Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Adrian, Mar 17, 2017
- CREATE OR REPLACE FUNCTION pu_cancel_submit_gr_consignment(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pDatetime ALIAS FOR $4;
- pRgId ALIAS FOR $5;
- pRemarkApproval ALIAS FOR $6;
- pPrevProcessNo ALIAS FOR $7;
- vStatusDocRelease character varying := 'R';
- vStatusDocDraft character varying := 'D';
- vStatusDocInProgress character varying := 'I';
- vStatusDocFinal character varying := 'F';
- vWorkflowStatusDraft character varying := 'DRAFT';
- vDocTypeId bigint;
- vOuId bigint;
- vDocNo character varying;
- vScheme character varying;
- vStatusDoc character varying;
- vPoId bigint;
- vEmptyId bigint := -99;
- vEmptyValue character varying := ' ';
- vInProgress character varying := 'I';
- vYes character varying := 'Y';
- vDocDate character varying ;
- vStatusLedgerNotDone character varying := '0';
- vProductStatus character varying(50);
- vFunctionSubmit character varying := 'pu_submit_gr_consignment';
- vOutletId bigint;
- vOuWarehouseId bigint;
- vDbVersion character varying(10);
- vUnfinishedItem bigint;
- BEGIN
- SELECT product_status_code INTO vProductStatus
- FROM m_product_status
- WHERE flg_buy = 'Y';
- --RAISE EXCEPTION 'Function Cancel submit for Goods Receive Consignment is not yet Created';
- /**
- * 1. Cek document
- * 2. If doc exists and status doc = R
- */
- SELECT A.ou_id, A.doc_date, A.doc_type_id, A.doc_no, A.status_doc, A.ref_id, B.scheme
- INTO vOuId, vDocDate, vDocTypeId, vDocNo, vStatusDoc, vPoId, vScheme
- FROM pu_receive_goods_consignment A
- INNER JOIN m_document B ON A.doc_type_id = B.doc_type_id
- WHERE A.receive_goods_consignment_id = pRgId
- AND A.status_doc = vStatusDocRelease;
- IF FOUND THEN
- -- check document already used in claim note
- IF EXISTS (
- SELECT 1
- FROM in_balance_receive_goods_consignment_item
- WHERE receive_goods_consignment_id = pRgId
- AND po_id = vPoId
- AND (qty_return <> 0 OR status_item <> 'R')
- ) THEN
- RAISE EXCEPTION 'Purchase Order already claim';
- ELSE
- /**
- * validasi tidak sedang digunakan oleh dokumen lain
- */
- IF NOT EXISTS (
- SELECT (1)
- FROM pu_po_balance_item_consignment A, pu_receive_goods_consignment_item B
- WHERE A.po_item_id = B.ref_id
- AND A.tenant_id = B.tenant_id
- AND B.receive_goods_consignment_id = pRgId
- AND A.status_item <> vStatusDocRelease
- ) THEN
- /*
- * 1. delete pu_log_po_balance_item_consignment
- * 4. delete in_product_balance
- * 6. delete/update in_product_balance_stock
- * 7. delete in_log_product_consignment_balance_stock
- * 9. update status pu_receive_goods_consignment
- * 10.update status pu_po_balance_item_consignment
- * 10b.update status pu_po
- * 11.delete balance receive goods item
- */
- -- 1. delete pu_log_po_balance_item_consignment
- DELETE FROM pu_log_po_balance_item_consignment Z
- WHERE EXISTS (
- SELECT (1)
- FROM pu_receive_goods_consignment A, pu_receive_goods_consignment_item B, pu_po_item C
- WHERE Z.tenant_id = A.tenant_id
- AND Z.po_id = C.po_id
- AND Z.po_item_id = C.po_item_id
- AND Z.ref_doc_type_id = A.doc_type_id
- AND Z.ref_id = pRgId
- AND Z.ref_item_id = B.receive_goods_consignment_item_id
- AND Z.qty_trx = B.qty_rcv_po * -1
- AND Z.trx_uom_id = B.po_uom_id
- AND Z.qty_int = B.qty_rcv_int * -1
- AND Z.base_uom_id = B.base_uom_id
- AND Z.remark = B.remark
- AND A.receive_goods_consignment_id = pRgId
- AND A.receive_goods_consignment_id = B.receive_goods_consignment_id
- AND B.ref_id = C.po_item_id
- );
- -- Insert untuk data product yang memiliki serial number dan serial number tidak di-generate
- INSERT INTO tt_pu_product_balance
- (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
- serial_number, lot_number, product_expired_date, product_year_made,
- product_price_balance_id, product_buy_date, partner_id,
- doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
- po_id, po_no, po_date, po_item_id,
- curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
- SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
- C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
- vEmptyId, A.doc_date, A.partner_id,
- A.doc_type_id, A.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no, A.doc_date,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, SUM(C.qty_rcv_int), D.base_uom_id, D.flg_stock
- FROM pu_receive_goods_consignment A, pu_receive_goods_consignment_item B, pu_receive_goods_consignment_product C, pu_po_item D, pu_po E
- WHERE A.receive_goods_consignment_id = pRgId AND
- A.receive_goods_consignment_id = B.receive_goods_consignment_id AND
- B.receive_goods_consignment_item_id = C.receive_goods_consignment_item_id AND
- B.ref_id = D.po_item_id AND
- D.po_id = E.po_id
- GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
- C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
- A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
- -- validasi item tidak digunakan pada DO, GTO atau Adjs Stock
- IF EXISTS (
- SELECT 1
- FROM sl_do_product
- WHERE product_balance_id IN (
- SELECT A.product_balance_id
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- )
- )
- THEN
- RAISE EXCEPTION 'Product already used in delivery order';
- END IF;
- IF EXISTS (
- SELECT 1
- FROM in_inventory_item
- WHERE product_balance_id IN (
- SELECT A.product_balance_id
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- )
- )
- THEN
- RAISE EXCEPTION 'Product already used in GTO or Adjustment Stock';
- END IF;
- /*
- * update product_balance_id dari in_product_balance
- */
- UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
- FROM in_product_balance A
- WHERE tt_pu_product_balance.session_id = pSessionId AND
- tt_pu_product_balance.tenant_id = A.tenant_id AND
- tt_pu_product_balance.product_id = A.product_id AND
- tt_pu_product_balance.serial_number = A.serial_number AND
- tt_pu_product_balance.lot_number = A.lot_number;
- -- 4. delete in_product_balance
- DELETE FROM in_product_balance A
- WHERE A.tenant_id = pTenantId
- AND product_balance_id IN (
- SELECT A.product_balance_id
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- );
- -- 6. delete/update in_product_balance_stock
- DELETE FROM in_product_balance_stock
- WHERE tenant_id = pTenantId
- AND product_balance_id IN (
- SELECT A.product_balance_id
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- AND A.flg_stock = 'Y'
- );
- -- Insert untuk data product yang tidak memiliki serial number
- INSERT INTO tt_pu_product_balance
- (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
- serial_number, lot_number, product_expired_date, product_year_made,
- product_price_balance_id, product_buy_date, partner_id,
- doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
- po_id, po_no, po_date, po_item_id,
- curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
- SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
- vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
- vEmptyId, A.doc_date, A.partner_id,
- A.doc_type_id, A.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no, A.doc_date,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.gross_price_po - D.discount_amount, SUM(B.qty_rcv_po), D.po_uom_id, SUM(B.qty_rcv_int), D.base_uom_id, D.flg_stock
- FROM pu_receive_goods_consignment A, pu_receive_goods_consignment_item B, pu_po_item D, pu_po E
- WHERE A.receive_goods_consignment_id = pRgId AND
- A.receive_goods_consignment_id = B.receive_goods_consignment_id AND
- NOT EXISTS (SELECT 1 FROM pu_receive_goods_consignment_product C
- WHERE B.receive_goods_consignment_item_id = C.receive_goods_consignment_item_id) AND
- NOT EXISTS (SELECT 1 FROM pu_receive_goods_consignment_product_auto_sn C
- WHERE B.receive_goods_consignment_item_id = C.receive_goods_consignment_item_id) AND
- B.ref_id = D.po_item_id AND
- D.po_id = E.po_id
- GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
- A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;
- /*
- * update product_balance_id dari in_product_balance
- */
- UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
- FROM in_product_balance A
- WHERE tt_pu_product_balance.session_id = pSessionId AND
- tt_pu_product_balance.tenant_id = A.tenant_id AND
- tt_pu_product_balance.product_id = A.product_id AND
- tt_pu_product_balance.serial_number = A.serial_number AND
- tt_pu_product_balance.lot_number = A.lot_number;
- /*
- * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
- */
- INSERT INTO tt_pu_product_balance_summary_stock
- (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
- product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
- SELECT pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
- vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
- A.po_uom_id, A.base_uom_id, A.flg_stock;
- -- 6. delete/update in_product_balance_stock
- UPDATE in_product_balance_stock
- SET qty = qty - A.qty_int_rcv,
- update_datetime = pDatetime,
- update_user_id = pUserId
- FROM tt_pu_product_balance_summary_stock A
- WHERE A.session_id = pSessionId AND
- in_product_balance_stock.product_id = A.product_id AND
- in_product_balance_stock.tenant_id = A.tenant_id AND
- in_product_balance_stock.warehouse_id = A.warehouse_id AND
- in_product_balance_stock.product_balance_id = A.product_balance_id AND
- in_product_balance_stock.product_status = vProductStatus AND
- A.flg_stock = 'Y';
- -- QTY tidak boleh < 0
- IF EXISTS (
- SELECT 1
- FROM in_product_balance_stock A
- INNER JOIN tt_pu_product_balance B ON A.product_balance_id = B.product_balance_id
- WHERE B.session_id = pSessionId
- AND A.qty < 0
- )
- THEN
- RAISE EXCEPTION 'Qty product in stock product less than zero';
- END IF;
- -- 7. delete in_log_product_consignment_balance_stock
- DELETE FROM in_log_product_consignment_balance_stock Z
- WHERE EXISTS (
- SELECT (1)
- FROM tt_pu_product_balance A
- WHERE Z.tenant_id = A.tenant_id
- AND Z.ou_id = A.ou_id
- AND Z.doc_type_id = A.doc_type_id
- AND Z.ref_id = A.ref_id
- AND Z.doc_no = A.doc_no
- AND Z.doc_date = A.doc_date
- AND Z.warehouse_id = A.warehouse_id
- AND Z.product_id = A.product_id
- AND Z.product_balance_id = A.product_balance_id
- AND Z.product_status = vProductStatus
- AND A.session_id = pSessionId
- AND A.flg_stock = 'Y'
- );
- DELETE FROM in_balance_receive_goods_consignment_item
- WHERE tenant_id = pTenantId
- AND receive_goods_consignment_item_id IN (
- SELECT A.ref_item_id
- FROM tt_pu_product_balance A
- WHERE A.session_id = pSessionId
- );
- -- 9. update status pu_receive_goods_consignment
- UPDATE pu_receive_goods_consignment
- SET status_doc = vStatusDocDraft,
- workflow_status = vWorkflowStatusDraft,
- update_datetime = pDatetime,
- update_user_id = pUserId
- WHERE receive_goods_consignment_id = pRgId
- AND status_doc = vStatusDocRelease;
- -- 10.update status pu_po_balance_item_consignment
- UPDATE pu_po_balance_item_consignment
- SET status_item = vStatusDocInProgress,
- update_datetime = pDatetime,
- update_user_id = pUserId
- FROM pu_receive_goods_consignment_item A
- WHERE pu_po_balance_item_consignment.po_item_id = A.ref_id AND
- pu_po_balance_item_consignment.tenant_id = A.tenant_id AND
- A.receive_goods_consignment_id = pRgId;
- -- do reset approval to draft
- PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pRgId, vDocNo, pDatetime, pRemarkApproval);
- -- update previous process no
- UPDATE t_process_message
- SET process_no = pPrevProcessNo,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = version + 1
- WHERE tenant_id = pTenantId
- AND process_name = vFunctionSubmit
- AND process_no = pRgId || '_' || vDocNo;
- ELSE
- RAISE EXCEPTION 'Item in purchase order with id % is already used in other document', vPoId;
- END IF;
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pRgId;
- END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement