VIEW: APPS.QA_ERES_SPECS_V

Source

View Text - Preformatted

SELECT QS.SPEC_ID, QS.LAST_UPDATE_DATE, QS.LAST_UPDATED_BY, QS.CREATION_DATE, QS.CREATED_BY, QS.LAST_UPDATE_LOGIN, QS.SPEC_NAME, QS.ORGANIZATION_ID, MP.ORGANIZATION_CODE, HRORG.NAME, QS.EFFECTIVE_FROM, QS.EFFECTIVE_TO, QS.COMMON_SPEC_ID COMMON_SPEC_ID, decode(QS.COMMON_SPEC_ID, QS.SPEC_ID, NULL, QS2.SPEC_NAME) COMMON_SPEC_NAME, QS.ASSIGNMENT_TYPE, ML2.MEANING ASSIGNMENT_TYPE_MEANING, decode(QS.CATEGORY_SET_ID, -1, to_number(NULL), QS.CATEGORY_SET_ID) CATEGORY_SET_ID, CSETTL.CATEGORY_SET_NAME, CSETTL.DESCRIPTION CATEGORY_SET_DESCRIPTION, CSET.STRUCTURE_ID, decode(QS.CATEGORY_ID, -1, to_number(NULL), QS.CATEGORY_ID) CATEGORY_ID, qa_eres_pkg.get_category_name(QS.CATEGORY_ID, QS.CATEGORY_SET_ID) CATEGORY_NAME, qa_eres_pkg.get_category_desc(QS.CATEGORY_ID, QS.CATEGORY_SET_ID) CATEGORY_DESCRIPTIION, decode(QS.ITEM_ID, -1, to_number(NULL), QS.ITEM_ID) ITEM_ID, decode(QS.ITEM_ID, -1, NULL, MSIK1.CONCATENATED_SEGMENTS) ITEM, ITL.DESCRIPTION ITEM_DESCRIPTION, decode(QS.ITEM_REVISION, 'NONE', NULL, QS.ITEM_REVISION) ITEM_REVISION, I.REVISION_QTY_CONTROL_CODE REV_CONTROL_FLAG, decode(QS.VENDOR_ID, -1, to_number(NULL), QS.VENDOR_ID) VENDOR_ID, V.SEGMENT1 VENDOR_NUMBER, V.VENDOR_NAME, decode(QS.CUSTOMER_ID, -1, to_number(NULL), QS.CUSTOMER_ID) CUSTOMER_ID, CUS.CUSTOMER_NUMBER, CUS.CUSTOMER_NAME, decode(QS.CHAR_ID, -1, to_number(NULL), QS.CHAR_ID ) CHAR_ID, QC.NAME SPEC_ELEMENT, decode(QS.CHAR_ID, -1, NULL, QS.SPEC_ELEMENT_VALUE) SPEC_ELEMENT_VALUE, QS.SPEC_STATUS, ML4.MEANING, QS.ATTRIBUTE_CATEGORY, QS.ATTRIBUTE1, QS.ATTRIBUTE2, QS.ATTRIBUTE3, QS.ATTRIBUTE4, QS.ATTRIBUTE5, QS.ATTRIBUTE6, QS.ATTRIBUTE7, QS.ATTRIBUTE8, QS.ATTRIBUTE9, QS.ATTRIBUTE10, QS.ATTRIBUTE11, QS.ATTRIBUTE12, QS.ATTRIBUTE13, QS.ATTRIBUTE14, QS.ATTRIBUTE15 FROM MFG_LOOKUPS ML2, MFG_LOOKUPS ML4, MTL_CATEGORY_SETS_B CSET, MTL_CATEGORY_SETS_TL CSETTL, PO_VENDORS V, QA_CUSTOMERS_LOV_V CUS, MTL_SYSTEM_ITEMS_B I, MTL_SYSTEM_ITEMS_TL ITL, QA_SPECS QS2, QA_SPECS QS, QA_CHARS QC, MTL_PARAMETERS MP, HR_ALL_ORGANIZATION_UNITS_TL HRORG, MTL_SYSTEM_ITEMS_KFV MSIK1 WHERE QS.ASSIGNMENT_TYPE = ML2.LOOKUP_CODE AND ML2.LOOKUP_TYPE = 'QA_SPEC_TYPE' AND QS.SPEC_STATUS = ML4.LOOKUP_CODE AND ML4.LOOKUP_TYPE = 'QA_ERES_SPEC_STATUS' AND QS.ORGANIZATION_ID = MP.ORGANIZATION_ID(+) AND HRORG.LANGUAGE = USERENV('LANG') AND QS.ORGANIZATION_ID = HRORG.ORGANIZATION_ID(+) AND QS.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID (+) AND CSET.CATEGORY_SET_ID = CSETTL.CATEGORY_SET_ID (+) AND CSETTL.LANGUAGE (+) = USERENV('LANG') AND QS.VENDOR_ID = V.VENDOR_ID (+) AND QS.CUSTOMER_ID = CUS.CUSTOMER_ID (+) AND QS.ITEM_ID = I.INVENTORY_ITEM_ID (+) AND I.INVENTORY_ITEM_ID = ITL.INVENTORY_ITEM_ID (+) AND I.ORGANIZATION_ID = ITL.ORGANIZATION_ID (+) AND ITL.LANGUAGE (+) = USERENV('LANG') AND QS.ORGANIZATION_ID = I.ORGANIZATION_ID (+) AND QS.COMMON_SPEC_ID = QS2.SPEC_ID AND QS.CHAR_ID = QC.CHAR_ID (+) AND QS.ITEM_ID = MSIK1.INVENTORY_ITEM_ID(+) AND QS.ORGANIZATION_ID = MSIK1.ORGANIZATION_ID(+)

View Text - HTML Formatted

SELECT QS.SPEC_ID
, QS.LAST_UPDATE_DATE
, QS.LAST_UPDATED_BY
, QS.CREATION_DATE
, QS.CREATED_BY
, QS.LAST_UPDATE_LOGIN
, QS.SPEC_NAME
, QS.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HRORG.NAME
, QS.EFFECTIVE_FROM
, QS.EFFECTIVE_TO
, QS.COMMON_SPEC_ID COMMON_SPEC_ID
, DECODE(QS.COMMON_SPEC_ID
, QS.SPEC_ID
, NULL
, QS2.SPEC_NAME) COMMON_SPEC_NAME
, QS.ASSIGNMENT_TYPE
, ML2.MEANING ASSIGNMENT_TYPE_MEANING
, DECODE(QS.CATEGORY_SET_ID
, -1
, TO_NUMBER(NULL)
, QS.CATEGORY_SET_ID) CATEGORY_SET_ID
, CSETTL.CATEGORY_SET_NAME
, CSETTL.DESCRIPTION CATEGORY_SET_DESCRIPTION
, CSET.STRUCTURE_ID
, DECODE(QS.CATEGORY_ID
, -1
, TO_NUMBER(NULL)
, QS.CATEGORY_ID) CATEGORY_ID
, QA_ERES_PKG.GET_CATEGORY_NAME(QS.CATEGORY_ID
, QS.CATEGORY_SET_ID) CATEGORY_NAME
, QA_ERES_PKG.GET_CATEGORY_DESC(QS.CATEGORY_ID
, QS.CATEGORY_SET_ID) CATEGORY_DESCRIPTIION
, DECODE(QS.ITEM_ID
, -1
, TO_NUMBER(NULL)
, QS.ITEM_ID) ITEM_ID
, DECODE(QS.ITEM_ID
, -1
, NULL
, MSIK1.CONCATENATED_SEGMENTS) ITEM
, ITL.DESCRIPTION ITEM_DESCRIPTION
, DECODE(QS.ITEM_REVISION
, 'NONE'
, NULL
, QS.ITEM_REVISION) ITEM_REVISION
, I.REVISION_QTY_CONTROL_CODE REV_CONTROL_FLAG
, DECODE(QS.VENDOR_ID
, -1
, TO_NUMBER(NULL)
, QS.VENDOR_ID) VENDOR_ID
, V.SEGMENT1 VENDOR_NUMBER
, V.VENDOR_NAME
, DECODE(QS.CUSTOMER_ID
, -1
, TO_NUMBER(NULL)
, QS.CUSTOMER_ID) CUSTOMER_ID
, CUS.CUSTOMER_NUMBER
, CUS.CUSTOMER_NAME
, DECODE(QS.CHAR_ID
, -1
, TO_NUMBER(NULL)
, QS.CHAR_ID ) CHAR_ID
, QC.NAME SPEC_ELEMENT
, DECODE(QS.CHAR_ID
, -1
, NULL
, QS.SPEC_ELEMENT_VALUE) SPEC_ELEMENT_VALUE
, QS.SPEC_STATUS
, ML4.MEANING
, QS.ATTRIBUTE_CATEGORY
, QS.ATTRIBUTE1
, QS.ATTRIBUTE2
, QS.ATTRIBUTE3
, QS.ATTRIBUTE4
, QS.ATTRIBUTE5
, QS.ATTRIBUTE6
, QS.ATTRIBUTE7
, QS.ATTRIBUTE8
, QS.ATTRIBUTE9
, QS.ATTRIBUTE10
, QS.ATTRIBUTE11
, QS.ATTRIBUTE12
, QS.ATTRIBUTE13
, QS.ATTRIBUTE14
, QS.ATTRIBUTE15
FROM MFG_LOOKUPS ML2
, MFG_LOOKUPS ML4
, MTL_CATEGORY_SETS_B CSET
, MTL_CATEGORY_SETS_TL CSETTL
, PO_VENDORS V
, QA_CUSTOMERS_LOV_V CUS
, MTL_SYSTEM_ITEMS_B I
, MTL_SYSTEM_ITEMS_TL ITL
, QA_SPECS QS2
, QA_SPECS QS
, QA_CHARS QC
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS_TL HRORG
, MTL_SYSTEM_ITEMS_KFV MSIK1
WHERE QS.ASSIGNMENT_TYPE = ML2.LOOKUP_CODE
AND ML2.LOOKUP_TYPE = 'QA_SPEC_TYPE'
AND QS.SPEC_STATUS = ML4.LOOKUP_CODE
AND ML4.LOOKUP_TYPE = 'QA_ERES_SPEC_STATUS'
AND QS.ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND HRORG.LANGUAGE = USERENV('LANG')
AND QS.ORGANIZATION_ID = HRORG.ORGANIZATION_ID(+)
AND QS.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID (+)
AND CSET.CATEGORY_SET_ID = CSETTL.CATEGORY_SET_ID (+)
AND CSETTL.LANGUAGE (+) = USERENV('LANG')
AND QS.VENDOR_ID = V.VENDOR_ID (+)
AND QS.CUSTOMER_ID = CUS.CUSTOMER_ID (+)
AND QS.ITEM_ID = I.INVENTORY_ITEM_ID (+)
AND I.INVENTORY_ITEM_ID = ITL.INVENTORY_ITEM_ID (+)
AND I.ORGANIZATION_ID = ITL.ORGANIZATION_ID (+)
AND ITL.LANGUAGE (+) = USERENV('LANG')
AND QS.ORGANIZATION_ID = I.ORGANIZATION_ID (+)
AND QS.COMMON_SPEC_ID = QS2.SPEC_ID
AND QS.CHAR_ID = QC.CHAR_ID (+)
AND QS.ITEM_ID = MSIK1.INVENTORY_ITEM_ID(+)
AND QS.ORGANIZATION_ID = MSIK1.ORGANIZATION_ID(+)