GMD Item/Location Required Analysis

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Product Development
Source: Item/Location Required Analysis Report (XML)
Short Name: QCR05USR_XML
DB package: GMD_QCR05USR_XMLP_PKG
SELECT
        msi.concatenated_segments item_no,         
        '['|| msi.description ||']' Item,          
        msi.inventory_item_Id item_id,             
        mln.lot_number  Lotno,                     
        '['||mln.description||']' Lot,             
        mln.status_id      lot_status_id ,         
        stat.status_code     lot_status,         
        '['||stat.description||']' Status,       
        sub.secondary_inventory_name Subinventory, 
        '[ '||sub.description ||' ]' Sub_Desc,     
        loc.concatenated_segments Location1,       
        '[ '||loc.description ||' ]' Location,     
        loc.inventory_location_id locator_Id,      
        t.test_code  assay_code  ,   
        st.target_value_num  target_spec, 
        NVL(TO_CHAR(st.min_value_num), st.min_value_char) min_spec,    
        NVL(TO_CHAR(st.max_value_num),st.max_value_char) max_spec,  
        NVL(st.report_precision, t.report_precision) precision,  
        t.test_unit,      
        st.target_value_char text_spec,   
        st.text_code,   
        t.text_code,
        t.test_type,
        s.spec_name,
        mln.expiration_date,        
        mln.retest_date,             
       mln.expiration_action_code,  
       '[ ' || decode (nvl(mln.expiration_action_code,'0'),'0','',( select description FROM mtl_actions 
        WHERE action_code = mln.expiration_action_code)) || ' ]'    Action,     
        mln.expiration_action_date, 
	GMD_QCR05USR_XMLP_PKG.cf_maxformula(NVL(TO_CHAR(st.max_value_num),st.max_value_char), t.test_type, NVL(st.report_precision, t.report_precision)) CF_max, 
	GMD_QCR05USR_XMLP_PKG.cf_minformula(NVL(TO_CHAR(st.min_value_num), st.min_value_char),  t.test_type,NVL(st.report_precision, t.report_precision)) CF_min, 
	GMD_QCR05USR_XMLP_PKG.cf_targetformula(st.target_value_num, t.test_type, NVL(st.report_precision, t.report_precision)) CF_target
FROM    GMD_SPECIFICATIONS_B   s,
        GMD_SPEC_TESTS_B      st,
        GMD_INVENTORY_SPEC_VRS  si,
        GMD_QC_TESTS_B   t,
        MTL_SYSTEM_ITEMS_KFV msi,          
        MTL_LOT_NUMBERS mln,               
        MTL_SECONDARY_INVENTORIES sub,     
        MTL_ITEM_LOCATIONS_KFV  loc,       
        MTL_MATERIAL_STATUSES stat,      
        MTL_ONHAND_QUANTITIES_DETAIL  moqd 
WHERE           
             moqd.organization_id    = msi.organization_id 
        AND  moqd.inventory_item_id  = msi.inventory_item_id 
        AND  moqd.organization_id    = mln.organization_id
        AND  moqd.inventory_item_id  = mln.inventory_item_id
        AND  moqd.lot_number         = mln.lot_number
        AND  moqd.organization_id    = sub.organization_id 
        AND  moqd.subinventory_code  = sub.secondary_inventory_name
        AND  moqd.organization_id    = loc.organization_id 
        AND  moqd.locator_id         = loc.inventory_location_id 
        AND  mln.status_id (+)       = stat.status_id 
        AND  s.spec_id               = st.spec_id
        AND  s.spec_id               = si.spec_id
        AND  st.test_id              = t.test_id
        AND  t.delete_mark           = 0   
        AND  s.delete_mark           = 0
        AND moqd.primary_transaction_quantity > 0  
       AND si.spec_vr_id = GMD_SPEC_MATCH_GRP.GET_INV_SPEC_OR_VR_ID( moqd.inventory_item_id    
				,NULL     
                                                                 ,mln.grade_code       
                                                                 ,moqd.organization_id      
                                                                 ,moqd.subinventory_code        
				 , NULL  
                                                                 ,moqd.lot_number         
                                                                 ,moqd.locator_id      
                                                                 ,SYSDATE          
                                                                 ,'N'              
                                                                 ,NULL             
                                                                ,'SPECVRID'     )  
             &ExpiredCF
             &Retest_ReqCF
             &Future_ExpiryCF
             &FutureRetestCF
             &Exclusive_TestsCF
             &lot_statusCF   
             &SubinventoryCF    
ORDER BY 
         item_no,       
         lotno,         
         Subinventory,  
         location1,      
        assay_code
Parameter Name SQL text Validation
SY ALL
 
Exclusive Within
 
Number
Exclusive Test
 
LOV Oracle
Retest Within
 
Number
Future Retest
 
LOV Oracle
Expire Within
 
Number
Future Expire
 
LOV Oracle
Retest Items
 
LOV Oracle
Expired Items
 
LOV Oracle
To Status
 
LOV Oracle
From Status
 
LOV Oracle
To Subinventory
 
LOV Oracle
From Subinventory
 
LOV Oracle
Organization
 
LOV Oracle