[Home] [Help]
PACKAGE BODY: APPS.PO_AUTOCREATE_POSTPROC_PVT
Source
1 PACKAGE BODY po_autocreate_postproc_pvt AS
2 /* $Header: PO_AUTOCREATE_POSTPROC_PVT.plb 120.33.12020000.13 2013/05/08 08:24:14 akyanama ship $ */
3 g_pkg_name CONSTANT VARCHAR2 (1000) := 'po_autocreate_mainproc_pvt';
4 g_log_head CONSTANT VARCHAR2 (1000)
5 := 'po.plsql.PO_AUTOCREATE_POSTPROC_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
9
10 PROCEDURE set_clm_structure;
11
12 PROCEDURE set_line_num_display;
13
14 PROCEDURE update_uda_template_id;
15
16 PROCEDURE copy_exhibit_from_sol( p_contract_doc_type IN VARCHAR2, p_org_id IN NUMBER);
17
18 /* ----------------------------------------------------
19 ----------------- PUBLIC PROCEDURES -------------------
20 ---------------------------------------------------- */
21
22 /* ============================================================================
23 NAME: process
24 DESC: Main Procedure for the AutoCreate Post Processing logic which includes -
25
26 - Creating Document from the Draft
27 - Copy Attachment
28 - Calculate Tax
29 - Wrapup
30
31 ==============================================================================*/
32 PROCEDURE process
33 IS
34 l_api_name VARCHAR2 (30) := 'process';
35 l_progress VARCHAR2 (3) := '000';
36 x_create_doc_flag VARCHAR2 (1);
37 l_return_status VARCHAR2 (1);
38 l_doc_row doc_row_type;
39 x_document_num po_headers_all.clm_document_number%TYPE; --<Bug 14838385>
40 l_contract_doc_type VARCHAR2 (150);
41 x_bid_number NUMBER;
42 l_msg_data VARCHAR2 (2000);
43 l_msg_count NUMBER;
44 l_contracts_call_exception EXCEPTION;
45
46 -- Document numbering
47 x_doc_number VARCHAR2(4000);
48 x_return_status VARCHAR2(50);
49 x_err_msg VARCHAR2(2000);
50 x_template_id NUMBER;
51 x_dodaac VARCHAR2(150);
52 x_clm_source_document_id PO_HEADERS_DRAFT_ALL.clm_source_document_id%TYPE;
53
54 x_errorcode NUMBER;
55 x_msg_count NUMBER;
56 x_msg_data VARCHAR2(2000);
57
58 l_draft_id NUMBER;
59 l_ame_approval_id po_headers_all.ame_approval_id%TYPE;
60 l_ame_transaction_type po_headers_all.ame_transaction_type%TYPE;
61 l_create_doc_from_draft VARCHAR2(1);
62
63 l_uda_template_exception EXCEPTION;
64 l_default_doc_number_exception EXCEPTION;
65
66 l_is_ord_off_idv VARCHAR2(2);
67 l_print_doc_type VARCHAR2(2000);
68 l_standard_form VARCHAR2(200);
69 l_document_format VARCHAR2(200);
70 l_authorization_status po_headers_all.AUTHORIZATION_STATUS%TYPE; /*bug 12611018*/
71 l_clm_default_dist VARCHAR2(1);
72 l_clm_mipr_type VARCHAR2(15);
73 l_clm_contract_fin_code VARCHAR2(30);
74 l_umbrella_program_id NUMBER ;
75
76 BEGIN
77 IF g_debug_stmt
78 THEN
79 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
80 END IF;
81
82 l_progress := '001';
83
84 /*
85 * Set the CLM structure and UDA tempalte id only when the target docuemnt is CLM enabled.
86 */
87 IF po_autocreate_params.g_is_clm_po = 'Y'
88 THEN
89 l_progress := '002';
90 set_clm_structure;
91 l_progress := '003';
92 update_uda_template_id;
93 END IF;
94
95 l_progress := '004';
96 IF create_doc_from_draft_check (po_autocreate_params.g_po_header_id) = 'Y'
97 THEN
98 l_draft_id := -1;
99 ELSE
100 l_draft_id := po_autocreate_params.g_draft_id;
101 END IF;
102
103 l_progress := '005';
104
105 --<Bug 14554825> : The uda data needs to be copied into the ext tables
106 --for all flows except in case of ADD FUNDS TO MOD
107 IF Nvl(po_autocreate_params.g_process_code,'*') <> 'ADD_FUNDS'
108 THEN
109 PO_UDA_AUTOCREATE_PKG.autocreate_uda_data(
110 p_source_document => NULL , -- PR,SOL
111 p_target_document => NULL, -- SOL,PO,IDV
112 p_src_pk1_value => NULL,
113 p_src_pk2_value => NULL,
114 p_tar_pk1_value => po_autocreate_params.g_po_header_id, -- PO_HEADER_ID
115 p_tar_pk2_value => l_draft_id, -- DRAFT_ID
116 x_return_status => x_return_status,
117 x_errorcode => x_errorcode,
118 x_msg_count => x_msg_count,
119 x_msg_data => x_msg_data);
120 END IF;
121
122 /*Bug 13547051 starts*/
123 IF po_autocreate_params.g_mode = 'NEW'
124 --<Bug 15928376> : c_ext_attr40 value to be updated for all NEW flows.
125 --Removing SOURCING condition
126 THEN
127
128 /*
129 While autocreating a new award the c_ext_attr40 column has null value or
130 SOL_UDA_ADDRESS_TYPES when called from sourcing.The value for this column
131 should be PO_UDA_ADDRESS_TYPES.This is done because on multi-mod page
132 address type is defaulted to PO_MOD_UDA_ADDRESS_TYPES but while copying on actual modification it
133 should be PO_UDA_ADDRESS_TYPES. When modification is created from BWC, award related address fields
134 have value PO_UDA_ADDRESS_TYPES.
135 */
136
137 UPDATE po_headers_all_ext_b
138 SET c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
139 WHERE po_header_id = po_autocreate_params.g_po_header_id
140 AND draft_id = -1
141 AND c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')
142 AND attr_group_id = (SELECT attr_group_id
143 FROM ego_attr_groups_v
144 WHERE ATTR_GROUP_NAME = 'addresses'
145 AND ATTR_GROUP_TYPE = 'PO_HEADER_EXT_ATTRS' );
146
147 END IF;
148 /*Bug 13547051 ends*/
149
150 OPEN c_doc;
151
152 LOOP
153 FETCH c_doc
154 INTO l_doc_row;
155
156 EXIT WHEN c_doc%NOTFOUND;
157 l_progress := '006';
158
159
160 BEGIN
161
162 SELECT PEI_INFORMATION2
163 INTO x_dodaac
164 FROM per_people_extra_info pei, fnd_user fu
165 WHERE pei.information_type = 'CLM_CONTACT_TITLE'
166 AND pei.person_id= fu.employee_id
167 AND fu.user_id = fnd_global.user_id;
168 EXCEPTION
169 WHEN No_Data_Found THEN
170 x_dodaac := NULL;
171 WHEN OTHERS THEN
172 x_dodaac := NULL;
173
174 END;
175
176 IF po_autocreate_params.g_mode = 'NEW' AND po_autocreate_params.g_clm_source_document_id IS NOT NULL
177 THEN
178
179 SELECT umbrella_program_id,
180 clm_contract_finance_code
181 INTO l_umbrella_program_id,
182 l_clm_contract_fin_code
183 FROM po_headers_all
184 WHERE po_header_id =po_autocreate_params.g_clm_source_document_id;
185
186 -- Bug 10057141: Removed the additional WHERE condition
187 UPDATE po_headers_draft_all
188 SET clm_source_document_id = po_autocreate_params.g_clm_source_document_id,
189 /*umbrella program changes*/
190 umbrella_program_id = nvl(umbrella_program_id,l_umbrella_program_id),
191 /*umbrella program changes*/
192 --Bug 16399373 - contract financing value to be carried forwarded from idv to the award
193 clm_contract_finance_code = l_clm_contract_fin_code
194 WHERE po_header_id = l_doc_row.po_header_id;
195
196 END IF;
197
198 -- Getting the CLM Source Document Number for Order off IDV
199 -- Bug 10057141: Added check for CLM Award Type
200 BEGIN
201
202 SELECT phda.clm_source_document_id
203 INTO x_clm_source_document_id
204 FROM po_headers_draft_all phda,
205 po_headers_all pha
206 WHERE phda.po_header_id = l_doc_row.po_header_id
207 AND pha.po_header_id = phda.clm_source_document_id
208 AND pha.clm_award_type IS NOT NULL
209 AND PO_AUTOCREATE_PARAMS.g_document_type <> 'PA'; --Bug 13787095
210 -- While creating CLM IDV with lines from sourcing
211 -- if reference IDV is provided ignore it
212
213 EXCEPTION
214 WHEN No_Data_Found THEN
215 x_clm_source_document_id := NULL;
216 WHEN OTHERS THEN
217 x_clm_source_document_id := NULL;
218 END;
219
220
221 -- Get new document number
222 IF po_autocreate_params.g_mode = 'NEW'
223 THEN
224 IF po_autocreate_params.g_sys.user_defined_po_num_code =
225 'AUTOMATIC'
226 AND ( po_autocreate_params.g_document_subtype = 'BLANKET'
227 OR po_autocreate_params.g_document_subtype = 'STANDARD'
228 )
229 AND
230 /* If emergency po number is mentioned then the interface document number
231 will have that value and it can be alphanumeric though the po num code
232 is automatic.
233 In case of automatic, we populate a dummy value in segment1(in po_headers)
234 which is negative of po_header_id.
235 We should be populating the segment1 from uniqus
236 table only if the segment1 is negative of po_header id and
237 hence the following logic.
238 (interface.document_num = to_char((-1* x_document_id))) THEN
239 */
240 (l_doc_row.document_num =
241 TO_CHAR ((-1 * l_doc_row.po_header_id))
242 )
243 THEN
244
245 l_progress := '003';
246
247 -- Call Document Numbering API to get the order number
248 x_template_id := PO_UDA_DATA_UTIL.GET_TEMPLATE_ID(p_functional_area => 'PURCHASING',
249 p_document_type => PO_AUTOCREATE_PARAMS.g_document_subtype,
250 p_document_style_id => l_doc_row.po_style_id,
251 p_document_level => 'HEADER',
252 p_input_date => sysdate,
253 x_return_status => x_return_status,
254 x_err_msg => x_err_msg);
255
256 IF g_debug_stmt THEN
257 po_debug.debug_stmt
258 (p_log_head => g_log_head || l_api_name,
259 p_token => l_progress,
260 p_message => 'After getting the UDA template id: ' ||x_template_id
261 );
262 END IF;
263
264
265 IF x_template_id IS NOT NULL THEN
266
267 l_progress := '008';
268 PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA(p_doc_header_id => l_doc_row.po_header_id,
269 p_draft_id => -1,
270 p_template_id => x_template_id,
271 p_context_usage => 'Base Document',
272 p_source_org_owned => PO_DOC_NUMBERING_PKG.get_owned_by_issuing_org(x_clm_source_document_id),
273 p_caller => 'PO',
274 x_doc_number => x_doc_number,
275 x_return_status => x_return_status);
276
277 --bug11843262 : Handle error when returning from DEFAULT_DOC_NUMBER_UDA
278 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
279 RAISE l_default_doc_number_exception;
280 END IF;
281
282 IF g_debug_stmt THEN
283 po_debug.debug_stmt
284 (p_log_head => g_log_head || l_api_name,
285 p_token => l_progress,
286 p_message => 'After getting the Document Number: ' ||x_doc_number
287 );
288 END IF;
289
290 ELSE
291 RAISE l_uda_template_exception;
292 END IF;
293
294 IF x_doc_number IS NOT NULL THEN
295
296 x_document_num := REPLACE(x_doc_number,'-');
297
298 IF Length(x_document_num) > 20 THEN
299
300 x_document_num := po_core_sv1.default_po_unique_identifier
301 (p_table_name => 'PO_HEADERS',
302 p_org_id => po_autocreate_params.g_purchasing_ou_id
303 );
304
305 END IF;
306 ELSE
307
308 x_document_num := po_core_sv1.default_po_unique_identifier
309 (p_table_name => 'PO_HEADERS',
310 p_org_id => po_autocreate_params.g_purchasing_ou_id
311 );
312 END IF;
313
314 UPDATE po_headers_draft_all
315 SET segment1 = x_document_num,
316 clm_document_number = x_doc_number
317 WHERE po_header_id = l_doc_row.po_header_id;
318
319 END IF; -- po_autocreate_params.g_sys.user_defined_po_num_code =
320
321 ELSIF po_autocreate_params.g_mode = 'ADD' THEN
322 /*Bug 12611018 : The Document number UDA attributes should not be defaulted
323 when the award that we are adding to is an Incomplete Award.
324 Hence skip the defaulting when award is Incomplete.*/
325 BEGIN
326
327 select authorization_status
328 INTO l_authorization_status
329 FROM po_headers_all
330 WHERE po_header_id = PO_AUTOCREATE_PARAMS.g_po_header_id;
331
332 EXCEPTION
333 WHEN No_Data_Found THEN
334 l_authorization_status:='';
335 END;
336 /*Bug 12611018 ends */
337
338 IF PO_AUTOCREATE_PARAMS.g_is_mod_exists = FALSE -- New Mod
339 AND Nvl(l_authorization_status,'INCOMPLETE') = 'APPROVED' THEN -- Bug 12611018
340
341 -- Call Document Numbering API to get the order number
342 x_template_id := PO_UDA_DATA_UTIL.GET_TEMPLATE_ID(p_functional_area => 'PURCHASING',
343 p_document_type => PO_AUTOCREATE_PARAMS.g_document_subtype,
344 p_document_style_id => l_doc_row.po_style_id,
345 p_document_level => 'HEADER',
346 p_input_date => sysdate,
347 x_return_status => x_return_status,
348 x_err_msg => x_err_msg);
349
350 IF g_debug_stmt THEN
351 po_debug.debug_stmt
352 (p_log_head => g_log_head || l_api_name,
353 p_token => l_progress,
354 p_message => 'After getting the UDA template id: ' ||x_template_id
355 );
356 END IF;
357
358
359 IF x_template_id IS NOT NULL THEN
360
361 l_progress := '010';
362 PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA(p_doc_header_id => l_doc_row.po_header_id,
363 p_draft_id => l_doc_row.draft_id,
364 p_template_id => x_template_id,
365 p_context_usage => 'Modification',
366 p_source_org_owned => PO_DOC_NUMBERING_PKG.get_owned_by_issuing_org(x_clm_source_document_id),
367 p_caller => 'PO_MOD',
368 x_doc_number => x_doc_number,
369 x_return_status => x_return_status);
370
371 --bug11843262 : Handle error when returning from DEFAULT_DOC_NUMBER_UDA
372 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
373 RAISE l_default_doc_number_exception;
374 END IF;
375 ELSE
376 RAISE l_uda_template_exception;
377 END IF;
378 END IF;
379 END IF;
380
381 l_progress := '020';
382
383 l_create_doc_from_draft := create_doc_from_draft_check(l_doc_row.po_header_id);
384
385
386 -- Getting CLM Standard Form and CLM Document Format
387 IF po_autocreate_params.g_mode = 'ADD' AND
388 l_create_doc_from_draft = 'N' AND
389 NOT PO_AUTOCREATE_PARAMS.g_is_mod_exists -- New Mod
390 THEN
391
392 l_print_doc_type := 'PO_MOD_STD_FORM';
393
394 ELSIF po_autocreate_params.g_mode = 'NEW' OR
395 l_create_doc_from_draft = 'Y'
396 THEN
397
398 BEGIN
399
400 --<Bug 14335533 Starts>
401 --Commented out the existing logic to derive whether the document is Ord Off IDV
402 --Document is an Order Off IDV if the award type is either BPA Call or Delivery Order
403
404 --SELECT nvl2(clm_source_document_id, 'Y', nvl2(clm_external_idv, 'Y', 'N'))
405 --INTO l_is_ord_off_idv
406 --FROM po_headers_draft_all
407 --WHERE po_header_id = l_doc_row.po_header_id
408 --AND draft_id = l_doc_row.draft_id;
409
410 SELECT Decode(Nvl(clm_award_type,'*'),
411 'DELIVERY_ORD', 'Y',
412 'BPA_CALL', 'Y',
413 'N')
414 INTO l_is_ord_off_idv
415 FROM po_headers_draft_all
416 WHERE po_header_id = l_doc_row.po_header_id
417 AND draft_id = l_doc_row.draft_id;
418 --<Bug 14335533 Ends>
419
420 EXCEPTION
421 WHEN No_Data_Found THEN
422 l_is_ord_off_idv := 'N';
423 WHEN OTHERS THEN
424 l_is_ord_off_idv := 'N';
425 END;
426
427
428 IF l_is_ord_off_idv = 'Y' THEN
429 l_print_doc_type := 'PO_DEL_ORD_STD_FORM';
430 ELSE
431 IF PO_AUTOCREATE_PARAMS.g_document_subtype = 'STANDARD' THEN
432 l_print_doc_type := 'PO_AWARD_STD_FORM';
433 ELSIF PO_AUTOCREATE_PARAMS.g_document_subtype = 'BLANKET' THEN
434 l_print_doc_type := 'PO_IDV_STD_FORM';
435 ELSIF PO_AUTOCREATE_PARAMS.g_document_subtype = 'CONTRACT' THEN
436 l_print_doc_type := 'PO_IDV_STD_FORM';
437 END IF;
438 END IF; -- IF l_is_ord_off_idv = 'Y'
439
440 END IF; -- IF po_autocreate_params.g_mode = 'NEW' OR l_create_doc_from_draft = 'Y'
441
442 -- Getting Standard Form and Document Format of the output document
443 BEGIN
444 --Validating the standard_form and document_format in interface
445 SELECT standard_form,
446 document_format
447 INTO l_standard_form,
448 l_document_format
449 FROM po_print_form_formats
450 WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
451 AND standard_form = x_headers.clm_standard_form
452 AND document_format = x_headers.clm_document_format;
453
454 EXCEPTION
455 WHEN No_Data_Found THEN
456 BEGIN
457 --Defaulting standard_form and document_format
458 SELECT standard_form,
459 document_format
460 INTO l_standard_form,
461 l_document_format
462 FROM po_print_form_formats
463 WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
464 AND default_flag = 'Y'
465 AND style_id = l_doc_row.po_style_id
466 AND document_type = l_print_doc_type;
467
468 EXCEPTION
469 WHEN No_Data_Found THEN
470 l_standard_form := NULL;
471 l_document_format := NULL;
472 WHEN OTHERS THEN
473 l_standard_form := NULL;
474 l_document_format := NULL;
475 END;
476 WHEN OTHERS THEN
477 l_standard_form := NULL;
478 l_document_format := NULL;
479 END;
480
481
482 -- New PO case
483 -- Default the value of clm_standard_form and clm_standard_form in Award
484 -- CLM-LnSc: Default clm_default_dist_flag for New PO
485 IF po_autocreate_params.g_mode = 'NEW' THEN
486
487 -- <CLM-LnSc-START>
488 -- Fetch the value for CLM_DEFAULT_DIST_FLAG
489 l_clm_default_dist := PO_CORE_S.get_create_default_dist(
490 p_org_id => PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
491 p_style_id => l_doc_row.po_style_id,
492 p_doc_sub_type => PO_AUTOCREATE_PARAMS.g_document_subtype);
493 -- <CLM-LnSc-END>
494
495 UPDATE po_headers_draft_all
496 SET clm_standard_form = l_standard_form,
497 clm_document_format = l_document_format,
498 clm_default_dist_flag = l_clm_default_dist
499 WHERE po_header_id = l_doc_row.po_header_id;
500
501 END IF;
502
503
504 -- Add to PO case
505 -- Default the value of clm_standard_form and clm_standard_form in Modification
506 IF po_autocreate_params.g_mode = 'ADD'
507 -- AND create_doc_from_draft_check (l_doc_row.po_header_id) = 'N'
508 AND l_create_doc_from_draft = 'N'
509 AND NOT PO_AUTOCREATE_PARAMS.g_is_mod_exists -- New Mod
510 THEN
511 UPDATE po_drafts
512 SET draft_type = 'MOD'
513 ,modification_number = x_doc_number
514 ,mod_effective_date = sysdate
515 ,clm_standard_form = l_standard_form
516 ,clm_document_format = l_document_format
517 WHERE draft_id = po_autocreate_params.g_draft_id;
518
519 SELECT po_ame_approvals_s.nextval
520 INTO l_ame_approval_id
521 FROM dual;
522
523 IF g_debug_stmt
524 THEN
525 po_debug.debug_stmt
526 (p_log_head => g_log_head || l_api_name,
527 p_token => l_progress,
528 p_message => ' ame_approval_id: '||l_ame_approval_id
529 );
530 END IF;
531
532 SELECT mod_ame_transaction_type
533 INTO l_ame_transaction_type
534 FROM po_doc_style_headers pdsh, po_headers_all pha
535 WHERE pha.po_header_id = l_doc_row.po_header_id
536 AND pha.style_id = pdsh.style_id;
537
538 -- <CLM-LnSc-START>
539 -- Fetch the value for CLM_DEFAULT_DIST_FLAG for New Mod
540 l_clm_default_dist := PO_CORE_S.get_create_default_dist(
541 p_org_id => PO_AUTOCREATE_PARAMS.g_purchasing_ou_id,
542 p_style_id => l_doc_row.po_style_id,
543 p_doc_sub_type => PO_AUTOCREATE_PARAMS.g_document_subtype);
544 -- <CLM-LnSc-END>
545
546 UPDATE po_headers_draft_all
547 SET ame_approval_id = l_ame_approval_id,
548 ame_transaction_type = l_ame_transaction_type,
549 clm_default_dist_flag = l_clm_default_dist
550 WHERE po_header_id = l_doc_row.po_header_id
551 AND draft_id = l_doc_row.draft_id;
552
553 -- Copy Attachments
554 copy_attachment (p_doc_rec => l_doc_row, p_is_mod => 'Y');
555 l_progress := '30';
556
557 -- Contract terms
558 IF ( (po_autocreate_params.g_interface_source_code = 'SOURCING'
559 )
560 AND (po_autocreate_params.x_conterms_exist_flag = 'Y')
561 )
562 THEN
563 l_progress := '40';
564 l_contract_doc_type :=
565 po_conterms_utl_grp.get_po_contract_doctype_mod
566 (p_sub_doc_type => l_doc_row.document_subtype);
567
568 SELECT MAX (pli.bid_number)
569 INTO x_bid_number
570 FROM po_lines_interface pli
571 WHERE pli.interface_header_id =
572 po_autocreate_params.x_interface_header_id;
573
574 IF g_debug_stmt
575 THEN --< Bug 3210331: use proper debugging >
576 po_debug.debug_stmt
577 (p_log_head => g_log_head || l_api_name,
578 p_token => l_progress,
579 p_message => 'Contracts template attached'
580 );
581 po_debug.debug_var (p_log_head => g_log_head
582 || l_api_name,
583 p_progress => l_progress,
584 p_name => 'x_document_id',
585 p_value => l_doc_row.po_header_id
586 );
587 po_debug.debug_var (p_log_head => g_log_head
588 || l_api_name,
589 p_progress => l_progress,
590 p_name => 'x_draft_id',
591 p_value => l_doc_row.draft_id
592 );
593 po_debug.debug_var (p_log_head => g_log_head
594 || l_api_name,
595 p_progress => l_progress,
596 p_name => 'x_document_num',
597 p_value => x_document_num
598 );
599 po_debug.debug_var (p_log_head => g_log_head
600 || l_api_name,
601 p_progress => l_progress,
602 p_name => 'l_contract_doc_type',
603 p_value => l_contract_doc_type
604 );
605 po_debug.debug_var (p_log_head => g_log_head
606 || l_api_name,
607 p_progress => l_progress,
608 p_name => 'x_bid_number',
609 p_value => x_bid_number
610 );
611 po_debug.debug_var
612 (p_log_head => g_log_head || l_api_name,
613 p_progress => l_progress,
614 p_name => 'p_sourcing_k_doc_type',
615 p_value => po_autocreate_params.x_sourcing_k_doc_type
616 );
617 po_debug.debug_stmt
618 (p_log_head => g_log_head || l_api_name,
619 p_token => l_progress,
620 p_message => 'before call okc_terms_copy_grp.copy_doc'
621 );
622 END IF;
623
624 l_progress := '050';
625 okc_terms_copy_grp.copy_doc
626 (p_api_version => 1.0,
627 p_source_doc_type => po_autocreate_params.x_sourcing_k_doc_type,
628 p_source_doc_id => x_bid_number,
629 p_target_doc_type => l_contract_doc_type,
630 p_target_doc_id => l_doc_row.draft_id,
631 p_keep_version => 'Y',
632 p_article_effective_date => SYSDATE,
633 p_initialize_status_yn => 'N',
634 p_reset_fixed_date_yn => 'N',
635 p_copy_del_attachments_yn => 'Y',
636 p_copy_deliverables => 'Y',
637 p_document_number => x_document_num,
638 p_copy_abstract_yn => 'Y',
639 x_return_status => l_return_status,
640 x_msg_data => l_msg_data,
641 x_msg_count => l_msg_count
642 );
643 l_progress := '060';
644
645 IF g_debug_stmt
646 THEN
647 po_debug.debug_stmt
648 (p_log_head => g_log_head || l_api_name,
649 p_token => l_progress,
650 p_message => 'after call okc_terms_copy_grp.copy_doc.Return status:'
651 || l_return_status
652 );
653 END IF;
654
655 IF l_return_status <> fnd_api.g_ret_sts_success
656 THEN
657 RAISE l_contracts_call_exception;
658 END IF; -- Return status from contracts
659 END IF; -- If PO_AUTOCREATE_PARAMS.x_conterms_exist_flag= 'Y'
660
661 END IF; --IF ... AND l_create_doc_from_draft = 'N'
662
663 l_progress := '070';
664 -- Transfer draft records to transaction
665 IF po_autocreate_params.g_mode = 'NEW'
666 -- OR create_doc_from_draft_check (l_doc_row.po_header_id) = 'Y'
667 OR l_create_doc_from_draft = 'Y'
668 THEN
669 -- The records are insterted in the draft tables
670 -- Now these draft records should be moved to the main tables if
671 -- mode is NEW or
672 -- mode is ADD and the added document is not yet Approved or
673 -- mode is ADD and the added document doesnot follow
674 -- Modification change process
675
676 po_drafts_pvt.transfer_draft_to_txn
677 (p_api_version => 1.0,
678 p_init_msg_list => fnd_api.g_false,
679 p_draft_id => l_doc_row.draft_id,
680 p_po_header_id => l_doc_row.po_header_id,
681 p_delete_processed_draft => fnd_api.g_true,
682 p_acceptance_action => NULL,
683 x_return_status => l_return_status
684 );
685
686
687 --<<MIPR Project - Start>>
688 --call the PO_CLM_CAR_UTIL.car_for_control_type to set the CAR for only MIPR Own type
689 --Update the obligation type only if the action is 'New Award'
690 l_clm_mipr_type := POR_UTIL_PKG.G_REQ_TYPE;
691 IF Nvl(l_clm_mipr_type,'PR') = 'MIPR' AND l_draft_id = -1
692 THEN
693
694 UPDATE po_headers_all
695 SET CLM_SPECIAL_CONTRACT_TYPE = 'OBLIGATION_DOC'
696 WHERE po_header_id = po_autocreate_params.g_po_header_id;
697
698 PO_CLM_CAR_UTIL.car_for_control_type
699 ( p_po_header_id => po_autocreate_params.g_po_header_id,
700 p_draft_id => l_draft_id,
701 p_special_contract_type => 'OBLIGATION_DOC'
702 );
703
704 END IF ;
705 --<<MIPR Project - End>>
706
707 po_autocreate_params.g_draft_id := NULL;
708
709 IF (l_return_status <> fnd_api.g_ret_sts_success)
710 THEN
711 RAISE fnd_api.g_exc_unexpected_error;
712 END IF;
713
714 l_progress := '080';
715
716 -- Document specific actions
717 IF po_autocreate_params.g_document_subtype = 'BLANKET'
718 THEN
719 create_blanket (l_doc_row);
720 ELSIF po_autocreate_params.g_document_subtype = 'STANDARD'
721 THEN
722 create_standard_po (l_doc_row);
723 END IF;
724
725 l_progress := '090';
726 -- Copy Attachments
727 copy_attachment (p_doc_rec => l_doc_row);
728 l_progress := '100';
729
730 -- Contract terms
731 IF ( (po_autocreate_params.g_interface_source_code = 'SOURCING'
732 )
733 AND (po_autocreate_params.x_conterms_exist_flag = 'Y')
734 )
735 THEN
736 l_progress := '110';
737 l_contract_doc_type :=
738 po_conterms_utl_grp.get_po_contract_doctype
739 (p_sub_doc_type => l_doc_row.document_subtype);
740
741 SELECT MAX (pli.bid_number)
742 INTO x_bid_number
743 FROM po_lines_interface pli
744 WHERE pli.interface_header_id =
745 po_autocreate_params.x_interface_header_id;
746
747 IF g_debug_stmt
748 THEN --< Bug 3210331: use proper debugging >
749 po_debug.debug_stmt
750 (p_log_head => g_log_head || l_api_name,
751 p_token => l_progress,
752 p_message => 'Contracts template attached'
753 );
754 po_debug.debug_var (p_log_head => g_log_head
755 || l_api_name,
756 p_progress => l_progress,
757 p_name => 'x_document_id',
758 p_value => l_doc_row.po_header_id
759 );
760 po_debug.debug_var (p_log_head => g_log_head
761 || l_api_name,
762 p_progress => l_progress,
763 p_name => 'x_document_num',
764 p_value => x_document_num
765 );
766 po_debug.debug_var (p_log_head => g_log_head
767 || l_api_name,
768 p_progress => l_progress,
769 p_name => 'l_contract_doc_type',
770 p_value => l_contract_doc_type
771 );
772 po_debug.debug_var (p_log_head => g_log_head
773 || l_api_name,
774 p_progress => l_progress,
775 p_name => 'x_bid_number',
776 p_value => x_bid_number
777 );
778 po_debug.debug_var
779 (p_log_head => g_log_head || l_api_name,
780 p_progress => l_progress,
781 p_name => 'p_sourcing_k_doc_type',
782 p_value => po_autocreate_params.x_sourcing_k_doc_type
783 );
784 po_debug.debug_stmt
785 (p_log_head => g_log_head || l_api_name,
786 p_token => l_progress,
787 p_message => 'before call okc_terms_copy_grp.copy_doc'
788 );
789 END IF;
790
791 l_progress := '120';
792 okc_terms_copy_grp.copy_doc
793 (p_api_version => 1.0,
794 p_source_doc_type => po_autocreate_params.x_sourcing_k_doc_type,
795 p_source_doc_id => x_bid_number,
796 p_target_doc_type => l_contract_doc_type,
797 p_target_doc_id => l_doc_row.po_header_id,
798 p_keep_version => 'Y',
799 p_article_effective_date => SYSDATE,
800 p_initialize_status_yn => 'N',
801 p_reset_fixed_date_yn => 'N',
802 p_copy_del_attachments_yn => 'Y',
803 p_copy_deliverables => 'Y',
804 p_document_number => x_document_num,
805 p_copy_abstract_yn => 'Y', -- Bug 4051316
806 x_return_status => l_return_status,
807 x_msg_data => l_msg_data,
808 x_msg_count => l_msg_count
809 );
810 l_progress := '130';
811
812 IF g_debug_stmt
813 THEN --< Bug 3210331: use proper debugging >
814 po_debug.debug_stmt
815 (p_log_head => g_log_head || l_api_name,
816 p_token => l_progress,
817 p_message => 'after call okc_terms_copy_grp.copy_doc.Return status:'
818 || l_return_status
819 );
820 END IF;
821
822 IF l_return_status <> fnd_api.g_ret_sts_success
823 THEN
824 RAISE l_contracts_call_exception;
825 END IF; -- Return status from contracts
826 END IF; -- If PO_AUTOCREATE_PARAMS.x_conterms_exist_flag= 'Y'
827
828 /* Update the terms after the successful completion of PO */
829 l_progress := '140';
830
831 IF (po_autocreate_params.g_document_subtype = 'STANDARD')
832 AND (po_autocreate_params.g_interface_source_code <>
833 'CONSUMPTION_ADVICE'
834 )
835 THEN -- CONSIGNED FPI
836 po_interface_s2.update_terms (l_doc_row.po_header_id);
837 END IF;
838
839 l_progress := '150';
840 SELECT po_ame_approvals_s.nextval
841 INTO l_ame_approval_id
842 FROM dual;
843
844
845 SELECT pdsh.ame_transaction_type
846 INTO l_ame_transaction_type
847 FROM po_doc_style_headers pdsh, po_headers_all pha
848 WHERE pha.po_header_id = l_doc_row.po_header_id
849 AND pha.style_id = pdsh.style_id;
850
851 UPDATE po_headers_draft_all
852 SET ame_approval_id = l_ame_approval_id,
853 ame_transaction_type = l_ame_transaction_type
854 WHERE po_header_id = l_doc_row.po_header_id
855 AND draft_id = l_doc_row.draft_id;
856
857 END IF; --PO_AUTOCREATE_PARAMS.g_mode = 'NEW'
858
859 END LOOP;
860
861 CLOSE c_doc;
862
863 l_progress := '160';
864
865
866 /*Autocreate grouping phase2. Calling the set_line_num_display when the g_interface_source_code is 'SOURCING' too. Earlier in case of 'SOURCING' we were not making this call.*/
867 --IF (po_autocreate_params.g_interface_source_code <> 'SOURCING')
868 --THEN
869 set_line_num_display;
870 -- END IF;
871
872 --CLM Phase4 Changes
873 l_progress := '165';
874 --copy data from PON_AUCTION_EXHIBIT_DETAILS to PO_EXHIBIT_DETAILS when coming from Sourcing
875 IF (po_autocreate_params.g_interface_source_code = 'SOURCING')
876 THEN
877 copy_exhibit_from_sol(l_contract_doc_type, po_autocreate_params.g_purchasing_ou_id);
878 END IF;
879
880
881 l_progress := '170';
882 IF (po_autocreate_params.g_org_context_changed = 'Y') THEN
883 PO_MOAC_UTILS_PVT.set_org_context(po_autocreate_params.g_original_operating_unit_id) ;
884 END IF;
885
886
887
888 IF g_debug_stmt
889 THEN
890 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
891 END IF;
892 EXCEPTION
893 WHEN l_uda_template_exception THEN
894
895 IF g_debug_stmt THEN
896 po_debug.debug_stmt
897 (p_log_head => g_log_head || l_api_name,
898 p_token => l_progress,
899 p_message => 'PO_AUTOCREATE_POSTPROC_PVT.process: Inside l_uda_template_exception'
900 );
901 END IF;
902
903 fnd_message.set_name ('PO', 'PO_UDA_TEMPLATE_ID_NULL');
904 fnd_msg_pub.ADD;
905
906 PO_AUTOCREATE_PARAMS.g_number_records_processed := 0;
907 CLOSE c_doc;
908
909 --CLM Phase 2 changes : error handling
910 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_UDA_TEMP_ERR',x_token1_value => sqlerrm);
911
912 po_autocreate_pvt.wrapup ();
913 RAISE;
914
915 WHEN l_contracts_call_exception
916 THEN
917 IF g_debug_stmt
918 THEN
919 po_debug.debug_stmt
920 (p_log_head => g_log_head || l_api_name,
921 p_token => l_progress,
922 p_message => 'PO_AUTOCREATE_POSTPROC_PVT.process: Inside l_contracts_call_exception'
923 );
924 END IF;
925
926 fnd_message.set_name ('PO', 'PO_API_ERROR');
927 fnd_message.set_token (token => 'PROC_CALLER',
928 VALUE => 'PO_INTERFACE_S.CREATE_PO'
929 );
930 fnd_message.set_token (token => 'PROC_CALLED',
931 VALUE => 'OKC_TERMS_CPOY_GRP.COPY_DOC'
932 );
933 fnd_msg_pub.ADD;
934
935 IF g_debug_stmt
936 THEN
937 l_msg_count := fnd_msg_pub.count_msg;
938
939 FOR i IN 1 .. l_msg_count
940 LOOP
941 po_debug.debug_stmt
942 (p_log_head => g_log_head || l_api_name,
943 p_token => l_progress
944 || '_EXCEPTION_'
945 || i,
946 p_message => fnd_msg_pub.get
947 (p_msg_index => i,
948 p_encoded => 'F'
949 )
950 );
951 END LOOP;
952 END IF;
953
954 CLOSE c_doc;
955
956 --CLM Phase 2 changes : error handling
957 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_CONT_ERR',x_token1_value => sqlerrm);
958
959 po_autocreate_pvt.wrapup ();
960 RAISE;
961 WHEN l_default_doc_number_exception THEN
962
963 IF g_debug_stmt THEN
964 po_debug.debug_stmt
965 (p_log_head => g_log_head || l_api_name,
966 p_token => l_progress,
967 p_message => 'PO_AUTOCREATE_POSTPROC_PVT.process: Inside l_default_doc_number_exception'
968 );
969 END IF;
970
971 fnd_message.set_name ('PO', 'PO_API_ERROR');
972 fnd_message.set_token (token => 'PROC_CALLER',
973 VALUE => 'PO_INTERFACE_S.CREATE_PO'
974 );
975 fnd_message.set_token (token => 'PROC_CALLED',
976 VALUE => 'PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA'
977 );
978 fnd_msg_pub.ADD;
979
980 IF g_debug_stmt
981 THEN
982 l_msg_count := fnd_msg_pub.count_msg;
983
984 FOR i IN 1 .. l_msg_count
985 LOOP
986 po_debug.debug_stmt
987 (p_log_head => g_log_head || l_api_name,
988 p_token => l_progress
989 || '_EXCEPTION_'
990 || i,
991 p_message => fnd_msg_pub.get
992 (p_msg_index => i,
993 p_encoded => 'F'
994 )
995 );
996 END LOOP;
997 END IF;
998
999 PO_AUTOCREATE_PARAMS.g_number_records_processed := 0;
1000 IF (c_doc%ISOPEN) THEN
1001 CLOSE c_doc;
1002 END IF;
1003
1004 --CLM Phase 2 changes : error handling
1005 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_DOC_NUM_ERR',x_token1_value => sqlerrm);
1006
1007 po_autocreate_pvt.wrapup ();
1008 RAISE;
1009 WHEN OTHERS
1010 THEN
1011 IF g_debug_unexp
1012 THEN
1013 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
1014 p_progress => l_progress
1015 );
1016 END IF;
1017
1018 CLOSE c_doc;
1019
1020 po_message_s.sql_error ('PROCESS', l_progress, SQLCODE);
1021 po_autocreate_pvt.wrapup ();
1022 RAISE;
1023 END process;
1024
1025 /*
1026 -------------------------------------------------------
1027 ---------------- PRIVATE PROCEDURES -------------------
1028 -------------------------------------------------------
1029
1030
1031
1032 /* ============================================================================
1033 NAME: create_standard_po
1034 DESC: Performs necessary post processing action to create a standard po.
1035
1036 - Get the document num
1037 - Transfer data from draft to base
1038 - Calculate Tax
1039
1040 ARGS: IN : p_doc_rec doc_row_type -Some attribute values of the document
1041
1042 NOTE: This procedure is called from process()
1043
1044 ==============================================================================*/
1045 PROCEDURE create_standard_po (p_doc_rec IN doc_row_type)
1046 IS
1047 l_api_name VARCHAR2 (30) := 'create_standard_po';
1048 l_progress VARCHAR2 (3) := '000';
1049 BEGIN
1050 IF g_debug_stmt
1051 THEN
1052 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1053 END IF;
1054
1055 -- calculate Tax
1056 calculate_tax (p_doc_rec);
1057 l_progress := '010';
1058
1059 -- Bug#9898165: CLM Partial Funding Changes
1060 PO_PARTIAL_FUNDING_PKG.recalculate_pf_attributes(p_doc_header_id => p_doc_rec.po_header_id);
1061 l_progress := '020';
1062
1063 IF g_debug_stmt
1064 THEN
1065 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
1066 END IF;
1067 EXCEPTION
1068 WHEN OTHERS
1069 THEN
1070 IF g_debug_unexp
1071 THEN
1072 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
1073 p_progress => l_progress
1074 );
1075 END IF;
1076
1077 --CLM Phase 2 changes : error handling
1078 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_CREATEPO_ERR',x_token1_value => sqlerrm);
1079
1080 po_message_s.sql_error ('CREATE_STANDARD_PO', l_progress, SQLCODE);
1081 po_autocreate_pvt.wrapup ();
1082 RAISE;
1083 END;
1084
1085 /* ============================================================================
1086 NAME: create_blanket
1087 DESC: Performs necessary post processing action to create a blanket.
1088
1089 - Get the document num
1090 - Transfer data from draft to base
1091 ARGS: IN : p_doc_rec doc_row_type -Some attribute values of the document
1092
1093 NOTE: This procedure is called from process()
1094
1095 ==============================================================================*/
1096 PROCEDURE create_blanket (p_doc_rec IN doc_row_type)
1097 IS
1098 l_api_name VARCHAR2 (30) := 'create_blanket';
1099 l_progress VARCHAR2 (3) := '000';
1100 BEGIN
1101 IF g_debug_stmt
1102 THEN
1103 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1104 END IF;
1105
1106 NULL;
1107
1108 IF po_autocreate_params.g_interface_source_code = 'SOURCING'
1109 THEN
1110 -- PO_ATTRIBUTE_VALUES_PVT.transfer_intf_item_attribs(p_doc_rec.interface_header_id);
1111 NULL;
1112 END IF;
1113
1114 l_progress := '010';
1115
1116 IF g_debug_stmt
1117 THEN
1118 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
1119 END IF;
1120 EXCEPTION
1121 WHEN OTHERS
1122 THEN
1123 IF g_debug_unexp
1124 THEN
1125 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
1126 p_progress => l_progress
1127 );
1128 END IF;
1129
1130 po_message_s.sql_error ('CREATE_BLANKET', l_progress, SQLCODE);
1131
1132 po_autocreate_pvt.wrapup ();
1133 RAISE;
1134 END;
1135
1136 /* ============================================================================
1137 NAME: calculate_tax
1138 DESC: Procedure to calculate Tax.
1139
1140 ARGS: IN : p_doc_rec doc_row_type -Some attribute values of the document
1141
1142 NOTE: Use the function PO_TAX_INTERFACE_PVT.calculate_tax()
1143
1144 ==============================================================================
1145 */
1146 PROCEDURE calculate_tax (p_doc_rec IN doc_row_type)
1147 IS
1148 l_api_name VARCHAR2 (30) := 'calculate_tax';
1149 l_progress VARCHAR2 (3) := '000';
1150 x_return_status VARCHAR2 (1);
1151 BEGIN
1152 IF g_debug_stmt
1153 THEN
1154 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1155 END IF;
1156
1157 po_tax_interface_pvt.calculate_tax
1158 (p_po_header_id => p_doc_rec.po_header_id,
1159 p_po_release_id => NULL,
1160 p_calling_program => 'POXBWVRP_PO',
1161 x_return_status => x_return_status
1162 );
1163 l_progress := '010';
1164
1165 IF g_debug_stmt
1166 THEN
1167 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
1168 END IF;
1169 EXCEPTION
1170 WHEN OTHERS
1171 THEN
1172 IF g_debug_unexp
1173 THEN
1174 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
1175 p_progress => l_progress
1176 );
1177 END IF;
1178
1179 po_message_s.sql_error ('CALCULATE_TAX', l_progress, SQLCODE);
1180
1181 --CLM Phase 2 changes : error handling
1182 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_CAL_TAX_ERR',x_token1_value => sqlerrm);
1183
1184 po_autocreate_pvt.wrapup ();
1185 RAISE;
1186 END;
1187
1188 /* ============================================================================
1189 NAME: copy_attachment
1190 DESC: Copy the Header and Line level attachment.
1191
1192 ARGS: IN : p_doc_rec doc_row_type -Some attribute values of the document
1193 IN : is_mod Y/N Is this a modification
1194
1195 NOTE: Use the FND function fnd_attached_documents2_pkg.copy_attachments()
1196
1197 ==============================================================================*/
1198 PROCEDURE copy_attachment (p_doc_rec IN doc_row_type, p_is_mod IN VARCHAR2 default 'N')
1199 IS
1200 l_api_name VARCHAR2 (30) := 'copy_attachment';
1201 l_progress VARCHAR2 (3) := '000';
1202 pk1_header VARCHAR2(100);
1203 pk1_line VARCHAR2(100);
1204 pk1_shipment VARCHAR2(100);
1205 l_draft_id NUMBER;
1206
1207 CURSOR c_line_attach
1208 IS
1209 SELECT plt.purchase_basis, pla.po_line_id, pla.line_num
1210 FROM po_lines_merge_v pla, po_line_types plt
1211 WHERE pla.po_header_id = po_autocreate_params.g_po_header_id
1212 AND pla.draft_id = l_draft_id
1213 AND plt.line_type_id(+) = pla.line_type_id
1214 AND EXISTS (
1215 SELECT 'Y'
1216 FROM po_lines_interface pli
1217 WHERE pli.line_num = pla.line_num
1218 AND pli.interface_header_id =
1219 po_autocreate_params.x_interface_header_id);
1220
1221 CURSOR c_copy_header_attachment
1222 IS
1223 SELECT pli.requisition_line_id, pli.created_by, pli.creation_date,
1224 pli.last_update_login, pli.last_updated_by,
1225 pli.last_update_date, pli.clm_info_flag,
1226 pli.auction_header_id, pli.auction_line_number,
1227 pli.bid_number, pli.bid_line_number, pli.interface_line_id
1228 FROM po_lines_interface pli
1229 WHERE pli.interface_header_id =
1230 po_autocreate_params.x_interface_header_id
1231 AND Nvl(pli.shipment_type, 'NONE') <> 'PRICE BREAK';
1232
1233 CURSOR c_interface_info (p_line_num NUMBER)
1234 IS
1235 SELECT pli.requisition_line_id, pli.created_by, pli.creation_date,
1236 pli.last_update_login, pli.last_updated_by,
1237 pli.last_update_date, pli.clm_info_flag,
1238 pli.auction_header_id, pli.auction_line_number,
1239 pli.bid_number, pli.bid_line_number, pli.interface_line_id,
1240 pli.shipment_type
1241 FROM po_lines_interface pli
1242 WHERE pli.interface_header_id =
1243 po_autocreate_params.x_interface_header_id
1244 AND pli.line_num = p_line_num;
1245
1246 CURSOR c_copy_lineloc_attachment (p_po_line_id NUMBER)
1247 IS
1248 SELECT pll.line_location_id
1249 FROM po_line_locations_merge_v pll,
1250 po_lines_interface pli,
1251 po_lines_all pla
1252 WHERE pll.po_line_id = p_po_line_id
1253 AND pll.draft_id = l_draft_id
1254 AND pli.interface_header_id =
1255 po_autocreate_params.g_po_header_id
1256 AND pli.shipment_num = pll.shipment_num
1257 AND pla.line_num = pli.line_num
1258 ORDER BY pll.line_location_id;
1259
1260 CURSOR c_copy_payitem_attach (
1261 p_intf_line_id NUMBER,
1262 p_line_location_id NUMBER
1263 )
1264 IS
1265 SELECT line_location_id
1266 FROM po_line_locations_interface plli
1267 WHERE plli.line_location_id <> p_line_location_id
1268 AND plli.interface_line_id = p_intf_line_id;
1269
1270 l_job_long_description po_requisition_lines_all.job_long_description%TYPE;
1271 l_who_rec po_negotiations_sv2.who_rec_type;
1272 x_requisition_header_id NUMBER;
1273 x_column1 VARCHAR2 (10);
1274 l_pk2 VARCHAR2 (30);
1275 --The following flag indicates whether copying the attachments from (all)the
1276 --sourcing entities need to be suppressed due to the grouping of lines.
1277 x_attch_suppress_flag VARCHAR2 (1) := 'N';
1278 l_one_time_att_doc_id NUMBER;
1279 x_line_location_id NUMBER;
1280 x_auction_header_id po_lines_interface.auction_header_id%TYPE;
1281 BEGIN
1282 IF g_debug_stmt
1283 THEN
1284 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1285 END IF;
1286
1287 SELECT MAX (pli.auction_header_id)
1288 INTO x_auction_header_id
1289 FROM po_lines_interface pli
1290 WHERE pli.interface_header_id =
1291 po_autocreate_params.x_interface_header_id;
1292
1293 IF p_is_mod = 'Y' THEN
1294 pk1_header := to_char(p_doc_rec.po_header_id) || '-' || to_char(p_doc_rec.draft_id);
1295 l_draft_id := p_doc_rec.draft_id;
1296 ELSE
1297 pk1_header := to_char(p_doc_rec.po_header_id);
1298 l_draft_id := -1;
1299 END IF;
1300
1301 -- Copying Sourcing Header Level attachment to PO header
1302 IF po_autocreate_params.g_interface_source_code = 'SOURCING'
1303 THEN
1304 -- copy attachments from negotiation header to blanket header
1305 po_negotiations_sv2.copy_attachments ('PON_AUCTION_HEADERS_ALL',
1306 x_auction_header_id,
1307 NULL,
1308 NULL,
1309 NULL,
1310 NULL,
1311 'PO_HEADERS',
1312 p_doc_rec.po_header_id,
1313 NULL,
1314 NULL,
1315 NULL,
1316 NULL,
1317 p_doc_rec.created_by,
1318 p_doc_rec.last_update_login,
1319 NULL,
1320 NULL,
1321 NULL,
1322 'NEG'
1323 );
1324 l_progress := '005';
1325
1326 FOR c_copy_header_attachment_rec IN c_copy_header_attachment
1327 LOOP
1328 IF NVL (c_copy_header_attachment_rec.clm_info_flag, 'N') <> 'Y'
1329 THEN
1330 l_progress := '006';
1331 -- build and attach negotiation header notes as to supplier attachments
1332 -- on po/blanket header.
1333 po_negotiations_sv2.add_attch_dynamic
1334 ('PON_AUC_SUPPLIER_HEADER_NOTES',
1335 c_copy_header_attachment_rec.auction_header_id,
1336 c_copy_header_attachment_rec.auction_line_number,
1337 c_copy_header_attachment_rec.bid_number,
1338 c_copy_header_attachment_rec.bid_line_number,
1339 'PO_HEADERS',
1340 p_doc_rec.po_header_id,
1341 p_doc_rec.created_by,
1342 p_doc_rec.last_update_login,
1343 NULL,
1344 NULL,
1345 NULL
1346 );
1347 l_progress := '007';
1348 -- Attach negotiation/bid header attributes as to supplier attachment on po/blanket header
1349 po_negotiations_sv2.add_attch_dynamic
1350 ('PON_BID_HEADER_ATTRIBUTES',
1351 c_copy_header_attachment_rec.auction_header_id,
1352 NULL,
1353 c_copy_header_attachment_rec.bid_number,
1354 NULL,
1355 'PO_HEADERS',
1356 p_doc_rec.po_header_id,
1357 p_doc_rec.created_by,
1358 p_doc_rec.last_update_login,
1359 NULL,
1360 NULL,
1361 NULL
1362 );
1363 l_progress := '008';
1364
1365 IF g_debug_stmt
1366 THEN
1367 po_debug.debug_stmt
1368 (p_log_head => g_log_head || l_api_name,
1369 p_token => l_progress,
1370 p_message => 'After copying attachments from Sourcing'
1371 );
1372 END IF;
1373 END IF;
1374 -- Nvl(c_copy_header_attachment_rec.clm_info_flag,'N')<>'Y'
1375 END LOOP; -- c_copy_header_attachment
1376 END IF; -- PO_AUTOCREATE_PARAMS.g_interface_source_code ='SOURCING'
1377
1378 -- Sourcing Header attachment copy ends here
1379
1380 -- Copying line level attachment
1381 FOR line_rec IN c_line_attach
1382 LOOP
1383 l_progress := '010';
1384 IF p_is_mod = 'Y' THEN
1385 pk1_line := to_char(line_rec.po_line_id) || '-' || to_char(p_doc_rec.draft_id);
1386 ELSE
1387 pk1_line := to_char(line_rec.po_line_id);
1388 END IF;
1389
1390
1391 FOR intf_rec IN c_interface_info (line_rec.line_num)
1392 LOOP
1393 ---------------------------------------------------------------------------
1394 -- Req Line (TEXT) -> PO Line (Attachment) --------------------------------
1395 ---------------------------------------------------------------------------
1396 -- "Temp Labor" Lines have a Job Long Description, which resides on the
1397 -- Requisition Line as a LONG Text column, but needs to be copied over
1398 -- as an attachment on the PO Line.
1399 --
1400 IF (line_rec.purchase_basis = 'TEMP LABOR')
1401 THEN
1402 l_progress := '020';
1403 -- Get the Job Long Description from the Req Line.
1404 l_job_long_description :=
1405 po_services_pvt.get_job_long_description
1406 (p_req_line_id => intf_rec.requisition_line_id);
1407
1408 -- If Job Long Description exists, convert it to an attachment.
1409 --
1410 IF (l_job_long_description IS NOT NULL)
1411 THEN
1412 l_progress := '030';
1413 -- Initialize Standard WHO Columns.
1414 --
1415 l_who_rec.created_by := intf_rec.created_by;
1416 l_who_rec.creation_date := intf_rec.creation_date;
1417 l_who_rec.last_update_login := intf_rec.last_update_login;
1418 l_who_rec.last_updated_by := intf_rec.last_updated_by;
1419 l_who_rec.last_update_date := intf_rec.last_update_date;
1420 l_progress := '040';
1421 -- Call Text-to-Attachment Conversion procedure
1422 --
1423 po_negotiations_sv2.convert_text_to_attachment
1424 (p_long_text => l_job_long_description,
1425 p_description => NULL,
1426 p_category_id => 33
1427 -- To Supplier
1428 ,
1429 p_to_entity_name => 'PO_LINES',
1430 p_to_pk1_value => line_rec.po_line_id,
1431 p_who_rec => l_who_rec
1432 );
1433 END IF; -- ( l_job_long_description IS NOT NULL )
1434 END IF; -- ( l_purchase_basis = 'TEMP LABOR' )
1435
1436 IF po_autocreate_params.g_interface_source_code NOT IN
1437 ('SOURCING', 'CONSUMPTION_ADVICE')
1438 THEN -- CONSIGNED FPI
1439 l_progress := '050';
1440 fnd_attached_documents2_pkg.copy_attachments
1441 ('REQ_LINES',
1442 intf_rec.requisition_line_id,
1443 '',
1444 '',
1445 '',
1446 '',
1447 'PO_LINES',
1448 pk1_line, --line_rec.po_line_id,
1449 '',
1450 '',
1451 '',
1452 '',
1453 intf_rec.created_by,
1454 intf_rec.last_update_login,
1455 '',
1456 '',
1457 ''
1458 );
1459 l_progress := '060';
1460
1461 -- Copy of the requisition header attachements to the purchase
1462 -- order line.
1463 SELECT requisition_header_id
1464 INTO x_requisition_header_id
1465 FROM po_requisition_lines_all --<Shared Proc FPJ>
1466 WHERE requisition_line_id = intf_rec.requisition_line_id;
1467
1468 l_progress := '070';
1469 fnd_attached_documents2_pkg.copy_attachments
1470 ('REQ_HEADERS',
1471 x_requisition_header_id,
1472 '',
1473 '',
1474 '',
1475 '',
1476 'PO_LINES',
1477 pk1_line, --line_rec.po_line_id,
1478 '',
1479 '',
1480 '',
1481 '',
1482 intf_rec.created_by,
1483 intf_rec.last_update_login,
1484 '',
1485 '',
1486 ''
1487 );
1488 END IF;
1489
1490 l_progress := '080';
1491
1492 IF g_debug_stmt
1493 THEN
1494 po_debug.debug_stmt
1495 (p_log_head => g_log_head || l_api_name,
1496 p_token => l_progress,
1497 p_message => 'Start: copy attachments/notes from negotiation/bid to po/blanket line'
1498 );
1499 END IF;
1500
1501 --copy attachments/notes from negotiation/bid to po/blanket line
1502 IF po_autocreate_params.g_interface_source_code = 'SOURCING'
1503 THEN
1504 IF po_autocreate_params.g_document_subtype = 'STANDARD'
1505 THEN
1506 IF intf_rec.requisition_line_id IS NOT NULL
1507 THEN
1508 x_column1 := 'NEGREQ';
1509 l_progress := '090';
1510
1511 SELECT requisition_header_id
1512 INTO x_requisition_header_id
1513 FROM po_requisition_lines_all --<Shared Proc FPJ>
1514 WHERE requisition_line_id = intf_rec.requisition_line_id;
1515 ELSE
1516 x_column1 := 'NEG';
1517 END IF; -- attach_rec.requisition_line_id is not null
1518 ELSIF po_autocreate_params.g_document_subtype = 'BLANKET'
1519 THEN --po_autocreate_params.g_document_subtype = 'STANDARD'
1520 x_column1 := 'NEG';
1521 END IF;
1522
1523 IF ( po_autocreate_params.g_document_subtype IN
1524 ('BLANKET', 'STANDARD')
1525 /*AND NVL (intf_rec.clm_info_flag, 'N') <> 'Y'*/ AND Nvl(intf_rec.shipment_type, 'NONE') <> 'PRICE BREAK'
1526 )
1527 THEN
1528 l_progress := '100';
1529 po_negotiations_sv2.handle_sourcing_attachments
1530 (intf_rec.auction_header_id,
1531 intf_rec.auction_line_number,
1532 intf_rec.bid_number,
1533 intf_rec.bid_line_number,
1534 x_requisition_header_id,
1535 intf_rec.requisition_line_id,
1536 line_rec.po_line_id,
1537 x_column1,
1538 x_attch_suppress_flag,
1539 intf_rec.created_by,
1540 intf_rec.last_update_login
1541 );
1542 END IF;
1543 END IF;
1544
1545 l_progress := '110';
1546
1547 IF g_debug_stmt
1548 THEN --< Bug 3210331: use proper debugging >
1549 po_debug.debug_stmt
1550 (p_log_head => g_log_head || l_api_name,
1551 p_token => l_progress,
1552 p_message => 'End: copy attachments/notes from negotiation/bid to po/blanket line'
1553 );
1554 END IF;
1555
1556 -- If autocreating a SPO or PPO, and the requisition line has a one-time
1557 -- location, move the attachment from the PO line to the PO shipment
1558 IF (po_autocreate_params.g_document_subtype IN
1559 ('STANDARD', 'PLANNED')
1560 )
1561 AND (po_autocreate_mainproc_pvt.has_one_time_location
1562 (intf_rec.requisition_line_id)
1563 )
1564 THEN
1565 BEGIN
1566 l_progress := '120';
1567
1568 IF g_debug_stmt
1569 THEN
1570 po_debug.debug_stmt
1571 (p_log_head => g_log_head || l_api_name,
1572 p_token => l_progress,
1573 p_message => 'Before selecting one-time attachment'
1574 );
1575 END IF;
1576
1577 --SQL What: Locate the one-time location attachment currently under
1578 -- the PO_LINES entity by it's unique iP identifier prefix
1579 --SQL Why: Need the attached_document_id to move the attachment
1580 SELECT fad.attached_document_id
1581 INTO l_one_time_att_doc_id
1582 FROM fnd_attached_documents fad, fnd_documents_tl fdt
1583 WHERE fad.entity_name = 'PO_LINES'
1584 AND fad.pk1_value = TO_CHAR (line_rec.po_line_id)
1585 AND fad.document_id = fdt.document_id
1586 AND fdt.LANGUAGE = USERENV ('LANG')
1587 AND fdt.description LIKE 'POR:%'; -- iP unique identifier
1588
1589 l_progress := '130';
1590
1591 OPEN c_copy_lineloc_attachment (line_rec.po_line_id);
1592
1593 FETCH c_copy_lineloc_attachment
1594 INTO x_line_location_id;
1595
1596 CLOSE c_copy_lineloc_attachment;
1597
1598 IF p_is_mod = 'Y' THEN
1599 pk1_shipment := to_char(x_line_location_id) || '-' || to_char(p_doc_rec.draft_id);
1600 ELSE
1601 pk1_shipment := to_char(x_line_location_id);
1602 END IF;
1603
1604 -- Move the attachment from the PO line to the PO shipment
1605 UPDATE fnd_attached_documents
1606 SET entity_name = 'PO_SHIPMENTS',
1607 pk1_value = pk1_shipment, --TO_CHAR (x_line_location_id),
1608 pk2_value = 'ONE_TIME_LOCATION'
1609 WHERE attached_document_id = l_one_time_att_doc_id;
1610 EXCEPTION
1611 WHEN NO_DATA_FOUND
1612 THEN
1613 -- If cannot locate one-time loc attchmnt, do nothing. This
1614 -- supports original FPH behavior.
1615 IF g_debug_stmt
1616 THEN
1617 po_debug.debug_stmt
1618 (p_log_head => g_log_head || l_api_name,
1619 p_token => l_progress,
1620 p_message => 'One-time loc attachment missing iP prefix, so do not try to move'
1621 );
1622 END IF;
1623 END;
1624 END IF;
1625
1626 -- <Complex Work R12 Start> : Copy attachments to payitems
1627 IF (po_autocreate_params.g_is_complex_work_po)
1628 THEN
1629 l_progress := '140';
1630
1631 FOR c_payitem_rec IN
1632 c_copy_payitem_attach (intf_rec.interface_line_id,
1633 x_line_location_id
1634 )
1635 LOOP
1636 -- copy one-time location from first actual shipment to
1637 -- each payitem.
1638 l_progress := '150';
1639
1640 IF (l_one_time_att_doc_id IS NOT NULL)
1641 THEN
1642 fnd_attached_documents2_pkg.copy_attachments
1643 (x_from_entity_name => 'PO_SHIPMENTS',
1644 x_from_pk1_value => x_line_location_id,
1645 x_from_pk2_value => 'ONE_TIME_LOCATION',
1646 x_to_entity_name => 'PO_SHIPMENTS',
1647 x_to_pk1_value => c_payitem_rec.line_location_id,
1648 x_to_pk2_value => 'ONE_TIME_LOCATION',
1649 x_created_by => intf_rec.created_by,
1650 x_last_update_login => intf_rec.last_update_login
1651 );
1652 END IF;
1653
1654 l_progress := '160';
1655 po_negotiations_sv2.copy_sourcing_payitem_atts
1656 (p_line_location_id => c_payitem_rec.line_location_id,
1657 p_created_by => intf_rec.created_by,
1658 p_last_update_login => intf_rec.last_update_login,
1659 p_auction_header_id => intf_rec.auction_header_id,
1660 p_auction_line_number => intf_rec.auction_line_number,
1661 p_bid_number => intf_rec.bid_number,
1662 p_bid_line_number => intf_rec.bid_line_number
1663 );
1664 END LOOP;
1665 END IF; -- is Complex Work PO
1666 END LOOP;
1667 END LOOP;
1668
1669 l_progress := '200';
1670
1671 IF g_debug_stmt
1672 THEN
1673 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
1674 END IF;
1675 EXCEPTION
1676 WHEN OTHERS
1677 THEN
1678 IF g_debug_unexp
1679 THEN
1680 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
1681 p_progress => l_progress
1682 );
1683 END IF;
1684
1685 po_message_s.sql_error ('COPY_ATTACHMENT', l_progress, SQLCODE);
1686
1687 --CLM Phase 2 changes : error handling
1688 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_CPY_ATTCH_ERR',x_token1_value => sqlerrm);
1689
1690 po_autocreate_pvt.wrapup ();
1691 RAISE;
1692 END copy_attachment;
1693
1694 /* ============================================================================
1695 NAME: create_doc_from_draft_check
1696 DESC: Check whether document should be created from draft.
1697
1698 ==============================================================================
1699 */
1700 FUNCTION create_doc_from_draft_check (p_po_header_id IN NUMBER)
1701 RETURN VARCHAR2
1702 IS
1703 l_api_name VARCHAR2 (30) := 'create_doc_from_draft_check';
1704 l_progress VARCHAR2 (3) := '000';
1705 x_create_doc_flag VARCHAR2 (1) := 'Y';
1706 BEGIN
1707 IF g_debug_stmt
1708 THEN
1709 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1710 END IF;
1711
1712 SELECT 'N'
1713 INTO x_create_doc_flag
1714 FROM po_headers_all poh, po_doc_style_headers pdsh
1715 WHERE poh.po_header_id = p_po_header_id
1716 AND poh.style_id = pdsh.style_id
1717 AND poh.approved_flag = 'Y'
1718 AND pdsh.change_process_type = 'MODIFICATION';
1719
1720 l_progress := '020';
1721
1722 IF g_debug_stmt
1723 THEN
1724 po_debug.debug_stmt
1725 (p_log_head => g_log_head || l_api_name,
1726 p_token => l_progress,
1727 p_message => 'End: create_doc_from_draft_check: x_create_doc_flag : '
1728 || x_create_doc_flag
1729 );
1730 END IF;
1731
1732 l_progress := '020';
1733
1734 IF g_debug_stmt
1735 THEN
1736 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
1737 END IF;
1738
1739 RETURN x_create_doc_flag;
1740 EXCEPTION
1741 WHEN OTHERS
1742 THEN
1743 RETURN x_create_doc_flag;
1744 END create_doc_from_draft_check;
1745
1746 /* ============================================================================
1747 NAME: set_clm_structure
1748
1749 DESC: Performs Line number Display Processing.
1750 Processing Logic is similiar to the derivation of Line Number generation.
1751
1752 ARGS: IN : NONE
1753
1754 Steps:
1755 1. Get All Slins
1756 2. For each Slin
1757 a) Get the Group Line Id
1758 b) Find the Line Id of line in Interface table whose Req Line Matches the above Group Line Id
1759 c) Update Slin's Group Line Id with the Line Id from step2.
1760 3. Get All options
1761 4. For each Option Line.
1762 a) Get the Base Line Id (clm_base_line_num)
1763 b) Find the Line Id of line in Interface table whose Req Line Matches the above Base Line Id
1764 c) Update Slin's clm_base_line_num with the Line Id from step2.
1765
1766 NOTE: This procedure is called from process()
1767
1768 ==============================================================================*/
1769 PROCEDURE set_clm_structure
1770 IS
1771 l_api_name VARCHAR2 (30) := 'set_clm_structure';
1772 l_progress VARCHAR2 (3) := '000';
1773 l_new_option_flag VARCHAR2(1) := 'Y';
1774 l_curr_clm_base_line_num NUMBER := -1;
1775 --bug 13584453
1776 l_next_option_num NUMBER := 1;
1777 l_draft_id NUMBER := -1;
1778
1779
1780 CURSOR c_old_parent
1781 IS
1782 SELECT pla.group_line_id, pla.po_line_id
1783 FROM po_lines_draft_all pla, po_lines_interface pli
1784 WHERE pli.group_line_id IS NOT NULL
1785 AND pli.interface_header_id =
1786 po_autocreate_params.x_interface_header_id
1787 AND pli.line_num = pla.line_num
1788 AND pla.po_header_id = po_autocreate_params.g_po_header_id
1789 AND pla.draft_id = po_autocreate_params.g_draft_id;
1790
1791 CURSOR c_old_base
1792 IS
1793 SELECT pla.clm_base_line_num, pla.po_line_id
1794 FROM po_lines_draft_all pla, po_lines_interface pli
1795 WHERE pli.clm_base_line_num IS NOT NULL
1796 AND pli.interface_header_id =
1797 po_autocreate_params.x_interface_header_id
1798 AND pli.line_num = pla.line_num
1799 AND pla.po_header_id = po_autocreate_params.g_po_header_id
1800 AND pla.draft_id = po_autocreate_params.g_draft_id;
1801
1802 CURSOR c_old_option_line
1803 IS
1804 SELECT pla.line_num_display,
1805 pla.clm_base_line_num,
1806 pla.po_line_id,
1807 rownum --bug 13584453
1808 FROM po_lines_draft_all pla,
1809 po_lines_interface pli
1810 WHERE pli.clm_base_line_num IS NOT NULL
1811 AND pli.interface_header_id = po_autocreate_params.x_interface_header_id
1812 AND pli.line_num = pla.line_num
1813 AND pla.po_header_id = po_autocreate_params.g_po_header_id
1814 AND pla.draft_id = po_autocreate_params.g_draft_id
1815 order by pla.clm_base_line_num,
1816 pla.line_num_display;
1817
1818 BEGIN
1819 IF g_debug_stmt
1820 THEN
1821 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
1822 END IF;
1823
1824 l_progress := 010;
1825
1826 FOR l_rec IN c_old_parent
1827 LOOP
1828 UPDATE po_lines_draft_all
1829 SET group_line_id =
1830 (SELECT pla.po_line_id
1831 FROM po_lines_interface pli, po_lines_draft_all pla
1832 WHERE 1 = 1
1833 AND pla.po_header_id =
1834 po_autocreate_params.g_po_header_id
1835 AND pla.group_line_id IS NULL
1836 AND pla.line_num = pli.line_num
1837 AND pli.interface_line_id = l_rec.group_line_id
1838 AND pla.draft_id = po_autocreate_params.g_draft_id)
1839 WHERE po_line_id = l_rec.po_line_id
1840 AND draft_id = po_autocreate_params.g_draft_id;
1841 END LOOP;
1842
1843 IF g_debug_stmt
1844 THEN
1845 po_debug.debug_stmt
1846 (p_log_head => g_log_head || l_api_name,
1847 p_token => l_progress,
1848 p_message => 'After setting the CLIN/SLIN structure'
1849 );
1850 END IF;
1851
1852 l_progress := 020;
1853
1854 FOR l_rec IN c_old_base
1855 LOOP
1856 UPDATE po_lines_draft_all
1857 SET clm_base_line_num =
1858 (SELECT pla.po_line_id
1859 FROM po_lines_interface pli, po_lines_draft_all pla
1860 WHERE 1 = 1
1861 AND pla.po_header_id =
1862 po_autocreate_params.g_po_header_id
1863 AND pla.clm_base_line_num IS NULL
1864 AND pla.line_num = pli.line_num
1865 AND pli.interface_line_id = l_rec.clm_base_line_num
1866 AND pla.draft_id = po_autocreate_params.g_draft_id)
1867 WHERE po_line_id = l_rec.po_line_id
1868 AND draft_id = po_autocreate_params.g_draft_id;
1869 END LOOP;
1870
1871
1872 /*CLM Phase-2 : Autocreate Grouping*/
1873 --Updating the option numbers to po_lines_draft_all
1874 FOR l_rec IN c_old_option_line
1875 LOOP
1876 IF g_debug_stmt
1877 THEN
1878 po_debug.debug_stmt
1879 (p_log_head => g_log_head || l_api_name,
1880 p_token => l_progress,
1881 p_message => 'po_autocreate_params.g_mode: '||po_autocreate_params.g_mode
1882 );
1883 END IF;
1884 IF po_autocreate_params.g_mode = 'NEW' THEN
1885
1886 /*bug 13584453 the option num was always set as 1 due to this incorrect logic.
1887 Getting the max(clm_otiopn_num) for the given clm_base_line_num
1888 and setting the subsequent option nums by incrementing it.*/
1889 IF l_curr_clm_base_line_num <> l_rec.clm_base_line_num THEN
1890 l_curr_clm_base_line_num := l_rec.clm_base_line_num;
1891 l_new_option_flag := 'Y';
1892 ELSE
1893 l_new_option_flag := 'N';
1894 END IF;
1895
1896 IF l_new_option_flag = 'Y' THEN
1897 l_next_option_num := 1;
1898 ELSE
1899 l_next_option_num := l_next_option_num + 1;
1900 END IF;
1901
1902 UPDATE po_lines_draft_all
1903 SET clm_option_num = l_next_option_num
1904 WHERE po_line_id = l_rec.po_line_id
1905 AND draft_id = po_autocreate_params.g_draft_id;
1906
1907 ELSIF po_autocreate_params.g_mode = 'ADD' THEN
1908
1909 --bug 13584453 start
1910
1911 IF l_curr_clm_base_line_num <> l_rec.clm_base_line_num THEN
1912 l_curr_clm_base_line_num := l_rec.clm_base_line_num;
1913 END IF;
1914
1915 IF create_doc_from_draft_check (po_autocreate_params.g_po_header_id) = 'Y'
1916 THEN
1917 l_draft_id := -1;
1918 ELSE
1919 l_draft_id := po_autocreate_params.g_draft_id;
1920 END IF;
1921
1922 IF g_debug_stmt
1923 THEN
1924 po_debug.debug_stmt
1925 (p_log_head => g_log_head || l_api_name,
1926 p_token => l_progress,
1927 p_message => 'l_draft_id: '||l_draft_id
1928 );
1929 END IF;
1930
1931 SELECT NVL(MAX(clm_option_num), 0)
1932 INTO l_next_option_num
1933 FROM po_lines_merge_v
1934 WHERE clm_base_line_num = l_rec.clm_base_line_num
1935 AND draft_id = l_draft_id
1936 ;
1937
1938 UPDATE po_lines_draft_all
1939 SET clm_option_num = l_next_option_num + DECODE(l_draft_id, -1, l_rec.rownum, 1)
1940 WHERE po_line_id = l_rec.po_line_id
1941 AND draft_id = po_autocreate_params.g_draft_id;
1942
1943 END IF;
1944
1945 IF g_debug_stmt
1946 THEN
1947 po_debug.debug_stmt
1948 (p_log_head => g_log_head || l_api_name,
1949 p_token => l_progress,
1950 p_message => 'l_next_option_num: '||l_next_option_num||'clm_base_line_num: '||l_rec.clm_base_line_num||'po_line_id: '||l_rec.po_line_id||'l_rec.rownum: '||l_rec.rownum
1951 );
1952 END IF;
1953 --bug 13584453 end
1954
1955
1956 END LOOP;
1957 /*CLM Phas-2 changes end*/
1958
1959 IF g_debug_stmt
1960 THEN
1961 po_debug.debug_stmt
1962 (p_log_head => g_log_head || l_api_name,
1963 p_token => l_progress,
1964 p_message => 'After setting the Option/Base structure'
1965 );
1966 END IF;
1967
1968 l_progress := 30;
1969
1970 UPDATE po_distributions_draft_all pda
1971 SET group_line_id = ( SELECT pld.po_line_id
1972 FROM po_lines_draft_all pld , po_lines_interface PLI
1973 WHERE pld.draft_id = po_autocreate_params.g_draft_id
1974 AND pld.po_header_id = po_autocreate_params.g_po_header_id
1975 AND pld.line_num = pli.line_num
1976 AND PLI.interface_line_id = pda.group_line_id
1977 )
1978 WHERE pda.po_header_id = po_autocreate_params.g_po_header_id
1979 AND pda.draft_id = po_autocreate_params.g_draft_id;
1980
1981 IF g_debug_stmt
1982 THEN
1983 po_debug.debug_stmt
1984 (p_log_head => g_log_head || l_api_name,
1985 p_token => l_progress,
1986 p_message => 'Number of distributions drafts updated '|| SQL%ROWCOUNT
1987 );
1988
1989 END IF;
1990
1991 /*CLM Phase-2 : Autocreate Grouping*/
1992 -- If you have dist at both clin and slin level, move all dist to clin level.
1993 UPDATE po_distributions_draft_all pda3
1994 SET pda3.group_line_id = NULL
1995 WHERE pda3.po_header_id = po_autocreate_params.g_po_header_id
1996 AND pda3.draft_id = po_autocreate_params.g_draft_id
1997 AND pda3.po_line_id IN ( SELECT pda1.po_line_id
1998 FROM po_distributions_draft_all pda1
1999 WHERE pda1.po_header_id = po_autocreate_params.g_po_header_id
2000 AND pda1.draft_id = po_autocreate_params.g_draft_id
2001 AND pda1.group_line_id IS NULL
2002 AND EXISTS ( SELECT pda2.group_line_id
2003 FROM po_distributions_draft_all pda2
2004 WHERE pda2.po_line_id = pda1.po_line_id
2005 AND pda2.group_line_id IS NOT NULL
2006 AND pda2.po_header_id = po_autocreate_params.g_po_header_id
2007 AND pda2.draft_id = po_autocreate_params.g_draft_id
2008 )
2009 );
2010
2011 /*CLM Phas-2 changes end*/
2012
2013
2014 IF g_debug_stmt
2015 THEN
2016 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
2017 END IF;
2018 EXCEPTION
2019 WHEN OTHERS
2020 THEN
2021 IF g_debug_unexp
2022 THEN
2023 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
2024 p_progress => l_progress
2025 );
2026 END IF;
2027
2028 --CLM Phase 2 changes : error handling
2029 PO_AUTOCREATE_PVT.report_error('PO_AUTO_POST_CLM_STR_ERR',x_token1_value => sqlerrm);
2030
2031 po_autocreate_pvt.wrapup ();
2032 po_message_s.sql_error ('SET_CLM_STRUCTURE', l_progress, SQLCODE);
2033 RAISE;
2034 END set_clm_structure;
2035
2036
2037 /* ============================================================================
2038 NAME: set_line_num_display
2039
2040 DESC: Performs Line number Display Processing.
2041 Processing Logic is similiar to the derivation of Line Number generation.
2042 Should be called only when interface source code is not equal to 'SOURCING'
2043
2044 ARGS: IN : NONE
2045
2046 NOTE: This procedure is called from process()
2047
2048 ==============================================================================*/
2049 PROCEDURE set_line_num_display
2050 IS
2051 l_api_name VARCHAR2 (30) := 'set_line_num_display';
2052 l_progress VARCHAR2 (3) := '000';
2053 l_clin_num VARCHAR2 (10);
2054 l_info_slin_num VARCHAR2 (10);
2055 l_priced_slin_num VARCHAR2 (10);
2056
2057 CURSOR c_clin
2058 IS
2059 SELECT po_line_id, line_num_display
2060 FROM po_lines_all
2061 WHERE po_header_id = po_autocreate_params.g_po_header_id
2062 AND group_line_id IS NULL
2063 ORDER BY line_num;
2064
2065 CURSOR c_info_slin (p_group_line_id NUMBER)
2066 IS
2067 SELECT po_line_id, line_num_display
2068 FROM po_lines_all
2069 WHERE po_header_id = po_autocreate_params.g_po_header_id
2070 AND group_line_id = p_group_line_id
2071 AND NVL (clm_info_flag, 'N') = 'Y'
2072 ORDER BY line_num;
2073
2074 CURSOR c_priced_slin (p_group_line_id NUMBER)
2075 IS
2076 SELECT po_line_id, line_num_display
2077 FROM po_lines_all
2078 WHERE po_header_id = po_autocreate_params.g_po_header_id
2079 AND group_line_id = p_group_line_id
2080 AND NVL (clm_info_flag, 'N') = 'N'
2081 ORDER BY line_num;
2082
2083 CURSOR c_mismatch
2084 IS
2085 SELECT PARENT.line_num_display, CHILD.po_line_id
2086 FROM po_lines_all PARENT, po_lines_all CHILD
2087 WHERE PARENT.po_header_id = po_autocreate_params.g_po_header_id
2088 AND PARENT.group_line_id IS NULL
2089 AND CHILD.po_header_id = po_autocreate_params.g_po_header_id
2090 AND CHILD.group_line_id IS NOT NULL
2091 AND PARENT.po_line_id = CHILD.group_line_id
2092 AND PARENT.line_num_display <>
2093 SUBSTR (CHILD.line_num_display, 1, 4);
2094
2095 x_document_num VARCHAR2 (30);
2096 x_document_type_code VARCHAR2 (25);
2097 x_document_subtype VARCHAR2 (25);
2098 x_group_code VARCHAR2 (30);
2099 x_action VARCHAR2 (25);
2100 x_mode VARCHAR2 (25);
2101 BEGIN
2102 IF g_debug_stmt
2103 THEN
2104 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
2105 END IF;
2106
2107 -- Fetch the Docuemnt Type Code, Doc Sub type, group_cod,action
2108 SELECT phi.document_num, phi.document_type_code, phi.document_subtype,
2109 phi.group_code, phi.action
2110 INTO x_document_num, x_document_type_code, x_document_subtype,
2111 x_group_code, x_mode
2112 FROM po_headers_interface phi
2113 WHERE phi.interface_header_id =
2114 po_autocreate_params.x_interface_header_id;
2115
2116 IF (po_autocreate_params.g_group_code = 'REQUISITION')
2117 THEN
2118 IF (po_autocreate_params.g_mode = 'NEW')
2119 THEN
2120 -- Create a new PO with Req. lines in
2121 -- the same order as on the requisition.
2122 -- The interface table will hold the requisition line id
2123 -- that we need to get the req line number from.
2124 -- We need to update the shipment number to 1.
2125 l_progress := '030';
2126
2127 IF (x_document_subtype IN ('STANDARD', 'PLANNED'))
2128 THEN
2129 -- For Standard and Planned POs, the line number
2130 -- will be the same as the req line number if the profile
2131 -- is set to 'Y' otherwise use sequential numbers
2132 IF (fnd_profile.VALUE ('PO_USE_REQ_NUM_IN_AUTOCREATE') = 'Y')
2133 THEN
2134 -- use requisition numbers
2135 l_progress := '035';
2136
2137 UPDATE po_lines_all pla
2138 SET pla.line_num_display =
2139 (SELECT prl.line_num_display
2140 FROM po_requisition_lines_all prl,
2141 po_lines_interface pli
2142 WHERE prl.requisition_line_id =
2143 pli.requisition_line_id
2144 AND pli.interface_header_id =
2145 po_autocreate_params.x_interface_header_id
2146 AND pli.line_num = pla.line_num)
2147 WHERE pla.po_header_id =
2148 po_autocreate_params.g_po_header_id
2149 AND pla.line_num_display IS NULL;
2150 ELSE
2151 -- use sequential numbers
2152 l_progress := '040';
2153 l_clin_num := '0001';
2154
2155 FOR clin_rec IN c_clin
2156 LOOP
2157 UPDATE po_lines_all
2158 SET line_num_display = l_clin_num
2159 WHERE po_line_id = clin_rec.po_line_id
2160 AND line_num_display IS NULL;
2161
2162 l_info_slin_num := l_clin_num || '01';
2163
2164 FOR info_slin_rec IN c_info_slin (clin_rec.po_line_id)
2165 LOOP
2166 UPDATE po_lines_all
2167 SET line_num_display = l_info_slin_num
2168 WHERE po_line_id = info_slin_rec.po_line_id;
2169
2170 l_info_slin_num :=
2171 pon_clo_renumber_pkg.increment_info_slin_number
2172 (l_info_slin_num);
2173 END LOOP;
2174
2175 l_priced_slin_num := l_clin_num || 'AA';
2176
2177 FOR priced_slin_rec IN c_priced_slin (clin_rec.po_line_id)
2178 LOOP
2179 UPDATE po_lines_all
2180 SET line_num_display = l_priced_slin_num
2181 WHERE po_line_id = priced_slin_rec.po_line_id;
2182
2183 l_priced_slin_num :=
2184 pon_clo_renumber_pkg.increment_priced_slin_number
2185 (l_priced_slin_num);
2186 END LOOP;
2187
2188 l_clin_num :=
2189 pon_clo_renumber_pkg.increment_clin_number (l_clin_num);
2190 END LOOP;
2191 END IF; -- if fnd_profile.value(PO_USE_REQ_NUM...) = 'Y'
2192 END IF; -- x_document_subtype IN ('STANDARD', 'PLANNED')
2193 ELSE
2194 -- mode = 'ADD'
2195 -- add to a po with the same order as on the req.
2196 IF (x_document_subtype IN ('STANDARD', 'PLANNED'))
2197 THEN
2198 -- The inteface table will hold the requisition line id that we
2199 -- will use to get the line number. Select the maximum line number
2200 -- that exists on the purchase order. Update the line number in
2201 -- the interface talbe to be the req. line number + max po line num.
2202 -- Shipment num should be 1.
2203 l_progress := '120';
2204
2205 IF g_debug_stmt
2206 THEN
2207 po_debug.debug_stmt
2208 (p_log_head => g_log_head || l_api_name,
2209 p_token => l_progress,
2210 p_message => 'set_line_num_display mode is :'
2211 || po_autocreate_params.g_mode
2212 );
2213 END IF;
2214
2215 l_progress := '140';
2216
2217 FOR clin_rec IN c_clin
2218 LOOP
2219 IF clin_rec.line_num_display IS NULL
2220 THEN
2221 l_clin_num :=
2222 pon_clo_renumber_pkg.next_clin_num_wrapper
2223 (po_autocreate_params.g_po_header_id,
2224 'PO'
2225 );
2226
2227 UPDATE po_lines_all
2228 SET line_num_display = l_clin_num
2229 WHERE po_line_id = clin_rec.po_line_id;
2230
2231 l_info_slin_num := l_clin_num || '01';
2232
2233 FOR info_slin_rec IN c_info_slin (clin_rec.po_line_id)
2234 LOOP
2235 UPDATE po_lines_all
2236 SET line_num_display = l_info_slin_num
2237 WHERE po_line_id = info_slin_rec.po_line_id;
2238
2239 l_info_slin_num :=
2240 pon_clo_renumber_pkg.increment_info_slin_number
2241 (l_info_slin_num);
2242 END LOOP;
2243
2244 l_priced_slin_num := l_clin_num || 'AA';
2245
2246 FOR priced_slin_rec IN c_priced_slin (clin_rec.po_line_id)
2247 LOOP
2248 UPDATE po_lines_all
2249 SET line_num_display = l_priced_slin_num
2250 WHERE po_line_id = priced_slin_rec.po_line_id;
2251
2252 l_priced_slin_num :=
2253 pon_clo_renumber_pkg.increment_priced_slin_number
2254 (l_priced_slin_num);
2255 END LOOP;
2256 END IF;
2257 END LOOP;
2258 END IF; -- x_document_subtype IN ('STANDARD', 'PLANNED')
2259 END IF; -- mode is NEW/ADD
2260 ELSE
2261 -- g_group_code = 'DEFAULT'
2262 IF (x_document_subtype IN ('STANDARD', 'PLANNED', 'BLANKET'))
2263 THEN
2264 l_progress := '230';
2265
2266 IF g_debug_stmt
2267 THEN
2268 po_debug.debug_stmt (p_log_head => g_log_head
2269 || l_api_name,
2270 p_token => l_progress,
2271 p_message => 'Grouping is default'
2272 );
2273 END IF;
2274
2275 FOR clin_rec IN c_clin
2276 LOOP
2277 IF clin_rec.line_num_display IS NULL
2278 THEN
2279 l_clin_num :=
2280 pon_clo_renumber_pkg.next_clin_num_wrapper
2281 (po_autocreate_params.g_po_header_id,
2282 'PO'
2283 );
2284
2285 UPDATE po_lines_all
2286 SET line_num_display = l_clin_num
2287 WHERE po_line_id = clin_rec.po_line_id;
2288
2289 l_info_slin_num := l_clin_num || '01';
2290
2291 FOR info_slin_rec IN c_info_slin (clin_rec.po_line_id)
2292 LOOP
2293 UPDATE po_lines_all
2294 SET line_num_display = l_info_slin_num
2295 WHERE po_line_id = info_slin_rec.po_line_id;
2296
2297 l_info_slin_num :=
2298 pon_clo_renumber_pkg.increment_info_slin_number
2299 (l_info_slin_num);
2300 END LOOP;
2301
2302 l_priced_slin_num := l_clin_num || 'AA';
2303
2304 FOR priced_slin_rec IN c_priced_slin (clin_rec.po_line_id)
2305 LOOP
2306 UPDATE po_lines_all
2307 SET line_num_display = l_priced_slin_num
2308 WHERE po_line_id = priced_slin_rec.po_line_id;
2309
2310 l_priced_slin_num :=
2311 pon_clo_renumber_pkg.increment_priced_slin_number
2312 (l_priced_slin_num);
2313 END LOOP;
2314 END IF;
2315 END LOOP;
2316 END IF;
2317 -- IF (x_document_subtype IN ('STANDARD', 'PLANNED', 'BLANKET'))
2318 END IF; -- g_group_code = 'DEFAULT'
2319
2320 -- If there is a mismatch is between clin number/SLIn number. Correct It.
2321 FOR mis_rec IN c_mismatch
2322 LOOP
2323 UPDATE po_lines_all pla
2324 SET pla.line_num_display =
2325 mis_rec.line_num_display
2326 || SUBSTR (pla.line_num_display, 5, 2)
2327 WHERE pla.po_line_id = mis_rec.po_line_id;
2328 END LOOP;
2329
2330 IF g_debug_stmt
2331 THEN
2332 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
2333 END IF;
2334 EXCEPTION
2335 WHEN OTHERS
2336 THEN
2337 IF g_debug_unexp
2338 THEN
2339 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
2340 p_progress => l_progress
2341 );
2342 END IF;
2343
2344 --CLM Phase 2 changes : error handling
2345 PO_AUTOCREATE_PVT.report_error('PO_AUTO_SET_LINE_NUM_DISP_ERR',x_token1_value => sqlerrm);
2346
2347 -- PO_AUTOCREATE_PVT.wrapup();
2348 po_message_s.sql_error ('SET_LINE_NUM_DISPLAY', l_progress, SQLCODE);
2349 RAISE;
2350 END set_line_num_display;
2351
2352
2353 PROCEDURE update_uda_template_id
2354 IS
2355
2356 l_api_name VARCHAR2 (30) := 'update_uda_template_id';
2357 l_progress VARCHAR2 (3) := '000';
2358
2359
2360 l_header_uda_tmpl_id NUMBER;
2361 l_line_uda_tmpl_id NUMBER;
2362 l_ship_uda_tmpl_id NUMBER;
2363
2364 x_doc_level_tbl po_tbl_varchar30;
2365
2366 l_functional_area VARCHAR2(100) := 'PURCHASING';
2367
2368 CURSOR c_lines
2369 IS
2370
2371 SELECT pld.po_line_id
2372 FROM po_lines_draft_all pld, po_lines_interface PLI
2373 WHERE pld.uda_template_id IS NULL
2374 AND PLI.interface_header_id = po_autocreate_params.g_interface_header_id
2375 AND PLD.po_header_id = po_autocreate_params.g_po_header_id
2376 AND pld. line_num =PLI.line_num
2377 AND pld.draft_id = po_autocreate_params.g_draft_id;
2378
2379
2380 CURSOR c_shipments
2381 IS
2382
2383 SELECT pll.line_location_id
2384 FROM po_lines_draft_all pld, po_lines_interface PLI, po_line_locations_draft_all pll
2385 WHERE pll.uda_template_id IS NULL
2386 AND PLI.interface_header_id = po_autocreate_params.g_interface_header_id
2387 AND PLD.po_header_id = po_autocreate_params.g_po_header_id
2388 AND pld. line_num =PLI.line_num
2389 AND pld.draft_id = po_autocreate_params.g_draft_id
2390 AND pld.po_line_id = pll.po_line_id
2391 AND pll.shipment_num=PLI.shipment_num
2392 AND pll.draft_id = po_autocreate_params.g_draft_id;
2393
2394 l_style_id NUMBER;
2395 l_template_id NUMBER;
2396
2397 l_return_status VARCHAR2(1);
2398 l_err_msg VARCHAR2(100);
2399
2400
2401 BEGIN
2402 IF g_debug_stmt
2403 THEN
2404 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
2405 END IF;
2406
2407 l_progress := '020';
2408 SELECT style_id
2409 INTO l_style_id
2410 FROM po_headers_interface
2411 WHERE interface_header_id = po_autocreate_params.g_interface_header_id;
2412
2413 IF g_debug_stmt
2414 THEN
2415 po_debug.debug_stmt
2416 (p_log_head => g_log_head || l_api_name,
2417 p_token => l_progress,
2418 p_message => 'Style Id is : '|| l_style_id
2419 );
2420 END IF;
2421
2422
2423 l_progress := '040';
2424 SELECT DISTINCT document_level
2425 BULK COLLECT INTO x_doc_level_tbl
2426 FROM po_uda_ag_templates
2427 WHERE functional_area = l_functional_area
2428 AND document_type = po_autocreate_params.g_document_subtype
2429 AND document_style_id = l_style_id;
2430
2431 IF g_debug_stmt
2432 THEN
2433 po_debug.debug_var
2434 (p_log_head => g_log_head || l_api_name,
2435 p_progress => l_progress,
2436 p_name => 'x_doc_level_tbl',
2437 p_value => x_doc_level_tbl
2438 );
2439 END IF;
2440
2441 FOR i IN 1..x_doc_level_tbl.Count
2442 LOOP
2443
2444 l_progress := '060';
2445 l_template_id := po_uda_data_util.get_template_id( p_functional_area => l_functional_area,
2446 p_document_type => po_autocreate_params.g_document_subtype,
2447 p_document_style_id => l_style_id,
2448 p_document_level => x_doc_level_tbl(i),
2449 p_input_date => SYSDATE,
2450 x_return_status => l_return_status,
2451 x_err_msg => l_err_msg
2452 );
2453 IF g_debug_stmt THEN
2454 po_debug.debug_stmt
2455 (p_log_head => g_log_head || l_api_name,
2456 p_token => l_progress,
2457 p_message => 'After getting the UDA template id: ' ||l_template_id
2458 );
2459 END IF;
2460
2461
2462 IF l_return_status = 'S' THEN
2463 IF x_doc_level_tbl(i) = 'HEADER' THEN
2464 l_header_uda_tmpl_id := l_template_id;
2465 ELSIF x_doc_level_tbl(i) = 'LINE' THEN
2466 l_line_uda_tmpl_id := l_template_id;
2467 ELSIF x_doc_level_tbl(i) = 'SHIPMENT' THEN
2468 l_ship_uda_tmpl_id := l_template_id;
2469 END IF;
2470 END IF;
2471 END LOOP;
2472
2473
2474 FOR i IN 1..x_doc_level_tbl.Count
2475 LOOP
2476 IF x_doc_level_tbl(i) = 'HEADER'
2477 THEN
2478 IF po_autocreate_params.g_mode='NEW'
2479 OR (po_autocreate_params.g_mode='ADD'
2480 AND NOT po_autocreate_params.g_is_mod_exists)
2481 THEN
2482
2483 l_progress := '080';
2484
2485 /* bug 10202181 : After autocreating the PO ,
2486 the UDA_TEMPLATE_DATE was getting stamped with
2487 null value, now stamping it with sysdate*/
2488 UPDATE po_headers_draft_all
2489 SET uda_template_id = l_header_uda_tmpl_id
2490 , uda_template_date = sysdate -- bug 10202181
2491 WHERE po_header_id = po_autocreate_params.g_po_header_id
2492 AND draft_id = po_autocreate_params.g_draft_id;
2493
2494 END IF;
2495 ELSIF x_doc_level_tbl(i) = 'LINE' THEN
2496 FOR l_line_rec IN c_lines
2497 LOOP
2498 l_progress := '100';
2499 UPDATE po_lines_draft_all
2500 SET uda_template_id = l_line_uda_tmpl_id
2501 WHERE po_line_id = l_line_rec.po_line_id
2502 AND draft_id = po_autocreate_params.g_draft_id;
2503 END LOOP;
2504
2505 ELSIF x_doc_level_tbl(i) = 'SHIPMENT' THEN
2506 FOR l_ship_rec IN c_shipments
2507 LOOP
2508 l_progress := '120';
2509 UPDATE po_line_locations_draft_all
2510 SET uda_template_id = l_ship_uda_tmpl_id
2511 WHERE line_location_id = l_ship_rec.line_location_id
2512 AND draft_id = po_autocreate_params.g_draft_id;
2513 END LOOP;
2514 END IF;
2515 END LOOP;
2516
2517 IF g_debug_stmt
2518 THEN
2519 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
2520 END IF;
2521
2522 EXCEPTION
2523 WHEN OTHERS
2524 THEN
2525 IF g_debug_unexp
2526 THEN
2527 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
2528 p_progress => l_progress
2529 );
2530 END IF;
2531
2532 -- PO_AUTOCREATE_PVT.wrapup();
2533 po_message_s.sql_error ('UPDATE_UDA_TEMPLATE_ID', l_progress, SQLCODE);
2534 RAISE;
2535 END update_uda_template_id;
2536
2537 -- CLM Autocreate - convert funding to info SLIN start
2538
2539 /* ============================================================================
2540 NAME: create_info_slin
2541
2542 DESC: Creates a info SLIN line for a given priced CLIN line
2543
2544 NOTE: This is a private procedure and is called from convert_fund_to_info_slin()
2545
2546 ==============================================================================*/
2547 PROCEDURE create_info_slin( p_po_header_id IN NUMBER
2548 ,p_po_line_id IN NUMBER
2549 ,p_draft_id IN NUMBER
2550 ,x_slin_line_id IN OUT NOCOPY NUMBER)
2551
2552 IS
2553 l_api_name VARCHAR2 (30) := 'create_info_slin';
2554 l_progress VARCHAR2 (3) := '000';
2555 l_max_line_num NUMBER;
2556 l_slin_line_num NUMBER;
2557 l_clin_line_num_display VARCHAR2(100);
2558 l_slin_line_num_display VARCHAR2(100);
2559
2560 BEGIN
2561
2562 l_progress := '010';
2563 x_slin_line_id := po_lines_s.nextval;
2564
2565 IF g_debug_stmt THEN
2566 po_debug.debug_var(g_log_head || l_api_name,l_progress,'x_slin_line_id',x_slin_line_id);
2567 END IF;
2568 /*create a info SLIN line. The group_line_id will be the po_line_id of the parent clin line. clm_info_flag will be 'Y' and the item description = 'Funding for '||item_description of the parent line*/
2569
2570 l_progress := '020';
2571
2572 BEGIN
2573 select max(line_num)
2574 into l_max_line_num
2575 from po_lines_merge_v
2576 where po_header_id = p_po_header_id
2577 and draft_id = p_draft_id;
2578
2579 EXCEPTION
2580 when no_data_found THEN
2581 l_max_line_num := null;
2582 END;
2583
2584 l_slin_line_num := l_max_line_num +1;
2585
2586 IF g_debug_stmt THEN
2587 po_debug.debug_var(g_log_head || l_api_name,l_progress,'l_max_line_num',l_max_line_num);
2588 po_debug.debug_var(g_log_head || l_api_name,l_progress,'l_slin_line_num',l_slin_line_num);
2589 END IF;
2590
2591 l_progress := '020';
2592 BEGIN
2593 select line_num_display
2594 into l_clin_line_num_display
2595 from po_lines_merge_v
2596 where po_line_id = p_po_line_id
2597 and draft_id = p_draft_id;
2598
2599 EXCEPTION
2600 when no_data_found THEN
2601 l_clin_line_num_display := null;
2602 END;
2603
2604 l_slin_line_num_display := PO_LINES_DRAFT_PVT.get_next_slin_num(p_po_line_id, --po_line_id
2605 NULL,
2606 l_clin_line_num_display,--line_num_display
2607 'Y', --clmInfoFlag
2608 -9999);--Invalid Draft Id
2609
2610 IF g_debug_stmt THEN
2611 po_debug.debug_var(g_log_head || l_api_name,l_progress,'l_clin_line_num_display',l_clin_line_num_display);
2612 po_debug.debug_var(g_log_head || l_api_name,l_progress,'l_slin_line_num_display',l_slin_line_num_display);
2613 END IF;
2614
2615
2616 IF p_draft_id <> -1 THEN
2617 l_progress := '030';
2618 INSERT INTO po_lines_draft_all
2619 ( draft_id,
2620 po_line_id,
2621 last_update_date,
2622 last_updated_by,
2623 po_header_id,
2624 line_num,
2625 line_num_display,
2626 creation_date,
2627 created_by,
2628 last_update_login,
2629 item_description,
2630 org_id,
2631 group_line_id,
2632 clm_info_flag,
2633 -- Bug 16511873: New Line should have change_status as NEW
2634 change_status,
2635 uda_template_id --<Bug 16513396>
2636 )
2637 SELECT
2638 p_draft_id
2639 ,x_slin_line_id
2640 ,last_update_date
2641 ,last_updated_by
2642 ,po_header_id
2643 ,l_slin_line_num
2644 ,l_slin_line_num_display
2645 ,creation_date
2646 ,created_by
2647 ,last_update_login
2648 ,'Funding for Line'||line_num_display
2649 ,org_id
2650 ,po_line_id
2651 ,'Y'
2652 ,'NEW' --Bug 16511873
2653 ,uda_template_id --<Bug 16513396>
2654 FROM po_lines_draft_all
2655 WHERE po_line_id = p_po_line_id
2656 AND draft_id = p_draft_id;
2657 ELSE
2658 l_progress := '040';
2659 INSERT INTO po_lines_all
2660 (
2661 po_line_id,
2662 last_update_date,
2663 last_updated_by,
2664 po_header_id,
2665 line_num,
2666 line_num_display,
2667 creation_date,
2668 created_by,
2669 last_update_login,
2670 item_description,
2671 org_id,
2672 group_line_id,
2673 clm_info_flag,
2674 uda_template_id --<Bug 16513396>
2675 )
2676 SELECT
2677
2678 x_slin_line_id
2679 ,last_update_date
2680 ,last_updated_by
2681 ,po_header_id
2682 ,l_slin_line_num
2683 ,l_slin_line_num_display
2684 ,creation_date
2685 ,created_by
2686 ,last_update_login
2687 ,'Funding for Line'||line_num_display
2688 ,org_id
2689 ,po_line_id
2690 ,'Y'
2691 ,uda_template_id --<Bug 16513396>
2692 FROM po_lines_all
2693 WHERE po_line_id = p_po_line_id;
2694 END IF;
2695
2696
2697 EXCEPTION
2698 WHEN others THEN
2699 IF g_debug_unexp THEN
2700 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
2701 p_progress => l_progress
2702 );
2703 END IF;
2704 po_message_s.sql_error ('CREATE_INFO_SLIN', l_progress, SQLCODE);
2705 RAISE;
2706
2707 END create_info_slin;
2708
2709
2710 /* ============================================================================
2711 NAME: convert_fund_to_info_slins
2712
2713 DESC: Converts the funding line in the priced CLIN to that of the informational SLINs
2714 when 'Convert Funding to Informational SLINs' action is selected
2715
2716 ==============================================================================*/
2717
2718 PROCEDURE convert_fund_to_info_slin( p_po_header_id_tbl IN PO_TBL_NUMBER,
2719 p_po_line_id_tbl IN PO_TBL_NUMBER,
2720 p_draft_id_tbl IN PO_TBL_NUMBER
2721 )
2722
2723
2724 IS
2725 l_api_name VARCHAR2 (30) := 'convert_fund_to_info_slins';
2726 l_progress VARCHAR2 (3) := '000';
2727 l_record_already_exist VARCHAR2(1);
2728 l_num_lines NUMBER;
2729 l_distribution_id_tbl PO_TBL_NUMBER;
2730 x_slin_line_id NUMBER;
2731 l_is_convert_valid VARCHAR2(1) := 'N';
2732
2733 BEGIN
2734
2735 l_progress := '010';
2736 IF g_debug_stmt THEN
2737 po_debug.debug_var(g_log_head || l_api_name,l_progress,'p_po_header_id',p_po_header_id_tbl);
2738 po_debug.debug_var(g_log_head || l_api_name,l_progress,'p_po_line_id',p_po_line_id_tbl);
2739 po_debug.debug_var(g_log_head || l_api_name,l_progress,'p_draft_id',p_draft_id_tbl);
2740 END IF;
2741
2742 l_progress := '020';
2743
2744
2745 FOR i IN 1..p_po_header_id_tbl.COUNT LOOP
2746
2747 BEGIN
2748
2749 SELECT 'Y'
2750 INTO l_is_convert_valid
2751 FROM po_lines_merge_v polm
2752 WHERE polm.po_line_id = p_po_line_id_tbl(i)
2753 AND polm.draft_id = p_draft_id_tbl(i)
2754 AND polm.group_line_id IS NULL
2755 AND NVL(polm.clm_info_flag,'N') <> 'Y'
2756 AND EXISTS ( SELECT 1 FROM po_distributions_merge_v podm
2757 WHERE podm.po_line_id = polm.po_line_id
2758 AND podm.draft_id = polm.draft_id
2759 AND podm.group_line_id IS NULL);
2760
2761
2762 EXCEPTION
2763 WHEN no_data_found THEN
2764 l_is_convert_valid := 'N';
2765 END;
2766
2767 IF g_debug_stmt THEN
2768 po_debug.debug_var(g_log_head || l_api_name,l_progress,'l_is_convert_valid',l_is_convert_valid);
2769 END IF;
2770
2771 l_progress := '050';
2772
2773 IF l_is_convert_valid = 'Y' THEN
2774
2775 --create info slins
2776 l_progress := '070';
2777 create_info_slin(p_po_header_id_tbl(i)
2778 ,p_po_line_id_tbl(i)
2779 , p_draft_id_tbl(i)
2780 , x_slin_line_id);
2781
2782 IF g_debug_stmt THEN
2783 po_debug.debug_var(g_log_head || l_api_name,l_progress,'x_slin_line_id',x_slin_line_id);
2784 END IF;
2785
2786 --move distributions
2787 IF p_draft_id_tbl(i) <> -1 THEN
2788 l_progress := '080';
2789 update po_distributions_draft_all
2790 set group_line_id = x_slin_line_id
2791 where po_line_id = p_po_line_id_tbl(i)
2792 AND draft_id = p_draft_id_tbl(i)
2793 AND group_line_id IS NULL;
2794 ELSE
2795 update po_distributions_all
2796 set group_line_id = x_slin_line_id
2797 where po_line_id = p_po_line_id_tbl(i)
2798 AND group_line_id IS NULL;
2799 END IF; --IF p_draft_id <> -1
2800
2801 END IF;-- IF l_is_convert_valid = 'Y'
2802 END LOOP;
2803
2804 EXCEPTION
2805 WHEN others THEN
2806 IF g_debug_unexp THEN
2807 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
2808 p_progress => l_progress
2809 );
2810 END IF;
2811 po_message_s.sql_error ('CONVERT_FUND_TO_INFO_SLIN', l_progress, SQLCODE);
2812 RAISE;
2813
2814 END convert_fund_to_info_slin;
2815
2816 -- CLM Autocreate - convert funding to info SLIN end
2817
2818
2819 --CLM Phase4 Changes
2820
2821 -- This procedure is used to copy exhibits from PON_AUCTION_EXHIBIT_DETAILS to PO_EXHIBIT_DETAILS
2822 -- and delete the deliverables that were linked to lins in Sol but not awarded.
2823
2824 PROCEDURE copy_exhibit_from_sol( p_contract_doc_type IN VARCHAR2, p_org_id IN NUMBER)
2825 IS
2826 l_api_name VARCHAR2 (30) := 'copy_exhibit_from_sol';
2827 l_progress VARCHAR2 (3) := '000';
2828
2829 l_exhibit_tbl okc_cdrl_pvt.exhibit_tbl_type;
2830 l_msg_data VARCHAR2(240);
2831 l_msg_count NUMBER;
2832 l_return_status VARCHAR2(30);
2833
2834 l_cdrls_enabled VARCHAR2(1);
2835 l_elins_enabled VARCHAR2(1);
2836
2837 BEGIN
2838
2839 l_progress := '010';
2840
2841 IF g_debug_stmt
2842 THEN
2843 po_debug.debug_begin (p_log_head => g_log_head || l_api_name);
2844 END IF;
2845
2846 l_cdrls_enabled := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
2847 p_option_column => PO_CORE_S.g_CONT_DATA_REQS_COL);
2848 l_elins_enabled := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
2849 p_option_column => PO_CORE_S.g_EXHIBITS_LINES_COL);
2850
2851 IF(l_cdrls_enabled = 'Y' OR l_elins_enabled = 'Y') THEN
2852 --copy data from PON_AUCTION_EXHIBIT_DETAILS to PO_EXHIBIT_DETAILS
2853 INSERT INTO po_exhibit_details
2854 (
2855 PO_EXHIBIT_DETAILS_ID
2856 ,EXHIBIT_NAME
2857 ,EXHIBIT_DESCRIPTION
2858 ,IS_CDRL
2859 ,PO_HEADER_ID
2860 ,REFERENCE_LINE_ID
2861 ,LAST_UPDATE_DATE
2862 ,LAST_UPDATED_BY
2863 ,CREATION_DATE
2864 ,CREATED_BY
2865 ,LAST_UPDATE_LOGIN
2866 )
2867 SELECT po_exhibit_details_s.NEXTVAL --PO_EXHIBIT_DETAILS_ID
2868 ,EXHIBIT_NUMBER --EXHIBIT_NAME
2869 ,EXHIBIT_DESCRIPTION --EXHIBIT_DESCRIPTION
2870 ,IS_CDRL --IS_CDRL
2871 ,po_autocreate_params.g_po_header_id --PO_HEADER_ID
2872 ,(SELECT po_line_id FROM po_lines_merge_v
2873 WHERE po_header_id = po_autocreate_params.g_po_header_id
2874 AND AUCTION_LINE_NUMBER = paed.associated_to_line
2875 AND draft_id = Nvl(po_autocreate_params.g_draft_id, -1)
2876 )
2877 ,SYSDATE --LAST_UPDATE_DATE
2878 ,FND_GLOBAL.user_id --LAST_UPDATED_BY
2879 ,SYSDATE --CREATION_DATE
2880 ,FND_GLOBAL.user_id --CREATED_BY
2881 ,FND_GLOBAL.login_id --LAST_UPDATE_LOGIN
2882 FROM PON_AUCTION_EXHIBIT_DETAILS paed
2883 WHERE (paed.auction_header_id, paed.associated_to_line)
2884 IN (
2885 SELECT auction_header_id,auction_line_number FROM po_lines_merge_v pol
2886 WHERE pol.po_header_id = po_autocreate_params.g_po_header_id
2887 AND pol.draft_id = Nvl(po_autocreate_params.g_draft_id, -1)
2888 )
2889 ;
2890
2891 l_progress := '020';
2892
2893 IF g_debug_stmt THEN
2894 po_debug.debug_stmt
2895 (p_log_head => g_log_head || l_api_name,
2896 p_token => l_progress,
2897 p_message => 'No. of records copied : ' || SQL%ROWCOUNT
2898 );
2899 END IF;
2900
2901
2902 -- Delete the deliverables whose referenced lines are not awarded.
2903 SELECT exhibit_name
2904 BULK COLLECT INTO l_exhibit_tbl
2905 FROM po_exhibit_details
2906 WHERE po_header_id = po_autocreate_params.g_po_header_id
2907 AND is_cdrl = 'Y';
2908
2909 IF(l_exhibit_tbl IS NOT NULL) THEN
2910
2911 okc_cdrl_pvt.retain_cdrls_for_exhibits (
2912 p_api_version => 1.0,
2913 p_init_msg_list => FND_API.G_TRUE,
2914 p_commit => FND_API.G_FALSE,
2915 p_doc_type => p_contract_doc_type,
2916 p_doc_id => po_autocreate_params.g_po_header_id,
2917 p_doc_version => NULL,
2918 p_mode => NULL,
2919 p_exhibit_tbl => l_exhibit_tbl,
2920 x_msg_data => l_msg_data,
2921 x_msg_count => l_msg_count,
2922 x_return_status => l_return_status
2923 );
2924
2925 END IF;
2926
2927 l_progress := '020';
2928
2929 IF g_debug_stmt THEN
2930 po_debug.debug_stmt
2931 (p_log_head => g_log_head || l_api_name,
2932 p_token => l_progress,
2933 p_message => 'l_return_status : ' || l_return_status ||' l_msg_data:'||l_msg_data
2934 );
2935 END IF;
2936
2937
2938 END IF;
2939 l_progress := '030';
2940
2941 IF g_debug_stmt
2942 THEN
2943 po_debug.debug_end (p_log_head => g_log_head || l_api_name);
2944 END IF;
2945
2946 EXCEPTION
2947 WHEN others THEN
2948 IF g_debug_unexp THEN
2949 po_debug.debug_exc (p_log_head => g_log_head || l_api_name,
2950 p_progress => l_progress
2951 );
2952 END IF;
2953 po_message_s.sql_error ('COPY_EXHIBIT_FROM_SOL', l_progress, SQLCODE);
2954 RAISE;
2955
2956 END copy_exhibit_from_sol;
2957
2958 END po_autocreate_postproc_pvt;