gl_temp_closing_simulation

-- author : sts
CREATE OR REPLACE FUNCTION gl_temp_closing_simulation(character varying, bigint, bigint, character varying, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE

    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pUserId         ALIAS FOR $3;
    pDatetime           ALIAS FOR $4;
  pYearMonth    ALIAS FOR $5;
    pOuId           ALIAS FOR $6;

  vLastYearMonth character varying;
  vLastYearMonthPlus character varying;

  vComboLedger  character varying := 'LEDGER';
  vFlowPostingDailyJournal character varying := 'gl_temp_posting_daily_journal_with_var';

BEGIN
  DELETE FROM tt_gl_temp_closing_simulation WHERE session_id = pSessionId;

  -- cari tutup bulan terakhir
    SELECT MAX(date_year_month) INTO vLastYearMonth
    FROM m_admin_process_ledger
    WHERE ou_id = pOuId
        AND ledger_code = 'GL'
        AND status_ledger = '1';

  SELECT TO_CHAR(TO_DATE(vLastYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vLastYearMonthPlus;

  --cek apakah vLastYearMonth + 1 month = pYearMonth
  IF vLastYearMonthPlus <> pYearMonth THEN
    RAISE EXCEPTION 'bulan yang dipilih bukan bulan terakhir tutup bulan + 1 bulan';
  ELSE
    RAISE NOTICE 'Simulasi Closing bisa dilakukan';
  END IF;

  -- get ledger code insert ke tt_gl_temp_closing_simulation
  INSERT INTO tt_gl_temp_closing_simulation
  (session_id, admin_process_ledger_id, tenant_id, ou_id,
  date_year_month, ledger_code, status_ledger, flg_process,
  process_no, last_processed_year_month,
  flow_type)
  SELECT pSessionId, e.admin_process_ledger_id, e.tenant_id, e.ou_id,
    e.date_year_month, e.ledger_code, e.status_ledger, e.flag_process,
    e.process_no, f_get_last_processed_year_month_admin_process_ledger(e.tenant_id, e.ou_id, e.ledger_code),
    vFlowPostingDailyJournal AS flow_type
  FROM m_admin_process_ledger e
  INNER JOIN t_combo_value f ON f.combo_id = vComboLedger AND f.code = e.ledger_code
  WHERE e.tenant_id = pTenantId
  AND e.ou_id = pOuId
  AND e.date_year_month = vLastYearMonthPlus
  ORDER BY f.sort_no, f.code;


  --2. execute gl_temp_posting_daily_journal
  PERFORM gl_temp_posting_daily_journal_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, A.ledger_code, pYearMonth)
  FROM tt_gl_temp_closing_simulation A
  WHERE A.flow_type = vFlowPostingDailyJournal
  AND A.session_id = pSessionId;

  --3. execute in_temp_closing_costing_raw_materials
  PERFORM in_temp_closing_costing_raw_materials_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);

  --4 execute in_temp_closing_wip
  PERFORM in_temp_closing_costing_wip_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);

  --5. execute in_temp_closing_costing_finish_goods
  PERFORM in_temp_closing_costing_finish_goods_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);

  --6. execute gl_temp_posting_monthly_journal
  PERFORM gl_temp_posting_monthly_journal_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth);

  --7. execute gl_temp_closing_ledger (tanpa process forex ap, forex ar, dan forex cb)
  PERFORM gl_temp_closing_ledger_with_var(pTenantId, pSessionId, pOuId, pUserId, pDatetime, pYearMonth, 1);

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/