-- Interface Module: InvcMtchAp_Tx
-- Interface: Im_Ap_Detail_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE im_ap_detail_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: IM_AP_DETAIL_OUT
-- This table is used to integrate related item details.  Related item details define the type of relationship and the related item. 

CREATE TABLE IM_AP_DETAIL_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT im_ap_detail_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',
  -- Holds the invoice matching document id of the document being posted to oracle financials 11.5.10.
  doc_id NUMBER(10,0) NOT NULL,
  -- Holds a sequence number that is generated to act as part of the primary key for the table, along with the doc_id column.
  seq_no NUMBER(10,0) NOT NULL,
  -- Holds the transaction code.
  tran_code VARCHAR2(6) NOT NULL,
  -- This value will vary; the rules are: if the tran-code is unr or vwt or reason or crn, then this value should be item. if this is a generated tax line, then this value should be tax. if none of the above, then this value should be miscellaneous.
  line_type_lookup_code VARCHAR2(25) NOT NULL,
  -- holds the amount from the transaction.  exception - for generated tax lines, the amount for this line should be the amount from the taxable line times the tax rate.
  amount NUMBER(20,4) NOT NULL,
  -- Holds the value of the first account segment.
  segment1 VARCHAR2(25),
  -- Holds the value of the second account segment.
  segment2 VARCHAR2(25),
  -- Holds the value of the third account segment.
  segment3 VARCHAR2(25),
  -- Holds the value of the fourth account segment.
  segment4 VARCHAR2(25),
  -- Holds the value of the fifth account segment.
  segment5 VARCHAR2(25),
  -- Holds the value of the sixth account segment.
  segment6 VARCHAR2(25),
  -- Holds the value of the seventh account segment.
  segment7 VARCHAR2(25),
  -- Holds the value of the eighth account segment.
  segment8 VARCHAR2(25),
  -- Holds the value of the ninth account segment.
  segment9 VARCHAR2(25),
  -- Holds the value of the tenth account segment.
  segment10 VARCHAR2(25),
  -- Holds the system date and time stamp of the creation of the posting header record.
  create_date_time TIMESTAMP NOT NULL,
  -- Set of books identifier, default value should be -1.
  set_of_books_id NUMBER(15,0) NOT NULL,
  -- Reference to posting process that added this row.
  posting_id NUMBER(10,0),
  -- Org unit.
  org_unit NUMBER(15,0),
  -- segment 11.
  segment11 VARCHAR2(25),
  -- segment 12.
  segment12 VARCHAR2(25),
  -- segment 13.
  segment13 VARCHAR2(25),
  -- segment 14.
  segment14 VARCHAR2(25),
  -- segment 15.
  segment15 VARCHAR2(25),
  -- segment 16.
  segment16 VARCHAR2(25),
  -- segment 17.
  segment17 VARCHAR2(25),
  -- segment 18.
  segment18 VARCHAR2(25),
  -- segment 19.
  segment19 VARCHAR2(25),
  -- segment 20.
  segment20 VARCHAR2(25),
  -- odi session number.
  odi_session_num NUMBER(10,0),
  -- reference id.
  reference_id VARCHAR2(25) NOT NULL,
  -- vat code.
  vat_code VARCHAR2(6),
  -- vat rate.
  vat_rate NUMBER(20,10),
  -- Groups together the TAX line and all lines that contribute to the TAX amounts. Only populated when the system option prorate_across_tax_codes is set to ?Y?.
  line_group_no NUMBER(10,0),
  -- Flag to identify tax postings subject to proration.
  prorate_across_flag VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE IM_AP_DETAIL_OUT IS 'This table is used to integrate related item details.  Related item details define the type of relationship and the related item. ';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.doc_id IS 'Holds the invoice matching document id of the document being posted to oracle financials 11.5.10.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.seq_no IS 'Holds a sequence number that is generated to act as part of the primary key for the table, along with the doc_id column.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.tran_code IS 'Holds the transaction code.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.line_type_lookup_code IS 'This value will vary; the rules are: if the tran-code is unr or vwt or reason or crn, then this value should be item. if this is a generated tax line, then this value should be tax. if none of the above, then this value should be miscellaneous.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.amount IS 'holds the amount from the transaction.  exception - for generated tax lines, the amount for this line should be the amount from the taxable line times the tax rate.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment1 IS 'Holds the value of the first account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment2 IS 'Holds the value of the second account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment3 IS 'Holds the value of the third account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment4 IS 'Holds the value of the fourth account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment5 IS 'Holds the value of the fifth account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment6 IS 'Holds the value of the sixth account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment7 IS 'Holds the value of the seventh account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment8 IS 'Holds the value of the eighth account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment9 IS 'Holds the value of the ninth account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment10 IS 'Holds the value of the tenth account segment.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.create_date_time IS 'Holds the system date and time stamp of the creation of the posting header record.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.set_of_books_id IS 'Set of books identifier, default value should be -1.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.posting_id IS 'Reference to posting process that added this row.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.org_unit IS 'Org unit.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment11 IS 'segment 11.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment12 IS 'segment 12.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment13 IS 'segment 13.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment14 IS 'segment 14.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment15 IS 'segment 15.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment16 IS 'segment 16.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment17 IS 'segment 17.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment18 IS 'segment 18.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment19 IS 'segment 19.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.segment20 IS 'segment 20.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.odi_session_num IS 'odi session number.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.reference_id IS 'reference id.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.vat_code IS 'vat code.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.vat_rate IS 'vat rate.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.line_group_no IS 'Groups together the TAX line and all lines that contribute to the TAX amounts. Only populated when the system option prorate_across_tax_codes is set to ?Y?.';
COMMENT ON COLUMN IM_AP_DETAIL_OUT.prorate_across_flag IS 'Flag to identify tax postings subject to proration.';


-- Add BDI primary key constraint

ALTER TABLE IM_AP_DETAIL_OUT ADD CONSTRAINT pk_im_ap_detail_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE IM_AP_DETAIL_OUT ADD CONSTRAINT chk_type_im_ap_detail_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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