Thursday, November 7, 2013

Unprocessed Material Query


  SELECT 'Unprocessed Material' ||'~'||
       Ood.Organization_code ||'~'||
       to_char(Mmtt.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
       decode(trunc(sysdate)-trunc(mmtt.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
       Mmtt.Transaction_temp_id ||'~'||
       Msi.Segment1 ||'~'||
       Mmtt.Transaction_quantity ||'~'||
       Mtst.Transaction_source_type_name ||'~'||
       Mtt.Transaction_type_name ||'~'||
       Mmtt.Error_code ||'~'||
       replace(Mmtt.Error_explanation,chr(10),' ') ||'~'||
       Mmtt.Process_flag ||'~'||
       Mmtt.Lock_flag ||'~'||
       Mmtt.Transaction_mode ||'~'||
       Mmtt.Organization_id  l_out
  From Mtl_material_transactions_temp Mmtt,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi,
       Mtl_txn_source_types Mtst,
       Mtl_transaction_types Mtt
 Where Mmtt.Organization_id = Ood.Organization_id
   And Msi.Inventory_item_id = Mmtt.Inventory_item_id
   And Msi.Organization_id = Mmtt.Organization_id
   And Mtst.Transaction_source_type_id = Mmtt.Transaction_source_type_id
   And Mtt.Transaction_type_id = Mmtt.Transaction_type_id
   and ood.organization_id     = nvl (p_org_id,ood.organization_id)
   And Mmtt.Process_flag in ('Y','E')
   and nvl(mmtt.transaction_status,0) <> 2
   and (:p_minutes is null or mmtt.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, Mmtt.creation_date;

No comments:

Post a Comment

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