View: PMIFV_QC_PRODUCTION_SPEC_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	QC Production Specification Full View
Implementation/DBA Data:	APPS.PMIFV_QC_PRODUCTION_SPEC_V

View Text

SELECT QS.QC_SPEC_ID AS QUALITY_CONTROL_SPEC_ID
, QS.QCASSY_TYP_ID AS ASSAY_TYPE_ID
, QS.ORGN_CODE AS ORGANIZATION_CODE
, QS.WHSE_CODE AS WAREHOUSE_CODE
, QS.LOCATION AS LOCATION
, QS.DOC_TYPE AS DOCUMENT_TYPE
, QS.VENDOR_SPECIFICATION AS VENDOR_SPECIFICATION
, QS.VENDOR_CERTIFICATION AS VENDOR_CERTIFICATION
, QS.CUST_SPECIFICATION AS CUSTOMER_SPECIFICATION
, QS.CUST_CERTIFICATION AS CUSTOMER_CERTIFICATION
, QS.ASSAY_CODE AS ASSAY_CODE
, QS.TEXT_SPEC AS TEXT_SPEC
, QS.MIN_SPEC AS MIN_SPEC
, QS.MAX_SPEC AS MAX_SPEC
, QS.TARGET_SPEC AS TARGET_SPEC
, QS.QCUNIT_CODE AS QUALITY_CONTROL_UNIT_CODE
, QS.FROM_DATE AS FROM_DATE
, QS.TO_DATE AS TO_DATE
, QS.OUTACTION_CODE AS OUT_OF_ACTION_CODE
, QS.OUTACTION_INTERVAL AS OUT_OF_ACTION_INTERVAL
, QS.PREFERENCE AS PREFERENCE
, QS.PRINT_COA_SHIPPED AS PRINT_COA_SHIPPED
, QS.PRINT_COA_INVOICED AS PRINT_COA_INVOICED
, QS.VENDOR_COA_REQUIRED AS VENDOR_COA_REQUIRED
, QS.DELETE_MARK AS DELETE_MARK
, QS.ROUTINGSTEP_NO AS ROUTING_STEP_NUMBER
, ' ' AS CUSTOMER_NUMBER
, ' ' AS CUSTOMER_NAME
, IM.ITEM_NO AS ITEM_NUMBER
, IM.ITEM_DESC1 AS ITEM_DESCRIPTION
, 0 AS VENDOR_ID
, ' ' AS VENDOR_NUMBER
, ' ' AS VENDOR_NAME
, FM.FORMULA_ID AS FORMULA_ID
, FM.FORMULA_NO AS FORMULA_NUMBER
, FM.FORMULA_VERS AS FORMULA_VERSION
, LO.LOT_NO AS LOT_NUMBER
, LO.SUBLOT_NO AS SUB_LOT_NUMBER
, PM.BATCH_NO AS BATCH_NUMBER
, RO.ROUTING_ID AS ROUTING_ID
, RO.ROUTING_NO AS ROUTING_NUMBER
, RO.ROUTING_VERS AS ROUTING_VERSION
, OP.OPRN_ID AS OPERATION_ID
, OP.OPRN_NO AS OPERATION_NUMBER
, OP.OPRN_VERS AS OPERATION_VERSION
, ACTIVELKUP.MEANING AS EFFECTIVE
FROM QC_SPEC_MST QS
, IC_ITEM_MST IM
, FM_FORM_MST FM
, IC_LOTS_MST LO
, GME_BATCH_HEADER PM
, FM_ROUT_HDR RO
, FM_OPRN_MST OP
, FND_LOOKUPS ACTIVELKUP
WHERE QS.ITEM_ID = IM.ITEM_ID
AND QS.FORMULA_ID = FM.FORMULA_ID
AND QS.LOT_ID = LO.LOT_ID(+)
AND QS.ITEM_ID = LO.ITEM_ID(+)
AND QS.BATCH_ID = PM.BATCH_ID
AND QS.ROUTING_ID = RO.ROUTING_ID(+)
AND QS.OPRN_ID = OP.OPRN_ID(+)
AND ACTIVELKUP.LOOKUP_TYPE = 'PMI_ACTIVE'
AND ACTIVELKUP.LOOKUP_CODE = 1
AND (SYSDATE >= QS.FROM_DATE
AND SYSDATE <= QS.TO_DATE)
AND (( QS.ORGN_CODE IS NULL) OR ('TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(QS.ORGN_CODE) )) UNION SELECT QS.QC_SPEC_ID AS QUALITY_CONTROL_SPEC_ID
, QS.QCASSY_TYP_ID AS ASSAY_TYPE_ID
, QS.ORGN_CODE AS ORGANIZATION_CODE
, QS.WHSE_CODE AS WAREHOUSE_CODE
, QS.LOCATION AS LOCATION
, QS.DOC_TYPE AS DOCUMENT_TYPE
, QS.VENDOR_SPECIFICATION AS VENDOR_SPECIFICATION
, QS.VENDOR_CERTIFICATION AS VENDOR_CERTIFICATION
, QS.CUST_SPECIFICATION AS CUSTOMER_SPECIFICATION
, QS.CUST_CERTIFICATION AS CUSTOMER_CERTIFICATION
, QS.ASSAY_CODE AS ASSAY_CODE
, QS.TEXT_SPEC AS TEXT_SPEC
, QS.MIN_SPEC AS MIN_SPEC
, QS.MAX_SPEC AS MAX_SPEC
, QS.TARGET_SPEC AS TARGET_SPEC
, QS.QCUNIT_CODE AS QUALITY_CONTROL_UNIT_CODE
, QS.FROM_DATE AS FROM_DATE
, QS.TO_DATE AS TO_DATE
, QS.OUTACTION_CODE AS OUT_OF_ACTION_CODE
, QS.OUTACTION_INTERVAL AS OUT_OF_ACTION_INTERVAL
, QS.PREFERENCE AS PREFERENCE
, QS.PRINT_COA_SHIPPED AS PRINT_COA_SHIPPED
, QS.PRINT_COA_INVOICED AS PRINT_COA_INVOICED
, QS.VENDOR_COA_REQUIRED AS VENDOR_COA_REQUIRED
, QS.DELETE_MARK AS DELETE_MARK
, QS.ROUTINGSTEP_NO AS ROUTING_STEP_NUMBER
, ' ' AS CUSTOMER_NUMBER
, ' ' AS CUSTOMER_NAME
, IM.ITEM_NO AS ITEM_NUMBER
, IM.ITEM_DESC1 AS ITEM_DESCRIPTION
, 0 AS VENDOR_ID
, ' ' AS VENDOR_NUMBER
, ' ' AS VENDOR_NAME
, FM.FORMULA_ID AS FORMULA_ID
, FM.FORMULA_NO AS FORMULA_NUMBER
, FM.FORMULA_VERS AS FORMULA_VERSION
, LO.LOT_NO AS LOT_NUMBER
, LO.SUBLOT_NO AS SUB_LOT_NUMBER
, PM.BATCH_NO AS BATCH_NUMBER
, RO.ROUTING_ID AS ROUTING_ID
, RO.ROUTING_NO AS ROUTING_NUMBER
, RO.ROUTING_VERS AS ROUTING_VERSION
, OP.OPRN_ID AS OPERATION_ID
, OP.OPRN_NO AS OPERATION_NUMBER
, OP.OPRN_VERS AS OPERATION_VERSION
, ACTIVELKUP.MEANING AS EFFECTIVE
FROM QC_SPEC_MST QS
, IC_ITEM_MST IM
, FM_FORM_MST FM
, IC_LOTS_MST LO
, GME_BATCH_HEADER PM
, FM_ROUT_HDR RO
, FM_OPRN_MST OP
, FND_LOOKUPS ACTIVELKUP
WHERE QS.ITEM_ID = IM.ITEM_ID
AND QS.FORMULA_ID = FM.FORMULA_ID
AND QS.LOT_ID = LO.LOT_ID(+)
AND QS.ITEM_ID = LO.ITEM_ID(+)
AND QS.BATCH_ID = PM.BATCH_ID
AND QS.ROUTING_ID = RO.ROUTING_ID(+)
AND QS.OPRN_ID = OP.OPRN_ID(+)
AND ACTIVELKUP.LOOKUP_TYPE = 'PMI_ACTIVE'
AND ACTIVELKUP.LOOKUP_CODE = 2
AND ( SYSDATE < QS.FROM_DATE OR SYSDATE > QS.TO_DATE)
AND (( QS.ORGN_CODE IS NULL) OR ('TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(QS.ORGN_CODE) ))

Columns