Re: FW: [sqr-users] help with PeopleSoft SQR containg SQL w. twodynamicdate parameters

[rrodri21@prtcmail.prtc.net]

Tara,

Your first suggestion from e-mail below fixed the problem. I guess I have 
learned to be careful with the Oracle to_date() conversion function in SQL 
embedded in an SQR.

Regards,


-ricardo





"O'Neil, Tara" <tara.oneil@marquette.edu>
01/19/2005 06:52 PM

 
        To:     <rrodri21@prtcmail.prtc.net>
        cc: 
        Subject:        FW: [sqr-users] help with PeopleSoft SQR containg SQL 
w. two  dynamicdate 
parameters


Consider changing:

Begin-Setup
 page-size 60 80
 no-formfeed
End-Setup

To:

Begin-Setup
 page-size 60 80
 no-formfeed
 declare-variable
    date                     $FromDate
    date                     $ToDate
 end-declare
End-Setup

Then replace in your sql to_date($FromDate, 'YYYY-MM-DD') 
with just $FromDate
Do that for all your date references for FromDate and ToDate.


Another thing is in: 
Home > PeopleTools > Process Scheduler Manager > Use > Process
Definitions
Under the Process Definition Options tab
Make sure your *Run Location: says Both



Tara K. O'Neil, Assistant Director-Systems  414-288-5269
Office of Student Financial Aid        414-288-1718(fax)
Marquette University            tara.oneil@marquette.edu
1212 Building, Room 415   http://www.mu.edu/financialaid
P.O. Box 1881
Milwaukee WI 53201-1881


I think this is not a SQR problem, it is a Process Scheduler problem.
Have
you asked a Peopletools discussion group for help?

-----Original Message-----
From: rrodri21@prtcmail.prtc.net [mailto:rrodri21@prtcmail.prtc.net]
Sent: Wednesday, January 19, 2005 1:46 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] help with PeopleSoft SQR containg SQL w. two
dynamicdate parameters


My apologies for posting this message numerous times. I am trying to run

an SQR on Windows NT Process Scheduler in PeopleSoft Financials 8.8
(which 
runs on an Oracle 9i database)

Please refer to the SQR source pasted below as you read this.

I have an SQR Process, VZINTFAC, that performs a simple query and writes

the query results in an output file that the process itself creates in a

specified directory. The SQR Process runs on the NT Process Scheduler
and 
the output directory is specified within the SQR. The SQR Process must
run 
on the Windows NT Process Scheduler because of its association with
other 
processes.

The Query involves obtaining information from Payment and Voucher tables

based on a date range (from a certain date to a certain date). These
date 
parameters are entered in a Run Control Page, and are stored in a Run 
Control Record, VZ_FACSRUN_TBL.

My QAA environment is an exact copy of Production. QAA is refreshed
every 
night with Production. This process executes flawlessly in QAA and 
successfully generates an output file with results from the embedded SQL

query. In Production, on the other hand, the Run Status stays
"Processing" 
forever.

The parameters are accurately passed to the SQR as shown in the results
of 
the Trace File of a Production run below:

***** Starting Init-Report
***** Starting Get-Values
TR96854
FACS1
12012004
12312004
***** Ending Get-Values
***** Ending Init-Report
***** Starting Get-Values
TR96854
FACS1
12012004
12312004
***** Ending Get-Values
***** Starting Open-files
***** Ending Open-files
***** Starting Get-And-Report
2004-12-01
2004-12-31


The Trace output file also contains comments I placed with "Display"
that 
indicate when a Procedure within an SQR starts and ends. As you can see 
from the Trace File Output, Procedure Get-And-Report is started but does

not end. This is the procedure where the embedded SQL is executed.

The procedure Open-files works fine, since a file is created. Nothing is

written to the file in PRD, since the SQR Process hangs at the 
Get-And-Report Procedure.

What is very particular is that in PRD, the SQR Process finishes 
successfully when the date parameters are hard coded. The SQL within the

SQR also runs well when executed in a SQL client usch as Toad or
Benthic. 
I made sure that all tables accessed by the SQL in this SQR process were

properly tuned.

I also performed the following tests on the SQL within the SQR process
in 
Production (please refer to SQL in SQR Process within Get-And-Report 
Procedure):

----------------------------------------------------------------------
FROM PS_PAYMENT_TBL A, PS_PYMNT_VCHR_XREF B, PS_VOUCHER C 
WHERE A.REMIT_VENDOR = 'O11' AND A.FORM_NBR_CONFIRMED = 'Y' 
AND A.PYMNT_METHOD = 'CHK' AND A.PYMNT_STATUS <> 'X'
AND A.PYMNT_DT >= to_date($FromDate, 'YYYY-MM-DD') AND A.PYMNT_DT <= 
to_date($ToDate, 'YYYY-MM-DD') 
AND A.PYMNT_ID = B.PYMNT_ID AND B.VOUCHER_ID = C.VOUCHER_ID

Results: 

1. SQR Process runs forever in Processing Run Status
2. File is generated
3. No data is written to file

----------------------------------------------------------------------
FROM PS_PAYMENT_TBL A, PS_PYMNT_VCHR_XREF B, PS_VOUCHER C 
WHERE A.REMIT_VENDOR = 'O11' AND A.FORM_NBR_CONFIRMED = 'Y' 
AND A.PYMNT_METHOD = 'CHK' AND A.PYMNT_STATUS <> 'X'
AND A.PYMNT_DT >= to_date($FromDate, 'YYYY-MM-DD') 
AND A.PYMNT_ID = B.PYMNT_ID AND B.VOUCHER_ID = C.VOUCHER_ID

Results: 

1. SQR Process finished with Success Run Status
2. File is generated
3. Data is written to file (but obviously with more data than I need)

----------------------------------------------------------------------
FROM PS_PAYMENT_TBL A, PS_PYMNT_VCHR_XREF B, PS_VOUCHER C 
WHERE A.REMIT_VENDOR = 'O11' AND A.FORM_NBR_CONFIRMED = 'Y' 
AND A.PYMNT_METHOD = 'CHK' AND A.PYMNT_STATUS <> 'X'
AND (A.PYMNT_DT BETWEEN to_date($FromDate, 'YYYY-MM-DD') AND 
to_date($ToDate, 'YYYY-MM-DD')) 
AND A.PYMNT_ID = B.PYMNT_ID AND B.VOUCHER_ID = C.VOUCHER_ID

Results: 

1. SQR Process runs forever in Processing Run Status
2. File is generated
3. No data is written to file

----------------------------------------------------------------------
FROM PS_PAYMENT_TBL A, PS_PYMNT_VCHR_XREF B, PS_VOUCHER C 
WHERE A.REMIT_VENDOR = 'O11' AND A.FORM_NBR_CONFIRMED = 'Y' 
AND A.PYMNT_METHOD = 'CHK' AND A.PYMNT_STATUS <> 'X'
AND (A.PYMNT_DT BETWEEN to_date('2004-12-01', 'YYYY-MM-DD') AND 
to_date($ToDate, '2004-12-31')) 
AND A.PYMNT_ID = B.PYMNT_ID AND B.VOUCHER_ID = C.VOUCHER_ID

Results: 

1. SQR Process finished with Success Run Status
2. File is generated
3. The appropriate data is written to the file.


This is a very odd phenomenon. Any help would be greatly appreciated. 
Thanks!


-lounge56


------------------------------------------------------------------------
----
------------------------------------------------------------------------
----
-------------------

Begin-Setup
 page-size 60 80
 no-formfeed
End-Setup

Begin-Program
  DO Initialize
  ! Setup for processing
  DO Init-Report
  ! Open output file
  DO Get-Values
  DO Open-Files
  ! Extract and Write Data
  DO Get-And-Report
  DO Terminate
End-Program

begin-procedure Initialize
  do Init-DateTime
  do Init-Number
  do Define-Prcs-Vars
  do Get-Run-Control-Parms
  do Get-Current-DateTime
end-procedure

begin-procedure Terminate
   if $prcs_process_instance <> ''
     do Update-Prcs-Run-Status
   end-if

  do Reset
end-procedure

!**********************************************************************
!* Procedure Init-Report                                              *
!**********************************************************************
#include 'setenv.sqc'    !Set environment
#include 'setup02.sqc'   !Printer and page-size initialization

Begin-Procedure Init-Report
display '***** Starting Init-Report'
let $PS_HOME = 'E:\peoplesoft\financials\fin88prd\intfc\output\FACS\'

begin-SELECT
A.OPRID                         &OPRID
A.RUN_CNTL_ID   &RUN_CNTL_ID
TO_CHAR(A.FROM_DT, 'MMDDYYYY')  &FROM_DT
TO_CHAR(A.TO_DT, 'MMDDYYYY')            &TO_DT

        do Get-Values

FROM PS_VZ_FACSRUN_TBL A
End-select
display '***** Ending Init-Report'
End-Procedure !Init-Report

!**********************************************************************
Begin-Procedure Get-Values 
!**********************************************************************
display '***** Starting Get-Values'
  move &FROM_DT to $FromDate
  move &TO_DT to $ToDate

  show $prcs_oprid
        show $prcs_run_cntl_id
        show $FromDate
        show $ToDate
display '***** Ending Get-Values'
End-Procedure

!******************************************************************
!* Procedure Open-Files                                           *
!******************************************************************
Begin-Procedure Open-files
display '***** Starting Open-files'

let $DataFilePath = $PS_HOME
let $DataFileName =  'FACST.BSSPP.PSFARC.FACSI928'
let $DataFile = $DataFilePath || $DataFileName

Open $DataFile As 10
 For-Writing
 RECORD=88:FIXED
 STATUS=#Open10
 
display '***** Ending Open-files'
End-Procedure !Open-Files

!*******************************************************************
!* Procedure Get-And-Report                                        *
!*******************************************************************
Begin-Procedure Get-And-Report
display '***** Starting Get-And-Report'
let $FromDate = substr($FromDate,5,4) || '-' || substr($FromDate,1,2) ||

'-' || substr($FromDate,3,2)
let $ToDate = substr($ToDate,5,4) || '-' || substr($ToDate,1,2) || '-'
|| 
substr($ToDate,3,2)
show $FromDate
show $ToDate
Begin-SELECT
A.PYMNT_ID_REF          &PAYMENT_REFID
A.NAME1                                         &NAME
A.PYMNT_AMT                             &AMOUNT
to_char(A.PYMNT_DT, 'YYYYMMDD') &PAYMENT_DT
C.INVOICE_ID                    &INVOICE

        do Write-Row

FROM PS_PAYMENT_TBL A, PS_PYMNT_VCHR_XREF B, PS_VOUCHER C 
WHERE (A.PYMNT_DT >= to_date($FromDate, 'YYYY-MM-DD')
AND A.PYMNT_DT <= to_date($ToDate, 'YYYY-MM-DD'))
AND A.REMIT_VENDOR = 'O11' 
AND A.FORM_NBR_CONFIRMED = 'Y' 
AND A.PYMNT_METHOD = 'CHK' 
AND A.PYMNT_STATUS <> 'X' 
AND A.PYMNT_ID = B.PYMNT_ID 
AND B.VOUCHER_ID = C.VOUCHER_ID
End-Select
display '***** Ending Get-And-Report'
End-Procedure           !Get-And-Report

!*****************************************************************
!* Prodecure Write-Row                                           *
!*****************************************************************
 
Begin-Procedure Write-Row
display '***** Starting Write-Row' 
        Let $TxtFile = ''

 
  move &PAYMENT_REFID to $PAYMENT_REFID
  move &INVOICE to $INVOICE
  move &NAME to $NAME
  move &AMOUNT to $AMOUNT
  move &PAYMENT_DT to $PAYMENT_DT
 
  !grab phone number and customer code from PS Invoice Number
  Let $INVOICE=substr($INVOICE, 3, 10)
 
  !name length
  Let #NAME_LENGTH = length($NAME)
 
  !pad name field
  If #NAME_LENGTH <> 40
        Let $NAME = rpad($NAME, 40, ' ')
  End-If
 
  !amount length
  Let #AMT_L = length($AMOUNT)
 
  !amount decimal position
  Let #AMT_DEC_POS = instr($AMOUNT, '.', 1)
 
  !check amount has no decimal position;
  !add decimal and '00'
  If #AMT_DEC_POS > 0
        !check amount has more than 2 decimal positions; 
        !take away that the excessive positions
        If #AMT_L - #AMT_DEC_POS > 2
                Let $AMOUNT = substr($AMOUNT, 1, #AMT_L - 1)
        End-If
 
        !check amount has no second decimal value; add a 0
        If #AMT_L - #AMT_DEC_POS = 1
                String $AMOUNT '0' By '' Into $AMOUNT
        End-If
  Else
        !amount has no decimal point; add '.00'
        String $AMOUNT '.00' By '' Into $AMOUNT
  End-If
 
  !pad amount field 
  Let $Amount = lpad($AMOUNT, 12, ' ')
 
 !concatenate fields for each row and write to file
 String $PAYMENT_REFID $INVOICE $NAME $AMOUNT $PAYMENT_DT By '' Into $Z

Let $TxtFile = $Z
 
Write 10 From $TxtFile
display '***** Ending Write-Row'
End-Procedure Write-Row

!***********************************************************************
****
*
! SQC's  *
!***********************************************************************
****
*
#Include 'reset.sqc'     !Reset printer procedure
#Include 'curdttim.sqc'  !Get-Current-DateTime procedure
#Include 'datetime.sqc'  !Routines for date and time formatting
#Include 'number.sqc'    !Routines to format numbers
#Include 'prcsapi.sqc'   !Update Process Request API
#Include 'prcsdef.sqc'   !Update Process Request variable declaration

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users