Understanding Invoice Forms

This chapter discusses:

Invoice programming
The delivered invoice forms.
The Billing extract tables.
Deleting old invoice data from the extract tables.

Understanding Invoice Programming

PeopleSoft provides two methods for programming invoice forms, SQR and Crystal Reports. The SQR invoices combine data selection, processing and printing into a single, modular program. You can create and modify SQR invoices with any appropriate program editor or development environment. The Crystal Reports invoices separate data selection and processing from formatting and printing. Data selection and processing are handled by queries; formatting and printing are handled by a report form. Queries can be created and modified inside the PeopleSoft application using the Query Manager. Crystal Report forms can be created and modified using the Crystal Reports application.

Invoice programs operate on the data in the Billing extract tables. The Billing Invoice Extract Process (BI_IVCEXT) extracts the invoice data and stores in the Billing extract tables. In addition, control information is provided by the run control record. An invoice program must select only the data it can process. The following criteria are typically used to select the rows from the extract tables that an invoice program or report is capable of processing:

PROCESS_INSTANCE	A process instance number is assigned to each extract row by BI_IVCEXT when a print job is run. The process instance number that an invoice program should select is available in the run control record.
CONSOL_HDR	Y (yes) for consolidated invoices.
INVOICE_FORM_ID	The id of the invoice form as defined on the Invoice Formatting Options page.
PAGE_ORIENTATION	Select P (portrait) or L (landscape) for SQR invoices; E (extract) for Crystal invoices.
SUM_IVC_PRINT_OPT	Whether an invoice should be printed in the detail or summarized formats, or both formats. DETL (detail). SUM (summarized). Only summarized invoice programs should select invoices with a SUM setting. BOTH.
ACCEPTGIRO_IND	Only acceptgiro invoice programs should select invoices with a Y (yes) setting.

Common Modules

PeopleSoft writes its processes in a modular fashion to facilitate modification. Routines that are common to all invoice printing processes are isolated into modules that are shared by multiple processes. Routines that you are most likely to modify are organized into groups of similar modules. Use an existing program as the template and substitute modules as appropriate.

There are, however, several areas of significance where the programs differ from each other:

The extract program BI_IVCEXT performs all processing that is required to print an invoice and places the results in a set of extract tables.

BI_IVCEXT does not print the invoice. PeopleSoft delivers queries and Crystal reports to print invoices using the data in the extract table. You can also customize the system to use any compatible report writer program to print invoices from this data..
The logic that is used to select bills for processing differs for SQRs and Crystals.

For standard SQR invoices, member BIIVCNON.SQC selects the bill activity. For consolidated SQR invoices, member BIIVCCON.SQC selects the bill activity. For both standard and consolidated Crystals, PeopleSoft Billing Query definitions select the bill activity.
All common steps that involve a print command are included in BIIVCSQR.SQC and are only used in the SQR invoices.
Printer definitions and declarations are specific to page orientation. Appropriate modules are included in each program.
Generally, for SQR invoices, portrait and landscape versions of the program support multiple invoice layouts.

The appropriate set of print routines for these layouts and the supporting column definitions are included by page orientation. For instance, the sample RIGHTSTUB layout is of portrait orientation, so the routines for RIGHTSTUB printing are included in the portrait invoice print processes for standard and consolidated invoice printing (BIIVCPN and BIIVCPC).
Grants invoice layouts are all portrait invoices and are produced by BIGIVCPN.
Federal invoice layouts are also portrait invoices and are produced by BISF108X.
The Crystal version of the invoice print process also supports multiple invoice layouts, which are all included in the Query definitions.

Modifying the Billing Invoice Extract Application Engine Program for New Layouts

If an invoice form requires information not provided in the extract tables, you can modify the extract Application Engine program, BI_IVCEXT, to extract, calculate or derive that information and place it in the extract tables. The recommended method for this type of modification is to add a section to perform the additional processing. The new procedure should only be called for invoices that require the different information. The Invoice Layout ID field can be used to select those invoices in the Billing data that require special handling. Once the system is programmed to extract the needed data into the Billing extract tables, SQR or Crystal form modifications might also be needed.

The following example shows a procedure, BIEVALX.SQC, that calls PROCESS-XXX-LAYOUT when an invoice with a layout id of PS_XX is processed. Include this SQC in the print process BIXXXXXX.SQR. Define the procedure PROCESS-XXX-LAYOUT in the modified SQC that is called by BIXXXXXX.SQR.

!-------------------------------------------------------------------! ! Procedure: PROCESS-LAYOUT-SPECIFIC-INFO ! ! Desc: Select the extra processing routine according to the ! ! invoice layout. ! !-------------------------------------------------------------------! begin-procedure PROCESS-LAYOUT-SPECIFIC-INFO add 1 to #level move 'PROCESS-LAYOUT-SPECIFIC-INFO' to $Current-Procedure evaluate $InvoiceLayout when = 'PS_XX' ! Example Invoice Layout do PROCESS-XXX-LAYOUT break when-other break end-evaluate

Understanding the Billing Sample Invoices

PeopleSoft provides the following sample invoice forms (depending on which PeopleSoft applications you have installed, you may not see all the forms in this list):

CRYSTAL	A basic, sample Crystal Reports invoice.
GM_103X	Grant forms 1034 and 1035 invoices.
GM_270	Grants OMB 270 invoice.
GM_GEN	General grants invoice form.
GM_LOC	Grants Letter of Credit invoice.
LANDSCAPE	A landscape-orientation, sample SQR invoice.
PORTSTUB	A portrait-orientation, sample SQR invoice with a tear-off payment stub on the right side of the page.
PS/PSA51, PS/PSA01	This format groups lines by project description, employee name and resource type if they exist. Prints subtotals on change of project or employee. Prints contract prepaid information for utilization lines based on the existence of a contract number and a contract prepaid sequence number.
PS/CA51, PS/CA	This format groups lines by contract detail, and it applies to PeopleSoft Contracts without PeopleSoft Project Costing.
PS/PSA52, PS/PSA02	This format groups lines by project description, activity description, employee name and resource type if they exist. Prints subtotals on change of project, activity, or employee. Prints contract prepaid information for utilization lines based on the existence of contract number and a contract prepaid sequence number.
PS/PSA53, PS/PSA03	This format groups lines by project description and line type.
PS/OM, PS/OM50	Sample Order Management invoice.
SF1080	Standard form 1080.
SF1081	Standard form 1081.
STANDARD	A portrait-orientation, sample SQR invoice.
Summarized invoice forms	The summarized invoice forms are not defined under the invoice setup options and cannot be explicitly assigned to an invoice. The summarized invoice forms are used when an invoice contains at least one line with a summarization template attached and the run control print options are set to generate a summarized invoice. Note. When invoices are generated in a situation where the print options are unavailable, such as when generating an invoice or pro forma directly from the online bill entry pages or when using a direct invoicing process through the Billing interface, then a summarized invoice will be printed whenever a bill line has an attached summarization template (and a detail invoice will not be printed).
(NLD) Acceptgiro invoice forms	PeopleSoft provides example forms for Dutch acceptgiro invoices. Acceptgiro invoice forms are not defined under the invoice setup options and cannot be explicitly assigned to an invoice. For an acceptgiro invoice to be generated, both the Receivables business unit of the Billing business unit and the invoiced customer must be set up properly. See (NLD) Printing Acceptgiro Invoices. Important! Your organization may need to make modifications to the Crystal Report and SQR formats to ensure that the fields line up correctly on the acceptgiro attachment. PeopleSoft does not certify that the acceptgiro feature adheres to documented standards for the Netherlands. Your organization is responsible for all acceptgiro testing, certification, and any other forms of compliance with legal and contractual obligations relating to acceptgiro printing and usage.

An invoice form may be produced by different invoice programs or Crystal Reports depending on the context in which the invoice is printed. For example, an invoice with an Invoice Form value of CRYSTAL would be produced by the BIPRNT00 Crystal Report when generated as a stand-alone invoice, but would be produced by BIPRNC00 when attached to a consolidated invoice. The following table lists which invoice programs or Crystal Reports generate which invoice forms, and provides the name of the run control record:

Program/Report	Run Control Record	Invoice Form IDs
BIPRNT00.RPT	RUN_BI_PRNCRX1	CRYSTAL
BIPRNTA0.RPT	RUN_BI_PRNCRX1A	CRYSTAL with acceptgiro
BICLSUM.RPT	RUN_BI_PRNCRX1S	Crystal summarization invoice form
BIPRNT01.RPT	RUN_BI_PRNCRX2	Crystal PS/PC
BIPRNT02.RPT	RUN_BI_PRNCRX3	Crystal PS/OM
BIPRNA2.RPT	RUN_BI_PRNCRX3A	Crystal PS/OM with acceptgiro
BIPRNC00.RPT	RUN_BI_PRNCRX4	Consolidated CRYSTAL
BIPRNCA0.RPT	RUN_BI_PRNCRX4A	Consolidated CRYSTAL with acceptgiro
BICLSUMC.RPT	RUN_BI_PRNCRX4S	Consolidated summarization Crystal invoice
BIPRNC02.RPT	RUN_BI_PRNCRX5	Consolidated Crystal PS/OM
BIPRNCA2.RPT	RUN_BI_PRNCRX5A	Consolidated Crystal PS/OM with acceptgiro
BIPSA01.RPT	RUN_BI_PRNCRX6	Crystal PS/PSA01
BIPSAA1.RPT	RUN_BI_PRNCRX6A	Crystal PS/PSA01 with acceptgiro
BIPSA02.RPT	RUN_BI_PRNCRX7	Crystal PS/PSA02
BIPSAA2.RPT	RUN_BI_PRNCRX7A	Crystal PS/PSA02 with acceptgiro
BIPSA03.RPT	RUN_BI_PRNCRX8	Crystal PS/PSA03
BIPSAA3.RPT	RUN_BI_PRNCRX8A	Crystal PS/PSA03 with acceptgiro
BICA.RPT	RUN_BI_PRNCRX9	Crystal PS/CA
BICAA.RPT	RUN_BI_PRNCRX9A	Crystal PS/CA with acceptgiro
BIIVCPN.SQR	RUN_BI_PRNSQR1	SQR Portrait including: PORTSTUB PS/CA51 PS/OM50 PS/PSA51 PS/PSA52 PS/PSA53 STANDARD
BIIVCPNA.SQR	RUN_BI_PRNSQR1A	SQR Portrait invoices with acceptgiro, including: PORTSTUB PS/CA51 PS/OM50 PS/PSA51 PS/PSA52 PS/PSA53 STANDARD
BIIVCLN.SQR	RUN_BI_PRNSQR2	SQR LANDSCAPE
BISLSUM.SQR	RUN_BI_PRNSQR2S	Summarized SQR LANDSCAPE
BIIVCPC.SQR	RUN_BI_PRNSQR3	Consolidated SQR Portrait invoice forms, including: PORTSTUB PS/CA51 PS/OM50 PS/PSA51 PS/PSA52 PS/PSA53 STANDARD
BIIVCPCA.SQR	RUN_BI_PRNSQR3A	Consolidated SQR Portrait invoice forms with acceptgiro, including: PORTSTUB PS/CA51 PS/OM50 PS/PSA51 PS/PSA52 PS/PSA53 STANDARD
BIIVCLC.SQR	RUN_BI_PRNSQR4	Consolidated SQR LANDSCAPE
BISLSUMC.SQR	RUN_BI_PRNSQR4S	Summarized consolidated SQR LANDSCAPE
BIGIVCPN.SQR	RUN_BI_PRNSQR5	Grants invoice forms, including: GM_103X GM_270 GM_GEN GM_LOC
BISF108X.SQR	RUN_BI_PRNSQR6	SQR standard forms: SF1080 SF1081

Understanding the Extract Tables

All invoices select data from the extract tables rather than directly from the Billing tables. The first step to printing invoices (after finalization) is to run the BI_IVCEXT extract process. The extract process populates the following extract tables:

BI_EXTRCT	The main printing extract table. Inserts one row per invoice. Most calculated data applies to the entire invoice, not the invoice lines.
BI_EXTRCT_LINE	Comprised of calculated data for invoice lines. The data in this table supports printing Product Kit Descriptions with the invoice lines. These descriptions are not stored with the invoice table structure; therefore, they must be calculated at print time. The BI_EXTRCT_LINE table is only required if you want to print Product Kit Descriptions. The order management invoice example (BIPRNT02) is the only query/report example that uses this feature.
BI_EXT_HDR_NOTE	Stores the header, customer, and VAT notes that are to be printed on an invoice. If a note is a standard note, then the text that is to be printed comes from the Standard Notes table and is language specific. This table is populated with the nonstandard note text from the bill header and customer. The notes are updated as primary notes if you define the note type as a primary note type on the invoice form.
BI_EXT_LIN_NOTE	Stores line notes to be printed on an invoice.
BI_EXTRCT_TAX	Stores sales and use tax data for invoice lines.
BI_EXTRCT_VAT	Contains VAT information for invoice lines.
BI_EXT_INST_SEC	Contains installment billing information that is used only for installment invoices.
BI_EXT_INST_SUM	Contains installment billing summary information that is used only for installment invoices.
BI_EXTRCT_PROJ	Stores PeopleSoft Project lines that are extracted for Invoice sample formats PS/PSA01, PS/PSA02, and PS/PSA03.
BI_EXTR_CONTR	Stores PeopleSoft Contract lines that are extracted for Invoice sample format PS/CA only.
BI_EXTRCT_UTL	Contains PeopleSoft Contract prepaid utilization rows. Used in Invoice sample formats PS/PSA01and PS/PSA02.
BI_EXT_SHIP_TO	Contains ship-to name and address of extract based on the ship-to customer ID and ship-to address sequence number that is defined on the bill header. It is currently used in Invoice sample formats PS/PSA01, PS/PSA02, PS/PSA03 and PS/CA.
PS_BI_EXTRCT_TXDTL	Contains sales tax detail.
PS_BI_EXT_EXSDTL	Contains sales/excise tax detail for India transactions.
PS_BI_EXTRCT_CC	Contains Carbon Copy address information.
PS_BI_EXTRCT_PAY	Contains invoice payment information.
PS_BI_EXT_SUM_GPHR	Contains summarized invoice group header information.
PS_BI_EXT_SUM_IVC	Contains summarized bill line information.
PS_BI_EXT_SUM_NT	Contains summarized bill line note information.
PS_BI_EXT_SUM_LNNT	Contains summarized bill line note information.

Important BI_EXTRCT Table Fields

The primary extract table, BI_EXTRCT, contains the following important fields used by invoices:

PRINT_SEQ_NBR	Printing sequence number that is calculated by the Billing extract process based on Print Invoice Sort By information specified by the run control. The query should order by this field to take advantage of this run control feature.
LANGUAGE_CD	The language code is usually specified by the run control. When it is not specified in the run control, the system looks it up either from user preferences or the PeopleSoft general options table. Both SQR and Crystal invoices can be printed in any supported languages. If the PeopleSoft environment is configured to support multiple languages, the definitional data in the transaction, such as the description of a project, will be extracted from the related language tables in the desired language for both SQR and Crystal forms. However, there is a difference in handling predefined labels on SQR and Crystal forms. SQR uses the related language Strings table. The Crystal report definition does not include any language-specific text. Every text portion on the invoice is printed using Crystal report formulas, enabling one report to contain multiple invoices, printed in multiple languages. Each portion of text uses a formula called @langlookup, resembling the following: StringVar RptLang := {BIPRNT00.LANGUAGE_CD}; if stringVar RptLang = "USA" then 1 Else if RptLang = "ENG" then 1 Else if RptLang = "FRA" then 2 Else if RptLang = "ESP" then 3 Else if RptLang = "CFR" then 4 else if RptLang = "GER" then 5 else if RptLang = "JPN" then 6 else if RptLang = "INE" then 7 else if RptLang = "DUT" then 8 else if RptLang = "POR" then 9 else if RptLang = "ITA" then 10 else if RptLang = "ZHS" then 11 else 1 Each text field prints a different text string of an array, indexing it with the @langlookup formula.
XEU_PARTICIPATING	Indicates whether the billing currency is a euro-participating currency. If the currency is euro-participating, the invoice prints the euro invoice amount. Otherwise, it does not. Crystal reports use a format formula on the field, and the text suppression: {BIPRNT00.XEU_PARTICIPATING} = 'N'. See Crystal Reports documentation for more detail about how to use formulas.
DECIMAL_POSITIONS	Indicates how many decimal places to print for the billing currency. Crystal reports are set up to print any number of decimal places. The field format must be set up accordingly. The edit formula on the decimals uses DECIMAL_POSITIONS to define how many places to print.
INVOICE_DT	The invoice date that is printed on the invoice uses this field from the extract table. There is also an INVOICE_DT on the table BI_HDR, but depending on where the bill is in the invoice process, that field may not contain a value. BI_IVCEXT calculates the invoice date, if needed, and populates INVOICE_DT on the extract table.
PYMNT_TERMS_ CD_DSC(Payment Terms Code Description)	. Use this field to print the description, and not the code that is stored on BI_HDR.
DISC_AMT, DISC_DT	The discount amount and discount date. Calculated in BI_IVCEXT.
DUE_DT	Due date. Calculated in BI_IVCEXT.
ADDR_LN1 thru 8	These lines contain the customer name, customer contact (attention to), and address lines formatted appropriately for the country. The country-dependent formatting is done in BI_IVCEXT. If all address lines are used in an address, there are eight printed lines. If all address lines are not used, there are fewer than eight lines, and the ADDR_LN fields are filled out from top to bottom with blank lines at the bottom.
REMIT_ADDR_LN1 thru 8	These lines contain the remit to description and address. They are handled the same as Customer address lines.
INV_LABEL	Invoice label is the text that is printed at the top of the invoice. It is a language-specific version of invoice, pro forma, temporary, credit invoice, and so on.
CUSTOM_LABEL	Contains printed text. For example, it may contain the label that is specified on the run control, or a language-specific version of original, duplicate, courtesy copy, original sent via electronic data interchange.
CONFIG_DTL_FLAG	Equals Y if the invoice form specifies the printing of order management configuration detail. This field is used in the example query BIPRNCONFGDTL, which is used in a subreport in the order management invoice examples, BIPRNT02 and BIPRNC02.
CONFIG_CD_FLAG	Equals Y if the invoice form specifies the printing of order management configuration code. This field is used in the example query BIPRNCONFGCD, which is used in a subreport of the order management invoice examples, BIPRNT02 and BIPRNC02.
CR_CARD_FLG	Determines whether a bill has credit card information.
AMOUNT	Stores a credit card amount.
AMOUNT_XEU	Stores a credit card amount in euros.
NET_AMOUNT	Contains a net amount.
NET_AMOUNT_XEU	Stores a net amount in euros.
TEMPLATE_INVOICE	Used with installment bills, and contains the installment template invoice number.
BILLING_FREQUENCY	Used to identify billing frequency for processing.
PRINT_INST_DETAIL	Controls printing of installment bill details.
PRINT_INST_SUMMARY	Controls printing of the installment bill summary.
PRINT_VAT_ANAL_FLG	Controls printing of VAT analysis
PRINT_TAX_FLG	Controls printing of sales tax
PRINT_UTL_PPD_FLG	Controls printing of contract utilization prepaid information, and is used in the example query BIPSA01, BIPSA02.
PROJECT_ID	Stores the project ID from the bill header, and is used in the example query BIPSA01, BIPSA02, BIPSA03.
PROJ_DESCR	Stores project description that is derived from the Project table corresponding to the project ID on the bill header.
CONTRACT_NUM	Stores the contract number from the bill header, and is used in the example query BIPSA01, BIPSA02, BIPSA03, BICA.
PO_REF	Stores the purchase order reference number from the bill header, and is used in the example query BIPSA01, BIPSA02, BIPSA03, BICA.

Deleting Old Invoice Data from the Extract Tables

Billing information moved into the extract tables when invoices are generated accumulates if you do not periodically delete it. To assist you in maintaining the Billing extract tables, PeopleSoft provides an online utility and a batch utility.

Note. After deleting old invoice data from the Billing extract tables, the database or system administrator should update the extract table statistics. This will insure a better performance for the invoice extract process.

Here are all the extract tables: PS_BI_EXTRCT, PS_BI_EXT_HDR_NOTE, PS_BI_EXT_SHIP_TO, PS_BI_EXTRCT_LINE, PS_BI_EXTRCT_PROJ, PS_BI_EXTRCT_CONTR, PS_BI_EXTRCT_UTL, PS_BI_EXT_INST_SEC, PS_BI_EXT_INST_SUM, PS_BI_EXT_LIN_NOTE, PS_BI_EXTRCT_TAX, PS_BI_EXTRCT_VAT, PS_BI_EXTRCT_TXDTL, PS_BI_EXT_EXSDTL, PS_BI_EXTRCT_CC, PS_BI_EXTRCT_PAY, PS_BI_EXT_SUM_GPHR, PS_BI_EXT_SUM_IVC, PS_BI_EXT_SUM_LNNT, PS_BI_EXT_SUM_NT

The invoice extract process generates run control records for the invoice print SQR and Crystal processes. Once the old invoice data is deleted from the extract tables, the run control records that are associated with the deleted extract data becomes obsolete. Therefore, it also makes sense to update the run control table statistics.

Here are all the invoice run print process run control tables for all invoice print SQR and Crystal processes: PS_RUN_BI_PRNCRX1, PS_RUN_BI_PRNCRX5, PS_RUN_BI_PRNCRX5A, PS_RUN_BI_PRNCRX6, PS_RUN_BI_PRNCRX6A, PS_RUN_BI_PRNCRX7, PS_RUN_BI_PRNCRX7A, PS_RUN_BI_PRNCRX8, PS_RUN_BI_PRNCRX8A, PS_RUN_BI_PRNCRX9, PS_RUN_BI_PRNCRX9A, PS_RUN_BI_PRNCRX1A, PS_RUN_BI_PRNSQR1, PS_RUN_BI_PRNSQR1A, PS_RUN_BI_PRNSQR2, PS_RUN_BI_PRNSQR2S, PS_RUN_BI_PRNSQR3, PS_RUN_BI_PRNSQR3A, PS_RUN_BI_PRNSQR4, PS_RUN_BI_PRNSQR4S, PS_RUN_BI_PRNSQR5, PS_RUN_BI_PRNSQR6, PS_RUN_BI_PRNCRX1S, PS_RUN_BI_PRNCRX2, PS_RUN_BI_PRNCRX3, PS_RUN_BI_PRNCRX3A, PS_RUN_BI_PRNCRX4, PS_RUN_BI_PRNCRX4A, PS_RUN_BI_PRNCRX4S,

Using the Online Utility

On the Delete Extract Bills page, define the range selection and select the bills for deletion. Click the Delete button to delete the data from all extract-related tables.

Using the Batch Utility

The Workflow/Batch Parameters page enables you to designate the number of days that extracted PeopleSoft Billing information can collect in the Billing Extract table before deletion. Schedule and run this process to look for and delete extracted information on a regular basis.

To define the number of days that extracted information can remain before deletion:

Access the Define Workflow page.
Indicate the number of days after which you want to delete PeopleSoft Billing information from the extract tables in the Delete Extract Bills, Number of Days Old field.

On the Delete Bills run control page, select Delete Extract Bills. Run the BIDELDATA process to delete from all extract-related tables and its associated run control tables. This process will delete any row older than the number of days specified Workflow/Batch Parameters page, from those tables.

Note. This process runs in three-tier mode only.