SELECT (SELECT NAME
FROM apps.hr_operating_unitsWHERE organization_id = ooh.org_id) sales_org,
(SELECT ood1.organization_name
FROM apps.org_organization_definitions ood1
WHERE ood1.organization_id =
ool.ship_from_org_id)
ship_from_warehouse,
hcs_ship.attribute1 sales_channel,
NVL
(jrs.NAME,
(SELECT c.full_name
FROM apps.per_all_people_f c
WHERE 1 = 1
AND c.employee_number = jrs.salesrep_number
AND TRUNC (SYSDATE) BETWEEN c.effective_start_date
AND c.effective_end_date)
) sales_person,
ooh.flow_status_code header_status, ool.flow_status_code line_status,
ota.order_category_code order_category, ott.NAME order_type_code,
NVL (ott.description, ott.NAME) description, ooh.order_number,
NVL ((SELECT full_name
FROM apps.per_all_people_f a, apps.fnd_user b
WHERE a.person_id = b.employee_id
AND b.user_id = ooh.created_by
AND ROWNUM < 2),
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = ooh.created_by)
) created_by,
ooh.transactional_curr_code currency_code, ooh.fob_point_code,
ooh.freight_terms_code, ooh.cust_po_number, msi.segment1 item_code,
msi.description item_description, msi.weight_uom_code, msi.unit_weight,
CASE
WHEN xxfcc.channel_level1 = 'CONSUMER'
AND xxfcc.channel_level2 IN ('LOC_TT', 'EXP_TT')
THEN 'GT'
WHEN xxfcc.channel_level1 = 'CONSUMER'
AND xxfcc.channel_level2 IN ('LOC_MT', 'EXP_MT')
THEN 'MT'
ELSE xxfcc.channel_level1
END channel_group,
ool.unit_selling_price, ool.line_number, ool.order_quantity_uom,
CASE
WHEN ota.order_category_code = 'RETURN'
THEN ool.ordered_quantity
END return_ordered_quantity,
CASE
WHEN ota.order_category_code = 'RETURN'
THEN ((ool.ordered_quantity * msi.unit_weight) / 1000
)
END return_ordered_wt_mt,
CASE
WHEN ota.order_category_code = 'RETURN'
THEN ool.shipped_quantity
END return_shipped_quantity
FROM ont.oe_order_headers_all ooh,
ont.oe_transaction_types_tl ott,
ont.oe_transaction_types_all ota,
ont.oe_order_lines_all ool,
apps.org_organization_definitions ood,
apps.hr_operating_units hou,
inv.mtl_system_items_b msi,
ar.hz_cust_site_uses_all hcs_ship,
jtf.jtf_rs_salesreps jrs,
ar.hz_cust_acct_sites_all hca_ship,
ar.hz_party_sites hps_ship,
ar.hz_parties hp_ship,
ar.hz_locations hl_ship,
ar.hz_cust_site_uses_all hcs_bill,
ar.hz_cust_acct_sites_all hca_bill,
ar.hz_party_sites hps_bill,
ar.hz_parties hp_bill,
ar.hz_locations hl_bill,
inv.mtl_parameters mp,
apps.xxiff_fin_customer_channel xxfcc
WHERE 1 = 1
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND ott.transaction_type_id = ota.transaction_type_id
AND ott.transaction_type_id = ooh.order_type_id
AND ott.LANGUAGE = 'US'
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hcs_bill.site_use_id = xxfcc.site_use_id(+)
AND msi.inventory_item_id = ool.inventory_item_id
AND msi.organization_id = ood.organization_id
AND ood.organization_id = ooh.org_id
AND hou.organization_id = ood.operating_unit
AND ooh.salesrep_id = jrs.salesrep_id
AND ooh.org_id = jrs.org_id
AND ooh.header_id = ool.header_id
AND hps_bill.party_id = hp_bill.party_id
AND ooh.org_id = fnd_profile.VALUE ('ORG_ID')
--AND trunc(ooh.creation_date)='31-JUL-2013'
--AND ooh.order_number='8880101739'
AND ota.order_category_code = 'RETURN'
AND ool.flow_status_code = 'CLOSED'
AND hps_bill.location_id = hl_bill.location_id
AND UPPER (ott.NAME) NOT LIKE '%IDS%ISO%'
AND mp.organization_id(+) = ooh.ship_from_org_id
No comments:
Post a Comment