Oracle and Open Source Technology Blog
online technical discussion platform
Suggestuons, SQL Queries , content shared for the Oracle Apps R12 – Oracle Receivables
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,
Continue readingThe 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,
Continue readingPlease 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|| ‘.’||
Continue readingPlease 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
Continue readingSELECT 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 =
Continue readingSELECT 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 =
Continue readingHi All, The below 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. SELECT RA_CUST_TRX.STATUS_TRX, RA_CUST_TRX.TRX_NUMBER, RA_CUST_TRX.TRX_DATE, RA_CUST_TRX.BILLING_DATE,
Continue readingHi All, Please find below the common AR setup to be done in Oracle Apps – Oracle Receivables for the transaction processing. Setup Description Required? Define Sets Of Books Yes Use The Account Generator Yes Define System Item Flexfields
Continue readingHi 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,
Continue readingIn Oracle Receivables, Receipts can be created of 2 types: Standard Receipts: Standard Receipts includes Cash or Check received from the Customer in the exchange of goods or services provided. These are termed as ‘Cash Receipts’ also. Miscellaneous Receipts: Miscellaneous
Continue readingTransaction Source Setup describes the behavior for the Account Receivable (AR) transactions . Whenever a transaction is processed in Oracle Apps R12 and associated to the transaction source , it validates with the Transaction Source Setup to determine the validations
Continue readingPlease given are the Account Receivables (AR) Tables used for AR Setup , AR Transactions Oracle Apps R12 Account Receivables (AR) Tables Table Name Table Description AR Setup tables RA_BATCH_SOURCES AR Table for storing the transaction sources
Continue readingTransaction Types are used in Oracle Account Receivables (AR) to define the accounting for the debit memos, credit memos, on-account credits, chargebacks, commitments, and invoices which are created in Oracle Account Receivables (AR) . Transaction types are also used to determine
Continue readingOracle Account Receivables (AR) defines Payment Terms Setup for the Customers in Oracle Apps R12. Payment terms are used to determine the amount of each installment. Oracle Account Receivables allows to distribute tax and freight charges across all installments, or
Continue readingPlease find below the Navigation Path for the main AR Windows Window Name Navigator Path Account Details Collections->Account Details Account Overview Collections->Account Overview Accounting Calendar Set Up->Financials->Calendars-> Periods Accounting Periods Accounting->Open/Close Periods Aging Collections->Aging Alternate Name Receipt Matches Customers->Alternate
Continue reading