[Home] [Help]
52: -- Also create/update the corresponding attributes/attributesTLP records.
53: -- This API should be called during the upgrade phase only.
54: --Parameters:
55: --INend p_batch_size size of the po lines batch to be processed
56: -- p_start_id start rowid of the po_lines_all to be processed
57: -- p_end_id end rowid of the po_lines_all to be processed
58: --OUT:
59: -- x_return_status: status useful for the upgrade
60: -- x_rows_processed: rows processed - useful for the ad parallel upgrade
53: -- This API should be called during the upgrade phase only.
54: --Parameters:
55: --INend p_batch_size size of the po lines batch to be processed
56: -- p_start_id start rowid of the po_lines_all to be processed
57: -- p_end_id end rowid of the po_lines_all to be processed
58: --OUT:
59: -- x_return_status: status useful for the upgrade
60: -- x_rows_processed: rows processed - useful for the ad parallel upgrade
61: --End of Comments
109: DECODE( NVL(icxm.shopping_category_id, NULL_ID), TLP.ip_category_id,
110: decode(POL.item_description, TLP.description,
111: decode(POL.item_id, TLP.inventory_item_id,
112: 'N', 'Y'), 'Y'), 'Y') -- Recreate Attribute TLP
113: FROM PO_LINES_ALL POL, PO_ATTRIBUTE_VALUES_TLP TLP,
114: ICX_CAT_PURCHASING_CAT_MAP_V ICXM
115: WHERE POL.last_updated_program = g_R12_MIGRATION_PROGRAM
116: AND POL.po_line_id = TLP.po_line_id
117: AND POL.CATEGORY_ID = ICXM.po_category_id(+)
125: POL.ip_category_id, NVL(POL.item_id, NULL_ID), POL.org_id, POL.item_description,
126: POL.attribute13, -- Image
127: POL.attribute14, -- Image URL
128: 'ITEM_TRANSLATION', 'N','Y' -- Attribute, Recreate Attrib, Recreate Attrib TLP
129: FROM PO_LINES_ALL POL, MTL_SYSTEM_ITEMS_TL MTL,
130: FINANCIALS_SYSTEM_PARAMS_ALL FSP
131: WHERE POL.last_updated_program = g_R12_MIGRATION_PROGRAM
132: AND POL.item_id IS NOT NULL
133: AND POL.item_id = MTL.inventory_item_id
153: 'LINE_DELETED', 'N', 'N' -- Attribute, Recreate Attrib, Recreate Attrib TLP
154: FROM PO_ATTRIBUTE_VALUES POAT
155: WHERE po_line_id <> NULL_ID --Bug#4865650
156: AND NOT EXISTS ( SELECT 'PO Line deleted after pre-upgrade'
157: FROM PO_LINES_ALL POL
158: WHERE POL.po_line_id = POAT.po_line_id )
159: AND POAT.po_line_id between p_start_id and p_end_id ;--Bug#5156673
160:
161: l_was_R12_upg_ever_run_before NUMBER := 0;
175: -- lines that have been modified since the last upgrade.
176: -- SQL Join: last_updated_program
177: SELECT count(*)
178: INTO l_was_R12_upg_ever_run_before
179: FROM po_lines_all pol
180: WHERE pol.last_updated_program = g_R12_MIGRATION_PROGRAM
181: AND POL.po_line_id between p_start_id and p_end_id
182: AND rownum=1;
183:
249: -- because we want to preserve that information(updating -2 to these
250: -- columns is not useful when we already have the
251: -- last_updated_program updated to g_R12_MIGRATION_PROGRAM
252: FORALL i IN 1..gPoLineIds.COUNT
253: UPDATE PO_LINES_ALL POL
254: SET ip_category_id = gIpCategoryIds(i),
255: last_updated_program = g_R12_MIGRATION_PROGRAM
256: WHERE po_line_id = gPoLineIds(i)
257: AND gUpdatedAttribute(i) = 'IP_CATEGORY_ID';
257: AND gUpdatedAttribute(i) = 'IP_CATEGORY_ID';
258:
259: x_rows_processed := SQL%ROWCOUNT; --Bug#5156673:
260:
261: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_LINES_ALL rows updated='||x_rows_processed); END IF;
262:
263: l_progress := '100';
264:
265: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO Lines: Deleting Attributes'); END IF;
344: -- This API should be called during the upgrade phase only.
345: --Parameters:
346: --IN:
347: -- p_batch_size size of the po lines batch to be processed
348: -- p_start_id start po_line_id of the po_lines_all to be processed
349: -- p_end_id end po_line_id of the po_lines_all to be processed
350: --OUT:
351: -- x_return_status: status useful for the upgrade
352: -- x_rows_processed: rows processed - useful for the ad parallel upgrade
345: --Parameters:
346: --IN:
347: -- p_batch_size size of the po lines batch to be processed
348: -- p_start_id start po_line_id of the po_lines_all to be processed
349: -- p_end_id end po_line_id of the po_lines_all to be processed
350: --OUT:
351: -- x_return_status: status useful for the upgrade
352: -- x_rows_processed: rows processed - useful for the ad parallel upgrade
353: --End of Comments
375: POL.attribute14, -- ImageUrl
376: NULL, -- Attribute
377: 'Y', -- Recreate Attrib Flag
378: 'Y' -- Recreate Attrib TLP Flag
379: FROM PO_LINES_ALL POL,
380: PO_HEADERS_ALL POH,
381: ICX_CAT_PURCHASING_CAT_MAP_V ICXM
382: WHERE POL.last_updated_program IS NULL
383: AND POL.po_header_id = POH.po_header_id
450: -- because we want to preserve that information(updating -2 to these
451: -- columns is not useful when we already have the
452: -- last_updated_program updated to g_R12_MIGRATION_PROGRAM
453: FORALL i IN 1..gPoLineIds.COUNT
454: UPDATE PO_LINES_ALL POL
455: SET ip_category_id = gIpCategoryIds(i),
456: last_updated_program = g_R12_MIGRATION_PROGRAM
457: WHERE po_line_id = gPoLineIds(i);
458:
457: WHERE po_line_id = gPoLineIds(i);
458:
459: x_rows_processed := SQL%rowcount;--Bug#5156673
460:
461: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_LINES_ALL rows updated='||x_rows_processed); END IF;
462:
463: -- IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of IP_CATEGORY_IDs loaded='||SQL%rowcount); END IF;
464:
465: l_progress := '100';
792: sysdate, -- last_update_date
793: g_R12_UPGRADE_USER, -- created_by
794: sysdate -- creation_date
795: FROM PO_HEADERS_ALL POH,
796: PO_LINES_ALL POL
797: --, PO_ATTRIBUTE_VALUES POAT
798: WHERE gPoLineIds(i) <> NULL_ID
799: AND gInvItemids(i) <> NULL_ID
800: AND POL.po_line_id = gPoLineIds(i)