[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_ATTR_PROCESS_PVT
Source
1 PACKAGE BODY PO_PDOI_ATTR_PROCESS_PVT AS
2 /* $Header: PO_PDOI_ATTR_PROCESS_PVT.plb 120.18 2011/12/01 12:24:16 sbontala ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_ATTR_PROCESS_PVT');
6
7 --------------------------------------------------------------------------
8 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
9 --------------------------------------------------------------------------
10
11 --------------------------------------------------------------------------
12 ---------------------- PUBLIC PROCEDURES ---------------------------------
13 --------------------------------------------------------------------------
14
15 -------------------------------------------------------------------------
16 --Start of Comments
17 --Name: open_attr_values
18 --Pre-reqs: None
19 --Modifies:
20 --Locks:
21 -- None
22 --Function:
23 -- open cursor which reads the attr values record in batch
24 --Parameters:
25 --IN:
26 -- p_max_intf_attr_values_id
27 -- maximal intf_attr_values_id processed in previous batches
28 --IN OUT:
29 -- x_attr_values_csr
30 -- cursor variable which points to next to-be-processed record
31 --OUT: None
32 --Returns:
33 --Notes:
34 --Testing:
35 --End of Comments
36 ------------------------------------------------------------------------
37 PROCEDURE open_attr_values
38 (
39 p_max_intf_attr_values_id IN NUMBER,
40 x_attr_values_csr OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
41 ) IS
42
43 d_api_name CONSTANT VARCHAR2(30) := 'open_attr_values';
44 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
45 d_position NUMBER;
46
47 BEGIN
48 d_position := 0;
49
50 IF (PO_LOG.d_proc) THEN
51 PO_LOG.proc_begin(d_module, 'p_max_intf_attr_values_id',
52 p_max_intf_attr_values_id);
53 END IF;
54
55 OPEN x_attr_values_csr FOR
56 SELECT intf_attrs.interface_attr_values_id,
57 intf_attrs.org_id,
58
59 -- attributes read from line
60 draft_lines.po_line_id,
61 draft_lines.ip_category_id,
62 draft_lines.item_id,
63
64 -- attributes read from header
65 intf_headers.draft_id,
66 -- attribute values id
67 NULL,
68 -- initial value for error_flag
69 FND_API.g_FALSE
70 FROM po_attr_values_interface intf_attrs,
71 po_lines_interface intf_lines,
72 po_headers_interface intf_headers,
73 po_lines_draft_all draft_lines
74 WHERE intf_attrs.interface_line_id = intf_lines.interface_line_id
75 AND intf_lines.interface_header_id = intf_headers.interface_header_id
76 AND intf_headers.draft_id = draft_lines.draft_id
77 AND intf_lines.po_line_id = draft_lines.po_line_id
78 AND intf_attrs.processing_id = PO_PDOI_PARAMS.g_processing_id
79 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
80 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
81 AND intf_attrs.interface_attr_values_id > p_max_intf_attr_values_id
82 AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
83 <> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
84 ORDER BY intf_attrs.interface_attr_values_id;
85
86 IF (PO_LOG.d_proc) THEN
87 PO_LOG.proc_end (d_module);
88 END IF;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 PO_MESSAGE_S.add_exc_msg
93 (
94 p_pkg_name => d_pkg_name,
95 p_procedure_name => d_api_name || '.' || d_position
96 );
97 RAISE;
98 END open_attr_values;
99
100 -------------------------------------------------------------------------
101 --Start of Comments
102 --Name: fetch_attr_values
103 --Pre-reqs: None
104 --Modifies:
105 --Locks:
106 -- None
107 --Function:
108 -- fetch attr values records based on batch size
109 --Parameters:
110 --IN:
111 --IN OUT:
112 -- x_attr_values_csr
113 -- cursor variable which points to next to-be-processed record
114 -- x_attr_values
115 -- record containing all attr values info within the batch
116 --OUT: None
117 --Returns:
118 --Notes:
119 --Testing:
120 --End of Comments
121 ------------------------------------------------------------------------
122 PROCEDURE fetch_attr_values
123 (
124 x_attr_values_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
125 x_attr_values OUT NOCOPY PO_PDOI_TYPES.attr_values_rec_type
126 ) IS
127
128 d_api_name CONSTANT VARCHAR2(30) := 'fetch_attr_values';
129 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
130 d_position NUMBER;
131
132 BEGIN
133 d_position := 0;
134
135 IF (PO_LOG.d_proc) THEN
136 PO_LOG.proc_begin(d_module);
137 END IF;
138
139 FETCH x_attr_values_csr BULK COLLECT INTO
140 x_attr_values.intf_attr_values_id_tbl,
141 x_attr_values.org_id_tbl,
142
143 -- attributes read from line
144 x_attr_values.ln_po_line_id_tbl,
145 x_attr_values.ln_ip_category_id_tbl,
146 x_attr_values.ln_item_id_tbl,
147
148 -- attributes read from header
149 x_attr_values.draft_id_tbl,
150
151 -- attribute values id
152 x_attr_values.attribute_values_id_tbl,
153
154 -- initial value for error_flag
155 x_attr_values.error_flag_tbl
156 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
157
158 IF (PO_LOG.d_proc) THEN
159 PO_LOG.proc_end (d_module);
160 END IF;
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 PO_MESSAGE_S.add_exc_msg
165 (
166 p_pkg_name => d_pkg_name,
167 p_procedure_name => d_api_name || '.' || d_position
168 );
169 RAISE;
170 END fetch_attr_values;
171
172 -------------------------------------------------------------------------
173 --Start of Comments
174 --Name: check_attr_actions
175 --Pre-reqs: None
176 --Modifies:
177 --Locks:
178 -- None
179 --Function:
180 -- check whether the action on each attr values row is CREATE/UPDATE;
181 -- If multiple rows are pointing to same po line, these rows will be
182 -- processed in separate groups.
183 --Parameters:
184 --IN:
185 --IN OUT:
186 -- x_processing_row_tbl
187 -- the procedure will only process rows which have non-empty
188 -- values in this pl/sql table
189 -- x_attr_values
190 -- record containing all attr values info within the batch
191 --OUT:
192 -- x_create_row_tbl
193 -- index of rows to be created in current group
194 -- x_update_row_tbl
195 -- index of rows to be updated in cuurent group
196 -- x_sync_attr_id_tbl
197 -- list of attr_value_ids of rows that need to be read from txn table
198 -- into draft table
199 -- x_sync_draft_id_tbl
200 -- corresponding draft_ids of rows that will be read from txn table
201 -- into draft table
202 --Returns:
203 --Notes:
204 --Testing:
205 --End of Comments
206 ------------------------------------------------------------------------
207 PROCEDURE check_attr_actions
208 (
209 x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
210 x_attr_values IN OUT NOCOPY PO_PDOI_TYPES.attr_values_rec_type,
211 x_merge_row_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
212 x_sync_attr_id_tbl OUT NOCOPY PO_TBL_NUMBER,
213 x_sync_draft_id_tbl OUT NOCOPY PO_TBL_NUMBER
214 ) IS
215
216 d_api_name CONSTANT VARCHAR2(30) := 'check_attr_actions';
217 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
218 d_position NUMBER;
219
220 -- ket value used to identify records in po_session_gt table
221 l_key PO_SESSION_GT.key%TYPE;
222
223 -- variables to save result read from po_session_gt
224 l_index_tbl PO_TBL_NUMBER;
225 l_result_tbl PO_TBL_NUMBER;
226 l_source_tbl PO_TBL_VARCHAR5; -- values can be 'draft' or 'txn'
227
228 l_index NUMBER;
229 l_counter NUMBER;
230
231 -- hash table to track whether same po_line_id exist within batch
232 l_attr_ref_tbl DBMS_SQL.NUMBER_TABLE;
233 BEGIN
234 d_position := 0;
235
236 IF (PO_LOG.d_proc) THEN
237 PO_LOG.proc_begin(d_module, 'x_processing_row_tbl.COUNT',
238 x_processing_row_tbl.COUNT);
239 l_index := x_processing_row_tbl.FIRST;
240 WHILE (l_index IS NOT NULL)
241 LOOP
242 PO_LOG.proc_begin(d_module, 'to be processed row num', l_index);
243 l_index := x_processing_row_tbl.NEXT(l_index);
244 END LOOP;
245 END IF;
246
247 x_sync_attr_id_tbl := PO_TBL_NUMBER();
248 x_sync_draft_id_tbl := PO_TBL_NUMBER();
249
250 l_key := PO_CORE_S.get_session_gt_nextval;
251
252 -- first check whether record exists in draft table
253 FORALL i IN INDICES OF x_processing_row_tbl
254 INSERT INTO po_session_gt(key, num1, num2, char1)
255 SELECT l_key,
256 x_processing_row_tbl(i),
257 attribute_values_id,
258 'DRAFT'
259 FROM po_attribute_values_draft
260 WHERE draft_id = x_attr_values.draft_id_tbl(i)
261 AND po_line_id = x_attr_values.ln_po_line_id_tbl(i)
262 AND org_id = x_attr_values.org_id_tbl(i);
263
264 d_position := 10;
265
266 -- second check whether record exists in txn table
267 -- it needs only to be done once since it won't change within batch processing
268 IF (x_processing_row_tbl.COUNT = x_attr_values.rec_count) THEN
269
270 d_position := 20;
271
272 FORALL i IN INDICES OF x_processing_row_tbl
273 INSERT INTO po_session_gt(key, num1, num2, char1)
274 SELECT l_key,
275 x_processing_row_tbl(i),
276 attribute_values_id,
277 'TXN'
278 FROM po_attribute_values
279 WHERE po_line_id = x_attr_values.ln_po_line_id_tbl(i)
280 AND org_id = x_attr_values.org_id_tbl(i);
281 END IF;
282
283 -- retrieve result from po_session_gt table
284 DELETE FROM po_session_gt
285 WHERE key = l_key
286 RETURNING num1, num2, char1 BULK COLLECT INTO
287 l_index_tbl, l_result_tbl, l_source_tbl;
288
289 -- set attr_values_id in x_attr_values
290 FOR i IN 1..l_index_tbl.COUNT
291 LOOP
292 l_index := l_index_tbl(i);
293
294 IF (PO_LOG.d_stmt) THEN
295 PO_LOG.stmt(d_module, d_position, 'index', l_index);
296 PO_LOG.stmt(d_module, d_position, 'current source exist?',
297 x_attr_values.source_tbl.EXISTS(l_index));
298 IF (x_attr_values.source_tbl.EXISTS(l_index)) THEN
299 PO_LOG.stmt(d_module, d_position, 'current source',
300 x_attr_values.source_tbl(l_index));
301 END IF;
302 PO_LOG.stmt(d_module, d_position, 'attr values id',
303 l_result_tbl(i));
304 PO_LOG.stmt(d_module, d_position, ' new source',
305 l_source_tbl(i));
306 END IF;
307
308 -- draft record will override txn record
309 IF (NOT x_attr_values.source_tbl.EXISTS(l_index) OR
310 x_attr_values.source_tbl(l_index) <> 'DRAFT') THEN
311 x_attr_values.attribute_values_id_tbl(l_index) := l_result_tbl(i);
312 x_attr_values.source_tbl(l_index) := l_source_tbl(i);
313 END IF;
314 END LOOP;
315
316 d_position := 30;
317
318 -- next, set actions on each record, if records with same po_line_id
319 -- exist, process them in separate groups
320 l_index := 0;
321 l_counter := x_processing_row_tbl.FIRST;
322 WHILE (l_counter IS NOT NULL)
323 LOOP
324 IF (PO_LOG.d_stmt) THEN
325 PO_LOG.stmt(d_module, d_position, 'counter', l_counter);
326 END IF;
327
328
329 -- check whether there is row existing in hashtable
330 IF (NOT l_attr_ref_tbl.EXISTS(x_attr_values.ln_po_line_id_tbl(l_counter))) THEN
331 -- register it in the hashtbale
332 l_attr_ref_tbl(x_attr_values.ln_po_line_id_tbl(l_counter)) := l_counter;
333
334 IF (PO_LOG.d_stmt) THEN
335 PO_LOG.stmt(d_module, d_position, 'row is handled in current loop');
336 PO_LOG.stmt(d_module, d_position, 'attr values id',
337 x_attr_values.attribute_values_id_tbl(l_counter));
338 END IF;
339
340 -- set actions
341 IF (x_attr_values.attribute_values_id_tbl(l_counter) IS NOT NULL) THEN
342 -- row existing in draft or txn tables
343 x_merge_row_tbl(l_counter) := l_counter;
344
345 -- for UPDATE action, track the rows that need to be synced from
346 -- txn table
347 IF (x_attr_values.source_tbl(l_counter) = 'TXN') THEN
348
349 l_index := l_index + 1;
350 x_sync_attr_id_tbl.EXTEND;
351 x_sync_draft_id_tbl.EXTEND;
352 x_sync_attr_id_tbl(l_index) := x_attr_values.attribute_values_id_tbl(l_counter);
353 x_sync_draft_id_tbl(l_index) := x_attr_values.draft_id_tbl(l_counter);
354 END IF;
355 ELSE
356 -- it is a new row
357 x_merge_row_tbl(l_counter) := l_counter;
358 END IF;
359
360 --mark rows as processed
361 x_processing_row_tbl.DELETE(l_counter);
362 END IF; -- IF (l_attr_ref_tbl(x_attr_values.po_line_id_tbl(i)) IS NULL)
363
364 l_counter := x_processing_row_tbl.NEXT(l_counter);
365 END LOOP;
366
367 --Bug 12980629
368 x_attr_values.source_tbl.DELETE;
369
370
371 IF (PO_LOG.d_proc) THEN
372 PO_LOG.proc_end (d_module);
373 END IF;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 PO_MESSAGE_S.add_exc_msg
378 (
379 p_pkg_name => d_pkg_name,
380 p_procedure_name => d_api_name || '.' || d_position
381 );
382 RAISE;
383 END check_attr_actions;
384
385 -------------------------------------------------------------------------
386 --Start of Comments
387 --Name: open_attr_values_tlp
388 --Pre-reqs: None
389 --Modifies:
390 --Locks:
391 -- None
392 --Function:
393 -- open cursor which reads the attr values tlp record in batch
394 --Parameters:
395 --IN:
396 -- p_max_intf_attr_values_tlp_id
397 -- maximal intf_attr_values_tlp_id processed in previous batches
398 --IN OUT:
399 -- x_attr_values_tlp_csr
400 -- cursor variable which points to next to-be-processed record
401 --OUT: None
402 --Returns:
403 --Notes:
404 --Testing:
405 --End of Comments
406 ------------------------------------------------------------------------
407 PROCEDURE open_attr_values_tlp
408 (
409 p_max_intf_attr_values_tlp_id IN NUMBER,
410 x_attr_values_tlp_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
411 ) IS
412
413 d_api_name CONSTANT VARCHAR2(30) := 'open_attr_values_tlp';
414 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
415 d_position NUMBER;
416
417 BEGIN
418 d_position := 0;
419
420 IF (PO_LOG.d_proc) THEN
421 PO_LOG.proc_begin(d_module);
422 END IF;
423
424 OPEN x_attr_values_tlp_csr FOR
425 SELECT intf_attrs_tlp.interface_attr_values_tlp_id,
426 intf_attrs_tlp.language,
427 intf_attrs_tlp.org_id,
428 intf_attrs_tlp.long_description, -- Bug7722053
429 -- attributes read from line
430 draft_lines.po_line_id,
431 draft_lines.ip_category_id,
432 draft_lines.item_id,
433 draft_lines.item_description,
434
435 -- attributes read from header
436 intf_headers.draft_id,
437
438 -- attr values tlp id
439 NULL,
440
441 -- initial value for error_flag
442 FND_API.g_FALSE
443 FROM po_attr_values_tlp_interface intf_attrs_tlp,
444 po_lines_interface intf_lines,
445 po_headers_interface intf_headers,
446 po_lines_draft_all draft_lines
447 WHERE intf_attrs_tlp.interface_line_id = intf_lines.interface_line_id
448 AND intf_lines.interface_header_id = intf_headers.interface_header_id
449 AND intf_headers.draft_id = draft_lines.draft_id
450 AND intf_lines.po_line_id = draft_lines.po_line_id
451 AND intf_attrs_tlp.processing_id = PO_PDOI_PARAMS.g_processing_id
452 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
453 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
454 AND intf_attrs_tlp.interface_attr_values_tlp_id > p_max_intf_attr_values_tlp_id
455 AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
456 <> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
457 ORDER BY intf_attrs_tlp.interface_attr_values_tlp_id;
458
459 IF (PO_LOG.d_proc) THEN
460 PO_LOG.proc_end (d_module);
461 END IF;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 PO_MESSAGE_S.add_exc_msg
466 (
467 p_pkg_name => d_pkg_name,
468 p_procedure_name => d_api_name || '.' || d_position
469 );
470 RAISE;
471 END open_attr_values_tlp;
472
473 -------------------------------------------------------------------------
474 --Start of Comments
475 --Name: fetch_attr_values_tlp
476 --Pre-reqs: None
477 --Modifies:
478 --Locks:
479 -- None
480 --Function:
481 -- fetch attr values tlp records based on batch size
482 --Parameters:
483 --IN:
484 --IN OUT:
485 -- x_attr_values_tlp_csr
486 -- cursor variable which points to next to-be-processed record
487 -- x_attr_values_tlp
488 -- record containing all attr values tlp info within the batch
489 --OUT: None
490 --Returns:
491 --Notes:
492 --Testing:
493 --End of Comments
494 ------------------------------------------------------------------------
495 PROCEDURE fetch_attr_values_tlp
496 (
497 x_attr_values_tlp_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
498 x_attr_values_tlp IN OUT NOCOPY PO_PDOI_TYPES.attr_values_tlp_rec_type
499 ) IS
500
501 d_api_name CONSTANT VARCHAR2(30) := 'fetch_attr_values_tlp';
502 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
503 d_position NUMBER;
504
505 BEGIN
506 d_position := 0;
507
508 IF (PO_LOG.d_proc) THEN
509 PO_LOG.proc_begin(d_module);
510 END IF;
511
512 FETCH x_attr_values_tlp_csr BULK COLLECT INTO
513 x_attr_values_tlp.intf_attr_values_tlp_id_tbl,
514 x_attr_values_tlp.language_tbl,
515 x_attr_values_tlp.org_id_tbl,
516 x_attr_values_tlp.ln_item_long_desc_tbl, -- Bug7722053
517
518 -- attributes read from line
519 x_attr_values_tlp.ln_po_line_id_tbl,
520 x_attr_values_tlp.ln_ip_category_id_tbl,
521 x_attr_values_tlp.ln_item_id_tbl,
522 x_attr_values_tlp.ln_item_desc_tbl,
523
524 -- attributes read from header
525 x_attr_values_tlp.draft_id_tbl,
526
527 -- attr values tlp id
528 x_attr_values_tlp.attribute_values_tlp_id_tbl,
529
530 -- initial value for error_flag
531 x_attr_values_tlp.error_flag_tbl
532 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
533
534 IF (PO_LOG.d_proc) THEN
535 PO_LOG.proc_end (d_module);
536 END IF;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 PO_MESSAGE_S.add_exc_msg
541 (
542 p_pkg_name => d_pkg_name,
543 p_procedure_name => d_api_name || '.' || d_position
544 );
545 RAISE;
546 END fetch_attr_values_tlp;
547
548 -------------------------------------------------------------------------
549 --Start of Comments
550 --Name: check_attr_tlp_actions
551 --Pre-reqs: None
552 --Modifies:
553 --Locks:
554 -- None
555 --Function:
556 -- check whether the action on each attr values tlp row is CREATE/UPDATE;
557 -- If multiple rows are pointing to same po line and language, these rows
558 -- will be processed in separate groups.
559 --Parameters:
560 --IN:
561 --IN OUT:
562 -- x_processing_row_tbl
563 -- the procedure will only process rows which have non-empty
564 -- values in this pl/sql table
565 -- x_attr_values_tlp
566 -- record containing all attr values tlp info within the batch
567 --OUT:
568 -- x_create_row_tbl
569 -- index of rows to be created in current group
570 -- x_update_row_tbl
571 -- index of rows to be updated in cuurent group
572 -- x_sync_attr_tlp_id_tbl
573 -- list of attr_value_tlp_ids of rows that need to be read from txn table
574 -- into draft table
575 -- x_sync_draft_id_tbl
576 -- corresponding draft_ids of rows that will be read from txn table
577 -- into draft table
578 --Returns:
579 --Notes:
580 --Testing:
581 --End of Comments
582 ------------------------------------------------------------------------
583 PROCEDURE check_attr_tlp_actions
584 (
585 x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
586 x_attr_values_tlp IN OUT NOCOPY PO_PDOI_TYPES.attr_values_tlp_rec_type,
587 x_merge_row_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
588 x_sync_attr_tlp_id_tbl OUT NOCOPY PO_TBL_NUMBER,
589 x_sync_draft_id_tbl OUT NOCOPY PO_TBL_NUMBER
590 ) IS
591
592 d_api_name CONSTANT VARCHAR2(30) := 'check_attr_tlp_actions';
593 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
594 d_position NUMBER;
595
596 -- ket value used to identify records in po_session_gt table
597 l_key PO_SESSION_GT.key%TYPE;
598
599 -- variables to save result read from po_session_gt
600 l_index_tbl PO_TBL_NUMBER;
601 l_result_tbl PO_TBL_NUMBER;
602 l_source_tbl PO_TBL_VARCHAR5; -- values can be 'draft' or 'txn'
603
604 l_index NUMBER;
605 l_counter NUMBER;
606
607 l_po_line_id NUMBER;
608 l_lang VARCHAR2(4);
609
610 -- hash table to track whether rows with same po_line_id and language exist within batch
611 TYPE attr_tlp_ref_type IS TABLE OF DBMS_SQL.NUMBER_TABLE INDEX BY VARCHAR2(4);
612 l_attr_tlp_ref_tbl attr_tlp_ref_type;
613 BEGIN
614 d_position := 0;
615
616 IF (PO_LOG.d_proc) THEN
617 PO_LOG.proc_begin(d_module, 'x_processing_row_tbl.COUNT',
618 x_processing_row_tbl.COUNT);
619 l_index := x_processing_row_tbl.FIRST;
620 WHILE (l_index IS NOT NULL)
621 LOOP
622 PO_LOG.proc_begin(d_module, 'to be processed row num', l_index);
623 l_index := x_processing_row_tbl.NEXT(l_index);
624 END LOOP;
625 END IF;
626
627 x_sync_attr_tlp_id_tbl := PO_TBL_NUMBER();
628 x_sync_draft_id_tbl := PO_TBL_NUMBER();
629
630 l_key := PO_CORE_S.get_session_gt_nextval;
631
632 -- first check whether record exists in draft table
633 FORALL i IN INDICES OF x_processing_row_tbl
634 INSERT INTO po_session_gt(key, num1, num2, char1)
635 SELECT l_key,
636 x_processing_row_tbl(i),
637 attribute_values_tlp_id,
638 'DRAFT'
639 FROM po_attribute_values_tlp_draft
640 WHERE draft_id = x_attr_values_tlp.draft_id_tbl(i)
641 AND po_line_id = x_attr_values_tlp.ln_po_line_id_tbl(i)
642 AND language = x_attr_values_tlp.language_tbl(i)
643 AND org_id = x_attr_values_tlp.org_id_tbl(i);
644
645 d_position := 10;
646
647 -- second check whether record exists in txn table
648 -- it needs only to be done once since it won't change within batch processing
649 IF (x_processing_row_tbl.COUNT = x_attr_values_tlp.rec_count) THEN
650
651 d_position := 20;
652
653 FORALL i IN INDICES OF x_processing_row_tbl
654 INSERT INTO po_session_gt(key, num1, num2, char1)
655 SELECT l_key,
656 x_processing_row_tbl(i),
657 attribute_values_tlp_id,
658 'TXN'
659 FROM po_attribute_values_tlp
660 WHERE po_line_id = x_attr_values_tlp.ln_po_line_id_tbl(i)
661 AND language = x_attr_values_tlp.language_tbl(i)
662 AND org_id = x_attr_values_tlp.org_id_tbl(i);
663 END IF;
664
665 -- retrieve result from po_session_gt table
666 DELETE FROM po_session_gt
667 WHERE key = l_key
668 RETURNING num1, num2, char1 BULK COLLECT INTO
669 l_index_tbl, l_result_tbl, l_source_tbl;
670
671 -- set attr_values_id in x_attr_values
672 FOR i IN 1..l_index_tbl.COUNT
673 LOOP
674 l_index := l_index_tbl(i);
675
676 IF (PO_LOG.d_stmt) THEN
677 PO_LOG.stmt(d_module, d_position, 'index', l_index);
678 PO_LOG.stmt(d_module, d_position, 'current source exist?',
679 x_attr_values_tlp.source_tbl.EXISTS(l_index));
680 IF (x_attr_values_tlp.source_tbl.EXISTS(l_index)) THEN
681 PO_LOG.stmt(d_module, d_position, 'current source',
682 x_attr_values_tlp.source_tbl(l_index));
683 END IF;
684 PO_LOG.stmt(d_module, d_position, 'attr values id',
685 l_result_tbl(i));
686 PO_LOG.stmt(d_module, d_position, ' new source',
687 l_source_tbl(i));
688 END IF;
689
690 -- draft record will override txn record
691 IF ( NOT x_attr_values_tlp.source_tbl.EXISTS(l_index) OR
692 x_attr_values_tlp.source_tbl(l_index) <> 'DRAFT') THEN
693 x_attr_values_tlp.attribute_values_tlp_id_tbl(l_index) := l_result_tbl(i);
694 x_attr_values_tlp.source_tbl(l_index) := l_source_tbl(i);
695 END IF;
696 END LOOP;
697
698 d_position := 30;
699
700 -- next, set actions on each record, if records with same po_line_id
701 -- and language exist, process them in separate groups
702 l_index := 0;
703 l_counter := x_processing_row_tbl.FIRST;
704 WHILE (l_counter IS NOT NULL)
705 LOOP
706 l_po_line_id := x_attr_values_tlp.ln_po_line_id_tbl(l_counter);
707 l_lang := x_attr_values_tlp.language_tbl(l_counter);
708
709 IF (PO_LOG.d_stmt) THEN
710 PO_LOG.stmt(d_module, d_position, 'l_counter', l_counter);
711 PO_LOG.stmt(d_module, d_position, 'l_po_line_id', l_po_line_id);
712 PO_LOG.stmt(d_module, d_position, 'l_lang',l_lang);
713 END IF;
714
715 -- check whether there is row existing in hashtable
716 IF (NOT l_attr_tlp_ref_tbl.EXISTS(l_lang) OR
717 NOT l_attr_tlp_ref_tbl(l_lang).EXISTS(l_po_line_id)) THEN
718
719 -- IF (l_attr_tlp_ref_tbl(l_lang)(l_po_line_id) IS NULL) THEN
720 -- register it in the hashtbale
721 l_attr_tlp_ref_tbl(l_lang)(l_po_line_id) := l_counter;
722
723 IF (PO_LOG.d_stmt) THEN
724 PO_LOG.stmt(d_module, d_position, 'row is handled in current loop');
725 PO_LOG.stmt(d_module, d_position, 'attr values tlp id',
726 x_attr_values_tlp.attribute_values_tlp_id_tbl(l_counter));
727 END IF;
728
729 -- set actions
730 IF (x_attr_values_tlp.attribute_values_tlp_id_tbl(l_counter) IS NOT NULL) THEN
731 -- row existing in draft or txn tables
732 x_merge_row_tbl(l_counter) := l_counter;
733
734 -- for UPDATE action, track the rows that need to be synced from
735 -- txn table
736 IF (x_attr_values_tlp.source_tbl(l_counter) = 'TXN') THEN
737 l_index := l_index + 1;
738 x_sync_attr_tlp_id_tbl.EXTEND;
739 x_sync_draft_id_tbl.EXTEND;
740 x_sync_attr_tlp_id_tbl(l_index) := x_attr_values_tlp.attribute_values_tlp_id_tbl(l_counter);
741 x_sync_draft_id_tbl(l_index) := x_attr_values_tlp.draft_id_tbl(l_counter);
742 END IF;
743 ELSE
744 -- it is a new row
745 x_merge_row_tbl(l_counter) := l_counter;
746 END IF;
747
748 -- mark rows as processed
749 x_processing_row_tbl.DELETE(l_counter);
750 END IF; -- IF (l_attr_tlp_ref_tbl(l_lang)(l_po_line_id) IS NULL)
751
752 l_counter := x_processing_row_tbl.NEXT(l_counter);
753 END LOOP;
754
755 --Bug12980629
756 x_attr_values_tlp.source_tbl.DELETE;
757
758 IF (PO_LOG.d_proc) THEN
759 PO_LOG.proc_end (d_module);
760 END IF;
761
762 EXCEPTION
763 WHEN OTHERS THEN
764 PO_MESSAGE_S.add_exc_msg
765 (
766 p_pkg_name => d_pkg_name,
767 p_procedure_name => d_api_name || '.' || d_position
768 );
769 RAISE;
770 END check_attr_tlp_actions;
771
772 -------------------------------------------------------------------------
773 --Start of Comments
774 --Name: add_default_attrs
775 --Pre-reqs: None
776 --Modifies:
777 --Locks:
778 -- None
779 --Function:
780 -- add default attr_values and attr_values_tlp rows if not provided;
781 -- the procedure is only called when line is created
782 --Parameters:
783 --IN:
784 --IN OUT:
785 --OUT: None
786 --Returns:
787 --Notes:
788 --Testing:
789 --End of Comments
790 ------------------------------------------------------------------------
791 PROCEDURE add_default_attrs
792 IS
793
794 d_api_name CONSTANT VARCHAR2(30) := 'add_default_attrs';
795 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
796 d_position NUMBER;
797
798 l_draft_id_tbl PO_TBL_NUMBER;
799 l_po_line_id_tbl PO_TBL_NUMBER;
800 l_item_id_tbl PO_TBL_NUMBER;
801 l_ip_category_id_tbl PO_TBL_NUMBER;
802 l_item_desc_tbl PO_TBL_VARCHAR2000;
803 l_created_lang_tbl PO_TBL_VARCHAR5;
804
805 -- Bug7039409: Declared new variables
806 l_master_org_id PO_ATTRIBUTE_VALUES.org_id%TYPE;
807 l_inv_org_id PO_ATTRIBUTE_VALUES.org_id%TYPE;
808 l_item_id_tbl2 PO_TBL_NUMBER; -- Different from l_item_id_tbl
809 l_lead_time_tbl PO_TBL_NUMBER;
810 l_mfg_part_num_tbl PO_TBL_VARCHAR2000;
811 l_mfg_name_tbl PO_TBL_VARCHAR2000;
812 BEGIN
813 d_position := 0;
814
815 IF (PO_LOG.d_proc) THEN
816 PO_LOG.proc_begin(d_module);
817 END IF;
818
819 -- Bug7039409: Get master_org_id and inv_org_id
820 -- Use master org to get mfg_part_num, manufacturer_name and long_description
821 -- as these are Master level attributes.
822 -- Use inventory org to get full_lead_time as this is Org level attribute.
823 SELECT mtl.master_organization_id,
824 fsp.inventory_organization_id
825 INTO l_master_org_id,
826 l_inv_org_id
827 FROM mtl_parameters mtl,
828 financials_system_parameters fsp
829 WHERE fsp.inventory_organization_id = mtl.organization_id;
830
831 IF (PO_LOG.d_stmt) THEN
832 PO_LOG.stmt(d_module, d_position, 'l_master_org_id', l_master_org_id);
833 PO_LOG.stmt(d_module, d_position, 'l_inv_org_id', l_inv_org_id);
834 END IF;
835
836 -- get lines for which default attr and attr_tlp rows need to be created
837 SELECT draft_lines.draft_id,
838 draft_lines.po_line_id,
839 draft_lines.item_id,
840 draft_lines.ip_category_id,
841 draft_lines.item_description,
842 NVL(draft_headers.created_language, txn_headers.created_language),
843 msi.full_lead_time -- Bug7039409: Get the lead time also
844 BULK COLLECT INTO
845 l_draft_id_tbl,
846 l_po_line_id_tbl,
847 l_item_id_tbl,
848 l_ip_category_id_tbl,
849 l_item_desc_tbl,
850 l_created_lang_tbl,
851 l_lead_time_tbl -- Bug7039409: Get lead time into l_lead_time_tbl
852 FROM po_lines_interface intf_lines,
853 po_headers_interface intf_headers,
854 po_lines_draft_all draft_lines,
855 po_headers_draft_all draft_headers,
856 po_headers_all txn_headers,
857 mtl_system_items_b msi -- Bug7039409: Added to get lead time
858 WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
859 AND intf_lines.processing_id = PO_PDOI_PARAMS.g_processing_id
860 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
861 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
862 AND intf_lines.action = PO_PDOI_CONSTANTS.g_ACTION_ADD
863 AND intf_lines.po_line_id = draft_lines.po_line_id
864 AND intf_headers.draft_id = draft_lines.draft_id
865 AND draft_lines.po_header_id = draft_headers.po_header_id(+)
866 AND draft_lines.draft_id = draft_headers.draft_id(+)
867 AND draft_lines.po_header_id = txn_headers.po_header_id(+)
868 -- Added for Bug 6503535 -- Start --
869 -- Exclude the entries for shipments and price-breaks
870 AND intf_lines.shipment_num IS NULL
871 AND intf_lines.shipment_type IS NULL
872 -- Added for Bug 6503535 -- End --
873 AND msi.inventory_item_id (+)= draft_lines.item_id -- Bug7039409: Join msi
874 AND msi.organization_id (+)= l_inv_org_id -- Bug7039409: Join msi
875 AND NOT EXISTS
876 (SELECT 1
877 FROM po_attribute_values_draft
878 WHERE po_line_id = draft_lines.po_line_id
879 AND draft_id = draft_lines.draft_id);
880
881 IF (PO_LOG.d_stmt) THEN
882 PO_LOG.stmt(d_module, d_position, 'l_draft_id_tbl', l_draft_id_tbl);
883 PO_LOG.stmt(d_module, d_position, 'l_po_line_id_tbl', l_po_line_id_tbl);
884 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl', l_item_id_tbl);
885 PO_LOG.stmt(d_module, d_position, 'l_ip_category_id_tbl',
886 l_ip_category_id_tbl);
887 PO_LOG.stmt(d_module, d_position, 'l_item_desc_tbl', l_item_desc_tbl);
888 PO_LOG.stmt(d_module, d_position, 'l_lead_time_tbl', l_lead_time_tbl);
889 END IF;
890 d_position := 10;
891
892 -- create default attr rows
893 FORALL i IN 1..l_draft_id_tbl.COUNT
894 INSERT INTO po_attribute_values_draft
895 (
896 ATTRIBUTE_VALUES_ID,
897 DRAFT_ID,
898 PO_LINE_ID,
899 REQ_TEMPLATE_NAME,
900 REQ_TEMPLATE_LINE_NUM,
901 IP_CATEGORY_ID,
902 INVENTORY_ITEM_ID,
903 ORG_ID,
904 LEAD_TIME,
905 LAST_UPDATE_LOGIN,
906 LAST_UPDATED_BY,
907 LAST_UPDATE_DATE,
908 CREATED_BY,
909 CREATION_DATE,
910 REQUEST_ID,
911 PROGRAM_APPLICATION_ID,
912 PROGRAM_ID,
913 PROGRAM_UPDATE_DATE
914 )
915 VALUES
916 (
917 PO_ATTRIBUTE_VALUES_S.nextval,
918 l_draft_id_tbl(i),
919 l_po_line_id_tbl(i),
920 '-2', -- REQ_TEMPLATE_NAME
921 -2, --REQ_TEMPLATE_LINE_NUM
922 NVL(l_ip_category_id_tbl(i), -2),
923 NVL(l_item_id_tbl(i), -2),
924 PO_PDOI_PARAMS.g_request.org_id,
925 l_lead_time_tbl(i), -- Bug7039409: LEAD_TIME
926 FND_GLOBAL.login_id,
927 FND_GLOBAL.user_id,
928 sysdate,
929 FND_GLOBAL.user_id,
930 sysdate,
931 FND_GLOBAL.conc_request_id,
932 FND_GLOBAL.prog_appl_id,
933 FND_GLOBAL.conc_program_id,
934 sysdate
935 );
936
937 d_position := 20;
938
939 -- create default attr_tlp rows in document created languages
940 FORALL i IN 1..l_draft_id_tbl.COUNT
941 INSERT INTO po_attribute_values_tlp_draft
942 (
943 ATTRIBUTE_VALUES_TLP_ID,
944 DRAFT_ID,
945 PO_LINE_ID,
946 REQ_TEMPLATE_NAME,
947 REQ_TEMPLATE_LINE_NUM,
948 IP_CATEGORY_ID,
949 INVENTORY_ITEM_ID,
950 ORG_ID,
951 LANGUAGE,
952 DESCRIPTION,
953 LAST_UPDATE_LOGIN,
954 LAST_UPDATED_BY,
955 LAST_UPDATE_DATE,
956 CREATED_BY,
957 CREATION_DATE,
958 REQUEST_ID,
959 PROGRAM_APPLICATION_ID,
960 PROGRAM_ID,
961 PROGRAM_UPDATE_DATE
962 )
963 VALUES
964 (
965 PO_ATTRIBUTE_VALUES_TLP_S.nextval,
966 l_draft_id_tbl(i),
967 l_po_line_id_tbl(i),
968 '-2', -- REQ_TEMPLATE_NAME
969 -2, --REQ_TEMPLATE_LINE_NUM
970 NVL(l_ip_category_id_tbl(i), -2),
971 NVL(l_item_id_tbl(i), -2),
972 PO_PDOI_PARAMS.g_request.org_id,
973 l_created_lang_tbl(i),
974 l_item_desc_tbl(i),
975 FND_GLOBAL.login_id,
976 FND_GLOBAL.user_id,
977 sysdate,
978 FND_GLOBAL.user_id,
979 sysdate,
980 FND_GLOBAL.conc_request_id,
981 FND_GLOBAL.prog_appl_id,
982 FND_GLOBAL.conc_program_id,
983 sysdate
984 );
985
986 d_position := 30;
987
988 -- Bug7039409: get mfg_part_num and mfg_name values where exists
989 SELECT mmpn.inventory_item_id,
990 mmpn.mfg_part_num,
991 mmpn.manufacturer_name
992 BULK COLLECT INTO l_item_id_tbl2,
993 l_mfg_part_num_tbl,
994 l_mfg_name_tbl
995 FROM mtl_mfg_part_numbers_all_v mmpn
996 WHERE row_id IN (SELECT MIN(mmpn2.row_id)
997 FROM mtl_mfg_part_numbers_all_v mmpn2,
998 po_attribute_values_draft pavd
999 WHERE pavd.inventory_item_id = mmpn2.inventory_item_id
1000 AND mmpn2.organization_id = l_master_org_id
1001 AND pavd.request_id = fnd_global.conc_request_id
1002 AND pavd.program_application_id = fnd_global.prog_appl_id
1003 AND pavd.program_id = fnd_global.conc_program_id
1004 GROUP BY pavd.inventory_item_id);
1005
1006 IF (PO_LOG.d_stmt) THEN
1007 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl2', l_item_id_tbl2);
1008 PO_LOG.stmt(d_module, d_position, 'l_mfg_part_num_tbl', l_mfg_part_num_tbl);
1009 PO_LOG.stmt(d_module, d_position, 'l_mfg_name_tbl', l_mfg_name_tbl);
1010 END IF;
1011
1012 -- Bug7039409: update po_attribute_values_draft.manufacturer_part_num
1013 FORALL i IN 1..l_item_id_tbl2.COUNT
1014 UPDATE po_attribute_values_draft
1015 SET manufacturer_part_num = l_mfg_part_num_tbl(i)
1016 WHERE inventory_item_id = l_item_id_tbl2(i)
1017 AND org_id = l_master_org_id
1018 AND request_id = fnd_global.conc_request_id
1019 AND program_application_id = fnd_global.prog_appl_id
1020 AND program_id = fnd_global.conc_program_id;
1021
1022 -- Bug7039409: update po_attribute_values_tlp_draft.manufacturer
1023 FORALL i IN 1..l_item_id_tbl2.COUNT
1024 UPDATE po_attribute_values_tlp_draft
1025 SET manufacturer = l_mfg_name_tbl(i)
1026 WHERE inventory_item_id = l_item_id_tbl2(i)
1027 AND org_id = l_master_org_id
1028 AND request_id = fnd_global.conc_request_id
1029 AND program_application_id = fnd_global.prog_appl_id
1030 AND program_id = fnd_global.conc_program_id;
1031
1032 -- Bug7039409: update po_attribute_values_tlp_draft.long_description
1033 -- Bug7722053: Added the condition to check the long desc value
1034 UPDATE po_attribute_values_tlp_draft pavd_tlp
1035 SET long_description = (SELECT long_description
1036 FROM mtl_system_items_tl msi_tl,
1037 fnd_languages lang
1038 WHERE msi_tl.inventory_item_id = pavd_tlp.inventory_item_id
1039 AND msi_tl.organization_id = l_master_org_id
1040 AND msi_tl.language = NVL(pavd_tlp.language,lang.language_code)
1041 AND lang.installed_flag = 'B')
1042 WHERE pavd_tlp.request_id = fnd_global.conc_request_id
1043 AND pavd_tlp.program_application_id = fnd_global.prog_appl_id
1044 AND pavd_tlp.program_id = fnd_global.conc_program_id
1045 AND pavd_tlp.long_description IS NULL;
1046
1047 IF (PO_LOG.d_proc) THEN
1048 PO_LOG.proc_end (d_module);
1049 END IF;
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 PO_MESSAGE_S.add_exc_msg
1054 (
1055 p_pkg_name => d_pkg_name,
1056 p_procedure_name => d_api_name || '.' || d_position
1057 );
1058 RAISE;
1059 END add_default_attrs;
1060
1061 END PO_PDOI_ATTR_PROCESS_PVT;