1 PACKAGE pnp_util_func AUTHID CURRENT_USER AS
2 -- $Header: PNPFUNCS.pls 120.12 2010/03/24 11:27:12 acprakas ship $
3
4
5 -- Global Variable for use by
6 -- SET_VIEW_CONTEXT (procedure) and GET_VIEW_CONTEXT (function)
7
8 g_view_context VARCHAR2(2) DEFAULT NULL;
9 g_start_of_time DATE := TO_DATE('01-01-0001','DD-MM-YYYY');
10 g_end_of_time DATE := TO_DATE('31-12-4712','DD-MM-YYYY');
11 g_as_of_date DATE := SYSDATE;
12 g_retro_enabled BOOLEAN := FALSE;
13 g_mini_retro_enabled BOOLEAN := TRUE;
14 g_as_of_date_4_loc_pubview DATE := NULL;
15 g_as_of_date_4_emp_pubview DATE := NULL;
16
17 TYPE emp_hr_data_rec IS RECORD (
18 person_id PER_ALL_PEOPLE_F.person_id%TYPE,
19 effective_start_date PER_ALL_PEOPLE_F.effective_start_date%TYPE,
20 effective_end_date PER_ALL_PEOPLE_F.effective_end_date%TYPE,
21 assignment_id PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE,
22 last_name PER_ALL_PEOPLE_F.last_name%TYPE,
23 employee_number PER_ALL_PEOPLE_F.employee_number%TYPE,
24 email_address PER_ALL_PEOPLE_F.email_address%TYPE,
25 first_name PER_ALL_PEOPLE_F.first_name%TYPE,
26 full_name PER_ALL_PEOPLE_F.full_name%TYPE,
27 person_type_id PER_ALL_PEOPLE_F.person_type_id%TYPE,
28 employee_type PER_PERSON_TYPES_tl.user_person_type%TYPE,
29 phone_number PER_PHONES.phone_number%TYPE,
30 position_id PER_ALL_ASSIGNMENTS_F.position_id%TYPE,
31 position VARCHAR2(2000),
32 job_id PER_ALL_ASSIGNMENTS_F.job_id%TYPE,
33 job PER_JOBS.name%TYPE,
34 organization_id PER_ALL_ASSIGNMENTS_F.organization_id%TYPE,
35 organization HR_ORGANIZATION_UNITS.name%TYPE,
36 employment_category PER_ALL_ASSIGNMENTS_F.employment_category%TYPE,
37 employment_category_meaning FND_COMMON_LOOKUPS.meaning%TYPE
38 );
39
40 TYPE emp_pr_data_rec IS RECORD (
41 segment1 PA_PROJECTS.segment1%TYPE,
42 name HR_ORGANIZATION_UNITS.name%TYPE
43 );
44
45 TYPE emp_tr_data_rec IS RECORD (
46 task_name PA_TASKS.task_name%TYPE
47 );
48
49 TYPE location_name_rec IS RECORD (
50 office_location_code PN_LOCATIONS.location_code%TYPE,
51 office PN_LOCATIONS.office%TYPE,
52 floor_location_code PN_LOCATIONS.location_code%TYPE,
53 floor PN_LOCATIONS.floor%TYPE,
54 building_location_code PN_LOCATIONS.location_code%TYPE,
55 building PN_LOCATIONS.building%TYPE,
56 property_code PN_PROPERTIES.property_code%TYPE,
57 property_name PN_PROPERTIES.property_name%TYPE,
58 office_park_name PN_LOCATION_PARKS.name%TYPE,
59 region_name PN_LOCATION_PARKS.name%TYPE
60 );
61
62 TYPE currency_table_type IS TABLE OF pn_currencies%ROWTYPE
63 INDEX BY BINARY_INTEGER;
64
65 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
66
67 /* table to create virtual schedule bug # 4229248 */
68 TYPE virtual_sched_rec IS RECORD(
69 start_date DATE,
70 end_date DATE,
71 schedule_date DATE);
72
73 TYPE virtual_sched_tbl_type IS TABLE OF virtual_sched_rec INDEX BY BINARY_INTEGER;
74
75 TYPE item_end_dt_rec IS RECORD (term_id pn_payment_terms_all.payment_term_id%TYPE,
76 item_end_dt DATE,
77 index_period_id pn_payment_terms_all.index_period_id%TYPE);
78
79 TYPE item_end_dt_tbl_type IS TABLE OF item_end_dt_rec INDEX BY BINARY_INTEGER;
80
81 -------------------------------------------------------------------------------
82
83 currencies_table currency_table_type;
84
85 -------------------------------------------------------------------------------
86
87
88 TYPE space_assignment_rec IS RECORD (
89
90 location_id PN_LOCATIONS_ALL.location_id%type,
91 assignment_id PN_SPACE_ASSIGN_CUST_ALL.cust_space_assign_id%type,
92 assign_start_date PN_SPACE_ASSIGN_CUST_ALL.cust_assign_start_date%type,
93 assign_end_date PN_SPACE_ASSIGN_CUST_ALL.cust_assign_start_date%type,
94 allocated_area_pct PN_SPACE_ASSIGN_CUST_ALL.allocated_area_pct%type,
95 allocated_area PN_SPACE_ASSIGN_CUST_ALL.allocated_area%type,
96 utilized_area PN_SPACE_ASSIGN_CUST_ALL.utilized_area%type,
97 project_id PN_SPACE_ASSIGN_CUST_ALL.project_id%type,
98 task_id PN_SPACE_ASSIGN_CUST_ALL.task_id%type,
99 person_id PN_SPACE_ASSIGN_EMP_ALL.person_id%type,
100 cust_account_id PN_SPACE_ASSIGN_CUST_ALL.cust_account_id%type,
101 org_id PN_SPACE_ASSIGN_CUST_ALL.org_id%type,
102 lease_id PN_SPACE_ASSIGN_CUST_ALL.lease_id%type
103 );
104
105
106 TYPE space_assignment_tbl IS TABLE OF space_assignment_rec INDEX BY BINARY_INTEGER;
107
108 FUNCTION validate_lease_terminate_date (p_lease_id IN NUMBER,
109 p_termination_date IN DATE)
110 RETURN BOOLEAN;
111
112 FUNCTION min_lease_terminate_date (p_lease_id IN NUMBER) RETURN DATE;
113
114 FUNCTION item_end_date (p_term_id IN NUMBER,p_freq_code IN VARCHAR) RETURN DATE;
115
116 FUNCTION fetch_item_end_dates( p_lease_id NUMBER)
117 RETURN pnp_util_func.item_end_dt_tbl_type;
118
119 FUNCTION norm_trm_exsts (p_lease_id IN NUMBER) RETURN BOOLEAN;
120
121 FUNCTION get_total_payment_item_amt (
122 p_status IN VARCHAR2,
123 p_curr_code IN VARCHAR2,
124 p_payment_schedule_id IN NUMBER,
125 p_called_from IN VARCHAR2 DEFAULT 'PNTAUPMT'
126
127 ) RETURN NUMBER;
128
129 -------------------------------------------------------------------------------
130
131 FUNCTION get_total_payment_term_amt (
132 p_paymentTermId IN NUMBER
133 ) RETURN NUMBER;
134
135
136 -------------------------------------------------------------------------------
137
138 FUNCTION get_concatenated_address (
139 address_style IN VARCHAR2,
140 address_line1 IN VARCHAR2,
141 address_line2 IN VARCHAR2,
142 address_line3 IN VARCHAR2,
143 address_line4 IN VARCHAR2,
144 city IN VARCHAR2,
145 county IN VARCHAR2,
146 state IN VARCHAR2,
147 province IN VARCHAR2,
148 zip_code IN VARCHAR2,
149 territory_short_name IN VARCHAR2
150 )RETURN VARCHAR2;
151
152
153 -------------------------------------------------------------------------------
154
155 FUNCTION get_vacant_area ( p_location_id IN NUMBER,
156 p_As_Of_Date IN DATE default NULL ) RETURN NUMBER;
157
158 -------------------------------------------------------------------------------
159
160 FUNCTION get_vacant_area_percent ( p_location_id IN NUMBER,
161 p_As_Of_Date IN DATE default NULL ) RETURN NUMBER;
162
163 -------------------------------------------------------------------------------
164
165 FUNCTION get_load_factor ( p_location_id IN NUMBER ,
166 p_As_Of_Date IN DATE default NULL ) RETURN NUMBER;
167
168 -------------------------------------------------------------------------------
169
170 FUNCTION get_floors ( p_location_id IN NUMBER ,
171 p_as_of_date IN DATE DEFAULT NULL) RETURN NUMBER;
172
173 -------------------------------------------------------------------------------
174
175 FUNCTION get_offices ( p_location_id IN NUMBER ,
176 p_as_of_date IN DATE DEFAULT NULL) RETURN NUMBER;
177
178 -------------------------------------------------------------------------------
179
180 FUNCTION get_utilized_capacity ( p_location_id IN NUMBER,
181 p_as_Of_Date IN DATE default NULL
182 ) RETURN NUMBER;
183
184 -------------------------------------------------------------------------------
185
186 FUNCTION get_vacancy ( p_location_id IN NUMBER ,
187 p_as_of_Date IN DATE default NULL ) RETURN NUMBER;
188
189 -------------------------------------------------------------------------------
190
191 FUNCTION get_occupancy_percent ( p_location_id IN NUMBER ,
192 p_as_of_Date IN DATE default NULL ) RETURN NUMBER;
193
194 -------------------------------------------------------------------------------
195
196 FUNCTION get_area_utilized ( p_location_id IN NUMBER ,
197 p_as_of_Date IN DATE default NULL ) RETURN NUMBER;
198
199 -------------------------------------------------------------------------------
200
201 FUNCTION get_total_leased_area (
202 p_leaseId IN NUMBER ,
203 p_as_of_Date IN DATE default NULL ) RETURN NUMBER;
204
205
206 -------------------------------------------------------------------------------
207
208 FUNCTION GET_LEASE_STATUS (
209 p_leaseId NUMBER
210 ) RETURN VARCHAR2;
211
212
213 -------------------------------------------------------------------------------
214
215 FUNCTION pn_distinct_zip_code (
216 p_address_id NUMBER,
217 p_zip_code VARCHAR2
218 ) RETURN NUMBER ;
219
220
221 -------------------------------------------------------------------------------
222
223 ----------------------------------------------------------------------
224 -- FUNCTION : GET_LOCATION_OCCUPANCY
225 -- This function returns the number of employees assigned
226 -- to a location
227 ----------------------------------------------------------------------
228 FUNCTION GET_LOCATION_OCCUPANCY (
229 p_locationId IN NUMBER,
230 p_As_Of_Date IN DATE DEFAULT NULL
231 ) RETURN NUMBER ;
232
233
234
235 ----------------------------------------------------------------------
236 -- Returns the cost center of an employee at HR assignment level
237 -- 28-NOV-05 sdmahesh o Added parameter org_id
238 ----------------------------------------------------------------------
239 FUNCTION get_cost_center ( p_employee_id IN NUMBER,
240 p_column_name IN VARCHAR2 DEFAULT NULL,
241 p_org_id IN NUMBER
242 ) RETURN VARCHAR2;
243 -- Don't put in pragma for this, pkg body will not compile! Naga
244
245
246 -------------------------------------
247 -- valid_lookup_code
248 --------------------------------------
249 FUNCTION valid_lookup_code (
250 p_lookup_type VARCHAR2,
251 p_lookup_code VARCHAR2 )
252 RETURN BOOLEAN;
253
254
255 -------------------------------------
256 -- valid_country_code
257 --------------------------------------
258 FUNCTION valid_country_code (
259 p_country VARCHAR2 )
260 RETURN BOOLEAN;
261
262
263
264 -------------------------------------
265 -- valid_uom_code
266 --------------------------------------
267 FUNCTION valid_uom_code (
268 p_uom_code VARCHAR2 )
269 RETURN BOOLEAN;
270
271
272
273 -------------------------------------
274 -- valid_employee
275 --------------------------------------
276 FUNCTION valid_employee (
277 p_employee_id NUMBER )
278 RETURN BOOLEAN;
279
280
281
282 -------------------------------------------------------------------------------
283 -- valid_cost_center
284 -- 28-NOV-2005 sdmahesh o Added parameter p_org_id
285 -------------------------------------------------------------------------------
286 FUNCTION valid_cost_center (
287 p_cost_center VARCHAR2, p_org_id NUMBER )
288 RETURN BOOLEAN;
289
290 -- Don't put in pragma for this, pkg body will not compile! Naga
291
292
293 -------------------------------------
294 -- valid_emp_cc_comb
295 --------------------------------------
296 FUNCTION valid_emp_cc_comb (
297 p_employee_id NUMBER,
298 p_cost_center VARCHAR2 )
299 RETURN BOOLEAN;
300
301 -- Don't put in pragma for this, pkg body will not compile! Naga
302
303 -------------------------------------
304 -- valid_location
305 --------------------------------------
306 FUNCTION valid_location (
307 p_location_id NUMBER ,
308 p_as_of_date IN DATE DEFAULT NULL)
309 RETURN BOOLEAN;
310
311
312
313 /*-- This should be taken care of by a l_vacant_area in PNVLOSPB.pls --
314
315 -------------------------------------
316 -- allowed_allocated_area
317 --------------------------------------
318 FUNCTION allowed_allocated_area (
319 p_allocated_area NUMBER )
320 RETURN BOOLEAN;
321
322 pragma restrict_references ( allowed_allocated_area, WNDS, WNPS );
323
324 -- Read Comment at start of function spec --*/
325
326
327 -------------------------------------------------------------------------------
328 -- get_cc_code
329 --28-NOV-05 sdmahesh o Added parameter P_ORG_ID
330
331 -------------------------------------------------------------------------------
332 FUNCTION get_cc_code (
333 p_employee_id NUMBER,
334 p_org_id NUMBER)
335 RETURN VARCHAR2;
336
337 -- Don't put in pragma for this, pkg body will not compile! Naga
338
339
340 -------------------------------------
341 -- get_segment_column_name
342 --28-NOV-05 sdmahesh o Added parameter p_org_id
343 --------------------------------------
344 FUNCTION get_segment_column_name(p_org_id NUMBER)
345 RETURN VARCHAR2;
346
347 -------------------------------------
348 -- pn_get_next_location_id
349 --------------------------------------
350 FUNCTION pn_get_next_location_id RETURN NUMBER;
351
352 -------------------------------------
353 -- pn_get_next_space_alloc_id
354 --------------------------------------
355 FUNCTION pn_get_next_space_alloc_id RETURN NUMBER;
356
357 -------------------------------------
358 -- SET_VIEW_CONTEXT
359 --------------------------------------
360 PROCEDURE SET_VIEW_CONTEXT(p_ap_ar VARCHAR2);
361
362 -------------------------------------
363 -- GET_VIEW_CONTEXT
364 --------------------------------------
365 FUNCTION GET_VIEW_CONTEXT RETURN VARCHAR2;
366
367 -------------------------------------------------------------------
368 -- For getting the daily conversion rate from GL's new API in 11.5
369 -- The form uses this, to display the amount in foreign currency,
370 -- when user chooses a currency code different from the functional
371 -- currency.
372 -------------------------------------------------------------------
373
374 -------------------------------------------------------------------
375 -- To Return EXPORT_CURRENCY_AMOUNT column
376 -- Get Export Currency Amount from GL's API
377 -------------------------------------------------------------------
378 FUNCTION Export_Curr_Amount (
379 currency_code in VARCHAR2,
380 export_currency_code in VARCHAR2,
381 export_date in DATE,
382 conversion_type in VARCHAR2,
383 actual_amount in NUMBER,
384 p_called_from IN VARCHAR2 DEFAULT NULL
385 )
386
387 RETURN NUMBER ;
388
389 -- Don't put in pragma for this, pkg body will not compile! Naga
390
391
392 -------------------------------------------------------------------
393 -- To Return the Start_Date, given the Period_Name
394 -- For use in PN_EXP_TO_AP, PN_EXP_TO_AR packkages
395 --28-NOV-05 sdmahesh o Added parameter P_ORG_ID
396 -------------------------------------------------------------------
397 FUNCTION Get_Start_Date(p_Period_Name VARCHAR2,p_org_id NUMBER)
398 RETURN date ;
399
400
401 -------------------------------------------------------
402 -- FUNCTION Get_Occupancy_Status
403 -------------------------------------------------------
404 FUNCTION Get_Occupancy_Status(p_location_id NUMBER,
405 p_As_Of_Date DATE default NULL)
406 RETURN NUMBER ;
407
408
409 -------------------------------------------------------
410 -- FUNCTION Get_Location_Code
411 -------------------------------------------------------
412
413 FUNCTION Get_Location_Code ( p_location_id NUMBER ,
414 p_As_Of_Date DATE default NULL,
415 p_ignore_date BOOLEAN default FALSE)
416 RETURN VarChar2 ;
417
418 -------------------------------------------------------
419 -- FUNCTION Get_Location_Type_Lookup_Code
420 -------------------------------------------------------
421
422 FUNCTION Get_Location_Type_Lookup_Code ( p_location_id NUMBER ,
423 p_as_of_date DATE default NULL,
424 p_ignore_date BOOLEAN default FALSE)
425 RETURN VarChar2 ;
426
427 -------------------------------------------------------------
428 -- FUNCTION Get_Allocated_Area_By_CC - For use in Report PNSPALLO
429 -------------------------------------------------------------
430 FUNCTION Get_Allocated_Area_By_CC ( p_Location_Id NUMBER ,
431 p_Cost_Center VarChar2,
432 p_As_Of_Date DATE default NULL )
433 RETURN NUMBER ;
434
435
436 -------------------------------------------------------------
437 -- FUNCTION Get_High_Schedule_Date - For use in Form PNTLEASE
438 -------------------------------------------------------------
439 FUNCTION Get_High_Schedule_Date ( p_leaseId NUMBER )
440 RETURN Date;
441
442
443 -------------------------------------------------------------
444 -- FUNCTION Get_High_Change_Comm_Date - For use in Form PNTLEASE
445 -------------------------------------------------------------
446 FUNCTION Get_High_Change_Comm_Date ( p_leaseId NUMBER )
447 RETURN Date ;
448
449 -------------------------------------------------------------
450 -- FUNCTION Get_Emp_Hr_Data - For use in Form PNTSPACE
451 -------------------------------------------------------------
452 FUNCTION Get_Emp_Hr_Data ( p_personId NUMBER )
453 RETURN emp_hr_data_rec;
454
455 -------------------------------------------------------------
456 -- FUNCTION Get_Emp_Pr_Data - For use in Form PNTSPACE
457 -------------------------------------------------------------
458 FUNCTION Get_Emp_Pr_Data ( p_projectId NUMBER )
459 RETURN emp_pr_data_rec;
460
461 -------------------------------------------------------------
462 -- FUNCTION Get_Emp_Tr_Data - For use in Form PNTSPACE
463 -------------------------------------------------------------
464 FUNCTION Get_Emp_Tr_Data ( p_taskId NUMBER )
465 RETURN emp_tr_data_rec;
466
467 -------------------------------------------------------------------------------
468
469 FUNCTION get_building_rentable_area ( p_location_id IN NUMBER ,
470 p_as_of_date IN DATE DEFAULT NULL) RETURN NUMBER;
471
472 -------------------------------------------------------------------------------
473 -------------------------------------------------------------------------------
474
475 FUNCTION get_building_usable_area( p_location_id IN NUMBER ,
476 p_as_of_date IN DATE DEFAULT NULL
477 ) RETURN NUMBER;
478
479 -------------------------------------------------------------------------------
480 -------------------------------------------------------------------------------
481
482 FUNCTION get_building_assignable_area ( p_location_id IN NUMBER ,
483 p_as_of_date IN DATE DEFAULT NULL
484 ) RETURN NUMBER;
485
486 -------------------------------------------------------------------------------
487 -------------------------------------------------------------------------------
488
489 FUNCTION get_floor_rentable_area ( p_location_id IN NUMBER ,
490 p_as_of_date IN DATE DEFAULT NULL
491 ) RETURN NUMBER;
492
493 -------------------------------------------------------------------------------
494 -------------------------------------------------------------------------------
495
496 FUNCTION get_floor_usable_area( p_location_id IN NUMBER ,
497 p_as_of_date IN DATE DEFAULT NULL
498 ) RETURN NUMBER;
499
500 -------------------------------------------------------------------------------
501 -------------------------------------------------------------------------------
502
503 FUNCTION get_floor_assignable_area ( p_location_id IN NUMBER ,
504 p_as_of_date IN DATE DEFAULT NULL
505 ) RETURN NUMBER;
506
507 -------------------------------------------------------------------------------
508 -------------------------------------------------------------------------------
509
510 FUNCTION get_floor_common_area ( p_location_id IN NUMBER ,
511 p_as_of_date IN DATE DEFAULT NULL
512 ) RETURN NUMBER;
513
514 -------------------------------------------------------------------------------
515 -------------------------------------------------------------------------------
516 FUNCTION get_building_common_area ( p_location_id IN NUMBER ,
517 p_as_of_date IN DATE DEFAULT NULL
518 ) RETURN NUMBER;
519
520 -------------------------------------------------------------------------------
521 -------------------------------------------------------------------------------
522
523
524
525 FUNCTION get_building_max_capacity ( p_location_id IN NUMBER ,
526 p_as_of_date IN DATE DEFAULT NULL
527 ) RETURN NUMBER;
528
529 -------------------------------------------------------------------------------
530 -------------------------------------------------------------------------------
531
532 FUNCTION get_building_optimum_capacity( p_location_id IN NUMBER ,
533 p_as_of_date IN DATE DEFAULT NULL
534 ) RETURN NUMBER;
535
536 -------------------------------------------------------------------------------
537 -------------------------------------------------------------------------------
538
539 FUNCTION get_floor_max_capacity ( p_location_id IN NUMBER ,
540 p_as_of_date IN DATE DEFAULT NULL
541 ) RETURN NUMBER;
542
543 -------------------------------------------------------------------------------
544 -------------------------------------------------------------------------------
545
546 FUNCTION get_floor_optimum_capacity( p_location_id IN NUMBER ,
547 p_as_of_date IN DATE DEFAULT NULL
548 ) RETURN NUMBER;
549
550 -------------------------------------------------------------------------------
551 -------------------------------------------------------------------------------
552
553 FUNCTION get_floor_vacancy ( p_location_id IN NUMBER,
554 p_as_of_date IN DATE DEFAULT NULL ) RETURN NUMBER;
555
556 -------------------------------------------------------------------------------
557 -------------------------------------------------------------------------------
558
559 FUNCTION get_office_vacancy (p_location_id IN NUMBER ,
560 p_as_of_date IN DATE DEFAULT NULL
561 ) RETURN NUMBER;
562
563 -------------------------------------------------------------------------------
564 -------------------------------------------------------------------------------
565
566 FUNCTION get_space_assigned_status (p_location_id IN NUMBER,
567 p_as_of_date IN DATE DEFAULT NULL
568 )
569 RETURN BOOLEAN;
570
571 -------------------------------------------------------------------------------
572 -------------------------------------------------------------------------------
573
574 FUNCTION get_floor_secondary_area ( p_location_id IN NUMBER,
575 p_as_of_date IN DATE DEFAULT NULL ) RETURN NUMBER;
576
577 -------------------------------------------------------------------------------
578 -------------------------------------------------------------------------------
579
580 FUNCTION get_office_secondary_area ( p_location_id IN NUMBER ,
581 p_as_of_date IN DATE DEFAULT NULL
582 ) RETURN NUMBER;
583 -------------------------------------------------------------------------------
584 -------------------------------------------------------------------------------
585 FUNCTION get_parent_location_id ( p_location_id IN NUMBER ) RETURN NUMBER;
586
587 -------------------------------------------------------------------------------
588 FUNCTION get_normalize_flag ( p_paymentTermId IN NUMBER
589 ) RETURN VarChar2;
590
591 -------------------------------------------------------------------
592 -- To Return the Hire Date, given the Person_Id
593 -- For use in insert script pninsspa.sql
594 -------------------------------------------------------------------
595 FUNCTION get_hire_date(p_PersonId IN NUMBER)
596 RETURN date ;
597
598 -------------------------------------------------------------
599 -- FUNCTION Get_Location_Name - For use in RXi
600 -------------------------------------------------------------
601 FUNCTION get_location_name ( p_Location_Id IN NUMBER ,
602 p_as_of_date IN DATE DEFAULT NULL)
603 RETURN location_name_rec;
604
605 -------------------------------------------------------------------
606 -- To Return the Termination Date, given the Person_Id
607 -- For use in the PNEMPDSP.rdf
608 -------------------------------------------------------------------
609 FUNCTION get_termination_date(p_PersonId IN NUMBER)
610 RETURN date ;
611
612 -------------------------------------------------------------------
613 -- To return Rentable Area, given Location Type Lookup Code and
614 -- Location ID. For use in PNSPUTIL.rdf
615 -------------------------------------------------------------------
616 FUNCTION get_rentable_area(p_loc_type_lookup_code IN VARCHAR2,
617 p_location_id IN NUMBER,
618 p_as_of_date DATE DEFAULT NULL )
619 RETURN NUMBER;
620
621 ---------------------------------------------------------------------
622 -- To return default GL period name for a given GL date. If GL period
623 -- for that date is closed then next open GL period name is defaulted.
624 -- 28-NOV-2005 sdmahesh o Added parameter P_ORG_ID
625 ---------------------------------------------------------------------
626 FUNCTION get_default_gl_period(p_sch_date IN DATE,
627 p_application_id IN NUMBER,
628 p_org_id IN NUMBER)
629 RETURN VARCHAR2;
630
631 -------------------------------------------------------------------
632 -- To return the UOM_CODE, given Location Type Lookup Code and
633 -- Location ID. For use in PNTSPACE.fmb
634 -- Bug Fix for the Bug ID#1540803.
635 -------------------------------------------------------------------
636 FUNCTION Get_Unit_Of_Measure (p_location_id IN NUMBER,
637 p_loc_type IN VARCHAR2 DEFAULT NULL,
638 p_as_of_date IN DATE DEFAULT NULL
639 )
640 RETURN VARCHAR2;
641
642 -------------------------------------------------------------------
643 -- To return Payment Term Name for a given Term Id for Payables.
644 -------------------------------------------------------------------
645 FUNCTION Get_Ap_Payment_term (p_ap_term_id IN NUMBER)
646 RETURN VARCHAR2;
647
648 -------------------------------------------------------------------
649 -- To return Payment Term Name for a given Term Id for Receivables.
650 -------------------------------------------------------------------
651 FUNCTION Get_Ar_Payment_term (p_ar_term_id IN NUMBER)
652 RETURN VARCHAR2;
653
654 -------------------------------------------------------------------
655 -- To return Distribution Set Name for a given Distribution Set Id.
656 -------------------------------------------------------------------
657 FUNCTION Get_Distribution_Set_Name (p_dist_set_id IN NUMBER)
658 RETURN VARCHAR2;
659
660 -------------------------------------------------------------------
661 -- To return Project Name for a given Porject Id for Payables.
662 -------------------------------------------------------------------
663 FUNCTION Get_Ap_Project_Name (p_project_id IN NUMBER)
664 RETURN VARCHAR2;
665
666 -------------------------------------------------------------------
667 -- To return Task Name for a given Task Id for Payables.
668 -------------------------------------------------------------------
669 FUNCTION Get_Ap_Task_Name (p_task_id IN NUMBER)
670 RETURN VARCHAR2;
671
672 -------------------------------------------------------------------
673 -- To return Organization Name for a given Organization Id for Payables.
674 -------------------------------------------------------------------
675 FUNCTION Get_Ap_Organization_Name (p_org_id IN NUMBER)
676 RETURN VARCHAR2;
677
678 -------------------------------------------------------------------------------
679 -- To return Transaction Type for a given Customer Transaction Type
680 -- Id from Receivables.
681 -- IMPORTANT - Do not use this after MOAC goes ON
682 -------------------------------------------------------------------------------
683 FUNCTION Get_Ar_Trx_type (p_trx_id IN NUMBER)
684 RETURN VARCHAR2;
685
686 -------------------------------------------------------------------
687 -- To return Invoice Rule Name for a given Invoice Rule Id from Receivables.
688 -------------------------------------------------------------------
689 FUNCTION Get_Ar_Rule_Name (p_rule_id IN NUMBER)
690 RETURN VARCHAR2;
691
692 -------------------------------------------------------------------
693 -- To return Sales Person Name for a given Sales Person Id from Receivables.
694 -------------------------------------------------------------------
695 FUNCTION Get_Salesrep_Name (p_salesrep_id IN NUMBER,
696 p_org_id IN NUMBER)
697 RETURN VARCHAR2;
698
699 --------------------------------------------------------------------------------
700 -- To return allocated area for a date range.
701 -- 07-Jan-04 dthota o bug # 3354278 - new param p_allocated_area_pct
702 -- 30-DEC-04 Kiran o Bug # 4093603 - new param p_called_frm_mode
703 --------------------------------------------------------------------------------
704 PROCEDURE get_allocated_area (p_loc_id IN NUMBER,
705 p_str_dt IN DATE,
706 p_new_end_dt IN OUT NOCOPY DATE,
707 p_allocated_area OUT NOCOPY NUMBER,
708 p_allocated_area_pct OUT NOCOPY NUMBER,
709 p_future OUT NOCOPY VARCHAR2,
710 p_called_frm_mode IN VARCHAR2 DEFAULT NULL);
711
712 --------------------------------------------------------------------------------
713 -- To return end_date, assignable_area and to indicate if future
714 -- dated assignment exists and if vacant area is available for a location
715 -- for a gien start and end date.
716 -- 30-DEC-04 Kiran o Bug # 4093603 - new param p_called_frm_mode
717 --------------------------------------------------------------------------------
718 PROCEDURE validate_vacant_area (p_location_id IN NUMBER,
719 p_st_date IN DATE,
720 p_end_dt IN OUT NOCOPY DATE,
721 p_assignable_area IN OUT NOCOPY NUMBER,
722 p_old_allocated_area IN NUMBER,
723 p_new_allocated_area IN NUMBER,
724 p_old_allocated_area_pct IN NUMBER,
725 p_new_allocated_area_pct IN NUMBER,
726 p_display_message IN VARCHAR2,
727 p_future OUT NOCOPY VARCHAR2,
728 p_available_vacant_area OUT NOCOPY BOOLEAN,
729 p_called_frm_mode IN VARCHAR2 DEFAULT NULL);
730
731 --------------------------------------------------------------------------------
732 -- To return Minimum Future start date for an assigned location on a given date.
733 --------------------------------------------------------------------------------
734 FUNCTION get_min_futr_str_dt(p_loc_id IN NUMBER,
735 p_str_dt IN DATE)
736 RETURN DATE;
737
738 -----------------------------------------------------------------------------------
739 -- To return Conversion Rate Type either from profile option setup or pn_currencies.
740 -- 28-NOV-2005 sdmahesh o Added parameter P_ORG_ID
741 -----------------------------------------------------------------------------------
742 FUNCTION check_conversion_type(p_curr_code IN VARCHAR2,
743 p_org_id IN NUMBER) RETURN VARCHAR2;
744
745 -----------------------------------------------------------------------------------
746 -- This procedure updates allocated_area_pct in pn_space_assign_emp and pn_space_assign_cust
747 -- when assignable area is changed for a given location.
748 -----------------------------------------------------------------------------------
749 PROCEDURE loctn_assgn_area_update(p_loc_id IN NUMBER,
750 p_assgn_area IN NUMBER,
751 p_str_dt IN DATE,
752 p_end_dt IN DATE);
753
754
755
756 FUNCTION get_as_of_date(p_as_of_date IN DATE) RETURN DATE;
757
758 -----------------------------------------------------------------------------------
759 --These type are used to get the output from get_area procedure
760 -----------------------------------------------------------------------------------
761
762 TYPE PN_LOCATION_AREA_REC IS RECORD (assignable_area NUMBER ,
763 rentable_area NUMBER ,
764 usable_area NUMBER ,
765 common_area NUMBER ,
766 secondary_area NUMBER ,
767 max_capacity NUMBER ,
768 optimum_capacity NUMBER
769 );
770
771 TYPE PN_SPACE_AREA_REC IS RECORD (allocated_area NUMBER ,
772 allocated_area_emp NUMBER ,
773 allocated_area_cust NUMBER ,
774 UtilizedCapacityEmp NUMBER ,
775 UtilizedCapacityCust NUMBER ,
776 UtilizedCapacity NUMBER ,
777 Occupancy_percent NUMBER ,
778 vacant_area NUMBER ,
779 vacant_area_percent NUMBER ,
780 vacancy NUMBER ,
781 area_utilized NUMBER
782 );
783
784 PROCEDURE get_area ( p_Location_Id IN NUMBER ,
785 p_location_type IN VARCHAR2 ,
786 p_area_type IN VARCHAR2 DEFAULT NULL,
787 p_as_of_date IN DATE DEFAULT NULL,
788 p_loc_area OUT NOCOPY PN_LOCATION_AREA_REC,
789 p_space_area OUT NOCOPY PN_SPACE_AREA_REC
790 );
791
792 ----------------------------------------------------------------------------------------
793 -- Validate_Assignable_Area Fix for Bug#2384573
794 -- For use in PNSULOCN. Checks if the new Assignable_Area is greater than Allocated_Area
795 -- irrespective of date.
796 ----------------------------------------------------------------------------------------
797
798 FUNCTION validate_assignable_area ( p_Location_Id IN NUMBER,
799 p_Location_Type IN VARCHAR2,
800 p_Assignable_Area IN NUMBER )
801 RETURN BOOLEAN;
802
803 ----------------------------------------------------------------------------------------
804 -- Validate Term Template for all required data to create a term when Term Template is
805 -- used by Index Rent or Variable Rent forms.
806 ----------------------------------------------------------------------------------------
807 FUNCTION validate_term_template(p_term_temp_id IN NUMBER,
808 p_lease_cls_code IN VARCHAR2)
809 RETURN BOOLEAN;
810
811 -------------------------------------------------------------------
812 -- To return Term Template Name for a given Term Template Id.
813 -------------------------------------------------------------------
814 FUNCTION get_term_template_name(p_term_temp_id IN NUMBER)
815 RETURN VARCHAR2;
816
817 -----------------------------------------------------
818 -- Procedure to return all attributes of a location
819 -- If location is at a higher level than OFFICE/SECTION
820 -- it drills down to the leaf level and return all
821 -- attributes from the assignment tables
822 -----------------------------------------------------
823 PROCEDURE Get_space_assignments
824 ( p_location_id IN NUMBER,
825 p_location_type IN VARCHAR2 DEFAULT NULL,
826 p_start_date IN DATE,
827 p_end_date IN DATE,
828 x_space_assign_cust_tbl OUT NOCOPY SPACE_ASSIGNMENT_TBL,
829 x_space_assign_emp_tbl OUT NOCOPY SPACE_ASSIGNMENT_TBL,
830 x_return_status OUT NOCOPY VARCHAR2,
831 x_return_message OUT NOCOPY VARCHAR2
832 );
833
834 ----------------------------------------------
835 -- Procedure to validate if there are any
836 -- existing assignment for the date range
837 --------------------------------------------
838
839 PROCEDURE Validate_assignment_for_date (
840 p_location_id IN NUMBER,
841 p_start_date IN DATE,
842 p_end_date IN DATE,
843 p_start_date_old IN DATE,
844 p_end_date_old IN DATE,
845 x_return_status OUT NOCOPY VARCHAR2,
846 x_return_message OUT NOCOPY VARCHAR2
847 );
848
849 -----------------------------------------
850 -- Procedure to validate availability
851 -- of assignable area
852 ----------------------------------------
853 PROCEDURE Validate_assignable_area (
854 p_location_id IN NUMBER,
855 p_assignable_area IN NUMBER,
856 p_start_date IN DATE,
857 p_end_date IN DATE,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_return_message OUT NOCOPY VARCHAR2
860 );
861 ---------------------------------------------------------------------
862 -- Procedure that will be called by client programs to validate
863 -- date effectivity of the location and also to validate the changes
864 -- made to assignable_area. This is to make sure that there are
865 -- no assignments within the proposed end dates
866 --------------------------------------------------------------------
867 PROCEDURE validate_date_assignable_area
868 ( p_location_id IN NUMBER,
869 p_location_type IN VARCHAR2,
870 p_start_date IN DATE,
871 p_end_date IN DATE,
872 p_active_start_date_old IN DATE,
873 p_active_end_date_old IN DATE,
874 p_change_mode IN VARCHAR2 DEFAULT 'CORRECT',
875 p_assignable_area IN NUMBER DEFAULT NULL,
876 x_return_status OUT NOCOPY VARCHAR2,
877 x_return_message OUT NOCOPY VARCHAR2
878 );
879
880 ---------------------------------------------------------
881 -- This procedure will validate the start and end dates
882 -- for space assignments to make sure that they lie within
883 -- the effective date range for that location
884 -------------------------------------------------
885 PROCEDURE Validate_date_for_assignments
886 ( p_location_id IN NUMBER,
887 p_start_date IN DATE,
888 p_end_date IN DATE DEFAULT G_END_OF_TIME,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_return_message OUT NOCOPY VARCHAR2
891 );
892
893 --------------------------------------------------------------------------
894 -- This Function validates if there exists atleast one primary tenancy
895 -- with an end date greater than the new end date
896 --
897 -- History:
898 -- 24-jun-2003 Kiran o Created. CAM impact on Locations.
899 --------------------------------------------------------------------------
900
901 FUNCTION Exist_Tenancy_For_End_Date
902 ( p_Location_Id IN NUMBER,
903 p_New_End_Date IN DATE
904 )
905 RETURN BOOLEAN;
906
907 --------------------------------------------------------------------------
908 -- This Function validates if there exists atleast one primary tenancy
909 -- with a start date lesser than the new start date
910 --
911 -- History:
912 -- 24-jun-2003 Kiran o Created. CAM impact on Locations.
913 --------------------------------------------------------------------------
914
915 FUNCTION Exist_Tenancy_For_Start_Date
916 ( p_Location_Id IN NUMBER,
917 p_New_Start_Date IN DATE
918 )
919 RETURN BOOLEAN;
920
921 --------------------------------------------------------------------------
922 -- This function returns TRUE if there exists if there is atleast one
923 -- Area Class Detail for the goven Location or any of its child locations.
924 -- The check is actually mde against the pn_rec_arcl_dtlln table.
925 --
926 -- History:
927 -- 24-jun-2003 Kiran o Created. CAM impact on locations.
928 --------------------------------------------------------------------------
929
930 FUNCTION Exist_Area_Class_Dtls_For_Loc
931 ( p_Location_Id IN NUMBER,
932 p_active_start_date IN DATE default NULL,
933 p_active_end_date IN DATE default NULL)
934 RETURN BOOLEAN;
935
936 --------------------------------------------------------------------------
937 -- FUNCTION : chk_terms_for_tenancy
938 -- DESCRIPTION: checks payment terms for ties to tenancy
939 -- RETURNS : TRUE if any payment term is associated to the tenancy
940 -- : FALSE otherwise
941 -- HISTORY
942 -- 15-JAN-04 ftanudja o created.
943 --------------------------------------------------------------------------
944 FUNCTION chk_terms_for_tenancy(
945 p_tenancy_id NUMBER,
946 p_type VARCHAR2) RETURN BOOLEAN;
947
948
949 --------------------------------------------------------------------------
950 -- PROCEDURE : chk_terms_for_lease_area_chg
951 -- DESCRIPTION: checks payment terms for possible impacts of changes in
952 -- lease rentable, usable or assignable area.
953 -- RETURNS : 1) a table containing list of impacted term ID's.
954 -- 2) a table containing their new respective areas.
955 -- HISTORY
956 -- 08-JAN-04 ftanudja o created
957 -- 11-FEB-04 ftanudja o added NOCOPY hint for OUT param
958 -- 20-FEB-04 ftanudja o added parameter p_share_pct
959 --------------------------------------------------------------------------
960 PROCEDURE chk_terms_for_lease_area_chg(
961 p_tenancy_id NUMBER,
962 p_lease_id NUMBER,
963 p_rentable NUMBER,
964 p_usable NUMBER,
965 p_assignable NUMBER,
966 p_share_pct NUMBER,
967 x_term_id_tbl OUT NOCOPY num_tbl,
968 x_area_tbl OUT NOCOPY num_tbl);
969
970 --------------------------------------------------------------------------
971 -- PROCEDURE : chk_terms_for_locn_area_chg
972 -- DESCRIPTION: checks payment terms for possible impacts of changes in
973 -- location rentable, usable or assignable area.
974 -- RETURNS : 1) a table containing list of impacted term ID's.
975 -- 2) a table containing their new respective areas.
976 -- HISTORY
977 -- 08-JAN-04 ftanudja o created
978 -- 11-FEB-04 ftanudja o added NOCOPY hint for OUT param
979 --------------------------------------------------------------------------
980 PROCEDURE chk_terms_for_locn_area_chg (
981 p_bld_loc_id NUMBER,
982 p_flr_loc_id NUMBER,
983 p_ofc_loc_id NUMBER,
984 p_rentable NUMBER,
985 p_usable NUMBER,
986 p_assignable NUMBER,
987 x_term_id_tbl OUT NOCOPY num_tbl,
988 x_area_tbl OUT NOCOPY num_tbl);
989
990 --------------------------------------------------------------------------
991 -- PROCEDURE : batch_update_terms_area
992 -- DESCRIPTION: performs batch updates of area value onto the payment
993 -- terms table.
994 -- HISTORY
995 -- 08-JAN-04 ftanudja o created
996 --------------------------------------------------------------------------
997 PROCEDURE batch_update_terms_area(
998 x_area_tbl num_tbl,
999 x_term_id_tbl num_tbl);
1000
1001 --------------------------------------------------------------------------
1002 -- PROCEDURE : fetch_loctn_area
1003 -- DESCRIPTION: Generic function to fetch area.
1004 -- HISTORY
1005 -- 25-FEB-04 ftanudja o created.
1006 --------------------------------------------------------------------------
1007 PROCEDURE fetch_loctn_area(
1008 p_type VARCHAR2,
1009 p_location_id NUMBER,
1010 p_as_of_date DATE,
1011 x_area OUT NOCOPY pn_location_area_rec);
1012
1013 -------------------------------------------------------------------------------
1014 -- FUNCTION : fetch_tenancy_area
1015 -- RETURNS : gets area given an area type code, taking into account
1016 -- the tenancy percentage share.
1017 -- HISTORY
1018 -- 21-APR-05 ftanudja o created. #4324777
1019 -- 01-SEP-05 Kiran o Changed the type of params from
1020 -- pn_payment_terms.%TYPE to pn_payment_terms_all.%TYPE
1021 -------------------------------------------------------------------------------
1022 FUNCTION fetch_tenancy_area (
1023 p_lease_id pn_payment_terms_all.lease_id%TYPE,
1024 p_location_id pn_payment_terms_all.location_id%TYPE,
1025 p_as_of_date pn_payment_terms_all.start_date%TYPE,
1026 p_area_type_code pn_payment_terms_all.area_type_code%TYPE)
1027 RETURN NUMBER;
1028
1029 --------------------------------------------------------------------------------
1030 -- FUNCTION : create_virtual_schedules
1031 -- DESCRIPTION: Creates VIRTUAL SCHEDULE
1032 -- HISTORY
1033 -- 01-JUL-04 Kiran o Created. bug # 4229248
1034 --------------------------------------------------------------------------------
1035 FUNCTION create_virtual_schedules( p_start_date DATE
1036 ,p_end_date DATE
1037 ,p_sch_day NUMBER
1038 ,p_term_freq VARCHAR2
1039 ,p_limit_date DATE)
1040 RETURN PNP_UTIL_FUNC.virtual_sched_tbl_type;
1041
1042 --------------------------------------------------------------------------
1043 -- FUNCTION : valid_early_term_date
1044 -- DESCRIPTION: Validates the early termination date
1045 -- HISTORY
1046 -- 01-JUL-04 Kiran o Created. bug # 3562487
1047 --------------------------------------------------------------------------
1048 FUNCTION valid_early_term_date( p_lease_id NUMBER
1049 ,p_term_id NUMBER
1050 ,p_normalized VARCHAR2
1051 ,p_frequency VARCHAR2
1052 ,p_termination_date DATE
1053 ,p_called_from VARCHAR2)
1054 RETURN BOOLEAN;
1055
1056 -- Retro Start
1057 FUNCTION retro_enabled RETURN BOOLEAN;
1058 FUNCTION retro_enabled_char RETURN VARCHAR2;
1059
1060 PROCEDURE check_var_rent_retro( p_term_id IN NUMBER
1061 ,p_new_start_date IN DATE
1062 ,p_new_end_date IN DATE
1063 ,p_error OUT NOCOPY BOOLEAN);
1064
1065 PROCEDURE get_yr_mth_days(p_from_date IN DATE
1066 ,p_to_date IN DATE
1067 ,p_yrs OUT NOCOPY NUMBER
1068 ,p_mths OUT NOCOPY NUMBER
1069 ,p_days OUT NOCOPY NUMBER);
1070
1071 FUNCTION get_date_from_ymd(p_from_date IN DATE
1072 ,p_yrs IN NUMBER
1073 ,p_mths IN NUMBER
1074 ,p_days IN NUMBER)
1075 RETURN DATE;
1076 -- Retro End
1077
1078 /* public view as of date setter/getters functions */
1079 /*------------------------------------------------------------------------------
1080 -- set G_AS_OF_DATE_4_LOC_PUBVIEW
1081 ------------------------------------------------------------------------------*/
1082 FUNCTION set_as_of_date_4_loc_pubview(p_date IN DATE) RETURN NUMBER;
1083
1084 /*------------------------------------------------------------------------------
1085 -- get G_AS_OF_DATE_4_LOC_PUBVIEW
1086 ------------------------------------------------------------------------------*/
1087 FUNCTION get_as_of_date_4_loc_pubview RETURN DATE;
1088
1089 /*------------------------------------------------------------------------------
1090 -- set G_AS_OF_DATE_4_EMP_PUBVIEW
1091 ------------------------------------------------------------------------------*/
1092 FUNCTION set_as_of_date_4_emp_pubview(p_date IN DATE) RETURN NUMBER;
1093
1094 /*------------------------------------------------------------------------------
1095 -- get G_AS_OF_DATE_4_EMP_PUBVIEW
1096 ------------------------------------------------------------------------------*/
1097 FUNCTION get_as_of_date_4_emp_pubview RETURN DATE;
1098
1099 /* public view as of date setter/getters functions */
1100
1101 /* overloaded functions and procedures for MOAC */
1102 -------------------------------------------------------------------------------
1103 -- To return Transaction Type for a given Customer Transaction Type
1104 -- Id from Receivables.
1105 -- USE THIS IN R12
1106 -------------------------------------------------------------------------------
1107 FUNCTION Get_Ar_Trx_type (p_trx_id IN NUMBER, p_org_id IN NUMBER)
1108 RETURN VARCHAR2;
1109
1110 /*-----------------------------------------------------------------------------
1111 -- Returns a boolean TRUE if mini retro is enabled
1112 -----------------------------------------------------------------------------*/
1113 FUNCTION mini_retro_enabled RETURN BOOLEAN;
1114
1115 /*-----------------------------------------------------------------------------
1116 -- Returns 'Y' if mini retro is enabled
1117 -----------------------------------------------------------------------------*/
1118 FUNCTION mini_retro_enabled_char RETURN VARCHAR2;
1119
1120
1121 /*----------------------------------------------------------------------------
1122 -- Functions added for MTM uptake. Called from leases form-view.
1123 -----------------------------------------------------------------------------*/
1124 FUNCTION get_loc_name_disp(p_lease_id IN NUMBER,
1125 p_as_of_date IN DATE)
1126 RETURN VARCHAR2;
1127
1128 FUNCTION get_loc_code_disp(p_lease_id IN NUMBER,
1129 p_as_of_date IN DATE)
1130 RETURN VARCHAR2;
1131
1132 FUNCTION get_prop_name_disp(p_lease_id IN NUMBER,
1133 p_as_of_date IN DATE)
1134 RETURN VARCHAR2;
1135
1136 /* Added following three variables for bug 6514984*/
1137 G_location_id NUMBER;
1138 G_as_of_dt DATE;
1139 G_location_code pn_locations_all.location_code%TYPE;
1140 --------------------------------------
1141 -- End of Package Spec --
1142 --------------------------------------
1143 END pnp_util_func;
[Home] [Help]