Skip to main content

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

Read More

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

Read More

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

Read More

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

Read More

Copy Protected by Chetan's WP-Copyprotect.