eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_CUSTOMER_ITEM_XREFS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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