XML Bursting

Background / Business Requirement for Bursting
In Oracle Purchasing we have PO Print Report which can be used to print new or selected Purchase order. Organizations usually schedule this program on a daily basis to print new purchase orders. If we want to integrate this Oracle Report to XML/BI Publisher to create eye candy layouts and email the PO automatically to the suppliers, the common issue we face is:-

How to split the XML file created by report ? This is because report will create a single XML file consisting of the all the PO selected and if we apply the template on this XML file , it will create a single PDF file consisting of all the PO’s , and we definitely don’t want purchase orders sent to a supplier which is not related to him .If we have to send PO to individual supplier there has to be a way to parse the XML file generated from the PO Print Report.After parsing the XML file we can break the XML into smaller chunks based on the PO Number and then email the PO to respective Suppliers.

Advantages of XMLP bursting

a. No Additional Coding, just need to create XML bursting File. ( Isn't it exciting , otherwise we would have to write java program to split the XML File and then use XMLP delivery manager API to send emails .
b. Process of Creating XML data Definition and Template remains AS – IS .

Prerequisitesa. Apply patch 5968876 if not already applied
b. Verify XMLP version is 5.6.3

Steps to Verify if XMLP is indeed 5.6.3
1. adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class

Below matrix shows the the version you are and the patch to be applied to come to 5.6.3

Metainfo.class

Metainfo.class	XML Publisher	Patch	Reference
120.6	XDO 5.6.3	Patch 4440000
... for the Oracle E-Business Suite 11i
115.28	XDO 5.6.3	Patch 5472959	Note 422508.1
115.27	XDO 5.6.2 (core)	Patch 5097966	Note 367394.1
115.26	XDO 5.6.1	Patch 4905678	Note 357308.1
115.24	XDO 5.6.0	Patch 4561451	Note 337999.1
115.22	XDO 5.5.0	Patch 4206181	Note 316447.1
115.21	XDO 5.0.1 (core)	Patch 4236958
115.19	XDO 5.0.0	Patch 3822219	Note 295036.1

c. Set Temporary Directory in the XML Publisher Administrator- > Administration- > General to a directory to which you have write permission .

d. After the Patch is applied we will see a new Bursting control file button in the XML Publisher Data Definition Page
To check this, please go to Responsibility "XML Publisher Administrator", and search for any existing report, and click on Update. While updating an existing data-definition, you should see the button Bursting Control File.

Now since we have finished setting up , now we will see how to implement Bursting . The major component in this is bursting control file


What is a bursting file ?

Its an XML file which tells bursting engine


a) How to split the XML File created ?

b) How to deliver the Documents ?


Lets see a sample bursting file and its components


a) <xapi:request select> tag tells busting engine , bursting should be done at the occurrence of which tag ?
b) <xapi:delivery > tag tells bursting engine , which delivery mechanism to be used and on what condition ?

Sample bursting file


<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
<xapi:request select="/xxx_HQ_XML_POXPRPOL/LIST_G_INIT_INFO/G_INIT_INFO/LIST_G_HEADERS/G_HEADERS">
<xapi:delivery>
<xapi:email id="123" server="xxxort" port="25" from=" xxp@xx.org.uk This e-mail address is being protected from spambots. You need JavaScript enabled to view it " reply-to=" xx@xx.org.uk This e-mail address is being protected from spambots. You need JavaScript enabled to view it ">
<xapi:message id="123" to="${CF_VENDOR_EMAIL}" attachment="true" subject=" Purchase Order No: ${POH_PO_NUM}">
Please review the attached PO ${POH_PO_NUM}
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output-type="pdf" delivery="123">
<xapi:template type="rtf" location="/u0350/app/apps/UAT/xx/1.0/data/XXHPAPOPRINT.rtf">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>

Please Note : While doing testing please change the "to address" to your email address , else it will send the documents out to that email address .

Data Definition
Create the Data Definition and Template the normal way, where the Short name of the data definition is same as the short name of concurrent program to which you are integrating XMLP.While creating Data Definition Upload you bursting XML File . If there are any parsing issues in the XML file it will give error , please correct the format of the file and try to upload again .


Template File
Create the Template and Upload your Template file



Concurrent Program Registeration
In the Concurrent Program give the Output fomat as XML and the Concurrent Program Short name should be same as the data definition name .


After Report Trigger Changes
Now the Final Step , in the After report Trigger of your .RDf add the following code to call bursting program . This will lanuch the bursting program from your report , otherwise you will have to run the Bursting program ( which is a Java Concurrent Program ) manually .


Now you are all set to do the testing , run the report , please keep in mind to change "to email address" in the bursting control file to your email id while doing the testing . you will see the PO pdf coming to the email address given in the bursting control file.

sending mail to the user by using UTL_FILE package

CREATE OR REPLACE PACKAGE BODY APPS.xx_create_acct
AS
PROCEDURE submit_request(
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
param1 varchar2
param2 varchar2
)

IS
l_senderemail VARCHAR2 (2000) := 'xyz@erp.com';
l_request_id NUMBER;
errmsg VARCHAR2 (1000);
req_id NUMBER;
phase VARCHAR2 (200);
status VARCHAR2 (200);
dphase VARCHAR2 (200);
dstatus VARCHAR2 (200);
MESSAGE VARCHAR2 (200);
wait_stat BOOLEAN;
l_file_name VARCHAR2 (200);
l_file_path VARCHAR2 (2000);
crlf VARCHAR2 (4) := CHR (13) || CHR (10);
v_set_layout_option BOOLEAN;
dir_name varchar2(100);
/* CURSOR req_status
IS
SELECT SUBSTR (outfile_name, 55, 23) filename,
SUBSTR (outfile_name, 1, 64) outfilepath
FROM applsys.fnd_concurrent_requests
WHERE request_id = l_request_id
AND phase_code = 'C'
AND status_code = 'C';*/

directory_path VARCHAR2 (2000);
lv_subject VARCHAR2 (100);
lv_msg VARCHAR2 (700);
lv_msghdr VARCHAR2 (100);
lv_pdf_filename VARCHAR2 (200);
BEGIN

apps.fnd_global.apps_initialize
(user_id => apps.fnd_global.user_id,
resp_id =>apps.fnd_global.resp_id,
resp_appl_id => apps.fnd_global.resp_appl_id
);

l_request_id :=
apps.fnd_request.submit_request
( 'BOM',
'CSTCRACCRCV', --appl short name
NULL,
NULL,
FALSE,
param1,
param2
);
COMMIT;

IF (l_request_id <= 0)
THEN
errmsg := apps.fnd_message.get ();
ELSE
wait_stat :=
apps.fnd_concurrent.wait_for_request (l_request_id,
10,
360,
phase,
status,
dphase,
dstatus,
MESSAGE
);

IF dphase = 'COMPLETE'
THEN

dir_name :='XX_CREATE_ACCT_LOG';

l_file_name := 'l' || l_request_id || '.REQ';
lv_subject := NULL;
lv_msg := NULL;
lv_msghdr := NULL;
lv_pdf_filename := NULL;
lv_subject := 'testing mail :Request for Create Accounting not completed';

lv_msg :=
'Please find attachement here with the Status Of Create Accounting Reciving from cost management'
|| crlf;
lv_msghdr := 'Dear Sir' ;
lv_pdf_filename := l_file_name;
send_mail (l_senderemail,
'abc@erp.com',
lv_subject,
lv_msghdr,
lv_msg,
lv_pdf_filename,
'text/application',
dir_name
);

END IF;

END IF;

END submit_request;

PROCEDURE send_mail (
p_sender IN VARCHAR2,
p_recipient IN VARCHAR2,
p_subject IN VARCHAR2,
p_mailbodyhdr IN VARCHAR2,
p_mailbody IN VARCHAR2,
p_filename IN VARCHAR2,
p_filetype IN VARCHAR2,
dir_name in VARCHAR2
)
AS
v_msg VARCHAR2 (32000);
src_file BFILE;
i INTEGER := 1;
v_raw RAW (500);
v_length INTEGER := 0;
v_buffer_size INTEGER := 57;
v_mailconn UTL_SMTP.connection;
gc_crlf VARCHAR2 (4) := CHR (13)
|| CHR (10);
gc_lf VARCHAR2 (4) := CHR (10);
crlf VARCHAR2 (2) := CHR (13)
|| CHR (10);
gc_mailhost VARCHAR2 (255) := '172.16.11.2';
-- gc_maildomain VARCHAR2 (255) := 'pune.com';
msg VARCHAR2 (32767);
boundary CONSTANT VARCHAR2 (256)
:= '-----7D81B75CCC90D2974F7A1CBD';
first_boundary CONSTANT VARCHAR2 (256)
:= '--' || boundary || UTL_TCP.crlf;
last_boundary CONSTANT VARCHAR2 (256)
:= '--' || boundary || '--' || UTL_TCP.crlf;
-- A MIME type that denotes multi-part email (MIME) messages.
multipart_mime_type CONSTANT VARCHAR2 (256)
:= 'multipart/mixed; boundary="' || boundary || '"';
BEGIN
DBMS_OUTPUT.ENABLE (100000);
--fnd_file.put_line (fnd_file.output, '------------------------------------------------------' );
v_mailconn := UTL_SMTP.open_connection (gc_mailhost, 26);
UTL_SMTP.helo (v_mailconn, gc_mailhost);
UTL_SMTP.mail (v_mailconn, p_sender);
UTL_SMTP.rcpt (v_mailconn, p_recipient);
UTL_SMTP.open_data (v_mailconn);
msg :=
'Date: '
|| TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
|| crlf
|| 'From:xyz@erp.com '
-- || p_sender
|| crlf
|| 'Subject: '
|| p_subject
|| crlf
|| 'To: abc@erp.com'
-- || p_recipient
|| crlf
|| 'MIME-Version: 3.1'
|| crlf
|| -- Use MIME mail standard
'Content-Type: multipart/mixed;'
|| crlf
|| ' boundary="-----SECBOUND"'
|| crlf
|| crlf
|| '-------SECBOUND'
|| crlf
|| 'Content-Type: text/plain;'
|| crlf
|| 'Content-Transfer_Encoding: 7bit'
|| crlf
|| crlf
|| p_mailbodyhdr
|| crlf
|| crlf
|| p_mailbody
|| crlf
|| crlf
|| crlf
|| crlf
|| -- Message body
'Regards'
|| crlf
|| 'HR and Payroll Team'
|| crlf
|| crlf
|| ' Note- Please ignore the leave status and number of days.'
|| crlf
|| crlf
|| '-------SECBOUND'
|| crlf
|| 'Content-Type'
|| ': '
|| p_filetype
|| crlf
|| 'Content-Disposition: attachment; filename="'
-- || 'Create Accounting'
-- || '.REQ'
||p_filename -- p_filename
|| '"'
|| crlf
|| 'Content-Transfer-Encoding: base64'
|| crlf
|| crlf;

UTL_SMTP.write_data (v_mailconn, msg);
src_file := BFILENAME (dir_name, p_filename);
DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
v_length := DBMS_LOB.getlength (src_file);

WHILE i < v_length
LOOP
DBMS_LOB.READ (src_file, v_buffer_size, i, v_raw);
UTL_SMTP.write_raw_data (v_mailconn,
UTL_ENCODE.base64_encode (v_raw)
);
UTL_SMTP.write_data (v_mailconn, UTL_TCP.crlf);
i := i + v_buffer_size;
END LOOP while_loop;

UTL_SMTP.write_data (v_mailconn, last_boundary);
UTL_SMTP.write_data (v_mailconn, UTL_TCP.crlf);
DBMS_LOB.fileclose (src_file);
UTL_SMTP.close_data (v_mailconn);
UTL_SMTP.quit (v_mailconn);
EXCEPTION
WHEN OTHERS
THEN
UTL_SMTP.quit (v_mailconn);
DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
-- FND_FILE.PUT_LINE (FND_FILE.LOG,'error while fetching values from CO_CODE'|| SQLERRM );
END send_mail;
END xx_create_acct;

Getting the Rtf file from the Database without Accessing

In XMLP all the files such as .xml, .rtf , PDF etc are stored in the BLOB column in xdo_lobs table.

This Document briefs you on the different ways of retrieving the Rtf file from the

Database.There are 2 ways of doing this.

1) Retrieving and Storing a BLOB column in a file using UTL_FILE Package.

2) Retrieving and Storing a BLOB column in a file using Java Stored Procedure.

Retrieving and Storing a BLOB column in a file using UTL_FILE Package

We can retrieve only the RTF files from the Template Manager but using BLOB column we can

retrieve even the .xsl and files.

The following function shows how to retrieve the files stored in the blob column.

:

Step 1:

select file_data

into xmlstr

from xdo_lobs

where file_name='PO_STANDARD_PO.rtf';

Store the BLOB column FILE_DATA of xdo_lobs table in a variable of type BLOB.

Step 2:

--to write blob column data to a file

DECLARE

v_file utl_file.file_type;

line_buf RAW(32767);

maxbufsize BINARY_INTEGER := 32767;

amount BINARY_INTEGER;

offset varchar2(40);

xmlstr BLOB;

dir varchar2(50) := 'ATTACHMENTS';

filename varchar2(50) := 'PO_STANDARD_PO.rtf';

bsize number;

BEGIN

v_file := utl_file.fopen(dir,filename,'w',maxbufsize);

select file_data

into xmlstr

from xdo_lobs

where file_name='PO_STANDARD_PO.rtf';

bsize := dbms_lob.getLength(xmlstr);

dbms_output.put_line(bsize);

amount := maxbufsize;

offset := 1;

while (amount>=maxbufsize)

loop

DBMS_LOB.read(xmlstr,amount,offset,line_buf);

offset := offset+amount;

utl_file.put_raw(v_file,line_buf);

utl_file.fflush(v_file);

end loop;

--close context

utl_file.fclose(v_file);

END;

/

Store the BLOB data in the variable in a file using UTL_FILE package.

Retrieving and Storing a BLOB column in a file using Java Stored Procedure:

Another way is to used a Java Stored Procedure to retrieve the BLOB column.

Step1:

CREATE OR REPLACE JAVA SOURCE NAMED "BlobHandler" AS

import java.lang.*;

import java.sql.*;

import oracle.sql.*;

import java.io.*;

public class BlobHandler

{

public static void ExportBlob(String myFile, BLOB myBlob) throws

Exception

{

// Bind the image object to the database object

// Open streams for the output file and the blob

File binaryFile = new File(myFile);

FileOutputStream outStream = new FileOutputStream(binaryFile);

InputStream inStream = myBlob.getBinaryStream();

// Get the optimum buffer size and use this to create the read/write buffer

int size = myBlob.getBufferSize();

byte[] buffer = new byte[size];

int length = -1;

// Transfer the data

while ((length = inStream.read(buffer)) != -1)

{

outStream.write(buffer, 0, length);

outStream.flush();

}

// Close everything down

inStream.close();

outStream.close();

}

};

/

Load the java stored procedure into the database

Step2:

ALTER java source "BlobHandler" compile;

show errors java source "BlobHandler"

Compile the loaded java stored procedure code

Step3:

CREATE OR REPLACE PROCEDURE ExportBlob (p_file IN VARCHAR2,

p_blob IN BLOB)

AS LANGUAGE JAVA

NAME 'BlobHandler.ExportBlob(java.lang.String, oracle.sql.BLOB)';

/

Create a PL/SQL procedure which maps the java stored procedure

Step4:

EXEC Dbms_Java.Grant_Permission( 'APPS', 'java.io.FilePermission', '*', 'read ,write, execute,

delete');

Grant to scott in order to write to os files

Step5:

declare

xmlstr blob;

begin

select file_data

into xmlstr

from xdo_lobs

where file_name='onepage.rtf';

ExportBlob ('/data/tst1/attachment/sample.rtf', xmlstr);

end;

/Execute the Java Stored procedure

Oracle APPS interview questions

1.List of some API’S
 FND_PROGRAM.EXECUTABLE
 FND_PROGRAM.REGISTER
 FND_PROGRAM.PARAMETER
 FND_PROGRAM.ADD_TO_GROUP
 FND_REQUEST.SUBMIT_REQUEST
 FND_PROFILE.VALUE
 FND_PROFILE.GET

2.How to get second parameter value based on first parameter?

 $flex $ value setname.

3.Forms can be development in APPS in two ways
 A) Customization by extension (using template.fond)
 B) Customization by modification (using custom.pll)

4.How to call WHO columns into the form
 By using FND_STANDARD API’S
  1. FND_STANDARD.FORM_INFO
   Provides information about the form.
   Should be called form when_new_form – instance – instance trigger.
  2. FND_standard.set_who
   loads WHO columns with proper user information.
   Should be called from PRE_UPDTE and PRE_INSERT
   Triggers for each block with WHO fields
   If this is used FND-GLOBAL need not be called.
   (FND_GLOBAL.WHO)
  3. FND_STANDARD.SYSTEM_DATE
   This is a function which returns date.
   Behave exactly like SYSDATE built-in.
  4. FNID_STANDARD.USER
   This is a function which returns varchar2
   Behaves exactly like built in USER.
  5.APPCORE API’S
   APP_COMBO
   APP_DATE
   APP_EXCEPTION
   APP_FIELD
   APP_FIND
   APP_ITEM
   APP_ITEM_PROPERTY
   APP_NAVIGATE
   APP_RECORD
   APP_REGION
   APP_STANDARD
   APP_WINDOW

  6.FNDSQF API’S
   FND_CURRENCY
   FND_DATE
   FND_GLOBAL
   FND_ORG
   FND_STANDARD
   FND_UTILITIES.OPEN_URL
   FND_UTILITIES. PARAM_EXISTS

7.How to call flex fields in the form?

   By using FND_FLEX.EVENT (EVENT varchar 2)

8.How to register an executable and define a concurrent program through backend?

 By using concurrent processing API’S
 1. FND_CONC_GLOBAL.REQUEST_DATA.SET_REQUEST_GLOBALS
 2. FND_CONCURRENT.AF_COMMIT
          .AF_ROLLBACK
          .GET_REQUEST_STATUS
          .WAIT_FOR_REQUEST
          .SET_COMPLETION_STATUS
 3. FND_FILE .
            . PUT_LINE
            .NEW_NAME
            .PUT_NAMES
              .CLOSE
 4. FND-PROGRAM . MESSAGE
 . EXECUTABLE
 . REGISTER
 . PARAMETER
 . IN COMPATIBILITY
 . EXECUTABLE_EXISTS
 5. FND_REQUEST . SET-OPTIONS
 .SET_REPEAT_OPTIONS
 .SET_PRINT_OPTIONS
 .SUBMIT_REQUEST
 .SET_MODE
 6. FND_REQUEST_INFO . GET_PARAM_NUMBER
 . GET_PARAM_INFO
 . GET_PROGRAM
 . GET_PARAMETER
 7. FND_SET . MESSAGE
 .ADD_PROGRAM
 .ADD_STAGE
 .IN COMPATIBILITY
 8. FND_SUBMIT . SET_MODE
 .SET_REQUEST_STATUS
 .SUBMIT_PROGRAM
 .SUBMIT_SET
 9. FND_PROGRAM.EXECUTABLE
 - is used to define a concurrent program executable
 - it takes 8 parameters ( all are IN mode )
 syntax procedure FND_PROGRAM.EXECUTABLE
 (executable IN varchar2,(Full name) à application IN varchar2,(executable short  name) à short name IN varchar2,description IN varchar2 default null
 execution_method IN varchar2,execution_file_name IN varchar2 default null,
 (only fol spauned & Immedaite) à subroutine_name IN varchar2 default null,
 icon_name IN varchar2 default null,language_code IN varchar2 default (VS)
 (for Java Conc Program) à execution_file_path IN varchar2 default null,

10. FND_PROGRAM.REGISTER
 - this procedure no used to define a concument program.
 - It has 30 IN paranmeters. Out of which 9 are mandatory, the remaining are  default.
 Syntax procedure FND_PROGRAM.REGISTER
 (program IN varchar2,application IN varchar2,enabled IN varchar2,short_name IN  varchar2,description IN varchar2, default null,( executable_short_name)  executable_name IN varchar2 executable_application IN varchar2,
 mls_function_shelt_name IN varchar2,mls_function_application IN varchar2,
 incrementor IN varhcar2);

11. How to register a table and columns through back end?

 by using AD_DD package
 - for registering a table à AD_DD.REGISTER_TABLE
 - for registering columns à AD_DD.REGISTER_COLUMN.
 - AD_DD BPI doesn’t check for the existence of the registered table or column in  the data base schema, but only updates the required SQL tables.
 - It should be ensured that, all the tables and columns registered exist actually  and have the same format as that defined using AD_DD API.
 - Views need not be registered.
12. How to write to a file through concurrent program.

 By using FND_FILE package and it can be used only for log and output files.
 FND_FILE package contains procedures to write text to log and output files.
 FND_FILE supports a maximum buffer line size of 32k for both and output files.

13. FND_FILE.PUT
 - this is used to write text to a file with out a new line character
 - Multilane calls to FND_FILE.PUT will produce consummated text.
 Procedure FND_FILE.PUT (which IN Number,Buff IN varchar2);

14.Which log output file
 - can be FND_FILE.LOG or FND_FILE.OUTPUT.
 2. FND_FILE.PUT_LINE
 - this procedure as used to write a line of text to a file followed by a new line  character.
 Procedure FND_FILE.PUT_LINE (which IN number,buff IN varchar2);
 EX:- FND_FILE.PUT_LINE( FND_FILE.LOG, find_message_get);
 3. FND_FILE.NEW_LINE
 - this procedure is used to write line terminators to a file
 procedure FND_FILE.NEW_LINE (which IN number LINES IN NATURAL:=1);
 Ex:- to write two newline characters to a log file Fnd_file.new_line  (fnd_file.log,2);
 4. FND_FILE.PUT_NAMES
 - this procedure as used to set the temporary log file and output filenames and  the temporary directory to the user specified values.
 - This should be called before calling my other FND_FILE procedure and only once  per a session.

15.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 bookan default False,
 argument1,
 argument2,
 argument 100) return number;
 * If this is submitted from oracle forms, all the arguments ( 1 to 100 ) must be  specified.

16. How to display Request ID in the reports?

 By using the parameter P_CONC_REQUEST_ID
 which needs to be defined always in the reports.

17. How to get username / user id in reports?

 - By using the API FND_PROFILE THE OPTION values
 can be manipulated in client / server profile caches
 - FND_PROFILE.GET, FND_GLOBAL.USER_ID,FND_GLOBAL.USER_NAME
 - this procedure is located in FNDSQF library.
 - This procedure is to located to get the current value of the specifed user profile  option or null if the profile does not exist.
 - The server_side PL/SQL package FND_GLOBAL returns the values which need to  set who columns for inserts and updates from stored procedures.
 - Procedure FND_PROFILE.GET ( name in varchar2,Value out varchar2);
 FND_PROFILE.GET (‘USER_ID’, user_id);
 FND_PROFILE.VALUE
 - this function exactly works like GET, except it returns the values of the  specified profile option as a function result
 Function FND_PROGILE.VALUE (name in varchar2
 Return varchar2;

 FND_PROFILE.PUT
 - this is used to put a value to the specified profile option
 - if the option doesn’t exist, it can also be created with PUT.
 Procedure FND_GET (‘USERNAME’, user name);
 FND_PROFILE.GET (‘USER_ID’, user_id);
 - username, user-id, vsp_id , Appl_shrt_Name, Resp_Appl_Id, Form_Name,  Form_id, Form_Appl_Name, Form_Appl_Id, Logon_Date, Last_Lagon_Date,  login_id, Gone_Request_Id, Gone_Program_Id,Gone_program_Application_Id,  Gone_Login_Id, Gone_Print_Output, Gone_printstyle_ these are the user profile  options that can be accessed via user profile option routines .
 - the values of these profile options can be retrieved in forms, reports and  program
 - these values cannot be changed except Gone_Print_Output and  Gone_Print_Style.
 - SA’s and end users also cannot see the values or change these values.
 In which directory log and output files are stored?
 APPLCSF is the top level directory in which the concurrent manager puts logs and  output files.
 APPLLOG and APPLOUT are the subdirectories in which the concurrent manager  puts log and output files.
 APPLCSF variable need to be set in the prod.env ( environmental variable ), so  that all product log files
 $ APPLCSF/ $ APPLLOG à log files
 $APPLCSF/ $ APPLOUT à out files

 concurrent manager log and out put files should go to $ FND_TOP/ $ APPLOG and  $ FND _TOP / $APPLOUT if $ APPLCSF is not set.

18. How to submit concurrent programs through OS?

 - From the operating system the utility .CONCSUB is used to submit is concurrent  program.
 - This is basically used to test a concurrent program .
 - By using the WAIT token. The utility checks the request status every 60  seconds and returns the OS prompt upon completion of the request.
 - Concurrent manager doesnot abort, shutdown or start up until the concurrent  request completes.
 * If the concurrent program is compatible with it self, it can be checked for data  integrity and dead locks by submitting it many times so that it runs concurrently  with it self.
 *PL/SQL procedures can submit a request to run a program as a concurrent  process by calling.
 FND_REQUEST. SUBMIT_REQUEST.
 Before submitting a request, the following functions also should be called  optionally.
 FND_REQUEST.SET_OPTIONS
 FND_REQUEST.SET_REPEAT_OPTIONS
 FND_REQUEST.SET_PRINT_OPTIONS
 FND_REQUEST.SET_MODE

19. How to checks the request states?

 - A PL/SQL procedure can check the status of a concurrent request by calling.
 FND_CONCURENT.GET_REQUEST_STATUS
 FND_CONCURRENT.WAIT_FOR_REQUEST
 FND_CONCURRENT.GET_REQUEST_STATUS
 - This function returns the status of a concurrent request
 - If the request is already computed, it also returns the completion message.
 - This function returns both user friendly (translatable) phase and status values  as well as developer phase and status vales that can drive program logic.
 Syntax 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 varchar,
 dev_phase out varchar2,
 dev_status out varchar2,
 message out varchar2) return BOOLEAN;
 - when application and program are specified, the request_id of the last request  for the specified program should be returned to request_id.
 - Phase, and status values should be taken from
 FND_LOOKUPS
 dev_phase dev_status
 pending normal, standby, scheduled, paused
 running normal, waiting, resuming, terminating.
 Complete normal, Error, warning, cancelled, terminated
 Inactive disabled, on-hold, No-manager, supended
 - FND_REQUEST.WAIT_FOR_REQUEST
 - This function waits for request completion, then returns the request  phase/status and completion message to the caller.
 - Goes to sleep between checks for request completion.
 Syntax FND_CONCURRENT.WAIT_FOR_REQUEST
 ( request_id in number default null,
 interval in number default 60,
 max_wait in numbe default 0,
 phase out varchar2,
 status out varchar2,
 dev_phase out varchar2,
 dev_status out varchar2,
 message out varchar2) return BOOLEN;
 FND_CONCURRENT.SET_COMPLETION_STATUS
 - this function should be called from a concurrent program to set its completion  states.
 - This function returns TRUE on success, other wise FALSE.
 Syntax function FND_CONCURRENT.SET_COMPLETION_STATUS
 ( status in varchar2, message in varchar2) return BOOLEAN;


20. How to generate fmx at OS level?

 - Forms can be generated on the forms server as the APPLMGR user.
 - Generating the form on the Form server, $ FORM60_PATH

 Should be set and the current directory should be $AV_TOP/forms?us.
 Syntax à $F60 gen userid=apps/appsp module=<form_name>. Fmb
 Output_file=<schema_top>/forms?<language>/<form_name>.fmx
 Module_type=form bath =no compile_all=special




21.Flex fields:

Used to store information regarding industry, work or person.
Flexfiled  are made up of subfields and segments. They ca be differentiated into Key flexfield (KFF) and descriptive flexfiled(DFF)
KFF stores the key information of an organization, which appears as normal text field.
Where as descriptive flexfield stores the additional information, appears in square bracket, and show the value only if it is clicked.

Meaning full keys are called intelligent keys.

KFF & DFF uses Segment, value, validation, value set, and structure

Segment:-Subfields can be defined for the flexfield called segments. In database it will be stored in a single data column.

Value, Validation, Valuset:-are user dependent values given for the described segments.

e.g:-a segment Age has value column to be filled up, valueset can be 20, 30and 40 as we can set. After validation only defined value set can be accessed or compiled.
Structure:-Configuration of segment.

DFF used two different types of segments

1. Global-appears in pop-up window always.

2. Context Sensitive-optional to be viewed

Qualifiers:
Flexfield uses Qualifiers to identify a particular segment of KFF

PL/SQL Scripts

List all the registered concurrent programs by module 
 Summary
SQL script to list all the registered concurrent programs by module.
SELECT   SUBSTR(a.application_name,1,60) Application_NAME
,        b.application_short_name SHORT_NAME
,        DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
         ,      'Concurrent Manager Executable'
         ,      'Subprogram or Function') TYPE
,        SUBSTR(d.concurrent_program_name,1,16) PROGRAM
,        SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME
FROM     applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b
WHERE    cp.application_id = a.application_id
AND      d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND      a.APPLICATION_ID = b.APPLICATION_ID
AND      b.application_short_name LIKE UPPER('PA')
UNION ALL
SELECT   SUBSTR(a.application_name,1,60) c1
,        b.application_short_name c2  ,        'Form Executable' c3
,        SUBSTR(f.form_name,1,16) c4 ,       
SUBSTR(d.user_form_name,1,55) c5
FROM     applsys.fnd_form f ,        applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d
WHERE    f.application_id = a.application_id
AND      d.FORM_ID = f.FORM_ID
AND      a.APPLICATION_ID = b.APPLICATION_ID
AND      b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;

A list of E-Business Suite modules along with some usefull columns.
SELECT * FROM
(
SELECT fat.application_name MODULE,
       fa.application_short_name short_name,
       fa.basepath,
       fat.description,
       fl.meaning,
    fpi.patch_level,
       fpi.product_version
  FROM apps.fnd_application fa,
       apps.fnd_application_tl fat,
       apps.fnd_product_installations fpi,
       apps.fnd_lookups fl
 WHERE fa.APPLICATION_ID = fat.APPLICATION_ID
   AND fa.application_id = fpi.application_id
   AND fpi.status = fl.lookup_code
   AND fl.lookup_type = 'FND_PRODUCT_STATUS'
   AND fat.LANGUAGE = USERENV('LANG')
UNION
SELECT fa.application_short_name,
       fa.basepath,
       fat.application_name,
       fat.description,
       'Custom', ' ',
       fu.user_name||' ( '||fa.creation_date ||' )'
  FROM apps.fnd_application fa,
       apps.fnd_application_tl fat,
       apps.fnd_user fu
 WHERE fa.APPLICATION_ID = fat.APPLICATION_ID
  AND fat.LANGUAGE = USERENV('LANG')
  AND fa.created_by = fu.user_id
  AND fa.application_id NOT IN ( SELECT fpi.application_id
                                   FROM apps.fnd_product_installations fpi )
           )
--where short_name like '%AR%'
ORDER BY MODULE ASC;

Output example when filter with short_name like '%AR%'

Module:Oracle Receivables
Short_name:AR
Short_name:AR
basepath:AR_TOP
Description:Oracle Receivables
Meaning:Installed
Patch_level:11i.AR.L

SQL script to lists all the profile settings

SELECT pot.user_profile_option_name "Profile"
 , DECODE( a.profile_option_value
          , '1', '1 (may be "Yes")'
          , '2', '2 (may be "No")'
          , a.profile_option_value) "Value"
 , DECODE( a.level_id
          , 10001, 'Site'
          , 10002, 'Appl'
          , 10003, 'Resp'
          , 10004, 'User'
          , '????') "Levl"
 , DECODE( a.level_id
          , 10002, e.application_name
          , 10003, c.responsibility_name
          , 10004, d.user_name
          , '-') "Location"
FROM applsys.fnd_application_tl e
 , applsys.fnd_user d   , applsys.fnd_responsibility_tl c
 , applsys.fnd_profile_option_values a   , applsys.fnd_profile_options b
 , applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
 AND pot.profile_option_name = b.profile_option_name
 AND b.application_id = a.application_id (+)
 AND b.profile_option_id = a.profile_option_id (+)
 AND a.level_value = c.responsibility_id (+)
 AND a.level_value = d.user_id (+)   AND a.level_value = e.application_id
(+)
 AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
 OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
 OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
 ORDER BY "Profile", "Levl", "Location", "Value"
/

Concurrent Programs assigned to a request type 

SELECT RC.REQUEST_CLASS_NAME,
       CPTL.USER_CONCURRENT_PROGRAM_NAME,
       CP.CONCURRENT_PROGRAM_NAME
  FROM APPLSYS.FND_CONCURRENT_PROGRAMS_TL CPTL,
       APPLSYS.FND_CONCURRENT_PROGRAMS CP,
       APPLSYS.FND_CONCURRENT_REQUEST_CLASS RC
 WHERE RC.APPLICATION_ID = CP.CLASS_APPLICATION_ID
   AND RC.REQUEST_CLASS_ID = CP.CONCURRENT_CLASS_ID
   AND CP.APPLICATION_ID = CPTL.APPLICATION_ID
   AND CP.CONCURRENT_PROGRAM_ID = CPTL.CONCURRENT_PROGRAM_ID
 ORDER BY REQUEST_CLASS_NAME, USER_CONCURRENT_PROGRAM_NAME;

 

Set Who columns in pre-insert:

 

BEGIN

DATA BLOCK NAME;

FND_STANDARD.SET_WHO;

PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)          

END;

 

Set Who columns in pre-update:

 

begin

                XXGOD_JOB_NO;

                XXGOD_REV_ADD;

                fnd_standard.set_who;

                XXJOB_CARD.VALIDATE_APPROVAL1;

end;

 

Set triggers at  when-new-record-instance-level

DECLARE

                v_count number;

                v_count2 number;

 BEGIN

 

                SELECT count(*) into v_count

    FROM fnd_lookup_values

   WHERE lookup_type = ‘Receipt Number’(count records ad new in db)

  and sysdate between start_date_active and nvl(end_date_active,sysdate)

 and lookup_code=fnd_global.user_id;



                IF v_count = 0 then

--set datablock and procedure to validate                                               SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);

                                END IF;

 

  IF v_count = 1 THEN

  --SET_ITEM_PROPERTY('XXGOD_QUOTATION_HDR1.SALES_ORDER',ENABLED,PROPERTY_FALSE);

                                                  IF :XXGOD_JOB_CARD.APPROVALS ='Submitted' THEN

                                                   SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_TRUE);

                                                  ELSE

                                                   SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);

                                    END IF;

  END IF;

 

 END;

 

 

Validating Procedure and Package:

PACKAGE XX_VALIDATE IS

  PROCEDURE VALIDATE_APPROVAL;

  PROCEDURE VALIDATE_APPROVAL1;

  PROCEDURE XVALID_DESC_PRO(EVENT IN VARCHAR2);

END;

 

PACKAGE BODY XX_VALIDATE IS

  PROCEDURE VALIDATE_APPROVAL IS

  BEGIN

                IF  :DATA_BLOCK_NAME.APPROVALS IS NULL THEN

                                                                FND_MESSAGE.SET_STRING('Status needs to be selected');

                                                                  FND_MESSAGE.SHOW;

                                                                  RAISE FORM_TRIGGER_FAILURE;

                END IF;

                IF  :DATA_BLOCK_NAME.APPROVALS!='New'THEN

                                                                                                FND_MESSAGE.SET_STRING('This is a new Job card,select the status as New');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                RAISE FORM_TRIGGER_FAILURE;

                ELSE

                                                                XX_VALIDATE_1(CALLING ANOTHER PROCEDURE);

                END IF;

                EXCEPTION

                                WHEN OTHERS THEN

                                FND_MESSAGE.ERROR;

                                RAISE FORM_TRIGGER_FAILURE;

                END;

 ------------------------------------------------------------------------------------------------

                 PROCEDURE VALIDATE_APPROVAL1 IS

                 CURSOR C1 IS SELECT APPROVALS FROM table_name WHERE JOB_ID=:data_bolck.item;

   v1 VARCHAR2(20);

                 BEGIN

                                OPEN C1;

                                FETCH C1 INTO v1;

                               

  IF V1!=:DATA_BLOCK_NAME.APPROVALS THEN                           

                               

                                                IF V1='New' AND :DATA_BLOCK_NAME.APPROVALS!='Submitted' THEN

                                                                                                FND_MESSAGE.SET_STRING('This is a new Job card,needs to be Submitted');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                RAISE FORM_TRIGGER_FAILURE;

                                                END IF;

                                               

                                IF V1='Submitted' and :DATA_BLOCK_NAME.APPROVALS not in('Approved','Rejected') THEN

                                                                                                FND_MESSAGE.SET_STRING('Submitted Job card,needs to be Approved or Rejected');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                raise form_trigger_failure;

                                END IF;

                               

                                                                IF V1='Rejected' and :DATA_BLOCK_NAME.APPROVALS not in( 'Rejected','Submitted','Closed') THEN

                                                                                                FND_MESSAGE.SET_STRING('Rejected Quotation can only be Submitted or Closed');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                raise form_trigger_failure;

                                END IF;

                  

                                                                if v1='Approved' and :DATA_BLOCK_NAME.approvals not in('Inactive','Active') then

                                                                fnd_message.set_string('Approved Job can be Active or Inactive');

                                                                fnd_message.show;

                                                                raise form_trigger_failure;

                                                                end if;

                                                               

                                                                IF v1='Inactive' and :DATA_BLOCK_NAME.approvals!='Active' then

                                                                                                FND_MESSAGE.SET_STRING('Select the status as Active');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                raise form_trigger_failure;

                                                   END IF;

                                                               

                                                                IF v1='Active' and : DATA_BLOCK_NAME .approvals!='Closed' then

                                                                                                FND_MESSAGE.SET_STRING('Active Job card has to be Closed');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                raise form_trigger_failure;

                                                                END IF;

                                                if  v1='Closed' and : DATA_BLOCK_NAME.approvals!='Closed' then

                                                                                                FND_MESSAGE.SET_STRING('Closed Job card can not be modified');

                                                                                                FND_MESSAGE.SHOW;

                                                                                                raise form_trigger_failure;

                                                end if;  

                END IF;

                CLOSE C1;

                                EXCEPTION

                                                                WHEN OTHERS THEN

                                                                FND_MESSAGE.ERROR;

                                RAISE FORM_TRIGGER_FAILURE;

                                END;

 

PROCEDURE XGOD_DESC_PRO(EVENT IN VARCHAR2)

IS

BEGIN

                IF EVENT='PRE-FORM' THEN

                FND_DESCR_FLEX.DEFINE(

                                                BLOCK => 'XXGOD_JOB_CARD',

                                                FIELD => 'DFF1',

                                                APPL_SHORT_NAME => 'PO',

                                                DESC_FLEX_NAME => 'JobCardDFV');

                END IF;

                IF EVENT='WHEN-NEW-FORM-INSTANCE' THEN

                FND_DESCR_FLEX.DEFINE(

                                                BLOCK => 'XXGOD_JOB_CARD',

                                                FIELD => 'DFF1',

                                                APPL_SHORT_NAME => 'PO',

                                                DESC_FLEX_NAME => 'JobCardDFV');

                                               

                FND_DESCR_FLEX.DEFINE(

                                                BLOCK => 'XXGOD_JOB_CARD',

                                                FIELD => 'DFF',

                                                APPL_SHORT_NAME => 'PO',

                                                DESC_FLEX_NAME => 'JobCardDFF1');

                END IF;

 

                EXCEPTION

                                WHEN OTHERS THEN

                                RAISE FORM_TRIGGER_FAILURE;

                END;

END;