CANCEL SUBMIT SO

--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;
  /