[Home] [Help]
PACKAGE BODY: APPS.PNRX_SP_ASSIGN_BY_LEASE
Source
1 PACKAGE BODY pnrx_sp_assign_by_lease AS
2 /* $Header: PNRXLESB.pls 120.2 2005/12/01 14:35:27 appldev ship $ */
3
4 FUNCTION compare_assign_emplease(P_LOCATION_ID IN NUMBER,
5 P_PERSON_ID IN NUMBER,
6 P_COST_CENTER IN VARCHAR2,
7 P_REQUEST_ID IN NUMBER)
8 RETURN BOOLEAN IS
9 v_var1 VARCHAR2(1);
10
11 BEGIN
12
13 SELECT 'X'
14 INTO v_var1
15 FROM pn_space_assign_lease_itf
16 WHERE location_id = P_LOCATION_ID
17 AND person_id(+) = P_PERSON_ID
18 AND cost_center = P_COST_CENTER
19 AND request_id = P_REQUEST_ID;
20
21 RETURN TRUE;
22
23 EXCEPTION
24
25 WHEN NO_DATA_FOUND THEN
26
27 RETURN FALSE;
28
29 END compare_assign_emplease;
30
31 FUNCTION compare_assign_custlease(P_LOCATION_ID IN NUMBER,
32 P_ACCOUNT_ID IN VARCHAR2,
33 P_REQUEST_ID IN NUMBER)
34 RETURN BOOLEAN IS
35 v_var2 VARCHAR2(1);
36
37 BEGIN
38
39 SELECT 'Y'
40 INTO v_var2
41 FROM pn_space_assign_lease_itf
42 WHERE location_id = P_LOCATION_ID
43 AND customer_account = P_ACCOUNT_ID
44 AND request_id = P_REQUEST_ID;
45
46 RETURN TRUE;
47
48 EXCEPTION
49
50 WHEN NO_DATA_FOUND THEN
51
52 RETURN FALSE;
53
54 END compare_assign_custlease;
55
56 -------------------------------------------------------------------------------
57 -- PROCDURE : PN_SPACE_ASSIGN_LEASE
58 -- INVOKED FROM :
59 -- PURPOSE :
60 -- HISTORY :
61 -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_distributions with _ALL table.
62 -- 27-OCT-05 sdmahesh o ATG Mandated changes for SQL literals
63 -------------------------------------------------------------------------------
64
65 PROCEDURE pn_space_assign_lease(
66 lease_number_low IN VARCHAR2,
67 lease_number_high 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 emp_pn CUR_TYP;
85 cust_pn CUR_TYP;
86 query_str VARCHAR2(20000);
87 --declare the 'WHERE clauses here........'
88 lease_number_WHERE_clause VARCHAR2(4000);
89 l_one NUMBER default 1;
90 l_two NUMBER default 2;
91 l_three NUMBER default 3;
92 --declare all columns as variables here
93 V_LEASE_ID pn_space_assign_lease_itf.LEASE_ID%TYPE;
94 V_LEASE_NAME pn_space_assign_lease_itf.LEASE_NAME%TYPE;
95 V_LEASE_NUMBER pn_space_assign_lease_itf.LEASE_NUMBER%TYPE;
96 V_LEASE_COM_DATE pn_space_assign_lease_itf.LEASE_COMMENCEMENT_DATE%TYPE;
97 V_LEASE_TERM_DATE pn_space_assign_lease_itf.LEASE_TERMINATION_DATE%TYPE;
98 V_LOCATION_ID pn_space_assign_lease_itf.LOCATION_ID%TYPE;
99 V_LOCATION_ID_1 pn_space_assign_lease_itf.LOCATION_ID%TYPE;
100 V_LOCATION_ID_2 pn_space_assign_lease_itf.LOCATION_ID%TYPE;
101 V_LOCATION_TYPE pn_space_assign_lease_itf.LOCATION_TYPE%TYPE;
102 V_LOCATION_NAME pn_space_assign_lease_itf.LOCATION_NAME%TYPE;
103 V_LOCATION_CODE pn_space_assign_lease_itf.LOCATION_CODE%TYPE;
104 V_SPACE_TYPE pn_space_assign_lease_itf.SPACE_TYPE%TYPE;
105 V_PROPERTY_CODE pn_space_assign_lease_itf.PROPERTY_CODE%TYPE;
106 V_PERSON_ID pn_space_assign_lease_itf.PERSON_ID%TYPE;
107 V_COST_CENTER pn_space_assign_lease_itf.COST_CENTER%TYPE;
108 V_EMPLOYEE_PROJECT_NUMBER pn_space_assign_lease_itf.EMPLOYEE_PROJECT_NUMBER%TYPE;
109 V_EMPLOYEE_TASK_NUMBER pn_space_assign_lease_itf.EMPLOYEE_TASK_NUMBER%TYPE;
110 V_EMPLOYEE_ASSIGNED_FROM pn_space_assign_lease_itf.EMPLOYEE_ASSIGNED_FROM%TYPE;
111 V_EMPLOYEE_ASSIGNED_TO pn_space_assign_lease_itf.EMPLOYEE_ASSIGNED_TO%TYPE;
112 V_CUSTOMER_ACCOUNT pn_space_assign_lease_itf.CUSTOMER_ACCOUNT%TYPE;
113 V_EXP_ACCOUNT NUMBER;
114 V_CUSTOMER_ACCOUNT_ID NUMBER;
115 V_CUSTOMER_PROJECT_NUMBER pn_space_assign_lease_itf.CUSTOMER_PROJECT_NUMBER%TYPE;
116 V_CUSTOMER_TASK_NUMBER pn_space_assign_lease_itf.CUSTOMER_TASK_NUMBER%TYPE;
117 V_CUSTOMER_ASSIGNED_FROM pn_space_assign_lease_itf.CUSTOMER_ASSIGNED_FROM%TYPE;
118 V_CUSTOMER_ASSIGNED_TO pn_space_assign_lease_itf.CUSTOMER_ASSIGNED_TO%TYPE;
119 V_CUSTOMER_NAME pn_space_assign_lease_itf.CUSTOMER_NAME%TYPE;
120 V_CUSTOMER_SITE pn_space_assign_lease_itf.CUSTOMER_SITE%TYPE;
121 V_CUSTOMER_CATEGORY pn_space_assign_lease_itf.CUSTOMER_CATEGORY%TYPE;
122 V_RENTABLE_AREA pn_space_assign_lease_itf.RENTABLE_AREA%TYPE;
123 V_USABLE_AREA pn_space_assign_lease_itf.USABLE_AREA%TYPE;
124 V_ASSIGNABLE_AREA pn_space_assign_lease_itf.ASSIGNABLE_AREA%TYPE;
125 V_COMMON_AREA pn_space_assign_lease_itf.COMMON_AREA%TYPE;
126 V_EMPLOYEE_ASSIGNED_AREA pn_space_assign_lease_itf.EMPLOYEE_ASSIGNED_AREA%TYPE;
127 V_CUSTOMER_ASSIGNED_AREA pn_space_assign_lease_itf.CUSTOMER_ASSIGNED_AREA%TYPE;
128 V_VACANT_AREA NUMBER;
129 V_ATTRIBUTE_CATEGORY pn_space_assign_lease_itf.TEN_ATTRIBUTE_CATEGORY%TYPE;
130 V_ATTRIBUTE1 pn_space_assign_lease_itf.TEN_ATTRIBUTE1%TYPE;
131 V_ATTRIBUTE2 pn_space_assign_lease_itf.TEN_ATTRIBUTE2%TYPE;
132 V_ATTRIBUTE3 pn_space_assign_lease_itf.TEN_ATTRIBUTE3%TYPE;
133 V_ATTRIBUTE4 pn_space_assign_lease_itf.TEN_ATTRIBUTE4%TYPE;
134 V_ATTRIBUTE5 pn_space_assign_lease_itf.TEN_ATTRIBUTE5%TYPE;
135 V_ATTRIBUTE6 pn_space_assign_lease_itf.TEN_ATTRIBUTE6%TYPE;
136 V_ATTRIBUTE7 pn_space_assign_lease_itf.TEN_ATTRIBUTE7%TYPE;
137 V_ATTRIBUTE8 pn_space_assign_lease_itf.TEN_ATTRIBUTE8%TYPE;
138 V_ATTRIBUTE9 pn_space_assign_lease_itf.TEN_ATTRIBUTE9%TYPE;
139 V_ATTRIBUTE10 pn_space_assign_lease_itf.TEN_ATTRIBUTE10%TYPE;
140 V_ATTRIBUTE11 pn_space_assign_lease_itf.TEN_ATTRIBUTE11%TYPE;
141 V_ATTRIBUTE12 pn_space_assign_lease_itf.TEN_ATTRIBUTE12%TYPE;
142 V_ATTRIBUTE13 pn_space_assign_lease_itf.TEN_ATTRIBUTE13%TYPE;
143 V_ATTRIBUTE14 pn_space_assign_lease_itf.TEN_ATTRIBUTE14%TYPE;
144 V_ATTRIBUTE15 pn_space_assign_lease_itf.TEN_ATTRIBUTE15%TYPE;
145 V_LAST_UPDATE_DATE pn_space_assign_lease_itf.LAST_UPDATE_DATE%TYPE;
146 V_LAST_UPDATED_BY pn_space_assign_lease_itf.LAST_UPDATED_BY%TYPE;
147 V_LAST_UPDATE_LOGIN pn_space_assign_lease_itf.LAST_UPDATE_LOGIN%TYPE;
148 V_CREATION_DATE pn_space_assign_lease_itf.CREATION_DATE%TYPE;
149 V_CREATED_BY pn_space_assign_lease_itf.CREATED_BY%TYPE;
150 --declare the record type for the function here.........
151 v_emp_data PNP_UTIL_FUNC.emp_hr_data_rec := NULL;
152 v_code_data PNP_UTIL_FUNC.location_name_rec := NULL;
153 l_date DATE:=fnd_date.canonical_to_date(
154 '4712/12/31 00:00:00' );
155 v_compare_emp BOOLEAN;
156 v_compare_cust BOOLEAN;
157 v_coa_id NUMBER;
158 v_loc_type VARCHAR2(100) ; --BUG#2226865
159 v_loc_area PNP_UTIL_FUNC.PN_LOCATION_AREA_REC; --bug#2226865
160 v_space_area PNP_UTIL_FUNC.PN_SPACE_AREA_REC; --bug#2226865
161 l_count_1 NUMBER := 0;
162 l_count_2 NUMBER := 0;
163 l_count_3 NUMBER := 0;
164
165 l_lease_number_low VARCHAR2(30);
166 l_lease_number_high VARCHAR2(30);
167 l_statement VARCHAR2(10000);
168 l_cursor INTEGER;
169 l_rows INTEGER;
170 l_count INTEGER;
171
172 BEGIN
173
174 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_leaseConditions(+)');
175 --Initialise status parameters...
176 retcode:=0;
177 errbuf:='';
178 fnd_profile.get('LOGIN_ID', l_login_id);
179 SELECT chart_of_accounts_id INTO v_coa_id
180 FROM GL_SETS_OF_BOOKS
181 WHERE set_of_books_id= TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID',
182 pn_mo_cache_utils.get_current_org_id));
183 l_lease_number_low := lease_number_low;
184 l_lease_number_high := lease_number_high;
185 --lease number conditions.....
186 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
187 lease_number_WHERE_clause := ' AND les.lease_num BETWEEN :l_lease_number_low AND
188 :l_lease_number_high';
189 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
190 lease_number_WHERE_clause := ' AND les.lease_num = :l_lease_number_high';
191 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
192 lease_number_WHERE_clause := ' AND les.lease_num = :l_lease_number_low';
193 ELSE
194 lease_number_WHERE_clause := ' AND 1 = 1 ';
195 END IF;
196
197 --validate report type...
198 PNP_DEBUG_PKG.put_log_msg('report_type:'||report_type);
199 IF report_type IN ('EMPLOYEE','ALL') THEN
200 pnp_debug_pkg.put_log_msg('pn_sp_assign_Emp(+)');
201 l_statement :=
202 'SELECT
203 distinct
204 ten.location_id LOCATION_ID,
205 les.lease_id LEASE_ID,
206 les.name LEASE_NAME,
207 les.lease_num LEASE_NUMBER,
208 lda.lease_commencement_date LEASE_COMMENCEMENT_DATE,
209 lda.lease_termination_date LEASE_TERMINATION_DATE,
210 ten.attribute_category ATTRIBUTE_CATEGORY,
211 ten.attribute1 ATTRIBUTE1,
212 ten.attribute2 ATTRIBUTE2,
213 ten.attribute3 ATTRIBUTE3,
214 ten.attribute4 ATTRIBUTE4,
215 ten.attribute5 ATTRIBUTE5,
216 ten.attribute6 ATTRIBUTE6,
217 ten.attribute7 ATTRIBUTE7,
218 ten.attribute8 ATTRIBUTE8,
219 ten.attribute9 ATTRIBUTE9,
220 ten.attribute10 ATTRIBUTE10,
221 ten.attribute11 ATTRIBUTE11,
222 ten.attribute12 ATTRIBUTE12,
223 ten.attribute13 ATTRIBUTE13,
224 ten.attribute14 ATTRIBUTE14,
225 ten.attribute15 ATTRIBUTE15
226 FROM pn_leases les,
227 pn_lease_details_all lda,
228 pn_tenancies_all ten
229 WHERE ten.lease_id = les.lease_id
230 AND ten.lease_id = lda.lease_id '
231 ||lease_number_WHERE_clause;
232 l_cursor := dbms_sql.open_cursor;
233 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
234
235
236 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
237 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
238 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
239 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
240 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
241 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
242 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
243 END IF;
244
245 dbms_sql.define_column (l_cursor,1,V_LOCATION_ID_1);
246 dbms_sql.define_column (l_cursor,2,V_LEASE_ID);
247 dbms_sql.define_column (l_cursor,3,V_LEASE_NAME,50);
248 dbms_sql.define_column (l_cursor,4,V_LEASE_NUMBER,30);
249 dbms_sql.define_column (l_cursor,5,V_LEASE_COM_DATE);
250 dbms_sql.define_column (l_cursor,6,V_LEASE_TERM_DATE);
251 dbms_sql.define_column (l_cursor,7,V_ATTRIBUTE_CATEGORY,30);
252 dbms_sql.define_column (l_cursor,8,V_ATTRIBUTE1,150);
253 dbms_sql.define_column (l_cursor,9,V_ATTRIBUTE2,150);
254 dbms_sql.define_column (l_cursor,10,V_ATTRIBUTE3,150);
255 dbms_sql.define_column (l_cursor,11,V_ATTRIBUTE4,150);
256 dbms_sql.define_column (l_cursor,12,V_ATTRIBUTE5,150);
257 dbms_sql.define_column (l_cursor,13,V_ATTRIBUTE6,150);
258 dbms_sql.define_column (l_cursor,14,V_ATTRIBUTE7,150);
259 dbms_sql.define_column (l_cursor,15,V_ATTRIBUTE8,150);
260 dbms_sql.define_column (l_cursor,16,V_ATTRIBUTE9,150);
261 dbms_sql.define_column (l_cursor,17,V_ATTRIBUTE10,150);
262 dbms_sql.define_column (l_cursor,18,V_ATTRIBUTE11,150);
263 dbms_sql.define_column (l_cursor,19,V_ATTRIBUTE12,150);
264 dbms_sql.define_column (l_cursor,20,V_ATTRIBUTE13,150);
265 dbms_sql.define_column (l_cursor,21,V_ATTRIBUTE14,150);
266 dbms_sql.define_column (l_cursor,22,V_ATTRIBUTE15,150);
267
268 l_rows := dbms_sql.execute(l_cursor);
269
270 pnp_debug_pkg.put_log_msg('pn_sp_assign_Emp: c_e_pn Loop(+)');
271 l_count_1 := 0;
272 LOOP
273 l_count_1 := l_count_1 + 1;
274 l_count := dbms_sql.fetch_rows( l_cursor );
275 EXIT WHEN l_count <> 1;
276
277 dbms_sql.column_value(l_cursor,1,V_LOCATION_ID_1);
278 dbms_sql.column_value(l_cursor,2,V_LEASE_ID);
279 dbms_sql.column_value(l_cursor,3,V_LEASE_NAME);
280 dbms_sql.column_value(l_cursor,4,V_LEASE_NUMBER);
281 dbms_sql.column_value(l_cursor,5,V_LEASE_COM_DATE);
282 dbms_sql.column_value(l_cursor,6,V_LEASE_TERM_DATE);
283 dbms_sql.column_value(l_cursor,7,V_ATTRIBUTE_CATEGORY);
284 dbms_sql.column_value(l_cursor,8,V_ATTRIBUTE1);
285 dbms_sql.column_value(l_cursor,9,V_ATTRIBUTE2);
286 dbms_sql.column_value(l_cursor,10,V_ATTRIBUTE3);
287 dbms_sql.column_value(l_cursor,11,V_ATTRIBUTE4);
288 dbms_sql.column_value(l_cursor,12,V_ATTRIBUTE5);
289 dbms_sql.column_value(l_cursor,13,V_ATTRIBUTE6);
290 dbms_sql.column_value(l_cursor,14,V_ATTRIBUTE7);
291 dbms_sql.column_value(l_cursor,15,V_ATTRIBUTE8);
292 dbms_sql.column_value(l_cursor,16,V_ATTRIBUTE9);
293 dbms_sql.column_value(l_cursor,17,V_ATTRIBUTE10);
294 dbms_sql.column_value(l_cursor,18,V_ATTRIBUTE11);
295 dbms_sql.column_value(l_cursor,19,V_ATTRIBUTE12);
296 dbms_sql.column_value(l_cursor,20,V_ATTRIBUTE13);
297 dbms_sql.column_value(l_cursor,21,V_ATTRIBUTE14);
298 dbms_sql.column_value(l_cursor,22,V_ATTRIBUTE15);
299
300 OPEN c_e_assign_pn FOR
301 SELECT
302 distinct
303 loc.location_id LOCATION_ID,
304 fl1.meaning LOCATION_TYPE,
305 NVL(NVL(loc.building, loc.floor), loc.office) LOCATION_NAME,
306 loc.location_code LOCATION_CODE,
307 loc.location_type_lookup_code Loc_type , --BUG#2226865
308 fl.meaning USAGE_TYPE,
309 loc.rentable_area RENTABLE_AREA,
310 loc.usable_area USABLE_AREA,
311 loc.assignable_area ASSIGNABLE_AREA,
312 loc.common_area COMMON_AREA,
313 loc.last_update_date LAST_UPDATE_DATE,
314 loc.last_updated_by LAST_UPDATED_BY,
315 loc.last_update_login LAST_UPDATE_LOGIN,
316 loc.creation_date CREATION_DATE,
317 loc.created_by CREATED_BY
318 FROM pn_locations_all loc,
319 fnd_lookups fl,
320 fnd_lookups fl1
321 WHERE fl.lookup_code(+) = loc.space_type_lookup_code
322 AND fl.lookup_type(+) = 'PN_SPACE_TYPE'
323 AND fl1.lookup_code(+)= loc.location_type_lookup_code
324 AND fl1.lookup_type(+)= 'PN_LOCATION_TYPE'
325 AND loc.location_id IN (SELECT loc.location_id FROM pn_locations_all loc
326 WHERE active_start_date <= as_of_date
327 AND active_end_date >= as_of_date
328 START WITH loc.location_id = V_LOCATION_ID_1
329 CONNECT BY PRIOR loc.location_id = loc.parent_location_id
330 AND as_of_date between prior active_start_date AND prior active_end_date);
331 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_Emp: c_e_pn# '||l_count_1||': c_e_assign_pn Loop(+)');
332 l_count_2 := 0;
333 LOOP
334 l_count_2 := l_count_2 + 1;
335 FETCH c_e_assign_pn into V_LOCATION_ID_2 ,
336 V_LOCATION_TYPE ,
337 V_LOCATION_NAME ,
338 V_LOCATION_CODE ,
339 v_loc_type , --BUG#2226865
340 V_SPACE_TYPE ,
341 V_LOC_AREA.RENTABLE_AREA , --bug#2226865
342 V_LOC_AREA.USABLE_AREA , --bug#2226865
343 V_LOC_AREA.ASSIGNABLE_AREA , --bug#2226865
344 V_LOC_AREA.COMMON_AREA , --bug#2226865
345 V_LAST_UPDATE_DATE ,
346 V_LAST_UPDATED_BY ,
347 V_LAST_UPDATE_LOGIN ,
348 V_CREATION_DATE ,
349 V_CREATED_BY ;
350 EXIT WHEN c_e_assign_pn%NOTFOUND;
351 OPEN emp_pn FOR
352 SELECT
353 distinct
354 emp.location_id LOCATION_ID,
355 emp.person_id PERSON_ID,
356 emp.cost_center_code COST_CENTER,
357 pa.segment1 EMPLOYEE_PROJECT_NUMBER,
358 pat.task_name EMPLOYEE_TASK_NUMBER,
359 emp.allocated_area EMPLOYEE_ASSIGNED_AREA,
360 emp.emp_assign_start_date EMPLOYEE_ASSIGNED_FROM,
361 emp.emp_assign_end_date EMPLOYEE_ASSIGNED_TO,
362 NULL CUSTOMER_ACCOUNT,
363 NULL CUSTOMER_PROJECT_NUMBER,
364 NULL CUSTOMER_TASK_NUMBER,
365 0 CUSTOMER_ASSIGNED_AREA,
366 NULL CUSTOMER_ASSIGNED_FROM,
367 NULL CUSTOMER_ASSIGNED_TO,
368 NULL CUSTOMER_NAME,
369 NULL CUSTOMER_SITE,
370 NULL CUSTOMER_CATEGORY
371 FROM pa_projects_all pa,
372 pa_tasks pat,
373 pn_space_assign_emp_all emp
374 WHERE emp.location_id = V_LOCATION_ID_2
375 AND pa.project_id(+) = emp.project_id
376 AND pat.task_id(+) = emp.task_id
377 AND as_of_date between emp.emp_assign_start_date AND NVL(emp.emp_assign_end_date, l_date);
378 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_Emp: c_e_pn# '||l_count_1||': c_e_assign_pn# '
379 ||l_count_2||': emp_pn Loop(+)');
380 l_count_3 := 0;
381 LOOP
382 l_count_3 := l_count_3 + 1;
383 FETCH emp_pn into V_LOCATION_ID,
384 V_PERSON_ID,
385 V_COST_CENTER,
386 V_EMPLOYEE_PROJECT_NUMBER,
387 V_EMPLOYEE_TASK_NUMBER,
388 V_SPACE_AREA.ALLOCATED_AREA_EMP, --bug#2226865
389 V_EMPLOYEE_ASSIGNED_FROM,
390 V_EMPLOYEE_ASSIGNED_TO,
391 V_CUSTOMER_ACCOUNT,
392 V_CUSTOMER_PROJECT_NUMBER,
393 V_CUSTOMER_TASK_NUMBER,
394 V_SPACE_AREA.ALLOCATED_AREA_CUST, --bug#2226865
395 V_CUSTOMER_ASSIGNED_FROM,
396 V_CUSTOMER_ASSIGNED_TO,
397 V_CUSTOMER_NAME,
398 V_CUSTOMER_SITE,
399 V_CUSTOMER_CATEGORY;
400 EXIT WHEN emp_pn%notfound;
401 v_compare_emp:=compare_assign_emplease(V_LOCATION_ID, V_PERSON_ID, V_COST_CENTER, l_request_id);
402 IF NOT(v_compare_emp) THEN
403
404 v_code_data:=pnp_util_func.get_location_name(V_LOCATION_ID, AS_OF_DATE);
405 v_emp_data:= pnp_util_func.get_emp_hr_data(V_PERSON_ID);
406 --------------bug#2226865-----------------
407 IF V_LOC_TYPE in ('BUILDING' ,'LAND','FLOOR','PARCEL') THEN
408 PNP_UTIL_FUNC.get_area(v_location_id , v_loc_type ,NULL,AS_OF_DATE,V_LOC_AREA,V_SPACE_AREA);
409 END IF;
410 -----------bug#2226865--------
411 INSERT INTO pn_space_assign_lease_itf
412 (LEASE_ID ,
413 LEASE_NAME ,
414 LEASE_NUMBER ,
415 LEASE_COMMENCEMENT_DATE ,
416 LEASE_TERMINATION_DATE ,
417 LOCATION_ID ,
418 LOCATION_TYPE ,
419 LOCATION_NAME ,
420 LOCATION_CODE ,
421 SPACE_TYPE ,
422 PROPERTY_CODE ,
423 PROPERTY_NAME ,
424 BUILDING_LOCATION_CODE ,
425 BUILDING_OR_LAND_NAME ,
426 FLOOR_LOCATION_CODE ,
427 FLOOR_OR_PARCEL_NAME ,
428 OFFICE_LOCATION_CODE ,
429 OFFICE_OR_SECTION_NAME ,
430 RENTABLE_AREA ,
431 USABLE_AREA ,
432 ASSIGNABLE_AREA ,
433 COMMON_AREA ,
434 PERSON_ID ,
435 EMPLOYEE_NAME ,
436 COST_CENTER ,
437 EMPLOYEE_NUMBER ,
438 EMPLOYEE_TYPE ,
439 EMPLOYEE_CATEGORY ,
440 EMPLOYEE_POSITION ,
441 EMPLOYEE_PROJECT_NUMBER ,
442 EMPLOYEE_TASK_NUMBER ,
443 EMPLOYEE_ASSIGNED_AREA ,
444 EMPLOYEE_VACANT_AREA ,
445 EMPLOYEE_ASSIGNED_FROM ,
446 EMPLOYEE_ASSIGNED_TO ,
447 CUSTOMER_NAME ,
448 CUSTOMER_SITE ,
449 CUSTOMER_CATEGORY ,
450 CUSTOMER_ACCOUNT ,
451 CUSTOMER_PROJECT_NUMBER ,
452 CUSTOMER_TASK_NUMBER ,
453 CUSTOMER_ASSIGNED_AREA ,
454 CUSTOMER_ASSIGNED_FROM ,
455 CUSTOMER_ASSIGNED_TO ,
456 TEN_ATTRIBUTE_CATEGORY ,
457 TEN_ATTRIBUTE1 ,
458 TEN_ATTRIBUTE2 ,
459 TEN_ATTRIBUTE3 ,
460 TEN_ATTRIBUTE4 ,
461 TEN_ATTRIBUTE5 ,
462 TEN_ATTRIBUTE6 ,
463 TEN_ATTRIBUTE7 ,
464 TEN_ATTRIBUTE8 ,
465 TEN_ATTRIBUTE9 ,
466 TEN_ATTRIBUTE10 ,
467 TEN_ATTRIBUTE11 ,
468 TEN_ATTRIBUTE12 ,
469 TEN_ATTRIBUTE13 ,
470 TEN_ATTRIBUTE14 ,
471 TEN_ATTRIBUTE15 ,
472 LAST_UPDATE_DATE ,
473 LAST_UPDATED_BY ,
474 LAST_UPDATE_LOGIN ,
475 CREATION_DATE ,
476 CREATED_BY,
477 REQUEST_ID )
478 VALUES
479 ( V_LEASE_ID ,
480 V_LEASE_NAME ,
481 V_LEASE_NUMBER ,
482 V_LEASE_COM_DATE ,
483 V_LEASE_TERM_DATE ,
484 V_LOCATION_ID ,
485 V_LOCATION_TYPE ,
486 V_LOCATION_NAME ,
487 V_LOCATION_CODE ,
488 V_SPACE_TYPE ,
489 v_code_data.PROPERTY_CODE ,
490 v_code_data.PROPERTY_NAME ,
491 v_code_data.BUILDING_LOCATION_CODE ,
492 v_code_data.BUILDING ,
493 v_code_data.FLOOR_LOCATION_CODE ,
494 v_code_data.FLOOR ,
495 v_code_data.OFFICE_LOCATION_CODE ,
496 v_code_data.OFFICE ,
497 V_LOC_AREA.RENTABLE_AREA ,--bug#2226865
498 V_LOC_AREA.USABLE_AREA ,--bug#2226865
499 V_LOC_AREA.ASSIGNABLE_AREA ,--bug#2226865
500 V_LOC_AREA.COMMON_AREA ,--bug#2226865
501 V_PERSON_ID ,
502 v_emp_data.FULL_NAME ,
503 V_COST_CENTER ,
504 v_emp_data.EMPLOYEE_NUMBER ,
505 v_emp_data.EMPLOYEE_TYPE ,
506 v_emp_data.EMPLOYMENT_CATEGORY_MEANING ,
507 v_emp_data.POSITION ,
508 V_EMPLOYEE_PROJECT_NUMBER ,
509 V_EMPLOYEE_TASK_NUMBER ,
510 V_SPACE_AREA.ALLOCATED_AREA_EMP , --bug#2226865
511 V_SPACE_AREA.Vacant_area , --bug#2226865
512 V_EMPLOYEE_ASSIGNED_FROM ,
513 V_EMPLOYEE_ASSIGNED_TO ,
514 V_CUSTOMER_NAME ,
515 V_CUSTOMER_SITE ,
516 V_CUSTOMER_CATEGORY ,
517 V_CUSTOMER_ACCOUNT ,
518 V_CUSTOMER_PROJECT_NUMBER ,
519 V_CUSTOMER_TASK_NUMBER ,
520 V_SPACE_AREA.ALLOCATED_AREA_CUST , --bug#2226865
521 V_CUSTOMER_ASSIGNED_FROM ,
522 V_CUSTOMER_ASSIGNED_TO ,
523 V_ATTRIBUTE_CATEGORY ,
524 V_ATTRIBUTE1 ,
525 V_ATTRIBUTE2 ,
526 V_ATTRIBUTE3 ,
527 V_ATTRIBUTE4 ,
528 V_ATTRIBUTE5 ,
529 V_ATTRIBUTE6 ,
530 V_ATTRIBUTE7 ,
531 V_ATTRIBUTE8 ,
532 V_ATTRIBUTE9 ,
533 V_ATTRIBUTE10 ,
534 V_ATTRIBUTE11 ,
535 V_ATTRIBUTE12 ,
536 V_ATTRIBUTE13 ,
537 V_ATTRIBUTE14 ,
538 V_ATTRIBUTE15 ,
539 V_LAST_UPDATE_DATE ,
540 V_LAST_UPDATED_BY ,
541 V_LAST_UPDATE_LOGIN ,
542 V_CREATION_DATE ,
543 V_CREATED_BY ,
544 l_request_id );
545 END IF;
546 END LOOP;
547 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_Emp: c_e_pn# '||l_count_1||': c_e_assign_pn# '
548 ||l_count_2||': emp_pn Loop(-)');
549 END LOOP;
550 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_Emp: c_e_pn# '||l_count_1||': c_e_assign_pn Loop(-)');
551 END LOOP;
552 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_Emp: c_e_pn Loop(-)');
553 IF dbms_sql.is_open (l_cursor) THEN
554 dbms_sql.close_cursor (l_cursor);
555 END IF;
556
557 COMMIT;
558 pnp_debug_pkg.put_log_msg('pn_sp_assign_Emp(-)');
559 END IF;
560
561
562 V_LOC_TYPE := null ; ----Added BUG#2226865
563 l_count_1 := 0;
564 l_count_2 := 0;
565 l_count_3 := 0;
566
567 IF report_type IN ('CUSTOMER','ALL') THEN
568 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust(+)');
569
570 l_statement :=
571 'SELECT
572 distinct
573 ten.location_id LOCATION_ID,
574 les.lease_id LEASE_ID,
575 les.name LEASE_NAME,
576 les.lease_num LEASE_NUMBER,
577 lda.lease_commencement_date LEASE_COMMENCEMENT_DATE,
578 lda.lease_termination_date LEASE_TERMINATION_DATE,
579 ten.attribute_category ATTRIBUTE_CATEGORY,
580 ten.attribute1 ATTRIBUTE1,
581 ten.attribute2 ATTRIBUTE2,
582 ten.attribute3 ATTRIBUTE3,
583 ten.attribute4 ATTRIBUTE4,
584 ten.attribute5 ATTRIBUTE5,
585 ten.attribute6 ATTRIBUTE6,
586 ten.attribute7 ATTRIBUTE7,
587 ten.attribute8 ATTRIBUTE8,
588 ten.attribute9 ATTRIBUTE9,
589 ten.attribute10 ATTRIBUTE10,
590 ten.attribute11 ATTRIBUTE11,
591 ten.attribute12 ATTRIBUTE12,
592 ten.attribute13 ATTRIBUTE13,
593 ten.attribute14 ATTRIBUTE14,
594 ten.attribute15 ATTRIBUTE15
595 FROM pn_leases les,
596 pn_lease_details_all lda,
597 pn_tenancies_all ten
598 WHERE ten.lease_id = les.lease_id
599 AND ten.lease_id = lda.lease_id '
600 ||lease_number_WHERE_clause;
601
602 IF NOT dbms_sql.is_open (l_cursor) THEN
603 l_cursor := dbms_sql.open_cursor;
604 END IF;
605
606
607 dbms_sql.parse(l_cursor,l_statement,dbms_sql.native);
608 IF lease_number_low IS NOT NULL AND lease_number_high IS NOT NULL THEN
609 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
610 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
611 ELSIF lease_number_low IS NULL AND lease_number_high IS NOT NULL THEN
612 dbms_sql.bind_variable(l_cursor,'l_lease_number_high',l_lease_number_high);
613 ELSIF lease_number_low IS NOT NULL AND lease_number_high IS NULL THEN
614 dbms_sql.bind_variable(l_cursor,'l_lease_number_low',l_lease_number_low);
615 END IF;
616
617 dbms_sql.define_column (l_cursor,1,V_LOCATION_ID_1);
618 dbms_sql.define_column (l_cursor,2,V_LEASE_ID);
619 dbms_sql.define_column (l_cursor,3,V_LEASE_NAME,50);
620 dbms_sql.define_column (l_cursor,4,V_LEASE_NUMBER,30);
621 dbms_sql.define_column (l_cursor,5,V_LEASE_COM_DATE);
622 dbms_sql.define_column (l_cursor,6,V_LEASE_TERM_DATE);
623 dbms_sql.define_column (l_cursor,7,V_ATTRIBUTE_CATEGORY,30);
624 dbms_sql.define_column (l_cursor,8,V_ATTRIBUTE1,150);
625 dbms_sql.define_column (l_cursor,9,V_ATTRIBUTE2,150);
626 dbms_sql.define_column (l_cursor,10,V_ATTRIBUTE3,150);
627 dbms_sql.define_column (l_cursor,11,V_ATTRIBUTE4,150);
628 dbms_sql.define_column (l_cursor,12,V_ATTRIBUTE5,150);
629 dbms_sql.define_column (l_cursor,13,V_ATTRIBUTE6,150);
630 dbms_sql.define_column (l_cursor,14,V_ATTRIBUTE7,150);
631 dbms_sql.define_column (l_cursor,15,V_ATTRIBUTE8,150);
632 dbms_sql.define_column (l_cursor,16,V_ATTRIBUTE9,150);
633 dbms_sql.define_column (l_cursor,17,V_ATTRIBUTE10,150);
634 dbms_sql.define_column (l_cursor,18,V_ATTRIBUTE11,150);
635 dbms_sql.define_column (l_cursor,19,V_ATTRIBUTE12,150);
636 dbms_sql.define_column (l_cursor,20,V_ATTRIBUTE13,150);
637 dbms_sql.define_column (l_cursor,21,V_ATTRIBUTE14,150);
638 dbms_sql.define_column (l_cursor,22,V_ATTRIBUTE15,150);
639
640
641
642 l_rows := dbms_sql.execute(l_cursor);
643
644 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust: c_c_pn Loop(+)');
645 l_count_1 := 0;
646 LOOP
647 l_count_1 := l_count_1 + 1;
648 l_count := dbms_sql.fetch_rows( l_cursor );
649 EXIT WHEN l_count <> 1;
650
651 dbms_sql.column_value(l_cursor,1,V_LOCATION_ID_1);
652 dbms_sql.column_value(l_cursor,2,V_LEASE_ID);
653 dbms_sql.column_value(l_cursor,3,V_LEASE_NAME);
654 dbms_sql.column_value(l_cursor,4,V_LEASE_NUMBER);
655 dbms_sql.column_value(l_cursor,5,V_LEASE_COM_DATE);
656 dbms_sql.column_value(l_cursor,6,V_LEASE_TERM_DATE);
657 dbms_sql.column_value(l_cursor,7,V_ATTRIBUTE_CATEGORY);
658 dbms_sql.column_value(l_cursor,8,V_ATTRIBUTE1);
659 dbms_sql.column_value(l_cursor,9,V_ATTRIBUTE2);
660 dbms_sql.column_value(l_cursor,10,V_ATTRIBUTE3);
661 dbms_sql.column_value(l_cursor,11,V_ATTRIBUTE4);
662 dbms_sql.column_value(l_cursor,12,V_ATTRIBUTE5);
663 dbms_sql.column_value(l_cursor,13,V_ATTRIBUTE6);
664 dbms_sql.column_value(l_cursor,14,V_ATTRIBUTE7);
665 dbms_sql.column_value(l_cursor,15,V_ATTRIBUTE8);
666 dbms_sql.column_value(l_cursor,16,V_ATTRIBUTE9);
667 dbms_sql.column_value(l_cursor,17,V_ATTRIBUTE10);
668 dbms_sql.column_value(l_cursor,18,V_ATTRIBUTE11);
669 dbms_sql.column_value(l_cursor,19,V_ATTRIBUTE12);
670 dbms_sql.column_value(l_cursor,20,V_ATTRIBUTE13);
671 dbms_sql.column_value(l_cursor,21,V_ATTRIBUTE14);
672 dbms_sql.column_value(l_cursor,22,V_ATTRIBUTE15);
673
674 OPEN c_c_assign_pn FOR
675 SELECT
676 distinct
677 loc.location_id LOCATION_ID,
678 fl1.meaning LOCATION_TYPE,
679 NVL(NVL(loc.building, loc.floor), loc.office) LOCATION_NAME,
680 loc.location_code LOCATION_CODE,
681 loc.location_type_lookup_code Loc_type , --BUG#2226865
682 fl.meaning USAGE_TYPE,
683 loc.rentable_area RENTABLE_AREA,
684 loc.usable_area USABLE_AREA,
685 loc.assignable_area ASSIGNABLE_AREA,
686 loc.common_area COMMON_AREA,
687 loc.last_update_date LAST_UPDATE_DATE,
688 loc.last_updated_by LAST_UPDATED_BY,
689 loc.last_update_login LAST_UPDATE_LOGIN,
690 loc.creation_date CREATION_DATE,
691 loc.created_by CREATED_BY
692 FROM pn_locations_all loc,
693 fnd_lookups fl,
694 fnd_lookups fl1
695 WHERE fl.lookup_code(+) = loc.space_type_lookup_code
696 AND fl.lookup_type(+) = 'PN_SPACE_TYPE'
697 AND fl1.lookup_code(+)= loc.location_type_lookup_code
698 AND fl1.lookup_type(+)= 'PN_LOCATION_TYPE'
699 AND loc.location_id in (select loc.location_id FROM pn_locations_all loc
700 WHERE active_start_date <= as_of_date
701 AND active_end_date >= as_of_date
702 START WITH loc.location_id = V_LOCATION_ID_1
703 CONNECT BY PRIOR loc.location_id = loc.parent_location_id
704 AND as_of_date between prior active_start_date AND prior active_end_date);
705
706 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust: c_c_pn# '||l_count_1||': c_c_assign_pn Loop(+)');
707 l_count_2 := 0;
708 LOOP
709 l_count_2 := l_count_2 + 1;
710 FETCH c_c_assign_pn into V_LOCATION_ID_2 ,
711 V_LOCATION_TYPE ,
712 V_LOCATION_NAME ,
713 V_LOCATION_CODE ,
714 v_loc_type , --BUG#2226865
715 V_SPACE_TYPE ,
716 V_LOC_AREA.RENTABLE_AREA , --bug#2226865
717 V_LOC_AREA.USABLE_AREA , --bug#2226865
718 V_LOC_AREA.ASSIGNABLE_AREA , --bug#2226865
719 V_LOC_AREA.COMMON_AREA , --bug#2226865
720 V_LAST_UPDATE_DATE ,
721 V_LAST_UPDATED_BY ,
722 V_LAST_UPDATE_LOGIN ,
723 V_CREATION_DATE ,
724 V_CREATED_BY ;
725 EXIT WHEN c_c_assign_pn%notfound;
726 OPEN cust_pn FOR
727 SELECT
728 distinct
729 cust.location_id LOCATION_ID,
730 0 PERSON_ID,
731 NULL COST_CENTER,
732 NULL EMPLOYEE_PROJECT_NUMBER,
733 NULL EMPLOYEE_TASK_NUMBER,
734 0 EMPLOYEE_ASSIGNED_AREA,
735 NULL EMPLOYEE_ASSIGNED_FROM,
736 NULL EMPLOYEE_ASSIGNED_TO,
737 cust.expense_account_id EXP_ACCOUNT,
738 cust.cust_account_id CUSTOMER_ACCOUNT_ID,
739 pa.segment1 CUSTOMER_PROJECT_NUMBER,
740 pat.task_name CUSTOMER_TASK_NUMBER,
741 cust.allocated_area CUSTOMER_ASSIGNED_AREA,
742 cust.cust_assign_start_date CUSTOMER_ASSIGNED_FROM,
743 cust.cust_assign_end_date CUSTOMER_ASSIGNED_TO,
744 hp.party_name CUSTOMER_NAME,
745 hcsu.location CUSTOMER_SITE,
746 arl.meaning CUSTOMER_CATEGORY
747 FROM
748 pa_projects_all pa,
749 pa_tasks pat,
750 hz_parties hp,
751 hz_cust_site_uses_all hcsu,
752 ar_lookups arl,
753 hz_cust_accounts hca,
754 pn_space_assign_cust_all cust
755 WHERE cust.location_id =V_LOCATION_ID_2 AND
756 hca.cust_account_id = cust.cust_account_id AND
757 hca.party_id = hp.party_id AND
758 arl.lookup_code(+) = hp.category_code AND
759 arl.lookup_type(+) = 'CUSTOMER_CATEGORY' AND
760 hcsu.site_use_id(+)= cust.site_use_id AND
761 pa.project_id(+) = cust.project_id AND
762 pat.task_id(+) = cust.task_id
763 AND as_of_date between cust.cust_assign_start_date AND NVL(cust.cust_assign_end_date, l_date);
764 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust: c_c_pn# '||l_count_1||': c_c_assign_pn# '
765 ||l_count_2||': cust_pn Loop(+)');
766 l_count_3 := 0;
767 LOOP
768 l_count_3 := l_count_3 + 1;
769 FETCH cust_pn into V_LOCATION_ID,
770 V_PERSON_ID,
771 V_COST_CENTER,
772 V_EMPLOYEE_PROJECT_NUMBER,
773 V_EMPLOYEE_TASK_NUMBER,
774 V_SPACE_AREA.ALLOCATED_AREA_EMP, --bug#2226865
775 V_EMPLOYEE_ASSIGNED_FROM,
776 V_EMPLOYEE_ASSIGNED_TO,
777 V_EXP_ACCOUNT,
778 V_CUSTOMER_ACCOUNT_ID,
779 V_CUSTOMER_PROJECT_NUMBER,
780 V_CUSTOMER_TASK_NUMBER,
781 V_SPACE_AREA.ALLOCATED_AREA_CUST, --bug#2226865
782 V_CUSTOMER_ASSIGNED_FROM,
783 V_CUSTOMER_ASSIGNED_TO,
784 V_CUSTOMER_NAME,
785 V_CUSTOMER_SITE,
786 V_CUSTOMER_CATEGORY;
787 V_CUSTOMER_ACCOUNT := fnd_flex_ext.get_segs('SQLGL','GL#',v_coa_id,V_EXP_ACCOUNT);
788 EXIT WHEN cust_pn%notfound;
789 v_compare_cust:=compare_assign_custlease(V_LOCATION_ID, V_CUSTOMER_ACCOUNT, l_request_id);
790 IF NOT(v_compare_cust) THEN
791
792 v_code_data:=pnp_util_func.get_location_name(V_LOCATION_ID, AS_OF_DATE);
793 v_emp_data:= pnp_util_func.get_emp_hr_data(V_PERSON_ID);
794 --------------bug#2226865-----------------
795 IF V_LOC_TYPE in ('BUILDING' ,'LAND','FLOOR','PARCEL') THEN
796 PNP_UTIL_FUNC.get_area(v_location_id , v_loc_type ,NULL,AS_OF_DATE,V_LOC_AREA,V_SPACE_AREA);
797 END IF;
798 -----------bug#2226865--------
799 INSERT INTO pn_space_assign_lease_itf
800 (LEASE_ID ,
801 LEASE_NAME ,
802 LEASE_NUMBER ,
803 LEASE_COMMENCEMENT_DATE ,
804 LEASE_TERMINATION_DATE ,
805 LOCATION_ID ,
806 LOCATION_TYPE ,
807 LOCATION_NAME ,
808 LOCATION_CODE ,
809 SPACE_TYPE ,
810 PROPERTY_CODE ,
811 PROPERTY_NAME ,
812 BUILDING_LOCATION_CODE ,
813 BUILDING_OR_LAND_NAME ,
814 FLOOR_LOCATION_CODE ,
815 FLOOR_OR_PARCEL_NAME ,
816 OFFICE_LOCATION_CODE ,
817 OFFICE_OR_SECTION_NAME ,
818 RENTABLE_AREA ,
819 USABLE_AREA ,
820 ASSIGNABLE_AREA ,
821 COMMON_AREA ,
822 PERSON_ID ,
823 EMPLOYEE_NAME ,
824 COST_CENTER ,
825 EMPLOYEE_NUMBER ,
826 EMPLOYEE_TYPE ,
827 EMPLOYEE_CATEGORY ,
828 EMPLOYEE_POSITION ,
829 EMPLOYEE_PROJECT_NUMBER ,
830 EMPLOYEE_TASK_NUMBER ,
831 EMPLOYEE_ASSIGNED_AREA ,
832 EMPLOYEE_ASSIGNED_FROM ,
833 EMPLOYEE_ASSIGNED_TO ,
834 CUSTOMER_NAME ,
835 CUSTOMER_SITE ,
836 CUSTOMER_CATEGORY ,
837 CUSTOMER_ACCOUNT ,
838 CUSTOMER_PROJECT_NUMBER ,
839 CUSTOMER_TASK_NUMBER ,
840 CUSTOMER_ASSIGNED_AREA ,
841 CUSTOMER_VACANT_AREA ,
842 CUSTOMER_ASSIGNED_FROM ,
843 CUSTOMER_ASSIGNED_TO ,
844 TEN_ATTRIBUTE_CATEGORY ,
845 TEN_ATTRIBUTE1 ,
846 TEN_ATTRIBUTE2 ,
847 TEN_ATTRIBUTE3 ,
848 TEN_ATTRIBUTE4 ,
849 TEN_ATTRIBUTE5 ,
850 TEN_ATTRIBUTE6 ,
851 TEN_ATTRIBUTE7 ,
852 TEN_ATTRIBUTE8 ,
853 TEN_ATTRIBUTE9 ,
854 TEN_ATTRIBUTE10 ,
855 TEN_ATTRIBUTE11 ,
856 TEN_ATTRIBUTE12 ,
857 TEN_ATTRIBUTE13 ,
858 TEN_ATTRIBUTE14 ,
859 TEN_ATTRIBUTE15 ,
860 LAST_UPDATE_DATE ,
861 LAST_UPDATED_BY ,
862 LAST_UPDATE_LOGIN ,
863 CREATION_DATE ,
864 CREATED_BY,
865 REQUEST_ID )
866 VALUES
867 ( V_LEASE_ID ,
868 V_LEASE_NAME ,
869 V_LEASE_NUMBER ,
870 V_LEASE_COM_DATE ,
871 V_LEASE_TERM_DATE ,
872 V_LOCATION_ID ,
873 V_LOCATION_TYPE ,
874 V_LOCATION_NAME ,
875 V_LOCATION_CODE ,
876 V_SPACE_TYPE ,
877 v_code_data.PROPERTY_CODE ,
878 v_code_data.PROPERTY_NAME ,
879 v_code_data.BUILDING_LOCATION_CODE ,
880 v_code_data.BUILDING ,
881 v_code_data.FLOOR_LOCATION_CODE ,
882 v_code_data.FLOOR ,
883 v_code_data.OFFICE_LOCATION_CODE ,
884 v_code_data.OFFICE ,
885 V_LOC_AREA.RENTABLE_AREA ,--bug#2226865
886 V_LOC_AREA.USABLE_AREA ,--bug#2226865
887 V_LOC_AREA.ASSIGNABLE_AREA ,--bug#2226865
888 V_LOC_AREA.COMMON_AREA ,--bug#2226865
889 V_PERSON_ID ,
890 v_emp_data.FULL_NAME ,
891 V_COST_CENTER ,
892 v_emp_data.EMPLOYEE_NUMBER ,
893 v_emp_data.EMPLOYEE_TYPE ,
894 v_emp_data.EMPLOYMENT_CATEGORY_MEANING ,
895 v_emp_data.POSITION ,
896 V_EMPLOYEE_PROJECT_NUMBER ,
897 V_EMPLOYEE_TASK_NUMBER ,
898 V_SPACE_AREA.ALLOCATED_AREA_EMP , --bug#2226865
899 V_EMPLOYEE_ASSIGNED_FROM ,
900 V_EMPLOYEE_ASSIGNED_TO ,
901 V_CUSTOMER_NAME ,
902 V_CUSTOMER_SITE ,
903 V_CUSTOMER_CATEGORY ,
904 V_CUSTOMER_ACCOUNT ,
905 V_CUSTOMER_PROJECT_NUMBER ,
906 V_CUSTOMER_TASK_NUMBER ,
907 V_SPACE_AREA.ALLOCATED_AREA_CUST , --bug#2226865
908 V_SPACE_AREA.Vacant_area , --bug#2226865
909 V_CUSTOMER_ASSIGNED_FROM ,
910 V_CUSTOMER_ASSIGNED_TO ,
911 V_ATTRIBUTE_CATEGORY ,
912 V_ATTRIBUTE1 ,
913 V_ATTRIBUTE2 ,
914 V_ATTRIBUTE3 ,
915 V_ATTRIBUTE4 ,
916 V_ATTRIBUTE5 ,
917 V_ATTRIBUTE6 ,
918 V_ATTRIBUTE7 ,
919 V_ATTRIBUTE8 ,
920 V_ATTRIBUTE9 ,
921 V_ATTRIBUTE10 ,
922 V_ATTRIBUTE11 ,
923 V_ATTRIBUTE12 ,
924 V_ATTRIBUTE13 ,
925 V_ATTRIBUTE14 ,
926 V_ATTRIBUTE15 ,
927 V_LAST_UPDATE_DATE ,
928 V_LAST_UPDATED_BY ,
929 V_LAST_UPDATE_LOGIN ,
930 V_CREATION_DATE ,
931 V_CREATED_BY ,
932 l_request_id );
933 END IF;
934 END LOOP;
935 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust: c_c_pn# '||l_count_1||': c_c_assign_pn# '
936 ||l_count_2||': cust_pn Loop(-)');
937 END LOOP;
938 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust: c_c_pn# '||l_count_1||': c_c_assign_pn Loop(-)');
939 END LOOP;
940
941 IF dbms_sql.is_open (l_cursor) THEN
942 dbms_sql.close_cursor (l_cursor);
943 END IF;
944
945 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust: c_c_pn Loop(-)');
946 COMMIT;
947 pnp_debug_pkg.put_log_msg('pn_sp_assign_Cust(-)');
948 END IF;
949
950 PNP_DEBUG_PKG.put_log_msg('pn_sp_assign_leaseConditions(-)');
951
952 --If there is something amiss...
953 EXCEPTION
954 WHEN OTHERS THEN
955 retcode:=2;
956 errbuf:=SUBSTR(SQLERRM,1,235);
957 RAISE;
958 END pn_space_assign_lease;
959
960 END pnrx_sp_assign_by_lease;