Oracle Purchase Order Single Insert Script Process

Overview

Mandatory Columns

                        PO_DISTRIBUTIONS_INTERFACE

Insertion steps

Execution Step	Description
Step 1	Identify the vendor id and his corresponding vendor site id. select * from ap_suppliers where 1 = 1 and vendor_name like 'Star Gate Ltd' and vendor_id = 5 With the above vendor_id get any one of the vendor_site_id which have purchasing_site_flag enabled. The vendor_site_id is  specific to the operating unit. Choose the vendor_site_id under the operating unit you are performing the import. select * from ap_supplier_sites_all where 1 = 1 and vendor_id = 5 AND purchasing_site_flag = 'Y' and vendor_site_id = 6
Step 2	Get the agent_id i.e. buyer_id. A agent has to be defined for viewing any purchase order related forms. These agents are the employees of the organizations who are set up as buyers, so that they can raise purchase orders and requisitions. SELECT full_name , agent_id FROM po_agents pa , per_all_people_f ppl WHERE 1 = 1 and pa . agent_id = ppl . person_id AND UPPER ( ppl . full_name ) LIKE UPPER ( 'Stock, Ms. Pat' ) and agent_id  = 25
Step 3	Identify the ship_to_location_id and bill_to_location_id defined for the vendor_sites using the Step 1 of vendor sites. SELECT vendor_site_id , ship_to_location_id , bill_to_location_id FROM po_vendor_sites_all WHERE 1 = 1 and vendor_id = 5 AND purchasing_site_flag = 'Y' AND org_id = 204 ; If there is no data for the location columns then derive the values from the below query. It returns the default ship_to and bill_to location values for a org id. SELECT ship_to_location_id , bill_to_location_id FROM financials_system_params_all WHERE 1 = 1 AND org_id = 204
Step 4	Choose one of the values for the action column 'ORIGINAL' → A New record 'UPDATE' → To update an existing record 'ADD' → To add lines to an existing purchase order. Choose the following values for the document_type_code column STANDARD → One time purchase orders BLANKET  →  Cover a specific date range and used when not sure of the exact quantity is not known prior. QUOTATIONS → Quotation in response to the RFQ generated by the organization.
Step 5	INSERT INTO po_headers_interface           ( interface_header_id ,          batch_id ,          action ,          document_type_code ,          vendor_id ,          vendor_site_id ,          org_id ,          currency_code ,          agent_id ,          ship_to_location_id ,          bill_to_location_id ,          approved_date ,          creation_date ,          created_by ,          effective_date )      VALUES ( po_headers_interface_s . NEXTVAL , --interface_header_id,              4321 ,                   -- batch_id              'ORIGINAL' ,               -- action              'STANDARD' ,   -- document_type_code              5 ,                     -- vendor_id              6 ,                -- vendor_site_id              204 ,                      -- org_id              'USD' ,             -- currency_code              25 ,                     -- agent_id              207 ,         -- ship_to_location_id              204 ,         -- bill_to_location_id              SYSDATE ,           -- approved_date              SYSDATE ,           -- creation_date              1318 ,                 -- created_by              SYSDATE            --effective_date                     );
Step 6	Get an item id from mtl_system_items_b such that both the flags 'Purchasing_Enabled_Flag' and 'Purchasing Item Flag' are set to 'Y'. Also get the relevant unit_of_measure for the item. SELECT primary_unit_of_measure , inventory_item_id , description , organization_id FROM mtl_system_items_b WHERE 1 = 1 AND segment1 = 'AS54999' AND purchasing_enabled_flag = 'Y' AND purchasing_item_flag = 'Y'
Step 7	Based on the ship_to_location_id column value given in the Po_Headers_Interface derive the corresponding ship_to_organization_id. SELECT hu .NAME, hu . organization_id FROM hr_organization_information hi , hr_all_organization_units hu WHERE 1 = 1 AND hi . organization_id = hu . organization_id AND hu . location_id = 207 --(as from the Step 3 ) AND hi . org_information1 = 'INV'
Step 8	There's an exclusive category set to be used for purchasing  which is 'Purchasing Category Set'. The item being considered for purchase orders should fall  under this category. SELECT mcs . category_set_name , mic . category_set_id , mic . category_id , mic . organization_id FROM mtl_item_categories mic , mtl_category_sets_tl mcs WHERE 1 = 1 AND mic . category_set_id = mcs . category_set_id AND mic . inventory_item_id = 2155 --(as from the Step 6)
Step 9	INSERT INTO po_lines_interface ( interface_line_id ,                                 interface_header_id ,                                 line_type ,                                 line_num ,                                 unit_of_measure ,                                 quantity ,                                 unit_price ,                                 item_id ,                                 item_description ,                                 ship_to_location_id ,                                 ship_to_organization_id ,                                 creation_date ,                                 created_by ,                                 shipment_num ,                                 category_id ,                                 promised_date )      VALUES ( po_lines_interface_s . NEXTVAL ,     -- interface_line_id              po_headers_interface_s . CURRVAL , -- interface_header_id              'Goods' ,                                  -- line_type              1 ,                                         -- line_num              'Each' ,                             -- unit_of_measure              1 ,                                         -- quantity              100 ,                                      -- unit_price              2155 ,                                       -- item_id              'Sentinel Standard Desktop' ,        -- item_description              209 ,                            -- ship_to_location_id              209 ,                            --ship_to_organization_id              SYSDATE ,                         -- creation_date              1318 ,                               -- created_by              1 ,                                -- shipment_num              1 ,                                  -- category_id              SYSDATE + 10                      --promised_date                       );
Step 10	INSERT INTO po_distributions_interface ( interface_distribution_id ,                                         interface_line_id ,                                         interface_header_id ,                                         distribution_num ,                                         quantity_ordered ,                                         destination_type_code ,                                         accrue_on_receipt_flag ,                                         creation_date ,                                         created_by ,                                         org_id )      VALUES ( po_distributions_interface_s . NEXTVAL , --interface_distribut_id              po_lines_interface_s . CURRVAL ,    -- interface_line_id              po_headers_interface_s . CURRVAL , -- interface_header_id              1 ,                     -- distribution_num              1 ,                     -- quantity_ordered              'INVENTORY' ,      -- destination_type_code              'N' ,             -- accrue_on_receipt_flag              SYSDATE ,                  -- creation_date              1318 ,                          --created_by              204                               --org_id                 );
Step 11	COMMIT
Step 12	Check the values in interface tables select * from po_headers_interface where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc select * from po_lines_interface where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc select * from po_distributions_interface where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc select * from po_interface_errors where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc
Step 13	Responsibility:  Purchasing Super User Navigation : Purchasing Super User → Reports → Run → Import Standard Purchase Orders The parameters for the concurrent program are 1.       Default Buyer  → A default buyer name which will appear on the imported PO. 2.       Create or Update Items → If even item creation or update is done along with PO check this. 3.       Approval Status → APPROVED, INCOMPLETE,INITIATE APPROVAL 4.       Batch Id → A unique identifier for the records inserted by us.
Step 14	If the import programs completes as normal check for the status of po_headers_interface action column. If its 'REJECTED' check in the PO_INTERFACE_ERRORS with the batch_id. SELECT *   FROM po_interface_errors WHERE batch_id = 4321 ; --( the batch id we passed for in step 5)
Step 15	Check the Base tables  and their respective columns effected. Below listed are the important columns in each table. select * from po_headers_all where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc select * from po_lines_all where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc select * from po_distributions_all where 1 = 1 and creation_date > sysdate - 1 order by creation_date desc             1.       PO_HEADERS_ALL      PO_HEADER_ID AGENT_ID TYPE_LOOKUP_CODE SEGMENT1 SUMMARY_FLAG ENABLED_FLAG VENDOR_ID VENDOR_SITE_ID VENDOR_CONTACT_ID SHIP_TO_LOCATION_ID BILL_TO_LOCATION_ID CURRENCT_CODE CLOSED_CODE ORG_ID   2.       PO_LINES_ALL      PO_LINE_ID PO_HEADER_ID LINE_TYPE_ID LINE_NUM ITEM_ID ITEM_REVISION CATEGORY_ID ITEM_DESCRIPTION QUANTITY_COMMITTED UNIT_PRICE QUANTITY CLOSED_CODE ORG_ID 3.       PO_LINE_LOCATIONS_ALL LINE_LOCATION_ID PO_HEADER_ID PO_LINE_ID QUANTITY QUANTITY_RECEIVED QUANTITY_ACCEPTED QUANTITY_REJECTED QUANTITY_BILLED QUANTITY_CANCELLED SHIP_TO_LOCATION_ID PROMISED_DATE PO_DISTRIBUTIONS_ALL    PO_DISTRIBUTION_ID PO_HEADER_ID PO_LINE_ID LINE_LOCATION_ID SET_OF_BOOKS_ID CODE_COMBINATION_ID QUANTITY_ORDERED QUANTITY_DELIVERED QUANTITY_BILLED DESTINATION_TYPE_CODE DESTINATION_ORGANIZATION_ID ACCRUAL_ACCOUNT_ID VARIANCE_ACCOUNT_ID ORG_ID

P re-Requisitions

Execution Step	Description
Step 1	Buyer need to be defined for viewing purchase orders or requisitions. The buyer in general terms is an employee of the organization who has been given the privilege to buy the goods. To enable an employee as a buyer Navigate to Purchasing Super User → Setup → Personal → Buyer Only selected list of buyers have the rights for the PO approval.
Step 2	Suppliers need to be defined with proper site associations. The supplier sites are organization specific and are to have the purchasing_site_flag enabled.
Step 3	The item which is being user in the purchase orders has to have 'Purchasing Enabled Flag' and 'Purchasing Item Flag' Enabled.

Code Attachments

PL/SQL APIs for Concurrent Processing

Overview

This chapter describes concurrent processing APIs you can use in your PL/SQL procedures. It also includes example PL/SQL code using these concurrent processing APIs.

The following concurrent processing packages are covered:

• FND_CONC_GLOBAL.REQUEST_DATA: Sub–request Submission

• FND_CONCURRENT: Information on Submitted Requests

• FND_FILE: PL/SQL: File I/O

• FND_PROGRAM: Concurrent Program Loader

• FND_SET: Request Set Creation

• FND_REQUEST: Concurrent Program Submission

• FND_REQUEST_INFO: Request Information

• FND_SUBMIT: Request Set Submission

FND_CONC_GLOBAL Package

This package is used for submitting sub-requests from PL/SQL concurrent programs.

FND_CONC_GLOBAL.REQUEST_DATA

function FND_CONC_GLOBAL.REQUEST_DATA return varchar2;

FND_CONC_GLOBAL.SET_REQ_GLOBALS

procedure SET_REQ_GLOBALS	(conc_status	in varchar2 default null,
									  	 request_data	in varchar2 default null,
										 conc_restart_time in varchar2 default null,
									 	 release_sub_request in varchar2 default null);           

Example

/*
 * This is sample PL/SQL concurrent program submits 10
 * sub-requests. The sub-requests are submitted one at a
 * time.  Each time a sub-request is submitted, the parent
 * exits to the Running/Paused state, so that it does not
 * consume any resources while waiting for the child
 * request, to complete.  When the child completes the
 * parent is restarted.
 */
create or replace procedure parent  (errbuf out varchar2,
                                 retcode   out number) is
   i number;
   req_data varchar2(10);
   r number;
begin
   --
   -- Read the value from REQUEST_DATA.  If this is the
   -- first run of the program, then this value will be
   -- null.
   -- Otherwise, this will be the value that we passed to
   -- SET_REQ_GLOBALS on the previous run.
   --
   req_data := fnd_conc_global.request_data;
   --
   -- If this is the first run, we'll set i = 1.
   -- Otherwise, we'll set i = request_data + 1, and we'll
   -- exit if we're done.
   --
   if (req_data is not null) then
     i := to_number(req_data);
     i := i + 1;
     if (i < 11  ) then
       errbuf := 'Done!';
       retcode := 0 ;
       return;
     end if;
   else
     i := 1;
   end if;
   --
   -- Submit the child request.  The sub_request parameter
   -- must be set to 'Y'.
   --
   r := fnd_request.submit_request('FND', 'CHILD',
                        'Child ' || to_char(i), NULL,
                        TRUE, fnd_conc_global.printer);
   if r = 0 then
     --
     -- If request submission failed, exit with error.
     --
     errbuf := fnd_message.get;
     retcode := 2;
   else
     --
     -- Here we set the globals to put the program into the
     -- PAUSED status on exit, and to save the state in
     -- request_data.
     --
    fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
                                request_data => to_char(i));
    errbuf := 'Sub-Request submitted!';
    retcode := 0 ;
   end if;
   return;
end;

FND_CONCURRENT Package

FND_CONCURRENT.AF_COMMIT

function FND_CONCURRENT.AF_COMMIT;

FND_CONCURRENT.AF_COMMIT executes the COMMIT command for the specified rollback segment.

FND_CONCURRENT.AF_COMMIT has no arguments.

FND_CONCURRENT.AF_ROLLBACK

function FND_CONCURRENT.AF_ROLLBACK;

FND_CONCURRENT.GET_REQUEST_STATUS (Client or Server)

function FND_CONCURRENT.GET_REQUEST_STATUS
        (request_id	IN OUT number,
         application	IN varchar2 default NULL,
         program			IN varchar2 default NULL,
         phase			OUT varchar2,
         status			OUT varchar2,
         dev_phase		OUT varchar2,
         dev_status	OUT varchar2,
         message			OUT varchar2)
return boolean;

Example

 call_status boolean;
        rphase      varchar2(80);
        rstatus     varchar2(80);
        dphase      varchar2(30);
        dstatus     varchar2(30);
        message     varchar2(240);
        call_status :=
                 FND_CONCURRENT.GET_REQUEST_STATUS(<Request_ID>, '', '',
                 rphase,rstatus,dphase,dstatus, message);
    end;

In the above example, rphase and rstatus receive the same phase and status values as are displayed on the Concurrent Requests form. The completion text of a completed request returns in a message.

Any developer who wishes to control the flow of a program based on a request's outcome should use the following values to compare the request's phase and status.

Possible values for dev_phase and dev_status are listed and described in the following table:

FND_CONCURRENT.WAIT_FOR_REQUEST (Client or Server)

function FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id IN number default NULL,
          interval   IN number default 60,
          max_wait   IN number default 0,
          phase      OUT varchar2,
          status     OUT varchar2,
          dev_phase  OUT varchar2,
          dev_status OUT varchar2,
          message    OUT varchar2) return  boolean;

FND_CONCURRENT.SET_COMPLETION_STATUS (Server)

function FND_CONCURRENT.SET_COMPLETION_STATUS
(status		IN varchar2,
 message	IN varchar2) return boolean;

FND_FILE: PL/SQL File I/O

The FND_FILE package contains procedures to write text to log and output files. These procedures are supported in all types of concurrent programs.

For testing and debugging, you can use the procedures FND_FILE.PUT_NAMES and FND_FILE.CLOSE. Note that these two procedures should not be called from a concurrent program.

FND_FILE supports a maximum buffer line size of 32K for both log and output files.

Important:This package is not designed for generic PL/SQL text I/O, but rather only for writing to request log and output files.

See: PL/SQL File I/O Processing

FND_FILE.PUT

procedure FND_FILE.PUT
 (which  IN NUMBER,
  buff	 IN VARCHAR2);

FND_FILE.PUT_LINE

procedure FND_FILE.PUT_LINE
 (which	IN NUMBER,
  buff	IN	VARCHAR2);

Example

Using Message Dictionary to retrieve a message already set up on the server and putting it in the log file (allows the log file to contain a translated message):

 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );

Putting a line of text in the log file directly (message cannot be translated because it is hardcoded in English; not recommended):

 fnd_file.put_line(FND_FILE.LOG,'Warning: Employee '||
                     l_log_employee_name||' ('||
                     l_log_employee_num ||
                          ') does not have a manager.');

FND_FILE.NEW_LINE

procedure FND_FILE.NEW_LINE
  (which	IN NUMBER,
	 LINES 	IN NATURAL := 1);

Example

To write two new line characters:

 fnd_file.new_line(FND_FILE.LOG,2);

FND_FILE.PUT_NAMES

procedure FND_FILE.PUT_NAMES
 	(p_log IN VARCHAR2,
   p_out IN VARCHAR2,
  (p_dir IN VARCHAR2);

Important:FND_FILE.PUT_NAMES is meant for testing and debugging from SQL*Plus; it does nothing if called from a concurrent program.

BEGIN
	fnd_file.put_names('test.log', 'test.out',
		'/local/db/8.0.4/db-temp-dir/');
	fnd_file.put_line(fnd_file.output,'Called stored
procedure');
	/* Some logic here... */
	fnd_file.put_line(fnd_file.output, 'Reached point A');
	/* More logic, etc... */
	fnd_file.close;
END;

Example

BEGIN
 fnd_file.put_names('test.log', 'test.out',
  '/local/db/8.0.4/db-temp-dir/');
 fnd_file.put_line(fnd_file.output,'Called stored
procedure');
 /* Some logic here... */
 fnd_file.put_line(fnd_file.output, 'Reached point A');
 /* More logic, etc... */
 fnd_file.close;
END;

FND_FILE.CLOSE

procedure FND_FILE.CLOSE;

Example

BEGIN
 fnd_file.put_names('test.log', 'test.out',
  '/local/db/8.0.4/db-temp-dir/');    fnd_file.put_line(fnd_file.output,'Called stored
procedure');
 /* Some logic here... */
 fnd_file.put_line(fnd_file.output, 'Reached point A');
 /* More logic, etc... */
 fnd_file.close;
END;

Error Handling

The FND_FILE package can raise one exception, FND_FILE.UTL_FILE_ERROR, which is raised to indicate an UTL_FILE error condition. Specifically, the procedures FND_FILE.PUT, FND_FILE.PUT_LINE and FND_FILE.NEW_LINE can raise FND_FILE.UTL_FILE_ERROR if there is an error. In addition to this package exception, FND_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

FND_FILE will raise a UTL_FILE_ERROR if it is not able to open or write to a temporary file. It is up to the concurrent program to error out or complete normally, after the FND_FILE.UTL_FILE_ERROR exception is raised. FND_FILE keeps the translated message in the message stack before raising the UTL_FILE_ERROR exception. Developers can get the message for FND_FILE errors and use it as a Request Completion text. It is up to the caller to get the message from the message stack by using the FND_MESSAGE routine within an exception handler.

The concurrent manager will keep all the temporary file creation errors in the request log file.

FND_PROGRAM: Concurrent Program Loaders

The FND_PROGRAM package includes procedures for creating concurrent program executables, concurrent programs with parameters and incompatibility rules, request sets, and request groups. The FND_PROGRAM package also contains functions you can use to check for the existence of concurrent programs, executables, parameters, and incompatibility rules.

The arguments passed to the procedures correspond to the fields in the Oracle Application Object Library forms, with minor exceptions. In general, first enter the parameters to these procedures into the forms for validation and debugging.

If an error is detected, ORA-06501: PL/SQL: internal error is raised. The error message can be retrieved by a call to the function fnd_program.message().

Some errors are not trapped by the package, notably "duplicate value on index".

Note that an exception is raised if bad foreign key information is provided. For example, delete_program() does not fail if the program does not exist, but does fail if given a bad application name.

FND_PROGRAM.MESSAGE

function FND_PROGRAM.MESSAGE return VARCHAR2;

FND_PROGRAM.EXECUTABLE

procedure FND_PROGRAM.EXECUTABLE
(executable     			IN	VARCHAR2,
	application				IN VARCHAR2,
	description				IN VARCHAR2 DEFAULT NULL,
	execution_method		IN	VARCHAR2,
	execution_file_name	IN	VARCHAR2 DEFAULT NULL,
	subroutine_name		IN VARCHAR2 DEFAULT NULL,
	icon_name				IN	VARCHAR2 DEFAULT NULL,
	language_code			IN VARCHAR2 DEFAULT 'US');

FND_PROGRAM.DELETE_EXECUTABLE

procedure FND_PROGRAM.DELETE_EXECUTABLE
(executable		IN	varchar2,
	application		IN varchar2);

FND_PROGRAM.REGISTER

procedure FND_PROGRAM.REGISTER
 		(program							IN VARCHAR2,
		application  					IN VARCHAR2,
		enabled       					IN VARCHAR2,
		short_name	  					IN VARCHAR2,
		description	 					IN VARCHAR2 DEFAULT NULL,
		executable_name				IN VARCHAR2,
		executable_application 	IN VARCHAR2,
		execution_options				IN VARCHAR2 DEFAULT NULL,
		priority							IN NUMBER   DEFAULT NULL,
		save_output						IN VARCHAR2 DEFAULT 'Y',
		print								IN VARCHAR2 DEFAULT 'Y',
		cols								IN NUMBER   DEFAULT NULL,
		rows								IN NUMBER   DEFAULT NULL,
		style 							IN VARCHAR2 DEFAULT NULL,
		style_required					IN VARCHAR2 DEFAULT 'N',
		printer							IN VARCHAR2 DEFAULT NULL,
		request_type					IN VARCHAR2 DEFAULT NULL,
		request_type_application 	IN VARCHAR2 DEFAULT NULL,
		use_in_srs						IN VARCHAR2 DEFAULT 'N',
		allow_disabled_values		IN VARCHAR2 DEFAULT 'N',
		run_alone						IN VARCHAR2 DEFAULT 'N',
		output_type						IN VARCHAR2 DEFAULT 'TEXT',
		enable_trace					IN VARCHAR2 DEFAULT 'N',
		restart							IN VARCHAR2 DEFAULT 'Y',
		nls_compliant					IN VARCHAR2 DEFAULT 'N',
		icon_name						IN VARCHAR2 DEFAULT NULL,
		language_code					IN VARCHAR2 DEFAULT 'US'
		mls_function_short_name	IN VARCHAR2,
		mls_function_application	IN VARCHAR2,
		incrementor						IN VARCHAR2);

FND_PROGRAM.DELETE_PROGRAM

procedure FND_PROGRAM.DELETE_PROGRAM
(program_short_name	IN	varchar2,
    application			IN varchar2);

FND_PROGRAM.PARAMETER

procedure FND_PROGRAM.PARAMETER
(program_short_name 	IN	VARCHAR2,
	application				IN VARCHAR2,
	sequence  				IN NUMBER,
	parameter   			IN VARCHAR2,
	description 			IN VARCHAR2 DEFAULT NULL,
	enabled 					IN VARCHAR2 DEFAULT 'Y',
	value_set 				IN VARCHAR2,
	default_type 			IN VARCHAR2 DEFAULT NULL,
	default_value   		IN VARCHAR2 DEFAULT NULL,
	required 				IN VARCHAR2 DEFAULT 'N',
	enable_security 		IN VARCHAR2 DEFAULT 'N',
	range 					IN VARCHAR2 DEFAULT NULL,
	display 					IN VARCHAR2 DEFAULT 'Y',
	display_size 			IN NUMBER,
	description_size		IN NUMBER,
	concatenated_description_size IN NUMBER,
	prompt 					IN VARCHAR2 DEFAULT NULL,
	token  					IN VARCHAR2 DEFAULT NULL);

Important:A newly added parameter does not show up in the SRS form until the descriptive flexfields are compiled. The program $FND_TOP/$APPLBIN/fdfcmp compiles the descriptive flexfields.

FND_PROGRAM.DELETE_PARAMETER

procedure FND_PROGRAM.DELETE_PARAMETER
(program_short_name 	IN varchar2,
   	application			IN varchar2
	   parameter	      IN	varchar2);

FND_PROGRAM.INCOMPATIBILITY

procedure FND_PROGRAM.INCOMPATIBILITY
(program_short_name 		IN VARCHAR2,
	application					IN VARCHAR2
	inc_prog_short_name		IN	VARCHAR2,
	inc_prog_application	IN VARCHAR2,
	scope             		IN VARCHAR2 DEFAULT 'Set');

FND_PROGRAM.DELETE_INCOMPATIBILITY

procedure FND_PROGRAM.DELETE_INCOMPATIBILITY
 (program_short_name			IN	VARCHAR2,
		application	 				IN VARCHAR2,
		inc_prog_short_name  	IN VARCHAR2,
		inc_prog_application 	IN VARCHAR2);

FND_PROGRAM.REQUEST_GROUP

procedure FND_PROGRAM.REQUEST_GROUP
(request_group 	IN VARCHAR2,
 application		IN VARCHAR2,
	code 				IN VARCHAR2 DEFAULT NULL,
	description		IN VARCHAR2 DEFAULT NULL);

FND_PROGRAM.DELETE_GROUP

procedure FND_PROGRAM.DELETE_GROUP
(group			IN	VARCHAR2,
 application IN VARCHAR2);

FND_PROGRAM.ADD_TO_GROUP

procedure FND_PROGRAM.ADD_TO_GROUP
(program_short_name		IN	VARCHAR2,
    program_application	IN VARCHAR2,
    request_group			IN VARCHAR2,
    group_application		IN VARCHAR2);

FND_PROGRAM.REMOVE_FROM_GROUP

procedure FND_PROGRAM.REMOVE_FROM_GROUP
(program_short_name		IN	VARCHAR2,
    program_application	IN VARCHAR2,
    request_group			IN VARCHAR2,
    group_application		IN VARCHAR2);

FND_PROGRAM.PROGRAM_EXISTS

function FND_PROGRAM.PROGRAM_EXISTS
(program 		IN VARCHAR2,
 application	IN VARCHAR2)
return boolean;

FND_PROGRAM.PARAMETER_EXISTS

function FND_PROGRAM.PARAMETER_EXISTS
(program_short_name	IN VARCHAR2,
	application				IN	VARCHAR2,
	parameteR				IN	VARCHAR2)
return boolean;

FND_PROGRAM.INCOMPATIBILITY_EXISTS

function FND_PROGRAM.INCOMPATIBILITY_EXISTS
(program_short_name 		IN VARCHAR2,
	application 				IN	VARCHAR2,
	inc_prog_short_name		IN	VARCHAR2,
	inc_prog_application 	IN	VARCHAR2)
return boolean;

FND_PROGRAM.EXECUTABLE_EXISTS

function FND_PROGRAM.EXECUTABLE_EXISTS
(executable_short_name	IN VARCHAR2,
 application 					IN	VARCHAR2)
return boolean;

FND_PROGRAM.REQUEST_GROUP_EXISTS

function FND_PROGRAM.REQUEST_GROUP_EXISTS
(request_group	IN VARCHAR2,
 application		IN	VARCHAR2)
return boolean;

FND_PROGRAM.PROGRAM_IN_GROUP

function FND_PROGRAM.INCOMPATIBILITY_EXISTS
(program_short_name 	IN VARCHAR2,
	application				IN	VARCHAR2,
	request_group			IN	VARCHAR2,
	group_application		IN	VARCHAR2)
return boolean;

FND_PROGRAM.ENABLE_PROGRAM

procedure FND_PROGRAM_ENABLE_PROGRAM
(short_name 	IN VARCHAR2,
 application 	IN	VARCHAR2,
 ENABLED		IN	VARCHAR2);

FND_REQUEST Package

FND_REQUEST.SET_OPTIONS (Client or Server)

function FND_REQUEST.SET_OPTIONS
(implicit  IN varchar2 default 'NO',
          protected IN varchar2 default 'NO',
          language  IN varchar2 default NULL,
          territory IN varchar2 default NULL)
          return boolean;

FND_REQUEST.SET_REPEAT_OPTIONS (Client or Server)

function FND_REQUEST.SET_REPEAT_OPTIONS
(repeat_time 			IN varchar2 default NULL,
repeat_interval  	IN number default NULL,
repeat_unit      	IN varchar2 default 'DAYS',
repeat_type      	IN varchar2 default 'START'
repeat_end_time  	IN varchar2 default NULL)
return boolean;

FND_REQUEST.SET_PRINT_OPTIONS (Client or Server)

function FND_REQUEST.SET_PRINT_OPTIONS
(printer 			IN varchar2 default NULL,
style   		 	IN varchar2 default NULL,
copies  			IN number default NULL,
save_output  		IN boolean default TRUE,
print_together 	IN varchar2 default 'N')
return boolean;

Important:Some print options that are set at the program level (i.e., using the Concurrent Programs form) cannot be overridden using this procedure. See the following argument descriptions to determine which print options can be overridden.

FND_REQUEST.SUBMIT_REQUEST (Client or Server)

function FND_REQUEST.SUBMIT_REQUEST
(application 		IN varchar2 default NULL,
program   			IN varchar2 default NULL,
description    	IN varchar2 default NULL,
start_time     	IN varchar2 default NULL,
sub_request    	IN boolean default FALSE
argument1,
argument2, ..., argument99,
argument100) return number;

Important:FND_REQUEST must know information about the user and responsibility from which the request is submitted. Therefore, this function only works from concurrent programs or forms within Oracle E-Business Suite.

FND_REQUEST.ADD_NOTIFICATION (Client or Server)

function FND_REQUEST.ADD_NOTIFICATION
(user IN varchar2) return boolean; 

FND_REQUEST.SET_MODE (Server)

function FND_REQUEST.SET_MODE
 (db_trigger IN boolean) return boolean;

Note that a failure in the database trigger call of FND_REQUEST.SUBMIT_REQUEST does not roll back changes.

Example Request Submissions

/* Example 1 */
/* Submit a request from a form and commit*/
 :parameter.req_id :=
      FND_REQUEST.SUBMIT_REQUEST (
           :blockname.appsname,
           :blockname.program,
           :blockname.description,
           :blockname.start_time,
           :blockname.sub_req = 'Y',
           123, NAME_IN('ORDERS.ORDER_ID'), 'abc',
           chr(0), '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '',
           '', '', '', '', '', '', '', '', '', '');
IF :parameter.req_id = 0 THEN
    FND_MESSAGE.RETRIEVE;
    FND_MESSAGE.ERROR;
ELSE
  IF :SYSTEM.FORM_STATUS != 'CHANGED' THEN
    IF app_form.quietcommit  THEN
      /*form commits without asking user to save changes*/
     fnd_message.set_name('SQLGL',
           'GL_REQUEST_SUBMITTED');
     fnd_message.set_TOKEN('REQUEST_ID',
            TO_CHAR(:PARAMETER.REQ_ID), FALSE);
     fnd_message.show;
    ELSE
       fnd_message.set_name('FND',
            'CONC-REQUEST SUBMISSION FAILED');
       fnd_message.error;
    END IF;
  ELSE
    DO_KEY('COMMIT_FORM');
    IF :SYSTEM.FORM_STATUS != 'CHANGED' THEN
        /*commit was successful*/
        fnd_message.set_name('SQLGL',
           'GL_REQUEST_SUBMITTED');
        fnd_message.set_TOKEN('REQUEST_ID',
             TO_CHAR(:PARAMETER.REQ_ID), FALSE);
        fnd_message.show;
    END IF;
  END IF;
END IF;

/* Example 2 */
/* Submit a request where no setup is required */
declare
  req_id number;
begin
  req_id := FND_REQUEST.SUBMIT_REQUEST ('FND',
              'FNDMDGEN', 'Message File Generator',
              '01-NOV-02 00:00:00', FALSE, ...arguments...);
  if (req_id = 0) then
    /* Handle submission error */
    FND_MESSAGE.RETRIEVE;
    FND_MESSAGE.ERROR;
  else
    commit;
  end if;
end;

/* Example 3 */
/* Submit a request from a database trigger */
result := FND_REQUEST.SET_MODE(TRUE);
req_id := FND_REQUEST.SUBMIT_REQUEST (FND',
            'FNDMDGEN', 'Message File  Generator',
            '01-NOV-02 00:00:00', FALSE, ...arguments...);

/* Example 4 */
/* Submit a request inserting NULL arguments.
   This call inserts 6 arguments with arguments 1, 3,
   4, and 6 being NULL */
req_id := FND_REQUEST.SUBMIT_REQUEST ('FND',
                        'FNDPROG',
                        'Description of FNDPROG',
                        '01-FEB-01 00:00:00', FALSE,
                        '', 'arg2', '', NULL, arg5, '');

/* Example 5 */
/* Submit a repeating request */
result := FND_REQUEST.SET_REPEAT_OPTIONS ('', 4, 'HOURS', 'END');
req_id := FND_REQUEST.SUBMIT_REQUEST ('CUS',
                          'CUSPOST', 'Custom Posting',
                          '01-APR-01 00:00:00', FALSE,
                          ...arguments...);

Important:You may not want to submit a request if FND_REQUEST.SET_REPEAT_OPTIONS returns failure. Thus, you may wish to test the result of FND_REQUEST.SET_REPEAT_OPTIONS before issuing the call to FND_REQUEST.SUBMIT_REQUEST.

/* Example 6 */
/* Submit a request for 5 copies of a menu report */
result := FND_REQUEST.SET_PRINT_OPTIONS ('hqunx138',
                                         'Landscape',
                                          5,
                                         'Yes',
                                         FALSE);
req_id := FND_REQUEST.SUBMIT_REQUEST ('FND',
                                'FNDMNRMT',
                                '',
                                '',
                               'N', 0, 101);

/* Example 7 */
/* Submit a protected request that repeats at noon */
result := FND_REQUEST.SET_OPTIONS ('YES');
result := FND_REQUEST.SET_REPEAT_OPTIONS ('12:00');
req_id := FND_REQUEST.SUBMIT_REQUEST ('CUS',
                            'CUSPOST', 'Custom Posting',
                            '01-APR-01 00:00:00', FALSE,
                             ... args ...);

FND_REQUEST_INFO and Multiple Language Support (MLS)

FND_REQUEST_INFO APIs can be used in multi-language support functions (MLS functions) to get information for a request.

A multi-language support function is a function that supports running concurrent programs in multiple languages, as well as territories and numeric character settings (".," for example). A user can submit a single request for a concurrent program and have that program run several times, each time with different values for the parameters for language, territory, and numeric character setting.

To enable this functionality, a developer creates an MLS function as a stored function in the database. When called, the function determines which languages are to be used for the concurrent program's data set and returns the list of language codes as a comma-delimited string. The string is then used by the concurrent manager to submit child requests for the concurrent program for each target language. The MLS function can do the same for territory and numeric character setting.

Note:The fnd_concurrent_request should reflect the three values for language, territory, and numeric character setting passed in a string format. This string format consists of a language code, optional territory, and optional numeric character separated by colons and a delimiter of a semicolon as shown in following examples:

“US:US:,.”

“US::.,;JA:JP:;KO:KR:.,”

“US: : ”

The MLS function can use the FND_REQUEST_INFO APIs to retrieve the concurrent program application short name, the concurrent program short name, and the concurrent request parameters if needed.

The developer registers the MLS function in the Concurrent Program Executable form, and then associates the registered MLS function with a concurrent program in the Concurrent Programs form.

FND_REQUEST_INFO.GET_PARAM_NUMBER

function GET_PARAM_NUMBER
(name				IN VARCHAR2,
param_num			OUT NUMBER);

FND_REQUEST_INFO.GET_PARAM_INFO

function GET_PARAM_INFO
(param_num		IN NUMBER,
	 name			OUT VARCHAR2);

FND_REQUEST_INFO.GET_PROGRAM

procedure GET_PROGRAM
(program_name	 		OUT VARCHAR2,
program_app_name		OUT VARCHAR2);

FND_REQUEST_INFO.GET_PARAMETER

function GET_PARAMETER
(param_num	IN NUMBER)
	return varchar2;

Example MLS Function

Suppose you have a concurrent program that will send each employee a report of his or her available vacation days. Assume that the concurrent program will accept a range of employee numbers as parameters. The employees all want to receive the vacation days report in their preferred language. Without an MLS function, the user who submits this concurrent program has to guess at all the preferred languages for the given range of employee numbers and select those languages while submitting the request. Selecting all installed languages might be a waste of resources, because output may not be required in all installed languages.

Assume you have an employees table (emp) with the following columns:

 emp_no number(15),
     ...
     preferred_lang_code  varchar2(4),
     ...

Your concurrent program has two parameters for the range of employee numbers: parameter 1 is the starting emp_no and parameter 2 is the ending emp_no.

This MLS function could be used for other concurrent programs that have the same parameters for starting and ending employee numbers.

Example

CREATE OR REPLACE FUNCTION EMPLOYEE_LANG_FUNCTION RETURN VARCHAR2 IS
  language_string    varchar2(240);
  start_value        varchar2(240);
  end_value          varchar2(240);
  CURSOR language_cursor (starting number, ending number) IS
     SELECT DISTINCT(preferred_lang_code) language_code
      FROM  emp
      WHERE emp_no BETWEEN starting AND ending
      AND   preferred_lang_code IS NOT NULL;
  BEGIN
      -- Initialize the language string
      language_string := null;
      -- Get parameter values for starting and
      -- ending EMP_NO
      start_value := FND_REQUEST_INFO.GET_PARAMETER(1);
      end_value  := FND_REQUEST_INFO.GET_PARAMETER(2);
      FOR languages IN language_cursor(
                       to_number(start_value),
                       to_number(end_value)) LOOP
        IF( language_string IS NULL ) THEN
           language_string := languages.language_code;
        ELSE
           language_string := language_string || ',' ||
                         languages.language_code;
        END IF;
      END LOOP;
      RETURN (language_string);
END EMPLOYEE_LANG_FUNCTION;

FND_SET: Request Set Loaders

The FND_SET package includes procedures for creating concurrent program request sets, adding programs to a request set, deleting programs from a request set. and defining parameters for request sets.

The arguments passed to the procedures correspond to the fields in the Oracle Application Object Library forms, with minor exceptions. In general, first enter the parameters to these procedures into the forms for validation and debugging.

If an error is detected, ORA-06501: PL/SQL: internal error is raised. The error message can be retrieved by a call to the function fnd_program.message().

Some errors are not trapped by the package, notably "duplicate value on index".

Note that an exception is raised if bad foreign key information is provided. For example, delete_program() does not fail if the program does not exist, but does fail if given a bad application name.

See:
Overview of Request Sets

FND_SET.MESSAGE

FND_SET.CREATE_SET

procedure FND_SET.CREATE_SET
 (name	                    	IN VARCHAR2,
	short_name                 	IN VARCHAR2,
	application                	IN VARCHAR2,
	description                	IN VARCHAR2 DEFAULT NULL,
	owner                      	IN VARCHAR2 DEFAULT NULL,
	start_date                 	IN DATE     DEFAULT SYSDATE,
	end_date                   	IN DATE     DEFAULT NULL,
	print_together             	IN VARCHAR2 DEFAULT 'N',
	incompatibilities_allowed  	IN VARCHAR2 DEFAULT 'N',
	language_code              	IN VARCHAR2 DEFAULT 'US');

FND_SET.DELETE_SET

procedure FND_SET.DELETE_SET
(request_set 		IN VARCHAR2,
	application		IN VARCHAR2);

FND_SET.ADD_PROGRAM

procedure FND_SET.ADD_PROGRAM
(program                        IN VARCHAR2,
	program_application           IN VARCHAR2,
	request_set                   IN VARCHAR2,
	set_application               IN VARCHAR2,
	stage                         IN VARCHAR2,
	program_sequence              IN NUMBER,
  	critical                      IN VARCHAR2 DEFAULT 'Y',
	number_of_copies              IN NUMBER   DEFAULT 0,
	save_output                   IN VARCHAR2 DEFAULT 'Y',
	style                         IN VARCHAR2 DEFAULT NULL,
	printer                       IN VARCHAR2 DEFAULT NULL);

FND_SET.REMOVE_PROGRAM

procedure FND_SET.REMOVE_PROGRAM
(program_short_name             IN VARCHAR2,
	program_application           IN VARCHAR2,
	request_set                   IN VARCHAR2,
	set_application               IN VARCHAR2,
	stage                         IN VARCHAR2,
	program_sequence              IN NUMBER);

FND_SET.PROGRAM_PARAMETER

procedure FND_SET.PROGRAM_PARAMETER
(program 						IN VARCHAR2,
	program_application    IN VARCHAR2,
	request_set           	IN VARCHAR2,
	set_application       	IN VARCHAR2,
	stage                 	IN VARCHAR2.
	program_sequence 			IN NUMBER,
	parameter					IN VARCHAR2,
	display                IN VARCHAR2 DEFAULT 'Y',
	modify     					IN VARCHAR2 DEFAULT 'Y',
 	shared_parameter     	IN VARCHAR2 DEFAULT NULL,
	default_type   			IN VARCHAR2 DEFAULT NULL,
	default_value   			IN VARCHAR2 DEFAULT NULL);

FND_SET.DELETE_PROGRAM_PARAMETER

procedure FND_SET.DELETE_SET_PARAMETER
program		            	IN VARCHAR2,
	program_application 	IN VARCHAR2,
	request_set	  		 		IN VARCHAR2 DEFAULT NULL,
  stage						  	IN VARCHAR2,
	set_application 			IN VARCHAR2,
	program_sequence  		IN NUMBER,
	parameter					IN VARCHAR2);

FND_SET.ADD_STAGE

procedure FND_SET.ADD_STAGE
(name								IN VARCHAR2,
request_set          			IN VARCHAR2,
set_application					IN VARCHAR2,
short_name	 						IN VARCHAR2,
description	       			IN VARCHAR2 DEFAULT NULL,
display_sequence     			IN NUMBER,
function_short_name  			IN VARCHAR2 DEFAULT 'FNDRSSTE'
function_application 			IN VARCHAR2 DEFAULT 'FND',
critical 							IN VARCHAR2 DEFAULT 'N',
incompatibilities_allowed 	IN VARCHAR2 DEFAULT 'N',
start_stage						IN VARCHAR2 DEFAULT 'N',
language_code						IN VARCHAR2 DEFAULT 'US');

FND_SET.REMOVE_STAGE

procedure FND_SET.REMOVE_STAGE
(request_set 			IN VARCHAR2,
	set_application	IN VARCHAR2,
  stage					IN VARCHAR2);

FND_SET.LINK_STAGES

procedure FND_SET.LINK_STAGES
(request_set 			IN VARCHAR2,
	set_application	IN VARCHAR2,
	from_stage			IN VARCHAR2,
	to_stage				IN VARCHAR2 DEFAULT NULL,
	success				IN VARCHAR2 DEFAULT 'N',
	warning				IN VARCHAR2 DEFAULT 'N',
	error					IN VARCHAR2 DEFAULT 'N');

Important:This procedure updates the specified links. Sets created by FND_SET.CREATE_SET have null links between stages by default.

FND_SET.INCOMPATIBILITY

 procedure FND_SET.INCOMPATIBILITY
(request_set					IN VARCHAR2,
	application					IN VARCHAR2,
	stage							IN VARCHAR2 DEFAULT NULL,
	inc_prog						IN VARCHAR2 DEFAULT NULL
	inc_prog_application	IN VARCHAR2 DEFAULT NULL,
	inc_request_set			IN VARCHAR2 DEFAULT NULL,
	inc_set_application		IN VARCHAR2 DEFAULT NULL,
	inc_stage					IN VARCHAR2 DEFAULT NULL);

Examples

1. Set X is incompatible with program Y:

   
   

   fnd_set.incompatibility(request_set=>'X'
                           application=>'APPX'
                           inc_prog_short_name=>'Y',                                    inc_prog_application=>'APPY');
   

   
   

2. Set X is incompatible with set Y:

   
   

   fnd_set.incompatibility(request_set=>'X',
                           application=>'APPX',
                           inc_request_set=>'Y',
                           inc_set_application=>'APPY');
   

   
   

3. Set X is incompatible with stage 2 of set Y:

   
   

   fnd_set.incompatibility(request_set=>'X',
                           application=>'APPX',
                           inc_request_set=>'Y',
                           inc_set_application=>'APPY',
                           inc_stage_number=>2);
   

   
   

4. Stage 3 of set X is incompatible with program Y:

   
   

   fnd_set.incompatibility(request_set=>'X',
                           application=>'APPX',
                           stage_number=>3,
                           inc_prog_short_name=>'Y',
                           inc_prog_application=>'APPY');
          

   
   

FND_SET.DELETE_INCOMPATIBILITY

procedure FND_SET.DELETE_INCOMPATIBILITY
 (request_set	          	IN VARCHAR2,
	application	 				IN VARCHAR2,
	stage							IN VARCHAR2 DEFAULT NULL,
	inc_prog						IN VARCHAR2 DEFAULT NULL
	inc_prog_application	IN VARCHAR2 DEFAULT NULL,
	inc_request_set			IN VARCHAR2 DEFAULT NULL,
	inc_set_application		IN VARCHAR2 DEFAULT NULL,
	inc_stage					IN VARCHAR2 DEFAULT NULL);

FND_SET.ADD_SET_TO_GROUP

procedure FND_SET.ADD_SET_TO_GROUP
   (request_set		   IN VARCHAR2,
    set_application	IN VARCHAR2,
    request_group		IN VARCHAR2,
    group_application	IN VARCHAR2);

FND_SET.REMOVE_SET_FROM_GROUP

procedure FND_SET.REMOVE_SET_FROM_GROUP
(request_set				IN	VARCHAR2,
 set_application	 		IN VARCHAR2,
 request_group			IN VARCHAR2,
 group_application		IN VARCHAR2);

FND_SUBMIT: Request Set Submission

This document describes the FND_SUBMIT APIs for request set submission. The APIs are described in the order that they would be called. Some of these APIs are optional.

FND_SUBMIT.SET_MODE

function FND_SUBMIT.SET_MODE (db_trigger IN boolean) return boolean;

FND_SUBMIT.SET_REL_CLASS_OPTIONS

function FND_SUBMIT.SET_REL_CLASS_OPTIONS
(application		IN varchar2 default NULL,
 class_name		IN varchar2 default NULL,
 cancel_or_hold	IN varchar2 default 'H',
 stale_date		IN varchar2 default NULL)  return boolean;

FND_SUBMIT.SET_REPEAT_OPTIONS

function FND_SUBMIT.SET_REPEAT_OPTIONS
(repeat_time			IN varchar2 default NULL,
 repeat_interval		IN number default NULL,
 repeat_unit			IN varchar2 default 'DAYS',
 repeat_type			IN varchar2 default 'START',
 repeat_end_time 	IN varchar2 default NULL)  	return boolean; 

FND_SUBMIT_SET.REQUEST_SET

function FND_SUBMIT.SET_REQUEST_SET
(application	IN VARCHAR2,
 request_set	IN VARCHAR2)  return  boolean; 

FND_SUBMIT.SET_PRINT_OPTIONS

function FND_SUBMIT.SET_PRINT_OPTIONS
(printer		 	IN varchar2 default NULL,
style				IN varchar2 default NULL,
copies				IN number default NULL,
save_output		IN boolean default
print_together	IN varchar2 default 'N')  return boolean;

FND_SUBMIT.ADD_PRINTER

function FND_SUBMIT.SET.ADD_PRINTER
(printer 	IN varchar2 default null,
 copies  	IN number default null)  return boolean; 

FND_SUBMIT.ADD_NOTIFICATION

function FND_SUBMIT.ADD_NOTIFICATION
(user IN varchar2) return boolean;

FND_SUBMIT.SET_NLS_OPTIONS

function FND_SUBMIT.SET_NLS_OPTIONS
(language  	IN varchar2 default NULL,
 territory  	IN varchar2 default NULL) return boolean; 

FND_SUBMIT.SUBMIT_PROGRAM

function FND_SUBMIT.SUBMIT_PROGRAM
(application	IN varchar2,
 program		IN varchar2,
 stage			IN varchar2,
 argument1,...argument100)  return boolean;

FND_SUBMIT.SUBMIT_SET

function FND_SUBMIT.SUBMIT_SET
(start_time	IN varchar2 default null,
 sub_request	IN boolean default FALSE)  return integer;

Examples of Request Set Submission

/* Example 1 */
/* To submit a Request set which is having STAGE1 and
STAGE2. STAGE1 is having 'FNDSCARU' and 'FNDPRNEV'
programs. STAGE2 is having 'FNDSCURS'. */
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND', 'FNDRSTEST');
		if ( success ) then
		/* submit program FNDSCARU which is in stage STAGE1 */
		success := fnd_submit.submit_program('FND','FNDSCARU',
 		'STAGE1', CHR(0),'','','','','','','','','',
							...arguments...);
  	if ( not success ) then
			raise submit_failed;
		end if;
		/* submit program FNDPRNEV which is in stage STAGE1 */
		success := fnd_submit.submit_program('FND','FNDPRNEV',
				'STAGE1','','','','','','','','','','',
				CHR(0),'','','','','','','','','',
				...arguments...);
		if ( not success ) then
			raise submit_failed;
		end if;
		/* submit program FNDSCURS which is in stage STAGE2 */
		success := fnd_submit.submit_program('FND','FNDSCURS',
					'STAGE2', CHR(0),'','','','','','','','','',
					...arguments...);
		if ( not success ) then
				raise submit_failed;
		end if;
		/* Submit the Request Set */
		req_id := fnd_submit.submit_set(null,FALSE);
end if;

/* Example 2 */
/* To submit a request set FNDRSTEST as a repeating request set.
Request set FNDRSTEST has STAGE1 and STAGE2.
STAGE1 contains 'FNDSCARU'  and 'FNDPRNEV' programs.
STAGE2 has 'FNDSCURS'. */
   /* set the repeating options for the request set before
     calling the set_request_set  */
   success := fnd_submit.set_repeat_options( '', 4, 'HOURS',     'END');
   /* set the context for the request set FNDRSTEST */
   success := fnd_submit.set_request_set('FND',
	'FNDRSTEST');
   if ( success ) then
   /* submit program FNDSCARU which is in stage STAGE1 */
   success := fnd_submit.submit_program('FND','FNDSCARU',
      'STAGE1', CHR(0),'','','','','','','','','',
      ...arguments...);
   if ( not success ) then
     raise submit_failed;
   end if;
   /* submit program FNDPRNEV which is in stage STAGE1 */
     success := fnd_submit.submit_program('FND','FNDPRNEV',
	'STAGE1','','','','','','','','','','',
	CHR(0),'','','','','','','','','',
	...arguments...);
      if ( not success ) then
	raise submit_failed;
      end if;
   /* submit program FNDSCURS which is in stage STAGE2  */
   success := fnd_submit.submit_program('FND','FNDSCURS',
	'STAGE2', CHR(0),'','','','','','','','','',
        ...arguments...);
   if ( not success ) then
      raise submit_failed;
   end if;
   /*  Submit the Request set  */
   req_id := fnd_submit.submit_set(null,FALSE);
end if;

/* Example 3 */
/* To submit a Request set  FNDRSTEST with 5 copies of the Print environment variables report.  Request set FNDRSTEST has STAGE1 and STAGE2.  STAGE1 has 'FNDSCARU'  and 'FNDPRNEV' programs.  STAGE2 has 'FNDSCURS'. */
   /* set the context for the request set FNDRSTEST */
   success := fnd_submit.set_request_set('FND', 'FNDRSTEST');
   if ( success ) then
      /* submit program FNDSCARU which is in stage STAGE1 */
      success := fnd_submit.submit_program('FND','FNDSCARU',
          'STAGE1', CHR(0),'','','','','','','','','', ...arguments...);
   if ( not success ) then
          raise submit_failed;
   end if;
   /* set the print options for the program */
   success := fnd_submit.set_print_options( 'hqunx138', 'Landscape', 5,
       'Yes', FALSE);
   /* submit program FNDPRNEV which is in stage STAGE1 */
   success:= fnd_submit.submit_program('FND','FNDPRNEV',
         'STAGE1','','','','','','','','','','',
         CHR(0),'','','','','','','','','',
          ...arguments...);
   if ( not success ) then
       raise submit_failed;
   end if;
   /* submit program FNDSCURS which is in stage STAGE2  */
   success := fnd_submit.submit_program('FND','FNDSCURS',             'STAGE2', CHR(0),'','','','','','','','','',
         ...arguments...);
   if ( not success ) then
      raise submit_failed;
   end if;
   /*  Submit the Request set  */
   req_id := fnd_submit.submit_set(null,FALSE);
end if;

What is In “Purchase Order import”

Posted on December 24th, 2007 by Sanjit Anand |

You can use Purchase Document Open Interface which allows you to quickly import a large volume of Standard Purchase Orders into Oracle Purchasing.

The Import process involves populating the PO interface tables with the document information to be imported and then running the Import
Standard Purchase Orders concurrent program which will validate the data and create the PO in the application and return an error message if something fail.

First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Steps by Steps

Know what is getting ining Data into Purchase Order Interface Tables

1. Load PO header, lines, shipments and distributions data from your source system into the following interface tables
   • PO_HEADERS_INTERFACE
   • PO_LINES_INTERFACE
   • PO_DISTRIBUTIONS_INTERFACE
2. Once the data has been inserted into the interface tables, a queries like the following can be used to review the information before running the import program :
   • Select * from PO_HEADERS_INTERFACE where INTERFACE_HEADER_ID=<headerid>
   • Select * from PO_LINES_INTERFACE where INTERFACE_HEADER_ID=&headerid
   
   
   • Select * from PO_DISTRIBUTIONS_INTERFACE where INTERFACE_HEADER_ID=&headerid

Review data before calling Import Standard Purchase Orders program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

Understanding approval status in parameter

Significant impact is there on Approval Status parameter and have import logic which is as below:

Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

• You can notice , successful records get PROCESS_CODE as “ACCEPTED”
• It is good practice and important to check the Purchasing Interface Errors report always.
• This Error report you can submit after your import completed.


• Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

What happen with IMPORT

This seems sound intresting to you.

• The Purchasing Documents Open Interface (PDOI) programs first process a record from the PO_HEADERS_INTERFACE table.
• Then, the program processes the child records in the PO_LINES_INTERFACE table then process the PO_DISTRIBUTIONS_INTERFACE table, before going on to the next PO represented by a record in PO_HEADERS_INTERFACE. Make sense.


• In between , If the program gets an error while processing a record, the program writes the error details to the PO_INTERFACE_ERRORS table and increments the record's error counter.
• Therefore, the Purchasing Documents Open Interface saves or errors out on a line-byline basis.
• This means that if an error is found in a document line, only that line is rolled back (not submitted to Purchasing), and we will be able to find the error in the PO_INTERFACE_ERRORS table.
• You should be aware , because the Purchasing Documents Open Interface can accept partial documents as it saves or errors out line by line.
• If an error is found in a header, none of its lines are processed.
• The Purchasing Documents Open Interface rolls back the header, does not process its lines, and does the following:
  • Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_INTERFACE table.
  
  
  • Writes out the record identification number and the details of the error to the PO_INTERFACE_ERRORS table.
  • Begins processing the next header record.
• If no processing errors are found during processing, the header record and all successfully submitted child records are loaded into Purchasing, and then flagged as processed by setting the PROCESS_CODE column to ACCEPTED.

As mention earlier, To check for records in error, the Purchasing Interface Errors Report can be run to provide information as to the cause of the error.

Other tools

This is most acceptable interface and widly used every where, therefore Oracle have Diagnostics tool for this keeping developer in mind.

Oracle Diagnostics tool name is Oracle Purchasing Documents Open Interface Data Collection Test.

This diagnostic test will verify the data in the interface tables used by the purchasing documents open interface (PDOI) so that it can be used proactively or reactively to resolve or prevent issues in the purchasing documents open interface (PDOI).

Hope you find this is very useful and productive tool.should you need any input send me offline.

Purchase Document Open Interface(PDOI)