Product: | PMI - Process Manufacturing Intelligence (Obsolete) |
---|---|
Description: | QC Results Full View |
Implementation/DBA Data: | APPS.PMIFV_QC_RESULTS_V |
SELECT QR.ORGN_CODE AS ORGANIZATION_CODE
, QR.WHSE_CODE AS WAREHOUSE_CODE
, QS.DOC_TYPE AS DOCUMENT_TYPE
, QR.RESULT_DATE AS RESULT_DATE
, QR.TEXT_RESULT AS TEXT_RESULT
, QR.NUM_RESULT AS NUM_RESULT
, ALKUP.MEANING AS ACCEPT_ANYWAY
, FLKUP.MEANING AS FINAL_MARK
, QR.TEST_PROVIDER_CODE AS TEST_PROVIDER_CODE
, QR.LOCATION AS LOCATION
, QR.DELETE_MARK AS DELETE_MARK
, QR.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, QS.VENDOR_SPECIFICATION AS VENDOR_SPECIFICATION
, QS.CUST_SPECIFICATION AS CUSTOMER_SPECIFICATION
, 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
, PARTY.PARTY_NUMBER AS CUSTOMER_NUMBER
, PARTY.PARTY_NAME AS CUSTOMER_NAME
, PO.VENDOR_NO AS VENDOR_NO
, PO.VENDOR_NAME AS VENDOR_NAME
, QS.QC_SPEC_ID AS QUALITY_CONTROL_SPEC_ID
, IM.ITEM_NO AS ITEM_NUMBER
, IM.ITEM_DESC1 AS ITEM_DESCRIPTION
, LOT.LOT_NO AS LOT_NUMBER
, LOT.SUBLOT_NO AS SUB_LOT_NUMBER
, SAM.SAMPLE_NO AS SAMPLE_NUMBER
, PM.BATCH_NO AS BATCH_NUMBER
, FM.FORMULA_NO AS FORMULA_NUMBER
, FM.FORMULA_VERS AS FORMULA_VERSION
, FMR.ROUTING_NO AS ROUTING_NUMBER
, FMR.ROUTING_VERS AS ROUTING_VERSION
, FMO.OPRN_NO AS OPERATION_NUMBER
, FMO.OPRN_VERS AS OPERATION_VERSION
, QR.ROUTINGSTEP_NO AS ROUTING_STEP_NUMBER
, QR.CUST_ID
, QR.SHIP_TO_SITE_ID FROM QC_SPEC_MST QS
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, IC_ITEM_MST IM
, QC_RSLT_MST QR
, IC_LOTS_MST LOT
, PO_VEND_MST PO
, GME_BATCH_HEADER PM
, FM_FORM_MST FM
, FM_ROUT_HDR FMR
, FM_OPRN_MST FMO
, QC_SMPL_MST SAM
, QC_ASSY_TYP ASSY
, FND_LOOKUPS ALKUP
, FND_LOOKUPS FLKUP
WHERE PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = SAM.CUST_ID
AND FM.FORMULA_ID(+) = SAM.FORMULA_ID AND PM.BATCH_ID(+) = QR.BATCH_ID AND FMR.ROUTING_ID(+) = SAM.ROUTING_ID
AND FMO.OPRN_ID(+) = SAM.OPRN_ID AND PO.VENDOR_ID(+) = SAM.VENDOR_ID AND IM.ITEM_ID = QS.ITEM_ID AND LOT.LOT_ID(+) = QR.LOT_ID AND LOT.ITEM_ID(+)= QR.ITEM_ID AND QS.ASSAY_CODE = QR.ASSAY_CODE AND QS.QC_SPEC_ID = QR.QC_SPEC_ID AND SAM.SAMPLE_ID = QR.SAMPLE_ID AND ASSY.QCASSY_TYP_ID = QR.QCASSY_TYP_ID
AND QS.ITEM_ID = QR.ITEM_ID
AND ALKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND ALKUP.LOOKUP_CODE = QR.ACCEPT_ANYWAY
AND FLKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND FLKUP.LOOKUP_CODE = QR.FINAL_MARK
AND (( QR.ORGN_CODE IS NULL) OR ('TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(QR.ORGN_CODE) )) UNION SELECT QR.ORGN_CODE AS ORGANIZATION_CODE
, QR.WHSE_CODE AS WAREHOUSE_CODE
, ' ' AS DOCUMENT_TYPE
, QR.RESULT_DATE AS RESULT_DATE
, QR.TEXT_RESULT AS TEXT_RESULT
, QR.NUM_RESULT AS NUM_RESULT
, ALKUP.MEANING AS ACCEPT_ANYWAY
, FLKUP.MEANING AS FINAL_MARK
, QR.TEST_PROVIDER_CODE AS TEST_PROVIDER_CODE
, QR.LOCATION AS LOCATION
, QR.DELETE_MARK AS DELETE_MARK
, QR.LAST_UPDATE_DATE AS LAST_UPDATE_DATE
, 0 AS VENDOR_SPECIFICATION
, 0 AS CUSTOMER_SPECIFICATION
, ASSY.ASSAY_CODE AS ASSAY_CODE
, ' ' AS TEXT_SPEC
, ASSY.MIN_VALID AS MIN_SPEC
, ASSY.MAX_VALID AS MAX_SPEC
, 0 AS TARGET_SPEC
, ASSY.QCUNIT_CODE AS QUALITY_CONTROL_UNIT_CODE
, QR.RESULT_DATE AS FROM_DATE
, PARTY.PARTY_NUMBER AS CUSTOMER_NUMBER
, PARTY.PARTY_NAME AS CUSTOMER_NAME
, PO.VENDOR_NO AS VENDOR_NUMBER
, PO.VENDOR_NAME AS VENDOR_NAME
, 0 AS QUALITY_CONTROL_SPEC_ID
, IM.ITEM_NO AS ITEM_NUMBER
, IM.ITEM_DESC1 AS ITEM_DESCRIPTION
, LOT.LOT_NO AS LOT_NUMBER
, LOT.SUBLOT_NO AS SUB_LOT_NUMBER
, SAM.SAMPLE_NO AS SAMPLE_NUMBER
, PM.BATCH_NO AS BATCH_NUMNER
, FM.FORMULA_NO AS FORMULA_NUMBER
, FM.FORMULA_VERS AS FORMULA_VERSION
, FMR.ROUTING_NO AS ROUTING_NUMBER
, FMR.ROUTING_VERS AS ROUTING_VERSION
, FMO.OPRN_NO AS OPERATION_NUMBER
, FMO.OPRN_VERS AS OPERATION_VERSION
, QR.ROUTINGSTEP_NO AS ROUTING_STEP_NUMBER
, QR.CUST_ID
, QR.SHIP_TO_SITE_ID FROM QC_ASSY_TYP ASSY
, HZ_CUST_ACCOUNTS_ALL CUST_ACCT
, HZ_PARTIES PARTY
, IC_ITEM_MST IM
, QC_RSLT_MST QR
, IC_LOTS_MST LOT
, PO_VEND_MST PO
, GME_BATCH_HEADER PM
, FM_FORM_MST FM
, FM_ROUT_HDR FMR
, FM_OPRN_MST FMO
, QC_SMPL_MST SAM
, FND_LOOKUPS ALKUP
, FND_LOOKUPS FLKUP WHERE PARTY.PARTY_ID(+) = CUST_ACCT.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID(+) = SAM.CUST_ID
AND FM.FORMULA_ID(+) = SAM.FORMULA_ID
AND FMR.ROUTING_ID(+) = SAM.ROUTING_ID
AND FMO.OPRN_ID(+) = SAM.OPRN_ID
AND PM.BATCH_ID(+) = QR.BATCH_ID
AND PO.VENDOR_ID(+) = SAM.VENDOR_ID
AND IM.ITEM_ID = QR.ITEM_ID
AND QR.QC_SPEC_ID IS NULL
AND LOT.LOT_ID(+) = QR.LOT_ID
AND LOT.ITEM_ID(+) = QR.ITEM_ID
AND ASSY.ASSAY_CODE = QR.ASSAY_CODE
AND SAM.SAMPLE_ID = QR.SAMPLE_ID
AND ASSY.QCASSY_TYP_ID = QR.QCASSY_TYP_ID
AND ALKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND ALKUP.LOOKUP_CODE = QR.ACCEPT_ANYWAY
AND FLKUP.LOOKUP_TYPE = 'PMI_YES_NO'
AND FLKUP.LOOKUP_CODE = QR.FINAL_MARK
AND (( QR.ORGN_CODE IS NULL) OR ('TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(QR.ORGN_CODE) ))