Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE
Established Member.. cABany
Established Member..
258 views

Trying To Update Request Field With Current Date + (n)

Jump to solution

Hello,

 

I have a date field I want to update passing through an execution step...

 

when setting a date I've used [SYS.ITG_TIME_STAMP]

 

However, I need update a field to [SYS.ITG_TIME_STAMP] + 7 days

 

Based on my failed attempts, it seems like I can't perform an operation on [SYS.ITG_TIME_STAMP].

 

So what can I do?

 

I've tried using a "on field change" rule on the request type

 

select TO_CHAR(TO_DATE(sysdate+7,'DD-MON-YY'),'Month DD, YYYY'), TO_CHAR(TO_DATE(sysdate+7,'DD-MON-YY'),'Month DD, YYYY') from dual

 

This didn't work either.

 

Please Help

0 Likes
1 Solution

Accepted Solutions
Established Member.. cABany
Established Member..

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

After verifying the environment connectivity the following approach was successful.

 

ksc_run_sql QUERY_STRING="SELECT SYSDATE + 7 from dual" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"

 

Note1: You only need to ask for sysdate once.  I thought it was required twice for the parameter and visible parameter that is not the case.

 

Note2: There is no need to format sysdate.

 

ksc_store REQ.P.EXP_RPS_DT = "[SQL_OUTPUT]"

 

 

0 Likes
11 Replies
Absent Member.. favdjiev Absent Member..
Absent Member..

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Hi,

 

Could you please share if you have recieved any errors during the execution? If there is an erroneous behaviour there should be logged errors.

Also, I suspect there is an issue with the query you're using, could you please try this way:

select TO_CHAR(sysdate+7,'Month DD, YYYY'),TO_CHAR(sysdate+7,'Month DD, YYYY') from dual;
 
Finally, could you please doublecheck what is the datatype of the receiving field?
 
This is what I can propose you currently, as I'm afraid implementation questions are outside the scope of the technical support.
 
Best Regards,
Filip
mike_se Contributor.
Contributor.

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Hi,

I found some KCS articles that are similar to what you are trying to do.

 

See if these help you achieve the functionality you are looking for.

 

Looks like you are on the right track with the to_date() function.

 

-Mike

 

 

___________________________________________

 

KM189035

 

How to auto-populate a Request Detail field with the system date to be used for future calculation


Use [SYS.TIME_STAMP] Token, use a Text Field, and use to_date() for computations

The Token [SYS.DATE] resolves to a date value without a timestamp. In order to capture both date and timestamp, you must use the Token [SYS.TIME_STAMP].

Date field is to be used when a user needs update the value through the UI. If you are auto-populating the field using a ksc_store command, then it is recommended that you use a Text Field. If you use a Date field for this purpose, the value may not be displayed properly because of the differences in formats between the value that a Date field accepts and the value [SYS.TIME_STAMP] provides.

Data from all Request Detail fields is captured in the database as VARCHAR data type. Therefore if you want to use a date value from any Request Detail field in further date computations, you must use the to_date() function.

 

______________________________

 

KM201806

 

SELECT
round(to_number((to_date('[REQD.P.DATE2]', 'YYYY-MM-DD HH24:MI:SS')-to_date
('[REQD.P.DATE1]', 'YYYY-MM-DD HH24:MI:SS'))*1440)),
round(to_number((to_date('[REQD.P.DATE2]', 'YYYY-MM-DD HH24:MI:SS')-to_date
('[REQD.P.DATE1]', 'YYYY-MM-DD HH24:MI:SS'))*1440))
from dual

 

_______________________

KM184416

 

Example Configuration:
Create a Workflow Step with the Execution Type as "Workflow Step Commands." In the command steps, enter the following:

      ksc_run_sql QUERY_STRING="select to_Date('[P.FINISH_DATE]','YYYY-MM-DD HH24:MI:SS') - to_Date('[P.START_DATE]','YYYY-MM-DD HH24:MI:SS') from dual" ENV_NAME="[WFS.SOURCE_ENVIRONMENT_NAME]"

 

    ksc_store LEAD_DAYS="[SQL_OUTPUT]"

Note:
All parameters for the ksc_run_sql command above should be written in one line.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Established Member.. cABany
Established Member..

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

First... thank you for the response.

 

I have a field in KCRT_REQ_HEADER_DETAILS that I'm trying to update through the workflow.

 

I want to pass through an execution step that will update this field with the value of "submit date" + 7...

 

the requirement is to calculate an expected response date from the date of submission.

 

I had initially tried this...

 

ksc_store REQ.P.EXP_RPS_DT ="[SYS.ITG_TIME_STAMP]+7","[SYS.ITG_TIME_STAMP]+7"

 

Yes, I know this is all wrong. The concept at the time was trying to use the sysdate because I wasn't sure how to use anything else.

 

So it really needs to be something like this...

 

ksc_store REQ.P.EXP_RPS_DT = REQ.P.SUB_DT + 7, REQ.P.SUB_DT + 7

 

I'm just not sure how to pull this off syntax-wise or if it's even possible with java scripting (in which case I'm sunk)...

 

 

0 Likes
mike_se Contributor.
Contributor.

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Hi,

If you can do it in a query tool, then will be able to do the same in PPM.

Basically, have to use the sql to do the addition of the 7 days (just running it in the PPM's Commands area will not do it).

So I think you have to first use the ksc_run_sql to do the date calculation, then use the ksc_store to store the query result (it's a two step process).

-Mike

_______________________________________
Example of a ksc_run_sql (can just use Oracle's sysdate):
ksc_run_sql QUERY_STRING="select sysdate from sys.dual" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"

Example of adding 7 days (would use something like this in the QUERY_STRING above):
select to_date('11-05-2013 12:00:00','mm-dd-yyyy hh24:mi:ss')+7,to_date('11-05-2013 12:00:00','mm-dd-yyyy hh24:mi:ss')+7 from dual;

Example of ksc_store (would use this to set the Field with the result of the query above):
ksc_store TOKEN=”[SQL_OUTPUT]”

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
mike_se Contributor.
Contributor.

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

And I did a quick test in a query tool, and the "+7" will work with the sysdate as well:

select sysdate+7, sysdate+7 from dual

 

-Mike

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Highlighted
Established Member.. cABany
Established Member..

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Soooo...

 

first command

 

Using just sysdate, and assuming the env_name is correct (never used this before)...

 

ksc_run_sql QUERY_STRING="select sysdate + 7, sysdate +7 from dual;" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"

 

second command

 

REQ.P.EXP_RPS_DT is the token...

 

ksc_store REQ.P.EXP_RPS_DT = "[SQL_OUTPUT]"

 

i don't think I'm using the the right syntax (or ??) for the store command.

 

I think you probably know what I'm gettin with this...

 

"[SQL_OUTPUT]"

 

:  )

 

0 Likes
mike_se Contributor.
Contributor.

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Couple things to check:

1. Make sure to set the Environment for the Workflow Step

I believe it is under the "Edit Source..." right-click option. Basically, have to connect to an Oracle server, so just set it up to connect to the PPM schema. There will be a Source and Destination that you can set the Environment. Set it to use that default KINTANA_HOME. In the Workbench, make sure that the "Check" button shows that the jdbc connection to the database server passes the test.

 

2. Take out the semi-colon, as not seeing this in the examples I found.
ksc_run_sql QUERY_STRING="select sysdate + 7, sysdate +7 from dual"...

3. Just put the name of the Field like so:

ksc_store EXP_RPS_DT =....

The command will automatically put the first value in the Visible and the second one in the Hidden.

 

See if one of these things works. I suspect #1 is probably the issue. Once the Environment is set for the Workflow Step, and the WF Step can make a jdbc connection to an Oracle server, then can use those date commands to do calculations.

 

-Mike

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Jason Nichols K Absent Member.
Absent Member.

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution
I think you are going to need to change your SQL Statement. All Parameter fields in PPM are VARCHAR2 fields as far as the data type goes in the Database. A "Date" field in PPM just means that it has a specific format for how that data is stored so that PPM can dispaly and/or manipulate it as a date. Also, since you are using a Date field, your select statement only needs to have the one SYSDATE + 7 in the query. Also, you will need to convert it to a CHAR format using a specific format for PPM. The SQL Query should look like this:

SELECT to_char(SYSDATE + 7, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL

Now, your ksc_store command should be just like you have it and PPM will automagically put the value into both the hidden and visible parameter columns in the database.
0 Likes
Established Member.. cABany
Established Member..

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

After verifying the environment connectivity the following approach was successful.

 

ksc_run_sql QUERY_STRING="SELECT SYSDATE + 7 from dual" ENV_NAME="[SOURCE_ENV.ENVIRONMENT_NAME]"

 

Note1: You only need to ask for sysdate once.  I thought it was required twice for the parameter and visible parameter that is not the case.

 

Note2: There is no need to format sysdate.

 

ksc_store REQ.P.EXP_RPS_DT = "[SQL_OUTPUT]"

 

 

0 Likes
mike_se Contributor.
Contributor.

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Great, thanks for letting us know. Very valuable for our Support Database.

-Mike

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Established Member.. cABany
Established Member..

Re: Trying To Update Request Field With Current Date + (n)

Jump to solution

Thanks for your help on this!!! I did check the envirionment and we didn't have the jdbc set up... once I tested that and made a few other changes, it worked.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.