Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Adrian, Feb 16, 2017
- CREATE OR REPLACE FUNCTION sl_cancel_submit_so(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;
- pSalesOrderId ALIAS FOR $5;
- pRemarkApproval ALIAS FOR $6;
- pPrevProcessNo ALIAS FOR $7;
- vSalesOrderDocTypeId bigint;
- vFunctionSubmit character varying;
- vStatusDocReleased character varying;
- vStatusDocDraft character varying;
- vStatusDocInProgress character varying;
- vWorkflowStatusDraft character varying;
- vEmptyId bigint;
- vZero bigint;
- vOuId bigint;
- vDocNo character varying;
- vDocDate character varying;
- vScheme character varying;
- result RECORD;
- BEGIN
- vSalesOrderDocTypeId := 301;
- vFunctionSubmit := 'sl_submit_so';
- vStatusDocReleased := 'R';
- vStatusDocDraft := 'D';
- vStatusDocInProgress := 'I';
- vWorkflowStatusDraft := 'DRAFT';
- vEmptyId := -99;
- vZero := 0;
- --RAISE EXCEPTION 'Function Submit For Document Sales Order is not yet Created';
- -- get data
- select A.ou_id, A.doc_no, A.doc_date, C.scheme
- FROM sl_so A, sl_so_item B, m_document C
- WHERE A.so_id = B.so_id AND
- A.doc_type_id = C.doc_type_id AND
- A.so_id = pSalesOrderId AND
- A.doc_type_id = vSalesOrderDocTypeId
- AND A.status_doc = vStatusDocReleased INTO result;
- IF FOUND THEN
- vOuId := result.ou_id;
- vDocNo := result.doc_no;
- vDocDate := result.doc_date;
- vScheme := result.scheme;
- /*
- * validasi belum digunakan oleh dokumen lain
- */
- IF NOT EXISTS (
- SELECT (1) FROM sl_so_balance_item B, sl_so_item A
- WHERE A.so_id = pSalesOrderId
- AND B.so_item_id = A.so_item_id
- AND (
- (B.qty_dlv + B.qty_return + B.qty_cancel + B.qty_add) > vZero
- OR B.status_item <> vStatusDocReleased
- )
- ) THEN
- /*
- * validasi belum digunakan oleh dokumen lain
- */
- IF NOT EXISTS (
- SELECT (1) FROM sl_so_po_balance_item B, sl_so_item A
- WHERE A.so_id = pSalesOrderId
- AND B.so_item_id = A.so_item_id
- AND (
- (B.qty_po_int + B.qty_po_int_return + B.qty_po_int_cancel + B.qty_po_int_add) > vZero
- OR B.status_item <> vStatusDocReleased
- )
- ) THEN
- /*
- * validasi belum digunakan oleh dokumen lain
- */
- IF NOT EXISTS (
- SELECT (1) FROM sl_log_so_balance_item A
- WHERE A.so_id = pSalesOrderId
- AND A.ref_id <> vEmptyId
- AND A.ref_doc_type_id <> vEmptyId
- AND A.ref_item_id <> vEmptyId
- ) THEN
- /*
- * 1. update status doc sl_so
- * 2. remove sl_so_tax
- * 3. remove sl_so_balance_item
- * 4. remove sl_log_so_balance_item
- */
- -- 1. ubah status_doc menjadi D
- UPDATE sl_so SET status_doc = vStatusDocDraft, workflow_status = vWorkflowStatusDraft, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
- WHERE so_id = pSalesOrderId
- AND status_doc = vStatusDocReleased;
- DELETE
- FROM sl_so_tax Z
- WHERE EXISTS (
- SELECT (1)
- FROM sl_so_item A
- WHERE A.so_id = pSalesOrderId
- AND A.tax_id <> vEmptyId
- AND Z.tenant_id = A.tenant_id
- AND Z.so_id = A.so_id
- AND Z.tax_id = A.tax_id
- );
- DELETE
- FROM sl_so_balance_item Z
- WHERE EXISTS (
- SELECT (1)
- FROM sl_so_item A
- WHERE A.so_id = pSalesOrderId
- AND A.so_item_id = Z.so_item_id
- )
- AND Z.qty_dlv = vZero
- AND Z.qty_return = vZero
- AND Z.qty_cancel = vZero
- AND Z.qty_add = vZero
- AND Z.status_item = vStatusDocReleased;
- DELETE
- FROM sl_so_po_balance_item Z
- WHERE EXISTS (
- SELECT (1)
- FROM sl_so_item A
- WHERE A.so_id = pSalesOrderId
- AND A.so_item_id = Z.so_item_id
- )
- AND Z.qty_po_int = vZero
- AND Z.qty_po_int_return = vZero
- AND Z.qty_po_int_cancel = vZero
- AND Z.qty_po_int_add = vZero
- AND Z.status_item = vStatusDocReleased;
- DELETE
- FROM sl_log_so_balance_item Z
- WHERE EXISTS (
- SELECT (1)
- FROM sl_so_item A
- WHERE A.so_id = pSalesOrderId
- AND Z.so_id = A.so_id
- AND Z.so_item_id = A.so_item_id
- );
- ELSE
- RAISE EXCEPTION 'Document with id % is already used', pSalesOrderId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is already used by Purchase Order', pSalesOrderId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is already used by Delivery Order', pSalesOrderId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pSalesOrderId;
- END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement