Re: %DateIn and %DateOut problem (?)

[Parijat Sahai <parijatsahai@HOTMAIL.COM>]

Hi Tony,

I used the word "using" because of what I noticed in the code he had
attached. This is the code in question:
/*  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;

Now, look at the SQL statement in SQLExec() function. I thought that
this piece of code was custom written by Tom. Maybe not. In taht case,
it might be a bug with "delivered" Peoplesoft, IF THIS IS REALLY THE
PROBLEM.

Parijat.

>
>Parijit,
>   At first glance the problem "seems" simple... But if you consider
the
>FACT that this is "delivered" PeopleCode using their Standard Date
>functions (which are used hundreds OR thousands of times throughout an
>application)... Tom isn't "using" the function - it EXISTS behind the
>Panel he's accessing... Now you can see the problem isn't a "simple"
>TO_DATE function but rather a GLOBAL issue that could be propagating
>throughout the system... Hmmmm... Maybe the %datein system variable can
>be removed everywhere in the system - Can't wait until that next
>upgrade!!!! Sorry- just kidding! TOM - We had a similar problem with
>SQR's after a recent tools upgrade (SQR/SQC changes) in 7.5 Financials
-
>SETUPDB.SQC was changed using a different NLS_DATE_FORMAT and caused a
>few problems... Does this problem occur on every Panel with Date
>Conversions? (i.e. those that utilize %Datein)??? Maybe run an SQL
>and/or a PeopleCode Trace and see if that tells you something... Good
>Luck...
>                                                     -Tony DeLia
>
>
>Parijat Sahai wrote:
>>
>> Hi Tom,
>>
>> To me, the problem seems simple. The &DISPOSITION_DT is already in
>> native database format, so I don't think you need to use %datein() at
>> all. That's why it's value appears to be '11-NOV-98'.
>>
>> Correct me if I'm wrong.
>>
>> Parijat.
>>
>> >
>> >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.
>> >
>>
>> ______________________________________________________
>> Get Your Private, Free Email at http://www.hotmail.com
>
>--
>Tony DeLia
>AnswerThink Consulting Group
>PeopleSoft Solutions Practice - Delphi Partners
>tdelia@erols.com


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com