PO Approval History Query in Oracle apps

Action History on PO and REQ  

SELECT DISTINCT prha.segment1 req

              , pha.segment1 po

              , gcc.segment3 acct

              , acct_desc.description acct_desc

              , gcc.segment4 ctr

              , ctr_desc.description ctr_desc

              , papf.full_name req_raised_by

              , reqah.full_name req_last_approver

              , poah.full_name po_last_approver

              , pv.vendor_name supplier

              , pvsa.vendor_site_code site

           FROM po.po_requisition_headers_all prha

              , po.po_requisition_lines_all prla

              , po.po_line_locations_all plla

              , po.po_lines_all pla

              , po.po_headers_all pha

              , po.po_distributions_all pda

              , gl.gl_code_combinations gcc

              , apps.po_vendors pv

              , apps.po_vendor_sites_all pvsa

              , apps.fnd_flex_values_vl acct_desc

              , apps.fnd_flex_values_vl ctr_desc

              , hr.per_all_people_f papf

              , (SELECT papf.full_name

                      , pah.action_code

                      , pah.object_id

                   FROM po.po_action_history pah

                      , po.po_requisition_headers_all prha

                      , applsys.fnd_user fu

                      , hr.per_all_people_f papf

                  WHERE object_id = prha.requisition_header_id

                    AND pah.employee_id = fu.employee_id

                    AND fu.employee_id = papf.person_id

                    AND SYSDATE BETWEEN papf.effective_start_date

                                    AND papf.effective_end_date

                    AND pah.object_type_code = 'REQUISITION'

                    AND pah.action_code = 'APPROVE'

                    AND pah.sequence_num =

                           (SELECT MAX(sequence_num)

                              FROM po.po_action_history pah1

                             WHERE pah1.object_id = pah.object_id

                               AND pah1.object_type_code = 'REQUISITION'

                               AND pah1.action_code = 'APPROVE')) reqah

              , (SELECT papf.full_name

                      , pah.action_code

                      , pah.object_id

                   FROM po.po_action_history pah

                      , po.po_headers_all pha

                      , applsys.fnd_user fu

                      , hr.per_all_people_f papf

                  WHERE object_id = pha.po_header_id

                    AND pah.employee_id = fu.employee_id

                    AND fu.employee_id = papf.person_id

                    AND SYSDATE BETWEEN papf.effective_start_date

                                    AND papf.effective_end_date

                    AND pah.object_type_code = 'PO'

                    AND pah.action_code = 'APPROVE'

                    AND pah.sequence_num =

                           (SELECT MAX(sequence_num)

                              FROM po.po_action_history pah1

                             WHERE pah1.object_id = pah.object_id

                               AND pah1.object_type_code = 'PO'

                               AND pah1.action_code = 'APPROVE')) poah

          WHERE prha.requisition_header_id = prla.requisition_header_id

            AND prla.line_location_id = plla.line_location_id

            AND plla.po_header_id = pla.po_header_id

            AND pla.po_header_id = pha.po_header_id

            AND pla.po_line_id = pda.po_line_id

            AND pda.code_combination_id = gcc.code_combination_id

            AND reqah.object_id = prha.requisition_header_id

            AND poah.object_id = pha.po_header_id

            AND prha.preparer_id = papf.person_id

            AND gcc.segment3 = acct_desc.flex_value

            AND gcc.segment4 = ctr_desc.flex_value

            AND pha.vendor_id = pv.vendor_id

            AND pha.vendor_site_id = pvsa.vendor_site_id

            AND pv.vendor_id = pvsa.vendor_id

            AND SYSDATE BETWEEN papf.effective_start_date

                            AND papf.effective_end_date

            AND prha.creation_date >= '01-APR-2009'

            AND prha.creation_date <= '03-APR-2009'

       ORDER BY prha.segment1

              , gcc.segment4

Checking Requisition Approval History for a specific member of staff

SELECT   pah.action_code

       , pah.object_id

       , pah.action_date

       , pah.sequence_num step

       , pah.creation_date

       , prha.segment1 req_num

       , prha.wf_item_key

       , prha.authorization_status

       , fu.description

       , papf.full_name hr_full_name

       , papf.employee_number emp_no

       , pj.NAME job

    FROM po.po_action_history pah

       , po.po_requisition_headers_all prha

       , applsys.fnd_user fu

       , hr.per_all_people_f papf

       , hr.per_all_assignments_f paaf

       , hr.per_jobs pj

   WHERE object_id = prha.requisition_header_id

     AND pah.employee_id = fu.employee_id

     AND fu.employee_id = papf.person_id

     AND papf.person_id = paaf.person_id

     AND paaf.job_id = pj.job_id

     AND paaf.primary_flag = 'Y'

     AND SYSDATE BETWEEN papf.effective_start_date ANDpapf.effective_end_date

     AND SYSDATE BETWEEN paaf.effective_start_date ANDpaaf.effective_end_date

     AND pah.object_type_code = 'REQUISITION'

     AND pah.action_code = 'APPROVE'

     AND papf.full_name = :pn

ORDER BY pah.creation_date desc;

 

Checking Purchase Order Approval History for a specific member of staff

SELECT   pah.action_code

       , pah.object_id

       , pah.action_date

       , pah.sequence_num step

       , pah.creation_date

       , pha.segment1 po_num

       , fu.description

       , papf.full_name hr_full_name

       , papf.employee_number emp_no

       , papf.person_id

       , fu.user_name

       , pj.NAME job

    FROM po.po_action_history pah

       , po.po_headers_all pha

       , applsys.fnd_user fu

       , hr.per_all_people_f papf

       , hr.per_all_assignments_f paaf

       , hr.per_jobs pj

   WHERE object_id = pha.po_header_id

     AND pah.employee_id = fu.employee_id

     AND fu.employee_id = papf.person_id

     AND papf.person_id = paaf.person_id

     AND paaf.job_id = pj.job_id

     AND paaf.primary_flag = 'Y'

     AND SYSDATE BETWEEN papf.effective_start_date ANDpapf.effective_end_date

     AND SYSDATE BETWEEN paaf.effective_start_date ANDpaaf.effective_end_date

     AND pah.object_type_code = 'PO'

     AND pah.action_code = 'APPROVE'

     AND papf.full_name = :pn

ORDER BY pah.sequence_num;

 

Mass Close of Purchasing Documents

HOW DOES THE CLOSE ACTION WORK?

Purchasing allows documents to be closed in the two following methods:

•    Manually from the Control Documents window.

•    Automatically based on the receiving and invoicing activity

Overview of the Close Action from the Control Documents Window

Purchasing documents can be Closed, Closed for Receiving, Closed for Invoicing or Finally Closed at the Header, Lines and Shipment levels.  Requisitions can only be Finally Closed

To manually close a purchasing document from the Purchasing responsibility navigate to Purchase Order Summary or Requisition Summary form and navigate to the Control Document window by selecting Control from the Tools menu. 

For purchase orders, you can navigate to the Control Document window from the summary windows titled Purchase Order Headers, Purchase Order Lines, and Purchase Order Shipments. For requisitions Finally Close is only available from the Requisition Headers and Requisition Lines Summary windows.

Navigation: Purchasing responsibility > Purchase Orders> Purchase Order Summary OR Purchasing responsibility > Requisitions > Requisition Summary

1. Query the document
2. Select the control Action (Tools -> Control) at header, line or shipment level
3. Select the close action
4. Optionally enter a Reason and a note to supplier for the action

 What are the different Closure Actions?

Requisitions:  The only close action available for Requisitions is Final Close.

Purchase Orders:  For a full list of the close actions that can be performed on each of the different Purchasing document types 

•    Close
A purchase order is automatically closed once it is received (if you require a receipt) and is billed for all purchase order shipments. Since you do not require or expect any further activity, Purchasing closes the purchase order. You can also manually close the purchase order early if you do not expect further activity. Adding lines to it or receiving against it, reopens the purchase order. Purchasing does not consider closed purchase orders for accruals.

Close can be done at the header, release, line, and shipment levels.

•    Close for Invoicing
A purchase order control that you can assign manually or that Purchasing can assign automatically when the amount invoiced reaches a defined percentage of the order quantity.

You can close for invoicing at the header, release, line, and shipment levels. 

Note: that if you close for invoicing a purchase order header that is currently open, Purchasing sets the status of all purchase order shipments to Closed for Invoicing, but the purchase order lines and header remain open. This status will not prevent you from matching an invoice to the purchase order or to the receipt.

Close for Receiving
A purchase order control you can assign manually or that Purchasing can assign automatically when the amount received reaches a defined percentage of the order quantity.

You can close for receiving at the header, release, line, and shipment levels.

Finally Close
A purchase order control you can assign to prevent modifications to or actions against completed documents, lines, and shipments by finally closing them. Finally-closed documents are not accessible in the corresponding entry windows, and you cannot perform the following actions against finally-closed entities: receive, transfer, inspect, deliver, correct receipt quantities, invoice, return to supplier, or return to receiving.  Finally Close documents can not be reversed so consideration should be given when performing this action.

You can choose finally close at the header, release, line, and shipment levels.

If you are using encumbrance or budgetary control, Finally Close automatically creates credit encumbrance journal entries in your general ledger system to reverse the encumbrance that corresponds to the purchase order header, lines, or shipments you are closing, using the action date you specify in Action Date. See: Budgetary Control and Online Funds Checking, Oracle General Ledger User's Guide.

What Close Actions are valid for each document type and entity?

Current Document Type	Finally Close	Close	Close for Invoice	Close for Receipt
Planned PO Header	X	X	X	X
Planned PO Line	X	X	X	X
Planned PO Shipment	X	X	X	X
Standard PO Header	X	X	X	X
Standard PO Line	X	X	X	X
Standard PO Shipment	X	X	X	X
Blanket Agreement Header	X	X
Blanket Agreement Line	X	X
Contract Agreement Header	X	X
Release Header	X	X
Release Shipment	X	X	X	X
Requisition Header	X	X
Requisition Line	X	X

What is Mass close of purchasing Documents

The Mass Close of Purchasing Documents allows for Purchasing Documents to be closed via a concurrent request rather than having to use the Purchase Order Summary - Tools/Control. It is available in Release 12.1, and per Oracle Product Management is not being considered for backport to Release 11.5 or Release 12.0. The functionality is built upon the 12.1.1 code level and cannot be backported.

A new program “Mass Close of Purchase Document” will allow users to mass close Purchase documents. The Mass Close functionality applies to Purchase Orders that have been fully processed i.e. batch processing has completed, but are not in the closed status as well as for Purchase Orders that the user needs to manually close. The program is applicable to Standard Purchase Orders, Blanket Purchase Agreements, Planned Purchase Orders and Contract Agreement and Blanket Releases

The program has an array of parameters that can be used, that allows for the mass close of a large range of purchase orders based on numbering, or a date range can be used

The program does not conduct a Final Close (Finally Closed)- only Close.  

The Mass Close of Purchasing Documents concurrent program enables you to change the status of purchase orders to Closed.

This is applicable to purchase orders that do not have a status of Closed, that is, the Mass Close program closes all the shipments that are not already closed, canceled, encumbered or frozen. When you run the concurrent program, the purchase orders are specified are closed completely (this includes headers, lines, shipments).


You can mass close purchase orders with the following statuses:

• Frozen
• On Hold
• Approved
• Reserved
• Closed for Invoicing
• Closed for Receiving.

You can mass close the following documents:

• Standard Purchase Orders
• Blanket Purchase Agreements
• Planned Purchase Orders
• Contract Agreement

For Blanket Releases the functionality is being considered as an Enhancement Request - per Product Management - Bug 12349975 : ER: MASS CLOSE FUNCTIONALITY FOR RELEASES

The parameters for running the concurrent program are Document Type, Document number, Date Range and Supplier Name.A report is generated as output from the concurrent program. This report lists the document number and document type of the Oracle Purchasing documents that were updated by the program."

Testing 

To test the functionality, you can create a purchase order - and approve it.
- Then, move to Purchasing - Reports/Run
- Single Request
- Choose Operating Unit
- Give the purchase order type - then the purchase order number
- Dates are required
- Submit the request

Next, move to Purchase Orders/Purchase Order Summary 
- query the purchase order - and it should now be closed

How to programmatically close PO using PO_ACTIONS API

PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Purchase Order. We have to pass P_ACTION parameter value as 'CLOSE'/'FINALLY CLOSE' depending on the requirement to close a Purchase Order
--Sample wrapper procedure  to close  purchase order
CREATE OR REPLACE xx_po_close_prc(p_header_id NUMBER)
IS

 lv_result BOOLEAN;
 lv_return_code VARCHAR2(20);
 lv_header_id NUMBER := p_header_id;

CURSOR c_po_details IS
SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.closed_code,
pha.closed_date
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND authorization_status = 'APPROVED'
AND pha.closed_code <> 'FINALLY CLOSED'
AND pha.po_header_id = p_header_id; -- Enter the PO_HEADER_ID if one PO needs to be Closed/finally closed

BEGIN

-- Parameters :

  -- p_docid : Header ID for Document
  -- Table: PO_HEADERS_ALL.PO_HEADER_ID  
  -- p_doctyp : Document Type
  -- Table: PO_HEADERS_ALL.TYPE_LOOKUP_CODE
  -- Values : STANDARD, BLANKET, RELEASE
  -- p_lineid : Line ID for Document
  -- Table: PO_LINES_ALL.PO_LINE_ID
  -- p_shipid : Ship ID for Document
  -- Table: PO_LINE_LOCATIONS.LINE_LOCATION_ID
  -- p_action : Action to be performed
  -- Table: PO_ACTION_HISTORY.ACTION_CODE
  -- Values: 'CLOSE' -- Close for Receiving
  --         'INVOICE CLOSE'
  --         'FINALLY CLOSE'
  -- p_reason : Reason for Closing. This must be entered for Manual Closing

  -- p_calling_mode : Whether being invoked from 'PO', 'RCV' or 'AP'. This
  --                  determines which of the Closed States needs to be
  --                  checked (receiving, invoicing or both). This must be
  --                  entered for Auto Closing

  -- p_conc_flag : Whether invoked from a Concurrent Process. This must be
  --               entered for Manual Closing and is used by the Funds Checker

  -- p_return_code : Return Status of PO Closing

  -- p_auto_close : Whether to invoke Auto Closing or Manual Closing

  -- p_action_date is added to function close_po()

FOR po_details_rec IN c_po_details
LOOP
  BEGIN
  lv_result :=    PO_ACTIONS.CLOSE_PO(
          P_DOCID => po_details_rec.po_header_id,
          P_DOCTYP => 'PO',
          P_DOCSUBTYP => 'STANDARD', -- Can be STANDARD, BLANKET, RELEASE
          P_LINEID => NULL, -- If want to close Line
          P_SHIPID => NULL,-- If want to close Shipment
          P_ACTION => 'CLOSE', 
          P_REASON => 'Close Purchase Order ',
          P_CALLING_MODE => po_details_rec.document_type_code,
          P_CONC_FLAG => 'N',
          P_RETURN_CODE => lv_return_code,
          P_AUTO_CLOSE => 'N',
          P_ACTION_DATE => sysdate,
          P_ORIGIN_DOC_ID => NULL );
    IF lv_result = TRUE THEN
      DBMS_OUTPUT.PUT_LINE('Successfully closed PO#'||po_details_rec.segment1);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Cannot close PO#'||po_details_rec.segment1);
    END IF; 
   END;
 END LOOP;
 EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line(' Exception closing PO using PO_ACTIONS.CLOSE_PO'||SQLERRM);
END xx_po_close_prc;