Open Purchase Requisition and Internal Requisition  Query with org-wise

SELECT prha.segment1 Requisition_Number,

  prha.AUTHORIZATION_STATUS Approval_Status,

  prha.CREATION_DATE,

  prha.closed_code,

  prha.TYPE_LOOKUP_CODE Requisition_Type,

  prha.Description Header_description,

  prla.line_num,

  prla.item_descRIPTION,

  prla.unit_meas_lookup_code UOM,

  prla.unit_price,

  prla.quantity,

  prla.need_by_date,

  prla.cancel_flag,

  prla.cancel_date,

  mti.segment1 item_code

FROM apps.PO_REQUISITION_HEADERS_ALL prha,

  apps.po_requisition_lines_all prla,

  apps.mtl_system_items_B mti

WHERE 1                       =1

AND prha.authorization_status = 'APPROVED'

AND prha.TYPE_LOOKUP_CODE     = 'PURCHASE'

AND trunc(prha.creation_date) BETWEEN '01-Jan-2017' AND '05-Jan-2017'

AND prha.closed_code = 'OPEN'

AND prla.CANCEL_FLAG ='N'

and prla.DESTINATION_ORGANIZATION_ID=xxx

UNION

SELECT prha.segment1 Requisition_Number,

  prha.AUTHORIZATION_STATUS Approval_Status,

  prha.CREATION_DATE,

  prha.closed_code,

  prha.TYPE_LOOKUP_CODE Requisition_Type,

  prha.Description Header_description,

  prla.line_num,

  prla.item_descRIPTION,

  prla.unit_meas_lookup_code UOM,

  prla.unit_price,

  prla.quantity,

  prla.need_by_date,

  prla.cancel_flag,

  prla.cancel_date,

  mti.segment1 item_code

FROM apps.PO_REQUISITION_HEADERS_ALL prha,

  apps.po_requisition_lines_all prla,

  apps.mtl_system_items_B mti

WHERE 1                       =1

AND prha.authorization_status = 'APPROVED'

AND prha.TYPE_LOOKUP_CODE     ='INTERNAL'

AND trunc(prha.creation_date) BETWEEN '01-Jan-2017' AND '25-Jan-2017'

AND prha.closed_code = 'OPEN'

--and prha.TRANSFERRED_TO_OE_FLAG ='Y'

AND prla.CANCEL_FLAG='N'

and prla.DESTINATION_ORGANIZATION_ID=xxx;