This seems like a question for one of the EBS forums.
Yes, its from the EBS forms >> Submitting a Concurrent program request
So, why did you ask in the RDBMS general forum and not one of the EBS forums?
Sorry for that. I have moved this to General EBS discussion
You can't increment a string like that. You can increment a date parameter, however you'll need to modify your concurrent program to use SYSDATE (or a parameter) to solve this requirement.
i have used sysdate only in the file name generating parameter
select CASE WHEN :$FLEX$.ONB_COUNTRIES='US' THEN '/utldata/inbound/ONB/US/US_ONB_'||to_char(TO_CHAR(SYSDATE, 'MMDDYYYY')||'.csv' end from dual;
Used this query for the parameter where i have used the value set>> sql statement
is this not helpful??
The SQL Used in Value set seems to be correct, It will take current date only. I did small change where you used two times to_char, I make it only ones.
What should be the file name if country is other then US?
SELECT CASE WHEN :$FLEX$.ONB_COUNTRIES='US' THEN '/utldata/inbound/ONB/US/US_ONB_' ||TO_CHAR(SYSDATE, 'MMDDYYYY')||'.csv' END FROM dual;
As per value set countries will be displayed as US/IN/JP/CN, When the country is selected, based on the value set i have used the value set name in the sql statement (ONB_COUNTRIES)
if it is US then US_ONB_02202017.csv
if IN then IN_ONB_02202017.csv
SQL is as below
Now i want to schedule this program, and every day file name should change as per sysdate automatically when i schedule
US_ONB_02202017.csv on 20th
US_ONB_02212017.csv on 21st
US_ONB_02222017.csv on 22nd
See my comment above. If you are using a schedule then you can't increment a string like this. You should instead modify your concurrent program to calculate the filename. You can still pass in :$FLEX$.SYNA_ONB_COUNTRIES but you'll need to append the date string on thereafter.
How can we modify concurrent program. This program is SQL * loader which loads csv to staging table.
The above sql qeury is used to calculate the file name based on the valueset value (COuntry)
how can i do increment of date in the filename.
Thanks in advance
Concurrent program will take parameter during you schedule the program so date will not be current date. If possible convert your program to host program and call SQLLDR utility from host program, there you can easiliy check your file name whatever date format you require.
This is already a SQLLDR based concurrent program.
can you suggest how we can convert concurrent program to host program?
and will the date changes automatically if i schedule the concurrent program in that
You could write a wrapper script around it that calculates the date and then calls your request. Something like:
Procedure submit_my_program(errbuf, retcode, country varchar2, dateoverride varchar2) is
filename := country || to_char(nvl(fnd_date.canonical_to_date(dateoverride),sysdate),'formatmodel');
Be very wary of these types of program though - what happens if something goes wrong and it runs a minute past midnight instead of a minute to midnight? The date override I suggested above is so you can deal with that, but it's not ideal.