Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Modified by Adrian, Jul 31, 2017, mengubah order pada summary_monthly_purchasing_temp menjadi ORDER BY C.ref_doc_date DESC, C.create_datetime DESC
- --meng-comment kondisi AND A.item_amount > 0
- --po_balance_invoice_id diambil per tanggal dan hanya yang terakhir
- CREATE OR REPLACE FUNCTION pu_temp_summary_purch(bigint, character varying, bigint, character varying, character varying, bigint)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pOuId ALIAS FOR $3;
- pYearMonth ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pUserId ALIAS FOR $6;
- vReceiveGoodsDocTypeId bigint;
- vPrevYearMonth character varying(6);
- vDefaultCurrCode character varying(10);
- vDefaultPrice numeric;
- vDefaultGlCurrCode character varying(100);
- vRoundingMode character varying(10);
- BEGIN
- vReceiveGoodsDocTypeId := 111;
- vDefaultCurrCode := '-99';
- vDefaultPrice := 0;
- vDefaultGlCurrCode := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
- vRoundingMode := f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax');
- SELECT TO_CHAR(TO_DATE(pYearMonth,'YYYYMM') - interval '1 Month','YYYYMM') INTO vPrevYearMonth;
- INSERT INTO pu_temp_summary_monthly_latest_purchasing
- (session_id, tenant_id, ou_id, year_month_date,
- product_id, curr_code, nett_price, gross_price,
- gl_curr_code, gl_nett_price, gl_gross_price,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pSessionId, tenant_id, ou_id, pYearMonth,
- product_id, curr_code, nett_price, gross_price,
- gl_curr_code, gl_nett_price, gl_gross_price,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_temp_summary_monthly_latest_purchasing
- WHERE ou_id = pOuId
- AND tenant_id = pTenantId
- AND year_month_date = vPrevYearMonth;
- ANALYZE pu_temp_summary_monthly_latest_purchasing;
- INSERT INTO pu_temp_summary_monthly_latest_purchasing
- (session_id, tenant_id, ou_id, year_month_date,
- product_id, curr_code, nett_price, gross_price,
- gl_curr_code, gl_nett_price, gl_gross_price,
- ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pSessionId, A.tenant_id, pOuId, pYearMonth,
- A.product_id, vDefaultCurrCode, vDefaultPrice, vDefaultPrice,
- vDefaultGlCurrCode, vDefaultPrice, vDefaultPrice,
- -99, -99, ' ', ' ',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM m_product A
- WHERE A.tenant_id = pTenantId
- AND NOT EXISTS (
- SELECT 1 FROM pu_temp_summary_monthly_latest_purchasing B
- WHERE B.ou_id = pOuId
- AND B.tenant_id = pTenantId
- AND B.year_month_date = pYearMonth
- AND B.product_id = A.product_id
- )
- AND NOT EXISTS (
- SELECT 1 FROM m_ext_product C
- WHERE C.product_id = A.product_id
- );
- ANALYZE pu_temp_summary_monthly_latest_purchasing;
- WITH summary_monthly_purchasing_temp AS (
- SELECT A.tenant_id, A.ou_id, F.year_month_date,
- B.product_id, A.curr_code AS curr_code,
- CASE WHEN vRoundingMode = 'RD'
- THEN TRUNC(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
- ELSE ROUND(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
- END AS nett_price,
- CASE WHEN vRoundingMode = 'RD'
- THEN TRUNC(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
- ELSE ROUND(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, A.curr_code))
- END AS gross_price,
- CASE WHEN vRoundingMode = 'RD'
- THEN TRUNC(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
- ELSE ROUND(SUM(A.item_amount) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
- END AS gl_nett_price,
- CASE WHEN vRoundingMode = 'RD'
- THEN TRUNC(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
- ELSE ROUND(SUM(A.item_amount + COALESCE(G.tax_amount, 0)) / SUM(A.qty_rcv_po), f_get_digit_decimal_doc_curr(-99, vDefaultGlCurrCode)) * f_commercial_rate(pTenantId, A.ref_doc_date, A.curr_code, vDefaultGlCurrCode)
- END AS gl_gross_price,
- A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date
- FROM pu_po_balance_invoice A
- INNER JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id
- INNER JOIN dt_date F ON A.ref_doc_date = F.string_date
- LEFT OUTER JOIN pu_po_balance_invoice_tax G
- ON A.tenant_id = G.tenant_id
- AND A.ou_id = G.ou_id
- AND A.partner_id = G.partner_id
- AND A.ref_doc_type_id = G.ref_doc_type_id
- AND A.ref_id = G.ref_id
- AND A.ref_item_id = G.ref_item_id
- WHERE A.ref_doc_type_id = vReceiveGoodsDocTypeId
- AND A.item_amount > 0
- AND F.year_month_date = pYearMonth
- AND A.ou_id = pOuId
- AND A.tenant_id = pTenantId
- AND A.po_balance_invoice_id = (
- SELECT C.po_balance_invoice_id
- FROM pu_po_balance_invoice C
- INNER JOIN pu_receive_goods_item D ON C.ref_item_id = D.receive_goods_item_id
- INNER JOIN dt_date E ON C.ref_doc_date = E.string_date
- WHERE D.product_id = B.product_id
- --AND E.year_month_date = F.year_month_date
- AND C.ref_doc_date <= A.ref_doc_date
- AND C.ou_id = A.ou_id
- AND C.ref_doc_type_id = vReceiveGoodsDocTypeId
- AND C.tenant_id = pTenantId
- ORDER BY C.ref_doc_date DESC, C.create_datetime DESC, D.line_no DESC
- LIMIT 1
- )
- GROUP BY A.tenant_id, A.ou_id, F.year_month_date, B.product_id, A.curr_code, A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date
- )
- UPDATE pu_temp_summary_monthly_latest_purchasing Z
- SET curr_code = Y.curr_code, nett_price = Y.nett_price, gross_price = Y.gross_price,
- gl_curr_code = vDefaultGlCurrCode, gl_nett_price = Y.gl_nett_price, gl_gross_price = Y.gl_gross_price,
- ref_doc_type_id = Y.ref_doc_type_id, ref_id = Y.ref_id, ref_doc_no = Y.ref_doc_no, ref_doc_date = Y.ref_doc_date,
- version = Z.version + 1, update_datetime = pDatetime, update_user_id = pUserId
- FROM summary_monthly_purchasing_temp Y
- WHERE Y.tenant_id = Z.tenant_id
- AND Y.year_month_date = Z.year_month_date
- AND Y.product_id = Z.product_id
- AND Y.ou_id = Z.ou_id
- AND Z.session_id = pSessionId;
- ANALYZE pu_temp_summary_monthly_latest_purchasing;
- DELETE FROM pu_temp_summary_monthly_latest_purchasing A
- WHERE A.tenant_id = pTenantId
- AND A.year_month_date = pYearMonth
- AND A.ou_id = pOuId
- AND A.curr_code = vDefaultCurrCode
- AND A.session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement