6 Scheduled Integration

This chapter provides a summary of integrations that are scheduled either to be run once per day or periodically throughout the day. There are generally two types of integrations - those that expect or produce files and those that move data between integration tables, also referred to as Bulk Data Integration (BDI).

File-based Integration Overview

Merchandising and Sales Audit require that all inbound and outbound file-based transactions adhere to standard file layouts. There are two types of file layouts: detail-only and master-detail, which are described in the sections below.

Standard File Layouts

The Merchandising interface library supports two standard file layouts: one for master/detail processing, and one for processing detail records only. True sub-details are not supported within the Merchandising base package interface library functions.

A 5-character identification code or record type identifies all records within an I/O file, regardless of file type. The following includes common record type values:

  • FHEAD-File Header

  • FDETL-File Detail

  • FTAIL-File Tail

  • THEAD-Transaction Header

  • TDETL-Transaction Detail

  • TTAIL-Transaction Tail

Each line of the file must begin with the record type code followed by a 10-character record ID.

Detail-Only Files

File layouts have a standard file header record, a detail record for each transaction to be processed, and a file trailer record. Valid record types are FHEAD, FDETL, and FTAIL.

Example 6-1 Detail-Only Files

FHEAD0000000001STKU1996010100000019960929
FDETL0000000002SKU100000040000011011
FDETL0000000003SKU100000050003002001
FDETL0000000004SKU100000050003002001
FTAIL00000000050000000003
Master and Detail Files

File layouts consists of:

  • Standard file header record

  • Set of records for each transaction to be processed

  • File trailer record.

The transaction set consists of:

  • Transaction set header record

  • Transaction set detail for detail within the transaction

  • Transaction trailer record

Valid record types are FHEAD, THEAD, TDETL, TTAIL, and FTAIL.

Example 6-2 Master and Detail Files

FHEAD0000000001RTV 19960908172000
THEAD000000000200000000000001199609091202000000000003R
TDETL000000000300000000000001000001SKU10000012
TTAIL0000000004000001
THEAD000000000500000000000002199609091202001215720131R
TDETL000000000600000000000002000001UPC400100002667
TDETL0000000007000000000000020000021UPC400100002643 0
TTAIL0000000008000002
FTAIL00000000090000000007
Record Name Field Name Field Type Default Value Description

File Header

File Type Record Descriptor

Char(5)

FHEAD

Identifies file record type.

File Line Identifier

Number(10)

Specified by external system

Line number of the current file.

File Type Definition

Char(4)

N/A

Identifies transaction type.

File Create Date

Date

Create date

Date file was written by external system.

Transaction Header

File Type Record Descriptor

Char(5)

THEAD

Identifies file record type.

File Line Identifier

Number(10)

Specified by external system

Line number of the current file.

Transaction Set Control Number

Char(14)

Specified by external system

Used to force unique transaction check.

Detail Sequence Number

Char(6)

Specified by external system

Sequential number assigned to detail records within a transaction.

Transaction Detail

File Type Record Descriptor

Char(5)

TDETL

Identifies file record type.

File Line Identifier

Number(10)

Specified by external system

Line number of the current file.

Transaction Set Control Number

Char(14)

Specified by external system

Used to force unique transaction check.

Detail Sequence Number

Char(6)

Specified by external system

Sequential number assigned to detail records within a transaction.

Transaction Trailer

File Type Record Descriptor

Char(5)

TTAIL

Identifies file record type.

File Line Identifier

Number(10)

Specified by external system

Line number of the current file.

Transaction Detail Line Count

Number(6)

Sum of detail lines

Sum of the detail lines within a transaction.

File Trailer

File Record Type Descriptor

Char(5)

FTAIL

Identifies file record type.

File Line Identifier

Number(10)

Specified by external system

Line number of the current file.

Total Transaction Line Count

Number(10)

Sum of all transaction lines

All lines in file less the file header and trailer records.

Integration Batch Wrapper FAQ

See also the "Batch Wrapper Overview" in Chapter 1 of the Merchandising Operations Guide, Volume 1.

For input files, are there any specific file format names?

Usually, the input file pattern is the same as the batch name. This can be seen in the ParameterValue column of the Job tab in the Batch Schedule spreadsheet. For example, dealupld has this parameter value:

dealupld #SysOpt.dbwallet dealupld

The input file pattern is the 3rd parameter (dealupld) so the filename should have dealupld in it (for example, dealupld_input, input_dealupld, and so on). There are some batch programs that should start with a specific pattern like saimptlogi - the input file should start with RTLOG*. This can be seen as well in the Batch Schedule spreadsheet:

#SysOpt.dbwallet RTLOG
How and where will files be moved?

The input file (zipped) should be in the SFTP batch/incoming folder.

There are two ways to SFTP the input files. For bulk upload, ensure that a COMPLETE file is present in the batch/incoming/COMMAND folder. For individual file upload, ensure that each input file has a corresponding. complete file (for example, RTLOG_1521.zip will have a corresponding RTLOG_1521.zip.complete file in the batch/incoming folder).

The batch wrappers will take care of moving this to the input folder (data/in) given that the filename of the zip file corresponds to the expected file name pattern.

When will files be unzipped?

This will be unzipped when it is moved to the input folder (data/in).

What will happen to the files once they are processed?

Successfully processed files will be move to the processed folder (data/processed). Files with errors will remain in the input folder (data/in) and corresponding errors will be logged in the error log.

Will processed file be archived?

Yes. Input files will be archived in the processed folder (data/processed), while any output file will be archived in the archive folder (data/archive)

What will happen if the file is rejected?

Reject files will be created as applicable and they will be sent to the SFTP outgoing folder (batch/outgoing) as well as archived in the archive folder (data/archive). Depending on the batch, if the program completes successfully even with rejected records then the rejected input file will be moved to the processed folder (data/processed). But if the batch aborts, then the rejected input file will remain in the input folder (data/in).

How will rejected files be reprocessed?

Reprocessing the files would require manual intervention. Remove the files with errors from the input folder (data/in). Those input files would need to be corrected and resent.

File Movement Flow

Basic Flow for Files Through Batch Wrapper

Input/Output File Naming Convention

Batch Input File Name Zip File Name

dealupld

*dealupld*

*dealdupld*.zip

ediupack

*ediupack*

*ediupack*.zip

ediupavl

*ediupavl*

*ediupavl*.zip

lcup798

*lcup798*

*lcup798*.zip

lcupld

*lcupld*

*lcupld*.zip

cmpupld

*cmpupld*

*cmpupld*.zip

fcosttmplupld.ksh

*fcosttmplupld*

*fcosttmplupld*.zip

htsupld

*htsupld*

*htsupld*.zip

otbupld

*otbupld*

*otbupld*.zip

tranupld

*tranupld*

*tranupld*.zip

fcustomerupload.ksh

*fcustomerupload*

*fcustomerupload*.zip

iindbatch.ksh

Input file name is provided by user. Must end in *.xml

Template name must correspond to S9T_TEMPLATE.TEMPLATE_KEY

*<input file name>*.zip

poindbatch.ksh

Input file name is provided by user. Must end in *.xml

Template name must correspond to S9T_TEMPLATE.TEMPLATE_KEY

*<input file name>*.zip

replindbatch.ksh

Input file name is provided by user. Must end in *.xml

Template name must correspond to S9T_TEMPLATE.TEMPLATE_KEY

*<input file name>*.zip

saimpadj

*saimpadj*

*saimpadj*.zip

load_item_forecast.ksh

*demand*

*demand*.zip

lcmt700

*lcmt700*

*lcmt700*.zip

lcmt707

*lcmt707*

*lcmt707*.zip

resa2sim

SIMT*

No zip file.

Input file is from saexpsim.

resa2dw

RDWT*

RDWF*

RDWS*

RDWC*

No zip file.

Input file is from saexpdw.

lcmt730

*lcmt730*

*lcmt730*.zip

lcmt798

*lcmt798*

*lcmt798*.zip

lifstkup

*lifstkup*

*lifstkup*.zip

ordinvupld

ORIN*

ORIN*.zip

sa_rules_total_upload.ksh

sartexp_<table name>*

sartexp*.zip

saimptlogfin

storedayfile

No zip file.

Input file is from sagetref.

saimptlogi

RTLOG_<store>_<datetime>.dat

RTLOG*.zip

saimptlogtdup_upd

storedayfile

storeposfile

No zip file.

Input file is from sagetref.

savouch

*savouch*

*savouch*.zip

stlgdnld

*stlgdnld*

*stlgdnld*.zip

stockcountupload.ksh

STK*.<file extension>

STK*.zip

trandataload.ksh

*trandataload*

*trandataload*.zip

uploadsales.ksh

POSU_<store>_<tran_date>_ <sysdate>.<thread_val>

POSU*.zip

wfslsupld.ksh

*wfslsupld*

*wfslsupld*.zip

wfordupld.ksh

wford*.dat

wford*.zip

wfretupld.ksh

wfreturn*.dat

wfreturn*.zip

Bulk Data Integration Overview

Oracle Bulk Data Integration (BDI) is a solution that is part of the Oracle Retail Integration Cloud Service that defines the architecture and infrastructure used to move bulk data among Oracle Retail applications.

In a Bulk Data Integration system, message families are represented as interface modules. Each interface module (for example, DiffGrp_Fnd) contains a Merchandising component that takes care of pulling and staging data for publication to the External BDI system. Interface modules are divided by functional entity (for example, Item Master, Stores, Diffs, and so on).

For more information on BDI, see the Oracle Retail Integration Cloud Service documentation on docs.oracle.com/retail.

Outbound Scheduled Integration

This section provides a summary of integrations that are scheduled either to be run once per day or periodically throughout the day to send data from Merchandising or Sales Audit to another solution. It includes both file-based and BDI-based integrations.

Foundation Data

Merchandising publishes foundation data for many other solution areas, including stores, warehouses, omni-channel, and so on.

The following scheduled outbound integrations are included in this functional area:

Brand Publication API (BDI_Brand_Fnd_PF_From_RMS_EOW_JOB)

This section describes the Brand Publication BDI.

Functional Area

Foundation

Design Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Brand information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Bulk Interface Module

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.BRAND_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                            O_control_id    IN OUT NUMBER,
                            I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Brand table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Brand

Brand upload to BDI

Brand_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

BRAND_OUT

No

Yes

No

No

BRAND

Yes

No

No

No

Calendar Publication API (BDI_Calendar_Fnd_PF_From_RMS_EOW_JOB)

This section describes the Calendar Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Calendar information (2 prior years, current year, 2 future years) from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.CALENDAR_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,                               O_control_id    IN OUT NUMBER,                               I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising V_BDI_DAY_LEVEL_CALENDAR view.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Calendar

Calendar upload to BDI

Calendar_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

CALENDAR_OUT

No

Yes

No

No

V_BDI_DAY_LEVEL_CALENDAR

Yes

No

No

No

Code Detail Publication API (BDI_CodeDetail_Fnd_PF_From_RMS_JOB)

This section describes the Code Detail Publication BDI.

Functional Area

Cross Pillar

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Detail information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.CODE_DETAIL_UP(
                        O_error_message  IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
                        O_control_id     IN OUT  NUMBER,
                        I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function updates the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising CODE_DETAIL table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This updates the internal BDI control tables.

A database commit is issued, and the control ID is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Code Detail

Code Detail upload to BDI

CodeDetail_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

CODE_DETAIL_OUT

No

Yes

No

No

CODE_DETAIL

Yes

No

No

No

Code Head Publication API (BDI_CodeHead_Fnd_PF_From_RMS_JOB)

This section describes the Code Head Publication BDI.

Functional Area

Cross Pillar

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.CODE_HEAD_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                                  O_control_id    IN OUT NUMBER,
                                  I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising CODE_HEAD table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Code Head

Code Head upload to BDI

CodeHead_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

CODE_HEAD_OUT

No

Yes

No

No

CODE_HEAD

Yes

No

No

No

Company-wide Closings and Company Closed Exceptions (BDI_CompanyClosed_Fnd_PF_From_RMS_JOB)

This section describes the Company-wide Closings and Company Closed Exceptions Publication BDI.

Functional Area

Foundation

Design Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Store information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

The following packages are impacted:

Filename: bdifoundations.pls

BDI_FOUNDATION_SQL.COMPANY_CLOSED_UP (
                           O_error_message   IN OUT   RTK_ERRORS.RTK_TEXT%TYPE,
                           O_control_id      IN OUT   NUMBER,
                           I_job_context     IN       VARCHAR2)

Filename: bdifoundationb.pls

BDI_FOUNDATION_SQL.COMPANY_CLOSED_UP (
                           O_error_message   IN OUT   RTK_ERRORS.RTK_TEXT%TYPE,
                           O_control_id      IN OUT   NUMBER,
                           I_job_context     IN       VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising company closed and company closed exception table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Company Closed

Company Closed upload to BDI

CompanyClosed_Fnd_BdiInterfaceModule.xml

Company Closed Exceptions

Company Closed Exceptions upload to BDI

CompanyClosedExcep_Fnd_BdiInterfaceModule.xml

Tables
TABLE SELECT INSERT UPDATE DELETE

COMPANY_CLOSED_OUT

No

Yes

No

No

COMPANY_CLOSED_EXCEP_OUT

No

Yes

No

No

COMPANY_CLOSED_ECXEP

Yes

No

No

No

COMPANY_CLOSED

Yes

No

No

No

Currency Conversion Rates Publication API (BDI_CurrConvRates_Fnd_PF_From_RMS_EOW_JOB)

This section describes the Currency Conversion Rates Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Currency conversion rates information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.CURR_CONV_RATES_UP(
                          O_error_message IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
                          O_control_id    IN OUT  NUMBER,
                          I_job_context   IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising MV_CURRENCY_CONVERSION_RATES materialized view.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Currency Conversion Rates

Currency Conversion Rates upload to BDI

CurrConvRates_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

CURR_CONV_RATES_OUT

No

Yes

No

No

MV_CURRENCY_CONVERSION_RATES

Yes

No

No

No

SYSTEM_OPTIONS

Yes

No

No

No

STORE

Yes

No

No

No

WH

Yes

No

No

No

Delivery Slot Publication API (BDI_DeliverySlot_Fnd_PF_From_RMS_JOB)

This section describes the Delivery Slot Publication BDI.

Functional Area

Cross Pillar

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Delivery Slot information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.DELIVERY_SLOT_UP (
                        O_error_message  IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
                        O_control_id     IN OUT  NUMBER,
                        I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising DELIVERY_SLOT table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Delivery Slot

Delivery Slot upload to BDI

DeliverySlot_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

DELIVERY_SLOT_OUT

No

Yes

No

No

DELIVERY_SLOT

Yes

No

No

No

Diff Group Export (export_diffgrp.ksh)

Module Name

export_diffgrp.ksh

Description

Extraction of differentiator groups data.

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

RMS255

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising diff group information into a flat file. Data to be extracted will be pulled off from the differentiator group tables.The mode (full vs. delta) will be an input parameter for this batch. The mode will allow a full extract (all diff group records in Merchandising) as well as delta processing (all diff group record changes in the time frame passed in the program) of data. For a full extract, records will be retrieved from the differentiator group tables. For a delta extract, the action type and diff group ID will be retrieved from the differentiator group staging export table and the attributes will be retrieved from the differentiator group tables.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

diffgrphdr_date_[full/delta]_[#ofLines].dat

diffgrpdtl_date_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000212.html

IntCon000213.html

Design Assumptions

N/A

Diff Group Publication API (BDI_DiffGrp_Fnd_PF_From_RMS_JOB)

This section describes the Diff Group Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Diff Groups from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.DIFF_GROUP_UP(O_error_message  IN OUT  VARCHAR2,
                                   O_control_id     IN OUT  NUMBER,
                                   I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound tables that reside in the BDI_RMS_INT_SCHEMA schema. These outbound tables are loaded with records from the Merchandising Diff Group head and detail tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Diff Group

Diff Group upload to BDI

DiffGrp_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

DIFF_GRP_OUT

No

Yes

No

No

DIFF_GRP_DTL_OUT

No

Yes

No

No

DIFF_GROUP_HEAD

Yes

No

No

No

DIFF_TYPE

Yes

No

No

No

DIFF_GROUP_DETAIL

Yes

No

No

No

Diff ID and Type Export (export_diffs.ksh)

Module Name

export_diffs.ksh

Description

Extraction of differentiator's data defined for a differentiator type.

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

256

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising differentiator information into a flat file. Data to be extracted will be pulled off from the differentiator extract staging and the differentiator IDs table.

The mode (full vs. delta) will be an input parameter for this batch. The mode will allow a full extract (all differentiator records in Merchandising) as well as delta processing (all differentiator record changes in the time frame passed in the program) of data.

For a full extract, records will be solely retrieved from the differentiator IDs table. For a delta extract, the action type and differentiator ID will be retrieved from the differentiator export staging table and the attributes will be retrieved from the differentiator IDs table.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

diffs_date_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000206.html

Design Assumptions

N/A

Diff ID Publication API (BDI_Diff_Fnd_PF_From_RMS_JOB)

This section describes the Diff ID Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Diff IDs from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.DIFF_UP(O_error_message  IN OUT  VARCHAR2,
                             O_control_id     IN OUT  NUMBER,
                             I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Diff tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Type Description XML Schema Definition (XSD)

Diff Id

Diff Id upload to BDI

Diff_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

DIFF_OUT

No

Yes

No

No

DIFF_IDS

Yes

No

No

No

DIFF_TYPE

Yes

No

No

No

Finisher Address Publication API (BDI_FinisherAddr_Fnd_PF_From_RMS_JOB)

This section describes the Finisher Address Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Finisher Address positions from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package

Package Impact

Filename: bdifoundations/b.pls

BDI_FOUNDATION_SQL.FINISHER_ADDR_UP(O_error_message  IN OUT  VARCHAR2,
                                    O_control_id     IN OUT  NUMBER,
                                    I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Finisher Address tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Finisher Address

Finisher Address upload to BDI

FinisherAddr_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

FINISHER_ADDR_OUT

No

Yes

No

No

ADD_TYPE_MODULE

Yes

No

No

No

WH

Yes

No

No

No

V_ADD_TYPE_TL

Yes

No

No

No

COUNTRY

Yes

No

No

No

STATE

Yes

No

No

No

ADDR

Yes

No

No

No

PARTNER

Yes

No

No

No

Location Closed Publication API (BDI_LocClosed_Fnd_PF_From_RMS_JOB)

This section describes the Location Closed Publication BDI.

Functional Area

Foundation

Design Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Store information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

The following packages are impacted by this BDI:

Bulk Interface Module

The following build interface module packages are impacted:

Filename: bdifoundations.pls

FUNCTION LOCATION_CLOSED_UP(O_error_message   IN OUT   RTK_ERRORS.RTK_TEXT%TYPE,
                            O_control_id      IN OUT   NUMBER,
                            I_job_context     IN       VARCHAR2)

Filename: bdifoundationb.pls

FUNCTION LOCATION_CLOSED_UP(O_error_message   IN OUT   RTK_ERRORS.RTK_TEXT%TYPE,
                            O_control_id      IN OUT   NUMBER,
                            I_job_context     IN       VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Location closed table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Location Closed

Location Closed upload to BDI

LocationClosed_Fnd_BdiInterfaceModule.xml

Tables
TABLE SELECT INSERT UPDATE DELETE

LOCATION_CLOSED_OUT

No

Yes

No

No

LOCATION_CLOSED

Yes

No

No

No

Merch Hierarchy Publication API (BDI_MerchHier_Fnd_PF_From_RMS_JOB)

This section describes the Merch Hierarchy Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Merchandise Hierarchy information from Merchandising to other Oracle Retail Applications.

On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdimerchb.pls

BDI_MERCH_SQL.MERCH_HIER_UP(O_error_message  IN OUT  VARCHAR2,
                            O_control_id     IN OUT  NUMBER,
                            I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Merchandise Hierarchy tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Merchandise Hierarchy

Merchandise Hierarchy upload to BDI

MerchHier_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

MERCH_HIER_OUT

No

Yes

No

No

DIVISION

Yes

No

No

No

COMPHEAD

Yes

No

No

No

GROUPS

Yes

No

No

No

DEPS

Yes

No

No

No

CLASS

Yes

No

No

No

SUBCLASS

Yes

No

No

No

Merchandise Hierarchy Export (export_merchhier.ksh)

Module Name

export_merchhier.ksh

Description

Extraction of merchandise hierarchy data.

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

260

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising merchandise hierarchy information from division to subclass into a flat file. Data to be extracted will be pulled off from the merchandise hierarchy export staging table and the main merchandise hierarchy tables. The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all merchandise hierarchy records in Merchandising) as well as delta processing (all merchandise hierarchy changes since the last export) of data.For a full extract, records will be solely retrieved from the main merchandise hierarchy tables. For a delta extract, the action type and entity ID will be retrieved from the merchandise hierarchy export staging table and the attributes of the entities will be retrieved from their corresponding man entity tables.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

merchhierarchy_[Date]_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000207

Design Assumptions

N/A

Organization Hierarchy Publication API (BDI_OrgHier_Fnd_PF_From_RMS_JOB)

This section describes Organization Hierarchy Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Org Hierarchy information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiorgb.pls

BDI_ORG_SQL.ORG_HIER_UP(O_error_message  IN OUT  VARCHAR2,
                        O_control_id     IN OUT  NUMBER,
                        I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Organization Hierarchy tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Org Hierarchy

Org Hierarchy upload to BDI

OrgHier_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ORG_HIER_OUT

No

Yes

No

No

WH

Yes

No

No

No

AREA

Yes

No

No

No

CHAIN

Yes

No

No

No

COMPHEAD

Yes

No

No

No

DISTRICT

Yes

No

No

No

REGION

Yes

No

No

No

STORE

Yes

No

No

No

WH

Yes

No

No

No

Organizational Hierarchy Export (export_orghier.ksh)

Module Name

export_orghier.ksh

Description

Extraction of organizational hierarchy data.

Functional Area

Foundation

Module Type

Integration

Module Technology

Ksh

Catalog ID

RMS261

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising organizational hierarchy information from company to stores and warehouses into a flat file. Data to be extracted will be pulled off from the organizational hierarchy export staging table and the main organizational hierarchy tables. The mode (full vs. delta) will be an input parameter for this batch. The mode will allow a full extract (all organizational hierarchy records in Merchandising) as well as delta processing (all organizational hierarchy changes since the last export) of data.For a full extract, records will be solely retrieved from the main organizational hierarchy tables. For a delta extract, the action type and entity ID will be retrieved from the organizational hierarchy export staging table and the attributes of the entities will be retrieved from their corresponding man entity tables.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

orghierarchy_[Date]_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000203

Design Assumptions

N/A

Partner Address Publication API (BDI_PartnerAddr_Fnd_PF_From_RMS_JOB)

This section describes the Partner Address Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.PARTNER_ADDR_UP(
                               O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                               O_control_id    IN OUT NUMBER,
                               I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Partner Address table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Partner Address

Partner Address upload to BDI

PartnerAddr_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

PARTNER_ADDR_OUT

No

Yes

No

No

ADDR

Yes

No

No

No

V_ADD_TYPE_TL

Yes

No

No

No

ADD_TYPE_MODULE

Yes

No

No

No

STATE

Yes

No

No

No

COUNTRY

Yes

No

No

No

PARTNER

Yes

No

No

No

Partner Org Unit Publication API (BDI_PartOrgUnit_Fnd_PF_From_RMS_JOB)

This section describes the Partner Org Unit Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.PARTNER_ORG_UNIT_UP(
                         O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                         O_control_id    IN OUT NUMBER,
                         I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Partner Org Unit table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Partner Org Unit

Partner Org Unit upload to BDI

PartnerOrgUnit_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

PARTNER_ORG_UNIT_OUT

No

Yes

No

No

PARTNER_ORG_UNIT

Yes

No

No

No

Partner Publication API (BDI_Partner_Fnd_PF_From_RMS_JOB)

This section describes the Partner Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.PARTNER_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                              O_control_id    IN OUT NUMBER,
                              I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API. A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Partner table. After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Partner

Partner upload to BDI

Partner_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

PARTNER_OUT

No

Yes

No

No

PARTNER

Yes

No

No

No

Store Address Publication API (BDI_StoreAddr_Fnd_PF_From_RMS_JOB)

This section describes the Store Address Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Store Address information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiorgb.pls

BDI_ORG_SQL.STORE_ADDR_UP(O_error_message  IN OUT  VARCHAR2,
                          O_control_id     IN OUT  NUMBER,
                          I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Store Address table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Store Addr

Store Address upload to BDI

StoreAddr_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

STORE_ADDR_OUT

No

Yes

No

No

V_ADD_TYPE_TL

Yes

No

No

No

ADDR

Yes

No

No

No

STORE

Yes

No

No

No

STATE

Yes

No

No

No

COUNTRY

Yes

No

No

No

ADD_TYPE_MODULE

Yes

No

No

No

Store Hours Publication API (BDI_StoreHours_Fnd_PF_From_RMS_JOB)

This section describe the Store Hours Publication BDI.

Function Area

Foundation

Design Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Store information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

The following packages are impacted by the Store Hours Publication BDI:

Bulk Interface Module

In the Build Interface Module:

Filename: bdiorgb.pls

BDI_ORG_SQL.STORE_HOURS_UP(O_error_message  IN OUT  VARCHAR2,
                           O_control_id     IN OUT  NUMBER,
                           I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Store table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Store

Store upload to BDI

StoreHours_Fnd_BdiInterfaceModule.xml

Tables
TABLE SELECT INSERT UPDATE DELETE

STORE_HOURS_OUT

No

Yes

No

No

STORE_HOURS

Yes

No

No

No

Store Publication API (BDI_Store_Fnd_PF_From_RMS_JOB)

This section describes the Store Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Store information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiorgb.pls

BDI_ORG_SQL.STORE_UP(O_error_message  IN OUT  VARCHAR2,
                     O_control_id     IN OUT  NUMBER,
                     I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Item Location table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Store

Store upload to BDI

Store_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

STORE_OUT

No

Yes

No

No

CODE_DETAIL

Yes

No

No

No

CHANNELS

Yes

No

No

No

STORE_FORMAT

Yes

No

No

No

LANG

Yes

No

No

No

VAT_REGION

Yes

No

No

No

TSFZONE

Yes

No

No

No

Stores Export (export_stores.ksh)

Module Name

export_stores.ksh

Description

Extraction of store data.

Functional Area

Foundation

Module Type

Integration

Module Technology

Ksh

Catalog ID

RMS263

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising store information into two flat files - one for store and one for store addresses. Data to be extracted will be pulled from the store export staging, store and address tables. The mode (full vs. delta) will be an input parameter for this batch. The mode will allow a full extract (all store records in Merchandising) as well as delta processing (all store changes since the last export) of data.For a full extract, records will be solely retrieved from the store table for store information and address table for store addresses. For a delta extract, the action type, store ID and address will be retrieved from the store export staging table and the details of the store will be retrieved from both the store and address tables.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

store_[Date]_[full/delta]_[#ofLines].datstoreaddr_[Date]_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000204

IntCon000205

Design Assumptions

N/A

Supplier Address Publication API (BDI_SupplierAddr_Fnd_PF_From_RMS_JOB)

This section describes the Supplier Address Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Supplier Address positions from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundations/b.pls

BDI_FOUNDATION_SQL.SUPPLIER_ADDR_UP(O_error_message  IN OUT  VARCHAR2,
                                    O_control_id     IN OUT  NUMBER,
                                    I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Supplier Address tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Supplier Address

Supplier Address upload to BDI

SupplierAddr_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

SUPPLIER_ADDR_OUT

No

Yes

No

No

ADDR

Yes

No

No

No

V_ADD_TYPE_TL

Yes

No

No

No

STATE

Yes

No

No

No

COUNTRY

Yes

No

No

No

SUPS

Yes

No

No

No

ADD_TYPE_MODULE

Yes

No

No

No

Sups Publication API (BDI_Supplier_Fnd_PF_From_RMS_JOB)

This section describes the Sups Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.SUPS_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                           O_control_id    IN OUT NUMBER,
                           I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Sups table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Supplier

Supplier upload to BDI

Supplier_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

SUPS_OUT

No

Yes

No

No

SUPS

Yes

No

No

No

Tax Download - Brazil (taxdnld)

Module Name

taxdnld

Description

Tax Download to 3rd Party POS in Global Tax [GTAX] Implementations

Functional Area

Integration - 3rd Party POS

Module Type

Integration

Module Technology

ProC

Catalog ID

RMS124

Wrapper Script

N/A

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program downloads the tax information to 3rd Party POS systems when the Merchandising default tax type is GTAX.This program only needs to be run if the client uses Merchandising Global Tax functionality.

Restart/Recovery

The logical unit of work for this module is defined by item, ref_item and store combination. This batch program uses table-based restart/recovery. The commit happens in the database when the commit max counter is reached.

Integration Contract

Integration Type

Download from Merchandising

File Name

Determined by runtime parameter

Integration Contract

IntCon000020

Output File Layout

Table 6-1 Output File Layout

Record Name Field Name Field Type Default Value Description

FHEAD

File Type Record Descriptor

Char(5)

FHEAD

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

File Type Definition

Char(4)

TAXD

Identifies file as 'Tax Details'

File Create Date

Char(14)

create date

Vdate in 'YYYMMDDHHMISS'format

FDETL

FDETL

Char(5)

FDETL

FDETL

File Line Sequence

Number(10)

N/A

Line number of the current file

STORE

Char(10)

N/A

Store number

ITEM

Char(25)

N/A

Item

item_number_type

Char(6)

S - Store

W - Warehouse

Item number type

format_id

Char(1)

N/A

Format id

prefix

Char(2)

N/A

Prefix

ref_item

Char(25)

N/A

Reference Item

ref_item_number_type

Char(6)

N/A

Refrerence item number type

ref_format_id

Char(1)

N/A

Ref format id

ref_prefix

Char(2)

N/A

Ref no. prefix

taxable indicator

Char(1)

N/A

Taxable indicator

class_vat_ind

Char(1)

N/A

Class vat indicator

FTAXD

FTAXD

Char(5)

FTAXD

FTAXD

File Line Sequence

Number(10)

N/A

Line number of the current file

tax_code

Char(10)

N/A

Tax code

tax_rate

Char(20)

N/A

Tax rate

calculation_basis

Char(1)

N/A

Calculation basis

tax_amount

Char(20)

N/A

Tax amount

effective_from

Char(8)

N/A

Effective from

time

Char(6)

N/A

Time

status

Char(1)

N/A

Status

FTAIL

File Type Record Descriptor

Char(5)

FTAIL

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

rec_counter

Number(10)

N/A

Record counter

Design Assumptions

N/A

Ticket Download (tcktdnld)

Module Name

tcktdnld.pc

Description

Download of Data to be Printed on Tickets

Functional Area

Foundation Data

Module Type

Integration

Module Technology

PROC

Catalog ID

RMS59

Wrapper Script

rmswrap_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This program creates an output file containing the information to be printed on a ticket or label for a particular item/location. This program is driven by the requests for tickets generated from Merchandising and Pricing. The details of what should be printed on each ticket are defined in Merchandising on the ticket type details table.

Restart/Recovery

N/A

I/O Specification

Integration Type

Download from Merchandising

File Name

Determined by runtime parameters

Integration Contract

IntCon000107

Output File Layout

Table 6-2 tcktdnld.pc - Output File Layout

Record Name Field Name Field Type Default Value Description

FHEAD

File Type Record Descriptor

Char(5)

FHEAD

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

File Type Definition

Char(4)

TCKT

Identifies file as ‘Print Ticket Requests'

File Create Date

Char(14)

N/A

The date on which the file was created in ‘YYYMMDDHHMISS' format

THEAD

File Type Record Descriptor

Char(5)

THEAD

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

ITEM

Char(25)

N/A

ID number of the transaction level item for which the ticket applies.

Ticket Type

Char(4)

N/A

ID which indicates the ticket type to be printed

Location Type

Char(1)

N/A

Identifies the type of location for which tickets will be printed. Valid values are store (S) and warehouse (W).

Location

Char(10)

N/A

The ID of the store or warehouse for which tickets will be printed

Quantity

Number(12,4)

N/A

The quantity of tickets to be printed; which includes 4 implied decimal places

TCOMP

File Type Record Descriptor

Char(5)

TCOMP

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

ITEM

Char(25)

N/A

ID number of the item which is only populated if the item in THEAD is a pack item

Quantity

Number(12,4)

N/A

Quantity of the component item as a part of the pack; includes 4 implied decimal places

TDETL

File Type Record Descriptor

Char(5)

TDETL

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

Detail Sequence Number

Number(10)

N/A

Sequential number assigned to the detail records

Ticket Item

Char(4)

N/A

ID indicating the detail to be printed on the ticket. If the attribute is a UDA, then this will contain the ID of the UDA. Otherwise, it is the code associated with the attribute in Merchandising (such as, CLSS = class)

Attribute Description

Char(120)

N/A

Description of the attribute – either the UDA description or the Merchandising description for the attribute

Value

Char(250)

N/A

Detail to be printed on the ticket (for example:. Item number, Department Number, Item description)

Supplement

Char(120)

N/A

Supplemental description to the Value (for example: Department Name)

TTAIL

File Type Record Descriptor

Char(5)

TTAIL

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

Transaction Detail Line Count

Number(6)

sum of detail lines

Sum of the detail lines within a transaction

FTAIL

File Type Record Descriptor

Char(5)

FTAIL

Identifies file record type

File Line Sequence

Number(10)

N/A

Line number of the current file

Design Assumptions

N/A

UDA Publication API (BDI_Uda_Fnd_PF_From_RMS_JOB)

This section describes the UDA Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls

BDI_FOUNDATION_SQL.UDA_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                          O_control_id    IN OUT NUMBER,
                          I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UDA table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

UDA

UDA upload to BDI

Uda_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UDA_OUT

No

Yes

No

No

UDA

Yes

No

No

No

UDA Values Publication API (BDI_UdaValues_Fnd_PF_From_RMS_JOB)

This section describes the UDA Values Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundationb.pls.pls

BDI_FOUNDATION_SQL.UDA_VALUES_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                                 O_control_id    IN OUT NUMBER,
                                 I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UDA Values table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

UDA Values

UDA Values upload to BDI

UdaValues_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UDA_VALUES_OUT

No

Yes

No

No

UDA_VALUES

Yes

No

No

No

UOM Class Publication API (BDI_UomClass_Fnd_PF_From_RMS_JOB)

This section describes the UOM Class Publication BDI.

Functional Area

Cross Pillar

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Uom Class information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.UOM_CLASS_UP (
                        O_error_message  IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
                        O_control_id     IN OUT  NUMBER,
                        I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UOM_CLASS table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Uom Class

Uom Class upload to BDI

UomClass_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UOM_CLASS_OUT

No

Yes

No

No

UOM_CLASS

Yes

No

No

No

UOM Conversion Publication API (BDI_UomConversion_Fnd_PF_From_RMS_JOB)

This section describes the UOM Conversion BDI.

Functional Area

Cross Pillar

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Uom Conversion information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdicrosspillarb.pls

BDI_CROSS_PILLAR_SQL.UOM_CONVERSION_UP (
                        O_error_message  IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
                        O_control_id     IN OUT  NUMBER,
                        I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UOM_CONVERSION table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Uom Conversion

Uom Conversion upload to BDI

UomConversion_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UOM_CONVERSION_OUT

No

Yes

No

No

UOM_CONVERSION

Yes

No

No

No

VAT Codes, Regions, and Rates Export (export_vat.ksh)

Module Name

export_vat.ksh

Description

Extraction of vat data

Functional Area

Foundation

Module Type

Integration

Module Technology

Ksh

Catalog ID

RMS264

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising VAT information into a flat file. Data to be extracted will be pulled off from the VAT export staging, VAT region, VAT codes, and VAT code rates tables.

The mode (full vs. delta) will be an input parameter for this batch. The mode will allow a full extract (all vat region/vat code/vat code rate combination records in RMS) as well as delta processing (all VAT record changes in the time frame passed in the program) of data.

In either of the mode exempt vat region won't get fetched in case of SVAT tax type.

For a full extract, records will be retrieved from the VAT region, VAT code, and VAT code rates tables. For a delta extract, the action type, vat region, vat code and active date will be retrieved from the VAT export staging table and the attributes will be retrieved from the main table.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

vat_date_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000215

Design Assumptions

N/A

VAT Publication API (BDI_Vat_Fnd_PF_From_RMS_JOB)

This seciton describes the VAT Publication BDI.

Functional Area

Foundation

Design Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Vat information from RMS to other Oracle Retail Applications. On this particular integration stream, the data flow is from RMS to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling an RMS owned API that will pull data from RMS and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Bulk Interface Module

Filename: bdifoundationb.pls

BDI_FOUNDATION_SQL.FUNCTION VAT_UP
                            (O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                             O_control_id    IN OUT   NUMBER,
                             I_job_context   IN       VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the VAT_CODES, VAT_CODE_RATES and VAT_REGION tables from RMS.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Vat

Vat upload to BDI

Vat_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

VAT_OUT

No

Yes

No

No

VAT_CODES

Yes

No

No

No

VAT_CODE_RATES

Yes

No

No

No

VAT_REGION

Yes

No

No

No

Warehouse (BDI_Wh_Fnd_PF_From_RMS_JOB)

This section describes Warehouse Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Warehouse information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiorgb.pls

BDI_ORG_SQL.WH_UP(O_error_message  IN OUT  VARCHAR2,
                  O_control_id     IN OUT  NUMBER,
                  I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Warehouse tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Warehouse

Warehouse upload to BDI

Wh_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

WH_OUT

No

Yes

No

No

WH

Yes

No

No

No

Warehouse Address Publication API (BDI_WhAddr_Fnd_PF_From_RMS_JOB)

This section describes Warehouse Address Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Warehouse Address information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiorgb.pls

BDI_ORG_SQL.WH_ADDR_UP(O_error_message  IN OUT  VARCHAR2,
                       O_control_id     IN OUT  NUMBER,
                       I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Warehouse Address tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Warehouse Address

Warehouse Address upload to BDI

WhAddr_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

WH_ADDR_OUT

No

Yes

No

No

ADDR

Yes

No

No

No

Items

Merchandising publishes item data for many other solution areas, including stores, warehouses, omni-channel, and so on.

Item Image Publication API (BDI_ItemImage_Fnd_PF_From_RMS_JOB)

This section describes the Item Image Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item Image information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_IMAGE_UP (O_error_message  IN OUT  VARCHAR2,
                            O_control_id     IN OUT  NUMBER,
                            I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising ITEM_IMAGE table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Item Image

Item Image upload to BDI

ItemImage_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_IMAGE_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_IMAGE

Yes

No

No

No

Item Location Export (export_itemloc.ksh)

Module Name

export_itemloc.ksh

Description

Extraction of item location data.

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

RMS257

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job extracts new, updated and deleted Merchandising item-location information into a flat file.

  • This batch supports both a full and delta export of item-location data.

  • A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.

  • An optional location parameter may be passed in for either modes. If this value is passed in, the batch will create a flat file for the location passed in. If it is not passed in, the batch will create flat files for all locations.

  • This creates separate files per location (Store, Warehouse or External Finisher).

  • This exports delta item header information for each applicable store location.

  • This will export data only for approved, sellable items.

  • This will export attributes from item/location and item/location traits.

  • This should also include the item parent as its own record in the extract.

  • The flat files that will be created will now be pipe delimited.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

itemloc_[#date]_[#loc_type]_[#location]_[full/delta]_[#ofLines].dat

itemhdr_[#date]_[#store_id]_delta_[#ofLines].dat

Integration Contract

IntCon000209

IntCon000208

Design Assumptions

N/A

Item Location Publication API (BDI_ItemLoc_Fnd_PF_From_RMS_JOB)

This section describes the Item Location Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item Location information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_LOC_UP(O_error_message  IN OUT  VARCHAR2,
                         O_control_id     IN OUT  NUMBER,
                         I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Item Location table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XSD)

Item Location

Item Location upload to BDI

ItemLoc_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_LOC_OUT

No

Yes

No

No

ITEM_LOC

Yes

No

No

No

ITEM_LOC_TRAITS

Yes

No

No

No

STORE

Yes

No

No

No

WH

Yes

No

No

No

PARTNER

Yes

No

No

No

ITEM_MASTER

Yes

No

No

No

Item Master Export (export_itemmaster.ksh)

Module Name

export_itemmaster.ksh

Description

Extraction of item data

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

RMS258

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising item master information into a flat file.

Data to be extracted will be pulled off from the item export information, item export staging and the main item tables.

  • The mode (full vs. delta) will be an input parameter for this new batch. The mode will allow a full extract (all approved, sellable items in Merchandising) as well as delta processing (all approved, sellable item changes in the main item table since the last export) of data.

  • A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.

  • In full mode, normal operation will produce both a corporate level file and files for all stores. An optional input parameter will also allow the program to produce a location level file for a specified store.

  • In delta mode, the only option is to produce corporate level files. Item header files at the store level will be created in the export_itemloc.ksh for delta mode.

  • The store specific file will also include UPC items. To determine which UPC Items to include, the store where the UPC's parent and/or grandparent item is ranged should be taken into consideration.

  • The flat files that will be created will now be pipe delimited.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

itemhdr_[#date]_corp_[full/delta]_[#ofLines].dat

itemhdr_[#date]_[location]_full_[#ofLines].dat

Integration Contract

IntCon000208

Design Assumptions

N/A

Item Master Publication API (BDI_ItemHdr_Fnd_PF_From_RMS_JOB)

This section describes the Item Master Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item Master information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI calls a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API is in the form of a PLSQL function inside a PLSQL package.

Package Impact

This section describes the package impact.

Bulk Interface Module

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_MASTER_UP(O_error_message  IN OUT  VARCHAR2,
                            O_control_id     IN OUT  NUMBER,
                            I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Item Master table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML
Data Flow Description XML Schema Definition (XSD)

Item Master

Item Master upload to BDI

ItemHdr_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_HDR_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

CLASS

Yes

No

No

No

SUBCLASS

Yes

No

No

No

DIFF_GROUP_HEAD

Yes

No

No

No

DIFF_IDS

Yes

No

No

No

SYSTEM_OPTIONS

Yes

No

No

No

Item Supplier Country Dimensions Publication API (BDI_ItSupCtryDim_Fnd_PF_From_RMS_JOB)

This section describes the Item Supplier Country Dim Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item Supplier Country Dim information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_SUP_CTY_DIM_UP (O_error_message  IN OUT  VARCHAR2,
                                  O_control_id     IN OUT  NUMBER,
                                  I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising ITEM_SUPP_COUNTRY_DIM table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Item Supplier Country Dim

Item supplier country Dim upload to BDI

ItSupCtryDim_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_SUP_CTY_DIM_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_SUPP_COUNTRY_DIM

Yes

No

No

No

Item Supplier Country Publication API (BDI_ItSupCtry_Fnd_PF_From_RMS_JOB)

This section describes the Item Supplier Country Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item supplier country information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_SUPP_COUNTRY_UP (O_error_message  IN OUT  VARCHAR2,
                                   O_control_id     IN OUT  NUMBER,
                                   I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising ITEM_SUPP_COUNTRY table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Item Supplier Country

Item supplier country upload to BDI

ItSupCtry_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_SUPP_COUNTRY_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_SUPP_COUNTRY

Yes

No

No

No

Item Supplier Manufacturing Country Publication API (BDI_ItSupManCtry_Fnd_PF_From_RMS_JOB)

This section describes the Item Supplier Manufacturing Country Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item Supplier Manufacturing Country information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_SUP_MAN_CTY_UP (O_error_message  IN OUT  VARCHAR2,
                                  O_control_id     IN OUT  NUMBER,
                                  I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising ITEM_SUPP_MANU_COUNTRY table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Item Supplier Manufacturing Country

Item supplier Manufacturing Country upload to BDI

ItSupManCtry_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_SUP_MAN_CTY_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_SUPP_MANU_COUNTRY

Yes

No

No

No

Item Supplier Publication API (BDI_ItemSupp_Fnd_PF_From_RMS_JOB)

This section describes the Item Supplier Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item supplier information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_SUPPLIER_UP(O_error_message  IN OUT  VARCHAR2,
                              O_control_id     IN OUT  NUMBER,
                              I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising ITEM_SUPPLIER table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Item supplier

Item Supplier upload to BDI

ItemSupplier_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_SUPPLIER_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_SUPPLIER

Yes

No

No

No

Item Supplier UOM Publication API (BDI_ItemSuppUom_Fnd_PF_From_RMS_JOB)

This section describes the Item Supplier UOM Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Item supplier UOM information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.ITEM_SUPP_UOM_UP(O_error_message  IN OUT  VARCHAR2,
                              O_control_id     IN OUT  NUMBER,
                              I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising ITEM_SUPP_UOM table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Item supplier UOM

Item Supplier UOM upload to BDI

ItemSuppUom_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

ITEM_SUPP_UOM_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

ITEM_SUPP_UOM

Yes

No

No

No

Item VAT Rates Export (export_itemvat.ksh)

Module Name

export_itemvat.ksh

Description

Extraction of vat item data.

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

RMS259

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising item VAT information into a flat file.

  • This batch supports both a full and delta export of item VAT data.

  • A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.

  • In full mode, normal operation will produce both a corporate level file and files for all stores. An optional input parameter will also allow the program to produce a location level file for a specified store.

  • In full mode for store specific file if store belong to such a vat region, which is exempt (In case of tax type SVAT), then files for that store won't get generated.

  • In delta mode, this will produce both corporate level files and files for all stores the modified items are ranged to and the vat region the store is associated with.

  • In delta mode for store specific file if store belong to such a vat region, which is exempt, then files for that store won't get generated.

  • This will export data only for approved, sellable items.

  • This will export item VAT information from the item export staging and item tables.

  • This should also include the item parent as its own record in the extract.

  • The flat files that will be created will now be pipe delimited.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

vatitem_[#date]_corp_[full/delta]_[#ofLines].dat

vatitem_[#date]_[location]_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000214

Design Assumptions

N/A

Pack Item Publication API (BDI_PckitemBrkout_Fnd_PF_From_RMS_JOB)

This section describes the Pack Item Publication BDI.

Functional Area

Item

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Pack Item information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.PACK_ITEM_UP(O_error_message  IN OUT  VARCHAR2,
                          O_control_id     IN OUT  NUMBER,
                          I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising PACKITEM table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Pack Item

Pack Item upload to BDI

PackItem_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

PACK_ITEM_OUT

No

Yes

No

No

ITEM_MASTER

Yes

No

No

No

PACKITEM

Yes

No

No

No

POS Configuration Data to 3rd Party POS (poscdnld)

Module Name

poscdnld.pc

Description

Download of POS Configuration Data to 3rd Party POS

Functional Area

Integration - 3rd Party POS

Module Type

Integration

Module Technology

ProC

Catalog ID

RMS69

Wrapper Script

rmswrap_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This program downloads POS configuration information from Merchandising to a flat file. This file can be used to load POS and back-office systems.This program (and its related prepost function) should only be run if Merchandising is used to master:

  • Coupon definitions and relationships to items

  • Restrictions on product sales, including but not limited to minimum age of purchaser, time/days when product cannot be sold, tenders that cannot be used to purchase the product, and so on.

Restart/Recovery

The logic unit of work is pos configuration type and pos configuration ID. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The recommended commit counter setting is 1000 records (subject to change based on implementation).

I/O Specification

Integration Type

Download from Merchandising

File Name

Determined by runtime parameter.

Integration Contract

IntCon000063

Output File Layout

Table 6-3 Output File Layout

Record Name Field Name Field Type Default Value Description

FHEAD

Record Type

Char(5)

'FHEAD'

Record Identifier

Line id

Number(10)

0000000001

Sequential Line Identifier

File Name

Char(4)

'POSC'

File Identifier

File Date

Char(14)

N/A

Date the file was created in 'YYYYMMDD HHMMSS' format

TCOUP

Record Type

Char(5)

TCOUP

Record Identifier

Line id

Number(10)

N/A

Sequential Line Identifier

Coupon id

Number(6)

N/A

N/A

Coupon Desc

Char(250)

N/A

N/A

Currency Code

Char2(3)

N/A

N/A

Max Discount Amount

Number(20,4)

N/A

N/A

Amount

Number(20,4)

N/A

N/A

Percent Ind

Char(1)

'N' - Amount

'Y'- Percentage

N/A

Profit Center

Char(6)

N/A

N/A

Tax Class

Char(6)

N/A

N/A

Export Code

Char(6)

N/A

N/A

Effective Date

Char(14)

N/A

Indicates the first day the coupon can be used in 'YYYYMMDD HHMMSS' format

Expiration Date

Char(14)

N/A

Indicates the day the coupon becomes invalid in 'YYYYMMDD HHMMSS' format

Prompted Ind

Char(1)

'Y', 'N'

This indicator identifies if the cashier should be prompted to ask for a

Coupon.

Display Ind

Char(1)

'Y', 'N'

This indicator specifies whether the coupon is displayed in the list of valid coupons on the register.

Status

Char(1)

'A','C','D'

Indicates if the coupon configuration is new, has been changed, or being deleted.

Vendor

Number(10)

N/A

N/A

Vendor Type

Char(6)

'AG' - Agent

'AP' - Applicant

'BK' - Bank

'BR' - Broker

'CN' - Coonsignee

'CO' - Consolidator

'FA' - Factory

'FF' - Freight Forwarder

'IM' - Importer

'SU' - Supplier

N/A

Promotion

Number(10)

N/A

N/A

Coupon Barcode

Char(20)

N/A

N/A

Coupon Max Qty

Number(6)

N/A

N/A

TPRES

Record Type

Char(5)

TPRES

Record Identifier

Line id

Number(10)

N/A

Sequential Line Identifier

POS Product Restriction id

Number(6)

N/A

N/A

POS Product Restriction Desc

Char(120)

N/A

N/A

POS Product Restriction Type

Char(6)

'PPRT' include:

'STMP' - Food Stamp

'MNAG' - Minimum Age

'CNDP' -Container Deposit

'CNVL' - Container Redemption Value

'DTDR' - Day/Time/Date Restriction

'TENT' - Tender Type

'NDSC' - Non-Discountable

'RTRN' - Returnable

'QLMT' - Quantity Limit

N/A

Effective Date

Char(14)

N/A

Date the product restriction is first effective in 'YYYYMMDD HHMMSS' format

Currency Code

Char(3)

N/A

N/A

Product Restriction Amount

Number(20,4)

N/A

N/A

Age Minimum

Number(2)

N/A

N/A

Date Restriction

Char(14)

N/A

Date on which a specified product restriction is applied in 'YYYYMMDD HHMMSS' format

Before Time Restriction

Char(6)

N/A

N/A

After Time Restriction

Char(6)

N/A

N/A

Day Restriction

Char(6)

N/A

N/A

Max Qty Amount

Number(12,4)

N/A

N/A

Tender Type Group

Char(6)

'CASH' - Cash,

'CHECK' - Check,

'CCARD' - Credit,

'COUPON' - Coupon,

'LOTTRY' - Lottery,

'FSTAMP' - Food Stamp,

'DCARD' - Debit Card,

'MORDER' - Money Order

'VOUCH' - Voucher

'ERR' - Error,

'SOCASS' - Social Assistance,

'TERM' - Termination Record,

'DRIVEO' - Drive Off,

'EBS' - Electronic Benefits ( Food Stamps)

N/A

Status

Char(1)

'A','C','D'

Indicates if the product restriction configuration is new, has been changed, or being deleted.

TSTOR

Record Type

Char(5)

'TSTOR'

N/A

Line id

Number(10)

N/A

N/A

Store

Number(10)

N/A

N/A

Status

Char(1)

'A' - Add

'D' - Delete

'C' - Change

N/A

TITEM

Record Type

Char(5)

TITEM

Record Identifier

Line id

Number(10)

N/A

Sequential Line Identifier

Item

Char(25)

N/A

Left-Justified Item Identifier

Status

Char(1)

'A' - Add

'D' - Delete

'C' - Change

Indicates the item's status at the POS. Overlays of items as a result of a change to the merch criteria will have a ‘C' status.

FTAIL

Record Type

Char(5)

FTAIL

Marks end of file

Line id

Number(10)

N/A

Total number of lines in file

Number of transactions

Number(10)

N/A

Number of transactions in file

Design Assumptions

N/A

Price History Publication API (BDI_PriceHist_Fnd_PF_From_RMS_JOB)

This section describes the Price History Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Price History positions from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdifoundations/b.pls

BDI_FOUNDATION_SQL.PRICE_HIST_UP(O_error_message  IN OUT  VARCHAR2,
                                 O_control_id     IN OUT  NUMBER,
                                 I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising Price History tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Price History

Price History upload to BDI

PriceHist_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

PRICE_HIST_OUT

No

Yes

No

No

PRICE_HIST

Yes

No

No

No

Related Items Export (export_relitem.ksh)

Module Name

export_relitem.ksh

Description

Extraction of related item data

Functional Area

Foundation

Module Type

Integration

Module Technology

ksh

Catalog ID

RMS262

Wrapper Script

rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch job will extract new, updated and deleted Merchandising related items information into a flat file.

  • This batch will support both a full and delta export of related item data.

  • A threading indicator parameter should be passed. Passing 'Y' means a thread number (1-20) will be passed in. Passing 'N' means no thread number will be passed in and the program will use a default thread number.

  • In full mode, normal operation will produce both a corporate level files and files for all stores. An optional input parameter will also allow the program to produce location level files for a specified store.

  • In delta mode, this will produce both corporate level files and files for all stores the modified data are ranged to.

  • This will export data only for approved, sellable items.

  • This will export item related item information from the related item export staging and related item tables.

  • Two types of flat files will be created for this extract - one for the related item header information and one for the related item detail information.

  • When creating the location level files, ensure that both items (the main item and related item) are ranged in the location.

  • The flat files that will be created will now be pipe delimited.

Restart/Recovery

N/A

I/O Specification

Integration Type

Extract from Merchandising

File Name

relitemhead_date_corp_[full/delta]_[#ofLines].dat

relitemhead_date_[Location]_[full/delta]_[#ofLines].dat

relitemdet_date_corp_[full/delta]_[#ofLines].dat

relitemdet_date_[Location]_[full/delta]_[#ofLines].dat

Integration Contract

IntCon000210

IntCon000211

Design Assumptions

N/A

Related Item Publication API (BDI_RelatedItem_Fnd_PF_From_RMS_JOB)

This section describes the Related Item Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Related Items from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.REL_ITEM_UP(O_error_message  IN OUT  VARCHAR2,
                         O_control_id     IN OUT  NUMBER,
                         I_job_context    IN      VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound tables that reside in the BDI_RMS_INT_SCHEMA schema. These outbound tables are loaded with records from the Merchandising Related Item head and detail tables.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

Related Item

Related Item upload to BDI

RelatedItem_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

RELATED_ITEM_OUT

No

Yes

No

No

RELATED_ITEM_DTL_OUT

No

Yes

No

No

RELATED_ITEM_HEAD

Yes

No

No

No

RELATED_ITEM_DETAIL

Yes

No

No

No

ITEM_MASTER

Yes

No

No

No

UDA Item Date Publication API (BDI_UdaItemDate_Fnd_PF_From_RMS_JOB)

This section describes the UDA Item Date Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.UDA_ITEM_DATE_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                              O_control_id    IN OUT NUMBER,
                              I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UDA Item Date table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

UDA ITEM DATE

UDA Item Date upload to BDI

UdaItemDate_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UDA_ITEM_DATE_OUT

No

Yes

No

No

UDA_ITEM_DATE

Yes

No

No

No

UDA Item FF Publication API (BDI_UdaItemFF_Fnd_PF_From_RMS_JOB)

This section describes the UDA Item FF Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.UDA_ITEM_FF_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                            O_control_id    IN OUT NUMBER,
                            I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UDA Item FF table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.

A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

UDA ITEM FF

UDA Item FF upload to BDI

UdaItemFF_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UDA_ITEM_FF_OUT

No

Yes

No

No

UDA_ITEM_FF

Yes

No

No

No

UDA Item LOV Publication API (BDI_UdaItemLov_Fnd_From_RMS_JOB)

This section describes the UDA Item LOV Publication BDI.

Functional Area

Foundation

Business Overview

BDI (Bulk Data Integration) is an integration layer that facilitates the bulk transfer of Code Head information from Merchandising to other Oracle Retail Applications. On this particular integration stream, the data flow is from Merchandising to BDI, and then BDI to downstream applications. To accomplish this data transfer, BDI will be calling a Merchandising-owned API that will pull data from Merchandising and deliver these to the BDI integration layer. This API will be in the form of a PLSQL function inside a PLSQL package.

Package Impact

Filename: bdiitemb.pls

BDI_ITEM_SQL.UDA_ITEM_LOV_UP(O_error_message IN OUT RTK_ERRORS.RTK_TEXT%TYPE,
                             O_control_id    IN OUT NUMBER,
                             I_job_context   IN     VARCHAR2)

This function begins by calling a BDI function that signals the start of the interface process. The BDI function will update the internal BDI control tables to track the progress of the API.

A DML insert statement is then executed to populate the BDI outbound table that resides in the BDI_RMS_INT_SCHEMA schema. This outbound table is loaded with records from the Merchandising UDA Item LOV table.

After the insert, another call to a BDI function is performed to signify the successful loading of records. This will update the internal BDI control tables.A database commit is issued, and the control Id is returned by the API.

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition

Data Flow Description XML Schema Definition (XSD)

UDA ITEM LOV

UDA Item LOV upload to BDI

UdaItemLov_Fnd_BdiInterfaceModule.xml

Table Impact
TABLE SELECT INSERT UPDATE DELETE

UDA_ITEM_LOV_OUT

No

Yes

No

No

UDA_ITEM_LOV

Yes

No

No

No

Financials

Merchandising stages General Ledger (GL) data for subsequent upload into a financial system. A set of batch processes gather and organize the data before using it to populate the staging table, STG_FIF_GL_DATA.

For more information about how data moves from these staging tables to the General Ledger of a financial application and other integration between Merchandising and financial applications, see Oracle Retail Financial Integration for Oracle Retail Merchandise Operations Management and Oracle E-Business Suite Financials Implementation Guide.

The following scheduled outbound integrations are included in this functional area:

Daily or Weekly Donwload of Stock Ledger Data (stlgdnld)

Module Name

stlgdnld.pc

Description

Weekly or Historical Download of Stock Ledger Data

Functional Area

Stock Ledger

Module Type

Integration

Module Technology

ProC

Catalog ID

RMS17

Wrapper Script

batch_stlgdnld.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This program extracts stock ledger data at the item level. The program can extract data for a historic period or for the most current complete week. The program accepts an input file that determines whether the extract is a historic extract or a weekly extract.

This program is often used in integration with RPAS applications.

Restart/Recovery

The logical unit of work for this program is set at item, location type, location and date. Threading is done by dept using the v_restart_dept view to thread properly.

The changes will be posted when the commit_max_ctr value is reached. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O. The value of the counter is subject to change based on implementation.

I/O Specification

Integration Type

Download from Merchandising

File Name

The input filename is a runtime parameter.

The output filename is hardcoded to stkldgr%d.dat where %d is substituted with the domain id. Each run of the program can produce multiple output files, one for each department. Additional input parameters are defined in the input file

Integratin Contract

IntCon000034 (output file)

Input File Layout

Table 6-4 Input File Layout

Field Name Field Type Default Value Description

Task Indicator

Char(1)

N/A

Task Indicator. Valid values are 'H' - historical, 'W' - weekly

From Date

Char(8)

N/A

From Date in 'YYYYMMDD' format

To Date

Char(8)

N/A

To Date in 'YYYYMMDD' format

Output File Layout

Table 6-5 Output File Layout

Field Name Field Type Default Value Description

Item

Char(25)

N/A

Item number

Location Type

Char(1)

N/A

Location Type

Valid values are 'S','W'

Location

Number(20)

N/A

Location Number

Eow_date

Char(8)

N/A

End of Week date in 'YYYYMMDD' format

Update_Ind

Char(1)

N/A

Update Indicator

Valid values are 'I ' and 'U'

Regular_sales_retail

Number(25,4)

N/A

Regular sales value (retail)

Regular_sales_cost

Number(25,4)

N/A

Regular sales value (cost)

Regular_sales_units

Number(17,4)

N/A

Regular sales value (units)

Promo_sales_retail

Number(25,4)

N/A

Promo sales value (retail)

Promo_sales_cost

Number(25,4)

N/A

Promo sales value (cost)

Promo_sales_units

Number(17,4)

N/A

Promo sales value (units)

Clear_sales_retail

Number(25,4)

N/A

Clearance sales value (retail)

Clear_sales_cost

Number(25,4)

N/A

Clearance sales value (cost)

Clear_sales_units

Number(17,4)

N/A

Clearance sales value (units)

Sales_retail_excluding_vat

Number(25,4)

N/A

Sales value excluding vat (retail)

Custom_returns_retail

Number(25,4)

N/A

Custom returns value (retail)

Custom_returns_cost

Number(25,4)

N/A

Custom returns value (cost)

Custom_returns_units

Number(17,4)

N/A

Custom returns value (units)

Rtv_retail

Number(25,4)

N/A

Return to Vendor value (retail)

Rtv_cost

Number(25,4)

N/A

Return to Vendor value (cost)

Rtv_units

Number(17,4)

N/A

Return to Vendor value (units)

Reclass_in_retail

Number(25,4)

N/A

Reclass In value (retail)

Reclass_in_cost

Number(25,4)

N/A

Reclass In value (cost)

Reclass_in_units

Number(17,4)

N/A

Reclass In value (units)

Reclass_out_retail

Number(25,4)

N/A

Reclass Out value (retail)

Reclass_out_cost

Number(25,4)

N/A

Reclass Out value (cost)

Reclass_out_units

Number(17,4)

N/A

Reclass Out value (units)

Perm_markdown_value

Number(25,4)

N/A

Permanent markdown value (retail)

Prom_markdown_value

Number(25,4)

N/A

Promotion markdown value (retail)

Clear_markdown_value

Number(25,4)

N/A

Clearance markdown value (retail)

Markdown_cancel_value

Number(25,4)

N/A

Markdown cancel value

Markup_value

Number(25,4)

N/A

Markup value

Markup_cancel_value

Number(25,4)

N/A

Markup cancel value

Stock_adj_retail

Number(25,4)

N/A

Stock adjustment value (retail)

Stock_adj_cost

Number(25,4)

N/A

Stock adjustment value (cost)

Stock_adj_units

Number(17,4)

N/A

Stock adjustment value (units)

Received_retail

Number(25,4)

N/A

Received value (retail)

Received_cost

Number(25,4)

N/A

Received value (cost)

Received_units

Number(17,4)

N/A

Received value (units)

Tsf_in_retail

Number(25,4)

N/A

Transfer In value (retail)

Tsf_in_cost

Number(25,4)

N/A

Transfer In value (cost)

Tsf_in_units

Number(17,4)

N/A

Transfer In value (units)

Tsf_out_retail

Number(25,4)

N/A

Transfer Out value (retail)

Tsf_out_cost

Number(25,4)

N/A

Transfer Out value (cost)

Tsf_out_units

Number(17,4)

N/A

Transfer Out value (units)

Freight_cost

Number(25,4)

N/A

Freight cost

Employee_disc_retail

Number(25,4)

N/A

Employee disc (retail)

Cost_variance

Number(25,4)

N/A

Cost variance

Wkroom_other_cost_sales

Number(25,4)

N/A

Wkroom other sales (cost)

Cash_disc_retail

Number(25,4)

N/A

Cash disc (retail)

Freight_claim_retail

Number(25,4)

N/A

Freight Claim (retail)

Freight_claim_cost

Number(25,4)

N/A

Freight Claim (cost)

Freight_claim_units

Number(25,4)

N/A

Freight Claim (Units)

Stock_adj_cogs_retail

Number(25,4)

N/A

Stock Adjust COGS (retail)

Stock_adj_cogs_cost

Number(25,4)

N/A

Stock Adjust COGS (cost)

Stock_adj_cogs_units

Number(25,4)

N/A

Stock Adjust COGS (Units)

Intercompany_in_retail

Number(25,4)

N/A

Intercompany In value (retail)

Intercompany_in_cost

Number(25,4)

N/A

Intercompany In value (cost)

Intercompany_in_units

Number(25,4)

N/A

Intercompany In value (units)

Intercompany_out_retail

Number(25,4)

N/A

Intercompany Out value (retail)

Intercompany_out_cost

Number(25,4)

N/A

Intercompany Out value (cost)

Intercompany_out_units

Number(25,4)

N/A

Intercompany Out value (units)

Intercompany_markup

Number(25,4)

N/A

Intercompany Markup

Intercompany_markup_units

Number(25,4)

N/A

Intercompany Markup (units)

Intercompany_markdown

Number(25,4)

N/A

Intercompany Markdown

Intercompany_markdown_units

Number(25,4)

N/A

Intercompany Markdown (units)

Wo_activity_upd_inv

Number(25,4)

N/A

Work Order Activity - Update Inventory (cost)

Wo_activity_upd_inv_units

Number(25,4)

N/A

Work Order Activity - Update Inventory (units)

Wo_activity_post_fin

Number(25,4)

N/A

Work Order Activity - Post to Financials (retail)

Wo_activity_post_fin_units

Number(25,4)

N/A

Work Order Activity - Post to Financials (units)

Design Assumptions

N/A

Finance General Ledger to RFI (BDI_RFI_FinGenLdgr_Tx_PF_From_RMS_JOB)

Module Name

BDI_RFI_FinGenLdgr_Tx_PF_From_RMS_JOB

Description

Extracts financial general ledger to RFI

Functional Area

Finance

Module Type

Integration

Module Technology

BDI Job

Catalog ID

N/A

Runtime Parameters

FinGenLdgr_Tx_ProcessFlow_From_RMS FinGenLdgr_Tx_Extractor

Design Overview

This API extracts staged data from Merchandising and Sales Audit and transfers it to the General Ledger inbound staging tables. To accomplish this data transfer, BDI will call a Merchandising owned API that will pull data from RMS and deliver these to the BDI integration layer.

This integration is applicable when using Oracle Retail Financial Integration (RFI) to supported financial solutions. For more information on RFI, see the ORFI Implementation Guide, as part of the documentation for Oracle Retail Integration Cloud Service.

Scheduling Constraints
Schedule Information Description

Processing Cycle

End of Day

Frequency

Daily

Scheduling Consideration

N/A

Pre-Processing

N/A

Post-Processing

N/A

Threading Scheme

N/A

Data Definition XML

The BDI interface staging tables are generated based on the XML schema definition.

Data Flow Description XML Schema Definition (XML)

Finance

General Ledger upload to BDI

FinGenLdgr_Tx_BdiInterfaceModule.xml

Fixed Deal Income (dealfinc)

Module Name

dealfinc.pc

Description

Calculation & Interface of Fixed Deal Income for General Ledger

Functional Area

Integration - General Ledger

Module Type

Integration

Module Technology

ProC

Catalog ID

RMS65

Wrapper Script

rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This module writes to the STG_FIF_GL_DATA financial staging table to perform stock ledger processing for fixed deals. It splits deal income over all dept/class/subclass locations on the deal. This prorated income is written to the general ledger under a suitable cost center mapping.

Restart/Recovery

The logical unit of work for this program is a DEAL_ID. The database commit takes place when number of deal records processed is equal to the commit max counter in the restart control table.

I/O Specification

Integration Type

Download from Merchandising

File Name

N/A

Integration Contract

IntCon000019

STG_FIF_GL_DATA table

Design Assumptions

N/A

Franchise Billing Extract (wfbillex.ksh)

Module Name

wfbillex.ksh

Description

Franchise Billing Extract

Functional Area

Franchise Management

Module Type

Integration

Module Technology

ksh

Catalog ID

RMS155

Wrapper Script

rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this shell script module is to fetch all billing information for Franchise sale and return transactions and write these to an output file for integration with an external financial application that manages billing. A file is generated for each customer location (store)/day.

The format of the generated file is based on a run time parameter:

  • If no parameter is passed or if the value 1 is used, the previously existing format (refer Output File Layout Format 1) will be generated.

  • If the value 2 (or greater than 2) is passed, the new file format (refer Output File Layout Format 2) will be generated.

Restart/Recovery

The logical unit of work for this module is defined as the customer location (store). Only one commit will be done for a customer location that has been completely processed. The WFBX formatted output file will be created with a temporary name and renamed just before a customer location commit. In case of failure, all work done will be rolled back.

I/O Specification

Integration Type

Download from Merchandising

File Name

WFBX_<store>_<SYSDATE>

Integration Contract

IntCon000110

Output File Layout

Table 6-6 Output File Layout Format 1

Record Name Field Name Field Type Default Value Description

FHEAD

Record descriptor

Char(5)

FHEAD

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

File type definition

Char(4)

WFBX

Identifies the file type

File Create Date

Char(14)

File Create Date in YYYYMMDDHHMMSS format

THEAD

Record descriptor

Char(5)

THEAD

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

Customer Location

Number(10)

Franchise store number

Customer Order Reference Number

Char(20)

Reference number provided by the franchise customer

Franchise Order Number

Number(10)

Franchise Order Number

Transaction Type

Char(6)

SALES or RETURN

RMA Number

Number(10)

Return Merchandise Authorization Number for the return

Order Return Date

Number(8)

Order return date for Return transaction type or Order date for Sale transaction type in YYYYMMDD format

Shipment Date

Number(8)

Date on which the item was shipped to the franchise location or returned to the retailer

TDETL

Record descriptor

Char(5)

TDETL

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

Item

Char(25)

Item sequence number

Department

Number(4)

Department number of the item

Class

Number(4)

Class number of the item

Subclass

Char(4)

Subclass number of the item

Order Return Quantity

Number(12)

Return quantity with 4 implied decimal places

Order Return Quantity UOM

Char(4)

Return quantity unit of measure

Order Return Cost

Number(20)

Return cost for Return transaction type or Customer cost for Sale transaction type. For both it is the per-unit cost

Freight Cost

Number(20)

Freight associated to the franchise order

Return Restocking Fee

Number(20)

Unit restocking fee charged for received items

VAT Code

Char(6)

VAT code for the item

VAT Rate

Number(20)

VAT rate associated to the VAT code for the item

Other Order Charges

Number(20)

Other charges for the item

TTAIL

Record descriptor

Char(5)

TTAIL

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

Tran Record Counter

Number(6)

Number of TDETL records in this transaction set

FTAIL

Record descriptor

Char(5)

FTAIL

Identifies the file record type

File Line Id

Number(10)

Sequential file line number

File Record counter

Number(10)

Number of records/transactions processed in current file (only records between head & tail)

Table 6-7 Output File Layout Format 2

Record Name Field Name Field Type Default Value Description

FHEAD

Record descriptor

Char(5)

FHEAD

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

File type definition

Char(4)

WFBX

Identifies the file type

File Create Date

Char(14)

File Create Date in YYYYMMDDHHMMSS format

Version No.

Char(2)

02

Identifies the file format version

THEAD

Record descriptor

Char(5)

THEAD

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

Customer Location

Number(10)

Franchise store number

Customer Order Reference Number

Char(20)

Reference number provided by the franchise customer

Franchise Order Number

Number(10)

Franchise Order Number

Transaction Type

Char(6)

SALES or RETURN

RMA Number

Number(10)

Return Merchandise Authorization Number for the return

Order Return Date

Number(8)

Order return date for Return transaction type or Order date for Sale transaction type in YYYYMMDD format

Shipment Date

Number(8)

Date on which the item was shipped to the franchise location or returned to the retailer

TDETL

Record descriptor

Char(5)

TDETL

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

Item

Char(25)

Item sequence number

Department

Number(4)

Department number of the item

Class

Number(4)

Class number of the item

Subclass

Char(4)

Subclass number of the item

Order Return Quantity

Number(12)

Return quantity with 4 implied decimal places

Order Return Quantity UOM

Char(4)

Return quantity unit of measure

Order Return Cost

Number(20)

Return cost for Return transaction type or Customer cost for Sale transaction type. For both it is the per-unit cost

Freight Cost

Number(20)

Freight associated to the franchise order

Return Restocking Fee

Number(20)

Unit restocking fee charged for received items

VAT Code

Char(6)

VAT code for the item

VAT Rate

Number(20)

VAT rate associated to the VAT code for the item

Other Order Charges

Number(20)

Other charges for the item

Uom Type

Char(4)

Uom Type fetched from GTS tax engine to be considered when tax is in value

Uom Value

Number(20)

UOM value fetched from GTS tax engine to be considered when tax is in value. Having 10 places of decimal digits.

Uom Tax Value Per Unit

Number(20)

UOM tax value per unit fetched from GTS tax engine to be considered when tax is in value. Having 10 places of decimal digits.

TTAIL

Record descriptor

Char(5)

TTAIL

Identifies the file record type

File Line Id

Char(10)

Sequential file line number

Tran Record Counter

Number(6)

Number of TDETL records in this transaction set

FTAIL

Record descriptor

Char(5)

FTAIL

Identifies the file record type

File Line Id

Number(10)