Wednesday, March 22, 2017

R12 query to check Credit Limit of a customer

R12 query to check Credit Limit of a customer

 SELECT  haou.name ou_name,
         c.customer_number,
         c.customer_name,
         g.party_site_number,
         d.location,
         a.overall_credit_limit,
         d.site_use_id,
         SUM (e.amount_due_remaining) credit_balance      
    FROM HZ_CUST_PROFILE_AMTS a,
         HZ_CUST_ACCOUNTS b,
         ar_customers c,
         hz_cust_site_uses_all d,
         ar_payment_schedules_all e,
         hz_cust_acct_sites_all f,
         hz_party_sites g,
         hr_all_organization_units haou
   WHERE     overall_credit_limit IS NOT NULL
         AND a.cust_account_id = b.cust_account_id
         AND b.account_number = c.customer_number
         AND a.site_use_id = d.site_use_id
         AND c.customer_id = e.customer_id
         AND d.site_use_id = e.customer_site_use_id
         --and c.customer_number = :customer_number
         AND d.cust_acct_site_id = f.cust_acct_site_id
         AND g.party_site_id = f.party_site_id
         AND a.overall_credit_limit is not null
         and a.overall_credit_limit not in (0,1)
         --AND e.org_id = 101
         AND haou.organization_id=e.org_id
GROUP BY c.customer_name,
         c.customer_number,
         d.site_use_id,
         d.location,
         a.overall_credit_limit,
         g.party_site_number,
         haou.name
ORDER BY haou.name, CUSTOMER_NAME;

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...