[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_HEADER_PROCESS_PVT
Source
1 PACKAGE BODY PO_PDOI_HEADER_PROCESS_PVT AS
2 /* $Header: PO_PDOI_HEADER_PROCESS_PVT.plb 120.30.12010000.3 2009/01/09 14:45:07 ggandhi ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_HEADER_PROCESS_PVT');
6
7
8 --------------------------------------------------------------------------
9 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
10 --------------------------------------------------------------------------
11 PROCEDURE derive_rate_type_code
12 (
13 p_key IN po_session_gt.key%TYPE,
14 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
15 p_rate_type_tbl IN PO_TBL_VARCHAR30,
16 x_rate_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
17 );
18
19 PROCEDURE derive_agent_id
20 (
21 p_key IN po_session_gt.key%TYPE,
22 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
23 p_agent_name_tbl IN PO_TBL_VARCHAR2000,
24 x_agent_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
25 );
26
27 PROCEDURE derive_vendor_site_id
28 (
29 p_key IN po_session_gt.key%TYPE,
30 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
31 p_vendor_id_tbl IN PO_TBL_NUMBER,
32 p_vendor_site_code_tbl IN PO_TBL_VARCHAR30,
33 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
34 );
35
36 PROCEDURE derive_vendor_contact_id
37 (
38 p_key IN po_session_gt.key%TYPE,
39 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
40 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
41 p_vendor_contact_tbl IN PO_TBL_VARCHAR2000,
42 x_vendor_contact_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
43 );
44
45 PROCEDURE derive_from_header_id
46 (
47 p_key IN po_session_gt.key%TYPE,
48 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
49 p_from_rfq_num_tbl IN PO_TBL_VARCHAR30,
50 x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
51 );
52
53 PROCEDURE derive_style_id
54 (
55 p_key IN po_session_gt.key%TYPE,
56 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
57 p_style_display_name_tbl IN PO_TBL_VARCHAR2000,
58 x_style_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
59 );
60
61 PROCEDURE default_info_from_vendor
62 (
63 p_key IN po_session_gt.key%TYPE,
64 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
65 p_vendor_id_tbl IN PO_TBL_NUMBER,
66 x_invoice_currency_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
67 x_terms_id_tbl OUT NOCOPY PO_TBL_NUMBER
68 );
69
70 PROCEDURE default_info_from_vendor_site
71 (
72 p_key IN po_session_gt.key%TYPE,
73 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
74 p_vendor_id_tbl IN PO_TBL_NUMBER,
75 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
76 x_fob_tbl OUT NOCOPY PO_TBL_VARCHAR30,
77 x_freight_carrier_tbl OUT NOCOPY PO_TBL_VARCHAR30,
78 x_freight_term_tbl OUT NOCOPY PO_TBL_VARCHAR30,
79 x_ship_to_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
80 x_bill_to_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
81 x_invoice_currency_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
82 x_terms_id_tbl OUT NOCOPY PO_TBL_NUMBER,
83 x_shipping_control_tbl OUT NOCOPY PO_TBL_VARCHAR30,
84 x_pay_on_code_tbl OUT NOCOPY PO_TBL_VARCHAR30
85 );
86
87 PROCEDURE default_vendor_contact
88 (
89 p_key IN po_session_gt.key%TYPE,
90 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
91 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
92 x_vendor_contact_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
93 );
94
95 PROCEDURE default_dist_attributes
96 (
97 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
98 );
99
100 PROCEDURE populate_error_flag
101 (
102 x_results IN po_validation_results_type,
103 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
104 );
105 --------------------------------------------------------------------------
106 ---------------------- PUBLIC PROCEDURES ---------------------------------
107 --------------------------------------------------------------------------
108
109 -----------------------------------------------------------------------
110 --Start of Comments
111 --Name: open_headers
112 --Function:
113 -- Open cursor for query.
114 -- This query retrieves the header attributes for processing
115 --Parameters:
116 --IN:
117 -- p_max_intf_header_id
118 -- maximal interface_header_id processed so far
119 -- The query will only retrieve the header records which have
120 -- not been processed
121 --IN:
122 -- p_max_intf_header_id
123 -- maximal interface_header_id processed in previous batches
124 --IN OUT:
125 -- x_headers_csr
126 -- cursor variable to hold pointer to current processing row in the result
127 -- set returned by the query
128 --OUT:
129 --End of Comments
130 ------------------------------------------------------------------------
131 PROCEDURE open_headers
132 (
133 p_max_intf_header_id IN NUMBER,
134 x_headers_csr OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
135 ) IS
136 d_api_name CONSTANT VARCHAR2(30) := 'open_headers';
137 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
138 d_position NUMBER;
139
140 BEGIN
141 d_position := 0;
142
143 IF (PO_LOG.d_proc) THEN
144 PO_LOG.proc_begin(d_module, 'p_max_intf_header_id', p_max_intf_header_id);
145 END IF;
146
147 OPEN x_headers_csr FOR
148 SELECT interface_header_id,
149 draft_id,
150 po_header_id,
151 action,
152 document_num,
153 document_type_code,
154 document_subtype,
155 rate_type,
156 rate_type_code,
157 rate_date,
158 rate,
159 agent_id,
160 agent_name,
161 ship_to_location_id,
162 ship_to_location,
163 bill_to_location_id,
164 bill_to_location,
165 payment_terms,
166 terms_id,
167 vendor_name,
168 vendor_num,
169 vendor_id,
170 vendor_site_code,
171 vendor_site_id,
172 vendor_contact,
173 vendor_contact_id,
174 from_rfq_num,
175 from_header_id,
176 fob,
177 freight_carrier,
178 freight_terms,
179 pay_on_code,
180 shipping_control,
181 currency_code,
182 quote_warning_delay,
183 approval_required_flag,
184 reply_date,
185 approval_status,
186 approved_date,
187 from_type_lookup_code,
188 revision_num,
189 confirming_order_flag,
190 acceptance_required_flag,
191 min_release_amount,
192 closed_code,
193 print_count,
194 frozen_flag,
195 encumbrance_required_flag,
196 vendor_doc_num,
197 org_id,
198 acceptance_due_date,
199 amount_to_encumber,
200 effective_date,
201 expiration_date,
202 po_release_id,
203 release_num,
204 release_date,
205 revised_date,
206 printed_date,
207 closed_date,
208 amount_agreed,
209 amount_limit, -- bug5352625
210 firm_flag,
211 gl_encumbered_date,
212 gl_encumbered_period_name,
213 budget_account_id,
214 budget_account,
215 budget_account_segment1,
216 budget_account_segment2,
217 budget_account_segment3,
218 budget_account_segment4,
219 budget_account_segment5,
220 budget_account_segment6,
221 budget_account_segment7,
222 budget_account_segment8,
223 budget_account_segment9,
224 budget_account_segment10,
225 budget_account_segment11,
226 budget_account_segment12,
227 budget_account_segment13,
228 budget_account_segment14,
229 budget_account_segment15,
230 budget_account_segment16,
231 budget_account_segment17,
232 budget_account_segment18,
233 budget_account_segment19,
234 budget_account_segment20,
235 budget_account_segment21,
236 budget_account_segment22,
237 budget_account_segment23,
238 budget_account_segment24,
239 budget_account_segment25,
240 budget_account_segment26,
241 budget_account_segment27,
242 budget_account_segment28,
243 budget_account_segment29,
244 budget_account_segment30,
245 created_language,
246 style_id,
247 style_display_name,
248 global_agreement_flag,
249
250 -- standard who columns
251 last_update_date,
252 last_updated_by,
253 last_update_login,
254 creation_date,
255 created_by,
256 request_id,
257 program_application_id,
258 program_id,
259 program_update_date,
260 FND_API.g_FALSE, -- initial value for error_flag
261
262 -- txn table columns
263 NULL, -- status_lookup_code
264 NULL, -- cancel_flag
265 NULL, -- vendor_order_num
266 NULL, -- quote_vendor_quote_num
267 NULL, -- doc_creation_method
268 NULL, -- quotation_class_code
269 NULL, -- approved_flag
270 NULL, -- tax_attribute_update_code_tbl
271
272 -- blanket dist columns
273 NULL -- po_dist_id -- bug5252250
274
275 FROM po_headers_interface
276 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
277 AND processing_round_num = PO_PDOI_PARAMS.g_current_round_num
278 AND interface_header_id > p_max_intf_header_id
279 AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
280 PO_PDOI_CONSTANTS.g_ACTION_REPLACE)
281 ORDER by interface_header_id;
282
283 IF (PO_LOG.d_proc) THEN
284 PO_LOG.proc_end (d_module);
285 END IF;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 PO_MESSAGE_S.add_exc_msg
290 (
291 p_pkg_name => d_pkg_name,
292 p_procedure_name => d_api_name || '.' || d_position
293 );
294 RAISE;
295 END open_headers;
296
297 -----------------------------------------------------------------------
298 --Start of Comments
299 --Name: fetch_headers
300 --Function:
301 -- fetch results in batch
302 --Parameters:
303 --IN:
304 --IN OUT:
305 --x_headers_csr
306 -- cursor variable that hold pointers to currently processing row
307 --x_headers
308 -- record variable to hold header info within a batch
309 --OUT:
310 --End of Comments
311 ------------------------------------------------------------------------
312 PROCEDURE fetch_headers
313 (
314 x_headers_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
315 x_headers OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
316 ) IS
317
318 d_api_name CONSTANT VARCHAR2(30) := 'fetch_headers';
319 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
320 d_position NUMBER;
321
322 BEGIN
323 d_position := 0;
324
325 IF (PO_LOG.d_proc) THEN
326 PO_LOG.proc_begin(d_module);
327 END IF;
328
329 FETCH x_headers_csr BULK COLLECT INTO
330 x_headers.intf_header_id_tbl,
331 x_headers.draft_id_tbl,
332 x_headers.po_header_id_tbl,
333 x_headers.action_tbl,
334 x_headers.document_num_tbl,
335 x_headers.doc_type_tbl,
336 x_headers.doc_subtype_tbl,
337 x_headers.rate_type_tbl,
338 x_headers.rate_type_code_tbl,
339 x_headers.rate_date_tbl,
340 x_headers.rate_tbl,
341 x_headers.agent_id_tbl,
342 x_headers.agent_name_tbl,
343 x_headers.ship_to_loc_id_tbl,
344 x_headers.ship_to_loc_tbl,
345 x_headers.bill_to_loc_id_tbl,
346 x_headers.bill_to_loc_tbl,
347 x_headers.payment_terms_tbl,
348 x_headers.terms_id_tbl,
349 x_headers.vendor_name_tbl,
350 x_headers.vendor_num_tbl,
351 x_headers.vendor_id_tbl,
352 x_headers.vendor_site_code_tbl,
353 x_headers.vendor_site_id_tbl,
354 x_headers.vendor_contact_tbl,
355 x_headers.vendor_contact_id_tbl,
356 x_headers.from_rfq_num_tbl,
357 x_headers.from_header_id_tbl,
358 x_headers.fob_tbl,
359 x_headers.freight_carrier_tbl,
360 x_headers.freight_term_tbl,
361 x_headers.pay_on_code_tbl,
362 x_headers.shipping_control_tbl,
363 x_headers.currency_code_tbl,
364 x_headers.quote_warning_delay_tbl,
365 x_headers.approval_required_flag_tbl,
366 x_headers.reply_date_tbl,
367 x_headers.approval_status_tbl,
368 x_headers.approved_date_tbl,
369 x_headers.from_type_lookup_code_tbl,
370 x_headers.revision_num_tbl,
371 x_headers.confirming_order_flag_tbl,
372 x_headers.acceptance_required_flag_tbl,
373 x_headers.min_release_amount_tbl,
374 x_headers.closed_code_tbl,
375 x_headers.print_count_tbl,
376 x_headers.frozen_flag_tbl,
377 x_headers.encumbrance_required_flag_tbl,
378 x_headers.vendor_doc_num_tbl,
379 x_headers.org_id_tbl,
380 x_headers.acceptance_due_date_tbl,
381 x_headers.amount_to_encumber_tbl,
382 x_headers.effective_date_tbl,
383 x_headers.expiration_date_tbl,
384 x_headers.po_release_id_tbl,
385 x_headers.release_num_tbl,
386 x_headers.release_date_tbl,
387 x_headers.revised_date_tbl,
388 x_headers.printed_date_tbl,
389 x_headers.closed_date_tbl,
390 x_headers.amount_agreed_tbl,
391 x_headers.amount_limit_tbl, -- bug5352625
392 x_headers.firm_flag_tbl,
393 x_headers.gl_encumbered_date_tbl,
394 x_headers.gl_encumbered_period_tbl,
395 x_headers.budget_account_id_tbl,
396 x_headers.budget_account_tbl,
397 x_headers.budget_account_segment1_tbl,
398 x_headers.budget_account_segment2_tbl,
399 x_headers.budget_account_segment3_tbl,
400 x_headers.budget_account_segment4_tbl,
401 x_headers.budget_account_segment5_tbl,
402 x_headers.budget_account_segment6_tbl,
403 x_headers.budget_account_segment7_tbl,
404 x_headers.budget_account_segment8_tbl,
405 x_headers.budget_account_segment9_tbl,
406 x_headers.budget_account_segment10_tbl,
407 x_headers.budget_account_segment11_tbl,
408 x_headers.budget_account_segment12_tbl,
409 x_headers.budget_account_segment13_tbl,
410 x_headers.budget_account_segment14_tbl,
411 x_headers.budget_account_segment15_tbl,
412 x_headers.budget_account_segment16_tbl,
413 x_headers.budget_account_segment17_tbl,
414 x_headers.budget_account_segment18_tbl,
415 x_headers.budget_account_segment19_tbl,
416 x_headers.budget_account_segment20_tbl,
417 x_headers.budget_account_segment21_tbl,
418 x_headers.budget_account_segment22_tbl,
419 x_headers.budget_account_segment23_tbl,
420 x_headers.budget_account_segment24_tbl,
421 x_headers.budget_account_segment25_tbl,
422 x_headers.budget_account_segment26_tbl,
423 x_headers.budget_account_segment27_tbl,
424 x_headers.budget_account_segment28_tbl,
425 x_headers.budget_account_segment29_tbl,
426 x_headers.budget_account_segment30_tbl,
427 x_headers.created_language_tbl,
428 x_headers.style_id_tbl,
429 x_headers.style_display_name_tbl,
430 x_headers.global_agreement_flag_tbl,
431
432 -- standard who columns
433 x_headers.last_update_date_tbl,
434 x_headers.last_updated_by_tbl,
435 x_headers.last_update_login_tbl,
436 x_headers.creation_date_tbl,
437 x_headers.created_by_tbl,
438 x_headers.request_id_tbl,
439 x_headers.program_application_id_tbl,
440 x_headers.program_id_tbl,
441 x_headers.program_update_date_tbl,
442
443 x_headers.error_flag_tbl, -- set initial value on error_flag
444
445 -- tan table columns
446 x_headers.status_lookup_code_tbl,
447 x_headers.cancel_flag_tbl,
448 x_headers.vendor_order_num_tbl,
449 x_headers.quote_vendor_quote_num_tbl,
450 x_headers.doc_creation_method_tbl,
451 x_headers.quotation_class_code_tbl,
452 x_headers.approved_flag_tbl,
453 x_headers.tax_attribute_update_code_tbl,
454
455 -- blanket dist columns
456 x_headers.po_dist_id_tbl -- bug5252250
457 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
458
459 IF (PO_LOG.d_proc) THEN
460 PO_LOG.proc_end (d_module);
461 END IF;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 PO_MESSAGE_S.add_exc_msg
466 (
467 p_pkg_name => d_pkg_name,
468 p_procedure_name => d_api_name || '.' || d_position
469 );
470 RAISE;
471 END fetch_headers;
472 --------------------------------------------------------------------------
473 --Start of Comments
474 --Name: derive_headers
475 --Pre-reqs: None
476 --Modifies:
477 --Locks:
478 -- None
479 --Function:
480 -- perform derive logic on header records read in one batch;
481 -- derivation errors are handled all together after the
482 -- derivation logic
483 -- The derived attributes include:
484 -- rate_type, agent_id
485 -- ship_to_location_id, bill_to_location_id
486 -- terms_id, vendor_id
487 -- vendor_site_id, vendor_contact_id
488 --Parameters:
489 --IN: None
490 --IN OUT:
491 -- x_headers
492 -- variable to hold all the header attribute values in one batch;
493 -- derivation source and result are both placed inside the variable
494 --OUT: None
495 --Returns: None
496 --Notes:
497 --Testing:
498 --End of Comments
499 --------------------------------------------------------------------------
500 PROCEDURE derive_headers
501 (
502 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
503 ) IS
504
505 d_api_name CONSTANT VARCHAR2(30) := 'derive_headers';
506 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
507 d_position NUMBER;
508
509 -- key used when operating on temp table
510 l_key po_session_gt.key%TYPE;
511
512 -- table used to save the index of the each row
513 l_index_tbl DBMS_SQL.NUMBER_TABLE;
514
515 -- temp variable used in derivation error handling
516 l_column_name VARCHAR2(11);
517 BEGIN
518 d_position := 0;
519
520 IF (PO_LOG.d_proc) THEN
521 PO_LOG.proc_begin(d_module, 'header_count', x_headers.rec_count);
522 END IF;
523
524 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_DERIVE);
525
526 -- pick a new key which will be used in all derive logic
527 l_key := PO_CORE_S.get_session_gt_nextval;
528
529 IF (PO_LOG.d_stmt) THEN
530 PO_LOG.stmt(d_module, d_position, 'key', l_key);
531 END IF;
532
533 -- initialize table containing the row number
534 PO_PDOI_UTL.generate_ordered_num_list
535 (
536 p_size => x_headers.rec_count,
537 x_num_list => l_index_tbl
538 );
539
540 d_position := 10;
541
542 -- derive rate_type_code from rate_type
543 derive_rate_type_code
544 (
545 p_key => l_key,
546 p_index_tbl => l_index_tbl,
547 p_rate_type_tbl => x_headers.rate_type_tbl,
548 x_rate_type_code_tbl => x_headers.rate_type_code_tbl
549 );
550
551 d_position := 20;
552
553 -- derive agent_id from agent_name
554 derive_agent_id
555 (
556 p_key => l_key,
557 p_index_tbl => l_index_tbl,
558 p_agent_name_tbl => x_headers.agent_name_tbl,
559 x_agent_id_tbl => x_headers.agent_id_tbl
560 );
561
562 d_position := 30;
563
564 -- derive ship_to_location_id from ship_to_location
565 derive_location_id
566 (
567 p_key => l_key,
568 p_index_tbl => l_index_tbl,
569 p_location_type => 'SHIP_TO',
570 p_location_tbl => x_headers.ship_to_loc_tbl,
571 x_location_id_tbl => x_headers.ship_to_loc_id_tbl
572 );
573
574 d_position := 40;
575
576 -- derive bill_to_location_id from bill_to_location
577 derive_location_id
578 (
579 p_key => l_key,
580 p_index_tbl => l_index_tbl,
581 p_location_type => 'BILL_TO',
582 p_location_tbl => x_headers.bill_to_loc_tbl,
583 x_location_id_tbl => x_headers.bill_to_loc_id_tbl
584 );
585
586 d_position := 50;
587
588 -- derive terms_id from payment_terms
589 derive_terms_id
590 (
591 p_key => l_key,
592 p_index_tbl => l_index_tbl,
593 p_payment_terms_tbl => x_headers.payment_terms_tbl,
594 x_terms_id_tbl => x_headers.terms_id_tbl
595 );
596
597 d_position := 60;
598
599 -- derive vendor_id from vendor_name/vendor_num
600 derive_vendor_id
601 (
602 p_key => l_key,
603 p_index_tbl => l_index_tbl,
604 p_vendor_name_tbl => x_headers.vendor_name_tbl,
605 p_vendor_num_tbl => x_headers.vendor_num_tbl,
606 x_vendor_id_tbl => x_headers.vendor_id_tbl
607 );
608
609 d_position := 70;
610
611 -- derive vendor_site_id from vendor_site_code
612 derive_vendor_site_id
613 (
614 p_key => l_key,
615 p_index_tbl => l_index_tbl,
616 p_vendor_id_tbl => x_headers.vendor_id_tbl,
617 p_vendor_site_code_tbl => x_headers.vendor_site_code_tbl,
618 x_vendor_site_id_tbl => x_headers.vendor_site_id_tbl
619 );
620
621 d_position := 80;
622
623 -- derive vendor_contact_id from vendor_contact
624 derive_vendor_contact_id
625 (
626 p_key => l_key,
627 p_index_tbl => l_index_tbl,
628 p_vendor_site_id_tbl => x_headers.vendor_site_id_tbl,
629 p_vendor_contact_tbl => x_headers.vendor_contact_tbl,
630 x_vendor_contact_id_tbl => x_headers.vendor_contact_id_tbl
631 );
632
633 -- derive style_id from style_display_name
634 derive_style_id
635 (
636 p_key => l_key,
637 p_index_tbl => l_index_tbl,
638 p_style_display_name_tbl => x_headers.style_display_name_tbl,
639 x_style_id_tbl => x_headers.style_id_tbl
640 );
641
642 d_position := 90;
643
644 -- derive from_header_id from from_rfq_num for QUOTATION
645 IF (PO_PDOI_PARAMS.g_request.document_type =
646 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
647 IF (PO_LOG.d_stmt) THEN
648 PO_LOG.stmt(d_module, d_position, 'derive from header id');
649 END IF;
650
651 derive_from_header_id
652 (
653 p_key => l_key,
654 p_index_tbl => l_index_tbl,
655 p_from_rfq_num_tbl => x_headers.from_rfq_num_tbl,
656 x_from_header_id_tbl => x_headers.from_header_id_tbl
657 );
658 END IF;
659
660 d_position := 100;
661
662 IF (PO_LOG.d_stmt) THEN
663 PO_LOG.stmt(d_module, d_position, 'start processing derivation errors');
664 END IF;
665
666 -- handle derivation errors
667 FOR i IN 1..x_headers.rec_count
668 LOOP
669 d_position := 110;
670
671 IF (PO_LOG.d_stmt) THEN
672 PO_LOG.stmt(d_module, d_position, 'index', i);
673 END IF;
674
675 IF (x_headers.rate_type_tbl(i) IS NOT NULL AND
676 x_headers.rate_type_code_tbl(i) IS NULL) THEN
677 IF (PO_LOG.d_stmt) THEN
678 PO_LOG.stmt(d_module, d_position, 'rate type code derivation failed');
679 PO_LOG.stmt(d_module, d_position, 'rate type', x_headers.rate_type_tbl(i));
680 END IF;
681
682 PO_PDOI_ERR_UTL.add_fatal_error
683 (
684 p_interface_header_id => x_headers.intf_header_id_tbl(i),
685 p_error_message_name => 'PO_PDOI_DERV_ERROR',
686 p_table_name => 'PO_HEADERS_INTERFACE',
687 p_column_name => 'RATE_TYPE_CODE',
688 p_column_value => x_headers.rate_type_code_tbl(i),
689 p_token1_name => 'COLUMN_NAME',
690 p_token1_value => 'RATE_TYPE',
691 p_token2_name => 'VALUE',
692 p_token2_value => x_headers.rate_type_tbl(i)
693 );
694
695 x_headers.rate_type_code_tbl(i) := NULL;
696
697 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
698 END IF;
699
700 IF (x_headers.agent_name_tbl(i) IS NOT NULL AND
701 x_headers.agent_id_tbl(i) IS NULL) THEN
702 IF (PO_LOG.d_stmt) THEN
703 PO_LOG.stmt(d_module, d_position, 'agent id derivation failed');
704 PO_LOG.stmt(d_module, d_position, 'agent name', x_headers.agent_name_tbl(i));
705 END IF;
706
707 PO_PDOI_ERR_UTL.add_fatal_error
708 (
709 p_interface_header_id => x_headers.intf_header_id_tbl(i),
710 p_error_message_name => 'PO_PDOI_DERV_ERROR',
711 p_table_name => 'PO_HEADERS_INTERFACE',
712 p_column_name => 'AGENT_ID',
713 p_column_value => x_headers.agent_id_tbl(i),
714 p_token1_name => 'COLUMN_NAME',
715 p_token1_value => 'AGENT_NAME',
716 p_token2_name => 'VALUE',
717 p_token2_value => x_headers.agent_name_tbl(i)
718 );
719
720 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
721 END IF;
722
723 IF (x_headers.ship_to_loc_tbl(i) IS NOT NULL AND
724 x_headers.ship_to_loc_id_tbl(i) IS NULL) THEN
725 IF (PO_LOG.d_stmt) THEN
726 PO_LOG.stmt(d_module, d_position, 'ship_to loc id derivation failed');
727 PO_LOG.stmt(d_module, d_position, 'ship_to loc', x_headers.ship_to_loc_tbl(i));
728 END IF;
729
730 PO_PDOI_ERR_UTL.add_fatal_error
731 (
732 p_interface_header_id => x_headers.intf_header_id_tbl(i),
733 p_error_message_name => 'PO_PDOI_DERV_ERROR',
734 p_table_name => 'PO_HEADERS_INTERFACE',
735 p_column_name => 'SHIP_TO_LOCATION_ID',
736 p_column_value => x_headers.ship_to_loc_id_tbl(i),
737 p_token1_name => 'COLUMN_NAME',
738 p_token1_value => 'SHIP_TO_LOCATION_CODE',
739 p_token2_name => 'VALUE',
740 p_token2_value => x_headers.ship_to_loc_tbl(i)
741 );
742
743 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
744 END IF;
745
746 IF (x_headers.bill_to_loc_tbl(i) IS NOT NULL AND
747 x_headers.bill_to_loc_id_tbl(i) IS NULL) THEN
748 IF (PO_LOG.d_stmt) THEN
749 PO_LOG.stmt(d_module, d_position, 'bill_to loc id derivation failed');
750 PO_LOG.stmt(d_module, d_position, 'bill_to loc', x_headers.bill_to_loc_tbl(i));
751 END IF;
752
753 PO_PDOI_ERR_UTL.add_fatal_error
754 (
755 p_interface_header_id => x_headers.intf_header_id_tbl(i),
756 p_error_message_name => 'PO_PDOI_DERV_ERROR',
757 p_table_name => 'PO_HEADERS_INTERFACE',
758 p_column_name => 'BILL_TO_LOCATION_ID',
759 p_column_value => x_headers.bill_to_loc_id_tbl(i),
760 p_token1_name => 'COLUMN_NAME',
761 p_token1_value => 'BILL_TO_LOCATION_CODE',
762 p_token2_name => 'VALUE',
763 p_token2_value => x_headers.bill_to_loc_tbl(i)
764 );
765
766 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
767 END IF;
768
769 IF (x_headers.payment_terms_tbl(i) IS NOT NULL AND
770 x_headers.terms_id_tbl(i) IS NULL) THEN
771 IF (PO_LOG.d_stmt) THEN
772 PO_LOG.stmt(d_module, d_position, 'terms id derivation failed');
773 PO_LOG.stmt(d_module, d_position, 'payment terms', x_headers.payment_terms_tbl(i));
774 END IF;
775
776 PO_PDOI_ERR_UTL.add_fatal_error
777 (
778 p_interface_header_id => x_headers.intf_header_id_tbl(i),
779 p_error_message_name => 'PO_PDOI_DERV_ERROR',
780 p_table_name => 'PO_HEADERS_INTERFACE',
781 p_column_name => 'TERMS_ID',
782 p_column_value => x_headers.terms_id_tbl(i),
783 p_token1_name => 'COLUMN_NAME',
784 p_token1_value => 'PAYMENT_TERMS',
785 p_token2_name => 'VALUE',
786 p_token2_value => x_headers.payment_terms_tbl(i)
787 );
788
789 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
790 END IF;
791
792 IF ((x_headers.vendor_name_tbl(i) IS NOT NULL OR
793 x_headers.vendor_num_tbl(i) IS NOT NULL) AND
794 x_headers.vendor_id_tbl(i) IS NULL) THEN
795 IF (x_headers.vendor_num_tbl(i) IS NULL) THEN
796 l_column_name := 'VENDOR_NAME';
797 ELSE
798 l_column_name := 'VENDOR_NUM';
799 END IF;
800
801 IF (PO_LOG.d_stmt) THEN
802 PO_LOG.stmt(d_module, d_position, 'vendor id derivation failed');
803 PO_LOG.stmt(d_module, d_position, 'vendor name', x_headers.vendor_name_tbl(i));
804 PO_LOG.stmt(d_module, d_position, 'vendor num', x_headers.vendor_num_tbl(i));
805 END IF;
806
807 PO_PDOI_ERR_UTL.add_fatal_error
808 (
809 p_interface_header_id => x_headers.intf_header_id_tbl(i),
810 p_error_message_name => 'PO_PDOI_DERV_ERROR',
811 p_table_name => 'PO_HEADERS_INTERFACE',
812 p_column_name => 'VENDOR_ID',
813 p_column_value => x_headers.vendor_id_tbl(i),
814 p_token1_name => 'COLUMN_NAME',
815 p_token1_value => l_column_name,
816 p_token2_name => 'VALUE',
817 p_token2_value => NVL(x_headers.vendor_num_tbl(i),
818 x_headers.vendor_name_tbl(i))
819 );
820
821 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
822 END IF;
823
824 IF (x_headers.vendor_site_code_tbl(i) IS NOT NULL AND
825 x_headers.vendor_site_id_tbl(i) IS NULL) THEN
826 IF (PO_LOG.d_stmt) THEN
827 PO_LOG.stmt(d_module, d_position, 'vendor site id derivation failed');
828 PO_LOG.stmt(d_module, d_position, 'vendor site', x_headers.vendor_site_code_tbl(i));
829 END IF;
830
831 PO_PDOI_ERR_UTL.add_fatal_error
832 (
833 p_interface_header_id => x_headers.intf_header_id_tbl(i),
834 p_error_message_name => 'PO_PDOI_DERV_ERROR',
835 p_table_name => 'PO_HEADERS_INTERFACE',
836 p_column_name => 'VENDOR_SITE_ID',
837 p_column_value => x_headers.vendor_site_id_tbl(i),
838 p_token1_name => 'COLUMN_NAME',
839 p_token1_value => 'VENDOR_SITE_CODE',
840 p_token2_name => 'VALUE',
841 p_token2_value => x_headers.vendor_site_code_tbl(i)
842 );
843
844 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
845 END IF;
846
847 IF (x_headers.vendor_contact_tbl(i) IS NOT NULL AND
848 x_headers.vendor_contact_id_tbl(i) IS NULL) THEN
849 IF (PO_LOG.d_stmt) THEN
850 PO_LOG.stmt(d_module, d_position, 'contact id derivation failed');
851 PO_LOG.stmt(d_module, d_position, 'contact', x_headers.vendor_contact_tbl(i));
852 END IF;
853
854 PO_PDOI_ERR_UTL.add_fatal_error
855 (
856 p_interface_header_id => x_headers.intf_header_id_tbl(i),
857 p_error_message_name => 'PO_PDOI_DERV_ERROR',
858 p_table_name => 'PO_HEADERS_INTERFACE',
859 p_column_name => 'VENDOR_CONTACT_ID',
860 p_column_value => x_headers.vendor_contact_id_tbl(i),
861 p_token1_name => 'COLUMN_NAME',
862 p_token1_value => 'VENDOR_CONTACT',
863 p_token2_name => 'VALUE',
864 p_token2_value => x_headers.vendor_contact_tbl(i)
865 );
866
867 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
868 END IF;
869
870 IF (x_headers.style_display_name_tbl(i) IS NOT NULL AND
871 x_headers.style_id_tbl(i) IS NULL) THEN
872 IF (PO_LOG.d_stmt) THEN
873 PO_LOG.stmt(d_module, d_position, 'style id derivation failed');
874 PO_LOG.stmt(d_module, d_position, 'style_display_name', x_headers.style_display_name_tbl(i));
875 END IF;
876
877 PO_PDOI_ERR_UTL.add_fatal_error
878 (
879 p_interface_header_id => x_headers.intf_header_id_tbl(i),
880 p_error_message_name => 'PO_PDOI_DERV_ERROR',
881 p_table_name => 'PO_HEADERS_INTERFACE',
882 p_column_name => 'STYLE_ID',
883 p_column_value => x_headers.style_id_tbl(i),
884 p_token1_name => 'COLUMN_NAME',
885 p_token1_value => 'STYLE_DISPLAY_NAME',
886 p_token2_name => 'VALUE',
887 p_token2_value => x_headers.style_display_name_tbl(i)
888 );
889
890 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
891 END IF;
892
893 IF (PO_PDOI_PARAMS.g_request.document_type =
894 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
895 IF (x_headers.from_rfq_num_tbl(i) IS NOT NULL AND
896 x_headers.from_header_id_tbl(i) IS NULL) THEN
897 IF (PO_LOG.d_stmt) THEN
898 PO_LOG.stmt(d_module, d_position, 'from header id derivation failed');
899 PO_LOG.stmt(d_module, d_position, 'rfq num', x_headers.from_rfq_num_tbl(i));
900 END IF;
901
902 PO_PDOI_ERR_UTL.add_fatal_error
903 (
904 p_interface_header_id => x_headers.intf_header_id_tbl(i),
905 p_error_message_name => 'PO_PDOI_DERV_ERROR',
906 p_table_name => 'PO_HEADERS_INTERFACE',
907 p_column_name => 'FROM_HEADER_ID',
908 p_column_value => x_headers.from_header_id_tbl(i),
909 p_token1_name => 'COLUMN_NAME',
910 p_token1_value => 'FROM_RFQ_NUM',
911 p_token2_name => 'VALUE',
912 p_token2_value => x_headers.from_rfq_num_tbl(i)
913 );
914
915 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
916 END IF;
917 END IF;
918 END LOOP;
919
920 IF (PO_LOG.d_stmt) THEN
921 PO_LOG.stmt(d_module, d_position, 'end of processing derivation errors');
922 END IF;
923
924 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_DERIVE);
925
926 IF (PO_LOG.d_proc) THEN
927 PO_LOG.proc_end (d_module);
928 END IF;
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 PO_MESSAGE_S.add_exc_msg
933 (
934 p_pkg_name => d_pkg_name,
935 p_procedure_name => d_api_name || '.' || d_position
936 );
937 RAISE;
938 END derive_headers;
939
940 --------------------------------------------------------------------------
941 --Start of Comments
942 --Name: default_headers
943 --Pre-reqs: None
944 --Modifies:
945 --Locks:
946 -- None
947 --Function:
948 -- perform default logic on header records read in one batch;
949 --Parameters:
950 --IN: None
951 --IN OUT:
952 -- x_headers
953 -- variable to hold all the header attribute values in one batch;
954 -- default result are saved inside the variable
955 --OUT: None
956 --Returns: None
957 --Notes:
958 --Testing:
959 --End of Comments
960 --------------------------------------------------------------------------
961 PROCEDURE default_headers
962 (
963 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
964 ) IS
965
966 d_api_name CONSTANT VARCHAR2(30) := 'default_headers';
967 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
968 d_position NUMBER;
969
970 -- key used when operating on temp table
971 l_key po_session_gt.key%TYPE;
972
973 -- table used to save the index of the each row
974 l_index_tbl DBMS_SQL.NUMBER_TABLE;
975
976 -- information defaulted from vendor
977
978 -- <Bug 4546121: Supplier TCA conversion>
979 -- The following columns are being obsoleted from PO_VENDORS level.
980 --l_vendor_fob_tbl PO_TBL_VARCHAR30;
981 --l_vendor_freight_carrier_tbl PO_TBL_VARCHAR30;
982 --l_vendor_freight_term_tbl PO_TBL_VARCHAR30;
983 --l_vendor_ship_to_loc_id_tbl PO_TBL_NUMBER;
984 --l_vendor_bill_to_loc_id_tbl PO_TBL_NUMBER;
985
986 l_vendor_invoice_curr_code_tbl PO_TBL_VARCHAR30;
987 l_vendor_terms_id_tbl PO_TBL_NUMBER;
988
989 -- information defaulted from vendor site
990 l_site_fob_tbl PO_TBL_VARCHAR30;
991 l_site_freight_carrier_tbl PO_TBL_VARCHAR30;
992 l_site_freight_term_tbl PO_TBL_VARCHAR30;
993 l_site_ship_to_loc_id_tbl PO_TBL_NUMBER;
994 l_site_bill_to_loc_id_tbl PO_TBL_NUMBER;
995 l_site_invoice_curr_code_tbl PO_TBL_VARCHAR30;
996 l_site_terms_id_tbl PO_TBL_NUMBER;
997 l_site_shipping_control_tbl PO_TBL_VARCHAR30;
998 l_site_pay_on_code_tbl PO_TBL_VARCHAR30;
999
1000 l_lang VARCHAR2(4);
1001
1002 l_display_rate NUMBER;
1003 BEGIN
1004 d_position := 0;
1005
1006 IF (PO_LOG.d_proc) THEN
1007 PO_LOG.proc_begin(d_module);
1008 END IF;
1009
1010 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_DEFAULT);
1011
1012 -- pick a new key which will be used in all derive logic
1013 l_key := PO_CORE_S.get_session_gt_nextval;
1014
1015 IF (PO_LOG.d_stmt) THEN
1016 PO_LOG.stmt(d_module, d_position, 'key', l_key);
1017 END IF;
1018
1019 -- initialize table containing the row number
1020 PO_PDOI_UTL.generate_ordered_num_list
1021 (
1022 p_size => x_headers.rec_count,
1023 x_num_list => l_index_tbl
1024 );
1025
1026 d_position := 10;
1027
1028 -- default information from vendor
1029 default_info_from_vendor
1030 (
1031 p_key => l_key,
1032 p_index_tbl => l_index_tbl,
1033 p_vendor_id_tbl => x_headers.vendor_id_tbl,
1034 x_invoice_currency_code_tbl => l_vendor_invoice_curr_code_tbl,
1035 x_terms_id_tbl => l_vendor_terms_id_tbl
1036 );
1037
1038 d_position := 20;
1039
1040 -- default information from vendor site
1041 default_info_from_vendor_site
1042 (
1043 p_key => l_key,
1044 p_index_tbl => l_index_tbl,
1045 p_vendor_id_tbl => x_headers.vendor_id_tbl,
1046 x_vendor_site_id_tbl => x_headers.vendor_site_id_tbl,
1047 x_fob_tbl => l_site_fob_tbl,
1048 x_freight_carrier_tbl => l_site_freight_carrier_tbl,
1049 x_freight_term_tbl => l_site_freight_term_tbl,
1050 x_ship_to_loc_id_tbl => l_site_ship_to_loc_id_tbl,
1051 x_bill_to_loc_id_tbl => l_site_bill_to_loc_id_tbl,
1052 x_invoice_currency_code_tbl => l_site_invoice_curr_code_tbl,
1053 x_terms_id_tbl => l_site_terms_id_tbl,
1054 x_shipping_control_tbl => l_site_shipping_control_tbl,
1055 x_pay_on_code_tbl => l_site_pay_on_code_tbl
1056 );
1057
1058 d_position := 30;
1059
1060 -- default vendor contact from vendor site
1061 default_vendor_contact
1062 (
1063 p_key => l_key,
1064 p_index_tbl => l_index_tbl,
1065 p_vendor_site_id_tbl => x_headers.vendor_site_id_tbl,
1066 x_vendor_contact_id_tbl => x_headers.vendor_contact_id_tbl
1067 );
1068
1069 d_position := 40;
1070
1071 FOR i IN 1..x_headers.rec_count
1072 LOOP
1073 d_position := 50;
1074
1075 IF (PO_LOG.d_stmt) THEN
1076 PO_LOG.stmt(d_module, d_position, 'index', i);
1077 END IF;
1078
1079
1080
1081 -- default created_language for Blanket and Quotation
1082 IF (PO_PDOI_PARAMS.g_request.document_type IN
1083 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET, PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION)) THEN
1084 x_headers.created_language_tbl(i) :=
1085 NVL(x_headers.created_language_tbl(i), USERENV('LANG'));
1086 END IF;
1087
1088 -- default agent_id
1089 x_headers.agent_id_tbl(i) :=
1090 NVL(x_headers.agent_id_tbl(i), PO_PDOI_PARAMS.g_request.buyer_id);
1091
1092 -- default document_type_code
1093 x_headers.doc_type_tbl(i) :=
1094 NVL(x_headers.doc_type_tbl(i), PO_PDOI_PARAMS.g_request.document_type);
1095
1096 -- default fob_lookup_code
1097 x_headers.fob_tbl(i) :=
1098 COALESCE(x_headers.fob_tbl(i), l_site_fob_tbl(i),
1099 PO_PDOI_PARAMS.g_sys.fob_lookup_code);
1100
1101 -- default freight_carrier(ship_via_lookup_code)
1102 x_headers.freight_carrier_tbl(i) :=
1103 COALESCE(x_headers.freight_carrier_tbl(i), l_site_freight_carrier_tbl(i),
1104 PO_PDOI_PARAMS.g_sys.ship_via_lookup_code);
1105
1106 -- default freight_terms
1107 x_headers.freight_term_tbl(i) :=
1108 COALESCE(x_headers.freight_term_tbl(i), l_site_freight_term_tbl(i),
1109 PO_PDOI_PARAMS.g_sys.freight_terms_lookup_code);
1110
1111 -- default terms_id
1112 x_headers.terms_id_tbl(i) :=
1113 COALESCE(x_headers.terms_id_tbl(i), l_site_terms_id_tbl(i),
1114 l_vendor_terms_id_tbl(i));
1115
1116 -- default shipping_control
1117 x_headers.shipping_control_tbl(i) :=
1118 NVL(x_headers.shipping_control_tbl(i), l_site_shipping_control_tbl(i));
1119
1120 -- default ship_to_location_id
1121 x_headers.ship_to_loc_id_tbl(i) :=
1122 COALESCE(x_headers.ship_to_loc_id_tbl(i), l_site_ship_to_loc_id_tbl(i),
1123 PO_PDOI_PARAMS.g_sys.ship_to_location_id);
1124
1125 -- default bill_to_location_id
1126 x_headers.bill_to_loc_id_tbl(i) :=
1127 COALESCE(x_headers.bill_to_loc_id_tbl(i), l_site_bill_to_loc_id_tbl(i),
1128 PO_PDOI_PARAMS.g_sys.bill_to_location_id);
1129
1130 x_headers.global_agreement_flag_tbl(i) :=
1131 NVL(x_headers.global_agreement_flag_tbl(i), PO_PDOI_PARAMS.g_request.ga_flag);
1132
1133 -- default style_id
1134 IF (x_headers.doc_type_tbl(i) = 'QUOTATION' OR
1135 (x_headers.doc_type_tbl(i) = 'BLANKET' AND x_headers.global_agreement_flag_tbl(i) = 'N'))
1136 THEN
1137 x_headers.style_id_tbl(i) := PO_DOC_STYLE_GRP.get_standard_doc_style;
1138 ELSE
1139 x_headers.style_id_tbl(i) :=
1140 NVL(x_headers.style_id_tbl(i), PO_DOC_STYLE_GRP.get_standard_doc_style);
1141 END IF;
1142
1143 -- set pay_on_code
1144 x_headers.pay_on_code_tbl(i) := l_site_pay_on_code_tbl(i);
1145
1146 -- default approval_status
1147 x_headers.approval_status_tbl(i) :=
1148 NVL(x_headers.approval_status_tbl(i), PO_PDOI_PARAMS.g_request.approved_status);
1149
1150 -- bug4911383
1151 -- If intended approval status = 'APPROVED', it cannot require signature
1152
1153 x_headers.acceptance_required_flag_tbl(i) :=
1154 NVL(x_headers.acceptance_required_flag_tbl(i), PO_PDOI_PARAMS.g_sys.acceptance_required_flag); /* Bug 7518967 : Default Acceptance Required Check ER */
1155
1156
1157 IF (x_headers.approval_status_tbl(i) = 'APPROVED' AND
1158 x_headers.acceptance_required_flag_tbl(i) = 'S') THEN
1159
1160 x_headers.acceptance_required_flag_tbl(i) := 'N';
1161
1162
1163 END IF;
1164
1165 -- bug4690880
1166 -- All document types will share same behavior in terms of currency code
1167 -- defaulting
1168 -- default currency_code
1169 x_headers.currency_code_tbl(i) :=
1170 COALESCE(x_headers.currency_code_tbl(i), l_site_invoice_curr_code_tbl(i),
1171 l_vendor_invoice_curr_code_tbl(i), PO_PDOI_PARAMS.g_sys.currency_code);
1172
1173 d_position := 60;
1174
1175 -- default attributes for each document type
1176 IF (x_headers.doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1177 -- default document sub-type
1178 x_headers.doc_subtype_tbl(i) :=
1179 NVL(x_headers.doc_subtype_tbl(i), PO_PDOI_PARAMS.g_request.document_subtype);
1180
1181 -- set quotation_class_code
1182 x_headers.quotation_class_code_tbl(i) :=
1183 PO_PDOI_MAINPROC_UTL_PVT.get_quotation_class_code
1184 (
1185 x_headers.doc_subtype_tbl(i)
1186 );
1187
1188 IF (PO_LOG.d_stmt) THEN
1189 PO_LOG.stmt(d_module, d_position, 'quote class code',
1190 x_headers.quotation_class_code_tbl(i));
1191 END IF;
1192
1193 -- set global agreement flag to NULL
1194 x_headers.global_agreement_flag_tbl(i) := NULL;
1195
1196 -- default quote_warning_delay
1197 x_headers.quote_warning_delay_tbl(i) :=
1198 NVL(x_headers.quote_warning_delay_tbl(i),
1199 PO_PDOI_PARAMS.g_sys.def_quote_warning_delay);
1200
1201 -- default approval_required_flag
1202 x_headers.approval_required_flag_tbl(i) :=
1203 NVL(x_headers.approval_required_flag_tbl(i), 'N');
1204
1205 -- default reply_date
1206 x_headers.reply_date_tbl(i) := NVL(x_headers.reply_date_tbl(i), sysdate);
1207
1208 -- set approved_flag
1209 x_headers.approved_flag_tbl(i) := NULL;
1210
1211 -- set approved_date
1212 x_headers.approved_date_tbl(i) := NULL;
1213
1214 d_position := 70;
1215
1216 -- set status_lookup_code
1217 IF (x_headers.approval_status_tbl(i) = 'INCOMPLETE') THEN
1218 x_headers.status_lookup_code_tbl(i) := 'I';
1219 ELSE
1220 -- approval_status = 'APPROVED'
1221 x_headers.status_lookup_code_tbl(i) := 'A';
1222 END IF;
1223
1224 -- default from_type_lookup_code
1225 IF (x_headers.from_type_lookup_code_tbl(i) IS NULL AND
1226 x_headers.from_header_id_tbl(i) IS NOT NULL) THEN
1227 x_headers.from_type_lookup_code_tbl(i) := 'RFQ';
1228 END IF;
1229
1230 -- set cancel_flag
1231 x_headers.cancel_flag_tbl(i) := NULL;
1232
1233 -- set vendor_order_num
1234 x_headers.vendor_order_num_tbl(i) := NULL;
1235
1236 -- set quote_vendor_quote_num
1237 x_headers.quote_vendor_quote_num_tbl(i) := x_headers.vendor_doc_num_tbl(i);
1238
1239 -- set document_creation_method
1240 x_headers.doc_creation_method_tbl(i) := NULL;
1241
1242 -- default document_number
1243 -- this is not the final value for document_number,
1244 -- but a temp value used to insert record into draft table
1245
1246 -- if document num assigning method is 'AUTOMATIC', always overwrite
1247 -- user's document num input
1248 IF (PO_PDOI_PARAMS.g_sys.user_defined_quote_num_code = 'AUTOMATIC') THEN
1249
1250 d_position := 80;
1251
1252 -- bug5028275
1253 -- assign document number only if the user has not provided any
1254 IF (x_headers.document_num_tbl(i) IS NULL) THEN
1255 x_headers.document_num_tbl(i) := -x_headers.po_header_id_tbl(i);
1256 END IF;
1257
1258 IF (PO_LOG.d_stmt) THEN
1259 PO_LOG.stmt(d_module, d_position, 'temp doc num',
1260 x_headers.document_num_tbl(i));
1261 END IF;
1262
1263 END IF;
1264 ELSIF (x_headers.doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1265
1266 d_position := 90;
1267
1268 -- default revision_num
1269 x_headers.revision_num_tbl(i) := NVL(x_headers.revision_num_tbl(i), 0);
1270
1271 d_position := 100;
1272
1273 -- default confirming_order_flag
1274 x_headers.confirming_order_flag_tbl(i) :=
1275 NVL(x_headers.confirming_order_flag_tbl(i), 'N');
1276
1277 -- default acceptance_required_flag
1278 x_headers.acceptance_required_flag_tbl(i) :=
1279 NVL(x_headers.acceptance_required_flag_tbl(i), PO_PDOI_PARAMS.g_sys.acceptance_required_flag); /* Bug 7518967 : Default Acceptance Required Check ER */
1280
1281 -- default min_release_amount
1282 x_headers.min_release_amount_tbl(i) :=
1283 NVL(x_headers.min_release_amount_tbl(i),
1284 PO_PDOI_PARAMS.g_sys.min_rel_amount);
1285
1286 -- default closed_code
1287 x_headers.closed_code_tbl(i) := NVL(x_headers.closed_code_tbl(i), 'OPEN');
1288
1289 -- default print_count
1290 x_headers.print_count_tbl(i) := NVL(x_headers.print_count_tbl(i), 0);
1291
1292 -- default frozen_flag
1293 x_headers.frozen_flag_tbl(i) := NVL(x_headers.frozen_flag_tbl(i), 'N');
1294
1295 d_position := 110;
1296
1297 x_headers.approved_flag_tbl(i) := NULL;
1298 x_headers.approved_date_tbl(i) := NULL;
1299
1300 -- set status_lookup_code
1301 x_headers.status_lookup_code_tbl(i) := NULL;
1302
1303 -- set cancel_flag
1304 x_headers.cancel_flag_tbl(i) := 'N';
1305
1306 -- set vendor_order_num
1307 x_headers.vendor_order_num_tbl(i) := x_headers.vendor_doc_num_tbl(i);
1308
1309 -- set quote_vendor_quote_num
1310 x_headers.quote_vendor_quote_num_tbl(i) := NULL;
1311
1312 -- set document_creation_method
1313 x_headers.doc_creation_method_tbl(i) := 'PDOI';
1314
1315 -- default document_number
1316 -- this is not the final value for document_number,
1317 -- but a temp value used to insert record into draft table
1318
1319 -- if document num assigning method is 'AUTOMATIC', always overwrite
1320 -- user's document num input
1321 IF (PO_PDOI_PARAMS.g_sys.user_defined_po_num_code = 'AUTOMATIC') THEN
1322
1323 d_position := 120;
1324
1325 -- bug5028275
1326 -- assign document number only if the user has not provided any
1327 IF (x_headers.document_num_tbl(i) IS NULL) THEN
1328 x_headers.document_num_tbl(i) := -x_headers.po_header_id_tbl(i);
1329 END IF;
1330
1331 IF (PO_LOG.d_stmt) THEN
1332 PO_LOG.stmt(d_module, d_position, 'temp doc num',
1333 x_headers.document_num_tbl(i));
1334 END IF;
1335 END IF;
1336 ELSIF (x_headers.doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1337
1338 d_position := 130;
1339
1340 -- default revision_num
1341 x_headers.revision_num_tbl(i) := NVL(x_headers.revision_num_tbl(i), 0);
1342
1343 -- default confirming_order_flag
1344 x_headers.confirming_order_flag_tbl(i) :=
1345 NVL(x_headers.confirming_order_flag_tbl(i), 'N');
1346
1347 -- default acceptance_required_flag
1348 x_headers.acceptance_required_flag_tbl(i) :=
1349 NVL(x_headers.acceptance_required_flag_tbl(i), PO_PDOI_PARAMS.g_sys.acceptance_required_flag); /* Bug 7518967 : Default Acceptance Required Check ER */
1350
1351 -- default closed_code
1352 x_headers.closed_code_tbl(i) := NVL(x_headers.closed_code_tbl(i), 'OPEN');
1353
1354 -- default print_count
1355 x_headers.print_count_tbl(i) := NVL(x_headers.print_count_tbl(i), 0);
1356
1357 -- default frozen_flag
1358 x_headers.frozen_flag_tbl(i) := NVL(x_headers.frozen_flag_tbl(i), 'N');
1359
1360 d_position := 140;
1361
1362 x_headers.approved_flag_tbl(i) := NULL;
1363 x_headers.approved_date_tbl(i) := NULL;
1364
1365 d_position := 150;
1366
1367 -- set status_lookup_code
1368 x_headers.status_lookup_code_tbl(i) := NULL;
1369
1370 -- set cancel_flag
1371 x_headers.cancel_flag_tbl(i) := 'N';
1372
1373 -- set vendor_order_num
1374 x_headers.vendor_order_num_tbl(i) := x_headers.vendor_doc_num_tbl(i);
1375
1376 -- set quote_vendor_quote_num
1377 x_headers.quote_vendor_quote_num_tbl(i) := NULL;
1378
1379 -- set document_creation_method
1380 x_headers.doc_creation_method_tbl(i) := 'PDOI';
1381
1382 -- set tax attribute update code
1383 x_headers.tax_attribute_update_code_tbl(i) := 'CREATE';
1384
1385 -- default document_number
1386 -- this is not the final value for document_number,
1387 -- but a temp value used to insert record into draft table
1388
1389 -- if document num assigning method is 'AUTOMATIC', always overwrite
1390 -- user's document num input
1391 IF (PO_PDOI_PARAMS.g_sys.user_defined_po_num_code = 'AUTOMATIC') THEN
1392
1393 -- bug5028275
1394 -- assign document number only if the user has not provided any
1395 IF (x_headers.document_num_tbl(i) IS NULL) THEN
1396 x_headers.document_num_tbl(i) := -x_headers.po_header_id_tbl(i);
1397 END IF;
1398
1399 IF (PO_LOG.d_stmt) THEN
1400 PO_LOG.stmt(d_module, d_position, 'temp doc num',
1401 x_headers.document_num_tbl(i));
1402 END IF;
1403
1404 END IF;
1405 END IF;
1406
1407 d_position := 160;
1408
1409 -- default rate info after currency is defaulted
1410 IF (x_headers.currency_code_tbl(i) <> PO_PDOI_PARAMS.g_sys.currency_code) THEN
1411 IF (PO_LOG.d_stmt) THEN
1412 PO_LOG.stmt(d_module, d_position, 'default rate info');
1413 PO_LOG.stmt(d_module, d_position, 'currency_code',
1414 x_headers.currency_code_tbl(i));
1415 END IF;
1416
1417 -- default rate_date
1418 x_headers.rate_date_tbl(i) := NVL(x_headers.rate_date_tbl(i), sysdate);
1419
1420 -- default rate_type
1421 IF (GL_CURRENCY_API.is_fixed_rate
1422 (
1423 x_from_currency => x_headers.currency_code_tbl(i),
1424 x_to_currency => PO_PDOI_PARAMS.g_sys.currency_code,
1425 x_effective_date => x_headers.rate_date_tbl(i)
1426 ) = 'Y') THEN
1427 x_headers.rate_type_code_tbl(i) := 'EMU FIXED'; --bug 7653758
1428 ELSE
1429 x_headers.rate_type_code_tbl(i) :=
1430 NVL(x_headers.rate_type_code_tbl(i), PO_PDOI_PARAMS.g_sys.default_rate_type); --bug 7653758
1431 END IF;
1432
1433 IF (PO_LOG.d_stmt) THEN
1434 PO_LOG.stmt(d_module, d_position, 'rate type',
1435 x_headers.rate_type_code_tbl(i)); --bug7653758
1436 END IF;
1437
1438 d_position := 170;
1439
1440 -- default rate
1441 IF (x_headers.rate_tbl(i) IS NULL OR
1442 x_headers.rate_type_code_tbl(i) = 'EMU FIXED') THEN --bug 7653758
1443 po_currency_sv.get_rate
1444 (
1445 x_set_of_books_id => PO_PDOI_PARAMS.g_sys.sob_id,
1446 x_currency_code => x_headers.currency_code_tbl(i),
1447 x_rate_type => x_headers.rate_type_code_tbl(i), --bug 7653758
1448 x_rate_date => x_headers.rate_date_tbl(i),
1449 x_inverse_rate_display_flag => 'N',
1450 x_rate => x_headers.rate_tbl(i),
1451 x_display_rate => l_display_rate
1452 );
1453
1454 IF (PO_LOG.d_stmt) THEN
1455 PO_LOG.stmt(d_module, d_position, 'rate', x_headers.rate_tbl(i));
1456 END IF;
1457 END IF;
1458 END IF;
1459 END LOOP;
1460
1461 d_position := 180;
1462
1463 -- default the distribution related fields for a Blanket if
1464 -- encumbrance is required on this document
1465 IF (PO_PDOI_PARAMS.g_request.document_type =
1466 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET AND
1467 PO_PDOI_PARAMS.g_sys.po_encumbrance_flag = 'Y' AND
1468 PO_PDOI_PARAMS.g_sys.req_encumbrance_flag = 'Y') THEN
1469 IF (PO_LOG.d_stmt) THEN
1470 PO_LOG.stmt(d_module, d_position, 'create distribution for blanket' ||
1471 ' since encumbrance is required');
1472 END IF;
1473
1474 default_dist_attributes
1475 (
1476 x_headers => x_headers
1477 );
1478 END IF;
1479
1480 d_position := 190;
1481
1482 -- call utility method to default standard who columns
1483 PO_PDOI_MAINPROC_UTL_PVT.default_who_columns
1484 (
1485 x_last_update_date_tbl => x_headers.last_update_date_tbl,
1486 x_last_updated_by_tbl => x_headers.last_updated_by_tbl,
1487 x_last_update_login_tbl => x_headers.last_update_login_tbl,
1488 x_creation_date_tbl => x_headers.creation_date_tbl,
1489 x_created_by_tbl => x_headers.created_by_tbl,
1490 x_request_id_tbl => x_headers.request_id_tbl,
1491 x_program_application_id_tbl => x_headers.program_application_id_tbl,
1492 x_program_id_tbl => x_headers.program_id_tbl,
1493 x_program_update_date_tbl => x_headers.program_update_date_tbl
1494 );
1495
1496 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_DEFAULT);
1497
1498 IF (PO_LOG.d_proc) THEN
1499 PO_LOG.proc_end (d_module);
1500 END IF;
1501
1502 EXCEPTION
1503 WHEN OTHERS THEN
1504 PO_MESSAGE_S.add_exc_msg
1505 (
1506 p_pkg_name => d_pkg_name,
1507 p_procedure_name => d_api_name || '.' || d_position
1508 );
1509 RAISE;
1510 END default_headers;
1511
1512 -----------------------------------------------------------------------
1513 --Start of Comments
1514 --Name: validate_headers
1515 --Function:
1516 -- validate header attributes;
1517 -- If there is error(s) on any attribute of the header row,
1518 -- corresponding value in error_flag_tbl will be set with value
1519 -- FND_API.g_TRUE.
1520 --Parameters:
1521 --IN:
1522 --x_headers
1523 -- record containing header info within the batch;
1524 --IN OUT:
1525 --OUT:
1526 --End of Comments
1527 ------------------------------------------------------------------------
1528 PROCEDURE validate_headers
1529 (
1530 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
1531 ) IS
1532
1533 d_api_name CONSTANT VARCHAR2(30) := 'validate_headers';
1534 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1535 d_position NUMBER;
1536
1537 l_headers PO_HEADERS_VAL_TYPE := PO_HEADERS_VAL_TYPE();
1538 l_result_type VARCHAR2(30);
1539 l_results po_validation_results_type;
1540 l_parameter_name_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
1541 l_parameter_value_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
1542
1543 BEGIN
1544 d_position := 0;
1545
1546 IF (PO_LOG.d_proc) THEN
1547 PO_LOG.proc_begin(d_module, 'x_headers', x_headers.intf_header_id_tbl);
1548 END IF;
1549
1550 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_VALIDATE);
1551
1552 l_headers.interface_id := x_headers.intf_header_id_tbl;
1553 l_headers.po_header_id := x_headers.po_header_id_tbl;
1554 l_headers.start_date := x_headers.effective_date_tbl;
1555 l_headers.end_date := x_headers.expiration_date_tbl;
1556 l_headers.type_lookup_code := x_headers.doc_type_tbl;
1557 l_headers.acceptance_required_flag := x_headers.acceptance_required_flag_tbl;
1558 l_headers.revision_num := x_headers.revision_num_tbl;
1559 l_headers.document_num := x_headers.document_num_tbl;
1560 l_headers.org_id := x_headers.org_id_tbl;
1561 l_headers.currency_code := x_headers.currency_code_tbl;
1562 l_headers.rate_type := x_headers.rate_type_code_tbl; --bug 7653758
1563 l_headers.rate := x_headers.rate_tbl;
1564 l_headers.rate_date := x_headers.rate_date_tbl;
1565 l_headers.agent_id := x_headers.agent_id_tbl;
1566 l_headers.vendor_id := x_headers.vendor_id_tbl;
1567 l_headers.vendor_site_id := x_headers.vendor_site_id_tbl;
1568 l_headers.vendor_contact_id := x_headers.vendor_contact_id_tbl;
1569 l_headers.ship_to_location_id := x_headers.ship_to_loc_id_tbl;
1570 l_headers.bill_to_location_id := x_headers.bill_to_loc_id_tbl;
1571 l_headers.last_update_date := x_headers.last_update_date_tbl;
1572 l_headers.last_updated_by := x_headers.last_updated_by_tbl;
1573 l_headers.po_release_id := x_headers.po_release_id_tbl;
1574 l_headers.release_num := x_headers.release_num_tbl;
1575 l_headers.release_date := x_headers.release_date_tbl;
1576 l_headers.revised_date := x_headers.revised_date_tbl;
1577 l_headers.printed_date := x_headers.printed_date_tbl;
1578 l_headers.closed_date := x_headers.closed_date_tbl;
1579 l_headers.terms_id := x_headers.terms_id_tbl;
1580 l_headers.ship_via_lookup_code := x_headers.freight_carrier_tbl;
1581 l_headers.fob_lookup_code := x_headers.fob_tbl;
1582 l_headers.freight_terms_lookup_code := x_headers.freight_term_tbl;
1583 l_headers.shipping_control := x_headers.shipping_control_tbl;
1584 l_headers.confirming_order_flag := x_headers.confirming_order_flag_tbl;
1585 l_headers.acceptance_due_date := x_headers.acceptance_due_date_tbl;
1586 l_headers.amount_agreed := x_headers.amount_agreed_tbl;
1587 l_headers.amount_limit := x_headers.amount_limit_tbl; -- bug5352625
1588 l_headers.firm_status_lookup_code := x_headers.firm_flag_tbl;
1589 l_headers.cancel_flag := x_headers.cancel_flag_tbl;
1590 l_headers.closed_code := x_headers.closed_code_tbl;
1591 l_headers.print_count := x_headers.print_count_tbl;
1592 l_headers.frozen_flag := x_headers.frozen_flag_tbl;
1593 l_headers.approval_status := x_headers.approval_status_tbl;
1594 l_headers.amount_to_encumber := x_headers.amount_to_encumber_tbl;
1595 l_headers.quote_warning_delay := x_headers.quote_warning_delay_tbl;
1596 l_headers.approval_required_flag := x_headers.approval_required_flag_tbl;
1597 l_headers.style_id := x_headers.style_id_tbl;
1598
1599 l_parameter_name_tbl.EXTEND(6);
1600 l_parameter_value_tbl.EXTEND(6);
1601 l_parameter_name_tbl(1) := 'INVENTORY_ORG_ID';
1602 l_parameter_value_tbl(1) := PO_PDOI_PARAMS.g_sys.def_inv_org_id; -- bug5601416
1603 l_parameter_name_tbl(2) := 'SET_OF_BOOKS_ID';
1604 l_parameter_value_tbl(2) := PO_PDOI_PARAMS.g_sys.sob_id;
1605 l_parameter_name_tbl(3) := 'FUNCTIONAL_CURRENCY_CODE';
1606 l_parameter_value_tbl(3) := PO_PDOI_PARAMS.g_sys.currency_code;
1607 l_parameter_name_tbl(4) := 'FEDERAL_INSTANCE';
1608 l_parameter_value_tbl(4) := PO_PDOI_PARAMS.g_sys.is_federal_instance;
1609 l_parameter_name_tbl(5) := 'MANUAL_PO_NUM_TYPE';
1610 l_parameter_value_tbl(5) := PO_PDOI_PARAMS.g_sys.manual_po_num_type;
1611 l_parameter_name_tbl(6) := 'MANUAL_QUOTE_NUM_TYPE';
1612 l_parameter_value_tbl(6) := PO_PDOI_PARAMS.g_sys.manual_quote_num_type;
1613
1614 d_position := 10;
1615
1616 PO_VALIDATIONS.validate_pdoi(p_headers => l_headers,
1617 p_doc_type => PO_PDOI_PARAMS.g_request.document_type,
1618 p_parameter_name_tbl => l_parameter_name_tbl,
1619 p_parameter_value_tbl => l_parameter_value_tbl,
1620 x_result_type => l_result_type,
1621 x_results => l_results);
1622
1623 d_position := 20;
1624
1625 IF l_result_type = po_validations.c_result_type_failure THEN
1626 IF (PO_LOG.d_stmt) THEN
1627 PO_LOG.stmt(d_module, d_position, 'vaidate headers return failure');
1628 END IF;
1629
1630 PO_PDOI_ERR_UTL.process_val_type_errors
1631 (
1632 x_results => l_results,
1633 p_table_name => 'PO_HEADERS_INTERFACE',
1634 p_headers => x_headers
1635 );
1636
1637 d_position := 30;
1638
1639 populate_error_flag
1640 (
1641 x_results => l_results,
1642 x_headers => x_headers
1643 );
1644 END IF;
1645
1646 d_position := 40;
1647
1648 IF l_result_type = po_validations.c_result_type_fatal THEN
1649 IF (PO_LOG.d_stmt) THEN
1650 PO_LOG.stmt(d_module, d_position, 'vaidate headers return fatal');
1651 END IF;
1652
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654 END IF;
1655
1656 d_position := 50;
1657
1658 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_VALIDATE);
1659
1660 IF (PO_LOG.d_proc) THEN
1661 PO_LOG.proc_end (d_module);
1662 END IF;
1663
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 PO_MESSAGE_S.add_exc_msg
1667 (
1668 p_pkg_name => d_pkg_name,
1669 p_procedure_name => d_api_name || '.' || d_position
1670 );
1671 RAISE;
1672 END validate_headers;
1673
1674 -------------------------------------------------------------------------
1675 --Start of Comments
1676 --Name: derive_location_id
1677 --Pre-reqs: None
1678 --Modifies:
1679 --Locks:
1680 -- None
1681 --Function:
1682 -- handle the logic to derive location_id from location code in batch mode
1683 --Parameters:
1684 --IN:
1685 -- p_key
1686 -- identifier in the temp table on the derived result
1687 -- p_index_tbl
1688 -- indexes of the records
1689 -- p_location_type
1690 -- the value can be 'SHIP_TO'/'BILL_TO'
1691 -- p_location_tbl
1692 -- values of location code in current batch of records
1693 --IN OUT:
1694 -- x_location_id_tbl
1695 -- contains the derived result if original value is null
1696 --OUT: None
1697 --Returns:
1698 --Notes:
1699 --Testing:
1700 --End of Comments
1701 ------------------------------------------------------------------------
1702 PROCEDURE derive_location_id
1703 (
1704 p_key IN po_session_gt.key%TYPE,
1705 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1706 p_location_type IN VARCHAR2,
1707 p_location_tbl IN PO_TBL_VARCHAR100,
1708 x_location_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
1709 ) IS
1710
1711 d_api_name CONSTANT VARCHAR2(30) := 'derive_location_id';
1712 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1713 d_position NUMBER;
1714
1715 -- tables to store the derived result
1716 l_index_tbl PO_TBL_NUMBER;
1717 l_result_tbl PO_TBL_NUMBER;
1718 BEGIN
1719 d_position := 0;
1720
1721 IF (PO_LOG.d_proc) THEN
1722 PO_LOG.proc_begin(d_module, 'location type', p_location_type);
1723 PO_LOG.proc_begin(d_module, 'locations', p_location_tbl);
1724 PO_LOG.proc_begin(d_module, 'location ids', x_location_id_tbl);
1725 END IF;
1726
1727 IF (p_location_type = 'SHIP_TO') THEN
1728 FORALL i IN 1..p_index_tbl.COUNT
1729 INSERT INTO po_session_gt(key, num1, num2)
1730 SELECT p_key,
1731 p_index_tbl(i),
1732 location_id
1733 FROM po_locations_val_v
1734 WHERE x_location_id_tbl(i) IS NULL
1735 AND p_location_tbl(i) IS NOT NULL
1736 AND location_code = p_location_tbl(i)
1737 AND nvl(ship_to_site_flag, 'N') = 'Y';
1738 ELSE -- p_location_type = 'BILL_TO'
1739 FORALL i IN 1..p_index_tbl.COUNT
1740 INSERT INTO po_session_gt(key, num1, num2)
1741 SELECT p_key,
1742 p_index_tbl(i),
1743 location_id
1744 FROM po_locations_val_v
1745 WHERE x_location_id_tbl(i) IS NULL
1746 AND p_location_tbl(i) IS NOT NULL
1747 AND location_code = p_location_tbl(i)
1748 AND nvl(bill_to_site_flag, 'N') = 'Y';
1749 END IF;
1750
1751 d_position := 10;
1752
1753 DELETE FROM po_session_gt
1754 WHERE key = p_key
1755 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1756
1757 d_position := 20;
1758
1759 FOR i IN 1..l_index_tbl.COUNT
1760 LOOP
1761 IF (PO_LOG.d_stmt) THEN
1762 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1763 PO_LOG.stmt(d_module, d_position, 'new location id', l_result_tbl(i));
1764 END IF;
1765
1766 x_location_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
1767 END LOOP;
1768
1769 IF (PO_LOG.d_proc) THEN
1770 PO_LOG.proc_end(d_module);
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 PO_MESSAGE_S.add_exc_msg
1776 (
1777 p_pkg_name => d_pkg_name,
1778 p_procedure_name => d_api_name || '.' || d_position
1779 );
1780 RAISE;
1781 END derive_location_id;
1782
1783 -------------------------------------------------------------------------
1784 --Start of Comments
1785 --Name: derive_terms_id
1786 --Pre-reqs: None
1787 --Modifies:
1788 --Locks:
1789 -- None
1790 --Function:
1791 -- handle the logic to derive terms_id from payment_terms in batch mode
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 -- p_payment_terms_tbl
1799 -- values of payment terms in current batch of records
1800 --IN OUT:
1801 -- x_terms_id_tbl
1802 -- contains the derived result if original value is null
1803 --OUT: None
1804 --Returns:
1805 --Notes:
1806 --Testing:
1807 --End of Comments
1808 ------------------------------------------------------------------------
1809 PROCEDURE derive_terms_id
1810 (
1811 p_key IN po_session_gt.key%TYPE,
1812 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1813 p_payment_terms_tbl IN PO_TBL_VARCHAR100,
1814 x_terms_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
1815 ) IS
1816
1817 d_api_name CONSTANT VARCHAR2(30) := 'derive_terms_id';
1818 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1819 d_position NUMBER;
1820
1821 -- tables to store the derived result
1822 l_index_tbl PO_TBL_NUMBER;
1823 l_result_tbl PO_TBL_NUMBER;
1824 BEGIN
1825 d_position := 0;
1826
1827 IF (PO_LOG.d_proc) THEN
1828 PO_LOG.proc_begin(d_module, 'payment terms', p_payment_terms_tbl);
1829 PO_LOG.proc_begin(d_module, 'terms ids', x_terms_id_tbl);
1830 END IF;
1831
1832 FORALL i IN 1..p_index_tbl.COUNT
1833 INSERT INTO po_session_gt(key, num1, num2)
1834 SELECT p_key,
1835 p_index_tbl(i),
1836 term_id
1837 FROM ap_terms
1838 WHERE x_terms_id_tbl(i) IS NULL
1839 AND p_payment_terms_tbl(i) IS NOT NULL
1840 AND name = p_payment_terms_tbl(i)
1841 AND enabled_flag = 'Y'
1842 AND TRUNC(sysdate) between TRUNC(nvl(start_date_active, sysdate))
1843 AND TRUNC(nvl(end_date_active, sysdate));
1844
1845 d_position := 10;
1846
1847 DELETE FROM po_session_gt
1848 WHERE key = p_key
1849 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1850
1851 d_position := 20;
1852
1853 FOR i IN 1..l_index_tbl.COUNT
1854 LOOP
1855 IF (PO_LOG.d_stmt) THEN
1856 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
1857 PO_LOG.stmt(d_module, d_position, 'new terms id', l_result_tbl(i));
1858 END IF;
1859
1860 x_terms_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
1861 END LOOP;
1862
1863 IF (PO_LOG.d_proc) THEN
1864 PO_LOG.proc_end (d_module);
1865 END IF;
1866
1867 EXCEPTION
1868 WHEN OTHERS THEN
1869 PO_MESSAGE_S.add_exc_msg
1870 (
1871 p_pkg_name => d_pkg_name,
1872 p_procedure_name => d_api_name || '.' || d_position
1873 );
1874 RAISE;
1875 END derive_terms_id;
1876
1877 -------------------------------------------------------------------------
1878 --Start of Comments
1879 --Name: derive_vendor_id
1880 --Pre-reqs: None
1881 --Modifies:
1882 --Locks:
1883 -- None
1884 --Function:
1885 -- handle the logic to derive vendor_id from vendor_name or vendor_num
1886 -- in batch mode
1887 --Parameters:
1888 --IN:
1889 -- p_key
1890 -- identifier in the temp table on the derived result
1891 -- p_index_tbl
1892 -- indexes of the records
1893 -- p_vendor_name_tbl
1894 -- values of vendor name in current batch of records
1895 -- p_vendor_num_tbl
1896 -- values of vendor num in current batch of records
1897 --IN OUT:
1898 -- x_vendor_id_tbl
1899 -- contains the derived result if original value is null
1900 --OUT: None
1901 --Returns:
1902 --Notes:
1903 --Testing:
1904 --End of Comments
1905 ------------------------------------------------------------------------
1906 PROCEDURE derive_vendor_id
1907 (
1908 p_key IN po_session_gt.key%TYPE,
1909 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
1910 p_vendor_name_tbl IN PO_TBL_VARCHAR2000,
1911 p_vendor_num_tbl IN PO_TBL_VARCHAR30,
1912 x_vendor_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
1913 ) IS
1914
1915 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_id';
1916 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1917 d_position NUMBER;
1918
1919 -- tables to store the derived result
1920 l_index_tbl PO_TBL_NUMBER;
1921 l_result_tbl PO_TBL_NUMBER;
1922
1923 -- variable to hold the current index of the row processed
1924 l_index NUMBER;
1925 BEGIN
1926 d_position := 0;
1927
1928 IF (PO_LOG.d_proc) THEN
1929 PO_LOG.proc_begin(d_module, 'vendor names', p_vendor_name_tbl);
1930 PO_LOG.proc_begin(d_module, 'vendor nums', p_vendor_num_tbl);
1931 PO_LOG.proc_begin(d_module, 'vendor ids', x_vendor_id_tbl);
1932 END IF;
1933
1934 FORALL i IN 1..p_index_tbl.COUNT
1935 INSERT INTO po_session_gt(key, num1, num2)
1936 SELECT p_key,
1937 p_index_tbl(i),
1938 vendor_id
1939 FROM po_vendors
1940 WHERE x_vendor_id_tbl(i) IS NULL
1941 AND (p_vendor_name_tbl(i) IS NOT NULL OR p_vendor_num_tbl(i) IS NOT NULL)
1942 AND (vendor_name = p_vendor_name_tbl(i) OR
1943 segment1 = p_vendor_num_tbl(i));
1944
1945 d_position := 10;
1946
1947 DELETE FROM po_session_gt
1948 WHERE key = p_key
1949 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
1950
1951 d_position := 20;
1952
1953 -- There can be 3 types of result from above derivation logic:
1954 -- 1. No vendor_id can be derived: fine, we leave the vendor_id as NULL;
1955 -- 2. One vendor_id can be derived: the value will be set back
1956 -- 3. Two vendor_ids are derived from vendor_name and vendor_num: we
1957 -- should leave vendor_id as null
1958 FOR i IN 1..l_index_tbl.COUNT
1959 LOOP
1960 l_index := l_index_tbl(i);
1961
1962 IF (PO_LOG.d_stmt) THEN
1963 PO_LOG.stmt(d_module, d_position, 'index', l_index);
1964 PO_LOG.stmt(d_module, d_position, 'new vendor id', l_result_tbl(i));
1965 END IF;
1966
1967 IF (x_vendor_id_tbl(l_index) IS NULL) THEN
1968 x_vendor_id_tbl(l_index) := l_result_tbl(i);
1969 ELSE
1970 x_vendor_id_tbl(l_index) := NULL;
1971 END IF;
1972 END LOOP;
1973
1974 IF (PO_LOG.d_proc) THEN
1975 PO_LOG.proc_end (d_module);
1976 END IF;
1977
1978 EXCEPTION
1979 WHEN OTHERS THEN
1980 PO_MESSAGE_S.add_exc_msg
1981 (
1982 p_pkg_name => d_pkg_name,
1983 p_procedure_name => d_api_name || '.' || d_position
1984 );
1985 RAISE;
1986 END derive_vendor_id;
1987
1988 -------------------------------------------------------------------------
1989 --------------------- PRIVATE PROCEDURES --------------------------------
1990 -------------------------------------------------------------------------
1991
1992 -------------------------------------------------------------------------
1993 --Start of Comments
1994 --Name: derive_rate_type_code
1995 --Pre-reqs: None
1996 --Modifies:
1997 --Locks:
1998 -- None
1999 --Function:
2000 -- handle the logic to derive rate_type_code from rate_type in batch mode
2001 --Parameters:
2002 --IN:
2003 -- p_key
2004 -- identifier in the temp table on the derived result
2005 -- p_index_tbl
2006 -- indexes of the records
2007 -- p_rate_type_tbl
2008 -- values of rate type in current batch of records
2009 --IN OUT:
2010 -- x_rate_type_code_tbl
2011 -- contains the derived result if original value is null
2012 --OUT: None
2013 --Returns:
2014 --Notes:
2015 --Testing:
2016 --End of Comments
2017 ------------------------------------------------------------------------
2018 PROCEDURE derive_rate_type_code
2019 (
2020 p_key IN po_session_gt.key%TYPE,
2021 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2022 p_rate_type_tbl IN PO_TBL_VARCHAR30,
2023 x_rate_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
2024 ) IS
2025
2026 d_api_name CONSTANT VARCHAR2(30) := 'derive_rate_type_code';
2027 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2028 d_position NUMBER;
2029
2030 -- tables to store the derived result
2031 l_index_tbl PO_TBL_NUMBER;
2032 l_result_tbl PO_TBL_VARCHAR30;
2033 BEGIN
2034 d_position := 0;
2035
2036 IF (PO_LOG.d_proc) THEN
2037 PO_LOG.proc_begin(d_module, 'rate type', p_rate_type_tbl);
2038 PO_LOG.proc_begin(d_module, 'rate type code', x_rate_type_code_tbl);
2039 END IF;
2040
2041 FORALL i IN 1..p_index_tbl.COUNT
2042 INSERT INTO po_session_gt(key, num1, char1)
2043 SELECT p_key,
2044 p_index_tbl(i),
2045 conversion_type
2046 FROM gl_daily_conversion_types
2047 WHERE x_rate_type_code_tbl(i) IS NULL
2048 AND p_rate_type_tbl(i) IS NOT NULL
2049 AND user_conversion_type = p_rate_type_tbl(i);
2050
2051 d_position := 10;
2052
2053 DELETE FROM po_session_gt
2054 WHERE key = p_key
2055 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2056
2057 d_position := 20;
2058
2059 FOR i IN 1..l_index_tbl.COUNT
2060 LOOP
2061 IF (PO_LOG.d_stmt) THEN
2062 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2063 PO_LOG.stmt(d_module, d_position, 'new rate type code', l_result_tbl(i));
2064 END IF;
2065
2066 x_rate_type_code_tbl(l_index_tbl(i)) := l_result_tbl(i);
2067 END LOOP;
2068
2069 IF (PO_LOG.d_proc) THEN
2070 PO_LOG.proc_end (d_module);
2071 END IF;
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 PO_MESSAGE_S.add_exc_msg
2076 (
2077 p_pkg_name => d_pkg_name,
2078 p_procedure_name => d_api_name || '.' || d_position
2079 );
2080 RAISE;
2081 END derive_rate_type_code;
2082
2083 -------------------------------------------------------------------------
2084 --Start of Comments
2085 --Name: derive_agent_id
2086 --Pre-reqs: None
2087 --Modifies:
2088 --Locks:
2089 -- None
2090 --Function:
2091 -- handle the logic to derive agent_id from agent_name in batch mode
2092 --Parameters:
2093 --IN:
2094 -- p_key
2095 -- identifier in the temp table on the derived result
2096 -- p_index_tbl
2097 -- indexes of the records
2098 -- p_agent_name_tbl
2099 -- values of agent name in current batch of records
2100 --IN OUT:
2101 -- x_agent_id_tbl
2102 -- contains the derived result if original value is null
2103 --OUT: None
2104 --Returns:
2105 --Notes:
2106 --Testing:
2107 --End of Comments
2108 ------------------------------------------------------------------------
2109 PROCEDURE derive_agent_id
2110 (
2111 p_key IN po_session_gt.key%TYPE,
2112 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2113 p_agent_name_tbl IN PO_TBL_VARCHAR2000,
2114 x_agent_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2115 ) IS
2116
2117 d_api_name CONSTANT VARCHAR2(30) := 'derive_agent_id';
2118 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2119 d_position NUMBER;
2120
2121 -- tables to store the derived result
2122 l_index_tbl PO_TBL_NUMBER;
2123 l_result_tbl PO_TBL_NUMBER;
2124 BEGIN
2125 d_position := 0;
2126
2127 IF (PO_LOG.d_proc) THEN
2128 PO_LOG.proc_begin(d_module, 'agent name', p_agent_name_tbl);
2129 PO_LOG.proc_begin(d_module, 'agent id', x_agent_id_tbl);
2130 END IF;
2131
2132 FORALL i IN 1..p_index_tbl.COUNT
2133 INSERT INTO po_session_gt(key, num1, num2)
2134 SELECT p_key,
2135 p_index_tbl(i),
2136 employee_id
2137 FROM po_buyers_val_v
2138 WHERE x_agent_id_tbl(i) IS NULL
2139 AND p_agent_name_tbl(i) IS NOT NULL
2140 AND full_name = p_agent_name_tbl(i);
2141
2142 d_position := 10;
2143
2144 DELETE FROM po_session_gt
2145 WHERE key = p_key
2146 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2147
2148 d_position := 20;
2149
2150 FOR i IN 1..l_index_tbl.COUNT
2151 LOOP
2152 IF (PO_LOG.d_stmt) THEN
2153 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2154 PO_LOG.stmt(d_module, d_position, 'new agent id', l_result_tbl(i));
2155 END IF;
2156
2157 x_agent_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2158 END LOOP;
2159
2160 IF (PO_LOG.d_proc) THEN
2161 PO_LOG.proc_end (d_module);
2162 END IF;
2163
2164 EXCEPTION
2165 WHEN OTHERS THEN
2166 PO_MESSAGE_S.add_exc_msg
2167 (
2168 p_pkg_name => d_pkg_name,
2169 p_procedure_name => d_api_name || '.' || d_position
2170 );
2171 END derive_agent_id;
2172
2173 -------------------------------------------------------------------------
2174 --Start of Comments
2175 --Name: derive_vendor_site_id
2176 --Pre-reqs: None
2177 --Modifies:
2178 --Locks:
2179 -- None
2180 --Function:
2181 -- handle the logic to derive vendor_site_id from vendor_site_code
2182 -- and vendor_id in batch mode
2183 --Parameters:
2184 --IN:
2185 -- p_key
2186 -- identifier in the temp table on the derived result
2187 -- p_index_tbl
2188 -- indexes of the records
2189 -- p_vendor_id_tbl
2190 -- values of vendor id in current batch of records
2191 -- p_vendor_site_code_tbl
2192 -- values of vendor site codes in current batch of records
2193 --IN OUT:
2194 -- x_vendor_site_id_tbl
2195 -- contains the derived result if original value is null
2196 --OUT: None
2197 --Returns:
2198 --Notes:
2199 --Testing:
2200 --End of Comments
2201 ------------------------------------------------------------------------
2202 PROCEDURE derive_vendor_site_id
2203 (
2204 p_key IN po_session_gt.key%TYPE,
2205 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2206 p_vendor_id_tbl IN PO_TBL_NUMBER,
2207 p_vendor_site_code_tbl IN PO_TBL_VARCHAR30,
2208 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2209 ) IS
2210
2211 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_site_id';
2212 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2213 d_position NUMBER;
2214
2215 -- tables to store the derived result
2216 l_index_tbl PO_TBL_NUMBER;
2217 l_result_tbl PO_TBL_NUMBER;
2218 BEGIN
2219 d_position := 0;
2220
2221 IF (PO_LOG.d_proc) THEN
2222 PO_LOG.proc_begin(d_module, 'vendor id', p_vendor_id_tbl);
2223 PO_LOG.proc_begin(d_module, 'site code', p_vendor_site_code_tbl);
2224 PO_LOG.proc_begin(d_module, 'site id', x_vendor_site_id_tbl);
2225 END IF;
2226
2227 FORALL i IN 1..p_index_tbl.COUNT
2228 INSERT INTO po_session_gt(key, num1, num2)
2229 SELECT p_key,
2230 p_index_tbl(i),
2231 vendor_site_id
2232 FROM po_supplier_sites_val_v
2233 WHERE x_vendor_site_id_tbl(i) IS NULL
2234 AND p_vendor_site_code_tbl(i) IS NOT NULL
2235 AND p_vendor_id_tbl(i) IS NOT NULL
2236 AND vendor_id = p_vendor_id_tbl(i)
2237 AND vendor_site_code = p_vendor_site_code_tbl(i);
2238
2239 d_position := 10;
2240
2241 DELETE FROM po_session_gt
2242 WHERE key = p_key
2243 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2244
2245 d_position := 20;
2246
2247 FOR i IN 1..l_index_tbl.COUNT
2248 LOOP
2249 IF (PO_LOG.d_stmt) THEN
2250 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2251 PO_LOG.stmt(d_module, d_position, 'new site id', l_result_tbl(i));
2252 END IF;
2253
2254 x_vendor_site_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2255 END LOOP;
2256
2257 IF (PO_LOG.d_proc) THEN
2258 PO_LOG.proc_end (d_module);
2259 END IF;
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 PO_MESSAGE_S.add_exc_msg
2264 (
2265 p_pkg_name => d_pkg_name,
2266 p_procedure_name => d_api_name || '.' || d_position
2267 );
2268 RAISE;
2269 END derive_vendor_site_id;
2270
2271 -------------------------------------------------------------------------
2272 --Start of Comments
2273 --Name: derive_vendor_contact_id
2274 --Pre-reqs: None
2275 --Modifies:
2276 --Locks:
2277 -- None
2278 --Function:
2279 -- handle the logic to derive vendor_contac_id from vendor_contact
2280 -- and vendor_site_id in batch mode
2281 --Parameters:
2282 --IN:
2283 -- p_key
2284 -- identifier in the temp table on the derived result
2285 -- p_index_tbl
2286 -- indexes of the records
2287 -- p_vendor_site_id_tbl
2288 -- value of vendor site id in current batch mode
2289 -- p_vendor_contact_tbl
2290 -- values of vendor contact in current batch of records
2291 --IN OUT:
2292 -- x_vendor_contact_id_tbl
2293 -- contains the derived result if original value is null
2294 --OUT: None
2295 --Returns:
2296 --Notes:
2297 --Testing:
2298 --End of Comments
2299 ------------------------------------------------------------------------
2300 PROCEDURE derive_vendor_contact_id
2301 (
2302 p_key IN po_session_gt.key%TYPE,
2303 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2304 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
2305 p_vendor_contact_tbl IN PO_TBL_VARCHAR2000,
2306 x_vendor_contact_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2307 ) IS
2308
2309 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_contact_id';
2310 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2311 d_position NUMBER;
2312
2313 -- tables to store the derived result
2314 l_index_tbl PO_TBL_NUMBER;
2315 l_result_tbl PO_TBL_NUMBER;
2316 BEGIN
2317 d_position := 0;
2318
2319 IF (PO_LOG.d_proc) THEN
2320 PO_LOG.proc_begin(d_module, 'site id', p_vendor_site_id_tbl);
2321 PO_LOG.proc_begin(d_module, 'contact', p_vendor_contact_tbl);
2322 PO_LOG.proc_begin(d_module, 'contact id', x_vendor_contact_id_tbl);
2323 END IF;
2324
2325 FORALL i IN 1..p_index_tbl.COUNT
2326 INSERT INTO po_session_gt(key, num1, num2)
2327 SELECT p_key,
2328 p_index_tbl(i),
2329 vendor_contact_id
2330 FROM po_vendor_contacts
2331 WHERE x_vendor_contact_id_tbl(i) IS NULL
2332 AND p_vendor_contact_tbl(i) IS NOT NULL
2333 AND p_vendor_site_id_tbl(i) IS NOT NULL
2334 AND last_name||' '||first_name = p_vendor_contact_tbl(i)
2335 AND vendor_site_id = p_vendor_site_id_tbl(i);
2336
2337 d_position := 10;
2338
2339 DELETE FROM po_session_gt
2340 WHERE key = p_key
2341 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2342
2343 d_position := 20;
2344
2345 FOR i IN 1..l_index_tbl.COUNT
2346 LOOP
2347 IF (PO_LOG.d_stmt) THEN
2348 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2349 PO_LOG.stmt(d_module, d_position, 'new contact id', l_result_tbl(i));
2350 END IF;
2351
2352 x_vendor_contact_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2353 END LOOP;
2354
2355 IF (PO_LOG.d_proc) THEN
2356 PO_LOG.proc_end (d_module);
2357 END IF;
2358
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 PO_MESSAGE_S.add_exc_msg
2362 (
2363 p_pkg_name => d_pkg_name,
2364 p_procedure_name => d_api_name || '.' || d_position
2365 );
2366 RAISE;
2367 END derive_vendor_contact_id;
2368
2369
2370 -------------------------------------------------------------------------
2371 --Start of Comments
2372 --Name: derive_vendor_contact_id
2373 --Pre-reqs: None
2374 --Modifies:
2375 --Locks:
2376 -- None
2377 --Function:
2378 -- handle the logic to derive vendor_contac_id from vendor_contact
2379 -- and vendor_site_id in batch mode
2380 --Parameters:
2381 --IN:
2382 -- p_key
2383 -- identifier in the temp table on the derived result
2384 -- p_index_tbl
2385 -- indexes of the records
2386 -- p_vendor_site_id_tbl
2387 -- value of vendor site id in current batch mode
2388 -- p_vendor_contact_tbl
2389 -- values of vendor contact in current batch of records
2390 --IN OUT:
2391 -- x_vendor_contact_id_tbl
2392 -- contains the derived result if original value is null
2393 --OUT: None
2394 --Returns:
2395 --Notes:
2396 --Testing:
2397 --End of Comments
2398 ------------------------------------------------------------------------
2399 PROCEDURE derive_style_id
2400 (
2401 p_key IN po_session_gt.key%TYPE,
2402 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2403 p_style_display_name_tbl IN PO_TBL_VARCHAR2000,
2404 x_style_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2405 ) IS
2406
2407 d_api_name CONSTANT VARCHAR2(30) := 'derive_style_id';
2408 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2409 d_position NUMBER;
2410
2411 -- tables to store the derived result
2412 l_index_tbl PO_TBL_NUMBER;
2413 l_result_tbl PO_TBL_NUMBER;
2414 BEGIN
2415 d_position := 0;
2416
2417 IF (PO_LOG.d_proc) THEN
2418 PO_LOG.proc_begin(d_module, 'x_style_id_tbl', x_style_id_tbl);
2419 PO_LOG.proc_begin(d_module, 'p_style_display_name_tbl', p_style_display_name_tbl);
2420 END IF;
2421
2422 FORALL i IN 1..p_index_tbl.COUNT
2423 INSERT INTO po_session_gt(key, num1, num2)
2424 SELECT p_key,
2425 p_index_tbl(i),
2426 style_id
2427 FROM po_doc_style_lines_tl pds
2428 WHERE x_style_id_tbl(i) IS NULL AND
2429 pds.display_name = p_style_display_name_tbl(i) AND
2430 pds.LANGUAGE = USERENV('LANG');
2431
2432 d_position := 10;
2433
2434 DELETE FROM po_session_gt
2435 WHERE key = p_key
2436 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2437
2438 d_position := 20;
2439
2440 FOR i IN 1..l_index_tbl.COUNT
2441 LOOP
2442 IF (PO_LOG.d_stmt) THEN
2443 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2444 PO_LOG.stmt(d_module, d_position, 'new style id', l_result_tbl(i));
2445 END IF;
2446
2447 x_style_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2448 END LOOP;
2449
2450 IF (PO_LOG.d_proc) THEN
2451 PO_LOG.proc_end (d_module);
2452 END IF;
2453
2454 EXCEPTION
2455 WHEN OTHERS THEN
2456 PO_MESSAGE_S.add_exc_msg
2457 (
2458 p_pkg_name => d_pkg_name,
2459 p_procedure_name => d_api_name || '.' || d_position
2460 );
2461 RAISE;
2462
2463 END derive_style_id;
2464
2465 -------------------------------------------------------------------------
2466 --Start of Comments
2467 --Name: derive_from_header_id
2468 --Pre-reqs: None
2469 --Modifies:
2470 --Locks:
2471 -- None
2472 --Function:
2473 -- handle the logic to derive from_header_id from from_rfq_num in batch mode
2474 --Parameters:
2475 --IN:
2476 -- p_key
2477 -- identifier in the temp table on the derived result
2478 -- p_index_tbl
2479 -- indexes of the records
2480 -- p_from_rfq_num_tbl
2481 -- values of from quotation document number in current batch of records
2482 --IN OUT:
2483 -- x_from_header_id_tbl
2484 -- contains the derived result if original value is null
2485 --OUT: None
2486 --Returns:
2487 --Notes:
2488 --Testing:
2489 --End of Comments
2490 ------------------------------------------------------------------------
2491 PROCEDURE derive_from_header_id
2492 (
2493 p_key IN po_session_gt.key%TYPE,
2494 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2495 p_from_rfq_num_tbl IN PO_TBL_VARCHAR30,
2496 x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2497 ) IS
2498
2499 d_api_name CONSTANT VARCHAR2(30) := 'derive_from_header_id';
2500 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2501 d_position NUMBER;
2502
2503 -- tables to store the derived result
2504 l_index_tbl PO_TBL_NUMBER;
2505 l_result_tbl PO_TBL_NUMBER;
2506 BEGIN
2507 d_position := 0;
2508
2509 IF (PO_LOG.d_proc) THEN
2510 PO_LOG.proc_begin(d_module, 'from rfq num', p_from_rfq_num_tbl);
2511 PO_LOG.proc_begin(d_module, 'from header id', x_from_header_id_tbl);
2512 END IF;
2513
2514 FORALL i IN 1..p_index_tbl.COUNT
2515 INSERT INTO po_session_gt(key, num1, num2)
2516 SELECT p_key,
2517 p_index_tbl(i),
2518 po_header_id
2519 FROM po_headers
2520 WHERE x_from_header_id_tbl(i) IS NULL
2521 AND p_from_rfq_num_tbl(i) IS NOT NULL
2522 AND segment1 = p_from_rfq_num_tbl(i)
2523 AND type_lookup_code = 'RFQ'; -- PO_PDOI_CONSTANTS.g_DOC_TYPE_RFQ;
2524
2525 d_position := 10;
2526
2527 DELETE FROM po_session_gt
2528 WHERE key = p_key
2529 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2530
2531 d_position := 20;
2532
2533 FOR i IN 1..l_index_tbl.COUNT
2534 LOOP
2535 IF (PO_LOG.d_stmt) THEN
2536 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2537 PO_LOG.stmt(d_module, d_position, 'new from header id', l_result_tbl(i));
2538 END IF;
2539
2540 x_from_header_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2541 END LOOP;
2542
2543 IF (PO_LOG.d_proc) THEN
2544 PO_LOG.proc_end (d_module);
2545 END IF;
2546
2547 EXCEPTION
2548 WHEN OTHERS THEN
2549 PO_MESSAGE_S.add_exc_msg
2550 (
2551 p_pkg_name => d_pkg_name,
2552 p_procedure_name => d_api_name || '.' || d_position
2553 );
2554 RAISE;
2555 END derive_from_header_id;
2556
2557 -------------------------------------------------------------------------
2558 --Start of Comments
2559 --Name: default_info_from_vendor
2560 --Pre-reqs: None
2561 --Modifies:
2562 --Locks:
2563 -- None
2564 --Function:
2565 -- handle the logic to default attribute values from vendor specification
2566 -- in a batch mode
2567 --Parameters:
2568 --IN:
2569 -- p_key
2570 -- identifier in the temp table on the derived result
2571 -- p_index_tbl
2572 -- indexes of the records
2573 -- p_vendor_id_tbl
2574 -- values of vendor id in current batch of records
2575 --IN OUT: None
2576 --OUT:
2577 -- x_invoice_currency_code_tbl
2578 -- values of invoice currency code defined on vendor level
2579 -- x_terms_id_tbl
2580 -- values of terms id defined on vendor level
2581 --Returns:
2582 --Notes:
2583 --Testing:
2584 --End of Comments
2585 ------------------------------------------------------------------------
2586 PROCEDURE default_info_from_vendor
2587 (
2588 p_key IN po_session_gt.key%TYPE,
2589 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2590 p_vendor_id_tbl IN PO_TBL_NUMBER,
2591 x_invoice_currency_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2592 x_terms_id_tbl OUT NOCOPY PO_TBL_NUMBER
2593 ) IS
2594
2595 d_api_name CONSTANT VARCHAR2(30) := 'default_info_from_vendor';
2596 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2597 d_position NUMBER;
2598
2599 -- variables to hold values read from vendor definition
2600 l_index_tbl PO_TBL_NUMBER;
2601 l_currency_code_tbl PO_TBL_VARCHAR30;
2602 l_terms_id_tbl PO_TBL_NUMBER;
2603
2604 -- variable to hold index of the current processing row
2605 l_index NUMBER;
2606
2607 BEGIN
2608 d_position := 0;
2609
2610 IF (PO_LOG.d_proc) THEN
2611 PO_LOG.proc_begin(d_module, 'vendor ids', p_vendor_id_tbl);
2612 END IF;
2613
2614 -- Initialize OUT parameters
2615
2616 -- <Bug 4546121: Supplier TCA conversion>
2617 -- The following columns are being obsoleted from PO_VENDORS level
2618 --x_fob_tbl := PO_TBL_VARCHAR30();
2619 --x_freight_carrier_tbl := PO_TBL_VARCHAR30();
2620 --x_freight_term_tbl := PO_TBL_VARCHAR30();
2621 --x_ship_to_loc_id_tbl := PO_TBL_NUMBER();
2622 --x_bill_to_loc_id_tbl := PO_TBL_NUMBER();
2623
2624 x_invoice_currency_code_tbl := PO_TBL_VARCHAR30();
2625 x_terms_id_tbl := PO_TBL_NUMBER();
2626
2627 x_invoice_currency_code_tbl.EXTEND(p_index_tbl.COUNT);
2628 x_terms_id_tbl.EXTEND(p_index_tbl.COUNT);
2629
2630 FORALL i IN 1..p_index_tbl.COUNT
2631 INSERT INTO po_session_gt(
2632 key, num1, char1, num2)
2633 SELECT p_key,
2634 p_index_tbl(i),
2635 invoice_currency_code,
2636 terms_id
2637 FROM po_vendors
2638 WHERE vendor_id = p_vendor_id_tbl(i);
2639
2640 d_position := 10;
2641
2642 DELETE FROM po_session_gt
2643 WHERE key = p_key
2644 RETURNING num1, char1, num2
2645 BULK COLLECT INTO
2646 l_index_tbl,
2647 l_currency_code_tbl,
2648 l_terms_id_tbl;
2649
2650 d_position := 20;
2651
2652 IF (PO_LOG.d_stmt) THEN
2653 PO_LOG.stmt(d_module, d_position, 'l_index_tbl.COUNT', l_index_tbl.COUNT);
2654 END IF;
2655
2656 FOR i IN 1..l_index_tbl.COUNT
2657 LOOP
2658 l_index := l_index_tbl(i);
2659
2660 IF (PO_LOG.d_stmt) THEN
2661 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2662 PO_LOG.stmt(d_module, d_position, 'new currency', l_currency_code_tbl(i));
2663 PO_LOG.stmt(d_module, d_position, 'new terms id', l_terms_id_tbl(i));
2664 END IF;
2665
2666 x_invoice_currency_code_tbl(l_index) := l_currency_code_tbl(i);
2667 x_terms_id_tbl(l_index) := l_terms_id_tbl(i);
2668 END LOOP;
2669
2670 d_position := 30;
2671
2672 IF (PO_LOG.d_proc) THEN
2673 PO_LOG.proc_end (d_module);
2674 END IF;
2675
2676 EXCEPTION
2677 WHEN OTHERS THEN
2678 PO_MESSAGE_S.add_exc_msg
2679 (
2680 p_pkg_name => d_pkg_name,
2681 p_procedure_name => d_api_name || '.' || d_position
2682 );
2683 RAISE;
2684 END default_info_from_vendor;
2685
2686 -------------------------------------------------------------------------
2687 --Start of Comments
2688 --Name: default_info_from_vendor_site
2689 --Pre-reqs: None
2690 --Modifies:
2691 --Locks:
2692 -- None
2693 --Function:
2694 -- handle the logic to default attribute values from vendor site
2695 -- specification in a batch mode
2696 --Parameters:
2697 --IN:
2698 -- p_key
2699 -- identifier in the temp table on the derived result
2700 -- p_index_tbl
2701 -- indexes of the records
2702 -- p_vendor_id_tbl
2703 -- values of vendor id in current batch of records
2704 --IN OUT:
2705 -- x_vendor_site_id_tbl
2706 -- if original value is empty, we try to default vendor site id
2707 -- from vendor id first; then perform the default logic of other
2708 -- attributes based on new value of vendor site id--
2709 --OUT:
2710 -- x_fob_tbl
2711 -- values of fob defined on vendor site level
2712 -- x_freight_carrier_tbl
2713 -- values of freight carrier defined on site level
2714 -- x_freight_term_tbl
2715 -- values of freight term defined on site level
2716 -- x_ship_to_loc_id_tbl
2717 -- values of ship to location id defined on site level
2718 -- x_bill_to_loc_id_tbl
2719 -- values of bill to location id defined on site level
2720 -- x_invoice_currency_code_tbl
2721 -- values of invoice currency code defined on site level
2722 -- x_terms_id_tbl
2723 -- values of terms id defined on site level
2724 -- x_shipping_control_tbl
2725 -- values of shipping control defined on site level
2726 -- x_pay_on_code_tbl
2727 -- values of pay on code defined on site level
2728 --Returns:
2729 --Notes:
2730 --Testing:
2731 --End of Comments
2732 ------------------------------------------------------------------------
2733 PROCEDURE default_info_from_vendor_site
2734 (
2735 p_key IN po_session_gt.key%TYPE,
2736 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2737 p_vendor_id_tbl IN PO_TBL_NUMBER,
2738 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
2739 x_fob_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2740 x_freight_carrier_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2741 x_freight_term_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2742 x_ship_to_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
2743 x_bill_to_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
2744 x_invoice_currency_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2745 x_terms_id_tbl OUT NOCOPY PO_TBL_NUMBER,
2746 x_shipping_control_tbl OUT NOCOPY PO_TBL_VARCHAR30,
2747 x_pay_on_code_tbl OUT NOCOPY PO_TBL_VARCHAR30
2748 ) IS
2749
2750 d_api_name CONSTANT VARCHAR2(30) := 'default_info_from_vendor_site';
2751 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2752 d_position NUMBER;
2753
2754 -- variables to hold values read from vendor definition
2755 l_index_tbl PO_TBL_NUMBER;
2756 l_vendor_site_id_tbl PO_TBL_NUMBER;
2757 l_fob_tbl PO_TBL_VARCHAR30;
2758 l_freight_carrier_tbl PO_TBL_VARCHAR30;
2759 l_freight_term_tbl PO_TBL_VARCHAR30;
2760 l_ship_to_loc_id_tbl PO_TBL_NUMBER;
2761 l_bill_to_loc_id_tbl PO_TBL_NUMBER;
2762 l_currency_code_tbl PO_TBL_VARCHAR30;
2763 l_terms_id_tbl PO_TBL_NUMBER;
2764 l_shipping_control_tbl PO_TBL_VARCHAR30;
2765 l_pay_on_code_tbl PO_TBL_VARCHAR30;
2766
2767 -- variable to hold index of the current processing row
2768 l_index NUMBER;
2769
2770 BEGIN
2771 d_position := 0;
2772
2773 IF (PO_LOG.d_proc) THEN
2774 PO_LOG.proc_begin(d_module, 'vendor ids', p_vendor_id_tbl);
2775 PO_LOG.proc_begin(d_module, 'vendor site ids', x_vendor_site_id_tbl);
2776 END IF;
2777
2778 x_fob_tbl := PO_TBL_VARCHAR30();
2779 x_freight_carrier_tbl := PO_TBL_VARCHAR30();
2780 x_freight_term_tbl := PO_TBL_VARCHAR30();
2781 x_ship_to_loc_id_tbl := PO_TBL_NUMBER();
2782 x_bill_to_loc_id_tbl := PO_TBL_NUMBER();
2783 x_invoice_currency_code_tbl := PO_TBL_VARCHAR30();
2784 x_terms_id_tbl := PO_TBL_NUMBER();
2785 x_shipping_control_tbl := PO_TBL_VARCHAR30();
2786 x_pay_on_code_tbl := PO_TBL_VARCHAR30();
2787
2788 x_fob_tbl.EXTEND(p_index_tbl.COUNT);
2789 x_freight_carrier_tbl.EXTEND(p_index_tbl.COUNT);
2790 x_freight_term_tbl.EXTEND(p_index_tbl.COUNT);
2791 x_ship_to_loc_id_tbl.EXTEND(p_index_tbl.COUNT);
2792 x_bill_to_loc_id_tbl.EXTEND(p_index_tbl.COUNT);
2793 x_invoice_currency_code_tbl.EXTEND(p_index_tbl.COUNT);
2794 x_terms_id_tbl.EXTEND(p_index_tbl.COUNT);
2795 x_shipping_control_tbl.EXTEND(p_index_tbl.COUNT);
2796 x_pay_on_code_tbl.EXTEND(p_index_tbl.COUNT);
2797
2798 d_position := 10;
2799
2800 -- default vendor_site_id if it is empty
2801 FORALL i IN 1..p_index_tbl.COUNT
2802 INSERT INTO po_session_gt(key, num1, num2, num3)
2803 SELECT p_key,
2804 p_index_tbl(i),
2805 min(vendor_site_id),
2806 vendor_id
2807 FROM po_vendor_sites
2808 WHERE p_vendor_id_tbl(i) IS NOT NULL
2809 AND x_vendor_site_id_tbl(i) IS NULL
2810 AND vendor_id = p_vendor_id_tbl(i)
2811 AND purchasing_site_flag = 'Y'
2812 AND (sysdate) < nvl(inactive_date, TRUNC(sysdate + 1))
2813 AND DECODE(PO_PDOI_PARAMS.g_request.document_type,
2814 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION, 'N',
2815 NVL(rfq_only_site_flag, 'N')) <> 'Y'
2816 GROUP BY vendor_id
2817 HAVING count(vendor_site_id) = 1;
2818
2819 d_position := 20;
2820
2821 DELETE FROM po_session_gt
2822 WHERE key = p_key
2823 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_vendor_site_id_tbl;
2824
2825 FOR i IN 1..l_index_tbl.COUNT
2826 LOOP
2827 IF (PO_LOG.d_stmt) THEN
2828 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2829 PO_LOG.stmt(d_module, d_position, 'new site id', l_vendor_site_id_tbl(i));
2830 END IF;
2831
2832 x_vendor_site_id_tbl(l_index_tbl(i)) := l_vendor_site_id_tbl(i);
2833 END LOOP;
2834
2835 d_position := 30;
2836
2837 -- default other attributes from site definition
2838 -- to do (add char6 to gt table?)
2839 FORALL i IN 1..p_index_tbl.COUNT
2840 INSERT INTO po_session_gt(
2841 key, num1, char1, char2, char3, num2, num3, char4, num4, char5, char6)
2842 SELECT p_key,
2843 p_index_tbl(i),
2844 fob_lookup_code,
2845 ship_via_lookup_code,
2846 freight_terms_lookup_code,
2847 ship_to_location_id,
2848 bill_to_location_id,
2849 invoice_currency_code,
2850 terms_id,
2851 shipping_control,
2852 pay_on_code
2853 FROM po_vendor_sites_all
2854 WHERE vendor_site_id = x_vendor_site_id_tbl(i);
2855
2856 d_position := 40;
2857
2858 DELETE FROM po_session_gt
2859 WHERE key = p_key
2860 RETURNING num1, char1, char2, char3, num2, num3, char4, num4, char5, char6
2861 BULK COLLECT INTO
2862 l_index_tbl,
2863 l_fob_tbl,
2864 l_freight_carrier_tbl,
2865 l_freight_term_tbl,
2866 l_ship_to_loc_id_tbl,
2867 l_bill_to_loc_id_tbl,
2868 l_currency_code_tbl,
2869 l_terms_id_tbl,
2870 l_shipping_control_tbl,
2871 l_pay_on_code_tbl;
2872
2873 d_position := 50;
2874
2875 FOR i IN 1..l_index_tbl.COUNT
2876 LOOP
2877 l_index := l_index_tbl(i);
2878
2879 IF (PO_LOG.d_stmt) THEN
2880 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2881 PO_LOG.stmt(d_module, d_position, 'new fob', l_fob_tbl(i));
2882 PO_LOG.stmt(d_module, d_position, 'new freight carrier', l_freight_carrier_tbl(i));
2883 PO_LOG.stmt(d_module, d_position, 'new freight term', l_freight_term_tbl(i));
2884 PO_LOG.stmt(d_module, d_position, 'new ship_to loc id', l_ship_to_loc_id_tbl(i));
2885 PO_LOG.stmt(d_module, d_position, 'new bill_to loc id', l_bill_to_loc_id_tbl(i));
2886 PO_LOG.stmt(d_module, d_position, 'new currency', l_currency_code_tbl(i));
2887 PO_LOG.stmt(d_module, d_position, 'new terms id', l_terms_id_tbl(i));
2888 PO_LOG.stmt(d_module, d_position, 'new shipping control', l_shipping_control_tbl(i));
2889 PO_LOG.stmt(d_module, d_position, 'new pay on code', l_pay_on_code_tbl(i));
2890 END IF;
2891
2892 x_fob_tbl(l_index) := l_fob_tbl(i);
2893 x_freight_carrier_tbl(l_index) := l_freight_carrier_tbl(i);
2894 x_freight_term_tbl(l_index) := l_freight_term_tbl(i);
2895 x_ship_to_loc_id_tbl(l_index) := l_ship_to_loc_id_tbl(i);
2896 x_bill_to_loc_id_tbl(l_index) := l_bill_to_loc_id_tbl(i);
2897 x_invoice_currency_code_tbl(l_index) := l_currency_code_tbl(i);
2898 x_terms_id_tbl(l_index) := l_terms_id_tbl(i);
2899 x_shipping_control_tbl(l_index) := l_shipping_control_tbl(i);
2900 x_pay_on_code_tbl(l_index) := l_pay_on_code_tbl(i);
2901 END LOOP;
2902
2903 IF (PO_LOG.d_proc) THEN
2904 PO_LOG.proc_end (d_module);
2905 END IF;
2906
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 PO_MESSAGE_S.add_exc_msg
2910 (
2911 p_pkg_name => d_pkg_name,
2912 p_procedure_name => d_api_name || '.' || d_position
2913 );
2914 RAISE;
2915 END default_info_from_vendor_site;
2916
2917 -------------------------------------------------------------------------
2918 --Start of Comments
2919 --Name: default_vendor_contact
2920 --Pre-reqs: None
2921 --Modifies:
2922 --Locks:
2923 -- None
2924 --Function:
2925 -- handle the logic to default vendor contact from vendor site
2926 -- in a batch mode; Vendor contact can be defaulted only when
2927 -- there is exactly one contact defined for the specific site
2928 --Parameters:
2929 --IN:
2930 -- p_key
2931 -- identifier in the temp table on the derived result
2932 -- p_index_tbl
2933 -- indexes of the records
2934 -- p_vendor_site_id_tbl
2935 -- values of vendor site id in current batch of records
2936 --IN OUT:
2937 -- x_vendor_contact_id_tbl
2938 -- values of vendor contact id in current batch of records;
2939 -- defaulted results will be saved here
2940 --OUT: None
2941 --Returns:
2942 --Notes:
2943 --Testing:
2944 --End of Comments
2945 ------------------------------------------------------------------------
2946 PROCEDURE default_vendor_contact
2947 (
2948 p_key IN po_session_gt.key%TYPE,
2949 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2950 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
2951 x_vendor_contact_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2952 ) IS
2953
2954 d_api_name CONSTANT VARCHAR2(30) := 'default_vendor_contact';
2955 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2956 d_position NUMBER;
2957
2958 -- variables to hold defaulted results
2959 l_index_tbl PO_TBL_NUMBER;
2960 l_result_tbl PO_TBL_NUMBER;
2961
2962 BEGIN
2963 d_position := 0;
2964
2965 IF (PO_LOG.d_proc) THEN
2966 PO_LOG.proc_begin(d_module, 'site ids', p_vendor_site_id_tbl);
2967 PO_LOG.proc_begin(d_module, 'contact ids', x_vendor_contact_id_tbl);
2968 END IF;
2969
2970 -- select contact id from vendor_site table if there is only
2971 -- one contact defined on that site
2972 FORALL i IN 1..p_index_tbl.COUNT
2973 INSERT INTO po_session_gt(key, num1, num2, num3)
2974 SELECT p_key,
2975 p_index_tbl(i),
2976 max(vendor_contact_id),
2977 vendor_site_id
2978 FROM po_vendor_contacts
2979 WHERE p_vendor_site_id_tbl(i) IS NOT NULL
2980 AND x_vendor_contact_id_tbl(i) IS NULL
2981 AND vendor_site_id = p_vendor_site_id_tbl(i)
2982 AND TRUNC(sysdate) < NVL(inactive_date, TRUNC(sysdate + 1))
2983 GROUP BY vendor_site_id
2984 HAVING count(vendor_contact_id) = 1;
2985
2986 d_position := 10;
2987
2988 DELETE FROM po_session_gt
2989 WHERE key = p_key
2990 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2991
2992 d_position := 20;
2993
2994 FOR i IN 1..l_index_tbl.COUNT
2995 LOOP
2996 IF (PO_LOG.d_stmt) THEN
2997 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2998 PO_LOG.stmt(d_module, d_position, 'new contact id', l_result_tbl(i));
2999 END IF;
3000
3001 x_vendor_contact_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3002 END LOOP;
3003
3004 IF (PO_LOG.d_proc) THEN
3005 PO_LOG.proc_end (d_module);
3006 END IF;
3007
3008 EXCEPTION
3009 WHEN OTHERS THEN
3010 PO_MESSAGE_S.add_exc_msg
3011 (
3012 p_pkg_name => d_pkg_name,
3013 p_procedure_name => d_api_name || '.' || d_position
3014 );
3015 RAISE;
3016 END default_vendor_contact;
3017
3018 -------------------------------------------------------------------------
3019 --Start of Comments
3020 --Name: default_dist_attributes
3021 --Pre-reqs: None
3022 --Modifies:
3023 --Locks:
3024 -- None
3025 --Function:
3026 -- handle the logic to default distribution attributes for Blanket
3027 -- if encumbrance is required for the document;
3028 -- that is, x_headers.encumbrance_required_flag = 'Y'
3029 --Parameters:
3030 --IN: None
3031 --IN OUT:
3032 -- x_headers
3033 -- variable to hold all the header attribute values in one batch;
3034 -- derivation source and result are both placed inside the variable
3035 --OUT: None
3036 --Returns:
3037 --Notes:
3038 --Testing:
3039 --End of Comments
3040 ------------------------------------------------------------------------
3041 PROCEDURE default_dist_attributes
3042 (
3043 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
3044 ) IS
3045
3046 d_api_name CONSTANT VARCHAR2(30) := 'default_dist_attributes';
3047 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3048 d_position NUMBER;
3049
3050 BEGIN
3051 d_position := 0;
3052
3053 IF (PO_LOG.d_proc) THEN
3054 PO_LOG.proc_begin(d_module);
3055 END IF;
3056
3057
3058 FOR i IN 1..x_headers.rec_count
3059 LOOP
3060 d_position := 10;
3061
3062 IF (x_headers.encumbrance_required_flag_tbl(i) = 'Y') THEN
3063 IF (PO_LOG.d_stmt) THEN
3064 PO_LOG.stmt(d_module, d_position, 'distribution row created for encumbrance');
3065 PO_LOG.stmt(d_module, d_position, 'index', i);
3066 END IF;
3067
3068 -- default po_distribution_id
3069 x_headers.po_dist_id_tbl(i) := PO_PDOI_MAINPROC_UTL_PVT.get_next_dist_id;
3070
3071 IF (PO_LOG.d_stmt) THEN
3072 PO_LOG.stmt(d_module, d_position, 'new dist id', x_headers.po_dist_id_tbl(i));
3073 END IF;
3074
3075 -- default gl_encumbered_date and gl_encumbered_period
3076 IF (x_headers.gl_encumbered_date_tbl(i) IS NULL) THEN
3077 x_headers.gl_encumbered_date_tbl(i) := sysdate;
3078 END IF;
3079
3080 d_position := 20;
3081
3082 PO_PERIODS_SV.get_period_name
3083 (
3084 x_sob_id => PO_PDOI_PARAMS.g_sys.sob_id,
3085 x_gl_date => x_headers.gl_encumbered_date_tbl(i),
3086 x_gl_period => x_headers.gl_encumbered_period_tbl(i)
3087 );
3088
3089 d_position := 30;
3090
3091 -- default budget account id
3092 IF (x_headers.budget_account_id_tbl(i) IS NULL) THEN
3093 PO_PDOI_DIST_PROCESS_PVT.derive_account_id
3094 ( p_account_number => x_headers.budget_account_tbl(i),
3095 p_chart_of_accounts_id => PO_PDOI_PARAMS.g_sys.coa_id,
3096 p_account_segment1 => x_headers.budget_account_segment1_tbl(i),
3097 p_account_segment2 => x_headers.budget_account_segment2_tbl(i),
3098 p_account_segment3 => x_headers.budget_account_segment3_tbl(i),
3099 p_account_segment4 => x_headers.budget_account_segment4_tbl(i),
3100 p_account_segment5 => x_headers.budget_account_segment5_tbl(i),
3101 p_account_segment6 => x_headers.budget_account_segment6_tbl(i),
3102 p_account_segment7 => x_headers.budget_account_segment7_tbl(i),
3103 p_account_segment8 => x_headers.budget_account_segment8_tbl(i),
3104 p_account_segment9 => x_headers.budget_account_segment9_tbl(i),
3105 p_account_segment10 => x_headers.budget_account_segment10_tbl(i),
3106 p_account_segment11 => x_headers.budget_account_segment11_tbl(i),
3107 p_account_segment12 => x_headers.budget_account_segment12_tbl(i),
3108 p_account_segment13 => x_headers.budget_account_segment13_tbl(i),
3109 p_account_segment14 => x_headers.budget_account_segment14_tbl(i),
3110 p_account_segment15 => x_headers.budget_account_segment15_tbl(i),
3111 p_account_segment16 => x_headers.budget_account_segment16_tbl(i),
3112 p_account_segment17 => x_headers.budget_account_segment17_tbl(i),
3113 p_account_segment18 => x_headers.budget_account_segment18_tbl(i),
3114 p_account_segment19 => x_headers.budget_account_segment19_tbl(i),
3115 p_account_segment20 => x_headers.budget_account_segment20_tbl(i),
3116 p_account_segment21 => x_headers.budget_account_segment21_tbl(i),
3117 p_account_segment22 => x_headers.budget_account_segment22_tbl(i),
3118 p_account_segment23 => x_headers.budget_account_segment23_tbl(i),
3119 p_account_segment24 => x_headers.budget_account_segment24_tbl(i),
3120 p_account_segment25 => x_headers.budget_account_segment25_tbl(i),
3121 p_account_segment26 => x_headers.budget_account_segment26_tbl(i),
3122 p_account_segment27 => x_headers.budget_account_segment27_tbl(i),
3123 p_account_segment28 => x_headers.budget_account_segment28_tbl(i),
3124 p_account_segment29 => x_headers.budget_account_segment29_tbl(i),
3125 p_account_segment30 => x_headers.budget_account_segment30_tbl(i),
3126 x_account_id => x_headers.budget_account_id_tbl(i)
3127 );
3128
3129 IF (PO_LOG.d_stmt) THEN
3130 PO_LOG.stmt(d_module, d_position, 'default budget account id',
3131 x_headers.budget_account_id_tbl(i));
3132 END IF;
3133 END IF;
3134 END IF;
3135 END LOOP;
3136
3137 IF (PO_LOG.d_proc) THEN
3138 PO_LOG.proc_end (d_module);
3139 END IF;
3140
3141 EXCEPTION
3142 WHEN OTHERS THEN
3143 PO_MESSAGE_S.add_exc_msg
3144 (
3145 p_pkg_name => d_pkg_name,
3146 p_procedure_name => d_api_name || '.' || d_position
3147 );
3148 RAISE;
3149 END default_dist_attributes;
3150
3151 -----------------------------------------------------------------------
3152 --Start of Comments
3153 --Name: populate_error_flag
3154 --Function:
3155 -- corresponding value in error_flag_tbl will be set with value FND_API.G_FALSE.
3156 --Parameters:
3157 --IN:
3158 --x_results
3159 -- The validation results that contains the errored line information.
3160 --IN OUT:
3161 --x_headers
3162 -- The record contains the values to be validated.
3163 -- If there is error(s) on any attribute of the price differential row,
3164 -- corresponding value in error_flag_tbl will be set with value
3165 -- FND_API.g_TRUE.
3166 --OUT:
3167 --End of Comments
3168 ------------------------------------------------------------------------
3169 PROCEDURE populate_error_flag
3170 (
3171 x_results IN po_validation_results_type,
3172 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
3173 ) IS
3174
3175 d_api_name CONSTANT VARCHAR2(30) := 'populate_error_flag';
3176 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3177 d_position NUMBER;
3178
3179 l_index_tbl DBMS_SQL.number_table;
3180
3181 BEGIN
3182 d_position := 0;
3183
3184 IF (PO_LOG.d_proc) THEN
3185 PO_LOG.proc_begin(d_module);
3186 END IF;
3187
3188 FOR i IN 1 .. x_headers.rec_count LOOP
3189 l_index_tbl(x_headers.intf_header_id_tbl(i)) := i;
3190 END LOOP;
3191
3192 d_position := 10;
3193
3194 FOR i IN 1 .. x_results.entity_id.COUNT LOOP
3195 IF x_results.result_type(i) = po_validations.c_result_type_failure THEN
3196 x_headers.error_flag_tbl(l_index_tbl(x_results.entity_id(i))) := FND_API.g_TRUE;
3197 END IF;
3198 END LOOP;
3199
3200 IF (PO_LOG.d_proc) THEN
3201 PO_LOG.proc_end (d_module);
3202 END IF;
3203
3204 EXCEPTION
3205 WHEN OTHERS THEN
3206 PO_MESSAGE_S.add_exc_msg
3207 (
3208 p_pkg_name => d_pkg_name,
3209 p_procedure_name => d_api_name || '.' || d_position
3210 );
3211 RAISE;
3212 END populate_error_flag;
3213
3214 END PO_PDOI_HEADER_PROCESS_PVT;