Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_daily_sales(character varying, bigint, bigint, character varying, character varying, 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;
- pVehicleBrand ALIAS FOR $4;
- pVehicleType ALIAS FOR $5;
- pYearMade ALIAS FOR $6;
- pDateFrom ALIAS FOR $7;
- pDateTo ALIAS FOR $8;
- vEmptyValue character varying(1);
- vEmptyId bigint;
- vAllId bigint;
- vFilterOuId text := '';
- vFilterVehicleBrand text := '';
- vFilterVehicleType text := '';
- vFilterYearMade text := '';
- vParentOuId bigint;
- vYearMonth character varying(6);
- vDatetime character varying(14);
- vMaxYearMonth character varying(6);
- BEGIN
- vEmptyValue := '';
- vEmptyId := -99;
- vAllId := -99;
- DELETE FROM tr_daily_sales_report 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;
- vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
- SELECT TO_CHAR(now(), 'YYYYMM') INTO vYearMonth;
- SELECT TO_CHAR(now(), 'YYYYMMDDHH24MISS') INTO vDatetime;
- SELECT MAX(year_month_date) INTO vMaxYearMonth FROM in_product_standard_cogs;
- IF pOuId <> vAllId THEN
- vFilterOuId := ' AND A.ou_id = ' || pOuId;
- END IF;
- IF pVehicleBrand <> vEmptyValue THEN
- vFilterVehicleBrand := ' AND D.vehicle_brand = ''' || pVehicleBrand || '''';
- END IF;
- IF pVehicleType <> vEmptyValue THEN
- vFilterVehicleType := ' AND D.vehicle_type = ''' || pVehicleType || '''';
- END IF;
- IF pYearMade <> vEmptyValue THEN
- vFilterYearMade := ' AND D.year_made = ''' || pYearMade '''';
- END IF;
- -- produk non nempil
- EXECUTE '
- WITH summed_i_trx_pos_non_cash_payment AS (
- SELECT process_no, tenant_id, trx_pos_id, SUM(COALESCE(payment_amount, 0)) AS payment_amount, COALESCE(curr_payment_code, '') AS curr_payment_code
- FROM i_trx_pos_non_cash_payment
- GROUP BY process_no, tenant_id, trx_pos_id, curr_payment_code
- )
- INSERT INTO tr_daily_sales_report (
- session_id, ou_name, year,
- month, date, doc_no, vehicle_brand,
- vehicle_type, year_made, customer_name, product_name,
- ctgr_product_name, qty, curr_code,
- modal_price, gross_sell_price, discount_amount,
- nett_sell_price, curr_payment_code_cash, payment_amount_cash,
- curr_payment_code_non_cash, payment_amount_non_cash,
- curr_under_payment, under_payment_amount,
- tenant_id, ou_id, doc_date, product_id, line_no)
- SELECT $1, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
- SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, D.vehicle_brand,
- D.vehicle_type, D.year_made, COALESCE(E.partner_name, ''UMUM'') AS customer_name, f_get_product_name(G.product_id) AS product_name,
- f_get_ctgr_product_name(G.ctgr_product_id) AS ctgr_product_name, F.qty, F.curr_code,
- 0, F.gross_sell_price, COALESCE(F.discount_amount, 0) * -1 AS discount_amount,
- F.nett_sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN COALESCE(H.payment_amount, 0) ELSE 0 END AS payment_amount_cash,
- I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN I.payment_amount ELSE 0 END AS payment_amount_non_cash,
- COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(j.under_payment_amount, 0) ELSE 0 END AS under_payment_amount,
- A.tenant_id, B.ou_id, A.doc_date, G.product_id, F.line_no
- FROM i_trx_pos A
- INNER JOIN t_ou B on A.ou_id = B.ou_id
- INNER JOIN i_trx_pos_ext C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
- INNER JOIN m_vehicle_customer_own D on C.vehicle_code = D.vehicle_code
- LEFT OUTER JOIN m_partner E on C.partner_id = E.partner_id
- INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
- INNER JOIN m_product G on F.product_id = G.product_id
- LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and B.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
- LEFT OUTER JOIN i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
- LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
- LEFT JOIN t_combo_value K ON D.vehicle_brand = K.code AND K.combo_id = ''VEHICLEBRAND''
- LEFT JOIN t_combo_value L ON D.vehicle_type = L.code AND L.combo_id = ''VEHICLETYPE''
- WHERE A.status <> ''V''
- AND A.tenant_id = $3
- AND A.doc_date BETWEEN $4 AND $5 ' ||
- vFilterOuId ||
- vFilterVehicleBrand ||
- vFilterVehicleType ||
- vFilterYearMade
- USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo;
- ANALYZE tr_daily_sales_report;
- 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_daily_sales_report A
- INNER JOIN m_ou_structure B ON B.ou_id = A.ou_id
- WHERE A.tenant_id = pTenantId
- AND A.session_id = pSessionId
- GROUP BY B.ou_bu_id, A.doc_date, A.product_id;
- PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, vYearMonth, B.ou_bu_id, vDatetime, -1)
- FROM tr_daily_sales_report A
- INNER JOIN m_ou_structure B ON B.ou_id = A.ou_id
- GROUP BY B.ou_bu_id;
- ANALYZE tt_out_latest_purchasing_price_by_date;
- UPDATE tr_daily_sales_report B
- SET modal_price = 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 A.session_id = B.session_id
- AND B.product_id = A.product_id
- AND B.tenant_id = A.tenant_id
- AND B.doc_date = A.doc_date
- AND C.ou_id = B.ou_id;
- WITH in_product_standard_cogs_temp AS (
- SELECT D.tenant_id, D.ou_id, MAX(D.year_month_date) AS year_month_date, D.product_id, D.amount
- FROM in_product_standard_cogs D
- INNER JOIN m_ou_structure E ON E.ou_bu_id = D.ou_id
- GROUP BY D.tenant_id, D.ou_id, D.product_id, D.amount
- )
- UPDATE tr_daily_sales_report B
- SET modal_price = COALESCE(A.amount, 0)
- FROM in_product_standard_cogs_temp A
- INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_id
- WHERE B.session_id = pSessionId
- AND B.product_id = A.product_id
- AND B.tenant_id = A.tenant_id
- AND A.year_month_date <= SUBSTRING(B.doc_date, 1, 6)
- AND C.ou_id = B.ou_id
- AND B.modal_price = 0;
- -- produk nempil
- /**
- * mod by Didit, 2 des 2016
- * gunakan distinct on, karena ada produk nempil yg kode produk nya sama tp beda di trx_nempil_barang_item_id sehingga
- * produk nempil akan muncul lebih dari satu kali dan menyebabkan perhitungan nett amount akan salah
- */
- EXECUTE 'INSERT INTO tr_daily_sales_report (
- session_id, ou_name, year,
- month, date, doc_no, vehicle_brand,
- vehicle_type, year_made, customer_name, product_name,
- ctgr_product_name, qty, curr_code,
- modal_price, gross_sell_price, discount_amount,
- nett_sell_price, curr_payment_code_cash, payment_amount_cash,
- curr_payment_code_non_cash, payment_amount_non_cash,
- curr_under_payment, under_payment_amount,
- tenant_id, ou_id, doc_date, product_id, line_no)
- SELECT $1, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
- SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, D.vehicle_brand,
- D.vehicle_type, D.year_made, COALESCE(E.partner_name, ''UMUM'') AS customer_name, G.product_name,
- $2 AS ctgr_product_name, F.qty, F.curr_code,
- 0 AS modal_price, F.gross_sell_price, 0 AS discount_amount,
- F.gross_sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN COALESCE(H.payment_amount, 0) ELSE 0 END AS payment_amount_cash,
- COALESCE(I.curr_payment_code, $2) AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN COALESCE(I.payment_amount, 0) ELSE 0 END AS payment_amount_non_cash,
- COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(J.under_payment_amount, 0) ELSE 0 END AS under_payment_amount,
- A.tenant_id, B.ou_id, A.doc_date, -99, F.line_no
- FROM i_trx_pos A
- INNER JOIN t_ou B on A.ou_id = B.ou_id
- INNER JOIN i_trx_pos_ext C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
- INNER JOIN m_vehicle_customer_own D on C.vehicle_code = D.vehicle_code
- LEFT OUTER JOIN m_partner E on C.partner_id = E.partner_id
- INNER JOIN i_trx_pos_item_nempil_barang F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
- INNER JOIN in_nempil_barang_balance_stock G on F.product_code = G.product_code AND A.ou_id = G.ou_id
- LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and A.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
- LEFT OUTER JOIN i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
- LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
- LEFT JOIN t_combo_value K ON D.vehicle_brand = K.code AND K.combo_id = ''VEHICLEBRAND''
- LEFT JOIN t_combo_value L ON D.vehicle_type = L.code AND L.combo_id = ''VEHICLETYPE''
- WHERE A.status <> ''V''
- AND A.tenant_id = $3
- AND A.doc_date BETWEEN $4 AND $5 ' ||
- vFilterOuId ||
- vFilterVehicleBrand ||
- vFilterVehicleType ||
- vFilterYearMade
- USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo;
- /**
- * mod by Didit, 2 Des 2016
- * cash, non cash, dan piutang hanya terisi untuk baris yg pertama
- * selain baris pertama di isi 0
- */
- UPDATE tr_daily_sales_report A
- SET payment_amount_cash = 0,
- payment_amount_non_cash = 0,
- under_payment_amount = 0
- WHERE A.product_id = -99
- AND A.line_no = 1
- AND EXISTS(
- SELECT 1 FROM tr_daily_sales_report B WHERE A.doc_no = B.doc_no AND B.product_id <> -99 AND B.line_no = 1
- );
- -- nilai rounding
- EXECUTE 'INSERT INTO tr_daily_sales_report (
- session_id, ou_name, year,
- month, date, doc_no, vehicle_brand,
- vehicle_type, year_made, customer_name, product_name,
- ctgr_product_name, qty, curr_code,
- modal_price, gross_sell_price, discount_amount,
- nett_sell_price, curr_payment_code_cash, payment_amount_cash,
- curr_payment_code_non_cash, payment_amount_non_cash,
- curr_under_payment, under_payment_amount,
- tenant_id, ou_id, doc_date, product_id, line_no)
- SELECT $1, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
- SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, D.vehicle_brand,
- D.vehicle_type, D.year_made, COALESCE(E.partner_name, ''UMUM'') AS customer_name, ''ROUNDING'' AS product_name,
- ''ROUNDING'' AS ctgr_product_name, 1, 0,
- 0, 0, 0,
- A.rounding_amount, COALESCE(A.curr_code, $2) AS curr_payment_code_cash, CASE WHEN A.rounding_amount < 0 THEN A.rounding_amount ELSE 0 END AS payment_amount_cash,
- COALESCE(A.curr_code, $2) AS curr_payment_code_non_cash, 0 AS payment_amount_non_cash,
- COALESCE(A.curr_code, $2) AS curr_under_payment, 0 AS under_payment_amount,
- A.tenant_id, B.ou_id, A.doc_date, -99, -1
- FROM i_trx_pos A
- INNER JOIN t_ou B on A.ou_id = B.ou_id
- INNER JOIN i_trx_pos_ext C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
- INNER JOIN m_vehicle_customer_own D on C.vehicle_code = D.vehicle_code
- LEFT OUTER JOIN m_partner E on C.partner_id = E.partner_id
- WHERE A.status <> ''V''
- AND A.tenant_id = $3
- AND A.doc_date BETWEEN $4 AND $5
- AND A.rounding_amount <> 0 ' ||
- vFilterOuId ||
- vFilterVehicleBrand ||
- vFilterVehicleType ||
- vFilterYearMade
- USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo;
- Open pRefHeader FOR
- SELECT pTenantId;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT ou_name, year, month, date, doc_no, vehicle_brand, vehicle_type, year_made, customer_name, product_name, ctgr_product_name, qty,
- curr_code, modal_price, gross_sell_price, discount_amount, nett_sell_price, curr_payment_code_cash, payment_amount_cash,
- curr_payment_code_non_cash, payment_amount_non_cash, curr_under_payment, under_payment_amount
- FROM tr_daily_sales_report
- WHERE session_id = pSessionId
- ORDER BY ou_name, year, month, date, doc_no, line_no;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_daily_sales_report 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