[Home] [Help]
| Product: | BOM - Bills of Material |
|---|---|
| Description: | Left hand side of constraint clauses |
| Implementation/DBA Data: | Not implemented in this database |
SELECT DISTINCT CCA.ROWID ROW_ID
, CCA.ASSIGNMENT_ID ASSIGNMENT_ID
, CCA.CONSTRAINT_ID CONSTRAINT_ID
, CCA.INVENTORY_ITEM_ID ASSIGNMENT_ITEM_ID
, CCA.ORGANIZATION_ID ORGANIZATION_ID
, BE.SORT_ORDER SORT_ORDER
, CCA.EFFECTIVITY_DATE EFFECTIVITY_DATE
, CCA.DISABLE_DATE DISABLE_DATE
, CCC.CLAUSE_ID CLAUSE_ID
, CCC.NAME CLAUSE_NAME
, CCC.ATTRIBUTE_CODE1 LHS_ATTRIBUTE_CODE
, AAV.NAME LHS_ATTRIBUTE_NAME
, AAV.DESCRIPTION LHS_ATTRIBUTE_DESCRIPTION
, MSI.INVENTORY_ITEM_ID ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NAME
, MSI.DESCRIPTION ITEM_DESCRIPTION
, DECODE(AAV.DATA_TYPE
, 'VARCHAR2'
, AIAV.VALUE_VARCHAR2
, 'NUMBER'
, AIAV.VALUE_NUMBER
, AIAV.VALUE_DATE) LHS_ATTRIBUTE_VALUE
FROM MTL_SYSTEM_ITEMS_KFV MSI
, AK_INST_ATTRIBUTE_VALUES AIAV
, BOM_EXPLOSIONS BE
, BOM_BILL_OF_MATERIALS BOM
, AK_ATTRIBUTES_VL AAV
, AK_OBJECT_ATTRIBUTES AOA
, CZ_CONSTRAINT_CLAUSES CCC
, CZ_CONSTRAINT_ASSIGNMENTS CCA
WHERE CCC.CONSTRAINT_ID = CCA.CONSTRAINT_ID
AND AOA.ATTRIBUTE_CODE = CCC.ATTRIBUTE_CODE1
AND CCC.DB_OBJECT_NAME1 = 'MTL_SYSTEM_ITEMS_KFV'
AND AOA.ATTRIBUTE_APPLICATION_ID = 702
AND AOA.DATABASE_OBJECT_NAME = CCC.DB_OBJECT_NAME1
AND AAV.ATTRIBUTE_APPLICATION_ID = AOA.ATTRIBUTE_APPLICATION_ID
AND AAV.ATTRIBUTE_CODE = AOA.ATTRIBUTE_CODE
AND BOM.ASSEMBLY_ITEM_ID = CCA.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = CCA.ORGANIZATION_ID
AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND BE.TOP_BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND BE.EXPLOSION_TYPE = 'OPTIONAL'
AND NVL(BE.EFFECTIVITY_DATE
, CCA.EFFECTIVITY_DATE) <= NVL(CCA.EFFECTIVITY_DATE
, SYSDATE)
AND NVL(BE.DISABLE_DATE
, NVL(CCA.DISABLE_DATE
, SYSDATE+1)) > NVL(CCA.DISABLE_DATE
, SYSDATE)
AND AIAV.ATTRIBUTE_APPLICATION_ID = AOA.ATTRIBUTE_APPLICATION_ID
AND AIAV.ATTRIBUTE_CODE = AOA.ATTRIBUTE_CODE
AND AIAV.DATABASE_OBJECT_NAME = AOA.DATABASE_OBJECT_NAME
AND AIAV.KEY_VALUE1 = TO_CHAR(BE.ORGANIZATION_ID)
AND AIAV.KEY_VALUE2 =TO_CHAR(BE.COMPONENT_ITEM_ID)
AND MSI.INVENTORY_ITEM_ID = BE.COMPONENT_ITEM_ID
AND MSI.ORGANIZATION_ID = BE.ORGANIZATION_ID