VIEW: APPS.INVFV_CUSTOMER_ITEMS

Source

View Text - Preformatted

SELECT PARTY.PARTY_NAME , CUIT1.CUSTOMER_ITEM_NUMBER , AD.COUNTRY , AD.ADDRESS1 , AD.ADDRESS2 , AD.ADDRESS3 , AD.ADDRESS4 , AD.CITY , AD.POSTAL_CODE , AD.STATE , AD.PROVINCE , AD.COUNTY , LO.MEANING , '_LA:CUIT1.ITEM_DEFINITION_LEVEL:MFG_LOOKUPS:INV_ITEM_DEFINITION_LEVEL:MEANING' , CUIT1.CUSTOMER_ITEM_DESC , CUIT1.MIN_FILL_PERCENTAGE , CUIT1.DEMAND_TOLERANCE_POSITIVE , CUIT1.DEMAND_TOLERANCE_NEGATIVE , CUIT2.CUSTOMER_ITEM_NUMBER , CO.COMMODITY_CODE , AL.NAME , PA.ORGANIZATION_CODE , '_KF:INV:MSTK:SY1' , '_KF:INV:MSTK:SY2' , '_LA:CUIT1.DEP_PLAN_REQUIRED_FLAG:FND_LOOKUPS:YES_NO:MEANING' , '_LA:CUIT1.DEP_PLAN_PRIOR_BLD_FLAG:FND_LOOKUPS:YES_NO:MEANING' , '_LA:CUIT1.INACTIVE_FLAG:FND_LOOKUPS:YES_NO_REVERSE:MEANING' , CUIT1.CUSTOMER_CATEGORY_CODE , CUIT1.CUSTOMER_ITEM_ID , CUIT1.CUSTOMER_ID , CUIT1.ADDRESS_ID , CUIT1.MODEL_CUSTOMER_ITEM_ID , CUIT1.COMMODITY_CODE_ID , CUIT1.CONTAINER_ITEM_ORG_ID , CUIT1.MASTER_CONTAINER_ITEM_ID , CUIT1.DETAIL_CONTAINER_ITEM_ID, CUIT1.LAST_UPDATE_DATE , CUIT1.LAST_UPDATED_BY , CUIT1.CREATION_DATE , CUIT1.CREATED_BY FROM AR_LOOKUPS LO, MTL_PARAMETERS PA, HR_ALL_ORGANIZATION_UNITS AL, MTL_COMMODITY_CODES CO, MTL_CUSTOMER_ITEMS CUIT2, MTL_SYSTEM_ITEMS SY2, MTL_SYSTEM_ITEMS SY1, HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, (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)) AD, MTL_CUSTOMER_ITEMS CUIT1 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND CUIT1.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUIT1.ADDRESS_ID = AD.ADDRESS_ID(+) AND CUIT1.MASTER_CONTAINER_ITEM_ID = SY1.INVENTORY_ITEM_ID(+) AND CUIT1.CONTAINER_ITEM_ORG_ID = SY1.ORGANIZATION_ID(+) AND CUIT1.DETAIL_CONTAINER_ITEM_ID = SY2.INVENTORY_ITEM_ID(+) AND CUIT1.CONTAINER_ITEM_ORG_ID = SY2.ORGANIZATION_ID(+) AND CUIT1.MODEL_CUSTOMER_ITEM_ID = CUIT2.CUSTOMER_ITEM_ID(+) AND CUIT1.COMMODITY_CODE_ID = CO.COMMODITY_CODE_ID(+) AND CUIT1.CONTAINER_ITEM_ORG_ID = AL.ORGANIZATION_ID(+) AND CUIT1.CONTAINER_ITEM_ORG_ID = PA.ORGANIZATION_ID(+) AND CUIT1.CUSTOMER_CATEGORY_CODE = LO.LOOKUP_CODE(+) AND LO.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY' with read only

View Text - HTML Formatted

SELECT PARTY.PARTY_NAME
, CUIT1.CUSTOMER_ITEM_NUMBER
, AD.COUNTRY
, AD.ADDRESS1
, AD.ADDRESS2
, AD.ADDRESS3
, AD.ADDRESS4
, AD.CITY
, AD.POSTAL_CODE
, AD.STATE
, AD.PROVINCE
, AD.COUNTY
, LO.MEANING
, '_LA:CUIT1.ITEM_DEFINITION_LEVEL:MFG_LOOKUPS:INV_ITEM_DEFINITION_LEVEL:MEANING'
, CUIT1.CUSTOMER_ITEM_DESC
, CUIT1.MIN_FILL_PERCENTAGE
, CUIT1.DEMAND_TOLERANCE_POSITIVE
, CUIT1.DEMAND_TOLERANCE_NEGATIVE
, CUIT2.CUSTOMER_ITEM_NUMBER
, CO.COMMODITY_CODE
, AL.NAME
, PA.ORGANIZATION_CODE
, '_KF:INV:MSTK:SY1'
, '_KF:INV:MSTK:SY2'
, '_LA:CUIT1.DEP_PLAN_REQUIRED_FLAG:FND_LOOKUPS:YES_NO:MEANING'
, '_LA:CUIT1.DEP_PLAN_PRIOR_BLD_FLAG:FND_LOOKUPS:YES_NO:MEANING'
, '_LA:CUIT1.INACTIVE_FLAG:FND_LOOKUPS:YES_NO_REVERSE:MEANING'
, CUIT1.CUSTOMER_CATEGORY_CODE
, CUIT1.CUSTOMER_ITEM_ID
, CUIT1.CUSTOMER_ID
, CUIT1.ADDRESS_ID
, CUIT1.MODEL_CUSTOMER_ITEM_ID
, CUIT1.COMMODITY_CODE_ID
, CUIT1.CONTAINER_ITEM_ORG_ID
, CUIT1.MASTER_CONTAINER_ITEM_ID
, CUIT1.DETAIL_CONTAINER_ITEM_ID
, CUIT1.LAST_UPDATE_DATE
, CUIT1.LAST_UPDATED_BY
, CUIT1.CREATION_DATE
, CUIT1.CREATED_BY
FROM AR_LOOKUPS LO
, MTL_PARAMETERS PA
, HR_ALL_ORGANIZATION_UNITS AL
, MTL_COMMODITY_CODES CO
, MTL_CUSTOMER_ITEMS CUIT2
, MTL_SYSTEM_ITEMS SY2
, MTL_SYSTEM_ITEMS SY1
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, (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)) AD
, MTL_CUSTOMER_ITEMS CUIT1
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUIT1.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUIT1.ADDRESS_ID = AD.ADDRESS_ID(+)
AND CUIT1.MASTER_CONTAINER_ITEM_ID = SY1.INVENTORY_ITEM_ID(+)
AND CUIT1.CONTAINER_ITEM_ORG_ID = SY1.ORGANIZATION_ID(+)
AND CUIT1.DETAIL_CONTAINER_ITEM_ID = SY2.INVENTORY_ITEM_ID(+)
AND CUIT1.CONTAINER_ITEM_ORG_ID = SY2.ORGANIZATION_ID(+)
AND CUIT1.MODEL_CUSTOMER_ITEM_ID = CUIT2.CUSTOMER_ITEM_ID(+)
AND CUIT1.COMMODITY_CODE_ID = CO.COMMODITY_CODE_ID(+)
AND CUIT1.CONTAINER_ITEM_ORG_ID = AL.ORGANIZATION_ID(+)
AND CUIT1.CONTAINER_ITEM_ORG_ID = PA.ORGANIZATION_ID(+)
AND CUIT1.CUSTOMER_CATEGORY_CODE = LO.LOOKUP_CODE(+)
AND LO.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY' WITH READ ONLY