[Home] [Help]
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
320: (SELECT 'At least one line in Txn tables'
321: FROM po_lines_all POL
322: WHERE POL.po_header_id = POH.po_header_id);
323:
324: -- -- SQL What: Orphan PO Lines: Cursor to load PO lines for which the header
325: -- -- does not exist.
326: -- -- SQL Why : To perform cleanup.
327: -- -- SQL Join: processing_id, process_code, po_header_id
328: -- CURSOR load_orphan_lines_csr IS
329: -- SELECT POL.po_line_id
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
343: -- FROM PO_ATTRIBUTE_VALUES POATTR
344: -- WHERE POATTR.created_by = g_R12_UPGRADE_USER
345: -- AND NOT EXISTS
346: -- (SELECT 'Corresponding PO Line exists'
347: -- FROM PO_LINES_ALL POL
348: -- WHERE POL.po_line_id = POATTR.po_line_id);
349: --
350: -- -- SQL What: Orphan PO Attr TLP: Cursor to load PO Attr TLP for which the
351: -- -- Line does not exist.
356: -- FROM PO_ATTRIBUTE_VALUES_TLP POTLP
357: -- WHERE POTLP.created_by = g_R12_UPGRADE_USER
358: -- AND NOT EXISTS
359: -- (SELECT 'Corresponding PO Line exists'
360: -- FROM PO_LINES_ALL POL
361: -- WHERE POL.po_line_id = POTLP.po_line_id);
362:
363: l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
364: l_err_po_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2549: l_progress := '030';
2550:
2551: -- Delete from PO Lines
2552: FORALL i IN 1.. l_po_header_ids.COUNT
2553: DELETE FROM PO_LINES_ALL
2554: WHERE po_header_id = l_po_header_ids(i)
2555: RETURNING po_line_id
2556: BULK COLLECT INTO l_po_line_ids;
2557:
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
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;
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
3642: FORALL i IN 1 .. l_valid_lines.po_line_id.COUNT
3643: INSERT INTO po_lines_all POL
3644: (po_line_id,
3645: last_update_date,
3646: last_updated_by,
3647: po_header_id,
4011: -- and don't have any errors.
4012: -- SQL Why : To update the line level columns
4013: -- SQL Join: po_line_id
4014: FORALL i IN 1..p_doc_lines_rec.po_line_id.COUNT
4015: UPDATE po_lines_all
4016: SET
4017: -- Only the following 3 columns are allowed to be updated on a line,
4018: -- when it was not created by the migration program. Rest of them will
4019: -- be ignored. But for lines that were created by the migration program,
4042: -- and don't have any errors.
4043: -- SQL Why : To update the line level columns
4044: -- SQL Join: po_line_id
4045: FORALL i IN 1..p_doc_lines_rec.po_line_id.COUNT
4046: UPDATE po_lines_all
4047: SET
4048: -- Only the following 9 columns are allowed to be updated on a line,
4049: -- when it was created by the migration program. Rest of them will
4050: -- be ignored.
4099: -- originally created by upgrade/migration program
4100: -- SQL Why : To mark them as successfully updated
4101: -- SQL Join: interface_line_id
4102: FORALL i in 1..l_po_line_ids.COUNT
4103: UPDATE PO_LINES_ALL
4104: SET LAST_UPDATED_BY = g_R12_UPGRADE_USER,
4105: LAST_UPDATE_LOGIN = g_R12_UPGRADE_USER,
4106: LAST_UPDATED_PROGRAM = g_R12_MIGRATION_PROGRAM,
4107: LAST_UPDATE_DATE = sysdate
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
4172: WHERE po_line_id = p_doc_lines_rec.po_line_id(i)
4173: AND p_doc_lines_rec.has_errors(i) = 'N'
4174: AND p_doc_lines_rec.action(i) = 'DELETE'
4175: RETURNING po_line_id