[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_DB_EXPLINE_PKG
Source
1 PACKAGE BODY AP_WEB_DB_EXPLINE_PKG AS
2 /* $Header: apwdbelb.pls 120.113.12010000.5 2008/12/02 07:01:14 meesubra ship $ */
3
4
5 --------------------------------------------------------------------------------
6 FUNCTION GetTrxIdsAndAmtsCursor(p_reportId IN expLines_headerID,
7 p_cc_cursor OUT NOCOPY CCTrxnCursor)
8 RETURN BOOLEAN IS
9 --------------------------------------------------------------------------------
10 l_debugInfo varchar2(240);
11 BEGIN
12 l_debugInfo := 'Get ids and amts from expense report lines';
13
14 OPEN p_cc_cursor FOR
15 SELECT credit_card_trx_id, amount
16 FROM AP_EXPENSE_REPORT_LINES
17 WHERE (REPORT_HEADER_ID = p_reportId)
18 AND (credit_card_trx_id is not null);
19
20 return TRUE;
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 return FALSE;
24 WHEN OTHERS THEN
25 AP_WEB_DB_UTIL_PKG.RaiseException('GetTrxIdAndAmt',
26 l_debugInfo);
27 APP_EXCEPTION.RAISE_EXCEPTION;
28 return FALSE;
29 END GetTrxIdsAndAmtsCursor;
30
31 --------------------------------------------------------------------------------
32 FUNCTION GetReportLineCursor(p_reportId IN expLines_headerID,
33 p_line_cursor OUT NOCOPY ReportLinesCursor)
34 RETURN BOOLEAN IS
35 --------------------------------------------------------------------------------
36 l_date_format VARCHAR2(20);
37 BEGIN
38
39 l_date_format := ICX_SEC.getID( ICX_SEC.PV_DATE_FORMAT );
40 OPEN p_line_cursor FOR
41 SELECT TO_CHAR(WEB_PARAMETER_ID), -- xtype
42 TO_CHAR(AMOUNT), -- amount
43 RECEIPT_MISSING_FLAG, -- receiptmissing
44 JUSTIFICATION, -- justification
45 EXPENSE_GROUP, -- group
46 -- chiho:1283146:
47 TO_CHAR(START_EXPENSE_DATE, l_date_format), -- xdate1
48 TO_CHAR(END_EXPENSE_DATE, l_date_format), -- xdate2
49 RECEIPT_CURRENCY_CODE, -- currency
50 TO_CHAR(RECEIPT_CONVERSION_RATE), -- rate array
51 TO_CHAR(DAILY_AMOUNT), -- damount
52 TO_CHAR(RECEIPT_CURRENCY_AMOUNT), -- recamount
53 AERL.MERCHANT_NAME,
54 AERL.MERCHANT_DOCUMENT_NUMBER,
55 AERL.MERCHANT_REFERENCE,
56 AERL.MERCHANT_TAX_REG_NUMBER,
57 AERL.MERCHANT_TAXPAYER_ID,
58 AERL.COUNTRY_OF_SUPPLY,
59 AERL.TAX_CODE_ID,
60 AERL.TAX_CODE_OVERRIDE_FLAG,
61 AERL.AMOUNT_INCLUDES_TAX_FLAG,
62 AERL.CREDIT_CARD_TRX_ID,
63 AERL.ATTRIBUTE1,
64 AERL.ATTRIBUTE2,
65 AERL.ATTRIBUTE3,
66 AERL.ATTRIBUTE4,
67 AERL.ATTRIBUTE5,
68 AERL.ATTRIBUTE6,
69 AERL.ATTRIBUTE7,
70 AERL.ATTRIBUTE8,
71 AERL.ATTRIBUTE9,
72 AERL.ATTRIBUTE10,
73 AERL.ATTRIBUTE11,
74 AERL.ATTRIBUTE12,
75 AERL.ATTRIBUTE13,
76 AERL.ATTRIBUTE14,
77 AERL.ATTRIBUTE15,
78 AMOUNT_INCLUDES_TAX_FLAG,
79 VAT_CODE,
80 TO_CHAR(AERL.PROJECT_ID),
81 AERL.PROJECT_NUMBER,
82 TO_CHAR(AERL.TASK_ID),
83 AERL.TASK_NUMBER,
84 EXPENDITURE_TYPE,
85 AERL.DISTRIBUTION_LINE_NUMBER
86 FROM AP_EXPENSE_REPORT_LINES AERL
87 WHERE REPORT_HEADER_ID = p_reportId
88 ORDER BY AERL.DISTRIBUTION_LINE_NUMBER;
89 return TRUE;
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 return FALSE;
93 WHEN OTHERS THEN
94 AP_WEB_DB_UTIL_PKG.RaiseException('GetReportLineCursor');
95 APP_EXCEPTION.RAISE_EXCEPTION;
96 return FALSE;
97 END GetReportLineCursor;
98
99 PROCEDURE FetchReportLineCursor(p_ReportLinesCursor IN OUT NOCOPY ReportLinesCursor,
100 p_expLines IN OUT NOCOPY AP_WEB_DFLEX_PKG.ExpReportLines_A,
101 Attribute_Array IN OUT NOCOPY AP_WEB_PARENT_PKG.BigString_Array,
102 p_ReceiptIndex IN OUT NOCOPY NUMBER,
103 p_Rate OUT NOCOPY expLines_receiptConvRate,
104 p_AmtInclTax OUT NOCOPY expLines_amtInclTaxFlag,
105 p_TaxName OUT NOCOPY expLines_vatCode,
106 p_ExpenditureType OUT NOCOPY expLines_expendType)
107 IS
108 l_distribution_line_number number;
109 BEGIN
110 FETCH p_ReportLinesCursor INTO
111 p_expLines(p_ReceiptIndex).parameter_id,
112 p_expLines(p_ReceiptIndex).amount,
113 p_expLines(p_ReceiptIndex).receipt_missing_flag,
114 p_expLines(p_ReceiptIndex).justification,
115 p_expLines(p_ReceiptIndex).group_value,
116 p_expLines(p_ReceiptIndex).start_date,
117 p_expLines(p_ReceiptIndex).end_date,
118 p_expLines(p_ReceiptIndex).currency_code,
119 p_Rate,
120 p_expLines(p_ReceiptIndex).daily_amount,
121 p_expLines(p_ReceiptIndex).receipt_amount,
122 p_expLines(p_ReceiptIndex).merchant,
123 p_expLines(p_ReceiptIndex).merchantDoc,
124 p_expLines(p_ReceiptIndex).taxReference,
125 p_expLines(p_ReceiptIndex).taxRegNumber,
126 p_expLines(p_ReceiptIndex).taxPayerId,
127 p_expLines(p_ReceiptIndex).supplyCountry,
128 p_expLines(p_ReceiptIndex).taxId,
129 p_expLines(p_ReceiptIndex).taxOverrideFlag,
130 p_expLines(p_ReceiptIndex).amount_includes_tax,
131 p_expLines(p_ReceiptIndex).itemizeId,
132 p_expLines(p_ReceiptIndex).cCardTrxnId,
133 Attribute_Array(1),
134 Attribute_Array(2),
135 Attribute_Array(3),
136 Attribute_Array(4),
137 Attribute_Array(5),
138 Attribute_Array(6),
139 Attribute_Array(7),
140 Attribute_Array(8),
141 Attribute_Array(9),
142 Attribute_Array(10),
143 Attribute_Array(11),
144 Attribute_Array(12),
145 Attribute_Array(13),
146 Attribute_Array(14),
147 Attribute_Array(15),
148 p_AmtInclTax,
149 p_TaxName,
150 p_expLines(p_ReceiptIndex).project_id,
151 p_expLines(p_ReceiptIndex).project_number,
152 p_expLines(p_ReceiptIndex).task_id,
153 p_expLines(p_ReceiptIndex).task_number,
154 p_ExpenditureType,
155 l_distribution_line_number;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
160 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
161 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE','FetchReportLineCursor');
162 APP_EXCEPTION.RAISE_EXCEPTION;
163
164 END FetchReportLineCursor;
165
166
167 --------------------------------------------------------------------------------
168 FUNCTION GetDisplayXpenseLinesCursor(
169 p_report_header_id IN expLines_headerID,
170 p_xpense_lines_cursor OUT NOCOPY DisplayXpenseLinesCursor)
171 RETURN BOOLEAN IS
172 --------------------------------------------------------------------------------
173 BEGIN
174 -- get all expense lines
175 OPEN p_xpense_lines_cursor FOR
176 SELECT XL.receipt_missing_flag,
177 -- Called from GenerateExpClobLines for rendering old notifications for
178 -- expenses created prior to 11.5.10, will not be used for reports submitted
179 -- in R12 hence decision was made not to tune these old queries.
180 to_char(XL.start_expense_date),
181 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
182 XL.start_expense_date)+1),4),
183 LPAD(to_char(XL.daily_amount),9),
184 XL.receipt_currency_code,
185 LPAD(to_char(XL.receipt_conversion_rate),5),
186 LPAD(to_char(XL.receipt_currency_amount),10),
187 XL.amount,
188 nvl(XL.justification, '&' || 'nbsp'),
189 nvl(XP.web_friendly_prompt, XP.prompt),
190 PAP.segment1, --PAP.project_number,
191 nvl(PAT.task_number, '&' || 'nbsp'),
192 XL.credit_card_trx_id,
193 XL.distribution_line_number dist_num,
194 nvl(GMS.award_number, '&' || 'nbsp'),
195 av.displayed_field,
196 XL.merchant_name
197 FROM ap_expense_report_params XP,
198 ap_expense_report_lines XL,
199 ap_lookup_codes LC,
200 PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
201 PA_TASKS PAT, -- AP_WEB_PA_PROJECTS_TASKS_V PAT, -- bug 1652647
202 GMS_AWARDS GMS,
203 AP_POL_VIOLATIONS_V AV
204 WHERE XL.report_header_id = p_report_header_id
205 AND XL.project_id is not null
206 AND XL.task_id is not null
207 AND XL.web_parameter_id = XP.parameter_id
208 AND XL.line_type_lookup_code = LC.lookup_code
209 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
210 AND XL.project_id = PAP.project_id
211 AND XL.project_id = PAT.project_id
212 AND XL.task_id = PAT.task_id
213 AND XL.award_id = GMS.award_id(+)
214 AND XL.report_header_id = AV.report_header_id(+)
215 AND XL.distribution_line_number = AV.distribution_line_number(+)
216 AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
217 UNION ALL
218 SELECT XL.receipt_missing_flag,
219 to_char(XL.start_expense_date),
220 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
221 LPAD(to_char(XL.daily_amount),9),
222 XL.receipt_currency_code,
223 LPAD(to_char(XL.receipt_conversion_rate),5),
224 LPAD(to_char(XL.receipt_currency_amount),10),
225 XL.amount,
226 XL.justification,
227 nvl(XP.web_friendly_prompt, XP.prompt),
228 NULL,
229 NULL,
230 XL.credit_card_trx_id,
231 XL.distribution_line_number dist_num,
232 NULL,
233 av.displayed_field,
234 XL.merchant_name
235 FROM ap_expense_report_params XP,
236 ap_expense_report_lines XL,
237 ap_lookup_codes LC,
238 AP_POL_VIOLATIONS_V AV
239 WHERE XL.report_header_id = p_report_header_id
240 AND XL.web_parameter_id = XP.parameter_id
241 AND XL.line_type_lookup_code = LC.lookup_code
242 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
243 AND XL.project_id is null
244 AND XL.task_id is null
245 AND XL.award_id is null
246 AND XL.report_header_id = AV.report_header_id(+)
247 AND XL.distribution_line_number = AV.distribution_line_number(+)
248 AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
249 ORDER BY dist_num;
250
251 return TRUE;
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 return FALSE;
255 WHEN OTHERS THEN
256 AP_WEB_DB_UTIL_PKG.RaiseException('GetDisplayXpenseLinesCursor');
257 APP_EXCEPTION.RAISE_EXCEPTION;
258 return FALSE;
259 END GetDisplayXpenseLinesCursor;
260
261 --------------------------------------------------------------------------------
262 FUNCTION GetDisplayXpenseLinesCursor(
263 p_report_header_id IN expLines_headerID,
264 p_is_cc_lines IN BOOLEAN,
265 p_xpense_lines_cursor OUT NOCOPY DisplayXpenseLinesCursor)
266 RETURN BOOLEAN IS
267 --------------------------------------------------------------------------------
268 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
269 BEGIN
270 if p_is_cc_lines then
271 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
272 return FALSE;
273 END IF; /* GetPersonalParamID */
274
275 -- get credit card lines only
276 OPEN p_xpense_lines_cursor FOR
277 SELECT XL.receipt_missing_flag,
278 -- Called from GenerateExpClobLines for rendering old notifications for
279 -- expenses created prior to 11.5.10, will not be used for reports submitted
280 -- in R12 hence decision was made not to tune these old queries.
281 to_char(XL.start_expense_date),
282 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
283 XL.start_expense_date)+1),4),
284 LPAD(to_char(XL.daily_amount),9),
285 XL.receipt_currency_code,
286 LPAD(to_char(XL.receipt_conversion_rate),5),
287 LPAD(to_char(XL.receipt_currency_amount),10),
288 XL.amount,
289 nvl(XL.justification, '&' || 'nbsp'),
290 nvl(XP.web_friendly_prompt, XP.prompt),
291 PAP.segment1, --PAP.project_number,
292 nvl(PAT.task_number, '&' || 'nbsp'),
293 XL.credit_card_trx_id,
294 XL.distribution_line_number,
295 nvl(GMS.award_number, '&' || 'nbsp'),
296 av.displayed_field,
297 XL.merchant_name,
298 nvl(XL.flex_concatenated, XH.flex_concatenated),
299 XL.mileage_rate_adjusted_flag
300 FROM ap_expense_report_params XP,
301 ap_expense_report_lines XL,
302 ap_expense_report_headers XH,
303 ap_credit_card_trxns CC,
304 ap_lookup_codes LC,
305 PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
306 PA_TASKS PAT, -- AP_WEB_PA_PROJECTS_TASKS_V PAT -- bug 1652647
307 GMS_AWARDS GMS,
308 AP_POL_VIOLATIONS_V AV
309 WHERE XL.report_header_id = p_report_header_id
310 AND XL.report_header_id = XH.report_header_id
311 AND XL.project_id is not null
312 AND XL.task_id is not null
313 AND XL.web_parameter_id = XP.parameter_id
314 AND XL.line_type_lookup_code = LC.lookup_code
315 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
316 AND XL.project_id = PAP.project_id
317 AND XL.project_id = PAT.project_id
318 AND XL.task_id = PAT.task_id
319 AND XL.credit_card_trx_id is not null
320 AND CC.trx_id = XL.credit_card_trx_id
321 AND (CC.category is null OR CC.category NOT IN ('PERSONAL','DEACTIVATED'))--not a personal expense
322 AND XL.web_parameter_id <> l_personalParameterId -- not a personal expense
323 AND XL.award_id = GMS.award_id(+)
324 AND XL.report_header_id = AV.report_header_id(+)
325 AND XL.distribution_line_number = AV.distribution_line_number(+)
326 AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
327 UNION ALL
328 SELECT XL.receipt_missing_flag,
329 to_char(XL.start_expense_date),
330 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
331 LPAD(to_char(XL.daily_amount),9),
332 XL.receipt_currency_code,
333 LPAD(to_char(XL.receipt_conversion_rate),5),
334 LPAD(to_char(XL.receipt_currency_amount),10),
335 XL.amount,
336 XL.justification,
337 nvl(XP.web_friendly_prompt, XP.prompt),
338 NULL,
339 NULL,
340 XL.credit_card_trx_id,
341 XL.distribution_line_number,
342 NULL,
343 av.displayed_field,
344 XL.merchant_name,
345 nvl(XL.flex_concatenated, XH.flex_concatenated),
346 XL.mileage_rate_adjusted_flag
347 FROM ap_expense_report_params XP,
348 ap_expense_report_lines XL,
349 ap_expense_report_headers XH,
350 ap_credit_card_trxns CC,
351 ap_lookup_codes LC,
352 AP_POL_VIOLATIONS_V AV
353 WHERE XL.report_header_id = p_report_header_id
354 AND XL.report_header_id = XH.report_header_id
355 AND XL.web_parameter_id = XP.parameter_id
356 AND XL.line_type_lookup_code = LC.lookup_code
357 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
358 AND XL.project_id is null
359 AND XL.task_id is null
360 AND XL.credit_card_trx_id is not null
361 AND CC.trx_id = XL.credit_card_trx_id
362 AND (CC.category is null OR CC.category NOT IN ('PERSONAL','DEACTIVATED'))--not a personal expense
363 AND XL.web_parameter_id <> l_personalParameterId -- not a personal expense
364 AND XL.award_id is NULL
365 AND XL.report_header_id = AV.report_header_id(+)
366 AND XL.distribution_line_number = AV.distribution_line_number(+)
367 AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
368 ORDER BY 14;
369 else
370 -- get cash lines only
371 OPEN p_xpense_lines_cursor FOR
372 SELECT XL.receipt_missing_flag,
373 to_char(XL.start_expense_date),
374 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
375 XL.start_expense_date)+1),4),
376 LPAD(to_char(XL.daily_amount),9),
377 XL.receipt_currency_code,
378 LPAD(to_char(XL.receipt_conversion_rate),5),
379 LPAD(to_char(XL.receipt_currency_amount),10),
380 XL.amount,
381 nvl(XL.justification, '&' || 'nbsp'),
382 nvl(XP.web_friendly_prompt, XP.prompt),
383 PAP.segment1, --PAP.project_number,
384 nvl(PAT.task_number, '&' || 'nbsp'),
385 XL.credit_card_trx_id,
386 XL.distribution_line_number,
387 nvl(GMS.award_number, '&' || 'nbsp'),
388 av.displayed_field,
389 XL.merchant_name,
390 nvl(XL.flex_concatenated, XH.flex_concatenated),
391 XL.mileage_rate_adjusted_flag
392 FROM ap_expense_report_params XP,
393 ap_expense_report_lines XL,
394 ap_expense_report_headers XH,
395 ap_lookup_codes LC,
396 PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
397 PA_TASKS PAT, -- AP_WEB_PA_PROJECTS_TASKS_V PAT -- bug 1652647
398 GMS_AWARDS GMS,
399 AP_POL_VIOLATIONS_V AV
400 WHERE XL.report_header_id = p_report_header_id
401 AND XL.report_header_id = XH.report_header_id
402 AND XL.project_id is not null
403 AND XL.task_id is not null
404 AND XL.web_parameter_id = XP.parameter_id
405 AND XL.line_type_lookup_code = LC.lookup_code
406 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
407 AND XL.project_id = PAP.project_id
408 AND XL.project_id = PAT.project_id
409 AND XL.task_id = PAT.task_id
410 AND XL.credit_card_trx_id is null
411 AND XL.award_id = GMS.award_id(+)
412 AND XL.report_header_id = AV.report_header_id(+)
413 AND XL.distribution_line_number = AV.distribution_line_number(+)
414 AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
415 UNION ALL
416 SELECT XL.receipt_missing_flag,
417 to_char(XL.start_expense_date),
418 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
419 LPAD(to_char(XL.daily_amount),9),
420 XL.receipt_currency_code,
421 LPAD(to_char(XL.receipt_conversion_rate),5),
422 LPAD(to_char(XL.receipt_currency_amount),10),
423 XL.amount,
424 XL.justification,
425 nvl(XP.web_friendly_prompt, XP.prompt),
426 NULL,
427 NULL,
428 XL.credit_card_trx_id,
429 XL.distribution_line_number,
430 NULL,
431 av.displayed_field,
432 XL.merchant_name,
433 nvl(XL.flex_concatenated, XH.flex_concatenated),
434 XL.mileage_rate_adjusted_flag
435 FROM ap_expense_report_params XP,
436 ap_expense_report_lines XL,
437 ap_expense_report_headers XH,
438 ap_lookup_codes LC,
439 AP_POL_VIOLATIONS_V av
440 WHERE XL.report_header_id = p_report_header_id
441 AND XL.report_header_id = XH.report_header_id
442 AND XL.web_parameter_id = XP.parameter_id
443 AND XL.line_type_lookup_code = LC.lookup_code
444 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
445 AND XL.project_id is null
446 AND XL.task_id is null
447 AND XL.credit_card_trx_id is null
448 AND XL.award_id is null
449 AND XL.report_header_id = AV.report_header_id(+)
450 AND XL.distribution_line_number = AV.distribution_line_number(+)
451 AND (XL.itemization_parent_id is null or XL.itemization_parent_id = -1)
452 ORDER BY 14;
453 end if;
454
455 return TRUE;
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 return FALSE;
459 WHEN OTHERS THEN
460 AP_WEB_DB_UTIL_PKG.RaiseException('GetDisplayXpenseLinesCursor');
461 APP_EXCEPTION.RAISE_EXCEPTION;
462 return FALSE;
463 END GetDisplayXpenseLinesCursor;
464
465 --------------------------------------------------------------------------------
466 FUNCTION GetDisplayPersonalLinesCursor(
467 p_report_header_id IN expLines_headerID,
468 p_personal_lines_cursor OUT NOCOPY DisplayXpenseLinesCursor)
469 RETURN BOOLEAN IS
470 --------------------------------------------------------------------------------
471 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
472 BEGIN
473 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
474 return FALSE;
475 END IF; /* GetPersonalParamID */
476
477 OPEN p_personal_lines_cursor FOR
478 SELECT XL.receipt_missing_flag,
479 -- Called from GenerateExpClobLines for rendering old notifications for
480 -- expenses created prior to 11.5.10, will not be used for reports submitted
481 -- in R12 hence decision was made not to tune these old queries.
482 to_char(XL.start_expense_date),
483 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
484 XL.start_expense_date)+1),4),
485 LPAD(to_char(XL.daily_amount),9),
486 XL.receipt_currency_code,
487 LPAD(to_char(XL.receipt_conversion_rate),5),
488 LPAD(to_char(XL.receipt_currency_amount),10),
489 XL.amount,
490 nvl(XL.justification, '&' || 'nbsp'),
491 nvl(XP.web_friendly_prompt, XP.prompt),
492 PAP.segment1, --PAP.project_number,
493 nvl(PAT.task_number, '&' || 'nbsp'),
494 XL.credit_card_trx_id,
495 XL.distribution_line_number,
496 XL.MERCHANT_NAME --Bug 2942773
497 FROM ap_expense_report_params XP,
498 ap_expense_report_lines XL,
499 ap_credit_card_trxns CC,
500 ap_lookup_codes LC,
501 PA_PROJECTS_ALL PAP, -- AP_WEB_PA_PROJECTS_V PAP, -- bug 1652647
502 PA_TASKS PAT -- AP_WEB_PA_PROJECTS_TASKS_V PAT -- bug 1652647
503 WHERE XL.report_header_id = p_report_header_id
504 AND XL.project_id is not null
505 AND XL.task_id is not null
506 AND XL.web_parameter_id = XP.parameter_id
507 AND XL.line_type_lookup_code = LC.lookup_code
508 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
509 AND XL.project_id = PAP.project_id
510 AND XL.project_id = PAT.project_id
511 AND XL.task_id = PAT.task_id
512 AND XL.credit_card_trx_id is not null
513 AND CC.trx_id = XL.credit_card_trx_id
514 AND (CC.category = 'PERSONAL' OR XL.web_parameter_id = l_personalParameterId)
515 UNION ALL
516 SELECT XL.receipt_missing_flag,
517 to_char(XL.start_expense_date),
518 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) - XL.start_expense_date)+1),4),
519 LPAD(to_char(XL.daily_amount),9),
520 XL.receipt_currency_code,
521 LPAD(to_char(XL.receipt_conversion_rate),5),
522 LPAD(to_char(XL.receipt_currency_amount),10),
523 XL.amount,
524 XL.justification,
525 nvl(XP.web_friendly_prompt, XP.prompt),
526 NULL,
527 NULL,
528 XL.credit_card_trx_id,
529 XL.distribution_line_number,
530 XL.MERCHANT_NAME --Bug 2942773
531 FROM ap_expense_report_params XP,
532 ap_expense_report_lines XL,
533 ap_credit_card_trxns CC,
534 ap_lookup_codes LC
535 WHERE XL.report_header_id = p_report_header_id
536 AND XL.web_parameter_id = XP.parameter_id
537 AND XL.line_type_lookup_code = LC.lookup_code
538 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
539 AND XL.project_id is null
540 AND XL.task_id is null
541 AND XL.credit_card_trx_id is not null
542 AND CC.trx_id = XL.credit_card_trx_id
543 AND (CC.category = 'PERSONAL' OR XL.web_parameter_id = l_personalParameterId)
544 ORDER BY distribution_line_number;
545
546 return TRUE;
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 return FALSE;
550 WHEN OTHERS THEN
551 AP_WEB_DB_UTIL_PKG.RaiseException('GetDisplayPersonalLinesCursor');
552 APP_EXCEPTION.RAISE_EXCEPTION;
553 return FALSE;
554 END GetDisplayPersonalLinesCursor;
555
556 /**
557 * rlangi AUDIT
558 * Check to see if there are any line level audit issue
559 */
560 --------------------------------------------------------------------------------
561 FUNCTION AnyAuditIssue(p_report_header_id IN expLines_headerID)
562 RETURN BOOLEAN IS
563 --------------------------------------------------------------------------------
564 l_temp VARCHAR2(1);
565 BEGIN
566
567 SELECT 'Y'
568 INTO l_temp
569 FROM ap_expense_report_lines aerl
570 WHERE aerl.report_header_id = p_report_header_id
571 AND aerl.adjustment_reason_code is not null
572 AND rownum = 1;
573
574 return TRUE;
575
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 return FALSE;
579 WHEN OTHERS THEN
580 AP_WEB_DB_UTIL_PKG.RaiseException('AnyAuditIssue');
581 APP_EXCEPTION.RAISE_EXCEPTION;
582 return FALSE;
583 END AnyAuditIssue;
584
585
586
587 /**
588 * jrautiai ADJ Fix
589 * Modified to fetch the cursor for both adjustments and shortpays, this was done
590 * to group the logic together and to simplify it.
591 */
592 --------------------------------------------------------------------------------
593 FUNCTION GetAdjustmentsCursor(p_report_header_id IN expLines_headerID,
594 p_adjustment_type IN VARCHAR2,
595 p_cursor OUT NOCOPY AdjustmentCursorType)
596 RETURN BOOLEAN IS
597 --------------------------------------------------------------------------------
598 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
599 l_roundingParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
600 BEGIN
601 /**
602 * Note the select statement needs to have the same number and type of columns in order for the reference cursor
603 * to work. Currently the queries match except for where statement. Not using dynamic SQL here because there are
604 * a lot of delimiters in the query and also the columns might be calculated differently for the cases in the
605 * future.
606 */
607
608 /* jrautiai ADJ Fix Start */
609 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
610 l_personalParameterId := fnd_api.G_MISS_NUM;
611 END IF;
612
613 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetRoundingParamID(l_roundingParameterId)) THEN
614 l_roundingParameterId := fnd_api.G_MISS_NUM;
615 END IF;
616 /* jrautiai ADJ Fix End */
617
618 IF p_adjustment_type = 'ADJUSTMENT' THEN
619
620 OPEN p_cursor FOR
621 SELECT aerl.report_header_id,
622 aerl.start_expense_date,
623 aerl.amount,
624 aerl.submitted_amount,
625 (aerl.submitted_amount - aerl.amount) adjusted_amount,
626 aerl.web_parameter_id,
627 AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_disp,
628 aerl.justification,
629 aerl.adjustment_reason_code,
630 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_code_disp,
631 AP_WEB_POLICY_UTILS.get_lookup_description('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_description,
632 aerl.adjustment_reason,
633 DECODE(aerl.CREDIT_CARD_TRX_ID,
634 null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
635 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) credit_card_expense_disp,
636 DECODE(aerl.itemization_parent_id,
637 null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
638 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) itemized_expense_disp
639 FROM ap_expense_report_lines aerl
640 WHERE aerl.report_header_id in (select p_report_header_id from dual
641 union
642 select aerh1.report_header_id
643 from ap_expense_report_headers_all aerh1
644 where aerh1.SHORTPAY_PARENT_ID = p_report_header_id)
645 AND (itemization_parent_id is null OR itemization_parent_id = -1)
646 AND aerl.web_parameter_id <> l_roundingParameterId
647 AND aerl.amount <> NVL(aerl.submitted_amount,aerl.amount)
648 ORDER BY aerl.distribution_line_number;
649
650 ELSIF p_adjustment_type = 'AUDIT' THEN
651
652 OPEN p_cursor FOR
653 SELECT aerl.report_header_id,
654 aerl.start_expense_date,
655 aerl.amount,
656 aerl.submitted_amount,
657 (aerl.submitted_amount - aerl.amount) adjusted_amount,
658 aerl.web_parameter_id,
659 AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_disp,
660 aerl.justification,
661 aerl.adjustment_reason_code,
662 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_code_disp,
663 AP_WEB_POLICY_UTILS.get_lookup_description('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_description,
664 aerl.adjustment_reason,
665 DECODE(aerl.CREDIT_CARD_TRX_ID,
666 null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
667 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) credit_card_expense_disp,
668 DECODE(aerl.itemization_parent_id,
669 null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
670 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) itemized_expense_disp
671 FROM ap_expense_report_lines aerl
672 WHERE aerl.report_header_id = p_report_header_id
673 AND (itemization_parent_id is null OR itemization_parent_id = -1)
674 AND aerl.adjustment_reason_code is not null
675 AND aerl.web_parameter_id <> l_roundingParameterId
676 ORDER BY aerl.distribution_line_number;
677
678 ELSE
679 OPEN p_cursor FOR
680 SELECT aerl.report_header_id,
681 aerl.start_expense_date,
682 aerl.amount,
683 aerl.submitted_amount,
684 (aerl.submitted_amount - aerl.amount) adjusted_amount,
685 aerl.web_parameter_id,
686 AP_WEB_AUDIT_UTILS.get_expense_type(aerl.web_parameter_id) expense_type_disp,
687 aerl.justification,
688 aerl.adjustment_reason_code,
689 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_code_disp,
690 AP_WEB_POLICY_UTILS.get_lookup_description('OIE_LINE_ADJUSTMENT_REASONS',aerl.adjustment_reason_code) adjustment_reason_description,
691 aerl.adjustment_reason,
692 DECODE(aerl.CREDIT_CARD_TRX_ID,
693 null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
694 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) credit_card_expense_disp,
695 DECODE(aerl.itemization_parent_id,
696 null,AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','N'),
697 AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_YES_NO','Y')) itemized_expense_disp
698 FROM ap_expense_report_lines aerl
699 WHERE aerl.report_header_id = p_report_header_id
700 AND (itemization_parent_id is null OR itemization_parent_id = -1)
701 AND aerl.web_parameter_id <> l_roundingParameterId
702 ORDER BY aerl.distribution_line_number;
703 END IF;
704
705 return TRUE;
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 return FALSE;
709 WHEN OTHERS THEN
710 AP_WEB_DB_UTIL_PKG.RaiseException('GetAdjustmentsCursor');
711 APP_EXCEPTION.RAISE_EXCEPTION;
712 return FALSE;
713 END GetAdjustmentsCursor;
714
715 /* This function was merged with the following functions:
716 GetExpLineAcctTemplateCursor
717 GetXpenseLineInfoForPATCCursor
718 that selected columns from the same tables.
719 */
720 --------------------------------------------------------------------------------
721 FUNCTION GetExpDistAcctCursor(p_exp_report_id IN expLines_headerID,
722 p_cursor OUT NOCOPY XpenseLineAcctCursor)
723 RETURN BOOLEAN IS
724 --------------------------------------------------------------------------------
725 BEGIN
726 OPEN p_cursor FOR
727 SELECT XL.distribution_line_number,
728 XD.report_distribution_id,
729 XL.start_expense_date,
730 XD.amount,
731 nvl(XP.web_friendly_prompt, XP.prompt) expense_type,
732 XL.credit_card_trx_id,
733 XD.project_id,
734 XD.task_id,
735 XD.award_id,
736 XL.expenditure_item_date,
737 XL.expenditure_type,
738 XL.pa_quantity,
739 XD.expenditure_organization_id,
740 XL.web_parameter_id,
741 XL.adjustment_reason,
742 XP.flex_concactenated,
743 XL.category_code,
744 XL.attribute_category,
745 XL.attribute1,
746 XL.attribute2,
747 XL.attribute3,
748 XL.attribute4,
749 XL.attribute5,
750 XL.attribute6,
751 XL.attribute7,
752 XL.attribute8,
753 XL.attribute9,
754 XL.attribute10,
755 XL.attribute11,
756 XL.attribute12,
757 XL.attribute13,
758 XL.attribute14,
759 XL.attribute15,
760 XD.cost_center,
761 XL.AP_VALIDATION_ERROR,
762 XL.Report_Line_id
763 FROM ap_expense_report_params XP,
764 ap_expense_report_lines XL,
765 ap_exp_report_dists XD
766 WHERE XL.report_header_id = p_exp_report_id
767 AND XL.web_parameter_id = XP.parameter_id
768 AND XD.report_line_id(+) = XL.report_line_id
769 AND (XL.itemization_parent_id is null OR XL.itemization_parent_id <> -1)
770 ORDER BY XL.distribution_line_number;
771
772 return TRUE;
773
774 EXCEPTION
775 WHEN NO_DATA_FOUND THEN
776 return FALSE;
777 WHEN OTHERS THEN
778 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpDistAcctCursor');
779 APP_EXCEPTION.RAISE_EXCEPTION;
780 return FALSE;
781 END GetExpDistAcctCursor;
782
783 -------------------------------------------------------------------
784 FUNCTION CalcNoReceiptsShortpayAmts(
785 p_report_header_id IN expLines_headerID,
786 p_no_receipts_ccard_amt OUT NOCOPY NUMBER,
787 p_no_receipts_emp_amt OUT NOCOPY NUMBER
788 ) RETURN BOOLEAN IS
789 -------------------------------------------------------------------
790 BEGIN
791 SELECT sum(DECODE(aerl.credit_card_trx_id,null,0,aerl.amount)),
792 sum(DECODE(aerl.credit_card_trx_id, null, aerl.amount,0))
793 INTO p_no_receipts_ccard_amt, p_no_receipts_emp_amt
794 FROM ap_expense_report_lines aerl,
795 ap_expense_report_headers aerh
796 WHERE aerl.report_header_id = p_report_header_id
797 AND aerh.report_header_id = aerl.report_header_id
798 AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
799 AND nvl(aerl.receipt_required_flag, 'N') = 'Y'
800 AND nvl(aerl.receipt_verified_flag, 'N') = 'N'
801 AND nvl(aerl.policy_shortpay_flag, 'N') = 'N';
802
803 return TRUE;
804 EXCEPTION
805 WHEN NO_DATA_FOUND THEN
806 p_no_receipts_ccard_amt:= 0;
807 p_no_receipts_emp_amt:= 0;
808 return FALSE;
809 WHEN OTHERS THEN
810 AP_WEB_DB_UTIL_PKG.RaiseException('CalcNoReceiptsShortpayAmts');
811 APP_EXCEPTION.RAISE_EXCEPTION;
812 return FALSE;
813 END CalcNoReceiptsShortpayAmts;
814
815
816 -------------------------------------------------------------------
817 FUNCTION CalcNoReceiptsPersonalTotal(p_report_header_id IN expLines_headerID,
818 p_personal_total OUT NOCOPY NUMBER
819 ) RETURN BOOLEAN IS
820 -------------------------------------------------------------------
821 BEGIN
822 SELECT nvl(sum(erl.amount),0)
823 INTO p_personal_total
824 FROM ap_expense_report_lines erl,
825 ap_expense_report_headers aerh,
826 ap_credit_card_trxns cct
827 WHERE erl.report_header_id = p_report_header_id
828 AND aerh.report_header_id = erl.report_header_id
829 AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
830 AND nvl(erl.receipt_required_flag, 'N') = 'Y'
831 AND nvl(erl.receipt_verified_flag, 'N') = 'N'
832 AND nvl(erl.policy_shortpay_flag, 'N') = 'N'
833 AND erl.credit_card_trx_id = cct.trx_id
834 AND cct.category = 'PERSONAL';
835
836 return TRUE;
837 EXCEPTION
838 WHEN NO_DATA_FOUND THEN
839 p_personal_total:= 0;
840 return FALSE;
841 WHEN OTHERS THEN
842 AP_WEB_DB_UTIL_PKG.RaiseException('CalcNoReceiptsPersonalTotal');
843 APP_EXCEPTION.RAISE_EXCEPTION;
844 return FALSE;
845 END CalcNoReceiptsPersonalTotal;
846
847 -------------------------------------------------------------------
848 FUNCTION CalculatePolicyShortpayAmts(
849 p_report_header_id IN expLines_headerID,
850 p_policy_ccard_amt OUT NOCOPY NUMBER,
851 p_policy_emp_amt OUT NOCOPY NUMBER,
852 p_policy_shortpay_total OUT NOCOPY NUMBER
853 ) RETURN BOOLEAN IS
854 -------------------------------------------------------------------
855 BEGIN
856 --bug 5518553 : exclude itemization parent line
857 SELECT sum(DECODE(credit_card_trx_id, null,amount,0)), sum(DECODE(credit_card_trx_id, null,0,amount))
858 INTO p_policy_emp_amt, p_policy_ccard_amt
859 FROM ap_expense_report_lines
860 WHERE report_header_id = P_report_header_id
861 AND nvl(policy_shortpay_flag, 'N') = 'Y'
862 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
863
864 p_policy_shortpay_total := p_policy_ccard_amt + p_policy_emp_amt;
865
866 RETURN true;
867
868 EXCEPTION
869 WHEN NO_DATA_FOUND THEN
870 p_policy_ccard_amt:= 0;
871 p_policy_emp_amt:= 0;
872 p_policy_shortpay_total := 0;
873 return FALSE;
874 WHEN OTHERS THEN
875 AP_WEB_DB_UTIL_PKG.RaiseException('CalculatePolicyShortpayAmts');
876 APP_EXCEPTION.RAISE_EXCEPTION;
877 return FALSE;
878 END CalculatePolicyShortpayAmts;
879
880 -------------------------------------------------------------------
881 FUNCTION GetReceiptMissingTotal(p_report_header_id IN expLines_headerID,
882 p_sum_missing_receipts OUT NOCOPY NUMBER)
883 RETURN BOOLEAN IS
884 -------------------------------------------------------------------
885 BEGIN
886 -- bug 5518553 : exclude itemization parent line
887 SELECT sum(amount)
888 INTO p_sum_missing_receipts
889 FROM ap_expense_report_lines
890 WHERE report_header_id = p_report_header_id
891 AND amount >= 0
892 AND receipt_missing_flag = 'Y'
893 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
894
895 RETURN true;
896 EXCEPTION
897 WHEN NO_DATA_FOUND THEN
898 p_sum_missing_receipts:= 0;
899 return FALSE;
900 WHEN OTHERS THEN
901 AP_WEB_DB_UTIL_PKG.RaiseException('GetReceiptMissingTotal');
902 APP_EXCEPTION.RAISE_EXCEPTION;
903 return FALSE;
904 END GetReceiptMissingTotal;
905 -------------------------------------------------------------------
906 FUNCTION GetReceiptViolationsTotal(p_report_header_id IN expLines_headerID,
907 p_sum_violations OUT NOCOPY NUMBER)
908 RETURN BOOLEAN IS
909 -------------------------------------------------------------------
910 BEGIN
911
912 select sum(daily_amount *
913 LPAD(to_char((nvl(end_expense_date,start_expense_date) -
914 start_expense_date)+1),4)) violation_total
915 into p_sum_violations
916 from ap_expense_report_lines_all
917 where report_header_id = p_report_header_id
918 and amount >= 0
919 and receipt_missing_flag <> 'Y'
920 and distribution_line_number in (
921 select distinct (distribution_line_number)
922 from ap_pol_violations
923 where report_header_id = p_report_header_id);
924
925 RETURN true;
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928 p_sum_violations:= 0;
929 return FALSE;
930 WHEN OTHERS THEN
931 AP_WEB_DB_UTIL_PKG.RaiseException('GetReceiptViolationsTotal');
932 APP_EXCEPTION.RAISE_EXCEPTION;
933 return FALSE;
934 END GetReceiptViolationsTotal;
935
936 -------------------------------------------------------------------
937 FUNCTION GetPersonalTotalOfExpRpt(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
938 p_personal_total OUT NOCOPY NUMBER)
939 RETURN BOOLEAN IS
940 -------------------------------------------------------------------
941 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
942 BEGIN
943
944 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
945 return FALSE;
946 END IF; /* GetPersonalParamID */
947
948 -- used (itemization_parent_id is null OR itemization_parent_id <> -1)
949 -- as the same condition is used in CalculateAmtsDue
950 SELECT nvl(sum(amount),0)
951 INTO p_personal_total
952 FROM ap_expense_report_lines erl
953 WHERE erl.report_header_id = p_report_header_id
954 AND erl.web_parameter_id = l_personalParameterId
955 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
956
957 return TRUE;
958
959 EXCEPTION
960 WHEN NO_DATA_FOUND THEN
961 p_personal_total:= 0;
962 return FALSE;
963 WHEN OTHERS THEN
964 AP_WEB_DB_UTIL_PKG.RaiseException('GetPersonalTotalOfExpRpt');
965 APP_EXCEPTION.RAISE_EXCEPTION;
966 return FALSE;
967 END GetPersonalTotalOfExpRpt;
968
969 --------------------------------------------------------------------------------
970 FUNCTION CalculateAmtsDue(p_report_header_id IN expLines_headerID,
971 p_emp_amt OUT NOCOPY NUMBER,
972 p_ccard_amt OUT NOCOPY NUMBER
973 ) RETURN BOOLEAN IS
974 --------------------------------------------------------------------------------
975 BEGIN
976 SELECT sum(DECODE(credit_card_trx_id, null,amount,0)),
977 sum(DECODE(credit_card_trx_id, null,0,amount))
978 INTO p_emp_amt, p_ccard_amt
979 FROM ap_expense_report_lines
980 WHERE report_header_id = p_report_header_id
981 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
982
983 return TRUE;
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 p_emp_amt := 0;
987 p_ccard_amt := 0;
988 return FALSE;
989 WHEN OTHERS THEN
990 AP_WEB_DB_UTIL_PKG.RaiseException('CalculateAmtsDue');
991 APP_EXCEPTION.RAISE_EXCEPTION;
992 return FALSE;
993 END CalculateAmtsDue;
994
995 -------------------------------------------------------------------
996 FUNCTION GetNumReceiptRequiredLines(p_report_header_id IN expLines_headerID,
997 p_num_req_receipts OUT NOCOPY NUMBER)
998 RETURN BOOLEAN IS
999 -------------------------------------------------------------------
1000 BEGIN
1001 SELECT count(*)
1002 INTO p_num_req_receipts
1003 FROM ap_expense_report_lines
1004 WHERE report_header_id = p_report_header_id
1005 AND nvl(receipt_required_flag, 'N') = 'Y';
1006
1007 RETURN true;
1008
1009 EXCEPTION
1010 WHEN NO_DATA_FOUND THEN
1011 return FALSE;
1012 WHEN OTHERS THEN
1013 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumReceiptRequiredLines');
1014 APP_EXCEPTION.RAISE_EXCEPTION;
1015 return FALSE;
1016 END GetNumReceiptRequiredLines;
1017
1018
1019 -----------------------------------------------------------------------------------------------------
1020 FUNCTION GetNumReceiptShortpaidLines(p_report_header_id IN expLines_headerID,
1021 p_num_req_receipt_not_verified OUT NOCOPY NUMBER)
1022 RETURN BOOLEAN IS
1023 -----------------------------------------------------------------------------------------------------
1024 BEGIN
1025 SELECT count(*)
1026 INTO p_num_req_receipt_not_verified
1027 FROM ap_expense_report_lines aerl,
1028 ap_expense_report_headers aerh
1029 WHERE aerl.report_header_id = p_report_header_id
1030 AND aerh.report_header_id = aerl.report_header_id
1031 AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
1032 AND nvl(aerl.receipt_required_flag, 'N') = 'Y'
1033 AND nvl(aerl.receipt_missing_flag, 'N') = 'N'
1034 AND nvl(aerl.receipt_verified_flag, 'N') = 'N';
1035
1036 RETURN true;
1037
1038 EXCEPTION
1039 WHEN NO_DATA_FOUND THEN
1040 return FALSE;
1041 WHEN OTHERS THEN
1042 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumReceiptShortpaidLines');
1043 APP_EXCEPTION.RAISE_EXCEPTION;
1044 return FALSE;
1045 END GetNumReceiptShortpaidLines;
1046
1047 -------------------------------------------------------------------
1048 FUNCTION GetNumShortpaidLines(p_report_header_id IN expLines_headerID,
1049 p_count OUT NOCOPY NUMBER)
1050 RETURN BOOLEAN IS
1051 -------------------------------------------------------------------
1052 BEGIN
1053 SELECT count(*)
1054 INTO p_count
1055 FROM ap_expense_report_lines aerl,
1056 ap_expense_report_headers aerh
1057 WHERE aerl.report_header_id = p_report_header_id
1058 AND aerh.report_header_id = aerl.report_header_id
1059 AND (nvl(aerl.policy_shortpay_flag,'N') = 'Y'
1060 OR ( nvl(aerl.receipt_required_flag, 'N') = 'Y'
1061 AND nvl(aerl.receipt_verified_flag, 'N') = 'N'
1062 AND nvl(aerh.receipts_status,'NONE') <> AP_WEB_RECEIPT_MANAGEMENT_UTIL.C_STATUS_WAIVED
1063 )
1064 );
1065
1066 RETURN true;
1067
1068 EXCEPTION
1069 WHEN NO_DATA_FOUND THEN
1070 return FALSE;
1071 WHEN OTHERS THEN
1072 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumShortpaidLines');
1073 APP_EXCEPTION.RAISE_EXCEPTION;
1074 return FALSE;
1075 END GetNumShortpaidLines;
1076
1077 -------------------------------------------------------------------
1078 FUNCTION GetNumJustReqdLines(p_report_header_id IN expLines_headerID,
1079 p_num_req_receipts OUT NOCOPY NUMBER)
1080 RETURN BOOLEAN IS
1081 -------------------------------------------------------------------
1082 BEGIN
1083 SELECT count(*)
1084 INTO p_num_req_receipts
1085 FROM ap_expense_report_lines
1086 WHERE report_header_id = p_report_header_id
1087 AND nvl(justification_required_flag, 'V') = 'Y'
1088 AND amount >= 0;
1089
1090 RETURN true;
1091
1092 EXCEPTION
1093 WHEN NO_DATA_FOUND THEN
1094 return FALSE;
1095 WHEN OTHERS THEN
1096 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumJustReqdLines');
1097 APP_EXCEPTION.RAISE_EXCEPTION;
1098 return FALSE;
1099 END GetNumJustReqdLines;
1100
1101
1102 -------------------------------------------------------------------
1103 FUNCTION GetNumberOfExpLines(p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
1104 p_count OUT NOCOPY NUMBER)
1105 RETURN BOOLEAN IS
1106 -------------------------------------------------------------------
1107 BEGIN
1108 SELECT count(*)
1109 INTO p_count
1110 FROM ap_expense_report_lines
1111 WHERE report_header_id = p_report_header_id;
1112
1113 return true;
1114
1115 EXCEPTION
1116 WHEN NO_DATA_FOUND THEN
1117 return FALSE;
1118 WHEN OTHERS THEN
1119 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumberOfExpLines');
1120 APP_EXCEPTION.RAISE_EXCEPTION;
1121 return FALSE;
1122 END GetNumberOfExpLines;
1123
1124 -------------------------------------------------------------------
1125 FUNCTION GetNumCCLinesIncluded(p_report_header_id IN expLines_headerID,
1126 p_crd_card_count OUT NOCOPY NUMBER)
1127 RETURN BOOLEAN IS
1128 -------------------------------------------------------------------
1129 BEGIN
1130 SELECT count(*)
1131 INTO p_crd_card_count
1132 FROM ap_expense_report_lines
1133 WHERE report_header_id = p_report_header_id
1134 AND credit_card_trx_id IS NOT NULL;
1135
1136 return TRUE;
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 return FALSE;
1140 WHEN OTHERS THEN
1141 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumCCLinesIncluded');
1142 APP_EXCEPTION.RAISE_EXCEPTION;
1143 return FALSE;
1144 END GetNumCCLinesIncluded;
1145
1146
1147 -------------------------------------------------------------------
1148 FUNCTION GetNumberOfPersonalLines(p_report_header_id IN expLines_headerID,
1149 p_personal_count OUT NOCOPY NUMBER)
1150 RETURN BOOLEAN IS
1151 -------------------------------------------------------------------
1152 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
1153 BEGIN
1154 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
1155 return FALSE;
1156 END IF; /* GetPersonalParamID */
1157
1158 SELECT count(*)
1159 INTO p_personal_count
1160 FROM ap_expense_report_lines XL,
1161 ap_credit_card_trxns CC
1162 WHERE XL.report_header_id = p_report_header_id
1163 AND XL.credit_card_trx_id is not null
1164 AND CC.trx_id = XL.credit_card_trx_id
1165 AND (CC.category = 'PERSONAL' OR XL.web_parameter_id = l_personalParameterId);
1166
1167 return true;
1168
1169 EXCEPTION
1170 WHEN NO_DATA_FOUND THEN
1171 return FALSE;
1172 WHEN OTHERS THEN
1173 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumberOfPersonalLines');
1174 APP_EXCEPTION.RAISE_EXCEPTION;
1175 return FALSE;
1176 END GetNumberOfPersonalLines;
1177
1178
1179 --------------------------------------------------------------------------------
1180 FUNCTION ContainsProjectRelatedLine(
1181 p_ReportHeaderID IN expLines_headerID
1182 ) RETURN BOOLEAN IS
1183 --------------------------------------------------------------------------------
1184 V_Temp VARCHAR2(20);
1185 BEGIN
1186
1187 return AP_WEB_DB_EXPDIST_PKG.ContainsProjectRelatedDist(p_report_header_id => p_ReportHeaderID);
1188
1189 EXCEPTION
1190 WHEN TOO_MANY_ROWS THEN
1191 return TRUE;
1192 WHEN NO_DATA_FOUND THEN
1193 return FALSE;
1194
1195 WHEN OTHERS THEN
1196 AP_WEB_DB_UTIL_PKG.RaiseException( 'ContainProjectRelatedLine' );
1197 APP_EXCEPTION.RAISE_EXCEPTION;
1198 return FALSE;
1199 END ContainsProjectRelatedLine;
1200
1201
1202 --------------------------------------------------------------------------------
1203 FUNCTION ContainsNonProjectRelatedLine(
1204 p_ReportHeaderID IN expLines_headerID
1205 ) RETURN BOOLEAN IS
1206 --------------------------------------------------------------------------------
1207 V_Temp VARCHAR2(20);
1208 BEGIN
1209
1210 return AP_WEB_DB_EXPDIST_PKG.ContainsNonProjectRelatedDist(p_report_header_id => p_ReportHeaderID);
1211
1212 EXCEPTION
1213 WHEN TOO_MANY_ROWS THEN
1214 return TRUE;
1215 WHEN NO_DATA_FOUND THEN
1216 return FALSE;
1217 WHEN OTHERS THEN
1218 AP_WEB_DB_UTIL_PKG.RaiseException( 'ContainsNonProjectRelatedLine' );
1219
1220 APP_EXCEPTION.RAISE_EXCEPTION;
1221 return FALSE;
1222 END ContainsNonProjectRelatedLine;
1223
1224 --------------------------------------------------------------------------------
1225 FUNCTION AddReportLines(
1226 p_xpense_lines IN XpenseLineRec,
1227 p_expLines IN AP_WEB_DFLEX_PKG.ExpReportLineRec,
1228 P_AttributeCol IN AP_WEB_PARENT_PKG.BigString_Array,
1229 i IN NUMBER
1230 ) RETURN BOOLEAN IS
1231 --------------------------------------------------------------------------------
1232 C_LinesDescFlexName CONSTANT VARCHAR2(50) := 'AP_EXPENSE_REPORT_LINES';
1233 l_context_enabled VARCHAR2(1) := 'N';
1234 l_flexfield FND_DFLEX.DFLEX_R;
1235 l_flexinfo FND_DFLEX.DFLEX_DR;
1236 BEGIN
1237
1238 FND_DFLEX.Get_Flexfield('SQLAP',
1239 C_LinesDescFlexname,
1240 l_flexfield,
1241 l_flexinfo);
1242
1243 BEGIN
1244 SELECT 'Y' INTO l_context_enabled
1245 FROM fnd_descr_flex_contexts_vl
1246 WHERE application_id = l_flexfield.application_id
1247 AND descriptive_flexfield_name = l_flexfield.flexfield_name
1248 AND enabled_flag = 'Y'
1249 AND global_flag = 'N'
1250 AND descriptive_flex_context_code = p_xpense_lines.item_description;
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 null;
1254 END;
1255
1256 -- chiho: dealing with the case of having invalid parameter id(NULL) hereL:
1257 IF ( p_expLines.parameter_id IS NOT NULL ) THEN
1258 INSERT INTO AP_EXPENSE_REPORT_LINES
1259 (report_header_id,
1260 code_combination_id,
1261 web_parameter_id,
1262 set_of_books_id,
1263 amount,
1264 item_description,
1265 line_type_lookup_code,
1266 currency_code,
1267 receipt_missing_flag,
1268 receipt_required_flag,
1269 justification,
1270 expense_group,
1271 start_expense_date,
1272 end_expense_date,
1273 receipt_currency_code,
1274 receipt_conversion_rate,
1275 daily_amount,
1276 receipt_currency_amount,
1277 creation_date,
1278 created_by,
1279 last_update_date,
1280 last_updated_by,
1281 distribution_line_number,
1282 amount_includes_tax_flag,
1283 tax_code_id,
1284 vat_code, -- Bug 1303470
1285 tax_code_override_flag,
1286 merchant_name,
1287 merchant_document_number,
1288 merchant_reference,
1289 merchant_tax_reg_number,
1290 merchant_taxpayer_id,
1291 country_of_supply,
1292 credit_card_trx_id,
1293 project_id,
1294 project_number,
1295 task_id,
1296 task_number,
1297 expenditure_organization_id,
1298 expenditure_type,
1299 expenditure_item_date,
1300 project_accounting_context,
1301 company_prepaid_invoice_id,
1302 attribute_category,
1303 attribute1,
1304 attribute2,
1305 attribute3,
1306 attribute4,
1307 attribute5,
1308 attribute6,
1309 attribute7,
1310 attribute8,
1311 attribute9,
1312 attribute10,
1313 attribute11,
1314 attribute12,
1315 attribute13,
1316 attribute14,
1317 attribute15,
1318 report_line_id,
1319 org_id)
1320 SELECT
1321 p_xpense_lines.new_report_header_id,
1322 p_xpense_lines.code_combination_id,
1323 p_expLines.parameter_id,
1324 p_xpense_lines.set_of_books_id,
1325 p_expLines.amount,
1326 p_xpense_lines.item_description,
1327 p_xpense_lines.line_type_lookup_code,
1328 p_xpense_lines.reimbursement_currency_code,
1329 p_expLines.receipt_missing_flag,
1330 p_xpense_lines.require_receipt_flag,
1331 AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_expLines.justification),
1332 p_expLines.group_value,
1333 p_xpense_lines.date1_temp,
1334 p_xpense_lines.date2_temp,
1335 p_expLines.currency_code,
1336 p_xpense_lines.rate,
1337 p_expLines.daily_amount,
1338 p_expLines.receipt_amount,
1339 sysdate,
1340 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
1341 sysdate,
1342 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
1343 i,
1344 p_expLines.amount_includes_tax,
1345 p_expLines.taxId,
1346 p_expLines.tax_code, -- Bug 1303470
1347 p_expLines.taxOverrideFlag,
1348 p_expLines.merchant,
1349 p_expLines.merchantDoc,
1350 p_expLines.taxReference,
1351 p_expLines.taxRegNumber,
1352 p_expLines.taxPayerId,
1353 p_expLines.supplyCountry,
1354 p_expLines.cCardTrxnId,
1355 to_number(p_expLines.project_id),
1356 p_expLines.project_number,
1357 to_number(p_expLines.task_id),
1358 p_expLines.task_number,
1359 DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',
1360 p_xpense_lines.expenditure_organization_id,NULL),
1361 p_expLines.expenditure_type,
1362 DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',NVL(p_xpense_lines.date2_temp, p_xpense_lines.date1_temp),NULL),
1363 p_xpense_lines.IsReceiptProjectEnabled,
1364 p_xpense_lines.company_prepaid_invoice_id, --company prepaid invoice id
1365 DECODE(l_context_enabled,'Y',p_xpense_lines.item_description,NULL),
1366 P_AttributeCol(1),
1367 P_AttributeCol(2),
1368 P_AttributeCol(3),
1369 P_AttributeCol(4),
1370 P_AttributeCol(5),
1371 P_AttributeCol(6),
1372 P_AttributeCol(7),
1373 P_AttributeCol(8),
1374 P_AttributeCol(9),
1375 P_AttributeCol(10),
1376 P_AttributeCol(11),
1377 P_AttributeCol(12),
1378 P_AttributeCol(13),
1379 P_AttributeCol(14),
1380 P_AttributeCol(15),
1381 ap_expense_report_lines_s.nextval,
1382 mo_global.get_current_org_id()
1383 FROM ap_expense_report_params
1384 WHERE parameter_id = p_expLines.parameter_id;
1385
1386 ELSE -- parameter_id IS NULL
1387 INSERT INTO AP_EXPENSE_REPORT_LINES
1388 (report_header_id,
1389 code_combination_id,
1390 web_parameter_id,
1391 set_of_books_id,
1392 amount,
1393 item_description,
1394 line_type_lookup_code,
1395 currency_code,
1396 receipt_missing_flag,
1397 receipt_required_flag,
1398 justification,
1399 expense_group,
1400 start_expense_date,
1401 end_expense_date,
1402 receipt_currency_code,
1403 receipt_conversion_rate,
1404 daily_amount,
1405 receipt_currency_amount,
1406 creation_date,
1407 created_by,
1408 last_update_date,
1409 last_updated_by,
1410 distribution_line_number,
1411 amount_includes_tax_flag,
1412 tax_code_id,
1413 tax_code_override_flag,
1414 merchant_name,
1415 merchant_document_number,
1416 merchant_reference,
1417 merchant_tax_reg_number,
1418 merchant_taxpayer_id,
1419 country_of_supply,
1420 credit_card_trx_id,
1421 project_id,
1422 project_number,
1423 task_id,
1424 task_number,
1425 expenditure_organization_id,
1426 expenditure_type,
1427 expenditure_item_date,
1428 project_accounting_context,
1429 company_prepaid_invoice_id,
1430 attribute_category,
1431 attribute1,
1432 attribute2,
1433 attribute3,
1434 attribute4,
1435 attribute5,
1436 attribute6,
1437 attribute7,
1438 attribute8,
1439 attribute9,
1440 attribute10,
1441 attribute11,
1442 attribute12,
1443 attribute13,
1444 attribute14,
1445 attribute15,
1446 report_line_id,
1447 org_id )
1448 VALUES (
1449 p_xpense_lines.new_report_header_id,
1450 p_xpense_lines.code_combination_id,
1451 p_expLines.parameter_id,
1452 p_xpense_lines.set_of_books_id,
1453 p_expLines.amount,
1454 p_xpense_lines.item_description,
1455 p_xpense_lines.line_type_lookup_code,
1456 p_xpense_lines.reimbursement_currency_code,
1457 p_expLines.receipt_missing_flag,
1458 p_xpense_lines.require_receipt_flag,
1459 AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_expLines.justification),
1460 p_expLines.group_value,
1461 p_xpense_lines.date1_temp,
1462 p_xpense_lines.date2_temp,
1463 p_expLines.currency_code,
1464 p_xpense_lines.rate,
1465 p_expLines.daily_amount,
1466 p_expLines.receipt_amount,
1467 sysdate,
1468 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
1469 sysdate,
1470 icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
1471 i,
1472 p_expLines.amount_includes_tax,
1473 p_expLines.taxId,
1474 p_expLines.taxOverrideFlag,
1475 p_expLines.merchant,
1476 p_expLines.merchantDoc,
1477 p_expLines.taxReference,
1478 p_expLines.taxRegNumber,
1479 p_expLines.taxPayerId,
1480 p_expLines.supplyCountry,
1481 p_expLines.cCardTrxnId,
1482 to_number(p_expLines.project_id),
1483 p_expLines.project_number,
1484 to_number(p_expLines.task_id),
1485 p_expLines.task_number,
1486 DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',
1487 p_xpense_lines.expenditure_organization_id,NULL),
1488 p_expLines.expenditure_type,
1489 DECODE(p_xpense_lines.IsReceiptProjectEnabled,'Y',NVL(p_xpense_lines.date2_temp, p_xpense_lines.date1_temp),NULL),
1490 p_xpense_lines.IsReceiptProjectEnabled,
1491 p_xpense_lines.company_prepaid_invoice_id, --company prepaid invoice id
1492 DECODE(l_context_enabled,'Y',p_xpense_lines.item_description,NULL),
1493 P_AttributeCol(1),
1494 P_AttributeCol(2),
1495 P_AttributeCol(3),
1496 P_AttributeCol(4),
1497 P_AttributeCol(5),
1498 P_AttributeCol(6),
1499 P_AttributeCol(7),
1500 P_AttributeCol(8),
1501 P_AttributeCol(9),
1502 P_AttributeCol(10),
1503 P_AttributeCol(11),
1504 P_AttributeCol(12),
1505 P_AttributeCol(13),
1506 P_AttributeCol(14),
1507 P_AttributeCol(15),
1508 ap_expense_report_lines_s.nextval,
1509 mo_global.get_current_org_id() );
1510 END IF;
1511
1512 RETURN TRUE;
1513 EXCEPTION
1514 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1515 return FALSE;
1516 WHEN OTHERS THEN
1517 AP_WEB_DB_UTIL_PKG.RaiseException('AddReportLines');
1518 APP_EXCEPTION.RAISE_EXCEPTION;
1519 return FALSE;
1520 END AddReportLines;
1521
1522
1523 --------------------------------------------------------------------------------
1524 FUNCTION AddPolicyShortPaidExpLines(
1525 p_new_expense_report_id IN expLines_headerID,
1526 p_orig_expense_report_id IN expLines_headerID
1527 ) RETURN BOOLEAN IS
1528 --------------------------------------------------------------------------------
1529
1530 CURSOR ReportLines IS
1531 SELECT REPORT_LINE_ID, ITEMIZATION_PARENT_ID
1532 FROM AP_EXPENSE_REPORT_LINES
1533 WHERE REPORT_HEADER_ID = p_orig_expense_report_id
1534 AND nvl(policy_shortpay_flag,'N') = 'Y'
1535 AND nvl(itemization_parent_id,-1) = -1;/*Bug:6131435*/
1536
1537 CURSOR ItemizationChildLines(p_report_line_id in number) IS
1538 SELECT REPORT_LINE_ID
1539 FROM AP_EXPENSE_REPORT_LINES
1540 WHERE itemization_parent_id = p_report_line_id
1541 and report_header_id = p_orig_expense_report_id; -- Bug: 6705839, Performance Issue during shortpay
1542
1543 l_OrigReportLineID expLines_report_line_id;
1544 l_itemization_parent_id AP_EXPENSE_REPORT_LINES.itemization_parent_id%type;
1545 i number;
1546
1547 BEGIN
1548
1549 -- Find all lines associated with this report
1550 OPEN ReportLines;
1551
1552 LOOP
1553 FETCH ReportLines into l_OrigReportLineID, l_itemization_parent_id;
1554 EXIT WHEN ReportLines%NOTFOUND;
1555
1556 UPDATE AP_EXPENSE_REPORT_LINES_ALL
1557 SET report_header_id = p_new_expense_report_id,
1558 mileage_rate_adjusted_flag = C_Unchanged,
1559 last_update_date = sysdate,
1560 creation_date = sysdate
1561 WHERE report_line_id = l_OrigReportLineID;
1562
1563 if (l_itemization_parent_id = -1) then
1564 for i in ItemizationChildLines(l_OrigReportLineID) loop
1565
1566 UPDATE AP_EXPENSE_REPORT_LINES_ALL
1567 SET report_header_id = p_new_expense_report_id,
1568 mileage_rate_adjusted_flag = C_Unchanged,
1569 last_update_date = sysdate,
1570 creation_date = sysdate
1571 WHERE report_line_id = i.report_line_id;
1572
1573 -- Move distribution lines associated with this child line
1574 AP_WEB_DB_EXPDIST_PKG.MoveDistributions(
1575 p_target_report_header_id => p_new_expense_report_id,
1576 p_source_report_line_id => i.report_line_id,
1577 p_target_report_line_id => i.report_line_id);
1578
1579 end loop;
1580 end if;
1581
1582
1583 -- Move distribution lines associated with this line
1584 /* Bug# 6131435 : Parent Line will not be having any distribution lines
1585 So Distribution lines need not be moved for a parent line */
1586 /* Bug# 6632585 : Distribution lines of non-itemized lines should be moved */
1587 IF (l_itemization_parent_id <> -1 OR l_itemization_parent_id is NULL) THEN
1588 AP_WEB_DB_EXPDIST_PKG.MoveDistributions(
1589 p_target_report_header_id => p_new_expense_report_id,
1590 p_source_report_line_id => l_OrigReportLineID,
1591 p_target_report_line_id => l_OrigReportLineID);
1592 END IF;
1593 END LOOP;
1594
1595 return TRUE;
1596
1597 EXCEPTION -- Block which encapsulates the delete and insert code
1598 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
1599 AP_WEB_DB_UTIL_PKG.RaiseException('AddPolicyShortPaidExpLines','',
1600 'AP_WEB_SAVESUB_DELETE_FAILED');
1601 APP_EXCEPTION.RAISE_EXCEPTION;
1602 return FALSE;
1603 WHEN OTHERS THEN
1604 IF (SQLCODE = -00054) THEN
1605 -- Tried to obtain lock when deleting on an already locked row
1606 -- Get invoice prefix profile option, and trim if it is too long
1607 -- Get message stating delete failed
1608 AP_WEB_DB_UTIL_PKG.RaiseException('AddPolicyShortPaidExpLines','',
1609 'AP_WEB_SAVESUB_LOCK_FAILED',
1610 'V_ReportHeaderID = ' || p_orig_expense_report_id);
1611 APP_EXCEPTION.RAISE_EXCEPTION;
1612 return FALSE;
1613 END IF;
1614 END AddPolicyShortPaidExpLines;
1615
1616 --------------------------------------------------------------------------------
1617 FUNCTION AddUnverifiedShortpaidLines(
1618 p_new_expense_report_id IN expLines_headerID,
1619 p_orig_expense_report_id IN expLines_headerID
1620 ) RETURN BOOLEAN IS
1621 --------------------------------------------------------------------------------
1622 CURSOR ReportLines IS
1623 SELECT REPORT_LINE_ID, ITEMIZATION_PARENT_ID
1624 FROM ap_expense_report_lines aerl1
1625 WHERE aerl1.report_header_id = p_orig_expense_report_id
1626 AND nvl(itemization_parent_id,-1) = -1 /*Bug# 6131435*/
1627 /* jrautiai ADJ Fix
1628 * We need to move all lines in the itemization when one of them is missing a receipt
1629 */
1630 AND (
1631 ( aerl1.receipt_required_flag = 'Y'
1632 AND nvl(aerl1.receipt_verified_flag,'N') = 'N'
1633 AND nvl(aerl1.policy_shortpay_flag, 'N') = 'N'
1634 )
1635 OR
1636 ( EXISTS (SELECT aerl2.report_header_id
1637 FROM ap_expense_report_lines aerl2
1638 WHERE aerl2.report_header_id = aerl1.report_header_id
1639 AND ((aerl1.credit_card_trx_id IS NULL AND aerl2.credit_card_trx_id is NULL AND aerl1.itemization_parent_id = -1 AND aerl2.itemization_parent_id = aerl1.report_line_id)
1640 OR
1641 (aerl1.credit_card_trx_id IS NOT NULL AND aerl2.credit_card_trx_id = aerl1.credit_card_trx_id)
1642 )
1643 AND aerl2.receipt_required_flag = 'Y'
1644 AND nvl(aerl2.receipt_verified_flag,'N') = 'N'
1645 AND nvl(aerl2.policy_shortpay_flag, 'N') = 'N'
1646 )
1647 )
1648 );
1649
1650
1651 CURSOR ItemizationChildLines(p_report_line_id in number) IS
1652 SELECT REPORT_LINE_ID
1653 FROM AP_EXPENSE_REPORT_LINES
1654 WHERE itemization_parent_id = p_report_line_id
1655 and report_header_id = p_orig_expense_report_id; -- Bug: 6705839, Performance Issue during shortpay
1656
1657 l_OrigReportLineID expLines_report_line_id;
1658 l_itemization_parent_id AP_EXPENSE_REPORT_LINES.itemization_parent_id%type;
1659 i number;
1660
1661 BEGIN
1662
1663 -- Find all lines associated with this report
1664 OPEN ReportLines;
1665
1666 LOOP
1667 FETCH ReportLines into l_OrigReportLineID, l_itemization_parent_id;
1668 EXIT WHEN ReportLines%NOTFOUND;
1669
1670
1671 UPDATE AP_EXPENSE_REPORT_LINES_all
1672 SET report_header_id = p_new_expense_report_id,
1673 mileage_rate_adjusted_flag = C_Unchanged,
1674 last_update_date = sysdate,
1675 creation_date = sysdate
1676 WHERE report_line_id = l_OrigReportLineID;
1677
1678 if (l_itemization_parent_id = -1) then
1679 for i in ItemizationChildLines(l_OrigReportLineID) loop
1680
1681 UPDATE AP_EXPENSE_REPORT_LINES_ALL
1682 SET report_header_id = p_new_expense_report_id,
1683 mileage_rate_adjusted_flag = C_Unchanged,
1684 last_update_date = sysdate,
1685 creation_date = sysdate
1686 WHERE report_line_id = i.report_line_id;
1687
1688 -- Move distribution lines associated with this child line
1689 AP_WEB_DB_EXPDIST_PKG.MoveDistributions(
1690 p_target_report_header_id => p_new_expense_report_id,
1691 p_source_report_line_id => i.report_line_id,
1692 p_target_report_line_id => i.report_line_id);
1693
1694 end loop;
1695 end if;
1696
1697
1698 -- Move distribution lines associated with this line
1699 /* Bug# 6131435 : Parent Line will not be having any distribution lines.
1700 So Distribution lines need not be moved for a parent line */
1701 /* Bug# 6632585 : Distribution lines of non-itemized lines should be moved */
1702 IF (l_itemization_parent_id <> -1 OR l_itemization_parent_id is NULL) THEN
1703 AP_WEB_DB_EXPDIST_PKG.MoveDistributions(
1704 p_target_report_header_id => p_new_expense_report_id,
1705 p_source_report_line_id => l_OrigReportLineID,
1706 p_target_report_line_id => l_OrigReportLineID);
1707 END IF;
1708 END LOOP;
1709
1710 return TRUE;
1711
1712 EXCEPTION -- Block which encapsulates the delete and insert code
1713 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
1714 AP_WEB_DB_UTIL_PKG.RaiseException('AddUnverifiedShortpaidLines','',
1715 'AP_WEB_SAVESUB_DELETE_FAILED',
1716 'V_ReportHeaderID = ' || p_orig_expense_report_id);
1717 APP_EXCEPTION.RAISE_EXCEPTION;
1718 return FALSE;
1719 WHEN OTHERS THEN
1720 IF (SQLCODE = -00054) THEN
1721 -- Tried to obtain lock when deleting on an already locked row
1722 -- Get invoice prefix profile option, and trim if it is too long
1723 -- Get message stating delete failed
1724 AP_WEB_DB_UTIL_PKG.RaiseException('AddUnverifiedShortpaidLines','',
1725 'AP_WEB_SAVESUB_LOCK_FAILED',
1726 'V_ReportHeaderID = ' || p_orig_expense_report_id);
1727 APP_EXCEPTION.RAISE_EXCEPTION;
1728 return FALSE;
1729 END IF;
1730 END AddUnverifiedShortpaidLines;
1731
1732
1733 --------------------------------------------------------------------------------
1734 -- Called from AddCCReportLines and CopyCCItemizationChildLines
1735 -- Name: InsertCCLine
1736 -- Desc: Copies line from p_report_line_id to a new report p_new_report_header_id
1737 -- with p_new_report_line_id, p_itemization_parent_id,
1738 -- p_distribution_line_number, rest all data comes from p_report_line_id
1739 -- Input:
1740 -- p_new_report_header_id - report header id in the new line
1741 -- p_report_line_id - copy from line with p_report_line_id
1742 -- p_itemization_parent_id - itemization_parent_id in the new line
1743 -- p_new_report_line_id - report_line_id in the new line
1744 -- p_distribution_line_number - distribution_line_number in the new line
1745 --------------------------------------------------------------------------------
1746 PROCEDURE InsertCCLine(
1747 p_new_report_header_id IN expLines_headerID,
1748 p_report_line_id IN NUMBER,
1749 p_itemization_parent_id IN NUMBER,
1750 p_new_report_line_id IN NUMBER,
1751 p_distribution_line_number IN NUMBER) IS
1752
1753 l_clearning_ccid NUMBER;
1754 l_org_id AP_EXP_REPORT_DISTS.ORG_ID%TYPE;
1755 l_sequence_num AP_EXP_REPORT_DISTS.SEQUENCE_NUM%TYPE;
1756 l_last_updated_by AP_EXP_REPORT_DISTS.LAST_UPDATED_BY%TYPE;
1757 l_created_by AP_EXP_REPORT_DISTS.CREATED_BY%TYPE;
1758 l_segment1 AP_EXP_REPORT_DISTS.SEGMENT1%TYPE;
1759 l_segment2 AP_EXP_REPORT_DISTS.SEGMENT2%TYPE;
1760 l_segment3 AP_EXP_REPORT_DISTS.SEGMENT3%TYPE;
1761 l_segment4 AP_EXP_REPORT_DISTS.SEGMENT4%TYPE;
1762 l_segment5 AP_EXP_REPORT_DISTS.SEGMENT5%TYPE;
1763 l_segment6 AP_EXP_REPORT_DISTS.SEGMENT6%TYPE;
1764 l_segment7 AP_EXP_REPORT_DISTS.SEGMENT7%TYPE;
1765 l_segment8 AP_EXP_REPORT_DISTS.SEGMENT8%TYPE;
1766 l_segment9 AP_EXP_REPORT_DISTS.SEGMENT9%TYPE;
1767 l_segment10 AP_EXP_REPORT_DISTS.SEGMENT10%TYPE;
1768 l_segment11 AP_EXP_REPORT_DISTS.SEGMENT11%TYPE;
1769 l_segment12 AP_EXP_REPORT_DISTS.SEGMENT12%TYPE;
1770 l_segment13 AP_EXP_REPORT_DISTS.SEGMENT13%TYPE;
1771 l_segment14 AP_EXP_REPORT_DISTS.SEGMENT14%TYPE;
1772 l_segment15 AP_EXP_REPORT_DISTS.SEGMENT15%TYPE;
1773 l_segment16 AP_EXP_REPORT_DISTS.SEGMENT16%TYPE;
1774 l_segment17 AP_EXP_REPORT_DISTS.SEGMENT17%TYPE;
1775 l_segment18 AP_EXP_REPORT_DISTS.SEGMENT18%TYPE;
1776 l_segment19 AP_EXP_REPORT_DISTS.SEGMENT19%TYPE;
1777 l_segment20 AP_EXP_REPORT_DISTS.SEGMENT20%TYPE;
1778 l_segment21 AP_EXP_REPORT_DISTS.SEGMENT21%TYPE;
1779 l_segment22 AP_EXP_REPORT_DISTS.SEGMENT22%TYPE;
1780 l_segment23 AP_EXP_REPORT_DISTS.SEGMENT23%TYPE;
1781 l_segment24 AP_EXP_REPORT_DISTS.SEGMENT24%TYPE;
1782 l_segment25 AP_EXP_REPORT_DISTS.SEGMENT25%TYPE;
1783 l_segment26 AP_EXP_REPORT_DISTS.SEGMENT26%TYPE;
1784 l_segment27 AP_EXP_REPORT_DISTS.SEGMENT27%TYPE;
1785 l_segment28 AP_EXP_REPORT_DISTS.SEGMENT28%TYPE;
1786 l_segment29 AP_EXP_REPORT_DISTS.SEGMENT29%TYPE;
1787 l_segment30 AP_EXP_REPORT_DISTS.SEGMENT30%TYPE;
1788 l_preparer_modified_flag AP_EXP_REPORT_DISTS.PREPARER_MODIFIED_FLAG%TYPE;
1789 l_amount AP_EXP_REPORT_DISTS.AMOUNT%TYPE;
1790 l_cost_center AP_EXP_REPORT_DISTS.COST_CENTER%TYPE;
1791
1792 -- Bug: 6611357, Removed References to Project Information and updated dists
1793 -- table with nulls when creating a .1 report with BothPay
1794
1795
1796 CURSOR copy_dist(p_line_id NUMBER) IS
1797 SELECT DT.ORG_ID,
1798 DT.SEQUENCE_NUM,
1799 DT.LAST_UPDATED_BY,
1800 DT.CREATED_BY,
1801 DT.PREPARER_MODIFIED_FLAG,
1802 DT.AMOUNT,
1803 DT.COST_CENTER
1804 FROM AP_EXP_REPORT_DISTS_ALL DT,
1805 AP_EXPENSE_REPORT_LINES_ALL DL
1806 WHERE DT.REPORT_LINE_ID = p_line_id
1807 AND DT.REPORT_LINE_ID = DL.REPORT_LINE_ID
1808 AND (DL.ITEMIZATION_PARENT_ID IS NULL
1809 OR
1810 DL.ITEMIZATION_PARENT_ID <> -1);
1811
1812 BEGIN
1813
1814 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'Start InsertCCLine');
1815
1816 SELECT AP_WEB_DB_AP_INT_PKG.GetExpenseClearingCCID(credit_card_trx_id)
1817 INTO l_clearning_ccid
1818 FROM ap_expense_report_lines erl
1819 WHERE report_line_id = p_report_line_id;
1820
1821 INSERT INTO ap_expense_report_lines
1822 (report_header_id,
1823 last_update_date,
1824 last_updated_by,
1825 code_combination_id,
1826 item_description,
1827 set_of_books_id,
1828 amount,
1829 currency_code,
1830 exchange_rate_type,
1831 exchange_rate,
1832 exchange_date,
1833 line_type_lookup_code,
1834 last_update_login,
1835 creation_date,
1836 created_by,
1837 stat_amount,
1838 distribution_line_number,
1839 reference_1,
1840 reference_2,
1841 awt_group_id,
1842 org_id,
1843 justification_required_flag,
1844 receipt_required_flag,
1845 receipt_verified_flag,
1846 receipt_missing_flag,
1847 justification,
1848 expense_group,
1849 start_expense_date,
1850 end_expense_date,
1851 receipt_currency_code,
1852 receipt_conversion_rate,
1853 daily_amount,
1854 receipt_currency_amount,
1855 web_parameter_id,
1856 adjustment_reason,
1857 policy_shortpay_flag,
1858 merchant_document_number,
1859 merchant_name,
1860 merchant_reference,
1861 merchant_tax_reg_number,
1862 merchant_taxpayer_id,
1863 country_of_supply,
1864 company_prepaid_invoice_id,
1865 location_id,
1866 itemization_parent_id,
1867 func_currency_amt,
1868 ap_validation_error,
1869 category_code, -- bug 3311471
1870 flex_concatenated,
1871 location,
1872 adjustment_reason_code, -- jrautiai ADJ Fix
1873 submitted_amount, -- jrautiai ADJ Fix
1874 report_line_id, -- LLA
1875 allocation_split_code --Bug#6870253
1876 )
1877 SELECT p_new_report_header_id,
1878 sysdate,
1879 last_updated_by,
1880 l_clearning_ccid,
1881 item_description,
1882 set_of_books_id,
1883 amount,
1884 currency_code,
1885 exchange_rate_type,
1886 exchange_rate,
1887 exchange_date,
1888 'MISCELLANEOUS',
1889 last_update_login,
1890 sysdate,
1891 created_by,
1892 stat_amount,
1893 p_distribution_line_number AS distribution_line_number,
1894 reference_1,
1895 reference_2,
1896 awt_group_id,
1897 erl.org_id,
1898 justification_required_flag,
1899 receipt_required_flag,
1900 receipt_verified_flag,
1901 receipt_missing_flag,
1902 justification,
1903 expense_group,
1904 start_expense_date,
1905 end_expense_date,
1906 receipt_currency_code,
1907 receipt_conversion_rate,
1908 daily_amount,
1909 receipt_currency_amount,
1910 web_parameter_id,
1911 adjustment_reason,
1912 policy_shortpay_flag,
1913 merchant_document_number,
1914 merchant_name,
1915 erl.merchant_reference,
1916 merchant_tax_reg_number,
1917 merchant_taxpayer_id,
1918 country_of_supply,
1919 company_prepaid_invoice_id,
1920 location_id,
1921 p_itemization_parent_id AS itemization_parent_id,
1922 func_currency_amt,
1923 ap_validation_error,
1924 category_code, -- bug 3311471
1925 flex_concatenated,
1926 location,
1927 adjustment_reason_code, -- jrautiai ADJ Fix
1928 submitted_amount, -- jrautiai ADJ Fix
1929 p_new_report_line_id,
1930 allocation_split_code --Bug#6870253
1931 FROM ap_expense_report_lines erl
1932 WHERE report_line_id = p_report_line_id;
1933
1934 -- for bug 5288256: insert a new row in ap_exp_report_dists_all table
1935 SELECT GL.SEGMENT1, GL.SEGMENT2,GL.SEGMENT3,GL.SEGMENT4, GL.SEGMENT5, GL.SEGMENT6,
1936 GL.SEGMENT7, GL.SEGMENT8,GL.SEGMENT9,GL.SEGMENT10,GL.SEGMENT11, GL.SEGMENT12,
1937 GL.SEGMENT13, GL.SEGMENT14, GL.SEGMENT15, GL.SEGMENT16, GL.SEGMENT17, GL.SEGMENT18,
1938 GL.SEGMENT19, GL.SEGMENT20, GL.SEGMENT21, GL.SEGMENT22, GL.SEGMENT23, GL.SEGMENT24,
1939 GL.SEGMENT25, GL.SEGMENT26, GL.SEGMENT27, GL.SEGMENT28, GL.SEGMENT29, GL.SEGMENT30
1940 INTO l_segment1, l_segment2, l_segment3, l_segment4, l_segment5, l_segment6,
1941 l_segment7, l_segment8, l_segment9, l_segment10, l_segment11, l_segment12,
1942 l_segment13, l_segment14, l_segment15, l_segment16, l_segment17, l_segment18,
1943 l_segment19, l_segment20, l_segment21, l_segment22, l_segment23, l_segment24,
1944 l_segment25, l_segment26, l_segment27, l_segment28, l_segment29, l_segment30
1945 FROM GL_CODE_COMBINATIONS GL
1946 WHERE GL.code_combination_id(+) = l_clearning_ccid;
1947
1948 OPEN copy_dist(p_report_line_id);
1949
1950 LOOP
1951 FETCH copy_dist into l_org_id, l_sequence_num, l_last_updated_by, l_created_by,
1952 l_preparer_modified_flag, l_amount, l_cost_center;
1953 EXIT WHEN copy_dist%NOTFOUND;
1954
1955 INSERT INTO AP_EXP_REPORT_DISTS
1956 (
1957 report_header_id,
1958 report_line_id,
1959 report_distribution_id,
1960 org_id,
1961 sequence_num,
1962 last_update_date,
1963 last_updated_by,
1964 creation_date,
1965 created_by,
1966 code_combination_id,
1967 segment1,
1968 segment2,
1969 segment3,
1970 segment4,
1971 segment5,
1972 segment6,
1973 segment7,
1974 segment8,
1975 segment9,
1976 segment10,
1977 segment11,
1978 segment12,
1979 segment13,
1980 segment14,
1981 segment15,
1982 segment16,
1983 segment17,
1984 segment18,
1985 segment19,
1986 segment20,
1987 segment21,
1988 segment22,
1989 segment23,
1990 segment24,
1991 segment25,
1992 segment26,
1993 segment27,
1994 segment28,
1995 segment29,
1996 segment30,
1997 preparer_modified_flag,
1998 amount,
1999 project_id,
2000 task_id,
2001 award_id,
2002 expenditure_organization_id,
2003 cost_center
2004 )
2005 VALUES (
2006 p_new_report_header_id,
2007 p_new_report_line_id,
2008 AP_EXP_REPORT_DISTS_S.NEXTVAL,
2009 l_org_id,
2010 l_sequence_num,
2011 SYSDATE,
2012 l_last_updated_by,
2013 SYSDATE,
2014 l_created_by,
2015 l_clearning_ccid,
2016 l_segment1,
2017 l_segment2,
2018 l_segment3,
2019 l_segment4,
2020 l_segment5,
2021 l_segment6,
2022 l_segment7,
2023 l_segment8,
2024 l_segment9,
2025 l_segment10,
2026 l_segment11,
2027 l_segment12,
2028 l_segment13,
2029 l_segment14,
2030 l_segment15,
2031 l_segment16,
2032 l_segment17,
2033 l_segment18,
2034 l_segment19,
2035 l_segment20,
2036 l_segment21,
2037 l_segment22,
2038 l_segment23,
2039 l_segment24,
2040 l_segment25,
2041 l_segment26,
2042 l_segment27,
2043 l_segment28,
2044 l_segment29,
2045 l_segment30,
2046 l_preparer_modified_flag,
2047 l_amount,
2048 null,
2049 null,
2050 null,
2051 null,
2052 l_cost_center);
2053
2054 END LOOP;
2055 CLOSE copy_dist;
2056
2057
2058 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end InsertCCLine');
2059
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 AP_WEB_DB_UTIL_PKG.RaiseException('InsertCCLine');
2063 APP_EXCEPTION.RAISE_EXCEPTION;
2064 END InsertCCLine;
2065
2066 -------------------------------------------------------------------
2067 -- Name: CopyCCItemizationChildLines
2068 -- Desc: Copys all CCItemization Child Lines of p_source_parent_report_line_id
2069 -- Input:
2070 -- p_source_report_header_id - source report header id
2071 -- p_new_report_header_id - target report header id
2072 -- p_source_parent_report_line_id - source itemization parent report line id
2073 -- p_target_parent_report_line_id - target itemization parent report line id
2074 -------------------------------------------------------------------
2075 PROCEDURE CopyCCItemizationChildLines(
2076 p_source_report_header_id IN expLines_headerID,
2077 p_target_report_header_id IN expLines_headerID,
2078 p_source_parent_report_line_id IN NUMBER,
2079 p_target_parent_report_line_id IN NUMBER) IS
2080
2081 l_NewReportLineID expLines_report_line_id;
2082 i number;
2083
2084 CURSOR ReportLines IS
2085 SELECT REPORT_LINE_ID
2086 FROM AP_EXPENSE_REPORT_LINES
2087 WHERE REPORT_HEADER_ID = P_source_report_header_id
2088 AND ITEMIZATION_PARENT_ID = p_source_parent_report_line_id;
2089
2090 BEGIN
2091
2092 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG',
2093 'start CopyCCItemizationChildLines');
2094
2095 FOR i in ReportLines LOOP
2096
2097 -- Get new ID from sequence
2098 SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
2099 INTO l_NewReportLineID
2100 FROM DUAL;
2101
2102 InsertCCLine(p_new_report_header_id => p_target_report_header_id,
2103 p_report_line_id => i.report_line_id,
2104 p_itemization_parent_id => p_target_parent_report_line_id,
2105 p_new_report_line_id => l_NewReportLineID,
2106 p_distribution_line_number => l_NewReportLineID);
2107
2108 END LOOP;
2109
2110 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG',
2111 'end CopyCCItemizationChildLines');
2112
2113 EXCEPTION
2114 WHEN OTHERS THEN
2115 AP_WEB_DB_UTIL_PKG.RaiseException('CopyCCItemizationChildLines');
2116 APP_EXCEPTION.RAISE_EXCEPTION;
2117 END CopyCCItemizationChildLines;
2118
2119 --------------------------------------------------------------------------------
2120 FUNCTION AddCCReportLines(p_report_header_id IN expLines_headerID,
2121 p_new_report_id IN expLines_headerID
2122 ) RETURN BOOLEAN IS
2123 --------------------------------------------------------------------------------
2124 CURSOR CCReportLines IS
2125 SELECT report_line_id, itemization_parent_id, distribution_line_number
2126 FROM ap_expense_report_lines
2127 WHERE report_header_id = p_report_header_id
2128 AND credit_card_trx_id IS NOT NULL
2129 AND (itemization_parent_id is null
2130 OR
2131 itemization_parent_id = -1);
2132
2133 i number;
2134 l_NewReportLineID expLines_report_line_id;
2135
2136 BEGIN
2137
2138 for i in CCReportLines loop
2139
2140 -- Get new ID from sequence
2141 SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
2142 INTO l_NewReportLineID
2143 FROM DUAL;
2144
2145 InsertCCLine(p_new_report_header_id => p_new_report_id,
2146 p_report_line_id => i.report_line_id,
2147 p_itemization_parent_id => i.itemization_parent_id,
2148 p_new_report_line_id => l_NewReportLineID,
2149 p_distribution_line_number => i.distribution_line_number);
2150
2151 if (i.itemization_parent_id = -1) then
2152 CopyCCItemizationChildLines(
2153 p_report_header_id, --p_source_report_header_id
2154 p_new_report_id, --p_target_report_header_id
2155 i.report_line_id,
2156 l_NewReportLineID);
2157 end if;
2158
2159 end loop;
2160
2161 return TRUE;
2162 EXCEPTION
2163 WHEN NO_DATA_FOUND THEN
2164 return FALSE;
2165 WHEN OTHERS THEN
2166 AP_WEB_DB_UTIL_PKG.RaiseException('AddCCReportLines');
2167 APP_EXCEPTION.RAISE_EXCEPTION;
2168 return FALSE;
2169 END AddCCReportLines;
2170
2171
2172 -----------------------------------------------------------------------------
2173 PROCEDURE DeleteAddonRates(P_ReportID IN NUMBER) IS
2174 --------------------------------------------------------------------------------
2175 l_temp OIE_ADDON_MILEAGE_RATES.ADDON_RATE_TYPE%type;
2176
2177 -- Selects report lines to delete. The actual value being selected does not
2178 -- matter. For some reason the compiler complains when the OF column-name
2179 -- in the FOR UPDATE is missing and NOWAIT is used, so the OF
2180 -- EMPLOYEE_FLAG is used as a place holder.
2181 CURSOR addonRates IS
2182 SELECT ADDON_RATE_TYPE
2183 FROM OIE_ADDON_MILEAGE_RATES addon, AP_EXPENSE_REPORT_LINES el
2184 WHERE (el.REPORT_HEADER_ID = P_ReportID AND
2185 el.REPORT_LINE_ID = addon.REPORT_LINE_ID)
2186 FOR UPDATE OF ADDON_RATE_TYPE NOWAIT;
2187
2188 BEGIN
2189 -- Delete the addon mileage rate from table. An exception will occur if the row
2190 -- locks cannot be attained because of the NOWAIT argument for select.
2191 -- We are guaranteed a lock on the records because of the FOR UPDATE
2192 OPEN addonRates;
2193
2194 LOOP
2195 FETCH addonRates into l_temp;
2196 EXIT WHEN addonRates%NOTFOUND;
2197
2198 -- Delete matching line
2199 DELETE OIE_ADDON_MILEAGE_RATES WHERE CURRENT OF addonRates;
2200 END LOOP;
2201
2202 CLOSE addonRates;
2203
2204
2205 EXCEPTION
2206 WHEN NO_DATA_FOUND THEN
2207 NULL;
2208 WHEN OTHERS THEN
2209 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteAddonRates');
2210 APP_EXCEPTION.RAISE_EXCEPTION;
2211 END DeleteAddonRates;
2212
2213
2214 -----------------------------------------------------------------------------
2215 PROCEDURE DeletePDMDailyBreakup(P_ReportID IN NUMBER) IS
2216 --------------------------------------------------------------------------------
2217 l_temp OIE_PDM_DAILY_BREAKUPS.PDM_DAILY_BREAKUP_ID%type;
2218
2219 -- Selects report lines to delete. The actual value being selected does not
2220 -- matter. For some reason the compiler complains when the OF column-name
2221 -- in the FOR UPDATE is missing and NOWAIT is used, so the OF
2222 -- EMPLOYEE_FLAG is used as a place holder.
2223 CURSOR dailyBreakup IS
2224 SELECT PDM_DAILY_BREAKUP_ID
2225 FROM OIE_PDM_DAILY_BREAKUPS db, AP_EXPENSE_REPORT_LINES el
2226 WHERE (el.REPORT_HEADER_ID = P_ReportID AND
2227 el.REPORT_LINE_ID = db.REPORT_LINE_ID)
2228 FOR UPDATE OF PDM_DESTINATION_ID NOWAIT;
2229
2230 BEGIN
2231 -- Delete the PDM daily breakup from table. An exception will occur if the row
2232 -- locks cannot be attained because of the NOWAIT argument for select.
2233 -- We are guaranteed a lock on the records because of the FOR UPDATE
2234 OPEN dailyBreakup;
2235
2236 LOOP
2237 FETCH dailyBreakup into l_temp;
2238 EXIT WHEN dailyBreakup%NOTFOUND;
2239
2240 -- Delete matching line
2241 DELETE OIE_PDM_DAILY_BREAKUPS WHERE CURRENT OF dailyBreakup;
2242 END LOOP;
2243
2244 CLOSE dailyBreakup;
2245
2246
2247 EXCEPTION
2248 WHEN NO_DATA_FOUND THEN
2249 NULL;
2250 WHEN OTHERS THEN
2251 AP_WEB_DB_UTIL_PKG.RaiseException('DeletePDMDailyBreakup');
2252 APP_EXCEPTION.RAISE_EXCEPTION;
2253 END DeletePDMDailyBreakup;
2254
2255
2256 -----------------------------------------------------------------------------
2257 PROCEDURE DeletePDMDestination(P_ReportID IN NUMBER) IS
2258 --------------------------------------------------------------------------------
2259 l_temp OIE_PDM_DESTINATIONS.PDM_DESTINATION_ID%type;
2260
2261 -- Selects report lines to delete. The actual value being selected does not
2262 -- matter. For some reason the compiler complains when the OF column-name
2263 -- in the FOR UPDATE is missing and NOWAIT is used, so the OF
2264 -- EMPLOYEE_FLAG is used as a place holder.
2265 CURSOR pdmDestination IS
2266 SELECT PDM_DESTINATION_ID
2267 FROM OIE_PDM_DESTINATIONS db, AP_EXPENSE_REPORT_LINES el
2268 WHERE (el.REPORT_HEADER_ID = P_ReportID AND
2269 el.REPORT_LINE_ID = db.REPORT_LINE_ID)
2270 FOR UPDATE OF PDM_DESTINATION_ID NOWAIT;
2271
2272 BEGIN
2273 -- Delete the pdm destination from table. An exception will occur if the row
2274 -- locks cannot be attained because of the NOWAIT argument for select.
2275 -- We are guaranteed a lock on the records because of the FOR UPDATE
2276 OPEN pdmDestination;
2277
2278 LOOP
2279 FETCH pdmDestination into l_temp;
2280 EXIT WHEN pdmDestination%NOTFOUND;
2281
2282 -- Delete matching line
2283 DELETE OIE_PDM_DESTINATIONS WHERE CURRENT OF pdmDestination;
2284 END LOOP;
2285
2286 CLOSE pdmDestination;
2287
2288
2289 EXCEPTION
2290 WHEN NO_DATA_FOUND THEN
2291 NULL;
2292 WHEN OTHERS THEN
2293 AP_WEB_DB_UTIL_PKG.RaiseException('DeletePDMDestination');
2294 APP_EXCEPTION.RAISE_EXCEPTION;
2295 END DeletePDMDestination;
2296
2297 --------------------------------------------------------------------------------
2298 FUNCTION DeleteReportLines(P_ReportID IN expLines_headerID)
2299 RETURN BOOLEAN IS
2300 --------------------------------------------------------------------------------
2301
2302 l_TempReportHeaderID expLines_headerID;
2303 l_TempReportLineID NUMBER;
2304 l_curr_calling_sequence VARCHAR2(100) := 'DeleteReportLines';
2305
2306 -- Selects report lines to delete. The actual value being selected does not
2307 -- matter. For some reason the compiler complains when the OF column-name
2308 -- in the FOR UPDATE is missing and NOWAIT is used, so the OF
2309 -- REPORT_HEADER_ID is used as a place holder.
2310 CURSOR ReportLines IS
2311 SELECT REPORT_HEADER_ID, REPORT_LINE_ID
2312 FROM AP_EXPENSE_REPORT_LINES
2313 WHERE (REPORT_HEADER_ID = P_ReportID)
2314 FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
2315
2316 BEGIN
2317 -- Delete distribution lines associated to this report first
2318 AP_WEB_DB_EXPDIST_PKG.DeleteReportDistributions(P_ReportID);
2319
2320 -- Delete attendees
2321 AP_WEB_DB_EXP_ATTENDEES_PKG.deleteAttendees(P_ReportID);
2322
2323 -- Delete additional mileage rates
2324 DeleteAddonRates(P_ReportID);
2325 -- Delete pdm daily breakup
2326 DeletePDMDailyBreakup(P_ReportID);
2327 -- Delete pdm destination
2328 DeletePDMDestination(P_ReportID);
2329
2330 -- Delete the report lines from table. An exception will occur if the row
2331 -- locks cannot be attained because of the NOWAIT argument for select.
2332 -- We are guaranteed a lock on the records because of the FOR UPDATE
2333 OPEN ReportLines;
2334
2335 LOOP
2336 FETCH ReportLines into l_TempReportHeaderID, l_TempReportLineID;
2337 EXIT WHEN ReportLines%NOTFOUND;
2338
2339 -- Delete matching line
2340 DELETE AP_EXPENSE_REPORT_LINES WHERE CURRENT OF ReportLines;
2341
2342 /* Delete attachments assocated with the line */
2343 fnd_attached_documents2_pkg.delete_attachments(
2344 X_entity_name => 'OIE_LINE_ATTACHMENTS',
2345 X_pk1_value => l_TempReportLineID,
2346 X_delete_document_flag => 'Y'
2347 );
2348
2349 END LOOP;
2350
2351 CLOSE ReportLines;
2352
2353 return TRUE;
2354
2355 EXCEPTION
2356 WHEN NO_DATA_FOUND THEN
2357 return FALSE;
2358 WHEN OTHERS THEN
2359 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteReportLines');
2360 APP_EXCEPTION.RAISE_EXCEPTION;
2361 return FALSE;
2362 END DeleteReportLines;
2363
2364
2365 --------------------------------------------------------------------------------
2366 FUNCTION DeletePersonalLines(p_report_header_id IN expLines_headerID
2367 ) RETURN BOOLEAN IS
2368 --------------------------------------------------------------------------------
2369 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
2370
2371 BEGIN
2372
2373 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
2374 return FALSE;
2375 END IF; /* GetPersonalParamID */
2376
2377
2378 DELETE FROM ap_expense_report_lines
2379 WHERE web_parameter_id = l_personalParameterId
2380 AND report_header_id = p_report_header_id;
2381
2382 return TRUE;
2383 EXCEPTION
2384 WHEN NO_DATA_FOUND THEN
2385 return FALSE;
2386 WHEN OTHERS THEN
2387 AP_WEB_DB_UTIL_PKG.RaiseException('DeletePersonalLines');
2388 APP_EXCEPTION.RAISE_EXCEPTION;
2389 return FALSE;
2390 END DeletePersonalLines;
2391
2392
2393 --------------------------------------------------------------------------------
2394 FUNCTION DeleteCreditReportLines(p_report_header_id IN expLines_headerID) RETURN BOOLEAN IS
2395 --------------------------------------------------------------------------------
2396 BEGIN
2397 DELETE FROM ap_expense_report_lines
2398 WHERE report_header_id = p_report_header_id
2399 AND credit_card_trx_id IS NOT NULL;
2400
2401 return TRUE;
2402 EXCEPTION
2403 WHEN NO_DATA_FOUND THEN
2404 return FALSE;
2405 WHEN OTHERS THEN
2406 AP_WEB_DB_UTIL_PKG.RaiseException('DeleteCreditReportLines');
2407 APP_EXCEPTION.RAISE_EXCEPTION;
2408 return FALSE;
2409 END DeleteCreditReportLines;
2410
2411
2412 --------------------------------------------------------------------------------
2413 FUNCTION SetAWTGroupIDAndJustif(p_report_header_id IN expLines_headerID,
2414 p_sys_allow_awt_flag IN VARCHAR2,
2415 p_ven_allow_awt_flag IN VARCHAR2,
2416 p_ven_awt_group_id IN expLines_awtGroupID
2417 ) RETURN BOOLEAN IS
2418 --------------------------------------------------------------------------------
2419 BEGIN
2420 UPDATE ap_expense_report_lines RL
2421 SET awt_group_id = decode(p_sys_allow_awt_flag, 'Y',
2422 decode(p_ven_allow_awt_flag, 'Y', p_ven_awt_group_id,
2423 null), null),
2424 justification_required_flag = (SELECT nvl(justification_required_flag,'V')
2425 FROM ap_expense_report_params
2426 WHERE parameter_id = RL.web_parameter_id)
2427 WHERE report_header_id = p_report_header_id;
2428
2429 return TRUE;
2430 EXCEPTION
2431 WHEN NO_DATA_FOUND THEN
2432 return FALSE;
2433 WHEN OTHERS THEN
2434 AP_WEB_DB_UTIL_PKG.RaiseException('SetAWTGroupIDAndJustif');
2435 APP_EXCEPTION.RAISE_EXCEPTION;
2436 return FALSE;
2437 END SetAWTGroupIDAndJustif;
2438
2439 -------------------------------------------------------------------
2440 FUNCTION SetReceiptMissing(p_report_header_id IN expLines_headerID,
2441 p_flag IN expLines_receiptMissingFlag)
2442 RETURN BOOLEAN IS
2443 -------------------------------------------------------------------
2444 BEGIN
2445 UPDATE ap_expense_report_lines
2446 SET receipt_missing_flag = p_flag
2447 WHERE report_header_id = p_report_header_id;
2448
2449 return TRUE;
2450 EXCEPTION
2451 WHEN NO_DATA_FOUND THEN
2452 return FALSE;
2453 WHEN OTHERS THEN
2454 AP_WEB_DB_UTIL_PKG.RaiseException('SetReceiptMissing');
2455 APP_EXCEPTION.RAISE_EXCEPTION;
2456 return FALSE;
2457 END SetReceiptMissing;
2458
2459
2460 --------------------------------------------------------------------------------
2461 FUNCTION SetReceiptRequired( p_report_header_id IN expLines_headerID,
2462 p_required_flag IN expLines_receiptReqdFlag
2463 ) RETURN BOOLEAN IS
2464 --------------------------------------------------------------------------------
2465 BEGIN
2466 UPDATE ap_expense_report_lines
2467 SET receipt_required_flag = p_required_flag
2468 WHERE nvl(receipt_missing_flag, 'N') = 'Y'
2469 AND report_header_id = p_report_header_id;
2470
2471 return TRUE;
2472 EXCEPTION
2473 WHEN NO_DATA_FOUND THEN
2474 return FALSE;
2475 WHEN OTHERS THEN
2476 AP_WEB_DB_UTIL_PKG.RaiseException('SetReceiptRequired');
2477 APP_EXCEPTION.RAISE_EXCEPTION;
2478 return FALSE;
2479 END SetReceiptRequired;
2480
2481 --------------------------------------------------------------------------------
2482 FUNCTION GetCCardLineCursor(p_expReportHeaderId IN expLines_headerID,
2483 p_cCardLineCursor OUT NOCOPY CCardLinesCursor)
2484 RETURN BOOLEAN IS
2485 --------------------------------------------------------------------------------
2486 l_parameterId number;
2487 BEGIN
2488 if (AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_parameterId) <> TRUE) then
2489 APP_EXCEPTION.RAISE_EXCEPTION;
2490 end if;
2491
2492 -- Bug 3253775: Included personal credit card transactions in the cursor and
2493 -- negated the personal transaction amt to be deducted later from invoice total.
2494
2495 OPEN p_cCardLineCursor FOR
2496 SELECT decode(erl.web_parameter_id,l_parameterId,-erl.amount,erl.amount),
2497 cc.company_prepaid_invoice_id, cc.card_program_id,
2498 decode(erl.web_parameter_id,l_parameterId, 'PERSONAL', 'BUSINESS'),
2499 erl.org_id,
2500 nvl(cc.transaction_date,sysdate),
2501 erh.employee_id
2502 FROM ap_expense_report_lines erl,
2503 ap_credit_card_trxns cc,
2504 ap_expense_report_headers erh
2505 WHERE erl.report_header_id = erh.report_header_id
2506 AND nvl(erl.itemization_parent_id,0) <> -1 /* Itemization Project */
2507 AND cc.trx_id = erl.credit_card_trx_id -- is a credit card transaction
2508 AND cc.payment_due_from_code in ('BOTH','COMPANY') -- Both Pay split project
2509 AND erh.report_header_id = p_expReportHeaderId
2510 AND erh.source = 'SelfService';
2511
2512 return TRUE;
2513
2514 EXCEPTION
2515 WHEN NO_DATA_FOUND THEN
2516 return FALSE;
2517 WHEN OTHERS THEN
2518 AP_WEB_DB_UTIL_PKG.RaiseException('GetCCardLineCursor');
2519 APP_EXCEPTION.RAISE_EXCEPTION;
2520 return FALSE;
2521 END GetCCardLineCursor;
2522
2523 --------------------------------------------------------------------------------
2524 PROCEDURE GetCCPrepaidAdjustedInvAmt(p_expReportHeaderId IN NUMBER,
2525 p_invAmt IN OUT NOCOPY VARCHAR2) IS
2526 --------------------------------------------------------------------------------
2527 l_prepaid_amt expLines_amount;
2528 l_parameterId number;
2529 l_cCardLineCursor AP_WEB_DB_EXPLINE_PKG.CCTrxnCursor;
2530 l_invoiceAmt AP_WEB_DB_AP_INT_PKG.invLines_amount := 0;
2531 l_baseAmt AP_WEB_DB_AP_INT_PKG.invAll_baseAmount;
2532 l_totalCCardAmt NUMBER := 0;
2533 l_prepaidInvId AP_WEB_DB_CCARD_PKG.ccTrxn_companyPrepaidInvID;
2534 l_cCardLineAmt AP_WEB_DB_EXPLINE_PKG.expLines_amount;
2535 l_cardProgramID NUMBER;
2536 l_Personal VARCHAR2(10);
2537 l_debugInfo VARCHAR2(2000);
2538 l_transaction_date DATE;
2539 l_employee_id NUMBER;
2540 l_org_id NUMBER;
2541
2542 BEGIN
2543
2544 /* Bug 3649748 : Calling GetCCardLineCursor to get the COMPANY Pay
2545 * lines
2546 */
2547 /*
2548 if (AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_parameterId) <> TRUE) then
2549 APP_EXCEPTION.RAISE_EXCEPTION;
2550 end if;
2551
2552 SELECT sum(erl.amount)
2553 INTO l_prepaid_amt
2554 FROM ap_expense_report_lines erl,
2555 ap_credit_card_trxns cc
2556 WHERE erl.report_header_id = p_expReportHeaderId
2557 AND cc.trx_id = erl.credit_card_trx_id -- is a credit card transaction
2558 AND (cc.category is null OR cc.category <> 'PERSONAL') -- not a personal expense
2559 AND erl.web_parameter_id <> l_parameterId -- Not personal itemized line
2560 AND cc.company_prepaid_invoice_id IS NOT null; -- company prepaid
2561
2562 p_invAmt := to_char(to_number(p_invAmt) - nvl(l_prepaid_amt,0));
2563 */
2564 ------------------------------------------------------------------
2565 l_debugInfo := 'Get the credit card report line cursor.';
2566 ----------------------------------------------------------------
2567 IF (AP_WEB_DB_EXPLINE_PKG.GetCCardLineCursor(
2568 p_expReportHeaderId,
2569 l_cCardLineCursor) = TRUE) THEN
2570
2571
2572 LOOP
2573 FETCH l_cCardLineCursor INTO
2574 l_cCardLineAmt, l_prepaidInvId, l_cardProgramID, l_Personal, l_org_id,
2575 l_transaction_date, l_employee_id;
2576 EXIT WHEN l_cCardLineCursor%NOTFOUND;
2577
2578 l_totalCCardAmt := l_totalCCardAmt + l_cCardLineAmt;
2579
2580 END LOOP;
2581
2582 CLOSE l_cCardLineCursor;
2583
2584 IF (l_totalCCardAmt <> 0) then
2585 -------------------------------------------------------------------
2586 l_debugInfo := 'Update the amount.';
2587 -------------------------------------------------------------------
2588 p_invAmt := p_invAmt - l_totalCCardAmt;
2589 END IF;
2590
2591
2592 END IF;
2593
2594
2595 EXCEPTION
2596 WHEN OTHERS THEN
2597 AP_WEB_DB_UTIL_PKG.RaiseException('GetCCPrepaidAdjustedInvAmt');
2598 APP_EXCEPTION.RAISE_EXCEPTION;
2599 END GetCCPrepaidAdjustedInvAmt;
2600
2601 -------------------------------------------------------------------
2602 FUNCTION GetReceiptsMissingFlag( p_report_header_id IN expLines_headerID,
2603 p_missing_receipts_flag OUT NOCOPY VARCHAR2
2604 ) RETURN BOOLEAN IS
2605 -------------------------------------------------------------------
2606
2607 BEGIN
2608 select receipt_missing_flag
2609 into p_missing_receipts_flag
2610 from ap_expense_report_lines
2611 where report_header_id = p_report_header_id
2612 and receipt_missing_flag = 'Y'
2613 and rownum = 1;
2614
2615 RETURN TRUE;
2616
2617 EXCEPTION
2618 WHEN NO_DATA_FOUND THEN
2619 p_missing_receipts_flag := 'N';
2620 return FALSE;
2621
2622 WHEN OTHERS THEN
2623 AP_WEB_DB_UTIL_PKG.RaiseException('GetReceiptsMissingFlag');
2624 APP_EXCEPTION.RAISE_EXCEPTION;
2625 return FALSE;
2626
2627 END GetReceiptsMissingFlag;
2628 --------------------------------------------------------------------------------
2629 FUNCTION GetExpMileageLinesCursor(
2630 p_report_header_id IN expLines_headerID,
2631 p_mileage_lines_cursor OUT NOCOPY ExpLinesCursor)
2632 RETURN BOOLEAN IS
2633 --------------------------------------------------------------------------------
2634 BEGIN
2635 -- get all mileage lines
2636 OPEN p_mileage_lines_cursor FOR
2637 SELECT XL.start_expense_date,
2638 XL.end_expense_date,
2639 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
2640 XL.start_expense_date)+1),4),
2641 XL.distribution_line_number,
2642 XP.company_policy_id,
2643 XL.avg_mileage_rate,
2644 XL.distance_unit_code,
2645 nvl(XL.trip_distance,0),
2646 nvl(XL.daily_distance,0),
2647 XP.category_code,
2648 XL.currency_code,
2649 XL.amount,
2650 XL.number_people,
2651 XL.web_parameter_id,
2652 XL.rate_per_passenger,
2653 XL.attribute1,
2654 XL.attribute2,
2655 XL.attribute3,
2656 XL.attribute4,
2657 XL.attribute5,
2658 XL.attribute6,
2659 XL.attribute7,
2660 XL.attribute8,
2661 XL.attribute9,
2662 XL.attribute10,
2663 XL.attribute11,
2664 XL.attribute12,
2665 XL.attribute13,
2666 XL.attribute14,
2667 XL.attribute15,
2668 XL.report_line_id
2669 FROM ap_expense_report_params XP,
2670 ap_expense_report_lines XL,
2671 ap_lookup_codes LC,
2672 PA_PROJECTS_ALL PAP,
2673 PA_TASKS PAT,
2674 GMS_AWARDS GMS
2675 WHERE XL.report_header_id = p_report_header_id
2676 AND XL.project_id is not null
2677 AND XL.task_id is not null
2678 AND XL.web_parameter_id = XP.parameter_id
2679 AND XL.line_type_lookup_code = LC.lookup_code
2680 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
2681 AND XL.project_id = PAP.project_id
2682 AND XL.project_id = PAT.project_id
2683 AND XL.task_id = PAT.task_id
2684 AND XL.award_id = GMS.award_id(+)
2685 UNION ALL
2686 SELECT XL.start_expense_date,
2687 XL.end_expense_date,
2688 LPAD(to_char((nvl(XL.end_expense_date,XL.start_expense_date) -
2689 XL.start_expense_date)+1),4),
2690 XL.distribution_line_number,
2691 XP.company_policy_id,
2692 XL.avg_mileage_rate,
2693 XL.distance_unit_code,
2694 nvl(XL.trip_distance,0),
2695 nvl(XL.daily_distance,0),
2696 XP.category_code,
2697 XL.currency_code,
2698 XL.amount,
2699 XL.number_people,
2700 XL.web_parameter_id,
2701 XL.rate_per_passenger,
2702 XL.attribute1,
2703 XL.attribute2,
2704 XL.attribute3,
2705 XL.attribute4,
2706 XL.attribute5,
2707 XL.attribute6,
2708 XL.attribute7,
2709 XL.attribute8,
2710 XL.attribute9,
2711 XL.attribute10,
2712 XL.attribute11,
2713 XL.attribute12,
2714 XL.attribute13,
2715 XL.attribute14,
2716 XL.attribute15,
2717 XL.report_line_id
2718 FROM ap_expense_report_params XP,
2719 ap_expense_report_lines XL,
2720 ap_lookup_codes LC
2721 WHERE XL.report_header_id = p_report_header_id
2722 AND XL.web_parameter_id = XP.parameter_id
2723 AND XL.line_type_lookup_code = LC.lookup_code
2724 AND LC.lookup_type = 'INVOICE DISTRIBUTION TYPE'
2725 AND XL.project_id is null
2726 AND XL.task_id is null
2727 AND XL.award_id is null
2728 ORDER BY distribution_line_number;
2729
2730 return TRUE;
2731 EXCEPTION
2732 WHEN NO_DATA_FOUND THEN
2733 return FALSE;
2734 WHEN OTHERS THEN
2735 AP_WEB_DB_UTIL_PKG.RaiseException('GetExpMileageLinesCursor');
2736 APP_EXCEPTION.RAISE_EXCEPTION;
2737 return FALSE;
2738 END GetExpMileageLinesCursor;
2739
2740 --------------------------------------------------------------------------------
2741 PROCEDURE updateMileageExpLine(
2742 p_avg_mileage_rate IN expLines_avg_mileage_rate,
2743 p_report_header_id IN expLines_headerID,
2744 p_distribution_line_number IN expLines_distLineNum,
2745 p_new_dist_line_number IN expLines_distLineNum,
2746 p_amount IN expLines_amount,
2747 p_trip_distance IN expLines_trip_distance,
2748 p_daily_distance IN NUMBER,
2749 p_daily_amount IN expLines_dailyAmount,
2750 p_receipt_currency_amount IN NUMBER,
2751 p_status_code IN expLines_mrate_adj_flag
2752 )IS
2753 --------------------------------------------------------------------------------
2754 l_debug_info VARCHAR2(200);
2755 BEGIN
2756
2757 UPDATE ap_expense_report_lines
2758 SET avg_mileage_rate = p_avg_mileage_rate,
2759 amount = p_amount,
2760 distribution_line_number = p_new_dist_line_number,
2761 trip_distance = p_trip_distance,
2762 daily_distance = p_daily_distance,
2763 daily_amount = p_daily_amount,
2764 receipt_currency_amount = p_receipt_currency_amount,
2765 mileage_rate_adjusted_flag = p_status_code
2766 WHERE report_header_id = p_report_header_id
2767 AND distribution_line_number = p_distribution_line_number;
2768
2769
2770 EXCEPTION
2771 WHEN NO_DATA_FOUND THEN
2772 AP_WEB_DB_UTIL_PKG.RaiseException('updateMileageExpLine: no data found');
2773 APP_EXCEPTION.RAISE_EXCEPTION;
2774 WHEN OTHERS THEN
2775 AP_WEB_DB_UTIL_PKG.RaiseException('updateMileageExpLine');
2776 APP_EXCEPTION.RAISE_EXCEPTION;
2777 END updateMileageExpLine;
2778
2779
2780 --------------------------------------------------------------------------------
2781 PROCEDURE updateExpenseMileageLines(
2782 p_mileage_line_array IN Mileage_Line_Array,
2783 p_bUpdatedHeader OUT NOCOPY BOOLEAN
2784 ) IS
2785 --------------------------------------------------------------------------------
2786 i NUMBER := 1;
2787 l_amount NUMBER;
2788 l_tab_idx NUMBER;
2789 l_report_header_id expLines_headerID := p_mileage_line_array(i).report_header_id;
2790 l_new_report_line_id AP_WEB_DB_EXPLINE_PKG.expLines_report_line_id;
2791
2792 BEGIN
2793
2794 l_tab_idx := p_mileage_line_array.FIRST;
2795 p_bUpdatedHeader := FALSE;
2796
2797 LOOP
2798 EXIT WHEN l_tab_idx is null;
2799
2800 IF (p_mileage_line_array(i).status <> C_Unchanged) THEN
2801 IF (p_mileage_line_array(i).status = C_New) THEN
2802 -- New: add to database
2803
2804 AddMileageExpLine(
2805
2806 p_new_distribution_line_number => p_mileage_line_array(i).new_dist_line_number,
2807 p_new_trip_distance => p_mileage_line_array(i).trip_distance,
2808 p_new_daily_distance => p_mileage_line_array(i).daily_distance,
2809 p_new_amount => p_mileage_line_array(i).amount,
2810 p_new_avg_mileage_rate => p_mileage_line_array(i).avg_mileage_rate,
2811 p_orig_expense_report_id => p_mileage_line_array(i).report_header_id,
2812 p_orig_dist_line_number => p_mileage_line_array(i).copy_From,
2813 p_daily_amount => p_mileage_line_array(i).daily_amount,
2814 p_receipt_currency_amount => p_mileage_line_array(i).amount,
2815 x_report_line_id => l_new_report_line_id);
2816
2817 AP_WEB_DB_EXPDIST_PKG.AddDistributionLine(
2818 p_report_line_id => l_new_report_line_id);
2819
2820 ELSIF (p_mileage_line_array(i).status = C_Modified
2821 OR p_mileage_line_array(i).status = C_Split) THEN
2822
2823 -- Modified: update database
2824 updateMileageExpLine(
2825 p_avg_mileage_rate => p_mileage_line_array(i).avg_mileage_rate,
2826 p_report_header_id => p_mileage_line_array(i).report_header_id,
2827 p_distribution_line_number => p_mileage_line_array(i).orig_dist_line_number,
2828 p_new_dist_line_number => p_mileage_line_array(i).new_dist_line_number,
2829 p_amount => p_mileage_line_array(i).amount,
2830 p_trip_distance => p_mileage_line_array(i).trip_distance,
2831 p_daily_distance => p_mileage_line_array(i).daily_distance,
2832 p_daily_amount => p_mileage_line_array(i).daily_amount,
2833 p_receipt_currency_amount => p_mileage_line_array(i).receipt_currency_amount,
2834 p_status_code => p_mileage_line_array(i).status);
2835
2836 END IF;
2837 p_bUpdatedHeader := TRUE;
2838
2839 END IF;
2840 i := i + 1;
2841 l_tab_idx := p_mileage_line_array.NEXT(l_tab_idx);
2842 END LOOP;
2843
2844
2845 EXCEPTION
2846 WHEN OTHERS THEN
2847 AP_WEB_DB_UTIL_PKG.RaiseException('updateExpenseMileageLines');
2848 APP_EXCEPTION.RAISE_EXCEPTION;
2849 END updateExpenseMileageLines;
2850
2851
2852 --------------------------------------------------------------------------------
2853 PROCEDURE AddMileageExpLine(
2854 p_new_distribution_line_number IN NUMBER,
2855 p_new_trip_distance IN NUMBER,
2856 p_new_daily_distance IN NUMBER,
2857 p_new_amount IN NUMBER,
2858 p_new_avg_mileage_rate IN NUMBER,
2859 p_orig_expense_report_id IN expLines_headerID,
2860 p_orig_dist_line_number IN expLines_distLineNum,
2861 p_daily_amount IN NUMBER,
2862 p_receipt_currency_amount IN NUMBER,
2863 x_report_line_id OUT NOCOPY NUMBER
2864 ) IS
2865 --------------------------------------------------------------------------------
2866 l_debug_info varchar2(240);
2867 l_report_line_id NUMBER;
2868 BEGIN
2869
2870 l_debug_info := 'start of AddMileageExpLine';
2871
2872 SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
2873 INTO l_report_line_id
2874 FROM DUAL;
2875
2876
2877 INSERT INTO ap_expense_report_lines
2878 (REPORT_HEADER_ID,
2879 LAST_UPDATE_DATE,
2880 LAST_UPDATED_BY,
2881 CODE_COMBINATION_ID,
2882 ITEM_DESCRIPTION,
2883 SET_OF_BOOKS_ID,
2884 AMOUNT,
2885 ATTRIBUTE_CATEGORY,
2886 ATTRIBUTE1,
2887 ATTRIBUTE2,
2888 ATTRIBUTE3,
2889 ATTRIBUTE4,
2890 ATTRIBUTE5,
2891 ATTRIBUTE6,
2892 ATTRIBUTE7,
2893 ATTRIBUTE8,
2894 ATTRIBUTE9,
2895 ATTRIBUTE10,
2896 ATTRIBUTE11,
2897 ATTRIBUTE12,
2898 ATTRIBUTE13,
2899 ATTRIBUTE14,
2900 ATTRIBUTE15,
2901 CURRENCY_CODE,
2902 EXCHANGE_RATE_TYPE,
2903 EXCHANGE_RATE,
2904 EXCHANGE_DATE,
2905 VAT_CODE,
2906 LINE_TYPE_LOOKUP_CODE,
2907 LAST_UPDATE_LOGIN,
2908 CREATION_DATE,
2909 CREATED_BY,
2910 STAT_AMOUNT,
2911 PROJECT_ACCOUNTING_CONTEXT,
2912 EXPENDITURE_TYPE,
2913 EXPENDITURE_ITEM_DATE,
2914 PA_QUANTITY,
2915 DISTRIBUTION_LINE_NUMBER,
2916 REFERENCE_1,
2917 REFERENCE_2,
2918 AWT_GROUP_ID,
2919 ORG_ID,
2920 RECEIPT_VERIFIED_FLAG,
2921 JUSTIFICATION_REQUIRED_FLAG,
2922 RECEIPT_REQUIRED_FLAG,
2923 RECEIPT_MISSING_FLAG,
2924 JUSTIFICATION,
2925 EXPENSE_GROUP,
2926 START_EXPENSE_DATE,
2927 END_EXPENSE_DATE,
2928 RECEIPT_CURRENCY_CODE,
2929 RECEIPT_CONVERSION_RATE,
2930 DAILY_AMOUNT,
2931 RECEIPT_CURRENCY_AMOUNT,
2932 WEB_PARAMETER_ID,
2933 GLOBAL_ATTRIBUTE_CATEGORY,
2934 GLOBAL_ATTRIBUTE1,
2935 GLOBAL_ATTRIBUTE2,
2936 GLOBAL_ATTRIBUTE3,
2937 GLOBAL_ATTRIBUTE4,
2938 GLOBAL_ATTRIBUTE5,
2939 GLOBAL_ATTRIBUTE6,
2940 GLOBAL_ATTRIBUTE7,
2941 GLOBAL_ATTRIBUTE8,
2942 GLOBAL_ATTRIBUTE9,
2943 GLOBAL_ATTRIBUTE10,
2944 GLOBAL_ATTRIBUTE11,
2945 GLOBAL_ATTRIBUTE12,
2946 GLOBAL_ATTRIBUTE13,
2947 GLOBAL_ATTRIBUTE14,
2948 GLOBAL_ATTRIBUTE15,
2949 GLOBAL_ATTRIBUTE16,
2950 GLOBAL_ATTRIBUTE17,
2951 GLOBAL_ATTRIBUTE18,
2952 GLOBAL_ATTRIBUTE19,
2953 GLOBAL_ATTRIBUTE20,
2954 ADJUSTMENT_REASON,
2955 POLICY_SHORTPAY_FLAG,
2956 MERCHANT_DOCUMENT_NUMBER,
2957 MERCHANT_NAME,
2958 MERCHANT_REFERENCE,
2959 MERCHANT_TAX_REG_NUMBER,
2960 MERCHANT_TAXPAYER_ID,
2961 COUNTRY_OF_SUPPLY,
2962 TAX_CODE_OVERRIDE_FLAG,
2963 TAX_CODE_ID,
2964 CREDIT_CARD_TRX_ID,
2965 ALLOCATION_REASON,
2966 ALLOCATION_SPLIT_CODE,
2967 PROJECT_NAME,
2968 TASK_NAME,
2969 COMPANY_PREPAID_INVOICE_ID,
2970 PROJECT_NUMBER,
2971 TASK_NUMBER,
2972 PA_INTERFACED_FLAG,
2973 AWARD_NUMBER,
2974 VEHICLE_CATEGORY_CODE,
2975 VEHICLE_TYPE,
2976 FUEL_TYPE,
2977 NUMBER_PEOPLE,
2978 DAILY_DISTANCE,
2979 AVG_MILEAGE_RATE,
2980 DESTINATION_FROM,
2981 DESTINATION_TO,
2982 TRIP_DISTANCE,
2983 DISTANCE_UNIT_CODE,
2984 LICENSE_PLATE_NUMBER,
2985 LOCATION_ID,
2986 NUM_PDM_DAYS1,
2987 NUM_PDM_DAYS2,
2988 NUM_PDM_DAYS3,
2989 PER_DIEM_RATE1,
2990 PER_DIEM_RATE2,
2991 PER_DIEM_RATE3,
2992 DEDUCTION_ADDITION_AMT1,
2993 DEDUCTION_ADDITION_AMT2,
2994 DEDUCTION_ADDITION_AMT3,
2995 NUM_FREE_BREAKFASTS1,
2996 NUM_FREE_LUNCHES1,
2997 NUM_FREE_DINNERS1,
2998 NUM_FREE_ACCOMMODATIONS1,
2999 NUM_FREE_BREAKFASTS2,
3000 NUM_FREE_LUNCHES2,
3001 NUM_FREE_DINNERS2,
3002 NUM_FREE_ACCOMMODATIONS2,
3003 NUM_FREE_BREAKFASTS3,
3004 NUM_FREE_LUNCHES3,
3005 NUM_FREE_DINNERS3,
3006 NUM_FREE_ACCOMMODATIONS3,
3007 ATTENDEES,
3008 NUMBER_ATTENDEES,
3009 TICKET_CLASS_CODE,
3010 TRAVEL_TYPE,
3011 TICKET_NUMBER,
3012 FLIGHT_NUMBER,
3013 LOCATION_TO_ID,
3014 ITEMIZATION_PARENT_ID,
3015 FLEX_CONCATENATED,
3016 MILEAGE_RATE_ADJUSTED_FLAG,
3017 FUNC_CURRENCY_AMT,
3018 CATEGORY_CODE,
3019 ADJUSTMENT_REASON_CODE,
3020 LOCATION,
3021 AP_VALIDATION_ERROR,
3022 REPORT_LINE_ID
3023 )
3024 SELECT
3025 REPORT_HEADER_ID,
3026 SYSDATE,
3027 LAST_UPDATED_BY,
3028 CODE_COMBINATION_ID,
3029 ITEM_DESCRIPTION,
3030 SET_OF_BOOKS_ID,
3031 p_new_amount,
3032 ATTRIBUTE_CATEGORY,
3033 ATTRIBUTE1,
3034 ATTRIBUTE2,
3035 ATTRIBUTE3,
3036 ATTRIBUTE4,
3037 ATTRIBUTE5,
3038 ATTRIBUTE6,
3039 ATTRIBUTE7,
3040 ATTRIBUTE8,
3041 ATTRIBUTE9,
3042 ATTRIBUTE10,
3043 ATTRIBUTE11,
3044 ATTRIBUTE12,
3045 ATTRIBUTE13,
3046 ATTRIBUTE14,
3047 ATTRIBUTE15,
3048 CURRENCY_CODE,
3049 EXCHANGE_RATE_TYPE,
3050 EXCHANGE_RATE,
3051 EXCHANGE_DATE,
3052 VAT_CODE,
3053 LINE_TYPE_LOOKUP_CODE,
3054 LAST_UPDATE_LOGIN,
3055 SYSDATE,
3056 CREATED_BY,
3057 STAT_AMOUNT,
3058 PROJECT_ACCOUNTING_CONTEXT,
3059 EXPENDITURE_TYPE,
3060 EXPENDITURE_ITEM_DATE,
3061 PA_QUANTITY,
3062 p_new_distribution_line_number,
3063 REFERENCE_1,
3064 REFERENCE_2,
3065 AWT_GROUP_ID,
3066 ORG_ID,
3067 RECEIPT_VERIFIED_FLAG,
3068 JUSTIFICATION_REQUIRED_FLAG,
3069 RECEIPT_REQUIRED_FLAG,
3070 RECEIPT_MISSING_FLAG,
3071 JUSTIFICATION,
3072 EXPENSE_GROUP,
3073 START_EXPENSE_DATE,
3074 END_EXPENSE_DATE,
3075 RECEIPT_CURRENCY_CODE,
3076 RECEIPT_CONVERSION_RATE,
3077 p_daily_amount,
3078 p_receipt_currency_amount,
3079 WEB_PARAMETER_ID,
3080 GLOBAL_ATTRIBUTE_CATEGORY,
3081 GLOBAL_ATTRIBUTE1,
3082 GLOBAL_ATTRIBUTE2,
3083 GLOBAL_ATTRIBUTE3,
3084 GLOBAL_ATTRIBUTE4,
3085 GLOBAL_ATTRIBUTE5,
3086 GLOBAL_ATTRIBUTE6,
3087 GLOBAL_ATTRIBUTE7,
3088 GLOBAL_ATTRIBUTE8,
3089 GLOBAL_ATTRIBUTE9,
3090 GLOBAL_ATTRIBUTE10,
3091 GLOBAL_ATTRIBUTE11,
3092 GLOBAL_ATTRIBUTE12,
3093 GLOBAL_ATTRIBUTE13,
3094 GLOBAL_ATTRIBUTE14,
3095 GLOBAL_ATTRIBUTE15,
3096 GLOBAL_ATTRIBUTE16,
3097 GLOBAL_ATTRIBUTE17,
3098 GLOBAL_ATTRIBUTE18,
3099 GLOBAL_ATTRIBUTE19,
3100 GLOBAL_ATTRIBUTE20,
3101 ADJUSTMENT_REASON,
3102 POLICY_SHORTPAY_FLAG,
3103 MERCHANT_DOCUMENT_NUMBER,
3104 MERCHANT_NAME,
3105 MERCHANT_REFERENCE,
3106 MERCHANT_TAX_REG_NUMBER,
3107 MERCHANT_TAXPAYER_ID,
3108 COUNTRY_OF_SUPPLY,
3109 TAX_CODE_OVERRIDE_FLAG,
3110 TAX_CODE_ID,
3111 CREDIT_CARD_TRX_ID,
3112 ALLOCATION_REASON,
3113 ALLOCATION_SPLIT_CODE,
3114 PROJECT_NAME,
3115 TASK_NAME,
3116 COMPANY_PREPAID_INVOICE_ID,
3117 PROJECT_NUMBER,
3118 TASK_NUMBER,
3119 PA_INTERFACED_FLAG,
3120 AWARD_NUMBER,
3121 VEHICLE_CATEGORY_CODE,
3122 VEHICLE_TYPE,
3123 FUEL_TYPE,
3124 NUMBER_PEOPLE,
3125 p_new_daily_distance,
3126 p_new_avg_mileage_rate,
3127 DESTINATION_FROM,
3128 DESTINATION_TO,
3129 p_new_trip_distance,
3130 DISTANCE_UNIT_CODE,
3131 LICENSE_PLATE_NUMBER,
3132 LOCATION_ID,
3133 NUM_PDM_DAYS1,
3134 NUM_PDM_DAYS2,
3135 NUM_PDM_DAYS3,
3136 PER_DIEM_RATE1,
3137 PER_DIEM_RATE2,
3138 PER_DIEM_RATE3,
3139 DEDUCTION_ADDITION_AMT1,
3140 DEDUCTION_ADDITION_AMT2,
3141 DEDUCTION_ADDITION_AMT3,
3142 NUM_FREE_BREAKFASTS1,
3143 NUM_FREE_LUNCHES1,
3144 NUM_FREE_DINNERS1,
3145 NUM_FREE_ACCOMMODATIONS1,
3146 NUM_FREE_BREAKFASTS2,
3147 NUM_FREE_LUNCHES2,
3148 NUM_FREE_DINNERS2,
3149 NUM_FREE_ACCOMMODATIONS2,
3150 NUM_FREE_BREAKFASTS3,
3151 NUM_FREE_LUNCHES3,
3152 NUM_FREE_DINNERS3,
3153 NUM_FREE_ACCOMMODATIONS3,
3154 ATTENDEES,
3155 NUMBER_ATTENDEES,
3156 TICKET_CLASS_CODE,
3157 TRAVEL_TYPE,
3158 TICKET_NUMBER,
3159 FLIGHT_NUMBER,
3160 LOCATION_TO_ID,
3161 ITEMIZATION_PARENT_ID,
3162 FLEX_CONCATENATED,
3163 AP_WEB_DB_EXPLINE_PKG.C_New,
3164 FUNC_CURRENCY_AMT,
3165 CATEGORY_CODE,
3166 ADJUSTMENT_REASON_CODE,
3167 LOCATION,
3168 AP_VALIDATION_ERROR,
3169 l_report_line_id
3170 FROM ap_expense_report_lines
3171 WHERE report_header_id = p_orig_expense_report_id
3172 AND distribution_line_number = p_orig_dist_line_number;
3173
3174 ---------------------------------------------------------------
3175 -- Assign the out parameter only when the insert is successful.
3176 ---------------------------------------------------------------
3177 x_report_line_id := l_report_line_id;
3178
3179 -- copy additional mileage rate
3180 INSERT INTO OIE_ADDON_MILEAGE_RATES(REPORT_LINE_ID,
3181 ADDON_RATE_TYPE,
3182 MILEAGE_RATE,
3183 MILEAGE_AMOUNT,
3184 LAST_UPDATE_DATE,
3185 LAST_UPDATED_BY,
3186 LAST_UPDATE_LOGIN,
3187 CREATED_BY,
3188 CREATION_DATE)
3189 SELECT X_REPORT_LINE_ID,
3190 ADDON_RATE_TYPE,
3191 MILEAGE_RATE,
3192 MILEAGE_AMOUNT,
3193 SYSDATE,
3194 LAST_UPDATED_BY,
3195 LAST_UPDATE_LOGIN,
3196 CREATED_BY,
3197 SYSDATE
3198 FROM OIE_ADDON_MILEAGE_RATES
3199 WHERE REPORT_LINE_ID = (SELECT REPORT_LINE_ID
3200 FROM AP_EXPENSE_REPORT_LINES
3201 WHERE REPORT_HEADER_ID = P_ORIG_EXPENSE_REPORT_ID
3202 AND DISTRIBUTION_LINE_NUMBER = P_ORIG_DIST_LINE_NUMBER
3203 AND ROWNUM = 1);
3204
3205 EXCEPTION
3206 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
3207 AP_WEB_DB_UTIL_PKG.RaiseException('AddMileageExpLine','',
3208 'AP_WEB_SAVESUB_DELETE_FAILED');
3209 APP_EXCEPTION.RAISE_EXCEPTION;
3210 WHEN OTHERS THEN
3211 AP_WEB_DB_UTIL_PKG.RaiseException('AddMileageExpLine',
3212 l_debug_Info);
3213 APP_EXCEPTION.RAISE_EXCEPTION;
3214
3215 END AddMileageExpLine;
3216
3217
3218
3219 PROCEDURE CopyAddonRates(p_from_report_line_id IN NUMBER,
3220 p_to_report_line_id IN NUMBER)
3221 IS
3222 l_category_code VARCHAR2(30);
3223 BEGIN
3224 SELECT CATEGORY_CODE INTO L_CATEGORY_CODE
3225 FROM AP_EXPENSE_REPORT_LINES
3226 WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
3227
3228 IF (L_CATEGORY_CODE = 'MILEAGE') THEN
3229 -- COPY ADDITIONAL MILEAGE RATE
3230 INSERT INTO OIE_ADDON_MILEAGE_RATES(REPORT_LINE_ID,
3231 ADDON_RATE_TYPE,
3232 MILEAGE_RATE,
3233 MILEAGE_AMOUNT,
3234 LAST_UPDATE_DATE,
3235 LAST_UPDATED_BY,
3236 LAST_UPDATE_LOGIN,
3237 CREATED_BY,
3238 CREATION_DATE)
3239 SELECT P_TO_REPORT_LINE_ID,
3240 ADDON_RATE_TYPE,
3241 MILEAGE_RATE,
3242 MILEAGE_AMOUNT,
3243 SYSDATE,
3244 LAST_UPDATED_BY,
3245 LAST_UPDATE_LOGIN,
3246 CREATED_BY,
3247 SYSDATE
3248 FROM OIE_ADDON_MILEAGE_RATES
3249 WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
3250 END IF;
3251 EXCEPTION WHEN OTHERS THEN
3252 null;
3253 END CopyAddonRates;
3254
3255
3256 PROCEDURE CopyPDMDailyBreakup(p_from_report_line_id IN NUMBER,
3257 p_to_report_line_id IN NUMBER)
3258 IS
3259 l_category_code VARCHAR2(30);
3260 BEGIN
3261 SELECT CATEGORY_CODE INTO L_CATEGORY_CODE
3262 FROM AP_EXPENSE_REPORT_LINES
3263 WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
3264
3265 IF (L_CATEGORY_CODE = 'PER_DIEM') THEN
3266 -- COPY PDM DAILY BREAKUP
3267 INSERT INTO OIE_PDM_DAILY_BREAKUPS(PDM_DAILY_BREAKUP_ID,
3268 REPORT_LINE_ID,
3269 START_DATE,
3270 END_DATE,
3271 AMOUNT,
3272 NUMBER_OF_MEALS,
3273 MEALS_AMOUNT,
3274 BREAKFAST_FLAG,
3275 LUNCH_FLAG,
3276 DINNER_FLAG,
3277 ACCOMMODATION_FLAG,
3278 ACCOMMODATION_AMOUNT,
3279 HOTEL_NAME,
3280 NIGHT_RATE_TYPE,
3281 NIGHT_RATE_AMOUNT,
3282 PDM_RATE,
3283 RATE_TYPE_CODE,
3284 PDM_DESTINATION_ID,
3285 LAST_UPDATE_DATE,
3286 LAST_UPDATED_BY,
3287 LAST_UPDATE_LOGIN,
3288 CREATED_BY,
3289 CREATION_DATE)
3290 SELECT OIE_PDM_DAILY_BREAKUPS_S.nextval,
3291 p_to_report_line_id,
3292 START_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
3293 END_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
3294 AMOUNT,
3295 NUMBER_OF_MEALS,
3296 MEALS_AMOUNT,
3297 BREAKFAST_FLAG,
3298 LUNCH_FLAG,
3299 DINNER_FLAG,
3300 ACCOMMODATION_FLAG,
3301 ACCOMMODATION_AMOUNT,
3302 HOTEL_NAME,
3303 NIGHT_RATE_TYPE,
3304 NIGHT_RATE_AMOUNT,
3305 PDM_RATE,
3306 RATE_TYPE_CODE,
3307 PDM_DESTINATION_ID,
3308 SYSDATE,
3309 LAST_UPDATED_BY,
3310 LAST_UPDATE_LOGIN,
3311 CREATED_BY,
3312 SYSDATE
3313 FROM OIE_PDM_DAILY_BREAKUPS
3314 WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
3315 END IF;
3316 EXCEPTION WHEN OTHERS THEN
3317 null;
3318 END CopyPDMDailyBreakup;
3319
3320 PROCEDURE CopyPDMDestination(p_from_report_line_id IN NUMBER,
3321 p_to_report_line_id IN NUMBER)
3322 IS
3323 l_category_code VARCHAR2(30);
3324 BEGIN
3325 SELECT CATEGORY_CODE INTO L_CATEGORY_CODE
3326 FROM AP_EXPENSE_REPORT_LINES
3327 WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
3328
3329 IF (L_CATEGORY_CODE = 'PER_DIEM') THEN
3330 -- COPY PDM DESTINATION
3331 INSERT INTO OIE_PDM_DESTINATIONS(PDM_DESTINATION_ID,
3332 REPORT_LINE_ID,
3333 START_DATE,
3334 END_DATE,
3335 LOCATION_ID,
3336 LAST_UPDATE_DATE,
3337 LAST_UPDATED_BY,
3338 LAST_UPDATE_LOGIN,
3339 CREATED_BY,
3340 CREATION_DATE)
3341 SELECT OIE_PDM_DESTINATIONS_S.nextval,
3342 p_to_report_line_id,
3343 START_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
3344 END_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward,
3345 LOCATION_ID,
3346 SYSDATE,
3347 LAST_UPDATED_BY,
3348 LAST_UPDATE_LOGIN,
3349 CREATED_BY,
3350 SYSDATE
3351 FROM OIE_PDM_DESTINATIONS
3352 WHERE REPORT_LINE_ID = P_FROM_REPORT_LINE_ID;
3353 END IF;
3354 EXCEPTION WHEN OTHERS THEN
3355 null;
3356 END CopyPDMDestination;
3357
3358 --------------------------------------------------------------------------------
3359 -------------------------------------------------------------------
3360 -- Name: CopyItemizationChildLines
3361 -- Desc: Copys all Itemization Child Lines of p_source_parent_report_line_id
3362 -- Input:
3363 -- p_user_id - user_id of employee
3364 -- p_source_report_header_id - source report header id
3365 -- p_target_report_header_id - target report header id
3366 -- p_source_parent_report_line_id - source itemization parent report line id
3367 -- p_target_parent_report_line_id - target itemization parent report line id
3368 -------------------------------------------------------------------
3369 PROCEDURE CopyItemizationChildLines(
3370 p_user_id IN NUMBER,
3371 p_source_report_header_id IN expLines_headerID,
3372 p_target_report_header_id IN expLines_headerID,
3373 p_source_parent_report_line_id IN NUMBER,
3374 p_target_parent_report_line_id IN NUMBER) IS
3375
3376 l_TempReportLineID expLines_report_line_id;
3377 l_NewReportLineID expLines_report_line_id;
3378
3379 -- Bug 6689280 (sodash)
3380 l_Receipt_Conversion_Rate AP_EXPENSE_REPORT_LINES.RECEIPT_CONVERSION_RATE%type;
3381 -- Bug 7555144 - Swapping to and from Currency Codes
3382 l_from_currency_code AP_EXPENSE_REPORT_LINES.RECEIPT_CURRENCY_CODE%type;
3383 l_to_currency_code AP_EXPENSE_REPORT_LINES.CURRENCY_CODE%type;
3384 l_exchange_rate_type AP_EXPENSE_REPORT_LINES.EXCHANGE_RATE_TYPE%type;
3385 l_exchange_date AP_EXPENSE_REPORT_LINES.EXCHANGE_DATE%type;
3386 l_exchange_rate AP_EXPENSE_REPORT_LINES.EXCHANGE_RATE%type;
3387 l_lines_total AP_EXPENSE_REPORT_LINES.AMOUNT%type;
3388 l_amount AP_EXPENSE_REPORT_LINES.AMOUNT%type;
3389 -- Bug 7555144 - Commenting since Display Inverse Profile is for Display Purposes only.
3390 -- l_display_inverse_profile fnd_profile_option_values.profile_option_value%type;
3391
3392 -- Bug 7555144 - Changing the order to match with the from and to currencies
3393 CURSOR ReportLines IS
3394 SELECT report_line_id, receipt_currency_code, currency_code, (start_expense_date+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward), receipt_currency_amount
3395 FROM AP_EXPENSE_REPORT_LINES
3396 WHERE REPORT_HEADER_ID = P_source_report_header_id
3397 AND CREDIT_CARD_TRX_ID is null
3398 AND ITEMIZATION_PARENT_ID = p_source_parent_report_line_id;
3399 -- Bug 7150383(sodash) get the total of child lines and update the total of the parent line
3400 CURSOR update_new_parent_line_amt_c IS
3401 SELECT ael.*
3402 FROM ap_expense_report_lines ael
3403 WHERE report_header_id = p_target_report_header_id
3404 AND report_line_id = p_target_parent_report_line_id
3405 FOR UPDATE OF report_header_id, report_line_id NOWAIT;
3406
3407 l_parent_line_rec update_new_parent_line_amt_c%ROWTYPE;
3408
3409 BEGIN
3410
3411 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG',
3412 'start CopyItemizationChildLines');
3413 l_lines_total:=0;
3414 -- Find all lines associated with this report
3415 OPEN ReportLines;
3416
3417 LOOP
3418 FETCH ReportLines into l_TempReportLineID, l_from_currency_code, l_to_currency_code, l_exchange_date, l_amount;
3419 EXIT WHEN ReportLines%NOTFOUND;
3420
3421 -- Get new ID from sequence
3422 SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
3423 INTO l_NewReportLineID
3424 FROM DUAL;
3425
3426 -- Bug 6689280 (sodash)
3427 AP_WEB_DB_AP_INT_PKG.GetDefaultExchange( l_exchange_rate_type);
3428
3429 IF (l_from_currency_code <> l_to_currency_code) THEN
3430 l_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate
3431 (l_from_currency_code,
3432 l_to_currency_code,
3433 l_exchange_rate_type,
3434 l_exchange_date,
3435 'CalculateReceiptConversionRate');
3436 ELSE
3437 l_exchange_rate := 1;
3438 END IF;
3439
3440 -- Bug 7555144 - Commenting since Display Inverse Profile is for Display Purposes only.
3441 -- FND_PROFILE.GET('DISPLAY_INVERSE_RATE', l_display_inverse_profile);
3442
3443
3444 IF (l_exchange_rate IS NULL) THEN
3445 l_Receipt_Conversion_Rate := null;
3446 -- ELSIF(l_display_inverse_profile = 'Y') THEN
3447 -- l_Receipt_Conversion_Rate := 1/l_exchange_rate ;
3448 ELSE
3449 l_Receipt_Conversion_Rate := l_exchange_rate;
3450 END IF;
3451
3452 IF (l_exchange_rate IS NOT NULL) then
3453 l_amount := l_amount * l_Receipt_Conversion_Rate;
3454 ELSE
3455 l_amount := null;
3456 END IF;
3457
3458 -- For each line, duplicate its columns
3459 insert into AP_EXPENSE_REPORT_LINES
3460 (
3461 REPORT_HEADER_ID,
3462 CODE_COMBINATION_ID,
3463 ITEM_DESCRIPTION,
3464 SET_OF_BOOKS_ID,
3465 ITEMIZATION_PARENT_ID,
3466 AMOUNT,
3467 ATTRIBUTE_CATEGORY,
3468 ATTRIBUTE1,
3469 ATTRIBUTE2,
3470 ATTRIBUTE3,
3471 ATTRIBUTE4,
3472 ATTRIBUTE5,
3473 ATTRIBUTE6,
3474 ATTRIBUTE7,
3475 ATTRIBUTE8,
3476 ATTRIBUTE9,
3477 ATTRIBUTE10,
3478 ATTRIBUTE11,
3479 ATTRIBUTE12,
3480 ATTRIBUTE13,
3481 ATTRIBUTE14,
3482 ATTRIBUTE15,
3483 CURRENCY_CODE,
3484 EXCHANGE_RATE_TYPE,
3485 EXCHANGE_RATE,
3486 EXCHANGE_DATE,
3487 VAT_CODE,
3488 LINE_TYPE_LOOKUP_CODE,
3489 PROJECT_ACCOUNTING_CONTEXT,
3490 EXPENDITURE_TYPE,
3491 EXPENDITURE_ITEM_DATE,
3492 PA_QUANTITY,
3493 DISTRIBUTION_LINE_NUMBER,
3494 ORG_ID,
3495 GLOBAL_ATTRIBUTE_CATEGORY,
3496 GLOBAL_ATTRIBUTE1,
3497 GLOBAL_ATTRIBUTE2,
3498 GLOBAL_ATTRIBUTE3,
3499 GLOBAL_ATTRIBUTE4,
3500 GLOBAL_ATTRIBUTE5,
3501 GLOBAL_ATTRIBUTE6,
3502 GLOBAL_ATTRIBUTE7,
3503 GLOBAL_ATTRIBUTE8,
3504 GLOBAL_ATTRIBUTE9,
3505 GLOBAL_ATTRIBUTE10,
3506 GLOBAL_ATTRIBUTE11,
3507 GLOBAL_ATTRIBUTE12,
3508 GLOBAL_ATTRIBUTE13,
3509 GLOBAL_ATTRIBUTE14,
3510 GLOBAL_ATTRIBUTE15,
3511 GLOBAL_ATTRIBUTE16,
3512 GLOBAL_ATTRIBUTE17,
3513 GLOBAL_ATTRIBUTE18,
3514 GLOBAL_ATTRIBUTE19,
3515 GLOBAL_ATTRIBUTE20,
3516 JUSTIFICATION,
3517 EXPENSE_GROUP,
3518 START_EXPENSE_DATE,
3519 END_EXPENSE_DATE,
3520 RECEIPT_CURRENCY_CODE,
3521 RECEIPT_CONVERSION_RATE,
3522 DAILY_AMOUNT,
3523 RECEIPT_CURRENCY_AMOUNT,
3524 WEB_PARAMETER_ID,
3525 AMOUNT_INCLUDES_TAX_FLAG,
3526 MERCHANT_DOCUMENT_NUMBER,
3527 MERCHANT_NAME,
3528 MERCHANT_REFERENCE,
3529 MERCHANT_TAX_REG_NUMBER,
3530 MERCHANT_TAXPAYER_ID,
3531 COUNTRY_OF_SUPPLY,
3532 TAX_CODE_OVERRIDE_FLAG,
3533 TAX_CODE_ID,
3534 ALLOCATION_REASON,
3535 ALLOCATION_SPLIT_CODE,
3536 PROJECT_NAME,
3537 TASK_NAME,
3538 PA_INTERFACED_FLAG,
3539 PROJECT_NUMBER,
3540 TASK_NUMBER,
3541 AWARD_NUMBER,
3542 VEHICLE_TYPE,
3543 FUEL_TYPE,
3544 NUMBER_PEOPLE,
3545 AVG_MILEAGE_RATE,
3546 DESTINATION_FROM,
3547 DESTINATION_TO,
3548 TRIP_DISTANCE,
3549 LOCATION_ID,
3550 ATTENDEES,
3551 TICKET_NUMBER,
3552 FLIGHT_NUMBER,
3553 LICENSE_PLATE_NUMBER,
3554 NUMBER_ATTENDEES,
3555 LOCATION_TO_ID,
3556 NUM_PDM_DAYS1,
3557 NUM_PDM_DAYS2,
3558 NUM_PDM_DAYS3,
3559 PER_DIEM_RATE1,
3560 PER_DIEM_RATE2,
3561 PER_DIEM_RATE3,
3562 DEDUCTION_ADDITION_AMT1,
3563 DEDUCTION_ADDITION_AMT2,
3564 DEDUCTION_ADDITION_AMT3,
3565 NUM_FREE_BREAKFASTS1,
3566 NUM_FREE_LUNCHES1,
3567 NUM_FREE_DINNERS1,
3568 NUM_FREE_ACCOMMODATIONS1,
3569 NUM_FREE_BREAKFASTS2,
3570 NUM_FREE_LUNCHES2,
3571 NUM_FREE_DINNERS2,
3572 NUM_FREE_ACCOMMODATIONS2,
3573 NUM_FREE_BREAKFASTS3,
3574 NUM_FREE_LUNCHES3,
3575 NUM_FREE_DINNERS3,
3576 NUM_FREE_ACCOMMODATIONS3,
3577 TRAVEL_TYPE,
3578 FLEX_CONCATENATED,
3579 VEHICLE_CATEGORY_CODE,
3580 DISTANCE_UNIT_CODE,
3581 TICKET_CLASS_CODE,
3582 DAILY_DISTANCE,
3583 FUNC_CURRENCY_AMT,
3584 LOCATION,
3585 CATEGORY_CODE,
3586 CREATION_DATE,
3587 CREATED_BY,
3588 LAST_UPDATE_DATE,
3589 LAST_UPDATED_BY,
3590 REPORT_LINE_ID
3591 )
3592 select
3593 p_target_report_header_id AS REPORT_HEADER_ID,
3594 CODE_COMBINATION_ID,
3595 ITEM_DESCRIPTION,
3596 SET_OF_BOOKS_ID,
3597 p_target_parent_report_line_id AS ITEMIZATION_PARENT_ID,
3598 l_amount, -- Bug 6689280 (sodash)
3599 ATTRIBUTE_CATEGORY,
3600 ATTRIBUTE1,
3601 ATTRIBUTE2,
3602 ATTRIBUTE3,
3603 ATTRIBUTE4,
3604 ATTRIBUTE5,
3605 ATTRIBUTE6,
3606 ATTRIBUTE7,
3607 ATTRIBUTE8,
3608 ATTRIBUTE9,
3609 ATTRIBUTE10,
3610 ATTRIBUTE11,
3611 ATTRIBUTE12,
3612 ATTRIBUTE13,
3613 ATTRIBUTE14,
3614 ATTRIBUTE15,
3615 CURRENCY_CODE,
3616 EXCHANGE_RATE_TYPE,
3617 EXCHANGE_RATE,
3618 (START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward) as EXCHANGE_DATE,
3619 VAT_CODE,
3620 LINE_TYPE_LOOKUP_CODE,
3621 PROJECT_ACCOUNTING_CONTEXT,
3622 EXPENDITURE_TYPE,
3623 EXPENDITURE_ITEM_DATE,
3624 PA_QUANTITY,
3625 l_NewReportLineID,
3626 ORG_ID,
3627 GLOBAL_ATTRIBUTE_CATEGORY,
3628 GLOBAL_ATTRIBUTE1,
3629 GLOBAL_ATTRIBUTE2,
3630 GLOBAL_ATTRIBUTE3,
3631 GLOBAL_ATTRIBUTE4,
3632 GLOBAL_ATTRIBUTE5,
3633 GLOBAL_ATTRIBUTE6,
3634 GLOBAL_ATTRIBUTE7,
3635 GLOBAL_ATTRIBUTE8,
3636 GLOBAL_ATTRIBUTE9,
3637 GLOBAL_ATTRIBUTE10,
3638 GLOBAL_ATTRIBUTE11,
3639 GLOBAL_ATTRIBUTE12,
3640 GLOBAL_ATTRIBUTE13,
3641 GLOBAL_ATTRIBUTE14,
3642 GLOBAL_ATTRIBUTE15,
3643 GLOBAL_ATTRIBUTE16,
3644 GLOBAL_ATTRIBUTE17,
3645 GLOBAL_ATTRIBUTE18,
3646 GLOBAL_ATTRIBUTE19,
3647 GLOBAL_ATTRIBUTE20,
3648 JUSTIFICATION,
3649 EXPENSE_GROUP,
3650 START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS START_EXPENSE_DATE, -- roll forward 7 days
3651 END_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS END_EXPENSE_DATE, -- roll forward 7 days
3652 RECEIPT_CURRENCY_CODE,
3653 l_Receipt_Conversion_Rate, -- Bug 6689280 (sodash) reclculating the receipt conversion rate. Prior to it, It was getting set to null when the receipt currency and the reimbursement currency didn't match
3654 DAILY_AMOUNT,
3655 RECEIPT_CURRENCY_AMOUNT,
3656 WEB_PARAMETER_ID,
3657 AMOUNT_INCLUDES_TAX_FLAG,
3658 MERCHANT_DOCUMENT_NUMBER,
3659 MERCHANT_NAME,
3660 MERCHANT_REFERENCE,
3661 MERCHANT_TAX_REG_NUMBER,
3662 MERCHANT_TAXPAYER_ID,
3663 COUNTRY_OF_SUPPLY,
3664 TAX_CODE_OVERRIDE_FLAG,
3665 TAX_CODE_ID,
3666 ALLOCATION_REASON,
3667 ALLOCATION_SPLIT_CODE,
3668 PROJECT_NAME,
3669 TASK_NAME,
3670 PA_INTERFACED_FLAG,
3671 PROJECT_NUMBER,
3672 TASK_NUMBER,
3673 AWARD_NUMBER,
3674 VEHICLE_TYPE,
3675 FUEL_TYPE,
3676 NUMBER_PEOPLE,
3677 AVG_MILEAGE_RATE,
3678 DESTINATION_FROM,
3679 DESTINATION_TO,
3680 TRIP_DISTANCE,
3681 LOCATION_ID,
3682 ATTENDEES,
3683 TICKET_NUMBER,
3684 FLIGHT_NUMBER,
3685 LICENSE_PLATE_NUMBER,
3686 NUMBER_ATTENDEES,
3687 LOCATION_TO_ID,
3688 NUM_PDM_DAYS1,
3689 NUM_PDM_DAYS2,
3690 NUM_PDM_DAYS3,
3691 PER_DIEM_RATE1,
3692 PER_DIEM_RATE2,
3693 PER_DIEM_RATE3,
3694 DEDUCTION_ADDITION_AMT1,
3695 DEDUCTION_ADDITION_AMT2,
3696 DEDUCTION_ADDITION_AMT3,
3697 NUM_FREE_BREAKFASTS1,
3698 NUM_FREE_LUNCHES1,
3699 NUM_FREE_DINNERS1,
3700 NUM_FREE_ACCOMMODATIONS1,
3701 NUM_FREE_BREAKFASTS2,
3702 NUM_FREE_LUNCHES2,
3703 NUM_FREE_DINNERS2,
3704 NUM_FREE_ACCOMMODATIONS2,
3705 NUM_FREE_BREAKFASTS3,
3706 NUM_FREE_LUNCHES3,
3707 NUM_FREE_DINNERS3,
3708 NUM_FREE_ACCOMMODATIONS3,
3709 TRAVEL_TYPE,
3710 FLEX_CONCATENATED,
3711 VEHICLE_CATEGORY_CODE,
3712 DISTANCE_UNIT_CODE,
3713 TICKET_CLASS_CODE,
3714 DAILY_DISTANCE,
3715 FUNC_CURRENCY_AMT,
3716 LOCATION,
3717 CATEGORY_CODE,
3718 sysdate AS CREATION_DATE,
3719 p_user_id AS CREATED_BY,
3720 sysdate AS LAST_UPDATE_DATE,
3721 p_user_id AS LAST_UPDATED_BY,
3722 l_NewReportLineID
3723 from AP_EXPENSE_REPORT_LINES
3724 where REPORT_LINE_ID = l_TempReportLineID;
3725 l_lines_total := l_lines_total+l_amount;
3726 -- Duplicate distribution lines associated with this line
3727 AP_WEB_DB_EXPDIST_PKG.DuplicateDistributions(
3728 p_user_id,
3729 p_target_report_header_id,
3730 l_TempReportLineID,
3731 l_NewReportLineID);
3732
3733 -- Bug 5578059
3734 -- Duplicate Attendee info associated with this child line
3735 AP_WEB_DB_EXP_ATTENDEES_PKG.DuplicateAttendeeInfo(
3736 p_user_id,
3737 l_TempReportLineID,
3738 l_NewReportLineID);
3739
3740 -- copy additional rates
3741 CopyAddonRates(l_TempReportLineID, l_NewReportLineID);
3742 -- copy pdm daily breakup
3743 CopyPDMDailyBreakup(l_TempReportLineID, l_NewReportLineID);
3744 -- copy pdm destination
3745 CopyPDMDestination(l_TempReportLineID, l_NewReportLineID);
3746
3747 END LOOP;
3748
3749 CLOSE ReportLines;
3750
3751 -- Bug 7150383(sodash) get the total of child lines and update the total of the parent line
3752 OPEN update_new_parent_line_amt_c;
3753 FETCH update_new_parent_line_amt_c into l_parent_line_rec;
3754
3755 UPDATE ap_expense_report_lines
3756 SET amount = l_lines_total
3757 WHERE CURRENT OF update_new_parent_line_amt_c;
3758
3759 CLOSE update_new_parent_line_amt_c;
3760
3761 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG',
3762 'end CopyItemizationChildLines');
3763
3764 END CopyItemizationChildLines;
3765
3766 --------------------------------------------------------------------------------
3767
3768 -------------------------------------------------------------------
3769 -- Name: DuplicateLines
3770 -- Desc: duplicates Expense Report Lines
3771 -- Input: p_source_report_header_id - source expense report header id
3772 -- Returns: p_target_report_header_id - target expense report header id
3773 -------------------------------------------------------------------
3774 PROCEDURE DuplicateLines(
3775 p_user_id IN NUMBER,
3776 p_source_report_header_id IN expLines_headerID,
3777 p_target_report_header_id IN OUT NOCOPY expLines_headerID) IS
3778
3779 l_TempReportLineID expLines_report_line_id;
3780 l_NewReportLineID expLines_report_line_id;
3781
3782 -- Bug 6689280 (sodash)
3783 l_Receipt_Conversion_Rate AP_EXPENSE_REPORT_LINES.RECEIPT_CONVERSION_RATE%type;
3784 -- Bug 7555144 - Swapping to and from Currency Codes
3785 l_from_currency_code AP_EXPENSE_REPORT_LINES.RECEIPT_CURRENCY_CODE%type;
3786 l_to_currency_code AP_EXPENSE_REPORT_LINES.CURRENCY_CODE%type;
3787 l_exchange_rate_type AP_EXPENSE_REPORT_LINES.EXCHANGE_RATE_TYPE%type;
3788 l_exchange_date AP_EXPENSE_REPORT_LINES.EXCHANGE_DATE%type;
3789 l_exchange_rate AP_EXPENSE_REPORT_LINES.EXCHANGE_RATE%type;
3790 l_amount AP_EXPENSE_REPORT_LINES.AMOUNT%type;
3791 -- Bug 7555144 - Commenting since Display Inverse Profile is for Display Purposes only.
3792 -- l_display_inverse_profile fnd_profile_option_values.profile_option_value%type;
3793 l_itemization_parent_id AP_EXPENSE_REPORT_LINES.itemization_parent_id%type;
3794
3795 -- Bug 7555144 - Changing the Order to match with the from and to currencies
3796 CURSOR ReportLines IS
3797 SELECT report_line_id, itemization_parent_id, receipt_currency_code, currency_code, (start_expense_date+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward), receipt_currency_amount
3798 FROM AP_EXPENSE_REPORT_LINES
3799 WHERE REPORT_HEADER_ID = P_source_report_header_id
3800 AND CREDIT_CARD_TRX_ID is null
3801 AND (ITEMIZATION_PARENT_ID is null
3802 OR
3803 ITEMIZATION_PARENT_ID = -1);
3804
3805 BEGIN
3806
3807 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG',
3808 'start DuplicateLines');
3809
3810 -- Find all lines associated with this report
3811 OPEN ReportLines;
3812
3813 LOOP
3814 FETCH ReportLines into l_TempReportLineID,l_itemization_parent_id,l_from_currency_code, l_to_currency_code, l_exchange_date, l_amount;
3815 EXIT WHEN ReportLines%NOTFOUND;
3816
3817 -- Get new ID from sequence
3818 SELECT AP_EXPENSE_REPORT_LINES_S.NEXTVAL
3819 INTO l_NewReportLineID
3820 FROM DUAL;
3821
3822 -- Bug 6689280 (sodash)
3823 AP_WEB_DB_AP_INT_PKG.GetDefaultExchange( l_exchange_rate_type);
3824
3825 IF (l_from_currency_code <> l_to_currency_code) THEN
3826 l_exchange_rate := AP_UTILITIES_PKG.get_exchange_rate
3827 (l_from_currency_code,
3828 l_to_currency_code,
3829 l_exchange_rate_type,
3830 l_exchange_date,
3831 'CalculateReceiptConversionRate');
3832 ELSE
3833 l_exchange_rate := 1;
3834 END IF;
3835
3836 -- Bug 7555144 - Commenting since Display Inverse Profile is for Display Purposes only.
3837 -- FND_PROFILE.GET('DISPLAY_INVERSE_RATE', l_display_inverse_profile);
3838
3839 IF (l_exchange_rate IS NULL) THEN
3840 l_Receipt_Conversion_Rate := null;
3841 -- ELSIF (l_display_inverse_profile = 'Y') THEN
3842 -- l_Receipt_Conversion_Rate := 1/l_exchange_rate ;
3843 ELSE
3844 l_Receipt_Conversion_Rate := l_exchange_rate;
3845 END IF;
3846
3847 IF (l_exchange_rate IS NOT NULL) then
3848 l_amount := l_amount * l_Receipt_Conversion_Rate;
3849 ELSE
3850 l_amount := null;
3851 END IF;
3852
3853 -- For each line, duplicate its columns
3854 insert into AP_EXPENSE_REPORT_LINES
3855 (
3856 REPORT_HEADER_ID,
3857 CODE_COMBINATION_ID,
3858 ITEM_DESCRIPTION,
3859 SET_OF_BOOKS_ID,
3860 ITEMIZATION_PARENT_ID,
3861 AMOUNT,
3862 ATTRIBUTE_CATEGORY,
3863 ATTRIBUTE1,
3864 ATTRIBUTE2,
3865 ATTRIBUTE3,
3866 ATTRIBUTE4,
3867 ATTRIBUTE5,
3868 ATTRIBUTE6,
3869 ATTRIBUTE7,
3870 ATTRIBUTE8,
3871 ATTRIBUTE9,
3872 ATTRIBUTE10,
3873 ATTRIBUTE11,
3874 ATTRIBUTE12,
3875 ATTRIBUTE13,
3876 ATTRIBUTE14,
3877 ATTRIBUTE15,
3878 CURRENCY_CODE,
3879 EXCHANGE_RATE_TYPE,
3880 EXCHANGE_RATE,
3881 EXCHANGE_DATE,
3882 VAT_CODE,
3883 LINE_TYPE_LOOKUP_CODE,
3884 PROJECT_ACCOUNTING_CONTEXT,
3885 EXPENDITURE_TYPE,
3886 EXPENDITURE_ITEM_DATE,
3887 PA_QUANTITY,
3888 DISTRIBUTION_LINE_NUMBER,
3889 ORG_ID,
3890 GLOBAL_ATTRIBUTE_CATEGORY,
3891 GLOBAL_ATTRIBUTE1,
3892 GLOBAL_ATTRIBUTE2,
3893 GLOBAL_ATTRIBUTE3,
3894 GLOBAL_ATTRIBUTE4,
3895 GLOBAL_ATTRIBUTE5,
3896 GLOBAL_ATTRIBUTE6,
3897 GLOBAL_ATTRIBUTE7,
3898 GLOBAL_ATTRIBUTE8,
3899 GLOBAL_ATTRIBUTE9,
3900 GLOBAL_ATTRIBUTE10,
3901 GLOBAL_ATTRIBUTE11,
3902 GLOBAL_ATTRIBUTE12,
3903 GLOBAL_ATTRIBUTE13,
3904 GLOBAL_ATTRIBUTE14,
3905 GLOBAL_ATTRIBUTE15,
3906 GLOBAL_ATTRIBUTE16,
3907 GLOBAL_ATTRIBUTE17,
3908 GLOBAL_ATTRIBUTE18,
3909 GLOBAL_ATTRIBUTE19,
3910 GLOBAL_ATTRIBUTE20,
3911 JUSTIFICATION,
3912 EXPENSE_GROUP,
3913 START_EXPENSE_DATE,
3914 END_EXPENSE_DATE,
3915 RECEIPT_CURRENCY_CODE,
3916 RECEIPT_CONVERSION_RATE,
3917 DAILY_AMOUNT,
3918 RECEIPT_CURRENCY_AMOUNT,
3919 WEB_PARAMETER_ID,
3920 AMOUNT_INCLUDES_TAX_FLAG,
3921 MERCHANT_DOCUMENT_NUMBER,
3922 MERCHANT_NAME,
3923 MERCHANT_REFERENCE,
3924 MERCHANT_TAX_REG_NUMBER,
3925 MERCHANT_TAXPAYER_ID,
3926 COUNTRY_OF_SUPPLY,
3927 TAX_CODE_OVERRIDE_FLAG,
3928 TAX_CODE_ID,
3929 ALLOCATION_REASON,
3930 ALLOCATION_SPLIT_CODE,
3931 PROJECT_NAME,
3932 TASK_NAME,
3933 PA_INTERFACED_FLAG,
3934 PROJECT_NUMBER,
3935 TASK_NUMBER,
3936 AWARD_NUMBER,
3937 VEHICLE_TYPE,
3938 FUEL_TYPE,
3939 NUMBER_PEOPLE,
3940 AVG_MILEAGE_RATE,
3941 DESTINATION_FROM,
3942 DESTINATION_TO,
3943 TRIP_DISTANCE,
3944 LOCATION_ID,
3945 ATTENDEES,
3946 TICKET_NUMBER,
3947 FLIGHT_NUMBER,
3948 LICENSE_PLATE_NUMBER,
3949 NUMBER_ATTENDEES,
3950 LOCATION_TO_ID,
3951 NUM_PDM_DAYS1,
3952 NUM_PDM_DAYS2,
3953 NUM_PDM_DAYS3,
3954 PER_DIEM_RATE1,
3955 PER_DIEM_RATE2,
3956 PER_DIEM_RATE3,
3957 DEDUCTION_ADDITION_AMT1,
3958 DEDUCTION_ADDITION_AMT2,
3959 DEDUCTION_ADDITION_AMT3,
3960 NUM_FREE_BREAKFASTS1,
3961 NUM_FREE_LUNCHES1,
3962 NUM_FREE_DINNERS1,
3963 NUM_FREE_ACCOMMODATIONS1,
3964 NUM_FREE_BREAKFASTS2,
3965 NUM_FREE_LUNCHES2,
3966 NUM_FREE_DINNERS2,
3967 NUM_FREE_ACCOMMODATIONS2,
3968 NUM_FREE_BREAKFASTS3,
3969 NUM_FREE_LUNCHES3,
3970 NUM_FREE_DINNERS3,
3971 NUM_FREE_ACCOMMODATIONS3,
3972 TRAVEL_TYPE,
3973 FLEX_CONCATENATED,
3974 VEHICLE_CATEGORY_CODE,
3975 DISTANCE_UNIT_CODE,
3976 TICKET_CLASS_CODE,
3977 DAILY_DISTANCE,
3978 FUNC_CURRENCY_AMT,
3979 LOCATION,
3980 CATEGORY_CODE,
3981 CREATION_DATE,
3982 CREATED_BY,
3983 LAST_UPDATE_DATE,
3984 LAST_UPDATED_BY,
3985 REPORT_LINE_ID
3986 )
3987 select
3988 p_target_report_header_id AS REPORT_HEADER_ID,
3989 CODE_COMBINATION_ID,
3990 ITEM_DESCRIPTION,
3991 SET_OF_BOOKS_ID,
3992 ITEMIZATION_PARENT_ID,
3993 l_amount, -- Bug 6689280 (sodash) changed it because the earlier decode condition set the amount to null when the receipt currency and the reimbursement currency didn't match
3994 ATTRIBUTE_CATEGORY,
3995 ATTRIBUTE1,
3996 ATTRIBUTE2,
3997 ATTRIBUTE3,
3998 ATTRIBUTE4,
3999 ATTRIBUTE5,
4000 ATTRIBUTE6,
4001 ATTRIBUTE7,
4002 ATTRIBUTE8,
4003 ATTRIBUTE9,
4004 ATTRIBUTE10,
4005 ATTRIBUTE11,
4006 ATTRIBUTE12,
4007 ATTRIBUTE13,
4008 ATTRIBUTE14,
4009 ATTRIBUTE15,
4010 CURRENCY_CODE,
4011 EXCHANGE_RATE_TYPE,
4012 EXCHANGE_RATE, -- Bug 6689280 (sodash)
4013 START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS EXCHANGE_DATE, -- bug 6689280 (sodash)
4014 VAT_CODE,
4015 LINE_TYPE_LOOKUP_CODE,
4016 PROJECT_ACCOUNTING_CONTEXT,
4017 EXPENDITURE_TYPE,
4018 EXPENDITURE_ITEM_DATE,
4019 PA_QUANTITY,
4020 AP_WEB_DB_EXPLINE_PKG.C_InitialDistLineNumber+DISTRIBUTION_LINE_NUMBER,
4021 ORG_ID,
4022 GLOBAL_ATTRIBUTE_CATEGORY,
4023 GLOBAL_ATTRIBUTE1,
4024 GLOBAL_ATTRIBUTE2,
4025 GLOBAL_ATTRIBUTE3,
4026 GLOBAL_ATTRIBUTE4,
4027 GLOBAL_ATTRIBUTE5,
4028 GLOBAL_ATTRIBUTE6,
4029 GLOBAL_ATTRIBUTE7,
4030 GLOBAL_ATTRIBUTE8,
4031 GLOBAL_ATTRIBUTE9,
4032 GLOBAL_ATTRIBUTE10,
4033 GLOBAL_ATTRIBUTE11,
4034 GLOBAL_ATTRIBUTE12,
4035 GLOBAL_ATTRIBUTE13,
4036 GLOBAL_ATTRIBUTE14,
4037 GLOBAL_ATTRIBUTE15,
4038 GLOBAL_ATTRIBUTE16,
4039 GLOBAL_ATTRIBUTE17,
4040 GLOBAL_ATTRIBUTE18,
4041 GLOBAL_ATTRIBUTE19,
4042 GLOBAL_ATTRIBUTE20,
4043 JUSTIFICATION,
4044 EXPENSE_GROUP,
4045 START_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS START_EXPENSE_DATE, -- roll forward 7 days
4046 END_EXPENSE_DATE+AP_WEB_DB_EXPLINE_PKG.C_NumDaysRollForward AS END_EXPENSE_DATE, -- roll forward 7 days
4047 RECEIPT_CURRENCY_CODE,
4048 l_Receipt_Conversion_Rate, -- Bug 6689280 (sodash) reclculating the receipt conversion rate. Prior to it, It was getting set to null when the receipt currency and the reimbursement currency didn't match
4049 DAILY_AMOUNT,
4050 RECEIPT_CURRENCY_AMOUNT,
4051 WEB_PARAMETER_ID,
4052 AMOUNT_INCLUDES_TAX_FLAG,
4053 MERCHANT_DOCUMENT_NUMBER,
4054 MERCHANT_NAME,
4055 MERCHANT_REFERENCE,
4056 MERCHANT_TAX_REG_NUMBER,
4057 MERCHANT_TAXPAYER_ID,
4058 COUNTRY_OF_SUPPLY,
4059 TAX_CODE_OVERRIDE_FLAG,
4060 TAX_CODE_ID,
4061 ALLOCATION_REASON,
4062 ALLOCATION_SPLIT_CODE,
4063 PROJECT_NAME,
4064 TASK_NAME,
4065 PA_INTERFACED_FLAG,
4066 PROJECT_NUMBER,
4067 TASK_NUMBER,
4068 AWARD_NUMBER,
4069 VEHICLE_TYPE,
4070 FUEL_TYPE,
4071 NUMBER_PEOPLE,
4072 AVG_MILEAGE_RATE,
4073 DESTINATION_FROM,
4074 DESTINATION_TO,
4075 TRIP_DISTANCE,
4076 LOCATION_ID,
4077 ATTENDEES,
4078 TICKET_NUMBER,
4079 FLIGHT_NUMBER,
4080 LICENSE_PLATE_NUMBER,
4081 NUMBER_ATTENDEES,
4082 LOCATION_TO_ID,
4083 NUM_PDM_DAYS1,
4084 NUM_PDM_DAYS2,
4085 NUM_PDM_DAYS3,
4086 PER_DIEM_RATE1,
4087 PER_DIEM_RATE2,
4088 PER_DIEM_RATE3,
4089 DEDUCTION_ADDITION_AMT1,
4090 DEDUCTION_ADDITION_AMT2,
4091 DEDUCTION_ADDITION_AMT3,
4092 NUM_FREE_BREAKFASTS1,
4093 NUM_FREE_LUNCHES1,
4094 NUM_FREE_DINNERS1,
4095 NUM_FREE_ACCOMMODATIONS1,
4096 NUM_FREE_BREAKFASTS2,
4097 NUM_FREE_LUNCHES2,
4098 NUM_FREE_DINNERS2,
4099 NUM_FREE_ACCOMMODATIONS2,
4100 NUM_FREE_BREAKFASTS3,
4101 NUM_FREE_LUNCHES3,
4102 NUM_FREE_DINNERS3,
4103 NUM_FREE_ACCOMMODATIONS3,
4104 TRAVEL_TYPE,
4105 FLEX_CONCATENATED,
4106 VEHICLE_CATEGORY_CODE,
4107 DISTANCE_UNIT_CODE,
4108 TICKET_CLASS_CODE,
4109 DAILY_DISTANCE,
4110 FUNC_CURRENCY_AMT,
4111 LOCATION,
4112 CATEGORY_CODE,
4113 sysdate AS CREATION_DATE,
4114 p_user_id AS CREATED_BY,
4115 sysdate AS LAST_UPDATE_DATE,
4116 p_user_id AS LAST_UPDATED_BY,
4117 l_NewReportLineID
4118 from AP_EXPENSE_REPORT_LINES
4119 where REPORT_LINE_ID = l_TempReportLineID;
4120
4121 if (l_itemization_parent_id = '-1') then
4122 CopyItemizationChildLines(p_user_id,
4123 p_source_report_header_id,
4124 p_target_report_header_id,
4125 l_TempReportLineID,
4126 l_NewReportLineID);
4127 end if;
4128
4129 -- Duplicate distribution lines associated with this line
4130 AP_WEB_DB_EXPDIST_PKG.DuplicateDistributions(
4131 p_user_id,
4132 p_target_report_header_id,
4133 l_TempReportLineID,
4134 l_NewReportLineID);
4135
4136 -- Duplicate Attendee info associated with this line
4137 AP_WEB_DB_EXP_ATTENDEES_PKG.DuplicateAttendeeInfo(
4138 p_user_id,
4139 l_TempReportLineID,
4140 l_NewReportLineID);
4141
4142 -- copy additional rates
4143 CopyAddonRates(l_TempReportLineID, l_NewReportLineID);
4144 -- copy pdm daily breakup
4145 CopyPDMDailyBreakup(l_TempReportLineID, l_NewReportLineID);
4146 -- copy pdm destination
4147 CopyPDMDestination(l_TempReportLineID, l_NewReportLineID);
4148
4149 END LOOP;
4150
4151 CLOSE ReportLines;
4152
4153 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG',
4154 'end DuplicateLines');
4155
4156 END DuplicateLines;
4157
4158 -------------------------------------------------------------------
4159 PROCEDURE ResetAPValidationErrors(
4160 p_report_header_id IN expLines_headerID) IS
4161 -------------------------------------------------------------------
4162 BEGIN
4163
4164 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'start ResetAPValidationErrors');
4165
4166 UPDATE ap_expense_report_lines
4167 SET ap_validation_error = ''
4168 WHERE report_header_id = p_report_header_id;
4169
4170 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end ResetAPValidationErrors');
4171
4172 EXCEPTION
4173 WHEN NO_DATA_FOUND THEN
4174 AP_WEB_DB_UTIL_PKG.RaiseException('ResetAPValidationErrors');
4175 APP_EXCEPTION.RAISE_EXCEPTION;
4176 WHEN OTHERS THEN
4177 AP_WEB_DB_UTIL_PKG.RaiseException('ResetAPValidationErrors');
4178 APP_EXCEPTION.RAISE_EXCEPTION;
4179
4180 END ResetAPValidationErrors;
4181
4182 -------------------------------------------------------------------
4183 PROCEDURE UpdateAPValidationError(
4184 p_report_header_id IN expLines_headerID,
4185 p_dist_line_number IN expLines_distLineNum,
4186 p_ap_validation_error IN expLines_APValidationError) IS
4187 -------------------------------------------------------------------
4188 BEGIN
4189
4190 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'start UpdateAPValidationError');
4191
4192 /* Bug 3637166 : Doing a substrb before updation */
4193
4194 UPDATE ap_expense_report_lines
4195 SET ap_validation_error = substrb(ap_validation_error||p_ap_validation_error,1,240)
4196 WHERE report_header_id = p_report_header_id
4197 AND distribution_line_number = p_dist_line_number;
4198
4199 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end UpdateAPValidationError');
4200
4201 EXCEPTION
4202 WHEN NO_DATA_FOUND THEN
4203 AP_WEB_DB_UTIL_PKG.RaiseException('UpdateAPValidationError');
4204 APP_EXCEPTION.RAISE_EXCEPTION;
4205 WHEN OTHERS THEN
4206 AP_WEB_DB_UTIL_PKG.RaiseException('UpdateAPValidationError');
4207 APP_EXCEPTION.RAISE_EXCEPTION;
4208
4209 END UpdateAPValidationError;
4210
4211 -------------------------------------------------------------------
4212 PROCEDURE resetAPflags(
4213 p_report_header_id IN expLines_headerID) IS
4214 -------------------------------------------------------------------
4215 BEGIN
4216
4217 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'start resetAPflags');
4218
4219 UPDATE ap_expense_report_lines
4220 SET receipt_verified_flag = null,
4221 policy_shortpay_flag = null,
4222 adjustment_reason = null
4223 WHERE report_header_id = p_report_header_id;
4224
4225 UPDATE ap_expense_report_headers
4226 SET audit_code = null, -- Bug 4019412
4227 report_submitted_date = null
4228 WHERE report_header_id = p_report_header_id;
4229
4230 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end resetAPflags');
4231
4232 EXCEPTION
4233 WHEN NO_DATA_FOUND THEN
4234 AP_WEB_DB_UTIL_PKG.RaiseException('resetAPflags');
4235 APP_EXCEPTION.RAISE_EXCEPTION;
4236 WHEN OTHERS THEN
4237 AP_WEB_DB_UTIL_PKG.RaiseException('resetAPflags');
4238 APP_EXCEPTION.RAISE_EXCEPTION;
4239
4240 END resetAPflags;
4241
4242 -------------------------------------------------------------------
4243 FUNCTION GetNumCashLinesWOMerch(p_report_header_id IN expLines_headerID,
4244 p_count OUT NOCOPY NUMBER)
4245 RETURN BOOLEAN IS
4246 -------------------------------------------------------------------
4247 BEGIN
4248 SELECT count(*)
4249 INTO p_count
4250 FROM ap_expense_report_lines
4251 WHERE report_header_id = p_report_header_id
4252 AND merchant_name IS NOT NULL
4253 AND credit_card_trx_id IS NULL;
4254
4255 return TRUE;
4256 EXCEPTION
4257 WHEN NO_DATA_FOUND THEN
4258 return FALSE;
4259 WHEN OTHERS THEN
4260 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumCashLinesWOMerch');
4261 APP_EXCEPTION.RAISE_EXCEPTION;
4262 return FALSE;
4263 END GetNumCashLinesWOMerch;
4264
4265 -------------------------------------------------------------------
4266
4267 /**
4268 * jrautiai ADJ Fix start
4269 */
4270
4271 /**
4272 * jrautiai ADJ Fix
4273 * Need the ability to insert a single row, this procedure inserts a row in the
4274 * database, using the data provided in the record given as parameter.
4275 */
4276 PROCEDURE InsertLine(expense_line_rec in AP_EXPENSE_REPORT_LINES_ALL%ROWTYPE) IS
4277 l_debug_info varchar2(240);
4278 BEGIN
4279
4280 l_debug_info := 'InsertLine';
4281
4282 INSERT INTO AP_EXPENSE_REPORT_LINES_ALL
4283 (REPORT_HEADER_ID,
4284 LAST_UPDATE_DATE,
4285 LAST_UPDATED_BY,
4286 CODE_COMBINATION_ID,
4287 ITEM_DESCRIPTION,
4288 SET_OF_BOOKS_ID,
4289 AMOUNT,
4290 ATTRIBUTE_CATEGORY,
4291 ATTRIBUTE1,
4292 ATTRIBUTE2,
4293 ATTRIBUTE3,
4294 ATTRIBUTE4,
4295 ATTRIBUTE5,
4296 ATTRIBUTE6,
4297 ATTRIBUTE7,
4298 ATTRIBUTE8,
4299 ATTRIBUTE9,
4300 ATTRIBUTE10,
4301 ATTRIBUTE11,
4302 ATTRIBUTE12,
4303 ATTRIBUTE13,
4304 ATTRIBUTE14,
4305 ATTRIBUTE15,
4306 CURRENCY_CODE,
4307 EXCHANGE_RATE_TYPE,
4308 EXCHANGE_RATE,
4309 EXCHANGE_DATE,
4310 VAT_CODE,
4311 LINE_TYPE_LOOKUP_CODE,
4312 LAST_UPDATE_LOGIN,
4313 CREATION_DATE,
4314 CREATED_BY,
4315 STAT_AMOUNT,
4316 PROJECT_ACCOUNTING_CONTEXT,
4317 EXPENDITURE_TYPE,
4318 EXPENDITURE_ITEM_DATE,
4319 PA_QUANTITY,
4320 DISTRIBUTION_LINE_NUMBER,
4321 REFERENCE_1,
4322 REFERENCE_2,
4323 AWT_GROUP_ID,
4324 ORG_ID,
4325 RECEIPT_VERIFIED_FLAG,
4326 JUSTIFICATION_REQUIRED_FLAG,
4327 RECEIPT_REQUIRED_FLAG,
4328 RECEIPT_MISSING_FLAG,
4329 JUSTIFICATION,
4330 EXPENSE_GROUP,
4331 START_EXPENSE_DATE,
4332 END_EXPENSE_DATE,
4333 RECEIPT_CURRENCY_CODE,
4334 RECEIPT_CONVERSION_RATE,
4335 DAILY_AMOUNT,
4336 RECEIPT_CURRENCY_AMOUNT,
4337 WEB_PARAMETER_ID,
4338 GLOBAL_ATTRIBUTE_CATEGORY,
4339 GLOBAL_ATTRIBUTE1,
4340 GLOBAL_ATTRIBUTE2,
4341 GLOBAL_ATTRIBUTE3,
4342 GLOBAL_ATTRIBUTE4,
4343 GLOBAL_ATTRIBUTE5,
4344 GLOBAL_ATTRIBUTE6,
4345 GLOBAL_ATTRIBUTE7,
4346 GLOBAL_ATTRIBUTE8,
4347 GLOBAL_ATTRIBUTE9,
4348 GLOBAL_ATTRIBUTE10,
4349 AMOUNT_INCLUDES_TAX_FLAG,
4350 GLOBAL_ATTRIBUTE11,
4351 GLOBAL_ATTRIBUTE12,
4352 GLOBAL_ATTRIBUTE13,
4353 GLOBAL_ATTRIBUTE14,
4354 GLOBAL_ATTRIBUTE15,
4355 GLOBAL_ATTRIBUTE16,
4356 GLOBAL_ATTRIBUTE17,
4357 GLOBAL_ATTRIBUTE18,
4358 GLOBAL_ATTRIBUTE19,
4359 GLOBAL_ATTRIBUTE20,
4360 ADJUSTMENT_REASON,
4361 POLICY_SHORTPAY_FLAG,
4362 MERCHANT_DOCUMENT_NUMBER,
4363 MERCHANT_NAME,
4364 MERCHANT_REFERENCE,
4365 MERCHANT_TAX_REG_NUMBER,
4366 MERCHANT_TAXPAYER_ID,
4367 COUNTRY_OF_SUPPLY,
4368 TAX_CODE_OVERRIDE_FLAG,
4369 TAX_CODE_ID,
4370 CREDIT_CARD_TRX_ID,
4371 ALLOCATION_REASON,
4372 ALLOCATION_SPLIT_CODE,
4373 PROJECT_NAME,
4374 TASK_NAME,
4375 COMPANY_PREPAID_INVOICE_ID,
4376 PA_INTERFACED_FLAG,
4377 PROJECT_NUMBER,
4378 TASK_NUMBER,
4379 AWARD_NUMBER,
4380 VEHICLE_CATEGORY_CODE,
4381 VEHICLE_TYPE,
4382 FUEL_TYPE,
4383 NUMBER_PEOPLE,
4384 DAILY_DISTANCE,
4385 DISTANCE_UNIT_CODE,
4386 AVG_MILEAGE_RATE,
4387 DESTINATION_FROM,
4388 DESTINATION_TO,
4389 TRIP_DISTANCE,
4390 LICENSE_PLATE_NUMBER,
4391 MILEAGE_RATE_ADJUSTED_FLAG,
4392 LOCATION_ID,
4393 NUM_PDM_DAYS1,
4394 NUM_PDM_DAYS2,
4395 NUM_PDM_DAYS3,
4396 PER_DIEM_RATE1,
4397 PER_DIEM_RATE2,
4398 PER_DIEM_RATE3,
4399 DEDUCTION_ADDITION_AMT1,
4400 DEDUCTION_ADDITION_AMT2,
4401 DEDUCTION_ADDITION_AMT3,
4402 NUM_FREE_BREAKFASTS1,
4403 NUM_FREE_LUNCHES1,
4404 NUM_FREE_DINNERS1,
4405 NUM_FREE_ACCOMMODATIONS1,
4406 NUM_FREE_BREAKFASTS2,
4407 NUM_FREE_LUNCHES2,
4408 NUM_FREE_DINNERS2,
4409 NUM_FREE_ACCOMMODATIONS2,
4410 NUM_FREE_BREAKFASTS3,
4411 NUM_FREE_LUNCHES3,
4412 NUM_FREE_DINNERS3,
4413 NUM_FREE_ACCOMMODATIONS3,
4414 ATTENDEES,
4415 NUMBER_ATTENDEES,
4416 TRAVEL_TYPE,
4417 TICKET_CLASS_CODE,
4418 TICKET_NUMBER,
4419 FLIGHT_NUMBER,
4420 LOCATION_TO_ID,
4421 ITEMIZATION_PARENT_ID,
4422 FLEX_CONCATENATED,
4423 FUNC_CURRENCY_AMT,
4424 LOCATION,
4425 CATEGORY_CODE,
4426 ADJUSTMENT_REASON_CODE,
4427 AP_VALIDATION_ERROR,
4428 SUBMITTED_AMOUNT,
4429 REPORT_LINE_ID)
4430 VALUES (
4431 expense_line_rec.REPORT_HEADER_ID,
4432 expense_line_rec.LAST_UPDATE_DATE,
4433 expense_line_rec.LAST_UPDATED_BY,
4434 expense_line_rec.CODE_COMBINATION_ID,
4435 expense_line_rec.ITEM_DESCRIPTION,
4436 expense_line_rec.SET_OF_BOOKS_ID,
4437 expense_line_rec.AMOUNT,
4438 expense_line_rec.ATTRIBUTE_CATEGORY,
4439 expense_line_rec.ATTRIBUTE1,
4440 expense_line_rec.ATTRIBUTE2,
4441 expense_line_rec.ATTRIBUTE3,
4442 expense_line_rec.ATTRIBUTE4,
4443 expense_line_rec.ATTRIBUTE5,
4444 expense_line_rec.ATTRIBUTE6,
4445 expense_line_rec.ATTRIBUTE7,
4446 expense_line_rec.ATTRIBUTE8,
4447 expense_line_rec.ATTRIBUTE9,
4448 expense_line_rec.ATTRIBUTE10,
4449 expense_line_rec.ATTRIBUTE11,
4450 expense_line_rec.ATTRIBUTE12,
4451 expense_line_rec.ATTRIBUTE13,
4452 expense_line_rec.ATTRIBUTE14,
4453 expense_line_rec.ATTRIBUTE15,
4454 expense_line_rec.CURRENCY_CODE,
4455 expense_line_rec.EXCHANGE_RATE_TYPE,
4456 expense_line_rec.EXCHANGE_RATE,
4457 expense_line_rec.EXCHANGE_DATE,
4458 expense_line_rec.VAT_CODE,
4459 expense_line_rec.LINE_TYPE_LOOKUP_CODE,
4460 expense_line_rec.LAST_UPDATE_LOGIN,
4461 expense_line_rec.CREATION_DATE,
4462 expense_line_rec.CREATED_BY,
4463 expense_line_rec.STAT_AMOUNT,
4464 expense_line_rec.PROJECT_ACCOUNTING_CONTEXT,
4465 expense_line_rec.EXPENDITURE_TYPE,
4466 expense_line_rec.EXPENDITURE_ITEM_DATE,
4467 expense_line_rec.PA_QUANTITY,
4468 expense_line_rec.DISTRIBUTION_LINE_NUMBER,
4469 expense_line_rec.REFERENCE_1,
4470 expense_line_rec.REFERENCE_2,
4471 expense_line_rec.AWT_GROUP_ID,
4472 expense_line_rec.ORG_ID,
4473 expense_line_rec.RECEIPT_VERIFIED_FLAG,
4474 expense_line_rec.JUSTIFICATION_REQUIRED_FLAG,
4475 expense_line_rec.RECEIPT_REQUIRED_FLAG,
4476 expense_line_rec.RECEIPT_MISSING_FLAG,
4477 expense_line_rec.JUSTIFICATION,
4478 expense_line_rec.EXPENSE_GROUP,
4479 expense_line_rec.START_EXPENSE_DATE,
4480 expense_line_rec.END_EXPENSE_DATE,
4481 expense_line_rec.RECEIPT_CURRENCY_CODE,
4482 expense_line_rec.RECEIPT_CONVERSION_RATE,
4483 expense_line_rec.DAILY_AMOUNT,
4484 expense_line_rec.RECEIPT_CURRENCY_AMOUNT,
4485 expense_line_rec.WEB_PARAMETER_ID,
4486 expense_line_rec.GLOBAL_ATTRIBUTE_CATEGORY,
4487 expense_line_rec.GLOBAL_ATTRIBUTE1,
4488 expense_line_rec.GLOBAL_ATTRIBUTE2,
4489 expense_line_rec.GLOBAL_ATTRIBUTE3,
4490 expense_line_rec.GLOBAL_ATTRIBUTE4,
4491 expense_line_rec.GLOBAL_ATTRIBUTE5,
4492 expense_line_rec.GLOBAL_ATTRIBUTE6,
4493 expense_line_rec.GLOBAL_ATTRIBUTE7,
4494 expense_line_rec.GLOBAL_ATTRIBUTE8,
4495 expense_line_rec.GLOBAL_ATTRIBUTE9,
4496 expense_line_rec.GLOBAL_ATTRIBUTE10,
4497 expense_line_rec.AMOUNT_INCLUDES_TAX_FLAG,
4498 expense_line_rec.GLOBAL_ATTRIBUTE11,
4499 expense_line_rec.GLOBAL_ATTRIBUTE12,
4500 expense_line_rec.GLOBAL_ATTRIBUTE13,
4501 expense_line_rec.GLOBAL_ATTRIBUTE14,
4502 expense_line_rec.GLOBAL_ATTRIBUTE15,
4503 expense_line_rec.GLOBAL_ATTRIBUTE16,
4504 expense_line_rec.GLOBAL_ATTRIBUTE17,
4505 expense_line_rec.GLOBAL_ATTRIBUTE18,
4506 expense_line_rec.GLOBAL_ATTRIBUTE19,
4507 expense_line_rec.GLOBAL_ATTRIBUTE20,
4508 expense_line_rec.ADJUSTMENT_REASON,
4509 expense_line_rec.POLICY_SHORTPAY_FLAG,
4510 expense_line_rec.MERCHANT_DOCUMENT_NUMBER,
4511 expense_line_rec.MERCHANT_NAME,
4512 expense_line_rec.MERCHANT_REFERENCE,
4513 expense_line_rec.MERCHANT_TAX_REG_NUMBER,
4514 expense_line_rec.MERCHANT_TAXPAYER_ID,
4515 expense_line_rec.COUNTRY_OF_SUPPLY,
4516 expense_line_rec.TAX_CODE_OVERRIDE_FLAG,
4517 expense_line_rec.TAX_CODE_ID,
4518 expense_line_rec.CREDIT_CARD_TRX_ID,
4519 expense_line_rec.ALLOCATION_REASON,
4520 expense_line_rec.ALLOCATION_SPLIT_CODE,
4521 expense_line_rec.PROJECT_NAME,
4522 expense_line_rec.TASK_NAME,
4523 expense_line_rec.COMPANY_PREPAID_INVOICE_ID,
4524 expense_line_rec.PA_INTERFACED_FLAG,
4525 expense_line_rec.PROJECT_NUMBER,
4526 expense_line_rec.TASK_NUMBER,
4527 expense_line_rec.AWARD_NUMBER,
4528 expense_line_rec.VEHICLE_CATEGORY_CODE,
4529 expense_line_rec.VEHICLE_TYPE,
4530 expense_line_rec.FUEL_TYPE,
4531 expense_line_rec.NUMBER_PEOPLE,
4532 expense_line_rec.DAILY_DISTANCE,
4533 expense_line_rec.DISTANCE_UNIT_CODE,
4534 expense_line_rec.AVG_MILEAGE_RATE,
4535 expense_line_rec.DESTINATION_FROM,
4536 expense_line_rec.DESTINATION_TO,
4537 expense_line_rec.TRIP_DISTANCE,
4538 expense_line_rec.LICENSE_PLATE_NUMBER,
4539 expense_line_rec.MILEAGE_RATE_ADJUSTED_FLAG,
4540 expense_line_rec.LOCATION_ID,
4541 expense_line_rec.NUM_PDM_DAYS1,
4542 expense_line_rec.NUM_PDM_DAYS2,
4543 expense_line_rec.NUM_PDM_DAYS3,
4544 expense_line_rec.PER_DIEM_RATE1,
4545 expense_line_rec.PER_DIEM_RATE2,
4546 expense_line_rec.PER_DIEM_RATE3,
4547 expense_line_rec.DEDUCTION_ADDITION_AMT1,
4548 expense_line_rec.DEDUCTION_ADDITION_AMT2,
4549 expense_line_rec.DEDUCTION_ADDITION_AMT3,
4550 expense_line_rec.NUM_FREE_BREAKFASTS1,
4551 expense_line_rec.NUM_FREE_LUNCHES1,
4552 expense_line_rec.NUM_FREE_DINNERS1,
4553 expense_line_rec.NUM_FREE_ACCOMMODATIONS1,
4554 expense_line_rec.NUM_FREE_BREAKFASTS2,
4555 expense_line_rec.NUM_FREE_LUNCHES2,
4556 expense_line_rec.NUM_FREE_DINNERS2,
4557 expense_line_rec.NUM_FREE_ACCOMMODATIONS2,
4558 expense_line_rec.NUM_FREE_BREAKFASTS3,
4559 expense_line_rec.NUM_FREE_LUNCHES3,
4560 expense_line_rec.NUM_FREE_DINNERS3,
4561 expense_line_rec.NUM_FREE_ACCOMMODATIONS3,
4562 expense_line_rec.ATTENDEES,
4563 expense_line_rec.NUMBER_ATTENDEES,
4564 expense_line_rec.TRAVEL_TYPE,
4565 expense_line_rec.TICKET_CLASS_CODE,
4566 expense_line_rec.TICKET_NUMBER,
4567 expense_line_rec.FLIGHT_NUMBER,
4568 expense_line_rec.LOCATION_TO_ID,
4569 expense_line_rec.ITEMIZATION_PARENT_ID,
4570 expense_line_rec.FLEX_CONCATENATED,
4571 expense_line_rec.FUNC_CURRENCY_AMT,
4572 expense_line_rec.LOCATION,
4573 expense_line_rec.CATEGORY_CODE,
4574 expense_line_rec.ADJUSTMENT_REASON_CODE,
4575 expense_line_rec.AP_VALIDATION_ERROR,
4576 expense_line_rec.SUBMITTED_AMOUNT,
4577 ap_expense_report_lines_s.nextval);
4578
4579 EXCEPTION
4580 WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
4581 AP_WEB_DB_UTIL_PKG.RaiseException('InsertLine','',
4582 'AP_WEB_SAVESUB_DELETE_FAILED');
4583 APP_EXCEPTION.RAISE_EXCEPTION;
4584 WHEN OTHERS THEN
4585 AP_WEB_DB_UTIL_PKG.RaiseException('InsertLine',
4586 l_debug_Info);
4587 APP_EXCEPTION.RAISE_EXCEPTION;
4588
4589 END InsertLine;
4590
4591 /**
4592 * jrautiai ADJ Fix
4593 * Modified the amount calculating routine to centralize the different payment scenario
4594 * calculations in one place.
4595 */
4596 FUNCTION CalculateAmtsDue(p_report_header_id IN expLines_headerID,
4597 p_payment_due_from IN VARCHAR2,
4598 p_emp_amt OUT NOCOPY NUMBER,
4599 p_ccard_amt OUT NOCOPY NUMBER,
4600 p_total_amt OUT NOCOPY NUMBER
4601 ) RETURN BOOLEAN IS
4602 l_personal_total NUMBER := 0;
4603 l_cash_amt NUMBER := 0;
4604 l_ccard_amt NUMBER := 0;
4605 l_total_amt NUMBER := 0;
4606 l_company_pay VARCHAR2(100) := AP_WEB_EXPENSE_WF.C_CompanyPay;
4607 l_personalParameterId AP_WEB_DB_EXPTEMPLATE_PKG.expTempl_paramID;
4608 BEGIN
4609
4610 IF (NOT AP_WEB_DB_EXPTEMPLATE_PKG.GetPersonalParamID(l_personalParameterId)) THEN
4611 l_personalParameterId := fnd_api.G_MISS_NUM;
4612 END IF;
4613
4614 /**
4615 * Calculate the following amounts:
4616 * l_cash_amt = The total reimbursable amount of cash lines on the report
4617 * l_ccard_amt = The total reimbursable amount of credit card lines on the report.
4618 * For company pay scenario this amount is always the original total
4619 * amount, since it was already paid to the cc issuer.
4620 * l_total_amt = The total reimbursable amount of all lines. Since for company pay
4621 * underitemization case we have negative amount for the personal amount
4622 * which is already included in the reimbursable amounts of the other
4623 * underitemized lines it is not included in the calculations.
4624 */
4625 SELECT sum(DECODE(credit_card_trx_id, null,amount,0)),
4626 sum(DECODE(credit_card_trx_id,
4627 null,0,
4628 DECODE(web_parameter_id,
4629 l_personalParameterId,ABS(amount),
4630 amount
4631 )
4632 )
4633 ),
4634 sum(DECODE(web_parameter_id,
4635 l_personalParameterId,0,
4636 AMOUNT))
4637 INTO l_cash_amt, l_ccard_amt, l_total_amt
4638 FROM ap_expense_report_lines
4639 WHERE report_header_id = p_report_header_id
4640 AND (itemization_parent_id is null OR itemization_parent_id <> -1);
4641
4642 l_cash_amt := NVL(l_cash_amt,0);
4643 l_ccard_amt := NVL(l_ccard_amt,0);
4644 l_total_amt := NVL(l_total_amt,0);
4645
4646 /**
4647 * Different calculations are different depending on the payment scenario. Branch the code
4648 * depending on the scenario.
4649 */
4650 IF (p_payment_due_from = AP_WEB_EXPENSE_WF.C_CompanyPay) THEN
4651
4652 IF (NOT AP_WEB_DB_EXPLINE_PKG.GetPersonalTotalOfExpRpt(p_report_header_id, l_personal_total)) THEN
4653 l_personal_total := 0;
4654 END IF;
4655 /**
4656 * For company pay the amount due employee is the total of cash lines subtracted with the personal
4657 * amount created for underitemization or auditor adjustment. In case the personal amount is greater
4658 * than the cash due, the amount due employee is also negative indicating that employee owes the
4659 * company money.
4660 */
4661 p_emp_amt := l_cash_amt - ABS(l_personal_total);
4662
4663 /**
4664 * For company pay the amount due credit card issuer always the original total amount, since it was
4665 * already paid to the cc issuer.
4666 */
4667 p_ccard_amt := l_ccard_amt;
4668 p_total_amt := l_total_amt;
4669
4670 ELSIF (p_payment_due_from = AP_WEB_EXPENSE_WF.C_IndividualPay) THEN
4671
4672 /**
4673 * For individual pay the employee is paying for all the expenses and receiving the reimbursement in cash.
4674 * So the amount due to the employee is the total of the expense report, amount due cc issuer is 0.
4675 */
4676 p_emp_amt := l_total_amt;
4677 p_ccard_amt := 0;
4678 p_total_amt := l_total_amt;
4679
4680 ELSIF (p_payment_due_from = AP_WEB_EXPENSE_WF.C_BothPay) THEN
4681
4682 /**
4683 * For both pay the company is paying for business cc expenses, note this amount includes the auditor
4684 * adjustments, the adjusted amount is displayed to the user as personal cc expense.
4685 */
4686 p_emp_amt := l_cash_amt;
4687 p_ccard_amt := l_ccard_amt;
4688 p_total_amt := l_total_amt;
4689
4690 ELSE
4691 /**
4692 * If payment scenario not provided, populate based on the calculations.
4693 */
4694 p_emp_amt := l_cash_amt;
4695 p_ccard_amt := l_ccard_amt;
4696 p_total_amt := l_total_amt;
4697 END IF;
4698
4699 return TRUE;
4700 EXCEPTION
4701 WHEN NO_DATA_FOUND THEN
4702 p_emp_amt := 0;
4703 p_ccard_amt := 0;
4704 p_total_amt := 0;
4705 return FALSE;
4706 WHEN OTHERS THEN
4707 AP_WEB_DB_UTIL_PKG.RaiseException('CalculateAmtsDue');
4708 APP_EXCEPTION.RAISE_EXCEPTION;
4709 return FALSE;
4710 END CalculateAmtsDue;
4711
4712 /**
4713 * jrautiai ADJ Fix
4714 * Check whether a report has been shortpaid, used in the workflow logic to display messages.
4715 */
4716 FUNCTION GetShortpaidFlag( p_report_header_id IN expLines_headerID,
4717 p_shortpaid_flag OUT NOCOPY VARCHAR2 )
4718 RETURN BOOLEAN IS
4719 BEGIN
4720 select decode(shortpay_parent_id, null, 'N','Y')
4721 into p_shortpaid_flag
4722 from ap_expense_report_headers
4723 where report_header_id = p_report_header_id;
4724
4725 RETURN TRUE;
4726
4727 EXCEPTION
4728 WHEN NO_DATA_FOUND THEN
4729 p_shortpaid_flag := 'N';
4730 return FALSE;
4731
4732 WHEN OTHERS THEN
4733 AP_WEB_DB_UTIL_PKG.RaiseException('GetShortpaidFlag');
4734 APP_EXCEPTION.RAISE_EXCEPTION;
4735 return FALSE;
4736
4737 END GetShortpaidFlag;
4738
4739 -----------------------------------------------------------------------------------------------------
4740 FUNCTION GetNumPolicyShortpaidLines(p_report_header_id IN expLines_headerID,
4741 p_count OUT NOCOPY NUMBER)
4742 RETURN BOOLEAN IS
4743 -----------------------------------------------------------------------------------------------------
4744 BEGIN
4745 SELECT count(*)
4746 INTO p_count
4747 FROM ap_expense_report_lines
4748 WHERE report_header_id = p_report_header_id
4749 AND nvl(policy_shortpay_flag, 'N') = 'Y';
4750
4751 RETURN true;
4752
4753 EXCEPTION
4754 WHEN NO_DATA_FOUND THEN
4755 return FALSE;
4756 WHEN OTHERS THEN
4757 AP_WEB_DB_UTIL_PKG.RaiseException('GetNumPolicyShortpaidLines');
4758 APP_EXCEPTION.RAISE_EXCEPTION;
4759 return FALSE;
4760 END GetNumPolicyShortpaidLines;
4761
4762 FUNCTION GetAdjustedLineExists(p_report_header_id IN expLines_headerID)
4763 RETURN BOOLEAN IS
4764
4765 CURSOR result_cur IS
4766 SELECT sum(amount), sum(nvl(submitted_amount,0))
4767 FROM ap_expense_report_lines aerl
4768 WHERE aerl.report_header_id = p_report_header_id
4769 AND (itemization_parent_id is null OR itemization_parent_id = -1)
4770 AND aerl.credit_card_trx_id is null ;
4771
4772 CURSOR cc_result_cur IS
4773 SELECT report_header_id
4774 FROM ap_expense_report_lines aerl
4775 WHERE aerl.report_header_id = p_report_header_id
4776 AND (itemization_parent_id is null OR itemization_parent_id = -1)
4777 AND aerl.credit_card_trx_id is not null
4778 AND NVL(submitted_amount,amount) <> amount;
4779
4780 cc_result_rec cc_result_cur%ROWTYPE;
4781 l_amount number;
4782 l_submitted_amount number;
4783
4784 BEGIN
4785 /* Bug 3693572 : If the expense report total is unchanged, and if
4786 * cash and other expenses only are adjusted, then do not send the
4787 * notification.
4788 */
4789 IF p_report_header_id is NULL THEN
4790 RETURN FALSE;
4791 END IF;
4792
4793 OPEN cc_result_cur;
4794 FETCH cc_result_cur INTO cc_result_rec;
4795 IF cc_result_cur%FOUND THEN
4796 CLOSE cc_result_cur;
4797 RETURN TRUE;
4798 ELSE
4799 CLOSE cc_result_cur;
4800 OPEN result_cur;
4801 FETCH result_cur INTO l_amount, l_submitted_amount;
4802 IF result_cur%FOUND THEN
4803 CLOSE result_cur;
4804 IF l_amount <> l_submitted_amount THEN
4805 RETURN TRUE;
4806 ELSE
4807 RETURN FALSE;
4808 END IF;
4809 ELSE
4810 CLOSE result_cur;
4811 RETURN FALSE;
4812 END IF;
4813 END IF;
4814 END GetAdjustedLineExists;
4815
4816
4817 PROCEDURE ResetShortpayAdjustmentInfo(p_report_header_id IN expLines_headerID) IS
4818 BEGIN
4819
4820
4821 UPDATE ap_expense_report_lines
4822 SET submitted_amount = amount
4823 WHERE report_header_id = p_report_header_id;
4824
4825 EXCEPTION
4826 WHEN OTHERS THEN
4827 /* If an exception happens there is nothing we can do about it, so catching
4828 * the exception and suppressing it. In case the adjusted amount should have
4829 * been updated, but the update fails for some reason here, the implication is
4830 * that the employee will get a adjustment notification when the shortpaid report
4831 * is audited. Even if the auditor did not adjust the report. */
4832 null;
4833 END ResetShortpayAdjustmentInfo;
4834
4835 /**
4836 * jrautiai ADJ Fix end
4837 */
4838
4839 -------------------------------------------------------------------
4840 FUNCTION GetCountyProvince(
4841 p_addressstyle IN per_addresses.style%TYPE,
4842 p_region IN per_addresses.region_1%TYPE)
4843 RETURN VARCHAR2
4844 AS
4845 l_dflex_r fnd_dflex.dflex_r;
4846 l_dflex_dr fnd_dflex.dflex_dr;
4847 l_contexts_dr fnd_dflex.contexts_dr;
4848 l_segments_dr fnd_dflex.segments_dr;
4849 l_valueset_id fnd_descr_flex_col_usage_vl.flex_value_set_id%TYPE;
4850 l_vset_r fnd_vset.valueset_r;
4851 l_value_dr fnd_vset.value_dr;
4852 l_vset_dr fnd_vset.valueset_dr;
4853 l_resultval VARCHAR2(2000);
4854 l_found BOOLEAN;
4855 l_rowcount NUMBER;
4856 l_region per_addresses.region_1%TYPE;
4857 BEGIN
4858
4859 --Customizing for Guatemala, Costa Rica, Chile
4860 -- Bug: 7365109
4861 l_region := p_region;
4862 IF p_addressstyle = 'CL_GLB' OR p_addressstyle = 'GT_GLB' OR p_addressstyle = 'CR_GLB'
4863 OR AP_WEB_CUST_DFLEX_PKG.CustomGetCountyProvince(p_addressstyle, l_region) then
4864 return l_region;
4865 END IF;
4866
4867 fnd_dflex.get_flexfield('PER','Address Structure',l_dflex_r,l_dflex_dr);
4868 fnd_dflex.get_segments(fnd_dflex.make_context(l_dflex_r,p_addressStyle),l_segments_dr,TRUE);
4869
4870 FOR i IN 1 .. l_segments_dr.nsegments LOOP
4871 IF (UPPER(l_segments_dr.segment_name(i)) IN ('COUNTY','PROVINCE')) THEN
4872 l_valueset_id := l_segments_dr.value_set(i);
4873 EXIT;
4874 END IF;
4875 END LOOP;
4876
4877 fnd_vset.get_valueset(l_valueset_id, l_vset_r, l_vset_dr);
4878
4879 fnd_vset.get_value_init(l_vset_r, TRUE);
4880
4881 fnd_vset.get_value(l_vset_r, l_rowcount, l_found, l_value_dr);
4882 WHILE(l_found) LOOP
4883 IF (l_value_dr.id = p_region) THEN
4884 l_resultval := l_value_dr.value;
4885 EXIT;
4886 END IF;
4887 fnd_vset.get_value(l_vset_r, l_rowcount, l_found, l_value_dr);
4888 END LOOP;
4889
4890 fnd_vset.get_value_end(l_vset_r);
4891 RETURN l_resultval;
4892
4893 EXCEPTION
4894 WHEN NO_DATA_FOUND THEN
4895 RETURN p_region;
4896 WHEN OTHERS THEN
4897 AP_WEB_DB_UTIL_PKG.RaiseException('GetCountyProvince');
4898 APP_EXCEPTION.RAISE_EXCEPTION;
4899 END;
4900
4901
4902
4903
4904 /*Bug 2944363: Defined this function to get Personal Credit Card
4905 Information in Both Pay Scenario.
4906 */
4907
4908 --AMMISHRA - Both Pay Personal Only Lines project.
4909
4910 --------------------------------------------------------------------------------
4911 FUNCTION GetBothPayPersonalLinesCursor(
4912 p_report_header_id IN expLines_headerID,
4913 p_personal_lines_cursor OUT NOCOPY DisplayXpenseLinesCursor)
4914 RETURN BOOLEAN IS
4915 -------------------------------------------------------------------------------
4916 BEGIN
4917
4918 OPEN p_personal_lines_cursor FOR
4919 SELECT to_char(nvl(CC.TRANSACTION_DATE,SYSDATE)),
4920 LPAD(to_char(CC.expensed_amount),9),
4921 CC.billed_currency_code,
4922 nvl(CC.Merchant_name1 , Merchant_name2)
4923 FROM ap_expense_report_headers XH,
4924 ap_credit_card_trxns CC,
4925 ap_lookup_codes alc
4926 WHERE XH.report_header_id = p_report_header_id
4927 AND XH.report_header_id = CC.report_header_id
4928 AND XH.total = 0
4929 AND alc.lookup_type = 'PAYMENT_DUE_FROM'
4930 AND alc.lookup_code = CC.payment_due_from_code
4931 AND alc.lookup_code = 'BOTH'
4932 AND NOT EXISTS(SELECT 1 from AP_EXPENSE_REPORT_LINES XL
4933 WHERE XH.report_header_id = XL.report_header_id);
4934 return TRUE;
4935 EXCEPTION
4936 WHEN NO_DATA_FOUND THEN
4937 return FALSE;
4938 WHEN OTHERS THEN
4939 AP_WEB_DB_UTIL_PKG.RaiseException('GetBothPayPersonalLinesCursor');
4940 APP_EXCEPTION.RAISE_EXCEPTION;
4941 return FALSE;
4942 END GetBothPayPersonalLinesCursor;
4943
4944
4945 /*Bug 2944363: Defined this function to get Personal Credit Card
4946 Information in Both Pay Scenario.
4947 */
4948 --AMMISHRA - Both Pay Personal Only Lines project.
4949 -------------------------------------------------------------------
4950 FUNCTION GetNoOfBothPayPersonalLines(p_report_header_id IN expLines_headerID,
4951 p_personal_count OUT NOCOPY NUMBER)
4952 RETURN BOOLEAN IS
4953 -------------------------------------------------------------------
4954 BEGIN
4955
4956 SELECT count(*)
4957 INTO p_personal_count
4958 FROM ap_expense_report_headers XH,
4959 ap_credit_card_trxns CC,
4960 ap_lookup_codes alc
4961 WHERE XH.report_header_id = p_report_header_id
4962 AND XH.report_header_id = CC.report_header_id
4963 AND XH.total = 0
4964 AND alc.lookup_type = 'PAYMENT_DUE_FROM'
4965 AND alc.lookup_code = CC.payment_due_from_code
4966 AND alc.lookup_code = 'BOTH'
4967 AND NOT EXISTS(SELECT 1 from AP_EXPENSE_REPORT_LINES XL
4968 WHERE XH.report_header_id = XL.report_header_id);
4969 return true;
4970
4971 EXCEPTION
4972 WHEN NO_DATA_FOUND THEN
4973 return FALSE;
4974 WHEN OTHERS THEN
4975 AP_WEB_DB_UTIL_PKG.RaiseException('GetNoOfBothPayPersonalLines');
4976 APP_EXCEPTION.RAISE_EXCEPTION;
4977 return FALSE;
4978 END GetNoOfBothPayPersonalLines;
4979
4980 -----------------------------------------------------------------------------
4981 /*Written By :Ron Langi
4982 Purpose :Clears the Audit Return Reason and Instruction
4983 using the report_header_id.
4984 */
4985 -----------------------------------------------------------------------------
4986 PROCEDURE clearAuditReturnReasonInstr(
4987 p_report_header_id IN expLines_headerID)
4988 IS
4989
4990 BEGIN
4991 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_DB_EXPRPT_PKG', 'start clearAuditReturnReasonInstr');
4992
4993 UPDATE ap_expense_report_lines aerl
4994 SET aerl.adjustment_reason_code = '',
4995 aerl.adjustment_reason = '',
4996 aerl.policy_shortpay_flag = '' -- Bug 3683276
4997 WHERE aerl.report_header_id = p_report_header_id;
4998
4999 EXCEPTION
5000 WHEN NO_DATA_FOUND THEN
5001 RETURN ;
5002 WHEN OTHERS THEN
5003 AP_WEB_DB_UTIL_PKG.RaiseException('clearAuditReturnReasonInstr');
5004 APP_EXCEPTION.RAISE_EXCEPTION;
5005 END clearAuditReturnReasonInstr;
5006
5007 /**
5008 * aling
5009 * Check to see if there are any policy violation
5010 */
5011 --------------------------------------------------------------------------------
5012 FUNCTION AnyPolicyViolation(p_report_header_id IN expLines_headerID)
5013 RETURN BOOLEAN IS
5014 --------------------------------------------------------------------------------
5015 l_temp VARCHAR2(1);
5016 BEGIN
5017
5018 SELECT 'Y'
5019 INTO l_temp
5020 FROM ap_pol_violations
5021 WHERE report_header_id = p_report_header_id
5022 and rownum = 1;
5023
5024 return TRUE;
5025
5026 EXCEPTION
5027 WHEN NO_DATA_FOUND THEN
5028 return FALSE;
5029 WHEN OTHERS THEN
5030 AP_WEB_DB_UTIL_PKG.RaiseException('AnyPolicyViolation');
5031 APP_EXCEPTION.RAISE_EXCEPTION;
5032 return FALSE;
5033 END AnyPolicyViolation;
5034
5035 --------------------------------------------------------------------------------
5036 FUNCTION GetLineCCIDCursor(p_reportId IN expLines_headerID,
5037 p_line_cursor OUT NOCOPY ExpLineCCIDCursor)
5038 RETURN BOOLEAN IS
5039 --------------------------------------------------------------------------------
5040 BEGIN
5041
5042 OPEN p_line_cursor FOR
5043 SELECT AERL.report_line_id,
5044 AERL.code_combination_id,
5045 AERL.amount
5046 FROM AP_EXPENSE_REPORT_LINES AERL
5047 WHERE REPORT_HEADER_ID = p_reportId;
5048
5049 return TRUE;
5050
5051 EXCEPTION
5052 WHEN NO_DATA_FOUND THEN
5053 return FALSE;
5054 WHEN OTHERS THEN
5055 AP_WEB_DB_UTIL_PKG.RaiseException('GetLineCCIDCursor');
5056 APP_EXCEPTION.RAISE_EXCEPTION;
5057 return FALSE;
5058 END GetLineCCIDCursor;
5059
5060 -------------------------------------------------------------------
5061 PROCEDURE resetApplyAdvances(
5062 p_report_header_id IN expLines_headerID) IS
5063 -------------------------------------------------------------------
5064 BEGIN
5065
5066 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'start resetApplyAdvances');
5067
5068 -- bug : 4001778/3654956
5069 UPDATE ap_expense_report_headers_all
5070 SET maximum_amount_to_apply = null,
5071 advance_invoice_to_apply = null,
5072 apply_advances_default = null,
5073 prepay_apply_flag = null,
5074 prepay_num = null,
5075 prepay_dist_num = null,
5076 prepay_apply_amount = null,
5077 prepay_gl_date = null,
5078 advances_justification = null
5079 WHERE report_header_id = p_report_header_id;
5080
5081 AP_WEB_UTILITIES_PKG.LogProcedure('AP_WEB_DB_EXPLINE_PKG', 'end resetApplyAdvances');
5082
5083 EXCEPTION
5084 WHEN NO_DATA_FOUND THEN
5085 AP_WEB_DB_UTIL_PKG.RaiseException('resetApplyAdvances');
5086 APP_EXCEPTION.RAISE_EXCEPTION;
5087 WHEN OTHERS THEN
5088 AP_WEB_DB_UTIL_PKG.RaiseException('resetApplyAdvances');
5089 APP_EXCEPTION.RAISE_EXCEPTION;
5090
5091 END resetApplyAdvances;
5092
5093 -- 5666256: fp of 5464957 when accessing confirmation page from e-mail or comming from DBI
5094 -- or other resp that have profile OIE;Credit Cards set to N/null then the CC lines
5095 -- would not be displayed even if they exist in the report.
5096 FUNCTION ReportInclsCCardLines(p_report_header_id IN NUMBER) RETURN VARCHAR2 IS
5097 l_cc_lines_exist VARCHAR2(1);
5098 BEGIN
5099 l_cc_lines_exist := 'N';
5100
5101 select 'Y' into l_cc_lines_exist
5102 from ap_expense_report_lines_all
5103 where report_header_id = p_report_header_id
5104 and credit_card_trx_id is not null
5105 and rownum = 1;
5106
5107 RETURN l_cc_lines_exist;
5108
5109 EXCEPTION
5110 WHEN OTHERS THEN
5111 RETURN l_cc_lines_exist;
5112
5113 END ReportInclsCCardLines;
5114
5115 END AP_WEB_DB_EXPLINE_PKG;