Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION rpt.r_report_kartu_stock(character varying, bigint, bigint, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pRecordOwnerId ALIAS FOR $2;
- pProductId ALIAS FOR $3;
- pStartDate ALIAS FOR $4;
- pEndDate ALIAS FOR $5;
- vEmptyId bigint := -99;
- vBeginningQty bigint := 0;
- vEndQty bigint := 0;
- vCalculateQty bigint := 0;
- vEmpty character varying := '';
- vNo character varying := 'N';
- vYes character varying := 'Y';
- vYearMonth character varying := '';
- vFilerProduct character varying := '';
- vStartYearMonth character varying := '';
- vEndYearMonth character varying := '';
- vCursorRow tt_summary_trx_document%ROWTYPE;
- BEGIN
- DELETE FROM tt_monthly_summary_qty WHERE session_id = pSessionId;
- DELETE FROM tt_summary_trx_document WHERE session_id = pSessionId;
- SELECT substring(pStartDate, 1, 6) INTO vStartYearMonth;
- SELECT substring(pEndDate, 1, 6) INTO vEndYearMonth;
- IF pProductId <> vEmptyId THEN
- vFilerProduct := ' AND A.product_id = '''|| pProductId ||'''';
- END IF;
- OPEN pRefHeader FOR
- SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pStartDate AS date_from, pEndDate AS date_to,
- CASE WHEN pProductId <> vEmptyId THEN f_get_product_code(pProductId) ELSE 'All' END AS product_code,
- CASE WHEN pProductId <> vEmptyId THEN f_get_product_name(pProductId) ELSE 'All' END AS product_name;
- RETURN NEXT pRefHeader;
- -- Stock summary beginning qty per montlhy
- EXECUTE '
- INSERT INTO tt_monthly_summary_qty(
- session_id, year_month, record_owner_id, beginning_qty
- )
- SELECT $4, A.year_month, A.record_owner_id, COALESCE(SUM(A.qty), 0)
- FROM in_summary_monthly_qty A
- WHERE A.record_owner_id = $1
- AND A.year_month >= $2
- AND A.year_month <= $3
- ' || vFilerProduct ||'
- GROUP BY A.year_month, A.record_owner_id'
- USING pRecordOwnerId, vStartYearMonth, vEndYearMonth, pSessionId;
- -- Ambil Semua dokumen transaksi yang ada dalam jangka periode
- EXECUTE '
- INSERT INTO tt_summary_trx_document (
- session_id, record_owner_id, doc_no, doc_date, doc_desc, qty
- )
- SELECT $4, A.record_owner_id, A.ref_doc_no, A.ref_doc_date, f_get_doc_desc(A.ref_doc_type_id), COALESCE(SUM(A.qty), 0)
- FROM in_log_product_balance_stock A
- WHERE A.record_owner_id = $1
- AND A.ref_doc_date >= $2
- AND A.ref_doc_date <= $3
- ' || vFilerProduct ||'
- GROUP BY A.record_owner_id, A.ref_doc_no, A.ref_doc_date, A.ref_doc_type_id
- ORDER BY A.ref_doc_date ASC'
- USING pRecordOwnerId, pStartDate, pEndDate, pSessionId;
- FOR vCursorRow IN
- SELECT * FROM tt_summary_trx_document WHERE session_id = pSessionId
- LOOP
- SELECT substring(vCursorRow.doc_date, 1, 6) INTO vYearMonth;
- SELECT beginning_qty INTO vBeginningQty FROM tt_monthly_summary_qty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId AND year_month = vYearMonth;
- vCalculateQty := vBeginningQty+vCursorRow.qty;
- INSERT INTO tt_sumary_trx_log_product_balance(
- session_id, doc_no, doc_date, doc_desc, beginning_qty,
- trx_qty, end_qty
- ) VALUES (pSessionId, vCursorRow.doc_no, vCursorRow.doc_date, vCursorRow.doc_desc, vBeginningQty,
- vCursorRow.qty, vCalculateQty);
- UPDATE tt_monthly_summary_qty SET beginning_qty = vCalculateQty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId AND year_month = vYearMonth;
- END LOOP;
- OPEN pRefDetail FOR
- SELECT *
- FROM tt_sumary_trx_log_product_balance
- WHERE session_id = pSessionId;
- RETURN NEXT pRefDetail;
- DELETE FROM tt_monthly_summary_qty WHERE session_id = pSessionId;
- DELETE FROM tt_summary_trx_document 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