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 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 MoreIntroduction: This is used to fix the invalid revenue account for AR invoice Auto accounting is used to determine the accounts when the distribution table is not populated. When Auto…
Read More