VIEW: APPS.PMIBV_QC_RESULTS_V

Source

View Text - Preformatted

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 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

View Text - HTML Formatted

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 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