GMD Item/Location Test Results
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Item/Location Assay Results Report of OPM QC
Application: Process Manufacturing Product Development
Source: Item/Location Test Results Report (XML)
Short Name: QCR02USR_XML
DB package: GMD_QCR02USR_XMLP_PKG
Description: Item/Location Assay Results Report of OPM QC
Application: Process Manufacturing Product Development
Source: Item/Location Test Results Report (XML)
Short Name: QCR02USR_XML
DB package: GMD_QCR02USR_XMLP_PKG
Run
GMD Item/Location Test Results and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT msi.concatenated_segments ITEM, '['|| msi.description ||']' item_desc1, mln.lot_number LOT, '['||mln.description||']' lot_desc, gs.subinventory , gs.locator_id , gt.test_type, gt.test_code assay_code, gr1.text_code, decode(ges.spec_id,null, gt.test_unit,gst.test_uom) qcunit_code, gr1.result_date, decode(gsr.evaluation_ind,'0A','Y','N') accept_anyway, decode(gr1.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y', NVL(gst.print_result_ind, 'N'), 'Y', 'N'), 'N') final_mark, gs.revision, gs.sample_no , gs.sample_qty, gs.date_drawn sample_date, gs.sample_qty_uom sample_um, gr1.result_value_char text_result, gr1.result_value_num num_result, NVL(gst.report_precision, gt.report_precision) precision, gst.spec_id SPEC, gst.target_value_char text_spec, gst.target_value_num target_spec, gst.min_value_char min_char, gst.max_value_char max_char, gst.min_value_num min_spec, gst.max_value_num max_spec, gst.test_id qcassy_typ_id, nvl( gsr.in_spec_ind,'N') in_spec_ind, GMD_QCR02USR_XMLP_PKG.locator_namecfformula(gs.locator_id) Locator_NameCF, GMD_QCR02USR_XMLP_PKG.assay_desccfformula(gt.test_code) assay_descCF, GMD_QCR02USR_XMLP_PKG.text_codecfformula(gst.spec_id) text_codeCF, GMD_QCR02USR_XMLP_PKG.assay_dispcfformula(gt.test_code, gst.test_id, :targetspecCF) assay_dispCF, GMD_QCR02USR_XMLP_PKG.assaymin_dispcfformula(gst.test_id, :min_specCF) assaymin_dispCF, GMD_QCR02USR_XMLP_PKG.assaymax_dispcfformula(gst.test_id, :max_specCF) assaymax_dispCF, GMD_QCR02USR_XMLP_PKG.qcunit_codecfformula(gst.spec_id, gst.test_id) qcunit_codeCF, GMD_QCR02USR_XMLP_PKG.textcfformula(gst.target_value_char) textspecCF, GMD_QCR02USR_XMLP_PKG.targetspecformula(gst.target_value_num, gt.test_type, NVL ( gst.report_precision , gt.report_precision )) targetspecCF, GMD_QCR02USR_XMLP_PKG.min_vaildcfformula(gt.test_code) min_vaildCF, GMD_QCR02USR_XMLP_PKG.min_speccfformula(gst.min_value_char, gst.min_value_num) min_specCF, GMD_QCR02USR_XMLP_PKG.min_charcfformula(gst.min_value_char) min_charCF, GMD_QCR02USR_XMLP_PKG.minimumcfformula(gst.min_value_num, gt.test_type, NVL ( gst.report_precision , gt.report_precision ), gst.min_value_char) minimumCF, GMD_QCR02USR_XMLP_PKG.max_speccfformula(gst.max_value_char, gst.max_value_num) max_specCF, GMD_QCR02USR_XMLP_PKG.max_charcfformula(gst.max_value_char) max_charCF, GMD_QCR02USR_XMLP_PKG.max_vaildcfformula(gt.test_code) max_vaildCF, GMD_QCR02USR_XMLP_PKG.maximumcfformula(gst.max_value_num, gt.test_type, NVL ( gst.report_precision , gt.report_precision ), gst.max_value_char) maximumCF, GMD_QCR02USR_XMLP_PKG.effectivitycfformula(gs.sample_no) EffectivityCF, GMD_QCR02USR_XMLP_PKG.EFFECTIVITYCFFORMULACHR(gs.sample_no) EffectivityCFCH, GMD_QCR02USR_XMLP_PKG.effectivitycf1formula(gs.sample_no) EffectivityCF1, GMD_QCR02USR_XMLP_PKG.textarraycfformula(:text_codeCF) textarrayCF, GMD_QCR02USR_XMLP_PKG.assay_typeCP_p assay_typeCP, GMD_QCR02USR_XMLP_PKG.result_dispcfformula(gst.test_id, gr1.result_value_num) result_dispCF, GMD_QCR02USR_XMLP_PKG.num_resultcfformula(gr1.result_value_char, gr1.result_value_num, gt.test_type, NVL ( gst.report_precision , gt.report_precision )) num_resultCF, GMD_QCR02USR_XMLP_PKG.resultcfformula(gr1.result_value_char, gr1.result_value_num) ResultCF, GMD_QCR02USR_XMLP_PKG.acceptcfformula(decode ( gsr.evaluation_ind , '0A' , 'Y' , 'N' ), decode ( gr1.ad_hoc_print_on_coa_ind , 'Y' , decode ( gsr.evaluation_ind , '0A' , 'Y' , '1V' , 'Y' , '2R' , 'Y' , NVL ( gst.print_result_ind , 'N' ) , 'Y' , 'N' ) , 'N' ), nvl ( gsr.in_spec_ind , 'N' )) AcceptCF, GMD_QCR02USR_XMLP_PKG.textarray2cfformula(gr1.text_code) textarray2CF, GMD_QCR02USR_XMLP_PKG.SPEC_CP_p SPEC_CP, GMD_QCR02USR_XMLP_PKG.Accept_CP_p Accept_CP, GMD_QCR02USR_XMLP_PKG.Final_CP_p Final_CP, 'x' dummy, 'y' dummy, 'z' dummy FROM mtl_system_items_kfv msi, mtl_lot_Numbers mln, gmd_samples gs, gmd_results gr1, gmd_qc_tests_b gt, gmd_spec_results gsr, gmd_spec_tests_b gst , gmd_sampling_events gse, gmd_event_spec_disp ges WHERE gs.inventory_item_id = msi.inventory_item_id AND gs.organization_id = msi.organization_id AND gs.inventory_item_id = mln.inventory_item_id AND gs.organization_id = mln.organization_id AND gs.organization_id = :p_organization_Id AND nvl(gs.lot_number(+),0) = nvl(mln.lot_number,0) AND gs.sample_id = gr1.sample_id AND gse.sampling_event_id = gs.sampling_event_id AND gse.sampling_event_id = ges.sampling_event_id AND ges.spec_used_for_lot_attrib_ind ='Y' AND ges.spec_id(+) = gst.spec_id AND gst.test_id(+) = gt.test_id AND gs.sample_id = gr1.sample_id AND gr1.result_date IS NOT NULL AND gr1.result_id = gsr.result_id AND gr1.test_id = gt.test_id AND ges.event_spec_disp_id = gsr.event_spec_disp_id AND gs.cust_id IS NULL AND gs.supplier_id IS NULL AND gs.batch_id IS NULL AND gs.formula_id IS NULL AND gs.routing_id IS NULL AND gs.oprn_id IS NULL AND gr1.delete_mark = 0 AND gs.delete_mark = 0 &SampleCP &ItemCP &LotnoCP &SubinventoryCP &LocatorCP &DateCP &IncludeCP union SELECT distinct msi.concatenated_segments ITEM, '['|| msi.description ||']' item_desc1, mln.lot_number LOT, '['||mln.description||']' lot_desc, gs.subinventory , gs.locator_id , gt.test_type, gt.test_code assay_code, gr1.text_code, gt.test_unit qcunit_code , gr1.result_date, DECODE (gsr.evaluation_ind, '0A', 'Y', 'N') accept_anyway, DECODE (gr1.ad_hoc_print_on_coa_ind, 'Y', DECODE (gsr.evaluation_ind, '0A', 'Y', '1V', 'Y', '2R', 'Y', 'N' ), 'N' ) final_mark, gs.revision, gs.sample_no, gs.sample_qty, gs.date_drawn sample_date, gs.sample_qty_uom sample_um, gr1.result_value_char text_result, gr1.result_value_num num_result, NVL (gt.report_precision, gt.report_precision) PRECISION, NULL spec , NULL text_spec, NULL target_spec,NULL min_char, NULL max_char, gt.min_value_num min_spec, gt.max_value_num max_spec, gt.test_id qcassy_typ_id, NVL (gsr.in_spec_ind, 'N') in_spec_ind, GMD_QCR02USR_XMLP_PKG.locator_namecfformula(gs.locator_id) Locator_NameCF, GMD_QCR02USR_XMLP_PKG.assay_desccfformula(gt.test_code) assay_descCF, GMD_QCR02USR_XMLP_PKG.text_codecfformula(NULL) text_codeCF, GMD_QCR02USR_XMLP_PKG.assay_dispcfformula(gt.test_code, gt.test_id, :targetspecCF) assay_dispCF, GMD_QCR02USR_XMLP_PKG.assaymin_dispcfformula(gt.test_id, :min_specCF) assaymin_dispCF, GMD_QCR02USR_XMLP_PKG.assaymax_dispcfformula(gt.test_id, :max_specCF) assaymax_dispCF, GMD_QCR02USR_XMLP_PKG.qcunit_codecfformula(NULL, gt.test_id) qcunit_codeCF, GMD_QCR02USR_XMLP_PKG.textcfformula(NULL) textspecCF, GMD_QCR02USR_XMLP_PKG.targetspecformula(NULL, gt.test_type, NVL (gt.report_precision, gt.report_precision)) targetspecCF, GMD_QCR02USR_XMLP_PKG.min_vaildcfformula(gt.test_code) min_vaildCF, GMD_QCR02USR_XMLP_PKG.min_speccfformula(NULL, gt.min_value_num) min_specCF, GMD_QCR02USR_XMLP_PKG.min_charcfformula(NULL) min_charCF, GMD_QCR02USR_XMLP_PKG.minimumcfformula(gt.min_value_num, gt.test_type, NVL (gt.report_precision, gt.report_precision), NULL) minimumCF, GMD_QCR02USR_XMLP_PKG.max_speccfformula(NULL, gt.max_value_num) max_specCF, GMD_QCR02USR_XMLP_PKG.max_charcfformula(NULL) max_charCF, GMD_QCR02USR_XMLP_PKG.max_vaildcfformula(gt.test_code) max_vaildCF, GMD_QCR02USR_XMLP_PKG.maximumcfformula(gt.max_value_num, gt.test_type,NVL (gt.report_precision, gt.report_precision), NULL) maximumCF, GMD_QCR02USR_XMLP_PKG.effectivitycfformula(gs.sample_no) EffectivityCF, GMD_QCR02USR_XMLP_PKG.EFFECTIVITYCFFORMULACHR(gs.sample_no) EffectivityCFCH, GMD_QCR02USR_XMLP_PKG.effectivitycf1formula(gs.sample_no) EffectivityCF1, GMD_QCR02USR_XMLP_PKG.textarraycfformula(:text_codeCF) textarrayCF, GMD_QCR02USR_XMLP_PKG.assay_typeCP_p assay_typeCP, GMD_QCR02USR_XMLP_PKG.result_dispcfformula(gt.test_id, gr1.result_value_num) result_dispCF, GMD_QCR02USR_XMLP_PKG.num_resultcfformula(gr1.result_value_char, gr1.result_value_num, gt.test_type, NVL (gt.report_precision, gt.report_precision)) num_resultCF, GMD_QCR02USR_XMLP_PKG.resultcfformula(gr1.result_value_char, gr1.result_value_num) ResultCF, GMD_QCR02USR_XMLP_PKG.acceptcfformula(DECODE (gsr.evaluation_ind, '0A', 'Y', 'N'), DECODE (gr1.ad_hoc_print_on_coa_ind, 'Y', DECODE (gsr.evaluation_ind, '0A', 'Y', '1V', 'Y', '2R', 'Y', 'N' ), 'N' ), NVL (gsr.in_spec_ind, 'N')) AcceptCF, GMD_QCR02USR_XMLP_PKG.textarray2cfformula(gr1.text_code) textarray2CF, GMD_QCR02USR_XMLP_PKG.SPEC_CP_p SPEC_CP, GMD_QCR02USR_XMLP_PKG.Accept_CP_p Accept_CP, GMD_QCR02USR_XMLP_PKG.Final_CP_p Final_CP, 'x' dummy, 'y' dummy, 'z' dummy FROM mtl_system_items_kfv msi, mtl_lot_Numbers mln, gmd_samples gs, gmd_results gr1, gmd_qc_tests_b gt, gmd_spec_results gsr WHERE gs.inventory_item_id = msi.inventory_item_id AND gs.organization_id = msi.organization_id AND gs.inventory_item_id = mln.inventory_item_id AND gs.organization_id = mln.organization_id AND gs.organization_id = :p_organization_Id AND nvl(gs.lot_number(+),0) = nvl(mln.lot_number,0) AND gs.sample_id = gr1.sample_id AND gr1.test_id NOT IN (select gst.test_id from gmd_spec_tests_b gst, gmd_sampling_events gse,gmd_event_spec_disp ges where gse.sampling_event_id = gs.sampling_event_id AND gse.sampling_event_id = ges.sampling_event_id AND ges.event_spec_disp_id = gsr.event_spec_disp_id AND ges.spec_used_for_lot_attrib_ind = 'Y' AND ges.spec_id(+) = gst.spec_id) AND gr1.result_date IS NOT NULL AND gr1.result_id = gsr.result_id AND gr1.test_id = gt.test_id AND gs.cust_id IS NULL AND gs.supplier_id IS NULL AND gs.batch_id IS NULL AND gs.formula_id IS NULL AND gs.routing_id IS NULL AND gs.oprn_id IS NULL AND gr1.delete_mark = 0 AND gs.delete_mark = 0 &SampleCP &ItemCP &LotnoCP &SubinventoryCP &LocatorCP &DateCP &IncludeCP /*ORDER BY 1, 3, 5, 6, 9, 11 DESC*/ ORDER BY 1 ASC,2 ASC,14 ASC,6 ASC,5 ASC,3 ASC,4 ASC,22 ASC,8 ASC,29 ASC,27 ASC,25 ASC,26 ASC,28 ASC,24 ASC,7 ASC,21 ASC,23 ASC , 1 , 3 , 5 , 6 , 9 , 11 DESC |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization |
|
LOV Oracle | |
From Sample |
|
LOV Oracle | |
To Sample |
|
LOV Oracle | |
From Item |
|
LOV Oracle | |
To Item |
|
LOV Oracle | |
From Lot |
|
LOV Oracle | |
To Lot |
|
LOV Oracle | |
From Subinventory |
|
LOV Oracle | |
To Subinventory |
|
LOV Oracle | |
From Locator |
|
LOV Oracle | |
To Locator |
|
LOV Oracle | |
From Result Date |
|
Date | |
To Result Date |
|
Date | |
Include |
|
LOV Oracle | |
Print Condition |
|
LOV Oracle |