[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.34.12020000.3 2013/02/10 15:06:17 vegajula 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 --PDOI CLM Integration Starts
107 PROCEDURE default_clm_standard_format
108 (
109 p_key IN po_session_gt.key%TYPE,
110 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
111 p_style_id_tbl IN PO_TBL_NUMBER,
112 p_clm_award_type IN PO_TBL_VARCHAR30,
113 x_clm_standard_form_tbl IN OUT NOCOPY PO_TBL_VARCHAR2000,
114 x_clm_document_format_tbl IN OUT NOCOPY PO_TBL_VARCHAR2000
115 );
116
117 PROCEDURE derive_clm_award_type
118 (
119 p_key IN po_session_gt.key%TYPE,
120 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
121 p_clm_award_type_disp_tbl IN PO_TBL_VARCHAR240,
122 x_clm_award_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
123 );
124
125 PROCEDURE derive_clm_award_admin
126 (
127 p_key IN po_session_gt.key%TYPE,
128 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
129 p_clm_award_admin_disp_tbl IN PO_TBL_VARCHAR240,
130 x_clm_award_administrator_tbl IN OUT NOCOPY PO_TBL_VARCHAR2000
131 );
132
133 PROCEDURE derive_clm_contract_officer
134 (
135 p_key IN po_session_gt.key%TYPE,
136 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
137 p_clm_contract_oficer_disp_tbl IN PO_TBL_VARCHAR240,
138 x_clm_contract_officer_tbl IN OUT NOCOPY PO_TBL_NUMBER
139 );
140
141 PROCEDURE derive_vendor_site_id_from_idv
142 (
143 p_key IN po_session_gt.key%TYPE,
144 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
145 p_clm_source_document_id_tbl IN PO_TBL_NUMBER,
146 p_doc_type_tbl IN PO_TBL_VARCHAR30,
147 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
148 );
149
150 --PDOI CLM Integration Ends
151 --------------------------------------------------------------------------
152 ---------------------- PUBLIC PROCEDURES ---------------------------------
153 --------------------------------------------------------------------------
154
155 -----------------------------------------------------------------------
156 --Start of Comments
157 --Name: open_headers
158 --Function:
159 -- Open cursor for query.
160 -- This query retrieves the header attributes for processing
161 --Parameters:
162 --IN:
163 -- p_max_intf_header_id
164 -- maximal interface_header_id processed so far
165 -- The query will only retrieve the header records which have
166 -- not been processed
167 --IN:
168 -- p_max_intf_header_id
169 -- maximal interface_header_id processed in previous batches
170 --IN OUT:
171 -- x_headers_csr
172 -- cursor variable to hold pointer to current processing row in the result
173 -- set returned by the query
174 --OUT:
175 --End of Comments
176 ------------------------------------------------------------------------
177 PROCEDURE open_headers
178 (
179 p_max_intf_header_id IN NUMBER,
180 x_headers_csr OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
181 ) IS
182 d_api_name CONSTANT VARCHAR2(30) := 'open_headers';
183 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
184 d_position NUMBER;
185
186 BEGIN
187 d_position := 0;
188
189 IF (PO_LOG.d_proc) THEN
190 PO_LOG.proc_begin(d_module, 'p_max_intf_header_id', p_max_intf_header_id);
191 END IF;
192
193 OPEN x_headers_csr FOR
194 SELECT interface_header_id,
195 draft_id,
196 po_header_id,
197 action,
198 document_num,
199 document_type_code,
200 document_subtype,
201 rate_type,
202 rate_type_code,
203 rate_date,
204 rate,
205 agent_id,
206 agent_name,
207 ship_to_location_id,
208 ship_to_location,
209 bill_to_location_id,
210 bill_to_location,
211 payment_terms,
212 terms_id,
213 vendor_name,
214 vendor_num,
215 vendor_id,
216 vendor_site_code,
217 vendor_site_id,
218 vendor_contact,
219 vendor_contact_id,
220 from_rfq_num,
221 from_header_id,
222 fob,
223 freight_carrier,
224 freight_terms,
225 pay_on_code,
226 shipping_control,
227 currency_code,
228 quote_warning_delay,
229 approval_required_flag,
230 reply_date,
231 approval_status,
232 approved_date,
233 from_type_lookup_code,
234 revision_num,
235 confirming_order_flag,
236 acceptance_required_flag,
237 min_release_amount,
238 closed_code,
239 print_count,
240 frozen_flag,
241 encumbrance_required_flag,
242 vendor_doc_num,
243 org_id,
244 acceptance_due_date,
245 amount_to_encumber,
246 effective_date,
247 expiration_date,
248 po_release_id,
249 release_num,
250 release_date,
251 revised_date,
252 printed_date,
253 closed_date,
254 amount_agreed,
255 amount_limit, -- bug5352625
256 firm_flag,
257 gl_encumbered_date,
258 gl_encumbered_period_name,
259 budget_account_id,
260 budget_account,
261 budget_account_segment1,
262 budget_account_segment2,
263 budget_account_segment3,
264 budget_account_segment4,
265 budget_account_segment5,
266 budget_account_segment6,
267 budget_account_segment7,
268 budget_account_segment8,
269 budget_account_segment9,
270 budget_account_segment10,
271 budget_account_segment11,
272 budget_account_segment12,
273 budget_account_segment13,
274 budget_account_segment14,
275 budget_account_segment15,
276 budget_account_segment16,
277 budget_account_segment17,
278 budget_account_segment18,
279 budget_account_segment19,
280 budget_account_segment20,
281 budget_account_segment21,
282 budget_account_segment22,
283 budget_account_segment23,
284 budget_account_segment24,
285 budget_account_segment25,
286 budget_account_segment26,
287 budget_account_segment27,
288 budget_account_segment28,
289 budget_account_segment29,
290 budget_account_segment30,
291 created_language,
292 style_id,
293 style_display_name,
294 global_agreement_flag,
295
296 -- standard who columns
297 last_update_date,
298 last_updated_by,
299 last_update_login,
300 creation_date,
301 created_by,
302 request_id,
303 program_application_id,
304 program_id,
305 program_update_date,
306 FND_API.g_FALSE, -- initial value for error_flag
307
308 -- txn table columns
309 NULL, -- status_lookup_code
310 NULL, -- cancel_flag
311 NULL, -- vendor_order_num
312 NULL, -- quote_vendor_quote_num
313 NULL, -- doc_creation_method
314 NULL, -- quotation_class_code
315 NULL, -- approved_flag
316 NULL, -- tax_attribute_update_code_tbl
317
318 -- blanket dist columns
319 NULL, -- po_dist_id -- bug5252250
320 --PDOI CLM Integration
321 clm_standard_form,
322 clm_document_format,
323 clm_award_type,
324 clm_source_document_id,
325 clm_external_idv,
326 clm_vendor_offer_number,
327 clm_award_administrator,
328 comments,
329 clm_no_signed_copies_to_return,
330 umbrella_program_id,
331 note_to_vendor,
332 note_to_receiver,
333 clm_effective_date,
334 clm_min_guarantee_award_amt,
335 clm_min_guar_award_amt_percent,
336 clm_min_order_amount,
337 clm_max_order_amount,
338 clm_amount_released,
339 fon_ref_id,
340 clm_award_type_disp,
341 clm_award_administrator_disp,
342 clm_contract_officer_disp,
343 clm_contract_officer,
344 clm_source_document_disp,
345 null --draft_type
346
347 FROM po_headers_interface
348 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
349 AND processing_round_num = PO_PDOI_PARAMS.g_current_round_num
350 AND interface_header_id > p_max_intf_header_id
351 AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
352 PO_PDOI_CONSTANTS.g_ACTION_REPLACE)
353 ORDER by interface_header_id;
354
355 IF (PO_LOG.d_proc) THEN
356 PO_LOG.proc_end (d_module);
357 END IF;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 PO_MESSAGE_S.add_exc_msg
362 (
363 p_pkg_name => d_pkg_name,
364 p_procedure_name => d_api_name || '.' || d_position
365 );
366 RAISE;
367 END open_headers;
368
369 -- CLM Partial Funding Changes
370 -----------------------------------------------------------------------
371 --Start of Comments
372 --Name: open_specific_headers
373 --Function:
374 -- Open cursor for query.
375 -- This query retrieves the header attributes for processing,
376 -- for a specific PO Interface header id passed.
377 --Parameters:
378 --IN:
379 -- p_intf_po_header_id
380 -- The specific po_header_id for which record need to be retrieved.
381 --IN OUT:
382 -- x_headers_csr
383 -- cursor variable to hold pointer to current processing row in the result
384 -- set returned by the query
385 --OUT:
386 --End of Comments
387 ------------------------------------------------------------------------
388 PROCEDURE open_specific_headers
389 (
390 p_intf_po_header_id IN NUMBER,
391 x_headers_csr OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type
392 ) IS
393 d_api_name CONSTANT VARCHAR2(30) := 'open_specific_headers';
394 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
395 d_position NUMBER;
396
397 BEGIN
398 d_position := 0;
399
400 IF (PO_LOG.d_proc) THEN
401 PO_LOG.proc_begin(d_module, 'p_intf_po_header_id', p_intf_po_header_id);
402 END IF;
403
404 OPEN x_headers_csr FOR
405 SELECT interface_header_id,
406 draft_id,
407 po_header_id,
408 action,
409 document_num,
410 document_type_code,
411 document_subtype,
412 rate_type,
413 rate_type_code,
414 rate_date,
415 rate,
416 agent_id,
417 agent_name,
418 ship_to_location_id,
419 ship_to_location,
420 bill_to_location_id,
421 bill_to_location,
422 payment_terms,
423 terms_id,
424 vendor_name,
425 vendor_num,
426 vendor_id,
427 vendor_site_code,
428 vendor_site_id,
429 vendor_contact,
430 vendor_contact_id,
431 from_rfq_num,
432 from_header_id,
433 fob,
434 freight_carrier,
435 freight_terms,
436 pay_on_code,
437 shipping_control,
438 currency_code,
439 quote_warning_delay,
440 approval_required_flag,
441 reply_date,
442 approval_status,
443 approved_date,
444 from_type_lookup_code,
445 revision_num,
446 confirming_order_flag,
447 acceptance_required_flag,
448 min_release_amount,
449 closed_code,
450 print_count,
451 frozen_flag,
452 encumbrance_required_flag,
453 vendor_doc_num,
454 org_id,
455 acceptance_due_date,
456 amount_to_encumber,
457 effective_date,
458 expiration_date,
459 po_release_id,
460 release_num,
461 release_date,
462 revised_date,
463 printed_date,
464 closed_date,
465 amount_agreed,
466 amount_limit, -- bug5352625
467 firm_flag,
468 gl_encumbered_date,
469 gl_encumbered_period_name,
470 budget_account_id,
471 budget_account,
472 budget_account_segment1,
473 budget_account_segment2,
474 budget_account_segment3,
475 budget_account_segment4,
476 budget_account_segment5,
477 budget_account_segment6,
478 budget_account_segment7,
479 budget_account_segment8,
480 budget_account_segment9,
481 budget_account_segment10,
482 budget_account_segment11,
483 budget_account_segment12,
484 budget_account_segment13,
485 budget_account_segment14,
486 budget_account_segment15,
487 budget_account_segment16,
488 budget_account_segment17,
489 budget_account_segment18,
490 budget_account_segment19,
491 budget_account_segment20,
492 budget_account_segment21,
493 budget_account_segment22,
494 budget_account_segment23,
495 budget_account_segment24,
496 budget_account_segment25,
497 budget_account_segment26,
498 budget_account_segment27,
499 budget_account_segment28,
500 budget_account_segment29,
501 budget_account_segment30,
502 created_language,
503 style_id,
504 style_display_name,
505 global_agreement_flag,
506
507 -- standard who columns
508 last_update_date,
509 last_updated_by,
510 last_update_login,
511 creation_date,
512 created_by,
513 request_id,
514 program_application_id,
515 program_id,
516 program_update_date,
517 FND_API.g_FALSE, -- initial value for error_flag
518
519 -- txn table columns
520 NULL, -- status_lookup_code
521 NULL, -- cancel_flag
522 NULL, -- vendor_order_num
523 NULL, -- quote_vendor_quote_num
524 NULL, -- doc_creation_method
525 NULL, -- quotation_class_code
526 NULL, -- approved_flag
527 NULL, -- tax_attribute_update_code_tbl
528
529 -- blanket dist columns
530 NULL, -- po_dist_id -- bug5252250
531 --PDOI CLM Integration
532 clm_standard_form,
533 clm_document_format,
534 clm_award_type,
535 clm_source_document_id,
536 clm_external_idv,
537 clm_vendor_offer_number,
538 clm_award_administrator,
539 comments,
540 clm_no_signed_copies_to_return,
541 Decode(clm_source_document_id,
542 NULL, NULL,
543 umbrella_program_id
544 ),
545 note_to_vendor,
546 note_to_receiver,
547 clm_effective_date,
548 clm_min_guarantee_award_amt,
549 clm_min_guar_award_amt_percent,
550 clm_min_order_amount,
551 clm_max_order_amount,
552 clm_amount_released,
553 fon_ref_id,
554 clm_award_type_disp,
555 clm_award_administrator_disp,
556 clm_contract_officer_disp,
557 clm_contract_officer,
558 clm_source_document_disp,
559 null --draft_type
560
561 FROM po_headers_interface
562 WHERE processing_id = PO_PDOI_PARAMS.g_processing_id
563 AND processing_round_num = PO_PDOI_PARAMS.g_current_round_num
564 AND po_header_id = p_intf_po_header_id
565 AND action IN (PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL,
566 PO_PDOI_CONSTANTS.g_ACTION_REPLACE)
567 ORDER by interface_header_id;
568
569 IF (PO_LOG.d_proc) THEN
570 PO_LOG.proc_end (d_module);
571 END IF;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 PO_MESSAGE_S.add_exc_msg
576 (
577 p_pkg_name => d_pkg_name,
578 p_procedure_name => d_api_name || '.' || d_position
579 );
580 RAISE;
581 END open_specific_headers;
582
583 -----------------------------------------------------------------------
584 --Start of Comments
585 --Name: fetch_headers
586 --Function:
587 -- fetch results in batch
588 --Parameters:
589 --IN:
590 --IN OUT:
591 --x_headers_csr
592 -- cursor variable that hold pointers to currently processing row
593 --x_headers
594 -- record variable to hold header info within a batch
595 --OUT:
596 --End of Comments
597 ------------------------------------------------------------------------
598 PROCEDURE fetch_headers
599 (
600 x_headers_csr IN OUT NOCOPY PO_PDOI_TYPES.intf_cursor_type,
601 x_headers OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
602 ) IS
603
604 d_api_name CONSTANT VARCHAR2(30) := 'fetch_headers';
605 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
606 d_position NUMBER;
607
608 BEGIN
609 d_position := 0;
610
611 IF (PO_LOG.d_proc) THEN
612 PO_LOG.proc_begin(d_module);
613 END IF;
614
615 FETCH x_headers_csr BULK COLLECT INTO
616 x_headers.intf_header_id_tbl,
617 x_headers.draft_id_tbl,
618 x_headers.po_header_id_tbl,
619 x_headers.action_tbl,
620 x_headers.document_num_tbl,
621 x_headers.doc_type_tbl,
622 x_headers.doc_subtype_tbl,
623 x_headers.rate_type_tbl,
624 x_headers.rate_type_code_tbl,
625 x_headers.rate_date_tbl,
626 x_headers.rate_tbl,
627 x_headers.agent_id_tbl,
628 x_headers.agent_name_tbl,
629 x_headers.ship_to_loc_id_tbl,
630 x_headers.ship_to_loc_tbl,
631 x_headers.bill_to_loc_id_tbl,
632 x_headers.bill_to_loc_tbl,
633 x_headers.payment_terms_tbl,
634 x_headers.terms_id_tbl,
635 x_headers.vendor_name_tbl,
636 x_headers.vendor_num_tbl,
637 x_headers.vendor_id_tbl,
638 x_headers.vendor_site_code_tbl,
639 x_headers.vendor_site_id_tbl,
640 x_headers.vendor_contact_tbl,
641 x_headers.vendor_contact_id_tbl,
642 x_headers.from_rfq_num_tbl,
643 x_headers.from_header_id_tbl,
644 x_headers.fob_tbl,
645 x_headers.freight_carrier_tbl,
646 x_headers.freight_term_tbl,
647 x_headers.pay_on_code_tbl,
648 x_headers.shipping_control_tbl,
649 x_headers.currency_code_tbl,
650 x_headers.quote_warning_delay_tbl,
651 x_headers.approval_required_flag_tbl,
652 x_headers.reply_date_tbl,
653 x_headers.approval_status_tbl,
654 x_headers.approved_date_tbl,
655 x_headers.from_type_lookup_code_tbl,
656 x_headers.revision_num_tbl,
657 x_headers.confirming_order_flag_tbl,
658 x_headers.acceptance_required_flag_tbl,
659 x_headers.min_release_amount_tbl,
660 x_headers.closed_code_tbl,
661 x_headers.print_count_tbl,
662 x_headers.frozen_flag_tbl,
663 x_headers.encumbrance_required_flag_tbl,
664 x_headers.vendor_doc_num_tbl,
665 x_headers.org_id_tbl,
666 x_headers.acceptance_due_date_tbl,
667 x_headers.amount_to_encumber_tbl,
668 x_headers.effective_date_tbl,
669 x_headers.expiration_date_tbl,
670 x_headers.po_release_id_tbl,
671 x_headers.release_num_tbl,
672 x_headers.release_date_tbl,
673 x_headers.revised_date_tbl,
674 x_headers.printed_date_tbl,
675 x_headers.closed_date_tbl,
676 x_headers.amount_agreed_tbl,
677 x_headers.amount_limit_tbl, -- bug5352625
678 x_headers.firm_flag_tbl,
679 x_headers.gl_encumbered_date_tbl,
680 x_headers.gl_encumbered_period_tbl,
681 x_headers.budget_account_id_tbl,
682 x_headers.budget_account_tbl,
683 x_headers.budget_account_segment1_tbl,
684 x_headers.budget_account_segment2_tbl,
685 x_headers.budget_account_segment3_tbl,
686 x_headers.budget_account_segment4_tbl,
687 x_headers.budget_account_segment5_tbl,
688 x_headers.budget_account_segment6_tbl,
689 x_headers.budget_account_segment7_tbl,
690 x_headers.budget_account_segment8_tbl,
691 x_headers.budget_account_segment9_tbl,
692 x_headers.budget_account_segment10_tbl,
693 x_headers.budget_account_segment11_tbl,
694 x_headers.budget_account_segment12_tbl,
695 x_headers.budget_account_segment13_tbl,
696 x_headers.budget_account_segment14_tbl,
697 x_headers.budget_account_segment15_tbl,
698 x_headers.budget_account_segment16_tbl,
699 x_headers.budget_account_segment17_tbl,
700 x_headers.budget_account_segment18_tbl,
701 x_headers.budget_account_segment19_tbl,
702 x_headers.budget_account_segment20_tbl,
703 x_headers.budget_account_segment21_tbl,
704 x_headers.budget_account_segment22_tbl,
705 x_headers.budget_account_segment23_tbl,
706 x_headers.budget_account_segment24_tbl,
707 x_headers.budget_account_segment25_tbl,
708 x_headers.budget_account_segment26_tbl,
709 x_headers.budget_account_segment27_tbl,
710 x_headers.budget_account_segment28_tbl,
711 x_headers.budget_account_segment29_tbl,
712 x_headers.budget_account_segment30_tbl,
713 x_headers.created_language_tbl,
714 x_headers.style_id_tbl,
715 x_headers.style_display_name_tbl,
716 x_headers.global_agreement_flag_tbl,
717
718 -- standard who columns
719 x_headers.last_update_date_tbl,
720 x_headers.last_updated_by_tbl,
721 x_headers.last_update_login_tbl,
722 x_headers.creation_date_tbl,
723 x_headers.created_by_tbl,
724 x_headers.request_id_tbl,
725 x_headers.program_application_id_tbl,
726 x_headers.program_id_tbl,
727 x_headers.program_update_date_tbl,
728
729 x_headers.error_flag_tbl, -- set initial value on error_flag
730
731 -- tan table columns
732 x_headers.status_lookup_code_tbl,
733 x_headers.cancel_flag_tbl,
734 x_headers.vendor_order_num_tbl,
735 x_headers.quote_vendor_quote_num_tbl,
736 x_headers.doc_creation_method_tbl,
737 x_headers.quotation_class_code_tbl,
738 x_headers.approved_flag_tbl,
739 x_headers.tax_attribute_update_code_tbl,
740
741 -- blanket dist columns
742 x_headers.po_dist_id_tbl, -- bug5252250
743 --PDOI CLM Integration
744 x_headers.clm_standard_form_tbl,
745 x_headers.clm_document_format_tbl,
746 x_headers.clm_award_type_tbl,
747 x_headers.clm_source_document_id_tbl,
748 x_headers.clm_external_idv_tbl,
749 x_headers.clm_vendor_offer_number_tbl,
750 x_headers.clm_award_administrator_tbl,
751 x_headers.comments_tbl,
752 x_headers.no_signed_copies_to_return_tbl,
753 x_headers.umbrella_program_id_tbl,
754 x_headers.note_to_vendor_tbl,
755 x_headers.note_to_receiver_tbl,
756 x_headers.clm_effective_date_tbl,
757 x_headers.clm_min_guar_award_amt_tbl,
758 x_headers.clm_min_guar_award_amt_per_tbl,
759 x_headers.clm_min_order_amount_tbl,
760 x_headers.clm_max_order_amount_tbl,
761 x_headers.clm_amount_released_tbl,
762 x_headers.fon_ref_id_tbl,
763 x_headers.clm_award_type_disp_tbl,
764 x_headers.clm_award_admin_disp_tbl,
765 x_headers.clm_contract_officer_disp_tbl,
766 x_headers.clm_contract_officer_tbl,
767 x_headers.clm_source_document_disp_tbl,
768 x_headers.draft_type_tbl
769
770
771 LIMIT PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE;
772
773 IF (PO_LOG.d_proc) THEN
774 PO_LOG.proc_end (d_module);
775 END IF;
776
777 EXCEPTION
778 WHEN OTHERS THEN
779 PO_MESSAGE_S.add_exc_msg
780 (
781 p_pkg_name => d_pkg_name,
782 p_procedure_name => d_api_name || '.' || d_position
783 );
784 RAISE;
785 END fetch_headers;
786 --------------------------------------------------------------------------
787 --Start of Comments
788 --Name: derive_headers
789 --Pre-reqs: None
790 --Modifies:
791 --Locks:
792 -- None
793 --Function:
794 -- perform derive logic on header records read in one batch;
795 -- derivation errors are handled all together after the
796 -- derivation logic
797 -- The derived attributes include:
798 -- rate_type, agent_id
799 -- ship_to_location_id, bill_to_location_id
800 -- terms_id, vendor_id
801 -- vendor_site_id, vendor_contact_id
802 --Parameters:
803 --IN: None
804 --IN OUT:
805 -- x_headers
806 -- variable to hold all the header attribute values in one batch;
807 -- derivation source and result are both placed inside the variable
808 --OUT: None
809 --Returns: None
810 --Notes:
811 --Testing:
812 --End of Comments
813 --------------------------------------------------------------------------
814 PROCEDURE derive_headers
815 (
816 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
817 ) IS
818
819 d_api_name CONSTANT VARCHAR2(30) := 'derive_headers';
820 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
821 d_position NUMBER;
822
823 -- key used when operating on temp table
824 l_key po_session_gt.key%TYPE;
825
826 -- table used to save the index of the each row
827 l_index_tbl DBMS_SQL.NUMBER_TABLE;
828
829 -- temp variable used in derivation error handling
830 l_column_name VARCHAR2(11);
831 BEGIN
832 d_position := 0;
833
834 IF (PO_LOG.d_proc) THEN
835 PO_LOG.proc_begin(d_module, 'header_count', x_headers.rec_count);
836 END IF;
837
838 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_DERIVE);
839
840 -- pick a new key which will be used in all derive logic
841 l_key := PO_CORE_S.get_session_gt_nextval;
842
843 IF (PO_LOG.d_stmt) THEN
844 PO_LOG.stmt(d_module, d_position, 'key', l_key);
845 END IF;
846
847 -- initialize table containing the row number
848 PO_PDOI_UTL.generate_ordered_num_list
849 (
850 p_size => x_headers.rec_count,
851 x_num_list => l_index_tbl
852 );
853
854 d_position := 10;
855
856 -- derive rate_type_code from rate_type
857 derive_rate_type_code
858 (
859 p_key => l_key,
860 p_index_tbl => l_index_tbl,
861 p_rate_type_tbl => x_headers.rate_type_tbl,
862 x_rate_type_code_tbl => x_headers.rate_type_code_tbl
863 );
864
865 d_position := 20;
866
867 -- derive agent_id from agent_name
868 derive_agent_id
869 (
870 p_key => l_key,
871 p_index_tbl => l_index_tbl,
872 p_agent_name_tbl => x_headers.agent_name_tbl,
873 x_agent_id_tbl => x_headers.agent_id_tbl
874 );
875
876 d_position := 30;
877
878 -- derive ship_to_location_id from ship_to_location
879 derive_location_id
880 (
881 p_key => l_key,
882 p_index_tbl => l_index_tbl,
883 p_location_type => 'SHIP_TO',
884 p_location_tbl => x_headers.ship_to_loc_tbl,
885 x_location_id_tbl => x_headers.ship_to_loc_id_tbl
886 );
887
888 d_position := 40;
889
890 -- derive bill_to_location_id from bill_to_location
891 derive_location_id
892 (
893 p_key => l_key,
894 p_index_tbl => l_index_tbl,
895 p_location_type => 'BILL_TO',
896 p_location_tbl => x_headers.bill_to_loc_tbl,
897 x_location_id_tbl => x_headers.bill_to_loc_id_tbl
898 );
899
900 d_position := 50;
901
902 -- derive terms_id from payment_terms
903 derive_terms_id
904 (
905 p_key => l_key,
906 p_index_tbl => l_index_tbl,
907 p_payment_terms_tbl => x_headers.payment_terms_tbl,
908 x_terms_id_tbl => x_headers.terms_id_tbl
909 );
910
911 d_position := 60;
912
913 -- derive vendor_id from vendor_name/vendor_num
914 derive_vendor_id
915 (
916 p_key => l_key,
917 p_index_tbl => l_index_tbl,
918 p_vendor_name_tbl => x_headers.vendor_name_tbl,
919 p_vendor_num_tbl => x_headers.vendor_num_tbl,
920 x_vendor_id_tbl => x_headers.vendor_id_tbl
921 );
922
923 d_position := 70;
924
925 -- derive vendor_site_id from vendor_site_code
926 derive_vendor_site_id
927 (
928 p_key => l_key,
929 p_index_tbl => l_index_tbl,
930 p_vendor_id_tbl => x_headers.vendor_id_tbl,
931 p_vendor_site_code_tbl => x_headers.vendor_site_code_tbl,
932 x_vendor_site_id_tbl => x_headers.vendor_site_id_tbl
933 );
934
935 d_position := 80;
936
937 -- derive vendor_contact_id from vendor_contact
938 derive_vendor_contact_id
939 (
940 p_key => l_key,
941 p_index_tbl => l_index_tbl,
942 p_vendor_site_id_tbl => x_headers.vendor_site_id_tbl,
943 p_vendor_contact_tbl => x_headers.vendor_contact_tbl,
944 x_vendor_contact_id_tbl => x_headers.vendor_contact_id_tbl
945 );
946
947 -- derive style_id from style_display_name
948 derive_style_id
949 (
950 p_key => l_key,
951 p_index_tbl => l_index_tbl,
952 p_style_display_name_tbl => x_headers.style_display_name_tbl,
953 x_style_id_tbl => x_headers.style_id_tbl
954 );
955
956 d_position := 90;
957
958 -- derive from_header_id from from_rfq_num for QUOTATION
959 IF (PO_PDOI_PARAMS.g_request.document_type =
960 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
961 IF (PO_LOG.d_stmt) THEN
962 PO_LOG.stmt(d_module, d_position, 'derive from header id');
963 END IF;
964
965 derive_from_header_id
966 (
967 p_key => l_key,
968 p_index_tbl => l_index_tbl,
969 p_from_rfq_num_tbl => x_headers.from_rfq_num_tbl,
970 x_from_header_id_tbl => x_headers.from_header_id_tbl
971 );
972 END IF;
973
974 --CLM PDOI Integration Starts
975 IF Nvl(PO_PDOI_PARAMS.g_request.clm_flag,'N') = 'Y'
976 THEN
977
978 derive_clm_award_type
979 (
980 p_key => l_key,
981 p_index_tbl => l_index_tbl,
982 p_clm_award_type_disp_tbl => x_headers.clm_award_type_disp_tbl,
983 x_clm_award_type_tbl => x_headers.clm_award_type_tbl
984 );
985
986
987 derive_clm_award_admin
988 (
989 p_key => l_key,
990 p_index_tbl => l_index_tbl,
991 p_clm_award_admin_disp_tbl => x_headers.clm_award_admin_disp_tbl,
992 x_clm_award_administrator_tbl => x_headers.clm_award_administrator_tbl
993 );
994
995 derive_clm_contract_officer
996 (
997 p_key => l_key,
998 p_index_tbl => l_index_tbl,
999 p_clm_contract_oficer_disp_tbl => x_headers.clm_contract_officer_disp_tbl,
1000 x_clm_contract_officer_tbl => x_headers.clm_contract_officer_tbl
1001 );
1002
1003 derive_clm_source_document_id
1004 (
1005 p_key => l_key,
1006 p_index_tbl => l_index_tbl,
1007 p_clm_source_document_disp_tbl => x_headers.clm_source_document_disp_tbl,
1008 x_clm_source_document_id_tbl => x_headers.clm_source_document_id_tbl
1009 );
1010
1011 derive_vendor_id_from_idv
1012 (
1013 p_key => l_key,
1014 p_index_tbl => l_index_tbl,
1015 p_clm_source_document_id_tbl => x_headers.clm_source_document_id_tbl,
1016 p_doc_type_tbl => x_headers.doc_type_tbl,
1017 x_vendor_id_tbl => x_headers.vendor_id_tbl
1018 );
1019
1020 derive_vendor_site_id_from_idv
1021 (
1022 p_key => l_key,
1023 p_index_tbl => l_index_tbl,
1024 p_clm_source_document_id_tbl => x_headers.clm_source_document_id_tbl,
1025 p_doc_type_tbl => x_headers.doc_type_tbl,
1026 x_vendor_site_id_tbl => x_headers.vendor_site_id_tbl
1027 );
1028 END IF;
1029 --CLM PDOI Integration Ends
1030
1031 d_position := 100;
1032
1033 IF (PO_LOG.d_stmt) THEN
1034 PO_LOG.stmt(d_module, d_position, 'start processing derivation errors');
1035 END IF;
1036
1037 -- handle derivation errors
1038 FOR i IN 1..x_headers.rec_count
1039 LOOP
1040 d_position := 110;
1041
1042 IF (PO_LOG.d_stmt) THEN
1043 PO_LOG.stmt(d_module, d_position, 'index', i);
1044 END IF;
1045
1046 IF (x_headers.rate_type_tbl(i) IS NOT NULL AND
1047 x_headers.rate_type_code_tbl(i) IS NULL) THEN
1048 IF (PO_LOG.d_stmt) THEN
1049 PO_LOG.stmt(d_module, d_position, 'rate type code derivation failed');
1050 PO_LOG.stmt(d_module, d_position, 'rate type', x_headers.rate_type_tbl(i));
1051 END IF;
1052
1053 PO_PDOI_ERR_UTL.add_fatal_error
1054 (
1055 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1056 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1057 p_table_name => 'PO_HEADERS_INTERFACE',
1058 p_column_name => 'RATE_TYPE_CODE',
1059 p_column_value => x_headers.rate_type_code_tbl(i),
1060 p_token1_name => 'COLUMN_NAME',
1061 p_token1_value => 'RATE_TYPE',
1062 p_token2_name => 'VALUE',
1063 p_token2_value => x_headers.rate_type_tbl(i)
1064 );
1065
1066 x_headers.rate_type_code_tbl(i) := NULL;
1067
1068 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1069 END IF;
1070
1071 IF (x_headers.agent_name_tbl(i) IS NOT NULL AND
1072 x_headers.agent_id_tbl(i) IS NULL) THEN
1073 IF (PO_LOG.d_stmt) THEN
1074 PO_LOG.stmt(d_module, d_position, 'agent id derivation failed');
1075 PO_LOG.stmt(d_module, d_position, 'agent name', x_headers.agent_name_tbl(i));
1076 END IF;
1077
1078 PO_PDOI_ERR_UTL.add_fatal_error
1079 (
1080 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1081 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1082 p_table_name => 'PO_HEADERS_INTERFACE',
1083 p_column_name => 'AGENT_ID',
1084 p_column_value => x_headers.agent_id_tbl(i),
1085 p_token1_name => 'COLUMN_NAME',
1086 p_token1_value => 'AGENT_NAME',
1087 p_token2_name => 'VALUE',
1088 p_token2_value => x_headers.agent_name_tbl(i)
1089 );
1090
1091 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1092 END IF;
1093
1094 IF (x_headers.ship_to_loc_tbl(i) IS NOT NULL AND
1095 x_headers.ship_to_loc_id_tbl(i) IS NULL) THEN
1096 IF (PO_LOG.d_stmt) THEN
1097 PO_LOG.stmt(d_module, d_position, 'ship_to loc id derivation failed');
1098 PO_LOG.stmt(d_module, d_position, 'ship_to loc', x_headers.ship_to_loc_tbl(i));
1099 END IF;
1100
1101 PO_PDOI_ERR_UTL.add_fatal_error
1102 (
1103 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1104 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1105 p_table_name => 'PO_HEADERS_INTERFACE',
1106 p_column_name => 'SHIP_TO_LOCATION_ID',
1107 p_column_value => x_headers.ship_to_loc_id_tbl(i),
1108 p_token1_name => 'COLUMN_NAME',
1109 p_token1_value => 'SHIP_TO_LOCATION_CODE',
1110 p_token2_name => 'VALUE',
1111 p_token2_value => x_headers.ship_to_loc_tbl(i)
1112 );
1113
1114 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1115 END IF;
1116
1117 IF (x_headers.bill_to_loc_tbl(i) IS NOT NULL AND
1118 x_headers.bill_to_loc_id_tbl(i) IS NULL) THEN
1119 IF (PO_LOG.d_stmt) THEN
1120 PO_LOG.stmt(d_module, d_position, 'bill_to loc id derivation failed');
1121 PO_LOG.stmt(d_module, d_position, 'bill_to loc', x_headers.bill_to_loc_tbl(i));
1122 END IF;
1123
1124 PO_PDOI_ERR_UTL.add_fatal_error
1125 (
1126 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1127 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1128 p_table_name => 'PO_HEADERS_INTERFACE',
1129 p_column_name => 'BILL_TO_LOCATION_ID',
1130 p_column_value => x_headers.bill_to_loc_id_tbl(i),
1131 p_token1_name => 'COLUMN_NAME',
1132 p_token1_value => 'BILL_TO_LOCATION_CODE',
1133 p_token2_name => 'VALUE',
1134 p_token2_value => x_headers.bill_to_loc_tbl(i)
1135 );
1136
1137 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1138 END IF;
1139
1140 IF (x_headers.payment_terms_tbl(i) IS NOT NULL AND
1141 x_headers.terms_id_tbl(i) IS NULL) THEN
1142 IF (PO_LOG.d_stmt) THEN
1143 PO_LOG.stmt(d_module, d_position, 'terms id derivation failed');
1144 PO_LOG.stmt(d_module, d_position, 'payment terms', x_headers.payment_terms_tbl(i));
1145 END IF;
1146
1147 PO_PDOI_ERR_UTL.add_fatal_error
1148 (
1149 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1150 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1151 p_table_name => 'PO_HEADERS_INTERFACE',
1152 p_column_name => 'TERMS_ID',
1153 p_column_value => x_headers.terms_id_tbl(i),
1154 p_token1_name => 'COLUMN_NAME',
1155 p_token1_value => 'PAYMENT_TERMS',
1156 p_token2_name => 'VALUE',
1157 p_token2_value => x_headers.payment_terms_tbl(i)
1158 );
1159
1160 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1161 END IF;
1162
1163 IF ((x_headers.vendor_name_tbl(i) IS NOT NULL OR
1164 x_headers.vendor_num_tbl(i) IS NOT NULL) AND
1165 x_headers.vendor_id_tbl(i) IS NULL) THEN
1166 IF (x_headers.vendor_num_tbl(i) IS NULL) THEN
1167 l_column_name := 'VENDOR_NAME';
1168 ELSE
1169 l_column_name := 'VENDOR_NUM';
1170 END IF;
1171
1172 IF (PO_LOG.d_stmt) THEN
1173 PO_LOG.stmt(d_module, d_position, 'vendor id derivation failed');
1174 PO_LOG.stmt(d_module, d_position, 'vendor name', x_headers.vendor_name_tbl(i));
1175 PO_LOG.stmt(d_module, d_position, 'vendor num', x_headers.vendor_num_tbl(i));
1176 END IF;
1177
1178 PO_PDOI_ERR_UTL.add_fatal_error
1179 (
1180 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1181 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1182 p_table_name => 'PO_HEADERS_INTERFACE',
1183 p_column_name => 'VENDOR_ID',
1184 p_column_value => x_headers.vendor_id_tbl(i),
1185 p_token1_name => 'COLUMN_NAME',
1186 p_token1_value => l_column_name,
1187 p_token2_name => 'VALUE',
1188 p_token2_value => NVL(x_headers.vendor_num_tbl(i),
1189 x_headers.vendor_name_tbl(i))
1190 );
1191
1192 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1193 END IF;
1194
1195 IF (x_headers.vendor_site_code_tbl(i) IS NOT NULL AND
1196 x_headers.vendor_site_id_tbl(i) IS NULL) THEN
1197 IF (PO_LOG.d_stmt) THEN
1198 PO_LOG.stmt(d_module, d_position, 'vendor site id derivation failed');
1199 PO_LOG.stmt(d_module, d_position, 'vendor site', x_headers.vendor_site_code_tbl(i));
1200 END IF;
1201
1202 PO_PDOI_ERR_UTL.add_fatal_error
1203 (
1204 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1205 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1206 p_table_name => 'PO_HEADERS_INTERFACE',
1207 p_column_name => 'VENDOR_SITE_ID',
1208 p_column_value => x_headers.vendor_site_id_tbl(i),
1209 p_token1_name => 'COLUMN_NAME',
1210 p_token1_value => 'VENDOR_SITE_CODE',
1211 p_token2_name => 'VALUE',
1212 p_token2_value => x_headers.vendor_site_code_tbl(i)
1213 );
1214
1215 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1216 END IF;
1217
1218 IF (x_headers.vendor_contact_tbl(i) IS NOT NULL AND
1219 x_headers.vendor_contact_id_tbl(i) IS NULL) THEN
1220 IF (PO_LOG.d_stmt) THEN
1221 PO_LOG.stmt(d_module, d_position, 'contact id derivation failed');
1222 PO_LOG.stmt(d_module, d_position, 'contact', x_headers.vendor_contact_tbl(i));
1223 END IF;
1224
1225 PO_PDOI_ERR_UTL.add_fatal_error
1226 (
1227 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1228 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1229 p_table_name => 'PO_HEADERS_INTERFACE',
1230 p_column_name => 'VENDOR_CONTACT_ID',
1231 p_column_value => x_headers.vendor_contact_id_tbl(i),
1232 p_token1_name => 'COLUMN_NAME',
1233 p_token1_value => 'VENDOR_CONTACT',
1234 p_token2_name => 'VALUE',
1235 p_token2_value => x_headers.vendor_contact_tbl(i)
1236 );
1237
1238 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1239 END IF;
1240
1241 IF (x_headers.style_display_name_tbl(i) IS NOT NULL AND
1242 x_headers.style_id_tbl(i) IS NULL) THEN
1243 IF (PO_LOG.d_stmt) THEN
1244 PO_LOG.stmt(d_module, d_position, 'style id derivation failed');
1245 PO_LOG.stmt(d_module, d_position, 'style_display_name', x_headers.style_display_name_tbl(i));
1246 END IF;
1247
1248 PO_PDOI_ERR_UTL.add_fatal_error
1249 (
1250 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1251 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1252 p_table_name => 'PO_HEADERS_INTERFACE',
1253 p_column_name => 'STYLE_ID',
1254 p_column_value => x_headers.style_id_tbl(i),
1255 p_token1_name => 'COLUMN_NAME',
1256 p_token1_value => 'STYLE_DISPLAY_NAME',
1257 p_token2_name => 'VALUE',
1258 p_token2_value => x_headers.style_display_name_tbl(i)
1259 );
1260
1261 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1262 END IF;
1263
1264 IF (PO_PDOI_PARAMS.g_request.document_type =
1265 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1266 IF (x_headers.from_rfq_num_tbl(i) IS NOT NULL AND
1267 x_headers.from_header_id_tbl(i) IS NULL) THEN
1268 IF (PO_LOG.d_stmt) THEN
1269 PO_LOG.stmt(d_module, d_position, 'from header id derivation failed');
1270 PO_LOG.stmt(d_module, d_position, 'rfq num', x_headers.from_rfq_num_tbl(i));
1271 END IF;
1272
1273 PO_PDOI_ERR_UTL.add_fatal_error
1274 (
1275 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1276 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1277 p_table_name => 'PO_HEADERS_INTERFACE',
1278 p_column_name => 'FROM_HEADER_ID',
1279 p_column_value => x_headers.from_header_id_tbl(i),
1280 p_token1_name => 'COLUMN_NAME',
1281 p_token1_value => 'FROM_RFQ_NUM',
1282 p_token2_name => 'VALUE',
1283 p_token2_value => x_headers.from_rfq_num_tbl(i)
1284 );
1285
1286 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1287 END IF;
1288 END IF;
1289
1290 --CLM PDOI Integration Starts
1291 IF Nvl(PO_PDOI_PARAMS.g_request.clm_flag,'N') = 'Y'
1292 THEN
1293
1294 IF (x_headers.clm_award_type_disp_tbl(i) IS NOT NULL AND
1295 x_headers.clm_award_type_tbl(i) IS NULL) THEN
1296 IF (PO_LOG.d_stmt) THEN
1297 PO_LOG.stmt(d_module, d_position, 'clm award type derivation failed');
1298 PO_LOG.stmt(d_module, d_position, 'clm_award_type', x_headers.clm_award_type_disp_tbl(i));
1299 END IF;
1300
1301 PO_PDOI_ERR_UTL.add_fatal_error
1302 (
1303 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1304 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1305 p_table_name => 'PO_HEADERS_INTERFACE',
1306 p_column_name => 'CLM_AWARD_TYPE',
1307 p_column_value => x_headers.clm_award_type_tbl(i),
1308 p_token1_name => 'COLUMN_NAME',
1309 p_token1_value => 'CLM_AWARD_TYPE_DISP',
1310 p_token2_name => 'VALUE',
1311 p_token2_value => x_headers.clm_award_type_disp_tbl(i)
1312 );
1313
1314 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1315 END IF;
1316
1317 IF (x_headers.clm_award_admin_disp_tbl(i) IS NOT NULL AND
1318 x_headers.clm_award_administrator_tbl(i) IS NULL) THEN
1319 IF (PO_LOG.d_stmt) THEN
1320 PO_LOG.stmt(d_module, d_position, 'clm award admin derivation failed');
1321 PO_LOG.stmt(d_module, d_position, 'clm_award_administrator', x_headers.clm_award_admin_disp_tbl(i));
1322 END IF;
1323
1324 PO_PDOI_ERR_UTL.add_fatal_error
1325 (
1326 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1327 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1328 p_table_name => 'PO_HEADERS_INTERFACE',
1329 p_column_name => 'CLM_AWARD_ADMINISTRATOR',
1330 p_column_value => x_headers.clm_award_administrator_tbl(i),
1331 p_token1_name => 'COLUMN_NAME',
1332 p_token1_value => 'CLM_AWARD_ADMINISTRATOR_DISP',
1333 p_token2_name => 'VALUE',
1334 p_token2_value => x_headers.clm_award_admin_disp_tbl(i)
1335 );
1336
1337 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1338 END IF;
1339
1340 IF (x_headers.clm_contract_officer_disp_tbl(i) IS NOT NULL AND
1341 x_headers.clm_contract_officer_tbl(i) IS NULL) THEN
1342 IF (PO_LOG.d_stmt) THEN
1343 PO_LOG.stmt(d_module, d_position, 'clm contract officer derivation failed');
1344 PO_LOG.stmt(d_module, d_position, 'clm_contract_officer', x_headers.clm_contract_officer_disp_tbl(i));
1345 END IF;
1346
1347 PO_PDOI_ERR_UTL.add_fatal_error
1348 (
1349 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1350 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1351 p_table_name => 'PO_HEADERS_INTERFACE',
1352 p_column_name => 'CLM_CONTRACT_OFFICER',
1353 p_column_value => x_headers.clm_contract_officer_tbl(i),
1354 p_token1_name => 'COLUMN_NAME',
1355 p_token1_value => 'CLM_CONTRACT_OFFICER_DISP',
1356 p_token2_name => 'VALUE',
1357 p_token2_value => x_headers.clm_contract_officer_disp_tbl(i)
1358 );
1359
1360 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1361 END IF;
1362
1363 IF (x_headers.clm_source_document_disp_tbl(i) IS NOT NULL AND
1364 x_headers.clm_source_document_id_tbl(i) IS NULL) THEN
1365 IF (PO_LOG.d_stmt) THEN
1366 PO_LOG.stmt(d_module, d_position, 'clm source document id derivation failed');
1367 PO_LOG.stmt(d_module, d_position, 'clm_source_document_id', x_headers.clm_source_document_disp_tbl(i));
1368 END IF;
1369
1370 PO_PDOI_ERR_UTL.add_fatal_error
1371 (
1372 p_interface_header_id => x_headers.intf_header_id_tbl(i),
1373 p_error_message_name => 'PO_PDOI_DERV_ERROR',
1374 p_table_name => 'PO_HEADERS_INTERFACE',
1375 p_column_name => 'CLM_SOURCE_DOCUMENT_ID',
1376 p_column_value => x_headers.clm_source_document_id_tbl(i),
1377 p_token1_name => 'COLUMN_NAME',
1378 p_token1_value => 'CLM_SOURCE_DOCUMENT_DISP',
1379 p_token2_name => 'VALUE',
1380 p_token2_value => x_headers.clm_source_document_disp_tbl(i)
1381 );
1382
1383 x_headers.error_flag_tbl(i) := FND_API.g_TRUE;
1384 END IF;
1385 END IF;
1386 --CLM PDOI Integration Ends
1387 END LOOP;
1388
1389 IF (PO_LOG.d_stmt) THEN
1390 PO_LOG.stmt(d_module, d_position, 'end of processing derivation errors');
1391 END IF;
1392
1393 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_DERIVE);
1394
1395 IF (PO_LOG.d_proc) THEN
1396 PO_LOG.proc_end (d_module);
1397 END IF;
1398
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 PO_MESSAGE_S.add_exc_msg
1402 (
1403 p_pkg_name => d_pkg_name,
1404 p_procedure_name => d_api_name || '.' || d_position
1405 );
1406 RAISE;
1407 END derive_headers;
1408
1409 --------------------------------------------------------------------------
1410 --Start of Comments
1411 --Name: default_headers
1412 --Pre-reqs: None
1413 --Modifies:
1414 --Locks:
1415 -- None
1416 --Function:
1417 -- perform default logic on header records read in one batch;
1418 --Parameters:
1419 --IN: None
1420 --IN OUT:
1421 -- x_headers
1422 -- variable to hold all the header attribute values in one batch;
1423 -- default result are saved inside the variable
1424 --OUT: None
1425 --Returns: None
1426 --Notes:
1427 --Testing:
1428 --End of Comments
1429 --------------------------------------------------------------------------
1430 PROCEDURE default_headers
1431 (
1432 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
1433 ) IS
1434
1435 d_api_name CONSTANT VARCHAR2(30) := 'default_headers';
1436 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1437 d_position NUMBER;
1438
1439 -- key used when operating on temp table
1440 l_key po_session_gt.key%TYPE;
1441
1442 -- table used to save the index of the each row
1443 l_index_tbl DBMS_SQL.NUMBER_TABLE;
1444
1445 -- information defaulted from vendor
1446
1447 -- <Bug 4546121: Supplier TCA conversion>
1448 -- The following columns are being obsoleted from PO_VENDORS level.
1449 --l_vendor_fob_tbl PO_TBL_VARCHAR30;
1450 --l_vendor_freight_carrier_tbl PO_TBL_VARCHAR30;
1451 --l_vendor_freight_term_tbl PO_TBL_VARCHAR30;
1452 --l_vendor_ship_to_loc_id_tbl PO_TBL_NUMBER;
1453 --l_vendor_bill_to_loc_id_tbl PO_TBL_NUMBER;
1454
1455 l_vendor_invoice_curr_code_tbl PO_TBL_VARCHAR30;
1456 l_vendor_terms_id_tbl PO_TBL_NUMBER;
1457
1458 -- information defaulted from vendor site
1459 l_site_fob_tbl PO_TBL_VARCHAR30;
1460 l_site_freight_carrier_tbl PO_TBL_VARCHAR30;
1461 l_site_freight_term_tbl PO_TBL_VARCHAR30;
1462 l_site_ship_to_loc_id_tbl PO_TBL_NUMBER;
1463 l_site_bill_to_loc_id_tbl PO_TBL_NUMBER;
1464 l_site_invoice_curr_code_tbl PO_TBL_VARCHAR30;
1465 l_site_terms_id_tbl PO_TBL_NUMBER;
1466 l_site_shipping_control_tbl PO_TBL_VARCHAR30;
1467 l_site_pay_on_code_tbl PO_TBL_VARCHAR30;
1468
1469 l_lang VARCHAR2(4);
1470
1471 l_display_rate NUMBER;
1472 BEGIN
1473 d_position := 0;
1474
1475 IF (PO_LOG.d_proc) THEN
1476 PO_LOG.proc_begin(d_module);
1477 END IF;
1478
1479 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_DEFAULT);
1480
1481 -- pick a new key which will be used in all derive logic
1482 l_key := PO_CORE_S.get_session_gt_nextval;
1483
1484 IF (PO_LOG.d_stmt) THEN
1485 PO_LOG.stmt(d_module, d_position, 'key', l_key);
1486 END IF;
1487
1488 -- initialize table containing the row number
1489 PO_PDOI_UTL.generate_ordered_num_list
1490 (
1491 p_size => x_headers.rec_count,
1492 x_num_list => l_index_tbl
1493 );
1494
1495 d_position := 10;
1496
1497 -- default information from vendor
1498 default_info_from_vendor
1499 (
1500 p_key => l_key,
1501 p_index_tbl => l_index_tbl,
1502 p_vendor_id_tbl => x_headers.vendor_id_tbl,
1503 x_invoice_currency_code_tbl => l_vendor_invoice_curr_code_tbl,
1504 x_terms_id_tbl => l_vendor_terms_id_tbl
1505 );
1506
1507 d_position := 20;
1508
1509 -- default information from vendor site
1510 default_info_from_vendor_site
1511 (
1512 p_key => l_key,
1513 p_index_tbl => l_index_tbl,
1514 p_vendor_id_tbl => x_headers.vendor_id_tbl,
1515 x_vendor_site_id_tbl => x_headers.vendor_site_id_tbl,
1516 x_fob_tbl => l_site_fob_tbl,
1517 x_freight_carrier_tbl => l_site_freight_carrier_tbl,
1518 x_freight_term_tbl => l_site_freight_term_tbl,
1519 x_ship_to_loc_id_tbl => l_site_ship_to_loc_id_tbl,
1520 x_bill_to_loc_id_tbl => l_site_bill_to_loc_id_tbl,
1521 x_invoice_currency_code_tbl => l_site_invoice_curr_code_tbl,
1522 x_terms_id_tbl => l_site_terms_id_tbl,
1523 x_shipping_control_tbl => l_site_shipping_control_tbl,
1524 x_pay_on_code_tbl => l_site_pay_on_code_tbl
1525 );
1526
1527 d_position := 30;
1528
1529 -- default vendor contact from vendor site
1530 default_vendor_contact
1531 (
1532 p_key => l_key,
1533 p_index_tbl => l_index_tbl,
1534 p_vendor_site_id_tbl => x_headers.vendor_site_id_tbl,
1535 x_vendor_contact_id_tbl => x_headers.vendor_contact_id_tbl
1536 );
1537
1538 d_position := 40;
1539
1540 FOR i IN 1..x_headers.rec_count
1541 LOOP
1542 d_position := 50;
1543
1544 IF (PO_LOG.d_stmt) THEN
1545 PO_LOG.stmt(d_module, d_position, 'index', i);
1546 END IF;
1547
1548
1549
1550 -- default created_language for Blanket and Quotation
1551 IF (PO_PDOI_PARAMS.g_request.document_type IN
1552 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET, PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION)) THEN
1553 x_headers.created_language_tbl(i) :=
1554 NVL(x_headers.created_language_tbl(i), USERENV('LANG'));
1555 END IF;
1556
1557 -- default agent_id
1558 x_headers.agent_id_tbl(i) :=
1559 NVL(x_headers.agent_id_tbl(i), PO_PDOI_PARAMS.g_request.buyer_id);
1560
1561 -- default document_type_code
1562 x_headers.doc_type_tbl(i) :=
1563 NVL(x_headers.doc_type_tbl(i), PO_PDOI_PARAMS.g_request.document_type);
1564
1565 -- default fob_lookup_code
1566 x_headers.fob_tbl(i) :=
1567 COALESCE(x_headers.fob_tbl(i), l_site_fob_tbl(i),
1568 PO_PDOI_PARAMS.g_sys.fob_lookup_code);
1569
1570 -- default freight_carrier(ship_via_lookup_code)
1571 x_headers.freight_carrier_tbl(i) :=
1572 COALESCE(x_headers.freight_carrier_tbl(i), l_site_freight_carrier_tbl(i),
1573 PO_PDOI_PARAMS.g_sys.ship_via_lookup_code);
1574
1575 -- default freight_terms
1576 x_headers.freight_term_tbl(i) :=
1577 COALESCE(x_headers.freight_term_tbl(i), l_site_freight_term_tbl(i),
1578 PO_PDOI_PARAMS.g_sys.freight_terms_lookup_code);
1579
1580 -- default terms_id
1581 x_headers.terms_id_tbl(i) :=
1582 COALESCE(x_headers.terms_id_tbl(i), l_site_terms_id_tbl(i),
1583 l_vendor_terms_id_tbl(i));
1584
1585 -- default shipping_control
1586 x_headers.shipping_control_tbl(i) :=
1587 NVL(x_headers.shipping_control_tbl(i), l_site_shipping_control_tbl(i));
1588
1589 -- default ship_to_location_id
1590 x_headers.ship_to_loc_id_tbl(i) :=
1591 COALESCE(x_headers.ship_to_loc_id_tbl(i), l_site_ship_to_loc_id_tbl(i),
1592 PO_PDOI_PARAMS.g_sys.ship_to_location_id);
1593
1594 -- default bill_to_location_id
1595 x_headers.bill_to_loc_id_tbl(i) :=
1596 COALESCE(x_headers.bill_to_loc_id_tbl(i), l_site_bill_to_loc_id_tbl(i),
1597 PO_PDOI_PARAMS.g_sys.bill_to_location_id);
1598
1599 x_headers.global_agreement_flag_tbl(i) :=
1600 NVL(x_headers.global_agreement_flag_tbl(i), PO_PDOI_PARAMS.g_request.ga_flag);
1601
1602 -- default style_id
1603 IF (x_headers.doc_type_tbl(i) = 'QUOTATION' OR
1604 (x_headers.doc_type_tbl(i) = 'BLANKET' AND x_headers.global_agreement_flag_tbl(i) = 'N'))
1605 THEN
1606 --CLM PDOI Integration
1607 IF Nvl(PO_PDOI_PARAMS.g_request.clm_flag,'N') = 'Y'
1608 THEN
1609 NULL;
1610 ELSE
1611 x_headers.style_id_tbl(i) := PO_DOC_STYLE_GRP.get_standard_doc_style;
1612 END IF;
1613 ELSE
1614 x_headers.style_id_tbl(i) :=
1615 NVL(x_headers.style_id_tbl(i), PO_DOC_STYLE_GRP.get_standard_doc_style);
1616 END IF;
1617
1618 -- set pay_on_code
1619 x_headers.pay_on_code_tbl(i) := l_site_pay_on_code_tbl(i);
1620
1621 -- default approval_status
1622 x_headers.approval_status_tbl(i) :=
1623 NVL(x_headers.approval_status_tbl(i), PO_PDOI_PARAMS.g_request.approved_status);
1624
1625 -- bug4911383
1626 -- If intended approval status = 'APPROVED', it cannot require signature
1627
1628 x_headers.acceptance_required_flag_tbl(i) :=
1629 NVL(x_headers.acceptance_required_flag_tbl(i), PO_PDOI_PARAMS.g_sys.acceptance_required_flag); /* Bug 7518967 : Default Acceptance Required Check ER */
1630
1631
1632 IF (x_headers.approval_status_tbl(i) = 'APPROVED' AND
1633 x_headers.acceptance_required_flag_tbl(i) = 'S') THEN
1634
1635 x_headers.acceptance_required_flag_tbl(i) := 'N';
1636
1637
1638 END IF;
1639
1640 -- bug4690880
1641 -- All document types will share same behavior in terms of currency code
1642 -- defaulting
1643 -- default currency_code
1644 x_headers.currency_code_tbl(i) :=
1645 COALESCE(x_headers.currency_code_tbl(i), l_site_invoice_curr_code_tbl(i),
1646 l_vendor_invoice_curr_code_tbl(i), PO_PDOI_PARAMS.g_sys.currency_code);
1647
1648 d_position := 60;
1649
1650 -- default attributes for each document type
1651 IF (x_headers.doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1652 -- default document sub-type
1653 x_headers.doc_subtype_tbl(i) :=
1654 NVL(x_headers.doc_subtype_tbl(i), PO_PDOI_PARAMS.g_request.document_subtype);
1655
1656 -- set quotation_class_code
1657 x_headers.quotation_class_code_tbl(i) :=
1658 PO_PDOI_MAINPROC_UTL_PVT.get_quotation_class_code
1659 (
1660 x_headers.doc_subtype_tbl(i)
1661 );
1662
1663 IF (PO_LOG.d_stmt) THEN
1664 PO_LOG.stmt(d_module, d_position, 'quote class code',
1665 x_headers.quotation_class_code_tbl(i));
1666 END IF;
1667
1668 -- set global agreement flag to NULL
1669 x_headers.global_agreement_flag_tbl(i) := NULL;
1670
1671 -- default quote_warning_delay
1672 x_headers.quote_warning_delay_tbl(i) :=
1673 NVL(x_headers.quote_warning_delay_tbl(i),
1674 PO_PDOI_PARAMS.g_sys.def_quote_warning_delay);
1675
1676 -- default approval_required_flag
1677 x_headers.approval_required_flag_tbl(i) :=
1678 NVL(x_headers.approval_required_flag_tbl(i), 'N');
1679
1680 -- default reply_date
1681 x_headers.reply_date_tbl(i) := NVL(x_headers.reply_date_tbl(i), sysdate);
1682
1683 -- set approved_flag
1684 x_headers.approved_flag_tbl(i) := NULL;
1685
1686 -- set approved_date
1687 x_headers.approved_date_tbl(i) := NULL;
1688
1689 d_position := 70;
1690
1691 -- set status_lookup_code
1692 IF (x_headers.approval_status_tbl(i) = 'INCOMPLETE') THEN
1693 x_headers.status_lookup_code_tbl(i) := 'I';
1694 ELSE
1695 -- approval_status = 'APPROVED'
1696 x_headers.status_lookup_code_tbl(i) := 'A';
1697 END IF;
1698
1699 -- default from_type_lookup_code
1700 IF (x_headers.from_type_lookup_code_tbl(i) IS NULL AND
1701 x_headers.from_header_id_tbl(i) IS NOT NULL) THEN
1702 x_headers.from_type_lookup_code_tbl(i) := 'RFQ';
1703 END IF;
1704
1705 -- set cancel_flag
1706 x_headers.cancel_flag_tbl(i) := NULL;
1707
1708 -- set vendor_order_num
1709 x_headers.vendor_order_num_tbl(i) := NULL;
1710
1711 -- set quote_vendor_quote_num
1712 x_headers.quote_vendor_quote_num_tbl(i) := x_headers.vendor_doc_num_tbl(i);
1713
1714 -- set document_creation_method
1715 x_headers.doc_creation_method_tbl(i) := NULL;
1716
1717 -- default document_number
1718 -- this is not the final value for document_number,
1719 -- but a temp value used to insert record into draft table
1720
1721 -- if document num assigning method is 'AUTOMATIC', always overwrite
1722 -- user's document num input
1723 IF (PO_PDOI_PARAMS.g_sys.user_defined_quote_num_code = 'AUTOMATIC') THEN
1724
1725 d_position := 80;
1726
1727 -- bug5028275
1728 -- assign document number only if the user has not provided any
1729 IF (x_headers.document_num_tbl(i) IS NULL) THEN
1730 x_headers.document_num_tbl(i) := -x_headers.po_header_id_tbl(i);
1731 END IF;
1732
1733 IF (PO_LOG.d_stmt) THEN
1734 PO_LOG.stmt(d_module, d_position, 'temp doc num',
1735 x_headers.document_num_tbl(i));
1736 END IF;
1737
1738 END IF;
1739 ELSIF (x_headers.doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
1740
1741 d_position := 90;
1742
1743 -- default revision_num
1744 x_headers.revision_num_tbl(i) := NVL(x_headers.revision_num_tbl(i), 0);
1745
1746 d_position := 100;
1747
1748 -- default confirming_order_flag
1749 x_headers.confirming_order_flag_tbl(i) :=
1750 NVL(x_headers.confirming_order_flag_tbl(i), 'N');
1751
1752 -- default acceptance_required_flag
1753 x_headers.acceptance_required_flag_tbl(i) :=
1754 NVL(x_headers.acceptance_required_flag_tbl(i), PO_PDOI_PARAMS.g_sys.acceptance_required_flag); /* Bug 7518967 : Default Acceptance Required Check ER */
1755
1756 -- default min_release_amount
1757 x_headers.min_release_amount_tbl(i) :=
1758 NVL(x_headers.min_release_amount_tbl(i),
1759 PO_PDOI_PARAMS.g_sys.min_rel_amount);
1760
1761 -- default closed_code
1762 x_headers.closed_code_tbl(i) := NVL(x_headers.closed_code_tbl(i), 'OPEN');
1763
1764 -- default print_count
1765 x_headers.print_count_tbl(i) := NVL(x_headers.print_count_tbl(i), 0);
1766
1767 -- default frozen_flag
1768 x_headers.frozen_flag_tbl(i) := NVL(x_headers.frozen_flag_tbl(i), 'N');
1769
1770 d_position := 110;
1771
1772 x_headers.approved_flag_tbl(i) := NULL;
1773 x_headers.approved_date_tbl(i) := NULL;
1774
1775 -- set status_lookup_code
1776 x_headers.status_lookup_code_tbl(i) := NULL;
1777
1778 -- set cancel_flag
1779 x_headers.cancel_flag_tbl(i) := 'N';
1780
1781 -- set vendor_order_num
1782 x_headers.vendor_order_num_tbl(i) := x_headers.vendor_doc_num_tbl(i);
1783
1784 -- set quote_vendor_quote_num
1785 x_headers.quote_vendor_quote_num_tbl(i) := NULL;
1786
1787 -- set document_creation_method
1788 x_headers.doc_creation_method_tbl(i) := 'PDOI';
1789
1790 -- default document_number
1791 -- this is not the final value for document_number,
1792 -- but a temp value used to insert record into draft table
1793
1794 -- if document num assigning method is 'AUTOMATIC', always overwrite
1795 -- user's document num input
1796 IF (PO_PDOI_PARAMS.g_sys.user_defined_po_num_code = 'AUTOMATIC') THEN
1797
1798 d_position := 120;
1799
1800 -- bug5028275
1801 -- assign document number only if the user has not provided any
1802 IF (x_headers.document_num_tbl(i) IS NULL) THEN
1803 x_headers.document_num_tbl(i) := -x_headers.po_header_id_tbl(i);
1804 END IF;
1805
1806 IF (PO_LOG.d_stmt) THEN
1807 PO_LOG.stmt(d_module, d_position, 'temp doc num',
1808 x_headers.document_num_tbl(i));
1809 END IF;
1810 END IF;
1811 ELSIF (x_headers.doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1812
1813 d_position := 130;
1814
1815 -- default revision_num
1816 x_headers.revision_num_tbl(i) := NVL(x_headers.revision_num_tbl(i), 0);
1817
1818 -- default confirming_order_flag
1819 x_headers.confirming_order_flag_tbl(i) :=
1820 NVL(x_headers.confirming_order_flag_tbl(i), 'N');
1821
1822 -- default acceptance_required_flag
1823 x_headers.acceptance_required_flag_tbl(i) :=
1824 NVL(x_headers.acceptance_required_flag_tbl(i), PO_PDOI_PARAMS.g_sys.acceptance_required_flag); /* Bug 7518967 : Default Acceptance Required Check ER */
1825
1826 -- default closed_code
1827 x_headers.closed_code_tbl(i) := NVL(x_headers.closed_code_tbl(i), 'OPEN');
1828
1829 -- default print_count
1830 x_headers.print_count_tbl(i) := NVL(x_headers.print_count_tbl(i), 0);
1831
1832 -- default frozen_flag
1833 x_headers.frozen_flag_tbl(i) := NVL(x_headers.frozen_flag_tbl(i), 'N');
1834
1835 d_position := 140;
1836
1837 x_headers.approved_flag_tbl(i) := NULL;
1838 x_headers.approved_date_tbl(i) := NULL;
1839
1840 d_position := 150;
1841
1842 -- set status_lookup_code
1843 x_headers.status_lookup_code_tbl(i) := NULL;
1844
1845 -- set cancel_flag
1846 x_headers.cancel_flag_tbl(i) := 'N';
1847
1848 -- set vendor_order_num
1849 x_headers.vendor_order_num_tbl(i) := x_headers.vendor_doc_num_tbl(i);
1850
1851 -- set quote_vendor_quote_num
1852 x_headers.quote_vendor_quote_num_tbl(i) := NULL;
1853
1854 -- set document_creation_method
1855 x_headers.doc_creation_method_tbl(i) := 'PDOI';
1856
1857 -- set tax attribute update code
1858 x_headers.tax_attribute_update_code_tbl(i) := 'CREATE';
1859
1860 -- default document_number
1861 -- this is not the final value for document_number,
1862 -- but a temp value used to insert record into draft table
1863
1864 -- if document num assigning method is 'AUTOMATIC', always overwrite
1865 -- user's document num input
1866 IF (PO_PDOI_PARAMS.g_sys.user_defined_po_num_code = 'AUTOMATIC') THEN
1867
1868 -- bug5028275
1869 -- assign document number only if the user has not provided any
1870 IF (x_headers.document_num_tbl(i) IS NULL) THEN
1871 x_headers.document_num_tbl(i) := -x_headers.po_header_id_tbl(i);
1872 END IF;
1873
1874 IF (PO_LOG.d_stmt) THEN
1875 PO_LOG.stmt(d_module, d_position, 'temp doc num',
1876 x_headers.document_num_tbl(i));
1877 END IF;
1878
1879 END IF;
1880 END IF;
1881
1882 d_position := 160;
1883
1884 -- default rate info after currency is defaulted
1885 IF (x_headers.currency_code_tbl(i) <> PO_PDOI_PARAMS.g_sys.currency_code) THEN
1886 IF (PO_LOG.d_stmt) THEN
1887 PO_LOG.stmt(d_module, d_position, 'default rate info');
1888 PO_LOG.stmt(d_module, d_position, 'currency_code',
1889 x_headers.currency_code_tbl(i));
1890 END IF;
1891
1892 -- default rate_date
1893 x_headers.rate_date_tbl(i) := NVL(x_headers.rate_date_tbl(i), sysdate);
1894
1895 -- default rate_type
1896 /* 8688769 BUG,Added Exception Block for GL API call */
1897 BEGIN
1898 IF (GL_CURRENCY_API.is_fixed_rate
1899 (
1900 x_from_currency => x_headers.currency_code_tbl(i),
1901 x_to_currency => PO_PDOI_PARAMS.g_sys.currency_code,
1902 x_effective_date => x_headers.rate_date_tbl(i)
1903 ) = 'Y') THEN
1904 x_headers.rate_type_code_tbl(i) := 'EMU FIXED'; --bug 7653758
1905 ELSE
1906 x_headers.rate_type_code_tbl(i) :=
1907 NVL(x_headers.rate_type_code_tbl(i), PO_PDOI_PARAMS.g_sys.default_rate_type); --bug 7653758
1908 END IF;
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 IF (PO_LOG.d_stmt) THEN
1912 PO_LOG.stmt(d_module, d_position, 'Exception arised in GL_CURRENCY_API');
1913 END IF;
1914 END;
1915
1916 IF (PO_LOG.d_stmt) THEN
1917 PO_LOG.stmt(d_module, d_position, 'rate type',
1918 x_headers.rate_type_code_tbl(i)); --bug7653758
1919 END IF;
1920
1921 d_position := 170;
1922
1923 -- default rate
1924 IF (x_headers.rate_tbl(i) IS NULL OR
1925 x_headers.rate_type_code_tbl(i) = 'EMU FIXED') THEN --bug 7653758
1926 po_currency_sv.get_rate
1927 (
1928 x_set_of_books_id => PO_PDOI_PARAMS.g_sys.sob_id,
1929 x_currency_code => x_headers.currency_code_tbl(i),
1930 x_rate_type => x_headers.rate_type_code_tbl(i), --bug 7653758
1931 x_rate_date => x_headers.rate_date_tbl(i),
1932 x_inverse_rate_display_flag => 'N',
1933 x_rate => x_headers.rate_tbl(i),
1934 x_display_rate => l_display_rate
1935 );
1936
1937 IF (PO_LOG.d_stmt) THEN
1938 PO_LOG.stmt(d_module, d_position, 'rate', x_headers.rate_tbl(i));
1939 END IF;
1940 END IF;
1941 END IF;
1942 END LOOP;
1943
1944 d_position := 180;
1945
1946 -- default the distribution related fields for a Blanket if
1947 -- encumbrance is required on this document
1948 IF (PO_PDOI_PARAMS.g_request.document_type =
1949 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET AND
1950 PO_PDOI_PARAMS.g_sys.po_encumbrance_flag = 'Y' AND
1951 PO_PDOI_PARAMS.g_sys.req_encumbrance_flag = 'Y') THEN
1952 IF (PO_LOG.d_stmt) THEN
1953 PO_LOG.stmt(d_module, d_position, 'create distribution for blanket' ||
1954 ' since encumbrance is required');
1955 END IF;
1956
1957 default_dist_attributes
1958 (
1959 x_headers => x_headers
1960 );
1961 END IF;
1962
1963 d_position := 190;
1964
1965 -- call utility method to default standard who columns
1966 PO_PDOI_MAINPROC_UTL_PVT.default_who_columns
1967 (
1968 x_last_update_date_tbl => x_headers.last_update_date_tbl,
1969 x_last_updated_by_tbl => x_headers.last_updated_by_tbl,
1970 x_last_update_login_tbl => x_headers.last_update_login_tbl,
1971 x_creation_date_tbl => x_headers.creation_date_tbl,
1972 x_created_by_tbl => x_headers.created_by_tbl,
1973 x_request_id_tbl => x_headers.request_id_tbl,
1974 x_program_application_id_tbl => x_headers.program_application_id_tbl,
1975 x_program_id_tbl => x_headers.program_id_tbl,
1976 x_program_update_date_tbl => x_headers.program_update_date_tbl
1977 );
1978
1979 --CLM PDOI Integration Starts
1980 IF Nvl(PO_PDOI_PARAMS.g_request.clm_flag,'N') = 'Y'
1981 THEN
1982 default_clm_standard_format
1983 (
1984 p_key => l_key,
1985 p_index_tbl => l_index_tbl,
1986 p_style_id_tbl => x_headers.style_id_tbl,
1987 p_clm_award_type => x_headers.clm_award_type_tbl,
1988 x_clm_standard_form_tbl => x_headers.clm_standard_form_tbl,
1989 x_clm_document_format_tbl => x_headers.clm_document_format_tbl
1990 );
1991 END IF;
1992 --CLM PDOI Integration Ends
1993
1994 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_DEFAULT);
1995
1996 IF (PO_LOG.d_proc) THEN
1997 PO_LOG.proc_end (d_module);
1998 END IF;
1999
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 PO_MESSAGE_S.add_exc_msg
2003 (
2004 p_pkg_name => d_pkg_name,
2005 p_procedure_name => d_api_name || '.' || d_position
2006 );
2007 RAISE;
2008 END default_headers;
2009
2010 -----------------------------------------------------------------------
2011 --Start of Comments
2012 --Name: validate_headers
2013 --Function:
2014 -- validate header attributes;
2015 -- If there is error(s) on any attribute of the header row,
2016 -- corresponding value in error_flag_tbl will be set with value
2017 -- FND_API.g_TRUE.
2018 --Parameters:
2019 --IN:
2020 --x_headers
2021 -- record containing header info within the batch;
2022 --IN OUT:
2023 --OUT:
2024 --End of Comments
2025 ------------------------------------------------------------------------
2026 PROCEDURE validate_headers
2027 (
2028 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
2029 ) IS
2030
2031 d_api_name CONSTANT VARCHAR2(30) := 'validate_headers';
2032 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2033 d_position NUMBER;
2034
2035 l_headers PO_HEADERS_VAL_TYPE := PO_HEADERS_VAL_TYPE();
2036 l_result_type VARCHAR2(30);
2037 l_results po_validation_results_type;
2038 l_parameter_name_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2039 l_parameter_value_tbl PO_TBL_VARCHAR2000 := PO_TBL_VARCHAR2000();
2040
2041 BEGIN
2042 d_position := 0;
2043
2044 IF (PO_LOG.d_proc) THEN
2045 PO_LOG.proc_begin(d_module, 'x_headers', x_headers.intf_header_id_tbl);
2046 END IF;
2047
2048 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_VALIDATE);
2049
2050 l_headers.interface_id := x_headers.intf_header_id_tbl;
2051 l_headers.po_header_id := x_headers.po_header_id_tbl;
2052 l_headers.start_date := x_headers.effective_date_tbl;
2053 l_headers.end_date := x_headers.expiration_date_tbl;
2054 l_headers.type_lookup_code := x_headers.doc_type_tbl;
2055 l_headers.acceptance_required_flag := x_headers.acceptance_required_flag_tbl;
2056 l_headers.revision_num := x_headers.revision_num_tbl;
2057 l_headers.document_num := x_headers.document_num_tbl;
2058 l_headers.org_id := x_headers.org_id_tbl;
2059 l_headers.currency_code := x_headers.currency_code_tbl;
2060 l_headers.rate_type := x_headers.rate_type_code_tbl; --bug 7653758
2061 l_headers.rate := x_headers.rate_tbl;
2062 l_headers.rate_date := x_headers.rate_date_tbl;
2063 l_headers.agent_id := x_headers.agent_id_tbl;
2064 l_headers.vendor_id := x_headers.vendor_id_tbl;
2065 l_headers.vendor_site_id := x_headers.vendor_site_id_tbl;
2066 l_headers.vendor_contact_id := x_headers.vendor_contact_id_tbl;
2067 l_headers.ship_to_location_id := x_headers.ship_to_loc_id_tbl;
2068 l_headers.bill_to_location_id := x_headers.bill_to_loc_id_tbl;
2069 l_headers.last_update_date := x_headers.last_update_date_tbl;
2070 l_headers.last_updated_by := x_headers.last_updated_by_tbl;
2071 l_headers.po_release_id := x_headers.po_release_id_tbl;
2072 l_headers.release_num := x_headers.release_num_tbl;
2073 l_headers.release_date := x_headers.release_date_tbl;
2074 l_headers.revised_date := x_headers.revised_date_tbl;
2075 l_headers.printed_date := x_headers.printed_date_tbl;
2076 l_headers.closed_date := x_headers.closed_date_tbl;
2077 l_headers.terms_id := x_headers.terms_id_tbl;
2078 l_headers.ship_via_lookup_code := x_headers.freight_carrier_tbl;
2079 l_headers.fob_lookup_code := x_headers.fob_tbl;
2080 l_headers.freight_terms_lookup_code := x_headers.freight_term_tbl;
2081 l_headers.shipping_control := x_headers.shipping_control_tbl;
2082 l_headers.confirming_order_flag := x_headers.confirming_order_flag_tbl;
2083 l_headers.acceptance_due_date := x_headers.acceptance_due_date_tbl;
2084 l_headers.amount_agreed := x_headers.amount_agreed_tbl;
2085 l_headers.amount_limit := x_headers.amount_limit_tbl; -- bug5352625
2086 l_headers.firm_status_lookup_code := x_headers.firm_flag_tbl;
2087 l_headers.cancel_flag := x_headers.cancel_flag_tbl;
2088 l_headers.closed_code := x_headers.closed_code_tbl;
2089 l_headers.print_count := x_headers.print_count_tbl;
2090 l_headers.frozen_flag := x_headers.frozen_flag_tbl;
2091 l_headers.approval_status := x_headers.approval_status_tbl;
2092 l_headers.amount_to_encumber := x_headers.amount_to_encumber_tbl;
2093 l_headers.quote_warning_delay := x_headers.quote_warning_delay_tbl;
2094 l_headers.approval_required_flag := x_headers.approval_required_flag_tbl;
2095 l_headers.style_id := x_headers.style_id_tbl;
2096
2097 --PDOI CLM Integration
2098 l_headers.clm_standard_form := x_headers.clm_standard_form_tbl;
2099 l_headers.clm_document_format := x_headers.clm_document_format_tbl;
2100 l_headers.clm_award_type := x_headers.clm_award_type_tbl;
2101 l_headers.clm_source_document_id := x_headers.clm_source_document_id_tbl;
2102 l_headers.clm_award_administrator := x_headers.clm_award_administrator_tbl;
2103 l_headers.clm_contract_officer := x_headers.clm_contract_officer_tbl;
2104 l_headers.umbrella_program_id := x_headers.umbrella_program_id_tbl;
2105 l_headers.clm_external_idv := x_headers.clm_external_idv_tbl;
2106 l_headers.draft_type := x_headers.draft_type_tbl;
2107
2108 l_parameter_name_tbl.EXTEND(6);
2109 l_parameter_value_tbl.EXTEND(6);
2110 l_parameter_name_tbl(1) := 'INVENTORY_ORG_ID';
2111 l_parameter_value_tbl(1) := PO_PDOI_PARAMS.g_sys.def_inv_org_id; -- bug5601416
2112 l_parameter_name_tbl(2) := 'SET_OF_BOOKS_ID';
2113 l_parameter_value_tbl(2) := PO_PDOI_PARAMS.g_sys.sob_id;
2114 l_parameter_name_tbl(3) := 'FUNCTIONAL_CURRENCY_CODE';
2115 l_parameter_value_tbl(3) := PO_PDOI_PARAMS.g_sys.currency_code;
2116 l_parameter_name_tbl(4) := 'FEDERAL_INSTANCE';
2117 l_parameter_value_tbl(4) := PO_PDOI_PARAMS.g_sys.is_federal_instance;
2118 l_parameter_name_tbl(5) := 'MANUAL_PO_NUM_TYPE';
2119 l_parameter_value_tbl(5) := PO_PDOI_PARAMS.g_sys.manual_po_num_type;
2120 l_parameter_name_tbl(6) := 'MANUAL_QUOTE_NUM_TYPE';
2121 l_parameter_value_tbl(6) := PO_PDOI_PARAMS.g_sys.manual_quote_num_type;
2122
2123 d_position := 10;
2124
2125 PO_VALIDATIONS.validate_pdoi(p_headers => l_headers,
2126 p_doc_type => PO_PDOI_PARAMS.g_request.document_type,
2127 p_parameter_name_tbl => l_parameter_name_tbl,
2128 p_parameter_value_tbl => l_parameter_value_tbl,
2129 x_result_type => l_result_type,
2130 x_results => l_results);
2131
2132 d_position := 20;
2133
2134 IF l_result_type = po_validations.c_result_type_failure THEN
2135 IF (PO_LOG.d_stmt) THEN
2136 PO_LOG.stmt(d_module, d_position, 'vaidate headers return failure');
2137 END IF;
2138
2139 PO_PDOI_ERR_UTL.process_val_type_errors
2140 (
2141 x_results => l_results,
2142 p_table_name => 'PO_HEADERS_INTERFACE',
2143 p_headers => x_headers
2144 );
2145
2146 d_position := 30;
2147
2148 populate_error_flag
2149 (
2150 x_results => l_results,
2151 x_headers => x_headers
2152 );
2153 END IF;
2154
2155 d_position := 40;
2156
2157 IF l_result_type = po_validations.c_result_type_fatal THEN
2158 IF (PO_LOG.d_stmt) THEN
2159 PO_LOG.stmt(d_module, d_position, 'vaidate headers return fatal');
2160 END IF;
2161
2162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2163 END IF;
2164
2165 d_position := 50;
2166
2167 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_VALIDATE);
2168
2169 IF (PO_LOG.d_proc) THEN
2170 PO_LOG.proc_end (d_module);
2171 END IF;
2172
2173 EXCEPTION
2174 WHEN OTHERS THEN
2175 PO_MESSAGE_S.add_exc_msg
2176 (
2177 p_pkg_name => d_pkg_name,
2178 p_procedure_name => d_api_name || '.' || d_position
2179 );
2180 RAISE;
2181 END validate_headers;
2182
2183 -------------------------------------------------------------------------
2184 --Start of Comments
2185 --Name: derive_location_id
2186 --Pre-reqs: None
2187 --Modifies:
2188 --Locks:
2189 -- None
2190 --Function:
2191 -- handle the logic to derive location_id from location code in batch mode
2192 --Parameters:
2193 --IN:
2194 -- p_key
2195 -- identifier in the temp table on the derived result
2196 -- p_index_tbl
2197 -- indexes of the records
2198 -- p_location_type
2199 -- the value can be 'SHIP_TO'/'BILL_TO'
2200 -- p_location_tbl
2201 -- values of location code in current batch of records
2202 --IN OUT:
2203 -- x_location_id_tbl
2204 -- contains the derived result if original value is null
2205 --OUT: None
2206 --Returns:
2207 --Notes:
2208 --Testing:
2209 --End of Comments
2210 ------------------------------------------------------------------------
2211 PROCEDURE derive_location_id
2212 (
2213 p_key IN po_session_gt.key%TYPE,
2214 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2215 p_location_type IN VARCHAR2,
2216 p_location_tbl IN PO_TBL_VARCHAR100,
2217 x_location_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2218 ) IS
2219
2220 d_api_name CONSTANT VARCHAR2(30) := 'derive_location_id';
2221 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2222 d_position NUMBER;
2223
2224 -- tables to store the derived result
2225 l_index_tbl PO_TBL_NUMBER;
2226 l_result_tbl PO_TBL_NUMBER;
2227 BEGIN
2228 d_position := 0;
2229
2230 IF (PO_LOG.d_proc) THEN
2231 PO_LOG.proc_begin(d_module, 'location type', p_location_type);
2232 PO_LOG.proc_begin(d_module, 'locations', p_location_tbl);
2233 PO_LOG.proc_begin(d_module, 'location ids', x_location_id_tbl);
2234 END IF;
2235
2236 IF (p_location_type = 'SHIP_TO') THEN
2237 FORALL i IN 1..p_index_tbl.COUNT
2238 INSERT INTO po_session_gt(key, num1, num2)
2239 SELECT p_key,
2240 p_index_tbl(i),
2241 location_id
2242 FROM po_locations_val_v
2243 WHERE x_location_id_tbl(i) IS NULL
2244 AND p_location_tbl(i) IS NOT NULL
2245 AND location_code = p_location_tbl(i)
2246 AND nvl(ship_to_site_flag, 'N') = 'Y';
2247 ELSE -- p_location_type = 'BILL_TO'
2248 FORALL i IN 1..p_index_tbl.COUNT
2249 INSERT INTO po_session_gt(key, num1, num2)
2250 SELECT p_key,
2251 p_index_tbl(i),
2252 location_id
2253 FROM po_locations_val_v
2254 WHERE x_location_id_tbl(i) IS NULL
2255 AND p_location_tbl(i) IS NOT NULL
2256 AND location_code = p_location_tbl(i)
2257 AND nvl(bill_to_site_flag, 'N') = 'Y';
2258 END IF;
2259
2260 d_position := 10;
2261
2262 DELETE FROM po_session_gt
2263 WHERE key = p_key
2264 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2265
2266 d_position := 20;
2267
2268 FOR i IN 1..l_index_tbl.COUNT
2269 LOOP
2270 IF (PO_LOG.d_stmt) THEN
2271 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2272 PO_LOG.stmt(d_module, d_position, 'new location id', l_result_tbl(i));
2273 END IF;
2274
2275 x_location_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2276 END LOOP;
2277
2278 IF (PO_LOG.d_proc) THEN
2279 PO_LOG.proc_end(d_module);
2280 END IF;
2281
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284 PO_MESSAGE_S.add_exc_msg
2285 (
2286 p_pkg_name => d_pkg_name,
2287 p_procedure_name => d_api_name || '.' || d_position
2288 );
2289 RAISE;
2290 END derive_location_id;
2291
2292 -------------------------------------------------------------------------
2293 --Start of Comments
2294 --Name: derive_terms_id
2295 --Pre-reqs: None
2296 --Modifies:
2297 --Locks:
2298 -- None
2299 --Function:
2300 -- handle the logic to derive terms_id from payment_terms in batch mode
2301 --Parameters:
2302 --IN:
2303 -- p_key
2304 -- identifier in the temp table on the derived result
2305 -- p_index_tbl
2306 -- indexes of the records
2307 -- p_payment_terms_tbl
2308 -- values of payment terms in current batch of records
2309 --IN OUT:
2310 -- x_terms_id_tbl
2311 -- contains the derived result if original value is null
2312 --OUT: None
2313 --Returns:
2314 --Notes:
2315 --Testing:
2316 --End of Comments
2317 ------------------------------------------------------------------------
2318 PROCEDURE derive_terms_id
2319 (
2320 p_key IN po_session_gt.key%TYPE,
2321 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2322 p_payment_terms_tbl IN PO_TBL_VARCHAR100,
2323 x_terms_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2324 ) IS
2325
2326 d_api_name CONSTANT VARCHAR2(30) := 'derive_terms_id';
2327 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2328 d_position NUMBER;
2329
2330 -- tables to store the derived result
2331 l_index_tbl PO_TBL_NUMBER;
2332 l_result_tbl PO_TBL_NUMBER;
2333 BEGIN
2334 d_position := 0;
2335
2336 IF (PO_LOG.d_proc) THEN
2337 PO_LOG.proc_begin(d_module, 'payment terms', p_payment_terms_tbl);
2338 PO_LOG.proc_begin(d_module, 'terms ids', x_terms_id_tbl);
2339 END IF;
2340
2341 FORALL i IN 1..p_index_tbl.COUNT
2342 INSERT INTO po_session_gt(key, num1, num2)
2343 SELECT p_key,
2344 p_index_tbl(i),
2345 term_id
2346 FROM ap_terms
2347 WHERE x_terms_id_tbl(i) IS NULL
2348 AND p_payment_terms_tbl(i) IS NOT NULL
2349 AND name = p_payment_terms_tbl(i)
2350 AND enabled_flag = 'Y'
2351 AND TRUNC(sysdate) between TRUNC(nvl(start_date_active, sysdate))
2352 AND TRUNC(nvl(end_date_active, sysdate));
2353
2354 d_position := 10;
2355
2356 DELETE FROM po_session_gt
2357 WHERE key = p_key
2358 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2359
2360 d_position := 20;
2361
2362 FOR i IN 1..l_index_tbl.COUNT
2363 LOOP
2364 IF (PO_LOG.d_stmt) THEN
2365 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2366 PO_LOG.stmt(d_module, d_position, 'new terms id', l_result_tbl(i));
2367 END IF;
2368
2369 x_terms_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2370 END LOOP;
2371
2372 IF (PO_LOG.d_proc) THEN
2373 PO_LOG.proc_end (d_module);
2374 END IF;
2375
2376 EXCEPTION
2377 WHEN OTHERS THEN
2378 PO_MESSAGE_S.add_exc_msg
2379 (
2380 p_pkg_name => d_pkg_name,
2381 p_procedure_name => d_api_name || '.' || d_position
2382 );
2383 RAISE;
2384 END derive_terms_id;
2385
2386 -------------------------------------------------------------------------
2387 --Start of Comments
2388 --Name: derive_vendor_id
2389 --Pre-reqs: None
2390 --Modifies:
2391 --Locks:
2392 -- None
2393 --Function:
2394 -- handle the logic to derive vendor_id from vendor_name or vendor_num
2395 -- in batch mode
2396 --Parameters:
2397 --IN:
2398 -- p_key
2399 -- identifier in the temp table on the derived result
2400 -- p_index_tbl
2401 -- indexes of the records
2402 -- p_vendor_name_tbl
2403 -- values of vendor name in current batch of records
2404 -- p_vendor_num_tbl
2405 -- values of vendor num in current batch of records
2406 --IN OUT:
2407 -- x_vendor_id_tbl
2408 -- contains the derived result if original value is null
2409 --OUT: None
2410 --Returns:
2411 --Notes:
2412 --Testing:
2413 --End of Comments
2414 ------------------------------------------------------------------------
2415 PROCEDURE derive_vendor_id
2416 (
2417 p_key IN po_session_gt.key%TYPE,
2418 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2419 p_vendor_name_tbl IN PO_TBL_VARCHAR2000,
2420 p_vendor_num_tbl IN PO_TBL_VARCHAR30,
2421 x_vendor_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2422 ) IS
2423
2424 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_id';
2425 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2426 d_position NUMBER;
2427
2428 -- tables to store the derived result
2429 l_index_tbl PO_TBL_NUMBER;
2430 l_result_tbl PO_TBL_NUMBER;
2431
2432 -- variable to hold the current index of the row processed
2433 l_index NUMBER;
2434 BEGIN
2435 d_position := 0;
2436
2437 IF (PO_LOG.d_proc) THEN
2438 PO_LOG.proc_begin(d_module, 'vendor names', p_vendor_name_tbl);
2439 PO_LOG.proc_begin(d_module, 'vendor nums', p_vendor_num_tbl);
2440 PO_LOG.proc_begin(d_module, 'vendor ids', x_vendor_id_tbl);
2441 END IF;
2442
2443 FORALL i IN 1..p_index_tbl.COUNT
2444 INSERT INTO po_session_gt(key, num1, num2)
2445 SELECT p_key,
2446 p_index_tbl(i),
2447 vendor_id
2448 FROM po_vendors
2449 WHERE x_vendor_id_tbl(i) IS NULL
2450 AND (p_vendor_name_tbl(i) IS NOT NULL OR p_vendor_num_tbl(i) IS NOT NULL)
2451 AND (vendor_name = p_vendor_name_tbl(i) OR
2452 segment1 = p_vendor_num_tbl(i));
2453
2454 d_position := 10;
2455
2456 DELETE FROM po_session_gt
2457 WHERE key = p_key
2458 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2459
2460 d_position := 20;
2461
2462 -- There can be 3 types of result from above derivation logic:
2463 -- 1. No vendor_id can be derived: fine, we leave the vendor_id as NULL;
2464 -- 2. One vendor_id can be derived: the value will be set back
2465 -- 3. Two vendor_ids are derived from vendor_name and vendor_num: we
2466 -- should leave vendor_id as null
2467 FOR i IN 1..l_index_tbl.COUNT
2468 LOOP
2469 l_index := l_index_tbl(i);
2470
2471 IF (PO_LOG.d_stmt) THEN
2472 PO_LOG.stmt(d_module, d_position, 'index', l_index);
2473 PO_LOG.stmt(d_module, d_position, 'new vendor id', l_result_tbl(i));
2474 END IF;
2475
2476 IF (x_vendor_id_tbl(l_index) IS NULL) THEN
2477 x_vendor_id_tbl(l_index) := l_result_tbl(i);
2478 ELSE
2479 x_vendor_id_tbl(l_index) := NULL;
2480 END IF;
2481 END LOOP;
2482
2483 IF (PO_LOG.d_proc) THEN
2484 PO_LOG.proc_end (d_module);
2485 END IF;
2486
2487 EXCEPTION
2488 WHEN OTHERS THEN
2489 PO_MESSAGE_S.add_exc_msg
2490 (
2491 p_pkg_name => d_pkg_name,
2492 p_procedure_name => d_api_name || '.' || d_position
2493 );
2494 RAISE;
2495 END derive_vendor_id;
2496
2497 -------------------------------------------------------------------------
2498 --------------------- PRIVATE PROCEDURES --------------------------------
2499 -------------------------------------------------------------------------
2500
2501 -------------------------------------------------------------------------
2502 --Start of Comments
2503 --Name: derive_rate_type_code
2504 --Pre-reqs: None
2505 --Modifies:
2506 --Locks:
2507 -- None
2508 --Function:
2509 -- handle the logic to derive rate_type_code from rate_type in batch mode
2510 --Parameters:
2511 --IN:
2512 -- p_key
2513 -- identifier in the temp table on the derived result
2514 -- p_index_tbl
2515 -- indexes of the records
2516 -- p_rate_type_tbl
2517 -- values of rate type in current batch of records
2518 --IN OUT:
2519 -- x_rate_type_code_tbl
2520 -- contains the derived result if original value is null
2521 --OUT: None
2522 --Returns:
2523 --Notes:
2524 --Testing:
2525 --End of Comments
2526 ------------------------------------------------------------------------
2527 PROCEDURE derive_rate_type_code
2528 (
2529 p_key IN po_session_gt.key%TYPE,
2530 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2531 p_rate_type_tbl IN PO_TBL_VARCHAR30,
2532 x_rate_type_code_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
2533 ) IS
2534
2535 d_api_name CONSTANT VARCHAR2(30) := 'derive_rate_type_code';
2536 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2537 d_position NUMBER;
2538
2539 -- tables to store the derived result
2540 l_index_tbl PO_TBL_NUMBER;
2541 l_result_tbl PO_TBL_VARCHAR30;
2542 BEGIN
2543 d_position := 0;
2544
2545 IF (PO_LOG.d_proc) THEN
2546 PO_LOG.proc_begin(d_module, 'rate type', p_rate_type_tbl);
2547 PO_LOG.proc_begin(d_module, 'rate type code', x_rate_type_code_tbl);
2548 END IF;
2549
2550 FORALL i IN 1..p_index_tbl.COUNT
2551 INSERT INTO po_session_gt(key, num1, char1)
2552 SELECT p_key,
2553 p_index_tbl(i),
2554 conversion_type
2555 FROM gl_daily_conversion_types
2556 WHERE x_rate_type_code_tbl(i) IS NULL
2557 AND p_rate_type_tbl(i) IS NOT NULL
2558 AND user_conversion_type = p_rate_type_tbl(i);
2559
2560 d_position := 10;
2561
2562 DELETE FROM po_session_gt
2563 WHERE key = p_key
2564 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2565
2566 d_position := 20;
2567
2568 FOR i IN 1..l_index_tbl.COUNT
2569 LOOP
2570 IF (PO_LOG.d_stmt) THEN
2571 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2572 PO_LOG.stmt(d_module, d_position, 'new rate type code', l_result_tbl(i));
2573 END IF;
2574
2575 x_rate_type_code_tbl(l_index_tbl(i)) := l_result_tbl(i);
2576 END LOOP;
2577
2578 IF (PO_LOG.d_proc) THEN
2579 PO_LOG.proc_end (d_module);
2580 END IF;
2581
2582 EXCEPTION
2583 WHEN OTHERS THEN
2584 PO_MESSAGE_S.add_exc_msg
2585 (
2586 p_pkg_name => d_pkg_name,
2587 p_procedure_name => d_api_name || '.' || d_position
2588 );
2589 RAISE;
2590 END derive_rate_type_code;
2591
2592 -------------------------------------------------------------------------
2593 --Start of Comments
2594 --Name: derive_agent_id
2595 --Pre-reqs: None
2596 --Modifies:
2597 --Locks:
2598 -- None
2599 --Function:
2600 -- handle the logic to derive agent_id from agent_name in batch mode
2601 --Parameters:
2602 --IN:
2603 -- p_key
2604 -- identifier in the temp table on the derived result
2605 -- p_index_tbl
2606 -- indexes of the records
2607 -- p_agent_name_tbl
2608 -- values of agent name in current batch of records
2609 --IN OUT:
2610 -- x_agent_id_tbl
2611 -- contains the derived result if original value is null
2612 --OUT: None
2613 --Returns:
2614 --Notes:
2615 --Testing:
2616 --End of Comments
2617 ------------------------------------------------------------------------
2618 PROCEDURE derive_agent_id
2619 (
2620 p_key IN po_session_gt.key%TYPE,
2621 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2622 p_agent_name_tbl IN PO_TBL_VARCHAR2000,
2623 x_agent_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2624 ) IS
2625
2626 d_api_name CONSTANT VARCHAR2(30) := 'derive_agent_id';
2627 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2628 d_position NUMBER;
2629
2630 -- tables to store the derived result
2631 l_index_tbl PO_TBL_NUMBER;
2632 l_result_tbl PO_TBL_NUMBER;
2633 BEGIN
2634 d_position := 0;
2635
2636 IF (PO_LOG.d_proc) THEN
2637 PO_LOG.proc_begin(d_module, 'agent name', p_agent_name_tbl);
2638 PO_LOG.proc_begin(d_module, 'agent id', x_agent_id_tbl);
2639 END IF;
2640
2641 FORALL i IN 1..p_index_tbl.COUNT
2642 INSERT INTO po_session_gt(key, num1, num2)
2643 SELECT p_key,
2644 p_index_tbl(i),
2645 employee_id
2646 FROM po_buyers_val_v
2647 WHERE x_agent_id_tbl(i) IS NULL
2648 AND p_agent_name_tbl(i) IS NOT NULL
2649 AND full_name = p_agent_name_tbl(i);
2650
2651 d_position := 10;
2652
2653 DELETE FROM po_session_gt
2654 WHERE key = p_key
2655 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2656
2657 d_position := 20;
2658
2659 FOR i IN 1..l_index_tbl.COUNT
2660 LOOP
2661 IF (PO_LOG.d_stmt) THEN
2662 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2663 PO_LOG.stmt(d_module, d_position, 'new agent id', l_result_tbl(i));
2664 END IF;
2665
2666 x_agent_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2667 END LOOP;
2668
2669 IF (PO_LOG.d_proc) THEN
2670 PO_LOG.proc_end (d_module);
2671 END IF;
2672
2673 EXCEPTION
2674 WHEN OTHERS THEN
2675 PO_MESSAGE_S.add_exc_msg
2676 (
2677 p_pkg_name => d_pkg_name,
2678 p_procedure_name => d_api_name || '.' || d_position
2679 );
2680 END derive_agent_id;
2681
2682 -------------------------------------------------------------------------
2683 --Start of Comments
2684 --Name: derive_vendor_site_id
2685 --Pre-reqs: None
2686 --Modifies:
2687 --Locks:
2688 -- None
2689 --Function:
2690 -- handle the logic to derive vendor_site_id from vendor_site_code
2691 -- and vendor_id in batch mode
2692 --Parameters:
2693 --IN:
2694 -- p_key
2695 -- identifier in the temp table on the derived result
2696 -- p_index_tbl
2697 -- indexes of the records
2698 -- p_vendor_id_tbl
2699 -- values of vendor id in current batch of records
2700 -- p_vendor_site_code_tbl
2701 -- values of vendor site codes in current batch of records
2702 --IN OUT:
2703 -- x_vendor_site_id_tbl
2704 -- contains the derived result if original value is null
2705 --OUT: None
2706 --Returns:
2707 --Notes:
2708 --Testing:
2709 --End of Comments
2710 ------------------------------------------------------------------------
2711 PROCEDURE derive_vendor_site_id
2712 (
2713 p_key IN po_session_gt.key%TYPE,
2714 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2715 p_vendor_id_tbl IN PO_TBL_NUMBER,
2716 p_vendor_site_code_tbl IN PO_TBL_VARCHAR30,
2717 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2718 ) IS
2719
2720 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_site_id';
2721 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2722 d_position NUMBER;
2723
2724 -- tables to store the derived result
2725 l_index_tbl PO_TBL_NUMBER;
2726 l_result_tbl PO_TBL_NUMBER;
2727 BEGIN
2728 d_position := 0;
2729
2730 IF (PO_LOG.d_proc) THEN
2731 PO_LOG.proc_begin(d_module, 'vendor id', p_vendor_id_tbl);
2732 PO_LOG.proc_begin(d_module, 'site code', p_vendor_site_code_tbl);
2733 PO_LOG.proc_begin(d_module, 'site id', x_vendor_site_id_tbl);
2734 END IF;
2735
2736 FORALL i IN 1..p_index_tbl.COUNT
2737 INSERT INTO po_session_gt(key, num1, num2)
2738 SELECT p_key,
2739 p_index_tbl(i),
2740 vendor_site_id
2741 FROM po_supplier_sites_val_v
2742 WHERE x_vendor_site_id_tbl(i) IS NULL
2743 AND p_vendor_site_code_tbl(i) IS NOT NULL
2744 AND p_vendor_id_tbl(i) IS NOT NULL
2745 AND vendor_id = p_vendor_id_tbl(i)
2746 AND vendor_site_code = p_vendor_site_code_tbl(i);
2747
2748 d_position := 10;
2749
2750 DELETE FROM po_session_gt
2751 WHERE key = p_key
2752 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2753
2754 d_position := 20;
2755
2756 FOR i IN 1..l_index_tbl.COUNT
2757 LOOP
2758 IF (PO_LOG.d_stmt) THEN
2759 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2760 PO_LOG.stmt(d_module, d_position, 'new site id', l_result_tbl(i));
2761 END IF;
2762
2763 x_vendor_site_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2764 END LOOP;
2765
2766 IF (PO_LOG.d_proc) THEN
2767 PO_LOG.proc_end (d_module);
2768 END IF;
2769
2770 EXCEPTION
2771 WHEN OTHERS THEN
2772 PO_MESSAGE_S.add_exc_msg
2773 (
2774 p_pkg_name => d_pkg_name,
2775 p_procedure_name => d_api_name || '.' || d_position
2776 );
2777 RAISE;
2778 END derive_vendor_site_id;
2779
2780 -------------------------------------------------------------------------
2781 --Start of Comments
2782 --Name: derive_vendor_contact_id
2783 --Pre-reqs: None
2784 --Modifies:
2785 --Locks:
2786 -- None
2787 --Function:
2788 -- handle the logic to derive vendor_contac_id from vendor_contact
2789 -- and vendor_site_id in batch mode
2790 --Parameters:
2791 --IN:
2792 -- p_key
2793 -- identifier in the temp table on the derived result
2794 -- p_index_tbl
2795 -- indexes of the records
2796 -- p_vendor_site_id_tbl
2797 -- value of vendor site id in current batch mode
2798 -- p_vendor_contact_tbl
2799 -- values of vendor contact in current batch of records
2800 --IN OUT:
2801 -- x_vendor_contact_id_tbl
2802 -- contains the derived result if original value is null
2803 --OUT: None
2804 --Returns:
2805 --Notes:
2806 --Testing:
2807 --End of Comments
2808 ------------------------------------------------------------------------
2809 PROCEDURE derive_vendor_contact_id
2810 (
2811 p_key IN po_session_gt.key%TYPE,
2812 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2813 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
2814 p_vendor_contact_tbl IN PO_TBL_VARCHAR2000,
2815 x_vendor_contact_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2816 ) IS
2817
2818 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_contact_id';
2819 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2820 d_position NUMBER;
2821
2822 -- tables to store the derived result
2823 l_index_tbl PO_TBL_NUMBER;
2824 l_result_tbl PO_TBL_NUMBER;
2825 BEGIN
2826 d_position := 0;
2827
2828 IF (PO_LOG.d_proc) THEN
2829 PO_LOG.proc_begin(d_module, 'site id', p_vendor_site_id_tbl);
2830 PO_LOG.proc_begin(d_module, 'contact', p_vendor_contact_tbl);
2831 PO_LOG.proc_begin(d_module, 'contact id', x_vendor_contact_id_tbl);
2832 END IF;
2833
2834 FORALL i IN 1..p_index_tbl.COUNT
2835 INSERT INTO po_session_gt(key, num1, num2)
2836 SELECT p_key,
2837 p_index_tbl(i),
2838 vendor_contact_id
2839 FROM po_vendor_contacts
2840 WHERE x_vendor_contact_id_tbl(i) IS NULL
2841 AND p_vendor_contact_tbl(i) IS NOT NULL
2842 AND p_vendor_site_id_tbl(i) IS NOT NULL
2843 AND last_name||' '||first_name = p_vendor_contact_tbl(i)
2844 AND vendor_site_id = p_vendor_site_id_tbl(i);
2845
2846 d_position := 10;
2847
2848 DELETE FROM po_session_gt
2849 WHERE key = p_key
2850 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2851
2852 d_position := 20;
2853
2854 FOR i IN 1..l_index_tbl.COUNT
2855 LOOP
2856 IF (PO_LOG.d_stmt) THEN
2857 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2858 PO_LOG.stmt(d_module, d_position, 'new contact id', l_result_tbl(i));
2859 END IF;
2860
2861 x_vendor_contact_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2862 END LOOP;
2863
2864 IF (PO_LOG.d_proc) THEN
2865 PO_LOG.proc_end (d_module);
2866 END IF;
2867
2868 EXCEPTION
2869 WHEN OTHERS THEN
2870 PO_MESSAGE_S.add_exc_msg
2871 (
2872 p_pkg_name => d_pkg_name,
2873 p_procedure_name => d_api_name || '.' || d_position
2874 );
2875 RAISE;
2876 END derive_vendor_contact_id;
2877
2878
2879 -------------------------------------------------------------------------
2880 --Start of Comments
2881 --Name: derive_vendor_contact_id
2882 --Pre-reqs: None
2883 --Modifies:
2884 --Locks:
2885 -- None
2886 --Function:
2887 -- handle the logic to derive vendor_contac_id from vendor_contact
2888 -- and vendor_site_id in batch mode
2889 --Parameters:
2890 --IN:
2891 -- p_key
2892 -- identifier in the temp table on the derived result
2893 -- p_index_tbl
2894 -- indexes of the records
2895 -- p_vendor_site_id_tbl
2896 -- value of vendor site id in current batch mode
2897 -- p_vendor_contact_tbl
2898 -- values of vendor contact in current batch of records
2899 --IN OUT:
2900 -- x_vendor_contact_id_tbl
2901 -- contains the derived result if original value is null
2902 --OUT: None
2903 --Returns:
2904 --Notes:
2905 --Testing:
2906 --End of Comments
2907 ------------------------------------------------------------------------
2908 PROCEDURE derive_style_id
2909 (
2910 p_key IN po_session_gt.key%TYPE,
2911 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
2912 p_style_display_name_tbl IN PO_TBL_VARCHAR2000,
2913 x_style_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
2914 ) IS
2915
2916 d_api_name CONSTANT VARCHAR2(30) := 'derive_style_id';
2917 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2918 d_position NUMBER;
2919
2920 -- tables to store the derived result
2921 l_index_tbl PO_TBL_NUMBER;
2922 l_result_tbl PO_TBL_NUMBER;
2923 BEGIN
2924 d_position := 0;
2925
2926 IF (PO_LOG.d_proc) THEN
2927 PO_LOG.proc_begin(d_module, 'x_style_id_tbl', x_style_id_tbl);
2928 PO_LOG.proc_begin(d_module, 'p_style_display_name_tbl', p_style_display_name_tbl);
2929 END IF;
2930
2931 FORALL i IN 1..p_index_tbl.COUNT
2932 INSERT INTO po_session_gt(key, num1, num2)
2933 SELECT p_key,
2934 p_index_tbl(i),
2935 style_id
2936 FROM po_doc_style_lines_tl pds
2937 WHERE x_style_id_tbl(i) IS NULL AND
2938 pds.display_name = p_style_display_name_tbl(i) AND
2939 pds.LANGUAGE = USERENV('LANG');
2940
2941 d_position := 10;
2942
2943 DELETE FROM po_session_gt
2944 WHERE key = p_key
2945 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
2946
2947 d_position := 20;
2948
2949 FOR i IN 1..l_index_tbl.COUNT
2950 LOOP
2951 IF (PO_LOG.d_stmt) THEN
2952 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
2953 PO_LOG.stmt(d_module, d_position, 'new style id', l_result_tbl(i));
2954 END IF;
2955
2956 x_style_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
2957 END LOOP;
2958
2959 IF (PO_LOG.d_proc) THEN
2960 PO_LOG.proc_end (d_module);
2961 END IF;
2962
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 PO_MESSAGE_S.add_exc_msg
2966 (
2967 p_pkg_name => d_pkg_name,
2968 p_procedure_name => d_api_name || '.' || d_position
2969 );
2970 RAISE;
2971
2972 END derive_style_id;
2973
2974 -------------------------------------------------------------------------
2975 --Start of Comments
2976 --Name: derive_from_header_id
2977 --Pre-reqs: None
2978 --Modifies:
2979 --Locks:
2980 -- None
2981 --Function:
2982 -- handle the logic to derive from_header_id from from_rfq_num in batch mode
2983 --Parameters:
2984 --IN:
2985 -- p_key
2986 -- identifier in the temp table on the derived result
2987 -- p_index_tbl
2988 -- indexes of the records
2989 -- p_from_rfq_num_tbl
2990 -- values of from quotation document number in current batch of records
2991 --IN OUT:
2992 -- x_from_header_id_tbl
2993 -- contains the derived result if original value is null
2994 --OUT: None
2995 --Returns:
2996 --Notes:
2997 --Testing:
2998 --End of Comments
2999 ------------------------------------------------------------------------
3000 PROCEDURE derive_from_header_id
3001 (
3002 p_key IN po_session_gt.key%TYPE,
3003 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3004 p_from_rfq_num_tbl IN PO_TBL_VARCHAR30,
3005 x_from_header_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3006 ) IS
3007
3008 d_api_name CONSTANT VARCHAR2(30) := 'derive_from_header_id';
3009 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3010 d_position NUMBER;
3011
3012 -- tables to store the derived result
3013 l_index_tbl PO_TBL_NUMBER;
3014 l_result_tbl PO_TBL_NUMBER;
3015 BEGIN
3016 d_position := 0;
3017
3018 IF (PO_LOG.d_proc) THEN
3019 PO_LOG.proc_begin(d_module, 'from rfq num', p_from_rfq_num_tbl);
3020 PO_LOG.proc_begin(d_module, 'from header id', x_from_header_id_tbl);
3021 END IF;
3022
3023 FORALL i IN 1..p_index_tbl.COUNT
3024 INSERT INTO po_session_gt(key, num1, num2)
3025 SELECT p_key,
3026 p_index_tbl(i),
3027 po_header_id
3028 FROM po_headers
3029 WHERE x_from_header_id_tbl(i) IS NULL
3030 AND p_from_rfq_num_tbl(i) IS NOT NULL
3031 AND segment1 = p_from_rfq_num_tbl(i)
3032 AND type_lookup_code = 'RFQ'; -- PO_PDOI_CONSTANTS.g_DOC_TYPE_RFQ;
3033
3034 d_position := 10;
3035
3036 DELETE FROM po_session_gt
3037 WHERE key = p_key
3038 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3039
3040 d_position := 20;
3041
3042 FOR i IN 1..l_index_tbl.COUNT
3043 LOOP
3044 IF (PO_LOG.d_stmt) THEN
3045 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3046 PO_LOG.stmt(d_module, d_position, 'new from header id', l_result_tbl(i));
3047 END IF;
3048
3049 x_from_header_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3050 END LOOP;
3051
3052 IF (PO_LOG.d_proc) THEN
3053 PO_LOG.proc_end (d_module);
3054 END IF;
3055
3056 EXCEPTION
3057 WHEN OTHERS THEN
3058 PO_MESSAGE_S.add_exc_msg
3059 (
3060 p_pkg_name => d_pkg_name,
3061 p_procedure_name => d_api_name || '.' || d_position
3062 );
3063 RAISE;
3064 END derive_from_header_id;
3065
3066 -------------------------------------------------------------------------
3067 --Start of Comments
3068 --Name: default_info_from_vendor
3069 --Pre-reqs: None
3070 --Modifies:
3071 --Locks:
3072 -- None
3073 --Function:
3074 -- handle the logic to default attribute values from vendor specification
3075 -- in a batch mode
3076 --Parameters:
3077 --IN:
3078 -- p_key
3079 -- identifier in the temp table on the derived result
3080 -- p_index_tbl
3081 -- indexes of the records
3082 -- p_vendor_id_tbl
3083 -- values of vendor id in current batch of records
3084 --IN OUT: None
3085 --OUT:
3086 -- x_invoice_currency_code_tbl
3087 -- values of invoice currency code defined on vendor level
3088 -- x_terms_id_tbl
3089 -- values of terms id defined on vendor level
3090 --Returns:
3091 --Notes:
3092 --Testing:
3093 --End of Comments
3094 ------------------------------------------------------------------------
3095 PROCEDURE default_info_from_vendor
3096 (
3097 p_key IN po_session_gt.key%TYPE,
3098 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3099 p_vendor_id_tbl IN PO_TBL_NUMBER,
3100 x_invoice_currency_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
3101 x_terms_id_tbl OUT NOCOPY PO_TBL_NUMBER
3102 ) IS
3103
3104 d_api_name CONSTANT VARCHAR2(30) := 'default_info_from_vendor';
3105 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3106 d_position NUMBER;
3107
3108 -- variables to hold values read from vendor definition
3109 l_index_tbl PO_TBL_NUMBER;
3110 l_currency_code_tbl PO_TBL_VARCHAR30;
3111 l_terms_id_tbl PO_TBL_NUMBER;
3112
3113 -- variable to hold index of the current processing row
3114 l_index NUMBER;
3115
3116 BEGIN
3117 d_position := 0;
3118
3119 IF (PO_LOG.d_proc) THEN
3120 PO_LOG.proc_begin(d_module, 'vendor ids', p_vendor_id_tbl);
3121 END IF;
3122
3123 -- Initialize OUT parameters
3124
3125 -- <Bug 4546121: Supplier TCA conversion>
3126 -- The following columns are being obsoleted from PO_VENDORS level
3127 --x_fob_tbl := PO_TBL_VARCHAR30();
3128 --x_freight_carrier_tbl := PO_TBL_VARCHAR30();
3129 --x_freight_term_tbl := PO_TBL_VARCHAR30();
3130 --x_ship_to_loc_id_tbl := PO_TBL_NUMBER();
3131 --x_bill_to_loc_id_tbl := PO_TBL_NUMBER();
3132
3133 x_invoice_currency_code_tbl := PO_TBL_VARCHAR30();
3134 x_terms_id_tbl := PO_TBL_NUMBER();
3135
3136 x_invoice_currency_code_tbl.EXTEND(p_index_tbl.COUNT);
3137 x_terms_id_tbl.EXTEND(p_index_tbl.COUNT);
3138
3139 FORALL i IN 1..p_index_tbl.COUNT
3140 INSERT INTO po_session_gt(
3141 key, num1, char1, num2)
3142 SELECT p_key,
3143 p_index_tbl(i),
3144 invoice_currency_code,
3145 terms_id
3146 FROM po_vendors
3147 WHERE vendor_id = p_vendor_id_tbl(i);
3148
3149 d_position := 10;
3150
3151 DELETE FROM po_session_gt
3152 WHERE key = p_key
3153 RETURNING num1, char1, num2
3154 BULK COLLECT INTO
3155 l_index_tbl,
3156 l_currency_code_tbl,
3157 l_terms_id_tbl;
3158
3159 d_position := 20;
3160
3161 IF (PO_LOG.d_stmt) THEN
3162 PO_LOG.stmt(d_module, d_position, 'l_index_tbl.COUNT', l_index_tbl.COUNT);
3163 END IF;
3164
3165 FOR i IN 1..l_index_tbl.COUNT
3166 LOOP
3167 l_index := l_index_tbl(i);
3168
3169 IF (PO_LOG.d_stmt) THEN
3170 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3171 PO_LOG.stmt(d_module, d_position, 'new currency', l_currency_code_tbl(i));
3172 PO_LOG.stmt(d_module, d_position, 'new terms id', l_terms_id_tbl(i));
3173 END IF;
3174
3175 x_invoice_currency_code_tbl(l_index) := l_currency_code_tbl(i);
3176 x_terms_id_tbl(l_index) := l_terms_id_tbl(i);
3177 END LOOP;
3178
3179 d_position := 30;
3180
3181 IF (PO_LOG.d_proc) THEN
3182 PO_LOG.proc_end (d_module);
3183 END IF;
3184
3185 EXCEPTION
3186 WHEN OTHERS THEN
3187 PO_MESSAGE_S.add_exc_msg
3188 (
3189 p_pkg_name => d_pkg_name,
3190 p_procedure_name => d_api_name || '.' || d_position
3191 );
3192 RAISE;
3193 END default_info_from_vendor;
3194
3195 -------------------------------------------------------------------------
3196 --Start of Comments
3197 --Name: default_info_from_vendor_site
3198 --Pre-reqs: None
3199 --Modifies:
3200 --Locks:
3201 -- None
3202 --Function:
3203 -- handle the logic to default attribute values from vendor site
3204 -- specification in a batch mode
3205 --Parameters:
3206 --IN:
3207 -- p_key
3208 -- identifier in the temp table on the derived result
3209 -- p_index_tbl
3210 -- indexes of the records
3211 -- p_vendor_id_tbl
3212 -- values of vendor id in current batch of records
3213 --IN OUT:
3214 -- x_vendor_site_id_tbl
3215 -- if original value is empty, we try to default vendor site id
3216 -- from vendor id first; then perform the default logic of other
3217 -- attributes based on new value of vendor site id--
3218 --OUT:
3219 -- x_fob_tbl
3220 -- values of fob defined on vendor site level
3221 -- x_freight_carrier_tbl
3222 -- values of freight carrier defined on site level
3223 -- x_freight_term_tbl
3224 -- values of freight term defined on site level
3225 -- x_ship_to_loc_id_tbl
3226 -- values of ship to location id defined on site level
3227 -- x_bill_to_loc_id_tbl
3228 -- values of bill to location id defined on site level
3229 -- x_invoice_currency_code_tbl
3230 -- values of invoice currency code defined on site level
3231 -- x_terms_id_tbl
3232 -- values of terms id defined on site level
3233 -- x_shipping_control_tbl
3234 -- values of shipping control defined on site level
3235 -- x_pay_on_code_tbl
3236 -- values of pay on code defined on site level
3237 --Returns:
3238 --Notes:
3239 --Testing:
3240 --End of Comments
3241 ------------------------------------------------------------------------
3242 PROCEDURE default_info_from_vendor_site
3243 (
3244 p_key IN po_session_gt.key%TYPE,
3245 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3246 p_vendor_id_tbl IN PO_TBL_NUMBER,
3247 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER,
3248 x_fob_tbl OUT NOCOPY PO_TBL_VARCHAR30,
3249 x_freight_carrier_tbl OUT NOCOPY PO_TBL_VARCHAR30,
3250 x_freight_term_tbl OUT NOCOPY PO_TBL_VARCHAR30,
3251 x_ship_to_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
3252 x_bill_to_loc_id_tbl OUT NOCOPY PO_TBL_NUMBER,
3253 x_invoice_currency_code_tbl OUT NOCOPY PO_TBL_VARCHAR30,
3254 x_terms_id_tbl OUT NOCOPY PO_TBL_NUMBER,
3255 x_shipping_control_tbl OUT NOCOPY PO_TBL_VARCHAR30,
3256 x_pay_on_code_tbl OUT NOCOPY PO_TBL_VARCHAR30
3257 ) IS
3258
3259 d_api_name CONSTANT VARCHAR2(30) := 'default_info_from_vendor_site';
3260 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3261 d_position NUMBER;
3262
3263 -- variables to hold values read from vendor definition
3264 l_index_tbl PO_TBL_NUMBER;
3265 l_vendor_site_id_tbl PO_TBL_NUMBER;
3266 l_fob_tbl PO_TBL_VARCHAR30;
3267 l_freight_carrier_tbl PO_TBL_VARCHAR30;
3268 l_freight_term_tbl PO_TBL_VARCHAR30;
3269 l_ship_to_loc_id_tbl PO_TBL_NUMBER;
3270 l_bill_to_loc_id_tbl PO_TBL_NUMBER;
3271 l_currency_code_tbl PO_TBL_VARCHAR30;
3272 l_terms_id_tbl PO_TBL_NUMBER;
3273 l_shipping_control_tbl PO_TBL_VARCHAR30;
3274 l_pay_on_code_tbl PO_TBL_VARCHAR30;
3275
3276 -- variable to hold index of the current processing row
3277 l_index NUMBER;
3278
3279 BEGIN
3280 d_position := 0;
3281
3282 IF (PO_LOG.d_proc) THEN
3283 PO_LOG.proc_begin(d_module, 'vendor ids', p_vendor_id_tbl);
3284 PO_LOG.proc_begin(d_module, 'vendor site ids', x_vendor_site_id_tbl);
3285 END IF;
3286
3287 x_fob_tbl := PO_TBL_VARCHAR30();
3288 x_freight_carrier_tbl := PO_TBL_VARCHAR30();
3289 x_freight_term_tbl := PO_TBL_VARCHAR30();
3290 x_ship_to_loc_id_tbl := PO_TBL_NUMBER();
3291 x_bill_to_loc_id_tbl := PO_TBL_NUMBER();
3292 x_invoice_currency_code_tbl := PO_TBL_VARCHAR30();
3293 x_terms_id_tbl := PO_TBL_NUMBER();
3294 x_shipping_control_tbl := PO_TBL_VARCHAR30();
3295 x_pay_on_code_tbl := PO_TBL_VARCHAR30();
3296
3297 x_fob_tbl.EXTEND(p_index_tbl.COUNT);
3298 x_freight_carrier_tbl.EXTEND(p_index_tbl.COUNT);
3299 x_freight_term_tbl.EXTEND(p_index_tbl.COUNT);
3300 x_ship_to_loc_id_tbl.EXTEND(p_index_tbl.COUNT);
3301 x_bill_to_loc_id_tbl.EXTEND(p_index_tbl.COUNT);
3302 x_invoice_currency_code_tbl.EXTEND(p_index_tbl.COUNT);
3303 x_terms_id_tbl.EXTEND(p_index_tbl.COUNT);
3304 x_shipping_control_tbl.EXTEND(p_index_tbl.COUNT);
3305 x_pay_on_code_tbl.EXTEND(p_index_tbl.COUNT);
3306
3307 d_position := 10;
3308
3309 -- default vendor_site_id if it is empty
3310 FORALL i IN 1..p_index_tbl.COUNT
3311 INSERT INTO po_session_gt(key, num1, num2, num3)
3312 SELECT p_key,
3313 p_index_tbl(i),
3314 min(vendor_site_id),
3315 vendor_id
3316 FROM po_vendor_sites
3317 WHERE p_vendor_id_tbl(i) IS NOT NULL
3318 AND x_vendor_site_id_tbl(i) IS NULL
3319 AND vendor_id = p_vendor_id_tbl(i)
3320 AND purchasing_site_flag = 'Y'
3321 AND (sysdate) < nvl(inactive_date, TRUNC(sysdate + 1))
3322 AND DECODE(PO_PDOI_PARAMS.g_request.document_type,
3323 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION, 'N',
3324 NVL(rfq_only_site_flag, 'N')) <> 'Y'
3325 GROUP BY vendor_id
3326 HAVING count(vendor_site_id) = 1;
3327
3328 d_position := 20;
3329
3330 DELETE FROM po_session_gt
3331 WHERE key = p_key
3332 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_vendor_site_id_tbl;
3333
3334 FOR i IN 1..l_index_tbl.COUNT
3335 LOOP
3336 IF (PO_LOG.d_stmt) THEN
3337 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3338 PO_LOG.stmt(d_module, d_position, 'new site id', l_vendor_site_id_tbl(i));
3339 END IF;
3340
3341 x_vendor_site_id_tbl(l_index_tbl(i)) := l_vendor_site_id_tbl(i);
3342 END LOOP;
3343
3344 d_position := 30;
3345
3346 -- default other attributes from site definition
3347 -- to do (add char6 to gt table?)
3348 FORALL i IN 1..p_index_tbl.COUNT
3349 INSERT INTO po_session_gt(
3350 key, num1, char1, char2, char3, num2, num3, char4, num4, char5, char6)
3351 SELECT p_key,
3352 p_index_tbl(i),
3353 fob_lookup_code,
3354 ship_via_lookup_code,
3355 freight_terms_lookup_code,
3356 ship_to_location_id,
3357 bill_to_location_id,
3358 invoice_currency_code,
3359 terms_id,
3360 shipping_control,
3361 Decode(pay_on_code, --Bug 13461573
3362 'RECEIPT','RECEIPT',
3363 'RECEIPT_AND_USE','RECEIPT',
3364 NULL ) pay_on_code
3365 FROM po_vendor_sites_all
3366 WHERE vendor_site_id = x_vendor_site_id_tbl(i);
3367
3368 d_position := 40;
3369
3370 DELETE FROM po_session_gt
3371 WHERE key = p_key
3372 RETURNING num1, char1, char2, char3, num2, num3, char4, num4, char5, char6
3373 BULK COLLECT INTO
3374 l_index_tbl,
3375 l_fob_tbl,
3376 l_freight_carrier_tbl,
3377 l_freight_term_tbl,
3378 l_ship_to_loc_id_tbl,
3379 l_bill_to_loc_id_tbl,
3380 l_currency_code_tbl,
3381 l_terms_id_tbl,
3382 l_shipping_control_tbl,
3383 l_pay_on_code_tbl;
3384
3385 d_position := 50;
3386
3387 FOR i IN 1..l_index_tbl.COUNT
3388 LOOP
3389 l_index := l_index_tbl(i);
3390
3391 IF (PO_LOG.d_stmt) THEN
3392 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3393 PO_LOG.stmt(d_module, d_position, 'new fob', l_fob_tbl(i));
3394 PO_LOG.stmt(d_module, d_position, 'new freight carrier', l_freight_carrier_tbl(i));
3395 PO_LOG.stmt(d_module, d_position, 'new freight term', l_freight_term_tbl(i));
3396 PO_LOG.stmt(d_module, d_position, 'new ship_to loc id', l_ship_to_loc_id_tbl(i));
3397 PO_LOG.stmt(d_module, d_position, 'new bill_to loc id', l_bill_to_loc_id_tbl(i));
3398 PO_LOG.stmt(d_module, d_position, 'new currency', l_currency_code_tbl(i));
3399 PO_LOG.stmt(d_module, d_position, 'new terms id', l_terms_id_tbl(i));
3400 PO_LOG.stmt(d_module, d_position, 'new shipping control', l_shipping_control_tbl(i));
3401 PO_LOG.stmt(d_module, d_position, 'new pay on code', l_pay_on_code_tbl(i));
3402 END IF;
3403
3404 x_fob_tbl(l_index) := l_fob_tbl(i);
3405 x_freight_carrier_tbl(l_index) := l_freight_carrier_tbl(i);
3406 x_freight_term_tbl(l_index) := l_freight_term_tbl(i);
3407 x_ship_to_loc_id_tbl(l_index) := l_ship_to_loc_id_tbl(i);
3408 x_bill_to_loc_id_tbl(l_index) := l_bill_to_loc_id_tbl(i);
3409 x_invoice_currency_code_tbl(l_index) := l_currency_code_tbl(i);
3410 x_terms_id_tbl(l_index) := l_terms_id_tbl(i);
3411 x_shipping_control_tbl(l_index) := l_shipping_control_tbl(i);
3412 x_pay_on_code_tbl(l_index) := l_pay_on_code_tbl(i);
3413 END LOOP;
3414
3415 IF (PO_LOG.d_proc) THEN
3416 PO_LOG.proc_end (d_module);
3417 END IF;
3418
3419 EXCEPTION
3420 WHEN OTHERS THEN
3421 PO_MESSAGE_S.add_exc_msg
3422 (
3423 p_pkg_name => d_pkg_name,
3424 p_procedure_name => d_api_name || '.' || d_position
3425 );
3426 RAISE;
3427 END default_info_from_vendor_site;
3428
3429 -------------------------------------------------------------------------
3430 --Start of Comments
3431 --Name: default_vendor_contact
3432 --Pre-reqs: None
3433 --Modifies:
3434 --Locks:
3435 -- None
3436 --Function:
3437 -- handle the logic to default vendor contact from vendor site
3438 -- in a batch mode; Vendor contact can be defaulted only when
3439 -- there is exactly one contact defined for the specific site
3440 --Parameters:
3441 --IN:
3442 -- p_key
3443 -- identifier in the temp table on the derived result
3444 -- p_index_tbl
3445 -- indexes of the records
3446 -- p_vendor_site_id_tbl
3447 -- values of vendor site id in current batch of records
3448 --IN OUT:
3449 -- x_vendor_contact_id_tbl
3450 -- values of vendor contact id in current batch of records;
3451 -- defaulted results will be saved here
3452 --OUT: None
3453 --Returns:
3454 --Notes:
3455 --Testing:
3456 --End of Comments
3457 ------------------------------------------------------------------------
3458 PROCEDURE default_vendor_contact
3459 (
3460 p_key IN po_session_gt.key%TYPE,
3461 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3462 p_vendor_site_id_tbl IN PO_TBL_NUMBER,
3463 x_vendor_contact_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
3464 ) IS
3465
3466 d_api_name CONSTANT VARCHAR2(30) := 'default_vendor_contact';
3467 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3468 d_position NUMBER;
3469
3470 -- variables to hold defaulted results
3471 l_index_tbl PO_TBL_NUMBER;
3472 l_result_tbl PO_TBL_NUMBER;
3473
3474 BEGIN
3475 d_position := 0;
3476
3477 IF (PO_LOG.d_proc) THEN
3478 PO_LOG.proc_begin(d_module, 'site ids', p_vendor_site_id_tbl);
3479 PO_LOG.proc_begin(d_module, 'contact ids', x_vendor_contact_id_tbl);
3480 END IF;
3481
3482 -- select contact id from vendor_site table if there is only
3483 -- one contact defined on that site
3484 FORALL i IN 1..p_index_tbl.COUNT
3485 INSERT INTO po_session_gt(key, num1, num2, num3)
3486 SELECT p_key,
3487 p_index_tbl(i),
3488 max(vendor_contact_id),
3489 vendor_site_id
3490 FROM po_vendor_contacts
3491 WHERE p_vendor_site_id_tbl(i) IS NOT NULL
3492 AND x_vendor_contact_id_tbl(i) IS NULL
3493 AND vendor_site_id = p_vendor_site_id_tbl(i)
3494 AND TRUNC(sysdate) < NVL(inactive_date, TRUNC(sysdate + 1))
3495 GROUP BY vendor_site_id
3496 HAVING count(vendor_contact_id) = 1;
3497
3498 d_position := 10;
3499
3500 DELETE FROM po_session_gt
3501 WHERE key = p_key
3502 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3503
3504 d_position := 20;
3505
3506 FOR i IN 1..l_index_tbl.COUNT
3507 LOOP
3508 IF (PO_LOG.d_stmt) THEN
3509 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3510 PO_LOG.stmt(d_module, d_position, 'new contact id', l_result_tbl(i));
3511 END IF;
3512
3513 x_vendor_contact_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
3514 END LOOP;
3515
3516 IF (PO_LOG.d_proc) THEN
3517 PO_LOG.proc_end (d_module);
3518 END IF;
3519
3520 EXCEPTION
3521 WHEN OTHERS THEN
3522 PO_MESSAGE_S.add_exc_msg
3523 (
3524 p_pkg_name => d_pkg_name,
3525 p_procedure_name => d_api_name || '.' || d_position
3526 );
3527 RAISE;
3528 END default_vendor_contact;
3529
3530 -------------------------------------------------------------------------
3531 --Start of Comments
3532 --Name: default_dist_attributes
3533 --Pre-reqs: None
3534 --Modifies:
3535 --Locks:
3536 -- None
3537 --Function:
3538 -- handle the logic to default distribution attributes for Blanket
3539 -- if encumbrance is required for the document;
3540 -- that is, x_headers.encumbrance_required_flag = 'Y'
3541 --Parameters:
3542 --IN: None
3543 --IN OUT:
3544 -- x_headers
3545 -- variable to hold all the header attribute values in one batch;
3546 -- derivation source and result are both placed inside the variable
3547 --OUT: None
3548 --Returns:
3549 --Notes:
3550 --Testing:
3551 --End of Comments
3552 ------------------------------------------------------------------------
3553 PROCEDURE default_dist_attributes
3554 (
3555 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
3556 ) IS
3557
3558 d_api_name CONSTANT VARCHAR2(30) := 'default_dist_attributes';
3559 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3560 d_position NUMBER;
3561
3562 BEGIN
3563 d_position := 0;
3564
3565 IF (PO_LOG.d_proc) THEN
3566 PO_LOG.proc_begin(d_module);
3567 END IF;
3568
3569
3570 FOR i IN 1..x_headers.rec_count
3571 LOOP
3572 d_position := 10;
3573
3574 IF (x_headers.encumbrance_required_flag_tbl(i) = 'Y') THEN
3575 IF (PO_LOG.d_stmt) THEN
3576 PO_LOG.stmt(d_module, d_position, 'distribution row created for encumbrance');
3577 PO_LOG.stmt(d_module, d_position, 'index', i);
3578 END IF;
3579
3580 -- default po_distribution_id
3581 x_headers.po_dist_id_tbl(i) := PO_PDOI_MAINPROC_UTL_PVT.get_next_dist_id;
3582
3583 IF (PO_LOG.d_stmt) THEN
3584 PO_LOG.stmt(d_module, d_position, 'new dist id', x_headers.po_dist_id_tbl(i));
3585 END IF;
3586
3587 -- default gl_encumbered_date and gl_encumbered_period
3588 IF (x_headers.gl_encumbered_date_tbl(i) IS NULL) THEN
3589 x_headers.gl_encumbered_date_tbl(i) := sysdate;
3590 END IF;
3591
3592 d_position := 20;
3593
3594 PO_PERIODS_SV.get_period_name
3595 (
3596 x_sob_id => PO_PDOI_PARAMS.g_sys.sob_id,
3597 x_gl_date => x_headers.gl_encumbered_date_tbl(i),
3598 x_gl_period => x_headers.gl_encumbered_period_tbl(i)
3599 );
3600
3601 d_position := 30;
3602
3603 -- default budget account id
3604 IF (x_headers.budget_account_id_tbl(i) IS NULL) THEN
3605 PO_PDOI_DIST_PROCESS_PVT.derive_account_id
3606 ( p_account_number => x_headers.budget_account_tbl(i),
3607 p_chart_of_accounts_id => PO_PDOI_PARAMS.g_sys.coa_id,
3608 p_account_segment1 => x_headers.budget_account_segment1_tbl(i),
3609 p_account_segment2 => x_headers.budget_account_segment2_tbl(i),
3610 p_account_segment3 => x_headers.budget_account_segment3_tbl(i),
3611 p_account_segment4 => x_headers.budget_account_segment4_tbl(i),
3612 p_account_segment5 => x_headers.budget_account_segment5_tbl(i),
3613 p_account_segment6 => x_headers.budget_account_segment6_tbl(i),
3614 p_account_segment7 => x_headers.budget_account_segment7_tbl(i),
3615 p_account_segment8 => x_headers.budget_account_segment8_tbl(i),
3616 p_account_segment9 => x_headers.budget_account_segment9_tbl(i),
3617 p_account_segment10 => x_headers.budget_account_segment10_tbl(i),
3618 p_account_segment11 => x_headers.budget_account_segment11_tbl(i),
3619 p_account_segment12 => x_headers.budget_account_segment12_tbl(i),
3620 p_account_segment13 => x_headers.budget_account_segment13_tbl(i),
3621 p_account_segment14 => x_headers.budget_account_segment14_tbl(i),
3622 p_account_segment15 => x_headers.budget_account_segment15_tbl(i),
3623 p_account_segment16 => x_headers.budget_account_segment16_tbl(i),
3624 p_account_segment17 => x_headers.budget_account_segment17_tbl(i),
3625 p_account_segment18 => x_headers.budget_account_segment18_tbl(i),
3626 p_account_segment19 => x_headers.budget_account_segment19_tbl(i),
3627 p_account_segment20 => x_headers.budget_account_segment20_tbl(i),
3628 p_account_segment21 => x_headers.budget_account_segment21_tbl(i),
3629 p_account_segment22 => x_headers.budget_account_segment22_tbl(i),
3630 p_account_segment23 => x_headers.budget_account_segment23_tbl(i),
3631 p_account_segment24 => x_headers.budget_account_segment24_tbl(i),
3632 p_account_segment25 => x_headers.budget_account_segment25_tbl(i),
3633 p_account_segment26 => x_headers.budget_account_segment26_tbl(i),
3634 p_account_segment27 => x_headers.budget_account_segment27_tbl(i),
3635 p_account_segment28 => x_headers.budget_account_segment28_tbl(i),
3636 p_account_segment29 => x_headers.budget_account_segment29_tbl(i),
3637 p_account_segment30 => x_headers.budget_account_segment30_tbl(i),
3638 x_account_id => x_headers.budget_account_id_tbl(i)
3639 );
3640
3641 IF (PO_LOG.d_stmt) THEN
3642 PO_LOG.stmt(d_module, d_position, 'default budget account id',
3643 x_headers.budget_account_id_tbl(i));
3644 END IF;
3645 END IF;
3646 END IF;
3647 END LOOP;
3648
3649 IF (PO_LOG.d_proc) THEN
3650 PO_LOG.proc_end (d_module);
3651 END IF;
3652
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655 PO_MESSAGE_S.add_exc_msg
3656 (
3657 p_pkg_name => d_pkg_name,
3658 p_procedure_name => d_api_name || '.' || d_position
3659 );
3660 RAISE;
3661 END default_dist_attributes;
3662
3663 -----------------------------------------------------------------------
3664 --Start of Comments
3665 --Name: populate_error_flag
3666 --Function:
3667 -- corresponding value in error_flag_tbl will be set with value FND_API.G_FALSE.
3668 --Parameters:
3669 --IN:
3670 --x_results
3671 -- The validation results that contains the errored line information.
3672 --IN OUT:
3673 --x_headers
3674 -- The record contains the values to be validated.
3675 -- If there is error(s) on any attribute of the price differential row,
3676 -- corresponding value in error_flag_tbl will be set with value
3677 -- FND_API.g_TRUE.
3678 --OUT:
3679 --End of Comments
3680 ------------------------------------------------------------------------
3681 PROCEDURE populate_error_flag
3682 (
3683 x_results IN po_validation_results_type,
3684 x_headers IN OUT NOCOPY PO_PDOI_TYPES.headers_rec_type
3685 ) IS
3686
3687 d_api_name CONSTANT VARCHAR2(30) := 'populate_error_flag';
3688 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3689 d_position NUMBER;
3690
3691 l_index_tbl DBMS_SQL.number_table;
3692
3693 BEGIN
3694 d_position := 0;
3695
3696 IF (PO_LOG.d_proc) THEN
3697 PO_LOG.proc_begin(d_module);
3698 END IF;
3699
3700 FOR i IN 1 .. x_headers.rec_count LOOP
3701 l_index_tbl(x_headers.intf_header_id_tbl(i)) := i;
3702 END LOOP;
3703
3704 d_position := 10;
3705
3706 FOR i IN 1 .. x_results.entity_id.COUNT LOOP
3707 IF x_results.result_type(i) = po_validations.c_result_type_failure THEN
3708 x_headers.error_flag_tbl(l_index_tbl(x_results.entity_id(i))) := FND_API.g_TRUE;
3709 END IF;
3710 END LOOP;
3711
3712 IF (PO_LOG.d_proc) THEN
3713 PO_LOG.proc_end (d_module);
3714 END IF;
3715
3716 EXCEPTION
3717 WHEN OTHERS THEN
3718 PO_MESSAGE_S.add_exc_msg
3719 (
3720 p_pkg_name => d_pkg_name,
3721 p_procedure_name => d_api_name || '.' || d_position
3722 );
3723 RAISE;
3724 END populate_error_flag;
3725
3726
3727
3728 --PDOI CLM Integration Starts
3729 PROCEDURE default_clm_standard_format
3730 (
3731 p_key IN po_session_gt.key%TYPE,
3732 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3733 p_style_id_tbl IN PO_TBL_NUMBER,
3734 p_clm_award_type IN PO_TBL_VARCHAR30,
3735 x_clm_standard_form_tbl IN OUT NOCOPY PO_TBL_VARCHAR2000,
3736 x_clm_document_format_tbl IN OUT NOCOPY PO_TBL_VARCHAR2000
3737 ) IS
3738
3739 d_api_name CONSTANT VARCHAR2(30) := 'deafult_clm_standard_format';
3740 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3741 d_position NUMBER;
3742
3743 -- tables to store the derived result
3744 l_index_tbl PO_TBL_NUMBER;
3745 l_standard_form_tbl PO_TBL_VARCHAR30;
3746 l_document_format_tbl PO_TBL_VARCHAR30;
3747
3748 BEGIN
3749 d_position := 0;
3750
3751 IF (PO_LOG.d_proc) THEN
3752 PO_LOG.proc_begin(d_module, 'style_id_tbl', p_style_id_tbl);
3753 PO_LOG.proc_begin(d_module, 'clm_standard_form_tbl', x_clm_standard_form_tbl);
3754 PO_LOG.proc_begin(d_module, 'clm_document_format_tbl', x_clm_document_format_tbl);
3755 END IF;
3756
3757 FORALL i IN 1..p_index_tbl.COUNT
3758 INSERT INTO po_session_gt(key, num1, char1, char2, char3, num2)
3759 SELECT p_key,
3760 p_index_tbl(i),
3761 x_clm_standard_form_tbl(i),
3762 x_clm_document_format_tbl(i),
3763 p_clm_award_type(i),
3764 p_style_id_tbl(i)
3765 FROM PO_DOC_STYLE_HEADERS
3766 WHERE style_id = p_style_id_tbl(i)
3767 AND nvl(clm_flag, 'N') ='Y';
3768
3769 --Try to deafult both standard_from & document_format if there exists an entry of default type
3770 FORALL i IN 1..p_index_tbl.COUNT
3771 UPDATE po_session_gt
3772 SET char1 = ( SELECT standard_form
3773 FROM PO_PRINT_FORM_FORMATS
3774 WHERE document_type = decode(PO_PDOI_PARAMS.g_request.document_type, PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
3775 decode(char3,
3776 'BPA_CALL', 'PO_DEL_ORD_STD_FORM',
3777 'DELIVERY_ORD', 'PO_DEL_ORD_STD_FORM',
3778 'PO_AWARD_STD_FORM'),
3779 'PO_IDV_STD_FORM')
3780 AND Nvl(default_flag,'N') = 'Y'
3781 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate +1))
3782 AND style_id = num2
3783 ),
3784
3785 char2 = ( SELECT document_format
3786 FROM PO_PRINT_FORM_FORMATS
3787 WHERE document_type = decode(PO_PDOI_PARAMS.g_request.document_type, PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
3788 decode(char3,
3789 'BPA_CALL', 'PO_DEL_ORD_STD_FORM',
3790 'DELIVERY_ORD', 'PO_DEL_ORD_STD_FORM',
3791 'PO_AWARD_STD_FORM'),
3792 'PO_IDV_STD_FORM')
3793 AND Nvl(default_flag,'N') = 'Y'
3794 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate +1))
3795 AND style_id = num2
3796 )
3797 WHERE KEY = p_key
3798 AND char1 IS NULL
3799 AND char2 IS NULL
3800 AND num1 = p_index_tbl(i);
3801
3802
3803 --Try to default only the document_format from standard_form
3804 FORALL i IN 1..p_index_tbl.COUNT
3805 UPDATE po_session_gt
3806 SET char2 = ( SELECT document_format
3807 FROM PO_PRINT_FORM_FORMATS
3808 WHERE document_type = decode(PO_PDOI_PARAMS.g_request.document_type, PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
3809 decode(char3,
3810 'BPA_CALL', 'PO_DEL_ORD_STD_FORM',
3811 'DELIVERY_ORD', 'PO_DEL_ORD_STD_FORM',
3812 'PO_AWARD_STD_FORM'),
3813 'PO_IDV_STD_FORM')
3814 AND standard_form = char1
3815 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate +1))
3816 AND style_id = num2
3817 AND ( Nvl(default_flag,'N') = 'Y' OR 1 = (SELECT Count(document_format)
3818 FROM PO_PRINT_FORM_FORMATS
3819 WHERE document_type = decode(PO_PDOI_PARAMS.g_request.document_type, PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,
3820 decode(char3,
3821 'BPA_CALL', 'PO_DEL_ORD_STD_FORM',
3822 'DELIVERY_ORD', 'PO_DEL_ORD_STD_FORM',
3823 'PO_AWARD_STD_FORM'),
3824 'PO_IDV_STD_FORM')
3825 AND standard_form = char1
3826 AND trunc(sysdate) <= trunc(nvl(inactive_date, sysdate +1))
3827 AND style_id = num2)
3828 )
3829 )
3830 WHERE KEY = p_key
3831 AND char1 IS NOT NULL
3832 AND char2 IS NULL
3833 AND num1 = p_index_tbl(i);
3834
3835
3836 d_position := 10;
3837
3838 DELETE FROM po_session_gt
3839 WHERE key = p_key
3840 RETURNING num1, char1, char2 BULK COLLECT INTO l_index_tbl, l_standard_form_tbl, l_document_format_tbl ;
3841
3842 d_position := 20;
3843
3844 FOR i IN 1..l_index_tbl.COUNT
3845 LOOP
3846 IF (PO_LOG.d_stmt) THEN
3847 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3848 PO_LOG.stmt(d_module, d_position, 'new standard form', l_standard_form_tbl(i));
3849 PO_LOG.stmt(d_module, d_position, 'new document format', l_document_format_tbl(i));
3850 END IF;
3851
3852 x_clm_standard_form_tbl(l_index_tbl(i)) := l_standard_form_tbl(i);
3853 x_clm_document_format_tbl(l_index_tbl(i)) := l_document_format_tbl(i);
3854 END LOOP;
3855
3856 IF (PO_LOG.d_proc) THEN
3857 PO_LOG.proc_end (d_module);
3858 END IF;
3859
3860 EXCEPTION
3861 WHEN OTHERS THEN
3862 PO_MESSAGE_S.add_exc_msg
3863 (
3864 p_pkg_name => d_pkg_name,
3865 p_procedure_name => d_api_name || '.' || d_position
3866 );
3867 RAISE;
3868
3869 END default_clm_standard_format;
3870
3871
3872
3873 PROCEDURE derive_clm_award_type
3874 (
3875 p_key IN po_session_gt.key%TYPE,
3876 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3877 p_clm_award_type_disp_tbl IN PO_TBL_VARCHAR240,
3878 x_clm_award_type_tbl IN OUT NOCOPY PO_TBL_VARCHAR30
3879 ) IS
3880
3881 d_api_name CONSTANT VARCHAR2(30) := 'derive_clm_award_type';
3882 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3883 d_position NUMBER;
3884
3885 -- tables to store the derived result
3886 l_index_tbl PO_TBL_NUMBER;
3887 l_result_tbl PO_TBL_VARCHAR30;
3888 BEGIN
3889 d_position := 0;
3890
3891 IF (PO_LOG.d_proc) THEN
3892 PO_LOG.proc_begin(d_module, 'x_clm_award_type_tbl', x_clm_award_type_tbl);
3893 --PO_LOG.proc_begin(d_module, 'p_clm_award_type_disp_tbl', p_clm_award_type_disp_tbl);
3894 END IF;
3895
3896 FORALL i IN 1..p_index_tbl.COUNT
3897 INSERT INTO po_session_gt(key, num1, char1)
3898 SELECT p_key,
3899 p_index_tbl(i),
3900 lookup_code
3901 FROM PO_LOOKUP_CODES
3902 WHERE x_clm_award_type_tbl(i) IS NULL
3903 AND p_clm_award_type_disp_tbl(i) IS NOT null
3904 AND LOOKUP_TYPE IN ('PO_CLM_AWD_TYPE_SPO','PO_CLM_AWD_TYPE_ORD_OFF_IDV',
3905 'PO_CLM_AWD_TYPE_BPA','PO_CLM_AWD_TYPE_CPA')
3906 AND displayed_field = p_clm_award_type_disp_tbl(i);
3907
3908 d_position := 10;
3909
3910 DELETE FROM po_session_gt
3911 WHERE key = p_key
3912 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3913
3914 d_position := 20;
3915
3916 FOR i IN 1..l_index_tbl.COUNT
3917 LOOP
3918 IF (PO_LOG.d_stmt) THEN
3919 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3920 PO_LOG.stmt(d_module, d_position, 'new award type', l_result_tbl(i));
3921 END IF;
3922
3923 x_clm_award_type_tbl(l_index_tbl(i)) := l_result_tbl(i);
3924 END LOOP;
3925
3926 IF (PO_LOG.d_proc) THEN
3927 PO_LOG.proc_end (d_module);
3928 END IF;
3929
3930 EXCEPTION
3931 WHEN OTHERS THEN
3932 PO_MESSAGE_S.add_exc_msg
3933 (
3934 p_pkg_name => d_pkg_name,
3935 p_procedure_name => d_api_name || '.' || d_position
3936 );
3937 RAISE;
3938
3939 END derive_clm_award_type;
3940
3941
3942
3943
3944 PROCEDURE derive_clm_award_admin
3945 (
3946 p_key IN po_session_gt.key%TYPE,
3947 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
3948 p_clm_award_admin_disp_tbl IN PO_TBL_VARCHAR240,
3949 x_clm_award_administrator_tbl IN OUT NOCOPY PO_TBL_VARCHAR2000
3950 ) IS
3951
3952 d_api_name CONSTANT VARCHAR2(30) := 'derive_clm_award_admin';
3953 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3954 d_position NUMBER;
3955
3956 -- tables to store the derived result
3957 l_index_tbl PO_TBL_NUMBER;
3958 l_result_tbl PO_TBL_VARCHAR30;
3959 BEGIN
3960 d_position := 0;
3961
3962 IF (PO_LOG.d_proc) THEN
3963 PO_LOG.proc_begin(d_module);
3964 --PO_LOG.proc_begin(d_module, 'p_clm_award_type_disp_tbl', p_clm_award_type_disp_tbl);
3965 END IF;
3966
3967 FORALL i IN 1..p_index_tbl.COUNT
3968 INSERT INTO po_session_gt(key, num1, char1)
3969 SELECT p_key,
3970 p_index_tbl(i),
3971 To_Char(employee_id)
3972 FROM PO_BUYERS_VAL_V
3973 WHERE x_clm_award_administrator_tbl(i) IS NULL
3974 AND p_clm_award_admin_disp_tbl(i) IS NOT null
3975 AND ('Y' = 'Y' or BUSINESS_GROUP_ID = HR_GENERAL.get_business_group_id)
3976 AND full_name = p_clm_award_admin_disp_tbl(i);
3977
3978 d_position := 10;
3979
3980 DELETE FROM po_session_gt
3981 WHERE key = p_key
3982 RETURNING num1, char1 BULK COLLECT INTO l_index_tbl, l_result_tbl;
3983
3984 d_position := 20;
3985
3986 FOR i IN 1..l_index_tbl.COUNT
3987 LOOP
3988 IF (PO_LOG.d_stmt) THEN
3989 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
3990 PO_LOG.stmt(d_module, d_position, 'new award admin', l_result_tbl(i));
3991 END IF;
3992
3993 x_clm_award_administrator_tbl(l_index_tbl(i)) := l_result_tbl(i);
3994 END LOOP;
3995
3996 IF (PO_LOG.d_proc) THEN
3997 PO_LOG.proc_end (d_module);
3998 END IF;
3999
4000 EXCEPTION
4001 WHEN OTHERS THEN
4002 PO_MESSAGE_S.add_exc_msg
4003 (
4004 p_pkg_name => d_pkg_name,
4005 p_procedure_name => d_api_name || '.' || d_position
4006 );
4007 RAISE;
4008
4009 END derive_clm_award_admin;
4010
4011
4012
4013 PROCEDURE derive_clm_contract_officer
4014 (
4015 p_key IN po_session_gt.key%TYPE,
4016 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4017 p_clm_contract_oficer_disp_tbl IN PO_TBL_VARCHAR240,
4018 x_clm_contract_officer_tbl IN OUT NOCOPY PO_TBL_NUMBER
4019 ) IS
4020
4021 d_api_name CONSTANT VARCHAR2(30) := 'derive_clm_contract_officer';
4022 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4023 d_position NUMBER;
4024
4025 -- tables to store the derived result
4026 l_index_tbl PO_TBL_NUMBER;
4027 l_result_tbl PO_TBL_NUMBER;
4028 BEGIN
4029 d_position := 0;
4030
4031 IF (PO_LOG.d_proc) THEN
4032 PO_LOG.proc_begin(d_module);
4033 --PO_LOG.proc_begin(d_module, 'p_clm_award_type_disp_tbl', p_clm_award_type_disp_tbl);
4034 END IF;
4035
4036 FORALL i IN 1..p_index_tbl.COUNT
4037 INSERT INTO po_session_gt(key, num1, num2)
4038 SELECT p_key,
4039 p_index_tbl(i),
4040 employee_id
4041 FROM PO_BUYERS_VAL_V
4042 WHERE x_clm_contract_officer_tbl(i) IS NULL
4043 AND p_clm_contract_oficer_disp_tbl(i) IS NOT null
4044 AND ('Y' = 'Y' or BUSINESS_GROUP_ID = HR_GENERAL.get_business_group_id)
4045 AND full_name = p_clm_contract_oficer_disp_tbl(i)
4046 AND Nvl(contract_officer,'N') = 'Y';
4047
4048 d_position := 10;
4049
4050 DELETE FROM po_session_gt
4051 WHERE key = p_key
4052 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4053
4054 d_position := 20;
4055
4056 FOR i IN 1..l_index_tbl.COUNT
4057 LOOP
4058 IF (PO_LOG.d_stmt) THEN
4059 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4060 PO_LOG.stmt(d_module, d_position, 'new contract officer', l_result_tbl(i));
4061 END IF;
4062
4063 x_clm_contract_officer_tbl(l_index_tbl(i)) := l_result_tbl(i);
4064 END LOOP;
4065
4066 IF (PO_LOG.d_proc) THEN
4067 PO_LOG.proc_end (d_module);
4068 END IF;
4069
4070 EXCEPTION
4071 WHEN OTHERS THEN
4072 PO_MESSAGE_S.add_exc_msg
4073 (
4074 p_pkg_name => d_pkg_name,
4075 p_procedure_name => d_api_name || '.' || d_position
4076 );
4077 RAISE;
4078
4079 END derive_clm_contract_officer;
4080
4081
4082
4083 PROCEDURE derive_clm_source_document_id
4084 (
4085 p_key IN po_session_gt.key%TYPE,
4086 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4087 p_clm_source_document_disp_tbl IN PO_TBL_VARCHAR240,
4088 x_clm_source_document_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4089 ) IS
4090
4091 d_api_name CONSTANT VARCHAR2(30) := 'derive_clm_source_document_id';
4092 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4093 d_position NUMBER;
4094
4095 -- tables to store the derived result
4096 l_index_tbl PO_TBL_NUMBER;
4097 l_result_tbl PO_TBL_NUMBER;
4098 BEGIN
4099 d_position := 0;
4100
4101 IF (PO_LOG.d_proc) THEN
4102 PO_LOG.proc_begin(d_module);
4103 --PO_LOG.proc_begin(d_module, 'p_clm_award_type_disp_tbl', p_clm_award_type_disp_tbl);
4104 END IF;
4105
4106 FORALL i IN 1..p_index_tbl.COUNT
4107 INSERT INTO po_session_gt(key, num1, num2)
4108 SELECT p_key,
4109 p_index_tbl(i),
4110 po_header_id
4111 FROM po_headers_all
4112 WHERE x_clm_source_document_id_tbl(i) IS NULL
4113 AND p_clm_source_document_disp_tbl(i) IS NOT null
4114 AND clm_document_number = p_clm_source_document_disp_tbl(i);
4115
4116 d_position := 10;
4117
4118 DELETE FROM po_session_gt
4119 WHERE key = p_key
4120 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4121
4122 d_position := 20;
4123
4124 FOR i IN 1..l_index_tbl.COUNT
4125 LOOP
4126 IF (PO_LOG.d_stmt) THEN
4127 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4128 PO_LOG.stmt(d_module, d_position, 'new source idv', l_result_tbl(i));
4129 END IF;
4130
4131 x_clm_source_document_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
4132 END LOOP;
4133
4134 IF (PO_LOG.d_proc) THEN
4135 PO_LOG.proc_end (d_module);
4136 END IF;
4137
4138 EXCEPTION
4139 WHEN OTHERS THEN
4140 PO_MESSAGE_S.add_exc_msg
4141 (
4142 p_pkg_name => d_pkg_name,
4143 p_procedure_name => d_api_name || '.' || d_position
4144 );
4145 RAISE;
4146
4147 END derive_clm_source_document_id;
4148
4149
4150 PROCEDURE derive_vendor_id_from_idv
4151 (
4152 p_key IN po_session_gt.key%TYPE,
4153 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4154 p_clm_source_document_id_tbl IN PO_TBL_NUMBER,
4155 p_doc_type_tbl IN PO_TBL_VARCHAR30,
4156 x_vendor_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4157 ) IS
4158
4159 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_id_from_idv';
4160 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4161 d_position NUMBER;
4162
4163 -- tables to store the derived result
4164 l_index_tbl PO_TBL_NUMBER;
4165 l_result_tbl PO_TBL_NUMBER;
4166 BEGIN
4167 d_position := 0;
4168
4169 IF (PO_LOG.d_proc) THEN
4170 PO_LOG.proc_begin(d_module);
4171 PO_LOG.proc_begin(d_module, 'vendor_id_tbl', x_vendor_id_tbl);
4172 END IF;
4173
4174 FORALL i IN 1..p_index_tbl.Count
4175 INSERT INTO po_session_gt(key, num1, num2)
4176 SELECT p_key,
4177 p_index_tbl(i),
4178 vendor_id
4179 FROM po_headers_all
4180 WHERE x_vendor_id_tbl(i) IS NULL
4181 AND p_clm_source_document_id_tbl(i) IS NOT NULL
4182 AND po_header_id = p_clm_source_document_id_tbl(i)
4183 AND p_doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD;
4184
4185 d_position := 10;
4186
4187 DELETE FROM po_session_gt
4188 WHERE key = p_key
4189 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4190
4191 d_position := 20;
4192
4193 FOR i IN 1..l_index_tbl.COUNT
4194 LOOP
4195 IF (PO_LOG.d_stmt) THEN
4196 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4197 PO_LOG.stmt(d_module, d_position, 'new vendor id', l_result_tbl(i));
4198 END IF;
4199 x_vendor_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
4200 END LOOP;
4201
4202 IF (PO_LOG.d_proc) THEN
4203 PO_LOG.proc_end (d_module);
4204 END IF;
4205
4206 EXCEPTION
4207 WHEN OTHERS THEN
4208 PO_MESSAGE_S.add_exc_msg
4209 (
4210 p_pkg_name => d_pkg_name,
4211 p_procedure_name => d_api_name || '.' || d_position
4212 );
4213 RAISE;
4214
4215 END derive_vendor_id_from_idv;
4216
4217
4218 PROCEDURE derive_vendor_site_id_from_idv
4219 (
4220 p_key IN po_session_gt.key%TYPE,
4221 p_index_tbl IN DBMS_SQL.NUMBER_TABLE,
4222 p_clm_source_document_id_tbl IN PO_TBL_NUMBER,
4223 p_doc_type_tbl IN PO_TBL_VARCHAR30,
4224 x_vendor_site_id_tbl IN OUT NOCOPY PO_TBL_NUMBER
4225 ) IS
4226
4227 d_api_name CONSTANT VARCHAR2(30) := 'derive_vendor_site_id_from_idv';
4228 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
4229 d_position NUMBER;
4230
4231 -- tables to store the derived result
4232 l_index_tbl PO_TBL_NUMBER;
4233 l_result_tbl PO_TBL_NUMBER;
4234 BEGIN
4235 d_position := 0;
4236
4237 IF (PO_LOG.d_proc) THEN
4238 PO_LOG.proc_begin(d_module);
4239 --PO_LOG.proc_begin(d_module, 'p_clm_award_type_disp_tbl', p_clm_award_type_disp_tbl);
4240 END IF;
4241
4242 FORALL i IN 1..p_index_tbl.Count
4243 INSERT INTO po_session_gt(key, num1, num2)
4244 SELECT p_key,
4245 p_index_tbl(i),
4246 vendor_site_id
4247 FROM po_headers_all
4248 WHERE x_vendor_site_id_tbl(i) IS NULL
4249 AND p_clm_source_document_id_tbl(i) IS NOT NULL
4250 AND po_header_id = p_clm_source_document_id_tbl(i)
4251 AND p_doc_type_tbl(i) = PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD;
4252
4253 d_position := 10;
4254
4255 DELETE FROM po_session_gt
4256 WHERE key = p_key
4257 RETURNING num1, num2 BULK COLLECT INTO l_index_tbl, l_result_tbl;
4258
4259 d_position := 20;
4260
4261 FOR i IN 1..l_index_tbl.COUNT
4262 LOOP
4263 IF (PO_LOG.d_stmt) THEN
4264 PO_LOG.stmt(d_module, d_position, 'index', l_index_tbl(i));
4265 PO_LOG.stmt(d_module, d_position, 'new vendor id', l_result_tbl(i));
4266 END IF;
4267 x_vendor_site_id_tbl(l_index_tbl(i)) := l_result_tbl(i);
4268 END LOOP;
4269
4270 IF (PO_LOG.d_proc) THEN
4271 PO_LOG.proc_end (d_module);
4272 END IF;
4273
4274 EXCEPTION
4275 WHEN OTHERS THEN
4276 PO_MESSAGE_S.add_exc_msg
4277 (
4278 p_pkg_name => d_pkg_name,
4279 p_procedure_name => d_api_name || '.' || d_position
4280 );
4281 RAISE;
4282
4283 END derive_vendor_site_id_from_idv;
4284
4285 --PDOI CLM Integration Ends
4286
4287 END PO_PDOI_HEADER_PROCESS_PVT;