We created HR integrator and then selected Standalone_Integrator_Queries for assigning parameters which are following.
Integrator Name - CUSTOM TEST People3 INTG
SQL Where Clause – WHERE EFFECTIVE_DATE = '31-DEC-4712' AND EMPLOYEE_NUMBER = $PARAM$.EMPLOYEE_NUMBER
1st Parameter Name – EMPLOYEE_NUMBER
1st Parameter Type – Varchar2
1st Parameter Prompt– EMPLOYEE_NUMBER
Below error while download file for particular employee.
SQL error: ORA-00904: "EFFECTIVE_DATE": invalid identifier occurred processing stored SQL for Content 800:GENERAL_362_CNT.
Download parameters are not complete
Secondly, now if remove effective parameter coditions and reload the same then layout taking twice input for the employee field, is there any table which is configured parameter with integrator
As mentioned error in my reply was due to incorrect column name, now i have apply only one parameter but want it option if pass employee then show specific employee information otherwise will show all, is there any option or way to achieve this.
secondly, i have cofigured function but getting error
Please resolve the following error to continue.
800:GENERAL_363_INTG:layout=800:MY_EMP_DETAIL_1 is an invalid Integrator Key.
Could y pls help me regarding where clause parameter, suppose if user enter employee number then show only that particular employee otherwise show all, anybody have idea, how to set this where conditions.
I have added condition as you mentioned in y reply but still no data be extracted while giving null parameter, i think "NVL" condition is not working or some an other issued. but while in specific employee system is getting data.
you mentioned = where EMPLOYEE_NUMBER = NVL($PARAM$.PER:EMPLOYEE_NUMBER,employee_number)
(SYSTEM IS RAISING ERROR WHEN WE PASS PER: WITH THE PARAMETER, HOWEVER, WITHOUT THIS WORKING FIND BUT ONLY FOR SPECIFIC EMPLOYEE)
MY condition = WHERE (EMPLOYEE_NUMBER =NVL($PARAM$.EMPLOYEE_NUMBER,EMPLOYEE_NUMBER))
I have incorporated both option as you suggested but still there is not getting all employee when pass blank employee number at the time of Parameter.
if this is working try to evolve it into something like:
SQL Where Clause – WHERE (EMPLOYEE_NUMBER = $PARAM$.MY_EMPLOYEE_NUMBER or $PARAM$.MY_EMPLOYEE_NUMBER is null)
SQL Where Clause – WHERE (EMPLOYEE_NUMBER = nvl($PARAM$.MY_EMPLOYEE_NUMBER, EMPLOYEE_NUMBER)
Secondly user ("936671") suggested to use where_clause in such manner but this where system doesn't accespt due to (:PER) SHORT NAME.
should i apply patch for this or need to change some profiles setting.
I have added parameter without adding union in my Main View.
Add sql where like below is working...
In this case system is getting specific employee or all employee as per given value.
and (EMPLOYEE_NUMBER = decode($PARAM$.EMPLOYEE_NUMBER,'ALL',EMPLOYEE_NUMBER,$PARAM$.EMPLOYEE_NUMBER))
Add sql where like below is not working...
and (EMPLOYEE_NUMBER = decode($PARAM$.EMPLOYEE_NUMBER,NULL,EMPLOYEE_NUMBER,$PARAM$.EMPLOYEE_NUMBER))
As you provided solution is bit complicated, because in all reports are consists eithere you pass specific value and leave it blank to show all data.
In my opinion, web adi must take input parameter for each field.