Thursday, November 7, 2013

Uncosted Material Query


Select 'Uncosted Material' ||'~'||
       Ood.Organization_code ||'~'||
       to_char(Mmt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
       decode(trunc(sysdate)-trunc(mmt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
       Mmt.Transaction_id||'~'||
       Msi.Segment1 ||'~'||
       Mmt.Transaction_quantity ||'~'||
       Mtst.Transaction_source_type_name ||'~'||
       Mtt.Transaction_type_name ||'~'||
       Mmt.Error_code ||'~'||
       replace(Mmt.Error_explanation,chr(10),' ') ||'~'||
       Mmt.Costed_flag ||'~'||
       Mmt.Cost_update_id ||'~'||
       Mmt.Transaction_group_id ||'~'||
       Mmt.Organization_id  l_out
 From Mtl_material_transactions Mmt,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi,
       Mtl_txn_source_types Mtst,
       Mtl_transaction_types Mtt
 Where Mmt.Organization_id             = Ood.Organization_id
   And Msi.Inventory_item_id           = Mmt.Inventory_item_id
   And Msi.Organization_id             = Mmt.Organization_id
   And Mtst.Transaction_source_type_id = Mmt.Transaction_source_type_id
   And Mtt.Transaction_type_id         = Mmt.Transaction_type_id
   and ood.organization_id             = nvl (p_org_id,ood.organization_id)
   And Mmt.costed_flag                 in ('E','N')
   and (:p_minutes is null or  mmt.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, Mmt.creation_date;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.