Oracle eBS經驗分享

PO Header Control Document Function

一個PO shipment quantities 全部收料完畢後，Oracle  的workflow會將該shipment line 的close_code自動變更狀態為”close for receiving”，如果ㄧ張PO 有多個Line，部分收完料，你就會在同一張PO 不同的Shipment line (Table :PO_LINE_LOCATIONS_ALL) 看到有些shipment Close_code值，有些卻沒有。

但如果我們去PO Header 執行PO Close for Receiving.此時Oracle 會把PO_LINE_LOCATIONS_ALL下所有的Close code update 成'CLOSED FOR RECEIVING'；相同的，如果我們使用PO Header Control Document Function執行Open

此時所有該PO 下的Shipment 狀態全部還原成Open , Close_code全部會被清空。換言之，所以就算之前曾經有自動Close for Receiving的狀態，也會被清除。

所以Open PO 的Statusㄧ定要參照到PO_LINE_LOCATIONS_ALL的Close_Code

下方是我用來查詢 Open PO SQL Query跟大家分享

SELECT pha.org_id, pha.po_header_id, (SELECT full_name
                                        FROM hr_employees
                                       WHERE employee_id = pha.agent_id)
                                                                        buyer
                                                                             -- , PHA.PO_HEADER_ID
       ,
       pla.po_line_id, plla.line_location_id, pv.vendor_name,
       (SELECT vendor_site_code
          FROM po_vendor_sites_all pos
         WHERE pos.vendor_site_id = pha.vendor_site_id
           AND pos.vendor_id = pha.vendor_id) vendor_site_code,
       msib.planner_code, pha.segment1 po_number, pla.line_num,
       plla.shipment_num, pha.comments header_comment, msib.segment1 item,
       pla.item_description, pla.unit_price, plla.quantity,
       plla.quantity_received received_qty,
       plla.quantity_cancelled cancelled_qty,
         plla.quantity
       - (plla.quantity_received + plla.quantity_cancelled) unreceived_qty,
       pha.currency_code, plla.quantity * pla.unit_price amount,
       plla.promised_date, plla.need_by_date,
       plla.attribute1 supplier_ship_date, plla.attribute2 invoice,
       pha.authorization_status, pha.closed_date,
       plla.ship_to_organization_id,
       mp.organization_code ship_to_org         --PLLA.SHIP_TO_ORGANIZATION_ID
  --   , PLLA.CLOSED_CODE
FROM   po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       mtl_system_items_b msib,
       mtl_parameters mp,
       po_vendors pv
WHERE pha.po_header_id = pla.po_header_id
   AND pla.po_line_id = plla.po_line_id
   AND msib.inventory_item_id = pla.item_id
   AND msib.organization_id = plla.ship_to_organization_id
   AND pha.type_lookup_code = 'STANDARD'
   AND pha.authorization_status = 'APPROVED'
   -- AND PHA.CLOSED_DATE IS NULL
   AND plla.ship_to_organization_id = mp.organization_id
   AND pha.vendor_id = pv.vendor_id
   -- AND MP.ORGANIZATION_CODE = 'HKG'
   AND pha.org_id = 88
   AND NVL (plla.closed_code, 'OPEN') NOT IN
          ('CLOSED FOR INVOICE',
           'FINALLY CLOSED',
           'CLOSED FOR RECEIVING',
           'CLOSED'
          )
   AND NVL (pla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
   AND NVL (pha.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
   AND plla.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
   AND NVL (plla.cancel_flag, 'N') = 'N'
   AND NVL (pla.cancel_flag, 'N') = 'N'
   AND NVL (pha.cancel_flag, 'N') = 'N'
   AND plla.quantity > plla.quantity_received + plla.quantity_cancelled