[Home] [Help]
PACKAGE BODY: APPS.PO_MULTI_MOD_PVT
Source
1 PACKAGE BODY po_multi_mod_pvt AS
2 /* $Header: PO_MULTI_MOD_PVT.plb 120.11.12020000.2 2013/02/10 13:00:41 vegajula ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_MULTI_MOD_PVT');
6
7 PROCEDURE DEBUG
8 (
9 p_module IN VARCHAR2,
10 p_progress IN NUMBER,
11 p_msg IN VARCHAR2,
12 p_cp_log IN VARCHAR2 DEFAULT 'N'
13 )
14 IS
15 BEGIN
16 -- Write the message to the FND log, if enabled.
17 IF PO_LOG.d_stmt THEN
18 PO_LOG.stmt(p_module, p_progress, p_msg);
19 END IF;
20 -- Write the message to the concurrent program log file.
21 IF (p_cp_log = FND_API.G_TRUE) THEN
22 FND_FILE.put_line (FND_FILE.LOG, substrb(p_module || p_progress || ': ' || p_msg, 1, 2000));
23 END IF;
24
25 END DEBUG;
26
27 -------------------------------------------------------------------------------
28 --Start of Comments
29 --Name: remove_mmod_doc_data
30 --Pre-reqs:
31 -- None.
32 --Modifies:
33 -- None.
34 --Locks:
35 -- None.
36 --Function:
37 -- If a Document has been removed from the selected documents list of a multi-mod
38 -- request, then all the associated vendor changes for the doc will be removed.
39 -- Also, all the related Validation Exceptions to that Document will be removed
40 --Parameters:
41 --IN:
42 --p_multi_mod_request_id
43 -- The request id of the Multi-Mod Request
44 --Testing:
45 -- None.
46 --End of Comments
47 -------------------------------------------------------------------------------
48
49 PROCEDURE remove_mmod_doc_data(p_multi_mod_request_id IN NUMBER)
50 IS
51
52 d_api_name CONSTANT VARCHAR2(30) := 'REMOVE_MMOD_DOC_CHANGES';
53 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
54 d_position NUMBER;
55
56 BEGIN
57
58 d_position := 0;
59 IF (PO_LOG.d_proc) THEN
60 PO_LOG.proc_begin(d_module,'p_multi_mod_request_id',p_multi_mod_request_id);
61 END IF;
62
63 d_position := 10;
64 debug(d_module, d_position, 'Deleting redundant entries from po_multi_mod_changes');
65
66 DELETE
67 FROM po_multi_mod_changes
68 WHERE multi_mod_request_id = p_multi_mod_request_id
69 AND ((change_type = 'VENDOR_SITE_CONTACT'
70 AND (org_id, old_vendor_site_id) NOT IN
71 (SELECT DISTINCT org_id, vendor_site_id
72 FROM po_headers_all
73 WHERE po_header_id IN
74 (SELECT document_id
75 FROM po_multi_mod_docs
76 WHERE multi_mod_request_id = p_multi_mod_request_id)
77 UNION
78 SELECT DISTINCT purchasing_org_id org_id, vendor_site_id
79 FROM po_ga_org_assignments
80 WHERE po_header_id IN
81 (SELECT document_id
82 FROM po_multi_mod_docs
83 WHERE multi_mod_request_id = p_multi_mod_request_id)))
84 OR (change_type = 'VENDOR_REMIT_ADDRESS'
85 AND (org_id,old_remit_to_addr) NOT IN
86 (SELECT DISTINCT rmt_ofc.n_ext_attr3 org_id, rmt_ofc.n_ext_attr2 remit_to_office
87 FROM po_headers_all_ext_b rmt_ofc,
88 ego_attr_groups_v v_ag
89 WHERE v_ag.attr_group_type = 'PO_HEADER_EXT_ATTRS'
90 AND v_ag.attr_group_name = 'SUPPLIER_DTLS'
91 AND v_ag.attr_group_id = rmt_ofc.attr_group_id
92 AND rmt_ofc.draft_id = -1
93 AND rmt_ofc.po_header_id IN
94 (SELECT document_id
95 FROM po_multi_mod_docs
96 WHERE multi_mod_request_id = p_multi_mod_request_id)
97 AND rmt_ofc.n_ext_attr2 IS NOT NULL
98 AND rmt_ofc.n_ext_attr3 IS NOT NULL)));
99
100 d_position := 20;
101 debug(d_module, d_position, 'Done deleting Rows. Count: '|| SQL%ROWCOUNT);
102
103 d_position := 30;
104 debug(d_module, d_position, 'Deleting redundant entries from po_multi_val_results');
105
106 DELETE
107 FROM po_multi_mod_val_results pmmvr
108 WHERE multi_mod_request_id = p_multi_mod_request_id
109 AND multi_mod_doc_id IS NOT NULL -- Remove only document-level exceptions
110 AND NOT EXISTS(
111 SELECT 1
112 FROM po_multi_mod_docs
113 WHERE multi_mod_doc_id = pmmvr.multi_mod_doc_id);
114
115
116 d_position := 40;
117 debug(d_module, d_position, 'Done deleting Rows. Count: '|| SQL%ROWCOUNT);
118
119
120 d_position := 50;
121 IF (PO_LOG.d_proc) THEN
122 PO_LOG.proc_end(d_module);
123 END IF;
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 PO_MESSAGE_S.add_exc_msg(
128 p_pkg_name => d_pkg_name,
129 p_procedure_name => d_api_name || '.' || d_position,
130 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
131 );
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133
134 END remove_mmod_doc_data;
135
136 -------------------------------------------------------------------------------
137 --Start of Comments
138 --Name: remove_multi_mod_request
139 --Pre-reqs:
140 -- None.
141 --Modifies:
142 -- None.
143 --Locks:
144 -- None.
145 --Function:
146 -- Removes the multi_mod_request
147 -- Deletes entries from po_multi_mod_val_results, po_multi_mod_docs,
148 -- po_multi_mod_changes and po_multi_mod_requests
149 --Parameters:
150 --IN:
151 --p_multi_mod_request_id
152 -- The request id of the Multi-Mod Request
153 --Testing:
154 -- None.
155 --End of Comments
156 -------------------------------------------------------------------------------
157 PROCEDURE remove_multi_mod_request (p_multi_mod_request_id IN NUMBER,
158 x_return_status OUT NOCOPY VARCHAR2)
159 IS
160 d_api_name CONSTANT VARCHAR2(30) := 'REMOVE_MULTI_MOD_REQUEST';
161 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
162 d_position NUMBER;
163 BEGIN
164 d_position := 0;
165 IF (PO_LOG.d_proc) THEN
166 PO_LOG.proc_begin(d_module,'p_multi_mod_request_id ',p_multi_mod_request_id);
167 END IF;
168
169 -- remove all exceptionms occurred in multi mod request
170 DELETE FROM po_multi_mod_val_results
171 WHERE multi_mod_request_id = p_multi_mod_request_id;
172
173 -- remove all records selected for the multi mod request
174 DELETE FROM po_multi_mod_docs
175 WHERE multi_mod_request_id = p_multi_mod_request_id;
176
177 -- remove changes made in multi mod request
178 DELETE FROM po_multi_mod_changes
179 WHERE multi_mod_request_id = p_multi_mod_request_id;
180
181 --remove from po_multi_mod_requests table
182 DELETE FROM po_multi_mod_requests
183 WHERE multi_mod_request_id = p_multi_mod_request_id;
184
185 --remove Contracts data
186 DELETE FROM po_multi_mod_clause_changes
187 WHERE multi_mod_request_id = p_multi_mod_request_id;
188
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190 IF (PO_LOG.d_proc) THEN
191 PO_LOG.proc_end(d_module);
192 END IF;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 PO_MESSAGE_S.add_exc_msg(
198 p_pkg_name => d_pkg_name,
199 p_procedure_name => d_api_name || '.' || d_position,
200 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
201 );
202
203 END remove_multi_mod_request;
204
205 --Function returns whether UDA is enabled on the corresponding document
206 FUNCTION check_uda_enabled(p_po_header_id NUMBER
207 ) RETURN VARCHAR2 IS
208
209 d_api_name CONSTANT VARCHAR2(30) := 'CHECK_UDA_ENABLED';
210 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
211 d_position NUMBER;
212
213 l_functional_area_code VARCHAR2(500);
214 l_document_type_code VARCHAR2(500);
215 l_document_style_id NUMBER;
216 l_enabled_flag VARCHAR2(5);
217 l_errorcode VARCHAR2(50);
218 l_msg_count NUMBER;
219 l_msg_data VARCHAR2(4000);
220 l_return_status VARCHAR2(1);
221
222 BEGIN
223
224 SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID
225 INTO l_functional_area_code, l_document_type_code, l_document_style_id
226 FROM po_headers_all
227 WHERE PO_HEADER_ID = p_po_header_id;
228
229 d_position := 0;
230 debug(d_module, d_position, 'Before Check_Uda_Enabled');
231
232 PO_CLM_CLO_UTIL.Check_Uda_Enabled(
233 p_functional_area_code => l_functional_area_code,
234 p_document_type_code => l_document_type_code,
235 p_document_style_id => l_document_style_id,
236 x_enabled_flag => l_enabled_flag,
237 x_return_status => l_return_status,
238 x_errorcode => l_errorcode,
239 x_msg_count => l_msg_count,
240 x_msg_data => l_msg_data
241 );
242
243 d_position := 10;
244 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
245 THEN
246 debug(d_module, d_position, 'An exception was encountered in PO_CLM_CLO_UTIL.Check_Uda_Enabled: '||l_msg_data, FND_API.G_TRUE);
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 END IF;
249
250 d_position := 20;
251 debug(d_module, d_position, 'Check_Uda_Enabled with x_enabled_flag : ' || l_enabled_flag);
252
253 RETURN l_enabled_flag;
254
255 END;
256
257 -------------------------------------------------------------------------------
258 --Start of Comments
259 --Name: create_draft_from_txn
260 --Pre-reqs:
261 -- None.
262 --Modifies:
263 -- None.
264 --Locks:
265 -- None.
266 --Function:
267 -- 1. Create a record in PO_DRAFTS with a newly generated DRAFT_ID
268 -- 2. Call PO_HEADERS_DRAFT_PKG.sync_draft_from_txn to create a record in Po Draft Header Table.
269 --Parameters:
270 --IN:
271 --p_po_header_id
272 -- The id of the Order/Agreement for which the Mod needs to be created
273 --OUT:
274 --p_draft_id
275 -- The draft Id of the newly created Modification
276 --x_return_status
277 -- FND_API.G_RET_STS_SUCCESS if API succeeds
278 -- FND_API.G_RET_STS_ERROR if API fails
279 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
280 --End of Comments
281 -------------------------------------------------------------------------------
282 PROCEDURE create_draft_from_txn(p_po_header_id IN NUMBER,
283 x_draft_id OUT NOCOPY NUMBER,
284 x_return_status OUT NOCOPY VARCHAR2)
285 IS
286
287 d_api_name CONSTANT VARCHAR2(30) := 'CREATE_DRAFT_FROM_TXN';
288 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
289 d_position NUMBER;
290
291 l_draft_id NUMBER;
292 l_record_already_exists VARCHAR2(1);
293 BEGIN
294
295 d_position := 0;
296 IF (PO_LOG.d_proc) THEN
297 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
298 END IF;
299
300 d_position := 10;
301 debug(d_module, d_position, 'Create row in PO_DRAFTS for the new Mod', FND_API.G_TRUE);
302
303 -- Fetch the new DraftId from sequence
304 l_draft_id:= PO_DRAFTS_PVT.draft_id_nextval;
305
306 INSERT INTO po_drafts(
307 draft_id,
308 document_id,
309 owner_user_id,
310 owner_role,
311 status,
312 draft_type,
313 last_update_date,
314 last_updated_by,
315 last_update_login,
316 creation_date,
317 created_by)
318 VALUES(
319 l_draft_id,
320 p_po_header_id,
321 FND_GLOBAL.user_id,
322 'BUYER',
323 'DRAFT',
324 'MOD',
325 SYSDATE,
326 FND_GLOBAL.user_id,
327 FND_GLOBAL.login_id,
328 SYSDATE,
329 FND_GLOBAL.user_id);
330
331 d_position := 20;
332 debug(d_module, d_position, 'Row created into PO_DRAFTS');
333
334 d_position := 30;
335 debug(d_module, d_position, 'Create row into PO_HEADERS_DRAFT_ALL for the new Mod', FND_API.G_TRUE);
336
337 PO_HEADERS_DRAFT_PKG.sync_draft_from_txn(
338 p_po_header_id => p_po_header_id,
339 p_draft_id => l_draft_id,
340 p_delete_flag => 'N',
341 x_record_already_exist => l_record_already_exists
342 );
343
344 x_draft_id := l_draft_id;
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346
347 d_position := 40;
348 IF (PO_LOG.d_proc) THEN
349 PO_LOG.proc_end(d_module,'x_draft_id',x_draft_id);
350 PO_LOG.proc_end(d_module);
351 END IF;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 PO_MESSAGE_S.add_exc_msg(
356 p_pkg_name => d_pkg_name,
357 p_procedure_name => d_api_name || '.' || d_position,
358 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
359 );
360 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362
363 END create_draft_from_txn;
364
365 -------------------------------------------------------------------------------
366 --Start of Comments
367 --Name: default_and_merge_mod_data
368 --Pre-reqs:
369 -- None.
370 --Modifies:
371 -- None.
372 --Locks:
373 -- None.
374 --Function:
375 -- 1. Copy UDA Data from Base Document to the new Mod
376 -- 2. Copy Multi-Mod UDA Data from Base document to the new Mod
377 -- 3. Default Document Number for the newly created Mod
378 -- 4. Default Standard Doc/Standard Form fields
379 -- 5. Default AME Data
380 -- 6. Update PO_DRAFTS/ PO_DRAFT_HEADERS_ALL with the Multi-Mod data and other defaults
381 -- 7. Copy Contract terms to the new Mod
382 --Parameters:
383 --IN:
384 --p_multi_mod_request_record
385 -- The po_multi_mod_request record against the request id of the Multi-Mod Request
386 --p_po_header_id
387 -- The header id of the Order/Agreement for which the Mod needs to be created
388 --p_draft_id
389 -- The draft id of the new Mod
390 --p_style_id
391 -- StyleId of the PO
392 --p_doc_subtype
393 -- Document SubType of the PO
394 --p_org_id
395 -- Org Id of the PO
396 --p_template_id
397 -- UDA Template Id
398 --p_is_uda_enabled
399 -- UDA Enabled Flag
400 --OUT:
401 --x_mod_number
402 -- The Modification Number of the newly created Mod
403 --x_return_status
404 -- FND_API.G_RET_STS_SUCCESS if API succeeds
405 -- FND_API.G_RET_STS_ERROR if API fails
406 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
407 --End of Comments
408 -------------------------------------------------------------------------------
409 PROCEDURE default_and_merge_mod_data(p_multi_mod_request_record IN po_multi_mod_requests%ROWTYPE,
410 p_po_header_id IN NUMBER,
411 p_draft_id IN NUMBER,
412 p_style_id IN NUMBER,
413 p_doc_subtype IN VARCHAR,
414 p_org_id IN NUMBER,
415 p_template_id IN NUMBER,
416 p_is_uda_enabled IN VARCHAR2,
417 x_mod_number OUT NOCOPY VARCHAR2,
418 x_return_status OUT NOCOPY VARCHAR2)
419 IS
420
421 d_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_AND_MERGE_MOD_DATA';
422 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
423 d_position NUMBER;
424
425 l_msg_count NUMBER;
426 l_msg_data VARCHAR2(4000);
427 l_ags_table PO_TBL_VARCHAR30;
428 l_address_ag_name VARCHAR2(30) := 'addresses';
429 l_doc_num_ag_name VARCHAR2(30);
430 l_print_doc_type VARCHAR2(2000);
431
432 l_record_already_exists VARCHAR2(1);
433 l_clm_source_document_id NUMBER;
434 l_doc_number po_drafts.modification_number%TYPE;
435 l_standard_form po_headers_all.clm_standard_form%TYPE;
436 l_document_format po_headers_all.clm_document_format%TYPE;
437 l_ame_transaction_type po_headers_all.ame_transaction_type%TYPE;
438 l_ame_approval_id po_headers_all.ame_approval_id%TYPE;
439 l_clm_default_dist VARCHAR2(1);
440 BEGIN
441
442 d_position := 0;
443 IF (PO_LOG.d_proc) THEN
444 PO_LOG.proc_begin(d_module,'p_multi_mod_request_id',p_multi_mod_request_record.multi_mod_request_id);
445 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
446 PO_LOG.proc_begin(d_module,'p_draft_id',p_draft_id);
447 END IF;
448
449 d_position := 10;
450 debug(d_module, d_position, 'Fetch base document values');
451
452 -- Fetch the CLM Source Document Number for Order off IDV
453 BEGIN
454 SELECT phda.clm_source_document_id
455 INTO l_clm_source_document_id
456 FROM po_headers_draft_all phda,
457 po_headers_all pha
458 WHERE phda.po_header_id = p_po_header_id
459 AND phda.draft_id = p_draft_id
460 AND pha.po_header_id = phda.clm_source_document_id
461 AND pha.clm_award_type IS NOT NULL;
462
463 EXCEPTION
464 WHEN No_Data_Found THEN
465 l_clm_source_document_id := NULL;
466 WHEN OTHERS THEN
467 l_clm_source_document_id := NULL;
468 END;
469
470 -- Do UDA related handling if doc is UDA enabled
471 IF p_is_uda_enabled = 'Y' THEN
472
473 d_position := 20;
474 debug(d_module, d_position, 'Copy Header UDA Attributes from Base document to Mod', FND_API.G_TRUE);
475
476 --We should not copy the document numbering usages, so adding the
477 --document numbering usage of the Base Document in the exclusion list
478
479 BEGIN
480 SELECT distinct ags.ATTR_GROUP_NAME
481 INTO l_doc_num_ag_name
482 FROM po_headers_all_ext_b phaeb,
483 po_uda_ag_template_usages usages,
484 ego_attr_groups_v ags
485 WHERE phaeb.po_header_id = p_po_header_id
486 AND phaeb.draft_id = -1
487 AND phaeb.attr_group_id = ags.attr_group_id
488 AND phaeb.attr_group_id = usages.attribute_group_id
489 AND usages.attribute_category in ('DOCUMENT_NUMBERING', 'CLOSEOUT')
490 AND usages.template_id = p_template_id;
491 EXCEPTION
492 WHEN No_Data_Found THEN
493 l_doc_num_ag_name := NULL;
494 END;
495
496 l_ags_table := PO_TBL_VARCHAR30(l_doc_num_ag_name);
497
498 PO_UDA_DATA_UTIL.Copy_User_Attrs(
499 x_template_id => p_template_id,
500 x_from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY(
501 EGO_COL_NAME_VALUE_PAIR_OBJ( 'PO_HEADER_ID' , p_po_header_id),
502 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
503 ),
504 x_to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY(
505 EGO_COL_NAME_VALUE_PAIR_OBJ( 'PO_HEADER_ID' , p_po_header_id),
506 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , p_draft_id)
507 ),
508 x_copy_attribute_groups => 'EXCLUSIVE',
509 x_attribute_group_table => l_ags_table,
510 x_return_status => x_return_status,
511 x_msg_count => l_msg_count,
512 x_msg_data => l_msg_data
513 );
514
515 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
516 debug(d_module, d_position, 'An exception was encountered in PO_UDA_DATA_UTIL.Copy_User_Attrs: '||l_msg_data, FND_API.G_TRUE);
517 RAISE FND_API.g_exc_unexpected_error;
518 END IF;
519
520 d_position := 30;
521 debug(d_module, d_position, 'Copy Multi-Mod Specific UDA Attributes to Mod', FND_API.G_TRUE);
522
523 --We should not copy the Multi-Mod Address usages. They will be merged later
524 l_ags_table := PO_TBL_VARCHAR30(l_address_ag_name);
525
526 PO_UDA_DATA_UTIL.Copy_User_Attrs(
527 x_template_id => p_template_id,
528 x_from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY(
529 EGO_COL_NAME_VALUE_PAIR_OBJ( 'PO_HEADER_ID' , p_multi_mod_request_record.uda_key_po_header_id),
530 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , p_multi_mod_request_record.uda_key_draft_id)
531 ),
532 x_to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY(
533 EGO_COL_NAME_VALUE_PAIR_OBJ( 'PO_HEADER_ID' , p_po_header_id),
534 EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , p_draft_id)
535 ),
536 x_copy_attribute_groups => 'EXCLUSIVE',
537 x_attribute_group_table => l_ags_table,
538 x_return_status => x_return_status,
539 x_msg_count => l_msg_count,
540 x_msg_data => l_msg_data
541 );
542
543
544 /*
545 Including this code to change address type stored in UDA tables in column c_ext_attr40 from
546 PO_MOD_UDA_ADDRESS_TYPES to PO_UDA_ADDRESS_TYPES. This is done because on multi-mod page
547 address type is defaulted to PO_MOD_UDA_ADDRESS_TYPES but while copying on actual modification it
548 should be PO_UDA_ADDRESS_TYPES. When modification is created from BWC, award related address fields
549 have value PO_UDA_ADDRESS_TYPES.
550 */
551
552 UPDATE po_headers_all_ext_b
553 SET c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
554 WHERE po_header_id = p_po_header_id
555 AND draft_id = p_draft_id
556 AND c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE');
557
558 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
559 debug(d_module, d_position, 'An exception was encountered in PO_UDA_DATA_UTIL.Copy_User_Attrs: '||l_msg_data, FND_API.G_TRUE);
560 RAISE FND_API.g_exc_unexpected_error;
561 END IF;
562
563 d_position := 40;
564 debug(d_module, d_position, 'Default Document Number for the Mod', FND_API.G_TRUE);
565
566 PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA(
567 p_doc_header_id => p_po_header_id,
568 p_draft_id => p_draft_id,
569 p_template_id => p_template_id,
570 p_context_usage => 'Modification',
571 p_source_org_owned => PO_DOC_NUMBERING_PKG.get_owned_by_issuing_org(l_clm_source_document_id),
572 p_caller => 'PO_MOD',
573 x_doc_number => l_doc_number,
574 x_return_status => x_return_status
575 );
576
577 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
578 debug(d_module, d_position, 'An exception was encountered in PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA', FND_API.G_TRUE);
579 RAISE FND_API.g_exc_unexpected_error;
580 END IF;
581
582 END IF; --IF check_uda_enabled(p_po_header_id)
583
584 d_position := 50;
585 debug(d_module, d_position, 'Fetch defaults for CLM Standard Form and CLM Document Format');
586
587 l_print_doc_type := 'PO_MOD_STD_FORM';
588
589 -- Getting Standard Form and Document Format of the output document
590 BEGIN
591 --Default the standard_form and document_format
592 SELECT standard_form,
593 document_format
594 INTO l_standard_form,
595 l_document_format
596 FROM po_print_form_formats
597 WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
598 AND default_flag = 'Y'
599 AND style_id = p_style_id
600 AND document_type = l_print_doc_type;
601
602 EXCEPTION
603 WHEN No_Data_Found THEN
604 BEGIN
605 -- If default not available, use the first as default
606 SELECT standard_form,
607 document_format
608 INTO l_standard_form,
609 l_document_format
610 FROM po_print_form_formats
611 WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
612 AND style_id = p_style_id
613 AND document_type = l_print_doc_type
614 AND rownum =1;
615
616 EXCEPTION
617 WHEN No_Data_Found THEN
618 l_standard_form := NULL;
619 l_document_format := NULL;
620 WHEN OTHERS THEN
621 l_standard_form := NULL;
622 l_document_format := NULL;
623 END;
624 WHEN OTHERS THEN
625 l_standard_form := NULL;
626 l_document_format := NULL;
627 END;
628
629 d_position := 55;
630 debug(d_module, d_position, 'Fetch defaults for AME Attributes');
631
632 -- Generate new ame_approval_id from sequence
633 SELECT po_ame_approvals_s.nextval
634 INTO l_ame_approval_id
635 FROM dual;
636
637 SELECT mod_ame_transaction_type
638 INTO l_ame_transaction_type
639 FROM po_doc_style_headers
640 WHERE style_id = p_style_id;
641
642 d_position := 57;
643 -- <CLM-LnSc-START>
644 -- Fetch the value for CLM_DEFAULT_DIST_FLAG
645 l_clm_default_dist := PO_CORE_S.get_create_default_dist(
646 p_org_id => p_org_id,
647 p_style_id => p_style_id,
648 p_doc_sub_type => p_doc_subtype);
649 -- <CLM-LnSc-END>
650
651 d_position := 60;
652 debug(d_module, d_position, 'Update PO_DRAFTS with the Mod-specific defaults and Multi-mod fields', FND_API.G_TRUE);
653
654 UPDATE po_drafts
655 SET modification_number = l_doc_number,
656 clm_standard_form = l_standard_form,
657 clm_document_format = l_document_format,
658 mod_effective_date = p_multi_mod_request_record.mod_effective_date,
659 clm_noofcopies = p_multi_mod_request_record.clm_noofcopies,
660 clm_contract_officer = p_multi_mod_request_record.clm_contract_officer,
661 agent_id = p_multi_mod_request_record.agent_id --added as part of conc mod impacts
662 WHERE draft_id = p_draft_id;
663
664 d_position := 70;
665 debug(d_module, d_position, 'Update PO_DRAFT_HEADERS_ALL with the Mod-specific defaults and Multi-mod fields', FND_API.G_TRUE);
666
667 UPDATE po_headers_draft_all
668 SET ame_approval_id = l_ame_approval_id,
669 ame_transaction_type = l_ame_transaction_type,
670 acceptance_required_flag = p_multi_mod_request_record.acceptance_required_flag,
671 acceptance_due_date = p_multi_mod_request_record.acceptance_due_date,
672 clm_contract_officer = p_multi_mod_request_record.clm_contract_officer,
673 clm_default_dist_flag = l_clm_default_dist,
674 authorization_status = NULL,
675 document_creation_method = 'MULTI_MOD',
676 last_update_date = SYSDATE,
677 last_updated_by = FND_GLOBAL.user_id,
678 last_update_login = FND_GLOBAL.login_id
679 WHERE po_header_id = p_po_header_id
680 AND draft_id = p_draft_id;
681
682 d_position := 80;
683 debug(d_module, d_position, 'Copy Contract Terms to Mod', FND_API.G_TRUE);
684
685 PO_CONTERMS_UTL_GRP.Copy_Conterms_Mod(
686 p_document_id => p_po_header_id,
687 p_draft_id => p_draft_id,
688 p_to_mod => 'Y',
689 x_return_status => x_return_status
690 );
691
692 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
693 debug(d_module, d_position, 'An exception was encountered in PO_CONTERMS_UTL_GRP.Copy_Conterms_Mod', FND_API.G_TRUE);
694 RAISE FND_API.g_exc_unexpected_error;
695 END IF;
696
697 x_mod_number := l_doc_number;
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700 d_position := 90;
701 IF (PO_LOG.d_proc) THEN
702 PO_LOG.proc_end(d_module);
703 END IF;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 PO_MESSAGE_S.add_exc_msg(
708 p_pkg_name => d_pkg_name,
709 p_procedure_name => d_api_name || '.' || d_position,
710 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
711 );
712 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714
715 END default_and_merge_mod_data;
716
717 -------------------------------------------------------------------------------
718 --Start of Comments
719 --Name: merge_multi_mod_addresses
720 --Pre-reqs:
721 -- None.
722 --Modifies:
723 -- None.
724 --Locks:
725 -- None.
726 --Function:
727 -- Merge the Addresses from the Multi-Mod request onto the newly created Modification
728 -- Will follow the following rule while copying:
729 -- If the Multi-Mod Address Value is NULL, the base PO Address takes precedence.
730 --Parameters:
731 --IN:
732 --p_uda_key_po_header_id
733 -- The dummy uda_key_po_header_id from po_multi_mod_requests against which the UDA attributes are stored
734 --p_uda_key_draft_id
735 -- The dummy p_uda_key_draft_id from po_multi_mod_requests against which the UDA attributes are stored
736 --p_po_header_id
737 -- The header id of the new Modification
738 --p_draft_id
739 -- The draft id of the new Modification
740 --p_template_id
741 -- UDA Template Id
742 --p_is_uda_enabled
743 -- UDA Enabled Flag
744 --p_addr_change_flag
745 -- Flag will indicate if there are any address changes in the Multi-Mod request
746 --x_return_status
747 -- FND_API.G_RET_STS_SUCCESS if API succeeds
748 -- FND_API.G_RET_STS_ERROR if API fails
749 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
750 --End of Comments
751 -------------------------------------------------------------------------------
752 PROCEDURE merge_multi_mod_addresses(p_uda_key_po_header_id IN NUMBER,
753 p_uda_key_draft_id IN NUMBER,
754 p_po_header_id IN NUMBER,
755 p_draft_id IN NUMBER,
756 p_template_id IN NUMBER,
757 p_is_uda_enabled IN VARCHAR2,
758 p_addr_change_flag IN VARCHAR2,
759 x_return_status OUT NOCOPY VARCHAR2)
760 IS
761
762 d_api_name CONSTANT VARCHAR2(30) := 'MERGE_MULTI_MOD_ADDRESSES';
763 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
764 d_position NUMBER;
765
766 l_mod_uda_address_types CONSTANT VARCHAR2(30) := 'PO_MOD_UDA_ADDRESS_TYPES';
767 l_addr_attr_grp_type CONSTANT VARCHAR2(200) := 'PO_HEADER_EXT_ATTRS';
768 l_addr_attr_grp_name CONSTANT VARCHAR2(200) := 'addresses';
769 l_addr_attr_group_id NUMBER;
770
771 l_old_hdr_ext_id_tbl PO_TBL_NUMBER;
772 l_new_hdr_ext_id_tbl PO_TBL_NUMBER;
773
774 l_errorcode NUMBER;
775 l_msg_count NUMBER;
776 l_msg_data VARCHAR2(4000);
777
778 -- Cursor for fetching the extension id's against 'MOD_ISSUING_OFFICE','MOD_ADMIN_OFFICE'
779 CURSOR po_headers_ext_cursor(p_addr_attr_group_id NUMBER) IS
780 SELECT extension_id, EGO_EXTFWK_S.NEXTVAL
781 FROM po_headers_all_ext_b
782 WHERE po_header_id = p_uda_key_po_header_id
783 AND draft_id = p_uda_key_draft_id
784 AND attr_group_id = p_addr_attr_group_id
785 AND c_ext_attr39 IN ('MOD_ISSUING_OFFICE','MOD_ADMIN_OFFICE');
786
787 BEGIN
788 d_position := 0;
789 IF (PO_LOG.d_proc) THEN
790 PO_LOG.proc_begin(d_module,'p_uda_key_po_header_id',p_uda_key_po_header_id);
791 PO_LOG.proc_begin(d_module,'p_uda_key_draft_id',p_uda_key_draft_id);
792 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
793 PO_LOG.proc_begin(d_module,'p_draft_id',p_draft_id);
794 END IF;
795
796 -- Return if UDA not enabled
797 IF p_is_uda_enabled <> 'Y' THEN
798 RETURN;
799 END IF;
800
801 d_position := 10;
802 debug(d_module, d_position, 'Fetch the Addresses Attr Grp Id');
803
804 -- Get the attribute Group Id against addresses Attr Grp
805 SELECT attr_group_id
806 INTO l_addr_attr_group_id
807 FROM ego_attr_groups_v
808 WHERE attr_group_type = l_addr_attr_grp_type
809 AND attr_group_name = l_addr_attr_grp_name;
810
811 d_position := 20;
812 debug(d_module, d_position, 'Merge the Mod-specific addresses into Headers Ext tables', FND_API.G_TRUE);
813
814 OPEN po_headers_ext_cursor(l_addr_attr_group_id);
815 FETCH po_headers_ext_cursor BULK COLLECT INTO l_old_hdr_ext_id_tbl, l_new_hdr_ext_id_tbl;
816 CLOSE po_headers_ext_cursor;
817
818 -- Start Insert into PO_HEADERS_ALL_EXT_B
819 d_position := 30;
820 debug(d_module, d_position, 'Insert into Address Ext B');
821
822 FORALL i IN 1..l_old_hdr_ext_id_tbl.Count
823 INSERT INTO PO_HEADERS_ALL_EXT_B
824 (EXTENSION_ID
825 ,ATTR_GROUP_ID
826 ,PO_HEADER_ID
827 ,DATA_LEVEL_ID
828 ,PK1_VALUE
829 ,PK2_VALUE
830 ,PK3_VALUE
831 ,PK4_VALUE
832 ,PK5_VALUE
833 ,LAST_UPDATE_DATE
834 ,LAST_UPDATED_BY
835 ,LAST_UPDATE_LOGIN
836 ,CREATION_DATE
837 ,CREATED_BY
838 ,C_EXT_ATTR1
839 ,C_EXT_ATTR2
840 ,C_EXT_ATTR3
841 ,C_EXT_ATTR4
842 ,C_EXT_ATTR5
843 ,C_EXT_ATTR6
844 ,C_EXT_ATTR7
845 ,C_EXT_ATTR8
846 ,C_EXT_ATTR9
847 ,C_EXT_ATTR10
848 ,C_EXT_ATTR11
849 ,C_EXT_ATTR12
850 ,C_EXT_ATTR13
851 ,C_EXT_ATTR14
852 ,C_EXT_ATTR15
853 ,C_EXT_ATTR16
854 ,C_EXT_ATTR17
855 ,C_EXT_ATTR18
856 ,C_EXT_ATTR19
857 ,C_EXT_ATTR20
858 ,C_EXT_ATTR21
859 ,C_EXT_ATTR22
860 ,C_EXT_ATTR23
861 ,C_EXT_ATTR24
862 ,C_EXT_ATTR25
863 ,C_EXT_ATTR26
864 ,C_EXT_ATTR27
865 ,C_EXT_ATTR28
866 ,C_EXT_ATTR29
867 ,C_EXT_ATTR30
868 ,C_EXT_ATTR31
869 ,C_EXT_ATTR32
870 ,C_EXT_ATTR33
871 ,C_EXT_ATTR34
872 ,C_EXT_ATTR35
873 ,C_EXT_ATTR36
874 ,C_EXT_ATTR37
875 ,C_EXT_ATTR38
876 ,C_EXT_ATTR39
877 ,C_EXT_ATTR40
878 ,N_EXT_ATTR1
879 ,N_EXT_ATTR2
880 ,N_EXT_ATTR3
881 ,N_EXT_ATTR4
882 ,N_EXT_ATTR5
883 ,N_EXT_ATTR6
884 ,N_EXT_ATTR7
885 ,N_EXT_ATTR8
886 ,N_EXT_ATTR9
887 ,N_EXT_ATTR10
888 ,N_EXT_ATTR11
889 ,N_EXT_ATTR12
890 ,N_EXT_ATTR13
891 ,N_EXT_ATTR14
892 ,N_EXT_ATTR15
893 ,N_EXT_ATTR16
894 ,N_EXT_ATTR17
895 ,N_EXT_ATTR18
896 ,N_EXT_ATTR19
897 ,N_EXT_ATTR20
898 ,UOM_EXT_ATTR1
899 ,UOM_EXT_ATTR2
900 ,UOM_EXT_ATTR3
901 ,UOM_EXT_ATTR4
902 ,UOM_EXT_ATTR5
903 ,UOM_EXT_ATTR6
904 ,UOM_EXT_ATTR7
905 ,UOM_EXT_ATTR8
906 ,UOM_EXT_ATTR9
907 ,UOM_EXT_ATTR10
908 ,UOM_EXT_ATTR11
909 ,UOM_EXT_ATTR12
910 ,UOM_EXT_ATTR13
911 ,UOM_EXT_ATTR14
912 ,UOM_EXT_ATTR15
913 ,UOM_EXT_ATTR16
914 ,UOM_EXT_ATTR17
915 ,UOM_EXT_ATTR18
916 ,UOM_EXT_ATTR19
917 ,UOM_EXT_ATTR20
918 ,D_EXT_ATTR1
919 ,D_EXT_ATTR2
920 ,D_EXT_ATTR3
921 ,D_EXT_ATTR4
922 ,D_EXT_ATTR5
923 ,D_EXT_ATTR6
924 ,D_EXT_ATTR7
925 ,D_EXT_ATTR8
926 ,D_EXT_ATTR9
927 ,D_EXT_ATTR10
928 ,UDA_TEMPLATE_ID
929 ,DRAFT_ID
930 )
931 SELECT
932 l_new_hdr_ext_id_tbl(i)
933 ,ATTR_GROUP_ID
934 ,p_po_header_id
935 ,DATA_LEVEL_ID
936 ,PK1_VALUE
937 ,PK2_VALUE
938 ,PK3_VALUE
939 ,PK4_VALUE
940 ,PK5_VALUE
941 ,SYSDATE
942 ,FND_GLOBAL.user_id
943 ,FND_GLOBAL.login_id
944 ,SYSDATE
945 ,FND_GLOBAL.user_id
946 ,C_EXT_ATTR1
947 ,C_EXT_ATTR2
948 ,C_EXT_ATTR3
949 ,C_EXT_ATTR4
950 ,C_EXT_ATTR5
951 ,C_EXT_ATTR6
952 ,C_EXT_ATTR7
953 ,C_EXT_ATTR8
954 ,C_EXT_ATTR9
955 ,C_EXT_ATTR10
956 ,C_EXT_ATTR11
957 ,C_EXT_ATTR12
958 ,C_EXT_ATTR13
959 ,C_EXT_ATTR14
960 ,C_EXT_ATTR15
961 ,C_EXT_ATTR16
962 ,C_EXT_ATTR17
963 ,C_EXT_ATTR18
964 ,C_EXT_ATTR19
965 ,C_EXT_ATTR20
966 ,C_EXT_ATTR21
967 ,C_EXT_ATTR22
968 ,C_EXT_ATTR23
969 ,C_EXT_ATTR24
970 ,C_EXT_ATTR25
971 ,C_EXT_ATTR26
972 ,C_EXT_ATTR27
973 ,C_EXT_ATTR28
974 ,C_EXT_ATTR29
975 ,C_EXT_ATTR30
976 ,C_EXT_ATTR31
977 ,C_EXT_ATTR32
978 ,C_EXT_ATTR33
979 ,C_EXT_ATTR34
980 ,C_EXT_ATTR35
981 ,C_EXT_ATTR36
982 ,C_EXT_ATTR37
983 ,C_EXT_ATTR38
984 ,C_EXT_ATTR39
985 ,C_EXT_ATTR40
986 ,N_EXT_ATTR1
987 ,N_EXT_ATTR2
988 ,N_EXT_ATTR3
989 ,N_EXT_ATTR4
990 ,N_EXT_ATTR5
991 ,N_EXT_ATTR6
992 ,N_EXT_ATTR7
993 ,N_EXT_ATTR8
994 ,N_EXT_ATTR9
995 ,N_EXT_ATTR10
996 ,N_EXT_ATTR11
997 ,N_EXT_ATTR12
998 ,N_EXT_ATTR13
999 ,N_EXT_ATTR14
1000 ,N_EXT_ATTR15
1001 ,N_EXT_ATTR16
1002 ,N_EXT_ATTR17
1003 ,N_EXT_ATTR18
1004 ,N_EXT_ATTR19
1005 ,N_EXT_ATTR20
1006 ,UOM_EXT_ATTR1
1007 ,UOM_EXT_ATTR2
1008 ,UOM_EXT_ATTR3
1009 ,UOM_EXT_ATTR4
1010 ,UOM_EXT_ATTR5
1011 ,UOM_EXT_ATTR6
1012 ,UOM_EXT_ATTR7
1013 ,UOM_EXT_ATTR8
1014 ,UOM_EXT_ATTR9
1015 ,UOM_EXT_ATTR10
1016 ,UOM_EXT_ATTR11
1017 ,UOM_EXT_ATTR12
1018 ,UOM_EXT_ATTR13
1019 ,UOM_EXT_ATTR14
1020 ,UOM_EXT_ATTR15
1021 ,UOM_EXT_ATTR16
1022 ,UOM_EXT_ATTR17
1023 ,UOM_EXT_ATTR18
1024 ,UOM_EXT_ATTR19
1025 ,UOM_EXT_ATTR20
1026 ,D_EXT_ATTR1
1027 ,D_EXT_ATTR2
1028 ,D_EXT_ATTR3
1029 ,D_EXT_ATTR4
1030 ,D_EXT_ATTR5
1031 ,D_EXT_ATTR6
1032 ,D_EXT_ATTR7
1033 ,D_EXT_ATTR8
1034 ,D_EXT_ATTR9
1035 ,D_EXT_ATTR10
1036 ,UDA_TEMPLATE_ID
1037 ,p_draft_id
1038 FROM PO_HEADERS_ALL_EXT_B
1039 WHERE extension_id = l_old_hdr_ext_id_tbl(i);
1040 -- End PO_HEADERS_ALL_EXT_B
1041
1042 -- Start PO_HEADERS_ALL_EXT_TL
1043 d_position := 40;
1044 debug(d_module, d_position, 'Insert into Address Ext TL');
1045
1046 FORALL i IN 1..l_old_hdr_ext_id_tbl.Count
1047 INSERT INTO PO_HEADERS_ALL_EXT_TL
1048 (EXTENSION_ID
1049 ,ATTR_GROUP_ID
1050 ,PO_HEADER_ID
1051 ,DATA_LEVEL_ID
1052 ,PK1_VALUE
1053 ,PK2_VALUE
1054 ,PK3_VALUE
1055 ,PK4_VALUE
1056 ,PK5_VALUE
1057 ,SOURCE_LANG
1058 ,LANGUAGE
1059 ,LAST_UPDATE_DATE
1060 ,LAST_UPDATED_BY
1061 ,LAST_UPDATE_LOGIN
1062 ,CREATION_DATE
1063 ,CREATED_BY
1064 ,TL_EXT_ATTR1
1065 ,TL_EXT_ATTR2
1066 ,TL_EXT_ATTR3
1067 ,TL_EXT_ATTR4
1068 ,TL_EXT_ATTR5
1069 ,TL_EXT_ATTR6
1070 ,TL_EXT_ATTR7
1071 ,TL_EXT_ATTR8
1072 ,TL_EXT_ATTR9
1073 ,TL_EXT_ATTR10
1074 ,TL_EXT_ATTR11
1075 ,TL_EXT_ATTR12
1076 ,TL_EXT_ATTR13
1077 ,TL_EXT_ATTR14
1078 ,TL_EXT_ATTR15
1079 ,TL_EXT_ATTR16
1080 ,TL_EXT_ATTR17
1081 ,TL_EXT_ATTR18
1082 ,TL_EXT_ATTR19
1083 ,TL_EXT_ATTR20
1084 ,TL_EXT_ATTR21
1085 ,TL_EXT_ATTR22
1086 ,TL_EXT_ATTR23
1087 ,TL_EXT_ATTR24
1088 ,TL_EXT_ATTR25
1089 ,TL_EXT_ATTR26
1090 ,TL_EXT_ATTR27
1091 ,TL_EXT_ATTR28
1092 ,TL_EXT_ATTR29
1093 ,TL_EXT_ATTR30
1094 ,TL_EXT_ATTR31
1095 ,TL_EXT_ATTR32
1096 ,TL_EXT_ATTR33
1097 ,TL_EXT_ATTR34
1098 ,TL_EXT_ATTR35
1099 ,TL_EXT_ATTR36
1100 ,TL_EXT_ATTR37
1101 ,TL_EXT_ATTR38
1102 ,TL_EXT_ATTR39
1103 ,TL_EXT_ATTR40
1104 ,UDA_TEMPLATE_ID
1105 ,DRAFT_ID
1106 )
1107 SELECT
1108 l_new_hdr_ext_id_tbl(i)
1109 ,ATTR_GROUP_ID
1110 ,p_po_header_id
1111 ,DATA_LEVEL_ID
1112 ,PK1_VALUE
1113 ,PK2_VALUE
1114 ,PK3_VALUE
1115 ,PK4_VALUE
1116 ,PK5_VALUE
1117 ,SOURCE_LANG
1118 ,LANGUAGE
1119 ,SYSDATE
1120 ,FND_GLOBAL.user_id
1121 ,FND_GLOBAL.login_id
1122 ,SYSDATE
1123 ,FND_GLOBAL.user_id
1124 ,TL_EXT_ATTR1
1125 ,TL_EXT_ATTR2
1126 ,TL_EXT_ATTR3
1127 ,TL_EXT_ATTR4
1128 ,TL_EXT_ATTR5
1129 ,TL_EXT_ATTR6
1130 ,TL_EXT_ATTR7
1131 ,TL_EXT_ATTR8
1132 ,TL_EXT_ATTR9
1133 ,TL_EXT_ATTR10
1134 ,TL_EXT_ATTR11
1135 ,TL_EXT_ATTR12
1136 ,TL_EXT_ATTR13
1137 ,TL_EXT_ATTR14
1138 ,TL_EXT_ATTR15
1139 ,TL_EXT_ATTR16
1140 ,TL_EXT_ATTR17
1141 ,TL_EXT_ATTR18
1142 ,TL_EXT_ATTR19
1143 ,TL_EXT_ATTR20
1144 ,TL_EXT_ATTR21
1145 ,TL_EXT_ATTR22
1146 ,TL_EXT_ATTR23
1147 ,TL_EXT_ATTR24
1148 ,TL_EXT_ATTR25
1149 ,TL_EXT_ATTR26
1150 ,TL_EXT_ATTR27
1151 ,TL_EXT_ATTR28
1152 ,TL_EXT_ATTR29
1153 ,TL_EXT_ATTR30
1154 ,TL_EXT_ATTR31
1155 ,TL_EXT_ATTR32
1156 ,TL_EXT_ATTR33
1157 ,TL_EXT_ATTR34
1158 ,TL_EXT_ATTR35
1159 ,TL_EXT_ATTR36
1160 ,TL_EXT_ATTR37
1161 ,TL_EXT_ATTR38
1162 ,TL_EXT_ATTR39
1163 ,TL_EXT_ATTR40
1164 ,UDA_TEMPLATE_ID
1165 ,p_draft_id
1166 FROM PO_HEADERS_ALL_EXT_TL
1167 WHERE extension_id = l_old_hdr_ext_id_tbl(i);
1168 --AND language = userenv('LANG');
1169 -- End PO_HEADERS_ALL_EXT_TL
1170
1171 -- Merge the Multi-Mod Addresses only if there exists at least one address change on the Multi-Mod
1172 IF (p_addr_change_flag = 'Y') THEN
1173
1174 d_position := 50;
1175 debug(d_module, d_position, 'Merge the Address records as Address changes exist on Multi-Mod', FND_API.G_TRUE);
1176
1177 d_position := 60;
1178 debug(d_module, d_position, 'Merge records in Address B Table');
1179 -- Update Headers Ext Table with addresses from the Multi-Mod (stored against
1180 -- uda_key_po_header_id, uda_key_draft_id) if not null
1181 UPDATE po_headers_all_ext_b PHA1
1182 SET(
1183 C_EXT_ATTR1
1184 ,C_EXT_ATTR2
1185 ,C_EXT_ATTR3
1186 ,C_EXT_ATTR4
1187 ,C_EXT_ATTR5
1188 ,C_EXT_ATTR6
1189 ,C_EXT_ATTR7
1190 ,C_EXT_ATTR8
1191 ,C_EXT_ATTR9
1192 ,C_EXT_ATTR10
1193 ,C_EXT_ATTR11
1194 ,C_EXT_ATTR12
1195 ,C_EXT_ATTR13
1196 ,C_EXT_ATTR14
1197 ,C_EXT_ATTR15
1198 ,C_EXT_ATTR16
1199 ,C_EXT_ATTR17
1200 ,C_EXT_ATTR18
1201 ,C_EXT_ATTR19
1202 ,C_EXT_ATTR20
1203 ,C_EXT_ATTR21
1204 ,C_EXT_ATTR22
1205 ,C_EXT_ATTR23
1206 ,C_EXT_ATTR24
1207 ,C_EXT_ATTR25
1208 ,C_EXT_ATTR26
1209 ,C_EXT_ATTR27
1210 ,C_EXT_ATTR28
1211 ,C_EXT_ATTR29
1212 ,C_EXT_ATTR30
1213 ,C_EXT_ATTR31
1214 ,C_EXT_ATTR32
1215 ,C_EXT_ATTR33
1216 ,C_EXT_ATTR34
1217 ,C_EXT_ATTR35
1218 ,C_EXT_ATTR36
1219 ,C_EXT_ATTR37
1220 ,C_EXT_ATTR38
1221 ,C_EXT_ATTR39
1222 ,C_EXT_ATTR40
1223 ,N_EXT_ATTR1
1224 ,N_EXT_ATTR2
1225 ,N_EXT_ATTR3
1226 ,N_EXT_ATTR4
1227 ,N_EXT_ATTR5
1228 ,N_EXT_ATTR6
1229 ,N_EXT_ATTR7
1230 ,N_EXT_ATTR8
1231 ,N_EXT_ATTR9
1232 ,N_EXT_ATTR10
1233 ,N_EXT_ATTR11
1234 ,N_EXT_ATTR12
1235 ,N_EXT_ATTR13
1236 ,N_EXT_ATTR14
1237 ,N_EXT_ATTR15
1238 ,N_EXT_ATTR16
1239 ,N_EXT_ATTR17
1240 ,N_EXT_ATTR18
1241 ,N_EXT_ATTR19
1242 ,N_EXT_ATTR20
1243 ,UOM_EXT_ATTR1
1244 ,UOM_EXT_ATTR2
1245 ,UOM_EXT_ATTR3
1246 ,UOM_EXT_ATTR4
1247 ,UOM_EXT_ATTR5
1248 ,UOM_EXT_ATTR6
1249 ,UOM_EXT_ATTR7
1250 ,UOM_EXT_ATTR8
1251 ,UOM_EXT_ATTR9
1252 ,UOM_EXT_ATTR10
1253 ,UOM_EXT_ATTR11
1254 ,UOM_EXT_ATTR12
1255 ,UOM_EXT_ATTR13
1256 ,UOM_EXT_ATTR14
1257 ,UOM_EXT_ATTR15
1258 ,UOM_EXT_ATTR16
1259 ,UOM_EXT_ATTR17
1260 ,UOM_EXT_ATTR18
1261 ,UOM_EXT_ATTR19
1262 ,UOM_EXT_ATTR20
1263 ,D_EXT_ATTR1
1264 ,D_EXT_ATTR2
1265 ,D_EXT_ATTR3
1266 ,D_EXT_ATTR4
1267 ,D_EXT_ATTR5
1268 ,D_EXT_ATTR6
1269 ,D_EXT_ATTR7
1270 ,D_EXT_ATTR8
1271 ,D_EXT_ATTR9
1272 ,D_EXT_ATTR10) =
1273 (SELECT
1274 Nvl(PHA2.C_EXT_ATTR1, PHA1.C_EXT_ATTR1)
1275 ,Nvl(PHA2.C_EXT_ATTR2, PHA1.C_EXT_ATTR2)
1276 ,Nvl(PHA2.C_EXT_ATTR3, PHA1.C_EXT_ATTR3)
1277 ,Nvl(PHA2.C_EXT_ATTR4, PHA1.C_EXT_ATTR4)
1278 ,Nvl(PHA2.C_EXT_ATTR5, PHA1.C_EXT_ATTR5)
1279 ,Nvl(PHA2.C_EXT_ATTR6, PHA1.C_EXT_ATTR6)
1280 ,Nvl(PHA2.C_EXT_ATTR7, PHA1.C_EXT_ATTR7)
1281 ,Nvl(PHA2.C_EXT_ATTR8, PHA1.C_EXT_ATTR8)
1282 ,Nvl(PHA2.C_EXT_ATTR9, PHA1.C_EXT_ATTR9)
1283 ,Nvl(PHA2.C_EXT_ATTR10, PHA1.C_EXT_ATTR10)
1284 ,Nvl(PHA2.C_EXT_ATTR11, PHA1.C_EXT_ATTR11)
1285 ,Nvl(PHA2.C_EXT_ATTR12, PHA1.C_EXT_ATTR12)
1286 ,Nvl(PHA2.C_EXT_ATTR13, PHA1.C_EXT_ATTR13)
1287 ,Nvl(PHA2.C_EXT_ATTR14, PHA1.C_EXT_ATTR14)
1288 ,Nvl(PHA2.C_EXT_ATTR15, PHA1.C_EXT_ATTR15)
1289 ,Nvl(PHA2.C_EXT_ATTR16, PHA1.C_EXT_ATTR16)
1290 ,Nvl(PHA2.C_EXT_ATTR17, PHA1.C_EXT_ATTR17)
1291 ,Nvl(PHA2.C_EXT_ATTR18, PHA1.C_EXT_ATTR18)
1292 ,Nvl(PHA2.C_EXT_ATTR19, PHA1.C_EXT_ATTR19)
1293 ,Nvl(PHA2.C_EXT_ATTR20, PHA1.C_EXT_ATTR20)
1294 ,Nvl(PHA2.C_EXT_ATTR21, PHA1.C_EXT_ATTR21)
1295 ,Nvl(PHA2.C_EXT_ATTR22, PHA1.C_EXT_ATTR22)
1296 ,Nvl(PHA2.C_EXT_ATTR23, PHA1.C_EXT_ATTR23)
1297 ,Nvl(PHA2.C_EXT_ATTR24, PHA1.C_EXT_ATTR24)
1298 ,Nvl(PHA2.C_EXT_ATTR25, PHA1.C_EXT_ATTR25)
1299 ,Nvl(PHA2.C_EXT_ATTR26, PHA1.C_EXT_ATTR26)
1300 ,Nvl(PHA2.C_EXT_ATTR27, PHA1.C_EXT_ATTR27)
1301 ,Nvl(PHA2.C_EXT_ATTR28, PHA1.C_EXT_ATTR28)
1302 ,Nvl(PHA2.C_EXT_ATTR29, PHA1.C_EXT_ATTR29)
1303 ,Nvl(PHA2.C_EXT_ATTR30, PHA1.C_EXT_ATTR30)
1304 ,Nvl(PHA2.C_EXT_ATTR31, PHA1.C_EXT_ATTR31)
1305 ,Nvl(PHA2.C_EXT_ATTR32, PHA1.C_EXT_ATTR32)
1306 ,Nvl(PHA2.C_EXT_ATTR33, PHA1.C_EXT_ATTR33)
1307 ,Nvl(PHA2.C_EXT_ATTR34, PHA1.C_EXT_ATTR34)
1308 ,Nvl(PHA2.C_EXT_ATTR35, PHA1.C_EXT_ATTR35)
1309 ,Nvl(PHA2.C_EXT_ATTR36, PHA1.C_EXT_ATTR36)
1310 ,Nvl(PHA2.C_EXT_ATTR37, PHA1.C_EXT_ATTR37)
1311 ,Nvl(PHA2.C_EXT_ATTR38, PHA1.C_EXT_ATTR38)
1312 ,Nvl(PHA2.C_EXT_ATTR39, PHA1.C_EXT_ATTR39)
1313 ,PHA1.C_EXT_ATTR40 -- keeping value which is on base document.
1314 --,l_mod_uda_address_types -- The Address Type for the Mod is always PO_MOD_UDA_ADDRESS_TYPES
1315 ,Nvl(PHA2.N_EXT_ATTR1, PHA1.N_EXT_ATTR1)
1316 ,Nvl(PHA2.N_EXT_ATTR2, PHA1.N_EXT_ATTR2)
1317 ,Nvl(PHA2.N_EXT_ATTR3, PHA1.N_EXT_ATTR3)
1318 ,Nvl(PHA2.N_EXT_ATTR4, PHA1.N_EXT_ATTR4)
1319 ,Nvl(PHA2.N_EXT_ATTR5, PHA1.N_EXT_ATTR5)
1320 ,Nvl(PHA2.N_EXT_ATTR6, PHA1.N_EXT_ATTR6)
1321 ,Nvl(PHA2.N_EXT_ATTR7, PHA1.N_EXT_ATTR7)
1322 ,Nvl(PHA2.N_EXT_ATTR8, PHA1.N_EXT_ATTR8)
1323 ,Nvl(PHA2.N_EXT_ATTR9, PHA1.N_EXT_ATTR9)
1324 ,Nvl(PHA2.N_EXT_ATTR10, PHA1.N_EXT_ATTR10)
1325 ,Nvl(PHA2.N_EXT_ATTR11, PHA1.N_EXT_ATTR11)
1326 ,Nvl(PHA2.N_EXT_ATTR12, PHA1.N_EXT_ATTR12)
1327 ,Nvl(PHA2.N_EXT_ATTR13, PHA1.N_EXT_ATTR13)
1328 ,Nvl(PHA2.N_EXT_ATTR14, PHA1.N_EXT_ATTR14)
1329 ,Nvl(PHA2.N_EXT_ATTR15, PHA1.N_EXT_ATTR15)
1330 ,Nvl(PHA2.N_EXT_ATTR16, PHA1.N_EXT_ATTR16)
1331 ,Nvl(PHA2.N_EXT_ATTR17, PHA1.N_EXT_ATTR17)
1332 ,Nvl(PHA2.N_EXT_ATTR18, PHA1.N_EXT_ATTR18)
1333 ,Nvl(PHA2.N_EXT_ATTR19, PHA1.N_EXT_ATTR19)
1334 ,Nvl(PHA2.N_EXT_ATTR20, PHA1.N_EXT_ATTR20)
1335 ,Nvl(PHA2.UOM_EXT_ATTR1, PHA1.UOM_EXT_ATTR1)
1336 ,Nvl(PHA2.UOM_EXT_ATTR2, PHA1.UOM_EXT_ATTR2)
1337 ,Nvl(PHA2.UOM_EXT_ATTR3, PHA1.UOM_EXT_ATTR3)
1338 ,Nvl(PHA2.UOM_EXT_ATTR4, PHA1.UOM_EXT_ATTR4)
1339 ,Nvl(PHA2.UOM_EXT_ATTR5, PHA1.UOM_EXT_ATTR5)
1340 ,Nvl(PHA2.UOM_EXT_ATTR6, PHA1.UOM_EXT_ATTR6)
1341 ,Nvl(PHA2.UOM_EXT_ATTR7, PHA1.UOM_EXT_ATTR7)
1342 ,Nvl(PHA2.UOM_EXT_ATTR8, PHA1.UOM_EXT_ATTR8)
1343 ,Nvl(PHA2.UOM_EXT_ATTR9, PHA1.UOM_EXT_ATTR9)
1344 ,Nvl(PHA2.UOM_EXT_ATTR10, PHA1.UOM_EXT_ATTR10)
1345 ,Nvl(PHA2.UOM_EXT_ATTR11, PHA1.UOM_EXT_ATTR11)
1346 ,Nvl(PHA2.UOM_EXT_ATTR12, PHA1.UOM_EXT_ATTR12)
1347 ,Nvl(PHA2.UOM_EXT_ATTR13, PHA1.UOM_EXT_ATTR13)
1348 ,Nvl(PHA2.UOM_EXT_ATTR14, PHA1.UOM_EXT_ATTR14)
1349 ,Nvl(PHA2.UOM_EXT_ATTR15, PHA1.UOM_EXT_ATTR15)
1350 ,Nvl(PHA2.UOM_EXT_ATTR16, PHA1.UOM_EXT_ATTR16)
1351 ,Nvl(PHA2.UOM_EXT_ATTR17, PHA1.UOM_EXT_ATTR17)
1352 ,Nvl(PHA2.UOM_EXT_ATTR18, PHA1.UOM_EXT_ATTR18)
1353 ,Nvl(PHA2.UOM_EXT_ATTR19, PHA1.UOM_EXT_ATTR19)
1354 ,Nvl(PHA2.UOM_EXT_ATTR20, PHA1.UOM_EXT_ATTR20)
1355 ,Nvl(PHA2.D_EXT_ATTR1, PHA1.D_EXT_ATTR1)
1356 ,Nvl(PHA2.D_EXT_ATTR2, PHA1.D_EXT_ATTR2)
1357 ,Nvl(PHA2.D_EXT_ATTR3, PHA1.D_EXT_ATTR3)
1358 ,Nvl(PHA2.D_EXT_ATTR4, PHA1.D_EXT_ATTR4)
1359 ,Nvl(PHA2.D_EXT_ATTR5, PHA1.D_EXT_ATTR5)
1360 ,Nvl(PHA2.D_EXT_ATTR6, PHA1.D_EXT_ATTR6)
1361 ,Nvl(PHA2.D_EXT_ATTR7, PHA1.D_EXT_ATTR7)
1362 ,Nvl(PHA2.D_EXT_ATTR8, PHA1.D_EXT_ATTR8)
1363 ,Nvl(PHA2.D_EXT_ATTR9, PHA1.D_EXT_ATTR9)
1364 ,Nvl(PHA2.D_EXT_ATTR10, PHA1.D_EXT_ATTR10)
1365 FROM po_headers_all_ext_b PHA2
1366 WHERE PHA2.po_header_id = p_uda_key_po_header_id
1367 AND PHA2.draft_id = p_uda_key_draft_id
1368 AND PHA2.attr_group_id = l_addr_attr_group_id
1369 AND PHA1.attr_group_id = PHA2.attr_group_id
1370 AND PHA1.c_ext_attr39 = PHA2.c_ext_attr39)
1371 WHERE PHA1.po_header_id = p_po_header_id
1372 AND PHA1.draft_id = p_draft_id
1373 AND PHA1.attr_group_id = l_addr_attr_group_id;
1374
1375 d_position := 60;
1376 debug(d_module, d_position, 'Merge records in Address TL Table');
1377
1378 UPDATE po_headers_all_ext_tl PHAT1
1379 SET(
1380 TL_EXT_ATTR1
1381 ,TL_EXT_ATTR2
1382 ,TL_EXT_ATTR3
1383 ,TL_EXT_ATTR4
1384 ,TL_EXT_ATTR5
1385 ,TL_EXT_ATTR6
1386 ,TL_EXT_ATTR7
1387 ,TL_EXT_ATTR8
1388 ,TL_EXT_ATTR9
1389 ,TL_EXT_ATTR10
1390 ,TL_EXT_ATTR11
1391 ,TL_EXT_ATTR12
1392 ,TL_EXT_ATTR13
1393 ,TL_EXT_ATTR14
1394 ,TL_EXT_ATTR15
1395 ,TL_EXT_ATTR16
1396 ,TL_EXT_ATTR17
1397 ,TL_EXT_ATTR18
1398 ,TL_EXT_ATTR19
1399 ,TL_EXT_ATTR20
1400 ,TL_EXT_ATTR21
1401 ,TL_EXT_ATTR22
1402 ,TL_EXT_ATTR23
1403 ,TL_EXT_ATTR24
1404 ,TL_EXT_ATTR25
1405 ,TL_EXT_ATTR26
1406 ,TL_EXT_ATTR27
1407 ,TL_EXT_ATTR28
1408 ,TL_EXT_ATTR29
1409 ,TL_EXT_ATTR30
1410 ,TL_EXT_ATTR31
1411 ,TL_EXT_ATTR32
1412 ,TL_EXT_ATTR33
1413 ,TL_EXT_ATTR34
1414 ,TL_EXT_ATTR35
1415 ,TL_EXT_ATTR36
1416 ,TL_EXT_ATTR37
1417 ,TL_EXT_ATTR38
1418 ,TL_EXT_ATTR39
1419 ,TL_EXT_ATTR40
1420 ) =
1421 (SELECT
1422 Nvl(PHAT2.TL_EXT_ATTR1, PHAT1.TL_EXT_ATTR1)
1423 ,Nvl(PHAT2.TL_EXT_ATTR2, PHAT1.TL_EXT_ATTR2)
1424 ,Nvl(PHAT2.TL_EXT_ATTR3, PHAT1.TL_EXT_ATTR3)
1425 ,Nvl(PHAT2.TL_EXT_ATTR4, PHAT1.TL_EXT_ATTR4)
1426 ,Nvl(PHAT2.TL_EXT_ATTR5, PHAT1.TL_EXT_ATTR5)
1427 ,Nvl(PHAT2.TL_EXT_ATTR6, PHAT1.TL_EXT_ATTR6)
1428 ,Nvl(PHAT2.TL_EXT_ATTR7, PHAT1.TL_EXT_ATTR7)
1429 ,Nvl(PHAT2.TL_EXT_ATTR8, PHAT1.TL_EXT_ATTR8)
1430 ,Nvl(PHAT2.TL_EXT_ATTR9, PHAT1.TL_EXT_ATTR9)
1431 ,Nvl(PHAT2.TL_EXT_ATTR10, PHAT1.TL_EXT_ATTR10)
1432 ,Nvl(PHAT2.TL_EXT_ATTR11, PHAT1.TL_EXT_ATTR11)
1433 ,Nvl(PHAT2.TL_EXT_ATTR12, PHAT1.TL_EXT_ATTR12)
1434 ,Nvl(PHAT2.TL_EXT_ATTR13, PHAT1.TL_EXT_ATTR13)
1435 ,Nvl(PHAT2.TL_EXT_ATTR14, PHAT1.TL_EXT_ATTR14)
1436 ,Nvl(PHAT2.TL_EXT_ATTR15, PHAT1.TL_EXT_ATTR15)
1437 ,Nvl(PHAT2.TL_EXT_ATTR16, PHAT1.TL_EXT_ATTR16)
1438 ,Nvl(PHAT2.TL_EXT_ATTR17, PHAT1.TL_EXT_ATTR17)
1439 ,Nvl(PHAT2.TL_EXT_ATTR18, PHAT1.TL_EXT_ATTR18)
1440 ,Nvl(PHAT2.TL_EXT_ATTR19, PHAT1.TL_EXT_ATTR19)
1441 ,Nvl(PHAT2.TL_EXT_ATTR20, PHAT1.TL_EXT_ATTR20)
1442 ,Nvl(PHAT2.TL_EXT_ATTR21, PHAT1.TL_EXT_ATTR21)
1443 ,Nvl(PHAT2.TL_EXT_ATTR22, PHAT1.TL_EXT_ATTR22)
1444 ,Nvl(PHAT2.TL_EXT_ATTR23, PHAT1.TL_EXT_ATTR23)
1445 ,Nvl(PHAT2.TL_EXT_ATTR24, PHAT1.TL_EXT_ATTR24)
1446 ,Nvl(PHAT2.TL_EXT_ATTR25, PHAT1.TL_EXT_ATTR25)
1447 ,Nvl(PHAT2.TL_EXT_ATTR26, PHAT1.TL_EXT_ATTR26)
1448 ,Nvl(PHAT2.TL_EXT_ATTR27, PHAT1.TL_EXT_ATTR27)
1449 ,Nvl(PHAT2.TL_EXT_ATTR28, PHAT1.TL_EXT_ATTR28)
1450 ,Nvl(PHAT2.TL_EXT_ATTR29, PHAT1.TL_EXT_ATTR29)
1451 ,Nvl(PHAT2.TL_EXT_ATTR30, PHAT1.TL_EXT_ATTR30)
1452 ,Nvl(PHAT2.TL_EXT_ATTR31, PHAT1.TL_EXT_ATTR31)
1453 ,Nvl(PHAT2.TL_EXT_ATTR32, PHAT1.TL_EXT_ATTR32)
1454 ,Nvl(PHAT2.TL_EXT_ATTR33, PHAT1.TL_EXT_ATTR33)
1455 ,Nvl(PHAT2.TL_EXT_ATTR34, PHAT1.TL_EXT_ATTR34)
1456 ,Nvl(PHAT2.TL_EXT_ATTR35, PHAT1.TL_EXT_ATTR35)
1457 ,Nvl(PHAT2.TL_EXT_ATTR36, PHAT1.TL_EXT_ATTR36)
1458 ,Nvl(PHAT2.TL_EXT_ATTR37, PHAT1.TL_EXT_ATTR37)
1459 ,Nvl(PHAT2.TL_EXT_ATTR38, PHAT1.TL_EXT_ATTR38)
1460 ,Nvl(PHAT2.TL_EXT_ATTR39, PHAT1.TL_EXT_ATTR39)
1461 ,Nvl(PHAT2.TL_EXT_ATTR40, PHAT1.TL_EXT_ATTR40)
1462 FROM po_headers_all_ext_tl PHAT2
1463 WHERE PHAT2.po_header_id = p_uda_key_po_header_id
1464 AND PHAT2.draft_id = p_uda_key_draft_id
1465 AND PHAT2.attr_group_id = l_addr_attr_group_id
1466 AND PHAT1.attr_group_id = PHAT2.attr_group_id
1467 AND PHAT1.tl_ext_attr39 = PHAT2.tl_ext_attr39
1468 AND PHAT1.language = PHAT2.language)
1469 WHERE PHAT1.po_header_id = p_po_header_id
1470 AND PHAT1.draft_id = p_draft_id
1471 AND PHAT1.attr_group_id = l_addr_attr_group_id;
1472
1473 END IF; --IF (p_addr_change_flag = 'Y')
1474
1475 -- Execute the XPD Function for the Addresses as part of post save
1476 d_position := 70;
1477 debug(d_module, d_position, 'Execute the XPD Function for the Addresses as part of post save', FND_API.G_TRUE);
1478
1479 -- Setting the draft ID here which will be used in Post DML Functions.
1480 PO_FED_FIELD_FUNCTIONS.XPD_DRAFT_ID := p_draft_id;
1481
1482 PO_UDA_DEFAULTING_PKG.UDA_EXECUTE_ACTIONS(
1483 p_pk1_value => p_po_header_id,
1484 p_pk2_value => p_draft_id,
1485 p_template_id => p_template_id,
1486 p_event_type => 'XPD',
1487 p_write_to => 'TRUE',
1488 x_return_status => x_return_status,
1489 x_errorcode => l_errorcode,
1490 x_msg_count => l_msg_count,
1491 x_msg_data => l_msg_data
1492 );
1493
1494 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
1495 debug(d_module, d_position, 'An exception was encountered in PO_UDA_DEFAULTING_PKG.UDA_EXECUTE_ACTIONS'||
1496 '(with p_event_type => XPD): ' || l_msg_data, FND_API.G_TRUE);
1497 RAISE FND_API.g_exc_unexpected_error;
1498 END IF;
1499
1500 d_position := 80;
1501 IF (PO_LOG.d_proc) THEN
1502 PO_LOG.proc_end(d_module);
1503 END IF;
1504
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 PO_MESSAGE_S.add_exc_msg(
1508 p_pkg_name => d_pkg_name,
1509 p_procedure_name => d_api_name || '.' || d_position,
1510 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
1511 );
1512 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
1513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1514 END merge_multi_mod_addresses;
1515
1516 -------------------------------------------------------------------------------
1517 --Start of Comments
1518 --Name: fetch_supplier_comm_defaults
1519 --Pre-reqs:
1520 -- None.
1521 --Modifies:
1522 -- None.
1523 --Locks:
1524 -- None.
1525 --Function:
1526 -- Fetch Supplier Communication method defaults
1527 --Parameters:
1528 --IN:
1529 --p_vendor_id
1530 -- The new VendorId from the Multi-Mod (For Novation)
1531 --p_vendor_site_id
1532 -- The new VendorSiteId from the Multi-Mod (For Novation)
1533 --p_vendor_contact_id
1534 -- The new VendorContactId from the Multi-Mod (For Novation)
1535 --p_doc_sub_type
1536 -- Document Sub-type
1537 --OUT:
1538 --x_default_method
1539 -- default supplier notif method
1540 --x_email_address
1541 -- Communication e-mail address
1542 --x_fax_number
1543 -- Communication Fax Number
1544 --x_xml_flag
1545 -- XML Flag
1546 --x_edi_flag
1547 -- EDI Flag
1548 --x_return_status
1549 -- FND_API.G_RET_STS_SUCCESS if API succeeds
1550 -- FND_API.G_RET_STS_ERROR if API fails
1551 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1552 --End of Comments
1553 -------------------------------------------------------------------------------
1554
1555 PROCEDURE fetch_supplier_comm_defaults(p_vendor_id IN NUMBER,
1556 p_vendor_site_id IN NUMBER,
1557 p_vendor_contact_id IN NUMBER,
1558 p_doc_sub_type IN VARCHAR2,
1559 x_default_method OUT NOCOPY VARCHAR2,
1560 x_email_address OUT NOCOPY VARCHAR2,
1561 x_fax_number OUT NOCOPY VARCHAR2,
1562 x_xml_flag OUT NOCOPY VARCHAR2,
1563 x_edi_flag OUT NOCOPY VARCHAR2,
1564 x_return_status OUT NOCOPY VARCHAR2)
1565 IS
1566
1567 d_api_name CONSTANT VARCHAR2(30) := 'FETCH_SUPPLIER_COMM_DEFAULTS';
1568 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1569 d_position NUMBER;
1570
1571 l_fax_area po_vendor_sites_all.FAX_AREA_CODE%type;
1572 l_faxnum varchar2(30);
1573 l_po_comm_email_default VARCHAR2(10);
1574
1575
1576 l_retcode pls_integer;
1577 l_errmsg varchar2(2000);
1578 l_result boolean := FALSE;
1579
1580 BEGIN
1581 d_position := 0;
1582 IF (PO_LOG.d_proc) THEN
1583 PO_LOG.proc_begin(d_module,'p_vendor_id',p_vendor_id);
1584 PO_LOG.proc_begin(d_module,'p_vendor_site_id',p_vendor_site_id);
1585 PO_LOG.proc_begin(d_module,'p_vendor_contact_id',p_vendor_contact_id);
1586 END IF;
1587
1588 -- Query setup to get the XML_FLAG default
1589 -- XML delivery only applies to Standard POs and Blanket Releases
1590 IF (p_doc_sub_type = 'STANDARD') THEN
1591
1592 d_position := 10;
1593 debug(d_module, d_position, 'Query setup to get the XML_FLAG default');
1594
1595 BEGIN
1596 ecx_document.isDeliveryRequired(
1597 transaction_type => 'PO',
1598 transaction_subtype => 'PRO',
1599 party_id => p_vendor_id,
1600 party_site_id => p_vendor_site_id,
1601 resultout => l_result,
1602 retcode => l_retcode,
1603 errmsg => l_errmsg
1604 );
1605
1606 IF (l_result) THEN
1607 x_xml_flag := 'Y';
1608 ELSE
1609 x_xml_flag := 'N';
1610 END IF;
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 x_xml_flag := 'N';
1614 END;
1615 END IF;-- IF (l_doc_subtype = 'STANDARD')
1616
1617 -- Query setup to get the EDI Flag
1618 d_position := 20;
1619 debug(d_module, d_position, 'Query setup to get the EDI_FLAG default');
1620
1621 BEGIN
1622 SELECT etd.edi_flag
1623 INTO x_edi_flag
1624 FROM ece_tp_details etd,
1625 po_vendor_sites_all pvs
1626 WHERE pvs.tp_header_id = etd.tp_header_id
1627 AND etd.document_id = 'POO'
1628 AND pvs.vendor_site_id = p_vendor_site_id
1629 AND etd.document_type = p_doc_sub_type;
1630 EXCEPTION
1631 WHEN OTHERS THEN
1632 x_edi_flag := 'N';
1633 END;
1634
1635 -- Fetch Site-level defaults for Supplier Communication attributes
1636 d_position := 30;
1637 debug(d_module, d_position, 'Fetch Site-level defaults for Supplier Communication attributes');
1638
1639 SELECT supplier_notif_method,
1640 email_address,
1641 fax_area_code,
1642 fax
1643 INTO x_default_method,
1644 x_email_address,
1645 l_fax_area,
1646 l_faxnum
1647 FROM po_vendor_sites_all
1648 WHERE vendor_site_id = p_vendor_site_id;
1649
1650 -- Check Contact-level defaults only if Contact exists
1651 IF p_vendor_contact_id IS NOT NULL THEN
1652
1653 d_position := 40;
1654 debug(d_module, d_position, 'Fetch Contact-level defaults');
1655
1656 FND_PROFILE.get('PO_COMM_EMAIL_DEFAULT', l_po_comm_email_default);
1657
1658 SELECT DECODE(l_po_comm_email_default,
1659 'SITE', x_email_address,
1660 NVL(email_address, x_email_address)),
1661 Nvl(fax_area_code, l_fax_area),
1662 Nvl(fax, l_faxnum)
1663 INTO x_email_address,
1664 l_fax_area,
1665 l_faxnum
1666 FROM po_vendor_contacts
1667 WHERE vendor_contact_id = p_vendor_contact_id
1668 AND vendor_site_id = p_vendor_site_id;
1669
1670 END IF;
1671
1672 IF (l_faxnum is not null) then
1673 x_fax_number := l_fax_area || l_faxnum ;
1674 END IF;
1675
1676 -- Clear SupplierNotifMethod for the XML/EDI cases.
1677 -- For HTML Orders, store the method as NONE instead of null
1678 IF (x_default_method IS NULL OR x_xml_flag = 'Y' OR x_edi_flag = 'Y') THEN
1679 x_default_method := 'NONE';
1680 END IF;
1681 -- End Default Supplier Communication Method
1682
1683 x_return_status := FND_API.G_RET_STS_SUCCESS;
1684
1685 d_position := 50;
1686 IF (PO_LOG.d_proc) THEN
1687 PO_LOG.proc_end(d_module);
1688 END IF;
1689
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 PO_MESSAGE_S.add_exc_msg(
1693 p_pkg_name => d_pkg_name,
1694 p_procedure_name => d_api_name || '.' || d_position,
1695 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
1696 );
1697 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
1698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1699 END fetch_supplier_comm_defaults;
1700
1701 -------------------------------------------------------------------------------
1702 --Start of Comments
1703 --Name: merge_supplier_uda
1704 --Pre-reqs:
1705 -- None.
1706 --Modifies:
1707 -- None.
1708 --Locks:
1709 -- None.
1710 --Function:
1711 -- Merge the Supplier-related UDA Attrs based on the New Supplier
1712 --Parameters:
1713 --IN:
1714 --p_multi_mod_request_type
1715 -- The Request Type of the Multi-Mod. Possible values: VENDOR_CHANGE or VENDOR_NAME_CHANGE
1716 --p_po_header_id
1717 -- The header id of the new Modification
1718 --p_draft_id
1719 -- The draft id of the new Modification
1720 --p_template_id
1721 -- UDA Template Id
1722 --p_vendor_id
1723 -- The new VendorId from the Multi-Mod (For Novation)
1724 --p_vendor_site_id
1725 -- The new VendorSiteId from the Multi-Mod (For Novation)
1726 --p_vendor_contact_id
1727 -- The new VendorContactId from the Multi-Mod (For Novation)
1728 --p_remit_to_addr
1729 -- The new RemitToAddr from the Multi-Mod (For Novation)
1730 --p_org_id
1731 -- The OrgId of the new Modification
1732 --OUT:
1733 --x_return_status
1734 -- FND_API.G_RET_STS_SUCCESS if API succeeds
1735 -- FND_API.G_RET_STS_ERROR if API fails
1736 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1737 --End of Comments
1738 -------------------------------------------------------------------------------
1739 PROCEDURE merge_supplier_uda(p_multi_mod_request_type IN VARCHAR2,
1740 p_po_header_id IN NUMBER,
1741 p_draft_id IN NUMBER,
1742 p_template_id IN NUMBER,
1743 p_vendor_id IN NUMBER DEFAULT NULL,
1744 p_vendor_site_id IN NUMBER DEFAULT NULL,
1745 p_vendor_contact_id IN NUMBER DEFAULT NULL,
1746 p_remit_to_addr IN NUMBER DEFAULT NULL,
1747 p_org_id IN NUMBER DEFAULT NULL,
1748 x_return_status OUT NOCOPY VARCHAR2)
1749 IS
1750
1751 d_api_name CONSTANT VARCHAR2(30) := 'MERGE_SUPPLIER_UDA';
1752 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
1753 d_position NUMBER;
1754
1755 d_called_api VARCHAR2(100);
1756
1757 l_change_type_vendor_name CONSTANT VARCHAR2(30) := 'VENDOR_NAME_CHANGE';
1758 l_change_type_vendor CONSTANT VARCHAR2(30) := 'VENDOR_CHANGE';
1759
1760 l_entity_code CONSTANT VARCHAR2(200) := 'PO_HEADER_EXT_ATTRS';
1761 l_data_level CONSTANT VARCHAR2(200) := 'PO_HEADER';
1762 l_supplier_attr_grp CONSTANT VARCHAR2(200) := 'SUPPLIER_DTLS';
1763
1764 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1765
1766 l_sup_attr_group_id NUMBER;
1767 l_attr_list VARCHAR2(2000);
1768
1769 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
1770
1771 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
1772 l_attr_name_value_pairs EGO_USER_ATTR_DATA_TABLE;
1773 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1774
1775 l_ext_attr_value_pairs_ind NUMBER;
1776 l_ext_name_val_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
1777
1778 l_external_attr_value_pairs EGO_COL_NAME_VALUE_PAIR_TABLE;
1779
1780 l_base_object_name VARCHAR2(200);
1781 l_failed_row_id_list VARCHAR2(2000);
1782
1783 l_errorcode NUMBER;
1784 l_msg_count NUMBER;
1785 l_msg_data VARCHAR2(2000);
1786
1787 CURSOR c_po_attr_list IS
1788 SELECT end_user_column_name
1789 FROM fnd_descr_flex_column_usages
1790 WHERE descriptive_flexfield_name = l_entity_code
1791 AND descriptive_flex_context_code = l_supplier_attr_grp;
1792
1793 BEGIN
1794
1795 d_position := 0;
1796 IF (PO_LOG.d_proc) THEN
1797 PO_LOG.proc_begin(d_module,'p_multi_mod_request_type',p_multi_mod_request_type);
1798 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
1799 PO_LOG.proc_begin(d_module,'p_draft_id',p_draft_id);
1800 END IF;
1801
1802 -- Initialize l_attr_group_request_table
1803 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE();
1804
1805 -- Get the attribute Group Id against Supplier Attr Grp
1806 SELECT attr_group_id
1807 INTO l_sup_attr_group_id
1808 FROM ego_attr_groups_v
1809 WHERE attr_group_type = l_entity_code
1810 AND attr_group_name = l_supplier_attr_grp;
1811
1812 d_position := 10;
1813 debug(d_module, d_position, 'After fetching Supplier AG Id: '||l_sup_attr_group_id);
1814
1815 d_position := 20;
1816 debug(d_module, d_position, 'Building the l_attr_list');
1817
1818 -- Fetch the list of Attributes for the Supplier AG
1819 FOR c_po_attr_list_rec IN c_po_attr_list LOOP
1820 IF l_attr_list IS NULL OR l_attr_list = '' THEN
1821 l_attr_list := c_po_attr_list_rec.end_user_column_name;
1822 ELSE
1823 l_attr_list := l_attr_list || ',' || c_po_attr_list_rec.end_user_column_name;
1824 END IF;
1825 END LOOP;
1826
1827 d_position := 30;
1828 debug(d_module, d_position, 'After building the l_attr_list : '||l_attr_list);
1829
1830 IF l_attr_list IS NOT NULL THEN
1831 l_attr_group_request_table.EXTEND(1);
1832 l_attr_group_request_table(l_attr_group_request_table.COUNT) := ego_attr_group_request_obj(
1833 l_sup_attr_group_id,
1834 201,
1835 l_entity_code,
1836 l_supplier_attr_grp,
1837 l_data_level,
1838 NULL,
1839 NULL,
1840 NULL,
1841 NULL,
1842 NULL,
1843 l_attr_list
1844 );
1845 END IF;
1846
1847 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1848 EGO_COL_NAME_VALUE_PAIR_OBJ('PO_HEADER_ID' , p_po_header_id),
1849 EGO_COL_NAME_VALUE_PAIR_OBJ('DRAFT_ID' , p_draft_id)
1850 );
1851
1852 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1853 EGO_COL_NAME_VALUE_PAIR_OBJ('UDA_TEMPLATE_ID' ,p_template_id || '')
1854 );
1855
1856 d_position := 40;
1857 d_called_api := 'EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data';
1858 debug(d_module, d_position, 'Calling ' || d_called_api || ' to fetch Supplier AG Attributes', FND_API.G_TRUE);
1859
1860 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
1861 p_api_version => 1.0,
1862 p_object_name => PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_code).l_object_name,
1863 p_pk_column_name_value_pairs => l_pk_column_name_value_pairs,
1864 p_attr_group_request_table => l_attr_group_request_table,
1865 x_attributes_row_table => l_attributes_row_table,
1866 x_attributes_data_table => l_attr_name_value_pairs,
1867 x_return_status => x_return_status,
1868 x_errorcode => l_errorcode,
1869 x_msg_count => l_msg_count,
1870 x_msg_data => l_msg_data
1871 );
1872
1873 d_position := 50;
1874 debug(d_module, d_position, 'After Calling EGO_USER_ATTRS_DATA_PVT.get_user_attrs_data ' ||
1875 'Return Status ' || x_return_status);
1876 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1877 IF (p_multi_mod_request_type = l_change_type_vendor_name) THEN
1878 --For Supplier Name change, we re-default the CDI Attrs using XRD
1879 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
1880 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
1881 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
1882 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
1883
1884 d_position := 60;
1885 d_called_api := 'PO_UDA_IMPORT_PKG.execute_import_uda_function(with p_event => XRD)';
1886 debug(d_module, d_position, 'Calling ' || d_called_api || ' to refresh Supplier CDI Attributes', FND_API.G_TRUE);
1887
1888 -- Call the XRD Function to refresh Supplier CDI Attributes
1889 PO_UDA_IMPORT_PKG.execute_import_uda_function(
1890 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).Value,
1891 p_event => 'XRD',
1892 x_external_attr_value_pairs => l_external_attr_value_pairs,
1893 x_pk_column_name_value_pairs => l_pk_column_name_value_pairs,
1894 x_attr_name_value_pairs => l_attr_name_value_pairs,
1895 x_attributes_row_table => l_attributes_row_table,
1896 x_return_status => x_return_status,
1897 x_errorcode => l_errorcode,
1898 x_msg_count => l_msg_count,
1899 x_msg_data => l_msg_data
1900 );
1901
1902 d_position := 70;
1903 debug(d_module, d_position, 'After Calling PO_UDA_IMPORT_PKG.execute_import_uda_function ' ||
1904 'for XRD Function Return Status ' || x_return_status);
1905
1906 ELSIF (p_multi_mod_request_type = l_change_type_vendor) THEN
1907 --For Supplier Name change, we re-default the CDI Attrs using XD and then XLE(for RemitToAddr)
1908 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_return_status', FND_API.G_RET_STS_SUCCESS, l_external_attr_value_pairs);
1909 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_errorcode', '0', l_external_attr_value_pairs);
1910 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_count', '0', l_external_attr_value_pairs);
1911 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('x_msg_data', 'SUCCESS', l_external_attr_value_pairs);
1912 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('VENDOR_CONTACT_ID', p_vendor_contact_id || '', l_external_attr_value_pairs);
1913 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('VENDOR_SITE_ID', p_vendor_site_id || '', l_external_attr_value_pairs);
1914 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('VENDOR_ID', p_vendor_id || '', l_external_attr_value_pairs);
1915 PO_UDA_DEFAULTING_PKG.SET_EXT_ATTR_NAME_VALUE_PAIR('ORG_ID', p_org_id || '', l_external_attr_value_pairs);
1916
1917 d_position := 80;
1918 d_called_api := 'PO_UDA_IMPORT_PKG.execute_import_uda_function(with p_event => XD)';
1919 debug(d_module, d_position, 'Calling ' || d_called_api || ' to default Supplier CDI Attributes', FND_API.G_TRUE);
1920
1921 -- Call the XD Function to default Supplier CDI Attributes
1922 PO_UDA_IMPORT_PKG.execute_import_uda_function(
1923 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).Value,
1924 p_event => 'XD',
1925 x_external_attr_value_pairs => l_external_attr_value_pairs,
1926 x_pk_column_name_value_pairs => l_pk_column_name_value_pairs,
1927 x_attr_name_value_pairs => l_attr_name_value_pairs,
1928 x_attributes_row_table => l_attributes_row_table,
1929 x_return_status => x_return_status,
1930 x_errorcode => l_errorcode,
1931 x_msg_count => l_msg_count,
1932 x_msg_data => l_msg_data
1933 );
1934
1935 d_position := 90;
1936 debug(d_module, d_position, 'After Calling PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION ' ||
1937 'for XD Function Return Status ' || x_return_status);
1938
1939 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1940
1941 -- Update the RemitToAddr in the Supplier AG with the new value in order to re-default the RemitToAddr CDI Attributes
1942 FOR i in 1..l_attr_name_value_pairs.count LOOP
1943 IF l_attr_name_value_pairs(i).ATTR_NAME = 'REMIT_TO_ADDRS' THEN
1944 l_attr_name_value_pairs(i).ATTR_VALUE_NUM := p_remit_to_addr;
1945 l_attr_name_value_pairs(i).ATTR_DISP_VALUE := NULL;
1946 END IF;
1947 END LOOP;
1948
1949 d_position := 100;
1950 d_called_api := 'PO_UDA_IMPORT_PKG.execute_import_uda_function(with p_event => XLE)';
1951 debug(d_module, d_position, 'Calling ' || d_called_api || ' to default RemitToAddress dependent Attrs', FND_API.G_TRUE);
1952
1953 -- Call the XLE Function to default Supplier RemitToAddress dependent Attributes
1954 PO_UDA_IMPORT_PKG.execute_import_uda_function(
1955 p_template_id => l_class_code_name_value_pairs(l_class_code_name_value_pairs.FIRST).Value,
1956 p_event => 'XLE',
1957 x_external_attr_value_pairs => l_external_attr_value_pairs,
1958 x_pk_column_name_value_pairs => l_pk_column_name_value_pairs,
1959 x_attr_name_value_pairs => l_attr_name_value_pairs,
1960 x_attributes_row_table => l_attributes_row_table,
1961 x_return_status => x_return_status,
1962 x_errorcode => l_errorcode,
1963 x_msg_count => l_msg_count,
1964 x_msg_data => l_msg_data
1965 );
1966
1967 d_position := 110;
1968 debug(d_module, d_position, 'After Calling PO_UDA_IMPORT_PKG.EXECUTE_IMPORT_UDA_FUNCTION ' ||
1969 'for XLE Function Return Status ' || x_return_status);
1970
1971 END IF;-- IF x_return_status = FND_API.G_RET_STS_SUCCESS
1972 END IF;--IF(p_multi_mod_request_type = d_change_type_vendor_name)
1973
1974 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1975 --Need to populate the UDA data back (Sync the new defaults with existing)
1976 l_base_object_name := PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_code).l_object_name;
1977
1978 d_position := 120;
1979 d_called_api := 'EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data';
1980 debug(d_module, d_position, 'Calling ' || d_called_api || ' to populate the updated Supplier Attrs', FND_API.G_TRUE);
1981
1982 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data(
1983 p_api_version => 1.0,
1984 p_object_name => l_base_object_name,
1985 p_attributes_row_table => l_attributes_row_table,
1986 p_attributes_data_table => l_attr_name_value_pairs,
1987 p_pk_column_name_value_pairs => l_pk_column_name_value_pairs,
1988 p_class_code_name_value_pairs => l_class_code_name_value_pairs,
1989 x_failed_row_id_list => l_failed_row_id_list,
1990 x_return_status => x_return_status,
1991 x_errorcode => l_errorcode,
1992 x_msg_count => l_msg_count,
1993 x_msg_data => l_msg_data
1994 );
1995
1996 d_position := 130;
1997 debug(d_module, d_position, 'After Calling EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data ' ||
1998 'for populating Supplier Attrs. Return Status ' || x_return_status);
1999 END IF;
2000 END IF; -- IF x_return_status = FND_API.G_RET_STS_SUCCESS
2001
2002 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2003 debug(d_module, d_position, 'An exception was encountered in ' || d_called_api || ': ' || l_msg_data, FND_API.G_TRUE);
2004 RAISE FND_API.g_exc_unexpected_error;
2005 END IF;
2006
2007 d_position := 140;
2008 IF (PO_LOG.d_proc) THEN
2009 PO_LOG.proc_end(d_module);
2010 END IF;
2011
2012 EXCEPTION
2013 WHEN OTHERS THEN
2014 PO_MESSAGE_S.add_exc_msg(
2015 p_pkg_name => d_pkg_name,
2016 p_procedure_name => d_api_name || '.' || d_position,
2017 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
2018 );
2019 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
2020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2021 END merge_supplier_uda;
2022
2023 -------------------------------------------------------------------------------
2024 --Start of Comments
2025 --Name: merge_multi_mod_supplier
2026 --Pre-reqs:
2027 -- None.
2028 --Modifies:
2029 -- None.
2030 --Locks:
2031 -- None.
2032 --Function:
2033 -- Merge the Supplier-related details from the Multi-Mod request onto the newly created Modification
2034 --Parameters:
2035 --IN:
2036 --p_multi_mod_request_id
2037 -- The Mult-Mod Request Id
2038 --p_multi_mod_request_type
2039 -- The Request Type of the Multi-Mod. Possible values: VENDOR_CHANGE or VENDOR_NAME_CHANGE
2040 --p_po_header_id
2041 -- The header id of the new Modification
2042 --p_draft_id
2043 -- The draft id of the new Modification
2044 --p_template_id
2045 -- UDA Template Id
2046 --p_is_uda_enabled
2047 -- UDA Enabled Flag
2048 --OUT:
2049 --x_return_status
2050 -- FND_API.G_RET_STS_SUCCESS if API succeeds
2051 -- FND_API.G_RET_STS_ERROR if API fails
2052 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2053 --End of Comments
2054 -------------------------------------------------------------------------------
2055 PROCEDURE merge_multi_mod_supplier(p_multi_mod_request_id IN NUMBER,
2056 p_multi_mod_request_type IN VARCHAR2,
2057 p_po_header_id IN NUMBER,
2058 p_draft_id IN NUMBER,
2059 p_template_id IN NUMBER,
2060 p_is_uda_enabled IN VARCHAR2,
2061 x_return_status OUT NOCOPY VARCHAR2)
2062 IS
2063
2064 d_api_name CONSTANT VARCHAR2(30) := 'MERGE_MULTI_MOD_SUPPLIER';
2065 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2066 d_position NUMBER;
2067
2068 l_change_type_vendor_name CONSTANT VARCHAR2(30) := 'VENDOR_NAME_CHANGE';
2069 l_change_type_vendor CONSTANT VARCHAR2(30) := 'VENDOR_CHANGE';
2070
2071 l_org_id NUMBER;
2072 l_doc_subtype po_headers_all.type_lookup_code%TYPE;
2073
2074 l_old_vendor_id NUMBER;
2075 l_old_vendor_site_id NUMBER;
2076 l_old_vendor_contact_id NUMBER;
2077 l_old_remit_to_addr NUMBER;
2078
2079 l_new_vendor_id NUMBER;
2080 l_new_vendor_site_id NUMBER;
2081 l_new_vendor_contact_id NUMBER;
2082 l_new_remit_to_addr NUMBER;
2083
2084 l_new_vendor_name po_headers_all.CLM_SUPPLIER_NAME%TYPE;
2085 l_new_vendor_site_name po_headers_all.CLM_SUPPLIER_SITE_NAME%TYPE;
2086
2087 l_default_method po_headers_all.SUPPLIER_NOTIF_METHOD%TYPE;
2088 l_fax_number po_headers_all.FAX%TYPE;
2089 l_email_address po_headers_all.EMAIL_ADDRESS%TYPE;
2090 l_xml_flag VARCHAR2(1) := 'N';
2091 l_edi_flag VARCHAR2(1) := 'N';
2092
2093 l_retcode pls_integer;
2094 l_errmsg varchar2(2000);
2095 l_result boolean := FALSE;
2096
2097 BEGIN
2098 d_position := 0;
2099 IF (PO_LOG.d_proc) THEN
2100 PO_LOG.proc_begin(d_module,'p_multi_mod_request_id',p_multi_mod_request_id);
2101 PO_LOG.proc_begin(d_module,'p_multi_mod_request_type',p_multi_mod_request_type);
2102 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
2103 PO_LOG.proc_begin(d_module,'p_draft_id',p_draft_id);
2104 END IF;
2105
2106 d_position := 10;
2107 debug(d_module, d_position, 'Check the Request Change Type');
2108
2109 IF (p_multi_mod_request_type = l_change_type_vendor_name) THEN
2110
2111 d_position := 20;
2112 debug(d_module, d_position, 'Request Type is Supplier Name Change.');
2113
2114 IF p_is_uda_enabled = 'Y' THEN
2115 d_position := 30;
2116 debug(d_module, d_position, 'Calling merge_supplier_uda for merging Supplier UDA Attributes', FND_API.G_TRUE);
2117
2118 merge_supplier_uda(
2119 p_multi_mod_request_type => p_multi_mod_request_type,
2120 p_po_header_id => p_po_header_id,
2121 p_draft_id => p_draft_id,
2122 p_template_id => p_template_id,
2123 x_return_status => x_return_status
2124 );
2125 END IF;
2126
2127 ELSIF (p_multi_mod_request_type = l_change_type_vendor) THEN
2128
2129 d_position := 40;
2130 debug(d_module, d_position, 'Request Type is Supplier Change(Novation).');
2131
2132 -- Get existing Mod Vendor Details
2133 SELECT org_id,
2134 type_lookup_code,
2135 vendor_id,
2136 vendor_site_id,
2137 vendor_contact_id
2138 INTO l_org_id,
2139 l_doc_subtype,
2140 l_old_vendor_id,
2141 l_old_vendor_site_id,
2142 l_old_vendor_contact_id
2143 FROM po_headers_draft_all
2144 WHERE po_header_id = p_po_header_id
2145 AND draft_id = p_draft_id;
2146
2147 d_position := 45;
2148 debug(d_module, d_position, 'Fetching the existing Remit To Address on the Document');
2149
2150 -- Get existing RemitToAddress
2151 l_old_remit_to_addr := To_Number(PO_UDA_PUB.get_single_attr_value(
2152 p_template_id,
2153 NULL,
2154 p_po_header_id,
2155 p_draft_id,
2156 NULL,
2157 NULL,
2158 NULL,
2159 NULL,
2160 'SUPPLIER_DTLS',
2161 NULL,
2162 'REMIT_TO_ADDRS',
2163 'INTERNAL_VALUE'
2164 ));
2165
2166
2167 d_position := 50;
2168 debug(d_module, d_position, 'The existing Supplier Details on the Mod are:');
2169 debug(d_module, d_position, 'l_old_vendor_id: '||l_old_vendor_id);
2170 debug(d_module, d_position, 'l_old_vendor_site_id: '||l_old_vendor_site_id);
2171 debug(d_module, d_position, 'l_old_vendor_contact_id: '||l_old_vendor_contact_id);
2172 debug(d_module, d_position, 'l_old_remit_to_addr: '||l_old_remit_to_addr);
2173
2174 --Get the New Suplier Details one by one:
2175 --1. New Supplier Id
2176 SELECT new_vendor_id, new_vendor_name
2177 INTO l_new_vendor_id, l_new_vendor_name
2178 FROM po_multi_mod_changes
2179 WHERE multi_mod_request_id = p_multi_mod_request_id
2180 AND change_type = 'VENDOR'
2181 AND old_vendor_id = l_old_vendor_id;
2182
2183 --2. New Supplier Site and Contact
2184 SELECT new_vendor_site_id, new_vendor_contact_id
2185 INTO l_new_vendor_site_id, l_new_vendor_contact_id
2186 FROM po_multi_mod_changes
2187 WHERE multi_mod_request_id = p_multi_mod_request_id
2188 AND change_type = 'VENDOR_SITE_CONTACT'
2189 AND org_id = l_org_id
2190 AND old_vendor_site_id = l_old_vendor_site_id;
2191
2192 --3. New Remit to Address
2193 BEGIN
2194 SELECT new_remit_to_addr
2195 INTO l_new_remit_to_addr
2196 FROM po_multi_mod_changes
2197 WHERE multi_mod_request_id = p_multi_mod_request_id
2198 AND change_type = 'VENDOR_REMIT_ADDRESS'
2199 AND old_remit_to_addr = l_old_remit_to_addr
2200 AND org_id = l_org_id;
2201 EXCEPTION
2202 WHEN No_Data_Found THEN --No RemitToAddr exists on Base Document
2203 l_new_remit_to_addr := NULL;
2204 WHEN OTHERS THEN
2205 l_new_remit_to_addr := NULL;
2206 END;
2207
2208 d_position := 60;
2209 debug(d_module, d_position, 'The new Supplier Details to be merged into the Mod are:');
2210 debug(d_module, d_position, 'l_new_vendor_id: '||l_new_vendor_id);
2211 debug(d_module, d_position, 'l_new_vendor_site_id: '||l_new_vendor_site_id);
2212 debug(d_module, d_position, 'l_new_vendor_contact_id: '||l_new_vendor_contact_id);
2213 debug(d_module, d_position, 'l_new_remit_to_addr: '||l_new_remit_to_addr);
2214
2215 --Get the default values for Supplier Site/Contact dependent attributes (including the two Denormalized Name Attrs)
2216
2217 --Get the new denormalized Supplier Site name from PO_VENDOR_SITES_ALL and Supplier Name from PO_VENDORS_ALL
2218 SELECT vendor_site_code
2219 INTO l_new_vendor_site_name
2220 FROM po_vendor_sites_all
2221 WHERE vendor_site_id = l_new_vendor_site_id;
2222
2223 d_position := 65;
2224 debug(d_module, d_position, 'Calling fetch_supplier_comm_defaults API to fetch Supplier communication defaults', FND_API.G_TRUE);
2225 -- Get Supplier Communication defaults
2226 fetch_supplier_comm_defaults(
2227 p_vendor_id => l_new_vendor_id,
2228 p_vendor_site_id => l_new_vendor_site_id,
2229 p_vendor_contact_id => l_new_vendor_contact_id,
2230 p_doc_sub_type => l_doc_subtype,
2231 x_default_method => l_default_method,
2232 x_email_address => l_email_address,
2233 x_fax_number => l_fax_number,
2234 x_xml_flag => l_xml_flag,
2235 x_edi_flag => l_edi_flag,
2236 x_return_status => x_return_status
2237 );
2238
2239 d_position := 70;
2240 debug(d_module, d_position, 'The new Supplier Dependent fields to be merged into the Mod are:');
2241 debug(d_module, d_position, 'l_default_method: '||l_default_method);
2242 debug(d_module, d_position, 'l_email_address: '||l_email_address);
2243 debug(d_module, d_position, 'l_fax_number: '||l_fax_number);
2244 debug(d_module, d_position, 'l_new_vendor_name: '||l_new_vendor_name);
2245 debug(d_module, d_position, 'l_new_vendor_site_name: '||l_new_vendor_site_name);
2246
2247 d_position := 80;
2248 debug(d_module, d_position, 'Updating the Mod with new Supplier details', FND_API.G_TRUE);
2249
2250 UPDATE po_headers_draft_all
2251 SET vendor_id = l_new_vendor_id,
2252 vendor_site_id = l_new_vendor_site_id,
2253 vendor_contact_id = l_new_vendor_contact_id,
2254 --clm_supplier_name = l_new_vendor_name,
2255 --clm_supplier_site_name = l_new_vendor_site_name,
2256 fax = l_fax_number,
2257 email_address = l_email_address,
2258 supplier_notif_method = l_default_method,
2259 xml_flag = l_xml_flag,
2260 edi_processed_flag = Decode(l_xml_flag, 'Y', 'N', NULL)
2261 WHERE po_header_id = p_po_header_id
2262 AND draft_id = p_draft_id;
2263
2264 --Sync the Org Assignment Sites with the new Site Details for Agreements
2265 IF (l_doc_subtype in ('BLANKET', 'CONTRACT')) THEN
2266 d_position := 90;
2267 debug(d_module, d_position, 'Syncing the Org Assignment Sites with the new Site Details', FND_API.G_TRUE);
2268
2269 INSERT INTO po_ga_org_assign_draft(
2270 draft_id,
2271 delete_flag,
2272 change_accepted_flag,
2273 change_status,
2274 po_header_id,
2275 organization_id,
2276 enabled_flag,
2277 vendor_site_id,
2278 purchasing_org_id,
2279 org_assignment_id,
2280 last_update_date,
2281 last_updated_by,
2282 last_update_login,
2283 creation_date,
2284 created_by)
2285 SELECT
2286 p_draft_id,
2287 NULL, -- delete_flag,
2288 NULL, -- change_accepted_flag,
2289 'UPDATE',
2290 PGOA.po_header_id,
2291 PGOA.organization_id,
2292 PGOA.enabled_flag,
2293 PMMC.new_vendor_site_id,
2294 PGOA.purchasing_org_id,
2295 PGOA.org_assignment_id,
2296 SYSDATE,
2297 FND_GLOBAL.user_id,
2298 FND_GLOBAL.login_id,
2299 SYSDATE,
2300 FND_GLOBAL.user_id
2301 FROM po_ga_org_assignments PGOA,
2302 po_multi_mod_changes PMMC
2303 WHERE PGOA.po_header_id = p_po_header_id
2304 AND PMMC.org_id = PGOA.purchasing_org_id
2305 AND PMMC.old_vendor_site_id = PGOA.vendor_site_id
2306 AND PMMC.change_type = 'VENDOR_SITE_CONTACT'
2307 AND PMMC.multi_mod_request_id = p_multi_mod_request_id;
2308
2309 END IF;
2310
2311 --Merge RemitToAddress and other Supplier-related UDA Attributes
2312 IF p_is_uda_enabled = 'Y' THEN --Do the UDA related processing for the Mod if UDA Enabled
2313 d_position := 100;
2314 debug(d_module, d_position, 'Calling merge_supplier_uda for merging Supplier UDA Attributes', FND_API.G_TRUE);
2315
2316 merge_supplier_uda(
2317 p_multi_mod_request_type => p_multi_mod_request_type,
2318 p_po_header_id => p_po_header_id,
2319 p_draft_id => p_draft_id,
2320 p_template_id => p_template_id,
2321 p_vendor_id => l_new_vendor_id,
2322 p_vendor_site_id => l_new_vendor_site_id,
2323 p_vendor_contact_id => l_new_vendor_contact_id,
2324 p_remit_to_addr => l_new_remit_to_addr,
2325 p_org_id => l_org_id,
2326 x_return_status => x_return_status
2327 );
2328
2329 END IF;
2330
2331 END IF;--IF(p_multi_mod_request_type = d_change_type_vendor_name)
2332
2333 x_return_status := FND_API.G_RET_STS_SUCCESS;
2334
2335 d_position := 110;
2336 IF (PO_LOG.d_proc) THEN
2337 PO_LOG.proc_end(d_module);
2338 END IF;
2339
2340 EXCEPTION
2341 WHEN OTHERS THEN
2342 PO_MESSAGE_S.add_exc_msg(
2343 p_pkg_name => d_pkg_name,
2344 p_procedure_name => d_api_name || '.' || d_position,
2345 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
2346 );
2347 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
2348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2349 END merge_multi_mod_supplier;
2350
2351 -------------------------------------------------------------------------------
2352 --Start of Comments
2353 --Name: start_mod_approval
2354 --Pre-reqs:
2355 -- None.
2356 --Modifies:
2357 -- None.
2358 --Locks:
2359 -- None.
2360 --Function:
2361 -- 1. Starts the approval workflow for the current document in background mode
2362 -- by calling PO_REQAPPROVAL_INIT1.Start_WF_Process
2363 --Parameters:
2364 --IN:
2365 --p_multi_mod_request_id
2366 -- The Multi-Mod Request Id
2367 --p_po_header_id
2368 -- The header id of the Order/Agreement for which the Mod has been created
2369 --p_draft_id
2370 -- The draft Id of the newly created Modification
2371 --OUT:
2372 --x_return_status
2373 -- FND_API.G_RET_STS_SUCCESS if API succeeds
2374 -- FND_API.G_RET_STS_ERROR if API fails
2375 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2376 --End of Comments
2377 -------------------------------------------------------------------------------
2378 PROCEDURE start_mod_approval(p_multi_mod_request_id IN NUMBER,
2379 p_po_header_id IN NUMBER,
2380 p_draft_id IN NUMBER,
2381 x_return_status OUT NOCOPY VARCHAR2)
2382 IS
2383
2384 d_api_name CONSTANT VARCHAR2(30) := 'START_MOD_APPROVAL';
2385 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2386 d_position NUMBER;
2387
2388 l_headers_draft_rec po_headers_draft_all%ROWTYPE;
2389 l_change_type_vendor CONSTANT VARCHAR2(30) := 'VENDOR_CHANGE';
2390
2391 l_doc_type_code VARCHAR2(30);
2392 l_print_flag VARCHAR2(1) := 'N';
2393 l_fax_flag VARCHAR2(1) := 'N';
2394 l_email_flag VARCHAR2(1) := 'N';
2395 l_org_assign_change_flag VARCHAR2(1) := 'N';
2396 l_communicate_price_change varchar2(1);
2397
2398 BEGIN
2399
2400 d_position := 0;
2401 IF (PO_LOG.d_proc) THEN
2402 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
2403 PO_LOG.proc_begin(d_module,'p_draft_id',p_draft_id);
2404 END IF;
2405
2406 d_position := 10;
2407 debug(d_module, d_position, 'Fetch Headers Draft Row for the Mod');
2408
2409 SELECT *
2410 INTO l_headers_draft_rec
2411 FROM po_headers_draft_all
2412 WHERE po_header_id = p_po_header_id
2413 AND draft_id = p_draft_id;
2414
2415 -- Derive Document Type Code
2416 IF (l_headers_draft_rec.type_lookup_code = 'STANDARD') THEN
2417 l_doc_type_code := 'PO';
2418 ELSE
2419 l_doc_type_code := 'PA';
2420 END IF;
2421
2422 -- Derive Print/Fax/Email Flag
2423 IF (l_headers_draft_rec.supplier_notif_method = 'PRINT') THEN
2424 l_print_flag := 'Y';
2425 ELSIF (l_headers_draft_rec.supplier_notif_method = 'FAX') THEN
2426 l_fax_flag := 'Y';
2427 ELSIF (l_headers_draft_rec.supplier_notif_method = 'EMAIL') THEN
2428 l_email_flag := 'Y';
2429 END IF;
2430
2431 -- Derive OrgAssignChange
2432 -- The OrgAssignChange flag is 'Y' if the request Type is Vendor Change and for Agreements
2433 IF (l_doc_type_code = 'PA') THEN
2434 SELECT Decode(multi_mod_request_type, l_change_type_vendor, 'Y', 'N')
2435 INTO l_org_assign_change_flag
2436 FROM po_multi_mod_requests
2437 WHERE multi_mod_request_id = p_multi_mod_request_id;
2438 END IF;
2439 -- Derive CommunicatePriceChange
2440 IF (l_doc_type_code = 'PO') THEN
2441 l_communicate_price_change := 'Y';
2442 ELSE
2443 l_communicate_price_change := l_headers_draft_rec.retro_price_apply_updates_flag;
2444 END IF;
2445
2446 d_position := 20;
2447 debug(d_module, d_position, 'Calling PO_REQAPPROVAL_INIT1.Start_WF_Process', FND_API.G_TRUE);
2448
2449 PO_REQAPPROVAL_INIT1.Start_WF_Process(
2450 ItemType => NULL,
2451 ItemKey => NULL,
2452 WorkflowProcess => NULL,
2453 ActionOriginatedFrom => 'MULTI_MOD',
2454 DocumentID => p_po_header_id,
2455 DocumentNumber => l_headers_draft_rec.segment1,
2456 PreparerID => FND_GLOBAL.employee_id,
2457 DocumentTypeCode => l_doc_type_code,
2458 DocumentSubtype => l_headers_draft_rec.type_lookup_code,
2459 SubmitterAction => 'APPROVE',
2460 forwardToID => NULL,
2461 forwardFromID => NULL,
2462 DefaultApprovalPathID => NULL,
2463 Note => NULL,
2464 PrintFlag => l_print_flag,
2465 FaxFlag => l_fax_flag,
2466 FAXNumber => l_headers_draft_rec.fax,
2467 EmailFlag => l_email_flag,
2468 EmailAddress => l_headers_draft_rec.email_address,
2469 CreateSourcingRule => l_headers_draft_rec.auto_sourcing_flag,
2470 ReleaseGenMethod => NULL,
2471 UpdateSourcingRule => l_headers_draft_rec.update_sourcing_rules_flag,
2472 MassUpdateReleases => l_headers_draft_rec.retro_price_apply_updates_flag,
2473 RetroactivePriceChange => 'N',
2474 OrgAssignChange => l_org_assign_change_flag,
2475 CommunicatePriceChange => l_communicate_price_change,
2476 p_Background_Flag => 'N',
2477 p_Initiator => NULL,
2478 p_xml_flag => l_headers_draft_rec.xml_flag,
2479 FpdsngFlag => 'N',
2480 p_source_type_code => NULL,
2481 DraftID => p_draft_id,
2482 p_bypass_checks_flag => 'N' --hard-coded in case of Multi-Mod
2483 );
2484
2485 d_position := 30;
2486 debug(d_module, d_position, 'After the call to PO_REQAPPROVAL_INIT1.Start_WF_Process', FND_API.G_TRUE);
2487
2488 x_return_status := FND_API.G_RET_STS_SUCCESS;
2489
2490 d_position := 40;
2491 IF (PO_LOG.d_proc) THEN
2492 PO_LOG.proc_end(d_module);
2493 END IF;
2494
2495 EXCEPTION
2496 WHEN OTHERS THEN
2497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2498 PO_MESSAGE_S.add_exc_msg(
2499 p_pkg_name => d_pkg_name,
2500 p_procedure_name => d_api_name || '.' || d_position,
2501 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
2502 );
2503 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
2504 END start_mod_approval;
2505
2506
2507 -------------------------------------------------------------------------------
2508 --Start of Comments
2509 --Name: create_modification
2510 --Pre-reqs:
2511 -- None.
2512 --Modifies:
2513 -- None.
2514 --Locks:
2515 -- None.
2516 --Function:
2517 -- Create Modification as part of the Multi-Mod Concurrent Request.
2518 --Parameters:
2519 --IN:
2520 --p_multi_mod_request_id
2521 -- The request id of the Multi-Mod Request
2522 --p_po_header_id
2523 -- The id of the Order/Agreement for which the Mod needs to be created
2524 --p_addr_change_flag
2525 -- Flag will indicate if there are any address changes in the Multi-Mod request
2526 --p_clause_change_flag
2527 -- Flag will indicate if there are any clause changes in the Multi-Mod request
2528 --OUT:
2529 --x_draft_id
2530 -- The draft Id of the newly created Modification
2531 --x_mod_number
2532 -- The Modification Number of the newly created Modification
2533 --x_mod_creation_flag
2534 -- Flag indicate whether there are any relevant changes that would trigger
2535 -- the need for Mod-creation.
2536 --x_return_status
2537 -- FND_API.G_RET_STS_SUCCESS if API succeeds
2538 -- FND_API.G_RET_STS_ERROR if API fails
2539 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs.
2540 --End of Comments
2541 -------------------------------------------------------------------------------
2542 PROCEDURE create_modification(p_multi_mod_request_id IN NUMBER,
2543 p_po_header_id IN NUMBER,
2544 p_addr_change_flag IN VARCHAR2,
2545 p_clause_change_flag IN VARCHAR2,
2546 x_draft_id OUT NOCOPY NUMBER,
2547 x_mod_number OUT NOCOPY VARCHAR2,
2548 x_mod_creation_flag OUT NOCOPY VARCHAR2,
2549 x_return_status OUT NOCOPY VARCHAR2,
2550 x_error_msg_code OUT NOCOPY VARCHAR2)
2551 IS
2552
2553 d_api_name CONSTANT VARCHAR2(30) := 'CREATE_MODIFICATION';
2554 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
2555 d_position NUMBER;
2556
2557 l_change_type_multi_mod CONSTANT VARCHAR2(30) := 'MULTI_MOD';
2558
2559 l_draft_id NUMBER;
2560 l_template_id NUMBER;
2561 l_style_id NUMBER;
2562 l_doc_subtype PO_HEADERS_ALL.type_lookup_code%TYPE;
2563 l_org_id NUMBER;
2564 l_is_uda_enabled VARCHAR2(5);
2565 l_clause_updated_yn VARCHAR2(1);
2566 l_conc_mod_enabled VARCHAR2(1) ;
2567
2568 l_multi_mod_request_record po_multi_mod_requests%ROWTYPE;
2569 l_fpds_reporting VARCHAR2(25);
2570
2571 l_entity_names_tbl PO_TBL_VARCHAR30;
2572 l_entity_pk1 PO_TBL_VARCHAR240;
2573 l_entity_pk2 PO_TBL_VARCHAR240;
2574 l_edited_entity_pk2 PO_TBL_VARCHAR240;
2575 l_entity_pk3 PO_TBL_VARCHAR240;
2576 l_entity_pk4 PO_TBL_VARCHAR240;
2577 l_sum_check_tbl PO_TBL_NUMBER;
2578 l_conc_mods_enabled_flag VARCHAR2(1);
2579 l_cntr NUMBER;
2580 x_results PO_VALIDATION_RESULTS_TYPE;
2581 l_addr_attr_group_id NUMBER;
2582 l_mod_exists VARCHAR2(1);
2583 l_pk2_cntr NUMBER := 1;
2584
2585 mod_already_exists_exception EXCEPTION;
2586 BEGIN
2587 d_position := 0;
2588 IF (PO_LOG.d_proc) THEN
2589 PO_LOG.proc_begin(d_module,'p_multi_mod_request_id',p_multi_mod_request_id);
2590 PO_LOG.proc_begin(d_module,'p_po_header_id',p_po_header_id);
2591 PO_LOG.proc_begin(d_module,'p_addr_change_flag',p_addr_change_flag);
2592 PO_LOG.proc_begin(d_module,'p_clause_change_flag',p_clause_change_flag);
2593 END IF;
2594
2595 debug(d_module, d_position, 'Entered create_modification for po_header_id: '|| p_po_header_id, FND_API.G_TRUE);
2596
2597 d_position := 10;
2598 debug(d_module, d_position, 'Fetch record from po_multi_mod_requests');
2599
2600 SELECT *
2601 INTO l_multi_mod_request_record
2602 FROM po_multi_mod_requests
2603 WHERE multi_mod_request_id = p_multi_mod_request_id;
2604
2605 -- Populate Document-level variables required down the flow
2606 SELECT uda_template_id,
2607 style_id,
2608 type_lookup_code,
2609 org_id
2610 INTO l_template_id,
2611 l_style_id,
2612 l_doc_subtype,
2613 l_org_id
2614 FROM po_headers_all
2615 WHERE po_header_id = p_po_header_id;
2616
2617 /*Need to check if conc mod is enabled for this style of document. If not and there exists
2618 a mod already, new mod creation should not be allowed.*/
2619
2620 -- check conc mod enabled for doc style.
2621 SELECT conc_mods_enabled_flag
2622 INTO l_conc_mods_enabled_flag
2623 FROM po_doc_style_headers
2624 WHERE style_id = l_style_id;
2625
2626 IF Nvl(l_conc_mods_enabled_flag, 'N') <> 'Y' THEN
2627 BEGIN
2628 --check if mod already exists.
2629 SELECT 'Y'
2630 INTO l_mod_exists
2631 FROM dual
2632 WHERE EXISTS (SELECT 'Mod Exists'
2633 FROM po_drafts
2634 WHERE document_id = p_po_header_id
2635 AND status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED'));
2636 EXCEPTION
2637 WHEN NO_DATA_FOUND THEN
2638 null;
2639 END;
2640
2641 IF l_mod_exists = 'Y' THEN
2642 --raise excption
2643 RAISE mod_already_exists_exception;
2644 END IF;
2645 END IF;
2646
2647 l_is_uda_enabled := check_uda_enabled(p_po_header_id);
2648 -- End: Populate Document-level variables required down the flow
2649
2650 -- Clear the message stack before processing each document
2651 FND_MSG_PUB.initialize;
2652
2653 d_position := 20;
2654 debug(d_module, d_position, 'Create draft record for Mod', FND_API.G_TRUE);
2655
2656 create_draft_from_txn(
2657 p_po_header_id => p_po_header_id,
2658 x_draft_id => l_draft_id,
2659 x_return_status => x_return_status
2660 );
2661
2662 d_position := 30;
2663 debug(d_module, d_position, 'Default Mod fields and merge fields from the Multi-Mod request', FND_API.G_TRUE);
2664
2665 default_and_merge_mod_data(
2666 p_multi_mod_request_record => l_multi_mod_request_record,
2667 p_po_header_id => p_po_header_id,
2668 p_draft_id => l_draft_id,
2669 p_style_id => l_style_id,
2670 p_doc_subtype => l_doc_subtype,
2671 p_org_id => l_org_id,
2672 p_template_id => l_template_id,
2673 p_is_uda_enabled => l_is_uda_enabled,
2674 x_mod_number => x_mod_number,
2675 x_return_status => x_return_status
2676 );
2677
2678 d_position := 40;
2679 debug(d_module, d_position, 'Update Multi-Mod documents in po_multi_mod_docs with the new draft_id: ' || l_draft_id);
2680 --Also update the who columns
2681 UPDATE po_multi_mod_docs
2682 SET draft_id = l_draft_id,
2683 last_update_date = sysdate,
2684 last_updated_by = FND_GLOBAL.user_id,
2685 last_update_login = FND_GLOBAL.login_id
2686 WHERE multi_mod_request_id = p_multi_mod_request_id
2687 AND document_id = p_po_header_id;
2688
2689 --Merge the Multi-Mod Addresses
2690 d_position := 50;
2691 debug(d_module, d_position, 'Merge Multi-Mod Address UDA Attributes to the Mod', FND_API.G_TRUE);
2692
2693 merge_multi_mod_addresses(
2694 p_uda_key_po_header_id => l_multi_mod_request_record.uda_key_po_header_id,
2695 p_uda_key_draft_id => l_multi_mod_request_record.uda_key_draft_id,
2696 p_po_header_id => p_po_header_id,
2697 p_draft_id => l_draft_id,
2698 p_template_id => l_template_id,
2699 p_is_uda_enabled => l_is_uda_enabled,
2700 p_addr_change_flag => p_addr_change_flag,
2701 x_return_status => x_return_status
2702 );
2703
2704 --Based on the Multi-Mod Request Type, merge the Supplier Name or Supplier
2705 IF(l_multi_mod_request_record.multi_mod_request_type <> l_change_type_multi_mod) THEN
2706
2707 d_position := 60;
2708 debug(d_module, d_position, 'Merge Supplier Details to the Mod', FND_API.G_TRUE);
2709
2710 merge_multi_mod_supplier(
2711 p_multi_mod_request_id => l_multi_mod_request_record.multi_mod_request_id,
2712 p_multi_mod_request_type => l_multi_mod_request_record.multi_mod_request_type,
2713 p_po_header_id => p_po_header_id,
2714 p_draft_id => l_draft_id,
2715 p_template_id => l_template_id,
2716 p_is_uda_enabled => l_is_uda_enabled,
2717 x_return_status => x_return_status
2718 );
2719
2720 END IF;
2721
2722 --check if locked
2723 l_entity_names_tbl := PO_TBL_VARCHAR30('PO_HEADER');
2724 l_entity_pk1 := PO_TBL_VARCHAR240(p_po_header_id);
2725 l_edited_entity_pk2 := PO_TBL_VARCHAR240(null);
2726 l_entity_pk3 := PO_TBL_VARCHAR240(null);
2727 l_entity_pk4 := PO_TBL_VARCHAR240(null);
2728
2729 SELECT attr_group_id
2730 INTO l_addr_attr_group_id
2731 FROM ego_attr_groups_v
2732 WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS'
2733 AND attr_group_name = 'addresses';
2734
2735 SELECT ext.c_ext_attr39,
2736 NVL(SUM(NVL(n_ext_attr1,0) + NVL(n_ext_attr2,0) + NVL(n_ext_attr3,0) +
2737 NVL(n_ext_attr4,0) + NVL(n_ext_attr5,0) + NVL(n_ext_attr6,0) +
2738 NVL(n_ext_attr7,0) + NVL(n_ext_attr8,0) + NVL(n_ext_attr9,0) +
2739 NVL(n_ext_attr10,0) + NVL(n_ext_attr11,0) + NVL(n_ext_attr12,0) +
2740 NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
2741 NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
2742 NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
2743 BULK COLLECT INTO l_entity_pk2, l_sum_check_tbl
2744 FROM po_headers_all_ext_b ext,
2745 po_multi_mod_requests multimod
2746 WHERE ext.po_header_id = multimod.uda_key_po_header_id
2747 AND ext.draft_id = multimod.uda_key_draft_id
2748 AND ext.attr_group_id = l_addr_attr_group_id
2749 AND multimod.multi_mod_request_id = p_multi_mod_request_id
2750 AND ext.c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')
2751 GROUP BY ext.c_ext_attr39;
2752
2753 l_cntr := 1;
2754 FOR i IN 1..l_entity_pk2.Count() LOOP
2755 IF l_sum_check_tbl(i) > 0 THEN
2756 l_cntr := l_cntr + 1;
2757
2758 l_edited_entity_pk2.extend;
2759 l_edited_entity_pk2(l_cntr) := l_entity_pk2(i);
2760
2761 l_entity_names_tbl.extend;
2762 l_entity_names_tbl(l_cntr) := 'PO_UDA_ADDRESS';
2763
2764 l_entity_pk1.extend;
2765 l_entity_pk1(l_cntr) := p_po_header_id;
2766
2767 l_entity_pk3.extend;
2768 l_entity_pk3(l_cntr) := null;
2769
2770 l_entity_pk4.extend;
2771 l_entity_pk4(l_cntr) := null;
2772 END IF;
2773 END LOOP;
2774
2775 PO_DRAFTS_PVT.lock_entities(p_entity_name_tbl => l_entity_names_tbl,
2776 p_draft_id => l_draft_id,
2777 p_pk1_tbl => l_entity_pk1,
2778 p_pk2_tbl => l_edited_entity_pk2,
2779 p_pk3_tbl => l_entity_pk3,
2780 p_pk4_tbl => l_entity_pk4,
2781 x_return_status => x_return_status,
2782 x_results => x_results);
2783
2784 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2785 x_mod_creation_flag := 'N';
2786 x_return_status := fnd_api.g_ret_sts_success;
2787 x_error_msg_code := 'MULTI_MOD_DOCUMENT_LOCKED';
2788 RETURN;
2789 END IF;
2790
2791 -- Merge the Contracts changes if there exists at least one clause change on the Multi-Mod
2792 IF (p_clause_change_flag = 'Y') THEN
2793
2794 --Calling the Contracts API that will merge the Mult-Mod clauses on to the Mod
2795 d_position := 70;
2796 debug(d_module, d_position, 'Calling Contracts API to merge Clauses from Multi-Mod', FND_API.G_TRUE);
2797
2798 select conc_mods_enabled_flag into l_conc_mod_enabled from po_doc_style_headers where style_id = l_style_id;
2799
2800 OKC_PO_MOD_PKG.upd_mulmod_clause_changes(
2801 p_multi_mod_req_id => p_multi_mod_request_id,
2802 p_document_type => PO_CONTERMS_UTL_GRP.Get_Po_Contract_Doctype_Mod(l_doc_subtype),
2803 p_document_id => l_draft_id, --DraftId Of Mod serves As documentId For Contracts
2804 p_org_id => l_org_id,
2805 x_updated_yn => l_clause_updated_yn,
2806 x_return_status => x_return_status,
2807 p_locking_enabled_yn => l_conc_mod_enabled);
2808
2809 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2810 debug(d_module, d_position, 'An exception was encountered in OKC_PO_MOD_PKG.upd_mulmod_clause_changes', FND_API.G_TRUE);
2811 --RAISE FND_API.g_exc_unexpected_error;
2812 END IF;
2813
2814 END IF;
2815
2816 -- If RequestType is MULTIMOD and no Address or Clause changes exist,
2817 -- there is no need to create the Mod
2818 IF (l_multi_mod_request_record.multi_mod_request_type = l_change_type_multi_mod
2819 AND p_addr_change_flag = 'N'
2820 AND (p_clause_change_flag = 'N' OR l_clause_updated_yn = 'N')) THEN
2821
2822 d_position := 80;
2823 debug(d_module, d_position, 'No Address or Clause changes exist for the Mod. Return', FND_API.G_TRUE);
2824
2825 x_mod_creation_flag := 'N';
2826 RETURN;
2827 ELSE
2828 x_mod_creation_flag := 'Y';
2829 END IF;
2830
2831 -- Create records in CAR tables for the Mod
2832 -- CLM Controls Project Changes
2833 l_fpds_reporting := PO_CORE_S.retrieveOptionValue(p_org_id => l_org_id,
2834 p_option_column => PO_CORE_S.g_FPDS_REPORTING_COL);
2835 --nvl(fnd_profile.value('PO_CLM_ENABLE_FPDS_REPORTING'), 'N');
2836
2837 IF(l_fpds_reporting = 'Y') THEN
2838
2839 d_position := 90;
2840 debug(d_module, d_position, 'FPDS-NG Reporting is Enabled. Inserting defaut Car Records', FND_API.G_TRUE);
2841
2842 PO_CLM_CAR_UTIL.insert_mod_default_car(
2843 p_po_header_id => p_po_header_id,
2844 p_draft_id => l_draft_id,
2845 p_invoked_from => 'MULTI_MOD',
2846 p_rel_without_rpt_reason => l_multi_mod_request_record.exemption_reason
2847 );
2848 END IF;
2849
2850 x_draft_id := l_draft_id;
2851 x_return_status := FND_API.G_RET_STS_SUCCESS;
2852 debug(d_module, d_position, 'Exiting create_modification for po_header_id: '|| p_po_header_id ||
2853 ' with draft_id: '||x_draft_id, FND_API.G_TRUE);
2854
2855 d_position := 100;
2856 IF (PO_LOG.d_proc) THEN
2857 PO_LOG.proc_end(d_module,'x_draft_id',x_draft_id);
2858 PO_LOG.proc_end(d_module);
2859 END IF;
2860
2861 EXCEPTION
2862 WHEN mod_already_exists_exception THEN
2863 x_return_status := fnd_api.g_ret_sts_SUCCESS;
2864 x_mod_creation_flag := 'N';
2865
2866 --TODO add ne messafge
2867 x_error_msg_code := 'PO_MOD_ALREADY_EXISTS';
2868
2869 WHEN OTHERS THEN
2870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2871 PO_MESSAGE_S.add_exc_msg(
2872 p_pkg_name => d_pkg_name,
2873 p_procedure_name => d_api_name || '.' || d_position,
2874 p_error_text => 'ERROR : ' || SUBSTRB(SQLERRM,1,200)
2875 );
2876 debug(d_module, d_position, 'ERROR : ' || SUBSTRB(SQLERRM,1,200), FND_API.G_TRUE);
2877 END create_modification;
2878
2879
2880 -------------------------------------------------------------------------------
2881 --Start of Comments
2882 --Name: remove_from_ASL
2883 --Pre-reqs:
2884 -- None.
2885 --Modifies:
2886 -- None.
2887 --Locks:
2888 -- None.
2889 --Function:
2890 -- Checks if there exists any ASL entry for the IDVs that are selected for
2891 -- multi-mod vendor change and delete the entry from the ASL
2892
2893 --Parameters:
2894 --IN:
2895 --p_multi_mod_request_id
2896 -- The request id of the Multi-Mod Request
2897 --Testing:
2898 -- None.
2899 --End of Comments
2900 -------------------------------------------------------------------------------
2901
2902 PROCEDURE remove_from_ASL (itemtype in varchar2,
2903 itemkey in varchar2,
2904 actid in number,
2905 funcmode in varchar2,
2906 resultout out NOCOPY varchar2) IS
2907 l_draft_id number;
2908 l_document_id number;
2909 l_progress VARCHAR2(500) := '000';
2910 l_document_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2911 l_approval_source VARCHAR2(50);
2912 l_multi_mod_req_id PO_MULTI_MOD_DOCS.MULTI_MOD_REQUEST_ID%TYPE;
2913 l_multi_mod_req_type PO_MULTI_MOD_REQUESTS.MULTI_MOD_REQUEST_TYPE%TYPE;
2914
2915 BEGIN
2916 l_progress := 'Process_Response_Internal: 001';
2917
2918 l_document_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2919 itemkey => itemkey,
2920 aname => 'DOCUMENT_ID');
2921
2922 l_document_type := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2923 itemkey => itemkey,
2924 aname => 'DOCUMENT_TYPE');
2925
2926 l_approval_source := po_wf_util_pkg.GetItemAttrText( itemtype => itemtype,
2927 itemkey => itemkey,
2928 aname => 'APPROVAL_SOURCE');
2929
2930 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => itemtype,
2931 itemkey => itemkey,
2932 aname => 'DRAFT_ID');
2933
2934 IF l_approval_source = 'MULTI_MOD' THEN
2935
2936
2937 SELECT MULTI_MOD_REQUEST_ID
2938 INTO l_multi_mod_req_id
2939 FROM PO_MULTI_MOD_DOCS
2940 WHERE DRAFT_ID = l_draft_id;
2941
2942 SELECT MULTI_MOD_REQUEST_TYPE
2943 INTO l_multi_mod_req_type
2944 FROM PO_MULTI_MOD_REQUESTS
2945 WHERE MULTI_MOD_REQUEST_ID = l_multi_mod_req_id;
2946
2947 IF l_document_type = 'PA' THEN
2948 DELETE FROM po_asl_documents
2949 WHERE DOCUMENT_HEADER_ID = l_document_id;
2950 END IF;
2951
2952 END IF;
2953
2954 EXCEPTION
2955 WHEN OTHERS THEN
2956 WF_CORE.context('PO_MULTI_MOD_PVT', 'REMOVE_FROM_ASL', l_progress);
2957 resultout:='COMPLETE:'||'N';
2958 END remove_from_ASL;
2959
2960 END po_multi_mod_pvt;