Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Modified by Adrian, Jul 31, 2017
- --meng-comment kondisi AND A.item_amount > 0 pada tt_purch_by_period
- CREATE OR REPLACE FUNCTION pu_get_latest_purchasing_price_by_date(character varying, bigint, character varying, bigint, character varying, bigint)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pYearMonth ALIAS FOR $3;
- pOuId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pUserId ALIAS FOR $6;
- vEmptyString character varying := '';
- vNo character varying := 'N';
- vYes character varying := 'Y';
- vZero numeric := 0;
- vStartDate character varying(8);
- vStartDateFromStartMonth character varying(8);
- vEndDate character varying(8);
- vLastSummaryPeriod character varying(8);
- vDefaultGlCurrCode character varying(100);
- vRoundingMode character varying(10);
- vOneMonthBeforeStartDate character varying(6);
- vOneMonthAfterLastSummary character varying(6);
- vReceiveGoodsDocTypeId bigint := 111;
- BEGIN
- 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');
- DELETE FROM pu_temp_summary_monthly_latest_purchasing WHERE session_id = pSessionId;
- DELETE FROM tt_purch_by_period WHERE session_id = pSessionId;
- ANALYZE tt_in_latest_purchasing_price_by_date;
- -- 1. pindahkan semua yg di input ke output nya terlebih dahulu
- -- (contoh: doc date ada bulan Desember 2014 - Maret 2015)
- INSERT INTO tt_out_latest_purchasing_price_by_date (
- session_id, tenant_id, ou_bu_id, doc_date, product_id,
- purch_curr_code, purch_nett_price, purch_gross_price,
- gl_purch_curr_code, gl_purch_nett_price, gl_purch_gross_price,
- flg_cogs
- )
- SELECT pSessionId, tenant_id, ou_bu_id, doc_date, product_id,
- vEmptyString, vZero, vZero,
- vEmptyString, vZero, vZero,
- vNo
- FROM tt_in_latest_purchasing_price_by_date
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId;
- ANALYZE tt_out_latest_purchasing_price_by_date;
- -- 2. cari periode penjualan yang harus dicari nilai pembeliannya
- SELECT MIN(doc_date), MAX(doc_date) INTO vStartDate, vEndDate
- FROM tt_out_latest_purchasing_price_by_date
- WHERE session_id = pSessionId;
- ANALYZE pu_summary_monthly_latest_purchasing;
- -- 3. cari rekap purchasing terakhir bulan apa (contoh: Januari 2015) sebelum start date
- SELECT MAX(year_month_date) INTO vLastSummaryPeriod
- FROM pu_summary_monthly_latest_purchasing
- WHERE ou_id = pOuId
- AND tenant_id = pTenantId
- AND year_month_date < vStartDate;
- -- 4. lakukan proses rekap temporary dari periode terakhir rekap + 1 bulan sampai periode penjualan awal - 1 bulan
- SELECT TO_CHAR(TO_DATE(vStartDate,'YYYYMMDD') - interval '1 Month','YYYYMM') INTO vOneMonthBeforeStartDate;
- SELECT TO_CHAR(TO_DATE(vLastSummaryPeriod,'YYYYMM') + interval '1 Month','YYYYMM') INTO vOneMonthAfterLastSummary;
- SELECT SUBSTRING(vStartDate, 1, 6) || '01' INTO vStartDateFromStartMonth;
- IF SUBSTRING(vStartDate, 1, 6) <= vLastSummaryPeriod THEN
- 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, 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,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_summary_monthly_latest_purchasing
- WHERE ou_id = pOuId
- AND tenant_id = pTenantId
- AND year_month_date = vOneMonthBeforeStartDate;
- ELSE
- 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, 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,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM pu_summary_monthly_latest_purchasing
- WHERE ou_id = pOuId
- AND tenant_id = pTenantId
- AND year_month_date = vLastSummaryPeriod;
- WHILE vOneMonthAfterLastSummary <= vOneMonthBeforeStartDate LOOP
- PERFORM pu_temp_summary_purch(pTenantId,pSessionId,pOuId,vOneMonthAfterLastSummary,pDatetime,pUserId);
- SELECT TO_CHAR(TO_DATE(vOneMonthAfterLastSummary,'YYYYMM') + interval '1 Month','YYYYMM') INTO vOneMonthAfterLastSummary;
- END LOOP;
- END IF;
- ANALYZE tt_out_latest_purchasing_price_by_date;
- -- 5. ambil transaksi pembelian dari bulan transaksi yg di cari di nomor 1 (contoh: Desember 2014 - Maret 2015)
- INSERT INTO tt_purch_by_period (
- session_id, tenant_id, ou_id, doc_date, product_id, curr_code,
- nett_price, gross_price, gl_curr_code, gl_nett_price, gl_gross_price
- )
- SELECT pSessionId, A.tenant_id, A.ou_id, A.ref_doc_date,
- B.product_id, A.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,
- 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,
- vDefaultGlCurrCode,
- 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,
- 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
- FROM pu_po_balance_invoice A
- INNER JOIN pu_receive_goods_item B ON A.ref_item_id = B.receive_goods_item_id
- 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 A.ref_doc_date BETWEEN vStartDateFromStartMonth AND vEndDate
- AND A.ou_id = pOuId
- AND A.tenant_id = pTenantId
- AND EXISTS (
- SELECT 1
- FROM tt_out_latest_purchasing_price_by_date C
- WHERE C.session_id = pSessionId
- AND C.product_id = B.product_id
- AND A.ou_id = C.ou_bu_id
- )
- 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 = SUBSTR(A.ref_doc_date, 1, 6)
- 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
- LIMIT 1
- )
- GROUP BY A.tenant_id, A.ou_id, A.ref_doc_date, B.product_id, A.curr_code
- ORDER BY A.ref_doc_date DESC;
- ANALYZE tt_out_latest_purchasing_price_by_date;
- ANALYZE tt_purch_by_period;
- -- 6. update nilai pembelian terakhir dari step nomor 5
- WITH tt_purch_by_period_by_date AS (
- SELECT A.session_id, B.ou_id, A.product_id, A.doc_date, MAX(B.doc_date) AS max_doc_date
- FROM tt_purch_by_period B
- INNER JOIN tt_out_latest_purchasing_price_by_date A
- ON B.session_id = A.session_id
- AND A.product_id = B.product_id
- AND A.ou_bu_id = B.ou_id
- AND B.doc_date <= A.doc_date
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, B.ou_id, A.product_id, A.doc_date
- )
- UPDATE tt_out_latest_purchasing_price_by_date A
- SET purch_curr_code = B.curr_code,
- purch_nett_price = B.nett_price,
- purch_gross_price = B.gross_price,
- gl_purch_curr_code = B.gl_curr_code,
- gl_purch_nett_price = B.gl_nett_price,
- gl_purch_gross_price = B.gl_gross_price,
- flg_cogs = vYes
- FROM tt_purch_by_period B
- INNER JOIN tt_purch_by_period_by_date C
- ON B.session_id = C.session_id
- AND C.product_id = B.product_id
- AND C.ou_id = B.ou_id
- AND B.doc_date = C.max_doc_date
- WHERE B.session_id = A.session_id
- AND A.session_id = pSessionId
- AND A.product_id = B.product_id
- AND A.ou_bu_id = B.ou_id
- AND A.doc_date = C.doc_date
- AND A.flg_cogs = vNo;
- ANALYZE pu_temp_summary_monthly_latest_purchasing;
- -- 7. update nilai pembelian terakhir bagi yang belum dapat nilai di nomor 6 dengan nilai rekap terakhir dari rekap pembelian (temp)
- UPDATE tt_out_latest_purchasing_price_by_date A
- SET purch_curr_code = B.curr_code,
- purch_nett_price = B.nett_price,
- purch_gross_price = B.gross_price,
- gl_purch_curr_code = B.gl_curr_code,
- gl_purch_nett_price = B.gl_nett_price,
- gl_purch_gross_price = B.gl_gross_price,
- flg_cogs = vYes
- FROM pu_temp_summary_monthly_latest_purchasing B
- WHERE A.flg_cogs = vNo
- AND A.session_id = pSessionId
- AND A.product_id = B.product_id
- AND A.ou_bu_id = B.ou_id
- AND B.year_month_date = vOneMonthBeforeStartDate
- AND B.session_id = pSessionId;
- DELETE FROM pu_temp_summary_monthly_latest_purchasing WHERE session_id = pSessionId;
- DELETE FROM tt_purch_by_period WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement