customer_details_credit_limits

 customer_details_credit_limits

select party.party_name Customer_name,party.party_number CUSTOMER_NUMBER,
 cpa.overall_credit_limit cust_overall_limit,rt.NAME terms_name ,
decode(OOL.line_category_code,'ORDER',nvl(sum(OOl.ordered_quantity),0),0) ordqty,
sum(wd.SHIPPED_QUANTITY) ,wnd.CONFIRM_DATE shipped_date,
wd.SHIPPED_QUANTITY*ool.UNIT_SELLING_PRICE supply_amount ,
        decode(OOl.line_category_code,'ORDER', nvl(sum(OOl.shipped_quantity),0),0) shipqty,
        decode(OOl.line_category_code,'RETURN',nvl(sum(OOl.ordered_quantity),0),0) retqty,
        decode(OOL.line_category_code,'RETURN',nvl(sum(OOl.ORDERED_QUANTITY*OOL.UNIT_COST),0),0) ret_AMOUNT

from
wsh_delivery_details wd,
wsh_new_deliveries wnd,
oe_order_headers_all ooh,
oe_order_lines_all ool,
--ra_customers ac,
ra_terms_tl rt,
 Hz_cust_accounts c,
  hz_parties party,
 hz_cust_accounts ca
, hz_customer_profiles cp
, hz_cust_site_uses_all csu
, hz_cust_profile_amts cpa
, hz_credit_usages cu
where wd.SOURCE_HEADER_ID=wnd.SOURCE_HEADER_ID
 AND c.cust_account_id=ca.cust_account_id
   AND c.party_id = party.party_id
   AND ca.cust_account_id=cp.cust_account_id
  AND cp.cust_account_profile_id=cpa.cust_account_profile_id
  AND cpa.cust_acct_profile_amt_id=cu.cust_acct_profile_amt_id(+)
  AND csu.site_use_id(+)=cp.site_use_id
and ooh.HEADER_ID=ool.HEADER_ID
and ool.LINE_ID=wd.SOURCE_LINE_ID
and ca.cust_account_id=ooh.SOLD_TO_ORG_ID
and rt.TERM_ID=ooh.PAYMENT_TERM_ID
--AND HCP.CUST_ACCOUNT_ID=AC.CUSTOMER_ID
AND CPA.cust_account_profile_id=cpa.cust_account_profile_id
AND cu.credit_profile_amt_id IS NULL
  AND cu.profile_class_amount_id IS NULL
  --AND ca.cust_account_id BETWEEN NVL(:p_customer_id_low, ca.cust_account_id)
             --    AND NVL(:p_customer_id_high, ca.cust_account_id)
  --AND NVL(cu.credit_usage_rule_set_id,0)=NVL(:p_cust_rset_id,NVL(cu.credit_usage_rule_set_id,0))
--AND CPA.credit_profile_amt_id IS NULL
  --AND HCP.profile_class_amount_id IS NULL
  --AND cpa.cust_acct_profile_amt_id=HCP.cust_acct_profile_amt_id(+)
group by party.party_name,
party.party_number,
wnd.confirm_date,
wd.SHIPPED_QUANTITY,
ool.UNIT_SELLING_PRICE ,
rt.name,
OOL.ORDERED_QUANTITY,
OOL.SHIPPED_QUANTITY,
OOL.UNIT_COST,
OOL.LINE_CATEGORY_CODE
, cpa.overall_credit_limit