SELECT
ooh.order_number
, hps_ship_to.party_site_number site_number
, hcs_ship_to.SITE_USE_CODE "SITE_USE_PURPOSE_CODE"
, hp_ship_to.party_name customer_name
, hl_ship_to.address1
||' '||Decode(hl_ship_to.address2,NULL,'',chr(10))
||' '||hl_ship_to.address2
||' '||Decode(hl_ship_to.address3,NULL,'', chr(10))
||' '||hl_ship_to.address3
||' '||Decode(hl_ship_to.address4,NULL,'', chr(10))
||' '||hl_ship_to.address4
||' '||Decode(hl_ship_to.city,NULL,'',chr( 10))
||' '||hl_ship_to.city
||' '||Decode(hl_ship_to.state,NULL,'',',')
||' '||hl_ship_to.state
||' '||Decode(hl_ship_to.postal_code,'',',')
||' '||hl_ship_to.postal_code ship_to_address
,hl_ship_to.country ship_to_country
, (
Select
hcp.phone_area_code
|| ' '
|| hcp.phone_number
from
apps.hz_cust_accounts hca
, apps.hz_contact_points hcp
where
1 =1
and hp_ship_to.party_id = hca.party_id
and hca.cust_account_id = hca_ship_to.cust_account_id
and hcp.owner_table_id = hca_ship_to.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
)
Phone_number
FROM
oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_ship_to
, hz_cust_acct_sites_all hca_ship_to
, hz_party_sites hps_ship_to
, hz_parties hp_ship_to
, hz_locations hl_ship_to
, mtl_parameters mp
, apps.wsh_DELIVERY_details wdd
, apps.oe_order_lines_all oola
, apps.wsh_new_deliveries wnd
, apps.wsh_DELIVERY_assignments wda
WHERE
1 = 1
AND ooh.ship_to_org_id = hcs_ship_to.site_use_id
AND hcs_ship_to.cust_acct_site_id = hca_ship_to.cust_acct_site_id
AND hca_ship_to.party_site_id = hps_ship_to.party_site_id
AND hps_ship_to.party_id = hp_ship_to.party_id
AND hps_ship_to.location_id = hl_ship_to.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id
AND ooh.header_id = oola.header_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wdd.organization_id = wnd.organization_id
and wda.delivery_id = :IP_DELIVERY_ID
union
SELECT
ooh.order_number
, hps_bill_to.party_site_number site_number
, hcs_bill_to.SITE_USE_CODE "SITE_USE_PURPOSE_CODE"
, hp_bill_to.party_name BILL_TO_customer_name
, hl_bill_to.address1
||' '||Decode(hl_bill_to.address2,NULL,'',chr(10))
||' '||hl_bill_to.address2
||' '||Decode(hl_bill_to.address3,NULL,'', chr(10))
||' '||hl_bill_to.address3
||' '||Decode(hl_bill_to.address4,NULL,'', chr(10))
||' '||hl_bill_to.address4
||' '||Decode(hl_bill_to.city,NULL,'',chr( 10))
||' '||hl_bill_to.city
||' '||Decode(hl_bill_to.state,NULL,'',',')
||' '||hl_bill_to.state
||' '||Decode(hl_bill_to.postal_code,'',',')
||' '||hl_bill_to.postal_code bill_to_address
,hl_bill_to.country ship_to_country
, (
Select
hcp.phone_area_code
|| ' '
|| hcp.phone_number
from
apps.hz_cust_accounts hca
, apps.hz_contact_points hcp
where
1 =1
and hp_bill_to.party_id = hca.party_id
and hca.cust_account_id = hca_bill_to.cust_account_id
and hcp.owner_table_id = hca_bill_to.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
)
Phone_number
FROM
oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_bill_to
, hz_cust_acct_sites_all hca_bill_to
, hz_party_sites hps_bill_to
, hz_parties hp_bill_to
, hz_locations hl_bill_to
, mtl_parameters mp
, apps.wsh_DELIVERY_details wdd
, apps.oe_order_lines_all oola
, apps.wsh_new_deliveries wnd
, apps.wsh_DELIVERY_assignments wda
WHERE
1 = 1
AND ooh.invoice_to_org_id = hcs_bill_to.site_use_id
AND hcs_bill_to.cust_acct_site_id = hca_bill_to.cust_acct_site_id
AND hca_bill_to.party_site_id = hps_bill_to.party_site_id
AND hps_bill_to.party_id = hp_bill_to.party_id
AND hps_bill_to.location_id = hl_bill_to.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id
AND ooh.header_id = oola.header_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wdd.organization_id = wnd.organization_id
and wda.delivery_id = :IP_DELIVERY_ID
union
SELECT
ooh.order_number
, hps_del_to.party_site_number site_number
, hcs_del_to.SITE_USE_CODE "SITE_USE_PURPOSE_CODE"
, hp_del_to.party_name customer_name
, hl_del_to.address1
||' '||Decode(hl_del_to.address2,NULL,'',chr(10))
||' '||hl_del_to.address2
||' '||Decode(hl_del_to.address3,NULL,'', chr(10))
||' '||hl_del_to.address3
||' '||Decode(hl_del_to.address4,NULL,'', chr(10))
||' '||hl_del_to.address4
||' '||Decode(hl_del_to.city,NULL,'',chr( 10))
||' '||hl_del_to.city
||' '||Decode(hl_del_to.state,NULL,'',',')
||' '||hl_del_to.state
||' '||Decode(hl_del_to.postal_code,'',',')
||' '||hl_del_to.postal_code delivery_to_address
,hl_del_to.country ship_to_country
, (
Select
hcp.phone_area_code
|| ' '
|| hcp.phone_number
from
apps.hz_cust_accounts hca
, apps.hz_contact_points hcp
where
1 =1
and hp_del_to.party_id = hca.party_id
and hca.cust_account_id = hca_del_to.cust_account_id
and hcp.owner_table_id = hca_del_to.party_site_id
and hcp.owner_table_name = 'HZ_PARTY_SITES'
)
Phone_number
FROM
oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_del_to
, hz_cust_acct_sites_all hca_del_to
, hz_party_sites hps_del_to
, hz_parties hp_del_to
, hz_locations hl_del_to
, mtl_parameters mp
, apps.wsh_DELIVERY_details wdd
, apps.oe_order_lines_all oola
, apps.wsh_new_deliveries wnd
, apps.wsh_DELIVERY_assignments wda
WHERE
1 = 1
AND ooh.DELIVER_TO_ORG_ID = hcs_del_to.site_use_id
AND hcs_del_to.cust_acct_site_id = hca_del_to.cust_acct_site_id
AND hca_del_to.party_site_id = hps_del_to.party_site_id
AND hps_del_to.party_id = hp_del_to.party_id
AND hps_del_to.location_id = hl_del_to.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id
AND ooh.header_id = oola.header_id
AND oola.header_id = wdd.source_header_id
AND oola.line_id = wdd.source_line_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wdd.organization_id = wnd.organization_id
and wda.delivery_id = :IP_DELIVERY_ID
;