View: AX_SUBS_V

View Text

SELECT 200 APPLICATION_ID
, VENDOR_ID THIRD_PARTY_ID
, VENDOR_SITE_ID SUB_ID
, VENDOR_SITE_CODE SUB_NAME
FROM PO_VENDOR_SITES_ALL UNION SELECT 222 APPLICATION_ID
, RA.CUSTOMER_ID THIRD_PARTY_ID
, RSU.SITE_USE_ID SUB_ID
, RA.ADDRESS1 ||' '|| RA.ADDRESS2 ||' '|| RA.ADDRESS3 ||' '|| RA.ADDRESS4 ||' '|| RA.CITY ||' '|| NVL(RA.STATE
, RA.PROVINCE)||' '|| RA.POSTAL_CODE SUB_NAME
FROM RA_ADDRESSES_ALL RA
, RA_SITE_USES_ALL RSU
WHERE RSU.SITE_USE_CODE = 'BILL_TO'
AND RA.CUSTOMER_ID <> -999
AND RSU.ADDRESS_ID = RA.ADDRESS_ID UNION SELECT 222 APPLICATION_ID
, -999 THIRD_PARTY_ID
, -999 SUB_ID
, MEANING SUB_NAME
FROM AX_LOOKUPS
WHERE LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND LOOKUP_CODE = 'CUSTOMER_SITE' UNION SELECT 222 APPLICATION_ID
, RC.CUSTOMER_ID THIRD_PARTY_ID
, -999 SUB_ID
, AL.MEANING SUB_NAME
FROM RA_CUSTOMERS RC
, AX_LOOKUPS AL
WHERE RC.CUSTOMER_ID <> -999
AND AL.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AL.LOOKUP_CODE = 'CUSTOMER_SITE' UNION SELECT 401 APPLICATION_ID
, ASI.ORGANIZATION_ID THIRD_PARTY_ID
, ASI.SECONDARY_INVENTORY_ID SUB_ID
, ASI.SECONDARY_INVENTORY_NAME SUB_NAME
FROM AX_SECONDARY_INVENTORY ASI
, MTL_PARAMETERS MTP
WHERE MTP.ORGANIZATION_ID = ASI.ORGANIZATION_ID
AND MTP.PRIMARY_COST_METHOD <> 2 UNION SELECT 401 APPLICATION_ID
, MTP.ORGANIZATION_ID THIRD_PARTY_ID
, CCG.COST_GROUP_ID SUB_ID
, CCG.COST_GROUP SUB_NAME
FROM CST_COST_GROUPS CCG
, MTL_PARAMETERS MTP
WHERE MTP.ORGANIZATION_ID = NVL(CCG.ORGANIZATION_ID
, MTP.ORGANIZATION_ID)
AND MTP.PRIMARY_COST_METHOD = 2 UNION SELECT 401 APPLICATION_ID
, OOD.ORGANIZATION_ID THIRD_PARTY_ID
, 0 SUB_ID
, AL.MEANING SUB_NAME
FROM ORG_ORGANIZATION_DEFINITIONS OOD
, AX_LOOKUPS AL
WHERE AL.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AL.LOOKUP_CODE = 'INVENTORY_SITE'
AND OOD.ORGANIZATION_ID <> 0 UNION SELECT 401 APPLICATION_ID
, 0 THIRD_PARTY_ID
, 0 SUB_ID
, MEANING SUB_NAME
FROM AX_LOOKUPS
WHERE LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND LOOKUP_CODE = 'INVENTORY_SITE'

Columns