Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Modified by Adrian, Aug 1, 2017
- * monthly_price_amount diambil per year-month
- */
- CREATE OR REPLACE FUNCTION r_detail_analysis_sales_gross_profit(character varying, bigint, 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;
- pUserId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pPartnerId ALIAS FOR $6;
- pDateFrom ALIAS FOR $7;
- pDateTo ALIAS FOR $8;
- pProductCodeName ALIAS FOR $9;
- vEmptyValue character varying(1);
- vAwalTahun character varying(6);
- vAkhirRekap character varying(6);
- vAllId bigint;
- vEmptyId bigint;
- vRoundingMode character varying(5);
- vValutaBuku character varying(5);
- vYearMonth character varying(6);
- vFilterPartner text := '';
- vCurrentYearMonth character varying(6);
- vEndYearMonth character varying(6);
- /**
- * 1. ambil semua so balance invoice di main business unit yang tidak di do receipt
- * jika transaksi itu ada item assembly ambil berdasarkan core product id nya
- * ambil juga harga beli terakhir dari item itu sesuai dengan tahun bulan
- * 2. isi table report
- * *belum selesai ini coi*
- */
- BEGIN
- vEmptyValue := '';
- vAllId := -99;
- vEmptyId := -99;
- vValutaBuku := 'IDR';
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;
- SELECT TO_CHAR(now(), 'YYYYMM') INTO vYearMonth;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vValutaBuku;
- DELETE FROM tr_detail_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tt_report_detail_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tt_input_data WHERE session_id = pSessionId;
- DELETE FROM tt_output_data 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;
- IF (pPartnerId <> vEmptyId) THEN
- vFilterPartner := ' AND A.partner_id = ' || pPartnerId;
- END IF;
- /**
- * 1. ambil semua so balance invoice di main business unit yang tidak di do receipt
- * jika transaksi itu ada item assembly ambil berdasarkan core product id nya
- * ambil juga harga beli terakhir dari item itu sesuai dengan tahun bulan
- */
- EXECUTE 'INSERT INTO tr_detail_sales_gross_profit(
- session_id, tenant_id, ou_id, year_month_date, partner_id, partner_code, partner_name,
- doc_type_id, doc_no, doc_date,
- product_id, curr_code, qty, gross_sell_price, gross_item_amount,
- monthly_price_curr_code, monthly_price_amount
- )
- SELECT $1, A.tenant_id, E.ou_bu_id, SUBSTRING(A.ref_doc_date, 1, 6), A.partner_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id),
- A.ref_doc_type_id, A.ref_doc_no, A.ref_doc_date,
- B.product_id, A.curr_code, A.qty_dlv_so, (A.price_so + COALESCE(D.tax_price, 0)),
- SUM(A.item_amount + f_get_so_balance_invoice_tax_amount_by_unique(A.tenant_id, A.ou_id, A.partner_id, A.ref_doc_type_id, A.ref_id, A.ref_item_id, A.do_receipt_item_id)),
- $7, 0
- FROM sl_so_balance_invoice A
- INNER JOIN sl_do_item B ON A.ref_item_id = B.do_item_id
- INNER JOIN sl_do C ON A.ref_doc_type_id = C.doc_type_id AND C.do_id = A.ref_id AND C.do_id = B.do_id
- INNER JOIN sl_so_item D ON B.ref_id = D.so_item_id
- INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
- INNER JOIN m_product G ON B.tenant_id = G.tenant_id AND B.product_id = G.product_id
- WHERE A.tenant_id = $2
- AND A.ref_doc_date BETWEEN $3 AND $4
- AND E.ou_bu_id = $6 '|| vFilterPartner ||
- ' GROUP BY A.tenant_id, E.ou_bu_id, A.ref_doc_date, A.partner_id, A.ref_doc_type_id, A.ref_doc_no, B.product_id, A.curr_code, A.qty_dlv_so, A.price_so, D.tax_price '
- USING pSessionId, pTenantId, pDateFrom, pDateTo, vEmptyId, pOuId, vEmptyValue;
- ANALYZE tr_detail_sales_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 */
- SELECT MIN(year_month_date), MAX(year_month_date) FROM tr_detail_sales_gross_profit INTO vCurrentYearMonth, vEndYearMonth;
- --Modified by Adrian, Aug 1, 2017
- --monthly_price_amount di-update per year-month
- WHILE vCurrentYearMonth <= vEndYearMonth 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_detail_sales_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;
- PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, vYearMonth, pOuId, pDatetime, pUserId);
- /*sebelum v329
- UPDATE tr_detail_sales_gross_profit
- --SET monthly_price_curr_code = A.curr_code, monthly_price_amount = ROUND(A.amount / A.qty)
- SET monthly_price_curr_code = COALESCE(A.curr_code, tr_detail_sales_gross_profit.curr_code), monthly_price_amount = COALESCE(f_get_gross_price_from_nett_amount(A.amount, A.qty, A.flg_tax_amount, A.tax_percentage, vRoundingMode, f_get_digit_decimal_doc_curr(-99, A.curr_code)), 0)
- FROM pu_monthly_price_product A
- WHERE tr_detail_sales_gross_profit.session_id = pSessionId
- AND tr_detail_sales_gross_profit.product_id = A.product_id
- AND tr_detail_sales_gross_profit.year_month_date = A.year_month_date
- AND tr_detail_sales_gross_profit.ou_id = A.ou_id;*/
- ANALYZE tt_out_latest_purchasing_price_by_date;
- UPDATE tr_detail_sales_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.tenant_id = A.tenant_id
- AND B.doc_date = A.doc_date
- 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_detail_sales_gross_profit;
- /*
- * update nilai harga beli terakhir menggunakan valuta penjualan (gunakan kurs komersial)
- UPDATE tr_detail_sales_gross_profit SET monthly_price_amount = monthly_price_amount * f_commercial_rate(tenant_id, doc_date, monthly_price_curr_code, vValutaBuku), monthly_price_curr_code = vValutaBuku
- WHERE monthly_price_curr_code <> vValutaBuku
- AND session_id = pSessionId AND monthly_price_curr_code <> '';*/
- UPDATE tr_detail_sales_gross_profit SET gross_item_amount = gross_item_amount * f_commercial_rate(tenant_id, doc_date, curr_code, vValutaBuku), curr_code = vValutaBuku
- WHERE curr_code <> vValutaBuku
- AND session_id = pSessionId AND curr_code <> '';
- ANALYZE tr_detail_sales_gross_profit;
- /*
- * pindahkan data tahun bulan yang diminta (sudah direkap per partner)
- */
- INSERT INTO tr_current_sales_gross_profit (
- session_id, tenant_id, ou_id, product_id, partner_id, partner_code, partner_name, curr_code,
- current_sales_amount, current_monthly_price_amount, current_gross_profit, qty
- )
- SELECT pSessionId, tenant_id, ou_id, product_id, partner_id, partner_code, partner_name, curr_code,
- SUM(gross_item_amount) AS current_sales_amount, SUM(monthly_price_amount * qty) AS current_monthly_price_amount, 0, SUM(qty)
- FROM tr_detail_sales_gross_profit
- WHERE session_id = pSessionId
- --AND year_month_date = vYearMonth
- AND doc_date BETWEEN pDateFrom AND pDateTo
- GROUP BY tenant_id, ou_id, product_id, partner_id, partner_code, partner_name, curr_code;
- ANALYZE tr_current_sales_gross_profit;
- /*
- * update data gross profit untuk bulan yang diminta
- */
- UPDATE tr_current_sales_gross_profit SET current_gross_profit = current_sales_amount - current_monthly_price_amount
- WHERE session_id = pSessionId;
- ANALYZE tr_detail_sales_gross_profit;
- /**
- * isi table report
- */
- INSERT INTO tt_report_detail_sales_gross_profit(session_id, tenant_id,
- ou_id, partner_id, partner_code, partner_name,
- product_id, curr_code, gross_item_amount, margin, percentage)
- SELECT A.session_id, A.tenant_id,
- A.ou_id, A.partner_id, A.partner_code, A.partner_name,
- A.product_id, A.curr_code, SUM(A.gross_item_amount), 0, 0
- FROM tr_detail_sales_gross_profit A
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, A.tenant_id, A.ou_id, A.product_id, A.partner_id, A.partner_code, A.partner_name, A.curr_code;
- ANALYZE tt_report_detail_sales_gross_profit;
- /*
- * return header
- */
- Open pRefHeader FOR
- SELECT fullName AS full_name, f_get_ou_name(pOuId) AS ou_name,
- CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pPartnerId) END AS partner_name,
- CASE WHEN pProductCodeName = vEmptyValue THEN '(All)' ELSE ('%'||pProductCodeName||'%') END AS product_code_name,
- pDateFrom AS date_from, pDateTo AS date_to,
- f_get_username(pUserId) AS username, pDatetime AS datetime
- FROM t_user
- WHERE user_id = pUserId;
- RETURN NEXT pRefHeader;
- ANALYZE tr_current_sales_gross_profit;
- INSERT INTO tt_input_data
- (session_id, id_data, data_group_1,
- data_group_2, data_group_3, data_group_4, data_group_5, data_measure)
- SELECT pSessionId, 'SALDO', A.curr_code,
- A.qty::text, B.product_id::text, A.current_sales_amount::text, A.current_gross_profit::text, A.current_gross_profit::numeric
- FROM tr_current_sales_gross_profit A
- INNER JOIN m_product B ON A.product_id = B.product_id
- WHERE A.session_id = pSessionId;
- PERFORM f_agregate_percentage(pSessionId, 'SALDO', ';', 3, 1, 2);
- IF TRIM(pProductCodeName) <> vEmptyValue THEN
- Open pRefDetail FOR
- SELECT A.data_group_1 AS curr_code,
- A.data_group_2::numeric AS qty,
- f_get_product_code(A.data_group_3::bigint) AS product_code,
- f_get_product_name(A.data_group_3::bigint) AS product_name,
- A.data_group_4::numeric AS sell_amount,
- A.data_group_5::numeric AS margin,
- round(A.percentage_measure, 2) AS percentage
- FROM tt_output_data A
- WHERE A.session_id = pSessionId
- AND UPPER(A.data_group_2) LIKE '%' || UPPER(pProductCodeName) || '%' OR UPPER(A.data_group_3) LIKE '%' || UPPER(pProductCodeName) || '%'
- ORDER BY curr_code, percentage, sell_amount, margin, product_code;
- ELSE
- Open pRefDetail FOR
- SELECT A.data_group_1 AS curr_code,
- A.data_group_2::numeric AS qty,
- f_get_product_code(A.data_group_3::bigint) AS product_code,
- f_get_product_name(A.data_group_3::bigint) AS product_name,
- A.data_group_4::numeric AS sell_amount,
- A.data_group_5::numeric AS margin,
- round(A.percentage_measure, 2) AS percentage
- FROM tt_output_data A
- WHERE A.session_id = pSessionId
- ORDER BY curr_code, percentage, sell_amount, margin, product_code;
- END IF;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_detail_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tt_report_detail_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tt_input_data WHERE session_id = pSessionId;
- DELETE FROM tt_output_data WHERE session_id = pSessionId;
- DELETE FROM tr_current_sales_gross_profit 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