GMD Item/Location Required Analysis

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