Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Modified by Adrian, Aug 1, 2017
- * monthly_price_amount di-update per year-month
- */
- CREATE OR REPLACE FUNCTION r_outlet_analysis_commision_gross_profit(character varying, 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;
- pOuId ALIAS FOR $3;
- pYearMonth ALIAS FOR $4;
- pUserId ALIAS FOR $5;
- pOutletName ALIAS FOR $6;
- pTglAwal ALIAS FOR $7;
- pTglAkhir ALIAS FOR $8;
- vEmptyValue character varying(1);
- vAwalTahun character varying(6);
- vAkhirRekap character varying(6);
- vAllId bigint;
- vEmptyId bigint;
- vRoundingMode character varying(5);
- vDatetime character varying(14);
- vCurrentYearMonth character varying(6);
- BEGIN
- vEmptyValue := '';
- vAllId := -99;
- vEmptyId := -99;
- vAwalTahun := SUBSTRING(pYearMonth, 1, 4) || '01';
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;
- IF SUBSTRING(pYearMonth, 5, 2) = '01' THEN
- vAkhirRekap := pYearMonth;
- ELSE
- vAkhirRekap := TO_CHAR(to_date(pYearMonth, 'YYYYMM') - interval '1 month', 'YYYYMM');
- END IF;
- DELETE FROM tr_commision_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_current_commision_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_rekap_commision_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_commision_gross_profit_for_output WHERE session_id = pSessionId;
- DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- /*
- * ambil semua trx pos di main business unit yang tidak di void
- * jika transaksi itu ada item assembly ambil berdasarkan core product id nya
- * ambil juga harga beli terakhir dari item itu sesuai dengan tahun bulan
- */
- INSERT INTO tr_commision_gross_profit(
- session_id, tenant_id, year_month_date, ou_id, ou_code, ou_name, ou_parent_id,
- doc_type_id, doc_no, doc_date, line_no,
- product_id, curr_code, qty, nett_sell_price, nett_amount_item,
- commision_ou_id, commision_type, commision_percentage, commision_curr_code, commision_amount,
- monthly_price_curr_code, monthly_price_amount
- )
- SELECT pSessionId, A.tenant_id, SUBSTRING(A.doc_date, 1, 6), A.ou_id, B.ou_code, B.ou_name, B.ou_parent_id,
- A.doc_type_id, A.doc_no, A.doc_date, C.line_no,
- COALESCE(G.core_product_id, C.product_id), C.curr_code, C.qty, C.nett_sell_price, C.nett_amount_item,
- D.commision_ou_id, D.commision_type, D.commision_percentage, D.curr_code, D.commision_amount,
- '', 0
- FROM i_trx_pos A
- INNER JOIN t_ou B ON A.ou_id = B.ou_id
- INNER JOIN i_trx_pos_item C ON A.tenant_id = C.tenant_id AND A.trx_pos_id = C.trx_pos_id AND A.process_no = C.process_no
- INNER JOIN m_commision_ou D ON C.group_product_ou_id = D.group_product_ou_id
- LEFT OUTER JOIN m_ext_product G ON C.product_id = G.product_id
- WHERE A.tenant_id = pTenantId
- AND SUBSTRING(A.doc_date, 1, 6) BETWEEN vAwalTahun AND pYearMonth
- AND B.ou_parent_id = pOuId
- AND NOT EXISTS (
- SELECT 1 FROM i_trx_log_voided_pos F
- WHERE A.tenant_id = F.tenant_id
- AND A.doc_no = F.doc_no
- AND A.doc_date = F.doc_date
- AND A.ou_id = F.ou_id
- );
- ANALYZE tr_commision_gross_profit;
- /* v329 data yang diambil dari pu_monthly_price_product diubah
- * menjadi ambil dari table
- * tt_out_latest_purchasing_price_by_date menggunakan
- * pu_get_latest_purchasing_price_by_date yang sebelumnya datanya dimasukkan ke
- * tt_in_latest_purchasing_price_by_date */
- --Modified by Adrian, Aug 1, 2017
- --monthly_price_amount di-update per year-month
- vCurrentYearMonth := vAwalTahun;
- WHILE vCurrentYearMonth <= pYearMonth LOOP
- DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- INSERT INTO tt_in_latest_purchasing_price_by_date(session_id, tenant_id, ou_bu_id, doc_date, product_id)
- SELECT pSessionId, pTenantId, B.ou_bu_id, A.doc_date, A.product_id
- FROM tr_commision_gross_profit A
- INNER JOIN m_ou_structure B ON A.ou_id = B.ou_id
- WHERE A.tenant_id = pTenantId
- AND A.session_id = pSessionId
- AND A.year_month_date = vCurrentYearMonth
- GROUP BY B.ou_bu_id, A.doc_date, A.product_id;
- SELECT TO_CHAR(now(), 'YYYYMMDDHH24MISS') INTO vDatetime;
- PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, vCurrentYearMonth, pOuId, vDatetime, pUserId);
- ANALYZE tr_commision_gross_profit;
- UPDATE tr_commision_gross_profit B
- --SET monthly_price_curr_code = A.curr_code, monthly_price_amount = ROUND(A.amount / A.qty)
- SET monthly_price_curr_code = COALESCE(A.gl_purch_curr_code, B.curr_code), monthly_price_amount = COALESCE(A.gl_purch_gross_price, 0)
- FROM tt_out_latest_purchasing_price_by_date A
- INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_bu_id
- WHERE B.session_id = A.session_id
- AND B.product_id = A.product_id
- AND B.doc_date = A.doc_date
- AND B.tenant_id = A.tenant_id
- AND B.year_month_date = vCurrentYearMonth
- AND C.ou_id = B.ou_id;
- SELECT TO_CHAR(TO_DATE(vCurrentYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vCurrentYearMonth;
- DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- END LOOP;
- ANALYZE tr_commision_gross_profit;
- /*
- * update nilai komisi jika menggunakan tipe komisi persentase
- */
- UPDATE tr_commision_gross_profit SET commision_amount = ROUND(nett_sell_price * commision_percentage / 100,0), commision_curr_code = curr_code
- WHERE commision_type = 'PCT'
- AND session_id = pSessionId;
- /*sebelum v329
- * update nilai harga beli terakhir menggunakan valuta penjualan (gunakan kurs komersial)
- UPDATE tr_commision_gross_profit SET monthly_price_amount = monthly_price_amount * f_commercial_rate(tenant_id, doc_date, monthly_price_curr_code, curr_code), monthly_price_curr_code = curr_code
- WHERE monthly_price_curr_code <> curr_code
- AND session_id = pSessionId AND monthly_price_curr_code <> '';
- */
- /*
- * pindahkan data tahun bulan yang diminta (sudah direkap per outlet)
- */
- ANALYZE tr_commision_gross_profit;
- INSERT INTO tr_current_commision_gross_profit (
- session_id, tenant_id, ou_id, ou_code, ou_name, curr_code,
- current_sales_amount, current_commision_amount, current_monthly_price_amount, current_gross_profit
- )
- SELECT pSessionId, tenant_id, ou_id, ou_code, ou_name, curr_code,
- SUM(nett_amount_item) AS current_sales_amount, SUM(commision_amount * qty) AS current_commision_amount, SUM(monthly_price_amount * qty) AS current_monthly_price_amount, 0
- FROM tr_commision_gross_profit
- WHERE session_id = pSessionId
- AND year_month_date = pYearMonth
- AND doc_date BETWEEN pTglAwal AND pTglAkhir
- GROUP BY tenant_id, ou_id, ou_code, ou_name, curr_code;
- /*
- * pindahkan data sebelum tahun bulan yang diminta (sudah direkap per outlet)
- */
- INSERT INTO tr_rekap_commision_gross_profit (
- session_id, tenant_id, ou_id, ou_code, ou_name, curr_code,
- rekap_sales_amount, rekap_commision_amount, rekap_monthly_price_amount, rekap_gross_profit
- )
- SELECT pSessionId, tenant_id, ou_id, ou_code, ou_name, curr_code,
- SUM(nett_amount_item) AS rekap_sales_amount, SUM(commision_amount * qty) AS rekap_commision_amount, SUM(monthly_price_amount * qty) AS rekap_monthly_price_amount, 0
- FROM tr_commision_gross_profit
- WHERE session_id = pSessionId
- AND year_month_date <> pYearMonth
- GROUP BY tenant_id, ou_id, ou_code, ou_name, curr_code;
- ANALYZE tr_current_commision_gross_profit;
- /*
- * update data gross profit untuk bulan yang diminta
- */
- UPDATE tr_current_commision_gross_profit SET current_gross_profit = current_sales_amount - current_monthly_price_amount
- WHERE session_id = pSessionId;
- ANALYZE tr_rekap_commision_gross_profit;
- /*
- * update data gross profit untuk sebelum bulan yang diminta
- */
- UPDATE tr_rekap_commision_gross_profit SET rekap_gross_profit = rekap_sales_amount - rekap_monthly_price_amount
- WHERE session_id = pSessionId;
- ANALYZE tr_current_commision_gross_profit;
- INSERT INTO tr_commision_gross_profit_for_output
- (session_id, tenant_id, ou_id, ou_code, ou_name,
- curr_code,
- current_sales_amount, current_commision_amount, current_gross_profit,
- rekap_sales_amount, rekap_commision_amount, rekap_gross_profit)
- SELECT pSessionId, A.tenant_id, A.ou_id, f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
- A.curr_code,
- A.current_sales_amount, A.current_commision_amount, A.current_gross_profit,
- 0, 0, 0
- FROM tr_current_commision_gross_profit A
- WHERE A.session_id = pSessionId;
- ANALYZE tr_rekap_commision_gross_profit;
- UPDATE tr_commision_gross_profit_for_output
- SET rekap_sales_amount = A.rekap_sales_amount, rekap_commision_amount = A.rekap_commision_amount, rekap_gross_profit = A.rekap_gross_profit
- FROM tr_rekap_commision_gross_profit A
- WHERE A.session_id = pSessionId AND tr_commision_gross_profit_for_output.ou_id = A.ou_id;
- INSERT INTO tr_commision_gross_profit_for_output
- (session_id, tenant_id, ou_id, ou_code, ou_name,
- curr_code,
- current_sales_amount, current_commision_amount, current_gross_profit,
- rekap_sales_amount, rekap_commision_amount, rekap_gross_profit)
- SELECT pSessionId, A.tenant_id, A.ou_id, f_get_ou_code(A.ou_id), f_get_ou_name(A.ou_id),
- A.curr_code,
- 0, 0, 0,
- A.rekap_sales_amount, A.rekap_commision_amount, A.rekap_gross_profit
- FROM tr_rekap_commision_gross_profit A
- WHERE A.session_id = pSessionId
- AND A.ou_id NOT IN ( SELECT B.ou_id FROM tr_commision_gross_profit_for_output B );
- /*
- * return header
- */
- Open pRefHeader FOR
- SELECT A.fullname AS username, B.ou_name AS ou_name, pYearMonth AS year_month_date_now, vAwalTahun AS year_month_date_from, vAkhirRekap AS year_month_date_to
- FROM t_user A
- INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
- WHERE user_id = pUserId
- AND ou_id = pOuId;
- RETURN NEXT pRefHeader;
- IF TRIM(pOutletName) <> vEmptyValue THEN
- Open pRefDetail FOR
- SELECT A.ou_id AS outlet_id, A.ou_code AS outlet_code, A.ou_name AS outlet_name,
- A.curr_code AS curr_code,
- A.current_sales_amount AS sales_amount_start, A.current_commision_amount AS commission_start, A.current_gross_profit AS gross_profit_start,
- A.rekap_sales_amount AS sales_amount_end, A.rekap_commision_amount AS commission_end, A.rekap_gross_profit AS gross_profit_end
- FROM tr_commision_gross_profit_for_output A
- WHERE A.session_id = pSessionId
- AND UPPER(A.ou_name) LIKE '%' || UPPER(pOutletName) || '%'
- ORDER BY curr_code, outlet_name;
- ELSE
- Open pRefDetail FOR
- SELECT A.ou_id AS outlet_id, A.ou_code AS outlet_code, A.ou_name AS outlet_name,
- A.curr_code AS curr_code,
- A.current_sales_amount AS sales_amount_start, A.current_commision_amount AS commission_start, A.current_gross_profit AS gross_profit_start,
- A.rekap_sales_amount AS sales_amount_end, A.rekap_commision_amount AS commission_end, A.rekap_gross_profit AS gross_profit_end
- FROM tr_commision_gross_profit_for_output A
- WHERE A.session_id = pSessionId
- ORDER BY curr_code, outlet_name;
- END IF;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_commision_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_current_commision_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_rekap_commision_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_commision_gross_profit_for_output WHERE session_id = pSessionId;
- DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement