[Home] [Help]
PACKAGE BODY: APPS.PO_R12_CAT_UPG_FINAL_GRP
Source
1 PACKAGE BODY PO_R12_CAT_UPG_FINAL_GRP AS
2 /* $Header: PO_R12_CAT_UPG_FINAL_GRP.plb 120.13 2006/08/18 22:32:19 pthapliy noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_FINAL_GRP';
5 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
6
7 g_debug BOOLEAN := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
8 g_err_num NUMBER := PO_R12_CAT_UPG_PVT.g_application_err_num;
9
10 -- BEGIN: Forward function declarations
11
12 PROCEDURE create_action_history_batch
13 (
14 p_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
15 p_agent_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
16 );
17
18 PROCEDURE archive_gbpa_bulk
19 (
20 p_batch_size NUMBER,
21 p_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
22 );
23
24 -- END: Forward function declarations
25
26 --------------------------------------------------------------------------------
27 --Start of Comments
28 --Name: R12_upgrade_processing
29 --Pre-reqs:
30 -- The iP catalog data has been migrated to PO Transaction tables.
31 --Modifies:
32 -- a) PO Transaction and Archive Tables
33 -- b) FND_MSG_PUB on unhandled exceptions.
34 --Locks:
35 -- None.
36 --Function:
37 -- * An entry in Action History is created.
38 -- * Document is encumbered if the Org has encumbrance enabled.
39 -- * Archival
40 -- * Document Numbers are assigned to the Transaction and Archive tables.
41 -- * Finally, document status is changed from 'IN PROCESS' to 'APPROVED'.
42 --
43 -- This API would commit data per document. The reason why we need this is
44 -- because in cae of exceptions, we want to rollback the transaction for just
45 -- that one document that is being processed, not all the documents.
46 --
47 -- This API should be called after the upgrade phase and during the final upgrade only.
48 --Parameters:
49 --p_api_version
50 -- Apps API Std - To control correct version in use
51 --p_commit
52 -- Apps API Std - Should data be committed?
53 --p_init_msg_list
54 -- Apps API Std - Initialize the message list?
55 --p_validation_level
56 -- Apps API Std - Level of validations to be done
57 --p_log_level
58 -- Specifies the level for which logging is enabled.
59 --p_batch_size
60 -- The maximum number of rows that should be processed at a time, to avoid
61 -- exceeding rollback segment. The transaction would be committed after
62 -- processing each batch.
63 --OUT:
64 --x_return_status
65 -- Apps API Std
66 -- FND_API.g_ret_sts_success - if the procedure completed successfully
67 -- FND_API.g_ret_sts_error - if an error occurred
68 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
69 --x_msg_count
70 -- Apps API Std
71 -- The number of error messages returned in the FND error stack in case
72 -- x_return_status returned FND_API.G_RET_STS_ERROR or
73 -- FND_API.G_RET_STS_UNEXP_ERROR
74 --x_msg_data
75 -- Apps API Std
76 -- Contains error msg in case x_return_status returned FND_API.G_RET_STS_ERROR
77 -- or FND_API.G_RET_STS_UNEXP_ERROR
78 --
79 --End of Comments
80 --------------------------------------------------------------------------------
81 PROCEDURE R12_upgrade_processing
82 (
83 p_api_version IN NUMBER
84 , p_commit IN VARCHAR2 default FND_API.G_FALSE
85 , p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE
86 , p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL
87 , p_log_level IN NUMBER default 1
88 , p_start_rowid IN rowid default NULL --Bug#5156673
89 , p_end_rowid IN rowid default NULL --Bug#5156673
90 , p_batch_size IN NUMBER default 2500
91 , x_return_status OUT NOCOPY VARCHAR2
92 , x_msg_count OUT NOCOPY NUMBER
93 , x_msg_data OUT NOCOPY VARCHAR2
94 , x_rows_processed OUT NOCOPY NUMBER --Bug#5156673
95 )
96 IS
97 l_api_name CONSTANT VARCHAR2(30) := 'R12_upgrade_processing';
98 l_api_version CONSTANT NUMBER := 1.0;
99 l_module CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
100 l_progress VARCHAR2(3) := '000';
101
102 -- SQL What: Cursor to fetch all the GBPA's created by the migration program.
103 -- SQL Why : To perform post upgrade processing on these GBPA's
104 -- SQL Join: created_by, authorization_status
105 CURSOR transferred_gbpas_csr(l_start_rowid rowid, l_end_rowid rowid) IS
106 SELECT POH.po_header_id
107 , POH.agent_id
108 , POH.org_id
109 FROM PO_HEADERS_ALL POH
110 WHERE POH.created_by = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER
111 AND POH.authorization_status = 'IN PROCESS'
112 AND POH.rowid between l_start_rowid and l_end_rowid; --Bug#5156673
113
114 l_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
115 l_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
116 l_agent_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
117 l_key PO_SESSION_GT.key%TYPE;
118 l_current_batch NUMBER; -- Bug 5468308: Track the progress of the script
119 BEGIN
120 l_progress := '010';
121
122 -- Set logging options
123 PO_R12_CAT_UPG_DEBUG.set_logging_options(p_log_level => p_log_level);
124 g_debug := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
125
126 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'START'); END IF;
127
128 -- Standard call to check for call compatibility
129 IF NOT FND_API.compatible_API_call(
130 p_current_version_number => l_api_version,
131 p_caller_version_number => p_api_version,
132 p_api_name => l_api_name,
133 p_pkg_name => g_pkg_name)
134 THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 l_progress := '020';
139 -- Initialize message list if p_init_msg_list is set to TRUE.
140 IF FND_API.to_boolean(p_init_msg_list) THEN
141 FND_MSG_PUB.initialize;
142 END IF;
143
144 l_progress := '030';
145 -- Initialize API return status to success
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147 x_msg_count := 0;
148 x_msg_data := NULL;
149
150 l_progress := '020';
151
152 OPEN transferred_gbpas_csr(p_start_rowid, p_end_rowid); --Bug#5156673
153
154 l_progress := '020';
155 l_current_batch := 0;
156 LOOP
157 l_current_batch := l_current_batch + 1;
158 BEGIN -- block to handle SNAPSHOT_TOO_OLD exception
159 l_progress := '025';
160 -- Bug 5468308: Adding FND log messages at Unexpected level.
161 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
162 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module||'.'||l_progress,
163 'current_batch='||l_current_batch);
164 END IF;
165
166 FETCH transferred_gbpas_csr
167 BULK COLLECT INTO l_po_header_ids, l_agent_ids, l_org_ids;
168 -- LIMIT p_batch_size; --Bug#5156673: no need of batchsize
169
170 l_progress := '030';
171
172 EXIT WHEN l_po_header_ids.COUNT = 0;
173
174 l_progress := '050';
175 -- Create the Action History for the headers
176 create_action_history_batch
177 (
178 p_po_header_ids => l_po_header_ids,
179 p_agent_ids => l_agent_ids
180 );
181
182 l_progress := '060';
183 -- Call the procedure to archive the newly created GBPA's
184 -- The batch_size is required in this procedure because each header could
185 -- have multiple lines and so when archivung the lines, we need to loop
186 -- with the same batch_size.
187 archive_gbpa_bulk
188 (
189 p_batch_size => p_batch_size,
190 p_po_header_ids => l_po_header_ids
191 );
192
193 l_progress := '090';
194 -- SQL What: Update status of GBPA Headers.
195 -- SQL Why : Bulk update the status to APPROVED for the new GBPA's created
196 -- by the migration program.
197 -- SQL Join: po_header_id
198 FORALL i IN 1.. l_po_header_ids.COUNT
199 UPDATE PO_HEADERS_ALL GBPA
200 SET
201 -- Set it to APPROVED if there is no CPA_REFERENCE,
202 -- Else, if the status on the CPA is APPROVED, then
203 -- set it to APPROVED. If the status on CPA is anything
204 -- other than APPROVED, then set the status of the new
205 -- GBAP as INCOMPLETE.
206 authorization_status =
207 DECODE
208 (GBPA.cpa_reference,
209 NULL, 'APPROVED',
210 -- else
211 (SELECT DECODE
212 (CPA.authorization_status,
213 'APPROVED', 'APPROVED',
214 -- else
215 'INCOMPLETE')
216 FROM PO_HEADERS_ALL CPA
217 WHERE CPA.po_header_id = GBPA.cpa_reference)),
218 GBPA.approved_flag = 'Y',
219 GBPA.approved_date = sysdate,
220 GBPA.last_update_date = sysdate,
221 GBPA.last_updated_by = FND_GLOBAL.user_id,
222 GBPA.last_update_login = FND_GLOBAL.login_id,
223 GBPA.cat_admin_auth_enabled_flag = 'Y' -- Part of ECO bug 4554461
224 WHERE po_header_id = l_po_header_ids(i);
225
226 x_rows_processed := SQL%rowcount; --Bug#5156673 --TBD: What is the impact due to the loop ; mutiple loops doing multiple updates; x_rows_processed will not be the total rows proecssed by this worked then ?
227
228 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Number of rows of PO_HEADERS_ALL updated='||SQL%rowcount); END IF;
229
230 l_progress := '110';
231 -- SQL What: Bulk update the PO Number on the Archive table (there would
232 -- be only 1 revision = 0, for the newly created GBPA's)
233 -- SQL Why : PO Number on Archive table and Txn table should be same.
234 -- SQL Join: po_header_id, revision_num
235 FORALL i IN 1.. l_po_header_ids.COUNT
236 UPDATE PO_HEADERS_ARCHIVE_ALL
237 SET authorization_status = 'APPROVED',
238 approved_flag = 'Y',
239 approved_date = sysdate,
240 last_update_date = sysdate,
241 last_updated_by = FND_GLOBAL.user_id,
242 last_update_login = FND_GLOBAL.login_id
243 WHERE po_header_id = l_po_header_ids(i)
244 AND revision_num = 0;
245
246 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'Number of rows of PO_HEADERS_ARCHIVE_ALL updated='||SQL%rowcount); END IF;
247
248 l_progress := '110';
249 IF (l_po_header_ids.COUNT < p_batch_size) THEN
250 EXIT;
251 END IF;
252
253 l_progress := '120';
254 COMMIT;
255 EXCEPTION
256 WHEN g_SNAPSHOT_TOO_OLD THEN
257 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the transferred_gbpas_csr'); END IF;
258
259 -- Commit and re-open the cursor
260 l_progress := '080';
261 COMMIT;
262
263 l_progress := '090';
264 CLOSE transferred_gbpas_csr;
265
266 l_progress := '100';
267 OPEN transferred_gbpas_csr(p_start_rowid, p_end_rowid); --Bug#5156673
268 l_progress := '110';
269 END; -- block to handle SNAPSHOT_TOO_OLD exception
270 END LOOP; -- Main cursor batch loop
271
272 l_progress := '140';
273 IF (transferred_gbpas_csr%ISOPEN) THEN
274 CLOSE transferred_gbpas_csr;
275 END IF;
276
277 -- Insert GBPA numbers in the referenced CPA's as a long text attachment
278 -- Bug#5156673 : Not ad_parallelizing the rest of the code - as it
279 -- has a sort of group by logic for the attachments: order by and checks for current vs previous value.
280
281 -- attach_gbpa_numbers_in_cpa;
282
283 l_progress := '150';
284 -- Standard check of p_commit.
285 IF FND_API.to_boolean(p_commit) THEN
286 COMMIT;
287 END IF;
288
289 l_progress := '160';
290 -- Standard call to get message count and if count is 1, get message info.
291 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
292 p_data => x_msg_data );
293
294 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'END'); END IF;
295 EXCEPTION
296 WHEN OTHERS THEN
297 BEGIN
298 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'OTHERS Start'); END IF;
299 IF (transferred_gbpas_csr%ISOPEN) THEN
300 CLOSE transferred_gbpas_csr;
301 END IF;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303
304 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'x_return_status='||x_return_status); END IF;
305
306 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
307 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name,SQLERRM);
308 END IF;
309
310 -- Standard call to get message count and if count is 1, get message info.
311 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
312 p_data => x_msg_data );
313
314 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'OTHERS End'); END IF;
315 EXCEPTION
316 WHEN OTHERS THEN
317 NULL; -- If exception occurs inside the outer exception handling block, ignore it.
318 END;
319 END R12_upgrade_processing;
320
321 --------------------------------------------------------------------------------
322 --Start of Comments
323 --Name: create_action_history_batch
324 --Pre-reqs:
325 -- The iP catalog data has been migrated to PO Transaction tables.
326 --Modifies:
327 -- a) PO Archive Tables for Action History
328 -- b) FND_MSG_PUB on unhandled exceptions.
329 --Locks:
330 -- None.
331 --Function:
332 -- This procedure create the Action History of the GBPA's created as part of
333 -- the unified catalog migration.
334 -- This API should be called during the upgrade phase only.
335 --Parameters:
336 --IN:
337 --p_po_header_ids
338 -- A pl/sql table of po_header_id's that need to be archived.
339 --p_po_header_ids
340 -- A pl/sql table of agent_id's correspong to each header.
341 --OUT:
342 -- None
343 --End of Comments
344 --------------------------------------------------------------------------------
345 PROCEDURE create_action_history_batch
346 (
347 p_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER,
348 p_agent_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
349 )
350 IS
351 l_api_name CONSTANT VARCHAR2(30) := 'create_action_history_batch';
352 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
353 l_progress VARCHAR2(3) := '000';
354 BEGIN
355 l_progress := '010';
356 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
357
358 IF (p_po_header_ids.COUNT > 0) THEN
359 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_po_header_ids(1)='||p_po_header_ids(1)); END IF;
360 END IF;
361
362 IF (p_agent_ids.COUNT > 0) THEN
363 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_agent_ids(1)='||p_agent_ids(1)); END IF;
364 END IF;
365
366 -- SQL What: Bulk insert into Action History
367 -- SQL Why : To create action history for the new GBPA's
368 -- SQL Join: object_id, object_type_code
369 FORALL i IN 1.. p_po_header_ids.COUNT
370 INSERT INTO PO_ACTION_HISTORY
371 (object_id,
372 object_type_code,
373 object_sub_type_code,
374 sequence_num,
375 last_update_date,
376 last_updated_by,
377 creation_date,
378 created_by,
379 action_code,
380 action_date,
381 employee_id,
382 approval_path_id,
383 note,
384 object_revision_num,
385 offline_code,
386 last_update_login,
387 request_id,
388 program_application_id,
389 program_id,
390 program_update_date,
391 program_date
392 )
393 SELECT
394 p_po_header_ids(i), -- object_id
395 'PA', -- object_type_code
396 'BLANKET', -- object_sub_type_code
397 1, -- sequence_num
398 sysdate, -- last_update_date
399 FND_GLOBAL.user_id, -- last_updated_by
400 sysdate, -- creation_date
401 PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER, -- created_by = -12
402 'APPROVE', -- action_code
403 sysdate, -- action_date
404 p_agent_ids(i), -- employee_id
405 NULL, -- approval_path_id
406 NULL, -- note
407 0, -- object_revision_num
408 NULL, -- offline_code
409 FND_GLOBAL.login_id, -- last_update_login
410 FND_GLOBAL.conc_request_id, -- request_id
411 NULL, -- program_application_id
412 NULL, -- program_id
413 NULL, -- program_update_date
414 NULL -- program_date
415 FROM DUAL
416 WHERE NOT EXISTS
417 (SELECT 'Action History Record alreday exists'
418 FROM PO_ACTION_HISTORY POAH
419 WHERE POAH.object_id = p_po_header_ids(i)
420 AND POAH.object_type_code = 'PA'
421 AND POAH.object_sub_type_code = 'BLANKET');
422
423 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of action history records inserted='||SQL%rowcount); END IF;
424
425 l_progress := '010';
426 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
427 EXCEPTION
428 WHEN OTHERS THEN
429 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
430 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
431 END create_action_history_batch;
432
433 --------------------------------------------------------------------------------
434 --Start of Comments
435 --Name: archive_gbpa_bulk
436 --Pre-reqs:
437 -- The iP catalog data has been migrated to PO Transaction tables.
438 --Modifies:
439 -- a) PO Archive Tables
440 -- b) FND_MSG_PUB on unhandled exceptions.
441 --Locks:
442 -- None.
443 --Function:
444 -- This procedure archives the GBPA's created as part of the unified catalog
445 -- migration. It archives the PO Header, Line, Attribute, TLP and
446 -- Org Assignment tables.
447 -- This API should be called during the upgrade phase only.
448 --Parameters:
449 --IN:
450 --p_batch_size
451 -- The maximum number of rows that should be processed at a time, to avoid
452 -- exceeding rollback segment. The transaction would be committed after
453 -- processing each batch.
454 --p_po_header_ids
455 -- A pl/sql table of po_header_id's that need to be archived.
456 --OUT:
457 -- None
458 --End of Comments
459 --------------------------------------------------------------------------------
460 PROCEDURE archive_gbpa_bulk
461 (
462 p_batch_size NUMBER,
463 p_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
464 )
465 IS
466 l_api_name CONSTANT VARCHAR2(30) := 'archive_gbpa_bulk';
467 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
468 l_progress VARCHAR2(3) := '000';
469
470 l_po_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
471 l_start_index NUMBER;
472 l_end_index NUMBER;
473 l_end_index_tmp NUMBER;
474 l_last_batch_flag VARCHAR2(1);
475
476 l_key PO_SESSION_GT.key%TYPE;
477 BEGIN
478 l_progress := '010';
479 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
480
481 -- SQL What: Insert into headers archive
482 -- SQL Why : To archive the headers of the new GBPA's
483 -- SQL Join: po_header_id
484 FORALL i IN 1.. p_po_header_ids.COUNT
485 INSERT INTO PO_HEADERS_ARCHIVE_ALL
486 (
487 acceptance_due_date,
488 acceptance_required_flag,
489 agent_id,
490 amount_limit,
491 approval_required_flag,
492 approved_date,
493 approved_flag,
494 attribute1,
495 attribute10,
496 attribute11,
497 attribute12,
498 attribute13,
499 attribute14,
500 attribute15,
501 attribute2,
502 attribute3,
503 attribute4,
504 attribute5,
505 attribute6,
506 attribute7,
507 attribute8,
508 attribute9,
509 attribute_category,
510 authorization_status,
511 auto_sourcing_flag,
512 bill_to_location_id,
513 blanket_total_amount,
514 cancel_flag,
515 cbc_accounting_date,
516 change_requested_by,
517 change_summary,
518 closed_code,
519 closed_date,
520 comments,
521 confirming_order_flag,
522 consigned_consumption_flag,
523 consume_req_demand_flag,
524 conterms_articles_upd_date,
525 conterms_deliv_upd_date,
526 conterms_exist_flag,
527 cpa_reference,
528 created_by,
529 created_language,
530 creation_date,
531 currency_code,
532 document_creation_method,
533 edi_processed_flag,
534 edi_processed_status,
535 email_address,
536 enabled_flag,
537 encumbrance_required_flag,
538 end_date,
539 end_date_active,
540 fax,
541 firm_date,
542 firm_status_lookup_code,
543 fob_lookup_code,
544 freight_terms_lookup_code,
545 from_header_id,
546 from_type_lookup_code,
547 frozen_flag,
548 global_agreement_flag,
549 global_attribute1,
550 global_attribute10,
551 global_attribute11,
552 global_attribute12,
553 global_attribute13,
554 global_attribute14,
555 global_attribute15,
556 global_attribute16,
557 global_attribute17,
558 global_attribute18,
559 global_attribute19,
560 global_attribute2,
561 global_attribute20,
562 global_attribute3,
563 global_attribute4,
564 global_attribute5,
565 global_attribute6,
566 global_attribute7,
567 global_attribute8,
568 global_attribute9,
569 global_attribute_category,
570 government_context,
571 interface_source_code,
572 last_update_date,
573 last_update_login,
574 last_updated_by,
575 last_updated_program,
576 min_release_amount,
577 mrc_rate,
578 mrc_rate_date,
579 mrc_rate_type,
580 note_to_authorizer,
581 note_to_receiver,
582 note_to_vendor,
583 org_id,
584 pay_on_code,
585 pcard_id,
586 pending_signature_flag,
587 po_header_id,
588 price_update_tolerance,
589 print_count,
590 printed_date,
591 program_application_id,
592 program_id,
593 program_update_date,
594 quotation_class_code,
595 quote_type_lookup_code,
596 quote_vendor_quote_number,
597 quote_warning_delay,
598 quote_warning_delay_unit,
599 rate,
600 rate_date,
601 rate_type,
602 reference_num,
603 reply_date,
604 reply_method_lookup_code,
605 request_id,
606 retro_price_apply_updates_flag,
607 retro_price_comm_updates_flag,
608 revised_date,
609 revision_num,
610 rfq_close_date,
611 segment1,
612 segment2,
613 segment3,
614 segment4,
615 segment5,
616 ship_to_location_id,
617 ship_via_lookup_code,
618 shipping_control,
619 start_date,
620 start_date_active,
621 status_lookup_code,
622 style_id,
623 submit_date,
624 summary_flag,
625 supplier_auth_enabled_flag,
626 supplier_notif_method,
627 supply_agreement_flag,
628 terms_id,
629 type_lookup_code,
630 update_sourcing_rules_flag,
631 user_hold_flag,
632 ussgl_transaction_code,
633 vendor_contact_id,
634 vendor_id,
635 vendor_order_num,
636 vendor_site_id,
637 wf_item_key,
638 wf_item_type,
639 xml_change_send_date,
640 xml_flag,
641 xml_send_date,
642 ever_approved_flag, -- Not present in txn table
643 latest_external_flag, -- Not present in txn table
644 standard_comment_code -- Not present in txn table
645 )
646 SELECT
647 acceptance_due_date,
648 acceptance_required_flag,
649 agent_id,
650 amount_limit,
651 approval_required_flag,
652 approved_date,
653 approved_flag,
654 attribute1,
655 attribute10,
656 attribute11,
657 attribute12,
658 attribute13,
659 attribute14,
660 attribute15,
661 attribute2,
662 attribute3,
663 attribute4,
664 attribute5,
665 attribute6,
666 attribute7,
667 attribute8,
668 attribute9,
669 attribute_category,
670 authorization_status,
671 auto_sourcing_flag,
672 bill_to_location_id,
673 blanket_total_amount,
674 cancel_flag,
675 cbc_accounting_date,
676 change_requested_by,
677 change_summary,
678 closed_code,
679 closed_date,
680 comments,
681 confirming_order_flag,
682 consigned_consumption_flag,
683 consume_req_demand_flag,
684 conterms_articles_upd_date,
685 conterms_deliv_upd_date,
686 conterms_exist_flag,
687 cpa_reference,
688 created_by,
689 created_language,
690 creation_date,
691 currency_code,
692 document_creation_method,
693 edi_processed_flag,
694 edi_processed_status,
695 email_address,
696 enabled_flag,
697 encumbrance_required_flag,
698 end_date,
699 end_date_active,
700 fax,
701 firm_date,
702 firm_status_lookup_code,
703 fob_lookup_code,
704 freight_terms_lookup_code,
705 from_header_id,
706 from_type_lookup_code,
707 frozen_flag,
708 global_agreement_flag,
709 global_attribute1,
710 global_attribute10,
711 global_attribute11,
712 global_attribute12,
713 global_attribute13,
714 global_attribute14,
715 global_attribute15,
716 global_attribute16,
717 global_attribute17,
718 global_attribute18,
719 global_attribute19,
720 global_attribute2,
721 global_attribute20,
722 global_attribute3,
723 global_attribute4,
724 global_attribute5,
725 global_attribute6,
726 global_attribute7,
727 global_attribute8,
728 global_attribute9,
729 global_attribute_category,
730 government_context,
731 interface_source_code,
732 last_update_date,
733 last_update_login,
734 last_updated_by,
735 last_updated_program,
736 min_release_amount,
737 mrc_rate,
738 mrc_rate_date,
739 mrc_rate_type,
740 note_to_authorizer,
741 note_to_receiver,
742 note_to_vendor,
743 org_id,
744 pay_on_code,
745 pcard_id,
746 pending_signature_flag,
747 po_header_id,
748 price_update_tolerance,
749 print_count,
750 printed_date,
751 program_application_id,
752 program_id,
753 program_update_date,
754 quotation_class_code,
755 quote_type_lookup_code,
756 quote_vendor_quote_number,
757 quote_warning_delay,
758 quote_warning_delay_unit,
759 rate,
760 rate_date,
761 rate_type,
762 reference_num,
763 reply_date,
764 reply_method_lookup_code,
765 request_id,
766 retro_price_apply_updates_flag,
767 retro_price_comm_updates_flag,
768 revised_date,
769 revision_num,
770 rfq_close_date,
771 segment1,
772 segment2,
773 segment3,
774 segment4,
775 segment5,
776 ship_to_location_id,
777 ship_via_lookup_code,
778 shipping_control,
779 start_date,
780 start_date_active,
781 status_lookup_code,
782 style_id,
783 submit_date,
784 summary_flag,
785 supplier_auth_enabled_flag,
786 supplier_notif_method,
787 supply_agreement_flag,
788 terms_id,
789 type_lookup_code,
790 update_sourcing_rules_flag,
791 user_hold_flag,
792 ussgl_transaction_code,
793 vendor_contact_id,
794 vendor_id,
795 vendor_order_num,
796 vendor_site_id,
797 wf_item_key,
798 wf_item_type,
799 xml_change_send_date,
800 xml_flag,
801 xml_send_date,
802 'Y', -- ever_approved_flag, -- Not present in txn table
803 'Y', -- latest_external_flag, -- Not present in txn table
804 NULL -- standard_comment_code -- Not present in txn table
805 FROM PO_HEADERS_ALL
806 WHERE po_header_id = p_po_header_ids(i)
807 AND NOT EXISTS
808 (SELECT 'Archive record for Header already exists'
809 FROM PO_HEADERS_ARCHIVE_ALL POHA2
810 WHERE POHA2.po_header_id = p_po_header_ids(i));
811
812 l_progress := '020';
813 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of header archive records inserted='||SQL%rowcount); END IF;
814
815 -- Similarly, bulk insert into lines, attributes, TLP and org_assignment archives
816
817 -- SQL What: Pick a new key from session GT sequence .
818 -- SQL Why : To get po_line_id's
819 -- SQL Join: none
820 SELECT PO_SESSION_GT_S.nextval
821 INTO l_key
822 FROM DUAL;
823
824 l_progress := '030';
825 -- SQL What: Get all the line id's for the given set of header id's
826 -- SQL Why : To archive the GBPA lines.
827 -- SQL Join: po_header_id
828 FORALL i IN 1 .. p_po_header_ids.COUNT
829 INSERT INTO PO_SESSION_GT(key, num1)
830 SELECT l_key,
831 po_line_id
832 FROM PO_LINES_ALL
833 WHERE po_header_id = p_po_header_ids(i);
834
835 l_progress := '040';
836 -- SQL What: Transfer from session GT table to local array
837 -- SQL Why : The po_lie_id's are requied for archival of lines
838 -- SQL Join: key
839 DELETE FROM PO_SESSION_GT
840 WHERE key = l_key
841 RETURNING num1
842 BULK COLLECT INTO l_po_line_ids;
843
844 l_progress := '050';
845 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines='||l_po_line_ids.COUNT); END IF;
846
847 -- Archive the lines in batches
848 l_start_index := 1;
849 l_end_index := p_batch_size;
850 l_last_batch_flag := 'N';
851
852 l_progress := '060';
853 IF (l_po_line_ids.COUNT <= p_batch_size) THEN
854 l_end_index := l_po_line_ids.COUNT;
855 l_last_batch_flag := 'Y';
856 END IF;
857
858 l_progress := '070';
859 -- Archive lines
860 LOOP
861 l_progress := '080';
862
863 -- SQL What: Insert data into PO_LINES_ARCHIVE_ALL
864 -- SQL Why : To archive GBPA lines
865 -- SQL Join: po_line_id
866 FORALL i IN l_start_index .. l_end_index
867 INSERT INTO PO_LINES_ARCHIVE_ALL
868 (
869 allow_price_override_flag,
870 attribute1,
871 attribute10,
872 attribute11,
873 attribute12,
874 attribute13,
875 attribute14,
876 attribute15,
877 attribute2,
878 attribute3,
879 attribute4,
880 attribute5,
881 attribute6,
882 attribute7,
883 attribute8,
884 attribute9,
885 attribute_category,
886 auction_display_number,
887 auction_header_id,
888 auction_line_number,
889 base_qty,
890 base_uom,
891 bid_line_number,
892 bid_number,
893 cancel_date,
894 cancel_flag,
895 cancel_reason,
896 cancelled_by,
897 capital_expense_flag,
898 catalog_name,
899 category_id,
900 closed_by,
901 closed_code,
902 closed_date,
903 closed_flag,
904 closed_reason,
905 committed_amount,
906 contract_num,
907 created_by,
908 creation_date,
909 expiration_date,
910 firm_date,
911 firm_status_lookup_code,
912 from_header_id,
913 from_line_id,
914 global_attribute1,
915 global_attribute10,
916 global_attribute11,
917 global_attribute12,
918 global_attribute13,
919 global_attribute14,
920 global_attribute15,
921 global_attribute16,
922 global_attribute17,
923 global_attribute18,
924 global_attribute19,
925 global_attribute2,
926 global_attribute20,
927 global_attribute3,
928 global_attribute4,
929 global_attribute5,
930 global_attribute6,
931 global_attribute7,
932 global_attribute8,
933 global_attribute9,
934 global_attribute_category,
935 government_context,
936 hazard_class_id,
937 ip_category_id,
938 item_description,
939 item_id,
940 item_revision,
941 last_update_date,
942 last_update_login,
943 last_updated_by,
944 last_updated_program,
945 line_num,
946 line_reference_num,
947 line_type_id,
948 list_price_per_unit,
949 --manual_price_change_flag, Not present in archive table
950 market_price,
951 max_order_quantity,
952 min_order_quantity,
953 min_release_amount,
954 negotiated_by_preparer_flag,
955 not_to_exceed_price,
956 note_to_vendor,
957 --oke_contract_header_id, Not present in archive table
958 --oke_contract_version_id, Not present in archive table
959 org_id,
960 over_tolerance_error_flag,
961 po_header_id,
962 po_line_id,
963 preferred_grade,
964 price_break_lookup_code,
965 price_type_lookup_code,
966 program_application_id,
967 program_id,
968 program_update_date,
969 project_id,
970 qc_grade,
971 qty_rcv_tolerance,
972 quantity,
973 quantity_committed,
974 reference_num,
975 request_id,
976 --retroactive_date, Not present in archive table
977 secondary_qty,
978 secondary_quantity,
979 secondary_unit_of_measure,
980 secondary_uom,
981 supplier_part_auxid,
982 task_id,
983 tax_code_id,
984 tax_name,
985 taxable_flag,
986 transaction_reason_code,
987 type_1099,
988 un_number_id,
989 unit_meas_lookup_code,
990 unit_price,
991 unordered_flag,
992 user_hold_flag,
993 ussgl_transaction_code,
994 vendor_product_num,
995 --advance_amount, Not present in archive table
996 amount,
997 base_unit_price,
998 contract_id,
999 contractor_first_name,
1000 contractor_last_name,
1001 --from_line_location_id, Not present in archive table
1002 job_id,
1003 matching_basis,
1004 max_retainage_amount,
1005 order_type_lookup_code,
1006 progress_payment_rate,
1007 purchase_basis,
1008 recoupment_rate,
1009 retainage_rate,
1010 start_date,
1011 supplier_ref_number,
1012 svc_amount_notif_sent,
1013 svc_completion_notif_sent,
1014 latest_external_flag,
1015 revision_num
1016 )
1017 SELECT
1018 allow_price_override_flag,
1019 attribute1,
1020 attribute10,
1021 attribute11,
1022 attribute12,
1023 attribute13,
1024 attribute14,
1025 attribute15,
1026 attribute2,
1027 attribute3,
1028 attribute4,
1029 attribute5,
1030 attribute6,
1031 attribute7,
1032 attribute8,
1033 attribute9,
1034 attribute_category,
1035 auction_display_number,
1036 auction_header_id,
1037 auction_line_number,
1038 base_qty,
1039 base_uom,
1040 bid_line_number,
1041 bid_number,
1042 cancel_date,
1043 cancel_flag,
1044 cancel_reason,
1045 cancelled_by,
1046 capital_expense_flag,
1047 catalog_name,
1048 category_id,
1049 closed_by,
1050 closed_code,
1051 closed_date,
1052 closed_flag,
1053 closed_reason,
1054 committed_amount,
1055 contract_num,
1056 created_by,
1057 creation_date,
1058 expiration_date,
1059 firm_date,
1060 firm_status_lookup_code,
1061 from_header_id,
1062 from_line_id,
1063 global_attribute1,
1064 global_attribute10,
1065 global_attribute11,
1066 global_attribute12,
1067 global_attribute13,
1068 global_attribute14,
1069 global_attribute15,
1070 global_attribute16,
1071 global_attribute17,
1072 global_attribute18,
1073 global_attribute19,
1074 global_attribute2,
1075 global_attribute20,
1076 global_attribute3,
1077 global_attribute4,
1078 global_attribute5,
1079 global_attribute6,
1080 global_attribute7,
1081 global_attribute8,
1082 global_attribute9,
1083 global_attribute_category,
1084 government_context,
1085 hazard_class_id,
1086 ip_category_id,
1087 item_description,
1088 item_id,
1089 item_revision,
1090 last_update_date,
1091 last_update_login,
1092 last_updated_by,
1093 last_updated_program,
1094 line_num,
1095 line_reference_num,
1096 line_type_id,
1097 list_price_per_unit,
1098 --manual_price_change_flag, Not present in archive table
1099 market_price,
1100 max_order_quantity,
1101 min_order_quantity,
1102 min_release_amount,
1103 negotiated_by_preparer_flag,
1104 not_to_exceed_price,
1105 note_to_vendor,
1106 --oke_contract_header_id, Not present in archive table
1107 --oke_contract_version_id, Not present in archive table
1108 org_id,
1109 over_tolerance_error_flag,
1110 po_header_id,
1111 po_line_id,
1112 preferred_grade,
1113 price_break_lookup_code,
1114 price_type_lookup_code,
1115 program_application_id,
1116 program_id,
1117 program_update_date,
1118 project_id,
1119 qc_grade,
1120 qty_rcv_tolerance,
1121 quantity,
1122 quantity_committed,
1123 reference_num,
1124 request_id,
1125 -- retroactive_date, Not present in archive table
1126 secondary_qty,
1127 secondary_quantity,
1128 secondary_unit_of_measure,
1129 secondary_uom,
1130 supplier_part_auxid,
1131 task_id,
1132 tax_code_id,
1133 tax_name,
1134 taxable_flag,
1135 transaction_reason_code,
1136 type_1099,
1137 un_number_id,
1138 unit_meas_lookup_code,
1139 unit_price,
1140 unordered_flag,
1141 user_hold_flag,
1142 ussgl_transaction_code,
1143 vendor_product_num,
1144 --advance_amount, Not present in archive table
1145 amount,
1146 base_unit_price,
1147 contract_id,
1148 contractor_first_name,
1149 contractor_last_name,
1150 --from_line_location_id, Not present in archive table
1151 job_id,
1152 matching_basis,
1153 max_retainage_amount,
1154 order_type_lookup_code,
1155 progress_payment_rate,
1156 purchase_basis,
1157 recoupment_rate,
1158 retainage_rate,
1159 start_date,
1160 supplier_ref_number,
1161 svc_amount_notif_sent,
1162 svc_completion_notif_sent,
1163 'Y', -- latest_external_flag
1164 0 -- revision_num
1165 FROM PO_LINES_ALL POL
1166 WHERE POL.po_line_id = l_po_line_ids(i)
1167 AND NOT EXISTS
1168 (SELECT 'Archive record for Line already exists'
1169 FROM PO_LINES_ARCHIVE_ALL POLA2
1170 WHERE POLA2.po_line_id = l_po_line_ids(i));
1171
1172 l_progress := '090';
1173 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of line archive records inserted='||SQL%rowcount); END IF;
1174
1175 EXIT WHEN (l_last_batch_flag = 'Y');
1176
1177 l_start_index := l_start_index + p_batch_size;
1178 l_end_index_tmp := l_end_index + p_batch_size;
1179
1180 l_progress := '100';
1181 IF (l_end_index_tmp >= l_po_line_ids.COUNT) THEN
1182 l_end_index_tmp := l_po_line_ids.COUNT;
1183 l_last_batch_flag := 'Y';
1184 END IF;
1185
1186 l_end_index := l_end_index_tmp;
1187
1188 l_progress := '110';
1189 END LOOP; -- archive lines
1190
1191 l_progress := '120';
1192 -- Archive the attribute_values in batches
1193 l_start_index := 1;
1194 l_end_index := p_batch_size;
1195 l_last_batch_flag := 'N';
1196
1197 IF (l_po_line_ids.COUNT <= p_batch_size) THEN
1198 l_end_index := l_po_line_ids.COUNT;
1199 l_last_batch_flag := 'Y';
1200 END IF;
1201
1202 l_progress := '130';
1203 -- Archive attribute_values
1204 LOOP
1205 l_progress := '140';
1206
1207 -- SQL What: Insert data into PO_ATTR_VALUES_ARCHIVE
1208 -- SQL Why : To archive Attr values
1209 -- SQL Join: po_line_id
1210 FORALL i IN l_start_index .. l_end_index
1211 INSERT INTO PO_ATTR_VALUES_ARCHIVE
1212 (
1213 attribute_values_id,
1214 revision_num,
1215 po_line_id,
1216 req_template_name,
1217 req_template_line_num,
1218 ip_category_id,
1219 inventory_item_id,
1220 org_id,
1221 manufacturer_part_num,
1222 thumbnail_image,
1223 supplier_url,
1224 manufacturer_url,
1225 attachment_url,
1226 unspsc,
1227 availability,
1228 lead_time,
1229 text_base_attribute1,
1230 text_base_attribute2,
1231 text_base_attribute3,
1232 text_base_attribute4,
1233 text_base_attribute5,
1234 text_base_attribute6,
1235 text_base_attribute7,
1236 text_base_attribute8,
1237 text_base_attribute9,
1238 text_base_attribute10,
1239 text_base_attribute11,
1240 text_base_attribute12,
1241 text_base_attribute13,
1242 text_base_attribute14,
1243 text_base_attribute15,
1244 text_base_attribute16,
1245 text_base_attribute17,
1246 text_base_attribute18,
1247 text_base_attribute19,
1248 text_base_attribute20,
1249 text_base_attribute21,
1250 text_base_attribute22,
1251 text_base_attribute23,
1252 text_base_attribute24,
1253 text_base_attribute25,
1254 text_base_attribute26,
1255 text_base_attribute27,
1256 text_base_attribute28,
1257 text_base_attribute29,
1258 text_base_attribute30,
1259 text_base_attribute31,
1260 text_base_attribute32,
1261 text_base_attribute33,
1262 text_base_attribute34,
1263 text_base_attribute35,
1264 text_base_attribute36,
1265 text_base_attribute37,
1266 text_base_attribute38,
1267 text_base_attribute39,
1268 text_base_attribute40,
1269 text_base_attribute41,
1270 text_base_attribute42,
1271 text_base_attribute43,
1272 text_base_attribute44,
1273 text_base_attribute45,
1274 text_base_attribute46,
1275 text_base_attribute47,
1276 text_base_attribute48,
1277 text_base_attribute49,
1278 text_base_attribute50,
1279 text_base_attribute51,
1280 text_base_attribute52,
1281 text_base_attribute53,
1282 text_base_attribute54,
1283 text_base_attribute55,
1284 text_base_attribute56,
1285 text_base_attribute57,
1286 text_base_attribute58,
1287 text_base_attribute59,
1288 text_base_attribute60,
1289 text_base_attribute61,
1290 text_base_attribute62,
1291 text_base_attribute63,
1292 text_base_attribute64,
1293 text_base_attribute65,
1294 text_base_attribute66,
1295 text_base_attribute67,
1296 text_base_attribute68,
1297 text_base_attribute69,
1298 text_base_attribute70,
1299 text_base_attribute71,
1300 text_base_attribute72,
1301 text_base_attribute73,
1302 text_base_attribute74,
1303 text_base_attribute75,
1304 text_base_attribute76,
1305 text_base_attribute77,
1306 text_base_attribute78,
1307 text_base_attribute79,
1308 text_base_attribute80,
1309 text_base_attribute81,
1310 text_base_attribute82,
1311 text_base_attribute83,
1312 text_base_attribute84,
1313 text_base_attribute85,
1314 text_base_attribute86,
1315 text_base_attribute87,
1316 text_base_attribute88,
1317 text_base_attribute89,
1318 text_base_attribute90,
1319 text_base_attribute91,
1320 text_base_attribute92,
1321 text_base_attribute93,
1322 text_base_attribute94,
1323 text_base_attribute95,
1324 text_base_attribute96,
1325 text_base_attribute97,
1326 text_base_attribute98,
1327 text_base_attribute99,
1328 text_base_attribute100,
1329 num_base_attribute1,
1330 num_base_attribute2,
1331 num_base_attribute3,
1332 num_base_attribute4,
1333 num_base_attribute5,
1334 num_base_attribute6,
1335 num_base_attribute7,
1336 num_base_attribute8,
1337 num_base_attribute9,
1338 num_base_attribute10,
1339 num_base_attribute11,
1340 num_base_attribute12,
1341 num_base_attribute13,
1342 num_base_attribute14,
1343 num_base_attribute15,
1344 num_base_attribute16,
1345 num_base_attribute17,
1346 num_base_attribute18,
1347 num_base_attribute19,
1348 num_base_attribute20,
1349 num_base_attribute21,
1350 num_base_attribute22,
1351 num_base_attribute23,
1352 num_base_attribute24,
1353 num_base_attribute25,
1354 num_base_attribute26,
1355 num_base_attribute27,
1356 num_base_attribute28,
1357 num_base_attribute29,
1358 num_base_attribute30,
1359 num_base_attribute31,
1360 num_base_attribute32,
1361 num_base_attribute33,
1362 num_base_attribute34,
1363 num_base_attribute35,
1364 num_base_attribute36,
1365 num_base_attribute37,
1366 num_base_attribute38,
1367 num_base_attribute39,
1368 num_base_attribute40,
1369 num_base_attribute41,
1370 num_base_attribute42,
1371 num_base_attribute43,
1372 num_base_attribute44,
1373 num_base_attribute45,
1374 num_base_attribute46,
1375 num_base_attribute47,
1376 num_base_attribute48,
1377 num_base_attribute49,
1378 num_base_attribute50,
1379 num_base_attribute51,
1380 num_base_attribute52,
1381 num_base_attribute53,
1382 num_base_attribute54,
1383 num_base_attribute55,
1384 num_base_attribute56,
1385 num_base_attribute57,
1386 num_base_attribute58,
1387 num_base_attribute59,
1388 num_base_attribute60,
1389 num_base_attribute61,
1390 num_base_attribute62,
1391 num_base_attribute63,
1392 num_base_attribute64,
1393 num_base_attribute65,
1394 num_base_attribute66,
1395 num_base_attribute67,
1396 num_base_attribute68,
1397 num_base_attribute69,
1398 num_base_attribute70,
1399 num_base_attribute71,
1400 num_base_attribute72,
1401 num_base_attribute73,
1402 num_base_attribute74,
1403 num_base_attribute75,
1404 num_base_attribute76,
1405 num_base_attribute77,
1406 num_base_attribute78,
1407 num_base_attribute79,
1408 num_base_attribute80,
1409 num_base_attribute81,
1410 num_base_attribute82,
1411 num_base_attribute83,
1412 num_base_attribute84,
1413 num_base_attribute85,
1414 num_base_attribute86,
1415 num_base_attribute87,
1416 num_base_attribute88,
1417 num_base_attribute89,
1418 num_base_attribute90,
1419 num_base_attribute91,
1420 num_base_attribute92,
1421 num_base_attribute93,
1422 num_base_attribute94,
1423 num_base_attribute95,
1424 num_base_attribute96,
1425 num_base_attribute97,
1426 num_base_attribute98,
1427 num_base_attribute99,
1428 num_base_attribute100,
1429 text_cat_attribute1,
1430 text_cat_attribute2,
1431 text_cat_attribute3,
1432 text_cat_attribute4,
1433 text_cat_attribute5,
1434 text_cat_attribute6,
1435 text_cat_attribute7,
1436 text_cat_attribute8,
1437 text_cat_attribute9,
1438 text_cat_attribute10,
1439 text_cat_attribute11,
1440 text_cat_attribute12,
1441 text_cat_attribute13,
1442 text_cat_attribute14,
1443 text_cat_attribute15,
1444 text_cat_attribute16,
1445 text_cat_attribute17,
1446 text_cat_attribute18,
1447 text_cat_attribute19,
1448 text_cat_attribute20,
1449 text_cat_attribute21,
1450 text_cat_attribute22,
1451 text_cat_attribute23,
1452 text_cat_attribute24,
1453 text_cat_attribute25,
1454 text_cat_attribute26,
1455 text_cat_attribute27,
1456 text_cat_attribute28,
1457 text_cat_attribute29,
1458 text_cat_attribute30,
1459 text_cat_attribute31,
1460 text_cat_attribute32,
1461 text_cat_attribute33,
1462 text_cat_attribute34,
1463 text_cat_attribute35,
1464 text_cat_attribute36,
1465 text_cat_attribute37,
1466 text_cat_attribute38,
1467 text_cat_attribute39,
1468 text_cat_attribute40,
1469 text_cat_attribute41,
1470 text_cat_attribute42,
1471 text_cat_attribute43,
1472 text_cat_attribute44,
1473 text_cat_attribute45,
1474 text_cat_attribute46,
1475 text_cat_attribute47,
1476 text_cat_attribute48,
1477 text_cat_attribute49,
1478 text_cat_attribute50,
1479 num_cat_attribute1,
1480 num_cat_attribute2,
1481 num_cat_attribute3,
1482 num_cat_attribute4,
1483 num_cat_attribute5,
1484 num_cat_attribute6,
1485 num_cat_attribute7,
1486 num_cat_attribute8,
1487 num_cat_attribute9,
1488 num_cat_attribute10,
1489 num_cat_attribute11,
1490 num_cat_attribute12,
1491 num_cat_attribute13,
1492 num_cat_attribute14,
1493 num_cat_attribute15,
1494 num_cat_attribute16,
1495 num_cat_attribute17,
1496 num_cat_attribute18,
1497 num_cat_attribute19,
1498 num_cat_attribute20,
1499 num_cat_attribute21,
1500 num_cat_attribute22,
1501 num_cat_attribute23,
1502 num_cat_attribute24,
1503 num_cat_attribute25,
1504 num_cat_attribute26,
1505 num_cat_attribute27,
1506 num_cat_attribute28,
1507 num_cat_attribute29,
1508 num_cat_attribute30,
1509 num_cat_attribute31,
1510 num_cat_attribute32,
1511 num_cat_attribute33,
1512 num_cat_attribute34,
1513 num_cat_attribute35,
1514 num_cat_attribute36,
1515 num_cat_attribute37,
1516 num_cat_attribute38,
1517 num_cat_attribute39,
1518 num_cat_attribute40,
1519 num_cat_attribute41,
1520 num_cat_attribute42,
1521 num_cat_attribute43,
1522 num_cat_attribute44,
1523 num_cat_attribute45,
1524 num_cat_attribute46,
1525 num_cat_attribute47,
1526 num_cat_attribute48,
1527 num_cat_attribute49,
1528 num_cat_attribute50,
1529 last_update_login,
1530 last_updated_by,
1531 last_update_date,
1532 created_by,
1533 creation_date,
1534 request_id,
1535 program_application_id,
1536 program_id,
1537 program_update_date,
1538 last_updated_program,
1539 latest_external_flag
1540 )
1541 SELECT
1542 attribute_values_id,
1543 0, -- revision_num
1544 po_line_id,
1545 req_template_name,
1546 req_template_line_num,
1547 ip_category_id,
1548 inventory_item_id,
1549 org_id,
1550 manufacturer_part_num,
1551 thumbnail_image,
1552 supplier_url,
1553 manufacturer_url,
1554 attachment_url,
1555 unspsc,
1556 availability,
1557 lead_time,
1558 text_base_attribute1,
1559 text_base_attribute2,
1560 text_base_attribute3,
1561 text_base_attribute4,
1562 text_base_attribute5,
1563 text_base_attribute6,
1564 text_base_attribute7,
1565 text_base_attribute8,
1566 text_base_attribute9,
1567 text_base_attribute10,
1568 text_base_attribute11,
1569 text_base_attribute12,
1570 text_base_attribute13,
1571 text_base_attribute14,
1572 text_base_attribute15,
1573 text_base_attribute16,
1574 text_base_attribute17,
1575 text_base_attribute18,
1576 text_base_attribute19,
1577 text_base_attribute20,
1578 text_base_attribute21,
1579 text_base_attribute22,
1580 text_base_attribute23,
1581 text_base_attribute24,
1582 text_base_attribute25,
1583 text_base_attribute26,
1584 text_base_attribute27,
1585 text_base_attribute28,
1586 text_base_attribute29,
1587 text_base_attribute30,
1588 text_base_attribute31,
1589 text_base_attribute32,
1590 text_base_attribute33,
1591 text_base_attribute34,
1592 text_base_attribute35,
1593 text_base_attribute36,
1594 text_base_attribute37,
1595 text_base_attribute38,
1596 text_base_attribute39,
1597 text_base_attribute40,
1598 text_base_attribute41,
1599 text_base_attribute42,
1600 text_base_attribute43,
1601 text_base_attribute44,
1602 text_base_attribute45,
1603 text_base_attribute46,
1604 text_base_attribute47,
1605 text_base_attribute48,
1606 text_base_attribute49,
1607 text_base_attribute50,
1608 text_base_attribute51,
1609 text_base_attribute52,
1610 text_base_attribute53,
1611 text_base_attribute54,
1612 text_base_attribute55,
1613 text_base_attribute56,
1614 text_base_attribute57,
1615 text_base_attribute58,
1616 text_base_attribute59,
1617 text_base_attribute60,
1618 text_base_attribute61,
1619 text_base_attribute62,
1620 text_base_attribute63,
1621 text_base_attribute64,
1622 text_base_attribute65,
1623 text_base_attribute66,
1624 text_base_attribute67,
1625 text_base_attribute68,
1626 text_base_attribute69,
1627 text_base_attribute70,
1628 text_base_attribute71,
1629 text_base_attribute72,
1630 text_base_attribute73,
1631 text_base_attribute74,
1632 text_base_attribute75,
1633 text_base_attribute76,
1634 text_base_attribute77,
1635 text_base_attribute78,
1636 text_base_attribute79,
1637 text_base_attribute80,
1638 text_base_attribute81,
1639 text_base_attribute82,
1640 text_base_attribute83,
1641 text_base_attribute84,
1642 text_base_attribute85,
1643 text_base_attribute86,
1644 text_base_attribute87,
1645 text_base_attribute88,
1646 text_base_attribute89,
1647 text_base_attribute90,
1648 text_base_attribute91,
1649 text_base_attribute92,
1650 text_base_attribute93,
1651 text_base_attribute94,
1652 text_base_attribute95,
1653 text_base_attribute96,
1654 text_base_attribute97,
1655 text_base_attribute98,
1656 text_base_attribute99,
1657 text_base_attribute100,
1658 num_base_attribute1,
1659 num_base_attribute2,
1660 num_base_attribute3,
1661 num_base_attribute4,
1662 num_base_attribute5,
1663 num_base_attribute6,
1664 num_base_attribute7,
1665 num_base_attribute8,
1666 num_base_attribute9,
1667 num_base_attribute10,
1668 num_base_attribute11,
1669 num_base_attribute12,
1670 num_base_attribute13,
1671 num_base_attribute14,
1672 num_base_attribute15,
1673 num_base_attribute16,
1674 num_base_attribute17,
1675 num_base_attribute18,
1676 num_base_attribute19,
1677 num_base_attribute20,
1678 num_base_attribute21,
1679 num_base_attribute22,
1680 num_base_attribute23,
1681 num_base_attribute24,
1682 num_base_attribute25,
1683 num_base_attribute26,
1684 num_base_attribute27,
1685 num_base_attribute28,
1686 num_base_attribute29,
1687 num_base_attribute30,
1688 num_base_attribute31,
1689 num_base_attribute32,
1690 num_base_attribute33,
1691 num_base_attribute34,
1692 num_base_attribute35,
1693 num_base_attribute36,
1694 num_base_attribute37,
1695 num_base_attribute38,
1696 num_base_attribute39,
1697 num_base_attribute40,
1698 num_base_attribute41,
1699 num_base_attribute42,
1700 num_base_attribute43,
1701 num_base_attribute44,
1702 num_base_attribute45,
1703 num_base_attribute46,
1704 num_base_attribute47,
1705 num_base_attribute48,
1706 num_base_attribute49,
1707 num_base_attribute50,
1708 num_base_attribute51,
1709 num_base_attribute52,
1710 num_base_attribute53,
1711 num_base_attribute54,
1712 num_base_attribute55,
1713 num_base_attribute56,
1714 num_base_attribute57,
1715 num_base_attribute58,
1716 num_base_attribute59,
1717 num_base_attribute60,
1718 num_base_attribute61,
1719 num_base_attribute62,
1720 num_base_attribute63,
1721 num_base_attribute64,
1722 num_base_attribute65,
1723 num_base_attribute66,
1724 num_base_attribute67,
1725 num_base_attribute68,
1726 num_base_attribute69,
1727 num_base_attribute70,
1728 num_base_attribute71,
1729 num_base_attribute72,
1730 num_base_attribute73,
1731 num_base_attribute74,
1732 num_base_attribute75,
1733 num_base_attribute76,
1734 num_base_attribute77,
1735 num_base_attribute78,
1736 num_base_attribute79,
1737 num_base_attribute80,
1738 num_base_attribute81,
1739 num_base_attribute82,
1740 num_base_attribute83,
1741 num_base_attribute84,
1742 num_base_attribute85,
1743 num_base_attribute86,
1744 num_base_attribute87,
1745 num_base_attribute88,
1746 num_base_attribute89,
1747 num_base_attribute90,
1748 num_base_attribute91,
1749 num_base_attribute92,
1750 num_base_attribute93,
1751 num_base_attribute94,
1752 num_base_attribute95,
1753 num_base_attribute96,
1754 num_base_attribute97,
1755 num_base_attribute98,
1756 num_base_attribute99,
1757 num_base_attribute100,
1758 text_cat_attribute1,
1759 text_cat_attribute2,
1760 text_cat_attribute3,
1761 text_cat_attribute4,
1762 text_cat_attribute5,
1763 text_cat_attribute6,
1764 text_cat_attribute7,
1765 text_cat_attribute8,
1766 text_cat_attribute9,
1767 text_cat_attribute10,
1768 text_cat_attribute11,
1769 text_cat_attribute12,
1770 text_cat_attribute13,
1771 text_cat_attribute14,
1772 text_cat_attribute15,
1773 text_cat_attribute16,
1774 text_cat_attribute17,
1775 text_cat_attribute18,
1776 text_cat_attribute19,
1777 text_cat_attribute20,
1778 text_cat_attribute21,
1779 text_cat_attribute22,
1780 text_cat_attribute23,
1781 text_cat_attribute24,
1782 text_cat_attribute25,
1783 text_cat_attribute26,
1784 text_cat_attribute27,
1785 text_cat_attribute28,
1786 text_cat_attribute29,
1787 text_cat_attribute30,
1788 text_cat_attribute31,
1789 text_cat_attribute32,
1790 text_cat_attribute33,
1791 text_cat_attribute34,
1792 text_cat_attribute35,
1793 text_cat_attribute36,
1794 text_cat_attribute37,
1795 text_cat_attribute38,
1796 text_cat_attribute39,
1797 text_cat_attribute40,
1798 text_cat_attribute41,
1799 text_cat_attribute42,
1800 text_cat_attribute43,
1801 text_cat_attribute44,
1802 text_cat_attribute45,
1803 text_cat_attribute46,
1804 text_cat_attribute47,
1805 text_cat_attribute48,
1806 text_cat_attribute49,
1807 text_cat_attribute50,
1808 num_cat_attribute1,
1809 num_cat_attribute2,
1810 num_cat_attribute3,
1811 num_cat_attribute4,
1812 num_cat_attribute5,
1813 num_cat_attribute6,
1814 num_cat_attribute7,
1815 num_cat_attribute8,
1816 num_cat_attribute9,
1817 num_cat_attribute10,
1818 num_cat_attribute11,
1819 num_cat_attribute12,
1820 num_cat_attribute13,
1821 num_cat_attribute14,
1822 num_cat_attribute15,
1823 num_cat_attribute16,
1824 num_cat_attribute17,
1825 num_cat_attribute18,
1826 num_cat_attribute19,
1827 num_cat_attribute20,
1828 num_cat_attribute21,
1829 num_cat_attribute22,
1830 num_cat_attribute23,
1831 num_cat_attribute24,
1832 num_cat_attribute25,
1833 num_cat_attribute26,
1834 num_cat_attribute27,
1835 num_cat_attribute28,
1836 num_cat_attribute29,
1837 num_cat_attribute30,
1838 num_cat_attribute31,
1839 num_cat_attribute32,
1840 num_cat_attribute33,
1841 num_cat_attribute34,
1842 num_cat_attribute35,
1843 num_cat_attribute36,
1844 num_cat_attribute37,
1845 num_cat_attribute38,
1846 num_cat_attribute39,
1847 num_cat_attribute40,
1848 num_cat_attribute41,
1849 num_cat_attribute42,
1850 num_cat_attribute43,
1851 num_cat_attribute44,
1852 num_cat_attribute45,
1853 num_cat_attribute46,
1854 num_cat_attribute47,
1855 num_cat_attribute48,
1856 num_cat_attribute49,
1857 num_cat_attribute50,
1858 last_update_login,
1859 last_updated_by,
1860 last_update_date,
1861 created_by,
1862 creation_date,
1863 request_id,
1864 program_application_id,
1865 program_id,
1866 program_update_date,
1867 last_updated_program,
1868 'Y' -- latest_external_flag
1869 FROM PO_ATTRIBUTE_VALUES
1870 WHERE po_line_id = l_po_line_ids(i)
1871 AND NOT EXISTS
1872 (SELECT 'Archive record for Attr already exists'
1873 FROM PO_ATTR_VALUES_ARCHIVE POAVA2
1874 WHERE POAVA2.po_line_id = l_po_line_ids(i));
1875
1876 l_progress := '150';
1877 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of attr archive records inserted='||SQL%rowcount); END IF;
1878
1879 EXIT WHEN (l_last_batch_flag = 'Y');
1880
1881 l_start_index := l_start_index + p_batch_size;
1882 l_end_index_tmp := l_end_index + p_batch_size;
1883
1884 IF (l_end_index_tmp >= l_po_line_ids.COUNT) THEN
1885 l_end_index_tmp := l_po_line_ids.COUNT;
1886 l_last_batch_flag := 'Y';
1887 END IF;
1888
1889 l_end_index := l_end_index_tmp;
1890
1891 l_progress := '160';
1892 END LOOP; -- archive attribute_values
1893
1894 l_progress := '170';
1895 -- Archive the attribute_values_tlp in batches
1896 l_start_index := 1;
1897 l_end_index := p_batch_size;
1898 l_last_batch_flag := 'N';
1899
1900 IF (l_po_line_ids.COUNT <= p_batch_size) THEN
1901 l_end_index := l_po_line_ids.COUNT;
1902 l_last_batch_flag := 'Y';
1903 END IF;
1904
1905 l_progress := '180';
1906 -- Archive attribute_values_tlp
1907 LOOP
1908 l_progress := '190';
1909
1910 -- SQL What: Insert data into PO_ATTR_VALUES_TLP_ARCHIVE
1911 -- SQL Why : To archive Attr TLP values
1912 -- SQL Join: po_line_id
1913 FORALL i IN l_start_index .. l_end_index
1914 INSERT INTO PO_ATTR_VALUES_TLP_ARCHIVE
1915 (
1916 attribute_values_tlp_id,
1917 revision_num,
1918 latest_external_flag,
1919 po_line_id,
1920 req_template_name,
1921 req_template_line_num,
1922 ip_category_id,
1923 inventory_item_id,
1924 org_id,
1925 language,
1926 description,
1927 manufacturer,
1928 comments,
1929 alias,
1930 long_description,
1931 tl_text_base_attribute1,
1932 tl_text_base_attribute2,
1933 tl_text_base_attribute3,
1934 tl_text_base_attribute4,
1935 tl_text_base_attribute5,
1936 tl_text_base_attribute6,
1937 tl_text_base_attribute7,
1938 tl_text_base_attribute8,
1939 tl_text_base_attribute9,
1940 tl_text_base_attribute10,
1941 tl_text_base_attribute11,
1942 tl_text_base_attribute12,
1943 tl_text_base_attribute13,
1944 tl_text_base_attribute14,
1945 tl_text_base_attribute15,
1946 tl_text_base_attribute16,
1947 tl_text_base_attribute17,
1948 tl_text_base_attribute18,
1949 tl_text_base_attribute19,
1950 tl_text_base_attribute20,
1951 tl_text_base_attribute21,
1952 tl_text_base_attribute22,
1953 tl_text_base_attribute23,
1954 tl_text_base_attribute24,
1955 tl_text_base_attribute25,
1956 tl_text_base_attribute26,
1957 tl_text_base_attribute27,
1958 tl_text_base_attribute28,
1959 tl_text_base_attribute29,
1960 tl_text_base_attribute30,
1961 tl_text_base_attribute31,
1962 tl_text_base_attribute32,
1963 tl_text_base_attribute33,
1964 tl_text_base_attribute34,
1965 tl_text_base_attribute35,
1966 tl_text_base_attribute36,
1967 tl_text_base_attribute37,
1968 tl_text_base_attribute38,
1969 tl_text_base_attribute39,
1970 tl_text_base_attribute40,
1971 tl_text_base_attribute41,
1972 tl_text_base_attribute42,
1973 tl_text_base_attribute43,
1974 tl_text_base_attribute44,
1975 tl_text_base_attribute45,
1976 tl_text_base_attribute46,
1977 tl_text_base_attribute47,
1978 tl_text_base_attribute48,
1979 tl_text_base_attribute49,
1980 tl_text_base_attribute50,
1981 tl_text_base_attribute51,
1982 tl_text_base_attribute52,
1983 tl_text_base_attribute53,
1984 tl_text_base_attribute54,
1985 tl_text_base_attribute55,
1986 tl_text_base_attribute56,
1987 tl_text_base_attribute57,
1988 tl_text_base_attribute58,
1989 tl_text_base_attribute59,
1990 tl_text_base_attribute60,
1991 tl_text_base_attribute61,
1992 tl_text_base_attribute62,
1993 tl_text_base_attribute63,
1994 tl_text_base_attribute64,
1995 tl_text_base_attribute65,
1996 tl_text_base_attribute66,
1997 tl_text_base_attribute67,
1998 tl_text_base_attribute68,
1999 tl_text_base_attribute69,
2000 tl_text_base_attribute70,
2001 tl_text_base_attribute71,
2002 tl_text_base_attribute72,
2003 tl_text_base_attribute73,
2004 tl_text_base_attribute74,
2005 tl_text_base_attribute75,
2006 tl_text_base_attribute76,
2007 tl_text_base_attribute77,
2008 tl_text_base_attribute78,
2009 tl_text_base_attribute79,
2010 tl_text_base_attribute80,
2011 tl_text_base_attribute81,
2012 tl_text_base_attribute82,
2013 tl_text_base_attribute83,
2014 tl_text_base_attribute84,
2015 tl_text_base_attribute85,
2016 tl_text_base_attribute86,
2017 tl_text_base_attribute87,
2018 tl_text_base_attribute88,
2019 tl_text_base_attribute89,
2020 tl_text_base_attribute90,
2021 tl_text_base_attribute91,
2022 tl_text_base_attribute92,
2023 tl_text_base_attribute93,
2024 tl_text_base_attribute94,
2025 tl_text_base_attribute95,
2026 tl_text_base_attribute96,
2027 tl_text_base_attribute97,
2028 tl_text_base_attribute98,
2029 tl_text_base_attribute99,
2030 tl_text_base_attribute100,
2031 tl_text_cat_attribute1,
2032 tl_text_cat_attribute2,
2033 tl_text_cat_attribute3,
2034 tl_text_cat_attribute4,
2035 tl_text_cat_attribute5,
2036 tl_text_cat_attribute6,
2037 tl_text_cat_attribute7,
2038 tl_text_cat_attribute8,
2039 tl_text_cat_attribute9,
2040 tl_text_cat_attribute10,
2041 tl_text_cat_attribute11,
2042 tl_text_cat_attribute12,
2043 tl_text_cat_attribute13,
2044 tl_text_cat_attribute14,
2045 tl_text_cat_attribute15,
2046 tl_text_cat_attribute16,
2047 tl_text_cat_attribute17,
2048 tl_text_cat_attribute18,
2049 tl_text_cat_attribute19,
2050 tl_text_cat_attribute20,
2051 tl_text_cat_attribute21,
2052 tl_text_cat_attribute22,
2053 tl_text_cat_attribute23,
2054 tl_text_cat_attribute24,
2055 tl_text_cat_attribute25,
2056 tl_text_cat_attribute26,
2057 tl_text_cat_attribute27,
2058 tl_text_cat_attribute28,
2059 tl_text_cat_attribute29,
2060 tl_text_cat_attribute30,
2061 tl_text_cat_attribute31,
2062 tl_text_cat_attribute32,
2063 tl_text_cat_attribute33,
2064 tl_text_cat_attribute34,
2065 tl_text_cat_attribute35,
2066 tl_text_cat_attribute36,
2067 tl_text_cat_attribute37,
2068 tl_text_cat_attribute38,
2069 tl_text_cat_attribute39,
2070 tl_text_cat_attribute40,
2071 tl_text_cat_attribute41,
2072 tl_text_cat_attribute42,
2073 tl_text_cat_attribute43,
2074 tl_text_cat_attribute44,
2075 tl_text_cat_attribute45,
2076 tl_text_cat_attribute46,
2077 tl_text_cat_attribute47,
2078 tl_text_cat_attribute48,
2079 tl_text_cat_attribute49,
2080 tl_text_cat_attribute50,
2081 last_update_login,
2082 last_updated_by,
2083 last_update_date,
2084 created_by,
2085 creation_date,
2086 request_id,
2087 program_application_id,
2088 program_id,
2089 program_update_date,
2090 last_updated_program
2091 )
2092 SELECT
2093 attribute_values_tlp_id,
2094 0, -- revision_num
2095 'Y', -- latest_external_flag,
2096 po_line_id,
2097 req_template_name,
2098 req_template_line_num,
2099 ip_category_id,
2100 inventory_item_id,
2101 org_id,
2102 language,
2103 description,
2104 manufacturer,
2105 comments,
2106 alias,
2107 long_description,
2108 tl_text_base_attribute1,
2109 tl_text_base_attribute2,
2110 tl_text_base_attribute3,
2111 tl_text_base_attribute4,
2112 tl_text_base_attribute5,
2113 tl_text_base_attribute6,
2114 tl_text_base_attribute7,
2115 tl_text_base_attribute8,
2116 tl_text_base_attribute9,
2117 tl_text_base_attribute10,
2118 tl_text_base_attribute11,
2119 tl_text_base_attribute12,
2120 tl_text_base_attribute13,
2121 tl_text_base_attribute14,
2122 tl_text_base_attribute15,
2123 tl_text_base_attribute16,
2124 tl_text_base_attribute17,
2125 tl_text_base_attribute18,
2126 tl_text_base_attribute19,
2127 tl_text_base_attribute20,
2128 tl_text_base_attribute21,
2129 tl_text_base_attribute22,
2130 tl_text_base_attribute23,
2131 tl_text_base_attribute24,
2132 tl_text_base_attribute25,
2133 tl_text_base_attribute26,
2134 tl_text_base_attribute27,
2135 tl_text_base_attribute28,
2136 tl_text_base_attribute29,
2137 tl_text_base_attribute30,
2138 tl_text_base_attribute31,
2139 tl_text_base_attribute32,
2140 tl_text_base_attribute33,
2141 tl_text_base_attribute34,
2142 tl_text_base_attribute35,
2143 tl_text_base_attribute36,
2144 tl_text_base_attribute37,
2145 tl_text_base_attribute38,
2146 tl_text_base_attribute39,
2147 tl_text_base_attribute40,
2148 tl_text_base_attribute41,
2149 tl_text_base_attribute42,
2150 tl_text_base_attribute43,
2151 tl_text_base_attribute44,
2152 tl_text_base_attribute45,
2153 tl_text_base_attribute46,
2154 tl_text_base_attribute47,
2155 tl_text_base_attribute48,
2156 tl_text_base_attribute49,
2157 tl_text_base_attribute50,
2158 tl_text_base_attribute51,
2159 tl_text_base_attribute52,
2160 tl_text_base_attribute53,
2161 tl_text_base_attribute54,
2162 tl_text_base_attribute55,
2163 tl_text_base_attribute56,
2164 tl_text_base_attribute57,
2165 tl_text_base_attribute58,
2166 tl_text_base_attribute59,
2167 tl_text_base_attribute60,
2168 tl_text_base_attribute61,
2169 tl_text_base_attribute62,
2170 tl_text_base_attribute63,
2171 tl_text_base_attribute64,
2172 tl_text_base_attribute65,
2173 tl_text_base_attribute66,
2174 tl_text_base_attribute67,
2175 tl_text_base_attribute68,
2176 tl_text_base_attribute69,
2177 tl_text_base_attribute70,
2178 tl_text_base_attribute71,
2179 tl_text_base_attribute72,
2180 tl_text_base_attribute73,
2181 tl_text_base_attribute74,
2182 tl_text_base_attribute75,
2183 tl_text_base_attribute76,
2184 tl_text_base_attribute77,
2185 tl_text_base_attribute78,
2186 tl_text_base_attribute79,
2187 tl_text_base_attribute80,
2188 tl_text_base_attribute81,
2189 tl_text_base_attribute82,
2190 tl_text_base_attribute83,
2191 tl_text_base_attribute84,
2192 tl_text_base_attribute85,
2193 tl_text_base_attribute86,
2194 tl_text_base_attribute87,
2195 tl_text_base_attribute88,
2196 tl_text_base_attribute89,
2197 tl_text_base_attribute90,
2198 tl_text_base_attribute91,
2199 tl_text_base_attribute92,
2200 tl_text_base_attribute93,
2201 tl_text_base_attribute94,
2202 tl_text_base_attribute95,
2203 tl_text_base_attribute96,
2204 tl_text_base_attribute97,
2205 tl_text_base_attribute98,
2206 tl_text_base_attribute99,
2207 tl_text_base_attribute100,
2208 tl_text_cat_attribute1,
2209 tl_text_cat_attribute2,
2210 tl_text_cat_attribute3,
2211 tl_text_cat_attribute4,
2212 tl_text_cat_attribute5,
2213 tl_text_cat_attribute6,
2214 tl_text_cat_attribute7,
2215 tl_text_cat_attribute8,
2216 tl_text_cat_attribute9,
2217 tl_text_cat_attribute10,
2218 tl_text_cat_attribute11,
2219 tl_text_cat_attribute12,
2220 tl_text_cat_attribute13,
2221 tl_text_cat_attribute14,
2222 tl_text_cat_attribute15,
2223 tl_text_cat_attribute16,
2224 tl_text_cat_attribute17,
2225 tl_text_cat_attribute18,
2226 tl_text_cat_attribute19,
2227 tl_text_cat_attribute20,
2228 tl_text_cat_attribute21,
2229 tl_text_cat_attribute22,
2230 tl_text_cat_attribute23,
2231 tl_text_cat_attribute24,
2232 tl_text_cat_attribute25,
2233 tl_text_cat_attribute26,
2234 tl_text_cat_attribute27,
2235 tl_text_cat_attribute28,
2236 tl_text_cat_attribute29,
2237 tl_text_cat_attribute30,
2238 tl_text_cat_attribute31,
2239 tl_text_cat_attribute32,
2240 tl_text_cat_attribute33,
2241 tl_text_cat_attribute34,
2242 tl_text_cat_attribute35,
2243 tl_text_cat_attribute36,
2244 tl_text_cat_attribute37,
2245 tl_text_cat_attribute38,
2246 tl_text_cat_attribute39,
2247 tl_text_cat_attribute40,
2248 tl_text_cat_attribute41,
2249 tl_text_cat_attribute42,
2250 tl_text_cat_attribute43,
2251 tl_text_cat_attribute44,
2252 tl_text_cat_attribute45,
2253 tl_text_cat_attribute46,
2254 tl_text_cat_attribute47,
2255 tl_text_cat_attribute48,
2256 tl_text_cat_attribute49,
2257 tl_text_cat_attribute50,
2258 last_update_login,
2259 last_updated_by,
2260 last_update_date,
2261 created_by,
2262 creation_date,
2263 request_id,
2264 program_application_id,
2265 program_id,
2266 program_update_date,
2267 last_updated_program
2268 FROM PO_ATTRIBUTE_VALUES_TLP POTLP
2269 WHERE po_line_id = l_po_line_ids(i)
2270 AND NOT EXISTS
2271 (SELECT 'Archive record for TLP already exists'
2272 FROM PO_ATTR_VALUES_TLP_ARCHIVE POAVTA2
2273 WHERE POAVTA2.po_line_id = POTLP.po_line_id
2274 AND POAVTA2.language = POTLP.language);
2275
2276 l_progress := '200';
2277 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of TLP archive records inserted='||SQL%rowcount); END IF;
2278
2279 EXIT WHEN (l_last_batch_flag = 'Y');
2280
2281 l_start_index := l_start_index + p_batch_size;
2282 l_end_index_tmp := l_end_index + p_batch_size;
2283
2284 IF (l_end_index_tmp >= l_po_line_ids.COUNT) THEN
2285 l_end_index_tmp := l_po_line_ids.COUNT;
2286 l_last_batch_flag := 'Y';
2287 END IF;
2288
2289 l_end_index := l_end_index_tmp;
2290
2291 l_progress := '210';
2292 END LOOP; -- archive attribute_tlp_values
2293
2294 l_progress := '210';
2295 -- Archive the org_assignments table
2296 -- SQL What: Insert data into PO_GA_ORG_ASSIGNMENTS_ARCHIVE
2297 -- SQL Why : To archive org assignments
2298 -- SQL Join: po_header_id
2299 FORALL i IN 1.. p_po_header_ids.COUNT
2300 INSERT INTO PO_GA_ORG_ASSIGNMENTS_ARCHIVE
2301 (
2302 po_header_id,
2303 organization_id,
2304 enabled_flag,
2305 vendor_site_id,
2306 last_update_date,
2307 last_updated_by,
2308 creation_date,
2309 revision_num,
2310 created_by,
2311 last_update_login,
2312 purchasing_org_id,
2313 latest_external_flag,
2314 org_assignment_id
2315 )
2316 SELECT
2317 po_header_id,
2318 organization_id,
2319 enabled_flag,
2320 vendor_site_id,
2321 last_update_date,
2322 last_updated_by,
2323 creation_date,
2324 0, -- revision_num
2325 created_by,
2326 last_update_login,
2327 purchasing_org_id,
2328 'Y', -- latest_external_flag
2329 org_assignment_id
2330 FROM PO_GA_ORG_ASSIGNMENTS POGA
2331 WHERE po_header_id = p_po_header_ids(i)
2332 AND NOT EXISTS
2333 (SELECT 'Archive record for Org Assignement already exists'
2334 FROM PO_GA_ORG_ASSIGNMENTS_ARCHIVE POGAA2
2335 WHERE POGAA2.org_assignment_id = POGA.org_assignment_id);
2336
2337 l_progress := '220';
2338 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of org assignment archive records inserted='||SQL%rowcount); END IF;
2339
2340 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2341 EXCEPTION
2342 WHEN OTHERS THEN
2343 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2344 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2345 END archive_gbpa_bulk;
2346
2347 FUNCTION get_next_po_number
2348 (
2349 p_org_id IN NUMBER
2350 )
2351 RETURN VARCHAR2
2352 IS
2353 l_api_name CONSTANT VARCHAR2(30) := 'get_next_po_number';
2354 l_api_version CONSTANT NUMBER := 1.0;
2355 l_module CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2356 l_progress VARCHAR2(3) := '000';
2357
2358 l_new_po_number PO_HEADERS_ALL.segment1%TYPE;
2359 BEGIN
2360 l_progress := '010';
2361 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'START'); END IF;
2362
2363 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'p_org_id='||p_org_id); END IF;
2364
2365 l_progress := '020';
2366 -- Set the org context because the fuction default_po_unique_identifier
2367 -- depends on the org context. It uses the org-striped view
2368 -- PO_UNIQUE_IDENTIFIER_CONTROL to get the next PO Number.
2369 --FND_CLIENT_ INFO.set_org_context(p_org_id);
2370 PO_MOAC_UTILS_PVT.set_policy_context('S', p_org_id); -- Bug#5259328
2371
2372 l_progress := '030';
2373 l_new_po_number := PO_CORE_SV1.default_po_unique_identifier('PO_HEADERS');
2374
2375 l_progress := '040';
2376 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'l_new_po_number='||l_new_po_number); END IF;
2377
2378 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_module,l_progress,'END'); END IF;
2379 RETURN l_new_po_number;
2380 END get_next_po_number;
2381
2382 --------------------------------------------------------------------------------
2383 --Start of Comments
2384 --Name: prepare_long_text
2385 --Pre-reqs:
2386 -- None
2387 --Modifies:
2388 -- None
2389 --Locks:
2390 -- None.
2391 --Function:
2392 -- This procedure creates a long text containing a list of GBPA numbers and
2393 -- their corresponding Operating Units. These GBPA are those that were created
2394 -- during catalog migration and refer the given CPA.
2395 --
2396 -- This API should be called during the upgrade phase only.
2397 --Parameters:
2398 --IN:
2399 --p_cpa_header_id
2400 -- The PO_HEADER_ID of the Contract for which the attachment needs to be
2401 -- created.
2402 --p_gbpa_header_id_list
2403 -- A list of PO_HEADER_ID's of GBPA's that have the given CPA in the
2404 -- CPA_REFERENCE column.
2405 --OUT:
2406 --x_long_text
2407 -- The long text containing the GBPA numbers and OU names.
2408 --End of Comments
2409 --------------------------------------------------------------------------------
2410 PROCEDURE prepare_long_text
2411 (
2412 p_cpa_header_id IN NUMBER
2413 , p_gbpa_header_id_list IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
2414 , x_long_text IN OUT NOCOPY LONG
2415 )
2416 IS
2417 l_api_name CONSTANT VARCHAR2(30) := 'prepare_long_text';
2418 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2419 l_progress VARCHAR2(3) := '000';
2420
2421 l_long_text LONG := NULL;
2422 l_gbpa_number PO_HEADERS_ALL.segment1%TYPE;
2423 l_ou_name HR_ALL_ORGANIZATION_UNITS_TL.name%TYPE;
2424 l_gbpa_info LONG;
2425
2426 -- Bug 4941073:
2427 -- GSCC Error: File.SQL.10: Do not use CHR character function in sql scripts
2428 -- Instead, use FND_GLOBAL.local_chr(x)
2429 --l_eoln_char CONSTANT VARCHAR2(1) := chr(10);
2430 --l_tab_char CONSTANT VARCHAR2(1) := chr(09);
2431 l_eoln_char CONSTANT VARCHAR2(1) := FND_GLOBAL.local_chr(10);
2432 l_tab_char CONSTANT VARCHAR2(1) := FND_GLOBAL.local_chr(09);
2433
2434 l_heading_global_blanket FND_NEW_MESSAGES.message_text%TYPE;
2435 l_heading_ou_name FND_NEW_MESSAGES.message_text%TYPE;
2436
2437 BEGIN
2438 l_progress := '010';
2439 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2440
2441 FND_MESSAGE.set_name('PO','PO_GA_TYPE'); -- 'Global Agreement'
2442 l_heading_global_blanket := FND_MESSAGE.get;
2443
2444 FND_MESSAGE.set_name('PO','PO_R12_CAT_UPG_ATTACH_OU_NAME'); -- 'Operating Unit Name'
2445 l_heading_ou_name := FND_MESSAGE.get;
2446
2447 -- TODO: Get this message from PM. Also check issue of formatting.
2448 l_long_text := l_tab_char || l_heading_global_blanket|| l_tab_char || l_tab_char ||
2449 l_heading_ou_name || l_eoln_char || l_eoln_char;
2450
2451 FOR i IN 1 .. p_gbpa_header_id_list.COUNT LOOP
2452 -- SQL What: Get the PO Number for the GBPA and the OU Name
2453 -- SQL Why : It will be inserted as long text attachment in CPA
2454 -- SQL Join: po_header_id, organization_id, language
2455 SELECT POH.segment1,
2456 HROUTL.name
2457 INTO l_gbpa_number,
2458 l_ou_name
2459 FROM PO_HEADERS_ALL POH,
2460 HR_ALL_ORGANIZATION_UNITS_TL HROUTL
2461 WHERE POH.po_header_id = p_gbpa_header_id_list(i)
2462 AND HROUTL.organization_id = POH.org_id
2463 AND HROUTL.language = userenv('LANG');
2464
2465 l_gbpa_info := '' || i || '.' || l_tab_char || l_tab_char ||
2466 l_gbpa_number ||
2467 l_tab_char || l_tab_char || l_tab_char || l_tab_char ||
2468 l_ou_name ||
2469 l_eoln_char;
2470
2471 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gbpa_info='||to_char(l_gbpa_info)); END IF;
2472
2473 l_long_text := l_long_text || l_gbpa_info;
2474 END LOOP;
2475
2476 x_long_text := l_long_text; -- OUT value
2477
2478 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_long_text='||to_char(x_long_text)); END IF;
2479
2480 l_progress := '060';
2481 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2485 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2486 END prepare_long_text;
2487
2488 --------------------------------------------------------------------------------
2489 --Start of Comments
2490 --Name: create_attachment
2491 --Pre-reqs:
2492 -- None
2493 --Modifies:
2494 -- FND_DOCUMENTS, FND_DOCUMENTS_LONG_TEXT, FND_ATTACHED_DOCUMENTS
2495 --Locks:
2496 -- None.
2497 --Function:
2498 -- This procedure creates a long text attachment at the header level of the
2499 -- given CPA. The attachment contains a list of GBPA numbers and their
2500 -- corresponding Operating Units. These GBPA are those that were created
2501 -- during catalog migration and refer the given CPA.
2502 --
2503 -- This API should be called during the upgrade phase only.
2504 --Parameters:
2505 --IN:
2506 --p_cpa_header_id
2507 -- The PO_HEADER_ID of the Contract for which the attachment needs to be
2508 -- created.
2509 --p_cpa_org_id
2510 -- The ORG_ID of the CPA. This is used as the security_id while creating
2511 -- the attachment.
2512 --p_gbpa_header_id_list
2513 -- A list of PO_HEADER_ID's of GBPA's that have the given CPA in the
2514 -- CPA_REFERENCE column.
2515 --OUT:
2516 -- None
2517 --End of Comments
2518 --------------------------------------------------------------------------------
2519 PROCEDURE create_attachment
2520 (
2521 p_cpa_header_id IN NUMBER
2522 , p_cpa_org_id IN NUMBER
2523 , p_gbpa_header_id_list IN PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER
2524 )
2525 IS
2526 l_api_name CONSTANT VARCHAR2(30) := 'create_attachment';
2527 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2528 l_progress VARCHAR2(3) := '000';
2529
2530 l_rowid VARCHAR2(30);
2531 l_document_id NUMBER;
2532 l_security_id NUMBER;
2533 l_media_id NUMBER;
2534 l_seq_num NUMBER;
2535 l_description VARCHAR2(200);
2536 l_long_text LONG;
2537 l_to_pk1_value FND_ATTACHED_DOCUMENTS.pk1_value%TYPE;
2538 l_to_entity_name FND_ATTACHED_DOCUMENTS.entity_name%TYPE;
2539
2540 BEGIN
2541 l_progress := '010';
2542 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2543
2544 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_cpa_header_id='||p_cpa_header_id); END IF;
2545 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_cpa_org_id='||p_cpa_org_id); END IF;
2546 IF (p_gbpa_header_id_list IS NOT NULL) THEN
2547 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_gbpa_header_id_list.COUNT='||p_gbpa_header_id_list.COUNT); END IF;
2548 ELSE
2549 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_gbpa_header_id_list is NULL'); END IF;
2550 END IF;
2551
2552 FND_MESSAGE.set_name('PO','PO_R12_CAT_UPG_ATTACH_REF_GBPA'); -- 'Referencing Global Agreements'
2553 l_description := FND_MESSAGE.get;
2554
2555 l_to_entity_name := 'PO_HEADERS';
2556 l_to_pk1_value := p_cpa_header_id;
2557 --l_security_id := PO_MOAC_UTILS_PVT.get_current_org_id;
2558 l_security_id := p_cpa_header_id;
2559
2560 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'security_id(org_id)='||l_security_id); END IF;
2561 -- IF g_debug THEN
2562 -- IF (l_security_id IS NULL) THEN
2563 -- l_security_id := 204;
2564 -- IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'DEBUG: security_id(org_id) was NULL. Now hardcoded to:'||l_security_id); END IF;
2565 -- END IF;
2566 -- END IF;
2567
2568 l_progress := '020';
2569 -- Prepare long text
2570 prepare_long_text
2571 (
2572 p_cpa_header_id => p_cpa_header_id
2573 , p_gbpa_header_id_list => p_gbpa_header_id_list
2574 , x_long_text => l_long_text -- IN/OUT
2575 );
2576
2577 l_progress := '030';
2578 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Calling FND_DOCUMENTS_PKG.insert_row()'); END IF;
2579 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Parameter: userenv(LANG)='||userenv('LANG')); END IF;
2580
2581 -- Insert into FND_DOCUMENTS
2582 FND_DOCUMENTS_PKG.insert_row
2583 (
2584 x_rowid => l_rowid -- IN/OUT
2585 , x_document_id => l_document_id -- IN/OUT
2586 , x_creation_date => sysdate
2587 , x_created_by => FND_GLOBAL.user_id
2588 , x_last_update_date => sysdate
2589 , x_last_updated_by => FND_GLOBAL.user_id
2590 , x_last_update_login => FND_GLOBAL.login_id
2591 , x_datatype_id => 2 -- Long Text
2592 , x_category_id => 1 -- Miscellaneous
2593 , x_security_type => 1
2594 , x_security_id => l_security_id
2595 , x_publish_flag => 'Y'
2596 , x_usage_type => 'O' -- 'One-time'. Other options include 'Std'.
2597 , x_program_update_date => sysdate
2598 , x_language => userenv('LANG')
2599 , x_description => l_description
2600 , x_media_id => l_media_id -- IN/OUT
2601 );
2602
2603 l_progress := '040';
2604 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'FND_DOCUMENTS_PKG.insert_row() returns media_id='||l_media_id||', document_id='||l_document_id||', row_id='||l_rowid); END IF;
2605
2606 -- SQL What: Insert long text into FND_DOCUMENTS_LONG_TEXT
2607 -- SQL Why : This long text will be attached to the CPA
2608 -- SQL Join: none
2609 INSERT INTO FND_DOCUMENTS_LONG_TEXT
2610 (
2611 media_id
2612 , long_text
2613 )
2614 VALUES
2615 (
2616 l_media_id
2617 , l_long_text
2618 );
2619
2620 l_progress := '050';
2621 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows inserted in FND_DOCUMENTS_LONG_TEXT='||SQL%rowcount); END IF;
2622
2623 -- SQL What: Get maximum sequence number for the CPA header attachments
2624 -- SQL Why : To get the next sequence number for the new attachment
2625 -- SQL Join: pk1_value, entity_name
2626 SELECT max(seq_num)
2627 INTO l_seq_num
2628 FROM FND_ATTACHED_DOCUMENTS
2629 WHERE pk1_value = l_to_pk1_value
2630 AND entity_name = l_to_entity_name;
2631
2632 l_seq_num := nvl(l_seq_num, 0) + 10;
2633
2634 l_progress := '060';
2635 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Next seq_num='||l_seq_num); END IF;
2636
2637 -- SQL What: Insert document into FND_ATTACHED_DOCUMENTS
2638 -- SQL Why : This long text document will be attached to the CPA
2639 -- SQL Join: none
2640 INSERT INTO FND_ATTACHED_DOCUMENTS
2641 (
2642 attached_document_id
2643 , document_id
2644 , creation_date
2645 , created_by
2646 , last_update_date
2647 , last_updated_by
2648 , last_update_login
2649 , seq_num
2650 , entity_name
2651 , pk1_value
2652 , automatically_added_flag
2653 , program_update_date
2654 )
2655 VALUES
2656 (
2657 FND_ATTACHED_DOCUMENTS_S.nextval
2658 , l_document_id
2659 , sysdate
2660 , FND_GLOBAL.user_id
2661 , sysdate
2662 , FND_GLOBAL.user_id
2663 , FND_GLOBAL.login_id
2664 , l_seq_num
2665 , l_to_entity_name
2666 , l_to_pk1_value
2667 , 'N'
2668 , sysdate
2669 );
2670
2671 l_progress := '070';
2672 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows inserted in FND_ATTACHED_DOCUMENTS='||SQL%rowcount); END IF;
2673
2674 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2675 EXCEPTION
2676 WHEN OTHERS THEN
2677 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2678 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2679 END create_attachment;
2680
2681 --------------------------------------------------------------------------------
2682 --Start of Comments
2683 --Name: attach_gbpa_numbers_in_cpa
2684 --Pre-reqs:
2685 -- None
2686 --Modifies:
2687 -- a) PO_HEADERS_ALL: updated CPA_REFERENCE column
2688 -- b) FND_MSG_PUB on unhandled exceptions.
2689 --Locks:
2690 -- None.
2691 --Function:
2692 -- This procedure gathers the GBPA numbers that refer a CPA and then
2693 -- creates a long text attachment in the CPA header, with the list
2694 -- of the GBPA numbers and their owning Operating Units.
2695 --
2696 -- This API should be called during the upgrade phase only.
2697 --Parameters:
2698 --IN:
2699 -- None
2700 --OUT:
2701 -- None
2702 --End of Comments
2703 --------------------------------------------------------------------------------
2704 PROCEDURE attach_gbpa_numbers_in_cpa
2705 IS
2706 l_api_name CONSTANT VARCHAR2(30) := 'attach_gbpa_numbers_in_cpa';
2707 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2708 l_progress VARCHAR2(3) := '000';
2709
2710 -- SQL What: Cursor to fetch all the CPA's that have at least one GBPA
2711 -- referring to it.
2712 -- SQL Why : It will be used to create a long text attachment in the CPA
2713 -- SQL Join: type_lookup_code, created_by, authorization_status, cpa_reference
2714 CURSOR cpa_references_csr IS
2715 SELECT GBPA.cpa_reference,
2716 CPA.org_id cpa_org_id,
2717 GBPA.po_header_id
2718 FROM PO_HEADERS_ALL GBPA,
2719 PO_HEADERS_ALL CPA
2720 WHERE GBPA.type_lookup_code = 'BLANKET'
2721 AND GBPA.created_by = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER
2722 AND GBPA.authorization_status = 'IN PROCESS'
2723 AND GBPA.cpa_reference IS NOT NULL
2724 AND CPA.po_header_id = GBPA.cpa_reference
2725 ORDER BY GBPA.cpa_reference, GBPA.org_id, GBPA.po_header_id;
2726
2727 l_cpa_reference NUMBER;
2728 l_cpa_org_id NUMBER;
2729 l_po_header_id NUMBER;
2730 l_cpa_reference_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2731 l_po_header_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2732
2733 l_prev_cpa_reference NUMBER;
2734 l_count NUMBER;
2735 l_current_batch NUMBER; -- Bug 5468308: Track the progress of the script
2736 BEGIN
2737 l_progress := '010';
2738 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2739
2740 OPEN cpa_references_csr;
2741
2742 l_progress := '020';
2743 l_count := 0;
2744 l_prev_cpa_reference := NULL;
2745 l_current_batch := 0;
2746 LOOP
2747 l_current_batch := l_current_batch + 1;
2748
2749 l_progress := '025';
2750 -- Bug 5468308: Adding FND log messages at Unexpected level.
2751 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2752 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_log_head||'.'||l_progress,
2753 'current_batch='||l_current_batch);
2754 END IF;
2755
2756 FETCH cpa_references_csr
2757 INTO l_cpa_reference, l_cpa_org_id, l_po_header_id;
2758
2759 l_progress := '030';
2760 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'cpa_reference='||l_cpa_reference||', cpa_org_id='||l_cpa_org_id||', po_header_id='||l_po_header_id); END IF;
2761
2762 EXIT WHEN CPA_REFERENCES_CSR%NOTFOUND;
2763
2764 IF (l_prev_cpa_reference IS NOT NULL AND
2765 l_cpa_reference <> l_prev_cpa_reference) THEN
2766
2767 -- Create the attachment
2768 create_attachment
2769 (
2770 p_cpa_header_id => l_prev_cpa_reference
2771 , p_cpa_org_id => l_cpa_org_id
2772 , p_gbpa_header_id_list => l_po_header_id_list
2773 );
2774
2775 -- Reset the l_po_header_id_list and the counter
2776 l_po_header_id_list.DELETE;
2777 l_count := 0;
2778 END IF;
2779
2780 l_count := l_count + 1;
2781 l_po_header_id_list(l_count) := l_po_header_id;
2782
2783 -- Mark the previous CPA_REFERENCE in the list
2784 l_prev_cpa_reference := l_cpa_reference;
2785
2786 END LOOP; -- Main cursor loop
2787
2788 -- Create the attachment for the last batch of the loop above
2789 IF (l_prev_cpa_reference IS NOT NULL) THEN
2790 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Calling create_attchment() outside the loop'); END IF;
2791 create_attachment
2792 (
2793 p_cpa_header_id => l_prev_cpa_reference
2794 , p_cpa_org_id => l_cpa_org_id
2795 , p_gbpa_header_id_list => l_po_header_id_list
2796 );
2797 END IF;
2798
2799 l_progress := '140';
2800 CLOSE cpa_references_csr;
2801
2802 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of CPAs updated='||SQL%rowcount); END IF;
2803
2804 l_progress := '010';
2805 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2806 EXCEPTION
2807 WHEN OTHERS THEN
2808 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2809 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2810 END attach_gbpa_numbers_in_cpa;
2811
2812 END PO_R12_CAT_UPG_FINAL_GRP;