[Home] [Help]
PACKAGE BODY: APPS.PN_RECOVERY_TENANCY_UPG_PKG
Source
1 PACKAGE BODY pn_recovery_tenancy_upg_pkg AS
2 -- $Header: PNUPTENB.pls 120.3 2007/01/24 12:23:03 rdonthul ship $
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE : tenancy_upgrade_batch
6 -- Referenced in the Concurrent Program executable definition.
7 --
8 -- HISTORY
9 -- 19-Jun-2003 Pooja Sidhu o Created.
10 -- 22-AUG-2003 Satish Tripathi o Fixed for BUG# 3111676, Added parameter p_action
11 -- when calling create_auto_space_assign.
12 -- 06-NOV-2003 Satish Tripathi o Modified for Multi Primary Tenancy Enhancement.
13 -- Modified CURSOR c_mass_tenancies to Create assignments
14 -- for all tenancies of Sublease/Third Party Leases.
15 -- 25-APR-04 atuppad o Changed the cursor c_mass_tenancies to get the
16 -- NVL(occupancy_date, estimated_occupancy_date) rather
17 -- than direct estimated_occupancy_date. Bug#3592232
18 -- 15-JUL-05 hareesha o Bug 4284035 - Replaced pn_tenancies with _ALL table.
19 ---------------------------------------------------------------------------
20 PROCEDURE tenancy_upgrade_batch ( errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY VARCHAR2,
22 p_lease_num_from IN VARCHAR2,
23 p_lease_num_to IN VARCHAR2,
24 p_rec_space_std_code IN VARCHAR2,
25 p_rec_type_code IN VARCHAR2,
26 p_upd_customer IN VARCHAR2,
27 p_upd_fin_oblg_end_dt IN VARCHAR2) IS
28
29 CURSOR c_cust_info(p_lease_id NUMBER) IS
30 SELECT customer_id,
31 customer_site_use_id
32 FROM pn_payment_terms_all
33 WHERE lease_id = p_lease_id
34 AND rownum = 1;
35
36 CURSOR c_mass_tenancies IS
37 SELECT pt.tenancy_id,
38 pt.customer_id,
39 pt.customer_site_use_id,
40 pt.fin_oblig_end_date,
41 pt.allocated_area_pct,
42 pt.recovery_type_code,
43 pt.recovery_space_std_code,
44 NVL(pt.occupancy_date, pt.estimated_occupancy_date),
45 pt.expiration_date,
46 pt.lease_id,
47 pt.location_id,
48 pt.org_id
49 FROM pn_tenancies_all pt,
50 pn_leases pl
51 WHERE pt.lease_id = pl.lease_id
52 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
53 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
54 AND pl.lease_class_code IN ('SUB_LEASE', 'THIRD_PARTY')
55 AND NOT EXISTS (SELECT NULL
56 FROM pn_space_assign_cust_all psc
57 WHERE psc.tenancy_id = pt.tenancy_id);
58
59 TYPE t_tenancy_id IS
60 TABLE OF PN_TENANCIES_ALL.TENANCY_ID%TYPE
61 INDEX BY BINARY_INTEGER;
62
63 TYPE t_cust_id IS
64 TABLE OF PN_TENANCIES_ALL.CUSTOMER_ID%TYPE
65 INDEX BY BINARY_INTEGER;
66
67 TYPE t_cust_site_use_id IS
68 TABLE OF PN_TENANCIES_ALL.CUSTOMER_SITE_USE_ID%TYPE
69 INDEX BY BINARY_INTEGER;
70
71 TYPE t_fin_oblig_end_date IS
72 TABLE OF PN_TENANCIES_ALL.FIN_OBLIG_END_DATE%TYPE
73 INDEX BY BINARY_INTEGER;
74
75 TYPE t_allocated_area_pct IS
76 TABLE OF PN_TENANCIES_ALL.ALLOCATED_AREA_PCT%TYPE
77 INDEX BY BINARY_INTEGER;
78
79 TYPE t_recovery_type_code IS
80 TABLE OF PN_TENANCIES_ALL.RECOVERY_TYPE_CODE%TYPE
81 INDEX BY BINARY_INTEGER;
82
83 TYPE t_recovery_space_std_code IS
84 TABLE OF PN_TENANCIES_ALL.RECOVERY_SPACE_STD_CODE%TYPE
85 INDEX BY BINARY_INTEGER;
86
87 TYPE t_estimated_occupancy_date IS
88 TABLE OF PN_TENANCIES_ALL.ESTIMATED_OCCUPANCY_DATE%TYPE
89 INDEX BY BINARY_INTEGER;
90
91 TYPE t_expiration_date IS
92 TABLE OF PN_TENANCIES_ALL.EXPIRATION_DATE%TYPE
93 INDEX BY BINARY_INTEGER;
94
95 TYPE t_lease_id IS
96 TABLE OF PN_TENANCIES_ALL.LEASE_ID%TYPE
97 INDEX BY BINARY_INTEGER;
98
99 TYPE t_location_id IS
100 TABLE OF PN_TENANCIES_ALL.LOCATION_ID%TYPE
101 INDEX BY BINARY_INTEGER;
102
103 TYPE t_org_id IS
104 TABLE OF PN_TENANCIES_ALL.ORG_ID%TYPE
105 INDEX BY BINARY_INTEGER;
106
107 tenancy_id_tbl t_tenancy_id;
108 cust_id_tbl t_cust_id;
109 cust_site_use_id_tbl t_cust_site_use_id;
110 fin_oblig_end_date_tbl t_fin_oblig_end_date;
111 allocated_area_pct_tbl t_allocated_area_pct;
112 allocated_area_pct t_allocated_area_pct;
113 recovery_type_code_tbl t_recovery_type_code;
114 recovery_space_std_code_tbl t_recovery_space_std_code;
115 estm_occupancy_date_tbl t_estimated_occupancy_date;
116 exp_date_tbl t_expiration_date;
117 lease_id_tbl t_lease_id;
118 location_id_tbl t_location_id;
119 org_id_tbl t_org_id;
120
121 i NUMBER := 0;
122 l_count NUMBER := 0;
123 l_total_count NUMBER := 0;
124 l_count_spc_std NUMBER := 0;
125 l_count_rec_type NUMBER := 0;
126 l_count_cust NUMBER := 0;
127 l_count_fin_end_dt NUMBER := 0;
128 l_action VARCHAR2(2000):= NULL;
129 l_msg VARCHAR2(2000):= NULL;
130 l_context VARCHAR2(200) := NULL;
131 l_cust_id pn_tenancies_all.customer_id%type;
132 l_cust_site_id pn_tenancies_all.customer_site_use_id%type;
133 l_date pn_tenancies_all.fin_oblig_end_date%type;
134 l_customer_id pn_tenancies_all.customer_id%type;
135 l_customer_site_id pn_tenancies_all.customer_site_use_id%type;
136 l_rec_type_code pn_tenancies_all.recovery_type_code%type;
137 l_rec_space_std_code pn_tenancies_all.recovery_space_std_code%type;
138 l_fin_oblig_end_date pn_tenancies_all.fin_oblig_end_date%type;
139
140 BEGIN
141 pnp_debug_pkg.log('pn_recovery_tenancy_upg_pkg.tenancy_upgrade_batch (+)' );
142 pnp_debug_pkg.log('Parameters ' );
143 pnp_debug_pkg.log('-------------------------------------------' );
144 pnp_debug_pkg.log('Lease Num. From : ' || p_lease_num_from);
145 pnp_debug_pkg.log('Lease Num. To : ' || p_lease_num_to);
146 pnp_debug_pkg.log('Space Standard Code : ' || p_rec_space_std_code);
147 pnp_debug_pkg.log('Recovery Type Code : ' || p_rec_type_code);
148 pnp_debug_pkg.log('Update Customer : ' || p_upd_customer);
149 pnp_debug_pkg.log('Update Financial Obligation End Date : ' || p_upd_fin_oblg_end_dt);
150
151 tenancy_id_tbl.delete;
152 cust_id_tbl.delete;
153 cust_site_use_id_tbl.delete;
154 fin_oblig_end_date_tbl.delete;
155 allocated_area_pct_tbl.delete;
156 recovery_type_code_tbl.delete;
157 recovery_space_std_code_tbl.delete;
158 estm_occupancy_date_tbl.delete;
159 exp_date_tbl.delete;
160 lease_id_tbl.delete;
161 location_id_tbl.delete;
162 org_id_tbl.delete;
163
164 l_context := 'Opening c_mass_tenancies cursor';
165
166 OPEN c_mass_tenancies;
167 FETCH c_mass_tenancies BULK COLLECT
168 INTO tenancy_id_tbl,
169 cust_id_tbl,
170 cust_site_use_id_tbl,
171 fin_oblig_end_date_tbl,
172 allocated_area_pct_tbl,
173 recovery_type_code_tbl,
174 recovery_space_std_code_tbl,
175 estm_occupancy_date_tbl,
176 exp_date_tbl,
177 lease_id_tbl,
178 location_id_tbl,
179 org_id_tbl;
180 CLOSE c_mass_tenancies;
181
182 pnp_debug_pkg.log('Processed Bulk Collect. Nof records found: '||tenancy_id_tbl.COUNT);
183
184 FOR i IN 1..tenancy_id_tbl.COUNT
185 LOOP
186 pnp_debug_pkg.log('Processing record# '||i||', Tenancy_id : '||tenancy_id_tbl(i)||
187 ', Location_Id: '||location_id_tbl(i));
188 l_context := 'Looping through pl/sql tables';
189 l_cust_id := NULL;
190 l_cust_site_id := NULL;
191 l_date := NULL;
192 l_customer_id := NULL;
193 l_customer_site_id := NULL;
194 l_rec_type_code := NULL;
195 l_rec_space_std_code := NULL;
196 l_fin_oblig_end_date := NULL;
197
198 IF NVL(p_upd_customer,'N')='Y' AND cust_id_tbl(i) IS NULL THEN
199 l_context := 'Opening c_cust_info cursor';
200 OPEN c_cust_info(lease_id_tbl(i));
201 FETCH c_cust_info into l_cust_id, l_cust_site_id;
202 CLOSE c_cust_info;
203 END IF;
204 pnp_debug_pkg.log('Processed c_cust_info. Cust_id : '||l_cust_id);
205
206 IF NVL(p_upd_fin_oblg_end_dt,'N')='Y' AND fin_oblig_end_date_tbl(i) IS NULL THEN
207 l_date := exp_date_tbl(i);
208 END IF;
209
210 l_context := 'Updating pn_tenancies_all table';
211
212 IF (recovery_type_code_tbl(i) IS NULL AND p_rec_type_code IS NOT NULL) OR
213 (recovery_space_std_code_tbl(i) IS NULL AND
214 p_rec_space_std_code IS NOT NULL) OR
215 (l_cust_id IS NOT NULL) OR
216 (l_date IS NOT NULL) THEN
217
218 UPDATE pn_tenancies_all
219 SET customer_id = NVL(cust_id_tbl(i), l_cust_id),
220 customer_site_use_id = NVL(cust_site_use_id_tbl(i), l_cust_site_id),
221 recovery_type_code = NVL(recovery_type_code_tbl(i), p_rec_type_code),
222 recovery_space_std_code = NVL(recovery_space_std_code_tbl(i),
223 p_rec_space_std_code),
224 fin_oblig_end_date = NVL(fin_oblig_end_date_tbl(i), l_date),
225 last_update_date = SYSDATE,
226 last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
227 WHERE tenancy_id = tenancy_id_tbl(i)
228 RETURNING customer_id,
229 customer_site_use_id,
230 recovery_type_code,
231 recovery_space_std_code,
232 fin_oblig_end_date
233 INTO l_customer_id,
234 l_customer_site_id,
235 l_rec_type_code,
236 l_rec_space_std_code,
237 l_fin_oblig_end_date;
238 pnp_debug_pkg.log('Updated pn_tenancies.');
239
240 IF l_customer_id IS NOT NULL AND
241 NOT(cust_space_assign_exists(tenancy_id_tbl(i))) THEN
242
243 l_context := 'Creating Space Assignment Record';
244 pn_tenancies_pkg.create_auto_space_assign(
245 p_location_id => location_id_tbl(i)
246 ,p_lease_id => lease_id_tbl(i)
247 ,p_customer_id => l_customer_id
248 ,p_cust_site_use_id => l_customer_site_id
249 ,p_cust_assign_start_dt => estm_occupancy_date_tbl(i)
250 ,p_cust_assign_end_dt => exp_date_tbl(i)
251 ,p_recovery_space_std_code => l_rec_space_std_code
252 ,p_recovery_type_code => l_rec_type_code
253 ,p_fin_oblig_end_date => l_fin_oblig_end_date
254 ,p_allocated_pct => allocated_area_pct_tbl(i)
255 ,p_tenancy_id => tenancy_id_tbl(i)
256 ,p_org_id => org_id_tbl(i)
257 ,p_action => l_action
258 ,p_msg => l_msg);
259 END IF;
260
261 l_count := l_count + 1;
262 l_total_count := l_total_count + 1;
263
264 IF l_count = 1000 THEN
265 l_context := 'Commiting transaction';
266 commit;
267 l_count := 0;
268 END IF;
269
270 END IF; --(recovery_type_code_tbl(i)IS NULL ...
271
272 IF (recovery_space_std_code_tbl(i) IS NULL AND
273 l_rec_space_std_code IS NULL) THEN
274 l_count_spc_std := l_count_spc_std + 1;
275 END IF;
276 IF (recovery_type_code_tbl(i) IS NULL AND l_rec_type_code IS NULL) THEN
277 l_count_rec_type := l_count_rec_type + 1;
278 END IF;
279 IF (cust_id_tbl(i) IS NULL AND l_customer_id IS NULL) THEN
280 l_count_cust := l_count_cust + 1;
281 END IF;
282 IF (fin_oblig_end_date_tbl(i) IS NULL AND l_fin_oblig_end_date IS NULL) THEN
283 l_count_fin_end_dt := l_count_fin_end_dt + 1;
284 END IF;
285
286
287 END LOOP;
288
289
290 pnp_debug_pkg.log('+------------------------------------------------------------+');
291 pnp_debug_pkg.put_log_msg(fnd_message.get_string('PN','PN_REC_UPG_LEASE_SELECTED') ||' '||tenancy_id_tbl.COUNT);
292 pnp_debug_pkg.put_log_msg(fnd_message.get_string('PN','PN_REC_UPG_LEASE_PROC') ||' '||l_total_count);
293 pnp_debug_pkg.put_log_msg(fnd_message.get_string('PN','PN_REC_UPG_NO_SPC_STD') ||' '||l_count_spc_std);
294 pnp_debug_pkg.put_log_msg(fnd_message.get_string('PN','PN_REC_UPG_NO_RECOV_TYPE') ||' '||l_count_rec_type);
295 pnp_debug_pkg.put_log_msg(fnd_message.get_string('PN','PN_REC_UPG_NO_CUST') ||' '||l_count_cust);
296 pnp_debug_pkg.put_log_msg(fnd_message.get_string('PN','PN_REC_UPG_NO_FIN_OBLIG_DT')||' '||l_count_fin_end_dt);
297 pnp_debug_pkg.log('pn_recovery_tenancy_upg_pkg.tenancy_upgrade_batch (-)' );
298
299 EXCEPTION
300 when others THEN
301 raise_application_error ('-20001','Error ' || TO_CHAR(sqlcode) || '- while ' || l_context );
302
303 END tenancy_upgrade_batch;
304
305 ---------------------------------------------------------------------------------
306 -- FUNCTION: cust_space_assign_exists
307 --
308 -- HISTORY
309 -- 19-Jun-2003 Pooja Sidhu o Created.
310 --
311 -- Returns true IF a row exists in pn_space_assign_cust_all for a tenancy_id
312 --
313 ---------------------------------------------------------------------------
314 FUNCTION cust_space_assign_exists(p_tenancy_id NUMBER)
315 RETURN BOOLEAN
316 IS
317 CURSOR c_exists IS
318 SELECT 'Y'
319 FROM pn_space_assign_cust_all
320 WHERE tenancy_id = p_tenancy_id
321 AND rownum = 1;
322
323 l_exists VARCHAR2(1) := 'N';
324 BEGIN
325
326 OPEN c_exists;
327 FETCH c_exists INTO l_exists;
328 CLOSE c_exists;
329
330 IF l_exists = 'Y' THEN return TRUE;
331 ELSE return FALSE;
332 END IF;
333 END cust_space_assign_exists;
334
335
336 END pn_recovery_tenancy_upg_pkg;
337