[Home] [Help]
View: AX_ORGS_SUBS_V
| Product: | AX - Global Accounting Engine |
|---|
| Description: | ID, Name, and Org ID of the 3rd Party Subidentifiers |
|---|
| Implementation/DBA Data: |
Not implemented in this database
|
|---|
View Text
SELECT 200 APPLICATION_ID
, PVSSA.VENDOR_ID THIRD_PARTY_ID
, PVSSA.VENDOR_SITE_ID SUB_ID
, PVSSA.VENDOR_SITE_CODE SUB_NAME
, PVSSA.ORG_ID ORG_ID
, HOU.NAME ORG_NAME
FROM HR_ORGANIZATION_UNITS HOU
, PO_VENDOR_SITES_ALL PVSSA
WHERE PVSSA.ORG_ID = HOU.ORGANIZATION_ID(+) 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
, RSU.ORG_ID ORG_ID
, HOU.NAME ORG_NAME
FROM HR_ORGANIZATION_UNITS HOU
, 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
AND RSU.ORG_ID = HOU.ORGANIZATION_ID(+) UNION SELECT 222 APPLICATION_ID
, -999 THIRD_PARTY_ID
, -999 SUB_ID
, MEANING SUB_NAME
, TO_NUMBER(NULL) ORG_ID
, NULL ORG_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
, TO_NUMBER(NULL) ORG_ID
, NULL ORG_NAME
FROM RA_CUSTOMERS RC
, AX_LOOKUPS AL
WHERE RC.CUSTOMER_ID <> -999
AND LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND 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
, ASI.ORGANIZATION_ID ORG_ID
, HOU.NAME ORG_NAME
FROM HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MTL
, AX_SECONDARY_INVENTORY ASI
WHERE ASI.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
AND ASI.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND MTL.PRIMARY_COST_METHOD <> 2 UNION SELECT 401 APPLICATION_ID
, NVL(CST.ORGANIZATION_ID
, MTL.ORGANIZATION_ID) THIRD_PARTY_ID
, CST.COST_GROUP_ID SUB_ID
, CST.COST_GROUP SUB_NAME
, NVL(CST.ORGANIZATION_ID
, MTL.ORGANIZATION_ID) ORG_ID
, HOU.NAME ORG_NAME
FROM HR_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MTL
, CST_COST_GROUPS CST
WHERE CST.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
AND MTL.ORGANIZATION_ID = NVL(CST.ORGANIZATION_ID
, MTL.ORGANIZATION_ID)
Columns
| Name |
| APPLICATION_ID |
| THIRD_PARTY_ID |
| SUB_ID |
| SUB_NAME |
| ORG_ID |
| ORG_NAME |