GMD Customer/Supplier Test Results

SELECT  cust.account_number,    
        '['||cust.account_name||']' cust_name ,          
         msi.concatenated_segments item_number,  
        '['|| msi.description ||']' item_desc1, 
        mln.lot_number,                          
        '['||mln.description||']' lot_desc,     
        gt.test_type, 
        gt.test_code assay_code,          
        gr1.text_code,       
        gt.test_unit qcunit_code,        
        gr1.result_date, 
        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 qc_spec_id,               
        gst.test_id  qcassy_typ_id,             
        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,
        decode(gsr.evaluation_ind,'0A','Y','N') accept_anyway,
        gs.ship_to_site_id,
        gs.order_id order_header_id,       
        gs.order_line_id       ,            
        gs.order_line_id order_line  ,      
        gs.org_id order_org_id, 
	GMD_QCR01USR_XMLP_PKG.operating_unitcfformula(gs.org_id) operating_unitCF, 
	GMD_QCR01USR_XMLP_PKG.ship_to_sitecfformula(gs.ship_to_site_id) ship_to_siteCF, 
	GMD_QCR01USR_XMLP_PKG.order_headercfformula(gs.order_id) order_headerCF,
	GMD_QCR01USR_XMLP_PKG.order_typeCP_p order_typeCP, 
	GMD_QCR01USR_XMLP_PKG.assay_desccfformula(gt.test_code) assay_descCF, 
	GMD_QCR01USR_XMLP_PKG.cf_minformula(gt.test_type, nvl ( gst.report_precision , gt.report_precision ), gst.min_value_num, gst.min_value_char) CF_min, 
	GMD_QCR01USR_XMLP_PKG.cf_maxformula(gt.test_type, nvl ( gst.report_precision , gt.report_precision ), gst.max_value_num, gst.max_value_char) CF_max, 
	GMD_QCR01USR_XMLP_PKG.textcfformula(gst.target_value_char) textCF, 
	GMD_QCR01USR_XMLP_PKG.targetcfformula(gst.target_value_char, gst.target_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision )) targetCF, 
	GMD_QCR01USR_XMLP_PKG.target_dispcfformula(gt.test_code, gst.test_id,GMD_QCR01USR_XMLP_PKG.targetcfformula(gst.target_value_char, gst.target_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision ))) target_dispCF, 
	GMD_QCR01USR_XMLP_PKG.texttargetformula(GMD_QCR01USR_XMLP_PKG.textcfformula(gst.target_value_char),GMD_QCR01USR_XMLP_PKG.targetcfformula(gst.target_value_char, gst.target_value_num, gt.test_type, nvl ( gst.report_precision , gt.report_precision ))) texttargetCF, 
	GMD_QCR01USR_XMLP_PKG.qcunit_codecfformula(gst.spec_id, gt.test_code, gst.test_id) qcunit_codeCF, 
	GMD_QCR01USR_XMLP_PKG.min_speccfformula(gst.spec_id, gt.test_code, gst.min_value_char, gst.min_value_num) min_specCF, 
	GMD_QCR01USR_XMLP_PKG.min_charcfformula(gst.min_value_char) min_charCF, 
	GMD_QCR01USR_XMLP_PKG.min_dispcfformula(gst.test_id, GMD_QCR01USR_XMLP_PKG.max_specformula(gst.max_value_char, gst.spec_id, gt.test_code, gst.max_value_num)) min_dispCF, 
	GMD_QCR01USR_XMLP_PKG.max_specformula(gst.max_value_char, gst.spec_id, gt.test_code, gst.max_value_num) max_specCF, 
	GMD_QCR01USR_XMLP_PKG.max_charcfformula(gst.max_value_char) max_charCF, 
	GMD_QCR01USR_XMLP_PKG.max_dispcfformula(gst.test_id, GMD_QCR01USR_XMLP_PKG.max_specformula(gst.max_value_char, gst.spec_id, gt.test_code, gst.max_value_num)) max_dispCF, 
	GMD_QCR01USR_XMLP_PKG.from_datecfformula(gs.sample_no) from_dateCF, 
	GMD_QCR01USR_XMLP_PKG.todateformula(gs.sample_no) to_dateCF, 
	GMD_QCR01USR_XMLP_PKG.textarraycfformula(GMD_QCR01USR_XMLP_PKG.textcodecfformula(gst.spec_id)) textarrayCF, 
	GMD_QCR01USR_XMLP_PKG.textcodecfformula(gst.spec_id) TextcodeCF,
	GMD_QCR01USR_XMLP_PKG.assay_typeCP_p assay_typeCP, 
	GMD_QCR01USR_XMLP_PKG.result1cfformula(gr1.result_value_char, gr1.result_value_num) Result1CF, 
	GMD_QCR01USR_XMLP_PKG.result_dispcfformula(gst.test_id, gr1.result_value_num) result_dispCF, 
	GMD_QCR01USR_XMLP_PKG.accept_finalcfformula(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' )) Accept_FinalCF, 
	GMD_QCR01USR_XMLP_PKG.text_resultcfformula(gr1.result_value_char) text_resultCF, 
	GMD_QCR01USR_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_QCR01USR_XMLP_PKG.textarray2cfformula(gr1.text_code) textarray2CF,
	GMD_QCR01USR_XMLP_PKG.Accept_CP_p Accept_CP,
	GMD_QCR01USR_XMLP_PKG.Final_CP_p Final_CP,
	'x' dummy,'y' dummy
FROM    
        hz_cust_accounts_all  cust, 
        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                    
             cust.cust_account_id   = gs.cust_id
        AND  gs.supplier_id  IS  NULL
        AND  gs.inventory_item_id = msi.inventory_item_id    
        AND  gs.organization_id   = msi.organization_id      
        AND  gs.inventory_item_id = mln.inventory_item_id(+)     -- plowe 10122626  
        AND  gs.organization_id   = mln.organization_id(+)       -- plowe 10122626  
        AND  gs.organization_id  = :p_organization_Id        
        AND  nvl(gs.lot_number,0) = nvl(mln.lot_number(+),0)      -- plowe 10122626  
        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.batch_id      IS NULL  
        AND  gs.formula_id    IS NULL  
        AND  gs.routing_id    IS NULL  
        AND  gs.oprn_id       IS NULL 
        AND  cust.status     = 'A'
        AND  gr1.delete_mark  = 0     
        AND  gs.delete_mark  = 0    
        &Date1CP
        &Final1CP
        &Cust_VendCP_1
        &ItemCP
        &LotnoCP
        &SampleCP
ORDER BY 
        cust.account_number,
        gs.org_id, 
        gs.ship_to_site_id,  
        gs.order_id,   
        gs.order_line_id,
        item_number,        
        mln.lot_number ,    
        gt.test_code, 
        gr1.result_date  DESC