Re: %DateIn and %DateOut problem (?)

["Nguyen, Tom P" <tom.p.nguyen@INTEL.COM>]

Bob,
Thanks for your input.  Maybe I did not state the problem clearly but this
is what I am seeing.
After I've changed App_Status on Application_Data1 panel and tried to save
the record.

APP_STATUS.SavePostChg was triggered.  It then called update_to_hired to
update ps_job_requisition table and you see it was trying to set status_dt =
%datein(&DISPOSITION_DT).   But somehow this was wrongly translated to
status_dt = TO_DATE('11-NOV-98','YYYY-MM-DD').

%datein(&DISPOSITION_DT) should translate to
TO_DATE('1998-11-11','YYYY-MM-DD'.

This is all peoplesoft peoplecode and worked perfectly in our production
environment but not in our development environment.  There's obviously
something screwed up in our development environment  but I don't know what
led to.




Declare Function update_to_hired PeopleCode FUNCLIB_HR.APP_STATUS
FieldFormula;

/* If in the Application Data 1 panel and you have just HIRED a candidate */
If Not RecordDeleted(EMPLID) And
      FieldChanged(APP_STATUS) And
      APP_STATUS = "1" And
      %Panel = "APPLICATION_DATA1" Then
   /* Get req#, jobcode and position data */
   SQLExec("Select JOB_REQUISITION#, JOBCODE, POSITION_NBR, DISPOSITION_DT
from PS_POSN_APPLIEDFOR where EMPLID = :1", EMPLID, &JOB_REQUISITION#,
&JOBCODE, &POSITION_NBR, &DISPOSITION_DT);
   If %SqlRows = 1 Then
      update_to_hired(EMPLID, &DISPOSITION_DT, &JOB_REQUISITION#, &JOBCODE,
&POSITION_NBR);
   End-If;
End-If;


/* Update all tables necessary when an applicant has been HIRED */
Function update_to_hired(&EMPLID, &DISPOSITION_DT, &JOB_REQUISITION#,
&JOBCODE, &POSITION_NBR);
...
...
...
...
/*  Set Job Requisition to FILLED in JOB_REQUISITION record */
   If All(&JOB_REQUISITION#) Then
      SQLExec("Update ps_job_requisition set status_dt = %datein(:1),
close_dt = %datein(:2),job_req_status = 'F' where job_requisition# = :3",
&DISPOSITION_DT, &DISPOSITION_DT, &JOB_REQUISITION#);
   End-If;
End-Function;




        -----Original Message-----
        From:   Bob Buford [SMTP:bbuford@CCCI.ORG]
        Sent:   Friday, November 13, 1998 6:31 AM
        To:     Multiple recipients of list SQR-USERS
        Subject:        Re: %DateIn and %DateOut problem (?)

        Tom,

        In your TO_DATE function, the format (2nd parameter) is supposed to
show the pattern that you are using in the first parameter
            e.g.   TO_DATE('11-NOV-98','DD-MON-YY')
                      or
                     TO_DATE('1998-11-11','YYYY-MM-DD').

        The To_date function just makes your text data into the internal
date format, which you don't have to be concerned with.

        HTH

        Bob

        >>> "Nguyen, Tom P" <tom.p.nguyen@INTEL.COM> 11/12 2:41 PM >>>
        All,
        This happened to us and we still don't know what went wrong in our
        development environment.
        Under Recruit Work Force - Use - Application Data - Application
Data1

        When I tried to change Application Status from Active to Hired of
any
        employee,
        I got this message first:

        SQL error. Stmt #: 0  Error Position: 50  Return: 1858 - ORA-01858:
a
        non-numeric character was found where a numeric was expected

        And then:

        Update ps_job_requisition set status_dt =
TO_DATE('11-NOV-98','YYYY-MM-DD'),
        close_dt = TO_DATE('11-NOV-98','YYYY-MM-DD'),job_req_status = 'F'
where
        job_requisition# = '110638'

        Why Peoplesoft code passed %Datein as '11-NOV-98'  which is contrast
to the
        format 'YYYY-MM-DD'?

        Does anyone have any idea?

        Thanks in advance!

        Tom Nguyen
        Intel Co.