After running the Pre-Payments process to prepare the results for payment (according to the payment methods), you produce payments for your employees.
With Oracle Payroll, you can run the following types of payment process:
The Magnetic Tape process - MAGTAPE
The Cheque process - CHEQUE
The Cash Payments process - CASH (UK only)
See: Cash Process
The payment processes take the unpaid prepayment values allocated to each payment type and produce the required payment file.
You can also record any manual payments you make to a specific employee. These payments are not handled by the Payments processes. Recording a manual payment has the effect of marking the prepayment as paid.
The Magnetic Tape process generates the payment due and writes the data to a file on magnetic tape. It is this tape that is taken to the bank for payment.
There are two types of magnetic tape file, which are created differently:
Payments
End of year tax reporting
The actual format of these tapes is legislation specific.
The tape process is a simple 'C' harness which calls Oracle stored procedures and FastFormula formulas to produce the required tape file. The routine is generic: you can use it for any task that requires magnetic tape reporting. The actual structure and content of the tape is defined entirely by the stored procedure and a series of formulas.
Some examples that use the routine are:
BACS
NACHA
W2
P35 submissions (and equivalent in other countries)
Note: The order of the entries in the magnetic file is critical. Therefore the Magnetic Tape process cannot run with multiple threads (unlike the PrePayments or Cheque/Check Writer processes).
See also:
The Payroll Archive Reporter (PAR) Process
The payroll assignment action creation code is the entry point to the Magnetic Tape Payments process. Employee magnetic tape payments are recorded in Oracle HRMS as payroll and assignment actions with interlocks to the relevant pre-payment assignment actions. The interlocks prevent the pre-payments actions being rolled back while the magnetic tape actions exist.
Third party payments (such as the company's health plan contributions) do not result in payroll and assignment actions, and therefore would use the magnetic tape report interface.
You run PYUGEN with the following parameters:
consolidation_set_id - mandatory
Defines which set of unpaid pre-payments are paid.
payment_type_id - mandatory
Defines the driving PL/SQL procedure.
effective_date - optional
Identifies the effective date for processing.
payroll_id - optional
Restricts the assignments processed to those on the specified payroll on the effective date
start_date - optional
Specifies how far back the process searches for target prepayments. If this parameter is not specified, then the process scans back to the beginning of time.
organisation_payment_method_id - optional
Creates assignment actions interlocking to unpaid prepayments for that payment.
legislative - optional
Free-format parameters, available to all payroll actions. Your localization team may use these to pass in a number of legislation-specific parameters, made accessible to the payroll action through the entity horizon.
The system uses the PL/SQL driving procedure specified for the payment type on the database (for example, <package name>.<procedure name>). The PL/SQL procedure for the Magnetic Tape Writer process must drive off the assignment actions and not further restrict the assignments processed. Further restricting the assignments presents the danger of leaving some magnetic tape assignment actions never processed. When the process first runs the PL/SQL, one of the parameters passed is the payroll action id (PAYROLL_ACTION_ID).
The Magnetic Tape process actions prepayments with an effective date on or before the effective date of the magnetic tape action. The magnetic tape effective date defaults to session date in an AOL environment, and sysdate outside AOL.
The magnetic tape file generated is named as per the normal file-naming standards:
p<trunc(conc_request_id, 5)>.mf
The file name is padded with zeros if the length of the request id is shorter than five characters, (for example, p03451.mf).
It is written to the $APPLCSF/$APPLOUT directory, if $APPLCSF is defined, and otherwise to $PAY_TOP/$APPLOUT.
Several other files can be produced by this process. You can use these files to audit the assignments that are being processed. The audit files are created in the same way, except that the file extension .a<file_number>. So if a formula returns a value for audit file 6 then a file with the extension .a6 is created in the correct directory using the concurrent request id as described above.
Magnetic Tape reports are not recorded as payroll and assignment actions. The entry point is the specific Magnetic Tape code, PYUMAG. The PL/SQL determines which assignments to process.
Driving PL/SQL procedure (<package name>.<procedure name>)
Output file (full pathname included)
Audit file prefix (the prefix to the extension, plus the full path)
Effective date (the parameters to the driving PL/SQL procedure)
The optional parameters to the PL/SQL must be tokenised, so that the generic tape writer process can populate the PL/SQL tables for parameter name and parameter value. These tables constitute the interface between the generic writer process and the driving PL/SQL procedure.
See: The PL/SQL Driving Procedure
The magnetic tape action only processes formulas with an effective date on or before the effective date of the magnetic tape action. The magnetic tape effective date defaults to session date, in an AOL environment, and sysdate outside AOL.
The magnetic tape filename is generated if it is not supplied to the process. The filename is in the format:
o<trunc(conc_request_id, 5)>.mf
When an audit file prefix is not set but the process tries to write to an audit, the concurrent request id is used as the prefix and .out used as the extension. In these circumstances all audit returns are written to this file.
Using SRS, the generic tape writer process is defined once as an executable. You can then define any number of concurrent programs that invoke that executable. Each concurrent program can have its own set of parameters, its own hidden parameters, defaults and so on. For example, we can define two concurrent programs:
W2 report
Illinois Quarterly State Tax report
They would both use the magnetic tape writer executable PYUMAG, each with a hidden parameter specifying the appropriate PL/SQL procedure, and possibly, each with specific parameters. They appear as completely distinct reports to the user. This would be set up in the SRS process interface.
Similarly, magnetic payments can be made to appear as distinct processes to the user - the only difference is that the payment type is the hidden parameter, and the generic code determines the driving PL/SQL procedure from that.
Magnetic tapes are usually broken down into:
Records
Fields
The sequence in which the process writes the records to tape follows strictly defined rules. As a result, you can write a piece of code to return the name of the next record to write to tape.
Similarly, the actual records have strict field place and length requirements. For example:
Record | Fields |
---|---|
Tape Header | Batch Id, Company Name, Batch Record Length, and so on |
Employee | Employee Id, Salary, Age, Job, and so on |
Tape Footer | No. of Records Processed, Salary Total, and so on |
The following figure illustrates the Magnetic Tape process.
A C code harness performs the file handling (opening, closing and writing to files), and enables the PL/SQL and the formulas to interface.
The driving PL/SQL code sequences records by returning the name of a formula.
Each formula writes one type of record, such as the Tape Header, to tape. It defines the contents of the record.
The process of getting the formula and record name, then writing the record to tape is repeated until all the records are processed.
The driving PL/SQL determines which type of record is required at any stage of the processing, and uses context and parameter values to communicate with the formula.
The following figure illustrates how the C code acts as an interface between the PL/SQL and formula, and how the data is passed as context values.
Formulas use database items to reference variable values. For example, the employee and assignment number could be different for each run of the formula and record.
The database item is held within the database, which consists of components to make up a SQL statement. As the value could be different for each run of the formula, the 'where' clause of the statement is slightly different. This is done by substituting key values into the 'where' clause that uniquely select the required value. These substitution values are known as context values.
Context values are set by the driving PL/SQL procedure that places the values into a PL/SQL table. The PL/SQL table is passed back to the C code, which in turn places it in the formula structure.
Parameter values are used to store the variable data to be transferred between the formula and the PL/SQL. For example, the running totals are passed to the formula in this way.
The parameters can be:
Passed into the C process from the command line
Created by the driving PL/SQL procedure
Created by the formula
Only the driving PL/SQL procedure and the formula can update the values.
The PL/SQL driving procedure determines the format of the magnetic tape file. You can write this procedure from scratch by opening cursors processing a particular formula for each fetch of the cursor, or you can use the generic PL/SQL. The generic PL/SQL drives off the magnetic tape batch tables.
The interface between the 'C' process and the stored procedure makes extensive use of PL/SQL tables. PL/SQL tables are single column tables that are accessed by an integer index value. Items in the tables use indexes beginning with 1 and increasing contiguously to the number of elements. The index number is used to match items in the name and value tables.
The names of the tables used to interface with the PL/SQL procedure are:
pay_mag_tape.internal_prm_names
pay_mag_tape.internal_prm_values
pay_mag_tape.internal_cxt_names
pay_mag_tape.internal_cxt_values
The first two tables (pay_mag_tape.internal_prm_names and pay_mag_tape.internal_prm_values) are used to pass parameter details to the PL/SQL and formula. These are reserved for the number of entries in the parameter tables and the formula ID that is to be executed. The second two tables (pay_mag_tape.internal_cxt_names and pay_mag_tape.internal_cxt_values) are used to set the context rules for the database items in the formula. These are reserved for the number of entries in the context tables.
The Magnetic Tape process uses generic PL/SQL that drives off several tables that contain cursor names. These cursors and tables control the format of the magnetic tape.
These cursors retrieve three types of data:
Data that is used in subsequent cursors
Data that is to be used as context value data
Data to be held as parameter/variable data
Here are two select statements as examples:
cursor business is
select business_group_id,
'DATE_EFFECTIVE=C', effective_start_date
from per_business_groups
cursor assignment is
select 'ASSIGN_NO=P', assignment_id
from pay_assignments
In the above example, the first select (DATE_EFFECTIVE) is a context value that is passed to a subsequent formula. The business_group_id column is retrieved for use in subsequent cursors. It is accessed by using a function described later.
The second select (ASSIGN_NO=P) is used as a parameter.
When the cursor is opened, it assigns rows in a retrieval table that it can select into (the number of rows depends on the number of columns retrieved by the cursor). For example, if the above cursors were used, and the previous example was run, the retrieval table would look like this:
After First Run | After Second Run |
---|---|
50000 | 50000 |
DATE_EFFECTIVE= | DATE_EFFECTIVE=C |
16-MAR-1997 | 16-MAR-1997 |
ASSIGN_NO=P | |
50367 |
Some cursors require access to data previously selected. This can be achieved in two ways:
If the column was selected as a context or an individual column (like business group in the previous example), use the get_cursor_return function. It returns the value, given the cursor name and the column position in the select statement. For example, to get the business group in the above select statement use the following command:
pay_magtape_generic.get_cursor_return('business', 1)
Or, select the value as a parameter and access a function that retrieves that value given the parameter name. For example to get the ASSIGN_NO parameter value use the following command:
pay_magtape_generic.get_parameter_value('ASSIGN_NO')
The formula requires two types of data:
Context
Parameter
The context data is held in PL/SQL tables, which are filled by the PL/SQL with data retrieved by the cursors, as described above. The context rules are inherited to lower levels unless the lower level cursor retrieves a different value for that context name. The PL/SQL always uses the lowest level context value for a particular context. For example, if the second cursor above retrieved a context value for DATE_EFFECTIVE, this value would be used for the formula until the cursor is closed. It is at a lower level in the retrieval table than the previous DATE_EFFECTIVE. When the cursor is closed, the rows in the retrieval table are reclaimed and the DATE_EFFECTIVE context reverts to the first one.
The Parameter data is also held in tables, but unlike context values the values are not level dependent. The formula can access these values by selecting the parameter on the input line. If the formula returns a value for that parameter, it overwrites the entry in the table. If the formula returns a parameter that does not exist, the parameter is entered in the table.
The driving structure for the package procedure is held in two database tables:
PAY_MAGNETIC_BLOCKS
PAY_MAGNETIC_RECORDS (the Formula/Record table, see below)
The PAY_MAGNETIC_BLOCKS table is as follows:
Name | Null? | Type |
---|---|---|
MAGNETIC_BLOCK_ID | NOT NULL | NUMBER (9) |
BLOCK_NAME | NOT NULL | VARCHAR2 (80) |
MAIN_BLOCK_FLAG | NOT NULL | VARCHAR2 (30) |
REPORT_FORMAT | NOT NULL | VARCHAR2 (30) |
CURSOR_NAME | VARCHAR2 (80) | |
NO_COLUMN_RETURNED | NUMBER (5) |
block_id | cursor_name | block_name | no_of_select_ values | main_block | type |
---|---|---|---|---|---|
1 | company_curs | companies | 2 | Y | CA |
2 | employee_curs | employees | 2 | N | CA |
3 | assignment_curs | assignments | 1 | N | CA |
Block_id is system generated.
No_of_select_values is the number of columns retrieved by the select statement specified by cursor_name.
Main_block signifies the starting block to use. Only one of these can be set to Y for a given report.
Type refers to the type of report that the select statement represents.
The PAY_MAGNETIC_RECORDS table is as follows:
Name | Null? | Type |
---|---|---|
FORMULA_ID | NOT NULL | NUMBER (9) |
MAGNETIC_BLOCK_ID | NOT NULL | NUMBER (9) |
NEXT_BLOCK_ID | NUMBER (9) | |
LAST_RUN_EXECUTED_MODE | NOT NULL | VARCHAR2 (30) |
OVERFLOW_MODE | NOT NULL | VARCHAR2 (30) |
SEQUENCE | NOT NULL | NUMBER (5) |
FREQUENCY | NUMBER (5) |
formula_name | block_id | seq | next_block | frequency | O/F | exec.last |
---|---|---|---|---|---|---|
formula 1 | 1 | 1 | - | - | N | N |
formula 2 | 1 | 2 | 2 | - | N | N |
formula 3 | 2 | 1 | - | - | N | N |
formula 4 | 2 | 2 | 3 | - | N | N |
formula 5 | 3 | 1 | - | - | N | N |
formula 6 | 2 | 3 | - | - | N | N |
formula 7 | 1 | 3 | - | - | N | N |
Formulas/records can be of three general types:
Standard formulas executed for every row returned from cursor
Intermediate formulas executed once every x number of rows
Formula executed depending on the result of the previous formula (overflow formula)
The table columns are as follows:
Block id refers to the block that this formula is part of.
Seq refers to the sequence in the block.
Next_block column signifies that after this formula has run, the cursor defined by next_block should be opened and that block's formula should be run until there are no more rows for that cursor.
Frequency is used by the intermediate formula to specify the number of rows to be skipped before the formula is run.
O/F (overflow) specifies whether the formula is an overflow. If it is (set to Y), and if the last formula returned the TRANSFER_RUN_OVERFLOW flag set to Y, then the formula runs.
Similarly, if the formula is a Repeated overflow (set to R), and the TRANSFER_RUN_OVERFLOW flag is set to Y then that formula is continually repeated until the formula does not return TRANSFER_RUN_OVERFLOW set to Y.
Exec.last can apply to all the types of formula but most commonly the intermediate formulas. This column specifies that the formula can run one extra time after the last row has been retrieved from the cursor.
For intermediate formula this column can be set to 4 different values:
N - Never run after last row returned
A - Always run after last row returned
R - Run only if the intermediate formula has run for this cursor
F - Run only if this is the first run of the formula for this cursor
Note: For overflow and standard formula only N and A are valid.
Using the above specification the formulas could be retrieved in the following sequence:
The generic PL/SQL procedure identifies which type of report to process. It does this by passing the parameter MAGTAPE_REPORT_ID when calling the process. The previous figure illustrates how MAGTAPE_REPORT_ID=CA is passed when calling the process.
Typically, a magnetic tape consists of a number of record types. Oracle suggests having a formula associated with (generating) each record type. The formulas do the following:
Define the field positions in the records
Perform calculations
Report on the details written to tape (auditing)
Raise different levels of error messages
A PL/SQL stored procedure provides the main control flow and determines the order in which the formulas are called.
The routine uses FastFormula to prepare records The records are written to an ASCII file in preparation for transfer to magnetic tape. To implement the required actions, there are more formula result rule types. These are listed below:
Result Rule Types | Purpose |
---|---|
TRANSFER | This transfers the output parameter to the input of the stored procedure. The parameter may or may not be modified by the stored procedure before being used in the next execution of the formula. |
WRITE TO TAPE | This instructs the process to write the result to the magnetic tape file. This is always a character string that represents the desired record. The writes are performed in the order in which they are returned from the formula. |
REPORT FILE | This writes the string result to an "audit" file. |
ERROR | This instructs the process that an ERROR/WARNING has been detected within the formula. Thus the process should handle the error appropriately. |
These are not implemented in the traditional manner using the formula result rules table. They use the naming convention:
WRITE TO TAPE results are named WRITE_<result_name>.
TRANSFER results follow a similar convention, but the result_name part must be the name of the parameter. For example, a result company_total_income would be named transfer_company_total_income.
The REPORT result must identify which file is to be written to. The file number is embedded in the formula return name For example: REPORT1_<result_name> - this writes to report/audit file 1.
Reports can be written during the production of the magnetic tape file. These reports could be used to check the details that are produced. A number of reports can be created in the same run. The number can be limited by using the ADD_MAG_REP_FILES action parameter in the PAY_ACTION_PARAMETERS table.
Each report is accessed by using a prefix that denotes the file, for example, REPORT1_ to denote report number 1, REPORT2_to denote report number 2, and so on. If a report number is outside the range of the ADD_MAG_REP_FILES value, an invalid return error is reported. The report files are opened as and when needed with the names of the files previously described.
Errors can be of three types:
Payroll errors
These are identified by a return of ERROR_PAY_<error_name>.
Assignment errors
These are denoted by ERROR_ASS_<error_name>.
Warning errors
These are denoted by ERROR_WARN_<error_name>.
The actual messages themselves have to be prefixed with the assignment action id or payroll action id. This is done to insert the messages into the PAY_MESSAGE_LINES table. Warning messages are regarded as being at the assignment action level and require the assignment action id. If no id is supplied, the message is only written to the log file. No id must be supplied when running a magnetic tape report, since no actions exist for reports. Only payments have actions.
Here are some examples of the format to use:
Error | Message | Meaning |
---|---|---|
ERROR_PAY_TEXT1 | = '50122: Unexpected value' | - Payroll action id 50122 with message 'Unexpected Value' |
ERROR_PAY_TEXT1 | = ':Unexpected value' | - No payroll action id just a message |
ERROR_ASS_TEXT1 | = '56988: Unexpected value' | |
ERROR_ASS_TEXT1 | = 'Unexpected value' | |
ERROR_WARN_TEXT1 | = '56988: Unexpected value' | |
ERROR_WARN_TEXT1 | = ':Unexpected value' |
Magnetic tape either fully completes the process, or marks the whole run with a status of error.
Within this there are two types of errors:
Payroll action level errors, which are fatal
If this form of error is encountered, the error is reported and the process terminates.
Assignment action level
These can be set up in formulas and result in the error message being reported and the process continuing to run. This can be used to report on as many errors as possible during the processing so that they can be resolved before the next run.
The payroll action errors at the end of the run if assignment action level errors are encountered.
A description of the error message is written to the Log file. Also an entry is placed in the PAY_MESSAGE_LINES table if the action id is known.
The following piece of PL/SQL code could be used to format a magnetic tape payment (drives off assignment actions). An alternative to writing a PL/SQL procedure would be to use the generic procedure and populate the batch magnetic tape tables.
Note: This example only works for a business group of 'MAG Test GB' (the legislative formula is for GB only).
create or replace package body pytstm1 as CURSOR get_assignments( p_payroll_action_id NUMBER) IS SELECT ppp.org_payment_method_id,ppp.personal_payment_method_id, ppp.value, paa.assignment_id FROM pay_assignment_actions paa, pay_pre_payments ppp WHERE paa.payroll_action_id = p_payroll_action_id AND ppp.pre_payment_id = paa.pre_payment_id ORDER BY ppp.org_payment_method_id; Also need to: Test that the assignment are date effective? Order by name or person_number or other ? p_business_grp NUMBER; -- -- PROCEDURE new_formula IS -- p_payroll_action_id NUMBER; assignment NUMBER; p_org_payment_method_id NUMBER; p_personal_payment_method_id NUMBER; p_value NUMBER; -- -- FUNCTION get_formula_id ( p_formula_name IN VARCHAR2) RETURN NUMBER IS p_formula_id NUMBER; BEGIN SELECT formula_id INTO p_formula_id FROM ff_formulas_f WHERE formula_name = p_formula_name AND (business_group_id = p_business_grp OR (business_group_id IS NULL AND legislation_code = 'GB') OR (business_group_id IS NULL AND legislation_code IS NULL) ); -- RETURN p_formula_id; -- END get_formula_id; -- BEGIN -- pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS'; -- Reserved positions pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';-- -- Number of parameters may be greater than 2 because formulas may be -- keeping running totals.-- pay_mag_tape.internal_cxt_names(1) := 'Number_of_contexts'; pay_mag_tape.internal_cxt_values(1) := 1; -- Initial value---- IF NOT get_assignments%ISOPEN THEN -- New file-- pay_mag_tape.internal_prm_values(1) := 2; pay_mag_tape.internal_prm_values(2) := get_formula_id ('REPORT_HEADER_1');-- if pay_mag_tape.internal_prm_names(3) = 'PAYROLL_ACTION_ID' then p_payroll_action_id := to_number(pay_mag_tape.internal_prm_values(3)); end if;-- OPEN get_assignments (p_payroll_action_id);-- ELSE---- FETCH get_assignments INTO p_org_payment_method_id, p_personal_payment_method_id, p_value, assignment;-- IF get_assignments%FOUND THEN -- New company pay_mag_tape.internal_prm_values(1) := 2; pay_mag_tape.internal_cxt_names(2) := 'ASSIGNMENT_ID'; pay_mag_tape.internal_cxt_values(2) := assignment; pay_mag_tape.internal_cxt_names(3) := 'DATE_EARNED'; pay_mag_tape.internal_cxt_values(3) := to_char (sysdate,'DD-MON-YYYY'); pay_mag_tape.internal_cxt_values(1) := 3; pay_mag_tape.internal_prm_values(2) := get_formula_id ('ENTRY _DETAIL'); ELSE-- pay_mag_tape.internal_prm_values(1) := 2; pay_mag_tape.internal_prm_values(2) := get_formula_id ('REPORT_CONTROL_1'); CLOSE get_assignments; -- END IF; --END IF;-- END new_formula; BEGIN -- 'MAG test BG' used as an example. The business group could be -- retrieved using the payroll action id. select business_group_id into p_business_grp from per_business_groups where name = 'MAG test BG'; --END pytstm1;
Note: For ease, we refer to the Cheque Writer/Check Writer process as Cheque Writer throughout this technical essay.
You run the Cheque Writer process to produce cheque payments for unpaid pre-payment actions. Before you run the process, you need to set up certain things, for example, the SRW2 report and the 'order by' option to sequence cheques (if required).
You run Cheque Writer through Standard Reports Submission (SRS). Unlike the Magnetic Tape process, you can have multiple threads in Cheque Writer.
The Cheque Writer process has two distinct steps:
Cheque Writer creates cheque assignment actions for each of the target pre-payments, subject to the restrictions of the parameters specified. The target pre-payments must be unpaid-that is, never been paid-or if they have been paid, then voided.
Cheque Writer creates assignment actions in two stages:
Multiple threads insert ranges of assignment actions, which interlock back to previous actions.
This happens in the same way as Pre-Payments and Magnetic Tape create assignment actions.
See: The Process (Pre-Payments)
A single thread runs through all the assignment actions in a specific order to update the chunk and cheque number.
The order is specified by a PL/SQL procedure that you can customize. The thread divides the assignment actions equally into chunks, one chunk per thread. It assigns each action a cheque number.
At this stage, the status of the assignment actions is 'Unprocessed'.
Note: Cheque Writer creates an assignment action and cheque for each target pre-payment of the assignment. Consequently, a single Cheque Writer run can produce more than one cheque for a single assignment.
When Cheque Writer has created the assignment actions and interlocks, each thread submits the specified SRW2 report as a synchronously spawned concurrent process. The reports produce files in a specific cheque format.
If the spawned concurrent process is successful, the status of the assignment actions is changed to 'Complete'. If the process fails, the status of the assignment actions is changed to 'In Error'. So, if you resubmit Cheque Writer, it can start at the point of submitting the report.
In this respect, Cheque Writer is similar to the magnetic tape process: the whole process must be successful before the payroll action is Complete. But, while the Magnetic Tape process can mark individual assignment actions In Error, Cheque Writer marks all assignment actions In Error.
The batch process has a number of parameters users can enter. The definition of the printer type (for example, laser or line printer for the report output) is not a parameter. The default for this is specified as part of the registration of the concurrent process for the report. Consult your Oracle E-Business Suite System Administrator's Guide - Configuration for more information on printers and concurrent programs.
payroll_id - optional
This parameter restricts the cheques generated according to the current payroll of the assignment. It is a standard parameter to most payroll processes.
consolidation_set_id - mandatory
This parameter restricts the target pre-payments for Cheque Writer to those which are for runs of that consolidation set.
start_date - optional
This parameter specifies how far back, date effectively, Cheque Writer searches for target pre-payments. If this parameter is not specified, Cheque Writer scans back to the beginning of time.
effective_date - optional
This parameter specifies the effective date for the execution of Cheque Writer. If it is null, the effective date is taken to be the effective date held in FND_SESSIONS. If there is no such row, then it is defaulted to SYSDATE.
payment_type_id - mandatory
This parameter specifies which payment type is being paid. For UK legislation, it must be a payment type which is of payment category Cheque. For US legislation, it must be a payment type which is of payment category Check.
org_payment_method_id - optional
This parameter restricts the target prepayments to those which are for that organization payment method. It would be used where different cheque styles are required by organization payment method.
order_by_option - mandatory
This parameter specifies which order by option is called to create and order the cheque assignment actions. By providing this as a parameter, the user can specify what ordering they want to take effect for the generated cheques.
report_name - mandatory
This parameter is the name of the SRW2 report that is synchronously spawned by Cheque Writer to generate the print file of cheques and any attached pay advices, and such.
A user-extensible lookup is provided.
start_cheque_number - mandatory
This parameter specifies the contiguous range of numbers to be assigned to cheques generated.
end_cheque_number - optional
This parameter specifies the contiguous range of numbers to be assigned to cheques generated. If this parameter is specified, this range constrains how many cheque assignment actions are created. Cheque Writer is the only payroll action that does not necessarily process, what would otherwise be, all of its target actions.
If the end number is not specified, Cheque Writer assigns numbers sequentially from the start number onwards for all generated cheque assignment actions.
If cheques must be printed for different contiguous ranges (as may occur when using up the remnants of one box of cheque stationery, before opening another box), then the Cheque Writer process must be invoked separately for each contiguous range.
The cheque stationery onto which the details are printed is typically authorized, and has the cheque number preprinted on it. It is common in the UK for there to be a further cheque number box which is populated when the cheque is finally printed. It is this number that the generating payroll system uses.
Usually, these two numbers are the same. It is not known whether any clearing system invalidates the cheque if they are not. However, it seems likely that if you need to trace the path of a cheque through a clearing system, the preprinted cheque number would prove most useful, and hence, it should be the number recorded for the cheque payment on the payroll system.
It is a user's responsibility to ensure that the cheque numbers used by Cheque Writer (and recorded on the system) are identical to those on the preprinted stationery. In certain circumstances, you might want to use numbers that are not the same. In this case, the cheque number recorded by the payroll system is simply a different cheque identifier from the preprinted cheque number.
Note: Preprinted stationery usually comes in batches, for example, boxes of 10000. Therefore, you may want to use different ranges of cheque numbers when printing off cheques at the end of the pay period. For example, you may have to print off 2500 cheques using the remains of one box (numbered 9500 - 10000) and then an unopened box (numbered 20001 - 30000). Cheque Writer uses the start and end cheque number parameters to enforce these ranges.
Under some circumstances, users might need to void a cheque and optionally issue a replacement. For example, an employee loses their cheque and requests a replacement, or you discover that the employee has previously left employment and should not have been paid. In both cases the first step is to void the cheque. This activity may also involve contacting the bank that holds the source account and cancelling the cheque.
Note: Voiding a cheque does not prevent the payment from being made again.
Voiding and reissuing a cheque is different from rolling back and reprinting a cheque. You void a cheque when it has actually been issued and you need to keep a record of the voided cheque. You rollback when a cheque has not yet been issued. For example, during a print run your printer might jam on a single cheque and think it has printed more than one. These cheques have not been issued and the batch process should be rolled back and restarted for those actions.
Depending on the reason for voiding, a user may want to issue another cheque. This is known as 'reissuing'. This requires no extra functionality. The user has the choice of issuing a manual cheque and recording the details online, or of resubmitting the batch process for automatic printing.
You cannot reprocess actions that have already been paid. The process only creates payments for those actions that have never been paid, or have been voided.
Cheque Writer actions can be marked for retry. As with the rollback process, when marking a Cheque Writer payroll action for retry, the user can determine which assignment actions are to be marked by specifying an assignment set parameter.
Marking cheque assignment actions for retry does not remove the assignment actions, but simply updates their status to 'Marked For Retry' (standard behavior for all action types). The assigned cheque numbers are left unaltered. Hence, on retry, Cheque Writer generates a new print file.
The reason for this is that we cannot reassign cheque numbers for assignment actions of a cheque payroll action. The payroll action stores the start and end cheque numbers specified. If different ranges of numbers could be used on several retries of the payroll action, then some of its assignment actions could be assigned numbers outside the range held on the payroll action.
If a user wants to assign new cheque numbers, they must rollback the Cheque Writer payroll and assignment actions, and submit a separate batch request.
Note: It usually makes sense to roll back all of the cheques. If you mark individual cheques for retry, their cheque numbers are unlikely to be contiguous and it would be difficult to print these on the correct preprinted cheque stationery.
You may need to set up the format for the cheque stationery. The SRW2 report, invoked by Cheque Writer is passed in two parameters:
payroll_action_id (of the cheque action)
chunk number (to be processed)
For this purpose, the report must take the parameters named PACTID and CHNKNO.
By the time the report is run, the appropriate assignment actions have been created and cheque numbers assigned according to the order specified in the order by parameter.
The report must drive off the assignment actions for the cheque payroll action and chunk number specified. It must generate one cheque for each assignment action. The cheque number is held directly on the assignment action, while the amount to be paid is retrieved from the associated pre-payment.
The report must maintain the order of the cheques when printed out, the report must process the assignment actions in order of cheque number.
The following select statement illustrates how to drive a report:
select to_number(ass.serial_number),
ass.assignment_action_id,
round(ppa.value,2),
ppf.last_name,
ppf.first_name
from per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions ass,
pay_pre_payments ppa
where ass.payroll_action_id =:PACTID
and ass.chunk_number =:CHNKNO
and ppa.pre_payment_id = ass.pre_payment_id
and ass.assignment_id = paf.assignment_id
and ass.status <>'C'
and paf.person_id = ppf.person_id
order by to_number(ass.serial_number)
Once the SRW2 report is written, you must register it as a Cheque Writer report. This is similar to registering 'Cash Analysis Rules' for the Pre-Payments process.
You must also define a new Lookup Value for the Type of 'CHEQUE_REPORT'. Enter the report name and description.
In a similar way to the Magnetic Tape process, the file generated by the report is named:
p<trunc(conc_request_id,5)>.c<chunk_number>
The file name is padded with zeros if the length of the request id is shorter than five characters, for example, p03451.cl.
It is written to the $APPLCSF/$APPLOUT directory, if $APPLCSF is defined, and otherwise to $PAY_TOP/$APPLOUT.
If Cheque Writer is run with multiple threads, it produces several files. This is because Cheque Writer assignment actions are split into several chunks, one chunk per thread. So, each thread can pick a chunk and process it. This is done to improve performance on machines with multiple processors. For example, if there are four threads processing, there would be four files produced:
p03451.c1
p03451.c2
p03451.c3
p03451.c4
Cheque Writer creates a fifth file (by the process that concatenates the four files into one). The name of this file is p03451.ch.
Cheque Writer updates the assignment actions with the cheque and chunk number in the sequence determined by a PL/SQL procedure, called anonymously from the process. A default PL/SQL procedure is provided with the generic product - pay_chqwrt_pkg.chqsql.
The default sort order is:
Organization
Department
Surname
First name
You can change this procedure to set up several different sorting orders by criteria, denoted by a flag passed to the procedure. You should copy the core select statement, and alter the subquery to order according to your own business needs.
The advantage of giving access to the whole SQL statement is that the cheques can be ordered by any criteria. If we had only allowed specification of an ORDER BY clause, then the ordering would have been restricted to attributes on those tables already in the FROM clause of the core SQL statement.
To set up new order by requirements, change the pay_chqwrt_pkg.chqsql package procedure. You could add the following IF statement when checking the procname variable:
else if procname = 'NEW ORDER BY' then
sqlstr := 'select ....'
The select statement could be a copy of the existing select statement but with the order by clause changed. The select statement must return the assignment action's rowid.
Based on this information the assignment action can be given a serial/cheque number and assigned to a chunk.
Similarly, as with the SRW2 report the new order by option has to be registered before it can be used. This is done in a similar manner except that the Lookup Type is CHEQUE PROCEDURE. Enter a meaningful description in the Meaning field and the name of the option, for example NEW ORDER BY, in the Description field.
The Cash process indicates to the system that payment has been made, and prevents pre-payments from being rolled back.
Note: This is a UK-only process.