Understanding Crystal Invoices

This chapter provides an overview of the Crystal invoices, and discusses how to create new crystal invoices.

Understanding Crystal Invoice Structure

Crystal invoices use invoice layout slightly differently than do SQR invoices. SQR invoices use an invoice layout ID to determine where to place a particular piece of information on the page, for example, to determine where to print header notes. With Crystal invoices, that type of information placement is determined by the Crystal report.

Crystal invoices use an invoice layout ID to perform extra or different calculations or to collect extra or different information. The baseline invoice extract Application Engine process performs most calculations and processing to support the same features that are supported with SQR invoices. Both Crystal and SQR invoices get their data from the extract tables. If you include additional information on the modified invoices, do so by adding invoice layouts.

A Crystal invoice form has a main query, a report and may contain subqueries to select, summarize or group invoice data.

Subqueries

The following subqueries are used in the sample Crystal invoice forms:

Notes

You can print notes on the Crystal report using subreports. The Header Note Print Extract table, BI_EXT_HDR_NOTE, contains all header and customer notes in the appropriate language for standard notes. The Line Note Print Extract table, BI_EXT_LIN_NOTE, contains all line notes, and indicates whether the note type should be treated as primary, secondary, or other, based on the invoice form (NOTE_CATEGORY). The NOTES_SEQ_NUM indicates the order in which you want to print notes, if there is more than one note.

For example, BIPRNT00 prints line notes after the line and prints all header notes in the footer of the invoice, regardless of NOTE_CATEGORY. Each uses a different subreport and query to select the appropriate notes. The Line Notes Query (BIPRNTNOTE_LINE) select statement resembles the following:

 SELECT A.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.INVOICE, A.LINE_SEQ_NBR,
 A.NOTES_SEQ_NUM, A.NOTE_LEVEL_TYPE, A.TEXT254
 FROM PS_BI_EXT_LIN_NOTE A
 ORDER BY 1, 2, 3, 4, 5

The subreport links the keys with the keys on the main report. The placement of the subreport on the report controls where the notes are printed.

Similarly, the Header Notes Query (BIPRNTNOTE_HDR) select statement resembles the following:

SELECT A.PROCESS_INSTANCE, A.BUSINESS_UNIT, A.INVOICE, A.NOTES_SEQ_NUM,
A.NOTE_LEVEL_TYPE, A.TEXT254, A.ORDER_NO, A.LANGUAGE_CD
 FROM PS_BI_EXT_HDR_NOTE A
 ORDER BY 1, 2, 3, 4

To print primary header notes in a location that is different than that where you print secondary header notes, create two queries and two subreports that include the following condition:

AND NOTE_CATEGORY = 'P'

or

AND NOTE_CATEGORY <> 'P'

Place the subreports in the appropriate place on the main report.

Subtotals

Add subtotaling to Crystal reports by adding groups to the reports. Use the order management invoice example, BIPRNT02. The entire report is comprised of several invoices. The first group is the invoice; the second group is grouped on the field to subtotal by. The delivered example subtotals by line type. This example prints the line type description (such as revenue, freight, or miscellaneous) in the group header and a subtotal of the line extended amount that is in the footer.

Note. If you want to print line numbers in order and still group by another field, you must include that field in the sort by ID that is used on that invoice form.

PeopleSoft Order Management Product Configuration Detail

If the invoice form specifies to print configuration detail, the system populates the extract table with CONFIG_DTL_FLAG = Y. The query BIPRNCONFGDTL matches configuration details with rows on the extract table that print configuration. The Crystal report must use a subreport with this query, linking the keys from it and the base query. The PS/OM example Crystal report, BIPRNT02, does this; copy it to use as an example.

Consolidated Invoices

BI_EXTRCT contains information for both the consolidated header and all attached headers. The fields CONSOL_HDR, CONSOL_BUS_UNIT, and CONSOL_INVOICE indicate which rows are the consolidated headers, which are the attached headers, and what consolidated invoices they are attached to.

Consolidated Crystal Report

PeopleSoft Billing provides BIPRNC00 as an example of a consolidated Crystal report. You can use the example as provided or modify it as appropriate.

The provided report example contains all invoices that print from the run control selection. These invoices are divided into four groups:

1. Single process instance.

2. Single consolidated report.

3. Single business unit of an attached invoice.

4. Single attached invoice.

Because a consolidated invoice can contain invoices from mixed business units, there are two keys for attached invoices, and therefore two groups. There is no additional printing for the attached business unit, so the entire group is suppressed.

In the provided example, we display each attached invoice as a group of lines with a subtotal, rather than printing each attached invoice on a separate page like the SQR consolidated invoices. However, you can change the pagination, group headings, and detail headings to give this report a different appearance.

In the provided example, we assume that the header information of the attached bills is the same as the consolidated header information. Nothing in the system, however, enforces this condition. Some fields that could be different include elements such as customer number, due date, and remit to address. If you know that these could be different for attached invoices, you can print them in the group three header.

Creating New Crystal Invoices

To create a new Crystal invoice:

1. Add an invoice form using the EXTRACT layout.

2. Add a new query.

3. Create a new Crystal report form.

4. Create a new run control record.

5. Add a new process definition.

6. Update the Invoice Extract (BI_IVCEXT) Application Engine program.

Note. Using an existing PeopleSoft Query and Crystal report forms as reference will assist you when creating a new Crystal report

Adding an Invoice Form Using the EXTRACT Layout

To add a new invoice form using the EXTRACT layout:

1. Access the Invoice Formatting Options page.

2. Create a new Invoice Form.

3. Choose a Layout ID that uses a layout type of Extract Table. Set the other formatting options as required.

See Also

Establishing Invoice Formatting, Sorting, and Printing Options

Adding a New Query

After you create a new invoice form, you must create a new query that uses the new form that you created, and save it as a different name. This query selects, derives or calculates the information used by the fields on the form. You can create the query from scratch, but often it is faster to modify an existing query and save it as your new query. BIPRNT00, the base query for Crystal invoices, and BIPRNC00, the base query for consolidated Crystal invoices are good starting points.

The query must contain at least one criteria: BI_EXTRCT.INVOICE_FORM = xxxxx, where xxxxx is the Invoice Form that you just created. Include additional fields in the query or remove fields that you don't use on the new Crystal report.

Creating a New Crystal Report Form

After you've created a new invoice form and a new query, create the new Crystal report or copy an existing report and modify it appropriately and save it as your new Crystal report form. BIPRNT00.rpt is the base report for Crystal. Change the query in the new report.

To change the query:

1. Select Database, Set Location in the Crystal Report Designer.

   The system prompts you to log in to a PeopleSoft database, and it lists the queries in that database.

2. Select the query that you previously created and modify the report as appropriate.

Note. You may need to configure the Crystal reports to expand the amount sizes to meet your business needs.

Creating a New Run Control Record

A new Crystal report needs to be associated with a run control record if you want to run the new Crystal invoice with the process scheduler. Therefore, you should create a new run control record.

To create a new run control record:

1. Access Application Designer.

2. Select record definition RUN_BI_PRNCRX1 and save it, using Save As, using a different name.

3. Create an SQL table for the record definition.

Adding a New Process Definition

If you want to run the new Crystal invoice with the Process Scheduler, you must add a process and a job definition. This step is optional.

To add a process and a job definition:

1. Access the PeopleTools, Process Scheduler, Processes page

2. Create a new process using name of the query you created as the process name.

3. Fill in the required information on the Process Definition page.

4. Click the Process Definition Options tab.

5. Add a Process Group of BIALL and if the invoice may be reprinted with the eBill Payment application, EBALL.

6. To pass the run control information to the Crystal report, define the Parameter List as ORIENTL :PRCSRUNCNTL.OPRID :PRCSRUNCNTL.RUN_CNTL_ID where ORIENTL is for landscape orientation. Similarly, ORIENTP is for portrait orientation.

7. Do not link the new invoice print process with any component; leave the Component list blank.

   Note. You can add the process to an existing job or create a new one. You must make sure that other required processes, including BI_IVCEXT, are run in the correct sequence.

8. Access the PeopleTools, Process Scheduler, Jobs page

9. Create a new job. The job name can be anything you want. Fill in the required information on the Job definition page

10. Add the invoice extract process (BI_IVCEXT) and the new invoice print process to the Process List.

11. Click the Job Definition Options tab.

12. Add a Process Group of BIALL and, if the invoice may be reprinted with the eBill Payment application, EBALL.

13. To add the new invoice print job directly to one or more run control pages, add the component names of the run control page(s) to the Component list.

See Also

Enterprise PeopleTools 8.48PeopleBook: PeopleSoft Process Scheduler

Updating the Invoice Extract (BI_IVCEXT) Application Engine Program

The Invoice Extract (BI_IVCEXT) Application Engine program needs to be modified to populate the new run control table.

To update the Invoice Extract Application Engine program:

1. Access the Invoice Extract Application Engine program.

2. Expand the POPRUNCR section.

3. Add this code to step 10: PeopleCode action where these values are replaced with the results of your setup:

   Description	Value
   New invoice form ID	CRX99
   New Crystal process name	BIPRNT99
   New run control record	RUN_BI_PRNCRX99

   When = "BIPRNT99"
   BI_EXTRACT_AET.SQLTEXT = " AND PAGE_ORIENTATION = 'E' AND  
   INVOICE_LAYOUT_ID NOT IN ('GM_103X', 'GM_270', 'GM_GEN', 'SF1080' 
   , 'SF1081') AND INVOICE_FORM_ID = 'CRX99' AND CONSOLIDATE = 'N' 
   AND ACCEPTGIRO_IND <> 'Y' ";
      BI_EXTRACT_AET.RECNAME = "RUN_BI_PRNCRX99";
      Break;
   

Note. The Invoice Extract program can be modified to perform complex calculations. The result can be saved in an extract table which can pulled into the Crystal report by the query.