View: MTL_CUSTOMER_ITEM_XREFS_V

Product:	INV - Inventory
Description:	10SC ONLY
Implementation/DBA Data:	APPS.MTL_CUSTOMER_ITEM_XREFS_V

View Text

SELECT MCIXRF.ROWID ROW_ID
, MCIXRF.CUSTOMER_ITEM_ID
, MCI.CUSTOMER_ITEM_NUMBER
, MCI.CUSTOMER_ITEM_DESC
, MCI.CUSTOMER_CATEGORY_CODE
, MCI.CUSTOMER_ID
, MCI.ITEM_DEFINITION_LEVEL
, MFL.MEANING ITEM_LEVEL
, HZP.PARTY_NAME CUSTOMER_NAME
, HZC.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HZC.STATUS
, 'CUSTOMER' CUSTOMER_PROSPECT_CODE
, MCI.ADDRESS_ID
, RAD.COUNTRY
, TERR.TERRITORY_SHORT_NAME
, TERR.ADDRESS_STYLE
, RAD.ADDRESS1
, RAD.ADDRESS2
, RAD.ADDRESS3
, RAD.ADDRESS4
, RAD.CITY
, RAD.COUNTY
, RAD.STATE
, RAD.PROVINCE
, RAD.POSTAL_CODE
, ARP_ADDR_PKG.FORMAT_ADDRESS( RAD.ADDRESS_STYLE
, RAD.ADDRESS1
, RAD.ADDRESS2
, RAD.ADDRESS3
, RAD.ADDRESS4
, RAD.CITY
, RAD.COUNTY
, RAD.STATE
, RAD.PROVINCE
, RAD.POSTAL_CODE
, TERR.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS
, MCIXRF.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, MSIT.DESCRIPTION ITEM_DESCRIPTION
, MCIXRF.MASTER_ORGANIZATION_ID
, MCIXRF.PREFERENCE_NUMBER RANK
, MCIXRF.INACTIVE_FLAG
, MCIXRF.LAST_UPDATE_DATE
, MCIXRF.LAST_UPDATED_BY
, MCIXRF.CREATION_DATE
, MCIXRF.CREATED_BY
, MCIXRF.LAST_UPDATE_LOGIN
, MCIXRF.ATTRIBUTE_CATEGORY
, MCIXRF.ATTRIBUTE1
, MCIXRF.ATTRIBUTE2
, MCIXRF.ATTRIBUTE3
, MCIXRF.ATTRIBUTE4
, MCIXRF.ATTRIBUTE5
, MCIXRF.ATTRIBUTE6
, MCIXRF.ATTRIBUTE7
, MCIXRF.ATTRIBUTE8
, MCIXRF.ATTRIBUTE9
, MCIXRF.ATTRIBUTE10
, MCIXRF.ATTRIBUTE11
, MCIXRF.ATTRIBUTE12
, MCIXRF.ATTRIBUTE13
, MCIXRF.ATTRIBUTE14
, MCIXRF.ATTRIBUTE15
, MCIXRF.REQUEST_ID
, MCIXRF.PROGRAM_APPLICATION_ID
, MCIXRF.PROGRAM_ID
, MCIXRF.PROGRAM_UPDATE_DATE
, ARL.LOOKUP_TYPE
, ARL.LOOKUP_CODE
, ARL.MEANING CUSTOMER_CATEGORY
, ARL.DESCRIPTION LKUP_DESCRIPTION
, ARL.ENABLED_FLAG
, ARL.START_DATE_ACTIVE
, ARL.END_DATE_ACTIVE
FROM MTL_CUSTOMER_ITEMS MCI
, MTL_CUSTOMER_ITEM_XREFS MCIXRF
, MTL_SYSTEM_ITEMS_TL MSIT
, MTL_SYSTEM_ITEMS_B_KFV MSI
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZC
, (SELECT LOC.ADDRESS_STYLE
, LOC.COUNTRY
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, ACCT_SITE.STATUS
FROM HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, - 99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, - 99)) RAD
, FND_TERRITORIES_VL TERR
, AR_LOOKUPS ARL
, MFG_LOOKUPS MFL
WHERE MCIXRF.CUSTOMER_ITEM_ID = MCI.CUSTOMER_ITEM_ID
AND MCIXRF.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MCIXRF.MASTER_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MCIXRF.INVENTORY_ITEM_ID = MSIT.INVENTORY_ITEM_ID
AND MCIXRF.MASTER_ORGANIZATION_ID = MSIT.ORGANIZATION_ID
AND MSIT.LANGUAGE = USERENV('LANG')
AND MCI.CUSTOMER_ID = HZC.CUST_ACCOUNT_ID
AND MCI.ADDRESS_ID = RAD.ADDRESS_ID(+)
AND TERR.TERRITORY_CODE(+) = RAD.COUNTRY
AND MCI.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+)
AND ARL.ENABLED_FLAG(+) = 'Y'
AND ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC((ARL.START_DATE_ACTIVE(+)))
, SYSDATE)
AND NVL(TRUNC((ARL.END_DATE_ACTIVE(+)))
, SYSDATE)
AND HZC.STATUS = 'A'
AND RAD.STATUS(+) = 'A'
AND MCI.ITEM_DEFINITION_LEVEL = MFL.LOOKUP_CODE
AND MFL.LOOKUP_TYPE = 'INV_ITEM_DEFINITION_LEVEL'
AND MCI.INACTIVE_FLAG = 'N'
AND HZC.PARTY_ID = HZP.PARTY_ID

Columns