View: PMIFV_QC_SPECIFICATIONS_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	QC Specifications Base View
Implementation/DBA Data:	APPS.PMIFV_QC_SPECIFICATIONS_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
, COALKUP.MEANING AS VENDOR_COA_REQUIRED
, QS.DELETE_MARK AS DELETE_MARK
, QS.ROUTINGSTEP_NO AS ROUTING_STEP_NUMBER
, PARTY.PARTY_NUMBER AS CUSTOMER_NUMBER
, PARTY.PARTY_NAME AS CUSTOMER_NAME
, IM.ITEM_NO AS ITEM_NUMBER
, IM.ITEM_DESC1 AS ITEM_DESCRIPTION
, V.VENDOR_ID AS VENDOR_ID
, V.VENDOR_NO AS VENDOR_NUMBER
, V.VENDOR_NAME AS VENDOR_NAME
, ' ' AS FORMULA_NUMBER
, 0 AS FORMULA_VERSION
, LO.LOT_NO AS LOT_NUMBER
, LO.SUBLOT_NO AS SUB_LOT_NUMBER
, '0' AS BATCH_NUMBER
, '0' AS ROUTING_NUMBER
, 1 AS ROUTING_VERSION
, '0' AS OPERATION_NUMBER
, 1 AS OPERATION_VERSION
, ACTIVELKUP.MEANING AS EFFECTIVE
, QS.CUST_ID
, QS.SHIP_TO_SITE_ID FROM QC_SPEC_MST QS
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, IC_ITEM_MST IM
, PO_VEND_MST V
, IC_LOTS_MST LO
, FND_LOOKUPS COALKUP
, FND_LOOKUPS ACTIVELKUP
WHERE PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = QS.CUST_ID
AND LO.LOT_ID(+) = QS.LOT_ID
AND LO.ITEM_ID(+) = QS.ITEM_ID
AND IM.ITEM_ID(+) = QS.ITEM_ID
AND V.VENDOR_ID(+) = QS.VENDOR_ID
AND COALKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND COALKUP.LOOKUP_CODE = NVL(QS.VENDOR_COA_REQUIRED
, 0)
AND ACTIVELKUP.LOOKUP_TYPE = 'PMI_ACTIVE'
AND ACTIVELKUP.LOOKUP_CODE = 1
AND (TRUNC(SYSDATE) >= TRUNC(QS.FROM_DATE)
AND TRUNC(SYSDATE) < TRUNC(QS.TO_DATE)) UNION SELECT QS.QC_SPEC_ID AS QUALITY_CONTORL_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
, COALKUP.MEANING AS VENDOR_COA_REQUIRED
, QS.DELETE_MARK AS DELETE_MARK
, QS.ROUTINGSTEP_NO AS ROUTING_STEP_NUMBER
, PARTY.PARTY_NUMBER AS CUSTOMER_NUMBER
, PARTY.PARTY_NAME AS CUSTOMER_NAME
, IM.ITEM_NO AS ITEM_NUMBER
, IM.ITEM_DESC1 AS ITEM_DESCRIPTION
, V.VENDOR_ID AS VENDOR_ID
, V.VENDOR_NO AS VENDOR_NUMBER
, V.VENDOR_NAME AS VENDOR_NAME
, ' ' AS FORMULA_NUMBER
, 0 AS FORMULA_VERSION
, LO.LOT_NO AS LOT_NUMBER
, LO.SUBLOT_NO AS SUB_LOT_NUMBER
, ' ' AS BATCH_NUMBER
, '0' AS ROUTING_NUMBER
, 0 AS ROUTING_VERSION
, '0' AS OPERATION_NUMBER
, 0 AS OPERATION_VERSION
, ACTIVELKUP.MEANING AS EFFECTIVE
, QS.CUST_ID
, QS.SHIP_TO_SITE_ID FROM QC_SPEC_MST QS
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, IC_ITEM_MST IM
, PO_VEND_MST V
, IC_LOTS_MST LO
, FND_LOOKUPS COALKUP
, FND_LOOKUPS ACTIVELKUP WHERE PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = QS.CUST_ID
AND LO.LOT_ID(+) = QS.LOT_ID
AND LO.ITEM_ID(+) = QS.ITEM_ID
AND IM.ITEM_ID(+) = QS.ITEM_ID
AND V.VENDOR_ID(+) = QS.VENDOR_ID
AND COALKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND COALKUP.LOOKUP_CODE = NVL(QS.VENDOR_COA_REQUIRED
, 0)
AND ACTIVELKUP.LOOKUP_TYPE = 'PMI_ACTIVE'
AND ACTIVELKUP.LOOKUP_CODE = 2
AND ( TRUNC(SYSDATE) < TRUNC(QS.FROM_DATE) OR TRUNC(SYSDATE) >= TRUNC(QS.TO_DATE))

Columns