Order Management:
Headers:
=======
select
order_number,
ot.name,
h.ordered_date,
qlp.name price_name,
h.flow_status_code,
f.CURRENCY_CODE,
j.name saname,
hp.party_name,
hp.party_number,
h.CUST_PO_NUMBER,
hcsu.LOCATION,
hl.ADDRESS1,
(DECODE (hl.city,
NULL, NULL,
hl.city || ', '
)
|| DECODE (hl.state,
NULL, hl.province
|| ', ',
hl.state || ', '
)
|| DECODE (hl.postal_code,
NULL, NULL,
hl.postal_code || ', '
)
|| DECODE (hl.country, NULL, NULL, hl.country))
ship_to_address5,
hcs1.LOCATION
bill_to,hl1.ADDRESS1,
DECODE (hl1.city,
NULL, NULL,
hl1.city || ', '
)
|| DECODE (hl1.state,
NULL, hl1.province
|| ', ',
hl1.state || ', '
)
|| DECODE (hl1.postal_code,
NULL, NULL,
hl1.postal_code || ', '
)
|| DECODE (hl1.country, NULL, NULL, hl1.country)
invoice_to_address5
from
oe_order_headers_all h,
oe_transaction_types_tl ot,
qp_list_headers_tl qlp,
fnd_currencies f,
JTF_RS_SALESREPS j,
hz_parties hp,
hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_locations hl,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca,
-----------------------------------
hz_cust_site_uses_all hcs1,
hz_cust_acct_sites_all hcas1,
hz_party_sites hps1,
hz_locations hl1
where
order_number='50002'
and ot.TRANSACTION_TYPE_ID=h.ORDER_TYPE_ID
and qlp.list_header_id=h.price_list_id
AND h.transactional_curr_code =
f.currency_code
and j.SALESREP_ID=h.SALESREP_ID
and j.org_id=204
AND h.ship_to_org_id = hcsu.site_use_id
and hcsu.SITE_USE_CODE='SHIP_TO'
and hps.LOCATION_ID=hl.location_id
and hps.party_id=hp.PARTY_ID
and hcas.party_site_id=hps.party_site_id
and hca.CUST_ACCOUNT_ID=hcas.CUST_ACCOUNT_ID
and hcsu.CUST_ACCT_SITE_ID=hcas.CUST_ACCT_SITE_ID
AND h.sold_to_org_id = HCA.cust_account_id(+)
and hcs1.site_use_id=h.invoice_to_org_id
AND hcs1.cust_acct_site_id=hcas1.cust_acct_site_id
AND hcas1.party_site_id=hps1.party_site_id
and hcs1.SITE_USE_CODE='BILL_TO'
and hl1.location_id=hps1.location_id
Lines:
select msib.segment1,
ool.PRICING_QUANTITY,
mp.ORGANIZATION_CODE,
ool.SOURCE_TYPE_CODE,
ool.SCHEDULE_SHIP_DATE,
ool.SCHEDULE_ARRIVAL_DATE,
ool.REQUEST_DATE,
ool.PROMISE_DATE,
ool.SHIPPED_QUANTITY,
ool.SHIPPING_METHOD_CODE
,
(select MEANING from apps.oe_lookups
where upper (lookup_type) = 'FREIGHT_TERMS' and enabled_flag = 'Y'
and upper (lookup_code) = upper (ool.freight_terms_code))FREIGH_TTERMS,
(select wsh.ship_method_code from wsh.wsh_carrier_services wsh
where wsh.ship_method_code = ool.shipping_method_code)method_code,
(SELECT
NVL(hcasa.translated_customer_name, hp.party_name) Customer_Name
FROM
hz_cust_site_uses_all
hcsua
,hz_cust_acct_sites_all hcasa
,hz_parties hp
,hz_cust_accounts hca
,hz_party_sites hps
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND ool.ship_to_org_id = hcsua.site_use_id(+)
AND hcsua.cust_acct_site_id= hcasa.cust_acct_site_id(+)
AND ool.sold_to_org_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND hcasa.party_site_id = hps.party_site_id(+)) ship_to_customer_name,
(select hcs.location
from
hz_cust_site_uses_all
hcs,
apps.hz_cust_acct_sites_all hca,
hz_party_sites hps
where site_use_id=ool.ship_to_org_id
and hca.cust_acct_site_id=hcs.cust_acct_site_id
and hps.party_site_id=hca.party_site_id
and hcs.SITE_USE_CODE='SHIP_TO')ship_to_location,
(select
ship_party.person_last_name
|| DECODE (ship_party.person_first_name,
NULL, NULL,
', ' || ship_party.person_first_name
)
|| DECODE (ship_arl.meaning, NULL, NULL, ' ' || ship_arl.meaning)ship_to_contact
from
hz_cust_account_roles ship_roles,
hz_relationships ship_rel,
hz_parties ship_party,
hz_cust_accounts ship_acct,
ar_lookups ship_arl
where
ool.ship_to_contact_id = ship_roles.cust_account_role_id(+)
AND ship_roles.party_id = ship_rel.party_id(+)
AND ship_roles.role_type(+) = 'CONTACT'
AND ship_rel.subject_id = ship_party.party_id(+)
AND ship_roles.cust_account_id = ship_acct.cust_account_id(+)
AND NVL (ship_rel.object_id, -1) = NVL (ship_acct.party_id, -1)
AND ship_arl.lookup_type(+) = 'CONTACT_TITLE'
AND ship_arl.lookup_code(+) = ship_party.person_pre_name_adjunct)ship_to_contact_name,
(
select
invoice_party.person_last_name
|| DECODE (invoice_party.person_first_name,
NULL, NULL,
', ' || invoice_party.person_first_name
)
|| DECODE (invoice_arl.meaning,
NULL, NULL,
' ' || invoice_arl.meaning
) invoice_to_contact
from
hz_cust_account_roles invoice_roles,
hz_relationships invoice_rel,
hz_parties invoice_party,
hz_cust_accounts invoice_acct,
ar_lookups invoice_arl
where
ool.invoice_to_contact_id = invoice_roles.cust_account_role_id(+)
AND invoice_roles.party_id = invoice_rel.party_id(+)
AND invoice_roles.role_type(+) = 'CONTACT'
AND invoice_rel.subject_id = invoice_party.party_id(+)
AND invoice_roles.cust_account_id = invoice_acct.cust_account_id(+)
AND NVL (invoice_rel.object_id, -1) = NVL (invoice_acct.party_id, -1)
AND invoice_arl.lookup_type(+) = 'CONTACT_TITLE'
AND invoice_arl.lookup_code(+) = invoice_party.person_pre_name_adjunct)bill_to_contact,
(select hcs.location
from
hz_cust_site_uses_all
hcs,
apps.hz_cust_acct_sites_all hca,
hz_party_sites hps
where site_use_id=ool.invoice_to_org_id
and hca.cust_acct_site_id=hcs.cust_acct_site_id
and hps.party_site_id=hca.party_site_id
and hcs.SITE_USE_CODE='BILL_TO')bill_to_location,
(select hp.party_name
from
apps.hz_cust_accounts hca,
apps.hz_parties hp
where ool.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id)Bill_to_customer_name
from mtl_system_items_b msib,
oe_order_lines_all ool,
mtl_parameters mp
where header_id=67871
and ool.INVENTORY_ITEM_ID=msib.INVENTORY_ITEM_ID
and org_id=msib.organization_id
and org_id=889
and ool.SHIP_FROM_ORG_ID=mp.ORGANIZATION_ID
No comments:
Post a Comment