I wrote my query for which I will create the template. I did not format the date in the query because I will format it on the RTF side.
SELECT PPN.DISPLAY_NAME AS FULLNAME,
PPOS.DATE_START AS START_DATE,
HAPTL.NAME AS POSITION,
HOUFL.NAME AS DEPARTMAN,
PJFL.NAME AS JOB,
PAPF.PERSON_NUMBER AS PERNR
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERSON_NAMES_F PPN,
HR_ALL_POSITIONS_F HAPF,
HR_ALL_POSITIONS_F_TL HAPTL,
PER_JOBS_F PJF,
PER_JOBS_F_TL PJFL,
HR_ORGANIZATION_UNITS HOU,
HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
HR_ORGANIZATION_UNITS_F_TL HOUFL,
(SELECT *
FROM PER_PERIODS_OF_SERVICE Z
WHERE Z.PRIMARY_FLAG = 'Y'
AND Z.PERIOD_OF_SERVICE_ID =
(SELECT MAX (X.PERIOD_OF_SERVICE_ID)
FROM PER_PERIODS_OF_SERVICE X
WHERE X.PRIMARY_FLAG = 'Y' AND Z.PERSON_ID = X.PERSON_ID))
PPOS
WHERE TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PPN.PERSON_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PAAM.POSITION_ID = HAPF.POSITION_ID
AND PAAM.JOB_ID = PJF.JOB_ID
AND PAAM.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.ASSIGNMENT_TYPE IN ('E', 'C')
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE
AND PPN.NAME_TYPE = 'GLOBAL'
AND TRUNC (SYSDATE) BETWEEN PPN.EFFECTIVE_START_DATE
AND PPN.EFFECTIVE_END_DATE
AND PJF.JOB_ID = PJFL.JOB_ID
AND PJFL.LANGUAGE = 'US'
AND HAPF.POSITION_ID = HAPTL.POSITION_ID
AND TRUNC (SYSDATE) BETWEEN HAPF.EFFECTIVE_START_DATE
AND HAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN HAPTL.EFFECTIVE_START_DATE
AND HAPTL.EFFECTIVE_END_DATE
AND HAPTL.LANGUAGE = 'US'
AND HOUCF.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND HOUCF.ORGANIZATION_ID = HOUFL.ORGANIZATION_ID
AND TRUNC (SYSDATE) BETWEEN HOUCF.EFFECTIVE_START_DATE
AND HOUCF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN HOUFL.EFFECTIVE_START_DATE
AND HOUFL.EFFECTIVE_END_DATE
AND HOUFL.LANGUAGE = 'US'
AND HOUCF.CLASSIFICATION_CODE = 'DEPARTMENT'
ORDER BY PAPF.PERSON_NUMBER ASCHere, when I press the Export button under the Data section, it gives me XML.
BI Publisher needs to be installed for Office.
When BI Publisher is successfully installed, you will see a tab like this when you open Word .
Select Sample XML from this tab and upload the XML file we exported here.
(Warning: If you see something like office2003.xml here, try again by installing the 32-bit version of BI Publisher or a different version. )
You should see a text like this when you upload your XML.
After loading the XML, the gray tabs opened. Now I can use these tabs. I call Table Wizard.
I chose table because I want to create a table here.
If you have created more than one data set, you should select a data set here. I am proceeding with my single data set.
In this section, I select the fields I will use in the table by throwing them to the right.
There are data Group options here. Since I will not make any Grouping in my data, I say forward.
In the same way, since I don’t want to rank the efficiency, I say Finish here too.In the same way, since I don’t want to rank the efficiency, I say Finish here too.
This is how my painting arrived.
I add a table wizard in the header, you have to double click on the header.
On the left I write the code that retrieves the system date and on the right I leave an image.
<?xdoxslt:sysdate('DD-MON-YYYY HH24:MI')?>
After saving Word in RTF format, we can look at the output in PDF or HTML format.
After editing the column names, I double click on Start_Date.
Here I say Advanced.
Here you can see that the data is stored in XML format as <?START_DATE?>.
To format the date, we use the format-date syntax.
<?format-date:START_DATE;'DD-MM-YYYY'?>As you can see, it formatted the data.
After saving the RTF in my Word file, I say Create a Report from Oracle Publisher and Finish.
I am naming my report.
Then we upload the template from here.
My report now appears in the Oracle system.
No Comments