Purchase Order Status Report

CREATE OR REPLACE FORCE VIEW apps.xxaye_po_order_status_disc
AS   create or replace view XXAYE_PO_ORDER_STATUS_DISC
   as
   SELECT apps.xxaly_scm_general_pkg.get_operating_unit (pha.org_id)
                                                               operating_unit,
          pha.segment1 po_number, TRUNC (pha.creation_date) po_date,
          pll.promised_date, TO_CHAR (pll.promised_date, 'YYYY') promise_year,
          pha.authorization_status po_status, pv.vendor_name, xtm.item_code,
          xtm.description, xtm.CATEGORY category_seg1,
          xtm.sub_category cat_seg2,
          xtm.CATEGORY || '.' || xtm.sub_category CATEGORY,
          pll.quantity qty_ordered, pll.quantity_received qty_received,
          pll.quantity - pll.quantity_received bal_qty, NULL grn_num,
          NULL grn_date, NULL grn_qty,xxh.user_name created_by,null receiving_organization
     FROM apps.po_headers_all pha,
          apps.po_lines_all pla,
          apps.po_vendors pv,
          apps.po_line_locations_all pll,
          apps.xx_mtl_master_items_v xtm,
          apps.xxaly_hr_user_person_v xxh
    WHERE pha.po_header_id = pla.po_header_id
      AND pll.po_header_id(+) = pla.po_header_id
      AND pll.po_line_id(+) = pla.po_line_id
      AND pla.po_line_id NOT IN (SELECT NVL (rsl.po_line_id, 0)
                                   FROM apps.rcv_shipment_lines rsl
                                  WHERE pla.po_line_id = rsl.po_line_id)
      AND pv.vendor_id(+) = pha.vendor_id
      AND xtm.inventory_item_id(+) = pla.item_id
      AND pha.created_by = xxh.user_id      
   UNION
   SELECT apps.xxaly_scm_general_pkg.get_operating_unit (pha.org_id)
                                                               operating_unit,
          pha.segment1 po_number, TRUNC (pha.creation_date) po_date,
          pll.promised_date, TO_CHAR (pll.promised_date, 'YYYY') promise_year,
          pha.authorization_status po_status, pv.vendor_name, xtm.item_code,
          xtm.description, xtm.CATEGORY category_seg1,
          xtm.sub_category cat_seg2,
          xtm.CATEGORY || '.' || xtm.sub_category CATEGORY,
          pll.quantity qty_ordered, pll.quantity_received qty_received,
          pll.quantity - pll.quantity_received bal_qty,
          rsh.receipt_num grn_num, TRUNC (rt.transaction_date) grn_date,
          rsl.quantity_received grn_qty,xxh.user_name created_by,ood.organization_name receiving_organization
     FROM apps.po_headers_all pha,
          apps.po_lines_all pla,
          apps.rcv_transactions rt,
          apps.rcv_shipment_headers rsh,
          apps.rcv_shipment_lines rsl,
          apps.po_vendors pv,
          apps.po_line_locations_all pll,
          apps.xx_mtl_master_items_v xtm,
          apps.xxaly_hr_user_person_v xxh,
          apps.org_organization_definitions ood
    WHERE pha.po_header_id = pla.po_header_id
      AND pha.po_header_id = rt.po_header_id
      AND pla.po_line_id = rt.po_line_id
      AND rsh.shipment_header_id = rt.shipment_header_id
      AND rt.transaction_type = 'RECEIVE'
      AND pv.vendor_id = pha.vendor_id
      AND pll.po_header_id(+) = pla.po_header_id
      AND pll.po_line_id(+) = pla.po_line_id
      AND xtm.inventory_item_id(+) = pla.item_id
      AND rsh.shipment_header_id = rsl.shipment_header_id
      AND rsl.po_line_id = pla.po_line_id
      AND pha.created_by = xxh.user_id
      AND ood.organization_id = rt.organization_id ;
/