Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION sl_validate_process_so_do_from_pos(character varying,bigint,bigint,bigint,bigint)
- RETURNS CHARACTER VARYING AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pOuId ALIAS FOR $2;
- pTenantId ALIAS FOR $3;
- pEventId ALIAS FOR $4;
- pPosId ALIAS FOR $5;
- vNullLongValue BIGINT := -99;
- vCountFail BIGINT;
- vStatusOk CHARACTER VARYING;
- vStatusFail CHARACTER VARYING;
- vCurrentDateTime CHARACTER VARYING;
- vCurrentDate CHARACTER VARYING;
- vYes CHARACTER VARYING;
- vNo CHARACTER VARYING;
- vEmptyValue CHARACTER VARYING;
- BEGIN
- vStatusOk := 'OK';
- vStatusFail := 'FAIL';
- vYes := 'Y';
- vNo := 'N';
- vEmptyValue := '';
- SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;
- SELECT to_char(CURRENT_DATE, 'YYYYMMDD') INTO vCurrentDate;
- /*
- * Delete table temporary
- */
- DELETE FROM tt_sl_so_closing_event_pos WHERE tenant_id = pTenantId AND ou_id = pOuId AND event_id = pEventId AND session_id = pSessionId;
- IF (SELECT COUNT(1) FROM m_event WHERE end_date < vCurrentDate and event_id = pEventId) > 0 THEN
- RETURN 'cannot.be.processed.because.the.date.is.now.smaller.than.the.date.of.the.event.ends';
- END IF;
- /*
- * Insert ke table temporary
- */
- INSERT INTO tt_sl_so_closing_event_pos(
- session_id, tenant_id, ou_id, event_id, pos_id, line_no, product_id,
- product_code, product_name, qty_pos, uom_code, price_per_unit, sub_total,
- status, message)
- SELECT pSessionId,pTenantId,pOuId,pEventId,B.pos_id,ROW_NUMBER() OVER (ORDER BY A.product_id),A.product_id,
- A.product_code_correction,A.product_name_correction,SUM(A.qty_correction) AS qty_pos,A.uom_code,A.price_per_unit_correction,SUM(A.sub_total_correction) AS sub_total,
- vEmptyValue AS status,vEmptyValue AS message
- FROM sl_pos_item A
- INNER JOIN sl_pos B ON A.pos_id = B.pos_id
- INNER JOIN m_event C ON B.event_id = C.event_id
- WHERE B.tenant_id = pTenantId
- AND B.ou_id = pOuId
- AND C.event_id = pEventId
- AND B.pos_id = pPosId
- AND A.flg_correction = vYes
- AND C.active = vYes
- GROUP BY B.pos_id,A.product_code_correction,A.product_name_correction,
- A.price_per_unit_correction,A.product_id,A.uom_code;
- RAISE NOTICE '1 SELESAI INSERT KE TABLE TEMPORARY';
- IF EXISTS (SELECT 1 FROM tt_sl_so_closing_event_pos WHERE session_id = pSessionId) THEN
- /*
- * Cek Ketersediaan Kode Produk di table master
- */
- UPDATE tt_sl_so_closing_event_pos Z
- SET status = vStatusFail , message = Z.message || 'Kode Produk tidak terdaftar.'
- FROM tt_sl_so_closing_event_pos A
- LEFT OUTER JOIN m_product B ON A.product_id = B.product_id
- WHERE A.session_id = pSessionId
- AND A.status <> vStatusFail
- AND B.product_id IS NULL;
- RAISE NOTICE '2 CEK KETERSEDIAAN PRODUK';
- /*
- * Cek ketersediaan stock di gudang
- */
- WITH availabelQty AS (
- SELECT A.session_id,A.tenant_id,A.ou_id,A.pos_id,A.event_id,A.product_id,B.warehouse_id
- FROM tt_sl_so_closing_event_pos A
- INNER JOIN m_event B ON A.event_id = B.event_id
- INNER JOIN in_product_balance_stock C ON A.product_id = C.product_id
- WHERE A.qty_pos > C.qty
- AND B.warehouse_id = C.warehouse_id
- )
- UPDATE tt_sl_so_closing_event_pos Z
- SET status = vStatusFail, message= Z.message || 'Qty tidak cukup di gudang ' || f_get_warehouse_name(A.warehouse_id)
- FROM availabelQty A
- WHERE Z.tenant_id = A.tenant_id
- AND Z.session_id = A.session_id
- AND Z.ou_id = A.ou_id
- AND Z.product_id = A.product_id
- AND Z.status <> vStatusFail;
- RAISE NOTICE '3 CEK KETERSEDIAAN QTY';
- UPDATE tt_sl_so_closing_event_pos
- SET status = vStatusOk
- WHERE session_id = pSessionId AND status = vEmptyValue;
- IF (SELECT COUNT(1) FROM tt_sl_so_closing_event_pos WHERE session_id = pSessionId AND status = vStatusFail) = 0 THEN
- /*
- * Panggil function untuk lakukan generate so dan do
- * PERFORM process_generate_so_do_from_pos(pSessionId);
- */
- RETURN vStatusOk;
- ELSE
- RETURN 'error.data.product';
- END IF;
- ELSE
- RETURN 'cannot.process.because.not.yet.data.pos.transaction';
- END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
Advertisement
Add Comment
Please, Sign In to add comment