-- Interface Module: TranData_Tx
-- Interface: Tran_Data_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE tran_data_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: TRAN_DATA_OUT
-- This table is used to integrate RMS transaction data to MFP.

CREATE TABLE TRAN_DATA_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT tran_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',
  -- Indicates the end of week date that the tran data information pertains to.
  eow_date TIMESTAMP NOT NULL,
  -- Transaction level item only.
  item VARCHAR2(25) NOT NULL,
  -- Could be a store or warehouse.
  location NUMBER(10) NOT NULL,
  -- Indicates if the location is a store or warehouse - S = Store; W = Warehouse.
  loc_type VARCHAR2(1) NOT NULL,
  -- If Y, item/location is currently on clearance.
  clear_ind VARCHAR2(1) NOT NULL,
  -- Holds standard unit of measure for an item all the units related fields are expressed in.
  standard_uom VARCHAR2(4) NOT NULL,
  -- Holds the primary currency all cost and retail related fields are expressed in.
  currency_code VARCHAR2(3) NOT NULL,
  -- This field contains the number of units of the item involved in the transaction for tran_code = 1 or 3 and sales type = R.
  net_sales_reg_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 1 or 3 and sales type = R.
  net_sales_reg_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 1 or 3 and sales type = R.
  net_sales_reg_retail NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 1 or 3 and sales type = P.
  net_sales_promo_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 1 or 3 and sales type = P.
  net_sales_promo_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 1 or 3 and sales type = P.
  net_sales_promo_retail NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 1 or 3 and sales type = C.
  net_sales_clear_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 1 or 3 and sales type = C.
  net_sales_clear_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 1 or 3 and sales type = C.
  net_sales_clear_retail NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 2 or 5 and sales type = R.
  net_sales_reg_retail_vat_excl NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 2 or 5 and sales type = P.
  net_sales_promo_rtl_vat_excl NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 2 or 5 and sales type = C.
  net_sales_clr_retail_vat_excl NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 4 and sales type = R.
  returns_reg_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 4 and sales type = R.
  returns_reg_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 4 and sales type = R.
  returns_reg_retail NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 4 and sales type = P.
  returns_promo_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 4 and sales type = P.
  returns_promo_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 4 and sales type = P.
  returns_promo_retail NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 4 and sales type = C.
  returns_clear_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 4 and sales type = C.
  returns_clear_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 4 and sales type = C.
  returns_clear_retail NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 13.
  reg_markdown_retail NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code 15, if the item is not on clearance EOW.
  promo_markdown_retail_reg NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code 15, if the item is on clearance EOW.
  promo_markdown_retail_clear NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code 16.
  clear_markdown_retail NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code 85.
  wf_markdown_retail NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code 84.
  wf_markup_retail NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 22.
  shrink_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 22.
  shrink_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 22.
  shrink_retail NUMBER(20,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 6.
  deal_income_sales NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction, where tran_code = 20.
  po_receipt_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 20.
  po_receipt_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 20.
  po_receipt_retail NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction for tran_code = 23.
  non_shrink_adj_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 23.
  non_shrink_adj_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 23.
  non_shrink_adj_retail NUMBER(20,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 7.
  deal_income_purchases NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 11.
  markup NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 14.
  markdown_cancel NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 17.
  intercompany_markup NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code = 18.
  intercompany_markdown NUMBER(20,4),
  -- This field contains the Return to Vendor Units, where tran_code = 24.
  rtv_units NUMBER(12,4),
  -- This field contains the Return to Vendor Cost, where tran_code = 24.
  rtv_cost NUMBER(20,4),
  -- This field contains the Return to Vendor Retail, where tran_code = 24.
  rtv_retail NUMBER(20,4),
  -- This field contains the Transfer/Allocation Inbound Units, where tran_code = 30.
  tsf_in_units NUMBER(12,4),
  -- This field contains the Transfer/Allocation Inbound Cost, where tran_code = 30.
  tsf_in_cost NUMBER(20,4),
  -- This field contains the Transfer/Allocation Inbound Retail, where tran_code = 30.
  tsf_in_retail NUMBER(20,4),
  -- This field contains the Book Transfer/Allocation Inbound Units, where tran_code = 31.
  tsf_in_units_book NUMBER(12,4),
  -- This field contains the Book Transfer/Allocation Inbound Cost, where tran_code = 31.
  tsf_in_cost_book NUMBER(20,4),
  -- This field contains the Book Transfer/Allocation Inbound Retail, where tran_code = 31.
  tsf_in_retail_book NUMBER(20,4),
  -- This field contains the Transfer/Allocation Outbound Units, where tran_code = 32.
  tsf_out_units NUMBER(12,4),
  -- This field contains the Transfer/Allocation Outbound Cost, where tran_code = 32.
  tsf_out_cost NUMBER(20,4),
  -- This field contains the Transfer/Allocation Outbound Retail, where tran_code = 32.
  tsf_out_retail NUMBER(20,4),
  -- This field contains the Book Transfer/Allocation Outbound Units, where tran_code = 33.
  tsf_out_units_book NUMBER(12,4),
  -- This field contains the Book Transfer/Allocation Outbound Cost, where tran_code = 33.
  tsf_out_cost_book NUMBER(20,4),
  -- This field contains the Book Transfer/Allocation Outbound Retail, where tran_code = 33.
  tsf_out_retail_book NUMBER(20,4),
  -- This field contains the Reclass In Units, where tran_code = 34.
  reclass_in_units NUMBER(12,4),
  -- This field contains the Reclass In Cost, where tran_code = 34.
  reclass_in_cost NUMBER(20,4),
  -- This field contains the Reclass In Retail, where tran_code = 34.
  reclass_in_retail NUMBER(20,4),
  -- This field contains the Reclass Out Units, where tran_code = 36.
  reclass_out_units NUMBER(12,4),
  -- This field contains the Reclass Out Cost, where tran_code = 36.
  reclass_out_cost NUMBER(20,4),
  -- This field contains the Reclass Out Retail, where tran_code = 36.
  reclass_out_retail NUMBER(20,4),
  -- This field contains the Intercompany Transfer/Allocation Inbound Units, where tran_code = 37.
  tsf_in_units_ict NUMBER(12,4),
  -- This field contains the Intercompany Transfer/Allocation Inbound Cost, where tran_code = 37.
  tsf_in_cost_ict NUMBER(20,4),
  -- This field contains the Intercompany Transfer/Allocation Inbound Retail, where tran_code = 37.
  tsf_in_retail_ict NUMBER(20,4),
  -- This field contains the Intercompany Transfer/Allocation Outbound Units, where tran_code = 38.
  tsf_out_units_ict NUMBER(12,4),
  -- This field contains the Intercompany Transfer/Allocation Outbound Cost, where tran_code = 38.
  tsf_out_cost_ict NUMBER(20,4),
  -- This field contains the Intercompany Transfer/Allocation Outbound Retail, where tran_code = 38.
  tsf_out_retail_ict NUMBER(20,4),
  -- This field contains the total retail associated to the transaction, where tran_code = 39.
  intercompany_margin NUMBER(20,4),
  -- This field contains the number of units of the item involved in the transaction, where tran_code = 44.
  tsf_receipt_units NUMBER(12,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 44.
  tsf_receipt_cost NUMBER(20,4),
  -- This field contains the total retail value of the transaction, where tran_code =  44.
  tsf_receipt_retail NUMBER(20,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 65.
  rtv_restock_fee NUMBER(20,4),
  -- This field contains the Franchise Sales Units, where tran_code = 82.
  franchise_sales_units NUMBER(12,4),
  -- This field contains the Franchise Sales Cost, where tran_code = 82.
  franchise_sales_cost NUMBER(20,4),
  -- This field contains the Franchise Sales Retail, where tran_code = 82.
  franchise_sales_retail NUMBER(20,4),
  -- This field contains the Franchise Returns Units, where tran_code = 83.
  franchise_returns_units NUMBER(12,4),
  -- This field contains the Franchise Returns Cost, where tran_code = 83.
  franchise_returns_cost NUMBER(20,4),
  -- This field contains the Franchise Returns Retail, where tran_code = 83.
  franchise_returns_retail NUMBER(20,4),
  -- This field contains the total cost associated to the transaction, where tran_code = 86.
  franchise_restock_fee NUMBER(20,4)
);

COMMENT ON TABLE TRAN_DATA_OUT IS 'This table is used to integrate RMS transaction data to MFP.';
COMMENT ON COLUMN TRAN_DATA_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN TRAN_DATA_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN TRAN_DATA_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN TRAN_DATA_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN TRAN_DATA_OUT.eow_date IS 'Indicates the end of week date that the tran data information pertains to.';
COMMENT ON COLUMN TRAN_DATA_OUT.item IS 'Transaction level item only.';
COMMENT ON COLUMN TRAN_DATA_OUT.location IS 'Could be a store or warehouse.';
COMMENT ON COLUMN TRAN_DATA_OUT.loc_type IS 'Indicates if the location is a store or warehouse - S = Store; W = Warehouse.';
COMMENT ON COLUMN TRAN_DATA_OUT.clear_ind IS 'If Y, item/location is currently on clearance.';
COMMENT ON COLUMN TRAN_DATA_OUT.standard_uom IS 'Holds standard unit of measure for an item all the units related fields are expressed in.';
COMMENT ON COLUMN TRAN_DATA_OUT.currency_code IS 'Holds the primary currency all cost and retail related fields are expressed in.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_reg_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 1 or 3 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_reg_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 1 or 3 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_reg_retail IS 'This field contains the total retail value of the transaction, where tran_code = 1 or 3 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_promo_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 1 or 3 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_promo_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 1 or 3 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_promo_retail IS 'This field contains the total retail value of the transaction, where tran_code = 1 or 3 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_clear_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 1 or 3 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_clear_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 1 or 3 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_clear_retail IS 'This field contains the total retail value of the transaction, where tran_code = 1 or 3 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_reg_retail_vat_excl IS 'This field contains the total retail value of the transaction, where tran_code = 2 or 5 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_promo_rtl_vat_excl IS 'This field contains the total retail value of the transaction, where tran_code = 2 or 5 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.net_sales_clr_retail_vat_excl IS 'This field contains the total retail value of the transaction, where tran_code = 2 or 5 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_reg_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 4 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_reg_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 4 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_reg_retail IS 'This field contains the total retail value of the transaction, where tran_code = 4 and sales type = R.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_promo_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 4 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_promo_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 4 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_promo_retail IS 'This field contains the total retail value of the transaction, where tran_code = 4 and sales type = P.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_clear_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 4 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_clear_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 4 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.returns_clear_retail IS 'This field contains the total retail value of the transaction, where tran_code = 4 and sales type = C.';
COMMENT ON COLUMN TRAN_DATA_OUT.reg_markdown_retail IS 'This field contains the total retail value of the transaction, where tran_code = 13.';
COMMENT ON COLUMN TRAN_DATA_OUT.promo_markdown_retail_reg IS 'This field contains the total retail value of the transaction, where tran_code 15, if the item is not on clearance EOW.';
COMMENT ON COLUMN TRAN_DATA_OUT.promo_markdown_retail_clear IS 'This field contains the total retail value of the transaction, where tran_code 15, if the item is on clearance EOW.';
COMMENT ON COLUMN TRAN_DATA_OUT.clear_markdown_retail IS 'This field contains the total retail value of the transaction, where tran_code 16.';
COMMENT ON COLUMN TRAN_DATA_OUT.wf_markdown_retail IS 'This field contains the total retail value of the transaction, where tran_code 85.';
COMMENT ON COLUMN TRAN_DATA_OUT.wf_markup_retail IS 'This field contains the total retail value of the transaction, where tran_code 84.';
COMMENT ON COLUMN TRAN_DATA_OUT.shrink_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 22.';
COMMENT ON COLUMN TRAN_DATA_OUT.shrink_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 22.';
COMMENT ON COLUMN TRAN_DATA_OUT.shrink_retail IS 'This field contains the total retail value of the transaction, where tran_code = 22.';
COMMENT ON COLUMN TRAN_DATA_OUT.deal_income_sales IS 'This field contains the total cost associated to the transaction, where tran_code = 6.';
COMMENT ON COLUMN TRAN_DATA_OUT.po_receipt_units IS 'This field contains the number of units of the item involved in the transaction, where tran_code = 20.';
COMMENT ON COLUMN TRAN_DATA_OUT.po_receipt_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 20.';
COMMENT ON COLUMN TRAN_DATA_OUT.po_receipt_retail IS 'This field contains the total retail value of the transaction, where tran_code = 20.';
COMMENT ON COLUMN TRAN_DATA_OUT.non_shrink_adj_units IS 'This field contains the number of units of the item involved in the transaction for tran_code = 23.';
COMMENT ON COLUMN TRAN_DATA_OUT.non_shrink_adj_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 23.';
COMMENT ON COLUMN TRAN_DATA_OUT.non_shrink_adj_retail IS 'This field contains the total retail value of the transaction, where tran_code = 23.';
COMMENT ON COLUMN TRAN_DATA_OUT.deal_income_purchases IS 'This field contains the total cost associated to the transaction, where tran_code = 7.';
COMMENT ON COLUMN TRAN_DATA_OUT.markup IS 'This field contains the total retail value of the transaction, where tran_code = 11.';
COMMENT ON COLUMN TRAN_DATA_OUT.markdown_cancel IS 'This field contains the total retail value of the transaction, where tran_code = 14.';
COMMENT ON COLUMN TRAN_DATA_OUT.intercompany_markup IS 'This field contains the total retail value of the transaction, where tran_code = 17.';
COMMENT ON COLUMN TRAN_DATA_OUT.intercompany_markdown IS 'This field contains the total retail value of the transaction, where tran_code = 18.';
COMMENT ON COLUMN TRAN_DATA_OUT.rtv_units IS 'This field contains the Return to Vendor Units, where tran_code = 24.';
COMMENT ON COLUMN TRAN_DATA_OUT.rtv_cost IS 'This field contains the Return to Vendor Cost, where tran_code = 24.';
COMMENT ON COLUMN TRAN_DATA_OUT.rtv_retail IS 'This field contains the Return to Vendor Retail, where tran_code = 24.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_units IS 'This field contains the Transfer/Allocation Inbound Units, where tran_code = 30.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_cost IS 'This field contains the Transfer/Allocation Inbound Cost, where tran_code = 30.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_retail IS 'This field contains the Transfer/Allocation Inbound Retail, where tran_code = 30.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_units_book IS 'This field contains the Book Transfer/Allocation Inbound Units, where tran_code = 31.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_cost_book IS 'This field contains the Book Transfer/Allocation Inbound Cost, where tran_code = 31.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_retail_book IS 'This field contains the Book Transfer/Allocation Inbound Retail, where tran_code = 31.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_units IS 'This field contains the Transfer/Allocation Outbound Units, where tran_code = 32.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_cost IS 'This field contains the Transfer/Allocation Outbound Cost, where tran_code = 32.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_retail IS 'This field contains the Transfer/Allocation Outbound Retail, where tran_code = 32.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_units_book IS 'This field contains the Book Transfer/Allocation Outbound Units, where tran_code = 33.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_cost_book IS 'This field contains the Book Transfer/Allocation Outbound Cost, where tran_code = 33.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_retail_book IS 'This field contains the Book Transfer/Allocation Outbound Retail, where tran_code = 33.';
COMMENT ON COLUMN TRAN_DATA_OUT.reclass_in_units IS 'This field contains the Reclass In Units, where tran_code = 34.';
COMMENT ON COLUMN TRAN_DATA_OUT.reclass_in_cost IS 'This field contains the Reclass In Cost, where tran_code = 34.';
COMMENT ON COLUMN TRAN_DATA_OUT.reclass_in_retail IS 'This field contains the Reclass In Retail, where tran_code = 34.';
COMMENT ON COLUMN TRAN_DATA_OUT.reclass_out_units IS 'This field contains the Reclass Out Units, where tran_code = 36.';
COMMENT ON COLUMN TRAN_DATA_OUT.reclass_out_cost IS 'This field contains the Reclass Out Cost, where tran_code = 36.';
COMMENT ON COLUMN TRAN_DATA_OUT.reclass_out_retail IS 'This field contains the Reclass Out Retail, where tran_code = 36.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_units_ict IS 'This field contains the Intercompany Transfer/Allocation Inbound Units, where tran_code = 37.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_cost_ict IS 'This field contains the Intercompany Transfer/Allocation Inbound Cost, where tran_code = 37.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_in_retail_ict IS 'This field contains the Intercompany Transfer/Allocation Inbound Retail, where tran_code = 37.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_units_ict IS 'This field contains the Intercompany Transfer/Allocation Outbound Units, where tran_code = 38.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_cost_ict IS 'This field contains the Intercompany Transfer/Allocation Outbound Cost, where tran_code = 38.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_out_retail_ict IS 'This field contains the Intercompany Transfer/Allocation Outbound Retail, where tran_code = 38.';
COMMENT ON COLUMN TRAN_DATA_OUT.intercompany_margin IS 'This field contains the total retail associated to the transaction, where tran_code = 39.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_receipt_units IS 'This field contains the number of units of the item involved in the transaction, where tran_code = 44.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_receipt_cost IS 'This field contains the total cost associated to the transaction, where tran_code = 44.';
COMMENT ON COLUMN TRAN_DATA_OUT.tsf_receipt_retail IS 'This field contains the total retail value of the transaction, where tran_code =  44.';
COMMENT ON COLUMN TRAN_DATA_OUT.rtv_restock_fee IS 'This field contains the total cost associated to the transaction, where tran_code = 65.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_sales_units IS 'This field contains the Franchise Sales Units, where tran_code = 82.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_sales_cost IS 'This field contains the Franchise Sales Cost, where tran_code = 82.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_sales_retail IS 'This field contains the Franchise Sales Retail, where tran_code = 82.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_returns_units IS 'This field contains the Franchise Returns Units, where tran_code = 83.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_returns_cost IS 'This field contains the Franchise Returns Cost, where tran_code = 83.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_returns_retail IS 'This field contains the Franchise Returns Retail, where tran_code = 83.';
COMMENT ON COLUMN TRAN_DATA_OUT.franchise_restock_fee IS 'This field contains the total cost associated to the transaction, where tran_code = 86.';


-- Add BDI primary key constraint

ALTER TABLE TRAN_DATA_OUT ADD CONSTRAINT pk_tran_data_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE TRAN_DATA_OUT ADD CONSTRAINT chk_type_tran_data_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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