[Home] [Help]
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_PVT';
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: -- Value of IP_CATEGORY_ID column if no category mappings exist
11: g_NULL_IP_CATEGORY_ID CONSTANT NUMBER := -2;
153: l_intf_hdr_id_list DBMS_SQL.NUMBER_TABLE;
154: l_count NUMBER;
155: BEGIN
156: l_progress := '010';
157: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
158: IF g_debug THEN
159: IF (p_doc_headers_table.interface_header_id IS NOT NULL) THEN
160: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_table.COUNT='||p_doc_headers_table.interface_header_id.COUNT);
161: ELSE
156: l_progress := '010';
157: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
158: IF g_debug THEN
159: IF (p_doc_headers_table.interface_header_id IS NOT NULL) THEN
160: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_table.COUNT='||p_doc_headers_table.interface_header_id.COUNT);
161: ELSE
162: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_table.interface_header_id IS NULL');
163: END IF;
164: END IF;
158: IF g_debug THEN
159: IF (p_doc_headers_table.interface_header_id IS NOT NULL) THEN
160: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_table.COUNT='||p_doc_headers_table.interface_header_id.COUNT);
161: ELSE
162: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_table.interface_header_id IS NULL');
163: END IF;
164: END IF;
165:
166: IF ( (p_doc_headers_table.interface_header_id IS NOT NULL) AND
175: l_intf_hdr_id_list(l_count) := p_doc_headers_table.interface_header_id(i);
176: END IF;
177: END LOOP;
178:
179: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of non-errored headers being sent to IP='||l_count); END IF;
180:
181: IF (l_intf_hdr_id_list.COUNT > 0) THEN
182: ICX_CAT_R12_UPGRADE_GRP.updatePOHeaderId
183: (
190: );
191: END IF;
192: END IF;
193:
194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
195: EXCEPTION
196: WHEN OTHERS THEN
197: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
198: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
193:
194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
195: EXCEPTION
196: WHEN OTHERS THEN
197: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
198: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
199: END update_ip_tables_hdr;
200:
201: --------------------------------------------------------------------------------
232: l_intf_line_id_list DBMS_SQL.NUMBER_TABLE;
233: l_count NUMBER;
234: BEGIN
235: l_progress := '010';
236: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
237: IF g_debug THEN
238: IF (p_doc_lines_table.interface_line_id IS NOT NULL) THEN
239: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_table.COUNT='||p_doc_lines_table.interface_line_id.COUNT);
240: ELSE
235: l_progress := '010';
236: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
237: IF g_debug THEN
238: IF (p_doc_lines_table.interface_line_id IS NOT NULL) THEN
239: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_table.COUNT='||p_doc_lines_table.interface_line_id.COUNT);
240: ELSE
241: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_table.interface_line_id IS NULL');
242: END IF;
243: END IF;
237: IF g_debug THEN
238: IF (p_doc_lines_table.interface_line_id IS NOT NULL) THEN
239: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_table.COUNT='||p_doc_lines_table.interface_line_id.COUNT);
240: ELSE
241: PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_table.interface_line_id IS NULL');
242: END IF;
243: END IF;
244:
245: IF ( (p_doc_lines_table.interface_line_id IS NOT NULL) AND
254: l_intf_line_id_list(l_count) := p_doc_lines_table.interface_line_id(i);
255: END IF;
256: END LOOP;
257:
258: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of non-errored lines being sent to IP='||l_count); END IF;
259:
260: IF (l_intf_line_id_list.COUNT > 0) THEN
261: ICX_CAT_R12_UPGRADE_GRP.updatePOLineId
262: (
269: );
270: END IF;
271: END IF;
272:
273: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
274: EXCEPTION
275: WHEN OTHERS THEN
276: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
277: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
272:
273: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
274: EXCEPTION
275: WHEN OTHERS THEN
276: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
277: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
278: END update_ip_tables_line;
279: --Bug 4865553: End>
280:
365: l_return_status VARCHAR2(100);
366: l_intf_hdr_id_list DBMS_SQL.NUMBER_TABLE;
367: BEGIN
368: l_progress := '010';
369: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
370: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'g_processing_id='||g_processing_id); END IF;
371:
372: IF ( (g_processing_id IS NULL) OR
373: (g_processing_id <= 0)) THEN
366: l_intf_hdr_id_list DBMS_SQL.NUMBER_TABLE;
367: BEGIN
368: l_progress := '010';
369: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
370: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'g_processing_id='||g_processing_id); END IF;
371:
372: IF ( (g_processing_id IS NULL) OR
373: (g_processing_id <= 0)) THEN
374: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'g_processing_id is invalid. Early return...'); END IF;
370: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'g_processing_id='||g_processing_id); END IF;
371:
372: IF ( (g_processing_id IS NULL) OR
373: (g_processing_id <= 0)) THEN
374: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'g_processing_id is invalid. Early return...'); END IF;
375: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
376: RETURN;
377: END IF;
378:
371:
372: IF ( (g_processing_id IS NULL) OR
373: (g_processing_id <= 0)) THEN
374: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'g_processing_id is invalid. Early return...'); END IF;
375: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
376: RETURN;
377: END IF;
378:
379: OPEN load_err_headers_csr(g_processing_id);
387: BULK COLLECT INTO l_interface_header_ids, l_err_po_header_ids
388: LIMIT g_job.batch_size;
389:
390: l_progress := '030';
391: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids.COUNT='||l_interface_header_ids.COUNT); END IF;
392:
393: EXIT WHEN l_interface_header_ids.COUNT = 0;
394:
395: l_progress := '040';
418: processing_id = -processing_id,
419: po_header_id = NULL -- Bug 4865553: Null out the PO_HEADER_ID before calling IP's API
420: WHERE interface_header_id = l_interface_header_ids(i);
421:
422: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of records marked as error in headers interface='||SQL%rowcount); END IF;
423:
424: l_progress := '060';
425: -- SQL What: Delete header from txn tables
426: -- SQL Why : To cleanup corrupt data
428: FORALL i IN 1.. l_err_po_header_ids.COUNT
429: DELETE FROM PO_HEADERS_ALL
430: WHERE po_header_id = l_err_po_header_ids(i);
431:
432: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of records deleted from txn Headers='||SQL%rowcount); END IF;
433:
434: l_progress := '070';
435: COMMIT;
436:
457: EXIT;
458: END IF;
459: EXCEPTION
460: WHEN g_SNAPSHOT_TOO_OLD THEN
461: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_err_headers_csr'); END IF;
462:
463: -- Commit and re-open the cursor
464: l_progress := '080';
465: COMMIT;
478: IF (load_err_headers_csr%ISOPEN) THEN
479: CLOSE load_err_headers_csr;
480: END IF;
481:
482: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
483: EXCEPTION
484: WHEN OTHERS THEN
485: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
486: IF (load_err_headers_csr%ISOPEN) THEN
481:
482: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
483: EXCEPTION
484: WHEN OTHERS THEN
485: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
486: IF (load_err_headers_csr%ISOPEN) THEN
487: CLOSE load_err_headers_csr;
488: END IF;
489: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
551: l_progress VARCHAR2(3) := '000';
552: l_return_status VARCHAR2(1);
553: BEGIN
554: l_progress := '010';
555: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
556:
557: -- Initialize API return status to success
558: x_return_status := FND_API.G_RET_STS_SUCCESS;
559:
581: -- Migrate lines
582: migrate_document_lines(p_validate_only_mode => p_validate_only_mode);
583:
584: l_progress := '050';
585: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
586: EXCEPTION
587: WHEN OTHERS THEN
588: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
589: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
584: l_progress := '050';
585: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
586: EXCEPTION
587: WHEN OTHERS THEN
588: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
589: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
590: END migrate_documents;
591:
592: --------------------------------------------------------------------------------
632: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
633: l_progress VARCHAR2(3) := '000';
634: BEGIN
635: l_progress := '010';
636: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
637:
638: PO_R12_CAT_UPG_UTL.init_startup_values
639: ( p_commit => p_commit,
640: p_selected_batch_id => p_batch_id,
655: p_validate_only_mode => p_validate_only_mode
656: );
657:
658: l_progress := '020';
659: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
660: EXCEPTION
661: WHEN OTHERS THEN
662: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
663: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
658: l_progress := '020';
659: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
660: EXCEPTION
661: WHEN OTHERS THEN
662: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
663: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
664: END initialize_system_values;
665:
666: --------------------------------------------------------------------------------
707: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
708: l_progress VARCHAR2(3) := '000';
709: BEGIN
710: l_progress := '010';
711: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
712:
713: -- Updates all records that will be processed by the current request with a
714: -- single processing_id. Each time PDOI is called, a new processing_id is
715: -- generated. All operations afterwards will only look at records with this
744:
745: l_progress := '060';
746: COMMIT;
747:
748: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
749: EXCEPTION
750: WHEN OTHERS THEN
751: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
752: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
747:
748: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
749: EXCEPTION
750: WHEN OTHERS THEN
751: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
752: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
753: END pre_process;
754:
755: --------------------------------------------------------------------------------
947: l_err_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
948: l_count NUMBER := NULL;
949: BEGIN
950: l_progress := '010';
951: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
952:
953: -- Algorithm:
954: -- 1. Load Headers batch (batch_size) into pl/sql table.
955: -- 2. Call PDOI modules to process data in batches (default, derive, validate).
1113: LIMIT g_job.batch_size;
1114:
1115: l_progress := '030';
1116:
1117: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_doc_headers_table.interface_header_id.COUNT='||l_doc_headers_table.interface_header_id.COUNT); END IF;
1118:
1119: IF (l_doc_headers_table.interface_header_id.COUNT > 0) THEN
1120: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_doc_headers_table.po_header_id(1)='||l_doc_headers_table.po_header_id(1)); END IF;
1121: END IF;
1116:
1117: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_doc_headers_table.interface_header_id.COUNT='||l_doc_headers_table.interface_header_id.COUNT); END IF;
1118:
1119: IF (l_doc_headers_table.interface_header_id.COUNT > 0) THEN
1120: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_doc_headers_table.po_header_id(1)='||l_doc_headers_table.po_header_id(1)); END IF;
1121: END IF;
1122:
1123: EXIT WHEN l_doc_headers_table.interface_header_id.COUNT = 0;
1124:
1179: END IF;
1180: l_progress := '140';
1181: EXCEPTION
1182: WHEN g_SNAPSHOT_TOO_OLD THEN
1183: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_headers_csr'); END IF;
1184:
1185: -- Commit and re-open the cursor
1186: l_progress := '150';
1187: COMMIT;
1199: IF (load_headers_csr%ISOPEN) THEN
1200: CLOSE load_headers_csr;
1201: END IF;
1202:
1203: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1204: EXCEPTION
1205: WHEN OTHERS THEN
1206: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1207: IF (load_headers_csr%ISOPEN) THEN
1202:
1203: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1204: EXCEPTION
1205: WHEN OTHERS THEN
1206: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1207: IF (load_headers_csr%ISOPEN) THEN
1208: CLOSE load_headers_csr;
1209: END IF;
1210: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1246: l_progress VARCHAR2(3) := '000';
1247:
1248: BEGIN
1249: l_progress := '010';
1250: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1251:
1252: -- Insert Headers
1253: insert_doc_headers(x_doc_headers_rec => x_doc_headers_rec);
1254:
1259: l_progress := '030';
1260: -- Delete Headers
1261: delete_doc_headers(p_doc_headers_rec => x_doc_headers_rec);
1262:
1263: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1264: EXCEPTION
1265: WHEN OTHERS THEN
1266: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1267: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1262:
1263: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1264: EXCEPTION
1265: WHEN OTHERS THEN
1266: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1267: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1268: END transfer_doc_headers;
1269:
1270: --------------------------------------------------------------------------------
1334: l_is_attach_err_intf_hdr_id PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR1;
1335:
1336: BEGIN
1337: l_progress := '010';
1338: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1339:
1340: l_progress := '020';
1341: l_err_count := 0;
1342: FOR i IN 1 .. p_valid_po_hdr_ids.COUNT
1372:
1373: l_err_count := l_err_count + 1;
1374: l_is_attach_err_intf_hdr_id(p_valid_intf_hdr_ids(i)) := 'Y'; -- sparse collection
1375:
1376: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Copy attachment error for INTF_HDR_ID='||p_valid_intf_hdr_ids(i)||', PO_HEADER_ID='||p_valid_po_hdr_ids(i)); END IF;
1377:
1378: FOR j IN 1 .. x_doc_headers_rec.interface_header_id.COUNT
1379: LOOP
1380: l_progress := '140';
1398: END; -- exception block
1399: END IF; -- IF (p_valid_cpa_references(i) IS NOT NULL)
1400: END LOOP; -- copy attachment loop
1401:
1402: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of err in copy attach='||l_err_count); END IF;
1403:
1404: -- Now subtract the attachment errored records so that Org Assignments are created
1405: -- only for the successfully inserted ones.
1406: l_count := 0;
1410: -- DELETE the attachment copy errored header from txn table
1411: DELETE FROM PO_HEADERS_ALL
1412: WHERE PO_HEADER_ID = p_valid_po_hdr_ids(i);
1413:
1414: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO Header deleted for '||i); END IF;
1415: ELSE
1416: l_count := l_count + 1;
1417: x_remaining_val_intf_hdr_ids(l_count) := p_valid_intf_hdr_ids(i);
1418: x_remaining_val_po_hdr_ids(l_count) := p_valid_po_hdr_ids(i);
1419: x_remaining_val_vndr_site_ids(l_count) := p_valid_vndr_site_ids(i);
1420: END IF;
1421: END LOOP;
1422:
1423: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of success copy attach='||l_count); END IF;
1424:
1425: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1426: EXCEPTION
1427: WHEN OTHERS THEN
1421: END LOOP;
1422:
1423: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of success copy attach='||l_count); END IF;
1424:
1425: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1426: EXCEPTION
1427: WHEN OTHERS THEN
1428: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1429: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1424:
1425: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1426: EXCEPTION
1427: WHEN OTHERS THEN
1428: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1429: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1430: END copy_cpa_attachments;
1431:
1432: --------------------------------------------------------------------------------
1500: j NUMBER;
1501: l_is_valid_cpa VARCHAR2(1);
1502: BEGIN
1503: l_progress := '010';
1504: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1505:
1506: -- First copy the CPA attachments into the new GBPA.
1507: copy_cpa_attachments
1508: (
1534: x_remaining_val_po_hdr_ids(i) IS NOT NULL AND
1535: x_doc_headers_rec.has_errors(j) <> 'Y' ) THEN
1536:
1537: l_progress := '050';
1538: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'cpa_reference('||j||')='||x_doc_headers_rec.cpa_reference(j)); END IF;
1539:
1540: -- Validate the CPA
1541: l_is_valid_cpa := 'N';
1542:
1552: l_is_valid_cpa := 'N';
1553: END;
1554:
1555: l_progress := '060';
1556: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_is_valid_cpa='||l_is_valid_cpa); END IF;
1557:
1558: IF (l_is_valid_cpa = 'Y') THEN
1559: l_progress := '070';
1560: UPDATE PO_HEADERS_ALL GBPA
1732: FROM PO_HEADERS_ALL CPA
1733: WHERE CPA.po_header_id = x_doc_headers_rec.cpa_reference(j))
1734: WHERE GBPA.po_header_id = x_remaining_val_po_hdr_ids(i);
1735:
1736: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Copied attributes of CPA header_id='||x_doc_headers_rec.cpa_reference(j)||' into GBPA header_id='||x_remaining_val_po_hdr_ids(i)); END IF;
1737: END IF; -- IF (l_is_valid_cpa = 'Y')
1738: END IF;
1739: END LOOP;
1740: END LOOP;
1738: END IF;
1739: END LOOP;
1740: END LOOP;
1741:
1742: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1743: EXCEPTION
1744: WHEN OTHERS THEN
1745: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1746: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1741:
1742: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1743: EXCEPTION
1744: WHEN OTHERS THEN
1745: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1746: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1747: END manage_copy_cpa;
1748:
1749: --------------------------------------------------------------------------------
1794: l_remaining_val_intf_hdr_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1795: l_remaining_val_vndr_site_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1796: BEGIN
1797: l_progress := '010';
1798: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1799:
1800: l_progress := '020';
1801: -- Find the number of valid headers so that we can initialize the size of
1802: -- the arrays.
1808: l_num_valid_headers := l_num_valid_headers + 1;
1809: END IF;
1810: END LOOP;
1811:
1812: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_num_valid_headers='||l_num_valid_headers); END IF;
1813:
1814: l_progress := '030';
1815: -- Get the valid rows into l_valid_headers array.
1816: l_count := 0;
1825: SELECT PO_HEADERS_S.nextval
1826: INTO l_valid_headers.po_header_id(l_count)
1827: FROM dual;
1828:
1829: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'From sequence, next po_header_id='||l_valid_headers.po_header_id(l_count)); END IF;
1830:
1831: l_progress := '060';
1832: l_valid_headers.interface_header_id(l_count) := x_doc_headers_rec.interface_header_id(i);
1833: l_progress := '061';
2314: --NULL, -- supplier_auth_enabled_flag -- Not present in 11.5.9, 11.5.10
2315: );
2316:
2317: l_progress := '080';
2318: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of txn headers inserted='||SQL%rowcount); END IF;
2319:
2320:
2321: manage_copy_cpa
2322: (
2330: , x_remaining_val_intf_hdr_ids => l_remaining_val_intf_hdr_ids
2331: , x_remaining_val_vndr_site_ids => l_remaining_val_vndr_site_ids
2332: );
2333:
2334: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'After manage_copy_cpa(), l_remaining_val_po_hdr_ids.COUNT='||l_remaining_val_po_hdr_ids.COUNT); END IF;
2335:
2336: -- SQL What: For each GBPA Header inserted above, insert the default
2337: -- Org Assignments Row.
2338: -- SQL Why : To migrate data to txn tables
2371: sysdate,
2372: g_R12_UPGRADE_USER);
2373:
2374: l_progress := '170';
2375: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of txn Org Assignments inserted='||SQL%rowcount); END IF;
2376:
2377: l_progress := '180';
2378: -- SQL What: Insert the PO_HEADER_ID back into Interface table for
2379: -- successfull header creation
2385: PROCESS_CODE = g_PROCESS_CODE_PROCESSED
2386: WHERE interface_header_id = l_remaining_val_intf_hdr_ids(i);
2387:
2388: l_progress := '190';
2389: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface headers records updated with po_header_id='||SQL%rowcount); END IF;
2390:
2391: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2392: EXCEPTION
2393: WHEN OTHERS THEN
2387:
2388: l_progress := '190';
2389: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface headers records updated with po_header_id='||SQL%rowcount); END IF;
2390:
2391: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2392: EXCEPTION
2393: WHEN OTHERS THEN
2394: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2395: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2390:
2391: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2392: EXCEPTION
2393: WHEN OTHERS THEN
2394: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2395: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2396: END insert_doc_headers;
2397:
2398: --------------------------------------------------------------------------------
2430: l_progress VARCHAR2(3) := '000';
2431:
2432: BEGIN
2433: l_progress := '010';
2434: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2435:
2436: -- Update Rows that do not have errors, and action = 'UPDATE'
2437:
2438: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id.COUNT='||p_doc_headers_rec.po_header_id.COUNT); END IF;
2434: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2435:
2436: -- Update Rows that do not have errors, and action = 'UPDATE'
2437:
2438: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id.COUNT='||p_doc_headers_rec.po_header_id.COUNT); END IF;
2439:
2440: IF (p_doc_headers_rec.po_header_id.COUNT > 0) THEN
2441: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id(1)='||p_doc_headers_rec.po_header_id(1)); END IF;
2442: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.has_errors(1)='||p_doc_headers_rec.has_errors(1)); END IF;
2437:
2438: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id.COUNT='||p_doc_headers_rec.po_header_id.COUNT); END IF;
2439:
2440: IF (p_doc_headers_rec.po_header_id.COUNT > 0) THEN
2441: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id(1)='||p_doc_headers_rec.po_header_id(1)); END IF;
2442: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.has_errors(1)='||p_doc_headers_rec.has_errors(1)); END IF;
2443: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.action(1)='||p_doc_headers_rec.action(1)); END IF;
2444: END IF;
2445:
2438: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id.COUNT='||p_doc_headers_rec.po_header_id.COUNT); END IF;
2439:
2440: IF (p_doc_headers_rec.po_header_id.COUNT > 0) THEN
2441: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id(1)='||p_doc_headers_rec.po_header_id(1)); END IF;
2442: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.has_errors(1)='||p_doc_headers_rec.has_errors(1)); END IF;
2443: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.action(1)='||p_doc_headers_rec.action(1)); END IF;
2444: END IF;
2445:
2446: -- SQL What: Update all the headers that were created by the Catalog Upgrade.
2439:
2440: IF (p_doc_headers_rec.po_header_id.COUNT > 0) THEN
2441: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.po_header_id(1)='||p_doc_headers_rec.po_header_id(1)); END IF;
2442: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.has_errors(1)='||p_doc_headers_rec.has_errors(1)); END IF;
2443: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_headers_rec.action(1)='||p_doc_headers_rec.action(1)); END IF;
2444: END IF;
2445:
2446: -- SQL What: Update all the headers that were created by the Catalog Upgrade.
2447: -- For these, only the CPA_REFERENCE is allowed to be updated. The
2458: WHERE po_header_id = p_doc_headers_rec.po_header_id(i)
2459: AND p_doc_headers_rec.has_errors(i) = 'N'
2460: AND p_doc_headers_rec.action(i) = 'UPDATE';
2461:
2462: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of headers updated with CPA_REFERENCE='||SQL%rowcount); END IF;
2463:
2464: l_progress := '020';
2465: -- Update the Headers Interface Table for process_code as 'PROCESSED'
2466: -- SQL What: Update the Headers Interface Table for process_code as 'PROCESSED'
2473: WHERE interface_header_id = p_doc_headers_rec.interface_header_id(i)
2474: AND p_doc_headers_rec.has_errors(i) = 'N'
2475: AND p_doc_headers_rec.action(i) = 'UPDATE';
2476:
2477: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface headers PROCESSED='||SQL%rowcount); END IF;
2478:
2479:
2480: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2481: EXCEPTION
2476:
2477: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface headers PROCESSED='||SQL%rowcount); END IF;
2478:
2479:
2480: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2481: EXCEPTION
2482: WHEN OTHERS THEN
2483: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2484: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2479:
2480: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2481: EXCEPTION
2482: WHEN OTHERS THEN
2483: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2484: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2485: END update_doc_headers;
2486:
2487: --------------------------------------------------------------------------------
2523: l_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2524: l_po_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2525: BEGIN
2526: l_progress := '010';
2527: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2528:
2529: -- Delete Rows that do not have errors and action = 'DELETE',
2530: FORALL i IN 1.. p_doc_headers_rec.po_header_id.COUNT
2531: DELETE FROM po_headers_all
2534: AND p_doc_headers_rec.action(i) = 'DELETE'
2535: RETURNING po_header_id
2536: BULK COLLECT INTO l_po_header_ids;
2537:
2538: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of headers deleted='||SQL%rowcount); END IF;
2539:
2540: l_progress := '020';
2541:
2542: -- For each of the above GBPA Header, delete the default Org Assignments Row
2543: FORALL i IN 1.. l_po_header_ids.COUNT
2544: DELETE FROM PO_GA_ORG_ASSIGNMENTS
2545: WHERE po_header_id = l_po_header_ids(i);
2546:
2547: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Org Assignments deleted='||SQL%rowcount); END IF;
2548:
2549: l_progress := '030';
2550:
2551: -- Delete from PO Lines
2554: WHERE po_header_id = l_po_header_ids(i)
2555: RETURNING po_line_id
2556: BULK COLLECT INTO l_po_line_ids;
2557:
2558: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines deleted='||SQL%rowcount); END IF;
2559:
2560: l_progress := '040';
2561:
2562: -- Delete from Attribute tables
2563: FORALL i IN 1.. l_po_line_ids.COUNT
2564: DELETE FROM PO_ATTRIBUTE_VALUES
2565: WHERE PO_LINE_ID = l_po_line_ids(i);
2566:
2567: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Attr deleted='||SQL%rowcount); END IF;
2568:
2569: l_progress := '050';
2570:
2571: -- Delete from Attribute TLP tables
2572: FORALL i IN 1.. l_po_line_ids.COUNT
2573: DELETE FROM PO_ATTRIBUTE_VALUES_TLP
2574: WHERE PO_LINE_ID = l_po_line_ids(i);
2575:
2576: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of TLP deleted='||SQL%rowcount); END IF;
2577:
2578: l_progress := '100';
2579: -- Mark headers interface as PROCESSED
2580: FORALL i IN 1.. p_doc_headers_rec.po_header_id.COUNT
2584: AND p_doc_headers_rec.has_errors(i) = 'N'
2585: AND p_doc_headers_rec.action(i) = 'DELETE';
2586:
2587: l_progress := '110';
2588: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Headers Interface records PROCESSED='||SQL%rowcount); END IF;
2589:
2590: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2591: EXCEPTION
2592: WHEN OTHERS THEN
2586:
2587: l_progress := '110';
2588: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Headers Interface records PROCESSED='||SQL%rowcount); END IF;
2589:
2590: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2591: EXCEPTION
2592: WHEN OTHERS THEN
2593: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2594: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2589:
2590: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2591: EXCEPTION
2592: WHEN OTHERS THEN
2593: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2594: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2595: END delete_doc_headers;
2596:
2597: --------------------------------------------------------------------------------
2847: l_err_interface_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2848: l_count NUMBER := NULL;
2849: BEGIN
2850: l_progress := '010';
2851: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2852:
2853: -- Algorithm:
2854: -- 1. Load Lines batch (batch_size) into pl/sql table.
2855: -- 2. Call PDOI modules to process data in batches (default, derive, validate).
3073: LIMIT g_job.batch_size;
3074:
3075: l_progress := '030';
3076:
3077: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_doc_lines_table.interface_line_id.COUNT='||l_doc_lines_table.interface_line_id.COUNT); END IF;
3078:
3079: EXIT WHEN l_doc_lines_table.interface_line_id.COUNT = 0;
3080:
3081: l_progress := '040';
3132: END IF;
3133: l_progress := '120';
3134: EXCEPTION
3135: WHEN g_SNAPSHOT_TOO_OLD THEN
3136: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_lines_csr'); END IF;
3137:
3138: -- Commit and re-open the cursor
3139: l_progress := '130';
3140: COMMIT;
3152: IF (load_lines_csr%ISOPEN) THEN
3153: CLOSE load_lines_csr;
3154: END IF;
3155:
3156: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3157: EXCEPTION
3158: WHEN OTHERS THEN
3159: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3160: IF (load_lines_csr%ISOPEN) THEN
3155:
3156: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3157: EXCEPTION
3158: WHEN OTHERS THEN
3159: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3160: IF (load_lines_csr%ISOPEN) THEN
3161: CLOSE load_lines_csr;
3162: END IF;
3163: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3198: l_progress VARCHAR2(3) := '000';
3199:
3200: BEGIN
3201: l_progress := '010';
3202: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3203:
3204: -- Insert Lines
3205: insert_doc_lines(p_doc_lines_rec => p_doc_lines_rec);
3206:
3211: l_progress := '030';
3212: -- Delete Lines
3213: delete_doc_lines(p_doc_lines_rec => p_doc_lines_rec);
3214:
3215: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3216: EXCEPTION
3217: WHEN OTHERS THEN
3218: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3219: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3214:
3215: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3216: EXCEPTION
3217: WHEN OTHERS THEN
3218: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3219: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3220: END transfer_doc_lines;
3221:
3222: --------------------------------------------------------------------------------
3261: l_process_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
3262: l_index NUMBER;
3263: BEGIN
3264: l_progress := '010';
3265: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3266:
3267: IF (p_doc_lines_rec.interface_line_id IS NULL OR
3268: p_doc_lines_rec.interface_line_id.COUNT = 0) THEN
3269: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Early return because there are no records to process.'); END IF;
3265: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3266:
3267: IF (p_doc_lines_rec.interface_line_id IS NULL OR
3268: p_doc_lines_rec.interface_line_id.COUNT = 0) THEN
3269: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Early return because there are no records to process.'); END IF;
3270: RETURN;
3271: END IF;
3272:
3273: -- pick a new key from temp table which will be used in all validate logic
3289: POHI.process_code
3290: FROM PO_HEADERS_INTERFACE POHI
3291: WHERE interface_header_id = p_doc_lines_rec.interface_header_id(i);
3292:
3293: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3294:
3295: l_progress := '040';
3296: -- SQL What: Transfer from session GT table to local arrays
3297: -- SQL Why : It will be used to get the process_codes
3300: WHERE key = l_key
3301: RETURNING num1, char1
3302: BULK COLLECT INTO l_indexes, l_process_code_list;
3303:
3304: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3305:
3306: -- Rearrange the indexes properly
3307: l_progress := '050';
3308: FOR i IN 1 .. l_indexes.COUNT
3317: l_index := l_indexes(i);
3318: x_process_code_list(l_index) := l_process_code_list(i);
3319: END LOOP;
3320:
3321: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_process_code_list(1)='||x_process_code_list(1)); END IF;
3322:
3323: l_progress := '070';
3324: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3325: EXCEPTION
3320:
3321: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_process_code_list(1)='||x_process_code_list(1)); END IF;
3322:
3323: l_progress := '070';
3324: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3325: EXCEPTION
3326: WHEN OTHERS THEN
3327: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3328: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3323: l_progress := '070';
3324: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3325: EXCEPTION
3326: WHEN OTHERS THEN
3327: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3328: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3329: END get_hdr_process_code_list;
3330:
3331: --------------------------------------------------------------------------------
3370: i NUMBER;
3371: l_process_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
3372: BEGIN
3373: l_progress := '010';
3374: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3375:
3376: get_hdr_process_code_list
3377: (
3378: p_doc_lines_rec => p_doc_lines_rec
3405: -- the tables and assign max(line_num)+1 to l_max_line_num of that Header.
3406: -- If an entry already exists, then increment max_line_num.
3407: IF (l_max_po_line_num.exists(p_doc_lines_rec.po_header_id(i))) THEN
3408: l_progress := '060';
3409: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_max_po_line_num(p_doc_lines_rec.po_header_id(i))='||l_max_po_line_num(p_doc_lines_rec.po_header_id(i))); END IF;
3410:
3411: l_max_po_line_num(p_doc_lines_rec.po_header_id(i)) :=
3412: l_max_po_line_num(p_doc_lines_rec.po_header_id(i)) + 1;
3413: ELSE
3411: l_max_po_line_num(p_doc_lines_rec.po_header_id(i)) :=
3412: l_max_po_line_num(p_doc_lines_rec.po_header_id(i)) + 1;
3413: ELSE
3414: l_progress := '070';
3415: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'max_line_num for po_header_id['||p_doc_lines_rec.po_header_id(i)||'] does not exist'); END IF;
3416:
3417: SELECT (NVL(max(line_num), 0) + 1)
3418: INTO l_max_po_line_num(p_doc_lines_rec.po_header_id(i))
3419: FROM PO_LINES_ALL
3418: INTO l_max_po_line_num(p_doc_lines_rec.po_header_id(i))
3419: FROM PO_LINES_ALL
3420: WHERE po_header_id = p_doc_lines_rec.po_header_id(i);
3421:
3422: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Queried max_line_num for po_header_id['||p_doc_lines_rec.po_header_id(i)||'] from tables='||l_max_po_line_num(p_doc_lines_rec.po_header_id(i))); END IF;
3423: END IF;
3424:
3425: l_progress := '080';
3426: -- Finally, copy the value of l_max_po_line_num into the structure
3633: END IF;
3634: END LOOP;
3635:
3636: l_progress := '090';
3637: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_count='||l_count); END IF;
3638:
3639: -- SQL What: Insert rows that have no errors, and action = PO_R12_CAT_UPG_PVT.g_action_line_create
3640: -- SQL Why : To migrate data to txn tables
3641: -- SQL Join: none
3936: --NULL, -- advance_amount, Not present in 11.5.9, 11.5.10
3937: );
3938:
3939: l_progress := '100';
3940: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines inserted='||SQL%rowcount); END IF;
3941:
3942: l_progress := '110';
3943: -- SQL What: Insert the PO_LINE_ID back into Interface table for successfull
3944: -- line creation.
3950: PROCESS_CODE = g_PROCESS_CODE_PROCESSED
3951: WHERE interface_line_id = l_valid_lines.interface_line_id(i);
3952:
3953: l_progress := '120';
3954: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface_line recs updated='||SQL%rowcount); END IF;
3955:
3956: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3957: EXCEPTION
3958: WHEN OTHERS THEN
3952:
3953: l_progress := '120';
3954: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface_line recs updated='||SQL%rowcount); END IF;
3955:
3956: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3957: EXCEPTION
3958: WHEN OTHERS THEN
3959: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3960: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3955:
3956: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3957: EXCEPTION
3958: WHEN OTHERS THEN
3959: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3960: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3961: END insert_doc_lines;
3962:
3963: --------------------------------------------------------------------------------
3995: l_progress VARCHAR2(3) := '000';
3996: l_po_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER; --Bug#4731494
3997: BEGIN
3998: l_progress := '010';
3999: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4000:
4001: IF (p_doc_lines_rec.po_line_id.COUNT > 0) THEN
4002: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.po_line_id(1)='||p_doc_lines_rec.po_line_id(1)); END IF;
4003: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.has_errors(1)='||p_doc_lines_rec.has_errors(1)); END IF;
3998: l_progress := '010';
3999: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4000:
4001: IF (p_doc_lines_rec.po_line_id.COUNT > 0) THEN
4002: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.po_line_id(1)='||p_doc_lines_rec.po_line_id(1)); END IF;
4003: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.has_errors(1)='||p_doc_lines_rec.has_errors(1)); END IF;
4004: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.action(1)='||p_doc_lines_rec.action(1)); END IF;
4005: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.ip_category_id(1)='||p_doc_lines_rec.ip_category_id(1)); END IF;
4006: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.item_description(1)='||p_doc_lines_rec.item_description(1)); END IF;
3999: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4000:
4001: IF (p_doc_lines_rec.po_line_id.COUNT > 0) THEN
4002: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.po_line_id(1)='||p_doc_lines_rec.po_line_id(1)); END IF;
4003: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.has_errors(1)='||p_doc_lines_rec.has_errors(1)); END IF;
4004: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.action(1)='||p_doc_lines_rec.action(1)); END IF;
4005: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.ip_category_id(1)='||p_doc_lines_rec.ip_category_id(1)); END IF;
4006: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.item_description(1)='||p_doc_lines_rec.item_description(1)); END IF;
4007: END IF;
4000:
4001: IF (p_doc_lines_rec.po_line_id.COUNT > 0) THEN
4002: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.po_line_id(1)='||p_doc_lines_rec.po_line_id(1)); END IF;
4003: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.has_errors(1)='||p_doc_lines_rec.has_errors(1)); END IF;
4004: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.action(1)='||p_doc_lines_rec.action(1)); END IF;
4005: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.ip_category_id(1)='||p_doc_lines_rec.ip_category_id(1)); END IF;
4006: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.item_description(1)='||p_doc_lines_rec.item_description(1)); END IF;
4007: END IF;
4008:
4001: IF (p_doc_lines_rec.po_line_id.COUNT > 0) THEN
4002: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.po_line_id(1)='||p_doc_lines_rec.po_line_id(1)); END IF;
4003: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.has_errors(1)='||p_doc_lines_rec.has_errors(1)); END IF;
4004: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.action(1)='||p_doc_lines_rec.action(1)); END IF;
4005: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.ip_category_id(1)='||p_doc_lines_rec.ip_category_id(1)); END IF;
4006: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.item_description(1)='||p_doc_lines_rec.item_description(1)); END IF;
4007: END IF;
4008:
4009: l_progress := '020';
4002: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.po_line_id(1)='||p_doc_lines_rec.po_line_id(1)); END IF;
4003: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.has_errors(1)='||p_doc_lines_rec.has_errors(1)); END IF;
4004: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.action(1)='||p_doc_lines_rec.action(1)); END IF;
4005: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.ip_category_id(1)='||p_doc_lines_rec.ip_category_id(1)); END IF;
4006: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_doc_lines_rec.item_description(1)='||p_doc_lines_rec.item_description(1)); END IF;
4007: END IF;
4008:
4009: l_progress := '020';
4010: -- SQL What: Update all the lines that were NOT created by the Catalog Upgrade
4034: AND p_doc_lines_rec.has_errors(i) = 'N'
4035: AND p_doc_lines_rec.action(i) = 'UPDATE'
4036: AND created_by <> g_R12_UPGRADE_USER; --Bug#4865568
4037:
4038: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines updated for exiting docs='||SQL%rowcount); END IF;
4039:
4040: l_progress := '030';
4041: -- SQL What: Update all the lines that were created by the Catalog Upgrade
4042: -- and don't have any errors.
4089: AND p_doc_lines_rec.action(i) = 'UPDATE'
4090: AND created_by = g_R12_UPGRADE_USER --Bug#4865568
4091: RETURNING po_line_id BULK COLLECT into l_po_line_ids; --Bug#4731494
4092:
4093: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines updated for docs created by migration program='||SQL%rowcount); END IF;
4094:
4095: l_progress := '035';
4096:
4097: -- Bug#4731494
4117: WHERE interface_line_id = p_doc_lines_rec.interface_line_id(i)
4118: AND p_doc_lines_rec.has_errors(i) = 'N'
4119: AND p_doc_lines_rec.action(i) = 'UPDATE';
4120:
4121: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface lines PROCESSED='||SQL%rowcount); END IF;
4122:
4123: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4124: EXCEPTION
4125: WHEN OTHERS THEN
4119: AND p_doc_lines_rec.action(i) = 'UPDATE';
4120:
4121: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface lines PROCESSED='||SQL%rowcount); END IF;
4122:
4123: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4124: EXCEPTION
4125: WHEN OTHERS THEN
4126: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4127: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4122:
4123: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4124: EXCEPTION
4125: WHEN OTHERS THEN
4126: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4127: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4128: END update_doc_lines;
4129:
4130: --------------------------------------------------------------------------------
4163: l_po_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4164:
4165: BEGIN
4166: l_progress := '010';
4167: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4168:
4169: -- Delete Rows that do not have errors
4170: FORALL i IN 1..p_doc_lines_rec.po_line_id.COUNT
4171: DELETE FROM po_lines_all
4174: AND p_doc_lines_rec.action(i) = 'DELETE'
4175: RETURNING po_line_id
4176: BULK COLLECT INTO l_po_line_ids;
4177:
4178: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines deleted='||SQL%rowcount); END IF;
4179:
4180: l_progress := '020';
4181:
4182: -- Delete from Attribute tables
4183: FORALL i IN 1.. l_po_line_ids.COUNT
4184: DELETE FROM PO_ATTRIBUTE_VALUES
4185: WHERE PO_LINE_ID = l_po_line_ids(i);
4186:
4187: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of attr deleted='||SQL%rowcount); END IF;
4188:
4189: l_progress := '030';
4190:
4191: -- Delete from Attribute TLP tables
4192: FORALL i IN 1.. l_po_line_ids.COUNT
4193: DELETE FROM PO_ATTRIBUTE_VALUES_TLP
4194: WHERE PO_LINE_ID = l_po_line_ids(i);
4195:
4196: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of TLP deleted='||SQL%rowcount); END IF;
4197:
4198: l_progress := '040';
4199: -- Mark interface lines as PROCESSED
4200: FORALL i IN 1..p_doc_lines_rec.po_line_id.COUNT
4203: WHERE interface_line_id = p_doc_lines_rec.interface_line_id(i)
4204: AND p_doc_lines_rec.has_errors(i) = 'N'
4205: AND p_doc_lines_rec.action(i) = 'DELETE';
4206:
4207: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines PROCESSED='||SQL%rowcount); END IF;
4208:
4209: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4210: EXCEPTION
4211: WHEN OTHERS THEN
4205: AND p_doc_lines_rec.action(i) = 'DELETE';
4206:
4207: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of lines PROCESSED='||SQL%rowcount); END IF;
4208:
4209: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4210: EXCEPTION
4211: WHEN OTHERS THEN
4212: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4213: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4208:
4209: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4210: EXCEPTION
4211: WHEN OTHERS THEN
4212: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4213: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4214: END delete_doc_lines;
4215:
4216: --------------------------------------------------------------------------------
4597: l_attrib_table record_of_attr_values_type;
4598:
4599: BEGIN
4600: l_progress := '010';
4601: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4602:
4603: -- Algorithm:
4604: -- 1. Load Lines batch (batch_size) into pl/sql table.
4605: -- 2. Call PDOI modules to process data in batches (default, derive, validate).
4946: LIMIT g_job.batch_size;
4947:
4948: l_progress := '030';
4949:
4950: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_attrib_table.interface_line_id.COUNT='||l_attrib_table.interface_line_id.COUNT); END IF;
4951:
4952: IF (l_attrib_table.interface_line_id.COUNT > 0) THEN
4953: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_attrib_table.interface_attr_values_id(1)='||l_attrib_table.interface_attr_values_id(1)); END IF;
4954: END IF;
4949:
4950: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_attrib_table.interface_line_id.COUNT='||l_attrib_table.interface_line_id.COUNT); END IF;
4951:
4952: IF (l_attrib_table.interface_line_id.COUNT > 0) THEN
4953: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_attrib_table.interface_attr_values_id(1)='||l_attrib_table.interface_attr_values_id(1)); END IF;
4954: END IF;
4955:
4956: EXIT WHEN l_attrib_table.interface_line_id.COUNT = 0;
4957:
4973: END IF;
4974: l_progress := '120';
4975: EXCEPTION
4976: WHEN g_SNAPSHOT_TOO_OLD THEN
4977: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_attrib_csr'); END IF;
4978:
4979: -- Commit and re-open the cursor
4980: l_progress := '130';
4981: COMMIT;
4993: IF (load_attrib_csr%ISOPEN) THEN
4994: CLOSE load_attrib_csr;
4995: END IF;
4996:
4997: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4998: EXCEPTION
4999: WHEN OTHERS THEN
5000: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5001: IF (load_attrib_csr%ISOPEN) THEN
4996:
4997: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4998: EXCEPTION
4999: WHEN OTHERS THEN
5000: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5001: IF (load_attrib_csr%ISOPEN) THEN
5002: CLOSE load_attrib_csr;
5003: END IF;
5004: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5038: l_progress VARCHAR2(3) := '000';
5039:
5040: BEGIN
5041: l_progress := '010';
5042: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
5043:
5044: -- Insert Attributes
5045: insert_attributes(p_attr_values_tbl => p_attrib_values_tbl);
5046:
5053: -- This procedure is not required anymore. Deletion of the default attribute row is
5054: -- not allowed. Only update is allowed. In the pre-process validations, the rows
5055: -- that specified the DELETE action are already filtered.
5056: --delete_attributes(p_attr_values_tbl => p_attrib_values_tbl);
5057: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5058: EXCEPTION
5059: WHEN OTHERS THEN
5060: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5061: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5056: --delete_attributes(p_attr_values_tbl => p_attrib_values_tbl);
5057: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5058: EXCEPTION
5059: WHEN OTHERS THEN
5060: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5061: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5062: END transfer_attributes;
5063:
5064: --------------------------------------------------------------------------------
5094: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
5095: l_progress VARCHAR2(3) := '000';
5096: BEGIN
5097: l_progress := '010';
5098: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
5099:
5100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.po_line_id.COUNT='||p_attr_values_tbl.po_line_id.COUNT); END IF;
5101:
5102: IF (p_attr_values_tbl.po_line_id.COUNT > 0) THEN
5096: BEGIN
5097: l_progress := '010';
5098: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
5099:
5100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.po_line_id.COUNT='||p_attr_values_tbl.po_line_id.COUNT); END IF;
5101:
5102: IF (p_attr_values_tbl.po_line_id.COUNT > 0) THEN
5103: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.has_errors(1)='||p_attr_values_tbl.has_errors(1)); END IF;
5104: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.action(1)='||p_attr_values_tbl.action(1)); END IF;
5099:
5100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.po_line_id.COUNT='||p_attr_values_tbl.po_line_id.COUNT); END IF;
5101:
5102: IF (p_attr_values_tbl.po_line_id.COUNT > 0) THEN
5103: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.has_errors(1)='||p_attr_values_tbl.has_errors(1)); END IF;
5104: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.action(1)='||p_attr_values_tbl.action(1)); END IF;
5105: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.interface_attr_values_id(1)='||p_attr_values_tbl.interface_attr_values_id(1)); END IF;
5106: END IF;
5107:
5100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.po_line_id.COUNT='||p_attr_values_tbl.po_line_id.COUNT); END IF;
5101:
5102: IF (p_attr_values_tbl.po_line_id.COUNT > 0) THEN
5103: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.has_errors(1)='||p_attr_values_tbl.has_errors(1)); END IF;
5104: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.action(1)='||p_attr_values_tbl.action(1)); END IF;
5105: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.interface_attr_values_id(1)='||p_attr_values_tbl.interface_attr_values_id(1)); END IF;
5106: END IF;
5107:
5108: -- SQL What: Insert rows that have no errors, and action = PO_R12_CAT_UPG_PVT.g_action_attr_create
5101:
5102: IF (p_attr_values_tbl.po_line_id.COUNT > 0) THEN
5103: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.has_errors(1)='||p_attr_values_tbl.has_errors(1)); END IF;
5104: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.action(1)='||p_attr_values_tbl.action(1)); END IF;
5105: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_attr_values_tbl.interface_attr_values_id(1)='||p_attr_values_tbl.interface_attr_values_id(1)); END IF;
5106: END IF;
5107:
5108: -- SQL What: Insert rows that have no errors, and action = PO_R12_CAT_UPG_PVT.g_action_attr_create
5109: -- SQL Why : To migrate data to txn tables
5775: AND p_attr_values_tbl.action(i) = PO_R12_CAT_UPG_PVT.g_action_attr_create
5776: -- ECO bug 4738058
5777: AND POLI.interface_line_id = POAVI.interface_line_id;
5778:
5779: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Attr inserted='||SQL%rowcount); END IF;
5780:
5781: l_progress := '020';
5782: -- SQL What: Update the process_code on interface table as PROCESSED
5783: -- SQL Why : To mark the rows as successfully inserted
5794: WHERE POAVI.interface_attr_values_id = p_attr_values_tbl.interface_attr_values_id(i)
5795: AND p_attr_values_tbl.has_errors(i) = 'N'
5796: AND p_attr_values_tbl.action(i) = PO_R12_CAT_UPG_PVT.g_action_attr_create;
5797:
5798: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface Attr PROCESSED='||SQL%rowcount); END IF;
5799:
5800: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5801: EXCEPTION
5802: WHEN OTHERS THEN
5796: AND p_attr_values_tbl.action(i) = PO_R12_CAT_UPG_PVT.g_action_attr_create;
5797:
5798: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface Attr PROCESSED='||SQL%rowcount); END IF;
5799:
5800: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5801: EXCEPTION
5802: WHEN OTHERS THEN
5803: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5804: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5799:
5800: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5801: EXCEPTION
5802: WHEN OTHERS THEN
5803: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5804: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5805: END insert_attributes;
5806:
5807: --------------------------------------------------------------------------------
5837: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
5838: l_progress VARCHAR2(3) := '000';
5839: BEGIN
5840: l_progress := '010';
5841: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
5842:
5843: -- SQL What: Update Rows that do not have errors. Overwrite all values
5844: -- from interface table to transaction table
5845: -- SQL Why : To update the po_attribute_values columns
6178: AND req_template_name = p_attr_values_tbl.req_template_name (i)
6179: AND req_template_line_num = p_attr_values_tbl.req_template_line_num(i)
6180: AND org_id = p_attr_values_tbl.org_id(i);
6181:
6182: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Attr updated='||SQL%rowcount); END IF;
6183:
6184: l_progress := '020';
6185: -- SQL What: Update the process_code on interface table as PROCESSED
6186: -- SQL Why : To mark the rows as successfully inserted
6200: AND req_template_name = p_attr_values_tbl.req_template_name (i)
6201: AND req_template_line_num = p_attr_values_tbl.req_template_line_num(i)
6202: AND org_id = p_attr_values_tbl.org_id(i);
6203:
6204: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface Attr PROCESSED='||SQL%rowcount); END IF;
6205:
6206: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6207: EXCEPTION
6208: WHEN OTHERS THEN
6202: AND org_id = p_attr_values_tbl.org_id(i);
6203:
6204: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface Attr PROCESSED='||SQL%rowcount); END IF;
6205:
6206: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6207: EXCEPTION
6208: WHEN OTHERS THEN
6209: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6210: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6205:
6206: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6207: EXCEPTION
6208: WHEN OTHERS THEN
6209: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6210: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6211: END update_attributes;
6212:
6213: --------------------------------------------------------------------------------
6244: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
6245: l_progress VARCHAR2(3) := '000';
6246: BEGIN
6247: l_progress := '010';
6248: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
6249:
6250: -- Delete Rows that do not have errors
6251: FORALL i IN 1..p_attr_values_tbl.po_line_id.COUNT
6252: DELETE FROM po_attribute_values
6259: AND req_template_line_num = p_attr_values_tbl.req_template_line_num(i)
6260: AND org_id = p_attr_values_tbl.org_id(i)
6261: AND p_attr_values_tbl.req_template_line_num(i) <> g_NOT_REQUIRED_ID));
6262:
6263: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Attr deleted='||SQL%rowcount); END IF;
6264:
6265: l_progress := '020';
6266: -- Mark interface attr rows as PROCESSED
6267: -- Bug 5677911: Added the hint for performance reason.
6278: AND req_template_line_num = p_attr_values_tbl.req_template_line_num(i)
6279: AND org_id = p_attr_values_tbl.org_id(i)
6280: AND p_attr_values_tbl.req_template_line_num(i) <> g_NOT_REQUIRED_ID));
6281:
6282: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface Attr rows PROCESSED='||SQL%rowcount); END IF;
6283:
6284: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6285: EXCEPTION
6286: WHEN OTHERS THEN
6280: AND p_attr_values_tbl.req_template_line_num(i) <> g_NOT_REQUIRED_ID));
6281:
6282: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface Attr rows PROCESSED='||SQL%rowcount); END IF;
6283:
6284: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6285: EXCEPTION
6286: WHEN OTHERS THEN
6287: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6288: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6283:
6284: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6285: EXCEPTION
6286: WHEN OTHERS THEN
6287: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6288: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6289: END delete_attributes;
6290:
6291: --------------------------------------------------------------------------------
6520: l_attrib_tlp_table record_of_attr_values_tlp_type;
6521:
6522: BEGIN
6523: l_progress := '010';
6524: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
6525:
6526: -- Algorithm:
6527: -- 1. Load Lines batch (batch_size) into pl/sql table.
6528: -- 2. Call PDOI modules to process data in batches (default, derive, validate).
6717: LIMIT g_job.batch_size;
6718:
6719: l_progress := '030';
6720:
6721: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_attrib_tlp_table.interface_line_id.COUNT='||l_attrib_tlp_table.interface_line_id.COUNT); END IF;
6722:
6723: EXIT WHEN l_attrib_tlp_table.interface_line_id.COUNT = 0;
6724:
6725: -- Derive + Default + Validation are not required for attribute tlp values
6741: END IF;
6742: l_progress := '120';
6743: EXCEPTION
6744: WHEN g_SNAPSHOT_TOO_OLD THEN
6745: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_attrib_tlp_csr'); END IF;
6746:
6747: -- Commit and re-open the cursor
6748: l_progress := '130';
6749: COMMIT;
6761: IF (load_attrib_tlp_csr%ISOPEN) THEN
6762: CLOSE load_attrib_tlp_csr;
6763: END IF;
6764:
6765: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6766: EXCEPTION
6767: WHEN OTHERS THEN
6768: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6769: IF (load_attrib_tlp_csr%ISOPEN) THEN
6764:
6765: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6766: EXCEPTION
6767: WHEN OTHERS THEN
6768: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6769: IF (load_attrib_tlp_csr%ISOPEN) THEN
6770: CLOSE load_attrib_tlp_csr;
6771: END IF;
6772: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6806: l_progress VARCHAR2(3) := '000';
6807:
6808: BEGIN
6809: l_progress := '010';
6810: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
6811:
6812: -- Insert Attributes TLP
6813: insert_attributes_tlp(p_attr_values_tlp_tbl => p_attrib_tlp_values_tbl);
6814:
6819: l_progress := '030';
6820: -- Delete Attributes TLP
6821: delete_attributes_tlp(p_attr_values_tlp_tbl => p_attrib_tlp_values_tbl);
6822:
6823: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6824: EXCEPTION
6825: WHEN OTHERS THEN
6826: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6827: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6822:
6823: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
6824: EXCEPTION
6825: WHEN OTHERS THEN
6826: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
6827: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
6828: END transfer_attributes_tlp;
6829:
6830: --------------------------------------------------------------------------------
6860: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
6861: l_progress VARCHAR2(3) := '000';
6862: BEGIN
6863: l_progress := '010';
6864: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
6865:
6866: -- SQL What: Insert rows that have no errors, and action = PO_R12_CAT_UPG_PVT.g_action_tlp_create
6867: -- SQL Why : To migrate data to txn tables
6868: -- SQL Join: none
7228: AND p_attr_values_tlp_tbl.action(i) = PO_R12_CAT_UPG_PVT.g_action_tlp_create
7229: -- ECO bug 4738058
7230: AND POLI.interface_line_id = POAVTI.interface_line_id;
7231:
7232: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of TLP inserted='||SQL%rowcount); END IF;
7233:
7234: l_progress := '020';
7235: -- SQL What: Update the process_code in interface table as PROCESSED
7236: -- SQL Why : To mark the rows as successfully inserted
7247: WHERE POAVTI.interface_attr_values_tlp_id = p_attr_values_tlp_tbl.interface_attr_values_tlp_id(i)
7248: AND p_attr_values_tlp_tbl.has_errors(i) = 'N'
7249: AND p_attr_values_tlp_tbl.action(i) = PO_R12_CAT_UPG_PVT.g_action_tlp_create;
7250:
7251: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface TLP rows PROCESSED='||SQL%rowcount); END IF;
7252:
7253: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7254: EXCEPTION
7255: WHEN OTHERS THEN
7249: AND p_attr_values_tlp_tbl.action(i) = PO_R12_CAT_UPG_PVT.g_action_tlp_create;
7250:
7251: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface TLP rows PROCESSED='||SQL%rowcount); END IF;
7252:
7253: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7254: EXCEPTION
7255: WHEN OTHERS THEN
7256: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7257: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7252:
7253: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7254: EXCEPTION
7255: WHEN OTHERS THEN
7256: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7257: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7258: END insert_attributes_tlp;
7259:
7260: --------------------------------------------------------------------------------
7290: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
7291: l_progress VARCHAR2(3) := '000';
7292: BEGIN
7293: l_progress := '010';
7294: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
7295:
7296: -- SQL What: Update Rows that do not have errors. Overwrite all values
7297: -- from interface table to transaction table
7298: -- SQL Why : To update the po_attribute_tlp_values columns
7482: AND req_template_name = p_attr_values_tlp_tbl.req_template_name (i)
7483: AND req_template_line_num = p_attr_values_tlp_tbl.req_template_line_num(i)
7484: AND org_id = p_attr_values_tlp_tbl.org_id(i);
7485:
7486: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of TLP updated='||SQL%rowcount); END IF;
7487:
7488: l_progress := '020';
7489: -- SQL What: Update the process_code in interface table as PROCESSED
7490: -- SQL Why : To mark the rows as successfully updated
7505: AND req_template_name = p_attr_values_tlp_tbl.req_template_name (i)
7506: AND req_template_line_num = p_attr_values_tlp_tbl.req_template_line_num(i)
7507: AND org_id = p_attr_values_tlp_tbl.org_id(i);
7508:
7509: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface TLP rows PROCESSED='||SQL%rowcount); END IF;
7510:
7511: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7512: EXCEPTION
7513: WHEN OTHERS THEN
7507: AND org_id = p_attr_values_tlp_tbl.org_id(i);
7508:
7509: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of interface TLP rows PROCESSED='||SQL%rowcount); END IF;
7510:
7511: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7512: EXCEPTION
7513: WHEN OTHERS THEN
7514: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7515: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7510:
7511: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7512: EXCEPTION
7513: WHEN OTHERS THEN
7514: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7515: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7516: END update_attributes_tlp;
7517:
7518: --------------------------------------------------------------------------------
7547: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
7548: l_progress VARCHAR2(3) := '000';
7549: BEGIN
7550: l_progress := '010';
7551: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
7552:
7553: -- Delete Rows that do not have errors
7554: FORALL i IN 1..p_attr_values_tlp_tbl.po_line_id.COUNT
7555: DELETE FROM po_attribute_values_tlp
7563: AND req_template_line_num = p_attr_values_tlp_tbl.req_template_line_num(i)
7564: AND org_id = p_attr_values_tlp_tbl.org_id(i)
7565: AND p_attr_values_tlp_tbl.req_template_line_num(i) <> g_NOT_REQUIRED_ID));
7566:
7567: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of TLP deleted='||SQL%rowcount); END IF;
7568:
7569: l_progress := '020';
7570: -- Delete Rows that do not have errors
7571: -- Bug 5677911: Added the hint for performance reason.
7583: AND req_template_line_num = p_attr_values_tlp_tbl.req_template_line_num(i)
7584: AND org_id = p_attr_values_tlp_tbl.org_id(i)
7585: AND p_attr_values_tlp_tbl.req_template_line_num(i) <> g_NOT_REQUIRED_ID));
7586:
7587: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface TLP rows PROCESSED='||SQL%rowcount); END IF;
7588:
7589: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7590: EXCEPTION
7591: WHEN OTHERS THEN
7585: AND p_attr_values_tlp_tbl.req_template_line_num(i) <> g_NOT_REQUIRED_ID));
7586:
7587: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of Interface TLP rows PROCESSED='||SQL%rowcount); END IF;
7588:
7589: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7590: EXCEPTION
7591: WHEN OTHERS THEN
7592: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7593: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7588:
7589: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7590: EXCEPTION
7591: WHEN OTHERS THEN
7592: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7593: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7594: END delete_attributes_tlp;
7595:
7596: --------------------------------------------------------------------------------
7677: l_rt_lines_table record_of_rt_lines_type;
7678:
7679: BEGIN
7680: l_progress := '010';
7681: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
7682:
7683: -- Initialize API return status to success
7684: x_return_status := FND_API.G_RET_STS_SUCCESS;
7685:
7708: LIMIT p_batch_size;
7709:
7710: l_progress := '030';
7711:
7712: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_rt_lines_table.req_template_name.COUNT='||l_rt_lines_table.req_template_name.COUNT); END IF;
7713:
7714: EXIT WHEN l_rt_lines_table.req_template_name.COUNT = 0;
7715:
7716: l_progress := '070';
7730: END IF;
7731: l_progress := '120';
7732: EXCEPTION
7733: WHEN g_SNAPSHOT_TOO_OLD THEN
7734: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_rt_lines_csr'); END IF;
7735:
7736: -- Commit and re-open the cursor
7737: l_progress := '130';
7738: COMMIT;
7751: IF (load_rt_lines_csr%ISOPEN) THEN
7752: CLOSE load_rt_lines_csr;
7753: END IF;
7754:
7755: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7756: EXCEPTION
7757: WHEN OTHERS THEN
7758: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7759: IF (load_rt_lines_csr%ISOPEN) THEN
7754:
7755: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7756: EXCEPTION
7757: WHEN OTHERS THEN
7758: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7759: IF (load_rt_lines_csr%ISOPEN) THEN
7760: CLOSE load_rt_lines_csr;
7761: END IF;
7762: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7795: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
7796: l_progress VARCHAR2(3) := '000';
7797: BEGIN
7798: l_progress := '010';
7799: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
7800:
7801: -- SQL What: Update all the ReqTemplate lines.
7802: -- For these, only the IP_CATEGORY_ID and DESCRIPTION is allowed
7803: -- to be updated. The other columns, if provided in the interface
7818: WHERE express_name = p_rt_lines_rec.req_template_name(i)
7819: AND sequence_num = p_rt_lines_rec.req_template_line_num(i)
7820: AND org_id = p_rt_lines_rec.org_id(i);
7821:
7822: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of RT updated='||SQL%rowcount); END IF;
7823:
7824: -- Now update the upper level Line and Header Records to mark them PROCESSED
7825: FORALL i IN 1..p_rt_lines_rec.interface_attr_values_tlp_id.COUNT
7826: UPDATE PO_LINES_INTERFACE
7826: UPDATE PO_LINES_INTERFACE
7827: SET PROCESS_CODE = g_PROCESS_CODE_PROCESSED
7828: WHERE interface_line_id = p_rt_lines_rec.interface_line_id(i);
7829:
7830: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of LinesInterface rows marked PROCESSED='||SQL%rowcount); END IF;
7831:
7832: FORALL i IN 1..p_rt_lines_rec.interface_attr_values_tlp_id.COUNT
7833: UPDATE PO_HEADERS_INTERFACE
7834: SET PROCESS_CODE = g_PROCESS_CODE_PROCESSED
7833: UPDATE PO_HEADERS_INTERFACE
7834: SET PROCESS_CODE = g_PROCESS_CODE_PROCESSED
7835: WHERE interface_header_id = p_rt_lines_rec.interface_header_id(i);
7836:
7837: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of HeadersInterface rows marked PROCESSED='||SQL%rowcount); END IF;
7838:
7839: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7840: EXCEPTION
7841: WHEN OTHERS THEN
7835: WHERE interface_header_id = p_rt_lines_rec.interface_header_id(i);
7836:
7837: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of HeadersInterface rows marked PROCESSED='||SQL%rowcount); END IF;
7838:
7839: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7840: EXCEPTION
7841: WHEN OTHERS THEN
7842: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7843: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7838:
7839: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7840: EXCEPTION
7841: WHEN OTHERS THEN
7842: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7843: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7844: END update_req_template_batch;
7845:
7846: --------------------------------------------------------------------------------
7876: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
7877: l_progress VARCHAR2(3) := '000';
7878: BEGIN
7879: l_progress := '010';
7880: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
7881:
7882: -- Validate the org_id's. For invalid orgs, mark all associated records as REJECTED.
7883: PO_R12_CAT_UPG_VAL_PVT.validate_org_ids(
7884: p_batch_id => p_batch_id
7895: WHERE batch_id = p_batch_id
7896: AND process_code = g_PROCESS_CODE_NEW;
7897:
7898: l_progress := '020';
7899: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of distinct orgs='||x_org_id_list.COUNT); END IF;
7900:
7901: l_progress := '030';
7902: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7903: EXCEPTION
7898: l_progress := '020';
7899: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of distinct orgs='||x_org_id_list.COUNT); END IF;
7900:
7901: l_progress := '030';
7902: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7903: EXCEPTION
7904: WHEN OTHERS THEN
7905: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7906: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7901: l_progress := '030';
7902: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
7903: EXCEPTION
7904: WHEN OTHERS THEN
7905: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
7906: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
7907: END get_distinct_orgs;
7908:
7909: END PO_R12_CAT_UPG_PVT;