r_inventory_summary_mutation_monthly_by_product_code_periode

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

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

CREATE OR REPLACE FUNCTION r_inventory_summary_mutation_monthly_by_product_code_periode(character varying, bigint, bigint, bigint, character varying, bigint, character varying, 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;
    pYearMonthFrom              ALIAS FOR $7;
    pYearMonthTo                ALIAS FOR $8;
    pCodeProduct                ALIAS FOR $9;

    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,
            qty_stock_awal)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
            sum(A.qty)
            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 = pYearMonthFrom
            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_awal: Journal Voucher Debt
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_awal,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
            qty_stock_awal)
            SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount),vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
            sum(A.qty)
            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) < pYearMonthFrom
            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 stock_awal: Journal Voucher Credit
    INSERT INTO tt_mutasi_nilai_persediaan(
            session_id,year_month,stock_awal,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
            qty_stock_awal)
            SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount) * -1,vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
            sum(A.qty)
            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) < pYearMonthFrom
            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;

    -- 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,
            qty_stock_receipt)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
            sum(A.qty)
            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 BETWEEN pYearMonthFrom AND pYearMonthTo
            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,
            qty_stock_sales)
            SELECT pSessionId,A.date_year_month,(SUM(gl_amount) * -1),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
            sum(A.qty) * -1
            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 BETWEEN pYearMonthFrom AND pYearMonthTo
            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,
            qty_stock_return_sales)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
            sum(A.qty)
            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 BETWEEN pYearMonthFrom AND pYearMonthTo
            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,
            qty_stock_adj_qty)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
            sum(A.qty)
            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 BETWEEN pYearMonthFrom AND pYearMonthTo
            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,
            qty_stock_adj_amount)
            SELECT pSessionId,A.date_year_month,SUM(gl_amount),A.product_id,B.ctgr_product_id,B.sub_ctgr_product_id,
            sum(A.qty)
            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 BETWEEN pYearMonthFrom AND pYearMonthTo
            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,
            qty_stock_claim_note)
            SELECT pSessionId, A.date_year_month, SUM(gl_amount) * -1, A.product_id, B.ctgr_product_id, B.sub_ctgr_product_id,
            sum(A.qty)* -1
            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 BETWEEN pYearMonthFrom AND pYearMonthTo
            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_adj_amount,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
            qty_stock_adj_amount)
            SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount),vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
            sum(A.qty)
            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) BETWEEN pYearMonthFrom AND pYearMonthTo
            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_adj_amount,product_id,ctgr_product_id,sub_ctgr_product_id,journal_trx_id,coa_id,
            qty_stock_adj_amount)
            SELECT pSessionId,SUBSTRING(B.doc_date, 1, 6),SUM(gl_amount) * -1,vEmptyId,vEmptyId,vEmptyId,A.journal_trx_id,A.coa_id,
            sum(A.qty)
            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) BETWEEN pYearMonthFrom AND pYearMonthTo
            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, pYearMonthFrom AS year_month_from, pYearMonthTo AS year_month_to,
                f_get_username(pUserId) AS username, f_get_role_name(pRoleId) AS role_name, pDatetime AS datetime;
    RETURN NEXT pRefHeader;


    OPEN pRefDetail FOR
        SELECT C.*
        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.qty_stock_awal) AS qty_stock_awal,
                SUM(A.stock_awal) AS stock_awal,
                SUM(A.qty_stock_receipt) AS qty_stock_receipt,
                SUM(A.stock_receipt) AS stock_receipt,
                SUM(A.qty_stock_claim_note) AS qty_stock_claim_note,
                SUM(A.stock_claim_note) AS stock_claim_note,
                SUM(A.qty_stock_sales) AS qty_stock_sales,
                SUM(A.stock_sales) AS stock_sales,
                SUM(A.qty_stock_return_sales) AS qty_stock_return_sales,
                SUM(A.stock_return_sales) AS stock_return_sales,
                SUM(A.qty_stock_adj_qty) AS qty_stock_adj_qty,
                SUM(A.stock_adj_qty) AS stock_adj_qty,
                SUM(A.qty_stock_adj_amount) AS qty_stock_adj_amount,
                SUM(A.stock_adj_amount) AS stock_adj_amount,
                SUM( A.qty_stock_awal + qty_stock_receipt - qty_stock_sales + qty_stock_return_sales + qty_stock_adj_qty + qty_stock_adj_amount - qty_stock_claim_note )
                  AS qty_stock_akhir,
                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
            AND A.year_month < pYearMonthFrom
            GROUP BY A.session_id, A.journal_trx_id, A.year_month, B.doc_no, A.coa_id
            ORDER BY A.year_month, B.doc_no, f_get_coa_desc(A.coa_id)
        ) C

        UNION ALL

        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.qty_stock_awal) AS qty_stock_awal,
                SUM(A.stock_awal) AS stock_awal,
                SUM(A.qty_stock_receipt) AS qty_stock_receipt,
                SUM(A.stock_receipt) AS stock_receipt,
                SUM(A.qty_stock_claim_note) AS qty_stock_claim_note,
                SUM(A.stock_claim_note) AS stock_claim_note,
                SUM(A.qty_stock_sales) AS qty_stock_sales,
                SUM(A.stock_sales) AS stock_sales,
                SUM(A.qty_stock_return_sales) AS qty_stock_return_sales,
                SUM(A.stock_return_sales) AS stock_return_sales,
                SUM(A.qty_stock_adj_qty) AS qty_stock_adj_qty,
                SUM(A.stock_adj_qty) AS stock_adj_qty,
                SUM(A.qty_stock_adj_amount) AS qty_stock_adj_amount,
                SUM(A.stock_adj_amount) AS stock_adj_amount,
                SUM( A.qty_stock_awal + qty_stock_receipt - qty_stock_sales + qty_stock_return_sales + qty_stock_adj_qty + qty_stock_adj_amount - qty_stock_claim_note )
                  AS qty_stock_akhir,
                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.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.qty_stock_awal) AS qty_stock_awal,
                SUM(A.stock_awal) AS stock_awal,
                SUM(A.qty_stock_receipt) AS qty_stock_receipt,
                SUM(A.stock_receipt) AS stock_receipt,
                SUM(A.qty_stock_claim_note) AS qty_stock_claim_note,
                SUM(A.stock_claim_note) AS stock_claim_note,
                SUM(A.qty_stock_sales) AS qty_stock_sales,
                SUM(A.stock_sales) AS stock_sales,
                SUM(A.qty_stock_return_sales) AS qty_stock_return_sales,
                SUM(A.stock_return_sales) AS stock_return_sales,
                SUM(A.qty_stock_adj_qty) AS qty_stock_adj_qty,
                SUM(A.stock_adj_qty) AS stock_adj_qty,
                SUM(A.qty_stock_adj_amount) AS qty_stock_adj_amount,
                SUM(A.stock_adj_amount) AS stock_adj_amount,
                SUM( A.qty_stock_awal + qty_stock_receipt - qty_stock_sales + qty_stock_return_sales + qty_stock_adj_qty + qty_stock_adj_amount - qty_stock_claim_note )
                  AS qty_stock_akhir,
                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
            AND A.year_month >= pYearMonthFrom
            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;
ALTER FUNCTION r_inventory_summary_mutation_monthly_by_product_code_periode(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying)
  OWNER TO sts;