T E C H N I C A L P A P E R
CONFIGURING AND
OPERATING STREAMED
PROCESSING IN PEOPLESOFT
GLOBAL PAYROLL
Prepared By David Kurtz, Go-Faster Consultancy Ltd.
Technical Paper
Version 1.0
Wednesday 16 November 2011
(E-mail: david.kurtz@go-faster.co.uk, telephone +44-7771-760660)
File: gp.streaming.public.doc, 16 November 2011
Contents
Contents ..................................................................................................................................... 1
Introduction ................................................................................................................................ 3
Caveat .................................................................................................................................... 3
Streaming ................................................................................................................................... 4
Benefits of Streaming ............................................................................................................ 4
Drawbacks of Streaming ........................................................................................................ 5
Read Consistency ............................................................................................................... 5
Physically Separating the Streams ............................................................................................. 6
Avoiding Consistent Reads .................................................................................................... 6
Partitioned Result Tables ....................................................................................................... 7
Range Partitioning ............................................................................................................. 7
Partition Elimination .......................................................................................................... 7
Hash Partitioning ............................................................................................................... 7
Global Temporary Working Storage Tables .......................................................................... 9
Payroll Calculation ............................................................................................................ 9
Implementation Recipie ........................................................................................................... 10
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 1
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Phyiscal Database Changes .................................................................................................. 10
PeopleSoft Configuration Changes ...................................................................................... 10
Physical Database Changes ...................................................................................................... 11
How many streams? ............................................................................................................. 11
Single Server Example ..................................................................................................... 11
Two Server Example........................................................................................................ 11
Create Tablespaces .............................................................................................................. 12
Calculate Stream Boundaries ............................................................................................... 12
Building the Partitioned & Global Temporay Tables .......................................................... 13
Other Database Configuration Issues ................................................................................... 13
Temporary Space Management ....................................................................................... 13
Partitioning & Parallel Query .......................................................................................... 13
Reversing the Changes ..................................................................................................... 14
PeopleSoft Configuration Changes .......................................................................................... 15
Definition of the Streams ..................................................................................................... 15
Newly Hired and Terminated Employees ........................................................................ 15
Specification of Streams .................................................................................................. 15
Process Scheduler Configuration ......................................................................................... 17
Process Type Definitions ................................................................................................. 17
Process Definitions .......................................................................................................... 19
Job Definitions ................................................................................................................. 21
Server Definition.............................................................................................................. 24
Other Configuration Issues .................................................................................................. 25
Calendar Group ID ........................................................................................................... 25
Run Controls .................................................................................................................... 26
Operational Issues .................................................................................................................... 29
Rebalancing the Streams ...................................................................................................... 29
Balancing of CAL_RUN_IDs across Hash Partitions .......................................................... 29
Bugs & Fixes ....................................................................................................................... 31
Streaming fix.................................................................................................................... 31
AE. GPGB_PSLIP ........................................................................................................... 31
Scripts ...................................................................................................................................... 32
Tablespaces for Partitioned Tables (mkpartspc.sql) ............................................................ 32
Calculate Stream Range Values (gpstrmit.sql) .................................................................... 34
Preventing Accidental Stream Boundary Changes (nostrmchg.sql) .................................... 36
Stream Test (strmtest.sql) .................................................................................................... 36
Stream Volume Reports (strmvols.sql) ................................................................................ 37
DDL Build Scripts (gpbuild.sql) .......................................................................................... 40
Sample Output - Partitioned Table................................................................................... 46
Sample Output - Global Temporary Table....................................................................... 51
Process Type Definitions (gen_prcstypedefn.sql) ................................................................ 51
Process Definitions .............................................................................................................. 52
Payroll Calculation .......................................................................................................... 52
Banking and GL processes (gen_prcsdefn2.sql) .............................................................. 58
Job Definitions ..................................................................................................................... 61
Payroll Calculation Job (gen_prcsjobdefn.sql) ................................................................ 61
GL and Banking Jobs (gen_prcsjobdefn2.sql) ................................................................. 63
Server Process Types(gen_serverclass.sql) ......................................................................... 69
Run control builder for Payroll Calculation ......................................................................... 70
Run control builder for Banking process (gppmtprep.sql) ................................................... 73
Run control builder for GL Process (gpglprep.sql).............................................................. 74
Calculate CAL_RUN_ID Suffixes to Balance Volumes of Hash Partitions ........................ 75
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 2 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Introduction
This document has been prepared to explain the changes need and the operation of ‘Streamed’
processing in PeopleSoft Global Payroll (GP).
By splitting each of the major payroll processes into a number of sections that can be run
simultaneously it is possible to reduce overall execution time by utilising additional available
system resources. PeopleSoft refers to each of these sections and concurrent processes as
streams.
It is necessary not simply to configure the streaming option, but also to make certain
configuration changes to PeopleSoft, and some physical changes to the database. These
changes are set out in the Implementation Recipie (see page 10).
Caveat
This is still a draft document. The expression ‘QWERT’ indicates that more work is required
on a particular area
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 3
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Streaming
The Global payroll calculation is a fairly typical financial batch process. Although the
application is complicated, it is an otherwise simple Cobol program. As such, one process can
only run on one CPU at any one time. Therefore, to reduce processing time, and to make full
use of a multi-CPU machine it is necessary to run a number of processes in parallel.
Streaming is the term that PeopleSoft uses to describe the act of breaking the set of employees
for whom Payroll is to be calculated into a number of subsets and processing each subset in a
separately. Each of these processes can then be run in parallel. PeopleSoft, rather
confusingly, uses the term ‘stream’ to refer to both the process and the subset of data being
processed.
The ‘streaming’ facility is a part of the GP product delivered by PeopleSoft. Using it is not a
matter of application customisation, but rather one of configuration.
It is only possible to have a single, global definition for the stream boundaries. If there are
different calendars, either for different companies or different frequencies, then it is not
possible to balance the streams for each frequency. Some sort of compromise must be be
made. This is also why multiple payrolls with the same frequency may as well be processes in
the same run.
At some companies employees and pensioners are paid monthly. They are in different pay
groups, but generally they have still been set up to processes together.
In this paper, most attention will be given to GPPDPRUN, the payroll calculation process, but
the banking and GL process can also be run in streams.
Benefits of Streaming
The Global Payroll calculation engine is a Cobol program. It is capable of fully utilising up to
one processor while it is not waiting for the database. When it waits for the database, either
the Oracle shadow process or the core, database server processes will consume processor time.
Therefore, a single payroll calculation process is incapable of using more than a single CPU.
Most modern Unix servers have several CPUs. I have worked on some that have as many as
20 CPUs of each node of a two-node cluster. The objective has been mimimise the time taken
to calculate payroll, by utilising all available CPUs and by running a number of instances of
the GP engine in parallel. With the physical database changes described below it has been
possible to run a GP calculation with as many as 36 streams in parallel, without significant
inter-stream contention.
The streams are defined as ranges of employee IDs. The decision to use employee ID was
made by PeopleSoft. There is no option for the customer to use a different attribute or a
different method of partitioning.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 4 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Drawbacks of Streaming
That sounds straightforward, but if streaming is enabled on an otherwise vanilla
PeopleSoft/Oracle installation the usual result is that all but one of the streams will fail with
Oracle error 'ORA-1555, Snapshot too old, Rollback Segment Too Small'.
Read Consistency
One of the principles at the heart of Oracle is 'read consistency'. This means that the version
of data that any user sees is consistent throughout the life of a query. When a query reads a
block from the database it checks that it has not changed since the query started. If it has
changed, then the database duplicates the block in memory and recovers the copy back to the
state it was in when the query started. This undo information is obtained from the rollback
segment. If the undo information is not available, then the query will raise the 'Snapshot Too
Old' error. The important thing to remember is that the query fails because somebody else has
changed the data that the failing process has changed.
As updates are made, the undo information is written to the rollback segment. When the end
of the rollback segment is reached, the database goes back to writing data at the start again.
This is called wrapping. Only information in the rollback segment that relates to uncommitted
changes cannot be overwritten. So, if an update was made shortly after the query started, and
was the committed, and then many other updates were made, all while the long running query
was still running, then the undo information may well have been overwritten.
This makes read consistency sound like an expensive problem, but in fact is it one of reasons
to buy Oracle in the first place. The processes that guarentee read consistency are highly
sophisticated, and lie at the very heart of the database kernel. However, performing a
consistent read is an extremely expensive operation and should be avoid where possible.
In the case of Global Payroll the queries are cursors that are kept open during almost the entire
calculation process. Rows are only fetched as required by the calculation. So queries may be
long running because the application takes time to deal with the data that they produce.
The payroll calculationg writes batches of result data to the database, and commits after batch
insert.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 5
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Physically Separating the Streams
When processes that share common resources run concurrently they are likely to contend.
The total execution time of the processes will increase although the total elapsed time will be
reduced. As the number of processes that execute at any one time increases so this effect will
grow.
If it were possible to eliminate all areas of contention between two identical processes then the
overall execution time would be halved, and the processes would be said to have scaled
linearly.
In GP, the same processes execute in each stream but each acts on different data. The
following sections describe how to separate the resources for each stream.
Avoiding Consistent Reads
A consistent read, as described above, occurs when a user starts a long running query, and
somebody else updates the data blocks being queried. However, a long running delete or
update is also a long query to find the rows being updated or deleted. If two sessions are both
running a long running update on the same table at the same time, they can update a
completely distinct set of rows, thus never locking each other out. However, if they update
different rows in the same block then at least one session will have to perform a consistent
read on that block.
If GP payroll streams are run in parallel where the result and working storage tables are not
partitioned, then it is likely that most of the data blocks in these tables will contain rows
required by most of the streams. This guarantees that a significant amount of consistent read
will be take place.
During the cancellation phase the results are deleted from the result tables using monolithic
delete statements. All the data is copied to the rollback segments. The rollback segment must
be large enough to hold all of the data for any one pay period without wrapping otherwise a
payroll stream, probably one that is still running while others have already finished, will fail
with an ORA-1555 error.
So, consistent reads will affect both the performance and stability of the payroll processing.
To avoid consistent read during the GP calculation is it necessary to avoid having rows for
different streams in the same database block. Or, to put the other way around, each database
block must contain only rows relating to a single stream.
So, we require a technique that consistently maps the physical location of data within the
database to the logical data value.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 6 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Partitioned Result Tables
Range Partitioning
Oracle introduced the ability to physically partition a table by a range of data values from
version 8.0. Logically the table remains a single entity. However, physically each partition is
a table. The value of the data inserted into a table determines the physical partition into which
the data is actually placed. So, here we do have a relationship between logical value and
physical location.
If each of the GP result tables were range partitioned on the employee ID in exactly the same
ranges as the processing is partitioned, then each streamed process will update one and only
one partition. That partition would not be updated by another process. This guarantees that
there would be no more than one image in the buffer cache of any block in a partitioned table.
This effectively eliminates consistent read.
The range of employees that define a stream is applied to queries on many tables within GP
processing. Therefore, it is sensible that all tables that are to be partitioned share the same
partition boundary values as the GP streams.
Partition Elimination
Oracle designed partitioning1 for use with what are sometimes called ‘decision support
systems (also referred to as ‘data warehouses’). These involved queries of data from very
large tables.
The usual benefit of partitioning is Oracle’s ability to eliminate whole partitions from a query,
if possible at parse time, and thus reduce the amount of data that is scanned by a query. Most
of the payroll queries will contain the following construction.
WHERE A.EMPLID BETWEEN :1 AND :2
AND A.CAL_RUN_ID=:3 AND
If a partition corresponds to a stream then all the other partitions will be eliminated from this
query, and only the one partition with the required data will be examined. In the case of the
above examine the elimination will not be done at parse time because the values of the bind
variables are not known at that stage. However, when the query is executed, partitions that
could not contain any results will not be scanned.
I have found that partitioning is generally more efficient on larger tables. Hence, the payroll
result tables are the obvious candidates for partitioning. However, experience has shown that
other tables that are only read by the calculation process may also be partitioned to improve
the performance of queries on them.
Hash Partitioning
From Oracle 8.1 it is also possible to partition a table by the hash value of one or more
columns on a row. For each row inserted into the table, the data value of one or more columns
1 The forerunner to partitioning in Oracle was the ‘partitioned view’. Here you took a number
of tables and UNIONed them together in a view. Constraints were added to the tables to
specify the range of the data. Using the constraints, Oracle sometimes able to eliminate tables
from the query that could not possibly return any data. This features is now deprecated by
Oracle.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 7
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
is fed into a hash function, and the result of that function determined the partition into which
the row is placed.
The hash function is sometimes called a pseudo-random function. At first glance the result of
the hash function looks random. However, it is actually a deterministic mathematical
function. That is to say that for the same parameters you will always get the same output.
The idea is that a set of data values will be distributed across a range of hash values. This is
what the Oracle manual says:
Use hash partitioning if your data does not easily lend itself to range partitioning, but you
would like to partition for performance and manageability reasons. Hash partitioning provides
a method of evenly distributing data across a specified number of partitions. Rows are mapped
into partitions based on a hash value of the partitioning key. Creating and using hash partitions
gives you a highly tunable method of data placement, because you can influence availability
and performance by spreading these evenly sized partitions across I/O devices (striping).
From Oracle DBA Guide, Ch17 Managing Partitioned Tables and Indexes
Rows that produce the same hash value will end up in the same partition. Each partition in a
range partitioned table can additionally be hash sub-partitioned. Just as with range
partitioning it is possible to eliminate partitions from a query, but only where a particular
value has been specified. It cannot be used with a range of values, but any range of values
could involve all of the hash partitions.
It has been observed in a number of GP systems that the volume of data grows considerably,
and that as it does the performance of the payroll processes degrade. This is principally due to
the volume of data being scanned in the result tables. The cancellation process is particularly
subject to this. So hash-partitioning has been considered to improve the databases ability to
eliminate data from queries at an earlier stage.
Lets return to the same example from GP. The following predicates are very common in
payroll processing.
WHERE A.EMPLID BETWEEN :1 AND :2
AND A.CAL_RUN_ID=:3 AND
It is clearly not appropriate to hash partition by EMPLID because a range of values are
specified, but a single CAL_RUN_ID (functionally a single calendar or pay period) is
specified. Therefore, I have proposed hash sub-partitioning the largest tables
(GP_RSLT_ACUM and GP_RSLT_PIN) on the column CAL_RUN_ID. In GP processes
that act upon a single pay period a single value for CAL_RUN_ID is specified (as in the
example above). This allows Oracle to eliminate all but one of the hash sub-partitions.
Oracle recommends that the number of hash partitions should be a power of 2. So, 8, 16, 32
etc. not 12, 27, or 42. There will be 12 pay periods per year, so I initially suggest that 16
hash-subpartitions will be created.
The two forms of partitioning can be combined, and both forms of partition elimination can
also work together. So with 6 streams and 16 hash partitions per range, there will be 96
partitions in the largest tables, and the payroll process will eliminate 95 of those 96 partitions.
Ideally as data volumes grow an archiving solution will be introduced. However, if necessary,
before then the number of hash-partitions can be increased to keep the number of periods per
hash down, and so preserve performance. This tactic will not help process that work on
multiple periods, such as end of year reporting. Eventually the question of archiving will have
to be addressed, but this will defer the time when that becomes critical.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 8 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
The balance of data across the hash partitions will not be even, some partitions may not be
used, while some partitions may end up several pay periods. Hence, the performance will also
vary from month to month. If it were possible to control the exact data values that are used to
identify the calendar periods then it would be possible to balance data volumes between hash
partitions.
Global Temporary Working Storage Tables
Payroll Calculation
The payroll process also writes workng storage data to some tables. These can also be a
source of consistent reads, leading to ‘snapshot too old’ errors. The solution is to recreate
these tables as Global Temporary tables (GT). This is a new feature in Oracle 8.1. GT tables
have a permanent definition but temporary content that is private to the session that created it.
Physically, for each session that references a GT table, a copy of the table will be
automatically created in the temporary segment by the database. Thus different GP
calculation streams will reference different segments, and again, like partitioned tables, there
will only be a single copy of the blocks in the rollback segment.
One of their other benefits is that there is a reduction in redo logging, and therefore I/O. The
redo records are not written to the redo log, although the undo records are still written.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 9
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Implementation Recipie
Phyiscal Database Changes
1. How many streams? (see page 11).
2. Create Tablespaces (see page 12) using script to create Tablespaces for Partitioned Tables
(mkpartspc.sql) (see page 32)
3. Make sure that at least paygroups AN001 (employees) and AN002 (pensioners) are both
identified. If necessary run an identify process.
4. Disable trigger on PS_GP_STRM (see Preventing Accidental Stream Boundary Changes
(nostrmchg.sql) on page 36
5. Calculate Stream Range Values (gpstrmit.sql) (see page 34).
6. Reenable or rebuild trigger on PS_GP_STRM (see point 4).
7. Stream Test (strmtest.sql) (see page 36).
8. Stream Volume Reports (strmvols.sql) (see page 37).
9. If satisfied with balance of streams commit update made by gpstrmit.sql, otherwise
rollback, check identification and possibly consider adjustments to gpstrmit.sql.
10. Build the script to build the partitioned and GT tables (see DDL Build Scripts
(gpbuild.sql) on page 40), and then use the resultant script to build the objects.
PeopleSoft Configuration Changes
11. Add addition Process Scheduler Configuration (see page 17) in order to be able to PSJobs
to run all streams.
11.1. Process Type Definitions (see page 17).
11.2. Process Definitions (see page 19).
11.3. Job Definitions (see page 21).
11.4. Server Definition (see page 24).
12. Run controls should be created for each of the processes (see Run control builder for
Payroll Calculatio on page 70).
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 10 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Physical Database Changes
How many streams?
The first question is to decide how many streams. This is usually determined by the number
of processors on the production application/database server(s). The purpose of parallel
processing is to bring more resources to bear upon a problem in order to complete the work in
a shorter elapsed time. The number of streams should be as many as are required to fully
consume all the CPUs, or all that you are allowed to use!
If the calculation is run during the working day, as is sometimes a payroll operational
requirement, this will certainly degrade the performance of the on-line system. I would
recommend running the batch environment with a lower operating system priority by using
the Unix ‘nice’ command2. This can either be incorporated via a shell script, or by Tuxedo if
the process scheduler is running under Tuxedo.
In a well-tuned GP system I would expect that the Cobol process would consume 2/3 of the
processing time and the SQL would be the other third. If you are not achieving this ratio, then
there might be some scope for SQL performance tuning.
Single Server Example
If the Cobol and the database are co-resident, then either the Cobol will be active, or the
database will be active, although the database might be waiting for the disk sub-system. Thus
1 stream should full consume most of one CPU. Therefore, I would suggest that the number
of streams should be equal to the number of CPUs.
If more streams than this are run then the effect might be to starve the database of CPU, an
this might have undesirable effects.
Two Server Example
Consider the situation where two servers are in use, with the database on one, and the Cobol
running on the other. Both of the servers have 4 identical CPUs.
Assuming that the Cobol is active for 2/3 of the time, then with 6 streams, on average, 4 of the
COBOL processes should be active, rather than waiting on the database, and this should
consume 100% of all 4 CPUs on the application server while consuming approximately 2
CPUs on the database server. The limiting factor is therefore the application server, and I
would recommend using 6 streams.
2 The equivalent to this on Windows is ‘start … /below normal’. However, I have never
attempted to incorporate this into the process scheduler.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 11
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Create Tablespaces
A pair of tablespaces should be created for each stream, one for data, one for indexes (see
Tablespaces for Partitioned Tables (mkpartspc.sql) on page 32). This is principally to permits
a finer degree of measurement and monitoring. However, it also provides the flexibility to
place different tablespace, and hence streams, on different physical devices.
The naming convention for these tablespaces is GPPARTTAB01, GPPARTIDX01,
GPPARTTAB02, GPPARTIDX02 etc. This convention must be followed because the
gpbuild.sql that generates the DDL to build the partitioned tables will explicitly reference
these tablespaces.
The only merit in separating indexes from tables is that these could then be placed on different
physical disks, and if only one stream were running, the I/O would be distributed.
Calculate Stream Boundaries
Next, calculate the stream boundaries (see Calculate Stream Range Values (gpstrmit.sql) on
page 34).
The execution time of the payroll is the time when the first stream starts, to the end of the last
stream. The idea is to have all the streams take roughly the same amount of time. Therefore,
the streams should have roughly the same amount of work to do. The amount of work is
roughly proportional to number of segments that are to be processed. The gpstrmit.sql script
performs this calculation. It also makes an allowance for an addition 1% rows that will be
added to the last stream as new employees are hired. Hence the last stream is slightly smaller.
The most recently identified payroll is used for this calculation. The identification process
populates the table PS_GP_PYE_SEG_STAT. There is one row for each calculation type
(absence, pay etc) for each segment for each segment for each employee to be paid. Thus the
number of rows provides a good approximation of the amount of work to be done.
The script gpstrmit.sql (see page 34) is used to calculate the stream ranges. It notionally
breaks the table GP_PYE_SEG_STAT into as many equal sized pieces, allowing for some
extra rows added to the end of the last piece, and writes the values directly into
PS_GP_STRM, which is the table that specifies the streams in GP. The update made by this
script is not committed. Two further scripts have been provided to check that the results of the
stream boundary calculation is reasonable. If the results are satisfactory the update can be
committed manually.
Stream Test (strmtest.sql) (see page 36) checks that every employee is a member of a
stream.
Stream Volume Reports (strmvols.sql) (see page 37) reports of the data volumes in
each stream of employees, payees, segments by calendar ID, paygroup, and retro
period.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 12 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Building the Partitioned & Global Temporay Tables
The PeopleSoft Application Designer is unable to create the DDL to create either partitioned
or global temporary tables. It is unlikely that PeopleSoft will ever introduce this because both
require Oracle specific syntax. Other database platforms have no log objects, but they are
implemented differently. Other databases support partitioning, but the syntax will vary
widely.
It is possible to coax the Application Designer into generating the DDL to build a Global
Tempoary table, it requires rather convoluted changes to the DDL model.
Therefore, a utility has been developed to generate the DDL to build these types of tables and
their indexes (see DDL Build Scripts (gpbuild.sql) on page 40). This simply replaces the
object build facility in Application Designer. The stream boundaries must be calculated
before this script is run, because the literal values for the boundaries are included in the DDL
script that is generated by this script.
There are three DDL scripts generated by gpbuild.sql.
gpbuild_.sql: This is similar in structure to the alter script built by
Application Desinger. The existing tables are renamed, new tables are built,
populated, renamed and indexed, and then original tables are dropped, The script
should be run with SQL*Plus. It contains pauses so that the operator can determine
that there have been no errors before dropping the original tables. This is the script
that will be used most often when installing streaming or after rebalancing the
streams.
gpindex_.sql: This simply drops and recreates the indexes in
place. This is useful to when the keys on a table have been adjusted.
gpstats_.sql: This script can be used to regenerate the statistics for
Oracle’s cost based optimiser, It can be given to the DBA to be incorporated into the
process that regenerates statistics.
Other Database Configuration Issues
Temporary Space Management
Changing a table from permanent to global temporary will remove it physically from its
tablespace thus saving space, but it may be necessary to increase the size of the temporary
tablespace.
The situation is similar for indexes. An index on a GT table is a global temporary index and
only exists in the temporary segment and then only when the table exists.
Partitioning & Parallel Query
There will be queries, especially end-of-year reporting queries, that will scan some of all of
the partitions in the result tables, and not just one. Typically, these will not query data by
EMPLID. These queries will not eliminate many, or sometimes even any partitions, and will
perform concatenated partition scans, which will work through some or all of the partitions
and repeat the same scan on each.
Partitioning is a special form of parallelism, and can cause the parallel query functionality to
be invoked, in which case a parallel query slave will be allocated to each partition. This
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 13
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
approach allows the database to use extra CPU to execute the same query in less elapsed time.
It is fine for a data warehouse were there are few user sessions. It is not suitable for an OLTP
system because it is highly likely that queries will have to wait for a parallel query slave to
come free. Therefore, parallel query should be disabled by setting the following initialisation
parameters.
PARALLEL_MAX_SERVERS=0
Reversing the Changes
Even with the physical changes described, it is still possible to run the entire payroll in a
single stream. The physical changes do not imply any logical change.
The gpbuild.sql script that builds these objects also creates an Application Designer project
GFC_GPBUILD, which contains all the objects that have been physically changes.
The purpose of the project is two-fold:
It provides a list of tables that are created by the script, and so must not be built be
Application Designer.
The project can be used to build a script to rebuild the tables as ordinary tables, so
reversing the physical changes described in this document. It may also be necessary
to rebuild the global temporary tables as normal tables in order to assist debugging.
It is not possible to see what another session has put in a GT table.
The original run controls and process definitions will continue to work alongside the new ones
described in this document.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 14 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
PeopleSoft Configuration Changes
This section discusses some of the configuration that is required within PeopleSoft.
Definition of the Streams
Newly Hired and Terminated Employees
As new employees are hired, and new employee I.D.s are allocated, so they will have to be
processed by payroll. Similarly, as employees are terminated they will be dropped from the
payroll. Over time this will cause an imbalance in the execution time of the streams.
Eventually, it will be necessary to rebalance the streams by recalculating the boundaries and
rebuilding the partitioned objects.
Specification of Streams
The values for the stream ranges can be defined by a page in the GP Rules step up.
This page is no more than a view of the database table PS_GP_STRM.
STRM_NUM EMPLID_FROM EMPLID_TO
-------- ----------- -----------
1 0 10000505Z
2 10000506 10363691Z
3 10378667 10378668Z
4 10628518 10816472Z
5 10816473 10938454Z
6 10938455 ZZZZZZZZZZZ
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 15
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
However, I prefer to populate this table with the script gpstrmit.sql (see page 34). It calculates
stream boundary values. The above page can be used to view the results.
It might be advisable to prevent accidental updates to this page by
i. Making the page read only
ii. Adding a trigger to PS_GP_STREAM that will raise an error when the table is
updated.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 16 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Process Scheduler Configuration
The next stage is to configure the process scheduler to run the streamed payroll within a job
on the process scheduler.
Process Type Definitions
I have created new process types that are clones of the default COBOL SQL and Application
Engine process type definitions, one for each stream. The reason for this is so that should
there be two streamed payrolls, only one of them can run in the same stream at the same time.
The occurance level on each stream in the process scheduler will be 1. This will also, for
example, prevent a cancel being run at the same time as a calculated.
The command line on the new type definitions has been changed from that in COBOL SQL.
The run control is fixed as 'GPStreamn' where n is the stream number.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 17
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
The only way to relate a particular stream to a particular process or process type is via the run
control. The stream number is on the run control, and the run control ID is embedded into the
command line in the process type definition.
So that the entire payroll can be run from a process scheduler JOB, the name of the run control
has been hard coded into the process type definition. GP Cobol SQL Stream 1 can only run
runcontrol GPStream1, etc.
N.B. The Run control must be a single string, and may not contain embedded spaces.
The new type definitions can also be created with a script (see Process Type Definitions
(gen_prcstypedefn.sql) on page 51).
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 18 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Process Definitions
A new process type has been created for each of the process type definitions 3.
The new processes are defined as high priority. They will be scheduled by the process
sheduler in preference to lower priority jobs.
3 In UK there is also a process definition for the Application Engine process GPGB_PSLIP.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 19
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
These process defnitions can be set up by script (see Process Definitions on page 52).
They have the same security set up as the original COBOL SQL and Application Engine
process definitions.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 20 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Job Definitions
A job has been created to run the streams in parallel 4.
4 A problem has been observed with using the job to initiate streamed payroll. If the last
stream finishes while any other stream is still running, then the status on the job erroneously
goes to 'Not Successful'. This would appear to be a fault in process scheduler. In the example
there are only 6 streams, so it may be easier not to use jobs and have the users check all the
checkboxes in the process scheduler screen. However, in situations where there are many
streams (I have worked with 30 in one case) this may not be acceptable.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 21
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
The security definition for the job is also copied from the process definitions.
A process scheduler job has been set up for each process.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 22 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
The jobs can be created by script (see page 61).
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 23
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Server Definition
NB: The new process types have been added to the process scheduler server definiton. They
have an occurance level of 1. This prevents more than one payroll being run in any one
stream at the same time.
The maximum number of API aware jobs has also been set to 6. This will prevent any other
jobs from running while all the payroll processes are running. If any other jobs are running
when all the payroll processes are scheduled, one stream will wait per running process. The
payroll processes have been given high priority. They will be run before previously scheduled
processes of a lower priority.
The new process types can be added to the server by script (see page 69).
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 24 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Other Configuration Issues
Calendar Group ID
The calendar group must be set up manually though the panel. The point to notice here is that
3 employee calendars and one pensioners calendar are all set up within the same calendar
group that will all be processed together.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 25
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Run Controls
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 26 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
A quirk of the above panel is that a processing option must be specified before the stream
number can be entered. This is not immediately obvious in the PIA because the stream
number filed does not grey as it does in the windows client.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 27
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Each stream within the job is reported separately in the process monitor if the 'View Job Items'
box is checked.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 28 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Operational Issues
Rebalancing the S treams
In 'Definition of the Streams' (see page 15) I discussed how the streams should be defined so
that they have roughly equal numbers of employees to process. It is also known that as new
employees are hired, and existing employees are terminated the streams will go out of balance.
That is to say that they will take different amounts of time because they have different amount
of work to do. This becomes a problem because the processing time of payroll is really the
processing time of the longest stream. When this becomes a problem will depend on the rates
or hire and termination of employees.
However, the solution is simple. It will be necessary to recalculate the stream boundaries and
rebuild the partitioned tables with the same number of partitions, but with new partition
boundaries. There will be no operational change as a result of this.
The number of streams is mainly determined by the number of CPUs available to the Cobol
GP calculation processes. If the hardware configuration changes then it might well be
appropriate to change the number of streams. In which case the stream boundaries should be
recalculated, and the partitioned tables should be rebuilt accordingly.
Balancing of CAL_RUN_IDs across Hash Partitions
The largest tables in GP have been hash subpartitioned by the calendar ID, CAL_RUN_ID.
So the physical partition in which each row resides is determined by logical value of the
CAL_RUN_ID on that row. The column value is passed through a hash function that is also
accessible from the PL/SQL get_hash_value() function.
It is probably easiest to explain this with the following worked example. Lets suppose that
there are monthly and weekly payrolls. The monthly calendar IDs will be of the form
GFC2003M12 (meaning ‘month twelve in year 2003’) and weekly ones will be of the for
GFC2003W52 (meaning ‘week 52 in year 2003’).
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 29
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
PROCDATE CAL_RUN_ID5 WEIGHT6 HASHVALUE7 CUMWEIGHT CAL_RUN_IDX8
HASHVALUEX9CUMWEIGHTX10
---------- --------------- ---------- ---------- ---------- -------------------- ---------- --------
07/04/2003 GFC2003W01 2000 9 200011
14/04/2003 GFC2003W02 2000 1 2000
21/04/2003 GFC2003W03 2000 2 2000
28/04/2003 GFC2003W04 2000 6 2000
05/05/2003 GFC2003W05 2000 2 4000
12/05/2003 GFC2003W06 2000 12 2000
16/05/2003 GFC2003M02 10000 12 12000
19/05/2003 GFC2003W07 2000 5 2000
26/05/2003 GFC2003W08 2000 16 2000
02/06/2003 GFC2003W09 2000 5 4000
09/06/2003 GFC2003W10 2000 6 4000
13/06/2003 GFC2003M03 10000 15 10000
16/06/2003 GFC2003W11 2000 13 2000
23/06/2003 GFC2003W12 2000 16 4000
30/06/2003 GFC2003W13 2000 6 6000
07/07/2003 GFC2003W14C 2000 14 2000 GFC2003W14 13 4000 12
14/07/2003 GFC2003M04 10000 11 10000 GFC2003M04 11 12000
14/07/2003 GFC2003W15C 2000 8 2000 GFC2003W15 11 12000
21/07/2003 GFC2003W16A 2000 7 2000 GFC2003W16 9 4000
28/07/2003 GFC2003W17B 2000 3 2000 GFC2003W17 5 6000
04/08/2003 GFC2003W18H 2000 10 2000 GFC2003W18 3 2000
11/08/2003 GFC2003W19B 2000 4 2000 GFC2003W19 8 2000
13/08/2003 GFC2003M05B 10000 3 12000 GFC2003M05 5 16000
18/08/2003 GFC2003W20A 2000 1 4000 GFC2003W20 2 6000
25/08/2003 GFC2003W21 2000 13 4000 GFC2003W21 13 6000
01/09/2003 GFC2003W22 2000 14 4000 GFC2003W22 14 2000
08/09/2003 GFC2003W23 2000 7 4000 GFC2003W23 7 2000
13/09/2003 GFC2003M06D 10000 8 12000 GFC2003M06 14 12000
15/09/2003 GFC2003W24F 2000 4 4000 GFC2003W24 1 4000
22/09/2003 GFC2003W25B 2000 9 4000 GFC2003W25 16 6000
29/09/2003 GFC2003W26B 2000 7 6000 GFC2003W26 3 4000
5 CAL_RUN_ID is the Calendar Group ID which may change.
6 WEIGHT is the total number of employee months that exist in the hash partition to date
including the current month.
7 HASHVALUE is the hash value of CAL_RUN_ID.
8 CAL_RUN_IDX is the old CAL_RUN_ID before the suffix was added.
9 HASHVALUEX is the hash value of the old CAL_RUN_ID
10 CUMWEIGHTX is the total number of employees for all the months in the old partition.
11 We will assume that this is a UK payroll where the tax year starts on 6 th April.
12 Week 13 would previously gone into hash 13, but week 11 is is already in there, and there
are still empty partitions. Adding suffix C moves the data to hash partition 14 which was
previously empty.
The suffix is chosen such that the data is placed in the hash partition with the least data.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 30 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
The output from this function appears to be random. Notice that the hash values 11 and 12
each appear twice, hash 13 three times, and hash 6 four times in the first five months of
operation! This would mean that these hash partition would have more data in them and the
SQL would be affected accordingly. So the hash values are not distributed evenly for small
volumes of data.
If it were possible to change the value of the CAL_RUN_ID, I could determine the physical
partition into which the data will go. There is no functional reason against this. The calendar
ID is just a name that is chosen by the user. Therefore, by adding a suffix character, where
necessary I can force the data into a particular partition.
Suppose that I have 10000 monthly and 2000 weekly paid employees, and suppose that
introduce my payroll in 2003. Lets also suppose that this change will be implemented from 1 st
July when months 1 to 3, and weeks 1 to 13 have already been processed when I decide to
adjust the calendar IDs. Calendars that have already been processed will not be changed.
The script to calculate the suffixes, based on this example is in the appendix (see page 75). It
will be necessary to adjust the script in order to tailor it to each specific configuration.
Bugs & Fixes
Streaming fix
PeopleSoft patch R-ETENNA-3T8TN is a prerequisite for streaming. This patch is rolled up
in service pack 1.
AE.GPGB_PSLIP
This application engine process generates the payslips in the UK GP extentions. Although it
appears to be capable of being run in streams, in fact it cannot.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 31
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
APPENDIX
Scripts
The scripts reproduced in the appendix are supplied with this document. All of these scripts
are intended to run in SQL*Plus.
Tablespaces for Partitioned Tables ( mkpartspc.sql )
A pair of new tablespace is created for each partition. Each tablespace only contains objects
for a particular stream. The tablespaces are created as locally managed tablespace with
uniform extent size, therefore there are no storage clauses on the create statements for the
partitioned objects.
There is no demonstrable link between the number of extents in a table and the performance of
DML13 on that table, so there should be no problem with some segments having a large
number of extenets.
spool mkpartspc
create tablespace gptabpart01
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gptabpart01_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create tablespace gptabpart02
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gptabpart02_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create tablespace gptabpart03
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gptabpart03_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create tablespace gptabpart04
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gptabpart04_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create tablespace gptabpart05
13 Data Modification Language: SELECT, INSERT, UPDATE, DELETE SQL statements.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 32 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gptabpart05_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create tablespace gptabpart06
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gptabpart06_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create idxlespace gpidxpart01
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gpidxpart01_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create idxlespace gpidxpart02
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gpidxpart02_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create idxlespace gpidxpart03
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gpidxpart03_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create idxlespace gpidxpart04
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gpidxpart04_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m;
create idxlespace gpidxpart05
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gpidxpart05_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m
;
create idxlespace gpidxpart06
datafile '/sx059a/hr0001/oradata/HRPROD/HRPROD_gpidxpart06_1.dbf'
size 2001m autoextend off
extent management local uniform size 4m
;
spool off
mkpartspc.sql
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 33
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Calculate Stream Range Values ( gpstrmit.sql )
This script calculates the stream boundary values.
set pause off
column ADJ new_value ADJ heading 'Adjustment'
column EMP heading 'Total|Employees'
column RPE heading 'Rows per|Employee'
spool gpstrmit
Select min(emplid)
, max(emplid)
, count(*)
from ps_gp_pye_seg_stat;
select cal_run_id
, count(distinct emplid) EMP
, count(*) num_rows
, count(*)/count(distinct emplid) RPE
, count(*)*.01 ADJ 14
from ps_gp_pye_seg_stat
WHERE cal_run_id = (
SELECT MAX(cal_run_id) max_cal_run_id
FROM ps_gp_pye_seg_stat
WHERE cal_run_id like 'AN%'
)
group by cal_run_id
;
rollback;
delete FROM ps_gp_strm;
INSERT INTO ps_gp_strm
(strm_num, emplid_FROM , emplid_to)
SELECT partition_number
, MIN(part_value) part_start
, MAX(part_value) part_end
FROM ( --calculate partition for each emplid
SELECT part_value
, CEIL(&num_partitions* 15
14There were 36381 employees in the period 1 payroll in 2003. 1% have be removed from the
4th stream to allow for some growth of this segment.
Fri Jun 27 page 1
gp_pye_seg_stat by calendar and stream
CAL_RUN_ID STRM_NUM MIN_EMP MAX_EMP EMPS NUM_ROWS
------------------ ---------- ----------- ----------- ---------- ----------
AN2003/01 1 00000713 10000493 6977 22300
2 10000506 10363684 6198 22300
3 10363692 10628366 6091 22304
4 10628518 10816457 5827 22300
5 10816473 10938447 5890 22300
6 10938455 11015502 5398 20980
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 34 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
LEAST( 1,
SUM(proportion) OVER (ORDER BY part_value range
unbounded preceding)
)) partition_number
FROM ( --calculate proportion of month
SELECT part_value
, ratio_to_report(elements) OVER () proportion
FROM ( -- sum elements by partion value
SELECT part_value
, SUM(elements) elements
FROM ( --filter and generate partition key
SELECT s.emplid part_value
, COUNT(*) elements
FROM ps_gp_pye_seg_stat s
WHERE cal_run_id = (
SELECT MAX(cal_run_id) max_cal_run_id
FROM ps_gp_pye_seg_stat
WHERE cal_run_id like 'AN%'
)
GROUP BY s.emplid
UNION ALL
SELECT MAX(emplid), &&ADJ
FROM ps_personal_data
)
GROUP BY part_value
)
)
)
GROUP BY partition_number
/
UPDATE ps_gp_strm
SET emplid_from = '0'16
WHERE strm_num = (
SELECT MIN(strm_num)
FROM ps_gp_strm)
;
UPDATE ps_gp_strm a
SET emplid_to = (
SELECT SUBSTR(emplid_from,1,LENGTH(emplid_from)-1)
|| CHR(ASCII(SUBSTR(emplid_from,LENGTH(emplid_from),1))-1)
|| 'Z'
FROM ps_gp_strm b
WHERE b.strm_num = a.strm_num + 1)
WHERE strm_num = '8.4' THEN /*new column in pt8.4*/
l_sql := l_sql || 'OBJECTOWNERID, ';
END IF;
l_sql := l_sql ||'LASTUPDDTTM, LASTUPDOPRID, PROJECTDESCR, RELEASELABEL, RELEASEDTTM) '
24This script also creates an Application Designer project GFC_GPBUILD. This serves three
purposes.
It documents which records have been converted to partitioned and global temporary
tables
It enables a script to be built to rebuild the records are normal tables, thus enabling the
changes for streaming to be easily reversed.
By building ALTER scripts, it is possible to check for missing or incorrectly created
indexes.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 41
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
||'VALUES ('||l_version||','''||l_projectname||''','' '','' '','' '','
||''' '',16232832,0,1,3,'
||'50,0,'' '', null,' ;
IF l_ptver >= '8.4' THEN /*new column in pt8.4*/
l_sql := l_sql || ''' '',' ;
END IF;
l_sql := l_sql || 'sysdate,''PS'','' '', '' '', NULL)';
EXECUTE IMMEDIATE l_sql;
UPDATE PSLOCK
SET VERSION = VERSION + 1
WHERE OBJECTTYPENAME IN ('PJM');
END;
--populate list of tables
--19.6.2003 list of tables reorganised
PROCEDURE gfc_gp_tables IS
BEGIN
INSERT INTO gfc_gp_tables
(recname, table_name, table_type)
SELECT r.recname,
DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) table_name,
'P' table_type
FROM psrecdefn r
WHERE r.rectype = 0
-- AND r.recname LIKE '%STAT' --restrict tables
AND ( r.recname IN( 25
'GP_ABS_EVENT', /*absence - added 3.10.2003*/
'GP_GL_DATA', /*gl transfer table*/
'GP_GRP_LIST_RUN', /*new in 8.4*/
'GP_ITER_TRGR',
'GP_MESSAGES',
'GP_PAYMENT',
'GP_PI_GEN_DATA', /*postitive input*/
'GP_PI_MNL_DATA', /*postitive input*/
'GP_PI_MNL_SOVR', /*postitive input*/
'GP_PYE_OVRD',
'GP_PYE_PRC_STAT',
'GP_PYE_SEG_STAT',
'GP_RSLT_ABS', /*payroll calculation results*/
'GP_RSLT_ACUM', /*payroll calculation results*/
'GP_RSLT_DELTA', /*payroll calculation results*/
'GP_RSLT_ERN_DED', /*payroll calculation results*/
'GP_RSLT_PI_DATA', /*payroll calculation results*/
'GP_RSLT_PI_SOVR', /*payroll calculation results*/
'GP_RSLT_PIN', /*payroll calculation results*/
'GP_RTO_TRG_CTRY', /*8.1 + 8.3 only*/
25 This section is a list of the tables to be partitioned within GP. They are the core result
tables, and the writable array tables, which are country specific. This list includes Swiss as
well as UK tables. If the table is not present in PSRECDEFN (for example, because the Swiss
GP module has not been loaded) then this script will not attempt to build it.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 42 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
'GP_RTO_TRGR', /*deadlock problem*/
'GPCH_BK_XFER_EE', /*bank transfer*/
'GPCH_TX_DATA', /*tax data table - added 19.6.2003 - to
improve scan and reduce latch contention*/
'GPGB_ABS_EVT_JR',
'JOB', /*hr data */
'X_PYE_OVRD_ET' /*customer table*/
--uk payslip process gpgb_pslip cannot be run streamed yet 26
-- /*gp payslip tables*/
-- 'GPGB_PSLIP_P_ED','GPGB_PSLIP_P_BL',
-- 'GPGB_PSLIP_P_HR','GPGB_PSLIP_P_FT'
)
OR r.recname IN(
SELECT recname
FROM ps_gp_wa_array
)
--GP_PYE_STAT_WRK partitioned prior to 8.1.7.3 else GT due to suspected Oracle in 8.1.7.327
OR ( r.recname = 'GP_PYE_STAT_WRK'
AND l_oraver = '8.1.7.3'
)
)
;
END;
…
…
--process partitioned tables
PROCEDURE part_tables IS
CURSOR tables_cursor IS
SELECT *
FROM gfc_gp_tables t
WHERE table_type = 'P'
AND NOT recname IN( -- supress partitioning of tables with long columns
SELECT c.recname
FROM gfc_ps_tab_columns c
, psdbfield f
WHERE c.fieldname = f.fieldname
AND f.fieldtype IN(1,8,9)
AND (f.length = 0 OR f.length > 2000)
)
ORDER BY recname;
p_tables tables_cursor%ROWTYPE;
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 45
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
l_subpartitions INTEGER := 0;
l_analyze VARCHAR2(200);
l_hint VARCHAR2(100) := '+APPEND';
l_counter INTEGER := 0;
l_degree VARCHAR2(100);
BEGIN
IF l_nologging THEN
l_hint := l_hint||' NOLOGGING';
END IF;
OPEN tables_cursor;
LOOP
FETCH tables_cursor INTO p_tables;
EXIT WHEN tables_cursor%NOTFOUND;
--for HASH sub-partitioning the largest result tables by hash(cal_run_id)
--,'GP_PI_MNL_SOVR','GP_PI_MNL_DATA','GP_PYE_OVRD', 'X_PYE_OVRD_ET'
l_subpartitions := 0;29
IF p_tables.recname IN('GP_RSLT_ACUM','GP_RSLT_PIN') THEN /*number of hash
partitions - 0 to disable*/
l_subpartitions := l_num_sub_parts;
ELSIF p_tables.recname IN('GP_RSLT_ERN_DED') THEN
l_subpartitions := l_num_sub_parts;
ELSIF p_tables.recname IN('GP_RSLT_PI_SOVR','GP_RSLT_PI_DATA') THEN
l_subpartitions := l_num_sub_parts;
ELSIF p_tables.recname IN('GP_GL_DATA') THEN
l_subpartitions := l_num_sub_parts;
ELSIF p_tables.recname IN('GP_PYE_SEG_STAT') THEN --not 'GP_PYE_PRC_STAT',
l_subpartitions := l_num_sub_parts;
END IF;
…
extracts from gpbuild.sql
Sample Output - Partitioned Table
The following is an extract from the generated build script for one of the partitinoed tables
set echo on pause off message on verify on feedback on timi on autotrace off pause off lines 100
spool gpbuild_XXXX_gp_rslt_acum.lst
REM XXXX @ 12:30:28 21.11.2003
WHENEVER SQLERROR CONTINUE
DROP TABLE old_gp_rslt_acum;
WHENEVER SQLERROR EXIT FAILURE30
ALTER TABLE PS_GP_RSLT_ACUM RENAME PARTITION GP_RSLT_ACUM_01 TO OLD_GP_RSLT_ACUM_01; 31
…
ALTER TABLE PS_GP_RSLT_ACUM RENAME PARTITION GP_RSLT_ACUM_40 TO OLD_GP_RSLT_ACUM_40;
29 This section specifies the tables to be hash-subpartitioned.
30 SQL*Plus will terminate if an error is raised. This prevents this script causing data loss.
31 Only existing partitions are renamed.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 46 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
ALTER INDEX PS_GP_RSLT_ACUM RENAME PARTITION GP_RSLT_ACUM_01 TO OLD_GP_RSLT_ACUM_01;
…
ALTER INDEX PS_GP_RSLT_ACUM RENAME PARTITION GP_RSLT_ACUM_40 TO OLD_GP_RSLT_ACUM_40;
pause
CREATE TABLE gfc_gp_rslt_acum
(emplid VARCHAR2(11) NOT NULL
,cal_run_id VARCHAR2(18) NOT NULL
,empl_rcd SMALLINT NOT NULL
,gp_paygroup VARCHAR2(10) NOT NULL
,cal_id VARCHAR2(18) NOT NULL
,orig_cal_run_id VARCHAR2(18) NOT NULL
,rslt_seg_num SMALLINT NOT NULL
,pin_num INTEGER NOT NULL
,empl_rcd_acum SMALLINT NOT NULL
,acm_from_dt DATE
,acm_thru_dt DATE
,slice_bgn_dt DATE
,slice_end_dt DATE
,seq_num8 INTEGER NOT NULL
,user_key1 VARCHAR2(25) NOT NULL
,user_key2 VARCHAR2(25) NOT NULL
,user_key3 VARCHAR2(25) NOT NULL
,user_key4 VARCHAR2(25) NOT NULL
,country VARCHAR2(3) NOT NULL
,acm_type VARCHAR2(1) NOT NULL
,acm_prd_optn VARCHAR2(1) NOT NULL
,calc_rslt_val DECIMAL(18,6) NOT NULL
,calc_val DECIMAL(18,6) NOT NULL
,user_adj_val DECIMAL(18,6) NOT NULL
,pin_parent_num INTEGER NOT NULL
,corr_rto_ind VARCHAR2(1) NOT NULL
)
PCTFREE 10
PCTUSED 80
PARTITION BY RANGE(EMPLID)
SUBPARTITION BY HASH (CAL_RUN_ID) SUBPARTITIONS 32
(PARTITION gp_rslt_acum_01 VALUES LESS THAN ('032586510') 32 TABLESPACE gptabpart01
(SUBPARTITION gp_rslt_acum_01_01
…
,SUBPARTITION gp_rslt_acum_01_32
)
,PARTITION gp_rslt_acum_02 VALUES LESS THAN ('063683322') TABLESPACE gptabpart02
(SUBPARTITION gp_rslt_acum_02_01
…
,SUBPARTITION gp_rslt_acum_02_32
)
…
,PARTITION gp_rslt_acum_40 VALUES LESS THAN (maxvalue) TABLESPACE gptabpart40
32The values in partition 1 must be less than the EMPLID_FROM for stream 2, and so on.
The values in the last partition have no limit. The build script takes these values from
PS_GP_STRM.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 47
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
(SUBPARTITION gp_rslt_acum_40_01
…
,SUBPARTITION gp_rslt_acum_40_32
)
)
ENABLE ROW MOVEMENT
NOLOGGING
;
GRANT SELECT ON gfc_gp_rslt_acum TO PS_READ_ALL;
GRANT INSERT, UPDATE, DELETE ON gfc_gp_rslt_acum TO PS_UPDATE_ALL;
LOCK TABLE ps_gp_rslt_acum IN EXCLUSIVE MODE;
CREATE OR REPLACE TRIGGER gp_rslt_acum_nochange
BEFORE INSERT OR UPDATE OR DELETE ON ps_gp_rslt_acum
BEGIN
RAISE_APPLICATION_ERROR(-20100,'NO OPERATIONS ALLOW ON GP_RSLT_ACUM'); 33
END;
/
LOCK TABLE ps_gp_rslt_acum IN EXCLUSIVE MODE;
INSERT /*+APPEND NOLOGGING*/ INTO gfc_gp_rslt_acum(
emplid,cal_run_id,empl_rcd,gp_paygroup,cal_id,orig_cal_run_id,rslt_seg_num,pin_num
,empl_rcd_acum,acm_from_dt,acm_thru_dt,slice_bgn_dt,slice_end_dt,seq_num8,user_key1
,user_key2,user_key3,user_key4,country,acm_type,acm_prd_optn,calc_rslt_val,calc_val
,user_adj_val,pin_parent_num,corr_rto_ind
) SELECT
emplid,cal_run_id,empl_rcd,gp_paygroup,cal_id,orig_cal_run_id,rslt_seg_num,pin_num
,empl_rcd_acum,acm_from_dt,acm_thru_dt,slice_bgn_dt,slice_end_dt,seq_num8,user_key1
,user_key2,user_key3,user_key4,country,acm_type,acm_prd_optn,calc_rslt_val,calc_val
,user_adj_val,pin_parent_num,corr_rto_ind
FROM ps_gp_rslt_acum;
COMMIT;
pause
CREATE UNIQUE INDEX gfc_gp_rslt_acum ON gfc_gp_rslt_acum
(emplid
,cal_run_id
,empl_rcd
,gp_paygroup
,cal_id
,orig_cal_run_id
,rslt_seg_num
,pin_num
,empl_rcd_acum
,acm_from_dt
,acm_thru_dt
,slice_bgn_dt
,slice_end_dt
33Changes to the table being rebuilt are not permitted when this script is running. The trigger
will be dropped with the old table.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 48 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
,seq_num8
) LOCAL34
(PARTITION gp_rslt_acum_01 TABLESPACE gpidxpart01
(SUBPARTITION gp_rslt_acum_01_01
…
,SUBPARTITION gp_rslt_acum_01_32
)
…
,PARTITION gp_rslt_acum_40 TABLESPACE gpidxpart40
(SUBPARTITION gp_rslt_acum_40_01
…
,SUBPARTITION gp_rslt_acum_40_32
)
)
NOLOGGING
;
ALTER INDEX gfc_gp_rslt_acum
LOGGING NOPARALLEL
;
ALTER INDEX ps_gp_rslt_acum RENAME TO old_gp_rslt_acum;
ALTER INDEX gfc_gp_rslt_acum RENAME TO ps_gp_rslt_acum;
pause
ALTER TABLE ps_gp_rslt_acum LOGGING NOPARALLEL MONITORING;
ALTER TABLE ps_gp_rslt_acum RENAME TO old_gp_rslt_acum;
ALTER TABLE gfc_gp_rslt_acum RENAME TO ps_gp_rslt_acum;
pause
BEGIN
sys.dbms_stats.gather_table_stats
(ownname=>'SYSADM'
,tabname=>'ps_gp_rslt_acum'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1'
,degree=>5
,granularity=>'SUBPARTITION'
,cascade=>TRUE);
END;
/
pause
DROP TABLE old_gp_rslt_acum;
spool off
gpbuild_xxxx.sql
34 The indexes on the partitioned tables are locally partitioned, so they have the same partition
ranges, and the values do not need to be specified.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 49
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 50 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Sample Output - Global Temporary Table
The following is an extract from the generated build script for one of the Global Temporary
tables
REM FIN37D09 @ 12:30:58 21.11.2003
WHENEVER SQLERROR CONTINUE
DROP TABLE ps_gpch_rp_al07_1;
WHENEVER SQLERROR EXIT FAILURE
CREATE GLOBAL TEMPORARY TABLE ps_gpch_rp_al07_1
(gpch_ac_pnr INTEGER NOT NULL
,gpch_ac_seq INTEGER NOT NULL
,cal_run_id VARCHAR2(18) NOT NULL
,gpch_ac_sig SMALLINT NOT NULL
) ON COMMIT PRESERVE ROWS;
GRANT SELECT ON ps_gpch_rp_al07_1 TO PS_READ_ALL;
GRANT INSERT, UPDATE, DELETE ON ps_gpch_rp_al07_1 TO PS_UPDATE_ALL;
CREATE UNIQUE INDEX ps_gpch_rp_al07_1 ON ps_gpch_rp_al07_1
(gpch_ac_pnr
,gpch_ac_seq
,cal_run_id
);
spool off
Process Type Definitions (gen_prcstypedefn.sql)
This script creates the new GP COBOL SQL Stream and GP Application Engine Stream
process type definitions for the streamed payroll processes.
rem gen_prcstypedefn.sql
rollback
/
drop table dmk
/
create table dmk as
select 'GP '|| replace(PRCSTYPE,'Application Engine','App Engine')||' Stream
'||LTRIM(TO_CHAR(s.strm_num,'00')) prcstype
, OPSYS
, DBTYPE
, VERSION
, replace(PARMLIST,'%%RUNCNTLID%%','GPStream'||LTRIM(TO_CHAR(s.strm_num,'00'))) parmlist
, CMDLINE
, WORKINGDIR
, OUTPUTDEST
, GENPRCSTYPE
, WINPARM
, MVSSHELLID
, AS4JOBDESCNAME
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 51
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
, AS4JOBDESCLIB
, PRCSTYPEDESCR ||' Stream '||LTRIM(TO_CHAR(s.strm_num,'00')) PRCSTYPEDESCR
, '0' RESTARTENABLED
, sysdate LASTUPDDTTM
, LASTUPDOPRID
from ps_prcstypedefn t
, ps_gp_strm s
where t.prcstype IN('COBOL SQL','Application Engine')
and t.dbtype = 2 /*ORACLE*/
and t.opsys = 4 /*UNIX*/
/
delete from ps_prcstypedefn t
where ( t.prcstype like 'GP COBOL SQL Stream%'
or t.prcstype like 'GP App Engine Stream%'
or t.prcstype like 'GP Application Engine Stream%')
and t.dbtype = 2 /*ORACLE*/
and t.opsys = 4 /*UNIX*/
;
insert into ps_prcstypedefn
select * from dmk
minus
select * from ps_prcstypedefn;
select * from ps_prcstypedefn
where (prcstype like '%COBOL SQL%'
or prcstype like '%App%Engine%')
and dbtype = 2
and opsys = 4
/
gen_prcstypedefn.sql
Process Definitions
The following scripts create new process definitions for the streamed payroll processes.
Payroll Calculation
PeopleTools 8.1 (gen_prcsdefn.sql)
rem gen_prcsdefn.sql
lock table ps_prcsdefn in exclusive mode;
delete from ps_prcsdefn where prcstype like 'GP COBOL SQL%';
delete from ps_prcsdefngrp where prcstype like 'GP COBOL SQL%';
delete from ps_prcsdefnpnl where prcstype like 'GP COBOL SQL%';
DECLARE
CURSOR PS_CURSOR IS
SELECT
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 52 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
t.PRCSTYPE,
d.PRCSNAME,
d.VERSION,
replace(d.PARMLIST,'%%RUNCNTLID%%','GPStream'||LTRIM(TO_CHAR(s.strm_num,'0
0'))) parmlist,
d.PARMLISTTYPE,
d.CMDLINE,
d.CMDLINETYPE,
d.WORKINGDIR,
d.WORKINGDIRTYPE,
d.OUTDESTTYPE,
d.OUTDEST,
d.OUTDESTSRC,
d.SQRRTFLAG,
d.LOGRQST,
d.APIAWARE,
9 PRCSPRIORITY,
2 RUNLOCATION,
'PSUNX' SERVERNAME,
d.MVSSHELLID,
d.MSGLOGTBL,
d.RQSTTBL,
d.RECURNAME,
d.AS4JOBDESCNAME,
d.AS4JOBDESCLIB,
d.DESCR||' Stream '||LTRIM(TO_CHAR(s.strm_num,'00')) descr,
d.LASTUPDDTTM,
d.LASTUPDOPRID,
d.DESCRLONG
FROM PS_PRCSDEFN D
, ps_prcstypedefn t
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP COBOL SQL%'
;
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO ps_prcsdefn (
PRCSTYPE,
PRCSNAME,
VERSION,
PARMLIST,
PARMLISTTYPE,
CMDLINE,
CMDLINETYPE,
WORKINGDIR,
WORKINGDIRTYPE,
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 53
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
OUTDESTTYPE,
OUTDEST,
OUTDESTSRC,
SQRRTFLAG,
LOGRQST,
APIAWARE,
PRCSPRIORITY,
RUNLOCATION,
SERVERNAME,
MVSSHELLID,
MSGLOGTBL,
RQSTTBL,
RECURNAME,
AS4JOBDESCNAME,
AS4JOBDESCLIB,
DESCR,
LASTUPDDTTM,
LASTUPDOPRID,
DESCRLONG)
VALUES (
PS_REC.PRCSTYPE,
PS_REC.PRCSNAME,
PS_REC.VERSION,
PS_REC.PARMLIST,
PS_REC.PARMLISTTYPE,
PS_REC.CMDLINE,
PS_REC.CMDLINETYPE,
PS_REC.WORKINGDIR,
PS_REC.WORKINGDIRTYPE,
PS_REC.OUTDESTTYPE,
PS_REC.OUTDEST,
PS_REC.OUTDESTSRC,
PS_REC.SQRRTFLAG,
PS_REC.LOGRQST,
PS_REC.APIAWARE,
9 /*PRCSPRIORITY=HIGH*/,
2 /*RUNLOCATION=SERVER*/,
PS_REC.SERVERNAME,
PS_REC.MVSSHELLID,
PS_REC.MSGLOGTBL,
PS_REC.RQSTTBL,
PS_REC.RECURNAME,
PS_REC.AS4JOBDESCNAME,
PS_REC.AS4JOBDESCLIB,
PS_REC.DESCR,
PS_REC.LASTUPDDTTM,
PS_REC.LASTUPDOPRID,
PS_REC.DESCRLONG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
-- COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 54 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
END;
/
insert into PS_PRCSDEFNGRP
select t.prcstype
,d.prcsname
,d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcstypedefn t
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP COBOL SQL%'
minus
select * from ps_prcsdefngrp
;
insert into PS_PRCSDEFNPNL
select t.prcstype
,d.prcsname
,d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcstypedefn t
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP COBOL SQL%'
minus
select * from ps_prcsdefnpnl
;
gen_prcsdefn.sql
PeopleTools 8.4 (gen_prcsdefn84.sql)
rem gen_prcsdefn84.sql
lock table ps_prcsdefn in exclusive mode;
delete from ps_prcsdefn where prcstype like 'GP COBOL SQL%';
delete from ps_prcsdefngrp where prcstype like 'GP COBOL SQL%';
delete from ps_prcsdefnpnl where prcstype like 'GP COBOL SQL%';
DECLARE
CURSOR PS_CURSOR IS
SELECT
t.PRCSTYPE,
d.PRCSNAME,
d.VERSION,
replace(d.PARMLIST,'%%RUNCNTLID%%','GPStream'||LTRIM(TO_CHAR(s.strm_num,'0
0'))) parmlist,
d.PARMLISTTYPE,
d.CMDLINE,
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 55
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
d.CMDLINETYPE,
d.WORKINGDIR,
d.WORKINGDIRTYPE,
d.OUTDESTTYPE,
d.OUTDEST,
d.OUTDESTSRC,
d.SQRRTFLAG,
d.LOGRQST,
d.APIAWARE,
5 PRCSPRIORITY,
2 RUNLOCATION,
' ' SERVERNAME,
d.MVSSHELLID,
d.MSGLOGTBL,
d.RQSTTBL,
d.RECURNAME,
d.DESCR||' Stream '||LTRIM(TO_CHAR(s.strm_num,'00')) descr,
d.LASTUPDDTTM,
d.LASTUPDOPRID,
d.RECVRYPRCSTYPE,
d.RECVRYPRCSNAME,
d.RETENTIONDAYS,
d.OUTDESTFORMAT,
d.PSRF_FOLDER_NAME,
d.DESCRLONG
FROM PS_PRCSDEFN D
, ps_prcstypedefn t
, ps_gp_strm s
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and t.dbtype = 2
and t.opsys = 4
and 'GP COBOL SQL Stream '||LTRIM(TO_CHAR(s.strm_num,'00')) =
t.prcstype
;
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO ps_prcsdefn (
PRCSTYPE,
PRCSNAME,
VERSION,
PARMLIST,
PARMLISTTYPE,
CMDLINE,
CMDLINETYPE,
WORKINGDIR,
WORKINGDIRTYPE,
OUTDESTTYPE,
OUTDEST,
OUTDESTSRC,
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 56 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
SQRRTFLAG,
LOGRQST,
APIAWARE,
PRCSPRIORITY,
RUNLOCATION,
SERVERNAME,
MVSSHELLID,
MSGLOGTBL,
RQSTTBL,
RECURNAME,
DESCR,
LASTUPDDTTM,
LASTUPDOPRID,
RECVRYPRCSTYPE,
RECVRYPRCSNAME,
RETENTIONDAYS,
OUTDESTFORMAT,
PSRF_FOLDER_NAME,
DESCRLONG)
VALUES (
PS_REC.PRCSTYPE,
PS_REC.PRCSNAME,
PS_REC.VERSION,
PS_REC.PARMLIST,
PS_REC.PARMLISTTYPE,
PS_REC.CMDLINE,
PS_REC.CMDLINETYPE,
PS_REC.WORKINGDIR,
PS_REC.WORKINGDIRTYPE,
PS_REC.OUTDESTTYPE,
PS_REC.OUTDEST,
PS_REC.OUTDESTSRC,
PS_REC.SQRRTFLAG,
PS_REC.LOGRQST,
PS_REC.APIAWARE,
9 /*PRCSPRIORITY=HIGH*/,
2 /*RUNLOCATION=SERVER*/,
PS_REC.SERVERNAME,
PS_REC.MVSSHELLID,
PS_REC.MSGLOGTBL,
PS_REC.RQSTTBL,
PS_REC.RECURNAME,
PS_REC.DESCR,
PS_REC.LASTUPDDTTM,
PS_REC.LASTUPDOPRID,
PS_REC.RECVRYPRCSTYPE,
PS_REC.RECVRYPRCSNAME,
PS_REC.RETENTIONDAYS,
PS_REC.OUTDESTFORMAT,
PS_REC.PSRF_FOLDER_NAME,
PS_REC.DESCRLONG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
-- COMMIT;
RECORDS_PROCESSED := 0;
END IF;
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 57
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;
/
insert into PS_PRCSDEFNGRP
select t.prcstype
, d.prcsname
, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcstypedefn t
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP COBOL SQL%'
minus
select * from ps_prcsdefngrp
;
insert into PS_PRCSDEFNPNL
select t.prcstype
, d.prcsname
, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcstypedefn t
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP COBOL SQL%'
minus
select * from ps_prcsdefnpnl
; ;
gen_prcsdefn84.sql
Banking and GL processes (gen_prcsdefn2.sql)
rem gen_prcsdefn.sql
lock table ps_prcsdefn in exclusive mode;
delete from ps_prcsdefn where prcstype like 'GP App%Engine%';
delete from ps_prcsdefngrp where prcstype like 'GP App%Engine%';
delete from ps_prcsdefnpnl where prcstype like 'GP App%Engine%';
DECLARE
CURSOR PS_CURSOR IS
SELECT
t.PRCSTYPE,
d.PRCSNAME,
d.VERSION,
d.PARMLIST,
d.PARMLISTTYPE,
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 58 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
d.CMDLINE,
d.CMDLINETYPE,
d.WORKINGDIR,
d.WORKINGDIRTYPE,
d.OUTDESTTYPE,
d.OUTDEST,
d.OUTDESTSRC,
d.SQRRTFLAG,
d.LOGRQST,
d.APIAWARE,
9 PRCSPRIORITY,
2 RUNLOCATION,
'PSUNX' SERVERNAME,
d.MVSSHELLID,
d.MSGLOGTBL,
d.RQSTTBL,
d.RECURNAME,
d.AS4JOBDESCNAME,
d.AS4JOBDESCLIB,
d.DESCR,
d.LASTUPDDTTM,
d.LASTUPDOPRID,
d.DESCRLONG
FROM PS_PRCSDEFN D
, ps_prcstypedefn t
where d.prcstype = 'Application Engine'
and d.prcsname IN('GP_PMT_PREP','GP_GL_PREP','GPGB_PSLIP')
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP App Engine%'
and not exists(select 'x'
from ps_prcsdefn d1
where d1.prcstype = t.prcstype
and d1.prcsname = d.prcsname
)
;
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO ps_prcsdefn (
PRCSTYPE,
PRCSNAME,
VERSION,
PARMLIST,
PARMLISTTYPE,
CMDLINE,
CMDLINETYPE,
WORKINGDIR,
WORKINGDIRTYPE,
OUTDESTTYPE,
OUTDEST,
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 59
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
OUTDESTSRC,
SQRRTFLAG,
LOGRQST,
APIAWARE,
PRCSPRIORITY,
RUNLOCATION,
SERVERNAME,
MVSSHELLID,
MSGLOGTBL,
RQSTTBL,
RECURNAME,
AS4JOBDESCNAME,
AS4JOBDESCLIB,
DESCR,
LASTUPDDTTM,
LASTUPDOPRID,
DESCRLONG)
VALUES (
PS_REC.PRCSTYPE,
PS_REC.PRCSNAME,
PS_REC.VERSION,
PS_REC.PARMLIST,
PS_REC.PARMLISTTYPE,
PS_REC.CMDLINE,
PS_REC.CMDLINETYPE,
PS_REC.WORKINGDIR,
PS_REC.WORKINGDIRTYPE,
PS_REC.OUTDESTTYPE,
PS_REC.OUTDEST,
PS_REC.OUTDESTSRC,
PS_REC.SQRRTFLAG,
PS_REC.LOGRQST,
PS_REC.APIAWARE,
9 /*PRCSPRIORITY=HIGH*/,
2 /*RUNLOCATION=SERVER*/,
PS_REC.SERVERNAME,
PS_REC.MVSSHELLID,
PS_REC.MSGLOGTBL,
PS_REC.RQSTTBL,
PS_REC.RECURNAME,
PS_REC.AS4JOBDESCNAME,
PS_REC.AS4JOBDESCLIB,
PS_REC.DESCR,
PS_REC.LASTUPDDTTM,
PS_REC.LASTUPDOPRID,
PS_REC.DESCRLONG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
-- COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;
/
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 60 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
insert into PS_PRCSDEFNGRP
select t.prcstype
,d.prcsname
,d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcstypedefn t
where d.prcstype = 'Application Engine'
and d.prcsname IN('GP_PMT_PREP','GP_GL_PREP','GPGB_PSLIP')
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP App Engine%'
minus
select * from ps_prcsdefngrp
;
insert into PS_PRCSDEFNPNL
select t.prcstype
,d.prcsname
,d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcstypedefn t
where d.prcstype = 'Application Engine'
and d.prcsname IN('GP_PMT_PREP','GP_GL_PREP','GPGB_PSLIP')
and t.dbtype = 2
and t.opsys = 4
and t.prcstype like 'GP App Engine%'
minus
select * from ps_prcsdefnpnl
;
gen_prcsdefn2.sql
Job Definitions
The PeopleSoft jobs can be created by script.
Payroll Calculation Job (gen_prcsjobdefn.sql)
gen_prcsjobdefn
rollback
/
delete from ps_prcsjobdefn where prcsjobname = 'GPSTREAM';
delete from ps_prcsjobdefnlang where prcsjobname = 'GPSTREAM';
delete from ps_prcsjobitem where prcsjobname = 'GPSTREAM';
delete from ps_prcsjobpnl where prcsjobname = 'GPSTREAM';
delete from ps_prcsjobgrp where prcsjobname = 'GPSTREAM';
insert into ps_prcsjobdefn
values
('GPSTREAM'
,'PSJob'
,0
--123456789012345678901234567890
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 61
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
,'GP GPPDPRUN Stream Job'
,'PSUNX'
,' '
,'9'
,'1'
,' '
,' '
,sysdate
,'GFC'
);
insert into ps_prcsjobitem
select j.prcsjobname,
TO_NUMBER(substr(p.prcstype,LENGTH(p.prcstype),1))
, p.prcstype
, p.prcsname
, '1' runalways
from ps_prcsjobdefn j
, ps_prcsdefn p
WHERE j.prcsjobname = 'GPSTREAM'
and p.prcsname = 'GPPDPRUN'
and p.prcstype like 'GP COBOL SQL Stream%'
minus
select *
from ps_prcsjobitem
WHERE prcsjobname = 'GPSTREAM'
;
insert into ps_prcsjobgrp
select j.prcsjobname, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcsjobdefn j
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and j.prcsjobname = 'GPSTREAM'
minus select * from ps_prcsjobgrp
;
insert into ps_prcsjobpnl
select j.prcsjobname, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcsjobdefn j
where d.prcstype = 'COBOL SQL'
and d.prcsname = 'GPPDPRUN'
and j.prcsjobname = 'GPSTREAM'
minus
select * from ps_prcsjobpnl
;
gen_prcsjobdefn.sql
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 62 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
GL and Banking Jobs (gen_prcsjobdefn2.sql)
rem gen_prcsjobdefn
rollback
/
delete from ps_prcsjobdefn where prcsjobname IN('GPGLPREP','GPPMTPRP','GPGB_PSLIP');
delete from ps_prcsjobdefnlang where prcsjobname IN('GPGLPREP','GPPMTPRP','GPGB_PSLIP');
delete from ps_prcsjobitem where prcsjobname IN('GPGLPREP','GPPMTPRP','GPGB_PSLIP');
delete from ps_prcsjobpnl where prcsjobname IN('GPGLPREP','GPPMTPRP','GPGB_PSLIP');
delete from ps_prcsjobgrp where prcsjobname IN('GPGLPREP','GPPMTPRP','GPGB_PSLIP');
rem---------------------------------------------------------------------------------
rem GPPMTPRP - GP_PMT_PREP
insert into ps_prcsjobdefn
values
('GPPMTPRP'
,'PSJob'
,0
--123456789012345678901234567890
,'GP AE GP_PMT_PREP Stream Job'
,'PSUNX'
,' '
,'9'
,'1'
,' '
,' '
,sysdate
,'GFC'
);
insert into ps_prcsjobgrp
select j.prcsjobname, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_PMT_PREP'
and j.prcsjobname = 'GPPMTPRP'
minus select * from ps_prcsjobgrp
;
insert into ps_prcsjobpnl
select j.prcsjobname, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_PMT_PREP'
and j.prcsjobname = 'GPPMTPRP'
minus
select * from ps_prcsjobpnl
;
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 63
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
insert into ps_prcsjobitem
select j.prcsjobname,
TO_NUMBER(substr(p.prcstype,LENGTH(p.prcstype)-1,2))
, p.prcstype
, p.prcsname
, '1' runalways
from ps_prcsjobdefn j
, ps_prcsdefn p
WHERE j.prcsjobname = 'GPPMTPRP'
and p.prcsname = 'GP_PMT_PREP'
and p.prcstype like 'GP App Engine Stream%'
minus
select *
from ps_prcsjobitem
WHERE prcsjobname = 'GPPMTPRP'
;
rem---------------------------------------------------------------------------------
rem GPGLPREP - GP_GL_PREP
insert into ps_prcsjobdefn
values
('GPGLPREP'
,'PSJob'
,0
--123456789012345678901234567890
,'GP AE GP_GL_PREP Stream Job'
,'PSUNX'
,' '
,'9'
,'1'
,' '
,' '
,sysdate
,'GFC'
);
insert into ps_prcsjobgrp
select j.prcsjobname, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_GL_PREP'
and j.prcsjobname = 'GPGLPREP'
minus select * from ps_prcsjobgrp
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 64 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
;
insert into ps_prcsjobpnl
select j.prcsjobname, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_GL_PREP'
and j.prcsjobname = 'GPGLPREP'
minus
select * from ps_prcsjobpnl
;
insert into ps_prcsjobitem
select j.prcsjobname,
TO_NUMBER(substr(p.prcstype,LENGTH(p.prcstype)-1,2))
, p.prcstype
, p.prcsname
, '1' runalways
from ps_prcsjobdefn j
, ps_prcsdefn p
WHERE j.prcsjobname = 'GPGLPREP'
and p.prcsname = 'GP_GL_PREP'
and p.prcstype like 'GP App Engine Stream%'
minus
select *
from ps_prcsjobitem
WHERE prcsjobname = 'GPGLPREP'
;
insert into ps_prcsjobdefn
values
('GPGLPREP'
,'PSJob'
,0
--123456789012345678901234567890
,'GP AE GP_GL_PREP Stream Job'
,'PSUNX'
,' '
,'9'
,'1'
,' '
,' '
,sysdate
,'GFC'
);
insert into ps_prcsjobgrp
select j.prcsjobname, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 65
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
and d.prcsname = 'GP_GL_PREP'
and j.prcsjobname = 'GPGLPREP'
minus select * from ps_prcsjobgrp
;
insert into ps_prcsjobpnl
select j.prcsjobname, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_GL_PREP'
and j.prcsjobname = 'GPGLPREP'
minus
select * from ps_prcsjobpnl
;
insert into ps_prcsjobitem
select j.prcsjobname,
TO_NUMBER(substr(p.prcstype,LENGTH(p.prcstype)-1,2))
, p.prcstype
, p.prcsname
, '1' runalways
from ps_prcsjobdefn j
, ps_prcsdefn p
WHERE j.prcsjobname = 'GPGLPREP'
and p.prcsname = 'GP_GL_PREP'
and p.prcstype like 'GP App Engine Stream%'
minus
select *
from ps_prcsjobitem
WHERE prcsjobname = 'GPGLPREP'
;
insert into ps_prcsjobdefn
values
('GPGLPREP'
,'PSJob'
,0
--123456789012345678901234567890
,'GP AE GP_GL_PREP Stream Job'
,'PSUNX'
,' '
,'9'
,'1'
,' '
,' '
,sysdate
,'GFC'
);
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 66 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
insert into ps_prcsjobgrp
select j.prcsjobname, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_GL_PREP'
and j.prcsjobname = 'GPGLPREP'
minus select * from ps_prcsjobgrp
;
insert into ps_prcsjobpnl
select j.prcsjobname, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GP_GL_PREP'
and j.prcsjobname = 'GPGLPREP'
minus
select * from ps_prcsjobpnl
;
insert into ps_prcsjobitem
select j.prcsjobname,
TO_NUMBER(substr(p.prcstype,LENGTH(p.prcstype)-1,2))
, p.prcstype
, p.prcsname
, '1' runalways
from ps_prcsjobdefn j
, ps_prcsdefn p
WHERE j.prcsjobname = 'GPGLPREP'
and p.prcsname = 'GP_GL_PREP'
and p.prcstype like 'GP App Engine Stream%'
minus
select *
from ps_prcsjobitem
WHERE prcsjobname = 'GPGLPREP'
;
rem---------------------------------------------------------------------------------
rem GPGBPSLP - GPGB_PSLIP
insert into ps_prcsjobdefn
values
('GPGBPSLP'
,'PSJob'
,0
--123456789012345678901234567890
,'GP AE GPGB_PSLIP Stream Job'
,'PSUNX'
,' '
,'9'
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 67
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
,'1'
,' '
,' '
,sysdate
,'GFC'
);
insert into ps_prcsjobgrp
select j.prcsjobname, d.prcsgrp
FROM PS_PRCSDEFNGRP d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GPGB_PSLIP'
and j.prcsjobname = 'GPGBPSLP'
minus select * from ps_prcsjobgrp
;
insert into ps_prcsjobpnl
select j.prcsjobname, d.pnlgrpname
FROM PS_PRCSDEFNPNL d
, ps_prcsjobdefn j
where d.prcstype = 'Application Engine'
and d.prcsname = 'GPGB_PSLIP'
and j.prcsjobname = 'GPGBPSLP'
minus
select * from ps_prcsjobpnl
;
insert into ps_prcsjobitem
select j.prcsjobname,
TO_NUMBER(substr(p.prcstype,LENGTH(p.prcstype)-1,2))
, p.prcstype
, p.prcsname
, '1' runalways
from ps_prcsjobdefn j
, ps_prcsdefn p
WHERE j.prcsjobname = 'GPGBPSLP'
and p.prcsname = 'GPGB_PSLIP'
and p.prcstype like 'GP App Engine Stream%'
minus
select *
from ps_prcsjobitem
WHERE prcsjobname = 'GPGBPSLP'
;
gen_prcsjobdefn2.sql
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 68 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
Server Process Types( gen_serverclass.sql)
This script adds the new process type definitions to the server.
rem gen_prcsdefn.sql
rollback;
delete from ps_serverclass
WHERE ( prcstype like 'GP COBOL SQL Stream%'
or prcstype like 'GP App% Engine Stream%')
;
insert into ps_serverclass
select 'PSUNX' SERVERNAME
, 4 OPSYS
, PRCSTYPE
, '9' PRCSPRIORITY
, 1 MAXCONCURRENT
FROM ps_prcstypedefn
WHERE ( prcstype like 'GP COBOL SQL Stream%'
or prcstype like 'GP App% Engine Stream%')
;
gen_serverclass.sql
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 69
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
Run control build er for Payroll Calculation
Each stream requires a separate run control. The name of the run controls are fixed because
they are hard coded in the command line for the Cobol process (see Process Type Definitions
on page 17). The run controls need to be set up prior to each run. This can be done via the
PIA, but it can also be done by updating the run control table PS_GP_RUNCTL as below. All
that is necessary is to the flags as desired.
PeopleTools 8.1 (gpcalcall.sql)
rem gpcalcall.sql
DELETE FROM ps_prcsruncntl
WHERE oprid IN('PS')
AND run_cntl_id like 'GPStream%'
;
INSERT INTO ps_prcsruncntl
SELECT o.oprid
, 'GPStream'||s.strm_num run_cntl_id
, 'ENG','0'
FROM ps_gp_strm s
, psoprdefn o
WHERE o.oprid IN(‘PS’)
AND NOT EXISTS(
SELECT 'x'
FROM ps_prcsruncntl r
WHERE r.oprid = o.oprid
AND r.run_cntl_id = 'GPStream'||s.strm_num)
;
DELETE FROM ps_gp_runctl r
WHERE EXISTS(
SELECT 'x'
FROM ps_prcsruncntl p
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
AND r.run_cntl_id like 'GPStream%'
;
INSERT INTO ps_gp_runctl
SELECT p.oprid
, p.run_cntl_id
, c.cal_run_id
, s.strm_num
, 'Y' RUN_IDNT_IND
, 'Y' RUN_CALC_IND
, 'Y' RUN_RECALC_ALL_IND
, 'N' RUN_EXCL_ITER_IND
, 'N' RUN_FINAL_IND
, 'N' RUN_CANCEL_IND
, 'N' RUN_TRACE_OPTN
, '1' RUN_PHASE_OPTN
, '01' RUN_PHASE_STEP
, ' ' IDNT_PGM_OPTN
, ' ' NEXT_PGM
, 0 NEXT_STEP
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 70 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
, 0 NEXT_NUM
, ' ' CANCEL_PGM_OPTN
, ' ' NEXT_EMPLID
, 'Y' UPDATE_STATS_IND
, 'ENG' LANGUAGE_CD
, ' ' EXIT_POINT
, 0 SEQ_NUM5
, ' ' UE_CHKPT_CH1
, ' ' UE_CHKPT_CH2
, ' ' UE_CHKPT_CH3
, NULL UE_CHKPT_DT1
, NULL UE_CHKPT_DT2
, NULL UE_CHKPT_DT3
, 0 UE_CHKPT_NUM1
, 0 UE_CHKPT_NUM2
, 0 UE_CHKPT_NUM3
FROM ps_gp_strm s
, ps_gp_cal_run c
, ps_prcsruncntl p
WHERE c.process_strm_ind = 'Y'
AND p.run_cntl_id = 'GPStream'||s.strm_num
AND C.RUN_FINALIZED_IND = 'N'
--AND c.run_open_ind = 'Y'
AND NOT EXISTS(
SELECT 'x'
FROM ps_gp_runctl r
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
;
COMMIT
;
gpcalcall.sql
PeopleTools 8.4 (gpcalcall84.sql)
DELETE FROM ps_prcsruncntl
WHERE oprid IN('PIROGOVSKIG_DE','PS')
AND run_cntl_id like 'GPStream%'
;
INSERT INTO ps_prcsruncntl
SELECT o.oprid
, 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00')) run_cntl_id
, 'ENG','0'
FROM ps_gp_strm s
, psoprdefn o
WHERE o.oprid IN('PIROGOVSKIG_DE')
AND NOT EXISTS(
SELECT 'x'
FROM ps_prcsruncntl r
WHERE r.oprid = o.oprid
AND r.run_cntl_id = 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00'))
)
;
DELETE FROM ps_gp_runctl r
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 71
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
WHERE EXISTS(
SELECT 'x'
FROM ps_prcsruncntl p
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
AND r.run_cntl_id like 'GPStream%'
;
INSERT INTO ps_gp_runctl
SELECT p.oprid
, p.run_cntl_id
, c.cal_run_id
, 0 TXN_ID
, s.strm_num
, 0 PRC_NUM
, ' ' GROUP_LIST_ID
, 'Y' RUN_IDNT_IND
, 'N' RUN_UNFREEZE_IND
, 'Y' RUN_CALC_IND
, 'N' RUN_RECALC_ALL_IND
, 'N' RUN_FREEZE_IND
, 'N' RUN_APPROVE_IND
, 'N' APPR_BY_CAL_IND
, 'N' RUN_FINAL_IND
, 'N' RUN_CANCEL_IND
, 'N' RUN_TRACE_OPTN
, '1' RUN_PHASE_OPTN
, '01' RUN_PHASE_STEP
, ' ' IDNT_PGM_OPTN
, ' ' NEXT_PGM
, 0 NEXT_STEP
, 0 NEXT_NUM
, ' ' CANCEL_PGM_OPTN
, ' ' NEXT_EMPLID
, 'Y' UPDATE_STATS_IND
, 'ENG' LANGUAGE_CD
, ' ' EXIT_POINT
, 0 SEQ_NUM5
, ' ' UE_CHKPT_CH1
, ' ' UE_CHKPT_CH2
, ' ' UE_CHKPT_CH3
, NULL UE_CHKPT_DT1
, NULL UE_CHKPT_DT2
, NULL UE_CHKPT_DT3
, 0 UE_CHKPT_NUM1
, 0 UE_CHKPT_NUM2
, 0 UE_CHKPT_NUM3
, NULL PRC_SAVE_TS
FROM ps_gp_strm s
, ps_gp_cal_run c
, ps_prcsruncntl p
WHERE c.process_strm_ind = 'Y'
AND p.run_cntl_id = 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00'))
AND C.RUN_FINALIZED_IND = 'N'
--AND c.run_open_ind = 'Y'
AND c.cal_run_id = 'DD0604'
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 72 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
AND NOT EXISTS(
SELECT 'x'
FROM ps_gp_runctl r
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
;
COMMIT
;
gpcalcall84.sql
Run control builder for Banking process ( gppmtprep. sql)
There is a corresponding script to set up the run control for the Banking process.
DELETE FROM ps_prcsruncntl
WHERE oprid IN('PS')
AND run_cntl_id like 'GPStream%'
;
INSERT INTO ps_prcsruncntl
SELECT o.oprid
, 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00')) run_cntl_id
, 'ENG','0'
FROM ps_gp_strm s
, psoprdefn o
WHERE o.oprid IN('PS')
AND NOT EXISTS(
SELECT 'x'
FROM ps_prcsruncntl r
WHERE r.oprid = o.oprid
AND r.run_cntl_id = 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00')))
;
DELETE FROM ps_gp_pmt_prepare r
WHERE r.run_cntl_id like 'GPStream%'
--AND EXISTS(
-- SELECT 'x'
-- FROM ps_prcsruncntl p
-- WHERE r.oprid = p.oprid
-- AND r.run_cntl_id = p.run_cntl_id)
;
INSERT INTO ps_gp_pmt_prepare
SELECT p.run_cntl_id
, p.oprid
, c.cal_run_id
, s.strm_num
, 'Y' RUN_CALC_IND
, 'N' RUN_FINAL_IND
, 'Y' UPDATE_STATS_IND
FROM ps_gp_strm s
, ps_gp_cal_run c
, ps_prcsruncntl p
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 73
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
WHERE p.run_cntl_id = 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00'))
--AND c.run_open_ind = 'Y'
AND c.cal_run_id = (
SELECT MAX(c1.cal_run_id)
FROM ps_gp_cal_run c1
WHERE C1.RUN_FINALIZED_IND = 'Y'
AND c1.pmt_sent_ind = 'N'
AND c1.process_strm_ind = 'Y'
)
AND NOT EXISTS(
SELECT 'x'
FROM ps_gp_pmt_prepare r
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
AND p.oprid IN('PS')
;
COMMIT
;
gppmtprep.sql
Run control builder for GL Process ( gpglprep.sql )
There is a corresponding script to set up the run control for the GL process.
DELETE FROM ps_prcsruncntl
WHERE oprid IN('PS')
AND run_cntl_id like 'GPStream%'
;
INSERT INTO ps_prcsruncntl
SELECT o.oprid
, 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00')) run_cntl_id
, 'ENG','0'
FROM ps_gp_strm s
, psoprdefn o
WHERE o.oprid IN('PS')
AND NOT EXISTS(
SELECT 'x'
FROM ps_prcsruncntl r
WHERE r.oprid = o.oprid
AND r.run_cntl_id = 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00')))
;
DELETE FROM ps_gp_gl_prepare r
WHERE EXISTS(
SELECT 'x'
FROM ps_prcsruncntl p
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
AND r.run_cntl_id like 'GPStream%'
;
INSERT INTO ps_gp_gl_prepare
SELECT p.run_cntl_id
, p.oprid
, c.cal_run_id
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 74 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
, s.strm_num
, TRUNC(sysdate) POSTING_DATE
, 'Y' RUN_CALC_IND
, 'N' RUN_FINAL_IND
, 'Y' UPDATE_STATS_IND
FROM ps_gp_strm s
, ps_gp_cal_run c
, ps_prcsruncntl p
WHERE p.run_cntl_id = 'GPStream'||LTRIM(TO_CHAR(s.strm_num,'00'))
AND c.cal_run_id = (
SELECT MIN(c1.cal_run_id)
FROM ps_gp_cal_run c1
WHERE C1.RUN_FINALIZED_IND = 'Y'
AND c1.process_strm_ind = 'Y'
AND c1.run_open_ts is not null
)
AND NOT EXISTS(
SELECT 'x'
FROM ps_gp_gl_prepare r
WHERE r.oprid = p.oprid
AND r.run_cntl_id = p.run_cntl_id)
AND p.oprid IN('PS')
;
COMMIT
;
gpglprep.sql
Calculate CAL_RUN_ID Suffixes to Balance Volumes of Hash Partitions
The following script was used to calculate the suffixes.
rem hashcalc.sql
ALTER SESSION SET NLS_DATE_format = 'DD/MM/YYYY';
column cal_run_id format a15
drop table gfc_cal_run_id;
drop table gfc_hashvalues;
create table gfc_cal_run_id35
(procdate date
,cal_run_id varchar2(20) NOT NULL
,weight number
,hashvalue number
,cumweight number
,cal_run_idx varchar2(20)
,hashvaluex number
,cumweightx number
);
35 The table GFC_CAL_RUN_ID will hold a list of the calendars for which suffixes will be
calculated. The weight column is used when there are different pay frequencies that will have
separate sequences of CAL_RUN_IDs (eg. monthly and weekly). The procdate column is
used to determine the order in which the calendars will be processed. The relative size of the
two communities needs to be considered.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 75
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
create unique index gfc_cal_run_id on gfC_cal_run_id(cal_run_id);
create table gfc_hashvalues36
(cal_run_id varchar2(20) NOT NULL
,suffix varchar2(1)
,hashvalue number
,prevweight number
);
create unique index gfc_hashvalues on gfc_hashvalues (cal_run_id,suffix);
--populate future calendars
insert into gfc_cal_run_id (CAL_RUN_ID,weight,procdate)
select cal_run_id, weight, procdate
from (
select 'GFC'||TO_CHAR(procdate,'YYYY')||'M'||TO_CHAR(procdate,'MM') cal_run_id
, 10000 weight
, procdate+104 procdate
from (select ADD_MONTHS(TO_DATE('20030101','YYYYMMDD'),rownum) procdate
from dba_objects
where rownum = TO_DATE('01072003','DDMMYYYY') 38
--where procdate > sysdate39
--and not cal_run_id IN (SELECT cal_run_id FROM ps_gp_cal_run) --uncomment to remove setup calendars 40
;
select *
from gfc_cal_run_id
order by procdate, cal_Run_id
;
truncate table gfc_hashvalues;
--build list of all hash values and all suffixes
insert into gfc_hashvalues(cal_run_id,suffix)
select cal_run_id, suffix
from (
select cal_run_id
from gfc_cal_run_id
where hashvalue is null
) a
, (
select ' ' suffix
from dual
union all
select CHR(64+rownum)
from dba_objects
where rownum <= 26
) b;
update gfc_hashvalues
set hashvalue = sys.dbms_utility.get_hash_value(rtrim(cal_run_id||suffix),1,16)
;
38 In this example the new suffixes will be introduced from 1 st July 2003.
39 In reality you would certainly choose to exclude calendars that have already started
40 You might also choose to exclude calendars that have already been set up in payroll.
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 77
TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC 16 NOVEMBER 2011
declare
cursor c_cal_run_id is
select *
from gfc_cal_run_id
where hashvalue IS NULL
order by procdate, cal_run_id
;
p_cal_run_id c_cal_run_id%ROWTYPE;
l_hashvalue INTEGER;
l_suffix VARCHAR2(1);
cursor c_hashvalue is41
select h.hashvalue
, suffix
, NVL(SUM(c.weight),0) weight
from gfc_hashvalues h
, gfc_cal_run_id c
where h.hashvalue = c.hashvalue(+)
and h.cal_run_id = p_cal_run_id.cal_run_id
group by h.cal_run_id, h.hashvalue, h.suffix
order by weight, suffix
;
p_hashvalue c_hashvalue%ROWTYPE;
begin
OPEN c_cal_run_id;
LOOP
FETCH c_cal_run_id INTO p_cal_run_id;
EXIT WHEN c_cal_run_id%NOTFOUND;
OPEN c_hashvalue;
FETCH c_hashvalue INTO p_hashvalue;
update gfc_cal_run_id
set cal_run_id = p_cal_run_id.cal_run_id||p_hashvalue.suffix
, hashvalue = p_hashvalue.hashvalue
where cal_run_id = p_cal_run_id.cal_run_id
;
update gfc_cal_run_id a
set cumweight = (
SELECT SUM(b.weight)
FROM gfc_cal_run_id b
WHERE ( b.procdate <= a.procdate
OR ( b.procdate = a.procdate
AND b.cal_run_id <= a.cal_run_id
)
)
AND b.hashvalue = a.hashvalue
41 The hash value for the partition with the least amount of data in it will be chosen. Where
there are a number of hashvalues containing the same weight, or a number suffixes that will
produce the same hash value, then the alphabetically lowest suffix will be chosen. Where
possible this will be blank, ie no suffix will be added.
CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 78 GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL
16 NOVEMBER 2011 TECHNICAL PAPER - 62EF2748-0E8D-407D-AC4E-456F0759282B.DOC
)
WHERE a.weight IS NOT NULL
AND a.cumweight IS NULL
;
CLOSE c_hashvalue;
END LOOP;
CLOSE c_cal_run_id;
end;
/
spool hashcalc
select * from gfc_cal_run_id
order by procdate, cal_Run_id
;
spool off
hashcalc.sql
The calendar groups should be set up according to the results of this script. The following
statement can be used to insert the calendar groups with the adjusted IDs directly.
insert into ps_gp_cal_run
(CAL_RUN_ID
,DESCR
,DESCRSHORT
,PROCESS_RETRO_IND
,PROCESS_STRM_IND
,RUN_OPEN_IND
,RUN_FINALIZED_IND
,PMT_SENT_IND
,GL_SENT_IND
,TL_SENT_IND
,COUNTRY
)
select CAL_RUN_ID
, DECODE(SUBSTR(cal_run_id,8,1),
'M','Month '||SUBSTR(cal_run_id,9,2)||' '||SUBSTR(cal_run_id,4,4),
'W','Week ' ||SUBSTR(cal_run_id,9,2)||' '||SUBSTR(cal_run_id,4,4)
) DESCR
, DECODE(SUBSTR(cal_run_id,8,1),
'M','Mth '||SUBSTR(cal_run_id,9,2)||'/'||SUBSTR(cal_run_id,6,2) ,
'W','Wk ' ||SUBSTR(cal_run_id,9,2)||' '||SUBSTR(cal_run_id,4,4)
) DESCRSHORT
, 'Y'PROCESS_RETRO_IND
, DECODE(SUBSTR(cal_run_id,8,1),'M','Y','N') PROCESS_STRM_IND
, 'N' RUN_OPEN_IND
, 'N' RUN_FINALIZED_IND
, 'N' PMT_SENT_IND
, 'N' GL_SENT_IND
, 'N' TL_SENT_IND
, 'GBR' COUNTRY
from gfc_cal_run_id
where not cal_run_id IN (SELECT cal_run_id FROM ps_gp_cal_run)
;
GO-FASTER CONSULTANCY LTD. - CONFIDENTIAL CONFIGURING AND OPERATING STREAMED PROCESSING IN PEOPLESOFT GLOBAL PAYROLL 79