Advertisement
henikseptiana15

r_ledger_card_report_grouping

Jan 31st, 2018
249
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION r_ledger_card_report_grouping(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, character varying, character varying)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader          REFCURSOR := 'refHeader';
  6.     pRefDetail          REFCURSOR := 'refDetail';
  7.     pSessionId          ALIAS FOR $1;
  8.     pTenantId           ALIAS FOR $2;
  9.     pUserId             ALIAS FOR $3;
  10.     pRoleId             ALIAS FOR $4;
  11.     pDatetime           ALIAS FOR $5;
  12.     pOuBuId             ALIAS FOR $6;
  13.     pOuBranchId         ALIAS FOR $7;
  14.     pOuSubBuId          ALIAS FOR $8;
  15.     pGroupCoaId         ALIAS FOR $9;
  16.     pOuRc               ALIAS FOR $10;
  17.     pSegment            ALIAS FOR $11;
  18.     pPeriodFrom         ALIAS FOR $12;
  19.     pPeriodTo           ALIAS FOR $13;
  20.     pMainAcc            ALIAS FOR $14;
  21.     pSubAcc             ALIAS FOR $15;
  22.    
  23.     vEmptyId            bigint := -99;
  24.     vEmptyString        character varying := '';
  25.     vEmptyAmount        numeric := 0;
  26.     vYes                character varying := 'Y';
  27.     vStatusDocTrx       character varying := '';
  28.     vStatusDocDraft     character varying := 'D';
  29.     vStatusDocRelease   character varying := 'R';
  30.     vSignDebt           character varying := 'D';
  31.     vSignCredit         character varying := 'C';
  32.     vTypeRateCom        character varying := 'COM';
  33.     vCurrGL             character varying(5);
  34.     vJoinOuRc           text := '';
  35.     vJoinSegment        text := '';
  36.     vFilterBranch       text := '';
  37.     vFilterSubBu        text := '';
  38.     vFilterGroupCoa     text := '';
  39.     vFilterOuRc         text := '';
  40.     vFilterSegment      text := '';
  41.     vFilterCoaMainAcc   text := '';
  42.     vFilterCoaSubAcc    text := '';
  43.     vRoundingDigit      integer;
  44.     vTypeSaldoAwal      character varying := 'SALDO AWAL';
  45.    
  46. BEGIN
  47.     vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
  48.    
  49.     DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
  50.     DELETE FROM tr_gl_detail_ledger_card WHERE session_id = pSessionId;
  51.    
  52.     /* ambil nilai pembulatan*/
  53.     vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
  54.    
  55.     /* vJoinOuRc, jika ouRc diisi */
  56.     IF (pOuRc <> vEmptyString) THEN
  57.         vJoinOuRc := '  INNER JOIN t_ou K ON B.ou_rc_id = K.ou_id
  58.                         INNER JOIN t_ou_type L ON K.ou_type_id = L.ou_type_id AND L.flg_accounting = '''|| vYes || '''';
  59.     END IF;
  60.    
  61.     /* vJoinSegment, jika segment diisi*/
  62.     IF (pSegment <> vEmptyString) THEN
  63.         vJoinSegment := ' INNER JOIN m_segment_coa M ON B.segmen_id = M.segment_coa_id ';
  64.     END IF;
  65.    
  66.     /* branchId <> -99*/
  67.     IF (pOuBranchId <> vEmptyId) THEN
  68.         vFilterBranch := ' AND B.ou_branch_id = ' || pOuBranchId;
  69.     END IF;
  70.    
  71.     /* subBuId <> -99*/
  72.     IF (pOuSubBuId <> vEmptyId) THEN
  73.         vFilterSubBu := ' AND B.ou_sub_bu_id =  ' || pOuSubBuId;
  74.     END IF;
  75.    
  76.     /* gorupCoaId <> -99*/
  77.     IF (pGroupCoaId <> vEmptyId) THEN
  78.         vFilterGroupCoa := ' AND E.group_coa_id = ' || pGroupCoaId;
  79.     END IF;
  80.    
  81.     /* jika ouRc diisi*/
  82.     IF (pOuRc <> vEmptyString) THEN
  83.         vFilterOuRc := ' AND UPPER(K.ou_name) LIKE UPPER(''%' ||  pOuRc || '%'')';
  84.     END IF;
  85.    
  86.     /* jika segment diisi*/
  87.     IF (pSegment <> vEmptyString) THEN
  88.         vFilterSegment := ' AND UPPER(M.segment_coa_name) LIKE UPPER(''%' || pSegment || '%'')';
  89.     END IF;
  90.    
  91.     /* jika main acc diisi*/
  92.     IF (pMainAcc <> vEmptyString) THEN
  93.         vFilterCoaMainAcc := ' AND E.main_acc LIKE ''' || pMainAcc || '%''';
  94.     END IF;
  95.  
  96.     /* jika sub acc diisi*/
  97.     IF (pSubAcc <> vEmptyString) THEN
  98.         vFilterCoaSubAcc := ' AND E.sub_acc LIKE ''' || pSubAcc || '%''';
  99.     END IF;
  100.    
  101.     /*
  102.      * ambil saldo awal ledger card
  103.      */
  104.     PERFORM gl_get_ledger_card_beginning_balance(pSessionId, pTenantId, pUserId, pRoleId, pDatetime, pOuBuId, pOuBranchId, pOuSubBuId, pGroupCoaId, pOuRc, pSegment, pPeriodFrom, pMainAcc, pSubAcc);
  105.    
  106.     /* ambil data detail yang, status doc = R
  107.      * 1) Ada transaksi tapi tidak ada saldo
  108.      * 2) Ada transaksi dan ada saldo awal
  109.      */
  110.     EXECUTE '
  111.     INSERT INTO tr_gl_detail_ledger_card(
  112.            session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
  113.            doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
  114.            journal_date, numerator_rate, denominator_rate, gl_curr_code,
  115.            gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
  116.            ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
  117.            sub_acc, beginning_balance, sign_coa, coa_desc, remark, order_num)
  118.     WITH gl_journal_trx_details AS (
  119.         SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
  120.               a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
  121.               a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark,
  122.               CASE WHEN a.ou_branch_id = $7 AND a.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE a.ou_branch_id END AS ou_branch_id,
  123.               CASE WHEN a.ou_branch_id = $7 AND a.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE a.ou_sub_bu_id END AS ou_sub_bu_id    
  124.         FROM gl_journal_trx_item a
  125.         INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
  126.         INNER JOIN dt_date p ON x.doc_date = p.string_date
  127.         WHERE a.tenant_id = $2
  128.         AND p.year_month_date BETWEEN $3 AND $4
  129.           UNION ALL
  130.         SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
  131.               b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
  132.               b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark,
  133.               CASE WHEN b.ou_branch_id = $7 AND b.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE b.ou_branch_id END AS ou_branch_id,
  134.               CASE WHEN b.ou_branch_id = $7 AND b.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE b.ou_sub_bu_id END AS ou_sub_bu_id
  135.         FROM gl_journal_trx_mapping b
  136.         INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id  
  137.         INNER JOIN dt_date p ON x.doc_date = p.string_date
  138.         WHERE b.tenant_id = $2
  139.         AND p.year_month_date BETWEEN $3 AND $4
  140.           UNION ALL
  141.         SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
  142.               c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
  143.               c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark,
  144.               CASE WHEN c.ou_branch_id = $7 AND c.ou_sub_bu_id = $7 THEN x.ou_branch_id ELSE c.ou_branch_id END AS ou_branch_id,
  145.               CASE WHEN c.ou_branch_id = $7 AND c.ou_sub_bu_id = $7 THEN x.ou_sub_bu_id ELSE c.ou_sub_bu_id END AS ou_sub_bu_id
  146.         FROM gl_journal_trx_fx c
  147.         INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
  148.         INNER JOIN dt_date p ON x.doc_date = p.string_date
  149.         WHERE c.tenant_id = $2
  150.         AND p.year_month_date BETWEEN $3 AND $4
  151.     )
  152.    SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
  153.             A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
  154.             B.journal_date, B.numerator_rate, B.denominator_rate, B.gl_curr_code,
  155.             B.gl_amount, B.ou_branch_id, f_get_ou_name(B.ou_branch_id) AS branch_name, B.ou_sub_bu_id,f_get_ou_name(B.ou_sub_bu_id) AS sub_bu_name,
  156.             B.ou_rc_id, f_get_ou_name(B.ou_rc_id) AS rc_name, B.segmen_id, f_get_segment_name(B.segmen_id) AS segment_name,
  157.             B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
  158.             CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark, 2
  159.     FROM gl_journal_trx A
  160.     INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
  161.     INNER JOIN m_coa E ON B.coa_id = E.coa_id' ||
  162.     vJoinOuRc ||
  163.     vJoinSegment ||
  164.     ' LEFT OUTER JOIN tr_gl_beginning_balance_ledger_card C ON A.ou_bu_id = C.ou_bu_id AND A.ou_branch_id = C.ou_branch_id
  165.         AND A.ou_sub_bu_id = C.ou_sub_bu_id AND B.coa_id = C.coa_id AND B.ou_rc_id = C.ou_rc_id AND B.segmen_id = C.segment_id
  166.         AND C.session_id = $1
  167.     WHERE A.tenant_id = $2
  168.         AND A.status_doc = $5
  169.         AND A.ou_bu_id = $6' ||
  170.         vFilterBranch ||
  171.         vFilterSubBu ||
  172.         vFilterGroupCoa ||
  173.         vFilterOuRc ||
  174.         vFilterSegment ||
  175.         vFilterCoaMainAcc ||
  176.         vFilterCoaSubAcc ||
  177.     ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, pPeriodFrom, pPeriodTo, vStatusDocRelease, pOuBuId, vEmptyId;
  178.    
  179.     /* ambil data detail, status doc = D */
  180.     EXECUTE '
  181.     INSERT INTO tr_gl_detail_ledger_card(
  182.            session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
  183.            doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
  184.            journal_date, numerator_rate, denominator_rate, gl_curr_code,
  185.            gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
  186.            ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
  187.            sub_acc, beginning_balance, sign_coa, coa_desc, remark, order_num)
  188.     WITH gl_journal_trx_details AS (
  189.         SELECT a.journal_trx_id, a.amount, a.gl_amount, a.sign_journal,
  190.               a.curr_code, a.journal_date, a.numerator_rate, a.denominator_rate,
  191.               a.gl_curr_code, a.ou_rc_id, a.segmen_id, a.coa_id, a.remark, a.type_rate,
  192.               CASE WHEN a.ou_branch_id = $10 AND a.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE a.ou_branch_id END AS ou_branch_id,
  193.               CASE WHEN a.ou_branch_id = $10 AND a.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE a.ou_sub_bu_id END AS ou_sub_bu_id  
  194.         FROM gl_journal_trx_item a
  195.         INNER JOIN gl_journal_trx x ON a.journal_trx_id = x.journal_trx_id
  196.         INNER JOIN dt_date p ON x.doc_date = p.string_date
  197.         WHERE a.tenant_id = $2
  198.         AND p.year_month_date BETWEEN $3 AND $4
  199.           UNION ALL
  200.         SELECT b.journal_trx_id, b.amount, b.gl_amount, b.sign_journal,
  201.               b.curr_code, b.journal_date, b.numerator_rate, b.denominator_rate,
  202.               b.gl_curr_code, b.ou_rc_id, b.segmen_id, b.coa_id, b.remark, b.type_rate,
  203.               CASE WHEN b.ou_branch_id = $10 AND b.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE b.ou_branch_id END AS ou_branch_id,
  204.               CASE WHEN b.ou_branch_id = $10 AND b.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE b.ou_sub_bu_id END AS ou_sub_bu_id  
  205.         FROM gl_journal_trx_mapping b
  206.         INNER JOIN gl_journal_trx x ON b.journal_trx_id = x.journal_trx_id  
  207.         INNER JOIN dt_date p ON x.doc_date = p.string_date
  208.         WHERE b.tenant_id = $2
  209.         AND p.year_month_date BETWEEN $3 AND $4
  210.           UNION ALL
  211.         SELECT c.journal_trx_id, c.amount, c.gl_amount, c.sign_journal,
  212.               c.curr_code, c.journal_date, c.numerator_rate, C.denominator_rate,
  213.               c.gl_curr_code, c.ou_rc_id, c.segmen_id, c.coa_id, c.remark, $8,
  214.               CASE WHEN c.ou_branch_id = $10 AND c.ou_sub_bu_id = $10 THEN x.ou_branch_id ELSE c.ou_branch_id END AS ou_branch_id,
  215.               CASE WHEN c.ou_branch_id = $10 AND c.ou_sub_bu_id = $10 THEN x.ou_sub_bu_id ELSE c.ou_sub_bu_id END AS ou_sub_bu_id  
  216.         FROM gl_journal_trx_fx c
  217.         INNER JOIN gl_journal_trx x ON c.journal_trx_id = x.journal_trx_id
  218.         INNER JOIN dt_date p ON x.doc_date = p.string_date
  219.         WHERE c.tenant_id = $2
  220.         AND p.year_month_date BETWEEN $3 AND $4
  221.     ), tt_gl_admin_journal_rate AS (
  222.         SELECT A.type_rate, A.journal_date, A.curr_code, $9, f_get_exchange_rate($2, A.type_rate, A.journal_date, A.curr_code, $9) AS data_exchange_rate
  223.         FROM (
  224.             SELECT A.journal_date, A.type_rate, A.curr_code
  225.             FROM gl_journal_trx_details A
  226.             GROUP BY journal_date, type_rate, curr_code
  227.         ) A
  228.     )
  229.    SELECT $1, $2, A.ou_bu_id, A.doc_type_id, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
  230.             A.doc_id, A.doc_no, A.doc_date, B.sign_journal, B.curr_code, B.amount,
  231.             B.journal_date, (D.data_exchange_rate).numerator_rate AS numerator_rate, (D.data_exchange_rate).denominator_rate AS denominator_rate, CASE WHEN (B.gl_curr_code is null OR TRIM(B.gl_curr_code) = '''') THEN $9 ELSE B.gl_curr_code END gl_curr_code,
  232.             ROUND(B.amount * (D.data_exchange_rate).numerator_rate / (D.data_exchange_rate).denominator_rate, $7) AS gl_amount,
  233.             B.ou_branch_id, f_get_ou_name(B.ou_branch_id) AS branch_name, B.ou_sub_bu_id, f_get_ou_name(B.ou_sub_bu_id) AS sub_bu_name,
  234.             B.ou_rc_id, f_get_ou_name(B.ou_rc_id) AS rc_name, B.segmen_id, f_get_segment_name(B.segmen_id) AS segment_name,
  235.             B.coa_id, E.main_acc, E.sub_acc, COALESCE(C.beginning_balance, 0) AS beginning_balance, E.sign_coa, E.coa_desc,
  236.             CASE WHEN (B.remark is null OR TRIM(B.remark) = '''') THEN A.remark ELSE B.remark END AS remark, 2
  237.     FROM gl_journal_trx A
  238.     INNER JOIN gl_journal_trx_details B ON A.journal_trx_id = B.journal_trx_id
  239.     INNER JOIN m_coa E ON B.coa_id = E.coa_id
  240.     INNER JOIN tt_gl_admin_journal_rate D ON D.type_rate=B.type_rate AND B.journal_date=D.journal_date AND D.curr_code=B.curr_code'||
  241.     vJoinOuRc ||
  242.     vJoinSegment ||
  243.     ' LEFT OUTER JOIN tr_gl_beginning_balance_ledger_card C ON A.ou_bu_id = C.ou_bu_id AND A.ou_branch_id = C.ou_branch_id
  244.         AND A.ou_sub_bu_id = C.ou_sub_bu_id AND B.coa_id = C.coa_id AND B.ou_rc_id = C.ou_rc_id AND B.segmen_id = C.segment_id
  245.         AND C.session_id = $1
  246.     WHERE A.tenant_id = $2
  247.         AND A.status_doc = $5
  248.         AND A.ou_bu_id = $6' ||
  249.         vFilterBranch ||
  250.         vFilterSubBu ||
  251.         vFilterGroupCoa ||
  252.         vFilterOuRc ||
  253.         vFilterSegment ||
  254.         vFilterCoaMainAcc ||
  255.         vFilterCoaSubAcc ||
  256.     ' ORDER BY A.doc_no, A.doc_date ' USING pSessionId, pTenantId, pPeriodFrom, pPeriodTo, vStatusDocDraft, pOuBuId, vRoundingDigit, vTypeRateCom, vCurrGL, vEmptyId;
  257.    
  258.     /* ambil data detail yang tidak ada transaksi tapi ada saldo */
  259.     INSERT INTO tr_gl_detail_ledger_card(
  260.             session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
  261.             doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
  262.             journal_date, gl_curr_code,
  263.             gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
  264.             ou_rc_id, rc_name, segment_id, segment_name,
  265.             coa_id, main_acc, sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
  266.     SELECT pSessionId, pTenantId, A.ou_bu_id, vEmptyId, vEmptyString,
  267.             vEmptyId, vEmptyString, vEmptyString, L.sign_coa, vEmptyString, vEmptyAmount,
  268.             vEmptyString, vEmptyString,
  269.             vEmptyAmount, A.ou_branch_id, f_get_ou_name(A.ou_branch_id) AS branch_name, A.ou_sub_bu_id, f_get_ou_name(A.ou_sub_bu_id) AS sub_bu_name,
  270.             A.ou_rc_id, f_get_ou_name(A.ou_rc_id) AS rc_name, A.segment_id, f_get_segment_name(A.segment_id) AS segment_name,
  271.             A.coa_id, L.main_acc, L.sub_acc, COALESCE(A.beginning_balance, 0) AS beginning_balance, L.sign_coa, L.coa_desc, 1, vEmptyString
  272.     FROM tr_gl_beginning_balance_ledger_card A
  273.     INNER JOIN m_coa L ON A.coa_id = L.coa_id
  274.     WHERE NOT EXISTS (SELECT 1
  275.                        FROM tr_gl_detail_ledger_card B
  276.                        WHERE A.ou_bu_id = B.ou_bu_id
  277.                             AND A.ou_branch_id = B.ou_branch_id
  278.                             AND A.ou_sub_bu_id = B.ou_sub_bu_id
  279.                             AND A.coa_id = B.coa_id
  280.                             AND A.ou_rc_id = B.ou_rc_id
  281.                             AND A.segment_id = B.segment_id
  282.                             AND A.session_id = B.session_id)
  283.         AND A.tenant_id = pTenantId
  284.         AND A.session_id = pSessionId;                 
  285.     /*         
  286.     /* Insert data beginning balance yang ada transaksinya ambil dari table tr_gl_detail_ledger_card yang order_num= 2 */
  287.     INSERT INTO tr_gl_detail_ledger_card(
  288.         session_id, tenant_id, ou_bu_id, doc_type_id, doc_type_desc,
  289.         doc_id, doc_no, doc_date, sign_journal, curr_code_trx, amount_trx,
  290.         journal_date, gl_curr_code,
  291.         gl_amount, ou_branch_id, branch_name, ou_sub_bu_id, sub_bu_name,
  292.         ou_rc_id, rc_name, segment_id, segment_name, coa_id, main_acc,
  293.         sub_acc, beginning_balance, sign_coa, coa_desc, order_num, remark)
  294.     WITH data_saldo_awal_with_transaksi AS (
  295.         SELECT A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
  296.             A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
  297.             A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
  298.         FROM tr_gl_detail_ledger_card A
  299.         WHERE A.session_id = pSessionId
  300.         AND A.tenant_id = pTenantId
  301.         AND A.order_num = 2
  302.         GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
  303.             A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
  304.             A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc
  305.     )    
  306.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, vEmptyId, vTypeSaldoAwal,
  307.         vEmptyId, vEmptyString, vEmptyString, A.sign_coa, vEmptyString, vEmptyAmount,
  308.         vEmptyString, vEmptyString,
  309.         A.beginning_balance, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name,
  310.         A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id, A.main_acc,
  311.         A.sub_acc, A.beginning_balance, A.sign_coa, A.coa_desc, 1, vEmptyString
  312.     FROM data_saldo_awal_with_transaksi A
  313.     ORDER BY A.coa_id;
  314.     */
  315.        
  316.     /* header result*/
  317.     Open pRefHeader FOR
  318.     SELECT pOuBuId AS ou_id, f_get_ou_name(pOuBuId) AS ou_name, pPeriodFrom AS period_from, pPeriodTo AS period_to, f_get_username(pUserId) AS username, pDatetime AS datetime;
  319.     RETURN NEXT pRefHeader;
  320.    
  321.     Open pRefDetail FOR
  322.     SELECT A.doc_type_id, A.doc_type_desc, A.doc_no, A.doc_date, A.sign_journal, A.curr_code_trx, SUM(A.amount_trx) AS amount_trx, A.journal_date,
  323.             A.numerator_rate, A.denominator_rate, A.sign_coa, A.gl_curr_code, SUM(A.gl_amount) AS gl_amount, A.ou_branch_id, A.branch_name,
  324.             A.ou_sub_bu_id, A.sub_bu_name, A.ou_rc_id, A.rc_name, A.segment_id, A.segment_name, A.coa_id,
  325.             A.main_acc, A.sub_acc, A.beginning_balance, A.coa_desc, A.remark,
  326.             CASE WHEN A.sign_journal = vSignDebt THEN SUM(A.gl_amount) ELSE 0 END AS gl_amount_debt,
  327.             CASE WHEN A.sign_journal = vSignCredit THEN SUM(A.gl_amount) ELSE 0 END AS gl_amount_credit,
  328.             (A.curr_code_trx || to_char(A.denominator_rate, '999,999.90') || ' = ' || A.gl_curr_code || to_char(A.numerator_rate, '999,999.90')) AS rate
  329.     FROM tr_gl_detail_ledger_card A
  330.     WHERE A.session_id = pSessionId
  331.         AND A.tenant_id = pTenantId
  332.     GROUP BY A.doc_type_id, A.doc_type_desc, A.doc_no, A.doc_date, A.sign_journal, A.curr_code_trx, A.journal_date, A.numerator_rate,
  333.             A.denominator_rate, A.sign_coa, A.gl_curr_code, A.ou_branch_id, A.branch_name, A.ou_sub_bu_id, A.sub_bu_name, A.ou_rc_id, A.rc_name,
  334.             A.segment_id, A.segment_name, A.coa_id, A.main_acc, A.sub_acc, A.beginning_balance, A.coa_desc, A.remark
  335. --  ORDER BY A.branch_name, A.sub_bu_name, A.main_acc, A.sub_acc, A.rc_name, A.segment_name, A.coa_desc, A.doc_date, A.doc_no, A.curr_code_trx, A.gl_curr_code;
  336.     ORDER BY A.branch_name, A.sub_bu_name, A.coa_id, A.rc_name, A.segment_name, A.doc_date, A.doc_type_desc, A.doc_no, A.sign_journal;
  337.    
  338.     RETURN NEXT pRefDetail ;
  339.    
  340.     DELETE FROM tr_gl_beginning_balance_ledger_card WHERE session_id = pSessionId;
  341.     DELETE FROM tr_gl_detail_ledger_card WHERE session_id = pSessionId;
  342. END;
  343. $BODY$
  344.   LANGUAGE plpgsql VOLATILE
  345.   COST 100
  346.   ROWS 1000;
  347. /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement