Acceptance Testing/Format loader script postgres

CREATE OR REPLACE FUNCTION create_imp_formats()
  RETURNS void AS
$BODY$ DECLARE 
v_client_id NUMERIC(10);
v_format_id NUMERIC(10);
v_formatrow_id NUMERIC(10);

BEGIN

SELECT MAX(AD_CLIENT_ID) INTO v_client_id FROM AD_CLIENT;

v_format_id := AD_SEQUENCE_NEXT('AD_ImpFormat', v_client_id);

--Business partner
INSERT INTO AD_IMPFORMAT (AD_IMPFORMAT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, AD_TABLE_ID, FORMATTYPE, PROCESSING)
VALUES
  (v_format_id, v_client_id, 0, 'Y', now(), 100, now(), 100, 'Business partner', 533, 'C', 'N');
  
  v_formatrow_id := AD_SEQUENCE_NEXT('AD_ImpFormat_Row', v_client_id);
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100, CONSTANTVALUE)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 10, 'Organization', 7893, 'C', '.', 'N', '0');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 20, 'Name', 7901, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, AD_IMPFORMAT_ID, 
  SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 30, 'Search key', 7887, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 40, 'Business partner category', 7966, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 50, 'Addres 1', 7899, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, AD_IMPFORMAT_ID,
   SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 60, 'Contact name', 7907, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 70, 'City', 7896, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 80, 'Country code', 7865, 'S', '.', 'N');
  
  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, v_client_id, 0, 'Y', now(), 100, now(), 100, v_format_id, 90, 'Region name', 7902, 'S', '.', 'N');
  

--Taxes

v_format_id:=v_format_id+1;
  
INSERT INTO AD_IMPFORMAT
  (AD_IMPFORMAT_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, NAME, AD_TABLE_ID, FORMATTYPE, PROCESSING)
VALUES
  (v_format_id, v_client_id, 0, 'Y', now(), 100, now(), 100, 'Taxes Import', 800222, 'C', 'N');

  v_formatrow_id:=v_formatrow_id+1;
  
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 10, 'Tax Name', 803725, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 20, 'Tax Description', 803726, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 30, 'Rate', 803730, 'N', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DATAFORMAT, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 40, 'Valid from', 803728, 'D', 'dd/mm/yyyy', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 50, 'Summary Level', 803729, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 60, 'SOPO Type', 803737, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 70, 'Parent Tax Name', 803752, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 80, 'Code Country From', 803753, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 90, 'Code Region From', 803755, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 100, 'Code Country To', 803754, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 110, 'Code Region To', 803756, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 120, 'Tax Category Name', 803741, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 130, 'Tax Cat. Description', 803742, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 140, 'BP Tax Cat Name', 803745, 'S', '.', 'N');
  v_formatrow_id:=v_formatrow_id+1;
  INSERT INTO AD_IMPFORMAT_ROW
  (AD_IMPFORMAT_ROW_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, UPDATED, UPDATEDBY, 
  AD_IMPFORMAT_ID, SEQNO, NAME, AD_COLUMN_ID, DATATYPE, DECIMALPOINT, DIVIDEBY100, CONSTANTVALUE)
  VALUES
  (v_formatrow_id, 0, 0, 'Y', now(), 100, now(), 100, v_format_id, 5, 'Organization', 803718, 'C', '.', 'N', '0');

PERFORM AD_UPDATE_SEQUENCE();

END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE;

SELECT create_imp_formats();

drop function create_imp_formats();