VIEW: APPS.MTL_CUSTOMER_ITEMS_ACTIVE_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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