GMD Customer/Supplier Test Results

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Product Development
Source: Customer/Supplier Test Results Report (XML)
Short Name: QCR01USR_XML
DB package: GMD_QCR01USR_XMLP_PKG
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
Parameter Name SQL text Validation
SY ALL
 
Print Condition
 
LOV Oracle
Include
 
LOV Oracle
To Sample
 
LOV Oracle
From Sample
 
LOV Oracle
To Result Date
 
Date
From Result Date
 
Date
To Lot
 
LOV Oracle
From Lot
 
LOV Oracle
To Item
 
LOV Oracle
From Item
 
LOV Oracle
Organization
 
LOV Oracle
To Customer/Supplier
 
LOV Oracle
From Customer/Supplier
 
LOV Oracle
Report Type
 
LOV Oracle
Ask a question