Oracle Receivables

Query to get AR and AGIS Invoices

The below given SQL Query will retrieve the set of data for 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 […]

Query to get the Receipt Class and Receipt Method setup in the system

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 CLASS.NAME CLASS_NAME, CLASS.CREATION_METHOD_CODE, CLASS.REMIT_METHOD_CODE, METHOD.NAME METHOD_NAME, METHOD.ATTRIBUTE_CATEGORY, METHOD.ATTRIBUTE1, METHOD.ATTRIBUTE6, METHOD.ATTRIBUTE7, METHOD.ATTRIBUTE9, METHOD.ATTRIBUTE10, METHOD.ATTRIBUTE11, BNKACCT.BANK_ACCOUNT_NAME, BNKACCT.CURRENCY_CODE, BNKBRCH.BANK_NAME, […]

Oracle apps R12 Query to get the Credit Memo Balance Due

  SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) CM_BALANCE_DUE FROM HZ_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 CC WHERE 1=1 AND AR_PAY_SCH.CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID AND AR_PAY_SCH.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_ID AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND LOC.LOCATION_ID = HPS.LOCATION_ID AND HZ_ACCT.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HCASA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID AND AR_PAY_SCH.CUSTOMER_ID […]

Oracle apps R12 Query to check Customer Transaction Balances

SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) INVOICE_BALANCE FROM HZ_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 CC WHERE 1=1 AND AR_PAY_SCH.CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID AND AR_PAY_SCH.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_ID AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND LOC.LOCATION_ID = HPS.LOCATION_ID AND HZ_ACCT.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID AND HCASA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID AND AR_PAY_SCH.CUSTOMER_ID = […]

AR SQL Query to get Open Invoices where Customer payment status is Open

Hi All, The below given SQL Query for Oracle Apps Receivables (AR)  provides the main columns from the table – RA_CUSTOMER_TRX_ALL and AR_PAYMENT_SCHEDULES_ALL to get the details for the Open Invoices for a particular Customer ( enter TRX_NUMBER) or for all Customers. SELECTRA_CUST_TRX.STATUS_TRX,RA_CUST_TRX.TRX_NUMBER,RA_CUST_TRX.TRX_DATE,RA_CUST_TRX.BILLING_DATE,RA_CUST_TRX.BATCH_ID,RA_CUST_TRX.BATCH_SOURCE_ID,RA_TERMS.NAME || ‘-‘ || RA_TERMS.DESCRIPTION   TERM_NAME,RA_CUST_TRX.INVOICE_CURRENCY_CODE TRX_CURR,APS.ORG_ID,APS.CUSTOMER_ID,APS.CUSTOMER_TRX_ID,–APS.TRX_NUMBER,–APS.TRX_DATE,APS.GL_DATE,APS.CLASS,APS.GL_DATE_CLOSED,APS.STATUS PAYMENT_STATUS,APS.INVOICE_CURRENCY_CODE,APS.EXCHANGE_DATE,APS.EXCHANGE_RATE_TYPE,APS.EXCHANGE_RATE,APS.DUE_DATE,APS.AMOUNT_DUE_ORIGINAL,APS.AMOUNT_DUE_REMAINING,APS.AMOUNT_LINE_ITEMS_REMAINING,APS.AMOUNT_APPLIED,APS.NUMBER_OF_DUE_DATESFROMHZ_CUST_ACCOUNTS HZ_ACCT,HZ_PARTIES HP,HZ_CUST_ACCT_SITES_ALL HCASA,HZ_CUST_SITE_USES_ALL HCSUA,HZ_PARTY_SITES HPS_BILL,RA_CUST_TRX_LINE_GL_DIST_ALL […]

Oracle Receivables – AR Setup Checklist that requires for transaction processing

The below given are the common AR setup to be done in Oracle Apps –  Oracle Receivables for the transaction processing. Oracle Receivable Setup Description Required ? Define Sets Of Books Yes Use The Account Generator Yes Define System Item Flexfields Structure Yes Define Organizations Yes Define Territory Flexfield No Define Sales Tax Location Flexfields […]

Scroll to top
Copy Protected by Chetan's WP-Copyprotect.