samuel025

f_check_gl_balance

Jun 18th, 2021
815
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_check_gl_balance(character varying, bigint, bigint, character varying, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pSessionId          ALIAS FOR $1;
  6.     pTenantId           ALIAS FOR $2;
  7.     pOuId               ALIAS FOR $3;
  8.     pDocDateFrom        ALIAS FOR $4;
  9.     pDocDateTo          ALIAS FOR $5;
  10. BEGIN
  11.    
  12.     DELETE FROM tt_gl_check_balance_header WHERE session_id = pSessionId;
  13.     DELETE FROM tt_gl_check_balance_details WHERE session_id = pSessionId;
  14.    
  15.     /*
  16.      * gl_journal_trx_item
  17.      *
  18.      * */
  19.     INSERT INTO tt_gl_check_balance_header(
  20.             session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
  21.             doc_no, doc_date, type_coa, main_acc,
  22.             sub_acc, coa_desc, sign_coa, sign_journal, curr_code, amount,
  23.             gl_amount, sign_gl_amount, asset_amount, liabilities_amount,
  24.             revenue_amount, costs_amount, selisih_neraca, selisih_laba_rugi)
  25.     SELECT pSessionId, a.tenant_id, a.ou_bu_id, a.journal_trx_id, a.doc_type_id,
  26.             a.doc_no, a.doc_date, d.type_coa, c.main_acc,
  27.             c.sub_acc, c.coa_desc, c.sign_coa, b.sign_journal, b.curr_code, b.amount,
  28.             b.gl_amount, (CASE WHEN c.sign_coa <> b.sign_journal THEN b.gl_amount *-1 ELSE b.gl_amount END) AS sign_gl_amount,
  29.             0, 0, 0, 0, 0, 0
  30.     FROM gl_journal_trx a, gl_journal_trx_item b, m_coa c, m_group_coa d
  31.     WHERE a.tenant_id = pTenantId
  32.         AND a.ou_bu_id = pOuId
  33.         AND a.journal_trx_id = b.journal_trx_id
  34.         AND a.doc_date BETWEEN pDocDateFrom AND pDocDateTo
  35.         AND b.coa_id = c.coa_id
  36.         AND c.group_coa_id = d.group_coa_id;
  37.        
  38.     /*
  39.      * gl_journal_trx_mapping
  40.      *
  41.      * */
  42.     INSERT INTO tt_gl_check_balance_header(
  43.             session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
  44.             doc_no, doc_date, type_coa, main_acc,
  45.             sub_acc, coa_desc, sign_coa, sign_journal, curr_code, amount,
  46.             gl_amount, sign_gl_amount, asset_amount, liabilities_amount,
  47.             revenue_amount, costs_amount, selisih_neraca, selisih_laba_rugi)
  48.     SELECT pSessionId, a.tenant_id, a.ou_bu_id, a.journal_trx_id, a.doc_type_id,
  49.             a.doc_no, a.doc_date, d.type_coa, c.main_acc,
  50.             c.sub_acc, c.coa_desc, c.sign_coa, b.sign_journal, b.curr_code, b.amount,
  51.             b.gl_amount, (CASE WHEN c.sign_coa <> b.sign_journal THEN b.gl_amount *-1 ELSE b.gl_amount END) AS sign_gl_amount,
  52.             0, 0, 0, 0, 0, 0
  53.     FROM gl_journal_trx a, gl_journal_trx_mapping b, m_coa c, m_group_coa d
  54.     WHERE a.tenant_id = pTenantId
  55.         AND a.ou_bu_id = pOuId
  56.         AND a.journal_trx_id = b.journal_trx_id
  57.         AND a.doc_date BETWEEN pDocDateFrom AND pDocDateTo
  58.         AND b.coa_id = c.coa_id
  59.         AND c.group_coa_id = d.group_coa_id;
  60.        
  61.     /*
  62.      * gl_journal_trx_fx
  63.      *
  64.      * */  
  65.     INSERT INTO tt_gl_check_balance_header(
  66.             session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
  67.             doc_no, doc_date, type_coa, main_acc,
  68.             sub_acc, coa_desc, sign_coa, sign_journal, curr_code, amount,
  69.             gl_amount, sign_gl_amount, asset_amount, liabilities_amount,
  70.             revenue_amount, costs_amount, selisih_neraca, selisih_laba_rugi)
  71.     SELECT pSessionId, a.tenant_id, a.ou_bu_id, a.journal_trx_id, a.doc_type_id,
  72.             a.doc_no, a.doc_date, d.type_coa, c.main_acc,
  73.             c.sub_acc, c.coa_desc, c.sign_coa, b.sign_journal, b.curr_code, b.amount,
  74.             b.gl_amount, (CASE WHEN c.sign_coa <> b.sign_journal THEN b.gl_amount *-1 ELSE b.gl_amount END) AS sign_gl_amount,
  75.             0, 0, 0, 0, 0, 0
  76.     FROM gl_journal_trx a, gl_journal_trx_fx b, m_coa c, m_group_coa d
  77.     WHERE a.tenant_id = pTenantId
  78.         AND a.ou_bu_id = pOuId
  79.         AND a.journal_trx_id = b.journal_trx_id
  80.         AND a.doc_date BETWEEN pDocDateFrom AND pDocDateTo
  81.         AND b.coa_id = c.coa_id
  82.         AND c.group_coa_id = d.group_coa_id;
  83.        
  84.     UPDATE tt_gl_check_balance_header
  85.     SET asset_amount = sign_gl_amount * -1
  86.     WHERE type_coa = 'A'
  87.         AND sign_coa = 'C'
  88.         AND session_id = pSessionId;
  89.        
  90.     UPDATE tt_gl_check_balance_header
  91.     SET asset_amount = sign_gl_amount
  92.     WHERE type_coa = 'A'
  93.         AND sign_coa = 'D'
  94.         AND session_id = pSessionId;
  95.    
  96.     UPDATE tt_gl_check_balance_header
  97.     SET liabilities_amount = sign_gl_amount * -1
  98.     WHERE type_coa = 'L'
  99.         AND sign_coa = 'C'
  100.         AND session_id = pSessionId;
  101.        
  102.     UPDATE tt_gl_check_balance_header
  103.     SET liabilities_amount = sign_gl_amount
  104.     WHERE type_coa = 'L'
  105.         AND sign_coa = 'D'
  106.         AND session_id = pSessionId;
  107.    
  108.      UPDATE tt_gl_check_balance_header
  109.     SET revenue_amount = sign_gl_amount
  110.     WHERE type_coa = 'R'
  111.         AND sign_coa = 'C'
  112.         AND session_id = pSessionId;
  113.        
  114.     UPDATE tt_gl_check_balance_header
  115.     SET revenue_amount = sign_gl_amount * -1
  116.     WHERE type_coa = 'R'
  117.         AND sign_coa = 'D'
  118.         AND session_id = pSessionId;
  119.        
  120.     UPDATE tt_gl_check_balance_header
  121.     SET costs_amount = sign_gl_amount
  122.     WHERE type_coa = 'C'
  123.         AND sign_coa = 'C'
  124.         AND session_id = pSessionId;
  125.        
  126.     UPDATE tt_gl_check_balance_header
  127.     SET costs_amount = sign_gl_amount * -1
  128.     WHERE type_coa = 'C'
  129.         AND sign_coa = 'D'
  130.         AND session_id = pSessionId;
  131.    
  132.     UPDATE tt_gl_check_balance_header
  133.     SET selisih_neraca = asset_amount + liabilities_amount,
  134.         selisih_laba_rugi = revenue_amount + costs_amount
  135.     WHERE session_id = pSessionId;
  136.    
  137.     INSERT INTO tt_gl_check_balance_details(
  138.             session_id, tenant_id, ou_id, journal_trx_id, doc_type_id,
  139.             doc_no, doc_date, jumlah_selisih_neraca,
  140.             jumlah_selisih_laba_rugi, balance_amount, flg_balance)
  141.     SELECT pSessionId, tenant_id, ou_id, journal_trx_id, doc_type_id,
  142.             doc_no, doc_date, SUM(selisih_neraca),
  143.             SUM(selisih_laba_rugi), SUM(selisih_neraca) - SUM(selisih_laba_rugi), 'N'
  144.     FROM tt_gl_check_balance_header
  145.     WHERE session_id = pSessionId
  146.         AND tenant_id = pTenantId
  147.         AND ou_id = pOuId
  148.     GROUP BY tenant_id, ou_id, journal_trx_id, doc_type_id, doc_no, doc_date;
  149.        
  150.     UPDATE tt_gl_check_balance_details
  151.     SET flg_balance = 'Y'
  152.     WHERE balance_amount = 0
  153.         AND session_id = pSessionId;
  154.    
  155. END;
  156. $BODY$
  157.   LANGUAGE plpgsql VOLATILE
  158.   COST 100;
  159.   /
RAW Paste Data