1 PACKAGE BODY AP_WEB_MANAGEMENT_REPORTS_PKG AS
2 /* $Header: apwmrptb.pls 120.5.12010000.3 2008/12/18 23:36:27 rveliche ship $ */
3
4 /*=========================================================================================
5 | Procedure GetBaseCurrencyInfo
6 |
7 | Description: This procedure will retrieve the functional currency and the default
8 | exchange rate type for the currenct user.
9 |
10 | MODIFICATION HISTORY
11 | Person Date Comments
12 | krmenon 06-09-2002 Created
13 *=========================================================================================*/
14 PROCEDURE GetBaseCurrencyInfo ( P_BaseCurrencyCode OUT NOCOPY VARCHAR2,
15 P_ExchangeRateType OUT NOCOPY VARCHAR2 ) IS
16 BEGIN
17
18 SELECT base_currency_code, default_exchange_rate_type
19 INTO P_BaseCurrencyCode, P_ExchangeRateType
20 FROM ap_system_parameters;
21
22 EXCEPTION
23 WHEN TOO_MANY_ROWS THEN
24 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_MANAGEMENT_REPORTS_PKG.GetBaseCurrencyInfo',
25 SQLERRM);
26
27 APP_EXCEPTION.RAISE_EXCEPTION;
28 WHEN NO_DATA_FOUND THEN
29 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_MANAGEMENT_REPORTS_PKG.GetBaseCurrencyInfo',
30 SQLERRM);
31 APP_EXCEPTION.RAISE_EXCEPTION;
32 WHEN OTHERS THEN
33 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_MANAGEMENT_REPORTS_PKG.GetBaseCurrencyInfo',
34 SQLERRM);
35 APP_EXCEPTION.RAISE_EXCEPTION;
36
37 END GetBaseCurrencyInfo;
38
39
40 /*=========================================================================================
41 | Procedure GetPeriodDateRange
42 |
43 | Description: This procedure will retrieve the date range for a period type and a date
44 | falls in the date range of the period type.
45 |
46 | MODIFICATION HISTORY
47 | Person Date Comments
48 | krmenon 06-09-2002 Created
49 *=========================================================================================*/
50 PROCEDURE GetPeriodDateRange ( P_PeriodType IN VARCHAR2 ,
51 P_Date IN VARCHAR2 ,
52 P_StartDate OUT NOCOPY DATE,
53 P_EndDate OUT NOCOPY DATE ) IS
54
55 --
56 -- Cursor to fetch the date range for a given period
57 -- and a date.
58 --
59 CURSOR C_PeriodDateRange ( P_PeriodType VARCHAR2, P_Date VARCHAR2 ) IS
60 SELECT glps.start_date, glps.end_date
61 FROM ap_system_parameters SP,
62 gl_sets_of_books SOB,
63 gl_date_period_map map,
64 gl_periods glps
65 WHERE SOB.set_of_books_id = SP.set_of_books_id
66 AND MAP.period_set_name = SOB.period_set_name
67 AND MAP.period_type = P_PeriodType
68 AND MAP.accounting_date = to_date(P_Date, icx_sec.getID(icx_sec.PV_DATE_FORMAT))
69 AND GLPS.period_name = MAP.period_name
70 AND GLPS.period_set_name = SOB.period_set_name;
71
72 BEGIN
73 FOR rec in C_PeriodDateRange ( P_PeriodType, P_Date )
74 LOOP
75 P_StartDate := rec.start_date;
76 P_EndDate := rec.end_date;
77 END LOOP;
78
79 IF ( P_StartDate IS NULL OR P_EndDate IS NULL ) THEN
80 AP_WEB_DB_UTIL_PKG.RaiseException ('OIE_MANAGEMENT_REPORTS_PKG',
81 'Date Range not defined.',
82 'OIE_REPORTING_INVALID_DATE');
83 APP_EXCEPTION.RAISE_EXCEPTION;
84 END IF;
85 END GetPeriodDateRange;
86
87
88
89 /*=========================================================================================
90 | Function
91 |
92 | Description: This function is a recursive function to find out if the user has acces to
93 | view the data. It will try to find out the supervisor for the user whos data
94 | is being queried and if a match is found for the effective dates then returns
95 | Y, else N.
96 |
97 | NOTE:
98 | This is necessary because HR's security profile is based on access as of
99 | today and we need to have it as of the date range being queried for.
100 |
101 |
102 | Parameters:
103 | P_SupervisorId Employee Id of the supervisor
104 | P_PersionId Employee Id of the user who's data is being queried
105 | P_StartDate Start date of the date range in the query
106 | P_EndDate End date of the date range in the query
107 |
108 | Returns:
109 | 'Y' if supervisor match is found else 'N'
110 |
111 |
112 | MODIFICATION HISTORY
113 | Person Date Comments
114 | krmenon 11-26-2004 Created
115 | skoukunt 01-05-2007 bug 5534394: exit when L_ReturnValue is Y, otherwise it would
116 | return N when multiple assignments exist and the last assignment
117 | returned by the query does not satisfy the condition.
118 *=========================================================================================*/
119 FUNCTION IsSupervisor ( P_SupervisorId IN NUMBER,
120 P_PersonId IN NUMBER,
121 P_StartDate IN DATE,
122 P_EndDate IN DATE
123 ) RETURN VARCHAR2 IS
124
125 L_SupervisorId NUMBER(15);
126 L_ReturnValue VARCHAR2(1);
127
128 -- Cursor to fetch supervisor
129 CURSOR C_Supervisor (P_StartDate DATE, P_EndDate DATE) IS
130 SELECT distinct supervisor_id
131 FROM per_all_assignments_f
132 WHERE person_id = P_PersonId
133 AND P_StartDate < effective_end_date
134 AND P_EndDate > effective_start_date
135 AND assignment_type in ('C','E');
136
137 BEGIN
138
139 -- The supervisor id and person id are the same
140 -- data can be viewed
141 IF ( P_SupervisorId = P_PersonId ) THEN
142 RETURN 'Y';
143 END IF;
144
145 -- Find the supervisor for the person
146 -- Must traverse all tree branches to determine correct
147 -- supervisor
148 FOR supervisor in C_Supervisor(P_StartDate, P_EndDate)
149 LOOP
150
151 L_SupervisorId := supervisor.supervisor_id;
152
153 -- If the supervisor is null or same as person id
154 -- then reached top of hierarchy hence return false
155 -- If supervisor is same then return else recurse
156 IF ( L_SupervisorId IS NULL ) THEN
157 L_ReturnValue := 'N';
158 ELSIF ( P_PersonId = L_SupervisorId ) THEN
159 L_ReturnValue := 'N';
160 ELSIF ( P_SupervisorId = L_SupervisorId ) THEN
161 L_ReturnValue := 'Y';
162 exit;
163 ELSE
164 RETURN IsSupervisor(P_SupervisorId, L_SupervisorId, P_StartDate, P_EndDate);
165 END IF;
166
167 END LOOP;
168
169 -- Traversed all over and could not find matching record
170 RETURN nvl(L_ReturnValue, 'N');
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 RETURN 'N';
175
176 END IsSupervisor;
177
178
179 /*=========================================================================================
180 | Function
181 |
182 | Description: This function is a wrapper on the recursive function to find out if the user
183 | has acces to view the data based on the period specified.
184 | NOTE:
185 | This is necessary because HR's security profile is based on access as of
186 | today and we need to have it as of the date range being queried for.
187 |
188 | Parameters:
189 | P_SupervisorId Fnd User Id of the supervisor
190 | P_PersionId Employee Id of the user who's data is being queried
191 | P_PeriodType The period type for the query (month/quarter ...)
192 | P_Date The date which falls in the period type being queried
193 |
194 | Returns:
195 | 'Y' if supervisor has permission else 'N'.
196 |
197 |
198 | MODIFICATION HISTORY
199 | Person Date Comments
200 | krmenon 11-26-2004 Created
201 *=========================================================================================*/
202 FUNCTION HasPermission ( P_SupervisorId IN NUMBER,
203 P_PersonId IN NUMBER,
204 P_PeriodType IN VARCHAR2,
205 P_Date IN DATE
206 ) RETURN VARCHAR2 IS
207
208 L_StartDate DATE;
209 L_EndDate DATE;
210 L_SupervisorEmpId NUMBER(15);
211 BEGIN
212
213 -- In this call P_SupervisorId is the fnd user id
214 -- So we must get the emp id for this user
215
216 SELECT employee_id
217 INTO L_SupervisorEmpId
218 FROM fnd_user
219 WHERE user_id = P_SupervisorId;
220
221 -- Get the date range for the period
222 GetPeriodDateRange ( P_PeriodType ,
223 P_Date ,
224 L_StartDate ,
225 L_EndDate );
226
227 -- Call the recursive func. now
228 RETURN IsSupervisor( L_SupervisorEmpId, P_PersonId, L_StartDate, L_EndDate);
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 IF ((L_StartDate IS NULL) OR (L_EndDate IS NULL)) THEN
233 RETURN 'OIE_REPORTING_INVALID_DATE';
234 END IF;
235 RETURN 'N';
236
237 END HasPermission;
238
239
240 /*=========================================================================================
241 | Function
242 |
243 | Description: This function is a wrapper on the recursive function to find out if the user
244 | has acces to view the data based on the fnd user and date range specified.
245 | NOTE:
246 | This is necessary because HR's security profile is based on access as of
247 | today and we need to have it as of the date range being queried for.
248 |
249 |
250 | Parameters:
251 | P_SupervisorId Fnd User Id of the supervisor
252 | P_PersionId Employee Id of the user who's data is being queried
253 | P_StartDate Start date of the date range in the query
254 | P_EndDate End date of the date range in the query
255 |
256 | Returns:
257 | 'Y' if supervisor has permission else 'N'
258 |
259 |
260 | MODIFICATION HISTORY
261 | Person Date Comments
262 | krmenon 11-26-2004 Created
263 *=========================================================================================*/
264 FUNCTION HasPermission ( P_SupervisorId IN NUMBER,
265 P_PersonId IN NUMBER,
266 P_StartDate IN DATE,
267 P_EndDate IN DATE
268 ) RETURN VARCHAR2 IS
269
270 L_SupervisorEmpId NUMBER(15);
271
272 BEGIN
273
274 -- In this call P_SupervisorId is the fnd user id
275 -- So we must get the emp id for this user
276
277 SELECT employee_id
278 INTO L_SupervisorEmpId
279 FROM fnd_user
280 WHERE user_id = P_SupervisorId;
281
282 -- Call the recursive func. now
283 RETURN IsSupervisor( L_SupervisorEmpId, P_PersonId, P_StartDate, P_EndDate);
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 RETURN 'N';
288
289 END HasPermission;
290
291
292 /*=========================================================================================
293 | Procedure
294 |
295 | Description: This procedure will take calculate the values for the manager expense report
296 | based on the input parameters and store the value into a global temporary
297 | table, which in turn will be used by the BC4J components for UI display.
298 |
299 | MODIFICATION HISTORY
300 | Person Date Comments
301 | krmenon 06-09-2002 Created
302 *=========================================================================================*/
303 PROCEDURE ManagerHierarchySearch ( P_EmployeeId IN VARCHAR2,
304 P_ExpenseCategory IN VARCHAR2,
305 P_ViolationType IN VARCHAR2,
306 P_PeriodType IN VARCHAR2,
307 P_Date IN VARCHAR2,
308 P_UserCurrencyCode IN VARCHAR2,
309 P_QryType IN VARCHAR2 ) AS
310
311 -- Declare program variables
312 TYPE ByManagerRecType IS RECORD (
313 employee_id ap_expense_report_headers.EMPLOYEE_ID%TYPE,
314 full_name per_workforce_x.FULL_NAME%TYPE,
315 total_expenses NUMBER,
316 violation_line_amount NUMBER,
317 violation_amount NUMBER,
318 percent_violation NUMBER,
319 number_of_violations NUMBER,
320 most_violated_policy ap_lookup_codes.DISPLAYED_FIELD%TYPE,
321 supervisor_id per_workforce_x.SUPERVISOR_ID%TYPE,
322 effective_start_date DATE, -- 4319234 the two date columns are added to the record
323 effective_end_date DATE
324 );
325
326
327 TYPE ByManagerTabType IS TABLE OF ByManagerRecType
328 INDEX BY BINARY_INTEGER;
329
330 -- Local Variables
331 L_ByManagerTab ByManagerTabType;
332
333 L_TabIndex BINARY_INTEGER := 0;
334 L_EmployeeId ap_expense_report_headers.EMPLOYEE_ID%TYPE;
335 L_BaseCurrencyCode ap_system_parameters.BASE_CURRENCY_CODE%TYPE;
336 L_ExchangeRateType ap_system_parameters.DEFAULT_EXCHANGE_RATE_TYPE%TYPE;
337 L_ExpenseAmount NUMBER := 0;
338 L_ViolationLineAmount NUMBER;
339 L_ViolationAmount NUMBER;
340 L_Convert BOOLEAN := FALSE;
341 L_StartDate DATE;
342 L_EndDate DATE;
343 L_EffectiveStartDate DATE;
344 L_EffectiveEndDate DATE;
345 L_PrevReportHeaderId ap_expense_report_headers.REPORT_HEADER_ID%TYPE := 0;
346 L_ReportHeaderId ap_expense_report_headers.REPORT_HEADER_ID%TYPE := 0;
347 L_PrevDistributionLineNumber ap_expense_report_lines.DISTRIBUTION_LINE_NUMBER%TYPE;
348 L_DistributionLineNumber ap_expense_report_lines.DISTRIBUTION_LINE_NUMBER%TYPE;
349 L_NumberOfViolations NUMBER;
350
351 --
352 -- Cursor to fetch the line amounts for an employee for an expense category
353 -- for a given period.
354 --
355 CURSOR C_ExpensesForCategory ( P_EmployeeId VARCHAR2,
356 P_ExpenseCategory VARCHAR2,
357 P_StartDate DATE,
358 P_EndDate DATE,
359 P_BaseCurrencyCode VARCHAR2,
360 P_ExchangeRateType VARCHAR2
361 ) IS
362 SELECT sum(decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
363 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
364 P_BaseCurrencyCode,
365 sysdate,
366 P_ExchangeRateType,
367 aerl.amount
368 ))) as expense_amount
369 FROM ap_expense_report_lines_v aerl
370 WHERE aerl.employee_id = P_EmployeeId
371 AND aerl.week_end_date between P_StartDate and P_EndDate
372 AND nvl(aerl.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory);
373
374 --
375 -- Cursor to fetch lines with violation for an employee/direct report and expense category and violation type
376 --
377 CURSOR C_ViolationLinesForCategory ( P_EmployeeId VARCHAR2,
378 P_ExpenseCategory VARCHAR2,
379 P_ViolationType VARCHAR2,
380 P_StartDate VARCHAR2,
381 P_EndDate VARCHAR2,
382 P_BaseCurrencyCode VARCHAR2,
383 P_ExchangeRateType VARCHAR2
384 ) IS
385 SELECT SUM(line_amount) AS violation_line_amount
386 FROM
387 (
388 SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
389 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
390 P_BaseCurrencyCode,
391 sysdate,
392 P_ExchangeRateType,
393 aerl.amount
394 )) as line_amount
395 FROM ap_expense_report_lines_v aerl
396 WHERE EXISTS ( SELECT 'X'
397 FROM ap_pol_violations_all apv
398 WHERE apv.report_header_id = aerl.report_header_id
399 AND apv.distribution_line_number = aerl.distribution_line_number
400 AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
401 AND ( aerl.employee_id= P_EmployeeId or aerl.paid_on_behalf_employee_id = P_EmployeeId )
402 AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
403 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
404 );
405
406 --
407 -- Cursor to fetch the violation amounts for an employee for an expense category
408 -- for a violation type and for a period.
409 --
410 CURSOR C_ViolationsForExpenseCategory ( P_EmployeeId VARCHAR2,
411 P_ExpenseCategory VARCHAR2,
412 P_ViolationType VARCHAR2,
413 P_StartDate DATE,
414 P_EndDate DATE,
415 P_BaseCurrencyCode VARCHAR2,
416 P_ExchangeRateType VARCHAR2
417 ) IS
418 SELECT sum(violation_amount) as violation_amount,
419 sum(number_of_violations) as number_of_violations
420 FROM
421 ( SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
422 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
423 P_BaseCurrencyCode,
424 sysdate,
425 P_ExchangeRateType,
426 aerv.violation_amount
427 ))) as violation_amount,
428 count(aerv.report_header_id) as number_of_violations
429 FROM ap_expense_report_violations_v aerv
430 WHERE ( (aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
431 AND aerv.week_end_date between P_StartDate and P_EndDate
432 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
433 AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
434 AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
435 UNION ALL
436 /* The below query is to fetch the daily sum limit violations for MEALS category */
437 SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
438 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
439 P_BaseCurrencyCode,
440 SYSDATE,
441 P_ExchangeRateType,
442 apv.exceeded_amount
443 )) as violation_amount,
444 1 as number_of_violations
445 FROM ap_expense_report_headers_all aerh,
446 ap_pol_violations_all apv
447 WHERE aerh.report_header_id = apv.report_header_id
448 AND apv.distribution_line_number = -1
449 AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
450 AND aerh.source <> 'Both Pay'
451 AND NVL(aerh.expense_status_code,
452 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
453 aerh.Workflow_approved_flag,
454 aerh.report_header_id,
455 'N'
456 )) IN ('MGRPAYAPPR','INVOICED','PAID')
457 AND ( (aerh.employee_id = P_EmployeeId) or (aerh.paid_on_behalf_employee_id = P_EmployeeId))
458 AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
459 AND aerh.week_end_date BETWEEN P_StartDate AND P_EndDate
460 UNION ALL
461 /* The below query is to bundle up RECEIPT_MISSING violations per line */
462 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
463 GL_CURRENCY_API.convert_amount_sql( aerv.currency_code,
464 P_BaseCurrencyCode,
465 SYSDATE,
466 P_ExchangeRateType,
467 aerv.violation_amount
468 ))) as violation_amount,
469 1 as number_of_violations
470 FROM ap_expense_report_violations_v aerv
471 WHERE ( (aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
472 AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
473 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
474 AND aerv.week_end_date BETWEEN P_StartDate AND P_EndDate
475 group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
476 );
477
478
479 --
480 -- Cursor to fetch the violation type with max violations
481 --
482 CURSOR C_MaxViolationByType ( P_EmployeeId VARCHAR2,
483 P_ExpenseCategory VARCHAR2,
484 P_StartDate DATE,
485 P_EndDate DATE
486 ) IS
487 SELECT count(number_of_violations),
488 violation_type
489 from
490 (
491 SELECT 1 as number_of_violations,
492 alc.displayed_field as violation_type
493 FROM ap_lookup_codes alc,
494 ap_expense_report_violations_v aerv
495 WHERE alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
496 AND alc.lookup_code = aerv.violation_type
497 AND ( aerv.employee_id = P_EmployeeId OR aerv.paid_on_behalf_employee_id = P_EmployeeId)
498 AND aerv.week_end_date between P_StartDate and P_EndDate
499 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
500 AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
501 UNION ALL
502 /* Count all the violations for Daily Sum Limit */
503 SELECT 1 as number_of_violations,
504 alc.displayed_field as violation_type
505 FROM ap_expense_report_headers_all aerh,
506 ap_pol_violations_all apv,
507 ap_lookup_codes alc
508 WHERE aerh.report_header_id = apv.report_header_id
509 AND apv.distribution_line_number = -1
510 AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
511 AND aerh.source <> 'Both Pay'
512 AND NVL(aerh.expense_status_code,
513 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
514 aerh.Workflow_approved_flag,
515 aerh.report_header_id,
516 'N'
517 )) IN ('MGRPAYAPPR','INVOICED','PAID')
518 AND apv.violation_type = 'DAILY_SUM_LIMIT'
519 AND ( aerh.employee_id = P_EmployeeId OR aerh.paid_on_behalf_employee_id = P_EmployeeId)
520 AND ( 'MEALS' = P_ExpenseCategory OR 'ALL' = P_ExpenseCategory)
521 AND aerh.week_end_date between P_StartDate and P_EndDate
522 AND apv.violation_type = alc.lookup_code
523 AND alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
524 UNION ALL
525 /* Count all the violations for Receipts missing */
526 SELECT 1 as number_of_violations,
527 alc.displayed_field as violation_type
528 FROM ap_lookup_codes alc,
529 ap_expense_report_violations_v aerv
530 WHERE alc.lookup_type = 'OIE_POL_VIOLATION_TYPES'
531 AND alc.lookup_code = aerv.violation_type
532 AND ( aerv.employee_id = P_EmployeeId OR aerv.paid_on_behalf_employee_id = P_EmployeeId)
533 AND aerv.week_end_date between P_StartDate and P_EndDate
534 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
535 AND aerv.violation_type = 'RECEIPT_MISSING'
536 group by nvl(aerv.itemization_parent_id, aerv.report_line_id), alc.displayed_field
537 )
538 GROUP BY violation_type
539 ORDER BY 1 desc;
540
541 --
542 -- Cursor to fetch all employee and direct reports
543 --
544 CURSOR C_Employees ( P_EmployeeId VARCHAR2,
545 P_QryType VARCHAR2,
546 P_StartDate DATE,
547 P_EndDate DATE ) IS
548 /* SELECT distinct
549 pap.full_name,
550 pap.person_id,
551 paa.supervisor_id,
552 greatest(paa.effective_start_date, P_StartDate) as Start_Date,
553 least(paa.effective_end_date, P_EndDate) as End_Date
554 FROM per_all_people_f pap,
555 per_all_assignments_f paa
556 WHERE pap.person_id = paa.person_id
557 AND pap.person_id = P_EmployeeId
558 AND P_StartDate < paa.effective_end_date
559 AND P_EndDate > paa.effective_start_date
560 AND (paa.assignment_type = 'E' OR paa.assignment_type = 'C')
561 AND P_StartDate < pap.effective_end_date
562 AND P_EndDate > pap.effective_start_date
563 UNION ALL
564 SELECT distinct
565 pap.full_name,
566 pap.person_id,
567 paa.supervisor_id,
568 greatest(paa.effective_start_date, P_StartDate) as Start_Date,
569 least(paa.effective_end_date, P_EndDate) as End_Date
570 FROM per_all_people_f pap,
571 per_all_assignments_f paa
572 WHERE pap.person_id = paa.person_id
573 AND paa.supervisor_id = P_EmployeeId
574 AND 'MANAGER' = P_QryType
575 AND P_StartDate < paa.effective_end_date
576 AND P_EndDate > paa.effective_start_date
577 AND (paa.assignment_type = 'E' OR paa.assignment_type = 'C')
578 AND P_StartDate < pap.effective_end_date
579 AND P_EndDate > pap.effective_start_date;
580 */
581 -- 4319234: Query changed to produce only a coalesce assignmetns separated by 1 day
582 SELECT FULL_NAME,
583 PERSON_ID,
584 SUPERVISOR_ID,
585 GREATEST( P_StartDate, MIN( effective_start_date ) ) as Start_Date,
586 MAX_END_DATE as End_Date
587 FROM
588 ( SELECT distinct
589 pap.full_name,
590 pap.person_id,
591 paa.supervisor_id,
592 paa.effective_start_date,
593 paa.effective_end_date,
594 ( select least( P_EndDate , max(effective_end_date) )
595 from per_all_assignments_f
596 where person_id = pap.person_id
597 and supervisor_id = paa.supervisor_id
598 and ( assignment_type = 'E' OR assignment_type = 'C')
599 start with effective_start_date = paa.effective_start_date
600 connect by person_id = pap.person_id
601 and supervisor_id = paa.supervisor_id
602 AND ( assignment_type = 'E' OR assignment_type = 'C')
603 and prior effective_end_date = ( effective_start_date - 1 )
604 ) max_end_date
605 FROM per_all_people_f pap,
606 per_all_assignments_f paa
607 WHERE pap.person_id = paa.person_id
608 AND ( pap.person_id = P_EmployeeId or ( P_QryType = 'MANAGER' AND paa.supervisor_id = P_EmployeeId ) )
609 AND P_StartDate <= paa.effective_end_date
610 AND P_EndDate >= paa.effective_start_date
611 AND (paa.assignment_type = 'E' OR paa.assignment_type = 'C')
612 AND P_StartDate <= pap.effective_end_date
613 AND P_EndDate >= pap.effective_start_date
614 ) V1
615 WHERE max_end_date is not null
616 GROUP BY FULL_NAME, PERSON_ID, SUPERVISOR_ID, MAX_END_DATE;
617
618 BEGIN
619
620
621 -- Get the period date range
622 GetPeriodDateRange(P_PeriodType, P_Date, L_StartDate, L_EndDate);
623
624
625 -- Get the Base Currency Code
626 GetBaseCurrencyInfo(L_BaseCurrencyCode, L_ExchangeRateType);
627
628 -- If the base currency is not same as user preference currency the
629 -- set the conversion flag to true
630 IF ( L_BaseCurrencyCode <> NVL(P_UserCurrencyCode, L_BaseCurrencyCode) ) THEN
631 L_Convert := TRUE;
632 END IF;
633
634 --
635 -- Begin the loop to fetch data
636 --
637 FOR emprec in C_Employees( P_EmployeeId, P_QryType, L_StartDate, L_EndDate )
638 LOOP
639 -- Increment Table Index
640 L_TabIndex := L_TabIndex + 1;
641
642 L_ByManagerTab( L_TabIndex ).employee_id := emprec.person_id;
643 L_ByManagerTab( L_TabIndex ).full_name := emprec.full_name;
644 L_ByManagerTab( L_TabIndex ).supervisor_id := emprec.supervisor_id;
645
646 -- Initialize all amounts fields as 0
647 L_ByManagerTab( L_TabIndex ).total_expenses := 0;
648 L_ByManagerTab( L_TabIndex ).violation_line_amount := 0;
649 L_ByManagerTab( L_TabIndex ).violation_amount := 0;
650 L_ByManagerTab( L_TabIndex ).percent_violation := 0;
651 L_ByManagerTab( L_TabIndex ).number_of_violations := 0;
652 L_ByManagerTab( L_TabIndex ).most_violated_policy := NULL;
653
654 L_EmployeeId := emprec.person_id;
655 L_EffectiveStartDate := emprec.start_date;
656 L_EffectiveEndDate := emprec.end_date;
657 L_ExpenseAmount := 0;
658 L_PrevReportHeaderId := 0;
659 L_PrevDistributionLineNumber := 0;
660 L_ReportHeaderId := 0;
661 L_DistributionLineNumber := 0;
662
663 -- Change for 4319234 as it is necessary to store every assignment's start date and end date
664 L_ByManagerTab( L_TabIndex ).effective_start_date := L_EffectiveStartDate;
665 L_ByManagerTab( L_TabIndex ).effective_end_date := L_EffectiveEndDate;
666
667 -- Get total expenses for a category
668 FOR exprec in C_ExpensesForCategory ( L_EmployeeId,
669 P_ExpenseCategory,
670 L_EffectiveStartDate,
671 L_EffectiveEndDate,
672 L_BaseCurrencyCode,
673 L_ExchangeRateType
674 )
675 LOOP
676 L_ExpenseAmount := L_ExpenseAmount + NVL(exprec.expense_amount,0);
677 END LOOP;
678
679
680 -- Get total violations for a category and violation type
681 L_ViolationLineAmount := 0;
682 L_ViolationAmount := 0;
683 L_NumberOfViolations := 0;
684 L_PrevReportHeaderId := 0;
685 L_PrevDistributionLineNumber := 0;
686 L_ReportHeaderId := 0;
687 L_DistributionLineNumber := 0;
688
689 -- Get the total for the lines which have violations
690 FOR violline in C_ViolationLinesForCategory ( L_EmployeeId,
691 P_ExpenseCategory,
692 P_ViolationType,
693 L_EffectiveStartDate,
694 L_EffectiveEndDate,
695 L_BaseCurrencyCode,
696 L_ExchangeRateType
697 )
698 LOOP
699 L_ViolationLineAmount := L_ViolationLineAmount + NVL(violline.violation_line_amount,0);
700 END LOOP;
701
702 -- Get the violaiton totals
703 FOR violrec in C_ViolationsForExpenseCategory ( L_EmployeeId,
704 P_ExpenseCategory,
705 P_ViolationType,
706 L_EffectiveStartDate,
707 L_EffectiveEndDate,
708 L_BaseCurrencyCode,
709 L_ExchangeRateType
710 )
711 LOOP
712 L_ViolationAmount := L_ViolationAmount + NVL(violrec.violation_amount,0);
713
714 -- Get number of violations
715 L_NumberOfViolations := L_NumberOfViolations + violrec.number_of_violations;
716 END LOOP;
717
718 -- Calculate % of violation
719 -- Bug 2925136: Round the value for percentage calculation to 2 decimal places
720 IF ( L_ViolationLineAmount > 0 ) THEN
721 L_ByManagerTab( L_TabIndex ).percent_violation := round((L_ViolationAmount * 100) /
722 L_ViolationLineAmount , 2 );
723 END IF;
724
725 L_ByManagerTab( L_TabIndex ).number_of_violations := L_NumberOfViolations;
726
727 -- If amount needs to be converted to user preference currency
728 -- convert the amounts
729 IF ( L_Convert ) THEN
730 L_ByManagerTab( L_TabIndex ).total_expenses := gl_currency_api.CONVERT_AMOUNT_SQL(
731 L_BaseCurrencyCode,
732 P_UserCurrencyCode,
733 sysdate,
734 L_ExchangeRateType,
735 L_ExpenseAmount
736 );
737
738 L_ByManagerTab( L_TabIndex ).violation_line_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
739 L_BaseCurrencyCode,
740 P_UserCurrencyCode,
741 sysdate,
742 L_ExchangeRateType,
743 L_ViolationLineAmount
744 );
745 L_ByManagerTab( L_TabIndex ).violation_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
746 L_BaseCurrencyCode,
747 P_UserCurrencyCode,
748 sysdate,
749 L_ExchangeRateType,
750 L_ViolationAmount
751 );
752 ELSE
753 L_ByManagerTab( L_TabIndex ).total_expenses := round(L_ExpenseAmount, 2);
754 L_ByManagerTab( L_TabIndex ).violation_line_amount := round(L_ViolationLineAmount, 2);
755 L_ByManagerTab( L_TabIndex ).violation_amount := round(L_ViolationAmount, 2);
756 END IF;
757
758 -- Fetch the most violated violation type
759 FOR maxviolrec in C_MaxViolationByType ( L_EmployeeId,
760 P_ExpenseCategory,
761 L_EffectiveStartDate,
762 L_EffectiveEndDate
763 )
764 LOOP
765 L_ByManagerTab( L_TabIndex ).most_violated_policy := maxviolrec.violation_type;
766 -- akita/krmenon 27 Jul 2004
767 -- need to exit after fetch of first row as that has the max.
768 exit;
769 END LOOP;
770
771 END LOOP;
772
773 -- Clear previous query results
774 DELETE FROM AP_WEB_MANAGEMENT_REPORTS_GT;
775
776 FOR i in 1..L_TabIndex
777 LOOP
778 -- Insert search results into global temporary table
779 INSERT INTO AP_WEB_MANAGEMENT_REPORTS_GT (
780 EMPLOYEE_ID,
781 SUPERVISOR_ID,
782 TOTAL_EXPENSES,
783 VIOLATION_LINE_AMOUNT,
784 VIOLATION_AMOUNT,
785 PERCENT_VIOLATION,
786 NUMBER_OF_VIOLATIONS,
787 MOST_VIOLATED_POLICY,
788 FULL_NAME,
789 EXPENSE_CATEGORY,
790 VIOLATION_TYPE,
791 PERIOD_TYPE,
792 PERIOD_DATE,
793 START_DATE_RANGE,
794 END_DATE_RANGE,
795 ROLLUP_TYPE,
796 REPORTING_CURRENCY_CODE
797 )
798 VALUES (
799 L_ByManagerTab( i ).employee_id,
800 L_ByManagerTab( i ).supervisor_id,
801 L_ByManagerTab( i ).total_expenses,
802 L_ByManagerTab( i ).violation_line_amount,
803 L_ByManagerTab( i ).violation_amount,
804 L_ByManagerTab( i ).percent_violation,
805 L_ByManagerTab( i ).number_of_violations,
806 L_ByManagerTab( i ).most_violated_policy,
807 L_ByManagerTab( i ).full_name,
808 P_ExpenseCategory,
809 P_ViolationType,
810 P_PeriodType,
811 P_Date,
812 L_ByManagerTab( i ).effective_start_date,
813 L_ByManagerTab( i ).effective_end_date,
814 P_QryType,
815 NVL(P_UserCurrencyCode, L_BaseCurrencyCode)
816 );
817
818 END LOOP;
819
820 -- Commit all the inserts
821 COMMIT;
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_MANAGEMENT_REPORTING_PKG.ManagerHierarchySearch',
826 SQLERRM);
827 APP_EXCEPTION.RAISE_EXCEPTION;
828 -- dbms_output.put_line('EXCEPTION: '||SQLERRM) ;
829
830
831 END ManagerHierarchySearch;
832
833
834 /*=========================================================================================
835 | Procedure
836 |
837 | Description: This procedure will take calculate the values for the expense category
838 | report based on the input parameters and store the value into a global
839 | temporary table, which in turn will be used by the BC4J components for
840 | UI display.
841 |
842 | MODIFICATION HISTORY
843 | Person Date Comments
844 | krmenon 06-09-2002 Created
845 *=========================================================================================*/
846 PROCEDURE ExpenseCategorySearch ( P_EmployeeId IN VARCHAR2 ,
847 P_ExpenseCategory IN VARCHAR2 ,
848 P_ViolationType IN VARCHAR2 ,
849 P_PeriodType IN VARCHAR2 ,
850 P_Date IN VARCHAR2 ,
851 P_UserCurrencyCode IN VARCHAR2 ,
852 P_QryType IN VARCHAR2 ) IS
853
854 -- Declare program variables
855 TYPE ByExpenseCategoryRecType IS RECORD (
856 employee_id ap_expense_report_headers.EMPLOYEE_ID%TYPE,
857 total_expenses NUMBER,
858 violation_line_amount NUMBER,
859 violation_amount NUMBER,
860 allowable_amount NUMBER,
861 percent_violation NUMBER,
862 percent_allowable NUMBER,
863 number_of_violations NUMBER,
864 expense_category ap_lookup_codes.LOOKUP_CODE%TYPE,
865 expense_category_desc ap_lookup_codes.DISPLAYED_FIELD%TYPE
866 );
867
868
869 TYPE ByExpenseCategoryTabType IS TABLE OF ByExpenseCategoryRecType
870 INDEX BY BINARY_INTEGER;
871
872 -- Local Variables
873 L_ByExpCatTab ByExpenseCategoryTabType;
874
875 L_TabIndex BINARY_INTEGER := 0;
876 L_EmployeeId ap_expense_report_headers.EMPLOYEE_ID%TYPE;
877 L_BaseCurrencyCode ap_system_parameters.BASE_CURRENCY_CODE%TYPE;
878 L_ExchangeRateType ap_system_parameters.DEFAULT_EXCHANGE_RATE_TYPE%TYPE;
879 L_ExpenseCategory ap_lookup_codes.LOOKUP_CODE%TYPE;
880 L_ExpenseAmount NUMBER := 0;
881 L_ViolationLineAmount NUMBER;
882 L_ViolationAmount NUMBER;
883 L_AllowableAmount NUMBER;
884 L_Convert BOOLEAN := FALSE;
885 L_StartDate DATE;
886 L_EndDate DATE;
887 L_PrevReportHeaderId ap_expense_report_headers.REPORT_HEADER_ID%TYPE := 0;
888 L_ReportHeaderId ap_expense_report_headers.REPORT_HEADER_ID%TYPE := 0;
889 L_PrevDistributionLineNumber ap_expense_report_lines.DISTRIBUTION_LINE_NUMBER%TYPE;
890 L_DistributionLineNumber ap_expense_report_lines.DISTRIBUTION_LINE_NUMBER%TYPE;
891 L_NumberOfViolations NUMBER;
892
893
894 --
895 -- Cursor to fetch all expense categories
896 --
897 CURSOR C_ExpenseCategories ( P_ExpenseCategory VARCHAR2 )IS
898 SELECT lookup_code,
899 displayed_field
900 FROM ap_lookup_codes
901 WHERE lookup_type = 'OIE_EXPENSE_CATEGORY'
902 AND lookup_code = decode (P_ExpenseCategory, 'ALL', lookup_code, P_ExpenseCategory);
903
904 --
905 -- Cursor to fetch expense amount for an employee/direct report and expense category
906 --
907 CURSOR C_ExpenseAmountForCategory ( P_EmployeeId VARCHAR2,
908 P_StartDate DATE,
909 P_EndDate DATE,
910 P_ExpenseCategory VARCHAR2,
911 P_QryType VARCHAR2,
912 P_BaseCurrencyCode VARCHAR2,
913 P_ExchangeRateType VARCHAR2
914 ) IS
915 SELECT sum(NVL(expense_amount,0)) as expense_amount
916 FROM
917 ( SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
918 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
919 P_BaseCurrencyCode,
920 sysdate,
921 P_ExchangeRateType,
922 aerl.amount
923 )) as expense_amount
924 FROM ap_expense_report_lines_v aerl
925 WHERE ((aerl.employee_id= P_EmployeeId) or (aerl.paid_on_behalf_employee_id = P_EmployeeId))
926 AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
927 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
928 UNION ALL
929 SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
930 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
931 P_BaseCurrencyCode,
932 sysdate,
933 P_ExchangeRateType,
934 aerl.amount
935 )) as expense_amount
936 FROM per_all_assignments_f paf,
937 ap_expense_report_lines_v aerl
938 WHERE paf.supervisor_id = P_EmployeeId
939 AND paf.effective_start_date < P_EndDate
940 AND paf.effective_end_date > P_StartDate
941 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
942 AND (( aerl.employee_id = paf.person_id) or (aerl.paid_on_behalf_employee_id = paf.person_id))
943 AND aerl.week_end_date BETWEEN greatest(paf.effective_start_date,P_StartDate) AND least(paf.effective_end_date,P_EndDate)
944 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
945 AND 'MANAGER' = P_QryType
946 );
947
948 --
949 -- Cursor to fetch lines with violation for an employee/direct report and expense category and violation type
950 --
951 CURSOR C_ViolationLinesForCategory ( P_EmployeeId VARCHAR2,
952 P_ExpenseCategory VARCHAR2,
953 P_ViolationType VARCHAR2,
954 P_StartDate VARCHAR2,
955 P_EndDate VARCHAR2,
956 P_BaseCurrencyCode VARCHAR2,
957 P_ExchangeRateType VARCHAR2
958 ) IS
959 SELECT SUM(line_amount) AS violation_line_amount
960 FROM
961 (
962 SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
963 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
964 P_BaseCurrencyCode,
965 sysdate,
966 P_ExchangeRateType,
967 aerl.amount
968 )) as line_amount
969 FROM ap_expense_report_lines_v aerl
970 WHERE EXISTS ( SELECT 'X'
971 FROM ap_pol_violations_all apv
972 WHERE apv.report_header_id = aerl.report_header_id
973 AND apv.distribution_line_number = aerl.distribution_line_number
974 AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
975 AND ((aerl.employee_id= P_EmployeeId) or (aerl.paid_on_behalf_employee_id = P_EmployeeId))
976 AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
977 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
978 UNION ALL
979 SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
980 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
981 P_BaseCurrencyCode,
982 sysdate,
983 P_ExchangeRateType,
984 aerl.amount
985 )) as line_amount
986 FROM ap_expense_report_lines_v aerl,
987 per_all_assignments_f paf
988 WHERE EXISTS ( SELECT 'X'
989 FROM ap_pol_violations_all apv
990 WHERE apv.report_header_id = aerl.report_header_id
991 AND apv.distribution_line_number = aerl.distribution_line_number
992 AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
993 AND paf.supervisor_id = P_EmployeeId
994 AND paf.effective_start_date < P_EndDate
995 AND paf.effective_end_date > P_StartDate
996 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
997 AND ((aerl.employee_id = paf.person_id) or (aerl.paid_on_behalf_employee_id = paf.person_id))
998 AND aerl.week_end_date BETWEEN greatest(paf.effective_start_date,P_StartDate) AND least(paf.effective_end_date,P_EndDate)
999 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
1000 AND 'MANAGER' = P_QryType
1001 );
1002
1003 --
1004 -- Cursor to fetch violation amounts for an employee/direct report and expense category and violation type
1005 --
1006 CURSOR C_ViolationsForCategory ( P_EmployeeId VARCHAR2,
1007 P_ExpenseCategory VARCHAR2,
1008 P_ViolationType VARCHAR2,
1009 P_StartDate VARCHAR2,
1010 P_EndDate VARCHAR2,
1011 P_BaseCurrencyCode VARCHAR2,
1012 P_ExchangeRateType VARCHAR2
1013 ) IS
1014 SELECT sum(nvl(violation_amount,0)) as violation_amount,
1015 sum(nvl(allowable_amount,0)) as allowable_amount,
1016 sum(nvl(number_of_violations,0)) as number_of_violations
1017 FROM
1018 (
1019 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1020 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1021 P_BaseCurrencyCode,
1022 sysdate,
1023 P_ExchangeRateType,
1024 aerv.violation_amount
1025 ))) as violation_amount,
1026 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1027 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1028 P_BaseCurrencyCode,
1029 sysdate,
1030 P_ExchangeRateType,
1031 aerv.allowable_amount
1032 ))) as allowable_amount,
1033 count(aerv.report_header_id) as number_of_violations
1034 FROM ap_expense_report_violations_v aerv
1035 WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
1036 AND aerv.week_end_date between P_StartDate and P_EndDate
1037 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1038 AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
1039 AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
1040 UNION ALL
1041 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1042 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1043 P_BaseCurrencyCode,
1044 sysdate,
1045 P_ExchangeRateType,
1046 aerv.violation_amount
1047 ))) as violation_amount,
1048 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1049 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1050 P_BaseCurrencyCode,
1051 sysdate,
1052 P_ExchangeRateType,
1053 aerv.allowable_amount
1054 ))) as allowable_amount,
1055 count(aerv.report_header_id) as number_of_violations
1056 FROM per_all_assignments_f paf,
1057 ap_expense_report_violations_v aerv
1058 WHERE paf.supervisor_id = P_EmployeeId
1059 AND paf.effective_start_date < P_EndDate
1060 AND paf.effective_end_date > P_StartDate
1061 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1062 AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
1063 AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
1064 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1065 AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
1066 AND 'MANAGER' = P_QryType
1067 AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
1068 UNION ALL
1069 /* The below two queries are to fetch the daily sum limit violations for MEALS category */
1070 SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
1071 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1072 P_BaseCurrencyCode,
1073 SYSDATE,
1074 P_ExchangeRateType,
1075 apv.exceeded_amount
1076 )) as violation_amount,
1077 decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
1078 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1079 P_BaseCurrencyCode,
1080 SYSDATE,
1081 P_ExchangeRateType,
1082 apv.allowable_amount
1083 )) as allowable_amount,
1084 1 as number_of_violations
1085 FROM ap_expense_report_headers_all aerh,
1086 ap_pol_violations_all apv
1087 WHERE aerh.report_header_id = apv.report_header_id
1088 AND aerh.org_id = apv.org_id
1089 AND apv.distribution_line_number = -1
1090 AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
1091 AND aerh.source <> 'Both Pay'
1092 AND NVL(aerh.expense_status_code,
1093 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
1094 aerh.Workflow_approved_flag,
1095 aerh.report_header_id,
1096 'N'
1097 )) IN ('MGRPAYAPPR','INVOICED','PAID')
1098 AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
1099 AND (aerh.employee_id = P_EmployeeId OR aerh.paid_on_behalf_employee_id = P_EmployeeId)
1100 AND aerh.week_end_date BETWEEN P_StartDate AND P_EndDate
1101 UNION ALL
1102 SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
1103 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1104 P_BaseCurrencyCode,
1105 SYSDATE,
1106 P_ExchangeRateType,
1107 apv.exceeded_amount
1108 )) as violation_amount,
1109 decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
1110 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1111 P_BaseCurrencyCode,
1112 SYSDATE,
1113 P_ExchangeRateType,
1114 apv.allowable_amount
1115 )) as allowable_amount,
1116 1 as number_of_violations
1117 FROM ap_expense_report_headers_all aerh,
1118 ap_pol_violations_all apv,
1119 per_all_assignments_f paf
1120 WHERE aerh.report_header_id = apv.report_header_id
1121 AND aerh.org_id = apv.org_id
1122 AND apv.distribution_line_number = -1
1123 AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
1124 AND aerh.source <> 'Both Pay'
1125 AND NVL(aerh.expense_status_code,
1126 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.source,
1127 aerh.workflow_approved_flag,
1128 aerh.report_header_id,
1129 'N'
1130 )) IN ('MGRPAYAPPR','INVOICED','PAID')
1131 AND paf.supervisor_id = P_EmployeeId
1132 AND paf.effective_start_date < P_EndDate
1133 AND paf.effective_end_date > P_StartDate
1134 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1135 AND ( aerh.employee_id = paf.person_id OR aerh.paid_on_behalf_employee_id = paf.person_id )
1136 AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
1137 AND aerh.week_end_date between P_StartDate and P_EndDate
1138 AND 'MANAGER' = P_QryType
1139 /* The below query is to bundle up RECEIPT_MISSING violations per line */
1140 UNION ALL
1141 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1142 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1143 P_BaseCurrencyCode,
1144 sysdate,
1145 P_ExchangeRateType,
1146 aerv.violation_amount
1147 ))) as violation_amount,
1148 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1149 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1150 P_BaseCurrencyCode,
1151 sysdate,
1152 P_ExchangeRateType,
1153 aerv.allowable_amount
1154 ))) as allowable_amount,
1155 count(aerv.report_header_id) as number_of_violations
1156 FROM ap_expense_report_violations_v aerv
1157 WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
1158 AND aerv.week_end_date between P_StartDate and P_EndDate
1159 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1160 AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
1161 group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
1162 UNION ALL
1163 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1164 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1165 P_BaseCurrencyCode,
1166 sysdate,
1167 P_ExchangeRateType,
1168 aerv.violation_amount
1169 ))) as violation_amount,
1170 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1171 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1172 P_BaseCurrencyCode,
1173 sysdate,
1174 P_ExchangeRateType,
1175 aerv.allowable_amount
1176 ))) as allowable_amount,
1177 count(aerv.report_header_id) as number_of_violations
1178 FROM per_all_assignments_f paf,
1179 ap_expense_report_violations_v aerv
1180 WHERE paf.supervisor_id = P_EmployeeId
1181 AND paf.effective_start_date < P_EndDate
1182 AND paf.effective_end_date > P_StartDate
1183 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1184 AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
1185 AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
1186 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1187 AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
1188 AND 'MANAGER' = P_QryType
1189 group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
1190 );
1191
1192
1193 BEGIN
1194
1195 -- Get the period date range
1196 GetPeriodDateRange(P_PeriodType, P_Date, L_StartDate, L_EndDate);
1197
1198
1199 -- Get the Base Currency Code
1200 GetBaseCurrencyInfo(L_BaseCurrencyCode, L_ExchangeRateType);
1201
1202 -- If the base currency is not same as user preference currency the
1203 -- set the conversion flag to true
1204 IF ( L_BaseCurrencyCode <> NVL(P_UserCurrencyCode,L_BaseCurrencyCode) ) THEN
1205 L_Convert := TRUE;
1206 END IF;
1207
1208 -- Fetch data for all expense categories
1209 FOR expcatrec in C_ExpenseCategories ( P_ExpenseCategory )
1210 LOOP
1211 -- Increment Table Index
1212 L_TabIndex := L_TabIndex + 1;
1213
1214 L_ExpenseCategory := expcatrec.lookup_code;
1215 L_ByExpCatTab( L_TabIndex ).expense_category := expcatrec.lookup_code;
1216 L_ByExpCatTab( L_TabIndex ).expense_category_desc := expcatrec.displayed_field;
1217
1218 -- Initialize all amounts fields as 0
1219 L_ByExpCatTab( L_TabIndex ).total_expenses := 0;
1220 L_ByExpCatTab( L_TabIndex ).violation_line_amount := 0;
1221 L_ByExpCatTab( L_TabIndex ).violation_amount := 0;
1222 L_ByExpCatTab( L_TabIndex ).allowable_amount := 0;
1223 L_ByExpCatTab( L_TabIndex ).percent_violation := 0;
1224 L_ByExpCatTab( L_TabIndex ).percent_allowable := 0;
1225 L_ByExpCatTab( L_TabIndex ).number_of_violations := 0;
1226
1227 L_ExpenseAmount := 0;
1228 L_PrevReportHeaderId := 0;
1229 L_PrevDistributionLineNumber := 0;
1230 L_ReportHeaderId := 0;
1231 L_DistributionLineNumber := 0;
1232
1233 -- Fetch expenses for the category
1234 FOR exprec in C_ExpenseAmountForCategory(P_EmployeeId,
1235 L_StartDate,
1236 L_EndDate,
1237 L_ExpenseCategory,
1238 P_QryType,
1239 L_BaseCurrencyCode,
1240 L_ExchangeRateType
1241 )
1242 LOOP
1243 L_ExpenseAmount := L_ExpenseAmount + NVL(exprec.expense_amount,0);
1244 END LOOP;
1245
1246
1247 -- Fetch violations for the category
1248 L_ViolationLineAmount := 0;
1249 L_ViolationAmount := 0;
1250 L_AllowableAmount := 0;
1251 L_NumberOfViolations := 0;
1252 L_PrevReportHeaderId := 0;
1253 L_PrevDistributionLineNumber := 0;
1254 L_ReportHeaderId := 0;
1255 L_DistributionLineNumber := 0;
1256
1257 -- Get the total for the lines which have violations
1258 FOR violline in C_ViolationLinesForCategory ( P_EmployeeId,
1259 L_ExpenseCategory,
1260 P_ViolationType,
1261 L_StartDate,
1262 L_EndDate,
1263 L_BaseCurrencyCode,
1264 L_ExchangeRateType
1265 )
1266 LOOP
1267 L_ViolationLineAmount := L_ViolationLineAmount + NVL(violline.violation_line_amount,0);
1268 END LOOP;
1269
1270 -- Get the total of violations and allowable amounts
1271 FOR violrec in C_ViolationsForCategory ( P_EmployeeId,
1272 L_ExpenseCategory,
1273 P_ViolationType,
1274 L_StartDate,
1275 L_EndDate,
1276 L_BaseCurrencyCode,
1277 L_ExchangeRateType
1278 )
1279 LOOP
1280
1281 L_ViolationAmount := L_ViolationAmount + NVL(violrec.violation_amount,0);
1282 L_AllowableAmount := L_AllowableAmount + NVL(violrec.allowable_amount,0);
1283
1284 -- Get number of violations
1285 L_NumberOfViolations := L_NumberOfViolations + violrec.number_of_violations;
1286
1287 END LOOP;
1288
1289 -- Calculate % of violation
1290 -- Bug 2925136: Round the value for percentage calculation to 2 decimal places
1291 IF ( L_ExpenseAmount > 0 ) THEN
1292 L_ByExpCatTab( L_TabIndex ).percent_violation := round((L_ViolationAmount * 100) /
1293 L_ExpenseAmount , 2 );
1294 END IF;
1295
1296 -- Calculate % of allowable amount
1297 -- Bug 2925136: Round the value for percentage calculation to 2 decimal places
1298 IF ( L_AllowableAmount > 0 ) THEN
1299 L_ByExpCatTab( L_TabIndex ).percent_allowable := round((L_ViolationAmount * 100) /
1300 L_AllowableAmount , 2 );
1301 END IF;
1302
1303 -- Get number of violations
1304 L_ByExpCatTab( L_TabIndex ).number_of_violations := L_NumberOfViolations;
1305
1306 -- If amount needs to be converted to user preference currency
1307 -- convert the amounts
1308 IF ( L_Convert ) THEN
1309 L_ByExpCatTab( L_TabIndex ).total_expenses := gl_currency_api.CONVERT_AMOUNT_SQL(
1310 L_BaseCurrencyCode,
1311 P_UserCurrencyCode,
1312 sysdate,
1313 L_ExchangeRateType,
1314 L_ExpenseAmount
1315 );
1316 L_ByExpCatTab( L_TabIndex ).violation_line_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
1317 L_BaseCurrencyCode,
1318 P_UserCurrencyCode,
1319 sysdate,
1320 L_ExchangeRateType,
1321 L_ViolationLineAmount
1322 );
1323 L_ByExpCatTab( L_TabIndex ).violation_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
1324 L_BaseCurrencyCode,
1325 P_UserCurrencyCode,
1326 sysdate,
1327 L_ExchangeRateType,
1328 L_ViolationAmount
1329 );
1330 L_ByExpCatTab( L_TabIndex ).allowable_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
1331 L_BaseCurrencyCode,
1332 P_UserCurrencyCode,
1333 sysdate,
1334 L_ExchangeRateType,
1335 L_AllowableAmount
1336 );
1337 ELSE
1338 L_ByExpCatTab( L_TabIndex ).total_expenses := round(L_ExpenseAmount, 2);
1339 L_ByExpCatTab( L_TabIndex ).violation_line_amount := round(L_ViolationLineAmount, 2);
1340 L_ByExpCatTab( L_TabIndex ).violation_amount := round(L_ViolationAmount, 2);
1341 L_ByExpCatTab( L_TabIndex ).allowable_amount := round(L_AllowableAmount, 2);
1342 END IF;
1343
1344 END LOOP;
1345
1346 -- Clear previous query results
1347 DELETE FROM AP_WEB_MANAGEMENT_REPORTS_GT;
1348
1349 FOR i in 1..L_TabIndex
1350 LOOP
1351 -- Insert search results into global temporary table
1352 INSERT INTO AP_WEB_MANAGEMENT_REPORTS_GT (
1353 EMPLOYEE_ID,
1354 TOTAL_EXPENSES,
1355 VIOLATION_LINE_AMOUNT,
1356 VIOLATION_AMOUNT,
1357 ALLOWABLE_AMOUNT,
1358 PERCENT_VIOLATION,
1359 PERCENT_ALLOWABLE,
1360 NUMBER_OF_VIOLATIONS,
1361 EXPENSE_CATEGORY,
1362 EXPENSE_CATEGORY_DESC,
1363 VIOLATION_TYPE,
1364 PERIOD_TYPE,
1365 PERIOD_DATE,
1366 ROLLUP_TYPE,
1367 REPORTING_CURRENCY_CODE
1368 )
1369 VALUES (
1370 P_EmployeeId,
1371 L_ByExpCatTab( i ).total_expenses,
1372 L_ByExpCatTab( i ).violation_line_amount,
1373 L_ByExpCatTab( i ).violation_amount,
1374 L_ByExpCatTab( i ).allowable_amount,
1375 L_ByExpCatTab( i ).percent_violation,
1376 L_ByExpCatTab( i ).percent_allowable,
1377 L_ByExpCatTab( i ).number_of_violations,
1378 L_ByExpCatTab( i ).expense_category,
1379 L_ByExpCatTab( i ).expense_category_desc,
1380 P_ViolationType,
1381 P_PeriodType,
1382 P_Date,
1383 P_QryType,
1384 NVL(P_UserCurrencyCode, L_BaseCurrencyCode)
1385 );
1386
1387 END LOOP;
1388
1389 -- Commit all the inserts
1390 COMMIT;
1391
1392 EXCEPTION
1393 WHEN OTHERS THEN
1394 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_MANAGEMENT_REPORTING_PKG.ExpenseCategorySearch',
1395 SQLERRM);
1396 APP_EXCEPTION.RAISE_EXCEPTION;
1397 -- dbms_output.put_line('EXCEPTION: '||SQLERRM) ;
1398
1399 END ExpenseCategorySearch;
1400
1401
1402 /*=========================================================================================
1403 | Procedure
1404 |
1405 | Description: This procedure will take calculate the values for the violation type
1406 | report based on the input parameters and store the value into a global
1407 | temporary table, which in turn will be used by the BC4J components for
1408 | UI display.
1409 |
1410 | MODIFICATION HISTORY
1411 | Person Date Comments
1412 | krmenon 06-09-2002 Created
1413 *=========================================================================================*/
1414 PROCEDURE ViolationTypeSearch ( P_EmployeeId IN VARCHAR2 ,
1415 P_ExpenseCategory IN VARCHAR2 ,
1416 P_ViolationType IN VARCHAR2 ,
1417 P_PeriodType IN VARCHAR2 ,
1418 P_Date IN VARCHAR2 ,
1419 P_UserCurrencyCode IN VARCHAR2 ,
1420 P_QryType IN VARCHAR2 ) IS
1421
1422 -- Declare program variables
1423 TYPE ByViolationTypeRecType IS RECORD (
1424 employee_id ap_expense_report_headers.EMPLOYEE_ID%TYPE,
1425 violation_line_amount NUMBER,
1426 violation_amount NUMBER,
1427 allowable_amount NUMBER,
1428 percent_violation NUMBER,
1429 percent_allowable NUMBER,
1430 number_of_violations NUMBER,
1431 violation_type ap_lookup_codes.LOOKUP_CODE%TYPE,
1432 violation_type_desc ap_lookup_codes.DISPLAYED_FIELD%TYPE
1433 );
1434
1435
1436 TYPE ByViolationTypeTabType IS TABLE OF ByViolationTypeRecType
1437 INDEX BY BINARY_INTEGER;
1438
1439 -- Local Variables
1440 L_ByViolTypeTab ByViolationTypeTabType;
1441
1442 L_TabIndex BINARY_INTEGER := 0;
1443 L_EmployeeId ap_expense_report_headers.EMPLOYEE_ID%TYPE;
1444 L_BaseCurrencyCode ap_system_parameters.BASE_CURRENCY_CODE%TYPE;
1445 L_ExchangeRateType ap_system_parameters.DEFAULT_EXCHANGE_RATE_TYPE%TYPE;
1446 L_ViolationType ap_lookup_codes.LOOKUP_CODE%TYPE;
1447 L_ViolationLineAmount NUMBER;
1448 L_ViolationAmount NUMBER;
1449 L_AllowableAmount NUMBER;
1450 L_Convert BOOLEAN := FALSE;
1451 L_StartDate DATE;
1452 L_EndDate DATE;
1453 L_PrevReportHeaderId ap_expense_report_headers.REPORT_HEADER_ID%TYPE := 0;
1454 L_ReportHeaderId ap_expense_report_headers.REPORT_HEADER_ID%TYPE := 0;
1455 L_PrevDistributionLineNumber ap_expense_report_lines.DISTRIBUTION_LINE_NUMBER%TYPE;
1456 L_DistributionLineNumber ap_expense_report_lines.DISTRIBUTION_LINE_NUMBER%TYPE;
1457 L_NumberOfViolations NUMBER;
1458
1459
1460 --
1461 -- Cursor to fetch all violation types
1462 --
1463 CURSOR C_ViolationTypes ( P_ViolationType VARCHAR2 )IS
1464 SELECT lookup_code,
1465 displayed_field
1466 FROM ap_lookup_codes
1467 WHERE lookup_type = 'OIE_POL_VIOLATION_TYPES'
1468 AND lookup_code = decode (P_ViolationType, 'ALL', lookup_code, P_ViolationType);
1469
1470 --
1471 -- Cursor to fetch lines with violation for an employee/direct report and expense category and violation type
1472 --
1473 CURSOR C_ViolationLinesForCategory ( P_EmployeeId VARCHAR2,
1474 P_ExpenseCategory VARCHAR2,
1475 P_ViolationType VARCHAR2,
1476 P_StartDate VARCHAR2,
1477 P_EndDate VARCHAR2,
1478 P_BaseCurrencyCode VARCHAR2,
1479 P_ExchangeRateType VARCHAR2
1480 ) IS
1481 SELECT SUM(line_amount) AS violation_line_amount
1482 FROM
1483 (
1484 SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
1485 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
1486 P_BaseCurrencyCode,
1487 sysdate,
1488 P_ExchangeRateType,
1489 aerl.amount
1490 )) as line_amount
1491 FROM ap_expense_report_lines_v aerl
1492 WHERE EXISTS ( SELECT 'X'
1493 FROM ap_pol_violations_all apv
1494 WHERE apv.report_header_id = aerl.report_header_id
1495 AND apv.distribution_line_number = aerl.distribution_line_number
1496 AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType))
1497 AND ((aerl.employee_id= P_EmployeeId) or (aerl.paid_on_behalf_employee_id = P_EmployeeId))
1498 AND aerl.week_end_date BETWEEN P_StartDate AND P_EndDate
1499 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
1500 UNION ALL
1501 SELECT decode(aerl.currency_code, P_BaseCurrencyCode, aerl.amount,
1502 gl_currency_api.CONVERT_AMOUNT_SQL( aerl.currency_code,
1503 P_BaseCurrencyCode,
1504 sysdate,
1505 P_ExchangeRateType,
1506 aerl.amount
1507 )) as line_amount
1508 FROM ap_expense_report_lines_v aerl,
1509 per_all_assignments_f paf
1510 WHERE EXISTS ( SELECT 'X'
1511 FROM ap_pol_violations_all apv
1512 WHERE apv.report_header_id = aerl.report_header_id
1513 AND apv.distribution_line_number = aerl.distribution_line_number
1514 AND apv.violation_type = decode(P_ViolationType, 'ALL', apv.violation_type, P_ViolationType) )
1515 AND paf.supervisor_id = P_EmployeeId
1516 AND paf.effective_start_date < P_EndDate
1517 AND paf.effective_end_date > P_StartDate
1518 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1519 AND ((aerl.employee_id = paf.person_id) or (aerl.paid_on_behalf_employee_id = paf.person_id))
1520 AND aerl.week_end_date BETWEEN greatest(paf.effective_start_date,P_StartDate) AND least(paf.effective_end_date,P_EndDate)
1521 AND nvl(aerl.category_code, 'ALL') = decode (P_ExpenseCategory, 'ALL', nvl(aerl.category_code, 'ALL'), P_ExpenseCategory)
1522 AND 'MANAGER' = P_QryType
1523 AND aerl.paid_on_behalf_employee_id IS NULL
1524 );
1525
1526 --
1527 -- Cursor to fetch violation amount for an employee/direct report and expense category and violation type
1528 --
1529 CURSOR C_ViolationsForCategory ( P_EmployeeId VARCHAR2,
1530 P_ExpenseCategory VARCHAR2,
1531 P_ViolationType VARCHAR2,
1532 P_StartDate VARCHAR2,
1533 P_EndDate VARCHAR2,
1534 P_BaseCurrencyCode VARCHAR2,
1535 P_ExchangeRateType VARCHAR2
1536 ) IS
1537 SELECT sum(nvl(violation_amount,0)) as violation_amount,
1538 sum(nvl(allowable_amount,0)) as allowable_amount,
1539 sum(nvl(number_of_violations,0)) as number_of_violations
1540 FROM
1541 (
1542 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1543 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1544 P_BaseCurrencyCode,
1545 sysdate,
1546 P_ExchangeRateType,
1547 aerv.violation_amount
1548 ))) as violation_amount,
1549 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1550 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1551 P_BaseCurrencyCode,
1552 sysdate,
1553 P_ExchangeRateType,
1554 aerv.allowable_amount
1555 ))) as allowable_amount,
1556 count(aerv.report_header_id) as number_of_violations
1557 FROM ap_expense_report_violations_v aerv
1558 WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
1559 AND aerv.week_end_date between P_StartDate and P_EndDate
1560 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1561 AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
1562 AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
1563 UNION ALL
1564 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1565 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1566 P_BaseCurrencyCode,
1567 sysdate,
1568 P_ExchangeRateType,
1569 aerv.violation_amount
1570 ))) as violation_amount,
1571 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1572 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1573 P_BaseCurrencyCode,
1574 sysdate,
1575 P_ExchangeRateType,
1576 aerv.allowable_amount
1577 ))) as allowable_amount,
1578 count(aerv.report_header_id) as number_of_violations
1579 FROM per_all_assignments_f paf,
1580 ap_expense_report_violations_v aerv
1581 WHERE paf.supervisor_id = P_EmployeeId
1582 AND paf.effective_start_date < P_EndDate
1583 AND paf.effective_end_date > P_StartDate
1584 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1585 AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
1586 AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
1587 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1588 AND aerv.violation_type = decode(P_ViolationType, 'ALL', aerv.violation_type, P_ViolationType)
1589 AND 'MANAGER' = P_QryType
1590 AND aerv.violation_type NOT IN ( 'RECEIPT_MISSING', 'DAILY_SUM_LIMIT')
1591 UNION ALL
1592 /* The below two queries are to fetch the daily sum limit violations for MEALS category */
1593 SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
1594 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1595 P_BaseCurrencyCode,
1596 SYSDATE,
1597 P_ExchangeRateType,
1598 apv.exceeded_amount
1599 )) as violation_amount,
1600 decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
1601 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1602 P_BaseCurrencyCode,
1603 SYSDATE,
1604 P_ExchangeRateType,
1605 apv.allowable_amount
1606 )) as allowable_amount,
1607 1 as number_of_violations
1608 FROM ap_expense_report_headers_all aerh,
1609 ap_pol_violations_all apv
1610 WHERE aerh.report_header_id = apv.report_header_id
1611 AND aerh.org_id = apv.org_id
1612 AND apv.distribution_line_number = -1
1613 AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
1614 AND aerh.source <> 'Both Pay'
1615 AND NVL(aerh.expense_status_code,
1616 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.Source,
1617 aerh.Workflow_approved_flag,
1618 aerh.report_header_id,
1619 'N'
1620 )) IN ('MGRPAYAPPR','INVOICED','PAID')
1621 AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
1622 AND (aerh.employee_id = P_EmployeeId OR aerh.paid_on_behalf_employee_id = P_EmployeeId)
1623 AND aerh.week_end_date BETWEEN P_StartDate AND P_EndDate
1624 UNION ALL
1625 SELECT decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.exceeded_amount,
1626 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1627 P_BaseCurrencyCode,
1628 SYSDATE,
1629 P_ExchangeRateType,
1630 apv.exceeded_amount
1631 )) as violation_amount,
1632 decode(aerh.default_currency_code, P_BaseCurrencyCode, apv.allowable_amount,
1633 GL_CURRENCY_API.convert_amount_sql( aerh.default_currency_code,
1634 P_BaseCurrencyCode,
1635 SYSDATE,
1636 P_ExchangeRateType,
1637 apv.allowable_amount
1638 )) as allowable_amount,
1639 1 as number_of_violations
1640 FROM ap_expense_report_headers_all aerh,
1641 ap_pol_violations_all apv,
1642 per_all_assignments_f paf
1643 WHERE aerh.report_header_id = apv.report_header_id
1644 AND aerh.org_id = apv.org_id
1645 AND apv.distribution_line_number = -1
1646 AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL)
1647 AND aerh.source <> 'Both Pay'
1648 AND NVL(aerh.expense_status_code,
1649 AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(aerh.source,
1650 aerh.workflow_approved_flag,
1651 aerh.report_header_id,
1652 'N'
1653 )) IN ('MGRPAYAPPR','INVOICED','PAID')
1654 AND paf.supervisor_id = P_EmployeeId
1655 AND paf.effective_start_date < P_EndDate
1656 AND paf.effective_end_date > P_StartDate
1657 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1658 AND ( aerh.employee_id = paf.person_id OR aerh.paid_on_behalf_employee_id = paf.person_id )
1659 AND ( (P_ViolationType = 'ALL') or (P_ViolationType = 'DAILY_SUM_LIMIT')) AND apv.violation_type = 'DAILY_SUM_LIMIT'
1660 AND aerh.week_end_date between P_StartDate and P_EndDate
1661 AND 'MANAGER' = P_QryType
1662 /* The below query is to bundle up RECEIPT_MISSING violations per line */
1663 UNION ALL
1664 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1665 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1666 P_BaseCurrencyCode,
1667 sysdate,
1668 P_ExchangeRateType,
1669 aerv.violation_amount
1670 ))) as violation_amount,
1671 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1672 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1673 P_BaseCurrencyCode,
1674 sysdate,
1675 P_ExchangeRateType,
1676 aerv.allowable_amount
1677 ))) as allowable_amount,
1678 count(aerv.report_header_id) as number_of_violations
1679 FROM ap_expense_report_violations_v aerv
1680 WHERE ((aerv.employee_id = P_EmployeeId) or (aerv.paid_on_behalf_employee_id = P_EmployeeId))
1681 AND aerv.week_end_date between P_StartDate and P_EndDate
1682 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1683 AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
1684 group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
1685 UNION ALL
1686 SELECT sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.violation_amount,
1687 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1688 P_BaseCurrencyCode,
1689 sysdate,
1690 P_ExchangeRateType,
1691 aerv.violation_amount
1692 ))) as violation_amount,
1693 sum(decode(aerv.currency_code, P_BaseCurrencyCode, aerv.allowable_amount,
1694 gl_currency_api.CONVERT_AMOUNT_SQL( aerv.currency_code,
1695 P_BaseCurrencyCode,
1696 sysdate,
1697 P_ExchangeRateType,
1698 aerv.allowable_amount
1699 ))) as allowable_amount,
1700 count(aerv.report_header_id) as number_of_violations
1701 FROM per_all_assignments_f paf,
1702 ap_expense_report_violations_v aerv
1703 WHERE paf.supervisor_id = P_EmployeeId
1704 AND paf.effective_start_date < P_EndDate
1705 AND paf.effective_end_date > P_StartDate
1706 AND (paf.assignment_type = 'E' OR paf.assignment_type = 'C')
1707 AND ((aerv.employee_id = paf.person_id) or (aerv.paid_on_behalf_employee_id = paf.person_id))
1708 AND aerv.week_end_date BETWEEN greatest(paf.effective_start_date, P_StartDate) AND least(paf.effective_end_date, P_EndDate)
1709 AND nvl(aerv.category_code, 'ALL') = decode(P_ExpenseCategory, 'ALL', nvl(aerv.category_code, 'ALL'), P_ExpenseCategory)
1710 AND ((P_ViolationType = 'ALL') or (P_ViolationType = 'RECEIPT_MISSING')) AND aerv.violation_type = 'RECEIPT_MISSING'
1711 AND 'MANAGER' = P_QryType
1712 group by nvl(aerv.itemization_parent_id, aerv.report_line_id)
1713 );
1714
1715 BEGIN
1716
1717 -- Get the period date range
1718 GetPeriodDateRange(P_PeriodType, P_Date, L_StartDate, L_EndDate);
1719
1720
1721 -- Get the Base Currency Code
1722 GetBaseCurrencyInfo(L_BaseCurrencyCode, L_ExchangeRateType);
1723
1724 -- If the base currency is not same as user preference currency the
1725 -- set the conversion flag to true
1726 IF ( L_BaseCurrencyCode <> NVL(P_UserCurrencyCode, L_BaseCurrencyCode) ) THEN
1727 L_Convert := TRUE;
1728 END IF;
1729
1730
1731 -- Fetch data for all expense categories
1732 FOR violtyperec in C_ViolationTypes ( P_ViolationType )
1733 LOOP
1734 -- Increment Table Index
1735 L_TabIndex := L_TabIndex + 1;
1736
1737 L_ViolationType := violtyperec.lookup_code;
1738
1739 L_ByViolTypeTab( L_TabIndex ).violation_type := violtyperec.lookup_code;
1740 L_ByViolTypeTab( L_TabIndex ).violation_type_desc := violtyperec.displayed_field;
1741
1742 -- Initialize all amounts fields as 0
1743 L_ByViolTypeTab( L_TabIndex ).violation_line_amount := 0;
1744 L_ByViolTypeTab( L_TabIndex ).violation_amount := 0;
1745 L_ByViolTypeTab( L_TabIndex ).allowable_amount := 0;
1746 L_ByViolTypeTab( L_TabIndex ).percent_violation := 0;
1747 L_ByViolTypeTab( L_TabIndex ).percent_allowable := 0;
1748 L_ByViolTypeTab( L_TabIndex ).number_of_violations := 0;
1749
1750 -- Fetch violations for the category
1751 L_ViolationLineAmount := 0;
1752 L_ViolationAmount := 0;
1753 L_AllowableAmount := 0;
1754 L_NumberOfViolations := 0;
1755 L_PrevReportHeaderId := 0;
1756 L_PrevDistributionLineNumber := 0;
1757 L_ReportHeaderId := 0;
1758 L_DistributionLineNumber := 0;
1759
1760 -- Get the total for the lines which have violations
1761 FOR violline in C_ViolationLinesForCategory ( P_EmployeeId,
1762 P_ExpenseCategory,
1763 L_ViolationType,
1764 L_StartDate,
1765 L_EndDate,
1766 L_BaseCurrencyCode,
1767 L_ExchangeRateType
1768 )
1769 LOOP
1770 L_ViolationLineAmount := L_ViolationLineAmount + NVL(violline.violation_line_amount,0);
1771 END LOOP;
1772
1773 -- Get the total of violations and allowable amounts
1774 FOR violrec in C_ViolationsForCategory ( P_EmployeeId,
1775 P_ExpenseCategory,
1776 L_ViolationType,
1777 L_StartDate,
1778 L_EndDate,
1779 L_BaseCurrencyCode,
1780 L_ExchangeRateType
1781 )
1782 LOOP
1783
1784 L_ViolationAmount := L_ViolationAmount + NVL(violrec.violation_amount,0);
1785 L_AllowableAmount := L_AllowableAmount + NVL(violrec.allowable_amount,0);
1786
1787 -- Get number of violations
1788 L_NumberOfViolations := L_NumberOfViolations + violrec.number_of_violations;
1789
1790 END LOOP;
1791
1792
1793 -- Calculate % of violation
1794 -- Bug 2925136: Round the value for percentage calculation to 2 decimal places
1795 IF ( L_ViolationLineAmount > 0 ) THEN
1796 L_ByViolTypeTab( L_TabIndex ).percent_violation := round((L_ViolationAmount * 100) /
1797 L_ViolationLineAmount , 2 );
1798 END IF;
1799
1800 -- Calculate % of allowable amount
1801 -- Bug 2925136: Round the value for percentage calculation to 2 decimal places
1802 IF ( L_AllowableAmount > 0 ) THEN
1803 L_ByViolTypeTab( L_TabIndex ).percent_allowable := round((L_ViolationAmount * 100) /
1804 L_AllowableAmount , 2 );
1805 END IF;
1806
1807 -- Get number of violations
1808 L_ByViolTypeTab( L_TabIndex ).number_of_violations := L_NumberOfViolations;
1809
1810 -- If amount needs to be converted to user preference currency
1811 -- convert the amounts
1812 IF ( L_Convert ) THEN
1813 L_ByViolTypeTab( L_TabIndex ).violation_line_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
1814 L_BaseCurrencyCode,
1815 P_UserCurrencyCode,
1816 sysdate,
1817 L_ExchangeRateType,
1818 L_ViolationLineAmount
1819 );
1820 L_ByViolTypeTab( L_TabIndex ).violation_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
1821 L_BaseCurrencyCode,
1822 P_UserCurrencyCode,
1823 sysdate,
1824 L_ExchangeRateType,
1825 L_ViolationAmount
1826 );
1827 L_ByViolTypeTab( L_TabIndex ).allowable_amount := gl_currency_api.CONVERT_AMOUNT_SQL(
1828 L_BaseCurrencyCode,
1829 P_UserCurrencyCode,
1830 sysdate,
1831 L_ExchangeRateType,
1832 L_AllowableAmount
1833 );
1834 ELSE
1835 L_ByViolTypeTab( L_TabIndex ).violation_line_amount := round(L_ViolationLineAmount, 2);
1836 L_ByViolTypeTab( L_TabIndex ).violation_amount := round(L_ViolationAmount, 2);
1837 L_ByViolTypeTab( L_TabIndex ).allowable_amount := round(L_AllowableAmount, 2);
1838 END IF;
1839
1840 END LOOP;
1841
1842 -- Clear previous query results
1843 DELETE FROM AP_WEB_MANAGEMENT_REPORTS_GT;
1844
1845 FOR i in 1..L_TabIndex
1846 LOOP
1847 -- Insert search results into global temporary table
1848 INSERT INTO AP_WEB_MANAGEMENT_REPORTS_GT (
1849 EMPLOYEE_ID,
1850 VIOLATION_LINE_AMOUNT,
1851 VIOLATION_AMOUNT,
1852 ALLOWABLE_AMOUNT,
1853 PERCENT_VIOLATION,
1854 PERCENT_ALLOWABLE,
1855 NUMBER_OF_VIOLATIONS,
1856 EXPENSE_CATEGORY,
1857 VIOLATION_TYPE,
1858 VIOLATION_TYPE_DESC,
1859 PERIOD_TYPE,
1860 PERIOD_DATE,
1861 ROLLUP_TYPE,
1862 REPORTING_CURRENCY_CODE
1863 )
1864 VALUES (
1865 P_EmployeeId,
1866 L_ByViolTypeTab( i ).violation_line_amount,
1867 L_ByViolTypeTab( i ).violation_amount,
1868 L_ByViolTypeTab( i ).allowable_amount,
1869 L_ByViolTypeTab( i ).percent_violation,
1870 L_ByViolTypeTab( i ).percent_allowable,
1871 L_ByViolTypeTab( i ).number_of_violations,
1872 P_ExpenseCategory,
1873 L_ByViolTypeTab( i ).violation_type,
1874 L_ByViolTypeTab( i ).violation_type_desc,
1875 P_PeriodType,
1876 P_Date,
1877 P_QryType,
1878 NVL(P_UserCurrencyCode, L_BaseCurrencyCode)
1879 );
1880
1881 END LOOP;
1882
1883 -- Commit all the inserts
1884 COMMIT;
1885
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888 AP_WEB_DB_UTIL_PKG.RaiseException ('AP_WEB_MANAGEMENT_REPORTING_PKG.ViolationTypeSearch',
1889 SQLERRM);
1890 APP_EXCEPTION.RAISE_EXCEPTION;
1891 -- dbms_output.put_line('EXCEPTION: '||SQLERRM) ;
1892
1893 END ViolationTypeSearch;
1894
1895 END AP_WEB_MANAGEMENT_REPORTS_PKG;
[Home] [Help]