pu_cancel_submit_po

-- Adrian, Aug 28, 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;
    vFlagConsign                        character varying(1);

    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, C.flg_buy_consignment
    FROM pu_po A, m_document B, pu_po_ext C
    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
        AND A.po_id = C.po_id
        INTO result;

    IF FOUND THEN
        vOuId := result.ou_id;
        vDocNo := result.doc_no;
        vDocDate := result.doc_date;
        vScheme := result.scheme;
        vFlagConsign := result.flg_buy_consignment;

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


            IF vFlagConsign = 'N' THEN

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

                        /*
                         * Jika PO adalah bukan PO consignment
                         * 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 '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

                /*
                 * validasi belum digunakan oleh dokumen lain
                 */
                IF NOT EXISTS (
                    SELECT (1) FROM pu_po_balance_item_consignment 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

                    /*
                     * Jika PO adalah bukan PO consignment
                     * 1. remove pu_po_tax
                     * 2. remove pu_po_balance_item_consignment
                     */

                    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_consignment 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 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 'Item in document with id % is already used by other document', pPurchaseOrderId;
                END IF;

            END IF;

        ELSE
            RAISE EXCEPTION 'Document with id % is already used in Manage Purchase Order', 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;
  /