abirama62

gl_temp_closing_simulation

Feb 4th, 2021 (edited)
831
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- author : sts
  2. CREATE OR REPLACE FUNCTION gl_temp_closing_simulation(character varying, bigint, bigint, character varying, character varying, bigint)
  3.   RETURNS void AS
  4. $BODY$
  5. DECLARE
  6.  
  7.     pSessionId          ALIAS FOR $1;
  8.     pTenantId           ALIAS FOR $2;
  9.     pUserId         ALIAS FOR $3;
  10.     pDatetime           ALIAS FOR $4;
  11.   pYearMonth    ALIAS FOR $5;
  12.     pOuId           ALIAS FOR $6;
  13.  
  14.   vLastYearMonth character varying;
  15.   vLastYearMonthPlus character varying;
  16.  
  17.   vComboLedger  character varying := 'LEDGER';
  18.   vFlowPostingDailyJournal character varying := 'gl_temp_posting_daily_journal_with_var';
  19.  
  20. BEGIN
  21.   DELETE FROM tt_gl_temp_closing_simulation WHERE session_id = pSessionId;
  22.  
  23.   -- cari tutup bulan terakhir
  24.     SELECT MAX(date_year_month) INTO vLastYearMonth
  25.     FROM m_admin_process_ledger
  26.     WHERE ou_id = pOuId
  27.         AND ledger_code = 'GL'
  28.         AND status_ledger = '1';
  29.  
  30.   SELECT TO_CHAR(TO_DATE(vLastYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vLastYearMonthPlus;
  31.  
  32.   --cek apakah vLastYearMonth + 1 month = pYearMonth
  33.   IF vLastYearMonthPlus <> pYearMonth THEN
  34.     RAISE EXCEPTION 'bulan yang dipilih bukan bulan terakhir tutup bulan + 1 bulan';
  35.   ELSE
  36.     RAISE NOTICE 'Simulasi Closing bisa dilakukan';
  37.   END IF;
  38.  
  39.   -- get ledger code insert ke tt_gl_temp_closing_simulation
  40.   INSERT INTO tt_gl_temp_closing_simulation
  41.   (session_id, admin_process_ledger_id, tenant_id, ou_id,
  42.   date_year_month, ledger_code, status_ledger, flg_process,
  43.   process_no, last_processed_year_month,
  44.   flow_type)
  45.   SELECT pSessionId, e.admin_process_ledger_id, e.tenant_id, e.ou_id,
  46.     e.date_year_month, e.ledger_code, e.status_ledger, e.flag_process,
  47.     e.process_no, f_get_last_processed_year_month_admin_process_ledger(e.tenant_id, e.ou_id, e.ledger_code),
  48.     vFlowPostingDailyJournal AS flow_type
  49.   FROM m_admin_process_ledger e
  50.   INNER JOIN t_combo_value f ON f.combo_id = vComboLedger AND f.code = e.ledger_code
  51.   WHERE e.tenant_id = pTenantId
  52.   AND e.ou_id = pOuId
  53.   AND e.date_year_month = vLastYearMonthPlus
  54.   ORDER BY f.sort_no, f.code;
  55.  
  56.  
  57.   --2. execute gl_temp_posting_daily_journal
  58.   PERFORM gl_temp_posting_daily_journal_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, A.ledger_code, pYearMonth)
  59.   FROM tt_gl_temp_closing_simulation A
  60.   WHERE A.flow_type = vFlowPostingDailyJournal
  61.   AND A.session_id = pSessionId;
  62.  
  63.   --3. execute in_temp_closing_costing_raw_materials
  64.   PERFORM in_temp_closing_costing_raw_materials_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);
  65.  
  66.   --4 execute in_temp_closing_wip
  67.   PERFORM in_temp_closing_costing_wip_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);
  68.  
  69.   --5. execute in_temp_closing_costing_finish_goods
  70.   PERFORM in_temp_closing_costing_finish_goods_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);
  71.  
  72.   --6. execute gl_temp_posting_monthly_journal
  73.   PERFORM gl_temp_posting_monthly_journal_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);
  74.  
  75.   --7. execute gl_temp_closing_ledger (tanpa process forex ap, forex ar, dan forex cb)
  76.   PERFORM gl_temp_closing_ledger_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth, 1);
  77.  
  78. END;
  79. $BODY$
  80.   LANGUAGE plpgsql VOLATILE
  81.   COST 100;
  82. /
RAW Paste Data