Introduction
If you have worked in Oracle EBS Applications I am sure you might have faced some tough times figuring out how to pass a Data Type Input Parameter to a Concurrent Program. One might still be able to cope up with this challenge using some format type-casting in the underlying code which accepts the input from the UI makes some format changes and then passes on the required format to the calling package/procedure. But in a Cloud Deployment Model one does not has the liberty to make any changes in the underlying code. Changes if any has to be done on the UI itself ( be it the ESS UI page or the BIP Report UI Page or the Data Model UI Page).
In this article we will try to figure out what should be the ideal format in which a date type input should be passed to call a BI Report.
We would be trying out various scenarios which are:
-
Defining a Date Type Parameter (format MM-DD-YYYY which is the recommended format for passing date type parameter) and not defining any date format type on ESS Job definiton page. At this point I assume you all are aware of how to create an ESS job in Oracle Fusion Application but if thats not the case please do spend some time and read through this post here and hopefully you would be good to proceed.
-
Defining a Date Type Parameter (format MM-DD-YYYY at BI Data Model UI Page) and defining the same date format type on ESS Job Definition page.
-
Defining a Date Type Parameter (format YYYY-MM-DD which is the format in which ESS UI page passes the argument value to the BIP Report..yes the ESS Job definition page provides different date type format selection options but they are all for display purpose.. We will get this covered during our demonstration)
-
Defining a Text Type Parameter (Textbox input) which takes the value as send by the ESS job page
So without much ado lets begin.
Pre-requisite
In order to proceed with this we will define a very simple SQL query which would have a input parameter. The SQL query will fetch the “person number” and the “start date” details from per_all_people_f table.
SQL Query |
SELECT papf.person_number, papf.start_date FROM per_all_people_f papf WHERE :p_effective_date between papf.effective_start_date and papf.effective_end_date |
Once done we will pass a value try to get results and save sample data ( this is required before a BIP Report can be created from SQL Data Model)
Next we will make a Report from this data model and run the same the sample output will appear as below:
We would also create an ESS Job and the job definition page would appear as below:
The section highlighted in yellow explains the following points:
-
Data Type of Parameter is Date or Time
-
Only the Date part is to be shown
-
There is no default date format assigned.
Now we will get started with running the ESS Job (created above). We would refer BI Data Model Date Data Type as BIDATE <format> and ESSDATE<format> for ease of understanding purpose
Scenario 1: BIDATE <MM-DD-YYYY> and ESSDATE <No Format>
In this scenario we will submit the ESS job with current configuration
And if we check the results we could see that there is no output returned. A further deep dive and the XML data shows that the value of date paramter is completely different from the one passed which was passed from UI.
Scenario 2: BIDATE <MM-DD-YYYY> and ESSDATE <MM-DD-YYYY>
Now before runnng this scenario we would have to modify the ESS job definition and set the default date format to MM-dd-yyyy as shown below:
Now we will run the ESS job
And when we check the ESS job output we find that there were no records retrieved from the BIP Report and also the parameter value passed to the BI Data Model was totally different from the one specified on the ESS UI
Scenario 3: BIDATE <YYYY-MM-DD> and ESSDATE <YYYY-MM-DD>
For this scenario we will make two changes. First one is to change the ESS Job parameter default date format to YYYY-MM-DD (as shown below)
The second change is to change the Date Format of the BI Data Model (as shown below)
And next we will submit the ESS job
And on verifying the results we will find that we do get an Output and also the value of input parameter as passed to the BI Data Model is excatly matching with the one passed from ESS UI.
Scenario 3: BI Parmater (Data Type as Text) and ESS Parameter (No Default Date Format)
In this scenario we will make two changes again. The first change is to ensure that there is no default date format for the ESS Job parameter (as shown)
And the second change is to change the data type of BI Data Model parameter to Text
And we would next submit the scheduled process
And we look at the BI Report output we find that it has data.
Comments
Look advanced to far delivered agreeable from you!
However, how could we be in contact? donate for ukraine: https://www.aid4ue.org/about/
courage to go ahead and give you a shout out from Kingwood Texas!
Just wanted to tell you keep up the fantastic job! donate for ukraine: https://www.aid4ue.org/about/
I am glad that you shared this helpful info with
us. Please keep us up to date like this. Thanks for sharing.
wordpress plugins: http://refund.in/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wordpress themes: http://www.kendenney.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
be a great author. I will make sure to bookmark your blog and will
often come back at some point. I want to encourage
you to definitely continue your great posts, have a nice afternoon!
wordpress plugins: http://www.quickandcleantips.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wordpress plugins: http://southcarolinaaviationjournal.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
my breakfast coming again to read additional news.
wp themes: http://www.qfgolfclub.info/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wordpress themes: http://onairsolutions.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
about! Bookmarked. Please also discuss with my website =).
We could have a hyperlink alternate contract between us
wp plugins: http://rave420.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
wp
themes: http://vintageitaliana.com/__media__/js/netsoltrademark.php?d=www.blogexpamder.com
something totally, however this piece of writing provides pleasant understanding even.
wordpress plugins: http://etruckworld.com/__media__/js/netsoltrademark.php?d=www.blogexpander.com
wp themes: http://myhrbi.com/__media__/js/netsoltrademark.php?d=www.blogexpander.com
except it’s one thing that BIL wished as well - nephew is his kid, and supporting them as a household is 100% the goal.
Also visit my web-site :: EB2 business plan, Fran: https://.Ob.Ejam.Esa.Le.Ngjianf.Ei2013@%25252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252528...%25252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252529a.langtonSus.ta.i.n.j.ex.kfen.Gku.an.gx.r.ku.ai8.xn%25252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252520.xn%25252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252520.u.kMeli.S.a.Ri.c.h4223e.xultan.tacoustic.sfat.lettuceerzfault.ybeamdulltnderwearertwe.s.ep.laus.i.bleljhr.eces.si.v.e.x.g.zleanna.langtonWWW.EMEKAOLISAwww.karunakumari46sh.jdus.h.a.i.j.5.8.7.4.8574.85@c.o.nne.c.t.tn.tuGo.o.gle.email.2.%2525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525255C%2525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525252525255Cn1sarahjohnsonw.estbrookbertrew.e.rhu.fe.ng.k.Ua.ngniu.bi..uk41@Www.Zanelesilvia.woodw.o.r.t.hw.anting.parentcrazyre.stfir.stdrowww.mondaymorninginspirationfidelia.commonsHu.Fen.Gk.Uang.Ni.U.B.I.Xn--.U.K.6.2p.a.r.a.ju.mp.e.r.sj.a.s.s.en20.14@Leanna.LangtonYour.Qwe.AqmailSus.Ta.I.N.J.Ex.Kwww.idiving.de/en/node/2265/track,
RSS feed for comments to this post