OM /DOO/HZ /TCA /Shipments A/Addresses
--------------------------------------
--Business Unit (BU) Name
SELECT hou.name
FROM hr_operating_units hou
WHERE organization_id = xx.org_id
--Business Unit (BU) Address
(SELECT ( hla.address_line_1
|| ' '
|| hla.address_line_2
|| ' '
|| hla.address_line_3
|| ' '
|| hla.address_line_4
|| ' '
|| hla.town_or_city
|| ' '
|| hla.region_1
|| ' '
|| hla.region_2 ) addd
FROM hr_locations_all hla,
hr_all_organization_units hrou
WHERE 1 = 1
AND hrou.organization_id = rcta.org_id
AND hla.location_id = hrou.location_id)
(
SELECT hao.organization_id,
pl.location_id,
pdf.address_line_1 address1,
pdf.address_line_2 address2,
pdf.town_or_city city,
pdf.postal_code,
pdf.region_2 state,
pdf.country,
pdf.address_line_1
||pdf.address_line_2
||pdf.town_or_city
||pdf.region_2
||pdf.postal_code
||pdf.country bu_concatinated_address
FROM hr_all_organization_units hao,
hr_org_unit_classifications_f hac,
per_locations pl,
per_location_details_f pldf,
per_addresses_f pdf
WHERE hao.organization_id = hac.organization_id
--FILTER---
AND hac.classification_code IN ( 'FUN_BUSINESS_UNIT' )
AND hao.location_id = pl.location_id
AND pl.location_id = pldf.location_id
AND pldf.main_address_id = pdf.address_id
--ADDED ON 4APR22
AND SYSDATE BETWEEN hac.effective_start_date AND hac.effective_end_date
AND SYSDATE BETWEEN pldf.effective_start_date AND pldf.effective_end_date
AND SYSDATE BETWEEN pdf.effective_start_date AND pdf.effective_end_date) bu_address,
--LE Name
SELECT
xe.NAME legal_entity
FROM xle_entity_profiles xe
WHERE xe.legal_entity_id=
--BU LE Name
(
SELECT Max(hle.name)
FROM hr_legal_entities hle
WHERE hle.legal_entity_id = hu.default_legal_context_id) bu_le_name
--AR Transaction /Invoice BILL_TO_Customer /Site /Location /Address
(
SELECT rct.customer_trx_id,
rct.bill_to_customer_id,
rct.bill_to_site_use_id,
hp.party_name,
hp.party_number,
hp.category_code,
hca.account_number,
hp.party_id,
hps.party_site_id,
hcsa.cust_account_id,
hcsa.cust_acct_site_id,
hcsu.location party_site_number,
hl.location_id,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.state,
hl.postal_code,
ft.territory_short_name country,
hp.jgzz_fiscal_code,
hcsu.site_use_id,
per_address_format.Format_address (hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.postal_code, NULL, NULL, NULL, hl.floor_number, hl.building, hl.country, NULL, NULL, NULL, NULL, NULL, 'POSTAL_ADDR', '', 'HCM_ADDR', '3') bill_to_address
FROM ra_customer_trx_all rct,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
fnd_setid_sets fss,
hz_party_sites hps,
hz_locations hl,
fnd_territories_vl ft,
hz_parties hp
WHERE rct.bill_to_customer_id = hca.cust_account_id
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND fss.LANGUAGE = 'US'
AND hcsa.set_id = fss.set_id
AND hps.location_id = hl.location_id
AND hl.country = ft.territory_code
AND hps.party_id = hp.party_id)bc
--BILL_TO Address
SELECT hps_bill.party_site_number bill_party_site_number,
hps_bill.party_site_id bill_party_site_id,
hca_bill.cust_account_id id1,
hl_bill.country bill_country,
hl_bill.address1 bill_address1,
hl_bill.address2 bill_address2,
hl_bill.address3 bill_address3,
hl_bill.address4 bill_address4,
hl_bill.city bill_city,
hl_bill.postal_code bill_Postal_code,
hl_bill.state bill_state,
hl_bill.province bill_province,
hp_bill.person_first_name,
hp_bill.person_last_name,
hp_bill.email_address,
hcsu_bill.site_use_id
FROM hz_cust_accounts hca_bill,
hz_parties hp_bill,
hz_cust_site_uses_all hcsu_bill,
hz_cust_acct_sites_all hcas_bill,
hz_party_sites hps_bill,
hz_locations hl_bill
WHERE 1 = 1
AND hl_bill.location_id = hps_bill.location_id
AND hps_bill.party_site_id = hcas_bill.party_site_id
AND hcas_bill.cust_acct_site_id = hcsu_bill.cust_acct_site_id
AND hca_bill.party_id = hp_bill.party_id
AND hca_bill.cust_account_id = hcas_bill.cust_account_id
AND hcsu_bill.site_use_code = 'BILL_TO'
AND hcsu_bill.primary_flag = 'Y'
--AR Transaction /Invoice SHIP_TO_Customer /Site /Location /Address
(
SELECT rct.customer_trx_id,
rct.bill_to_customer_id,
rct.bill_to_site_use_id,
rct.ship_to_party_site_use_id
hp.party_name,
hp.category_code,
hca.account_number,
hp.party_id,
hps.party_site_id,
hcsa.cust_account_id,
hcsa.cust_acct_site_id,
CASE
WHEN hcsa.attribute_category = 'Domestic Additional Info' THEN hcsa.attribute2
END doc_through,
hps.party_site_number,
hl.location_id,
hl.address1,
hl.address2,
hl.address3,
hl.address4,
hl.city,
hl.state,
hl.postal_code,
ft.territory_short_name country,
hp.jgzz_fiscal_code,
hcsu.site_use_id,
per_address_format.Format_address (hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.postal_code, NULL, NULL, NULL, hl.floor_number, hl.building, hl.country, NULL, NULL, NULL, NULL, NULL, 'POSTAL_ADDR', '', 'HCM_ADDR', '3') ship_to_address
FROM ra_customer_trx_all rct,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
hz_party_site_uses hpsu2,
fnd_setid_sets fss,
hz_party_sites hps,
hz_locations hl,
fnd_territories_vl ft,
hz_parties hp
WHERE rct.sold_to_party_id = hp.party_id
AND rct.ship_to_party_site_use_id = hpsu2.party_site_use_id(+)
AND hpsu2.party_site_id = hps.party_site_id(+)
AND hca.cust_account_id = hcsa.cust_account_id
AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND fss.LANGUAGE = 'US'
AND hcsa.set_id = fss.set_id
AND hps.location_id = hl.location_id
AND hl.country = ft.territory_code
AND hps.party_id = hp.party_id)sc
--SHIP_TO site/ Address
SELECT hps_ship.party_site_number ship_party_site_number,
hps_ship.party_site_id ship_party_site_id,
hca_ship.cust_account_id id2,
hl_ship.country ship_country,
hl_ship.address1 ship_address1,
hl_ship.address2 ship_address2,
hl_ship.address3 ship_address3,
hl_ship.address4 ship_address4,
hl_ship.city ship_city,
hl_ship.postal_code ship_postal_code,
hl_ship.state ship_state,
hl_ship.province ship_province,
hcsu_ship.site_use_id
FROM hz_cust_accounts hca_ship,
hz_parties hp_ship,
hz_cust_site_uses_all hcsu_ship,
hz_cust_acct_sites_all hcas_ship,
hz_party_sites hps_ship,
hz_locations hl_ship
WHERE 1 = 1
AND hl_ship.location_id = hps_ship.location_id
AND hps_ship.party_site_id = hcas_ship.party_site_id
AND hcas_ship.cust_acct_site_id = hcsu_ship.cust_acct_site_id
AND hca_ship.party_id = hp_ship.party_id
AND hca_ship.cust_account_id = hcas_ship.cust_account_id
AND hcsu_ship.site_use_code = 'SHIP_TO'
AND hcsu_ship.primary_flag = 'Y'
--Customer Site/Contact/Address Details
SELECT hps.party_site_id,
HP.party_name PARTY_NAME,
hps.party_site_number Site_Number,
hcp.contact_point_type Contact_Type,
hcp.phone_line_type Phone_Line_Type,
hcp.phone_country_code Country_Code,
hcp.phone_number Phone_number,
hcp1.email_address Email_Address,
HCP1.email_format EMAIL_FORMAT,
HCP1.contact_point_purpose CONTACT_POINT_PURPOSE,
HL.address1
||','
||HL.address2
||' '
||HL.address3
||' '
||HL.address4
||' '
||HL.city
||','
||HL.state
||','
||hl.postal_code
||','
||hl.country SITE_ADDRESS
FROM hz_parties hp,
hz_party_sites hps,
hz_contact_points HCP,
hz_contact_points HCP1,
hz_locations hl
WHERE 1 = 1
AND hp.party_id = hps.party_id
AND hcp.owner_table_name = 'HZ_PARTY_SITES'
AND hcp.owner_table_id = hps.party_site_id
AND hcp.contact_point_type = 'PHONE'
AND hcp1.owner_table_name = 'HZ_PARTY_SITES'
AND hcp1.owner_table_id = hps.party_site_id
AND HCP1.contact_point_type = 'EMAIL'
AND hps.location_id = hl.location_id
-- Contact Point/ Person Details
SELECT hcar.cust_acct_site_id,
rel_hp.party_number rel_party_number,
hoc.org_contact_id,
hoc.party_relationship_id,
hoc.comments org_cont_comments,
hoc.contact_number,
hoc.mail_stop,
hpp.person_first_name,
hpp.person_middle_name,
hpp.person_last_name
FROM hz_parties hp,
hz_parties rel_hp,
hz_person_profiles hpp,
hz_relationships hr,
hz_org_contacts hoc,
hz_cust_account_roles hcar
WHERE hoc.party_relationship_id = hr.relationship_id
AND hr.subject_id = hp.party_id
AND rel_hp.party_id = hr.party_id
AND hp.party_id = hpp.party_id(+)
-- AND hpp.content_source_type(+) = user_entered
AND hpp.effective_end_date IS NULL
AND rel_hp.party_id = hcar.party_id(+)
AND hoc.party_relationship_id = hr.relationship_id
AND hr.subject_table_name = 'HZ_PARTIES'
AND hr.subject_type = 'PERSON'
AND hr.relationship_code = 'CONTACT_OF'
AND hcar.cust_account_id = 100000069879253
-- AND hcar.cust_acct_site_id = vl_acct_site_id
--Contact_email Address
(
SELECT Max(cp.email_address)
FROM hz_contact_points cp,
hz_cust_account_roles acct_role
WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id
AND acct_role.relationship_id = cp.relationship_id
AND cp.owner_table_name = 'hz_parties'
AND cp.contact_point_type='email'
AND cp.status ='a'
AND cp.primary_flag = 'y') contact_email
--Site Email Address
SELECT hcsa.party_site_id,
hcp.email_address
FROM hz_cust_accounts hca,
hz_contact_points hcp,
hz_relationships hr,
hz_parties hp,
hz_cust_account_roles hcar,
hz_cust_acct_sites_all hcsa,
hz_party_sites hps
WHERE hcp.relationship_id = hr.relationship_id
AND hr.relationship_code = 'CONTACT_OF'
AND hr.object_id = hp.party_id
AND Nvl(hcp.end_date, sysdate + 5) > sysdate
AND Nvl(HCAR.status, 'A') = 'A'
AND hcar.relationship_id = hcp.relationship_id
AND hp.party_id = hca.party_id
AND hcar.cust_account_id = hca.cust_account_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hcar.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND contact_point_type = 'EMAIL'
--FAX Numnber
SELECT raw_phone_number
FROM hz_contact_points
WHERE owner_table_id = hr.object_id
AND phone_type = 'FAX'
AND Trunc (SYSDATE) BETWEEN Trunc (start_date) AND Trunc (end_date)
--Fax
SELECT hcsa.party_site_id,
hcp.phone_country_code
|| '-'
|| hcp.phone_number FAX
FROM hz_cust_accounts hca,
hz_contact_points hcp,
hz_relationships hr,
hz_parties hp,
hz_cust_account_roles hcar,
hz_cust_acct_sites_all hcsa,
hz_party_sites hps
WHERE hcp.relationship_id = hr.relationship_id
AND hr.relationship_code = 'CONTACT_OF'
AND hr.object_id = hp.party_id
AND Nvl(hcp.end_date, sysdate + 5) > sysdate
AND Nvl(HCAR.status, 'A') = 'A'
AND hcar.relationship_id = hcp.relationship_id
AND hp.party_id = hca.party_id
AND hcar.cust_account_id = hca.cust_account_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hcar.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND contact_point_type = 'PHONE'
AND phone_line_type = 'FAX'
--Contact Mobile Number
(
SELECT Max(cp.raw_phone_number)
FROM hz_contact_points cp,
hz_cust_account_roles acct_role
WHERE acct_role.cust_account_role_id = trx.bill_to_contact_id
AND acct_role.relationship_id = cp.relationship_id
AND cp.owner_table_name = ‘hz_parties’
AND cp.contact_point_type='PHONE'
AND cp.phone_line_type='MOBILE'
AND cp.status ='A'
AND cp.primary_flag = 'Y' ) contact_mobile_ph_number
--Mobile Number
SELECT hcsa.party_site_id,
hcp.phone_country_code
|| '-'
|| hcp.phone_number Mobile
FROM hz_cust_accounts hca,
hz_contact_points hcp,
hz_relationships hr,
hz_parties hp,
hz_cust_account_roles hcar,
hz_cust_acct_sites_all hcsa,
hz_party_sites hps
WHERE hcp.relationship_id = hr.relationship_id
AND hr.relationship_code = 'CONTACT_OF'
AND hr.object_id = hp.party_id
AND Nvl(hcp.end_date, sysdate + 5) > sysdate
AND Nvl(HCAR.status, 'A') = 'A'
AND hcar.relationship_id = hcp.relationship_id
AND hp.party_id = hca.party_id
AND hcar.cust_account_id = hca.cust_account_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hcar.cust_acct_site_id = hcsa.cust_acct_site_id
AND hcsa.party_site_id = hps.party_site_id
AND contact_point_type = 'PHONE'
AND phone_line_type = 'MOBILE'
--Work Phone Number
SELECT raw_phone_number
FROM hz_contact_points
WHERE owner_table_id = hr.object_id
AND phone_type = 'WORK'
AND Trunc (SYSDATE) BETWEEN Trunc (start_date) AND Trunc (end_date)
-- SO /Sales Order /Order /DOO/Shipment/Item Details Query
SELECT dha.order_number,
dla.display_line_number,
dla.unit_selling_price,
dfla.unit_selling_price dfla_unit_selling_price,
dla.ordered_qty ordered_qty1,
dfla.ordered_qty dfla_ordered_qty1,
NULL wsh_shipqty12,
NULL conv_ordered_qty1,
dla.extended_amount,
dfla.extended_amount dfla_extended_amount,
dfla.ordered_uom,
dla.inventory_item_id,
dfla.fulfill_org_id,
dha.source_order_system,
itm.primary_uom_code,
itm.item_number sku,
itm.description,
itm.product_type,
itm.product_family
FROM doo_headers_all dha,
doo_lines_all dla,
doo_fulfill_lines_all dfla,
(
SELECT ecv.category_code,
ev.category_set_id,
ev.category_set_name,
eic.category_id,
eic.inventory_item_id,
eic.organization_id,
eipv.primary_uom_code,
esib.item_number,
item_tl.description,
Substr(category_name, 1, Instr(category_name, '.') - 1) product_type,
Substr(category_name, Instr(category_name, '.') + 1) product_family
FROM egp_item_categories eic,
egp_categories_vl ecv,
egp_category_sets_vl ev,
egp_item_primary_uoms_v eipv,
egp_system_items_b esib,
egp_system_items_tl item_tl
WHERE eic.inventory_item_id = esib.inventory_item_id
AND eic.organization_id = esib.organization_id
AND item_tl.inventory_item_id = esib.inventory_item_id
AND item_tl.organization_id = esib.organization_id
AND eic.category_id = ecv.category_id
AND ev.category_set_id = eic.category_set_id
AND ev.category_set_name = 'Inventory'
AND eipv.inventory_item_id = eic.inventory_item_id
AND item_tl.LANGUAGE = Userenv('LANG')) itm,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE dla.line_id = dfla.line_id
AND dha.header_id = dla.header_id
AND dha.submitted_flag = 'Y'
AND dha.order_number = :p_order_number --'1000002365'
AND dfla.status_code = 'CLOSED'
AND dla.inventory_item_id = itm.inventory_item_id
AND dfla.fulfill_org_id = itm.organization_id
--
AND wnd.delivery_id = wda.delivery_id
AND wdd.source_shipment_id = dfla.fulfill_line_id(+)
AND wda.delivery_detail_id = wdd.delivery_detail_id
ORDER BY dla.display_line_number
--
select f.source_order_number,
h.header_id ,
f.line_id ,
f.fulfill_line_id ,
f.fulfill_line_number,
l.display_line_number,
(l.display_line_number
|| '-'
|| f.fulfill_line_number) exact_line_number,
fd.delivery_name AS shipment_number
FROM doo_fulfill_line_details fd,
doo_fulfill_lines_all f ,
doo_lines_all l ,
doo_headers_all h
WHERE fd.fulfill_line_id =f.fulfill_line_id
AND f.line_id =l.line_id
AND fd.task_type ='Shipment'
AND h.header_id =f.header_id
AND h.header_id =l.header_id
AND h.submitted_flag ='Y'
AND f.source_order_number = :p_order_number
ORDER BY l.line_id
---
SELECT
/* XXXXXXXXXX */
fl.source_order_number,
dha.header_id,
dla.display_line_number,
fl.fulfill_line_id,
dd.delivery_detail_id,
dd.split_from_delivery_detail_id,
fl.status_code,
fl.ordered_qty,
dla.ordered_qty ordered_qty1,
dd.delivery_detail_id delivery_detail_id1,
nd.delivery_name,
fl.fulfillment_split_ref_id,
fl.ordered_qty ordered_qty2,
fl.shipped_qty,
dd.lot_number,
decode (dd.released_status,
'B', 'Backordered',
'C', 'Shipped',
'D', 'Cancelled',
'N', 'Not Ready for Release' ,
'R', 'Ready to Release',
'S', 'Released to Warehouse' ,
'X', 'Not Applicable',
'Y', 'Staged') "Shipping Status"
FROM fusion.doo_headers_all dha,
fusion.doo_fulfill_lines_all fl,
fusion.doo_fulfill_line_details fld,
fusion.doo_lines_all dla,
fusion.wsh_delivery_details dd,
fusion.wsh_delivery_assignments da,
fusion.wsh_new_deliveries nd
WHERE dha.header_id = fl.header_id
AND fl.line_id = dla.line_id
AND fld.task_type (+) = 'Shipment'
AND dha.source_order_number = :p_order_number
AND dha.source_order_number = dd.sales_order_number
AND dd.delivery_detail_id = da.delivery_detail_id (+)
AND nd.delivery_id (+) = da.delivery_id
AND fl.fulfill_line_id = dd.source_shipment_id
AND split_from_delivery_detail_id IS NULL
AND fld.delivery_name = nd.delivery_name (+)
AND fl.fulfill_line_id = fld.fulfill_line_id (+)
UNION
SELECT
/* XXXXXXXXXX */
fl.source_order_number,
dha.header_id,
dla.display_line_number,
fl.fulfill_line_id,
dd.delivery_detail_id,
dd.split_from_delivery_detail_id,
fl.status_code,
fl.ordered_qty ordered_qty2,
dla.ordered_qty,
dd.delivery_detail_id delivery_detail_id1,
nd.delivery_name,
fl.fulfillment_split_ref_id,
fl.ordered_qty ordered_qty1,
fl.shipped_qty,
dd.lot_number,
decode (dd.released_status,
'B', 'Backordered',
'C', 'Shipped',
'D', 'Cancelled',
'N', 'Not Ready for Release' ,
'R', 'Ready to Release',
'S', 'Released to Warehouse' ,
'X', 'Not Applicable',
'Y', 'Staged') "Shipping Status"
FROM fusion.doo_headers_all dha,
fusion.doo_fulfill_lines_all fl,
fusion.doo_fulfill_line_details fld,
fusion.doo_lines_all dla,
fusion.wsh_delivery_details dd,
fusion.wsh_delivery_assignments da,
fusion.wsh_new_deliveries nd
WHERE dha.header_id = fl.header_id
AND fl.line_id = dla.line_id
AND fld.task_type (+) = 'Shipment'
AND dha.source_order_number = :p_order_number
AND dha.source_order_number = dd.sales_order_number
AND dd.delivery_detail_id = da.delivery_detail_id(+)
AND nd.delivery_id (+) = da.delivery_id
AND fl.fulfillment_split_ref_id = dd.source_shipment_id
AND split_from_delivery_detail_id IS NOT NULL
AND fld.delivery_name (+) = nd.delivery_name
AND fl.fulfill_line_id = fld.fulfill_line_id (+)
ORDER BY 1,
3
--------------
SELECT fl.source_order_number,
dha.header_id,
dla.display_line_number,
-- fl.fulfill_line_id,
dd.delivery_detail_id,
dd.split_from_delivery_detail_id,
fl.status_code,
fl.ordered_qty ordered_qty2,
dla.ordered_qty,
dd.shipped_quantity,
dd.delivery_detail_id delivery_detail_id1,
-- nd.delivery_name,
fl.fulfillment_split_ref_id,
fl.ordered_qty ordered_qty1,
fl.shipped_qty,
decode (dd.released_status,
'B', 'Backordered',
'C', 'Shipped',
'D', 'Cancelled',
'N', 'Not Ready for Release' ,
'R', 'Ready to Release',
'S', 'Released to Warehouse' ,
'X', 'Not Applicable',
'Y', 'Staged') "Shipping Status",
fld.*
FROM fusion.doo_headers_all dha,
fusion.doo_fulfill_lines_all fl,
fusion.doo_fulfill_line_details fld,
fusion.doo_lines_all dla,
fusion.wsh_delivery_details dd,
fusion.wsh_delivery_assignments da,
fusion.wsh_new_deliveries nd
WHERE dha.header_id = fl.header_id
AND fl.line_id = dla.line_id
AND fld.task_type = 'Shipment'
AND dha.source_order_number = :p_order_number
AND dha.source_order_number = dd.sales_order_number
AND dd.delivery_detail_id = da.delivery_detail_id (+)
AND nd.delivery_id (+) = da.delivery_id
AND fl.fulfill_line_id = dd.source_shipment_id
AND split_from_delivery_detail_id IS NULL
AND fld.delivery_name = nd.delivery_name (+)
AND fl.fulfill_line_id = fld.fulfill_line_id (+)
--SO/Sales Order with Address details
SELECT hdr.order_number SO_Number,
hou.name Business_Unit,
hdr.status_code,
To_char(hdr.creation_date, 'MM/DD/YYYY') SO_CREATION_DATE,
(SELECT Listagg(party1.party_name, ', ')
within GROUP ( ORDER BY sales.sales_credit_id )
FROM doo_sales_credits sales,
hz_parties party1,
msc_sales_credit_types typ
WHERE 1 = 1
AND sales.salesperson_id = party1.party_id
AND sales.header_id = hdr.header_id
AND typ.sales_credit_type_id = sales.sales_credit_type_id
AND typ.name = 'Quota Sales Credit')sales_Person,
party2.party_name Customer_Name,
acct.account_name Customer_Account,
acct.account_number Acct_Number,
val.meaning SO_Order_Type,
hdr.customer_po_number,
hdr.creation_date,
hdr.ordered_date,
ship_party.party_name ship_to_Customer,
ship_to_loc.address1
||' '
||ship_to_loc.address2
||' '
||ship_to_loc.city
||' '
||ship_to_loc.state ship_to_address
FROM doo_headers_all hdr,
hz_parties party2,
fnd_lookup_values val,
doo_order_addresses address,
doo_order_addresses ship_address,
hz_parties ship_party,
hz_party_sites party_site,
hz_locations ship_to_loc,
hz_cust_accounts acct,
hr_operating_units hou
WHERE 1 = 1
AND hdr.sold_to_party_id = party2.party_id(+)
AND val.lookup_type = 'ORA_DOO_ORDER_TYPES'
AND val.lookup_code = hdr.order_type_code
AND address.header_id(+) = hdr.header_id
AND address.address_use_type(+) = 'BILL_TO'
AND address.cust_acct_id = acct.cust_account_id(+)
AND ship_address.header_id(+) = hdr.header_id
AND ship_address.address_use_type(+) = 'SHIP_TO'
AND ship_party.party_id(+) = ship_address.party_id
AND party_site.party_site_id(+) = ship_address.party_site_id
AND ship_to_loc.location_id(+) = party_site.location_id
AND hou.organization_id = hdr.org_id
AND hdr.object_version_number = (SELECT Max(object_version_number)
FROM doo_headers_all dha_latest
WHERE
dha_latest.order_number = hdr.order_number
AND dha_latest.status_code = hdr.status_code
)
No comments:
Post a Comment