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 Moreselect * 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 MoreIntroduction: 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 MoreSELECT 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 MoreIntroduction: 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 MoreIntroduction: 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 MoreIntroduction: 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 MoreIntroduction: This script will used to extract the AR accounted receipts details. select ‘HEADERS’ “FILE_IDENTIFIER” ,DECODE(ou.name,’IM US BU’,’IM US OU’,’IM CA BU’,’IM Canada OU’,ou.name) “BU_NAME”, cr.RECEIPT_NUMBER “Receipt Numder”, cr.type,crh_current.status “Current…
Read MoreIntroduction: This procedure is used to extract the adjustments from EBS for Cloud Import. create or replace PROCEDURE XXXX_AR_ADJ_EXTRACT_CLOUD ( errbuf OUT VARCHAR2, retcode OUT VARCHAR2, p_org_id IN NUMBER, p_trx_date_from…
Read More