CANCEL SUBMIT CANCEL SO

--Adrian, Mar 3, 2017

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

    vEmptyId                            bigint;
    vCancelSoDocTypeId                  bigint;
    vFlagNo                             character varying(1);
    vRoundingModeNonTax                 character varying(5);
    vStatusRelease                      character varying(1);
    vOuStructure                        OU_BU_STRUCTURE%ROWTYPE;
    RESULT                              RECORD;

    vFunctionSubmit                     character varying;
    vStatusDraft                        character varying;
    vStatusInProgress                   character varying;
    vStatusCancel                       character varying(1);
    vWorkflowStatusDraft                character varying;
    vSoId                               bigint;
    vZero                               bigint;
    vTypeDataHeader                     character varying;
    vTypeDataFinance                    character varying;

    vDoDocDate                          character varying;
    vDoReceiptDocDate                   character varying;
    vOuId                               bigint;
    vLedgerCode                         character varying;
    vStatusLedgerNotDone                character varying;
    vScheme                             character varying;
    vDocNo                              character varying;

    vEmpty                              character varying;

BEGIN

    vEmptyId := -99;
    vStatusRelease := 'R';
    vFlagNo := 'N';
    vCancelSoDocTypeId := 302;

    vFunctionSubmit := 'sl_submit_cancel_so';
    vStatusDraft := 'D';
    vStatusInProgress = 'I';
    vStatusCancel := 'C';
    vWorkflowStatusDraft := 'DRAFT';
    vZero := 0;

    vLedgerCode := 'INV';
    vStatusLedgerNotDone := '0';

    vEmpty := '';

    select f_get_ou_bu_structure(A.ou_id) AS ou,
        A.ou_id, A.doc_date,
        COALESCE(C.doc_date, '') AS do_doc_date, COALESCE(D.doc_date, '') AS do_receipt_doc_date,
        A.doc_no, E.scheme
    FROM sl_manage_so A
    LEFT JOIN sl_do C ON C.ref_id = A.so_id
    LEFT JOIN in_do_receipt D ON D.ref_id = C.do_id
    INNER JOIN m_document E ON E.doc_type_id = A.doc_type_id
    WHERE A.manage_so_id = pCancelSoId
        AND A.doc_type_id = vCancelSoDocTypeId
        AND A.status_doc = vStatusRelease INTO RESULT;

    --RAISE EXCEPTION 'Function Submit For Document Cancel SO is not yet created';

    IF FOUND THEN
        vOuStructure := RESULT.ou;
        vOuId := RESULT.ou_id;
        vDoDocDate := RESULT.do_doc_date;
        vDoReceiptDocDate := RESULT.do_receipt_doc_date;
        vScheme := RESULT.scheme;
        vDocNo := RESULT.doc_no;

        -- Cek status ledger not yet closed for DO
        IF ((vDoDocDate = vEmpty) OR EXISTS (
            SELECT 1
            FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
            WHERE e.tenant_id = pTenantId
                AND e.ou_id = f.ou_bu_id
                AND f.ou_id = vOuId
                AND e.date_year_month = SUBSTR(vDoDocDate, 1, 6)
                AND e.ledger_code = vLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone
            )) THEN

            -- Cek status ledger not yet closed for DO Receipt
            IF ((vDoReceiptDocDate = vEmpty) OR EXISTS (
                SELECT 1
                FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
                WHERE e.tenant_id = pTenantId
                    AND e.ou_id = f.ou_bu_id
                    AND f.ou_id = vOuId
                    AND e.date_year_month = SUBSTR(vDoReceiptDocDate, 1, 6)
                    AND e.ledger_code = vLedgerCode AND
                    f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone
                )) THEN

                SELECT A.so_id INTO vSoId FROM sl_manage_so A WHERE A.manage_so_id = pCancelSoId;

                /*
                 * validasi so tidak sedang digunakan
                 */
                IF NOT EXISTS (
                    SELECT (1)
                    FROM sl_so A, sl_manage_so_header B, sl_manage_so C
                    WHERE B.manage_so_id = pCancelSoId
                        AND B.manage_so_id = C.manage_so_id
                        AND A.so_id = C.so_id
                        AND A.status_doc <> vStatusRelease
                    ) THEN

                    /**
                     * validasi SO PO harus cancel
                     */
                    IF NOT EXISTS (
                        SELECT (1)
                        FROM sl_so_po_balance_item A, sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D
                        WHERE A.so_item_id = B.so_item_id
                            AND B.manage_so_id = C.manage_so_id
                            AND A.so_item_id = D.so_item_id
                            AND B.manage_so_id = pCancelSoId
                            AND A.status_item <> vStatusCancel
                    ) THEN

                        /**
                         * validasi SO Balance harus cancel
                         */
                        IF NOT EXISTS (
                            SELECT (1)
                            FROM sl_so_balance_item A, sl_manage_so_item B
                            WHERE A.so_item_id = B.so_item_id
                                AND B.manage_so_id = pCancelSoId
                                AND A.status_item <> vStatusCancel
                        ) THEN

                            /*
                             * 1.update status item balance SO
                             * 2.update status doc sl so
                             * 3.delete sl log balance item SO
                             * 4.update workflow status manage SO
                             * 5.update qty cancel dan status item balance PO terhadap SO
                             */

                            UPDATE sl_so_balance_item A
                            SET status_item = vStatusInProgress,
                               qty_cancel = B.qty_so,
                               qty_cancel_int = B.qty_int,
                               update_datetime = pDatetime,
                               update_user_id = pUserId
                            FROM sl_manage_so_item B
                            WHERE A.so_item_id = B.so_item_id
                                AND B.manage_so_id = pCancelSoId
                                AND A.status_item = vStatusCancel;

                            UPDATE sl_so SET status_doc = vStatusInProgress, update_datetime = pDatetime, update_user_id = pUserId
                            WHERE so_id = vSoId;

                            DELETE FROM sl_log_so_balance_item Z
                            WHERE EXISTS (
                                SELECT (1)
                                FROM sl_manage_so A, sl_manage_so_item B
                                WHERE A.manage_so_id = pCancelSoId
                                    AND A.manage_so_id = B.manage_so_id
                                    AND A.so_id = vSoId
                                    AND Z.tenant_id = A.tenant_id
                                    AND Z.so_id = A.so_id
                                    AND Z.so_item_id = B.so_item_id
                                    AND Z.ref_doc_type_id = A.doc_type_id
                                    AND Z.ref_id = A.manage_so_id
                                    AND Z.ref_item_id = B.manage_so_item_id
                                    AND Z.qty_trx = -1 * B.qty_so
                                    AND Z.trx_uom_id = B.so_uom_id
                                    AND Z.qty_int = -1 * B.qty_int
                                    AND Z.base_uom_id = B.base_uom_id
                                    AND Z.remark = A.remark
                            );

                            UPDATE sl_manage_so SET status_doc = vStatusDraft, workflow_status = vWorkflowStatusDraft, update_datetime = pDatetime, update_user_id = pUserId, version = version + 1
                            WHERE manage_so_id = pCancelSoId
                                AND status_doc = vStatusRelease;

                            -- Update status so po balance item
                            UPDATE sl_so_po_balance_item A
                            SET update_datetime = pDatetime, update_user_id = pUserId, version = A.version + 1, status_item = vStatusRelease
                            FROM sl_manage_so_item B
                            WHERE A.so_item_id = B.so_item_id
                                AND B.manage_so_id = pCancelSoId
                                AND A.status_item = vStatusCancel;

                            -- Update status menjadi 'I' jika terdapat PO yang menggunakan dan masih draft
                            UPDATE sl_so_po_balance_item A
                            SET update_datetime = pDatetime, update_user_id = pUserId, version = A.version + 1, status_item = vStatusInProgress
                            FROM sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D, pu_po_item E, pu_po F
                            WHERE A.so_item_id = B.so_item_id
                                AND B.manage_so_id = C.manage_so_id
                                AND A.so_item_id = D.so_item_id
                                AND B.manage_so_id = pCancelSoId
                                AND A.status_item = vStatusRelease
                                AND E.ref_id = D.so_item_id
                                AND F.po_id = E.po_id
                                AND F.status_doc = vStatusDraft;

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

                        ELSE
                            RAISE EXCEPTION 'Status of SO Balance with id % is not cancel', vSoId;
                        END IF;

                    ELSE
                        RAISE EXCEPTION 'Status of SO PO with id % is not cancel', vSoId;
                    END IF;

                ELSE
                    RAISE EXCEPTION 'Sales Order with id % is in use by other documnet', vSoId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Admin Process Ledger for Inventory in year month % is already closed', SUBSTR(vDoReceiptDocDate, 1, 6);
            END IF;

        ELSE
            RAISE EXCEPTION 'Admin Process Ledger for Inventory in year month % is already closed', SUBSTR(vDoDocDate, 1, 6);
        END IF;

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

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