Etext template tips and tricks Part 2
Do you know the functions that can be used in etext template? Have you faced issues with getting the sample functions to make them work?
All posts on BI Publisher
How to get Position Hierarchy in SQL Query?
We can use the below query to get the Position Hierarchy of a Position traversing up to the top level. SQL Query: SELECT hap.POSITION_CODE ,hap.position_id ,hap.name ,(SELECT name FROM HR_ALL_POSITIONS WHERE POSITION_ID = pphf.PARENT_POSITION_ID ) Parent_Position_NAME ,decode(hap.ACTIVE_STATUS, 'A', 'Active', 'I', 'Inactive') ACTIVE_Status ,pj.manager_level ,pj.name FROM HR_ALL_POSITIONS hap ,PER_JOBS_F_VL PJ ,PER_POSITION_HIERARCHY_F pphf WHERE hap.POSITION_ID = pphf.POSITION_ID AND hap.job_id = pj.job_id AND…
Skip Output file of HCM Extract when no data is fetched
In this article we will look into how modifying the HCM Extract not to generate output file when data is not fetched by HCM Extract. Normally a blank file will be generated by the HCM Extract when there is no data fetched by the Extract. If you want to know the basics of HCM Extract please Click here Even if there…
Configuring Payroll Process Configuration Groups
In this article we will look into setting up Payroll Process Configuration Groups and SQL Query to extract data. Want to learn Fusion Technical tools? Check out the article We use the Payroll Process Configuration groups while submitting HCM Extracts. These Configuration groups specify the multi threading count, BI output maximum size, GMFZT logging etc. We can navigate to this page…
Etext template tips and tricks Part 1
In this article we will look into how to create etext template. When we have a requirement to generate a fixed length or delimited output files using BI Publisher or HCM Extract we will use the e-text template to achieve this requirement. I have come across a lot of people who are new to etext template and asked me to…
Cancel an existing Work Relationship using HDL for employees
In this article we will look into cancelling an existing Work Relationship of an employee using HDL process. We can prepare the HDL file with the required user keys to delete the work relationship. Sample HDL to delete Work Relationship(Worker.dat): SET PURGE_FUTURE_CHANGES N METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|PersonNumber|DateStart|WorkerType|CancelWorkRelationshipFlag DELETE|WorkRelationship|HRC_SQLLOADER|1122334455|123456789_1|Legal Entity Two||2010/01/01|E|Y When we have a requirement to cancel multiple work relationships due to bad…
Scheduling BIP Report using SOAP Services?
We can run the BI Reports ad-hoc, or schedule them to email/FTP at weekly/daily basis. However we will explore the option of scheduling the BIP Report using SOAP webservices. SOAP WSDL: https://{hostname}/xmlpserver/services/v2/ScheduleService?wsdl Sample: https://abcd-test.fa.us6.oraclecloud.com/xmlpserver/services/v2/ScheduleService?wsdl Once we have the WSDL, we can use the SOAPUI to consume this WSDL and execute the scheduleReport request with the below payload. <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v2="http://xmlns.oracle.com/oxp/service/v2"><soapenv:Header/><soapenv:Body><v2:scheduleReport><v2:scheduleRequest><v2:bookBindingOutputOption>false</v2:bookBindingOutputOption><v2:mergeOutputOption>false</v2:mergeOutputOption><v2:notifyHttpWhenFailed>false</v2:notifyHttpWhenFailed><v2:notifyHttpWhenSkipped>false</v2:notifyHttpWhenSkipped><v2:notifyHttpWhenSuccess>false</v2:notifyHttpWhenSuccess><v2:notifyHttpWhenWarning>false</v2:notifyHttpWhenWarning><v2:notifyWhenFailed>false</v2:notifyWhenFailed><v2:notifyWhenSkipped>false</v2:notifyWhenSkipped><v2:notifyWhenSuccess>false</v2:notifyWhenSuccess><v2:notifyWhenWarning>false</v2:notifyWhenWarning><v2:repeatCount>1</v2:repeatCount><v2:repeatInterval>0</v2:repeatInterval><v2:reportRequest><v2:attributeFormat>csv</v2:attributeFormat><v2:attributeLocale>en_US</v2:attributeLocale><v2:attributeTemplate>xt</v2:attributeTemplate><v2:byPassCache>true</v2:byPassCache><v2:flattenXML>false</v2:flattenXML><v2:parameterNameValues><v2:listOfParamNameValues><v2:item><v2:name>CONTENTID</v2:name><v2:values><v2:item>INPUTCONTENTID</v2:item></v2:values></v2:item></v2:listOfParamNameValues></v2:parameterNameValues><v2:reportAbsolutePath>/Custom/SM/TEST/REPORT.xdo</v2:reportAbsolutePath><v2:sizeOfDataChunkDownload>0</v2:sizeOfDataChunkDownload></v2:reportRequest><v2:saveDataOption>false</v2:saveDataOption><v2:saveOutputOption>true</v2:saveOutputOption><v2:scheduleBurstringOption>true</v2:scheduleBurstringOption><v2:schedulePublicOption>false</v2:schedulePublicOption><v2:startDate/><v2:useUTF8Option>false</v2:useUTF8Option><v2:userJobDesc>SampleJob</v2:userJobDesc><v2:userJobName>SampleJob</v2:userJobName></v2:scheduleRequest><v2:userID>*******</v2:userID><v2:password>*******</v2:password></v2:scheduleReport></soapenv:Body></soapenv:Envelope> You can…
How to get the Payroll Balance details in BI Report?
If the client has implemented Payroll, we have a common requirement to pull the balances in BI Report multiple times as part of third party integrations. Getting the Payroll Balance requires calling the balance package and providing the right inputs. Many of us get it wrong in writing the balances query and find it difficult to achieve it. Here is…
Bursting attachment to Email along with html content in body
Normally when we burst the BI Reports we either do the html bursting so that the content will appear in the email body or burst with an attachment so that the end user can open the pdf and view it. However if there is a requirement to combine both of them, then we can use the below bursting SQL: select…
New features in HCM Extracts in 19B release
We will look into the new features introduced in 19B for HCM Extracts. Oracle has completely revamped the User Interface for HCM Extracts. Below are the features that have been added: We can add multiple Database Items at one go by selecting the DBIs and clicking on Add button. We would get the XML tag as the DBI name. Most…