in_process_costing_finish_goods

-- Function: in_process_costing_finish_goods(bigint, character varying, character varying, bigint, character varying, bigint)

-- DROP FUNCTION in_process_costing_finish_goods(bigint, character varying, character varying, bigint, character varying, bigint);

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

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

    vNextYearMonth          character varying(6);
    vCurrGL                 character varying(5);
    vFlagNo                 character varying(1);
    vFlagYes                character varying(1);
    vEmptyValue             character varying(1);
    vEmptyId                bigint;
    vDocTypeAwal            bigint;
    vGroupProductFG         character varying(5);

    vRoundingDigit          integer;
    vTypeRate               character varying(5);
    vStatusRelease          character varying(1);
BEGIN

    vFlagNo := 'N';
    vFlagYes := 'Y';
    vDocTypeAwal := -99;
    vEmptyId := -99;
    vEmptyValue := ' ';
    vGroupProductFG := 'FG';

    vTypeRate := 'COM';
    vStatusRelease := 'R';

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

    vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
    vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);

    DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;

    DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;

    DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_assembly_cogs WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_product_assembly WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_amount_remain_assembly WHERE session_id = pSessionId;

    /*
     * costing product hanya berdasarkan ou business unit, jadi untuk sementara sub_ou_id diisi dengan -99
     */

    /*
     * membuat summary nilai pembelian berdasarkan hasil jurnal
     * untuk transaksi receive goods
     * NK, 16 Feb 2015 : Pembelian Internal menghasilkan dokumen semua sesuai doc type Receive Goods
     * Receive Goods : 111
     */
    INSERT INTO in_summary_monthly_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
    SELECT pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, C.product_id, C.uom_id, SUM(C.qty),
        C.gl_curr_code, SUM(C.gl_amount), 'PURCH_PRICE',
        0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
    FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_finish_goods_product E
    WHERE A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.status_doc = vStatusRelease AND
        A.doc_date = B.string_date AND
        B.year_month_date = pYearMonth AND
        A.journal_trx_id = C.journal_trx_id AND
        A.doc_type_id = D.doc_type_id AND
        D.ledger_code IN ('PURCH') AND
        C.journal_desc = 'PRODUCT_STOCK' AND
        A.doc_type_id IN (111) AND
        C.product_id = E.product_id
    GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, C.product_id, C.uom_id, C.gl_curr_code;

    /*
     * ambil daftar product yang akan diproses,hny yang group FINISH GOODS
     * dan berikan tanda jika barang tersebut adalah barang assembly
     * NOT USED ANYMORE, NK 31 DES 2013, USE VIEW
    INSERT INTO tt_in_product
    (session_id, product_id, base_uom_id, flg_assembly)
    SELECT pSessionId, A.product_id, A.base_uom_id, vFlagNo
    FROM m_product A, m_ctgr_product B, m_group_product C
    WHERE A.ctgr_product_id = B.ctgr_product_id AND
        B.group_product_id = C.group_product_id AND
        C.group_product_code = vGroupProductFG;

    UPDATE tt_in_product SET flg_assembly = vFlagYes
    WHERE EXISTS (SELECT 1 FROM m_ext_product A
                WHERE tt_in_product.product_id = A.product_id);
     */
    /*
     * ambil data receive goods dari in_summary_monthly_amount yang product Finish Goods
     * NK, 16 Feb 2015 : receive goods internal menggunakan doc type receive goods
     */
    INSERT INTO tt_in_doc_product_price
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
     doc_type_id, ref_id, doc_no, doc_date,
     curr_code, amount, qty, uom_id,
     numerator_rate, denominator_rate,
     gl_curr_code, gl_amount)
    SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.product_id,
        A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
        A.gl_curr_code, A.gl_amount, A.qty, A.base_uom_id,
        1, 1,
        A.gl_curr_code, A.gl_amount
    FROM in_summary_monthly_amount A, vw_finish_goods_product E
    WHERE A.date_year_month = pYearMonth AND
        A.doc_type_id IN (111) AND
        A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.product_id = E.product_id;

    /*
     * ambil data adjusment stok amount
     * jika curr <> curr G/L, maka lakukan hitung rate untuk ke nilai sesuai valuta G/L
     * 522 : adj stock amount ( mempengaruhi COGS )
     * 523 : adj stock amount balance ( mempengaruhi saldo akhir )
     * 528 : costing allocation to product ( mempengaruhi COGS )
     */
    INSERT INTO tt_in_doc_product_price
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
     doc_type_id, ref_id, doc_no, doc_date,
     curr_code, amount, qty, uom_id,
     numerator_rate, denominator_rate,
     gl_curr_code, gl_amount)
    SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
        A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.curr_code, A.amount, A.qty, A.uom_id,
        1, 1, vCurrGL, ROUND( A.amount, vRoundingDigit)
    FROM in_product_price_balance A, m_ou_structure B, dt_date C, vw_finish_goods_product E
    WHERE A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.tenant_id = pTenantId AND
        A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_type_id IN (522, 523, 528) AND
        A.product_id = E.product_id;

    -- hitung nilai pembukuan untuk transaksi adjusment stok amount
    UPDATE tt_in_doc_product_price SET numerator_rate = B.amount_to, denominator_rate = B.amount_from
    FROM m_exchange_rate B
    WHERE tt_in_doc_product_price.session_id = pSessionId AND
        tt_in_doc_product_price.tenant_id = B.tenant_id AND
        B.type_exchange_rate = vTypeRate AND
        tt_in_doc_product_price.doc_date = B.date_from AND
        tt_in_doc_product_price.curr_code = B.curr_code_from AND
        tt_in_doc_product_price.gl_curr_code = B.curr_code_to AND
        tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
        tt_in_doc_product_price.curr_code <> vCurrGL;

    UPDATE tt_in_doc_product_price SET gl_amount = ROUND(amount * numerator_rate / denominator_rate, vRoundingDigit)
    WHERE tt_in_doc_product_price.session_id = pSessionId AND
          tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
          tt_in_doc_product_price.curr_code <> vCurrGL;

    /*
     * product non assembly
     * ambil data transaksi yg tambah stok, tp belum memiliki nilai
     * 1.return note : 502
     * 2.adj stok qty plus : 521
     * 3.adj stok outlet qty plus : 413
     */
    INSERT INTO tt_in_add_product_unamount
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
     doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
        A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id = 502 AND
        A.product_id = E.product_id;

    INSERT INTO tt_in_add_product_unamount
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
     doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
        A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN ( 521, 413 ) AND
        A.product_id = E.product_id AND
        A.qty > 0;

/* NK, 25 Jan 2014, di submit adj stok qty diubah sehingga log product balance stock akan ada 2 kelompok,
 * kelompok yang qty > 0 dan qty < 0

    INSERT INTO tt_in_add_product_unamount
    (session_id, tenant_id, ou_id, sub_ou_id, product_id,
     doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
        A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
    FROM in_inventory A, in_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product E
    WHERE A.tenant_id = pTenantId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.inventory_id = B.inventory_id AND
        A.ou_from_id = D.ou_id AND
        D.ou_bu_id = pOuId AND
        A.doc_type_id = 521 AND
        B.qty_realization > 0 AND
        B.product_id = E.product_id;

    INSERT INTO tt_in_add_product_unamount
    (session_id, tenant_id, ou_id, sub_ou_id, product_id,
     doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
        A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
    FROM i_trx_inventory A, i_trx_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product E
    WHERE A.tenant_id = pTenantId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.trx_inventory_id = B.trx_inventory_id AND
        A.ou_id = D.ou_id AND
        D.ou_bu_id = pOuId AND
        A.doc_type_id = 413 AND
        B.qty_realization > 0 AND
        B.product_id = E.product_id;
 */
    /*
     * buat data yang akan menjadi acuan harga standard
     * 1.data purch price di bulan bersangkutan
     * 2.data nilai barang di awal bulan
     * 3.data master harga standard
     */
    INSERT INTO tt_in_product_base_price
    (session_id, tenant_id, ou_id, product_id,
     flg_amount, qty, uom_id, gl_curr_code, gl_amount)
    SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
        'PURCH_PRICE', SUM(A.qty), A.uom_id, A.gl_curr_code, SUM(A.gl_amount)
    FROM tt_in_doc_product_price A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id IN ( 111)
    GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
        A.uom_id, A.gl_curr_code;

    INSERT INTO tt_in_product_base_price
    (session_id, tenant_id, ou_id, product_id,
     flg_amount, qty, uom_id, gl_curr_code, gl_amount)
    SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.product_id,
        'BGN_BALANCE', SUM(A.qty), A.base_uom_id, A.gl_curr_code, SUM(A.gl_amount)
    FROM in_summary_monthly_amount A, vw_finish_goods_product E
    WHERE A.date_year_month = pYearMonth AND
        A.doc_type_id = vDocTypeAwal AND
        A.tenant_id = pTenantId AND
        A.ou_bu_id = pOuId AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, A.ou_bu_id, A.product_id,
        A.base_uom_id, A.gl_curr_code;

    INSERT INTO tt_in_product_base_price
    (session_id, tenant_id, ou_id, product_id,
     flg_amount, qty, uom_id, gl_curr_code, gl_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.product_id,
        'STANDARD_PRICE', 0, E.base_uom_id, A.curr_code, SUM(A.amount)
    FROM in_product_standard_cogs A, vw_finish_goods_product E
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.year_month_date = pYearMonth AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, A.ou_id, A.product_id, A.curr_code, E.base_uom_id;

    /*
     * isi nilai data tt_in_add_product_unamount dengan urutan :
     * 1. update dengan data flg amount STANDARD_PRICE
     * 2. jika belum ada, update dengan data flg amount BGN_BALANCE
     * 3. jika belum ada, update dengan data flg amount PURCH_PRICE
     */

    UPDATE tt_in_add_product_unamount SET gl_amount = tt_in_add_product_unamount.qty * A.gl_amount, flg_amount = A.flg_amount
    FROM tt_in_product_base_price A
    WHERE A.session_id = pSessionId AND
        tt_in_add_product_unamount.session_id = A.session_id AND
        tt_in_add_product_unamount.tenant_id = A.tenant_id AND
        tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
        tt_in_add_product_unamount.product_id = A.product_id AND
        tt_in_add_product_unamount.flg_amount = vEmptyValue AND
        A.flg_amount = 'STANDARD_PRICE';

    UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
    FROM tt_in_product_base_price A
    WHERE A.session_id = pSessionId AND
        tt_in_add_product_unamount.session_id = A.session_id AND
        tt_in_add_product_unamount.tenant_id = A.tenant_id AND
        tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
        tt_in_add_product_unamount.product_id = A.product_id AND
        tt_in_add_product_unamount.flg_amount = vEmptyValue AND
        A.flg_amount = 'BGN_BALANCE';

    UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
    FROM tt_in_product_base_price A
    WHERE A.session_id = pSessionId AND
        tt_in_add_product_unamount.session_id = A.session_id AND
        tt_in_add_product_unamount.tenant_id = A.tenant_id AND
        tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
        tt_in_add_product_unamount.product_id = A.product_id AND
        tt_in_add_product_unamount.flg_amount = vEmptyValue AND
        A.flg_amount = 'PURCH_PRICE';


/*
 *  menghitung harga rata rata product (non assembly ) berdasarkan :
 *  1. harga purchasing
 *  2. harga adj stock amount ( bukan yg balance amount ), cost allocation product
 *  3. transaksi tambah stok qty, dengan harga sudah diproses : retur jual, adj stok qty > 0
 *  4. harga saldo awal
 */
    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
        A.gl_curr_code, SUM(A.gl_amount), 'PURCH_PRICE'
    FROM tt_in_doc_product_price A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id IN (111)
    GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;

    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
        A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
    FROM tt_in_doc_product_price A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id IN ( 522, 528 )
    GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;

    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
        A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
    FROM tt_in_add_product_unamount A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code, A.flg_amount;

    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
        A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
    FROM in_summary_monthly_amount A, vw_finish_goods_product E
    WHERE A.date_year_month = pYearMonth AND
        A.doc_type_id = vDocTypeAwal AND
        A.ou_bu_id = pOuId AND
        A.tenant_id = pTenantId AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id, A.gl_curr_code, A.flg_amount;

    /*
     * hitung harga rata-rata sebagai dasar harga pokok
     */
    INSERT INTO tt_in_summary_monthly_cogs
    (session_id, date_year_month, tenant_id, ou_id,
    product_id, qty_total, curr_code, amount_total, avg_price)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
        A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), 0
    FROM tt_in_summary_monthly_product A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;

    UPDATE tt_in_summary_monthly_cogs
      SET avg_price = amount_total / qty_total
    WHERE session_id = pSessionId AND
      qty_total <> 0;

    /*
     * memberikan nilai untuk transaksi stok yang mengurangi qty, sesuai harga rata rata bulan ini
     * transaksi selain adj stok qty dan adj stok qty outlet , yang qty < 0
     * transaksi pemakaian Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop
     * transaksi pemakaian Nota klaim (511), DO (311) , POS Shop (431), Pos Shop In Shop(431)
     * NK, 16 Feb 2015 : tambahan transaksi DO Receipt ( 526 )
     * Sales Invoice Konsinyasi(354), DO Internal tidak jadi dipakai(154)
     * Return POS Shop, Return POS SHop In SHop belum ada

     */
    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN (511,311,431,526) AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;

    -- khusus untuk DO POS, ambil dari in_log yg doc nya POS
    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        431, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN (401,402,403,404,405,406) AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id, A.base_uom_id
    HAVING SUM(A.qty) <> 0;


    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN (521, 413) AND
        A.product_id = E.product_id AND
        A.qty < 0
    GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;

/*  NK, 25 Jan 2014, submit adj stok qty diubah sehingga menghasilkan 2 jenis log data product balance stock
 *  qty < 0 dan qty > 0 dipisahkan
    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_id, sub_ou_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_id,
        A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_finish_goods_product E
    WHERE A.ou_from_id = C.ou_id AND
        C.ou_bu_id = pOuId AND
        A.inventory_id = B.inventory_id AND
        A.doc_date = D.string_date AND
        D.date_year_month = pYearMonth AND
        A.doc_type_id IN (521, 413) AND
        B.qty_realization < 0 AND
        B.product_id = E.product_id
    GROUP BY A.tenant_id, C.ou_bu_id, C.ou_id, A.doc_type_id, B.product_id, B.base_uom_id;
*/
    UPDATE tt_in_summary_monthly_product SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
    FROM tt_in_summary_monthly_cogs A
    WHERE A.session_id = pSessionId AND
        tt_in_summary_monthly_product.session_id = A.session_id AND
        tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
        tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
        tt_in_summary_monthly_product.product_id = A.product_id AND
        tt_in_summary_monthly_product.doc_type_id IN (511,311,431,526,521,413) AND
        tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
        A.qty_total <> 0;

    /*
     * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
     * 1. ambil data adj stok balance amount ( adjust nilai akhir saldo barang )
     * 2. hitung saldo akhir
     */
    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
        A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
    FROM tt_in_doc_product_price A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id = 523
    GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;

    /*
     * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
     * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
     */
    INSERT INTO tt_in_summary_monthly_amount_remain
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount, summary_monthly_product_id)
    SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
        vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
    FROM tt_in_summary_monthly_product A
    WHERE A.session_id = pSessionId
    GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;

    -- Cari id data sebagai penampung alokasi nilai
    UPDATE tt_in_summary_monthly_amount_remain
        SET summary_monthly_product_id = (
            SELECT MAX(A.summary_monthly_product_id)
            FROM tt_in_summary_monthly_product A
            WHERE A.session_id = pSessionId AND
                tt_in_summary_monthly_amount_remain.session_id = A.session_id AND
                tt_in_summary_monthly_amount_remain.date_year_month = A.date_year_month AND
                tt_in_summary_monthly_amount_remain.tenant_id = A.tenant_id AND
                tt_in_summary_monthly_amount_remain.ou_bu_id = A.ou_bu_id AND
                tt_in_summary_monthly_amount_remain.product_id = A.product_id AND
                A.doc_type_id IN (511,311,431,526,521,413) AND
                A.flg_amount = 'MONTHLY_AVG')
    WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;

    /*
     * NK, 16 Feb 2015
     * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
     * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
     */
    INSERT INTO in_summary_monthly_zero_qty_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
    doc_type_id, qty, gl_amount, remark)
    SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
        B.doc_type_id, B.qty, B.gl_amount, vGroupProductFG
    FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
    WHERE A.session_id = pSessionId AND
        A.summary_monthly_product_id <> vEmptyId AND
        B.summary_monthly_product_id = A.summary_monthly_product_id;

    UPDATE tt_in_summary_monthly_product SET gl_amount = tt_in_summary_monthly_product.gl_amount - A.gl_amount
    FROM tt_in_summary_monthly_amount_remain A
    WHERE A.session_id = pSessionId AND
        A.summary_monthly_product_id <> vEmptyId AND
        tt_in_summary_monthly_product.summary_monthly_product_id = A.summary_monthly_product_id;

    INSERT INTO tt_in_summary_monthly_product
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
    SELECT A.session_id, vNextYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
    FROM tt_in_summary_monthly_amount_remain A
    WHERE A.session_id = pSessionId AND A.qty > 0;

    INSERT INTO in_summary_monthly_cogs
    (date_year_month, tenant_id, ou_id, product_id,
    qty_total, curr_code, amount_total, avg_price,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
    SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
            A.qty_total, A.curr_code, A.amount_total, A.avg_price,
            0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
    FROM tt_in_summary_monthly_cogs A
    WHERE A.session_id = pSessionId;

    INSERT INTO in_summary_monthly_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
    SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
        A.gl_curr_code, A.gl_amount, A.flg_amount,
        0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
    FROM tt_in_summary_monthly_product A
    WHERE A.session_id = pSessionId AND
            A.date_year_month = pYearMonth AND
            A.doc_type_id NOT IN (vDocTypeAwal, 111);

    INSERT INTO in_summary_monthly_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
    SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
        A.gl_curr_code, A.gl_amount, A.flg_amount,
        0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
    FROM tt_in_summary_monthly_product A
    WHERE A.session_id = pSessionId AND
        A.date_year_month = vNextYearMonth;

/*
 * PERHITUNGAN PRODUCT ASSEMBLY
 * 1. tentukan harga awal transaksi assembly : 541
 * 2. tentukan harga transaksi return, adj
 * 3. ambil nilai saldo awal product assembly
 * 4. tentukan harga rata assembly
 * 5. tentukan nilai transaksi pemakaian assembly
 */
    INSERT INTO tt_in_doc_product_assembly_price
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    ref_id, doc_type_id, child_product_id, child_qty, child_amount)
    SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        A.log_product_balance_stock_id, A.doc_type_id,
        E.child_product_id, E.qty_base_uom, 0
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly D, m_product_assembly E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.product_id = D.product_id AND
        D.product_id = E.parent_product_id AND
        A.doc_type_id IN (541);

    /*
     * memberikan nilai untuk child product, krn child product pasti bukan product assembly
     */
    UPDATE tt_in_doc_product_assembly_price SET child_amount = ROUND(child_qty * (A.amount_total / A.qty_total), vRoundingDigit)
    FROM tt_in_summary_monthly_cogs A
    WHERE A.session_id = pSessionId AND
        tt_in_doc_product_assembly_price.session_id = A.session_id AND
        tt_in_doc_product_assembly_price.tenant_id = A.tenant_id AND
        tt_in_doc_product_assembly_price.ou_bu_id = A.ou_id AND
        tt_in_doc_product_assembly_price.child_product_id = A.product_id;

    /*
     * hitung nilai assembly product, sum dari amount child product
     */
    INSERT INTO tt_in_product_assembly_price_balance
    (session_id, tenant_id, ou_id, product_id, product_balance_id,
    product_buy_date, partner_id, doc_type_id, ref_id,
    doc_no, doc_date, curr_code, amount, qty, uom_id)
    SELECT pSessionId, B.tenant_id, pOuId, B.product_id, B.product_balance_id,
        B.doc_date, B.partner_id, B.doc_type_id, B.ref_id,
        B.doc_no, B.doc_date, vCurrGL, SUM(A.total_child_amount * B.qty), SUM(B.qty), B.base_uom_id
    FROM (SELECT A.ref_id, SUM(A.child_amount) AS total_child_amount
            FROM tt_in_doc_product_assembly_price A
            WHERE A.session_id = pSessionId
            GROUP BY A.ref_id) A,
            in_log_product_balance_stock B
    WHERE A.ref_id = B.log_product_balance_stock_id
    GROUP BY B.tenant_id, B.product_id, B.product_balance_id, B.doc_date, B.partner_id, B.doc_type_id, B.ref_id, B.doc_no, B.doc_date, B.base_uom_id;

    /*
     * product assembly,harus cari child product nya
     * ambil data transaksi yg tambah stok, tp belum memiliki nilai
     * 1.return note : 502
     * 2.adj stok qty plus : 521
     */
    INSERT INTO tt_in_add_product_assembly_unamount
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    ref_id, doc_type_id, flg_amount,
     child_product_id, child_qty, child_amount)
    SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_branch_id, D.ou_sub_bu_id,
        A.log_product_balance_stock_id, A.doc_type_id, vEmptyValue,
        F.child_product_id, F.qty_base_uom, 0
    FROM in_log_product_balance_stock A, dt_date C, m_ou_structure D, vw_finish_goods_product_assembly E, m_product_assembly F
    WHERE A.tenant_id = pTenantId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.ou_id = D.ou_id AND
        D.ou_bu_id = pOuId AND
        A.doc_type_id = 502 AND
        A.product_id = E.product_id AND
        E.product_id = F.parent_product_id;

    INSERT INTO tt_in_add_product_assembly_unamount
    (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    ref_id, doc_type_id, flg_amount,
     child_product_id, child_qty, child_amount)
    SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_branch_id, D.ou_sub_bu_id,
        A.log_product_balance_stock_id, A.doc_type_id, vEmptyValue,
        F.child_product_id, F.qty_base_uom, 0
    FROM in_log_product_balance_stock A, dt_date C, m_ou_structure D, vw_finish_goods_product_assembly E, m_product_assembly F
    WHERE A.tenant_id = pTenantId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.ou_id = D.ou_id AND
        D.ou_bu_id = pOuId AND
        A.doc_type_id = 521 AND
        A.product_id = E.product_id AND
        E.product_id = F.parent_product_id AND
        A.qty > 0;

/*  NK, 26 Jan 2014, submit adj stok akan menghasilkan 2 jenis log data
 *  qty > 0 dan qty < 0 akan dipisahkan dalam row data terpisah
    INSERT INTO tt_in_add_product_assembly_unamount
    (session_id, tenant_id, ou_id, sub_ou_id,
    ref_id, doc_type_id, flg_amount,
     child_product_id, child_qty, child_amount)
    SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id,
        B.inventory_item_id, A.doc_type_id, vEmptyValue,
        F.child_product_id, F.qty_base_uom, 0
    FROM in_inventory A, in_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product_assembly E, m_product_assembly F
    WHERE A.tenant_id = pTenantId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.inventory_id = B.inventory_id AND
        A.ou_from_id = D.ou_id AND
        D.ou_bu_id = pOuId AND
        A.doc_type_id = 521 AND
        B.product_id = E.product_id AND
        E.product_id = F.parent_product_id AND
        B.qty_realization > 0;
*/

    /*
     * isi nilai data tt_in_add_product_assembly_unamount untuk nilai child_amount dengan urutan :
     * 1. update dengan data flg amount STANDARD_PRICE
     * 2. jika belum ada, update dengan data flg amount BGN_BALANCE
     * 3. jika belum ada, update dengan data flg amount PURCH_PRICE
     */

    UPDATE tt_in_add_product_assembly_unamount SET child_amount = child_qty * A.gl_amount, flg_amount = A.flg_amount
    FROM tt_in_product_base_price A
    WHERE A.session_id = pSessionId AND
        tt_in_add_product_assembly_unamount.session_id = A.session_id AND
        tt_in_add_product_assembly_unamount.tenant_id = A.tenant_id AND
        tt_in_add_product_assembly_unamount.ou_bu_id = A.ou_id AND
        tt_in_add_product_assembly_unamount.child_product_id = A.product_id AND
        tt_in_add_product_assembly_unamount.flg_amount = vEmptyValue AND
        A.flg_amount = 'STANDARD_PRICE';

    UPDATE tt_in_add_product_assembly_unamount SET child_amount = ROUND(child_qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
    FROM tt_in_product_base_price A
    WHERE A.session_id = pSessionId AND
        tt_in_add_product_assembly_unamount.session_id = A.session_id AND
        tt_in_add_product_assembly_unamount.tenant_id = A.tenant_id AND
        tt_in_add_product_assembly_unamount.ou_bu_id = A.ou_id AND
        tt_in_add_product_assembly_unamount.child_product_id = A.product_id AND
        tt_in_add_product_assembly_unamount.flg_amount = vEmptyValue AND
        A.flg_amount = 'BGN_BALANCE';

    UPDATE tt_in_add_product_assembly_unamount SET child_amount = ROUND(child_qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
    FROM tt_in_product_base_price A
    WHERE A.session_id = pSessionId AND
        tt_in_add_product_assembly_unamount.session_id = A.session_id AND
        tt_in_add_product_assembly_unamount.tenant_id = A.tenant_id AND
        tt_in_add_product_assembly_unamount.ou_bu_id = A.ou_id AND
        tt_in_add_product_assembly_unamount.child_product_id = A.product_id AND
        tt_in_add_product_assembly_unamount.flg_amount = vEmptyValue AND
        A.flg_amount = 'PURCH_PRICE';

/*
 * buat data summary harga product assembly :
 * 1. assembly
 * 2. add product assembly without amount
 *    a.return note
 *    b.adj stock qty plus
 */
    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, pTenantId, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        B.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty),
        vCurrGL, SUM(A.total_child_amount * B.qty), 'ASSEMBLY'
    FROM (SELECT A.ref_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, SUM(A.child_amount) AS total_child_amount
            FROM tt_in_doc_product_assembly_price A
            WHERE A.session_id = pSessionId
            GROUP BY A.ref_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id) A,
        in_log_product_balance_stock B
    WHERE A.ref_id = B.log_product_balance_stock_id
    GROUP BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, B.doc_type_id, B.product_id, B.base_uom_id;

    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, pTenantId,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        B.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty),
        vCurrGL, SUM(A.total_child_amount * B.qty), 'ASSEMBLY'
    FROM (SELECT A.ref_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, SUM(A.child_amount) AS total_child_amount
            FROM tt_in_add_product_assembly_unamount A
            WHERE A.session_id = pSessionId AND
                A.doc_type_id = 502
            GROUP BY A.ref_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id) A,
            in_log_product_balance_stock B
    WHERE A.ref_id = B.log_product_balance_stock_id
    GROUP BY  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, B.doc_type_id, B.product_id, B.base_uom_id;

    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, pTenantId,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
        vCurrGL, SUM(A.total_child_amount * B.qty_realization), 'ASSEMBLY'
    FROM (SELECT A.ref_id, A.doc_type_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, SUM(A.child_amount) AS total_child_amount
            FROM tt_in_add_product_assembly_unamount A
            WHERE A.session_id = pSessionId AND
                A.doc_type_id = 521
            GROUP BY A.ref_id, A.doc_type_id,  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id) A,
            in_inventory_item B
    WHERE A.ref_id = B.inventory_item_id
    GROUP BY  A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, B.product_id, B.base_uom_id;

    /*
     * ambil saldo awal product assembly
     */
    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, pTenantId, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
        A.gl_curr_code, SUM(A.gl_amount), 'BGN_BALANCE'
    FROM in_summary_monthly_amount A, vw_finish_goods_product_assembly E
    WHERE A.date_year_month = pYearMonth AND
        A.doc_type_id = vDocTypeAwal AND
        A.ou_bu_id = pOuId AND
        A.tenant_id = pTenantId AND
        A.product_id = E.product_id
    GROUP BY A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id, A.gl_curr_code;

    /*
     * hitung harga rata-rata sebagai dasar harga pokok product assembly
     */
    INSERT INTO tt_in_summary_monthly_assembly_cogs
    (session_id, date_year_month, tenant_id, ou_id,
    product_id, qty_total, curr_code, amount_total, avg_price)
    SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
        A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), SUM(A.gl_amount) / SUM(A.qty)
    FROM tt_in_summary_monthly_product_assembly A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;

    /*
     * buat data transaksi yang menggunakan product assembly
     * DO (311), DO Receipt(526)  POS Shop (431), POS Shop In Shop (431)
     * Adj Stok Qty (521) < 0
     * NK, 16 Feb 2015 :
     * DO Internal, Sales Invoice Konsinyasi,Return POS Shop,Return POS Shop In Shop tidak digunakan
     */
    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN (311,431,526) AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;

    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        431, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN (401,402,403,404,405,406) AND
        A.product_id = E.product_id
    GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id, A.base_uom_id
    HAVING SUM(A.qty) <> 0;


    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, vw_finish_goods_product_assembly E
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = pOuId AND
        A.doc_date = C.string_date AND
        C.year_month_date = pYearMonth AND
        A.doc_type_id IN (521) AND
        A.product_id = E.product_id AND
        A.qty < 0
    GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
/* NK, 26 Jan 2014
 * adj stok qty sudah dipisahkan untuk data log menjadi qty > 0 dan qty < 0
    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount)
    SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_branch_id, C.ou_sub_bu_id,
        A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
        vCurrGL, 0, 'MONTHLY_AVG'
    FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_finish_goods_product_assembly E
    WHERE A.ou_from_id = C.ou_id AND
        C.ou_bu_id = pOuId AND
        A.inventory_id = B.inventory_id AND
        A.doc_date = D.string_date AND
        D.date_year_month = pYearMonth AND
        A.doc_type_id IN (521) AND
        B.qty_realization < 0 AND
        B.product_id = E.product_id
    GROUP BY A.tenant_id, C.ou_bu_id, C.ou_branch_id, C.ou_sub_bu_id, A.doc_type_id, B.product_id, B.base_uom_id;
*/
    UPDATE tt_in_summary_monthly_product_assembly SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
    FROM tt_in_summary_monthly_assembly_cogs A
    WHERE A.session_id = pSessionId AND
        tt_in_summary_monthly_product_assembly.session_id = A.session_id AND
        tt_in_summary_monthly_product_assembly.tenant_id = A.tenant_id AND
        tt_in_summary_monthly_product_assembly.ou_bu_id = A.ou_id AND
        tt_in_summary_monthly_product_assembly.product_id = A.product_id AND
        tt_in_summary_monthly_product_assembly.doc_type_id IN (311,431,526,521) AND
        tt_in_summary_monthly_product_assembly.flg_amount = 'MONTHLY_AVG' AND
        A.qty_total <> 0;

    /*
     * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
     * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
     */
    INSERT INTO tt_in_summary_monthly_amount_remain_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount, summary_monthly_product_assembly_id)
    SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
        vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
        vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
    FROM tt_in_summary_monthly_product_assembly A
    WHERE A.session_id = pSessionId
    GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;

    -- Cari id data sebagai penampung alokasi nilai
    UPDATE tt_in_summary_monthly_amount_remain_assembly
        SET summary_monthly_product_assembly_id = (
            SELECT MAX(A.summary_monthly_product_assembly_id)
            FROM tt_in_summary_monthly_product_assembly A
            WHERE A.session_id = pSessionId AND
                tt_in_summary_monthly_amount_remain_assembly.session_id = A.session_id AND
                tt_in_summary_monthly_amount_remain_assembly.date_year_month = A.date_year_month AND
                tt_in_summary_monthly_amount_remain_assembly.tenant_id = A.tenant_id AND
                tt_in_summary_monthly_amount_remain_assembly.ou_bu_id = A.ou_bu_id AND
                tt_in_summary_monthly_amount_remain_assembly.product_id = A.product_id AND
                A.doc_type_id IN (311,431,526,521) AND
                A.flg_amount = 'MONTHLY_AVG')
    WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;

    /*
     * NK, 16 Feb 2015
     * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
     * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
     */
    INSERT INTO in_summary_monthly_zero_qty_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
    doc_type_id, qty, gl_amount)
    SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
        B.doc_type_id, B.qty, B.gl_amount
    FROM tt_in_summary_monthly_amount_remain_assembly A, tt_in_summary_monthly_product_assembly B
    WHERE A.session_id = pSessionId AND
        A.summary_monthly_product_assembly_id <> vEmptyId AND
        B.summary_monthly_product_assembly_id = A.summary_monthly_product_assembly_id;

    UPDATE tt_in_summary_monthly_product_assembly SET gl_amount = tt_in_summary_monthly_product_assembly.gl_amount - A.gl_amount
    FROM tt_in_summary_monthly_amount_remain_assembly A
    WHERE A.session_id = pSessionId AND
        A.summary_monthly_product_assembly_id <> vEmptyId AND
        tt_in_summary_monthly_product_assembly.summary_monthly_product_assembly_id = A.summary_monthly_product_assembly_id;

    /*
     * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
     */
    INSERT INTO tt_in_summary_monthly_product_assembly
    (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
    SELECT A.session_id, vNextYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
    FROM tt_in_summary_monthly_amount_remain_assembly A
    WHERE A.session_id = pSessionId AND A.qty > 0;

    INSERT INTO in_summary_monthly_assembly_cogs
    (date_year_month, tenant_id, ou_id, product_id,
    qty_total, curr_code, amount_total, avg_price,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
            A.qty_total, A.curr_code, A.amount_total, A.avg_price,
            0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_in_summary_monthly_assembly_cogs A
    WHERE A.session_id = pSessionId;

    INSERT INTO in_summary_monthly_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
    SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
            A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
            A.gl_curr_code, A.gl_amount, A.flg_amount,
            0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
    FROM tt_in_summary_monthly_product_assembly A
    WHERE A.session_id = pSessionId AND
            A.date_year_month = pYearMonth AND
            A.doc_type_id <> vDocTypeAwal;

    INSERT INTO in_summary_monthly_amount
    (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
    doc_type_id, product_id, base_uom_id, qty,
    gl_curr_code, gl_amount, flg_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
    SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
        A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
        A.gl_curr_code, A.gl_amount, A.flg_amount,
        0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
    FROM tt_in_summary_monthly_product_assembly A
    WHERE A.session_id = pSessionId AND
        A.date_year_month = vNextYearMonth;

    DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;

    DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;

    DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_assembly_cogs WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_product_assembly WHERE session_id = pSessionId;

    DELETE FROM tt_in_summary_monthly_amount_remain_assembly WHERE session_id = pSessionId;

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