1 PACKAGE BODY OKE_APPROVAL_STEPS_PKG AS
2 /* $Header: OKEAPVEB.pls 120.1 2005/06/02 12:03:51 appldev $ */
3
4 PROCEDURE INSERT_ROW
5 ( X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6 , X_APPROVAL_PATH_ID IN NUMBER
7 , X_APPROVAL_SEQUENCE IN NUMBER
8 , X_APPROVER_ROLE_ID IN NUMBER
9 , X_REQUIRED_FLAG IN VARCHAR2
10 , X_CREATION_DATE IN DATE
11 , X_CREATED_BY IN NUMBER
12 , X_LAST_UPDATE_DATE IN DATE
13 , X_LAST_UPDATED_BY IN NUMBER
14 , X_LAST_UPDATE_LOGIN IN NUMBER
15 , X_RECORD_VERSION_NUMBER IN OUT NOCOPY /* file.sql.39 change */ NUMBER
16 ) IS
17
18 CURSOR c IS
19 SELECT ROWID
20 FROM OKE_APPROVAL_STEPS
21 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
22 AND APPROVAL_SEQUENCE = X_APPROVAL_SEQUENCE;
23
24 BEGIN
25
26 X_RECORD_VERSION_NUMBER := 1;
27
28 INSERT INTO OKE_APPROVAL_STEPS
29 ( APPROVAL_PATH_ID
30 , APPROVAL_SEQUENCE
31 , APPROVER_ROLE_ID
32 , REQUIRED_FLAG
33 , RECORD_VERSION_NUMBER
34 , CREATION_DATE
35 , CREATED_BY
36 , LAST_UPDATE_DATE
37 , LAST_UPDATED_BY
38 , LAST_UPDATE_LOGIN
39 ) VALUES
40 ( X_APPROVAL_PATH_ID
41 , X_APPROVAL_SEQUENCE
42 , X_APPROVER_ROLE_ID
43 , X_REQUIRED_FLAG
44 , X_RECORD_VERSION_NUMBER
45 , X_CREATION_DATE
46 , X_CREATED_BY
47 , X_LAST_UPDATE_DATE
48 , X_LAST_UPDATED_BY
49 , X_LAST_UPDATE_LOGIN
50 );
51
52 OPEN c;
53 FETCH c INTO X_ROWID;
54 IF (c%notfound) THEN
55 CLOSE c;
56 RAISE NO_DATA_FOUND;
57 END IF;
58 CLOSE c;
59
60 END INSERT_ROW;
61
62 PROCEDURE LOCK_ROW
63 ( X_APPROVAL_PATH_ID IN NUMBER
64 , X_APPROVAL_SEQUENCE IN NUMBER
65 , X_RECORD_VERSION_NUMBER IN NUMBER
66 ) IS
67
68 CURSOR c IS
69 SELECT RECORD_VERSION_NUMBER
70 FROM OKE_APPROVAL_STEPS
71 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
72 AND APPROVAL_SEQUENCE = X_APPROVAL_SEQUENCE
73 FOR UPDATE OF APPROVAL_PATH_ID NOWAIT;
74 RecInfo c%rowtype;
75
76 BEGIN
77 OPEN c;
78 FETCH c INTO RecInfo;
79 IF (c%notfound) THEN
80 CLOSE c;
81 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
82 APP_EXCEPTION.RAISE_EXCEPTION;
83 END IF;
84 CLOSE c;
85 IF (RecInfo.RECORD_VERSION_NUMBER = X_RECORD_VERSION_NUMBER) THEN
86 NULL;
87 ELSE
88 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
89 APP_EXCEPTION.RAISE_EXCEPTION;
90 END IF;
91
92 RETURN;
93
94 END LOCK_ROW;
95
96 PROCEDURE UPDATE_ROW
97 ( X_APPROVAL_PATH_ID IN NUMBER
98 , X_APPROVAL_SEQUENCE IN NUMBER
99 , X_APPROVER_ROLE_ID IN NUMBER
100 , X_REQUIRED_FLAG IN VARCHAR2
101 , X_LAST_UPDATE_DATE IN DATE
102 , X_LAST_UPDATED_BY IN NUMBER
103 , X_LAST_UPDATE_LOGIN IN NUMBER
104 , X_RECORD_VERSION_NUMBER IN OUT NOCOPY /* file.sql.39 change */ NUMBER
105 ) IS
106
107 CURSOR c IS
108 SELECT RECORD_VERSION_NUMBER
109 FROM OKE_APPROVAL_STEPS
110 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
111 AND APPROVAL_SEQUENCE = X_APPROVAL_SEQUENCE;
112
113 BEGIN
114 UPDATE OKE_APPROVAL_STEPS
115 SET APPROVER_ROLE_ID = X_APPROVER_ROLE_ID
116 , REQUIRED_FLAG = X_REQUIRED_FLAG
117 , LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
118 , LAST_UPDATED_BY = X_LAST_UPDATED_BY
119 , LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
120 , RECORD_VERSION_NUMBER = RECORD_VERSION_NUMBER + 1
121 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
122 AND APPROVAL_SEQUENCE = X_APPROVAL_SEQUENCE;
123
124 IF (sql%notfound) THEN
125 RAISE NO_DATA_FOUND;
126 END IF;
127
128 END UPDATE_ROW;
129
130 PROCEDURE DELETE_ROW
131 ( X_APPROVAL_PATH_ID IN NUMBER
132 , X_APPROVAL_SEQUENCE IN NUMBER
133 ) IS
134 BEGIN
135
136 DELETE FROM OKE_APPROVAL_STEPS
137 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
138 AND APPROVAL_SEQUENCE = X_APPROVAL_SEQUENCE;
139
140 IF (sql%notfound) THEN
141 RAISE NO_DATA_FOUND;
142 END IF;
143
144 END DELETE_ROW;
145
146 PROCEDURE LOAD_ROW
147 ( X_APPROVAL_PATH_ID IN NUMBER
148 , X_APPROVAL_SEQUENCE IN NUMBER
149 , X_APPROVER_ROLE_ID IN NUMBER
150 , X_REQUIRED_FLAG IN VARCHAR2
151 , X_LAST_UPDATE_DATE IN DATE
152 , X_LAST_UPDATED_BY IN NUMBER
153 ) IS
154 BEGIN
155
156 INSERT INTO OKE_APPROVAL_STEPS
157 ( APPROVAL_PATH_ID
158 , APPROVAL_SEQUENCE
159 , APPROVER_ROLE_ID
160 , REQUIRED_FLAG
161 , RECORD_VERSION_NUMBER
162 , CREATION_DATE
163 , CREATED_BY
164 , LAST_UPDATE_DATE
165 , LAST_UPDATED_BY
166 , LAST_UPDATE_LOGIN
167 )
168 SELECT X_APPROVAL_PATH_ID
169 , X_APPROVAL_SEQUENCE
170 , X_APPROVER_ROLE_ID
171 , X_REQUIRED_FLAG
172 , 1
173 , X_LAST_UPDATE_DATE
174 , X_LAST_UPDATED_BY
175 , X_LAST_UPDATE_DATE
176 , X_LAST_UPDATED_BY
177 , NULL
178 FROM DUAL
179 WHERE NOT EXISTS (
180 SELECT NULL
181 FROM OKE_APPROVAL_STEPS
182 WHERE APPROVAL_PATH_ID = X_APPROVAL_PATH_ID
183 AND ( APPROVAL_SEQUENCE = X_APPROVAL_SEQUENCE
184 OR APPROVER_ROLE_ID = X_APPROVER_ROLE_ID )
185 );
186
187 END LOAD_ROW;
188
189 END OKE_APPROVAL_STEPS_PKG;
[Home] [Help]