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';
- pRefSummary REFCURSOR := 'refSummary';
- pSessionId ALIAS FOR $1;
- pRecordOwnerId ALIAS FOR $2;
- pProductId ALIAS FOR $3;
- pDateFrom ALIAS FOR $4;
- pDateTo 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 := '';
- vLastYearMonth character varying := '';
- vNextYearMonth character varying := '';
- vSaldoDateFrom character varying := '';
- vCursorRow tt_summary_trx_document%ROWTYPE;
- BEGIN
- DELETE FROM tt_summary_qty WHERE session_id = pSessionId;
- DELETE FROM tt_summary_trx_document WHERE session_id = pSessionId;
- OPEN pRefHeader FOR
- SELECT f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, pDateFrom AS date_from, pDateTo AS date_to,
- f_get_product_code(pProductId) AS product_code, f_get_product_name(pProductId) AS product_name;
- RETURN NEXT pRefHeader;
- /**
- * cari terakhir bulan tahun terakhir tutup bulan
- */
- SELECT COALESCE(MAX(year_month), ' ') INTO vLastYearMonth
- FROM m_admin_process_ledger
- WHERE record_owner_id = pRecordOwnerId AND
- flag_process = vYes AND
- ledger_code = 'INV' AND
- year_month < substring(pDateFrom,1,6);
- SELECT TO_CHAR(TO_DATE(vLastYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
- vSaldoDateFrom := vNextYearMonth||'01';
- /**
- * 1. Ambil qty dari in_summary_monthly_qty berdasrkan (vNextYearMonth)
- * 2. Init Saldo awal dari log product balance stock berdasrkan awal tahun bulan dari terakhir tutup bulan
- */
- EXECUTE '
- WITH TEMP AS (
- SELECT $1 AS session_id, A.record_owner_id, COALESCE(SUM(A.qty), 0) AS qty
- FROM in_summary_monthly_qty A
- WHERE A.year_month = $6
- AND A.record_owner_id = $2
- AND A.product_id = $5
- GROUP BY A.record_owner_id, A.product_id
- UNION
- SELECT $1 AS session_id, record_owner_id, COALESCE(SUM(qty), 0) AS qty
- FROM in_log_product_balance_stock
- WHERE record_owner_id = $2
- AND ref_doc_date >= $3
- AND ref_doc_date < $4
- AND product_id = $5
- GROUP BY record_owner_id, product_id
- )
- INSERT INTO tt_summary_qty(
- session_id, record_owner_id, beginning_qty, end_qty
- )
- SELECT session_id, record_owner_id, SUM(qty), SUM(qty)
- FROM TEMP
- WHERE session_id = $1
- GROUP BY session_id, record_owner_id'
- USING pSessionId, pRecordOwnerId, vSaldoDateFrom, pDateFrom, pProductId, vNextYearMonth;
- -- 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 BETWEEN $2 AND $3
- AND A.product_id = $5
- 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, pDateFrom, pDateTo, pSessionId, pProductId;
- FOR vCursorRow IN
- SELECT *
- FROM tt_summary_trx_document
- WHERE session_id = pSessionId
- LOOP
- /*
- * Melakukan pengecekan apakah ada saldo awal jika tidak ada lakukan insert saldo awal
- * dari hasil stock akhir
- */
- IF(SELECT 1 FROM tt_summary_qty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId) THEN
- SELECT COALESCE(SUM(end_qty), 0) INTO vBeginningQty FROM tt_summary_qty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId;
- ELSE
- vBeginningQty := 0;
- INSERT INTO tt_summary_qty(
- session_id, record_owner_id, beginning_qty, end_qty
- ) VALUES
- (pSessionId, pRecordOwnerId, 0, 0);
- END IF;
- 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_summary_qty SET end_qty = vCalculateQty WHERE session_id = pSessionId AND record_owner_id = pRecordOwnerId;
- END LOOP;
- OPEN pRefDetail FOR
- SELECT *
- FROM tt_sumary_trx_log_product_balance
- WHERE session_id = pSessionId;
- RETURN NEXT pRefDetail;
- OPEN pRefSummary FOR
- SELECT COALESCE(SUM(beginning_qty), 0) AS beginning_qty, COALESCE(SUM(end_qty), 0) AS end_qty
- FROM tt_summary_qty
- WHERE session_id = pSessionId;
- RETURN NEXT pRefSummary;
- DELETE FROM tt_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