1 PACKAGE BODY PO_R12_CAT_UPG_VAL_PVT AS
2 /* $Header: PO_R12_CAT_UPG_VAL_PVT.plb 120.15 2006/12/19 11:09:07 bao noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_VAL_PVT';
5 g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
6
7 g_debug BOOLEAN := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
8 g_err_num NUMBER := PO_R12_CAT_UPG_PVT.g_application_err_num;
9
10 -- Value of PO_LINE_ID and REQ_TEMPLATE_LINE_NUM if they are not
11 -- required in Attr/TLP tables.
12 g_NOT_REQUIRED_ID CONSTANT NUMBER := PO_R12_CAT_UPG_PVT.g_NOT_REQUIRED_ID;
13 -- Value of REQ_TEMPLATE_NAME if it is not required in Attr/TLP tables.
14 g_NOT_REQUIRED_REQ_TEMPLATE CONSTANT VARCHAR2(30) := PO_R12_CAT_UPG_PVT.g_NOT_REQUIRED_REQ_TEMPLATE ;
15
16 -- If the value of a column (vendor_id, site_id, etc.) in the interface table
17 -- is -2, then treat it as NULL for validation routines.
18 g_NULL_COLUMN_VALUE NUMBER := -2;
19
20 --------------------------------------------------------------------------------
21 --Start of Comments
22 --Name: validate_org_ids
23 --Pre-reqs:
24 -- The iP catalog data is populated in input pl/sql tables.
25 --Modifies:
26 -- a) Interface tables.
27 -- b) FND_MSG_PUB on unhandled exceptions.
28 --Locks:
29 -- None.
30 --Function:
31 -- Validates org_id. It can not be NULL or -2.
32 -- (For Operating Unit (org_id), iProc defined a values of -2 for 'All Orgs'.
33 -- This value will now fail validation in migration program.)
34 -- This procedure also validates the org_id against the HR_OPERATING_UNITS
35 -- table. The org_id must exist in this table and must not be end-dated.
36 --
37 --Parameters:
38 --IN:
39 --p_batch_id
40 -- Key used to identify all records to be processed in the interface tables.
41 --p_batch_size
42 -- The size of the batch that should be processed in each commit cycle.
43 --p_validate_only_mode
44 -- If set to Y, the cascading of errors to lower levels will not happen.
45 --OUT:
46 -- None
47 --End of Comments
48 --------------------------------------------------------------------------------
49 PROCEDURE validate_org_ids
50 (
51 p_batch_id IN NUMBER
52 , p_batch_size IN NUMBER
53 , p_validate_only_mode IN VARCHAR2
54 )
55 IS
56 l_api_name CONSTANT VARCHAR2(30) := 'validate_org_ids';
57 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
58 l_progress VARCHAR2(3) := '000';
59
60 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
61 l_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
62
63 l_err_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
64 l_err_org_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
65 i NUMBER;
66
67 l_ou_name HR_ALL_ORGANIZATION_UNITS_TL.name%TYPE;
68
69 -- iProc defined value for org_id if item is valid for All OU's.
70 g_OU_REQD CONSTANT NUMBER := -2;
71
72 CURSOR load_org_ids_csr(p_batch_id NUMBER) IS
73 SELECT interface_header_id
74 , org_id
75 FROM PO_HEADERS_INTERFACE
76 WHERE batch_id = p_batch_id
77 AND process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_NEW;
78
79 BEGIN
80 l_progress := '010';
81 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
82
83 OPEN load_org_ids_csr(p_batch_id);
84
85 l_progress := '020';
86 LOOP
87 BEGIN -- block to handle SNAPSHOT_TOO_OLD exception
88
89 l_progress := '030';
90 FETCH load_org_ids_csr
91 BULK COLLECT INTO l_interface_header_ids
92 , l_org_ids
93 LIMIT p_batch_size;
94
95 l_progress := '040';
96
97 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids.COUNT='||l_interface_header_ids.COUNT); END IF;
98
99 IF (l_interface_header_ids.COUNT > 0) THEN
100 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids(1)='||l_interface_header_ids(1)); END IF;
101 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_org_ids(1)='||l_org_ids(1)); END IF;
102 END IF;
103
104 l_progress := '050';
105 EXIT WHEN l_interface_header_ids.COUNT = 0;
106
107 -- ECO bug 5584556: Add new messages
108 l_progress := '060';
109 -- SQL What: Bulk validate org_id -- it should not be NULL or -2.
110 -- Also validates the org_id against HR_OPERATING_UNITS.
111 -- Gets the errored rows into GT table.
112 -- SQL Why : It will be used to mark the record in interface tables as rejected.
113 -- SQL Join: interface_header_id
114 FORALL i IN 1 .. l_interface_header_ids.COUNT
115 UPDATE PO_HEADERS_INTERFACE
116 SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
117 WHERE interface_header_id = l_interface_header_ids(i)
118 AND (l_org_ids(i) IS NULL
119 OR l_org_ids(i) = g_OU_REQD -- where g_OU_REQD is -2 (iProc defined this value)
120 OR NOT EXISTS
121 (SELECT 'ORG_ID exists'
122 FROM HR_ALL_ORGANIZATION_UNITS HAOU
123 WHERE HAOU.organization_id = l_org_ids(i)
124 ))
125 RETURNING interface_header_id, org_id
126 BULK COLLECT INTO l_err_interface_header_ids, l_err_org_ids;
127
128 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
129
130 l_progress := '070';
131 -- Mark the error records
132 FOR i IN 1 .. l_err_interface_header_ids.COUNT
133 LOOP
134 IF (l_err_org_ids(i) = g_OU_REQD) THEN
135 -- ICX_CAT_ALL_BUYERS_DEPRECATED:
136 -- "Provide an Operating Unit. 'All Buyers' has been deprecated."
137 PO_R12_CAT_UPG_UTL.add_fatal_error(
138 p_interface_header_id => l_err_interface_header_ids(i),
139 --p_error_message_name => 'PO_R12_CAT_UPG_OU_REQD',
140 p_error_message_name => 'ICX_CAT_ALL_BUYERS_DEPRECATED',
141 p_table_name => 'PO_HEADERS_INTERFACE',
142 p_column_name => 'ORG_ID',
143 p_column_value => l_err_org_ids(i) -- debug purposes only, not used in the msg
144 );
145 ELSE -- if org_id is NULL or if ID does not exist in table.
146 -- ICX_CAT_OU_REQD:
147 -- "Operating unit is missing."
148 PO_R12_CAT_UPG_UTL.add_fatal_error(
149 p_interface_header_id => l_err_interface_header_ids(i),
150 p_error_message_name => 'ICX_CAT_OU_REQD',
151 p_table_name => 'PO_HEADERS_INTERFACE',
152 p_column_name => 'ORG_ID',
153 p_column_value => l_err_org_ids(i)
154 );
155 END IF;
156
157 l_progress := '080';
158 -- Cascade errors down to lower levels
159 -- Skip cascading of errors in Validate Only mode (iP Requirement - all errors should be reported)
160 IF (p_validate_only_mode <> FND_API.G_TRUE) THEN
161 PO_R12_CAT_UPG_UTL.reject_headers_intf('INTERFACE_HEADER_ID', l_err_interface_header_ids, 'Y');
162 END IF;
163 END LOOP;
164 -- ECO bug 5584556: End
165
166 l_progress := '090';
167 -- SQL What: Bulk validate org_id.
168 -- Validate the org_id against HR_OPERATING_UNITS.
169 -- Gets the errored rows into GT table.
170 -- SQL Why : It will be used to mark the record in interface tables as rejected.
171 -- SQL Join: interface_header_id
172 FORALL i IN 1 .. l_interface_header_ids.COUNT
173 UPDATE PO_HEADERS_INTERFACE
174 SET process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
175 WHERE interface_header_id = l_interface_header_ids(i)
176 -- ECO bug 5584556: Skip the validations covered above
177 AND l_org_ids(i) IS NOT NULL
178 AND l_org_ids(i) <> g_OU_REQD -- where g_OU_REQD is -2 (iProc defined this value)
179 AND EXISTS
180 (SELECT 'ORG_ID exists'
181 FROM HR_ALL_ORGANIZATION_UNITS HAOU
182 WHERE HAOU.organization_id = l_org_ids(i)
183 )
184 -- ECO bug 5584556: End
185 AND (
186 NOT EXISTS
187 (SELECT 'Valid Operating Unit ID'
188 FROM HR_OPERATING_UNITS HROU
189 WHERE HROU.organization_id = l_org_ids(i)
190 -- Bug 5060582: Dont need the date checks
191 --AND sysdate BETWEEN
192 -- nvl(HROU.date_from, sysdate-1)
193 -- AND nvl(HROU.date_to, sysdate+1)
194 )
195 -- ECO bug 5584556: Add new messages
196 -- This check is done as part of UT'ing this ECO.
197 -- We found a case where the PSP row does not exist for the org_id.
198 -- The migration program would die for such cases. The following query
199 -- is copied from init_sys_parameters() where it was failing.
200 OR
201 NOT EXISTS
202 ( SELECT 'Valid OU references in FSP, PSP, SOB and RCV'
203 FROM FINANCIALS_SYSTEM_PARAMS_ALL FSPA,
204 GL_SETS_OF_BOOKS SOB,
205 PO_SYSTEM_PARAMETERS_ALL PSPA,
206 RCV_PARAMETERS RCV
207 WHERE FSPA.set_of_books_id = SOB.set_of_books_id
208 AND RCV.organization_id (+) = FSPA.inventory_organization_id
209 AND PSPA.org_id = l_org_ids(i)
210 AND FSPA.org_id = l_org_ids(i)
211 )
212 -- ECO bug 5584556: End
213 )
214 RETURNING interface_header_id, org_id
215 BULK COLLECT INTO l_err_interface_header_ids, l_err_org_ids;
216
217 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
218
219 l_progress := '100';
220 -- Mark the error records
221 FOR i IN 1 .. l_err_interface_header_ids.COUNT
222 LOOP
223 -- ECO bug 5584556: id-to-name conversion for new message
224 -- Get the Operating Unit name
225 BEGIN
226 SELECT name
227 INTO l_ou_name
228 FROM HR_ALL_ORGANIZATION_UNITS_TL HAOUTL
229 WHERE HAOUTL.organization_id = l_err_org_ids(i)
230 AND language = userenv('LANG');
231 EXCEPTION
232 WHEN OTHERS THEN
233 l_ou_name := to_char(l_err_org_ids(i));
234 END;
235 -- ECO bug 5584556: End
236
237 -- ICX_CAT_INVALID_OU:
238 -- "Operating Unit (VALUE) is invalid or inactive."
239 PO_R12_CAT_UPG_UTL.add_fatal_error(
240 p_interface_header_id => l_err_interface_header_ids(i),
241 --p_error_message_name => 'PO_R12_CAT_UPG_INVALID_OU',
242 p_error_message_name => 'ICX_CAT_INVALID_OU',
243 p_table_name => 'PO_HEADERS_INTERFACE',
244 p_column_name => 'ORG_ID',
245 p_column_value => l_ou_name, -- ECO bug 5584556
246 p_token1_name => 'VALUE',
247 p_token1_value => l_err_org_ids(i)
248 );
249
250 l_progress := '110';
251 -- Cascade errors down to lower levels
252 -- Skip cascading of errors in Validate Only mode (iP Requirement - all errors should be reported)
253 IF (p_validate_only_mode <> FND_API.G_TRUE) THEN
254 PO_R12_CAT_UPG_UTL.reject_headers_intf('INTERFACE_HEADER_ID', l_err_interface_header_ids, 'Y');
255 END IF;
256 END LOOP;
257
258 l_progress := '120';
259 COMMIT;
260
261 l_progress := '130';
262 IF (l_interface_header_ids.COUNT < p_batch_size) THEN
263 EXIT;
264 END IF;
265 l_progress := '140';
266 EXCEPTION
267 WHEN g_SNAPSHOT_TOO_OLD THEN
268 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_org_ids_csr'); END IF;
269
270 -- Commit and re-open the cursor
271 l_progress := '150';
272 COMMIT;
273
274 l_progress := '160';
275 CLOSE load_org_ids_csr;
276
277 l_progress := '170';
278 OPEN load_org_ids_csr(p_batch_id);
279 l_progress := '180';
280 END; -- block to handle SNAPSHOT_TOO_OLD exception
281 END LOOP; -- batch loop
282
283 l_progress := '190';
284 IF (load_org_ids_csr%ISOPEN) THEN
285 CLOSE load_org_ids_csr;
286 END IF;
287
288 l_progress := '200';
289 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
290 EXCEPTION
291 WHEN OTHERS THEN
292 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
293 IF (load_org_ids_csr%ISOPEN) THEN
294 CLOSE load_org_ids_csr;
295 END IF;
296 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
297 END validate_org_ids;
298
299 --------------------------------------------------------------------------------
300 --Start of Comments
301 --Name: validate_expiration_date
302 --Pre-reqs:
303 -- The iP catalog data is populated in input pl/sql tables.
304 --Modifies:
305 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
306 -- b) FND_MSG_PUB on unhandled exceptions.
307 --Locks:
308 -- None.
309 --Function:
310 -- End date cannot be earlier than start date. This procedure validates this.
311 --Parameters:
312 --IN/OUT:
313 --p_headers_rec
314 -- A record of plsql tables containing a batch of headers. If this validation
315 -- fails, the 'has_errors' column is set to 'Y'.
316 --OUT:
317 --End of Comments
318 --------------------------------------------------------------------------------
319 PROCEDURE validate_expiration_date
320 (
321 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
322 )
323 IS
324 l_api_name CONSTANT VARCHAR2(30) := 'validate_expiration_date';
325 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
326 l_progress VARCHAR2(3) := '000';
327
328 i NUMBER;
329 BEGIN
330 l_progress := '010';
331 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
332
333 FOR i IN 1 .. p_headers_rec.expiration_date.COUNT
334 LOOP
335 l_progress := '020';
336 IF (--p_headers_rec.has_errors(i) = 'N' AND
337 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create AND --Bug#5018883
338 p_headers_rec.effective_date(i) IS NOT NULL AND
339 p_headers_rec.expiration_date(i) IS NOT NULL AND
340 p_headers_rec.effective_date(i) > p_headers_rec.expiration_date(i)) THEN
341
342 l_progress := '030';
343 p_headers_rec.has_errors(i) := 'Y';
344
345 -- Add error message into INTERFACE_ERRORS table
346 -- PO_PDOI_INVALID_START_DATE:
347 -- "Effective Date (VALUE =VALUE) specified should be less than the end date specified."
348 PO_R12_CAT_UPG_UTL.add_fatal_error(
349 p_interface_header_id => p_headers_rec.interface_header_id(i),
350 p_error_message_name => 'PO_PDOI_INVALID_START_DATE',
351 p_table_name => 'PO_HEADERS_INTERFACE',
352 p_column_name => 'EXPIRATION_DATE',
353 p_column_value => p_headers_rec.expiration_date(i),
354 p_token1_name => 'VALUE',
355 p_token1_value => p_headers_rec.effective_date(i)
356 );
357 END IF;
358 END LOOP;
359
360 l_progress := '040';
361 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
362 EXCEPTION
363 WHEN OTHERS THEN
364 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
365 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
366 END validate_expiration_date;
367
368 --------------------------------------------------------------------------------
369 --Start of Comments
370 --Name: validate_buyer
371 --Pre-reqs:
372 -- The iP catalog data is populated in input pl/sql tables.
373 --Modifies:
374 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
375 -- b) FND_MSG_PUB on unhandled exceptions.
376 --Locks:
377 -- None.
378 --Function:
379 -- agent_id can not be null.
380 --Parameters:
381 --IN/OUT:
382 --p_headers_rec
383 -- A record of plsql tables containing a batch of headers. If this validation
384 -- fails, the 'has_errors' column is set to 'Y'.
385 --OUT:
386 --End of Comments
387 --------------------------------------------------------------------------------
388 PROCEDURE validate_buyer
389 (
390 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
391 )
392 IS
393 l_api_name CONSTANT VARCHAR2(30) := 'validate_buyer';
394 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
395 l_progress VARCHAR2(3) := '000';
396
397 i NUMBER;
398 BEGIN
399 l_progress := '010';
400 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
401
402 FOR i IN 1 .. p_headers_rec.agent_id.COUNT
403 LOOP
404 l_progress := '020';
405 IF (--p_headers_rec.has_errors(i) = 'N' AND
406 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create AND --Bug#5018883
407 p_headers_rec.agent_id(i) IS NULL) THEN
408
409 l_progress := '030';
410 p_headers_rec.has_errors(i) := 'Y';
411
412 -- Add error message into INTERFACE_ERRORS table
413 -- ICX_CAT_BUYER_REQD:
414 -- "The system cannot obtain the default value for the buyer."
415 PO_R12_CAT_UPG_UTL.add_fatal_error(
416 p_interface_header_id => p_headers_rec.interface_header_id(i),
417 p_error_message_name => 'ICX_CAT_BUYER_REQD', -- ECO bug 5584556: changed from ICX_CAT_NO_BUYER
418 p_table_name => 'PO_HEADERS_INTERFACE',
419 p_column_name => 'AGENT_ID',
420 p_column_value => p_headers_rec.agent_id(i),
421 p_token1_name => 'VALUE',
422 p_token1_value => p_headers_rec.agent_id(i)
423 );
424 END IF;
425 END LOOP;
426
427 l_progress := '040';
428 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
429 EXCEPTION
430 WHEN OTHERS THEN
431 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
432 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
433 END validate_buyer;
434
435 --------------------------------------------------------------------------------
436 --Start of Comments
437 --Name: validate_currency_code
438 --Pre-reqs:
439 -- The iP catalog data is populated in input pl/sql tables.
440 --Modifies:
441 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
442 -- b) FND_MSG_PUB on unhandled exceptions.
443 --Locks:
444 -- None.
445 --Function:
446 -- Validates currency_code not null and against FND_CURRENCIES.
447 --Parameters:
448 --IN:
449 --p_key
450 -- Key used to access records in PO_SESSION_GT table.
451 --IN/OUT:
452 --p_headers_rec
453 -- A record of plsql tables containing a batch of headers. If this validation
454 -- fails, the 'has_errors' column is set to 'Y'.
455 --OUT:
456 --End of Comments
457 --------------------------------------------------------------------------------
458 PROCEDURE validate_currency_code
459 (
460 p_key IN NUMBER,
461 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
462 )
463 IS
464 l_api_name CONSTANT VARCHAR2(30) := 'validate_currency_code';
465 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
466 l_progress VARCHAR2(3) := '000';
467
468 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
469
470 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
471 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
472 l_err_currency_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15;
473
474 l_index NUMBER;
475 i NUMBER;
476 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
477 BEGIN
478 l_progress := '010';
479 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
480
481 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.currency_code.COUNT);
482
483 IF (p_headers_rec.currency_code.COUNT > 0) THEN
484 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.currency_code(1)='||p_headers_rec.currency_code(1)); END IF;
485 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
486 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
487 END IF;
488
489 -- ECO bug 5584556: Add new messages
490 FOR i IN 1 .. p_headers_rec.currency_code.COUNT
491 LOOP
492 IF ( (p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create) AND
493 (p_headers_rec.currency_code(i) IS NULL) ) THEN
494 p_headers_rec.has_errors(i) := 'Y';
495
496 -- Add error message into INTERFACE_ERRORS table
497 -- ICX_CAT_CURRENCY_REQD:
498 -- "Currency code is missing."
499 PO_R12_CAT_UPG_UTL.add_fatal_error(
500 p_interface_header_id => l_interface_header_ids(i),
501 p_error_message_name => 'ICX_CAT_CURRENCY_REQD',
502 p_table_name => 'PO_HEADERS_INTERFACE',
503 p_column_name => 'CURRENCY_CODE',
504 p_column_value => p_headers_rec.currency_code(i)
505 );
506 END IF;
507 END LOOP;
508 -- ECO bug 5584556: End
509
510 l_progress := '020';
511 -- SQL What: Bulk validate currency_code not null and against FND_CURRENCIES.
512 -- Get the errored rows into GT table.
513 -- SQL Why : It will be used to mark the record in plsql table as error.
514 -- SQL Join: currency_code
515 FORALL i IN 1 .. p_headers_rec.currency_code.COUNT
516 INSERT INTO PO_SESSION_GT(key,
517 num1,
518 num2,
519 index_char1)
520 SELECT p_key
521 , l_subscript_array(i)
522 , p_headers_rec.interface_header_id(i)
523 , p_headers_rec.currency_code(i)
524 FROM DUAL
525 WHERE --p_headers_rec.has_errors(i) = 'N'
526 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
527 -- ECO bug 5584556: Start
528 -- The following validation check is already done above.
529 -- So adding these additional where-clauses to skip those cases.
530 AND p_headers_rec.currency_code(i) IS NOT NULL
531 -- ECO bug 5584556: End
532 AND NOT EXISTS(SELECT 1
533 FROM FND_CURRENCIES CUR
534 WHERE p_headers_rec.currency_code(i) = CUR.currency_code
535 AND CUR.enabled_flag = 'Y'
536 AND sysdate BETWEEN
537 nvl(CUR.start_date_active, sysdate-1)
538 AND nvl(CUR.end_date_active, sysdate+1));
539
540 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
541
542 l_progress := '030';
543 -- SQL What: Transfer from session GT table to local arrays
544 -- SQL Why : It will be used to mark the error records.
545 -- SQL Join: key
546 DELETE FROM PO_SESSION_GT
547 WHERE key = p_key
548 RETURNING num1, num2, index_char1
549 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_currency_code_list;
550
551 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
552
553 l_progress := '040';
554 -- Mark the error records
555 FOR i IN 1 .. l_indexes.COUNT
556 LOOP
557 l_index := l_indexes(i);
558 p_headers_rec.has_errors(l_index) := 'Y';
559
560 -- Add error message into INTERFACE_ERRORS table
561 -- ICX_CAT_INVALID_CURRENCY:
562 -- "Currency code (VALUE) is inactive or invalid."
563 PO_R12_CAT_UPG_UTL.add_fatal_error(
564 p_interface_header_id => l_interface_header_ids(i),
565 --p_error_message_name => 'PO_PDOI_INVALID_CURRENCY',
566 p_error_message_name => 'ICX_CAT_INVALID_CURRENCY',
567 p_table_name => 'PO_HEADERS_INTERFACE',
568 p_column_name => 'CURRENCY_CODE',
569 p_column_value => l_err_currency_code_list(i),
570 p_token1_name => 'VALUE',
571 p_token1_value => l_err_currency_code_list(i)
572 );
573 END LOOP;
574
575 l_progress := '050';
576 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
577 EXCEPTION
578 WHEN OTHERS THEN
579 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
580 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
581 END validate_currency_code;
582
583 --------------------------------------------------------------------------------
584 --Start of Comments
585 --Name: validate_vendor_info
586 --Pre-reqs:
587 -- The iP catalog data is populated in input pl/sql tables.
588 --Modifies:
589 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
590 -- b) FND_MSG_PUB on unhandled exceptions.
591 --Locks:
592 -- None.
593 --Function:
594 -- validate vendorId is Not Null
595 -- validate vendorSiteId is Not Null
596 -- validate vendor_id using po_suppliers_val_v
597 -- validate vendor_site_id using po_supplier_sites_val_v
598 -- validate vendor_contact_id using po_vendor_contacts
599 -- validate vendor site CCR if approval status is APPROVED.
600 --Parameters:
601 --IN:
602 --p_key
603 -- Key used to access records in PO_SESSION_GT table.
604 --IN/OUT:
605 --p_headers_rec
606 -- A record of plsql tables containing a batch of headers. If this validation
607 -- fails, the 'has_errors' column is set to 'Y'.
608 --OUT:
609 --End of Comments
610 --------------------------------------------------------------------------------
611 PROCEDURE validate_vendor_info
612 (
613 p_key IN NUMBER,
614 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
615 )
616 IS
617 l_api_name CONSTANT VARCHAR2(30) := 'validate_vendor_info';
618 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
619 l_progress VARCHAR2(3) := '000';
620
621 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
622
623 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
624 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
625 l_vendor_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
626 l_vendor_name_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR240;
627 l_vendor_site_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
628 l_vendor_site_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR15;
629 l_vendor_contact_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
630
631 l_return_status VARCHAR2(1);
632 l_msg_count NUMBER;
633 l_msg_data VARCHAR2(2000);
634 l_ccr_status VARCHAR2(1);
635 l_error_code NUMBER;
636
637 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
638 l_index NUMBER;
639 i NUMBER;
640
641 -- If the profile option 'Enable Transaction Code' is set to 'Yes',
642 -- then it is a Federal instance.
643 l_federal_instance VARCHAR2(1) :=
644 NVL(FND_PROFILE.value('USSGL_OPTION'), 'N');
645 x_temp_val BOOLEAN;
646 BEGIN
647 l_progress := '010';
648 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
649
650 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.vendor_id.COUNT);
651
652 ----------------------------------------------------------------------------
653 -- Validate vendor_id
654 ----------------------------------------------------------------------------
655
656 -- ECO bug 5584556: Add new messages
657 l_progress := '020';
658 -- SQL What: Bulk validate vendor_id. Check that the ID is not NULL and it exists
659 -- in PO_VENDORS table.
660 -- Insert the error rows into GT table.
661 -- SQL Why : It will be used to mark the record in plsql table as error.
662 -- SQL Join: vendor_id
663 FORALL i IN 1 .. p_headers_rec.vendor_id.COUNT
664 INSERT INTO PO_SESSION_GT(key,
665 num1,
666 num2,
667 num3,
668 char1)
669 SELECT p_key
670 , l_subscript_array(i)
671 , p_headers_rec.interface_header_id(i)
672 , p_headers_rec.vendor_id(i)
673 , p_headers_rec.vendor_name(i)
674 FROM DUAL
675 WHERE --p_headers_rec.has_errors(i) = 'N'
676 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
677 AND (p_headers_rec.vendor_id(i) IS NULL
678 OR p_headers_rec.vendor_id(i) = g_NULL_COLUMN_VALUE -- -2
679 OR NOT EXISTS(SELECT 1
680 FROM PO_VENDORS PV
681 WHERE p_headers_rec.vendor_id(i) = PV.vendor_id));
682
683 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
684
685 l_progress := '030';
686 -- SQL What: Transfer from session GT table to local arrays
687 -- SQL Why : It will be used to mark the error records.
688 -- SQL Join: key
689 DELETE FROM PO_SESSION_GT
690 WHERE key = p_key
691 RETURNING num1, num2, num3, char1
692 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_id_list, l_vendor_name_list;
693
694 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
695
696 l_progress := '040';
697 -- Mark the error records
698 FOR i IN 1 .. l_indexes.COUNT
699 LOOP
700 l_index := l_indexes(i);
701 p_headers_rec.has_errors(l_index) := 'Y';
702
703 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_id does not exist'||', vendor_id='||l_vendor_id_list(i)); END IF;
704
705 -- Add error message into INTERFACE_ERRORS table
706 -- ICX_CAT_SUPPLIER_REQD:
707 -- "Supplier is missing."
708 PO_R12_CAT_UPG_UTL.add_fatal_error(
709 p_interface_header_id => l_interface_header_ids(i),
710 p_error_message_name => 'ICX_CAT_SUPPLIER_REQD',
711 p_table_name => 'PO_HEADERS_INTERFACE',
712 p_column_name => 'VENDOR_ID',
713 p_column_value => l_vendor_id_list(i)
714 );
715 END LOOP;
716 -- ECO bug 5584556: End
717
718 l_progress := '050';
719 -- SQL What: Bulk validate vendor_id.
720 -- Insert the error rows into GT table.
721 -- SQL Why : It will be used to mark the record in plsql table as error.
722 -- SQL Join: vendor_id
723 FORALL i IN 1 .. p_headers_rec.vendor_id.COUNT
724 INSERT INTO PO_SESSION_GT(key,
725 num1,
726 num2,
727 num3,
728 char1)
729 SELECT p_key
730 , l_subscript_array(i)
731 , p_headers_rec.interface_header_id(i)
732 , p_headers_rec.vendor_id(i)
733 , p_headers_rec.vendor_name(i)
734 FROM DUAL
735 WHERE --p_headers_rec.has_errors(i) = 'N'
736 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
737 -- ECO bug 5584556: Start
738 -- The following 3 validation checks are already done above.
739 -- So adding these additional where-clauses to skip those cases.
740 AND p_headers_rec.vendor_id(i) IS NOT NULL
741 AND p_headers_rec.vendor_id(i) <> g_NULL_COLUMN_VALUE -- -2
742 AND EXISTS(SELECT 1
743 FROM PO_VENDORS PV
744 WHERE p_headers_rec.vendor_id(i) = PV.vendor_id)
745 -- ECO bug 5584556: End
746 AND NOT EXISTS(SELECT 1
747 FROM PO_SUPPLIERS_VAL_V PSV
748 WHERE p_headers_rec.vendor_id(i) = PSV.vendor_id);
749
750 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
751
752 l_progress := '060';
753 -- SQL What: Transfer from session GT table to local arrays
754 -- SQL Why : It will be used to mark the error records.
755 -- SQL Join: key
756 DELETE FROM PO_SESSION_GT
757 WHERE key = p_key
758 RETURNING num1, num2, num3, char1
759 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_id_list, l_vendor_name_list;
760
761 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
762
763 l_progress := '070';
764 -- Mark the error records
765 FOR i IN 1 .. l_indexes.COUNT
766 LOOP
767 l_index := l_indexes(i);
768 p_headers_rec.has_errors(l_index) := 'Y';
769
770 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_name='||l_vendor_name_list(i)||', vendor_id='||l_vendor_id_list(i)); END IF;
771
772 -- Add error message into INTERFACE_ERRORS table
773 -- ICX_CAT_INVALID_SUPPLIER:
774 -- "Supplier (VALUE) is inactive or invalid."
775 PO_R12_CAT_UPG_UTL.add_fatal_error(
776 p_interface_header_id => l_interface_header_ids(i),
777 --p_error_message_name => 'PO_PDOI_INVALID_VENDOR',
778 p_error_message_name => 'ICX_CAT_INVALID_SUPPLIER',
779 p_table_name => 'PO_HEADERS_INTERFACE',
780 p_column_name => 'VENDOR_NAME',
781 p_column_value => l_vendor_name_list(i),
782 p_token1_name => 'VALUE',
783 p_token1_value => l_vendor_id_list(i) -- debug only
784 );
785 END LOOP;
786
787 ----------------------------------------------------------------------------
788 -- Validate vendor_site_id
789 ----------------------------------------------------------------------------
790
791 -- ECO bug 5584556: Start: Add new messages
792 l_progress := '080';
793 -- SQL What: Bulk validate vendor_site_id. Check that it is not NULL or -2.
794 -- Also check that the ID exists in PO_VENDOR_SITES_ALL table.
795 -- Insert the error rows into GT table.
796 -- SQL Why : It will be used to mark the record in plsql table as error.
797 -- SQL Join: vendor_site_id
798 FORALL i IN 1 .. p_headers_rec.vendor_site_id.COUNT
799 INSERT INTO PO_SESSION_GT(key,
800 num1,
801 num2,
802 num3,
803 char1)
804 SELECT p_key
805 , l_subscript_array(i)
806 , p_headers_rec.interface_header_id(i)
807 , p_headers_rec.vendor_site_id(i)
808 , p_headers_rec.vendor_site_code(i)
809 FROM DUAL
810 WHERE --p_headers_rec.has_errors(i) = 'N'
811 p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE')
812 AND
813 (
814 (
815 -- Handle null Supplier Sites for Create action only
816 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
817 AND (p_headers_rec.vendor_site_id(i) IS NULL
818 OR p_headers_rec.vendor_site_id(i) = g_NULL_COLUMN_VALUE)
819 )
820 OR
821 (
822 -- Handle invalid Supplier Site id for Create/update action
823 p_headers_rec.vendor_site_id(i) IS NOT NULL
824 AND p_headers_rec.vendor_site_id(i) <> g_NULL_COLUMN_VALUE
825 AND NOT EXISTS( SELECT 1
826 FROM PO_VENDOR_SITES_ALL PVSA
827 WHERE p_headers_rec.vendor_site_id(i) = PVSA.vendor_site_id)
828 )
829 );
830
831 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
832
833 l_progress := '090';
834 -- SQL What: Transfer from session GT table to local arrays
835 -- SQL Why : It will be used to mark the error records.
836 -- SQL Join: key
837 DELETE FROM PO_SESSION_GT
838 WHERE key = p_key
839 RETURNING num1, num2, num3, char1
840 BULK COLLECT INTO l_indexes, l_interface_header_ids,
841 l_vendor_site_id_list, l_vendor_site_code_list;
842
843 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
844
845 l_progress := '100';
846 -- Mark the error records
847 FOR i IN 1 .. l_indexes.COUNT
848 LOOP
849 l_index := l_indexes(i);
850 p_headers_rec.has_errors(l_index) := 'Y';
851
852 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_site_id='||l_vendor_site_id_list(i)); END IF;
853
854 -- Add error message into INTERFACE_ERRORS table
855 -- ICX_CAT_SUPPLIER_SITE_REQD:
856 -- "Supplier site is missing."
857 PO_R12_CAT_UPG_UTL.add_fatal_error(
858 p_interface_header_id => l_interface_header_ids(i),
859 p_error_message_name => 'ICX_CAT_SUPPLIER_SITE_REQD',
860 p_table_name => 'PO_HEADERS_INTERFACE',
861 p_column_name => 'VENDOR_SITE_ID',
862 p_column_value => l_vendor_site_id_list(i)
863 );
864 END LOOP;
865 -- ECO bug 5584556: End
866
867 l_progress := '110';
868 -- SQL What: Bulk validate vendor_site_id. Check that the site is not inactive.
869 -- Insert the error rows into GT table.
870 -- SQL Why : It will be used to mark the record in plsql table as error.
871 -- SQL Join: vendor_site_id
872 FORALL i IN 1 .. p_headers_rec.vendor_site_id.COUNT
873 INSERT INTO PO_SESSION_GT(key,
874 num1,
875 num2,
876 num3,
877 char1)
878 SELECT p_key
879 , l_subscript_array(i)
880 , p_headers_rec.interface_header_id(i)
881 , p_headers_rec.vendor_site_id(i)
882 , p_headers_rec.vendor_site_code(i)
883 FROM DUAL
884 WHERE --p_headers_rec.has_errors(i) = 'N'
885 p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE')
886 -- ECO bug 5584556: Start
887 -- The following 3 validation checks are already done above.
888 -- So adding these additional where-clauses to skip those cases.
889 AND p_headers_rec.vendor_site_id(i) IS NOT NULL
890 AND p_headers_rec.vendor_site_id(i) <> g_NULL_COLUMN_VALUE
891 AND EXISTS(SELECT 1
892 FROM PO_VENDOR_SITES_ALL PVSA
893 WHERE p_headers_rec.vendor_site_id(i) = PVSA.vendor_site_id)
894 -- ECO bug 5584556: End
895 AND NOT EXISTS(SELECT 1
896 FROM PO_SUPPLIER_SITES_VAL_V PSSV
897 WHERE p_headers_rec.vendor_site_id(i) = PSSV.vendor_site_id);
898
899 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
900
901 l_progress := '120';
902 -- SQL What: Transfer from session GT table to local arrays
903 -- SQL Why : It will be used to mark the error records.
904 -- SQL Join: key
905 DELETE FROM PO_SESSION_GT
906 WHERE key = p_key
907 RETURNING num1, num2, num3, char1
908 BULK COLLECT INTO l_indexes, l_interface_header_ids,
909 l_vendor_site_id_list, l_vendor_site_code_list;
910
911 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
912
913 l_progress := '130';
914 -- Mark the error records
915 FOR i IN 1 .. l_indexes.COUNT
916 LOOP
917 l_index := l_indexes(i);
918 p_headers_rec.has_errors(l_index) := 'Y';
919
920 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_site_code='||l_vendor_site_code_list(i)||', vendor_site_id='||l_vendor_site_id_list(i)); END IF;
921
922 -- Add error message into INTERFACE_ERRORS table
923 -- ICX_CAT_INVALID_SUPPLIER_SITE:
924 -- "Supplier site (VALUE) is not an active and valid purchasing supplier site."
925 PO_R12_CAT_UPG_UTL.add_fatal_error(
926 p_interface_header_id => l_interface_header_ids(i),
927 --p_error_message_name => 'PO_INVALID_VENDOR_SITE_ID',
928 p_error_message_name => 'ICX_CAT_INVALID_SUPPLIER_SITE',
929 p_table_name => 'PO_HEADERS_INTERFACE',
930 p_column_name => 'VENDOR_SITE_ID',
931 p_column_value => l_vendor_site_code_list(i),
932 p_token1_name => 'VALUE',
933 p_token1_value => l_vendor_site_code_list(i)
934 );
935 END LOOP;
936
937 ----------------------------------------------------------------------------
938 -- Validate vendor_contact_id
939 ----------------------------------------------------------------------------
940
941 l_progress := '140';
942 -- SQL What: Bulk validate vendor_contact_id.
943 -- Insert the error rows into GT table.
944 -- SQL Why : It will be used to mark the record in plsql table as error.
945 -- SQL Join: vendor_id
946 FORALL i IN 1 .. p_headers_rec.vendor_contact_id.COUNT
947 INSERT INTO PO_SESSION_GT(key,
948 num1,
949 num2,
950 num3)
951 SELECT p_key
952 , l_subscript_array(i)
953 , p_headers_rec.interface_header_id(i)
954 , p_headers_rec.vendor_contact_id(i)
955 FROM DUAL
956 WHERE --p_headers_rec.has_errors(i) = 'N'
957 p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE')
958 AND p_headers_rec.vendor_id(i) IS NOT NULL
959 AND p_headers_rec.vendor_site_id(i) IS NOT NULL
960 AND p_headers_rec.vendor_contact_id(i) IS NOT NULL
961 AND NOT EXISTS(SELECT 1
962 FROM PO_VENDOR_CONTACTS PVC
963 WHERE p_headers_rec.vendor_site_id(i) = PVC.vendor_site_id
964 AND p_headers_rec.vendor_contact_id(i) = PVC.vendor_contact_id);
965
966 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
967
968 l_progress := '150';
969 -- SQL What: Transfer from session GT table to local arrays
970 -- SQL Why : It will be used to mark the error records.
971 -- SQL Join: key
972 DELETE FROM PO_SESSION_GT
973 WHERE key = p_key
974 RETURNING num1, num2, num3
975 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_contact_id_list;
976
977 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
978
979 l_progress := '160';
980 -- Mark the error records
981 FOR i IN 1 .. l_indexes.COUNT
982 LOOP
983 l_index := l_indexes(i);
984
985 -- No need to error out. Just NULL out the vendor contact and proceed
986 --p_headers_rec.has_errors(l_index) := 'Y';
987 p_headers_rec.vendor_contact_id(l_index) := NULL;
988
989 -- Add error message into INTERFACE_ERRORS table
990 -- PO_PDOI_INVALID_VDR_CNTCT:
991 -- "Supplier Contact (VALUE=VALUE) is not an active and valid contact for the specified supplier site."
992 --PO_R12_CAT_UPG_UTL.add_fatal_error(
993 -- p_interface_header_id => l_interface_header_ids(i),
994 -- p_error_message_name => 'PO_PDOI_INVALID_VDR_CNTCT',
995 -- p_table_name => 'PO_HEADERS_INTERFACE',
996 -- p_column_name => 'VENDOR_CONTACT_ID',
997 -- p_column_value => l_vendor_contact_id_list(i),
998 -- p_token1_name => 'VALUE',
999 -- p_token1_value => l_vendor_contact_id_list(i)
1000 -- );
1001 END LOOP;
1002
1003 ----------------------------------------------------------------------------
1004 -- Validate vendor_site_id for CCR status
1005 ----------------------------------------------------------------------------
1006
1007 -- The PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis() API is not
1008 -- available in 11.5.9. It was coded as part of the JFMIP project in 11.5.10
1009
1010 -- validate vendor site CCR if approval status is APPROVED.
1011 l_progress := '170';
1012 IF (l_federal_instance = 'Y') THEN
1013 FOR i IN 1 .. p_headers_rec.vendor_site_id.COUNT
1014 LOOP
1015
1016 IF (p_headers_rec.vendor_id(i) IS NOT NULL AND
1017 p_headers_rec.vendor_id(i) <> g_NULL_COLUMN_VALUE AND -- -2
1018 p_headers_rec.vendor_site_id(i) IS NOT NULL AND
1019 p_headers_rec.vendor_site_id(i) <> g_NULL_COLUMN_VALUE AND -- -2
1020 p_headers_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_header_create, 'UPDATE') --AND
1021 --p_headers_rec.has_errors(i) = 'N'
1022 ) THEN
1023
1024 l_progress := '180';
1025 -- Call PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis to check the
1026 -- Central Contractor Registration (CCR) status of the vendor site
1027 x_temp_val := PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis(
1028 p_vendor_id => p_headers_rec.vendor_id(i),
1029 p_vendor_site_id => p_headers_rec.vendor_site_id(i));
1030
1031 IF (x_temp_val = FALSE) THEN
1032 p_headers_rec.has_errors(i) := 'Y';
1033
1034 l_progress := '190';
1035 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_site_code='||p_headers_rec.vendor_site_code(i)||', vendor_site_id='||p_headers_rec.vendor_site_id(i)||
1036 ', vendor_name='||p_headers_rec.vendor_name(i)||', vendor_id='||p_headers_rec.vendor_id(i)); END IF;
1037
1038 -- Add error message into INTERFACE_ERRORS table
1039 -- ICX_CAT_SUPPLIER_SITE_CCR_INV:
1040 -- "Supplier site (VENDOR_SITE) is assigned to a CCR supplier
1041 -- (VENDOR_NAME) with an expired or deleted registration."
1042 PO_R12_CAT_UPG_UTL.add_fatal_error(
1043 p_interface_header_id => p_headers_rec.interface_header_id(i),
1044 --p_error_message_name => 'PO_PDOI_VENDOR_SITE_CCR_INV',
1045 p_error_message_name => 'ICX_CAT_SUPPLIER_SITE_CCR_INV',
1046 p_table_name => 'PO_HEADERS_INTERFACE',
1047 p_column_name => 'VENDOR_SITE_ID',
1048 p_column_value => p_headers_rec.vendor_site_code(i),
1049 p_token1_name => 'VENDOR_SITE_ID',
1050 p_token1_value => p_headers_rec.vendor_site_id(i),
1051 p_token2_name => 'VENDOR_NAME',
1052 p_token2_value => p_headers_rec.vendor_name(i)
1053 );
1054 END IF;
1055 END IF;
1056 END LOOP;
1057 END IF; -- IF (l_federal_instance = 'Y')
1058
1059 l_progress := '200';
1060 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1064 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1065 END validate_vendor_info;
1066
1067 --------------------------------------------------------------------------------
1068 --Start of Comments
1069 --Name: get_hr_location_name_from_id
1070 --Pre-reqs:
1071 -- None
1072 --Modifies:
1073 -- None
1074 --Locks:
1075 -- None.
1076 --Function:
1077 -- Performs the id-to-name conversion for location_id.
1078 -- Used when logging validation errors.
1079 --Parameters:
1080 --IN:
1081 --p_category_id
1082 -- The LOCATION_ID for which we want the name
1083 --OUT:
1084 --p_category_name
1085 -- The outut name for the given ID.
1086 --OUT:
1087 --End of Comments
1088 --------------------------------------------------------------------------------
1089 PROCEDURE get_hr_location_name_from_id
1090 (
1091 p_location_id IN NUMBER,
1092 x_location_name OUT NOCOPY VARCHAR2
1093 )
1094 IS
1095 l_api_name CONSTANT VARCHAR2(30) := 'get_hr_location_name_from_id';
1096 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1097 l_progress VARCHAR2(3) := '000';
1098 BEGIN
1099 l_progress := '010';
1100 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1101
1102 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_location_id='||p_location_id); END IF;
1103
1104 -- ECO bug 5584556: id-to-name conversion for new message
1105 -- Get the category name
1106 l_progress := '020';
1107 BEGIN
1108 SELECT location_code
1109 INTO x_location_name
1110 FROM HR_LOCATIONS_ALL_TL
1111 WHERE location_id = p_location_id
1112 AND language = userenv('LANG');
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 l_progress := '030';
1116 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from HR_LOCATIONS_ALL_TL: '||SQLERRM); END IF;
1117
1118 x_location_name := p_location_id;
1119 END;
1120 -- ECO bug 5584556: End
1121
1122 l_progress := '040';
1123 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'location_name='||x_location_name); END IF;
1124
1125 l_progress := '050';
1126 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1130 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1131 END get_hr_location_name_from_id;
1132
1133 --------------------------------------------------------------------------------
1134 --Start of Comments
1135 --Name: validate_ship_to_location_id
1136 --Pre-reqs:
1137 -- The iP catalog data is populated in input pl/sql tables.
1138 --Modifies:
1139 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
1140 -- b) FND_MSG_PUB on unhandled exceptions.
1141 --Locks:
1142 -- None.
1143 --Function:
1144 -- Validates ship_to_location_id not null and against HR_LOCATIONS.
1145 --Parameters:
1146 --IN:
1147 --p_key
1148 -- Key used to access records in PO_SESSION_GT table.
1149 --IN/OUT:
1150 --p_headers_rec
1151 -- A record of plsql tables containing a batch of headers. If this validation
1152 -- fails, the 'has_errors' column is set to 'Y'.
1153 --OUT:
1154 --End of Comments
1155 --------------------------------------------------------------------------------
1156 PROCEDURE validate_ship_to_location_id
1157 (
1158 p_key IN NUMBER,
1159 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1160 )
1161 IS
1162 l_api_name CONSTANT VARCHAR2(30) := 'validate_ship_to_location_id';
1163 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1164 l_progress VARCHAR2(3) := '000';
1165
1166 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
1167
1168 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1169 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1170 l_err_ship_to_loc_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1171
1172 l_index NUMBER;
1173 i NUMBER;
1174 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1175
1176 l_location_name HR_LOCATIONS_ALL_TL.location_code%TYPE;
1177 BEGIN
1178 l_progress := '010';
1179 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1180
1181 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.ship_to_location_id.COUNT);
1182
1183 IF (p_headers_rec.currency_code.COUNT > 0) THEN
1184 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.ship_to_location_id(1)='||p_headers_rec.ship_to_location_id(1)); END IF;
1185 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1186 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1187 END IF;
1188
1189 l_progress := '020';
1190 -- ECO bug 5584556: Start
1191 -- Add a separate message to check for null values and id's that dont exist
1192
1193 -- SQL What: Bulk validate ship_to_location_id not null and against HR_LOCATIONS_ALL.
1194 -- Get the errored rows into GT table.
1195 -- SQL Why : It will be used to mark the record in plsql table as error.
1196 -- SQL Join: ship_to_location_id
1197 FORALL i IN 1 .. p_headers_rec.ship_to_location_id.COUNT
1198 INSERT INTO PO_SESSION_GT(key,
1199 num1,
1200 num2,
1201 num3)
1202 SELECT p_key
1203 , l_subscript_array(i)
1204 , p_headers_rec.interface_header_id(i)
1205 , p_headers_rec.ship_to_location_id(i)
1206 FROM DUAL
1207 WHERE --p_headers_rec.has_errors(i) = 'N'
1208 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1209 AND (p_headers_rec.ship_to_location_id(i) IS NULL OR
1210 NOT EXISTS(SELECT 1
1211 FROM HR_LOCATIONS_ALL HRLA
1212 WHERE p_headers_rec.ship_to_location_id(i) = HRLA.location_id));
1213
1214 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1215
1216 l_progress := '030';
1217 -- SQL What: Transfer from session GT table to local arrays
1218 -- SQL Why : It will be used to mark the error records.
1219 -- SQL Join: key
1220 DELETE FROM PO_SESSION_GT
1221 WHERE key = p_key
1222 RETURNING num1, num2, num3
1223 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_to_loc_id_list;
1224
1225 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1226
1227 l_progress := '040';
1228 -- Mark the error records
1229 FOR i IN 1 .. l_indexes.COUNT
1230 LOOP
1231 l_index := l_indexes(i);
1232 p_headers_rec.has_errors(l_index) := 'Y';
1233
1234 -- Add error message into INTERFACE_ERRORS table
1235 -- ICX_CAT_SHIP_LOC_REQD:
1236 -- "The system cannot obtain the default value for the ship to location."
1237 PO_R12_CAT_UPG_UTL.add_fatal_error(
1238 p_interface_header_id => l_interface_header_ids(i),
1239 p_error_message_name => 'ICX_CAT_SHIP_LOC_REQD',
1240 p_table_name => 'PO_HEADERS_INTERFACE',
1241 p_column_name => 'SHIP_TO_LOCATION_ID',
1242 p_column_value => l_err_ship_to_loc_id_list(i)
1243 );
1244 END LOOP;
1245 -- ECO bug 5584556: End
1246
1247 l_progress := '050';
1248 -- SQL What: Bulk validate ship_to_location_id against HR_LOCATIONS.
1249 -- Check if it is an active location.
1250 -- Get the errored rows into GT table.
1251 -- SQL Why : It will be used to mark the record in plsql table as error.
1252 -- SQL Join: ship_to_location_id
1253 FORALL i IN 1 .. p_headers_rec.ship_to_location_id.COUNT
1254 INSERT INTO PO_SESSION_GT(key,
1255 num1,
1256 num2,
1257 num3)
1258 SELECT p_key
1259 , l_subscript_array(i)
1260 , p_headers_rec.interface_header_id(i)
1261 , p_headers_rec.ship_to_location_id(i)
1262 FROM DUAL
1263 WHERE --p_headers_rec.has_errors(i) = 'N'
1264 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1265 -- ECO bug 5584556: Start
1266 -- The following validation checks are already done above.
1267 -- So adding these additional where-clauses to skip those cases.
1268 AND p_headers_rec.ship_to_location_id(i) IS NOT NULL
1269 AND EXISTS(SELECT 'ID exists'
1270 FROM HR_LOCATIONS_ALL HRLA
1271 WHERE p_headers_rec.ship_to_location_id(i) = HRLA.location_id)
1272 -- ECO bug 5584556: End
1273 AND NOT EXISTS(SELECT 'Active ship-to-location'
1274 FROM HR_LOCATIONS HRL
1275 WHERE HRL.ship_to_site_flag = 'Y'
1276 AND p_headers_rec.ship_to_location_id(i) = HRL.location_id
1277 AND SYSDATE < NVL(HRL.inactive_date, SYSDATE + 1));
1278
1279 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1280
1281 l_progress := '060';
1282 -- SQL What: Transfer from session GT table to local arrays
1283 -- SQL Why : It will be used to mark the error records.
1284 -- SQL Join: key
1285 DELETE FROM PO_SESSION_GT
1286 WHERE key = p_key
1287 RETURNING num1, num2, num3
1288 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_to_loc_id_list;
1289
1290 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1291
1292 l_progress := '070';
1293 -- Mark the error records
1294 FOR i IN 1 .. l_indexes.COUNT
1295 LOOP
1296 l_index := l_indexes(i);
1297 p_headers_rec.has_errors(l_index) := 'Y';
1298
1299 -- ECO bug 5584556: id-to-name conversion for new message
1300 l_progress := '080';
1301 get_hr_location_name_from_id
1302 (
1303 p_location_id => l_err_ship_to_loc_id_list(i)
1304 , x_location_name => l_location_name
1305 );
1306 -- ECO bug 5584556: End
1307
1308 -- Add error message into INTERFACE_ERRORS table
1309 -- ICX_CAT_INVALID_SHIP_LOC:
1310 -- "Default ship to location (VALUE) is inactive or invalid."
1311 PO_R12_CAT_UPG_UTL.add_fatal_error(
1312 p_interface_header_id => l_interface_header_ids(i),
1313 --p_error_message_name => 'PO_PDOI_INVALID_SHIP_LOC_ID',
1314 p_error_message_name => 'ICX_CAT_INVALID_SHIP_LOC',
1315 p_table_name => 'PO_HEADERS_INTERFACE',
1316 p_column_name => 'SHIP_TO_LOCATION_ID',
1317 p_column_value => l_location_name, -- ECO bug 5584556
1318 p_token1_name => 'VALUE',
1319 p_token1_value => l_err_ship_to_loc_id_list(i)
1320 );
1321 END LOOP;
1322
1323 l_progress := '090';
1324 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1328 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1329 END validate_ship_to_location_id;
1330
1331 --------------------------------------------------------------------------------
1332 --Start of Comments
1333 --Name: validate_bill_to_location_id
1334 --Pre-reqs:
1335 -- The iP catalog data is populated in input pl/sql tables.
1336 --Modifies:
1337 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
1338 -- b) FND_MSG_PUB on unhandled exceptions.
1339 --Locks:
1340 -- None.
1341 --Function:
1342 -- Validates bill_to_location_id not null and against HR_LOCATIONS.
1343 --Parameters:
1344 --IN:
1345 --p_key
1346 -- Key used to access records in PO_SESSION_GT table.
1347 --IN/OUT:
1348 --p_headers_rec
1349 -- A record of plsql tables containing a batch of headers. If this validation
1350 -- fails, the 'has_errors' column is set to 'Y'.
1351 --OUT:
1352 --End of Comments
1353 --------------------------------------------------------------------------------
1354 PROCEDURE validate_bill_to_location_id
1355 (
1356 p_key IN NUMBER,
1357 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1358 )
1359 IS
1360 l_api_name CONSTANT VARCHAR2(30) := 'validate_bill_to_location_id';
1361 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1362 l_progress VARCHAR2(3) := '000';
1363
1364 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
1365
1366 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1367 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1368 l_err_bill_to_loc_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1369
1370 l_index NUMBER;
1371 i NUMBER;
1372 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1373
1374 l_location_name HR_LOCATIONS_ALL_TL.location_code%TYPE;
1375 BEGIN
1376 l_progress := '010';
1377 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1378
1379 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.bill_to_location_id.COUNT);
1380
1381 IF (p_headers_rec.currency_code.COUNT > 0) THEN
1382 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.bill_to_location_id(1)='||p_headers_rec.bill_to_location_id(1)); END IF;
1383 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1384 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1385 END IF;
1386
1387 l_progress := '020';
1388 -- SQL What: Bulk validate bill_to_location_id not null and against HR_LOCATIONS_ALL.
1389 -- Get the errored rows into GT table.
1390 -- SQL Why : It will be used to mark the record in plsql table as error.
1391 -- SQL Join: bill_to_location_id
1392 FORALL i IN 1 .. p_headers_rec.bill_to_location_id.COUNT
1393 INSERT INTO PO_SESSION_GT(key,
1394 num1,
1395 num2,
1396 num3)
1397 SELECT p_key
1398 , l_subscript_array(i)
1399 , p_headers_rec.interface_header_id(i)
1400 , p_headers_rec.bill_to_location_id(i)
1401 FROM DUAL
1402 WHERE --p_headers_rec.has_errors(i) = 'N'
1403 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1404 AND (p_headers_rec.bill_to_location_id(i) IS NULL OR
1405 NOT EXISTS(SELECT 1
1406 FROM HR_LOCATIONS_ALL HRLA
1407 WHERE p_headers_rec.bill_to_location_id(i) = HRLA.location_id));
1408
1409 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1410
1411 l_progress := '030';
1412 -- SQL What: Transfer from session GT table to local arrays
1413 -- SQL Why : It will be used to mark the error records.
1414 -- SQL Join: key
1415 DELETE FROM PO_SESSION_GT
1416 WHERE key = p_key
1417 RETURNING num1, num2, num3
1418 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_bill_to_loc_id_list;
1419
1420 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1421
1422 l_progress := '040';
1423 -- Mark the error records
1424 FOR i IN 1 .. l_indexes.COUNT
1425 LOOP
1426 l_index := l_indexes(i);
1427 p_headers_rec.has_errors(l_index) := 'Y';
1428
1429 -- Add error message into INTERFACE_ERRORS table
1430 -- ICX_CAT_BILL_LOC_REQD:
1431 -- "The system cannot obtain the default value for the bill to location."
1432 PO_R12_CAT_UPG_UTL.add_fatal_error(
1433 p_interface_header_id => l_interface_header_ids(i),
1434 p_error_message_name => 'ICX_CAT_BILL_LOC_REQD',
1435 p_table_name => 'PO_HEADERS_INTERFACE',
1436 p_column_name => 'BILL_TO_LOCATION_ID',
1437 p_column_value => l_err_bill_to_loc_id_list(i)
1438 );
1439 END LOOP;
1440
1441 l_progress := '050';
1442 -- SQL What: Bulk validate bill_to_location_id against HR_LOCATIONS.
1443 -- Check if it is an active location.
1444 -- Get the errored rows into GT table.
1445 -- SQL Why : It will be used to mark the record in plsql table as error.
1446 -- SQL Join: bill_to_location_id
1447 FORALL i IN 1 .. p_headers_rec.bill_to_location_id.COUNT
1448 INSERT INTO PO_SESSION_GT(key,
1449 num1,
1450 num2,
1451 num3)
1452 SELECT p_key
1453 , l_subscript_array(i)
1454 , p_headers_rec.interface_header_id(i)
1455 , p_headers_rec.bill_to_location_id(i)
1456 FROM DUAL
1457 WHERE --p_headers_rec.has_errors(i) = 'N'
1458 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1459 -- ECO bug 5584556: Start
1460 -- The following validation checks are already done above.
1461 -- So adding these additional where-clauses to skip those cases.
1462 AND p_headers_rec.bill_to_location_id(i) IS NOT NULL
1463 AND EXISTS(SELECT 'ID exists'
1464 FROM HR_LOCATIONS_ALL HRLA
1465 WHERE p_headers_rec.bill_to_location_id(i) = HRLA.location_id)
1466 -- ECO bug 5584556: End
1467 AND NOT EXISTS(SELECT 1
1468 FROM HR_LOCATIONS HRL
1469 WHERE HRL.bill_to_site_flag = 'Y'
1470 AND p_headers_rec.bill_to_location_id(i) = HRL.location_id
1471 AND SYSDATE < NVL(HRL.inactive_date, SYSDATE + 1));
1472
1473 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1474
1475 l_progress := '060';
1476 -- SQL What: Transfer from session GT table to local arrays
1477 -- SQL Why : It will be used to mark the error records.
1478 -- SQL Join: key
1479 DELETE FROM PO_SESSION_GT
1480 WHERE key = p_key
1481 RETURNING num1, num2, num3
1482 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_bill_to_loc_id_list;
1483
1484 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1485
1486 l_progress := '070';
1487 -- Mark the error records
1488 FOR i IN 1 .. l_indexes.COUNT
1489 LOOP
1490 l_index := l_indexes(i);
1491 p_headers_rec.has_errors(l_index) := 'Y';
1492
1493 -- ECO bug 5584556: id-to-name conversion for new message
1494 l_progress := '080';
1495 get_hr_location_name_from_id
1496 (
1497 p_location_id => l_err_bill_to_loc_id_list(i)
1498 , x_location_name => l_location_name
1499 );
1500 -- ECO bug 5584556: End
1501
1502 -- Add error message into INTERFACE_ERRORS table
1503 -- ICX_CAT_INVALID_BILL_LOC:
1504 -- "Default bill to location (VALUE) is inactive or invalid."
1505 PO_R12_CAT_UPG_UTL.add_fatal_error(
1506 p_interface_header_id => l_interface_header_ids(i),
1507 --p_error_message_name => 'PO_PDOI_INVALID_BILL_LOC_ID',
1508 p_error_message_name => 'ICX_CAT_INVALID_BILL_LOC',
1509 p_table_name => 'PO_HEADERS_INTERFACE',
1510 p_column_name => 'BILL_TO_LOCATION_ID',
1511 p_column_value => l_location_name, -- ECO bug 5584556
1512 p_token1_name => 'VALUE',
1513 p_token1_value => l_err_bill_to_loc_id_list(i)
1514 );
1515 END LOOP;
1516
1517 l_progress := '090';
1518 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1522 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1523 END validate_bill_to_location_id;
1524
1525 --------------------------------------------------------------------------------
1526 --Start of Comments
1527 --Name: get_lookup_name_from_code
1528 --Pre-reqs:
1529 -- None
1530 --Modifies:
1531 -- None
1532 --Locks:
1533 -- None.
1534 --Function:
1535 -- Performs the id-to-name conversion for PO_LOOKUP_CODES.
1536 -- Used when logging validation errors.
1537 --Parameters:
1538 --IN:
1539 --p_po_lookup_code
1540 -- The LOOKUP_CODE for which we want the meaning
1541 --p_po_lookup_type
1542 -- The LOOKUP_TYPE for which we want the meaning
1543 --OUT:
1544 --x_lookup_meaning
1545 -- The outut name for the given code.
1546 --OUT:
1547 --End of Comments
1548 --------------------------------------------------------------------------------
1549 PROCEDURE get_lookup_name_from_code
1550 (
1551 p_po_lookup_code IN VARCHAR2,
1552 p_po_lookup_type IN VARCHAR2,
1553 x_lookup_meaning OUT NOCOPY VARCHAR2
1554 )
1555 IS
1556 l_api_name CONSTANT VARCHAR2(30) := 'get_lookup_name_from_code';
1557 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1558 l_progress VARCHAR2(3) := '000';
1559 BEGIN
1560 l_progress := '010';
1561 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1562
1563 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_po_lookup_code='||p_po_lookup_code); END IF;
1564
1565 -- ECO bug 5584556: id-to-name conversion for new message
1566 -- Get the lookup meaning
1567 l_progress := '020';
1568 BEGIN
1569 SELECT displayed_field
1570 INTO x_lookup_meaning
1571 FROM PO_LOOKUP_CODES
1572 WHERE lookup_code = p_po_lookup_code
1573 AND lookup_type = p_po_lookup_type;
1574 -- The PO_LOOKUP_CODES is a view on top of FND_LOOKUP_VALUES with a userenv('LANG') join
1575 --AND language = userenv('LANG');
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 l_progress := '030';
1579 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from PO_LOOKUP_CODES: '||SQLERRM); END IF;
1580
1581 x_lookup_meaning := p_po_lookup_code;
1582 END;
1583 -- ECO bug 5584556: End
1584
1585 l_progress := '040';
1586 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_lookup_meaning='||x_lookup_meaning); END IF;
1587
1588 l_progress := '050';
1589 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1593 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1594 END get_lookup_name_from_code;
1595
1596 --------------------------------------------------------------------------------
1597 --Start of Comments
1598 --Name: validate_fob_lookup_code
1599 --Pre-reqs:
1600 -- The iP catalog data is populated in input pl/sql tables.
1601 --Modifies:
1602 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
1603 -- b) FND_MSG_PUB on unhandled exceptions.
1604 --Locks:
1605 -- None.
1606 --Function:
1607 -- Validates fob_lookup_code against PO_LOOKUP_CODES.
1608 --Parameters:
1609 --IN:
1610 --p_key
1611 -- Key used to access records in PO_SESSION_GT table.
1612 --IN/OUT:
1613 --p_headers_rec
1614 -- A record of plsql tables containing a batch of headers. If this validation
1615 -- fails, the 'has_errors' column is set to 'Y'.
1616 --OUT:
1617 --End of Comments
1618 --------------------------------------------------------------------------------
1619 PROCEDURE validate_fob_lookup_code
1620 (
1621 p_key IN NUMBER,
1622 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1623 )
1624 IS
1625 l_api_name CONSTANT VARCHAR2(30) := 'validate_fob_lookup_code';
1626 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1627 l_progress VARCHAR2(3) := '000';
1628
1629 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
1630
1631 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1632 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1633 l_err_fob_lookup_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
1634
1635 l_index NUMBER;
1636 i NUMBER;
1637 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1638
1639 l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
1640 BEGIN
1641 l_progress := '010';
1642 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1643 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- FOB_LOOKUP_CODE'); END IF;
1644
1645 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.fob.COUNT);
1646
1647 IF (p_headers_rec.fob.COUNT > 0) THEN
1648 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.fob(1)='||p_headers_rec.fob(1)); END IF;
1649 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1650 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1651 END IF;
1652
1653 l_progress := '020';
1654 -- SQL What: Bulk validate fob_lookup_code not null and against PO_LOOKUP_CODES.
1655 -- Get the errored rows into GT table.
1656 -- SQL Why : It will be used to mark the record in plsql table as error.
1657 -- SQL Join: bill_to_location_id
1658 FORALL i IN 1 .. p_headers_rec.fob.COUNT
1659 INSERT INTO PO_SESSION_GT(key,
1660 num1,
1661 num2,
1662 char1)
1663 SELECT p_key
1664 , l_subscript_array(i)
1665 , p_headers_rec.interface_header_id(i)
1666 , p_headers_rec.fob(i)
1667 FROM DUAL
1668 WHERE --p_headers_rec.has_errors(i) = 'N'
1669 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1670 AND p_headers_rec.fob(i) IS NOT NULL
1671 AND NOT EXISTS(SELECT 1
1672 FROM PO_LOOKUP_CODES PLC
1673 WHERE p_headers_rec.fob(i) = PLC.lookup_code
1674 AND PLC.lookup_type = 'FOB'
1675 AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
1676
1677 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1678
1679 l_progress := '030';
1680 -- SQL What: Transfer from session GT table to local arrays
1681 -- SQL Why : It will be used to mark the error records.
1682 -- SQL Join: key
1683 DELETE FROM PO_SESSION_GT
1684 WHERE key = p_key
1685 RETURNING num1, num2, char1
1686 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_fob_lookup_code_list;
1687
1688 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1689
1690 l_progress := '040';
1691 -- Mark the error records
1692 FOR i IN 1 .. l_indexes.COUNT
1693 LOOP
1694 l_index := l_indexes(i);
1695 p_headers_rec.has_errors(l_index) := 'Y';
1696
1697 -- ECO bug 5584556: id-to-name conversion
1698 l_progress := '050';
1699 get_lookup_name_from_code
1700 (
1701 p_po_lookup_code => l_err_fob_lookup_code_list(i)
1702 , p_po_lookup_type => 'FOB'
1703 , x_lookup_meaning => l_lookup_meaning
1704 );
1705 -- ECO bug 5584556: End
1706
1707 l_progress := '060';
1708 -- Add error message into INTERFACE_ERRORS table
1709 -- ICX_CAT_INVALID_FOB:
1710 -- "Default FOB carrier (VALUE) is inactive or invalid."
1711 PO_R12_CAT_UPG_UTL.add_fatal_error(
1712 p_interface_header_id => l_interface_header_ids(i),
1713 --p_error_message_name => 'ICX_CAT_INVALID_FOB_CODE',
1714 p_error_message_name => 'ICX_CAT_INVALID_FOB', -- Bug 5461235
1715 p_table_name => 'PO_HEADERS_INTERFACE',
1716 p_column_name => 'FOB_LOOKUP_CODE',
1717 p_column_value => l_lookup_meaning, -- ECO Bug 5584556
1718 p_token1_name => 'VALUE',
1719 p_token1_value => l_err_fob_lookup_code_list(i)
1720 );
1721 END LOOP;
1722
1723 l_progress := '070';
1724 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1728 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1729 END validate_fob_lookup_code;
1730
1731 --------------------------------------------------------------------------------
1732 --Start of Comments
1733 --Name: validate_ship_via_luc
1734 --Pre-reqs:
1735 -- The iP catalog data is populated in input pl/sql tables.
1736 --Modifies:
1737 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
1738 -- b) FND_MSG_PUB on unhandled exceptions.
1739 --Locks:
1740 -- None.
1741 --Function:
1742 -- Validates ship_via_lookup_code against ORG_FREIGHTS.
1743 --Parameters:
1744 --IN:
1745 --p_key
1746 -- Key used to access records in PO_SESSION_GT table.
1747 --IN/OUT:
1748 --p_headers_rec
1749 -- A record of plsql tables containing a batch of headers. If this validation
1750 -- fails, the 'has_errors' column is set to 'Y'.
1751 --OUT:
1752 --End of Comments
1753 --------------------------------------------------------------------------------
1754 PROCEDURE validate_ship_via_luc
1755 (
1756 p_key IN NUMBER,
1757 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1758 )
1759 IS
1760 l_api_name CONSTANT VARCHAR2(30) := 'validate_ship_via_luc';
1761 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1762 l_progress VARCHAR2(3) := '000';
1763
1764 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
1765
1766 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1767 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1768 l_err_ship_via_luc_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
1769
1770 l_index NUMBER;
1771 i NUMBER;
1772 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1773 BEGIN
1774 l_progress := '010';
1775 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1776 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
1777
1778 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_carrier.COUNT);
1779
1780 IF (p_headers_rec.freight_carrier.COUNT > 0) THEN
1781 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_carrier(1)='||p_headers_rec.freight_carrier(1)); END IF;
1782 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1783 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1784 END IF;
1785
1786 l_progress := '020';
1787 -- SQL What: Bulk validate ship_via_lookup_code against ORG_FREIGHT.
1788 -- Get the errored rows into GT table.
1789 -- SQL Why : It will be used to mark the record in plsql table as error.
1790 -- SQL Join: ship_via_lookup_code
1791
1792 -- bug5601416: use default inv org id instead of master org
1793
1794 FORALL i IN 1 .. p_headers_rec.freight_carrier.COUNT
1795 INSERT INTO PO_SESSION_GT(key,
1796 num1,
1797 num2,
1798 char1)
1799 SELECT p_key
1800 , l_subscript_array(i)
1801 , p_headers_rec.interface_header_id(i)
1802 , p_headers_rec.freight_carrier(i)
1803 FROM DUAL
1804 WHERE --p_headers_rec.has_errors(i) = 'N'
1805 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1806 AND p_headers_rec.freight_carrier(i) IS NOT NULL
1807 AND NOT EXISTS(SELECT 1
1808 FROM ORG_FREIGHT OFR
1809 WHERE p_headers_rec.freight_carrier(i) = OFR.freight_code
1810 AND NVL(OFR.disable_date, SYSDATE + 1) > SYSDATE
1811 AND OFR.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id);
1812
1813 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1814
1815 l_progress := '030';
1816 -- SQL What: Transfer from session GT table to local arrays
1817 -- SQL Why : It will be used to mark the error records.
1818 -- SQL Join: key
1819 DELETE FROM PO_SESSION_GT
1820 WHERE key = p_key
1821 RETURNING num1, num2, char1
1822 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_via_luc_list;
1823
1824 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1825
1826 l_progress := '040';
1827 -- Mark the error records
1828 FOR i IN 1 .. l_indexes.COUNT
1829 LOOP
1830 l_index := l_indexes(i);
1831 p_headers_rec.has_errors(l_index) := 'Y';
1832
1833 -- Add error message into INTERFACE_ERRORS table
1834 -- ICX_CAT_INVALID_FREIGHT_CAR:
1835 -- "Default freight carrier (VALUE) is inactive or invalid."
1836 PO_R12_CAT_UPG_UTL.add_fatal_error(
1837 p_interface_header_id => l_interface_header_ids(i),
1838 p_error_message_name => 'ICX_CAT_INVALID_FREIGHT_CAR', -- ECO bug 5584556: 'ICX_CAT_INVALID_SHIP_VIA_CODE',
1839 p_table_name => 'PO_HEADERS_INTERFACE',
1840 p_column_name => 'SHIP_VIA_LOOKUP_CODE',
1841 p_column_value => l_err_ship_via_luc_list(i),
1842 p_token1_name => 'VALUE',
1843 p_token1_value => l_err_ship_via_luc_list(i)
1844 );
1845 END LOOP;
1846
1847 l_progress := '050';
1848 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1849 EXCEPTION
1850 WHEN OTHERS THEN
1851 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1852 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1853 END validate_ship_via_luc;
1854
1855 --------------------------------------------------------------------------------
1856 --Start of Comments
1857 --Name: validate_freight_terms
1858 --Pre-reqs:
1859 -- The iP catalog data is populated in input pl/sql tables.
1860 --Modifies:
1861 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
1862 -- b) FND_MSG_PUB on unhandled exceptions.
1863 --Locks:
1864 -- None.
1865 --Function:
1866 -- Validates freight_terms_lookup_code against PO_LOOKUP_CODES.
1867 --Parameters:
1868 --IN:
1869 --p_key
1870 -- Key used to access records in PO_SESSION_GT table.
1871 --IN/OUT:
1872 --p_headers_rec
1873 -- A record of plsql tables containing a batch of headers. If this validation
1874 -- fails, the 'has_errors' column is set to 'Y'.
1875 --OUT:
1876 --End of Comments
1877 --------------------------------------------------------------------------------
1878 PROCEDURE validate_freight_terms
1879 (
1880 p_key IN NUMBER,
1881 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
1882 )
1883 IS
1884 l_api_name CONSTANT VARCHAR2(30) := 'validate_freight_terms';
1885 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1886 l_progress VARCHAR2(3) := '000';
1887
1888 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
1889
1890 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1891 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1892 l_err_freight_terms_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
1893
1894 l_index NUMBER;
1895 i NUMBER;
1896 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1897
1898 l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
1899 BEGIN
1900 l_progress := '010';
1901 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1902 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
1903
1904 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_terms.COUNT);
1905
1906 IF (p_headers_rec.freight_terms.COUNT > 0) THEN
1907 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_terms(1)='||p_headers_rec.freight_terms(1)); END IF;
1908 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1909 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1910 END IF;
1911
1912 l_progress := '020';
1913 -- SQL What: Bulk validate freight_terms_lookup_code against PO_LOOKUP_CODES.
1914 -- Get the errored rows into GT table.
1915 -- SQL Why : It will be used to mark the record in plsql table as error.
1916 -- SQL Join: ship_via_lookup_code
1917 FORALL i IN 1 .. p_headers_rec.freight_terms.COUNT
1918 INSERT INTO PO_SESSION_GT(key,
1919 num1,
1920 num2,
1921 char1)
1922 SELECT p_key
1923 , l_subscript_array(i)
1924 , p_headers_rec.interface_header_id(i)
1925 , p_headers_rec.freight_terms(i)
1926 FROM DUAL
1927 WHERE --p_headers_rec.has_errors(i) = 'N'
1928 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
1929 AND p_headers_rec.freight_terms(i) IS NOT NULL
1930 AND NOT EXISTS(
1931 SELECT 1
1932 FROM PO_LOOKUP_CODES PLC
1933 WHERE p_headers_rec.freight_terms(i) = PLC.lookup_code
1934 AND PLC.lookup_type = 'FREIGHT TERMS'
1935 AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
1936
1937 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1938
1939 l_progress := '030';
1940 -- SQL What: Transfer from session GT table to local arrays
1941 -- SQL Why : It will be used to mark the error records.
1942 -- SQL Join: key
1943 DELETE FROM PO_SESSION_GT
1944 WHERE key = p_key
1945 RETURNING num1, num2, char1
1946 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_freight_terms_list;
1947
1948 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1949
1950 l_progress := '040';
1951 -- Mark the error records
1952 FOR i IN 1 .. l_indexes.COUNT
1953 LOOP
1954 l_index := l_indexes(i);
1955 p_headers_rec.has_errors(l_index) := 'Y';
1956
1957 -- ECO bug 5584556: id-to-name conversion
1958 l_progress := '050';
1959 get_lookup_name_from_code
1960 (
1961 p_po_lookup_code => l_err_freight_terms_list(i)
1962 , p_po_lookup_type => 'FREIGHT TERMS'
1963 , x_lookup_meaning => l_lookup_meaning
1964 );
1965 -- ECO bug 5584556: End
1966
1967 l_progress := '060';
1968 -- Add error message into INTERFACE_ERRORS table
1969 -- ICX_CAT_INVALID_FREIGHT_TERMS:
1970 -- "Default freight terms (VALUE) is inactive or invalid."
1971 PO_R12_CAT_UPG_UTL.add_fatal_error(
1972 p_interface_header_id => l_interface_header_ids(i),
1973 p_error_message_name => 'ICX_CAT_INVALID_FREIGHT_TERMS',
1974 p_table_name => 'PO_HEADERS_INTERFACE',
1975 p_column_name => 'FREIGHT_TERMS_LOOKUP_CODE',
1976 p_column_value => l_lookup_meaning, -- ECO bug 5584556
1977 p_token1_name => 'VALUE',
1978 p_token1_value => l_err_freight_terms_list(i)
1979 );
1980 END LOOP;
1981
1982 l_progress := '070';
1983 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1987 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1988 END validate_freight_terms;
1989
1990 --------------------------------------------------------------------------------
1991 --Start of Comments
1992 --Name: get_category_name_from_id
1993 --Pre-reqs:
1994 -- None
1995 --Modifies:
1996 -- None
1997 --Locks:
1998 -- None.
1999 --Function:
2000 -- Performs the id-to-name conversion for AP's terms_id.
2001 -- Used when logging validation errors.
2002 --Parameters:
2003 --IN:
2004 --p_terms_id
2005 -- The TERMS_ID for which we want the name
2006 --OUT:
2007 --p_terms_name
2008 -- The outut name for the given ID.
2009 --OUT:
2010 --End of Comments
2011 --------------------------------------------------------------------------------
2012 PROCEDURE get_ap_terms_name_from_id
2013 (
2014 p_terms_id IN NUMBER,
2015 x_terms_name OUT NOCOPY VARCHAR2
2016 )
2017 IS
2018 l_api_name CONSTANT VARCHAR2(30) := 'get_ap_terms_name_from_id';
2019 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2020 l_progress VARCHAR2(3) := '000';
2021 BEGIN
2022 l_progress := '010';
2023 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2024
2025 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_terms_id='||p_terms_id); END IF;
2026
2027 -- ECO bug 5584556: id-to-name conversion for new message
2028 -- Get the category name
2029 l_progress := '020';
2030 BEGIN
2031 SELECT name
2032 INTO x_terms_name
2033 FROM AP_TERMS_TL
2034 WHERE term_id = p_terms_id
2035 AND language = userenv('LANG');
2036 EXCEPTION
2037 WHEN OTHERS THEN
2038 l_progress := '030';
2039 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from AP_TERMS_TL: '||SQLERRM); END IF;
2040
2041 x_terms_name := p_terms_id;
2042 END;
2043 -- ECO bug 5584556: End
2044
2045 l_progress := '040';
2046 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'terms_name='||x_terms_name); END IF;
2047
2048 l_progress := '050';
2049 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2053 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2054 END get_ap_terms_name_from_id;
2055
2056 --------------------------------------------------------------------------------
2057 --Start of Comments
2058 --Name: validate_terms_id
2059 --Pre-reqs:
2060 -- The iP catalog data is populated in input pl/sql tables.
2061 --Modifies:
2062 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2063 -- b) FND_MSG_PUB on unhandled exceptions.
2064 --Locks:
2065 -- None.
2066 --Function:
2067 -- Validates terms_id against AP_TERMS.
2068 --Parameters:
2069 --IN:
2070 --p_key
2071 -- Key used to access records in PO_SESSION_GT table.
2072 --IN/OUT:
2073 --p_headers_rec
2074 -- A record of plsql tables containing a batch of headers. If this validation
2075 -- fails, the 'has_errors' column is set to 'Y'.
2076 --OUT:
2077 --End of Comments
2078 --------------------------------------------------------------------------------
2079 PROCEDURE validate_terms_id
2080 (
2081 p_key IN NUMBER,
2082 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
2083 )
2084 IS
2085 l_api_name CONSTANT VARCHAR2(30) := 'validate_terms_id';
2086 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2087 l_progress VARCHAR2(3) := '000';
2088
2089 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
2090
2091 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2092 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2093 l_err_terms_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2094
2095 l_index NUMBER;
2096 i NUMBER;
2097 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2098
2099 l_terms_name AP_TERMS_TL.name%TYPE;
2100 BEGIN
2101 l_progress := '010';
2102 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2103 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
2104
2105 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.terms_id.COUNT);
2106
2107 IF (p_headers_rec.terms_id.COUNT > 0) THEN
2108 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.terms_id(1)='||p_headers_rec.terms_id(1)); END IF;
2109 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2110 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2111 END IF;
2112
2113 l_progress := '020';
2114 -- SQL What: Bulk validate terms_id against AP_TERMS.
2115 -- Get the errored rows into GT table.
2116 -- SQL Why : It will be used to mark the record in plsql table as error.
2117 -- SQL Join: ship_via_lookup_code
2118 FORALL i IN 1 .. p_headers_rec.terms_id.COUNT
2119 INSERT INTO PO_SESSION_GT(key,
2120 num1,
2121 num2,
2122 num3)
2123 SELECT p_key
2124 , l_subscript_array(i)
2125 , p_headers_rec.interface_header_id(i)
2126 , p_headers_rec.terms_id(i)
2127 FROM DUAL
2128 WHERE --p_headers_rec.has_errors(i) = 'N'
2129 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
2130 AND p_headers_rec.terms_id(i) IS NOT NULL
2131 AND NOT EXISTS
2132 (SELECT 1
2133 FROM AP_TERMS APT
2134 WHERE p_headers_rec.terms_id(i) = APT.term_id
2135 AND sysdate BETWEEN
2136 nvl(APT.start_date_active, sysdate - 1) AND
2137 nvl(APT.end_date_active, sysdate + 1));
2138
2139 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2140
2141 l_progress := '030';
2142 -- SQL What: Transfer from session GT table to local arrays
2143 -- SQL Why : It will be used to mark the error records.
2144 -- SQL Join: key
2145 DELETE FROM PO_SESSION_GT
2146 WHERE key = p_key
2147 RETURNING num1, num2, num3
2148 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_terms_id_list;
2149
2150 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2151
2152 l_progress := '040';
2153 -- Mark the error records
2154 FOR i IN 1 .. l_indexes.COUNT
2155 LOOP
2156 l_index := l_indexes(i);
2157 p_headers_rec.has_errors(l_index) := 'Y';
2158
2159 -- ECO bug 5584556: convert id to name
2160 l_progress := '050';
2161 get_ap_terms_name_from_id
2162 (
2163 p_terms_id => l_err_terms_id_list(i)
2164 , x_terms_name => l_terms_name
2165 );
2166 -- ECO bug 5584556: End
2167
2168 l_progress := '060';
2169 -- Add error message into INTERFACE_ERRORS table
2170 -- ICX_CAT_INVALID_PAY_TERMS
2171 -- "Default payment terms (VALUE) is inactive or invalid."
2172 PO_R12_CAT_UPG_UTL.add_fatal_error(
2173 p_interface_header_id => l_interface_header_ids(i),
2174 --p_error_message_name => 'ICX_CAT_INVALID_TERMS_ID',
2175 p_error_message_name => 'ICX_CAT_INVALID_PAY_TERMS', -- Bug 5461235
2176 p_table_name => 'PO_HEADERS_INTERFACE',
2177 p_column_name => 'TERMS_ID',
2178 p_column_value => l_terms_name, -- ECO bug 5584556
2179 p_token1_name => 'VALUE',
2180 p_token1_value => l_err_terms_id_list(i)
2181 );
2182 END LOOP;
2183
2184 l_progress := '070';
2185 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2189 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2190 END validate_terms_id;
2191
2192 --------------------------------------------------------------------------------
2193 --Start of Comments
2194 --Name: validate_shipping_control
2195 --Pre-reqs:
2196 -- The iP catalog data is populated in input pl/sql tables.
2197 --Modifies:
2198 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2199 -- b) FND_MSG_PUB on unhandled exceptions.
2200 --Locks:
2201 -- None.
2202 --Function:
2203 -- Validates shipping_control against PO_LOOKUP_CODES.
2204 --Parameters:
2205 --IN:
2206 --p_key
2207 -- Key used to access records in PO_SESSION_GT table.
2208 --IN/OUT:
2209 --p_headers_rec
2210 -- A record of plsql tables containing a batch of headers. If this validation
2211 -- fails, the 'has_errors' column is set to 'Y'.
2212 --OUT:
2213 --End of Comments
2214 --------------------------------------------------------------------------------
2215 PROCEDURE validate_shipping_control
2216 (
2217 p_key IN NUMBER,
2218 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
2219 )
2220 IS
2221 l_api_name CONSTANT VARCHAR2(30) := 'validate_shipping_control';
2222 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2223 l_progress VARCHAR2(3) := '000';
2224
2225 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
2226
2227 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2228 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2229 l_err_shipping_control_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30;
2230
2231 l_index NUMBER;
2232 i NUMBER;
2233 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2234
2235 l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
2236 BEGIN
2237 l_progress := '010';
2238 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2239 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
2240
2241 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.shipping_control.COUNT);
2242
2243 IF (p_headers_rec.shipping_control.COUNT > 0) THEN
2244 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.shipping_control(1)='||p_headers_rec.shipping_control(1)); END IF;
2245 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2246 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2247 END IF;
2248
2249 l_progress := '020';
2250 -- SQL What: Bulk validate shipping_control against AP_TERMS.
2251 -- Get the errored rows into GT table.
2252 -- SQL Why : It will be used to mark the record in plsql table as error.
2253 -- SQL Join: ship_via_lookup_code
2254 FORALL i IN 1 .. p_headers_rec.shipping_control.COUNT
2255 INSERT INTO PO_SESSION_GT(key,
2256 num1,
2257 num2,
2258 char1)
2259 SELECT p_key
2260 , l_subscript_array(i)
2261 , p_headers_rec.interface_header_id(i)
2262 , p_headers_rec.shipping_control(i)
2263 FROM DUAL
2264 WHERE --p_headers_rec.has_errors(i) = 'N'
2265 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
2266 AND p_headers_rec.shipping_control(i) IS NOT NULL
2267 AND NOT EXISTS(
2268 SELECT 1
2269 FROM PO_LOOKUP_CODES PLC
2270 WHERE p_headers_rec.shipping_control(i) = PLC.lookup_code
2271 AND PLC.lookup_type = 'SHIPPING CONTROL'
2272 AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
2273
2274 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2275
2276 l_progress := '030';
2277 -- SQL What: Transfer from session GT table to local arrays
2278 -- SQL Why : It will be used to mark the error records.
2279 -- SQL Join: key
2280 DELETE FROM PO_SESSION_GT
2281 WHERE key = p_key
2282 RETURNING num1, num2, char1
2283 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_shipping_control_list;
2284
2285 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2286
2287 l_progress := '040';
2288 -- Mark the error records
2289 FOR i IN 1 .. l_indexes.COUNT
2290 LOOP
2291 l_index := l_indexes(i);
2292 p_headers_rec.has_errors(l_index) := 'Y';
2293
2294 -- ECO bug 5584556: id-to-name conversion
2295 l_progress := '050';
2296 get_lookup_name_from_code
2297 (
2298 p_po_lookup_code => l_err_shipping_control_list(i)
2299 , p_po_lookup_type => 'SHIPPING CONTROL'
2300 , x_lookup_meaning => l_lookup_meaning
2301 );
2302 -- ECO bug 5584556: End
2303
2304 l_progress := '060';
2305 -- Add error message into INTERFACE_ERRORS table
2306 -- ICX_CAT_INVALID_SHIPPING_CTRL:
2307 -- "Default shipping control (VALUE) is inactive or invalid."
2308 PO_R12_CAT_UPG_UTL.add_fatal_error(
2309 p_interface_header_id => l_interface_header_ids(i),
2310 p_error_message_name => 'ICX_CAT_INVALID_SHIPPING_CTRL', -- ECO bug 5584556 'ICX_CAT_INVALID_SHIP_CONTROL'
2311 p_table_name => 'PO_HEADERS_INTERFACE',
2312 p_column_name => 'SHIPPING_CONTROL',
2313 p_column_value => l_lookup_meaning, -- ECO bug 5584556
2314 p_token1_name => 'VALUE',
2315 p_token1_value => l_err_shipping_control_list(i)
2316 );
2317 END LOOP;
2318
2319 l_progress := '070';
2320 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2321 EXCEPTION
2322 WHEN OTHERS THEN
2323 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2324 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2325 END validate_shipping_control;
2326
2327 -- Bug 5461235: Start
2328 --------------------------------------------------------------------------------
2329 --Start of Comments
2330 --Name: validate_rate_type_code
2331 --Pre-reqs:
2332 -- The iP catalog data is populated in input pl/sql tables.
2333 --Modifies:
2334 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2335 -- b) FND_MSG_PUB on unhandled exceptions.
2336 --Locks:
2337 -- None.
2338 --Function:
2339 -- Validates rate_type_code against GL_DAILY_CONVERSION_TYPES_V.
2340 --Parameters:
2341 --IN:
2342 --p_key
2343 -- Key used to access records in PO_SESSION_GT table.
2344 --IN/OUT:
2345 --p_headers_rec
2346 -- A record of plsql tables containing a batch of headers. If this validation
2347 -- fails, the 'has_errors' column is set to 'Y'.
2348 --OUT:
2349 --End of Comments
2350 --------------------------------------------------------------------------------
2351 PROCEDURE validate_rate_type_code
2352 (
2353 p_key IN NUMBER,
2354 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
2355 )
2356 IS
2357 l_api_name CONSTANT VARCHAR2(30) := 'validate_rate_type_code';
2358 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2359 l_progress VARCHAR2(3) := '000';
2360
2361 l_size NUMBER := p_headers_rec.interface_header_id.COUNT;
2362
2363 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2364 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2365 l_err_rate_type_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR30;
2366
2367 l_index NUMBER;
2368 i NUMBER;
2369 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2370 BEGIN
2371 l_progress := '010';
2372 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2373
2374 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.rate_type_code.COUNT);
2375
2376 IF (p_headers_rec.rate_type_code.COUNT > 0) THEN
2377 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.rate_type_code(1)='||p_headers_rec.rate_type_code(1)); END IF;
2378 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2379 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2380 END IF;
2381
2382 -- ECO bug 5584556: added new message
2383 -- Add a separate message for NULL value of rate_type_code
2384 l_progress := '020';
2385 FOR i IN 1 .. p_headers_rec.rate_type_code.COUNT
2386 LOOP
2387 IF ( (p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create)
2388 AND (p_headers_rec.currency_code(i) <> PO_R12_CAT_UPG_PVT.g_sys.currency_code)
2389 AND (p_headers_rec.rate_type_code(i) IS NULL) ) THEN
2390 -- ICX_CAT_RATE_TYPE_REQD:
2391 -- "The system cannot obtain the default value for the rate type."
2392 PO_R12_CAT_UPG_UTL.add_fatal_error(
2393 p_interface_header_id => p_headers_rec.interface_header_id(i),
2394 p_error_message_name => 'ICX_CAT_RATE_TYPE_REQD',
2395 p_table_name => 'PO_HEADERS_INTERFACE',
2396 p_column_name => 'RATE_TYPE',
2397 p_column_value => NULL,
2398 -- debug purposes only
2399 p_token1_name => 'HEADER_CURRENCY',
2400 p_token1_value => p_headers_rec.currency_code(i),
2401 p_token2_name => 'FUNCTIONAL_CURRENCY',
2402 p_token2_value => PO_R12_CAT_UPG_PVT.g_sys.currency_code
2403 );
2404 END IF;
2405 END LOOP;
2406 -- ECO bug 5584556: End
2407
2408 l_progress := '030';
2409 -- SQL What: Bulk validate rate_type_code against GL_DAILY_CONVERSION_TYPES_V.
2410 -- Get the errored rows into GT table.
2411 -- SQL Why : It will be used to mark the record in plsql table as error.
2412 -- SQL Join: rate_type
2413 FORALL i IN 1 .. p_headers_rec.rate_type_code.COUNT
2414 INSERT INTO PO_SESSION_GT(key,
2415 num1,
2416 num2,
2417 char1)
2418 SELECT p_key
2419 , l_subscript_array(i)
2420 , p_headers_rec.interface_header_id(i)
2421 , p_headers_rec.rate_type_code(i)
2422 FROM DUAL
2423 WHERE --p_headers_rec.has_errors(i) = 'N'
2424 p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create --Bug#5018883
2425 AND p_headers_rec.currency_code(i) <> PO_R12_CAT_UPG_PVT.g_sys.currency_code
2426 AND p_headers_rec.rate_type_code(i) IS NOT NULL
2427 AND NOT EXISTS(
2428 SELECT 'Rate type exists'
2429 FROM GL_DAILY_CONVERSION_TYPES_V GLDCT
2430 WHERE GLDCT.conversion_type = p_headers_rec.rate_type_code(i));
2431
2432 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2433
2434 l_progress := '040';
2435 -- SQL What: Transfer from session GT table to local arrays
2436 -- SQL Why : It will be used to mark the error records.
2437 -- SQL Join: key
2438 DELETE FROM PO_SESSION_GT
2439 WHERE key = p_key
2440 RETURNING num1, num2, char1
2441 BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_rate_type_code_list;
2442
2443 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2444
2445 l_progress := '050';
2446 -- Mark the error records
2447 FOR i IN 1 .. l_indexes.COUNT
2448 LOOP
2449 l_index := l_indexes(i);
2450 p_headers_rec.has_errors(l_index) := 'Y';
2451
2452 -- Add error message into INTERFACE_ERRORS table
2453 -- ICX_CAT_INVALID_RATE_TYPE:
2454 -- "Default rate type (VALUE) is inactive or invalid."
2455 PO_R12_CAT_UPG_UTL.add_fatal_error(
2456 p_interface_header_id => l_interface_header_ids(i),
2457 p_error_message_name => 'ICX_CAT_INVALID_RATE_TYPE',
2458 p_table_name => 'PO_HEADERS_INTERFACE',
2459 p_column_name => 'RATE_TYPE',
2460 p_column_value => l_err_rate_type_code_list(i),
2461 p_token1_name => 'VALUE',
2462 p_token1_value => l_err_rate_type_code_list(i)
2463 );
2464 END LOOP;
2465
2466 l_progress := '060';
2467 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2468 EXCEPTION
2469 WHEN OTHERS THEN
2470 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2471 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2472 END validate_rate_type_code;
2473 -- Bug 5461235: End
2474
2475 --------------------------------------------------------------------------------
2476 --Start of Comments
2477 --Name: validate_headers
2478 --Pre-reqs:
2479 -- The iP catalog data is populated in input pl/sql tables.
2480 --Modifies:
2481 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2482 -- b) FND_MSG_PUB on unhandled exceptions.
2483 --Locks:
2484 -- None.
2485 --Function:
2486 -- This is the main function to validate headers. It calls the following
2487 -- procedures:
2488 --
2489 -- validate_buyer
2490 -- validate_currency_code
2491 -- validate_vendor_info
2492 -- validate_expiration_date
2493 -- validate_ship_to_location_id
2494 -- validate_bill_to_location_id
2495 -- validate_fob_lookup_code
2496 -- validate_ship_via_luc
2497 -- validate_freight_terms
2498 -- validate_terms_id
2499 -- validate_shipping_control
2500 -- validate_rate_type_code
2501 --
2502 --Parameters:
2503 --IN/OUT:
2504 --p_headers_rec
2505 -- A record of plsql tables containing a batch of headers. If this validation
2506 -- fails, the 'has_errors' column is set to 'Y'.
2507 --OUT:
2508 --End of Comments
2509 --------------------------------------------------------------------------------
2510 PROCEDURE validate_headers
2511 (
2512 p_headers_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_headers_type
2513 )
2514 IS
2515 l_api_name CONSTANT VARCHAR2(30) := 'validate_headers';
2516 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2517 l_progress VARCHAR2(3) := '000';
2518
2519 l_key PO_SESSION_GT.key%TYPE;
2520 i NUMBER;
2521 BEGIN
2522 l_progress := '010';
2523 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2524
2525 -- pick a new key from temp table which will be used in all validate logic
2526 SELECT PO_SESSION_GT_S.nextval
2527 INTO l_key
2528 FROM DUAL;
2529
2530 l_progress := '020';
2531 -- validate that agent_id is not null
2532 validate_buyer
2533 (
2534 p_headers_rec => p_headers_rec
2535 );
2536
2537 l_progress := '030';
2538 -- validate currency_code not null and against FND_CURRENCIES.
2539 validate_currency_code
2540 (
2541 p_key => l_key,
2542 p_headers_rec => p_headers_rec
2543 );
2544
2545 l_progress := '040';
2546 -- validate vendorId is Not Null
2547 -- validate vendorSiteId is Not Null
2548 -- validate vendor_id using PO_SUPPLIERS_VAL_V
2549 -- validate vendor_site_id using PO_SUPPLIER_SITES_VAL_V
2550 -- validate vendor_contact_id using PO_VENDOR_CONTACTS
2551 -- validate vendor site CCR if approval status is APPROVED.
2552 validate_vendor_info
2553 (
2554 p_key => l_key,
2555 p_headers_rec => p_headers_rec
2556 );
2557
2558 l_progress := '050';
2559 -- validate that end_date is greater than the start_date
2560 validate_expiration_date
2561 (
2562 p_headers_rec => p_headers_rec
2563 );
2564
2565 l_progress := '060';
2566 -- validate ship_to_location_id not null and against HR_LOCATIONS.
2567 validate_ship_to_location_id
2568 (
2569 p_key => l_key,
2570 p_headers_rec => p_headers_rec
2571 );
2572
2573 l_progress := '070';
2574 -- validate bill_to_location_id not null and against HR_LOCATIONS.
2575 validate_bill_to_location_id
2576 (
2577 p_key => l_key,
2578 p_headers_rec => p_headers_rec
2579 );
2580
2581 l_progress := '080';
2582 validate_fob_lookup_code
2583 (
2584 p_key => l_key,
2585 p_headers_rec => p_headers_rec
2586 );
2587
2588 l_progress := '090';
2589 validate_ship_via_luc
2590 (
2591 p_key => l_key,
2592 p_headers_rec => p_headers_rec
2593 );
2594
2595 l_progress := '100';
2596 validate_freight_terms
2597 (
2598 p_key => l_key,
2599 p_headers_rec => p_headers_rec
2600 );
2601
2602 l_progress := '110';
2603 validate_terms_id
2604 (
2605 p_key => l_key,
2606 p_headers_rec => p_headers_rec
2607 );
2608
2609 l_progress := '120';
2610 validate_shipping_control
2611 (
2612 p_key => l_key,
2613 p_headers_rec => p_headers_rec
2614 );
2615
2616 l_progress := '130';
2617 -- Bug 5461235
2618 validate_rate_type_code
2619 (
2620 p_key => l_key,
2621 p_headers_rec => p_headers_rec
2622 );
2623
2624 l_progress := '140';
2625 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2626 EXCEPTION
2627 WHEN OTHERS THEN
2628 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2629 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2630 END validate_headers;
2631
2632 --------------------------------------------------------------------------------
2633 --Start of Comments
2634 --Name: validate_item
2635 --Pre-reqs:
2636 -- The iP catalog data is populated in input pl/sql tables.
2637 --Modifies:
2638 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2639 -- b) FND_MSG_PUB on unhandled exceptions.
2640 --Locks:
2641 -- None.
2642 --Function:
2643 -- Checks that if item id is not null, that it exists in
2644 -- MTL_SYSTEM_ITEMS table.
2645 --Parameters:
2646 --IN:
2647 --p_key
2648 -- Key used to access records in PO_SESSION_GT table.
2649 --IN/OUT:
2650 --p_lines_rec
2651 -- A record of plsql tables containing a batch of lines. If this validation
2652 -- fails, the 'has_errors' column is set to 'Y'.
2653 --OUT:
2654 --End of Comments
2655 --------------------------------------------------------------------------------
2656 PROCEDURE validate_item
2657 (
2658 p_key IN NUMBER,
2659 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
2660 )
2661 IS
2662 l_api_name CONSTANT VARCHAR2(30) := 'validate_item';
2663 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2664 l_progress VARCHAR2(3) := '000';
2665
2666 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
2667
2668 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2669 l_interface_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2670 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2671 l_item_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2672
2673 l_index NUMBER;
2674 i NUMBER;
2675 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2676 BEGIN
2677 l_progress := '010';
2678 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2679
2680 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2681
2682 l_progress := '020';
2683 -- SQL What: Bulk validate item_id. If item id is not null, it has to exist
2684 -- in mtl_system_items table.
2685 -- Insert the error rows into GT table.
2686 -- SQL Why : It will be used to mark the record in plsql table as error.
2687 -- SQL Join: item_id
2688 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
2689 INSERT INTO PO_SESSION_GT(key,
2690 num1,
2691 num2,
2692 num3,
2693 num4)
2694 SELECT p_key
2695 , l_subscript_array(i)
2696 , p_lines_rec.interface_header_id(i)
2697 , p_lines_rec.interface_line_id(i)
2698 , p_lines_rec.item_id(i)
2699 FROM PO_LINE_TYPES_B PLT
2700 WHERE --p_lines_rec.has_errors(i) = 'N'
2701 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
2702 AND p_lines_rec.item_id(i) is not null
2703 AND p_lines_rec.line_type_id(i) is not null
2704 AND p_lines_rec.line_type_id(i)= PLT.line_type_id
2705 AND PLT.outside_operation_flag is not null
2706 AND NOT EXISTS (SELECT 1
2707 FROM MTL_SYSTEM_ITEMS MSI
2708 WHERE MSI.inventory_item_id = p_lines_rec.item_id(i)
2709 AND MSI.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
2710 AND MSI.enabled_flag = 'Y'
2711 AND MSI.purchasing_item_flag = 'Y'
2712 AND MSI.purchasing_enabled_flag = 'Y'
2713 AND MSI.outside_operation_flag = PLT.outside_operation_flag
2714 AND TRUNC(nvl(MSI.start_date_active, sysdate))<= TRUNC(sysdate)
2715 AND TRUNC(nvl(MSI.end_date_active, sysdate)) >= TRUNC(sysdate));
2716
2717 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2718
2719 l_progress := '030';
2720 -- SQL What: Transfer from session GT table to local arrays
2721 -- SQL Why : It will be used to mark the error records.
2722 -- SQL Join: key
2723 DELETE FROM PO_SESSION_GT
2724 WHERE key = p_key
2725 RETURNING num1, num2, num3, num4
2726 BULK COLLECT INTO l_indexes, l_interface_header_ids,
2727 l_interface_line_ids, l_item_id_list;
2728
2729 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2730
2731 l_progress := '040';
2732 -- Mark the error records
2733 FOR i IN 1 .. l_indexes.COUNT
2734 LOOP
2735 l_index := l_indexes(i);
2736
2737 -- For bulkloaded items, there is no issue since there is no item number.
2738 -- For extracted item, the item is in a PO document. Whatever happens to
2739 -- the item, the document is still valid. User is not going to fix
2740 -- existing PO documents for invalid items. Keep item as it is and dont
2741 -- give any exception.
2742 --p_lines_rec.has_errors(l_index) := 'Y';
2743
2744 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring validation error in ITEM_ID='||l_item_id_list(i)); END IF;
2745
2746 -- Add error message into INTERFACE_ERRORS table
2747 -- PO_PDOI_INVALID_ITEM_ID:
2748 -- "ITEM ID (VALUE =VALUE) is not a valid purchasable item."
2749 --PO_R12_CAT_UPG_UTL.add_fatal_error(
2750 -- p_interface_header_id => l_interface_header_ids(i),
2751 -- p_interface_line_id => l_interface_line_ids(i),
2752 -- p_error_message_name => 'PO_PDOI_INVALID_ITEM_ID',
2753 -- p_table_name => 'PO_LINES_INTERFACE',
2754 -- p_column_name => 'ITEM_ID',
2755 -- p_column_value => l_item_id_list(i),
2756 -- p_token1_name => 'VALUE',
2757 -- p_token1_value => l_item_id_list(i)
2758 -- );
2759 END LOOP;
2760
2761 l_progress := '050';
2762 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2763 EXCEPTION
2764 WHEN OTHERS THEN
2765 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2766 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2767 END validate_item;
2768
2769
2770 --------------------------------------------------------------------------------
2771 --Start of Comments
2772 --Name: validate_item_description
2773 --Pre-reqs:
2774 -- The iP catalog data is populated in input pl/sql tables.
2775 --Modifies:
2776 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2777 -- b) FND_MSG_PUB on unhandled exceptions.
2778 --Locks:
2779 -- None.
2780 --Function:
2781 -- Make sure that the item_description is populated, and also need to find out
2782 -- if it is different from what is setup for the item.
2783 --Parameters:
2784 --IN:
2785 --p_key
2786 -- Key used to access records in PO_SESSION_GT table.
2787 --IN/OUT:
2788 --p_lines_rec
2789 -- A record of plsql tables containing a batch of lines. If this validation
2790 -- fails, the 'has_errors' column is set to 'Y'.
2791 --OUT:
2792 --End of Comments
2793 --------------------------------------------------------------------------------
2794 PROCEDURE validate_item_description
2795 (
2796 p_key IN NUMBER,
2797 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
2798 )
2799 IS
2800 l_api_name CONSTANT VARCHAR2(30) := 'validate_item_description';
2801 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2802 l_progress VARCHAR2(3) := '000';
2803
2804 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
2805
2806 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2807 l_interface_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2808 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2809 l_item_description_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR240;
2810
2811 l_index NUMBER;
2812 i NUMBER;
2813 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2814 BEGIN
2815 l_progress := '010';
2816 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2817
2818 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2819
2820 l_progress := '020';
2821 -- SQL What: Bulk validate item_description. Make sure that the
2822 -- item_description is populated, and also need to find out if it
2823 -- is different from what is setup for the item.
2824 -- Insert the error rows into GT table.
2825 -- SQL Why : It will be used to mark the record in plsql table as error.
2826 -- SQL Join: item_id
2827 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
2828 INSERT INTO PO_SESSION_GT(key,
2829 num1,
2830 num2,
2831 num3,
2832 char1)
2833 SELECT p_key
2834 , l_subscript_array(i)
2835 , p_lines_rec.interface_header_id(i)
2836 , p_lines_rec.interface_line_id(i)
2837 , p_lines_rec.item_description(i)
2838 FROM DUAL
2839 WHERE --p_lines_rec.has_errors(i) = 'N'
2840 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
2841 AND (p_lines_rec.item_description(i) is null OR
2842 (p_lines_rec.item_id(i) is not null AND
2843 EXISTS (SELECT 1
2844 FROM MTL_SYSTEM_ITEMS MSI
2845 WHERE MSI.inventory_item_id = p_lines_rec.item_id(i)
2846 AND MSI.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
2847 AND MSI.allow_item_desc_update_flag = 'N'
2848 AND p_lines_rec.item_description(i) <> MSI.description)));
2849 --AND create_or_update_item_flag = 'N'))); its a parameter to PDOI, For catalog migration, it would always be 'N'
2850
2851 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2852
2853 l_progress := '030';
2854 -- SQL What: Transfer from session GT table to local arrays
2855 -- SQL Why : It will be used to mark the error records.
2856 -- SQL Join: key
2857 DELETE FROM PO_SESSION_GT
2858 WHERE key = p_key
2859 RETURNING num1, num2, num3, char1
2860 BULK COLLECT INTO l_indexes, l_interface_header_ids,
2861 l_interface_line_ids, l_item_description_list;
2862
2863 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2864
2865 l_progress := '040';
2866 -- Mark the error records
2867 FOR i IN 1 .. l_indexes.COUNT
2868 LOOP
2869 l_index := l_indexes(i);
2870
2871 -- Do not give an error. In this case, ignore the description that comes from iP.
2872 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring the validation error of item description for ITEM_ID='||p_lines_rec.item_id(l_index)||'. Also ignoring the item description from interface table'); END IF;
2873 p_lines_rec.item_description(l_index) := NULL;
2874
2875 --p_lines_rec.has_errors(l_index) := 'Y';
2876
2877 -- Add error message into INTERFACE_ERRORS table
2878 -- PO_PDOI_DIFF_ITEM_DESC:
2879 -- "Pre-defined item description cannot be changed for this item."
2880 --PO_R12_CAT_UPG_UTL.add_fatal_error(
2881 -- p_interface_header_id => l_interface_header_ids(i),
2882 -- p_interface_line_id => l_interface_line_ids(i),
2883 -- p_error_message_name => 'PO_PDOI_DIFF_ITEM_DESC',
2884 -- p_table_name => 'PO_LINES_INTERFACE',
2885 -- p_column_name => 'ITEM_DESCRIPTION',
2886 -- p_column_value => l_item_description_list(i)
2887 -- );
2888 END LOOP;
2889
2890 l_progress := '050';
2891 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2892 EXCEPTION
2893 WHEN OTHERS THEN
2894 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2895 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2896 END validate_item_description;
2897
2898 --------------------------------------------------------------------------------
2899 --Start of Comments
2900 --Name: validate_uom
2901 --Pre-reqs:
2902 -- The iP catalog data is populated in input pl/sql tables.
2903 --Modifies:
2904 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
2905 -- b) FND_MSG_PUB on unhandled exceptions.
2906 --Locks:
2907 -- None.
2908 --Function:
2909 -- check to see if unit_meas_lookup_code is valid in mtl_item_uoms_view
2910 --Parameters:
2911 --IN:
2912 --p_key
2913 -- Key used to access records in PO_SESSION_GT table.
2914 --IN/OUT:
2915 --p_lines_rec
2916 -- A record of plsql tables containing a batch of lines. If this validation
2917 -- fails, the 'has_errors' column is set to 'Y'.
2918 --OUT:
2919 --End of Comments
2920 --------------------------------------------------------------------------------
2921 PROCEDURE validate_uom
2922 (
2923 p_key IN NUMBER,
2924 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
2925 )
2926 IS
2927 l_api_name CONSTANT VARCHAR2(30) := 'validate_uom';
2928 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2929 l_progress VARCHAR2(3) := '000';
2930
2931 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
2932
2933 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2934 l_interface_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2935 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2936 l_unit_of_measure_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR25;
2937 l_uom_code_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR3;
2938
2939 l_index NUMBER;
2940 i NUMBER;
2941 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2942 BEGIN
2943 l_progress := '010';
2944 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2945
2946 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2947
2948 IF (p_lines_rec.unit_of_measure.COUNT > 0) THEN
2949 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_lines_rec.unit_of_measure(1)='||p_lines_rec.unit_of_measure(1)); END IF;
2950 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_lines_rec.uom_code(1)='||p_lines_rec.uom_code(1)); END IF;
2951 END IF;
2952
2953 -- ECO bug 5584556: Add new messages
2954 -- uom_code must not be NULL. iP populates uom_code.
2955 FOR i IN 1 .. p_lines_rec.uom_code.COUNT
2956 LOOP
2957 l_progress := '050';
2958 IF (--p_lines_rec.has_errors(i) = 'N' AND
2959
2960 -- Bug 5060582: UNIT_OF_MEASURE is updatable when CREATED_BY = -12.
2961 -- If it is specified as NULL in INTF table, then the column
2962 -- remains unchanged in TXN table. This is handled via a DECODE in the
2963 -- UPDATE statement, later in the flow. So allow NULL value in UPDATE flow
2964 -- where CREATED_BY is NOT -12.
2965 --p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
2966 ( p_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create
2967 OR
2968 (p_lines_rec.action(i) = 'UPDATE' AND
2969 p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
2970 )
2971 AND
2972 p_lines_rec.uom_code(i) IS NULL) THEN
2973
2974 l_progress := '060';
2975 p_lines_rec.has_errors(i) := 'Y';
2976
2977 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
2978
2979 -- Add error message into INTERFACE_ERRORS table
2980 -- ECO bug 5584556: Add new messages
2981 -- ICX_CAT_UOM_CODE_REQD:
2982 -- "Unit of measure is missing for one or more lines."
2983 PO_R12_CAT_UPG_UTL.add_fatal_error(
2984 p_interface_header_id => p_lines_rec.interface_header_id(i),
2985 p_interface_line_id => p_lines_rec.interface_line_id(i),
2986 p_error_message_name => 'ICX_CAT_UOM_CODE_REQD',
2987 p_table_name => 'PO_LINES_INTERFACE',
2988 p_column_name => 'UOM_CODE',
2989 p_column_value => p_lines_rec.uom_code(i)
2990 );
2991 END IF;
2992 END LOOP;
2993 -- ECO bug 5584556: End
2994
2995 -- Derive unit_of_measure from uom_code
2996 FOR i IN 1 .. p_lines_rec.uom_code.COUNT
2997 LOOP
2998 l_progress := '020';
2999 IF (--p_lines_rec.has_errors(i) = 'N' AND
3000 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
3001 p_lines_rec.unit_of_measure(i) IS NULL AND
3002 p_lines_rec.uom_code(i) IS NOT NULL) THEN
3003
3004 l_progress := '030';
3005 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Deriving unit_of_measure for uom_code='||p_lines_rec.uom_code(i)); END IF;
3006
3007 BEGIN
3008 SELECT unit_of_measure
3009 INTO p_lines_rec.unit_of_measure(i)
3010 FROM PO_UNITS_OF_MEASURE_VAL_V
3011 WHERE uom_code = p_lines_rec.uom_code(i)
3012 -- ECO bug 5584556: This validation is already done above.
3013 -- So skip these cases.
3014 AND uom_code IS NOT NULL;
3015 -- ECO bug 5584556: End
3016 EXCEPTION
3017 WHEN OTHERS THEN
3018 p_lines_rec.unit_of_measure(i) := NULL;
3019 END;
3020
3021 l_progress := '040';
3022 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'unit_of_measure='||p_lines_rec.unit_of_measure(i)); END IF;
3023 END IF;
3024 END LOOP;
3025
3026 -- unit_of_measure must not be NULL
3027 FOR i IN 1 .. p_lines_rec.unit_of_measure.COUNT
3028 LOOP
3029 l_progress := '050';
3030 IF (--p_lines_rec.has_errors(i) = 'N' AND
3031
3032 -- Bug 5060582: UNIT_OF_MEASURE is updatable when CREATED_BY = -12.
3033 -- If it is specified as NULL in INTF table, then the column
3034 -- remains unchanged in TXN table. This is handled via a DECODE in the
3035 -- UPDATE statement, later in the flow. So allow NULL value in UPDATE flow
3036 -- where CREATED_BY is NOT -12.
3037 --p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
3038 ( p_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create
3039 OR
3040 (p_lines_rec.action(i) = 'UPDATE' AND
3041 p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
3042 )
3043 AND p_lines_rec.unit_of_measure(i) IS NULL
3044 -- ECO bug 5584556: This validation is already done above.
3045 -- So skip these cases.
3046 AND p_lines_rec.uom_code(i) IS NOT NULL) THEN
3047 -- ECO bug 5584556: End
3048
3049 l_progress := '060';
3050 p_lines_rec.has_errors(i) := 'Y';
3051
3052 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3053
3054 -- Add error message into INTERFACE_ERRORS table
3055 -- ECO bug 5584556: Add new messages
3056 -- ICX_CAT_INVALID_UOM_CODE:
3057 -- "The following unit of measure codes are inactive or invalid: VALUE, VALUE"
3058 PO_R12_CAT_UPG_UTL.add_fatal_error(
3059 p_interface_header_id => p_lines_rec.interface_header_id(i),
3060 p_interface_line_id => p_lines_rec.interface_line_id(i),
3061 p_error_message_name => 'ICX_CAT_INVALID_UOM_CODE',
3062 p_table_name => 'PO_LINES_INTERFACE',
3063 p_column_name => 'UOM_CODE',
3064 p_column_value => p_lines_rec.uom_code(i),
3065 p_token1_name => 'VALUE',
3066 p_token1_value => p_lines_rec.uom_code(i)
3067 );
3068 END IF;
3069 END LOOP;
3070
3071 l_progress := '070';
3072 -- SQL What: Bulk validate UOM. Check if unit_meas_lookup_code is valid
3073 -- in MTL_ITEM_UOMS_VIEW.
3074 -- Insert the error rows into GT table.
3075 -- SQL Why : It will be used to mark the record in plsql table as error.
3076 -- SQL Join: item_id, unit_of_measure, inv_org_id
3077 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
3078 INSERT INTO PO_SESSION_GT(key,
3079 num1,
3080 num2,
3081 num3,
3082 char1,
3083 char2)
3084 SELECT p_key
3085 , l_subscript_array(i)
3086 , p_lines_rec.interface_header_id(i)
3087 , p_lines_rec.interface_line_id(i)
3088 , p_lines_rec.unit_of_measure(i)
3089 , p_lines_rec.uom_code(i)
3090 FROM DUAL
3091 WHERE --p_lines_rec.has_errors(i) = 'N'
3092 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
3093 AND p_lines_rec.unit_of_measure(i) is not null
3094 AND p_lines_rec.uom_code(i) is not null
3095 AND ( (p_lines_rec.item_id(i) is not null
3096 AND NOT EXISTS (SELECT 1
3097 FROM MTL_ITEM_UOMS_VIEW MIUV
3098 WHERE MIUV.inventory_item_id = p_lines_rec.item_id(i)
3099 AND MIUV.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
3100 AND MIUV.unit_of_measure = p_lines_rec.unit_of_measure(i)))
3101 OR
3102 (p_lines_rec.item_id(i) is null
3103 AND NOT EXISTS (SELECT 1
3104 FROM MTL_UNITS_OF_MEASURE MUOM
3105 WHERE MUOM.unit_of_measure = p_lines_rec.unit_of_measure(i)
3106 AND sysdate < NVL(disable_date, sysdate+1))));
3107
3108 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3109
3110 l_progress := '080';
3111 -- SQL What: Transfer from session GT table to local arrays
3112 -- SQL Why : It will be used to mark the error records.
3113 -- SQL Join: key
3114 DELETE FROM PO_SESSION_GT
3115 WHERE key = p_key
3116 RETURNING num1, num2, num3, char1, char2
3117 BULK COLLECT INTO l_indexes, l_interface_header_ids,
3118 l_interface_line_ids, l_unit_of_measure_list, l_uom_code_list;
3119
3120 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3121
3122 l_progress := '090';
3123 -- Mark the error records
3124 FOR i IN 1 .. l_indexes.COUNT
3125 LOOP
3126 l_index := l_indexes(i);
3127 p_lines_rec.has_errors(l_index) := 'Y';
3128
3129 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: unit_of_measure='||l_unit_of_measure_list(i)||', uom_code='||l_uom_code_list(i)); END IF;
3130
3131 -- Add error message into INTERFACE_ERRORS table
3132 -- ICX_CAT_INVALID_UOM_CODE:
3133 -- "The following unit of measure codes are inactive or invalid: VALUE, VALUE"
3134 PO_R12_CAT_UPG_UTL.add_fatal_error(
3135 p_interface_header_id => l_interface_header_ids(i),
3136 p_interface_line_id => l_interface_line_ids(i),
3137 --p_error_message_name => 'PO_PDOI_INVALID_UOM_CODE',
3138 p_error_message_name => 'ICX_CAT_INVALID_UOM_CODE',
3139 p_table_name => 'PO_LINES_INTERFACE',
3140 p_column_name => 'UOM_CODE',
3141 p_column_value => l_uom_code_list(i),
3142 p_token1_name => 'VALUE',
3143 p_token1_value => l_uom_code_list(i)
3144 );
3145 END LOOP;
3146
3147 l_progress := '100';
3148 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3149 EXCEPTION
3150 WHEN OTHERS THEN
3151 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3152 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3153 END validate_uom;
3154
3155 --------------------------------------------------------------------------------
3156 --Start of Comments
3157 --Name: validate_item_revision
3158 --Pre-reqs:
3159 -- The iP catalog data is populated in input pl/sql tables.
3160 --Modifies:
3161 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
3162 -- b) FND_MSG_PUB on unhandled exceptions.
3163 --Locks:
3164 -- None.
3165 --Function:
3166 -- If order_type_lookup_code is FIXED PRICE or RATE, or item id is null,
3167 -- then item revision has to be NULL
3168 -- Check to see if the item_revision exists in mtl_item_revisions table
3169 --Parameters:
3170 --IN:
3171 --p_key
3172 -- Key used to access records in PO_SESSION_GT table.
3173 --IN/OUT:
3174 --p_lines_rec
3175 -- A record of plsql tables containing a batch of lines. If this validation
3176 -- fails, the 'has_errors' column is set to 'Y'.
3177 --OUT:
3178 --End of Comments
3179 --------------------------------------------------------------------------------
3180 PROCEDURE validate_item_revision
3181 (
3182 p_key IN NUMBER,
3183 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
3184 )
3185 IS
3186 l_api_name CONSTANT VARCHAR2(30) := 'validate_item_revision';
3187 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3188 l_progress VARCHAR2(3) := '000';
3189
3190 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
3191
3192 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3193 l_interface_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3194 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3195 l_item_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3196 l_item_revision_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR3;
3197
3198 l_index NUMBER;
3199 i NUMBER;
3200 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3201 BEGIN
3202 l_progress := '010';
3203 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3204
3205 -- if item id is null, then item revision has to be NULL
3206 FOR i IN 1 .. p_lines_rec.item_revision.COUNT
3207 LOOP
3208 l_progress := '020';
3209 IF (--p_lines_rec.has_errors(i) = 'N' AND
3210 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
3211 p_lines_rec.item_id(i) IS NULL AND
3212 p_lines_rec.item_revision(i) IS NOT NULL) THEN
3213
3214 l_progress := '030';
3215
3216 -- If this happens, ignore the revision for a non-inventory item. Dont give any exception.
3217 --p_lines_rec.has_errors(i) := 'Y';
3218 p_lines_rec.item_revision(i) := NULL;
3219
3220 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring item_revision for interface_lien-id='||p_lines_rec.interface_line_id(i)||' because item_id is NULL'); END IF;
3221
3222 -- Add error message into INTERFACE_ERRORS table
3223 --PO_R12_CAT_UPG_UTL.add_fatal_error(
3224 -- p_interface_header_id => p_lines_rec.interface_header_id(i),
3225 -- p_interface_line_id => p_lines_rec.interface_line_id(i),
3226 -- p_error_message_name => 'PO_COLUMN_NOT_NULL',
3227 -- p_table_name => 'PO_LINES_INTERFACE',
3228 -- p_column_name => 'ITEM_REVISION',
3229 -- p_column_value => p_lines_rec.item_revision(i)
3230 -- );
3231 END IF;
3232 END LOOP;
3233
3234 l_progress := '040';
3235 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
3236
3237 l_progress := '050';
3238 -- SQL What: Bulk validate item revision. Check to see if the item_revision
3239 -- exists in MTL_ITEM_REVISIONS table.
3240 -- Insert the error rows into GT table.
3241 -- SQL Why : It will be used to mark the record in plsql table as error.
3242 -- SQL Join: item_id, item_revision
3243 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
3244 INSERT INTO PO_SESSION_GT(key,
3245 num1,
3246 num2,
3247 num3,
3248 num4,
3249 char1)
3250 SELECT p_key
3251 , l_subscript_array(i)
3252 , p_lines_rec.interface_header_id(i)
3253 , p_lines_rec.interface_line_id(i)
3254 , p_lines_rec.item_id(i)
3255 , p_lines_rec.item_revision(i)
3256 FROM DUAL
3257 WHERE --p_lines_rec.has_errors(i) = 'N'
3258 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
3259 AND p_lines_rec.item_revision(i) is not null
3260 AND p_lines_rec.item_id(i) is not null
3261 AND NOT EXISTS (SELECT 1
3262 FROM MTL_ITEM_REVISIONS MIR
3263 WHERE MIR.inventory_item_id = p_lines_rec.item_id(i)
3264 AND MIR.revision = p_lines_rec.item_revision(i));
3265
3266 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3267
3268 l_progress := '060';
3269 -- SQL What: Transfer from session GT table to local arrays
3270 -- SQL Why : It will be used to mark the error records.
3271 -- SQL Join: key
3272 DELETE FROM PO_SESSION_GT
3273 WHERE key = p_key
3274 RETURNING num1, num2, num3, num4, char1
3275 BULK COLLECT INTO l_indexes, l_interface_header_ids,
3276 l_interface_line_ids, l_item_id_list, l_item_revision_list;
3277
3278 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3279
3280 l_progress := '070';
3281 -- Mark the error records
3282 FOR i IN 1 .. l_indexes.COUNT
3283 LOOP
3284 l_index := l_indexes(i);
3285
3286 -- Same as item ID. Keep item as it is and dont give any exception
3287 --p_lines_rec.has_errors(l_index) := 'Y';
3288 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring the validation error of Item Revision '|| l_item_revision_list(i) ||' for ITEM_ID='||l_item_id_list(i)); END IF;
3289
3290 -- Add error message into INTERFACE_ERRORS table
3291 -- PO_PDOI_ITEM_RELATED_INFO:
3292 -- "COLUMN_NAME (VALUE=VALUE) specified is inactive or invalid for item_id (VALUE=ITEM)."
3293 --PO_R12_CAT_UPG_UTL.add_fatal_error(
3294 -- p_interface_header_id => l_interface_header_ids(i),
3295 -- p_interface_line_id => l_interface_line_ids(i),
3296 -- p_error_message_name => 'PO_PDOI_ITEM_RELATED_INFO',
3297 -- p_table_name => 'PO_LINES_INTERFACE',
3298 -- p_column_name => 'ITEM_REVISION',
3299 -- p_column_value => l_item_revision_list(i),
3300 -- p_token1_name => 'COLUMN_NAME',
3301 -- p_token1_value => 'ITEM_REVISION',
3302 -- p_token2_name => 'VALUE',
3303 -- p_token2_value => l_item_revision_list(i),
3304 -- p_token3_name => 'ITEM',
3305 -- p_token4_value => l_item_id_list(i)
3306 -- );
3307 END LOOP;
3308
3309 l_progress := '080';
3310 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3311 EXCEPTION
3312 WHEN OTHERS THEN
3313 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3314 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3315 END validate_item_revision;
3316
3317 --------------------------------------------------------------------------------
3318 --Start of Comments
3319 --Name: get_category_name_from_id
3320 --Pre-reqs:
3321 -- None
3322 --Modifies:
3323 -- None
3324 --Locks:
3325 -- None.
3326 --Function:
3327 -- Performs the id-to-name conversion for category_id.
3328 -- Used when logging validation errors.
3329 --Parameters:
3330 --IN:
3331 --p_category_id
3332 -- The CATEGORY_ID for which we want the name
3333 --OUT:
3334 --p_category_name
3335 -- The outut name for the given ID.
3336 --OUT:
3337 --End of Comments
3338 --------------------------------------------------------------------------------
3339 PROCEDURE get_category_name_from_id
3340 (
3341 p_category_id IN NUMBER,
3342 x_category_name OUT NOCOPY VARCHAR2
3343 )
3344 IS
3345 l_api_name CONSTANT VARCHAR2(30) := 'get_category_name_from_id';
3346 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3347 l_progress VARCHAR2(3) := '000';
3348 BEGIN
3349 l_progress := '010';
3350 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3351
3352 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_category_id='||p_category_id); END IF;
3353
3354 -- ECO bug 5584556: id-to-name conversion for new message
3355 -- Get the category name
3356 l_progress := '020';
3357 BEGIN
3358 SELECT concatenated_segments
3359 INTO x_category_name
3360 FROM MTL_CATEGORIES_B_KFV
3361 WHERE category_id = p_category_id;
3362 EXCEPTION
3363 WHEN OTHERS THEN
3364 l_progress := '030';
3365 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from MTL_CATEGORIES_B_KFV: '||SQLERRM); END IF;
3366
3367 x_category_name := p_category_id;
3368 END;
3369 -- ECO bug 5584556: End
3370
3371 l_progress := '040';
3372 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'category_name='||x_category_name); END IF;
3373
3374 l_progress := '050';
3375 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3376 EXCEPTION
3377 WHEN OTHERS THEN
3378 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3379 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3380 END get_category_name_from_id;
3381
3382 --------------------------------------------------------------------------------
3383 --Start of Comments
3384 --Name: get_ip_catgeory_name
3385 --Pre-reqs:
3386 -- None
3387 --Modifies:
3388 -- None
3389 --Locks:
3390 -- None.
3391 --Function:
3392 -- Performs the id-to-name conversion for ip_category_id.
3393 -- Used when logging validation errors.
3394 --Parameters:
3395 --IN:
3396 --p_ip_category_id
3397 -- The IP_CATEGORY_ID for which we want the name
3398 --OUT:
3399 --x_ip_category_name
3400 -- The outut name for the given ID.
3401 --OUT:
3402 --End of Comments
3403 --------------------------------------------------------------------------------
3404 PROCEDURE get_ip_catgeory_name
3405 (
3406 p_ip_category_id IN NUMBER,
3407 p_interface_header_id IN NUMBER,
3408 p_interface_line_id IN NUMBER,
3409 x_ip_category_name OUT NOCOPY VARCHAR2,
3410 x_ip_catgeory_found OUT NOCOPY VARCHAR2
3411 )
3412 IS
3413 l_api_name CONSTANT VARCHAR2(30) := 'get_ip_catgeory_name';
3414 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3415 l_progress VARCHAR2(3) := '000';
3416 BEGIN
3417 l_progress := '010';
3418 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3419
3420 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_ip_category_id='||p_ip_category_id); END IF;
3421
3422 x_ip_catgeory_found := 'Y';
3423 -- ECO bug 5584556: id-to-name conversion for new message
3424 -- Get the category name
3425 l_progress := '020';
3426 BEGIN
3427 SELECT category_name
3428 INTO x_ip_category_name
3429 FROM ICX_CAT_CATEGORIES_V
3430 WHERE rt_category_id = p_ip_category_id
3431 AND language = userenv('LANG');
3432 EXCEPTION
3433 WHEN OTHERS THEN
3434 -- If the shopping category does not exist, or other cases.
3435 l_progress := '030';
3436 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting IP ctageory_name using ICX_CAT_CATEGORIES_V view: '||SQLERRM); END IF;
3437
3438 x_ip_catgeory_found := 'N';
3439 END;
3440 -- ECO bug 5584556: End
3441
3442 IF ( (x_ip_category_name IS NULL) OR
3443 (x_ip_catgeory_found = 'N') ) THEN
3444
3445 x_ip_category_name := p_ip_category_id;
3446 x_ip_catgeory_found := 'N';
3447
3448 l_progress := '040';
3449 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Could not find IP category name'); END IF;
3450
3451 -- Add error message into INTERFACE_ERRORS table
3452 -- ICX_CAT_SHOP_CATEG_REQD:
3453 -- "Shopping category is missing for one or more lines."
3454 PO_R12_CAT_UPG_UTL.add_fatal_error(
3455 p_interface_header_id => p_interface_header_id,
3456 p_interface_line_id => p_interface_line_id,
3457 p_error_message_name => 'ICX_CAT_SHOP_CATEG_REQD',
3458 p_table_name => 'PO_LINES_INTERFACE',
3459 p_column_name => 'IP_CATEGORY_ID',
3460 p_column_value => p_ip_category_id
3461 );
3462 END IF;
3463
3464 l_progress := '050';
3465 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_ip_category_name='||x_ip_category_name); END IF;
3466
3467 l_progress := '060';
3468 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3469 EXCEPTION
3470 WHEN OTHERS THEN
3471 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3472 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3473 END get_ip_catgeory_name;
3474
3475 --------------------------------------------------------------------------------
3476 --Start of Comments
3477 --Name: validate_category
3478 --Pre-reqs:
3479 -- The iP catalog data is populated in input pl/sql tables.
3480 --Modifies:
3481 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
3482 -- b) FND_MSG_PUB on unhandled exceptions.
3483 --Locks:
3484 -- None.
3485 --Function:
3486 -- validate and make sure category_id is a valid category within the default
3487 -- category set for Purchasing. Validate if category_id belong to the item.
3488 -- Check if the Purchasing Category set has 'Validate flag' ON. If Yes, we
3489 -- will validate the Category to exist in the 'Valid Category List'. If No,
3490 -- we will just validate if the category is Enable and Active.
3491 --Parameters:
3492 --IN:
3493 --p_key
3494 -- Key used to access records in PO_SESSION_GT table.
3495 --IN/OUT:
3496 --p_lines_rec
3497 -- A record of plsql tables containing a batch of lines. If this validation
3498 -- fails, the 'has_errors' column is set to 'Y'.
3499 --OUT:
3500 --End of Comments
3501 --------------------------------------------------------------------------------
3502 PROCEDURE validate_category
3503 (
3504 p_key IN NUMBER,
3505 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
3506 )
3507 IS
3508 l_api_name CONSTANT VARCHAR2(30) := 'validate_category';
3509 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3510 l_progress VARCHAR2(3) := '000';
3511
3512 l_size NUMBER := p_lines_rec.interface_line_id.COUNT;
3513
3514 l_interface_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3515 l_interface_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3516 l_indexes PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3517 l_item_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3518 l_category_id_list PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3519 l_category_name_list PO_R12_CAT_UPG_TYPES.PO_TBL_VARCHAR2000;
3520
3521 l_index NUMBER;
3522 i NUMBER;
3523 l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3524
3525 l_validate_flag MTL_CATEGORY_SETS_V.validate_flag%TYPE;
3526 l_category_set_id MTL_CATEGORY_SETS_V.category_set_id%TYPE;
3527
3528 l_category_name MTL_CATEGORIES_B_KFV.concatenated_segments%TYPE;
3529
3530 l_ip_category_name ICX_CAT_CATEGORIES_V.category_name%TYPE;
3531 l_ip_catgeory_found VARCHAR2(1);
3532 BEGIN
3533 l_progress := '010';
3534 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3535
3536 -- category_id must not be NULL
3537 FOR i IN 1 .. p_lines_rec.category_id.COUNT
3538 LOOP
3539 l_progress := '020';
3540 IF (--p_lines_rec.has_errors(i) = 'N' AND
3541
3542 -- Bug 5060582: CATEGORY_ID is updatable when CREATED_BY = -12.
3543 -- If it is specified as NULL in INTF table, then the column
3544 -- remains unchanged in TXN table. This is handled via a DECODE in the
3545 -- UPDATE statement, later in the flow. So allow NULL value in UPDATE flow
3546 -- where CREATED_BY is NOT -12.
3547 --p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
3548 ( p_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create
3549 OR
3550 (p_lines_rec.action(i) = 'UPDATE' AND
3551 p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
3552 )
3553 AND
3554 (p_lines_rec.category_id(i) IS NULL
3555 OR p_lines_rec.category_id(i) = g_NULL_COLUMN_VALUE)) -- -2
3556 THEN
3557
3558 l_progress := '030';
3559 p_lines_rec.has_errors(i) := 'Y';
3560
3561 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||p_lines_rec.category(i)||', category_id='||p_lines_rec.category_id(i)); END IF;
3562 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3563
3564 -- ECO bug 5584556: Get the IP category name, that will be the token in th err msg
3565 l_progress := '035';
3566 get_ip_catgeory_name
3567 (
3568 p_ip_category_id => p_lines_rec.ip_category_id(i)
3569 , p_interface_header_id => p_lines_rec.interface_header_id(i)
3570 , p_interface_line_id => p_lines_rec.interface_line_id(i)
3571 , x_ip_category_name => l_ip_category_name
3572 , x_ip_catgeory_found => l_ip_catgeory_found
3573 );
3574 -- ECO bug 5584556: End
3575
3576 IF (l_ip_catgeory_found = 'Y') THEN
3577 -- Add error message into INTERFACE_ERRORS table
3578 -- ICX_CAT_CATEGORY_REQD:
3579 -- "The following categories cannot be mapped to a valid purchasing category: VALUE, VALUE"
3580 PO_R12_CAT_UPG_UTL.add_fatal_error(
3581 p_interface_header_id => p_lines_rec.interface_header_id(i),
3582 p_interface_line_id => p_lines_rec.interface_line_id(i),
3583 --p_error_message_name => 'PO_COLUMN_IS_NULL',
3584 p_error_message_name => 'ICX_CAT_CATEGORY_REQD',
3585 p_table_name => 'PO_LINES_INTERFACE',
3586 p_column_name => 'CATEGORY',
3587 p_column_value => l_ip_category_name, -- ECO bug 5584556
3588 p_token1_name => 'PO_CATEGORY_ID',
3589 p_token1_value => p_lines_rec.category(i), -- debug only, this is NULL here
3590 p_token2_name => 'IP_CATGEORY_ID',
3591 p_token2_value => p_lines_rec.ip_category_id(i) -- debug only
3592 );
3593 END IF; -- IF (l_ip_catgeory_found='Y')
3594 END IF;
3595 END LOOP;
3596
3597 l_progress := '040';
3598 -- Find out the default category_set_id and validate_flag for function_area
3599 -- of PURCHASING".
3600 SELECT validate_flag, category_set_id
3601 INTO l_validate_flag, l_category_set_id
3602 FROM MTL_CATEGORY_SETS_V
3603 WHERE category_set_id =
3604 (SELECT category_set_id
3605 FROM MTL_DEFAULT_CATEGORY_SETS
3606 WHERE functional_area_id = 2) ; -- Purchasing
3607
3608 l_progress := '050';
3609 l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
3610
3611 l_progress := '060';
3612 -- SQL What: Bulk validate category. Validate if category_id belongs to the
3613 -- item.
3614 -- Insert the error rows into GT table.
3615 -- SQL Why : It will be used to mark the record in plsql table as error.
3616 -- SQL Join: item_id, category_id
3617 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
3618 INSERT INTO PO_SESSION_GT(key,
3619 num1,
3620 num2,
3621 num3,
3622 num4,
3623 num5,
3624 char1)
3625 SELECT p_key
3626 , l_subscript_array(i)
3627 , p_lines_rec.interface_header_id(i)
3628 , p_lines_rec.interface_line_id(i)
3629 , p_lines_rec.item_id(i)
3630 , p_lines_rec.category_id(i)
3631 , p_lines_rec.category(i) -- category name
3632 FROM DUAL
3633 WHERE --p_lines_rec.has_errors(i) = 'N'
3634 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
3635 AND p_lines_rec.item_id(i) is not null
3636 AND p_lines_rec.category_id(i) is not null
3637 AND p_lines_rec.category_id(i) <> g_NULL_COLUMN_VALUE -- -2
3638 AND NOT EXISTS (SELECT 1
3639 FROM MTL_ITEM_CATEGORIES MIC,
3640 MTL_CATEGORIES MCS
3641 WHERE
3642 MIC.category_id = MCS.category_id
3643 AND MIC.category_set_id = l_category_set_id
3644 AND MIC.category_id = p_lines_rec.category_id(i)
3645 AND MIC.inventory_item_id = p_lines_rec.item_id(i)
3646 AND MIC.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
3647 AND sysdate < nvl(MCS.disable_date, sysdate+1)
3648 AND MCS.enabled_flag = 'Y');
3649
3650 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3651
3652 l_progress := '070';
3653 -- SQL What: Transfer from session GT table to local arrays
3654 -- SQL Why : It will be used to mark the error records.
3655 -- SQL Join: key
3656 DELETE FROM PO_SESSION_GT
3657 WHERE key = p_key
3658 RETURNING num1, num2, num3, num4, num5, char1
3659 BULK COLLECT INTO l_indexes, l_interface_header_ids,
3660 l_interface_line_ids, l_item_id_list,
3661 l_category_id_list, l_category_name_list;
3662
3663 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3664
3665 l_progress := '080';
3666 -- Mark the error records
3667 FOR i IN 1 .. l_indexes.COUNT
3668 LOOP
3669 l_index := l_indexes(i);
3670 p_lines_rec.has_errors(l_index) := 'Y';
3671
3672 -- ECO bug 5584556: id-to-name conversion for new message
3673 l_progress := '085';
3674 get_category_name_from_id
3675 (
3676 p_category_id => l_category_id_list(i)
3677 , x_category_name => l_category_name
3678 );
3679 -- ECO bug 5584556: End
3680
3681 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||l_category_name_list(i)||', category_id='||l_category_id_list(i)||', item_id='||l_item_id_list(i)); END IF;
3682
3683 -- Add error message into INTERFACE_ERRORS table
3684 -- ICX_CAT_INVALID_CATEGORY:
3685 -- "The following purchasing categories are inactive or invalid: VALUE, VALUE"
3686 PO_R12_CAT_UPG_UTL.add_fatal_error(
3687 p_interface_header_id => l_interface_header_ids(i),
3688 p_interface_line_id => l_interface_line_ids(i),
3689 --p_error_message_name => 'PO_PDOI_ITEM_RELATED_INFO',
3690 p_error_message_name => 'ICX_CAT_INVALID_CATEGORY',
3691 p_table_name => 'PO_LINES_INTERFACE',
3692 p_column_name => 'CATEGORY_ID',
3693 p_column_value => l_category_name, -- ECO bug 5584556
3694 p_token1_name => 'VALUE',
3695 p_token1_value => l_category_id_list(i),
3696 p_token2_name => 'ITEM',
3697 p_token2_value => l_item_id_list(i)
3698 );
3699 END LOOP;
3700
3701 l_progress := '090';
3702 IF l_validate_flag = 'Y' THEN
3703 l_progress := '100';
3704 -- SQL What: Bulk validate category. Validate category_id against
3705 -- MTL_CATEGORY_SET_VALID_CATS.
3706 -- Insert the error rows into GT table.
3707 -- SQL Why : It will be used to mark the record in plsql table as error.
3708 -- SQL Join: category_id
3709 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
3710 INSERT INTO PO_SESSION_GT(key,
3711 num1,
3712 num2,
3713 num3,
3714 num4,
3715 char1)
3716 SELECT p_key
3717 , l_subscript_array(i)
3718 , p_lines_rec.interface_header_id(i)
3719 , p_lines_rec.interface_line_id(i)
3720 , p_lines_rec.category_id(i)
3721 , p_lines_rec.category(i) -- category name
3722 FROM DUAL
3723 WHERE --p_lines_rec.has_errors(i) = 'N'
3724 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
3725 AND p_lines_rec.item_id(i) is null
3726 AND p_lines_rec.category_id(i) is not null
3727 AND p_lines_rec.category_id(i) <> g_NULL_COLUMN_VALUE -- -2
3728 AND NOT EXISTS
3729 (SELECT 'Y'
3730 FROM MTL_CATEGORIES_VL MCS,
3731 MTL_CATEGORY_SET_VALID_CATS MCSVC
3732 WHERE MCS.category_id = p_lines_rec.category_id(i)
3733 AND MCS.category_id = MCSVC.category_id
3734 AND MCSVC.category_set_id = l_category_set_id
3735 AND sysdate < nvl(MCS.disable_date, sysdate+1)
3736 AND MCS.enabled_flag = 'Y');
3737
3738 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3739
3740 l_progress := '110';
3741 -- SQL What: Transfer from session GT table to local arrays
3742 -- SQL Why : It will be used to mark the error records.
3743 -- SQL Join: key
3744 DELETE FROM PO_SESSION_GT
3745 WHERE key = p_key
3746 RETURNING num1, num2, num3, num4, char1
3747 BULK COLLECT INTO l_indexes, l_interface_header_ids,
3748 l_interface_line_ids,
3749 l_category_id_list, l_category_name_list;
3750
3751 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3752
3753 l_progress := '120';
3754 -- Mark the error records
3755 FOR i IN 1 .. l_indexes.COUNT
3756 LOOP
3757 l_index := l_indexes(i);
3758 p_lines_rec.has_errors(l_index) := 'Y';
3759
3760 -- ECO bug 5584556: id-to-name conversion for new message
3761 l_progress := '125';
3762 get_category_name_from_id
3763 (
3764 p_category_id => l_category_id_list(i)
3765 , x_category_name => l_category_name
3766 );
3767 -- ECO bug 5584556: End
3768
3769 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||l_category_name_list(i)||', category_id='||l_category_id_list(i)); END IF;
3770
3771 -- Add error message into INTERFACE_ERRORS table
3772 -- ICX_CAT_INVALID_CATEGORY:
3773 -- "The following purchasing categories are inactive or invalid: VALUE, VALUE"
3774 PO_R12_CAT_UPG_UTL.add_fatal_error(
3775 p_interface_header_id => l_interface_header_ids(i),
3776 p_interface_line_id => l_interface_line_ids(i),
3777 --p_error_message_name => 'PO_PDOI_INVALID_CATEGORY_ID',
3778 p_error_message_name => 'ICX_CAT_INVALID_CATEGORY',
3779 p_table_name => 'PO_LINES_INTERFACE',
3780 p_column_name => 'CATEGORY_ID',
3781 p_column_value => l_category_name, -- ECO bug 5584556
3782 p_token1_name => 'VALUE',
3783 p_token1_value => l_category_id_list(i)
3784 );
3785 END LOOP;
3786
3787 l_progress := '130';
3788
3789 ELSE -- IF l_validate_flag <> 'Y' THEN
3790
3791 l_progress := '140';
3792 -- SQL What: Bulk validate category. Validate category_id against
3793 -- MTL_CATEGORIES_VL.
3794 -- Insert the error rows into GT table.
3795 -- SQL Why : It will be used to mark the record in plsql table as error.
3796 -- SQL Join: category_id
3797 FORALL i IN 1 .. p_lines_rec.item_id.COUNT
3798 INSERT INTO PO_SESSION_GT(key,
3799 num1,
3800 num2,
3801 num3,
3802 num4,
3803 char1)
3804 SELECT p_key
3805 , l_subscript_array(i)
3806 , p_lines_rec.interface_header_id(i)
3807 , p_lines_rec.interface_line_id(i)
3808 , p_lines_rec.category_id(i)
3809 , p_lines_rec.category(i) -- category name
3810 FROM DUAL
3811 WHERE --p_lines_rec.has_errors(i) = 'N'
3812 p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE')
3813 AND p_lines_rec.item_id(i) is null
3814 AND p_lines_rec.category_id(i) is not null
3815 AND p_lines_rec.category_id(i) <> g_NULL_COLUMN_VALUE -- -2
3816 AND NOT EXISTS
3817 (SELECT 1
3818 FROM MTL_CATEGORIES_VL MCS
3819 WHERE MCS.category_id = p_lines_rec.category_id(i)
3820 AND sysdate < nvl(MCS.disable_date, sysdate+1)
3821 AND MCS.enabled_flag = 'Y');
3822
3823 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3824
3825 l_progress := '150';
3826 -- SQL What: Transfer from session GT table to local arrays
3827 -- SQL Why : It will be used to mark the error records.
3828 -- SQL Join: key
3829 DELETE FROM PO_SESSION_GT
3830 WHERE key = p_key
3831 RETURNING num1, num2, num3, num4, char1
3832 BULK COLLECT INTO l_indexes, l_interface_header_ids,
3833 l_interface_line_ids,
3834 l_category_id_list, l_category_name_list;
3835
3836 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3837
3838 l_progress := '160';
3839 -- Mark the error records
3840 FOR i IN 1 .. l_indexes.COUNT
3841 LOOP
3842 l_progress := '170';
3843
3844 l_index := l_indexes(i);
3845 p_lines_rec.has_errors(l_index) := 'Y';
3846
3847 -- ECO bug 5584556: id-to-name conversion for new message
3848 l_progress := '175';
3849 get_category_name_from_id
3850 (
3851 p_category_id => l_category_id_list(i)
3852 , x_category_name => l_category_name
3853 );
3854 -- ECO bug 5584556: End
3855
3856 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||l_category_name_list(i)||', category_id='||l_category_id_list(i)); END IF;
3857
3858 -- Add error message into INTERFACE_ERRORS table
3859 -- ICX_CAT_INVALID_CATEGORY:
3860 -- "The following purchasing categories are inactive or invalid: VALUE, VALUE"
3861 PO_R12_CAT_UPG_UTL.add_fatal_error(
3862 p_interface_header_id => l_interface_header_ids(i),
3863 p_interface_line_id => l_interface_line_ids(i),
3864 --p_error_message_name => 'PO_PDOI_INVALID_CATEGORY_ID',
3865 p_error_message_name => 'ICX_CAT_INVALID_CATEGORY',
3866 p_table_name => 'PO_LINES_INTERFACE',
3867 p_column_name => 'CATEGORY_ID',
3868 p_column_value => l_category_name, -- ECO bug 5584556
3869 p_token1_name => 'VALUE',
3870 p_token1_value => l_category_id_list(i)
3871 );
3872 END LOOP;
3873 END IF; -- IF x_validate_flag = 'Y'
3874
3875 l_progress := '180';
3876 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3877 EXCEPTION
3878 WHEN OTHERS THEN
3879 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3880 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3881 END validate_category;
3882
3883 --------------------------------------------------------------------------------
3884 --Start of Comments
3885 --Name: validate_unit_price
3886 --Pre-reqs:
3887 -- The iP catalog data is populated in input pl/sql tables.
3888 --Modifies:
3889 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
3890 -- b) FND_MSG_PUB on unhandled exceptions.
3891 --Locks:
3892 -- None.
3893 --Function:
3894 -- If order_type_lookup_code is not 'FIXED PRICE', unit_price cannot be null
3895 -- and cannot be less than zero.
3896 --Parameters:
3897 --IN:
3898 --p_key
3899 -- Key used to access records in PO_SESSION_GT table.
3900 --IN/OUT:
3901 --p_lines_rec
3902 -- A record of plsql tables containing a batch of lines. If this validation
3903 -- fails, the 'has_errors' column is set to 'Y'.
3904 --OUT:
3905 --End of Comments
3906 --------------------------------------------------------------------------------
3907 PROCEDURE validate_unit_price
3908 (
3909 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
3910 )
3911 IS
3912 l_api_name CONSTANT VARCHAR2(30) := 'validate_unit_price';
3913 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3914 l_progress VARCHAR2(3) := '000';
3915
3916 i NUMBER;
3917 BEGIN
3918 l_progress := '010';
3919 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3920
3921 FOR i IN 1 .. p_lines_rec.unit_price.COUNT
3922 LOOP
3923 l_progress := '020';
3924
3925 IF (--p_lines_rec.has_errors(i) = 'N' AND
3926 -- Bug 5060582: UNIT_PRICE is updatable when CREATED_BY = -12.
3927 -- If it is specified as NULL in INTF table, then the column
3928 -- remains unchanged in TXN table. This is handled via a DECODE in the
3929 -- UPDATE statement, later in the flow. So allow NULL value in UPDATE flow
3930 -- where CREATED_BY is NOT -12.
3931 --p_lines_rec.action(i) IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE') AND
3932 ( p_lines_rec.action(i) =PO_R12_CAT_UPG_PVT.g_action_line_create
3933 OR
3934 (p_lines_rec.action(i) = 'UPDATE' AND
3935 p_lines_rec.created_by(i) = PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER)
3936 )
3937 AND
3938 (p_lines_rec.unit_price(i) IS NULL OR
3939 p_lines_rec.unit_price(i) < 0)) THEN
3940
3941 l_progress := '030';
3942
3943 p_lines_rec.has_errors(i) := 'Y';
3944 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3945
3946 -- Add error message into INTERFACE_ERRORS table
3947 -- PO_PDOI_LT_ZERO:
3948 -- "COLUMN_NAME (VALUE =VALUE) specified is less than zero."
3949 PO_R12_CAT_UPG_UTL.add_fatal_error(
3950 p_interface_header_id => p_lines_rec.interface_header_id(i),
3951 p_interface_line_id => p_lines_rec.interface_line_id(i),
3952 --p_error_message_name => 'PO_PDOI_LT_ZERO',
3953 p_error_message_name => 'ICX_CAT_INVALID_PRICE',
3954 p_table_name => 'PO_LINES_INTERFACE',
3955 p_column_name => 'UNIT_PRICE',
3956 p_column_value => p_lines_rec.unit_price(i),
3957 p_token1_name => 'COLUMN_NAME',
3958 p_token1_value => 'UNIT_PRICE',
3959 p_token2_name => 'VALUE',
3960 p_token2_value => p_lines_rec.unit_price(i)
3961 );
3962 END IF;
3963 END LOOP;
3964
3965 l_progress := '040';
3966 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3967 EXCEPTION
3968 WHEN OTHERS THEN
3969 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3970 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3971 END validate_unit_price;
3972
3973 --------------------------------------------------------------------------------
3974 --Start of Comments
3975 --Name: validate_lines
3976 --Pre-reqs:
3977 -- The iP catalog data is populated in input pl/sql tables.
3978 --Modifies:
3979 -- a) Input pl/sql table: May set the value of 'has_errors' to 'Y'.
3980 -- b) FND_MSG_PUB on unhandled exceptions.
3981 --Locks:
3982 -- None.
3983 --Function:
3984 -- This is the main function to validate lines. It calls the following
3985 -- procedures:
3986 --
3987 -- validate_item
3988 -- validate_item_revision
3989 -- validate_item_description
3990 -- validate_uom
3991 -- validate_category
3992 -- validate_unit_price
3993 --
3994 -- UN_NUMBERID, HAZARD_CLASS_ID: These are 2 more defaulted fields
3995 -- (from vendor/site) that IP will not provide in Interface table. However,
3996 -- for these 2 fields, the only validation in PDOI is that if the line
3997 -- type is TEMP LABOR, then these fields must be NULL. Since in IP catalog
3998 -- migration, the line type is always GOODS we do not need these validations.
3999 --
4000 --Parameters:
4001 --IN/OUT:
4002 --p_lines_rec
4003 -- A record of plsql tables containing a batch of lines. If this validation
4004 -- fails, the 'has_errors' column is set to 'Y'.
4005 --OUT:
4006 --End of Comments
4007 --------------------------------------------------------------------------------
4008 PROCEDURE validate_lines
4009 (
4010 p_lines_rec IN OUT NOCOPY PO_R12_CAT_UPG_PVT.record_of_lines_type
4011 )
4012 IS
4013 l_api_name CONSTANT VARCHAR2(30) := 'validate_lines';
4014 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
4015 l_progress VARCHAR2(3) := '000';
4016
4017 l_key PO_SESSION_GT.key%TYPE;
4018 i NUMBER;
4019 BEGIN
4020 l_progress := '010';
4021 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4022
4023 -- pick a new key from temp table which will be used in all validate logic
4024 SELECT PO_SESSION_GT_S.nextval
4025 INTO l_key
4026 FROM DUAL;
4027
4028 l_progress := '020';
4029 -- if item id is not null, it has to exist in mtl_system_items table
4030 validate_item
4031 (
4032 p_key => l_key,
4033 p_lines_rec => p_lines_rec
4034 );
4035
4036 l_progress := '030';
4037 -- if order_type_lookup_code is FIXED PRICE or RATE, or item id is null,
4038 -- then item revision has to be NULL
4039 -- check to see if there are x_item_revision exists in mtl_item_revisions table
4040 validate_item_revision
4041 (
4042 p_key => l_key,
4043 p_lines_rec => p_lines_rec
4044 );
4045
4046 l_progress := '040';
4047 -- Make sure that the item_description is populated, and also need to find out
4048 -- if it is different from what is setup for the item.
4049 validate_item_description
4050 (
4051 p_key => l_key,
4052 p_lines_rec => p_lines_rec
4053 );
4054
4055 l_progress := '050';
4056 -- check to see if unit_meas_lookup_code is valid in mtl_item_uoms_view
4057 validate_uom
4058 (
4059 p_key => l_key,
4060 p_lines_rec => p_lines_rec
4061 );
4062
4063 l_progress := '060';
4064 -- validate and make sure category_id is a valid category within the default
4065 -- category set for Purchasing. Validate if category_id belong to the item.
4066 -- Check if the Purchasing Category set has 'Validate flag' ON. If Yes, we
4067 -- will validate the Category to exist in the 'Valid Category List'. If No,
4068 -- we will just validate if the category is Enable and Active.
4069 validate_category
4070 (
4071 p_key => l_key,
4072 p_lines_rec => p_lines_rec
4073 );
4074
4075 l_progress := '070';
4076 -- If order_type_lookup_code is not 'FIXED PRICE', unit_price cannot be null
4077 -- and cannot be less than zero.
4078 validate_unit_price
4079 (
4080 p_lines_rec => p_lines_rec
4081 );
4082
4083 l_progress := '080';
4084 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4085 EXCEPTION
4086 WHEN OTHERS THEN
4087 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4088 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4089 END validate_lines;
4090
4091
4092 --------------------------------------------------------------------------------
4093 -- Validate ACTIONS in pre-process
4094 --------------------------------------------------------------------------------
4095
4096
4097 --------------------------------------------------------------------------------
4098 --Start of Comments
4099 --Name: validate_create_action
4100 --Pre-reqs:
4101 -- The iP catalog data is populated in PO Interface tables.
4102 --Modifies:
4103 -- a) PO_INTERFACE_ERRORS table: Inserts error messages for those rows that
4104 -- failed validation.
4105 -- b) FND_MSG_PUB on unhandled exceptions.
4106 --Locks:
4107 -- None.
4108 --Function:
4109 -- Validates CREATE actions at line, attribute and TLP levels
4110 -- * For Lines,
4111 -- checks that INTERFACE_HEADER_ID is provided where the line needs to be added to
4112 -- * For Attributes,
4113 -- checks that INTERFACE_LINE_ID is provided for which the attribute needs to be created for.
4114 -- * For Attributes TLP,
4115 -- checks that INTERFACE_LINE_ID is provided for which the attribute TLP needs to be created for
4116 --
4117 -- This API should be called during the upgrade phase only.
4118 --Parameters:
4119 --IN:
4120 --p_validate_only_mode
4121 -- Indicates if the API is being called in a Validate Only mode or not
4122 --OUT:
4123 -- None
4124 --
4125 --End of Comments
4126 --------------------------------------------------------------------------------
4127 PROCEDURE validate_create_action
4128 (
4129 p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
4130 )
4131 IS
4132 l_api_name CONSTANT VARCHAR2(30) := 'validate_create_action';
4133 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
4134 l_progress VARCHAR2(3) := '000';
4135
4136 l_err_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4137 l_err_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4138 l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4139 l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4140 BEGIN
4141 l_progress := '010';
4142 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4143
4144 -----------------------------------------------------------------------------
4145 -- CREATE action validations for Lines
4146 -----------------------------------------------------------------------------
4147
4148 l_progress := '040';
4149 -- For Lines,
4150 -- ORIGINAL action is not allowed at line level. Use ADD action (to conform
4151 -- to PDOI style of action names).
4152
4153 -- SQL What: validate create action at line level
4154 -- SQL Why : To find incorrect action
4155 -- SQL Join: processing_id, action, interface_header_id
4156 UPDATE PO_LINES_INTERFACE POLI
4157 SET POLI.processing_id = -POLI.processing_id,
4158 POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4159 WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4160 AND POLI.action = 'ORIGINAL'
4161 RETURNING POLI.interface_line_id, POLI.interface_header_id
4162 BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
4163
4164 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Line level='||SQL%rowcount); END IF;
4165
4166 l_progress := '050';
4167 -- Add error message into INTERFACE_ERRORS table
4168 FOR i in 1..l_err_line_ids.COUNT
4169 LOOP
4170 PO_R12_CAT_UPG_UTL.add_fatal_error(
4171 p_interface_header_id => l_err_header_ids(i),
4172 p_interface_line_id => l_err_line_ids(i),
4173 p_error_message_name => 'PO_CAT_UPG_ORIG_NOT_ALLOWED',
4174 p_table_name => 'PO_LINES_INTERFACE',
4175 p_column_name => 'ACTION'
4176 );
4177 END LOOP;
4178
4179 l_progress := '060';
4180 -- Cascade errors down to attribute and TLP levels
4181 -- Skip cascading of errors in Validate Only mode (iP Requirement - all errors should be reported)
4182 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4183 PO_R12_CAT_UPG_UTL.reject_attr_values_intf('INTERFACE_LINE_ID', l_err_line_ids);
4184 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
4185 END IF;
4186
4187 -----------------------------------------------------------------------------
4188 -- CREATE action validations for Attribute Values
4189 -----------------------------------------------------------------------------
4190 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_line_create='||PO_R12_CAT_UPG_PVT.g_action_line_create); END IF;
4191 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_attr_create='||PO_R12_CAT_UPG_PVT.g_action_attr_create); END IF;
4192
4193 -- Clear the error arrays
4194 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4195 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4196 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4197
4198 l_err_attr_values_ids.DELETE;
4199 l_err_line_ids.DELETE;
4200 l_err_header_ids.DELETE;
4201
4202 l_progress := '070';
4203 -- For Attributes,
4204 -- check that INTERFACE_LINE_ID is provided for which the attribute needs to be created for
4205
4206 -- SQL What: validate create action at attr level
4207 -- SQL Why : To find incorrect action
4208 -- SQL Join: processing_id, action, interface_line_id
4209 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4210 SET POAI.processing_id = -POAI.processing_id,
4211 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4212 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4213 AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
4214 AND (
4215 POAI.interface_line_id IS NULL
4216 OR
4217 -- the following are NOT NULL columns in the TXN table, so they must
4218 -- have a not null value in the INTF table as well.
4219 (
4220 --POAI.po_line_id IS NULL OR
4221 POAI.req_template_name IS NULL OR
4222 POAI.req_template_line_num IS NULL OR
4223 POAI.org_id IS NULL OR
4224 POAI.inventory_item_id IS NULL OR
4225 POAI.ip_category_id IS NULL
4226 )
4227 )
4228 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4229 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4230
4231 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4232
4233 l_progress := '080';
4234 -- Add error message into INTERFACE_ERRORS table
4235 FOR i in 1..l_err_attr_values_ids.COUNT
4236 LOOP
4237 PO_R12_CAT_UPG_UTL.add_fatal_error(
4238 p_interface_header_id => l_err_header_ids(i),
4239 p_interface_line_id => l_err_line_ids(i),
4240 p_interface_attr_values_id => l_err_attr_values_ids(i),
4241 p_error_message_name => 'PO_CAT_UPG_NULL_IDS',
4242 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4243 p_column_name => 'INTERFACE_ATTR_VALUES_ID'
4244 );
4245 END LOOP;
4246
4247 l_progress := '090';
4248 -- Cascade errors down to TLP level
4249 -- Skip cascading of errors in Validate Only mode.
4250 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4251 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
4252 END IF;
4253
4254 ----------------------------------------------------------------------------------
4255 -- Clear the error arrays
4256 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4257 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4258 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4259
4260 l_err_attr_values_ids.DELETE;
4261 l_err_line_ids.DELETE;
4262 l_err_header_ids.DELETE;
4263
4264 l_progress := '071';
4265 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4266 SET POAI.processing_id = -POAI.processing_id,
4267 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4268 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4269 AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
4270 AND (EXISTS
4271 (SELECT 'Invalid relationship with Line Level'
4272 FROM PO_LINES_INTERFACE POLI
4273 WHERE POLI.interface_line_id = POAI.interface_line_id
4274 AND ( -- Attribute Row for GBPA must have appr action at line level
4275 (POAI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE AND
4276 POAI.req_template_line_num = g_NOT_REQUIRED_ID AND
4277 POLI.action NOT IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE', 'DELETE'))
4278 )))
4279 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4280 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4281
4282 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4283
4284 l_progress := '072';
4285 -- Add error message into INTERFACE_ERRORS table
4286 FOR i in 1..l_err_attr_values_ids.COUNT
4287 LOOP
4288 PO_R12_CAT_UPG_UTL.add_fatal_error(
4289 p_interface_header_id => l_err_header_ids(i),
4290 p_interface_line_id => l_err_line_ids(i),
4291 p_interface_attr_values_id => l_err_attr_values_ids(i),
4292 p_error_message_name => 'PO_CAT_UPG_INV_LINE_ACTION_BL',
4293 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4294 p_column_name => 'INTERFACE_ATTR_VALUES_ID'
4295 );
4296 END LOOP;
4297
4298 -- Clear the error arrays
4299 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4300 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4301 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4302
4303 l_err_attr_values_ids.DELETE;
4304 l_err_line_ids.DELETE;
4305 l_err_header_ids.DELETE;
4306
4307 l_progress := '071';
4308 -- Clear the error arrays
4309 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4310 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4311 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4312
4313 l_err_attr_values_ids.DELETE;
4314 l_err_line_ids.DELETE;
4315 l_err_header_ids.DELETE;
4316
4317 l_progress := '07';
4318 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4319 SET POAI.processing_id = -POAI.processing_id,
4320 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4321 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4322 AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
4323 AND (EXISTS
4324 (SELECT 'Invalid relationship with Line Level'
4325 FROM PO_LINES_INTERFACE POLI
4326 WHERE POLI.interface_line_id = POAI.interface_line_id
4327 AND ( -- Attribute Row for ReqTemplate must have action as REQTEMPALTE at line level
4328 ((POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE OR
4329 POAI.req_template_line_num <> g_NOT_REQUIRED_ID) AND
4330 POLI.action NOT IN ('REQTEMPLATE'))
4331 )))
4332 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4333 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4334
4335 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4336
4337 l_progress := '072';
4338 -- Add error message into INTERFACE_ERRORS table
4339 FOR i in 1..l_err_attr_values_ids.COUNT
4340 LOOP
4341 PO_R12_CAT_UPG_UTL.add_fatal_error(
4342 p_interface_header_id => l_err_header_ids(i),
4343 p_interface_line_id => l_err_line_ids(i),
4344 p_interface_attr_values_id => l_err_attr_values_ids(i),
4345 p_error_message_name => 'PO_CAT_UPG_INV_LINE_ACTION_RT',
4346 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4347 p_column_name => 'INTERFACE_ATTR_VALUES_ID'
4348 );
4349 END LOOP;
4350
4351 -- Clear the error arrays
4352 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4353 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4354 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4355
4356 l_err_attr_values_ids.DELETE;
4357 l_err_line_ids.DELETE;
4358 l_err_header_ids.DELETE;
4359
4360 l_progress := '073';
4361 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4362 SET POAI.processing_id = -POAI.processing_id,
4363 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4364 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4365 AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
4366 AND (
4367 ( -- For ReqTemplates, ALL the 3 columns must be given
4368 (POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
4369 (POAI.req_template_line_num = g_NOT_REQUIRED_ID OR
4370 POAI.org_id = g_NOT_REQUIRED_ID))
4371 OR
4372 (POAI.req_template_line_num <> g_NOT_REQUIRED_ID AND
4373 (POAI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE OR
4374 POAI.org_id = g_NOT_REQUIRED_ID))
4375 )
4376 )
4377 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4378 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4379
4380 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4381
4382 l_progress := '074';
4383 -- Add error message into INTERFACE_ERRORS table
4384 FOR i in 1..l_err_attr_values_ids.COUNT
4385 LOOP
4386 PO_R12_CAT_UPG_UTL.add_fatal_error(
4387 p_interface_header_id => l_err_header_ids(i),
4388 p_interface_line_id => l_err_line_ids(i),
4389 p_interface_attr_values_id => l_err_attr_values_ids(i),
4390 p_error_message_name => 'PO_CAT_UPG_ALL_3_FOR_REQT',
4391 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4392 p_column_name => 'INTERFACE_ATTR_VALUES_ID'
4393 );
4394 END LOOP;
4395
4396 -- Clear the error arrays
4397 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4398 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4399 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4400
4401 l_err_attr_values_ids.DELETE;
4402 l_err_line_ids.DELETE;
4403 l_err_header_ids.DELETE;
4404
4405 l_progress := '075';
4406 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4407 SET POAI.processing_id = -POAI.processing_id,
4408 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4409 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4410 AND POAI.action = PO_R12_CAT_UPG_PVT.g_action_attr_create
4411 AND (
4412 (-- For ReqTemplates, the ReqTemplate Line must exist in Txn tables
4413 POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
4414 POAI.req_template_line_num <> g_NOT_REQUIRED_ID AND
4415 POAI.org_id <> g_NOT_REQUIRED_ID AND
4416 NOT EXISTS
4417 (SELECT 'ReqTemplate Line in txn tables'
4418 FROM PO_REQEXPRESS_LINES_ALL PORT
4419 WHERE PORT.express_name = POAI.req_template_name
4420 AND PORT.sequence_num = POAI.req_template_line_num
4421 AND PORT.org_id = POAI.org_id)
4422 )
4423 )
4424 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4425 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4426
4427 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4428
4429 l_progress := '076';
4430 -- Add error message into INTERFACE_ERRORS table
4431 FOR i in 1..l_err_attr_values_ids.COUNT
4432 LOOP
4433 PO_R12_CAT_UPG_UTL.add_fatal_error(
4434 p_interface_header_id => l_err_header_ids(i),
4435 p_interface_line_id => l_err_line_ids(i),
4436 p_interface_attr_values_id => l_err_attr_values_ids(i),
4437 p_error_message_name => 'PO_CAT_UPG_ALL_NO_REQTEMP',
4438 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4439 p_column_name => 'INTERFACE_ATTR_VALUES_ID'
4440 );
4441 END LOOP;
4442
4443 ----------------------------------------------------------------------------------
4444
4445 l_progress := '080';
4446 -- Add error message into INTERFACE_ERRORS table
4447 FOR i in 1..l_err_attr_values_ids.COUNT
4448 LOOP
4449 PO_R12_CAT_UPG_UTL.add_fatal_error(
4450 p_interface_header_id => l_err_header_ids(i),
4451 p_interface_line_id => l_err_line_ids(i),
4452 p_interface_attr_values_id => l_err_attr_values_ids(i),
4453 p_error_message_name => 'PO_CAT_UPG_INVALID_ATTR_ROW',
4454 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4455 p_column_name => 'INTERFACE_ATTR_VALUES_ID'
4456 );
4457 END LOOP;
4458
4459 l_progress := '090';
4460 -- Cascade errors down to TLP level
4461 -- Skip cascading of errors in Validate Only mode.
4462 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4463 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
4464 END IF;
4465
4466 -----------------------------------------------------------------------------
4467 -- CREATE action validations for Attribute Values TLP
4468 -----------------------------------------------------------------------------
4469 -- Clear the error arrays
4470 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4471 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4472 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4473 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_tlp_ids.COUNT='||l_err_attr_values_tlp_ids.COUNT); END IF;
4474
4475 l_err_attr_values_ids.DELETE;
4476 l_err_attr_values_tlp_ids.DELETE;
4477 l_err_line_ids.DELETE;
4478 l_err_header_ids.DELETE;
4479
4480 l_progress := '100';
4481 -- For Attribute Values TLP,
4482 -- check that INTERFACE_LINE_ID is provided for which the attribute TLP needs to be created for
4483
4484 -- SQL What: validate create action at attr TLP level
4485 -- SQL Why : To find incorrect action
4486 -- SQL Join: processing_id, action, interface_line_id
4487 UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
4488 SET POATI.processing_id = -POATI.processing_id,
4489 POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4490 WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4491 AND POATI.action = PO_R12_CAT_UPG_PVT.g_action_tlp_create
4492 AND (
4493 POATI.language IS NULL
4494 OR
4495 POATI.interface_line_id IS NULL
4496 OR
4497 -- the following are NOT NULL columns in the TXN table, so they must
4498 -- have a not null value in the INTF table as well.
4499 (
4500 --POATI.po_line_id IS NULL OR
4501 POATI.req_template_name IS NULL OR
4502 POATI.req_template_line_num IS NULL OR
4503 POATI.org_id IS NULL OR
4504 POATI.inventory_item_id IS NULL OR
4505 POATI.ip_category_id IS NULL OR
4506 POATI.description IS NULL
4507 )
4508 OR
4509 (EXISTS
4510 (SELECT 'Invalid relationship with Line Level'
4511 FROM PO_LINES_INTERFACE POLI
4512 WHERE POLI.interface_line_id = POATI.interface_line_id
4513 AND ( -- Attribute Row for GBPA must have appr action at line level
4514 (POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE AND
4515 POATI.req_template_line_num = g_NOT_REQUIRED_ID AND
4516 POLI.action NOT IN (PO_R12_CAT_UPG_PVT.g_action_line_create, 'UPDATE', 'DELETE'))
4517 OR
4518 -- Attribute Row for ReqTemplate must have action as REQTEMPALTE at line level
4519 ((POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE OR
4520 POATI.req_template_line_num <> g_NOT_REQUIRED_ID) AND
4521 POLI.action NOT IN ('REQTEMPLATE'))
4522 )
4523 )
4524 )
4525 OR
4526 ( -- For ReqTemplates, ALL the 3 columns must be given
4527 (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
4528 (POATI.req_template_line_num = g_NOT_REQUIRED_ID OR
4529 POATI.org_id = g_NOT_REQUIRED_ID))
4530 OR
4531 (POATI.req_template_line_num <> g_NOT_REQUIRED_ID AND
4532 (POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE OR
4533 POATI.org_id = g_NOT_REQUIRED_ID))
4534 )
4535 OR
4536 (-- For ReqTemplates, the ReqTemplate Line must exist in Txn tables
4537 POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
4538 POATI.req_template_line_num <> g_NOT_REQUIRED_ID AND
4539 POATI.org_id <> g_NOT_REQUIRED_ID AND
4540 NOT EXISTS
4541 (SELECT 'ReqTemplate Line in txn tables'
4542 FROM PO_REQEXPRESS_LINES_ALL PORT
4543 WHERE PORT.express_name = POATI.req_template_name
4544 AND PORT.sequence_num = POATI.req_template_line_num
4545 AND PORT.org_id = POATI.org_id)
4546 )
4547 )
4548 RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
4549 BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
4550
4551 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at TLP level='||SQL%rowcount); END IF;
4552
4553 -- TODO: Similar SQL will be used to validate Artributes TLP for Req Templates
4554
4555 l_progress := '110';
4556 -- Add error message into INTERFACE_ERRORS table
4557 FOR i in 1..l_err_attr_values_tlp_ids.COUNT
4558 LOOP
4559 PO_R12_CAT_UPG_UTL.add_fatal_error(
4560 p_interface_header_id => l_err_header_ids(i),
4561 p_interface_line_id => l_err_line_ids(i),
4562 p_interface_attr_values_tlp_id => l_err_attr_values_tlp_ids(i),
4563 p_error_message_name => 'PO_CAT_UPG_INVALID_TLP_ROW',
4564 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4565 p_column_name => 'INTERFACE_ATTR_VALUES_TLP_ID'
4566 );
4567 END LOOP;
4568
4569 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4570 EXCEPTION
4571 WHEN OTHERS THEN
4572 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4573 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4574 END validate_create_action;
4575
4576 --------------------------------------------------------------------------------
4577 --Start of Comments
4578 --Name: validate_add_action
4579 --Pre-reqs:
4580 -- The iP catalog data is populated in PO Interface tables.
4581 --Modifies:
4582 -- a) PO_INTERFACE_ERRORS table: Inserts error messages for those rows that
4583 -- failed validation.
4584 -- b) FND_MSG_PUB on unhandled exceptions.
4585 --Locks:
4586 -- None.
4587 --Function:
4588 -- Validates ADD actions at line, attribute and TLP levels
4589 -- * For Headers,
4590 -- ADD action is not allowed.
4591 -- * For Lines,
4592 -- org_id check same as above for headers
4593 -- checks that a PO_HEADER_ID is provided where the line needs to be added to
4594 -- checks that the header document exists and was created by the Migration program
4595 -- * For Attributes,
4596 -- checks that a PO_LINE_ID is provided for which the attribute needs to be created for.
4597 -- checks that the document line exists for the given po_line_id
4598 -- * For Attributes TLP,
4599 -- checks that a PO_LINE_ID is provided for which the attribute TLP needs to be created for
4600 -- checks that the document line exists for the given po_line_id
4601 -- checks that the TLP row for that lang does not already exist
4602 --
4603 -- This API should be called during the upgrade phase only.
4604 --Parameters:
4605 --IN:
4606 --p_validate_only_mode
4607 -- Indicates if the API is being called in a Validate Only mode or not
4608 --OUT:
4609 -- None
4610 --
4611 --End of Comments
4612 --------------------------------------------------------------------------------
4613 PROCEDURE validate_add_action
4614 (
4615 p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
4616 )
4617 IS
4618 l_api_name CONSTANT VARCHAR2(30) := 'validate_add_action';
4619 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
4620 l_progress VARCHAR2(3) := '000';
4621
4622 l_err_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4623 l_err_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4624 l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4625 l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4626 BEGIN
4627 l_progress := '010';
4628 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4629
4630 -----------------------------------------------------------------------------
4631 -- ADD action validations for Headers
4632 -----------------------------------------------------------------------------
4633
4634 -- For Headers,
4635 -- ADD action is not allowed. Use ORIGINAL.
4636
4637 -- SQL What: validate add action at header level
4638 -- SQL Why : To find incorrect action
4639 -- SQL Join: processing_id, action
4640 UPDATE PO_HEADERS_INTERFACE POHI
4641 SET POHI.processing_id = -POHI.processing_id,
4642 POHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4643 WHERE POHI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4644 AND POHI.action = 'ADD'
4645 RETURNING POHI.interface_header_id
4646 BULK COLLECT INTO l_err_header_ids;
4647
4648 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ADD action at Header level='||SQL%rowcount); END IF;
4649
4650 l_progress := '020';
4651 -- Add error message into INTERFACE_ERRORS table
4652 FOR i in 1..l_err_header_ids.COUNT
4653 LOOP
4654 PO_R12_CAT_UPG_UTL.add_fatal_error(
4655 p_interface_header_id => l_err_header_ids(i),
4656 p_error_message_name => 'PO_CAT_UPG_ADD_NOT_ALLOWED',
4657 p_table_name => 'PO_HEADERS_INTERFACE',
4658 p_column_name => 'ACTION'
4659 );
4660 END LOOP;
4661
4662 l_progress := '030';
4663 -- Cascade errors down to attribute and TLP levels
4664 -- Skip cascading of errors in Validate Only mode (iP Requirement - all errors should be reported)
4665 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4666 PO_R12_CAT_UPG_UTL.reject_headers_intf('INTERFACE_HEADER_ID', l_err_header_ids, 'Y');
4667 END IF;
4668
4669 -----------------------------------------------------------------------------
4670 -- ADD action validations for Lines
4671 -----------------------------------------------------------------------------
4672
4673 l_progress := '040';
4674 -- For Lines,
4675 -- check that a PO_HEADER_ID is provided where the line needs to be added to
4676 -- (The Line may be added to a Line created by -12, or a pre-existing Header)
4677
4678 -- SQL What: validate ADD action at line level
4679 -- SQL Why : To find incorrect action
4680 -- SQL Join: processing_id, action, po_header_id
4681 UPDATE PO_LINES_INTERFACE POLI
4682 SET POLI.processing_id = -POLI.processing_id,
4683 POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4684 WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4685 AND POLI.action = PO_R12_CAT_UPG_PVT.g_action_line_create -- ADD
4686 AND (POLI.interface_header_id IS NULL OR
4687 (POLI.po_header_id IS NOT NULL AND
4688 NOT EXISTS
4689 (SELECT 'PO_HEADER_ID points to valid doc'
4690 FROM PO_HEADERS_ALL POH
4691 WHERE POH.po_header_id = POLI.po_header_id)))
4692 RETURNING POLI.interface_line_id, POLI.interface_header_id
4693 BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
4694
4695 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ADD action at Line level='||SQL%rowcount); END IF;
4696
4697 l_progress := '050';
4698 -- Add error message into INTERFACE_ERRORS table
4699 FOR i in 1..l_err_line_ids.COUNT
4700 LOOP
4701 PO_R12_CAT_UPG_UTL.add_fatal_error(
4702 p_interface_header_id => l_err_header_ids(i),
4703 p_interface_line_id => l_err_line_ids(i),
4704 p_error_message_name => 'PO_CAT_UPG_INVALID_HEADER_ID',
4705 p_table_name => 'PO_LINES_INTERFACE',
4706 p_column_name => 'PO_HEADER_ID'
4707 );
4708 END LOOP;
4709
4710 l_progress := '060';
4711 -- Cascade errors down to attribute and TLP levels
4712 -- Skip cascading of errors in Validate Only mode (iP Requirement - all errors should be reported)
4713 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4714 PO_R12_CAT_UPG_UTL.reject_attr_values_intf('INTERFACE_LINE_ID', l_err_line_ids);
4715 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
4716 END IF;
4717
4718 -----------------------------------------------------------------------------
4719 -- ORIGINAL action validations for Attribute Values
4720 -----------------------------------------------------------------------------
4721
4722 l_progress := '070';
4723 -- For Attributes,
4724 -- ORIGINAL action is not allowed. Use ADD.
4725
4726 -- SQL What: validate create action at attr level
4727 -- SQL Why : To find incorrect action
4728 -- SQL Join: processing_id, action, po_line_id
4729 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4730 SET POAI.processing_id = -POAI.processing_id,
4731 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4732 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4733 AND POAI.action = 'ORIGINAL'
4734 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4735 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4736
4737 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ORIGINAL action at Attr level='||SQL%rowcount); END IF;
4738
4739 -- TODO: Similar SQL will be used to validate Artributes for Req Templates
4740
4741 l_progress := '080';
4742 -- Add error message into INTERFACE_ERRORS table
4743 FOR i in 1..l_err_attr_values_ids.COUNT
4744 LOOP
4745 PO_R12_CAT_UPG_UTL.add_fatal_error(
4746 p_interface_header_id => l_err_header_ids(i),
4747 p_interface_line_id => l_err_line_ids(i),
4748 p_interface_attr_values_id => l_err_attr_values_ids(i),
4749 p_error_message_name => 'PO_CAT_UPG_ORIG_NOT_ALLOWED',
4750 p_table_name => 'PO_ATTR_VALUES_INTERFACE',
4751 p_column_name => 'ACTION'
4752 );
4753 END LOOP;
4754
4755 l_progress := '090';
4756 -- Cascade errors down to TLP level
4757 -- Skip cascading of errors in Validate Only mode.
4758 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4759 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
4760 END IF;
4761
4762 -----------------------------------------------------------------------------
4763 -- ORIGINAL action validations for Attribute Values TLP
4764 -----------------------------------------------------------------------------
4765
4766 l_progress := '100';
4767 -- For Attribute Values TLP,
4768 -- ORIGINAL action is not allowed. Use ADD.
4769
4770 -- SQL What: validate create action at attr TLP level
4771 -- SQL Why : To find incorrect action
4772 -- SQL Join: processing_id, action, po_line_id
4773 UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
4774 SET POATI.processing_id = -POATI.processing_id,
4775 POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4776 WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4777 AND POATI.action = 'ORIGINAL'
4778 RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
4779 BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
4780
4781 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ORIGINAL action at TLP level='||SQL%rowcount); END IF;
4782
4783 -- TODO: Similar SQL will be used to validate Artributes TLP for Req Templates
4784
4785 l_progress := '110';
4786 -- Add error message into INTERFACE_ERRORS table
4787 FOR i in 1..l_err_attr_values_tlp_ids.COUNT
4788 LOOP
4789 PO_R12_CAT_UPG_UTL.add_fatal_error(
4790 p_interface_header_id => l_err_header_ids(i),
4791 p_interface_line_id => l_err_line_ids(i),
4792 p_interface_attr_values_tlp_id => l_err_attr_values_tlp_ids(i),
4793 p_error_message_name => 'PO_CAT_UPG_ORIG_NOT_ALLOWED',
4794 p_table_name => 'PO_ATTR_VALUES_TLP_INTERFACE',
4795 p_column_name => 'ACTION'
4796 );
4797 END LOOP;
4798
4799 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4800 EXCEPTION
4801 WHEN OTHERS THEN
4802 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4803 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4804 END validate_add_action;
4805
4806 --------------------------------------------------------------------------------
4807 --Start of Comments
4808 --Name: validate_update_action
4809 --Pre-reqs:
4810 -- The iP catalog data is populated in PO Interface tables.
4811 --Modifies:
4812 -- a) PO_INTERFACE_ERRORS table: Inserts error messages for those rows that
4813 -- failed validation.
4814 -- b) FND_MSG_PUB on unhandled exceptions.
4815 --Locks:
4816 -- None.
4817 --Function:
4818 -- Validates UPDATE actions at header, line, attribute and TLP levels
4819 -- * For Headers,
4820 -- check that the ORG_ID is provided (used for defaulting from PSP, etc.)
4821 -- checks that a PO_HEADER_ID is provided that needs to be updated
4822 -- checks that the po_header_id points to a valid document
4823 -- * For Lines,
4824 -- checks that a PO_LINE_ID is provided that needs to be updated
4825 -- checks that the po_line_id points to a valid document
4826 -- * For Attributes,
4827 -- checks that a PO_LINE_ID is provided for which attr needs to be updated
4828 -- checks that the po_line_id points to a valid document
4829 -- * For Attributes TLP,
4830 -- checks that a PO_LINE_ID is provided for which TLP needs to be updated
4831 -- checks that the po_line_id points to a valid document
4832 -- check that a TLP row for the given language exists for updation
4833 --
4834 -- This API should be called during the upgrade phase only.
4835 --Parameters:
4836 --IN:
4837 --p_validate_only_mode
4838 -- Indicates if the API is being called in a Validate Only mode or not
4839 --OUT:
4840 -- None
4841 --
4842 --End of Comments
4843 --------------------------------------------------------------------------------
4844 PROCEDURE validate_update_action
4845 (
4846 p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
4847 )
4848 IS
4849 l_api_name CONSTANT VARCHAR2(30) := 'validate_update_action';
4850 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
4851 l_progress VARCHAR2(3) := '000';
4852
4853 l_err_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4854 l_err_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4855 l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4856 l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4857 BEGIN
4858 l_progress := '010';
4859 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4860
4861 -----------------------------------------------------------------------------
4862 -- UPDATE action validations for Headers
4863 -----------------------------------------------------------------------------
4864
4865 -- For Headers,
4866 -- check that a PO_HEADER_ID is provided that needs to be updated
4867 -- check that the po_header_id points to an existing document
4868
4869 -- SQL What: validate update action at Header level
4870 -- SQL Why : To find incorrect action
4871 -- SQL Join: processing_id, action, po_header_id
4872 UPDATE PO_HEADERS_INTERFACE POHI
4873 SET POHI.processing_id = -POHI.processing_id,
4874 POHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4875 WHERE POHI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4876 AND POHI.action = 'UPDATE'
4877 AND (POHI.po_header_id is null
4878 OR NOT EXISTS
4879 (SELECT 'document exists'
4880 FROM PO_HEADERS_ALL POH
4881 WHERE POH.po_header_id = POHI.po_header_id))
4882 RETURNING POHI.interface_header_id
4883 BULK COLLECT INTO l_err_header_ids;
4884
4885 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Header level='||SQL%rowcount); END IF;
4886
4887 l_progress := '020';
4888 -- Add error message into INTERFACE_ERRORS table
4889 FOR i in 1..l_err_header_ids.COUNT
4890 LOOP
4891 PO_R12_CAT_UPG_UTL.add_fatal_error(
4892 p_interface_header_id => l_err_header_ids(i),
4893 p_error_message_name => 'PO_CAT_UPG_INVALID_HEADER_ID',
4894 p_table_name => 'PO_HEADERS_INTERFACE',
4895 p_column_name => 'PO_HEADER_ID'
4896 );
4897 END LOOP;
4898
4899 l_progress := '030';
4900 -- Cascade errors down to attribute and TLP levels
4901 -- Skip cascading of errors in Validate Only mode.
4902 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4903 PO_R12_CAT_UPG_UTL.reject_lines_intf(p_id_param_type => 'INTERFACE_HEADER_ID',
4904 p_id_tbl => l_err_header_ids,
4905 p_cascade => 'Y');
4906 END IF;
4907
4908 -----------------------------------------------------------------------------
4909 -- UPDATE action validations for Lines
4910 -----------------------------------------------------------------------------
4911
4912 -- For Lines,
4913 -- check that a PO_LINE_ID is provided that needs to be updated
4914 -- check that the po_line_id points to an existing document
4915
4916 -- SQL What: validate update action at Line level
4917 -- SQL Why : To find incorrect action
4918 -- SQL Join: processing_id, action, po_line_id
4919 UPDATE PO_LINES_INTERFACE POLI
4920 SET POLI.processing_id = -POLI.processing_id,
4921 POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4922 WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4923 AND POLI.action = 'UPDATE'
4924 AND (POLI.po_line_id is null
4925 OR NOT EXISTS
4926 (SELECT 'line exists'
4927 FROM PO_LINES_ALL POL
4928 WHERE POL.po_line_id = POLI.po_line_id))
4929 RETURNING POLI.interface_line_id, POLI.interface_header_id
4930 BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
4931
4932 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Line level='||SQL%rowcount); END IF;
4933
4934 l_progress := '040';
4935 -- Add error message into INTERFACE_ERRORS table
4936 FOR i in 1..l_err_line_ids.COUNT
4937 LOOP
4938 PO_R12_CAT_UPG_UTL.add_fatal_error(
4939 p_interface_header_id => l_err_header_ids(i),
4940 p_interface_line_id => l_err_line_ids(i),
4941 p_error_message_name => 'PO_CAT_UPG_INVALID_LINE_ID',
4942 p_table_name => 'po_lines_interface',
4943 p_column_name => 'PO_LINE_ID'
4944 );
4945 END LOOP;
4946
4947 l_progress := '050';
4948 -- Cascade errors down to attribute and TLP levels
4949 -- Skip cascading of errors in Validate Only mode.
4950 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
4951 PO_R12_CAT_UPG_UTL.reject_attr_values_intf('INTERFACE_LINE_ID', l_err_line_ids);
4952 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
4953 END IF;
4954
4955 -----------------------------------------------------------------------------
4956 -- UPDATE action validations for Attribute Values
4957 -----------------------------------------------------------------------------
4958
4959 l_progress := '060';
4960 -- For Attributes,
4961 -- If all 3 Primary Keys are NULL, its an error
4962 -- We need at least one PK reference to update in txn tables
4963 -- If PO_LINE_ID is given then RT keys must be null
4964 -- IF PO_LINE_ID is given, then Attr for line must exist
4965 -- IF RT keys is given, then Attr for RT line must exist
4966
4967 -- SQL What: validate update action at attr level
4968 -- SQL Why : To find incorrect action
4969 -- SQL Join: processing_id, action, po_line_id
4970 UPDATE PO_ATTR_VALUES_INTERFACE POAI
4971 SET POAI.processing_id = -POAI.processing_id,
4972 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
4973 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
4974 AND POAI.action = 'UPDATE'
4975 AND (
4976 -- the following are NOT NULL columns in the TXN table, so they must
4977 -- have a not null value in the INTF table as well.
4978 (
4979 POAI.po_line_id IS NULL OR
4980 POAI.req_template_name IS NULL OR
4981 POAI.req_template_line_num IS NULL OR
4982 POAI.org_id IS NULL OR
4983 POAI.inventory_item_id IS NULL
4984 --OR POAI.ip_category_id IS NULL
4985 )
4986 OR
4987 -- If all 3 Primary Keys are -2, its an error
4988 -- We need at least one PK reference to update in txn tables
4989 (POAI.po_line_id = g_NOT_REQUIRED_ID
4990 AND (POAI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE
4991 OR POAI.req_template_line_num = g_NOT_REQUIRED_ID))
4992
4993 ---------------------------------------------------
4994 -- If PO_LINE_ID is given then RT keys must be null
4995 OR
4996 (POAI.po_line_id <> g_NOT_REQUIRED_ID
4997 AND (POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
4998 OR POAI.req_template_line_num <> g_NOT_REQUIRED_ID))
4999 ---------------------------------------------------
5000
5001 -- IF PO_LINE_ID is given, then Attr for line must exist
5002 OR
5003 (POAI.po_line_id <> g_NOT_REQUIRED_ID
5004 AND NOT EXISTS
5005 (SELECT 'Attribute row exists for PO Line'
5006 FROM PO_ATTRIBUTE_VALUES POAV
5007 WHERE POAV.po_line_id = POAI.po_line_id))
5008 -- IF RT keys is given, then Attr for RT line must exist
5009 OR
5010 (POAI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
5011 AND POAI.req_template_line_num <> g_NOT_REQUIRED_ID
5012 AND NOT EXISTS
5013 (SELECT 'Attribute row exists for Req Template Line'
5014 FROM PO_ATTRIBUTE_VALUES POAV
5015 WHERE POAV.req_template_name = POAI.req_template_name
5016 AND POAV.req_template_line_num = POAI.req_template_line_num))
5017 )
5018 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
5019 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
5020
5021 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Attr level='||SQL%rowcount); END IF;
5022
5023 l_progress := '070';
5024 -- Add error message into INTERFACE_ERRORS table
5025 FOR i in 1..l_err_attr_values_ids.COUNT
5026 LOOP
5027 PO_R12_CAT_UPG_UTL.add_fatal_error(
5028 p_interface_header_id => l_err_header_ids(i),
5029 p_interface_line_id => l_err_line_ids(i),
5030 p_interface_attr_values_id => l_err_attr_values_ids(i),
5031 p_error_message_name => 'PO_CAT_UPG_INVALID_LINE_ID',
5032 p_table_name => 'po_attr_values_interface',
5033 p_column_name => 'PO_LINE_ID'
5034 );
5035 END LOOP;
5036
5037 l_progress := '080';
5038 -- Cascade errors down to TLP level
5039 -- Skip cascading of errors in Validate Only mode.
5040 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
5041 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
5042 END IF;
5043
5044 -----------------------------------------------------------------------------
5045 -- UPDATE action validations for Attribute Values TLP
5046 -----------------------------------------------------------------------------
5047
5048 l_progress := '090';
5049 -- For Attribute Values TLP,
5050 -- If all 3 Primary Keys are NULL, its an error
5051 -- We need at least one PK reference to update in txn tables
5052 -- If language is NULL, it is an error.
5053 -- If PO_LINE_ID is given then RT keys must be null
5054 -- IF PO_LINE_ID is given, then Attr for line must exist
5055 -- IF RT keys is given, then Attr for RT line must exist
5056
5057 -- SQL What: validate update action at attr TLP level
5058 -- SQL Why : To find incorrect action
5059 -- SQL Join: processing_id, action, po_line_id
5060 UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
5061 SET POATI.processing_id = -POATI.processing_id,
5062 POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
5063 WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
5064 AND POATI.action = 'UPDATE'
5065 AND (
5066 -- the following are NOT NULL columns in the TXN table, so they must
5067 -- have a not null value in the INTF table as well.
5068 (
5069 POATI.po_line_id IS NULL OR
5070 POATI.req_template_name IS NULL OR
5071 POATI.req_template_line_num IS NULL OR
5072 POATI.org_id IS NULL OR
5073 POATI.inventory_item_id IS NULL OR
5074 POATI.ip_category_id IS NULL --OR
5075 --POATI.description IS NULL
5076 )
5077
5078 OR POATI.language IS NULL
5079
5080 OR
5081 -- If all 3 Primary Keys are NULL, its an error
5082 -- We need at least one PK reference to update in txn tables
5083 (POATI.po_line_id = g_NOT_REQUIRED_ID
5084 AND (POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE
5085 OR POATI.req_template_line_num = g_NOT_REQUIRED_ID))
5086
5087 ---------------------------------------------------
5088 -- If PO_LINE_ID is given then RT keys must be null
5089 OR
5090 (POATI.po_line_id <> g_NOT_REQUIRED_ID
5091 AND (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
5092 OR POATI.req_template_line_num <> g_NOT_REQUIRED_ID))
5093 ---------------------------------------------------
5094
5095 -- IF PO_LINE_ID is given, then Attr for line must exist
5096 OR
5097 (POATI.po_line_id <> g_NOT_REQUIRED_ID
5098 AND NOT EXISTS
5099 (SELECT 'Attribute TLP row exists for PO Line'
5100 FROM PO_ATTRIBUTE_VALUES_TLP POTLP
5101 WHERE POTLP.po_line_id = POATI.po_line_id
5102 AND POTLP.language = POATI.language))
5103 -- IF RT keys is given, then:
5104 -- 1. ORG_ID must be given
5105 -- 2. TLP row for RT line must exist
5106 OR
5107 (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
5108 AND POATI.req_template_line_num <> g_NOT_REQUIRED_ID
5109 AND (POATI.org_id = g_NOT_REQUIRED_ID OR
5110 NOT EXISTS
5111 (SELECT 'Attribute row exists for Req Template Line'
5112 FROM PO_ATTRIBUTE_VALUES_TLP POTLP
5113 WHERE POTLP.req_template_name = POATI.req_template_name
5114 AND POTLP.req_template_line_num = POATI.req_template_line_num
5115 AND POTLP.org_id = POATI.org_id
5116 AND POTLP.language = POATI.language)))
5117 )
5118 RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
5119 BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
5120
5121 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at TLP level='||SQL%rowcount); END IF;
5122
5123 l_progress := '100';
5124 -- Add error message into INTERFACE_ERRORS table
5125 FOR i in 1..l_err_attr_values_tlp_ids.COUNT
5126 LOOP
5127 PO_R12_CAT_UPG_UTL.add_fatal_error(
5128 p_interface_header_id => l_err_header_ids(i),
5129 p_interface_line_id => l_err_line_ids(i),
5130 p_interface_attr_values_tlp_id => l_err_attr_values_tlp_ids(i),
5131 p_error_message_name => 'PO_CAT_UPG_INVALID_LINE_ID',
5132 p_table_name => 'po_attr_values_tlp_interface',
5133 p_column_name => 'PO_LINE_ID'
5134 );
5135 END LOOP;
5136
5137 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5138 EXCEPTION
5139 WHEN OTHERS THEN
5140 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5141 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5142 END validate_update_action;
5143
5144 --------------------------------------------------------------------------------
5145 --Start of Comments
5146 --Name: validate_delete_action
5147 --Pre-reqs:
5148 -- The iP catalog data is populated in PO Interface tables.
5149 --Modifies:
5150 -- a) PO_INTERFACE_ERRORS table: Inserts error messages for those rows that
5151 -- failed validation.
5152 -- b) FND_MSG_PUB on unhandled exceptions.
5153 --Locks:
5154 -- None.
5155 --Function:
5156 -- Validates DELETE action at header, line, attribute and TLP levels
5157 -- * For Headers,
5158 -- checks that a PO_HEADER_ID is provided that needs to be deleted
5159 -- checks that the document exists and was created by the Migration program
5160 -- * For Lines,
5161 -- checks that a PO_LINE_ID is provided that needs to be deleted
5162 -- checks that the document exists and was created by the Migration program
5163 -- * For Attributes,
5164 -- Never allow deletion of attribute row.
5165 -- * For Attributes TLP,
5166 -- checks that a PO_LINE_ID is provided for which TLP needs to be deleted
5167 -- Never allow deletion of TLP for for the created_lang
5168 -- check that a TLP row for the given language exists for deletion
5169 --
5170 -- This API should be called during the upgrade phase only.
5171 --Parameters:
5172 --IN:
5173 --p_validate_only_mode
5174 -- Indicates if the API is being called in a Validate Only mode or not
5175 --OUT:
5176 -- None
5177 --
5178 --End of Comments
5179 --------------------------------------------------------------------------------
5180 PROCEDURE validate_delete_action
5181 (
5182 p_validate_only_mode IN VARCHAR2 default FND_API.G_FALSE
5183 )
5184 IS
5185 l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_action';
5186 l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
5187 l_progress VARCHAR2(3) := '000';
5188
5189 l_err_line_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
5190 l_err_header_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
5191 l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
5192 l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
5193
5194 l_base_language FND_LANGUAGES.language_code%TYPE;
5195 BEGIN
5196 l_progress := '010';
5197 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
5198
5199 -----------------------------------------------------------------------------
5200 -- DELETE action validations for Headers
5201 -----------------------------------------------------------------------------
5202
5203 -- For Headers,
5204 -- check that a PO_HEADER_ID is provided that needs to be deleted
5205 -- check that the po_header_id points to a valid document
5206
5207 -- SQL What: validate delete action at header level
5208 -- SQL Why : To find incorrect action
5209 -- SQL Join: processing_id, action, po_header_id
5210 UPDATE PO_HEADERS_INTERFACE POHI
5211 SET POHI.processing_id = -POHI.processing_id,
5212 POHI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
5213 WHERE POHI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
5214 AND POHI.action = 'DELETE'
5215 AND (POHI.po_header_id is null
5216 OR EXISTS
5217 (SELECT 'document not created by migration program'
5218 FROM PO_HEADERS_ALL POH
5219 WHERE POH.po_header_id = POHI.po_header_id
5220 AND POH.created_by <> PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER))
5221 RETURNING POHI.interface_header_id
5222 BULK COLLECT INTO l_err_header_ids;
5223
5224 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Header level='||SQL%rowcount); END IF;
5225
5226 l_progress := '020';
5227 -- Add error message into INTERFACE_ERRORS table
5228 FOR i in 1..l_err_header_ids.COUNT
5229 LOOP
5230 PO_R12_CAT_UPG_UTL.add_fatal_error(
5231 p_interface_header_id => l_err_header_ids(i),
5232 p_error_message_name => 'PO_CAT_UPG_INVALID_HEADER_ID',
5233 p_table_name => 'po_headers_interface',
5234 p_column_name => 'PO_HEADER_ID'
5235 );
5236 END LOOP;
5237
5238 l_progress := '030';
5239 -- Cascade errors down to attribute and TLP levels
5240 -- Skip cascading of errors in Validate Only mode.
5241 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
5242 PO_R12_CAT_UPG_UTL.reject_lines_intf(p_id_param_type => 'INTERFACE_HEADER_ID',
5243 p_id_tbl => l_err_header_ids,
5244 p_cascade => 'Y');
5245 END IF;
5246
5247 -----------------------------------------------------------------------------
5248 -- DELETE action validations for Lines
5249 -----------------------------------------------------------------------------
5250
5251 -- For Lines,
5252 -- check that a PO_LINE_ID is provided that needs to be deleted
5253 -- check that the document was created by the Migration program
5254
5255 -- SQL What: validate delete action at line level
5256 -- SQL Why : To find incorrect action
5257 -- SQL Join: processing_id, action, po_line_id
5258 UPDATE PO_LINES_INTERFACE POLI
5259 SET POLI.processing_id = -POLI.processing_id,
5260 POLI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
5261 WHERE POLI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
5262 AND POLI.action = 'DELETE'
5263 AND (POLI.po_line_id is null
5264 OR EXISTS
5265 (SELECT 'line not created by migration program'
5266 FROM PO_LINES_ALL POL
5267 WHERE POL.po_line_id = POLI.po_line_id
5268 AND POL.created_by <> PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER))
5269 RETURNING POLI.interface_line_id, POLI.interface_header_id
5270 BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
5271
5272 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Line level='||SQL%rowcount); END IF;
5273
5274 l_progress := '040';
5275 -- Add error message into INTERFACE_ERRORS table
5276 FOR i in 1..l_err_line_ids.COUNT
5277 LOOP
5278 PO_R12_CAT_UPG_UTL.add_fatal_error(
5279 p_interface_header_id => l_err_header_ids(i),
5280 p_interface_line_id => l_err_line_ids(i),
5281 p_error_message_name => 'PO_CAT_UPG_INVALID_LINE_ID',
5282 p_table_name => 'po_lines_interface',
5283 p_column_name => 'PO_LINE_ID'
5284 );
5285 END LOOP;
5286
5287 l_progress := '050';
5288 -- Cascade errors down to attribute and TLP levels
5289 -- Skip cascading of errors in Validate Only mode.
5290 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
5291 PO_R12_CAT_UPG_UTL.reject_attr_values_intf('INTERFACE_LINE_ID', l_err_line_ids);
5292 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
5293 END IF;
5294
5295 -----------------------------------------------------------------------------
5296 -- DELETE action validations for Attribute Values
5297 -----------------------------------------------------------------------------
5298
5299 l_progress := '060';
5300 -- For Attributes,
5301 -- Never allow deletion of attribute row
5302 -- If required for Lines created by -12, the Line level delete would delete
5303 -- the Attr row also.
5304 -- For ReqTemplates you may only UPDATE an Attr row, you may not
5305 -- delete it.
5306
5307 -- SQL What: validate delete action at attr level
5308 -- SQL Why : To find incorrect action
5309 -- SQL Join: processing_id, action
5310 UPDATE PO_ATTR_VALUES_INTERFACE POAI
5311 SET POAI.processing_id = -POAI.processing_id,
5312 POAI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
5313 WHERE POAI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
5314 AND POAI.action = 'DELETE'
5315 RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
5316 BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
5317
5318 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Attr level='||SQL%rowcount); END IF;
5319
5320 l_progress := '070';
5321 -- Add error message into INTERFACE_ERRORS table
5322 FOR i in 1..l_err_attr_values_ids.COUNT
5323 LOOP
5324 PO_R12_CAT_UPG_UTL.add_fatal_error(
5325 p_interface_header_id => l_err_header_ids(i),
5326 p_interface_line_id => l_err_line_ids(i),
5327 p_interface_attr_values_id => l_err_attr_values_ids(i),
5328 p_error_message_name => 'PO_CAT_UPG_CANT_DEL_ATTR',
5329 p_table_name => 'po_attr_values_interface',
5330 p_column_name => 'ATTRIBUTE_VALUE_ID'
5331 );
5332 END LOOP;
5333
5334 l_progress := '080';
5335 -- Cascade errors down to TLP level
5336 -- Skip cascading of errors in Validate Only mode.
5337 IF (p_validate_only_mode = FND_API.G_FALSE) THEN
5338 PO_R12_CAT_UPG_UTL.reject_attr_values_tlp_intf('INTERFACE_LINE_ID', l_err_line_ids);
5339 END IF;
5340
5341 -----------------------------------------------------------------------------
5342 -- DELETE action validations for Attribute Values TLP
5343 -----------------------------------------------------------------------------
5344
5345 l_progress := '090';
5346 l_base_language := PO_R12_CAT_UPG_UTL.get_base_lang;
5347
5348 -- For Attribute Values TLP,
5349 -- Check that a Unique Key is provided for which the attribute TLP needs to be deleted
5350 -- Unique key for TLP table is:
5351 --
5352 -- LANGUAGE
5353 -- PO_LINE_ID
5354 -- (REQ_TEMPLATE_NAME, REQ_TEMPLATE_LINE_NUM, ORG_ID)
5355 --
5356 -- For GBPA/BPA/Quotations:
5357 -- Never allow deletion of TLP row for created_language
5358 -- For ReqTemplates:
5359 -- Never allow deletion of TLP row for base_language.
5360 --
5361 -- Check that a TLP row for the given language exists for deletion
5362 --
5363 -- Note: If required for Lines created by -12, the Line level delete would delete
5364 -- the Attr TLP row also.
5365 -- For GBPA/BPA/Quotations you may only UPDATE an Attr row with the created_language
5366 -- you may not delete it.
5367 -- For ReqTemplates s you may only UPDATE an Attr row with the base_language
5368 -- you may not delete it.
5369
5370 -- SQL What: validate delete action at attr TLP level
5371 -- SQL Why : To find incorrect action
5372 -- SQL Join: processing_id, action, ppo_line_id
5373 UPDATE PO_ATTR_VALUES_TLP_INTERFACE POATI
5374 SET POATI.processing_id = -POATI.processing_id,
5375 POATI.process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_REJECTED
5376 WHERE POATI.processing_id = PO_R12_CAT_UPG_PVT.g_processing_id
5377 AND POATI.action = 'DELETE'
5378 AND (
5379 -- the following are NOT NULL columns in the TXN table, so they must
5380 -- have a not null value in the INTF table as well.
5381 (
5382 POATI.po_line_id IS NULL OR
5383 POATI.req_template_name IS NULL OR
5384 POATI.req_template_line_num IS NULL OR
5385 POATI.org_id IS NULL
5386 )
5387
5388 OR POATI.language IS NULL
5389
5390 OR
5391 -- If all 3 Primary Keys are NULL, its an error
5392 -- We need at least one PK reference to update in txn tables
5393 (POATI.po_line_id = g_NOT_REQUIRED_ID
5394 AND (POATI.req_template_name = g_NOT_REQUIRED_REQ_TEMPLATE
5395 OR POATI.req_template_line_num = g_NOT_REQUIRED_ID))
5396
5397 ---------------------------------------------------
5398 -- If PO_LINE_ID is given then RT keys must be null
5399 OR
5400 (POATI.po_line_id <> g_NOT_REQUIRED_ID
5401 AND (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
5402 OR POATI.req_template_line_num <> g_NOT_REQUIRED_ID))
5403 ---------------------------------------------------
5404
5405 -- IF PO_LINE_ID is given, then Attr for line must exist
5406 OR
5407 (POATI.po_line_id <> g_NOT_REQUIRED_ID
5408 AND NOT EXISTS
5409 (SELECT 'Attribute TLP row exists for PO Line'
5410 FROM PO_ATTRIBUTE_VALUES_TLP POTLP
5411 WHERE POTLP.po_line_id = POATI.po_line_id
5412 AND POTLP.language = POATI.language))
5413 -- IF RT keys is given, then:
5414 -- 1. ORG_ID must be given
5415 -- 2. TLP row for RT line must exist
5416 OR
5417 (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE
5418 AND POATI.req_template_line_num <> g_NOT_REQUIRED_ID
5419 AND (POATI.org_id = g_NOT_REQUIRED_ID OR
5420 NOT EXISTS
5421 (SELECT 'Attribute row exists for Req Template Line'
5422 FROM PO_ATTRIBUTE_VALUES_TLP POTLP
5423 WHERE POTLP.req_template_name = POATI.req_template_name
5424 AND POTLP.req_template_line_num = POATI.req_template_line_num
5425 AND POTLP.org_id = POATI.org_id
5426 AND POTLP.language = POATI.language)))
5427 ---------------------------------------------------
5428
5429 -- For GBPA/BPA/Quotations, you can not delete TLP row for created_language
5430 OR (POATI.po_line_id <> g_NOT_REQUIRED_ID
5431 AND EXISTS
5432 (SELECT 'TLP row for deletion is specified for created_lang'
5433 FROM PO_LINES_ALL POL,
5434 PO_HEADERS_ALL POH
5435 WHERE POL.po_line_id = POATI.po_line_id
5436 AND POH.po_header_id = POL.po_header_id
5437 AND POH.created_language = POATI.language))
5438 -- For ReqTemplates, you can not delete TLP row for base_language
5439 OR (POATI.req_template_name <> g_NOT_REQUIRED_REQ_TEMPLATE AND
5440 POATI.req_template_line_num <> g_NOT_REQUIRED_ID AND
5441 POATI.language = l_base_language)
5442 )
5443 RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
5444 BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
5445
5446 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at TLP level='||SQL%rowcount); END IF;
5447
5448 l_progress := '100';
5449 -- Add error message into INTERFACE_ERRORS table
5450 FOR i in 1..l_err_attr_values_tlp_ids.COUNT
5451 LOOP
5452 PO_R12_CAT_UPG_UTL.add_fatal_error(
5453 p_interface_header_id => l_err_header_ids(i),
5454 p_interface_line_id => l_err_line_ids(i),
5455 p_interface_attr_values_tlp_id => l_err_attr_values_tlp_ids(i),
5456 p_error_message_name => 'PO_CAT_UPG_INVALID_LINE_ID',
5457 p_table_name => 'po_attr_values_tlp_interface',
5458 p_column_name => 'PO_LINE_ID'
5459 );
5460 END LOOP;
5461
5462 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5463 EXCEPTION
5464 WHEN OTHERS THEN
5465 IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5466 RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5467 END validate_delete_action;
5468
5469
5470 END PO_R12_CAT_UPG_VAL_PVT;
[Home] [Help]