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