Right Align Field in SQR

How to align right in SQR?

Sometimes the answers are so obvious you kick yourself when you see them:

   Let $total = lpad(To_Char(#total), 15, ' ')

   Print $total (, 40) edit $999,999,999.99 bold

Browser Language Problems

Heard about some problems when browser's default language is not same as Psoft enabled language. Found I couldn't log in when set to Spanish.

Here is the solution
http://docs.google.com/Doc?id=df48r26b_49chqwb3dt

Tricky EFFDT Logic in Query

Sometimes you want to write something like the following in Query
(code snippet for outer join + effdt solution btw):

AND K.PASSWORD (+) = F.PASSWORD
AND
(K.EFFDT IS NULL
OR K.EFFDT =
(SELECT MAX( M.EFFDT)
FROM PS_UQ_CHK_SIG_FILE M
WHERE M.EFFDT <= SYSDATE))

It would appear that Query doesn't let you, but if you do things in the correct order you can force it to do something it was specifically designed to not do. : )

1. Add your subquery
2. Allow the default Effdt Logic to be added by PeopleSoft/Query
3. Add a new expression "max(effdt)" and add as a selected field.
   Note: do not add the alias, just effdt.
4. Correct the Criteria.

Some subqueries don't use effdt logic, the important step is to not add the alias in the expression, until you've finished all the criteria.

Queries in Project and number of Records

select
b.qryname
, b.descr
, b.descrlong
, (select count(*) from PSQRYRECORD where qryname = b.qryname) as Num_Records
from
psprojectitem a
, psqrydefn b
where a.projectname = 'UQXM1210'
and b.qryname = a.objectvalue1
order by 4

App Engine Comments

Would seem that you can make comments in App Engine SQL Actions if:

#1 You use the /* */ commenting standard,
#2 Comments are after the WHERE clause.
#3 Comments do not contain a "%", such as %Table(RecName).

Field Record Definition for Detailed Design

run, copy and paste (work in progess):

DEFINE RECNAME = 'UQ_BNK_RCN_PAYM';
-- FIELD, KEY, SEARCHKEY, LIST, REQ, EDIT, PROMPT TABLE, DEFAULT
SELECT
FIELDNAME AS FIELD
, ' ' AS KEY
, ' ' AS SEARCHKEY
, ' ' AS LISTT
, ' ' AS REQ
, ' ' AS EDIT
, ' ' AS PROMPTTABLE
, ' ' AS DEFAULTT
FROM PSRECFIELD
WHERE RECNAME = &RECNAME
ORDER BY FIELDNUM
;

Security for Purchase Orders, Create & Approve.

Security for Purchase Orders, Create & Approve.

Requestor:
Peopletools > Security > User Profiles
Role: 'xx C PO Officer'
Route Control: (same as the approver)

Set Up Financials/Supply Chain > Common Definitions > User Preferences > Define User Preferences > (Procurement Link)
Operational Unit: (same as the approver)
Requester: 'userA'

> (Purchase Order Authorisations)
Tick everything (if appropriate for environment)
Buyers User Authorised For: 'userA'


Approver:
Peopletools > Security > User Profiles
Role: 'xx W Approver - Fin Lvl 1'
Route Control: (same as the requester)

Set Up Financials/Supply Chain > Common Definitions > User Preferences > Define User Preferences > (Procurement Link)
Operational Unit: (same as the requester)
> (Purchase Order Authorisations)
Tick everything (if appropriate for environment)