AR SLA Queries For PSA Tables

Home » Ar_cash_receipts_all And Xla_ae_headers » AR SLA Queries For PSA Tables

Maybe your like

Tuesday, October 7, 2014

AR SLA Queries for PSA tables

Below are the queries for AR. Pass on to the team members if anyone needs they can use for remediating AR objects. -- AR Transactions SELECT rct.trx_number, rct.customer_trx_id, rctd.cust_trx_line_gl_dist_id, xal.entered_dr, xal.entered_cr, xal.code_combination_id, xah.event_id, xal.ae_header_id FROM ra_customer_trx_all rct, ra_customer_trx_lines_all rctl, ra_cust_trx_line_gl_dist_all rctd, xla_ae_headers xah, xla_ae_lines xal, xla_events xet, xla_distribution_links xdl WHERE xal.ae_header_id = xah.ae_header_id AND xet.event_id = xah.event_id AND xdl.event_id = xet.event_id AND xdl.ae_header_id = xah.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND xdl.application_id =222 AND xdl.source_distribution_type IN('RA_CUST_TRX_LINE_GL_DIST_ALL', 'MFAR_DISTRIBUTIONS_ALL') AND xdl.source_distribution_id_num_1 = rctd.CUST_TRX_LINE_GL_DIST_ID AND rctl.customer_trx_line_id = rctd.customer_trx_line_id AND rct.customer_trx_id = rctl.customer_trx_id AND rct.trx_number =:p_trx_number; -- AR adjustments SELECT rct.trx_number, adj.customer_trx_id, adj.adjustment_id, xal.entered_dr, xal.entered_cr, xal.code_combination_id, xah.event_id, xal.ae_header_id FROM ra_customer_trx_all rct, ar_adjustments_all adj, ar_distributions_all ard, xla_ae_headers xah, xla_ae_lines xal, xla_events xet, xla_distribution_links xdl WHERE xal.ae_header_id = xah.ae_header_id AND xet.event_id = xah.event_id AND xdl.event_id = xet.event_id AND xdl.ae_header_id = xah.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND xdl.application_id =222 AND xdl.source_distribution_type IN('AR_DISTRIBUTIONS_ALL', 'MFAR_DISTRIBUTIONS_ALL') AND xdl.source_distribution_id_num_1 = ard.line_id AND ard.source_table ='ADJ' AND ard.source_id = adj.adjustment_id AND adj.adjustment_id =:p_adj_id AND rct.customer_trx_id = adj.customer_trx_id AND rct.trx_number =:p_trx_number; -- Misc Receipts SELECT cr.receipt_number, cr.cash_receipt_id, arm.misc_cash_distribution_id, xal.entered_dr, xal.entered_cr, xal.code_combination_id, xah.event_id, xal.ae_header_id FROM ar_cash_receipts_all cr, ar_misc_cash_distributions_all arm, ar_distributions_all ard, xla_ae_headers xah, xla_ae_lines xal, xla_events xet, xla_distribution_links xdl WHERE xal.ae_header_id = xah.ae_header_id AND xet.event_id = xah.event_id AND xdl.event_id = xet.event_id AND xdl.ae_header_id = xah.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND xdl.source_distribution_type IN('AR_DISTRIBUTIONS_ALL', 'MFAR_DISTRIBUTIONS_ALL') AND xdl.source_distribution_id_num_1 = ard.line_id AND ard.source_id = arm.misc_cash_distribution_id AND ard.source_table ='MCD' AND xdl.application_id =222 AND arm.cash_receipt_id = cr.cash_receipt_id AND cr.cash_receipt_id =:receipt_id; -- CASH Receipts SELECT cr.receipt_number, cr.cash_receipt_id, arp.receivable_application_id, xal.entered_dr, xal.entered_cr, xal.code_combination_id, xah.event_id, xal.ae_header_id FROM ar_cash_receipts_all cr, ar_receivable_applications_all arp, ar_distributions_all ard, xla_ae_headers xah, xla_ae_lines xal, xla_events xet, xla_distribution_links xdl, gl_code_combinations gcc WHERE xal.ae_header_id = xah.ae_header_id AND xet.event_id = xah.event_id AND xdl.event_id = xet.event_id AND xdl.ae_header_id = xah.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND xdl.source_distribution_type IN('AR_DISTRIBUTIONS_ALL', 'MFAR_DISTRIBUTIONS_ALL') AND xdl.source_distribution_id_num_1 = ard.line_id AND ard.source_id = arp.receivable_application_id AND ard.source_table ='RA' AND xdl.application_id =222 AND arp.cash_receipt_id = cr.cash_receipt_id AND cr.cash_receipt_id =:receipt_id AND gcc.code_combination_id = xal.code_combination_id AND gcc.segment1 !='00000000000000' Posted by at 11:19 AM Labels: R12 Upgradation

3 comments:

  1. UnknownApril 3, 2017 at 6:08 AM

    Very helpful, thanks!

    ReplyDeleteReplies
      Reply
  2. ykOctober 12, 2017 at 9:16 AM

    Extremely helpful.. Thanks!

    ReplyDeleteReplies
      Reply
  3. PedroJanuary 15, 2019 at 6:24 PM

    yes this is extremely helpful!, do you have the corresponding queries for CLoud tables? thanks!

    ReplyDeleteReplies
      Reply
Add commentLoad more... Newer Post Older Post Home Subscribe to: Post Comments (Atom)

About Me

Suresh View my complete profile

Search This Blog

Translate

Total Pageviews

Labels

  • AP (2)
  • AR (4)
  • Bulk Collect (2)
  • Flex Fields (1)
  • Forms (5)
  • General (24)
  • GL (2)
  • Interview Questions (15)
  • INV (2)
  • O2C (2)
  • OAF (1)
  • OM (16)
  • Oracle Apps (13)
  • Oracle Certifications (1)
  • P2P (1)
  • Performance Tuning (2)
  • PL SQL (20)
  • PO (2)
  • R12 Upgradation (4)
  • Special Value set (1)
  • SQL (10)
  • SQLLDR (1)
  • TCA (2)
  • Unix (3)
  • Workflows (1)
  • xml publisher (7)

Blog Archive

  • ▼  2014 (17)
    • ▼  October (1)
      • AR SLA Queries for PSA tables

Followers

Popular Posts

  • SQL query to find Open/Close Periods in Oracle Apps R12 Query to find the Gl Set of Books. To find SET_OF_BOOKS_ID: SELECT * FROM gl_sets_of_books Inventory SELECT DISTINCT opu.na...
  • Oracle Applications R12 Trading Community Architecture ( TCA ) Background What is TCA? Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about...
  • Pick Release Sales order - Oracle Apps Technical What is Pick Release? Pick release perform process starts, once the Order is scheduled and booked, then we need to release the order ...
  • (no title) Oracle Report Manager Release Notes, Release 12.0.6 (Note  741767.1 ) This document provides delta ination from Release Update Pack 4 (.A...
  • Differentiates between a Party and Customer in Oracle Apps In AR (Account receivables) or TCA (Trading community architecture), we usually comes across two common terms, party and customer. Though ...
  • Oracle Built-in Functions Oracle Built-in Functions Version 11.1   Function Name Analytic Collection Conver sion Date Time Data Mining Misc. Model Clause Nul...
  • Excel file output from Oracle Applications concurrent request using SYLK (PCL Format type). aka Look Ma, no BIP! Need to create Microsoft Excel style files directly operable from Oracle Applications concurrent request output ... without using BI Publ...
  • How to invoke an Oracle apps form from a workflow notification How to invoke an Oracle apps form from a workflow notification Open Document functionality is a standard functionality within Oracle w...
  • Setting up Forms60_Path in Registry Setting up Forms60_Path in Registry Setting up Forms60_Path in Registry Copy all these PL/SQL libraries and the Template form in ...
  • O2C Cycle Oracle Apps R12 O2C CYCLE steps Steps Enter sales order Book the sales order Pick release ( Release sales order) Move order Ship confirm Creat...

Tag » Ar_cash_receipts_all And Xla_ae_headers