GL To AR (Receivable) Query -R12 - ORACLE EBS PL/SQL

Tuesday, 3 March 2015

GL to AR (Receivable) Query -R12

SELECT GJH.NAME, GJH.DESCRIPTION, TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE, (SELECT HP.PARTY_NAME FROM RA_CUSTOMER_TRX_ALL RCTA , HZ_CUST_ACCOUNTS_ALL HCA , HZ_PARTIES HP WHERE RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID AND HCA.PARTY_ID = HP.PARTY_ID AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1) PARTY, (SELECT NVL(TO_CHAR(RCTA.DOC_SEQUENCE_VALUE),RCTA.TRX_NUMBER) FROM RA_CUSTOMER_TRX_ALL RCTA WHERE RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1 ) DOC_SEQUENCE_VALUE, GJH.JE_CATEGORY, XAL.ACCOUNTED_DR ACCOUNTED_DR, XAL.ACCOUNTED_CR ACCOUNTED_CR, GJL.JE_HEADER_ID, XAL.PARTY_TYPE_CODE, GJH.JE_SOURCE, GJH.PERIOD_NAME, GCC.SEGMENT5, GJL.JE_LINE_NUM, GJH.DEFAULT_EFFECTIVE_DATE FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH, GL_JE_LINES GJL, GL_CODE_COMBINATIONS GCC, GL_IMPORT_REFERENCES GIR, XLA_AE_LINES XAL, XLA_AE_HEADERS XAH, XLA.XLA_TRANSACTION_ENTITIES XTEWHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XTE.APPLICATION_ID = XAH.APPLICATION_ID AND XTE.ENTITY_ID = XAH.ENTITY_ID AND GJL.STATUS = 'P' AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5) AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)) AND GJH.JE_SOURCE = 'Receivables'---------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION, GJH.NAME JV_NAME, GJH.JE_CATEGORY, GJH.JE_SOURCE, GJH.PERIOD_NAME, NVL(XAL.ACCOUNTED_CR,0) GL_CR, NVL(XAL.ACCOUNTED_DR,0) GL_DR, GJL.DESCRIPTION JV_LINE_DESCRIPTION, XAH.EVENT_TYPE_CODE, XAH.DESCRIPTION SLA_DESCRIPTION, XAL.AE_LINE_NUM, XAL.ACCOUNTING_DATE GL_DATE, (SELECT AC.CUSTOMER_NAME FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME, (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER, (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE ) RECEIPT_DATE, (SELECT ACR.DOC_SEQUENCE_VALUE FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER, (SELECT ACR.CREATION_DATE FROM AR_CASH_RECEIPTS_ALL ACR WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_DATE, DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0) RECEIPT, DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH, GL_JE_LINES GJL, GL_CODE_COMBINATIONS GCC, GL_IMPORT_REFERENCES GIR, XLA_AE_LINES XAL, XLA_AE_HEADERS XAH, XLA.XLA_TRANSACTION_ENTITIES XTEWHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XTE.APPLICATION_ID = XAH.APPLICATION_ID AND XTE.ENTITY_ID = XAH.ENTITY_ID AND GJL.STATUS = 'P' AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5) AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)) AND GJH.JE_SOURCE = 'Receivables' By Unknown - March 03, 2015 Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest Labels: AR, General Ledger, GL, oracle

No comments:

Post a Comment

Newer Post Older Post Home Subscribe to: Post Comments (Atom)

Query to Find Receipt Class and its GL Combinition Query

SELECT ARC.NAME ReceiptClass,        ARC.CREATION_METHOD_CODE Creation_Mehthod,        DECODE (ARC.REMIT_METHOD_CODE,             ...

• Account Recievables Module (AR) Explanation (Recievables Useful Information) Accounts Receivables useful information ACCOUNTS RECEIVABLES: ==================== ACCOUNTING METHOD , ACCRUAL OR CASH :     So do you...
• Query to find Active Customer in Oracle Apps SELECT hou.name Organization_name,        ----------------------        --Customer Information        ----------------------    ...
• Important Join conditions between Oracle apps modules GL AND AP GL_CODE_COMBINATIONS          AP_INVOICES_ALL               code_combination_id        =        acct_pay_code_combination_id G...

Search This Blog

Total Pageviews

Pages

• Home
• AR
• AP
• GL
• OM
• Inventory
• Oracle Apps Tables
• Others
• Interface

Blog Archive

• ►  2018 (2)
  • ►  January (2)

• ►  2017 (4)
  • ►  August (3)
  • ►  March (1)

• ►  2016 (5)
  • ►  October (4)
  • ►  January (1)

• ▼  2015 (77)
  • ►  August (12)
  • ►  July (2)
  • ►  June (1)
  • ►  May (19)
  • ►  April (1)
  • ▼  March (11)
    • Supplier Invoice Interface Script
    • PO Report Quries
    • GL interface from legacy to GL
    • Bulk Collect
    • AP Interface Procedure 2nd Method
    • AP Complete Interface
    • UTL File Procedure
    • Concurrent Request Status Codes and Phase Codes
    • GL to AP (Payable) Query Oracle Apps R12
    • GL to AR (Receivable) Query -R12
    • Sub Ledger Break Up Query - Oracle Apps R12
  • ►  February (5)
  • ►  January (26)

About Me

Unknown View my complete profile

Report Abuse

• Usefull Links