Oracle Receivables Archives - oracleappshelp.com

Query to get AP and AGIS Invoices

By oracleappsadminPosted on November 20, 2017August 10, 2020Posted in AP- Account Payables, Oracle ReceivablesTagged AGIS and AP Invoices for Consolidated batches, AGIS and AP Invoices for Non Consolidated batches, AGIS R12 Reconciliation issues with AP Invoice mapping, AP and AGIS Invoice mapping in R12, Oracle Apps AGIS R12 invoice mappiing with AP

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 […]

Query to get AR and AGIS Invoices

By oracleappsadminPosted on November 20, 2017August 10, 2020Posted 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, 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

By oracleappsadminPosted on November 20, 2017November 20, 2017Posted 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|| […]

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

By oracleappsadminPosted on November 20, 2017Posted 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

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

By oracleappsadminPosted on July 17, 2016July 17, 2016Posted in Oracle ReceivablesTagged Customer Credit Memo Amount Due, Oracle apps R12 Credit Memo Balance Due, 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

By oracleappsadminPosted on July 17, 2016August 7, 2020Posted in Oracle ReceivablesTagged AR Customer SQL to get Pending due amount, oracle apps customer balances for a particular date

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

By oracleappsadminPosted on July 12, 2016August 10, 2020Posted in Oracle ReceivablesTagged AR Invoice balance, AR_PAYMENT_SCHEDULES_ALL, How to get invoice status columns, Open AR transaction in R12, open customer payment status, Query to Extract AR Invoice open payment status, query to get unpaid invoices in oracle apps, RA_CUSTOMER_TRX_ALL

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

By oracleappsadminPosted on July 12, 2016August 7, 2020Posted in Oracle Receivables

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 […]

AR Invoice detailed query to identify if transactions has been posted to GL tables

By oracleappsadminPosted on July 12, 2016July 12, 2016Posted in Oracle ReceivablesTagged AR Invoice Details Query into GL tables, AR Invoice query, AR transaction data from RA_CUSTOMER_TRX_ALL, AR transaction to GL SQL Queries, AR transactions posted to GL Tables, AR transactions submitted to GL, GL Header and Line Table data for AR Transactions, GL Import reference table -.GL_IMPORT_REFERENCES, GL_CODE_COMBINATIONS, identify if AR transactions have been accoutned to GL tables, invoices query R12 of receivables, Oracle Accounts Receivables ( AR ) SQL Scripts, Oracle R12 AR query for Invoice transaction details, Query to Extract AR Invoice Details, R12 AR Customer Query, R12- AR to GL Query, RA_CUST_TRX_LINE_GL_DIST_ALL, XLA Distribution Table -XLA_DISTRIBUTION_LINKS, XLA Event Header Table - XLA_AE_HEADERS, XLA Transaction table - XLA_TRANSACTION_ENTITIES

Hi All, Please find below the Query to identify if transactions has been posted to GL. This will be helpful when validating transactions during the Month End Period Closure. SELECT RA_CUST_TRX.STATUS_TRX, GL.SHORT_NAME, RA_CUST_TRX.INVOICE_CURRENCY_CODE TRX_CURR, RA_CUST_TRX.TRX_NUMBER, RA_CUST_TRX.TRX_DATE, RA_CUST_TRX.BILLING_DATE, GJH.NAME GL_JE_HEADER_NAME, GJH.DESCRIPTION GL_HDR_DESC, GJH.JE_BATCH_ID, GJL.JE_LINE_NUM, RA_CUST_TRX.CUST_TRX_TYPE_ID, RA_CUST_TRX.BILL_TO_CUSTOMER_ID, RA_CUST_TRX.BATCH_ID, RA_CUST_TRX.BATCH_SOURCE_ID, RA_CUST_TRX.TERM_ID, RA_CUST_TRX_DIST.AMOUNT AR_INV_DIST_AMOUNT, GJL.CODE_COMBINATION_ID GL_JE_LINE_CCID, RA_CUST_TRX_DIST.CODE_COMBINATION_ID AR_INV_DIST_CCID, […]