[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_ARCHIVE_PURGE_ER
Source
1 PACKAGE BODY AP_WEB_ARCHIVE_PURGE_ER AS
2 /* $Header: apwxprgb.pls 120.3.12020000.2 2012/07/05 14:36:52 rveliche ship $ */
3
4 PROCEDURE ArchiveData(p_source_date IN DATE,
5 p_org_id IN NUMBER,
6 p_request_id IN NUMBER) IS
7
8
9 l_debug_info VARCHAR2(2000);
10
11 BEGIN
12
13 -- Use GT for best performance, insert into select from can be used.
14
15 ---------------------------------------------------------------------
16 l_debug_info := 'Insert into the GT table.';
17 ---------------------------------------------------------------------
18 IF (p_org_id IS NULL) THEN
19 insert into AP_EXP_REPORT_HEADERS_GT
20 (report_header_id)
21 (select report_header_id
22 from
23 (
24 select report_header_id
25 from ap_expense_report_headers_all
26 where source in ('CREDIT CARD', 'SelfService', 'XpenseXpress')
27 and trunc(creation_date) < p_source_date
28 and expense_status_code = 'PAID'
29 UNION
30 -- For Both Pay reports, make sure that the parent report is also paid.
31 select a.report_header_id
32 from ap_expense_report_headers_all a,
33 ap_expense_report_headers_all b
34 where a.source = 'Both Pay'
35 and trunc(a.creation_date) < p_source_date
36 and a.expense_status_code = 'PAID'
37 and a.bothpay_parent_id = b.report_header_id
38 and b.expense_status_code = 'PAID'
39 and trunc(b.creation_date) < p_source_date
40 ));
41 ELSE
42 insert into AP_EXP_REPORT_HEADERS_GT
43 (report_header_id)
44 (select report_header_id
45 from
46 (
47 select report_header_id
48 from ap_expense_report_headers_all
49 where source in ('CREDIT CARD', 'SelfService', 'XpenseXpress')
50 and trunc(creation_date) < p_source_date
51 and org_id = p_org_id
52 and expense_status_code = 'PAID'
53 UNION
54 -- For Both Pay reports, make sure that the parent report is also paid.
55 select a.report_header_id
56 from ap_expense_report_headers_all a,
57 ap_expense_report_headers_all b
58 where a.source = 'Both Pay'
59 and trunc(a.creation_date) < p_source_date
60 and a.org_id = p_org_id
61 and a.expense_status_code = 'PAID'
62 and a.bothpay_parent_id = b.report_header_id
63 and b.expense_status_code = 'PAID'
64 and trunc(b.creation_date) < p_source_date
65 ));
66
67 END IF;
68
69 -- Insert into Headers
70 ---------------------------------------------------------------------
71 l_debug_info := 'Insert into Headers.';
72 ---------------------------------------------------------------------
73
74 insert into ap_expense_report_headers_arc (
75 ARC_REQ_ID,
76 ARCHIVE_DATE,
77 REPORT_HEADER_ID,
78 EMPLOYEE_ID,
79 WEEK_END_DATE,
80 CREATION_DATE,
81 CREATED_BY,
82 LAST_UPDATE_DATE,
83 LAST_UPDATED_BY,
84 VOUCHNO,
85 TOTAL,
86 VENDOR_ID,
87 VENDOR_SITE_ID,
88 EXPENSE_CHECK_ADDRESS_FLAG,
89 REFERENCE_1,
90 REFERENCE_2,
91 INVOICE_NUM,
92 EXPENSE_REPORT_ID,
93 ACCTS_PAY_CODE_COMBINATION_ID,
94 SET_OF_BOOKS_ID,
95 SOURCE,
96 PURGEABLE_FLAG,
97 ACCOUNTING_DATE,
98 MAXIMUM_AMOUNT_TO_APPLY,
99 ADVANCE_INVOICE_TO_APPLY,
100 APPLY_ADVANCES_DEFAULT,
101 EMPLOYEE_CCID,
102 DESCRIPTION,
103 REJECT_CODE,
104 HOLD_LOOKUP_CODE,
105 ATTRIBUTE_CATEGORY,
106 ATTRIBUTE1,
107 ATTRIBUTE2,
108 ATTRIBUTE3,
109 ATTRIBUTE4,
110 ATTRIBUTE5,
111 ATTRIBUTE6,
112 ATTRIBUTE7,
113 ATTRIBUTE8,
114 ATTRIBUTE9,
115 ATTRIBUTE10,
116 ATTRIBUTE11,
117 ATTRIBUTE12,
118 ATTRIBUTE13,
119 ATTRIBUTE14,
120 ATTRIBUTE15,
121 DEFAULT_CURRENCY_CODE,
122 DEFAULT_EXCHANGE_RATE_TYPE,
123 DEFAULT_EXCHANGE_RATE,
124 DEFAULT_EXCHANGE_DATE,
125 LAST_UPDATE_LOGIN,
126 VOUCHER_NUM,
127 USSGL_TRANSACTION_CODE,
128 USSGL_TRX_CODE_CONTEXT,
129 DOC_CATEGORY_CODE,
130 AWT_GROUP_ID,
131 ORG_ID,
132 WORKFLOW_APPROVED_FLAG,
133 GLOBAL_ATTRIBUTE_CATEGORY,
134 GLOBAL_ATTRIBUTE1,
135 GLOBAL_ATTRIBUTE2,
136 GLOBAL_ATTRIBUTE3,
137 GLOBAL_ATTRIBUTE4,
138 GLOBAL_ATTRIBUTE5,
139 GLOBAL_ATTRIBUTE6,
140 GLOBAL_ATTRIBUTE7,
141 GLOBAL_ATTRIBUTE8,
142 GLOBAL_ATTRIBUTE9,
143 GLOBAL_ATTRIBUTE10,
144 GLOBAL_ATTRIBUTE11,
145 GLOBAL_ATTRIBUTE12,
146 GLOBAL_ATTRIBUTE13,
147 GLOBAL_ATTRIBUTE14,
148 GLOBAL_ATTRIBUTE15,
149 GLOBAL_ATTRIBUTE16,
150 GLOBAL_ATTRIBUTE17,
151 GLOBAL_ATTRIBUTE18,
152 GLOBAL_ATTRIBUTE19,
153 GLOBAL_ATTRIBUTE20,
154 FLEX_CONCATENATED,
155 OVERRIDE_APPROVER_ID,
156 PAYMENT_CROSS_RATE_TYPE,
157 PAYMENT_CROSS_RATE_DATE,
158 PAYMENT_CROSS_RATE,
159 PAYMENT_CURRENCY_CODE,
160 CORE_WF_STATUS_FLAG,
161 PREPAY_APPLY_FLAG,
162 PREPAY_NUM,
163 PREPAY_DIST_NUM,
164 PREPAY_APPLY_AMOUNT,
165 PREPAY_GL_DATE,
166 BOTHPAY_PARENT_ID,
167 SHORTPAY_PARENT_ID,
168 PAID_ON_BEHALF_EMPLOYEE_ID,
169 OVERRIDE_APPROVER_NAME,
170 AMT_DUE_CCARD_COMPANY,
171 AMT_DUE_EMPLOYEE,
172 DEFAULT_RECEIPT_CURRENCY_CODE,
173 MULTIPLE_CURRENCIES_FLAG,
174 EXPENSE_STATUS_CODE,
175 EXPENSE_LAST_STATUS_DATE,
176 EXPENSE_CURRENT_APPROVER_ID,
177 REPORT_FILING_NUMBER,
178 RECEIPTS_RECEIVED_DATE,
179 AUDIT_CODE,
180 REPORT_SUBMITTED_DATE,
181 LAST_AUDITED_BY,
182 RETURN_REASON_CODE,
183 RETURN_INSTRUCTION,
184 RECEIPTS_STATUS,
185 HOLDING_REPORT_HEADER_ID,
186 REQUEST_ID,
187 ADVANCES_JUSTIFICATION,
188 IMAGE_RECEIPTS_STATUS,
189 IMAGE_RECEIPTS_RECEIVED_DATE,
190 MISSING_IMG_JUST,
191 OVERDUE_REQUEST_ID
192 )
193 (select p_request_id arc_req_id, sysdate archive_date,
194 A.REPORT_HEADER_ID,
195 EMPLOYEE_ID,
196 WEEK_END_DATE,
197 CREATION_DATE,
198 CREATED_BY,
199 LAST_UPDATE_DATE,
200 LAST_UPDATED_BY,
201 VOUCHNO,
202 TOTAL,
203 VENDOR_ID,
204 VENDOR_SITE_ID,
205 EXPENSE_CHECK_ADDRESS_FLAG,
206 REFERENCE_1,
207 REFERENCE_2,
208 INVOICE_NUM,
209 EXPENSE_REPORT_ID,
210 ACCTS_PAY_CODE_COMBINATION_ID,
211 SET_OF_BOOKS_ID,
212 SOURCE,
213 PURGEABLE_FLAG,
214 ACCOUNTING_DATE,
215 MAXIMUM_AMOUNT_TO_APPLY,
216 ADVANCE_INVOICE_TO_APPLY,
217 APPLY_ADVANCES_DEFAULT,
218 EMPLOYEE_CCID,
219 DESCRIPTION,
220 REJECT_CODE,
221 HOLD_LOOKUP_CODE,
222 ATTRIBUTE_CATEGORY,
223 ATTRIBUTE1,
224 ATTRIBUTE2,
225 ATTRIBUTE3,
226 ATTRIBUTE4,
227 ATTRIBUTE5,
228 ATTRIBUTE6,
229 ATTRIBUTE7,
230 ATTRIBUTE8,
231 ATTRIBUTE9,
232 ATTRIBUTE10,
233 ATTRIBUTE11,
234 ATTRIBUTE12,
235 ATTRIBUTE13,
236 ATTRIBUTE14,
237 ATTRIBUTE15,
238 DEFAULT_CURRENCY_CODE,
239 DEFAULT_EXCHANGE_RATE_TYPE,
240 DEFAULT_EXCHANGE_RATE,
241 DEFAULT_EXCHANGE_DATE,
242 LAST_UPDATE_LOGIN,
243 VOUCHER_NUM,
244 USSGL_TRANSACTION_CODE,
245 USSGL_TRX_CODE_CONTEXT,
246 DOC_CATEGORY_CODE,
247 AWT_GROUP_ID,
248 ORG_ID,
249 WORKFLOW_APPROVED_FLAG,
250 GLOBAL_ATTRIBUTE_CATEGORY,
251 GLOBAL_ATTRIBUTE1,
252 GLOBAL_ATTRIBUTE2,
253 GLOBAL_ATTRIBUTE3,
254 GLOBAL_ATTRIBUTE4,
255 GLOBAL_ATTRIBUTE5,
256 GLOBAL_ATTRIBUTE6,
257 GLOBAL_ATTRIBUTE7,
258 GLOBAL_ATTRIBUTE8,
259 GLOBAL_ATTRIBUTE9,
260 GLOBAL_ATTRIBUTE10,
261 GLOBAL_ATTRIBUTE11,
262 GLOBAL_ATTRIBUTE12,
263 GLOBAL_ATTRIBUTE13,
264 GLOBAL_ATTRIBUTE14,
265 GLOBAL_ATTRIBUTE15,
266 GLOBAL_ATTRIBUTE16,
267 GLOBAL_ATTRIBUTE17,
268 GLOBAL_ATTRIBUTE18,
269 GLOBAL_ATTRIBUTE19,
270 GLOBAL_ATTRIBUTE20,
271 FLEX_CONCATENATED,
272 OVERRIDE_APPROVER_ID,
273 PAYMENT_CROSS_RATE_TYPE,
274 PAYMENT_CROSS_RATE_DATE,
275 PAYMENT_CROSS_RATE,
276 PAYMENT_CURRENCY_CODE,
277 CORE_WF_STATUS_FLAG,
278 PREPAY_APPLY_FLAG,
279 PREPAY_NUM,
280 PREPAY_DIST_NUM,
281 PREPAY_APPLY_AMOUNT,
282 PREPAY_GL_DATE,
283 BOTHPAY_PARENT_ID,
284 SHORTPAY_PARENT_ID,
285 PAID_ON_BEHALF_EMPLOYEE_ID,
286 OVERRIDE_APPROVER_NAME,
287 AMT_DUE_CCARD_COMPANY,
288 AMT_DUE_EMPLOYEE,
289 DEFAULT_RECEIPT_CURRENCY_CODE,
290 MULTIPLE_CURRENCIES_FLAG,
291 EXPENSE_STATUS_CODE,
292 EXPENSE_LAST_STATUS_DATE,
293 EXPENSE_CURRENT_APPROVER_ID,
294 REPORT_FILING_NUMBER,
295 RECEIPTS_RECEIVED_DATE,
296 AUDIT_CODE,
297 REPORT_SUBMITTED_DATE,
298 LAST_AUDITED_BY,
299 RETURN_REASON_CODE,
300 RETURN_INSTRUCTION,
301 RECEIPTS_STATUS,
302 HOLDING_REPORT_HEADER_ID,
303 REQUEST_ID,
304 ADVANCES_JUSTIFICATION,
305 IMAGE_RECEIPTS_STATUS,
306 IMAGE_RECEIPTS_RECEIVED_DATE,
307 MISSING_IMG_JUST,
308 OVERDUE_REQUEST_ID
309 from ap_expense_report_headers_all a, AP_EXP_REPORT_HEADERS_GT gt
310 where gt.report_header_id = a.report_header_id);
311 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Headers');
312
313 -- Insert into Lines
314 ----------------------------------------------------------------------
315 l_debug_info := 'Insert into Lines.';
316 ----------------------------------------------------------------------
317 insert into ap_expense_report_lines_arc (
318 ARC_REQ_ID,
319 ARCHIVE_DATE,
320 REPORT_HEADER_ID,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 CODE_COMBINATION_ID,
324 ITEM_DESCRIPTION,
325 SET_OF_BOOKS_ID,
326 AMOUNT,
327 ATTRIBUTE_CATEGORY,
328 ATTRIBUTE1,
329 ATTRIBUTE2,
330 ATTRIBUTE3,
331 ATTRIBUTE4,
332 ATTRIBUTE5,
333 ATTRIBUTE6,
334 ATTRIBUTE7,
335 ATTRIBUTE8,
336 ATTRIBUTE9,
337 ATTRIBUTE10,
338 ATTRIBUTE11,
339 ATTRIBUTE12,
340 ATTRIBUTE13,
341 ATTRIBUTE14,
342 ATTRIBUTE15,
343 CURRENCY_CODE,
344 EXCHANGE_RATE_TYPE,
345 EXCHANGE_RATE,
346 EXCHANGE_DATE,
347 VAT_CODE,
348 LINE_TYPE_LOOKUP_CODE,
349 LAST_UPDATE_LOGIN,
350 CREATION_DATE,
351 CREATED_BY,
352 STAT_AMOUNT,
353 USSGL_TRANSACTION_CODE,
354 USSGL_TRX_CODE_CONTEXT,
355 PROJECT_ACCOUNTING_CONTEXT,
356 PROJECT_ID,
357 TASK_ID,
358 EXPENDITURE_ORGANIZATION_ID,
359 EXPENDITURE_TYPE,
360 EXPENDITURE_ITEM_DATE,
361 PA_QUANTITY,
362 DISTRIBUTION_LINE_NUMBER,
363 REFERENCE_1,
364 REFERENCE_2,
365 AWT_GROUP_ID,
366 ORG_ID,
367 RECEIPT_VERIFIED_FLAG,
368 JUSTIFICATION_REQUIRED_FLAG,
369 RECEIPT_REQUIRED_FLAG,
370 GLOBAL_ATTRIBUTE_CATEGORY,
371 GLOBAL_ATTRIBUTE1,
372 GLOBAL_ATTRIBUTE2,
373 GLOBAL_ATTRIBUTE3,
374 GLOBAL_ATTRIBUTE4,
375 GLOBAL_ATTRIBUTE5,
376 GLOBAL_ATTRIBUTE6,
377 GLOBAL_ATTRIBUTE7,
378 GLOBAL_ATTRIBUTE8,
379 GLOBAL_ATTRIBUTE9,
380 GLOBAL_ATTRIBUTE10,
381 GLOBAL_ATTRIBUTE11,
382 GLOBAL_ATTRIBUTE12,
383 GLOBAL_ATTRIBUTE13,
384 GLOBAL_ATTRIBUTE14,
385 GLOBAL_ATTRIBUTE15,
386 GLOBAL_ATTRIBUTE16,
387 GLOBAL_ATTRIBUTE17,
388 GLOBAL_ATTRIBUTE18,
389 GLOBAL_ATTRIBUTE19,
390 GLOBAL_ATTRIBUTE20,
391 RECEIPT_MISSING_FLAG,
392 JUSTIFICATION,
393 EXPENSE_GROUP,
394 START_EXPENSE_DATE,
395 END_EXPENSE_DATE,
396 RECEIPT_CURRENCY_CODE,
397 RECEIPT_CONVERSION_RATE,
398 DAILY_AMOUNT,
399 RECEIPT_CURRENCY_AMOUNT,
400 WEB_PARAMETER_ID,
401 AMOUNT_INCLUDES_TAX_FLAG,
402 ADJUSTMENT_REASON,
403 POLICY_SHORTPAY_FLAG,
404 AWARD_ID,
405 CREDIT_CARD_TRX_ID,
406 MERCHANT_DOCUMENT_NUMBER,
407 MERCHANT_NAME,
408 MERCHANT_REFERENCE,
409 MERCHANT_TAX_REG_NUMBER,
410 MERCHANT_TAXPAYER_ID,
411 COUNTRY_OF_SUPPLY,
412 TAX_CODE_OVERRIDE_FLAG,
413 TAX_CODE_ID,
414 ITEMIZE_ID,
415 PROJECT_NAME,
416 TASK_NAME,
417 COMPANY_PREPAID_INVOICE_ID,
418 PA_INTERFACED_FLAG,
419 PROJECT_NUMBER,
420 TASK_NUMBER,
421 AWARD_NUMBER,
422 VEHICLE_CATEGORY_CODE,
423 VEHICLE_TYPE,
424 FUEL_TYPE,
425 NUMBER_PEOPLE,
426 DAILY_DISTANCE,
427 DISTANCE_UNIT_CODE,
428 AVG_MILEAGE_RATE,
429 DESTINATION_FROM,
430 DESTINATION_TO,
431 TRIP_DISTANCE,
432 LICENSE_PLATE_NUMBER,
433 MILEAGE_RATE_ADJUSTED_FLAG,
434 LOCATION_ID,
435 NUM_PDM_DAYS1,
436 NUM_PDM_DAYS2,
437 NUM_PDM_DAYS3,
438 PER_DIEM_RATE1,
439 PER_DIEM_RATE2,
440 PER_DIEM_RATE3,
441 DEDUCTION_ADDITION_AMT1,
442 DEDUCTION_ADDITION_AMT2,
443 DEDUCTION_ADDITION_AMT3,
444 NUM_FREE_BREAKFASTS1,
445 NUM_FREE_LUNCHES1,
446 NUM_FREE_DINNERS1,
447 NUM_FREE_ACCOMMODATIONS1,
448 NUM_FREE_BREAKFASTS2,
449 NUM_FREE_LUNCHES2,
450 NUM_FREE_DINNERS2,
451 NUM_FREE_ACCOMMODATIONS2,
452 NUM_FREE_BREAKFASTS3,
453 NUM_FREE_LUNCHES3,
454 NUM_FREE_DINNERS3,
455 NUM_FREE_ACCOMMODATIONS3,
456 ATTENDEES,
457 NUMBER_ATTENDEES,
458 TRAVEL_TYPE,
459 TICKET_CLASS_CODE,
460 TICKET_NUMBER,
461 FLIGHT_NUMBER,
462 LOCATION_TO_ID,
463 ITEMIZATION_PARENT_ID,
464 FLEX_CONCATENATED,
465 FUNC_CURRENCY_AMT,
466 LOCATION,
467 CATEGORY_CODE,
468 ADJUSTMENT_REASON_CODE,
469 AP_VALIDATION_ERROR,
470 SUBMITTED_AMOUNT,
471 REPORT_LINE_ID,
472 ALLOCATION_REASON,
473 ALLOCATION_SPLIT_CODE,
474 RATE_PER_PASSENGER,
475 PASSENGER_RATE_TYPE,
476 PASSENGER_AMOUNT,
477 RANGE_LOW,
478 RANGE_HIGH,
479 UOM_DAYS,
480 IMAGE_RECEIPT_REQUIRED_FLAG,
481 RECEIPTS_ISSUE,
482 ADJUSTMENT_REASON_COPY
483 )
484 (select p_request_id arc_req_id, sysdate archive_date,
485 A.REPORT_HEADER_ID,
486 LAST_UPDATE_DATE,
487 LAST_UPDATED_BY,
488 CODE_COMBINATION_ID,
489 ITEM_DESCRIPTION,
490 SET_OF_BOOKS_ID,
491 AMOUNT,
492 ATTRIBUTE_CATEGORY,
493 ATTRIBUTE1,
494 ATTRIBUTE2,
495 ATTRIBUTE3,
496 ATTRIBUTE4,
497 ATTRIBUTE5,
498 ATTRIBUTE6,
499 ATTRIBUTE7,
500 ATTRIBUTE8,
501 ATTRIBUTE9,
502 ATTRIBUTE10,
503 ATTRIBUTE11,
504 ATTRIBUTE12,
505 ATTRIBUTE13,
506 ATTRIBUTE14,
507 ATTRIBUTE15,
508 CURRENCY_CODE,
509 EXCHANGE_RATE_TYPE,
510 EXCHANGE_RATE,
511 EXCHANGE_DATE,
512 VAT_CODE,
513 LINE_TYPE_LOOKUP_CODE,
514 LAST_UPDATE_LOGIN,
515 CREATION_DATE,
516 CREATED_BY,
517 STAT_AMOUNT,
518 USSGL_TRANSACTION_CODE,
519 USSGL_TRX_CODE_CONTEXT,
520 PROJECT_ACCOUNTING_CONTEXT,
521 PROJECT_ID,
522 TASK_ID,
523 EXPENDITURE_ORGANIZATION_ID,
524 EXPENDITURE_TYPE,
525 EXPENDITURE_ITEM_DATE,
526 PA_QUANTITY,
527 DISTRIBUTION_LINE_NUMBER,
528 REFERENCE_1,
529 REFERENCE_2,
530 AWT_GROUP_ID,
531 ORG_ID,
532 RECEIPT_VERIFIED_FLAG,
533 JUSTIFICATION_REQUIRED_FLAG,
534 RECEIPT_REQUIRED_FLAG,
535 GLOBAL_ATTRIBUTE_CATEGORY,
536 GLOBAL_ATTRIBUTE1,
537 GLOBAL_ATTRIBUTE2,
538 GLOBAL_ATTRIBUTE3,
539 GLOBAL_ATTRIBUTE4,
540 GLOBAL_ATTRIBUTE5,
541 GLOBAL_ATTRIBUTE6,
542 GLOBAL_ATTRIBUTE7,
543 GLOBAL_ATTRIBUTE8,
544 GLOBAL_ATTRIBUTE9,
545 GLOBAL_ATTRIBUTE10,
546 GLOBAL_ATTRIBUTE11,
550 GLOBAL_ATTRIBUTE15,
547 GLOBAL_ATTRIBUTE12,
548 GLOBAL_ATTRIBUTE13,
549 GLOBAL_ATTRIBUTE14,
551 GLOBAL_ATTRIBUTE16,
552 GLOBAL_ATTRIBUTE17,
553 GLOBAL_ATTRIBUTE18,
554 GLOBAL_ATTRIBUTE19,
555 GLOBAL_ATTRIBUTE20,
556 RECEIPT_MISSING_FLAG,
557 JUSTIFICATION,
558 EXPENSE_GROUP,
559 START_EXPENSE_DATE,
560 END_EXPENSE_DATE,
561 RECEIPT_CURRENCY_CODE,
562 RECEIPT_CONVERSION_RATE,
563 DAILY_AMOUNT,
564 RECEIPT_CURRENCY_AMOUNT,
565 WEB_PARAMETER_ID,
566 AMOUNT_INCLUDES_TAX_FLAG,
567 ADJUSTMENT_REASON,
568 POLICY_SHORTPAY_FLAG,
569 AWARD_ID,
570 CREDIT_CARD_TRX_ID,
571 MERCHANT_DOCUMENT_NUMBER,
572 MERCHANT_NAME,
573 MERCHANT_REFERENCE,
574 MERCHANT_TAX_REG_NUMBER,
575 MERCHANT_TAXPAYER_ID,
576 COUNTRY_OF_SUPPLY,
577 TAX_CODE_OVERRIDE_FLAG,
578 TAX_CODE_ID,
579 ITEMIZE_ID,
580 PROJECT_NAME,
581 TASK_NAME,
582 COMPANY_PREPAID_INVOICE_ID,
583 PA_INTERFACED_FLAG,
584 PROJECT_NUMBER,
585 TASK_NUMBER,
586 AWARD_NUMBER,
587 VEHICLE_CATEGORY_CODE,
588 VEHICLE_TYPE,
589 FUEL_TYPE,
590 NUMBER_PEOPLE,
591 DAILY_DISTANCE,
592 DISTANCE_UNIT_CODE,
593 AVG_MILEAGE_RATE,
594 DESTINATION_FROM,
595 DESTINATION_TO,
596 TRIP_DISTANCE,
597 LICENSE_PLATE_NUMBER,
598 MILEAGE_RATE_ADJUSTED_FLAG,
599 LOCATION_ID,
600 NUM_PDM_DAYS1,
601 NUM_PDM_DAYS2,
602 NUM_PDM_DAYS3,
603 PER_DIEM_RATE1,
604 PER_DIEM_RATE2,
605 PER_DIEM_RATE3,
606 DEDUCTION_ADDITION_AMT1,
607 DEDUCTION_ADDITION_AMT2,
608 DEDUCTION_ADDITION_AMT3,
609 NUM_FREE_BREAKFASTS1,
610 NUM_FREE_LUNCHES1,
611 NUM_FREE_DINNERS1,
612 NUM_FREE_ACCOMMODATIONS1,
613 NUM_FREE_BREAKFASTS2,
614 NUM_FREE_LUNCHES2,
615 NUM_FREE_DINNERS2,
616 NUM_FREE_ACCOMMODATIONS2,
617 NUM_FREE_BREAKFASTS3,
618 NUM_FREE_LUNCHES3,
619 NUM_FREE_DINNERS3,
620 NUM_FREE_ACCOMMODATIONS3,
621 ATTENDEES,
622 NUMBER_ATTENDEES,
623 TRAVEL_TYPE,
624 TICKET_CLASS_CODE,
625 TICKET_NUMBER,
626 FLIGHT_NUMBER,
627 LOCATION_TO_ID,
628 ITEMIZATION_PARENT_ID,
629 FLEX_CONCATENATED,
630 FUNC_CURRENCY_AMT,
631 LOCATION,
632 CATEGORY_CODE,
633 ADJUSTMENT_REASON_CODE,
634 AP_VALIDATION_ERROR,
635 SUBMITTED_AMOUNT,
636 REPORT_LINE_ID,
637 ALLOCATION_REASON,
638 ALLOCATION_SPLIT_CODE,
639 RATE_PER_PASSENGER,
640 PASSENGER_RATE_TYPE,
641 PASSENGER_AMOUNT,
642 RANGE_LOW,
643 RANGE_HIGH,
644 UOM_DAYS,
645 IMAGE_RECEIPT_REQUIRED_FLAG,
646 RECEIPTS_ISSUE,
647 ADJUSTMENT_REASON_COPY
648 from ap_expense_report_lines_all a, AP_EXP_REPORT_HEADERS_GT gt
649 where gt.report_header_id = a.report_header_id);
650
651 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Lines');
652
653 -- Insert into Dists
654 ----------------------------------------------------------------------
655 l_debug_info := 'Insert into Dists.';
656 ----------------------------------------------------------------------
657
658 insert into ap_exp_report_dists_arc (
659 ARC_REQ_ID,
660 ARCHIVE_DATE,
661 REPORT_HEADER_ID,
662 REPORT_LINE_ID,
663 REPORT_DISTRIBUTION_ID,
664 ORG_ID,
665 SEQUENCE_NUM,
666 LAST_UPDATE_DATE,
667 LAST_UPDATED_BY,
668 CREATION_DATE,
669 CREATED_BY,
670 LAST_UPDATE_LOGIN,
671 CODE_COMBINATION_ID,
672 SEGMENT1,
673 SEGMENT2,
674 SEGMENT3,
675 SEGMENT4,
676 SEGMENT5,
677 SEGMENT6,
678 SEGMENT7,
679 SEGMENT8,
680 SEGMENT9,
681 SEGMENT10,
682 SEGMENT11,
683 SEGMENT12,
684 SEGMENT13,
685 SEGMENT14,
686 SEGMENT15,
687 SEGMENT16,
688 SEGMENT17,
689 SEGMENT18,
690 SEGMENT19,
691 SEGMENT20,
692 SEGMENT21,
693 SEGMENT22,
694 SEGMENT23,
695 SEGMENT24,
696 SEGMENT25,
697 SEGMENT26,
698 SEGMENT27,
699 SEGMENT28,
700 SEGMENT29,
701 SEGMENT30,
702 PREPARER_MODIFIED_FLAG,
703 AMOUNT,
704 PROJECT_ID,
705 TASK_ID,
706 AWARD_ID,
707 EXPENDITURE_ORGANIZATION_ID,
708 COST_CENTER,
709 RECEIPT_CURRENCY_CODE,
710 RECEIPT_CONVERSION_RATE,
711 RECEIPT_CURRENCY_AMOUNT,
712 WEB_PARAMETER_ID
713 )
714 (select p_request_id arc_req_id, sysdate archive_date,
715 A.REPORT_HEADER_ID,
716 REPORT_LINE_ID,
717 REPORT_DISTRIBUTION_ID,
718 ORG_ID,
719 SEQUENCE_NUM,
720 LAST_UPDATE_DATE,
721 LAST_UPDATED_BY,
722 CREATION_DATE,
723 CREATED_BY,
724 LAST_UPDATE_LOGIN,
725 CODE_COMBINATION_ID,
726 SEGMENT1,
727 SEGMENT2,
728 SEGMENT3,
729 SEGMENT4,
730 SEGMENT5,
731 SEGMENT6,
732 SEGMENT7,
733 SEGMENT8,
734 SEGMENT9,
735 SEGMENT10,
736 SEGMENT11,
737 SEGMENT12,
738 SEGMENT13,
739 SEGMENT14,
740 SEGMENT15,
741 SEGMENT16,
742 SEGMENT17,
743 SEGMENT18,
744 SEGMENT19,
745 SEGMENT20,
746 SEGMENT21,
747 SEGMENT22,
748 SEGMENT23,
749 SEGMENT24,
750 SEGMENT25,
751 SEGMENT26,
755 SEGMENT30,
752 SEGMENT27,
753 SEGMENT28,
754 SEGMENT29,
756 PREPARER_MODIFIED_FLAG,
757 AMOUNT,
758 PROJECT_ID,
759 TASK_ID,
760 AWARD_ID,
761 EXPENDITURE_ORGANIZATION_ID,
762 COST_CENTER,
763 RECEIPT_CURRENCY_CODE,
764 RECEIPT_CONVERSION_RATE,
765 RECEIPT_CURRENCY_AMOUNT,
766 WEB_PARAMETER_ID
767 from ap_exp_report_dists_all a, AP_EXP_REPORT_HEADERS_GT gt
768 where gt.report_header_id = a.report_header_id);
769
770 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Dists');
771
772 -- Insert into CC transactions
773 ----------------------------------------------------------------------
774 l_debug_info := 'Insert into CC Transactions.';
775 ----------------------------------------------------------------------
776
777 insert into ap_credit_card_trxns_arc (
778 ARC_REQ_ID,
779 ARCHIVE_DATE,
780 TRX_ID,
781 VALIDATE_CODE,
782 CARD_PROGRAM_ID,
783 EXPENSED_AMOUNT,
784 CARD_NUMBER,
785 REFERENCE_NUMBER,
786 TRANSACTION_TYPE,
787 TRANSACTION_DATE,
788 TRANSACTION_AMOUNT,
789 DEBIT_FLAG,
790 BILLED_DATE,
791 BILLED_AMOUNT,
792 BILLED_DECIMAL,
793 BILLED_CURRENCY_CODE,
794 POSTED_DATE,
795 POSTED_AMOUNT,
796 POSTED_DECIMAL,
797 POSTED_CURRENCY_CODE,
798 CURRENCY_CONVERSION_EXPONENT,
799 CURRENCY_CONVERSION_RATE,
800 MIS_INDUSTRY_CODE,
801 SIC_CODE,
802 MERCHANT_TAX_ID,
803 MERCHANT_REFERENCE,
804 MERCHANT_NAME1,
805 MERCHANT_NAME2,
806 MERCHANT_ADDRESS1,
807 MERCHANT_ADDRESS2,
808 MERCHANT_ADDRESS3,
809 MERCHANT_ADDRESS4,
810 MERCHANT_CITY,
811 MERCHANT_PROVINCE_STATE,
812 MERCHANT_POSTAL_CODE,
813 MERCHANT_COUNTRY,
814 TOTAL_TAX,
815 LOCAL_TAX,
816 NATIONAL_TAX,
817 OTHER_TAX,
818 ORG_ID,
819 LAST_UPDATE_DATE,
820 LAST_UPDATED_BY,
821 LAST_UPDATE_LOGIN,
822 CREATION_DATE,
823 CREATED_BY,
824 FOLIO_TYPE,
825 ATM_CASH_ADVANCE,
826 ATM_TRANSACTION_DATE,
827 ATM_FEE_AMOUNT,
828 ATM_TYPE,
829 ATM_ID,
830 ATM_NETWORK_ID,
831 RESTAURANT_FOOD_AMOUNT,
832 RESTAURANT_BEVERAGE_AMOUNT,
833 RESTAURANT_TIP_AMOUNT,
834 CAR_RENTAL_DATE,
835 CAR_RETURN_DATE,
836 CAR_RENTAL_LOCATION,
837 CAR_RENTAL_STATE,
838 CAR_RETURN_LOCATION,
839 CAR_RETURN_STATE,
840 CAR_RENTER_NAME,
841 CAR_RENTAL_DAYS,
842 CAR_RENTAL_AGREEMENT_NUMBER,
843 CAR_CLASS,
844 CAR_TOTAL_MILEAGE,
845 CAR_GAS_AMOUNT,
846 CAR_INSURANCE_AMOUNT,
847 CAR_MILEAGE_AMOUNT,
848 CAR_DAILY_RATE,
849 HOTEL_ARRIVAL_DATE,
850 HOTEL_DEPART_DATE,
851 HOTEL_CHARGE_DESC,
852 HOTEL_GUEST_NAME,
853 HOTEL_STAY_DURATION,
854 HOTEL_ROOM_RATE,
855 HOTEL_NO_SHOW_FLAG,
856 HOTEL_ROOM_AMOUNT,
857 HOTEL_TELEPHONE_AMOUNT,
858 HOTEL_ROOM_TAX,
859 HOTEL_BAR_AMOUNT,
860 HOTEL_MOVIE_AMOUNT,
861 HOTEL_GIFT_SHOP_AMOUNT,
862 HOTEL_LAUNDRY_AMOUNT,
863 HOTEL_HEALTH_AMOUNT,
864 HOTEL_RESTAURANT_AMOUNT,
865 HOTEL_BUSINESS_AMOUNT,
866 HOTEL_PARKING_AMOUNT,
867 HOTEL_ROOM_SERVICE_AMOUNT,
868 HOTEL_TIP_AMOUNT,
869 HOTEL_MISC_AMOUNT,
870 HOTEL_CITY,
871 HOTEL_STATE,
872 HOTEL_FOLIO_NUMBER,
873 HOTEL_ROOM_TYPE,
874 AIR_DEPARTURE_DATE,
875 AIR_DEPARTURE_CITY,
876 AIR_ROUTING,
877 AIR_ARRIVAL_CITY,
878 AIR_STOPOVER_FLAG,
879 AIR_BASE_FARE_AMOUNT,
880 AIR_FARE_BASIS_CODE,
881 AIR_SERVICE_CLASS,
882 AIR_CARRIER_ABBREVIATION,
883 AIR_CARRIER_CODE,
884 AIR_TICKET_ISSUER,
885 AIR_ISSUER_CITY,
886 AIR_PASSENGER_NAME,
887 AIR_REFUND_TICKET_NUMBER,
888 AIR_EXCHANGED_TICKET_NUMBER,
889 AIR_AGENCY_NUMBER,
890 AIR_TICKET_NUMBER,
891 FINANCIAL_CATEGORY,
892 PAYMENT_FLAG,
893 RECORD_TYPE,
894 MERCHANT_ACTIVITY,
895 CATEGORY,
896 REPORT_HEADER_ID,
897 EXPENSE_STATUS,
898 COMPANY_PREPAID_INVOICE_ID,
899 INACTIVE_EMP_WF_ITEM_KEY,
900 LOCATION_ID,
901 REQUEST_ID,
902 MERCHANT_COUNTRY_CODE,
903 DISPUTE_DATE,
904 PAYMENT_DUE_FROM_CODE,
905 TRX_AVAILABLE_DATE,
906 CARD_ACCEPTOR_ID,
907 TRXN_DETAIL_FLAG,
908 CARD_ID,
909 DESCRIPTION,
910 COMPANY_NUMBER,
911 MARKET_CODE,
912 VALIDATE_REQUEST_ID
913 )
914 (select p_request_id arc_req_id, sysdate archive_date,
915 TRX_ID,
916 VALIDATE_CODE,
917 CARD_PROGRAM_ID,
918 EXPENSED_AMOUNT,
919 CARD_NUMBER,
920 REFERENCE_NUMBER,
921 TRANSACTION_TYPE,
922 TRANSACTION_DATE,
923 TRANSACTION_AMOUNT,
924 DEBIT_FLAG,
925 BILLED_DATE,
926 BILLED_AMOUNT,
927 BILLED_DECIMAL,
928 BILLED_CURRENCY_CODE,
929 POSTED_DATE,
930 POSTED_AMOUNT,
931 POSTED_DECIMAL,
932 POSTED_CURRENCY_CODE,
933 CURRENCY_CONVERSION_EXPONENT,
934 CURRENCY_CONVERSION_RATE,
935 MIS_INDUSTRY_CODE,
936 SIC_CODE,
937 MERCHANT_TAX_ID,
938 MERCHANT_REFERENCE,
939 MERCHANT_NAME1,
940 MERCHANT_NAME2,
941 MERCHANT_ADDRESS1,
942 MERCHANT_ADDRESS2,
943 MERCHANT_ADDRESS3,
944 MERCHANT_ADDRESS4,
945 MERCHANT_CITY,
946 MERCHANT_PROVINCE_STATE,
950 LOCAL_TAX,
947 MERCHANT_POSTAL_CODE,
948 MERCHANT_COUNTRY,
949 TOTAL_TAX,
951 NATIONAL_TAX,
952 OTHER_TAX,
953 ORG_ID,
954 LAST_UPDATE_DATE,
955 LAST_UPDATED_BY,
956 LAST_UPDATE_LOGIN,
957 CREATION_DATE,
958 CREATED_BY,
959 FOLIO_TYPE,
960 ATM_CASH_ADVANCE,
961 ATM_TRANSACTION_DATE,
962 ATM_FEE_AMOUNT,
963 ATM_TYPE,
964 ATM_ID,
965 ATM_NETWORK_ID,
966 RESTAURANT_FOOD_AMOUNT,
967 RESTAURANT_BEVERAGE_AMOUNT,
968 RESTAURANT_TIP_AMOUNT,
969 CAR_RENTAL_DATE,
970 CAR_RETURN_DATE,
971 CAR_RENTAL_LOCATION,
972 CAR_RENTAL_STATE,
973 CAR_RETURN_LOCATION,
974 CAR_RETURN_STATE,
975 CAR_RENTER_NAME,
976 CAR_RENTAL_DAYS,
977 CAR_RENTAL_AGREEMENT_NUMBER,
978 CAR_CLASS,
979 CAR_TOTAL_MILEAGE,
980 CAR_GAS_AMOUNT,
981 CAR_INSURANCE_AMOUNT,
982 CAR_MILEAGE_AMOUNT,
983 CAR_DAILY_RATE,
984 HOTEL_ARRIVAL_DATE,
985 HOTEL_DEPART_DATE,
986 HOTEL_CHARGE_DESC,
987 HOTEL_GUEST_NAME,
988 HOTEL_STAY_DURATION,
989 HOTEL_ROOM_RATE,
990 HOTEL_NO_SHOW_FLAG,
991 HOTEL_ROOM_AMOUNT,
992 HOTEL_TELEPHONE_AMOUNT,
993 HOTEL_ROOM_TAX,
994 HOTEL_BAR_AMOUNT,
995 HOTEL_MOVIE_AMOUNT,
996 HOTEL_GIFT_SHOP_AMOUNT,
997 HOTEL_LAUNDRY_AMOUNT,
998 HOTEL_HEALTH_AMOUNT,
999 HOTEL_RESTAURANT_AMOUNT,
1000 HOTEL_BUSINESS_AMOUNT,
1001 HOTEL_PARKING_AMOUNT,
1002 HOTEL_ROOM_SERVICE_AMOUNT,
1003 HOTEL_TIP_AMOUNT,
1004 HOTEL_MISC_AMOUNT,
1005 HOTEL_CITY,
1006 HOTEL_STATE,
1007 HOTEL_FOLIO_NUMBER,
1008 HOTEL_ROOM_TYPE,
1009 AIR_DEPARTURE_DATE,
1010 AIR_DEPARTURE_CITY,
1011 AIR_ROUTING,
1012 AIR_ARRIVAL_CITY,
1013 AIR_STOPOVER_FLAG,
1014 AIR_BASE_FARE_AMOUNT,
1015 AIR_FARE_BASIS_CODE,
1016 AIR_SERVICE_CLASS,
1017 AIR_CARRIER_ABBREVIATION,
1018 AIR_CARRIER_CODE,
1019 AIR_TICKET_ISSUER,
1020 AIR_ISSUER_CITY,
1021 AIR_PASSENGER_NAME,
1022 AIR_REFUND_TICKET_NUMBER,
1023 AIR_EXCHANGED_TICKET_NUMBER,
1024 AIR_AGENCY_NUMBER,
1025 AIR_TICKET_NUMBER,
1026 FINANCIAL_CATEGORY,
1027 PAYMENT_FLAG,
1028 RECORD_TYPE,
1029 MERCHANT_ACTIVITY,
1030 CATEGORY,
1031 A.REPORT_HEADER_ID,
1032 EXPENSE_STATUS,
1033 COMPANY_PREPAID_INVOICE_ID,
1034 INACTIVE_EMP_WF_ITEM_KEY,
1035 LOCATION_ID,
1036 REQUEST_ID,
1037 MERCHANT_COUNTRY_CODE,
1038 DISPUTE_DATE,
1039 PAYMENT_DUE_FROM_CODE,
1040 TRX_AVAILABLE_DATE,
1041 CARD_ACCEPTOR_ID,
1042 TRXN_DETAIL_FLAG,
1043 CARD_ID,
1044 DESCRIPTION,
1045 COMPANY_NUMBER,
1046 MARKET_CODE,
1047 VALIDATE_REQUEST_ID
1048 from ap_credit_card_trxns_all a, AP_EXP_REPORT_HEADERS_GT gt
1049 where gt.report_header_id = a.report_header_id);
1050
1051 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' CC Transactions');
1052
1053 ----------------------------------------------------------------------
1054 l_debug_info := 'Insert into CC Transaction Details.';
1055 ----------------------------------------------------------------------
1056
1057 insert into ap_cc_trx_details_arc (
1058 ARC_REQ_ID,
1059 ARCHIVE_DATE,
1060 TRX_DETAIL_ID,
1061 LAST_UPDATE_DATE,
1062 LAST_UPDATED_BY,
1063 LAST_UPDATE_LOGIN,
1064 CREATION_DATE,
1065 CREATED_BY,
1066 TRX_ID,
1067 TRANSACTION_AMOUNT,
1068 BILLED_AMOUNT,
1069 EXT_FOLIO_TYPE,
1070 FOLIO_TYPE,
1071 ITEM_SEQ_NUMBER,
1072 ITEM_DESCRIPTION,
1073 TRANSACTION_DATE
1074 )
1075 (select p_request_id arc_req_id, sysdate archive_date,
1076 A.TRX_DETAIL_ID,
1077 A.LAST_UPDATE_DATE,
1078 A.LAST_UPDATED_BY,
1079 A.LAST_UPDATE_LOGIN,
1080 A.CREATION_DATE,
1081 A.CREATED_BY,
1082 A.TRX_ID,
1083 A.TRANSACTION_AMOUNT,
1084 A.BILLED_AMOUNT,
1085 A.EXT_FOLIO_TYPE,
1086 A.FOLIO_TYPE,
1087 A.ITEM_SEQ_NUMBER,
1088 A.ITEM_DESCRIPTION,
1089 A.TRANSACTION_DATE
1090 from ap_cc_trx_details a, ap_credit_card_trxns_all b, AP_EXP_REPORT_HEADERS_GT gt
1091 where gt.report_header_id = b.report_header_id
1092 and a.trx_id = b.trx_id);
1093
1094 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' CC Transaction Details');
1095
1096 -- Insert into Add On Mileage Rates
1097 ----------------------------------------------------------------------
1098 l_debug_info := 'Insert into Add On Mileage Rates.';
1099 ----------------------------------------------------------------------
1100
1101 insert into oie_addon_mileage_rates_arc (
1102 ARC_REQ_ID,
1103 ARCHIVE_DATE,
1104 REPORT_LINE_ID,
1105 ADDON_RATE_TYPE,
1106 MILEAGE_RATE,
1107 MILEAGE_AMOUNT,
1108 MILEAGE_PAYROLL_TAX_CODE_ID,
1109 LAST_UPDATE_DATE,
1110 LAST_UPDATED_BY,
1111 LAST_UPDATE_LOGIN,
1112 CREATED_BY,
1113 CREATION_DATE
1114 )
1115 (select p_request_id arc_req_id, sysdate archive_date,
1116 A.REPORT_LINE_ID,
1117 A.ADDON_RATE_TYPE,
1118 A.MILEAGE_RATE,
1119 A.MILEAGE_AMOUNT,
1120 A.MILEAGE_PAYROLL_TAX_CODE_ID,
1121 A.LAST_UPDATE_DATE,
1122 A.LAST_UPDATED_BY,
1123 A.LAST_UPDATE_LOGIN,
1124 A.CREATED_BY,
1125 A.CREATION_DATE
1126 from oie_addon_mileage_rates a, AP_EXP_REPORT_HEADERS_GT gt,
1127 ap_expense_report_lines_all b
1128 where gt.report_header_id = b.report_header_id
1132
1129 and a.report_line_id = b.report_line_id);
1130
1131 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Add On Mileage Rates');
1133 -- Insert into Perdiem Daily Breakups
1134 ----------------------------------------------------------------------
1135 l_debug_info := 'Insert into Perdiem Daily Breakups.';
1136 ----------------------------------------------------------------------
1137
1138 insert into oie_pdm_daily_breakups_arc (
1139 ARC_REQ_ID,
1140 ARCHIVE_DATE,
1141 PDM_DAILY_BREAKUP_ID,
1142 REPORT_LINE_ID,
1143 START_DATE,
1144 END_DATE,
1145 AMOUNT,
1146 NUMBER_OF_MEALS,
1147 MEALS_AMOUNT,
1148 BREAKFAST_FLAG,
1149 LUNCH_FLAG,
1150 DINNER_FLAG,
1151 ACCOMMODATION_AMOUNT,
1152 ACCOMMODATION_FLAG,
1153 HOTEL_NAME,
1154 NIGHT_RATE_TYPE,
1155 NIGHT_RATE_AMOUNT,
1156 PDM_RATE,
1157 PDM_DESTINATION_ID,
1158 RATE_TYPE_CODE,
1159 LAST_UPDATE_DATE,
1160 LAST_UPDATED_BY,
1161 LAST_UPDATE_LOGIN,
1162 CREATED_BY,
1163 CREATION_DATE,
1164 SCHEDULE_TYPE_CODE,
1165 CUST_MEALS_AMOUNT,
1166 CUST_ACCOMMODATION_AMOUNT,
1167 CUST_NIGHT_RATE_AMOUNT,
1168 CUST_PDM_RATE
1169 )
1170 (select p_request_id arc_req_id, sysdate archive_date,
1171 A.PDM_DAILY_BREAKUP_ID,
1172 A.REPORT_LINE_ID,
1173 A.START_DATE,
1174 A.END_DATE,
1175 A.AMOUNT,
1176 A.NUMBER_OF_MEALS,
1177 A.MEALS_AMOUNT,
1178 A.BREAKFAST_FLAG,
1179 A.LUNCH_FLAG,
1180 A.DINNER_FLAG,
1181 A.ACCOMMODATION_AMOUNT,
1182 A.ACCOMMODATION_FLAG,
1183 A.HOTEL_NAME,
1184 A.NIGHT_RATE_TYPE,
1185 A.NIGHT_RATE_AMOUNT,
1186 A.PDM_RATE,
1187 A.PDM_DESTINATION_ID,
1188 A.RATE_TYPE_CODE,
1189 A.LAST_UPDATE_DATE,
1190 A.LAST_UPDATED_BY,
1191 A.LAST_UPDATE_LOGIN,
1192 A.CREATED_BY,
1193 A.CREATION_DATE,
1194 A.SCHEDULE_TYPE_CODE,
1195 A.CUST_MEALS_AMOUNT,
1196 A.CUST_ACCOMMODATION_AMOUNT,
1197 A.CUST_NIGHT_RATE_AMOUNT,
1198 A.CUST_PDM_RATE
1199 from oie_pdm_daily_breakups a, AP_EXP_REPORT_HEADERS_GT gt,
1200 ap_expense_report_lines_all b
1201 where gt.report_header_id = b.report_header_id
1202 and a.report_line_id = b.report_line_id);
1203
1204 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Perdiem Daily Breakups');
1205
1206 -- Insert into Perdiem Destinations
1207 ----------------------------------------------------------------------
1208 l_debug_info := 'Insert into Perdiem Destinations.';
1209 ----------------------------------------------------------------------
1210
1211 insert into oie_pdm_destinations_arc (
1212 ARC_REQ_ID,
1213 ARCHIVE_DATE,
1214 PDM_DESTINATION_ID,
1215 REPORT_LINE_ID,
1216 START_DATE,
1217 END_DATE,
1218 LOCATION_ID,
1219 LAST_UPDATE_DATE,
1220 LAST_UPDATED_BY,
1221 LAST_UPDATE_LOGIN,
1222 CREATED_BY,
1223 CREATION_DATE
1224 )
1225 (select p_request_id arc_req_id, sysdate archive_date,
1226 A.PDM_DESTINATION_ID,
1227 A.REPORT_LINE_ID,
1228 A.START_DATE,
1229 A.END_DATE,
1230 A.LOCATION_ID,
1231 A.LAST_UPDATE_DATE,
1232 A.LAST_UPDATED_BY,
1233 A.LAST_UPDATE_LOGIN,
1234 A.CREATED_BY,
1235 A.CREATION_DATE
1236 from oie_pdm_destinations a, AP_EXP_REPORT_HEADERS_GT gt,
1237 ap_expense_report_lines_all b
1238 where gt.report_header_id = b.report_header_id
1239 and a.report_line_id = b.report_line_id);
1240
1241 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Perdiem Destinations');
1242
1243 -- Insert into OIE Attendees
1244 ----------------------------------------------------------------------
1245 l_debug_info := 'Insert into OIE Attendees.';
1246 ----------------------------------------------------------------------
1247
1248 insert into oie_attendees_arc (
1249 ARC_REQ_ID,
1250 ARCHIVE_DATE,
1251 ATTENDEE_LINE_ID,
1252 REPORT_LINE_ID,
1253 EMPLOYEE_FLAG,
1254 EMPLOYEE_ID,
1255 ATTENDEE_TYPE,
1256 NAME,
1257 TITLE,
1258 EMPLOYER,
1259 EMPLOYER_ADDRESS,
1260 TAX_ID,
1261 ORG_ID,
1262 LAST_UPDATE_DATE,
1263 LAST_UPDATED_BY,
1264 CREATION_DATE,
1265 CREATED_BY,
1266 LAST_UPDATE_LOGIN
1267 )
1268 (select p_request_id arc_req_id, sysdate archive_date,
1269 A.ATTENDEE_LINE_ID,
1270 A.REPORT_LINE_ID,
1271 A.EMPLOYEE_FLAG,
1272 A.EMPLOYEE_ID,
1273 A.ATTENDEE_TYPE,
1274 A.NAME,
1275 A.TITLE,
1276 A.EMPLOYER,
1277 A.EMPLOYER_ADDRESS,
1278 A.TAX_ID,
1279 A.ORG_ID,
1280 A.LAST_UPDATE_DATE,
1281 A.LAST_UPDATED_BY,
1282 A.CREATION_DATE,
1283 A.CREATED_BY,
1284 A.LAST_UPDATE_LOGIN
1285 from oie_attendees_all a, AP_EXP_REPORT_HEADERS_GT gt,
1286 ap_expense_report_lines_all b
1287 where gt.report_header_id = b.report_header_id
1288 and a.report_line_id = b.report_line_id);
1289
1290 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Attendees');
1291
1292 -- Insert into Audit Reasons
1293 ----------------------------------------------------------------------
1294 l_debug_info := 'Insert into Audit Reasons.';
1295 ----------------------------------------------------------------------
1296
1297 insert into ap_aud_audit_reasons_arc (
1298 ARC_REQ_ID,
1299 ARCHIVE_DATE,
1300 AUDIT_REASON_ID,
1301 REPORT_HEADER_ID,
1305 LAST_UPDATE_LOGIN,
1302 AUDIT_REASON_CODE,
1303 CREATION_DATE,
1304 CREATED_BY,
1306 LAST_UPDATE_DATE,
1307 LAST_UPDATED_BY
1308 )
1309 (select p_request_id arc_req_id, sysdate archive_date,
1310 A.AUDIT_REASON_ID,
1311 A.REPORT_HEADER_ID,
1312 A.AUDIT_REASON_CODE,
1313 A.CREATION_DATE,
1314 A.CREATED_BY,
1315 A.LAST_UPDATE_LOGIN,
1316 A.LAST_UPDATE_DATE,
1317 A.LAST_UPDATED_BY
1318 from ap_aud_audit_reasons a, AP_EXP_REPORT_HEADERS_GT gt
1319 where gt.report_header_id = a.report_header_id);
1320
1321 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Audit Reasons');
1322
1323 -- Insert into Violations
1324 ----------------------------------------------------------------------
1325 l_debug_info := 'Insert into Violations.';
1326 ----------------------------------------------------------------------
1327
1328 insert into ap_pol_violations_arc (
1329 ARC_REQ_ID,
1330 ARCHIVE_DATE,
1331 REPORT_HEADER_ID,
1332 DISTRIBUTION_LINE_NUMBER,
1333 VIOLATION_NUMBER,
1334 VIOLATION_TYPE,
1335 ALLOWABLE_AMOUNT,
1336 FUNC_CURRENCY_ALLOWABLE_AMT,
1337 ORG_ID,
1338 CREATED_BY,
1339 CREATION_DATE,
1340 LAST_UPDATED_BY,
1341 LAST_UPDATE_LOGIN,
1342 LAST_UPDATE_DATE,
1343 EXCEEDED_AMOUNT,
1344 VIOLATION_DATE,
1345 DUP_REPORT_HEADER_ID,
1346 DUP_REPORT_LINE_ID,
1347 DUP_DIST_LINE_NUMBER
1348 )
1349 (select p_request_id arc_req_id, sysdate archive_date,
1350 A.REPORT_HEADER_ID,
1351 A.DISTRIBUTION_LINE_NUMBER,
1352 A.VIOLATION_NUMBER,
1353 A.VIOLATION_TYPE,
1354 A.ALLOWABLE_AMOUNT,
1355 A.FUNC_CURRENCY_ALLOWABLE_AMT,
1356 A.ORG_ID,
1357 A.CREATED_BY,
1358 A.CREATION_DATE,
1359 A.LAST_UPDATED_BY,
1360 A.LAST_UPDATE_LOGIN,
1361 A.LAST_UPDATE_DATE,
1362 A.EXCEEDED_AMOUNT,
1363 A.VIOLATION_DATE,
1364 A.DUP_REPORT_HEADER_ID,
1365 A.DUP_REPORT_LINE_ID,
1366 A.DUP_DIST_LINE_NUMBER
1367 from ap_pol_violations_all a, AP_EXP_REPORT_HEADERS_GT gt
1368 where gt.report_header_id = a.report_header_id);
1369
1370 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Policy Violations');
1371
1372 -- Insert into AP Notes
1373 ----------------------------------------------------------------------
1374 l_debug_info := 'Insert into AP Notes.';
1375 ----------------------------------------------------------------------
1376
1377 insert into ap_notes_arc (
1378 ARC_REQ_ID,
1379 ARCHIVE_DATE,
1380 NOTE_ID,
1381 SOURCE_OBJECT_CODE,
1382 SOURCE_OBJECT_ID,
1383 NOTE_TYPE,
1384 NOTES_DETAIL,
1385 ENTERED_BY,
1386 ENTERED_DATE,
1387 SOURCE_LANG,
1388 CREATION_DATE,
1389 CREATED_BY,
1390 LAST_UPDATE_DATE,
1391 LAST_UPDATED_BY,
1392 LAST_UPDATE_LOGIN
1393 )
1394 (select p_request_id arc_req_id, sysdate archive_date,
1395 A.NOTE_ID,
1396 A.SOURCE_OBJECT_CODE,
1397 A.SOURCE_OBJECT_ID,
1398 A.NOTE_TYPE,
1399 A.NOTES_DETAIL,
1400 A.ENTERED_BY,
1401 A.ENTERED_DATE,
1402 A.SOURCE_LANG,
1403 A.CREATION_DATE,
1404 A.CREATED_BY,
1405 A.LAST_UPDATE_DATE,
1406 A.LAST_UPDATED_BY,
1407 A.LAST_UPDATE_LOGIN
1408 from ap_notes a, AP_EXP_REPORT_HEADERS_GT gt
1409 where gt.report_header_id = a.source_object_id);
1410
1411 fnd_file.put_line(fnd_file.log,'Archived ' || SQL%ROWCOUNT || ' Notes');
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 fnd_file.put_line(fnd_file.log,'Exception when archiving data ' || SQLERRM);
1416 fnd_file.put_line(fnd_file.log,'When Performing ' || l_debug_info);
1417 RAISE;
1418 END ArchiveData;
1419
1420 PROCEDURE PurgeData(p_request_id IN NUMBER,
1421 p_purge_wf_attach_flag IN VARCHAR2) IS
1422
1423 l_debug_info VARCHAR2(2000);
1424 CURSOR c_exp_reports(l_request_id IN NUMBER) is
1425 select report_header_id
1426 from ap_expense_report_headers_arc
1427 where arc_req_id = l_request_id;
1428 CURSOR c_exp_report_lines(l_request_id IN NUMBER) is
1429 select report_line_id
1430 from ap_expense_report_lines_arc
1431 where arc_req_id = l_request_id;
1432 l_report_header_id ap_expense_report_headers_arc.report_header_id%TYPE;
1433 l_report_line_id ap_expense_report_lines_arc.report_line_id%TYPE;
1434 l_childItemKeySeq NUMBER;
1435 l_wf_active BOOLEAN := FALSE;
1436 l_wf_exist BOOLEAN := FALSE;
1437 l_end_date wf_items.end_date%TYPE;
1438 l_child_item_key varchar2(2000);
1439
1440 BEGIN
1441 -- The where clause in all the delete sqls below make sure that only the records
1442 -- that were archived in this run are deleted.
1443
1444 -- Delete AP Notes.
1445 ----------------------------------------------------------------------
1446 l_debug_info := 'Delete AP Notes.';
1447 ----------------------------------------------------------------------
1448
1449 DELETE FROM ap_notes
1450 WHERE source_object_id IN (
1451 SELECT source_object_id
1452 FROM ap_notes_arc
1453 WHERE arc_req_id = p_request_id);
1454
1455 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Notes');
1456
1457 -- Delete Policy Violations.
1458 ----------------------------------------------------------------------
1459 l_debug_info := 'Delete Policy Violations.';
1460 ----------------------------------------------------------------------
1461
1462 DELETE FROM ap_pol_violations_all
1463 WHERE report_header_id IN (
1464 SELECT report_header_id
1465 FROM ap_pol_violations_arc
1469
1466 WHERE arc_req_id = p_request_id);
1467
1468 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Policy Violations');
1470 -- Delete Audit reasons.
1471 ----------------------------------------------------------------------
1472 l_debug_info := 'Delete Audit reasons.';
1473 ----------------------------------------------------------------------
1474
1475 DELETE FROM ap_aud_audit_reasons
1476 WHERE audit_reason_id IN (
1477 SELECT audit_reason_id
1478 FROM ap_aud_audit_reasons_arc
1479 WHERE arc_req_id = p_request_id);
1480
1481 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Audit Reasons');
1482
1483 -- Delete Attendee information
1484 ----------------------------------------------------------------------
1485 l_debug_info := 'Delete Attendee information.';
1486 ----------------------------------------------------------------------
1487
1488 DELETE FROM oie_attendees_all
1489 WHERE attendee_line_id IN (
1490 SELECT attendee_line_id
1491 FROM oie_attendees_arc
1492 WHERE arc_req_id = p_request_id);
1493
1494 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Attendees');
1495
1496 -- Delete Perdiem destinations
1497 ----------------------------------------------------------------------
1498 l_debug_info := 'Delete Perdiem destinations.';
1499 ----------------------------------------------------------------------
1500
1501 DELETE FROM oie_pdm_destinations
1502 WHERE pdm_destination_id IN (
1503 SELECT pdm_destination_id
1504 FROM oie_pdm_destinations_arc
1505 WHERE arc_req_id = p_request_id);
1506
1507 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Perdiem Destinations');
1508
1509 -- Delete Perdiem Daily Beakups.
1510 ----------------------------------------------------------------------
1511 l_debug_info := 'Delete Perdiem Daily Beakups.';
1512 ----------------------------------------------------------------------
1513
1514 DELETE FROM oie_pdm_daily_breakups
1515 WHERE pdm_daily_breakup_id IN (
1516 SELECT pdm_daily_breakup_id
1517 FROM oie_pdm_daily_breakups_arc
1518 WHERE arc_req_id = p_request_id);
1519
1520 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Perdiem Daily Breakups');
1521
1522 -- Delete Add On Mileage rates.
1523 ----------------------------------------------------------------------
1524 l_debug_info := 'Delete Add On Mileage rates.';
1525 ----------------------------------------------------------------------
1526
1527 DELETE FROM oie_addon_mileage_rates
1528 WHERE report_line_id IN (
1529 SELECT report_line_id
1530 FROM oie_addon_mileage_rates_arc
1531 WHERE arc_req_id = p_request_id);
1532
1533 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Add On Mileage Rates');
1534
1535 -- Delete CC transactions.
1536 ----------------------------------------------------------------------
1537 l_debug_info := 'Delete CC Transaction Details.';
1538 ----------------------------------------------------------------------
1539
1540 DELETE FROM ap_cc_trx_details
1541 WHERE trx_detail_id IN (
1542 SELECT trx_detail_id
1543 FROM ap_cc_trx_details_arc
1544 WHERE arc_req_id = p_request_id);
1545
1546 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' CC Transaction Details');
1547
1548 -- Delete CC transactions.
1549 ----------------------------------------------------------------------
1550 l_debug_info := 'Delete CC Transactions.';
1551 ----------------------------------------------------------------------
1552
1553 DELETE FROM ap_credit_card_trxns_all
1554 WHERE report_header_id IN (
1555 SELECT report_header_id
1556 FROM ap_credit_card_trxns_arc
1557 WHERE arc_req_id = p_request_id);
1558
1559 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' CC Transactions');
1560
1561 -- Delete Distributions.
1562 ----------------------------------------------------------------------
1563 l_debug_info := 'Delete Distributions.';
1564 ----------------------------------------------------------------------
1565
1566 DELETE FROM ap_exp_report_dists_all
1567 WHERE report_distribution_id IN (
1568 SELECT report_distribution_id
1569 FROM ap_exp_report_dists_arc
1570 WHERE arc_req_id = p_request_id);
1571
1572 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Dists');
1573
1574 -- Delete Lines.
1575 ----------------------------------------------------------------------
1576 l_debug_info := 'Delete Lines.';
1577 ----------------------------------------------------------------------
1578
1579 DELETE FROM ap_expense_report_lines_all
1580 WHERE report_line_id IN (
1581 SELECT report_line_id
1582 FROM ap_expense_report_lines_arc
1583 WHERE arc_req_id = p_request_id);
1584
1585 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Lines');
1586
1587 -- Delete Headers.
1588 ----------------------------------------------------------------------
1589 l_debug_info := 'Delete Headers.';
1590 ----------------------------------------------------------------------
1591
1592 DELETE FROM ap_expense_report_headers_all
1593 WHERE report_header_id IN (
1594 SELECT report_header_id
1595 FROM ap_expense_report_headers_arc
1596 WHERE arc_req_id = p_request_id);
1597
1598 fnd_file.put_line(fnd_file.log,'Purged ' || SQL%ROWCOUNT || ' Headers');
1599
1600 -- Purge Workflow.
1601 ----------------------------------------------------------------------
1602 l_debug_info := 'Purge Workflow.';
1603 ----------------------------------------------------------------------
1604 IF (p_purge_wf_attach_flag ='Y') THEN
1605
1606 fnd_file.put_line(fnd_file.log,'Purging Workflow and Attachments');
1607
1608 -- Delete Line Attachments
1612 exit when c_exp_report_lines%NOTFOUND;
1609 open c_exp_report_lines(p_request_id);
1610 LOOP
1611 fetch c_exp_report_lines into l_report_line_id;
1613 l_debug_info := 'Delete Line Attachements for report Line ' || to_char(l_report_line_id);
1614 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1615 X_entity_name => 'OIE_LINE_ATTACHMENTS',
1616 X_pk1_value => to_char(l_report_line_id),
1617 X_delete_document_flag => 'N');
1618
1619 END LOOP;
1620 open c_exp_reports(p_request_id);
1621 LOOP
1622 fetch c_exp_reports into l_report_header_id;
1623 exit when c_exp_reports%NOTFOUND;
1624
1625 begin
1626 select end_date
1627 into l_end_date
1628 from wf_items
1629 where item_type = 'APEXP'
1630 and item_key = to_char(l_report_header_id);
1631 if l_end_date is NULL then
1632 l_wf_active := TRUE;
1633 else
1634 l_wf_active := FALSE;
1635 end if;
1636 l_wf_exist := TRUE;
1637 exception
1638 when no_data_found then
1639 l_wf_active := FALSE;
1640 l_wf_exist := FALSE;
1641 end;
1642 IF l_wf_exist THEN
1643 fnd_file.put_line(fnd_file.log,'Purging Workflow');
1644 -- Abort the parent workflow if active
1645 IF l_wf_active THEN
1646 l_debug_info := 'WF Exists, abort process';
1647 fnd_file.put_line(fnd_file.log,'Abort Existing Workflow');
1648 wf_engine.AbortProcess (itemtype => 'APEXP',
1649 itemkey => to_char(l_report_header_id),
1650 cascade => TRUE);
1651 END IF;
1652
1653 l_debug_info := 'Purge WF for report ' || to_char(l_report_header_id);
1654 -- Check the child item keys
1655 begin
1656 l_childItemKeySeq := WF_ENGINE.GetItemAttrNumber('APEXP',
1657 l_report_header_id,
1658 'AME_CHILD_ITEM_KEY_SEQ');
1659 exception
1660 when others then
1661 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
1662 l_childItemKeySeq := 0;
1663 else
1664 raise;
1665 end if;
1666 end;
1667
1668 l_debug_info := 'Purge Child Workflow, Child Item Count ' || l_childItemKeySeq;
1669
1670 IF (l_childItemKeySeq IS NOT NULL AND l_childItemKeySeq > 0) THEN
1671 FOR i in 1 .. l_childItemKeySeq LOOP
1672 l_child_item_key := to_char(l_report_header_id) || '-' || to_char(i);
1673 begin
1674 select end_date
1675 into l_end_date
1676 from wf_items
1677 where item_type = 'APEXP'
1678 and item_key = l_child_item_key;
1679 if l_end_date is NULL then
1680 l_wf_active := TRUE;
1681 else
1682 l_wf_active := FALSE;
1683 end if;
1684 l_wf_exist := TRUE;
1685 exception
1686 when no_data_found then
1687 l_wf_active := FALSE;
1688 l_wf_exist := FALSE;
1689 end;
1690 IF (l_wf_exist) THEN
1691 -- Abort the child workflow if active
1692 IF l_wf_active THEN
1693 l_debug_info := 'WF Exists, abort process';
1694 fnd_file.put_line(fnd_file.log,'Abort Existing Workflow');
1695 wf_engine.AbortProcess (itemtype => 'APEXP',
1696 itemkey => l_child_item_key,
1697 cascade => TRUE);
1698 END IF;
1699 wf_purge.Items(itemtype => 'APEXP',
1700 itemkey => l_child_item_key);
1701
1702 wf_purge.TotalPerm(itemtype => 'APEXP',
1703 itemkey => l_child_item_key,
1704 runtimeonly => TRUE);
1705 END IF;
1706 END LOOP;
1707 END IF;
1708
1709 l_debug_info := 'Purge Parent Workflow.';
1710 wf_purge.Items(itemtype => 'APEXP',
1711 itemkey => to_char(l_report_header_id));
1712
1713 wf_purge.TotalPerm(itemtype => 'APEXP',
1714 itemkey => to_char(l_report_header_id),
1715 runtimeonly => TRUE);
1716 END IF;
1717 l_debug_info := 'Delete Header Attachements for report ' || to_char(l_report_header_id);
1718 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1719 X_entity_name => 'OIE_HEADER_ATTACHMENTS',
1720 X_pk1_value => to_char(l_report_header_id),
1721 X_delete_document_flag => 'N');
1722
1723 END LOOP;
1724 fnd_file.put_line(fnd_file.log,'Purged Workflow and Attachments');
1725 END IF;
1726
1727
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 fnd_file.put_line(fnd_file.log,'Exception when purging data ' || SQLERRM);
1731 fnd_file.put_line(fnd_file.log,'When Performing ' || l_debug_info);
1732 RAISE;
1733 END PurgeData;
1734
1735 PROCEDURE RunProgram(errbuf OUT NOCOPY VARCHAR2,
1736 retcode OUT NOCOPY NUMBER,
1737 p_org_id IN NUMBER DEFAULT NULL,
1738 p_source_date IN VARCHAR2,
1739 p_purge_wf_attach_flag IN VARCHAR2) IS
1740
1741 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
1742 l_source_date DATE;
1743 l_count NUMBER;
1744 BEGIN
1745 -- p_source_date is a required parameter, no need to check for null.
1746 l_source_date := fnd_date.canonical_to_date(p_source_date);
1747
1748 fnd_file.put_line(fnd_file.log, '=================================================================');
1749 fnd_file.put_line(fnd_file.log, 'Archive and Purge Parameters');
1750 fnd_file.put_line(fnd_file.log, '=================================================================');
1751 fnd_file.put_line(fnd_file.log, 'Request Id: ' || l_request_id);
1752 fnd_file.put_line(fnd_file.log, 'Source Date: ' || l_source_date);
1753 fnd_file.put_line(fnd_file.log, 'Purge WF and Attachments: ' || p_purge_wf_attach_flag);
1754 IF (p_org_id IS NULL) THEN
1755 fnd_file.put_line(fnd_file.log, 'Operating Unit: ' || 'Processing all operating units');
1756 ELSE
1757 fnd_file.put_line(fnd_file.log, 'Operating Unit: ' || p_org_id);
1761 fnd_file.put_line(fnd_file.log, ' ');
1758 END IF;
1759 fnd_file.put_line(fnd_file.log, '=================================================================');
1760
1762 fnd_file.put_line(fnd_file.log, ' ');
1763 fnd_file.put_line(fnd_file.log, ' ');
1764
1765 fnd_file.put_line(fnd_file.log, '=================================================================');
1766 fnd_file.put_line(fnd_file.log, 'Archiving the selected Expense Reports');
1767 fnd_file.put_line(fnd_file.log, '=================================================================');
1768
1769 -- Archive the data.
1770 ArchiveData(l_source_date, p_org_id, l_request_id);
1771
1772 fnd_file.put_line(fnd_file.log, '=================================================================');
1773 fnd_file.put_line(fnd_file.log, 'Archive Complete');
1774 fnd_file.put_line(fnd_file.log, '=================================================================');
1775
1776 fnd_file.put_line(fnd_file.log, ' ');
1777 fnd_file.put_line(fnd_file.log, ' ');
1778 fnd_file.put_line(fnd_file.log, ' ');
1779
1780 fnd_file.put_line(fnd_file.log, '=================================================================');
1781 fnd_file.put_line(fnd_file.log, 'Purging the selected Expense Reports');
1782 fnd_file.put_line(fnd_file.log, '=================================================================');
1783
1784 -- Purge the data.
1785 PurgeData(l_request_id, p_purge_wf_attach_flag);
1786
1787 fnd_file.put_line(fnd_file.log, '=================================================================');
1788 fnd_file.put_line(fnd_file.log, 'Purge Complete');
1789 fnd_file.put_line(fnd_file.log, '=================================================================');
1790
1791 fnd_file.put_line(fnd_file.log, ' ');
1792 fnd_file.put_line(fnd_file.log, ' ');
1793 fnd_file.put_line(fnd_file.log, ' ');
1794
1795 select count(*) into l_count from AP_EXP_REPORT_HEADERS_GT;
1796
1797 fnd_file.put_line(fnd_file.log, '=================================================================');
1798 fnd_file.put_line(fnd_file.log, 'Total Expenses processed: ' || l_count);
1799 fnd_file.put_line(fnd_file.log, '=================================================================');
1800
1801 delete from AP_EXP_REPORT_HEADERS_GT;
1802
1803 fnd_file.put_line(fnd_file.log, '=================================================================');
1804 fnd_file.put_line(fnd_file.log, 'Cleared Temp Contents');
1805 fnd_file.put_line(fnd_file.log, '=================================================================');
1806
1807 commit;
1808
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 RAISE;
1812 END RunProgram;
1813
1814
1815 END AP_WEB_ARCHIVE_PURGE_ER;