SL CANCEL SUBMIT SALES INVOICE TEMP

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

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

                    /*
                     * 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, karena sudah digunakan oleh sales invoice temporer
                     */
                    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;

                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 or document is on approval progress', pSalesInvoiceTempId;
    END IF;

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