GMD Production Test Results

SELECT
                pbh.batch_no,
                ffm.formula_no,
                ffm.formula_vers,
                '['||ffm.formula_desc1||']' formula_desc1,
                frh.routing_no,
                frh.routing_vers,
                '['||frh.routing_desc||']' routing_desc,
                gs.step_id  routingstep_id,
                gs. oprn_id,
                gs. routing_id,
                msi.concatenated_segments item_no,
                '['|| msi.description ||']' item_desc1,
                 gs.revision,
                mln.lot_number  LOT,
                '['||mln.description||']' lot_desc,
                fmd.line_type,
                '['||DECODE(line_type,1,'Product',-1,'Ingredient',2,'By Product')||']' type_desc,
                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',
                               NVL(gst.print_result_ind, 'N'), 'Y',
                               'N'),'N')  final_mark,
                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,
                gr1.result_id qc_result_id,
                gst.spec_id  qc_spec_id,
                gs.charge charge ,
                nvl(gst.report_precision, gt.report_precision) precision,
                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   ,
                gt.text_code test_text_code
                , &OprnnoCP
                ,&RoutstepCP ,
	GMD_QCR03USR_XMLP_PKG.stepcfformula(gs.routing_id, gs.step_id) StepCF,
	GMD_QCR03USR_XMLP_PKG.oprn_verscfformula0017(gs.oprn_id) Oprn_versCF,
	GMD_QCR03USR_XMLP_PKG.operationcfformula(gs.oprn_id) OperationCF,
	GMD_QCR03USR_XMLP_PKG.assay_desccfformula(gt.test_code) assay_descCF,
	GMD_QCR03USR_XMLP_PKG.assay_displaycfformula(gt.test_code, gst.test_id, :targetCF) assay_displayCF,
	GMD_QCR03USR_XMLP_PKG.assaymin_dispcfformula(gst.test_id, :min_specCF) assaymin_dispCF,
	GMD_QCR03USR_XMLP_PKG.assaymax_dispcfformula(gst.test_id, :max_specCF) assaymax_dispCF,
	GMD_QCR03USR_XMLP_PKG.textcfformula(gst.target_value_char) textCF,
	GMD_QCR03USR_XMLP_PKG.targetcfformula(gst.target_value_char, gst.target_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision )) targetCF,
	GMD_QCR03USR_XMLP_PKG.qcunit_codecfformula(gst.spec_id, gt.test_code, gst.test_id) qcunit_codeCF,
	GMD_QCR03USR_XMLP_PKG.min_speccfformula(gst.min_value_char, gst.min_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision )) min_specCF,
	GMD_QCR03USR_XMLP_PKG.min_charcfformula(gst.min_value_char) min_charCF,
	GMD_QCR03USR_XMLP_PKG.max_speccfformula(gst.max_value_char, gst.max_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision )) max_specCF,
	GMD_QCR03USR_XMLP_PKG.max_charcfformula(gst.max_value_char) max_charCF,
	GMD_QCR03USR_XMLP_PKG.from_datecfformula(gs.sample_no) from_dateCF,
	GMD_QCR03USR_XMLP_PKG.to_datecfformula(gs.sample_no) to_dateCF,
	GMD_QCR03USR_XMLP_PKG.textarraycfformula(:textcodeCF) textarrayCF,
	GMD_QCR03USR_XMLP_PKG.text_codecfformula(gst.spec_id) textcodeCF,
	GMD_QCR03USR_XMLP_PKG.assay_typeCP_p assay_typeCP,
	GMD_QCR03USR_XMLP_PKG.result_numcfformula(gr1.result_value_char, gr1.result_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision )) result_numCF,
	GMD_QCR03USR_XMLP_PKG.result_dispcfformula(gst.test_id, gr1.result_value_num) result_dispCF,
	GMD_QCR03USR_XMLP_PKG.accept_cfformula(decode(gsr.evaluation_ind,'0A','Y','N')) Accept_CF,
GMD_QCR03USR_XMLP_PKG.Final_cfformula(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_CF,
	GMD_QCR03USR_XMLP_PKG.textcodersltcfformula(gr1.result_id, gt.test_code) textcodersltCF,
	GMD_QCR03USR_XMLP_PKG.textarray2cfformula(gr1.text_code) textarray2CF
FROM       pm_btch_hdr pbh,
                fm_form_mst ffm,
                fm_rout_hdr frh,
                fm_matl_dtl fmd,
                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
                &OprntblCP
                &RoutsteptblCP
    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  pbh.batch_id(+)    = gs.batch_id
                AND  ffm.formula_id(+)  = gs.formula_id
                AND  frh.routing_id(+)  = gs.routing_id
                AND  fmd.formula_id(+)  = gs.formula_id
                AND  fmd.formulaline_id(+) = gs.formulaline_id
                AND  gs.sample_id          = gr1.sample_id
                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.batch_id    IS NOT NULL
                      OR gs.formula_id  IS NOT NULL
                      OR gs.routing_id  IS NOT NULL
                      OR gs.oprn_id     IS NOT NULL
		      )
                AND  gr1.delete_mark = 0
                AND  gs.delete_mark = 0
                AND gsr.delete_mark =0
                AND ges.delete_mark =0
                     &SampleCP
                     &BatchCP
                     &FormulaCP
                     &Formula_VersCP
                     &RoutingCP
                     &Routing_VersCP
                     &ItemCP
                     &LotnoCP
                     &Date1CP
                     &OprnCP
                     &Rout_StepnoCP
                     &Final1CP
     /* ORDER BY    pbh.batch_no,
                  ffm.formula_no,
                  ffm.formula_vers,
                  frh.routing_no,
                  frh.routing_vers,
                  &OrderCP
		  item_no,
		  LOT ,
                  fmd.line_type,
                  gt.test_code,
                  gr1.result_date
                 DESC*/
ORDER BY 1 ASC,2 ASC,3 ASC,4
  ASC,5 ASC,6 ASC,7 ASC,10 ASC,9 ASC,13 ASC,11 ASC,12 ASC,33 ASC,8 ASC,16 ASC,
  17 ASC,31 ASC,14 ASC,15 ASC,35 ASC,19 ASC,41 ASC,37 ASC,20 ASC,38 ASC,42
  ASC , pbh.batch_no , ffm.formula_no , ffm.formula_vers , frh.routing_no ,
  frh.routing_vers , item_no , LOT , fmd.line_type , gt.test_code ,
  gr1.result_date DESC