GMD Item/Location Test Results

➜ Back to Library
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
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
Download
Related Blitz Reports
GMD Customer/Supplier Test Results GMD Production Test Results GMD Item/Location Required Analysis ECC Genealogy and Trace, Process Batch ECC Process Genealogy and Trace, Process Batch ECC Cost Management, WIP Valuation (Process) OPM Transactions, Reservations, Pending Product Lots OPM WIP Value OPM2
Blitz Report™

Blitz Report™ provides multiple benefits: