Thursday, November 7, 2013

Pending Material Query


   Select 'Pending Material' ||'~'||
       ood.Organization_code ||'~'||
       to_char(Mti.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
            Mti.Transaction_interface_id ||'~'||
       Msi.Segment1 ||'~'||
       Mti.Transaction_quantity ||'~'||
       Mtst.Transaction_source_type_name ||'~'||
       Mtt.Transaction_type_name ||'~'||
       Mti.Error_code ||'~'||
       mti.created_by ||'~'||
       mti.request_id||'~'||
       mti.subinventory_code||'~'||
       Mti.Process_flag ||'~'||
       Mti.Lock_flag||'~'||
       Mti.Transaction_mode||'~'||
       Mti.Validation_required||'~'||
       replace(Mti.Error_explanation,chr(10),' ') ||'~'||
       Mti.Organization_id  l_out
  From Mtl_transactions_interface Mti,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi,
       Mtl_txn_source_types Mtst,
       Mtl_transaction_types Mtt
 Where Mti.Organization_id = Ood.Organization_id
   And Msi.Inventory_item_id = Mti.Inventory_item_id
   And Msi.Organization_id = Mti.Organization_id
   And Mtst.Transaction_source_type_id = Mti.Transaction_source_type_id
   And Mtt.Transaction_type_id = Mti.Transaction_type_id
   And Mti.process_flag <> 9
   and ood.organization_id = nvl(p_org_id,ood.organization_id)
   and (:p_minutes is null or mti.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, mti.creation_date;

No comments:

Post a Comment

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