[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_ITEM_PROCESS_PVT
Source
1 PACKAGE BODY PO_PDOI_ITEM_PROCESS_PVT AS
2 /* $Header: PO_PDOI_ITEM_PROCESS_PVT.plb 120.13 2011/03/04 11:33:28 sbontala ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_ITEM_PROCESS_PVT');
6
7 --------------------------------------------------------------------------
8 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
9 --------------------------------------------------------------------------
10 PROCEDURE construct_item_records
11 (
12 p_lines IN PO_PDOI_TYPES.lines_rec_type,
13 x_items OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
14 );
15
16 PROCEDURE derive_default_loc_attrs
17 (
18 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
19 );
20
21 PROCEDURE identify_actions
22 (
23 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type,
24 x_unprocessed_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
25 x_proc_row_in_round_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
26 x_create_in_inv_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
27 x_create_in_master_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
28 x_create_in_ship_to_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
29 x_update_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE
30 );
31
32 PROCEDURE insert_master_item
33 (
34 p_org_type IN VARCHAR2,
35 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
36 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
37 );
38
39 PROCEDURE update_master_item
40 (
41 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
42 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
43 );
44 --------------------------------------------------------------------------
45 ---------------------- PUBLIC PROCEDURES ---------------------------------
46 --------------------------------------------------------------------------
47
48 -----------------------------------------------------------------------
49 --Start of Comments
50 --Name: create_items
51 --Function: create or update item in item master for default inv org,
52 -- default master org and each ship_to org.
53 -- The new item id will be set back to x_lines.
54 --Parameters:
55 --IN:
56 --IN OUT:
57 -- x_lines
58 -- record which stores all the line rows within the batch;
59 --OUT:
60 --End of Comments
61 ------------------------------------------------------------------------
62 PROCEDURE create_items
63 (
64 x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
65 ) IS
66
67 d_api_name CONSTANT VARCHAR2(30) := 'create_items';
68 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
69 d_position NUMBER;
70
71 l_items PO_PDOI_TYPES.line_locs_rec_type;
72
73 l_create_in_inv_index_tbl DBMS_SQL.NUMBER_TABLE;
74 l_create_in_master_index_tbl DBMS_SQL.NUMBER_TABLE;
75 l_create_in_ship_to_index_tbl DBMS_SQL.NUMBER_TABLE;
76 l_update_index_tbl DBMS_SQL.NUMBER_TABLE;
77
78 -- identify rows that have not been processed
79 l_unprocessed_row_tbl DBMS_SQL.NUMBER_TABLE;
80 l_index NUMBER;
81
82 -- rows processed in current loop round
83 l_proc_row_in_round_tbl DBMS_SQL.NUMBER_TABLE;
84 BEGIN
85 d_position := 0;
86
87 IF (PO_LOG.d_proc) THEN
88 PO_LOG.proc_begin(d_module);
89 END IF;
90
91 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_ITEM_CREATION);
92
93 -- fetch location attributes and contruct item record on each line location
94 construct_item_records
95 (
96 p_lines => x_lines,
97 x_items => l_items
98 );
99
100 d_position := 10;
101
102 -- derive and default logic for fetched location attributes
103 derive_default_loc_attrs
104 (
105 x_items => l_items
106 );
107
108 d_position := 20;
109
110 -- initialize table containing the row number(index)
111 PO_PDOI_UTL.generate_ordered_num_list
112 (
113 p_size => l_items.rec_count,
114 x_num_list => l_unprocessed_row_tbl
115 );
116
117 -- remove records that has derivation errors on location attrs;
118 -- we won't create/update items for such records
119 FOR i IN 1..l_items.rec_count
120 LOOP
121 IF (l_items.error_flag_tbl(i) = FND_API.g_TRUE) THEN
122 l_unprocessed_row_tbl.DELETE(i);
123
124 IF (PO_LOG.d_stmt) THEN
125 PO_LOG.stmt(d_module, d_position, 'record removed due to ' ||
126 'location derivation error', i);
127 PO_LOG.stmt(d_module, d_position, 'set line record error ' ||
128 'flag to Y', l_items.line_ref_index_tbl(i));
129 END IF;
130
131 -- set corresponding po_line's error_flag to 'Y'
132 x_lines.error_flag_tbl(l_items.line_ref_index_tbl(i)) :=
133 FND_API.g_TRUE;
134
135 END IF;
136 END LOOP;
137
138 -- create/update items
139 -- item with same item number will be processed in different
140 -- loop round;
141 LOOP
142 IF (l_unprocessed_row_tbl.COUNT = 0) THEN
143 IF (PO_LOG.d_stmt) THEN
144 PO_LOG.stmt(d_module, d_position, 'all rows processed');
145 END IF;
146
147 EXIT;
148 END IF;
149
150 -- identify rows for which items need to be created/updated
151 identify_actions
152 (
153 x_items => l_items,
154 x_unprocessed_row_tbl => l_unprocessed_row_tbl,
155 x_proc_row_in_round_tbl => l_proc_row_in_round_tbl,
156 x_create_in_inv_index_tbl => l_create_in_inv_index_tbl,
157 x_create_in_master_index_tbl => l_create_in_master_index_tbl,
158 x_create_in_ship_to_index_tbl => l_create_in_ship_to_index_tbl,
159 x_update_index_tbl => l_update_index_tbl
160 );
161
162 IF (PO_LOG.d_stmt) THEN
163 l_index := l_create_in_inv_index_tbl.FIRST;
164 WHILE (l_index IS NOT NULL)
165 LOOP
166 PO_LOG.stmt(d_module, d_position, 'l_create_in_inv_index_tbl('||l_index||')',
167 l_create_in_inv_index_tbl(l_index));
168 l_index := l_create_in_inv_index_tbl.NEXT(l_index);
169 END LOOP;
170
171 l_index := l_create_in_master_index_tbl.FIRST;
172 WHILE (l_index IS NOT NULL)
173 LOOP
174 PO_LOG.stmt(d_module, d_position, 'l_create_in_master_index_tbl('||l_index||')',
175 l_create_in_master_index_tbl(l_index));
176 l_index := l_create_in_master_index_tbl.NEXT(l_index);
177 END LOOP;
178
179 l_index := l_create_in_ship_to_index_tbl.FIRST;
180 WHILE (l_index IS NOT NULL)
181 LOOP
182 PO_LOG.stmt(d_module, d_position, 'l_create_in_ship_to_index_tbl('||l_index||')',
183 l_create_in_ship_to_index_tbl(l_index));
184 l_index := l_create_in_ship_to_index_tbl.NEXT(l_index);
185 END LOOP;
186
187 l_index := l_update_index_tbl.FIRST;
188 WHILE (l_index IS NOT NULL)
189 LOOP
190 PO_LOG.stmt(d_module, d_position, 'l_update_index_tbl('||l_index||')',
191 l_update_index_tbl(l_index));
192 l_index := l_update_index_tbl.NEXT(l_index);
193 END LOOP;
194 END IF;
195
196 d_position := 30;
197
198 -- insert item in inv, master and ship_to orgs
199 -- new item_id will be set back to l_items
200
201 -- bug5247736
202 -- The order of inv org for which the item is to be created should be
203 -- 1) Master Org
204 -- 2) Inv Org specified in FSP
205 -- 3) Ship To Org
206
207 -- Bug7117320: As part of fix 5247736, value for p_index_tbl was not changed
208 -- Corrected the param value.
209
210 insert_master_item
211 (
212 p_org_type => 'MASTER',
213 p_index_tbl => l_create_in_master_index_tbl,
214 x_items => l_items
215 );
216
217 d_position := 40;
218
219 insert_master_item
220 (
221 p_org_type => 'INV',
222 p_index_tbl => l_create_in_inv_index_tbl,
223 x_items => l_items
224 );
225
226 d_position := 50;
227
228 insert_master_item
229 (
230 p_org_type => 'SHIP_TO',
231 p_index_tbl => l_create_in_ship_to_index_tbl,
232 x_items => l_items
233 );
234
235 d_position := 60;
236
237 -- update items in item master
238 update_master_item
239 (
240 p_index_tbl => l_update_index_tbl,
241 x_items => l_items
242 );
243
244 d_position := 70;
245
246 -- set item_id and error_flag back to x_lines
247 l_index := l_proc_row_in_round_tbl.FIRST;
248 WHILE (l_index IS NOT NULL)
249 LOOP
250 IF (PO_LOG.d_stmt) THEN
251 PO_LOG.stmt(d_module, d_position, 'from item index',
252 l_index);
253 PO_LOG.stmt(d_module, d_position, 'to line index',
254 l_items.line_ref_index_tbl(l_index));
255 PO_LOG.stmt(d_module, d_position, 'new item id',
256 l_items.ln_item_id_tbl(l_index));
257 PO_LOG.stmt(d_module, d_position, 'new error flag',
258 l_items.error_flag_tbl(l_index));
259 END IF;
260
261 x_lines.item_id_tbl(l_items.line_ref_index_tbl(l_index)) :=
262 l_items.ln_item_id_tbl(l_index);
263
264 x_lines.error_flag_tbl(l_items.line_ref_index_tbl(l_index)) :=
265 l_items.error_flag_tbl(l_index);
266
267 l_index := l_proc_row_in_round_tbl.NEXT(l_index);
268 END LOOP;
269
270 d_position := 80;
271
272 -- if there is error occured on item creation, error out the
273 -- other unprocessed rows in same po_line
274 l_index := l_unprocessed_row_tbl.FIRST;
275 WHILE (l_index IS NOT NULL)
276 LOOP
277 IF (x_lines.error_flag_tbl(l_items.line_ref_index_tbl(l_index)) =
278 FND_API.g_TRUE) THEN
279 IF (PO_LOG.d_stmt) THEN
280 PO_LOG.stmt(d_module, d_position, 'reject item on index',
281 l_index);
282 END IF;
283
284 -- no need to do further processing since line has error
285 l_unprocessed_row_tbl.DELETE(l_index);
286 END IF;
287
288 l_index := l_unprocessed_row_tbl.NEXT(l_index);
289 END LOOP;
290 END LOOP;
291
292 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_ITEM_CREATION);
293
294 IF (PO_LOG.d_proc) THEN
295 PO_LOG.proc_end (d_module);
296 END IF;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 PO_MESSAGE_S.add_exc_msg
301 (
302 p_pkg_name => d_pkg_name,
303 p_procedure_name => d_api_name || '.' || d_position
304 );
305 RAISE;
306 END create_items;
307
308 -------------------------------------------------------------------------
309 --------------------- PRIVATE PROCEDURES --------------------------------
310 -------------------------------------------------------------------------
311
312 -----------------------------------------------------------------------
313 --Start of Comments
314 --Name: construct_item_records
315 --Function:
316 -- Read all the locations for the lines within the batch. Then construct
317 -- item records which contain both the line and location info.
318 -- The records will be used later for derive, default and insert/update
319 -- into item master
320 --Parameters:
321 --IN:
322 -- p_lines
323 -- record which stores all the line rows within the batch;
324 --IN OUT:
325 -- x_items
326 -- the item records containing line info for each location
327 --OUT:
328 --End of Comments
329 ------------------------------------------------------------------------
330 PROCEDURE construct_item_records
331 (
332 p_lines IN PO_PDOI_TYPES.lines_rec_type,
333 x_items OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
334 ) IS
335
336 d_api_name CONSTANT VARCHAR2(30) := 'construct_item_records';
337 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
338 d_position NUMBER;
339
340 l_key PO_SESSION_GT.key%TYPE;
341 l_index_tbl DBMS_SQL.NUMBER_TABLE;
342 l_line_index NUMBER;
343 BEGIN
344 d_position := 0;
345
346 IF (PO_LOG.d_proc) THEN
347 PO_LOG.proc_begin(d_module);
348 END IF;
349
350 l_key := PO_CORE_S.get_session_gt_nextval;
351
352 PO_PDOI_UTL.generate_ordered_num_list
353 (
354 p_size => p_lines.rec_count,
355 x_num_list => l_index_tbl
356 );
357
358 IF (PO_LOG.d_stmt) THEN
359 PO_LOG.stmt(d_module, d_position, 'error flag', p_lines.error_flag_tbl);
360 PO_LOG.stmt(d_module, d_position, 'need to reject flag',
361 p_lines.need_to_reject_flag_tbl);
362 PO_LOG.stmt(d_module, d_position, 'purchase basis', p_lines.purchase_basis_tbl);
363 PO_LOG.stmt(d_module, d_position, 'item', p_lines.item_tbl);
364 PO_LOG.stmt(d_module, d_position, 'item id', p_lines.item_id_tbl);
365 END IF;
366
367 -- insert interface_line_ids within the batch in po_session_gt table;
368 -- Thus it can be used to bulk select location rows
369 FORALL i IN 1..p_lines.rec_count
370 INSERT INTO po_session_gt
371 (
372 key,
373 num1, -- index
374 num2, -- interface_line_id
375 num3, -- po_header_id,
376 num4 -- draft_id
377 )
378 SELECT
379 l_key,
380 l_index_tbl(i),
381 p_lines.intf_line_id_tbl(i),
382 p_lines.hd_po_header_id_tbl(i),
383 p_lines.draft_id_tbl(i)
384 FROM DUAL
385 WHERE p_lines.error_flag_tbl(i) = FND_API.g_FALSE
386 AND p_lines.need_to_reject_flag_tbl(i) = FND_API.g_FALSE
387 AND p_lines.purchase_basis_tbl(i) NOT IN ('TEMP LABOR', 'SERVICES')
388 AND (p_lines.item_tbl(i) IS NOT NULL OR
389 p_lines.item_id_tbl(i) IS NOT NULL);
390
391 d_position := 10;
392
393 -- read location rows and put into x_items
394 /* Bug 6926550 modified the where clause to select
395 only matched key records from po_session_gt*/
396 SELECT
397 -- attributes from headers
398 NVL(draft_headers.ship_to_location_id, txn_headers.ship_to_location_id),
399 NVL(draft_headers.vendor_id, txn_headers.vendor_id),
400
401 -- attributes from line interface
402 intf_lines.unit_weight,
403 intf_lines.unit_volume,
404 intf_lines.item_attribute_category,
405 intf_lines.item_attribute1,
406 intf_lines.item_attribute2,
407 intf_lines.item_attribute3,
408 intf_lines.item_attribute4,
409 intf_lines.item_attribute5,
410 intf_lines.item_attribute6,
411 intf_lines.item_attribute7,
412 intf_lines.item_attribute8,
413 intf_lines.item_attribute9,
414 intf_lines.item_attribute10,
415 intf_lines.item_attribute11,
416 intf_lines.item_attribute12,
417 intf_lines.item_attribute13,
418 intf_lines.item_attribute14,
419 intf_lines.item_attribute15,
420
421 -- attributes from location inteface
422 intf_locs.inspection_required_flag,
423 intf_locs.receipt_required_flag,
424 intf_locs.invoice_close_tolerance,
425 intf_locs.receive_close_tolerance,
426 intf_locs.days_early_receipt_allowed,
427 intf_locs.days_late_receipt_allowed,
428 intf_locs.enforce_ship_to_location_code,
429 intf_locs.allow_substitute_receipts_flag,
430 intf_locs.receiving_routing,
431 intf_locs.receiving_routing_id,
432 intf_locs.receipt_days_exception_code,
433 intf_locs.ship_to_organization_code,
434 intf_locs.ship_to_organization_id,
435 intf_locs.ship_to_location,
436 intf_locs.ship_to_location_id,
437 intf_locs.taxable_flag,
438 intf_locs.qty_rcv_exception_code,
439 intf_locs.qty_rcv_tolerance,
440
441 -- assign dummay values on these columns
442 -- so they won't be defaulted in location default logic
443 'DUMMY', -- shipment_type
444 0, -- shipment_num
445 0, -- line_location_id
446 'DUMMY', -- match_option
447 NULL, -- accrue_on_receipt_flag
448 NULL, -- firm_flag
449 NULL, -- tax_name
450 NULL, -- payment_terms
451 NULL, -- terms_id
452 NULL, -- header terms_id
453 NULL, -- fob
454 NULL, -- header fob
455 NULL, -- freight_carrier
456 NULL, -- header freight_carrier
457 NULL, -- freight_term
458 NULL, -- header freight_term
459 -1, -- price_override
460 -1, -- price_discount
461 -1, -- outsourced_assembly
462 NULL, -- value_basis
463 NULL, -- matching_basis
464 NULL, -- unit_of_measure
465
466 -- standard who columns
467 sysdate,
468 fnd_global.user_id,
469 fnd_global.login_id,
470 sysdate,
471 fnd_global.user_id,
472 fnd_global.conc_request_id,
473 fnd_global.prog_appl_id,
474 fnd_global.conc_program_id,
475 sysdate,
476
477 -- error_flag
478 FND_API.g_FALSE,
479
480 -- reference index in po_lines
481 gt.num1
482 BULK COLLECT INTO
483 -- attributes from headers
484 x_items.hd_ship_to_loc_id_tbl,
485 x_items.hd_vendor_id_tbl,
486
487 -- attributes from lines
488 x_items.ln_unit_weight_tbl,
489 x_items.ln_unit_volume_tbl,
490 x_items.ln_item_attribute_category_tbl,
491 x_items.ln_item_attribute1_tbl,
492 x_items.ln_item_attribute2_tbl,
493 x_items.ln_item_attribute3_tbl,
494 x_items.ln_item_attribute4_tbl,
495 x_items.ln_item_attribute5_tbl,
496 x_items.ln_item_attribute6_tbl,
497 x_items.ln_item_attribute7_tbl,
498 x_items.ln_item_attribute8_tbl,
499 x_items.ln_item_attribute9_tbl,
500 x_items.ln_item_attribute10_tbl,
501 x_items.ln_item_attribute11_tbl,
502 x_items.ln_item_attribute12_tbl,
503 x_items.ln_item_attribute13_tbl,
504 x_items.ln_item_attribute14_tbl,
505 x_items.ln_item_attribute15_tbl,
506
507 -- attributes from location inteface
508 x_items.inspection_required_flag_tbl,
509 x_items.receipt_required_flag_tbl,
510 x_items.invoice_close_tolerance_tbl,
511 x_items.receive_close_tolerance_tbl,
512 x_items.days_early_receipt_allowed_tbl,
513 x_items.days_late_receipt_allowed_tbl,
514 x_items.enforce_ship_to_loc_code_tbl,
515 x_items.allow_sub_receipts_flag_tbl,
516 x_items.receiving_routing_tbl,
517 x_items.receiving_routing_id_tbl,
518 x_items.receipt_days_except_code_tbl,
519 x_items.ship_to_org_code_tbl,
520 x_items.ship_to_org_id_tbl,
521 x_items.ship_to_loc_tbl,
522 x_items.ship_to_loc_id_tbl,
523 x_items.taxable_flag_tbl,
524 x_items.qty_rcv_exception_code_tbl,
525 x_items.qty_rcv_tolerance_tbl,
526
527 -- columns with dummay non-empty values
528 x_items.shipment_type_tbl,
529 x_items.shipment_num_tbl,
530 x_items.line_loc_id_tbl,
531 x_items.match_option_tbl,
532 x_items.accrue_on_receipt_flag_tbl,
533 x_items.firm_flag_tbl,
534 x_items.tax_name_tbl,
535 x_items.payment_terms_tbl,
536 x_items.terms_id_tbl,
537 x_items.hd_terms_id_tbl,
538 x_items.fob_tbl,
539 x_items.hd_fob_tbl,
540 x_items.freight_carrier_tbl,
541 x_items.hd_freight_carrier_tbl,
542 x_items.freight_term_tbl,
543 x_items.hd_freight_term_tbl,
544 x_items.price_override_tbl,
545 x_items.price_discount_tbl,
546 x_items.outsourced_assembly_tbl,
547 x_items.value_basis_tbl,
548 x_items.matching_basis_tbl,
549 x_items.unit_of_measure_tbl,
550
551 -- standard who columns
552 x_items.last_update_date_tbl,
553 x_items.last_updated_by_tbl,
554 x_items.last_update_login_tbl,
555 x_items.creation_date_tbl,
556 x_items.created_by_tbl,
557 x_items.request_id_tbl,
558 x_items.program_application_id_tbl,
559 x_items.program_id_tbl,
560 x_items.program_update_date_tbl,
561
562 -- error flag
563 x_items.error_flag_tbl,
564
565 -- reference index in p_lines
566 x_items.line_ref_index_tbl
567 FROM po_line_locations_interface intf_locs,
568 po_lines_interface intf_lines,
569 po_headers_draft_all draft_headers,
570 po_headers_all txn_headers,
571 po_session_gt gt
572 WHERE gt.num2 = intf_lines.interface_line_id
573 AND intf_lines.interface_line_id = intf_locs.interface_line_id
574 AND intf_locs.processing_id = PO_PDOI_PARAMS.g_processing_id
575 AND gt.num3 = draft_headers.po_header_id(+)
576 AND gt.num4 = draft_headers.draft_id(+)
577 AND gt.num3 = txn_headers.po_header_id(+)
578 AND gt.key = l_key
579 ORDER BY gt.num1, intf_locs.interface_line_location_id;
580
581 IF (PO_LOG.d_stmt) THEN
582 PO_LOG.stmt(d_module, d_position, 'item count',
583 x_items.rec_count);
584 END IF;
585
586 d_position := 20;
587
588 -- bug5106386
589 -- Initialize all pl/sql tables that are not part of the query
590 PO_PDOI_TYPES.fill_all_line_locs_attr
591 ( p_num_records => x_items.line_ref_index_tbl.COUNT,
592 x_line_locs => x_items
593 );
594
595 FOR i IN 1..x_items.rec_count
596 LOOP
597 l_line_index := x_items.line_ref_index_tbl(i);
598
599 x_items.intf_header_id_tbl(i) := p_lines.intf_header_id_tbl(l_line_index);
600 x_items.intf_line_id_tbl(i) := p_lines.intf_line_id_tbl(l_line_index);
601 x_items.ln_po_line_id_tbl(i) := p_lines.po_line_id_tbl(l_line_index);
602 x_items.ln_item_tbl(i) := p_lines.item_tbl(l_line_index);
603 x_items.ln_item_id_tbl(i) := p_lines.item_id_tbl(l_line_index);
604 x_items.ln_item_desc_tbl(i) := p_lines.item_desc_tbl(l_line_index);
605 x_items.ln_unit_of_measure_tbl(i) := p_lines.unit_of_measure_tbl(l_line_index);
606 x_items.ln_list_price_per_unit_tbl(i) := p_lines.list_price_per_unit_tbl(l_line_index);
607 x_items.ln_market_price_tbl(i) := p_lines.market_price_tbl(l_line_index);
608 x_items.ln_un_number_id_tbl(i) := p_lines.un_number_id_tbl(l_line_index);
609 x_items.ln_hazard_class_id_tbl(i) := p_lines.hazard_class_id_tbl(l_line_index);
610 x_items.ln_qty_rcv_exception_code_tbl(i) := p_lines.qty_rcv_exception_code_tbl(l_line_index);
611 x_items.ln_weight_uom_code_tbl(i) := p_lines.weight_uom_code_tbl(l_line_index);
612 x_items.ln_volume_uom_code_tbl(i) := p_lines.volume_uom_code_tbl(l_line_index);
613 x_items.ln_template_id_tbl(i) := p_lines.template_id_tbl(l_line_index);
614 x_items.ln_category_id_tbl(i) := p_lines.category_id_tbl(l_line_index);
615 x_items.ln_order_type_lookup_code_tbl(i) := p_lines.order_type_lookup_code_tbl(l_line_index);
616 x_items.ln_line_type_id_tbl(i) := p_lines.line_type_id_tbl(l_line_index);
617 x_items.ln_matching_basis_tbl(i) := p_lines.matching_basis_tbl(l_line_index);
618 x_items.ln_unit_price_tbl(i) := p_lines.unit_price_tbl(l_line_index);
619 x_items.hd_currency_code_tbl(i) := p_lines.hd_currency_code_tbl(l_line_index);
620 END LOOP;
621
622 IF (PO_LOG.d_proc) THEN
623 PO_LOG.proc_end (d_module);
624 END IF;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 PO_MESSAGE_S.add_exc_msg
629 (
630 p_pkg_name => d_pkg_name,
631 p_procedure_name => d_api_name || '.' || d_position
632 );
633 RAISE;
634 END construct_item_records;
635
636 -----------------------------------------------------------------------
637 --Start of Comments
638 --Name: derive_default_loc_attrs
639 --Function:
640 -- derive and default location related attributes read from locations
641 -- interface table; no need to derive and default line related attributes
642 -- since it is done before creating/updating items in item master
643 --Parameters:
644 --IN:
645 --IN OUT:
646 -- x_items
647 -- the item records containing line info for each location
648 --OUT:
649 --End of Comments
650 ------------------------------------------------------------------------
651 PROCEDURE derive_default_loc_attrs
652 (
653 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
654 ) IS
655
656 d_api_name CONSTANT VARCHAR2(30) := 'derive_default_loc_attrs';
657 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
658 d_position NUMBER;
659
660 BEGIN
661 d_position := 0;
662
663 IF (PO_LOG.d_proc) THEN
664 PO_LOG.proc_begin(d_module);
665 END IF;
666
667 -- derive attributes from location interface table
668 PO_PDOI_LINE_LOC_PROCESS_PVT.derive_line_locs
669 (
670 x_line_locs => x_items
671 );
672
673 d_position := 10;
674
675 -- default attributes from location interface table
676 PO_PDOI_LINE_LOC_PROCESS_PVT.default_line_locs
677 (
678 x_line_locs => x_items
679 );
680
681 IF (PO_LOG.d_proc) THEN
682 PO_LOG.proc_end (d_module);
683 END IF;
684
685 EXCEPTION
686 WHEN OTHERS THEN
687 PO_MESSAGE_S.add_exc_msg
688 (
689 p_pkg_name => d_pkg_name,
690 p_procedure_name => d_api_name || '.' || d_position
691 );
692 RAISE;
693 END derive_default_loc_attrs;
694
695 -----------------------------------------------------------------------
696 --Start of Comments
697 --Name: identify_actions
698 --Function:
699 -- This procedure is to determine for which location and organization,
700 -- we need to create/update items in item master;
701 -- To create an item, the item number must be non-empty; To update
702 -- an item, item id must be non-empty after line derivation logic;
703 -- Items can be created in 3 organizations: default inv org, default
704 -- master org and ship_to org;
705 -- Items can only be updated in default master org
706 --Parameters:
707 --IN:
708 -- p_items
709 -- the record containing item information
710 --IN OUT:
711 --OUT:
712 -- x_create_index_tbl
713 -- index of p_items for which we need to create an item; The organizations
714 -- in which items are created are specified in x_create_org_id_tbl
715 -- x_create_org_id_tbl
716 -- The organizations in which items are created in item master;
717 -- The values can be default inv org id, default master org id
718 -- or ship_to org id on each location
719 -- x_update_index_tbl
720 -- index of p_items for which items are going to be updated
721 --End of Comments
722 ------------------------------------------------------------------------
723 PROCEDURE identify_actions
724 (
725 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type,
726 x_unprocessed_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
727 x_proc_row_in_round_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
728 x_create_in_inv_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
729 x_create_in_master_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
730 x_create_in_ship_to_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
731 x_update_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE
732 ) IS
733
734 d_api_name CONSTANT VARCHAR2(30) := 'identify_actions';
735 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
736 d_position NUMBER;
737
738 l_index NUMBER;
739 l_key PO_SESSION_GT.key%TYPE;
740
741 -- variables used to read result from po_session_gt table
742 l_index_tbl PO_TBL_NUMBER;
743 l_org_id_tbl PO_TBL_NUMBER;
744 l_item_id_tbl PO_TBL_NUMBER;
745
746 -- flag to indicate whether item exists in some orgs
747 l_exist_in_ship_to_org_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
748 l_exist_in_master_org_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
749 l_exist_in_inv_org_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
750
751 -- hashtable on item number
752 TYPE item_ref_type IS TABLE OF NUMBER INDEX BY VARCHAR2(1000);
753 l_item_ref_tbl item_ref_type;
754
755 BEGIN
756 d_position := 0;
757
758 IF (PO_LOG.d_proc) THEN
759 PO_LOG.proc_begin(d_module);
760 END IF;
761
762 -- allocate memory for nested table
763 l_exist_in_inv_org_tbl.EXTEND(x_items.rec_count);
764 l_exist_in_master_org_tbl.EXTEND(x_items.rec_count);
765 l_exist_in_ship_to_org_tbl.EXTEND(x_items.rec_count);
766
767 d_position := 10;
768
769 -- determine rows that are going to be processed in this
770 -- procedure call
771 l_index := x_unprocessed_row_tbl.FIRST;
772 WHILE (l_index IS NOT NULL)
773 LOOP
774 IF (NOT l_item_ref_tbl.EXISTS(x_items.ln_item_tbl(l_index))) THEN
775 IF (PO_LOG.d_stmt) THEN
776 PO_LOG.stmt(d_module, d_position, 'process row index in current round',
777 l_index);
778 END IF;
779
780 -- can be processed in this time of procedure call
781 x_proc_row_in_round_tbl(l_index) := l_index;
782
783 -- remove this row from unprocessed rows list
784 x_unprocessed_row_tbl.DELETE(l_index);
785
786 -- register this item_num in hashtable
787 --Bug 6956962 <start>
788 if x_items.ln_item_tbl(l_index) is not null then
789 l_item_ref_tbl(x_items.ln_item_tbl(l_index)) := l_index;
790 else
791 l_item_ref_tbl(x_items.ln_item_id_tbl(l_index)) := l_index;
792 end if;
793 --l_item_ref_tbl(x_items.ln_item_tbl(l_index)) := l_index;
794 -- Bug 6956962 <end>
795
796 -- set initial values for l_exist_in_xxx_org_tbl to 'N'
797 l_exist_in_inv_org_tbl(l_index) := FND_API.g_FALSE;
798 l_exist_in_master_org_tbl(l_index) := FND_API.g_FALSE;
799 l_exist_in_ship_to_org_tbl(l_index) := FND_API.g_FALSE;
800 END IF;
801
802 l_index := x_unprocessed_row_tbl.NEXT(l_index);
803 END LOOP;
804
805 d_position := 20;
806
807 l_key := PO_CORE_S.get_session_gt_nextval;
808 -- check whether item exists in default inv org, default master
809 -- org or ship_to org
810 FORALL i IN INDICES OF x_proc_row_in_round_tbl
811 INSERT INTO po_session_gt
812 (
813 key,
814 num1,
815 num2,
816 num3
817 )
818 SELECT
819 l_key,
820 x_proc_row_in_round_tbl(i),
821 organization_id,
822 inventory_item_id
823 FROM mtl_system_items_vl
824 WHERE organization_id IN
825 (PO_PDOI_PARAMS.g_sys.def_inv_org_id,
826 PO_PDOI_PARAMS.g_sys.master_inv_org_id,
827 x_items.ship_to_org_id_tbl(i)
828 )
829 AND (concatenated_segments = x_items.ln_item_tbl(i)
830 OR inventory_item_id = x_items.ln_item_id_tbl(i)) ; --6956962
831
832 --concatenated_segments = x_items.ln_item_tbl(i);
833
834 d_position := 30;
835
836 DELETE FROM po_session_gt
837 WHERE key = l_key
838 RETURNING num1, num2, num3 BULK COLLECT INTO
839 l_index_tbl, l_org_id_tbl, l_item_id_tbl;
840
841 FOR i IN 1..l_index_tbl.COUNT
842 LOOP
843 l_index := l_index_tbl(i);
844
845 IF (PO_LOG.d_stmt) THEN
846 PO_LOG.stmt(d_module, d_position, 'i', i);
847 PO_LOG.stmt(d_module, d_position, 'index', l_index);
848 PO_LOG.stmt(d_module, d_position, 'l_org_id_tbl(i)', l_org_id_tbl(i));
849 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl(i)', l_item_id_tbl(i));
850 PO_LOG.stmt(d_module, d_position, 'ship_to_org_id_tbl(l_index)',
851 x_items.ship_to_org_id_tbl(l_index));
852 END IF;
853
854 IF (l_org_id_tbl(i) = PO_PDOI_PARAMS.g_sys.def_inv_org_id) THEN
855 l_exist_in_inv_org_tbl(l_index) := FND_API.g_TRUE;
856 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
857 END IF;
858
859 IF (l_org_id_tbl(i) = PO_PDOI_PARAMS.g_sys.master_inv_org_id) THEN
860 l_exist_in_master_org_tbl(l_index) := FND_API.g_TRUE;
861 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
862 END IF;
863
864 IF (l_org_id_tbl(i) = x_items.ship_to_org_id_tbl(l_index)) THEN
865 l_exist_in_ship_to_org_tbl(l_index) := FND_API.g_TRUE;
866 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
867 END IF;
868 END LOOP;
869
870 d_position := 40;
871
872 -- depend on whether item exists in item master, assign actions
873 l_index := x_proc_row_in_round_tbl.FIRST;
874 WHILE (l_index IS NOT NULL)
875 LOOP
876 IF (PO_LOG.d_stmt) THEN
877 PO_LOG.stmt(d_module, d_position, 'index', l_index);
878 PO_LOG.stmt(d_module, d_position, 'exist_in_inv_org',
879 l_exist_in_inv_org_tbl(l_index));
880 PO_LOG.stmt(d_module, d_position, 'exist_in_ship_to_org',
881 l_exist_in_ship_to_org_tbl(l_index));
882 PO_LOG.stmt(d_module, d_position, 'exist_in_master_org',
883 l_exist_in_master_org_tbl(l_index));
884 PO_LOG.stmt(d_module, d_position, 'ship_to_org_id',
885 x_items.ship_to_org_id_tbl(l_index));
886 PO_LOG.stmt(d_module, d_position, 'item_id',
887 x_items.ln_item_id_tbl(l_index));
888 END IF;
889
890 IF (l_exist_in_inv_org_tbl(l_index) = FND_API.g_FALSE OR
891 l_exist_in_ship_to_org_tbl(l_index) = FND_API.g_FALSE) THEN
892 -- set whether item needs to be created in inv/master org
893 IF (l_exist_in_master_org_tbl(l_index) = FND_API.g_FALSE) THEN
894 x_create_in_master_index_tbl(l_index) := l_index;
895
896 IF (l_exist_in_inv_org_tbl(l_index) = FND_API.g_FALSE AND
897 PO_PDOI_PARAMS.g_sys.master_inv_org_id <>
898 PO_PDOI_PARAMS.g_sys.def_inv_org_id) THEN
899 x_create_in_inv_index_tbl(l_index) := l_index;
900 END IF;
901 END IF;
902
903 d_position := 50;
904
905 -- set flag for whether item needs to be created in ship_to org
906 IF (l_exist_in_ship_to_org_tbl(l_index) = FND_API.g_FALSE AND
907 x_items.ship_to_org_id_tbl(l_index) IS NOT NULL AND
908 x_items.ship_to_org_id_tbl(l_index) <> PO_PDOI_PARAMS.g_sys.master_inv_org_id AND
909 x_items.ship_to_org_id_tbl(l_index) <> PO_PDOI_PARAMS.g_sys.def_inv_org_id) THEN
910 x_create_in_ship_to_index_tbl(l_index) := l_index;
911 END IF;
912 ELSIF (x_items.ln_item_id_tbl(l_index) IS NOT NULL) THEN
913 -- for each po line, if item_id is not empty, then update item in item master
914 x_update_index_tbl(l_index) := l_index;
915 END IF;
916
917 l_index := x_proc_row_in_round_tbl.NEXT(l_index);
918 END LOOP;
919
920 IF (PO_LOG.d_proc) THEN
921 PO_LOG.proc_end (d_module);
922 END IF;
923
924 EXCEPTION
925 WHEN OTHERS THEN
926 PO_MESSAGE_S.add_exc_msg
927 (
928 p_pkg_name => d_pkg_name,
929 p_procedure_name => d_api_name || '.' || d_position
930 );
931 RAISE;
932 END identify_actions;
933
934 -----------------------------------------------------------------------
935 --Start of Comments
936 --Name: insert_master_item
937 --Function:
938 -- This procedure is to create items in item master;
939 -- we will set item_id in x_items if creation is successful;
940 -- and set error_flag to g_TRUE if failed
941 --Parameters:
942 --IN:
943 -- p_org_type
944 -- organization type in which items are going to be created;
945 -- the values can be 'INV', 'MASTER' or 'SHIP_TO'
946 -- p_index_tbl
947 -- index of x_items for which we need to create an item;
948 --IN OUT:
949 -- x_items
950 -- the record containing item information
951 --OUT:
952 --End of Comments
953 ------------------------------------------------------------------------
954 PROCEDURE insert_master_item
955 (
956 p_org_type IN VARCHAR2,
957 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
958 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
959 ) IS
960
961 d_api_name CONSTANT VARCHAR2(30) := 'insert_master_item';
962 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
963 d_position NUMBER;
964
965 l_set_process_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
966 l_index NUMBER;
967
968 -- key value used to identify records in po_session_gt
969 l_key PO_SESSION_GT.key%TYPE;
970
971 -- variables used to call Inventory API
972 l_org_id NUMBER;
973 l_err_text VARCHAR2(3000);
974 l_return_code NUMBER;
975
976 -- variables used to get processed result from interface table
977 l_index_tbl PO_TBL_NUMBER;
978 l_process_flag_tbl PO_TBL_NUMBER;
979 l_transaction_id_tbl PO_TBL_NUMBER;
980 l_item_id_tbl PO_TBL_NUMBER;
981 l_org_id_tbl PO_TBL_NUMBER;
982 l_revision_tbl PO_TBL_VARCHAR5;
983 l_category_id_tbl DBMS_SQL.NUMBER_TABLE;
984
985 -- rows that return errors when creating items
986 l_error_index_tbl DBMS_SQL.NUMBER_TABLE;
987 l_table_name_tbl PO_TBL_VARCHAR30;
988 l_message_name_tbl PO_TBL_VARCHAR30;
989 l_column_name_tbl PO_TBL_VARCHAR100;
990 BEGIN
991 d_position := 0;
992
993 IF (PO_LOG.d_proc) THEN
994 PO_LOG.proc_begin(d_module, 'p_org_type', p_org_type);
995 END IF;
996
997 -- insert data in item interface table
998 l_set_process_id_tbl.EXTEND(x_items.rec_count);
999 l_index := p_index_tbl.FIRST;
1000 WHILE (l_index IS NOT NULL)
1001 LOOP
1002 l_set_process_id_tbl(l_index) :=
1003 PO_PDOI_MAINPROC_UTL_PVT.get_next_set_process_id;
1004
1005 IF (PO_LOG.d_stmt) THEN
1006 PO_LOG.stmt(d_module, d_position, 'index', l_index);
1007 PO_LOG.stmt(d_module, d_position, 'set process id',
1008 l_set_process_id_tbl(l_index));
1009 END IF;
1010
1011 l_index := p_index_tbl.NEXT(l_index);
1012 END LOOP;
1013
1014 d_position := 10;
1015
1016 FORALL i IN INDICES OF p_index_tbl
1017 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
1018 (
1019 process_flag,
1020 set_process_id,
1021 transaction_type,
1022 item_number,
1023 inventory_item_id,
1024 description,
1025 purchasing_item_flag,
1026 inventory_item_flag,
1027 purchasing_enabled_flag,
1028 primary_unit_of_measure,
1029 list_price_per_unit,
1030 market_price,
1031 un_number_id,
1032 hazard_class_id,
1033 taxable_flag,
1034 inspection_required_flag,
1035 receipt_required_flag,
1036 invoice_close_tolerance,
1037 receive_close_tolerance,
1038 days_early_receipt_allowed,
1039 days_late_receipt_allowed,
1040 enforce_ship_to_location_code,
1041 allow_substitute_receipts_flag,
1042 receiving_routing_id,
1043 qty_rcv_tolerance,
1044 qty_rcv_exception_code,
1045 receipt_days_exception_code,
1046 last_update_date,
1047 last_updated_by,
1048 last_update_login,
1049 creation_date,
1050 created_by,
1051 request_id,
1052 program_application_id,
1053 program_id,
1054 program_update_date,
1055 organization_id,
1056 attribute_category,
1057 attribute1,
1058 attribute2,
1059 attribute3,
1060 attribute4,
1061 attribute5,
1062 attribute6,
1063 attribute7,
1064 attribute8,
1065 attribute9,
1066 attribute10,
1067 attribute11,
1068 attribute12,
1069 attribute13,
1070 attribute14,
1071 attribute15,
1072 unit_weight,
1073 weight_uom_code,
1074 volume_uom_code,
1075 unit_volume,
1076 template_id
1077 )
1078 VALUES
1079 (
1080 1, -- process_flag
1081 l_set_process_id_tbl(i),
1082 'CREATE', -- transaction_type
1083 x_items.ln_item_tbl(i),
1084 x_items.ln_item_id_tbl(i),
1085 x_items.ln_item_desc_tbl(i),
1086 'Y', -- purchasing_item_flag
1087 decode(x_items.ln_template_id_tbl(i), NULL, 'Y', NULL), -- inventory_item_flag
1088 'Y', -- purchasing_enabled_flag,
1089 x_items.ln_unit_of_measure_tbl(i),
1090 x_items.ln_list_price_per_unit_tbl(i),
1091 x_items.ln_market_price_tbl(i),
1092 x_items.ln_un_number_id_tbl(i),
1093 x_items.ln_hazard_class_id_tbl(i),
1094 x_items.taxable_flag_tbl(i),
1095 x_items.inspection_required_flag_tbl(i),
1096 x_items.receipt_required_flag_tbl(i),
1097 x_items.invoice_close_tolerance_tbl(i),
1098 x_items.receive_close_tolerance_tbl(i),
1099 x_items.days_early_receipt_allowed_tbl(i),
1100 x_items.days_late_receipt_allowed_tbl(i),
1101 x_items.enforce_ship_to_loc_code_tbl(i),
1102 x_items.allow_sub_receipts_flag_tbl(i),
1103 x_items.receiving_routing_id_tbl(i),
1104 x_items.qty_rcv_tolerance_tbl(i),
1105 x_items.qty_rcv_exception_code_tbl(i),
1106 x_items.receipt_days_except_code_tbl(i),
1107 x_items.last_update_date_tbl(i),
1108 x_items.last_updated_by_tbl(i),
1109 x_items.last_update_login_tbl(i),
1110 x_items.creation_date_tbl(i),
1111 x_items.created_by_tbl(i),
1112 x_items.request_id_tbl(i),
1113 x_items.program_application_id_tbl(i),
1114 x_items.program_id_tbl(i),
1115 x_items.program_update_date_tbl(i),
1116 DECODE(p_org_type, 'MASTER', PO_PDOI_PARAMS.g_sys.master_inv_org_id,
1117 'INV', PO_PDOI_PARAMS.g_sys.def_inv_org_id,
1118 x_items.ship_to_org_id_tbl(i)), -- organization_id
1119 x_items.ln_item_attribute_category_tbl(i),
1120 x_items.ln_item_attribute1_tbl(i),
1121 x_items.ln_item_attribute2_tbl(i),
1122 x_items.ln_item_attribute3_tbl(i),
1123 x_items.ln_item_attribute4_tbl(i),
1124 x_items.ln_item_attribute5_tbl(i),
1125 x_items.ln_item_attribute6_tbl(i),
1126 x_items.ln_item_attribute7_tbl(i),
1127 x_items.ln_item_attribute8_tbl(i),
1128 x_items.ln_item_attribute9_tbl(i),
1129 x_items.ln_item_attribute10_tbl(i),
1130 x_items.ln_item_attribute11_tbl(i),
1131 x_items.ln_item_attribute12_tbl(i),
1132 x_items.ln_item_attribute13_tbl(i),
1133 x_items.ln_item_attribute14_tbl(i),
1134 x_items.ln_item_attribute15_tbl(i),
1135 x_items.ln_unit_weight_tbl(i),
1136 x_items.ln_weight_uom_code_tbl(i),
1137 x_items.ln_volume_uom_code_tbl(i),
1138 x_items.ln_unit_volume_tbl(i),
1139 x_items.ln_template_id_tbl(i)
1140 );
1141
1142 d_position := 20;
1143
1144 -- Call inventory API to handle rows in item interface table
1145 l_index := p_index_tbl.FIRST;
1146 WHILE (l_index IS NOT NULL)
1147 LOOP
1148 IF (p_org_type = 'MASTER') THEN
1149 l_org_id := PO_PDOI_PARAMS.g_sys.master_inv_org_id;
1150 ELSIF (p_org_type = 'INV') THEN
1151 l_org_id := PO_PDOI_PARAMS.g_sys.def_inv_org_id;
1152 ELSE
1153 l_org_id := x_items.ship_to_org_id_tbl(l_index);
1154 END IF;
1155
1156 -- Bug7117320: Added param names when calling the function.
1157 l_return_code := invpopif.inopinp_open_interface_process
1158 (
1159 org_id => l_org_id,
1160 all_org => 2,
1161 val_item_flag => 1,
1162 pro_item_flag => 1,
1163 del_rec_flag => 2, -- do not delete the record
1164 prog_appid => fnd_global.prog_appl_id,
1165 prog_id => -1, -- Inventory does not gather statistics when processing the records inserted into its interface table
1166 request_id => fnd_global.conc_request_id,
1167 user_id => fnd_global.user_id,
1168 login_id => fnd_global.login_id,
1169 err_text => l_err_text,
1170 xset_id => l_set_process_id_tbl(l_index),
1171 commit_flag => 2 -- no commit
1172 );
1173
1174 l_index := p_index_tbl.NEXT(l_index);
1175 END LOOP;
1176
1177 d_position := 30;
1178
1179 l_key := PO_CORE_S.get_session_gt_nextval;
1180 -- get processed result
1181 FORALL i IN INDICES OF p_index_tbl
1182 INSERT INTO po_session_gt
1183 (
1184 key,
1185 num1,
1186 num2,
1187 num3,
1188 num4,
1189 num5,
1190 num6,
1191 char1
1192 )
1193 SELECT
1194 l_key,
1195 p_index_tbl(i),
1196 set_process_id,
1197 process_flag,
1198 transaction_id,
1199 inventory_item_id,
1200 organization_id,
1201 revision
1202 FROM mtl_system_items_interface
1203 WHERE set_process_id = l_set_process_id_tbl(i);
1204
1205 d_position := 40;
1206
1207 DELETE FROM po_session_gt
1208 WHERE key = l_key
1209 RETURNING num1, num2, num3, num4, num5, num6, char1
1210 BULK COLLECT INTO
1211 l_index_tbl,
1212 l_set_process_id_tbl,
1213 l_process_flag_tbl,
1214 l_transaction_id_tbl,
1215 l_item_id_tbl,
1216 l_org_id_tbl,
1217 l_revision_tbl;
1218
1219 IF (PO_LOG.d_stmt) THEN
1220 PO_LOG.stmt(d_module, d_position, 'l_index_tbl',
1221 l_index_tbl);
1222 PO_LOG.stmt(d_module, d_position, 'l_set_process_id_tbl',
1223 l_set_process_id_tbl);
1224 PO_LOG.stmt(d_module, d_position, 'l_process_flag_tbl',
1225 l_process_flag_tbl);
1226 PO_LOG.stmt(d_module, d_position, 'l_transaction_id_tbl',
1227 l_transaction_id_tbl);
1228 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl',
1229 l_item_id_tbl);
1230 PO_LOG.stmt(d_module, d_position, 'l_org_id_tbl',
1231 l_org_id_tbl);
1232 PO_LOG.stmt(d_module, d_position, 'l_revision_tbl',
1233 l_revision_tbl);
1234 END IF;
1235
1236 FOR i IN 1..l_index_tbl.COUNT
1237 LOOP
1238 l_index := l_index_tbl(i);
1239
1240 IF (l_process_flag_tbl(i) = 7) THEN
1241 -- set item_id back
1242 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
1243
1244 IF (x_items.ln_category_id_tbl(l_index) IS NOT NULL) THEN
1245 l_category_id_tbl(i) := x_items.ln_category_id_tbl(l_index);
1246 END IF;
1247 ELSE
1248 -- remember the rows with errors
1249 -- error handling will be done in batch later
1250 l_error_index_tbl(i) := l_index_tbl(i);
1251
1252 -- no need to remove records from interface tables below
1253 l_index_tbl.DELETE(i);
1254 END IF;
1255 END LOOP;
1256
1257 d_position := 50;
1258
1259 -- update category table
1260 FORALL i IN INDICES OF l_category_id_tbl
1261 UPDATE mtl_item_categories
1262 SET category_id = l_category_id_tbl(i)
1263 WHERE inventory_item_id = l_item_id_tbl(i)
1264 AND organization_id = l_org_id_tbl(i)
1265 AND category_set_id = PO_PDOI_PARAMS.g_sys.def_cat_set_id;
1266
1267 d_position := 60;
1268
1269 -- delete rows from item interface tables
1270 FORALL i IN 1..l_index_tbl.COUNT
1271 DELETE FROM mtl_system_items_interface
1272 WHERE transaction_id = l_transaction_id_tbl(i)
1273 AND set_process_id = l_set_process_id_tbl(i);
1274
1275 d_position := 70;
1276
1277 FORALL i IN 1..l_index_tbl.COUNT
1278 DELETE FROM mtl_item_categories_interface
1279 WHERE inventory_item_id = l_item_id_tbl(i)
1280 AND organization_id = l_org_id_tbl(i);
1281
1282 d_position := 80;
1283
1284 FORALL i IN 1..l_index_tbl.COUNT
1285 DELETE FROM mtl_item_revisions_interface
1286 WHERE inventory_item_id = l_item_id_tbl(i)
1287 AND organization_id = l_org_id_tbl(i)
1288 AND revision = l_revision_tbl(i);
1289
1290 d_position := 90;
1291
1292 -- handle the errors thrown by item creation
1293 -- 1. read errors from item error interface table
1294 FORALL i IN INDICES OF l_error_index_tbl
1295 INSERT INTO po_session_gt
1296 (
1297 key,
1298 num1,
1299 char1,
1300 char2,
1301 char3
1302 )
1303 SELECT
1304 l_key,
1305 l_error_index_tbl(i),
1306 table_name,
1307 message_name,
1308 column_name
1309 FROM mtl_interface_errors
1310 WHERE transaction_id = l_transaction_id_tbl(i)
1311 OR transaction_id = (
1312 SELECT transaction_id
1313 FROM mtl_item_categories_interface
1314 WHERE organization_id = l_org_id_tbl(i)
1315 AND inventory_item_id = l_item_id_tbl(i))
1316 OR transaction_id = (
1317 SELECT transaction_id
1318 FROM mtl_item_revisions_interface
1319 WHERE organization_id = l_org_id_tbl(i)
1320 AND inventory_item_id = l_item_id_tbl(i)
1321 AND revision = l_revision_tbl(i));
1322
1323 d_position := 100;
1324
1325 DELETE FROM po_session_gt
1326 WHERE key = l_key
1327 RETURNING num1, char1, char2, char3 BULK COLLECT INTO
1328 l_error_index_tbl,
1329 l_table_name_tbl,
1330 l_message_name_tbl,
1331 l_column_name_tbl;
1332
1333 -- add fatal errors to po interface error table
1334 FOR i IN 1..l_error_index_tbl.COUNT
1335 LOOP
1336 -- get index in x_items
1337 l_index := l_error_index_tbl(i);
1338
1339 IF (PO_LOG.d_stmt) THEN
1340 PO_LOG.stmt(d_module, d_position, 'add error on index',
1341 l_index);
1342 PO_LOG.stmt(d_module, d_position, 'intf line id',
1343 x_items.intf_line_id_tbl(l_index));
1344 PO_LOG.stmt(d_module, d_position, 'error message',
1345 l_message_name_tbl(i));
1346 PO_LOG.stmt(d_module, d_position, 'table name',
1347 l_table_name_tbl(i));
1348 PO_LOG.stmt(d_module, d_position, 'column name',
1349 l_column_name_tbl(i));
1350 END IF;
1351
1352 PO_PDOI_ERR_UTL.add_fatal_error
1353 (
1354 p_interface_header_id => x_items.intf_header_id_tbl(l_index),
1355 p_interface_line_id => x_items.intf_line_id_tbl(l_index),
1356 p_app_name => 'INV',
1357 p_error_message_name => l_message_name_tbl(i),
1358 p_table_name => l_table_name_tbl(i),
1359 p_column_name => l_column_name_tbl(i),
1360 p_column_value => NULL
1361 );
1362
1363 x_items.error_flag_tbl(l_index) := FND_API.g_TRUE;
1364 END LOOP;
1365
1366 IF (PO_LOG.d_proc) THEN
1367 PO_LOG.proc_end (d_module);
1368 END IF;
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 PO_MESSAGE_S.add_exc_msg
1373 (
1374 p_pkg_name => d_pkg_name,
1375 p_procedure_name => d_api_name || '.' || d_position
1376 );
1377 RAISE;
1378 END insert_master_item;
1379
1380 -----------------------------------------------------------------------
1381 --Start of Comments
1382 --Name: update_master_item
1383 --Function:
1384 -- This procedure is to update items in item master if needed
1385 --Parameters:
1386 --IN:
1387 -- p_index_tbl
1388 -- index of p_items for which we need to update if needed;
1389 -- p_items
1390 -- the record containing item information
1391 --IN OUT:
1392 --OUT:
1393 --End of Comments
1394 ------------------------------------------------------------------------
1395 PROCEDURE update_master_item
1396 (
1397 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1398 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
1399 ) IS
1400
1401 d_api_name CONSTANT VARCHAR2(30) := 'update_master_item';
1402 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1403 d_position NUMBER;
1404
1405 l_index NUMBER;
1406 l_key po_session_gt.key%TYPE;
1407
1408 -- variables to store results of original values defined in item
1409 l_index_tbl PO_TBL_NUMBER;
1410 l_orig_desc_tbl PO_TBL_VARCHAR2000;
1411 l_orig_list_price_tbl PO_TBL_NUMBER;
1412
1413 l_update_index_tbl DBMS_SQL.NUMBER_TABLE;
1414
1415 -- variables to hold results from INV's API call
1416 l_inventory_item_id NUMBER;
1417 l_organization_id NUMBER;
1418 l_return_status VARCHAR2(1);
1419 l_msg_count VARCHAR2(10);
1420 l_msg_data VARCHAR2(1000);
1421 l_message_list Error_Handler.Error_Tbl_Type;
1422 BEGIN
1423 d_position := 0;
1424
1425 IF (PO_LOG.d_proc) THEN
1426 PO_LOG.proc_begin(d_module);
1427 END IF;
1428
1429 -- get original description and list price for comparison
1430 l_key := PO_CORE_S.get_session_gt_nextval;
1431 l_update_index_tbl := p_index_tbl;
1432
1433 FORALL i IN INDICES OF l_update_index_tbl
1434 INSERT INTO po_session_gt(key, num1, char1, num2)
1435 SELECT l_key,
1436 l_update_index_tbl(i),
1437 description,
1438 list_price_per_unit
1439 FROM mtl_system_items
1440 WHERE inventory_item_id = x_items.ln_item_id_tbl(i)
1441 AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
1442
1443 d_position := 10;
1444
1445 DELETE FROM po_session_gt
1446 WHERE key = l_key
1447 RETURNING num1, char1, num2 BULK COLLECT INTO
1448 l_index_tbl, l_orig_desc_tbl, l_orig_list_price_tbl;
1449
1450 IF (PO_LOG.d_stmt) THEN
1451 PO_LOG.stmt(d_module, d_position, 'l_index_tbl',
1452 l_index_tbl);
1453 PO_LOG.stmt(d_module, d_position, 'l_orig_desc_tbl',
1454 l_orig_desc_tbl);
1455 PO_LOG.stmt(d_module, d_position, 'l_orig_list_price_tbl',
1456 l_orig_list_price_tbl);
1457 END IF;
1458
1459 d_position := 20;
1460
1461 FOR i IN 1..l_index_tbl.COUNT
1462 LOOP
1463 l_index := l_index_tbl(i);
1464
1465 IF (PO_LOG.d_stmt) THEN
1466 PO_LOG.stmt(d_module, d_position, 'i',
1467 i);
1468 PO_LOG.stmt(d_module, d_position, 'l_index',
1469 l_index);
1470 PO_LOG.stmt(d_module, d_position, 'x_items.ln_item_desc_tbl(l_index)',
1471 x_items.ln_item_desc_tbl(l_index));
1472 PO_LOG.stmt(d_module, d_position, 'x_items.ln_list_price_per_unit_tbl(l_index)',
1473 x_items.ln_list_price_per_unit_tbl(l_index));
1474 END IF;
1475
1476 d_position := 30;
1477
1478 IF (x_items.ln_item_desc_tbl(l_index) IS NULL AND
1479 x_items.ln_list_price_per_unit_tbl(l_index) IS NULL) THEN
1480 -- nothing to update
1481 l_update_index_tbl.DELETE(l_index);
1482 ELSIF (x_items.ln_item_desc_tbl(l_index) = l_orig_desc_tbl(i) AND
1483 (x_items.ln_list_price_per_unit_tbl(l_index) = l_orig_list_price_tbl(i)
1484 OR
1485 (x_items.ln_list_price_per_unit_tbl(l_index) is null AND
1486 l_orig_list_price_tbl(i) is null)
1487 )
1488 ) THEN
1489 -- no change, no need to update
1490 l_update_index_tbl.DELETE(l_index);
1491 ELSE
1492 NULL;
1493 END IF;
1494 END LOOP;
1495
1496 d_position := 40;
1497
1498 -- call Inventory Team's API to update item description and list price if needed
1499 l_index := l_update_index_tbl.FIRST;
1500 WHILE (l_index IS NOT NULL)
1501 LOOP
1502 EGO_ITEM_PUB.Process_Item
1503 (
1504 p_api_version => 1.0,
1505 p_init_msg_list => FND_API.g_TRUE,
1506 p_commit => FND_API.g_TRUE,
1507 p_Transaction_Type => 'UPDATE',
1508 p_Inventory_Item_Id => x_items.ln_item_id_tbl(l_index),
1509 p_Organization_Id => PO_PDOI_PARAMS.g_sys.def_inv_org_id,
1510 p_description => NVL(x_items.ln_item_desc_tbl(l_index), EGO_ITEM_PUB.G_MISS_CHAR),
1511 p_list_price_per_unit => NVL(x_items.ln_list_price_per_unit_tbl(l_index), EGO_ITEM_PUB.G_MISS_NUM),
1512 p_Item_Number => x_items.ln_item_tbl(l_index),
1513 x_Inventory_Item_Id => l_inventory_item_id,
1514 x_Organization_Id => l_organization_id,
1515 x_return_status => l_return_status,
1516 x_msg_count => l_msg_count,
1517 x_msg_data => l_msg_data
1518 );
1519
1520 IF (PO_LOG.d_stmt) THEN
1521 PO_LOG.stmt(d_module, d_position, 'return status for item update',
1522 l_return_status);
1523 END IF;
1524
1525 d_position := 50;
1526
1527 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1528 -- error handling
1529 Error_Handler.GET_MESSAGE_LIST
1530 (
1531 x_message_list => l_message_list
1532 );
1533 IF (PO_LOG.d_stmt) THEN
1534 PO_LOG.stmt(d_module, d_position, 'count of error message',
1535 l_message_list.COUNT);
1536 END IF;
1537
1538 d_position := 60;
1539 FOR i IN 1..l_message_list.COUNT
1540 LOOP
1541 IF (PO_LOG.d_stmt) THEN
1542 PO_LOG.stmt(d_module, d_position, 'index', i);
1543 PO_LOG.stmt(d_module, d_position, 'intf header id',
1544 x_items.intf_header_id_tbl(l_index));
1545 PO_LOG.stmt(d_module, d_position, 'intf line id',
1546 x_items.intf_line_id_tbl(l_index));
1547 PO_LOG.stmt(d_module, d_position, 'message text',
1548 l_message_list(i).message_text);
1549 PO_LOG.stmt(d_module, d_position, 'table name',
1550 l_message_list(i).table_name);
1551 END IF;
1552
1553 PO_PDOI_ERR_UTL.add_fatal_error
1554 (
1555 p_interface_header_id => x_items.intf_header_id_tbl(l_index),
1556 p_interface_line_id => x_items.intf_line_id_tbl(l_index),
1557 p_app_name => 'INV',
1558 p_error_message_name => l_message_list(i).message_text,
1559 p_table_name => l_message_list(i).table_name,
1560 p_column_name => NULL,
1561 p_column_value => NULL
1562 );
1563 END LOOP;
1564
1565 x_items.error_flag_tbl(l_index) := FND_API.g_TRUE;
1566 END IF;
1567
1568 l_index := l_update_index_tbl.NEXT(l_index);
1569 END LOOP;
1570
1571 IF (PO_LOG.d_proc) THEN
1572 PO_LOG.proc_end (d_module);
1573 END IF;
1574
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 PO_MESSAGE_S.add_exc_msg
1578 (
1579 p_pkg_name => d_pkg_name,
1580 p_procedure_name => d_api_name || '.' || d_position
1581 );
1582 RAISE;
1583 END update_master_item;
1584
1585 END PO_PDOI_ITEM_PROCESS_PVT;