21 Generating the Wine Premises Operations Report (Form 5120.17)

This chapter contains the following topics:

Section 21.1, "Understanding the Wine Premises Operations Process"
Section 21.2, "Setting Up the Wine Premises Operations Report"
Section 21.3, "Previewing Wine Premises Operations Report"
Section 21.4, "Generating the Wine Premises Operations Report (Form 5120.17)"

21.1 Understanding the Wine Premises Operations Process

Bonded wineries in the United States must submit the report of Wine Premises Operations (Form 5120.17) to the Alcohol and Tobacco Tax and Trade Bureau periodically to report taxable winery operations on the bonded premises. The bonded wineries are required to file the report monthly, quarterly, or annually, based on the size of their operations. This report reviews the complete inventory transactions (closed operations) of a winery for a specified time period.

You run the Wine Premises Operations report (Form 5120.17) to capture periodic data and generate a PDF of the report to submit to the Alcohol and Tobacco Tax and Trade Bureau.

To generate the Wine Premises Operations report, you must:

Set up line descriptions and their attributes for each line of the Wine Premises Operations report, using the Wine Premises Report Setup program (P31B5101).
Generate data using the Wine Premises Report Preview program (P31B5120).

21.1.1 Wine Premises Operations Report Generation Process Flow

The following process flow illustrates the Wine Premises Operations report generation process, and the relationship between the programs and the tables used in the process:

Figure 21-1 Wine Premises Report Generation Process Flow

Description of "Figure 21-1 Wine Premises Report Generation Process Flow"

The Wine Premises Operations report generation process flow includes the following steps:

Run the Wine Premises Report Attribute Extraction program (R31B5100) to populate the valid material type, configured operation code, and lot value combinations in the Wine Premises Report Attribute table (F31B5100).
Set up line descriptions in the Line Description Revisions form of the Wine Premises Report Setup program (P31B5101). The system updates the line description records in the Wine Premises Report Line Definition table (F31B5101).
Set up report attributes (material type, configured operation, and lot value) in the Report Configuration Revisions form of the Wine Premises Report Setup program (P31B5101). Perform this action to calculate column values for each line of the Wine Premises Operations report.

The system stores the line configuration details in the Wine Premises Report Configuration table (F31B5102).
Extract data to the Work With Wine Premises Report Preview form of the Wine Premises Report Preview program (P31B5120).

The system fetches setup data from the Wine Premises Report Configuration table (F31B5102) and column values from the Blend Transactions table (F31B66).
Validate the Wine Premises Operations report details, and review error messages in the Wine Premises Report Preview Revisions form of the Wine Premises Report Preview program (P31B5120).

The system stores the edited values in the Wine Premises Report Preview table (F31B5120).
Rectify any errors in the Wine Premises Report Preview Revisions form. Click the value you want to edit in the report. The system displays the configuration setup of the selected value in the Report Configuration Revisions form of the Wine Premises Report Setup program (P31B5101) for editing.
Click Generate Report in the Work With Wine Premises Report Preview form to generate the Wine Premises Operations report in PDF format.

21.2 Setting Up the Wine Premises Operations Report

This section provides an overview of the Wine Premises Report Setup program (P31B5101), describes a prerequisite, and discusses how to:

Run the Wine Premises Report Attribute Extraction program (R31B5100).
Set up line descriptions.
Set up line configurations.

21.2.1 Understanding Wine Premises Operations Report Setup

The Wine Premises Report Setup program enables you to:

Define the line description for each line of the Wine Premises Operations report and associate it with the part, section, and line number of that line in the report.

Using the Work With Line Descriptions form, you can:

Review existing line descriptions.
Add or edit line descriptions.

Delete line descriptions.

Note:

You can delete a line description record for a winery only after you delete the configuration records associated with that line description.

If a configuration record exists for a line description, the system displays an error message.

Copy line descriptions and configurations from one winery to another.

Note:
When you copy a line description, you can select the Copy Configuration option to also copy the associated configurations.

Define attributes to calculate the column values for each line of the Wine Premises Operations report. Attributes are values that you select to identify data for each column of the report. You can choose any or all of these attributes:
- Material Type
- Configured Operation Code
- Which Lot Quantity
  - FB: From Before Lot
  - FA: From After Lot
  - FB-FA: From Before - From After
  - TB: To Before Lot
  - TA: To After Lot
  - TA-TB: To After - To Before
Using the Report Configuration Revisions form, you can:
- Review attributes (material type, configured operation code, and lot values) for each line of the Wine Premises Operations report.
- Add or edit attributes for each line of the Wine Premises Operations report.
- Delete attributes for each line of the Wine Premises Operations report.

The system stores information for the Wine Premises Report Setup program in the Wine Premises Report Line Definition table (F31B5101) and the Wine Premises Report Configuration table (F31B5102).

21.2.1.1 Lines That Do Not Require Line Configuration Setup

The following lines are populated automatically in the Wine Premises Report Preview program. You must not set these lines.

Part I, Section A, Line 1 - On hand beginning of period
Part I, Section A, Line 12 - Total
Part I, Section A, Line 31 - On hand end of period
Part I, Section A, Line 32 - Total
Part I, Section B, Line 1 - On hand beginning of period
Part I, Section B, Line 7 - Total
Part I, Section B, Line 20 - On hand end of period
Part I, Section B, Line 21 - Total
Part III, Line 1 - On hand beginning of period
Part III, Line 4 - Total
Part III, Line 9 - On hand end of period
Part III, Line 10 - Total
Part IV, Line 1 - On hand beginning of period
Part IV, Line 4 - Total
Part IV, Line 9 - On hand end of period
Part IV, Line 10 - Total
Part VI, Line 1 - On hand beginning of period
Part VI, Line 5 - Total
Part VI, Line 10 - On hand end of period (Storage Tanks)
Part VI, Line 11 - Total
Part VII, Line 1, Column (f) - Total
Part VIII, Line 1, Column (c) - Total
Part VIII, Line 2, Column (c) - Total
Part IX, Line 1, Column (f) - Total
Part IX, Line 2, Column (f) - Total
Part IX, Line 3, Column (f) - Total
Part IX, Line 3 - On hand end of period

21.2.2 Prerequisites

Before you set up line configurations, you must run the Wine Premises Report Attribute Extraction - Build program (R31B5100) to derive the list of available attribute combinations. The system stores the attribute combinations in the Wine Premises Report Attribute table (F31B5100).

Select Blend Facility Setup (G31B02), Wine Premises Report Attribute Extraction.

Note:

You must run the Wine Premises Report Attribute Extraction - Build program every time you add new material types or configured operations to the blend system. Otherwise, you will not be able to view the new attributes in the Report Configuration Revisions form.

21.2.3 Forms Used to Set Up Wine Premises Operations Report

Form Name	Form ID	Navigation	Usage
Work With Line Descriptions	W31B5101B	Blend Facility Setup (G31B02), Wine Premises Report Setup	View existing line descriptions. Select Copy Winery Lines from the Form menu to copy line descriptions and configuration records from one winery to another. Delete line descriptions.
Line Description Revisions	W31B5101F	Click the Add button on the Work With line Descriptions form.	Add, edit, or delete a line description.
Report Configuration Revisions	W31B5101D	Select a line description and then select Report Configuration from the Row menu on the Work With Line Description form.	Review, add, or modify attributes for each line of the report.

Form Name

Form ID

Navigation

Usage

Work With Line Descriptions

W31B5101B

Blend Facility Setup (G31B02), Wine Premises Report Setup

View existing line descriptions.

Select Copy Winery Lines from the Form menu to copy line descriptions and configuration records from one winery to another.

Delete line descriptions.

Line Description Revisions

W31B5101F

Click the Add button on the Work With line Descriptions form.

Add, edit, or delete a line description.

Report Configuration Revisions

W31B5101D

Select a line description and then select Report Configuration from the Row menu on the Work With Line Description form.

Review, add, or modify attributes for each line of the report.

21.2.4 Setting Up Line Descriptions

Access the Line Description Revisions form.

Figure 21-2 Line Description Revisions form

Surrounding text describes Figure 21-2 .

Winery

Enter a unique identifier of the winery for which you are setting up line descriptions.

Note:

If you want the system to automatically display a default winery unit, set it up in the Defaults Printers & Locations program (P400951).

Enter * and click visual assist (F2) to view a list of all wineries in the system.

Part Number

Enter a number that indicates a part of the Wine Premises Operations report.

Section

Enter a value that indicates a section of the Wine Premises Operations report.

Note:

This is a mandatory field only if the line description belongs to Part 1, Section A of the Wine Premises Operations report.

Line Number

Enter the line number of the line in the Wine Premises Operations report.

Line Description

Enter a description for the line.

You can enter up to 50 alphanumeric characters in this field.

21.2.5 Setting Up Line Configurations

Access the Report Configuration Revisions form.

Each line and column of the Wine Premises Operations report can have more than one configuration record based on the various attribute types in the winery's inventory.

Figure 21-3 Report Configuration Revisions form

Surrounding text describes Figure 21-3 .

Column Letter

Select the column letter for the selected line description from the drop-down list.

This value represents the column in which the value is displayed in the Wine Premises Operations report.

Note:

Some lines of the Wine Premises Operations report can have two values to represent the processes used to create sparkling wine; Bottle Fermentation (BF) and Bulk Process (BP). Enter 'e' to specify BF and 'e1' to specify BP as the process used.

All Attributes

Displays all the valid combinations of material type, configured operation, and lot value. To associate one or more attributes with the selected line description, select the check box next to the field, and click Move Selected Attributes.

You can use the available filters to narrow down the combinations of material type, configured operation, and lot value. Enter *ALL in the Material Type and Configured Op Code fields to filter all material types and configured operations respectively.

Note:

For operations which have only From vessels, such as Add operations, In-place operations, Administrative operations, and QA operations, the TB, TA, and TA-TB lot values are not displayed.

Selected Attributes

Displays the selected combinations of material type, configured operation code, and lot value.

The system calculates quantities based on the attribute values selected in this table, and displays them in the report.

To delete a selected attribute from this list, select the attribute and click Delete. In the confirmation window, click OK to confirm your updates.

21.2.5.1 Additional Fields

The following additional fields are displayed in the Report Configuration Revisions form when you access this form from the Wine Premises Report Preview Revisions form.

Selected Operations: Displays the operations associated with the selected attributes. Click Find to populate this section with the list of operations.
Result: Displays the total quantity of the selected operations.
Result UOM: Displays the unit of measure of the total quantity in the Result field.

21.2.6 Examples - Setting Up Line Configurations

The following table shows examples of line configurations of the Wine Premises Operations report:

Part	Section	Line	Column Letter	Line Description	Material Type	Configured Operation	Which Lot
1	A	2	a	Produced By Fermentation	W (Wine under 14%)	T2B	TA
1	A	5	a	Produced By Blending	W (Wine under 14%)	T2T	TA
1	A	16	a	Removed for Distilling Material	DM (Distilling Material)	SHIP (Ship Bulk Volume Operation)	FB-FA
7		1	a	In Fermenters (Estimated Quantity of Liquid)	FJ (Fermenting Juice)	*ALL	*ALL
3		2	a	Received	S (Grape Spirits)	RECV (BOL Receive Bulk Volume)	FA

21.3 Previewing Wine Premises Operations Report

This section provides an overview of the Wine Premises Report Preview program (P31B5120), lists processing options, and discusses how to:

Set processing options for Wine Premises Report Preview program (P31B5120)
Generate Wine Premises Operations report data.
Validate Wine Premises Operations report details.
Resolve errors in the Wine Premises Operations report details.

21.3.1 Understanding How to Preview the Wine Premises Operations Report

The system uses the Wine Premises Report Preview program to extract data for a specific winery and period from the Blend Transaction table (F31B66) according to the data set up in the Wine Premises Report Setup program. The system stores the extracted data in the Wine Premises Report Preview table (F31B5120).

The Wine Premises Report Preview program enables you to:

Preview the wine premises operations report for a winery and for a specific period.
Validate the generated data for discrepancies.
Navigate to the Wine Premises Report Setup program (Report Configuration Revisions form) to resolve discrepancies.
Modify the system-generated values, if required.
Generate the Wine Premises Operations report in PDF format.

21.3.1.1 Special Handling Fields

The following lines of the Wine Premises Operations Report are coded with special logic to handle unique conversions:

Part 1, Section A, Line 5 - Produced by Blending: The system extracts this quantity if From lots of different tax classes are blended. If lots of the same tax class are blended, the system does not extract the quantity.
Part 1, Section A, Line 23 - Used for Testing: For the QA operations that you specify in the line description and configuration setup, the system extracts the sample quantity from the Sample Detail table (F3712). If you also specify bottling operations, the system extracts the sample quantity from the Blend Transaction table (F31B66).
Part 1, Section A, Line 9 - Inventory Gains and Part 1, Section A, Line 30 - Inventory Losses: The system extracts survey and operational gains and losses from all operations within the specified date range and populates the combined gain quantity or loss quantity in line 9 or line 30 respectively.
Part 3, Summary of Distilled Spirits: The system converts gallons of spirits to proof gallons.
Part 7, In Fermenters: If fruit is included as a whole, the weight of the fruit is converted to gallons.

21.3.2 Forms Used to Set Up Wine Premises Report Preview

Form Name	Form ID	Navigation	Usage
Work With Wine Premises Report Preview	W31B5120B	Blend Reports (G31B06), Wine Premises Report Preview	Retrieve report data for review. Generate the Wine Premises Operations report.
Wine Premises Report Preview Revisions	W31B5120A	Select a line description from the Work With Wine Premises Report Preview form and click OK.	Validate the data in the report.

21.3.3 Setting Processing Options for Wine Premises Report Preview (P31B5120)

These processing options control default processing for the Wine Premises Report Preview program.

21.3.3.1 Versions

This processing option controls which version the Wine Premises Report Preview program (P31B5120) uses for data extraction.

Wine Premises Report Generator Version (R31B5121): Specify the version of the Wine Premises Report Generator program (R31B5121) that the system uses. If you leave this processing option blank, the system uses the default XJDE0001 version.

21.3.3.2 Defaults

These processing options control the default values that the system uses when you generate the Wine Premises Operations report.

Winery: Enter a unique identifier of the winery for which to generate the report.
Report Start Date: Enter the start date for the period for which to generate the report.
Report End Date: Enter the end date for the period for which to generate the report.

21.3.3.3 Process

Report Mode

Use this processing option to specify whether the system runs the report in final or proof mode. Values are:

1: Run the report in final mode. The system uses the ending balance of the previous report as the beginning balance for the report.

Blank: Run the report in proof mode. The system does not update the end balance in the Wine Premises Report Preview table (F31B5120).

21.3.4 Generating the Wine Premises Report Preview

Access the Work With Wine Premises Report Preview form.

Figure 21-4 Work With Wine Premises Report Preview form

Surrounding text describes Figure 21-4 .

Winery: Enter a unique identifier of the winery for which you are generating the data report.
Report Start Date: Enter the start date for the selection of blend transactions to include in the report.
Report End Date: Enter the end date for the selection of transactions to include in the report.
Refresh Report: Click to generate a preview of the report for review and validation.
Generate Report: Click to generate the Wine Premises Operations report in PDF format.

Note:

For lines with configured operation attributes, the system includes quantities from only the closed operations in the Blend Transactions table (F31B66).

21.3.5 Validating Wine Premises Operations Report Details

Access the Wine Premises Report Preview Revisions form and click Validate Data.

The system validates the column values against the aggregate values fetched from the Blend Transactions table (F31B66) and displays the discrepancies along with the error details in the Messages grid.

Figure 21-5 Wine Premises Report Preview Revisions form

Surrounding text describes Figure 21-5 .

To override a system-generated value, modify the value and save the form. The system updates the Wine Premises Report Preview table (F31B5120) with the new values.

21.3.6 Resolving Errors in the Wine Premises Operations Report Details

Access the Work With Wine Premises Report Preview form and enter the Winery, Report Start Date, and Report End Date. Then, click Refresh Report.

The system generates a preview of the report and displays the discrepancies in red colored font.

To rectify an error, click the value. The system displays the Report Configuration Revisions form with the attributes for the selected line item. Reset the attributes and click Save to resolve the errors.

21.4 Generating the Wine Premises Operations Report (Form 5120.17)

This section discusses the prerequisites for generating the Wine Premises Operations report and describes how to generate the Wine Premises Operations report.

21.4.1 Prerequisites

To generate the Wine Premises Operations report:

Define all the required lines of the Wine Premises Operations report, in the Wine Premises Report Setup program (P31B5101).
Set up the line configurations in the Wine Premises Report Setup program (P31B5101).

See "Setting Up the Wine Premises Operations Report".

21.4.2 Generating the Wine Premises Operations Report

Access the Work With Wine Premises Report Preview form and click Refresh Report. Enter the Winery, Report Start Date, and Report End Date, and generate the report preview. Now, click Generate Report.

Select the output option on the Report Output Destination form to generate the Wine Premises Operations report. The system uses Oracle's Business Intelligence (BI) Publisher to generate the report in PDF format.

You can also export the data to a comma-separated values (CSV) file by selecting the Export to CSV option on the Report Output Destination form.

When you click Generate report, the "On-hand end of period" quantities in the report are stored. The next time you run Refresh Report in final mode, these quantities become the "On-hand beginning of period" balances.

Note:

To add the previous report's on-hand end of period balance to your new report's beginning balance, set the report mode to Final in the processing options for the Wine Premises Report Preview program.

Figure 21-6 Wine Premises Operations Report - Form 5120.17

Surrounding text describes Figure 21-6 .