We can develop XML Publisher Report by using following steps:
1)Develop the Report(.rdf) Only DataModel
2)Save the report and Compoile and Generate the data in .xml
Format File=>Generate to File => XML
3)Open the Ms-Word document Design the Layout include the follwing Tool Bar options called Form and Templete builder
4)Define the Table and Specify the Titles and Labels whatever we would like to print
5)Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax : Here G_USER_ID is Group name from .rdf Data Model Note: It means Indirectley Repeating Frame starting
6)Define the Fields which we would like to display Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax ?
Note: Define all the fields like this
7)We have to close the Repeating Frame (For each Loop) Include 'Text Form Field' Double CLick => Add Help Text button enter the following syntax
8)Save the document in .rtf(Rich Text File format)
9)Load the .xml file data and generate the Output in pdf Format Data=>Load XMl Data=>Load the .xml File=>Once Data is Loaded succesfully
10)Generate the Preview as per this Preview=>PDF
Registering in Apps
1)Develop the .rdf file and .rtf file
2)Register both .rdf and .rtf file
.rdf File Registration
1)Move the .rdf file into CUS_TOP\11.5.0\Reports\US
2)Create Execuiteable
Concurrent Program Output = XML
Request Group
Responsibility
User
SRS
3)Copy the Concurrent Program Short name .rtf File
RTF Registration:
1)Goto the XML Publisher Administrator Responsibility
2) Creation of Data Defination
Select Data Definations form Enter Data Defination
Name = Enter Any Name
Code = Concurrent Program Short Name
Application = Any application
startdate = Current Date
3)Copy the DataDefination name we have to add this to the Templete
4)Creation of Template form
Open the Templete Form
Enter Templete Name : Any Name
DataDefination : enter data defination whatever we have created
Code : Concurrent Program Short Name
Type : rtf
application : any Application
File : Upload the .rtf file
Laguage : English
Territory : United States
5)Select Apply button
After register the .rtf file submit concurrent Program from SRS window system will automatically pick the .rtf file and generates the Output.
If we want to customze the .rtf file we have to download by using templete , Customize thenupload the .rtf templeteme, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal
Steps to create a XML Publisher Report
1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.
2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.
3] Set the user parameter as p_conc_request_id.
4] Add the default values to the Before Report and After Report triggers(not mandatory)
5] Ftp the Report to the Cust_Top/Report/Us.
6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.
7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.
8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.
9] Design the template in Ms Word(Using the .Rtf file).
10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.
11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.
12] Now go to the Responsibility and run the request.
Setting the Password for PDF File sent through XML Publisher
Open the rtf for which you want to set password and do the following things
1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true
Note : property name should always start with xdo- .
/***********CODE TO DELETE XML TEMPLATE***************/
BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
xdo_templates_pkg.DELETE_ROW (
X_APPLICATION_SHORT_NAME => 'XX',
X_TEMPLATE_CODE => 'XXRSDWIPSR-P'
) ;
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/***********CODE TO DELETE XML DATA DEFINITION *******************/
BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (
X_APPLICATION_SHORT_NAME => 'XX',
X_DATA_SOURCE_CODE => 'XXRSDWIPSR-P'
);
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/******************* DELETING TEMPLATES AND FILES ********************/
SELECT * FROM XDO_LOBS WHERE LOB_CODE = 'XXRSDWIPSR-P';
DELETE FROM XDO_LOBS
WHERE LOB_TYPE = 'TEMPLATE_SOURCE'
AND APPLICATION_SHORT_NAME = 'XX'
AND LOB_CODE = 'XXRSDWIPSR-P'
AND FILE_NAME = 'XX_PICK_SLIP_REPORT_PARENT.rtf'
DELETE FROM XDO_LOBS
WHERE LOB_TYPE = 'TEMPLATE'
AND APPLICATION_SHORT_NAME = 'XX'
AND LOB_CODE = 'XXRSDWIPSR-P'
AND FILE_NAME = 'XX_PICK_SLIP_REPORT_PARENT.xsl'
/******************* DELETING PROGRAM ********************/
SELECT * FROM FND_CONCURRENT_PROGRAMS_TL WHERE USER_CONCURRENT_PROGRAM_NAME = 'RRL - EFT (ICICI) Output Generation';
SELECT * FROM FND_CONCURRENT_PROGRAMS WHERE CONCURRENT_PROGRAM_ID = 67358;
SELECT * FROM FND_APPLICATION WHERE APPLICATION_ID = 20003;
SELECT OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%FND%APPLICATION%' AND OBJECT_TYPE IN ('TABLE')
BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
FND_PROGRAM.DELETE_PROGRAM (
PROGRAM_SHORT_NAME => 'XXRSDWIPSR-P',
APPLICATION => 'XX'
);
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/****************** DELETING EXECUTABLE **************/
SELECT * FROM FND_EXECUTABLES_TL WHERE USER_EXECUTABLE_NAME ='';
SELECT * FROM FND_EXECUTABLES WHERE EXECUTABLE_ID = 67358;
SELECT * FROM FND_APPLICATION WHERE APPLICATION_ID = 20003;
SELECT OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%FND%APPLICATION%' AND OBJECT_TYPE IN ('TABLE')
BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
FND_PROGRAM.DELETE_EXECUTABLE (
EXECUTABLE_SHORT_NAME => 'XXRPGICICIEFT',
APPLICATION => 'XXRPG'
);
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/************** ADDING LAYOUT TO A REQUEST FROM BACKEND ************/
ln_layout := fnd_request.add_layout (template_appl_name => 'SQLAP',
template_code => 'XXRPGICICIEFT',
template_language => 'en',
template_territory => 'US',
output_format => 'EXCEL');
/**************** SUBMITTING A REQUEST FROM BACKEND ************/
fnd_request.submit_request(application => 'SQLAP',
program => 'PROGRAM NAME
argument1 => 'argument1',
argument2 =>'argument2',
argument3 => 'argument3'
);
/************* FINDING REQUEST GROUP - CONCURRENT PROGRAM ***********/
SELECT fcpt.user_concurrent_program_name,frg.request_group_name
FROM fnd_request_groups frg,fnd_request_group_units frgu,fnd_concurrent_programs_tl fcpt
WHERE frg.request_group_id = frgu.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
/************* FND_GLOBAL.APPS_INITIALIZE ****************/
select * from fnd_user where user_name = 'USER NAME'; --15647
SELECT * FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME = 'RESPONSIBILITY NAME'; --54204,660
FND_GLOBAL.APPS_INITIALIZE (FND_GLOBAL.USER_ID,FND_GLOBAL.RESP_ID,FND_GLOBAL.RESP_APPL_ID);
/********** TOTAL AMOUNT OF TIME FOR WAIT EVENT ******************/
select time_waited from v$system_event where event = 'PL/SQL lock timer';
/************ QUERY TO FIND COLUMN AND TABLE DESCRIPTION *******************/
SELECT fa.application_id
, fa.application_short_name
, fat.application_name
, table_name
, column_name
, ft.description table_description
, fc.description column_description
FROM fnd_tables ft
, fnd_columns fc
, fnd_application_tl fat
, fnd_application fa
WHERE ft.table_id = fc.table_id
AND fc.column_name = 'INVENTORY_ITEM_ID'
AND fat.application_id = ft.application_id
AND fat.LANGUAGE = USERENV ('LANG')
AND fa.application_id = fat.application_id
/*************** QUERY TO FIND CONCURRENT PROGRAM DETAILS ***************/
SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = 'FSD RSD Pick Slip Report'
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num
/*********** QUERY TO FIND LIST OF RESPONSIBILITIES ATTACHED TO A USER **********/
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = 'JOSPHIN'--:user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')
No comments:
Post a Comment