CREATE OR REPLACE FUNCTION generate_gljournallines()
  RETURNS void AS

$BODY$

DECLARE
    client_id VARCHAR(32) := '23C59575B9CF467C9620760EB255B389';
    org_id VARCHAR(32) := 'B843C30461EA4501935CB1D125C9C25A';
    user_id VARCHAR(32) := '100';
    description_batch VARCHAR(2000) := 'Ampliaciones de capital';
    description_gl VARCHAR(2000) := 'Ampliacion de capital';
    currency_id VARCHAR(2000) := '102';
    period_id VARCHAR(2000) := '311614366A88487B89B3E0D66100C0DC';
    batch_no VARCHAR(32);
    batch_id VARCHAR(32);
    gl_no VARCHAR(32);
    gl_id VARCHAR(32);
    acctschema_id VARCHAR(32) := 'F6488042ACD14B6A87EBF42DB13F9EFC';
    doctype_id VARCHAR(32) := 'BE84926D9C1B4C98866E3F5BE8621F02';
    glcategory_id VARCHAR(32) := 'EDA7B85AF9A5486D9B00CAFFD3B86FC2';
    validcomb_dr VARCHAR(32) := 'FA63454C68B84209906CC855EEAE6B0D';
    validcomb_cr VARCHAR(32) := '28EAF153C6DA46F382E8D71B9725ACCB';
    amount NUMERIC := 50000;
    i NUMERIC := 0;
    max NUMERIC := 10000;

BEGIN

    SELECT DOCUMENTNO
    INTO batch_no
    FROM GL_JOURNALBATCH
    WHERE AD_CLIENT_ID = client_id AND AD_ORG_ID = org_id
    ORDER BY DOCUMENTNO DESC
    LIMIT 1;

    batch_no := TO_CHAR(TO_NUMBER(batch_no)+1);

    batch_id := GET_UUID();

    -- Create batch
    INSERT INTO GL_JOURNALBATCH
	(GL_JOURNALBATCH_ID, AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY, DESCRIPTION, 
	DOCUMENTNO, POSTINGTYPE,
	C_PERIOD_ID, C_CURRENCY_ID, TOTALDR, TOTALCR)
	VALUES (batch_id, client_id, org_id, user_id, user_id, description_batch,
	batch_no, 'A',
	period_id, currency_id, amount, amount);


    SELECT DOCUMENTNO
        INTO gl_no
	FROM GL_JOURNAL
	WHERE AD_CLIENT_ID = client_id AND AD_ORG_ID = org_id
	ORDER BY DOCUMENTNO DESC
	LIMIT 1;

	gl_no := TO_CHAR(TO_NUMBER(gl_no)+1);
	
    WHILE i < max
    LOOP

	gl_no := TO_CHAR(TO_NUMBER(gl_no)+1);
	
	gl_id := GET_UUID();

	-- Create journal header
        INSERT INTO GL_JOURNAL
		(GL_JOURNALBATCH_ID, GL_JOURNAL_ID, AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY,
		C_ACCTSCHEMA_ID, C_DOCTYPE_ID, DOCUMENTNO, DESCRIPTION, POSTINGTYPE, DOCSTATUS, DOCACTION,
		GL_CATEGORY_ID, C_PERIOD_ID, C_CURRENCY_ID, TOTALDR, TOTALCR, DATEDOC, DATEACCT, CURRENCYRATETYPE, CURRENCYRATE)
		VALUES (batch_id, gl_id, client_id, org_id, user_id, user_id,
		acctschema_id, doctype_id, gl_no, description_gl, 'A', 'DR', 'CO',
		glcategory_id, period_id, currency_id, amount, amount, NOW(), NOW(), 'S', 1);

	-- Create journal lines
	-- Debit
	INSERT INTO GL_JOURNALLINE
		(GL_JOURNAL_ID, GL_JOURNALLINE_ID, AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY,
		DESCRIPTION, AMTSOURCEDR, AMTACCTDR, C_VALIDCOMBINATION_ID, C_CURRENCY_ID, CURRENCYRATETYPE, CURRENCYRATE, LINE)
		VALUES (gl_id, GET_UUID(), client_id, org_id, user_id, user_id,
		description_gl, amount, amount, validcomb_dr, currency_id, 'S', 1, 10);
	-- Credit
	INSERT INTO GL_JOURNALLINE
		(GL_JOURNAL_ID, GL_JOURNALLINE_ID, AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY,
		DESCRIPTION, AMTSOURCECR, AMTACCTCR, C_VALIDCOMBINATION_ID, C_CURRENCY_ID, CURRENCYRATETYPE, CURRENCYRATE, LINE)
		VALUES (gl_id, GET_UUID(), client_id, org_id, user_id, user_id,
		description_gl, amount, amount, validcomb_cr, currency_id, 'S', 1, 20);
		
	i := i+1;
    END LOOP;

    RETURN;

END

$BODY$

LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION generate_gljournallines()
  OWNER TO tad;