[Home] [Help]
311: CURSOR load_err_headers_csr(request_processing_id NUMBER) IS
312: SELECT intf_headers.interface_header_id,
313: intf_headers.po_header_id
314: FROM PO_HEADERS_INTERFACE intf_headers,
315: PO_HEADERS_ALL POH
316: WHERE intf_headers.processing_id = request_processing_id
317: AND intf_headers.process_code <> g_PROCESS_CODE_REJECTED
318: AND POH.po_header_id = intf_headers.po_header_id
319: AND NOT EXISTS
330: -- FROM PO_LINES_ALL POL
331: -- WHERE POL.created_by = g_R12_UPGRADE_USER
332: -- AND NOT EXISTS
333: -- (SELECT 'Corresponding PO Header exists'
334: -- FROM PO_HEADERS_ALL POH
335: -- WHERE POH.po_header_id = POL.po_header_id);
336: --
337: -- -- SQL What: Orphan PO Attr: Cursor to load PO Attr for which the Line
338: -- -- does not exist.
425: -- SQL What: Delete header from txn tables
426: -- SQL Why : To cleanup corrupt data
427: -- SQL Join: po_header_id
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:
1407: FOR i IN 1 .. p_valid_po_hdr_ids.COUNT
1408: LOOP
1409: IF (l_is_attach_err_intf_hdr_id.exists(p_valid_intf_hdr_ids(i))) THEN
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
1543: l_progress := '055';
1544: BEGIN
1545: SELECT 'Y'
1546: INTO l_is_valid_cpa
1547: FROM PO_HEADERS_ALL
1548: WHERE po_header_id = x_doc_headers_rec.cpa_reference(j)
1549: AND type_lookup_code = 'CONTRACT';
1550: EXCEPTION
1551: WHEN NO_DATA_FOUND THEN
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
1561: SET
1562: (
1563: attribute_category
1564: , attribute1
1728: , xml_flag
1729: , xml_send_date
1730: , xml_change_send_date
1731: , cbc_accounting_date
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;
1993: -- is PO_R12_CAT_UPG_PVT.g_action_header_create
1994: -- SQL Why : To migrate data to txn tables
1995: -- SQL Join: none
1996: FORALL i IN 1 .. l_valid_headers.po_header_id.COUNT
1997: INSERT INTO po_headers_all POH
1998: (po_header_id,
1999: agent_id,
2000: type_lookup_code,
2001: last_update_date,
2449: -- ignored - including the CREATED_LANGUAGE.
2450: -- SQL Why : To update the header columns
2451: -- SQL Join: po_header_id
2452: FORALL i IN 1.. p_doc_headers_rec.po_header_id.COUNT
2453: UPDATE po_headers_all
2454: SET cpa_reference = DECODE(p_doc_headers_rec.cpa_reference(i),
2455: g_NULLIFY_NUM, NULL,
2456: NULL, cpa_reference,
2457: p_doc_headers_rec.cpa_reference(i))
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
2532: WHERE po_header_id = p_doc_headers_rec.po_header_id(i)
2533: AND p_doc_headers_rec.has_errors(i) = 'N'
2534: AND p_doc_headers_rec.action(i) = 'DELETE'
2535: RETURNING po_header_id