pu_cancel_submit_po

-- Adrian, Aug 25, 2017

CREATE OR REPLACE FUNCTION pu_cancel_submit_po(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;
    pPurchaseOrderId                    ALIAS FOR $5;
    pRemarkApproval                     ALIAS FOR $6;
    pPrevProcessNo                      ALIAS FOR $7;

    vPurchaseOrderDocTypeId             bigint;
    vFunctionSubmit                     character varying;
    vStatusDocReleased                  character varying;
    vStatusDocDraft                     character varying;
    vStatusDocInProgress                character varying;
    vStatusFinal                        character varying;
    vWorkflowStatusDraft                character varying;
    vEmptyId                            bigint;
    vZero                               bigint;

    vOuId                               bigint;
    vDocNo                              character varying;
    vDocDate                            character varying;
    vScheme                             character varying;

    result                              RECORD;

BEGIN

    vPurchaseOrderDocTypeId := 101;
    vFunctionSubmit := 'pu_submit_po';
    vStatusDocReleased := 'R';
    vStatusDocDraft := 'D';
    vStatusDocInProgress := 'I';
    vStatusFinal := 'F';
    vWorkflowStatusDraft := 'DRAFT';
    vEmptyId := -99;
    vZero := 0;

    --RAISE EXCEPTION 'Function Cancel Submit is not yet Created';

    -- get data
    select A.ou_id, A.doc_no, A.doc_date, B.scheme
    FROM pu_po A, m_document B
    WHERE A.doc_type_id = B.doc_type_id AND
        A.po_id = pPurchaseOrderId AND
        A.doc_type_id = vPurchaseOrderDocTypeId
        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 pu_po_balance_item B, pu_po_item A
            WHERE A.po_id = pPurchaseOrderId
                AND B.po_item_id = A.po_item_id
                AND (
                    (B.qty_rcv + B.qty_return + B.qty_cancel + B.qty_add) > vZero
                    OR B.status_item NOT IN (vStatusDocReleased)
                )
            ) THEN

            /*
             * validasi belum digunakan oleh dokumen lain
             */
            IF NOT EXISTS (
                SELECT (1) FROM sl_so_po_balance_item B, pu_po_item A
                WHERE A.po_id = pPurchaseOrderId
                    AND B.so_item_id = A.ref_id
                    AND (
                        (B.qty_po_int + B.qty_po_int_return + B.qty_po_int_cancel + B.qty_po_int_add) - A.qty_int < vZero
                        OR B.status_item NOT IN (vStatusDocReleased, vStatusFinal)
                    )
                ) THEN

                /*
                 * validasi belum digunakan oleh manage PO
                 */
                IF NOT EXISTS (
                    SELECT (1) FROM pu_manage_po B
                    WHERE B.po_id = pPurchaseOrderId
                    ) THEN

                    /*
                     * 1. remove pu_po_tax
                     * 2. remove pu_po_balance_item
                     * 3. update sl_so_po_balance_item
                     * 4. update pu_po
                     */

                    DELETE
                    FROM pu_po_tax Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM pu_po_item A
                        WHERE A.po_id = pPurchaseOrderId
                            AND A.tax_id <> vEmptyId
                            AND Z.tenant_id = A.tenant_id
                            AND Z.po_id = A.po_id
                            AND Z.tax_id = A.tax_id
                        );

                    DELETE
                    FROM pu_po_balance_item Z
                    WHERE EXISTS (
                        SELECT (1)
                        FROM pu_po_item A
                        WHERE A.po_id = pPurchaseOrderId
                            AND A.po_item_id = Z.po_item_id
                        )
                        AND Z.qty_rcv = vZero
                        AND Z.qty_return = vZero
                        AND Z.qty_cancel = vZero
                        AND Z.qty_add = vZero
                        AND Z.status_item = vStatusDocReleased;

                    UPDATE sl_so_po_balance_item
                        SET qty_po_int = qty_po_int - A.qty_int, status_item = vStatusDocInProgress, update_datetime = pDatetime, update_user_id = pUserId
                    FROM pu_po_item A
                    WHERE A.po_id = pPurchaseOrderId AND
                        A.ref_id <> -99 AND
                        A.ref_id = sl_so_po_balance_item.so_item_id;

                    UPDATE pu_po SET status_doc = vStatusDocDraft, workflow_status = vWorkflowStatusDraft, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
                    WHERE po_id = pPurchaseOrderId
                    AND status_doc = vStatusDocReleased;

                    -- PERFORM f_reset_approval_to_draft
                    PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pPurchaseOrderId, vDocNo, pDatetime, pRemarkApproval);

                    -- UPDATE t_process_message
                    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 = pPurchaseOrderId || '_' || vDocNo;

                ELSE
                    RAISE EXCEPTION 'Document with id % is already used in Manage Purchase Order', pPurchaseOrderId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Item in document with id % is already used by other document or not sufficient', pPurchaseOrderId;
            END IF;

        ELSE
            RAISE EXCEPTION 'Item in document with id % is already used by other document', pPurchaseOrderId;
        END IF;

    ELSE
        RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pPurchaseOrderId;
    END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /