View: AX_THIRD_PARTIES_SUBS_V

View Text

SELECT 200 APPLICATION_ID
, PV.VENDOR_ID THIRD_PARTY_ID
, PV.VENDOR_NAME THIRD_PARTY_NAME
, SEGMENT1 THIRD_PARTY_NUMBER
, PVS.VENDOR_SITE_ID SUB_ID
, PVS.VENDOR_SITE_CODE SUB_NAME
FROM PO_VENDOR_SITES_ALL PVS
, PO_VENDORS PV
WHERE PVS.VENDOR_ID = PV.VENDOR_ID UNION SELECT 222 APPLICATION_ID
, RC.CUSTOMER_ID THIRD_PARTY_ID
, RC.CUSTOMER_NAME THIRD_PARTY_NAME
, RC.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, 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
, RA_CUSTOMERS RC
, AX_LOOKUPS ATP
WHERE RC.CUSTOMER_ID = RA.CUSTOMER_ID
AND RSU.SITE_USE_CODE = 'BILL_TO'
AND RC.CUSTOMER_ID <> -999
AND RSU.ADDRESS_ID = RA.ADDRESS_ID
AND RC.CUSTOMER_NAME <> ATP.MEANING
AND ATP.LOOKUP_CODE = 'CUSTOMER'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED' UNION SELECT 222 APPLICATION_ID
, -999 THIRD_PARTY_ID
, TP.MEANING THIRD_PARTY_NAME
, TP.MEANING THIRD_PARTY_NUMBER
, -999 SUB_ID
, SUB.MEANING SUB_NAME
FROM AX_LOOKUPS SUB
, AX_LOOKUPS TP
WHERE SUB.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND SUB.LOOKUP_CODE = 'CUSTOMER_SITE'
AND TP.LOOKUP_CODE = 'CUSTOMER'
AND TP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED' UNION SELECT 222 APPLICATION_ID
, RC.CUSTOMER_ID THIRD_PARTY_ID
, RC.CUSTOMER_NAME THIRD_PARTY_NAME
, RC.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, -999 SUB_ID
, ASU.MEANING SUB_NAME
FROM RA_CUSTOMERS RC
, AX_LOOKUPS ASU
, AX_LOOKUPS ATP
WHERE RC.CUSTOMER_ID <> -999
AND ASU.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ASU.LOOKUP_CODE = 'CUSTOMER_SITE'
AND RC.CUSTOMER_NAME <> ATP.MEANING
AND ATP.LOOKUP_CODE = 'CUSTOMER'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED' UNION SELECT 401 APPLICATION_ID
, ASI.ORGANIZATION_ID THIRD_PARTY_ID
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, OD.ORGANIZATION_CODE THIRD_PARTY_NUMBER
, ASI.SECONDARY_INVENTORY_ID SUB_ID
, ASI.SECONDARY_INVENTORY_NAME SUB_NAME
FROM AX_SECONDARY_INVENTORY ASI
, ORG_ORGANIZATION_DEFINITIONS OD
, AX_LOOKUPS AL
WHERE OD.ORGANIZATION_ID = ASI.ORGANIZATION_ID
AND AL.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AL.LOOKUP_CODE = 'INVENTORY_SITE'
AND AL.MEANING <> ASI.SECONDARY_INVENTORY_NAME UNION SELECT 401 APPLICATION_ID
, OD.ORGANIZATION_ID THIRD_PARTY_ID
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, OD.ORGANIZATION_CODE THIRD_PARTY_NUMBER
, 0 SUB_ID
, AL.MEANING SUB_NAME
FROM ORG_ORGANIZATION_DEFINITIONS OD
, AX_LOOKUPS AL
WHERE AL.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AL.LOOKUP_CODE = 'INVENTORY_SITE'

Columns