[SYS] SQL for Internal Requisition (IR)

select 'IR' as req_type,pla.attribute1 as lot_no,mpv.project_number,pha.segment1 doc_no,
pla.line_num,pda.distribution_num dist_num,pha.description doc_type,trunc(pha.creation_date) ord_date,

trunc( pla.need_by_date) pro_date, msi.segment1 item_no,pla.item_revision item_rev,

msi.description item_desc,pla.quantity, pla.unit_price,
pla.quantity_delivered qty_rcvd, nvl(pla.quantity,0)-nvl(pla.quantity_delivered,0) os_qty,
nvl(pla.closed_code,'OPEN') closed_code
from po.po_requisition_headers_all pha, po.po_requisition_lines_all pla, po.po_req_distributions_all pda, apps.mrp_project_v mpv, apps.mtl_system_items msi, gl.gl_code_combinations gl
where pha.type_lookup_code='INTERNAL'
and pha.authorization_status='APPROVED'
and pla.destination_organization_id in (100,101) --destination organization, parties that perform receiving
and pla.source_organization_id=103 --source organization, party that supply the item
and pha.requisition_header_id=pla.requisition_header_id
and pla.requisition_line_id=pda.requisition_line_id
and pda.code_combination_id=gl.code_combination_id
and pla.item_id=msi.inventory_item_id
and pla.destination_organization_id=msi.organization_id
and pda.project_id = mpv.project_id (+) --if project is used
order by req_type,doc_no,line_num,dist_num;