[Home] [Help]
PACKAGE BODY: APPS.PN_INDEX_LEASE_TERMS_PKG
Source
1 PACKAGE BODY PN_INDEX_LEASE_TERMS_PKG AS
2 -- $Header: PNILTRHB.pls 120.3 2005/11/30 21:36:01 appldev noship $
3
4 -- +==========================================================================+
5 -- | Copyright (c) 2001 Oracle Corporation
6 -- | Redwood Shores, California, USA
7 -- | All rights reserved.
8 -- +==========================================================================+
9 -- | Name
10 -- | PN_INDEX_LEASE_TERMS_PKG
11 -- |
12 -- | Description
13 -- | This package contains row handler procedures to populate
14 -- | PN_INDEX_LEASE_TERMS_ALL.
15 -- |
16 -- | History
17 -- | 05-dec-2001 achauhan Created
18 -- | 15-JAN-2002 Mrinal Misra Added dbdrv command.
19 -- | 01-FEB-2002 Mrinal Misra Added checkfile command.
20 -- | 14-JUL-2005 SatyaDeep Replaced bases views by _ALL table
21 -- +==========================================================================+
22
23
24 -------------------------------------------------------------------------------
25 -- PROCDURE : INSERT_ROW
26 -- INVOKED FROM : insert_row procedure
27 -- PURPOSE : inserts the row
28 -- HISTORY :
29 -- 14-jul-05 sdmahesh o Bug 4284035 - Replaced pn_index_lease_terms with
30 -- _ALL table.
31 -- 12-Nov-05 HRodda o Bug 4734542 Modified select statement to select
32 -- org_id from index_leases_all instead of
33 -- pn_index_lease_terms_all.
34 -- 28-NOV-05 pikhar o fetched org_id using cursor
35 -------------------------------------------------------------------------------
36 procedure INSERT_ROW
37 (
38 X_INDEX_LEASE_TERM_ID IN OUT NOCOPY NUMBER
39 ,X_INDEX_LEASE_ID IN NUMBER
40 ,X_INDEX_PERIOD_ID IN NUMBER
41 ,X_LEASE_TERM_ID IN NUMBER
42 ,X_RENT_INCREASE_TERM_ID IN NUMBER
43 ,X_AMOUNT IN NUMBER
44 ,X_APPROVED_FLAG IN VARCHAR2
45 ,X_INDEX_TERM_INDICATOR IN VARCHAR2
46 ,X_LAST_UPDATE_DATE IN DATE
47 ,X_LAST_UPDATED_BY IN NUMBER
48 ,X_CREATION_DATE IN DATE
49 ,X_CREATED_BY IN NUMBER
50 ,X_LAST_UPDATE_LOGIN IN NUMBER
51 ) IS
52
53 l_return_status VARCHAR2(30) := NULL;
54 l_rowId VARCHAR2(18) := NULL;
55 l_rowExists VARCHAR2(10) := NULL;
56 l_org_id NUMBER;
57
58 CURSOR org_cur IS
59 SELECT org_id
60 FROM pn_index_leases_all
61 WHERE index_lease_id = x_index_lease_id;
62
63
64 BEGIN
65
66 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.insert_row (+)');
67
68 FOR rec IN org_cur LOOP
69 l_org_id := rec.org_id;
70 END LOOP;
71
72 -- If no INDEX_LEASE_TERM_ID is provided, get one from sequence
73
74
75 BEGIN
76 SELECT '1'
77 INTO l_rowExists
78 FROM DUAL
79 WHERE EXISTS (
80 SELECT 1
81 FROM pn_index_lease_terms_all ilt
82 WHERE ilt.index_lease_id = x_index_lease_id
83 AND ilt.index_period_id = x_index_period_id
84 AND ilt.lease_term_id = x_lease_term_id
85 AND ilt.rent_increase_term_id = x_rent_increase_term_id);
86 EXCEPTION
87 WHEN NO_DATA_FOUND
88 THEN
89 l_rowExists := '0';
90 END;
91
92
93 IF l_rowExists = '0' THEN
94
95 IF (X_INDEX_LEASE_TERM_ID IS NULL) THEN
96 SELECT PN_INDEX_LEASE_TERM_S.nextval into X_INDEX_LEASE_TERM_ID from dual;
97 END IF;
98
99 INSERT INTO PN_INDEX_LEASE_TERMS_ALL
100 (
101 index_lease_term_id
102 ,index_lease_id
103 ,index_period_id
104 ,lease_term_id
105 ,rent_increase_term_id
106 ,amount
107 ,approved_flag
108 ,index_term_indicator
109 ,last_update_date
110 ,last_updated_by
111 ,creation_date
112 ,created_by
113 ,last_update_login
114 ,org_id
115 )
116 VALUES
117 (
118 x_index_lease_term_id
119 ,x_index_lease_id
120 ,x_index_period_id
121 ,x_lease_term_id
122 ,x_rent_increase_term_id
123 ,x_amount
124 ,x_approved_flag
125 ,x_index_term_indicator
126 ,x_last_update_date
127 ,x_last_updated_by
128 ,x_creation_date
129 ,x_created_by
130 ,x_last_update_login
131 ,l_org_id
132 );
133
134 END IF;
135 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.insert_row (-)');
136 END INSERT_ROW;
137
138
139
140 -------------------------------------------------------------------------------
141 -- PROCDURE : UPDATE_ROW
142 -- INVOKED FROM : update_row procedure
143 -- PURPOSE : updates the row
144 -- HISTORY :
145 -- 14-jul-05 sdmahesh o Bug 4284035 - Replaced pn_index_lease_terms with _ALL table.
146 ------------------------------------------------------------------------------
147 procedure UPDATE_ROW
148 (
149 X_INDEX_LEASE_TERM_ID IN NUMBER
150 ,X_INDEX_LEASE_ID IN NUMBER
151 ,X_INDEX_PERIOD_ID IN NUMBER
152 ,X_LEASE_TERM_ID IN NUMBER
153 ,X_RENT_INCREASE_TERM_ID IN NUMBER
154 ,X_AMOUNT IN NUMBER
155 ,X_APPROVED_FLAG IN VARCHAR2
156 ,X_INDEX_TERM_INDICATOR IN VARCHAR2
157 ,X_LAST_UPDATE_DATE IN DATE
158 ,X_LAST_UPDATED_BY IN NUMBER
159 ,X_LAST_UPDATE_LOGIN IN NUMBER
160 ) IS
161
162 BEGIN
163
164 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.update_row (+)');
165
166 UPDATE PN_INDEX_LEASE_TERMS_ALL
167 SET
168 INDEX_LEASE_TERM_ID = X_INDEX_LEASE_TERM_ID
169 ,INDEX_LEASE_ID = X_INDEX_LEASE_ID
170 ,INDEX_PERIOD_ID = X_INDEX_PERIOD_ID
171 ,LEASE_TERM_ID = X_LEASE_TERM_ID
172 ,RENT_INCREASE_TERM_ID = X_RENT_INCREASE_TERM_ID
173 ,AMOUNT = X_AMOUNT
174 ,APPROVED_FLAG = X_APPROVED_FLAG
175 ,INDEX_TERM_INDICATOR = X_INDEX_TERM_INDICATOR
176 ,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
177 ,LAST_UPDATED_BY = X_LAST_UPDATED_BY
178 ,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
179 WHERE INDEX_LEASE_TERM_ID = X_INDEX_LEASE_TERM_ID;
180
181 if (sql%notfound) then raise no_data_found ; end if;
182
183 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.update_row (-)');
184 end update_row;
185
186
187
188
189 -------------------------------------------------------------------------------
190 -- PROCDURE : LOCK_ROW
191 -- INVOKED FROM : lock_row procedure
192 -- PURPOSE : locks the row
193 -- HISTORY :
194 -- 14-jul-05 sdmahesh o Bug 4284035 - Replaced pn_index_lease_terms with _ALL table.
195 ------------------------------------------------------------------------------
196
197 procedure LOCK_ROW
198 (
199 X_INDEX_LEASE_TERM_ID IN NUMBER
200 ,X_INDEX_LEASE_ID IN NUMBER
201 ,X_INDEX_PERIOD_ID IN NUMBER
202 ,X_LEASE_TERM_ID IN NUMBER
203 ,X_RENT_INCREASE_TERM_ID IN NUMBER
204 ,X_AMOUNT IN NUMBER
205 ,X_APPROVED_FLAG IN VARCHAR2
206 ,X_INDEX_TERM_INDICATOR IN VARCHAR2
207 ) IS
208 CURSOR c1 IS
209 SELECT *
210 FROM PN_INDEX_LEASE_TERMS_ALL
211 WHERE INDEX_LEASE_TERM_ID = X_INDEX_LEASE_TERM_ID
212 FOR UPDATE OF INDEX_LEASE_TERM_ID NOWAIT;
213 tlinfo c1%ROWTYPE;
214
215 BEGIN
216 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.lock_row (+)');
217 open c1; fetch c1 into tlinfo; if (c1%notfound) then close c1; return; end if; close c1;
218 if (
219 tlinfo.INDEX_LEASE_TERM_ID = X_INDEX_LEASE_TERM_ID
220 AND tlinfo.INDEX_LEASE_ID = X_INDEX_LEASE_ID
221 AND tlinfo.INDEX_PERIOD_ID = X_INDEX_PERIOD_ID
222 AND tlinfo.LEASE_TERM_ID = X_LEASE_TERM_ID
223 AND tlinfo.RENT_INCREASE_TERM_ID = X_RENT_INCREASE_TERM_ID
224 AND tlinfo.AMOUNT = X_AMOUNT
225 AND tlinfo.APPROVED_FLAG = X_APPROVED_FLAG
226 AND tlinfo.INDEX_TERM_INDICATOR = X_INDEX_TERM_INDICATOR
227 )
228 then null; ELSE
229 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED'); app_exception.raise_exception;
230 end if;
231 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.lock_row (-)');
232 end LOCK_ROW;
233
234
235
236 -------------------------------------------------------------------------------
237 -- PROCDURE : DELETE_ROW
238 -- INVOKED FROM : delete_row procedure
239 -- PURPOSE : deletes the row
240 -- HISTORY :
241 -- 14-jul-05 sdmahesh o Bug 4284035 - Replaced pn_index_lease_terms with
242 -- _ALL table.
243 ------------------------------------------------------------------------------
244
245 procedure delete_row
246 (
247 X_INDEX_LEASE_TERM_ID IN NUMBER
248 ,X_INDEX_LEASE_ID IN NUMBER
249 ,X_INDEX_PERIOD_ID IN NUMBER
250 ,X_LEASE_TERM_ID IN NUMBER
251 ,X_RENT_INCREASE_TERM_ID IN NUMBER
252 ) IS
253
254 l_rowExists VARCHAR2(10) := NULL;
255
256 BEGIN
257 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.delete_row (+)');
258
259 BEGIN
260 SELECT '1'
261 INTO l_rowExists
262 FROM DUAL
263 WHERE EXISTS (
264 SELECT 1
265 FROM pn_index_lease_terms_all ilt
266 WHERE ilt.index_lease_id = x_index_lease_id
267 AND ilt.index_period_id = x_index_period_id
268 AND ilt.lease_term_id = x_lease_term_id
269 AND ilt.rent_increase_term_id = x_rent_increase_term_id);
270
271 EXCEPTION
272 WHEN NO_DATA_FOUND
273 THEN
274 l_rowExists := '0';
275 END;
276
277 IF l_rowExists = '1' THEN
278
279 DELETE FROM pn_index_lease_terms_all ilt
280 WHERE ilt.index_lease_id = x_index_lease_id
281 AND ilt.index_period_id = x_index_period_id
282 AND ilt.lease_term_id = x_lease_term_id
283 AND ilt.rent_increase_term_id = x_rent_increase_term_id;
284
285 if (sql%notfound) then
286 raise no_data_found;
287 end if;
288
289 END IF;
290 PNP_DEBUG_PKG.debug (' PN_INDEX_LEASE_TERMS_PKG.delete_row (-)');
291 END delete_row;
292
293
294 END PN_INDEX_LEASE_TERMS_PKG;