SQL to find XML Files (RTF/Data Template/RTF ) Details

Given SQL is to fetch the files details uploaded through XML Publisher Administrator Responsibility

SELECT
a.application_short_name,
a.data_source_code,
b.lob_code,
b.lob_type,
b.file_name,
b.last_update_date
FROM
XDO_DS_DEFINITIONS_VL a,
XDO_LOBS b
WHERE a.data_source_code =b.lob_code
AND a.application_short_name=’XXXXX’
AND a.data_source_code =:ConCurrentProgram or Data Defintion Code;

Import data Template/RTF/Bursting control files

Problem is that any custom XML publisher report requires dependency on application access in PROD (XML
Publisher Administrator (R)). All supporting files for XML report are being uploaded through application.

Requirement is to have any import method for XML publisher supporting files i.e.
Data Template file (XML file)
Bursting Control File (XML file)
Template (RTF file)

Resolution
XDOLoader is the standard OA utility to suffice this requirement. This is similar to unix based FNDLOAD utility and
should be run from server with all parameters.

Syntex

java oracle.apps.xdo.oa.util.XDOLoader
MODE UPLOAD/DOWNLOAD
–DB_USERNAME apps user name
–DB_PASSOWRD apps password
–JDBC_CONNECTION jdbc connection string
–LOB_TYPE TEMPLATE_SOURCE/
DATA_TEMPLATE/
BURSTING_FILE
–APPS_SHORT_NAME xxxxx
–LOB_CODE Data definition Code
–LANGUAGE en
–XDO_FILE_TYPE RTF/XML-DATA-TEMPLATE
/XML-BURSTING-FILE
[–CUSTOM_MODE] FORCE
[–LOG_FILE] log file name
[-DEBUG] true/false

RTF File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -
JDBC_CONNECTION "(DESCRIPTION= (ADDRESS=(
PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))" -LOB_TYPE
TEMPLATE_SOURCE -APPS_SHORT_NAME XXXXX -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en -
XDO_FILE_TYPE RTF -FILE_NAME123.rtf -CUSTOM_MODE FORCE
 Data Template File >>
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -
JDBC_CONNECTION "(DESCRIPTION= (ADDRESS=(
PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))" -LOB_TYPE
DATA_TEMPLATE -APPS_SHORT_NAME XXXXX -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en -
XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME 123.xml -CUSTOM_MODE FORCE

Bursting File >>

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD appsPWD -
JDBC_CONNECTION "(DESCRIPTION= (ADDRESS=(
PROTOCOL=TCP)(Host=XXXXXXXXXX)(Port=100))(CONNECT_DATA=(SID=EBS123)))" -LOB_TYPE
BURSTING_FILE -APPS_SHORT_NAME XXXXX -LOB_CODE DATA_DEFINITION_CODE -LANGUAGE en -
XDO_FILE_TYPE XML-BURSTING-FILE -FILE_NAME 123b.xml -CUSTOM_MODE FORCE

**Highlighted are user defined inputs

Multiple Queries in Data Templete

Problem – Oracle report builder provides flexibility to create multiple queries and join those with common attribute.
How does data template method help us to use multiple queries?

Solution – Data template also provides same flexibilities to list down queries in multiple sections and connect them
as parent child relationship.
There are two approached available to join multiple queries in data template.
 Bind variable
 Data link method
Bind Variable most efficient and faster option. Identify the common column in parent query and use it as bind
variable in child queries.
<dataQuery>
<sqlstatement name="Q1_USER">
<![CDATA[
SELECT user_id BIND_USER_ID ,user_name From fnd_user
]]>
</sqlstatement>

<sqlstatement name="Q2_RESP">
<![CDATA[
SELECT responsibility_id From fnd_user_resp_groups WHERE user_id=: BIND_USER_ID
]]>
</sqlstatement>
</dataQuery>

Data Link is another way to establish the relationship between multiple queries
<link name="USER_RESP_LINK" parentQuery="Q1_USER" parentColumn="user_id" childQuery="Q2_RESP"
childColumn="user_id" condition="Q1_USER.user_id= Q2_RESP.user_id" />

XML Output
<?xml version="1.0" encoding="UTF-8" ?>
< XXXOALEARNING >
<P_CUSTOMER/>100 </P_CUSTOMER/>
<LIST_G_USER>
<G_USER>
<USERID>100</USERID>
<USERNAME>Sandeep</USERNAME>
<LIST_G_RESP>
<G_RESP>
<RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >
<RESPONSIBILITY_ID>1020</ RESPONSIBILITY_ID >
</G_RESP>
<LIST_G_RESP>
</G_USER>
<G_USER>
<USERID>200</USERID>
<USERNAME>Deepak</USERNAME>
<LIST_G_RESP>
<G_RESP>
<RESPONSIBILITY_ID>1010</ RESPONSIBILITY_ID >
<RESPONSIBILITY_ID>1030</ RESPONSIBILITY_ID >
</G_RESP>
<LIST_G_RESP>
</G_USER>
</LIST_G_USER>
</ XXXOALEARNING >

Insert Table in XML Layout

Requirement – Insert a Table in xml layout. Inserting table in MS word and placing place holder creates some
alignment issues.

Options – Instead of creating table in MS word manually, we can use BI publisher desktop utility to insert table in
RTF file.

1. Generate the XML Tags

2. Open the MS Word to create RTF layout
3. Go to Add on ( Menu) >> Data >> Load XML Data

4. Browse the xml file and load

5. Go to Add on ( Menu) >> Insert >> Table/Form>>Select the group and drag to Template section and
press Drop All Nodes
 6. Select the Group and pick Table property (Other options are form and free form).Select and delete any

unwanted field and press OK.

Table will be created with all column and respective place holders.

XML Report Using Source as Data Template

Problem – Generate XML source for BI publisher report.

Solution – There is several options available to generate XML tags, intend as source for BI/XML publisher reports in
EBS. One among those is use of data template.

Data Template is an xml file having predefined set of tags. Create a data template file and attach file at (XML
Publisher Administrator (R) >> Data Definition >> Add Files >> Data Template)

Header <?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name="XXXOALEARNING"
dataSourceRef="ORCL_DB1" defaultPackage=”T1” version="1.0"
>

Data Trigger <dataTrigger name="beforeParameter"

source="T1.beforeParameterFunction"/>

<Parameters>
Parameter <parameter name="p_param1" datatype="number"/>
Section </Parameters>
 Data Trigger <dataTrigger name="afterParameter"
source="T1.afterParameterFunction"/>
<dataTrigger name="beforeReport"
source="T1.beforeReportFunction"/>

Query Section : <dataQuery>

We can use <sqlstatement name="Q_EMP">
multiple query <![CDATA[SELECT empname From EMP WHERE
here empid=: p_param1]]>
</sqlstatement>
</dataQuery>

Data Structure : <dataStructure>

XML tag <group name="G_EMP" source="Q_EMP">
generation. <element name="EMPNAME" value="empname"/>
</group>
</dataStructure>

Data Trigger <dataTrigger name="aftreReport" source="T1. aftreReportFunction

"/>

</dataTemplate>

Sample Data Template File

<?xml version="1.0" encoding="WINDOWS-1252" ?>
<dataTemplate name="XXXOALEARNING" dataSourceRef="ORCL_DB1" defaultPackage="T1" version="1.0" >
<Parameters>
<parameter name="p_user_id" datatype="number"/>
</Parameters>
<dataQuery>
<sqlstatement name="Q_USER">
<![CDATA[
SELECT user_id,
User_name,
FROM FND_USER
where
user_id=:p_user_id
]]>
</sqlstatement>
</dataQuery>
<dataStructure>
<group name="G_USER" source="Q_USER">
<element name="USERID" value="user_id"/>
<element name="USERNAME" value="user_name"/>
</group>
</dataStructure>
</dataTemplate>

Sample generated XML -

<?xml version="1.0" encoding="UTF-8" ?>
< XXXOALEARNING >
<P_CUSTOMER/>100 </P_CUSTOMER/>
<LIST_G_USER>
<G_USER>
<USERID>100</USERID>
<USERNAME>Sandeep</USERNAME>
</G_USER>
</LIST_G_USER>
</ XXXOALEARNING >

XML Publisher - Different Executables & Data Source available

This post is to list out different executable and corresponding data source available for XML publisher report.

System Administrator Responsibility -

Concurrent >> Program >> Executable >> Create Executable file
XML_LEARNING (Oracle Report Builder)
OA_LEARNING.XML_LEARNING (PL/SQL Stored Procedure)
XDODTEXE (Java Concurrent Porgram) <*** Standard program provided by Oracle>

Concurrent >> Program >> Define >> Create Program

Executable Name – should be as above
Output Format - XML

XML Publisher Responsibility -

Data Definition >> Create New data definition (code should be CP name)
Data Template File = Attached if data source is XML file and CP is Java concurrent program
Bursting File = Attach XML file, if bursting is required

Template >> Create a new template and attach the layout (RTF) file

Table: Different Executable and Data source for BI Report

 XML Bursting
Requirement -Split the generated report output into multiple reports based on no of
suppliers.

For example - Report is having 3 invoices, 2 for one supplier and 1 for second. concurrent
program will generate one file as output having all 3 invoice detail.
Project need is to split output either on 3 files (one for each invoice) or 2 files ( for 2
supplier).

XML bursting concept help us to cut down the generated output in multiple pieces based on
requirement.
Also we can send output through email using this service.

Assumption - SMTP configuration should be available

Steps for XML bursting -

1) Create and set temp folder
- Create one folder in unix box ( application server) and set the temp path in application
- Go to XML administrator responsibility
- Administrator Function
- Give the folder path for TEMP directory

2)Create the bursting control file

Create the control file. control file contains split logic and delivery method, which will be
used by bursting engine. Sample file as below-

<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" typ

e="bursting">

<xapi:request select="/XXXXX/LIST_G_SUPPLIER_NO/G_SUPPLIER_NO">
<xapi:delivery>

<xapi:email id="123" server="smtp.host.com" port="25" from="${EMAILFROM}" reply-

to="${EMAILFROM}">

<xapi:message id="123" to="${SUPPLIERSITEEMAIL}" cc="$

{EMAILCC}" attachment="true" subject="XML Bursting">This program is for xml
bursting check

</xapi:message> </xapi:email> </xapi:delivery>

<xapi:document output="AttachmentName" output-type="pdf" delivery="123">

<xapi:template type="rtf" location="xdo://APPSHORTNAME.TEMPLATECODE.en.00/?

getSource=true" />

</xapi:document>

</xapi:request></xapi:requestset>

*${XML_TAG_NAME}
Request – Describe group level, on which bursting is required. In above example bursting
will be on G_SUPPLIER_NO level
Delivery – How to send the split data ( Email, Fax, Printer etc).
Document output – Subject and format of output
Template Type – Related template information. Location can have temp directory path
( make sure your rtf file is placed at temp directory too) too or use the above syntax only.

***en is the territory code.Either use the correct territory code or upload rtf file without territory
name.

3)Place the control file –

XML publisher responsibility >> Data Definition >> Search for your Data Definition
>> Bursting Control file >> Add File
 4)Invoke Bursting Concurrent Program – XDOBURSTREP is oracle provided standard
concurrent program for bursting. Invoke this concurrent program in your program at the
last.
This will read the instructions given in control file and perform bursting.

l_request_id := FND_REQUEST.SUBMIT_REQUEST (
'XDO',
'XDOBURSTREP',
'XML Publisher Report Bursting Program',
'',
FALSE,
'N',
fnd_global.conc_request_id, 'Y',
chr(0), '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '');

Different Header and Footer for First Page - XML Publisher Report
Requirement was to report invoice details in xml publisher in PDF format. If there is multiple pages for any invoice, it should
show different header details.
In rtf it is very easily achievable using MS word features.
 1) Open the rtf file
 2) Go Header & Footer section
 3) Check the flag “Different First Page”


 4) Write the Header/footer Details


 5) Go to the second page (if rtf has only one page, put some line space to reach second page)
 6) Create second header/footer details


 7) Remove the extra line space created in step 5
 8) Load the data source
 9) Preview the output

Defects Manager - XML Publisher

Using troubleshoot page, I shall summarize the list of problems faced during xml report creation and it's resolution.

1) Leading zero issue - Invoice number can have leading zeros ("000INV1").Reporting any invoice number field in
excel format can not preserve the leading zeros and display only "INV1".

Solutions - use inbuilt function fo-bidi

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?INVOICE_NO_TAG?></fo:bidi-override>
or
Open the template and put some space before invoice number tag field.
==========================================================================
2) RTF formatting issue – Any changes to border and shading is reverting back if we close the rtf and re open it.
Solution – While changing border and shading apply it to TABLE level not COLUMN level. Changes at TABLE level
will be preserved. refer below screenshots -

==========================================================================
3) OPP service failure - This issue occurs during concurrent program execution and most frequently while CP is
generating any xml report and program will end in warning.
Solution - Check with DBA and ask to up the OPP service if down or check your template. there will be few garbage
character available in template, which is causing the issue.(For more details check - OPP Service post)

XML Publisher - A Beginning ....

Basic steps to create BI Reports -
1) Create the simple oracle report (rdf file)
2) create concurrent program with executable as oracle report
3) set the output format as XML
4) run the program. output report will generate the xml tags
5) Now create Data Definition with code as concurrent program name in xml publisher admin(R)
6) create the rtf template (in word/excel/pdf)
7) use can xml tags generated in step 3 as data source to validate the output in rtf template
8) create the template with default output format and associate with created data definition and attach the rtf file.
now run the concurrent program output will be in the specified format.

SQL - Concurrent Program

/* Query to check program status and completion time*/

SELECT REQUEST_ID,
FU.USER_NAME,
USER_CONCURRENT_PROGRAM_NAME,
DECODE(PHASE_CODE,'C','Completed','R','Running','E','Error','P','Pending','Other') PHASE,

DECODE(STATUS_CODE,'C','Normal','E','Error','G','Warning','X','Terminated','Q','Sacheduled',STATUS_CODE)STA
TUS ,
TO_CHAR(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') STARTED_AT,
TO_CHAR(ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ENDS_ON,
ROUND((NVL(ACTUAL_COMPLETION_DATE,SYSDATE)-ACTUAL_START_DATE)*24*60) "Duration (mins)",
argument_text
FROM FND_CONC_REQ_SUMMARY_V FCR,
FND_USER FU
WHERE FCR.REQUESTED_BY=FU.USER_ID
AND PROGRAM_SHORT_NAME LIKE ( 'XXXXX%')
ORDER BY NVL(ACTUAL_START_DATE,SYSDATE-1000) DESC

Excel Output Without XML publisher

Requirement– There are limited formats available to view Concurrent Program output. XML is one format which is
not available.

Available Options – XML publisher is one frequently used solution, which has capability to display output in PDF,
EXCEL and RTF formats. EXCEL file generated using xml processing is having more size compare to actuals.

Solution – This post is to discuss another way to generate output file in EXCEL format without using XML publisher.
A small setup change is required.
1. Create a concurrent program having PL/SQL as executable
2. Select the output type as any available option (ex - PCI)
3. Print the output using FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’Output Message’);
4. Change the Mime type(view option) as EXCEL for PCL file format

5. Run the concurrent program

6. Select Tool>>Copy option to see the output
 Script to Update the Mime Type
Limitation – To view multi column format, use a separator during output print. Once output is opened in excel use
excel options to display multi column view by using used separator.

Emailing Concurrent Program Output

Requirement – It is very common that business request to share concurrent program output file on every run.

Resolution – There are multiple options available to notify the output via email. Some of those are as below -

 - PL/SQL program unit to send the output file

 - Unix commands to send the output file
 - Email bursting concepts, if it is XML publisher report

Each program requires, additional effort/custom programs to achieve the emailing feature. In this post we shall learn
the options already available in system. Which need small configuration change and CP will send the o/p as email.

Steps :-
1) Configuration Change – 2 profile needs to be set.
 FND: SMTP Host >> This to store the smtp server host details
 FND: SMTP Host >> This to store the smtp server port details

2) While submitting the CP, click the Delivery Opts (B)

3) Go to Email tab

4) Provide the To/From email address and Subject line, then press OK (B)
5) Submit/Schedule the program
Email bursting should be better approach, if we are working with XML publisher report.
Program will send the output file to specified email address. Program log file can be referenced in case any issue
occurred.

Enable/Disable Concurrent Program Parameters

Requirement - Business need is to have multiple parameters but end user will be allowed to use one
at a time. selection of any parameter, should disable others.

Solution - Below is one possible option which provides the same behavior in other way.
This requires one additional parameter as Selection/Choice which will decide the enabled parameter.

There will be as many dummy (non displayed) parameters as required parameters in program. actual
parameters will be dependent on respective dummy parameters.

Dummy parameters will be dependent on selection parameters and hold values based on selection.

Example - User requires 2 parameters supplier number and name to be available on one report and
only one should be selected at time. So the requirement was to make other disable. dependent
parameters has limitations and it cannot flow bottom to top. we can achieve this by introducing one
selection parameter, which can hold 2 values -
1) Name – Supplier Name
2) Number – Supplier Number

Initially both Supplier Number and Name range parameter will be disabled.

Selection = Name , will enable supplier Name parameter range.

Selection = Number , will enable supplier Number parameter range.

Implementation – Create 3 dummy parameter (1 displayable and 2 non displayable).

Parame Value Enab Displ

ter Set le ay
Test1 VS0 Y Y
Test2 VS1 Y N
Test3 VS2 Y N

Value Set - VS0, will hold below values

Parameter Test2 and Test3 :

Default Value select decode(:$FLEX$.VS0,'NUMBER','Y',NULL) from dual

Test3 will have similar definition.

Value Set = VS2
Default Value select decode(:$FLEX$.VS0,'NAME','Y',NULL) from dual

Now include the below condition for Number and Supplier Value set definition –
Supplier Number Value set - :$FLEX$.VS1='Y'
Supplier Name Value set - :$FLEX$.VS2='Y'

Add Concurrent Program to Request Group Using Database

 Requirement – Need to register custom concurrent program to request group without having system administrator
responsibility

Solution – We can use below script to add custom program to respective request group

DECLARE
BEGIN
FND_PROGRAM.add_to_group
(
PROGRAM_SHORT_NAME =>'XX_MANAGERS'
,PROGRAM_APPLICATION =>'AR'
,REQUEST_GROUP => 'Receivables All'
,GROUP_APPLICATION =>'AR'
);

COMMIT;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Object already exists');
END ;
/

Run Time Restriction on Concurrent Program Parameter Values

Requirement – Have one program with 2 parameters. 1) Email Options and 2) Invoice Status
Email option can be Yes or No – No will be default value
Invoice Status can have Approved, Cancelled, Needs reapproval and Never approved options.
As per requirement user should be able to pick any of 4 possible Invoice Status, if Email option is No but in case if
Email option is Yes
User should be restricted to select any value other than Approved.

Solution – This can be easily achieved if we create a custom table having invoice status values along with one
additional column to map Email options value.

- Create one custom table using below script

CREATE TABLE O_APPROVAL_STATUS_TAB
(
status_id NUMBER,
inv_approval_status VARCHAR2(50),
description VARCHAR2(50),
email_flag VARCHAR2(5)
)
/
GRANT ALL ON O_APPROVAL_STATUS_TAB TO APPS
/
insert into O_APPROVAL_STATUS_TAB values(10,'APPROVED','Validated','Y')
/
insert into O_APPROVAL_STATUS_TAB values(20,'APPROVED','Validated','N')
/
insert into O_APPROVAL_STATUS_TAB values(30,'CANCELLED','Calcelled','N')
/
insert into O_APPROVAL_STATUS_TAB values(40,'NEEDS REAPPROVAL','Needs Revalidation','N')
/
insert into O_APPROVAL_STATUS_TAB values(50,'NEVER APPROVED','Never Validated','N')
- Create custom value set O_APPROVAL_VS

Go to Edit Information and select table name as O_APPROVAL_STATUS_TAB

- Set Where condition as email_flag=$:FLEX$.Yes_No
- Create one concurrent Program and define 2 parameters
a) Email Option – Yes_No ( Value Set )
b) Invoice Status – O_APPROVAL_VS ( Value Set )

- Assign this program to any request group and validate the results

Output Validation – Run the program and change the Email option values Yes and No.
Concurrent Program Parameter Validation
Requirement – Input should be in specific format for one parameter. Let’s say we have one free text parameter
(email) which must be ended with “@OLearning.com” .
It should not accept if email is not having @OLearning.com at end

How to Achieve -
Create one value set of validation type = Special

Press Edit Information and than Select event = Validate

Write validation logic in Function section

FND PLSQL "DECLARE

lv_str varchar2(100) := :!VALUE;
lv_com varchar2(20);
lv_standard varchar2(100) :='@OLearning.com';
BEGIN
IF (lv_str IS NOT NULL) THEN
Select substr(lv_str,instr(lv_str,'@'
,1),length(lv_str)) INTO
lv_com from dual;
IF lv_com<>lv_standard THEN
fnd_message.set_name('XXNBN','@OLearning is Required');
fnd_message.raise_error;
END IF;
END IF;
END;"
Go to concurrent program definition page
Press parameter button
Create parameter for email address and associate above created value set (OLearning_VS)
 Save the program

Validation –
Go to respective responsibility
Run the concurrent program
Pass the email parameter = sandeep@gmail.com and tab out

Pass the email parameter = sandeep@OLearning.com and tab out

Value for the Flexfield Segment does not Exist in the Value Set

Defining a value set on any DFF will disallow to search records having non eligible (out of value set range) segment
values.

A good example of this is to create a DFF of segment project task name. Initially associate a value set of Table type
which shows all tasks from pa_tasks_all.
We create some records using assigned DFF. After sometime update value set definition to restrict disabled/end
dated tasks.

Now search for records which has been created earlier and having disabled tasks assigned. Oracle form will throw
below error message –

Approach – Try restricting the validation query while search.

Solution – We faced similar issue and did below steps to overcome issue –
1) Created a profile (XXXXX_SANDEEP_PRF) and set value as N
2) Updated the value set query as

Current condition –
WHERE sysdate between start_date and nvl(completion_date,sysdate)

Updated Condition –
WHERE (sysdate between start_date and nvl(completion_date,sysdate))
OR
(nvl(:$PROFILES$.XXXXX_SANDEEP_PRF,'Y')='N')
3) Use form personalization to change the profile value in cache in search and edit mode
4) Set the profile value =Y, when in Edit mode. This will fail the second condition and only first
condition will be valid and so only active task will appear in LOV.
Set the profile value = N when in search mode. First condition will be failed since task is
end dated but profile condition will pass and so record will appear without error.
5) Did not try this in same form. We have created 2 form functions ( Entry (profile Y) and
Enquiry (profile N) mode)

Pivot Function - Transform table rows to colums

PIVOT is very powerful option when data is available in table and requirement is to convert
rows in column and display data for those column. One group function is mandatory to use this
option.

Syntex -
SELECT * FROM (
SELECT ColumnA,ColumnB,ColumnC,ColumnD
FROM TABLE_NAME
WHERE conditions)
PIVOT ( groupFunction(ColumnD) FOR ColumnC in (Values of ColumnC))

Example- assume we have employee expense details for each month. Now requirement is to
generate a report which will display each employee’s monthly expenses in column.

Create Table & Insert Values>>

create table test_sandeep(name varchar2(10),dept varchar2(10),month number,amount number);
insert into test_sandeep values('Sandeep','ABC',1,300)
insert into test_sandeep values('Sandeep','ABC',1,120)
insert into test_sandeep values('Sandeep','ABC',2,400)
insert into test_sandeep values('Sandeep','ABC',3,200)
insert into test_sandeep values('Kumar','DEF',2,600)
insert into test_sandeep values('Kumar','DEF',3,700)
insert into test_sandeep values('Kumar','DEF',3,450)

To obtain monthly basis expense amount, we used to write below group by code -
SELECT name,
dept,
month,
SUM (amount)
 FROM test_sandeep
GROUP BY name, dept, month

Using Pivot, we can obtain same data in below manner

SELECT *
FROM (SELECT name,
dept,
month,
amount
FROM test_sandeep) PIVOT (SUM (amount) FOR month IN (1 "Jan", 2 "Feb", 3 "Mar"))
Table walkthrough - HRMS
This post to make us familiar with basic HRMS tables. Since for each employee user creation is a normal
process, I am mentioning about user details table too.

--User Details
select * from FND_USER where user_name = :name

-- Employee Associated with User

select * from PER_ALL_PEOPLE_F where person_id in (select employee_id from fnd_user where user_na
me = :name)

-- Assignment for Employee/User

select * from PER_ALL_ASSIGNMENTS_F where person_id in (select employee_id from fnd_user wher
e user_name = :name)

-- Position for Employee/User

select a.name,a.* from PER_ALL_POSITIONS a where position_id in (select position_id from per_all_a
ssignments_f whereperson_id in (select employee_id from fnd_user where user_name = :name))

-- Position Hirerachy for Employee/User

SELECT ppse.subordinate_position_id,ppse.parent_position_id,pap.name "Subordinate
Position" FROM
PER_POS_STRUCTURE_ELEMENTS ppse, per_all_positions
pap WHERE ppse.subordinate_position_id=pap.position_id
START WITH
ppse.subordinate_position_id in (select position_id from per_all_assignments_f where person_id in (sele
ctemployee_id from fnd_user where user_name = :name))
CONNECT BY PRIOR ppse.parent_position_id = ppse.subordinate_position_id;

PS – Post intention is to be familiar with HRMS Tables name, below written script can be used by
adding additional filter conditions to filter inactive records.
TRUNC(effective_start_date) <= TRUNC(SYSDATE)
AND TRUNC(effective_end_date) > TRUNC(SYSDATE)
File Handling using PL/SQL - UTL_FILE
Intention of this post is to list out different exceptions being raised while using UTL_FILE.

First create DBA Directory

File Handling Program -

DECLARE
p_file_path VARCHAR2 (200);
p_file_handle UTL_FILE.FILE_TYPE;
p_data_file VARCHAR2 (200);
BEGIN
p_data_file := 'SANDEEP_UTLFILE_TEST.txt';
p_file_path := 'SANDEEP_DIR';

DBMS_OUTPUT.PUT_LINE('File Path >>' ||p_file_path );

DBMS_OUTPUT.PUT_LINE('File Name >>' ||p_data_file );

p_file_handle := UTL_FILE.FOPEN (p_file_path,p_data_file,'W',32767);// OPEN the FILE

/**********************************************************************************
p_file_path >> Location where data file being placed
p_data_file >> Data file name
‘W’ >> Operation Mode
'W' indicates write mode ,
'R' indicates read mode ,
'A' indicates Append mode
32767 >> Length of the string. 32767 is the max length.
**********************************************************************************/
UTL_FILE.PUT_LINE (p_file_handle, 'Sandeep is working on UTL File Demo');
// WRITE into FILE
-- UTL_FILE.FFLUSH(p_file_handle);
-- FFLUSH will write all buffer contents, useful if contents are larger in size.

UTL_FILE.FCLOSE(p_file_handle);// CLOSE the FILE

EXCEPTION

WHEN UTL_FILE.invalid_mode THEN

DBMS_OUTPUT.PUT_LINE('20051, Invalid Option in UTL File >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN UTL_FILE.invalid_path THEN

DBMS_OUTPUT.PUT_LINE('20052, Invalid Path in UTL File >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN UTL_FILE.invalid_filehandle THEN

DBMS_OUTPUT.PUT_LINE('20053, Invalid Filehandle in UTL File >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN UTL_FILE.invalid_operation THEN

DBMS_OUTPUT.PUT_LINE('20054, Invalid Operation in UTL File >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN UTL_FILE.read_error THEN

DBMS_OUTPUT.PUT_LINE('20055, Read Error in UTL File >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN UTL_FILE.write_error THEN

DBMS_OUTPUT.PUT_LINE('20056, Write Error in UTL File >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN UTL_FILE.internal_error THEN

DBMS_OUTPUT.PUT_LINE('20057, Internal Error in UTL File le >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unexpected Error occured during report generation >>' ||SQLERRM );
UTL_FILE.FCLOSE(p_file_handle);

END;

OUTPUT – File Generated

 Defects Manager - Oracle Reports
1) ORA - 24324 : Service handle not initiated ==> SELECT

Resolution - Check the Database connection and re connect. This error is because of Database disconnection.

Upload/Download Workflow (wft) Files

Requirement - Upload wft files to database or download the latest wft file from database

Solution - Workflow files (with extension .wft) are being stored in database and it can be upload (to) or download
(from) database using below 2 methods.
1) Standard Concurrent Program
2) WFLOAD Utility

Standard Concurrent Program – Run the program Workflow Definitions Loader using sysadmin responsibility
(you can add this program to any of responsibility and run from there).
This program has 3 parameters as below
1) Mode – Upload/Download
2) File - /staging/sandeep/Project/PAPROWF.wft
(wft file name with Full Path. Make sure file is available at given path for UPLOAD Mode)
3) Item Type – Item type Name for above file (This can be get from workflow builder. Open your wft file in workflow
builder and create any process. The first attribute will be Item Type Name)
4)

WFLOAD Utility – similar to FNDLOAD utility used for LDT files, oracle has provided WFLOAD for workflow files.
Below are the available options -
To upgrade - WFLOAD apps/pwd 0 Y UPGRADE filename.wft
To upload - WFLOAD apps/pwd 0 Y UPLOAD filename.wft
 To force - WFLOAD apps/pwd 0 Y FORCE filename.wft
To download - WFLOAD apps/pwd 0 Y DOWNLOAD filename.wft ITEMTYPE1
[ITEMTYPE2 ... ITEMTYPEN]
SLA or XLA(AP-GL,RECEIPT-GL,AR-GL)(SubledgerAccounting)
SELECT DISTINCT PS.PERIOD_NAME,
GJL.REFERENCE_2,
TO_CHAR (NULL) PO_ORDER_NUMBER,
TO_CHAR (NULL) "RECEIPT NUMBER",
TO_DATE (NULL) "RECEIPT DATE",
TO_CHAR (NULL) "INVOICE NUMBER",
TO_DATE (NULL) "INVOICE DATE",
TO_CHAR (NULL) "VENDOR NAME",
TO_CHAR (NULL) "VENDOR NUMBER",
JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID,
GSCV.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID,
GSCV.CODE_COMBINATION_ID) SEGMENT2,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR ,
GJL.ENTERED_CR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_DR ))
ACCOUNTED_DR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_CR ))
ACCOUNTED_CR,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJL.CODE_COMBINATION_ID ,
GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
GJL.ATTRIBUTE1 "STAFF NUMBER",
GJL.ATTRIBUTE2 "PROJECT",
GJL.ATTRIBUTE3 "CUSTOMER NUM/NAME" ,
GJL.DESCRIPTION DESCRIPTION
FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH, GL_SUMMARY_COMBINATIONS_V GSCV,
GL_LEDGERS,GL_PERIOD_STATUSES PS,GL_JE_BATCHES B,
GL_LEDGER_RELATIONSHIPS LR
WHERE GSCV.CODE_COMBINATION_ID =GJL.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME = PS.PERIOD_NAME
AND PS.LEDGER_ID = GJL.LEDGER_ID
AND B.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJH.LEDGER_ID = LR.SOURCE_LEDGER_ID
AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND GJH.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES','COST MANAGEMENT','CONSOLIDATION','RECEIVABLES')
AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
--- AND GL_LEDGERS.SUSPENSE_ALLOWED_FLAG = 'P'
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GSCV.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',GJH.CURRENCY_CODE,
'NON INR',DECODE(GJH.CURRENCY_CODE,'INR','###',GJH.CURRENCY_CODE))
UNION ALL

SELECT DISTINCT B.PERIOD_NAME, D.REFERENCE_2, AAA.PO_ORDER_NUMBER,

AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
AAA.TRX_DATE "RECEIPT DATE", AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
AAA.ACCOUNTING_DATE "INVOICE DATE", AAA.THIRD_PARTY_NAME "VENDOR NAME",
AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER", B.JE_SOURCE "SOURCE", B.LEDGER_ID
"SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID)
SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID)
SEGMENT2,
AAA.CURRENCY_CODE, AAA.ENTERED_DR, AAA.ENTERED_CR, AAA.ACCOUNTED_DR,
AAA.ACCOUNTED_CR, AAA.JE_HEADER_ID, AAA.JE_LINE_NUM, AAA.CODE_COMBINATION_ID,
AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER", AAA.GL_DATE "GL_DATE",
AAA.ATTRIBUTE1 "STAFF NUMBER", AAA.ATTRIBUTE2 "PROJECT",
AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME", AAA.DESCRIPTION "DESCRIPTION"
FROM (SELECT DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER, RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
RCT.TRANSACTION_DATE TRX_DATE, API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
POV.VENDOR_NAME THIRD_PARTY_NAME,
POV.SEGMENT1 THIRD_PARTY_NUMBER, RRS.CURRENCY_CODE,
RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR,
RRS.ACCOUNTED_CR, R.JE_HEADER_ID, R.JE_LINE_NUM,
RRS.CODE_COMBINATION_ID, 201 APPLICATION_ID,
TO_DATE (NULL) GL_DATE, TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
TO_CHAR (NULL) DESCRIPTION, TO_CHAR (NULL) ATTRIBUTE1,
TO_CHAR (NULL) ATTRIBUTE2, TO_CHAR (NULL) ATTRIBUTE3
--POH.ORG_ID
FROM PO_HEADERS_ALL POH, PO_LINES_ALL PL, PO_DISTRIBUTIONS_ALL POD
, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT,
RCV_RECEIVING_SUB_LEDGER RRS
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA,AP_INVOICES_ALL API , XLA_AE_HEADERS XAH , XLA_AE_LINES
XAL
,GL_IMPORT_REFERENCES R
,AP_SUPPLIERS POV
WHERE 1=1
AND POH.PO_HEADER_ID=PL.PO_HEADER_ID
AND POH.PO_HEADER_ID=POD.PO_HEADER_ID
AND POH.PO_HEADER_ID= RSL.PO_HEADER_ID(+)
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RCT.SHIPMENT_HEADER_ID(+)
AND POD.PO_DISTRIBUTION_ID= AIDA.PO_DISTRIBUTION_ID(+)
AND API.INVOICE_ID(+)=AIDA.INVOICE_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND AIDA.ACCOUNTING_EVENT_ID=XAH.EVENT_ID(+)
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID(+)
AND XAL.GL_SL_LINK_ID= R.GL_SL_LINK_ID(+)
AND R.REFERENCE_8=XAL.AE_LINE_NUM
AND POH.VENDOR_ID=POV.VENDOR_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE(+)='ACCRUAL'
AND RRS.ACCOUNTING_LINE_TYPE='ACCRUAL'
AND XAL.ACCOUNTING_CLASS_CODE(+)='LIABILITY'
AND POD.DESTINATION_TYPE_CODE='EXPENSE'
AND EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AI
WHERE AID.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID) ) AAA,
GL_JE_HEADERS B, GL_JE_LINES D,GL_JE_BATCHES E, GL_PERIOD_STATUSES PS,
GL_SUMMARY_COMBINATIONS_V CCC
WHERE B.JE_HEADER_ID = AAA.JE_HEADER_ID
AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
AND B.JE_HEADER_ID=D.JE_HEADER_ID
AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND PS.LEDGER_ID = D.LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND B.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING','CONSOLIDATION')
--AND AAA.ORG_ID=:P_ORG
AND ( NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND ( NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (CCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND B.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND B.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',B.CURRENCY_CODE,
'NON INR',DECODE(B.CURRENCY_CODE,'INR','###',B.CURRENCY_CODE))
UNION ALL
--PO-RECEIPT-GL
SELECT GPS.PERIOD_NAME,
L.REFERENCE_2,
PHA.SEGMENT1 PO_ORDER_NUMBER,
RSH.RECEIPT_NUM "RECEIPT NUMBER",
RT.TRANSACTION_DATE "RECEIPT DATE",
NULL "INVOICE NUMBER",
NULL "INVOICE DATE",
PV.VENDOR_NAME "VENDOR NAME",
PV.SEGMENT1 "VENDOR NUMBER",
H.JE_SOURCE "SOURCE",
H.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCC.CONCATENATED_SEGMENTS SEGMENT,
GCC.CONCATENATED_SEGMENTS SEGMENT2,
PHA.CURRENCY_CODE,
-- PLA.LINE_NUM,
-- PLLA.SHIPMENT_NUM,
-- PLA.ITEM_DESCRIPTION,
-- PLA.UNIT_PRICE,
-- PLLA.QUANTITY,
-- PLLA.QUANTITY_RECEIVED,
-- PLLA.QUANTITY_BILLED,
-- RT.TRANSACTION_TYPE,
 --RT.TRANSACTION_DATE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
RRSL.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
TO_NUMBER (NULL) "VOCHER NUMBER",
TO_DATE (NULL) GL_DATE,
TO_CHAR (NULL) "STAFF NUMBER",
TO_CHAR (NULL) "PROJECT",
TO_CHAR (NULL) "CUSTOMER NUM/NAME" ,
--PHA.ORG_ID,
-- TO_CHAR (NULL) DESCRIPTION
RSL.ITEM_DESCRIPTION "DESCRIPTION"
FROM GL.GL_JE_HEADERS H,
GL.GL_JE_LINES L,
GL_CODE_COMBINATIONS_KFV GCC,
GL.GL_IMPORT_REFERENCES GIR,
APPS.XLA_AE_LINES XAL,
APPS.XLA_DISTRIBUTION_LINKS XDL,
PO.RCV_RECEIVING_SUB_LEDGER RRSL,
PO.RCV_TRANSACTIONS RT,
PO.PO_HEADERS_ALL PHA,
PO.PO_LINES_ALL PLA,
PO.PO_LINE_LOCATIONS_ALL PLLA,
APPS.PO_VENDORS PV,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
GL_PERIOD_STATUSES GPS
WHERE 1=1
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ( NVL (GCC.SEGMENT5, 0) >=:P_ACCOUNT_FROM
AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)

AND ( NVL (GCC.SEGMENT1, 0) >=:P_COMPANY_FROM

AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
--AND H.LEDGER_ID = 2041--UTSC
AND H.JE_SOURCE = 'COST MANAGEMENT'
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID =RSL.SHIPMENT_LINE_ID
AND L.PERIOD_NAME = GPS.PERIOD_NAME
AND GPS.LEDGER_ID = L.LEDGER_ID
AND GPS.APPLICATION_ID = 101
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)

--AND H.JE_CATEGORY = 'RECEIVING'

--AND H.STATUS = 'P'
--AND H.PERIOD_NAME IN ('MAY-12')
AND H.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND H.CURRENCY_CODE != 'STAT'
AND H.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',H.CURRENCY_CODE,
'NON INR',DECODE(H.CURRENCY_CODE,'INR','###',H.CURRENCY_CODE))
UNION ALL

SELECT
GPS.PERIOD_NAME,
GJL.REFERENCE_2,
(SELECT DISTINCT PHA.SEGMENT1 FROM AP_INVOICE_LINES_ALL AILLA, PO_HEADERS_ALL PHA
WHERE AILLA.INVOICE_ID=AIA.INVOICE_ID AND AILLA.PO_HEADER_ID=PHA.PO_HEADER_ID)
PO_ORDER_NUMBER,

(SELECT DISTINCT RSH.RECEIPT_NUM

FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT,
AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA
WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL) )
"RECEIPT NUMBER",
(SELECT DISTINCT RT.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT,
AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA
WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL))
"RECEIPT DATE",
AIA.INVOICE_NUM "INVOICE NUMBER",
AIA.GL_DATE "INVOICE DATE",
APS.VENDOR_NAME "VENDOR NAME",
APS.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT2,
AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,
XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,
XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID,
--GPS.APPLICATION_ID,
--
AIA.DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AIA.GL_DATE "GL_DATE"
,NULL "STAFF NUMBER"
 ,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAL.DESCRIPTION "DESCRIPTION"
FROM APPS.AP_INVOICES_ALL AIA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS

WHERE AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1

AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM
EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)

--AND AIA.ORG_ID=:P_ORG
AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)

AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM

AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
 AND AIA.PAYMENT_CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.PAYMENT_CURRENCY_CODE,
'NON
INR',DECODE(AIA.PAYMENT_CURRENCY_CODE,'INR','###',AIA.PAYMENT_CURRENCY_CODE))

UNION ALL
SELECT
GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
NULL "RECEIPT NUMBER",
NULL "RECEIPT DATE",
ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
AIA.CHECK_DATE "INVOICE DATE",
AV.VENDOR_NAME "VENDOR NAME",
AV.SEGMENT1 "VENDOR NUMBER",
GJH.JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
GCCK.CONCATENATED_SEGMENTS SEGMENT,
GCCK.CONCATENATED_SEGMENTS SEGMENT21,
AIA.CURRENCY_CODE CURRENCY_CODE
,AEL.ENTERED_DR,
AEL.ENTERED_CR,
AEL.ACCOUNTED_DR,
AEL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
GCCK.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,AEH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,AEL.DESCRIPTION "DESCRIPTION"
FROM
XLA_AE_HEADERS AEH
,XLA_AE_LINES AEL
,XLA_EVENTS XLE
,XLA_EVENT_TYPES_TL XET
,XLA_TRANSACTION_ENTITIES ENT
,GL_LEDGERS GLG
,GL_PERIODS GLP
,XLA_SUBLEDGERS XLS
,GL_CODE_COMBINATIONS_KFV GCCK
,GL_IMPORT_REFERENCES GIR
,GL_JE_LINES GJL
,GL_JE_HEADERS GJH
,GL_JE_BATCHES GJB
--,AP_INVOICE_DISTRIBUTIONS_ALL AID
-- ,AP_INVOICE_LINES_ALL AILA
,AP_CHECKS_ALL AIA
,GL_PERIOD_STATUSES GPS
,AP_SUPPLIERS AV
--,PA_PROJECT_CUSTOMERS_V PPCV
--,PA_PROJECTS_ALL PPA
WHERE 1=1
AND AEH.LEDGER_ID = GLG.LEDGER_ID
AND AEL.APPLICATION_ID = AEH.APPLICATION_ID
AND AEL.AE_HEADER_ID = AEH.AE_HEADER_ID
AND XLE.APPLICATION_ID = AEH.APPLICATION_ID
 AND XLE.EVENT_ID = AEH.EVENT_ID
AND XET.APPLICATION_ID = XLE.APPLICATION_ID
AND XET.EVENT_TYPE_CODE = XLE.EVENT_TYPE_CODE
AND XET.LANGUAGE = USERENV('LANG')
AND ENT.APPLICATION_ID = AEH.APPLICATION_ID
AND ENT.ENTITY_ID = AEH.ENTITY_ID
AND GLP.PERIOD_NAME = AEH.PERIOD_NAME
AND GLP.PERIOD_SET_NAME = GLG.PERIOD_SET_NAME
AND XLS.APPLICATION_ID = AEH.APPLICATION_ID
AND GCCK.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N' AND
AEH.APPLICATION_ID = 200 AND AEH.BALANCE_TYPE_CODE = 'A' AND (
NVL(AEL.ACCOUNTED_CR,0) <> 0
OR NVL(AEL.ACCOUNTED_DR,0) <> 0 )
AND GIR.GL_SL_LINK_ID(+) = AEL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE(+) = AEL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM(+) = GIR.JE_LINE_NUM
AND GJH.JE_HEADER_ID(+) = GIR.JE_HEADER_ID
AND GJB.JE_BATCH_ID (+) = GIR.JE_BATCH_ID
AND DECODE(GJH.JE_HEADER_ID,NULL,'Y',GJH.JE_FROM_SLA_FLAG) IN ('U', 'Y')
--AND AEL.ACCOUNTING_CLASS_CODE IN ('ITEM
EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
-- AND AIA.DOC_SEQUENCE_VALUE=GIR.SUBLEDGER_DOC_SEQUENCE_VALUE
AND AIA.VENDOR_ID=AV.VENDOR_ID
AND GJH.JE_SOURCE ='PAYABLES'
AND AEH.JE_CATEGORY_NAME='PAYMENTS'
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND AIA.ORG_ID=:P_ORG
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
AND ( NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND ( NVL (GCCK.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND AIA.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',AIA.CURRENCY_CODE,
'NON INR',DECODE(AIA.CURRENCY_CODE,'INR','###',AIA.CURRENCY_CODE))

UNION ALL

--ADDED BY MANOHAR
SELECT GJH.PERIOD_NAME,
NULL REFERENCE_2,
NULL PO_ORDER_NUMBER,
CASE
WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER
ELSE
NULL
END "RECEIPT NUMBER",
CASE
 WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "RECEIPT DATE",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XTE.TRANSACTION_NUMBER ELSE
NULL
END "INVOICE NUMBER",
CASE
WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
XAH.ACCOUNTING_DATE
ELSE
NULL
END "INVOICE DATE",
CASE
WHEN XAL.PARTY_TYPE_CODE = 'S' THEN
(SELECT APS.SEGMENT1
||'|'||APS.VENDOR_NAME
||'|'||HZP.JGZZ_FISCAL_CODE
||'|'||HZP.TAX_REFERENCE
||'|'||HPS.PARTY_SITE_NUMBER
||'|'||HPS.PARTY_SITE_NAME
||'|'||NULL
FROM AP_SUPPLIERS APS
,AP_SUPPLIER_SITES_ALL APSS
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE APS.VENDOR_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = APS.PARTY_ID
AND APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID(+) = APSS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT --HCA.ACCOUNT_NUMBER||'|'||
HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NAME",
 CASE
WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
(SELECT HCA.ACCOUNT_NUMBER
--||'|'|| HZP.PARTY_NAME
--||'|'||HZP.JGZZ_FISCAL_CODE
--||'|'||HZP.TAX_REFERENCE
--||'|'||HPS.PARTY_SITE_NUMBER
--||'|'||HPS.PARTY_SITE_NAME
--||'|'||HZCU.TAX_REFERENCE
FROM HZ_CUST_ACCOUNTS HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
,HZ_CUST_SITE_USES_ALL HZCU
,HZ_PARTIES HZP
,HZ_PARTY_SITES HPS
,XLA_AE_LINES AEL2
WHERE HCA.CUST_ACCOUNT_ID = AEL2.PARTY_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HZCU.SITE_USE_ID(+) = AEL2.PARTY_SITE_ID
AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID(+) = HCAS.PARTY_SITE_ID
AND AEL2.APPLICATION_ID = XAL.APPLICATION_ID
AND AEL2.AE_HEADER_ID = XAL.AE_HEADER_ID
AND AEL2.AE_LINE_NUM = XAL.AE_LINE_NUM )
ELSE
NULL
END "VENDOR NUMBER",
GJH.JE_SOURCE"SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
CC.CONCATENATED_SEGMENTS SEGMENT,
CC.CONCATENATED_SEGMENTS SEGMENT21,
XAL.CURRENCY_CODE CURRENCY_CODE,
XAL.ENTERED_DR,XAL.ENTERED_CR,
XAL.ACCOUNTED_DR,XAL.ACCOUNTED_CR,
GIR.JE_HEADER_ID,
GIR.JE_LINE_NUM,
CC.CODE_COMBINATION_ID
,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
,XAH.ACCOUNTING_DATE "GL_DATE"
,NULL "STAFF NUMBER"
,NULL "PROJECT"
,NULL "CUSTOMER NUM/NAME"
,XAH.DESCRIPTION "DESCRIPTION"

FROM XLA.XLA_TRANSACTION_ENTITIES XTE,

APPS.XLA_EVENTS XEV,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_AE_LINES XAL,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GJL,
APPS.GL_CODE_COMBINATIONS_KFV CC,
--APPS.AP_SUPPLIERS APS,
GL_PERIOD_STATUSES GPS

WHERE 1=1--AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1

AND XEV.ENTITY_ID= XTE.ENTITY_ID
AND XAH.ENTITY_ID= XTE.ENTITY_ID
AND XAH.EVENT_ID= XEV.EVENT_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
--AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
 AND GJH.STATUS = 'P'
AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
--AND AIA.VENDOR_ID=APS.VENDOR_ID
AND GJH.STATUS='P'
AND GJH.ACTUAL_FLAG='A'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
--AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM
EXPENSE','ACCRUAL','LIABILITY','INTRA')
--AND CC.SEGMENT1=111
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.JE_SOURCE ='RECEIVABLES'
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
--AND XAL.ENTERED_DR='136547.55'
AND GJH.PERIOD_NAME = GPS.PERIOD_NAME
--AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)

--AND AIA.ORG_ID=:P_ORG
--AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
AND ( NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
)
-- AND 24116=:P_ACCOUNT_FROM
-- AND 24116=:P_ACCOUNT_TO
AND ( NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)

AND ( NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM

AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
--AND XTE.TRANSACTION_NUMBER='101914 - ZPF TDS APRIL 12'
AND XAL.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',XAL.CURRENCY_CODE,
'NON INR',DECODE(XAL.CURRENCY_CODE,'INR','###',XAL.CURRENCY_CODE));