Year: 2016

Oracle apps R12 Query to get the Credit Memo Balance Due

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 apps R12 Query to check Customer Transaction Balances

The below given SQL Query provides the data extract 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 […]

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

oracle apps R12 Oracle Receivables (AR) Receipt creation

In 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 Receipts includes the revenue earned from the interest, refunds, investments or any other nonstandard item. […]

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