Credit Memo Application Report
Introduction: This blog has the script to get the credit memo application details in Oracle Cloud application for Audit purpose. Cause of the issue: Auditor wanted to see the credit…
Read MoreQuery to display the customer name and customer address details
Introduction: This query is used to fetch the data of customer name and also the details like the account number, addresses, city, country, postal code and customer account id. In…
Read MoreQuery to get Accounting issue invoice details
select org_id, customer_trx_id, sum(tax) tax, sum(rev) rev, sum(rec) rec, sum(tax)+sum(rev) acutal_rec from ( select org_id, customer_trx_id, case when account_class=’TAX’ then sum(amount) else 0 end tax, case when account_class=’REV’ then sum(amount)…
Read MoreQuery to get the AR Netting details
select * from (select fnar.batch_id, fnar.customer_trx_id, rct.bill_to_customer_id cust_acct_id, rct.bill_to_site_use_id cust_site_use_id, fnar.ar_txn_rank, nvl(fnar.netted_amt,0) as netted_amt, fnb.batch_currency as reckoning_currency, rct.trx_number, rct.invoice_currency_code as transaction_currency, rctt.description as transaction_type, hp.party_name as cust_acct_name, hca.account_number as cust_acct_number,…
Read MoreCustomer Contact Details in EBS
Introduction: This blog has the SQL query that can be used to pull the data Organization contact details. Cause of the issue: Business wants to reach the organization contact…
Read MoreOracle R12 AR Lockbox Query
SELECT hou.name operating_unit, xep.name legal_entity, ala.lockbox_number “NUMBER”, absa.name batch_source, cebb.bank_name, –cebb.bank_number, –cebb.bank_branch_name, –cebb.bank_branch_number, –cebb.bank_branch_type, — Branch type indicates which list the bank routing number is on. Valid types are ABA,…
Read MoreScript to extract the AR partial credit memo’s with reference invoices
Introduction: This script will used to extract the AR partial credit memo’s with reference invoice details. select c.trx_number CM_Number, c.trx_date CM_DATE, (select a1.account_number from hz_cust_accounts_all a1 where a1.cust_account_id=c.bill_to_customer_id)CM_Customer_Number, d.line_type CM_LINE_TYPE,…
Read MoreScripts to get the customer bank details in fusion.
Introduction: This scripts will used to extract the customer bank information in fusion. SELECT IBY_EBA.*,hp.party_name, CA.ACCOUNT_NUMBER, cbb.bank_branch_name Branch_name FROM hz_cust_accounts ca, hz_parties hp, iby_external_payers_all IBY_PAYEE, iby_pmt_instr_uses_all IBY_PAYEE_USES ,…
Read MoreScript to extract the accounting rule and source wise AR invoice counts
Introduction: This script will extract the accounting rule and trx source wise invoice counts. SELECT C.NAME RULE_NAME, D.NAME SOURCE_NAME, count(A.TRX_NUMBER), DECODE(A.ORG_ID,’XX’,’IM US OU’,’XXX’,’IM CANADA OU’) OU_NAME FROM RA_CUSTOMER_TRX_ALL A ,…
Read More