Query to Find Requisition Details :

Query to Find Requisition Details :

SELECT prl.line_num                                 Line
,      plt.line_type                                Line_Type
,      prl.item_id                            prl_item_id
,      msi.segment1                           Item
,      prl.item_revision                            Rev
,      prl.need_by_date                             Need_By_Date
,      prl.unit_meas_lookup_code                    Unit
,      round(prl.quantity,2)         Quantity_Amount
,      prl.unit_price                               Unit_Price
,      DECODE (PRL.order_type_lookup_code,                              /* <SERVICES FPJ> */
                       'FIXED PRICE', PRL.amount,
                       'RATE', PRL.amount,
                       NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT
,      ppf.full_name                                Requestor
,      plc.displayed_field                         Source_Type
,      decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '||prl.source_subinventory,'VENDOR',prh.segment1||' - '||prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '||prl.suggested_vendor_contact||' - '||prl.suggested_buyer_id,null)                                  Source
,      prl.item_description                         Item_Description
,      prd.req_line_quantity                        Distributions
,      prl.justification                            Justification
,      prl.requisition_header_id
,      prl.requisition_line_id
FROM   po_requisition_headers         prh
,      po_requisition_lines           prl
,      po_req_distributions           prd
,      po_line_types                  plt
,      per_people_f                   ppf
,      org_organization_definitions   ood
,      po_lookup_codes           plc
,      mtl_system_items               msi
,      mtl_categories                 mca
,      gl_code_combinations           gcc,      financials_system_parameters   fsp
,      po_system_parameters           psp
WHERE  prh.segment1 = '1713'
AND    prl.requisition_line_id      = prd.requisition_line_id
AND    prl.requisition_header_id    = prh.requisition_header_id
AND    prl.line_type_id             = plt.line_type_id
AND    prl.to_person_id             = ppf.person_id (+)
AND    prl.source_organization_id   = ood.organization_id(+)
AND    plc.lookup_type = 'REQUISITION SOURCE TYPE'
AND    plc.lookup_code = prl.source_type_code
AND    nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0)
                                                                           from financials_system_parameters fsp)
AND    trunc(sysdate)
         BETWEEN nvl(ppf.effective_start_date, trunc(sysdate))
          AND nvl(ppf.effective_end_date, trunc(sysdate))                
AND    prl.item_id                  = msi.inventory_item_id(+)
AND    msi.organization_id = 204
AND    prl.category_id              = mca.category_id
AND    prd.code_combination_id      = gcc.code_combination_id
AND    nvl(prl.modified_by_agent_flag,'N') = 'N'
AND    nvl(prl.cancel_flag,'N') != 'Y'
AND    nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'
ORDER BY prl.line_num