GMD Item/Location Test Results

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Product Development
Source: Item/Location Test Results Report (XML)
Short Name: QCR02USR_XML
DB package: GMD_QCR02USR_XMLP_PKG
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
SY ALL
 
Print Condition
 
LOV Oracle
Include
 
LOV Oracle
To Result Date
 
Date
From Result Date
 
Date
To Locator
 
LOV Oracle
From Locator
 
LOV Oracle
To Subinventory
 
LOV Oracle
From Subinventory
 
LOV Oracle
To Lot
 
LOV Oracle
From Lot
 
LOV Oracle
To Item
 
LOV Oracle
From Item
 
LOV Oracle
To Sample
 
LOV Oracle
From Sample
 
LOV Oracle
Organization
 
LOV Oracle