[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.12.12010000.2 2008/08/04 08:38:25 rramasam 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 IF (PO_LOG.d_proc) THEN
368 PO_LOG.proc_end (d_module);
369 END IF;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 PO_MESSAGE_S.add_exc_msg
374 (
375 p_pkg_name => d_pkg_name,
376 p_procedure_name => d_api_name || '.' || d_position
377 );
378 RAISE;
379 END check_attr_actions;
380
381 -------------------------------------------------------------------------
382 --Start of Comments
383 --Name: open_attr_values_tlp
384 --Pre-reqs: None
385 --Modifies:
386 --Locks:
387 -- None
388 --Function:
389 -- open cursor which reads the attr values tlp record in batch
390 --Parameters:
391 --IN:
392 -- p_max_intf_attr_values_tlp_id
393 -- maximal intf_attr_values_tlp_id processed in previous batches
394 --IN OUT:
395 -- x_attr_values_tlp_csr
396 -- cursor variable which points to next to-be-processed record
397 --OUT: None
398 --Returns:
399 --Notes:
400 --Testing:
401 --End of Comments
402 ------------------------------------------------------------------------
403 PROCEDURE open_attr_values_tlp
404 (
405 p_max_intf_attr_values_tlp_id IN NUMBER,
406 x_attr_values_tlp_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
407 ) IS
408
409 d_api_name CONSTANT VARCHAR2(30) := 'open_attr_values_tlp';
410 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
411 d_position NUMBER;
412
413 BEGIN
414 d_position := 0;
415
416 IF (PO_LOG.d_proc) THEN
417 PO_LOG.proc_begin(d_module);
418 END IF;
419
420 OPEN x_attr_values_tlp_csr FOR
421 SELECT intf_attrs_tlp.interface_attr_values_tlp_id,
422 intf_attrs_tlp.language,
423 intf_attrs_tlp.org_id,
424
425 -- attributes read from line
426 draft_lines.po_line_id,
427 draft_lines.ip_category_id,
428 draft_lines.item_id,
429 draft_lines.item_description,
430
431 -- attributes read from header
432 intf_headers.draft_id,
433
434 -- attr values tlp id
435 NULL,
436
437 -- initial value for error_flag
438 FND_API.g_FALSE
439 FROM po_attr_values_tlp_interface intf_attrs_tlp,
440 po_lines_interface intf_lines,
441 po_headers_interface intf_headers,
442 po_lines_draft_all draft_lines
443 WHERE intf_attrs_tlp.interface_line_id = intf_lines.interface_line_id
444 AND intf_lines.interface_header_id = intf_headers.interface_header_id
445 AND intf_headers.draft_id = draft_lines.draft_id
446 AND intf_lines.po_line_id = draft_lines.po_line_id
447 AND intf_attrs_tlp.processing_id = PO_PDOI_PARAMS.g_processing_id
448 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
449 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
450 AND intf_attrs_tlp.interface_attr_values_tlp_id > p_max_intf_attr_values_tlp_id
451 AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
452 <> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
453 ORDER BY intf_attrs_tlp.interface_attr_values_tlp_id;
454
455 IF (PO_LOG.d_proc) THEN
456 PO_LOG.proc_end (d_module);
457 END IF;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 PO_MESSAGE_S.add_exc_msg
462 (
463 p_pkg_name => d_pkg_name,
464 p_procedure_name => d_api_name || '.' || d_position
465 );
466 RAISE;
467 END open_attr_values_tlp;
468
469 -------------------------------------------------------------------------
470 --Start of Comments
471 --Name: fetch_attr_values_tlp
472 --Pre-reqs: None
473 --Modifies:
474 --Locks:
475 -- None
476 --Function:
477 -- fetch attr values tlp records based on batch size
478 --Parameters:
479 --IN:
480 --IN OUT:
481 -- x_attr_values_tlp_csr
482 -- cursor variable which points to next to-be-processed record
483 -- x_attr_values_tlp
484 -- record containing all attr values tlp info within the batch
485 --OUT: None
486 --Returns:
487 --Notes:
488 --Testing:
489 --End of Comments
490 ------------------------------------------------------------------------
491 PROCEDURE fetch_attr_values_tlp
492 (
493 x_attr_values_tlp_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
494 x_attr_values_tlp IN OUT NOCOPY PO_PDOI_TYPES.attr_values_tlp_rec_type
495 ) IS
496
497 d_api_name CONSTANT VARCHAR2(30) := 'fetch_attr_values_tlp';
498 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
499 d_position NUMBER;
500
501 BEGIN
502 d_position := 0;
503
504 IF (PO_LOG.d_proc) THEN
505 PO_LOG.proc_begin(d_module);
506 END IF;
507
508 FETCH x_attr_values_tlp_csr BULK COLLECT INTO
509 x_attr_values_tlp.intf_attr_values_tlp_id_tbl,
510 x_attr_values_tlp.language_tbl,
511 x_attr_values_tlp.org_id_tbl,
512
513 -- attributes read from line
514 x_attr_values_tlp.ln_po_line_id_tbl,
515 x_attr_values_tlp.ln_ip_category_id_tbl,
516 x_attr_values_tlp.ln_item_id_tbl,
517 x_attr_values_tlp.ln_item_desc_tbl,
518
519 -- attributes read from header
520 x_attr_values_tlp.draft_id_tbl,
521
522 -- attr values tlp id
523 x_attr_values_tlp.attribute_values_tlp_id_tbl,
524
525 -- initial value for error_flag
526 x_attr_values_tlp.error_flag_tbl
527 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
528
529 IF (PO_LOG.d_proc) THEN
530 PO_LOG.proc_end (d_module);
531 END IF;
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 PO_MESSAGE_S.add_exc_msg
536 (
537 p_pkg_name => d_pkg_name,
538 p_procedure_name => d_api_name || '.' || d_position
539 );
540 RAISE;
541 END fetch_attr_values_tlp;
542
543 -------------------------------------------------------------------------
544 --Start of Comments
545 --Name: check_attr_tlp_actions
546 --Pre-reqs: None
547 --Modifies:
548 --Locks:
549 -- None
550 --Function:
551 -- check whether the action on each attr values tlp row is CREATE/UPDATE;
552 -- If multiple rows are pointing to same po line and language, these rows
553 -- will be processed in separate groups.
554 --Parameters:
555 --IN:
556 --IN OUT:
557 -- x_processing_row_tbl
558 -- the procedure will only process rows which have non-empty
559 -- values in this pl/sql table
560 -- x_attr_values_tlp
561 -- record containing all attr values tlp info within the batch
562 --OUT:
563 -- x_create_row_tbl
564 -- index of rows to be created in current group
565 -- x_update_row_tbl
566 -- index of rows to be updated in cuurent group
567 -- x_sync_attr_tlp_id_tbl
568 -- list of attr_value_tlp_ids of rows that need to be read from txn table
569 -- into draft table
570 -- x_sync_draft_id_tbl
571 -- corresponding draft_ids of rows that will be read from txn table
572 -- into draft table
573 --Returns:
574 --Notes:
575 --Testing:
576 --End of Comments
577 ------------------------------------------------------------------------
578 PROCEDURE check_attr_tlp_actions
579 (
580 x_processing_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
581 x_attr_values_tlp IN OUT NOCOPY PO_PDOI_TYPES.attr_values_tlp_rec_type,
582 x_merge_row_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
583 x_sync_attr_tlp_id_tbl OUT NOCOPY PO_TBL_NUMBER,
584 x_sync_draft_id_tbl OUT NOCOPY PO_TBL_NUMBER
585 ) IS
586
587 d_api_name CONSTANT VARCHAR2(30) := 'check_attr_tlp_actions';
588 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
589 d_position NUMBER;
590
591 -- ket value used to identify records in po_session_gt table
592 l_key PO_SESSION_GT.key%TYPE;
593
594 -- variables to save result read from po_session_gt
595 l_index_tbl PO_TBL_NUMBER;
596 l_result_tbl PO_TBL_NUMBER;
597 l_source_tbl PO_TBL_VARCHAR5; -- values can be 'draft' or 'txn'
598
599 l_index NUMBER;
600 l_counter NUMBER;
601
602 l_po_line_id NUMBER;
603 l_lang VARCHAR2(4);
604
605 -- hash table to track whether rows with same po_line_id and language exist within batch
606 TYPE attr_tlp_ref_type IS TABLE OF DBMS_SQL.NUMBER_TABLE INDEX BY VARCHAR2(4);
607 l_attr_tlp_ref_tbl attr_tlp_ref_type;
608 BEGIN
609 d_position := 0;
610
611 IF (PO_LOG.d_proc) THEN
612 PO_LOG.proc_begin(d_module, 'x_processing_row_tbl.COUNT',
613 x_processing_row_tbl.COUNT);
614 l_index := x_processing_row_tbl.FIRST;
615 WHILE (l_index IS NOT NULL)
616 LOOP
617 PO_LOG.proc_begin(d_module, 'to be processed row num', l_index);
618 l_index := x_processing_row_tbl.NEXT(l_index);
619 END LOOP;
620 END IF;
621
622 x_sync_attr_tlp_id_tbl := PO_TBL_NUMBER();
623 x_sync_draft_id_tbl := PO_TBL_NUMBER();
624
625 l_key := PO_CORE_S.get_session_gt_nextval;
626
627 -- first check whether record exists in draft table
628 FORALL i IN INDICES OF x_processing_row_tbl
629 INSERT INTO po_session_gt(key, num1, num2, char1)
630 SELECT l_key,
631 x_processing_row_tbl(i),
632 attribute_values_tlp_id,
633 'DRAFT'
634 FROM po_attribute_values_tlp_draft
635 WHERE draft_id = x_attr_values_tlp.draft_id_tbl(i)
636 AND po_line_id = x_attr_values_tlp.ln_po_line_id_tbl(i)
637 AND language = x_attr_values_tlp.language_tbl(i)
638 AND org_id = x_attr_values_tlp.org_id_tbl(i);
639
640 d_position := 10;
641
642 -- second check whether record exists in txn table
643 -- it needs only to be done once since it won't change within batch processing
644 IF (x_processing_row_tbl.COUNT = x_attr_values_tlp.rec_count) THEN
645
646 d_position := 20;
647
648 FORALL i IN INDICES OF x_processing_row_tbl
649 INSERT INTO po_session_gt(key, num1, num2, char1)
650 SELECT l_key,
651 x_processing_row_tbl(i),
652 attribute_values_tlp_id,
653 'TXN'
654 FROM po_attribute_values_tlp
655 WHERE po_line_id = x_attr_values_tlp.ln_po_line_id_tbl(i)
656 AND language = x_attr_values_tlp.language_tbl(i)
657 AND org_id = x_attr_values_tlp.org_id_tbl(i);
658 END IF;
659
660 -- retrieve result from po_session_gt table
661 DELETE FROM po_session_gt
662 WHERE key = l_key
663 RETURNING num1, num2, char1 BULK COLLECT INTO
664 l_index_tbl, l_result_tbl, l_source_tbl;
665
666 -- set attr_values_id in x_attr_values
667 FOR i IN 1..l_index_tbl.COUNT
668 LOOP
669 l_index := l_index_tbl(i);
670
671 IF (PO_LOG.d_stmt) THEN
672 PO_LOG.stmt(d_module, d_position, 'index', l_index);
673 PO_LOG.stmt(d_module, d_position, 'current source exist?',
674 x_attr_values_tlp.source_tbl.EXISTS(l_index));
675 IF (x_attr_values_tlp.source_tbl.EXISTS(l_index)) THEN
676 PO_LOG.stmt(d_module, d_position, 'current source',
677 x_attr_values_tlp.source_tbl(l_index));
678 END IF;
679 PO_LOG.stmt(d_module, d_position, 'attr values id',
680 l_result_tbl(i));
681 PO_LOG.stmt(d_module, d_position, ' new source',
682 l_source_tbl(i));
683 END IF;
684
685 -- draft record will override txn record
686 IF ( NOT x_attr_values_tlp.source_tbl.EXISTS(l_index) OR
687 x_attr_values_tlp.source_tbl(l_index) <> 'DRAFT') THEN
688 x_attr_values_tlp.attribute_values_tlp_id_tbl(l_index) := l_result_tbl(i);
689 x_attr_values_tlp.source_tbl(l_index) := l_source_tbl(i);
690 END IF;
691 END LOOP;
692
693 d_position := 30;
694
695 -- next, set actions on each record, if records with same po_line_id
696 -- and language exist, process them in separate groups
697 l_index := 0;
698 l_counter := x_processing_row_tbl.FIRST;
699 WHILE (l_counter IS NOT NULL)
700 LOOP
701 l_po_line_id := x_attr_values_tlp.ln_po_line_id_tbl(l_counter);
702 l_lang := x_attr_values_tlp.language_tbl(l_counter);
703
704 IF (PO_LOG.d_stmt) THEN
705 PO_LOG.stmt(d_module, d_position, 'l_counter', l_counter);
706 PO_LOG.stmt(d_module, d_position, 'l_po_line_id', l_po_line_id);
707 PO_LOG.stmt(d_module, d_position, 'l_lang',l_lang);
708 END IF;
709
710 -- check whether there is row existing in hashtable
711 IF (NOT l_attr_tlp_ref_tbl.EXISTS(l_lang) OR
712 NOT l_attr_tlp_ref_tbl(l_lang).EXISTS(l_po_line_id)) THEN
713
714 -- IF (l_attr_tlp_ref_tbl(l_lang)(l_po_line_id) IS NULL) THEN
715 -- register it in the hashtbale
716 l_attr_tlp_ref_tbl(l_lang)(l_po_line_id) := l_counter;
717
718 IF (PO_LOG.d_stmt) THEN
719 PO_LOG.stmt(d_module, d_position, 'row is handled in current loop');
720 PO_LOG.stmt(d_module, d_position, 'attr values tlp id',
721 x_attr_values_tlp.attribute_values_tlp_id_tbl(l_counter));
722 END IF;
723
724 -- set actions
725 IF (x_attr_values_tlp.attribute_values_tlp_id_tbl(l_counter) IS NOT NULL) THEN
726 -- row existing in draft or txn tables
727 x_merge_row_tbl(l_counter) := l_counter;
728
729 -- for UPDATE action, track the rows that need to be synced from
730 -- txn table
731 IF (x_attr_values_tlp.source_tbl(l_counter) = 'TXN') THEN
732 l_index := l_index + 1;
733 x_sync_attr_tlp_id_tbl.EXTEND;
734 x_sync_draft_id_tbl.EXTEND;
735 x_sync_attr_tlp_id_tbl(l_index) := x_attr_values_tlp.attribute_values_tlp_id_tbl(l_counter);
736 x_sync_draft_id_tbl(l_index) := x_attr_values_tlp.draft_id_tbl(l_counter);
737 END IF;
738 ELSE
739 -- it is a new row
740 x_merge_row_tbl(l_counter) := l_counter;
741 END IF;
742
743 -- mark rows as processed
744 x_processing_row_tbl.DELETE(l_counter);
745 END IF; -- IF (l_attr_tlp_ref_tbl(l_lang)(l_po_line_id) IS NULL)
746
747 l_counter := x_processing_row_tbl.NEXT(l_counter);
748 END LOOP;
749
750 IF (PO_LOG.d_proc) THEN
751 PO_LOG.proc_end (d_module);
752 END IF;
753
754 EXCEPTION
755 WHEN OTHERS THEN
756 PO_MESSAGE_S.add_exc_msg
757 (
758 p_pkg_name => d_pkg_name,
759 p_procedure_name => d_api_name || '.' || d_position
760 );
761 RAISE;
762 END check_attr_tlp_actions;
763
764 -------------------------------------------------------------------------
765 --Start of Comments
766 --Name: add_default_attrs
767 --Pre-reqs: None
768 --Modifies:
769 --Locks:
770 -- None
771 --Function:
772 -- add default attr_values and attr_values_tlp rows if not provided;
773 -- the procedure is only called when line is created
774 --Parameters:
775 --IN:
776 --IN OUT:
777 --OUT: None
778 --Returns:
779 --Notes:
780 --Testing:
781 --End of Comments
782 ------------------------------------------------------------------------
783 PROCEDURE add_default_attrs
784 IS
785
786 d_api_name CONSTANT VARCHAR2(30) := 'add_default_attrs';
787 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
788 d_position NUMBER;
789
790 l_draft_id_tbl PO_TBL_NUMBER;
791 l_po_line_id_tbl PO_TBL_NUMBER;
792 l_item_id_tbl PO_TBL_NUMBER;
793 l_ip_category_id_tbl PO_TBL_NUMBER;
794 l_item_desc_tbl PO_TBL_VARCHAR2000;
795 l_created_lang_tbl PO_TBL_VARCHAR5;
796
797 -- Bug7039409: Declared new variables
798 l_master_org_id PO_ATTRIBUTE_VALUES.org_id%TYPE;
799 l_inv_org_id PO_ATTRIBUTE_VALUES.org_id%TYPE;
800 l_item_id_tbl2 PO_TBL_NUMBER; -- Different from l_item_id_tbl
801 l_lead_time_tbl PO_TBL_NUMBER;
802 l_mfg_part_num_tbl PO_TBL_VARCHAR2000;
803 l_mfg_name_tbl PO_TBL_VARCHAR2000;
804 BEGIN
805 d_position := 0;
806
807 IF (PO_LOG.d_proc) THEN
808 PO_LOG.proc_begin(d_module);
809 END IF;
810
811 -- Bug7039409: Get master_org_id and inv_org_id
812 -- Use master org to get mfg_part_num, manufacturer_name and long_description
813 -- as these are Master level attributes.
814 -- Use inventory org to get full_lead_time as this is Org level attribute.
815 SELECT mtl.master_organization_id,
816 fsp.inventory_organization_id
817 INTO l_master_org_id,
818 l_inv_org_id
819 FROM mtl_parameters mtl,
820 financials_system_parameters fsp
821 WHERE fsp.inventory_organization_id = mtl.organization_id;
822
823 IF (PO_LOG.d_stmt) THEN
824 PO_LOG.stmt(d_module, d_position, 'l_master_org_id', l_master_org_id);
825 PO_LOG.stmt(d_module, d_position, 'l_inv_org_id', l_inv_org_id);
826 END IF;
827
828 -- get lines for which default attr and attr_tlp rows need to be created
829 SELECT draft_lines.draft_id,
830 draft_lines.po_line_id,
831 draft_lines.item_id,
832 draft_lines.ip_category_id,
833 draft_lines.item_description,
834 NVL(draft_headers.created_language, txn_headers.created_language),
835 msi.full_lead_time -- Bug7039409: Get the lead time also
836 BULK COLLECT INTO
837 l_draft_id_tbl,
838 l_po_line_id_tbl,
839 l_item_id_tbl,
840 l_ip_category_id_tbl,
841 l_item_desc_tbl,
842 l_created_lang_tbl,
843 l_lead_time_tbl -- Bug7039409: Get lead time into l_lead_time_tbl
844 FROM po_lines_interface intf_lines,
845 po_headers_interface intf_headers,
846 po_lines_draft_all draft_lines,
847 po_headers_draft_all draft_headers,
848 po_headers_all txn_headers,
849 mtl_system_items_b msi -- Bug7039409: Added to get lead time
850 WHERE intf_lines.interface_header_id = intf_headers.interface_header_id
851 AND intf_lines.processing_id = PO_PDOI_PARAMS.g_processing_id
852 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
853 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
854 AND intf_lines.action = PO_PDOI_CONSTANTS.g_ACTION_ADD
855 AND intf_lines.po_line_id = draft_lines.po_line_id
856 AND intf_headers.draft_id = draft_lines.draft_id
857 AND draft_lines.po_header_id = draft_headers.po_header_id(+)
858 AND draft_lines.draft_id = draft_headers.draft_id(+)
859 AND draft_lines.po_header_id = txn_headers.po_header_id(+)
860 -- Added for Bug 6503535 -- Start --
861 -- Exclude the entries for shipments and price-breaks
862 AND intf_lines.shipment_num IS NULL
863 AND intf_lines.shipment_type IS NULL
864 -- Added for Bug 6503535 -- End --
865 AND msi.inventory_item_id (+)= draft_lines.item_id -- Bug7039409: Join msi
866 AND msi.organization_id (+)= l_inv_org_id -- Bug7039409: Join msi
867 AND NOT EXISTS
868 (SELECT 1
869 FROM po_attribute_values_draft
870 WHERE po_line_id = draft_lines.po_line_id
871 AND draft_id = draft_lines.draft_id);
872
873 IF (PO_LOG.d_stmt) THEN
874 PO_LOG.stmt(d_module, d_position, 'l_draft_id_tbl', l_draft_id_tbl);
875 PO_LOG.stmt(d_module, d_position, 'l_po_line_id_tbl', l_po_line_id_tbl);
876 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl', l_item_id_tbl);
877 PO_LOG.stmt(d_module, d_position, 'l_ip_category_id_tbl',
878 l_ip_category_id_tbl);
879 PO_LOG.stmt(d_module, d_position, 'l_item_desc_tbl', l_item_desc_tbl);
880 PO_LOG.stmt(d_module, d_position, 'l_lead_time_tbl', l_lead_time_tbl);
881 END IF;
882 d_position := 10;
883
884 -- create default attr rows
885 FORALL i IN 1..l_draft_id_tbl.COUNT
886 INSERT INTO po_attribute_values_draft
887 (
888 ATTRIBUTE_VALUES_ID,
889 DRAFT_ID,
890 PO_LINE_ID,
891 REQ_TEMPLATE_NAME,
892 REQ_TEMPLATE_LINE_NUM,
893 IP_CATEGORY_ID,
894 INVENTORY_ITEM_ID,
895 ORG_ID,
896 LEAD_TIME,
897 LAST_UPDATE_LOGIN,
898 LAST_UPDATED_BY,
899 LAST_UPDATE_DATE,
900 CREATED_BY,
901 CREATION_DATE,
902 REQUEST_ID,
903 PROGRAM_APPLICATION_ID,
904 PROGRAM_ID,
905 PROGRAM_UPDATE_DATE
906 )
907 VALUES
908 (
909 PO_ATTRIBUTE_VALUES_S.nextval,
910 l_draft_id_tbl(i),
911 l_po_line_id_tbl(i),
912 '-2', -- REQ_TEMPLATE_NAME
913 -2, --REQ_TEMPLATE_LINE_NUM
914 NVL(l_ip_category_id_tbl(i), -2),
915 NVL(l_item_id_tbl(i), -2),
916 PO_PDOI_PARAMS.g_request.org_id,
917 l_lead_time_tbl(i), -- Bug7039409: LEAD_TIME
918 FND_GLOBAL.login_id,
919 FND_GLOBAL.user_id,
920 sysdate,
921 FND_GLOBAL.user_id,
922 sysdate,
923 FND_GLOBAL.conc_request_id,
924 FND_GLOBAL.prog_appl_id,
925 FND_GLOBAL.conc_program_id,
926 sysdate
927 );
928
929 d_position := 20;
930
931 -- create default attr_tlp rows in document created languages
932 FORALL i IN 1..l_draft_id_tbl.COUNT
933 INSERT INTO po_attribute_values_tlp_draft
934 (
935 ATTRIBUTE_VALUES_TLP_ID,
936 DRAFT_ID,
937 PO_LINE_ID,
938 REQ_TEMPLATE_NAME,
939 REQ_TEMPLATE_LINE_NUM,
940 IP_CATEGORY_ID,
941 INVENTORY_ITEM_ID,
942 ORG_ID,
943 LANGUAGE,
944 DESCRIPTION,
945 LAST_UPDATE_LOGIN,
946 LAST_UPDATED_BY,
947 LAST_UPDATE_DATE,
948 CREATED_BY,
949 CREATION_DATE,
950 REQUEST_ID,
951 PROGRAM_APPLICATION_ID,
952 PROGRAM_ID,
953 PROGRAM_UPDATE_DATE
954 )
955 VALUES
956 (
957 PO_ATTRIBUTE_VALUES_TLP_S.nextval,
958 l_draft_id_tbl(i),
959 l_po_line_id_tbl(i),
960 '-2', -- REQ_TEMPLATE_NAME
961 -2, --REQ_TEMPLATE_LINE_NUM
962 NVL(l_ip_category_id_tbl(i), -2),
963 NVL(l_item_id_tbl(i), -2),
964 PO_PDOI_PARAMS.g_request.org_id,
965 l_created_lang_tbl(i),
966 l_item_desc_tbl(i),
967 FND_GLOBAL.login_id,
968 FND_GLOBAL.user_id,
969 sysdate,
970 FND_GLOBAL.user_id,
971 sysdate,
972 FND_GLOBAL.conc_request_id,
973 FND_GLOBAL.prog_appl_id,
974 FND_GLOBAL.conc_program_id,
975 sysdate
976 );
977
978 d_position := 30;
979
980 -- Bug7039409: get mfg_part_num and mfg_name values where exists
981 SELECT mmpn.inventory_item_id,
982 mmpn.mfg_part_num,
983 mmpn.manufacturer_name
984 BULK COLLECT INTO l_item_id_tbl2,
985 l_mfg_part_num_tbl,
986 l_mfg_name_tbl
987 FROM mtl_mfg_part_numbers_all_v mmpn
988 WHERE row_id IN (SELECT MIN(mmpn2.row_id)
989 FROM mtl_mfg_part_numbers_all_v mmpn2,
990 po_attribute_values_draft pavd
991 WHERE pavd.inventory_item_id = mmpn2.inventory_item_id
992 AND mmpn2.organization_id = l_master_org_id
993 AND pavd.request_id = fnd_global.conc_request_id
994 AND pavd.program_application_id = fnd_global.prog_appl_id
995 AND pavd.program_id = fnd_global.conc_program_id
996 GROUP BY pavd.inventory_item_id);
997
998 IF (PO_LOG.d_stmt) THEN
999 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl2', l_item_id_tbl2);
1000 PO_LOG.stmt(d_module, d_position, 'l_mfg_part_num_tbl', l_mfg_part_num_tbl);
1001 PO_LOG.stmt(d_module, d_position, 'l_mfg_name_tbl', l_mfg_name_tbl);
1002 END IF;
1003
1004 -- Bug7039409: update po_attribute_values_draft.manufacturer_part_num
1005 FORALL i IN 1..l_item_id_tbl2.COUNT
1006 UPDATE po_attribute_values_draft
1007 SET manufacturer_part_num = l_mfg_part_num_tbl(i)
1008 WHERE inventory_item_id = l_item_id_tbl2(i)
1009 AND org_id = l_master_org_id
1010 AND request_id = fnd_global.conc_request_id
1011 AND program_application_id = fnd_global.prog_appl_id
1012 AND program_id = fnd_global.conc_program_id;
1013
1014 -- Bug7039409: update po_attribute_values_tlp_draft.manufacturer
1015 FORALL i IN 1..l_item_id_tbl2.COUNT
1016 UPDATE po_attribute_values_tlp_draft
1017 SET manufacturer = l_mfg_name_tbl(i)
1018 WHERE inventory_item_id = l_item_id_tbl2(i)
1019 AND org_id = l_master_org_id
1020 AND request_id = fnd_global.conc_request_id
1021 AND program_application_id = fnd_global.prog_appl_id
1022 AND program_id = fnd_global.conc_program_id;
1023
1024 -- Bug7039409: update po_attribute_values_tlp_draft.long_description
1025 UPDATE po_attribute_values_tlp_draft pavd_tlp
1026 SET long_description = (SELECT long_description
1027 FROM mtl_system_items_tl msi_tl,
1028 fnd_languages lang
1029 WHERE msi_tl.inventory_item_id = pavd_tlp.inventory_item_id
1030 AND msi_tl.organization_id = l_master_org_id
1031 AND msi_tl.language = NVL(pavd_tlp.language,lang.language_code)
1032 AND lang.installed_flag = 'B')
1033 WHERE pavd_tlp.request_id = fnd_global.conc_request_id
1034 AND pavd_tlp.program_application_id = fnd_global.prog_appl_id
1035 AND pavd_tlp.program_id = fnd_global.conc_program_id;
1036
1037 IF (PO_LOG.d_proc) THEN
1038 PO_LOG.proc_end (d_module);
1039 END IF;
1040
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 PO_MESSAGE_S.add_exc_msg
1044 (
1045 p_pkg_name => d_pkg_name,
1046 p_procedure_name => d_api_name || '.' || d_position
1047 );
1048 RAISE;
1049 END add_default_attrs;
1050
1051 END PO_PDOI_ATTR_PROCESS_PVT;