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;

Using Oracle Applications Flexfields in your custom forms:

Using Oracle Applications Flexfields in your custom forms: step-by-step.

There are several complicated things you may need to do with flexfields in your forms but one of them is simple and very common: create a foreign key on the accounting flexfield. It is easy to get lost in the Oracle Application Developer's Guide because it tries to cover comprehensively what can be done with flexfields. But there is nothing to show you the basics step-by-step, which is what we will attempt in this article. The end result of what we show is illustrated in Figure 1: one field that brings up the standard flexfield popup when the user asks for a list of values.

Figure 1: The end result of this step-by-step article.

A basic conceptual understanding of what flexfields are is necessary in order to understand what follows. There is a very good 2^1/2 page explanation in the chapter "Flexfields" of the Oracle Application Developer's Guide.

Step 1: Create a FK Column

First you need to define a column in the base table maintained by your form. This column will be the foreign key on the unique ID of the combination table, in our case: GL_CODE_COMBINATIONS. So you need a number(38) mandatory column in your table.

Step 2: Create a Flexfield hidden item

In your form you need to create a hidden item for the FK column. This field needs to be hidden, that is set to canvas null. It should use the TEXT_ITEM property class which comes from the Oracle Application template. Set the query length to 2000 just to be on the safe side. Figure 2 shows an example of such hidden ID.

Figure 2: Hidden field to hold the Flexfield reference.

Step 3: Create a Flexfield display item

Now we want to create a non-database text item that will display the concatenated values of the segments of the accounting field. This item should use the TEXT_ITEM property class and be assigned to the appropriate canvas where you want users to see it. You then need to assign to it the dummy LOV 'ENABLE_LIST_LAMP' which comes from the Oracle Application template. Make sure that property 'Validate from list' is set to No. This ensures that the List lamp works properly for your flexfield. Figure 3 shows an example of such displayed field. We only show the most relevant item properties.

Figure 3: Displayed field to show the concatenated values of the Flexfield.

Step 4: Create the Flexfield definition

Then there is a little bit of PL/SQL to do in order to define your flexfield. This is done with the WHEN-NEW-FORM-INSTANCE trigger. It is always a good practice to do the actual work in a procedure or package as is illustrated in listing A. There are several reasons for this but this is out of the scope of this article. Note: the code in listing A is an abbreviated version of the real code: we have left out the comments and the error handling in order to keep this article as short as possible.

procedure initialize is      cursor get_cao is         select  to_char(id_flex_num)         from    fnd_id_flex_structures         where   id_flex_structure_code = 'ACCOUNTING_FLEXFIELD'                 and id_flex_code = 'GL#';      v_cao    varchar2(1000);             begin     open get_cao;     fetch get_cao into v_cao;     if get_cao%notfound then         v_cao := '101';     end if;     close get_cao;         app_standard.event('WHEN-NEW-FORM-INSTANCE');     fnd_key_flex.define(       BLOCK=>'COMBO',       FIELD=>'ACCOUNT_FLEXFIELD',       APPL_SHORT_NAME=>'SQLGL',       CODE=>'GL#',       NUM=>v_cao,       ID=>'CODE_COMBINATION_ID',       DESCRIPTION=>'',       TITLE=>'____ Your nice user friendly title here _____',       VALIDATE=>'FULL',       QBE_IN=>'Y',       DERIVE_ALWAYS=>'Y',       updateable => '',      VRULE=>'\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN',       where_clause => 'summary_flag != ''Y''',       QUERY_SECURITY =>'Y');   end initialize;

Listing A: Dynamic Flexfield definition

Step 5: Code several event triggers

Then, all that is left to do is to program a few events:

• WHEN-VALIDATE-ITEM
• WHEN-NEW-ITEM-INSTANCE
• POST-QUERY
• PRE-QUERY
• KEY-LISTVAL

The code is shown in Listing B.

We always try to put this code in the form level triggers as it is more convenient and consistent than doing it at block or item level, especially when you have more than one foreign key flexfield in the form. Only in the case of very large and complicated forms would we do otherwise.

If you are updating someone else's form, you may need to check that no block or item triggers are overriding your form level triggers. Check also the execution style of your form level triggers. Whether your trigger should fire in mode Before, After or Override will depend on your context.

WHEN-VALIDATE-ITEM     if ( :system.mode = 'NORMAL' ) then        fnd_flex.event( 'WHEN-VALIDATE-ITEM' );     end if; WHEN-NEW-ITEM-INSTANCE     app_standard.event('WHEN-NEW-ITEM-INSTANCE');     fnd_flex.event('WHEN-NEW-ITEM-INSTANCE' ); POST-QUERY --Loads the flexfields (in our case, it populates --the concatenated field on execute query).     FND_FLEX.EVENT('POST-QUERY'); PRE-QUERY --If you don't do this, whatever query criteria you may enter in -- the concatenated flex field, it is not taken into account.     FND_FLEX.EVENT('PRE-QUERY' ); KEY-LISTVAL     APP_STANDARD.EVENT('KEY-LISTVAL');     FND_FLEX.EVENT('KEY-LISTVAL' );

Listing B: Event programming for the Flexfields.

Et voilà! With only these 5 steps you can achieve the result illustrated in figure 1. What we have shown only scratches the surface of what can be done with Oracle Application flexfields but it provides an easy-to-follow introduction.