GMD Production Test Results

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Production Assay Results Report of OPM QC
Application: Process Manufacturing Product Development
Source: Production Test Results Report (XML)
Short Name: QCR03USR_XML
DB package: GMD_QCR03USR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Parameter Name SQL text Validation
Organization
 
LOV Oracle
From Sample
 
LOV Oracle
To Sample
 
LOV Oracle
From Batch
 
LOV Oracle
To Batch
 
LOV Oracle
From Formula
 
LOV Oracle
To Formula
 
LOV Oracle
From Formula Version
 
LOV Oracle
To Formula Version
 
LOV Oracle
From Routing
 
LOV Oracle
To Routing
 
LOV Oracle
From Routing Version
 
LOV Oracle
To Routing Version
 
LOV Oracle
From Routing Step Number
 
LOV Oracle
To Routing Step Number
 
LOV Oracle
From Operation
 
LOV Oracle
To Operation
 
LOV Oracle
From Item
 
LOV Oracle
To Item
 
LOV Oracle
From Lot
 
LOV Oracle
To Lot
 
LOV Oracle
From Result Date
 
Date
To Result Date
 
Date
Include
 
LOV Oracle
Print Condition
 
LOV Oracle