Scheduled Integration

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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.

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

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
FTAIL	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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

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.

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

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.

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)