27 Integration with General Ledger

RMS stages 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 RMS and financial applications, see Oracle Retail Financial Integration for Oracle Retail Merchandise Operations Management and Oracle E-Business Suite Financials Implementation Guide

Batch Design Summary

The following batch designs are included in this functional area:

• dealfinc.pc - Calculation & Interface of Fixed Deal Income for General Ledger

• fifgldn1.pc - Interface to General Ledger of Item/Loc Level Transactions

• fifgldn2. pc - Interface to General Ledger of Rolled Up Transactions

• fifgldn3. pc - Interface to General Ledger of Month Level Information

• gl_extract.ksh (Extraction of General Ledger transaction data from RMS and RESA)

dealfinc (Calculation of Fixed Deal Income for General Ledger)

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
Runtime Parameters	NA

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.

Scheduling Constraints

Table 27-1 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Should be run after DEALACT.PC, before DEALFCT.PC, DEALDAY.PC and SALMTH.PC
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multithreaded on Deal ID

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.

Key Tables Affected

Table 27-2 Key Tables Affected

Table	Select	Insert	Update	Delete
FIXED_DEAL	Yes	No	No	No
FIXED_DEAL_DATES	Yes	No	No	No
FIXED_DEAL_MERCH	Yes	No	No	No
FIXED_DEAL_MERCH_LOC	Yes	No	No	No
SUBCLASS	Yes	No	No	No
FIF_GL_CROSS_REF	Yes	No	No	No
STG_FIF_GL_DATA	No	Yes	No	No
MV_LOC_SOB	Yes	No	No	No
KEY_MAP_GL	No	Yes	No	No
FIXED_DEAL_GL_REF_DATA	No	Yes	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
ITEM_MASTER	Yes	No	No	No
ITEM_SUPP_COUNTRY	Yes	No	No	No
SUPS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	NA
Integration Contract	IntCon000019 STG_FIF_GL_DATA table

Design Assumptions

NA

fifgldn1 (Interface to General Ledger of Item/Loc Level Transactions)

Module Name	fifgldn1.pc
Description	Interface to General Ledger of Item/Loc Level Transactions
Functional Area	General Ledger
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS66
Runtime Parameters	NA

Design Overview

This program extracts the detailed stock ledger information for certain transaction types on a daily basis in order to bridge the information to an interfaced financial application. The program reads from the IF_TRAN_DATA table for each transaction type/amount type and posts it to the Oracle Retail General Ledger staging table (STG_FIF_GL_DATA) at the SKU detail level.

Scheduling Constraints

Table 27-3 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Should run after SALSTAGE and prior to SALAPND
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Threaded by department

Restart/Recovery

The logical unit of work is department/class/subclass. The batch is multithreaded using the v_restart_dept view.

Key Tables Affected

Table 27-4 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
WH	Yes	No	No	No
PARTNER	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
FIF_GL_CROSS_REF	Yes	No	No	No
STG_FIF_GL_DATA	No	Yes	No	No
MV_LOC_SOB	Yes	No	No	No
KEY_MAP_GL	No	Yes	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	NA
Integration Contract	IntCon000019 STG_FIF_GL_DATA table

Design Assumptions

NA

fifgldn2 (Interface to General Ledger of Rolled Up Transactions)

Module Name	fifgldn2.pc
Description	Interface to General Ledger of Rolled Up Transactions
Functional Area	Integration - General Ledger
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS67
Runtime Parameters	NA

Design Overview

This program summarizes stock ledger data from the transaction staging table (IF_TRAN_DATA) based on the level of information required and writes it to the financial general ledger staging table. The transactions extracted are determined by the CODE_TYPE 'GLRT' (General Ledger Rolled Transactions). The written information can then be extracted by the financial applications. Stock ledger information may be rolled-up at department, class or subclass level. The level at which information is rolled-up to is determined by the system parameter GL_ROLLUP.

Scheduling Constraints

Table 27-5 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	Should run after salstage.pc and prior to salapnd.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Threaded by department

Restart/Recovery

The logical unit of work is dependent on the level of rollup defined in system_options.gl_rollup. It can be department (department rollup), department/class (class rollup) or department/class/subclass (subclass rollup). The batch is multithreaded using the v_restart_dept view.

Key Tables Affected

Table 27-6 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
WH	Yes	No	No	No
PARTNER	Yes	No	No	No
IF_TRAN_DATA	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
FIF_GL_CROSS_REF	Yes	No	No	No
STG_FIF_GL_DATA	No	Yes	No	No
MV_LOC_SOB	Yes	No	No	No
KEY_MAP_GL	No	Yes	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	NA
Integration Contract	IntCon000019 STG_FIF_GL_DATA table

Design Assumptions

NA

fifgldn3 (Interface to General Ledger of Month Level Information)

Module Name	fifgldn3.pc
Description	General Ledger Interface 3
Functional Area	Interface to General Ledger of Month Level Information
Module Type	Integration
Module Technology	ProC
Catalog ID	RMS68
Runtime Parameters	NA

Design Overview

This program summarizes stock ledger data from the monthly stock ledger table (MONTH_DATA) based on the level of information required and writes it to the financial general ledger staging table. The transactions extracted are determined by the CODE_TYPE 'GLRT' (general ledger rolled transactions). Written information is then sent to the financial application. Stock ledger information may be rolled-up at department, class or subclass level. The level at which information is rolled-up to is determined by the system parameter GL_ROLLUP.

Scheduling Constraints

Table 27-7 Scheduling Constraints

Schedule Information	Description
Frequency	Monthly
Scheduling Considerations	Should run after salmth.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Threaded by location

Restart/Recovery

The logical unit of work is dependent on the level of rollup defined in system_options.gl_rollup. It can be department (department rollup), department/class (class rollup) or department/class/subclass (subclass rollup). The batch is multithreaded using the v_restart_all_locations view.

Key Tables Affected

Table 27-8 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE	Yes	No	No	No
WH	Yes	No	No	No
PARTNER	Yes	No	No	No
MONTH_DATA	Yes	No	No	No
CODE_DETAIL	Yes	No	No	No
FIF_GL_CROSS_REF	Yes	No	No	No
FIF_GL_SETUP	Yes	No	No	No
TRAN_DATA_HISTORY	Yes	No	No	No
STG_FIF_GL_DATA	No	Yes	No	No
KEY_MAP_GL	No	Yes	No	No
SYSTEM_VARIABLES	Yes	No	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
V_RESTART_ALL_LOCATIONS	Yes	No	No	No

Integration Contract

Integration Type	Download from RMS
File Name	NA
Integration Contract	IntCon000019 STG_FIF_GL_DATA table

Design Assumptions

NA

gl_extract.ksh (Extraction of General Ledger transaction data from RMS and RESA)

Module Name	gl_extract.ksh
Description	Extraction of General Ledger transaction data from RMS and RESA to be interfaced to third party GL/Financial system
Functional Area	Integration to General Ledger
Module Type	Integration
Module Technology	ksh
Catalog ID	RMS495
Runtime Parameters	Database connection

Design Overview

This batch job will extract general ledger transaction data from RESA and RMS into a file. Data to be extracted will be pulled off from the STG_FIF_GL_DATA table. Once the data is extracted into the file batch will purge the data from the table.

Scheduling Constraints

Table 27-9 Scheduling Constraints

Schedule Information	Description
Frequency	Daily
Scheduling Considerations	After fifgldn1.pc,fifgldn2.pc,fifgldn3.pc, dealfinc.pc
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 27-10 Key Tables Affected

Table	Select	Insert	Update	Delete
STG_FIF_GL_DATA	Yes	No	No	Yes
SYSTEM_OPTIONS	Yes	No	No	No

Integration Contract

Integration Type	Extract from RMS
File Name	GL_EXTRACT_[#date].dat
Integration Contract	Na

File Layout

The output file is comma delimited with the following fields:

• SET_OF_BOOKS_ID

• ACCOUNTING_DATE

• CURRENCY_CODE

• STATUS

• DATE_CREATED

• CREATED_BY

• ACTUAL_FLAG

• USER_JE_CATEGORY_NAME

• USER_JE_SOURCE_NAME

• CURRENCY_CONVERSION_DATE

• CURRENCY_CONVERSION_TYPE

• ACCT_SEGMENT1

• ACCT_SEGMENT2

• ACCT_SEGMENT3

• ACCT_SEGMENT4

• ACCT_SEGMENT5

• ACCT_SEGMENT6

• ACCT_SEGMENT7

• ACCT_SEGMENT8

• ACCT_SEGMENT9

• ACCT_SEGMENT10

• ENTERED_DR_AMOUNT

• ENTERED_CR_AMOUNT

• TRANSACTION_DATE

• REFERENCE1

• REFERENCE2

• REFERENCE3

• REFERENCE4

• REFERENCE5

• ATTRIBUTE1

• ATTRIBUTE2

• ATTRIBUTE3

• ATTRIBUTE4

• ATTRIBUTE5

• ATTRIBUTE6

• PERIOD_NAME

• CODE_COMBINATION_ID

• PGM_NAME

• ACCT_SEGMENT11

• ACCT_SEGMENT12

• ACCT_SEGMENT13

• ACCT_SEGMENT14

• ACCT_SEGMENT15

• ACCT_SEGMENT16

• ACCT_SEGMENT17

• ACCT_SEGMENT18

• ACCT_SEGMENT19

• ACCT_SEGMENT20

• REFERENCE_TRACE_ID

• PRIM_CURRENCY_CODE

• PRIM_ENTERED_DR_AMOUNT

• PRIM_ENTERED_CR_AMOUNT

• FIN_GL_SEQ_ID

• PROCESSED_FLAG

Design Assumptions

NA