[Home] [Help]
339: -- -- SQL Why : To perform cleanup.
340: -- -- SQL Join: processing_id, process_code, po_header_id
341: -- CURSOR load_orphan_attr_csr IS
342: -- SELECT POATTR.attribute_values_id
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
352: -- -- SQL Why : To perform cleanup.
353: -- -- SQL Join: processing_id, process_code, po_header_id
354: -- CURSOR load_orphan_tlp_csr IS
355: -- SELECT POTLP.attribute_values_tlp_id
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
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:
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:
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
5110: -- SQL Join: none
5111: FORALL i IN 1..p_attr_values_tbl.po_line_id.COUNT
5112: INSERT INTO po_attribute_values
5113: (attribute_values_id,
5114: po_line_id,
5115: req_template_name,
5116: req_template_line_num,
5437: last_updated_program
5438: )
5439: -- Bug 5677911: Added the hint for performance reason.
5440: SELECT /*+ INDEX(POAVI, PO_ATTR_VALUES_INT_U1)*/
5441: PO_ATTRIBUTE_VALUES_S.nextval,
5442:
5443: -- ECO bug 4738058
5444: --p_attr_values_tbl.po_line_id(i),
5445: --bug 7245624 added nvl condition
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
5846: -- SQL Join: po_line_id
5847:
5848: -- Bug#5389286: Removed unnecessary OR conditions in the criteria
5849: -- For an update action, iP will always populate the
5849: -- For an update action, iP will always populate the
5850: -- po_line_id/req_template_name/req_template_line_num/org_id in the
5851: -- attr/attr_tlp interface tables
5852: FORALL i IN 1..p_attr_values_tbl.po_line_id.COUNT
5853: UPDATE po_attribute_values
5854: SET
5855: ip_category_id = p_attr_values_tbl.ip_category_id(i),
5856: inventory_item_id = p_attr_values_tbl.inventory_item_id(i),
5857: manufacturer_part_num = p_attr_values_tbl.manufacturer_part_num(i),
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
6253: WHERE p_attr_values_tbl.has_errors(i) = 'N'
6254: AND p_attr_values_tbl.action(i) = 'DELETE'
6255: AND ( (po_line_id = p_attr_values_tbl.po_line_id(i)
6256: AND p_attr_values_tbl.po_line_id(i) <> g_NOT_REQUIRED_ID)
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
6869: FORALL i IN 1..p_attr_values_tlp_tbl.po_line_id.COUNT
6870: INSERT INTO po_attribute_values_tlp
6871: (attribute_values_tlp_id,
6872: po_line_id,
6873: req_template_name,
6874: req_template_line_num,
7043: last_updated_program
7044: )
7045: -- Bug 5677911: Added the hint for performance reason.
7046: SELECT /*+ INDEX(POAVTI, PO_ATTR_VALUES_TLP_INT_U1)*/
7047: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
7048:
7049: -- ECO bug 4738058
7050: --p_attr_values_tlp_tbl.po_line_id(i),
7051: --bug 7245624 added nvl cond
7301: -- For an update action, iP will always populate the
7302: -- po_line_id/req_template_name/req_template_line_num/org_id in the
7303: -- attr/attr_tlp interface tables
7304: FORALL i IN 1..p_attr_values_tlp_tbl.po_line_id.COUNT
7305: UPDATE po_attribute_values_tlp
7306: SET
7307: ip_category_id = p_attr_values_tlp_tbl.ip_category_id(i),
7308: inventory_item_id = p_attr_values_tlp_tbl.inventory_item_id(i),
7309: language = p_attr_values_tlp_tbl.language(i),
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
7556: WHERE p_attr_values_tlp_tbl.has_errors(i) = 'N'
7557: AND p_attr_values_tlp_tbl.action(i) = 'DELETE'
7558: AND language = p_attr_values_tlp_tbl.language(i)
7559: AND ( (po_line_id = p_attr_values_tlp_tbl.po_line_id(i)