1 PACKAGE BODY PA_TRANS_UTILS AS
2 /* $Header: PATRUTLB.pls 115.4 2003/02/14 19:13:38 riyengar noship $
3 /* This api validates the actuals (EI) exists for the given assignment_id
4 * when assignment dates are modified / updated /inserted/ deleted
5 * if the EI exists for the given assignment and transaction date not
6 * falling between current start and end dates of the assignments OR
7 * if the transaction date doesnot falls between the new assignment start and end dates
8 * it returns the following error message depending on the calling modes
9 * calling mode error message
10 * ---------------------------------------------
11 * CANCEL / DELETE PA_EI_ASSGN_EXISTS
12 * UPDATE / INSERT PA_EI_ASSGN_DATE_OUTOFRANGE
13 * PA_EI_ASSGN_INVALID_PARAMS
14 */
15 PROCEDURE check_txn_exists (p_assignment_id IN NUMBER
16 ,p_old_start_date IN DATE
17 ,p_old_end_date IN DATE
18 ,p_new_start_date IN DATE
19 ,p_new_end_date IN DATE
20 ,p_calling_mode IN VARCHAR2 ---default 'CANCEL'
21 ,p_project_id IN NUMBER
22 ,p_person_id IN NUMBER
23 ,x_error_message_code OUT NOCOPY VARCHAR2
24 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
25
26 /* Bug fix: 2783152 Added EXISTS clause to reduce the cost from 91273 to 2
27 Please refer to the above bug for detail explain plan **/
28
29 CURSOR cur_validate_ei_ins IS
30 SELECT decode(p_calling_mode,'CANCEL','PA_EI_ASSGN_EXISTS'
31 ,'DELETE','PA_EI_ASSGN_EXISTS'
32 ,'UPDATE','PA_EI_ASSGN_DATE_OUTOFRANGE'
33 ,'INSERT','PA_EI_ASSGN_DATE_OUTOFRANGE'
34 ,'ERROR' ) error_msg_code
35 FROM dual
36 WHERE EXISTS
37 ( SELECT 'Y'
38 FROM pa_expenditure_items_all ei
39 ,pa_expenditures_all exp
40 WHERE exp.INCURRED_BY_PERSON_ID = p_person_id
41 AND exp.expenditure_id = ei.expenditure_id
42 AND ei.project_id = p_project_id
43 AND ei.assignment_id = p_assignment_id
44 AND ei.system_linkage_function in ('ST','OT','ER')
45 AND ei.expenditure_item_date not between trunc(p_new_start_date) and trunc(p_new_end_date)
46 ) ;
47
48
49 CURSOR cur_validate_ei_upd IS
50 SELECT decode(p_calling_mode,'CANCEL','PA_EI_ASSGN_EXISTS'
54 ,'ERROR' ) error_msg_code
51 ,'DELETE','PA_EI_ASSGN_EXISTS'
52 ,'UPDATE','PA_EI_ASSGN_DATE_OUTOFRANGE'
53 ,'INSERT','PA_EI_ASSGN_DATE_OUTOFRANGE'
55 FROM dual
56 WHERE EXISTS
57 ( SELECT 'Y'
58 FROM pa_expenditure_items_all ei
59 ,pa_expenditures_all exp
60 WHERE exp.INCURRED_BY_PERSON_ID = p_person_id
61 AND exp.EXPENDITURE_ENDING_DATE between p_old_start_date and p_old_end_date
62 AND exp.expenditure_id = ei.expenditure_id
63 AND ei.project_id = p_project_id
64 AND ei.assignment_id = p_assignment_id
65 AND ei.system_linkage_function in ('ST','OT','ER')
66 AND ei.expenditure_item_date between p_old_start_date and p_old_end_date
67 AND ei.expenditure_item_date not between p_new_start_date and p_new_end_date
68 );
69
70
71 CURSOR cur_validate_ei_del IS
72 SELECT decode(p_calling_mode,'CANCEL','PA_EI_ASSGN_EXISTS'
73 ,'DELETE','PA_EI_ASSGN_EXISTS'
74 ,'UPDATE','PA_EI_ASSGN_DATE_OUTOFRANGE'
75 ,'INSERT','PA_EI_ASSGN_DATE_OUTOFRANGE'
76 ,'ERROR' ) error_msg_code
77 FROM dual
78 WHERE EXISTS
79 ( SELECT 'Y'
80 FROM pa_expenditure_items_all ei
81 ,pa_expenditures_all exp
82 WHERE exp.INCURRED_BY_PERSON_ID = p_person_id
83 AND exp.EXPENDITURE_ENDING_DATE between p_new_start_date and p_new_end_date
84 AND exp.expenditure_id = ei.expenditure_id
85 AND ei.project_id = p_project_id
86 AND ei.assignment_id = p_assignment_id
87 AND ei.system_linkage_function in ('ST','OT','ER')
88 ) ;
89
90 l_exp_item_id NUMBER;
91 l_exp_item_date DATE;
92 l_error_msg VARCHAR2(100);
93
94 BEGIN
95
96 x_return_status := 'S';
97 x_error_message_code := NULL;
98 -- validate in parameters
99 IF nvl(p_assignment_id,0) = 0 OR p_project_id is NULL OR p_person_id is NULL OR
100 (p_calling_mode in ('CANCEL','DELETE') and (p_new_start_date is NULL or p_new_end_date is NULL ))OR
101 (p_calling_mode = 'UPDATE' and (p_new_start_date is NULL or
102 p_new_end_date is NULL or p_old_start_date is NULL or p_old_end_date is NULL )) OR
103 (p_calling_mode = 'INSERT' and (p_new_start_date is NULL or p_new_end_date is NULL )) THEN
104
105 x_error_message_code := 'PA_EI_ASSGN_INVALID_PARAMS';
106 x_return_status := 'E';
107
108 ELSE -- validate the EI
109
110 /** Bug fix:2706479 Cursor is broken into three parts based on the calling mode
111
112 OPEN cur_validate_ei;
113 FETCH cur_validate_ei INTO l_error_msg;
114 IF cur_validate_ei%found then
115 x_error_message_code := l_error_msg;
116 x_return_status := 'E';
117 END IF;
118 CLOSE cur_validate_ei;
119 RETURN;
120 ** End of Bug fix 2706479 **/
121
122 IF p_calling_mode in ('CANCEL','DELETE') Then
123 OPEN cur_validate_ei_del;
124 FETCH cur_validate_ei_del INTO l_error_msg;
125 IF cur_validate_ei_del%found then
126 x_error_message_code := l_error_msg;
127 x_return_status := 'E';
128 END IF;
129 CLOSE cur_validate_ei_del;
130
131 ELSIF p_calling_mode = 'INSERT' Then
132 OPEN cur_validate_ei_ins;
133 FETCH cur_validate_ei_ins INTO l_error_msg;
134 IF cur_validate_ei_ins%found then
135 x_error_message_code := l_error_msg;
136 x_return_status := 'E';
137 END IF;
138 CLOSE cur_validate_ei_ins;
139
140 ELSIF p_calling_mode = 'UPDATE' Then
141 OPEN cur_validate_ei_upd;
145 x_return_status := 'E';
142 FETCH cur_validate_ei_upd INTO l_error_msg;
143 IF cur_validate_ei_upd%found then
144 x_error_message_code := l_error_msg;
146 END IF;
147 CLOSE cur_validate_ei_upd;
148
149 End If;
150 END IF;
151
152 RETURN;
153
154
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 X_error_message_code := NULL;
158 x_return_status := 'S';
159 WHEN OTHERS THEN
160 IF cur_validate_ei_del%isopen then
161 close cur_validate_ei_del;
162 End if;
163 IF cur_validate_ei_ins%isopen then
164 close cur_validate_ei_ins;
165 End if;
166 IF cur_validate_ei_upd%isopen then
167 close cur_validate_ei_upd;
168 End if;
169 X_error_message_code := sqlcode||sqlerrm;
170 X_return_status := 'U';
171 raise;
172
173 END check_txn_exists;
174
175 END PA_TRANS_UTILS;
[Home] [Help]