Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION rpt.r_report_sales(character varying, bigint, character varying, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pRecordOwnerId ALIAS FOR $2;
- pSalesman ALIAS FOR $3;
- pDateFrom ALIAS FOR $4;
- pDateTo ALIAS FOR $5;
- pCustomer ALIAS FOR $6;
- vEmpty CHARACTER VARYING := '';
- vNo CHARACTER VARYING := 'N';
- vYes CHARACTER VARYING := 'Y';
- vFilterSalesman character varying := '';
- vFilterCustomer character varying := '';
- vPosDocTypeId BIGINT := 390;
- vVoidPosDocTypeId BIGINT := 391;
- vEmptyId BIGINT := -99;
- BEGIN
- DELETE FROM tt_summary_trx_pos WHERE session_id = pSessionId;
- IF pSalesman <> vEmpty THEN
- vFilterSalesman := ' AND UPPER(Z.salesman_name) LIKE UPPER(''%'|| pSalesman ||'%'')';
- END IF;
- IF pCustomer <> vEmpty THEN
- vFilterCustomer := ' AND ( UPPER(Z.customer_code) LIKE UPPER(''%'|| pCustomer ||'%'') OR UPPER(Z.customer_name) LIKE UPPER(''%'|| pCustomer ||'%'') )';
- END IF;
- Open pRefHeader FOR
- SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pDateFrom AS date_from, pDateTo AS date_to;
- RETURN NEXT pRefHeader;
- /**
- * Ambil data penjualan yang type dokumen nya
- * 1. POS,Koreksi POS (390)
- */
- INSERT INTO tt_summary_trx_pos (
- session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
- customer_id, salesman_id, product_id, total_trx_qty
- )
- SELECT pSessionId, C.pos_id, C.doc_type_id, A.record_owner_id, A.ref_doc_no, A.ref_doc_date, substring(A.ref_doc_date, 1, 6),
- C.customer_id, C.salesman_id, A.product_id, A.qty
- FROM in_log_product_balance_stock A
- INNER JOIN trx_pos C ON A.ref_id = C.pos_id
- AND A.ref_doc_type_id = C.doc_type_id
- WHERE A.record_owner_id = pRecordOwnerId
- AND A.ref_doc_type_id IN (vPosDocTypeId)
- AND A.ref_doc_date BETWEEN pDateFrom AND pDateTo;
- /**
- * Ambil Summary produk yg jumlah trx qty <> 0
- */
- INSERT INTO tt_rekap_summary_trx_pos_item (
- session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
- customer_id, salesman_id, product_id, total_trx_qty
- )
- SELECT session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
- customer_id, salesman_id, product_id, SUM(abs(total_trx_qty)) AS total_qty
- FROM tt_summary_trx_pos
- WHERE session_id = pSessionId
- GROUP BY session_id, doc_id, doc_type_id, record_owner_id, doc_no, doc_date, year_month,
- customer_id, salesman_id, product_id
- HAVING SUM(total_trx_qty) <> 0;
- /**
- * Hapus Dokumen POS yang dilakukan void
- */
- DELETE FROM tt_rekap_summary_trx_pos_item Z WHERE EXISTS (SELECT 1 FROM trx_pos A WHERE A.doc_type_id = vVoidPosDocTypeId AND A.ref_id = Z.doc_id);
- OPEN pRefDetail FOR
- EXECUTE '
- WITH TEMP AS (
- SELECT A.doc_no, A.doc_date, f_get_customer_code(A.customer_id) AS customer_code, f_get_customer_name(A.customer_id) AS customer_name,
- f_get_salesman_name(A.salesman_id) AS salesman_name, SUM((B.unit_sell_price-B.item_discount_amount)*A.total_trx_qty) AS total_trx_amount,
- COALESCE(C.due_date, $2) AS due_date, A.record_owner_id, COALESCE(SUM((D.amount_total/D.qty_total)*A.total_trx_qty), 0) AS hpp_avg
- FROM tt_rekap_summary_trx_pos_item A
- INNER JOIN trx_pos_item B ON A.doc_id = B.pos_id
- AND A.product_id = B.product_id
- LEFT JOIN fi_invoice_ar C ON A.doc_id = C.ref_id
- AND A.doc_type_id = C.ref_doc_type_id
- LEFT JOIN in_summary_cogs D ON A.record_owner_id = D.record_owner_id
- AND A.product_id = D.product_id
- AND A.year_month = D.year_month
- WHERE session_id = $1
- GROUP BY A.doc_no, A.doc_date, C.due_date, A.customer_id, A.salesman_id, A.record_owner_id
- )
- SELECT *, total_trx_amount-hpp_avg AS total_laba_rugi
- FROM TEMP Z
- WHERE true
- '|| vFilterSalesman || vFilterCustomer ||''
- USING pSessionId, vEmpty;
- RETURN NEXT pRefDetail;
- DELETE FROM tt_summary_trx_pos WHERE session_id = pSessionId;
- DELETE FROM tt_rekap_summary_trx_pos_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- SET search_path = public, mstr, ptg, htg, pb, pj, batch
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement