Function Cancel Submit TSI

--Adrian, Feb 28, 2017

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

    vSalesInvoiceTempDocTypeId          bigint;
    vFunctionSubmit                     character varying;
    vStatusDocReleased                  character varying;
    vStatusDocDraft                     character varying;
    vStatusDocInProgress                character varying;
    vWorkflowStatusDraft                character varying;
    vEmptyId                            bigint;
    vZero                               bigint;
    vYes                                character varying;
    vNo                                 character varying;
    vDoDocTypeId                        bigint;
    vJointDppPpn                        character varying(1);

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

    result                              RECORD;

    vInvArBalanceId                     bigint;
    vInvTaxArBalanceId                  bigint;
    vDoId                               bigint;

    vPaymentAmount                      numeric;
    vPaymentAmountTax                   numeric;
    vFlgPayment                         character varying;
    vFlgPaymentTax                      character varying;

BEGIN

    vSalesInvoiceTempDocTypeId := 361;
    vFunctionSubmit := 'sl_submit_sales_invoice_temp';
    vStatusDocReleased := 'R';
    vStatusDocDraft := 'D';
    vStatusDocInProgress := 'I';
    vWorkflowStatusDraft := 'DRAFT';
    vEmptyId := -99;
    vZero := 0;
    vYes = 'Y';
    vNo = 'N';
    vDoDocTypeId := 311;
    vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');

    --RAISE EXCEPTION 'Function Submit For Document Temporary Sales Invoice is not yet Created';

    -- get data
    select A.ou_id, A.doc_no, A.doc_date, C.scheme
    FROM sl_invoice_temp A, m_document C
    WHERE A.doc_type_id = C.doc_type_id AND
        A.invoice_temp_id = pSalesInvoiceTempId AND
        A.doc_type_id = vSalesInvoiceTempDocTypeId
        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 invoice
         */
        IF NOT EXISTS (
            SELECT (1) FROM sl_so_balance_invoice A, sl_invoice_temp B, sl_invoice_temp_item C
            WHERE B.invoice_temp_id = pSalesInvoiceTempId AND
                B.invoice_temp_id = C.invoice_temp_id AND
                A.ou_id = B.ou_id AND
                A.so_id = B.ref_id AND
                A.ref_doc_type_id = vDoDocTypeId AND
                A.ref_id = C.ref_id AND
                A.ref_item_id = C.ref_item_id AND
                A.do_receipt_item_id = C.do_receipt_item_id AND
                A.flg_invoice = vYes
            ) THEN

            /*
             * validasi belum digunakan oleh invoice
             */
            IF NOT EXISTS (
                SELECT (1) FROM sl_do_inv A, sl_invoice_temp_item B, sl_do C
                WHERE B.invoice_temp_id = pSalesInvoiceTempId
                    AND B.ref_id = C.do_id
                    AND A.do_id = C.do_id
                    AND A.flg_invoice = vYes
                ) THEN

                /*
                 * validasi belum digunakan oleh invoice
                 */
                IF NOT EXISTS (
                    SELECT (1) FROM sl_so_balance_advance_invoice A, sl_invoice_temp_advance B
                    WHERE B.ref_id = A.ref_id
                        AND B.invoice_temp_id = pSalesInvoiceTempId
                        AND A.flg_invoice = vYes
                    ) THEN

                    -- Sales Invoice yang hendak dibatalkan harus belum pernah dialokasi sama sekali
                    SELECT A.invoice_ar_balance_id, A.payment_amount, A.flg_payment
                    INTO vInvArBalanceId, vPaymentAmount, vFlgPayment
                    FROM fi_invoice_ar_balance A
                    WHERE A.invoice_ar_id = pSalesInvoiceTempId
                        AND A.doc_type_id = vSalesInvoiceTempDocTypeId;

                    SELECT A.invoice_tax_ar_balance_id, A.payment_amount, A.flg_payment
                    INTO vInvTaxArBalanceId, vPaymentAmountTax, vFlgPaymentTax
                    FROM fi_invoice_tax_ar_balance A
                    WHERE A.invoice_ar_balance_id = vInvArBalanceId;


                    --1) cek saldo dokumen Sales Invoice-nya apakah payment_amountnya tidak 0 atau flg_payment tidak sama dengan 'N' di fi_invoice_ar_balance berdasarkan dokumen Sales Invoice terkait.
                    --  Apabila tidak sesuai dengan kondisi tersebut, maka muncul exception dan berhenti prosesnya.
                    --2) cek saldo tax dokumen Sales Invoice-nya apakah payment_amountnya tidak 0 atau flg_payment tidak sama dengan 'N' di fi_invoice_tax_ar_balance berdasarkan dokumen Sales Invoice terkait.
                    --  Apabila tidak sesuai dengan kondisi tersebut, maka muncul exception dan berhenti prosesnya.
                    IF EXISTS (
                        SELECT 1
                        FROM fi_invoice_ar_balance A
                        WHERE A.invoice_ar_balance_id = vInvArBalanceId
                            AND (A.flg_payment IN ( vYes, vStatusDocInProgress ) OR A.payment_amount <> 0) )
                        OR

                        EXISTS (
                            SELECT 1
                            FROM fi_invoice_tax_ar_balance A
                            INNER JOIN fi_invoice_ar_balance B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id
                            WHERE B.invoice_ar_balance_id = vInvArBalanceId
                                AND (A.flg_payment IN ( vYes, vStatusDocInProgress ) OR A.payment_amount <> 0) )
                    THEN
                        RAISE EXCEPTION 'Document is already used in allocation process, process aborted';
                    ELSE

                        /*
                         * 1. update status doc sl_invoice_temp
                         * 2. update sl_so_balance_invoice
                         * 3. update sl_so_balance_invoice_tax
                         * 4. delete fi_invoice_ar_balance
                         * 5. delete fi_invoice_tax_ar_balance
                         * 6. delete sl_do_inv
                         */

                        -- 1. update status doc sl_invoice_temp
                        UPDATE sl_invoice_temp SET status_doc = vStatusDocDraft, workflow_status = vWorkflowStatusDraft,  update_datetime = pDatetime, update_user_id = pUserId
                        WHERE invoice_temp_id = pSalesInvoiceTempId
                            AND status_doc = vStatusDocReleased;

                        -- 2. update sl_so_balance_invoice
                        UPDATE sl_so_balance_invoice A SET flg_invoice_temp = vNo, update_datetime = pDatetime, update_user_id = pUserId
                        FROM sl_invoice_temp B, sl_invoice_temp_item C
                        WHERE B.invoice_temp_id = pSalesInvoiceTempId AND
                            B.invoice_temp_id = C.invoice_temp_id AND
                            A.ou_id = B.ou_id AND
                            A.so_id = B.ref_id AND
                            A.ref_doc_type_id = vDoDocTypeId AND
                            A.ref_id = C.ref_id AND
                            A.ref_item_id = C.ref_item_id AND
                            A.do_receipt_item_id = C.do_receipt_item_id AND
                            A.flg_invoice = vNo AND
                            A.flg_invoice_temp = vYes;

                        -- 3. update sl_so_balance_invoice_tax
                        UPDATE sl_so_balance_invoice_tax A SET flg_invoice = vNo, update_datetime = pDatetime, update_user_id = pUserId
                        FROM sl_invoice_temp B, sl_invoice_temp_item C
                        WHERE B.invoice_temp_id = pSalesInvoiceTempId AND
                            B.invoice_temp_id = C.invoice_temp_id AND
                            A.ou_id = B.ou_id AND
                            A.so_id = B.ref_id AND
                            A.ref_doc_type_id = vDoDocTypeId AND
                            A.ref_id = C.ref_id AND
                            A.ref_item_id = C.ref_item_id AND
                            A.do_receipt_item_id = C.do_receipt_item_id;

                        -- 4. delete fi_invoice_ar_balance
                        -- 5. delete fi_invoice_tax_ar_balance
                        /*SELECT invoice_ar_balance_id INTO vInvArBalanceId
                        FROM fi_invoice_ar_balance A, sl_invoice_temp B
                        WHERE A.invoice_ar_id = pSalesInvoiceTempId AND A.doc_type_id = vSalesInvoiceTempDocTypeId;*/

                        /*SELECT invoice_tax_ar_balance_id INTO vInvTaxArBalanceId
                        FROM fi_invoice_tax_ar_balance A
                        WHERE A.invoice_ar_balance_id = vInvArBalanceId;*/

                        DELETE FROM fi_invoice_ar_balance_due_date WHERE invoice_ar_balance_id = vInvArBalanceId;

                        DELETE FROM fi_invoice_tax_ar_balance_due_date WHERE invoice_tax_ar_balance_id = vInvTaxArBalanceId;

                        DELETE FROM fi_invoice_ar_balance WHERE invoice_ar_balance_id = vInvArBalanceId;

                        DELETE FROM fi_invoice_tax_ar_balance WHERE invoice_ar_balance_id = vInvArBalanceId;

                        -- 6. delete sl_do_inv
                        DELETE FROM sl_do_inv Z
                        WHERE EXISTS (
                            SELECT (1)
                            FROM sl_invoice_temp_item A, sl_do B
                            WHERE A.invoice_temp_id = pSalesInvoiceTempId
                                AND A.ref_id = B.do_id
                                AND Z.do_id = B.do_id
                                AND Z.flg_invoice = vNo
                            );

                        /*
                         * update flg saldo so advance invoice
                         */
                        UPDATE sl_so_balance_advance_invoice
                        SET flg_invoice_temp = vNo, invoice_id = vEmptyId,
                            update_datetime = pDatetime, update_user_id = pUserId,
                            version = sl_so_balance_advance_invoice.version + 1
                        FROM sl_invoice_temp_advance A
                        WHERE A.ref_id = sl_so_balance_advance_invoice.ref_id
                            AND A.invoice_temp_id = pSalesInvoiceTempId;

                        -- PERFORM f_reset_approval_to_draft
                        PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pSalesInvoiceTempId, 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 = pSalesInvoiceTempId || '_' || vDocNo;

                    END IF;

                ELSE
                    RAISE EXCEPTION 'Document with id % is already used in invoice', pSalesInvoiceTempId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Document with id % is already used in invoice', pSalesInvoiceTempId;
            END IF;

        ELSE
            RAISE EXCEPTION 'Document with id % is already used in invoice', pSalesInvoiceTempId;
        END IF;

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

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