r_inventory_summary_mutation_monthly_by_product_code

-- Author by Widana Nur Azis, 16 Nov 2016
/**
 * Modified by Adrian, Dec 15, 2017
 * Menambahkan Journal Voucher untuk group COA Persediaan
 * Menambahkan Cost Allocation to Product ke stock_adj_amount
 */

CREATE OR REPLACE FUNCTION r_inventory_summary_mutation_monthly_by_product_code(character varying,bigint, bigint, bigint, character varying, bigint, character varying,character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pUserId                     ALIAS FOR $3;
    pRoleId                     ALIAS FOR $4;
    pDatetime                   ALIAS FOR $5;
    pOuId                       ALIAS FOR $6;
    pPeriodDate                 ALIAS FOR $7;
    pCodeProduct                ALIAS FOR $8;

    vDefaultDocTypeId                       integer := -99;
    vDocTypeIdGr                            integer := 111;
    vDocTypeIdDo                            integer := 311;
    vDocTypeIdDoReceipt                     integer := 526;
    vDocTypeIdRn                            integer := 502;
    vDocTypeidAsq                           integer := 521;
    vDocTypeIdAsa                           integer := 522;
    vDocTypeIdClaimNote                     integer := 511;
    vDocTypeIdCostingAllocationToProduct    integer := 528;
    vEmptyString                character varying := '';

    vGroupCoaPersediaanId               integer := 13;
    vGroupCoaPersediaanIdTenant11       integer := 1113;
    vEmptyId                            integer := -99;
    vSignJournalCredit                  character varying := 'C';
    vSignJournalDebit                   character varying := 'D';

BEGIN

    -- SUM for stock_awal
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_awal,product_id,ctgr_product_id,sub_ctgr_product_id)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id = vDefaultDocTypeId
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for stock_receipt
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_receipt,product_id,ctgr_product_id,sub_ctgr_product_id)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id = vDocTypeIdGr
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for stock_sales
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_sales,product_id,ctgr_product_id,sub_ctgr_product_id)
            SELECT pSessionId,A.date_year_month,(SUM(gl_amount) * -1),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id IN ( vDocTypeIdDo, vDocTypeIdDoReceipt )
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for stock_return_sales
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_return_sales,product_id,ctgr_product_id,sub_ctgr_product_id)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id = vDocTypeIdRn
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for stock_adj_qty
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_adj_qty,product_id,ctgr_product_id,sub_ctgr_product_id)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id = vDocTypeidAsq
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for stock_adj_amount
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_adj_amount,product_id,ctgr_product_id,sub_ctgr_product_id)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id IN (vDocTypeIdAsa, vDocTypeIdCostingAllocationToProduct)
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for stock_claim_note
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id, year_month, stock_claim_note, product_id, ctgr_product_id, sub_ctgr_product_id)
            SELECT pSessionId, A.date_year_month, SUM(gl_amount) * -1, A.product_id, B.ctgr_product_id, B.sub_ctgr_product_id
            FROM in_summary_monthly_amount A
            INNER JOIN m_product B USING (product_id)
            WHERE A.tenant_id = pTenantId
            AND A.ou_bu_id = pOuId
            AND A.date_year_month = pPeriodDate
            AND A.doc_type_id = vDocTypeIdClaimNote
            GROUP BY A.date_year_month,A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id;

    -- SUM for Journal Voucher Persediaan Debit
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_receipt,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id)
            SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount),vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id
            FROM vw_gl_journal_trx_details A
            INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
            INNER JOIN m_coa C ON A.coa_id = C.coa_id
            WHERE A.tenant_id = pTenantId
            AND B.ou_bu_id = pOuId
            AND SUBSTRING(B.doc_date, 1, 6) = pPeriodDate
            AND B.doc_type_id NOT IN (
                vDefaultDocTypeId, vDocTypeIdGr, vDocTypeIdDo,
                vDocTypeIdDoReceipt, vDocTypeIdRn, vDocTypeidAsq,
                vDocTypeIdAsa, vDocTypeIdClaimNote, vDocTypeIdCostingAllocationToProduct
            )
            AND C.group_coa_id IN (vGroupCoaPersediaanId, vGroupCoaPersediaanIdTenant11)
            AND A.sign_journal = vSignJournalDebit
            GROUP BY SUBSTRING(B.doc_date, 1, 6),A.journal_trx_id,A.coa_id;

    -- SUM for Journal Voucher Persediaan Credit
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_sales,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id)
            SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount),vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id
            FROM vw_gl_journal_trx_details A
            INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
            INNER JOIN m_coa C ON A.coa_id = C.coa_id
            WHERE A.tenant_id = pTenantId
            AND B.ou_bu_id = pOuId
            AND SUBSTRING(B.doc_date, 1, 6) = pPeriodDate
            AND B.doc_type_id NOT IN (
                vDefaultDocTypeId, vDocTypeIdGr, vDocTypeIdDo,
                vDocTypeIdDoReceipt, vDocTypeIdRn, vDocTypeidAsq,
                vDocTypeIdAsa, vDocTypeIdClaimNote, vDocTypeIdCostingAllocationToProduct
            )
            AND C.group_coa_id IN (vGroupCoaPersediaanId, vGroupCoaPersediaanIdTenant11)
            AND A.sign_journal = vSignJournalCredit
            GROUP BY SUBSTRING(B.doc_date, 1, 6),A.journal_trx_id,A.coa_id;

    OPEN pRefHeader FOR
        SELECT f_get_ou_name(pOuId) AS ou_name,pPeriodDate AS period,f_get_username(pUserId) AS username,f_get_role_name(pRoleId) AS role_name,pDatetime AS datetime;
    RETURN NEXT pRefHeader;


    OPEN pRefDetail FOR
        SELECT A.*
        FROM (
            SELECT A.session_id,f_get_ctgr_product_name(A.ctgr_product_id) AS ctgr_product_name,
                f_get_sub_ctgr_product_name(A.sub_ctgr_product_id) AS sub_ctgr_product_name,
                f_get_product_code(A.product_id) AS product_code,
                f_get_product_name(A.product_id) AS product_name,
                SUM(A.stock_awal) AS stock_awal,
                SUM(A.stock_receipt) AS stock_receipt,
                SUM(A.stock_sales) AS stock_sales,
                SUM(A.stock_return_sales) AS stock_return_sales,
                SUM(A.stock_adj_qty) AS stock_adj_qty,
                SUM(A.stock_adj_amount) AS stock_adj_amount,
                SUM(A.stock_claim_note) AS stock_claim_note,
                SUM( A.stock_awal + stock_receipt - stock_sales + stock_return_sales + stock_adj_qty + stock_adj_amount - stock_claim_note )
                  AS stock_akhir
            FROM tt_mutasi_nilai_persediaan A
            WHERE A.session_id = pSessionId AND
            CASE WHEN pCodeProduct <> vEmptyString THEN UPPER(f_get_product_code(A.product_id)) LIKE UPPER('%'||pCodeProduct||'%') ELSE f_get_product_code(A.product_id) <> vEmptyString END
            AND A.journal_trx_id = vEmptyId
            GROUP BY A.session_id, A.year_month, A.product_id, A.sub_ctgr_product_id, A.ctgr_product_id
            ORDER BY f_get_ctgr_product_name(A.ctgr_product_id), f_get_sub_ctgr_product_name(A.sub_ctgr_product_id), f_get_product_code(A.product_id)
    --           A.stock_sales,A.stock_return_sales,A.stock_adj_qty,A.stock_adj_amount,A.stock_awal,A.stock_receipt;
        ) A

        UNION ALL

        SELECT B.*
        FROM (
            SELECT A.session_id, B.doc_no AS ctgr_product_name,
                f_get_coa_desc(A.coa_id) AS sub_ctgr_product_name,
                vEmptyString AS product_code,
                vEmptyString AS product_name,
                SUM(A.stock_awal) AS stock_awal,
                SUM(A.stock_receipt) AS stock_receipt,
                SUM(A.stock_sales) AS stock_sales,
                SUM(A.stock_return_sales) AS stock_return_sales,
                SUM(A.stock_adj_qty) AS stock_adj_qty,
                SUM(A.stock_adj_amount) AS stock_adj_amount,
                SUM(A.stock_claim_note) AS stock_claim_note,
                SUM( A.stock_awal + stock_receipt - stock_sales + stock_return_sales + stock_adj_qty + stock_adj_amount - stock_claim_note )
                  AS stock_akhir
            FROM tt_mutasi_nilai_persediaan A
            INNER JOIN gl_journal_trx B ON A.journal_trx_id = B.journal_trx_id
            WHERE A.session_id = pSessionId
            AND A.journal_trx_id <> vEmptyId
            GROUP BY A.session_id, A.journal_trx_id, B.doc_no, A.coa_id
            ORDER BY B.doc_no, f_get_coa_desc(A.coa_id)
        ) B;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_mutasi_nilai_persediaan WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/