1 PACKAGE BODY PNT_LOCATIONS_PKG AS
2 -- $Header: PNTLOCNB.pls 120.8.12010000.3 2008/11/27 04:42:30 rthumma ship $
3
4 TYPE loc_info_rec IS
5 RECORD (active_start_date pn_locations.active_start_date%TYPE,
6 active_end_date pn_locations.active_end_date%TYPE,
7 area pn_locations.assignable_area%TYPE);
8
9 TYPE loc_info_type IS
10 TABLE OF loc_info_rec
11 INDEX BY BINARY_INTEGER;
12
13 loc_info_tbl loc_info_type;
14
15 ---------------------------------------------------------------------------------------------
16 -- CURSOR : For_Insert_Cur, For_Update_St_Cur, For_Update_End_Cur
17 -- DESCRIPTION: Moved similar cursors from procedures check_location_overlap and
18 -- check_location_gaps.
19 -- 15-NOV-2004 Satish Tripathi o Moved from check_location_overlap and check_location_gaps.
20 ---------------------------------------------------------------------------------------------
21
22 CURSOR For_Insert_Cur (p_loc_cd VARCHAR2, p_loc_type_cd VARCHAR2, p_org_id NUMBER)
23 IS
24 SELECT MIN(active_start_date), MAX(active_end_date)
25 FROM pn_locations_all
26 WHERE location_code = p_loc_cd
27 AND location_type_lookup_code = p_loc_type_cd
28 AND org_id = p_org_id;
29
30 CURSOR For_Update_St_Cur (p_loc_id NUMBER, p_str_dt DATE, p_str_dt_old DATE)
31 IS
32 SELECT TO_NUMBER(MIN(p_str_dt - active_end_date)) start_date_diff
33 FROM pn_locations_all
34 WHERE location_id = p_loc_id
35 AND ROWID <> g_pn_locations_rowid
36 AND active_end_date < p_str_dt_old;
37
38 CURSOR For_Update_End_Cur (p_loc_id NUMBER, p_end_dt DATE, p_end_dt_old DATE)
39 IS
40 SELECT TO_NUMBER(MAX(p_end_dt - active_start_date)) end_date_diff
41 FROM pn_locations_all
42 WHERE location_id = p_loc_id
43 AND ROWID <> g_pn_locations_rowid
44 AND active_start_date > p_end_dt_old;
45
46
47 PROCEDURE Put_Log(p_String VarChar2) IS
48
49 BEGIN
50
51 Fnd_File.Put_Line(Fnd_File.Log, p_String);
52
53 EXCEPTION
54
55 When Others Then Raise;
56
57 END Put_Log;
58
59 --------------------------------------------------------------------
60 -- FUNCTION get_max_rent_area
61 --------------------------------------------------------------------
62 FUNCTION Get_Max_Rent_Area(
63 p_loc_id IN NUMBER
64 ,p_lkp_code IN VARCHAR2
65 ,p_act_str_dt IN DATE
66 ,p_act_end_dt IN DATE
67 )
68 RETURN NUMBER
69 IS
70
71 CURSOR csr_loc_info(p_loc_id IN NUMBER,
72 p_lkp_code IN VARCHAR2,
73 p_act_str_dt IN DATE,
74 p_act_end_dt IN DATE) IS
75 SELECT location_id, active_start_date, active_end_date, NVL(rentable_area,0) rentable_area
76 FROM pn_locations_all
77 WHERE location_type_lookup_code = p_lkp_code
78 AND active_start_date <= NVL(p_act_end_dt, TO_DATE('12/31/4712','MM/DD/YYYY'))
79 AND active_end_date >= p_act_str_dt
80 START WITH location_id = p_loc_id
81 CONNECT BY PRIOR location_id = parent_location_id
82 AND p_act_str_dt between prior active_start_date and
83 NVL(prior active_end_date,TO_DATE('12/31/4712','MM/DD/YYYY'));
84
85 i NUMBER := 0;
86 l_num_table pn_recovery_extract_pkg.number_table_TYPE;
87 l_date_table pn_recovery_extract_pkg.date_table_TYPE;
88 l_max_area NUMBER := 0;
89
90 BEGIN
91 loc_info_tbl.delete;
92 FOR rec_loc_info IN csr_loc_info(p_loc_id, p_lkp_code, p_act_str_dt, p_act_end_dt)
93 LOOP
94
95 loc_info_tbl(i).active_start_date := rec_loc_info.active_start_date;
96 loc_info_tbl(i).active_end_date := rec_loc_info.active_end_date;
97 loc_info_tbl(i).area := rec_loc_info.rentable_area;
98 i := i + 1;
99 END LOOP;
100
101 FOR i IN 0 .. loc_info_tbl.count-1
102 LOOP
103 pn_recovery_extract_pkg.process_vacancy(
104 p_start_date => loc_info_tbl(i).active_start_date,
105 p_end_date => loc_info_tbl(i).active_end_date,
106 p_area => loc_info_tbl(i).area,
107 p_date_table => l_date_table,
108 p_number_table => l_num_table,
109 p_add => TRUE);
110 END LOOP;
111
112 FOR i IN 0 .. l_num_table.count-1
113 LOOP
114 IF l_num_table(i) > l_max_area THEN
115 l_max_area := l_num_table(i);
116 END IF;
117 END LOOP;
118
119 RETURN l_max_area;
120
121 END Get_Max_Rent_Area;
122
123 -------------------------------------------------------
124 -- Validates that the same location does not lie between
125 -- overlapping time periods
126 -------------------------------------------------------
127 PROCEDURE check_location_overlap (
128 p_org_id IN NUMBER,
129 p_location_id IN NUMBER ,
130 p_location_code IN VARCHAR2,
131 p_location_type_lookup_code IN VARCHAR2,
132 p_active_start_date IN DATE,
133 p_active_end_date IN DATE,
134 p_active_start_date_old IN DATE,
135 p_active_end_date_old IN DATE,
136 x_return_status OUT NOCOPY VARCHAR2,
137 x_return_message OUT NOCOPY VARCHAR2)
138 IS
139
140 l_min_start_date DATE;
141 l_max_end_date DATE;
142
143 BEGIN
144
145 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_location_overlap(+)');
146 pnp_debug_pkg.debug(' ChkLocOlap=> In Parameters :: p_location_id: '||p_location_id||', LocCd: '||p_location_code
147 ||', Type: '||p_location_type_lookup_code||' OrgId: '||p_org_id);
148 pnp_debug_pkg.debug(' ChkLocOlap=> p_active_start_date : '||TO_CHAR(p_active_start_date, 'MM/DD/YYYY')
149 ||', p_active_end_date : '||TO_CHAR(p_active_end_date, 'MM/DD/YYYY'));
150 pnp_debug_pkg.debug(' ChkLocOlap=> p_active_start_date_old: '||TO_CHAR(p_active_start_date_old, 'MM/DD/YYYY')
151 ||', p_active_end_date_old: '||TO_CHAR(p_active_end_date_old, 'MM/DD/YYYY'));
152
153 IF p_location_id IS NULL THEN
154
155 -- Check for overlaps during create record
156 OPEN for_insert_cur(p_loc_cd => p_location_code, p_loc_type_cd => p_location_type_lookup_code, p_org_id => p_org_id);
157 FETCH for_insert_cur
158 INTO l_min_start_date,
159 l_max_end_date;
160 CLOSE for_insert_cur;
161
162 pnp_debug_pkg.debug(' ChkLocOlap> MinStrDt: '||TO_CHAR(l_min_start_date, 'MM/DD/YYYY')
163 ||', MaxEndDt: '||TO_CHAR(l_max_end_date, 'MM/DD/YYYY'));
164 -- Validate for start date
165 IF p_active_start_date IS NOT NULL THEN
166 IF p_active_start_date BETWEEN l_min_start_date AND l_max_end_date THEN
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 fnd_message.set_name('PN','PN_INVALID_EFFECTIVE_DATES');
169 return;
170 END IF;
171 END IF;
172
173 -- Validate for start date
174 IF p_active_end_date IS NULL THEN
175 IF p_active_start_date BETWEEN l_min_start_date AND l_max_end_date THEN
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 fnd_message.set_name('PN','PN_INVALID_EFFECTIVE_DATES');
178 return;
179 END IF;
180
181 -- Validate for end date
182 ELSE
183 IF (p_active_end_date BETWEEN l_min_start_date AND l_max_end_date)
184 OR (p_active_end_date = g_end_of_time and p_active_start_date < l_min_start_date) THEN
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 fnd_message.set_name('PN','PN_INVALID_EFFECTIVE_DATES');
187 return;
188 END IF;
189 END IF;
190
191 ELSE
192
193 IF p_active_start_date IS NOT NULL THEN
194 FOR update_rec in for_update_st_cur(p_loc_id => p_location_id, p_str_dt => p_active_start_date, p_str_dt_old => p_active_start_date_old)
195 LOOP
196 pnp_debug_pkg.debug(' ChkLocOlap> Start date diff = '|| update_rec.start_date_diff);
197 IF update_rec.start_date_diff < 1 then
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 fnd_message.set_name('PN','PN_INVALID_EFFECTIVE_DATES');
200 return;
201 END IF;
202 END LOOP;
203 END IF;
204
205 IF p_active_end_date IS NOT NULL THEN
206 FOR update_rec in for_update_end_cur(p_loc_id => p_location_id, p_end_dt => p_active_end_date, p_end_dt_old => p_active_end_date_old)
207 LOOP
208 pnp_debug_pkg.debug(' ChkLocOlap> End date diff = '|| update_rec.end_date_diff);
209 IF update_rec.end_date_diff > -1 then
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 fnd_message.set_name('PN','PN_INVALID_EFFECTIVE_DATES');
212 return;
213 END IF;
214 END LOOP;
215 END IF;
216 END IF;
217
218 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_location_overlap(-) ReturnStatus: '||x_return_status);
219 EXCEPTION
220
221 WHEN OTHERS THEN
222 fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
223 fnd_message.set_token('ERR_MSG',sqlerrm);
224
225 END check_location_overlap;
226
227 ------------------------------------------------------
228 -- Procedure to validate that there cannot be any gaps
229 -- at building and floor level
230 -- 13-JAN-2005 Satish Tripathi o Fixed for BUG# 4104674. Sub/Add 1 from l_min/max_start_date.
231 ----------------------------------------------------
232 PROCEDURE check_location_gaps (
233 p_org_id IN NUMBER
234 ,p_location_id IN NUMBER
235 ,p_location_code IN VARCHAR2
236 ,p_location_type_lookup_code IN VARCHAR2
237 ,p_active_start_date IN DATE
238 ,p_active_end_date IN DATE
239 ,p_active_start_date_old IN DATE
240 ,p_active_end_date_old IN DATE
241 ,x_return_status OUT NOCOPY VARCHAR2
242 ,x_return_message OUT NOCOPY VARCHAR2
243 )
244 IS
245
246 l_min_start_date DATE;
247 l_max_end_date DATE;
248
249 BEGIN
250
251 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_location_gaps(+)');
252 pnp_debug_pkg.debug(' ChkLocGap=> In Parameters :: p_location_id: '||p_location_id
253 ||', LocCd: '||p_location_code||', OrgId: '||p_org_id);
254 pnp_debug_pkg.debug(' ChkLocGap=> p_active_start_date : '||TO_CHAR(p_active_start_date, 'MM/DD/YYYY')
255 ||', p_active_end_date : '||TO_CHAR(p_active_end_date, 'MM/DD/YYYY'));
256 pnp_debug_pkg.debug(' ChkLocGap=> p_active_start_date_old: '||TO_CHAR(p_active_start_date_old, 'MM/DD/YYYY')
257 ||', p_active_end_date_old: '||TO_CHAR(p_active_end_date_old, 'MM/DD/YYYY'));
258
259 IF p_location_id IS NULL THEN
260 -- Check for gaps during create record
261
262 OPEN for_insert_cur(p_loc_cd => p_location_code, p_loc_type_cd => p_location_type_lookup_code, p_org_id => p_org_id);
263 FETCH for_insert_cur
264 INTO l_min_start_date,
265 l_max_end_date;
266 CLOSE for_insert_cur;
267
268 l_min_start_date := l_min_start_date - 1;
269 l_max_end_date := l_max_end_date + 1;
270
271 pnp_debug_pkg.debug(' ChkLocGap> MinStrDt: '||TO_CHAR(l_min_start_date, 'MM/DD/YYYY')
272 ||', MaxEndDt: '||TO_CHAR(l_max_end_date, 'MM/DD/YYYY'));
273 IF p_active_start_date IS NOT NULL THEN
274 IF NOT ((p_active_start_date <= l_min_start_date AND
275 NVL(p_active_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) = l_min_start_date) OR
276 (p_active_start_date >= l_max_end_date and p_active_start_date = l_max_end_date))
277 THEN
278 pnp_debug_pkg.debug(' ChkLocGap> StrDt<MinStrDt OR StrDt>MaxEndDt');
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 fnd_message.set_name('PN','PN_LOC_GAPS_MSG');
281 fnd_message.set_token('GAP_START_DATE',l_max_end_date);
282 fnd_message.set_token('GAP_END_DATE',p_active_start_date - 1);
283 RETURN;
284 END IF;
285 ELSIF p_active_end_date IS NOT NULL THEN
286 IF p_active_end_date < l_min_start_date THEN
287 pnp_debug_pkg.debug(' ChkLocGap> EndDt<MinStrDt');
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 fnd_message.set_name('PN','PN_LOC_GAPS_MSG');
290 fnd_message.set_token('GAP_START_DATE',p_active_end_date + 1);
291 fnd_message.set_token('GAP_END_DATE',l_min_start_date);
292 RETURN;
293 END IF;
294 END IF;
295
296 ELSE
297
298 -- Check for gaps during update record
299
300 IF p_active_start_date IS NOT NULL THEN
301 FOR update_rec in for_update_st_cur(p_loc_id => p_location_id, p_str_dt => p_active_start_date, p_str_dt_old => p_active_start_date_old)
302 LOOP
303 pnp_debug_pkg.debug(' ChkLocGap> Start date diff = '|| update_rec.start_date_diff);
304 IF update_rec.start_date_diff <> 1 THEN
305 pnp_debug_pkg.debug(' ChkLocGap> StrDt not Null, Diff <> 1');
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 fnd_message.set_name('PN','PN_LOC_GAPS_MSG');
308 fnd_message.set_token('GAP_START_DATE',p_active_start_date_old);
309 fnd_message.set_token('GAP_END_DATE',p_active_start_date - 1);
310 RETURN;
311 END IF;
312 END LOOP;
313 END IF;
314
315 IF p_active_end_date IS NOT NULL THEN
316 FOR update_rec in for_update_end_cur(p_loc_id => p_location_id, p_end_dt => p_active_end_date, p_end_dt_old => p_active_end_date_old)
317 LOOP
318 pnp_debug_pkg.debug(' ChkLocGap> End date diff = '|| update_rec.end_date_diff);
319 IF update_rec.end_date_diff <> -1 then
320 pnp_debug_pkg.debug(' ChkLocGap> EndDt not Null, Diff <> 1');
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 fnd_message.set_name('PN','PN_LOC_GAPS_MSG');
323 fnd_message.set_token('GAP_START_DATE',p_active_end_date + 1);
324 fnd_message.set_token('GAP_END_DATE',p_active_end_date_old);
325 RETURN;
326 END IF;
327 END LOOP;
328 END IF;
329 END IF;
330
331 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_location_gaps(-) ReturnStatus: '||x_return_status);
332 EXCEPTION
333
334 WHEN OTHERS THEN
335 fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
336 fnd_message.set_token('ERR_MSG',sqlerrm);
337
338 END check_location_gaps;
339
340 ----------------------------------------------------------------------
341 -- PROCEDURE SET_ROWID
342 ----------------------------------------------------------------------
343 PROCEDURE SET_ROWID
344 ( p_location_id IN NUMBER,
345 p_active_start_date IN DATE,
346 p_active_end_date IN DATE,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_return_message OUT NOCOPY VARCHAR2)
349
350 IS
351
352 l_rowid ROWID;
353
354 BEGIN
355
356 pnp_debug_pkg.debug(' PntLocnPkg.SetRowid (+) LocId: '||p_location_id
357 ||', ActStrDt: '||TO_CHAR(p_active_start_date, 'MM/DD/YYYY')
358 ||', ActEndDt: '||TO_CHAR(p_active_end_date, 'MM/DD/YYYY'));
359
360 -- Location ID, Active Start Date, Active End Date are the old values retrived by the query
361
362 SELECT ROWID
363 INTO l_rowid
364 FROM pn_locations_all
365 WHERE location_id = p_location_id
366 AND active_start_date = p_active_start_date
367 AND active_end_date = NVL(p_active_end_date,g_end_of_time);
368
369 g_pn_locations_rowid := l_rowid;
370
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 pnp_debug_pkg.debug(' PntLocnPkg.SetRowid (-) ReturnStatus: '||x_return_status);
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 x_return_status := FND_API.G_RET_STS_ERROR;
378
379 WHEN TOO_MANY_ROWS THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381
382 WHEN OTHERS THEN
383 x_return_status := FND_API.G_RET_STS_ERROR;
384
385 END SET_ROWID;
386
387 -------------------------------------------------------------------------------
388 -- PROCEDURE : Set_Cascade
389 -- PURPOSE : This procedure sets the occupancy_status_code and
390 -- assignable_emp/cc/cust
391 -- of g_loc_recinfo_tmp depending on the value of p_cascade.
392 -- IN PARAM :
393 -- History :
394 -- 01-DEC-04 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
395 -- 22-DEC-04 Satish Tripathi o Added cursor parent_location_cursor to retain
396 -- not assignable of parent.
397 -- 21-FEB-06 Hareesha o Bug # 4918666 Copy values into g_loc_recinfo_tmp
398 -- only when l_cascade_emp AND l_cascade_cc AND
399 -- l_cascade_cust are not null.
400 -- 30-MAY-06 Hareesha o Bug #5222847 Cascade the value of occupancy status
401 -- to the child locations if made non-occupiable.
402 -------------------------------------------------------------------------------
403 PROCEDURE Set_Cascade (
404 p_cascade IN VARCHAR2
405 )
406 IS
407 l_cascade_occ VARCHAR2(30) := SUBSTR(p_cascade, 1, 1);
408 l_cascade_emp VARCHAR2(30) := SUBSTR(p_cascade, 2, 1);
409 l_cascade_cc VARCHAR2(30) := SUBSTR(p_cascade, 3, 1);
410 l_cascade_cust VARCHAR2(30) := SUBSTR(p_cascade, 4, 1);
411
412 CURSOR parent_location_cursor IS
413 SELECT *
414 FROM pn_locations_all
415 WHERE location_id = g_loc_recinfo_tmp.parent_location_id
416 AND active_start_date <= g_loc_recinfo_tmp.active_end_date
417 AND active_end_date >= g_loc_recinfo_tmp.active_start_date;
418
419 BEGIN
420
421 pnp_debug_pkg.debug(' Set_Cascade (+) '||p_cascade
422 ||', Occ/Asgn:|'||g_loc_recinfo_tmp.Occupancy_status_code
423 ||'|'||g_loc_recinfo_tmp.Assignable_emp
424 ||'|'||g_loc_recinfo_tmp.Assignable_cc
425 ||'|'||g_loc_recinfo_tmp.Assignable_cust
426 ||'|'
427 ||', LocId: '||g_loc_recinfo_tmp.location_id
428 ||', LocCd: '||g_loc_recinfo_tmp.location_code
429 ||', Type: '||g_loc_recinfo_tmp.location_type_lookup_code
430 ||', StrDt: '||TO_CHAR(g_loc_recinfo_tmp.active_start_date, 'MM/DD/YYYY')
431 ||', EndDt: '||TO_CHAR(g_loc_recinfo_tmp.active_end_date, 'MM/DD/YYYY'));
432
433 IF l_cascade_occ IN ('Y', 'N') THEN
434 FOR parent_locn IN parent_location_cursor
435 LOOP
436 IF l_cascade_occ = 'N' THEN
437 g_loc_recinfo_tmp.occupancy_status_code := 'N';
438 END IF;
439 IF l_cascade_emp IN ('Y','N') THEN
440 IF NVL(parent_locn.assignable_emp, 'Y') = 'N' THEN
441 g_loc_recinfo_tmp.assignable_emp := 'N';
442 END IF;
443 END IF;
444 IF l_cascade_cc IN ('Y','N') THEN
445 IF NVL(parent_locn.assignable_cc, 'Y') = 'N' THEN
446 g_loc_recinfo_tmp.assignable_cc := 'N';
447 END IF;
448 END IF;
449 IF l_cascade_cust IN ('Y','N') THEN
450 IF NVL(parent_locn.assignable_cust, 'Y') = 'N' THEN
451 g_loc_recinfo_tmp.assignable_cust := 'N';
452 END IF;
453 END IF;
454 END LOOP;
455
456 ELSE
457 IF l_cascade_emp IN ('Y', 'N') THEN
458 g_loc_recinfo_tmp.assignable_emp := l_cascade_emp;
459 END IF;
460
461 IF l_cascade_cc IN ('Y', 'N') THEN
462 g_loc_recinfo_tmp.assignable_cc := l_cascade_cc;
463 END IF;
464
465 IF l_cascade_cust IN ('Y', 'N') THEN
466 g_loc_recinfo_tmp.assignable_cust := l_cascade_cust;
467 END IF;
468 END IF;
469
470 pnp_debug_pkg.debug(' Set_Cascade (-) '||p_cascade
471 ||', Occ/Asgn:|'||g_loc_recinfo_tmp.Occupancy_status_code
472 ||'|'||g_loc_recinfo_tmp.Assignable_emp
473 ||'|'||g_loc_recinfo_tmp.Assignable_cc
474 ||'|'||g_loc_recinfo_tmp.Assignable_cust
475 ||'|');
476
477 END Set_Cascade;
478
479
480 -----------------------------------------------------------------------
481 -- PROCEDURE : Set_Null_Request_Program_Id
482 -- PURPOSE : This procedure sets request_id, program_id, program_update_date
483 -- and program_application_id to NULL of g_loc_recinfo_tmp.
484 -- IN PARAM :
485 -- History :
486 -- 01-DEC-2004 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
487 -----------------------------------------------------------------------
488 PROCEDURE Set_Null_Request_Program_Id
489 IS
490 BEGIN
491 g_loc_recinfo_tmp.request_id := NULL;
492 g_loc_recinfo_tmp.program_id := NULL;
493 g_loc_recinfo_tmp.program_update_date := NULL;
494 g_loc_recinfo_tmp.program_application_id := NULL;
495 END Set_Null_Request_Program_Id;
496
497
498 -------------------------------------------------------------------------------
499 -- PROCEDURE CORRECT_UPDATE_ROW
500 -- HISTORY:
501 -- 14-JAN-2002 Mrinal Misra o Did conditional assignment of dates to
502 -- Validate_date_assignable_area for
503 -- CORRECT/UPDATE.
504 -- 14-OCT-2003 Anand Tuppad o Added code to consider the new column
505 -- bookable_flag.
506 -- 06-FEB-2004 Kiran Hegde o indented code
507 -- o changed the way l_end_date is populated
508 -- o changed the condition for populating
509 -- G_LOC_RECINFO
510 -- 01-DEC-2004 Satish Tripathi o Modified for Portfolio Status Enh BUG# 4030816.
511 -- Added parameter p_cascade to cascade the changes in
512 -- occupancy_status_code, assignable_emp/cc/cust to
513 -- Child locations by calling Cascade_Child_Locn.
514 -- Populate g_loc_recinfo_tmp to call Insert/Update_Locn_Row.
515 -- 21-JAN-2008 acprakas o Bug#6755579: Commented code which sets g_loc_recinfo_tmp.common_area_flag
516 -- to NULL.
517 -------------------------------------------------------------------------------
518 PROCEDURE Correct_Update_Row(
519 p_pn_locations_rec IN pn_locations_all%ROWTYPE
520 ,p_pn_addresses_rec IN pn_addresses_all%ROWTYPE
521 ,p_change_mode IN VARCHAR2
522 ,p_as_of_date IN DATE
523 ,p_active_start_date_old IN DATE
524 ,p_active_end_date_old IN DATE
525 ,p_assgn_area_chgd_flag IN VARCHAR2
526 ,p_validate IN BOOLEAN
527 ,p_cascade IN VARCHAR2
528 ,x_return_status OUT NOCOPY VARCHAR2
529 ,x_return_message OUT NOCOPY VARCHAR2
530 )
531 IS
532
533 l_rowid rowid;
534 l_location_id NUMBER := NULL;
535 l_str_dt DATE := NULL;
536 l_str_dt_old DATE := NULL;
537 l_end_dt DATE := NULL;
538 l_active_start_date_new DATE := NULL;
539 l_active_end_date_new DATE := NULL;
540 l_as_of_date DATE := NULL;
541
542 CURSOR location_cursor_old is
543 SELECT *
544 FROM pn_locations_all
545 WHERE location_id = p_pn_locations_rec.location_id
546 AND active_start_date = NVL(p_active_start_date_old, g_start_of_time)
547 AND active_end_date = NVL(p_active_end_date_old, g_end_of_time);
548
549 BEGIN
550
551
552 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Correct_Update_Row (+) Mode: '||p_change_mode||', Cascade: '||p_cascade
553 ||', AsofDt: '||p_as_of_date);
554 pnp_debug_pkg.debug(' CorUpdRow=> LocId: '||p_pn_locations_rec.location_id||', LocCd: '||p_pn_locations_rec.location_code
555 ||', Type: '||p_pn_locations_rec.location_type_lookup_code
556 ||', StrDt: '||TO_CHAR(p_pn_locations_rec.active_start_date, 'MM/DD/YYYY')
557 ||', EndDt: '||TO_CHAR(p_pn_locations_rec.active_end_date, 'MM/DD/YYYY'));
558
559 x_return_status := FND_API.G_RET_STS_SUCCESS;
560
561 PNT_LOCATIONS_PKG.SET_ROWID(
562 p_location_id => p_pn_locations_rec.location_id,
563 p_active_start_date => p_active_start_date_old,
564 p_active_end_date => p_active_end_date_old,
565 x_return_status => x_return_status,
566 x_return_message => x_return_message);
567
568 IF ( p_validate) THEN
569 pnp_debug_pkg.put_log_msg('Calling validate_assignable_area');
570
571 IF p_change_mode = 'CORRECT' THEN
572 l_str_dt := p_pn_locations_rec.active_start_date;
573 l_end_dt := p_pn_locations_rec.active_end_date;
574 ELSIF p_change_mode = 'UPDATE' THEN
575 l_str_dt := p_as_of_date;
576 l_end_dt := p_pn_locations_rec.active_end_date;
577 END IF;
578
579 PNP_UTIL_FUNC.Validate_date_assignable_area
580 (p_location_id => p_pn_locations_rec.location_id,
581 p_location_type => p_pn_locations_rec.location_type_lookup_code,
582 p_start_date => l_str_dt,
583 p_end_date => l_end_dt,
584 p_active_start_date_old => p_active_start_date_old,
585 p_active_end_date_old => p_active_end_date_old,
586 p_change_mode => p_change_mode,
587 p_assignable_area => p_pn_locations_rec.assignable_area,
588 x_return_status => x_return_status,
589 x_return_message => x_return_message
590 );
591
592 IF not(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
593 x_return_status := FND_API.G_RET_STS_ERROR;
594 APP_EXCEPTION.Raise_Exception;
595 END IF;
596
597 -- Added redwin Fix for Bug 2722698
598
599 PNT_LOCATIONS_PKG.update_assignments (
600 p_location_id => p_pn_locations_rec.location_id,
601 p_active_start_date => p_pn_locations_Rec.active_start_date,
602 p_active_end_date => p_pn_locations_rec.active_end_date,
603 p_active_start_date_old => p_active_start_date_old,
604 p_active_end_date_old => p_active_end_date_old,
605 x_return_status => x_return_status,
606 x_return_message => x_return_message);
607
608 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 APP_EXCEPTION.Raise_Exception;
611 END IF;
612
613 END IF; -- if p_validate
614
615 IF G_LOC_RECINFO.location_id IS NULL OR
616 G_LOC_RECINFO.location_id <> p_pn_locations_rec.location_id OR
617 NVL(G_LOC_RECINFO.active_start_date, G_START_OF_TIME)
618 <> NVL(p_active_start_date_old, G_START_OF_TIME) OR
619 NVL(G_LOC_RECINFO.active_end_date, G_END_OF_TIME)
620 <> NVL(p_active_end_date_old, G_END_OF_TIME) THEN
621
622 OPEN LOCATION_CURSOR_OLD;
623 FETCH location_cursor_old INTO G_LOC_RECINFO;
624 IF (location_cursor_old%NOTFOUND) THEN
625 CLOSE location_cursor_old;
626 APP_EXCEPTION.Raise_Exception;
627 END IF;
628 CLOSE LOCATION_CURSOR_OLD;
629
630 END IF;
631
632 -- The following is called if dates are brought in, then we need to update the children
633
634 IF G_LOC_RECINFO.active_end_date >
635 nvl(p_pn_locations_rec.active_end_date,G_END_OF_TIME) OR
636 G_LOC_RECINFO.active_start_date
637 < nvl(p_pn_locations_rec.active_start_date,G_START_OF_TIME) THEN
638
639 PNT_LOCATIONS_PKG.Update_child_for_dates (
640 p_location_id => p_pn_locations_rec.location_id,
641 p_active_start_date => p_pn_locations_Rec.active_start_date,
642 p_active_end_date => p_pn_locations_rec.active_end_date,
643 p_active_start_date_old => p_active_start_date_old,
644 p_active_end_date_old => p_active_end_date_old,
645 p_location_type_lookup_code => p_pn_locations_rec.location_type_lookup_code,
646 x_return_status => x_return_status,
647 x_return_message => x_return_message);
648
649 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 APP_EXCEPTION.Raise_Exception;
652 END IF;
653
654 END IF;
655
656 IF p_change_mode = 'CORRECT' THEN
657
658 -- The following is to check if gaps are there in location records
659 IF ( p_validate) then
660
661 ----------------------------------------------
662 -- Call check_location_overlap
663 -- This procedure checks that there is no overlap
664 -- of dates for the same location
665 ------------------------------------------------
666 pnp_debug_pkg.put_log_msg('Calling check_location_overlap');
667 PNT_LOCATIONS_PKG.check_location_overlap (
668 p_org_id => p_pn_locations_rec.org_id,
669 p_location_id => p_pn_locations_rec.location_id,
670 p_location_code => p_pn_locations_rec.location_code,
671 p_location_type_lookup_code => p_pn_locations_rec.location_type_lookup_code,
672 p_active_start_date => p_pn_locations_rec.active_start_date,
673 p_active_end_date => p_pn_locations_rec.active_end_date,
674 p_active_start_date_old => p_active_start_date_old,
675 p_active_end_date_old => p_active_end_date_old,
676 x_return_status => x_return_status,
677 x_return_message => x_return_message);
678
679 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
680 pnp_debug_pkg.debug(' CorUpdRow> Error in check_location_overlap');
681 pnp_debug_pkg.put_log_msg(' CorUpdRow> Error :Calling check_location_overlap');
682 x_return_status := FND_API.G_RET_STS_ERROR;
683 APP_EXCEPTION.Raise_Exception;
684 END IF;
685
686 IF p_pn_locations_rec.location_type_lookup_code NOT IN ('OFFICE', 'SECTION') THEN
687 pnp_debug_pkg.put_log_msg('Calling check_location_gaps');
688 PNT_LOCATIONS_PKG.check_location_gaps (
689 p_org_id => p_pn_locations_rec.org_id,
690 p_location_id => p_pn_locations_rec.location_id,
691 p_location_code => p_pn_locations_rec.location_code,
692 p_location_type_lookup_code => p_pn_locations_rec.location_type_lookup_code,
693 p_active_start_date => p_pn_locations_rec.active_start_date,
694 p_active_end_date => p_pn_locations_rec.active_end_date,
695 p_active_start_date_old => p_active_start_date_old,
696 p_active_end_date_old => p_active_end_date_old,
697 x_return_status => x_return_status,
698 x_return_message => x_return_message);
699
700 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
701 pnp_debug_pkg.put_log_msg('Error Calling check_location_gaps');
702 x_return_status := FND_API.G_RET_STS_ERROR;
703 APP_EXCEPTION.Raise_Exception;
704 END IF;
705
706 END IF;
707
708 END IF; -- endif for p_validate
709
710 PNP_DEBUG_PKG.put_log_msg('call update row ');
711
712 g_loc_recinfo_tmp := p_pn_locations_rec;
713 g_loc_adrinfo_tmp := p_pn_addresses_rec;
714 pnt_locations_pkg.Set_Cascade(p_cascade);
715 pnt_locations_pkg.Update_Locn_Row(
716 p_loc_recinfo => g_loc_recinfo_tmp
717 ,p_adr_recinfo => g_loc_adrinfo_tmp
718 ,p_assgn_area_chgd_flag => p_change_mode
719 ,x_return_status => x_return_status
720 ,x_return_message => x_return_message
721 );
722
723 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 APP_EXCEPTION.Raise_Exception;
726 END IF;
727
728 IF p_pn_locations_rec.location_type_lookup_code NOT IN ('OFFICE', 'SECTION') AND
729 LTRIM(RTRIM(p_cascade)) IS NOT NULL THEN
730 pnt_locations_pkg.cascade_child_locn(
731 p_location_id => p_pn_locations_rec.location_id
732 ,p_start_date => p_pn_locations_rec.active_start_date
733 ,p_end_date => p_pn_locations_rec.active_end_date
734 ,p_cascade => p_cascade
735 ,p_change_mode => p_change_mode
736 ,x_return_status => x_return_status
737 ,x_return_message => x_return_message
738 );
739
740 END IF;
741
742 -- 'CORRECT' MODE ENDS
743
744 ELSIF p_change_mode = 'UPDATE' THEN
745
746 l_active_start_date_new := G_LOC_RECINFO.active_start_date;
747 l_active_end_date_new := G_LOC_RECINFO.active_end_date;
748
749 IF p_pn_locations_rec.active_start_date <> G_LOC_RECINFO.active_start_date THEN
750 -- this means user had changed the start date with update option.
751 l_active_start_date_new := p_pn_locations_rec.active_start_date;
752 END IF;
753 IF p_pn_locations_rec.active_end_date <> G_LOC_RECINFO.active_end_date THEN
754 -- this means user had changed the start date with update option.
755 l_active_end_date_new := p_pn_locations_rec.active_end_date;
756 END IF;
757
758 g_loc_recinfo_tmp := p_pn_locations_rec;
759 g_loc_adrinfo_tmp := p_pn_addresses_rec;
760 g_loc_recinfo_tmp.active_start_date := p_as_of_date;
761 g_loc_recinfo_tmp.active_end_date := l_active_end_date_new;
762 pnt_locations_pkg.Set_Cascade(p_cascade);
763 pnt_locations_pkg.Update_Locn_Row(
764 p_loc_recinfo => g_loc_recinfo_tmp
765 ,p_adr_recinfo => g_loc_adrinfo_tmp
766 ,p_assgn_area_chgd_flag => p_change_mode
767 ,x_return_status => x_return_status
768 ,x_return_message => x_return_message
769 );
770
771 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 APP_EXCEPTION.Raise_Exception;
774 END IF;
775
776 pnp_debug_pkg.put_log_msg('Calling UPDATE ROW.');
777
778 pnp_debug_pkg.put_log_msg('Calling INSERT ROW address = ' || G_LOC_RECINFO.ADDRESS_ID);
779
780 g_loc_recinfo_tmp := g_loc_recinfo;
781 g_loc_adrinfo_tmp := p_pn_addresses_rec;
782 --Bug#6755579 g_loc_recinfo_tmp.common_area_flag := NULL;
783 g_loc_recinfo_tmp.active_start_date := l_active_start_date_new;
784 g_loc_recinfo_tmp.active_end_date := p_as_of_date - 1;
785 pnt_locations_pkg.Set_Null_Request_Program_Id;
786 pnt_locations_pkg.Insert_Locn_Row(
787 p_loc_recinfo => g_loc_recinfo_tmp
788 ,p_adr_recinfo => g_loc_adrinfo_tmp
789 ,p_change_mode => p_change_mode
790 ,x_return_status => x_return_status
791 ,x_return_message => x_return_message
792 );
793
794 IF NOT ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 APP_EXCEPTION.Raise_Exception;
797 END IF;
798
799 IF p_pn_locations_rec.location_type_lookup_code NOT IN ('OFFICE', 'SECTION') AND
800 LTRIM(RTRIM(p_cascade)) IS NOT NULL THEN
801 pnt_locations_pkg.cascade_child_locn(
802 p_location_id => p_pn_locations_rec.location_id
803 ,p_start_date => p_as_of_date
804 ,p_end_date => l_active_end_date_new
805 ,p_cascade => p_cascade
806 ,p_change_mode => p_change_mode
807 ,x_return_status => x_return_status
808 ,x_return_message => x_return_message
809 );
810 END IF;
811
812 END IF;
813 -- End if of correct or update
814
815 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Correct_Update_Row (-) ReturnStatus: '||x_return_status);
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 x_return_status := FND_API.G_RET_STS_ERROR;
820
821 END Correct_Update_Row;
822
823
824 -------------------------------------------------------------------------------
825 -- PROCEDURE : Cascade_Child_Locn
826 -- PURPOSE : This procedure cascades changes of occupancy_status_code
827 -- and assignable_emp/cc/cust to the child location depending
828 -- on the value of p_cascade. Called from Correct_Update_Row.
829 -- IN PARAM :
830 -- History :
831 -- 01-DEC-04 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
832 -- 18-JAN-05 Satish Tripathi o For splitting a loc, 1st call Set_RowId. Check for
833 -- x_return_status = 'S' before calling
834 -- Insert/Update_Locn_Row.
835 -- 11-JUL-06 Hareesha o Bug #5351698 Modified location_cursor
836 -- to remove duplicate records.
837 -------------------------------------------------------------------------------
838 PROCEDURE Cascade_Child_Locn (
839 p_location_id IN NUMBER
840 ,p_start_date IN DATE
841 ,p_end_date IN DATE
842 ,p_cascade IN VARCHAR2
843 ,p_change_mode IN VARCHAR2
844 ,x_return_status OUT NOCOPY VARCHAR2
845 ,x_return_message OUT NOCOPY VARCHAR2
846 )
847 IS
848 l_loc_type VARCHAR2(30) := 'BUILDING';
849 l_split VARCHAR2(30) := 'N';
850 l_cascade_occ VARCHAR2(30) := SUBSTR(p_cascade, 1, 1);
851 l_cascade_emp VARCHAR2(30) := SUBSTR(p_cascade, 2, 1);
852 l_cascade_cc VARCHAR2(30) := SUBSTR(p_cascade, 3, 1);
853 l_cascade_cust VARCHAR2(30) := SUBSTR(p_cascade, 4, 1);
854
855 CURSOR location_cursor IS
856 SELECT *
857 FROM pn_locations_all
858 START WITH ( parent_location_id = p_location_id
859 AND active_start_date <= NVL(p_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'))
860 AND active_end_date >= p_start_date
861 )
862 CONNECT BY ( PRIOR location_id = parent_location_id
863 AND active_end_date >= prior active_start_date
864 AND active_start_date <= prior active_end_date
865 )
866 ORDER BY location_type_lookup_code, active_start_date;
867
868 BEGIN
869
870 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Cascade_Child_Locn (+)');
871 pnp_debug_pkg.debug(' CacCldLoc=> In Parameters :: p_location_id: '||p_location_id||', Cascade: '||p_cascade
872 ||', Mode: '||p_change_mode);
873 pnp_debug_pkg.debug(' CacCldLoc=> StrDt: '||TO_CHAR(p_start_date, 'MM/DD/YYYY')
874 ||', EndDt: '||TO_CHAR(p_end_date, 'MM/DD/YYYY'));
875
876 FOR locn_rec IN location_cursor
877 LOOP
878 g_loc_recinfo_tmp := NULL;
879 g_pn_locations_rowid := NULL;
880 l_split := 'N';
881 IF l_cascade_occ IN ('Y', 'N') THEN
882 IF locn_rec.occupancy_status_code <> l_cascade_occ OR
883 locn_rec.assignable_emp <> l_cascade_occ OR
884 locn_rec.assignable_cc <> l_cascade_occ OR
885 locn_rec.assignable_cust <> l_cascade_occ
886 THEN
887 l_split := 'Y';
888 END IF;
889 ELSE
890 IF l_cascade_emp IN ('Y', 'N') AND
891 locn_rec.assignable_emp <> l_cascade_emp
892 THEN
893 l_split := 'Y';
894 END IF;
895
896 IF l_cascade_cc IN ('Y', 'N') AND
897 locn_rec.assignable_cc <> l_cascade_cc
898 THEN
899 l_split := 'Y';
900 END IF;
901
902 IF l_cascade_cust IN ('Y', 'N') AND
903 locn_rec.assignable_cust <> l_cascade_cust
904 THEN
905 l_split := 'Y';
906 END IF;
907 END IF;
908
909 pnp_debug_pkg.debug(' CacCldLoc> Row#: '||location_cursor%ROWCOUNT||', LocId: '||locn_rec.location_id
910 ||', LocCd: '||locn_rec.location_code
911 ||', Type: '||locn_rec.location_type_lookup_code
912 ||', Split: '||l_split);
913 pnp_debug_pkg.debug(' CacCldLoc> ActStrDate : '||TO_CHAR(locn_rec.active_start_date, 'MM/DD/YYYY')
914 ||', ActEndDate : '||TO_CHAR(locn_rec.active_end_date, 'MM/DD/YYYY'));
915
916 IF l_split = 'Y' THEN
917 pnt_locations_pkg.Set_RowId(locn_rec.location_id, locn_rec.active_start_date, locn_rec.active_end_date,
918 x_return_status, x_return_message);
919
920 IF locn_rec.active_start_date < p_start_date AND
921 locn_rec.active_end_date > p_end_date THEN
922
923 pnp_debug_pkg.debug(' CacCldLoc> ... Case# 1: ActStrDt < pStrDt AND ActEndDt > pEndDt');
924 IF x_return_status = 'S' THEN
925 g_loc_recinfo_tmp := locn_rec;
926 g_loc_recinfo_tmp.active_end_date := p_start_date-1;
927 pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
928 END IF;
929
930 IF x_return_status = 'S' THEN
931 g_loc_recinfo_tmp := locn_rec;
932 g_loc_recinfo_tmp.active_start_date := p_start_date;
933 g_loc_recinfo_tmp.active_end_date := p_end_date;
934 pnt_locations_pkg.Set_Null_Request_Program_Id;
935 pnt_locations_pkg.Set_Cascade(p_cascade);
936 pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
937 END IF;
938
939 IF x_return_status = 'S' THEN
940 g_loc_recinfo_tmp := locn_rec;
941 g_loc_recinfo_tmp.active_start_date := p_end_date+1;
942 pnt_locations_pkg.Set_Null_Request_Program_Id;
943 pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
944 END IF;
945
946 ELSIF locn_rec.active_start_date < p_start_date THEN
947
948 pnp_debug_pkg.debug(' CacCldLoc> ... Case# 2: ActStrDt < pStrDt');
949 IF x_return_status = 'S' THEN
950 g_loc_recinfo_tmp := locn_rec;
951 g_loc_recinfo_tmp.active_end_date := p_start_date-1;
952 pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
953 END IF;
954
955 IF x_return_status = 'S' THEN
956 g_loc_recinfo_tmp := locn_rec;
957 g_loc_recinfo_tmp.active_start_date := p_start_date;
958 pnt_locations_pkg.Set_Null_Request_Program_Id;
959 pnt_locations_pkg.Set_Cascade(p_cascade);
960 pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
961 END IF;
962
963 ELSIF locn_rec.active_end_date > p_end_date THEN
964
965 pnp_debug_pkg.debug(' CacCldLoc> ... Case# 3: ActEndDt > pEndDt');
966 IF x_return_status = 'S' THEN
967 g_loc_recinfo_tmp := locn_rec;
968 g_loc_recinfo_tmp.active_end_date := p_end_date;
969 pnt_locations_pkg.Set_Cascade(p_cascade);
970 pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
971 END IF;
972
973 IF x_return_status = 'S' THEN
974 g_loc_recinfo_tmp := locn_rec;
975 g_loc_recinfo_tmp.active_start_date := p_end_date+1;
976 pnt_locations_pkg.Set_Null_Request_Program_Id;
977 pnt_locations_pkg.Insert_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
978 END IF;
979
980 ELSE
981
982 pnp_debug_pkg.debug(' CacCldLoc> ... Case# 4: ActStrDt >= pStrDt AND ActEndDt <= pEndDt');
983 IF x_return_status = 'S' THEN
984 g_loc_recinfo_tmp := locn_rec;
985 pnt_locations_pkg.Set_Cascade(p_cascade);
986 pnt_locations_pkg.Update_Locn_Row(g_loc_recinfo_tmp, NULL, p_change_mode, x_return_status, x_return_message);
987 END IF;
988
989 END IF;
990 END IF;
991
992 END LOOP;
993
994 IF x_return_status IS NULL THEN
995 x_return_status := fnd_api.g_ret_sts_success;
996 END IF;
997
998 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Cascade_Child_Locn (-) ReturnStatus: '||x_return_status);
999
1000 END Cascade_Child_Locn;
1001
1002
1003 -----------------------------------------------------------------------
1004 -- PROCEDURE : Check_Locn_Assgn
1005 -- PURPOSE : This Function returns TRUE when there exists either of any
1006 -- of employee, cost center, customer assignments of location
1007 -- depending on the parameter Assignable Mode (p_asgn_mode).
1008 -- IN PARAM :
1009 -- History :
1010 -- 01-DEC-2004 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
1011 -----------------------------------------------------------------------
1012 FUNCTION Check_Locn_Assgn (
1013 p_location_id IN NUMBER
1014 ,p_location_type IN VARCHAR2
1015 ,p_str_date IN DATE
1016 ,p_end_date IN DATE
1017 ,p_asgn_mode IN VARCHAR2
1018 )
1019 RETURN BOOLEAN
1020 IS
1021 l_exists BOOLEAN;
1022 l_asgn_exists VARCHAR2(30) := 'FALSE';
1023 l_str_date DATE;
1024 l_end_date DATE;
1025
1026 --Modified the cursor for Bug 6827603
1027 CURSOR check_assign_csr IS
1028 SELECT 'TRUE'
1029 FROM DUAL
1030 WHERE EXISTS (SELECT NULL
1031 FROM pn_space_assign_cust_all, (SELECT location_id loc_id
1032 FROM pn_locations_all
1033 WHERE active_start_date <= l_end_date
1034 AND active_end_date >= l_str_date
1035 START WITH location_id = p_location_id
1036 CONNECT BY PRIOR location_id = parent_location_id
1037 UNION
1038 SELECT -1 loc_id
1039 FROM DUAL) loc
1040 WHERE p_asgn_mode IN ('ALL', 'CUST')
1041 AND ((p_location_type IN ('OFFICE', 'SECTION') AND
1042 location_id = p_location_id AND
1043 loc.loc_id=-1) OR
1044 (p_location_type NOT IN ('OFFICE', 'SECTION') AND
1045 location_id =loc.loc_id))
1046 AND cust_assign_start_date <= l_end_date
1047 AND NVL(cust_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) >= l_str_date)
1048 OR EXISTS (SELECT NULL
1049 FROM pn_space_assign_emp_all, (SELECT location_id loc_id
1050 FROM pn_locations_all
1051 WHERE active_start_date <= l_end_date
1052 AND active_end_date >= l_str_date
1053 START WITH location_id = p_location_id
1054 CONNECT BY PRIOR location_id = parent_location_id
1055 UNION
1056 SELECT -1 loc_id
1057 FROM DUAL) loc
1058 WHERE p_asgn_mode IN ('ALL', 'EMP', 'CC')
1059 AND ((p_location_type IN ('OFFICE', 'SECTION') AND
1060 location_id = p_location_id AND
1061 loc.loc_id=-1) OR
1062 (p_location_type NOT IN ('OFFICE', 'SECTION') AND
1063 location_id =loc.loc_id))
1064 AND ((p_asgn_mode = 'ALL') OR
1065 (p_asgn_mode = 'EMP' AND person_id IS NOT NULL) OR
1066 (p_asgn_mode = 'CC' AND cost_center_code IS NOT NULL))
1067 AND emp_assign_start_date <= l_end_date
1068 AND NVL(emp_assign_end_date, TO_DATE('12/31/4712','MM/DD/YYYY')) >= l_str_date);
1069
1070 BEGIN
1071
1072 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Check_Locn_Assgn (+)');
1073 l_str_date := p_str_date;
1074 l_end_date := NVL(p_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'));
1075
1076 OPEN check_assign_csr;
1077 FETCH check_assign_csr INTO l_asgn_exists;
1078 CLOSE check_assign_csr;
1079
1080 IF l_asgn_exists = 'TRUE' THEN
1081 l_exists := TRUE;
1082 ELSE
1083 l_exists := FALSE;
1084 END IF;
1085
1086 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Check_Locn_Assgn (-) l_exists: '||l_asgn_exists);
1087
1088 RETURN l_exists;
1089
1090 END Check_Locn_Assgn;
1091
1092 -----------------------------------------------------------------------
1093 -- PROCEDURE : Parent_Not_Occpble_Asgnble
1094 -- PURPOSE : This Function returns TRUE when Parent location is
1095 -- not Occupiable or Emp/CC/Cust assignable depending on the
1096 -- parameter Status Mode (p_status_mode).
1097 -- IN PARAM :
1098 -- History :
1099 -- 22-DEC-2004 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
1100 -----------------------------------------------------------------------
1101 FUNCTION Parent_Not_Occpble_Asgnble (
1102 p_parent_location_id IN NUMBER
1103 ,p_str_date IN DATE
1104 ,p_end_date IN DATE
1105 ,p_status_mode IN VARCHAR2
1106 )
1107 RETURN BOOLEAN
1108 IS
1109 l_exists BOOLEAN;
1110 l_loc_status VARCHAR2(30) := 'FALSE';
1111 l_str_date DATE;
1112 l_end_date DATE;
1113
1114 CURSOR check_loc_status_csr IS
1115 SELECT 'TRUE'
1116 FROM DUAL
1117 WHERE EXISTS (SELECT NULL
1118 FROM pn_locations_all
1119 WHERE location_id = p_parent_location_id
1120 AND active_start_date <= l_end_date
1121 AND active_end_date >= l_str_date
1122 AND ((p_status_mode IN ('OCC') AND NVL(occupancy_status_code, 'Y') = 'N')
1123 OR (p_status_mode IN ('EMP') AND NVL(assignable_emp, 'Y') = 'N')
1124 OR (p_status_mode IN ('CC') AND NVL(assignable_cc, 'Y') = 'N')
1125 OR (p_status_mode IN ('CUST') AND NVL(assignable_cust, 'Y') = 'N')));
1126
1127 BEGIN
1128
1129 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Parent_Not_Occpble_Asgnble (+) LocId:'||p_parent_location_id
1130 ||', Mode: '||p_status_mode
1131 ||', StrDt: '||TO_CHAR(p_str_date, 'MM/DD/YYYY')
1132 ||', EndDt: '||TO_CHAR(p_end_date, 'MM/DD/YYYY'));
1133 l_str_date := p_str_date;
1134 l_end_date := NVL(p_end_date, TO_DATE('12/31/4712','MM/DD/YYYY'));
1135
1136 IF p_status_mode IN ('OCC', 'EMP', 'CC', 'CUST') THEN
1137 OPEN check_loc_status_csr;
1138 FETCH check_loc_status_csr INTO l_loc_status;
1139 CLOSE check_loc_status_csr;
1140
1141 IF l_loc_status = 'TRUE' THEN
1142 l_exists := TRUE;
1143 ELSE
1144 l_exists := FALSE;
1145 END IF;
1146 END IF;
1147
1148 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Parent_Not_Occpble_Asgnble (-) l_exists: '||l_loc_status);
1149
1150 RETURN l_exists;
1151
1152 END Parent_Not_Occpble_Asgnble;
1153
1154
1155 -------------------------------------------------------------------------------
1156 -- PROCEDURE insert_row
1157 -- DESCRIPTION : inserts a row in pn_addresses_all
1158 -- SCOPE : PUBLIC
1159 -- INVOKED FROM :
1160 -- RETURNS : NONE
1161 -- HISTORY :
1162 -- 14-Feb-04 Kiran o validate_gross_area will now be called for both
1163 -- Offices and Sections
1164 -- 01-DEC-04 STripathi o Modified for Portfolio Status Enh BUG# 4030816.
1165 -- Added parameters occupancy_status_code,
1166 -- assignable_emp, assignable_cc, assignable_cust,
1167 -- disposition, acc_treatment.
1168 -- 28-APR-05 piagrawa o Modified the select statements to retrieve values
1169 -- from pn_locations_all instead of pn_locations
1170 -- Also passed org_id as parameter to
1171 -- PNT_ADDR_PKG.insert_row
1172 -- 19-JUL-05 SatyaDeep o Added argument x_source to insert the source
1173 -- product from pn_locations_itf for bug#4468893
1174 -- 28-NOV-05 pikhar o fetched org_id using cursor
1175 -- 21-AUG-08 rthumma o Bug 7273859 : Modified call to pnt_addr_pkg.insert_row
1176 -- to pass NULL for ATTRIBUTE_CATEGORY,attribute1..15
1177 -------------------------------------------------------------------------------
1178 PROCEDURE insert_row (
1179 x_rowid IN OUT NOCOPY rowid
1180 ,x_org_id IN NUMBER
1181 ,x_LOCATION_ID IN OUT NOCOPY NUMBER
1182 ,x_LAST_UPDATE_DATE DATE
1183 ,x_LAST_UPDATED_BY NUMBER
1184 ,x_CREATION_DATE DATE
1185 ,x_CREATED_BY NUMBER
1186 ,x_LAST_UPDATE_LOGIN NUMBER
1187 ,x_LOCATION_PARK_ID NUMBER
1188 ,x_LOCATION_TYPE_LOOKUP_CODE VARCHAR2
1189 ,x_SPACE_TYPE_LOOKUP_CODE VARCHAR2
1190 ,x_FUNCTION_TYPE_LOOKUP_CODE VARCHAR2
1191 ,x_STANDARD_TYPE_LOOKUP_CODE VARCHAR2
1192 ,x_LOCATION_ALIAS VARCHAR2
1193 ,x_LOCATION_CODE VARCHAR2
1194 ,x_BUILDING VARCHAR2
1195 ,x_LEASE_OR_OWNED VARCHAR2
1196 ,x_CLASS VARCHAR2
1197 ,x_STATUS_TYPE VARCHAR2
1198 ,x_FLOOR VARCHAR2
1199 ,x_OFFICE VARCHAR2
1200 ,x_ADDRESS_ID IN OUT NOCOPY NUMBER
1201 ,x_MAX_CAPACITY NUMBER
1202 ,x_OPTIMUM_CAPACITY NUMBER
1203 ,x_GROSS_AREA NUMBER
1204 ,x_RENTABLE_AREA NUMBER
1205 ,x_USABLE_AREA NUMBER
1206 ,x_ASSIGNABLE_AREA NUMBER
1207 ,x_COMMON_AREA NUMBER
1208 ,x_SUITE VARCHAR2
1209 ,x_ALLOCATE_COST_CENTER_CODE VARCHAR2
1210 ,x_UOM_CODE VARCHAR2
1211 ,x_DESCRIPTION VARCHAR2
1212 ,x_PARENT_LOCATION_ID NUMBER
1213 ,x_INTERFACE_FLAG VARCHAR2
1214 ,x_REQUEST_ID NUMBER
1215 ,x_PROGRAM_APPLICATION_ID NUMBER
1216 ,x_PROGRAM_ID NUMBER
1217 ,x_PROGRAM_UPDATE_DATE DATE
1218 ,x_STATUS VARCHAR2
1219 ,x_PROPERTY_ID NUMBER
1220 ,x_ATTRIBUTE_CATEGORY VARCHAR2
1221 ,x_ATTRIBUTE1 VARCHAR2
1222 ,x_ATTRIBUTE2 VARCHAR2
1223 ,x_ATTRIBUTE3 VARCHAR2
1224 ,x_ATTRIBUTE4 VARCHAR2
1225 ,x_ATTRIBUTE5 VARCHAR2
1226 ,x_ATTRIBUTE6 VARCHAR2
1227 ,x_ATTRIBUTE7 VARCHAR2
1228 ,x_ATTRIBUTE8 VARCHAR2
1229 ,x_ATTRIBUTE9 VARCHAR2
1230 ,x_ATTRIBUTE10 VARCHAR2
1231 ,x_ATTRIBUTE11 VARCHAR2
1232 ,x_ATTRIBUTE12 VARCHAR2
1233 ,x_ATTRIBUTE13 VARCHAR2
1234 ,x_ATTRIBUTE14 VARCHAR2
1235 ,x_ATTRIBUTE15 VARCHAR2
1236 ,x_address_line1 VARCHAR2
1237 ,x_address_line2 VARCHAR2
1238 ,x_address_line3 VARCHAR2
1239 ,x_address_line4 VARCHAR2
1240 ,x_county VARCHAR2
1241 ,x_city VARCHAR2
1242 ,x_state VARCHAR2
1243 ,x_province VARCHAR2
1244 ,x_zip_code VARCHAR2
1245 ,x_country VARCHAR2
1246 ,x_territory_id NUMBER
1247 ,x_addr_last_update_date DATE
1248 ,x_addr_last_updated_by NUMBER
1249 ,x_addr_creation_date DATE
1250 ,x_addr_created_by NUMBER
1251 ,x_addr_last_update_login NUMBER
1252 ,x_addr_attribute_category VARCHAR2
1253 ,x_addr_attribute1 VARCHAR2
1254 ,x_addr_attribute2 VARCHAR2
1255 ,x_addr_attribute3 VARCHAR2
1256 ,x_addr_attribute4 VARCHAR2
1257 ,x_addr_attribute5 VARCHAR2
1258 ,x_addr_attribute6 VARCHAR2
1259 ,x_addr_attribute7 VARCHAR2
1260 ,x_addr_attribute8 VARCHAR2
1261 ,x_addr_attribute9 VARCHAR2
1262 ,x_addr_attribute10 VARCHAR2
1263 ,x_addr_attribute11 VARCHAR2
1264 ,x_addr_attribute12 VARCHAR2
1265 ,x_addr_attribute13 VARCHAR2
1266 ,x_addr_attribute14 VARCHAR2
1267 ,x_addr_attribute15 VARCHAR2
1268 ,x_common_area_flag VARCHAR2
1269 ,x_active_start_date DATE
1270 ,x_active_end_date DATE
1271 ,x_return_status OUT NOCOPY VARCHAR2
1272 ,x_return_message OUT NOCOPY VARCHAR2
1273 ,x_bookable_flag VARCHAR2
1274 ,x_change_mode VARCHAR2
1275 ,x_occupancy_status_code VARCHAR2
1276 ,x_assignable_emp VARCHAR2
1277 ,x_assignable_cc VARCHAR2
1278 ,x_assignable_cust VARCHAR2
1279 ,x_disposition_code VARCHAR2
1280 ,x_acc_treatment_code VARCHAR2
1281 ,x_source VARCHAR2
1282 )
1283 IS
1284
1285 CURSOR c IS
1286 SELECT ROWID
1287 FROM pn_locations_all
1288 WHERE location_id = x_location_id
1289 AND active_start_date = NVL(x_active_start_date, g_start_of_time)
1290 AND active_end_date = NVL(x_active_end_date, g_end_of_time);
1291
1292 l_return_status VARCHAR2(2000) := NULL;
1293 l_return_message VARCHAR2(32767) := NULL;
1294 l_sqlerrm VARCHAR2(2000);
1295
1296 BEGIN
1297 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.insert_row (+) LocId: '||x_location_id||', LocCd: '||x_location_code
1298 ||', Type: '||x_location_type_lookup_code
1299 ||', ActStrDt: '||TO_CHAR(x_active_start_date, 'MM/DD/YYYY')
1300 ||', ActEndDt: '||TO_CHAR(x_active_end_date, 'MM/DD/YYYY'));
1301 x_return_status := fnd_api.g_ret_sts_success;
1302
1303 -----------------------------------------------------------------
1304 -- Call CHECK_UNIQUE_LOCATION_ALIAS to check if the location alias
1305 -- is unique.
1306 -----------------------------------------------------------------
1307 l_return_status := NULL;
1308 PNT_LOCATIONS_PKG.check_unique_location_alias ( l_return_status,
1309 x_location_id,
1310 x_parent_location_id,
1311 x_location_type_lookup_code,
1312 x_location_alias,
1313 x_active_start_date,
1314 x_active_end_date,
1315 x_org_id);
1316 IF (l_return_status IS NOT NULL) THEN
1317 pnp_debug_pkg.put_log_msg('Error in unique_location_alias');
1318 APP_EXCEPTION.Raise_Exception;
1319 END IF;
1320
1321 -----------------------------------------------------------------
1322 -- Call CHECK_UNIQUE_LOCATION_CODE to check if the location code
1323 -- is unique.
1324 -----------------------------------------------------------------
1325 l_return_status := NULL;
1326 check_unique_location_code (
1327 l_return_status
1328 ,x_location_id
1329 ,x_location_code
1330 ,x_active_start_date
1331 ,x_active_end_date
1332 ,x_org_id);
1333 IF (l_return_status IS NOT NULL) THEN
1334 pnp_debug_pkg.put_log_msg('Error in unique_location_code');
1335 APP_EXCEPTION.Raise_Exception;
1336 END IF;
1337
1338 IF x_location_type_lookup_code IN ('OFFICE', 'SECTION') AND
1339 x_rentable_area IS NOT NULL THEN
1340
1341 IF NOT pnt_locations_pkg.validate_gross_area(x_parent_location_id,
1342 x_rentable_area,
1343 x_location_type_lookup_code,
1344 x_active_start_date,
1345 x_active_end_date) THEN
1346
1347 fnd_message.set_name('PN', 'PN_GROSS_RENTABLE');
1348 app_exception.raise_Exception;
1349 END IF;
1350 END IF;
1351
1352 -- Check for location overlap in insert row
1353
1354 l_return_status := NULL;
1355
1356 IF NVL(x_change_mode,'X') not in ('UPDATE') THEN
1357 PNT_LOCATIONS_PKG.check_location_overlap (
1358 p_org_id => x_org_id,
1359 p_location_id => NULL,
1360 p_location_code => x_location_code,
1361 p_location_type_lookup_code => x_location_type_lookup_code,
1362 p_active_start_date => x_active_start_date,
1363 p_active_end_date => NVL(x_active_end_date,G_END_OF_TIME),
1364 p_active_start_date_old => null,
1365 p_active_end_date_old => null,
1366 x_return_status => l_return_status,
1367 x_return_message => l_return_message);
1368
1369 IF NOT ( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1370 pnp_debug_pkg.debug(' InsRow> Error in check_location_overlap ');
1371 APP_EXCEPTION.Raise_Exception;
1372 END IF;
1373
1374 END IF; -- x_change_mode
1375
1376 l_return_status := NULL;
1377
1378 IF NOT (NVL(x_LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('OFFICE', 'SECTION'))
1379 and NVL(x_change_mode,'X') not in ('UPDATE') THEN
1380
1381 PNT_LOCATIONS_PKG.check_location_gaps (
1382 p_org_id => x_org_id,
1383 p_location_id => null,
1384 p_location_code => x_location_code,
1385 p_location_type_lookup_code => x_location_type_lookup_code,
1386 p_active_start_date => x_active_start_date,
1387 p_active_end_date => x_active_end_date,
1388 p_active_start_date_old => null,
1389 p_active_end_date_old => null,
1390 x_return_status => l_return_status,
1391 x_return_message => l_return_message);
1392
1393 IF NOT ( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1394 APP_EXCEPTION.Raise_Exception;
1395 END IF;
1396
1397 END IF; -- x_LOCATION_TYPE_LOOKUP_CODE
1398
1399 -----------------------------------------------------------------
1400 -- Call the PN_ADDRESSES insert table handler to create an address
1401 -- row and also return the address_id (OUT parameter) for
1402 -- PN_LOCATIONS table. This will only be called when we insert the
1403 -- building record.
1404 -----------------------------------------------------------------
1405
1406 pnp_debug_pkg.put_log_msg('Calling address insert address =' || x_address_id);
1407 IF (NVL(x_LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('BUILDING','LAND'))
1408 AND x_address_id is null THEN
1409 PNT_ADDR_PKG.insert_row (
1410 x_address_id,
1411 x_address_line1,
1412 x_address_line2,
1413 x_address_line3,
1414 x_address_line4,
1415 x_county,
1416 x_city,
1417 x_state,
1418 x_province,
1419 x_zip_code,
1420 x_country,
1421 x_territory_id,
1422 x_last_update_date,
1423 x_last_updated_by,
1424 x_creation_date,
1425 x_created_by,
1426 x_last_update_login,
1427 null,
1428 null,
1429 null,
1430 null,
1431 null,
1432 null,
1433 null,
1434 null,
1435 null,
1436 null,
1437 null,
1438 null,
1439 null,
1440 null,
1441 null,
1442 null,
1443 x_addr_attribute_category,
1444 x_addr_attribute1,
1445 x_addr_attribute2,
1446 x_addr_attribute3,
1447 x_addr_attribute4,
1448 x_addr_attribute5,
1449 x_addr_attribute6,
1450 x_addr_attribute7,
1451 x_addr_attribute8,
1452 x_addr_attribute9,
1453 x_addr_attribute10,
1454 x_addr_attribute11,
1455 x_addr_attribute12,
1456 x_addr_attribute13,
1457 x_addr_attribute14,
1458 x_addr_attribute15,
1459 x_org_id
1460 );
1461 END IF;
1462
1463 -----------------------------------------------------------------
1464 -- Allocate the sequence to the primary key loction_id
1465 -- Do not get a new location_id in case of UPDATE
1466 -----------------------------------------------------------------
1467 IF x_change_mode IS NULL and x_location_id is null THEN
1468 SELECT pn_locations_s.NEXTVAL
1469 INTO x_location_id
1470 FROM DUAL;
1471 END IF;
1472
1473 pnp_debug_pkg.debug(' InsRow> before insert' || x_change_mode);
1474 BEGIN
1475 INSERT INTO pn_locations_all (
1476 LOCATION_ID
1477 ,LAST_UPDATE_DATE
1478 ,LAST_UPDATED_BY
1479 ,CREATION_DATE
1480 ,CREATED_BY
1481 ,LAST_UPDATE_LOGIN
1482 ,LOCATION_PARK_ID
1483 ,LOCATION_TYPE_LOOKUP_CODE
1484 ,SPACE_TYPE_LOOKUP_CODE
1485 ,FUNCTION_TYPE_LOOKUP_CODE
1486 ,STANDARD_TYPE_LOOKUP_CODE
1487 ,LOCATION_ALIAS
1488 ,LOCATION_CODE
1489 ,BUILDING
1490 ,LEASE_OR_OWNED
1491 ,CLASS
1492 ,STATUS_TYPE
1493 ,FLOOR
1494 ,OFFICE
1495 ,ADDRESS_ID
1496 ,MAX_CAPACITY
1497 ,OPTIMUM_CAPACITY
1498 ,GROSS_AREA
1499 ,RENTABLE_AREA
1500 ,USABLE_AREA
1501 ,ASSIGNABLE_AREA
1502 ,COMMON_AREA
1503 ,SUITE
1504 ,ALLOCATE_COST_CENTER_CODE
1505 ,UOM_CODE
1506 ,DESCRIPTION
1507 ,PARENT_LOCATION_ID
1508 ,INTERFACE_FLAG
1509 ,REQUEST_ID
1510 ,PROGRAM_APPLICATION_ID
1511 ,PROGRAM_ID
1512 ,PROGRAM_UPDATE_DATE
1513 ,STATUS
1514 ,PROPERTY_ID
1515 ,ATTRIBUTE_CATEGORY
1516 ,ATTRIBUTE1
1517 ,ATTRIBUTE2
1518 ,ATTRIBUTE3
1519 ,ATTRIBUTE4
1520 ,ATTRIBUTE5
1521 ,ATTRIBUTE6
1522 ,ATTRIBUTE7
1523 ,ATTRIBUTE8
1524 ,ATTRIBUTE9
1525 ,ATTRIBUTE10
1526 ,ATTRIBUTE11
1527 ,ATTRIBUTE12
1528 ,ATTRIBUTE13
1529 ,ATTRIBUTE14
1530 ,ATTRIBUTE15
1531 ,COMMON_AREA_FLAG
1532 ,ORG_ID
1533 ,ACTIVE_START_DATE
1534 ,ACTIVE_END_DATE
1535 ,BOOKABLE_FLAG
1536 ,occupancy_status_code
1537 ,assignable_emp
1538 ,assignable_cc
1539 ,assignable_cust
1540 ,disposition_code
1541 ,acc_treatment_code
1542 ,source
1543 )
1544 VALUES
1545 (
1546 x_LOCATION_ID
1547 ,x_LAST_UPDATE_DATE
1548 ,x_LAST_UPDATED_BY
1549 ,x_CREATION_DATE
1550 ,x_CREATED_BY
1551 ,x_LAST_UPDATE_LOGIN
1552 ,x_LOCATION_PARK_ID
1553 ,x_LOCATION_TYPE_LOOKUP_CODE
1554 ,x_SPACE_TYPE_LOOKUP_CODE
1555 ,x_FUNCTION_TYPE_LOOKUP_CODE
1556 ,x_STANDARD_TYPE_LOOKUP_CODE
1557 ,x_LOCATION_ALIAS
1558 ,x_LOCATION_CODE
1559 ,x_BUILDING
1560 ,x_LEASE_OR_OWNED
1561 ,x_CLASS
1562 ,x_STATUS_TYPE
1563 ,x_FLOOR
1564 ,x_OFFICE
1565 ,x_ADDRESS_ID
1566 ,x_MAX_CAPACITY
1567 ,x_OPTIMUM_CAPACITY
1568 ,x_GROSS_AREA
1569 ,x_RENTABLE_AREA
1570 ,x_USABLE_AREA
1571 ,x_ASSIGNABLE_AREA
1572 ,x_COMMON_AREA
1573 ,x_SUITE
1574 ,x_ALLOCATE_COST_CENTER_CODE
1575 ,x_UOM_CODE
1576 ,x_DESCRIPTION
1577 ,x_PARENT_LOCATION_ID
1578 ,x_INTERFACE_FLAG
1579 ,x_REQUEST_ID
1580 ,x_PROGRAM_APPLICATION_ID
1581 ,x_PROGRAM_ID
1582 ,x_PROGRAM_UPDATE_DATE
1583 ,x_STATUS
1584 ,x_PROPERTY_ID
1585 ,x_ATTRIBUTE_CATEGORY
1586 ,x_ATTRIBUTE1
1587 ,x_ATTRIBUTE2
1588 ,x_ATTRIBUTE3
1589 ,x_ATTRIBUTE4
1590 ,x_ATTRIBUTE5
1591 ,x_ATTRIBUTE6
1592 ,x_ATTRIBUTE7
1593 ,x_ATTRIBUTE8
1594 ,x_ATTRIBUTE9
1595 ,x_ATTRIBUTE10
1596 ,x_ATTRIBUTE11
1597 ,x_ATTRIBUTE12
1598 ,x_ATTRIBUTE13
1599 ,x_ATTRIBUTE14
1600 ,x_ATTRIBUTE15
1601 ,x_COMMON_AREA_FLAG
1602 ,x_org_id
1603 ,x_ACTIVE_START_DATE
1604 ,NVL(x_ACTIVE_END_DATE,G_END_OF_TIME)
1605 ,x_bookable_flag
1606 ,x_occupancy_status_code
1607 ,x_assignable_emp
1608 ,x_assignable_cc
1609 ,x_assignable_cust
1610 ,x_disposition_code
1611 ,x_acc_treatment_code
1612 ,x_source
1613 );
1614
1615 OPEN C;
1616 FETCH C INTO x_rowid;
1617 IF (C%NOTFOUND) THEN
1618 CLOSE C;
1619 pnp_debug_pkg.debug(' InsRow> Error in insert');
1620 RAISE NO_DATA_FOUND;
1621 END IF;
1622 CLOSE C;
1623
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 x_return_status := FND_API.G_RET_STS_ERROR;
1627 fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
1628 fnd_message.set_token('ERR_MSG',sqlerrm);
1629 pnp_debug_pkg.debug(' InsRow> Other errors');
1630 pnp_debug_pkg.debug(sqlerrm);
1631 END;
1632
1633 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.insert_row (-) ReturnStatus: '||x_return_status);
1634
1635 END insert_row;
1636
1637 -------------------------------------------------------------------------------
1638 -- PROCEDURE update_row
1639 --
1640 -- 14-Feb-04 Kiran o validate_gross_area will now be called for both
1641 -- Offices and Sections
1642 -- 01-DEC-04 STripathi o Modified for Portfolio Status Enh BUG# 4030816.
1643 -- Added parameters occupancy_status_code,
1644 -- assignable_emp,assignable_cc, assignable_cust,
1645 -- disposition, acc_treatment.
1646 -- 19-JUL-05 SatyaDeep o Added argument x_source to update the source
1647 -- product from pn_locations_itf for bug#4468893
1648 -- 12-SEP-05 Hareesha o Modified update statement to include row_id
1649 -- in Where clause.
1650 -- 27-feb-06 piagrawa o Bug#5015429 - Modified to make a call to
1651 -- loctn_assgn_area_update when x_assgn_area_chgd_flag
1652 -- is equal to 'CORRECT' or 'UPDATE'
1653 -- 21-AUG-08 rthumma o Bug 7273859 : Modified call to pnt_addr_pkg.update_row
1654 -- to pass NULL for ATTRIBUTE_CATEGORY,attribute1..15
1655 -------------------------------------------------------------------------------
1656 PROCEDURE UPDATE_ROW (
1657 x_LOCATION_ID NUMBER
1658 ,x_LAST_UPDATE_DATE DATE
1659 ,x_LAST_UPDATED_BY NUMBER
1660 ,x_LAST_UPDATE_LOGIN NUMBER
1661 ,x_LOCATION_PARK_ID NUMBER
1662 ,x_LOCATION_TYPE_LOOKUP_CODE VARCHAR2
1663 ,x_SPACE_TYPE_LOOKUP_CODE VARCHAR2
1664 ,x_FUNCTION_TYPE_LOOKUP_CODE VARCHAR2
1665 ,x_STANDARD_TYPE_LOOKUP_CODE VARCHAR2
1666 ,x_BUILDING VARCHAR2
1667 ,x_LEASE_OR_OWNED VARCHAR2
1668 ,x_CLASS VARCHAR2
1669 ,x_STATUS_TYPE VARCHAR2
1670 ,x_FLOOR VARCHAR2
1671 ,x_OFFICE VARCHAR2
1672 ,x_ADDRESS_ID NUMBER
1673 ,x_MAX_CAPACITY NUMBER
1674 ,x_OPTIMUM_CAPACITY NUMBER
1675 ,x_GROSS_AREA NUMBER
1676 ,x_RENTABLE_AREA NUMBER
1677 ,x_USABLE_AREA NUMBER
1678 ,x_ASSIGNABLE_AREA NUMBER
1679 ,x_COMMON_AREA NUMBER
1680 ,x_SUITE VARCHAR2
1681 ,x_ALLOCATE_COST_CENTER_CODE VARCHAR2
1682 ,x_UOM_CODE VARCHAR2
1683 ,x_DESCRIPTION VARCHAR2
1684 ,x_PARENT_LOCATION_ID NUMBER
1685 ,x_INTERFACE_FLAG VARCHAR2
1686 ,x_STATUS VARCHAR2
1687 ,x_PROPERTY_ID NUMBER
1688 ,x_ATTRIBUTE_CATEGORY VARCHAR2
1689 ,x_ATTRIBUTE1 VARCHAR2
1690 ,x_ATTRIBUTE2 VARCHAR2
1691 ,x_ATTRIBUTE3 VARCHAR2
1692 ,x_ATTRIBUTE4 VARCHAR2
1693 ,x_ATTRIBUTE5 VARCHAR2
1694 ,x_ATTRIBUTE6 VARCHAR2
1695 ,x_ATTRIBUTE7 VARCHAR2
1696 ,x_ATTRIBUTE8 VARCHAR2
1697 ,x_ATTRIBUTE9 VARCHAR2
1698 ,x_ATTRIBUTE10 VARCHAR2
1699 ,x_ATTRIBUTE11 VARCHAR2
1700 ,x_ATTRIBUTE12 VARCHAR2
1701 ,x_ATTRIBUTE13 VARCHAR2
1702 ,x_ATTRIBUTE14 VARCHAR2
1703 ,x_ATTRIBUTE15 VARCHAR2
1704 ,x_address_line1 VARCHAR2
1705 ,x_address_line2 VARCHAR2
1706 ,x_address_line3 VARCHAR2
1707 ,x_address_line4 VARCHAR2
1708 ,x_county VARCHAR2
1709 ,x_city VARCHAR2
1710 ,x_state VARCHAR2
1711 ,x_province VARCHAR2
1712 ,x_zip_code VARCHAR2
1713 ,x_country VARCHAR2
1714 ,x_territory_id NUMBER
1715 ,x_addr_last_update_date DATE
1716 ,x_addr_last_updated_by NUMBER
1717 ,x_addr_last_update_login NUMBER
1718 ,x_addr_attribute_category VARCHAR2
1719 ,x_addr_attribute1 VARCHAR2
1720 ,x_addr_attribute2 VARCHAR2
1721 ,x_addr_attribute3 VARCHAR2
1722 ,x_addr_attribute4 VARCHAR2
1723 ,x_addr_attribute5 VARCHAR2
1724 ,x_addr_attribute6 VARCHAR2
1725 ,x_addr_attribute7 VARCHAR2
1726 ,x_addr_attribute8 VARCHAR2
1727 ,x_addr_attribute9 VARCHAR2
1728 ,x_addr_attribute10 VARCHAR2
1729 ,x_addr_attribute11 VARCHAR2
1730 ,x_addr_attribute12 VARCHAR2
1731 ,x_addr_attribute13 VARCHAR2
1732 ,x_addr_attribute14 VARCHAR2
1733 ,x_addr_attribute15 VARCHAR2
1734 ,x_common_area_flag VARCHAR2
1735 ,x_assgn_area_chgd_flag VARCHAR2
1736 ,x_active_start_date DATE
1737 ,x_active_end_date DATE
1738 ,x_return_status OUT NOCOPY varchar2
1739 ,x_return_message OUT NOCOPY varchar2
1740 ,x_bookable_flag VARCHAR2
1741 ,x_occupancy_status_code VARCHAR2
1742 ,x_assignable_emp VARCHAR2
1743 ,x_assignable_cc VARCHAR2
1744 ,x_assignable_cust VARCHAR2
1745 ,x_disposition_code VARCHAR2
1746 ,x_acc_treatment_code VARCHAR2
1747 ,x_source VARCHAR2
1748 )
1749 IS
1750
1751 l_return_status VARCHAR2(2000) := NULL;
1752
1753 BEGIN
1754
1755 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_row (+) LocId: '||x_location_id||', Type: '||x_location_type_lookup_code
1756 ||', ActStrDt: '||TO_CHAR(x_active_start_date, 'MM/DD/YYYY')
1757 ||', ActEndDt: '||TO_CHAR(x_active_end_date, 'MM/DD/YYYY'));
1758 x_return_status := FND_API.G_RET_STS_SUCCESS;
1759
1760 IF x_location_type_lookup_code IN ('OFFICE', 'SECTION') AND
1761 x_rentable_area IS NOT NULL THEN
1762 IF NOT pnt_locations_pkg.validate_gross_area(x_location_id,
1763 x_rentable_area,
1764 x_location_type_lookup_code,
1765 x_active_start_date,
1766 x_active_end_date,
1767 'UPDATE') THEN
1768
1769 fnd_message.set_name('PN', 'PN_GROSS_RENTABLE');
1770 app_exception.raise_Exception;
1771 END IF;
1772 END IF;
1773
1774 IF x_assgn_area_chgd_flag IN ('CORRECT', 'UPDATE') THEN
1775
1776 pnp_util_func.loctn_assgn_area_update(p_loc_id => x_location_id
1777 ,p_assgn_area => x_assignable_area
1778 ,p_str_dt => x_active_start_date
1779 ,p_end_dt => x_active_end_date
1780 );
1781 END IF;
1782
1783 pnp_debug_pkg.put_log_msg('update locations');
1784
1785 UPDATE PN_LOCATIONS_ALL
1786 SET
1787 LAST_UPDATE_DATE = x_LAST_UPDATE_DATE
1788 ,LAST_UPDATED_BY = x_LAST_UPDATED_BY
1789 ,LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN
1790 ,LOCATION_PARK_ID = x_LOCATION_PARK_ID
1791 ,LOCATION_TYPE_LOOKUP_CODE = x_LOCATION_TYPE_LOOKUP_CODE
1792 ,SPACE_TYPE_LOOKUP_CODE = x_SPACE_TYPE_LOOKUP_CODE
1793 ,FUNCTION_TYPE_LOOKUP_CODE = x_FUNCTION_TYPE_LOOKUP_CODE
1794 ,STANDARD_TYPE_LOOKUP_CODE = x_STANDARD_TYPE_LOOKUP_CODE
1795 ,BUILDING = x_BUILDING
1796 ,LEASE_OR_OWNED = x_LEASE_OR_OWNED
1797 ,CLASS = x_CLASS -- Added redwin
1798 ,STATUS_TYPE = x_STATUS_TYPE
1799 ,FLOOR = x_FLOOR
1800 ,OFFICE = x_OFFICE
1801 ,ADDRESS_ID = x_ADDRESS_ID
1802 ,MAX_CAPACITY = x_MAX_CAPACITY
1803 ,OPTIMUM_CAPACITY = x_OPTIMUM_CAPACITY
1804 ,GROSS_AREA = x_GROSS_AREA
1805 ,RENTABLE_AREA = x_RENTABLE_AREA
1806 ,USABLE_AREA = x_USABLE_AREA
1807 ,ASSIGNABLE_AREA = x_ASSIGNABLE_AREA
1808 ,COMMON_AREA = x_COMMON_AREA
1809 ,SUITE = x_SUITE
1810 ,ALLOCATE_COST_CENTER_CODE = x_ALLOCATE_COST_CENTER_CODE
1811 ,UOM_CODE = x_UOM_CODE
1812 ,DESCRIPTION = x_DESCRIPTION
1813 ,PARENT_LOCATION_ID = x_PARENT_LOCATION_ID
1814 ,INTERFACE_FLAG = x_INTERFACE_FLAG
1815 ,STATUS = x_STATUS
1816 ,PROPERTY_ID = x_PROPERTY_ID
1817 ,ATTRIBUTE_CATEGORY = x_ATTRIBUTE_CATEGORY
1818 ,ATTRIBUTE1 = x_ATTRIBUTE1
1819 ,ATTRIBUTE2 = x_ATTRIBUTE2
1820 ,ATTRIBUTE3 = x_ATTRIBUTE3
1821 ,ATTRIBUTE4 = x_ATTRIBUTE4
1822 ,ATTRIBUTE5 = x_ATTRIBUTE5
1823 ,ATTRIBUTE6 = x_ATTRIBUTE6
1824 ,ATTRIBUTE7 = x_ATTRIBUTE7
1825 ,ATTRIBUTE8 = x_ATTRIBUTE8
1826 ,ATTRIBUTE9 = x_ATTRIBUTE9
1827 ,ATTRIBUTE10 = x_ATTRIBUTE10
1828 ,ATTRIBUTE11 = x_ATTRIBUTE11
1829 ,ATTRIBUTE12 = x_ATTRIBUTE12
1830 ,ATTRIBUTE13 = x_ATTRIBUTE13
1831 ,ATTRIBUTE14 = x_ATTRIBUTE14
1832 ,ATTRIBUTE15 = x_ATTRIBUTE15
1833 ,COMMON_AREA_FLAG = x_COMMON_AREA_FLAG
1834 ,ACTIVE_START_DATE = x_active_start_date
1835 ,ACTIVE_END_DATE = NVL(x_active_end_date,g_end_of_time)
1836 ,BOOKABLE_FLAG = x_bookable_flag
1837 ,occupancy_status_code = x_occupancy_status_code
1838 ,assignable_emp = x_assignable_emp
1839 ,assignable_cc = x_assignable_cc
1840 ,assignable_cust = x_assignable_cust
1841 ,disposition_code = x_disposition_code
1842 ,acc_treatment_code = x_acc_treatment_code
1843 ,source = x_source
1844 WHERE ROWID = g_pn_locations_rowid;
1845
1846 IF (SQL%NOTFOUND) THEN
1847 RAISE NO_DATA_FOUND;
1848 END IF;
1849
1850 -----------------------------------------------------------------
1851 -- Call the PN_ADDRESSES update table handler to update address
1852 -- elements. This will only be called when we update the
1853 -- building record.
1854 -----------------------------------------------------------------
1855 IF NVL(x_LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('BUILDING','LAND') THEN
1856 PNT_ADDR_PKG.update_row (
1857 x_address_id,
1858 x_address_line1,
1859 x_address_line2,
1860 x_address_line3,
1861 x_address_line4,
1862 x_county,
1863 x_city,
1864 x_state,
1865 x_province,
1866 x_zip_code,
1867 x_country,
1868 x_territory_id,
1869 x_last_update_date,
1870 x_last_updated_by,
1871 x_last_update_login,
1872 null,
1873 null,
1874 null,
1875 null,
1876 null,
1877 null,
1878 null,
1879 null,
1880 null,
1881 null,
1882 null,
1883 null,
1884 null,
1885 null,
1886 null,
1887 null,
1888 x_addr_attribute_category,
1889 x_addr_attribute1,
1890 x_addr_attribute2,
1891 x_addr_attribute3,
1892 x_addr_attribute4,
1893 x_addr_attribute5,
1894 x_addr_attribute6,
1895 x_addr_attribute7,
1896 x_addr_attribute8,
1897 x_addr_attribute9,
1898 x_addr_attribute10,
1899 x_addr_attribute11,
1900 x_addr_attribute12,
1901 x_addr_attribute13,
1902 x_addr_attribute14,
1903 x_addr_attribute15
1904 );
1905 END IF;
1906
1907 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_row (-) ReturnStatus: '||x_return_status);
1908
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 x_return_status := FND_API.G_RET_STS_ERROR;
1912 fnd_message.set_name('PN','PN_OTHERS_EXCEPTION');
1913 fnd_message.set_token('ERR_MSG',sqlerrm);
1914 pnp_debug_pkg.put_log_msg('OTHER ERRORS'|| sqlerrm);
1915 END UPDATE_ROW;
1916
1917 ------------------------------------------------------------------------
1918 -- PROCEDURE : lock_row
1919 -- 01-DEC-2004 Satish Tripathi o Modified for Portfolio Status Enh BUG# 4030816.
1920 -- Added parameters occupancy_status_code, assignable_emp,
1921 -- assignable_cc, assignable_cust, disposition, acc_treatment.
1922 -- 21-AUG-2008 rthumma o Bug 7273859 : Modified call to pnt_addr_pkg.lock_row
1923 -- to pass NULL for ATTRIBUTE_CATEGORY,attribute1..15
1924 ------------------------------------------------------------------------
1925 PROCEDURE lock_row (
1926 x_LOCATION_ID NUMBER
1927 ,x_LOCATION_PARK_ID NUMBER
1928 ,x_LOCATION_TYPE_LOOKUP_CODE VARCHAR2
1929 ,x_SPACE_TYPE_LOOKUP_CODE VARCHAR2
1930 ,x_FUNCTION_TYPE_LOOKUP_CODE VARCHAR2
1931 ,x_STANDARD_TYPE_LOOKUP_CODE VARCHAR2
1932 ,x_LOCATION_ALIAS VARCHAR2
1933 ,x_LOCATION_CODE VARCHAR2
1934 ,x_BUILDING VARCHAR2
1935 ,x_LEASE_OR_OWNED VARCHAR2
1936 ,x_CLASS VARCHAR2
1937 ,x_STATUS_TYPE VARCHAR2
1938 ,x_FLOOR VARCHAR2
1939 ,x_OFFICE VARCHAR2
1940 ,x_ADDRESS_ID NUMBER
1941 ,x_MAX_CAPACITY NUMBER
1942 ,x_OPTIMUM_CAPACITY NUMBER
1943 ,x_GROSS_AREA NUMBER
1944 ,x_RENTABLE_AREA NUMBER
1945 ,x_USABLE_AREA NUMBER
1946 ,x_ASSIGNABLE_AREA NUMBER
1947 ,x_COMMON_AREA NUMBER
1948 ,x_SUITE VARCHAR2
1949 ,x_ALLOCATE_COST_CENTER_CODE VARCHAR2
1950 ,x_UOM_CODE VARCHAR2
1951 ,x_DESCRIPTION VARCHAR2
1952 ,x_PARENT_LOCATION_ID NUMBER
1953 ,x_INTERFACE_FLAG VARCHAR2
1954 ,x_STATUS VARCHAR2
1955 ,x_PROPERTY_ID NUMBER
1956 ,x_ATTRIBUTE_CATEGORY VARCHAR2
1957 ,x_ATTRIBUTE1 VARCHAR2
1958 ,x_ATTRIBUTE2 VARCHAR2
1959 ,x_ATTRIBUTE3 VARCHAR2
1960 ,x_ATTRIBUTE4 VARCHAR2
1961 ,x_ATTRIBUTE5 VARCHAR2
1962 ,x_ATTRIBUTE6 VARCHAR2
1963 ,x_ATTRIBUTE7 VARCHAR2
1964 ,x_ATTRIBUTE8 VARCHAR2
1965 ,x_ATTRIBUTE9 VARCHAR2
1966 ,x_ATTRIBUTE10 VARCHAR2
1967 ,x_ATTRIBUTE11 VARCHAR2
1968 ,x_ATTRIBUTE12 VARCHAR2
1969 ,x_ATTRIBUTE13 VARCHAR2
1970 ,x_ATTRIBUTE14 VARCHAR2
1971 ,x_ATTRIBUTE15 VARCHAR2
1972 ,x_address_line1 VARCHAR2
1973 ,x_address_line2 VARCHAR2
1974 ,x_address_line3 VARCHAR2
1975 ,x_address_line4 VARCHAR2
1976 ,x_county VARCHAR2
1977 ,x_city VARCHAR2
1978 ,x_state VARCHAR2
1979 ,x_province VARCHAR2
1980 ,x_zip_code VARCHAR2
1981 ,x_country VARCHAR2
1982 ,x_territory_id NUMBER
1983 ,x_addr_attribute_category VARCHAR2
1984 ,x_addr_attribute1 VARCHAR2
1985 ,x_addr_attribute2 VARCHAR2
1986 ,x_addr_attribute3 VARCHAR2
1987 ,x_addr_attribute4 VARCHAR2
1988 ,x_addr_attribute5 VARCHAR2
1989 ,x_addr_attribute6 VARCHAR2
1990 ,x_addr_attribute7 VARCHAR2
1991 ,x_addr_attribute8 VARCHAR2
1992 ,x_addr_attribute9 VARCHAR2
1993 ,x_addr_attribute10 VARCHAR2
1994 ,x_addr_attribute11 VARCHAR2
1995 ,x_addr_attribute12 VARCHAR2
1996 ,x_addr_attribute13 VARCHAR2
1997 ,x_addr_attribute14 VARCHAR2
1998 ,x_addr_attribute15 VARCHAR2
1999 ,x_common_area_flag VARCHAR2
2000 ,x_active_start_date DATE
2001 ,x_active_end_date DATE
2002 ,x_active_start_date_old DATE
2003 ,x_active_end_date_old DATE
2004 ,x_bookable_flag VARCHAR2
2005 ,x_occupancy_status_code VARCHAR2
2006 ,x_assignable_emp VARCHAR2
2007 ,x_assignable_cc VARCHAR2
2008 ,x_assignable_cust VARCHAR2
2009 ,x_disposition_code VARCHAR2
2010 ,x_acc_treatment_code VARCHAR2
2011 )
2012 IS
2013
2014 l_return_status varchar2(30);
2015 l_return_message varchar2(2000);
2016
2017
2018 CURSOR c IS
2019 SELECT *
2020 FROM pn_locations_all
2021 WHERE location_id = x_location_id
2022 AND active_start_date = NVL(x_active_start_date_old, g_start_of_time)
2023 AND active_end_date = NVL(x_active_end_date_old, g_end_of_time)
2024 FOR UPDATE OF location_id NOWAIT;
2025
2026 BEGIN
2027 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.lock_row (+)');
2028
2029 pnp_debug_pkg.debug(' LockRow=> In Parameters :: LocId: '||x_location_id
2030 ||', Type: '||x_location_type_lookup_code);
2031 pnp_debug_pkg.debug(' LockRow=> ActStrDate : '||TO_CHAR(x_active_start_date, 'MM/DD/YYYY')
2032 ||', ActEndDate : '||TO_CHAR(x_active_end_date, 'MM/DD/YYYY'));
2033 pnp_debug_pkg.debug(' LockRow=> ActStrDateOld: '||TO_CHAR(x_active_start_date_old, 'MM/DD/YYYY')
2034 ||', ActEndDateOld: '||TO_CHAR(x_active_end_date_old, 'MM/DD/YYYY'));
2035
2036 OPEN C;
2037 FETCH C INTO G_LOC_RECINFO;
2038 IF (C%NOTFOUND) THEN
2039 CLOSE C;
2040 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
2041 APP_EXCEPTION.Raise_Exception;
2042 ELSE
2043 PNT_LOCATIONS_PKG.SET_ROWID(
2044 p_location_id => x_location_id,
2045 p_active_start_date => x_active_start_date_old,
2046 p_active_end_date => x_active_end_date_old,
2047 x_return_status => l_return_status,
2048 x_return_message => l_return_message);
2049 END IF;
2050 CLOSE C;
2051
2052 IF NOT ((G_LOC_RECINFO.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
2053 OR ((G_LOC_RECINFO.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null))) THEN
2054 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE_CATEGORY',G_LOC_RECINFO.ATTRIBUTE_CATEGORY);
2055 END IF;
2056
2057 IF NOT ((G_LOC_RECINFO.ATTRIBUTE1 = X_ATTRIBUTE1)
2058 OR ((G_LOC_RECINFO.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null))) THEN
2059 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE1', G_LOC_RECINFO.attribute1);
2060 END IF;
2061
2062 IF NOT ((G_LOC_RECINFO.ATTRIBUTE2 = X_ATTRIBUTE2)
2063 OR ((G_LOC_RECINFO.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null))) THEN
2064 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE2', G_LOC_RECINFO.attribute2);
2065 END IF;
2066
2067 IF NOT ((G_LOC_RECINFO.ATTRIBUTE3 = X_ATTRIBUTE3)
2068 OR ((G_LOC_RECINFO.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null))) THEN
2069 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE3', G_LOC_RECINFO.attribute3);
2070 END IF;
2071
2072 IF NOT ((G_LOC_RECINFO.ATTRIBUTE4 = X_ATTRIBUTE4)
2073 OR ((G_LOC_RECINFO.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null))) THEN
2074 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE4', G_LOC_RECINFO.attribute4);
2075 END IF;
2076
2077 IF NOT ((G_LOC_RECINFO.ATTRIBUTE5 = X_ATTRIBUTE5)
2078 OR ((G_LOC_RECINFO.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null))) THEN
2079 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE5', G_LOC_RECINFO.attribute5);
2080 END IF;
2081
2082 IF NOT ((G_LOC_RECINFO.ATTRIBUTE6 = X_ATTRIBUTE6)
2083 OR ((G_LOC_RECINFO.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null))) THEN
2084 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE6', G_LOC_RECINFO.attribute6);
2085 END IF;
2086
2087 IF NOT ((G_LOC_RECINFO.ATTRIBUTE7 = X_ATTRIBUTE7)
2088 OR ((G_LOC_RECINFO.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null))) THEN
2089 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE7', G_LOC_RECINFO.attribute7);
2090 END IF;
2091
2092 IF NOT ((G_LOC_RECINFO.ATTRIBUTE8 = X_ATTRIBUTE8)
2093 OR ((G_LOC_RECINFO.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null))) THEN
2094 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE8', G_LOC_RECINFO.attribute8);
2095 END IF;
2096
2097 IF NOT ((G_LOC_RECINFO.ATTRIBUTE9 = X_ATTRIBUTE9)
2098 OR ((G_LOC_RECINFO.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null))) THEN
2099 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE9', G_LOC_RECINFO.attribute9);
2100 END IF;
2101
2102 IF NOT ((G_LOC_RECINFO.ATTRIBUTE10 = X_ATTRIBUTE10)
2103 OR ((G_LOC_RECINFO.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null))) THEN
2104 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE10', G_LOC_RECINFO.attribute10);
2105 END IF;
2106
2107 IF NOT ((G_LOC_RECINFO.ATTRIBUTE11 = X_ATTRIBUTE11)
2108 OR ((G_LOC_RECINFO.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null))) THEN
2109 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE11', G_LOC_RECINFO.attribute11);
2110 END IF;
2111
2112 IF NOT ((G_LOC_RECINFO.ATTRIBUTE12 = X_ATTRIBUTE12)
2113 OR ((G_LOC_RECINFO.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null))) THEN
2114 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE12', G_LOC_RECINFO.attribute12);
2115 END IF;
2116
2117 IF NOT ((G_LOC_RECINFO.ATTRIBUTE13 = X_ATTRIBUTE13)
2118 OR ((G_LOC_RECINFO.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null))) THEN
2119 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE13', G_LOC_RECINFO.attribute13);
2120 END IF;
2121
2122 IF NOT ((G_LOC_RECINFO.ATTRIBUTE14 = X_ATTRIBUTE14)
2123 OR ((G_LOC_RECINFO.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null))) THEN
2124 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE14', G_LOC_RECINFO.attribute14);
2125 END IF;
2126
2127 IF NOT ((G_LOC_RECINFO.ATTRIBUTE15 = X_ATTRIBUTE15)
2128 OR ((G_LOC_RECINFO.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null))) THEN
2129 pn_var_rent_pkg.lock_row_exception('ATTRIBUTE15', G_LOC_RECINFO.attribute15);
2130 END IF;
2131
2132 IF NOT ((G_LOC_RECINFO.COMMON_AREA_FLAG = X_COMMON_AREA_FLAG)
2133 OR ((G_LOC_RECINFO.COMMON_AREA_FLAG is null) AND (X_COMMON_AREA_FLAG is null))) THEN
2134 pn_var_rent_pkg.lock_row_exception('COMMON_AREA_FLAG',G_LOC_RECINFO.COMMON_AREA_FLAG);
2135 END IF;
2136
2137 IF NOT (G_LOC_RECINFO.LOCATION_TYPE_LOOKUP_CODE = X_LOCATION_TYPE_LOOKUP_CODE) THEN
2138 pn_var_rent_pkg.lock_row_exception('LOCATION_TYPE_LOOKUP_CODE',G_LOC_RECINFO.LOCATION_TYPE_LOOKUP_CODE);
2139 END IF;
2140
2141 IF NOT ((G_LOC_RECINFO.FUNCTION_TYPE_LOOKUP_CODE = X_FUNCTION_TYPE_LOOKUP_CODE)
2142 OR ((G_LOC_RECINFO.FUNCTION_TYPE_LOOKUP_CODE is null) AND (X_FUNCTION_TYPE_LOOKUP_CODE is null))) THEN
2143 pn_var_rent_pkg.lock_row_exception('FUNCTION_TYPE_LOOKUP_CODE',G_LOC_RECINFO.FUNCTION_TYPE_LOOKUP_CODE);
2144 END IF;
2145
2146
2147 IF NOT ((G_LOC_RECINFO.SPACE_TYPE_LOOKUP_CODE = X_SPACE_TYPE_LOOKUP_CODE)
2148 OR ((G_LOC_RECINFO.SPACE_TYPE_LOOKUP_CODE is null) AND (X_SPACE_TYPE_LOOKUP_CODE is null))) THEN
2149 pn_var_rent_pkg.lock_row_exception('SPACE_TYPE_LOOKUP_CODE',G_LOC_RECINFO.SPACE_TYPE_LOOKUP_CODE);
2150 END IF;
2151
2152
2153 IF NOT ((G_LOC_RECINFO.STANDARD_TYPE_LOOKUP_CODE = X_STANDARD_TYPE_LOOKUP_CODE)
2154 OR ((G_LOC_RECINFO.STANDARD_TYPE_LOOKUP_CODE is null) AND (X_STANDARD_TYPE_LOOKUP_CODE is null))) THEN
2155 pn_var_rent_pkg.lock_row_exception('STANDARD_TYPE_LOOKUP_CODE',G_LOC_RECINFO.STANDARD_TYPE_LOOKUP_CODE);
2156 END IF;
2157
2158 IF NOT (G_LOC_RECINFO.LOCATION_CODE = X_LOCATION_CODE) THEN
2159 pn_var_rent_pkg.lock_row_exception('LOCATION_CODE',G_LOC_RECINFO.LOCATION_CODE);
2160 END IF;
2161
2162 IF NOT ((G_LOC_RECINFO.BUILDING = X_BUILDING)
2163 OR ((G_LOC_RECINFO.BUILDING is null) AND (X_BUILDING is null))) THEN
2164 pn_var_rent_pkg.lock_row_exception('BUILDING',G_LOC_RECINFO.BUILDING);
2165 END IF;
2166
2167 IF NOT ((G_LOC_RECINFO.FLOOR = X_FLOOR)
2168 OR ((G_LOC_RECINFO.FLOOR is null) AND (X_FLOOR is null))) THEN
2169 pn_var_rent_pkg.lock_row_exception('FLOOR',G_LOC_RECINFO.FLOOR);
2170 END IF;
2171
2172 IF NOT ((G_LOC_RECINFO.LOCATION_ALIAS = X_LOCATION_ALIAS)
2173 OR ((G_LOC_RECINFO.LOCATION_ALIAS is null) AND (X_LOCATION_ALIAS is null))) THEN
2174 pn_var_rent_pkg.lock_row_exception('LOCATION_ALIAS',G_LOC_RECINFO.LOCATION_ALIAS);
2175 END IF;
2176
2177 IF NOT ((G_LOC_RECINFO.PROPERTY_ID = X_PROPERTY_ID)
2178 OR ((G_LOC_RECINFO.PROPERTY_ID is null) AND (X_PROPERTY_ID is null))) THEN
2179 pn_var_rent_pkg.lock_row_exception('PROPERTY_ID',G_LOC_RECINFO.PROPERTY_ID);
2180 END IF;
2181
2182 IF NOT ((G_LOC_RECINFO.PARENT_LOCATION_ID = X_PARENT_LOCATION_ID)
2183 OR ((G_LOC_RECINFO.PARENT_LOCATION_ID is null) AND (X_PARENT_LOCATION_ID is null))) THEN
2184 pn_var_rent_pkg.lock_row_exception('PARENT_LOCATION_ID',G_LOC_RECINFO.PARENT_LOCATION_ID);
2185 END IF;
2186
2187 IF NOT ((G_LOC_RECINFO.INTERFACE_FLAG = X_INTERFACE_FLAG)
2188 OR ((G_LOC_RECINFO.INTERFACE_FLAG is null) AND (X_INTERFACE_FLAG is null))) THEN
2189 pn_var_rent_pkg.lock_row_exception('INTERFACE_FLAG',G_LOC_RECINFO.INTERFACE_FLAG);
2190 END IF;
2191
2192 IF NOT ((G_LOC_RECINFO.STATUS = X_STATUS)
2193 OR ((G_LOC_RECINFO.STATUS is null) AND (X_STATUS is null))) THEN
2194 pn_var_rent_pkg.lock_row_exception('STATUS',G_LOC_RECINFO.STATUS);
2195 END IF;
2196
2197
2198 IF NOT ((G_LOC_RECINFO.ACTIVE_START_DATE = x_ACTIVE_START_DATE)
2199 OR ((G_LOC_RECINFO.ACTIVE_START_DATE is null) AND (x_ACTIVE_START_DATE is null))) THEN
2200 pn_var_rent_pkg.lock_row_exception('ACTIVE_START_DATE',G_LOC_RECINFO.ACTIVE_START_DATE);
2201 END IF;
2202
2203 IF NOT ((G_LOC_RECINFO.ACTIVE_END_DATE = x_ACTIVE_END_DATE)
2204 OR ((G_LOC_RECINFO.ACTIVE_END_DATE is null) AND (x_ACTIVE_END_DATE is null))) THEN
2205 pn_var_rent_pkg.lock_row_exception('ACTIVE_END_DATE',G_LOC_RECINFO.ACTIVE_END_DATE);
2206 END IF;
2207
2208 -- DO SPECIAL CHECKS FOR PARTICULAR KINDS
2209
2210 IF (NVL(x_LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('BUILDING','LAND')) THEN
2211 IF NOT ((G_LOC_RECINFO.LOCATION_PARK_ID = X_LOCATION_PARK_ID)
2212 OR ((G_LOC_RECINFO.LOCATION_PARK_ID is null) AND (X_LOCATION_PARK_ID is null))) THEN
2213 pn_var_rent_pkg.lock_row_exception('LOCATION_PARK_ID',G_LOC_RECINFO.LOCATION_PARK_ID);
2214 END IF;
2215
2216 IF NOT ((G_LOC_RECINFO.LEASE_OR_OWNED = X_LEASE_OR_OWNED)
2217 OR ((G_LOC_RECINFO.LEASE_OR_OWNED is null) AND (X_LEASE_OR_OWNED is null))) THEN
2218 pn_var_rent_pkg.lock_row_exception('LEASE_OR_OWNED',G_LOC_RECINFO.LEASE_OR_OWNED);
2219 END IF;
2220
2221 IF NOT ((G_LOC_RECINFO.CLASS = X_CLASS)
2222 OR ((G_LOC_RECINFO.CLASS is null) AND (X_CLASS is null))) THEN
2223 pn_var_rent_pkg.lock_row_exception('CLASS',G_LOC_RECINFO.CLASS);
2224 END IF;
2225
2226 IF NOT ((G_LOC_RECINFO.STATUS_TYPE = X_STATUS_TYPE)
2227 OR ((G_LOC_RECINFO.STATUS_TYPE is null) AND (X_STATUS_TYPE is null))) THEN
2228 pn_var_rent_pkg.lock_row_exception('STATUS_TYPE',G_LOC_RECINFO.STATUS_TYPE);
2229 END IF;
2230
2231 IF NOT ((G_LOC_RECINFO.OFFICE = X_OFFICE)
2232 OR ((G_LOC_RECINFO.OFFICE is null) AND (X_OFFICE is null))) THEN
2233 pn_var_rent_pkg.lock_row_exception('OFFICE',G_LOC_RECINFO.OFFICE);
2234 END IF;
2235
2236 IF NOT ((G_LOC_RECINFO.ADDRESS_ID = X_ADDRESS_ID)
2237 OR ((G_LOC_RECINFO.ADDRESS_ID is null) AND (X_ADDRESS_ID is null))) THEN
2238 pn_var_rent_pkg.lock_row_exception('ADDRESS_ID',G_LOC_RECINFO.ADDRESS_ID);
2239 END IF;
2240
2241 IF NOT ((G_LOC_RECINFO.GROSS_AREA = X_GROSS_AREA)
2242 OR ((G_LOC_RECINFO.GROSS_AREA is null) AND (X_GROSS_AREA is null))) THEN
2243 pn_var_rent_pkg.lock_row_exception('GROSS_AREA',G_LOC_RECINFO.GROSS_AREA);
2244 END IF;
2245
2246 IF NOT ((G_LOC_RECINFO.SUITE = X_SUITE)
2247 OR ((G_LOC_RECINFO.SUITE is null) AND (X_SUITE is null))) THEN
2248 pn_var_rent_pkg.lock_row_exception('SUITE',G_LOC_RECINFO.SUITE);
2249 END IF;
2250
2251 IF NOT ((G_LOC_RECINFO.ALLOCATE_COST_CENTER_CODE = X_ALLOCATE_COST_CENTER_CODE)
2252 OR ((G_LOC_RECINFO.ALLOCATE_COST_CENTER_CODE is null) AND (X_ALLOCATE_COST_CENTER_CODE is null))) THEN
2253 pn_var_rent_pkg.lock_row_exception('ALLOCATE_COST_CENTER_CODE',G_LOC_RECINFO.ALLOCATE_COST_CENTER_CODE);
2254 END IF;
2255
2256 IF NOT ((G_LOC_RECINFO.UOM_CODE = X_UOM_CODE)
2257 OR ((G_LOC_RECINFO.UOM_CODE is null) AND (X_UOM_CODE is null))) THEN
2258 pn_var_rent_pkg.lock_row_exception('UOM_CODE',G_LOC_RECINFO.UOM_CODE);
2259 END IF;
2260
2261 IF NOT ((G_LOC_RECINFO.DESCRIPTION = X_DESCRIPTION)
2262 OR ((G_LOC_RECINFO.DESCRIPTION is null) AND (X_DESCRIPTION is null))) THEN
2263 pn_var_rent_pkg.lock_row_exception('DESCRIPTION',G_LOC_RECINFO.DESCRIPTION);
2264 END IF;
2265
2266 ELSIF (NVL(x_LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('OFFICE','SECTION')) THEN
2267
2268 IF NOT ((G_LOC_RECINFO.OFFICE = X_OFFICE)
2269 OR ((G_LOC_RECINFO.OFFICE is null) AND (X_OFFICE is null))) THEN
2270 pn_var_rent_pkg.lock_row_exception('OFFICE',G_LOC_RECINFO.OFFICE);
2271 END IF;
2272
2273 IF NOT ((G_LOC_RECINFO.MAX_CAPACITY = X_MAX_CAPACITY)
2274 OR ((G_LOC_RECINFO.MAX_CAPACITY is null) AND (X_MAX_CAPACITY is null))) THEN
2275 pn_var_rent_pkg.lock_row_exception('MAX_CAPACITY',G_LOC_RECINFO.MAX_CAPACITY);
2276 END IF;
2277
2278 IF NOT ((G_LOC_RECINFO.OPTIMUM_CAPACITY = X_OPTIMUM_CAPACITY)
2279 OR ((G_LOC_RECINFO.OPTIMUM_CAPACITY is null) AND (X_OPTIMUM_CAPACITY is null))) THEN
2280 pn_var_rent_pkg.lock_row_exception('OPTIMUM_CAPACITY',G_LOC_RECINFO.OPTIMUM_CAPACITY);
2281 END IF;
2282
2283 IF NOT ((G_LOC_RECINFO.RENTABLE_AREA = X_RENTABLE_AREA)
2284 OR ((G_LOC_RECINFO.RENTABLE_AREA is null) AND (X_RENTABLE_AREA is null))) THEN
2285 pn_var_rent_pkg.lock_row_exception('RENTABLE_AREA',G_LOC_RECINFO.RENTABLE_AREA);
2286 END IF;
2287
2288 IF NOT ((G_LOC_RECINFO.USABLE_AREA = X_USABLE_AREA)
2289 OR ((G_LOC_RECINFO.USABLE_AREA is null) AND (X_USABLE_AREA is null))) THEN
2290 pn_var_rent_pkg.lock_row_exception('USABLE_AREA',G_LOC_RECINFO.USABLE_AREA);
2291 END IF;
2292
2293 IF NOT ((G_LOC_RECINFO.ASSIGNABLE_AREA = X_ASSIGNABLE_AREA)
2294 OR ((G_LOC_RECINFO.ASSIGNABLE_AREA is null) AND (X_ASSIGNABLE_AREA is null))) THEN
2295 pn_var_rent_pkg.lock_row_exception('ASSIGNABLE_AREA',G_LOC_RECINFO.ASSIGNABLE_AREA);
2296 END IF;
2297
2298 IF NOT ((G_LOC_RECINFO.COMMON_AREA = X_COMMON_AREA)
2299 OR ((G_LOC_RECINFO.COMMON_AREA is null) AND (X_COMMON_AREA is null))) THEN
2300 pn_var_rent_pkg.lock_row_exception('COMMON_AREA',G_LOC_RECINFO.COMMON_AREA);
2301 END IF;
2302
2303 IF NOT ((G_LOC_RECINFO.SUITE = X_SUITE)
2304 OR ((G_LOC_RECINFO.SUITE is null) AND (X_SUITE is null))) THEN
2305 pn_var_rent_pkg.lock_row_exception('SUITE',G_LOC_RECINFO.SUITE);
2306 END IF;
2307
2308 IF NOT ((G_LOC_RECINFO.BOOKABLE_FLAG = X_BOOKABLE_FLAG)
2309 OR ((G_LOC_RECINFO.BOOKABLE_FLAG is null) AND (X_BOOKABLE_FLAG is null))) THEN
2310 pn_var_rent_pkg.lock_row_exception('BOOKABLE_FLAG', G_LOC_RECINFO.BOOKABLE_FLAG);
2311 END IF;
2312
2313 IF NOT ((g_loc_recinfo.occupancy_status_code = x_occupancy_status_code)
2314 OR ((g_loc_recinfo.occupancy_status_code IS NULL) AND (x_occupancy_status_code IS NULL))) THEN
2315 pn_var_rent_pkg.lock_row_exception('OCCUPANCY_STATUS_CODE', g_loc_recinfo.occupancy_status_code);
2316 END IF;
2317
2318 IF NOT ((g_loc_recinfo.assignable_emp = x_assignable_emp)
2319 OR ((g_loc_recinfo.assignable_emp IS NULL) AND (x_assignable_emp IS NULL))) THEN
2320 pn_var_rent_pkg.lock_row_exception('ASSIGNABLE_EMP', g_loc_recinfo.assignable_emp);
2321 END IF;
2322
2323 IF NOT ((g_loc_recinfo.assignable_cc = x_assignable_cc)
2324 OR ((g_loc_recinfo.assignable_cc IS NULL) AND (x_assignable_cc IS NULL))) THEN
2325 pn_var_rent_pkg.lock_row_exception('ASSIGNABLE_CC', g_loc_recinfo.assignable_cc);
2326 END IF;
2327
2328 IF NOT ((g_loc_recinfo.assignable_cust = x_assignable_cust)
2329 OR ((g_loc_recinfo.assignable_cust IS NULL) AND (x_assignable_cust IS NULL))) THEN
2330 pn_var_rent_pkg.lock_row_exception('ASSIGNABLE_CUST', g_loc_recinfo.assignable_cust);
2331 END IF;
2332
2333 IF NOT ((g_loc_recinfo.disposition_code = x_disposition_code)
2334 OR ((g_loc_recinfo.disposition_code IS NULL) AND (x_disposition_code IS NULL))) THEN
2335 pn_var_rent_pkg.lock_row_exception('DISPOSITION_CODE', g_loc_recinfo.disposition_code);
2336 END IF;
2337
2338 IF NOT ((g_loc_recinfo.acc_treatment_code = x_acc_treatment_code)
2339 OR ((g_loc_recinfo.acc_treatment_code IS NULL) AND (x_acc_treatment_code IS NULL))) THEN
2340 pn_var_rent_pkg.lock_row_exception('ACC_TREATMENT_CODE', g_loc_recinfo.acc_treatment_code);
2341 END IF;
2342
2343 END IF;
2344
2345 -- NO SPECIAL CHECKS FOR TYPE 'LAND' AND 'PARCEL'
2346
2347 -----------------------------------------------------------------
2348 -- Call the PN_ADDRESSES lock table handler to lock the address
2349 -- row for update. This will only be called when we lock the
2350 -- building record.
2351 -----------------------------------------------------------------
2352 IF (NVL(x_LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('BUILDING','LAND')) THEN
2353 PNT_ADDR_PKG.LOCK_ROW(
2354 x_address_id,
2355 x_address_line1,
2356 x_address_line2,
2357 x_address_line3,
2358 x_address_line4,
2359 x_county,
2360 x_city,
2361 x_state,
2362 x_province,
2363 x_zip_code,
2364 x_country,
2365 x_territory_id,
2366 null,
2367 null,
2368 null,
2369 null,
2370 null,
2371 null,
2372 null,
2373 null,
2374 null,
2375 null,
2376 null,
2377 null,
2378 null,
2379 null,
2380 null,
2381 null,
2382 x_addr_attribute_category,
2383 x_addr_attribute1,
2384 x_addr_attribute2,
2385 x_addr_attribute3,
2386 x_addr_attribute4,
2387 x_addr_attribute5,
2388 x_addr_attribute6,
2389 x_addr_attribute7,
2390 x_addr_attribute8,
2391 x_addr_attribute9,
2392 x_addr_attribute10,
2393 x_addr_attribute11,
2394 x_addr_attribute12,
2395 x_addr_attribute13,
2396 x_addr_attribute14,
2397 x_addr_attribute15
2398 );
2399
2400 END IF;
2401
2402 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.lock_row (-)');
2403
2404 END lock_row;
2405
2406 -------------------------------------------------------------------------------
2407 -- PROCEDURE : update_child_for_dates
2408 -- PURPOSE : This procedure updates or deletes children locations during
2409 -- bring in process.
2410 -- IN PARAMS : Location Id, Active Start Date, Active End Date,
2411 -- Active Start Date Old, Active End Date Old, Location Type
2412 -- Lookup Code.
2413 -- HISTORY :
2414 --
2415 -- 11-NOV-02 Ganesh o Created.
2416 -- 18-FEB-03 MMisra o Added p_location_type_lookup_code new IN parameter.
2417 -- Changed the procedure to check for tenancy defined
2418 -- at lease.
2419 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced pn_space_assign_emp,
2420 -- pn_space_assign_cust with _ALL table.
2421 -------------------------------------------------------------------------------
2422 PROCEDURE Update_Child_for_Dates (
2423 p_location_id IN NUMBER
2424 ,p_active_start_date IN DATE
2425 ,p_active_end_date IN DATE
2426 ,p_active_start_date_old IN DATE
2427 ,p_active_end_date_old IN DATE
2428 ,p_location_type_lookup_code IN VARCHAR2
2429 ,x_return_status OUT NOCOPY VARCHAR2
2430 ,x_return_message OUT NOCOPY VARCHAR2
2431 )
2432 IS
2433
2434 CURSOR check_tenancy IS
2435 SELECT 'Y'
2436 FROM pn_tenancies_all pt
2437 WHERE location_id IN (SELECT location_id
2438 FROM pn_locations_all
2439 WHERE active_start_date > p_active_end_date
2440 START WITH location_id = p_location_id
2441 CONNECT BY PRIOR location_id = parent_location_id)
2442 AND NOT EXISTS (SELECT '1'
2443 FROM pn_locations_all pl1
2444 where pl1.location_id = pt.location_id
2445 AND active_start_date <= p_active_end_date)
2446 AND ROWNUM < 2;
2447
2448 CURSOR check_start_tenancy IS
2449 SELECT 'Y'
2450 FROM pn_tenancies_all pt
2451 WHERE location_id IN (SELECT location_id
2452 FROM pn_locations_all
2453 WHERE active_end_date < p_active_start_date
2454 START WITH location_id = p_location_id
2455 CONNECT BY PRIOR location_id = parent_location_id)
2456 AND NOT EXISTS (SELECT '1'
2457 FROM pn_locations_all pl1
2458 WHERE pl1.location_id = pt.location_id
2459 AND active_end_date >= p_active_start_date)
2460 AND ROWNUM < 2;
2461
2462 l_exists VARCHAR2(1) := 'N';
2463
2464 BEGIN
2465
2466 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_child_for_dates (+)');
2467 pnp_debug_pkg.debug(' UpdChiDt=> In Parameters :: p_location_id: '||p_location_id);
2468 pnp_debug_pkg.debug(' UpdChiDt=> p_active_start_date : '||TO_CHAR(p_active_start_date, 'MM/DD/YYYY')
2469 ||', p_active_end_date : '||TO_CHAR(p_active_end_date, 'MM/DD/YYYY'));
2470 pnp_debug_pkg.debug(' UpdChiDt=> p_active_start_date_old: '||TO_CHAR(p_active_start_date_old, 'MM/DD/YYYY')
2471 ||', p_active_end_date_old: '||TO_CHAR(p_active_end_date_old, 'MM/DD/YYYY'));
2472
2473 IF p_active_end_date < p_active_end_date_old AND
2474 p_location_type_lookup_code NOT IN ('OFFICE','SECTION')THEN
2475
2476 l_exists := 'N';
2477
2478 OPEN check_tenancy;
2479 FETCH check_tenancy into l_exists;
2480 CLOSE check_tenancy;
2481
2482 IF l_exists = 'Y' THEN
2483 fnd_message.set_name ('PN','PN_LOCTN_TENANCY_CHK_MSG');
2484 x_return_status := 'E';
2485 RETURN;
2486 END IF;
2487
2488 DELETE FROM pn_locations_all
2489 WHERE (location_id, active_start_date,active_end_date) IN
2490 (SELECT location_id, active_start_date,active_end_date
2491 FROM pn_locations_all pl
2492 WHERE active_start_date > p_active_end_date
2493 AND NOT EXISTS (SELECT '1'
2494 FROM pn_space_assign_emp_all
2495 WHERE location_id = pl.location_id
2496 AND p_active_end_date BETWEEN emp_assign_start_date
2497 AND emp_assign_end_date)
2498 AND NOT EXISTS (SELECT '1'
2499 FROM pn_space_assign_cust_all
2500 WHERE location_id = pl.location_id
2501 AND p_active_end_date BETWEEN cust_assign_start_date
2502 AND cust_assign_end_date)
2503 START WITH location_id = p_location_id
2504 CONNECT BY PRIOR location_id = parent_location_id);
2505
2506
2507 UPDATE pn_locations_all
2508 SET active_end_date = p_active_end_date
2509 WHERE active_start_date <= p_active_end_date
2510 AND active_end_date > p_active_end_date
2511 AND location_id IN (SELECT location_id
2512 FROM pn_locations_all
2513 START WITH location_id = p_location_id
2514 CONNECT BY PRIOR location_id = parent_location_id);
2515
2516 END IF;
2517
2518 IF p_active_start_date > p_active_start_date_old AND
2519 p_location_type_lookup_code NOT IN ('OFFICE','SECTION')THEN
2520
2521 l_exists := 'N';
2522
2523 OPEN check_start_tenancy;
2524 FETCH check_start_tenancy into l_exists;
2525 CLOSE check_start_tenancy;
2526
2527 IF l_exists = 'Y' THEN
2528 fnd_message.set_name ('PN','PN_LOCTN_TENANCY_CHK_MSG');
2529 x_return_status := 'E';
2530 RETURN;
2531 END IF;
2532
2533 DELETE FROM pn_locations_all
2534 WHERE (location_id, active_start_date,active_end_date) IN
2535 (SELECT location_id, active_start_date,active_end_date
2536 FROM pn_locations_all pl
2537 WHERE active_end_date < p_active_start_date
2538 AND NOT EXISTS (SELECT '1'
2539 FROM pn_space_assign_emp_all
2540 WHERE location_id = pl.location_id
2541 AND p_active_start_date BETWEEN emp_assign_start_date
2542 AND emp_assign_end_date)
2543 AND NOT EXISTS (SELECT '1'
2544 FROM pn_space_assign_cust_all
2545 WHERE location_id = pl.location_id
2546 AND p_active_start_date BETWEEN cust_assign_start_date
2547 AND cust_assign_end_date)
2548 START WITH location_id = p_location_id
2549 CONNECT BY PRIOR location_id = parent_location_id);
2550
2551 UPDATE pn_locations_all
2552 SET active_start_date = p_active_start_date
2553 WHERE active_start_date < p_active_start_date
2554 AND active_end_date >= p_active_start_date
2555 AND location_id IN (SELECT location_id
2556 FROM pn_locations_all
2557 START WITH location_id = p_location_id
2558 CONNECT BY PRIOR location_id = parent_location_id);
2559
2560 END IF;
2561
2562 x_return_status := FND_API.G_RET_STS_SUCCESS;
2563 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_child_for_dates (-) ReturnStatus: '||x_return_status);
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567 x_return_status := FND_API.G_RET_STS_ERROR;
2568 fnd_message.set_name ('PN','PN_OTHERS_EXCEPTION');
2569 fnd_message.set_token('ERR_MSG',sqlerrm);
2570
2571 END update_child_for_dates;
2572
2573 -------------------------------------------------------------------------------
2574 -- check_for_popup : This procedure compares the form field values
2575 -- with the locked row and returns 'Y', if the value has changed
2576 -- HISTORY:
2577 -- 14-OCT-03 Anand o Added code to consider the new column bookable_flag.
2578 -- 01-DEC-2004 Satish Tripathi o Modified for Portfolio Status Enh BUG# 4030816.
2579 -- Added code to condider columns occupancy_status_code, assignable_emp,
2580 -- assignable_cc, assignable_cust.
2581 -------------------------------------------------------------------------------
2582 PROCEDURE check_for_popup (p_pn_locations_rec pn_locations_all%rowtype,
2583 p_start_date_old IN DATE,
2584 p_end_date_old IN DATE,
2585 x_flag OUT NOCOPY VARCHAR2,
2586 x_return_status OUT NOCOPY VARCHAR2,
2587 x_return_message OUT NOCOPY VARCHAR2) IS
2588
2589 BEGIN
2590
2591 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_for_popup (+) LocId: '||p_pn_locations_rec.location_id
2592 ||', LocCd: '||p_pn_locations_rec.location_code
2593 ||', Type: '||p_pn_locations_rec.location_type_lookup_code);
2594 x_flag := 'N';
2595
2596 -- Compare the form field values in p_pn_locations_rec with
2597 -- the row locked by the lock_row procedure
2598 -- and return 'Y' if the values are different
2599
2600 IF ((G_LOC_RECINFO.ATTRIBUTE_CATEGORY <> p_pn_locations_rec.attribute_category)
2601 OR ((G_LOC_RECINFO.ATTRIBUTE_CATEGORY is NULL) AND (p_pn_locations_rec.attribute_category is NOT NULL))
2602 OR ((G_LOC_RECINFO.ATTRIBUTE_CATEGORY is NOT NULL) AND (p_pn_locations_rec.attribute_category is NULL))) THEN
2603 x_flag := 'Y';
2604 return;
2605 END IF;
2606
2607 IF ((G_LOC_RECINFO.ATTRIBUTE1 <> p_pn_locations_rec.attribute1)
2608 OR ((G_LOC_RECINFO.ATTRIBUTE1 is NULL) AND (p_pn_locations_rec.attribute1 is NOT NULL))
2609 OR ((G_LOC_RECINFO.ATTRIBUTE1 is NOT NULL) AND (p_pn_locations_rec.attribute1 is NULL))) THEN
2610 x_flag := 'Y';
2611 return;
2612 END IF;
2613
2614 IF ((G_LOC_RECINFO.ATTRIBUTE2 <> p_pn_locations_rec.attribute2)
2615 OR ((G_LOC_RECINFO.ATTRIBUTE2 is NULL) AND (p_pn_locations_rec.attribute2 is NOT NULL))
2616 OR ((G_LOC_RECINFO.ATTRIBUTE2 is NOT NULL) AND (p_pn_locations_rec.attribute2 is NULL))) THEN
2617 x_flag := 'Y';
2618 return;
2619 END IF;
2620
2621 IF ((G_LOC_RECINFO.ATTRIBUTE3 <> p_pn_locations_rec.attribute3)
2622 OR ((G_LOC_RECINFO.ATTRIBUTE3 is NULL) AND (p_pn_locations_rec.attribute3 is NOT NULL))
2623 OR ((G_LOC_RECINFO.ATTRIBUTE3 is NOT NULL) AND (p_pn_locations_rec.attribute3 is NULL))) THEN
2624 x_flag := 'Y';
2625 return;
2626 END IF;
2627 IF ((G_LOC_RECINFO.ATTRIBUTE4 <> p_pn_locations_rec.attribute4)
2628 OR ((G_LOC_RECINFO.ATTRIBUTE4 is NULL) AND (p_pn_locations_rec.attribute4 is NOT NULL))
2629 OR ((G_LOC_RECINFO.ATTRIBUTE4 is NOT NULL) AND (p_pn_locations_rec.attribute4 is NULL))) THEN
2630 x_flag := 'Y';
2631 return;
2632 END IF;
2633 IF ((G_LOC_RECINFO.ATTRIBUTE5 <> p_pn_locations_rec.attribute5)
2634 OR ((G_LOC_RECINFO.ATTRIBUTE5 is NULL) AND (p_pn_locations_rec.attribute5 is NOT NULL))
2635 OR ((G_LOC_RECINFO.ATTRIBUTE5 is NOT NULL) AND (p_pn_locations_rec.attribute5 is NULL))) THEN
2636 x_flag := 'Y';
2637 return;
2638 END IF;
2639 IF ((G_LOC_RECINFO.ATTRIBUTE6 <> p_pn_locations_rec.attribute6)
2640 OR ((G_LOC_RECINFO.ATTRIBUTE6 is NULL) AND (p_pn_locations_rec.attribute6 is NOT NULL))
2641 OR ((G_LOC_RECINFO.ATTRIBUTE6 is NOT NULL) AND (p_pn_locations_rec.attribute6 is NULL))) THEN
2642 x_flag := 'Y';
2643 return;
2644 END IF;
2645 IF ((G_LOC_RECINFO.ATTRIBUTE7 <> p_pn_locations_rec.attribute7)
2646 OR ((G_LOC_RECINFO.ATTRIBUTE7 is NULL) AND (p_pn_locations_rec.attribute7 is NOT NULL))
2647 OR ((G_LOC_RECINFO.ATTRIBUTE7 is NOT NULL) AND (p_pn_locations_rec.attribute7 is NULL))) THEN
2648 x_flag := 'Y';
2649 return;
2650 END IF;
2651 IF ((G_LOC_RECINFO.ATTRIBUTE8 <> p_pn_locations_rec.attribute8)
2652 OR ((G_LOC_RECINFO.ATTRIBUTE8 is NULL) AND (p_pn_locations_rec.attribute8 is NOT NULL))
2653 OR ((G_LOC_RECINFO.ATTRIBUTE8 is NOT NULL) AND (p_pn_locations_rec.attribute8 is NULL))) THEN
2654 x_flag := 'Y';
2655 return;
2656 END IF;
2657 IF ((G_LOC_RECINFO.ATTRIBUTE9 <> p_pn_locations_rec.attribute9)
2658 OR ((G_LOC_RECINFO.ATTRIBUTE9 is NULL) AND (p_pn_locations_rec.attribute9 is NOT NULL))
2659 OR ((G_LOC_RECINFO.ATTRIBUTE9 is NOT NULL) AND (p_pn_locations_rec.attribute9 is NULL))) THEN
2660 x_flag := 'Y';
2661 return;
2662 END IF;
2663 IF ((G_LOC_RECINFO.ATTRIBUTE10 <> p_pn_locations_rec.attribute10)
2664 OR ((G_LOC_RECINFO.ATTRIBUTE10 is NULL) AND (p_pn_locations_rec.attribute10 is NOT NULL))
2665 OR ((G_LOC_RECINFO.ATTRIBUTE10 is NOT NULL) AND (p_pn_locations_rec.attribute10 is NULL))) THEN
2666 x_flag := 'Y';
2667 return;
2668 END IF;
2669 IF ((G_LOC_RECINFO.ATTRIBUTE11 <> p_pn_locations_rec.attribute11)
2670 OR ((G_LOC_RECINFO.ATTRIBUTE11 is NULL) AND (p_pn_locations_rec.attribute11 is NOT NULL))
2671 OR ((G_LOC_RECINFO.ATTRIBUTE11 is NOT NULL) AND (p_pn_locations_rec.attribute11 is NULL))) THEN
2672 x_flag := 'Y';
2673 return;
2674 END IF;
2675 IF ((G_LOC_RECINFO.ATTRIBUTE12 <> p_pn_locations_rec.attribute12)
2676 OR ((G_LOC_RECINFO.ATTRIBUTE12 is NULL) AND (p_pn_locations_rec.attribute12 is NOT NULL))
2677 OR ((G_LOC_RECINFO.ATTRIBUTE12 is NOT NULL) AND (p_pn_locations_rec.attribute12 is NULL))) THEN
2678 x_flag := 'Y';
2679 return;
2680 END IF;
2681 IF ((G_LOC_RECINFO.ATTRIBUTE13 <> p_pn_locations_rec.attribute13)
2682 OR ((G_LOC_RECINFO.ATTRIBUTE13 is NULL) AND (p_pn_locations_rec.attribute13 is NOT NULL))
2683 OR ((G_LOC_RECINFO.ATTRIBUTE13 is NOT NULL) AND (p_pn_locations_rec.attribute13 is NULL))) THEN
2684 x_flag := 'Y';
2685 return;
2686 END IF;
2687 IF ((G_LOC_RECINFO.ATTRIBUTE14 <> p_pn_locations_rec.attribute14)
2688 OR ((G_LOC_RECINFO.ATTRIBUTE14 is NULL) AND (p_pn_locations_rec.attribute14 is NOT NULL))
2689 OR ((G_LOC_RECINFO.ATTRIBUTE14 is NOT NULL) AND (p_pn_locations_rec.attribute14 is NULL))) THEN
2690 x_flag := 'Y';
2691 return;
2692 END IF;
2693 IF ((G_LOC_RECINFO.ATTRIBUTE15 <> p_pn_locations_rec.attribute15)
2694 OR ((G_LOC_RECINFO.ATTRIBUTE15 is NULL) AND (p_pn_locations_rec.attribute15 is NOT NULL))
2695 OR ((G_LOC_RECINFO.ATTRIBUTE15 is NOT NULL) AND (p_pn_locations_rec.attribute15 is NULL))) THEN
2696 x_flag := 'Y';
2697 return;
2698 END IF;
2699
2700 IF NOT ((nvl(G_LOC_RECINFO.COMMON_AREA_FLAG,'x') = p_pn_locations_rec.COMMON_AREA_FLAG)
2701 OR ((G_LOC_RECINFO.COMMON_AREA_FLAG is null) AND (p_pn_locations_rec.COMMON_AREA_FLAG is null))) THEN
2702 x_flag := 'Y';
2703 return;
2704 END IF;
2705
2706 IF NOT (nvl(G_LOC_RECINFO.LOCATION_TYPE_LOOKUP_CODE,'x') = p_pn_locations_rec.LOCATION_TYPE_LOOKUP_CODE) THEN
2707 x_flag := 'Y';
2708 return;
2709 END IF;
2710
2711 IF ((G_LOC_RECINFO.FUNCTION_TYPE_LOOKUP_CODE <> p_pn_locations_rec.FUNCTION_TYPE_LOOKUP_CODE)
2712 OR ((G_LOC_RECINFO.FUNCTION_TYPE_LOOKUP_CODE is NULL) AND (p_pn_locations_rec.FUNCTION_TYPE_LOOKUP_CODE is NOT NULL))
2713 OR ((G_LOC_RECINFO.FUNCTION_TYPE_LOOKUP_CODE is NOT NULL) AND (p_pn_locations_rec.FUNCTION_TYPE_LOOKUP_CODE is NULL))) THEN
2714 x_flag := 'Y';
2715 return;
2716 END IF;
2717
2718 IF ((G_LOC_RECINFO.STANDARD_TYPE_LOOKUP_CODE <> p_pn_locations_rec.STANDARD_TYPE_LOOKUP_CODE)
2719 OR ((G_LOC_RECINFO.STANDARD_TYPE_LOOKUP_CODE is NULL) AND (p_pn_locations_rec.STANDARD_TYPE_LOOKUP_CODE is NOT NULL))
2720 OR ((G_LOC_RECINFO.STANDARD_TYPE_LOOKUP_CODE is NOT NULL) AND (p_pn_locations_rec.STANDARD_TYPE_LOOKUP_CODE is NULL))) THEN
2721 x_flag := 'Y';
2722 return;
2723 END IF;
2724
2725 IF ((G_LOC_RECINFO.SPACE_TYPE_LOOKUP_CODE <> p_pn_locations_rec.SPACE_TYPE_LOOKUP_CODE)
2726 OR ((G_LOC_RECINFO.SPACE_TYPE_LOOKUP_CODE is NULL) AND (p_pn_locations_rec.SPACE_TYPE_LOOKUP_CODE is NOT NULL))
2727 OR ((G_LOC_RECINFO.SPACE_TYPE_LOOKUP_CODE is NOT NULL) AND (p_pn_locations_rec.SPACE_TYPE_LOOKUP_CODE is NULL))) THEN
2728 x_flag := 'Y';
2729 return;
2730 END IF;
2731
2732 IF NOT (nvl(G_LOC_RECINFO.LOCATION_CODE,'x') = p_pn_locations_rec.LOCATION_CODE) THEN
2733 x_flag := 'Y';
2734 return;
2735 END IF;
2736
2737 IF NOT ((nvl(G_LOC_RECINFO.BUILDING,'x') = p_pn_locations_rec.BUILDING)
2738 OR ((G_LOC_RECINFO.BUILDING is null) AND (p_pn_locations_rec.BUILDING is null))) THEN
2739 x_flag := 'Y';
2740 return;
2741 END IF;
2742
2743 IF NOT ((nvl(G_LOC_RECINFO.FLOOR,'x') = p_pn_locations_rec.FLOOR)
2744 OR ((G_LOC_RECINFO.FLOOR is null) AND (p_pn_locations_rec.FLOOR is null))) THEN
2745 x_flag := 'Y';
2746 return;
2747 END IF;
2748
2749 IF NOT ((nvl(G_LOC_RECINFO.LOCATION_ALIAS,'x') = p_pn_locations_rec.LOCATION_ALIAS)
2750 OR ((G_LOC_RECINFO.LOCATION_ALIAS is null) AND (p_pn_locations_rec.LOCATION_ALIAS is null))) THEN
2751 x_flag := 'Y';
2752 return;
2753 END IF;
2754
2755 IF NOT ((nvl(G_LOC_RECINFO.PROPERTY_ID,-9.99) = nvl(p_pn_locations_rec.PROPERTY_ID,-9.99))) THEN
2756 x_flag := 'Y';
2757 return;
2758 END IF;
2759
2760 IF NOT ((nvl(G_LOC_RECINFO.PARENT_LOCATION_ID,9.99) = p_pn_locations_rec.PARENT_LOCATION_ID)
2761 OR ((G_LOC_RECINFO.PARENT_LOCATION_ID is null) AND (p_pn_locations_rec.PARENT_LOCATION_ID is null))) THEN
2762 x_flag := 'Y';
2763 return;
2764 END IF;
2765
2766 IF NOT ((nvl(G_LOC_RECINFO.INTERFACE_FLAG,'x') = p_pn_locations_rec.INTERFACE_FLAG)
2767 OR ((G_LOC_RECINFO.INTERFACE_FLAG is null) AND (p_pn_locations_rec.INTERFACE_FLAG is null))) THEN
2768 x_flag := 'Y';
2769 return;
2770 END IF;
2771
2772 IF NOT ((nvl(G_LOC_RECINFO.STATUS,'x') = p_pn_locations_rec.STATUS)
2773 OR ((G_LOC_RECINFO.STATUS is null) AND (p_pn_locations_rec.STATUS is null))) THEN
2774 x_flag := 'Y';
2775 return;
2776 END IF;
2777
2778 IF ((g_loc_recinfo.occupancy_status_code <> p_pn_locations_rec.occupancy_status_code) OR
2779 ((g_loc_recinfo.occupancy_status_code IS NULL) AND (p_pn_locations_rec.occupancy_status_code IS NOT NULL)) OR
2780 ((g_loc_recinfo.occupancy_status_code IS NOT NULL) AND (p_pn_locations_rec.occupancy_status_code IS NULL)))
2781 THEN
2782 x_flag := 'Y';
2783 RETURN;
2784 END IF;
2785
2786 IF ((g_loc_recinfo.assignable_emp <> p_pn_locations_rec.assignable_emp) OR
2787 ((g_loc_recinfo.assignable_emp IS NULL) AND (p_pn_locations_rec.assignable_emp IS NOT NULL)) OR
2788 ((g_loc_recinfo.assignable_emp IS NOT NULL) AND (p_pn_locations_rec.assignable_emp IS NULL)))
2789 THEN
2790 x_flag := 'Y';
2791 RETURN;
2792 END IF;
2793
2794 IF ((g_loc_recinfo.assignable_cc <> p_pn_locations_rec.assignable_cc) OR
2795 ((g_loc_recinfo.assignable_cc IS NULL) AND (p_pn_locations_rec.assignable_cc IS NOT NULL)) OR
2796 ((g_loc_recinfo.assignable_cc IS NOT NULL) AND (p_pn_locations_rec.assignable_cc IS NULL)))
2797 THEN
2798 x_flag := 'Y';
2799 RETURN;
2800 END IF;
2801
2802 IF ((g_loc_recinfo.assignable_cust <> p_pn_locations_rec.assignable_cust) OR
2803 ((g_loc_recinfo.assignable_cust IS NULL) AND (p_pn_locations_rec.assignable_cust IS NOT NULL)) OR
2804 ((g_loc_recinfo.assignable_cust IS NOT NULL) AND (p_pn_locations_rec.assignable_cust IS NULL)))
2805 THEN
2806 x_flag := 'Y';
2807 RETURN;
2808 END IF;
2809
2810
2811 -- DO SPECIAL CHECKS FOR PARTICULAR KINDS
2812
2813 IF (NVL(G_LOC_RECINFO.LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('BUILDING','LAND')) THEN
2814 IF NOT ((nvl(G_LOC_RECINFO.LOCATION_PARK_ID,9.99) = p_pn_locations_rec.LOCATION_PARK_ID)
2815 OR ((G_LOC_RECINFO.LOCATION_PARK_ID is null) AND (p_pn_locations_rec.LOCATION_PARK_ID is null))) THEN
2816 x_flag := 'Y';
2817 return;
2818 END IF;
2819
2820 IF NOT ((nvl(G_LOC_RECINFO.LEASE_OR_OWNED,'x') = p_pn_locations_rec.LEASE_OR_OWNED)
2821 OR ((G_LOC_RECINFO.LEASE_OR_OWNED is null) AND (p_pn_locations_rec.LEASE_OR_OWNED is null))) THEN
2822 x_flag := 'Y';
2823 return;
2824 END IF;
2825
2826 IF ((G_LOC_RECINFO.CLASS <> p_pn_locations_rec.CLASS)
2827 OR ((G_LOC_RECINFO.CLASS is NULL) AND (p_pn_locations_rec.CLASS is NOT NULL))
2828 OR ((G_LOC_RECINFO.CLASS is NOT NULL) AND (p_pn_locations_rec.CLASS is NULL))) THEN
2829 x_flag := 'Y';
2830 return;
2831 END IF;
2832
2833 IF NOT ((nvl(G_LOC_RECINFO.STATUS_TYPE,'x') = p_pn_locations_rec.STATUS_TYPE)
2834 OR ((G_LOC_RECINFO.STATUS_TYPE is null) AND (p_pn_locations_rec.STATUS_TYPE is null))) THEN
2835 x_flag := 'Y';
2836 return;
2837 END IF;
2838
2839 IF NOT ((nvl(G_LOC_RECINFO.OFFICE,'x') = p_pn_locations_rec.OFFICE)
2840 OR ((G_LOC_RECINFO.OFFICE is null) AND (p_pn_locations_rec.OFFICE is null))) THEN
2841 x_flag := 'Y';
2842 return;
2843 END IF;
2844
2845 IF NOT ((nvl(G_LOC_RECINFO.ADDRESS_ID,9.99) = p_pn_locations_rec.ADDRESS_ID)
2846 OR ((G_LOC_RECINFO.ADDRESS_ID is null) AND (p_pn_locations_rec.ADDRESS_ID is null))) THEN
2847 x_flag := 'Y';
2848 return;
2849 END IF;
2850
2851 IF NOT ((nvl(G_LOC_RECINFO.GROSS_AREA,-9.99) = nvl(p_pn_locations_rec.GROSS_AREA,-9.99))) THEN
2852 x_flag := 'Y';
2853 return;
2854 END IF;
2855
2856 IF NOT ((nvl(G_LOC_RECINFO.SUITE,'x') = p_pn_locations_rec.SUITE)
2857 OR ((G_LOC_RECINFO.SUITE is null) AND (p_pn_locations_rec.SUITE is null))) THEN
2858 x_flag := 'Y';
2859 return;
2860 END IF;
2861
2862 IF NOT ((nvl(G_LOC_RECINFO.ALLOCATE_COST_CENTER_CODE,'x') = p_pn_locations_rec.ALLOCATE_COST_CENTER_CODE)
2863 OR ((G_LOC_RECINFO.ALLOCATE_COST_CENTER_CODE is null) AND (p_pn_locations_rec.ALLOCATE_COST_CENTER_CODE is null))) THEN
2864 x_flag := 'Y';
2865 return;
2866 END IF;
2867
2868 IF NOT ((G_LOC_RECINFO.UOM_CODE = p_pn_locations_rec.UOM_CODE)
2869 OR ((G_LOC_RECINFO.UOM_CODE is null) AND (p_pn_locations_rec.UOM_CODE is null))) THEN
2870 x_flag := 'Y';
2871 return;
2872 END IF;
2873
2874 IF NOT ((nvl(G_LOC_RECINFO.DESCRIPTION,'x') = p_pn_locations_rec.DESCRIPTION)
2875 OR ((G_LOC_RECINFO.DESCRIPTION is null) AND (p_pn_locations_rec.DESCRIPTION is null))) THEN
2876 x_flag := 'Y';
2877 return;
2878 END IF;
2879
2880 ELSIF (NVL(G_LOC_RECINFO.LOCATION_TYPE_LOOKUP_CODE, ' ') IN ('OFFICE','SECTION')) THEN
2881
2882 IF NOT ((nvl(G_LOC_RECINFO.OFFICE,'x') = p_pn_locations_rec.OFFICE)
2883 OR ((G_LOC_RECINFO.OFFICE is null) AND (p_pn_locations_rec.OFFICE is null))) THEN
2884 x_flag := 'Y';
2885 return;
2886 END IF;
2887
2888 IF NOT ((nvl(G_LOC_RECINFO.MAX_CAPACITY,-9.99) = nvl(p_pn_locations_rec.MAX_CAPACITY,-9.99))) THEN
2889 x_flag := 'Y';
2890 return;
2891 END IF;
2892
2893 IF NOT ((nvl(G_LOC_RECINFO.OPTIMUM_CAPACITY,-9.99) = nvl(p_pn_locations_rec.OPTIMUM_CAPACITY,-9.99))) THEN
2894 x_flag := 'Y';
2895 return;
2896 END IF;
2897
2898 IF NOT ((nvl(G_LOC_RECINFO.RENTABLE_AREA,-9.99) = nvl(p_pn_locations_rec.RENTABLE_AREA,-9.99))) THEN
2899 x_flag := 'Y';
2900 return;
2901 END IF;
2902
2903 IF NOT ((nvl(G_LOC_RECINFO.USABLE_AREA,-9.99) = nvl(p_pn_locations_rec.USABLE_AREA,-9.99))) THEN
2904 x_flag := 'Y';
2905 return;
2906 END IF;
2907
2908 IF NOT ((nvl(G_LOC_RECINFO.ASSIGNABLE_AREA,9.99) = p_pn_locations_rec.ASSIGNABLE_AREA)
2909 OR ((G_LOC_RECINFO.ASSIGNABLE_AREA is null) AND (p_pn_locations_rec.ASSIGNABLE_AREA is null))) THEN
2910 x_flag := 'Y';
2911 return;
2912 END IF;
2913
2914 IF NOT ((nvl(G_LOC_RECINFO.COMMON_AREA,9.99) = p_pn_locations_rec.COMMON_AREA)
2915 OR ((G_LOC_RECINFO.COMMON_AREA is null) AND (p_pn_locations_rec.COMMON_AREA is null))) THEN
2916 x_flag := 'Y';
2917 return;
2918 END IF;
2919
2920 IF ((G_LOC_RECINFO.SUITE <> p_pn_locations_rec.SUITE)
2921 OR ((G_LOC_RECINFO.SUITE is NULL) AND (p_pn_locations_rec.SUITE is NOT NULL))
2922 OR ((G_LOC_RECINFO.SUITE is NOT NULL) AND (p_pn_locations_rec.SUITE is NULL))) THEN
2923 x_flag := 'Y';
2924 return;
2925 END IF;
2926
2927 IF NOT ((nvl(G_LOC_RECINFO.BOOKABLE_FLAG,'x') = p_pn_locations_rec.BOOKABLE_FLAG)
2928 OR ((G_LOC_RECINFO.BOOKABLE_FLAG is null) AND (p_pn_locations_rec.BOOKABLE_FLAG is null))) THEN
2929 x_flag := 'Y';
2930 return;
2931 END IF;
2932
2933 END IF;
2934
2935 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_for_popup (-) Flag: '||x_flag||', ReturnStatus: '||x_return_status);
2936
2937 END check_for_popup;
2938
2939 -------------------------------------------------------------------------------
2940 -- PROCDURE : Update_Status
2941 -- INVOKED FROM :
2942 -- PURPOSE : Updates the status.
2943 -- HISTORY :
2944 -- xx-xxx-xx o Fix for bug 707274
2945 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION_ with _ALL table.
2946 -------------------------------------------------------------------------------
2947 PROCEDURE Update_Status (p_Location_Id Number) IS
2948
2949 BEGIN
2950
2951 UPDATE pn_locations_all
2952 SET Status = 'I'
2953 WHERE Location_Id IN (SELECT Location_Id
2954 FROM pn_locations_all
2955 START WITH Location_Id = p_Location_Id
2956 CONNECT BY PRIOR Location_Id = Parent_Location_id);
2957
2958 End Update_Status ;
2959
2960 ---------------------------------------------------------------------------------------
2961 -- Procedure update_assignments ( Fix for bug 2722698 )
2962 ---------------------------------------------------------------------------------------
2963 PROCEDURE update_assignments (
2964 p_location_id IN NUMBER
2965 ,p_active_start_date IN DATE
2966 ,p_active_end_date IN DATE
2967 ,p_active_start_date_old IN DATE
2968 ,p_active_end_date_old IN DATE
2969 ,x_return_status OUT NOCOPY VARCHAR2
2970 ,x_return_message OUT NOCOPY VARCHAR2
2971 )
2972 IS
2973
2974 CURSOR location_cursor IS
2975 SELECT *
2976 FROM pn_locations_all
2977 START WITH location_id = p_location_id
2978 CONNECT BY PRIOR location_id = parent_location_id;
2979
2980 BEGIN
2981
2982 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_assignments (+)');
2983 pnp_debug_pkg.debug(' UpdAsgn=> In Parameters :: p_location_id: '||p_location_id);
2984 pnp_debug_pkg.debug(' UpdAsgn=> p_active_start_date : '||TO_CHAR(p_active_start_date, 'MM/DD/YYYY')
2985 ||', p_active_end_date : '||TO_CHAR(p_active_end_date, 'MM/DD/YYYY'));
2986 pnp_debug_pkg.debug(' UpdAsgn=> p_active_start_date_old: '||TO_CHAR(p_active_start_date_old, 'MM/DD/YYYY')
2987 ||', p_active_end_date_old: '||TO_CHAR(p_active_end_date_old, 'MM/DD/YYYY'));
2988
2989 FOR l_location_rec in location_cursor LOOP
2990
2991 pnp_debug_pkg.debug(' UpdAsgn> Row#: '||location_cursor%ROWCOUNT||', Inside FOR loop LocId: '||l_location_rec.location_id||', LocCd: '||l_location_rec.location_code);
2992
2993 IF l_location_rec.active_end_date > p_active_end_date
2994 and p_active_end_date <> p_active_end_date_old THEN
2995
2996 pnp_debug_pkg.debug(' UpdAsgn> Inside end date updation');
2997
2998 DELETE FROM pn_space_assign_emp_all
2999 WHERE location_id = l_location_rec.location_id
3000 AND emp_assign_start_date > p_active_end_date
3001 AND allocated_area = 0;
3002
3003 UPDATE pn_space_assign_emp_all
3004 SET emp_assign_end_date = p_active_end_date
3005 WHERE location_id = l_location_rec.location_id
3006 AND emp_assign_end_date > p_active_end_date;
3007
3008 DELETE FROM pn_space_assign_cust_all
3009 WHERE location_id = l_location_rec.location_id
3010 AND cust_assign_start_date > p_active_end_date
3011 AND allocated_area = 0;
3012
3013 UPDATE pn_space_assign_cust_all
3014 SET cust_assign_end_date = p_active_end_date
3015 WHERE location_id = l_location_rec.location_id
3016 AND cust_assign_end_date > p_active_end_date;
3017
3018 END IF;
3019
3020 IF l_location_rec.active_start_date < p_active_start_date
3021 and p_active_start_date <> p_active_start_date_old THEN
3022
3023 pnp_debug_pkg.debug(' UpdAsgn> Inside start date updation');
3024
3025 DELETE FROM pn_space_assign_emp_all
3026 WHERE location_id = l_location_rec.location_id
3027 AND emp_assign_end_date < p_active_start_date
3028 AND allocated_area = 0;
3029
3030 UPDATE pn_space_assign_emp_all
3031 SET emp_assign_start_date = p_active_start_date
3032 WHERE location_id = l_location_rec.location_id
3033 AND emp_assign_start_date < p_active_start_date;
3034
3035 DELETE FROM pn_space_assign_cust_all
3036 WHERE location_id = l_location_rec.location_id
3037 AND cust_assign_end_date < p_active_start_date
3038 AND allocated_area = 0;
3039
3040 UPDATE pn_space_assign_cust_all
3041 SET cust_assign_start_date = p_active_start_date
3042 WHERE location_id = l_location_rec.location_id
3043 AND cust_assign_start_date < p_active_start_date;
3044
3045 END IF;
3046
3047 END LOOP;
3048
3049 x_return_status := FND_API.G_RET_STS_SUCCESS;
3050 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.update_assignments (-) ReturnStatus: '||x_return_status);
3051
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 x_return_status := FND_API.G_RET_STS_ERROR;
3055 fnd_message.set_name ('PN','PN_OTHERS_EXCEPTION');
3056 fnd_message.set_token('ERR_MSG',sqlerrm);
3057 pnp_debug_pkg.log('Other error update_assignments' || sqlerrm);
3058
3059 END update_assignments;
3060
3061 -----------------------------------------------------------------------
3062 -- FUNCTION : validate_gross_area
3063 -- PURPOSE : The function validates that sum off rentable area for all
3064 -- offices/sections under building/land must be less than the
3065 -- gross area of building/land.
3066 -- IN PARAM : Location Id, Rentable Area, Location Type Lookup Code,
3067 -- Active Start Date, Active End Date.
3068 -- History :
3069 -- 22-JAN-2003 Mrinal Misra o Created.
3070 -- 28-JAN-2003 Kiran o Modified
3071 -- o Changed i/p param p_rent_area to p_area
3072 -- o Changed the function to validate for both
3073 -- 'OFFICE' and 'BUILDING'.
3074 -- 28-JAN-2003 Mrinal Misra o Added p_change_mode parameter and related
3075 -- logic in function.
3076 -- 14-DEC-2004 Vikas Mehta o Changes to call function Get_Max_Rent_Area
3077 -- 18-JAN-2005 Satish Tripathi o Modified for debug messages. Set l_return appropriately
3078 -- and log it in debug before RETURN.
3079 -----------------------------------------------------------------------
3080 FUNCTION validate_gross_area(p_loc_id IN NUMBER,
3081 p_area IN NUMBER,
3082 p_lkp_code IN VARCHAR2,
3083 p_act_str_dt IN DATE,
3084 p_act_end_dt IN DATE,
3085 p_change_mode IN VARCHAR2
3086 )
3087 RETURN BOOLEAN IS
3088
3089 l_min_gross_area NUMBER;
3090 l_loc_id NUMBER;
3091 l_sum_rent_area NUMBER;
3092 l_old_rent_area NUMBER;
3093 l_act_end_dt DATE := NVL(p_act_end_dt, pnt_locations_pkg.g_end_of_time);
3094 l_off_lkp_code VARCHAR2(30);
3095 l_return VARCHAR2(30);
3096
3097 CURSOR get_min_gross_area IS
3098 SELECT MIN(gross_area) min_gross_area,
3099 location_id location_id
3100 FROM pn_locations_all
3101 WHERE parent_location_id IS NULL
3102 AND active_start_date <= l_act_end_dt
3103 AND active_end_date >= p_act_str_dt
3104 START WITH location_id = p_loc_id
3105 CONNECT BY PRIOR parent_location_id = location_id
3106 GROUP BY location_id;
3107
3108 CURSOR get_old_rent_area IS
3109 SELECT rentable_area
3110 FROM pn_locations_all
3111 WHERE location_id = p_loc_id
3112 AND location_type_lookup_code = p_lkp_code
3113 AND active_start_date <= NVL(p_act_end_dt, TO_DATE('12/31/4712','MM/DD/YYYY'))
3114 AND active_end_date >= p_act_str_dt;
3115 BEGIN
3116
3117 pnp_debug_pkg.debug(' PntLocnPkg.Validate_Gross_Area (+) LocId: '||p_loc_id||', Area: '||p_area
3118 ||', ActStrDt: '||TO_CHAR(p_act_str_dt, 'MM/DD/YYYY')
3119 ||', ActEndDt: '||TO_CHAR(p_act_end_dt, 'MM/DD/YYYY'));
3120 IF p_lkp_code in ('OFFICE', 'SECTION') THEN
3121
3122 OPEN get_min_gross_area;
3123 FETCH get_min_gross_area INTO l_min_gross_area, l_loc_id;
3124 CLOSE get_min_gross_area;
3125
3126 IF l_min_gross_area is null THEN
3127 l_return := 'Y';
3128 ELSE
3129 l_sum_rent_area := Get_Max_Rent_Area(l_loc_id, p_lkp_code, p_act_str_dt, l_act_end_dt);
3130
3131 IF p_change_mode = 'UPDATE' THEN
3132 OPEN get_old_rent_area;
3133 FETCH get_old_rent_area INTO l_old_rent_area;
3134 CLOSE get_old_rent_area;
3135
3136 l_sum_rent_area := l_sum_rent_area - l_old_rent_area;
3137 END IF;
3138
3139 IF NVL(l_min_gross_area,0) < NVL(l_sum_rent_area, 0) + NVL(p_area,0) THEN
3140 l_return := 'N';
3141 ELSE
3142 l_return := 'Y';
3143 END IF;
3144 END IF;
3145
3146 ELSIF p_lkp_code in ('BUILDING', 'LAND') THEN
3147
3148 IF p_lkp_code = 'BUILDING' THEN
3149 l_off_lkp_code := 'OFFICE';
3150 ELSIF p_lkp_code = 'LAND' THEN
3151 l_off_lkp_code := 'SECTION';
3152 END IF;
3153
3154 IF p_area IS NULL THEN
3155 l_return := 'Y';
3156 ELSE
3157
3158 l_sum_rent_area := Get_Max_Rent_Area(l_loc_id, l_off_lkp_code, p_act_str_dt, l_act_end_dt);
3159
3160 IF NVL(p_area,0) < NVL(l_sum_rent_area, 0) THEN
3161 l_return := 'N';
3162 ELSE
3163 l_return := 'Y';
3164 END IF;
3165 END IF;
3166
3167 END IF;
3168
3169 pnp_debug_pkg.debug(' PntLocnPkg.Validate_Gross_Area (+) LocId: '||p_loc_id||', Return: '||l_return);
3170
3171 IF l_return = 'Y' THEN
3172 RETURN TRUE;
3173 ELSE
3174 RETURN FALSE;
3175 END IF;
3176
3177 END validate_gross_area;
3178
3179 -------------------------------------------------------------------------------
3180 -- PROCEDURE : check_location_gaps
3181 -- PURPOSE : This procedure is being called from INSERT_ROW, UPDATE_ROW
3182 -- of employee_fdr_blk, customer_fdr_blk of PNTSPACE form.
3183 -- It checks for the gaps between office definition and stops
3184 -- the user from assinging an office during that gap interval.
3185 -- IN PARAM : Location Id, Actice_start_date, Active_end_date.
3186 -- History :
3187 -- 27-DEC-02 MMisra o Mrinal Misra
3188 -- 10-JAN-03 MMisra o Modified to run FOR LOOP one lesser
3189 -- count by 1..loctn_tab.count-1.
3190 -- 05-JUN-03 STripathi o Added parameter p_err_msg for Recovery (CAM)
3191 -- impact on Leases.
3192 -- 01-DEC-04 STripathi o Modified for Portfolio Status Enh BUG# 4030816.
3193 -- Take occupancy status, assignable_emp, assignable_cc and
3194 -- assignable_cust into account depending on new parameter
3195 -- Assignmane Mode (p_asgn_mode).
3196 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION with _ALL table.
3197 -------------------------------------------------------------------------------
3198 PROCEDURE Check_Location_Gaps (
3199 p_loc_id IN NUMBER
3200 ,p_str_dt IN DATE
3201 ,p_end_dt IN DATE
3202 ,p_asgn_mode IN VARCHAR2
3203 ,p_err_msg OUT NOCOPY VARCHAR2
3204 )
3205 IS
3206
3207 TYPE loctn_rec IS RECORD(
3208 location_id pn_locations.location_id%TYPE,
3209 active_start_date DATE,
3210 active_end_date DATE);
3211
3212 TYPE loc_type IS
3213 TABLE OF loctn_rec
3214 INDEX BY BINARY_INTEGER;
3215
3216 loctn_tab loc_type;
3217 l_rec_num NUMBER;
3218 l_diff NUMBER;
3219 l_date DATE;
3220 l_err_flag VARCHAR2(1);
3221 l_err_msg VARCHAR2(1) := NULL;
3222
3223 CURSOR get_loctn_dates IS
3224 SELECT location_id,
3225 active_start_date,
3226 active_end_date
3227 FROM pn_locations_all
3228 WHERE active_end_date >= p_str_dt
3229 AND active_start_date <= p_end_dt
3230 AND location_id = p_loc_id
3231 AND ((p_asgn_mode = 'NONE') OR
3232 (p_asgn_mode = 'EMP' AND NVL(assignable_emp, 'Y') = 'Y' AND NVL(assignable_cc, 'Y') = 'Y') OR
3233 (p_asgn_mode = 'CC' AND NVL(assignable_cc, 'Y') = 'Y') OR
3234 (p_asgn_mode = 'CUST' AND NVL(assignable_cust, 'Y') = 'Y')
3235 )
3236 ORDER BY active_start_date;
3237
3238 CURSOR check_loctn_gap(l_date IN DATE) IS
3239 SELECT 'Y'
3240 FROM DUAL
3241 WHERE NOT EXISTS (SELECT NULL
3242 FROM pn_locations_all
3243 WHERE l_date BETWEEN active_start_date AND active_end_date
3244 AND location_id = p_loc_id
3245 AND ((p_asgn_mode = 'NONE') OR
3246 (p_asgn_mode = 'EMP' AND NVL(assignable_emp, 'Y') = 'Y' AND NVL(assignable_cc, 'Y') = 'Y') OR
3247 (p_asgn_mode = 'CC' AND NVL(assignable_cc, 'Y') = 'Y') OR
3248 (p_asgn_mode = 'CUST' AND NVL(assignable_cust, 'Y') = 'Y')
3249 ));
3250
3251 BEGIN
3252
3253 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Check_Location_Gaps(+) LocId: '||p_loc_id||', Mode: '||p_asgn_mode
3254 ||', StrDt: '||TO_CHAR(p_str_dt, 'MM/DD/YYYY')||', EndDt: '||TO_CHAR(p_end_dt, 'MM/DD/YYYY'));
3255
3256 loctn_tab.delete;
3257 l_rec_num := 0;
3258 l_err_flag := 'N';
3259
3260 IF p_str_dt IS NOT NULL THEN
3261 OPEN check_loctn_gap(p_str_dt);
3262 FETCH check_loctn_gap INTO l_err_flag;
3263 CLOSE check_loctn_gap;
3264
3265 IF l_err_flag = 'Y' THEN --Invalid Locn
3266 l_err_msg := 'I';
3267 END IF;
3268 END IF;
3269
3270 IF p_end_dt IS NOT NULL AND
3271 l_err_flag = 'N'
3272 THEN
3273 OPEN check_loctn_gap(p_end_dt);
3274 FETCH check_loctn_gap INTO l_err_flag;
3275 CLOSE check_loctn_gap;
3276
3277 IF l_err_flag = 'Y' THEN --Invalid Locn
3278 l_err_msg := 'I';
3279 END IF;
3280 END IF;
3281
3282 IF p_end_dt IS NOT NULL AND p_str_dt IS NOT NULL AND
3283 l_err_flag = 'N'
3284 THEN
3285 FOR loc_rec IN get_loctn_dates LOOP
3286 l_rec_num := NVL(loctn_tab.count,0) + 1;
3287 loctn_tab(l_rec_num) := loc_rec;
3288 END LOOP;
3289
3290 IF NVL(l_rec_num,0) > 1 THEN
3291 FOR i in 1..loctn_tab.count-1 LOOP
3292
3293 l_diff := loctn_tab(i+1).active_start_date - loctn_tab(i).active_end_date;
3294
3295 IF l_diff > 1 THEN
3296 l_err_flag := 'Y';
3297 EXIT;
3298 END IF;
3299 END LOOP;
3300
3301 ELSIF NVL(l_rec_num,0) = 0 THEN
3302 l_err_flag := 'Y';
3303 END IF;
3304
3305 IF l_err_flag = 'Y' THEN --Gap Exists
3306 l_err_msg := 'G';
3307 END IF;
3308 END IF;
3309
3310 p_err_msg := l_err_msg;
3311
3312 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Check_Location_Gaps(-) ErrMsg: '||p_err_msg);
3313
3314 END Check_Location_Gaps;
3315
3316
3317 -----------------------------------------------------------------------
3318 -- PROCEDURE : Get_Location_Span
3319 -- PURPOSE : This procedure returns Min start date and Max end date
3320 -- of a location taking occupancy status and assignable group
3321 -- into account depending on Assignmane Mode (p_asgn_mode).
3322 -- IN PARAM :
3323 -- History :
3324 -- 01-DEC-2004 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
3325 -----------------------------------------------------------------------
3326 PROCEDURE Get_Location_Span (
3327 p_loc_id IN NUMBER
3328 ,p_asgn_mode IN VARCHAR2
3329 ,p_min_str_dt OUT NOCOPY DATE
3330 ,p_max_end_dt OUT NOCOPY DATE
3331 )
3332 IS
3333
3334 CURSOR get_loctn_span IS
3335 SELECT MIN(active_start_date)
3336 ,MAX(active_end_date)
3337 FROM pn_locations_all
3338 WHERE location_id = p_loc_id
3339 AND ((p_asgn_mode = 'NONE') OR
3340 (p_asgn_mode = 'EMP' AND NVL(assignable_emp, 'Y') = 'Y' AND NVL(assignable_cc, 'Y') = 'Y') OR
3341 (p_asgn_mode = 'CC' AND NVL(assignable_cc, 'Y') = 'Y') OR
3342 (p_asgn_mode = 'CUST' AND NVL(assignable_cust, 'Y') = 'Y'));
3343
3344 BEGIN
3345
3346 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Get_Location_Span(+) LocId: '||p_loc_id||', Mode: '||p_asgn_mode);
3347
3348 OPEN get_loctn_span;
3349 FETCH get_loctn_span INTO p_min_str_dt, p_max_end_dt;
3350 CLOSE get_loctn_span;
3351
3352 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Get_Location_Span(-) MinStrDt: '
3353 ||TO_CHAR(p_min_str_dt, 'MM/DD/YYYY')||', MaxEndDt: '||TO_CHAR(p_max_end_dt, 'MM/DD/YYYY'));
3354
3355 END Get_Location_Span;
3356
3357
3358 -----------------------------------------------------------------------
3359 -- PROCEDURE : Update_Locn_Row
3360 -- PURPOSE : This procedure calls pnt_locations_pkg.Update_Row for the
3361 -- pn_locations_all%ROWTYPE passed to it. Called from
3362 -- Correct_Update_Row and Cascade_Child_Locn
3363 -- IN PARAM :
3364 -- History :
3365 -- 01-DEC-2004 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
3366 -- 02-AUG-2005 Satya Deep o Added X_SOURCE in the call to
3367 -- pnt_locations_pkg.Update_Row
3368 -----------------------------------------------------------------------
3369 PROCEDURE Update_Locn_Row (
3370 p_loc_recinfo IN pn_locations_all%ROWTYPE
3371 ,p_adr_recinfo IN pn_addresses_all%ROWTYPE
3372 ,p_assgn_area_chgd_flag IN VARCHAR2
3373 ,x_return_status IN OUT NOCOPY VARCHAR2
3374 ,x_return_message IN OUT NOCOPY VARCHAR2
3375 )
3376 IS
3377 l_rowid ROWID;
3378 l_location_id pn_locations_all.location_id%TYPE;
3379 l_address_id pn_locations_all.address_id%TYPE;
3380 BEGIN
3381
3382 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Update_Locn_Row (+) LocId: '||p_loc_recinfo.location_id
3383 ||', LocCd: '||p_loc_recinfo.location_code
3384 ||', Type: '||p_loc_recinfo.location_type_lookup_code);
3385 l_location_id := p_loc_recinfo.location_id;
3386 l_address_id := p_loc_recinfo.address_id;
3387
3388 IF g_pn_locations_rowid IS NULL THEN
3389 pnt_locations_pkg.Set_ROWID(
3390 p_location_id => p_loc_recinfo.location_id
3391 ,p_active_start_date => p_loc_recinfo.active_start_date
3392 ,p_active_end_date => p_loc_recinfo.active_end_date
3393 ,x_return_status => x_return_status
3394 ,x_return_message => x_return_message
3395 );
3396 END IF;
3397
3398 IF NVL(x_return_status, 'S') = 'S' THEN
3399
3400 pnt_locations_pkg.Update_Row
3401 (
3402 x_location_id => l_location_id
3403 ,x_last_update_date => SYSDATE
3404 ,x_last_updated_by => fnd_global.user_id
3405 ,x_last_update_login => fnd_global.login_id
3406 ,x_location_park_id => p_loc_recinfo.location_park_id
3407 ,x_location_type_lookup_code => p_loc_recinfo.location_type_lookup_code
3408 ,x_space_type_lookup_code => p_loc_recinfo.space_type_lookup_code
3409 ,x_function_type_lookup_code => p_loc_recinfo.function_type_lookup_code
3410 ,x_standard_type_lookup_code => p_loc_recinfo.standard_type_lookup_code
3411 ,x_building => p_loc_recinfo.building
3412 ,x_lease_or_owned => p_loc_recinfo.lease_or_owned
3413 ,x_class => p_loc_recinfo.class
3414 ,x_status_type => p_loc_recinfo.status_type
3415 ,x_floor => p_loc_recinfo.floor
3416 ,x_office => p_loc_recinfo.office
3417 ,x_max_capacity => p_loc_recinfo.max_capacity
3418 ,x_optimum_capacity => p_loc_recinfo.optimum_capacity
3419 ,x_gross_area => p_loc_recinfo.gross_area
3420 ,x_rentable_area => p_loc_recinfo.rentable_area
3421 ,x_usable_area => p_loc_recinfo.usable_area
3422 ,x_assignable_area => p_loc_recinfo.assignable_area
3423 ,x_common_area => p_loc_recinfo.common_area
3424 ,x_suite => p_loc_recinfo.suite
3425 ,x_allocate_cost_center_code => p_loc_recinfo.allocate_cost_center_code
3426 ,x_uom_code => p_loc_recinfo.uom_code
3427 ,x_description => p_loc_recinfo.description
3428 ,x_parent_location_id => p_loc_recinfo.parent_location_id
3429 ,x_interface_flag => p_loc_recinfo.interface_flag
3430 ,x_status => p_loc_recinfo.status
3431 ,x_property_id => p_loc_recinfo.property_id
3432 ,x_common_area_flag => p_loc_recinfo.common_area_flag
3433 ,x_active_start_date => p_loc_recinfo.active_start_date
3434 ,x_active_end_date => p_loc_recinfo.active_end_date
3435 ,x_bookable_flag => p_loc_recinfo.bookable_flag
3436 ,x_occupancy_status_code => p_loc_recinfo.occupancy_status_code
3437 ,x_assignable_emp => p_loc_recinfo.assignable_emp
3438 ,x_assignable_cc => p_loc_recinfo.assignable_cc
3439 ,x_assignable_cust => p_loc_recinfo.assignable_cust
3440 ,x_disposition_code => p_loc_recinfo.disposition_code
3441 ,x_acc_treatment_code => p_loc_recinfo.acc_treatment_code
3442 ,x_attribute_category => p_loc_recinfo.attribute_category
3443 ,x_attribute1 => p_loc_recinfo.attribute1
3444 ,x_attribute2 => p_loc_recinfo.attribute2
3445 ,x_attribute3 => p_loc_recinfo.attribute3
3446 ,x_attribute4 => p_loc_recinfo.attribute4
3447 ,x_attribute5 => p_loc_recinfo.attribute5
3448 ,x_attribute6 => p_loc_recinfo.attribute6
3449 ,x_attribute7 => p_loc_recinfo.attribute7
3450 ,x_attribute8 => p_loc_recinfo.attribute8
3451 ,x_attribute9 => p_loc_recinfo.attribute9
3452 ,x_attribute10 => p_loc_recinfo.attribute10
3453 ,x_attribute11 => p_loc_recinfo.attribute11
3454 ,x_attribute12 => p_loc_recinfo.attribute12
3455 ,x_attribute13 => p_loc_recinfo.attribute13
3456 ,x_attribute14 => p_loc_recinfo.attribute14
3457 ,x_attribute15 => p_loc_recinfo.attribute15
3458 ,x_address_id => l_address_id
3459 ,x_addr_last_update_date => SYSDATE
3460 ,x_addr_last_updated_by => fnd_globaL.user_id
3461 ,x_addr_last_update_login => fnd_global.login_id
3462 ,x_address_line1 => p_adr_recinfo.address_line1
3463 ,x_address_line2 => p_adr_recinfo.address_line2
3464 ,x_address_line3 => p_adr_recinfo.address_line3
3465 ,x_address_line4 => p_adr_recinfo.address_line4
3466 ,x_county => p_adr_recinfo.county
3467 ,x_city => p_adr_recinfo.city
3468 ,x_state => p_adr_recinfo.state
3469 ,x_province => p_adr_recinfo.province
3470 ,x_zip_code => p_adr_recinfo.zip_code
3471 ,x_country => p_adr_recinfo.country
3472 ,x_territory_id => p_adr_recinfo.territory_id
3473 ,x_addr_attribute_category => p_adr_recinfo.addr_attribute_category
3474 ,x_addr_attribute1 => p_adr_recinfo.addr_attribute1
3475 ,x_addr_attribute2 => p_adr_recinfo.addr_attribute2
3476 ,x_addr_attribute3 => p_adr_recinfo.addr_attribute3
3477 ,x_addr_attribute4 => p_adr_recinfo.addr_attribute4
3478 ,x_addr_attribute5 => p_adr_recinfo.addr_attribute5
3479 ,x_addr_attribute6 => p_adr_recinfo.addr_attribute6
3480 ,x_addr_attribute7 => p_adr_recinfo.addr_attribute7
3481 ,x_addr_attribute8 => p_adr_recinfo.addr_attribute8
3482 ,x_addr_attribute9 => p_adr_recinfo.addr_attribute9
3483 ,x_addr_attribute10 => p_adr_recinfo.addr_attribute10
3484 ,x_addr_attribute11 => p_adr_recinfo.addr_attribute11
3485 ,x_addr_attribute12 => p_adr_recinfo.addr_attribute12
3486 ,x_addr_attribute13 => p_adr_recinfo.addr_attribute13
3487 ,x_addr_attribute14 => p_adr_recinfo.addr_attribute14
3488 ,x_addr_attribute15 => p_adr_recinfo.addr_attribute15
3489 ,x_assgn_area_chgd_flag => p_assgn_area_chgd_flag
3490 ,x_return_status => x_return_status
3491 ,x_return_message => x_return_message
3492 ,x_source => p_loc_recinfo.source
3493 );
3494
3495 END IF;
3496
3497 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Update_Locn_Row (-) ReturnStatus: '||x_return_status);
3498 END Update_Locn_Row;
3499
3500
3501 -----------------------------------------------------------------------
3502 -- PROCEDURE : Insert_Locn_Row
3503 -- PURPOSE : This procedure calls pnt_locations_pkg.Insert_Row for the
3504 -- pn_locations_all%ROWTYPE passed to it. Called from
3505 -- Correct_Update_Row and Cascade_Child_Locn
3506 -- IN PARAM :
3507 -- History :
3508 -- 01-DEC-2004 Satish Tripathi o Created for Portfolio Status Enh BUG# 4030816.
3509 -- 02-AUG-2005 Satya Deep o Added X_SOURCE in the call to
3510 -- pnt_locations_pkg.Insert_Row
3511 -----------------------------------------------------------------------
3512 PROCEDURE Insert_Locn_Row (
3513 p_loc_recinfo IN pn_locations_all%ROWTYPE
3514 ,p_adr_recinfo IN pn_addresses_all%ROWTYPE
3515 ,p_change_mode IN VARCHAR2
3516 ,x_return_status IN OUT NOCOPY VARCHAR2
3517 ,x_return_message IN OUT NOCOPY VARCHAR2
3518 )
3519 IS
3520 l_rowid ROWID;
3521 l_location_id pn_locations_all.location_id%TYPE;
3522 l_address_id pn_locations_all.address_id%TYPE;
3523 BEGIN
3524 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Insert_Locn_Row (+) LocId: '||p_loc_recinfo.location_id
3525 ||', LocCd: '||p_loc_recinfo.location_code
3526 ||', Type: '||p_loc_recinfo.location_type_lookup_code);
3527
3528 l_location_id := p_loc_recinfo.location_id;
3529 l_address_id := p_loc_recinfo.address_id;
3530
3531 pnt_locations_pkg.Insert_Row
3532 (
3533 x_rowid => l_rowid
3534 ,x_last_update_date => SYSDATE
3535 ,x_last_updated_by => fnd_global.user_id
3536 ,x_creation_date => SYSDATE
3537 ,x_created_by => fnd_global.user_id
3538 ,x_last_update_login => fnd_global.login_id
3539 ,x_location_id => l_location_id
3540 ,x_org_id => p_loc_recinfo.org_id
3541 ,x_location_park_id => p_loc_recinfo.location_park_id
3542 ,x_location_type_lookup_code => p_loc_recinfo.location_type_lookup_code
3543 ,x_space_type_lookup_code => p_loc_recinfo.space_type_lookup_code
3544 ,x_function_type_lookup_code => p_loc_recinfo.function_type_lookup_code
3545 ,x_standard_type_lookup_code => p_loc_recinfo.standard_type_lookup_code
3546 ,x_location_alias => p_loc_recinfo.location_alias
3547 ,x_location_code => p_loc_recinfo.location_code
3548 ,x_building => p_loc_recinfo.building
3549 ,x_lease_or_owned => p_loc_recinfo.lease_or_owned
3550 ,x_class => p_loc_recinfo.class
3551 ,x_status_type => p_loc_recinfo.status_type
3552 ,x_floor => p_loc_recinfo.floor
3553 ,x_office => p_loc_recinfo.office
3554 ,x_max_capacity => p_loc_recinfo.max_capacity
3555 ,x_optimum_capacity => p_loc_recinfo.optimum_capacity
3556 ,x_gross_area => p_loc_recinfo.gross_area
3557 ,x_rentable_area => p_loc_recinfo.rentable_area
3558 ,x_usable_area => p_loc_recinfo.usable_area
3559 ,x_assignable_area => p_loc_recinfo.assignable_area
3560 ,x_common_area => p_loc_recinfo.common_area
3561 ,x_suite => p_loc_recinfo.suite
3562 ,x_allocate_cost_center_code => p_loc_recinfo.allocate_cost_center_code
3563 ,x_uom_code => p_loc_recinfo.uom_code
3564 ,x_description => p_loc_recinfo.description
3565 ,x_parent_location_id => p_loc_recinfo.parent_location_id
3566 ,x_interface_flag => p_loc_recinfo.interface_flag
3567 ,x_request_id => p_loc_recinfo.request_id
3568 ,x_program_id => p_loc_recinfo.program_id
3569 ,x_program_application_id => p_loc_recinfo.program_application_id
3570 ,x_program_update_date => p_loc_recinfo.program_update_date
3571 ,x_status => p_loc_recinfo.status
3572 ,x_property_id => p_loc_recinfo.property_id
3573 ,x_common_area_flag => p_loc_recinfo.common_area_flag
3574 ,x_active_start_date => p_loc_recinfo.active_start_date
3575 ,x_active_end_date => p_loc_recinfo.active_end_date
3576 ,x_bookable_flag => p_loc_recinfo.bookable_flag
3577 ,x_occupancy_status_code => p_loc_recinfo.occupancy_status_code
3578 ,x_assignable_emp => p_loc_recinfo.assignable_emp
3579 ,x_assignable_cc => p_loc_recinfo.assignable_cc
3580 ,x_assignable_cust => p_loc_recinfo.assignable_cust
3581 ,x_disposition_code => p_loc_recinfo.disposition_code
3582 ,x_acc_treatment_code => p_loc_recinfo.acc_treatment_code
3583 ,x_attribute_category => p_loc_recinfo.attribute_category
3584 ,x_attribute1 => p_loc_recinfo.attribute1
3585 ,x_attribute2 => p_loc_recinfo.attribute2
3586 ,x_attribute3 => p_loc_recinfo.attribute3
3587 ,x_attribute4 => p_loc_recinfo.attribute4
3588 ,x_attribute5 => p_loc_recinfo.attribute5
3589 ,x_attribute6 => p_loc_recinfo.attribute6
3590 ,x_attribute7 => p_loc_recinfo.attribute7
3591 ,x_attribute8 => p_loc_recinfo.attribute8
3592 ,x_attribute9 => p_loc_recinfo.attribute9
3593 ,x_attribute10 => p_loc_recinfo.attribute10
3594 ,x_attribute11 => p_loc_recinfo.attribute11
3595 ,x_attribute12 => p_loc_recinfo.attribute12
3596 ,x_attribute13 => p_loc_recinfo.attribute13
3597 ,x_attribute14 => p_loc_recinfo.attribute14
3598 ,x_attribute15 => p_loc_recinfo.attribute15
3599 ,x_address_id => l_address_id
3600 ,x_address_line1 => p_adr_recinfo.address_line1
3601 ,x_address_line2 => p_adr_recinfo.address_line2
3602 ,x_address_line3 => p_adr_recinfo.address_line3
3603 ,x_address_line4 => p_adr_recinfo.address_line4
3604 ,x_county => p_adr_recinfo.county
3605 ,x_city => p_adr_recinfo.city
3606 ,x_state => p_adr_recinfo.state
3607 ,x_province => p_adr_recinfo.province
3608 ,x_zip_code => p_adr_recinfo.zip_code
3609 ,x_country => p_adr_recinfo.country
3610 ,x_territory_id => p_adr_recinfo.territory_id
3611 ,x_addr_last_update_date => SYSDATE
3612 ,x_addr_last_updated_by => fnd_globaL.user_id
3613 ,x_addr_creation_date => SYSDATE
3614 ,x_addr_created_by => fnd_global.user_id
3615 ,x_addr_last_update_login => fnd_global.login_id
3616 ,x_addr_attribute_category => p_adr_recinfo.addr_attribute_category
3617 ,x_addr_attribute1 => p_adr_recinfo.addr_attribute1
3618 ,x_addr_attribute2 => p_adr_recinfo.addr_attribute2
3619 ,x_addr_attribute3 => p_adr_recinfo.addr_attribute3
3620 ,x_addr_attribute4 => p_adr_recinfo.addr_attribute4
3621 ,x_addr_attribute5 => p_adr_recinfo.addr_attribute5
3622 ,x_addr_attribute6 => p_adr_recinfo.addr_attribute6
3623 ,x_addr_attribute7 => p_adr_recinfo.addr_attribute7
3624 ,x_addr_attribute8 => p_adr_recinfo.addr_attribute8
3625 ,x_addr_attribute9 => p_adr_recinfo.addr_attribute9
3626 ,x_addr_attribute10 => p_adr_recinfo.addr_attribute10
3627 ,x_addr_attribute11 => p_adr_recinfo.addr_attribute11
3628 ,x_addr_attribute12 => p_adr_recinfo.addr_attribute12
3629 ,x_addr_attribute13 => p_adr_recinfo.addr_attribute13
3630 ,x_addr_attribute14 => p_adr_recinfo.addr_attribute14
3631 ,x_addr_attribute15 => p_adr_recinfo.addr_attribute15
3632 ,x_change_mode => p_change_mode
3633 ,x_return_status => x_return_status
3634 ,x_return_message => x_return_message
3635 ,x_source => p_loc_recinfo.source
3636 );
3637
3638 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3639 x_return_status := FND_API.G_RET_STS_ERROR;
3640 APP_EXCEPTION.Raise_Exception;
3641 END IF;
3642
3643 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.Insert_Locn_Row (-) ReturnStatus: '||x_return_status);
3644 END Insert_Locn_Row;
3645
3646 /* --- CHANGED functions and procedures for MOAC START --- */
3647 /*============================================================================+
3648 -- NAME : get_location_id
3649 -- DESCRIPTION : This FUNCTION RETURNs Location id for given location code
3650 -- and location type look up code.
3651 -- SCOPE : PUBLIC
3652 -- INVOKED FROM : forms libraries
3653 -- ARGUMENTS : IN : p_location_code, p_loctn_type_lookup_code
3654 -- RETURNS : Location id
3655 -- HISTORY :
3656 -- 24-Jun-05 piagrawa o Created
3657 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
3658 -- must call this.
3659 +============================================================================*/
3660 FUNCTION get_location_id (
3661 p_location_code IN VARCHAR2,
3662 p_loctn_type_lookup_code IN VARCHAR2,
3663 p_org_id IN NUMBER
3664 ) RETURN number
3665 IS
3666
3667 l_location_id NUMBER := NULL;
3668
3669 BEGIN
3670
3671
3672 SELECT location_id
3673 INTO l_location_id
3674 FROM pn_locations_all
3675 WHERE location_code = p_location_code
3676 AND location_type_lookup_code = p_loctn_type_lookup_code
3677 AND org_id = p_org_id
3678 AND ROWNUM = 1;
3679
3680 RETURN l_location_id;
3681
3682 EXCEPTION
3683 when no_data_found then
3684 return l_location_id;
3685 when others then
3686 return l_location_id;
3687
3688 END get_location_id ;
3689
3690
3691 /*============================================================================+
3692 -- NAME : check_unique_location_code
3693 -- DESCRIPTION : This procedure checks if location code is unique.
3694 -- INVOKED FROM : forms libraries
3695 -- ARGUMENTS : IN : x_return_status, x_location_id, x_location_code,
3696 -- x_active_start_date, x_active_end_date
3697 -- RETURNS : NONE
3698 -- HISTORY :
3699 -- 24-Jun-05 piagrawa o Created
3700 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
3701 -- must call this.
3702 +============================================================================*/
3703 PROCEDURE check_unique_location_code (
3704 x_return_status IN OUT NOCOPY VARCHAR2,
3705 x_location_id NUMBER,
3706 x_location_code VARCHAR2,
3707 x_active_start_date DATE,
3708 x_active_end_date DATE,
3709 x_org_id NUMBER
3710 ) IS
3711 l_dummy NUMBER;
3712
3713 CURSOR loc_code_cur IS
3714 SELECT 1
3715 FROM DUAL
3716 WHERE EXISTS (SELECT 1
3717 FROM pn_locations_all pnl
3718 WHERE pnl.location_code = x_location_code
3719 AND pnl.active_start_date = x_active_start_date
3720 AND pnl.active_end_date = NVL(x_active_end_date,g_end_of_time)
3721 AND ((x_location_id IS NULL) OR (pnl.location_id <> x_location_id))
3722 AND pnl.org_id = x_org_id
3723 );
3724
3725 BEGIN
3726
3727 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_unique_location_code (+)');
3728
3729 OPEN loc_code_cur;
3730 FETCH loc_code_cur INTO l_dummy;
3731 CLOSE loc_code_cur;
3732
3733 IF l_dummy = 1 THEN
3734 fnd_message.set_name ('PN','PN_DUP_LOCATION_CODE');
3735 fnd_message.set_token('LOCATION_CODE', x_location_code);
3736 x_return_status := 'E';
3737 END IF;
3738
3739 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_unique_location_code (-) ReturnStatus: '||x_return_status);
3740 END check_unique_location_code;
3741
3742
3743 /*============================================================================+
3744 -- NAME : check_unique_building
3745 -- DESCRIPTION : This procedure checks if building is unique.
3746 -- INVOKED FROM : forms libraries
3747 -- ARGUMENTS : IN : x_return_status, x_location_id, x_building,
3748 -- x_active_start_date, x_active_end_date
3749 -- RETURNS : NONE
3750 -- HISTORY :
3751 -- 24-Jun-05 piagrawa o Created
3752 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
3753 -- must call this.
3754 +============================================================================*/
3755 PROCEDURE check_unique_building (
3756 x_return_status IN OUT NOCOPY VARCHAR2,
3757 x_location_id NUMBER,
3758 x_building VARCHAR2,
3759 x_active_start_date DATE,
3760 x_active_end_date DATE,
3761 x_org_id NUMBER
3762 ) IS
3763 l_dummy NUMBER;
3764 CURSOR building_cur IS
3765 SELECT 1
3766 FROM DUAL
3767 WHERE EXISTS (SELECT 1
3768 FROM pn_locations_all pnl
3769 WHERE UPPER(pnl.building) = UPPER(x_building)
3770 AND ((x_location_id IS NULL) OR (pnl.location_id <> x_location_id))
3771 AND pnl.active_start_date <= x_active_start_date
3772 AND pnl.active_end_date >= x_active_end_date
3773 AND pnl.org_id = x_org_id
3774 );
3775
3776 BEGIN
3777
3778 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_unique_building (+)');
3779
3780 OPEN building_cur;
3781 FETCH building_cur INTO l_dummy;
3782 CLOSE building_cur;
3783
3784 IF l_dummy = 1 THEN
3785 fnd_message.set_name ('PN','PN_DUP_BUILDING');
3786 fnd_message.set_token('BUILDING', x_building);
3787 x_return_status := 'E';
3788 END IF;
3789
3790 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_unique_building (-) ReturnStatus: '||x_return_status);
3791 END check_unique_building;
3792
3793
3794 -------------------------------------------------------------------------------
3795 -- PROCEDURE : check_unique_building_alias
3796 -- PURPOSE : The function validates that the alias and hence the code
3797 -- of a building/land is unique.
3798 -- IN PARAM : Location alias, Location Type Lookup Code, Location ID
3799 -- History :
3800 -- 24-Jun-05 piagrawa o Created
3801 -- IMPORTANT - Use this function once MOAC is enabled. All form libraries
3802 -- must call this.
3803 -------------------------------------------------------------------------------
3804 FUNCTION check_unique_building_alias
3805 ( p_location_id NUMBER,
3806 p_location_alias VARCHAR2,
3807 p_location_type_lookup_code VARCHAR2,
3808 p_org_id NUMBER)
3809 RETURN BOOLEAN IS
3810
3811 l_dup_alias NUMBER;
3812 l_msg_name VARCHAR2(30);
3813 l_token VARCHAR2(30);
3814
3815 CURSOR dupAlias IS
3816 SELECT loc.location_id AS location_id
3817 FROM pn_locations_all loc
3818 WHERE loc.LOCATION_TYPE_LOOKUP_CODE = p_location_type_lookup_code
3819 AND loc.location_alias = p_location_alias
3820 AND loc.location_id <> NVL(p_location_id,-1)
3821 AND loc.org_id = p_org_id;
3822
3823 BEGIN
3824
3825 IF p_location_type_lookup_code IN ('BUILDING', 'LAND') THEN
3826 FOR i IN dupAlias LOOP
3827 l_dup_alias := i.location_id;
3828 END LOOP;
3829 END IF;
3830
3831 IF l_dup_alias IS NOT NULL THEN
3832 l_msg_name := 'PN_DUP_'||p_location_type_lookup_code||'_ALIAS';
3833 l_token := p_location_type_lookup_code||'_ALIAS';
3834 fnd_message.set_name('PN', l_msg_name);
3835 fnd_message.set_token(l_token, p_location_alias);
3836 RETURN FALSE;
3837 ELSE
3838 RETURN TRUE;
3839 END IF;
3840
3841 END check_unique_building_alias;
3842
3843 -------------------------------------------------------------------------------
3844 -- PROCDURE : check_unique_location_alias
3845 -- INVOKED FROM :
3846 -- PURPOSE : checks unique location alias
3847 -- HISTORY :
3848 -- 13-JUL-05 hrodda o Bug 4284035 - Replaced PN_LOCATION with _ALL table.
3849 -------------------------------------------------------------------------------
3850 PROCEDURE check_unique_location_alias (
3851 x_return_status IN OUT NOCOPY VARCHAR2,
3852 x_location_id NUMBER,
3853 x_parent_location_id NUMBER,
3854 x_location_type_lookup_code VARCHAR2,
3855 x_location_alias VARCHAR2,
3856 x_active_start_date DATE,
3857 x_active_end_date DATE,
3858 x_org_id NUMBER
3859 )
3860 IS
3861
3862 l_dummy NUMBER;
3863 l_set_name VARCHAR2(30);
3864 l_set_token VARCHAR2(30);
3865
3866 CURSOR loc_alias_cur IS
3867 SELECT 1
3868 FROM DUAL
3869 WHERE EXISTS (SELECT 1
3870 FROM pn_locations_all pnl
3871 WHERE pnl.location_alias = x_location_alias
3872 AND location_type_lookup_code = x_location_type_lookup_code
3873 AND ((x_location_id IS NULL) OR (pnl.location_id <> x_location_id))
3874 AND pnl.parent_location_id = x_parent_location_id
3875 AND pnl.active_start_date = x_active_start_date
3876 AND pnl.active_end_date = NVL(x_active_end_date,g_end_of_time)
3877 AND pnl.org_id = x_org_id
3878 );
3879
3880 BEGIN
3881
3882 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_unique_location_alias (+)');
3883 pnp_debug_pkg.debug(' ChkUniAlis=> In Parameters :: LocAlias: '||x_location_alias||', LocId: '||x_location_id
3884 ||', Type: '||x_location_type_lookup_code);
3885 pnp_debug_pkg.debug(' ChkUniAlis=> ActStrDate : '||TO_CHAR(x_active_start_date, 'MM/DD/YYYY')
3886 ||', ActEndDate : '||TO_CHAR(x_active_end_date, 'MM/DD/YYYY'));
3887
3888 -- If the location alias is null then we don't need to check
3889 -- This will happen only for data created by the external system.
3890
3891 IF (x_location_alias IS NULL) THEN
3892 RETURN;
3893 END IF;
3894
3895 IF x_location_type_lookup_code IN ('BUILDING', 'LAND', 'FLOOR', 'PARCEL', 'OFFICE', 'SECTION') THEN
3896 l_set_name := 'PN_DUP_'||x_location_type_lookup_code||'_ALIAS';
3897 l_set_token := x_location_type_lookup_code||'_ALIAS';
3898 pnp_debug_pkg.debug(' ChkUniAlis> Duplicate');
3899 ELSE
3900 -- we should never reach this place
3901 x_return_status := 'E';
3902 END IF;
3903
3904 OPEN loc_alias_cur;
3905 FETCH loc_alias_cur INTO l_dummy;
3906 CLOSE loc_alias_cur;
3907
3908 IF l_dummy = 1 THEN
3909 fnd_message.set_name ('PN',l_set_name);
3910 fnd_message.set_token(l_set_token, x_location_alias);
3911 x_return_status := 'E';
3912 pnp_debug_pkg.debug(' ChkUniAlis> Error');
3913 END IF;
3914
3915 pnp_debug_pkg.debug('PNT_LOCATIONS_PKG.check_unique_location_alias (-) ReturnStatus: '||x_return_status);
3916
3917 END check_unique_location_alias;
3918
3919 /* --- CHANGED functions and procedures for MOAC END --- */
3920
3921 ---------------------------------------------------------------------------------------
3922 -- End of Pkg
3923 ---------------------------------------------------------------------------------------
3924 END PNT_LOCATIONS_PKG ;
[Home] [Help]