Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Adrian, Aug 18, 2017
- * Report Analysis Sales Gross Profit with Monthly Average
- * Dibuat berdasarkan Report Analysis Sales Gross Profit,
- * tetapi harga beli diambil dari in_summary_monthly_cogs
- */
- CREATE OR REPLACE FUNCTION r_analysis_sales_gross_profit_with_monthly_average(character varying, bigint, character varying, bigint, character varying, character varying, character varying, bigint, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pYearMonthDate ALIAS FOR $3;
- pUserId ALIAS FOR $4;
- pPartnerName ALIAS FOR $5;
- pTglAwal ALIAS FOR $6;
- pTglAkhir ALIAS FOR $7;
- pOuId ALIAS FOR $8;
- pDatetime 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);
- vCurrentYearMonth character varying(6);
- vDocTypeIdSaldoAwal bigint;
- BEGIN
- vEmptyValue := '';
- vAllId := -99;
- vEmptyId := -99;
- vAwalTahun := SUBSTRING(pYearMonthDate, 1, 4) || '01';
- vValutaBuku := 'IDR';
- vDocTypeIdSaldoAwal = -99;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vValutaBuku;
- IF SUBSTRING(pYearMonthDate, 5, 2) = '01' THEN
- vAkhirRekap := pYearMonthDate;
- ELSE
- vAkhirRekap := TO_CHAR(to_date(pYearMonthDate, 'YYYYMM') - interval '1 month', 'YYYYMM');
- END IF;
- DELETE FROM tr_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_current_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_rekap_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_sales_gross_profit_for_output WHERE session_id = pSessionId;
- /*
- * 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
- * v341, kolom nett_amount_item dan nett_sell_price sudah tidak nett lagi karena sudah ditambahkan tax
- */
- INSERT INTO tr_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, nett_sell_price,
- nett_amount_item,
- monthly_price_curr_code, monthly_price_amount
- )
- SELECT pSessionId, 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)),
- '', 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
- WHERE A.tenant_id = pTenantId
- AND SUBSTRING(A.ref_doc_date, 1, 6) BETWEEN vAwalTahun AND pYearMonthDate
- AND E.ou_bu_id = pOuId
- 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;
- ANALYZE tr_sales_gross_profit;
- vCurrentYearMonth := vAwalTahun;
- UPDATE tr_sales_gross_profit B
- SET monthly_price_curr_code = COALESCE(A.curr_code, B.curr_code),
- monthly_price_amount = (CASE WHEN COALESCE(qty_total, 0) = 0 OR COALESCE(amount_total, 0) = 0 THEN
- 0
- ELSE
- COALESCE(A.amount_total/A.qty_total, 0)
- END)
- FROM in_summary_monthly_cogs A
- INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_id
- WHERE B.tenant_id = A.tenant_id
- AND B.product_id = A.product_id
- AND B.year_month_date = A.date_year_month
- AND C.ou_id = B.ou_id;
- ANALYZE tr_sales_gross_profit;
- /*
- * update nilai harga beli terakhir menggunakan valuta penjualan (gunakan kurs komersial)
- UPDATE tr_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_sales_gross_profit SET nett_amount_item = nett_amount_item * f_commercial_rate(tenant_id, doc_date, curr_code, vValutaBuku), curr_code = vValutaBuku
- WHERE curr_code <> vValutaBuku
- AND session_id = pSessionId AND curr_code <> '';
- /*
- * pindahkan data tahun bulan yang diminta (sudah direkap per partner)
- */
- INSERT INTO tr_current_sales_gross_profit (
- session_id, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
- current_sales_amount, current_monthly_price_amount, current_gross_profit
- )
- SELECT pSessionId, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
- SUM(nett_amount_item) AS current_sales_amount, SUM(monthly_price_amount * qty) AS current_monthly_price_amount, 0
- FROM tr_sales_gross_profit
- WHERE session_id = pSessionId
- AND year_month_date = pYearMonthDate
- AND doc_date BETWEEN pTglAwal AND pTglAkhir
- GROUP BY tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code;
- ANALYZE tr_current_sales_gross_profit;
- /*
- * pindahkan data sebelum tahun bulan yang diminta (sudah direkap per partner)
- */
- INSERT INTO tr_rekap_sales_gross_profit (
- session_id, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
- rekap_sales_amount, rekap_monthly_price_amount, rekap_gross_profit
- )
- SELECT pSessionId, tenant_id, ou_id, partner_id, partner_code, partner_name, curr_code,
- SUM(nett_amount_item) AS rekap_sales_amount, SUM(monthly_price_amount * qty) AS rekap_monthly_price_amount, 0
- FROM tr_sales_gross_profit
- WHERE session_id = pSessionId
- AND year_month_date <> pYearMonthDate
- GROUP BY tenant_id, ou_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_rekap_sales_gross_profit;
- /*
- * update data gross profit untuk sebelum bulan yang diminta
- */
- UPDATE tr_rekap_sales_gross_profit SET rekap_gross_profit = rekap_sales_amount - rekap_monthly_price_amount
- WHERE session_id = pSessionId;
- ANALYZE tr_current_sales_gross_profit;
- INSERT INTO tr_sales_gross_profit_for_output
- (session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
- curr_code,
- current_sales_amount, current_gross_profit,
- rekap_sales_amount, rekap_gross_profit)
- SELECT pSessionId, A.tenant_id, ou_id, A.partner_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id),
- A.curr_code,
- A.current_sales_amount, A.current_gross_profit,
- 0, 0
- FROM tr_current_sales_gross_profit A
- WHERE A.session_id = pSessionId;
- ANALYZE tr_rekap_sales_gross_profit;
- UPDATE tr_sales_gross_profit_for_output
- SET rekap_sales_amount = A.rekap_sales_amount, rekap_gross_profit = A.rekap_gross_profit
- FROM tr_rekap_sales_gross_profit A
- WHERE A.session_id = pSessionId AND tr_sales_gross_profit_for_output.partner_id = A.partner_id;
- ANALYZE tr_rekap_sales_gross_profit;
- INSERT INTO tr_sales_gross_profit_for_output
- (session_id, tenant_id, ou_id, partner_id, partner_code, partner_name,
- curr_code,
- current_sales_amount, current_gross_profit,
- rekap_sales_amount, rekap_gross_profit)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.partner_id, f_get_partner_code(A.partner_id), f_get_partner_name(A.partner_id),
- A.curr_code,
- 0, 0,
- A.rekap_sales_amount, A.rekap_gross_profit
- FROM tr_rekap_sales_gross_profit A
- WHERE A.session_id = pSessionId
- AND A.partner_id NOT IN ( SELECT B.partner_id FROM tr_sales_gross_profit_for_output B WHERE B.session_id = pSessionId );
- ANALYZE tr_sales_gross_profit_for_output;
- /*
- * return header
- */
- Open pRefHeader FOR
- SELECT A.fullname AS username, B.ou_name AS ou_name, pYearMonthDate AS current_month_year, vAwalTahun AS start_month_year, vAkhirRekap AS end_month_year,
- vValutaBuku AS valuta_buku, pDatetime AS datetime
- 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(pPartnerName) <> vEmptyValue THEN
- Open pRefDetail FOR
- SELECT A.partner_id AS partner_id, A.partner_code AS partner_code, A.partner_name AS partner_name,
- A.curr_code AS curr_code,
- A.current_sales_amount AS current_sales, A.current_gross_profit AS current_gross_profit,
- A.rekap_sales_amount AS before_sales, A.rekap_gross_profit AS before_gross_profit
- FROM tr_sales_gross_profit_for_output A
- WHERE A.session_id = pSessionId
- AND UPPER(A.partner_name) LIKE '%' || UPPER(pPartnerName) || '%'
- ORDER BY curr_code, partner_name;
- ELSE
- Open pRefDetail FOR
- SELECT A.partner_id AS partner_id, A.partner_code AS partner_code, A.partner_name AS partner_name,
- A.curr_code AS curr_code,
- A.current_sales_amount AS current_sales, A.current_gross_profit AS current_gross_profit,
- A.rekap_sales_amount AS before_sales, A.rekap_gross_profit AS before_gross_profit
- FROM tr_sales_gross_profit_for_output A
- WHERE A.session_id = pSessionId
- ORDER BY curr_code, partner_name;
- END IF;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_current_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_rekap_sales_gross_profit WHERE session_id = pSessionId;
- DELETE FROM tr_sales_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