Re: [SQL] Best way to store Master-Detail Data - Mailing list pgsql-sql

CREATE TABLE sales_invoice
(
  id character varying(20) NOT NULL,
  customer_id character varying(20),
  branch_id integer,
  language_id integer,
  ncf character varying(19) NOT NULL,
  payment_term integer,
  payment_method integer,
  currency character varying(20),
  total_discount double precision,
  prompt_payment_discount_amount double precision,
  price_including_vat bit(1) NOT NULL,
  sales_invoice_date timestamp without time zone NOT NULL,
  create_by character varying(20),
  created_date timestamp without time zone NOT NULL,
  enable bit(1) NOT NULL,
)

CREATE TABLE sales_invoice_lines
(
  sales_invoice_id character varying(20) NOT NULL,
  line_no integer NOT NULL,
  item_id character varying(20),
  unit_of_measure integer,
  item_store integer,
  item_category integer,
  quantity double precision NOT NULL,
  price double precision NOT NULL,
  discount_amount double precision NOT NULL,
  vat_amount double precision NOT NULL,
  created_by character varying(20),
  created_date timestamp without time zone NOT NULL,
  enable bit(1) NOT NULL,

CREATE OR REPLACE FUNCTION save_sale_invoice(f_customer_id character varying, f_ncf_type character varying, f_branch_id integer, f_language_id integer, f_payment_term integer, f_payment_method integer, f_currency character varying, f_total_discount double precision, f_prompt_payment_discount_amount double precision, f_price_including_vat bit, f_sale_invoice_date timestamp without time zone, f_create_by character varying, f_line_no integer[], f_item_id character varying[], f_unit_of_measure integer[], f_item_store integer[], f_quantity double precision[], f_unit_price double precision[], f_discount_amount double precision[], f_vat_amount double precision[])
  RETURNS type_return_sales_invoice AS
$BODY$
  DECLARE
  new_invoice_number varchar(20);
  new_ncf varchar(19);
  lines_quantity int:=0;
  total_amount double precision:=0;    
  lines_transaction boolean:=false;
  entries_registered boolean:=false;
  f_apply_to_document_no varchar(20)[];
  f_amount_to_apply  double precision [];
  return_values type_return_sales_invoice;
  BEGIN
    lines_quantity:= array_upper(f_item_id,array_ndims(f_item_id));
    select * into new_invoice_number from getnexserialnumber(5);
    select * into new_ncf from ncf.getnexncf(1,f_ncf_type);

    return_values.document_no:=new_invoice_number;
    return_values.ncf:=new_ncf;
    insert into sales_invoice values(new_invoice_number,f_customer_id,f_branch_id,f_language_id,new_ncf,f_payment_term,
    f_payment_method,f_currency,f_total_discount,f_prompt_payment_discount_amount,f_price_including_vat,f_sale_invoice_date,f_create_by,current_timestamp,'B1');        
    select * into lines_transaction from save_sale_invoice_line(new_invoice_number , f_line_no ,  f_item_id,  f_unit_of_measure,f_item_store,f_quantity,f_unit_price, f_discount_amount, f_vat_amount,  f_create_by);

    FOR i in 1..lines_quantity
    LOOP
            total_amount:=total_amount+((f_quantity[i]*f_unit_price[i])-f_discount_amount[i]+f_vat_amount[i]);
    END LOOP;
        total_amount:=total_amount-f_total_discount;
        select * into entries_registered from save_customer_entry(f_customer_id,new_invoice_number,1,total_amount,f_sale_invoice_date,f_create_by,f_apply_to_document_no,f_amount_to_apply);

RETURN return_values;
  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION save_sale_invoice(character varying, character varying, integer, integer, integer, integer, character varying, double precision, double precision, bit, timestamp without time zone, character varying, integer[], character varying[], integer[], integer[], double precision[], double precision[], double precision[], double precision[])
  OWNER TO postgres;