Oracle Apps Account Receivables Tutorial

Oracle EBS R12 SQL to get AR and AGIS Invoices

By oracleappsadminPosted on November 20, 2017January 16, 2021Posted in Oracle ReceivablesTagged AGIS and AR Invoices for Consolidated batches, AGIS and AR Invoices for Non Consolidated batches, AGIS Reconciliation with transactions created, AR and AGIS Invoice mapping in R12, Oracle Apps AGIS R12 invoice mappiing with AR

The blog provides Oracle EBS R12 SQL to get AR and AGIS Invoices with consolidated and non consolidated batches based on the AGIS Batch Source Name , Org_id and Flex Value Set Name defined SELECT INTEF.ORG_ID, INTEF.INTERFACE_LINE_ID, INTEF.INTERFACE_LINE_ATTRIBUTE1 BATCH_ID, INTEF.INTERFACE_LINE_ATTRIBUTE2 TRX_ID, INTEF.INTERFACE_LINE_ATTRIBUTE3 LINE_ID, INTEF.INTERFACE_LINE_ATTRIBUTE4 BATCH_NUMBER, HEADERS.ATTRIBUTE1 , –trx_number in case of non-consolidated batches FDL.ATTRIBUTE1, — trx_number […]

Oracle EBS R12 SQL to get AGIS Matched or unmatched Balances at Account level

By oracleappsadminPosted on November 20, 2017January 16, 2021Posted in Oracle ReceivablesTagged AGIS Intercompany balances, AGIS R12 Reconciliation issues, AGIS R12 unmatched intercompany account details, Oracle Apps AGIS R12 Reconciliation issues

Please find below the SQL Query to identify the Intercompany Balances for a particular Account whether they have been MATCHED or UNMATCHED to resolve the Reconciliation Issues. SELECT DISTINCT A.SEGMENT1 INITIATOR_RECEIVABLE, A.SEGMENT5 RECEIPIENT_RECEIVABLE, ( A.SEGMENT1|| ‘.’|| A.SEGMENT2|| ‘.’|| A.SEGMENT3|| ‘.’|| A.SEGMENT4|| ‘.’|| A.SEGMENT5|| ‘.’|| A.SEGMENT6|| ‘.’|| A.SEGMENT7) REC_ACCOUNT, (A.RECEIVABLES), B.SEGMENT1 INITIATOR_PAYABLES, B.SEGMENT5 RECEIPIENT_PAYABLES, ( B.SEGMENT1|| […]

Oracle EBS R12 AR Receipt Class and Receipt Method SQL

By oracleappsadminPosted on November 20, 2017January 17, 2021Posted in Oracle ReceivablesTagged Oracle apps AR R12 Receipt method and Receipt class data retrieval, Query to get the Receipt Class and Receipt Method setup, Receipt Class, Receipt Method setup, Retrieve REceipt data from Oracle apps AR R12

The blog provides Oracle EBS R12 SQL to get AR Receipt Class and Receipt Method SQL Please find below the SQL Query to retrieve the Receipt Class and Receipt Method setup in the system Navigation in front End: Receivables Manager -> Setup -> Receipts -> Receipt Classes Query to verify the setup from backend SELECT […]

Oracle apps R12 Query to get the Credit Memo Balance Due

By oracleappsadminPosted on July 17, 2016August 13, 2020Posted in Oracle Receivables

The below given query provides the SQL Data extract for Credit Memo Balance Due. SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) CM_BALANCE_DUEFROMHZ_CUST_ACCOUNTS_ALL HZ_ACCT,HZ_CUST_ACCT_SITES_ALL HCASA,HZ_CUST_SITE_USES_ALL HCSU,HZ_PARTY_SITES HPS,HZ_LOCATIONS LOC,RA_CUSTOMER_TRX_ALL RCTA,RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUS_TRX_GL_DIST,AR_PAYMENT_SCHEDULES_ALL AR_PAY_SCH,GL_CODE_COMBINATIONS CCWHERE 1=1AND AR_PAY_SCH.CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_IDAND AR_PAY_SCH.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_IDAND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_IDAND LOC.LOCATION_ID = HPS.LOCATION_IDAND HZ_ACCT.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_IDAND HCASA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_IDAND AR_PAY_SCH.CUSTOMER_ID = HCASA.CUST_ACCOUNT_IDAND AR_PAY_SCH.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_IDAND […]

Oracle EBS R12 SQL to get Open Invoices Customer Transaction Balances

By oracleappsadminPosted on July 17, 2016January 17, 2021Posted in Oracle Receivables

The blog provides the Oracle EBS R12 SQL to check Customer Transaction Balances. The Oracle EBS R12 Account Receivable AR Invoices with Outstanding Customer Transaction Balances, AR Invoices which Over Due date are retrieved for AR Reconciliation The below given SQL Query provides the data extract to check Customer Transaction Balances SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM […]