AR Customer Details Credit Limits And Payment Terms Report
SELECT
a.Org_Name,
a.co,
a.corp_code,
a.corp_name,
a.cust_purpose,
a.customer_code,
a.customer_name,
a.city,
a.country_code,
a.country_name,
a.payment_terms,
a.collector_name,
a.currency_code,
a.credit_limit,
a.tax_code,
a.discount_grace_days,
a.customer_type,
a.ship_and_credit_flag,
a.dsa_flag,
a.freight_code,
a.bill_source,
a.tariff_delivery_method,
a.invoice_print_exception,
a.cust_part_required,
a.override_bill_to_addr,
a.bill_to_order_region,
a.end_use_code,
a.sub_corp_code,
a.business_class,
a.receipt_bank,
a.carrier_name,
a.ship_to_order_region,
a.final_dest_country,
a.sales_office,
a.assy_loc_flag,
a.handling_code,
a.tariff_ship_to_country,
a.customer_item_control_required,
a.transit_time_days,
a.free_trade_zone,
a.fob_code,
a.ship_via_code,
a.edi_location,
a.edi_trading_partner_id,
a.vat_registration_num,
a.corp_type,
a.address_line1,
a.address_line2,
a.address_line3,
a.address_line4,
a.postal_code,
a.state,
a.order_type,
a.freight_term,
a.item_type_identifier,
a.date_codes_max_age_weeks_qty,
a.on_time_ship_late_days,
a.service_program,
a.ship_early_days,
a.credit_quality_code,
a.partial_shipment_crd,
a.lockbox,
a.cust_status,
a.tp_reference_ext,
a.ship_to_dsa_flag,
a.ship_to_ship_and_credit,
a.ship_to_bill_source,
a.email_address,
replace(REGEXP_SUBSTR(a.jobt, '[^:]+', 1, 1),'@',NULL) csr_contact_user_name,
replace(REGEXP_SUBSTR(a.jobt, '[^:]+', 1, 2),'@',NULL) csr_contact_user_id
FROM
(Select Distinct
par.party_name corp_name,
cac.account_number corp_code,
cac.attribute5 corp_type,
--cst.org_id org_id,
cst.site_use_code cust_purpose,
cst.location customer_code,
cst.tax_code tax_code,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute11
ELSE NULL
END
customer_type,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute9
ELSE NULL
END
ship_and_credit_flag,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute10
ELSE NULL
END
dsa_flag,
--wcr.freight_code freight_code,
NUll freight_code,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute3
ELSE NULL
END
bill_source,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute1
ELSE NULL
END
tariff_delivery_method,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute6
ELSE NULL
END
invoice_print_exception,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute3
ELSE NULL
END
cust_part_required,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute7
ELSE NULL
END
override_bill_to_addr,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute12
ELSE NULL
END
bill_to_order_region,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute1
ELSE NULL
END
end_use_code,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute2
ELSE NULL
END
sub_corp_code,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute8
ELSE NULL
END
business_class,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute9
ELSE NULL
END
receipt_bank,
cnm.party_name carrier_name,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute12
ELSE NULL
END
ship_to_order_region,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute13
ELSE NULL
END
final_dest_country,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute14
ELSE NULL
END
sales_office,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute16
ELSE NULL
END
assy_loc_flag,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute21
ELSE NULL
END
handling_code,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute7
ELSE NULL
END
tariff_ship_to_country,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute18
ELSE NULL
END
customer_item_control_required,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute19
ELSE NULL
END
transit_time_days,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute22
ELSE NULL
END
free_trade_zone,
cst.tax_reference vat_registration_num,
--cst.freight_term,
NULL order_type,
NULL freight_term,
NULL item_type_identifier,
--cst.item_cross_ref_pref item_type_identifier,
NULL date_codes_max_age_weeks_qty,
NULL on_time_ship_late_days,
NULL service_program,
NULL ship_early_days,
NULL credit_quality_code,
NULL partial_shipment_crd,
CASE
WHEN cst.attribute_category = 'BILL_TO' THEN cst.attribute4
ELSE NULL
END
lockbox,
cst.status cust_status,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute4
ELSE NULL
END
ship_to_dsa_flag,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute5
ELSE NULL
END
ship_to_ship_and_credit,
CASE
WHEN cst.attribute_category = 'SHIP_TO' THEN cst.attribute6
ELSE NULL
END
ship_to_bill_source,
sit.ece_tp_location_code edi_location,
pts.attribute1 tp_reference_ext,
pts.attribute2 edi_trading_partner_id,
pts.party_site_name customer_name,
loc.city city,
loc.country country_code,
loc.address1 address_line1,
loc.address2 address_line2,
loc.address3 address_line3,
loc.address4 address_line4,
loc.postal_code postal_code,
loc.state state,
ter.territory_short_name country_name,
Substr(opr.name,1,3) co,
trm.name payment_terms,
prf.discount_grace_days,
pra.currency_code currency_code,
pra.overall_credit_limit credit_limit,
col.name collector_name,
hro.NAME Org_Name,
ctx.SHIP_VIA ship_via_code,
ctx.FOB_POINT fob_code,
(
SELECT
LISTAGG(hcp.email_address,', ') WITHIN GROUP(
ORDER BY
NULL
)
FROM
hz_party_sites hps,
hz_cust_acct_sites_all sit,
hz_contact_points hcp
WHERE
sit.party_site_id = hps.party_site_id
AND hcp.owner_table_id = hps.party_site_id
AND hcp.contact_point_type = 'EMAIL'
AND hcp.owner_table_name = 'HZ_PARTY_SITES'
AND hcp.status = 'A'
AND sit.cust_acct_site_id = cst.cust_acct_site_id
) email_address,
(SELECT
Listagg((CASE
WHEN hpsub.person_last_name IS NULL THEN hpsub.party_name
WHEN hpsub.person_last_name IS NOT NULL THEN hpsub.person_first_name
|| ', '
|| hpsub.person_last_name
END), '; ') Within GROUP(ORDER BY contact_number) || ':' ||
Listagg(hoc.contact_number, ', ') Within GROUP(ORDER BY contact_number)
FROM
hz_cust_account_roles hcar,
hz_parties hpsub,
hz_parties hprel,
hz_org_contacts hoc,
hz_relationships hr,
fnd_lookup_values_vl lookups,
hz_cust_accounts hca,
hz_contact_points con_mobile,
hz_contact_points con_gen,
hz_contact_points con_email
WHERE
1 = 1
AND hprel.party_id = con_mobile.owner_table_id (+)
AND hprel.party_id = con_gen.owner_table_id (+)
AND hprel.party_id = con_email.owner_table_id (+)
AND con_gen.owner_table_name (+) = 'HZ_PARTIES'
AND con_mobile.owner_table_name (+) = 'HZ_PARTIES'
AND con_email.owner_table_name (+) = 'HZ_PARTIES'
AND con_gen.contact_point_type (+) = 'PHONE'
AND con_mobile.contact_point_type (+) = 'PHONE'
AND con_email.contact_point_type (+) = 'EMAIL'
AND con_mobile.phone_line_type (+) = 'MOBILE'
AND con_gen.phone_line_type (+) = 'GEN'
AND hcar.role_type (+) = 'CONTACT'
--AND hcar.party_id = hr.party_id
AND hr.RELATIONSHIP_ID = hcar.RELATIONSHIP_ID
AND hr.Object_id = hprel.party_id
AND hr.subject_id = hpsub.party_id
AND hoc.party_relationship_id = hr.relationship_id
AND hcar.cust_account_id = hca.cust_account_id
AND hca.party_id = hr.object_id
AND hcar.status = 'A'
AND lookups.lookup_type (+) = 'RESPONSIBILITY'
AND lookups.lookup_code (+) = hoc.job_title_code
and hoc.job_title = 'CSR'
and hcar.cust_acct_site_id = sit.cust_acct_site_id
) jobt
From
hz_cust_accounts cac,
hz_cust_site_uses_all cst,
hz_cust_acct_sites_all sit,
hz_parties par,
hz_party_sites pts,
hz_locations loc,
fnd_territories_tl ter,
hr_operating_units opr,
Ra_Terms_Tl Trm,
Hz_Customer_Profiles_f prf,
Hz_Cust_Profile_Amts_f pra,
ar_collectors col,
fnd_setid_sets fss,
fnd_setid_assignments fsa,
hr_organization_units_f_tl hro,
Wsh_org_Carrier_Services wcs,
Wsh_Carriers Wcr,
hz_parties cnm,
Ra_Customer_Trx_All Ctx
where 1 = 1
--
AND sit.cust_acct_site_id (+) = cst.cust_acct_site_id
AND cac.cust_account_id (+) = sit.cust_account_id
AND par.party_id (+) = cac.party_id
--AND par.party_name='ARROW ELECTRONICS'
--AND pts.party_site_name ='ARWFZ'
AND pts.party_site_id (+) = sit.party_site_id
AND loc.location_id (+) = pts.location_id
AND ter.territory_code(+) = loc.country
AND ter.LANGUAGE (+) = 'US'
AND Cst.Status = 'A'
--AND opr.organization_id = cst.org_id
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Pra.Cust_Account_Profile_Id(+) = Prf.Cust_Account_Profile_Id
AND Trm.Term_Id(+) = Prf.Standard_Terms
AND trm.language (+) = 'US'
AND Col.Collector_Id(+) = Prf.Collector_Id
and fsa.reference_group_name= 'HZ_CUSTOMER_ACCOUNT_SITE'
and fsa.DETERMINANT_TYPE = 'BU'
AND FSA.SET_id=Cst.Set_Id
and fss.set_id = fsa.set_id
and fss.language= 'US'
and hro.language = 'US'
and fsa.DETERMINANT_VALUE = hro.organization_id
AND Opr.organization_id = hro.organization_id
AND Wcs.Organization_Id(+) =hro.organization_id
AND Wcr.Carrier_Id(+) = Wcs.Carrier_Id
AND Cnm.Party_Id(+) = Wcr.Carrier_Id
AND Ctx.Bill_To_Site_Use_Id(+) = Cst.Site_Use_Id
AND ( CASE
WHEN hro.NAME IN ( :p_ORG_NAME ) THEN 1
WHEN ( coalesce(NULL, :p_ORG_NAME) IS NULL ) THEN 1
END = 1 )
AND ( case when Substr(Opr.name, 1, 3) in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
AND ( CASE
WHEN Cac.Account_Number IN ( :p_Corp_Code ) THEN 1
WHEN ( coalesce(NULL, :p_Corp_Code) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Cst.Location IN ( :p_customer_code ) THEN 1
WHEN ( coalesce(NULL, :p_customer_code) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Cst.Site_Use_Code IN ( :p_cust_purpose ) THEN 1
WHEN ( coalesce(NULL, :p_cust_purpose) IS NULL ) THEN 1
END = 1 )
) a
WHERE 1 = 1
No comments:
Post a Comment