[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.8.12010000.2 2008/08/04 08:38:37 rramasam 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 END LOOP;
620
621 IF (PO_LOG.d_proc) THEN
622 PO_LOG.proc_end (d_module);
623 END IF;
624
625 EXCEPTION
626 WHEN OTHERS THEN
627 PO_MESSAGE_S.add_exc_msg
628 (
629 p_pkg_name => d_pkg_name,
630 p_procedure_name => d_api_name || '.' || d_position
631 );
632 RAISE;
633 END construct_item_records;
634
635 -----------------------------------------------------------------------
636 --Start of Comments
637 --Name: derive_default_loc_attrs
638 --Function:
639 -- derive and default location related attributes read from locations
640 -- interface table; no need to derive and default line related attributes
641 -- since it is done before creating/updating items in item master
642 --Parameters:
643 --IN:
644 --IN OUT:
645 -- x_items
646 -- the item records containing line info for each location
647 --OUT:
648 --End of Comments
649 ------------------------------------------------------------------------
650 PROCEDURE derive_default_loc_attrs
651 (
652 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
653 ) IS
654
655 d_api_name CONSTANT VARCHAR2(30) := 'derive_default_loc_attrs';
656 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
657 d_position NUMBER;
658
659 BEGIN
660 d_position := 0;
661
662 IF (PO_LOG.d_proc) THEN
663 PO_LOG.proc_begin(d_module);
664 END IF;
665
666 -- derive attributes from location interface table
667 PO_PDOI_LINE_LOC_PROCESS_PVT.derive_line_locs
668 (
669 x_line_locs => x_items
670 );
671
672 d_position := 10;
673
674 -- default attributes from location interface table
675 PO_PDOI_LINE_LOC_PROCESS_PVT.default_line_locs
676 (
677 x_line_locs => x_items
678 );
679
680 IF (PO_LOG.d_proc) THEN
681 PO_LOG.proc_end (d_module);
682 END IF;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 PO_MESSAGE_S.add_exc_msg
687 (
688 p_pkg_name => d_pkg_name,
689 p_procedure_name => d_api_name || '.' || d_position
690 );
691 RAISE;
692 END derive_default_loc_attrs;
693
694 -----------------------------------------------------------------------
695 --Start of Comments
696 --Name: identify_actions
697 --Function:
698 -- This procedure is to determine for which location and organization,
699 -- we need to create/update items in item master;
700 -- To create an item, the item number must be non-empty; To update
701 -- an item, item id must be non-empty after line derivation logic;
702 -- Items can be created in 3 organizations: default inv org, default
703 -- master org and ship_to org;
704 -- Items can only be updated in default master org
705 --Parameters:
706 --IN:
707 -- p_items
708 -- the record containing item information
709 --IN OUT:
710 --OUT:
711 -- x_create_index_tbl
712 -- index of p_items for which we need to create an item; The organizations
713 -- in which items are created are specified in x_create_org_id_tbl
714 -- x_create_org_id_tbl
715 -- The organizations in which items are created in item master;
716 -- The values can be default inv org id, default master org id
717 -- or ship_to org id on each location
718 -- x_update_index_tbl
719 -- index of p_items for which items are going to be updated
720 --End of Comments
721 ------------------------------------------------------------------------
722 PROCEDURE identify_actions
723 (
724 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type,
725 x_unprocessed_row_tbl IN OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
726 x_proc_row_in_round_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
727 x_create_in_inv_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
728 x_create_in_master_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
729 x_create_in_ship_to_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE,
730 x_update_index_tbl OUT NOCOPY DBMS_SQL.NUMBER_TABLE
731 ) IS
732
733 d_api_name CONSTANT VARCHAR2(30) := 'identify_actions';
734 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
735 d_position NUMBER;
736
737 l_index NUMBER;
738 l_key PO_SESSION_GT.key%TYPE;
739
740 -- variables used to read result from po_session_gt table
741 l_index_tbl PO_TBL_NUMBER;
742 l_org_id_tbl PO_TBL_NUMBER;
743 l_item_id_tbl PO_TBL_NUMBER;
744
745 -- flag to indicate whether item exists in some orgs
746 l_exist_in_ship_to_org_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
747 l_exist_in_master_org_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
748 l_exist_in_inv_org_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
749
750 -- hashtable on item number
751 TYPE item_ref_type IS TABLE OF NUMBER INDEX BY VARCHAR2(1000);
752 l_item_ref_tbl item_ref_type;
753
754 BEGIN
755 d_position := 0;
756
757 IF (PO_LOG.d_proc) THEN
758 PO_LOG.proc_begin(d_module);
759 END IF;
760
761 -- allocate memory for nested table
762 l_exist_in_inv_org_tbl.EXTEND(x_items.rec_count);
763 l_exist_in_master_org_tbl.EXTEND(x_items.rec_count);
764 l_exist_in_ship_to_org_tbl.EXTEND(x_items.rec_count);
765
766 d_position := 10;
767
768 -- determine rows that are going to be processed in this
769 -- procedure call
770 l_index := x_unprocessed_row_tbl.FIRST;
771 WHILE (l_index IS NOT NULL)
772 LOOP
773 IF (NOT l_item_ref_tbl.EXISTS(x_items.ln_item_tbl(l_index))) THEN
774 IF (PO_LOG.d_stmt) THEN
775 PO_LOG.stmt(d_module, d_position, 'process row index in current round',
776 l_index);
777 END IF;
778
779 -- can be processed in this time of procedure call
780 x_proc_row_in_round_tbl(l_index) := l_index;
781
782 -- remove this row from unprocessed rows list
783 x_unprocessed_row_tbl.DELETE(l_index);
784
785 -- register this item_num in hashtable
786 --Bug 6956962 <start>
787 if x_items.ln_item_tbl(l_index) is not null then
788 l_item_ref_tbl(x_items.ln_item_tbl(l_index)) := l_index;
789 else
790 l_item_ref_tbl(x_items.ln_item_id_tbl(l_index)) := l_index;
791 end if;
792 --l_item_ref_tbl(x_items.ln_item_tbl(l_index)) := l_index;
793 -- Bug 6956962 <end>
794
795 -- set initial values for l_exist_in_xxx_org_tbl to 'N'
796 l_exist_in_inv_org_tbl(l_index) := FND_API.g_FALSE;
797 l_exist_in_master_org_tbl(l_index) := FND_API.g_FALSE;
798 l_exist_in_ship_to_org_tbl(l_index) := FND_API.g_FALSE;
799 END IF;
800
801 l_index := x_unprocessed_row_tbl.NEXT(l_index);
802 END LOOP;
803
804 d_position := 20;
805
806 l_key := PO_CORE_S.get_session_gt_nextval;
807 -- check whether item exists in default inv org, default master
808 -- org or ship_to org
809 FORALL i IN INDICES OF x_proc_row_in_round_tbl
810 INSERT INTO po_session_gt
811 (
812 key,
813 num1,
814 num2,
815 num3
816 )
817 SELECT
818 l_key,
819 x_proc_row_in_round_tbl(i),
820 organization_id,
821 inventory_item_id
822 FROM mtl_system_items_vl
823 WHERE organization_id IN
824 (PO_PDOI_PARAMS.g_sys.def_inv_org_id,
825 PO_PDOI_PARAMS.g_sys.master_inv_org_id,
826 x_items.ship_to_org_id_tbl(i)
827 )
828 AND (concatenated_segments = x_items.ln_item_tbl(i)
829 OR inventory_item_id = x_items.ln_item_id_tbl(i)) ; --6956962
830
831 --concatenated_segments = x_items.ln_item_tbl(i);
832
833 d_position := 30;
834
835 DELETE FROM po_session_gt
836 WHERE key = l_key
837 RETURNING num1, num2, num3 BULK COLLECT INTO
838 l_index_tbl, l_org_id_tbl, l_item_id_tbl;
839
840 FOR i IN 1..l_index_tbl.COUNT
841 LOOP
842 l_index := l_index_tbl(i);
843
844 IF (PO_LOG.d_stmt) THEN
845 PO_LOG.stmt(d_module, d_position, 'i', i);
846 PO_LOG.stmt(d_module, d_position, 'index', l_index);
847 PO_LOG.stmt(d_module, d_position, 'l_org_id_tbl(i)', l_org_id_tbl(i));
848 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl(i)', l_item_id_tbl(i));
849 PO_LOG.stmt(d_module, d_position, 'ship_to_org_id_tbl(l_index)',
850 x_items.ship_to_org_id_tbl(l_index));
851 END IF;
852
853 IF (l_org_id_tbl(i) = PO_PDOI_PARAMS.g_sys.def_inv_org_id) THEN
854 l_exist_in_inv_org_tbl(l_index) := FND_API.g_TRUE;
855 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
856 END IF;
857
858 IF (l_org_id_tbl(i) = PO_PDOI_PARAMS.g_sys.master_inv_org_id) THEN
859 l_exist_in_master_org_tbl(l_index) := FND_API.g_TRUE;
860 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
861 END IF;
862
863 IF (l_org_id_tbl(i) = x_items.ship_to_org_id_tbl(l_index)) THEN
864 l_exist_in_ship_to_org_tbl(l_index) := FND_API.g_TRUE;
865 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
866 END IF;
867 END LOOP;
868
869 d_position := 40;
870
871 -- depend on whether item exists in item master, assign actions
872 l_index := x_proc_row_in_round_tbl.FIRST;
873 WHILE (l_index IS NOT NULL)
874 LOOP
875 IF (PO_LOG.d_stmt) THEN
876 PO_LOG.stmt(d_module, d_position, 'index', l_index);
877 PO_LOG.stmt(d_module, d_position, 'exist_in_inv_org',
878 l_exist_in_inv_org_tbl(l_index));
879 PO_LOG.stmt(d_module, d_position, 'exist_in_ship_to_org',
880 l_exist_in_ship_to_org_tbl(l_index));
881 PO_LOG.stmt(d_module, d_position, 'exist_in_master_org',
882 l_exist_in_master_org_tbl(l_index));
883 PO_LOG.stmt(d_module, d_position, 'ship_to_org_id',
884 x_items.ship_to_org_id_tbl(l_index));
885 PO_LOG.stmt(d_module, d_position, 'item_id',
886 x_items.ln_item_id_tbl(l_index));
887 END IF;
888
889 IF (l_exist_in_inv_org_tbl(l_index) = FND_API.g_FALSE OR
890 l_exist_in_ship_to_org_tbl(l_index) = FND_API.g_FALSE) THEN
891 -- set whether item needs to be created in inv/master org
892 IF (l_exist_in_master_org_tbl(l_index) = FND_API.g_FALSE) THEN
893 x_create_in_master_index_tbl(l_index) := l_index;
894
895 IF (l_exist_in_inv_org_tbl(l_index) = FND_API.g_FALSE AND
896 PO_PDOI_PARAMS.g_sys.master_inv_org_id <>
897 PO_PDOI_PARAMS.g_sys.def_inv_org_id) THEN
898 x_create_in_inv_index_tbl(l_index) := l_index;
899 END IF;
900 END IF;
901
902 d_position := 50;
903
904 -- set flag for whether item needs to be created in ship_to org
905 IF (l_exist_in_ship_to_org_tbl(l_index) = FND_API.g_FALSE AND
906 x_items.ship_to_org_id_tbl(l_index) IS NOT NULL AND
907 x_items.ship_to_org_id_tbl(l_index) <> PO_PDOI_PARAMS.g_sys.master_inv_org_id AND
908 x_items.ship_to_org_id_tbl(l_index) <> PO_PDOI_PARAMS.g_sys.def_inv_org_id) THEN
909 x_create_in_ship_to_index_tbl(l_index) := l_index;
910 END IF;
911 ELSIF (x_items.ln_item_id_tbl(l_index) IS NOT NULL) THEN
912 -- for each po line, if item_id is not empty, then update item in item master
913 x_update_index_tbl(l_index) := l_index;
914 END IF;
915
916 l_index := x_proc_row_in_round_tbl.NEXT(l_index);
917 END LOOP;
918
919 IF (PO_LOG.d_proc) THEN
920 PO_LOG.proc_end (d_module);
921 END IF;
922
923 EXCEPTION
924 WHEN OTHERS THEN
925 PO_MESSAGE_S.add_exc_msg
926 (
927 p_pkg_name => d_pkg_name,
928 p_procedure_name => d_api_name || '.' || d_position
929 );
930 RAISE;
931 END identify_actions;
932
933 -----------------------------------------------------------------------
934 --Start of Comments
935 --Name: insert_master_item
936 --Function:
937 -- This procedure is to create items in item master;
938 -- we will set item_id in x_items if creation is successful;
939 -- and set error_flag to g_TRUE if failed
940 --Parameters:
941 --IN:
942 -- p_org_type
943 -- organization type in which items are going to be created;
944 -- the values can be 'INV', 'MASTER' or 'SHIP_TO'
945 -- p_index_tbl
946 -- index of x_items for which we need to create an item;
947 --IN OUT:
948 -- x_items
949 -- the record containing item information
950 --OUT:
951 --End of Comments
952 ------------------------------------------------------------------------
953 PROCEDURE insert_master_item
954 (
955 p_org_type IN VARCHAR2,
956 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
957 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
958 ) IS
959
960 d_api_name CONSTANT VARCHAR2(30) := 'insert_master_item';
961 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
962 d_position NUMBER;
963
964 l_set_process_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
965 l_index NUMBER;
966
967 -- key value used to identify records in po_session_gt
968 l_key PO_SESSION_GT.key%TYPE;
969
970 -- variables used to call Inventory API
971 l_org_id NUMBER;
972 l_err_text VARCHAR2(3000);
973 l_return_code NUMBER;
974
975 -- variables used to get processed result from interface table
976 l_index_tbl PO_TBL_NUMBER;
977 l_process_flag_tbl PO_TBL_NUMBER;
978 l_transaction_id_tbl PO_TBL_NUMBER;
979 l_item_id_tbl PO_TBL_NUMBER;
980 l_org_id_tbl PO_TBL_NUMBER;
981 l_revision_tbl PO_TBL_VARCHAR5;
982 l_category_id_tbl DBMS_SQL.NUMBER_TABLE;
983
984 -- rows that return errors when creating items
985 l_error_index_tbl DBMS_SQL.NUMBER_TABLE;
986 l_table_name_tbl PO_TBL_VARCHAR30;
987 l_message_name_tbl PO_TBL_VARCHAR30;
988 l_column_name_tbl PO_TBL_VARCHAR100;
989 BEGIN
990 d_position := 0;
991
992 IF (PO_LOG.d_proc) THEN
993 PO_LOG.proc_begin(d_module, 'p_org_type', p_org_type);
994 END IF;
995
996 -- insert data in item interface table
997 l_set_process_id_tbl.EXTEND(x_items.rec_count);
998 l_index := p_index_tbl.FIRST;
999 WHILE (l_index IS NOT NULL)
1000 LOOP
1001 l_set_process_id_tbl(l_index) :=
1002 PO_PDOI_MAINPROC_UTL_PVT.get_next_set_process_id;
1003
1004 IF (PO_LOG.d_stmt) THEN
1005 PO_LOG.stmt(d_module, d_position, 'index', l_index);
1006 PO_LOG.stmt(d_module, d_position, 'set process id',
1007 l_set_process_id_tbl(l_index));
1008 END IF;
1009
1010 l_index := p_index_tbl.NEXT(l_index);
1011 END LOOP;
1012
1013 d_position := 10;
1014
1015 FORALL i IN INDICES OF p_index_tbl
1016 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
1017 (
1018 process_flag,
1019 set_process_id,
1020 transaction_type,
1021 item_number,
1022 inventory_item_id,
1023 description,
1024 purchasing_item_flag,
1025 inventory_item_flag,
1026 purchasing_enabled_flag,
1027 primary_unit_of_measure,
1028 list_price_per_unit,
1029 market_price,
1030 un_number_id,
1031 hazard_class_id,
1032 taxable_flag,
1033 inspection_required_flag,
1034 receipt_required_flag,
1035 invoice_close_tolerance,
1036 receive_close_tolerance,
1037 days_early_receipt_allowed,
1038 days_late_receipt_allowed,
1039 enforce_ship_to_location_code,
1040 allow_substitute_receipts_flag,
1041 receiving_routing_id,
1042 qty_rcv_tolerance,
1043 qty_rcv_exception_code,
1044 receipt_days_exception_code,
1045 last_update_date,
1046 last_updated_by,
1047 last_update_login,
1048 creation_date,
1049 created_by,
1050 request_id,
1051 program_application_id,
1052 program_id,
1053 program_update_date,
1054 organization_id,
1055 attribute_category,
1056 attribute1,
1057 attribute2,
1058 attribute3,
1059 attribute4,
1060 attribute5,
1061 attribute6,
1062 attribute7,
1063 attribute8,
1064 attribute9,
1065 attribute10,
1066 attribute11,
1067 attribute12,
1068 attribute13,
1069 attribute14,
1070 attribute15,
1071 unit_weight,
1072 weight_uom_code,
1073 volume_uom_code,
1074 unit_volume,
1075 template_id
1076 )
1077 VALUES
1078 (
1079 1, -- process_flag
1080 l_set_process_id_tbl(i),
1081 'CREATE', -- transaction_type
1082 x_items.ln_item_tbl(i),
1083 x_items.ln_item_id_tbl(i),
1084 x_items.ln_item_desc_tbl(i),
1085 'Y', -- purchasing_item_flag
1086 decode(x_items.ln_template_id_tbl(i), NULL, 'Y', NULL), -- inventory_item_flag
1087 'Y', -- purchasing_enabled_flag,
1088 x_items.ln_unit_of_measure_tbl(i),
1089 x_items.ln_list_price_per_unit_tbl(i),
1090 x_items.ln_market_price_tbl(i),
1091 x_items.ln_un_number_id_tbl(i),
1092 x_items.ln_hazard_class_id_tbl(i),
1093 x_items.taxable_flag_tbl(i),
1094 x_items.inspection_required_flag_tbl(i),
1095 x_items.receipt_required_flag_tbl(i),
1096 x_items.invoice_close_tolerance_tbl(i),
1097 x_items.receive_close_tolerance_tbl(i),
1098 x_items.days_early_receipt_allowed_tbl(i),
1099 x_items.days_late_receipt_allowed_tbl(i),
1100 x_items.enforce_ship_to_loc_code_tbl(i),
1101 x_items.allow_sub_receipts_flag_tbl(i),
1102 x_items.receiving_routing_id_tbl(i),
1103 x_items.qty_rcv_tolerance_tbl(i),
1104 x_items.qty_rcv_exception_code_tbl(i),
1105 x_items.receipt_days_except_code_tbl(i),
1106 x_items.last_update_date_tbl(i),
1107 x_items.last_updated_by_tbl(i),
1108 x_items.last_update_login_tbl(i),
1109 x_items.creation_date_tbl(i),
1110 x_items.created_by_tbl(i),
1111 x_items.request_id_tbl(i),
1112 x_items.program_application_id_tbl(i),
1113 x_items.program_id_tbl(i),
1114 x_items.program_update_date_tbl(i),
1115 DECODE(p_org_type, 'MASTER', PO_PDOI_PARAMS.g_sys.master_inv_org_id,
1116 'INV', PO_PDOI_PARAMS.g_sys.def_inv_org_id,
1117 x_items.ship_to_org_id_tbl(i)), -- organization_id
1118 x_items.ln_item_attribute_category_tbl(i),
1119 x_items.ln_item_attribute1_tbl(i),
1120 x_items.ln_item_attribute2_tbl(i),
1121 x_items.ln_item_attribute3_tbl(i),
1122 x_items.ln_item_attribute4_tbl(i),
1123 x_items.ln_item_attribute5_tbl(i),
1124 x_items.ln_item_attribute6_tbl(i),
1125 x_items.ln_item_attribute7_tbl(i),
1126 x_items.ln_item_attribute8_tbl(i),
1127 x_items.ln_item_attribute9_tbl(i),
1128 x_items.ln_item_attribute10_tbl(i),
1129 x_items.ln_item_attribute11_tbl(i),
1130 x_items.ln_item_attribute12_tbl(i),
1131 x_items.ln_item_attribute13_tbl(i),
1132 x_items.ln_item_attribute14_tbl(i),
1133 x_items.ln_item_attribute15_tbl(i),
1134 x_items.ln_unit_weight_tbl(i),
1135 x_items.ln_weight_uom_code_tbl(i),
1136 x_items.ln_volume_uom_code_tbl(i),
1137 x_items.ln_unit_volume_tbl(i),
1138 x_items.ln_template_id_tbl(i)
1139 );
1140
1141 d_position := 20;
1142
1143 -- Call inventory API to handle rows in item interface table
1144 l_index := p_index_tbl.FIRST;
1145 WHILE (l_index IS NOT NULL)
1146 LOOP
1147 IF (p_org_type = 'MASTER') THEN
1148 l_org_id := PO_PDOI_PARAMS.g_sys.master_inv_org_id;
1149 ELSIF (p_org_type = 'INV') THEN
1150 l_org_id := PO_PDOI_PARAMS.g_sys.def_inv_org_id;
1151 ELSE
1152 l_org_id := x_items.ship_to_org_id_tbl(l_index);
1153 END IF;
1154
1155 -- Bug7117320: Added param names when calling the function.
1156 l_return_code := invpopif.inopinp_open_interface_process
1157 (
1158 org_id => l_org_id,
1159 all_org => 2,
1160 val_item_flag => 1,
1161 pro_item_flag => 1,
1162 del_rec_flag => 2, -- do not delete the record
1163 prog_appid => fnd_global.prog_appl_id,
1164 prog_id => -1, -- Inventory does not gather statistics when processing the records inserted into its interface table
1165 request_id => fnd_global.conc_request_id,
1166 user_id => fnd_global.user_id,
1167 login_id => fnd_global.login_id,
1168 err_text => l_err_text,
1169 xset_id => l_set_process_id_tbl(l_index),
1170 commit_flag => 2 -- no commit
1171 );
1172
1173 l_index := p_index_tbl.NEXT(l_index);
1174 END LOOP;
1175
1176 d_position := 30;
1177
1178 l_key := PO_CORE_S.get_session_gt_nextval;
1179 -- get processed result
1180 FORALL i IN INDICES OF p_index_tbl
1181 INSERT INTO po_session_gt
1182 (
1183 key,
1184 num1,
1185 num2,
1186 num3,
1187 num4,
1188 num5,
1189 num6,
1190 char1
1191 )
1192 SELECT
1193 l_key,
1194 p_index_tbl(i),
1195 set_process_id,
1196 process_flag,
1197 transaction_id,
1198 inventory_item_id,
1199 organization_id,
1200 revision
1201 FROM mtl_system_items_interface
1202 WHERE set_process_id = l_set_process_id_tbl(i);
1203
1204 d_position := 40;
1205
1206 DELETE FROM po_session_gt
1207 WHERE key = l_key
1208 RETURNING num1, num2, num3, num4, num5, num6, char1
1209 BULK COLLECT INTO
1210 l_index_tbl,
1211 l_set_process_id_tbl,
1212 l_process_flag_tbl,
1213 l_transaction_id_tbl,
1214 l_item_id_tbl,
1215 l_org_id_tbl,
1216 l_revision_tbl;
1217
1218 IF (PO_LOG.d_stmt) THEN
1219 PO_LOG.stmt(d_module, d_position, 'l_index_tbl',
1220 l_index_tbl);
1221 PO_LOG.stmt(d_module, d_position, 'l_set_process_id_tbl',
1222 l_set_process_id_tbl);
1223 PO_LOG.stmt(d_module, d_position, 'l_process_flag_tbl',
1224 l_process_flag_tbl);
1225 PO_LOG.stmt(d_module, d_position, 'l_transaction_id_tbl',
1226 l_transaction_id_tbl);
1227 PO_LOG.stmt(d_module, d_position, 'l_item_id_tbl',
1228 l_item_id_tbl);
1229 PO_LOG.stmt(d_module, d_position, 'l_org_id_tbl',
1230 l_org_id_tbl);
1231 PO_LOG.stmt(d_module, d_position, 'l_revision_tbl',
1232 l_revision_tbl);
1233 END IF;
1234
1235 FOR i IN 1..l_index_tbl.COUNT
1236 LOOP
1237 l_index := l_index_tbl(i);
1238
1239 IF (l_process_flag_tbl(i) = 7) THEN
1240 -- set item_id back
1241 x_items.ln_item_id_tbl(l_index) := l_item_id_tbl(i);
1242
1243 IF (x_items.ln_category_id_tbl(l_index) IS NOT NULL) THEN
1244 l_category_id_tbl(i) := x_items.ln_category_id_tbl(l_index);
1245 END IF;
1246 ELSE
1247 -- remember the rows with errors
1248 -- error handling will be done in batch later
1249 l_error_index_tbl(i) := l_index_tbl(i);
1250
1251 -- no need to remove records from interface tables below
1252 l_index_tbl.DELETE(i);
1253 END IF;
1254 END LOOP;
1255
1256 d_position := 50;
1257
1258 -- update category table
1259 FORALL i IN INDICES OF l_category_id_tbl
1260 UPDATE mtl_item_categories
1261 SET category_id = l_category_id_tbl(i)
1262 WHERE inventory_item_id = l_item_id_tbl(i)
1263 AND organization_id = l_org_id_tbl(i)
1264 AND category_set_id = PO_PDOI_PARAMS.g_sys.def_cat_set_id;
1265
1266 d_position := 60;
1267
1268 -- delete rows from item interface tables
1269 FORALL i IN 1..l_index_tbl.COUNT
1270 DELETE FROM mtl_system_items_interface
1271 WHERE transaction_id = l_transaction_id_tbl(i)
1272 AND set_process_id = l_set_process_id_tbl(i);
1273
1274 d_position := 70;
1275
1276 FORALL i IN 1..l_index_tbl.COUNT
1277 DELETE FROM mtl_item_categories_interface
1278 WHERE inventory_item_id = l_item_id_tbl(i)
1279 AND organization_id = l_org_id_tbl(i);
1280
1281 d_position := 80;
1282
1283 FORALL i IN 1..l_index_tbl.COUNT
1284 DELETE FROM mtl_item_revisions_interface
1285 WHERE inventory_item_id = l_item_id_tbl(i)
1286 AND organization_id = l_org_id_tbl(i)
1287 AND revision = l_revision_tbl(i);
1288
1289 d_position := 90;
1290
1291 -- handle the errors thrown by item creation
1292 -- 1. read errors from item error interface table
1293 FORALL i IN INDICES OF l_error_index_tbl
1294 INSERT INTO po_session_gt
1295 (
1296 key,
1297 num1,
1298 char1,
1299 char2,
1300 char3
1301 )
1302 SELECT
1303 l_key,
1304 l_error_index_tbl(i),
1305 table_name,
1306 message_name,
1307 column_name
1308 FROM mtl_interface_errors
1309 WHERE transaction_id = l_transaction_id_tbl(i)
1310 OR transaction_id = (
1311 SELECT transaction_id
1312 FROM mtl_item_categories_interface
1313 WHERE organization_id = l_org_id_tbl(i)
1314 AND inventory_item_id = l_item_id_tbl(i))
1315 OR transaction_id = (
1316 SELECT transaction_id
1317 FROM mtl_item_revisions_interface
1318 WHERE organization_id = l_org_id_tbl(i)
1319 AND inventory_item_id = l_item_id_tbl(i)
1320 AND revision = l_revision_tbl(i));
1321
1322 d_position := 100;
1323
1324 DELETE FROM po_session_gt
1325 WHERE key = l_key
1326 RETURNING num1, char1, char2, char3 BULK COLLECT INTO
1327 l_error_index_tbl,
1328 l_table_name_tbl,
1329 l_message_name_tbl,
1330 l_column_name_tbl;
1331
1332 -- add fatal errors to po interface error table
1333 FOR i IN 1..l_error_index_tbl.COUNT
1334 LOOP
1335 -- get index in x_items
1336 l_index := l_error_index_tbl(i);
1337
1338 IF (PO_LOG.d_stmt) THEN
1339 PO_LOG.stmt(d_module, d_position, 'add error on index',
1340 l_index);
1341 PO_LOG.stmt(d_module, d_position, 'intf line id',
1342 x_items.intf_line_id_tbl(l_index));
1343 PO_LOG.stmt(d_module, d_position, 'error message',
1344 l_message_name_tbl(i));
1345 PO_LOG.stmt(d_module, d_position, 'table name',
1346 l_table_name_tbl(i));
1347 PO_LOG.stmt(d_module, d_position, 'column name',
1348 l_column_name_tbl(i));
1349 END IF;
1350
1351 PO_PDOI_ERR_UTL.add_fatal_error
1352 (
1353 p_interface_header_id => x_items.intf_header_id_tbl(l_index),
1354 p_interface_line_id => x_items.intf_line_id_tbl(l_index),
1355 p_app_name => 'INV',
1356 p_error_message_name => l_message_name_tbl(i),
1357 p_table_name => l_table_name_tbl(i),
1358 p_column_name => l_column_name_tbl(i),
1359 p_column_value => NULL
1360 );
1361
1362 x_items.error_flag_tbl(l_index) := FND_API.g_TRUE;
1363 END LOOP;
1364
1365 IF (PO_LOG.d_proc) THEN
1366 PO_LOG.proc_end (d_module);
1367 END IF;
1368
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 PO_MESSAGE_S.add_exc_msg
1372 (
1373 p_pkg_name => d_pkg_name,
1374 p_procedure_name => d_api_name || '.' || d_position
1375 );
1376 RAISE;
1377 END insert_master_item;
1378
1379 -----------------------------------------------------------------------
1380 --Start of Comments
1381 --Name: update_master_item
1382 --Function:
1383 -- This procedure is to update items in item master if needed
1384 --Parameters:
1385 --IN:
1386 -- p_index_tbl
1387 -- index of p_items for which we need to update if needed;
1388 -- p_items
1389 -- the record containing item information
1390 --IN OUT:
1391 --OUT:
1392 --End of Comments
1393 ------------------------------------------------------------------------
1394 PROCEDURE update_master_item
1395 (
1396 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1397 x_items IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
1398 ) IS
1399
1400 d_api_name CONSTANT VARCHAR2(30) := 'update_master_item';
1401 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1402 d_position NUMBER;
1403
1404 l_index NUMBER;
1405 l_key po_session_gt.key%TYPE;
1406
1407 -- variables to store results of original values defined in item
1408 l_index_tbl PO_TBL_NUMBER;
1409 l_orig_desc_tbl PO_TBL_VARCHAR2000;
1410 l_orig_list_price_tbl PO_TBL_NUMBER;
1411
1412 l_update_index_tbl DBMS_SQL.NUMBER_TABLE;
1413
1414 -- variables to hold results from INV's API call
1415 l_inventory_item_id NUMBER;
1416 l_organization_id NUMBER;
1417 l_return_status VARCHAR2(1);
1418 l_msg_count VARCHAR2(10);
1419 l_msg_data VARCHAR2(1000);
1420 l_message_list Error_Handler.Error_Tbl_Type;
1421 BEGIN
1422 d_position := 0;
1423
1424 IF (PO_LOG.d_proc) THEN
1425 PO_LOG.proc_begin(d_module);
1426 END IF;
1427
1428 -- get original description and list price for comparison
1429 l_key := PO_CORE_S.get_session_gt_nextval;
1430 l_update_index_tbl := p_index_tbl;
1431
1432 FORALL i IN INDICES OF l_update_index_tbl
1433 INSERT INTO po_session_gt(key, num1, char1, num2)
1434 SELECT l_key,
1435 l_update_index_tbl(i),
1436 description,
1437 list_price_per_unit
1438 FROM mtl_system_items
1439 WHERE inventory_item_id = x_items.ln_item_id_tbl(i)
1440 AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
1441
1442 d_position := 10;
1443
1444 DELETE FROM po_session_gt
1445 WHERE key = l_key
1446 RETURNING num1, char1, num2 BULK COLLECT INTO
1447 l_index_tbl, l_orig_desc_tbl, l_orig_list_price_tbl;
1448
1449 IF (PO_LOG.d_stmt) THEN
1450 PO_LOG.stmt(d_module, d_position, 'l_index_tbl',
1451 l_index_tbl);
1452 PO_LOG.stmt(d_module, d_position, 'l_orig_desc_tbl',
1453 l_orig_desc_tbl);
1454 PO_LOG.stmt(d_module, d_position, 'l_orig_list_price_tbl',
1455 l_orig_list_price_tbl);
1456 END IF;
1457
1458 d_position := 20;
1459
1460 FOR i IN 1..l_index_tbl.COUNT
1461 LOOP
1462 l_index := l_index_tbl(i);
1463
1464 IF (PO_LOG.d_stmt) THEN
1465 PO_LOG.stmt(d_module, d_position, 'i',
1466 i);
1467 PO_LOG.stmt(d_module, d_position, 'l_index',
1468 l_index);
1469 PO_LOG.stmt(d_module, d_position, 'x_items.ln_item_desc_tbl(l_index)',
1470 x_items.ln_item_desc_tbl(l_index));
1471 PO_LOG.stmt(d_module, d_position, 'x_items.ln_list_price_per_unit_tbl(l_index)',
1472 x_items.ln_list_price_per_unit_tbl(l_index));
1473 END IF;
1474
1475 d_position := 30;
1476
1477 IF (x_items.ln_item_desc_tbl(l_index) IS NULL AND
1478 x_items.ln_list_price_per_unit_tbl(l_index) IS NULL) THEN
1479 -- nothing to update
1480 l_update_index_tbl.DELETE(l_index);
1481 ELSIF (x_items.ln_item_desc_tbl(l_index) = l_orig_desc_tbl(i) AND
1482 x_items.ln_list_price_per_unit_tbl(l_index) = l_orig_list_price_tbl(i)) THEN
1483 -- no change, no need to update
1484 l_update_index_tbl.DELETE(l_index);
1485 ELSE
1486 NULL;
1487 END IF;
1488 END LOOP;
1489
1490 d_position := 40;
1491
1492 -- call Inventory Team's API to update item description and list price if needed
1493 l_index := l_update_index_tbl.FIRST;
1494 WHILE (l_index IS NOT NULL)
1495 LOOP
1496 EGO_ITEM_PUB.Process_Item
1497 (
1498 p_api_version => 1.0,
1499 p_init_msg_list => FND_API.g_TRUE,
1500 p_commit => FND_API.g_TRUE,
1501 p_Transaction_Type => 'UPDATE',
1502 p_Inventory_Item_Id => x_items.ln_item_id_tbl(l_index),
1503 p_Organization_Id => PO_PDOI_PARAMS.g_sys.def_inv_org_id,
1504 p_description => NVL(x_items.ln_item_desc_tbl(l_index), EGO_ITEM_PUB.G_MISS_CHAR),
1505 p_list_price_per_unit => NVL(x_items.ln_list_price_per_unit_tbl(l_index), EGO_ITEM_PUB.G_MISS_NUM),
1506 p_Item_Number => x_items.ln_item_tbl(l_index),
1507 x_Inventory_Item_Id => l_inventory_item_id,
1508 x_Organization_Id => l_organization_id,
1509 x_return_status => l_return_status,
1510 x_msg_count => l_msg_count,
1511 x_msg_data => l_msg_data
1512 );
1513
1514 IF (PO_LOG.d_stmt) THEN
1515 PO_LOG.stmt(d_module, d_position, 'return status for item update',
1516 l_return_status);
1517 END IF;
1518
1519 d_position := 50;
1520
1521 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1522 -- error handling
1523 Error_Handler.GET_MESSAGE_LIST
1524 (
1525 x_message_list => l_message_list
1526 );
1527 IF (PO_LOG.d_stmt) THEN
1528 PO_LOG.stmt(d_module, d_position, 'count of error message',
1529 l_message_list.COUNT);
1530 END IF;
1531
1532 d_position := 60;
1533 FOR i IN 1..l_message_list.COUNT
1534 LOOP
1535 IF (PO_LOG.d_stmt) THEN
1536 PO_LOG.stmt(d_module, d_position, 'index', i);
1537 PO_LOG.stmt(d_module, d_position, 'intf header id',
1538 x_items.intf_header_id_tbl(l_index));
1539 PO_LOG.stmt(d_module, d_position, 'intf line id',
1540 x_items.intf_line_id_tbl(l_index));
1541 PO_LOG.stmt(d_module, d_position, 'message text',
1542 l_message_list(i).message_text);
1543 PO_LOG.stmt(d_module, d_position, 'table name',
1544 l_message_list(i).table_name);
1545 END IF;
1546
1547 PO_PDOI_ERR_UTL.add_fatal_error
1548 (
1549 p_interface_header_id => x_items.intf_header_id_tbl(l_index),
1550 p_interface_line_id => x_items.intf_line_id_tbl(l_index),
1551 p_app_name => 'INV',
1552 p_error_message_name => l_message_list(i).message_text,
1553 p_table_name => l_message_list(i).table_name,
1554 p_column_name => NULL,
1555 p_column_value => NULL
1556 );
1557 END LOOP;
1558
1559 x_items.error_flag_tbl(l_index) := FND_API.g_TRUE;
1560 END IF;
1561
1562 l_index := l_update_index_tbl.NEXT(l_index);
1563 END LOOP;
1564
1565 IF (PO_LOG.d_proc) THEN
1566 PO_LOG.proc_end (d_module);
1567 END IF;
1568
1569 EXCEPTION
1570 WHEN OTHERS THEN
1571 PO_MESSAGE_S.add_exc_msg
1572 (
1573 p_pkg_name => d_pkg_name,
1574 p_procedure_name => d_api_name || '.' || d_position
1575 );
1576 RAISE;
1577 END update_master_item;
1578
1579 END PO_PDOI_ITEM_PROCESS_PVT;