[Home] [Help]
PACKAGE BODY: APPS.PN_CAD_IMPORT
Source
1 PACKAGE BODY PN_CAD_IMPORT AS
2 -- $Header: PNVLOSPB.pls 120.12.12010000.4 2008/11/27 05:06:36 rthumma ship $
3
4 -------------------------------------------------------------------------------
5 -- NAME : IMPORT_CAD
6 -- DESCRIPTION : Wrapper Procedure for Import of Locations/Space_Allocations
7 -- Data
8 -- NOTES : Called from within "Import from CAFM" form
9 -- Based on value function_flag, branches off into
10 -- locations_itf() or space_allocations_itf() procedures below
11 -- SCOPE : PRIVATE
12 -- PURPOSE :
13 -- INVOKED FROM :
14 -- ARGUMENTS : IN: p_Batch_Name
15 -- Function_Flag (L for Locations, S for Space)
16 -- OUT: Errbuf
17 -- RetCode
18 -- REFERENCE : PN_COMMON.debug()
19 -- HISTORY :
20
21 -- 1998 Naga Vijayapuram o Created
22 -- 1999 Naga Vijayapuram o Modified - Included Validations
23 -- 12-NOV-02 Kiran Hegde o Removed all calls to PNP_DEBUG_PKG.
24 -- enable_file_debug().
25 -- 01-APR-05 piagrawa o Modified the signature to include org_id
26 -- and updated the calls to locations_itf and
27 -- space_allocations_itf to pass org_id as
28 -- argument
29 -------------------------------------------------------------------------------
30
31 PROCEDURE IMPORT_CAD (
32 errbuf OUT NOCOPY VARCHAR2,
33 retcode OUT NOCOPY VARCHAR2,
34 p_batch_name VARCHAR2,
35 function_flag VARCHAR2,
36 p_org_id NUMBER
37 ) IS
38
39 l_filename VARCHAR2(40) := 'IMPORT'||to_char(SYSDATE,'DDMMYYHHMMSS');
40 l_org_ID NUMBER;
41
42 BEGIN
43 /* init the ORG */
44 IF pn_mo_cache_utils.is_MOAC_enabled THEN
45 l_org_ID := p_org_ID;
46 ELSE
47 l_org_ID := fnd_profile.value('ORG_ID');
48 END IF;
49
50 IF (function_flag = 'L') then
51 BEGIN
52 locations_itf(p_batch_name, l_org_ID, errbuf, retcode);
53 EXCEPTION
54 when OTHERS then
55 put_log('at exception');
56 END;
57
58 ELSE
59 BEGIN
60 space_allocations_itf(p_batch_name, l_org_ID, errbuf, retcode);
61 EXCEPTION
62 when OTHERS then
63 APP_EXCEPTION.raise_exception;
64 END;
65
66 END IF;
67
68 END IMPORT_CAD;
69
70
71 /*=============================================================================+
72 | PROCEDURE
73 | Locations_Itf
74 |
75 | DESCRIPTION
76 | Handles Import of Locations Data
77 |
78 | SCOPE - PRIVATE
79 |
80 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
81 |
82 | ARGUMENTS:
83 | IN: p_Batch_Name
84 | OUT: Errbuf
85 | RetCode
86 |
87 | NOTES:
88 | Called by IMPORT_CAD Procedure Above
89 |
90 | MODIFICATION HISTORY
91 | Created Naga Vijayapuram 1998
92 | Modified Naga Vijayapuram 1999 Included Validations
93 | 23-APR-02 Kiran Hegde o Fixed Bug#2324687. Added columns Location_Alias,
94 | Property_Id to PN_LOCATIONS_ITF. Modified the
95 | Inserts and Updates. Added function
96 | Exists_Property_Id for validating Property_id.
97 | Added validations for Locations_Alias.
98 | 08-MAY-02 Kiran Hegde o Fixed Bug#2341761. Added column
99 | Standard_Type_Lookup_Code to PN_LOCATIONS_ITF.
100 | Modified inserts and updates appropriately.
101 | Changed the validations of Usable, Rentable,
102 | Assignable to match the validations in the form
103 | 14-MAY-02 Kiran Hegde o Added validations to Locations_Itf to behave
104 | similar to the forms for Assgn and Common Area
105 | 12-nov-02 Kiran Hegde o Removed all calls to enable_file_debug().
106 | 22-JAN-03 MRinal Misra o Put a check for rentable area must not be
107 | greater than gross area of parent and displayed
108 | msg. PN_GROSS_RENTABLE in log.
109 | 24-JAN-03 Kiran o Made several changes to Locations_Itf procedure
110 | 1. Added validations for Active Start Date and
111 | End date to behave like the Locations form.
112 | 2. Removed rollback from exception handling
113 | 3. Added validations for a valid combination of
114 | Id and Code for Locations.
115 | 4. Changed several messages
116 | 07-AUG-03 Kiran o Populated the Addresses %ROWTYPE variable
117 | before passing it to PNT_LOCATIONS_PKG.
118 | correct_update_row. Removd explicit update
119 | statements to update pn_addresses_all. Bug #
120 | 3079433. Changed 'IF' condition for calling
121 | validate_gross_area in 'COrrect' and 'Update'
122 | mode.
123 | 07-JAN-04 Daniel Thota o Added OUT param l_total_allocated_area_pct
124 | in call to pnp_util_func.get_allocated_area
125 | bug # 3354278
126 | 01-APR-05 piagrawa o Modified the signature to include org_id,
127 | Modified the select statements to retrieve values
128 | from _ALL tables and in INSERT_ROW call passed
129 | the value of p_org_id in place of
130 | fnd_profile.value('ORG_ID')
131 | 14-JUL-05 piagrawa o passed org id in call to Is_Id_Code_Valid
132 | 27-JUL-05 SatyaDeep o Added a check for office/section in the call to
133 | PNT_LOCATIONS_PKG.check_location_gaps for Bug#4503407
134 | 27-JUL-05 SatyaDeep o Added validation for NEW_ACTIVE_START_DATE
135 | and NEW_ACTIVE_END_DATE for Bug#4503407
136 | 23-NOV-05 Hareesha o Fetched org_id using cursor
137 | 01-DEC-05 Pikhar o Passed org_id in
138 | PNT_LOCATIONS_PKG.check_unique_building_alias
139 | 26-Nov-08 rthumma o Bug 6670882 : Modified to do a commit after the
140 | processing loop.
141 | 27-Nov-08 rthumma o Bug 6861678 : Selected the occupancy_status_code
142 | of the parent_location in cursor loccur to
143 | set the occupancy_status_code for the child.
144 +===========================================================================*/
145
146 PROCEDURE LOCATIONS_ITF (
147 p_batch_name VARCHAR2,
148 p_org_id NUMBER,
149 errbuf OUT NOCOPY VARCHAR2,
150 retcode OUT NOCOPY VARCHAR2
151 )
152 IS
153 l_succ NUMBER DEFAULT 0;
154 l_fail NUMBER DEFAULT 0;
155 l_total NUMBER DEFAULT 0;
156 l_returnStatus VARCHAR2(30) DEFAULT NULL;
157 l_return_message VARCHAR2(32767) := NULL;
158 l_total_for_commit NUMBER DEFAULT 0; -- Bug 6670882
159 l_occ_status varchar2(1) := ''; /* Bug 6861678 */
160
161 CURSOR loccur IS
162 SELECT pli.*, pli.rowid,occupancy_status_code
163 FROM PN_LOCATIONS_ITF pli,
164 PN_LOCATIONS_ALL pnl
165 WHERE pli.parent_location_id = pnl.location_id(+)
166 AND pli.batch_name = p_batch_name
167 AND pli.transferred_to_pn IS NULL
168 ORDER BY pli.location_type_lookup_code, pli.active_start_date;
169
170 l_address_id NUMBER;
171 l_error_message VARCHAR2(512);
172 l_retcode VARCHAR2(1);
173 l_rowid VARCHAR2(30);
174 l_as_of_date DATE := SYSDATE;
175 l_allocated_area NUMBER;
176 l_allocated_area_pct NUMBER;
177 l_future VARCHAR2(1);
178 l_asgn_area_chng_flag VARCHAR2(1);
179
180 INVALID_RECORD EXCEPTION;
181 DELETE_RECORD EXCEPTION;
182
183 l_last_update_date DATE;
184 l_location_id NUMBER;
185 l_loc_id NUMBER;
186 v_loc_rec PN_LOCATIONS%ROWTYPE;
187 v_addr_rec PN_ADDRESSES%ROWTYPE;
188 l_act_str_dt DATE;
189 l_pn_locations_rec PN_LOCATIONS_ALL%ROWTYPE;
190 l_pn_addresses_rec PN_ADDRESSES_ALL%ROWTYPE;
191 l_active_start_date DATE;
192 l_active_end_date DATE;
193 l_filename VARCHAR2(50) := 'IMPORT_'||to_char(sysdate,'DDMMYYYYHHMMSS');
194 l_org_id NUMBER;
195
196 BEGIN
197
198 IF pn_mo_cache_utils.is_MOAC_enabled AND p_org_id IS NULL THEN
199 l_org_id := pn_mo_cache_utils.get_current_org_id;
200 ELSE
201 l_org_id := p_org_id;
202 END IF;
203
204 retcode := '0';
205
206 FOR loc in loccur LOOP
207
208 l_total := l_total + 1;
209 l_total_for_commit := l_total_for_commit + 1; -- Bug 6670882
210 l_occ_status := loc.occupancy_status_code; -- Bug 6861678
211
212 BEGIN
213 Put_Log('=============== Record #: ' || l_total || ' ===============');
214
215 --------------------
216 -- Set save point --
217 --------------------
218 put_log('Setting the save point');
219 SAVEPOINT S1;
220
221 Put_Log('Validate Entry Types');
222 ------------------------------
223 -- Validate Entry Types
224 ------------------------------
225 if (loc.ENTRY_TYPE not in ('A', 'U','R')) then --added the 'R' for fix BUG#2127286
226 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_ENTRY_TYPE');
227 fnd_message.set_token('LOCATION_ID', loc.LOCATION_ID);
228 l_error_message := fnd_message.get;
229 raise INVALID_RECORD;
230 end if;
231
232 put_log('Validate Location Type Lookup_Code');
233 --------------------------------------
234 -- Validate Location Type Lookup_Code
235 --------------------------------------
236 if (NOT PNP_UTIL_FUNC.valid_lookup_code(
237 'PN_LOCATION_TYPE', loc.LOCATION_TYPE_LOOKUP_CODE)) then
238 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_TYPE');
239 fnd_message.set_token('LOCATION_ID', loc.location_id);
240 l_error_message := fnd_message.get;
241 raise INVALID_RECORD;
242 end if;
243
244 ------------------------------------------------------------------
245 -- NOTE: If we are here, it means we are handling INSERT/UPDATE --
246 -- The entry type, locations type are both fine --
247 ------------------------------------------------------------------
248
249 put_log('Validate BUILDING/LAND');
250 --------------------------------------
251 -- Validate BUILDING/LAND
252 --------------------------------------
253 if (loc.LOCATION_TYPE_LOOKUP_CODE IN ( 'BUILDING', 'LAND' ) and
254 loc.BUILDING is NULL) then
255 fnd_message.set_name('PN', 'PN_CAFM_BUILDING');
256 fnd_message.set_token('LOCATION_ID', loc.location_id);
257 l_error_message := fnd_message.get;
258 raise INVALID_RECORD;
259 end if;
260
261 put_log('Validate FLOOR/PARCEL');
262 --------------------------------------
263 -- Validate FLOOR/PARCEL
264 --------------------------------------
265 if (loc.LOCATION_TYPE_LOOKUP_CODE IN ( 'FLOOR' , 'PARCEL' ) and
266 loc.FLOOR is NULL) then
267 fnd_message.set_name('PN', 'PN_CAFM_FLOOR');
268 fnd_message.set_token('LOCATION_ID', loc.location_id);
269 l_error_message := fnd_message.get;
270 raise INVALID_RECORD;
271 end if;
272
273 put_log('Validate OFFICE/SECTION');
274 --------------------------------------
275 -- Validate OFFICE/SECTION
276 --------------------------------------
277 if (loc.LOCATION_TYPE_LOOKUP_CODE IN ( 'OFFICE' , 'SECTION' ) and
278 loc.OFFICE is NULL) then
279 fnd_message.set_name('PN', 'PN_CAFM_OFFICE');
280 fnd_message.set_token('LOCATION_ID', loc.location_id);
281 l_error_message := fnd_message.get;
282 raise INVALID_RECORD;
283 end if;
284
285 put_log('Validate Space Type Lookup_Code');
286 --------------------------------------
287 -- Validate Space Type Lookup_Code
288 --------------------------------------
289 IF (loc.LOCATION_TYPE_LOOKUP_CODE IN ('OFFICE','FLOOR' )) THEN
290 if (loc.SPACE_TYPE_LOOKUP_CODE is NOT NULL and
291 NOT PNP_UTIL_FUNC.valid_lookup_code(
292 'PN_SPACE_TYPE', loc.SPACE_TYPE_LOOKUP_CODE)) then
293 fnd_message.set_name('PN', 'PN_CAFM_SPACE_TYPE');
294 fnd_message.set_token('LOCATION_ID', loc.location_id);
295 l_error_message := fnd_message.get;
296 raise INVALID_RECORD;
297 end if;
298 END IF;
299
300 put_log('Validate Parcel Type Lookup_Code');
301 --------------------------------------
302 -- Validate Parcel Type Lookup_Code
303 --------------------------------------
304 IF (loc.LOCATION_TYPE_LOOKUP_CODE IN ('PARCEL','SECTION' )) THEN
305 if (loc.SPACE_TYPE_LOOKUP_CODE is NOT NULL and
306 NOT PNP_UTIL_FUNC.valid_lookup_code(
307 'PN_PARCEL_TYPE', loc.SPACE_TYPE_LOOKUP_CODE)) then
308 fnd_message.set_name('PN', 'PN_CAFM_SPACE_TYPE');
309 fnd_message.set_token('LOCATION_ID', loc.location_id);
310 l_error_message := fnd_message.get;
311 raise INVALID_RECORD;
312 end if;
313 END IF;
314
315 put_log('Validate Function Type Lookup_Code');
316 --------------------------------------
317 -- Validate Function Type Lookup_Code
318 --------------------------------------
319 IF (loc.LOCATION_TYPE_LOOKUP_CODE IN ('PARCEL','SECTION','FLOOR','OFFICE' )) THEN
320 if (loc.FUNCTION_TYPE_LOOKUP_CODE is NOT NULL and
321 NOT PNP_UTIL_FUNC.valid_lookup_code(
322 'PN_FUNCTION_TYPE', loc.FUNCTION_TYPE_LOOKUP_CODE)) then
323 fnd_message.set_name('PN', 'PN_CAFM_FUNCTION_TYPE');
324 fnd_message.set_token('LOCATION_ID', loc.location_id);
325 l_error_message := fnd_message.get;
326 raise INVALID_RECORD;
327 end if;
328 END IF;
329
330 put_log('Validate Standard Type Lookup_Code');
331 --------------------------------------
332 -- Validate Standard Type Lookup_Code
333 --------------------------------------
334 IF (loc.LOCATION_TYPE_LOOKUP_CODE IN ('PARCEL','SECTION','FLOOR','OFFICE' )) THEN
335 if (loc.STANDARD_TYPE_LOOKUP_CODE is NOT NULL and
336 NOT PNP_UTIL_FUNC.valid_lookup_code(
337 'PN_STANDARD_TYPE', loc.STANDARD_TYPE_LOOKUP_CODE)) then
338 fnd_message.set_name('PN', 'PN_CAFM_STANDARD_TYPE');
339 fnd_message.set_token('LOCATION_ID', loc.location_id);
340 l_error_message := fnd_message.get;
341 raise INVALID_RECORD;
342 end if;
343 END IF;
344
345 put_log('Validate Parent_Location_Id');
346 --------------------------------------
347 -- Validate Parent_Location_Id
348 --------------------------------------
349 IF (loc.LOCATION_TYPE_LOOKUP_CODE IN ( 'BUILDING' , 'LAND' )) AND
350 (loc.PARENT_LOCATION_ID is NOT NULL) THEN
351
352 fnd_message.set_name('PN', 'PN_CAFM_BUILDING_PARENT_LOC_ID');
353 fnd_message.set_token('LOCATION_ID', loc.location_id);
354 l_error_message := fnd_message.get;
355 RAISE INVALID_RECORD;
356
357 ELSIF ((loc.LOCATION_TYPE_LOOKUP_CODE = 'FLOOR'AND
358 get_location_type(loc.PARENT_LOCATION_ID) <> 'BUILDING' ) OR
359 (loc.LOCATION_TYPE_LOOKUP_CODE = 'PARCEL'AND
360 get_location_type(loc.PARENT_LOCATION_ID) <> 'LAND' )) THEN
361
362 fnd_message.set_name('PN', 'PN_CAFM_FLOOR_PARENT_LOC_ID');
363 fnd_message.set_token('LOCATION_ID', loc.location_id);
364 IF loc.LOCATION_TYPE_LOOKUP_CODE = 'FLOOR' THEN
365 fnd_message.set_token('FLR_OR_PARCEL', 'floor');
366 fnd_message.set_token('BLD_OR_LAND', 'building');
367 ELSIF loc.LOCATION_TYPE_LOOKUP_CODE = 'PARCEL' THEN
368 fnd_message.set_token('FLR_OR_PARCEL', 'parcel');
369 fnd_message.set_token('BLD_OR_LAND', 'land');
370 END IF;
371 l_error_message := fnd_message.get;
372 RAISE INVALID_RECORD;
373
374 ELSIF ((loc.LOCATION_TYPE_LOOKUP_CODE = 'OFFICE'AND
375 get_location_type(loc.PARENT_LOCATION_ID) <> 'FLOOR' ) OR
376 (loc.LOCATION_TYPE_LOOKUP_CODE = 'SECTION'AND
377 get_location_type(loc.PARENT_LOCATION_ID) <> 'PARCEL' )) THEN
378
379 fnd_message.set_name('PN', 'PN_CAFM_OFFICE_PARENT_LOC_ID');
380 fnd_message.set_token('LOCATION_ID', loc.location_id);
381 IF loc.LOCATION_TYPE_LOOKUP_CODE = 'OFFICE' THEN
382 fnd_message.set_token('FLR_OR_PARCEL', 'floor');
383 fnd_message.set_token('OFF_OR_SECT', 'office');
384 ELSIF loc.LOCATION_TYPE_LOOKUP_CODE = 'SECTION' THEN
385 fnd_message.set_token('FLR_OR_PARCEL', 'parcel');
386 fnd_message.set_token('OFF_OR_SECT', 'section');
387 END IF;
388 l_error_message := fnd_message.get;
389 RAISE INVALID_RECORD;
390
391 END IF;
392
393 put_log('Validate Active_Start_Date');
394 -------------------------------------------------
395 -- Validate Active_Start_Date
396 -------------------------------------------------
397 if loc.ACTIVE_START_DATE is NULL then
398 fnd_message.set_name('PN', 'PN_CAFM_ACT_ST_DT');
399 fnd_message.set_token('LOCATION_ID', loc.location_id);
400 l_error_message := fnd_message.get;
401 raise INVALID_RECORD;
402 end if;
403 if TRUNC(loc.ACTIVE_START_DATE) >
404 TRUNC(NVL(loc.active_end_date,PNT_LOCATIONS_PKG.G_END_OF_TIME)) then
405 fnd_message.set_name('PN', 'PN_LOCN_STDT_VALID_MSG');
406 fnd_message.set_token('LOCATION_ID', loc.location_id);
407 l_error_message := fnd_message.get;
408 raise INVALID_RECORD;
409 end if;
410
411 put_log('Validate Active_Start_Date and Active_End_Date wrt the parent Location');
412 -------------------------------------------------------------------------
413 -- Validate Active_Start_Date and Active_End_Date wrt the parent Location
414 -------------------------------------------------------------------------
415 if loc.LOCATION_TYPE_LOOKUP_CODE in ('FLOOR', 'OFFICE', 'PARCEL', 'SECTION') then
416 select min(active_start_date), nvl(max(active_end_date),PNT_LOCATIONS_PKG.G_END_OF_TIME)
417 into l_active_start_date, l_active_end_date
418 from pn_locations_all where location_id = loc.PARENT_LOCATION_ID
419 group by location_id;
420
421 if trunc(loc.active_start_date) < trunc(l_active_start_date) then
422 fnd_message.set_name('PN', 'PN_CAFM_LOC_CHILD_ST_DT');
423 fnd_message.set_token('LOCATION_ID', loc.location_id);
424 fnd_message.set_token('P_LOCATION_ID', loc.parent_location_id);
425 l_error_message := fnd_message.get;
426 raise INVALID_RECORD;
427 elsif trunc(nvl(loc.active_end_date,PNT_LOCATIONS_PKG.G_END_OF_TIME))
428 > trunc(l_active_end_date) then
429 fnd_message.set_name('PN', 'PN_CAFM_LOC_CHILD_END_DT');
430 fnd_message.set_token('LOCATION_ID', loc.location_id);
431 fnd_message.set_token('P_LOCATION_ID', loc.parent_location_id);
432 l_error_message := fnd_message.get;
433 raise INVALID_RECORD;
434 end if;
435 end if;
436
437 put_log('Validate the combination of id and code');
438 ------------------------------------------
439 -- Validate the combination of id and code
440 ------------------------------------------
441 if not (Is_Id_Code_Valid(loc.LOCATION_ID, loc.LOCATION_CODE, l_org_id)) then
442 fnd_message.set_name('PN', 'PN_CAFM_LOC_ID_CODE_COMB');
443 fnd_message.set_token('LOCATION_ID', loc.location_id);
444 l_error_message := fnd_message.get;
445 raise INVALID_RECORD;
446 end if;
447
448 put_log('Validate for Duplicate Building/Land Code');
449 --------------------------------------------
450 -- Validate for Duplicate Building/Land Code
451 --------------------------------------------
452 if loc.LOCATION_TYPE_LOOKUP_CODE in ('BUILDING', 'LAND') then
453 if not(PNT_LOCATIONS_PKG.check_unique_building_alias(loc.LOCATION_ID,
454 loc.LOCATION_CODE,
455 loc.LOCATION_TYPE_LOOKUP_CODE,
456 l_org_id))
457 then
458 fnd_message.set_token('LOCATION_ID', loc.location_id);
459 l_error_message := fnd_message.get;
460 raise INVALID_RECORD;
461 end if;
462 end if;
463
464 put_log('Validate Lease_Or_Owned');
465 --------------------------------------
466 -- Validate Lease_Or_Owned
467 --------------------------------------
468 if (loc.LEASE_OR_OWNED is NULL) then
469 if (loc.ENTRY_TYPE IN ('A', 'U')) then
470 loc.LEASE_OR_OWNED := 'L';
471 end if;
472 else
473 if (NOT PNP_UTIL_FUNC.valid_lookup_code(
474 'PN_LEASED_OR_OWNED', loc.LEASE_OR_OWNED)) then
475 fnd_message.set_name('PN', 'PN_CAFM_LEASE_OR_OWNED');
476 fnd_message.set_token('LOCATION_ID', loc.location_id);
477 l_error_message := fnd_message.get;
478 raise INVALID_RECORD;
479 end if;
480 end if;
481
482 put_log('Validate Country');
483 --------------------------------------
484 -- Validate Country (Territory)
485 --------------------------------------
486 if (loc.COUNTRY is NULL) then
487 if (loc.ENTRY_TYPE IN ('A', 'U')) then
488 loc.COUNTRY := fnd_profile.value('DEFAULT_COUNTRY');
489 end if;
490 else
491 if (NOT PNP_UTIL_FUNC.valid_country_code(loc.COUNTRY)) then
492 fnd_message.set_name('PN', 'PN_CAFM_COUNTRY');
493 fnd_message.set_token('LOCATION_ID', loc.location_id);
494 l_error_message := fnd_message.get;
495 raise INVALID_RECORD;
496 end if;
497 end if;
498
499 put_log('Validate Optimum_Capacity');
500 --------------------------------------
501 -- Validate Optimum_Capacity
502 --------------------------------------
503 if (loc.optimum_capacity > loc.max_capacity) then
504 fnd_message.set_name('PN', 'PN_CAFM_OPTIMUM_CAPACITY');
505 fnd_message.set_token('LOCATION_ID', loc.location_id);
506 l_error_message := fnd_message.get;
507 raise INVALID_RECORD;
508 end if;
509
510 put_log('Setting appropriate areas to null based on lookup type');
511 ---------------------------------------------------------
512 -- Setting appropriate areas to null based on lookup type
513 ---------------------------------------------------------
514 if (loc.LOCATION_TYPE_LOOKUP_CODE in ('BUILDING', 'LAND', 'FLOOR', 'PARCEL')) then
515 loc.Rentable_Area := null;
516 loc.Usable_Area := null;
517 loc.Assignable_Area := null;
518 loc.Common_Area := null;
519 elsif (loc.LOCATION_TYPE_LOOKUP_CODE in ('OFFICE', 'SECTION')) then
520 loc.GROSS_AREA := null;
521 end if;
522
523 put_log('Validate Usable_Area');
524 --------------------------------------
525 -- Validate Usable_Area
526 --------------------------------------
527 if (loc.Usable_Area > loc.Rentable_Area) then
528 fnd_message.set_name('PN', 'PN_CAFM_USABLE_AREA');
529 fnd_message.set_token('LOCATION_ID', loc.location_id);
530 l_error_message := fnd_message.get;
531 raise INVALID_RECORD;
532 end if;
533
534 put_log('Validate Assignable_Area');
535 --------------------------------------
536 -- Validate Assignable_Area
537 --------------------------------------
538 if (loc.Assignable_Area > loc.Rentable_Area) then
539 fnd_message.set_name('PN', 'PN_CAFM_ASSIGNABLE_AREA');
540 fnd_message.set_token('LOCATION_ID', loc.location_id);
541 l_error_message := fnd_message.get;
542 raise INVALID_RECORD;
543 end if;
544
545 put_log('Validate Assignable_Area/Common Area is not null for Offices/Section');
546
547 -------------------------------------------------------------------
548 -- Validate Assignable_Area / Common Area is not null for location types 'OFFICE','SECTION'
549 -------------------------------------------------------------------
550 if (loc.assignable_area is null and
551 loc.location_type_lookup_code in ('OFFICE','SECTION') and
552 nvl(loc.common_area_flag,'N') = 'N') then
553 fnd_message.set_name('PN','PN_CAFM_ASSIGNABLE_REQ');
554 fnd_message.set_token('LOCATION_ID', loc.location_id);
555 l_error_message := fnd_message.get;
556 raise INVALID_RECORD;
557 end if;
558
559 if (loc.common_area is null and
560 loc.location_type_lookup_code in ('OFFICE','SECTION') and
561 loc.common_area_flag = 'Y') then
562 fnd_message.set_name('PN','PN_CAFM_COMMON_AREA_REQ');
563 fnd_message.set_token('LOCATION_ID', loc.location_id);
564 l_error_message := fnd_message.get;
565 raise INVALID_RECORD;
566 end if;
567
568 put_log('Validate Common_Area and Assignable_Area');
569 -------------------------------------------------------------------
570 -- Validate Common_Area and Assignable_Area are mutually exclusive
571 -------------------------------------------------------------------
572 if (loc.Common_Area is not null and loc.Assignable_Area is not null) then
573 fnd_message.set_name('PN', 'PN_CAFM_COMMON_ASSIGNABLE');
574 fnd_message.set_token('LOCATION_ID', loc.location_id);
575 l_error_message := fnd_message.get;
576 raise INVALID_RECORD;
577 end if;
578
579 put_log('Validate Common_Area');
580 --------------------------------------
581 -- Validate Common_Area
582 --------------------------------------
583 if (loc.Common_Area > loc.Rentable_Area) then
584 fnd_message.set_name('PN', 'PN_CAFM_COMMON_AREA');
585 fnd_message.set_token('LOCATION_ID', loc.location_id);
586 l_error_message := fnd_message.get;
587 raise INVALID_RECORD;
588 end if;
589
590 put_log('Validate SOURCE');
591 --------------------------------------
592 -- Validate SOURCE
593 --------------------------------------
594 if (NOT PNP_UTIL_FUNC.valid_lookup_code(
595 'PN_SOURCE', loc.SOURCE)) then
596 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_SOURCE');
597 fnd_message.set_token('LOCATION_ID', loc.location_id);
598 l_error_message := fnd_message.get;
599 raise INVALID_RECORD;
600 end if;
601
602 put_log('Validate PROPERTY_ID');
603 --------------------------------------
604 -- Validate PROPERTY_ID
605 --------------------------------------
606 if ( NOT EXISTS_PROPERTY_ID( loc.Property_Id ) ) then
607 fnd_message.set_name('PN', 'PN_CAFM_INVALID_PROPERTY_ID');
608 fnd_message.set_token('PROPERTY_ID', loc.Property_Id);
609 l_error_message := fnd_message.get;
610 raise INVALID_RECORD;
611 end if;
612
613 ---------------------------------------------------------------------------
614 -- General validatons all done. Now, we move to the specific validations --
615 -- and then to insert/correct/update --
616 ---------------------------------------------------------------------------
617
618 put_log('ENTRY_TYPE: --' || loc.ENTRY_TYPE || '--');
619
620 IF (loc.ENTRY_TYPE = 'A') THEN
621
622 put_log('Validate for Rentable area v/s Gross Area');
623 --------------------------------------------
624 -- Validate for Rentable area v/s Gross Area
625 --------------------------------------------
626 if loc.LOCATION_TYPE_LOOKUP_CODE in ('OFFICE', 'SECTION') then
627 if NOT pnt_locations_pkg.validate_gross_area
628 (p_loc_id => loc.PARENT_LOCATION_ID,
629 p_area => nvl(loc.RENTABLE_AREA,0),
630 p_lkp_code => loc.LOCATION_TYPE_LOOKUP_CODE,
631 p_act_str_dt => trunc(loc.ACTIVE_START_DATE),
632 p_act_end_dt => trunc(nvl(loc.ACTIVE_END_DATE,
633 PNT_LOCATIONS_PKG.G_END_OF_TIME))) --Used trunc() :Bug 6009957
634 then
635 fnd_message.set_name('PN', 'PN_GROSS_RENTABLE');
636 fnd_message.set_token('LOCATION_ID', loc.location_id);
637 l_error_message := fnd_message.get;
638 raise INVALID_RECORD;
639 end if;
640 end if;
641
642 --------------------------------------
643 -- Insert Data into PN_LOCATIONS
644 --------------------------------------
645 put_log( 'Just before Insert');
646
647 BEGIN
648
649 l_address_id := NULL;
650 PNT_LOCATIONS_PKG.INSERT_ROW (
651 x_rowid => l_rowid
652 ,x_org_id => l_org_id
653 ,x_LOCATION_ID => loc.location_id
654 ,x_LAST_UPDATE_DATE => sysdate
655 ,x_LAST_UPDATED_BY => fnd_global.user_id
656 ,x_CREATION_DATE => sysdate
657 ,x_CREATED_BY => fnd_global.user_id
658 ,x_LAST_UPDATE_LOGIN => fnd_global.user_id
659 ,x_LOCATION_PARK_ID => NULL
660 ,x_LOCATION_TYPE_LOOKUP_CODE => loc.location_type_lookup_code
661 ,x_SPACE_TYPE_LOOKUP_CODE => loc.space_type_lookup_code
662 ,x_FUNCTION_TYPE_LOOKUP_CODE => loc.function_type_lookup_code
663 ,x_STANDARD_TYPE_LOOKUP_CODE => loc.standard_type_lookup_code
664 ,x_LOCATION_ALIAS => loc.location_alias
665 ,x_LOCATION_CODE => loc.location_code
666 ,x_BUILDING => loc.building
667 ,x_LEASE_OR_OWNED => loc.lease_or_owned
668 ,x_CLASS => loc.class
669 ,x_STATUS_TYPE => loc.status_type
670 ,x_FLOOR => loc.floor
671 ,x_OFFICE => loc.office
672 ,x_MAX_CAPACITY => loc.max_capacity
673 ,x_OPTIMUM_CAPACITY => loc.optimum_capacity
674 ,x_GROSS_AREA => loc.gross_area
675 ,x_RENTABLE_AREA => loc.rentable_area
676 ,x_USABLE_AREA => loc.usable_area
677 ,x_ASSIGNABLE_AREA => loc.assignable_area
678 ,x_COMMON_AREA => loc.common_area
679 ,x_SUITE => loc.suite
680 ,x_ALLOCATE_COST_CENTER_CODE => loc.allocate_cost_center_code
681 ,x_UOM_CODE => loc.uom_code
682 ,x_DESCRIPTION => NULL
683 ,x_PARENT_LOCATION_ID => loc.parent_location_id
684 ,x_INTERFACE_FLAG => NULL
685 ,x_request_id => nvl(fnd_profile.value('CONC_REQUEST_ID'), 0)
686 ,x_PROGRAM_ID => nvl(fnd_profile.value('CONC_PROGRAM_APPLICATION_ID'), 0)
687 ,x_PROGRAM_APPLICATION_ID => nvl(fnd_profile.value('CONC_PROGRAM_ID'), 0)
688 ,x_PROGRAM_UPDATE_DATE => SYSDATE
689 ,x_STATUS => 'A'
690 ,x_PROPERTY_ID => loc.property_id
691 ,x_ATTRIBUTE_CATEGORY => loc.attribute_category
692 ,x_ATTRIBUTE1 => loc.attribute1
693 ,x_ATTRIBUTE2 => loc.attribute2
694 ,x_ATTRIBUTE3 => loc.attribute3
695 ,x_ATTRIBUTE4 => loc.attribute4
696 ,x_ATTRIBUTE5 => loc.attribute5
697 ,x_ATTRIBUTE6 => loc.attribute6
698 ,x_ATTRIBUTE7 => loc.attribute7
699 ,x_ATTRIBUTE8 => loc.attribute8
700 ,x_ATTRIBUTE9 => loc.attribute9
701 ,x_ATTRIBUTE10 => loc.attribute10
702 ,x_ATTRIBUTE11 => loc.attribute11
703 ,x_ATTRIBUTE12 => loc.attribute12
704 ,x_ATTRIBUTE13 => loc.attribute13
705 ,x_ATTRIBUTE14 => loc.attribute14
706 ,x_ATTRIBUTE15 => loc.attribute15
707 ,x_address_id => l_address_id
708 ,x_address_line1 => loc.address_line1
709 ,x_address_line2 => loc.address_line2
710 ,x_address_line3 => loc.address_line3
711 ,x_address_line4 => loc.address_line4
712 ,x_county => loc.county
713 ,x_city => loc.city
714 ,x_state => loc.state
715 ,x_province => loc.province
716 ,x_zip_code => loc.zip_code
717 ,x_country => loc.country
718 ,x_territory_id => NULL
719 ,x_addr_last_update_date => SYSDATE
720 ,x_addr_last_updated_by => FND_GLOBAl.USER_ID
721 ,x_addr_creation_date => SYSDATE
722 ,x_addr_created_by => FND_GLOBAL.USER_ID
723 ,x_addr_last_update_login => FND_GLOBAL.USER_ID
724 ,x_addr_attribute_category => loc.addr_attribute_category
725 ,x_addr_attribute1 => loc.addr_attribute1
726 ,x_addr_attribute2 => loc.addr_attribute2
727 ,x_addr_attribute3 => loc.addr_attribute3
728 ,x_addr_attribute4 => loc.addr_attribute4
729 ,x_addr_attribute5 => loc.addr_attribute5
730 ,x_addr_attribute6 => loc.addr_attribute6
731 ,x_addr_attribute7 => loc.addr_attribute7
732 ,x_addr_attribute8 => loc.addr_attribute8
733 ,x_addr_attribute9 => loc.addr_attribute9
734 ,x_addr_attribute10 => loc.addr_attribute10
735 ,x_addr_attribute11 => loc.addr_attribute11
736 ,x_addr_attribute12 => loc.addr_attribute12
737 ,x_addr_attribute13 => loc.addr_attribute13
738 ,x_addr_attribute14 => loc.addr_attribute14
739 ,x_addr_attribute15 => loc.addr_attribute15
740 ,x_COMMON_AREA_FLAG => loc.common_area_flag
741 ,x_ACTIVE_START_DATE => trunc(nvl(loc.active_start_date,PNT_LOCATIONS_PKG.G_START_OF_TIME))--Used trunc() :Bug 6009957
742 ,x_ACTIVE_END_DATE => trunc(nvl(loc.active_end_date ,PNT_LOCATIONS_PKG.G_END_OF_TIME))--Used trunc() :Bug 6009957
743 ,x_occupancy_status_code => l_occ_status /* Bug 6861678 */
744 ,x_change_mode => nvl(loc.change_mode,'INSERT')
745 ,x_return_status => l_returnstatus
746 ,x_return_message => l_return_message
747 ,x_bookable_flag => null
748 ,x_source => loc.source);
749
750 IF NOT( l_returnstatus = FND_API.G_RET_STS_SUCCESS) THEN
751 l_error_message := fnd_message.get;
752 pnp_debug_pkg.log(l_error_message);
753 raise INVALID_RECORD;
754 END IF;
755
756 EXCEPTION
757
758 WHEN OTHERS THEN
759 l_error_message := fnd_message.get;
760 pnp_debug_pkg.log(l_error_message);
761 pnp_debug_pkg.log(sqlerrm);
762 raise INVALID_RECORD;
763
764 END;
765
766 put_log('Just after Insert');
767
768 ELSIF (loc.ENTRY_TYPE IN ('U', 'R')) THEN
769
770 pnp_debug_pkg.log('change date: ' || loc.change_date);
771 pnp_debug_pkg.log('location: ' || loc.location_id);
772
773 --------------------------------------
774 -- Get the record to correct/update --
775 --------------------------------------
776 BEGIN
777 select *
778 into v_loc_rec
779 from PN_LOCATIONS_ALL
780 where LOCATION_ID = loc.location_id
781 and location_code = loc.location_code
782 and active_Start_date = trunc(loc.active_start_date)
783 and active_End_date = trunc(nvl(loc.active_end_date, PNT_LOCATIONS_PKG.G_END_OF_TIME));
784 --Used trunc() :Bug 6009957
785
786 EXCEPTION
787 WHEN no_data_found THEN
788 fnd_message.set_name('PN', 'PN_CAFM_LOC_REC_NOT_FOUND_UPD');
789 fnd_message.set_token('LOCATION_ID', loc.location_id);
790 l_error_message := fnd_message.get;
791 raise INVALID_RECORD;
792 END;
793
794 --------------------------------------
795 -- If building/land, get address --
796 --------------------------------------
797 pnp_debug_pkg.log('Address id = '|| v_loc_rec.address_id);
798 if (loc.location_type_lookup_code IN ( 'BUILDING' , 'LAND' ))
799 and v_loc_rec.address_id IS NOT NULL then
800
801 BEGIN
802 select *
803 into v_addr_rec
804 from pn_addresses_all
805 where address_id = v_loc_rec.address_id;
806 l_address_id := v_addr_rec.address_id;
807 EXCEPTION
808 WHEN no_data_found THEN
809 fnd_message.set_name('PN', 'PN_CAFM_LOC_REC_NOT_FOUND_UPD');
810 fnd_message.set_token('LOCATION_ID', loc.location_id);
811 l_error_message := fnd_message.get;
812 raise INVALID_RECORD;
813 END;
814
815 end if;
816
817 put_log('Validate New_Active_Start/End_Date');
818 -------------------------------------------------
819 -- Validate New_Active_Start/End_Date
820 -------------------------------------------------
821 IF NVL(loc.new_active_start_date, loc.active_start_date)
822 > NVL(loc.new_active_end_date, loc.active_end_date) THEN
823 fnd_message.set_name('PN', 'PN_LOCN_ENDT_VALID_MSG');
824 fnd_message.set_token('LOCATION_ID', loc.location_id);
825 l_error_message := fnd_message.get;
826 RAISE INVALID_RECORD;
827 END IF;
828
829 put_log('Validate if the start - end dates need to be changed');
830 -------------------------------------------------------------------
831 -- Check if the new start - end dates are null --
832 -- If not null, then we need to repeat the form leve validations --
833 -------------------------------------------------------------------
834
835 IF loc.new_active_start_date IS NOT NULL OR
836 loc.new_active_end_date IS NOT NULL THEN
837 -------------------------
838 -- set the ROWID first --
839 -------------------------
840 PNT_LOCATIONS_PKG.SET_ROWID(
841 p_location_id => v_loc_rec.location_id,
842 p_active_start_date => trunc(v_loc_rec.active_start_date), --Used trunc() :Bug 6009957
843 p_active_end_date => trunc(v_loc_rec.active_end_date), --Used trunc() :Bug 6009957
844 x_return_status => l_returnstatus,
845 x_return_message => l_return_message);
846
847 put_log('Check for location overlaps');
848 ---------------------------------
849 -- check for location overlaps --
850 ---------------------------------
851 PNT_LOCATIONS_PKG.check_location_overlap (
852 p_org_id => v_loc_rec.org_id,
853 p_location_id => v_loc_rec.location_id,
854 p_location_code => v_loc_rec.location_code,
855 p_location_type_lookup_code => v_loc_rec.location_type_lookup_code,
856 --Used trunc() :Bug 6009957
857 p_active_start_date => trunc(NVL(loc.new_active_start_date,
858 v_loc_rec.active_start_date)),
859 p_active_end_date => trunc(NVL(loc.new_active_end_date,
860 v_loc_rec.active_end_date)),
861 p_active_start_date_old => trunc(v_loc_rec.active_start_date),
862 p_active_end_date_old => trunc(v_loc_rec.active_end_date),
863 x_return_status => l_returnstatus,
864 x_return_message => l_return_message);
865
866 if NOT ( l_returnStatus = FND_API.G_RET_STS_SUCCESS) then
867 l_error_message := fnd_message.get;
868 pnp_debug_pkg.put_log_msg(l_return_message);
869 raise INVALID_RECORD;
870 end if;
871
872 put_log('Check for location gaps');
873 -----------------------------
874 -- check for location gaps --
875 -----------------------------
876 IF v_loc_rec.location_type_lookup_code NOT IN ('OFFICE', 'SECTION') THEN
877 PNT_LOCATIONS_PKG.check_location_gaps (
878 p_org_id => v_loc_rec.org_id,
879 p_location_id => v_loc_rec.location_id,
880 p_location_code => v_loc_rec.location_code,
881 p_location_type_lookup_code => v_loc_rec.location_type_lookup_code,
882 --Used trunc() :Bug 6009957
883 p_active_start_date => trunc(NVL(loc.new_active_start_date,
884 v_loc_rec.active_start_date)),
885 p_active_end_date => trunc(NVL(loc.new_active_end_date,
886 v_loc_rec.active_end_date)),
887 p_active_start_date_old => trunc(v_loc_rec.active_start_date),
888 p_active_end_date_old => trunc(v_loc_rec.active_end_date),
889 x_return_status => l_returnstatus,
890 x_return_message => l_return_message);
891 END IF;
892
893 if NOT ( l_returnStatus = FND_API.G_RET_STS_SUCCESS) then
894 l_error_message := fnd_message.get;
895 pnp_debug_pkg.put_log_msg(l_return_message);
896 raise INVALID_RECORD;
897 end if;
898
899 put_log('Check for active tenancies while bringing in the dates');
900 ------------------------------------------------------------
901 -- check if there exist tenancies while bringing in dates --
902 ------------------------------------------------------------
903 IF loc.new_active_start_date IS NOT NULL THEN
904 --Used trunc() :Bug 6009957
905 IF (trunc(loc.new_active_start_date) > trunc(v_loc_rec.active_start_date)) AND
906 PNP_UTIL_FUNC.exist_tenancy_for_start_date
907 (loc.location_id,
908 trunc(loc.new_active_start_date)) THEN
909 -- set msg based on loc type
910 IF loc.location_type_lookup_code IN ('OFFICE', 'SECTION') THEN
911 fnd_message.set_name('PN', 'PN_OFF_TEN_START_DATE');
912 ELSE
913 fnd_message.set_name('PN', 'PN_LOC_TEN_START_DATE');
914 END IF;
915 l_error_message := fnd_message.get;
916 RAISE INVALID_RECORD;
917 END IF;
918 END IF;
919
920 IF loc.new_active_end_date IS NOT NULL THEN
921 --Used trunc() :Bug 6009957
922 IF (trunc(loc.new_active_end_date) < trunc(v_loc_rec.active_end_date)) AND
923 PNP_UTIL_FUNC.exist_tenancy_for_end_date
924 (loc.location_id,
925 trunc(loc.new_active_end_date)) THEN
926 -- set msg based on loc type
927 IF loc.location_type_lookup_code IN ('OFFICE', 'SECTION') THEN
928 fnd_message.set_name('PN', 'PN_OFF_TEN_END_DATE');
929 ELSE
930 fnd_message.set_name('PN', 'PN_LOC_TEN_END_DATE');
931 END IF;
932 l_error_message := fnd_message.get;
933 RAISE INVALID_RECORD;
934 END IF;
935 END IF;
936
937 END IF; -- new date validations
938
939 put_log('Validate change_date');
940 -----------------------------
941 -- Validate p_as_of_date
942 -----------------------------
943 IF ( (nvl(loc.change_mode,'CORRECT') = 'UPDATE') and
944 loc.change_date is NULL ) THEN
945 fnd_message.set_name('PN', 'PN_CAFM_INVALID_CHANGE_DATE');
946 fnd_message.set_token('FIELD_NAME', 'Change Date');
947 l_error_message := fnd_message.get;
948 raise INVALID_RECORD;
949 END IF;
950
951 --Used trunc() :Bug 6009957
952 IF (nvl(loc.change_mode,'CORRECT') = 'UPDATE') AND
953 ((loc.CHANGE_DATE < trunc(NVL(loc.new_active_start_date,
954 v_loc_rec.active_start_date))) OR
955 (loc.CHANGE_DATE > trunc(NVL(loc.new_active_end_date,
956 v_loc_rec.active_end_date)))) THEN
957 fnd_message.set_name('PN', 'PN_LOC_SPILT_DATE_MSG');
958 fnd_message.set_token('LOCATION_ID', loc.LOCATION_ID);
959 l_error_message := fnd_message.get;
960 raise INVALID_RECORD;
961 END IF;
962
963 put_log('Validate for Rentable area v/s Gross Area');
964 --------------------------------------------
965 -- Validate for Rentable area v/s Gross Area
966 --------------------------------------------
967 -- init the active start - end dates for Area Validations
968
969 --Used trunc() :Bug 6009957
970 IF NVL(loc.change_mode, 'CORRECT') = 'UPDATE' THEN
971 l_active_start_date := trunc(loc.change_date);
972 ELSE
973 l_active_start_date := trunc(NVL(loc.new_active_start_date,
974 v_loc_rec.active_start_date));
975 END IF;
976 l_active_end_date := trunc(NVL(loc.new_active_end_date,
977 v_loc_rec.active_end_date));
978
979 IF loc.LOCATION_TYPE_LOOKUP_CODE IN ('OFFICE', 'SECTION') AND
980 loc.RENTABLE_AREA IS NOT NULL THEN
981
982 IF NOT pnt_locations_pkg.validate_gross_area
983 (p_loc_id => loc.PARENT_LOCATION_ID,
984 p_area => (NVL(v_loc_rec.RENTABLE_AREA,0)
985 - NVL(loc.RENTABLE_AREA,0)),
986 p_lkp_code => loc.LOCATION_TYPE_LOOKUP_CODE,
987 p_act_str_dt => l_active_start_date,
988 p_act_end_dt => l_active_end_date)
989 THEN
990 fnd_message.set_name('PN', 'PN_GROSS_RENTABLE');
991 fnd_message.set_token('LOCATION_ID', loc.location_id);
992 l_error_message := fnd_message.get;
993 raise INVALID_RECORD;
994 END IF;
995
996 ELSIF loc.LOCATION_TYPE_LOOKUP_CODE in ('BUILDING', 'LAND') AND
997 loc.GROSS_AREA IS NOT NULL THEN
998
999 IF NOT pnt_locations_pkg.validate_gross_area
1000 (p_loc_id => loc.LOCATION_ID,
1001 p_area => nvl(loc.GROSS_AREA,0),
1002 p_lkp_code => loc.LOCATION_TYPE_LOOKUP_CODE,
1003 p_act_str_dt => l_active_start_date,
1004 p_act_end_dt => l_active_end_date)
1005 THEN
1006 fnd_message.set_name('PN', 'PN_GROSS_VALIDATE');
1007 fnd_message.set_token('LOCATION_ID', loc.location_id);
1008 l_error_message := fnd_message.get;
1009 raise INVALID_RECORD;
1010 END IF;
1011
1012 END IF;
1013
1014 put_log('Validating if Assignments exist for making Assignable area Common');
1015 --------------------------------------------------------------------
1016 -- Validating if Assignments exist for making Assignable area Common
1017 --------------------------------------------------------------------
1018 IF ( NVL(loc.common_area_flag,'N') = 'Y' AND
1019 NVL(v_loc_rec.common_area_flag,'N') = 'N' AND
1020 PNP_UTIL_FUNC.get_space_assigned_status(
1021 v_loc_rec.location_id,
1022 l_active_start_date))
1023 THEN
1024 fnd_message.set_name('PN', 'PN_ASSIGNMENTS_EXIST');
1025 fnd_message.set_token('LOCATION_ID', loc.location_id);
1026 l_error_message := fnd_message.get;
1027 raise INVALID_RECORD;
1028 END IF;
1029
1030 ---------------------------------------
1031 -- init assignable area changed flag --
1032 ---------------------------------------
1033 IF NVL(loc.assignable_area,0)
1034 <> NVL(v_loc_rec.assignable_area,0) THEN
1035 l_asgn_area_chng_flag := 'Y';
1036 ELSE
1037 l_asgn_area_chng_flag := 'N';
1038 END IF;
1039
1040 IF (loc.ENTRY_TYPE = 'U') THEN
1041
1042 BEGIN
1043 l_active_end_date := trunc(v_loc_rec.active_end_date); --Used trunc() :Bug 6009957
1044
1045 l_pn_locations_rec.location_id := loc.location_id;
1046 l_pn_locations_rec.ORG_ID := l_org_id;
1047 l_pn_locations_rec.LOCATION_TYPE_LOOKUP_CODE := loc.location_type_lookup_code;
1048 l_pn_locations_rec.SPACE_TYPE_LOOKUP_CODE := loc.space_type_lookup_code;
1049 l_pn_locations_rec.LAST_UPDATE_DATE := sysdate;
1050 l_pn_locations_rec.PARENT_LOCATION_ID := loc.parent_location_id;
1051 l_pn_locations_rec.LEASE_OR_OWNED := loc.lease_or_owned;
1052 l_pn_locations_rec.BUILDING := loc.building;
1053 l_pn_locations_rec.FLOOR := loc.floor;
1054 l_pn_locations_rec.OFFICE := loc.office;
1055 l_pn_locations_rec.MAX_CAPACITY := loc.max_capacity;
1056 l_pn_locations_rec.OPTIMUM_CAPACITY := loc.optimum_capacity;
1057 l_pn_locations_rec.RENTABLE_AREA := loc.rentable_area;
1058 l_pn_locations_rec.USABLE_AREA := loc.usable_area;
1059 l_pn_locations_rec.GROSS_AREA := loc.gross_area;
1060 l_pn_locations_rec.ASSIGNABLE_AREA := loc.assignable_area;
1061 l_pn_locations_rec.COMMON_AREA := loc.common_area;
1062 l_pn_locations_rec.COMMON_AREA_FLAG := loc.common_area_flag;
1063 l_pn_locations_rec.CLASS := loc.class;
1064 l_pn_locations_rec.STATUS_TYPE := loc.status_type;
1065 l_pn_locations_rec.STATUS := 'A';
1066 l_pn_locations_rec.SUITE := loc.suite;
1067 l_pn_locations_rec.ALLOCATE_COST_CENTER_CODE := loc.allocate_cost_center_code;
1068 l_pn_locations_rec.UOM_CODE := loc.uom_code;
1069 l_pn_locations_rec.LAST_UPDATE_LOGIN := nvl(fnd_profile.value('CONC_LOGIN_ID'), 0);
1070 l_pn_locations_rec.LAST_UPDATED_BY := nvl(fnd_profile.value('CONC_USER_ID'), 0);
1071 l_pn_locations_rec.ATTRIBUTE_CATEGORY := loc.attribute_category;
1072 l_pn_locations_rec.ATTRIBUTE1 := loc.attribute1;
1073 l_pn_locations_rec.ATTRIBUTE2 := loc.attribute2;
1074 l_pn_locations_rec.ATTRIBUTE3 := loc.attribute3;
1075 l_pn_locations_rec.ATTRIBUTE4 := loc.attribute4;
1076 l_pn_locations_rec.ATTRIBUTE5 := loc.attribute5;
1077 l_pn_locations_rec.ATTRIBUTE6 := loc.attribute6;
1078 l_pn_locations_rec.ATTRIBUTE7 := loc.attribute7;
1079 l_pn_locations_rec.ATTRIBUTE8 := loc.attribute8;
1080 l_pn_locations_rec.ATTRIBUTE9 := loc.attribute9;
1081 l_pn_locations_rec.ATTRIBUTE10 := loc.attribute10;
1082 l_pn_locations_rec.ATTRIBUTE11 := loc.attribute11;
1083 l_pn_locations_rec.ATTRIBUTE12 := loc.attribute12;
1084 l_pn_locations_rec.ATTRIBUTE13 := loc.attribute13;
1085 l_pn_locations_rec.ATTRIBUTE14 := loc.attribute14;
1086 l_pn_locations_rec.ATTRIBUTE15 := loc.attribute15;
1087 l_pn_locations_rec.REQUEST_ID := nvl(fnd_profile.value('CONC_REQUEST_ID'), 0);
1088 l_pn_locations_rec.PROGRAM_APPLICATION_ID := nvl(fnd_profile.value('CONC_PROGRAM_APPLICATION_ID'), 0);
1089 l_pn_locations_rec.PROGRAM_ID := nvl(fnd_profile.value('CONC_PROGRAM_ID'), 0);
1090 l_pn_locations_rec.PROGRAM_UPDATE_DATE := sysdate ;
1091 l_pn_locations_rec.FUNCTION_TYPE_LOOKUP_CODE := loc.FUNCTION_TYPE_LOOKUP_CODE;
1092 l_pn_locations_rec.LOCATION_ALIAS := loc.Location_Alias;
1093 l_pn_locations_rec.PROPERTY_ID := loc.Property_Id;
1094 l_pn_locations_rec.STANDARD_TYPE_LOOKUP_CODE := loc.STANDARD_TYPE_LOOKUP_CODE;
1095 l_pn_locations_rec.ACTIVE_START_DATE := trunc(NVL(loc.new_active_start_date,
1096 loc.active_start_date));
1097 l_pn_locations_rec.ACTIVE_END_DATE := trunc(NVL(loc.new_active_end_date,
1098 loc.active_end_date));
1099 --Used trunc() :Bug 6009957
1100 l_pn_locations_rec.address_id := v_loc_rec.address_id;
1101 l_pn_locations_rec.source := loc.source;
1102
1103 /* populate the address_rec */
1104
1105 l_pn_addresses_rec.ADDRESS_LINE1 := loc.address_line1;
1106 l_pn_addresses_rec.ADDRESS_LINE2 := loc.address_line2;
1107 l_pn_addresses_rec.ADDRESS_LINE3 := loc.address_line3;
1108 l_pn_addresses_rec.ADDRESS_LINE4 := loc.address_line4;
1109 l_pn_addresses_rec.COUNTY := loc.county;
1110 l_pn_addresses_rec.CITY := loc.city;
1111 l_pn_addresses_rec.STATE := loc.state;
1112 l_pn_addresses_rec.PROVINCE := loc.province;
1113 l_pn_addresses_rec.ZIP_CODE := loc.zip_code;
1114 l_pn_addresses_rec.COUNTRY := loc.country;
1115 l_pn_addresses_rec.ADDRESS_STYLE := loc.address_style;
1116 l_pn_addresses_rec.LAST_UPDATE_DATE := sysdate;
1117 l_pn_addresses_rec.LAST_UPDATED_BY := nvl(fnd_profile.value('USER_ID'), 0);
1118 l_pn_addresses_rec.LAST_UPDATE_LOGIN := nvl(fnd_profile.value('CONC_LOGIN_ID'), 0);
1119 l_pn_addresses_rec.ADDR_ATTRIBUTE_CATEGORY := loc.addr_attribute_category;
1120 l_pn_addresses_rec.ADDR_ATTRIBUTE1 := loc.addr_attribute1;
1121 l_pn_addresses_rec.ADDR_ATTRIBUTE2 := loc.addr_attribute2;
1122 l_pn_addresses_rec.ADDR_ATTRIBUTE3 := loc.addr_attribute3;
1123 l_pn_addresses_rec.ADDR_ATTRIBUTE4 := loc.addr_attribute4;
1124 l_pn_addresses_rec.ADDR_ATTRIBUTE5 := loc.addr_attribute5;
1125 l_pn_addresses_rec.ADDR_ATTRIBUTE6 := loc.addr_attribute6;
1126 l_pn_addresses_rec.ADDR_ATTRIBUTE7 := loc.addr_attribute7;
1127 l_pn_addresses_rec.ADDR_ATTRIBUTE8 := loc.addr_attribute8;
1128 l_pn_addresses_rec.ADDR_ATTRIBUTE9 := loc.addr_attribute9;
1129 l_pn_addresses_rec.ADDR_ATTRIBUTE10 := loc.addr_attribute10;
1130 l_pn_addresses_rec.ADDR_ATTRIBUTE11 := loc.addr_attribute11;
1131 l_pn_addresses_rec.ADDR_ATTRIBUTE12 := loc.addr_attribute12;
1132 l_pn_addresses_rec.ADDR_ATTRIBUTE13 := loc.addr_attribute13;
1133 l_pn_addresses_rec.ADDR_ATTRIBUTE14 := loc.addr_attribute14;
1134 l_pn_addresses_rec.ADDR_ATTRIBUTE15 := loc.addr_attribute15;
1135
1136 put_log( 'U: Just before Correct/Update');
1137
1138 --Used trunc() :Bug 6009957
1139 PNT_LOCATIONS_PKG.correct_update_row
1140 ( p_pn_locations_rec => l_pn_locations_rec,
1141 p_pn_addresses_rec => l_pn_addresses_rec,
1142 p_change_mode => nvl(loc.change_mode, 'CORRECT'),
1143 p_as_of_date => loc.change_date,
1144 p_active_start_date_old => trunc(loc.active_start_date),
1145 p_active_end_date_old => trunc(v_loc_rec.active_end_date),
1146 p_assgn_area_chgd_flag => l_asgn_area_chng_flag,
1147 x_return_status => l_returnstatus,
1148 x_return_message => l_return_message
1149 );
1150 put_log( 'U: Just after Correct/Update');
1151
1152 IF NOT ( l_returnStatus = FND_API.G_RET_STS_SUCCESS) THEN
1153 l_error_message := fnd_message.get;
1154 pnp_debug_pkg.put_log_msg(l_return_message);
1155 RAISE INVALID_RECORD;
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN No_Data_Found THEN
1160 fnd_message.set_name('PN', 'PN_CAFM_LOC_REC_NOT_FOUND_UPD');
1161 fnd_message.set_token('LOCATION_ID', loc.location_id);
1162 l_error_message := fnd_message.get;
1163 RAISE INVALID_RECORD;
1164
1165 END;
1166
1167 ELSIF (loc.ENTRY_TYPE = 'R') THEN
1168
1169 BEGIN
1170 --------------------------------------
1171 -- Update Data in PN_LOCATIONS
1172 --------------------------------------
1173 l_active_end_date := trunc(v_loc_rec.active_end_date); --Used trunc() :Bug 6009957
1174
1175 put_log('R: Building the locations record');
1176 l_pn_locations_rec.location_id := loc.location_id;
1177 l_pn_locations_rec.ORG_ID := l_org_id;
1178 l_pn_locations_rec.LOCATION_CODE := nvl( loc.location_code, v_loc_rec.location_code);
1179 l_pn_locations_rec.LOCATION_TYPE_LOOKUP_CODE :=
1180 nvl(loc.location_type_lookup_code, v_loc_rec.location_type_lookup_code);
1181 l_pn_locations_rec.SPACE_TYPE_LOOKUP_CODE :=
1182 nvl( loc.space_type_lookup_code, v_loc_rec.space_type_lookup_code);
1183 l_pn_locations_rec.LAST_UPDATE_DATE := sysdate;
1184 l_pn_locations_rec.PARENT_LOCATION_ID :=
1185 nvl( loc.parent_location_id, v_loc_rec.parent_location_id);
1186 l_pn_locations_rec.LEASE_OR_OWNED := nvl( loc.lease_or_owned, v_loc_rec.lease_or_owned);
1187 l_pn_locations_rec.BUILDING := nvl( loc.building, v_loc_rec.building);
1188 l_pn_locations_rec.FLOOR := nvl( loc.floor, v_loc_rec.floor);
1189 l_pn_locations_rec.OFFICE := nvl( loc.office, v_loc_rec.office);
1190 l_pn_locations_rec.MAX_CAPACITY := nvl( loc.max_capacity, v_loc_rec.max_capacity);
1191 l_pn_locations_rec.OPTIMUM_CAPACITY := nvl( loc.optimum_capacity, v_loc_rec.optimum_capacity);
1192 l_pn_locations_rec.RENTABLE_AREA := nvl( loc.rentable_area, v_loc_rec.rentable_area);
1193 l_pn_locations_rec.USABLE_AREA := nvl( loc.usable_area, v_loc_rec.usable_area);
1194 l_pn_locations_rec.GROSS_AREA := nvl( loc.gross_area, v_loc_rec.gross_area);
1195 l_pn_locations_rec.ASSIGNABLE_AREA :=
1196 nvl( loc.assignable_area, v_loc_rec.assignable_area);
1197 l_pn_locations_rec.COMMON_AREA := nvl( loc.common_area, v_loc_rec.common_area);
1198 l_pn_locations_rec.COMMON_AREA_FLAG :=
1199 nvl( loc.common_area_flag, v_loc_rec.common_area_flag);
1200 l_pn_locations_rec.CLASS := nvl( loc.class, v_loc_rec.class);
1201 l_pn_locations_rec.STATUS_TYPE := nvl( loc.status_type, v_loc_rec.status_type);
1202 l_pn_locations_rec.STATUS := v_loc_rec.status;
1203 l_pn_locations_rec.SUITE := nvl( loc.suite, v_loc_rec.suite);
1204 l_pn_locations_rec.ALLOCATE_COST_CENTER_CODE :=
1205 nvl( loc.allocate_cost_center_code, v_loc_rec.allocate_cost_center_code);
1206 l_pn_locations_rec.UOM_CODE := nvl( loc.uom_code, v_loc_rec.uom_code);
1207 l_pn_locations_rec.LAST_UPDATE_LOGIN := nvl( fnd_profile.value('CONC_LOGIN_ID'), 0);
1208 l_pn_locations_rec.LAST_UPDATED_BY := nvl( fnd_profile.value('CONC_USER_ID'), 0);
1209 l_pn_locations_rec.ATTRIBUTE_CATEGORY :=
1210 nvl( loc.attribute_category, v_loc_rec.attribute_category);
1211 l_pn_locations_rec.ATTRIBUTE1 := nvl( loc.attribute1, v_loc_rec.attribute1);
1212 l_pn_locations_rec.ATTRIBUTE2 := nvl( loc.attribute2, v_loc_rec.attribute2);
1213 l_pn_locations_rec.ATTRIBUTE3 := nvl( loc.attribute3, v_loc_rec.attribute3);
1214 l_pn_locations_rec.ATTRIBUTE4 := nvl( loc.attribute4, v_loc_rec.attribute4);
1215 l_pn_locations_rec.ATTRIBUTE5 := nvl( loc.attribute5, v_loc_rec.attribute5);
1216 l_pn_locations_rec.ATTRIBUTE6 := nvl( loc.attribute6, v_loc_rec.attribute6);
1217 l_pn_locations_rec.ATTRIBUTE7 := nvl( loc.attribute7, v_loc_rec.attribute7);
1218 l_pn_locations_rec.ATTRIBUTE8 := nvl( loc.attribute8, v_loc_rec.attribute8);
1219 l_pn_locations_rec.ATTRIBUTE9 := nvl( loc.attribute9, v_loc_rec.attribute9);
1220 l_pn_locations_rec.ATTRIBUTE10 := nvl( loc.attribute10, v_loc_rec.attribute10);
1221 l_pn_locations_rec.ATTRIBUTE11 := nvl( loc.attribute11, v_loc_rec.attribute11);
1222 l_pn_locations_rec.ATTRIBUTE12 := nvl( loc.attribute12, v_loc_rec.attribute12);
1223 l_pn_locations_rec.ATTRIBUTE13 := nvl( loc.attribute13, v_loc_rec.attribute13);
1224 l_pn_locations_rec.ATTRIBUTE14 := nvl( loc.attribute14, v_loc_rec.attribute14);
1225 l_pn_locations_rec.ATTRIBUTE15 := nvl( loc.attribute15, v_loc_rec.attribute15);
1226 l_pn_locations_rec.REQUEST_ID := nvl( fnd_profile.value('CONC_REQUEST_ID'), 0);
1227 l_pn_locations_rec.PROGRAM_APPLICATION_ID :=
1228 nvl( fnd_profile.value('CONC_PROGRAM_APPLICATION_ID'), 0);
1229 l_pn_locations_rec.PROGRAM_ID := nvl( fnd_profile.value('CONC_PROGRAM_ID'), 0);
1230 l_pn_locations_rec.PROGRAM_UPDATE_DATE:= sysdate;
1231 l_pn_locations_rec.LOCATION_ALIAS :=
1232 nvl( loc.location_alias, v_loc_rec.location_alias);
1233 l_pn_locations_rec.PROPERTY_ID := nvl( loc.property_id, v_loc_rec.property_id);
1234
1235 l_pn_locations_rec.FUNCTION_TYPE_LOOKUP_CODE :=
1236 nvl( loc.function_type_lookup_code, v_loc_rec.function_type_lookup_code);
1237 l_pn_locations_rec.STANDARD_TYPE_LOOKUP_CODE :=
1238 nvl( loc.standard_type_lookup_code, v_loc_rec.standard_type_lookup_code);
1239 l_pn_locations_rec.active_start_date :=
1240 trunc(nvl(loc.new_active_start_date, v_loc_rec.active_start_date)); --Used trunc() :Bug 6009957
1241 l_pn_locations_rec.active_end_date :=
1242 trunc(nvl(loc.new_active_end_date,v_loc_rec.active_end_date)); --Used trunc() :Bug 6009957
1243 l_pn_locations_rec .address_id := nvl(v_loc_rec.address_id,l_Address_id);
1244 l_pn_locations_rec.source := nvl(loc.source,v_loc_rec.source);
1245
1246 l_pn_addresses_rec.ADDRESS_LINE1 := nvl( loc.address_line1, v_addr_rec.address_line1);
1247 l_pn_addresses_rec.ADDRESS_LINE2 := nvl( loc.address_line2, v_addr_rec.address_line2);
1248 l_pn_addresses_rec.ADDRESS_LINE3 := nvl( loc.address_line3, v_addr_rec.address_line3);
1249 l_pn_addresses_rec.ADDRESS_LINE4 := nvl( loc.address_line4, v_addr_rec.address_line4);
1250 l_pn_addresses_rec.COUNTY := nvl( loc.county, v_addr_rec.county);
1251 l_pn_addresses_rec.CITY := nvl( loc.city, v_addr_rec.city);
1252 l_pn_addresses_rec.STATE := nvl( loc.state, v_addr_rec.state);
1253 l_pn_addresses_rec.PROVINCE := nvl( loc.province, v_addr_rec.province);
1254 l_pn_addresses_rec.ZIP_CODE := nvl( loc.zip_code, v_addr_rec.zip_code);
1255 l_pn_addresses_rec.COUNTRY := nvl( loc.country, v_addr_rec.country);
1256 l_pn_addresses_rec.ADDRESS_STYLE := nvl( loc.address_style, v_addr_rec.address_style);
1257 l_pn_addresses_rec.LAST_UPDATE_DATE := sysdate;
1258 l_pn_addresses_rec.LAST_UPDATED_BY := nvl(fnd_profile.value('USER_ID'), 0);
1259 l_pn_addresses_rec.LAST_UPDATE_LOGIN := nvl(fnd_profile.value('CONC_LOGIN_ID'), 0);
1260 l_pn_addresses_rec.ADDR_ATTRIBUTE_CATEGORY
1261 := nvl(loc.addr_attribute_category,
1262 v_addr_rec.addr_attribute_category);
1263 l_pn_addresses_rec.ADDR_ATTRIBUTE1 := nvl(loc.addr_attribute1,v_addr_rec.addr_attribute1);
1264 l_pn_addresses_rec.ADDR_ATTRIBUTE2 := nvl(loc.addr_attribute2,v_addr_rec.addr_attribute2);
1265 l_pn_addresses_rec.ADDR_ATTRIBUTE3 := nvl(loc.addr_attribute3,v_addr_rec.addr_attribute3);
1266 l_pn_addresses_rec.ADDR_ATTRIBUTE4 := nvl(loc.addr_attribute4,v_addr_rec.addr_attribute4);
1267 l_pn_addresses_rec.ADDR_ATTRIBUTE5 := nvl(loc.addr_attribute5,v_addr_rec.addr_attribute5);
1268 l_pn_addresses_rec.ADDR_ATTRIBUTE6 := nvl(loc.addr_attribute6,v_addr_rec.addr_attribute6);
1269 l_pn_addresses_rec.ADDR_ATTRIBUTE7 := nvl(loc.addr_attribute7,v_addr_rec.addr_attribute7);
1270 l_pn_addresses_rec.ADDR_ATTRIBUTE8 := nvl(loc.addr_attribute8,v_addr_rec.addr_attribute8);
1271 l_pn_addresses_rec.ADDR_ATTRIBUTE9 := nvl(loc.addr_attribute9,v_addr_rec.addr_attribute9);
1272 l_pn_addresses_rec.ADDR_ATTRIBUTE10 := nvl(loc.addr_attribute10,v_addr_rec.addr_attribute10);
1273 l_pn_addresses_rec.ADDR_ATTRIBUTE11 := nvl(loc.addr_attribute11,v_addr_rec.addr_attribute11);
1274 l_pn_addresses_rec.ADDR_ATTRIBUTE12 := nvl(loc.addr_attribute12,v_addr_rec.addr_attribute12);
1275 l_pn_addresses_rec.ADDR_ATTRIBUTE13 := nvl(loc.addr_attribute13,v_addr_rec.addr_attribute13);
1276 l_pn_addresses_rec.ADDR_ATTRIBUTE14 := nvl(loc.addr_attribute14,v_addr_rec.addr_attribute14);
1277 l_pn_addresses_rec.ADDR_ATTRIBUTE15 := nvl(loc.addr_attribute15,v_addr_rec.addr_attribute15);
1278
1279 -------------------------------------------------------------------
1280 -- Validate Assignable_Area / Common Area is not null for location types 'OFFICE','SECTION'
1281 -------------------------------------------------------------------
1282
1283 put_log('Validate Common_Area and Assignable_Area');
1284
1285 if (l_pn_locations_rec.assignable_area is null and
1286 l_pn_locations_rec.location_type_lookup_code in ('OFFICE','SECTION') and
1287 nvl(l_pn_locations_rec.common_area_flag,'N') = 'N') then
1288 fnd_message.set_name('PN','PN_CAFM_ASSIGNABLE_REQ');
1289 fnd_message.set_token('LOCATION_ID', l_pn_locations_rec.location_id);
1290 l_error_message := fnd_message.get;
1291 raise INVALID_RECORD;
1292 end if;
1293
1294 if (l_pn_locations_rec.common_area is null and
1295 l_pn_locations_rec.location_type_lookup_code in ('OFFICE','SECTION') and
1296 l_pn_locations_rec.common_area_flag = 'Y') then
1297 fnd_message.set_name('PN','PN_CAFM_COMMON_AREA_REQ');
1298 fnd_message.set_token('LOCATION_ID', l_pn_locations_rec.location_id);
1299 l_error_message := fnd_message.get;
1300 raise INVALID_RECORD;
1301 end if;
1302
1303 if (l_pn_locations_rec.Common_Area is not null and
1304 l_pn_locations_rec.Assignable_Area is not null and
1305 l_pn_locations_rec.location_type_lookup_code in ('OFFICE','SECTION'))
1306 then
1307 if l_pn_locations_rec.common_area_flag = 'Y' then
1308 l_pn_locations_rec.Assignable_Area := null;
1309 elsif nvl(l_pn_locations_rec.common_area_flag,'N') = 'N' then
1310 l_pn_locations_rec.Common_Area := null;
1311 end if;
1312 end if;
1313
1314 put_log( 'R: Just before Correct/Update');
1315 PNT_LOCATIONS_PKG.correct_update_row
1316 ( p_pn_locations_rec => l_pn_locations_rec,
1317 p_pn_addresses_rec => l_pn_addresses_rec,
1318 p_change_mode => nvl(loc.change_mode, 'CORRECT'),
1319 p_as_of_date => loc.change_date,
1320 p_active_start_date_old => trunc(loc.active_start_date), --Used trunc() :Bug 6009957
1321 p_active_end_date_old => trunc(v_loc_rec.active_end_date), --Used trunc() :Bug 6009957
1322 p_assgn_area_chgd_flag => l_asgn_area_chng_flag,
1323 x_return_status => l_returnstatus,
1324 x_return_message => l_return_message
1325 );
1326 put_log( 'R: Just before Correct/Update');
1327
1328 IF NOT ( l_returnStatus = FND_API.G_RET_STS_SUCCESS) THEN
1329 put_log('R:Error in correctupdate_row ' || l_return_message);
1330 put_log(l_return_message);
1331 l_error_message := fnd_message.get;
1332 RAISE INVALID_RECORD;
1333 END IF;
1334
1335 EXCEPTION
1336 WHEN No_Data_Found THEN
1337 fnd_message.set_name('PN', 'PN_CAFM_LOC_REC_NOT_FOUND_UPD');
1338 fnd_message.set_token('LOCATION_ID', loc.location_id);
1339 l_error_message := fnd_message.get;
1340 RAISE INVALID_RECORD;
1341
1342 END;
1343
1344 END IF; -- 'U', 'R'
1345
1346 END IF; -- loc.ENTRY_TYPE
1347
1348 ------------------------------------------------
1349 -- Set PN_LOCATIONS_ITF.transferred_to_pn = 'Y'
1350 ------------------------------------------------
1351 put_log('Update ITF set transfer = Y');
1352 UPDATE pn_locations_itf
1353 SET transferred_to_pn = 'Y',
1354 error_message = NULL
1355 WHERE rowid = loc.rowid;
1356
1357 -------------------------------------------
1358 -- batch commit after every 1000 records --
1359 -------------------------------------------
1360 IF (NVL(l_total,0) > 0) AND
1361 (MOD(l_total, 1000) = 0) THEN
1362 COMMIT;
1363 l_total_for_commit := 0; -- Bug 6670882
1364 END IF;
1365
1366 EXCEPTION
1367 WHEN INVALID_RECORD THEN
1368 ROLLBACK TO S1;
1369 l_fail := l_fail + 1;
1370 -- Update ERROR_MESSAGE
1371 UPDATE pn_locations_itf
1372 SET error_message = substr(l_error_message, 1, 240)
1373 WHERE rowid = loc.rowid;
1374 -- Spool to Conc Log
1375 put_line(l_error_message);
1376
1377 errbuf := l_error_message;
1378 retcode := '2';
1379
1380 WHEN DELETE_RECORD THEN
1381 NULL;
1382
1383 WHEN OTHERS THEN
1384 ROLLBACK TO S1;
1385 l_fail := l_fail + 1;
1386 l_error_message := substr(sqlerrm,1,250);
1387 UPDATE pn_locations_itf
1388 SET error_message = substr(l_error_message, 1, 240)
1389 WHERE rowid = loc.rowid;
1390 errbuf := l_error_message;
1391 retcode := '2';
1392 APP_EXCEPTION.raise_exception;
1393
1394 END; -- end of begin that started in the FOR loop
1395
1396 END LOOP; -- End loop for loccursor
1397
1398 IF l_total_for_commit > 0 THEN -- Bug 6670882
1399 COMMIT;
1400 END IF;
1401
1402 IF (l_total = 0) THEN
1403 fnd_message.set_name ('PN', 'PN_CAFM_NO_LOC_REC_FOUND');
1404 errbuf := fnd_message.get;
1405 retcode := '2';
1406 put_line(errbuf);
1407
1408 ELSE
1409 l_succ := l_total - l_fail;
1410
1411 Put_Log('
1412 =============== Summary ===============');
1413
1414 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_SUCCESS');
1415 fnd_message.set_token('SUCCESS', l_succ);
1416 put_line(fnd_message.get);
1417
1418 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_FAILURE');
1419 fnd_message.set_token('FAILURE', l_fail);
1420 put_line(fnd_message.get);
1421
1422 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_TOTAL');
1423 fnd_message.set_token('TOTAL', l_total);
1424 put_line(fnd_message.get);
1425
1426 END IF;
1427
1428 PNP_DEBUG_PKG.disable_file_debug;
1429
1430 EXCEPTION
1431 WHEN NO_DATA_FOUND THEN
1432 put_line('EXCEPTION: NO_DATA_FOUND');
1433 fnd_message.set_name ('PN', 'PN_NO_LOC_DATA_FOUND');
1434 errbuf := l_error_message;
1435 retcode := '2';
1436 put_line(errbuf);
1437 APP_EXCEPTION.raise_exception;
1438 PNP_DEBUG_PKG.disable_file_debug;
1439
1440 WHEN OTHERS THEN
1441 put_line('EXCEPTION: OTHERS');
1442 If l_error_message is null then
1443 l_error_message := fnd_message.get;
1444 end if;
1445 pnp_debug_pkg.put_log_msg(l_error_message);
1446 errbuf := l_error_message;
1447 retcode := '2';
1448 put_line(errbuf);
1449 APP_EXCEPTION.raise_exception;
1450 PNP_DEBUG_PKG.disable_file_debug;
1451
1452 END LOCATIONS_ITF;
1453
1454 -------------------------------------------------------------------------------
1455 -- NAME : Is_Id_Code_Valid
1456 -- DESCRIPTION : Checks if the Location Id/Code combination is Valid.
1457 -- NOTES : Called by Locations_Itf Procedure
1458 -- ARGUMENTS : IN: p_loc_id
1459 -- p_loc_code
1460 -- REFERENCE : PN_COMMON.debug()
1461 -- HISTORY :
1462 -- 24-JAN-03 Kiran o Created
1463 -- 01-APR-05 piagrawa o Modified the select statements to retrieve values
1464 -- from _ALL tables
1465 -- 14-JUL-05 piagrawa o Bug 4284035 - also changed the signature
1466 -------------------------------------------------------------------------------
1467
1468 Function Is_Id_Code_Valid (
1469 p_Loc_Id NUMBER,
1470 p_Loc_Code VARCHAR2,
1471 p_org_id NUMBER
1472 )
1473
1474 Return Boolean IS
1475
1476 CURSOR locCodeCur IS
1477
1478 SELECT loc.LOCATION_CODE
1479 FROM PN_LOCATIONS_ALL loc
1480 WHERE loc.LOCATION_ID = p_Loc_Id;
1481
1482 CURSOR locIdCur is
1483 SELECT loc.LOCATION_ID
1484 FROM PN_LOCATIONS_ALL loc
1485 WHERE loc.LOCATION_CODE = p_Loc_Code
1486 AND org_id = p_org_id;
1487
1488 BEGIN
1489
1490 FOR code IN locCodeCur LOOP
1491 if code.LOCATION_CODE <> p_Loc_Code then
1492 RETURN FALSE;
1493 end if;
1494 END LOOP;
1495
1496 FOR id IN locIdCur LOOP
1497 if id.LOCATION_ID <> p_Loc_Id then
1498 RETURN FALSE;
1499 end if;
1500 END LOOP;
1501
1502 RETURN TRUE;
1503
1504 END Is_Id_Code_Valid ;
1505
1506
1507 /*============================================================================+
1508 | FUNCTION
1509 | Get_Location_Type
1510 |
1511 | DESCRIPTION
1512 | Given a Location_Id, Returns the Location_Type (BUILDING/FLOOR/OFFICE)
1513 |
1514 | SCOPE - PRIVATE
1515 |
1516 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1517 |
1518 | ARGUMENTS: Listed Very Clearly Below
1519 |
1520 | NOTES:
1521 | Called by Locations_Itf Procedure
1522 |
1523 | MODIFICATION HISTORY
1524 | 1999 Naga Vijayapuram o Created
1525 | 17-MAY-02 Kiran Hegde o Location_Type_lookup_Code is now validated
1526 | only from pn_locations
1527 | 01-APR-05 piagrawa o Modified the select statements to retrieve values
1528 | from _ALL tables
1529 +===========================================================================*/
1530
1531 Function Get_Location_Type ( p_location_id Number ) Return VarChar2 is
1532
1533 l_lookup_type varchar2(30);
1534
1535 BEGIN
1536
1537 Select distinct location_type_lookup_code
1538 into l_lookup_type
1539 from PN_LOCATIONS_ALL
1540 where location_id = p_location_id ;
1541
1542 Return l_lookup_type ;
1543
1544 EXCEPTION
1545
1546 When No_Data_Found Then
1547
1548 Return 'UNKNOWN' ;
1549
1550 When Others Then
1551 Raise;
1552
1553 END Get_Location_Type;
1554
1555
1556 /*===========================================================================+
1557 | FUNCTION
1558 | Exists_Property_Id
1559 |
1560 | DESCRIPTION
1561 | Checks whether the property_id is valid - from pn_properties
1562 |
1563 | SCOPE - PRIVATE
1564 |
1565 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1566 |
1567 | ARGUMENTS:
1568 | IN: p_property_id NUMBER
1569 |
1570 | NOTES:
1571 | Called by Locations_Itf Procedure
1572 |
1573 | MODIFICATION HISTORY
1574 | 23-APR-02 Kiran Hegde o Created -
1575 | Bug#2324687 - Validates if the Property_Id in the
1576 | table Pn_Locations_Itf exists in Pn_Properties
1577 | 01-APR-05 piagrawa o Modified the select statements to retrieve values
1578 | from _ALL tables
1579 +===========================================================================*/
1580
1581 Function Exists_Property_Id (
1582 p_property_id NUMBER
1583 )
1584 Return Boolean IS
1585
1586 l_number Number;
1587
1588 BEGIN
1589
1590 if ( p_property_id IS NOT NULL ) then
1591
1592 Select 1
1593 Into l_number
1594 From Pn_Properties_all
1595 Where Property_Id = p_property_id ;
1596 end if;
1597
1598 Return True;
1599
1600 EXCEPTION
1601
1602 When No_Data_Found Then
1603 Return False;
1604
1605 When Others Then
1606 Raise;
1607
1608 END Exists_Property_Id ;
1609
1610
1611
1612
1613 /*===========================================================================+
1614 | PROCEDURE
1615 | SPACE_ALLOCATIONS_ITF
1616 |
1617 | DESCRIPTION
1618 | Handles Import of Space Allocations Data
1619 |
1620 | SCOPE - PRIVATE
1621 |
1622 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1623 |
1624 | ARGUMENTS:
1625 | IN: p_Batch_Name
1626 | OUT: Errbuf
1627 | RetCode
1628 |
1629 | NOTES:
1630 | Called by IMPORT_CAD Procedure Above
1631 |
1632 | MODIFICATION HISTORY
1633 | ??-???-98 Naga o Created
1634 | ??-???-99 Naga o Included Validations
1635 | 15-MAY-02 Kiran o ??
1636 | 20-Nov-03 Daniel o Modified SELECT to go after
1637 | _ALL tables for PN_SPACE_ASSIGN_EMP
1638 | Fix for bug # 3272712
1639 | 26-MAY-04 abanerje o Added condition to check for p_assignable_area
1640 | is -99. In this case error out indicating that
1641 | the location is a common space.
1642 | Bug #3598315
1643 | 16-JUN-04 ftanudja o Created variable tlempinfo.
1644 | o Removed all references to pn_space_assign_emp_pkg.
1645 | tlempinfo.
1646 | o Added validation for allocated_area for manual
1647 | spc assignments.
1648 | o Fixed id validation for entry type 'U','R'.
1649 | o #3649373
1650 | 01-APR-05 piagrawa o Modified the signature to include org_id,
1651 | also in INSERT_ROW call passed
1652 | the value of p_org_id in place of
1653 | fnd_profile.value('ORG_ID')
1654 | 19-JUL-05 sdm o Passed SOURCE from PN_EMP_ASSIGN_SPACE_ITF to
1655 | Procedure PN_SPACE_ASSIGN_EMP.INSERT_ROW and
1656 | Procedure PN_SPACE_ASSIGN_EMP.UPDATE_ROW
1657 | 23-NOV-05 Hareesha o Modified get_profile_value to include org_id as
1658 | parameter.
1659 | 01-DEC-05 pikhar o passed org_id in pnp_util_func.get_cc_code and
1660 | o pnp_util_func.valid_cost_center
1661 | 31-Aug-06 Prabhakaro Bug #5449595 Validated project_id and task_id
1662 | and if valid,Added project_id and task_id
1663 | in insert_row and upate_row
1664 | 11-OCT-06 acprakas o Bug#5587012. Passed location_id to procedure
1665 | PNP_UTIL_FUNC.Validate_date_for_assignments instead
1666 | of EMP_SPACE_ASSIGN_ID.
1667 | 08-NOV-06 lbala o Bug#5636783.Added Commit prior to END LOOP and
1668 | SAVEPOINT S2.Added code in exception block for WHEN
1669 | OTHERS to update errored record count.
1670 | 24-AUG-08 RKARTHA o Bug#6826510 - Round off the allocated_area_pct to
1671 | two decimal places.
1672 +===========================================================================*/
1673
1674 PROCEDURE space_allocations_itf (
1675
1676 p_batch_name VARCHAR2,
1677 p_org_id NUMBER,
1678 errbuf out NOCOPY VarChar2,
1679 retcode out NOCOPY VarChar2
1680
1681 ) IS
1682
1683 l_succ Number Default 0;
1684 l_fail Number Default 0;
1685 l_total Number Default 0;
1686
1687 CURSOR spacecur IS
1688 SELECT spc.*, spc.rowid
1689 FROM pn_emp_space_assign_itf spc
1690 WHERE spc.batch_name = p_batch_name
1691 AND spc.transferred_to_pn is null;
1692
1693 tlempinfo pn_space_assign_emp_all%ROWTYPE;
1694
1695 l_error_message VarChar2(512);
1696
1697 l_assignable_area Number;
1698 l_old_allocated_area Number;
1699 l_new_allocated_area Number;
1700 l_available_vacant_area Boolean;
1701 l_future Varchar2(1) := 'N';
1702 l_allocated_area_pct Number;
1703 l_rowid_dummy Varchar2(30);
1704 l_changed_start_date Date;
1705 l_emp_assign_start_date Date;
1706 l_emp_exists Number := 0;
1707
1708 INVALID_RECORD EXCEPTION;
1709 l_return_status VARCHAR2(50);
1710 l_return_message VARCHAR2(2000);
1711 l_org_id NUMBER;
1712 l_task_valid BOOLEAN;
1713 l_project_valid BOOLEAN;
1714
1715 CURSOR check_project_valid( p_project_id NUMBER) IS
1716 SELECT project_id
1717 FROM pa_projects_all proj
1718 WHERE proj.project_id = p_project_id
1719 AND proj.org_id = l_org_id
1720 AND NVL(proj.template_flag,'N') <>'Y';
1721
1722 CURSOR check_task_valid( p_task_id NUMBER,p_project_id NUMBER) IS
1723 SELECT task_id
1724 FROM pa_tasks task
1725 WHERE task.project_id = p_project_id
1726 AND task.task_id = p_task_id;
1727
1728 BEGIN
1729
1730 IF pn_mo_cache_utils.is_MOAC_enabled AND p_org_id IS NULL THEN
1731 l_org_id := pn_mo_cache_utils.get_current_org_id;
1732 ELSE
1733 l_org_id := p_org_id;
1734 END IF;
1735
1736 retcode := '0';
1737
1738 FOR space in spacecur LOOP
1739
1740 l_total := l_total + 1;
1741
1742 BEGIN
1743
1744 Put_Log('
1745 =============== Record #: ' || l_total || ' ===============');
1746
1747 --------------------
1748 -- Set save point --
1749 --------------------
1750 put_log('Setting the save point');
1751 SAVEPOINT S2;
1752
1753
1754 put_log('Validate Entry Types');
1755 --------------------------------------------------------------------
1756 -- Validate Entry Types
1757 -- Note that 'D' is no more a valid entry for space_allocations_itf.
1758 --------------------------------------------------------------------
1759 if (space.ENTRY_TYPE not in ('A', 'U', 'R')) then
1760 fnd_message.set_name('PN', 'PN_CAFM_SPACE_ENTRY_TYPE');
1761 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1762 l_error_message := fnd_message.get;
1763 raise INVALID_RECORD;
1764 end if;
1765
1766 /*--------------------------------------------------------------
1767 -- NOTE: If we are here, it means we are handling INSERT/UPDATE
1768 -- get the old record if this is an update/replace --
1769 -- Using variables and Row Handlers from PN_SPACE_ASSIGN_EMP_PKG
1770 -- for insert and update into pn_space_assign_emp.
1771 --------------------------------------------------------------*/
1772
1773 put_log( 'Validating emp_space_assign_id and getting the old record for UPDATE/REPLACE' );
1774
1775 if ( space.entry_type IN ('U', 'R') ) then
1776
1777 IF space.emp_space_assign_id IS NOT NULL THEN
1778 SELECT *
1779 INTO tlempinfo
1780 FROM pn_space_assign_emp_all
1781 WHERE EMP_SPACE_ASSIGN_ID = space.EMP_SPACE_ASSIGN_ID;
1782 END IF;
1783
1784 IF tlempinfo.emp_space_assign_id IS NULL THEN
1785 put_log(' Provide a valid space_allocation_id');
1786 raise INVALID_RECORD;
1787 end if;
1788
1789 end if;
1790
1791 put_log('Validate Source');
1792 --------------------------------------
1793 -- Validate SOURCE
1794 --------------------------------------
1795 if (NOT PNP_UTIL_FUNC.valid_lookup_code( 'PN_SOURCE', space.SOURCE)) then
1796 fnd_message.set_name('PN', 'PN_CAFM_SPACE_SOURCE');
1797 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1798 l_error_message := fnd_message.get;
1799 raise INVALID_RECORD;
1800 end if;
1801
1802 put_log('Validate Location_Id');
1803 --------------------------------------
1804 -- Validate LOCATION_ID
1805 --------------------------------------
1806 if (NOT PNP_UTIL_FUNC.valid_location(space.LOCATION_ID)) then
1807 fnd_message.set_name('PN', 'PN_CAFM_SPACE_INVALID_LOCATION');
1808 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1809 l_error_message := fnd_message.get;
1810 raise INVALID_RECORD;
1811 end if;
1812
1813 /*-- For PERSON_ID, COST_CENTER_CODE validations,
1814 there are 4 possible situations -
1815
1816 1) Both PERSON_ID and COST_CENTER_CODE are NULL
1817 -- Inform that one of them is needed.
1818
1819 2) Both PERSON_ID and COST_CENTER_CODE are NOT NULL
1820 -- Validate combination
1821
1822 3) Only PERSON_ID is present
1823 -- Validate PERSON_ID and generate COST_CENTER_CODE
1824
1825 4) Only COST_CENTER_CODE is present
1826 -- Validate it. --*/
1827
1828 put_log('Validate Employee - Cost_Center - Situation 1');
1829 --------------------------------------
1830 -- Situation (1) above.
1831 --------------------------------------
1832 if (space.employee_id is NULL) and
1833 (space.COST_CENTER_CODE is NULL) then
1834 fnd_message.set_name('PN', 'PN_CAFM_SPACE_EMP_ID_CC_CODE_1');
1835 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1836 l_error_message := fnd_message.get;
1837 raise INVALID_RECORD;
1838 end if;
1839
1840
1841 put_log('Validate Employee - Cost_Center - Situation 2');
1842 --------------------------------------
1843 -- Situation (2) above.
1844 --------------------------------------
1845 if (space.employee_id is NOT NULL) and
1846 (space.COST_CENTER_CODE is NOT NULL) then
1847
1848 if (NOT PNP_UTIL_FUNC.valid_employee(space.employee_id)) then
1849 fnd_message.set_name('PN', 'PN_CAFM_SPACE_EMP_ID_CC_CODE3B');
1850 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1851 fnd_message.set_token('PERSON_ID', space.employee_id);
1852 l_error_message := fnd_message.get;
1853 end if;
1854
1855 if (NOT PNP_UTIL_FUNC.valid_cost_center(space.COST_CENTER_CODE,l_org_id)) then
1856 fnd_message.set_name('PN', 'PN_CAFM_SPACE_EMP_ID_CC_CODE_4');
1857 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1858 fnd_message.set_token('COST_CENTER_CODE', space.cost_center_code);
1859 l_error_message := fnd_message.get;
1860 raise INVALID_RECORD;
1861 end if;
1862
1863
1864 end if;
1865
1866 put_log('Validate Employee - Cost_Center - Situation 3');
1867 --------------------------------------
1868 -- Situation (3) above.
1869 --------------------------------------
1870 if (space.employee_id is NOT NULL) and
1871 (space.COST_CENTER_CODE is NULL) then
1872 if (PNP_UTIL_FUNC.valid_employee(space.employee_id)) then
1873 space.COST_CENTER_CODE := PNP_UTIL_FUNC.get_cc_code(space.employee_id,l_org_id);
1874 Put_Log('Cost_Center_Code of Employee: ' || space.COST_CENTER_CODE);
1875 if (space.COST_CENTER_CODE is NULL) then
1876 fnd_message.set_name('PN', 'PN_CAFM_SPACE_EMP_ID_CC_CODE3A');
1877 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1878 fnd_message.set_token('PERSON_ID', space.employee_id);
1879 l_error_message := fnd_message.get;
1880 raise INVALID_RECORD;
1881 end if;
1882 else -- Invalid Employee --
1883 fnd_message.set_name('PN', 'PN_CAFM_SPACE_EMP_ID_CC_CODE3B');
1884 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1885 fnd_message.set_token('PERSON_ID', space.employee_id);
1886 l_error_message := fnd_message.get;
1887 raise INVALID_RECORD;
1888 end if;
1889 end if;
1890
1891 put_log('Validate Employee - Cost_Center - Situation 4');
1892 --------------------------------------
1893 -- Situation (4) above.
1894 --------------------------------------
1895 if (space.employee_id is NULL) and
1896 ( space.COST_CENTER_CODE is NOT NULL) then
1897 if (NOT PNP_UTIL_FUNC.valid_cost_center(space.COST_CENTER_CODE,l_org_id)) then
1898 fnd_message.set_name('PN', 'PN_CAFM_SPACE_EMP_ID_CC_CODE_4');
1899 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1900 fnd_message.set_token('COST_CENTER_CODE', space.cost_center_code);
1901 l_error_message := fnd_message.get;
1902 raise INVALID_RECORD;
1903 end if;
1904 end if;
1905
1906 --------------------------------------------------------
1907 -- Check if Start Date is NULL
1908 --------------------------------------------------------
1909 If ( space.EMP_ASSIGN_START_DATE IS NULL AND space.entry_type IN ('A', 'U')) Then
1910 fnd_message.set_name('PN', 'PN_SPACE_EMP_START_DATE_NULL');
1911 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1912 l_error_message := fnd_message.get;
1913 raise INVALID_RECORD;
1914 End If;
1915
1916 --------------------------------------------------------
1917 -- Check if Start Date > End Date
1918 --------------------------------------------------------
1919 If ( space.EMP_ASSIGN_START_DATE IS NOT NULL ) Then
1920 if ( space.EMP_ASSIGN_START_DATE >
1921 NVL( space.emp_assign_end_date,
1922 NVL(tlempinfo.emp_assign_end_date, to_date('12/31/4712','mm/dd/yyyy'))) ) then
1923 fnd_message.set_name('PN', 'PN_SPACE_ASSIGN_EMP_END_DT');
1924 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1925 l_error_message := fnd_message.get;
1926 raise INVALID_RECORD;
1927 end if;
1928 End If;
1929
1930 If (pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id) = 'Y') Then
1931
1932 put_log('Check if Allocated_Area is NOT NULL - AUTO SPACE DISTRIBUTION');
1933 -----------------------------------------------------------------------
1934 -- Check if Allocated_Area is NOT NULL in case of AUTO SPACE ASSIGNMENT
1935 -----------------------------------------------------------------------
1936 If (space.allocated_area IS NOT NULL) Then
1937 fnd_message.set_name('PN', 'PN_CAFM_SPACE_NOT_NULL_AREA');
1938 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1939 l_error_message := fnd_message.get;
1940 raise INVALID_RECORD;
1941 End If;
1942
1943 Else -- PN_AUTOMATIC_SPACE_DISTRIBUTION='N' -- Manual Space Distribution
1944
1945 put_log('Check if Allocated_Area is NULL - MANUAL SPACE DISTRIBUTION');
1946 ----------------------------------------------------------------
1947 -- Check if Allocated_Area is NULL for manual space distribution
1948 ----------------------------------------------------------------
1949 If (space.allocated_area is NULL) AND
1950 (space.entry_type IN ('A','U')) AND
1951 pnp_util_func.get_location_type_lookup_code(
1952 p_location_id => space.location_id,
1953 p_as_of_date => space.emp_assign_start_date) IN ('OFFICE','SECTION')
1954 Then
1955 fnd_message.set_name('PN', 'PN_CAFM_SPACE_NULL_AREA');
1956 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1957 l_error_message := fnd_message.get;
1958 raise INVALID_RECORD;
1959
1960 Elsif (space.allocated_area IS NOT NULL) AND
1961 (space.entry_type IN ('A','U')) AND
1962 pnp_util_func.get_location_type_lookup_code(
1963 p_location_id => space.location_id,
1964 p_as_of_date => space.emp_assign_start_date) NOT IN ('OFFICE','SECTION')
1965 Then
1966 fnd_message.set_name('PN', 'PN_CAFM_SPACE_NOT_NULL_AREA');
1967 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
1968 l_error_message := fnd_message.get;
1969 raise INVALID_RECORD;
1970
1971 End If;
1972
1973 put_log('Validate Allocated_Area and End_Date');
1974 --------------------------------------
1975 -- Validate Allocated Area
1976 --------------------------------------
1977 if ( space.entry_type = 'A' ) then
1978 l_old_allocated_area := 0;
1979 l_new_allocated_area := space.allocated_area;
1980 l_emp_assign_start_date := space.emp_assign_start_date;
1981 elsif ( space.entry_type IN ('U', 'R')) then
1982 l_old_allocated_area := tlempinfo.allocated_area;
1983 l_new_allocated_area := nvl( space.allocated_area, tlempinfo.allocated_area );
1984 l_emp_assign_start_date := NVL(space.emp_assign_start_date, NVL( space.change_date, SYSDATE ));
1985 end if;
1986
1987
1988 put_log('*** Calling PNP_UTIL_FUNC.validate_vacant_area ***');
1989 PNP_UTIL_FUNC.validate_vacant_area( p_location_id => space.Location_Id,
1990 p_st_date => l_emp_assign_start_date,
1991 p_end_dt => space.emp_assign_end_date,
1992 p_assignable_area => l_assignable_area,
1993 p_old_allocated_area => l_old_allocated_area,
1994 p_new_allocated_area => l_new_allocated_area,
1995 p_old_allocated_area_pct => NULL,
1996 p_new_allocated_area_pct => NULL,
1997 p_display_message => 'Y',
1998 p_future => l_future,
1999 p_available_vacant_area => l_available_vacant_area );
2000 put_log('*** DONE PNP_UTIL_FUNC.validate_vacant_area ***');
2001
2002 IF (l_assignable_area = -99) THEN
2003 l_error_message := fnd_message.get;
2004 raise INVALID_RECORD;
2005 END IF;
2006
2007
2008 If ( l_available_vacant_area ) Then
2009
2010 put_log('Computing Allocated_Area_Pct');
2011 --------------------------------------
2012 -- Computing Allocated_Area_Pct
2013 --------------------------------------
2014 put_log('****************************');
2015 l_allocated_area_pct := round((space.allocated_area / NVL( l_assignable_area, space.allocated_area ) * 100), 2);
2016 put_log(l_allocated_area_pct);
2017
2018 if( NVL(l_future, 'N') = 'Y' ) then
2019 put_log ( 'This location has future assignments. The end date of current assignment will default to one day prior to the earliest
2020 date of all future dated assignments for this location.' );
2021 end if;
2022
2023 Else
2024 fnd_message.set_name('PN', 'PN_CAFM_SPACE_INVALID_AREA');
2025 fnd_message.set_token('LOCATION_ID', space.LOCATION_ID);
2026 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
2027 l_error_message := fnd_message.get;
2028 raise INVALID_RECORD;
2029
2030 End If;
2031
2032 End If;
2033
2034 put_log('***Check if project_id and task_id are valid*****');
2035
2036 IF (space.project_id IS NULL) THEN
2037 IF(space.task_id IS NOT NULL) THEN
2038 fnd_message.set_name('PN', 'PN_CAFM_TASK_PROJECT');
2039 fnd_message.set_token('TASK_ID', space.TASK_ID);
2040 l_error_message := fnd_message.get;
2041 RAISE INVALID_RECORD;
2042 END IF;
2043 ELSE
2044 l_project_valid := FALSE;
2045 FOR rec IN check_project_valid(space.project_id) LOOP
2046 IF rec.project_id IS NOT NULL THEN
2047 l_project_valid := TRUE;
2048
2049 IF space.task_id IS NOT NULL THEN
2050 l_task_valid := FALSE;
2051 FOR rec1 IN check_task_valid(space.task_id,space.project_id) LOOP
2052 IF rec1.task_id IS NOT NULL THEN
2053 l_task_valid := TRUE;
2054 END IF;
2055 END LOOP;
2056
2057 IF l_task_valid = FALSE THEN
2058 fnd_message.set_name('PN', 'PN_CAFM_TASK');
2059 fnd_message.set_token('PROJECT_ID', space.PROJECT_ID);
2060 fnd_message.set_token('TASK_ID', space.TASK_ID);
2061 l_error_message := fnd_message.get;
2062 raise INVALID_RECORD;
2063 END IF;
2064 END IF;
2065 END IF;
2066 END LOOP;
2067
2068 IF l_project_valid = FALSE THEN
2069 fnd_message.set_name('PN', 'PN_CAFM_PROJECT');
2070 fnd_message.set_token('PROJECT_ID', space.PROJECT_ID);
2071 l_error_message := fnd_message.get;
2072 raise INVALID_RECORD;
2073 END IF;
2074 END IF;
2075
2076 ---------------------------------------
2077 -- Additional Entry
2078 ---------------------------------------
2079 if (space.ENTRY_TYPE = 'A') then
2080
2081 put_log('Additional entry type');
2082
2083 space.EMP_SPACE_ASSIGN_ID := NULL;
2084
2085 PNP_UTIL_FUNC.Validate_date_for_assignments (
2086 p_location_id => space.LOCATION_ID, --Bug#5587012
2087 p_start_date => space.emp_assign_start_date,
2088 p_end_date => space.emp_assign_end_date,
2089 x_return_status => l_return_status,
2090 x_return_message => l_return_message
2091 );
2092
2093 IF NOT( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2094 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
2095 l_error_message := fnd_message.get;
2096 raise INVALID_RECORD; --Bug5515198
2097 END IF;
2098
2099
2100 PN_SPACE_ASSIGN_EMP_PKG.insert_row
2101 (
2102 X_ROWID => l_rowid_dummy,
2103 X_org_id => l_org_id,
2104 X_EMP_SPACE_ASSIGN_ID => space.EMP_SPACE_ASSIGN_ID,
2105 X_ATTRIBUTE1 => space.ATTRIBUTE1,
2106 X_ATTRIBUTE2 => space.ATTRIBUTE2,
2107 X_ATTRIBUTE3 => space.ATTRIBUTE3,
2108 X_ATTRIBUTE4 => space.ATTRIBUTE4,
2109 X_ATTRIBUTE5 => space.ATTRIBUTE5,
2110 X_ATTRIBUTE6 => space.ATTRIBUTE6,
2111 X_ATTRIBUTE7 => space.ATTRIBUTE7,
2112 X_ATTRIBUTE8 => space.ATTRIBUTE8,
2113 X_ATTRIBUTE9 => space.ATTRIBUTE9,
2114 X_ATTRIBUTE10 => space.ATTRIBUTE10,
2115 X_ATTRIBUTE11 => space.ATTRIBUTE11,
2116 X_ATTRIBUTE12 => space.ATTRIBUTE12,
2117 X_ATTRIBUTE13 => space.ATTRIBUTE13,
2118 X_ATTRIBUTE14 => space.ATTRIBUTE14,
2119 X_ATTRIBUTE15 => space.ATTRIBUTE15,
2120 X_LOCATION_ID => space.LOCATION_ID,
2121 X_PERSON_ID => space.EMPLOYEE_ID,
2122 X_PROJECT_ID => space.PROJECT_ID,
2123 X_TASK_ID => space.TASK_ID,
2124 X_EMP_ASSIGN_START_DATE => space.EMP_ASSIGN_START_DATE,
2125 X_EMP_ASSIGN_END_DATE => space.EMP_ASSIGN_END_DATE,
2126 X_COST_CENTER_CODE => space.COST_CENTER_CODE,
2127 X_ALLOCATED_AREA_PCT => l_allocated_area_pct,
2128 X_ALLOCATED_AREA => space.ALLOCATED_AREA,
2129 X_UTILIZED_AREA => space.UTILIZED_AREA,
2130 X_EMP_SPACE_COMMENTS => NULL,
2131 X_ATTRIBUTE_CATEGORY => space.ATTRIBUTE_CATEGORY,
2132 X_CREATION_DATE => SYSDATE,
2133 X_CREATED_BY => NVL( fnd_profile.value('USER_ID'), 0),
2134 X_LAST_UPDATE_DATE => SYSDATE,
2135 X_LAST_UPDATED_BY => NVL( fnd_profile.value('USER_ID'), 0),
2136 X_LAST_UPDATE_LOGIN => NVL( fnd_profile.value('CONC_LOGIN_ID'), 0),
2137 X_SOURCE => space.source
2138 );
2139
2140
2141 end if;
2142
2143 ---------------------------------------------------------------------------
2144 -- If we are here then it is UPDATE or REPLACE,
2145 ---------------------------------------------------------------------------
2146
2147 If (space.ENTRY_TYPE IN ('U', 'R')) Then
2148
2149 -- We do not allow UPDATE if current_space_allocation is not active
2150
2151 if ( NVL( space.change_mode, 'CORRECT' ) = 'UPDATE' ) then
2152 if ( nvl(space.change_date, trunc(SYSDATE)) < tlempinfo.emp_assign_start_date OR
2153 nvl(space.change_date, trunc(SYSDATE)) > NVL(tlempinfo.emp_assign_end_date,
2154 to_date('12/31/4712','mm/dd/yyyy')) ) then
2155 fnd_message.set_name('PN', 'PN_CAFM_SPACE_UNABLE_TO_UPD');
2156 fnd_message.set_token('SPACE_ALLOCATION_ID', space.EMP_SPACE_ASSIGN_ID);
2157 l_error_message := fnd_message.get;
2158 Raise INVALID_RECORD;
2159 end if;
2160
2161 space.emp_assign_start_date := NVL( space.change_date, SYSDATE );
2162
2163 end if;
2164
2165 End if;
2166
2167 If (space.ENTRY_TYPE = 'U' ) Then
2168
2169 PNP_UTIL_FUNC.Validate_date_for_assignments (
2170 p_location_id => space.LOCATION_ID, --Bug#5587012
2171 p_start_date => space.emp_assign_start_date,
2172 p_end_date => space.emp_assign_end_date,
2173 x_return_status => l_return_status,
2174 x_return_message => l_return_message
2175 );
2176
2177 IF NOT( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2178 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
2179 l_error_message := fnd_message.get;
2180 raise INVALID_RECORD; --Bug5515198
2181 END IF;
2182
2183
2184 PN_SPACE_ASSIGN_EMP_PKG.update_row
2185 (
2186 X_EMP_SPACE_ASSIGN_ID => space.EMP_SPACE_ASSIGN_ID,
2187 X_ATTRIBUTE1 => space.ATTRIBUTE1,
2188 X_ATTRIBUTE2 => space.ATTRIBUTE2,
2189 X_ATTRIBUTE3 => space.ATTRIBUTE3,
2190 X_ATTRIBUTE4 => space.ATTRIBUTE4,
2191 X_ATTRIBUTE5 => space.ATTRIBUTE5,
2192 X_ATTRIBUTE6 => space.ATTRIBUTE6,
2193 X_ATTRIBUTE7 => space.ATTRIBUTE7,
2194 X_ATTRIBUTE8 => space.ATTRIBUTE8,
2195 X_ATTRIBUTE9 => space.ATTRIBUTE9,
2196 X_ATTRIBUTE10 => space.ATTRIBUTE10,
2197 X_ATTRIBUTE11 => space.ATTRIBUTE11,
2198 X_ATTRIBUTE12 => space.ATTRIBUTE12,
2199 X_ATTRIBUTE13 => space.ATTRIBUTE13,
2200 X_ATTRIBUTE14 => space.ATTRIBUTE14,
2201 X_ATTRIBUTE15 => space.ATTRIBUTE15,
2202 X_LOCATION_ID => space.LOCATION_ID,
2203 X_PERSON_ID => space.EMPLOYEE_ID,
2204 X_PROJECT_ID => space.PROJECT_ID,
2205 X_TASK_ID => space.TASK_ID,
2206 X_EMP_ASSIGN_START_DATE => space.EMP_ASSIGN_START_DATE,
2207 X_EMP_ASSIGN_END_DATE => space.EMP_ASSIGN_END_DATE,
2208 X_COST_CENTER_CODE => space.COST_CENTER_CODE,
2209 X_ALLOCATED_AREA_PCT => l_allocated_area_pct,
2210 X_ALLOCATED_AREA => space.ALLOCATED_AREA,
2211 X_UTILIZED_AREA => space.UTILIZED_AREA,
2212 X_EMP_SPACE_COMMENTS => NULL,
2213 X_ATTRIBUTE_CATEGORY => space.ATTRIBUTE_CATEGORY,
2214 X_LAST_UPDATE_DATE => sysdate,
2215 X_LAST_UPDATED_BY => nvl(fnd_profile.value('USER_ID'), 0),
2216 X_LAST_UPDATE_LOGIN => nvl(fnd_profile.value('CONC_LOGIN_ID'), 0),
2217 X_UPDATE_CORRECT_OPTION => space.change_mode,
2218 X_CHANGED_START_DATE => l_changed_start_date,
2219 X_SOURCE => space.source
2220 );
2221
2222 End if; -- 'A/U'
2223
2224
2225 --- for BUG#2127286 added the IF condtion for ENTRY TYPE as 'R'
2226
2227 --------------------------------------
2228 -- Replace Data in PN_SPACE_ALLOCATIONS
2229 --------------------------------------
2230 if (space.ENTRY_TYPE = 'R') then
2231
2232 PNP_UTIL_FUNC.Validate_date_for_assignments (
2233 p_location_id =>NVL(space.LOCATION_ID,tlempinfo.LOCATION_ID), --Bug#5587012
2234 p_start_date =>NVL(space.EMP_ASSIGN_START_DATE,tlempinfo.EMP_ASSIGN_START_DATE),
2235 p_end_date =>NVL(space.EMP_ASSIGN_START_DATE,tlempinfo.EMP_ASSIGN_START_DATE),
2236 x_return_status => l_return_status,
2237 x_return_message => l_return_message
2238 );
2239
2240 IF NOT( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2241 fnd_message.set_name('PN', 'PN_INVALID_SPACE_ASSGN_DATE');
2242 l_error_message := fnd_message.get;
2243 raise INVALID_RECORD; --Bug5515198
2244 END IF;
2245
2246
2247 PN_SPACE_ASSIGN_EMP_PKG.update_row
2248 (
2249 X_EMP_SPACE_ASSIGN_ID => NVL(space.EMP_SPACE_ASSIGN_ID,tlempinfo.EMP_SPACE_ASSIGN_ID),
2250 X_ATTRIBUTE1 => NVL(space.ATTRIBUTE1,tlempinfo.ATTRIBUTE1),
2251 X_ATTRIBUTE2 => NVL(space.ATTRIBUTE2,tlempinfo.ATTRIBUTE2),
2252 X_ATTRIBUTE3 => NVL(space.ATTRIBUTE3,tlempinfo.ATTRIBUTE3),
2253 X_ATTRIBUTE4 => NVL(space.ATTRIBUTE4,tlempinfo.ATTRIBUTE4),
2254 X_ATTRIBUTE5 => NVL(space.ATTRIBUTE5,tlempinfo.ATTRIBUTE5),
2255 X_ATTRIBUTE6 => NVL(space.ATTRIBUTE6,tlempinfo.ATTRIBUTE6),
2256 X_ATTRIBUTE7 => NVL(space.ATTRIBUTE7,tlempinfo.ATTRIBUTE7),
2257 X_ATTRIBUTE8 => NVL(space.ATTRIBUTE8,tlempinfo.ATTRIBUTE8),
2258 X_ATTRIBUTE9 => NVL(space.ATTRIBUTE9,tlempinfo.ATTRIBUTE9),
2259 X_ATTRIBUTE10 => NVL(space.ATTRIBUTE10,tlempinfo.ATTRIBUTE10),
2260 X_ATTRIBUTE11 => NVL(space.ATTRIBUTE11,tlempinfo.ATTRIBUTE11),
2261 X_ATTRIBUTE12 => NVL(space.ATTRIBUTE12,tlempinfo.ATTRIBUTE12),
2262 X_ATTRIBUTE13 => NVL(space.ATTRIBUTE13,tlempinfo.ATTRIBUTE13),
2263 X_ATTRIBUTE14 => NVL(space.ATTRIBUTE14,tlempinfo.ATTRIBUTE14),
2264 X_ATTRIBUTE15 => NVL(space.ATTRIBUTE15,tlempinfo.ATTRIBUTE15),
2265 X_LOCATION_ID => NVL(space.LOCATION_ID,tlempinfo.LOCATION_ID),
2266 X_PERSON_ID => NVL(space.EMPLOYEE_ID,tlempinfo.PERSON_ID),
2267 X_PROJECT_ID => space.PROJECT_ID,
2268 X_TASK_ID => space.TASK_ID,
2269 X_EMP_ASSIGN_START_DATE => NVL(space.EMP_ASSIGN_START_DATE,tlempinfo.EMP_ASSIGN_START_DATE),
2270 X_EMP_ASSIGN_END_DATE => NVL(space.EMP_ASSIGN_END_DATE,tlempinfo.EMP_ASSIGN_END_DATE),
2271 X_COST_CENTER_CODE => NVL(space.COST_CENTER_CODE,tlempinfo.COST_CENTER_CODE),
2272 X_ALLOCATED_AREA_PCT => NVL(l_allocated_area_pct,tlempinfo.ALLOCATED_AREA_PCT),
2273 X_ALLOCATED_AREA => NVL(space.ALLOCATED_AREA,tlempinfo.ALLOCATED_AREA),
2274 X_UTILIZED_AREA => NVL(space.UTILIZED_AREA,tlempinfo.UTILIZED_AREA),
2275 X_EMP_SPACE_COMMENTS => NULL,
2276 X_ATTRIBUTE_CATEGORY => NVL(space.ATTRIBUTE_CATEGORY,tlempinfo.ATTRIBUTE_CATEGORY),
2277 X_LAST_UPDATE_DATE => sysdate,
2278 X_LAST_UPDATED_BY => nvl(fnd_profile.value('USER_ID'), 0),
2279 X_LAST_UPDATE_LOGIN => nvl(fnd_profile.value('CONC_LOGIN_ID'), 0),
2280 X_UPDATE_CORRECT_OPTION => space.change_mode,
2281 X_CHANGED_START_DATE => l_changed_start_date,
2282 X_SOURCE => NVL(space.source,tlempinfo.source)
2283 );
2284
2285
2286 end if; -- 'R'
2287
2288 ---For BUG#2127286 End of IF condition for ENTRY TYPE='R'
2289
2290 --auto distri
2291
2292 If (pn_mo_cache_utils.get_profile_value('PN_AUTOMATIC_SPACE_DISTRIBUTION',l_org_id) = 'Y') Then
2293
2294 PN_SPACE_ALLOCATIONS_PKG.area_pct_and_area(l_Assignable_area, Space.Location_Id);
2295
2296 End If;
2297
2298 ------------------------------------------------
2299 -- Set pn_emp_space_assign_itf.transferred_to_pn = 'Y'
2300 ------------------------------------------------
2301
2302 put_log('Updating transferred to pn flag in itf table');
2303
2304 update pn_emp_space_assign_itf
2305 set transferred_to_pn = 'Y',
2306 error_message = NULL
2307 where ROWID = space.ROWID;
2308
2309 put_log('Updated transferred to pn flag in itf table');
2310
2311 EXCEPTION
2312 WHEN INVALID_RECORD THEN
2313 ROLLBACK TO S2;
2314 l_fail := l_fail + 1;
2315
2316 -- Update ERROR_MESSAGE
2317 update pn_emp_space_assign_itf
2318 set error_message = substr(l_error_message, 1, 240)
2319 where ROWID = space.ROWID;
2320
2321 -- Spool to Conc Log
2322 put_line(l_error_message);
2323
2324 errbuf := l_error_message;
2325 retcode := '2';
2326
2327 WHEN OTHERS THEN
2328
2329 ROLLBACK TO S2;
2330 l_fail := l_fail + 1;
2331
2332 l_error_message := fnd_message.get;
2333
2334 update pn_emp_space_assign_itf
2335 set error_message = substr(l_error_message, 1, 240)
2336 where ROWID = space.ROWID;
2337
2338 put_line(l_error_message);
2339
2340
2341 errbuf := l_error_message;
2342 retcode := '2';
2343
2344 --Bug5609648 return;
2345
2346 END;
2347 COMMIT;
2348 END LOOP;
2349
2350
2351 if (l_total = 0) then
2352 fnd_message.set_name ('PN', 'PN_CAFM_NO_SPC_REC_FOUND');
2353 errbuf := fnd_message.get;
2354 retcode := '2';
2355 put_line(errbuf);
2356
2357 else
2358
2359 l_succ := l_total - l_fail;
2360
2361 Put_Log('
2362 =============== Summary ===============');
2363
2364 fnd_message.set_name('PN', 'PN_CAFM_SPACE_SUCCESS');
2365 fnd_message.set_token('SUCCESS', l_succ);
2366 put_line(fnd_message.get);
2367
2368 fnd_message.set_name('PN', 'PN_CAFM_SPACE_FAILURE');
2369 fnd_message.set_token('FAILURE', l_fail);
2370 put_line(fnd_message.get);
2371
2372 fnd_message.set_name('PN', 'PN_CAFM_SPACE_TOTAL');
2373 fnd_message.set_token('TOTAL', l_total);
2374 put_line(fnd_message.get);
2375
2376 end if;
2377
2378
2379 EXCEPTION
2380 WHEN NO_DATA_FOUND THEN
2381 put_line('EXCEPTION: NO_DATA_FOUND');
2382 fnd_message.set_name ('PN', 'PN_NO_SPACE_DATA_FOUND');
2383 errbuf := l_error_message;
2384 retcode := '2';
2385 put_line(errbuf);
2386 APP_EXCEPTION.raise_exception;
2387
2388
2389 WHEN OTHERS THEN
2390 put_line('EXCEPTION: OTHERS');
2391 fnd_message.set_name ('PN', 'PN_NO_SPACE_DATA_FOUND');
2392 errbuf := l_error_message;
2393 retcode := '2';
2394 put_line(errbuf);
2395 APP_EXCEPTION.raise_exception;
2396
2397
2398 END SPACE_ALLOCATIONS_ITF;
2399
2400 /*===========================================================================+
2401 | PROCEDURE
2402 | Put_Log
2403 |
2404 | DESCRIPTION
2405 | Writes the String passed as argument to Concurrent Log
2406 |
2407 | ARGUMENTS: p_String
2408 |
2409 | NOTES:
2410 | Called at all Debug points spread across this file
2411 |
2412 | MODIFICATION HISTORY
2413 | Created Naga Vijayapuram 1999
2414 |
2415 +===========================================================================*/
2416
2417 Procedure Put_Log(p_String VarChar2) IS
2418
2419 BEGIN
2420
2421 Fnd_File.Put_Line(Fnd_File.Log, p_String);
2422
2423 EXCEPTION
2424
2425 When Others Then Raise;
2426
2427 END Put_Log;
2428
2429
2430 /*===========================================================================+
2431 | PROCEDURE
2432 | Put_Line
2433 |
2434 | DESCRIPTION
2435 | Writes the String passed as argument to Concurrent Log/Output
2436 |
2437 | ARGUMENTS: p_String
2438 |
2439 | NOTES:
2440 | Called at all Debug points spread across this file
2441 |
2442 | MODIFICATION HISTORY
2443 | Created Naga Vijayapuram 1999
2444 |
2445 +===========================================================================*/
2446
2447 Procedure Put_Line(p_String VarChar2) IS
2448
2449 BEGIN
2450
2451 Fnd_File.Put_Line(Fnd_File.Log, p_String);
2452 Fnd_File.Put_Line(Fnd_File.Output, p_String);
2453
2454 EXCEPTION
2455
2456 When Others Then Raise;
2457
2458 END Put_Line;
2459
2460
2461 -------------------------------
2462 -- End of Package
2463 -------------------------------
2464 END PN_CAD_IMPORT;