PeopleSoft : Differences between App Engine and SQR

App Engine	SQR
Advantages	Disadvantages
Maintained with in PeopleSoft, so Impact analysis is much easier. Changes to objects need not be dealt with every A.E every time.	Lies outside PeopleSoft, so separate Impact analysis is required. Changes need to be done manually in every file.
A.E. is a PeopleTool object designed as an alternative for SQR, COBOL, etc for background processing using SQL and PeopleCode.
Can be – Run from Application Designer. – Called from People Code function. – Running from DOS Environment (Debugging). – Running from Application Engine People Tool. – Running from People soft Application.	Can be – Process Scheduler –Command Line (Can take user input) –Winddoes
Reusability Can use Component Interface Integration Broker Message Catalogs
Ease of coding We can use Meta-SQL in PeoplCode/SQL. These expand to SQL in runtime thus decreasing the effort of coding For e.g. %Join àJoins the given tables using common fields or keys, can exclude list of fields.  Avoids rework even when record structure changes. %EffdtCheck à Creates the sub query for effective date check %Coalesce à Avoids database dependent coding for NVL
State table/AET table (SQL Table or Derived/Work Record) used to store/share Program level variables. Temp Tables to break complicated logic/sql and perform faster.  Since all is done in database the processing is faster. Output doesn't reach permanent tables until program is successful. Avoids deadlocks etc. in case it abends it can be fixed and rerun again.	Have to use variables or arrays to pass values.
Restartability à In case the program abends due to some issue it can be set to restart from that point instead of running from start all over again. AET table must be SQL Table for this. Repeatability à SQLs can be set for reusability thus saving recompile time and running faster.
Different sections can be created for different Markets, or different Platforms (databases)
Debugging- Has built in debugger Trace can be set for various levels of debugging	Must use SHOW or DISPLAY for debugging. No debugger.
Can call other A.E. using Call Section or use Functions in FuncLib or App Packages.	Can call functions in SQCs.
Disadvantages
Programming technique is very restrictive. Cant use SQL and PeopleCode together.  Has its own advantages.
Restricted View Can see one SQL/peopleCode at a time.	Can see full program. Understanding is easy.  Even editor can be of our choice.
Control Structure is fixed	Flow is not restricted.

PeopleSoft : Reserved Variables in SQR

SQR reserves a library of predefined variables for general use.

#current-column : The current column on the page.

$current-date : The current date-time on the local machine when SQR starts running the program.

#end-file : Set to one (1) if end of file occurs when reading a flat file; otherwise, it is set to zero (0).
Your program should check this variable after each READ command.

#page-count : The current page number.

#current-line : The current line on the page.
This value is the physical line on the page, not the line in the report body. See Getting Started with SQR.
Line numbers are referenced in PRINT and other SQR commands used for positioning data on the page.
Optional page headers and footers, defined with BEGIN-HEADING and BEGIN-FOOTING commands,have their own line sequences. Line 2 of the heading is different from line 2 of the report body or footing.

#return-status : Value to be returned to the operating system when SQR exits.
Can be set in your report. #return-status is initialized to the “success” return value for the operating system.

#sql-count : The count of rows affected by a DML statement (INSERT, UPDATE, or DELETE). This is equivalent to ROWCOUNT in Oracle and Sybase.

PeopleSoft : Convert string to date in SQR

let $dt=strtodate('20120320')

strtodate function converts string to date in sqr report.