[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

[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