1 PACKAGE BODY PAY_IE_BIK_CHECK AS
2 /* $Header: pyiebikp.pkb 120.1 2006/09/19 09:44:37 mgettins noship $ */
3 --
4 -----------------------------------------------------------------------------
5 ----------------------purge delete function------------------------------
6 ----------------------------------------------------------------------------
7 --
8 --There are any claims that spans across any date cannot be purged.
9 --
10 FUNCTION purge_veh_alloc
11 (p_assignment_id IN NUMBER
12 ,p_effective_date IN DATE
13 ,p_vehicle_allocation_id IN NUMBER
14 ) RETURN NUMBER IS
15
16 -- Bug 3466513 Changed the cursor to get the count based on vehicle allocation id
17 /*CURSOR c_claim_count_cursor
18 IS
19 SELECT count(*)
20 FROM pay_element_types_f pet
21 ,pay_element_links_f pel
22 ,pay_element_entries_f pee
23 WHERE pel.element_type_id =pet.element_type_id
24 AND pee.assignment_id = p_assignment_id
25 AND pel.element_link_id = pee.element_link_id
26 AND pet.legislation_code = 'IE'
27 AND pet.element_name =('IE BIK Company Vehicle');*/
28
29 CURSOR c_claim_count_cursor
30 IS
31 SELECT count(*)
32 FROM pay_element_types_f pet
33 ,pay_element_links_f pel
34 ,pay_element_entries_f pee
35 ,pay_input_values_f piv
36 ,pay_element_entry_values_f peev
37 WHERE pel.element_type_id =pet.element_type_id
38 AND pee.assignment_id = p_assignment_id
39 AND pel.element_link_id = pee.element_link_id
40 AND pet.legislation_code = 'IE'
41 AND piv.element_type_id = pet.element_type_id
42 AND peev.element_entry_id = pee.element_entry_id
43 AND peev.input_value_id = piv.input_value_id
44 AND pet.element_name =('IE BIK Company Vehicle')
45 AND piv.name = 'Vehicle Allocation'
46 AND peev.screen_entry_value = p_vehicle_allocation_id;
47
48 l_alloc_count NUMBER ;
49
50 BEGIN
51
52 OPEN c_claim_count_cursor;
53 FETCH c_claim_count_cursor INTO l_alloc_count;
54 CLOSE c_claim_count_cursor;
55 --Check claims existence check
56
57 IF l_alloc_count > 0 THEN
58 RETURN -1 ;
59 END IF;
60 RETURN 0;
61 END purge_veh_alloc;
62 -- end function
63 -----------------------------------------------------------------------------
64 ----------------------End date delete----------------------------------------
65 -----------------------------------------------------------------------------
66 --
67 --There are no pending claims that spans across this date
68 --
69 FUNCTION enddate_veh_alloc
70 (p_assignment_id IN NUMBER
71 ,p_effective_date IN DATE
72 ) RETURN NUMBER IS
73 --Get the claim count for future and current date tracks
74 CURSOR c_claim_count_cursor IS
75 SELECT count(*)
76 FROM pay_element_types_f pet
77 ,pay_element_links_f pel
78 ,pay_element_entries_f pee
79 WHERE pel.element_type_id =pet.element_type_id
80 AND pee.assignment_id = p_assignment_id
81 AND pel.element_link_id = pee.element_link_id
82 AND pet.legislation_code = 'IE'
83 AND pet.element_name =('IE BIK Company Vehicle')
84 AND p_effective_date < pee.effective_end_date;
85
86 l_alloc_count NUMBER ;
87 BEGIN
88
89 OPEN c_claim_count_cursor;
90 FETCH c_claim_count_cursor INTO l_alloc_count;
91 CLOSE c_claim_count_cursor;
92 --Check claims existence check
93 IF l_alloc_count > 0 THEN
94 RETURN -1 ;
95 END IF;
96 RETURN 0;
97 END enddate_veh_alloc;
98 -- end function
99
100 PROCEDURE CHECK_BIK_ENTRY
101 (p_assignment_id_o IN NUMBER
102 ,p_vehicle_allocation_id in number --Bug 3466513 New parameter added
103 ,p_effective_date in date
104 ,p_datetrack_mode in varchar2
105 ,p_validation_start_date in date
106 ,p_validation_end_date in date
107 ) is
108 --
109 l_proc varchar2(72);
110 l_return_status NUMBER ;
111 l_message VARCHAR2(2500) ;
112 l_assignment_id pqp_vehicle_allocations_f.assignment_id%TYPE;
113 --
114 Begin
115 l_proc := 'PAY_IE_BIK_CHECK.CHECK_BIK_ENTRY';
116 hr_utility.set_location('Entering:'||l_proc, 1);
117 --
118 -- Added for GSI Bug 5472781
119 --
120 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
121 --
122 --Checking the vehicle availability before delete or purge.
123 IF p_datetrack_mode = 'ZAP' THEN
124 --This is for purge
125 l_return_status := purge_veh_alloc
126 (p_assignment_id => p_assignment_id_o
127 ,p_effective_date =>p_effective_date
128 ,p_vehicle_allocation_id => p_vehicle_allocation_id);
129 hr_utility.set_location('Purge delete status:'||l_return_status,2);
130 IF l_return_status = -1 THEN
131 fnd_message.set_name('PQP', 'PQP_230724_DEL_ALLOC_RESTRICT');
132 fnd_message.raise_error;
133 END IF;
134 /*Commented for Bug No. 3745749*/
135 /* ELSIF p_datetrack_mode = 'DELETE' THEN
136 --This is for enddate
137 l_return_status := enddate_veh_alloc
138 ( p_assignment_id => p_assignment_id_o
139 ,p_effective_date =>p_effective_date);
140 hr_utility.set_location('End date delete status :'||l_return_status,3);
141 IF l_return_status = -1 THEN
142 fnd_message.set_name('PQP', 'PQP_230700_CANCEL_INFO');
143 fnd_message.raise_error;
144 END IF;*/
145 END IF;
146 END IF;
147 hr_utility.set_location(' Leaving:'||l_proc,4);
148 Exception
149 when app_exception.application_exception then
150 IF hr_multi_message.exception_add
151 (
152 p_same_associated_columns => 'Y'
153 ) then
154 raise;
155 END IF;
156 End CHECK_BIK_ENTRY;
157 --
158 END PAY_IE_BIK_CHECK;
[Home] [Help]