[Home] [Help]
PACKAGE BODY: APPS.PO_AUTO_HEADER_PROCESS_PVT
Source
1 PACKAGE BODY PO_AUTO_HEADER_PROCESS_PVT AS
2 /* $Header: PO_AUTO_HEADER_PROCESS_PVT.plb 120.18.12020000.4 2013/05/08 08:36:22 akyanama ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(1000) := 'PO_AUTO_HEADER_PROCESS_PVT';
5 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.PO_AUTO_HEADER_PROCESS_PVT.';
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
8 -- Derive and validate Vendor related attributes
9 g_vendor_ship_to_loc_id NUMBER;
10 g_vendor_bill_to_loc_id NUMBER;
11 g_terms_id PO_HEADERS.terms_id%TYPE;
12 g_fob PO_HEADERS.fob_lookup_code%TYPE;
13 g_freight_term PO_HEADERS.freight_terms_lookup_code%TYPE;
14 g_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
15 g_vs_terms_id PO_HEADERS.terms_id%TYPE;
16 g_vs_fob PO_HEADERS.fob_lookup_code%TYPE;
17 g_vs_freight_term PO_HEADERS.freight_terms_lookup_code%TYPE;
18 g_vs_ship_via_lookup_code PO_HEADERS_ALL.ship_via_lookup_code%TYPE;
19
20 /* -------------------------------------------------------
21 ---------------- PRIVATE PROCEDURES -------------------
22 ------------------------------------------------------- */
23 /* =============================================================================
24
25 NAME: get_invoice_match_option
26 DESC: Derive invoice_match_option for a given vendor and vendor site
27 ARGS: IN : x_vendor_id IN NUMBER - Vendor Id.
28 x_vendor_site_id IN NUMBER - Vendor Site Id.
29 OUT : x_invoice_match_option OUT NOCOPY VARCHAR2 - Invoice match option
30 ============================================================================= */
31 PROCEDURE get_invoice_match_option
32 (
33 x_vendor_id IN NUMBER,
34 x_vendor_site_id IN NUMBER,
35 x_invoice_match_option OUT NOCOPY VARCHAR2);
36
37 /* -------------------------------------------------------
38 ---------------- PUBLIC PROCEDURES -------------------
39 ------------------------------------------------------- */
40 /* ============================================================================
41
42 NAME: fetch_headers
43 DESC: Fetch header details into header record type
44 ARGS: OUT : x_headers PO_AUTOCREATE_TYPES.headers_rec_type (Record variable to hold the header info)
45 Algorithm: Based on the in parameters select the interface record(s) into
46 the header record type.
47 ==============================================================================*/
48 PROCEDURE fetch_headers( p_interface_header_id IN NUMBER,
49 x_headers OUT NOCOPY PO_AUTOCREATE_TYPES.headers_rec_type)
50 IS
51 l_api_name VARCHAR2(30) := 'fetch_headers';
52 l_progress VARCHAR2(3) := '000';
53
54 BEGIN
55
56 IF g_debug_stmt THEN
57 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
58 END IF;
59
60 PO_AUTOCREATE_PARAMS.g_interface_header_id := p_interface_header_id;
61
62 -- Bug 10136310
63 IF po_autocreate_params.g_mode = 'NEW' THEN
64
65 SELECT interface_header_id,
66 draft_id,
67 po_header_id,
68 action,
69 document_num,
70 document_type_code,
71 document_subtype,
72 rate_type,
73 rate_type_code,
74 rate_date,
75 rate,
76 agent_id,
77 agent_name,
78 ship_to_location_id,
79 ship_to_location,
80 bill_to_location_id,
81 bill_to_location,
82 payment_terms,
83 terms_id,
84 vendor_name,
85 vendor_num,
86 vendor_id,
87 vendor_site_code,
88 vendor_site_id,
89 vendor_contact,
90 vendor_contact_id,
91 from_rfq_num,
92 from_header_id,
93 fob,
94 freight_carrier,
95 freight_terms,
96 pay_on_code,
97 shipping_control,
98 currency_code,
99 quote_warning_delay,
100 approval_required_flag,
101 reply_date,
102 approval_status,
103 approved_date,
104 from_type_lookup_code,
105 revision_num,
106 confirming_order_flag,
107 acceptance_required_flag,
108 min_release_amount,
109 closed_code,
110 print_count,
111 frozen_flag,
112 encumbrance_required_flag,
113 vendor_doc_num,
114 org_id,
115 acceptance_due_date,
116 amount_to_encumber,
117 effective_date,
118 expiration_date,
119 po_release_id,
120 release_num,
121 release_date,
122 revised_date,
123 printed_date,
124 closed_date,
125 amount_agreed,
126 amount_limit, --
127 firm_flag,
128 gl_encumbered_date,
129 gl_encumbered_period_name,
130 budget_account_id,
131 budget_account,
132 budget_account_segment1,
133 budget_account_segment2,
134 budget_account_segment3,
135 budget_account_segment4,
136 budget_account_segment5,
137 budget_account_segment6,
138 budget_account_segment7,
139 budget_account_segment8,
140 budget_account_segment9,
141 budget_account_segment10,
142 budget_account_segment11,
143 budget_account_segment12,
144 budget_account_segment13,
145 budget_account_segment14,
146 budget_account_segment15,
147 budget_account_segment16,
148 budget_account_segment17,
149 budget_account_segment18,
150 budget_account_segment19,
151 budget_account_segment20,
152 budget_account_segment21,
153 budget_account_segment22,
154 budget_account_segment23,
155 budget_account_segment24,
156 budget_account_segment25,
157 budget_account_segment26,
158 budget_account_segment27,
159 budget_account_segment28,
160 budget_account_segment29,
161 budget_account_segment30,
162 created_language,
163 style_id,
164 style_display_name,
165 global_agreement_flag,
166 clm_standard_form,
167 clm_document_format,
168 -- standard who columns
169 last_update_date,
170 last_updated_by,
171 last_update_login,
172 creation_date,
173 created_by,
174 request_id,
175 program_application_id,
176 program_id,
177 program_update_date,
178 FND_API.g_FALSE, -- initial value for error_flag
179 -- txn table columns
180 NULL, -- status_lookup_code
181 NULL, -- cancel_flag
182 NULL, -- vendor_order_num
183 NULL, -- quote_vendor_quote_num
184 'AUTOCREATE', -- doc_creation_method
185 NULL, -- quotation_class_code
186 NULL, -- approved_flag
187 NULL, -- tax_attribute_update_code
188 -- blanket dist columns
189 NULL,
190 clm_award_type, -- po_dist_id
191 -- CLM specific attributes
192 clm_source_document_id,
193 clm_effective_date,
194 clm_vendor_offer_number,
195 clm_award_administrator,
196 clm_no_signed_copies_to_return,
197 clm_min_guarantee_award_amt,
198 clm_min_guar_award_amt_percent,
199 clm_min_order_amount,
200 clm_max_order_amount,
201 clm_amount_released,
202 clm_external_idv,
203 NULL, --clm_contract_officer bug 12987412
204 umbrella_program_id, --umbrella program
205 fon_ref_id --umbrella program
206 INTO x_headers.intf_header_id,
207 x_headers.draft_id,
208 x_headers.po_header_id,
209 x_headers.action,
210 x_headers.document_num,
211 x_headers.doc_type,
212 x_headers.doc_subtype,
213 x_headers.rate_type,
214 x_headers.rate_type_code,
215 x_headers.rate_date,
216 x_headers.rate,
217 x_headers.agent_id,
218 x_headers.agent_name,
219 x_headers.ship_to_loc_id,
220 x_headers.ship_to_loc,
221 x_headers.bill_to_loc_id,
222 x_headers.bill_to_loc,
223 x_headers.payment_terms,
224 x_headers.terms_id,
225 x_headers.vendor_name,
226 x_headers.vendor_num,
227 x_headers.vendor_id,
228 x_headers.vendor_site_code,
229 x_headers.vendor_site_id,
230 x_headers.vendor_contact,
231 x_headers.vendor_contact_id,
232 x_headers.from_rfq_num,
233 x_headers.from_header_id,
234 x_headers.fob,
235 x_headers.freight_carrier,
236 x_headers.freight_term,
237 x_headers.pay_on_code,
238 x_headers.shipping_control,
239 x_headers.currency_code,
240 x_headers.quote_warning_delay,
241 x_headers.approval_required_flag,
242 x_headers.reply_date,
243 x_headers.approval_status,
244 x_headers.approved_date,
245 x_headers.from_type_lookup_code,
246 x_headers.revision_num,
247 x_headers.confirming_order_flag,
248 x_headers.acceptance_required_flag,
249 x_headers.min_release_amount,
250 x_headers.closed_code,
251 x_headers.print_count,
252 x_headers.frozen_flag,
253 x_headers.encumbrance_required_flag,
254 x_headers.vendor_doc_num,
255 x_headers.org_id,
256 x_headers.acceptance_due_date,
257 x_headers.amount_to_encumber,
258 x_headers.effective_date,
259 x_headers.expiration_date,
260 x_headers.po_release_id,
261 x_headers.release_num,
262 x_headers.release_date,
263 x_headers.revised_date,
264 x_headers.printed_date,
265 x_headers.closed_date,
266 x_headers.amount_agreed,
267 x_headers.amount_limit,
268 x_headers.firm_flag,
269 x_headers.gl_encumbered_date,
270 x_headers.gl_encumbered_period,
271 x_headers.budget_account_id,
272 x_headers.budget_account,
273 x_headers.budget_account_segment1,
274 x_headers.budget_account_segment2,
275 x_headers.budget_account_segment3,
276 x_headers.budget_account_segment4,
277 x_headers.budget_account_segment5,
278 x_headers.budget_account_segment6,
279 x_headers.budget_account_segment7,
280 x_headers.budget_account_segment8,
281 x_headers.budget_account_segment9,
282 x_headers.budget_account_segment10,
283 x_headers.budget_account_segment11,
284 x_headers.budget_account_segment12,
285 x_headers.budget_account_segment13,
286 x_headers.budget_account_segment14,
287 x_headers.budget_account_segment15,
288 x_headers.budget_account_segment16,
289 x_headers.budget_account_segment17,
290 x_headers.budget_account_segment18,
291 x_headers.budget_account_segment19,
292 x_headers.budget_account_segment20,
293 x_headers.budget_account_segment21,
294 x_headers.budget_account_segment22,
295 x_headers.budget_account_segment23,
296 x_headers.budget_account_segment24,
297 x_headers.budget_account_segment25,
298 x_headers.budget_account_segment26,
299 x_headers.budget_account_segment27,
300 x_headers.budget_account_segment28,
301 x_headers.budget_account_segment29,
302 x_headers.budget_account_segment30,
303 x_headers.created_language,
304 x_headers.style_id,
305 x_headers.style_display_name,
306 x_headers.global_agreement_flag,
307 x_headers.clm_standard_form,
308 x_headers.clm_document_format,
309 -- standard who columns
310 x_headers.last_update_date,
311 x_headers.last_updated_by,
312 x_headers.last_update_login,
313 x_headers.creation_date,
314 x_headers.created_by,
315 x_headers.request_id,
316 x_headers.program_application_id,
317 x_headers.program_id,
318 x_headers.program_update_date,
319 x_headers.error_flag, -- set initial value on error_flag
320 -- tan table columns
321 x_headers.status_lookup_code,
322 x_headers.cancel_flag,
323 x_headers.vendor_order_num,
324 x_headers.quote_vendor_quote_num,
325 x_headers.doc_creation_method,
326 x_headers.quotation_class_code,
327 x_headers.approved_flag,
328 x_headers.tax_attribute_update_code,
329 -- blanket dist columns
330 x_headers.po_dist_id,
331 x_headers.clm_award_type,
332 -- CLM specific attributes
333 x_headers.clm_source_document_id,
334 x_headers.clm_effective_date,
335 x_headers.clm_vendor_offer_number,
336 x_headers.clm_award_administrator,
337 x_headers.clm_no_signed_copies_to_return,
338 x_headers.clm_min_guarantee_award_amt,
339 x_headers.clm_min_guar_award_amt_percent,
340 x_headers.clm_min_order_amount,
341 x_headers.clm_max_order_amount,
342 x_headers.clm_amount_released,
343 x_headers.clm_external_idv,
344 x_headers.clm_contract_officer,
345 /* bug 12987412 new award is created ,
346 initially default clm_contract_officer to null*/
347 x_headers.umbrella_program_id, --umbrella program
348 x_headers.fon_ref_id --umbrella program
349 FROM po_headers_interface
350 WHERE interface_header_id = p_interface_header_id
351 ORDER BY interface_header_id;
352
353 ELSIF po_autocreate_params.g_mode = 'ADD' THEN
354
355 SELECT PHI.interface_header_id,
356 PH.draft_id,
357 PH.po_header_id,
358 PHI.action,
359 PHI.document_num,
360 PHI.document_type_code,
361 PHI.document_subtype,
362 PH.rate_type,
363 PHI.rate_type_code,
364 PH.rate_date,
365 PH.rate,
366 PH.agent_id,
367 PHI.agent_name,
368 PH.ship_to_location_id,
369 PHI.ship_to_location,
370 PH.bill_to_location_id,
371 PHI.bill_to_location,
372 PHI.payment_terms,
373 PH.terms_id,
374 PHI.vendor_name,
375 PHI.vendor_num,
376 PH.vendor_id,
377 PHI.vendor_site_code,
378 PH.vendor_site_id,
379 PHI.vendor_contact,
380 PH.vendor_contact_id,
381 PHI.from_rfq_num,
382 PH.from_header_id,
383 PHI.fob,
384 PHI.freight_carrier,
385 PHI.freight_terms,
386 PH.pay_on_code,
387 PH.shipping_control,
388 PH.currency_code,
389 PH.quote_warning_delay,
390 PH.approval_required_flag,
391 PH.reply_date,
392 PHI.approval_status,
393 PH.approved_date,
394 PH.from_type_lookup_code,
395 PHI.revision_num,
396 PH.confirming_order_flag,
397 PH.acceptance_required_flag,
398 PH.min_release_amount,
399 PH.closed_code,
400 PH.print_count,
401 PH.frozen_flag,
402 PH.encumbrance_required_flag,
403 PHI.vendor_doc_num,
404 PH.org_id,
405 PH.acceptance_due_date,
406 PHI.amount_to_encumber,
407 PHI.effective_date,
408 PHI.expiration_date,
409 PHI.po_release_id,
410 PHI.release_num,
411 PHI.release_date,
412 PH.revised_date,
413 PH.printed_date,
414 PH.closed_date,
415 PHI.amount_agreed,
416 PH.amount_limit, --
417 PHI.firm_flag,
418 PHI.gl_encumbered_date,
419 PHI.gl_encumbered_period_name,
420 PHI.budget_account_id,
421 PHI.budget_account,
422 PHI.budget_account_segment1,
423 PHI.budget_account_segment2,
424 PHI.budget_account_segment3,
425 PHI.budget_account_segment4,
426 PHI.budget_account_segment5,
427 PHI.budget_account_segment6,
428 PHI.budget_account_segment7,
429 PHI.budget_account_segment8,
430 PHI.budget_account_segment9,
431 PHI.budget_account_segment10,
432 PHI.budget_account_segment11,
433 PHI.budget_account_segment12,
434 PHI.budget_account_segment13,
435 PHI.budget_account_segment14,
436 PHI.budget_account_segment15,
437 PHI.budget_account_segment16,
438 PHI.budget_account_segment17,
439 PHI.budget_account_segment18,
440 PHI.budget_account_segment19,
441 PHI.budget_account_segment20,
442 PHI.budget_account_segment21,
443 PHI.budget_account_segment22,
444 PHI.budget_account_segment23,
445 PHI.budget_account_segment24,
446 PHI.budget_account_segment25,
447 PHI.budget_account_segment26,
448 PHI.budget_account_segment27,
449 PHI.budget_account_segment28,
450 PHI.budget_account_segment29,
451 PHI.budget_account_segment30,
452 PH.created_language,
453 PH. style_id,
454 PHI.style_display_name,
455 PH.global_agreement_flag,
456 PH.clm_standard_form,
457 PH.clm_document_format,
458 -- standard who columns
459 PH.last_update_date,
460 PH.last_updated_by,
461 PH.last_update_login,
462 PH.creation_date,
463 PH.created_by,
464 PH.request_id,
465 PH.program_application_id,
466 PH.program_id,
467 PH.program_update_date,
468 FND_API.g_FALSE, -- initial value for error_flag
469 -- txn table columns
470 NULL, -- status_lookup_code
471 NULL, -- cancel_flag
472 NULL, -- vendor_order_num
473 NULL, -- quote_vendor_quote_num
474 'AUTOCREATE', -- doc_creation_method
475 NULL, -- quotation_class_code
476 NULL, -- approved_flag
477 NULL, -- tax_attribute_update_code
478 -- blanket dist columns
479 NULL,
480 PH.clm_award_type, -- po_dist_id
481 -- CLM specific attributes
482 PH.clm_source_document_id,
483 PH.clm_effective_date,
484 PH.clm_vendor_offer_number,
485 PH.clm_award_administrator,
486 PH.clm_no_signed_copies_to_return,
487 PH.clm_min_guarantee_award_amt,
488 PH.clm_min_guar_award_amt_percent,
489 PH.clm_min_order_amount,
490 PH.clm_max_order_amount,
491 PH.clm_amount_released,
492 PH.clm_external_idv,
493 PH.clm_contract_officer,
494 /*bug 12987412 add to mode, default clm_contract_officer
495 from base document*/
496 PH.umbrella_program_id, --umbrella program
497 PH.fon_ref_id --umbrella program
498 INTO x_headers.intf_header_id,
499 x_headers.draft_id,
500 x_headers.po_header_id,
501 x_headers.action,
502 x_headers.document_num,
503 x_headers.doc_type,
504 x_headers.doc_subtype,
505 x_headers.rate_type,
506 x_headers.rate_type_code,
507 x_headers.rate_date,
508 x_headers.rate,
509 x_headers.agent_id,
510 x_headers.agent_name,
511 x_headers.ship_to_loc_id,
512 x_headers.ship_to_loc,
513 x_headers.bill_to_loc_id,
514 x_headers.bill_to_loc,
515 x_headers.payment_terms,
516 x_headers.terms_id,
517 x_headers.vendor_name,
518 x_headers.vendor_num,
519 x_headers.vendor_id,
520 x_headers.vendor_site_code,
521 x_headers.vendor_site_id,
522 x_headers.vendor_contact,
523 x_headers.vendor_contact_id,
524 x_headers.from_rfq_num,
525 x_headers.from_header_id,
526 x_headers.fob,
527 x_headers.freight_carrier,
528 x_headers.freight_term,
529 x_headers.pay_on_code,
530 x_headers.shipping_control,
531 x_headers.currency_code,
532 x_headers.quote_warning_delay,
533 x_headers.approval_required_flag,
534 x_headers.reply_date,
535 x_headers.approval_status,
536 x_headers.approved_date,
537 x_headers.from_type_lookup_code,
538 x_headers.revision_num,
539 x_headers.confirming_order_flag,
540 x_headers.acceptance_required_flag,
541 x_headers.min_release_amount,
542 x_headers.closed_code,
543 x_headers.print_count,
544 x_headers.frozen_flag,
545 x_headers.encumbrance_required_flag,
546 x_headers.vendor_doc_num,
547 x_headers.org_id,
548 x_headers.acceptance_due_date,
549 x_headers.amount_to_encumber,
550 x_headers.effective_date,
551 x_headers.expiration_date,
552 x_headers.po_release_id,
553 x_headers.release_num,
554 x_headers.release_date,
555 x_headers.revised_date,
556 x_headers.printed_date,
557 x_headers.closed_date,
558 x_headers.amount_agreed,
559 x_headers.amount_limit,
560 x_headers.firm_flag,
561 x_headers.gl_encumbered_date,
562 x_headers.gl_encumbered_period,
563 x_headers.budget_account_id,
564 x_headers.budget_account,
565 x_headers.budget_account_segment1,
566 x_headers.budget_account_segment2,
567 x_headers.budget_account_segment3,
568 x_headers.budget_account_segment4,
569 x_headers.budget_account_segment5,
570 x_headers.budget_account_segment6,
571 x_headers.budget_account_segment7,
572 x_headers.budget_account_segment8,
573 x_headers.budget_account_segment9,
574 x_headers.budget_account_segment10,
575 x_headers.budget_account_segment11,
576 x_headers.budget_account_segment12,
577 x_headers.budget_account_segment13,
578 x_headers.budget_account_segment14,
579 x_headers.budget_account_segment15,
580 x_headers.budget_account_segment16,
581 x_headers.budget_account_segment17,
582 x_headers.budget_account_segment18,
583 x_headers.budget_account_segment19,
584 x_headers.budget_account_segment20,
585 x_headers.budget_account_segment21,
586 x_headers.budget_account_segment22,
587 x_headers.budget_account_segment23,
588 x_headers.budget_account_segment24,
589 x_headers.budget_account_segment25,
590 x_headers.budget_account_segment26,
591 x_headers.budget_account_segment27,
592 x_headers.budget_account_segment28,
593 x_headers.budget_account_segment29,
594 x_headers.budget_account_segment30,
595 x_headers.created_language,
596 x_headers.style_id,
597 x_headers.style_display_name,
598 x_headers.global_agreement_flag,
599 x_headers.clm_standard_form,
600 x_headers.clm_document_format,
601 -- standard who columns
602 x_headers.last_update_date,
603 x_headers.last_updated_by,
604 x_headers.last_update_login,
605 x_headers.creation_date,
606 x_headers.created_by,
607 x_headers.request_id,
608 x_headers.program_application_id,
609 x_headers.program_id,
610 x_headers.program_update_date,
611 x_headers.error_flag, -- set initial value on error_flag
612 -- tan table columns
613 x_headers.status_lookup_code,
614 x_headers.cancel_flag,
615 x_headers.vendor_order_num,
616 x_headers.quote_vendor_quote_num,
617 x_headers.doc_creation_method,
618 x_headers.quotation_class_code,
619 x_headers.approved_flag,
620 x_headers.tax_attribute_update_code,
621 -- blanket dist columns
622 x_headers.po_dist_id,
623 x_headers.clm_award_type,
624 -- CLM specific attributes
625 x_headers.clm_source_document_id,
626 x_headers.clm_effective_date,
627 x_headers.clm_vendor_offer_number,
628 x_headers.clm_award_administrator,
629 x_headers.clm_no_signed_copies_to_return,
630 x_headers.clm_min_guarantee_award_amt,
631 x_headers.clm_min_guar_award_amt_percent,
632 x_headers.clm_min_order_amount,
633 x_headers.clm_max_order_amount,
634 x_headers.clm_amount_released,
635 x_headers.clm_external_idv,
636 x_headers.clm_contract_officer, --bug 12987412
637 x_headers.umbrella_program_id, --umbrella program
638 x_headers.fon_ref_id --umbrella program
639 FROM po_headers_all PH,
640 po_headers_interface PHI
641 WHERE PH.SEGMENT1 = REPLACE(PHI.DOCUMENT_NUM,'-')
642 AND PHI.interface_header_id = p_interface_header_id;
643
644 END IF ;
645
646 IF g_debug_stmt THEN
647 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
648 END IF;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 IF g_debug_unexp THEN --< Bug 3210331: use proper debugging >
653 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
654 END IF;
655
656 --CLM Phase 2 changes : error handling
657 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HEADER_FETCH_ERR',x_token1_value => sqlerrm);
658
659 po_message_s.sql_error('FETCH_HEADER',l_progress,SQLCODE);
660 PO_AUTOCREATE_PVT.wrapup();
661 RAISE;
662 END fetch_headers;
663
664 /* ============================================================================
665 NAME: derive_and_default_headers
666 DESC: Perform derive logic on header records from Interface table.
667 * Get the vendor details (po_vendors_sv.get_vendor_info
668 * Get the vendor site info (po_vendor_sites_sv.get_vendor_site_info)
669 (These values needs to be transferred to validation logic)
670 * Derive Pay On Code
671 * Derive Rate (get_rate_for_req_price)
672 * Derive ship to and bill to
673 * Default amount tolerance values for GBPA
674 ARGS: IN OUT : x_headers PO_AUTOCREATE_TYPES.headers_rec_type
675 Record variable to hold the header info
676 NOTE: If possible use the procedures from PDOI
677 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
678 - PO_PDOI_HEADER_PROCESS_PVT.derive_rate_type_code
679 - PO_PDOI_HEADER_PROCESS_PVT.derive_agent_id
680 - PO_PDOI_HEADER_PROCESS_PVT.derive_location_id
681 - PO_PDOI_HEADER_PROCESS_PVT.derive_terms_id
682 - PO_PDOI_HEADER_PROCESS_PVT.derive_vendor_id
683 - PO_PDOI_HEADER_PROCESS_PVT.derive_vendor_site_id
684 - PO_PDOI_HEADER_PROCESS_PVT.derive_vendor_contact_id
685 - PO_PDOI_HEADER_PROCESS_PVT.derive_style_id
686 - PO_PDOI_HEADER_PROCESS_PVT.derive_from_header_id
687 Use the procedure PO_PDOI_UTL.generate_ordered_num_list to create
688 the index table. For Header set the length of index table
689 (l_index_tbl) to 1.
690 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
691 ============================================================================= */
692 PROCEDURE derive_and_default_headers(x_headers IN OUT NOCOPY PO_AUTOCREATE_TYPES.headers_rec_type)
693 IS
694
695 l_api_name VARCHAR2(30) := 'derive_and_default_headers';
696 l_progress VARCHAR2(3) := '000';
697 -- Derive rate info
698 l_req_ou_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
699 l_po_ou_sob_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
700 l_display_rate NUMBER;
701 l_rate_type PO_HEADERS_INTERFACE.rate_type%TYPE;
702 /* bug 12987412 variables declared to get the employee_id using
703 po_employees_sv.get_employee
704 */
705 x_emp_id NUMBER;
706 x_emp_name per_employees_current_x.full_name%TYPE;
707 x_location_id NUMBER;
708 x_location_code hr_locations_all.location_code%TYPE;
709 x_is_buyer BOOLEAN := FALSE;
710 x_emp_flag BOOLEAN := FALSE;
711 x_fnd_user BOOLEAN := FALSE;
712 /* bug 12987412*/
713
714 BEGIN
715
716 IF g_debug_stmt THEN
717 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
718 END IF;
719
720 /*
721 ** If we are using automatic numbering, get segment1
722 ** from the po_unique_identifier_control table.
723 ** If we are using manual numbering, segment1
724 ** should already be loaded into the po_headers_interface table.
725 ** The checks to verify that a manual po number is unique
726 ** is done on the client side.
727 */
728 /*
729 * Temporarily using the replace(x_headers.document_num,'-') to compare with segment1
730 * But need to change later to use API which returns proper segment1
731 */
732 IF (PO_AUTOCREATE_PARAMS.g_mode = 'ADD') THEN
733
734 l_progress := '010';
735
736 SELECT ph.po_header_id
737 INTO x_headers.po_header_id
738 FROM po_headers_all ph
739 WHERE ph.type_lookup_code = PO_AUTOCREATE_PARAMS.g_document_subtype
740 AND ph.segment1 = REPLACE(x_headers.document_num,'-')
741 AND NVL(org_id, -99) = NVL(PO_AUTOCREATE_PARAMS.g_purchasing_ou_id, -99);
742
743 ELSIF (PO_AUTOCREATE_PARAMS.g_mode='NEW') THEN
744
745 l_progress := '020';
746
747 SELECT po_headers_s.nextval INTO x_headers.po_header_id FROM sys.dual;
748
749 IF (PO_AUTOCREATE_PARAMS.g_sys.user_defined_po_num_code='AUTOMATIC') AND (PO_AUTOCREATE_PARAMS.g_document_type IN ('PO','PA')) THEN
750
751 --< Bug 14314684 Create Workflow project >
752 --Commenting this condition as we dont have emergency PO number in case of CLM
753 --IF PO_AUTOCREATE_PARAMS.g_old_document_num IS NULL THEN
754 x_headers.document_num := '-'||x_headers.po_header_id;
755 --END IF;
756
757 END IF;
758
759 END IF; -- PO_AUTOCREATE_PARAMS.g_mode = 'ADD'
760
761 IF g_debug_stmt THEN
762 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After getting the po_header_id: po_header_id :'|| x_headers.po_header_id);
763 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After getting the po_header_id: document_num :'|| x_headers.document_num);
764 END IF;
765
766 l_progress := '030';
767 /* Complex PO - Check to find out is there a Complex work PO */
768 IF (x_headers.style_id IS NOT NULL) THEN
769 PO_AUTOCREATE_PARAMS.g_is_complex_work_po := PO_COMPLEX_WORK_PVT.is_complex_work_style(p_style_id => x_headers.style_id);
770 ELSE
771 PO_AUTOCREATE_PARAMS.g_is_complex_work_po := FALSE;
772 END IF;
773 /* Complex PO - end */
774
775
776 /* Derive vendor related info from vendor and vendor site based on the following
777 **
778 ** - Derive vendor related attributes from vendor sites
779 ** - Validate the vendor related attributes
780 ** - If NOT VALID, derive vendor related attributes from vendor
781 */
782 IF(x_headers.vendor_id IS NOT NULL) THEN
783 l_progress := '040';
784
785 IF g_debug_stmt THEN
786 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Before deriving vendor related info.');
787 END IF;
788
789 x_headers.ship_via_lookup_code := NULL;
790 x_headers.fob := NULL;
791 x_headers.freight_term := NULL;
792 x_headers.terms_id := NULL;
793
794 po_vendors_sv.get_vendor_info(x_headers.vendor_id,
795 g_vendor_ship_to_loc_id,
796 g_vendor_bill_to_loc_id,
797 g_ship_via_lookup_code,
798 g_fob, g_freight_term,
799 g_terms_id,
800 x_headers.type_1099,
801 x_headers.hold_flag,
802 x_headers.invoice_currency_code,
803 x_headers.receipt_required_flag,
804 x_headers.num_1099,
805 x_headers.vat_registration_num,
806 x_headers.inspection_required_flag);
807
808 PO_AUTOCREATE_PARAMS.g_vendor_receipt_req_flag := x_headers.receipt_required_flag;
809 PO_AUTOCREATE_PARAMS.g_vendor_inspect_req_flag := x_headers.inspection_required_flag;
810
811
812 IF (x_headers.vendor_site_id IS NOT NULL) THEN
813
814 l_progress := '050';
815 po_vendor_sites_sv.get_vendor_site_info(x_headers.vendor_site_id,
816 g_vendor_ship_to_loc_id,
817 g_vendor_bill_to_loc_id,
818 g_vs_ship_via_lookup_code,
819 g_vs_fob,
820 x_headers.pay_on_code,
821 g_vs_freight_term,
822 g_vs_terms_id,
823 x_headers.invoice_currency_code,
824 x_headers.shipping_control );
825
826 PO_AUTOCREATE_PARAMS. g_vendor_Ship_Via_Lookup_Code := g_vs_ship_via_lookup_code;
827
828 -- Default the pay_on_code for a Standard PO based
829 -- on the vendor site value.
830 IF (x_headers.pay_on_code = 'RECEIPT_AND_USE') THEN
831 x_headers.pay_on_code := 'RECEIPT';
832 ELSIF (x_headers.pay_on_code = 'USE') THEN
833 x_headers.pay_on_code := NULL;
834 END IF;
835
836 END IF;
837 END IF;
838 --Derive vendor related info ends here
839
840 l_progress := '060';
841 IF g_debug_stmt THEN
842 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After deriving the vendor related info');
843 END IF;
844
845 -- Get the conversion rate between PO Currency and Req Functional Currency for
846 -- Default Rate type of Purchasing Org.
847 -- If called from Sourcing, simply assign interface table rate to g_rate_for_req_fields
848 IF (PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'SOURCING' AND PO_AUTOCREATE_PARAMS.g_purchasing_ou_id <> PO_AUTOCREATE_PARAMS.g_hdr_requesting_ou_id AND PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD') THEN
849 BEGIN
850 l_progress := '070';
851
852 IF g_debug_stmt THEN
853 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Before deriving the rate info');
854 END IF;
855
856 SELECT req_fsp.set_of_books_id
857 INTO l_req_ou_sob_id
858 FROM financials_system_params_all req_fsp
859 WHERE req_fsp.org_id = PO_AUTOCREATE_PARAMS.g_hdr_requesting_ou_id;
860
861 l_progress := '071';
862 SELECT po_fsp.set_of_books_id
863 INTO l_po_ou_sob_id
864 FROM financials_system_params_all po_fsp
865 WHERE po_fsp.org_id = PO_AUTOCREATE_PARAMS.g_purchasing_ou_id;
866
867 l_progress := '072';
868 IF PO_AUTOCREATE_PARAMS.g_hdr_requesting_ou_id = PO_AUTOCREATE_PARAMS.g_purchasing_ou_id THEN
869 x_headers.rate := NULL;
870 END IF;
871
872 IF x_headers.rate_type IS NULL THEN
873 l_progress := '073';
874 SELECT default_rate_type
875 INTO l_rate_type
876 FROM po_system_parameters_all psp
877 WHERE psp.org_id = PO_AUTOCREATE_PARAMS.g_purchasing_ou_id;
878 ELSE
879 l_progress := '074';
880 l_rate_type := x_headers.rate_type;
881 END IF;
882
883 IF g_debug_stmt THEN
884 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Getting rate type:'||l_rate_type);
885 END IF;
886
887 -- Returns the conversion rate between PO Currency and Req Functional Currency
888 po_currency_sv.get_rate(PO_AUTOCREATE_PARAMS.g_hdr_requesting_ou_id,
889 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
890 l_rate_type,
891 x_headers.rate_date,
892 'N',
893 PO_AUTOCREATE_PARAMS.g_rate_for_req_fields,
894 l_display_rate);
895
896 l_progress := '075';
897 IF g_debug_stmt THEN
898 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After deriving the rate info: rate ='||PO_AUTOCREATE_PARAMS.g_rate_for_req_fields);
899 END IF;
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 IF g_debug_unexp THEN
904 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
905 END IF;
906 po_message_s.sql_error('GET_RATE_FOR_REQ_PRICE',l_progress,SQLCODE);
907 END;
908
909 -- If g_rate_for_req_fields is NULL, assign interface rate to g_rate_for_req_fields
910 IF PO_AUTOCREATE_PARAMS.g_rate_for_req_fields IS NULL THEN
911 PO_AUTOCREATE_PARAMS.g_rate_for_req_fields := x_headers.rate;
912 END IF;
913
914 ELSE
915 PO_AUTOCREATE_PARAMS.g_rate_for_req_fields := x_headers.rate;
916 END IF;
917
918 -- Derive the rate info ends here
919
920 -- Derive Invoice Match Option
921 IF PO_AUTOCREATE_PARAMS.g_interface_source_code <> 'CONSUMPTION_ADVICE' THEN -- bug 2741985
922 l_progress := '080';
923 get_invoice_match_option(x_headers.vendor_id,
924 x_headers.vendor_site_id,
925 x_headers.invoice_match_option);
926
927 PO_AUTOCREATE_PARAMS.g_vendor_invoice_match_option := x_headers.invoice_match_option;
928
929 END IF;
930
931 l_progress := '090';
932 /* Defaulting attributes - start */
933 -- Defaulting global_aggrement_flag
934 IF PO_AUTOCREATE_PARAMS.g_mode = 'NEW' THEN
935 l_progress := '100';
936 IF PO_AUTOCREATE_PARAMS.g_document_subtype = 'BLANKET' THEN
937 IF x_headers.global_agreement_flag = 'N' THEN
938 x_headers.global_agreement_flag := NULL;
939 END IF;
940 IF x_headers.global_agreement_flag = 'Y' THEN
941 x_headers.min_release_amount := NULL;
942 END IF;
943 END IF; -- x_headers.document_subtype = 'BLANKET'
944 END IF; --PO_AUTOCREATE_PARAMS.g_mode = 'NEW'
945
946 -- Defaulting acceptance_required_flag
947 IF PO_AUTOCREATE_PARAMS.g_mode = 'NEW' THEN
948 l_progress := '110';
949 IF (PO_AUTOCREATE_PARAMS.g_document_type IN ('PO','PA')) THEN
950 x_headers.acceptance_required_flag := NVL(x_headers.acceptance_required_flag, NVL(PO_AUTOCREATE_PARAMS.g_sys.acceptance_required_flag,'N'));
951 ELSE
952 x_headers.acceptance_required_flag := PO_AUTOCREATE_PARAMS.g_sys.acceptance_required_flag;
953 END IF;
954 END IF;
955 -- End Defaulting acceptance_required_flag
956
957 l_progress := '120';
958 -- Defaulting who columns
959 x_headers.last_update_date := SYSDATE;
960 x_headers.last_updated_by := FND_GLOBAL.user_id;
961 x_headers.last_update_login := FND_GLOBAL.login_id;
962 x_headers.creation_date := SYSDATE;
963 x_headers.created_by := FND_GLOBAL.user_id;
964 x_headers.request_id := FND_GLOBAL.conc_request_id;
965
966 /* bug 12987412 : While autocreating req to award, the clm_contract
967 officer was not getting defaulted.
968 If new award is created , default from preferences.
969 IF adding to existing award , then default from base document.
970 x_headers.clm_contract_officer will be null whle creating new award.*/
971 IF x_headers.clm_contract_officer IS NULL THEN
972 l_progress := '130';
973 x_fnd_user := po_employees_sv.get_employee(x_emp_id,
974 x_emp_name,
975 x_location_id,
976 x_location_code,
977 x_is_buyer,
978 x_emp_flag);
979 x_headers.clm_contract_officer := to_number(po_core_s.get_default_contract_officer(FND_GLOBAL.user_id,
980 x_emp_id));
981 END IF;
982 /*bug 12987412*/
983 IF g_debug_stmt THEN
984 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
985 END IF;
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 IF g_debug_unexp THEN --< Bug 3210331: use proper debugging >
990 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
991 END IF;
992
993 --CLM Phase 2 changes : error handling
994 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HEADER_DEFAULT_ERR',x_token1_value => sqlerrm);
995
996 po_message_s.sql_error('DERIVE_AND_DEFAULT_HEADERS',l_progress,SQLCODE);
997 PO_AUTOCREATE_PVT.wrapup();
998 RAISE;
999 END derive_and_default_headers;
1000 /* ============================================================================
1001 NAME: Validate_header
1002 DESC: Validate header attributes.
1003 1. Validate the following attributes.
1004 freight_carrier
1005 fob
1006 freight_terms
1007 ap_terms
1008 First validate the vendor site attribute. If it is valid use it.
1009 Otherwise, validate the vendor attribute. If it is valid then use
1010 that. if that is also not valid, then leave the value to be null.
1011 2. validate ship to location and bill to location
1012 ARGS: IN OUT : x_headers PO_AUTOCREATE_TYPES.headers_rec_type -Record variable to hold the header info
1013 NOTE: Use the following check to validate ship_to and bill_to locations:
1014 select 'Y' into x_is_valid
1015 from hr_locations_all
1016 where location_id = x_valid_ship_to/ x_valid_bill_to
1017 and NVL(ship_to_site_flag, 'N') = 'Y'
1018 and NVL(trunc(inactive_date),trunc(SYSDATE)+1) > trunc(SYSDATE);
1019 ==============================================================================*/
1020 PROCEDURE validate_header( x_headers IN OUT NOCOPY PO_AUTOCREATE_TYPES.headers_rec_type)
1021 IS
1022
1023 l_progress VARCHAR2(3) := '000';
1024 l_api_name CONSTANT VARCHAR2(30) := 'validate_header';
1025 x_valid_ship_to po_headers.ship_to_location_id%TYPE;
1026 x_valid_bill_to po_headers.bill_to_location_id%TYPE;
1027 x_is_valid VARCHAR(1) := 'N';
1028
1029 BEGIN
1030
1031 IF g_debug_stmt THEN
1032 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1033 END IF;
1034
1035 -- Validate ship_to and bill_to
1036 IF (PO_AUTOCREATE_PARAMS.g_interface_source_code = 'SOURCING') THEN
1037 x_valid_ship_to := NVL(x_headers.ship_to_loc_id, NVL(g_vendor_ship_to_loc_id, PO_AUTOCREATE_PARAMS.g_sys.ship_to_location_id));
1038 x_valid_bill_to := NVL(x_headers.bill_to_loc_id, NVL(g_vendor_bill_to_loc_id, PO_AUTOCREATE_PARAMS.g_sys.bill_to_location_id));
1039 ELSE
1040 x_valid_ship_to := NVL(g_vendor_ship_to_loc_id, x_headers.ship_to_loc_id);
1041 x_valid_bill_to := NVL(g_vendor_bill_to_loc_id, x_headers.bill_to_loc_id);
1042 END IF;
1043
1044 l_progress:= '010';
1045 BEGIN
1046 SELECT 'Y'
1047 INTO x_is_valid
1048 FROM hr_locations_all
1049 WHERE location_id = x_valid_ship_to
1050 AND NVL(ship_to_site_flag, 'N') = 'Y'
1051 AND NVL(TRUNC(inactive_date),TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
1052 EXCEPTION
1053 WHEN NO_DATA_FOUND THEN
1054 IF g_debug_stmt THEN
1055 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
1056 END IF;
1057 x_is_valid :='N';
1058 x_valid_ship_to := NULL;
1059 WHEN OTHERS THEN
1060 IF g_debug_unexp THEN
1061 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1062 END IF;
1063 raise;
1064 END;
1065
1066 IF g_debug_stmt THEN
1067 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After validating ship_to : x_is_valid :'||x_is_valid);
1068 END IF;
1069
1070 l_progress:= '020';
1071 BEGIN
1072 SELECT 'Y'
1073 INTO x_is_valid
1074 FROM hr_locations_all
1075 WHERE location_id = x_valid_bill_to
1076 AND NVL(bill_to_site_flag, 'N') = 'Y' --bug 4229954
1077 AND NVL(TRUNC(inactive_date),TRUNC(SYSDATE)+1) > TRUNC(SYSDATE);
1078 EXCEPTION
1079 WHEN NO_DATA_FOUND THEN
1080 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
1081 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'NO_DATA_FOUND: '||SQLERRM);
1082 END IF;
1083 x_is_valid :='N';
1084 x_valid_bill_to := NULL;
1085 WHEN OTHERS THEN
1086 IF g_debug_unexp THEN --< Bug 3210331: use proper debugging >
1087 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1088 END IF;
1089 raise;
1090 END;
1091
1092 IF g_debug_stmt THEN
1093 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After validating bill_to_to : x_is_valid :'||x_is_valid);
1094 END IF;
1095
1096
1097 --Bug 14369833
1098 --if x_valid_ship_to and x_valid_bill_to is null, need to default them from
1099 -- financials_system_parameters.this is done to maintain consistency with the other
1100 -- flows,i.e when creating a an award from BWC, when the user clicks on Create
1101 -- the ship_to_loc_id, bill_to_loc_id are defaulted from financials_system_parameters
1102 IF x_valid_ship_to IS null THEN
1103 SELECT ship_to_location_id
1104 INTO x_valid_ship_to
1105 FROM financials_system_parameters ;
1106 END IF;
1107
1108 IF x_valid_bill_to IS null THEN
1109 SELECT bill_to_location_id
1110 INTO x_valid_bill_to
1111 FROM financials_system_parameters ;
1112 END IF;
1113
1114 -- Storing the valid ship_to and bill_to
1115 x_headers.ship_to_loc_id := x_valid_ship_to;
1116 x_headers.bill_to_loc_id := x_valid_bill_to;
1117 -- Validate ship_to and bill_to ends here
1118
1119 l_progress:= '030';
1120 /* =========================================================================
1121 ** Validate the following attributes:
1122 **
1123 ** - freight_carrier
1124 ** - fob
1125 ** - freight_terms
1126 ** - ap_terms
1127 **
1128 ========================================================================== */
1129 -- Validate ship via
1130 IF g_vs_ship_via_lookup_code IS NOT NULL THEN
1131 po_vendors_sv.val_freight_carrier(g_vs_ship_via_lookup_code, PO_AUTOCREATE_PARAMS.g_sys.def_inv_org_id, x_headers.ship_via_lookup_code);
1132 END IF;
1133 IF x_headers.ship_via_lookup_code IS NULL THEN
1134 po_vendors_sv.val_freight_carrier(g_ship_via_lookup_code, PO_AUTOCREATE_PARAMS.g_sys.def_inv_org_id, x_headers.ship_via_lookup_code);
1135 END IF;
1136
1137 l_progress := '040';
1138 -- Validate fob code
1139 IF g_vs_fob IS NOT NULL THEN
1140 po_vendors_sv.val_fob(g_vs_fob,x_headers.fob);
1141 END IF;
1142 IF x_headers.fob IS NULL THEN
1143 po_vendors_sv.val_fob(g_fob,x_headers.fob);
1144 END IF;
1145
1146 l_progress := '050';
1147 -- Validate freight terms
1148 IF g_vs_freight_term IS NOT NULL THEN
1149 po_vendors_sv.val_freight_terms(g_vs_freight_term, x_headers.freight_term);
1150 END IF;
1151 IF x_headers.freight_term IS NULL THEN
1152 po_vendors_sv.val_freight_terms(g_freight_term, x_headers.freight_term);
1153 END IF;
1154
1155 -- Validate payment terms
1156 IF g_vs_terms_id IS NOT NULL THEN
1157 l_progress := '060';
1158 po_terms_sv.val_ap_terms(g_vs_terms_id,x_headers.terms_id);
1159 END IF;
1160
1161 IF x_headers.terms_id IS NULL THEN
1162 l_progress := '070';
1163 po_terms_sv.val_ap_terms(g_terms_id,x_headers.terms_id);
1164 END IF;
1165
1166 /* Validation of vendor related attributes ends here */
1167 l_progress := '080';
1168 IF g_debug_stmt THEN
1169 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1170 END IF;
1171
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 IF g_debug_unexp THEN
1175 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1176 END IF;
1177
1178 --CLM Phase 2 changes : error handling
1179 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HEADER_VALIDATE_ERR',x_token1_value => sqlerrm);
1180
1181 po_message_s.sql_error('VALIDATE_HEADER',l_progress,SQLCODE);
1182 PO_AUTOCREATE_PVT.wrapup();
1183 RAISE;
1184 END validate_header;
1185
1186 /* ============================================================================
1187 NAME: merge_to_headers_draft
1188 DESC: Insert/update header records into po_headers_draft_all table.
1189 ARGS: IN OUT : x_headers PO_AUTOCREATE_TYPES.headers_rec_type
1190 NOTE: if NEW DOCUMENT
1191 1. Create a draft record
1192 2. Create headers_draft record
1193 else if ADD TO DOCUMENT
1194 if there is no draft already exists for this document
1195 1. create a new draft record.
1196 2. popuate the headers draft from the main table.
1197 3. update (if required) the headers draft using headers_rec_type
1198 else if there is a draft already exists
1199 1. update (if required) the draft record.
1200 2. update (if required) the headers draft record using headers_rec_type
1201 end;
1202 end;
1203 ===========================================================================*/
1204 PROCEDURE merge_to_headers_draft(x_headers IN OUT NOCOPY PO_AUTOCREATE_TYPES.headers_rec_type)
1205 IS
1206
1207 l_progress VARCHAR2(3) := '000';
1208 l_api_name CONSTANT VARCHAR2(30) := 'merge_to_headers_draft';
1209 x_draft_id PO_DRAFTS.draft_id%TYPE;
1210 l_draft_type VARCHAR2(3) := NULL; --bug 16425245
1211 l_create_doc_from_draft VARCHAR2(1) := 'Y'; --bug 16425245
1212
1213 BEGIN
1214
1215 IF g_debug_stmt THEN
1216 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1217 END IF;
1218
1219 IF PO_AUTOCREATE_PARAMS.g_mode = 'ADD' THEN
1220
1221 /*If we are in the ADD mode then we are either adding lines to an
1222 Award or Modification
1223 The draft_id gets passed to backend code from the UI.
1224 If lines are being added to Modification g_draft_id will have the
1225 value of the draft_id of the modification
1226 else g_draft_id will be -1*/
1227 IF PO_AUTOCREATE_PARAMS.g_draft_id > -1 THEN -- Draft exists
1228 l_progress := '020';
1229 -- Indecates Draft exists for that document
1230 PO_AUTOCREATE_PARAMS.g_is_mod_exists := TRUE;
1231 -- Use the existing draft
1232 x_headers.draft_id := PO_AUTOCREATE_PARAMS.g_draft_id;
1233
1234 -- Update the PO drafts
1235 UPDATE po_drafts
1236 SET last_update_date = x_headers.last_update_date,
1237 last_updated_by = x_headers.last_updated_by,
1238 created_by = x_headers.created_by,
1239 last_update_login = x_headers.last_update_login
1240 WHERE draft_id = x_headers.draft_id;
1241
1242 l_progress := '021';
1243 -- Update the PO Headers Draft
1244 UPDATE po_headers_draft_all
1245 SET last_update_date = x_headers.last_update_date,
1246 last_updated_by = x_headers.last_updated_by,
1247 last_update_login = x_headers.last_update_login
1248 WHERE draft_id = x_headers.draft_id;
1249
1250 IF g_debug_stmt THEN
1251 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'After updating the existing draft');
1252 END IF;
1253
1254 ELSE -- Draft not exists PO_AUTOCREATE_PARAMS.g_draft_id is null or -1
1255 l_progress := '030';
1256
1257 --bug 16425245
1258 l_create_doc_from_draft := PO_AUTOCREATE_POSTPROC_PVT.create_doc_from_draft_check(x_headers.po_header_id);
1259 IF l_create_doc_from_draft ='Y' THEN
1260 l_draft_type := NULL;
1261 ELSE
1262 l_draft_type := 'MOD';
1263 END IF;
1264 -- bug 16425245
1265
1266 -- Get the new Draft Id
1267 x_headers.draft_id := PO_DRAFTS_PVT.draft_id_nextval;
1268 po_autocreate_params.g_draft_id := x_headers.draft_id;
1269 -- Indicates Draft exists for that document
1270 PO_AUTOCREATE_PARAMS.g_is_mod_exists := FALSE;
1271
1272 IF g_debug_stmt THEN
1273 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Getting new draft id for existing document:'||x_headers.draft_id);
1274 END IF;
1275
1276 -- If draft not exists then create a new entry in drafts and headers
1277 -- draft table.
1278 -- Insert into Draft table
1279 INSERT
1280 INTO po_drafts
1281 (
1282 draft_id,
1283 document_id,
1284 revision_num,
1285 owner_user_id,
1286 owner_role,
1287 status,
1288 draft_type,
1289 last_update_date,
1290 last_updated_by,
1291 last_update_login,
1292 creation_date,
1293 created_by,
1294 request_id,
1295 agent_id, --Bug13547051 Mod Buyer and CLM_contract_officer are not defaulted.
1296 clm_contract_officer --Bug13547051
1297 )
1298 VALUES
1299 (
1300 x_headers.draft_id,
1301 x_headers.po_header_id,
1302 NULL,
1303 FND_GLOBAL.user_id,
1304 'BUYER',
1305 'DRAFT',
1306 l_draft_type, -- bug 16425245
1307 x_headers.last_update_date,
1308 x_headers.last_updated_by,
1309 x_headers.last_update_login,
1310 x_headers.creation_date,
1311 x_headers.created_by,
1312 x_headers.request_id,
1313 x_headers.agent_id, --Bug13547051
1314 x_headers.clm_contract_officer --Bug13547051
1315 );
1316
1317 l_progress := '040';
1318 -- Insert esisting records from PO into po_headers_draft_all
1319 INSERT
1320 INTO po_headers_draft_all
1321 (
1322 draft_id,
1323 org_id,
1324 delete_flag,
1325 change_accepted_flag,
1326 po_header_id,
1327 agent_id,
1328 type_lookup_code,
1329 last_update_date,
1330 last_updated_by,
1331 segment1,
1332 summary_flag,
1333 enabled_flag,
1334 segment2,
1335 segment3,
1336 segment4,
1337 segment5,
1338 start_date_active,
1339 end_date_active,
1340 last_update_login,
1341 creation_date,
1342 created_by,
1343 vendor_id,
1344 vendor_site_id,
1345 vendor_contact_id,
1346 ship_to_location_id,
1347 bill_to_location_id,
1348 terms_id,
1349 ship_via_lookup_code,
1350 fob_lookup_code,
1351 freight_terms_lookup_code,
1352 status_lookup_code,
1353 currency_code,
1354 rate_type,
1355 rate_date,
1356 rate,
1357 from_header_id,
1358 from_type_lookup_code,
1359 start_date,
1360 end_date,
1361 blanket_total_amount,
1362 authorization_status,
1363 revision_num,
1364 revised_date,
1365 approved_flag,
1366 approved_date,
1367 amount_limit,
1368 min_release_amount,
1369 note_to_authorizer,
1370 note_to_vendor,
1371 note_to_receiver,
1372 print_count,
1373 printed_date,
1374 vendor_order_num,
1375 confirming_order_flag,
1376 comments,
1377 reply_date,
1378 reply_method_lookup_code,
1379 rfq_close_date,
1380 quote_type_lookup_code,
1381 quotation_class_code,
1382 quote_warning_delay,
1383 quote_vendor_quote_number,
1384 acceptance_required_flag,
1385 acceptance_due_date,
1386 closed_date,
1387 user_hold_flag,
1388 approval_required_flag,
1389 cancel_flag,
1390 firm_status_lookup_code,
1391 firm_date,
1392 frozen_flag,
1393 attribute_category,
1394 attribute1,
1395 attribute2,
1396 attribute3,
1397 attribute4,
1398 attribute5,
1399 attribute6,
1400 attribute7,
1401 attribute8,
1402 attribute9,
1403 attribute10,
1404 attribute11,
1405 attribute12,
1406 attribute13,
1407 attribute14,
1408 attribute15,
1409 closed_code,
1410 government_context,
1411 request_id,
1412 program_application_id,
1413 program_id,
1414 program_update_date,
1415 interface_source_code,
1416 reference_num,
1417 pay_on_code,
1418 quote_warning_delay_unit,
1419 global_agreement_flag,
1420 shipping_control,
1421 encumbrance_required_flag,
1422 document_creation_method,
1423 -- new columns in R12
1424 style_id,
1425 created_language,
1426 tax_attribute_update_code,
1427 clm_standard_form,
1428 clm_document_format,
1429 clm_award_type,
1430 conterms_exist_flag,
1431 clm_document_number,
1432 clm_source_document_id,
1433 clm_contract_officer,
1434 /* bug 12987412 Stamp the clm_contract_officer
1435 into the drafts table, which will eventually
1436 get stamped onto the PO txn tables*/
1437 umbrella_program_id, --umbrella program
1438 fon_ref_id, --umbrella program
1439 supplier_notif_method, --Bug13547051 In add to mode the communication should be copied from the base award.
1440 fax, --Bug13547051
1441 email_address, --Bug13547051
1442 clm_effective_date --Bug13547051 In add to mode clm_effective_date should be copied from base award.
1443 )
1444 SELECT x_headers.draft_id,
1445 org_id,
1446 NULL,
1447 NULL,
1448 po_header_id,
1449 agent_id,
1450 type_lookup_code,
1451 last_update_date,
1452 last_updated_by,
1453 segment1,
1454 summary_flag,
1455 enabled_flag,
1456 segment2,
1457 segment3,
1458 segment4,
1459 segment5,
1460 start_date_active,
1461 end_date_active,
1462 last_update_login,
1463 creation_date,
1464 created_by,
1465 vendor_id,
1466 vendor_site_id,
1467 vendor_contact_id,
1468 ship_to_location_id,
1469 bill_to_location_id,
1470 terms_id,
1471 ship_via_lookup_code,
1472 fob_lookup_code,
1473 freight_terms_lookup_code,
1474 status_lookup_code,
1475 currency_code,
1476 rate_type,
1477 rate_date,
1478 rate,
1479 from_header_id,
1480 from_type_lookup_code,
1481 start_date,
1482 end_date,
1483 blanket_total_amount,
1484 authorization_status,
1485 revision_num,
1486 revised_date,
1487 approved_flag,
1488 approved_date,
1489 amount_limit,
1490 min_release_amount,
1491 note_to_authorizer,
1492 note_to_vendor,
1493 note_to_receiver,
1494 print_count,
1495 printed_date,
1496 vendor_order_num,
1497 confirming_order_flag,
1498 comments,
1499 reply_date,
1500 reply_method_lookup_code,
1501 rfq_close_date,
1502 quote_type_lookup_code,
1503 quotation_class_code,
1504 quote_warning_delay,
1505 quote_vendor_quote_number,
1506 acceptance_required_flag,
1507 acceptance_due_date,
1508 closed_date,
1509 user_hold_flag,
1510 approval_required_flag,
1511 cancel_flag,
1512 firm_status_lookup_code,
1513 firm_date,
1514 frozen_flag,
1515 attribute_category,
1516 attribute1,
1517 attribute2,
1518 attribute3,
1519 attribute4,
1520 attribute5,
1521 attribute6,
1522 attribute7,
1523 attribute8,
1524 attribute9,
1525 attribute10,
1526 attribute11,
1527 attribute12,
1528 attribute13,
1529 attribute14,
1530 attribute15,
1531 closed_code,
1532 government_context,
1533 request_id,
1534 program_application_id,
1535 program_id,
1536 program_update_date,
1537 interface_source_code,
1538 reference_num,
1539 pay_on_code,
1540 quote_warning_delay_unit,
1541 global_agreement_flag,
1542 shipping_control,
1543 encumbrance_required_flag,
1544 document_creation_method,
1545 style_id,
1546 created_language,
1547 tax_attribute_update_code,
1548 clm_standard_form,
1549 clm_document_format,
1550 clm_award_type,
1551 conterms_exist_flag,
1552 clm_document_number,
1553 clm_source_document_id,
1554 clm_contract_officer,
1555 /* bug 12987412 since we are adding the to existing award,
1556 use value from base document*/
1557 umbrella_program_id, --umbrella program
1558 fon_ref_id, --umbrella program
1559 supplier_notif_method, --Bug13547051 In add to mode the communication should be copied from the base award.
1560 fax, --Bug13547051
1561 email_address, --Bug13547051
1562 clm_effective_date --Bug13547051
1563 FROM po_headers_all
1564 WHERE po_header_id = x_headers.po_header_id;
1565
1566 l_progress := '050';
1567 UPDATE po_headers_draft_all
1568 SET last_update_date = x_headers.last_update_date,
1569 last_updated_by = x_headers.last_updated_by,
1570 authorization_status = NULL,
1571 last_update_login = x_headers.last_update_login
1572 WHERE po_header_id = x_headers.po_header_id;
1573
1574 END IF; -- Draft Exists
1575
1576 ELSE -- g_mode = 'NEW'
1577
1578 l_progress := '060';
1579 -- Get the new Draft Id
1580 x_headers.draft_id := PO_DRAFTS_PVT.draft_id_nextval;
1581 po_autocreate_params.g_draft_id := x_headers.draft_id;
1582 PO_FED_FIELD_FUNCTIONS.XPD_DRAFT_ID := x_headers.draft_id; --Bug 11894977
1583 -- Indecates Draft exists for that document
1584 PO_AUTOCREATE_PARAMS.g_is_mod_exists := FALSE;
1585
1586 IF g_debug_stmt THEN
1587 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name, p_token => l_progress, p_message => 'Getting new draft id for new document:'||x_headers.draft_id);
1588 END IF;
1589 -- Create a new entry in drafts and headers draft table
1590 -- for new document
1591 -- Insert into Draft table
1592 INSERT
1593 INTO po_drafts
1594 (
1595 draft_id,
1596 document_id,
1597 revision_num,
1598 owner_user_id,
1599 owner_role,
1600 status,
1601 last_update_date,
1602 last_updated_by,
1603 last_update_login,
1604 creation_date,
1605 created_by,
1606 request_id
1607 )
1608 VALUES
1609 (
1610 x_headers.draft_id,
1611 x_headers.po_header_id,
1612 0,
1613 FND_GLOBAL.user_id,
1614 'BUYER',
1615 'DRAFT',
1616 x_headers.last_update_date,
1617 x_headers.last_updated_by,
1618 x_headers.last_update_login,
1619 x_headers.creation_date,
1620 x_headers.created_by,
1621 x_headers.request_id
1622 );
1623
1624 l_progress := '040';
1625 -- Insert new documents attribute values into po_headers_draft_all
1626 INSERT
1627 INTO po_headers_draft_all
1628 (
1629 draft_id,
1630 org_id,
1631 delete_flag,
1632 change_accepted_flag,
1633 po_header_id,
1634 agent_id,
1635 type_lookup_code,
1636 last_update_date,
1637 last_updated_by,
1638 segment1,
1639 summary_flag,
1640 enabled_flag,
1641 segment2,
1642 segment3,
1643 segment4,
1644 segment5,
1645 start_date_active,
1646 end_date_active,
1647 last_update_login,
1648 creation_date,
1649 created_by,
1650 vendor_id,
1651 vendor_site_id,
1652 vendor_contact_id,
1653 ship_to_location_id,
1654 bill_to_location_id,
1655 terms_id,
1656 ship_via_lookup_code,
1657 fob_lookup_code,
1658 freight_terms_lookup_code,
1659 status_lookup_code,
1660 currency_code,
1661 rate_type,
1662 rate_date,
1663 rate,
1664 from_header_id,
1665 from_type_lookup_code,
1666 start_date,
1667 end_date,
1668 blanket_total_amount,
1669 authorization_status,
1670 revision_num,
1671 revised_date,
1672 approved_flag,
1673 approved_date,
1674 amount_limit,
1675 min_release_amount,
1676 note_to_authorizer,
1677 note_to_vendor,
1678 note_to_receiver,
1679 print_count,
1680 printed_date,
1681 vendor_order_num,
1682 confirming_order_flag,
1683 comments,
1684 reply_date,
1685 reply_method_lookup_code,
1686 rfq_close_date,
1687 quote_type_lookup_code,
1688 quotation_class_code,
1689 quote_warning_delay,
1690 quote_vendor_quote_number,
1691 acceptance_required_flag,
1692 acceptance_due_date,
1693 closed_date,
1694 user_hold_flag,
1695 approval_required_flag,
1696 cancel_flag,
1697 firm_status_lookup_code,
1698 firm_date,
1699 frozen_flag,
1700 attribute_category,
1701 attribute1,
1702 attribute2,
1703 attribute3,
1704 attribute4,
1705 attribute5,
1706 attribute6,
1707 attribute7,
1708 attribute8,
1709 attribute9,
1710 attribute10,
1711 attribute11,
1712 attribute12,
1713 attribute13,
1714 attribute14,
1715 attribute15,
1716 closed_code,
1717 government_context,
1718 request_id,
1719 program_application_id,
1720 program_id,
1721 program_update_date,
1722 interface_source_code,
1723 reference_num,
1724 pay_on_code,
1725 quote_warning_delay_unit,
1726 global_agreement_flag,
1727 shipping_control,
1728 encumbrance_required_flag,
1729 document_creation_method,
1730 -- new columns in R12
1731 style_id,
1732 created_language,
1733 tax_attribute_update_code,
1734 clm_standard_form,
1735 clm_document_format,
1736 clm_award_type,
1737 conterms_exist_flag,
1738 -- CLM specific attributes
1739 clm_source_document_id,
1740 clm_effective_date,
1741 clm_vendor_offer_number,
1742 clm_award_administrator,
1743 clm_no_signed_copies_to_return,
1744 clm_min_guarantee_award_amt,
1745 clm_min_guar_award_amt_percent,
1746 clm_min_order_amount,
1747 clm_max_order_amount,
1748 clm_amount_released,
1749 clm_external_idv,
1750 clm_contract_officer,
1751 /* bug 12987412 Stamp the clm_contract_officer
1752 into the drafts table, which will eventually
1753 get stamped onto the PO txn tables*/
1754 umbrella_program_id, --umbrella program
1755 fon_ref_id --umbrella program
1756 )
1757 SELECT x_headers.draft_id,
1758 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
1759 NULL, -- delete_flag
1760 NULL, -- change_accepted_flag
1761 x_headers.po_header_id,
1762 x_headers.agent_id,
1763 x_headers.doc_subtype,
1764 x_headers.last_update_date,
1765 x_headers.last_updated_by,
1766 x_headers.document_num,
1767 'N', -- summary flag
1768 'Y', -- enabled_flag,
1769 NULL, -- segment2,
1770 NULL, -- segment3,
1771 NULL, -- segment4,
1772 NULL, -- segment5,
1773 NULL, -- start_date_active,
1774 NULL, -- end_date_active,
1775 x_headers.last_update_login,
1776 x_headers.creation_date,
1777 x_headers.created_by,
1778 x_headers.vendor_id,
1779 x_headers.vendor_site_id,
1780 x_headers.vendor_contact_id,
1781 x_headers.ship_to_loc_id,
1782 x_headers.bill_to_loc_id,
1783 x_headers.terms_id,
1784 x_headers.freight_carrier,
1785 x_headers.fob,
1786 x_headers.freight_term,
1787 x_headers.status_lookup_code,
1788 x_headers.currency_code,
1789 x_headers.rate_type_code,
1790 TRUNC(x_headers.rate_date),
1791 x_headers.rate,
1792 x_headers.from_header_id,
1793 x_headers.from_type_lookup_code,
1794 TRUNC(effective_date),
1795 TRUNC(expiration_date),
1796 amount_agreed,
1797 'INCOMPLETE', -- x_headers.authorization_status,
1798 0,
1799 revised_date,
1800 'N',
1801 x_headers.approved_date,
1802 x_headers.amount_limit, -- bug5352625
1803 x_headers.min_release_amount,
1804 NULL, -- note_to_authorizer,
1805 note_to_vendor,
1806 note_to_receiver,
1807 x_headers.print_count,
1808 printed_date,
1809 x_headers.vendor_order_num,
1810 x_headers.confirming_order_flag,
1811 comments,
1812 TRUNC(x_headers.reply_date),
1813 reply_method,
1814 TRUNC(rfq_close_date),
1815 x_headers.doc_subtype,
1816 x_headers.quotation_class_code,
1817 x_headers.quote_warning_delay,
1818 x_headers.quote_vendor_quote_num,
1819 x_headers.acceptance_required_flag,
1820 TRUNC(x_headers.acceptance_due_date),
1821 NULL, -- closed date
1822 NULL, -- user_hold_flag,
1823 x_headers.approval_required_flag,
1824 x_headers.cancel_flag,
1825 NULL, -- firm_status_lookup_code,
1826 NULL, -- firm_date,
1827 x_headers.frozen_flag,
1828 attribute_category,
1829 attribute1,
1830 attribute2,
1831 attribute3,
1832 attribute4,
1833 attribute5,
1834 attribute6,
1835 attribute7,
1836 attribute8,
1837 attribute9,
1838 attribute10,
1839 attribute11,
1840 attribute12,
1841 attribute13,
1842 attribute14,
1843 attribute15,
1844 'OPEN',
1845 NULL, -- government_context,
1846 x_headers.request_id,
1847 x_headers.program_application_id,
1848 x_headers.program_id,
1849 x_headers.program_update_date,
1850 interface_source_code,
1851 reference_num,
1852 x_headers.pay_on_code,
1853 NULL, -- quote_warning_delay_unit,
1854 x_headers.global_agreement_flag,
1855 x_headers.shipping_control,
1856 x_headers.encumbrance_required_flag,
1857 x_headers.doc_creation_method,
1858 -- new columns added in R12
1859 x_headers.style_id,
1860 x_headers.created_language,
1861 x_headers.tax_attribute_update_code,
1862 x_headers.clm_standard_form,
1863 x_headers.clm_document_format,
1864 x_headers.clm_award_type,
1865 PO_AUTOCREATE_PARAMS.x_conterms_exist_flag,
1866 -- CLM specific attributes
1867 x_headers.clm_source_document_id,
1868 x_headers.clm_effective_date,
1869 x_headers.clm_vendor_offer_number,
1870 x_headers.clm_award_administrator,
1871 x_headers.clm_no_signed_copies_to_return,
1872 x_headers.clm_min_guarantee_award_amt,
1873 x_headers.clm_min_guar_award_amt_percent,
1874 x_headers.clm_min_order_amount,
1875 x_headers.clm_max_order_amount,
1876 x_headers.clm_amount_released,
1877 x_headers.clm_external_idv,
1878 x_headers.clm_contract_officer,
1879 /* bug 12987412 new award is created hence use the value from
1880 x_headers.clm_contract_officer */
1881 x_headers.umbrella_program_id, --umbrella program
1882 x_headers.fon_ref_id --umbrella program
1883 FROM po_headers_interface
1884 WHERE interface_header_id = x_headers.intf_header_id;
1885
1886 END IF; -- g_mode = 'ADD'
1887
1888 l_progress := '050';
1889 po_autocreate_params.g_po_header_id := x_headers.po_header_id;
1890
1891 -- The following is needed during lines processing. (Fetch)
1892 UPDATE po_headers_interface
1893 SET po_header_id = x_headers.po_header_id ,
1894 draft_id = x_headers.draft_id
1895 WHERE interface_header_id = x_headers.intf_header_id;
1896
1897 IF g_debug_stmt THEN
1898 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1899 END IF;
1900
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903 IF g_debug_unexp THEN
1904 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1905 END IF;
1906
1907 --CLM Phase 2 changes : error handling
1908 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HEADER_MERGE_ERR',x_token1_value => sqlerrm);
1909
1910 po_message_s.sql_error('MERGE_TO_HEADERS_DRAFT',l_progress,SQLCODE);
1911 PO_AUTOCREATE_PVT.wrapup();
1912 RAISE;
1913 END merge_to_headers_draft;
1914
1915 /* ============================================================================
1916 NAME: merge_to_org_assign_draft
1917 DESC: Insert rows into po_ga_org_assign draft table;
1918 This applies only to global blanket
1919 ARGS: IN OUT : x_headers PO_AUTOCREATE_TYPES.headers_rec_type
1920 NOTE: if NEW DOCUMENT
1921 1. Create a ga_org_assignments_draft record.
1922 end;
1923 There would not be an ADD TO scenario for GBPA. So the rest of the
1924 if-else is not required.
1925 ==============================================================================*/
1926 PROCEDURE merge_to_org_assign_draft(x_headers IN OUT NOCOPY PO_AUTOCREATE_TYPES.headers_rec_type)
1927 IS
1928
1929 l_progress VARCHAR2(3) := '000';
1930 l_api_name CONSTANT VARCHAR2(30) := 'merge_to_org_assign_draft';
1931
1932 BEGIN
1933
1934 IF g_debug_stmt THEN
1935 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
1936 END IF;
1937
1938 -- For new document, create a ga_org_assignments_draft record
1939 IF PO_AUTOCREATE_PARAMS.g_document_type = 'PA' AND PO_AUTOCREATE_PARAMS.g_mode = 'NEW' THEN
1940 l_progress:= '010';
1941 -- Insert record into po_ga_org_assign_draft
1942 INSERT
1943 INTO po_ga_org_assign_draft
1944 (
1945 draft_id,
1946 delete_flag,
1947 change_accepted_flag,
1948 po_header_id,
1949 organization_id,
1950 enabled_flag,
1951 vendor_site_id,
1952 purchasing_org_id,
1953 org_assignment_id,
1954 last_update_date,
1955 last_updated_by,
1956 last_update_login,
1957 creation_date,
1958 created_by
1959 )
1960 SELECT x_headers.draft_id,
1961 NULL, -- delete_flag,
1962 NULL, -- change_accepted_flag,
1963 x_headers.po_header_id,
1964 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
1965 x_headers.global_agreement_flag,
1966 x_headers.vendor_site_id,
1967 PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
1968 PO_GA_ORG_ASSIGNMENTS_S.nextval, -- org_assignment_id,
1969 x_headers.last_update_date,
1970 x_headers.last_updated_by,
1971 x_headers.last_update_login,
1972 x_headers.creation_date,
1973 x_headers.created_by
1974 FROM po_headers_interface
1975 WHERE interface_header_id = x_headers.intf_header_id
1976 AND x_headers.doc_type = 'PA'
1977 AND NVL(x_headers.global_agreement_flag, 'N') = 'Y';
1978
1979 END IF;
1980
1981 IF g_debug_stmt THEN
1982 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
1983 END IF;
1984
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987
1988 IF g_debug_unexp THEN
1989 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
1990 END IF;
1991
1992 --CLM Phase 2 changes : error handling
1993 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HDR_MRG_ORG_ASSIGN_ERR',x_token1_value => sqlerrm);
1994
1995 po_message_s.sql_error('MERGE_TO_ORG_ASSIGN_DRAFT',l_progress,SQLCODE);
1996 PO_AUTOCREATE_PVT.wrapup();
1997 RAISE;
1998 END merge_to_org_assign_draft;
1999
2000 /* =============================================================================
2001 NAME: get_invoice_match_option
2002 DESC: Derive invoice_match_option for a given vendor and vendor site
2003 ARGS: IN : x_vendor_id IN NUMBER - Vendor Id.
2004 x_vendor_site_id IN NUMBER - Vendor Site Id.
2005 OUT : x_invoice_match_option OUT NOCOPY VARCHAR2 - Invoice match option
2006 ============================================================================= */
2007 PROCEDURE get_invoice_match_option( x_vendor_id IN NUMBER,
2008 x_vendor_site_id IN NUMBER,
2009 x_invoice_match_option OUT NOCOPY VARCHAR2)
2010 IS
2011 l_progress VARCHAR2(3) := '000';
2012 l_api_name CONSTANT VARCHAR2(30) := 'get_invoice_match_option';
2013
2014 BEGIN
2015
2016 IF g_debug_stmt THEN
2017 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
2018 END IF;
2019
2020 IF (X_vendor_site_id IS NOT NULL) THEN
2021 l_progress := '010';
2022 /* Retrieve Invoice Match Option from Vendor site*/
2023 SELECT match_option
2024 INTO x_invoice_match_option
2025 FROM po_vendor_sites_all --<Shared Proc FPJ>
2026 WHERE vendor_site_id = X_vendor_site_id;
2027 END IF;
2028
2029 IF (x_invoice_match_option IS NULL) THEN
2030 /* Retrieve Invoice Match Option from Vendor */
2031 IF (X_vendor_id IS NOT NULL) THEN
2032 l_progress := '020';
2033 SELECT match_option
2034 INTO x_invoice_match_option
2035 FROM po_vendors
2036 WHERE vendor_id = X_vendor_id;
2037 END IF;
2038 END IF;
2039
2040 IF (x_invoice_match_option IS NULL) THEN
2041 l_progress := '030';
2042 -- Get default from ap_product_setup instead of FSP.
2043 SELECT aps.match_option
2044 INTO x_invoice_match_option
2045 FROM ap_product_setup aps;
2046 END IF;
2047
2048 IF g_debug_stmt THEN
2049 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
2050 END IF;
2051
2052 EXCEPTION
2053 WHEN OTHERS THEN
2054 IF g_debug_unexp THEN
2055 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name, p_progress => l_progress);
2056 END IF;
2057
2058 --CLM Phase 2 changes : error handling
2059 PO_AUTOCREATE_PVT.report_error('PO_AUTO_HEADER_INV_MATCH_ERR',x_token1_value => sqlerrm);
2060
2061 PO_AUTOCREATE_PVT.wrapup;
2062 po_message_s.sql_error('GET INVOICE MATCH OPTION',l_progress,SQLCODE);
2063 raise;
2064 END get_invoice_match_option;
2065 END PO_AUTO_HEADER_PROCESS_PVT;