The below given SQL Query will retrieve the set of data for AP 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 AII.INVOICE_ID, AII.GROUP_ID, AII.INVOICE_NUM, AII.INVOICE_AMOUNT, AII.ORG_ID AP_ORG_ID, RCTA.ATTRIBUTE9, FTR.TRANSACTION_LE_ID, FTR.TP_LE_ID, FTH.TRX_ID, RCTA.ORG_ID AR_ORG_ID, RT.NAME, FTH.BATCH_ID, AII.SOURCE, FTH.TO_LEDGER_ID FROM […]
Category: Oracle Receivables
Suggestuons, SQL Queries , content shared for the Oracle Apps R12 – 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 AGIS – Intercompany Matched or unmatched Balances for a particular Account level details
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|| […]
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 […]