Thursday, November 7, 2013

Pending WIP Costing Query



 SELECT 'Pending WIP Costing'||'~'||
        Ood.Organization_code ||'~'||
        to_char(Wip.Creation_date,'DD-MON-RRRR HH24:MI:SS') ||'~'||
        decode(trunc(sysdate)-trunc(Wip.Creation_date),0,'',1,'',2,'','Escalate')||'~'||
        Wip.Transaction_id ||'~'||
        Wip.Source_code ||'~'||
        Msi.Segment1 ||'~'||
        Wip.Transaction_quantity ||'~'||
        Wip.Wip_entity_name ||'~'||
        Wip.Created_by_name ||'~'||
        Wip.Transaction_type ||'~'||
        replace(substr(wiperr.error_message,1,46),chr(10),' ')||'~'||
        Wip.Group_id ||'~'||
        Wip.Request_id ||'~'||
        Wip.Process_status ||'~'||
        Wip.Organization_id  l_out
  From Wip_cost_txn_interface Wip,
       Wip_txn_interface_errors Wiperr,
       Org_organization_definitions Ood,
       Mtl_system_items_b Msi
 Where Wip.Organization_id = Ood.Organization_id
   And Msi.Inventory_item_id(+) = Wip.Primary_item_id
   And Msi.Organization_id(+) = Wip.Organization_id
   And Wip.Transaction_id = Wiperr.Transaction_id(+)
   And Wip.Process_status in (1,3)
   And Wip.Acct_period_id is not null
   And ood.organization_id = nvl(p_org_id,ood.organization_id)
   And (:p_minutes is null or wip.creation_date < SYSDATE - (:p_minutes/(24*60)) )
   order by ood.organization_code, Wip.creation_date;

No comments:

Post a Comment

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