Query for the Details Of Requisition In Oracle APPS R12

 

Interface Tables:

• PO_REQUISITIONS_INTERFACE_ALL
• PO_REQ_DIST_INTERFACE_ALL
• PO_INTERFACE_ERRORS where interface_type =’REQIMPORT’ (Error Table)

Base Tables:

• PO_REQUISITIONS_HEADERS_ALL
• PO_REQUISITION_LINES_ALL
• PO_REQ_DISTRIBUTIONS_ALL

Concurrent Program:

• Requisition Import

SELECT hou.name      operating_unit ,
       prh2.segment1 requisition_number ,
       prh2.description ,
       msi2.segment1              item_code ,
       prl2.line_num              pr_line_num ,
       prl2.item_description      req_line_desc ,
       prl2.unit_meas_lookup_code uom ,
       prh2.authorization_status  req_status ,
       ppf.full_name              preparer ,
       prh2.type_lookup_code      requisition_type ,
       hout.name                  organization ,
       hla.location_code          location ,
       ppf2.full_name             requisiter ,
       prl2.quantity ,
       prl2.unit_price unit_cost
FROM   apps.po_requisition_headers_all prh2 ,
       apps.po_requisition_lines_all prl2 ,
       apps.per_all_people_f ppf ,
       apps.per_all_people_f ppf2 ,
       apps.mtl_system_items_b msi2 ,
       apps.hr_locations_all hla ,
       apps.hr_all_organization_units_tl hout ,
       apps.mtl_categories mic ,
       apps.hr_operating_units hou
WHERE  1=1
AND    hou.organization_id=prh2.org_id
AND    Nvl(prh2.cancel_flag,'N')<>'Y'
AND    Nvl(prl2.cancel_flag,'N')<>'Y'
AND    hout.organization_id(+) = prl2.destination_organization_id
AND    hout.LANGUAGE(+) = Userenv ('LANG')
AND    ppf2.person_id(+)=prl2.to_person_id
AND    prh2.org_id=:P_ORG_ID
AND    hla.location_id(+) = prl2.deliver_to_location_id
AND    mic.category_id(+)=prl2.category_id
AND    msi2.inventory_item_id=prl2.item_id
AND    msi2.inventory_item_id(+) = prl2.item_id
AND    msi2.organization_id(+) = prl2.destination_organization_id
AND    prl2.requisition_header_id=prh2.requisition_header_id
AND    ppf.person_id=prh2.preparer_id
AND    Trunc(SYSDATE) BETWEEN ppf.effective_start_date AND    ppf.effective_end_date
AND    Trunc(SYSDATE) BETWEEN ppf2.effective_start_date(+) AND    ppf2.effective_end_date(+)
AND    prh2.segment1 : :P_Requisition_Number;