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