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