pu_cancel_submit_po_consingment

CREATE OR REPLACE FUNCTION pu_cancel_submit_po_consingment(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; -- for update user id in transaction
    pDatetime               ALIAS FOR $4;
    pPoId                   ALIAS FOR $5;
    pRemarkApproval         ALIAS FOR $6; -- Remark for appear in approval reset history
    pPrevProcessNo          ALIAS FOR $7; -- Substitute for renaming prevous process no

    vDocTypeIdPo            bigint := 101;
    vStatusRelease          character varying(1) := 'R';
    vDocNo                  character varying;
    vScheme                 character varying;
    vStatusDocDraft         character varying := 'D';
    vWorkflowStatusDraft    character varying := 'DRAFT';
    vFlagConsign            character varying(1);
    vEmptyId                bigint := -99;
    vFunctionSubmit         character varying := 'pu_submit_po';

    vProcessId      bigint;
    vPoId           bigint;
    vUserId         bigint;
    vDatetime       character varying(14);

    vStatusFinal    character varying(1);

    vPurchaseOrderDocTypeId bigint;
    vRoundingModeNonTax character varying(5);


BEGIN

    -- Find document must release and match doc type
    SELECT A.doc_no, B.scheme
    INTO vDocNo, vScheme
    FROM pu_po A
    INNER JOIN m_document B ON A.doc_type_id = B.doc_type_id
    WHERE A.po_id = pPoId
        AND A.doc_type_id = vDocTypeIdPo
        AND A.status_doc = vStatusRelease;

    IF FOUND THEN
        -- Update pu_po, set doc_status to DRAFT
        UPDATE pu_po
        SET status_doc = vStatusDocDraft,
            workflow_status = vWorkflowStatusDraft,
            version = version + 1,
            update_datetime = pDatetime,
            update_user_id = pUserId
        WHERE po_id = pPoId;

        SELECT flg_buy_consignment INTO vFlagConsign
        FROM pu_po_ext
        WHERE po_id = pPoId;

        DELETE FROM pu_po_tax A
        WHERE EXISTS (
            SELECT 1 FROM pu_po_item B
            INNER JOIN m_tax C ON B.tax_id = C.tax_id
            WHERE B.po_id = pPoId
                AND B.tax_id <> vEmptyId
                AND A.tenant_id = B.tenant_id
                AND A.po_id = B.po_id
                AND A.tax_id = B.tax_id
        );

        IF vFlagConsign = 'Y' THEN
            DELETE FROM pu_po_balance_item_consignment A
            WHERE EXISTS (
                SELECT 1 FROM pu_po_item B
                INNER JOIN pu_po C ON B.po_id = C.po_id
                WHERE C.po_id = pPoId
                    AND A.po_item_id = B.po_item_id
            );

            DELETE FROM pu_log_po_balance_item_consignment A
            WHERE EXISTS (
                SELECT 1 FROM pu_po_item B
                WHERE B.po_id = pPoId
                    AND A.tenant_id = B.tenant_id
                    AND A.po_id = B.po_id
                    AND A.po_item_id = B.po_item_id
                    AND A.ref_doc_type_id = vEmptyId
                    AND A.ref_id = vEmptyId
                    AND A.ref_item_id = vEmptyId
            );

            -- PERFORM f_reset_approval_to_draft
            PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pPoId, 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 = pPoId || '_' || vDocNo;
        END IF;
    ELSE
        RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pPoId;
    END IF;

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