Introduction: This query will fetch you the party and location details for a sales order. The input parameter passed is ‘Sales order number’which will get processed and provide you with…
Read MoreIntroduction: This blog has the SQL query that can be used to pull the data for the AR Customer Collector Portfolio Report Cause of the issue: Business wants to see the Customer Collector Portfolio Details How do we solve: Create a BI report in fusion using below SQL query to extract the details. SELECT customer_name, account_number, portfolio, portfolio_description, business_unit, account_profile_class_effective_end_date, account_profile_class, account_profile_collector FROM ( SELECT hp.party_name customer_name, hca.account_number account_number, hca.attribute10 portfolio, ( CASE WHEN hca.attribute10 = ‘Default – Portfolio’ THEN ‘Portfolio Default for Unclassified Customer Accounts’ WHEN hca.attribute10 != ‘Default – Portfolio’ THEN hca.attribute10 END ) portfolio_description, hou.name business_unit,…
Read MoreIntroduction: This blog has the SQL query that can be used to pull the data for the AR Customer Dunning Strategy Status Report Cause of the issue: Business wants to see the AR Customer Dunning Strategy Status Report How do we solve: Create a BI report in fusion using below SQL query to extract the details. With cust as ( select hp.party_id, hp.party_number cust_number, hp.party_name cust_name, hca.cust_account_id, hca.account_number CUST_ACCOUNT_NUMBER from hz_parties hp, hz_cust_accounts hca where hp.party_id = hca.party_id ) , cltr as ( select acl.collector_id, acl.employee_id, acl.name collector, pmail.email_address collector_email from ar_collectors acl, per_email_addresses_v pmail where acl.employee_id = pmail.person_id ) , stg_temp as ( select WORK_ITEM_TEMP_ID, name task, DESCRIPTION from IEX_STRY_TEMP_WORK_ITEMS_VL group by WORK_ITEM_TEMP_ID, name, DESCRIPTION )…
Read MoreIntroduction : This blog has the configuration update the same customer tax registration number for multiple customers/sites Why we need to do : As per the legal requirement, Business requested…
Read MoreIntroduction : This blog has the configuration to skip the deletion of the entries from AR_STATEMENT_HEADERS and AR_STATMENT_LINE_CL Why we need to do : While creating/customizing/debugging the customer statement…
Read Moreoracle-ebs-customer-payment-terms SELECT customer_name, site_use_code, location, payment_term_id, name FROM apps.hz_cust_site_uses_all a1, hz_cust_acct_sites_all a2, ar_customers a3, ra_terms a4 WHERE a1.cust_acct_site_id = a2.cust_acct_site_id AND a2.cust_account_id = a3.customer_id …
Read MoreSELECT amount_dr, amount_cr, acctd_amount_dr, acct_amount_cr, gcc.segment1 ||’.’ ||gcc.segment2 ||’.’ ||gcc.segment3 ||’.’ ||gcc.segment4 ||’.’ ||gcc.segment5 FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc WHERE 1=1 and source_table=’CRH’ and ad.code_combination_id=gcc.code_combination_id and EXISTS( SELECT ‘T’ FROM…
Read MoreIntroduction: 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 MoreIntroduction: 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 Moreselect 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 More