[sqr-users] help with PeopleSoft SQR containg SQL w. two dynamicdate parameters

[rrodri21@prtcmail.prtc.net]

Hi! 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 attached to this case 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



Here was a non admissible message part of 'application/octet-stream'
MIME type. It has been automatically discarded before sending the
message to the list. 
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users