This technical essay explains:
Which existing payment processes you can enable for XML output
Which tables support XML enhancement of the payment processes
What predefined data Oracle HRMS delivers for each process
What extra data each localization team must add to the predefined data
You can enable XML enhancements for each of these payment processes:
Magtape
Cheque/Check Writer
Archiver
Postal
For each process, you can produce output in each of these formats:
XML
RTF
EFT
Each of your XML-enabled payment processes operates as follows:
Creates assignment actions for the payment type, or according to the assignment_action creation code
Creates assignment_action level action xml according to the xml generation procedure
Creates report_level xml according to xml generation procedures and by joining together the assignment action fragments
Applies report templates to report_level to produce formatted output
The following tables support XML enhancements of the payroll processes.
Use this table to group together all possible definitions for your report. You must specify a report_group_name and a short_name. Also, if any of the rows in this table correspond to rows in PAY_REPORT_FORMAT_MAPPINGS, ensure that you make an equivalent entry in PAY_REPORT_FORMAT_MAPPINGS.
Use the thread level and qualifying procedure together with the archiver (generic report) to create assignment actions.
The ASG_MAGNETIC_BLOCK_ID is the magnetic block that produces the XML for the assignment level.
| Row | Can be NULL? | Format |
|---|---|---|
| REPORT_GROUP_ID | NOT NULL | NUMBER |
| REPORT_FORMAT_MAPPING_ID | Can be NULL | NUMBER |
| REPORT_GROUP_NAME | NOT NULL | VARCHAR2(80) |
| SHORT_NAME | NOT NULL | VARCHAR2(30) |
| REPORT_ON_FULL_XML | Can be NULL | VARCHAR2(5) |
| LEGISLATION_CODE | Can be NULL | VARCHAR2(240) |
| BUSINESS_GROUP_ID | Can be NULL | NUMBER |
| REPORT_LEVEL | Can be NULL | VARCHAR2(10) |
| LAST_UPDATED_BY | Can be NULL | NUMBER (15) |
| LAST_UPDATE_DATE | Can be NULL | DATE |
| LAST_UPDATE_LOGIN | Can be NULL | NUMBER (15) |
| CREATED_BY | Can be NULL | NUMBER (15) |
| CREATION_DATE | Can be NULL | DATE |
| OBJECT_VERSION_NUMBER | Can be NULL | NUMBER (9) |
| THREAD_LEVEL | Can be NULL | VARCHAR2(30) |
| QUALIFYING_PROCEDURE | Can be NULL | VARCHAR2(250) |
| ASG_MAGNETIC_BLOCK_ID | Can be NULL | NUMBER (9) |
You can define many report definitions for each report group.
You can choose PDF, EFT or RTF as the report type.
You can choose P (Person), or A (Assignment) for the report level. If you choose P, the templates for this definition apply to the finished report. If you choose A, then the templates apply to each assignment fragment.
The MAGNETIC_BLOCK_ID is the magnetic block for the report level XML, (the headers and footers).
Set the ALTERABLE column to Y or N to determine whether you can update this data, and any child data.
The application short name is the application against which your definition templates are registered..
| Row | Can be NULL? | Format |
|---|---|---|
| REPORT_DEFINITION_ID | NOT NULL | NUMBER(15) |
| REPORT_GROUP_ID | NOT NULL | NUMBER(15) |
| REPORT_NAME | NOT NULL | VARCHAR2(80) |
| REPORT_TYPE | NOT NULL | VARCHAR2(30) |
| REPORT_LEVEL | NOT NULL | VARCHAR2(3) |
| MAGNETIC_BLOCK_ID | NOT NULL | NUMBER(15) |
| ALTERABLE | NOT NULL | VARCHAR2(1) |
| LAST_UPDATED_BY | Can be NULL | NUMBER (15) |
| LAST_UPDATE_DATE | Can be NULL | DATE |
| LAST_UPDATE_LOGIN | Can be NULL | NUMBER (15) |
| CREATED_BY | Can be NULL | NUMBER (15) |
| CREATION_DATE | Can be NULL | DATE |
| OBJECT_VERSION_NUMBER | Can be NULL | NUMBER (9) |
| APPLICATION_SHORT_NAME | Can be NULL | VARCHAR2(30) |
The PAY_REPORT_VARIABLES table contains all the valid templates for a report definition.
Your definition type in the DEFINITION_TYPE column should always be SS.
Ensure that:
The name in the NAME column is the name that you have used to refer to the variable.
The value entered in the VALUE column is the same value code by which the style sheet is registered in XML Publisher.
| Row | Can be NULL? | Format |
|---|---|---|
| REPORT_VARIABLE_ID | NOT NULL | NUMBER(15) |
| REPORT_DEFINITION_ID | NOT NULL | NUMBER(15) |
| DEFINITION_TYPE | NOT NULL | VARCHAR2(10) |
| NAME | NOT NULL | VARCHAR2(80) |
| VALUE | NOT NULL | VARCHAR2(80) |
| LEGISLATION_CODE | Can be NULL | VARCHAR2(240) |
| BUSINESS_GROUP_ID | NOT NULL | NUMBER(15) |
| LAST_UPDATED_BY | Can be NULL | NUMBER (15) |
| LAST_UPDATE_DATE | Can be NULL | DATE |
| LAST_UPDATE_LOGIN | Can be NULL | NUMBER (15) |
| CREATED_BY | Can be NULL | NUMBER (15) |
| CREATION_DATE | Can be NULL | DATE |
| OBJECT_VERSION_NUMBER | Can be NULL | NUMBER (9) |
The PAY_REPORT_CATEGORIES table indicates which report group you want to run. The information that you define in this table enables your process to select the correct report group id.
| Row | Can be NULL? | Format |
|---|---|---|
| REPORT_CATEGORY_ID | NOT NULL | NUMBER(15) |
| REPORT_GROUP_ID | NOT NULL | NUMBER(15) |
| CATEGORY_NAME | NOT NULL | VARCHAR2(80) |
| SHORT_NAME | NOT NULL | VARCHAR2(30) |
| LEGISLATION_CODE | Can be NULL | VARCHAR2(2) |
| BUSINESS_GROUP_ID | Can be NULL | NUMBER(15) |
| LAST_UPDATED_BY | Can be NULL | NUMBER (15) |
| LAST_UPDATE_DATE | Can be NULL | DATE |
| LAST_UPDATE_LOGIN | Can be NULL | NUMBER (15) |
| CREATED_BY | Can be NULL | NUMBER (15) |
| CREATION_DATE | Can be NULL | DATE |
| OBJECT_VERSION_NUMBER | Can be NULL | NUMBER (9) |
The PAY_REPORT_CATEGORY_COMPONENTS table defines the report definitions and templates that a report category can use. You define this information in the STYLE_SHEET_VARIABLE_ID column which then refers back to the REPORT_VARIABLE_ID column in the PAY_REPORT_VARIABLES table.
| Row | Can be NULL? | Format |
|---|---|---|
| REPORT_CATEGORY_COMP_ID | NOT NULL | NUMBER(15) |
| REPORT_CATEGORY_ID | NOT NULL | NUMBER(15) |
| REPORT_DEFINITION_ID | NOT NULL | NUMBER(15) |
| BREAKOUT_VARIABLE_ID | NOT NULL | NUMBER(15) |
| STYLESHEET_VARIABLE_ID | Can be NULL | NUMBER(15) |
| BUSINESS_GROUP_ID_ID | Can be NULL | NUMBER(15) |
| LAST_UPDATED_BY | Can be NULL | NUMBER (15) |
| LAST_UPDATE_DATE | Can be NULL | DATE |
| LAST_UPDATE_LOGIN | Can be NULL | NUMBER (15) |
| CREATED_BY | Can be NULL | NUMBER (15) |
| CREATION_DATE | Can be NULL | DATE |
| OBJECT_VERSION_NUMBER | Can be NULL | NUMBER (9) |
Use the PAY_MAGNETIC_BLOCKS table in conjunction with the PAY_MAGNETIC_RECORDS table to define the structure of your report.
In PAY_MAGNETIC_BLOCKS there are two levels of blocks:
The assignment level defines the XML generation for each assignment action.
The parent level defines the entire report (headers and footers and so on).
You can define many magnetic records for each magnetic block. The sequence of the records indicates the order of execution.
Give a value to the report level so that you group together the blocks for this report at assignment and report level.
Set the MAIN_BLOCK_FLAG to Y for the first block to be used.
The CURSOR_NAME column is used to return all rows to be processed, and to retrieve any parameter information.
The NO_COLUMN_RETURNED column indicate the number of columns that the cursor selects.
| Row | Can be NULL? | Format |
|---|---|---|
| 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 | Can be NULL | VARCHAR2(80) |
| NO_COLUMN_RETURNED | Can be NULL | NUMBER(5) |
Set the FORMULA_ID to -9999. (It is not used here).
Enter a value of 'A' for the magnetic record that retrieves the assignment_level_fragments of XML.
The XML_PROC_NAME column holds the name of the PLSQL that generates your XML.
| Row | Can be NULL? | Format |
|---|---|---|
| FORMULA_ID | NOT NULL | NUMBER(9) |
| MAGNETIC_BLOCK_ID | NOT NULL | NUMBER(9) |
| NEXT_BLOCK_ID | Can be NULL | NUMBER(9) |
| OVERFLOW_MODE | NOT NULL | VARCHAR2(30) |
| SEQUENCE | NOT NULL | NUMBER(5) |
| FREQUENCY | Can be NULL | NUMBER(5) |
| LAST_RUN_EXECUTED_MODE | NOT NULL | VARCHAR2(30) |
| ACTION_LEVEL | NOT NULL | VARCHAR2(30) |
| BLOCK_LABEL | Can be NULL | VARCHAR2(30) |
| BLOCK_ROW_LABEL | Can be NULL | VARCHAR2(30) |
| XML_PROC_NAME | Can be NULL | VARCHAR2(256) |
The PAY_REPORT_MAGNETIC_PROCEDURES table provides a hook that enables localizations to add localization specific XML to the standard XML generation procedures delivered in core. However, we recommend that localizations should always contact Core Payroll Development before attempting to create localized XML. Wherever possible, we recommend that you make use of the standard core procedures.
If you do use this table, it operates by creating a procedure name for any magnetic record, and then calling that same procedure from within the core generation procedure pypayxml.pkb. A magnetic record is always uniquely identified by magnetic block id, and sequence.
| Row | Can be NULL? | Format |
|---|---|---|
| REPORT_MAGNETIC_PROCEDURE_ID | NOT NULL | NUMBER(15) |
| MAGNETIC_BLOCK_ID | NOT NULL | NUMBER(15) |
| SEQUENCE | NOT NULL | NUMBER(15) |
| REPORT_GROUP_ID | NOT NULL | NUMBER(15) |
| LEGISLATION_CODE | Can be NULL | VARCHAR2(150) |
| PROCEDURE_NAMEL | NOT NULL | VARCHAR2(250) |
If you are running from Archiver, insert a row in this table too.
You need a PLSQ procedure referenced from the PAY_MAGNETIC_RECORDS that generate the XML. You also need a package header that contains the cursors referenced in the magnetic block.
Your template, required to format the XML, must be registered with XML Publisher.
Again, however, we strongly recommend that you contact Core Payroll Development if you are planning to modify the standard core approach.
You can use the pypayxml.pkb plsql package to produce XML for the payment processes.
Pypayxml.pkb enables you to produce generic xml output. localization teams can use this generic output to create their own templates.
We strongly recommend that you use this core package to create all your XML tags. If you require extra information, please contact Core Payroll.
To pay people using the Postal process, you must have a payment type with the payment category 'PP'. Provided that the correct payment methods exist for all assignments paid by the Postal Process, each assignment produces an XML file, and an output file formatted according to the data that you set up in your tables.
This data in these tables is predefined data, delivered in Core Payroll:
PAY_REPORT_GROUPS (for the Postal process)
| Column | Entries |
|---|---|
| REPORT_GROUP_NAME | 'XML_PAYMENT' |
| SHORT_NAME | 'XML_PAYMENT' |
| ASG_MAGNETIC_BLOCK_ID | Block id for magnetic block, where name is 'ASG_XML_PAYMENT_DETAILS' and report_format is 'XML_PAYMENT_ACT' |
PAY_REPORT_DEFINITIONS (for the Postal process)
There are two report definitions:
The first table defines how to produce the standard XML output for all XML payment processes.
The second table groups assignments by source bank details.
| Column | Entries |
|---|---|
| REPORT_GROUP_ID | Report group id, where report_group_name is 'XML_PAYMENT' |
| REPORT_NAME | 'Bank or Post Office payment report (pdf) |
| REPORT_TYPE | 'PDF' |
| MAGNETIC_BLOCK_ID | Block id for magnetic block, where name is 'XML_PAYMENT_HF' and report format is 'XML_PAYMENT' |
| ALTERABLE | 'Y' |
| REPORT_LEVEL | 'P' |
| Column | Entries |
|---|---|
| REPORT_GROUP_ID | Report group id, where report_group_name is 'XML_PAYMENT' |
| REPORT_NAME | 'Bank or Post Office payment report (pdf) Bank Group' |
| REPORT_TYPE | 'PDF' |
| MAGNETIC_BLOCK_ID | Block id for magnetic block, where name is 'XML_PAYMENT_HF' and report format is 'XML_PAYMENT_BANL_GROUP'' |
| ALTERABLE | 'Y' |
| REPORT_LEVEL | 'P' |
PAY_MAGNETIC_BLOCKS (Standard)
This table supports the report definition of standard XML output used by all XML payment processes:
| Column | Entries |
|---|---|
| BLOCK_NAME | XML_PAYMENT_HF |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT |
| CURSOR_NAME | pay_payment_xml_pkg.c_header_footer |
| NO_COLUMN_RETURNED | 4 |
| BLOCK_NAME | XML_PAYMENTS |
| MAIN_BLOCK_FLAG | N |
| REPORT_FORMAT | XML_PAYMENT |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_asg_actions |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | ASG_XML_PAYMENT_DETAILS |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT_ASG |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_details |
| NO_COLUMN_RETURNED | 20 |
PAY_MAGNETIC_RECORDS (Standard)
This table also supports the report definition of standard XML output used by all XML payment processes:
| Column | Entries |
|---|---|
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | Block id for block name XML_PAYMENTS |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_header_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_footer_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENTS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| ACTION_LEVEL | A |
| XML_PROC_NAME | null |
| MAGNETIC_BLOCK_ID | Block id for block name ASG_XML_PAYMENT_DETAILS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_payment_details_xml |
PAY_MAGNETIC_BLOCKS (Group by Source Bank)
This table supports those report definitions that have assignments grouped by source bank details:
| Column | Entries |
|---|---|
| BLOCK_NAME | XML_PAYMENT_HF |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT_BANK_GRP |
| CURSOR_NAME | pay_payment_xml_pkg.c_header_footer |
| NO_COLUMN_RETURNED | 4 |
| BLOCK_NAME | XML_PAYMENT_BANK_DET |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT_BANK_GRP |
| CURSOR_NAME | pay_payment_xml_pkg.c_bank_act_grp |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | XML_PAYMENTS |
| MAIN_BLOCK_FLAG | N |
| REPORT_FORMAT | XML_PAYMENT_BANK_GRP |
| CURSOR_NAME | pay_payment_xml_pkg.c_bank_asg_actions |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | ASG_XML_PAYMENT_DETAILS |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT_ASG |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_details |
| NO_COLUMN_RETURNED | 20 |
PAY_MAGNETIC_RECORDS (Group by Source Bank Name)
This table also supports those report definitions that have assignments grouped by source bank details:
| Column | Entries |
|---|---|
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | Block id for block name XML_PAYMENT_BANK_DET |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_header_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_footer_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_BANK_DET |
| NEXT_BLOCK_ID | Block id for block name XML_PAYMENTS |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_bank_footer_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_BANK_DET |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_footer_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENTS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| ACTION_LEVEL | A |
| XML_PROC_NAME | null |
| MAGNETIC_BLOCK_ID | Block id for block name ASG_XML_PAYMENT_DETAILS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_payment_details_xml |
PLSQL Package
pay_payment_xml_pkg.pypayxml.pkb - generates XML
pay_payment_xml_pkg.pypayxml.pkh - cursors
SRS Definition
The command line for this process is:
PYUGEN apps/apps 0 Y POSTAL <payroll id> consolidation_set_id <start_date> end_date payment_type_id <payment_method> <action_parameter_group> REP_GROUP=XML_PAYMENT REP_CAT=<category short_name>
There is an SRS screen with a program name of Bank or Post Office Payment, and a short name of PP_PAYMENT.
Supply the following localized data to enable the Postal process:
In the PAY_REPORT_VARIABLES table, add a row to define which templates are valid to format the XML for this report definition.
In the PAY_REPORT_CATEGORIES table, define a row to indicate which report_group you want to process.
In the PAY_REPORT_CATEGORY_COMPONENTS table, define a row to specify which templates are to be processed in the report category.
In the PAY_REPORT_MAGNETIC_PROCEDURES table, you can define a row to append localization specific data to your blob file. The XML being generated is defined in pypayxml.pkb
Append these two core procedures to the blob:
pay_core_files.write_to_magtae_lob(p_text in varchar2) or
pay_core_files.write_to_magtae_lob(p_data_in blob)
For the magnetic tape process:
You can reuse the core data supplied with the Postal process.
You must define a template even if you reuse the core data.
Remember that assignment actions already exist for assignments with this payment method. You created them by formula when you ran the magnetic tape processes.
PAY_REPORT_GROUPS (for Magnetic Tape Process)
| Row | Format |
|---|---|
| REPORT_GROUP_NAME | 'XML_PAYMENT' |
| SHORT_NAME | 'XML_PAYMENT' |
| ASG_MAGNETIC_BLOCK_ID | Block id for magnetic block where name is 'ASG_XML_PAYMENT_DETAILS' and report format is 'XML_PAYMENT_ACT' |
PAY_REPORT_DEFINITIONS (for Magnetic Tape Process)
| Row | Format |
|---|---|
| REPORT_GROUP_ID | Report group id where report_group_name is 'XML_PAYMENT' |
| REPORT_NAME | Bank or Post Office payment report (pdf) |
| REPORT_TYPE | 'PDF' |
| MAGNETIC_BLOCK_ID | Block id for magnetic block where name is 'XML_PAYMENT_HF' |
| ALTERABLE | 'Y' |
| REPORT_LEVEL | 'P' |
| REPORT_GROUP_ID | Report group id where report_group_name is 'XML_PAYMENT' |
| REPORT_NAME | Bank or Post Office payment report (xml) |
| REPORT_TYPE | 'XML' |
| MAGNETIC_BLOCK_ID | Block id for magnetic block where name is 'XML_PAYMENT_HF' |
| ALTERABLE | 'Y' |
| REPORT_LEVEL | 'P' |
| REPORT_GROUP_ID | Report group id where report_group_name is 'XML_PAYMENT' |
| REPORT_NAME | Bank or Post Office payment report (EFT) |
| REPORT_TYPE | 'EFT' |
| MAGNETIC_BLOCK_ID | Block id for magnetic block where name is 'XML_PAYMENT_HF' |
| ALTERABLE | 'Y' |
| REPORT_LEVEL | 'P' |
PAY_MAGNETIC_BLOCKS (for Magnetic Tape Process)
| Row | Format |
|---|---|
| BLOCK_NAME | XML_PAYMENT_HF |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT |
| CURSOR_NAME | pay_payment_xml_pkg.c_header_footer |
| NO_COLUMN_RETURNED | 4 |
| BLOCK_NAME | XML_PAYMENTS |
| MAIN_BLOCK_FLAG | N |
| REPORT_FORMAT | XML_PAYMENT |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_asg_actions |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | ASG_XML_PAYMENT_DETAILS |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT_ASG |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_details |
| NO_COLUMN_RETURNED | 20 |
PAY_MAGNETIC_RECORDS (for Magnetic Tape Process)
| Row | Format |
|---|---|
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | Block id for block name XML PAYMENTS |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_header_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_footer_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENTS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| ACTION_LEVEL | A |
| XML_PROC_NAME | null |
| MAGNETIC_BLOCK_ID | Block id for block name ASG_XML_PAYMENT_DETAILS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_payment_details_xml |
The Cheque Writer process creates assignment actions for each of the prepayments that have a payment type of CHEQUE. Cheque Writer then processes these assignment actions and produces an XML file, and a PDF document containing cheques.
This data in these tables is predefined data, delivered in Core payroll:
PAY_REPORT_GROUPS
| Column | Entries |
|---|---|
| REPORT_GROUP_NAME | 'XML_PAYMENT' |
| SHORT_NAME | 'XML_PAYMENT' |
| ASG_MAGNETIC_BLOCK_ID | Block id for the magnetic block where the name is 'ASG_XML_PAYMENT_DETAILS' and the report format is 'XML_PAYMENT_ACT' |
PAY_REPORT_DEFINITIONS
| Column | Entries |
|---|---|
| REPORT_GROUP_ID | Report group id, where report_group_name is 'XML_PAYMENT' |
| REPORT_NAME | 'Bank or Post Office payment report (pdf) |
| REPORT_TYPE | 'PDF' |
| MAGNETIC_BLOCK_ID | Block id for magnetic block, where name is 'XML_PAYMENT_HF' and report format is 'XML_PAYMENT' |
| ALTERABLE | 'Y' |
| REPORT_LEVEL | 'P' |
PAY_MAGNETIC_BLOCKS
| Column | Entries |
|---|---|
| BLOCK_NAME | XML_PAYMENT_HF |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT |
| CURSOR_NAME | pay_payment_xml_pkg.c_header_footer |
| NO_COLUMN_RETURNED | 4 |
| BLOCK_NAME | XML_PAYMENTS |
| MAIN_BLOCK_FLAG | N |
| REPORT_FORMAT | XML_PAYMENT |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_asg_actions |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | ASG_XML_PAYMENT_DETAILS |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | XML_PAYMENT_ASG |
| CURSOR_NAME | pay_payment_xml_pkg.c_payment_details |
| NO_COLUMN_RETURNED | 20 |
PAY_MAGNETIC_RECORDS
| Column | Entries |
|---|---|
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | Block id for block name XML_PAYMENTS |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_header_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENT_HF |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_footer_xml |
| MAGNETIC_BLOCK_ID | Block id for block name XML_PAYMENTS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| ACTION_LEVEL | A |
| XML_PROC_NAME | null |
| MAGNETIC_BLOCK_ID | Block id for block name ASG_XML_PAYMENT_DETAILS |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_payment_xml_pkg.gen_payment_details_xml |
PLSQL Package
pay_payment_xml_pkg.pypayxml.pkb - generates XML
pay_payment_xml_pkg.pypayxml.pkh - cursors
PAY_REPORT_VARIABLES
| Column | Entries |
|---|---|
| REPORT_DEFINITION_ID | Report definition id, where report name is 'Bank or Post Office payment report (PDF) |
| DEFINITION_TYPE | SS |
| NAME | PAYMENT_SS |
| VALUE | CHQGEN |
PAY_REPORT_CATEGORIES
| Column | Entries |
|---|---|
| REPORT_GROUP_ID | Report group id, where report name is 'XML_PAYMENT' |
| CATEGORY_NAME | 'CHEQUE_WRITER' |
| SHORT_NAME | 'CHEQUE_WRITER' |
PAY_REPORT_CATEGORY_COMPONENTS
| Column | Entries |
|---|---|
| REPORT_CATEGORY_ID | Report category id where category_name is 'CHEQUE_WRITER' |
| REPORT_DEFINITION_ID | Report definition id where report name is 'Bank or Post Office Payment report (PDF)' |
| STYLE_SHEET_VARIABLE_ID | Report variable id where value is 'CHQGEN' |
Details of XDO Register
Registered with XML Publisher, with a name of CHEQUE_GEN, code CHQGEN, application payroll, data definition chqgen, and type RTF.
SRS Definition
The command line for the generic Cheque Writer process is:
PYUGEN apps/apps 0 Y CHEQUE <> <>
PYUGEN apps/apps 0 Y CHEQUE <payroll id> consolidation_set_id <start_date>end_date payment_type_id <payment_method> sort_sequence "" Start_Cheque_Number <end_cheque_Number><action_parameter_group> REP_GROUP=XML_PAYMENT REP_CAT=CHEQUE_WRITER
There is an SRS screen with the program name of Cheque Writer (Generic) and a short name of CHEQUE_WRITER_GEN
Supply the following localized data to enable the Cheque Writer (Generic) process:
In the PAY_REPORT_MAGNETIC_PROCEDURES table, where the XML generated is defined in pypayxml.pkb, you can define a row to append localization specific data to your blob file.
There are two core procedures to use to append localized data:
pay_core_files.write_to_magtape_lob(p_text in varchar2) or
pay_core_files.write_to_magtape_lob(p_data in blob)
To use a localized template, define rows in PAY_REPORT_VARIABLES, PAY_REPORT_CATEGORIES, and PAY_REPORT_CATEGORY_COMPONENTS.
Your Database Administrator must complete each of these setup steps to enable the Cheque Writer (Generic) Process:
Set the environment variable PY_LIB_PATH so that it points to the location of libjava.so, and the corresponding sub directories.
Set up a pay_action_parameter REPORT_URL with a value of url for the concurrent output files.
If the dbc file is not stored in the default location of $FND_TOP/secure/ then set up a pay_action _parameter DBC_FILE with the path and name of the dbc file.
The Archiver process creates assignment actions, produces XML and formatted output in accordance with the data set up in the tables. You create an assignment action by defining a thread level (PER.ASG) and a qualifying procedure in the report group. The qualifying procedure takes an object id, and returns Y/N to control which assignment actions are created.
Magnetic blocks and records have been defined, with a consistent structure of three sections (Header, Asg Action Data, Footer). Each of these sections calls pay_mag_tape.call_leg_xml_proc. This procedure then calls any further procedures that localizations have defined in pay_report_magnetic_procedures, and these further procedures write the XML into each of the three sections.
This data in these tables is predefined data, delivered in Core payroll:
PAY_MAGNETIC_BLOCKS
| Column | Entries |
|---|---|
| BLOCK_NAME | GENERIC_REPORT_HF |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | GENERIC_REPORT |
| CURSOR_NAME | pay_mag_tape.c_header_footer |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | GENERIC_REPORT_AA |
| MAIN_BLOCK_FLAG | N |
| REPORT_FORMAT | GENERIC_REPORT |
| CURSOR_NAME | pay_mag_tape.c_asg_actions |
| NO_COLUMN_RETURNED | 2 |
| BLOCK_NAME | GENERIC_REPORT_ASG_XML |
| MAIN_BLOCK_FLAG | Y |
| REPORT_FORMAT | GENERIC_REPORT_ACT |
| CURSOR_NAME | pay_mag_tape.c_asg_actions |
| NO_COLUMN_RETURNED | 2 |
PAY_MAGNETIC_RECORDS
| Column | Entries |
|---|---|
| MAGNETIC_BLOCK_ID | Block id for block name GENERIC_REPORT_HF |
| NEXT_BLOCK_ID | Block id for block name GENERIC_REPORT_AA |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_mag_tape.call_leg_xml_proc_xml |
| MAGNETIC_BLOCK_ID | Block id for block name GENERIC_REPORT_HF |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| XML_PROC_NAME | pay_mag_tape.call_leg_xml_proc_xml |
| MAGNETIC_BLOCK_ID | Block id for block name GENERIC_REPORT_AA |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 2 |
| ACTION_LEVEL | A |
| XML_PROC_NAME | null |
| MAGNETIC_BLOCK_ID | Block id for block name GENERIC_REPORT_ASG_XML |
| NEXT_BLOCK_ID | null |
| SEQUENCE | 1 |
| XML_PROC_NAME | pay_mag_tape.call_leg_xml_proc_xml |
PAY_REPORT_FORMAT_MAPPINGS_F
| Column | Entries |
|---|---|
| REPORT_TYPE | GENERIC_REPORT |
| REPORT_QUALIFIER | DEFAULT |
| REPORT_CATEGORY | REPORT |
| RANGE_CODE | pay_generic_upgrade.range_cursor |
| ASSIGNMENT_ACTION_CODE | pay_generic_upgrade.action_creation |
| INITIALIZATION_CODE | pay_generic_upgrade.archinit |
| MAGNETIC_CODE | pay_magtape_generic.new_formula |
| REPORT_FORMAT | DEFAULT |
| DEINITIALIZATION_CODE | pay_generic_upgrade.deinitialise |
| TEMPORARY_ACTION_FLAG | Y |
| DISPLAY_NAME | Generic Report |
To enable the Archiver process for localized data, modify these tables as follows:
In the PAY_REPORT_GROUPS table, define the report group used for this set of reports. Include the thread level, and a qualifying procedure for the assignment action creation.
In the PAY_REPORT_DEFINITIONS table, define a row for each report definition in this group.
In the PAY_REPORT_VARIABLES table, add a row to define which templates are valid to format the XML for this report definition. Ensure that your templates are registered with XML Publisher.
In the PAY_REPORT_CATEGORIES table define a row to indicate which report group is processed.
In the PAY_REPORT_CATEGORY_COMPONENTS table, define a row for each template processed in this report category.
In the PAY_REPORT_MAGNETIC_PROCEDURES table, create a row for each section of XML to be created. Use these core procedures to append localized data to your blob file:
pay_core_files.write_to_magtape_lob(p_text in varchar2) or
pay_core_files.write_to_magtape_lob(p_data in blob)
Use the following syntax to run from the command line:
PYUGEN apps/apps 0 Y ARCHIVE GENERIC REPORT DEFAULT "1990/01/01 00:00:00" "1990/03/31 00:00:00" REPORT BUSINESS_GROUP_ID "" "" "" "" "" REP_GROUP=<report_group short name> REP_CAT=<report_category short_name>
For localizations, there are additional setup steps for:
Bank information tags
Earnings, deductions and accruals
File creation number
Your bank information tags, for source and destination banks, are:
BANK_NAME
BRANCH_NAME
ACCOUNT_NAME
ACCOUNT_NUMBER
TRANSIT_CODE
This function retrieves the information tags:
pay ce support pkg.bank segment value (<external account id>,
<segment name>,
<leg code>);
To set up bank information tags:
Set up lookup codes for each of the information tags and specify the segment name in which the segment value is held
To set up earnings, deductions and accruals:
Set the ADDITIONAL_CHQ_DATA legislation rule to Y
Ensure that you have applied all the prerequisite patches. The hrglobal patch contains the required balance attribute definitions, PAYMENT_EARNINGS, and PAYMENT_DEDUCTIONS.
Define a row in the PAY_BALANCE_ATTRIBUTES table for every earning or deduction that you want to output.
To set up your file creation number:
Create a procedure named pay_[leg_code]_rules.get_file_creation_no(pactid in number, file_no out number);
Set the XML_FILE_CREATION_NO legislation rule to Y
This section explains how to identify and resolve XML setup errors:
HR_6882_HRPROC_ASSERT
LOCATION pyjavals: 1
APP-PAY-06882:
Assertion failure detected at location pyjavals:1
Cause: An internal error has occurred at location pyjavals:1
Fix: Create a row in pay_action_parameters, so that the parameter_name=JRE_LIBRARY, and parameter_value=location of libjava.so. (For example, in our development instance, the location is /local/java/jre1.1.8/lib/i386/native_threads).
HR_6882_HRPROC_ASSERT
LOCATION pyjavals: 2
APP-PAY-06882:
Assertion failure detected at location pyjavals:2
Cause: An internal error has occurred at location pyjavals:2
The log file also contains the message:
symbol errno, version GLIBC 2.0 not defined in file libc.so,6 with link time reference (libzip.so)
Unable to initialize threads; cannot find class java/lang/Thread
Fix: Set the environment variable LD_ASSUME_KERNEL to 2.4.19. Then restart the concurrent manager.
HR_6882_HRPROC_ASSERT
LOCATION pyjavacom:2
APP-PAY-06882:
Assertion failure detected at location pyjavacom:2
Cause: An internal error has occurred at location pyjavacom:2
The log file also contains the message:
java.lang.NullPointerException
at java.io.File.<init>(File.java)
at oracle.apps.xdo.oa.util.ConfigHelper.getFontProperties(ConfigHelper.java:80)
at
oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5855)
at
oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3555)
at
oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3614)
Fix: Log in to the XML Publisher responsibility, navigate to the Administration screen, and set the SYSTEM TEMP DIR to /tmp