Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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_sub_ctgr_product_code(character varying,bigint, bigint, bigint, character varying, bigint, character varying,bigint)
- 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;
- pSubCtgrProductId 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;
- vEmpty integer := -99;
- 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,
- 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 pSubCtgrProductId <> vEmpty THEN A.sub_ctgr_product_id = pSubCtgrProductId ELSE A.sub_ctgr_product_id <> vEmpty END
- AND A.journal_trx_id = vEmptyId
- GROUP BY A.session_id, A.year_month,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)
- -- 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,
- 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;
- /
Add Comment
Please, Sign In to add comment