1 PACKAGE BODY ap_web_expense_form AS
2 /* $Header: apwerfmb.pls 120.7 2006/05/18 21:14:19 qle noship $ */
3
4 CURSOR violations_cur (p_report_header_id NUMBER, p_line_number NUMBER) IS
5 SELECT
6 alc.displayed_field
7 FROM
8 ap_pol_violations apv,
9 ap_lookup_codes alc
10 WHERE
11 apv.violation_type = alc.lookup_code
12 AND alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
13 AND apv.report_header_id = p_report_header_id
14 AND apv.distribution_line_number = p_line_number
15 ORDER BY violation_number;
16
17
18 /* ********************************************************************
19
20 Procedure get_post_query_values:
21
22 Used by APXXXEER.fmb to fetch values in POST-QUERY trigger
23
24 ******************************************************************** */
25
26 procedure get_post_query_values(
27 p_report_header_id IN NUMBER,
28 p_distribution_line_number IN NUMBER,
29 p_min_allowed_amount OUT NOCOPY NUMBER,
30 p_violation_string OUT NOCOPY VARCHAR2,
31 p_category_code OUT NOCOPY VARCHAR2) IS
32
33 l_violation_string VARCHAR2(240);
34 l_category_code VARCHAR2(80);
35
36 begin
37
38 -- select minimum of the allowable amounts of the policy violation
39 -- rows for the current expense report line
40
41 begin
42
43 select min(allowable_amount)
44 into p_min_allowed_amount
45 from ap_pol_violations_all
46 where report_header_id = p_report_header_id
47 and distribution_line_number = p_distribution_line_number;
48
49 exception
50 when no_data_found then
51 p_min_allowed_amount :=0;
52 when others then
53 raise;
54 end;
55
56 -- select and concatenate the policy violations for the current expense
57 -- line; limit result to 240 characters.
58
59
60 FOR violations_rec IN violations_cur(p_report_header_id, p_distribution_line_number) LOOP
61 if (l_violation_string IS NULL) then
62 l_violation_string := substr(violations_rec.displayed_field, 1, 240);
63 else
64 l_violation_string := substr(l_violation_string || ', ' || violations_rec.displayed_field , 1, 240);
65 end if;
66
67 END LOOP;
68
69 p_violation_string := l_violation_string;
70
71 begin
72 select erp.category_code
73 into l_category_code
74 from ap_expense_report_params erp,
75 ap_expense_report_lines erl
76 where erl.report_header_id = p_report_header_id
77 and erl.distribution_line_number = p_distribution_line_number
78 and erl.web_parameter_id = erp.parameter_id;
79
80 exception
81 when no_data_found then
82 l_category_code := 'NONE';
83 when others then
84 raise;
85 end;
86
87 p_category_code := l_category_code;
88
89 end get_post_query_values;
90
91
92 /* ********************************************************************
93
94 Procedure get_num_violation_lines:
95
96 Used by view ap_expense_report_headers_v for APXXXEER.fmb
97 to fetch values for audit functionality
98
99 ******************************************************************** */
100
101 function get_num_violation_lines(
102 p_report_header_id IN NUMBER) RETURN NUMBER IS
103
104 l_count NUMBER;
105
106 begin
107
108 /* Bug fix 3365438
109 * The header level daily sum limit violation is not included in the
110 * count. Also changed to look at the _all table since this is used
111 * also from audit HTML module, with which the auditor can view
112 * reports from different orgs than the one on the responsibility.
113 * This can be done since ecen though report_header_id is not
114 * a unique key, there can be only one report header id across orgs. */
115 select count(*)
116 into l_count
117 from ap_expense_report_lines_all aerp
118 where report_header_id = p_report_header_id
119 and (itemization_parent_id is null or itemization_parent_id <> -1)
120 and exists (select 1 from ap_pol_violations_all apv
121 where apv.report_header_id = p_report_header_id
122 and apv.distribution_line_number = aerp.distribution_line_number);
123
124 return l_count;
125 end;
126
127 /* ********************************************************************
128
129 Procedure get_num_total_violations:
130
131 Used by view ap_expense_report_headers_v for APXXXEER.fmb
132 to fetch values for audit functionality
133
134 ******************************************************************** */
135
136 function get_num_total_violations(
137 p_report_header_id IN NUMBER) RETURN NUMBER IS
138
139 l_count NUMBER;
140
141 begin
142 /* Bug fix 3365438
143 * The header level daily sum limit violation is not included in the
144 * count. Also changed to look at the _all table since this is used
145 * also from audit HTML module, with which the auditor can view
146 * reports from different orgs than the one on the responsibility.
147 * This can be done since ecen though report_header_id is not
148 * a unique key, there can be only one report header id across orgs. */
149 select count(*)
150 into l_count
151 from ap_pol_violations_all apv
152 where apv.report_header_id = p_report_header_id
153 and apv.distribution_line_number<> -1;
154
155 return l_count;
156 end;
157
158 /* ********************************************************************
159
160 Procedure is_employee_active:
161
162 Used by view ap_expense_report_headers_v for APXXXEER.fmb
163 to fetch values for audit functionality
164
165 ******************************************************************** */
166
167 function is_employee_active(
168 p_employee_id IN NUMBER) RETURN VARCHAR2 IS
169 l_employee_is_active VARCHAR2(1);
170
171 begin
172
173 begin
174 select 'Y'
175 into l_employee_is_active
176 from per_workforce_current_x -- Bug 3176205: view name changed from hr_employees_current_v to
177 where person_id = p_employee_id; -- per_workforce_current_x to consider Contingent workers as well.
178
179 exception
180 when no_data_found then
181 l_employee_is_active := 'N';
182 when others then
183 raise;
184 end;
185
186 return l_employee_is_active;
187
188 end is_employee_active;
189
190
191 /* ********************************************************************
192
193 Procedure get_grace_period
194
195 Used by form APXXXEER.fmb to derive grace period
196 profile setting for employee who filed report.
197
198 ******************************************************************** */
199
200 function get_grace_period(
201 p_employee_id IN NUMBER) RETURN VARCHAR2 IS
202 l_defined BOOLEAN;
203 l_grace_period VARCHAR2(80);
204 l_userid VARCHAR2(80);
205 begin
206
207 begin
208 AP_WEB_OA_MAINFLOW_PKG.GetUserID(p_employee_id, l_userid);
209 fnd_profile.get_specific(
210 name_z => 'AP_WEB_POLICY_GRACE_PERIOD',
211 user_id_z => to_number(l_userid),
212 responsibility_id_z => NULL,
213 application_id_z => 200,
214 val_z => l_grace_period,
215 defined_z => l_defined);
216
217 return to_number(l_grace_period);
218
219 exception
220 when others then
221 raise;
222 end;
223
224 end get_grace_period;
225
226
227 end ap_web_expense_form;
[Home] [Help]