Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010

    Unanswered: ERP: POs and PRs for a particular project and expenditure type‏

    Hi , I need to write a query for all POs and PRs for a particular project and all its expenditure types.I tried this one but if I add the expenditure types I am getting multiple lines.I cant join pa_projects_all and pa_expenditures_items_all. Could anyone help please.

    SELECT rh.segment1 requisition_no, rh.description,
    rh.type_lookup_code document_type, rh.attribute_category CATEGORY,
    rl.item_description, rl.unit_meas_lookup_code unit,
    rl.unit_price req_price, rl.quantity req_qty, si.segment1 item_code,
    rl.justification, rl.destination_type_code, pa.segment1 project_no,
    pa.project_type, pa.start_date project_start_date,
    pa.completion_date project_completion_date, pt.task_number,
    po.segment1 po_no, pl.unit_price po_unit_price, pl.quantity po_qty,
    rl.suggested_vendor_name, pa.project_id, pt.task_id,
    si.inventory_item_id, si.organization_id, po.authorization_status,
    rh.authorization_status ir_approval_status,
    pa.org_id project_organization
    FROM po_requisition_headers_all rh,
    po_requisition_lines_all rl,
    po_req_distributions_all rd,
    pa_projects_all pa,
    pa_tasks pt,
    po_distributions_all d,
    po_headers_all po,
    po_lines_all pl,
    mtl_system_items si
    WHERE pa.segment1 = '14962'
    and rh.requisition_header_id = rl.requisition_header_id
    AND rh.attribute_category = 'PROJECTS'
    AND pa.project_id = NVL (rl.attribute6, rd.project_id)
    AND pt.task_id = NVL (rl.attribute7, rd.task_id)
    AND rd.requisition_line_id = rl.requisition_line_id
    AND d.req_distribution_id(+) = rd.distribution_id
    AND po.po_header_id(+) = d.po_header_id
    AND d.po_line_id = pl.po_line_id(+)
    AND si.inventory_item_id = NVL (rl.item_id, pl.item_id)
    AND si.organization_id = '192'

  2. #2
    Join Date
    May 2010
    Use this link - it gives detail description of how to extract information from tables as per the requirements.

    PO: Tips and useful Query

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts