-- Interface Module: FinGenLdgr_Tx
-- Interface: Fif_Gl_Data_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE fif_gl_data_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: FIF_GL_DATA_OUT
-- This table will hold all the Stock Ledger information for RMS and Total ID information for ReSA to be sent across to Financial General Ledger.

CREATE TABLE FIF_GL_DATA_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT fif_gl_data_out_seq.nextval NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) DEFAULT sys_context('userenv', 'current_schema') NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20) DEFAULT 'FULL',
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20) DEFAULT 'REPLACE',
  -- This denotes unique identifier for the record.
  id NUMBER(15,0),
  -- This denotes the accounting books for an organization.
  set_of_books_id NUMBER(15,0),
  -- This denotes the effective date of the transaction.
  accounting_date TIMESTAMP,
  -- This denotes the Currency used for the Financial Information.
  currency_code VARCHAR2(15),
  -- This denotes the status of the  when there is a Journal Import. Default to New.
  status VARCHAR2(50),
  -- This denotes the date when the transaction was done.
  date_created TIMESTAMP,
  -- This denotes who created the transaction.
  created_by NUMBER(15,0),
  -- This denotes the Balance type and is default to A.
  actual_flag VARCHAR2(1),
  -- This denotes the Journal Entry Category User defined name.
  user_je_category_name VARCHAR2(25),
  -- This denotes the Journal Entry Source User defined name.
  user_je_source_name VARCHAR2(25),
  -- This denotes the date of currency exchange rate.
  currency_conversion_date TIMESTAMP,
  -- This denotes the type of currency exchange rate.
  currency_conversion_type VARCHAR2(30),
  -- This denotes the account segment for the accounting structure.
  acct_segment1 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment2 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment3 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment4 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment5 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment6 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment7 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment8 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment9 VARCHAR2(25),
  -- This denotes the account segment for the accounting structure.
  acct_segment10 VARCHAR2(25),
  -- This denotes the transaction debit amount.
  entered_dr_amount NUMBER(20,4),
  -- This denotes the transaction credit amount.
  entered_cr_amount NUMBER(20,4),
  -- This denotes the transaction date.
  transaction_date TIMESTAMP,
  -- This denotes the reference description to be used for the column.
  reference1 VARCHAR2(48),
  -- This denotes the reference description to be used for the column.
  reference2 VARCHAR2(48),
  -- This denotes the reference description to be used for the column.
  reference3 VARCHAR2(20),
  -- This denotes the reference description to be used for the column.
  reference4 VARCHAR2(20),
  -- This denotes the reference description to be used for the column.
  reference5 VARCHAR2(20),
  -- This denotes the attribute description to be used for the column.
  attribute1 VARCHAR2(20),
  -- This denotes the attribute description to be used for the column.
  attribute2 VARCHAR2(20),
  -- This denotes the attribute description to be used for the column.
  attribute3 VARCHAR2(20),
  -- This denotes the attribute description to be used for the column.
  attribute4 VARCHAR2(20),
  -- This denotes the attribute description to be used for the column.
  attribute5 VARCHAR2(20),
  -- This denotes the attribute description to be used for the column.
  attribute6 VARCHAR2(20),
  -- This denotes the accounting period.
  period_name VARCHAR2(15),
  -- This denotes the unique identification of an accounting structure.
  code_combination_id NUMBER(15,0),
  -- Identifies the Oracle Retail module where the record was written.
  pgm_name VARCHAR2(100),
  -- This field will contain the eleventh account segment information coming from the financial system.
  acct_segment11 VARCHAR2(25),
  -- This field will contain the twelfth account segment information coming from the financial system.
  acct_segment12 VARCHAR2(25),
  -- This field will contain the thirteenth account segment information coming from the financial system.
  acct_segment13 VARCHAR2(25),
  -- This field will contain the fourteenth account segment information coming from the financial system.
  acct_segment14 VARCHAR2(25),
  -- This field will contain the fifteenth account segment information coming from the financial system.
  acct_segment15 VARCHAR2(25),
  -- This field will contain the sixteenth account segment information coming from the financial system.
  acct_segment16 VARCHAR2(25),
  -- This field will contain the seventeenth account segment information coming from the financial system.
  acct_segment17 VARCHAR2(25),
  -- This field will contain the eighteenth account segment information coming from the financial system.
  acct_segment18 VARCHAR2(25),
  -- This field will contain the nineteenth account segment information coming from the financial system.
  acct_segment19 VARCHAR2(25),
  -- This field will contain the twentieth account segment information coming from the financial system.
  acct_segment20 VARCHAR2(25),
  -- This field will contain the reference key which needs for drilling back the reference data from RMS/ReSA.
  reference_trace_id VARCHAR2(30),
  -- This field will contain the base or primary currency code of the RMS System.
  prim_currency_code VARCHAR2(15),
  -- This field will contain the Debit amount value for that particular record in primary currency.
  prim_entered_dr_amount NUMBER(20,4),
  -- This field will contain the Credit amount value for that particular record in primary currency.
  prim_entered_cr_amount NUMBER(20,4),
  -- Contains the Sequence from GL.
  fin_gl_seq_id NUMBER(15,0),
  -- Indicates if the record is processed.
  processed_flag VARCHAR2(1)
);

COMMENT ON TABLE FIF_GL_DATA_OUT IS 'This table will hold all the Stock Ledger information for RMS and Total ID information for ReSA to be sent across to Financial General Ledger.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN FIF_GL_DATA_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN FIF_GL_DATA_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN FIF_GL_DATA_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN FIF_GL_DATA_OUT.id IS 'This denotes unique identifier for the record.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.set_of_books_id IS 'This denotes the accounting books for an organization.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.accounting_date IS 'This denotes the effective date of the transaction.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.currency_code IS 'This denotes the Currency used for the Financial Information.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.status IS 'This denotes the status of the  when there is a Journal Import. Default to New.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.date_created IS 'This denotes the date when the transaction was done.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.created_by IS 'This denotes who created the transaction.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.actual_flag IS 'This denotes the Balance type and is default to A.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.user_je_category_name IS 'This denotes the Journal Entry Category User defined name.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.user_je_source_name IS 'This denotes the Journal Entry Source User defined name.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.currency_conversion_date IS 'This denotes the date of currency exchange rate.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.currency_conversion_type IS 'This denotes the type of currency exchange rate.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment1 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment2 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment3 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment4 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment5 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment6 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment7 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment8 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment9 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment10 IS 'This denotes the account segment for the accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.entered_dr_amount IS 'This denotes the transaction debit amount.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.entered_cr_amount IS 'This denotes the transaction credit amount.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.transaction_date IS 'This denotes the transaction date.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.reference1 IS 'This denotes the reference description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.reference2 IS 'This denotes the reference description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.reference3 IS 'This denotes the reference description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.reference4 IS 'This denotes the reference description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.reference5 IS 'This denotes the reference description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.attribute1 IS 'This denotes the attribute description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.attribute2 IS 'This denotes the attribute description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.attribute3 IS 'This denotes the attribute description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.attribute4 IS 'This denotes the attribute description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.attribute5 IS 'This denotes the attribute description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.attribute6 IS 'This denotes the attribute description to be used for the column.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.period_name IS 'This denotes the accounting period.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.code_combination_id IS 'This denotes the unique identification of an accounting structure.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.pgm_name IS 'Identifies the Oracle Retail module where the record was written.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment11 IS 'This field will contain the eleventh account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment12 IS 'This field will contain the twelfth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment13 IS 'This field will contain the thirteenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment14 IS 'This field will contain the fourteenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment15 IS 'This field will contain the fifteenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment16 IS 'This field will contain the sixteenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment17 IS 'This field will contain the seventeenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment18 IS 'This field will contain the eighteenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment19 IS 'This field will contain the nineteenth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.acct_segment20 IS 'This field will contain the twentieth account segment information coming from the financial system.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.reference_trace_id IS 'This field will contain the reference key which needs for drilling back the reference data from RMS/ReSA.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.prim_currency_code IS 'This field will contain the base or primary currency code of the RMS System.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.prim_entered_dr_amount IS 'This field will contain the Debit amount value for that particular record in primary currency.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.prim_entered_cr_amount IS 'This field will contain the Credit amount value for that particular record in primary currency.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.fin_gl_seq_id IS 'Contains the Sequence from GL.';
COMMENT ON COLUMN FIF_GL_DATA_OUT.processed_flag IS 'Indicates if the record is processed.';


-- Add BDI primary key constraint

ALTER TABLE FIF_GL_DATA_OUT ADD CONSTRAINT pk_fif_gl_data_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE FIF_GL_DATA_OUT ADD CONSTRAINT chk_type_fif_gl_data_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

ALTER TABLE FIF_GL_DATA_OUT ADD CONSTRAINT chk_actn_fif_gl_data_out CHECK (bdi_dataset_action IN ('REPLACE', 'CREATE', 'UPDATE', 'DELETE'));