-- Interface Module: InvcMtchFin_Tx
-- Interface: Im_Financials_In

-- Create BDI Inbound Table: IM_FINANCIALS_IN
-- This table is used to .


CREATE TABLE IM_FINANCIALS_IN (
  -- bdi internal column
  bdi_seq_id NUMBER NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20),
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20),
  -- this column holds a sequence number that is generated to act as a primary key for the table.
  seq_no NUMBER(10,0) NOT NULL,
  -- this column holds a code that identifies each type of financial transaction that can be written to this table.  valid values are:  tap a record with the tran   code tap is mapped to the trade accounts payable account.  unr  a record with the tran code unr is mapped to an unmatched receipt account.  vwt  a record with the tran code vwt is mapped to a variance within tolerance account.  ppa a record with the tran code ppa is mapped to a pre-paid asset account.  crn a record with the tran code cnr is mapped to a credit note account.  rwo a record with the tran code rwo is mapped to a receipt write-off account.  nmrch a record with the tran code nmrch is mapped to a non-merchandise account.  reason a record with the tran code reason is mapped to a reason code account.
  tran_code VARCHAR2(6) NOT NULL,
  -- this indicated whether the record is a credit or debit.  valid values are credit and debit.  this column is mandatory not null.
  debit_credit_ind VARCHAR2(6) NOT NULL,
  -- this column holds the invoice matching document id of the document being posted to the financials system.  this column is mandatory not null.
  doc_id NUMBER(10,0),
  -- parent id from the im_parent_invoices table that has been pre paid.
  parent_id NUMBER(10,0),
  -- this column holds the date the document was created.
  doc_date TIMESTAMP,
  -- contains the unique number identifying a specific shipment of goods within the system. for receipt write offs, the trade accounts payable  (tran code=tap), unmatched receipt (tran code=unr), and receipt write off  (tran code = rwo) posting records will contain a receipt id instead of a doc id.
  receipt_id NUMBER(12,0),
  -- this field contains the date the shipment is received. for receipt write offs, the trade accounts payable  (tran code=tap), unmatched receipt (tran code=unr), and receipt write off  (tran code = rwo) posting records will contain a receipt date instead of a doc date.
  receipt_date TIMESTAMP,
  -- this column holds the type of vendor for the document.  this vendor type could be supp (for any supplier) or the partner type of any partners.  this information is stored with the document.
  vendor_type VARCHAR2(6) NOT NULL,
  -- this column holds the id of the supplier or partner associated with the document.
  vendor VARCHAR2(10) NOT NULL,
  -- this column holds the purchase order that the document relates to.
  order_no NUMBER(12,0),
  -- this column holds the currency code that is associated with the document.
  currency_code VARCHAR2(3) NOT NULL,
  -- this column holds the amount being posted to financials for this tran_code.
  amount NUMBER(20,4) NOT NULL,
  -- this column holds the term calculated as the best term for the document.
  best_terms VARCHAR2(15),
  -- this column holds the date calculated as the best terms date for the document.
  best_terms_date TIMESTAMP,
  -- this column indicates whether the document has already been paid.  if the value is n (no) the document has not been paid.  if the value is y (yes), the document has already been paid.
  manually_paid_ind VARCHAR2(1) NOT NULL,
  -- this column indicates that this post is for a document that was paid prior to being matched with receipts.
  pre_paid_ind VARCHAR2(1) NOT NULL,
  -- The username identifier of the user who created a record.
  create_id VARCHAR2(60) NOT NULL,
  -- The date a record was created.
  create_datetime TIMESTAMP NOT NULL,
  -- this column holds the value of the first account segment.
  segment_1 VARCHAR2(25),
  -- this column holds the value of the second account segment.
  segment_2 VARCHAR2(25),
  -- this column holds the value of the third account segment.
  segment_3 VARCHAR2(25),
  -- this column holds the value of the fourth account segment.
  segment_4 VARCHAR2(25),
  -- this column holds the value of the fifth account segment.
  segment_5 VARCHAR2(25),
  -- this column holds the value of the sixth account segment.
  segment_6 VARCHAR2(25),
  -- this column holds the value of the seventh account segment.
  segment_7 VARCHAR2(25),
  -- this column holds the value of the eighth account segment.
  segment_8 VARCHAR2(25),
  -- this column holds the value of the ninth account segment.
  segment_9 VARCHAR2(25),
  -- this column holds the value of the tenth account segment.
  segment_10 VARCHAR2(25),
  -- this column holds the rms id for deal.
  deal_id NUMBER(10,0),
  -- this column holds the locations currency.
  local_currency VARCHAR2(3),
  -- this column holds the sum of all the income in the local currency.
  income_local_currency NUMBER(20,4),
  -- this column holds the id for the document that is used in communication with the vendor. this is the document id that will generally be displayed to the end user. ext_doc_id is unique by vendor. if the vendor created the document (i.e. merchandise invoice, etc), this field will hold the vendors id for the document.
  ext_doc_id VARCHAR2(150),
  -- this column specifies the exchange rate for the currency code.
  exchange_rate NUMBER(20,10),
  -- this column specifies the exchange rate type.
  exchange_rate_type VARCHAR2(30),
  -- set of books identifier.  default  value is -1.
  set_of_books_id NUMBER(15,0) NOT NULL,
  -- reference to posting process that added this entry.
  posting_id NUMBER(10,0),
  -- indicates if the posting entry is for tax accounting.
  tax_entry VARCHAR2(1),
  -- transaction amount.
  trans_amount NUMBER(20,4),
  -- primary currency code.
  prim_currency_code VARCHAR2(3),
  -- primary transaction amount.
  prim_trans_amount NUMBER(20,4),
  -- segment 11.
  segment_11 VARCHAR2(25),
  -- segment 12.
  segment_12 VARCHAR2(25),
  -- segment 13.
  segment_13 VARCHAR2(25),
  -- segment 14.
  segment_14 VARCHAR2(25),
  -- segment 15.
  segment_15 VARCHAR2(25),
  -- segment 16.
  segment_16 VARCHAR2(25),
  -- segment 17.
  segment_17 VARCHAR2(25),
  -- segment 18.
  segment_18 VARCHAR2(25),
  -- segment 19.
  segment_19 VARCHAR2(25),
  -- segment 20.
  segment_20 VARCHAR2(25),
  -- reference id.
  reference_id VARCHAR2(25),
  -- odi session number.
  odi_session_num NUMBER(10,0),
  -- vat code.
  vat_code VARCHAR2(6),
  -- vat rate.
  vat_rate NUMBER(20,10),
  -- total cost including vat.
  total_cost_inc_vat NUMBER(20,4)
);

COMMENT ON TABLE IM_FINANCIALS_IN IS 'This table is used to .';
COMMENT ON COLUMN IM_FINANCIALS_IN.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN IM_FINANCIALS_IN.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN IM_FINANCIALS_IN.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN IM_FINANCIALS_IN.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN IM_FINANCIALS_IN.seq_no IS 'this column holds a sequence number that is generated to act as a primary key for the table.';
COMMENT ON COLUMN IM_FINANCIALS_IN.tran_code IS 'this column holds a code that identifies each type of financial transaction that can be written to this table.  valid values are:  tap a record with the tran   code tap is mapped to the trade accounts payable account.  unr  a record with the tran code unr is mapped to an unmatched receipt account.  vwt  a record with the tran code vwt is mapped to a variance within tolerance account.  ppa a record with the tran code ppa is mapped to a pre-paid asset account.  crn a record with the tran code cnr is mapped to a credit note account.  rwo a record with the tran code rwo is mapped to a receipt write-off account.  nmrch a record with the tran code nmrch is mapped to a non-merchandise account.  reason a record with the tran code reason is mapped to a reason code account.';
COMMENT ON COLUMN IM_FINANCIALS_IN.debit_credit_ind IS 'this indicated whether the record is a credit or debit.  valid values are credit and debit.  this column is mandatory not null.';
COMMENT ON COLUMN IM_FINANCIALS_IN.doc_id IS 'this column holds the invoice matching document id of the document being posted to the financials system.  this column is mandatory not null.';
COMMENT ON COLUMN IM_FINANCIALS_IN.parent_id IS 'parent id from the im_parent_invoices table that has been pre paid.';
COMMENT ON COLUMN IM_FINANCIALS_IN.doc_date IS 'this column holds the date the document was created.';
COMMENT ON COLUMN IM_FINANCIALS_IN.receipt_id IS 'contains the unique number identifying a specific shipment of goods within the system. for receipt write offs, the trade accounts payable  (tran code=tap), unmatched receipt (tran code=unr), and receipt write off  (tran code = rwo) posting records will contain a receipt id instead of a doc id.';
COMMENT ON COLUMN IM_FINANCIALS_IN.receipt_date IS 'this field contains the date the shipment is received. for receipt write offs, the trade accounts payable  (tran code=tap), unmatched receipt (tran code=unr), and receipt write off  (tran code = rwo) posting records will contain a receipt date instead of a doc date.';
COMMENT ON COLUMN IM_FINANCIALS_IN.vendor_type IS 'this column holds the type of vendor for the document.  this vendor type could be supp (for any supplier) or the partner type of any partners.  this information is stored with the document.';
COMMENT ON COLUMN IM_FINANCIALS_IN.vendor IS 'this column holds the id of the supplier or partner associated with the document.';
COMMENT ON COLUMN IM_FINANCIALS_IN.order_no IS 'this column holds the purchase order that the document relates to.';
COMMENT ON COLUMN IM_FINANCIALS_IN.currency_code IS 'this column holds the currency code that is associated with the document.';
COMMENT ON COLUMN IM_FINANCIALS_IN.amount IS 'this column holds the amount being posted to financials for this tran_code.';
COMMENT ON COLUMN IM_FINANCIALS_IN.best_terms IS 'this column holds the term calculated as the best term for the document.';
COMMENT ON COLUMN IM_FINANCIALS_IN.best_terms_date IS 'this column holds the date calculated as the best terms date for the document.';
COMMENT ON COLUMN IM_FINANCIALS_IN.manually_paid_ind IS 'this column indicates whether the document has already been paid.  if the value is n (no) the document has not been paid.  if the value is y (yes), the document has already been paid.';
COMMENT ON COLUMN IM_FINANCIALS_IN.pre_paid_ind IS 'this column indicates that this post is for a document that was paid prior to being matched with receipts.';
COMMENT ON COLUMN IM_FINANCIALS_IN.create_id IS 'The username identifier of the user who created a record.';
COMMENT ON COLUMN IM_FINANCIALS_IN.create_datetime IS 'The date a record was created.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_1 IS 'this column holds the value of the first account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_2 IS 'this column holds the value of the second account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_3 IS 'this column holds the value of the third account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_4 IS 'this column holds the value of the fourth account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_5 IS 'this column holds the value of the fifth account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_6 IS 'this column holds the value of the sixth account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_7 IS 'this column holds the value of the seventh account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_8 IS 'this column holds the value of the eighth account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_9 IS 'this column holds the value of the ninth account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_10 IS 'this column holds the value of the tenth account segment.';
COMMENT ON COLUMN IM_FINANCIALS_IN.deal_id IS 'this column holds the rms id for deal.';
COMMENT ON COLUMN IM_FINANCIALS_IN.local_currency IS 'this column holds the locations currency.';
COMMENT ON COLUMN IM_FINANCIALS_IN.income_local_currency IS 'this column holds the sum of all the income in the local currency.';
COMMENT ON COLUMN IM_FINANCIALS_IN.ext_doc_id IS 'this column holds the id for the document that is used in communication with the vendor. this is the document id that will generally be displayed to the end user. ext_doc_id is unique by vendor. if the vendor created the document (i.e. merchandise invoice, etc), this field will hold the vendors id for the document.';
COMMENT ON COLUMN IM_FINANCIALS_IN.exchange_rate IS 'this column specifies the exchange rate for the currency code.';
COMMENT ON COLUMN IM_FINANCIALS_IN.exchange_rate_type IS 'this column specifies the exchange rate type.';
COMMENT ON COLUMN IM_FINANCIALS_IN.set_of_books_id IS 'set of books identifier.  default  value is -1.';
COMMENT ON COLUMN IM_FINANCIALS_IN.posting_id IS 'reference to posting process that added this entry.';
COMMENT ON COLUMN IM_FINANCIALS_IN.tax_entry IS 'indicates if the posting entry is for tax accounting.';
COMMENT ON COLUMN IM_FINANCIALS_IN.trans_amount IS 'transaction amount.';
COMMENT ON COLUMN IM_FINANCIALS_IN.prim_currency_code IS 'primary currency code.';
COMMENT ON COLUMN IM_FINANCIALS_IN.prim_trans_amount IS 'primary transaction amount.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_11 IS 'segment 11.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_12 IS 'segment 12.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_13 IS 'segment 13.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_14 IS 'segment 14.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_15 IS 'segment 15.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_16 IS 'segment 16.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_17 IS 'segment 17.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_18 IS 'segment 18.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_19 IS 'segment 19.';
COMMENT ON COLUMN IM_FINANCIALS_IN.segment_20 IS 'segment 20.';
COMMENT ON COLUMN IM_FINANCIALS_IN.reference_id IS 'reference id.';
COMMENT ON COLUMN IM_FINANCIALS_IN.odi_session_num IS 'odi session number.';
COMMENT ON COLUMN IM_FINANCIALS_IN.vat_code IS 'vat code.';
COMMENT ON COLUMN IM_FINANCIALS_IN.vat_rate IS 'vat rate.';
COMMENT ON COLUMN IM_FINANCIALS_IN.total_cost_inc_vat IS 'total cost including vat.';


-- Add BDI primary key constraint

ALTER TABLE IM_FINANCIALS_IN ADD CONSTRAINT pk_im_financials_in PRIMARY KEY (bdi_app_name, bdi_seq_id);