1. Operations Guide
  2. Planning and Size Profile Integration

3 Planning and Size Profile Integration

As noted above, Allocation has the ability to take in plan and size profile information from planning and optimization solutions. This is done by leveraging the integration programs described in this chapter. These jobs all use a flat file sent from the external solution, which is loaded using SQL Loader into Allocation and then processed into the Allocation tables.

Functional Overview

The extracts from a planning solution may contain up to four levels of plans or profiles, department level, class level, subclass level, or item level. All of these levels are contained in a single normalized file. Each record in the size profile file has a dedicated space and distinct position for department, class, subclass, item, store, diff1, diff2, diff3, diff4 and size profile quantity values. Each record in the plan file has a dedicated space and distinct position for department, class, subclass, item, store, diff1, diff2, diff3, diff4, EOW date, and plan quantity values. It is crucial that the records are mapped using the correct positions and space/padding rules for each data value.

  • Regardless of the level of financial plan/size profile, each record must include a store, diff value in one of the four diff value fields and a quantity value (including an EOW date for Plan only).

  • All the data values must start in the beginning of the corresponding field, and padding comes after the data to fill all the dedicated space for that data field.

  • Department, Class, and Subclass Level

    • Department-level financial plans or size profiles include a department data value in the dedicated department field. The class, subclass and item fields do not contain any values. They remain empty.

    • Class-level financial plans or size profiles include a department and class data value in the dedicated department and class fields. The subclass and item fields do not contain any values. They remain empty.

    • Subclass-level financial plans or size profiles include a department, class and subclass data value in the dedicated department, class and subclass fields. The item fields do not contain any values. They remain empty.

    • All of the department, class and subclass records contain only the non-aggregate diff values mapped from the specific diff value for items in Merchandising to the corresponding diff value in the export file. It is crucial that the non-aggregate diffs are mapped to the correct diff_id in the export file.

  • Item Level

    • Item-level profiles include aggregate level IDs in the dedicated item field. The item level export records contains both the aggregate and non-aggregate diff values mapped from the specific diff id for items in Merchandising to the associated diff position in the export file.

    • Item-Level financial plan or profiles include item data value in the dedicated item field. The department, class and subclass fields do not contain any values.

Processing

Processing involves reading the input files placed in the batch incoming folder, which is /u01/retail/rms/batch/incoming/alloc

  1. Generating a dat file based on the input files.

  2. The dat file is processed using sqlloader.

  3. The dat is loaded into a staging where the validation are performed.

  4. Inserting and updating the plan or profile records into the final destination ALC_PLAN, ALC_RECEIPT_PLAN, or ALC_SIZE_PROFILE table respectively.

  5. Update quantity when matched department, class, subclass, style, store, size1, size2, size3, size4.

  6. Otherwise, insert records.

Running the Module

These ad hoc jobs can be run from POM (Process Orchestration and Monitoring) batch scheduler.

Log File Location

/u01/retail/alloc/alloc-batch/batch/data/logs

Plan Data Import

Processing Job:

ALC_PLAN_JOB: Configured in POM as an ad hoc job and should be run on demand after placing the input files in the batch incoming folder as mentioned above.

Plan Data File Layout

Plan data is provided to the allocation interfaces as a flat file. This section describes the file format for uploading plan data.

File Name: p1prodlevel.NN

  • The file name should start with p1 followed by four characters for product level and the domain number. The four product levels acceptable are:

    • itpt - for item

    • scls - for subclass

    • clss - for class

    • dept - for department

  • NN is for the domain ID - this should always be 01.

Example: p1scls.01 (subclass, domain 01)

Table 3-1 Plan Data File Layout

Field Name Start Position Width Format Content

Product ID

1

25 char

Alpha

141410001000

Dept length=4

Class length=4

Subclass length=4

Or

Item length =25

Location ID

26

20 char

Alpha

10000000014

Diff IDs

46

48 char

Alpha

_CCOLOR31_SMEDIUM

EOW Date

94

8 char

Alpha

20051225

Quantity

102

12 char

Numeric

000000137500

  • The Product ID, Location ID and Diff IDs fields are left justified and blank filled.

  • Product Id can be dept/class/subclass or item.

  • The number of separate Diffs in the Diff IDs field is in the range: 0-4. The first character of each Diff is an "_" (underscore) and the second character is the Diff Type. No underscore characters are present in the Diff ID field other than the character that immediately precedes each separate Diff Type within the field. Each Diff in the Diff IDs field is lesser than 12 characters in length, including the leading underscore character and the Diff Type.

  • The EOW Date field is in the format YYYYMMDD.

  • The Quantity field is a right-justified, zero-padded numeric and the decimal point is omitted, but the quantity has a 4-digit decimal fraction part (for example, 13.75 would appear in the record as 000000137500).

  • Total length of each record is 113.

  • When uploading data the system updates the quantity if the record exists for the hierarchy/location/Diff_id/EOW data combination or it appends the record into the tables.

Example:

1414                     1000000000          _CCOLOR 01_SSZ SMALL_PPAT 04_ESCENT 01          19910130000000050000

Receipt Plan Data Import

Processing Job:

ALC_RECEIPT_PLAN_JOB: Configured In POM as an ad hoc job and should be run on demand after placing the input files in the batch incoming folder as mentioned above.

Receipt Plan Data File Layout

Receipt plan data is provided to the allocation interfaces as a flat file. This section describes the file format for uploading receipt plan data.

File Name: p1prodlevel.NN

  • The file name should start with p1 followed by four characters for product level and the domain number. The four product levels acceptable are:

    • itpt - for item

    • scls - for subclass

    • clss - for class

    • dept - for department

  • NN is for the domain ID - this should always be 01.

Example: p1scls.01 (subclass, domain 01)

Table 3-2 Receipt Plan Data File Layout

Field Name Start Position Width Format Content

Product ID

1

25 char

Alpha

141410001000

Dept length=4

Class length=4

Subclass length=4

Or

Item length =25

Location ID

26

20 char

Alpha

10000000014

Diff IDs

46

48 char

Alpha

_CCOLOR31_SMEDIUM

EOW Date

94

8 char

Alpha

20051225

Quantity

102

12 char

Numeric

000000137500

  • The Product ID, Location ID and Diff IDs fields are left justified and blank filled.

  • The number of separate Diffs in the Diff IDs field is in the range: 0-4. The first character of each Diff is an "_" (underscore) and the second character is the Diff Type. No underscore characters are present in the Diff ID field other than the character that immediately precedes each separate Diff Type within the field. Each Diff in the Diff IDs field is lesser than 12 characters in length, including the leading underscore character and the Diff Type.

  • The EOW Date field is in the format YYYYMMDD.

  • The Quantity field is a right-justified, zero-padded numeric and the decimal point is omitted, but the quantity has a 4-digit decimal fraction part (for example, 13.75 would appear in the record as 000000137500).

  • Total length of each record is 113.

  • When uploading data the system updates the quantity if the record exists for the hierarchy/location/Diff_id/EOW data combination or it appends the record into the tables.

Example:

1414                     1000000000          _CCOLOR 01_SSZ SMALL_PPAT 04_ESCENT 01          19910130000000050000

Size Profile Data Import

Processing Job:

ALC_SIZE_PROFILE_JOB: Configured In POM as an ad hoc job and should be run on demand after placing the input files in the batch incoming folder as mentioned above.

Size Profile File Layout

Size profile data is provided to the allocation interfaces as a flat file. This section describes the file format for uploading size profile data.

File Name: dXprodlevel.NN

  • The file name should start with letter d, X is diff number being sent followed by four characters for product level and the domain number. The four product levels acceptable are:

    • itpt - for item

    • scls - for subclass

    • clss - for class

    • dept - for department

  • The domain ID should be numeric.

Example File Name: d1scls.01 (diff 1, subclass, domain 01)

Table 3-3 Size Profile File Layout

Field Name Start Position Width Format Content

Product ID

1

25 char

Alpha

100045078

Dept length=4

Class length=4

Subclass length=4

Or

Item length =25

Location ID

26

20 char

Alpha

1000000002

Diff IDs

46

48 char

Alpha

CCOLOR01_S30x32

Quantity

94

12 char

Numeric

000000137500

  • The Product ID, Location ID and Diff IDs fields are left justified and blank filled.

  • The number of separate Diffs in the Diff IDs field is in the range: 0-4. The first character of each Diff is an "_" (underscore) and the second character is the Diff Type. No underscore characters is present in the Diff ID field other than the character that immediately precedes each separate Diff Type within the field. Each Diff in the Diff IDs field is lesser than 12 characters in length, including the leading underscore character and the Diff Type.

  • The Quantity field is a right-justified, zero-padded numeric and the decimal point is omitted, but the quantity has a 4-digit decimal fraction part (for example, 13.75 would appear in the record as 000000137500).

  • Total length of each record is 105.

  • When uploading data the system updates the quantity if the record exists for the hierarchy/location/Diff_id/EOW data combination or it appends the record into the tables.

Example:

100078404                1000000005          _C1_A4                                          030000000000
Seasonal Size Profiles

Oracle Retail Size Profile Optimization has the ability to create seasonal size profiles and multiple store size profiles, which are assigned a unique ID called a called a generation ID (GID). If available, these are displayed to the Allocation user as options. This ID, along with a user defined name will be displayed in the Allocation user interface.

The batch for SPO data file format is as follows:

<Beginning of file>
<GID>
<GID_DESC>
<End of File>

Size Profile GID text file (which must be named spo_gid_label.txt) is passed along with the batch of Size Profile Hierarchy dat file described above. The text file is used as the GID for that batch of dat file. Running Batch for SPO imports data to three tables after extraction.

Table 3-4 Size Profile Tables

Table Head Description

ALC_GID_HEADER

The ALC_GID_HEADER table holds all generation ID descriptions.

ALC_GID_PROFILE

The ALC_GID_PROFILE table holds all generation ID profile IDs.

ALC_SIZE_PROFILE

The ALC_SIZE_PROFILE table holds all size profiles at Style/Color, Style, Subclass, Class and Department levels.

Limitations

These three programs for receipt plan, sales plan, and size profile have the following limitations:

  • The diff type is supports a maximum of 1-character length.

  • The diff id is supports a maximum of 10-character length.