How to evaluate date only in Datetime field in App Engine PeopleCode

Question

just wanted to preface by saying that I am new to PeopleSoft AppEngine and PeopleCode development.

I have the following step in a PeopleSoft App Engine program (OnExecute step) that retrieves records in a table and then takes those existing values and creates new rows with a new item price.

The program is executing a SQLExec statement from the table 'PS_ITM_VNDR_UOM_PR' and using EFFDT as part of the WHERE clause criteria. For EFFDT it is populating the variable value, formatted as 'YYYY-MM-DD'. The problem is that there are some rows in the table PS_ITM_VNDR_UOM_PR that for EFFDT have a date and time: (i.e. 2018-03-19 10:04:03.040), instead of just a date with default blank time: (i.e. 2018-03-16 00:00:00.000).

Here is the another way of looking at the problem, this is the SQL that the program is running, and it's results:

SQL Code / Results using standard EFFDT in WHERE clause (picks up no row(s):

Same SQL Code / Results with EFFDT Date convert logic (picks up a row):

Is there a way to modify the SQL statement in the code, similarly to what I have done in native SQL Server above, or a way I can define a date only program variable that I can use in the SQLExec code?

I have commented in the below code where I believe the line is with the SQL statement in question that would need to be modified.

Local SQL &SQL;
Local string &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, 
&Oprid, &PriceStatus, &VendorId_check, &insert_cols, &insert_select, 
&insert_where, &insert_sql, &Error, &MaxEffdt;
Local date &Effdt;
Local Record &ITM_VNDR_UOM_PR, &REC;

&Oprid = "'SYSUSER'";
&PriceStatus = "2";
&Error = "N";

MessageBox(0, "", 0, 0, "**** BEGINING OF VALIDATION ERRORS ****");

&SQL = CreateSQL("SELECT VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR, 
UNIT_OF_MEASURE, CONVERSION_RATE, PRICE_VNDR, %DateOut(EFFDT) FROM 
PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &VendorId, 
&InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt);

While &SQL.Fetch(&VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, 
&PriceVndr, &Effdt)

   SQLExec("SELECT A.VENDOR_ID FROM PS_ITM_VENDOR A, PS_ITM_VNDR_UOM B WHERE 
A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.VENDOR_SETID = 
B.VENDOR_SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VENDOR_ID = :1 AND 
A.INV_ITEM_ID = :2 AND A.ITM_ID_VNDR = :3 AND A.ITM_VNDR_PRIORITY = 1 AND 
B.UNIT_OF_MEASURE = :4 AND B.CONVERSION_RATE = :5", &VendorId, &InvItemId, 
&ItmIdVndr, &UOM, &ConvRate, &VendorId_check);

   If None(&VendorId_check) Then;
      &Error = "Y";
      MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr 
| " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt);
   Else;
      &CurrentDateTime = %Datetime;
      &Oprid = %OperatorId;
      &RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);    
      SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = 
:1 AND C.VENDOR_ID = :2 AND C.UNIT_OF_MEASURE = :3 AND C.EFFDT = (SELECT 
MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND 
C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND 
C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE 
= C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = 
C1.QTY_MIN)  ", &InvItemId, &VendorId, &UOM, &MaxEffdt);
/* **NEXT SECTION OF CODE IS WHERE THE ISSUE IS OCCURING I BELIEVE** */
SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND 
UNIT_OF_MEASURE = :4 AND EFFDT = :5", &RECITEM, &InvItemId, &VendorId, &UOM, 
&MaxEffdt, &RECITEM);
/* **** END OF ISSUE CODE IN QUESTION **** */
      &new_UNIT_OF_MEASURE = &RECITEM.UNIT_OF_MEASURE.Value;
      &REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
      &REC.SETID.Value = &RECITEM.SETID.Value;
      &REC.INV_ITEM_ID.Value = &RECITEM.INV_ITEM_ID.Value;
      &REC.VENDOR_SETID.Value = &RECITEM.VENDOR_SETID.Value;
      &REC.VENDOR_ID.Value = &RECITEM.VENDOR_ID.Value;
      &REC.VNDR_LOC.Value = &RECITEM.VNDR_LOC.Value;
      &REC.UNIT_OF_MEASURE.Value = &RECITEM.UNIT_OF_MEASURE.Value;
      &REC.CURRENCY_CD.Value = &RECITEM.CURRENCY_CD.Value;
      &REC.QTY_MIN.Value = &RECITEM.QTY_MIN.Value;
      &REC.EFFDT.Value = &Effdt; 
      &REC.EFF_STATUS.Value = &RECITEM.EFF_STATUS.Value;
      &REC.PRICE_VNDR.Value = &PriceVndr;
      &REC.PCT_UNIT_PRC_TOL.Value = &RECITEM.PCT_UNIT_PRC_TOL.Value;
      &REC.PCT_EXT_PRC_TOL.Value = &RECITEM.PCT_EXT_PRC_TOL.Value;
      &REC.USE_STD_TOLERANCES.Value = &RECITEM.USE_STD_TOLERANCES.Value;
      &REC.QTY_RECV_TOL_PCT.Value = &RECITEM.QTY_RECV_TOL_PCT.Value;
      &REC.UNIT_PRC_TOL_L.Value = &RECITEM.UNIT_PRC_TOL_L.Value;
      &REC.PCT_UNIT_PRC_TOL_L.Value = &RECITEM.PCT_UNIT_PRC_TOL_L.Value;
      &REC.EXT_PRC_TOL_L.Value = &RECITEM.EXT_PRC_TOL_L.Value;
      &REC.PCT_EXT_PRC_TOL_L.Value = &RECITEM.PCT_EXT_PRC_TOL_L.Value;
      &REC.BU_PRICE_STATUS.Value = "2";
      &REC.STD_PRICE_STATUS.Value = "2";
      &REC.LEAD_TIME.Value = &RECITEM.LEAD_TIME.Value;
      &REC.OPRID_MODIFIED_BY.Value = &Oprid;
      &REC.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
      &REC.PRICE_CHANGE.Value = &RECITEM.PRICE_CHANGE.Value;
      &REC.Insert();
   End-If;
End-While;
MessageBox(0, "", 0, 0, "**** END OF VALIDATION ERRORS ****");
MessageBox(0, "", 0, 0, " ");

I Appreciate any input you can provide!

score 1 · Accepted Answer · answered Jul 09 '18 at 17:31

1

If this is an Oracle DB you maybe be able to use TRUNC on the EFFDT field.

...UNIT_OF_MEASURE = :4 AND TRUNC(EFFDT) = :5

Not exactly sure what the equivalent would be on SQLServer, or another database. I think that would ignore the time and just join on the date. Although you might then have issues with multiple rows returned, or other duplicate row issues. An EFFDT field should not typically have a time component so there may be a data issue with that environment.

answered Jul 09 '18 at 17:31

otherted

151
3

This is a MS SQL Server database. Can I use `...UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5` – Nick Jul 09 '18 at 18:38
I think you would need it to remain a date data type. Something like this may work if SQLServer 2008... Cast(EFFDT as date). Info from https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 – otherted Jul 10 '18 at 03:45

score 0 · Answer 2 · answered Jul 18 '18 at 13:08

0

Define &MaxEffdt as a date.

You have it defined as a string, but you select a date into it, forcing implicit conversions.

answered Jul 18 '18 at 13:08

Based

950
7
18

your saying if I change the variable &MaxEffdt to be a Local Date, then I wouldn't have to do a SQL conversion? So when SQLExec run the SQL statement, the &MaxEffdt variable (:5) will be populated in the statement as a date only ( as opposed to datetime) ? Thanks again. – Nick Aug 14 '18 at 18:35

How to evaluate date only in Datetime field in App Engine PeopleCode

2 Answers2