Wednesday 19 September 2018

OPEN PO Details (Standard PO)

SELECT poh.type_lookup_code source_type,
    prh.segment1 pr_number,
    trunc(prh.creation_date) pr_date,
    (select action_date from po_action_history
        where object_type_code='REQUISITION' and action_code='APPROVE'
        and employee_id=prh.preparer_id and object_id=prh.requisition_header_id ) pr_approved_date,
     papf.full_name buyer,
        poh.segment1 po_number,
    poh.creation_date po_creation_date,
    aps.vendor_name supplier_name,
    apss.vendor_site_code supplier_site,
    hla.location_code ship_to_location,
    hla1.location_code bill_to_location,
    poh.authorization_status po_status,
    (select sum(pl.quantity*pl.unit_price) from po_lines_all pl where pl.po_header_id=poh.po_header_id) po_value,
    pol.line_num,
    mtl.segment1 item_code,
    pol.item_description item_description,
    pol.quantity quantity,
    pol.unit_meas_lookup_code uom,
    poll.need_by_date,
    pol.unit_price unit_price,
    (poll.quantity * poll.price_override) amount,
    pap.segment1 project,
    pap.name project_name,
    pat.task_number project_task,
    pat.task_name task_name,
    poll.quantity po_qty,
    poll.quantity_received,
    poll.quantity_billed
FROM
    po_requisition_headers_all prh,
    po_requisition_lines_all prl,
    po_req_distributions_all prd,
    po_headers_all poh,
    po_lines_all pol,
    po_line_locations_all poll,
    po_distributions_all pod,
    ap_suppliers aps,
    ap_supplier_sites_all apss,
    hr_locations hla,
    hr_locations hla1,
    mtl_system_items_b mtl,
    per_all_people_f papf,
    pa_projects_all pap,
    pa_tasks pat
WHERE
    1 = 1
    AND prl.requisition_header_id = prh.requisition_header_id --(+)
    AND prd.requisition_line_id = prl.requisition_line_id --(+)
    AND pod.req_distribution_id = prd.distribution_id --(+)
    AND poll.line_location_id = pod.line_location_id
    AND pol.po_line_id = poll.po_line_id
    AND pol.po_header_id = pod.po_header_id
    AND pol.po_line_id = pod.po_line_id
    AND poh.po_header_id = pol.po_header_id
    AND aps.vendor_id = poh.vendor_id
    AND poh.vendor_site_id = apss.vendor_site_id
    AND aps.vendor_id = apss.vendor_id
    AND hla.location_id(+) = poh.ship_to_location_id
    AND hla1.location_id(+)=poh.bill_to_location_id
    AND mtl.inventory_item_id = pol.item_id
    AND mtl.organization_id = poll.ship_to_organization_id
    AND poh.agent_id = papf.person_id
    AND trunc(poh.creation_date) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
    AND poh.type_lookup_code='STANDARD'
    AND pap.project_id(+) = NVL (pod.project_id, -1)
    AND pat.task_id(+)=NVL(pod.task_id,-1)
    and poh.authorization_status='APPROVED'
    AND poh.cancel_flag <> 'Y'
    AND NVL(poh.closed_code,'OPEN')='OPEN'
    AND NVL(pol.closed_code,'OPEN')='OPEN'

No comments:

Post a Comment