1 PACKAGE pqp_gb_css_daily_absences AS
2 /* $Header: pqgbdcss.pkh 120.0 2005/05/29 01:49:05 appldev noship $ */
3
4
5
6 -- Through out this Package wherever any parameter/variable if defined as
7 -- p_entitlements or l_entitlements will be used to store or pass a persons
8 -- Entitltments Band Wise.
9 -- Variable/Parameter p_absences_taken_to_date stores the entitlememts
10 -- used up
11 --Variable/paramter p_entitlements_remaining stores the entitlements
12 -- that are avaialable at that point of time Band wise
13
14 CURSOR csr_get_days_to_extend ( p_business_group_id NUMBER
15 ,p_assignment_id NUMBER
16 ,p_rolling_start_date DATE
17 ,p_rolling_end_date DATE
18 ,p_lookup_type VARCHAR2)
19 IS
20 SELECT SUM(
21 DECODE(
22 SIGN(paa.date_end - p_rolling_end_date)
23 ,1, p_rolling_end_date
24 ,paa.date_end
25 )
26 - DECODE(
27 SIGN(paa.date_start - p_rolling_start_date)
28 ,1, paa.date_start
29 ,p_rolling_start_date
30 )
31 + 1
32 ) cnt
33 FROM per_absence_attendances paa
34 ,hr_lookups hrl
35 ,ben_pl_f bp
36 ,pqp_gap_absence_plans gap
37 WHERE gap.assignment_id = p_assignment_id
38 and gap.pl_id = bp.pl_id
39 and hrl.lookup_type = p_lookup_type --'PQP_GAP_PLAN_TYPES_TO_EXTEND'
40 and bp.pl_typ_id = hrl.lookup_code
41 and ( p_rolling_start_date between
42 NVL(hrl.start_date_active, p_rolling_start_date)
43 and NVL(hrl.end_date_active, p_rolling_end_date)
44 OR
45 p_rolling_end_date between
46 NVL(hrl.start_date_active, p_rolling_start_date)
47 and NVL(hrl.end_date_active, p_rolling_end_date)
48 )
49 and gap.absence_attendance_id = paa.absence_attendance_id
50 and paa.business_group_id = p_business_group_id
51 and ( paa.date_start between
52 p_rolling_start_date and p_rolling_end_date
53 OR
54 paa.date_end between
55 p_rolling_start_date and p_rolling_end_date
56 ) ;
57
58
59 -------------debug------------------------------
60 PROCEDURE debug
61 (p_trace_message IN VARCHAR2,
62 p_trace_location IN NUMBER DEFAULT NULL
63 ) ;
64
65 -------------debug_enter-----------------------
66 PROCEDURE debug_enter
67 (p_proc_name IN VARCHAR2 DEFAULT NULL,
68 p_trace_on IN VARCHAR2 DEFAULT NULL
69 ) ;
70
71 -------------debug_exit-----------------------
72 PROCEDURE debug_exit
73 (p_proc_name IN VARCHAR2 DEFAULT NULL,
74 p_trace_off IN VARCHAR2 DEFAULT NULL
75 ) ;
76
77 -------------create_absence_plan_details--------------
78 PROCEDURE create_absence_plan_details
79 ( p_assignment_id IN NUMBER
80 ,p_business_group_id IN NUMBER
81 ,p_absence_id IN NUMBER
82 ,p_pl_id IN NUMBER
83 ,p_pl_typ_id IN NUMBER
84 ,p_create_start_date IN DATE
85 ,p_create_end_date IN DATE
86 ,p_entitlements IN pqp_absval_pkg.t_entitlements
87 ,p_plan_information IN pqp_absval_pkg.rec_plan_information
88 ,p_entitlements_remaining OUT NOCOPY pqp_absval_pkg.t_entitlements
89 ,p_entitlement_UOM OUT NOCOPY VARCHAR2
90 ,p_working_days_per_week OUT NOCOPY NUMBER
91 ,p_fte OUT NOCOPY NUMBER
92 ) ;
93 ------------------------------------------------------------
94
95 FUNCTION get_review_date (
96 p_absence_start_date IN DATE
97 ,p_absence_end_date IN DATE
98 ,p_assignment_id IN NUMBER
99 ,p_business_group_id IN NUMBER
100 ,p_pl_typ_id IN NUMBER
101 ,p_scheme_period_duration IN NUMBER
102 ,p_scheme_period_type IN VARCHAR2
103 ,p_scheme_period_uom IN VARCHAR2
104 ,p_total_entitlement IN NUMBER
105 ,p_total_remaining IN OUT NOCOPY NUMBER
106 ,p_4_year_rolling_period IN BOOLEAN --TRUE for 4 year
107 -- PT Changes cchappid
108 ,p_working_days_in_week IN NUMBER
109 ,p_standard_work_days_in_week IN NUMBER
110 ,p_fulltime IN BOOLEAN
111 ,p_lookup_type IN VARCHAR2
112 ) RETURN DATE ;
113 ---------------------------------------------------------
114 FUNCTION get_no_pay_days ( p_rolling_start_date IN DATE
115 ,p_rolling_end_date IN DATE
116 ,p_assignment_id IN NUMBER
117 ,p_business_group_id IN NUMBER
118 ,p_pl_typ_id IN NUMBER
119 ,p_dont_count_css_nopay_days IN BOOLEAN
120 ,p_lookup_type IN VARCHAR2
121 ) RETURN NUMBER ;
122 ----------------------------------------------------------------
123 FUNCTION get_rolling_start_date (
124 p_rolling_end_date IN DATE
125 ,p_scheme_period_duration IN NUMBER
126 ,p_assignment_id IN NUMBER
127 ,p_business_group_id IN NUMBER
128 ,p_scheme_period_type IN VARCHAR2
129 ,p_scheme_period_uom IN VARCHAR2
130 ,p_pl_typ_id IN NUMBER
131 ,p_4_year_rolling_period IN BOOLEAN
132 ,p_lookup_type IN VARCHAR2
133 ) RETURN DATE ;
134 -----------------------------------------------------
135
136 END pqp_gb_css_daily_absences ;
[Home] [Help]