abirama62

r_draft_trial_balance

Feb 5th, 2021
982
100 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- author : sts
  2. CREATE OR REPLACE FUNCTION r_draft_trial_balance(character varying, bigint, bigint, character varying, bigint, bigint, bigint, bigint, character varying)
  3.   RETURNS SETOF REFCURSOR AS
  4. $BODY$
  5. DECLARE
  6.     pRefHeader              REFCURSOR := 'refHeader';
  7.     pRefDetail          REFCURSOR := 'refDetail';
  8.  
  9.     pSessionId          ALIAS FOR $1;
  10.     pTenantId           ALIAS FOR $2;
  11.     pUserId         ALIAS FOR $3;
  12.     pDatetime           ALIAS FOR $4;
  13.     pOuId           ALIAS FOR $5;
  14.     pOuBranch           ALIAS FOR $6;
  15.     pOuSubBu            ALIAS FOR $7;
  16.     pGroupCoaId         ALIAS FOR $8;
  17.   pYearMonth            ALIAS FOR $9;
  18.  
  19.     vEmptyId            bigint := -99;
  20.     vAll                character varying := '(All)';
  21.     vNol                numeric := 0;
  22.     vFilterGroupCoa     text := '';
  23.     vFilterYearMonth    text := '';
  24.     vFilterOuSubBuId    text := '';
  25.     vFilterOuBranchId   text := '';
  26.     vEmptyValue         character varying := '';
  27.     vYearMonthNow       character varying(6);
  28.  
  29.     vReportName             character varying := 'Report Draft Trial Balance';
  30. BEGIN
  31.  
  32.     vYearMonthNow := LEFT(pDatetime,6);
  33.  
  34.     IF (pGroupCoaId <> vEmptyId) THEN
  35.         vFilterGroupCoa := ' AND B.group_coa_id = ' || pGroupCoaId;
  36.     END IF;
  37.  
  38.     IF (pOuSubBu <> vEmptyId) THEN
  39.         vFilterOuSubBuId := ' AND A.ou_sub_bu_id = ' || pOuSubBu;
  40.     END IF;
  41.  
  42.     IF (pOuBranch <> vEmptyId) THEN
  43.         vFilterOuBranchId := ' AND A.ou_branch_id = ' || pOuBranch;
  44.     END IF;
  45.  
  46.     PERFORM gl_temp_closing_simulation(pSessionId, pTenantId, pUserId, pDatetime, pYearMonth, pOuId);
  47.  
  48.     Open pRefHeader FOR
  49.         SELECT pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name, pYearMonth AS year_month_periode,
  50.          CASE WHEN pOuSubBu = vEmptyId THEN '(All)'
  51.             ELSE f_get_ou_name(pOuSubBu) END AS ou_sub_bu,
  52.          CASE WHEN pOuBranch = vEmptyId THEN '(All)'
  53.             ELSE f_get_ou_name(pOuBranch) END AS ou_branch,
  54.          pGroupCoaId AS group_coa_id, CASE WHEN pGroupCoaId = vEmptyId THEN '(All)' ELSE f_get_group_coa_name(pGroupCoaId) END AS group_coa_name,
  55.          pDatetime AS print_datetime;
  56.     RETURN NEXT pRefHeader;
  57.  
  58.     Open pRefDetail FOR
  59.         SELECT A.date_year_month, CASE WHEN pGroupCoaId = vEmptyId THEN vAll ELSE f_get_group_coa_name(pGroupCoaId) END AS group_coa_name,
  60.             A.coa_id, A.coa_id, (B.main_acc ||'.' || B.sub_acc) AS coa_acc, B.coa_desc AS coa_desc,
  61.             A.sign_coa, f_get_ou_name(A.ou_rc_id) AS ou_rc_name, f_get_segment_full_name_without_group(A.segmen_id) AS segment_desc,
  62.             SUM(A.beg_balance_1) AS beg_balance_1, SUM(A.mut_debit_1) AS mut_debit_1,
  63.             SUM(A.mut_credit_1) AS mut_credit_1, SUM(A.end_balance_1) AS end_balance_1, SUM(A.mut_debit_2) AS mut_debit_2,
  64.             SUM(A.mut_credit_2) AS mut_credit_2, SUM(A.end_balance_2) AS end_balance_2, SUM(A.mut_debit_3) AS mut_debit_3,
  65.             SUM(A.mut_credit_3) AS mut_credit_3, SUM(A.end_balance_3) AS end_balance_3,
  66.             COALESCE(C.group_segment, vEmptyValue) AS group_segment
  67.         FROM gl_temp_trial_balance A
  68.         INNER JOIN m_coa B ON A.tenant_id = B.tenant_id AND A.coa_id = B.coa_id
  69.         LEFT JOIN m_segment_coa C ON A.segmen_id = C.segment_coa_id
  70.         GROUP BY A.date_year_month, A.coa_id, B.main_acc, B.sub_acc, B.coa_desc, A.sign_coa, A.ou_rc_id, A.segmen_id, C.group_segment;
  71.     RETURN NEXT pRefDetail;
  72.  
  73.     DELETE FROM gl_temp_trial_balance;
  74.  
  75. END;
  76. $BODY$
  77.   LANGUAGE plpgsql VOLATILE
  78.   COST 100
  79.   ROWS 1000;
  80. /
RAW Paste Data