Get Reference Data for Sales Audit Import Processing (sagetref)
|
Module Name |
sagetref.pc |
|
Description |
Get Reference Data for Sales Audit Import Processing |
|
Functional Area |
Oracle Retail Sales Audit |
|
Module Type |
Integration |
|
Module Technology |
ProC |
|
Catalog ID |
RSA00 |
|
Wrapper Script |
batch_sagetref.ksh |
Design Overview
This program will fetch all reference information needed by SAIMPTLOG.PC for validation purposes and write this information out to various output files. The following files are produced:
-
Items - contains a listing of all items in the system.
-
Wastage - contains information about all items that have wastage associated with them.
-
Reference Items - contains reference items, or below transaction-level items.
-
Primary Variant - contains primary variant information.
-
Variable Weight UPC - contains all variable weight Universal Product Code (UPC) definitions in the system.
-
Store/Days - contains all of the valid store/day combinations in the system.
-
Codes and Code Types - contains all code types and codes used in field level validation.
-
Error Codes and Descriptions - contains all error codes, error descriptions, and systems affected by the error.
-
Store POS Mappings
-
Tender Types
-
Merchants
-
Partners
-
Suppliers
-
Sales Audit Employees
-
Banners
-
Currency Codes
-
Promotions
-
Warehouses
-
Inventory Statuses
These files will be used by the automated audit to validate information without repeatedly hitting the database.
When running sagetref.pc, retailers can either create and specify the output files, or create only the output that they desire. For example, a retailer interested in only creating a more recent employeefile would simply place a hyphen (-) in place of all the other parameters, but still specify an employeefile name. This technique can be applied to as many or as few of the parameters as retailers wish. Note, however, that the item-related files (itemfile, refitemfile, wastefile, and primvariantfile) contain significant interdependence. Thus, item files must all be created or not created together.
In the list of reference data files above, standard UOM is part of the itemfile. To obtain the value, Sales Audit converts the selling Unit of Measure (UOM) to the standard UOM during batch processing. This conversion enables Sales Audit to later export the standard UOM to the systems that require its use.
I/O Specification
|
Integration Type |
Download from Merchandising |
|
File Name |
Determined by runtime parameter |
|
Integration Contract |
IntCon000113 (itemfile) IntCon000114 (wastefile) IntCon000115 (refitemfile) IntCon000116 (primvariantfile) IntCon000117 (varupcfile) IntCon000118 (storedayfile) IntCon000119 (promfile) IntCon000120 (codesfile) IntCon000121 (errorfile) IntCon000122 (storeposfile) IntCon000123 (tendertypefile) IntCon000124 (merchcodesfile) IntCon000125 (partnerfile) IntCon000126 (supplierfile) IntCon000127 (employeefile) IntCon000128 (bannerfile) IntCon000129 (promfile) IntCon000130 (whfile) IntCon000131 (invstatusfile) |
File Name: Item File
The ItemFile file name (Itemfile) is not fixed; it is determined by a runtime parameter.
Table 19-3 Itemfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Item |
Char(25) |
N/A |
Item number |
|
Dept |
Number(4) |
N/A |
Department ID |
|
Class |
Number(4) |
N/A |
Class |
|
Subclass |
Number(4) |
N/A |
Subclass ID |
|
Standard UOM |
Char(4) |
N/A |
Standard Unit of Measure |
|
Catchweight Ind |
Char(1) |
N/A |
Catch weight indicator |
|
Class vat Ind |
Char(1) |
N/A |
Class Vat Ind |
File Name: Waste Data File
The Waste Data File file name (wastefile) is not fixed; it is determined by a runtime parameter.
Table 19-4 wastefile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Item |
Char(25) |
N/A |
Item number |
|
Waste type |
Char(6) |
N/A |
Waste type |
|
Waste pct |
Number(12,4) |
N/A |
Waste pct |
File Name: Reference Item Data
The Reference Item Data file name (ref_itemfile) is not fixed; it is determined by a runtime parameter.
Table 19-5 Ref_itemfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Ref Item |
Char(25) |
N/A |
Reference Item number |
|
Item |
Char(25) |
N/A |
Item number |
File Name: Primary Variant Data File
The Primary Variant Data File file name (prim_variantfile) is not fixed; it is determined by a runtime parameter.
Table 19-6 prim_variantfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Location |
Number(10) |
N/A |
Location number |
|
Item |
Char(25) |
N/A |
Item number |
|
Prim Variant |
Char(25) |
N/A |
Primary variant |
File Name: Variable Weight UPC Definition File
The Variable Weight UPC Definition File file name (varupcfile) is not fixed; it is determined by a runtime parameter.
Table 19-7 varupcfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Format Id |
Char(1) |
N/A |
Format ID |
|
Format desc |
Char(20) |
N/A |
Format description |
|
Prefix length |
Number(1) |
N/A |
Pefix Length |
|
Begin item digit |
Number(2) |
N/A |
Item digit begin |
|
Begin var digit |
Number(2) |
N/A |
Var digit begin |
|
Check digit |
Number(2) |
N/A |
Check digit |
|
Default prefix |
Number(1) |
N/A |
Default prefix |
|
Prefix |
Number(1) |
N/A |
Prefix |
File Name: Valid Store/Day Combination File
The Valid Store/Day Combination File file name (storedayfile) is not fixed; it is determined by a runtime parameter.
Table 19-8 storedayfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Store |
Number(10) |
N/A |
Store number |
|
Business date |
Char(8) |
N/A |
Business date in YYYYMMDD format |
|
Store day seq no |
Number(20) |
N/A |
Store day sequence number |
|
Day |
Number(3) |
N/A |
Day |
|
Tran no generated |
Char(6) |
N/A |
Generated transaction number |
|
POS data expected |
Char(1) |
N/A |
If system_code is POS, then Y; otherwise N |
|
Currency rtl dec |
Number(1) |
N/A |
Currency rtl dec |
|
Currency code |
Char(3) |
N/A |
Currency code |
|
Country id |
Char(3) |
N/A |
Country ID |
|
Vat Include Ind |
Char(1) |
N/A |
Vat Include Indicator |
File Name: Codes File
The Codes File file name (codesfile) is not fixed; it is determined by a runtime parameter.
Table 19-9 codefile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Code type |
Char(4) |
N/A |
Code type |
|
Code |
Char(6) |
N/A |
Code ID |
|
Code seq |
Number(4) |
N/A |
Code sequence |
File Name: Error Information File
The Error Information File file name (errorfile) is not fixed; it is determined by a runtime parameter.
Table 19-10 errorfile- File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Error code |
Char(25) |
N/A |
Error code |
|
System Code |
Char(6) |
N/A |
System Code |
|
Error desc |
Char(255) |
N/A |
Error description |
|
Rec solution |
Char(255) |
N/A |
Error rectify solution |
File Name: Store POS Mapping File
The Store POS Mapping File file name (storeposfile) is not fixed; it is determined by a runtime parameter.
Table 19-11 storeposfile- File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Store |
Number(10) |
N/A |
Store |
|
POS Type |
Char(6) |
N/A |
Point Of Sale type |
|
Start Tran No. |
Number(10) |
N/A |
Start transaction number |
|
End Tran No. |
Number(10) |
N/A |
End transaction number |
File Name: Tender Type Mapping File
The Tender Type Mapping File file name (tendertypefile) is not fixed; it is determined by a runtime parameter.
Table 19-12 tendertypefile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Group |
Char(6) |
N/A |
Tender type Group |
|
Id |
Number(6) |
N/A |
Tender type ID |
|
Desc |
Char(120) |
N/A |
Tender type description |
File Name: Merchant Code Mapping File
The Merchant Code Mapping File file name (merchcodesfile) is not fixed; it is determined by a runtime parameter.
Table 19-13 merchcodesfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Non Merch Code |
Char (6) |
N/A |
Non-Merchant Code |
File Name: Partner Mapping File
The Partner Mapping File file name (partnerfile) is not fixed; it is determined by a runtime parameter.
Table 19-14 partnerfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Partner Type |
Char(6) |
N/A |
Partner Type |
|
Partner Id |
Char(10) |
N/A |
Partner ID |
File Name: Supplier Mapping File
The Supplier Mapping File file name (supplierfile) is not fixed; it is determined by a runtime parameter.
Table 19-15 supplierfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Supplier |
Number(10) |
N/A |
Supplier ID |
|
Sup status |
Char(1) |
N/A |
Supplier status |
|
Supplier Parent |
Number(10) |
N/A |
Supplier Parent ID |
File Name: Employee Mapping File
The Employee Mapping File file name (employeefile) is not fixed; it is determined by a runtime parameter.
Table 19-16 employeefile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Store |
Number(10) |
N/A |
Store ID |
|
POS Id |
Char(10) |
N/A |
Point Of Sale ID |
|
Emp Id |
Char(10) |
N/A |
Employee ID |
File Name: Banner Information File
The Banner Information File file name (bannerfile) is not fixed; it is determined by a runtime parameter
Table 19-17 bannerfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Store |
Number(10) |
N/A |
Store ID |
|
Banner data |
Number(4) |
N/A |
Banner ID |
|
Stockholding Ind |
Char(1) |
N/A |
Stockholding Indicator |
|
Customer Order Loc Ind |
Char(1) |
Customer Order Location Indicator |
File Name: Currency Information File
The Currency Information File file name (currencyfile) is not fixed; it is determined by a runtime parameter.
Table 19-18 currencyfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Currency Code |
Char(1) |
N/A |
Currency Code |
File Name: Promotion Information File
The Promotion Information File file name (promfile) is not fixed; it is determined by a runtime parameter.
Table 19-19 promfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Promotion |
Number(10) |
N/A |
Promotion ID |
|
Component |
Number(10) |
N/A |
This contains the Offer ID value from Pricing. |
File Name: Warehouse Information File
The Warehouse Information File filename (whfile) is not fixed; it is determined by a runtime parameter.
Table 19-20 whfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
|
Warehouse |
Number(10) |
N/A |
Warehouse ID |
|
Physical Warehouse |
Number(10) |
N/A |
Physical Warehouse ID |
|
Customer Order Loc Ind |
Char(1) |
N/A |
Customer Order Location Indicator |
A Note about Primary Variant Relationships
Depending upon a retailer's system parameters, the retailer designates the primary variant during item setup (through the front-end) for several reasons. One of the reasons is that, in some cases, an item may be identified at the POS by the item parent, but the item parent may have several variants.
The primary variant is established through a form at the item location level. The retailer designates which variant item is the primary variant for the current transaction level item. For more information about the new item structure in Merchandising, see the Oracle Retail Merchandising System User Guide.
In the example shown in the diagram below, the retailer has established their transaction level as an Item Level 2.
Note:
The level of the primary variant is Item Level 1, and Item Level 3 is the sub-transaction level (the refitem).
The retailer set up golf shirts in the merchandising system as its Item Level 1 above the transaction level. The retailer set up two items at level 2 (the transaction level) based on size (small and medium).
Note:
The retailer assigned the level 2 items to all of the available locations (Minneapolis, China, and Fargo). The retailer also designated a primary variant for a single location - a medium golf shirt, in the case of Minneapolis, and a small golf shirt, in the case of China. The retailer failed to designate a primary variant for Fargo.
The primary variant affects Sales Audit in the following way. Sometimes a POS system does not provide Sales Audit with item level 2 (transaction item) data. For example, assume that the POS system in Minneapolis sold 10 medium golf shirts and 10 small golf shirts but only informed Sales Audit that 20 golf shirts were sold. 20 golf shirts presents a problem for Sales Audit because it can only interpret items at item level 2 (the transaction level). Thus, because medium golf shirts was the chosen primary variant for Minneapolis, the SAGETREF.PC module automatically transforms the 20 golf shirts into 20 medium golf shirts. If the same type of POS system in China informed Sales Audit of 20 golf shirts (instead of the 10 medium and 10 small that were sold), the sagetref.pc module would transform the 20 golf shirts sold in China into 20 small golf shirts. As the table shows, small golf shirts was the chosen primary variant for the China location. Sales Audit then goes on to export the data at the item 2 level (the transaction level) to, for example, a merchandising system, a data warehouse, and so on.
Note:
Depending upon system parameters, if a retailer fails to set up the primary variant for a location, an invalid item error is generated during batch processing. In the example below, if the POS system in Fargo sold 10 medium golf shirts and 10 small golf shirts, but only informed Sales Audit that 20 golf shirts were sold, the sagetref.pc module would not have a way to transform those 20 golf shirts to the transaction level. Because Sales Audit can only interpret items above the transaction level in conjunction with a primary variant, the invalid item error would occur during batch processing.
Figure 19-3 Primary Variant Relationships
