View: MTL_CUSTOMER_ITEMS_ACTIVE_V

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

View Text

SELECT MCI.ROWID ROW_ID
, MCI.CUSTOMER_ITEM_ID
, MCI.LAST_UPDATED_BY
, MCI.LAST_UPDATE_DATE
, MCI.LAST_UPDATE_LOGIN
, MCI.CREATED_BY
, MCI.CREATION_DATE
, MCI.REQUEST_ID
, MCI.PROGRAM_APPLICATION_ID
, MCI.PROGRAM_ID
, MCI.PROGRAM_UPDATE_DATE
, MCI.CUSTOMER_ID
, HZP.PARTY_NAME CUSTOMER_NAME
, HZC.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HZC.STATUS CUSTOMER_STATUS
, 'CUSTOMER' CUSTOMER_PROSPECT_CODE
, MCI.CUSTOMER_CATEGORY_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
, RAD.STATUS ADDRESS_STATUS
, MCI.CUSTOMER_ITEM_NUMBER
, MCI.ITEM_DEFINITION_LEVEL
, MFL.MEANING ITEM_LEVEL
, MCI.CUSTOMER_ITEM_DESC
, MCI.MODEL_CUSTOMER_ITEM_ID
, MCIM.CUSTOMER_ITEM_ID MODEL_ITEM_ID
, MCIM.CUSTOMER_ITEM_NUMBER MODEL_CUSTOMER_ITEM
, MCIM.CUSTOMER_ITEM_DESC MODEL_CUSTOMER_ITEM_DESC
, MCI.COMMODITY_CODE_ID
, MCC.COMMODITY_CODE
, MCC.DESCRIPTION
, MCC.INACTIVE_DATE
, MCI.MASTER_CONTAINER_ITEM_ID
, MCI.CONTAINER_ITEM_ORG_ID
, MCI.DETAIL_CONTAINER_ITEM_ID
, MCI.MIN_FILL_PERCENTAGE
, MCI.DEP_PLAN_REQUIRED_FLAG
, MCI.DEP_PLAN_PRIOR_BLD_FLAG
, MCI.INACTIVE_FLAG
, MCI.ATTRIBUTE_CATEGORY
, MCI.ATTRIBUTE1
, MCI.ATTRIBUTE2
, MCI.ATTRIBUTE3
, MCI.ATTRIBUTE4
, MCI.ATTRIBUTE5
, MCI.ATTRIBUTE6
, MCI.ATTRIBUTE7
, MCI.ATTRIBUTE8
, MCI.ATTRIBUTE9
, MCI.ATTRIBUTE10
, MCI.ATTRIBUTE11
, MCI.ATTRIBUTE12
, MCI.ATTRIBUTE13
, MCI.ATTRIBUTE14
, MCI.ATTRIBUTE15
, MCI.DEMAND_TOLERANCE_POSITIVE
, MCI.DEMAND_TOLERANCE_NEGATIVE
, 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
, 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_LOC_ASSIGNMENTS LOC_ASSIGN
, 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 LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, - 99) = NVL(LOC_ASSIGN.ORG_ID
, - 99)
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
, MTL_COMMODITY_CODES MCC
, MTL_CUSTOMER_ITEMS MCIM
, FND_TERRITORIES_VL TERR
, AR_LOOKUPS ARL
, MFG_LOOKUPS MFL
WHERE MCI.CUSTOMER_ID = HZC.CUST_ACCOUNT_ID
AND MCI.ADDRESS_ID = RAD.ADDRESS_ID(+)
AND MCI.COMMODITY_CODE_ID = MCC.COMMODITY_CODE_ID
AND MCI.MODEL_CUSTOMER_ITEM_ID = MCIM.CUSTOMER_ITEM_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.INACTIVE_FLAG = 'N'
AND NVL(MCC.INACTIVE_DATE
, SYSDATE + 1) > SYSDATE
AND MCI.ITEM_DEFINITION_LEVEL = MFL.LOOKUP_CODE
AND MFL.LOOKUP_TYPE = 'INV_ITEM_DEFINITION_LEVEL'
AND HZC.PARTY_ID = HZP.PARTY_ID

Columns