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;
Regards
ReplyDeleteSridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.