1 PACKAGE BODY PN_INDEX_EXCLUDE_TERM_PKG AS
2 -- $Header: PNINXTRB.pls 120.3 2006/12/20 07:40:40 rdonthul ship $
3
4
5 -------------------------------------------------------------------------------
6 -- PROCDURE : INSERT_ROW
7 -- INVOKED FROM : insert_row procedure
8 -- PURPOSE : inserts the row
9 -- HISTORY :
10 -- 05-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_exclude_term with _ALL
11 -- table.Also added a check for org id.
12 --20-SEP-06 pseeram o Modified insert_row procedure to include
13 -- new column include_exclude_falg
14 -------------------------------------------------------------------------------
15 procedure INSERT_ROW
16 (
17 X_INDEX_EXCLUDE_TERM_ID IN OUT NOCOPY NUMBER
18 ,X_ORG_ID IN NUMBER
19 ,X_INDEX_LEASE_ID IN NUMBER
20 ,X_PAYMENT_TERM_ID IN NUMBER
21 ,X_LAST_UPDATE_DATE IN DATE
22 ,X_LAST_UPDATED_BY IN NUMBER
23 ,X_CREATION_DATE IN DATE
24 ,X_CREATED_BY IN NUMBER
25 ,X_LAST_UPDATE_LOGIN IN NUMBER
26 ,X_INCLUDE_EXCLUDE_FLAG IN VARCHAR2
27 )
28 IS
29 l_return_status VARCHAR2(30) := NULL;
30 l_rowId VARCHAR2(18) := NULL;
31 l_rowExists VARCHAR2(10) := NULL;
32
33 CURSOR org_cur IS
34 SELECT org_id FROM pn_index_leases_all WHERE index_lease_id = x_index_lease_id;
35 l_org_ID NUMBER;
36 BEGIN
37
38 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.insert_row (+)');
39
40 /* If no INDEX_EXCLUDE_TERM_ID is provided, get one from sequence */
41
42 BEGIN
43 SELECT '1'
44 INTO l_rowExists
45 FROM DUAL
46 WHERE EXISTS ( SELECT 1
47 FROM pn_index_exclude_term_all exclude
48 WHERE exclude.index_lease_id = x_index_lease_id
49 AND exclude.payment_term_id = x_payment_term_id
50 AND exclude.org_id = x_org_id);
51
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 l_rowExists := '0';
55 END;
56
57
58 IF l_rowExists = '0' THEN
59
60 IF x_org_id IS NULL THEN
61 FOR rec IN org_cur LOOP
62 l_org_id := rec.org_id;
63 END LOOP;
64 ELSE
65 l_org_id := x_org_id;
66 END IF;
67
68 IF (X_INDEX_EXCLUDE_TERM_ID IS NULL) THEN
69 SELECT PN_INDEX_EXCLUDE_TERM_s.nextval
70 INTO X_INDEX_EXCLUDE_TERM_ID
71 FROM dual;
72 END IF;
73
74 INSERT INTO PN_INDEX_EXCLUDE_TERM_ALL
75 (
76 INDEX_EXCLUDE_TERM_ID
77 ,ORG_ID
78 ,INDEX_LEASE_ID
79 ,PAYMENT_TERM_ID
80 ,LAST_UPDATE_DATE
81 ,LAST_UPDATED_BY
82 ,CREATION_DATE
83 ,CREATED_BY
84 ,LAST_UPDATE_LOGIN
85 ,INCLUDE_EXCLUDE_FLAG
86 )
87 VALUES
88 (
89 X_INDEX_EXCLUDE_TERM_ID
90 ,l_org_id
91 ,X_INDEX_LEASE_ID
92 ,X_PAYMENT_TERM_ID
93 ,X_LAST_UPDATE_DATE
94 ,X_LAST_UPDATED_BY
95 ,X_CREATION_DATE
96 ,X_CREATED_BY
97 ,X_LAST_UPDATE_LOGIN
98 ,X_INCLUDE_EXCLUDE_FLAG
99 );
100
101 END IF;
102
103 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.insert_row (-)');
104
105 END INSERT_ROW;
106
107
108
109 -------------------------------------------------------------------------------
110 -- PROCDURE : UPDATE_ROW
111 -- INVOKED FROM : update_row procedure
112 -- PURPOSE : updates the row
113 -- HISTORY :
114 -- 05-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_exclude_term with _ALL
115 -- table.
116 -- 20-SEP-06 pseeram o Modified update_row procedure to include
117 -- new column include_exclude_falg
118 -------------------------------------------------------------------------------
119 procedure UPDATE_ROW
120 (
121 X_INDEX_EXCLUDE_TERM_ID IN NUMBER
122 ,X_INDEX_LEASE_ID IN NUMBER
123 ,X_PAYMENT_TERM_ID IN NUMBER
124 ,X_LAST_UPDATE_DATE IN DATE
125 ,X_LAST_UPDATED_BY IN NUMBER
126 ,X_LAST_UPDATE_LOGIN IN NUMBER
127 ,X_INCLUDE_EXCLUDE_FLAG IN VARCHAR2
128 )
129 IS
130 l_return_status VARCHAR2(30) := NULL;
131
132 BEGIN
133
134 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.update_row (+)');
135
136 IF (l_return_status IS NOT NULL) THEN
137 APP_EXCEPTION.Raise_Exception;
138 END IF;
139
140 UPDATE PN_INDEX_EXCLUDE_TERM_ALL
141 SET
142 INDEX_LEASE_ID =X_INDEX_LEASE_ID
143 ,PAYMENT_TERM_ID =X_PAYMENT_TERM_ID
144 ,LAST_UPDATE_DATE =X_LAST_UPDATE_DATE
145 ,LAST_UPDATED_BY =X_LAST_UPDATED_BY
146 ,LAST_UPDATE_LOGIN =X_LAST_UPDATE_LOGIN
147 ,INCLUDE_EXCLUDE_FLAG =X_INCLUDE_EXCLUDE_FLAG
148 WHERE INDEX_EXCLUDE_TERM_ID = X_INDEX_EXCLUDE_TERM_ID;
149
150 IF (sql%notfound) THEN
151 RAISE NO_DATA_FOUND ;
152 END IF;
153 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.update_row (-)');
154
155 END update_row;
156
157 -------------------------------------------------------------------------------
158 -- PROCDURE : LOCK_ROW
159 -- INVOKED FROM : LOCK_ROW procedure
160 -- PURPOSE : locks the row
161 -- HISTORY :
162 -- 05-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_exclude_term with _ALL
163 -- table.
164 --20-SEP-06 pseeram o Modified lock_row procedure to include
165 -- new column include_exclude_falg
166 -------------------------------------------------------------------------------
167 procedure LOCK_ROW
168 (
169 X_INDEX_EXCLUDE_TERM_ID IN NUMBER
170 ,X_INDEX_LEASE_ID IN NUMBER
171 ,X_PAYMENT_TERM_ID IN NUMBER
172 ,X_INCLUDE_EXCLUDE_FLAG IN VARCHAR2
173 )
174 IS
175
176 CURSOR c1 IS
177 SELECT * FROM PN_INDEX_EXCLUDE_TERM_ALL
178 WHERE INDEX_EXCLUDE_TERM_ID = X_INDEX_EXCLUDE_TERM_ID
179 FOR UPDATE OF INDEX_EXCLUDE_TERM_ID NOWAIT;
180
181 tlinfo c1%ROWTYPE;
182
183 BEGIN
184
185 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.lock_row (+)');
186
187 OPEN c1;
188 FETCH c1 INTO tlinfo;
189 IF (c1%notfound) THEN
190 CLOSE c1;
191 RETURN;
192 END IF;
193 CLOSE c1;
194
195 IF NOT (tlinfo.INDEX_EXCLUDE_TERM_ID = X_INDEX_EXCLUDE_TERM_ID) THEN
196 pn_var_rent_pkg.lock_row_exception('INDEX_EXCLUDE_TERM_ID',tlinfo.index_exclude_term_id);
197 END IF;
198
199 IF NOT (tlinfo.INDEX_LEASE_ID = X_INDEX_LEASE_ID) THEN
200 pn_var_rent_pkg.lock_row_exception('INDEX_LEASE_ID',tlinfo.index_lease_id);
201 END IF;
202
203 IF NOT (tlinfo.PAYMENT_TERM_ID = X_PAYMENT_TERM_ID) THEN
204 pn_var_rent_pkg.lock_row_exception('PAYMENT_TERM_ID',tlinfo.payment_term_id);
205 END IF;
206
207 IF NOT (tlinfo.INCLUDE_EXCLUDE_FLAG = X_INCLUDE_EXCLUDE_FLAG) THEN
208 pn_var_rent_pkg.lock_row_exception('INCLUDE_EXCLUDE_FLAG',tlinfo.include_exclude_flag);
209 END IF;
210
211 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.lock_row (-)');
212 END LOCK_ROW;
213
214
215
216 -------------------------------------------------------------------------------
217 -- PROCDURE : delete_row
218 -- INVOKED FROM : delete_row procedure
219 -- PURPOSE : deletes the row
220 -- HISTORY :
221 -- 05-JUL-05 hrodda o Bug 4284035 - Replaced pn_index_exclude_term with _ALL
222 -- table.
223 -------------------------------------------------------------------------------
224
225 procedure delete_row
226 (
227 X_INDEX_LEASE_ID IN NUMBER
228 ,X_PAYMENT_TERM_ID IN NUMBER
229 )
230 IS
231
232 l_rowExists VARCHAR2(10) := NULL;
233
234 BEGIN
235
236 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.delete_row (+)');
237
238 BEGIN
239 SELECT '1'
240 INTO l_rowExists
241 FROM DUAL
242 WHERE EXISTS ( SELECT 1
243 FROM pn_index_exclude_term_all exclude
244 WHERE exclude.index_lease_id = x_index_lease_id
245 AND exclude.payment_term_id = x_payment_term_id);
246
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 l_rowExists := '0';
250 END;
251
252 IF l_rowExists = '1' THEN
253
254 DELETE FROM PN_INDEX_EXCLUDE_TERM_ALL
255 WHERE INDEX_LEASE_ID = X_INDEX_LEASE_ID
256 AND PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;
257
258 IF (sql%notfound) THEN
259 RAISE NO_DATA_FOUND;
260 END IF;
261
262 END IF;
263
264 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.delete_row (-)');
265
266 END delete_row;
267
268 -------------------------------------------------------------------------------
269 -- PROCDURE : DELETE_ALL_EXCLUDE_TERMS
270 -- INVOKED FROM : DELETE_ALL_EXCLUDE_TERMS_THR procedure
271 -- PURPOSE : deletes all the rows corresponding to a particular index-lease-id
272 -- HISTORY :
273 -- 03-OCT-06 prabhakar o Created
274 -------------------------------------------------------------------------------
275
276 procedure DELETE_ALL_EXCLUDE_TERMS( X_INDEX_LEASE_ID IN NUMBER )
277 IS
278
279 BEGIN
280
281 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.DELETE_ALL_EXCLUDE_TERMS (+)');
282
283 DELETE FROM PN_INDEX_EXCLUDE_TERM_ALL
284 WHERE INDEX_LEASE_ID = X_INDEX_LEASE_ID;
285
286 PNP_DEBUG_PKG.debug (' PN_INDEX_EXCLUDE_TERM_PKG.DELETE_ALL_EXCLUDE_TERMS (-)');
287
288 END DELETE_ALL_EXCLUDE_TERMS;
289
290 END PN_INDEX_EXCLUDE_TERM_PKG;
[Home] [Help]