[Home] [Help]
PACKAGE BODY: APPS.PNRX_SP_ASSIGN_BY_LOC
Source
1 PACKAGE BODY pnrx_sp_assign_by_loc AS
2 /* $Header: PNRXSALB.pls 120.3 2005/12/29 22:49:31 appldev ship $ */
3
4 FUNCTION compare_assign_emploc(
5 p_location_id IN NUMBER,
6 p_person_id IN NUMBER,
7 p_cost_center IN VARCHAR2,
8 p_request_id IN NUMBER)
9 RETURN BOOLEAN IS
10 v1_location_id NUMBER;
11 v1_person_id NUMBER;
12 v1_cost_center VARCHAR2(30);
13 v1_request_id NUMBER;
14 v_returnvalue NUMBER;
15 v_var1 VARCHAR2(1);
16 BEGIN
17 SELECT 'X'
18 INTO v_var1
19 FROM pn_space_assign_loc_itf
20 WHERE location_id = p_location_id
21 AND person_id(+) = p_person_id
22 AND cost_center = p_cost_center
23 AND request_id = P_request_id;
24 RETURN TRUE;
25 EXCEPTION
26 WHEN NO_DATA_FOUND THEN
27 RETURN FALSE;
28 END compare_assign_emploc;
29
30 -------------------------------------------------------------------------------
31 -- FUNCTION : COMPARE_ASSIGN_CUSTLOC
32 -- INVOKED FROM :
33 -- PURPOSE :
34 -- HISTORY :
35 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_locations with _ALL table.
36 -- 27-OCT-05 sdmahesh o ATG Mandated changes for SQL literals
37 -------------------------------------------------------------------------------
38
39 FUNCTION compare_assign_custloc(
40 p_location_id IN NUMBER,
41 p_account_id IN VARCHAR2,
42 p_request_id IN NUMBER)
43 RETURN BOOLEAN IS
44 v1_location_id NUMBER;
45 v1_account_id NUMBER;
46 v1_request_id NUMBER;
47 v_returnvalue NUMBER;
48 v_var2 VARCHAR2(1);
49 BEGIN
50 SELECT 'Y'
51 INTO v_var2
52 FROM pn_space_assign_loc_itf
53 WHERE location_id = p_location_id
54 AND customer_account = p_account_id
55 AND request_id = p_request_id;
56 RETURN TRUE;
57 EXCEPTION
58 WHEN NO_DATA_FOUND THEN
59 RETURN FALSE;
60 END compare_assign_custloc;
61
62 PROCEDURE pn_space_assign_loc(
63 property_code_low IN VARCHAR2,
64 property_code_high IN VARCHAR2,
65 location_code_low IN VARCHAR2,
66 location_code_high IN VARCHAR2,
67 location_type IN VARCHAR2,
68 as_of_date IN DATE,
69 report_type IN VARCHAR2,
70 l_request_id IN NUMBER,
71 l_user_id IN NUMBER,
72 retcode OUT NOCOPY VARCHAR2,
73 errbuf OUT NOCOPY VARCHAR2
74 )
75 IS
76 l_login_id NUMBER;
77 type cur_typ is ref cursor;
78 c_e_pn CUR_TYP;
79 c_c_pn CUR_TYP;
80 c_e_assign_pn CUR_TYP;
81 c_c_assign_pn CUR_TYP;
82 c_e_prop CUR_TYP;
83 c_c_prop CUR_TYP;
84 c_e_v_pn CUR_TYP;
85 c_c_v_pn CUR_TYP;
86 emp_pn CUR_TYP;
87 cust_pn CUR_TYP;
88 query_str VARCHAR2(20000);
89
90 --declare the 'where clauses here........'
91 property_code_where_clause VARCHAR2(4000);
92 location_code_where_clause VARCHAR2(4000);
93 location_type_where_clause VARCHAR2(4000);
94 l_one NUMBER := 1;
95 l_two NUMBER := 2;
96 l_three NUMBER := 3;
97
98 --declare all columns as variables here
99 v_location_id pn_space_assign_loc_itf.location_id%TYPE;
100 v_location_id_1 pn_space_assign_loc_itf.location_id%TYPE;
101 v_location_id_2 pn_space_assign_loc_itf.location_id%TYPE;
102 v_location_id_3 pn_space_assign_loc_itf.location_id%TYPE;
103 v_location_type pn_space_assign_loc_itf.location_type%TYPE;
104 v_location_name pn_space_assign_loc_itf.location_name%TYPE;
105 v_location_code pn_space_assign_loc_itf.location_code%TYPE;
106 v_space_type pn_space_assign_loc_itf.space_type%TYPE;
107 v_property_code pn_space_assign_loc_itf.property_code%TYPE;
108 v_person_id pn_space_assign_loc_itf.person_id%TYPE;
109 v_person_id_3 pn_space_assign_loc_itf.person_id%TYPE;
110 v_cost_center pn_space_assign_loc_itf.cost_center%TYPE;
111 v_employee_project_number pn_space_assign_loc_itf.employee_project_number%TYPE;
112 v_employee_task_number pn_space_assign_loc_itf.employee_task_number%TYPE;
113 v_employee_assigned_from pn_space_assign_loc_itf.employee_assigned_from%TYPE;
114 v_employee_assigned_to pn_space_assign_loc_itf.employee_assigned_to%TYPE;
115 v_customer_account pn_space_assign_loc_itf.customer_account%TYPE;
116 v_exp_account NUMBER;
117 v_customer_account_id NUMBER;
118 v_customer_project_number pn_space_assign_loc_itf.customer_project_number%TYPE;
119 v_customer_task_number pn_space_assign_loc_itf.customer_task_number%TYPE;
120 v_customer_assigned_from pn_space_assign_loc_itf.customer_assigned_from%TYPE;
121 v_customer_assigned_to pn_space_assign_loc_itf.customer_assigned_to%TYPE;
122 v_customer_name pn_space_assign_loc_itf.customer_name%TYPE;
123 v_customer_site pn_space_assign_loc_itf.customer_site%TYPE;
124 v_customer_category pn_space_assign_loc_itf.customer_category%TYPE;
125 v_rentable_area pn_space_assign_loc_itf.rentable_area%TYPE;
126 v_usable_area pn_space_assign_loc_itf.usable_area%TYPE;
127 v_assignable_area pn_space_assign_loc_itf.assignable_area%TYPE;
128 v_common_area pn_space_assign_loc_itf.common_area%TYPE;
129 v_employee_assigned_area pn_space_assign_loc_itf.employee_assigned_area%TYPE;
130 v_customer_assigned_area pn_space_assign_loc_itf.customer_assigned_area%TYPE;
131 v_vacant_area NUMBER;
132 v_attribute_category pn_space_assign_loc_itf.loc_attribute_category%TYPE;
133 v_attribute1 pn_space_assign_loc_itf.loc_attribute1%TYPE;
134 v_attribute2 pn_space_assign_loc_itf.loc_attribute2%TYPE;
135 v_attribute3 pn_space_assign_loc_itf.loc_attribute3%TYPE;
136 v_attribute4 pn_space_assign_loc_itf.loc_attribute4%TYPE;
137 v_attribute5 pn_space_assign_loc_itf.loc_attribute5%TYPE;
138 v_attribute6 pn_space_assign_loc_itf.loc_attribute6%TYPE;
139 v_attribute7 pn_space_assign_loc_itf.loc_attribute7%TYPE;
140 v_attribute8 pn_space_assign_loc_itf.loc_attribute8%TYPE;
141 v_attribute9 pn_space_assign_loc_itf.loc_attribute9%TYPE;
142 v_attribute10 pn_space_assign_loc_itf.loc_attribute10%TYPE;
143 v_attribute11 pn_space_assign_loc_itf.loc_attribute11%TYPE;
144 v_attribute12 pn_space_assign_loc_itf.loc_attribute12%TYPE;
145 v_attribute13 pn_space_assign_loc_itf.loc_attribute13%TYPE;
146 v_attribute14 pn_space_assign_loc_itf.loc_attribute14%TYPE;
147 v_attribute15 pn_space_assign_loc_itf.loc_attribute15%TYPE;
148 v_last_update_date pn_space_assign_loc_itf.last_update_date%TYPE;
149 v_last_updated_by pn_space_assign_loc_itf.last_updated_by%TYPE;
150 v_last_update_login pn_space_assign_loc_itf.last_update_login%TYPE;
151 v_creation_date pn_space_assign_loc_itf.creation_date%TYPE;
152 v_created_by pn_space_assign_loc_itf.created_by%TYPE;
153
154 --declare the record type for the function here.........
155 v_emp_data pnp_util_func.emp_hr_data_rec := NULL;
156 v_code_data pnp_util_func.location_name_rec := NULL;
157 v_compare_emp BOOLEAN;
158 v_compare_cust BOOLEAN;
159 l_date DATE := fnd_date.canonical_to_date('4712/12/31 00:00:00' );
160 v_coa_id NUMBER; --VARCHAR2(30);
161 v_loc_type VARCHAR2(100) ; --BUG#2226865
162 v_loc_area pnp_util_func.pn_location_area_rec; --bug#2226865
163 v_space_area pnp_util_func.pn_space_area_rec; --bug#2226865
164
165 l_cursor INTEGER;
166 l_rows INTEGER;
167 l_count INTEGER;
168 l_property_code_low VARCHAR2(90);
169 l_property_code_high VARCHAR2(90);
170 l_location_code_low VARCHAR2(90);
171 l_location_code_high VARCHAR2(90);
172 l_location_type VARCHAR2(30);
173 l_statement VARCHAR2(10000);
174
175
176
177 BEGIN
181 retcode:=0;
178 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locCondotions(+)');
179
180 --Initialise status parameters...
182 errbuf:='';
183 fnd_profile.get('LOGIN_ID', l_login_id);
184 SELECT chart_of_accounts_id into v_coa_id
185 FROM gl_sets_of_books
186 WHERE set_of_books_id= TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
187 pn_mo_cache_utils.get_current_org_id));
188
189 l_cursor := dbms_sql.open_cursor;
190
191 --property code conditions
192
193 l_property_code_low := property_code_low;
194 l_property_code_high := property_code_high;
195
196 IF property_code_low IS NOT NULL AND property_code_high IS NOT NULL THEN
197 property_code_where_clause := ' AND p.property_code BETWEEN
198 :l_property_code_low AND :l_property_code_high';
199 ELSIF property_code_low IS NULL AND property_code_high IS NOT NULL THEN
200 property_code_where_clause := ' AND p.property_code =
201 :l_property_code_high';
202 ELSIF property_code_low IS NOT NULL AND property_code_high IS NULL THEN
203 property_code_where_clause := ' AND p.property_code =
204 :l_property_code_low';
205 ELSE property_code_where_clause := ' AND 1 = 1 ';
206 END IF;
207
208
209 /*IF property_code_low IS NOT NULL AND property_code_high IS NOT NULL THEN
210 property_code_where_clause := ' AND p.property_code BETWEEN '||''''||property_code_low||''''||
211 ' AND '||''''||property_code_high||'''';
212 ELSIF property_code_low IS NULL AND property_code_high IS NOT NULL THEN
213 property_code_where_clause := ' AND p.property_code = '||''''||property_code_high||'''';
214 ELSIF property_code_low IS NOT NULL AND property_code_high IS NULL THEN
215 property_code_where_clause := ' AND p.property_code = '||''''||property_code_low||'''';
216 ELSE
217 property_code_where_clause := ' AND 1 = 1 ';
218 END IF;*/
219
220 --location code conditions.....
221 l_location_code_low := location_code_low;
222 l_location_code_high := location_code_high;
223 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
224 location_code_where_clause := ' AND loc.location_code BETWEEN
225 :l_location_code_low AND :l_location_code_high';
226 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
227 location_code_where_clause := ' AND loc.location_code =
228 :l_location_code_high';
229 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
230 location_code_where_clause := ' AND loc.location_code =
231 :l_location_code_low';
232 ELSE location_code_where_clause := ' AND 2=2 ';
233 END IF;
234
235
236 --location type conditions....
237 l_location_type := location_type;
238 IF location_type IS NOT NULL THEN
239 location_type_where_clause := ' AND loc.location_type_lookup_code = :l_location_type';
240 /*location_type_where_clause := ' AND loc.location_type_lookup_code = '||''''||location_type||'''';*/
241 ELSE
242 location_type_where_clause := ' AND 3 = 3 ';
243 END IF;
244
245 --validate report type...
246 IF report_type IN ('EMPLOYEE','ALL') THEN
247
248 l_statement :=
249 'SELECT DISTINCT
250 loc.location_id location_i
251 FROM pn_locations loc,
252 pn_properties p
253 WHERE p.property_id(+) = loc.property_id '
254 ||property_code_where_clause||location_code_where_clause||location_type_where_clause;
255
256
257
258 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
259
260 --------------------------------------
261 --property code conditions
262 IF property_code_low IS NOT NULL AND property_code_high IS NOT NULL THEN
263 dbms_sql.bind_variable(l_cursor,'l_property_code_low',l_property_code_low);
264 dbms_sql.bind_variable(l_cursor,'l_property_code_high',l_property_code_high);
265 ELSIF property_code_low IS NULL AND property_code_high IS NOT NULL THEN
266 dbms_sql.bind_variable(l_cursor,'l_property_code_high',l_property_code_high);
267 ELSIF property_code_low IS NOT NULL AND property_code_high IS NULL THEN
268 dbms_sql.bind_variable(l_cursor,'l_property_code_low',l_property_code_low);
269 END IF;
270
271
272 --location code conditions.....
273 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
274 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
275 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
276 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
277 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
278 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
279 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
280 END IF;
281
282
283 --location type conditions....
284 IF location_type IS NOT NULL THEN
285 dbms_sql.bind_variable(l_cursor,'l_location_type',l_location_type);
286 END IF;
287
288 dbms_sql.define_column (l_cursor,1,v_location_id_1);
289 l_rows := dbms_sql.execute(l_cursor);
290
291 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locQuery(-)');
292 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locLoop(+)');
293 LOOP
294 l_count := dbms_sql.fetch_rows( l_cursor );
295 EXIT WHEN l_count <> 1;
296
297 dbms_sql.column_value(l_cursor,1,v_location_id_1);
298
299 OPEN c_e_assign_pn FOR
300 SELECT DISTINCT
301 loc.location_id location_id,
302 fl1.meaning location_type,
306 fl.meaning usage_type,
303 NVL(NVL(loc.building, loc.FLOOR), loc.office) location_name,
304 loc.location_code location_code,
305 loc.location_type_lookup_code lOC_TYPE, --BUG#2226865
307 loc.rentable_area rentable_area,
308 loc.usable_area usable_area,
309 loc.assignable_area assignable_area,
310 loc.common_area common_area,
311 loc.attribute_category attribute_category,
312 loc.attribute1 attribute1,
313 loc.attribute2 attribute2,
314 loc.attribute3 attribute3,
315 loc.attribute4 attribute4,
316 loc.attribute5 attribute5,
317 loc.attribute6 attribute6,
318 loc.attribute7 attribute7,
319 loc.attribute8 attribute8,
320 loc.attribute9 attribute9,
321 loc.attribute10 attribute10,
322 loc.attribute11 attribute11,
323 loc.attribute12 attribute12,
324 loc.attribute13 attribute13,
325 loc.attribute14 attribute14,
326 loc.attribute15 attribute15,
327 loc.last_update_date last_update_date,
328 loc.last_updated_by last_updated_by,
329 loc.last_update_login last_update_login,
330 loc.creation_date creation_date,
331 loc.created_by created_by
332 FROM pn_locations_all loc,
333 fnd_lookups fl,
334 fnd_lookups fl1
335 WHERE fl.lookup_code(+) = loc.space_type_lookup_code
336 AND fl.lookup_type(+) = 'PN_SPACE_TYPE'
337 AND fl1.lookup_code(+)= loc.location_type_lookup_code
338 AND fl1.lookup_type(+)= 'PN_LOCATION_TYPE'
339 and nvl(trunc(as_of_date),trunc(loc.active_start_date)) between
340 trunc(loc.active_start_date) and trunc(loc.active_end_date);
341 /* Bug 4748773
342
343 AND loc.location_id IN (SELECT loc.location_id from pn_locations_all loc
344 WHERE loc.active_start_date <= as_of_date
345 AND loc.active_end_date >= as_of_date
346 AND loc.location_id = v_location_id_1);
347 */
348
349 LOOP
350 FETCH c_e_assign_pn
351 INTO v_location_id_2 ,
352 v_location_type ,
353 v_location_name ,
354 v_location_code ,
355 v_loc_type , --BUG#2226865
356 v_space_type ,
357 v_loc_area.rentable_area , --BUG#2226865
358 v_loc_area.usable_area , --BUG#2226865
359 v_loc_area.assignable_area , --BUG#2226865
360 v_loc_area.common_area , --BUG#2226865
361 v_attribute_category ,
362 v_attribute1 ,
363 v_attribute2 ,
364 v_attribute3 ,
365 v_attribute4 ,
366 v_attribute5 ,
367 v_attribute6 ,
368 v_attribute7 ,
369 v_attribute8 ,
370 v_attribute9 ,
371 v_attribute10 ,
372 v_attribute11 ,
373 v_attribute12 ,
374 v_attribute13 ,
375 v_attribute14 ,
376 v_attribute15 ,
377 v_last_update_date ,
378 v_last_updated_by ,
379 v_last_update_login ,
380 v_creation_date ,
381 v_created_by ;
382 EXIT WHEN c_e_assign_pn%NOTFOUND;
383
384 OPEN emp_pn FOR
385 SELECT DISTINCT
386 emp.location_id location_id,
387 emp.person_id person_id,
388 emp.cost_center_code cost_center,
389 pa.segment1 employee_project_number,
390 pat.task_name employee_task_number,
391 emp.allocated_area employee_assigned_area,
392 emp.emp_assign_start_date employee_assigned_from,
393 emp.emp_assign_end_date employee_assigned_to,
394 NULL customer_account,
395 NULL customer_project_number,
399 NULL customer_assigned_to,
396 NULL customer_task_number,
397 0 customer_assigned_area,
398 NULL customer_assigned_from,
400 NULL customer_name,
401 NULL customer_site,
402 NULL customer_category
403 FROM pa_projects_all pa,
404 pa_tasks pat,
405 pn_space_assign_emp_all emp
406 WHERE emp.location_id = V_LOCATION_ID_2
407 AND pa.project_id(+)= emp.project_id
408 AND pat.task_id(+) = emp.task_id
409 AND as_of_date BETWEEN emp.emp_assign_start_date AND NVL(emp.emp_assign_end_date, l_date);
410 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locQuery(-)');
411 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locLoop(+)');
412 LOOP
413 FETCH emp_pn
414 INTO v_location_id,
415 v_person_id,
416 v_cost_center,
417 v_employee_project_number,
418 v_employee_task_number,
419 v_space_area.allocated_area_emp, --BUG#2226865
420 v_employee_assigned_from,
421 v_employee_assigned_to,
422 v_customer_account,
423 v_customer_project_number,
424 v_customer_task_number,
425 v_space_area.allocated_area_cust, --BUG#2226865
426 v_customer_assigned_from,
427 v_customer_assigned_to,
428 v_customer_name,
429 v_customer_site,
430 v_customer_category;
431 EXIT WHEN emp_pn%NOTFOUND;
432 v_compare_emp:=compare_assign_emploc(v_location_id, v_person_id, v_cost_center, l_request_id);
433 IF NOT(v_compare_emp) THEN
434
435 v_code_data:=pnp_util_func.get_location_name(v_location_id, as_of_date);
436 v_emp_data:= pnp_util_func.get_emp_hr_data(v_person_id);
437 --dbms_output.put_line('fet'||sqlerrm);
438 --------------bug#2226865-----------------
439 IF V_LOC_TYPE in ('BUILDING' ,'LAND','FLOOR','PARCEL') THEN
440 PNP_UTIL_FUNC.get_area(v_location_id , v_loc_type ,NULL,as_of_date,v_loc_area,v_space_area);
441 END IF;
442 -----------bug#2226865--------
443
444
445 INSERT INTO pn_space_assign_loc_itf
446 (location_id ,
447 location_type ,
448 location_name ,
449 location_code ,
450 space_type ,
451 property_code ,
452 property_name ,
453 building_location_code ,
454 building_or_land_name ,
455 floor_location_code ,
456 floor_or_parcel_name ,
457 office_location_code ,
458 office_or_section_name ,
459 rentable_area ,
460 usable_area ,
461 assignable_area ,
462 common_area ,
463 person_id ,
464 employee_name ,
465 cost_center ,
466 employee_number ,
467 employee_type ,
468 employee_category ,
469 employee_position ,
470 employee_project_number ,
471 employee_task_number ,
472 employee_assigned_area ,
473 employee_vacant_area ,
474 employee_assigned_from ,
475 employee_assigned_to ,
476 customer_name ,
477 customer_site ,
478 customer_category ,
479 customer_account ,
480 customer_project_number ,
481 customer_task_number ,
482 customer_assigned_area ,
483 customer_assigned_from ,
484 customer_assigned_to ,
485 loc_attribute_category ,
486 loc_attribute1 ,
487 loc_attribute2 ,
488 loc_attribute3 ,
489 loc_attribute4 ,
490 loc_attribute5 ,
491 loc_attribute6 ,
492 loc_attribute7 ,
493 loc_attribute8 ,
494 loc_attribute9 ,
495 loc_attribute10 ,
496 loc_attribute11 ,
497 loc_attribute12 ,
498 loc_attribute13 ,
499 loc_attribute14 ,
500 loc_attribute15 ,
501 last_update_date ,
505 created_by,
502 last_updated_by ,
503 last_update_login ,
504 creation_date ,
506 request_id )
507 VALUES
508 (v_location_id ,
509 v_location_type ,
510 v_location_name ,
511 v_location_code ,
512 v_space_type ,
513 v_code_data.property_code ,
514 v_code_data.property_name ,
515 v_code_data.building_location_code ,
516 v_code_data.building ,
517 v_code_data.floor_location_code ,
518 v_code_data.FLOOR ,
519 v_code_data.office_location_code ,
520 v_code_data.office ,
521 v_loc_area.rentable_area ,--BUG#2226865
522 v_loc_area.usable_area ,--BUG#2226865
523 v_loc_area.assignable_area ,--BUG#2226865
524 v_loc_area.common_area ,--BUG#2226865
525 v_person_id ,
526 v_emp_data.full_name ,
527 v_cost_center ,
528 v_emp_data.employee_number ,
529 v_emp_data.employee_type ,
530 v_emp_data.employment_category_meaning ,
531 v_emp_data.position ,
532 v_employee_project_number ,
533 v_employee_task_number ,
534 v_space_area.allocated_area_emp , --BUG#2226865
535 v_space_area.vacant_area , --BUG#2226865
536 v_employee_assigned_from ,
537 v_employee_assigned_to ,
538 v_customer_name ,
539 v_customer_site ,
540 v_customer_category ,
541 v_customer_account ,
542 v_customer_project_number ,
543 v_customer_task_number ,
544 v_space_area.allocated_area_cust , --BUG#2226865
545 v_customer_assigned_from ,
546 v_customer_assigned_to ,
547 v_attribute_category ,
548 v_attribute1 ,
549 v_attribute2 ,
550 v_attribute3 ,
551 v_attribute4 ,
552 v_attribute5 ,
553 v_attribute6 ,
554 v_attribute7 ,
555 v_attribute8 ,
556 v_attribute9 ,
557 v_attribute10 ,
558 v_attribute11 ,
559 v_attribute12 ,
560 v_attribute13 ,
561 v_attribute14 ,
562 v_attribute15 ,
563 v_last_update_date ,
564 v_last_updated_by ,
565 v_last_update_login ,
566 v_creation_date ,
567 v_created_by ,
568 l_request_id
569 );
570 END IF;
571 END LOOP;
572 END LOOP;
573 END LOOP;
574 IF dbms_sql.is_open (l_cursor) THEN
575 dbms_sql.close_cursor (l_cursor);
576 END IF;
577 COMMIT;
578 END IF;
579
580 V_LOC_TYPE := NULL; --Bug#2226865
581 IF report_type IN ('CUSTOMER','ALL') THEN
582 l_statement :=
583 'SELECT DISTINCT
584 loc.location_id location_id
585 FROM pn_locations loc,
586 pn_properties p
587 WHERE p.property_id(+) = loc.property_id '
588 ||property_code_where_clause||location_code_where_clause||location_type_where_clause;
589 IF NOT dbms_sql.is_open (l_cursor) THEN
590 l_cursor := dbms_sql.open_cursor;
591 END IF;
592 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
593
594 --------------------------------------
595 --property code conditions
596 IF property_code_low IS NOT NULL AND property_code_high IS NOT NULL THEN
597 dbms_sql.bind_variable(l_cursor,'l_property_code_low',l_property_code_low);
598 dbms_sql.bind_variable(l_cursor,'l_property_code_high',l_property_code_high);
599 ELSIF property_code_low IS NULL AND property_code_high IS NOT NULL THEN
600 dbms_sql.bind_variable(l_cursor,'l_property_code_high',l_property_code_high);
601 ELSIF property_code_low IS NOT NULL AND property_code_high IS NULL THEN
602 dbms_sql.bind_variable(l_cursor,'l_property_code_low',l_property_code_low);
603 END IF;
604
605
606 --location code conditions.....
607 IF location_code_low IS NOT NULL AND location_code_high IS NOT NULL THEN
608 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
609 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
610 ELSIF location_code_low IS NULL AND location_code_high IS NOT NULL THEN
611 dbms_sql.bind_variable(l_cursor,'l_location_code_high',l_location_code_high);
612 ELSIF location_code_low IS NOT NULL AND location_code_high IS NULL THEN
613 dbms_sql.bind_variable(l_cursor,'l_location_code_low',l_location_code_low);
614 END IF;
615
616
617 --location type conditions....
618 IF location_type IS NOT NULL THEN
619 dbms_sql.bind_variable(l_cursor,'l_location_type',l_location_type);
620 END IF;
621
622 dbms_sql.define_column (l_cursor,1,v_location_id_1);
623 l_rows := dbms_sql.execute(l_cursor);
624 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locQuery(-)');
625 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_locLoop(+)');
626 LOOP
627 l_count := dbms_sql.fetch_rows( l_cursor );
628 EXIT WHEN l_count <> 1;
629
630
631 dbms_sql.column_value(l_cursor,1,v_location_id_1);
632 OPEN c_c_assign_pn FOR
633 SELECT DISTINCT
634 loc.location_id location_id,
635 fl1.meaning location_type,
636 NVL(NVL(loc.building, loc.floor), loc.office) location_name,
637 loc.location_code location_code,
638 loc.location_type_lookup_code Loc_type, --BUG#2226865
639 fl.meaning usage_type,
640 loc.rentable_area rentable_area,
641 loc.usable_area usable_area,
642 loc.assignable_area assignable_area,
643 loc.common_area common_area,
644 loc.attribute_category attribute_category,
645 loc.attribute1 attribute1,
646 loc.attribute2 attribute2,
647 loc.attribute3 attribute3,
648 loc.attribute4 attribute4,
649 loc.attribute5 attribute5,
650 loc.attribute6 attribute6,
651 loc.attribute7 attribute7,
652 loc.attribute8 attribute8,
653 loc.attribute9 attribute9,
654 loc.attribute10 attribute10,
655 loc.attribute11 attribute11,
656 loc.attribute12 attribute12,
657 loc.attribute13 attribute13,
658 loc.attribute14 attribute14,
659 loc.attribute15 attribute15,
660 loc.last_update_date last_update_date,
661 loc.last_updated_by last_updated_by,
662 loc.last_update_login last_update_login,
663 loc.creation_date creation_date,
664 loc.created_by created_by
665 FROM pn_locations_all loc,
666 fnd_lookups fl,
667 fnd_lookups fl1
668 WHERE fl.lookup_code(+) = loc.space_type_lookup_code
669 AND fl.lookup_type(+) = 'PN_SPACE_TYPE'
670 AND fl1.lookup_code(+)= loc.location_type_lookup_code
671 AND fl1.lookup_type(+)= 'PN_LOCATION_TYPE'
672 and nvl(trunc(as_of_date),trunc(loc.active_start_date)) between
673 trunc(loc.active_start_date) and trunc(loc.active_end_date);
674 /* Bug 4748773
675 AND loc.location_id IN (SELECT loc.location_id from pn_locations_all loc
676 WHERE loc.active_start_date <= as_of_date
677 AND loc.active_end_date >= as_of_date
678 AND loc.location_id = v_location_id_1 );
679 */
680 LOOP
681 FETCH c_c_assign_pn
682 INTO v_location_id_2 ,
683 v_location_type ,
684 v_location_name ,
685 v_location_code ,
686 v_loc_type , --BUG#2226865
687 v_space_type ,
688 v_loc_area.rentable_area ,--BUG#2226865
689 v_loc_area.usable_area ,--BUG#2226865
690 v_loc_area.assignable_area ,--BUG#2226865
691 v_loc_area.common_area ,--BUG#2226865
692 v_attribute_category ,
693 v_attribute1 ,
694 v_attribute2 ,
695 v_attribute3 ,
696 v_attribute4 ,
697 v_attribute5 ,
698 v_attribute6 ,
699 v_attribute7 ,
700 v_attribute8 ,
701 v_attribute9 ,
702 v_attribute10 ,
703 v_attribute11 ,
704 v_attribute12 ,
705 v_attribute13 ,
706 v_attribute14 ,
707 v_attribute15 ,
708 v_last_update_date ,
709 v_last_updated_by ,
710 v_last_update_login ,
711 v_creation_date ,
712 v_created_by ;
713 EXIT WHEN c_c_assign_pn%NOTFOUND;
714
715 OPEN cust_pn FOR
716 SELECT DISTINCT
717 cust.location_id location_id,
718 0 person_id,
719 NULL cost_center,
720 NULL employee_project_number,
721 NULL employee_task_number,
722 0 employee_assigned_area,
723 NULL employee_assigned_FROM,
724 NULL employee_assigned_to,
725 cust.expense_account_id exp_account,
726 cust.cust_account_id customer_account_id,
727 pa.segment1 customer_project_number,
728 pat.task_name customer_task_number,
729 cust.allocated_area customer_assigned_area,
730 cust.cust_assign_start_date customer_assigned_from,
731 cust.cust_assign_end_date customer_assigned_to,
732 hp.party_name customer_name,
733 hcsu.location customer_site,
734 arl.meaning customer_category
735 FROM hz_parties hp,
736 hz_cust_site_uses_all hcsu,
737 ar_lookups arl,
738 hz_cust_accounts hca,
739 pa_projects_all pa,
740 pa_tasks pat,
744 AND hca.party_id = hp.party_id
741 pn_space_assign_cust_all cust
742 WHERE cust.location_id = v_location_id_2
743 AND hca.cust_account_id = cust.cust_account_id
745 AND arl.lookup_code(+) = hp.category_code
746 AND arl.lookup_type(+) = 'CUSTOMER_CATEGORY'
747 AND hcsu.site_use_id(+) = cust.site_use_id
748 AND pa.project_id(+) = cust.project_id
749 AND pat.task_id(+) = cust.task_id
750 AND as_of_date BETWEEN cust.cust_assign_start_date AND NVL(cust.cust_assign_end_date, l_date);
751 LOOP
752 FETCH cust_pn
753 INTO v_location_id,
754 v_person_id,
755 v_cost_center,
756 v_employee_project_number,
757 v_employee_task_number,
758 v_space_area.allocated_area_emp, --BUG#2226865
759 v_employee_assigned_from,
760 v_employee_assigned_to,
761 v_exp_account,
762 v_customer_account_id,
763 v_customer_project_number,
764 v_customer_task_number,
765 v_space_area.allocated_area_cust, --BUG#2226865
766 v_customer_assigned_from,
767 v_customer_assigned_to,
768 v_customer_name,
769 v_customer_site,
770 v_customer_category;
771
772 v_customer_account := fnd_flex_ext.get_segs('SQLGL','GL#',v_coa_id,v_exp_account);
773 EXIT WHEN cust_pn%NOTFOUND;
774 v_compare_cust:= compare_assign_custloc(v_location_id, v_customer_account, l_request_id);
775 IF NOT (v_compare_cust) THEN
776
777 v_code_data:=pnp_util_func.get_location_name(v_location_id, as_of_date);
778 v_emp_data:= pnp_util_func.get_emp_hr_data(v_person_id);
779 --dbms_output.put_line('fet'||sqlerrm);
780 --------------bug#2226865-----------------
781 IF V_LOC_TYPE in ('BUILDING' ,'LAND','FLOOR','PARCEL') THEN
782 PNP_UTIL_FUNC.get_area(v_location_id , v_loc_type ,NULL,as_of_date,v_loc_area,v_space_area);
783 END IF;
784 -----------bug#2226865--------
785
786
787 INSERT INTO pn_space_assign_loc_itf
788 (
789 location_id ,
790 location_type ,
791 location_name ,
792 location_code ,
793 space_type ,
794 property_code ,
795 property_name ,
796 building_location_code ,
797 building_or_land_name ,
798 floor_location_code ,
799 floor_or_parcel_name ,
800 office_location_code ,
801 office_or_section_name ,
802 rentable_area ,
803 usable_area ,
804 assignable_area ,
805 common_area ,
806 person_id ,
807 employee_name ,
808 cost_center ,
809 employee_number ,
810 employee_type ,
811 employee_category ,
812 employee_position ,
813 employee_project_number ,
814 employee_task_number ,
815 employee_assigned_area ,
816 employee_assigned_from ,
817 employee_assigned_to ,
818 customer_name ,
819 customer_site ,
820 customer_category ,
821 customer_account ,
822 customer_project_number ,
823 customer_task_number ,
824 customer_assigned_area ,
825 customer_vacant_area ,
826 customer_assigned_from ,
827 customer_assigned_to ,
828 loc_attribute_category ,
829 loc_attribute1 ,
830 loc_attribute2 ,
831 loc_attribute3 ,
832 loc_attribute4 ,
833 loc_attribute5 ,
834 loc_attribute6 ,
835 loc_attribute7 ,
836 loc_attribute8 ,
837 loc_attribute9 ,
838 loc_attribute10 ,
839 loc_attribute11 ,
840 loc_attribute12 ,
841 loc_attribute13 ,
842 loc_attribute14 ,
843 loc_attribute15 ,
844 last_update_date ,
845 last_updated_by ,
846 last_update_login ,
847 creation_date ,
848 created_by ,
849 request_id
850 )
851 VALUES
852 (
853 v_location_id ,
854 v_location_type ,
855 v_location_name ,
856 v_location_code ,
857 v_space_type ,
858 v_code_data.property_code ,
859 v_code_data.property_name ,
860 v_code_data.building_location_code ,
861 v_code_data.building ,
862 v_code_data.floor_location_code ,
863 v_code_data.floor ,
864 v_code_data.office_location_code ,
865 v_code_data.office ,
866 v_loc_area.rentable_area , --BUG#2226865
867 v_loc_area.usable_area ,--BUG#2226865
868 v_loc_area.assignable_area ,--BUG#2226865
869 v_loc_area.common_area ,--BUG#2226865
870 v_person_id ,
871 v_emp_data.full_name ,
872 v_cost_center ,
873 v_emp_data.employee_number ,
874 v_emp_data.employee_type ,
875 v_emp_data.employment_category_meaning ,
876 v_emp_data.position ,
877 v_employee_project_number ,
878 v_employee_task_number ,
879 v_space_area.allocated_area_emp , --BUG#2226865
880 v_employee_assigned_from ,
881 v_employee_assigned_to ,
882 v_customer_name ,
883 v_customer_site ,
884 v_customer_category ,
885 v_customer_account ,
886 v_customer_project_number ,
887 v_customer_task_number ,
888 v_space_area.allocated_area_cust , --BUG#2226865
889 v_space_area.vacant_area , --Bug#2226865
890 v_customer_assigned_from ,
891 v_customer_assigned_to ,
892 v_attribute_category ,
893 v_attribute1 ,
894 v_attribute2 ,
895 v_attribute3 ,
896 v_attribute4 ,
897 v_attribute5 ,
898 v_attribute6 ,
899 v_attribute7 ,
900 v_attribute8 ,
901 v_attribute9 ,
902 v_attribute10 ,
903 v_attribute11 ,
904 v_attribute12 ,
905 v_attribute13 ,
906 v_attribute14 ,
907 v_attribute15 ,
908 v_last_update_date ,
909 v_last_updated_by ,
910 v_last_update_login ,
911 v_creation_date ,
912 v_created_by ,
913 l_request_id
914 );
915 END IF;
916 END LOOP;
917 END LOOP;
918 END LOOP;
919 COMMIT;
920 IF dbms_sql.is_open (l_cursor) THEN
921 dbms_sql.close_cursor (l_cursor);
922 END IF;
923
924 END IF;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 retcode:=2;
929 errbuf:=SUBSTR(SQLERRM,1,235);
930 RAISE;
931 END pn_space_assign_loc;
932
933 END pnrx_sp_assign_by_loc;