Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION f_update_balance_stock(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
- RETURNS void 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;
- pWarehouseId ALIAS FOR $6;
- pDateNow ALIAS FOR $7;
- vLastClosingGeneralLedger CHARACTER VARYING := '';
- vStartDateLog CHARACTER VARYING := '';
- vEndDateLog CHARACTER VARYING := '';
- vNo CHARACTER VARYING := 'N';
- vYes CHARACTER VARYING := 'Y';
- vEmptyId bigint := -99;
- vOuId bigint;
- BEGIN
- DELETE FROM tr_summary_update_product_stock WHERE session_id = pSessionId;
- SELECT ou_id INTO vOuId
- FROM m_warehouse_ou WHERE warehouse_id = pWarehouseId;
- -- last closing ledger sebelum pDateNow
- SELECT COALESCE(MAX(date_year_month), MIN(date_year_month)) AS last_closing_gl INTO vLastClosingGeneralLedger
- FROM m_admin_process_ledger
- WHERE tenant_id = pTenantId AND
- ou_id = vOuId AND
- ledger_code = 'INV' AND
- status_ledger = '1' AND
- date_year_month <= SUBSTRING(pDateNow, 1, 6);
- -- tanggal awal untuk transaksi diantara tgl saldo awal sampai sebelum pDateNow
- vStartDateLog = vLastClosingGeneralLedger || '01';
- SELECT TO_CHAR(TO_DATE(pDateNow,'YYYYMMDD') + INTERVAL '-1 Day','YYYYMMDD') INTO vEndDateLog;
- -- insert from log product WHEN pFlgConsignment = 'N'
- EXECUTE '
- INSERT INTO tr_summary_update_product_stock(
- session_id, tenant_id, ou_id, product_id, product_code, product_name,
- product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
- warehouse_id, product_status, os_doc_qty
- )
- SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
- A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0,
- A.warehouse_id, A.product_status, 0
- FROM (
- SELECT A.product_id, B.product_code, B.product_name,
- A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
- CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
- A.warehouse_id, A.product_status
- FROM in_log_product_balance_stock A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_product_custom C ON B.product_id = C.product_id
- WHERE A.doc_date BETWEEN $4 AND $5
- AND A.warehouse_id = $3
- AND C.flg_buy_konsinyasi = $6
- UNION ALL
- SELECT A.product_id, B.product_code, B.product_name,
- A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
- CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
- A.warehouse_id, A.product_status
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_product_custom C ON B.product_id = C.product_id
- WHERE A.doc_date BETWEEN $4 AND $5
- AND A.warehouse_id = $3
- AND C.flg_buy_konsinyasi = $6 ) A
- GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
- USING pSessionId, pTenantId, pWarehouseId, vStartDateLog, pDateNow, vNo;
- -- insert from log product WHEN pFlgConsignment = 'Y'
- EXECUTE '
- INSERT INTO tr_summary_update_product_stock(
- session_id, tenant_id, ou_id, product_id, product_code, product_name,
- product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
- warehouse_id, product_status, os_doc_qty
- )
- SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
- A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0,
- A.warehouse_id, A.product_status, 0
- FROM (
- SELECT A.product_id, B.product_code, B.product_name,
- A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
- CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
- A.warehouse_id, A.product_status
- FROM in_log_product_balance_stock A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_product_custom C ON B.product_id = C.product_id
- WHERE A.doc_date BETWEEN $4 AND $5
- AND A.warehouse_id = $3
- AND C.flg_buy_konsinyasi = $6
- AND A.doc_type_id <> 111
- UNION ALL
- SELECT A.product_id, B.product_code, B.product_name,
- A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
- CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif,
- A.warehouse_id, A.product_status
- FROM in_log_product_consignment_balance_stock A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_product_custom C ON B.product_id = C.product_id
- WHERE A.doc_date BETWEEN $4 AND $5
- AND A.warehouse_id = $3
- AND C.flg_buy_konsinyasi = $6
- ) A
- GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
- USING pSessionId, pTenantId, pWarehouseId, vStartDateLog, pDateNow, vYes;
- -- update nilai saldo awal qty WHEN pFlgConsignment = 'N'
- WITH tt_in_summary_monthly_qty AS (
- SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
- FROM in_summary_monthly_qty
- WHERE tenant_id = pTenantId
- AND doc_type_id = vEmptyId
- AND date_year_month = vLastClosingGeneralLedger
- AND warehouse_id = pWarehouseId
- GROUP BY tenant_id, product_id, product_balance_id, warehouse_id, product_status
- )
- UPDATE tr_summary_update_product_stock A
- SET last_balance_qty = B.qty,
- expectation = B.qty
- FROM tt_in_summary_monthly_qty B
- WHERE A.tenant_id = B.tenant_id
- AND A.product_id = B.product_id
- AND A.product_balance_id = B.product_balance_id
- AND A.warehouse_id = B.warehouse_id
- AND A.product_status = B.product_status
- AND A.session_id = pSessionId;
- -- update nilai saldo awal qty WHEN pFlgConsignment = 'Y'
- WITH tt_in_summary_monthly_qty AS (
- SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
- FROM in_summary_monthly_qty_consignment
- WHERE tenant_id = pTenantId
- AND doc_type_id = vEmptyId
- AND date_year_month = vLastClosingGeneralLedger
- AND warehouse_id = pWarehouseId
- GROUP BY tenant_id, product_id, product_balance_id, warehouse_id, product_status
- )
- UPDATE tr_summary_update_product_stock A
- SET last_balance_qty = B.qty,
- expectation = B.qty
- FROM tt_in_summary_monthly_qty B
- WHERE A.tenant_id = B.tenant_id
- AND A.product_id = B.product_id
- AND A.product_balance_id = B.product_balance_id
- AND A.warehouse_id = B.warehouse_id
- AND A.product_status = B.product_status
- AND A.session_id = pSessionId;
- -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'N'
- EXECUTE '
- INSERT INTO tr_summary_update_product_stock(
- session_id, tenant_id, ou_id, product_id, product_code, product_name,
- product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
- warehouse_id, product_status, os_doc_qty
- )
- SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
- A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty),
- A.warehouse_id, A.product_status, 0
- FROM in_summary_monthly_qty A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_product_custom C ON B.product_id = C.product_id
- WHERE A.doc_type_id = $4
- AND A.date_year_month = $5
- AND A.warehouse_id = $3
- AND C.flg_buy_konsinyasi = $6
- AND NOT EXISTS(
- SELECT 1 FROM tr_summary_update_product_stock D
- WHERE A.tenant_id = D.tenant_id
- AND A.product_id = D.product_id
- AND A.product_balance_id = D.product_balance_id
- ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
- USING pSessionId, pTenantId, pWarehouseId, vEmptyId, vLastClosingGeneralLedger, vNo;
- -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'Y'
- EXECUTE '
- INSERT INTO tr_summary_update_product_stock(
- session_id, tenant_id, ou_id, product_id, product_code, product_name,
- product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation,
- warehouse_id, product_status, os_doc_qty
- )
- SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
- A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty),
- A.warehouse_id, A.product_status, 0
- FROM in_summary_monthly_qty_consignment A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN m_product_custom C ON B.product_id = C.product_id
- WHERE A.doc_type_id = $4
- AND A.date_year_month = $5
- AND A.warehouse_id = $3
- AND C.flg_buy_konsinyasi = $6
- AND NOT EXISTS(
- SELECT 1 FROM tr_summary_update_product_stock D
- WHERE A.tenant_id = D.tenant_id
- AND A.product_id = D.product_id
- AND A.product_balance_id = D.product_balance_id
- ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
- USING pSessionId, pTenantId, pWarehouseId, vEmptyId, vLastClosingGeneralLedger, vYes;
- EXECUTE '
- WITH tt_os_document_qty AS (
- SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(A.qty_realization) AS qty, A.warehouse_from_id AS warehouse_id, A.product_status
- FROM vw_in_product_out_with_outstanding_document A
- WHERE A.tenant_id = $2
- AND A.warehouse_from_id = $3
- GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_from_id, A.product_status
- )
- UPDATE tr_summary_update_product_stock A
- SET os_doc_qty = B.qty
- FROM tt_os_document_qty B
- WHERE A.tenant_id = B.tenant_id
- AND A.product_id = B.product_id
- AND A.product_balance_id = B.product_balance_id
- AND A.warehouse_id = B.warehouse_id
- AND A.product_status = B.product_status
- AND A.session_id = $1
- ' USING pSessionId, pTenantId, pWarehouseId;
- UPDATE in_product_balance_stock A
- SET qty = B.last_balance_qty + B.mutasi_positif - B.mutasi_minus - B.os_doc_qty ,
- update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
- FROM tr_summary_update_product_stock B
- WHERE A.tenant_id = B.tenant_id
- AND A.product_id = B.product_id
- AND A.product_balance_id = B.product_balance_id
- AND A.warehouse_id = B.warehouse_id
- AND A.product_status = B.product_status
- AND B.session_id = pSessionId;
- DELETE FROM tr_summary_update_product_stock WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment