IN CANCEL CLOSING COSTING RAW MATERIAL

CREATE OR REPLACE FUNCTION in_cancel_closing_costing_raw_materials(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE

    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vOuId                   bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vYearMonth              character varying(6);
    vNextYearMonth          character varying(6);
    vGroupProductRM         character varying(5);
    vAdminProcessLedgerId   bigint;
    vLedgerCodeCostRM       character varying(10);
    vDocTypeAwal            bigint;
    vStatusDraft            character varying(1);
    vEmptyId                bigint;

BEGIN

    vLedgerCodeCostRM := 'COST.RM';
    vDocTypeAwal := -99;
    vGroupProductRM := 'RM';
    vStatusDraft := 'D';
    vEmptyId := -99;

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'in_cancel_closing_costing_raw_materials' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vOuId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'ouId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    SELECT CAST(A.process_parameter_value AS character varying(6)) INTO vYearMonth
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'yearmonth';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vAdminProcessLedgerId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'adminProcessLedgerId';

    SELECT TO_CHAR(TO_DATE(vYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;

    /*
     * Hapus harga dasar pokok di bulan ybs
     */
    DELETE FROM in_summary_monthly_cogs A
    WHERE A.date_year_month = vYearMonth AND
        A.ou_id = vOuId AND
        A.remark = vGroupProductRM;

    /*
     * Hapus data saldo akhir tahun bulan ybs, kecuali saldo awal
     */
    DELETE FROM in_summary_monthly_amount A
    WHERE A.date_year_month = vYearMonth AND
        A.ou_bu_id = vOuId AND
        A.doc_type_id <> vDocTypeAwal AND
        A.remark = vGroupProductRM;

    /*
     * delete data in_summary_monthly_zero_qty_amount
     */
    DELETE FROM in_summary_monthly_zero_qty_amount A
    WHERE A.date_year_month = vYearMonth AND
        A.ou_bu_id = vOuId AND
        A.doc_type_id <> vDocTypeAwal AND
        A.remark = vGroupProductRM;

    /*
     * Hapus data saldo awal tahun bulan berikutnya
     */
    DELETE FROM in_summary_monthly_amount A
    WHERE A.date_year_month = vNextYearMonth AND
        A.ou_bu_id = vOuId AND
        A.remark = vGroupProductRM;

    UPDATE m_admin_process_ledger SET status_ledger = 0, flag_process = 'N',
        process_no = pProcessNo, process_datetime = vDatetime, process_user_id = vUserId
    WHERE tenant_id = pTenantId AND
        ou_id = vOuId AND
        date_year_month = vYearMonth AND
        ledger_code = vLedgerCodeCostRM;

    /* Adrian, Feb 27, 2017
     * update status journal menjadi D
     */
    DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;

    INSERT INTO tt_gl_journal_costing
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
    qty, uom_id, amount, total_amount_product, total_qty_product)
    SELECT pSessionId, A.tenant_id, A.ou_bu_id,
        CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
        CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
        A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
        B.qty, B.uom_id, 0, 0, 0
    FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_raw_materials_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_bu_id = vOuId AND
        A.doc_type_id IN (502, 521, 413, 511, 311, 154, 354, 431, 526, 537) AND
        A.doc_date = C.string_date AND
        C.year_month_date = vYearMonth AND
        A.journal_trx_id = B.journal_trx_id AND
        B.product_id <> vEmptyId AND
        B.product_id = D.product_id;

    UPDATE gl_journal_trx SET status_doc = vStatusDraft, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    FROM (SELECT journal_trx_id
            FROM tt_gl_journal_costing A
            WHERE A.session_id = pSessionId
            GROUP BY journal_trx_id) A
    WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;

    -- update status document khusus untuk document cost allocation to product (528), dan adjustment stok amount (522)
    UPDATE gl_journal_trx SET status_doc = vStatusDraft, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE doc_type_id IN (528,522)
    AND SUBSTRING(doc_date, 1, 6) = vYearMonth;

    DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;

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