[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_LINE_LOC_PROCESS_PVT
Source
1 PACKAGE BODY PO_PDOI_LINE_LOC_PROCESS_PVT AS
2 /* $Header: PO_PDOI_LINE_LOC_PROCESS_PVT.plb 120.26.12010000.2 2008/08/04 08:38:50 rramasam ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_LINE_LOC_PROCESS_PVT');
6
7 --------------------------------------------------------------------------
8 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
9 --------------------------------------------------------------------------
10 PROCEDURE derive_line_loc_id
11 (
12 p_key IN po_session_gt.key%TYPE,
13 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
14 p_po_line_id_tbl IN PO_TBL_NUMBER,
15 p_shipment_num_tbl IN PO_TBL_NUMBER,
16 x_line_loc_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
17 );
18
19 PROCEDURE derive_ship_to_org_id
20 (
21 p_key IN po_session_gt.key%TYPE,
22 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
23 p_ship_to_org_code_tbl IN PO_TBL_VARCHAR5,
24 x_ship_to_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
25 );
26
27 PROCEDURE derive_receiving_routing_id
28 (
29 p_key IN po_session_gt.key%TYPE,
30 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
31 p_receiving_routing_tbl IN PO_TBL_VARCHAR30,
32 x_receiving_routing_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
33 );
34
35 PROCEDURE derive_tax_name
36 (
37 p_key IN po_session_gt.key%TYPE,
38 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
39 p_tax_code_id_tbl IN PO_TBL_NUMBER,
40 x_tax_name_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
41 );
42
43 PROCEDURE default_locs_for_spo
44 (
45 p_key IN po_session_gt.key%TYPE,
46 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
47 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
48 );
49
50 PROCEDURE default_locs_for_blanket
51 (
52 p_key IN po_session_gt.key%TYPE,
53 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
54 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
55 );
56
57 PROCEDURE default_locs_for_quotation
58 (
59 p_key IN po_session_gt.key%TYPE,
60 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
61 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
62 );
63
64 PROCEDURE default_inspect_required_flag
65 (
66 p_key IN po_session_gt.key%TYPE,
67 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
68 p_item_id_tbl IN PO_TBL_NUMBER,
69 x_inspection_required_flag_tbl IN OUT NOCOPY PO_TBL_VARCHAR1
70 );
71
72 PROCEDURE default_ship_to_org_id
73 (
74 p_key IN po_session_gt.key%TYPE,
75 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
76 p_ship_to_loc_id_tbl IN PO_TBL_NUMBER,
77 x_ship_to_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
78 );
79
80 PROCEDURE default_close_tolerances
81 (
82 p_key IN po_session_gt.key%TYPE,
83 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
84 p_item_id_tbl IN PO_TBL_NUMBER,
85 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
86 p_line_type_id_tbl IN PO_TBL_NUMBER,
87 x_invoice_close_tolerance_tbl IN OUT NOCOPY PO_TBL_NUMBER,
88 x_receive_close_tolerance_tbl IN OUT NOCOPY PO_TBL_NUMBER
89 );
90
91 PROCEDURE default_invoice_match_options
92 (
93 p_key IN po_session_gt.key%TYPE,
94 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
95 p_vendor_id_tbl IN PO_TBL_NUMBER,
96 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
97 x_match_option_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
98 );
99
100 PROCEDURE default_accrue_on_receipt_flag
101 (
102 p_key IN po_session_gt.key%TYPE,
103 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
104 p_item_id_tbl IN PO_TBL_NUMBER,
105 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
106 p_receipt_required_flag_tbl IN PO_TBL_VARCHAR1,
107 x_accrue_on_receipt_flag_tbl IN OUT NOCOPY PO_TBL_VARCHAR1
108 );
109
110 PROCEDURE default_outsourced_assembly
111 (
112 p_item_id_tbl IN PO_TBL_NUMBER,
113 p_ship_to_organization_id_tbl IN PO_TBL_NUMBER,
114 x_outsourced_assembly_tbl IN OUT NOCOPY PO_TBL_NUMBER
115 );
116
117 PROCEDURE default_secondary_unit_of_meas
118 (
119 p_key IN po_session_gt.key%TYPE,
120 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
121 p_item_id_tbl IN PO_TBL_NUMBER,
122 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
123 x_secondary_unit_of_meas_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
124 );
125
126 PROCEDURE populate_error_flag
127 (
128 x_results IN po_validation_results_type,
129 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
130 );
131
132 ------------------------------------------------------------------------
133 ---------------------- PUBLIC PROCEDURES -------------------------------
134 ------------------------------------------------------------------------
135
136 -----------------------------------------------------------------------
137 --Start of Comments
138 --Name: open_line_locs
139 --Function:
140 -- Open cursor for query.
141 -- This query retrieves the line location attributes and related header
142 -- and line attributes for processing
143 --Parameters:
144 --IN:
145 -- p_max_intf_line_loc_id
146 -- maximal interface_line_location_id processed so far
147 -- The query will only retrieve the location records which have
148 -- not been processed
149 --IN OUT:
150 -- x_line_locs_csr
151 -- cursor variable to hold pointer to current processing row in the result
152 -- set returned by the query
153 --OUT:
154 --End of Comments
155 ------------------------------------------------------------------------
156 PROCEDURE open_line_locs
157 (
158 p_max_intf_line_loc_id IN NUMBER,
159 x_line_locs_csr OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
160 ) IS
161
162 d_api_name CONSTANT VARCHAR2(30) := 'open_line_locs';
163 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
164 d_position NUMBER;
165
166 BEGIN
167 d_position := 0;
168
169 IF (PO_LOG.d_proc) THEN
170 PO_LOG.proc_begin(d_module, 'p_max_intf_line_loc_id', p_max_intf_line_loc_id);
171 END IF;
172
173 OPEN x_line_locs_csr FOR
174 SELECT /*+ NO_INDEX(DRAFT_LINES PO_LINES_DRAFT_N0) */
175 intf_locs.interface_line_location_id,
176 intf_locs.interface_line_id,
177 intf_locs.interface_header_id,
178 intf_locs.shipment_num,
179 intf_locs.shipment_type,
180 intf_locs.line_location_id,
181 intf_locs.ship_to_organization_code,
182 intf_locs.ship_to_organization_id,
183 intf_locs.ship_to_location,
184 intf_locs.ship_to_location_id,
185 intf_locs.payment_terms,
186 intf_locs.terms_id,
187 intf_locs.receiving_routing,
188 intf_locs.receiving_routing_id,
189 intf_locs.inspection_required_flag,
190 intf_locs.receipt_required_flag,
191 intf_locs.price_override,
192 intf_locs.qty_rcv_tolerance,
193 intf_locs.qty_rcv_exception_code,
194 intf_locs.enforce_ship_to_location_code,
195 intf_locs.allow_substitute_receipts_flag,
196 intf_locs.days_early_receipt_allowed,
197 intf_locs.days_late_receipt_allowed,
198 intf_locs.receipt_days_exception_code,
199 intf_locs.invoice_close_tolerance,
200 intf_locs.receive_close_tolerance,
201 intf_locs.accrue_on_receipt_flag,
202 intf_locs.firm_flag,
203 intf_locs.fob,
204 intf_locs.freight_carrier,
205 intf_locs.freight_terms,
206 intf_locs.need_by_date,
207 intf_locs.promised_date,
208 intf_locs.quantity,
209 intf_locs.start_date,
210 intf_locs.end_date,
211 intf_locs.note_to_receiver,
212 intf_locs.price_discount,
213 intf_locs.tax_code_id,
214 intf_locs.tax_name,
215 intf_locs.secondary_quantity,
216 intf_locs.secondary_unit_of_measure,
217 intf_locs.preferred_grade,
218 intf_locs.unit_of_measure,
219 intf_locs.value_basis,
220 intf_locs.matching_basis,
221
222 -- attributes in txn table but not in intf table
223 NULL, -- outsourced_assembly - no such column in intf table
224 NULL, -- invoice match option - no such column in intf table
225 NULL, -- txn_flow_header_id
226 NULL, -- tax_attribute_update_code
227
228 -- standard who columns
229 intf_locs.last_updated_by,
230 intf_locs.last_update_date,
231 intf_locs.last_update_login,
232 intf_locs.creation_date,
233 intf_locs.created_by,
234 intf_locs.request_id,
235 intf_locs.program_application_id,
236 intf_locs.program_id,
237 intf_locs.program_update_date,
238
239 -- attributes read from the line record
240 draft_lines.po_line_id,
241 draft_lines.item_id,
242 draft_lines.order_type_lookup_code,
243 intf_lines.action,
244 draft_lines.unit_price,
245 draft_lines.line_type_id,
246 draft_lines.unit_meas_lookup_code,
247 draft_lines.closed_code,
248 draft_lines.purchase_basis,
249 draft_lines.matching_basis,
250 draft_lines.item_revision,
251 draft_lines.expiration_date,
252 draft_lines.government_context,
253 draft_lines.closed_reason,
254 draft_lines.closed_date,
255 draft_lines.closed_by,
256 draft_lines.from_header_id,
257 draft_lines.from_line_id,
258 draft_lines.price_break_lookup_code, -- bug5016163
259 -- attributes read from the header record
260 intf_headers.draft_id,
261 intf_headers.po_header_id,
262 NVL(draft_headers.ship_to_location_id, txn_headers.ship_to_location_id),
263 NVL(draft_headers.vendor_id, txn_headers.vendor_id),
264 NVL(draft_headers.vendor_site_id, txn_headers.vendor_site_id),
265 NVL(draft_headers.terms_id, txn_headers.terms_id),
266 NVL(draft_headers.fob_lookup_code, txn_headers.fob_lookup_code),
267 NVL(draft_headers.ship_via_lookup_code, txn_headers.ship_via_lookup_code),
268 NVL(draft_headers.freight_terms_lookup_code, txn_headers.freight_terms_lookup_code),
269 NVL(draft_headers.approved_flag, txn_headers.approved_flag),
270 NVL(draft_headers.start_date, txn_headers.start_date),
271 NVL(draft_headers.end_date, txn_headers.end_date),
272 NVL(draft_headers.style_id, txn_headers.style_id),
273
274 -- set initial value for error_flag
275 FND_API.g_FALSE
276 FROM po_line_locations_interface intf_locs,
277 po_lines_interface intf_lines,
278 po_headers_interface intf_headers,
279 po_lines_draft_all draft_lines,
280 po_headers_draft_all draft_headers,
281 po_headers_all txn_headers
282 WHERE intf_locs.interface_line_id = intf_lines.interface_line_id
283 AND intf_lines.interface_header_id = intf_headers.interface_header_id
284 AND intf_lines.po_line_id = draft_lines.po_line_id
285 AND intf_headers.draft_id = draft_lines.draft_id
286 AND draft_lines.po_header_id = draft_headers.po_header_id(+)
287 AND draft_lines.draft_id = draft_headers.draft_id(+)
288 AND draft_lines.po_header_id = txn_headers.po_header_id(+)
289 AND intf_locs.processing_id = PO_PDOI_PARAMS.g_processing_id
290 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
291 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
292 AND intf_locs.interface_line_location_id > p_max_intf_line_loc_id
293 AND NVL(intf_lines.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
294 <> PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
295 ORDER BY 1;
296
297 IF (PO_LOG.d_proc) THEN
298 PO_LOG.proc_end (d_module);
299 END IF;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 PO_MESSAGE_S.add_exc_msg
304 (
305 p_pkg_name => d_pkg_name,
306 p_procedure_name => d_api_name || '.' || d_position
307 );
308 RAISE;
309 END open_line_locs;
310
311 -----------------------------------------------------------------------
312 --Start of Comments
313 --Name: fetch_line_locs
314 --Function:
315 -- fetch results in batch
316 --Parameters:
317 --IN:
318 --IN OUT:
319 --x_line_locs_csr
320 -- cursor variable that hold pointers to currently processing row
321 --x_line_locs
322 -- record variable to hold line location info within a batch
323 --OUT:
324 --End of Comments
325 ------------------------------------------------------------------------
326 PROCEDURE fetch_line_locs
327 (
328 x_line_locs_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
329 x_line_locs OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
330 ) IS
331
332 d_api_name CONSTANT VARCHAR2(30) := 'fetch_line_locs';
333 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
334 d_position NUMBER;
335
336 BEGIN
337 d_position := 0;
338
339 IF (PO_LOG.d_proc) THEN
340 PO_LOG.proc_begin(d_module);
341 END IF;
342
343 FETCH x_line_locs_csr BULK COLLECT INTO
344 x_line_locs.intf_line_loc_id_tbl,
345 x_line_locs.intf_line_id_tbl,
346 x_line_locs.intf_header_id_tbl,
347 x_line_locs.shipment_num_tbl,
348 x_line_locs.shipment_type_tbl,
349 x_line_locs.line_loc_id_tbl,
350 x_line_locs.ship_to_org_code_tbl,
351 x_line_locs.ship_to_org_id_tbl,
352 x_line_locs.ship_to_loc_tbl,
353 x_line_locs.ship_to_loc_id_tbl,
354 x_line_locs.payment_terms_tbl,
355 x_line_locs.terms_id_tbl,
356 x_line_locs.receiving_routing_tbl,
357 x_line_locs.receiving_routing_id_tbl,
358 x_line_locs.inspection_required_flag_tbl,
359 x_line_locs.receipt_required_flag_tbl,
360 x_line_locs.price_override_tbl,
361 x_line_locs.qty_rcv_tolerance_tbl,
362 x_line_locs.qty_rcv_exception_code_tbl,
363 x_line_locs.enforce_ship_to_loc_code_tbl,
364 x_line_locs.allow_sub_receipts_flag_tbl,
365 x_line_locs.days_early_receipt_allowed_tbl,
366 x_line_locs.days_late_receipt_allowed_tbl,
367 x_line_locs.receipt_days_except_code_tbl,
368 x_line_locs.invoice_close_tolerance_tbl,
369 x_line_locs.receive_close_tolerance_tbl,
370 x_line_locs.accrue_on_receipt_flag_tbl,
371 x_line_locs.firm_flag_tbl,
372 x_line_locs.fob_tbl,
373 x_line_locs.freight_carrier_tbl,
374 x_line_locs.freight_term_tbl,
375 x_line_locs.need_by_date_tbl,
376 x_line_locs.promised_date_tbl,
377 x_line_locs.quantity_tbl,
378 x_line_locs.start_date_tbl,
379 x_line_locs.end_date_tbl,
380 x_line_locs.note_to_receiver_tbl,
381 x_line_locs.price_discount_tbl,
382 x_line_locs.tax_code_id_tbl,
383 x_line_locs.tax_name_tbl,
384 x_line_locs.secondary_quantity_tbl,
385 x_line_locs.secondary_unit_of_meas_tbl,
386 x_line_locs.preferred_grade_tbl,
387 x_line_locs.unit_of_measure_tbl,
388 x_line_locs.value_basis_tbl,
389 x_line_locs.matching_basis_tbl,
390
391 -- attributes exist in txn table but not in intf table
392 x_line_locs.outsourced_assembly_tbl,
393 x_line_locs.match_option_tbl,
394 x_line_locs.txn_flow_header_id_tbl,
395 x_line_locs.tax_attribute_update_code_tbl,
396
397 -- standard who columns
398 x_line_locs.last_updated_by_tbl,
399 x_line_locs.last_update_date_tbl,
400 x_line_locs.last_update_login_tbl,
401 x_line_locs.creation_date_tbl,
402 x_line_locs.created_by_tbl,
403 x_line_locs.request_id_tbl,
404 x_line_locs.program_application_id_tbl,
405 x_line_locs.program_id_tbl,
406 x_line_locs.program_update_date_tbl,
407
408 -- attributes read from the line record
409 x_line_locs.ln_po_line_id_tbl,
410 x_line_locs.ln_item_id_tbl,
411 x_line_locs.ln_order_type_lookup_code_tbl,
412 x_line_locs.ln_action_tbl,
413 x_line_locs.ln_unit_price_tbl,
414 x_line_locs.ln_line_type_id_tbl,
415 x_line_locs.ln_unit_of_measure_tbl,
416 x_line_locs.ln_closed_code_tbl,
417 x_line_locs.ln_purchase_basis_tbl,
418 x_line_locs.ln_matching_basis_tbl,
419 x_line_locs.ln_item_revision_tbl,
420 x_line_locs.ln_expiration_date_tbl,
421 x_line_locs.ln_government_context_tbl,
422 x_line_locs.ln_closed_reason_tbl,
423 x_line_locs.ln_closed_date_tbl,
424 x_line_locs.ln_closed_by_tbl,
425 x_line_locs.ln_from_header_id_tbl,
426 x_line_locs.ln_from_line_id_tbl,
427 x_line_locs.ln_price_break_lookup_code_tbl,
428
429 -- attributes read from the header record
430 x_line_locs.draft_id_tbl,
431 x_line_locs.hd_po_header_id_tbl,
432 x_line_locs.hd_ship_to_loc_id_tbl,
433 x_line_locs.hd_vendor_id_tbl,
434 x_line_locs.hd_vendor_site_id_tbl,
435 x_line_locs.hd_terms_id_tbl,
436 x_line_locs.hd_fob_tbl,
437 x_line_locs.hd_freight_carrier_tbl,
438 x_line_locs.hd_freight_term_tbl,
439 x_line_locs.hd_approved_flag_tbl,
440 x_line_locs.hd_effective_date_tbl,
441 x_line_locs.hd_expiration_date_tbl,
442 x_line_locs.hd_style_id_tbl,
443
447
444 -- set initial value for error_flag
445 x_line_locs.error_flag_tbl
446 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
448 IF (PO_LOG.d_proc) THEN
449 PO_LOG.proc_end (d_module);
450 END IF;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 PO_MESSAGE_S.add_exc_msg
455 (
456 p_pkg_name => d_pkg_name,
457 p_procedure_name => d_api_name || '.' || d_position
458 );
459 RAISE;
460 END fetch_line_locs;
461
462 -----------------------------------------------------------------------
463 --Start of Comments
464 --Name: derive_line_locs
465 --Function:
466 -- perform derive logic on line location records read in one batch;
467 -- derivation errors are handled all together after the
468 -- derivation logic
469 -- The derived attributes include:
470 -- line_location_id, ship_to_organization_id,
471 -- ship_to_location_id, terms_id
472 -- receiving_routing_id
473 --Parameters:
474 --IN:
475 --IN OUT:
476 --x_line_locs
477 -- variable to hold all the line location attribute values in one batch;
478 -- derivation source and result are both placed inside the variable
479 --OUT:
480 --End of Comments
481 ------------------------------------------------------------------------
482 PROCEDURE derive_line_locs
483 (
484 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
485 ) IS
486
487 d_api_name CONSTANT VARCHAR2(30) := 'derive_line_locs';
488 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
489 d_position NUMBER;
490
491 -- key of temp table used to identify the derived result
492 l_key po_session_gt.key%TYPE;
493
494 -- table used to save the index of the each row
495 l_index_tbl DBMS_SQL.NUMBER_TABLE;
496 BEGIN
497 d_position := 0;
498
499 IF (PO_LOG.d_proc) THEN
500 PO_LOG.proc_begin(d_module, 'count', x_line_locs.rec_count);
501 END IF;
502
503 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_DERIVE);
504
505 -- assign a new key
506 l_key := PO_CORE_S.get_session_gt_nextval;
507
508 -- initialize table containing the row number
509 PO_PDOI_UTL.generate_ordered_num_list
510 (
511 p_size => x_line_locs.rec_count,
512 x_num_list => l_index_tbl
513 );
514
515 d_position := 10;
516
517 -- derive line_location_id from shipment_num
518 derive_line_loc_id
519 (
520 p_key => l_key,
521 p_index_tbl => l_index_tbl,
522 p_po_line_id_tbl => x_line_locs.ln_po_line_id_tbl,
523 p_shipment_num_tbl => x_line_locs.shipment_num_tbl,
524 x_line_loc_id_tbl => x_line_locs.line_loc_id_tbl
525 );
526
527 d_position := 20;
528
529 -- derive ship_to_organization_id from ship_to_organization_code
530 derive_ship_to_org_id
531 (
532 p_key => l_key,
533 p_index_tbl => l_index_tbl,
534 p_ship_to_org_code_tbl => x_line_locs.ship_to_org_code_tbl,
535 x_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl
536 );
537
538 d_position := 30;
539
540 -- derive ship_to_location_id from ship_to_location_code
541 PO_PDOI_HEADER_PROCESS_PVT.derive_location_id
542 (
543 p_key => l_key,
544 p_index_tbl => l_index_tbl,
545 p_location_tbl => x_line_locs.ship_to_loc_tbl,
546 p_location_type => 'SHIP_TO', --bug6963861
547 x_location_id_tbl => x_line_locs.ship_to_loc_id_tbl
548 );
549
550 d_position := 40;
551
552 -- derive terms_id from payment_terms
553 PO_PDOI_HEADER_PROCESS_PVT.derive_terms_id
554 (
555 p_key => l_key,
556 p_index_tbl => l_index_tbl,
557 p_payment_terms_tbl => x_line_locs.payment_terms_tbl,
558 x_terms_id_tbl => x_line_locs.terms_id_tbl
559 );
560
561 d_position := 50;
562
563 -- derive receving_id from receiving_routing
564 derive_receiving_routing_id
565 (
566 p_key => l_key,
567 p_index_tbl => l_index_tbl,
568 p_receiving_routing_tbl => x_line_locs.receiving_routing_tbl,
569 x_receiving_routing_id_tbl => x_line_locs.receiving_routing_id_tbl
570 );
571
572 d_position := 60;
573
574 -- derive tax_name from tax_code_id
575 IF (PO_PDOI_PARAMS.g_request.document_type =
576 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
577 derive_tax_name
578 (
579 p_key => l_key,
580 p_index_tbl => l_index_tbl,
581 p_tax_code_id_tbl => x_line_locs.tax_code_id_tbl,
582 x_tax_name_tbl => x_line_locs.tax_name_tbl
583 );
584 END IF;
585
586 d_position := 70;
587
588 -- handle derivation errors
589 FOR i IN 1..x_line_locs.rec_count
590 LOOP
591 IF (PO_LOG.d_stmt) THEN
592 PO_LOG.stmt(d_module, d_position, 'index', i);
593 END IF;
594
595 -- check derivation error on ship_to_organziation_id
596 IF (x_line_locs.ship_to_org_code_tbl(i) IS NOT NULL AND
600 PO_LOG.stmt(d_module, d_position, 'ship_to org', x_line_locs.ship_to_org_code_tbl(i));
597 x_line_locs.ship_to_org_id_tbl(i) IS NULL) THEN
598 IF (PO_LOG.d_stmt) THEN
599 PO_LOG.stmt(d_module, d_position, 'ship_to org id derivation failed');
601 END IF;
602
603 PO_PDOI_ERR_UTL.add_fatal_error
604 (
605 p_interface_header_id => x_line_locs.intf_header_id_tbl(i),
606 p_interface_line_id => x_line_locs.intf_line_id_tbl(i),
607 p_interface_line_location_id => x_line_locs.intf_line_loc_id_tbl(i),
608 p_error_message_name => 'PO_PDOI_DERV_ERROR',
609 p_table_name => 'PO_LINE_LOCATIONS_INTERFACE',
610 p_column_name => 'SHIP_TO_ORGANIZATION_ID',
611 p_column_value => x_line_locs.ship_to_org_id_tbl(i),
612 p_token1_name => 'COLUMN_NAME',
613 p_token1_value => 'SHIP_TO_ORGANIZATION_CODE',
614 p_token2_name => 'VALUE',
615 p_token2_value => x_line_locs.ship_to_org_code_tbl(i),
616 p_validation_id => PO_VAL_CONSTANTS.c_ship_to_org_code_derv,
617 p_line_locs => x_line_locs
618 );
619
620 x_line_locs.error_flag_tbl(i) := FND_API.g_TRUE;
621 END IF;
622
623 -- check derivation error for ship_to_location_id
624 IF (x_line_locs.ship_to_loc_tbl(i) IS NOT NULL AND
625 x_line_locs.ship_to_loc_id_tbl(i) IS NULL) THEN
626 IF (PO_LOG.d_stmt) THEN
627 PO_LOG.stmt(d_module, d_position, 'ship_to loc id derivation failed');
628 PO_LOG.stmt(d_module, d_position, 'ship_to loc', x_line_locs.ship_to_loc_tbl(i));
629 END IF;
630
631 PO_PDOI_ERR_UTL.add_fatal_error
632 (
633 p_interface_header_id => x_line_locs.intf_header_id_tbl(i),
634 p_interface_line_id => x_line_locs.intf_line_id_tbl(i),
635 p_interface_line_location_id => x_line_locs.intf_line_loc_id_tbl(i),
636 p_error_message_name => 'PO_PDOI_DERV_ERROR',
637 p_table_name => 'PO_LINE_LOCATIONS_INTERFACE',
638 p_column_name => 'SHIP_TO_LOCATION_ID',
639 p_column_value => x_line_locs.ship_to_loc_id_tbl(i),
640 p_token1_name => 'COLUMN_NAME',
641 p_token1_value => 'SHIP_TO_LOCATION_CODE',
642 p_token2_name => 'VALUE',
643 p_token2_value => x_line_locs.ship_to_loc_tbl(i),
644 p_validation_id => PO_VAL_CONSTANTS.c_ship_to_location_derv,
645 p_line_locs => x_line_locs
646 );
647
648 x_line_locs.error_flag_tbl(i) := FND_API.g_TRUE;
649 END IF;
650
651 -- check derivation error for terms_id
652 IF (x_line_locs.payment_terms_tbl(i) IS NOT NULL AND
653 x_line_locs.terms_id_tbl(i) IS NULL) THEN
654 IF (PO_LOG.d_stmt) THEN
655 PO_LOG.stmt(d_module, d_position, 'terms id derivation failed');
656 PO_LOG.stmt(d_module, d_position, 'payment terms', x_line_locs.payment_terms_tbl(i));
657 END IF;
658
659 PO_PDOI_ERR_UTL.add_fatal_error
660 (
661 p_interface_header_id => x_line_locs.intf_header_id_tbl(i),
662 p_interface_line_id => x_line_locs.intf_line_id_tbl(i),
663 p_interface_line_location_id => x_line_locs.intf_line_loc_id_tbl(i),
664 p_error_message_name => 'PO_PDOI_DERV_ERROR',
665 p_table_name => 'PO_LINE_LOCATIONS_INTERFACE',
666 p_column_name => 'TERMS_ID',
667 p_column_value => x_line_locs.terms_id_tbl(i),
668 p_token1_name => 'COLUMN_NAME',
669 p_token1_value => 'PAYMENT_TERMS',
670 p_token2_name => 'VALUE',
671 p_token2_value => x_line_locs.payment_terms_tbl(i)
672 );
673
674 x_line_locs.error_flag_tbl(i) := FND_API.g_TRUE;
675 END IF;
676
677 -- check derivation error for receiving_routing_id
678 IF (x_line_locs.receiving_routing_tbl(i) IS NOT NULL AND
679 x_line_locs.receiving_routing_id_tbl(i) IS NULL) THEN
680 IF (PO_LOG.d_stmt) THEN
681 PO_LOG.stmt(d_module, d_position, 'routing id derivation failed');
682 PO_LOG.stmt(d_module, d_position, 'routing', x_line_locs.receiving_routing_tbl(i));
683 END IF;
684
685 PO_PDOI_ERR_UTL.add_fatal_error
686 (
687 p_interface_header_id => x_line_locs.intf_header_id_tbl(i),
688 p_interface_line_id => x_line_locs.intf_line_id_tbl(i),
689 p_interface_line_location_id => x_line_locs.intf_line_loc_id_tbl(i),
690 p_error_message_name => 'PO_PDOI_DERV_ERROR',
691 p_table_name => 'PO_LINE_LOCATIONS_INTERFACE',
692 p_column_name => 'RECEIVING_ROUTING_ID',
693 p_column_value => x_line_locs.receiving_routing_id_tbl(i),
694 p_token1_name => 'COLUMN_NAME',
695 p_token1_value => 'RECEIVING_ROUTING',
696 p_token2_name => 'VALUE',
697 p_token2_value => x_line_locs.receiving_routing_tbl(i)
698 );
699
700 x_line_locs.error_flag_tbl(i) := FND_API.g_TRUE;
701 END IF;
702
703 -- check derivation error for tax_name
704 IF (PO_PDOI_PARAMS.g_request.document_type =
708 IF (PO_LOG.d_stmt) THEN
705 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
706 IF (x_line_locs.tax_code_id_tbl(i) IS NOT NULL AND
707 x_line_locs.tax_name_tbl(i) IS NULL) THEN
709 PO_LOG.stmt(d_module, d_position, 'tax name derivation failed');
710 PO_LOG.stmt(d_module, d_position, 'tax code id',
711 x_line_locs.tax_code_id_tbl(i));
712 END IF;
713
714 PO_PDOI_ERR_UTL.add_fatal_error
715 (
716 p_interface_header_id => x_line_locs.intf_header_id_tbl(i),
717 p_interface_line_id => x_line_locs.intf_line_id_tbl(i),
718 p_interface_line_location_id => x_line_locs.intf_line_loc_id_tbl(i),
719 p_error_message_name => 'PO_PDOI_DERV_ERROR',
720 p_table_name => 'PO_LINE_LOCATIONS_INTERFACE',
721 p_column_name => 'TAX_NAME',
722 p_column_value => x_line_locs.tax_name_tbl(i),
723 p_token1_name => 'COLUMN_NAME',
724 p_token1_value => 'TAX_CODE_ID',
725 p_token2_name => 'VALUE',
726 p_token2_value => x_line_locs.tax_code_id_tbl(i)
727 );
728
729 x_line_locs.error_flag_tbl(i) := FND_API.g_TRUE;
730 END IF;
731 END IF;
732 END LOOP;
733
734 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_DERIVE);
735
736 IF (PO_LOG.d_proc) THEN
737 PO_LOG.proc_end (d_module);
738 END IF;
739
740 EXCEPTION
741 WHEN OTHERS THEN
742 PO_MESSAGE_S.add_exc_msg
743 (
744 p_pkg_name => d_pkg_name,
745 p_procedure_name => d_api_name || '.' || d_position
746 );
747 RAISE;
748 END derive_line_locs;
749
750 -----------------------------------------------------------------------
751 --Start of Comments
752 --Name: default_line_locs
753 --Function:
754 -- perform default logic on line location records read in one batch;
755 --Parameters:
756 --IN:
757 --IN OUT:
758 --x_line_locs
759 -- variable to hold all the line location attribute values in one batch;
760 -- default result are saved inside the variable
761 --OUT:
762 --End of Comments
763 ------------------------------------------------------------------------
764 PROCEDURE default_line_locs
765 (
766 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
767 ) IS
768
769 d_api_name CONSTANT VARCHAR2(30) := 'default_line_locs';
770 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
771 d_position NUMBER;
772
773 -- key of temp table used to identify the derived result
774 l_key po_session_gt.key%TYPE;
775
776 -- table used to save the index of the each row
777 l_index_tbl DBMS_SQL.NUMBER_TABLE;
778 BEGIN
779 d_position := 0;
780
781 IF (PO_LOG.d_proc) THEN
782 PO_LOG.proc_begin(d_module);
783 END IF;
784
785 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_DEFAULT);
786
787 -- pick a new key from temp table which will be used in all default logic
788 l_key := PO_CORE_S.get_session_gt_nextval;
789
790 -- initialize table containing the row number
791 PO_PDOI_UTL.generate_ordered_num_list
792 (
793 p_size => x_line_locs.rec_count,
794 x_num_list => l_index_tbl
795 );
796
797 -- handle default logic based on document types
798 IF (PO_PDOI_PARAMS.g_request.document_type =
799 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
800
801 d_position := 10;
802
803 default_locs_for_spo
804 (
805 p_key => l_key,
806 p_index_tbl => l_index_tbl,
807 x_line_locs => x_line_locs
808 );
809 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
810 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
811
812 d_position := 20;
813
814 default_locs_for_blanket
815 (
816 p_key => l_key,
817 p_index_tbl => l_index_tbl,
818 x_line_locs => x_line_locs
819 );
820 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
821 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
822
823 d_position := 30;
824
825 default_locs_for_quotation
826 (
827 p_key => l_key,
828 p_index_tbl => l_index_tbl,
829 x_line_locs => x_line_locs
830 );
831 END IF;
832
833 d_position := 40;
834
835 -- call utility method to default standard who columns
836 PO_PDOI_MAINPROC_UTL_PVT.default_who_columns
837 (
838 x_last_update_date_tbl => x_line_locs.last_update_date_tbl,
839 x_last_updated_by_tbl => x_line_locs.last_updated_by_tbl,
840 x_last_update_login_tbl => x_line_locs.last_update_login_tbl,
841 x_creation_date_tbl => x_line_locs.creation_date_tbl,
842 x_created_by_tbl => x_line_locs.created_by_tbl,
843 x_request_id_tbl => x_line_locs.request_id_tbl,
844 x_program_application_id_tbl => x_line_locs.program_application_id_tbl,
845 x_program_id_tbl => x_line_locs.program_id_tbl,
846 x_program_update_date_tbl => x_line_locs.program_update_date_tbl
847 );
848
852 PO_LOG.proc_end (d_module);
849 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_DEFAULT);
850
851 IF (PO_LOG.d_proc) THEN
853 END IF;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 PO_MESSAGE_S.add_exc_msg
858 (
859 p_pkg_name => d_pkg_name,
860 p_procedure_name => d_api_name || '.' || d_position
861 );
862 RAISE;
863 END default_line_locs;
864
865 -----------------------------------------------------------------------
866 --Start of Comments
867 --Name: validate_line_locs
868 --Function:
869 -- validate line location attributes read within a batch
870 --Parameters:
871 --IN:
872 --IN OUT:
873 --x_line_locs
874 -- The record contains the values to be validated.
875 -- If there is error(s) on any attribute of the location row,
876 -- corresponding value in error_flag_tbl will be set with value
877 -- FND_API.g_TRUE.
878 --OUT:
879 --End of Comments
880 ------------------------------------------------------------------------
881 PROCEDURE validate_line_locs
882 (
883 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
884 ) IS
885
886 d_api_name CONSTANT VARCHAR2(30) := 'validate_line_locs';
887 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
888 d_position NUMBER;
889
890 l_line_locations PO_LINE_LOCATIONS_VAL_TYPE := PO_LINE_LOCATIONS_VAL_TYPE();
891 l_parameter_name_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
892 l_parameter_value_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
893 l_result_type VARCHAR2(30);
894 l_results po_validation_results_type;
895
896 BEGIN
897 d_position := 0;
898
899 IF (PO_LOG.d_proc) THEN
900 PO_LOG.proc_begin(d_module);
901 END IF;
902
903 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_VALIDATE);
904
905 l_line_locations.interface_id := x_line_locs.intf_line_loc_id_tbl;
906 l_line_locations.purchase_basis := x_line_locs.ln_purchase_basis_tbl;
907 l_line_locations.need_by_date := x_line_locs.need_by_date_tbl;
908 l_line_locations.promised_date := x_line_locs.promised_date_tbl;
909 l_line_locations.shipment_type := x_line_locs.shipment_type_tbl;
910 l_line_locations.shipment_num := x_line_locs.shipment_num_tbl;
911 l_line_locations.po_header_id := x_line_locs.hd_po_header_id_tbl;
912 l_line_locations.po_line_id := x_line_locs.ln_po_line_id_tbl;
913 l_line_locations.terms_id := x_line_locs.terms_id_tbl;
914 l_line_locations.quantity := x_line_locs.quantity_tbl;
915 l_line_locations.order_type_lookup_code := x_line_locs.ln_order_type_lookup_code_tbl;
916 l_line_locations.price_override := x_line_locs.price_override_tbl;
917 l_line_locations.price_discount := x_line_locs.price_discount_tbl;
918 l_line_locations.ship_to_organization_id := x_line_locs.ship_to_org_id_tbl;
919 l_line_locations.item_id := x_line_locs.ln_item_id_tbl;
920 l_line_locations.item_revision := x_line_locs.ln_item_revision_tbl;
921 l_line_locations.ship_to_location_id := x_line_locs.ship_to_loc_id_tbl;
922 l_line_locations.line_expiration_date := x_line_locs.ln_expiration_date_tbl;
923 l_line_locations.to_date := x_line_locs.end_date_tbl;
924 l_line_locations.from_date := x_line_locs.start_date_tbl;
925 l_line_locations.hdr_start_date := x_line_locs.hd_effective_date_tbl;
926 l_line_locations.hdr_end_date := x_line_locs.hd_expiration_date_tbl;
927 l_line_locations.qty_rcv_exception_code := x_line_locs.qty_rcv_exception_code_tbl;
928 l_line_locations.enforce_ship_to_location_code := x_line_locs.enforce_ship_to_loc_code_tbl;
929 l_line_locations.allow_substitute_receipts_flag := x_line_locs.allow_sub_receipts_flag_tbl;
930 l_line_locations.days_early_receipt_allowed := x_line_locs.days_early_receipt_allowed_tbl;
931 l_line_locations.receipt_days_exception_code := x_line_locs.receipt_days_except_code_tbl;
932 l_line_locations.invoice_close_tolerance := x_line_locs.invoice_close_tolerance_tbl;
933 l_line_locations.receiving_routing_id := x_line_locs.receiving_routing_id_tbl;
934 l_line_locations.accrue_on_receipt_flag := x_line_locs.accrue_on_receipt_flag_tbl;
935
936 l_line_locations.freight_carrier := x_line_locs.freight_carrier_tbl;
937 l_line_locations.fob_lookup_code := x_line_locs.fob_tbl;
938 l_line_locations.freight_terms_lookup_code := x_line_locs.freight_term_tbl;
939 l_line_locations.qty_rcv_tolerance := x_line_locs.qty_rcv_tolerance_tbl;
940 l_line_locations.firm_status_lookup_code := x_line_locs.firm_flag_tbl;
941 l_line_locations.qty_rcv_exception_code := x_line_locs.qty_rcv_exception_code_tbl;
942 l_line_locations.receipt_required_flag := x_line_locs.receipt_required_flag_tbl;
943 l_line_locations.inspection_required_flag := x_line_locs.inspection_required_flag_tbl;
944 l_line_locations.receipt_days_exception_code := x_line_locs.receipt_days_except_code_tbl;
945 l_line_locations.invoice_close_tolerance := x_line_locs.invoice_close_tolerance_tbl;
949 l_line_locations.allow_substitute_receipts_flag := x_line_locs.allow_sub_receipts_flag_tbl;
946 l_line_locations.receive_close_tolerance := x_line_locs.receive_close_tolerance_tbl;
947 l_line_locations.days_late_receipt_allowed := x_line_locs.days_late_receipt_allowed_tbl;
948 l_line_locations.enforce_ship_to_location_code := x_line_locs.enforce_ship_to_loc_code_tbl;
950 l_line_locations.secondary_unit_of_measure := x_line_locs.secondary_unit_of_meas_tbl;
951 l_line_locations.secondary_quantity := x_line_locs.secondary_quantity_tbl;
952 l_line_locations.preferred_grade := x_line_locs.preferred_grade_tbl;
953 l_line_locations.item := x_line_locs.ln_item_tbl;
954 l_line_locations.hdr_style_id := x_line_locs.hd_style_id_tbl;
955 l_line_locations.tax_name := x_line_locs.tax_name_tbl;
956 l_line_locations.tax_code_id := x_line_locs.tax_code_id_tbl;
957 l_line_locations.line_price_break_lookup_code := x_line_locs.ln_price_break_lookup_code_tbl; -- bug5016163
958 l_line_locations.draft_id := x_line_locs.draft_id_tbl; -- bug 4642348
959 d_position := 10;
960
961 l_parameter_name_tbl.EXTEND(5);
962 l_parameter_value_tbl.EXTEND(5);
963 l_parameter_name_tbl(1) := 'CREATE_OR_UPDATE_ITEM';
964 l_parameter_value_tbl(1) := PO_PDOI_PARAMS.g_request.create_items;
965 l_parameter_name_tbl(2) := 'DOC_TYPE';
966 l_parameter_value_tbl(2) := PO_PDOI_PARAMS.g_request.document_type;
967 l_parameter_name_tbl(3) := 'OPERATING_UNIT';
968 l_parameter_value_tbl(3) := PO_PDOI_PARAMS.g_request.org_id;
969 l_parameter_name_tbl(4) := 'ALLOW_TAX_CODE_OVERRIDE';
970 l_parameter_value_tbl(4) := PO_PDOI_PARAMS.g_profile.allow_tax_code_override;
971 l_parameter_name_tbl(5) := 'INVENTORY_ORG_ID';
972 l_parameter_value_tbl(5) := PO_PDOI_PARAMS.g_sys.def_inv_org_id; -- bug5601416
973
974 PO_VALIDATIONS.validate_pdoi
975 (
976 p_line_locations => l_line_locations,
977 p_doc_type => PO_PDOI_PARAMS.g_request.document_type,
978 p_parameter_name_tbl => l_parameter_name_tbl,
979 p_parameter_value_tbl => l_parameter_value_tbl,
980 x_result_type => l_result_type,
981 x_results => l_results
982 );
983
984 d_position := 20;
985
986 IF l_result_type = po_validations.c_result_type_failure THEN
987
988 IF (PO_LOG.d_stmt) THEN
989 PO_LOG.stmt(d_module, d_position, 'vaidate line locs return failure');
990 END IF;
991
992 PO_PDOI_ERR_UTL.process_val_type_errors
993 (
994 x_results => l_results,
995 p_table_name => 'PO_LINE_LOCATIONS_INTERFACE',
996 p_line_locs => x_line_locs
997 );
998
999 d_position := 30;
1000
1001 populate_error_flag
1002 (
1003 x_results => l_results,
1004 x_line_locs => x_line_locs
1005 );
1006 END IF;
1007
1008 IF l_result_type = po_validations.c_result_type_fatal THEN
1009 IF (PO_LOG.d_stmt) THEN
1010 PO_LOG.stmt(d_module, d_position, 'vaidate line locs return fatal');
1011 END IF;
1012
1013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1014 END IF;
1015
1016 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_VALIDATE);
1017
1018 IF (PO_LOG.d_proc) THEN
1019 PO_LOG.proc_end (d_module);
1020 END IF;
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 PO_MESSAGE_S.add_exc_msg
1025 (
1026 p_pkg_name => d_pkg_name,
1027 p_procedure_name => d_api_name || '.' || d_position
1028 );
1029 RAISE;
1030 END validate_line_locs;
1031
1032 -----------------------------------------------------------------------
1033 --Start of Comments
1034 --Name: update_line_loc_interface
1035 --Function:
1036 -- Update line location interface table with the line_location_id.
1037 -- This value may be used in distribution processing
1038 --Parameters:
1039 --IN:
1040 --p_intf_line_loc_id_tbl
1041 -- list of interface_line_location_ids. Used to identify the rows to
1042 -- be updated in po_line_locations_interface table
1043 --p_line_loc_id_tbl
1044 -- list of the new line_location_ids which is going to be set in
1045 -- po_line_locations_interface table
1046 --p_error_flag_tbl
1047 -- list of error_flags which indicates whether there is any error
1048 -- found in the processing logic for the corresponding location record
1049 --IN OUT:
1050 --OUT:
1051 --End of Comments
1052 ------------------------------------------------------------------------
1053 PROCEDURE update_line_loc_interface
1054 (
1055 p_intf_line_loc_id_tbl IN PO_TBL_NUMBER,
1056 p_line_loc_id_tbl IN PO_TBL_NUMBER,
1057 p_error_flag_tbl IN PO_TBL_VARCHAR1
1058 ) IS
1059
1060 d_api_name CONSTANT VARCHAR2(30) := 'update_line_loc_interface';
1061 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1062 d_position NUMBER;
1063
1064 BEGIN
1065 d_position := 0;
1066
1067 IF (PO_LOG.d_proc) THEN
1068 PO_LOG.proc_begin(d_module, 'p_intf_line_loc_id_tbl', p_intf_line_loc_id_tbl);
1069 PO_LOG.proc_begin(d_module, 'p_line_loc_id_tbl', p_line_loc_id_tbl);
1070 PO_LOG.proc_begin(d_module, 'p_error_flag_tbl', p_error_flag_tbl);
1071 END IF;
1072
1076 SET line_location_id = p_line_loc_id_tbl(i),
1073 -- update line_location_interface table with the new line_location_id
1074 FORALL i IN 1..p_intf_line_loc_id_tbl.COUNT
1075 UPDATE po_line_locations_interface
1077 process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
1078 WHERE interface_line_location_id = p_intf_line_loc_id_tbl(i)
1079 AND p_error_flag_tbl(i) = FND_API.g_FALSE;
1080
1081 IF (PO_LOG.d_proc) THEN
1082 PO_LOG.proc_end (d_module);
1083 END IF;
1084
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 PO_MESSAGE_S.add_exc_msg
1088 (
1089 p_pkg_name => d_pkg_name,
1090 p_procedure_name => d_api_name || '.' || d_position
1091 );
1092 RAISE;
1093 END update_line_loc_interface;
1094
1095 -----------------------------------------------------------------------
1096 --Start of Comments
1097 --Name: update_amount_quantity_on_line
1098 --Function:
1099 -- Called when document type is Standard PO
1100 -- The procedure is to calculate the total of amount(quantity) of
1101 -- shipment lines for each po line, then set the total value back to
1102 -- po line record.
1103 --Parameters:
1104 --IN:
1105 --p_po_line_id_tbl
1106 -- List of po_line_ids for which we need to do the calculation
1107 --p_draft_id_tbl
1108 -- corresponding draft_id list for p_po_line_id_tbl
1109 --IN OUT:
1110 --OUT:
1111 --End of Comments
1112 ------------------------------------------------------------------------
1113 PROCEDURE update_amount_quantity_on_line
1114 (
1115 p_po_line_id_tbl IN DBMS_SQL.NUMBER_TABLE,
1116 p_draft_id_tbl IN DBMS_SQL.NUMBER_TABLE
1117 ) IS
1118
1119 d_api_name CONSTANT VARCHAR2(30) := 'update_amount_quantity_on_line';
1120 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1121 d_position NUMBER;
1122
1123 BEGIN
1124 d_position := 0;
1125
1126 IF (PO_LOG.d_proc) THEN
1127 PO_LOG.proc_begin(d_module);
1128 END IF;
1129
1130 FORALL i IN 1..p_po_line_id_tbl.COUNT
1131 UPDATE po_lines_draft_all draft_lines
1132 SET (amount, quantity) =
1133 (select sum(amount), sum(quantity)
1134 FROM po_line_locations_draft_all
1135 WHERE po_line_id = draft_lines.po_line_id
1136 AND draft_id = draft_lines.draft_id)
1137 WHERE po_line_id = p_po_line_id_tbl(i)
1138 AND draft_id = p_draft_id_tbl(i);
1139
1140 IF (PO_LOG.d_proc) THEN
1141 PO_LOG.proc_end (d_module);
1142 END IF;
1143
1144 EXCEPTION
1145 WHEN OTHERS THEN
1146 PO_MESSAGE_S.add_exc_msg
1147 (
1148 p_pkg_name => d_pkg_name,
1149 p_procedure_name => d_api_name || '.' || d_position
1150 );
1151 RAISE;
1152 END update_amount_quantity_on_line;
1153
1154 -----------------------------------------------------------------------
1155 --Start of Comments
1156 --Name: delete_exist_price_breaks
1157 --Function:
1158 -- Called when document type is Quotation
1159 -- The procedure is to delete all the existing price breaks for a
1160 -- quotation line if new price break(s) is loaded for this quotation
1161 --Parameters:
1162 --IN:
1163 --p_po_line_id_tbl
1164 -- List of po_line_ids for which we need to delete existing price breaks
1165 --p_draft_id_tbl
1166 -- corresponding draft_id list for p_po_line_id_tbl
1167 --IN OUT:
1168 --OUT:
1169 --End of Comments
1170 ------------------------------------------------------------------------
1171 PROCEDURE delete_exist_price_breaks
1172 (
1173 p_po_line_id_tbl IN DBMS_SQL.NUMBER_TABLE,
1174 p_draft_id_tbl IN DBMS_SQL.NUMBER_TABLE
1175 ) IS
1176
1177 d_api_name CONSTANT VARCHAR2(30) := 'delete_exist_price_breaks';
1178 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1179 d_position NUMBER;
1180
1181 -- key value used to identify rows in po_session_gt table
1182 l_key po_session_gt.key%TYPE;
1183
1184 -- variables to hold results from po_session_gt table
1185 l_line_loc_id_tbl PO_TBL_NUMBER;
1186 l_draft_id_tbl PO_TBL_NUMBER;
1187
1188 l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1189 l_record_already_exist_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1190 BEGIN
1191 d_position := 0;
1192
1193 IF (PO_LOG.d_proc) THEN
1194 PO_LOG.proc_begin(d_module);
1195 END IF;
1196
1197 l_key := PO_CORE_S.get_session_gt_nextval;
1198
1199 FORALL i IN 1..p_po_line_id_tbl.COUNT
1200 INSERT INTO po_session_gt(key, num1, num2)
1201 SELECT l_key,
1202 line_location_id,
1203 p_draft_id_tbl(i)
1204 FROM po_line_locations_all
1205 WHERE po_line_id = p_po_line_id_tbl(i);
1206
1207 d_position := 10;
1208
1209 DELETE FROM po_session_gt
1210 WHERE key = l_key
1211 RETURNING num1, num2 BULK COLLECT INTO l_line_loc_id_tbl, l_draft_id_tbl;
1212
1213 d_position := 20;
1214
1215 l_delete_flag_tbl.EXTEND(l_line_loc_id_tbl.COUNT);
1216 FOR i IN 1..l_line_loc_id_tbl.COUNT
1217 LOOP
1218 l_delete_flag_tbl(i) := 'Y';
1219 END LOOP;
1220 PO_LINE_LOCATIONS_DRAFT_PKG.sync_draft_from_txn
1221 (
1222 p_line_location_id_tbl => l_line_loc_id_tbl,
1223 p_draft_id_tbl => l_draft_id_tbl,
1227
1224 p_delete_flag_tbl => l_delete_flag_tbl,
1225 x_record_already_exist_tbl => l_record_already_exist_tbl
1226 );
1228 IF (PO_LOG.d_proc) THEN
1229 PO_LOG.proc_end (d_module);
1230 END IF;
1231
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 PO_MESSAGE_S.add_exc_msg
1235 (
1236 p_pkg_name => d_pkg_name,
1237 p_procedure_name => d_api_name || '.' || d_position
1238 );
1239 RAISE;
1240 END delete_exist_price_breaks;
1241
1242 -------------------------------------------------------------------------
1243 --------------------- PRIVATE PROCEDURES --------------------------------
1244 -------------------------------------------------------------------------
1245
1246 -----------------------------------------------------------------------
1247 --Start of Comments
1248 --Name: derive_line_loc_id
1249 --Function:
1250 -- logic to derive line_location_id from shipment_num and po_line_id
1251 -- in batch mode
1252 --Parameters:
1253 --IN:
1254 --p_key
1255 -- identifier in the temp table on the derived result
1256 --p_index_tbl
1257 -- indexes of the records
1258 --p_po_line_id_tbl
1259 -- list of po_line_ids within the batch
1260 --p_shipment_num_tbl
1261 -- list of shipment_nums within the batch
1262 --IN OUT:
1263 --x_line_loc_id_tbl
1264 -- contains the derived result if original value is null;
1265 -- original value will not be changed if it is not null
1266 --OUT:
1267 --End of Comments
1268 ------------------------------------------------------------------------
1269 PROCEDURE derive_line_loc_id
1270 (
1271 p_key IN po_session_gt.key%TYPE,
1272 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1273 p_po_line_id_tbl IN PO_TBL_NUMBER,
1274 p_shipment_num_tbl IN PO_TBL_NUMBER,
1275 x_line_loc_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
1276 ) IS
1277
1278 d_api_name CONSTANT VARCHAR2(30) := 'derive_line_loc_id';
1279 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1280 d_position NUMBER;
1281
1282 -- tables to store the derived result
1283 l_index_tbl PO_TBL_NUMBER;
1284 l_result_tbl PO_TBL_NUMBER;
1285 BEGIN
1286 d_position := 0;
1287
1288 IF (PO_LOG.d_proc) THEN
1289 PO_LOG.proc_begin(d_module, 'p_po_line_id_tbl', p_po_line_id_tbl);
1290 PO_LOG.proc_begin(d_module, 'p_shipment_num_tbl', p_shipment_num_tbl);
1291 PO_LOG.proc_begin(d_module, 'x_line_loc_id_tbl', x_line_loc_id_tbl);
1292 END IF;
1293
1294 -- run query to extract line_location_id
1295 FORALL i IN 1..p_index_tbl.COUNT
1296 INSERT INTO po_session_gt(key, num1, num2)
1297 SELECT p_key,
1298 p_index_tbl(i),
1299 line_location_id
1300 FROM po_line_locations
1301 WHERE p_shipment_num_tbl(i) IS NOT NULL
1302 AND x_line_loc_id_tbl(i) IS NULL
1303 AND po_line_id = p_po_line_id_tbl(i)
1304 AND shipment_num = p_shipment_num_tbl(i)
1305 AND shipment_type = 'PRICE BREAK';
1306
1307 d_position := 10;
1308
1309 -- read result from temp table, and delete the records from temp table
1310 DELETE FROM po_session_gt
1311 WHERE key = p_key
1312 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1313
1314 d_position := 20;
1315
1316 -- push the result back to x_line_loc_id_tbl
1317 FOR i IN 1..l_index_tbl.COUNT
1318 LOOP
1319 IF (PO_LOG.d_stmt) THEN
1320 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1321 PO_LOG.stmt(d_module, d_position, 'new line loc id', l_result_tbl(i));
1322 END IF;
1323
1324 x_line_loc_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
1325 END LOOP;
1326
1327 IF (PO_LOG.d_proc) THEN
1328 PO_LOG.proc_end (d_module);
1329 END IF;
1330
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 PO_MESSAGE_S.add_exc_msg
1334 (
1335 p_pkg_name => d_pkg_name,
1336 p_procedure_name => d_api_name || '.' || d_position
1337 );
1338 RAISE;
1339 END derive_line_loc_id;
1340
1341 -----------------------------------------------------------------------
1342 --Start of Comments
1343 --Name: derive_ship_to_org_id
1344 --Function:
1345 -- logic to derive ship_to_organization_id from ship_to_organization_code
1346 -- in batch mode
1347 --Parameters:
1348 --IN:
1349 --p_key
1350 -- identifier in the temp table on the derived result
1351 --p_index_tbl
1352 -- indexes of the records
1353 --p_ship_to_org_code_tbl
1354 -- ist of ship_to_organization_code values within the batch
1355 --IN OUT:
1356 --x_ship_to_org_id_tbl
1357 -- contains the derived result if original value is null;
1358 -- original value will not be changed if it is not null
1359 --OUT:
1360 --End of Comments
1361 ------------------------------------------------------------------------
1362 PROCEDURE derive_ship_to_org_id
1363 (
1364 p_key IN po_session_gt.key%TYPE,
1365 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1366 p_ship_to_org_code_tbl IN PO_TBL_VARCHAR5,
1367 x_ship_to_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
1368 ) IS
1369
1370 d_api_name CONSTANT VARCHAR2(30) := 'derive_ship_to_org_id';
1371 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1372 d_position NUMBER;
1376 l_result_tbl PO_TBL_NUMBER;
1373
1374 -- tables to store the derived result
1375 l_index_tbl PO_TBL_NUMBER;
1377 BEGIN
1378 d_position := 0;
1379
1380 IF (PO_LOG.d_proc) THEN
1381 PO_LOG.proc_begin(d_module, 'p_ship_to_org_code_tbl', p_ship_to_org_code_tbl);
1382 PO_LOG.proc_begin(d_module, 'x_ship_to_org_id_tbl', x_ship_to_org_id_tbl);
1383 END IF;
1384
1385 -- execute query to extract org_id from org_code
1386 FORALL i IN 1.. p_index_tbl.COUNT
1387 INSERT INTO po_session_gt(key, num1, num2)
1388 SELECT p_key,
1389 p_index_tbl(i),
1390 organization_id
1391 FROM org_organization_definitions
1392 WHERE p_ship_to_org_code_tbl(i) IS NOT NULL
1393 AND x_ship_to_org_id_tbl(i) IS NULL
1394 AND organization_code = p_ship_to_org_code_tbl(i)
1395 AND TRUNC(sysdate) < nvl(disable_date, TRUNC(sysdate+1))
1396 AND inventory_enabled_flag = 'Y';
1397
1398 d_position := 10;
1399
1400 -- read result from temp table, and delete the records from temp table
1401 DELETE FROM po_session_gt
1402 WHERE key = p_key
1403 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1404
1405 d_position := 20;
1406
1407 -- set derived result in x_ship_to_org_id_tbl
1408 FOR i IN 1..l_index_tbl.COUNT
1409 LOOP
1410 IF (PO_LOG.d_stmt) THEN
1411 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1412 PO_LOG.stmt(d_module, d_position, 'new ship_to org id', l_result_tbl(i));
1413 END IF;
1414
1415 x_ship_to_org_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
1416 END LOOP;
1417
1418 IF (PO_LOG.d_proc) THEN
1419 PO_LOG.proc_end (d_module);
1420 END IF;
1421
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 PO_MESSAGE_S.add_exc_msg
1425 (
1426 p_pkg_name => d_pkg_name,
1427 p_procedure_name => d_api_name || '.' || d_position
1428 );
1429 RAISE;
1430 END derive_ship_to_org_id;
1431
1432 -----------------------------------------------------------------------
1433 --Start of Comments
1434 --Name: derive_receiving_routing_id
1435 --Function:
1436 -- logic to derive receiving_routing_id from receiving_routing
1437 -- in batch mode
1438 --Parameters:
1439 --IN:
1440 --p_key
1441 -- identifier in the temp table on the derived result
1442 --p_index_tbl
1443 -- indexes of the records
1444 --p_receiving_routing_tbl
1445 -- list of receiving_routing values within the batch
1446 --IN OUT:
1447 --x_receiving_routing_id_tbl
1448 -- contains the derived result if original value is null;
1449 -- original value will not be changed if it is not null
1450 --OUT:
1451 --End of Comments
1452 ------------------------------------------------------------------------
1453 PROCEDURE derive_receiving_routing_id
1454 (
1455 p_key IN po_session_gt.key%TYPE,
1456 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1457 p_receiving_routing_tbl IN PO_TBL_VARCHAR30,
1458 x_receiving_routing_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
1459 ) IS
1460
1461 d_api_name CONSTANT VARCHAR2(30) := 'derive_receiving_routing_id';
1462 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1463 d_position NUMBER;
1464
1465 -- tables to store the derived result
1466 l_index_tbl PO_TBL_NUMBER;
1467 l_result_tbl PO_TBL_NUMBER;
1468 BEGIN
1469 d_position := 0;
1470
1471 IF (PO_LOG.d_proc) THEN
1472 PO_LOG.proc_begin(d_module, 'p_receiving_routing_tbl',
1473 p_receiving_routing_tbl);
1474 PO_LOG.proc_begin(d_module, 'x_receiving_routing_id_tbl',
1475 x_receiving_routing_id_tbl);
1476 END IF;
1477
1478 -- execute query to extract routing_id from receiving_routings
1479 FORALL i IN 1..p_index_tbl.COUNT
1480 INSERT INTO po_session_gt(key, num1, num2)
1481 SELECT p_key,
1482 p_index_tbl(i),
1483 routing_header_id
1484 FROM rcv_routing_headers
1485 WHERE p_receiving_routing_tbl(i) IS NOT NULL
1486 AND x_receiving_routing_id_tbl(i) IS NULL
1487 AND routing_name = p_receiving_routing_tbl(i);
1488
1489 d_position := 10;
1490
1491 -- read result from temp table, and delete the records from temp table
1492 DELETE FROM po_session_gt
1493 WHERE key = p_key
1494 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1495
1496 d_position := 20;
1497
1498 -- set derived result in x_receiving_routing_id_tbl
1499 FOR i IN 1..l_index_tbl.COUNT
1500 LOOP
1501 IF (PO_LOG.d_stmt) THEN
1502 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1503 PO_LOG.stmt(d_module, d_position, 'new routing id', l_result_tbl(i));
1504 END IF;
1505
1506 x_receiving_routing_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
1507 END LOOP;
1508
1509 IF (PO_LOG.d_proc) THEN
1510 PO_LOG.proc_end (d_module);
1511 END IF;
1512
1513 EXCEPTION
1514 WHEN OTHERS THEN
1515 PO_MESSAGE_S.add_exc_msg
1516 (
1517 p_pkg_name => d_pkg_name,
1518 p_procedure_name => d_api_name || '.' || d_position
1519 );
1520 RAISE;
1524 --Start of Comments
1521 END derive_receiving_routing_id;
1522
1523 -----------------------------------------------------------------------
1525 --Name: derive_tax_name
1526 --Function:
1527 -- logic to derive tax_name from tax_code_id
1528 -- in batch mode
1529 --Parameters:
1530 --IN:
1531 --p_key
1532 -- identifier in the temp table on the derived result
1533 --p_index_tbl
1534 -- indexes of the records
1535 --p_tax_code_id_tbl
1536 -- list of tax_code_id values within the batch
1537 --IN OUT:
1538 --x_tax_name_tbl
1539 -- contains the derived result if original value is null;
1540 -- original value will not be changed if it is not null
1541 --OUT:
1542 --End of Comments
1543 ------------------------------------------------------------------------
1544 PROCEDURE derive_tax_name
1545 (
1546 p_key IN po_session_gt.key%TYPE,
1547 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1548 p_tax_code_id_tbl IN PO_TBL_NUMBER,
1549 x_tax_name_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
1550 ) IS
1551
1552 d_api_name CONSTANT VARCHAR2(30) := 'derive_tax_name';
1553 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1554 d_position NUMBER;
1555
1556 -- tables to store the derived result
1557 l_index_tbl PO_TBL_NUMBER;
1558 l_result_tbl PO_TBL_VARCHAR30;
1559 BEGIN
1560 d_position := 0;
1561
1562 IF (PO_LOG.d_proc) THEN
1563 PO_LOG.proc_begin(d_module, 'p_tax_code_id_tbl',
1564 p_tax_code_id_tbl);
1565 PO_LOG.proc_begin(d_module, 'x_tax_name_tbl',
1566 x_tax_name_tbl);
1567 END IF;
1568
1569 -- execute query to extract tax_name from tax_code_id
1570 FORALL i IN 1..p_index_tbl.COUNT
1571 INSERT INTO po_session_gt(key, num1, char1)
1572 SELECT p_key,
1573 p_index_tbl(i),
1574 tax_classification_code
1575 FROM zx_id_tcc_mapping
1576 WHERE p_tax_code_id_tbl(i) IS NOT NULL
1577 AND x_tax_name_tbl(i) IS NULL
1578 AND tax_rate_code_id = p_tax_code_id_tbl(i)
1579 AND source = 'AP'
1580 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(effective_from, sysdate))
1581 AND TRUNC(NVL(effective_to, sysdate));
1582
1583 d_position := 10;
1584
1585 -- read result from temp table, and delete the records from temp table
1586 DELETE FROM po_session_gt
1587 WHERE key = p_key
1588 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1589
1590 d_position := 20;
1591
1592 -- set derived result in x_receiving_routing_id_tbl
1593 FOR i IN 1..l_index_tbl.COUNT
1594 LOOP
1595 IF (PO_LOG.d_stmt) THEN
1596 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1597 PO_LOG.stmt(d_module, d_position, 'new tax_name', l_result_tbl(i));
1598 END IF;
1599
1600 x_tax_name_tbl(l_index_tbl(i)) := l_result_tbl(i);
1601 END LOOP;
1602
1603 IF (PO_LOG.d_proc) THEN
1604 PO_LOG.proc_end (d_module);
1605 END IF;
1606
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 PO_MESSAGE_S.add_exc_msg
1610 (
1611 p_pkg_name => d_pkg_name,
1612 p_procedure_name => d_api_name || '.' || d_position
1613 );
1614 RAISE;
1615 END derive_tax_name;
1616 -----------------------------------------------------------------------
1617 --Start of Comments
1618 --Name: default_outsourced_assembly
1619 --Function:
1620 -- logic to derive outsourced_assembly from mtl_system_items_b
1621 -- in batch mode
1622 --Parameters:
1623 --IN:
1624 --p_item_id_tbl
1625 -- list of item_id values within the batch
1626 --p_ship_to_organizatin_id_tbl
1627 -- list of ship_to_organization_id values within the batch
1628 --OUT:
1629 --x_outsourced_assembly_tbl
1630 -- contains the defaulted result;
1631 --OUT:
1632 --End of Comments
1633 ------------------------------------------------------------------------
1634 PROCEDURE default_outsourced_assembly
1635 (
1636 p_item_id_tbl IN PO_TBL_NUMBER,
1637 p_ship_to_organization_id_tbl IN PO_TBL_NUMBER,
1638 x_outsourced_assembly_tbl IN OUT NOCOPY PO_TBL_NUMBER
1639 ) IS
1640
1641 d_api_name CONSTANT VARCHAR2(30) := 'default_outsourced_assembly';
1642 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1643 d_position NUMBER;
1644
1645 -- tables to store the derived result
1646 l_index_tbl PO_TBL_NUMBER;
1647 l_result_tbl PO_TBL_NUMBER;
1648 BEGIN
1649 d_position := 0;
1650
1651 IF (PO_LOG.d_proc) THEN
1652 PO_LOG.proc_begin(d_module, 'p_item_id_tbl', p_item_id_tbl);
1653 PO_LOG.proc_begin(d_module, 'p_ship_to_organization_id_tbl', p_ship_to_organization_id_tbl);
1654 END IF;
1655
1656 FOR i IN 1..p_item_id_tbl.COUNT LOOP
1657 x_outsourced_assembly_tbl(i) :=
1658 PO_CORE_S.get_outsourced_assembly
1659 ( p_item_id => p_item_id_tbl(i),
1660 p_ship_to_org_id => p_ship_to_organization_id_tbl(i)
1661 );
1662 END LOOP;
1663
1664 IF (PO_LOG.d_proc) THEN
1665 PO_LOG.proc_end (d_module);
1666 END IF;
1667
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 PO_MESSAGE_S.add_exc_msg
1671 (
1675 RAISE;
1672 p_pkg_name => d_pkg_name,
1673 p_procedure_name => d_api_name || '.' || d_position
1674 );
1676 END default_outsourced_assembly;
1677
1678 -----------------------------------------------------------------------
1679 --Start of Comments
1680 --Name: default_locs_for_spo
1681 --Function:
1682 -- default logic on line location attributes for Standard PO
1683 --Parameters:
1684 --IN:
1685 --p_key
1686 -- identifier in the temp table on the derived result
1687 --p_index_tbl
1688 -- indexes of the records
1689 --IN OUT:
1690 --x_line_locs
1691 -- variable to hold all the line location attribute values in one batch;
1692 -- default result are saved inside the variable
1693 --OUT:
1694 --End of Comments
1695 ------------------------------------------------------------------------
1696 PROCEDURE default_locs_for_spo
1697 (
1698 p_key IN po_session_gt.key%TYPE,
1699 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1700 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
1701 ) IS
1702
1703 d_api_name CONSTANT VARCHAR2(30) := 'default_locs_for_spo';
1704 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1705 d_position NUMBER;
1706
1707 -- receiving control default values
1708 l_enforce_ship_to_loc_code VARCHAR2(25);
1709 l_allow_sub_receipts_flag VARCHAR2(1);
1710 l_receiving_routing_id NUMBER;
1711 l_qty_rcv_tolerance NUMBER;
1712 l_qty_rcv_exception VARCHAR2(25);
1713 l_days_early_receipt_allowed NUMBER;
1714 l_days_late_receipt_allowed NUMBER;
1715 l_rct_days_exception_code VARCHAR2(25);
1716
1717 BEGIN
1718 d_position := 0;
1719
1720 IF (PO_LOG.d_proc) THEN
1721 PO_LOG.proc_begin(d_module);
1722 END IF;
1723
1724 -- default inspection_required_flag from item_id
1725 default_inspect_required_flag
1726 (
1727 p_key => p_key,
1728 p_index_tbl => p_index_tbl,
1729 p_item_id_tbl => x_line_locs.ln_item_id_tbl,
1730 x_inspection_required_flag_tbl => x_line_locs.inspection_required_flag_tbl
1731 );
1732
1733 d_position := 10;
1734
1735 -- default attribute on each record
1736 FOR i IN 1..x_line_locs.rec_count
1737 LOOP
1738 IF (PO_LOG.d_stmt) THEN
1739 PO_LOG.stmt(d_module, d_position, 'default for spo index', i);
1740 END IF;
1741
1742 -- default shipment_type
1743 x_line_locs.shipment_type_tbl(i) :=
1744 NVL(x_line_locs.shipment_type_tbl(i),'STANDARD');
1745
1746 -- default shipment_num if it is not provided or not unique
1747 IF (x_line_locs.shipment_num_tbl(i) IS NULL OR
1748 x_line_locs.shipment_num_unique_tbl(i) = 'N') THEN
1749 x_line_locs.shipment_num_tbl(i) :=
1750 PO_PDOI_MAINPROC_UTL_PVT.get_next_shipment_num
1751 (
1752 p_po_line_id => x_line_locs.ln_po_line_id_tbl(i)
1753 );
1754
1755 IF (PO_LOG.d_stmt) THEN
1756 PO_LOG.stmt(d_module, d_position, 'default shipment num',
1757 x_line_locs.shipment_num_tbl(i));
1758 END IF;
1759 END IF;
1760
1761 -- default line_location_id if not provided or derived
1762 IF (x_line_locs.line_loc_id_tbl(i) IS NULL) THEN
1763 x_line_locs.line_loc_id_tbl(i) :=
1764 PO_PDOI_MAINPROC_UTL_PVT.get_next_line_loc_id;
1765
1766 IF (PO_LOG.d_stmt) THEN
1767 PO_LOG.stmt(d_module, d_position, 'default line loc',
1768 x_line_locs.line_loc_id_tbl(i));
1769 END IF;
1770 END IF;
1771
1772 -- default price_override
1773 IF (x_line_locs.price_override_tbl(i) IS NULL AND
1774 x_line_locs.ln_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND
1775 x_line_locs.ln_action_tbl(i) <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
1776 x_line_locs.price_override_tbl(i) := x_line_locs.ln_unit_price_tbl(i);
1777 END IF;
1778
1779 -- default ship_to_location_id from header level
1780 x_line_locs.ship_to_loc_id_tbl(i) :=
1781 NVL(x_line_locs.ship_to_loc_id_tbl(i), x_line_locs.hd_ship_to_loc_id_tbl(i));
1782
1783 d_position := 20;
1784
1785 -- default inspection_required_flag from system if it cannot be
1786 -- be defaulted from item_id at the beginning of procedure
1787 x_line_locs.inspection_required_flag_tbl(i) :=
1788 NVL(x_line_locs.inspection_required_flag_tbl(i),
1789 PO_PDOI_PARAMS.g_sys.inspection_required_flag);
1790
1791 -- default receipt_required_flag from system
1792 x_line_locs.receipt_required_flag_tbl(i) :=
1793 NVL(x_line_locs.receipt_required_flag_tbl(i),
1794 PO_PDOI_PARAMS.g_sys.receiving_flag);
1795
1796 -- set tax_attribute_update_code to CREATE
1797 x_line_locs.tax_attribute_update_code_tbl(i) := 'CREATE';
1798
1799 -- set value_basis from order_type_lookup_code on line level
1800 x_line_locs.value_basis_tbl(i) := x_line_locs.ln_order_type_lookup_code_tbl(i);
1801
1802 -- set matching_basis from matching_basis on line level
1803 x_line_locs.matching_basis_tbl(i) := x_line_locs.ln_matching_basis_tbl(i);
1804
1805 -- set unit_of_measure from unit_meas_lookup_code on line level
1809 d_position := 30;
1806 x_line_locs.unit_of_measure_tbl(i) := x_line_locs.ln_unit_of_measure_tbl(i);
1807 END LOOP;
1808
1810
1811 -- default ship_to_organization_id from ship_to_location_id
1812 default_ship_to_org_id
1813 (
1814 p_key => p_key,
1815 p_index_tbl => p_index_tbl,
1816 p_ship_to_loc_id_tbl => x_line_locs.ship_to_loc_id_tbl,
1817 x_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl
1818 );
1819
1820 -- another loop to default based on defaulted ship_to_organization_id
1821 FOR i IN 1..x_line_locs.rec_count
1822 LOOP
1823 IF (PO_LOG.d_stmt) THEN
1824 PO_LOG.stmt(d_module, d_position, 'second default index', i);
1825 END IF;
1826
1827 -- default ship_to_organization_id from financial system parameter
1828 -- for STANDARD
1829 x_line_locs.ship_to_org_id_tbl(i) :=
1830 NVL(x_line_locs.ship_to_org_id_tbl(i),
1831 PO_PDOI_PARAMS.g_sys.def_inv_org_id);
1832
1833 IF (PO_LOG.d_stmt) THEN
1834 PO_LOG.stmt(d_module, d_position, 'ship_to org id',
1835 x_line_locs.ship_to_org_id_tbl(i));
1836 END IF;
1837
1838 d_position := 40;
1839
1840 -- default allow_sub_receipts_flag, qty_rcv_tolerance, and
1841 -- qty_rcv_exception_code
1842 RCV_CORE_S.get_receiving_controls
1843 (
1844 x_line_loc_id => x_line_locs.line_loc_id_tbl(i),
1845 x_item_id => x_line_locs.ln_item_id_tbl(i),
1846 x_vendor_id => x_line_locs.hd_vendor_id_tbl(i),
1847 x_org_id => x_line_locs.ship_to_org_id_tbl(i),
1848 x_enforce_ship_to_loc => l_enforce_ship_to_loc_code,
1849 x_allow_substitutes => l_allow_sub_receipts_flag,
1850 x_routing_id => l_receiving_routing_id,
1851 x_qty_rcv_tolerance => l_qty_rcv_tolerance,
1852 x_qty_rcv_exception => l_qty_rcv_exception,
1853 x_days_early_receipt => l_days_early_receipt_allowed,
1854 x_days_late_receipt => l_days_late_receipt_allowed,
1855 x_rcv_date_exception => l_rct_days_exception_code
1856 );
1857
1858 d_position := 50;
1859
1860 -- default qty_rcv_tolerance from receiving controls
1861 x_line_locs.qty_rcv_tolerance_tbl(i) :=
1862 NVL(x_line_locs.qty_rcv_tolerance_tbl(i), l_qty_rcv_tolerance);
1863
1864 -- default qty_rcv_exception_code from receiving controls
1865 x_line_locs.qty_rcv_exception_code_tbl(i) :=
1866 NVL(x_line_locs.qty_rcv_exception_code_tbl(i), l_qty_rcv_exception);
1867
1868 IF (PO_LOG.d_stmt) THEN
1869 PO_LOG.stmt(d_module, d_position, 'order type',
1870 x_line_locs.ln_order_type_lookup_code_tbl(i));
1871 END IF;
1872
1873 -- set the other attributes based on order_type
1874 IF (x_line_locs.ln_order_type_lookup_code_tbl(i) IN ('FIXED PRICE', 'RATE')) THEN
1875 x_line_locs.inspection_required_flag_tbl(i) := 'N';
1876 x_line_locs.receiving_routing_id_tbl(i) := 3; --For DIRECT Delivery
1877 x_line_locs.enforce_ship_to_loc_code_tbl(i) := NULL;
1878 x_line_locs.allow_sub_receipts_flag_tbl(i) := NULL;
1879 x_line_locs.days_early_receipt_allowed_tbl(i) := NULL;
1880 x_line_locs.days_late_receipt_allowed_tbl(i) := NULL;
1881 x_line_locs.receipt_days_except_code_tbl(i):= NULL;
1882 ELSE
1883 -- default receiving_routing_id, allow_sub_receipts_flag,
1884 -- enforce_ship_to_loc_code, days_early_receipt_allowed,
1885 -- days_late_receipt_allowed and receipt_days_exception_code
1886 -- from receiving controls
1887 x_line_locs.receiving_routing_id_tbl(i) :=
1888 NVL(x_line_locs.receiving_routing_id_tbl(i),
1889 l_receiving_routing_id);
1890 x_line_locs.enforce_ship_to_loc_code_tbl(i) :=
1891 NVL(x_line_locs.enforce_ship_to_loc_code_tbl(i),
1892 l_enforce_ship_to_loc_code);
1893 x_line_locs.allow_sub_receipts_flag_tbl(i) :=
1894 NVL(x_line_locs.allow_sub_receipts_flag_tbl(i),
1895 l_allow_sub_receipts_flag);
1896 x_line_locs.days_early_receipt_allowed_tbl(i) :=
1897 NVL(x_line_locs.days_early_receipt_allowed_tbl(i),
1898 l_days_early_receipt_allowed);
1899 x_line_locs.days_late_receipt_allowed_tbl(i) :=
1900 NVL(x_line_locs.days_late_receipt_allowed_tbl(i),
1901 l_days_late_receipt_allowed);
1902 x_line_locs.receipt_days_except_code_tbl(i):=
1903 NVL(x_line_locs.receipt_days_except_code_tbl(i),
1904 l_rct_days_exception_code);
1905 END IF;
1906 END LOOP;
1907
1908 d_position := 60;
1909
1910 -- default invoice_close_tolerance and receive_close_tolerance
1911 default_close_tolerances
1912 (
1913 p_key => p_key,
1914 p_index_tbl => p_index_tbl,
1915 p_item_id_tbl => x_line_locs.ln_item_id_tbl,
1916 p_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl,
1917 p_line_type_id_tbl => x_line_locs.ln_line_type_id_tbl,
1918 x_invoice_close_tolerance_tbl => x_line_locs.invoice_close_tolerance_tbl,
1919 x_receive_close_tolerance_tbl => x_line_locs.receive_close_tolerance_tbl
1920 );
1921
1922 d_position := 70;
1923
1924 -- default match options
1925 default_invoice_match_options
1929 p_vendor_id_tbl => x_line_locs.hd_vendor_id_tbl,
1926 (
1927 p_key => p_key,
1928 p_index_tbl => p_index_tbl,
1930 p_vendor_site_id_tbl => x_line_locs.hd_vendor_site_id_tbl,
1931 x_match_option_tbl => x_line_locs.match_option_tbl
1932 );
1933
1934 d_position := 80;
1935
1936 -- default accrue_on_receipt_flag
1937 default_accrue_on_receipt_flag
1938 (
1939 p_key => p_key,
1940 p_index_tbl => p_index_tbl,
1941 p_item_id_tbl => x_line_locs.ln_item_id_tbl,
1942 p_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl,
1943 p_receipt_required_flag_tbl => x_line_locs.receipt_required_flag_tbl,
1944 x_accrue_on_receipt_flag_tbl => x_line_locs.accrue_on_receipt_flag_tbl
1945 );
1946
1947 -- default outsourced_assembly from mtl_system_items_b
1948 default_outsourced_assembly
1949 (
1950 p_item_id_tbl => x_line_locs.ln_item_id_tbl,
1951 p_ship_to_organization_id_tbl => x_line_locs.ship_to_org_id_tbl,
1952 x_outsourced_assembly_tbl => x_line_locs.outsourced_assembly_tbl
1953 );
1954
1955 -- default secondary_unit_of_measure for dual-uom items
1956 -- Bug 4723323: add default logic for secondary_unit_of_measure
1957 default_secondary_unit_of_meas
1958 (
1959 p_key => p_key,
1960 p_index_tbl => p_index_tbl,
1961 p_item_id_tbl => x_line_locs.ln_item_id_tbl,
1962 p_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl,
1963 x_secondary_unit_of_meas_tbl => x_line_locs.secondary_unit_of_meas_tbl
1964 );
1965
1966 IF (PO_LOG.d_proc) THEN
1967 PO_LOG.proc_end (d_module);
1968 END IF;
1969
1970 EXCEPTION
1971 WHEN OTHERS THEN
1972 PO_MESSAGE_S.add_exc_msg
1973 (
1974 p_pkg_name => d_pkg_name,
1975 p_procedure_name => d_api_name || '.' || d_position
1976 );
1977 RAISE;
1978 END default_locs_for_spo;
1979
1980 -----------------------------------------------------------------------
1981 --Start of Comments
1982 --Name: default_locs_for_blanket
1983 --Function:
1984 -- default logic on line location attributes for Blanket
1985 --Parameters:
1986 --IN:
1987 -- p_key
1988 -- identifier in the temp table on the derived result
1989 -- p_index_tbl
1990 -- indexes of the records
1991 --IN OUT:
1992 --x_line_locs
1993 -- variable to hold all the line location attribute values in one batch;
1994 -- default result are saved inside the variable
1995 --OUT:
1996 --End of Comments
1997 ------------------------------------------------------------------------
1998 PROCEDURE default_locs_for_blanket
1999 (
2000 p_key IN po_session_gt.key%TYPE,
2001 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2002 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
2003 ) IS
2004
2005 d_api_name CONSTANT VARCHAR2(30) := 'default_locs_for_blanket';
2006 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2007 d_position NUMBER;
2008
2009 BEGIN
2010 d_position := 0;
2011
2012 IF (PO_LOG.d_proc) THEN
2013 PO_LOG.proc_begin(d_module);
2014 END IF;
2015
2016 -- default ship_to_organization_id from ship_to_location_id
2017 default_ship_to_org_id
2018 (
2019 p_key => p_key,
2020 p_index_tbl => p_index_tbl,
2021 p_ship_to_loc_id_tbl => x_line_locs.ship_to_loc_id_tbl,
2022 x_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl
2023 );
2024
2025 d_position := 10;
2026
2027 -- default on row by row base
2028 FOR i IN 1..x_line_locs.rec_count
2029 LOOP
2030 IF (PO_LOG.d_stmt) THEN
2031 PO_LOG.stmt(d_module, d_position, 'default for blanket index', i);
2032 END IF;
2033
2034 -- bug5307208
2035 -- Truncate start/end date of the price breaks
2036 x_line_locs.start_date_tbl(i) := TRUNC(x_line_locs.start_date_tbl(i));
2037 x_line_locs.end_date_tbl(i) := TRUNC(x_line_locs.end_date_tbl(i));
2038
2039 -- default shipment_type
2040 x_line_locs.shipment_type_tbl(i) :=
2041 NVL(x_line_locs.shipment_type_tbl(i), 'PRICE BREAK');
2042
2043 -- default shipment_num
2044 x_line_locs.shipment_num_tbl(i) :=
2045 NVL(x_line_locs.shipment_num_tbl(i),
2046 PO_PDOI_MAINPROC_UTL_PVT.get_next_shipment_num(x_line_locs.ln_po_line_id_tbl(i)));
2047
2048 -- default line_location_id
2049 x_line_locs.line_loc_id_tbl(i) :=
2050 NVL(x_line_locs.line_loc_id_tbl(i),
2051 PO_PDOI_MAINPROC_UTL_PVT.get_next_line_loc_id);
2052
2053 IF (PO_LOG.d_stmt) THEN
2054 PO_LOG.stmt(d_module, d_position, 'shipment type',
2055 x_line_locs.shipment_type_tbl(i));
2056 PO_LOG.stmt(d_module, d_position, 'shipment num',
2057 x_line_locs.shipment_num_tbl(i));
2058 PO_LOG.stmt(d_module, d_position, 'line loc id',
2059 x_line_locs.line_loc_id_tbl(i));
2060 END IF;
2061
2062 -- set accrue_on_receipt_flag and firm_flag to NULL
2063 x_line_locs.accrue_on_receipt_flag_tbl(i) := NULL;
2064 x_line_locs.firm_flag_tbl(i) := NULL;
2065
2069 -- set value_basis from order_type_lookup_code on line level
2066 -- ignore user's input of tax_name for blanket
2067 x_line_locs.tax_name_tbl(i) := NULL;
2068
2070 x_line_locs.value_basis_tbl(i) := x_line_locs.ln_order_type_lookup_code_tbl(i);
2071
2072 -- set matching_basis to NULL
2073 x_line_locs.matching_basis_tbl(i) := NULL;
2074
2075 -- default price_discount and price_override
2076
2077 --Bug # 6657206 Added the following If condition to bypass discount_price calculation when break price is 0
2078 IF ( x_line_locs.ln_unit_price_tbl(i) <> 0 ) THEN
2079 IF (x_line_locs.price_override_tbl(i) IS NOT NULL) THEN
2080 x_line_locs.price_discount_tbl(i) :=
2081 ROUND(((x_line_locs.ln_unit_price_tbl(i) - x_line_locs.price_override_tbl(i))/x_line_locs.ln_unit_price_tbl(i)) * 100, 2);
2082 ELSIF (x_line_locs.price_override_tbl(i) IS NULL AND
2083 x_line_locs.price_discount_tbl(i) IS NOT NULL) THEN
2084 x_line_locs.price_override_tbl(i) :=
2085 ROUND((((100 - x_line_locs.price_discount_tbl(i))/100) * x_line_locs.ln_unit_price_tbl(i)), 2);
2086 END IF;
2087 ELSE
2088 x_line_locs.price_override_tbl(i) :=0.0;
2089 END IF;
2090
2091 -- set outsourced_assembly to 2 since it is a required field
2092 x_line_locs.outsourced_assembly_tbl(i) := 2;
2093 END LOOP;
2094
2095 IF (PO_LOG.d_proc) THEN
2096 PO_LOG.proc_end (d_module);
2097 END IF;
2098
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 PO_MESSAGE_S.add_exc_msg
2102 (
2103 p_pkg_name => d_pkg_name,
2104 p_procedure_name => d_api_name || '.' || d_position
2105 );
2106 RAISE;
2107 END default_locs_for_blanket;
2108
2109 -----------------------------------------------------------------------
2110 --Start of Comments
2111 --Name: default_locs_for_quotation
2112 --Function:
2113 -- default logic on line location attributes for Quotation
2114 --Parameters:
2115 --IN:
2116 -- p_key
2117 -- identifier in the temp table on the derived result
2118 -- p_index_tbl
2119 -- indexes of the records
2120 --IN OUT:
2121 --x_line_locs
2122 -- variable to hold all the line location attribute values in one batch;
2123 -- default result are saved inside the variable
2124 --OUT:
2125 --End of Comments
2126 ------------------------------------------------------------------------
2127 PROCEDURE default_locs_for_quotation
2128 (
2129 p_key IN po_session_gt.key%TYPE,
2130 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2131 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
2132 ) IS
2133
2134 d_api_name CONSTANT VARCHAR2(30) := 'default_locs_for_quotation';
2135 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2136 d_position NUMBER;
2137
2138 -- receiving control default values
2139 l_enforce_ship_to_loc_code VARCHAR2(25);
2140 l_allow_sub_receipts_flag VARCHAR2(1);
2141 l_receiving_routing_id NUMBER;
2142 l_qty_rcv_tolerance NUMBER;
2143 l_qty_rcv_exception VARCHAR2(25);
2144 l_days_early_receipt_allowed NUMBER;
2145 l_days_late_receipt_allowed NUMBER;
2146 l_rct_days_exception_code VARCHAR2(25);
2147 BEGIN
2148 d_position := 0;
2149
2150 IF (PO_LOG.d_proc) THEN
2151 PO_LOG.proc_begin(d_module);
2152 END IF;
2153
2154 -- default inspection_required_flag from item_id
2155 default_inspect_required_flag
2156 (
2157 p_key => p_key,
2158 p_index_tbl => p_index_tbl,
2159 p_item_id_tbl => x_line_locs.ln_item_id_tbl,
2160 x_inspection_required_flag_tbl => x_line_locs.inspection_required_flag_tbl
2161 );
2162
2163 d_position := 10;
2164
2165 -- default ship_to_organization_id from ship_to_location_id
2166 default_ship_to_org_id
2167 (
2168 p_key => p_key,
2169 p_index_tbl => p_index_tbl,
2170 p_ship_to_loc_id_tbl => x_line_locs.ship_to_loc_id_tbl,
2171 x_ship_to_org_id_tbl => x_line_locs.ship_to_org_id_tbl
2172 );
2173
2174 d_position := 20;
2175
2176 -- default on row by row base
2177 FOR i IN 1..x_line_locs.rec_count
2178 LOOP
2179 IF (PO_LOG.d_stmt) THEN
2180 PO_LOG.stmt(d_module, d_position, 'default for quotation index', i);
2181 END IF;
2182
2183 -- bug5307208
2184 -- Truncate start/end date of the price breaks
2185 x_line_locs.start_date_tbl(i) := TRUNC(x_line_locs.start_date_tbl(i));
2186 x_line_locs.end_date_tbl(i) := TRUNC(x_line_locs.end_date_tbl(i));
2187
2188 -- default shipment_type according to document type
2189 x_line_locs.shipment_type_tbl(i) :=
2190 NVL(x_line_locs.shipment_type_tbl(i), 'QUOTATION');
2191
2192 -- default shipment_num to max_shipement_num+1
2193 x_line_locs.shipment_num_tbl(i) :=
2194 NVL(x_line_locs.shipment_num_tbl(i),
2195 PO_PDOI_MAINPROC_UTL_PVT.get_next_shipment_num(x_line_locs.ln_po_line_id_tbl(i)));
2196
2197 -- default line_location_id from sequence
2198 x_line_locs.line_loc_id_tbl(i) :=
2199 NVL(x_line_locs.line_loc_id_tbl(i),
2200 PO_PDOI_MAINPROC_UTL_PVT.get_next_line_loc_id);
2201
2202 IF (PO_LOG.d_stmt) THEN
2203 PO_LOG.stmt(d_module, d_position, 'shipment type',
2204 x_line_locs.shipment_type_tbl(i));
2205 PO_LOG.stmt(d_module, d_position, 'shipment num',
2209 END IF;
2206 x_line_locs.shipment_num_tbl(i));
2207 PO_LOG.stmt(d_module, d_position, 'line loc id',
2208 x_line_locs.line_loc_id_tbl(i));
2210
2211 -- default terms_id from header,
2212 x_line_locs.terms_id_tbl(i) :=
2213 NVL(x_line_locs.terms_id_tbl(i), x_line_locs.hd_terms_id_tbl(i));
2214
2215 d_position := 30;
2216
2217 -- default freight_carrier, fob and freight_term from header
2218 x_line_locs.fob_tbl(i) :=
2219 NVL(x_line_locs.fob_tbl(i), x_line_locs.hd_fob_tbl(i));
2220 x_line_locs.freight_carrier_tbl(i) :=
2221 NVL(x_line_locs.freight_carrier_tbl(i), x_line_locs.hd_freight_carrier_tbl(i));
2222 x_line_locs.freight_term_tbl(i) :=
2223 NVL(x_line_locs.freight_term_tbl(i), x_line_locs.hd_freight_term_tbl(i));
2224
2225 -- default inspection_required_flag from system
2226 x_line_locs.inspection_required_flag_tbl(i) :=
2227 NVL(x_line_locs.inspection_required_flag_tbl(i),
2228 PO_PDOI_PARAMS.g_sys.inspection_required_flag);
2229
2230 -- default receipt_required_flag from system
2231 x_line_locs.receipt_required_flag_tbl(i) :=
2232 NVL(x_line_locs.receipt_required_flag_tbl(i),
2233 PO_PDOI_PARAMS.g_sys.receiving_flag);
2234
2235 -- default allow_sub_receipts_flag, qty_rcv_tolerance, and
2236 -- qty_rcv_exception_code
2237 -- only call the rcv procedure when necessary
2238 IF (x_line_locs.qty_rcv_tolerance_tbl(i) IS NULL OR
2239 x_line_locs.qty_rcv_exception_code_tbl(i) IS NULL) THEN
2240
2241 d_position := 40;
2242
2243 RCV_CORE_S.get_receiving_controls
2244 (
2245 x_line_loc_id => x_line_locs.line_loc_id_tbl(i),
2246 x_item_id => x_line_locs.ln_item_id_tbl(i),
2247 x_vendor_id => x_line_locs.hd_vendor_id_tbl(i),
2248 x_org_id => x_line_locs.ship_to_org_id_tbl(i),
2249 x_enforce_ship_to_loc => l_enforce_ship_to_loc_code,
2250 x_allow_substitutes => l_allow_sub_receipts_flag,
2251 x_routing_id => l_receiving_routing_id,
2252 x_qty_rcv_tolerance => l_qty_rcv_tolerance,
2253 x_qty_rcv_exception => l_qty_rcv_exception,
2254 x_days_early_receipt => l_days_early_receipt_allowed,
2255 x_days_late_receipt => l_days_late_receipt_allowed,
2256 x_rcv_date_exception => l_rct_days_exception_code
2257 );
2258
2259 -- default qty_rcv_tolerance from receiving controls
2260 x_line_locs.qty_rcv_tolerance_tbl(i) :=
2261 NVL(x_line_locs.qty_rcv_tolerance_tbl(i), l_qty_rcv_tolerance);
2262
2263 -- default qty_rcv_exception_code from receiving controls
2264 x_line_locs.qty_rcv_exception_code_tbl(i) :=
2265 NVL(x_line_locs.qty_rcv_exception_code_tbl(i), l_qty_rcv_exception);
2266
2267 IF (PO_LOG.d_stmt) THEN
2268 PO_LOG.stmt(d_module, d_position, 'qty_rcv_tolerance',
2269 x_line_locs.qty_rcv_tolerance_tbl(i));
2270 PO_LOG.stmt(d_module, d_position, 'qty_rcv_exception_code',
2271 x_line_locs.qty_rcv_exception_code_tbl(i));
2272 END IF;
2273 END IF;
2274
2275 -- set accrue_on_receipt_flag and firm_flag to NULL
2276 x_line_locs.accrue_on_receipt_flag_tbl(i) := NULL;
2277 x_line_locs.firm_flag_tbl(i) := NULL;
2278
2279 -- ignore user's input of tax_name for quotation
2280 x_line_locs.tax_name_tbl(i) := NULL;
2281
2282 -- set value_basis from order_type_lookup_code on line level
2283 x_line_locs.value_basis_tbl(i) := x_line_locs.ln_order_type_lookup_code_tbl(i);
2284
2285 -- set matching_basis to NULL
2286 x_line_locs.matching_basis_tbl(i) := NULL;
2287
2288 -- set outsourced_assembly to 2 since it is a required field
2289 x_line_locs.outsourced_assembly_tbl(i) := 2;
2290 END LOOP;
2291
2292 IF (PO_LOG.d_proc) THEN
2293 PO_LOG.proc_end (d_module);
2294 END IF;
2295
2296 EXCEPTION
2297 WHEN OTHERS THEN
2298 PO_MESSAGE_S.add_exc_msg
2299 (
2300 p_pkg_name => d_pkg_name,
2301 p_procedure_name => d_api_name || '.' || d_position
2302 );
2303 RAISE;
2304 END default_locs_for_quotation;
2305
2306 -----------------------------------------------------------------------
2307 --Start of Comments
2308 --Name: default_inspect_required_flag
2309 --Function:
2310 -- logic to default inspection_required_flag from item_id
2311 -- in a batch mode
2312 --Parameters:
2313 --IN:
2314 -- p_key
2315 -- identifier in the temp table on the derived result
2316 -- p_index_tbl
2317 -- indexes of the records
2318 -- p_item_id_tbl
2319 -- list of item_id values in current batch
2320 --IN OUT:
2321 -- x_inspection_required_flag_tbl
2322 -- contains the default result if original value is null;
2323 -- original value won't be changed if it is not null
2324 --OUT:
2325 --End of Comments
2326 ------------------------------------------------------------------------
2327 PROCEDURE default_inspect_required_flag
2328 (
2329 p_key IN po_session_gt.key%TYPE,
2330 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2331 p_item_id_tbl IN PO_TBL_NUMBER,
2332 x_inspection_required_flag_tbl IN OUT NOCOPY PO_TBL_VARCHAR1
2333 ) IS
2334
2338
2335 d_api_name CONSTANT VARCHAR2(30) := 'default_inspect_required_flag';
2336 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2337 d_position NUMBER;
2339 -- variable to hold the default query result
2340 l_index_tbl PO_TBL_NUMBER;
2341 l_result_tbl PO_TBL_VARCHAR1;
2342 BEGIN
2343 d_position := 0;
2344
2345 IF (PO_LOG.d_proc) THEN
2346 PO_LOG.proc_begin(d_module);
2347 END IF;
2348
2349 -- retrieve the default value from database
2350 FORALL i IN 1..p_index_tbl.COUNT
2351 INSERT INTO po_session_gt(key, num1, char1)
2352 SELECT p_key,
2353 p_index_tbl(i),
2354 inspection_required_flag
2355 FROM mtl_system_items
2356 WHERE p_item_id_tbl(i) IS NOT NULL
2357 AND x_inspection_required_flag_tbl(i) IS NULL
2358 AND inventory_item_id = p_item_id_tbl(i)
2359 AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
2360
2361 d_position := 10;
2362
2363 -- get result from temp table
2364 DELETE FROM po_session_gt
2365 WHERE key = p_key
2366 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2367
2368 d_position := 20;
2369
2370 -- set result back to x_inspection_required_flag_tbl
2371 FOR i IN 1..l_index_tbl.COUNT
2372 LOOP
2373 IF (PO_LOG.d_stmt) THEN
2374 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2375 PO_LOG.stmt(d_module, d_position, 'default inspection required flag',
2376 l_result_tbl(i));
2377 END IF;
2378
2379 x_inspection_required_flag_tbl(l_index_tbl(i)) := l_result_tbl(i);
2380 END LOOP;
2381
2382 IF (PO_LOG.d_proc) THEN
2383 PO_LOG.proc_end (d_module);
2384 END IF;
2385
2386 EXCEPTION
2387 WHEN OTHERS THEN
2388 PO_MESSAGE_S.add_exc_msg
2389 (
2390 p_pkg_name => d_pkg_name,
2391 p_procedure_name => d_api_name || '.' || d_position
2392 );
2393 RAISE;
2394 END default_inspect_required_flag;
2395
2396 -----------------------------------------------------------------------
2397 --Start of Comments
2398 --Name: default_ship_to_org_id
2399 --Function:
2400 -- logic to default ship_to_organziation_id from ship_to_location_id
2401 -- in batch mode
2402 --Parameters:
2403 --IN:
2404 -- p_key
2405 -- identifier in the temp table on the derived result
2406 -- p_index_tbl
2407 -- indexes of the records
2408 -- p_ship_to_loc_id_tbl
2409 -- list of ship_to_location_id values in current batch
2410 --IN OUT:
2411 -- x_ship_to_org_id_tbl
2412 -- contains the default result if original value is null;
2413 -- original value won't be changed if it is not null
2414 --OUT:
2415 --End of Comments
2416 ------------------------------------------------------------------------
2417 PROCEDURE default_ship_to_org_id
2418 (
2419 p_key IN po_session_gt.key%TYPE,
2420 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2421 p_ship_to_loc_id_tbl IN PO_TBL_NUMBER,
2422 x_ship_to_org_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2423 ) IS
2424
2425 d_api_name CONSTANT VARCHAR2(30) := 'default_ship_to_org_id';
2426 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2427 d_position NUMBER;
2428
2429 -- variable to hold the default query result
2430 l_index_tbl PO_TBL_NUMBER;
2431 l_result_tbl PO_TBL_NUMBER;
2432 BEGIN
2433 d_position := 0;
2434
2435 IF (PO_LOG.d_proc) THEN
2436 PO_LOG.proc_begin(d_module);
2437 END IF;
2438
2439 -- default ship_to_organization_id from ship_to_location_id
2440 FORALL i IN 1..p_index_tbl.COUNT
2441 INSERT INTO po_session_gt(key, num1, num2)
2442 SELECT p_key,
2443 p_index_tbl(i),
2444 inventory_organization_id
2445 FROM hr_locations_v
2446 WHERE p_ship_to_loc_id_tbl(i) IS NOT NULL
2447 AND x_ship_to_org_id_tbl(i) IS NULL
2448 AND location_id = p_ship_to_loc_id_tbl(i)
2449 AND ship_to_site_flag = 'Y';
2450
2451 d_position := 10;
2452
2453 -- get result from temp table
2454 DELETE FROM po_session_gt
2455 WHERE key = p_key
2456 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2457
2458 d_position := 20;
2459
2460 -- set result in x_ship_to_org_id_tbl
2461 FOR i IN 1..l_index_tbl.COUNT
2462 LOOP
2463 IF (PO_LOG.d_stmt) THEN
2464 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2465 PO_LOG.stmt(d_module, d_position, 'default ship_to org id',
2466 l_result_tbl(i));
2467 END IF;
2468
2469 x_ship_to_org_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2470 END LOOP;
2471
2472 IF (PO_LOG.d_proc) THEN
2473 PO_LOG.proc_end (d_module);
2474 END IF;
2475
2476 EXCEPTION
2477 WHEN OTHERS THEN
2478 PO_MESSAGE_S.add_exc_msg
2479 (
2480 p_pkg_name => d_pkg_name,
2481 p_procedure_name => d_api_name || '.' || d_position
2482 );
2483 RAISE;
2484 END default_ship_to_org_id;
2485
2486 -----------------------------------------------------------------------
2487 --Start of Comments
2488 --Name: default_close_tolerances
2489 --Function:
2493 -- 2. default from item_id and default inventory org id
2490 -- logic to default invoice_close_tolerance and receive_close_tolerance
2491 -- in batch mode. The default order is as follows:
2492 -- 1. default from item_id and ship_to_organziation_id
2494 -- 3. default receive_close_tolerance from line_type_id
2495 -- 4. default from system parameters
2496 -- 5. default to 0
2497 --Parameters:
2498 --IN:
2499 -- p_key
2500 -- identifier in the temp table on the derived result
2501 -- p_index_tbl
2502 -- indexes of the records
2503 -- p_item_id
2504 -- list of item_id values in current batch
2505 -- p_ship_to_org_id_tbl
2506 -- list of ship_to_organization_id values in current batch
2507 -- p_line_type_id
2508 -- list of line_type_id values in current batch
2509 --IN OUT:
2510 -- x_invoice_close_tolerance_tbl
2511 -- contains the default result if original value is null;
2512 -- original value won't be changed if it is not null
2513 -- x_receive_close_tolerance_tbl
2514 -- contains the default result if original value is null;
2515 -- original value won't be changed if it is not null
2516 --OUT:
2517 --End of Comments
2518 ------------------------------------------------------------------------
2519 PROCEDURE default_close_tolerances
2520 (
2521 p_key IN po_session_gt.key%TYPE,
2522 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2523 p_item_id_tbl IN PO_TBL_NUMBER,
2524 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
2525 p_line_type_id_tbl IN PO_TBL_NUMBER,
2526 x_invoice_close_tolerance_tbl IN OUT NOCOPY PO_TBL_NUMBER,
2527 x_receive_close_tolerance_tbl IN OUT NOCOPY PO_TBL_NUMBER
2528 ) IS
2529
2530 d_api_name CONSTANT VARCHAR2(30) := 'default_close_tolerances';
2531 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2532 d_position NUMBER;
2533
2534 -- variable to hold the default query result
2535 l_index_tbl PO_TBL_NUMBER;
2536 l_invoice_tolerance_tbl PO_TBL_NUMBER;
2537 l_receive_tolerance_tbl PO_TBL_NUMBER;
2538
2539 -- temp index value
2540 l_index NUMBER;
2541 BEGIN
2542 d_position := 0;
2543
2544 IF (PO_LOG.d_proc) THEN
2545 PO_LOG.proc_begin(d_module);
2546 END IF;
2547
2548 -- first, default from item_id and ship_to_organization_id
2549 FORALL i IN 1..p_index_tbl.COUNT
2550 INSERT INTO po_session_gt(key, num1, num2, num3)
2551 SELECT p_key,
2552 p_index_tbl(i),
2553 invoice_close_tolerance,
2554 receive_close_tolerance
2555 FROM mtl_system_items
2556 WHERE p_item_id_tbl(i) IS NOT NULL
2557 AND (x_invoice_close_tolerance_tbl(i) IS NULL OR
2558 x_receive_close_tolerance_tbl(i) IS NULL)
2559 AND inventory_item_id = p_item_id_tbl(i)
2560 AND organization_id = p_ship_to_org_id_tbl(i);
2561
2562 d_position := 10;
2563
2564 DELETE FROM po_session_gt
2565 WHERE key = p_key
2566 RETURNING num1, num2, num3 BULK COLLECT INTO
2567 l_index_tbl, l_invoice_tolerance_tbl, l_receive_tolerance_tbl;
2568
2569 d_position := 20;
2570
2571 FOR i IN 1..l_index_tbl.COUNT
2572 LOOP
2573 l_index := l_index_tbl(i);
2574
2575 x_invoice_close_tolerance_tbl(l_index) :=
2576 NVL(x_invoice_close_tolerance_tbl(l_index),
2577 l_invoice_tolerance_tbl(i));
2578 x_receive_close_tolerance_tbl(l_index) :=
2579 NVL(x_receive_close_tolerance_tbl(l_index),
2580 l_receive_tolerance_tbl(i));
2581
2582 IF (PO_LOG.d_stmt) THEN
2583 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2584 PO_LOG.stmt(d_module, d_position, 'invoice close tolerance',
2585 x_invoice_close_tolerance_tbl(l_index));
2586 PO_LOG.stmt(d_module, d_position, 'receive close tolerance',
2587 x_receive_close_tolerance_tbl(l_index));
2588 END IF;
2589 END LOOP;
2590
2591 d_position := 30;
2592
2593 -- Second, default from item_id and default inventory org id
2594 FORALL i IN 1..p_index_tbl.COUNT
2595 INSERT INTO po_session_gt(key, num1, num2, num3)
2596 SELECT p_key,
2597 p_index_tbl(i),
2598 invoice_close_tolerance,
2599 receive_close_tolerance
2600 FROM mtl_system_items
2601 WHERE p_item_id_tbl(i) IS NOT NULL
2602 AND (x_invoice_close_tolerance_tbl(i) IS NULL OR
2603 x_receive_close_tolerance_tbl(i) IS NULL)
2604 AND inventory_item_id = p_item_id_tbl(i)
2605 AND organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
2606
2607 d_position := 40;
2608
2609 DELETE FROM po_session_gt
2610 WHERE key = p_key
2611 RETURNING num1, num2, num3 BULK COLLECT INTO
2612 l_index_tbl, l_invoice_tolerance_tbl, l_receive_tolerance_tbl;
2613
2614 d_position := 50;
2615
2616 FOR i IN 1..l_index_tbl.COUNT
2617 LOOP
2618 l_index := l_index_tbl(i);
2619
2620 x_invoice_close_tolerance_tbl(l_index) :=
2621 NVL(x_invoice_close_tolerance_tbl(l_index),
2622 l_invoice_tolerance_tbl(i));
2623 x_receive_close_tolerance_tbl(l_index) :=
2624 NVL(x_receive_close_tolerance_tbl(l_index),
2625 l_receive_tolerance_tbl(i));
2626
2627 IF (PO_LOG.d_stmt) THEN
2628 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2632 x_receive_close_tolerance_tbl(l_index));
2629 PO_LOG.stmt(d_module, d_position, 'invoice close tolerance',
2630 x_invoice_close_tolerance_tbl(l_index));
2631 PO_LOG.stmt(d_module, d_position, 'receive close tolerance',
2633 END IF;
2634 END LOOP;
2635
2636 d_position := 60;
2637
2638 -- Third, default receive_close_tolerance from line_type_id
2639 FORALL i IN 1..p_index_tbl.COUNT
2640 INSERT INTO po_session_gt(key, num1, num2)
2641 SELECT p_key,
2642 p_index_tbl(i),
2643 receipt_close
2644 FROM po_line_types_v
2645 WHERE p_line_type_id_tbl(i) IS NOT NULL
2646 AND x_receive_close_tolerance_tbl(i) IS NULL
2647 AND line_type_id = p_line_type_id_tbl(i);
2648
2649 d_position := 70;
2650
2651 DELETE FROM po_session_gt
2652 WHERE key = p_key
2653 RETURNING num1, num2 BULK COLLECT INTO
2654 l_index_tbl, l_receive_tolerance_tbl;
2655
2656 FOR i IN 1..l_index_tbl.COUNT
2657 LOOP
2658 IF (PO_LOG.d_stmt) THEN
2659 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2660 PO_LOG.stmt(d_module, d_position, 'receive close tolerance',
2661 l_receive_tolerance_tbl(i));
2662 END IF;
2663
2664 x_receive_close_tolerance_tbl(l_index_tbl(i)) := l_receive_tolerance_tbl(i);
2665 END LOOP;
2666
2667 d_position := 80;
2668
2669 -- Last, default from system parameters using loop
2670 FOR i IN 1..p_index_tbl.COUNT
2671 LOOP
2672 IF (PO_LOG.d_stmt) THEN
2673 PO_LOG.stmt(d_module, d_position, 'index', i);
2674 PO_LOG.stmt(d_module, d_position, 'invoice close tolerance',
2675 x_invoice_close_tolerance_tbl(i));
2676 PO_LOG.stmt(d_module, d_position, 'receive close tolerance',
2677 x_receive_close_tolerance_tbl(i));
2678 END IF;
2679
2680 x_invoice_close_tolerance_tbl(i) :=
2681 COALESCE(x_invoice_close_tolerance_tbl(i),
2682 PO_PDOI_PARAMS.g_sys.invoice_close_tolerance,
2683 0);
2684 x_receive_close_tolerance_tbl(i) :=
2685 COALESCE(x_receive_close_tolerance_tbl(i),
2686 PO_PDOI_PARAMS.g_sys.receive_close_tolerance,
2687 0);
2688 END LOOP;
2689
2690 IF (PO_LOG.d_proc) THEN
2691 PO_LOG.proc_end (d_module);
2692 END IF;
2693
2694 EXCEPTION
2695 WHEN OTHERS THEN
2696 PO_MESSAGE_S.add_exc_msg
2697 (
2698 p_pkg_name => d_pkg_name,
2699 p_procedure_name => d_api_name || '.' || d_position
2700 );
2701 RAISE;
2702 END default_close_tolerances;
2703
2704 -----------------------------------------------------------------------
2705 --Start of Comments
2706 --Name: default_invoice_match_options
2707 --Function:
2708 -- logic to default invoice_match_options
2709 -- in batch mode. The default order is as follows:
2710 -- 1. default from vendor_site_id
2711 -- 2. default from vendor id
2712 -- 3. default from system parameters
2713 --Parameters:
2714 --IN:
2715 -- p_key
2716 -- identifier in the temp table on the derived result
2717 -- p_index_tbl
2718 -- indexes of the records
2719 -- p_vendor_id_tbl
2720 -- list of vendor_id values in current batch
2721 -- p_vendor_site_id_tbl
2722 -- list of vendor_site_id values in current batch
2723 --IN OUT:
2724 -- x_match_option_tbl
2725 -- contains the default result if original value is null;
2726 -- original value won't be changed if it is not null
2727 --OUT:
2728 --End of Comments
2729 ------------------------------------------------------------------------
2730 PROCEDURE default_invoice_match_options
2731 (
2732 p_key IN po_session_gt.key%TYPE,
2733 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2734 p_vendor_id_tbl IN PO_TBL_NUMBER,
2735 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
2736 x_match_option_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
2737 ) IS
2738
2739 d_api_name CONSTANT VARCHAR2(30) := 'default_invoice_match_options';
2740 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2741 d_position NUMBER;
2742
2743 -- variable to hold the default query result
2744 l_index_tbl PO_TBL_NUMBER;
2745 l_result_tbl PO_TBL_VARCHAR30;
2746 BEGIN
2747 d_position := 0;
2748
2749 IF (PO_LOG.d_proc) THEN
2750 PO_LOG.proc_begin(d_module);
2751 END IF;
2752
2753 -- first, get default value from vendor_site
2754 FORALL i IN 1..p_index_tbl.COUNT
2755 INSERT INTO po_session_gt(key, num1, char1)
2756 SELECT p_key,
2757 p_index_tbl(i),
2758 match_option
2759 FROM po_vendor_sites
2760 WHERE p_vendor_site_id_tbl(i) IS NOT NULL
2761 AND x_match_option_tbl(i) IS NULL
2762 AND vendor_site_id = p_vendor_site_id_tbl(i);
2763
2764 d_position := 10;
2765
2766 DELETE FROM po_session_gt
2767 WHERE key = p_key
2768 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2769
2770 FOR i IN 1..l_index_tbl.COUNT
2771 LOOP
2772 IF (PO_LOG.d_stmt) THEN
2773 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2777 x_match_option_tbl(l_index_tbl(i)) := l_result_tbl(i);
2774 PO_LOG.stmt(d_module, d_position, 'new match option', l_result_tbl(i));
2775 END IF;
2776
2778 END LOOP;
2779
2780 d_position := 20;
2781
2782 -- next, default value from vendor, same as vendor_site
2783 FORALL i IN 1..p_index_tbl.COUNT
2784 INSERT INTO po_session_gt(key, num1, char1)
2785 SELECT p_key,
2786 p_index_tbl(i),
2787 match_option
2788 FROM po_vendors
2789 WHERE p_vendor_id_tbl(i) IS NOT NULL
2790 AND x_match_option_tbl(i) IS NULL
2791 AND vendor_id = p_vendor_id_tbl(i);
2792
2793 d_position := 30;
2794
2795 DELETE FROM po_session_gt
2796 WHERE key = p_key
2797 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2798
2799 FOR i IN 1..l_index_tbl.COUNT
2800 LOOP
2801 IF (PO_LOG.d_stmt) THEN
2802 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2803 PO_LOG.stmt(d_module, d_position, 'new match option', l_result_tbl(i));
2804 END IF;
2805
2806 x_match_option_tbl(l_index_tbl(i)) := l_result_tbl(i);
2807 END LOOP;
2808
2809 d_position := 40;
2810
2811 -- last, get default value from financial system parameter using loop
2812 FOR i IN 1..p_index_tbl.COUNT
2813 LOOP
2814 IF (PO_LOG.d_stmt) THEN
2815 PO_LOG.stmt(d_module, d_position, 'index', i);
2816 PO_LOG.stmt(d_module, d_position, 'curr match option', x_match_option_tbl(i));
2817 END IF;
2818
2819 x_match_option_tbl(i) :=
2820 NVL(x_match_option_tbl(i), PO_PDOI_PARAMS.g_sys.invoice_match_option);
2821 END LOOP;
2822
2823 IF (PO_LOG.d_proc) THEN
2824 PO_LOG.proc_end (d_module);
2825 END IF;
2826
2827 EXCEPTION
2828 WHEN OTHERS THEN
2829 PO_MESSAGE_S.add_exc_msg
2830 (
2831 p_pkg_name => d_pkg_name,
2832 p_procedure_name => d_api_name || '.' || d_position
2833 );
2834 RAISE;
2835 END default_invoice_match_options;
2836
2837 -----------------------------------------------------------------------
2838 --Start of Comments
2839 --Name: default_accrue_on_receipt_flag
2840 --Function:
2841 -- logic to default accrue_on_receipt_flag based on item info
2842 --Parameters:
2843 --IN:
2844 -- p_key
2845 -- identifier in the temp table on the derived result
2846 -- p_index_tbl
2847 -- indexes of the records
2848 -- p_itemid_tbl
2849 -- list of item_id values in current batch
2850 -- p_ship_to_org_id_tbl
2851 -- list of ship_to_org_id values in current batch
2852 -- p_receipt_required_flag_tbl
2853 -- list of receipt_required_flag values in current batch
2854 --IN OUT:
2855 -- x_accrue_on_receipt_flag_tbl
2856 -- contains the default result if original value is null;
2857 -- original value won't be changed if it is not null
2858 --OUT:
2859 --End of Comments
2860 ------------------------------------------------------------------------
2861 PROCEDURE default_accrue_on_receipt_flag
2862 (
2863 p_key IN po_session_gt.key%TYPE,
2864 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2865 p_item_id_tbl IN PO_TBL_NUMBER,
2866 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
2867 p_receipt_required_flag_tbl IN PO_TBL_VARCHAR1,
2868 x_accrue_on_receipt_flag_tbl IN OUT NOCOPY PO_TBL_VARCHAR1
2869 ) IS
2870
2871 d_api_name CONSTANT VARCHAR2(30) := 'default_accrue_on_receipt_flag';
2872 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2873 d_position NUMBER;
2874
2875 -- variable to hold the default query result
2876 l_index_tbl PO_TBL_NUMBER;
2877 l_outside_op_flag_tbl PO_TBL_VARCHAR1;
2878 l_stock_enabled_flag_tbl PO_TBL_VARCHAR1;
2879
2880 l_item_status VARCHAR2(1);
2881 l_index NUMBER;
2882 BEGIN
2883 d_position := 0;
2884
2885 IF (PO_LOG.d_proc) THEN
2886 PO_LOG.proc_begin(d_module);
2887 END IF;
2888
2889 -- get default values from item
2890 -- first, default from item_id and ship_to_organization_id
2891 FORALL i IN 1..p_index_tbl.COUNT
2892 INSERT INTO po_session_gt(key, num1, char1, char2)
2893 SELECT p_key,
2894 p_index_tbl(i),
2895 outside_operation_flag,
2896 nvl(stock_enabled_flag,'N')
2897 FROM mtl_system_items
2898 WHERE p_item_id_tbl(i) IS NOT NULL
2899 AND x_accrue_on_receipt_flag_tbl(i) IS NULL
2900 AND inventory_item_id = p_item_id_tbl(i)
2901 AND organization_id = p_ship_to_org_id_tbl(i);
2902
2903 d_position := 10;
2904
2905 DELETE FROM po_session_gt
2906 WHERE key = p_key
2907 RETURNING num1, char1, char2 BULK COLLECT INTO
2908 l_index_tbl, l_outside_op_flag_tbl, l_stock_enabled_flag_tbl;
2909
2910 FOR i IN 1..l_index_tbl.COUNT
2911 LOOP
2912 d_position := 20;
2913
2914 l_index := l_index_tbl(i);
2915
2916 -- get item status
2917 IF (l_outside_op_flag_tbl(i) = 'Y') THEN
2918 l_item_status := 'O'; -- Outside Processing
2919 ELSE
2920 IF (l_stock_enabled_flag_tbl(i) = 'Y') THEN
2921 l_item_status := 'E'; -- Inventory
2922 ELSE
2923 l_item_status := 'D'; -- Expense
2924 END IF;
2928 PO_LOG.stmt(d_module, d_position, 'index', l_index);
2925 END IF;
2926
2927 IF (PO_LOG.d_stmt) THEN
2929 PO_LOG.stmt(d_module, d_position, 'item status', l_item_status);
2930 END IF;
2931
2932 -- set default value
2933 IF (l_item_status = 'O') THEN
2934 x_accrue_on_receipt_flag_tbl(l_index) := 'Y';
2935 ELSIF (l_item_status = 'E') THEN
2936 IF (PO_LOG.d_stmt) THEN
2937 PO_LOG.stmt(d_module, d_position, 'INV installed',
2938 PO_PDOI_PARAMS.g_product.inv_installed);
2939 PO_LOG.stmt(d_module, d_position, 'default expense accrual code',
2940 PO_PDOI_PARAMS.g_sys.expense_accrual_code);
2941 END IF;
2942
2943 IF (PO_PDOI_PARAMS.g_product.inv_installed = FND_API.g_TRUE) then
2944 x_accrue_on_receipt_flag_tbl(l_index) := 'Y';
2945 ELSE
2946 IF (PO_PDOI_PARAMS.g_sys.expense_accrual_code = 'PERIOD END') then
2947 x_accrue_on_receipt_flag_tbl(l_index) := 'N';
2948 ELSE
2949 x_accrue_on_receipt_flag_tbl(l_index) :=
2950 p_receipt_required_flag_tbl(l_index);
2951 END IF;
2952 END IF;
2953 ELSE -- l_item_status := 'D'
2954 IF (PO_PDOI_PARAMS.g_sys.expense_accrual_code = 'PERIOD END') THEN
2955 x_accrue_on_receipt_flag_tbl(l_index) := 'N';
2956 ELSE
2957 x_accrue_on_receipt_flag_tbl(l_index) :=
2958 p_receipt_required_flag_tbl(l_index);
2959 END IF;
2960 END IF;
2961 END LOOP;
2962
2963 IF (PO_LOG.d_proc) THEN
2964 PO_LOG.proc_end (d_module);
2965 END IF;
2966
2967 EXCEPTION
2968 WHEN OTHERS THEN
2969 PO_MESSAGE_S.add_exc_msg
2970 (
2971 p_pkg_name => d_pkg_name,
2972 p_procedure_name => d_api_name || '.' || d_position
2973 );
2974 RAISE;
2975 END default_accrue_on_receipt_flag;
2976
2977 -----------------------------------------------------------------------
2978 --Start of Comments
2979 --Name: default_secondary_unit_of_meas
2980 --Function:
2981 -- logic to default secondary_unit_of_measure from item_id and
2982 -- ship_to_organization_id in a batch mode
2983 --Parameters:
2984 --IN:
2985 -- p_key
2986 -- identifier in the temp table on the defaulted result
2987 -- p_index_tbl
2988 -- indexes of the records
2989 -- p_item_id_tbl
2990 -- list of item_id values in current batch
2991 -- p_ship_to_org_id_tbl
2992 -- list of ship_to_organization_id values in current batch
2993 --IN OUT:
2994 -- x_secondary_unit_of_meas_tbl
2995 -- contains the default result if original value is null;
2996 -- original value won't be changed if it is not null
2997 --OUT:
2998 --End of Comments
2999 ------------------------------------------------------------------------
3000 PROCEDURE default_secondary_unit_of_meas
3001 (
3002 p_key IN po_session_gt.key%TYPE,
3003 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3004 p_item_id_tbl IN PO_TBL_NUMBER,
3005 p_ship_to_org_id_tbl IN PO_TBL_NUMBER,
3006 x_secondary_unit_of_meas_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
3007 ) IS
3008
3009 d_api_name CONSTANT VARCHAR2(30) := 'default_secondary_unit_of_meas';
3010 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3011 d_position NUMBER;
3012
3013 -- variable to hold the default query result
3014 l_index_tbl PO_TBL_NUMBER;
3015 l_result_tbl PO_TBL_VARCHAR30;
3016 BEGIN
3017 d_position := 0;
3018
3019 IF (PO_LOG.d_proc) THEN
3020 PO_LOG.proc_begin(d_module, 'p_item_id_tbl', p_item_id_tbl);
3021 PO_LOG.proc_begin(d_module, 'p_ship_to_org_id_tbl', p_ship_to_org_id_tbl);
3022 END IF;
3023
3024 -- retrieve the default value from database
3025 FORALL i IN 1..p_index_tbl.COUNT
3026 INSERT INTO po_session_gt(key, num1, char1)
3027 SELECT p_key,
3028 p_index_tbl(i),
3029 uom.unit_of_measure
3030 FROM mtl_system_items item,
3031 mtl_units_of_measure uom
3032 WHERE p_item_id_tbl(i) IS NOT NULL
3033 AND p_ship_to_org_id_tbl(i) IS NOT NULL
3034 AND x_secondary_unit_of_meas_tbl(i) IS NULL
3035 AND item.inventory_item_id = p_item_id_tbl(i)
3036 AND item.organization_id = p_ship_to_org_id_tbl(i)
3037 AND item.tracking_quantity_ind = 'PS'
3038 AND item.secondary_uom_code = uom.uom_code;
3039
3040 d_position := 10;
3041
3042 -- get result from temp table
3043 DELETE FROM po_session_gt
3044 WHERE key = p_key
3045 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3046
3047 d_position := 20;
3048
3049 -- set result back to x_inspection_required_flag_tbl
3050 FOR i IN 1..l_index_tbl.COUNT
3051 LOOP
3052 IF (PO_LOG.d_stmt) THEN
3053 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3054 PO_LOG.stmt(d_module, d_position, 'default secondary_unit_of_measure',
3055 l_result_tbl(i));
3056 END IF;
3057
3058 x_secondary_unit_of_meas_tbl(l_index_tbl(i)) := l_result_tbl(i);
3059 END LOOP;
3060
3061 IF (PO_LOG.d_proc) THEN
3062 PO_LOG.proc_end (d_module);
3063 END IF;
3064
3065 EXCEPTION
3066 WHEN OTHERS THEN
3067 PO_MESSAGE_S.add_exc_msg
3068 (
3072 RAISE;
3069 p_pkg_name => d_pkg_name,
3070 p_procedure_name => d_api_name || '.' || d_position
3071 );
3073 END default_secondary_unit_of_meas;
3074
3075 -----------------------------------------------------------------------
3076 --Start of Comments
3077 --Name: populate_error_flag
3078 --Function:
3079 -- corresponding value in error_flag_tbl will be set with value FND_API.G_FALSE.
3080 --Parameters:
3081 --IN:
3082 --p_results
3083 -- The validation results that contains the errored line information.
3084 --IN OUT:
3085 --p_line_locs
3086 -- The record contains the values to be validated.
3087 -- If there is error(s) on any attribute of the price differential row,
3088 -- corresponding value in error_flag_tbl will be set with value
3089 -- FND_API.g_TRUE.
3090 --OUT:
3091 --End of Comments
3092 ------------------------------------------------------------------------
3093 PROCEDURE populate_error_flag
3094 (
3095 x_results IN po_validation_results_type,
3096 x_line_locs IN OUT NOCOPY PO_PDOI_TYPES.line_locs_rec_type
3097 ) IS
3098
3099 d_api_name CONSTANT VARCHAR2(30) := 'populate_error_flag';
3100 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3101 d_position NUMBER;
3102
3103 l_index_tbl DBMS_SQL.number_table;
3104 l_index NUMBER;
3105 l_intf_header_id NUMBER;
3106 l_remove_err_line_loc_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3107 l_remove_err_line_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
3108 BEGIN
3109 d_position := 0;
3110
3111 IF (PO_LOG.d_proc) THEN
3112 PO_LOG.proc_begin(d_module);
3113 END IF;
3114
3115 FOR i IN 1 .. x_line_locs.intf_line_loc_id_tbl.COUNT LOOP
3116 l_index_tbl(x_line_locs.intf_line_loc_id_tbl(i)) := i;
3117 END LOOP;
3118
3119 d_position := 10;
3120
3121 FOR i IN 1 .. x_results.entity_id.COUNT LOOP
3122 l_index := l_index_tbl(x_results.entity_id(i));
3123
3124 -- Bug 5215781:
3125 -- set error_flag to TRUE for all remaining records if error threshold is
3126 -- hit for CATALOG UPLOAD
3127 IF (PO_PDOI_PARAMS.g_request.calling_module =
3128 PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD AND
3129 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
3130 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
3131 d_position := 20;
3132
3133 IF (PO_LOG.d_stmt) THEN
3134 PO_LOG.stmt(d_module, d_position, 'after error tolerance exceeded, collect error on index', l_index);
3135 END IF;
3136
3137 -- collect intf_line_loc_ids to remove the errors from error intf table
3138 IF (NOT PO_PDOI_PARAMS.g_errored_lines.EXISTS(x_line_locs.intf_line_id_tbl(l_index))) THEN
3139 d_position := 30;
3140
3141 l_remove_err_line_tbl.EXTEND;
3142 l_remove_err_line_loc_tbl.EXTEND;
3143 l_remove_err_line_tbl(l_remove_err_line_tbl.COUNT) := x_line_locs.intf_line_id_tbl(l_index);
3144 l_remove_err_line_loc_tbl(l_remove_err_line_loc_tbl.COUNT) := x_line_locs.intf_line_loc_id_tbl(l_index);
3145 END IF;
3146 ELSIF (x_results.result_type(i) = po_validations.c_result_type_failure) THEN
3147 d_position := 40;
3148
3149 IF (PO_LOG.d_stmt) THEN
3150 PO_LOG.stmt(d_module, d_position, 'set error on index', l_index);
3151 END IF;
3152
3153 x_line_locs.error_flag_tbl(l_index) := FND_API.g_TRUE;
3154
3155 -- Bug 5215781:
3156 -- price break level errors will be counted in line errors and threshold will be
3157 -- checked; If threshold is hit, reject all price break records that are processed
3158 -- after the current record and remove the errors from interface table for those
3159 -- records
3160 IF (NOT PO_PDOI_PARAMS.g_errored_lines.EXISTS(x_line_locs.intf_line_id_tbl(l_index))) THEN
3161 d_position := 50;
3162
3163 IF (PO_LOG.d_stmt) THEN
3164 PO_LOG.stmt(d_module, d_position, 'set error on line',
3165 x_line_locs.intf_line_id_tbl(l_index));
3166 END IF;
3167
3168 -- set corresponding line to ERROR
3169 PO_PDOI_PARAMS.g_errored_lines(x_line_locs.intf_line_id_tbl(l_index)) := 'Y';
3170
3171 l_intf_header_id := x_line_locs.intf_header_id_tbl(l_index);
3172 PO_PDOI_PARAMS.g_docs_info(l_intf_header_id).number_of_errored_lines
3173 := PO_PDOI_PARAMS.g_docs_info(l_intf_header_id).number_of_errored_lines +1;
3174
3175 -- check threshold
3176 IF (PO_PDOI_PARAMS.g_request.calling_module =
3177 PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD AND
3178 PO_PDOI_PARAMS.g_docs_info(l_intf_header_id).number_of_errored_lines
3179 = PO_PDOI_PARAMS.g_request.err_lines_tolerance) THEN
3180 IF (PO_LOG.d_stmt) THEN
3181 PO_LOG.stmt(d_module, d_position, 'threshold hit on line',
3182 x_line_locs.intf_line_id_tbl(l_index));
3183 END IF;
3184
3185 PO_PDOI_PARAMS.g_docs_info(l_intf_header_id).err_tolerance_exceeded := FND_API.g_TRUE;
3186
3187 -- reject all rows after this row
3188 FOR j IN l_index+1..x_line_locs.rec_count LOOP
3189 x_line_locs.error_flag_tbl(j) := FND_API.g_TRUE;
3190 END LOOP;
3191 END IF;
3192 END IF;
3193 END IF;
3194 END LOOP;
3195
3196 d_position := 60;
3197
3198 -- Bug 5215781:
3199 -- remove the errors for price breaks from po_interface_errors if those records are supposed to be processed
3200 -- after the price break where we hit the error tolerance; And they do not belong to any line that has
3201 -- already been counted in g_errored_lines. That means, we want to rollback some changes on po_interface_errors
3202 -- if error tolerance is reached at some point
3203 PO_INTERFACE_ERRORS_UTL.flush_errors_tbl;
3204
3205 FORALL i IN 1..l_remove_err_line_loc_tbl.COUNT
3206 DELETE FROM PO_INTERFACE_ERRORS
3207 WHERE interface_line_location_id = l_remove_err_line_loc_tbl(i)
3208 AND interface_line_id = l_remove_err_line_tbl(i);
3209
3210 d_position := 70;
3211
3212 IF (PO_LOG.d_proc) THEN
3213 PO_LOG.proc_end (d_module);
3214 END IF;
3215
3216 EXCEPTION
3217 WHEN OTHERS THEN
3218 PO_MESSAGE_S.add_exc_msg
3219 (
3220 p_pkg_name => d_pkg_name,
3221 p_procedure_name => d_api_name || '.' || d_position
3222 );
3223 RAISE;
3224 END populate_error_flag;
3225
3226 END PO_PDOI_LINE_LOC_PROCESS_PVT;