VIEW: APPS.PO_SHIP_TO_LOC_ORG_V

Source

View Text - Preformatted

SELECT TL. LOCATION_CODE , OOD.ORGANIZATION_CODE ||'-'|| OOD.ORGANIZATION_NAME ORG_NAME , L.ADDRESS_LINE_1 , L.TOWN_OR_CITY , DECODE(L.STYLE,'US',L.REGION_2,L.REGION_1) STATE , L.TAX_NAME , L.LOCATION_ID , OOD.ORGANIZATION_ID ,TL.DESCRIPTION , L.SHIP_TO_LOCATION_ID , L.INVENTORY_ORGANIZATION_ID , OOD.ORGANIZATION_NAME , OOD.ORGANIZATION_CODE , OOD.SET_OF_BOOKS_ID , L.INACTIVE_DATE , L.SHIP_TO_SITE_FLAG FROM ORG_ORGANIZATION_DEFINITIONS OOD, HR_LOCATIONS_ALL_TL TL, HR_LOCATIONS_ALL L where L.LOCATION_ID = TL.LOCATION_ID AND TL.LANGUAGE = USERENV ('LANG') AND NVL(L.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) AND SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1) AND NVL(L.SHIP_TO_SITE_FLAG,'N') = 'Y' AND L.INVENTORY_ORGANIZATION_ID = OOD.ORGANIZATION_ID union select TL.LOCATION_CODE , NULL ORG_NAME , L.ADDRESS_LINE_1 , L.TOWN_OR_CITY , DECODE(L.STYLE,'US',L.REGION_2,L.REGION_1) STATE , L.TAX_NAME , L.LOCATION_ID , to_number(null), TL.DESCRIPTION , L.SHIP_TO_LOCATION_ID , L.INVENTORY_ORGANIZATION_ID , null, null, to_number(null), L.INACTIVE_DATE , L.SHIP_TO_SITE_FLAG from HR_LOCATIONS_ALL_TL TL, HR_LOCATIONS_ALL L where L.LOCATION_ID = TL.LOCATION_ID AND TL.LANGUAGE = USERENV ('LANG') AND NVL(L.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) AND SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1) AND NVL(L.SHIP_TO_SITE_FLAG,'N') = 'Y' AND L.INVENTORY_ORGANIZATION_ID is null

View Text - HTML Formatted

SELECT TL. LOCATION_CODE
, OOD.ORGANIZATION_CODE ||'-'|| OOD.ORGANIZATION_NAME ORG_NAME
, L.ADDRESS_LINE_1
, L.TOWN_OR_CITY
, DECODE(L.STYLE
, 'US'
, L.REGION_2
, L.REGION_1) STATE
, L.TAX_NAME
, L.LOCATION_ID
, OOD.ORGANIZATION_ID
, TL.DESCRIPTION
, L.SHIP_TO_LOCATION_ID
, L.INVENTORY_ORGANIZATION_ID
, OOD.ORGANIZATION_NAME
, OOD.ORGANIZATION_CODE
, OOD.SET_OF_BOOKS_ID
, L.INACTIVE_DATE
, L.SHIP_TO_SITE_FLAG
FROM ORG_ORGANIZATION_DEFINITIONS OOD
, HR_LOCATIONS_ALL_TL TL
, HR_LOCATIONS_ALL L
WHERE L.LOCATION_ID = TL.LOCATION_ID
AND TL.LANGUAGE = USERENV ('LANG')
AND NVL(L.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)
AND SYSDATE < NVL(L.INACTIVE_DATE
, SYSDATE + 1)
AND NVL(L.SHIP_TO_SITE_FLAG
, 'N') = 'Y'
AND L.INVENTORY_ORGANIZATION_ID = OOD.ORGANIZATION_ID UNION SELECT TL.LOCATION_CODE
, NULL ORG_NAME
, L.ADDRESS_LINE_1
, L.TOWN_OR_CITY
, DECODE(L.STYLE
, 'US'
, L.REGION_2
, L.REGION_1) STATE
, L.TAX_NAME
, L.LOCATION_ID
, TO_NUMBER(NULL)
, TL.DESCRIPTION
, L.SHIP_TO_LOCATION_ID
, L.INVENTORY_ORGANIZATION_ID
, NULL
, NULL
, TO_NUMBER(NULL)
, L.INACTIVE_DATE
, L.SHIP_TO_SITE_FLAG
FROM HR_LOCATIONS_ALL_TL TL
, HR_LOCATIONS_ALL L
WHERE L.LOCATION_ID = TL.LOCATION_ID
AND TL.LANGUAGE = USERENV ('LANG')
AND NVL(L.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)
AND SYSDATE < NVL(L.INACTIVE_DATE
, SYSDATE + 1)
AND NVL(L.SHIP_TO_SITE_FLAG
, 'N') = 'Y'
AND L.INVENTORY_ORGANIZATION_ID IS NULL