1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132
| PROCEDURE SET_INV (
V_ROLLOUT_CODE IN INTEGER,
V_PLANNED IN VARCHAR2,
V_PLANNED_YEAR IN INTEGER,
V_PLANNED_MONTH IN INTEGER,
V_REMARKS IN VARCHAR2,
V_RESPONSE_ID OUT INTEGER,
V_RESPONSE_DESC OUT VARCHAR2
/* Parameters description :
** 1. V_ROLLOUT_CODE: a. record Code in the rollout_inv table
** b. mandatory parameter
** c. MUST already exist ! (No insert allowed into rollout_inv, only updates)
**
** 1bis. V_PLANNED: a. mandatory !
b. (YES, NO) -> (1,0) : 0 = not planned => V_REMARKS mandatory
1 = planned => V_PLANNED_YEAR mandatory
**
** 2. V_PLANNED_YEAR: a. if the status of the record (designed by its code) in the rollout table
** is 'PLANNED', then this parameter is mandatory
** b. MUST BE equal or higher than current year
**
** 3. V_PLANNED_MONTH: a. not mandatory
** b. must be in [1..12]
**
** 4. V_REMARKS: a. remarks on planification : NEEDED IF the status of 2.a is different from 'PLANNED'
** b. must be < 100 characters
**
** 5. V_RESPONSE_ID: a. 0 = all went OK
1 = validation problem : one or more of the previous points are not satisfied
2 = DB problem
** 6. V_RESPONSE_DESC: a. Description of the response (complementary of above code)
b. must be < 200 characters
** End of paramters description */
) IS
-- Invalid parameters exception
V_VALIDATION_ERROR EXCEPTION;
-- Checking point 1.c we will count the number of records with code=V_ROLLOUT_CODE and put the result into this variable
V_VALID_CODE INTEGER:=0;
BEGIN
/* Validation tests */
-- 1.b
IF (V_ROLLOUT_CODE IS NULL) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'Parameter RolloutCODE is mandatory !';
RAISE V_VALIDATION_ERROR;
END IF;
-- 1.c
SELECT COUNT(*) INTO V_VALID_CODE FROM SMART.V_ROLLOUT_INV WHERE V_ROLLOUT_INV.CODE = V_ROLLOUT_CODE;
IF(V_VALID_CODE<>1) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'There is no record with code '||V_ROLLOUT_CODE||' in the Rollout_INV table !';
RAISE V_VALIDATION_ERROR;
END IF;
-- 1bis.a
IF (V_PLANNED IS NULL) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'Parameters V_PLANNED is mandatory !';
RAISE V_VALIDATION_ERROR;
END IF;
-- 1bis.b (V_PLANNED other than 'YES' or 'NO')
IF (V_PLANNED <> 'YES') AND (V_PLANNED <> 'NO') THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'Parameters V_PLANNED is not recognized : must be YES or NO !';
RAISE V_VALIDATION_ERROR;
END IF;
-- 2.a
IF(V_PLANNED = 'YES') AND (V_PLANNED_YEAR IS NULL) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'The status of the record with code '||V_ROLLOUT_CODE||' is PLANNED : PLANNED_YEAR argument is mandatory !';
RAISE V_VALIDATION_ERROR;
END IF;
-- 2.b
IF(V_PLANNED_YEAR < extract(year from sysdate)) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'The input PLANNED_YEAR for the record with code '||V_ROLLOUT_CODE||' must be equal or higher than '||extract(year from sysdate)||'!';
RAISE V_VALIDATION_ERROR;
END IF;
--3.b
IF(V_PLANNED_MONTH IS NOT NULL) THEN
IF (V_PLANNED_MONTH < 1) OR (V_PLANNED_MONTH>12) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'The input PLANNED_MONTH for the record with code '||V_ROLLOUT_CODE||' must be in [1..12]!';
RAISE V_VALIDATION_ERROR;
END IF;
END IF;
--4.a
IF(V_PLANNED = 'NO') AND (V_REMARKS IS NULL) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'The status of the record with code '||V_ROLLOUT_CODE||' is not PLANNED : REMARKS argument is mandatory !';
RAISE V_VALIDATION_ERROR;
END IF;
--4.b
V_VALID_CODE := LENGTH(V_REMARKS);
IF(V_REMARKS IS NOT NULL) AND (LENGTH(V_REMARKS)>100) THEN
V_RESPONSE_ID := 1;
V_RESPONSE_DESC := 'For the record with code '||V_ROLLOUT_CODE||', the argument REMARKS must be < to 100 characters !';
RAISE V_VALIDATION_ERROR;
END IF;
/* End of validation tests */
-- Update
UPDATE V_ROLLOUT_INV
SET MONTH = V_PLANNED_MONTH, YEAR = V_PLANNED_YEAR, REMARKS = V_REMARKS
WHERE CODE = V_ROLLOUT_CODE;
-- Committing updates
COMMIT;
-- Output parameters setting
V_RESPONSE_ID := 0;
V_RESPONSE_DESC := 'The record with code '||V_ROLLOUT_CODE||' was updated properly.';
DBMS_OUTPUT.put_line('Successful update ['||V_RESPONSE_ID||'] : '||V_RESPONSE_DESC);
EXCEPTION
WHEN V_VALIDATION_ERROR THEN
DBMS_OUTPUT.put_line('Validation Error ['||V_RESPONSE_ID||'] : '||V_RESPONSE_DESC);
ROLLBACK;
WHEN OTHERS THEN
V_RESPONSE_ID := 2;
V_RESPONSE_DESC := 'For the record with code '||V_ROLLOUT_CODE||' : '||SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.put_line('Procedure Error ['||V_RESPONSE_ID||'] : '||V_RESPONSE_DESC);
ROLLBACK;
RAISE;
END SET_INV; |
Partager