1 PACKAGE BODY PO_R12_CAT_UPG_EXISTING_DOCS AS
2 /* $Header: PO_R12_CAT_UPG_EXISTING_DOCS.plb 120.10 2006/08/18 21:58:34 pthapliy noship $ */
3
4 g_debug BOOLEAN := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
5
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_EXISTING_DOCS';
7 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
8
9 type VARCHAR2_SIZE_1 IS TABLE OF VARCHAR2(1);
10 type VARCHAR2_SIZE_25 IS TABLE OF VARCHAR2(25);
11 type VARCHAR2_SIZE_150 IS TABLE OF VARCHAR2(150);
12 type VARCHAR2_SIZE_240 IS TABLE OF VARCHAR2(240);
13
14 gInvItemIds DBMS_SQL.NUMBER_TABLE;
15 gPoOrgIds DBMS_SQL.NUMBER_TABLE;
16 gItemDescriptions DBMS_SQL.VARCHAR2_TABLE;
17 gIpCategoryIds DBMS_SQL.NUMBER_TABLE;
18
19 gPoHeaderIds DBMS_SQL.NUMBER_TABLE;
20 gPoLineIds DBMS_SQL.NUMBER_TABLE;
21
22 gPoReqTemplateNames VARCHAR2_SIZE_25;
23 gPoReqTemplateLineIds DBMS_SQL.NUMBER_TABLE;
24
25 gUpdatedAttribute DBMS_SQL.VARCHAR2_TABLE;
26 gRecreateAttribRow VARCHAR2_SIZE_1;
27 gRecreateAttribTLPRow VARCHAR2_SIZE_1;
28
29 gImages VARCHAR2_SIZE_150;
30 gImageUrls VARCHAR2_SIZE_150;
31
32 --gNumLanguages NUMBER;
33 NULL_ID NUMBER := -2;
34 g_R12_UPGRADE_USER NUMBER := PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER;
35 g_R12_MIGRATION_PROGRAM PO_HEADERS_ALL.last_updated_program%TYPE
36 := PO_R12_CAT_UPG_PVT.g_R12_MIGRATION_PROGRAM;
37
38 g_err_num NUMBER := PO_R12_CAT_UPG_PVT.g_application_err_num;
39
40 --------------------------------------------------------------------------------
41 --Start of Comments
42 --Name: process_modified_po_lines
43 --Pre-reqs:
44 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
45 --Modifies:
46 -- a) FND_MSG_PUB on unhandled exceptions.
47 --Locks:
48 -- None.
49 --Function:
50 -- Update the po lines,which have been already upgraded, but certain attribtes
51 -- have changed - item_id, category_id, description.
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
61 --End of Comments
62 --------------------------------------------------------------------------------
63 PROCEDURE process_modified_po_lines(p_batch_size IN NUMBER default 2500,
64 p_base_lang IN FND_LANGUAGES.language_code%TYPE default null,
65 p_start_id IN NUMBER default null,
66 p_end_id IN NUMBER default null,
67 x_return_status IN OUT NOCOPY VARCHAR,
68 x_rows_processed IN OUT NOCOPY NUMBER)
69 IS
70 -- Recreate flag in the below sql means clear/delete+create that row
71 -- for description change, you have to just update the description not recreate
72 -- for item number change on line, you need to recreate the Attribute+TLP row
73 --
74 -- In the below sql, there is no need for a constraint on
75 -- po headers to look for only quotes/agreements
76 -- the check for pol.last_updated_program = g_R12_MIGRATION_PROGRAM is sufficient.
77 -- there is an index on last_updated_program and only agreements/quotes will have this
78 -- populated with g_R12_MIGRATION_PROGRAM.
79 --
80
81 -- SQL What: Cursor to get the PO lines that have been modified since the
82 -- last run of the upgrade program.
83 -- Whether to recreate the attribute/TLP record is based on
84 -- what has changed - recreate only if iP_category_id/description/
85 -- item_id has changed. Even if the PO category had changed but the
86 -- mapped shopping category is the same as before, we wont recreate
87 -- the attributes.
88 -- NOTE: Keep the iP_category_id check as the
89 -- first part of the decode, since we have some logic later for
90 -- ip_category_id changes
91 -- SQL Why : To update the columns of the PO lines and the corresponding
92 -- attribute and TLP values.
93 -- SQL Join: several
94 CURSOR getModifiedLinesCsr(p_start_id NUMBER, p_end_id NUMBER) is
95 SELECT POL.po_line_id, -- PoLineId
96 TO_CHAR(NULL_ID), NULL_ID, -- TemplateName, TemplateId
97 NVL(ICXM.SHOPPING_CATEGORY_ID, NULL_ID), -- iP Category Id
98 NVL(POL.item_id, NULL_ID), POL.org_id, POL.item_description,
99 POL.attribute13, -- Image
100 POL.attribute14, -- Image URL
101 DECODE( NVL(icxm.shopping_category_id, NULL_ID), TLP.ip_category_id,
102 decode(POL.item_description, TLP.description,
103 decode(POL.item_id, TLP.inventory_item_id,
104 NULL, 'ITEM_ID'), 'DESCRIPTION'), 'IP_CATEGORY_ID'), -- Attribute
105 DECODE( NVL(icxm.shopping_category_id, NULL_ID), TLP.ip_category_id,
106 decode(POL.item_description, TLP.description,
107 decode(POL.item_id, TLP.inventory_item_id,
108 'N', 'Y'), 'Y'), 'Y'), -- Recreate Attribute
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(+)
118 AND TLP.language = p_base_lang
119 AND (POL.item_description <> TLP.description
120 OR NVL(POL.item_id, NULL_ID) <> TLP.inventory_item_id)
121 AND POL.po_line_id between p_start_id and p_end_id --Bug#5156673
122 UNION ALL
123 SELECT POL.po_line_id, -- PoLineId
124 TO_CHAR(NULL_ID), NULL_ID, -- TemplateName, TemplateId
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
134 AND POL.org_id = FSP.org_id
135 AND FSP.inventory_organization_id = MTL.organization_id
136 AND MTL.language = MTL.source_lang
137 AND NOT EXISTS
138 (
139 SELECT 'Upgraded Lines with newly added item master translations'
140 FROM PO_ATTRIBUTE_VALUES_TLP POATLP
141 WHERE POATLP.po_line_id <> NULL_ID
142 AND POATLP.po_line_id = POL.po_line_id
143 AND POATLP.org_id = POL.org_id
144 AND POATLP.language = MTL.language
145 )
146 AND POL.po_line_id between p_start_id and p_end_id --Bug#5156673
147 UNION ALL
148 SELECT po_line_id, -- PoLineId
149 TO_CHAR(NULL_ID), NULL_ID, -- TemplateName, TemplateId
150 ip_category_id, nvl(inventory_item_id, NULL_ID), org_id, null, -- no need to get description(null)
151 NULL, -- Image
152 NULL, -- Image URL
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;
162
163 l_api_name CONSTANT VARCHAR2(30) := 'process_modified_po_lines';
164 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
165 l_progress VARCHAR2(3) := '000';
166
167 BEGIN
168 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
169
170 l_progress := '010';
171
172 -- SQL What: If this is the first time the upgrade script has been run
173 -- then directly run the procedure PROCESS_NEW_PO_LINES.
174 -- SQL Why : The procedure PROCESS_MODIFIED_PO_LINES is applicable only for those
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
184 l_progress := '030';
185 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'lines_already_upgraded flag='||l_was_R12_upg_ever_run_before); END IF;
186
187 IF (l_was_R12_upg_ever_run_before = 0) THEN
188 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'early END'); END IF;
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190 RETURN;
191 END IF;
192
193 l_progress := '040';
194 LOOP
195 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inside loop'); END IF;
196 -- The following exception occurs if we dont do this check for existence:
197 -- ORA-06531: Reference to uninitialized collection.
198 IF (gPoLineIds.exists(1)) THEN
199 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Modified Lines; gPoLineIds.DELETE'); END IF;
200 gPoLineIds.DELETE;
201 gIpCategoryIds.DELETE;
202 gInvItemIds.DELETE;
203 gPoOrgIds.DELETE;
204 gItemDescriptions.DELETE;
205 gImages.DELETE;
206 gImageUrls.DELETE;
207 gUpdatedAttribute.DELETE;
208 gRecreateAttribRow.DELETE;
209 gRecreateAttribTLPRow.DELETE;
210 gPoReqTemplateNames.DELETE;
211 gPoReqTemplateLineIds.DELETE;
212 END IF;
213
214 -- Get the PO Lines which have not been updated yet
215 l_progress := '050';
216 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Opening Cursor'); END IF;
217 OPEN getModifiedLinesCsr(p_start_id, p_end_id);
218
219 l_progress := '060';
220 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Fetching cursor'); END IF;
221 -- No need of LIMIT batchsize because the cursor is returning only
222 -- a batch of lines (start and end line id)
223 FETCH getModifiedLinesCsr
224 BULK COLLECT into gPoLineIds,
225 gPoReqTemplateNames, gPoReqTemplateLineIds,
226 gIpCategoryIds, gInvItemIds,
227 gPoOrgIds, gItemDescriptions,
228 gImages,
229 gImageUrls,
230 gUpdatedAttribute,
231 gRecreateAttribRow, gRecreateAttribTLPRow;
232
233 l_progress := '070';
234 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Closing cursor'); END IF;
235 CLOSE getModifiedLinesCsr;
236
237 l_progress := '080';
238 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gPoLineIds.COUNT='||gPoLineIds.COUNT); END IF;
239 EXIT WHEN gPoLineIds.COUNT = 0;
240
241 l_progress := '090';
242
243 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Updating PO Lines'); END IF;
244
245 -- SQL What: Update the PO Lines with ip_category_id
246 -- SQL Why : Part of catalog upgrade requirements
247 -- SQL Join: po_line_id
248 -- We are specifically not updating the last_updated_by, login columns
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';
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;
266
267 -- SQL What: For item change erase the attributes
268 -- SQL Why : Instead of erasing the columns, just delete the rows from
269 -- attributes table and recreate the row later.
270 -- SQL Join: po_line_id
271 FORALL i IN 1..gPoLineIds.COUNT
272 DELETE FROM PO_ATTRIBUTE_VALUES POAT
273 WHERE po_line_id = gPoLineIds(i)
274 AND (gRecreateAttribRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');
275
276 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES rows deleted='||SQL%rowcount); END IF;
277
278 l_progress := '110';
279
280 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Deleting Attribute TLP'); END IF;
281
282 -- SQL What: For item change erase the attributes TLP
283 -- SQL Why : Instead of erasing the columns, just delete the rows from
284 -- attributes TLP table and recreate the row later.
285 -- SQL Join: po_line_id
286 FORALL i IN 1..gPoLineIds.COUNT
287 DELETE FROM PO_ATTRIBUTE_VALUES_TLP POATLP
288 WHERE po_line_id = gPoLineIds(i)
289 AND (gRecreateAttribTLPRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');
290
291
292 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows deleted='||SQL%rowcount); END IF;
293
294 l_progress := '120';
295 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Updating Attributes'); END IF;
296
297 -- SQL What: Update the PO attribute values TLP when the description has changed
298 -- SQL Why : Sync up the description at the TLP level
299 -- SQL Join: po_line_id
300 FORALL i IN 1..gPoLineIds.COUNT
301 UPDATE PO_ATTRIBUTE_VALUES_TLP
302 SET description = gItemDescriptions(i),
303 last_updated_program = g_R12_MIGRATION_PROGRAM
304 WHERE po_line_id = gPoLineIds(i)
305 AND gUpdatedAttribute(i) = 'DESCRIPTION';
306
307 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows updated='||SQL%rowcount); END IF;
308
309 l_progress := '130';
310
311 -- Lines for which attributes records need to be created are in gPoLineIds
312 CREATE_LINE_ATTRIBUTES(p_batch_size, p_base_lang);
313
314 l_progress := '140';
315 -- Lines for which attributes TLP records need to be created are in gPoLineIds
316 CREATE_LINE_ATTRIBUTES_TLP(p_batch_size, p_base_lang);
317
318 l_progress := '150';
319 -- Commit is done by the calling program - poxukpol.sql
320
321 END LOOP;
322 x_return_status := FND_API.G_RET_STS_SUCCESS;
323
324 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
325 EXCEPTION
326 WHEN OTHERS THEN
327 ROLLBACK;
328 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
329 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
330 END process_modified_po_lines;
331
332 --------------------------------------------------------------------------------
333 --Start of Comments
334 --Name: process_new_po_lines
335 --Pre-reqs:
336 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
337 --Modifies:
338 -- a) FND_MSG_PUB on unhandled exceptions.
339 --Locks:
340 -- None.
341 --Function:
342 -- Update the po lines,which have never been upgraded, with ip category id info.
343 -- Also create the corresponding attributes/attributesTLP records.
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
353 --End of Comments
354 --------------------------------------------------------------------------------
355 PROCEDURE process_new_po_lines( p_batch_size IN NUMBER default 2500,
356 p_base_lang IN FND_LANGUAGES.language_code%TYPE default null,
357 p_start_id IN NUMBER default null,
358 p_end_id IN NUMBER default null,
359 x_return_status IN OUT NOCOPY VARCHAR,
360 x_rows_processed IN OUT NOCOPY NUMBER)
361 IS
362 -- SQL What: Cursor to get the PO lines that have not been upgraded yet.
363 -- SQL Why : To update the columns of the PO lines and the corresponding
364 -- attribute and TLP values.
365 -- SQL Join: type_lookup_code, po_header_id, last_updated_program
366 CURSOR getNonUpgradedLinesCsr(p_start_id NUMBER, p_end_id NUMBER) IS
367 SELECT po_line_id, -- po_line_id
368 to_char(NULL_ID), -- TemplateName
369 NULL_ID, -- TemplateId
370 NVL(ICXM.SHOPPING_CATEGORY_ID, NULL_ID),
371 NVL(item_id, NULL_ID),
372 pol.org_id,
373 item_description,
374 POL.attribute13, -- Image
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
384 AND POH.type_lookup_code IN ('BLANKET', 'QUOTATION')
385 AND POL.CATEGORY_ID = ICXM.po_category_id(+)
386 AND POL.po_line_id between p_start_id and p_end_id ;--Bug#5156673
387
388 l_api_name CONSTANT VARCHAR2(30) := 'process_new_po_lines';
389 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
390 l_progress VARCHAR2(3) := '000';
391
392 BEGIN
393 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
394
395 l_progress := '010';
396
397 LOOP
398 l_progress := '030';
399
400 -- The following exception occurs if we dont do this check for existence:
401 -- ORA-06531: Reference to uninitialized collection.
402 IF (gPoLineIds.exists(1)) THEN
403 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'New Lines; gPoLineIds.DELETE'); END IF;
404 gPoLineIds.DELETE;
405 gIpCategoryIds.DELETE;
406 gInvItemIds.DELETE;
407 gPoOrgIds.DELETE;
408 gItemDescriptions.DELETE;
409 gImages.DELETE;
410 gImageUrls.DELETE;
411 gUpdatedAttribute.DELETE;
412 gRecreateAttribRow.DELETE;
413 gRecreateAttribTLPRow.DELETE;
414 gPoReqTemplateNames.DELETE;
415 gPoReqTemplateLineIds.DELETE;
416 END IF;
417
418 -- Get the PO Lines which have not been updated yet
419 l_progress := '040';
420 OPEN getNonUpgradedLinesCsr(p_start_id, p_end_id);
421
422 l_progress := '050';
423 FETCH getNonUpgradedLinesCsr
424 BULK COLLECT into gPoLineIds,
425 gPoReqTemplateNames, gPoReqTemplateLineIds,
426 gIpCategoryIds, gInvItemIds,
427 gPoOrgIds, gItemDescriptions,
428 gImages,
429 gImageUrls,
430 gUpdatedAttribute,
431 gRecreateAttribRow, gRecreateAttribTLPRow;
432
433 l_progress := '060';
434 CLOSE getNonUpgradedLinesCsr;
435
436
437 l_progress := '070';
438 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Processing a batch with gPoLineIds.COUNT='||gPoLineIds.COUNT); END IF;
439 EXIT WHEN gPoLineIds.COUNT = 0;
440
441 l_progress := '080';
442
443 -- Update the PO Lines with ip_category_id
444
445 -- SQL What: Update the PO Lines with ip_category_id and stamp the
446 -- last_updated_program with the Catalog Upgrade User
447 -- SQL Why : Part of catalog upgrade requirements
448 -- SQL Join: po_line_id
449 -- We are specifically not updating the last_updated_by, login columns
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
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';
466 -- Lines for which attributes need to be created are in gPoLineIds
467 CREATE_LINE_ATTRIBUTES(p_batch_size, p_base_lang);
468
469 l_progress := '110';
470 -- Lines for which attributes TLP records need to be created are in gPoLineIds
471 CREATE_LINE_ATTRIBUTES_TLP(p_batch_size, p_base_lang);
472
473 l_progress := '120';
474 -- Commit is done by the calling program - poxukpol.sql
475 END LOOP;
476
477 x_return_status := FND_API.G_RET_STS_SUCCESS;
478
479 l_progress := '130';
480 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
481 EXCEPTION
482 WHEN OTHERS THEN
483 ROLLBACK;
484 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
485 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
486 END process_new_po_lines;
487
488 --------------------------------------------------------------------------------
489 --Start of Comments
490 --Name: create_line_attributes
491 --Pre-reqs:
492 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
493 --Modifies:
494 -- a) FND_MSG_PUB on unhandled exceptions.
495 --Locks:
496 -- None.
497 --Function:
498 -- Create Line Attributes for a Agreements/Quotations/Requisition Templates.
499 -- This API should be called during the upgrade phase only.
500 --Parameters:
501 --IN:
502 -- None
503 --OUT:
504 -- None
505 --End of Comments
506 --------------------------------------------------------------------------------
507 PROCEDURE create_line_attributes(p_batch_size IN NUMBER,
508 p_base_lang IN FND_LANGUAGES.language_code%TYPE)
509 IS
510 l_api_name CONSTANT VARCHAR2(30) := 'create_line_attributes';
511 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
512 l_progress VARCHAR2(3) := '000';
513
514 BEGIN
515 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
516
517 IF g_debug THEN
518 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gPoLineIds.COUNT='||gPoLineIds.COUNT);
519 IF (gPoLineIds.COUNT > 0) THEN
520 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gPoLineIds(1)='||gPoLineIds(1));
521 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gPoReqTemplateNames(1)='||gPoReqTemplateNames(1));
522 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gPoReqTemplateLineIds(1)='||gPoReqTemplateLineIds(1));
523 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gIpCategoryIds(1)='||gIpCategoryIds(1));
524 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gInvItemIds(1)='||gInvItemIds(1));
525 PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'gPoOrgIds(1)='||gPoOrgIds(1));
526 END IF;
527 END IF;
528
529 l_progress := '010';
530 -- SQL What: Create records in Attribute table
531 -- SQL Why : Only those records that have gRecreateAttribRow(i)='Y', need to be
532 -- created in the Attributes table. Insert 1 attribute record for every
533 -- line that has been just processed and does not already exist in
534 -- attributes table.
535 -- SQL Join: several
536 FORALL i IN 1..gPoLineIds.COUNT
537 INSERT INTO PO_ATTRIBUTE_VALUES
538 (
539 ATTRIBUTE_VALUES_ID,
540 PO_LINE_ID,
541 REQ_TEMPLATE_NAME,
542 REQ_TEMPLATE_LINE_NUM,
543 IP_CATEGORY_ID,
544 INVENTORY_ITEM_ID,
545 ORG_ID,
546 PICTURE,
547 LAST_UPDATED_BY,
548 LAST_UPDATE_LOGIN,
549 last_updated_program,
550 LAST_UPDATE_DATE,
551 CREATED_BY,
552 CREATION_DATE
553 )
554 SELECT PO_ATTRIBUTE_VALUES_S.nextval,
555 gPoLineIds(i),
556 gPoReqTemplateNames(i), -- req_template_name
557 gPoReqTemplateLineIds(i), -- req_template_line_id
558 gIpCategoryIds(i), -- ip_category_id
559 gInvItemIds(i), -- inventory_item_id
560 gPoOrgIds(i), -- org_id
561 NVL(gImages(i), gImageUrls(i)), -- Image or URL
562 g_R12_UPGRADE_USER, -- last_updated_by
563 g_R12_UPGRADE_USER, -- last_update_login
564 g_R12_MIGRATION_PROGRAM, -- last_update_program
565 sysdate, -- last_update_date
566 g_R12_UPGRADE_USER, -- created_by
567 sysdate -- creation_date
568 FROM DUAL
569 WHERE gRecreateAttribRow(i) = 'Y'
570 AND NOT EXISTS
571 (SELECT /*+ INDEX(POAT, PO_ATTRIBUTE_VALUES_U2) */
572 'Attr row already exists'
573 FROM PO_ATTRIBUTE_VALUES POAT
574 WHERE POAT.po_line_id = gPoLineIds(i)
575 AND POAT.req_template_name = gPoReqTemplateNames(i)
576 AND POAT.req_template_line_num = to_char(gPoReqTemplateLineIds(i))
577 AND POAT.org_id = gPoOrgIds(i));
578
579 l_progress := '020';
580 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES rows inserted='||SQL%rowcount); END IF;
581
582 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
583 EXCEPTION
584 WHEN OTHERS THEN
585 ROLLBACK;
586 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception:'||SQLERRM); END IF;
587 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
588 END create_line_attributes;
589
590 --------------------------------------------------------------------------------
591 --Start of Comments
592 --Name: create_line_attributes_tlp
593 --Pre-reqs:
594 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
595 --Modifies:
596 -- a) FND_MSG_PUB on unhandled exceptions.
597 --Locks:
598 -- None.
599 --Function:
600 -- Create Line Attributes TLP.
601 -- Create Line Attributes for TLP records for
602 -- Agreements/Quotations/Requisition Templates.
603 -- This API should be called during the upgrade phase only.
604 --Parameters:
605 --IN:
606 -- None
607 --OUT:
608 -- None
609 --End of Comments
610 --------------------------------------------------------------------------------
611 PROCEDURE create_line_attributes_tlp(p_batch_size IN NUMBER,
612 p_base_lang IN FND_LANGUAGES.language_code%TYPE)
613 IS
614 l_api_name CONSTANT VARCHAR2(30) := 'create_line_attributes_tlp';
615 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
616 l_progress VARCHAR2(3) := '000';
617 BEGIN
618 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
619
620 l_progress := '020';
621 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inserting for description based items'); END IF;
622
623 -- SQL What: Insert attributes TLP records for every line that has been just
624 -- processed and the attribute row does not already exist in
625 -- attributes_tlp table.
626 -- SQL Why : Only those records that have gRecreateAttribTLPRow(i)='Y', need
627 -- to be created in the Attributes table. Expense items will have
628 -- only 1 TLP record it does not matter which global plsql structure
629 -- we look at, for the count (we are using gInvItemIds.COUNT. For
630 -- expense items you will still have this element, but the value
631 -- will be null.
632 -- SQL Join: several
633 FORALL i in 1..gInvItemIds.COUNT
634 INSERT INTO PO_ATTRIBUTE_VALUES_TLP
635 (
636 ATTRIBUTE_VALUES_TLP_ID,
637 PO_LINE_ID,
638 REQ_TEMPLATE_NAME,
639 REQ_TEMPLATE_LINE_NUM,
640 IP_CATEGORY_ID,
641 INVENTORY_ITEM_ID,
642 ORG_ID,
643 LANGUAGE,
644 DESCRIPTION,
645 LAST_UPDATED_BY,
646 LAST_UPDATE_LOGIN,
647 last_updated_program,
648 LAST_UPDATE_DATE,
649 CREATED_BY,
650 CREATION_DATE
651 )
652 SELECT PO_ATTRIBUTE_VALUES_TLP_S.nextval,
653 gPoLineIds(i),
654 gPoReqTemplateNames(i), -- req_template_name
655 gPoReqTemplateLineIds(i), -- req_template_line_id
656 gIpCategoryIds(i), -- ip_category_id
657 gInvItemIds(i), -- inventory_item_id
658 gPoOrgIds(i), -- org_id
659 p_base_lang,
660 gItemDescriptions(i),
661 g_R12_UPGRADE_USER, -- last_updated_by
662 g_R12_UPGRADE_USER, -- last_update_login
663 g_R12_MIGRATION_PROGRAM, -- last_update_program
664 sysdate, -- last_update_date
665 g_R12_UPGRADE_USER, -- created_by
666 sysdate -- creation_date
667 FROM DUAL
668 WHERE gRecreateAttribTLPRow(i) = 'Y'
669 AND gInvItemIds(i) = NULL_ID -- Description based non catalog item
670 AND NOT EXISTS
671 (SELECT /*+ INDEX(POATLP, PO_ATTRIBUTE_VALUES_TLP_U2) */
672 NULL
673 FROM PO_ATTRIBUTE_VALUES_TLP POATLP
674 WHERE POATLP.po_line_id = gPoLineIds(i)
675 AND POATLP.req_template_name = gPoReqTemplateNames(i)
676 AND POATLP.req_template_line_num = to_char(gPoReqTemplateLineIds(i))
677 AND POATLP.org_id = gPoOrgIds(i));
678
679 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows inserted by default='||SQL%rowcount); END IF;
680
681 l_progress := '030';
682 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inserting for item master items (pulling translations from INV)'); END IF;
683
684 -- SQL What: Handle for Item master based documents. Pull translations
685 -- from INV.
686 -- SQL Why : Only those records that have gRecreateAttribTLPRow(i)='Y', need
687 -- to be created in the Attributes table.
688 -- SQL Join: several
689 FORALL i in 1..gPoLineIds.COUNT
690 INSERT INTO PO_ATTRIBUTE_VALUES_TLP
691 (
692 ATTRIBUTE_VALUES_TLP_ID,
693 PO_LINE_ID,
694 REQ_TEMPLATE_NAME,
695 REQ_TEMPLATE_LINE_NUM,
696 IP_CATEGORY_ID,
697 INVENTORY_ITEM_ID,
698 ORG_ID,
699 LANGUAGE,
700 DESCRIPTION,
701 LAST_UPDATED_BY,
702 LAST_UPDATE_LOGIN,
703 last_updated_program,
704 LAST_UPDATE_DATE,
705 CREATED_BY,
706 CREATION_DATE
707 )
708 SELECT PO_ATTRIBUTE_VALUES_TLP_S.nextval,
709 gPoLineIds(i),
710 gPoReqTemplateNames(i), -- req_template_name
711 gPoReqTemplateLineIds(i), -- req_template_line_id
712 gIpCategoryIds(i), -- ip_category_id
713 gInvItemIds(i), -- inventory_item_id
714 gPoOrgIds(i), -- org_id
715 mtl.language, -- Language
716 -- For catalog language/base lang, the description is from PO Lines
717 -- For the translations, the description is from items TL
718 NVL(decode(mtl.language, p_base_lang, gItemDescriptions(i), mtl.description), mtl.description), -- For null item_description, default from item master
719 g_R12_UPGRADE_USER, -- last_updated_by
720 g_R12_UPGRADE_USER, -- last_update_login
721 g_R12_MIGRATION_PROGRAM, -- last_updated_program
722 sysdate, -- last_update_date
723 g_R12_UPGRADE_USER, -- created_by
724 sysdate -- creation_date
725 FROM MTL_SYSTEM_ITEMS_TL MTL, FINANCIALS_SYSTEM_PARAMS_ALL FSP
726 WHERE gRecreateAttribTLPRow(i) = 'Y'
727 AND gInvItemIds(i) <> NULL_ID -- Item master items
728 AND gInvItemIds(i) = MTL.inventory_item_id
729 AND gPoOrgIds(i) = FSP.org_id
730 AND FSP.inventory_organization_id = MTL.organization_id
731 AND MTL.language = MTL.source_lang
732 AND NOT EXISTS
733 (SELECT /*+ INDEX(POATLP, PO_ATTRIBUTE_VALUES_TLP_U2) */
734 NULL
735 FROM PO_ATTRIBUTE_VALUES_TLP POATLP
736 WHERE POATLP.language = MTL.language
737 AND POATLP.po_line_id = gPoLineIds(i)
738 AND POATLP.req_template_name = gPoReqTemplateNames(i)
739 AND POATLP.req_template_line_num = to_char(gPoReqTemplateLineIds(i))
740 AND POATLP.org_id = gPoOrgIds(i));
741
742 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows inserted due to item master translation='||SQL%rowcount); END IF;
743
744 l_progress := '040';
745 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Inserting for item master items that have no translation available in base lang'); END IF;
746
747 -- SQL What: Handle those item master record that dont have translation in
748 -- the created_language of the Document header.
749 -- SQL Why : We need to create a default row with language=created_language
750 -- SQL Join: several
751 --
752 -- Assumption: Can we make the assumption or document that cst should not
753 -- change the base language ? if so then we can always use the base language
754 -- instead of joining with po_headers_all to get created_language.
755
756 -- This sql#3 is used to create a default TLP row for the created_lang
757 -- (only for Blankets/Quotations), if:
758 -- 1. The created_lang is different from base_lang
759 -- 2. The MTL translation for created_lang does not exist
760 -- In this case, copy the description from the PO Line
761 FORALL i in 1..gPoLineIds.COUNT
762 INSERT INTO PO_ATTRIBUTE_VALUES_TLP
763 (
764 ATTRIBUTE_VALUES_TLP_ID,
765 PO_LINE_ID,
766 REQ_TEMPLATE_NAME,
767 REQ_TEMPLATE_LINE_NUM,
768 IP_CATEGORY_ID,
769 INVENTORY_ITEM_ID,
770 ORG_ID,
771 LANGUAGE,
772 DESCRIPTION,
773 LAST_UPDATED_BY,
774 LAST_UPDATE_LOGIN,
775 last_updated_program,
776 LAST_UPDATE_DATE,
777 CREATED_BY,
778 CREATION_DATE
779 )
780 SELECT PO_ATTRIBUTE_VALUES_TLP_S.nextval,
781 gPoLineIds(i),
782 NULL_ID, --gPoReqTemplateNames(i), -- req_template_name
783 NULL_ID, --gPoReqTemplateLineIds(i), -- req_template_line_id
784 gIpCategoryIds(i), -- ip_category_id
785 gInvItemIds(i), -- inventory_item_id
786 gPoOrgIds(i), -- org_id
787 POH.created_language, -- Language
788 POL.item_description, -- item_description
789 g_R12_UPGRADE_USER, -- last_updated_by
790 g_R12_UPGRADE_USER, -- last_update_login
791 g_R12_MIGRATION_PROGRAM, -- last_update_program
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)
801 AND POH.po_header_id = POL.po_header_id
802 --AND POH.created_language <> p_base_lang (ECO bug 4862164)
803 --AND POAT.po_line_id = POL.po_line_id -- make sure that the Attr row exists
804 AND NOT EXISTS
805 (SELECT /*+ INDEX(POATLP, PO_ATTRIBUTE_VALUES_TLP_U2) */
806 'TLP row for created_lang already exists for Blanket/Quotation line'
807 FROM PO_ATTRIBUTE_VALUES_TLP POATLP
808 WHERE POATLP.language = POH.created_language
809 AND POATLP.po_line_id = gPoLineIds(i));
810
811 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows inserted due to nonexistence of item master in created_lang ='||SQL%rowcount); END IF;
812
813 l_progress := '050';
814 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
815 EXCEPTION
816 WHEN OTHERS THEN
817 ROLLBACK;
818 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
819 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
820 END create_line_attributes_tlp;
821
822 --------------------------------------------------------------------------------
823 --Start of Comments
824 --Name: process_modified_rt_lines
825 --Pre-reqs:
826 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
827 --Modifies:
828 -- a) FND_MSG_PUB on unhandled exceptions.
829 --Locks:
830 -- None.
831 --Function:
832 -- Create Attributes record for Req Templates
833 -- This API should be called during the upgrade phase only.
834 --Parameters:
835 --IN:
836 -- None
837 --OUT:
838 -- None
839 --End of Comments
840 --------------------------------------------------------------------------------
841 PROCEDURE process_modified_rt_lines(p_batch_size IN NUMBER,
842 p_base_lang IN FND_LANGUAGES.language_code%TYPE)
843 IS
844 -- SQL What: Cursor to get modified RT lines
845 -- Whether to recreate the attribute/TLP record is based on
846 -- what has changed - recreate only if iP_category_id/description/
847 -- item_id has changed. Even if the PO category had changed but the
848 -- mapped shopping category is the same as before, we wont recreate
849 -- the attributes.
850 -- NOTE: Keep the iP_category_id check as the
851 -- first part of the decode, since we have some logic later for
852 -- ip_category_id changes
853 -- SQL Why : To update the attributes
854 -- SQL Join: several
855 CURSOR getModifiedTemplatesCsr IS
856 SELECT NULL_ID, -- PoLineId
857 express_name, sequence_num, -- TemplateName, TemplateId
858 NVL(ICXM.shopping_category_id, NULL_ID),
859 NVL(PORL.item_id, NULL_ID), PORL.org_id, PORL.item_description,
860 NULL, -- Image
861 NULL, -- ImageUrl
862 DECODE(
863 NVL(ICXM.shopping_category_id, NULL_ID), POATLP.ip_category_id,
864 DECODE(PORL.item_id, POATLP.inventory_item_id,
865 DECODE(PORL.item_description, POATLP.description,
866 NULL, 'DESCRIPTION'), 'ITEM_ID'), 'IP_CATEGORY_ID'), -- Attribute Modified
867 DECODE(
868 NVL(ICXM.shopping_category_id, NULL_ID), POATLP.ip_category_id,
869 DECODE(PORL.item_id, POATLP.inventory_item_id,
870 DECODE(PORL.item_description, POATLP.description,
871 'N', 'Y'), 'Y'), 'Y'), -- Recreate Attribute
872 DECODE(
873 NVL(ICXM.shopping_category_id, NULL_ID), POATLP.ip_category_id,
874 DECODE(PORL.item_id, POATLP.inventory_item_id,
875 DECODE(PORL.item_description, POATLP.description,
876 'N', 'Y'), 'Y'), 'Y') -- Recreate Attribute TLP
877 FROM PO_REQEXPRESS_LINES_ALL PORL,
878 PO_ATTRIBUTE_VALUES_TLP POATLP,
879 ICX_CAT_PURCHASING_CAT_MAP_V ICXM
880 WHERE PORL.last_updated_program = g_R12_MIGRATION_PROGRAM
881 AND PORL.express_name = POATLP.req_template_name
882 AND PORL.sequence_num = POATLP.req_template_line_num
883 AND PORL.org_id = POATLP.org_id
884 AND PORL.last_update_date > POATLP.last_update_date
885 AND ( NVL(PORL.item_id, NULL_ID) <> POATLP.inventory_item_id
886 OR PORL.item_description <> POATLP.description)
887 AND POATLP.language = p_base_lang
888 AND PORL.CATEGORY_ID = ICXM.po_category_id(+)
889 UNION ALL
890 SELECT NULL_ID, -- PoLineId
891 express_name, sequence_num, -- TemplateName, TemplateId
892 PORL.ip_category_id, -- iP Category Id
893 NVL(PORL.item_id, NULL_ID), PORL.org_id, PORL.item_description,
894 NULL, -- Image
895 NULL, -- ImageUrl
896 'ITEM_TRANSLATION', 'N','Y' -- Attribute, Recreate Attrib, Recreate Attrib TLP
897 FROM PO_REQEXPRESS_LINES_ALL PORL,
898 MTL_SYSTEM_ITEMS_TL MTL,
899 FINANCIALS_SYSTEM_PARAMS_ALL FSP
900 WHERE PORL.last_updated_program = g_R12_MIGRATION_PROGRAM
901 AND item_id IS NOT NULL
902 AND PORL.item_id = MTL.inventory_item_id -- If item had changed then it would have been taken care by 'ITEM_ID' attribute change portion of this sql(it recreates the attributes)
903 AND PORL.org_id = FSP.org_id
904 AND FSP.inventory_organization_id = MTL.organization_id
905 AND MTL.language = MTL.source_lang
906 AND NOT EXISTS
907 (
908 SELECT 'Upgraded Lines with newly added item master translations'
909 FROM PO_ATTRIBUTE_VALUES_TLP POATLP
910 WHERE POATLP.req_template_name <> to_char(NULL_ID) -- Only look for Template records
911 AND PORL.express_name = POATLP.req_template_name
912 AND PORL.sequence_num = POATLP.req_template_line_num
913 AND PORL.org_id = POATLP.org_id
914 AND PORL.item_id = MTL.inventory_item_id
915 AND POATLP.language = MTL.language
916 )
917 UNION ALL
918 SELECT NULL_ID, -- PoLineId
919 req_template_name, req_template_line_num, -- TemplateName, TemplateId
920 ip_category_id, NVL(inventory_item_id, NULL_ID), org_id, null, -- description notneeded(null)
921 NULL, -- Image
922 NULL, -- ImageUrl
923 'LINE_DELETED', 'N','N' -- Attribute, Recreate Attrib, Recreate Attrib TLP
924 FROM PO_ATTRIBUTE_VALUES POAT
925 WHERE req_template_line_num <> NULL_ID --Bug#4865650
926 AND NOT EXISTS
927 -- Req Template lines that have been deleted but have attribute reference
928 -- (Need to be purged)
929 (
930 SELECT 'Req Template lines deleted'
931 FROM PO_REQEXPRESS_LINES_ALL PORL
932 WHERE PORL.express_name = POAT.req_template_name
933 AND PORL.sequence_num = POAT.req_template_line_num
934 AND PORL.org_id = POAT.org_id
935 );
936
937 l_api_name CONSTANT VARCHAR2(30) := 'process_modified_rt_lines';
938 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
939 l_progress VARCHAR2(3) := '000';
940
941 l_was_R12_upg_ever_run_before NUMBER := 0;
942 l_current_batch NUMBER; -- Bug 5468308: Track the progress of the script
943 BEGIN
944 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
945
946 l_progress := '020';
947
948 -- SQL What: If this is the first time the upgrade script has been run
949 -- then directly run the procedure PROCESS_NEW_RT_LINES.
950 -- SQL Why : The procedure PROCESS_MODIFIED_RT_LINES is applicable only
951 -- for those lines that have been modified since the last upgrade.
952 -- SQL Join: last_updated_program
953 SELECT count(*)
954 INTO l_was_R12_upg_ever_run_before
955 FROM PO_REQEXPRESS_LINES_ALL PORL
956 WHERE PORL.last_updated_program = g_R12_MIGRATION_PROGRAM
957 AND rownum=1;
958
959 l_progress := '030';
960
961 IF (l_was_R12_upg_ever_run_before = 0) THEN
962 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'early END'); END IF;
963 RETURN;
964 END IF;
965
966 l_progress := '040';
967 l_current_batch := 0;
968 LOOP
969 l_current_batch := l_current_batch + 1;
970 -- The following exception occurs if we dont do this check for existence:
971 -- ORA-06531: Reference to uninitialized collection.
972 IF (gPoLineIds.exists(1)) THEN
973 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Modified RTs; gPoLineIds.DELETE'); END IF;
974 gPoLineIds.DELETE;
975 gIpCategoryIds.DELETE;
976 gInvItemIds.DELETE;
977 gPoOrgIds.DELETE;
978 gItemDescriptions.DELETE;
979 gImages.DELETE;
980 gImageUrls.DELETE;
981 gUpdatedAttribute.DELETE;
982 gRecreateAttribRow.DELETE;
983 gRecreateAttribTLPRow.DELETE;
984 gPoReqTemplateNames.DELETE;
985 gPoReqTemplateLineIds.DELETE;
986 END IF;
987
988 -- Get the RT ids for which attribute rows need to be created
989 l_progress := '050';
990 OPEN getModifiedTemplatesCsr;
991
992 l_progress := '060';
993 -- Bug 5468308: Adding FND log messages at Unexpected level.
994 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
995 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_log_head||'.'||l_progress,
996 'current_batch='||l_current_batch);
997 END IF;
998
999 FETCH getModifiedTemplatesCsr
1000 BULK COLLECT into gPoLineIds,
1001 gPoReqTemplateNames, gPoReqTemplateLineIds,
1002 gIpCategoryIds, gInvItemIds,
1003 gPoOrgIds, gItemDescriptions,
1004 gImages,
1005 gImageUrls,
1006 gUpdatedAttribute,
1007 gRecreateAttribRow, gRecreateAttribTLPRow
1008 LIMIT p_batch_size;
1009
1010 l_progress := '070';
1011 CLOSE getModifiedTemplatesCsr;
1012
1013 l_progress := '080';
1014 EXIT WHEN gPoReqTemplateLineIds.COUNT = 0;
1015
1016 l_progress := '090';
1017
1018 -- SQL What: Update the PO_REQEXPRESS_LINES_ALL with ip_category_id
1019 -- SQL Why : Part of catalog upgrade requirements
1020 -- SQL Join: express_name, sequence_num, org_id
1021 -- We are specifically not updating the last_updated_by, login columns
1022 -- because we want to preserve that information(updating -2 to these
1023 -- columns is not useful when we already have the
1024 -- last_updated_program updated to g_R12_MIGRATION_PROGRAM
1025 FORALL i IN 1..gPoReqTemplateLineIds.COUNT
1026 UPDATE PO_REQEXPRESS_LINES_ALL PORL
1027 SET ip_category_id = gIpCategoryIds(i),
1028 last_updated_program = g_R12_MIGRATION_PROGRAM
1029 WHERE PORL.express_name = gPoReqTemplateNames(i)
1030 AND PORL.sequence_num = to_char(gPoReqTemplateLineIds(i))
1031 AND PORL.org_id = gPoOrgIds(i)
1032 AND gUpdatedAttribute(i) = 'IP_CATEGORY_ID';
1033
1034 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_REQEXPRESS_LINES_ALL rows updated='||SQL%rowcount); END IF;
1035
1036 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Deleting Attributes:Template'); END IF;
1037
1038 l_progress := '100';
1039 -- SQL What: For item change erase the attributes
1040 -- SQL Why : Instead of erasing the columns, just delete the rows from
1041 -- attributes table and recreate the row later.
1042 -- SQL Join: express_name, sequence_num, org_id
1043 FORALL i IN 1..gPoLineIds.COUNT
1044 DELETE FROM PO_ATTRIBUTE_VALUES
1045 WHERE req_template_name = gPoReqTemplateNames(i)
1046 AND req_template_line_num = to_char(gPoReqTemplateLineIds(i))
1047 AND org_id = gPoOrgIds(i)
1048 AND (gRecreateAttribRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');
1049
1050 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES rows deleted='||SQL%rowcount); END IF;
1051
1052 l_progress := '110';
1053
1054 -- SQL What: For item change erase the attributes ATLP
1055 -- SQL Why : Instead of erasing the columns, just delete the rows from
1056 -- attributes TLP table and recreate the row later.
1057 -- SQL Join: express_name, sequence_num, org_id
1058 FORALL i IN 1..gPoLineIds.COUNT
1059 DELETE FROM PO_ATTRIBUTE_VALUES_TLP
1060 WHERE req_template_name = gPoReqTemplateNames(i)
1061 AND req_template_line_num = to_char(gPoReqTemplateLineIds(i))
1062 AND org_id = gPoOrgIds(i)
1063 AND (gRecreateAttribTLPRow(i) = 'Y' OR gUpdatedAttribute(i) = 'LINE_DELETED');
1064
1065 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows deleted='||SQL%rowcount); END IF;
1066
1067 l_progress := '120';
1068
1069 -- SQL What: Update the PO attribute values TLP when the description has
1070 -- changed.
1071 -- SQL Why : Sync up the description
1072 -- SQL Join: express_name, sequence_num, org_id
1073 FORALL i in 1..gPoLineIds.COUNT
1074 UPDATE PO_ATTRIBUTE_VALUES_TLP
1075 SET description = gItemDescriptions(i),
1076 last_updated_program = g_R12_MIGRATION_PROGRAM
1077 WHERE req_template_name = gPoReqTemplateNames(i)
1078 AND req_template_line_num = to_char(gPoReqTemplateLineIds(i))
1079 AND org_id = gPoOrgIds(i)
1080 AND gUpdatedAttribute(i) = 'DESCRIPTION';
1081
1082 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_ATTRIBUTE_VALUES_TLP rows updated='||SQL%rowcount); END IF;
1083
1084 l_progress := '130';
1085
1086 -- Lines for which attributes records need to be created are in
1087 -- gPoReqTemplateNames/gPoReqTemplateLineIds tables
1088 CREATE_LINE_ATTRIBUTES(p_batch_size, p_base_lang);
1089
1090 l_progress := '140';
1091 -- Lines for which attributes TLP records need to be created are in
1092 -- gPoReqTemplateNames/gPoReqTemplateLineIds tables
1093 CREATE_LINE_ATTRIBUTES_TLP(p_batch_size, p_base_lang);
1094
1095 -- Commit after every batch
1096 COMMIT;
1097 END LOOP;
1098
1099 l_progress := '150';
1100 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 ROLLBACK;
1104 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1105 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1106 END process_modified_rt_lines;
1107
1108 --------------------------------------------------------------------------------
1109 --Start of Comments
1110 --Name: process_new_rt_lines
1111 --Pre-reqs:
1112 -- The datamodel changes for Unified Catlog Upgrade should have been applied.
1113 --Modifies:
1114 -- a) FND_MSG_PUB on unhandled exceptions.
1115 --Locks:
1116 -- None.
1117 --Function:
1118 -- Create Attributes record for Req Templates that have been created after
1119 -- last run of the Pass 1 upgrade program.
1120 -- This API should be called during the upgrade phase only.
1121 --Parameters:
1122 --IN:
1123 -- None
1124 --OUT:
1125 -- None
1126 --End of Comments
1127 --------------------------------------------------------------------------------
1128 PROCEDURE process_new_rt_lines(p_batch_size IN NUMBER,
1129 p_base_lang IN FND_LANGUAGES.language_code%TYPE)
1130 IS
1131 -- SQL What: Cursor to get RT lines that have not been upgraded
1132 -- SQL Why : To update the attributes
1133 -- SQL Join: last_updated_program
1134 CURSOR getNonUpgradedTemplatesCsr IS
1135 SELECT NULL_ID, -- PoLineId
1136 express_name, sequence_num, -- TemplateName, TemplateId
1137 NVL(ICXM.SHOPPING_CATEGORY_ID, NULL_ID), -- ip_category_id
1138 NVL(PORL.item_id, NULL_ID), PORL.org_id, PORL.item_description,
1139 NULL, -- Image
1140 NULL, -- ImageUrl
1141 NULL, 'Y','Y' -- Attribute, Recreate Attrib, Recreate Attrib TLP
1142 FROM PO_REQEXPRESS_LINES_ALL PORL,
1143 ICX_CAT_PURCHASING_CAT_MAP_V ICXM
1144 WHERE last_updated_program is null
1145 AND PORL.CATEGORY_ID = ICXM.po_category_id(+) ;
1146
1147 l_api_name CONSTANT VARCHAR2(30) := 'process_new_rt_lines';
1148 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1149 l_progress VARCHAR2(3) := '000';
1150
1151 l_current_batch NUMBER; -- Bug 5468308: Track the progress of the script
1152 BEGIN
1153 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1154
1155 l_progress := '030';
1156 l_current_batch := 0;
1157 LOOP
1158 l_current_batch := l_current_batch + 1;
1159
1160 -- The following exception occurs if we dont do this check for existence:
1161 -- ORA-06531: Reference to uninitialized collection.
1162 IF (gPoLineIds.exists(1)) THEN
1163 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'New RTs; gPoLineIds.DELETE'); END IF;
1164 gPoLineIds.DELETE;
1165 gIpCategoryIds.DELETE;
1166 gInvItemIds.DELETE;
1167 gPoOrgIds.DELETE;
1168 gItemDescriptions.DELETE;
1169 gImages.DELETE;
1170 gImageUrls.DELETE;
1171 gUpdatedAttribute.DELETE;
1172 gRecreateAttribRow.DELETE;
1173 gRecreateAttribTLPRow.DELETE;
1174 gPoReqTemplateNames.DELETE;
1175 gPoReqTemplateLineIds.DELETE;
1176 END IF;
1177
1178 -- Get the Requisition Template Lines that have not been updated yet
1179 l_progress := '040';
1180 OPEN getNonUpgradedTemplatesCsr;
1181
1182 l_progress := '050';
1183 -- Bug 5468308: Adding FND log messages at Unexpected level.
1184 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1185 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_log_head||'.'||l_progress,
1186 'current_batch='||l_current_batch);
1187 END IF;
1188
1189 FETCH getNonUpgradedTemplatesCsr
1190 BULK COLLECT into gPoLineIds,
1191 gPoReqTemplateNames, gPoReqTemplateLineIds,
1192 gIpCategoryIds, gInvItemIds,
1193 gPoOrgIds, gItemDescriptions,
1194 gImages,
1195 gImageUrls,
1196 gUpdatedAttribute,
1197 gRecreateAttribRow, gRecreateAttribTLPRow
1198 LIMIT p_batch_size;
1199
1200 l_progress := '060';
1201 CLOSE getNonUpgradedTemplatesCsr;
1202
1203 l_progress := '070';
1204 EXIT WHEN gPoReqTemplateLineIds.COUNT = 0;
1205
1206 l_progress := '080';
1207
1208 -- SQL What: Update the PO_REQEXPRESS_LINES_ALL with ip_category_id
1209 -- SQL Why : Part of catalog upgrade requirements
1210 -- SQL Join: express_name, sequence_num, org_id
1211 -- We are specifically not updating the last_updated_by, login columns
1212 -- because we want to preserve that information(updating -2 to these
1213 -- columns is not useful when we already have the
1214 -- last_updated_program updated to g_R12_MIGRATION_PROGRAM
1215 FORALL i IN 1..gPoReqTemplateLineIds.COUNT
1216 UPDATE PO_REQEXPRESS_LINES_ALL PORL
1217 SET ip_category_id = gIpCategoryIds(i),
1218 last_updated_program = g_R12_MIGRATION_PROGRAM
1219 WHERE express_name = gPoReqTemplateNames(i)
1220 AND sequence_num = to_char(gPoReqTemplateLineIds(i))
1221 AND org_id = gPoOrgIds(i);
1222
1223 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of PO_REQEXPRESS_LINES_ALL rows updated='||SQL%rowcount); END IF;
1224
1225 l_progress := '090';
1226
1227 -- Lines for which attributes need to be created are in gPoLineIds
1228 CREATE_LINE_ATTRIBUTES(p_batch_size, p_base_lang);
1229
1230 l_progress := '100';
1231 -- Lines for which attributes TLP records need to be created are in gPoLineIds
1232 CREATE_LINE_ATTRIBUTES_TLP(p_batch_size, p_base_lang);
1233
1234 l_progress := '110';
1235 -- Commit after every batch
1236 COMMIT;
1237 END LOOP;
1238
1239 l_progress := '120';
1240 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 ROLLBACK;
1244 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1245 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1246 END process_new_rt_lines;
1247
1248 /*
1249 FUNCTION logAndReturnAudSid
1250 l_upgrade_job_number NUMBER;
1251 l_err_loc NUMBER;
1252 begin
1253 l_err_loc := 100;
1254 SELECT NVL(MIN(job_number), 1)
1255 INTO l_upgrade_job_number
1256 FROM icx_cat_r12_upgrade_jobs;
1257
1258 l_err_loc := 110;
1259 IF (l_upgrade_job_number > 0) THEN
1260 l_upgrade_job_number := ICX_CAT_UTIL_PVT.g_upgrade_user;
1261 ELSE
1262 l_upgrade_job_number := l_upgrade_job_number - 1;
1263 END IF;
1264
1265 l_err_loc := 120;
1266
1267 end;
1268 */
1269
1270 --
1271 -- Set debug on procedure - used only for debugging
1272 --
1273 PROCEDURE debug_profiles_on
1274 IS
1275 BEGIN
1276 FND_PROFILE.put('PO_SET_DEBUG_WORKFLOW_ON', 'Y');
1277
1278 fnd_profile.put('AFLOG_ENABLED', 'Y');
1279 fnd_profile.put('AFLOG_MODULE', 'po.plsql.%,icx%'); -- note: comma-delimited list
1280 fnd_profile.put('AFLOG_LEVEL', 1);
1281 fnd_profile.put('AFLOG_FILENAME', '');
1282 fnd_log_repository.init;
1283
1284 END debug_profiles_on;
1285
1286
1287 --------------------------------------------------------------------------------
1288 --Start of Comments
1289 --Name: upgrade_existing_docs
1290 --Pre-reqs:
1291 -- None: This is a dummy procedure(implementd for iP)
1292 --Modifies:
1293 -- None: This is a dummy procedure(implementd for iP)
1294 --Locks:
1295 -- None.
1296 --Function:
1297 -- Dummy API, since iP Maintains the same codeline for 11.5.10 and R12
1298 -- they have the call to upgrade_existing_docs in the common code
1299 -- In PO this functionality is implemented in poxujpoh.sql, poxukpol.sql and
1300 -- poxukrt.sql. So we dont need to implement any logic for this API
1301 -- in R12. Having it dummy so that the iP upgrade code will not throw
1302 -- compilation error
1303 --Parameters:
1304 -- Not applicable: Dummy procedure
1305 --End of Comments
1306 --------------------------------------------------------------------------------
1307 PROCEDURE upgrade_existing_docs (
1308 p_batch_size IN NUMBER
1309 , x_return_status OUT NOCOPY VARCHAR2)
1310 IS
1311 BEGIN
1312 -- Dummy API, since iP Maintains the same codeline for 11.5.10 and R12
1313 -- they have the call to upgrade_existing_docs in the common code
1314 -- In PO this functionality is implemented in poxujpoh.sql, poxukpol.sql and
1315 -- poxukrt.sql. So we dont need to implement any logic for this API
1316 -- in R12. Having it dummy so that the iP upgrade code will not throw
1317 -- compilation error
1318 x_return_status := FND_API.G_RET_STS_SUCCESS;
1319 END upgrade_existing_docs;
1320
1321 END PO_R12_CAT_UPG_EXISTING_DOCS;
[Home] [Help]