SELECT a.*
FROM (SELECT (SELECT segment1
FROM apps.mtl_system_items_b
WHERE inventory_item_id = CIS.inventory_item_id
AND organization_id = CIS.inv_master_organization_id)
"Item",
(SELECT description
FROM apps.mtl_system_items_b
WHERE inventory_item_id = CIS.inventory_item_id
AND organization_id = CIS.inv_master_organization_id)
"Item Description",
INSTANCE_NUMBER "Item Instance #",
cis.serial_number "Serial Number",
cis.EXTERNAL_REFERENCE "External Reference",
(SELECT NAME
FROM APPS.CSI_INSTANCE_STATUSES
WHERE INSTANCE_STATUS_ID = CIS.instance_status_id)
"Status",
(SELECT TO_CHAR (order_number)
FROM apps.oe_order_headers_all b, apps.oe_order_lines_all c
WHERE b.header_id = c.header_id
AND line_id = CIS.last_oe_order_line_id)
"Sales Order Number",
(SELECT b.cust_po_number
FROM apps.oe_order_headers_all b, apps.oe_order_lines_all c
WHERE b.header_id = c.header_id
AND line_id = CIS.last_oe_order_line_id)
"Purchase Order Number",
(SELECT TO_CHAR (c.actual_shipment_date,
'DD-MON-YYYY HH24:MI:SS')
FROM apps.oe_order_headers_all b, apps.oe_order_lines_all c
WHERE b.header_id = c.header_id
AND line_id = CIS.last_oe_order_line_id)
"Shipped On Date",
(SELECT party_name
FROM apps.hz_parties
WHERE party_id = CIS.owner_party_id)
"Owner Party Name",
(SELECT party_number
FROM apps.hz_parties
WHERE party_id = CIS.owner_party_id)
"Owner Party Number",
(CASE
WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
THEN
(SELECT ADDRESS1
FROM apps.hz_locations
WHERE LOCATION_ID =
( (SELECT hl1.LOCATION_ID
FROM apps.hz_party_sites hps1,
apps.hz_locations hl1
WHERE hps1.party_site_id =
hps.party_site_id
AND hps1.party_site_id =
cis.install_location_id
AND hps1.location_id =
hl1.location_id)))
WHEN CIS.location_type_code = 'HZ_LOCATIONS'
THEN
(SELECT ADDRESS1
FROM apps.hz_locations
WHERE location_id = cis.install_location_id)
ELSE
NULL
END)
"Installed At Line1",
(CASE
WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
THEN
(SELECT ADDRESS2
FROM apps.hz_locations
WHERE LOCATION_ID IN
( (SELECT hl1.LOCATION_ID
FROM apps.hz_party_sites hps1,
apps.hz_locations hl1
WHERE hps1.party_site_id =
hps.party_site_id
AND hps1.party_site_id =
cis.install_location_id
AND hps1.location_id =
hl1.location_id)))
WHEN CIS.location_type_code = 'HZ_LOCATIONS'
THEN
(SELECT ADDRESS2
FROM apps.hz_locations
WHERE location_id = cis.install_location_id)
ELSE
NULL
END)
"Installed At Line2",
(CASE
WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
THEN
(SELECT ADDRESS3
FROM apps.hz_locations
WHERE LOCATION_ID IN
( (SELECT hl1.LOCATION_ID
FROM apps.hz_party_sites hps1,
apps.hz_locations hl1
WHERE hps1.party_site_id =
hps.party_site_id
AND hps1.party_site_id =
cis.install_location_id
AND hps1.location_id =
hl1.location_id)))
WHEN CIS.location_type_code = 'HZ_LOCATIONS'
THEN
(SELECT ADDRESS3
FROM apps.hz_locations
WHERE location_id = cis.install_location_id)
ELSE
NULL
END)
"Installed At Line3",
(CASE
WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
THEN
(SELECT city
FROM apps.hz_locations
WHERE LOCATION_ID IN
( (SELECT hl1.LOCATION_ID
FROM apps.hz_party_sites hps1,
apps.hz_locations hl1
WHERE hps1.party_site_id =
hps.party_site_id
AND hps1.party_site_id =
cis.install_location_id
AND hps1.location_id =
hl1.location_id)))
WHEN CIS.location_type_code = 'HZ_LOCATIONS'
THEN
(SELECT CITY
FROM apps.hz_locations
WHERE location_id = cis.install_location_id)
ELSE
NULL
END)
"City",
(CASE
WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
THEN
(SELECT POSTAL_CODE
FROM apps.hz_locations
WHERE LOCATION_ID IN
( (SELECT hl1.LOCATION_ID
FROM apps.hz_party_sites hps1,
apps.hz_locations hl1
WHERE hps1.party_site_id =
hps.party_site_id
AND hps1.party_site_id =
cis.install_location_id
AND hps1.location_id =
hl1.location_id)))
WHEN CIS.location_type_code = 'HZ_LOCATIONS'
THEN
(SELECT postal_code
FROM apps.hz_locations
WHERE location_id = cis.install_location_id)
ELSE
NULL
END)
"Postal Code",
(CASE
WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
THEN
(SELECT country
FROM apps.hz_locations
WHERE LOCATION_ID IN
( (SELECT hl1.LOCATION_ID
FROM apps.hz_party_sites hps1,
apps.hz_locations hl1
WHERE hps1.party_site_id =
hps.party_site_id
AND hps1.party_site_id =
cis.install_location_id
AND hps1.location_id =
hl1.location_id)))
WHEN CIS.location_type_code = 'HZ_LOCATIONS'
THEN
(SELECT country
FROM apps.hZ_locations
WHERE location_id = cis.install_location_id)
ELSE
NULL
END)
"Country",
HROU.NAME HROU_NAME
FROM apps.oe_order_lines_all ool,
apps.hz_locations hzl,
apps.hz_cust_site_uses_all hcsua,
apps.hz_party_sites hps,
apps.hz_cust_acct_sites_all hcasa,
apps.fnd_territories fndt,
apps.fnd_territories_tl fndtl,
apps.csi_item_instances cis,
apps.hr_operating_units hrou
WHERE hzl.location_id = hps.location_id
AND hcsua.site_use_code = 'SHIP_TO'
AND hps.party_site_id = hcasa.party_site_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND ool.line_id = cis.last_oe_order_line_id
AND ool.ship_to_org_id = hcsua.site_use_id
AND fndtl.territory_code = fndt.territory_code
AND fndt.territory_code = hzl.country
AND hrou.organization_ID = OOL.ORG_ID(+)
AND EXISTS
(SELECT inventory_item_ID
FROM apps.mtl_system_items_b msib
WHERE organization_ID IN
(SELECT ORGANIZATION_ID
FROM apps.hr_operating_units hrou
WHERE hrou.name LIKE
'Brazil ')
AND msib.inventory_item_id =
cis.inventory_item_id)
AND TERRITORY_SHORT_NAME IN
('Armenia',
'Australia',
)) A
No comments:
Post a Comment