VIEW: APPS.CSI_INST_CURRENT_LOCATION_V

Source

View Text - Preformatted

SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, HZL.ADDRESS1 ADDRESS1, HZL.ADDRESS2 ADDRESS2, HZL.ADDRESS3 ADDRESS3, HZL.ADDRESS4 ADDRESS4, HZL.CITY CITY, HZL.STATE STATE, HZL.POSTAL_CODE POSTAL_CODE, HZL.COUNTRY COUNTRY, NULL PARTY_SITE_NUMBER, HZL.ADDRESS1 || ' ' || HZL.ADDRESS2 || ' '||HZL.ADDRESS3 || ' '|| HZL.ADDRESS4 ||' '||HZL.CITY || ' '||HZL.STATE || ' '|| HZL.COUNTRY COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, HZ_LOCATIONS HZL WHERE CSII.LOCATION_TYPE_CODE = 'HZ_LOCATIONS' AND CSII.LOCATION_ID = HZL.LOCATION_ID UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, CHA.PARTY_NAME PARTY_NAME, CHA.PARTY_NUMBER PARTY_NUMBER, CHA.ADDRESS1 ADDRESS1, CHA.ADDRESS2 ADDRESS2, CHA.ADDRESS3 ADDRESS3, CHA.ADDRESS4 ADDRESS4, CHA.CITY CITY, CHA.STATE STATE, CHA.ZIP POSTAL_CODE, CHA.COUNTRY COUNTRY, CHA.PARTY_SITE_NUMBER PARTY_SITE_NUMBER, CHA.ADDRESS1 || ' ' || CHA.ADDRESS2 || ' ' || CHA.ADDRESS3 || ' '|| CHA.ADDRESS4 ||' '||CHA.CITY || ' '||CHA.STATE || ' '|| CHA.COUNTRY COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, CSI_HZPTY_ADDRESSES_V CHA WHERE CSII.LOCATION_TYPE_CODE = 'HZ_PARTY_SITES' AND CSII.LOCATION_ID = CHA.LOCATION UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, VS.ADDRESS_LINE1 ADDRESS1, VS.ADDRESS_LINE2 ADDRESS2, VS.ADDRESS_LINE3 ADDRESS3, NULL ADDRESS4, VS.CITY CITY, VS.STATE STATE, VS.ZIP POSTAL_CODE, VS.COUNTRY COUNTRY, NULL PARTY_SITE_NUMBER, VS.ADDRESS_LINE1 || ' ' || VS.ADDRESS_LINE2 || ' '||VS.ADDRESS_LINE3 || ' '|| VS.CITY || ' '||VS.STATE || ' '|| VS.COUNTRY COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, PO_VENDOR_SITES_ALL VS WHERE CSII.LOCATION_TYPE_CODE = 'VENDOR_SITE' AND CSII.LOCATION_ID = VS.VENDOR_SITE_ID UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, B.ORDER_NUMBER || '/' || A.LINE_NUMBER ADDRESS1, NULL ADDRESS2, NULL ADDRESS3, NULL ADDRESS4, NULL CITY, NULL STATE, NULL POSTAL_CODE, NULL COUNTRY, NULL PARTY_SITE_NUMBER, B.ORDER_NUMBER || '/' || A.LINE_NUMBER COMPLETE_ADDRESS FROM OE_ORDER_LINES_ALL A, OE_ORDER_HEADERS_ALL B, CSI_ITEM_INSTANCES CSII WHERE A.LINE_ID = CSII.IN_TRANSIT_ORDER_LINE_ID AND B.HEADER_ID = A.HEADER_ID AND CSII.LOCATION_TYPE_CODE='IN_TRANSIT' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, HR.ADDRESS_LINE_1 ADDRESS1, HR.ADDRESS_LINE_2 ADDRESS2, HR.ADDRESS_LINE_3 ADDRESS3, NULL ADDRESS4, HR.TOWN_OR_CITY CITY, HR.REGION_2 STATE, HR.POSTAL_CODE POSTALCODE, HR.COUNTRY COUNTRY , NULL PARTY_SITE_NUMBER, HR.ADDRESS_LINE_1 || ' ' || HR.ADDRESS_LINE_2 || ' '||HR.ADDRESS_LINE_3 || ' '|| HR.TOWN_OR_CITY || ' '||HR.REGION_2 || ' '|| HR.COUNTRY COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, HR_LOCATIONS HR WHERE CSII.LOCATION_ID = HR.LOCATION_ID AND CSII.LOCATION_TYPE_CODE='INTERNAL_SITE' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, MO.NAME ADDRESS1, CSII.INV_SUBINVENTORY_NAME ADDRESS2, INV_PROJECT.GET_LOCATOR(MILK.INVENTORY_LOCATION_ID, MILK.ORGANIZATION_ID) ADDRESS3, NULL ADDRESS4, NULL CITY, NULL STATE, NULL POSTAL_CODE, NULL COUNTRY, NULL PARTY_SITE_NUMBER, MO.NAME || ' ' ||CSII.INV_SUBINVENTORY_NAME || ' '|| INV_PROJECT.GET_LOCATOR(MILK.INVENTORY_LOCATION_ID, MILK.ORGANIZATION_ID) COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, HR_ALL_ORGANIZATION_UNITS_TL MO, MTL_ITEM_LOCATIONS MILK WHERE CSII.INV_ORGANIZATION_ID = MO.ORGANIZATION_ID AND MILK.INVENTORY_LOCATION_ID(+) = CSII.INV_LOCATOR_ID AND MO.SOURCE_LANG = USERENV('LANG') AND CSII.LOCATION_TYPE_CODE='INVENTORY' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, P.SEGMENT1 || '/'|| T.TASK_NUMBER ADDRESS1, P.NAME ||'/'||T.TASK_NAME ADDRESS2, NULL ADDRESS3, NULL ADDRESS4, NULL CITY, NULL STATE, NULL POSTAL_CODE, NULL COUNTRY, NULL PARTY_SITE_NUMBER, P.SEGMENT1 || '/'|| T.TASK_NUMBER || ' '|| P.NAME ||'/'||T.TASK_NAME COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, PA_PROJECTS_ALL P, PA_TASKS T WHERE P.PROJECT_ID = CSII.PA_PROJECT_ID AND T.TASK_ID = CSII.PA_PROJECT_TASK_ID AND CSII.LOCATION_TYPE_CODE='PROJECT' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID, NULL PARTY_NAME, NULL PARTY_NUMBER, WIP.WIP_ENTITY_NAME ADDRESS1, MO.NAME ADDRESS2, NULL ADDRESS3, NULL ADDRESS4, NULL CITY, NULL STATE, NULL POSTAL_CODE, NULL COUNTRY, NULL PARTY_SITE_NUMBER, WIP.WIP_ENTITY_NAME || ' ' || MO.NAME COMPLETE_ADDRESS FROM CSI_ITEM_INSTANCES CSII, WIP_ENTITIES WIP, HR_ALL_ORGANIZATION_UNITS_TL MO WHERE CSII.WIP_JOB_ID = WIP.WIP_ENTITY_ID AND WIP.ORGANIZATION_ID = MO.ORGANIZATION_ID AND MO.LANGUAGE = USERENV('LANG') AND LOCATION_TYPE_CODE='WIP'

View Text - HTML Formatted

SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, HZL.ADDRESS1 ADDRESS1
, HZL.ADDRESS2 ADDRESS2
, HZL.ADDRESS3 ADDRESS3
, HZL.ADDRESS4 ADDRESS4
, HZL.CITY CITY
, HZL.STATE STATE
, HZL.POSTAL_CODE POSTAL_CODE
, HZL.COUNTRY COUNTRY
, NULL PARTY_SITE_NUMBER
, HZL.ADDRESS1 || ' ' || HZL.ADDRESS2 || ' '||HZL.ADDRESS3 || ' '|| HZL.ADDRESS4 ||' '||HZL.CITY || ' '||HZL.STATE || ' '|| HZL.COUNTRY COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, HZ_LOCATIONS HZL
WHERE CSII.LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
AND CSII.LOCATION_ID = HZL.LOCATION_ID UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, CHA.PARTY_NAME PARTY_NAME
, CHA.PARTY_NUMBER PARTY_NUMBER
, CHA.ADDRESS1 ADDRESS1
, CHA.ADDRESS2 ADDRESS2
, CHA.ADDRESS3 ADDRESS3
, CHA.ADDRESS4 ADDRESS4
, CHA.CITY CITY
, CHA.STATE STATE
, CHA.ZIP POSTAL_CODE
, CHA.COUNTRY COUNTRY
, CHA.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, CHA.ADDRESS1 || ' ' || CHA.ADDRESS2 || ' ' || CHA.ADDRESS3 || ' '|| CHA.ADDRESS4 ||' '||CHA.CITY || ' '||CHA.STATE || ' '|| CHA.COUNTRY COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, CSI_HZPTY_ADDRESSES_V CHA
WHERE CSII.LOCATION_TYPE_CODE = 'HZ_PARTY_SITES'
AND CSII.LOCATION_ID = CHA.LOCATION UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, VS.ADDRESS_LINE1 ADDRESS1
, VS.ADDRESS_LINE2 ADDRESS2
, VS.ADDRESS_LINE3 ADDRESS3
, NULL ADDRESS4
, VS.CITY CITY
, VS.STATE STATE
, VS.ZIP POSTAL_CODE
, VS.COUNTRY COUNTRY
, NULL PARTY_SITE_NUMBER
, VS.ADDRESS_LINE1 || ' ' || VS.ADDRESS_LINE2 || ' '||VS.ADDRESS_LINE3 || ' '|| VS.CITY || ' '||VS.STATE || ' '|| VS.COUNTRY COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, PO_VENDOR_SITES_ALL VS
WHERE CSII.LOCATION_TYPE_CODE = 'VENDOR_SITE'
AND CSII.LOCATION_ID = VS.VENDOR_SITE_ID UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, B.ORDER_NUMBER || '/' || A.LINE_NUMBER ADDRESS1
, NULL ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL STATE
, NULL POSTAL_CODE
, NULL COUNTRY
, NULL PARTY_SITE_NUMBER
, B.ORDER_NUMBER || '/' || A.LINE_NUMBER COMPLETE_ADDRESS
FROM OE_ORDER_LINES_ALL A
, OE_ORDER_HEADERS_ALL B
, CSI_ITEM_INSTANCES CSII
WHERE A.LINE_ID = CSII.IN_TRANSIT_ORDER_LINE_ID
AND B.HEADER_ID = A.HEADER_ID
AND CSII.LOCATION_TYPE_CODE='IN_TRANSIT' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, HR.ADDRESS_LINE_1 ADDRESS1
, HR.ADDRESS_LINE_2 ADDRESS2
, HR.ADDRESS_LINE_3 ADDRESS3
, NULL ADDRESS4
, HR.TOWN_OR_CITY CITY
, HR.REGION_2 STATE
, HR.POSTAL_CODE POSTALCODE
, HR.COUNTRY COUNTRY
, NULL PARTY_SITE_NUMBER
, HR.ADDRESS_LINE_1 || ' ' || HR.ADDRESS_LINE_2 || ' '||HR.ADDRESS_LINE_3 || ' '|| HR.TOWN_OR_CITY || ' '||HR.REGION_2 || ' '|| HR.COUNTRY COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, HR_LOCATIONS HR
WHERE CSII.LOCATION_ID = HR.LOCATION_ID
AND CSII.LOCATION_TYPE_CODE='INTERNAL_SITE' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, MO.NAME ADDRESS1
, CSII.INV_SUBINVENTORY_NAME ADDRESS2
, INV_PROJECT.GET_LOCATOR(MILK.INVENTORY_LOCATION_ID
, MILK.ORGANIZATION_ID) ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL STATE
, NULL POSTAL_CODE
, NULL COUNTRY
, NULL PARTY_SITE_NUMBER
, MO.NAME || ' ' ||CSII.INV_SUBINVENTORY_NAME || ' '|| INV_PROJECT.GET_LOCATOR(MILK.INVENTORY_LOCATION_ID
, MILK.ORGANIZATION_ID) COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, HR_ALL_ORGANIZATION_UNITS_TL MO
, MTL_ITEM_LOCATIONS MILK
WHERE CSII.INV_ORGANIZATION_ID = MO.ORGANIZATION_ID
AND MILK.INVENTORY_LOCATION_ID(+) = CSII.INV_LOCATOR_ID
AND MO.SOURCE_LANG = USERENV('LANG')
AND CSII.LOCATION_TYPE_CODE='INVENTORY' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, P.SEGMENT1 || '/'|| T.TASK_NUMBER ADDRESS1
, P.NAME ||'/'||T.TASK_NAME ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL STATE
, NULL POSTAL_CODE
, NULL COUNTRY
, NULL PARTY_SITE_NUMBER
, P.SEGMENT1 || '/'|| T.TASK_NUMBER || ' '|| P.NAME ||'/'||T.TASK_NAME COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, PA_PROJECTS_ALL P
, PA_TASKS T
WHERE P.PROJECT_ID = CSII.PA_PROJECT_ID
AND T.TASK_ID = CSII.PA_PROJECT_TASK_ID
AND CSII.LOCATION_TYPE_CODE='PROJECT' UNION ALL SELECT CSII.INSTANCE_ID INSTANCE_ID
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, WIP.WIP_ENTITY_NAME ADDRESS1
, MO.NAME ADDRESS2
, NULL ADDRESS3
, NULL ADDRESS4
, NULL CITY
, NULL STATE
, NULL POSTAL_CODE
, NULL COUNTRY
, NULL PARTY_SITE_NUMBER
, WIP.WIP_ENTITY_NAME || ' ' || MO.NAME COMPLETE_ADDRESS
FROM CSI_ITEM_INSTANCES CSII
, WIP_ENTITIES WIP
, HR_ALL_ORGANIZATION_UNITS_TL MO
WHERE CSII.WIP_JOB_ID = WIP.WIP_ENTITY_ID
AND WIP.ORGANIZATION_ID = MO.ORGANIZATION_ID
AND MO.LANGUAGE = USERENV('LANG')
AND LOCATION_TYPE_CODE='WIP'