-- Interface Module: Partner_Fnd
-- Interface: Partner_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE partner_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: PARTNER_OUT
-- This table is used to integrate Partner information.

CREATE TABLE PARTNER_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT partner_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',
  -- Specifies the type of partner.  Valid values are Bank BK, Agent AG, Freight Forwarder FF, Importer IM, Broker BR, Factory FA, Applicant AP, Consolidator CO, and Consignee CN, Supplier hierarchy level 1 S1, Supplier hierarchy level 2 S2, Supplier hierarchy level 3 S3.
  partner_type VARCHAR2(6) NOT NULL,
  -- Unique identifying number for a partner within the system.  The user determines this number when a new partner is first added to the system.
  partner_id VARCHAR2(10) NOT NULL,
  -- Contains the partners description or name.
  partner_desc VARCHAR2(240) NOT NULL,
  -- Contains a code identifying the currency the partner uses for business transactions.
  currency_code VARCHAR2(3) NOT NULL,
  -- This field contains the partners preferred language.
  lang NUMBER(6,0),
  -- Determines whether the partner is currently active. Valid values include: A for an active partner or I for an inactive partner.  The status of a partner will be checked when an order is being created to make certain the partner is active.
  status VARCHAR2(1) NOT NULL,
  -- Contains the name of the partners representative contract.
  contact_name VARCHAR2(120) NOT NULL,
  -- Contains the phone number of the partners representative contact.
  contact_phone VARCHAR2(20) NOT NULL,
  -- Contains the fax number of the partners representative contact.
  contact_fax VARCHAR2(20),
  -- Contains the telex number of the partners representative contact.
  contact_telex VARCHAR2(20),
  -- Contains the e-mail address of the partners representative contact.
  contact_email VARCHAR2(100),
  -- Contains the manufacturers tax identification number.  This field is NULL when the Partner Type is Bank (BK).
  mfg_id VARCHAR2(18),
  -- Contains the country id to which the partner is assigned.  This field is NULL when the Partner Type is Bank (BK).
  principle_country_id VARCHAR2(3),
  -- Contains the line of credit the company has at the Bank in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).
  line_of_credit NUMBER(20,4),
  -- Contains the total amount of credit that the company has used or has charged against  in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).
  outstand_credit NUMBER(20,4),
  -- Contains the total amount that the company can still charge against in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).
  open_credit NUMBER(20,4),
  -- Contains the total amount of credit the company has used this year to date in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).
  ytd_credit NUMBER(20,4),
  -- Contains the year to date payments the bank has made on behalf of the company in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).
  ytd_drawdowns NUMBER(20,4),
  -- Contains the unique tax identification number of the partner.  This will be used for reporting during the Customs Entry process.
  tax_id VARCHAR2(18),
  -- Payment terms for the partner.  These terms specify when payment is due and if any discounts exist for early payment.  If populated, they will default on any invoice entered for this partner.
  terms VARCHAR2(15) NOT NULL,
  -- Indicates if the expense vendors services (e.g. snowplowing, window washing) must be confirmed as performed before paying an invoice from that expense vendor.  Valid values are Y (all service non-merchandise lines on an invoice from this expense vendor must be confirmed before the invoice can be paid) and N (services do not need to be confirmed or partner is not an expense vendor).
  service_perf_req_ind VARCHAR2(1) NOT NULL,
  -- Indicates where invoices from this expense vendor are paid - at the store or centrally through corporate accounting.  Valid values are S (paid at the store) and C (paid centrally).  This field will only be populated for expense vendors, and should only be S if using ReSA to accept payment at the store.
  invc_pay_loc VARCHAR2(6),
  -- Indicates where invoices from this expense vendor are received - at the store or centrally through corporate accounting.  Valid values are S (received at the store) and C (received centrally).  This field should only be populated when using invoice matching.
  invc_receive_loc VARCHAR2(6),
  -- Import country of the Import Authority.  This field is not populated for other partner types.
  import_country_id VARCHAR2(3),
  -- Indicates if an Import Authority is the primary Import Authority for an import country.  This field will always be N for other partner types.  There must be one and only one primary Import Authority for each country associated with an Import Authority on the partner table.
  primary_ia_ind VARCHAR2(1) NOT NULL,
  -- Contains any comments associated with the Partner.
  comment_desc VARCHAR2(2000),
  -- ID of the transfer entity with which an external finisher (partner_type = E) is associated.  Valid values are found on the TSF_ENTITY table.  A transfer entity is a group of locations that share legal requirements around product management.  If SYSTEM_OPTIONS.INTERCOMPANY_TRANSFER_IND = Y, then each external finisher will be required to have an associated TSF_ENTITY_ID.  If SYSTEM_OPTIONS.INTERCOMPANY_TRANSFER_IND = N, then transfer entity functionality is not used and PARTNER.TSF_ENTITY_ID will not be required.
  tsf_entity_id NUMBER(10,0),
  -- VAT (value added tax) region with which a partner is associated. Valid values will be found on the VAT_REGION table. If SYSTEM_OPTIONS.DEFAULT_TAX_TYPE is SVAT, then each partner will be required to have an associated VAT_REGION. For other default_tax_type, then VAT functionality is not used and PARTNER.VAT_REGION will not be required.
  vat_region NUMBER(4,0),
  -- Org Unit Id
  org_unit_id NUMBER(15,0),
  -- This wil hold the secondary name of the partner.
  partner_name_secondary VARCHAR2(240),
  -- This will indicate whether the system will update the stock for the external finisher when the 1st leg of the transfer is shipped. Valid values are Yes or No
  auto_rcv_stock_ind VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE PARTNER_OUT IS 'This table is used to integrate Partner information.';
COMMENT ON COLUMN PARTNER_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN PARTNER_OUT.partner_type IS 'Specifies the type of partner.  Valid values are Bank BK, Agent AG, Freight Forwarder FF, Importer IM, Broker BR, Factory FA, Applicant AP, Consolidator CO, and Consignee CN, Supplier hierarchy level 1 S1, Supplier hierarchy level 2 S2, Supplier hierarchy level 3 S3.';
COMMENT ON COLUMN PARTNER_OUT.partner_id IS 'Unique identifying number for a partner within the system.  The user determines this number when a new partner is first added to the system.';
COMMENT ON COLUMN PARTNER_OUT.partner_desc IS 'Contains the partners description or name.';
COMMENT ON COLUMN PARTNER_OUT.currency_code IS 'Contains a code identifying the currency the partner uses for business transactions.';
COMMENT ON COLUMN PARTNER_OUT.lang IS 'This field contains the partners preferred language.';
COMMENT ON COLUMN PARTNER_OUT.status IS 'Determines whether the partner is currently active. Valid values include: A for an active partner or I for an inactive partner.  The status of a partner will be checked when an order is being created to make certain the partner is active.';
COMMENT ON COLUMN PARTNER_OUT.contact_name IS 'Contains the name of the partners representative contract.';
COMMENT ON COLUMN PARTNER_OUT.contact_phone IS 'Contains the phone number of the partners representative contact.';
COMMENT ON COLUMN PARTNER_OUT.contact_fax IS 'Contains the fax number of the partners representative contact.';
COMMENT ON COLUMN PARTNER_OUT.contact_telex IS 'Contains the telex number of the partners representative contact.';
COMMENT ON COLUMN PARTNER_OUT.contact_email IS 'Contains the e-mail address of the partners representative contact.';
COMMENT ON COLUMN PARTNER_OUT.mfg_id IS 'Contains the manufacturers tax identification number.  This field is NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.principle_country_id IS 'Contains the country id to which the partner is assigned.  This field is NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.line_of_credit IS 'Contains the line of credit the company has at the Bank in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.outstand_credit IS 'Contains the total amount of credit that the company has used or has charged against  in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.open_credit IS 'Contains the total amount that the company can still charge against in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.ytd_credit IS 'Contains the total amount of credit the company has used this year to date in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.ytd_drawdowns IS 'Contains the year to date payments the bank has made on behalf of the company in the Partners currency.  This field is not NULL when the Partner Type is Bank (BK).';
COMMENT ON COLUMN PARTNER_OUT.tax_id IS 'Contains the unique tax identification number of the partner.  This will be used for reporting during the Customs Entry process.';
COMMENT ON COLUMN PARTNER_OUT.terms IS 'Payment terms for the partner.  These terms specify when payment is due and if any discounts exist for early payment.  If populated, they will default on any invoice entered for this partner.';
COMMENT ON COLUMN PARTNER_OUT.service_perf_req_ind IS 'Indicates if the expense vendors services (e.g. snowplowing, window washing) must be confirmed as performed before paying an invoice from that expense vendor.  Valid values are Y (all service non-merchandise lines on an invoice from this expense vendor must be confirmed before the invoice can be paid) and N (services do not need to be confirmed or partner is not an expense vendor).';
COMMENT ON COLUMN PARTNER_OUT.invc_pay_loc IS 'Indicates where invoices from this expense vendor are paid - at the store or centrally through corporate accounting.  Valid values are S (paid at the store) and C (paid centrally).  This field will only be populated for expense vendors, and should only be S if using ReSA to accept payment at the store.';
COMMENT ON COLUMN PARTNER_OUT.invc_receive_loc IS 'Indicates where invoices from this expense vendor are received - at the store or centrally through corporate accounting.  Valid values are S (received at the store) and C (received centrally).  This field should only be populated when using invoice matching.';
COMMENT ON COLUMN PARTNER_OUT.import_country_id IS 'Import country of the Import Authority.  This field is not populated for other partner types.';
COMMENT ON COLUMN PARTNER_OUT.primary_ia_ind IS 'Indicates if an Import Authority is the primary Import Authority for an import country.  This field will always be N for other partner types.  There must be one and only one primary Import Authority for each country associated with an Import Authority on the partner table.';
COMMENT ON COLUMN PARTNER_OUT.comment_desc IS 'Contains any comments associated with the Partner.';
COMMENT ON COLUMN PARTNER_OUT.tsf_entity_id IS 'ID of the transfer entity with which an external finisher (partner_type = E) is associated.  Valid values are found on the TSF_ENTITY table.  A transfer entity is a group of locations that share legal requirements around product management.  If SYSTEM_OPTIONS.INTERCOMPANY_TRANSFER_IND = Y, then each external finisher will be required to have an associated TSF_ENTITY_ID.  If SYSTEM_OPTIONS.INTERCOMPANY_TRANSFER_IND = N, then transfer entity functionality is not used and PARTNER.TSF_ENTITY_ID will not be required.';
COMMENT ON COLUMN PARTNER_OUT.vat_region IS 'VAT (value added tax) region with which a partner is associated. Valid values will be found on the VAT_REGION table. If SYSTEM_OPTIONS.DEFAULT_TAX_TYPE is SVAT, then each partner will be required to have an associated VAT_REGION. For other default_tax_type, then VAT functionality is not used and PARTNER.VAT_REGION will not be required.';
COMMENT ON COLUMN PARTNER_OUT.org_unit_id IS 'Org Unit Id';
COMMENT ON COLUMN PARTNER_OUT.partner_name_secondary IS 'This wil hold the secondary name of the partner.';
COMMENT ON COLUMN PARTNER_OUT.auto_rcv_stock_ind IS 'This will indicate whether the system will update the stock for the external finisher when the 1st leg of the transfer is shipped. Valid values are Yes or No';


-- Add BDI primary key constraint

ALTER TABLE PARTNER_OUT ADD CONSTRAINT pk_partner_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE PARTNER_OUT ADD CONSTRAINT chk_type_partner_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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