[Home] [Help]
PACKAGE BODY: APPS.PO_FED_FIELD_FUNCTIONS
Source
1 PACKAGE BODY po_fed_field_functions AS
2 /* $Header: PO_FED_FIELD_FUNCTIONS.plb 120.36.12020000.11 2013/05/09 15:16:44 rparise ship $ */
3 d_pkg_name CONSTANT varchar2(50) := PO_LOG.get_package_base('PO_FED_FIELD_FUNCTIONS');
4 d_appln_short_name CONSTANT VARCHAR2(20) := 'PO';
5
6 FUNCTION newline
7 RETURN VARCHAR2
8 IS
9 BEGIN
10 RETURN (FND_CONST.local_chr(13) || FND_CONST.local_chr(10));
11 END newline;
12
13 --CLM Phase 4 Changes
14
15 PROCEDURE derive_insp_accp_adrs_dtls
16 (
17 p_insp_location IN OUT nocopy NUMBER
18 ,p_insp_addresscode IN OUT nocopy NUMBER
19 ,p_insp_addressdetails IN OUT nocopy VARCHAR2
20 ,p_accp_location IN OUT nocopy NUMBER
21 ,p_accp_addresscode IN OUT nocopy NUMBER
22 ,p_accp_addressdetails IN OUT nocopy VARCHAR2
23 ,p_source IN VARCHAR2
24 );
25
26 PROCEDURE pr_addressdef (
27 addresstype IN OUT nocopy VARCHAR2
28 ,location IN OUT nocopy NUMBER
29 ,addresscode IN OUT nocopy NUMBER
30 ,contact IN OUT nocopy NUMBER
31 ,addressdetails IN OUT nocopy VARCHAR2
32 ,contactdetails IN OUT nocopy VARCHAR2
33 ,addressdetailsxml IN OUT nocopy VARCHAR2
34 ,contactdetailsxml IN OUT nocopy VARCHAR2
35 ,HiddenAddType IN VARCHAR2
36 ,HiddenLKPType IN VARCHAR2
37 )
38 IS
39 d_proc_name CONSTANT varchar2(50) := 'pr_addressdef';
40 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
41
42 HiddenCountry VARCHAR2(20);
43 HiddenZipCode VARCHAR(20);
44
45
46
47 BEGIN
48
49 addresstype := HiddenAddType;
50 IF (PO_LOG.d_proc) THEN
51 PO_LOG.proc_begin(d_module);
52 PO_LOG.proc_begin(d_module, 'HiddenAddType', HiddenAddType);
53 PO_LOG.proc_begin(d_module, 'location', location);
54 PO_LOG.proc_begin(d_module, 'addresscode', addresscode);
55 END IF;
56
57 address_XD(
58 addresstype
59 ,location
60 ,addresscode
61 ,contact
62 ,addressdetails
63 ,contactdetails
64 ,addressdetailsxml
65 ,contactdetailsxml
66 ,hiddenCountry
67 ,hiddenZipCode
68 ,HiddenAddType
69 ,HiddenLKPType
70 ,'REQUISITIONS'
71 );
72
73
74 END pr_addressdef;
75
76
77 PROCEDURE po_addressdef (
78 addresstype IN OUT nocopy VARCHAR2
79 ,location IN OUT nocopy NUMBER
80 ,addresscode IN OUT nocopy NUMBER
81 ,contact IN OUT nocopy NUMBER
82 ,addressdetails IN OUT nocopy VARCHAR2
83 ,contactdetails IN OUT nocopy VARCHAR2
84 ,addressdetailsxml IN OUT nocopy VARCHAR2
85 ,contactdetailsxml IN OUT nocopy VARCHAR2
86 ,hiddenCountry IN OUT nocopy VARCHAR2
87 ,hiddenZipCode IN OUT nocopy VARCHAR2
88 ,HiddenAddType IN VARCHAR2
89 ,HiddenLKPType IN VARCHAR2
90 )
91 IS
92 d_proc_name CONSTANT varchar2(50) := 'po_addressdef';
93 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
94
95
96 BEGIN
97
98 addresstype := HiddenAddType;
99
100 IF (PO_LOG.d_proc) THEN
101 PO_LOG.proc_begin(d_module);
102 PO_LOG.proc_begin(d_module, 'HiddenAddType', HiddenAddType);
103 PO_LOG.proc_begin(d_module, 'location', location);
104 PO_LOG.proc_begin(d_module, 'addresscode', addresscode);
105 END IF;
106
107
108 address_XD(
109 addresstype
110 ,location
111 ,addresscode
112 ,contact
113 ,addressdetails
114 ,contactdetails
115 ,addressdetailsxml
116 ,contactdetailsxml
117 ,hiddenCountry
118 ,hiddenZipCode
119 ,HiddenAddType
120 ,HiddenLKPType
121 ,'PURCHASING'
122 );
123
124 END po_addressdef;
125
126
127 PROCEDURE address_XD (
128 addresstype IN OUT nocopy VARCHAR2
129 ,location IN OUT nocopy NUMBER
130 ,addresscode IN OUT nocopy NUMBER
131 ,contact IN OUT nocopy NUMBER
132 ,addressdetails IN OUT nocopy VARCHAR2
133 ,contactdetails IN OUT nocopy VARCHAR2
134 ,addressdetailsxml IN OUT nocopy VARCHAR2
135 ,contactdetailsxml IN OUT nocopy VARCHAR2
136 ,hiddenCountry IN OUT NOCOPY VARCHAR2
137 ,hiddenZipCode IN OUT NOCOPY VARCHAR2
138 ,HiddenAddType IN VARCHAR2
139 ,HiddenLKPType IN VARCHAR2
140 ,p_functional_area IN VARCHAR2
141 )
142
143 IS
144 d_proc_name CONSTANT varchar2(50) := 'address_XD';
145 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
146
147 l_xml_sql VARCHAR2(4000);
148 l_xml_sql_c VARCHAR2(4000);
149
150 l_LOCATION_CODE VARCHAR2(200);
151 l_ADDRESS_LINE_1 VARCHAR2(200);
152 l_ADDRESS_LINE_2 VARCHAR2(200);
153 l_ADDRESS_LINE_3 VARCHAR2(200);
154 l_TOWN_OR_CITY VARCHAR2(200);
155 l_REGION_1 VARCHAR2(200);
156 l_REGION_2 VARCHAR2(200);
157 l_REGION_3 VARCHAR2(200);
158 l_TELEPHONE_NUMBER_1 VARCHAR2(200);
159 l_TELEPHONE_NUMBER_2 VARCHAR2(200);
160 l_POSTAL_CODE VARCHAR2(200);
161 l_COUNTRY VARCHAR2(200);
162 l_PERSON_ID VARCHAR2(200);
163 l_FIRST_NAME VARCHAR2(200);
164 l_LAST_NAME VARCHAR2(200);
165 l_TITLE VARCHAR2(200);
166 l_FULL_NAME VARCHAR2(200);
167 l_WORK_TELEPHONE VARCHAR2(200);
168 l_EMAIL_ADDRESS VARCHAR2(200);
169 l_POSITION VARCHAR2(200);
170 l_loc_doddac varchar2(200);
171 l_addressType VARCHAR2(200);
172 l_addr_concat_s VARCHAR2(2000);
173 l_addr_concat_s1 VARCHAR2(2000);
174 l_addr_concat_s2 VARCHAR2(2000);
175 l_addr_concat_l VARCHAR2(2000);
176 l_addr_concat_l1 VARCHAR2(2000);
177 l_cnt_concat_s VARCHAR2(2000);
178 l_cnt_concat_l VARCHAR2(2000);
179
180
181 BEGIN
182
183 addresstype := HiddenAddType;
184 l_addressType := HiddenAddType;
185
186 IF (PO_LOG.d_proc) THEN
187 PO_LOG.proc_begin(d_module);
188 PO_LOG.proc_begin(d_module, 'HiddenAddType', HiddenAddType);
189 PO_LOG.proc_begin(d_module, 'location', location);
190 PO_LOG.proc_begin(d_module, 'addresscode', addresscode);
191 END IF;
192
193 -- <Multi-Mod Project>
194 -- For Multi-Mod flow, default only Modification Addresses. Other addresses
195 -- should remain null
196 IF p_functional_area IS NOT NULL
197 AND (g_is_multi_mod_context = 'N'
198 OR g_is_multi_mod_context = 'Y' AND HiddenAddType LIKE 'MOD_%')
199 THEN
200
201 IF location IS NULL THEN
202
203 IF HiddenAddType LIKE 'MOD_%' THEN
204 l_addressType := SubStr(HiddenAddType,5);
205 END IF ;
206
207 BEGIN
208 SELECT location_id,location_id
209 INTO addresscode,location
210 FROM po_user_preferences
211 WHERE functional_area = p_functional_area
212 AND address_type= l_addressType
213 AND FND_USER_ID = fnd_global.user_id;
214 EXCEPTION
215 WHEN No_Data_Found THEN
216 NULL;
217 WHEN OTHERS THEN
218 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
219 END;
220 END IF;
221
222 IF contact IS NULL THEN
223
224 IF HiddenAddType LIKE 'MOD_%' THEN
225 l_addressType := SubStr(HiddenAddType,5);
226 END IF ;
227
228 BEGIN
229 SELECT contact_id
230 INTO contact
231 FROM po_user_preferences
232 WHERE functional_area = p_functional_area
233 AND address_type= l_addressType
234 AND FND_USER_ID = fnd_global.user_id;
235 EXCEPTION
236 WHEN No_Data_Found THEN
237 NULL;
238 WHEN OTHERS THEN
239 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
240 END;
241 END IF;
242
243 END IF ;
244
245 if location is not null then
246 addressdetails := '';
247 l_addr_concat_s := '';
248 l_addr_concat_s1 := '';
249 l_addr_concat_s2 := '';
250 l_addr_concat_l := '';
251 l_addr_concat_l1 := '';
252
253 SELECT LOCATION_CODE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,REGION_1,
254 REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,POSTAL_CODE,COUNTRY
255 INTO l_LOCATION_CODE,l_ADDRESS_LINE_1,l_ADDRESS_LINE_2,l_ADDRESS_LINE_3,l_TOWN_OR_CITY,l_REGION_1,
256 l_REGION_2,l_REGION_3,l_TELEPHONE_NUMBER_1,l_TELEPHONE_NUMBER_2,l_POSTAL_CODE,l_COUNTRY
257 FROM HR_LOCATIONS_ALL
258 WHERE LOCATION_ID = location;
259
260 hiddenCountry := l_REGION_2;
261 hiddenZipCode := l_POSTAL_CODE;
262
263 IF l_ADDRESS_LINE_1 IS NOT NULL THEN
264 addressdetails := l_ADDRESS_LINE_1 || newline;
265 END IF;
266 IF l_ADDRESS_LINE_2 IS NOT NULL THEN
267 addressdetails := addressdetails || l_ADDRESS_LINE_2 || newline;
268 END IF;
269 IF l_TOWN_OR_CITY IS NOT NULL THEN
270 addressdetails := addressdetails || l_TOWN_OR_CITY || newline;
271 END IF;
272 IF l_REGION_2 IS NOT NULL THEN
273 addressdetails := addressdetails || l_REGION_2 || newline;
274 END IF;
275 IF l_POSTAL_CODE IS NOT NULL THEN
276 addressdetails := addressdetails || l_POSTAL_CODE || newline;
277 END IF;
278
279 -- Address Concatenation logic for Federal Printing
280 -- Short Address
281
282 l_addr_concat_s1 := l_LOCATION_CODE;
283
284 IF l_ADDRESS_LINE_1 IS NOT NULL THEN
285 l_addr_concat_s1 := l_addr_concat_s1 || newline|| l_ADDRESS_LINE_1;
286 END IF;
287
288 IF l_TOWN_OR_CITY IS NOT NULL THEN
289 l_addr_concat_s2 := l_TOWN_OR_CITY;
290 END IF;
291
292 IF (l_REGION_2 IS NOT NULL AND l_addr_concat_s2 IS NOT NULL) THEN
293 l_addr_concat_s2 := l_addr_concat_s2 || ', ' || l_REGION_2;
294 ELSIF l_REGION_2 IS NOT NULL THEN
295 l_addr_concat_s2 := l_REGION_2;
296 END IF;
297
298 IF (l_POSTAL_CODE IS NOT NULL AND l_addr_concat_s2 IS NOT NULL) THEN
299 l_addr_concat_s2 := l_addr_concat_s2 || ', ' || l_POSTAL_CODE;
300 ELSIF l_POSTAL_CODE IS NOT NULL THEN
301 l_addr_concat_s2 := l_POSTAL_CODE;
302 END IF;
303
304 IF (l_COUNTRY IS NOT NULL AND l_addr_concat_s2 IS NOT NULL) THEN
305 l_addr_concat_s2 := l_addr_concat_s2 || ', ' || l_COUNTRY;
306 ELSIF l_COUNTRY IS NOT NULL THEN
307 l_addr_concat_s2 := l_COUNTRY;
308 END IF;
309
310 IF l_addr_concat_s2 IS NOT NULL THEN
311 l_addr_concat_s := l_addr_concat_s1 || newline|| l_addr_concat_s2;
312 ELSE
313 l_addr_concat_s := l_addr_concat_s1;
314 END IF;
315
316 -- Long Address
317
318 l_addr_concat_l1 := l_LOCATION_CODE;
319
320 IF l_ADDRESS_LINE_1 IS NOT NULL THEN
321 l_addr_concat_l1 := l_addr_concat_l1 || newline|| l_ADDRESS_LINE_1;
322 END IF;
323
324 IF l_ADDRESS_LINE_2 IS NOT NULL THEN
325 l_addr_concat_l1 := l_addr_concat_l1 || ', ' || l_ADDRESS_LINE_2;
326 END IF;
327
328 IF l_ADDRESS_LINE_3 IS NOT NULL THEN
329 l_addr_concat_l1 := l_addr_concat_l1 || ', ' || l_ADDRESS_LINE_3;
330 END IF;
331
332 IF l_addr_concat_s2 IS NOT NULL THEN
333 l_addr_concat_l := l_addr_concat_l1 || newline|| l_addr_concat_s2;
334 ELSE l_addr_concat_l := l_addr_concat_l1;
335 END IF;
336
337
338 SELECT DISTINCT LEI_INFORMATION1
339 into l_loc_doddac
340 FROM HR_LOCATION_EXTRA_INFO
341 where information_type = 'CLM_OFFICE_CODE'
342 AND 'Y' = po_uda_data_util.is_location_valid (location, HiddenAddType )
343 AND LOCATION_ID = location;
344
345
346 l_xml_sql :=
347 'Select XMLELEMENT ( ' || HiddenAddType || '_ADDR , XMLFOREST ('''||
348 l_LOCATION_CODE ||''' ' || HiddenAddType || '_' || 'LOCNAME , ''' ||
349 l_loc_doddac ||''' ' || HiddenAddType || '_' || 'LOCCODE , ''' ||
350 l_ADDRESS_LINE_1 || ''' ' || HiddenAddType || '_' || 'ADDRL1, ''' ||
351 l_ADDRESS_LINE_2 || ''' ' || HiddenAddType || '_' || 'ADDRL2 ,''' ||
352 l_ADDRESS_LINE_3 || ''' ' || HiddenAddType || '_' || 'ADDRL3 ,''' ||
353 l_TOWN_OR_CITY || ''' ' || HiddenAddType || '_' || 'CITY, ''' ||
354 l_REGION_1 || ''' '|| HiddenAddType || '_' || 'REGION1, ''' ||
355 l_REGION_2 || ''' ' || HiddenAddType || '_' || 'REGION2, ''' ||
356 l_REGION_3 || ''' ' || HiddenAddType || '_' || 'REGION3, ''' ||
357 l_TELEPHONE_NUMBER_1 || ''' ' || HiddenAddType || '_' || 'PHONE, ''' ||
358 l_TELEPHONE_NUMBER_2 || ''' '|| HiddenAddType || '_' || 'FAX, ''' ||
359 l_POSTAL_CODE || ''' ' || HiddenAddType || '_' || 'ZIPCODE, ''' ||
360 l_COUNTRY || ''' ' || HiddenAddType || '_' || 'COUNTRY, ''' ||
361 l_addr_concat_s || ''' ' || HiddenAddType || '_' || 'SADDR, ''' ||
362 l_addr_concat_l || ''' ' || HiddenAddType || '_' || 'LADDR ' ||
363 ' ))FROM DUAL';
364
365 execute immediate l_xml_sql into addressdetailsxml;
366
367
368 end if;
369
370 IF contact is not null then
371 contactdetails := '';
372 l_cnt_concat_s := '';
373 l_cnt_concat_l := '';
374
375 SELECT PERSON_ID,FIRST_NAME,LAST_NAME,TITLE,FULL_NAME,EMAIL_ADDRESS,
376 (SELECT PHONE_NUMBER FROM PER_PHONES WHERE parent_table = 'PER_ALL_PEOPLE_F' and phone_type = 'W1'
377 and parent_id = person_id and SYSDATE BETWEEN Nvl(date_from,SYSDATE) AND Nvl(date_to,SYSDATE )) WORK_TELEPHONE
378 INTO l_PERSON_ID,l_FIRST_NAME,l_LAST_NAME,l_TITLE,l_FULL_NAME,l_EMAIL_ADDRESS, l_WORK_TELEPHONE
379 FROM PER_ALL_PEOPLE_F
380 WHERE PERSON_ID = contact
381 AND SYSDATE BETWEEN Nvl(EFFECTIVE_START_DATE,SYSDATE) AND Nvl(EFFECTIVE_END_DATE,SYSDATE );
382
383
384 begin
385
386 SELECT PEI_INFORMATION1
387 INTO l_POSITION
388 FROM PER_PEOPLE_EXTRA_INFO
389 WHERE INFORMATION_TYPE = 'CLM_CONTACT_TITLE'
390 AND person_id = contact;
391 contactdetails := l_POSITION || newline;
392 EXCEPTION
393 WHEN no_data_found then
394 l_position := NULL;
395 END;
396
397
398 contactdetails := contactdetails || 'Phone: ' ||l_WORK_TELEPHONE || newline;
399 contactdetails := contactdetails || 'Email: ' || l_EMAIL_ADDRESS ||newline;
400
401 -- Short Contact
402
403 IF l_FULL_NAME IS NOT NULL THEN
404 l_cnt_concat_s := l_FULL_NAME;
405 END IF;
406
407 IF (l_WORK_TELEPHONE IS NOT NULL AND l_cnt_concat_s IS NOT NULL) THEN
408 l_cnt_concat_s := l_cnt_concat_s || ', ' || l_WORK_TELEPHONE;
409 ELSIF l_WORK_TELEPHONE IS NOT NULL THEN
410 l_cnt_concat_s := l_WORK_TELEPHONE;
411 END IF;
412
413 -- Long Contact includes Email Address
414
415 IF (l_EMAIL_ADDRESS IS NOT NULL AND l_cnt_concat_s IS NOT NULL) THEN
416 l_cnt_concat_l := l_cnt_concat_s || ', ' || l_EMAIL_ADDRESS;
417 ELSIF l_EMAIL_ADDRESS IS NOT NULL THEN
418 l_cnt_concat_l := l_EMAIL_ADDRESS;
419 ELSE l_cnt_concat_l := l_FULL_NAME;
420 END IF;
421
422 l_xml_sql_c :=
423 'Select XMLELEMENT ( ' || HiddenAddType || '_CNT , XMLFOREST (''' || l_POSITION ||''' '|| HiddenAddType || '_' || 'CTPOSITION , ''' ||
424 l_PERSON_ID ||''' '|| HiddenAddType || '_' || 'CTID, ''' ||
425 l_FIRST_NAME ||''' '|| HiddenAddType || '_' || 'CTFNAME, ''' ||
426 l_LAST_NAME ||''' '|| HiddenAddType || '_' || 'CTLNAME, ''' ||
427 l_TITLE ||''' '|| HiddenAddType || '_' || 'CTTITLE, ''' ||
428 l_FULL_NAME ||''' '|| HiddenAddType || '_' || 'CTNAME, ''' ||
429 l_WORK_TELEPHONE ||''' '|| HiddenAddType || '_' || 'CTPHONE, ''' ||
430 l_EMAIL_ADDRESS ||''' '|| HiddenAddType || '_' || 'CTEMAIL, ''' ||
431 l_cnt_concat_s ||''' '|| HiddenAddType || '_' || 'SCONTACT, ''' ||
432 l_cnt_concat_l ||''' '|| HiddenAddType || '_' || 'LCONTACT' ||
433 ' )) FROM DUAL';
434
435 execute immediate l_xml_sql_c into contactdetailsxml;
436
437 end if;
438
439 EXCEPTION
440 WHEN No_Data_Found THEN
441 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': NO DATA FOUND' );
442 WHEN OTHERS THEN
443 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
444 IF (PO_LOG.d_exc) THEN
445 PO_LOG.proc_end(d_module||': '|| SQLERRM );
446 PO_LOG.proc_end(d_module);
447 END IF;
448
449
450 END address_XD;
451
452 PROCEDURE pr_addresses_XSC
453 (
454 p_HiddenAddType IN VARCHAR2
455 ,p_location IN NUMBER
456 ,p_contact_id IN NUMBER
457 ,x_return_status OUT NOCOPY VARCHAR2
458 ,x_errorcode OUT NOCOPY VARCHAR2
459 ,x_msg_count OUT NOCOPY VARCHAR2
460 ,x_msg_data OUT NOCOPY VARCHAR2
461 )IS
462
463 d_proc_name CONSTANT varchar2(50) := 'pr_addresses_XSC';
464 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
465 l_submissionValid BOOLEAN ;
466 l_AddTypeDispField VARCHAR2(100);
467
468 BEGIN
469
470 IF (PO_LOG.d_proc) THEN
471 PO_LOG.proc_begin(d_module);
472 PO_LOG.proc_begin(d_module, 'p_HiddenAddType', p_HiddenAddType);
473 PO_LOG.proc_begin(d_module, 'p_location', p_location);
474 PO_LOG.proc_begin(d_module, 'p_contact_id', p_contact_id);
475 END IF;
476
477 IF p_location IS NULL THEN
478 CASE p_HiddenAddType
479 WHEN 'COTR_OFFICE' THEN l_submissionValid := FALSE;
480 WHEN 'ISSUING_OFFICE' THEN l_submissionValid := FALSE;
481 WHEN 'REQ_OFFICE' THEN l_submissionValid := FALSE;
482 WHEN 'PRO_ADMIN_OFFICE' THEN l_submissionValid := FALSE;
483 WHEN 'ASSIST_OFFICE' THEN l_submissionValid := FALSE;
484 WHEN 'INV_OFFICE' THEN l_submissionValid := FALSE;
485 ELSE
486 l_submissionValid := TRUE;
487 END CASE;
488 ELSE
489 l_submissionValid := TRUE;
490 END IF;
491
492 IF p_contact_id IS NULL AND p_HiddenAddType IN('ASSIST_OFFICE', 'INV_OFFICE') THEN
493 l_submissionValid := FALSE;
494 END IF;
495
496 IF l_submissionValid THEN
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498 x_errorcode := '0';
499 x_msg_count := '1';
500 x_msg_data := 'SUCCESS';
501 ELSE
502 SELECT displayed_field
503 INTO l_AddTypeDispField
504 FROM po_lookup_codes
505 WHERE lookup_type like '%PR_UDA_ADDRESS_TYPES'
506 AND lookup_code = p_HiddenAddType
507 AND rownum =1 ;
508
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 x_errorcode := '-1';
511 x_msg_count := '1';
512
513 IF p_location IS NULL then
514 x_msg_data := 'Location cannot be null for ' || l_AddTypeDispField;
515 fnd_message.set_name(d_appln_short_name,'PO_LOCATION_NOT_NULL') ;
516 fnd_message.set_token('ADDTYPE',l_AddTypeDispField);
517 fnd_msg_pub.ADD;
518 END IF;
519
520 IF p_contact_id IS NULL then
521 x_msg_data := 'Contact cannot be null for ' || l_AddTypeDispField;
522 fnd_message.set_name(d_appln_short_name,'PO_CONTACT_NOT_NULL') ;
523 fnd_message.set_token('ADDTYPE',l_AddTypeDispField);
524 fnd_msg_pub.ADD;
525 END IF;
526 END IF ;
527
528 IF (PO_LOG.d_proc) THEN
529 PO_LOG.proc_end(d_module);
530 PO_LOG.proc_end(d_module, 'x_errorcode', x_errorcode);
531 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
532 END IF;
533
534 EXCEPTION
535 WHEN OTHERS THEN
536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
537 x_errorcode := '-2';
538 x_msg_count := '1';
539 x_msg_data := 'Exception in pr_addresses_XSC';
540 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
541 END pr_addresses_XSC;
542
543 PROCEDURE po_addresses_XSC
544 (
545 p_HiddenAddType IN VARCHAR2
546 ,p_location IN NUMBER
547 ,p_contact_id IN NUMBER
548 ,x_return_status OUT NOCOPY VARCHAR2
549 ,x_errorcode OUT NOCOPY VARCHAR2
550 ,x_msg_count OUT NOCOPY VARCHAR2
551 ,x_msg_data OUT NOCOPY VARCHAR2
552 )IS
553 d_proc_name CONSTANT varchar2(50) := 'addresses_XSC';
554 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
555 l_submissionValid BOOLEAN;
556 l_AddTypeDispField VARCHAR2(100);
557
558 BEGIN
559 IF (PO_LOG.d_proc) THEN
560 PO_LOG.proc_begin(d_module);
561 PO_LOG.proc_begin(d_module, 'p_HiddenAddType', p_HiddenAddType);
562 PO_LOG.proc_begin(d_module, 'p_location', p_location);
563 PO_LOG.proc_begin(d_module, 'p_contact_id', p_contact_id);
564 END IF;
565
566 IF p_location IS NULL
567 AND (g_is_multi_mod_context = 'N'
568 OR g_is_multi_mod_context = 'Y' AND p_HiddenAddType LIKE 'MOD_%')
569 THEN
570 CASE p_HiddenAddType
571 WHEN 'INV_OFFICE' THEN l_submissionValid := FALSE;
572 WHEN 'COTR_OFFICE' THEN l_submissionValid := FALSE;
573 WHEN 'ISSUING_OFFICE' THEN l_submissionValid := FALSE;
574 WHEN 'REQ_OFFICE' THEN l_submissionValid := FALSE;
575 WHEN 'PRO_ADMIN_OFFICE' THEN l_submissionValid := FALSE;
576 WHEN 'ADMIN_OFFICE' THEN l_submissionValid := FALSE;
577 WHEN 'SBA_OFFICE' THEN l_submissionValid := FALSE;
578 WHEN 'PAY_OFFICE' THEN l_submissionValid := FALSE;
579 WHEN 'MOD_ISSUING_OFFICE' THEN l_submissionValid := FALSE;
580 ELSE
581 l_submissionValid := TRUE;
582 END CASE;
583 ELSE
584 l_submissionValid := TRUE;
585 END IF;
586
587 IF l_submissionValid THEN
588 x_return_status := FND_API.G_RET_STS_SUCCESS;
589 x_errorcode := '0';
590 x_msg_count := '1';
591 x_msg_data := 'SUCCESS';
592 ELSE
593
594 SELECT displayed_field
595 INTO l_AddTypeDispField
596 FROM po_lookup_codes
597 WHERE lookup_type = 'PO_MOD_UDA_ADDRESS_TYPES'
598 AND lookup_code = p_HiddenAddType;
599
600
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 x_errorcode := '-1';
603 x_msg_count := '1';
604 x_msg_data := 'Location cannot be null for ' || l_AddTypeDispField;
605
606 fnd_message.set_name(d_appln_short_name,'PO_LOCATION_NOT_NULL') ;
607 fnd_message.set_token('ADDTYPE',l_AddTypeDispField);
608 fnd_msg_pub.ADD;
609 END IF ;
610
611 IF (PO_LOG.d_proc) THEN
612 PO_LOG.proc_end(d_module);
613 PO_LOG.proc_end(d_module, 'x_errorcode', x_errorcode);
614 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
615 END IF;
616
617 EXCEPTION
618 WHEN OTHERS THEN
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 x_errorcode := '-2';
621 x_msg_count := '1';
622 x_msg_data := 'Exception in addresses_XSC';
623 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
624 END po_addresses_XSC;
625
626
627 PROCEDURE pr_addresses_XPD
628 (
629 p_header_id IN NUMBER
630 ,p_HiddenAddType IN VARCHAR2
631 ,p_HiddenLKPType IN VARCHAR2
632 ,x_location IN OUT NOCOPY NUMBER
633 ,x_addresscode IN OUT NOCOPY NUMBER
634 ,x_contact_id IN OUT NOCOPY NUMBER
635 ,x_addressdetails IN OUT NOCOPY VARCHAR2
636 ,x_contactdetails IN OUT NOCOPY VARCHAR2
637 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
638 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
639 ,x_return_status OUT NOCOPY VARCHAR2
640 ,x_errorcode OUT NOCOPY VARCHAR2
641 ,x_msg_count OUT NOCOPY VARCHAR2
642 ,x_msg_data OUT NOCOPY VARCHAR2
643 )IS
644 d_proc_name CONSTANT varchar2(50) := 'addresses_XPD';
645 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
646
647 BEGIN
648 IF (PO_LOG.d_proc) THEN
649 PO_LOG.proc_begin(d_module);
650 PO_LOG.proc_begin(d_module, 'p_HiddenAddType', p_HiddenAddType);
651 PO_LOG.proc_begin(d_module, 'p_header_id', p_header_id);
652 PO_LOG.proc_begin(d_module, 'p_HiddenLKPType', p_HiddenLKPType);
653 END IF;
654
655 IF p_HiddenAddType = 'ISSUING_OFFICE' THEN
656
657 UPDATE PO_REQUISITION_HEADERS_ALL
658 SET CLM_ISSUING_OFFICE = x_location,
659 CLM_COTR_CONTACT = x_contact_id
660 WHERE REQUISITION_HEADER_ID = p_header_id;
661
662 END IF;
663
664 IF p_HiddenAddType = 'COTR_OFFICE' THEN
665
666 UPDATE PO_REQUISITION_HEADERS_ALL
667 SET CLM_COTR_OFFICE = x_location,
668 CLM_COTR_CONTACT = x_contact_id
669 WHERE REQUISITION_HEADER_ID = p_header_id;
670
671 END IF;
672
673 IF p_HiddenAddType = 'REQ_OFFICE' THEN
674
675 UPDATE PO_REQUISITION_HEADERS_ALL
676 SET CLM_REQ_OFFICE = x_location,
677 CLM_REQ_CONTACT = x_contact_id
678 WHERE REQUISITION_HEADER_ID = p_header_id;
679
680 END IF;
681
682 IF p_HiddenAddType = 'ASSIST_OFFICE' THEN
683
684 UPDATE PO_REQUISITION_HEADERS_ALL
685 SET CLM_ASSIST_OFFICE = x_location,
686 CLM_ASSIST_CONTACT = x_contact_id
687 WHERE REQUISITION_HEADER_ID = p_header_id;
688
689 END IF;
690
691 IF (PO_LOG.d_proc) THEN
692 PO_LOG.proc_end(d_module);
693 PO_LOG.proc_end(d_module, 'x_errorcode', x_errorcode);
694 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
695 END IF;
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 x_errorcode := '-2';
701 x_msg_count := '1';
702 x_msg_data := 'Exception in addresses_XPD';
703 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
704 IF (PO_LOG.d_exc) THEN
705 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data ||': '|| SQLERRM );
706 PO_LOG.proc_end(d_module);
707 END IF;
708
709 END pr_addresses_XPD;
710
711 PROCEDURE po_addresses_XPD
712 (
713 p_header_id IN NUMBER
714 ,p_draft_id IN NUMBER
715 ,p_HiddenAddType IN VARCHAR2
716 ,p_HiddenLKPType IN VARCHAR2
717 ,x_location IN OUT NOCOPY NUMBER
718 ,x_addresscode IN OUT NOCOPY NUMBER
719 ,x_contact_id IN OUT NOCOPY NUMBER
720 ,x_addressdetails IN OUT NOCOPY VARCHAR2
721 ,x_contactdetails IN OUT NOCOPY VARCHAR2
722 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
723 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
724 ,x_return_status OUT NOCOPY VARCHAR2
725 ,x_errorcode OUT NOCOPY VARCHAR2
726 ,x_msg_count OUT NOCOPY VARCHAR2
727 ,x_msg_data OUT NOCOPY VARCHAR2
728 )IS
729 d_proc_name CONSTANT varchar2(50) := 'addresses_XPD';
730 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
731
732 BEGIN
733 IF (PO_LOG.d_proc) THEN
734 PO_LOG.proc_begin(d_module);
735 PO_LOG.proc_begin(d_module, 'p_HiddenAddType', p_HiddenAddType);
736 PO_LOG.proc_begin(d_module, 'p_header_id', p_header_id);
737 PO_LOG.proc_begin(d_module, 'p_HiddenLKPType', p_HiddenLKPType);
738 END IF;
739
740
741 IF(p_draft_id = -1 ) THEN
742 IF p_HiddenAddType = 'ISSUING_OFFICE' THEN
743 UPDATE PO_HEADERS_DRAFT_ALL
744 SET CLM_ISSUING_OFFICE = x_location
745 WHERE PO_HEADER_ID = p_header_id
746 AND DRAFT_ID = XPD_DRAFT_ID;
747 END IF;
748 IF p_HiddenAddType = 'COTR_OFFICE' THEN
749 UPDATE PO_HEADERS_DRAFT_ALL
750 SET CLM_COTR_OFFICE = x_location,
751 CLM_COTR_CONTACT = x_contact_id
752 WHERE PO_HEADER_ID = p_header_id
753 AND DRAFT_ID = XPD_DRAFT_ID;
754 END IF;
755 ELSE
756 IF p_HiddenAddType = 'ISSUING_OFFICE' THEN
757 UPDATE PO_HEADERS_DRAFT_ALL
758 SET CLM_ISSUING_OFFICE = x_location
759 WHERE PO_HEADER_ID = p_header_id
760 AND DRAFT_ID = XPD_DRAFT_ID;
761 END IF;
762 IF p_HiddenAddType = 'COTR_OFFICE' THEN
763 UPDATE PO_HEADERS_DRAFT_ALL
764 SET CLM_COTR_OFFICE = x_location,
765 CLM_COTR_CONTACT = x_contact_id
766 WHERE PO_HEADER_ID = p_header_id
767 AND DRAFT_ID = XPD_DRAFT_ID;
768 END IF;
769 IF p_HiddenAddType = 'MOD_ADMIN_OFFICE' THEN
770 UPDATE PO_HEADERS_DRAFT_ALL
771 SET CLM_MOD_ADMIN_OFFICE = x_location
772 WHERE PO_HEADER_ID = p_header_id
773 AND DRAFT_ID = XPD_DRAFT_ID;
774 END IF;
775 IF p_HiddenAddType = 'MOD_ISSUING_OFFICE' THEN
776 UPDATE PO_HEADERS_DRAFT_ALL
777 SET CLM_MOD_ISSUING_OFFICE = x_location
778 WHERE PO_HEADER_ID = p_header_id
779 AND DRAFT_ID = XPD_DRAFT_ID;
780 END IF;
781 IF p_HiddenAddType = 'REQ_OFFICE' THEN
782 UPDATE PO_HEADERS_DRAFT_ALL
783 SET CLM_REQ_OFFICE = x_location
784 WHERE PO_HEADER_ID = p_header_id
785 AND DRAFT_ID = XPD_DRAFT_ID;
786 END IF;
787
788 END IF ;
789
790
791 IF (PO_LOG.d_proc) THEN
792 PO_LOG.proc_end(d_module);
793 PO_LOG.proc_end(d_module, 'x_errorcode', x_errorcode);
794 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
795 END IF;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 x_errorcode := '-2';
801 x_msg_count := '1';
802 x_msg_data := 'Exception in addresses_XPD';
803 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
804 IF (PO_LOG.d_exc) THEN
805 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data ||': '|| SQLERRM );
806 PO_LOG.proc_end(d_module);
807 END IF;
808
809 END po_addresses_XPD;
810
811 PROCEDURE addresses_XLE
812 (
813 p_HiddenAddType IN VARCHAR2
814 ,p_HiddenLKPType IN VARCHAR2
815 ,p_source_item IN VARCHAR2
816 ,x_location IN OUT NOCOPY NUMBER
817 ,x_addresscode IN OUT NOCOPY NUMBER
818 ,x_contact_id IN OUT NOCOPY NUMBER
819 ,x_addressdetails IN OUT NOCOPY VARCHAR2
820 ,x_contactdetails IN OUT NOCOPY VARCHAR2
821 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
822 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
823 ,x_hiddenCountry IN OUT NOCOPY VARCHAR2
824 ,x_hiddenZipCode IN OUT NOCOPY VARCHAR2
825 ,x_return_status OUT NOCOPY VARCHAR2
826 ,x_errorcode OUT NOCOPY VARCHAR2
827 ,x_msg_count OUT NOCOPY VARCHAR2
828 ,x_msg_data OUT NOCOPY VARCHAR2
829 )
830 IS
831
832 d_proc_name CONSTANT varchar2(50) := 'addresses_XLE';
833 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
834 l_xml_sql VARCHAR2(4000);
835 l_xml_sql_c VARCHAR2(4000);
836
837
838 l_full_name VARCHAR2(1000);
839 l_position VARCHAR2(1000);
840 l_phone_number VARCHAR2(50);
841 l_email VARCHAR2(50);
842
843 l_addr_line_1 VARCHAR2(2000);
844 l_addr_line_2 VARCHAR2(2000);
845 l_town_or_city VARCHAR2(1000);
846 l_region2 VARCHAR2(1000);
847 l_style VARCHAR2(500);
848 l_postal_code VARCHAR2(50);
849 l_state VARCHAR2(100);
850 l_attr_bean_name VARCHAR2(250);
851 l_functional_area VARCHAR2(200);
852 l_addressType varchar2(200);
853
854 CURSOR c_get_bean_name(p_attr_name varchar2) IS
855 SELECT distinct att_assoc.ATTR_GROUP_TYPE || att_assoc.ATTR_GROUP_ID || attrs.attr_id || 'LOVDisp' Attr_Bean_Name
856 FROM ego_obj_attr_grp_assocs_v att_assoc , EGO_ATTRS_v attrs WHERE
857 attrs.ATTR_GROUP_TYPE = att_assoc.ATTR_GROUP_TYPE
858 AND attrs.ATTR_GROUP_NAME = att_assoc.ATTR_GROUP_NAME
859 AND attrs.ATTR_GROUP_NAME = 'addresses'
860 AND ATTR_NAME = p_attr_name;
861
862
863 BEGIN
864
865 IF (PO_LOG.d_proc) THEN
866 PO_LOG.proc_begin(d_module);
867 PO_LOG.proc_begin(d_module, 'p_HiddenAddType', p_HiddenAddType);
868 PO_LOG.proc_begin(d_module, 'p_source_item', p_source_item);
869 PO_LOG.proc_begin(d_module, 'p_HiddenLKPType', p_HiddenLKPType);
870 PO_LOG.proc_begin(d_module, 'x_location', x_location);
871 PO_LOG.proc_begin(d_module, 'x_addresscode', x_addresscode);
872 PO_LOG.proc_begin(d_module, 'x_contact_id', x_contact_id);
873 END IF;
874
875 FOR c_get_bean_name_rec IN c_get_bean_name('location') LOOP
876 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
877 l_attr_bean_name := 'LOCATION';
878 END IF;
879 END LOOP;
880
881 FOR c_get_bean_name_rec IN c_get_bean_name('addresscode') LOOP
882 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
883 l_attr_bean_name := 'ADDRESS_CODE';
884 END IF;
885 END LOOP;
886
887 FOR c_get_bean_name_rec IN c_get_bean_name('contact') LOOP
888 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
889 l_attr_bean_name := 'CONTACT';
890 END IF;
891 END LOOP;
892
893 IF l_attr_bean_name = 'LOCATION' THEN
894
895 x_addresscode := x_location;
896
897 if x_location is not null then
898
899 l_addressType := p_HiddenAddType;
900 l_functional_area := NULL;
901
902 address_XD(
903 l_addressType
904 ,x_location
905 ,x_addresscode
906 ,x_contact_id
907 ,x_addressdetails
908 ,x_contactdetails
909 ,x_addressdtlsxml
910 ,x_contactdtlsxml
911 ,x_hiddenCountry
912 ,x_hiddenZipCode
913 ,p_HiddenAddType
914 ,p_HiddenLKPType
915 ,l_functional_area
916 );
917
918 x_return_status := FND_API.G_RET_STS_SUCCESS;
919 x_errorcode := '0';
920 x_msg_count := '1';
921 x_msg_data := 'SUCCESS';
922
923 ELSE
924 x_addressdtlsxml := NULL;
925 x_addressdetails := NULL;
926 END IF ;
927
928 ELSIF l_attr_bean_name = 'ADDRESS_CODE' THEN
929
930 x_location := x_addresscode;
931 if x_location is not null then
932
933 l_addressType := p_HiddenAddType;
934 l_functional_area := NULL;
935
936 address_XD(
937 l_addressType
938 ,x_location
939 ,x_addresscode
940 ,x_contact_id
941 ,x_addressdetails
942 ,x_contactdetails
943 ,x_addressdtlsxml
944 ,x_contactdtlsxml
945 ,x_hiddenCountry
946 ,x_hiddenZipCode
947 ,p_HiddenAddType
948 ,p_HiddenLKPType
949 ,l_functional_area
950 );
951
952 x_return_status := FND_API.G_RET_STS_SUCCESS;
953 x_errorcode := '0';
954 x_msg_count := '1';
955 x_msg_data := 'SUCCESS';
956
957 ELSE
958 x_addressdtlsxml := NULL;
959 x_addressdetails := NULL;
960
961 END IF;
962
963 ELSIF l_attr_bean_name = 'CONTACT' THEN
964
965 IF x_contact_id is not null THEN
966
967 l_functional_area := NULL;
968 l_addressType := p_HiddenAddType;
969
970 address_XD(
971 l_addressType
972 ,x_location
973 ,x_addresscode
974 ,x_contact_id
975 ,x_addressdetails
976 ,x_contactdetails
977 ,x_addressdtlsxml
978 ,x_contactdtlsxml
979 ,x_hiddenCountry
980 ,x_hiddenZipCode
981 ,p_HiddenAddType
982 ,p_HiddenLKPType
983 ,l_functional_area
984 );
985
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987 x_errorcode := '0';
988 x_msg_count := '1';
989 x_msg_data := 'SUCCESS';
990 ELSE
991 x_contactdetails :=NULL;
992 x_contactdtlsxml :=NULL;
993 END IF;
994 ELSE
995
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 x_errorcode := '-1';
998 x_msg_count := '1';
999 x_msg_data := 'LOV Event is from not LOCATION/CONTACT/ADDRESS_CODE';
1000
1001 END IF;
1002
1003 IF (PO_LOG.d_proc) THEN
1004 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data );
1005 PO_LOG.proc_end(d_module,'x_return_status' , x_return_status );
1006 PO_LOG.proc_end(d_module);
1007 END IF;
1008
1009 EXCEPTION
1010 WHEN OTHERS THEN
1011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1012 x_errorcode := '-2';
1013 x_msg_count := '1';
1014 x_msg_data := 'Exception in addresses_XLE';
1015
1016 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
1017 IF (PO_LOG.d_exc) THEN
1018 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data ||': '|| SQLERRM );
1019 PO_LOG.proc_end(d_module);
1020 END IF;
1021
1022 END addresses_XLE;
1023
1024 PROCEDURE bu_pri_prj_inf_XLE
1025 (
1026 p_cust_proj_code IN VARCHAR2
1027 ,x_cust_proj_text OUT NOCOPY VARCHAR2
1028 ,x_return_status OUT NOCOPY VARCHAR2
1029 ,x_errorcode OUT NOCOPY VARCHAR2
1030 ,x_msg_count OUT NOCOPY VARCHAR2
1031 ,x_msg_data OUT NOCOPY VARCHAR2
1032 )IS
1033
1034 d_proc_name CONSTANT varchar2(50) := 'bu_pri_prj_inf_XLE';
1035 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1036
1037 BEGIN
1038
1039 IF (PO_LOG.d_proc) THEN
1040 PO_LOG.proc_begin(d_module);
1041 PO_LOG.proc_begin(d_module, 'p_cust_proj_code', p_cust_proj_code);
1042 END IF;
1043
1044
1045 SELECT DESCRIPTION
1046 INTO x_cust_proj_text
1047 FROM PO_LOOKUP_CODES
1048 WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
1049 AND LOOKUP_CODE = p_cust_proj_code;
1050
1051 x_return_status := FND_API.G_RET_STS_SUCCESS;
1052 x_errorcode := '0';
1053 x_msg_count := '1';
1054 x_msg_data := 'SUCCESS';
1055
1056 IF (PO_LOG.d_proc) THEN
1057 PO_LOG.proc_end(d_module);
1058 PO_LOG.proc_end (d_module, 'x_cust_proj_text', x_cust_proj_text);
1059 END IF;
1060
1061
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 x_errorcode := '-2';
1066 x_msg_count := '1';
1067 x_msg_data := 'Exception in bu_pri_prj_inf_XLE : '|| SQLERRM;
1068
1069 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1070 IF (PO_LOG.d_exc) THEN
1071 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data );
1072 PO_LOG.proc_end(d_module);
1073 END IF;
1074
1075 END bu_pri_prj_inf_XLE;
1076
1077 PROCEDURE pr_bu_pri_prj_inf_XPD
1078 (
1079 p_header_id IN NUMBER
1080 ,p_cust_proj_code IN VARCHAR2
1081 ,p_priority IN VARCHAR2
1082 ,x_cust_proj_text OUT NOCOPY VARCHAR2
1083 ,x_return_status OUT NOCOPY VARCHAR2
1084 ,x_errorcode OUT NOCOPY VARCHAR2
1085 ,x_msg_count OUT NOCOPY VARCHAR2
1086 ,x_msg_data OUT NOCOPY VARCHAR2
1087 )IS
1088
1089 d_proc_name CONSTANT varchar2(50) := 'pr_bu_pri_prj_inf_XPD';
1090 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1091
1092
1093 BEGIN
1094
1095 IF p_cust_proj_code IS NOT NULL THEN
1096 SELECT DESCRIPTION
1097 INTO x_cust_proj_text
1098 FROM PO_LOOKUP_CODES
1099 WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
1100 AND LOOKUP_CODE = p_cust_proj_code;
1101 END IF;
1102
1103 UPDATE PO_REQUISITION_HEADERS_ALL
1104 SET CLM_PRIORITY_CODE = p_priority
1105 WHERE REQUISITION_HEADER_ID = p_header_id;
1106
1107 x_return_status := FND_API.G_RET_STS_SUCCESS;
1108 x_errorcode := '0';
1109 x_msg_count := '1';
1110 x_msg_data := 'SUCCESS';
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1115 x_errorcode := '-2';
1116 x_msg_count := '1';
1117 x_msg_data := 'Exception in bu_pri_prj_inf_XPD : '|| SQLERRM ;
1118 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1119 END pr_bu_pri_prj_inf_XPD;
1120
1121 PROCEDURE po_bu_pri_prj_inf_XPD
1122 (
1123 p_header_id IN NUMBER
1124 ,p_draft_id IN NUMBER
1125 ,p_priority IN VARCHAR2
1126 ,x_return_status OUT NOCOPY VARCHAR2
1127 ,x_errorcode OUT NOCOPY VARCHAR2
1128 ,x_msg_count OUT NOCOPY VARCHAR2
1129 ,x_msg_data OUT NOCOPY VARCHAR2
1130 )IS
1131
1132 d_proc_name CONSTANT varchar2(50) := 'po_bu_pri_prj_inf_XPD';
1133 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1134
1135
1136 BEGIN
1137
1138 UPDATE PO_HEADERS_DRAFT_ALL
1139 SET CLM_PRIORITY_CODE = p_priority
1140 WHERE PO_HEADER_ID = p_header_id
1141 AND Draft_id = XPD_DRAFT_ID;
1142
1143 x_return_status := FND_API.G_RET_STS_SUCCESS;
1144 x_errorcode := '0';
1145 x_msg_count := '1';
1146 x_msg_data := 'SUCCESS';
1147
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1151 x_errorcode := '-2';
1152 x_msg_count := '1';
1153 x_msg_data := 'Exception in bu_pri_prj_inf_XPD : '|| SQLERRM ;
1154 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1155 END po_bu_pri_prj_inf_XPD;
1156
1157 PROCEDURE fed_cust_desg_XLE
1158 (
1159 p_cust_proj_code IN VARCHAR2
1160 ,x_cust_proj_text OUT NOCOPY VARCHAR2
1161 ,x_return_status OUT NOCOPY VARCHAR2
1162 ,x_errorcode OUT NOCOPY VARCHAR2
1163 ,x_msg_count OUT NOCOPY VARCHAR2
1164 ,x_msg_data OUT NOCOPY VARCHAR2
1165 )IS
1166
1167 d_proc_name CONSTANT varchar2(50) := 'fed_cust_desg_XLE';
1168 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1169
1170 BEGIN
1171
1172
1173 IF p_cust_proj_code IS NOT NULL THEN
1174 SELECT DESCRIPTION
1175 INTO x_cust_proj_text
1176 FROM PO_LOOKUP_CODES
1177 WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
1178 AND LOOKUP_CODE = p_cust_proj_code;
1179 END IF;
1180
1181 x_return_status := FND_API.G_RET_STS_SUCCESS;
1182 x_errorcode := '0';
1183 x_msg_count := '1';
1184 x_msg_data := 'SUCCESS';
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 x_errorcode := '-2';
1190 x_msg_count := '1';
1191 x_msg_data := 'Exception in fed_cust_desg_XLE : '|| SQLERRM ;
1192 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1193 END fed_cust_desg_XLE;
1194
1195 PROCEDURE po_fed_cust_desg_XPD
1196 (
1197 p_line_id IN NUMBER
1198 ,p_draft_id IN NUMBER
1199 ,p_mdaps_mais IN VARCHAR2
1200 ,p_naics IN VARCHAR2
1201 ,p_fsc_psc IN VARCHAR2
1202 ,x_return_status OUT NOCOPY VARCHAR2
1203 ,x_errorcode OUT NOCOPY VARCHAR2
1204 ,x_msg_count OUT NOCOPY VARCHAR2
1205 ,x_msg_data OUT NOCOPY VARCHAR2
1206 ) IS
1207
1208 d_proc_name CONSTANT varchar2(50) := 'po_fed_cust_desg_XPD';
1209 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1210
1211 BEGIN
1212
1213 UPDATE PO_LINES_DRAFT_ALL
1214 SET CLM_MDAPS_MAIS = p_mdaps_mais,
1215 CLM_NAICS = p_naics,
1216 CLM_FSC_PSC = p_fsc_psc
1217 WHERE PO_LINE_ID = p_line_id
1218 AND Draft_id = XPD_DRAFT_ID;
1219
1220
1221 x_return_status := FND_API.G_RET_STS_SUCCESS;
1222 x_errorcode := '0';
1223 x_msg_count := '1';
1224 x_msg_data := 'SUCCESS';
1225
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1229 x_errorcode := '-2';
1230 x_msg_count := '1';
1231 x_msg_data := 'Exception in po_fed_cust_desg_XPD: '|| SQLERRM ;
1232 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1233 END po_fed_cust_desg_XPD;
1234
1235 PROCEDURE add_item_info_XD
1236 (
1237 p_item_id IN NUMBER
1238 ,p_org_id IN NUMBER
1239 ,x_item_long_desc IN OUT NOCOPY VARCHAR2
1240 ,x_return_status OUT NOCOPY VARCHAR2
1241 ,x_errorcode OUT NOCOPY VARCHAR2
1242 ,x_msg_count OUT NOCOPY VARCHAR2
1243 ,x_msg_data OUT NOCOPY VARCHAR2
1244 )IS
1245
1246 d_proc_name CONSTANT varchar2(50) := 'add_item_info_XD';
1247 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1248
1249 BEGIN
1250
1251
1252 IF x_item_long_desc IS NULL THEN
1253
1254 BEGIN
1255 SELECT LONG_DESCRIPTION
1256 INTO x_item_long_desc
1257 FROM MTL_SYSTEM_ITEMS_TL
1258 WHERE INVENTORY_ITEM_ID = p_item_id
1259 AND ORGANIZATION_ID = p_org_id
1260 AND language = userenv('LANG');
1261 EXCEPTION
1262 WHEN no_data_found then
1263 x_item_long_desc:= NULL;
1264 END;
1265 x_return_status := FND_API.G_RET_STS_SUCCESS;
1266 x_errorcode := '0';
1267 x_msg_count := '1';
1268 x_msg_data := 'SUCCESS';
1269
1270
1271 ELSE
1272
1273 x_return_status := FND_API.G_RET_STS_ERROR;
1274 x_errorcode := '-1';
1275 x_msg_count := '1';
1276 x_msg_data := 'Item Long Description already defaulted';
1277
1278 END IF;
1279
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1283 x_errorcode := '-2';
1284 x_msg_count := '1';
1285 x_msg_data := 'Exception in add_item_info_XD';
1286 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1287
1288 END add_item_info_XD;
1289
1290
1291 PROCEDURE add_item_info_XLE
1292 (
1293 p_item_id IN NUMBER
1294 ,p_org_id IN NUMBER
1295 ,x_item_long_desc IN OUT NOCOPY VARCHAR2
1296 ,x_return_status OUT NOCOPY VARCHAR2
1297 ,x_errorcode OUT NOCOPY VARCHAR2
1298 ,x_msg_count OUT NOCOPY VARCHAR2
1299 ,x_msg_data OUT NOCOPY VARCHAR2
1300 )IS
1301
1302 d_proc_name CONSTANT varchar2(50) := 'add_item_info_XLE';
1303 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1304
1305
1306 BEGIN
1307
1308 IF p_item_id IS NOT NULL THEN
1309 SELECT LONG_DESCRIPTION
1310 INTO x_item_long_desc
1311 FROM MTL_SYSTEM_ITEMS_TL
1312 WHERE INVENTORY_ITEM_ID = p_item_id
1313 AND ORGANIZATION_ID = p_org_id
1314 AND language = userenv('LANG');
1315 END IF;
1316
1317 x_return_status := FND_API.G_RET_STS_SUCCESS;
1318 x_errorcode := '0';
1319 x_msg_count := '1';
1320 x_msg_data := 'SUCCESS';
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1325 x_errorcode := '-2';
1326 x_msg_count := '1';
1327 x_msg_data := 'Exception in add_item_info_XLE';
1328 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1329 END add_item_info_XLE;
1330
1331
1332 PROCEDURE ship_info_XD
1333 (
1334 p_ship_to_loc_id IN NUMBER
1335 ,x_ship_to_addr IN OUT NOCOPY VARCHAR2
1336 ,p_hidd_ship_addr IN OUT NOCOPY VARCHAR
1337 ,p_hiddenCountry IN OUT NOCOPY VARCHAR2
1338 ,p_hiddenZipCode IN OUT NOCOPY VARCHAR
1339 ,x_return_status OUT NOCOPY VARCHAR2
1340 ,x_errorcode OUT NOCOPY VARCHAR2
1341 ,x_msg_count OUT NOCOPY VARCHAR2
1342 ,x_msg_data OUT NOCOPY VARCHAR2
1343 )IS
1344
1345 l_addr_line_1 VARCHAR2(2000);
1346 l_addr_line_2 VARCHAR2(2000);
1347 l_town_or_city VARCHAR2(1000);
1348 l_region2 VARCHAR2(1000);
1349 l_style VARCHAR2(500);
1350 l_postal_code VARCHAR2(50);
1351 l_state VARCHAR2(100);
1352 l_ship_addr_xml_sql VARCHAR2(6000);
1353
1354 d_proc_name CONSTANT varchar2(50) := 'ship_info_XD';
1355 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1356
1357 BEGIN
1358
1359
1360 IF x_ship_to_addr IS NULL THEN
1361
1362 SELECT ADDRESS_LINE_1, ADDRESS_LINE_2, TOWN_OR_CITY, REGION_2, STYLE, POSTAL_CODE,COUNTRY
1363 INTO l_addr_line_1, l_addr_line_2, l_town_or_city, l_region2, l_style, l_postal_code,p_hiddenCountry
1364 FROM HR_LOCATIONS
1365 WHERE LOCATION_ID = p_ship_to_loc_id;
1366
1367 l_state := l_region2;
1368
1369 IF l_addr_line_1 IS NOT NULL THEN
1370 x_ship_to_addr := l_addr_line_1 || newline;
1371 END IF;
1372 IF l_addr_line_2 IS NOT NULL THEN
1373 x_ship_to_addr := x_ship_to_addr || l_addr_line_2 || newline;
1374 END IF;
1375 IF l_town_or_city IS NOT NULL THEN
1376 x_ship_to_addr := x_ship_to_addr || l_town_or_city || ', ';
1377 END IF;
1378 IF l_region2 IS NOT NULL THEN
1379 x_ship_to_addr := x_ship_to_addr || l_state || newline;
1380 END IF;
1381 IF l_style IS NOT NULL THEN
1382 x_ship_to_addr := x_ship_to_addr || l_style || ', ';
1383 END IF;
1384 IF l_postal_code IS NOT NULL THEN
1385 x_ship_to_addr := x_ship_to_addr || l_postal_code;
1386 p_hiddenZipCode := l_postal_code;
1387 END IF;
1388
1389 l_ship_addr_xml_sql := 'SELECT XMLFOREST(HLC.LOCATION_ID ship_to_location_id, HLC.LOCATION_CODE location_name,
1390 HLC.ADDRESS_LINE_1 address_line_1,
1391 HLC.ADDRESS_LINE_2 address_line_2,
1392 HLC.ADDRESS_LINE_3 address_line_3,
1393 hlc.TOWN_OR_CITY city,
1394 hlc.region_1, nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning) region1_desc,
1395 hlc.region_2, fcl2.meaning region2_desc,
1396 HLC.COUNTRY, NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY) country_name,
1397 hlc.postal_code,
1398 HLC.TELEPHONE_NUMBER_1 contact_phone, HLC.TELEPHONE_NUMBER_2 contact_fax).getStringVal()
1399 FROM HR_LOCATIONS HLC,
1400 FND_TERRITORIES_TL FTE,
1401 FND_LOOKUP_VALUES FCL1,
1402 FND_LOOKUP_VALUES FCL2,
1403 FND_LOOKUP_VALUES FCL3
1404 WHERE HLC.LOCATION_ID = :1
1405 AND HLC.COUNTRY = FTE.TERRITORY_CODE (+)
1406 AND DECODE(FTE.TERRITORY_CODE, NULL, ''1'', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, ''1'', USERENV(''LANG''))
1407 AND HLC.REGION_1 = FCL1.LOOKUP_CODE (+)
1408 AND HLC.COUNTRY
1409 || ''_PROVINCE'' = FCL1.LOOKUP_TYPE (+)
1410 AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.SECURITY_GROUP_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
1411 AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', 3)
1412 AND DECODE (FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
1413 AND HLC.REGION_2 = FCL2.LOOKUP_CODE (+)
1414 AND HLC.COUNTRY
1415 || ''_STATE'' = FCL2.LOOKUP_TYPE (+)
1416 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.SECURITY_GROUP_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
1417 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', 3)
1418 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.LANGUAGE) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
1419 AND HLC.REGION_1 = FCL3.LOOKUP_CODE (+)
1420 AND HLC.COUNTRY
1421 || ''_COUNTY'' = FCL3.LOOKUP_TYPE (+)
1422 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.SECURITY_GROUP_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
1423 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', 3)
1424 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))';
1425
1426 EXECUTE IMMEDIATE l_ship_addr_xml_sql INTO p_hidd_ship_addr USING p_ship_to_loc_id;
1427
1428 x_return_status := FND_API.G_RET_STS_SUCCESS;
1429 x_errorcode := '0';
1430 x_msg_count := '1';
1431 x_msg_data := 'SUCCESS';
1432 ELSE
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 x_errorcode := '-1';
1435 x_msg_count := '1';
1436 x_msg_data := 'Shipping address is already defaulted';
1437
1438 END IF;
1439 EXCEPTION
1440 WHEN No_Data_Found THEN
1441 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': NO DATA FOUND' );
1442 WHEN OTHERS THEN
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444 x_errorcode := '-2';
1445 x_msg_count := '1';
1446 x_msg_data := 'Exception in ship_info_XD';
1447 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1448 END ship_info_XD;
1449
1450 PROCEDURE ship_info_XLE
1451 (
1452 p_ship_to_loc_id IN NUMBER
1453 ,x_ship_to_addr IN OUT NOCOPY VARCHAR2
1454 ,p_hidd_ship_addr IN OUT NOCOPY VARCHAR
1455 ,p_hiddenCountry IN OUT NOCOPY VARCHAR2
1456 ,p_hiddenZipCode IN OUT NOCOPY VARCHAR
1457 ,x_return_status OUT NOCOPY VARCHAR2
1458 ,x_errorcode OUT NOCOPY VARCHAR2
1459 ,x_msg_count OUT NOCOPY VARCHAR2
1460 ,x_msg_data OUT NOCOPY VARCHAR2
1461 )IS
1462
1463 d_proc_name CONSTANT varchar2(50) := 'ship_info_XLE';
1464 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1465
1466 l_addr_line_1 VARCHAR2(2000);
1467 l_addr_line_2 VARCHAR2(2000);
1468 l_town_or_city VARCHAR2(1000);
1469 l_region2 VARCHAR2(1000);
1470 l_style VARCHAR2(500);
1471 l_postal_code VARCHAR2(50);
1472 l_state VARCHAR2(100);
1473 l_ship_addr_xml_sql VARCHAR2(6000);
1474
1475 BEGIN
1476
1477
1478 IF ( p_ship_to_loc_id IS NOT NULL ) THEN
1479
1480 SELECT ADDRESS_LINE_1, ADDRESS_LINE_2, TOWN_OR_CITY, REGION_2, STYLE, POSTAL_CODE,COUNTRY
1481 INTO l_addr_line_1, l_addr_line_2, l_town_or_city, l_region2, l_style, l_postal_code,p_hiddenCountry
1482 FROM HR_LOCATIONS
1483 WHERE LOCATION_ID = p_ship_to_loc_id;
1484
1485 l_state := l_region2;
1486
1487 IF l_addr_line_1 IS NOT NULL THEN
1488 x_ship_to_addr := l_addr_line_1 || newline;
1489 END IF;
1490 IF l_addr_line_2 IS NOT NULL THEN
1491 x_ship_to_addr := x_ship_to_addr || l_addr_line_2 || newline;
1492 END IF;
1493 IF l_town_or_city IS NOT NULL THEN
1494 x_ship_to_addr := x_ship_to_addr || l_town_or_city || ', ';
1495 END IF;
1496 IF l_region2 IS NOT NULL THEN
1497 x_ship_to_addr := x_ship_to_addr || l_state || newline;
1498 END IF;
1499 IF l_style IS NOT NULL THEN
1500 x_ship_to_addr := x_ship_to_addr || l_style || ', ';
1501 END IF;
1502 IF l_postal_code IS NOT NULL THEN
1503 x_ship_to_addr := x_ship_to_addr || l_postal_code;
1504 p_hiddenZipCode := l_postal_code;
1505 END IF;
1506
1507 l_ship_addr_xml_sql := 'SELECT XMLFOREST(HLC.LOCATION_ID ship_to_location_id, HLC.LOCATION_CODE location_name,
1508 HLC.ADDRESS_LINE_1 address_line_1,
1509 HLC.ADDRESS_LINE_2 address_line_2,
1510 HLC.ADDRESS_LINE_3 address_line_3,
1511 hlc.TOWN_OR_CITY city,
1512 hlc.region_1, nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning) region1_desc,
1513 hlc.region_2, fcl2.meaning region2_desc,
1514 HLC.COUNTRY, NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY) country_name,
1515 hlc.postal_code,
1516 HLC.TELEPHONE_NUMBER_1 contact_phone, HLC.TELEPHONE_NUMBER_2 contact_fax).getStringVal()
1517 FROM HR_LOCATIONS HLC,
1518 FND_TERRITORIES_TL FTE,
1519 FND_LOOKUP_VALUES FCL1,
1520 FND_LOOKUP_VALUES FCL2,
1521 FND_LOOKUP_VALUES FCL3
1522 WHERE HLC.LOCATION_ID = :1
1523 AND HLC.COUNTRY = FTE.TERRITORY_CODE (+)
1524 AND DECODE(FTE.TERRITORY_CODE, NULL, ''1'', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, ''1'', USERENV(''LANG''))
1525 AND HLC.REGION_1 = FCL1.LOOKUP_CODE (+)
1526 AND HLC.COUNTRY
1527 || ''_PROVINCE'' = FCL1.LOOKUP_TYPE (+)
1528 AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.SECURITY_GROUP_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
1529 AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', 3)
1530 AND DECODE (FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
1531 AND HLC.REGION_2 = FCL2.LOOKUP_CODE (+)
1532 AND HLC.COUNTRY
1533 || ''_STATE'' = FCL2.LOOKUP_TYPE (+)
1534 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.SECURITY_GROUP_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
1535 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', 3)
1536 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.LANGUAGE) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
1537 AND HLC.REGION_1 = FCL3.LOOKUP_CODE (+)
1538 AND HLC.COUNTRY
1539 || ''_COUNTY'' = FCL3.LOOKUP_TYPE (+)
1540 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.SECURITY_GROUP_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
1541 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', 3)
1542 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))';
1543
1544 EXECUTE IMMEDIATE l_ship_addr_xml_sql INTO p_hidd_ship_addr USING p_ship_to_loc_id;
1545
1546 ELSE
1547 p_hidd_ship_addr := NULL ;
1548 p_hiddenCountry := NULL ;
1549 p_hiddenZipCode := NULL ;
1550 x_ship_to_addr := NULL ;
1551
1552 END IF ;
1553
1554 x_return_status := FND_API.G_RET_STS_SUCCESS;
1555 x_errorcode := '0';
1556 x_msg_count := '1';
1557 x_msg_data := 'SUCCESS';
1558
1559 EXCEPTION
1560 WHEN No_Data_Found THEN
1561 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': NO DATA FOUND' );
1562 p_hidd_ship_addr := NULL ;
1563 p_hiddenCountry := NULL ;
1564 p_hiddenZipCode := NULL ;
1565 x_ship_to_addr := NULL ;
1566
1567 WHEN OTHERS THEN
1568 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1569 x_errorcode := '-2';
1570 x_msg_count := '1';
1571 x_msg_data := 'Exception in ship_info_XLE';
1572 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1573
1574 END ship_info_XLE;
1575
1576 PROCEDURE pr_sourcing_info_req_XPD
1577 (
1578 p_header_id IN NUMBER
1579 ,p_suggest_aw_no IN VARCHAR2
1580 ,x_return_status OUT NOCOPY VARCHAR2
1581 ,x_errorcode OUT NOCOPY VARCHAR2
1582 ,x_msg_count OUT NOCOPY VARCHAR2
1583 ,x_msg_data OUT NOCOPY VARCHAR2
1584 )IS
1585
1586 d_proc_name CONSTANT varchar2(50) := 'pr_sourcing_info_req_XPD';
1587 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1588
1589
1590 BEGIN
1591
1592 UPDATE PO_REQUISITION_HEADERS_ALL
1593 SET SUGGESTED_AWARD_NO = p_suggest_aw_no
1594 WHERE REQUISITION_HEADER_ID = p_header_id;
1595
1596 x_return_status := FND_API.G_RET_STS_SUCCESS;
1597 x_errorcode := '0';
1598 x_msg_count := '1';
1599 x_msg_data := 'SUCCESS';
1600
1601 EXCEPTION
1602 WHEN OTHERS THEN
1603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1604 x_errorcode := '-2';
1605 x_msg_count := '1';
1606 x_msg_data := 'Exception in sourcing_info_req_XPD';
1607 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1608 END pr_sourcing_info_req_XPD;
1609
1610
1611
1612 PROCEDURE competitive_info_XD
1613 (
1614 p_set_aside_stat IN OUT NOCOPY VARCHAR2
1615 ,p_set_aside_perc IN OUT NOCOPY VARCHAR2
1616 )
1617 IS
1618 d_proc_name CONSTANT varchar2(50) := 'competitive_info_XD';
1619 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1620
1621 BEGIN
1622 IF (PO_LOG.d_proc) THEN
1623 PO_LOG.proc_begin(d_module);
1624 PO_LOG.proc_begin(d_module, 'p_set_aside_stat', p_set_aside_stat);
1625 PO_LOG.proc_begin(d_module, 'p_set_aside_stat', p_set_aside_perc);
1626
1627 END IF;
1628
1629 IF p_set_aside_stat IS NULL THEN
1630 p_set_aside_stat := 'UNRESTRICTED';
1631 END IF;
1632
1633 IF p_set_aside_perc IS NULL THEN
1634 IF p_set_aside_stat = 'UNRESTRICTED' THEN
1635 p_set_aside_perc := '0';
1636 END IF;
1637 END IF;
1638
1639 END competitive_info_XD;
1640
1641 PROCEDURE competitive_info_XV
1642 (
1643
1644 p_set_aside_stat IN VARCHAR2
1645 ,p_set_aside_perc IN VARCHAR2
1646 ,p_set_aside_type IN VARCHAR2
1647 ,p_naics IN VARCHAR2
1648 ,p_size_stand IN VARCHAR2
1649 ,x_return_status OUT NOCOPY VARCHAR2
1650 ,x_errorcode OUT NOCOPY VARCHAR2
1651 ,x_msg_count OUT NOCOPY VARCHAR2
1652 ,x_msg_data OUT NOCOPY VARCHAR2
1653 ,p_draft_id IN NUMBER --<Bug 16421784>
1654 )IS
1655 d_proc_name CONSTANT varchar2(50) := 'competitive_info_XV';
1656 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1657 l_msg_count NUMBER(6) := 0;
1658
1659 BEGIN
1660
1661 x_return_status := FND_API.G_RET_STS_SUCCESS;
1662 x_errorcode := '0';
1663 x_msg_count := '1';
1664 x_msg_data := 'SUCCESS';
1665
1666 IF p_set_aside_stat = 'UNRESTRICTED' AND To_Number(Nvl(p_set_aside_perc,0)) > 0 THEN
1667 x_return_status := FND_API.G_RET_STS_ERROR;
1668 x_errorcode := '-1';
1669 l_msg_count := l_msg_count + 1;
1670 x_msg_data := 'The set aside percentage does not apply on an unrestricted action.';
1671 fnd_message.set_name(d_appln_short_name, 'PO_NO_PER_UNRES_ACTION');
1672 fnd_msg_pub.ADD;
1673 END IF ;
1674 IF p_set_aside_stat = 'SET_ASIDE' AND To_Number(Nvl(p_set_aside_perc,0)) <= 0 THEN
1675 x_return_status := FND_API.G_RET_STS_ERROR;
1676 x_errorcode := '-1';
1677 l_msg_count := l_msg_count + 1;
1678 x_msg_data := 'The Set Aside Percentage must contain a value greater than 0, when the Set Aside Status is ''Set Aside''.';
1679 fnd_message.set_name(d_appln_short_name, 'PO_SET_ASIDE_PER_POSITIVE');
1680 fnd_msg_pub.ADD;
1681 END IF;
1682 IF p_set_aside_stat = 'SET_ASIDE' AND p_set_aside_type IS null THEN
1683 x_return_status := FND_API.G_RET_STS_ERROR;
1684 x_errorcode := '-1';
1685 l_msg_count := l_msg_count + 1;
1686 x_msg_data := 'When the Set Aside Status is ''Set Aside'', you must select a value for the set aside type.';
1687 fnd_message.set_name(d_appln_short_name,'PO_SET_ASIDE_SELECT_TYPE');
1688 fnd_msg_pub.ADD;
1689 END IF;
1690
1691 --Bug 13491410
1692 --<Bug16421784>, this validation should be carried on only for Base
1693 --Documents, since it is a read only field in Modifications
1694 IF p_set_aside_stat = 'SET_ASIDE' AND p_set_aside_type = 'SMALL_BUSINESS'
1695 AND p_draft_id = -1 THEN
1696 x_return_status := FND_API.G_RET_STS_ERROR;
1697 x_errorcode := '-1';
1698 l_msg_count := l_msg_count + 1;
1699 x_msg_data := 'The value ''Small Business'' for Set-Aside is not valid for new Awards/ IDVs.';
1700 fnd_message.set_name(d_appln_short_name,'PO_SMALL_BUSNS_INVALID');
1701 fnd_msg_pub.ADD;
1702 END IF;
1703
1704 IF p_set_aside_stat = 'SET_ASIDE' AND p_naics IS null THEN
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 x_errorcode := '-1';
1707 l_msg_count := l_msg_count + 1;
1708 x_msg_data := 'When the Set Aside Status is ''Set Aside'' you must select a value for the NAICS ';
1709 fnd_message.set_name(d_appln_short_name, 'PO_SET_ASIDE_NAICS');
1710 fnd_msg_pub.ADD;
1711 END IF;
1712 IF p_set_aside_stat = 'UNRESTRICTED' AND p_size_stand IS NOT null THEN
1713 x_return_status := FND_API.G_RET_STS_ERROR;
1714 x_errorcode := '-1';
1715 l_msg_count := l_msg_count + 1;
1716 x_msg_data := 'The size standard does not apply on an unrestricted action.';
1717 fnd_message.set_name(d_appln_short_name, 'PO_NO_SIZE_UNRES_ACTION');
1718 fnd_msg_pub.ADD;
1719 END IF;
1720 IF p_set_aside_stat = 'UNRESTRICTED' AND p_naics IS NOT null THEN
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 x_errorcode := '-1';
1723 l_msg_count := l_msg_count + 1;
1724 x_msg_data := 'The NAICS designation does not apply on an unrestricted action.';
1725 fnd_message.set_name(d_appln_short_name, 'PO_NO_NAICS_UNRES_ACTION');
1726 fnd_msg_pub.ADD;
1727 END IF;
1728 IF p_set_aside_stat = 'UNRESTRICTED' AND p_set_aside_type IS NOT null THEN
1729 x_return_status := FND_API.G_RET_STS_ERROR;
1730 x_errorcode := '-1';
1731 l_msg_count := l_msg_count + 1;
1732 x_msg_data := 'The set aside type does not apply on an unrestricted action.';
1733 fnd_message.set_name(d_appln_short_name, 'PO_NO_ASIDETYPE_UNRES_ACTION');
1734 fnd_msg_pub.ADD;
1735 END IF;
1736
1737 IF l_msg_count > 0 THEN
1738 x_msg_count := l_msg_count;
1739 END IF ;
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1743 x_errorcode := '-2';
1744 x_msg_count := '1';
1745 x_msg_data := 'Exception in competitive_info_XV';
1746 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1747 END competitive_info_XV;
1748
1749
1750 PROCEDURE competitive_info_XLE
1751 (
1752
1753 p_set_aside_stat IN OUT NOCOPY VARCHAR2
1754 ,p_set_aside_perc IN OUT NOCOPY VARCHAR
1755 ,p_set_aside_type IN OUT NOCOPY VARCHAR
1756 ,p_naics IN OUT NOCOPY VARCHAR
1757 ,p_size_stand IN OUT NOCOPY VARCHAR
1758 ,p_source_item IN VARCHAR2
1759 ,x_return_status OUT NOCOPY VARCHAR2
1760 ,x_errorcode OUT NOCOPY VARCHAR2
1761 ,x_msg_count OUT NOCOPY VARCHAR2
1762 ,x_msg_data OUT NOCOPY VARCHAR2
1763 )IS
1764
1765 d_proc_name CONSTANT varchar2(50) := 'competitive_info_XLE';
1766 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
1767
1768 BEGIN
1769 IF (PO_LOG.d_proc) THEN
1770 PO_LOG.proc_begin(d_module);
1771 PO_LOG.proc_begin(d_module, 'p_set_aside_stat', p_set_aside_stat);
1772 PO_LOG.proc_begin(d_module, 'p_set_aside_perc', p_set_aside_perc);
1773 PO_LOG.proc_begin(d_module, 'p_set_aside_type', p_set_aside_type);
1774 PO_LOG.proc_begin(d_module, 'p_naics', p_naics);
1775 PO_LOG.proc_begin(d_module, 'p_size_stand', p_size_stand);
1776 PO_LOG.proc_begin(d_module, 'p_source_item', p_source_item);
1777
1778 END IF;
1779
1780 IF p_source_item = 'SET_ASIDE' THEN
1781 IF p_set_aside_stat = 'UNRESTRICTED' THEN
1782 p_set_aside_perc := '0';
1783 END IF;
1784 IF p_set_aside_stat = 'SET_ASIDE' THEN
1785 p_set_aside_perc := '100';
1786 END IF;
1787 IF p_set_aside_stat = 'UNRESTRICTED' THEN
1788 p_set_aside_type := NULL;
1789 END IF;
1790 IF p_set_aside_stat = 'SET_ASIDE' THEN
1791 p_set_aside_type := 'SMALL_BUSINESS';
1792 END IF;
1793 ELSE
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795 x_errorcode := '-1';
1796 x_msg_count := '1';
1797 x_msg_data := 'LOV Event is not from set_aside ';
1798 END IF;
1799
1800 x_return_status := FND_API.G_RET_STS_SUCCESS;
1801 x_errorcode := '0';
1802 x_msg_count := '1';
1803 x_msg_data := 'SUCCESS';
1804
1805 EXCEPTION
1806 WHEN OTHERS THEN
1807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1808 x_errorcode := '-2';
1809 x_msg_count := '1';
1810 x_msg_data := 'Exception in competitive_info_XLE';
1811 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
1812 END competitive_info_XLE;
1813
1814 FUNCTION get_supplier_contact_details(p_contact_id IN NUMBER, p_supplier_site_id IN NUMBER) RETURN VARCHAR2 IS
1815
1816 l_contact_xml_sql VARCHAR2(4000);
1817 p_hidd_contact_xml VARCHAR2(4000);
1818 d_proc_name CONSTANT varchar2(50) := 'get_supplier_contact_details';
1819 l_vendor_contact_first_name VARCHAR2(2000);
1820 l_vendor_contact_email_address VARCHAR2(2000);
1821 l_vendor_contact_phone VARCHAR2(2000);
1822 l_vendor_contact_id VARCHAR2(2000);
1823 l_vendor_contact_full_name VARCHAR2(2000);
1824 l_vendor_site_id VARCHAR2(2000);
1825 l_vendor_contact_fax VARCHAR2(2000);
1826 l_vendor_contact_last_name VARCHAR2(2000);
1827 l_user_name VARCHAR2(2000);
1828 l_vendor_contact_title VARCHAR2(2000);
1829 l_vendor_cnt_concat VARCHAR2(2000);
1830
1831
1832 BEGIN
1833
1834 SELECT pvc.first_name, pvc.email_address,
1835 DECODE(TRUNC(LENGTH(pvc.area_code || ' ' ||pvc.phone) / 26),0,pvc.area_code ||DECODE(pvc.area_code,NULL,NULL,
1836 DECODE(pvc.phone,NULL,NULL, ' ')) ||pvc.phone, pvc.area_code ||pvc.phone),
1837 pvc.vendor_contact_id,
1838 pvc.last_name ||NVL2(pvc.first_name,', ' ||NVL2(pvc.prefix,prefix ||' ',NULL) ||pvc.first_name,NULL),
1839 pvc.vendor_site_id,
1840 pvc.fax_area_code ||NVL2(pvc.fax_area_code,NVL2(pvc.fax,' ',NULL), NULL) ||pvc.fax,
1841 pvc.last_name,
1842 fu.user_name,
1843 pvc.title INTO l_vendor_contact_first_name, l_vendor_contact_email_address, l_vendor_contact_phone, l_vendor_contact_id, l_vendor_contact_full_name,
1844 l_vendor_site_id, l_vendor_contact_fax, l_vendor_contact_last_name, l_user_name, l_vendor_contact_title
1845 FROM po_vendor_contacts pvc,
1846 fnd_user fu
1847 WHERE pvc.per_party_id = fu.person_party_id(+) AND vendor_contact_id = p_contact_id and vendor_site_id = p_supplier_site_id;
1848
1849 l_vendor_cnt_concat := '';
1850
1851 IF l_vendor_contact_full_name IS NOT NULL THEN
1852 l_vendor_cnt_concat := l_vendor_contact_full_name;
1853 END IF;
1854
1855 IF (l_vendor_contact_phone IS NOT NULL AND l_vendor_cnt_concat IS NOT NULL) THEN
1856 l_vendor_cnt_concat := l_vendor_cnt_concat || ', ' || l_vendor_contact_phone;
1857 ELSIF l_vendor_contact_phone IS NOT NULL THEN
1858 l_vendor_cnt_concat := l_vendor_contact_phone;
1859 END IF;
1860
1861 IF (l_vendor_contact_email_address IS NOT NULL AND l_vendor_cnt_concat IS NOT NULL) THEN
1862 l_vendor_cnt_concat := l_vendor_cnt_concat || ', ' || l_vendor_contact_email_address;
1863 ELSIF l_vendor_contact_email_address IS NOT NULL THEN
1864 l_vendor_cnt_concat := l_vendor_contact_email_address;
1865 END IF;
1866
1867 l_contact_xml_sql := 'Select XMLFOREST (''' ||
1868 l_vendor_contact_first_name || '''VENDOR_CONTACT_FIRST_NAME , ''' ||
1869 l_vendor_contact_email_address || '''VENDOR_CONTACT_EMAIL_ADDRESS , ''' ||
1870 l_vendor_contact_phone || '''VENDOR_CONTACT_PHONE , ''' ||
1871 l_vendor_contact_id || '''VENDOR_CONTACT_ID , ''' ||
1872 l_vendor_contact_full_name || '''VENDOR_CONTACT_FULL_NAME , ''' ||
1873 l_vendor_site_id || '''VENDOR_SITE_ID , ''' ||
1874 l_vendor_contact_fax || '''VENDOR_CONTACT_FAX , ''' ||
1875 l_vendor_contact_last_name || '''VENDOR_CONTACT_LAST_NAME , ''' ||
1876 l_user_name || '''USER_NAME, ''' ||
1877 l_vendor_contact_title || '''VENDOR_CONTACT_TITLE, ''' ||
1878 l_vendor_cnt_concat || '''VENDOR_FCONTACT ' ||
1879 ' )FROM DUAL';
1880
1881 EXECUTE IMMEDIATE l_contact_xml_sql INTO p_hidd_contact_xml;
1882
1883 RETURN(p_hidd_contact_xml);
1884
1885 EXCEPTION
1886 WHEN No_Data_Found THEN
1887 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
1888 WHEN OTHERS THEN
1889 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
1890
1891 END get_supplier_contact_details;
1892
1893 FUNCTION get_supplier_addr_details(p_supplier_site_id IN NUMBER) RETURN VARCHAR2 IS
1894
1895 l_address_xml_sql VARCHAR2(4000);
1896 p_hidd_addr_xml VARCHAR2(4000);
1897 d_proc_name CONSTANT varchar2(50) := 'get_supplier_address_details';
1898 l_legal_buss_name VARCHAR2(2000);
1899 l_VENDOR_SITE_ID VARCHAR2(2000);
1900 l_VENDOR_SITE_CODE VARCHAR2(2000);
1901 l_vendor_addr_line1 VARCHAR2(2000);
1902 l_vendor_addr_line2 VARCHAR2(2000);
1903 l_vendor_addr_line3 VARCHAR2(2000);
1904 l_vendor_city VARCHAR2(2000);
1905 l_vendor_province_code VARCHAR2(2000);
1906 l_vendor_province VARCHAR2(2000);
1907 l_vendor_state_code VARCHAR2(2000);
1908 l_vendor_state VARCHAR2(2000);
1909 l_vendor_country_code VARCHAR2(2000);
1910 l_vendor_country VARCHAR2(2000);
1911 l_vendor_zip VARCHAR2(2000);
1912 l_doing_bus_name VARCHAR2(2000);
1913 l_vendor_name VARCHAR2(2000);
1914 l_vendor_site_name VARCHAR2(2000);
1915 l_vendor_concat_l VARCHAR2(2000);
1916 l_vendor_concat_l1 VARCHAR2(2000);
1917 l_vendor_concat_l2 VARCHAR2(2000);
1918
1919 BEGIN
1920 SELECT vsa.LEGAL_BUSINESS_NAME,
1921 vsa.DOING_BUS_AS_NAME,
1922 pvn.VENDOR_NAME,
1923 vsa.vendor_site_id,
1924 vsa.vendor_site_code,
1925 vsa.address_line1,
1926 vsa.address_line2,
1927 vsa.address_line3,
1928 vsa.city,
1929 nvl2(vsa.PROVINCE, vsa.PROVINCE, vsa.COUNTY),
1930 nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning),
1931 nvl2(vsa.STATE, vsa.STATE, vsa.PROVINCE),
1932 fcl2.meaning,
1933 vsa.Country,
1934 NVL(fte.TERRITORY_SHORT_NAME,vsa.COUNTRY),
1935 vsa.zip INTO l_legal_buss_name, l_doing_bus_name, l_vendor_name,l_VENDOR_SITE_ID, l_vendor_site_code, l_vendor_addr_line1, l_vendor_addr_line2, l_vendor_addr_line3,
1936 l_vendor_city, l_vendor_province_code, l_vendor_province, l_vendor_state_code, l_vendor_state, l_vendor_country_code,
1937 l_vendor_country,l_vendor_zip
1938 from PO_VENDOR_SITES_ALL vsa, po_vendors pvn,
1939 fnd_territories_tl fte,
1940 FND_LOOKUP_VALUES FCL1,
1941 FND_LOOKUP_VALUES FCL2,
1942 FND_LOOKUP_VALUES FCL3
1943 where vsa.vendor_site_id = p_supplier_site_id
1944 and vsa.vendor_id = pvn.vendor_id
1945 and vsa.country = fte.territory_code (+)
1946 AND DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG'))
1947 AND vsa.province = FCL1.LOOKUP_CODE (+)
1948 AND vsa.country || '_PROVINCE' = FCL1.LOOKUP_TYPE (+)
1949 AND DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.SECURITY_GROUP_ID)=
1950 DECODE(FCL1.LOOKUP_CODE, NULL, '1',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
1951 AND DECODE(FCL1.LOOKUP_CODE, NULL, '1', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, '1', 3)
1952 AND DECODE (FCL1.LOOKUP_CODE, NULL, '1', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, '1', USERENV('LANG'))
1953 AND vsa.state = FCL2.LOOKUP_CODE (+)
1954 AND vsa.country || '_STATE' = FCL2.LOOKUP_TYPE (+)
1955 AND DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.SECURITY_GROUP_ID)=
1956 DECODE(FCL2.LOOKUP_CODE, NULL, '1',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
1957 AND DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, '1', 3)
1958 AND DECODE(FCL2.LOOKUP_CODE, NULL, '1', FCL2.LANGUAGE)= DECODE(FCL2.LOOKUP_CODE, NULL, '1', USERENV('LANG'))
1959 AND vsa.county = FCL3.LOOKUP_CODE (+)
1960 AND vsa.country ||'_COUNTY' = FCL3.LOOKUP_TYPE (+)
1961 AND DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.SECURITY_GROUP_ID) =
1962 DECODE(FCL3.LOOKUP_CODE, NULL, '1',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
1963 AND DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, '1', 3)
1964 AND DECODE(FCL3.LOOKUP_CODE, NULL, '1', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, '1', USERENV('LANG'));
1965
1966
1967 IF l_legal_buss_name IS NOT NULL THEN
1968 l_vendor_site_name := l_legal_buss_name;
1969 ELSIF l_doing_bus_name IS NOT NULL THEN
1970 l_vendor_site_name := l_doing_bus_name;
1971 ELSE l_vendor_site_name := l_vendor_name;
1972 END IF;
1973 l_vendor_concat_l := '';
1974 l_vendor_concat_l1 := '';
1975 l_vendor_concat_l2 := '';
1976
1977 l_vendor_concat_l1 := l_vendor_site_name;
1978
1979 IF l_vendor_addr_line1 IS NOT NULL THEN
1980 l_vendor_concat_l1 := l_vendor_concat_l1 || newline|| l_vendor_addr_line1;
1981 END IF;
1982
1983 IF l_vendor_addr_line2 IS NOT NULL THEN
1984 l_vendor_concat_l1 := l_vendor_concat_l1 || ', ' || l_vendor_addr_line2;
1985 END IF;
1986
1987 IF l_vendor_addr_line3 IS NOT NULL THEN
1988 l_vendor_concat_l1 := l_vendor_concat_l1 || ', ' || l_vendor_addr_line3;
1989 END IF;
1990
1991 IF l_vendor_city IS NOT NULL THEN
1992 l_vendor_concat_l2 := l_vendor_city;
1993 END IF;
1994
1995 IF (l_vendor_state_code IS NOT NULL AND l_vendor_concat_l2 IS NOT NULL) THEN
1996 l_vendor_concat_l2 := l_vendor_concat_l2 || ', ' || l_vendor_state_code;
1997 ELSIF l_vendor_state_code IS NOT NULL THEN
1998 l_vendor_concat_l2 := l_vendor_state_code;
1999 END IF;
2000
2001 IF (l_vendor_zip IS NOT NULL AND l_vendor_concat_l2 IS NOT NULL) THEN
2002 l_vendor_concat_l2 := l_vendor_concat_l2 || ', ' || l_vendor_zip;
2003 ELSIF l_vendor_zip IS NOT NULL THEN
2004 l_vendor_concat_l2 := l_vendor_zip;
2005 END IF;
2006
2007 IF (l_vendor_country_code IS NOT NULL AND l_vendor_concat_l2 IS NOT NULL) THEN
2008 l_vendor_concat_l2 := l_vendor_concat_l2 || ', ' || l_vendor_country_code;
2009 ELSIF l_vendor_country_code IS NOT NULL THEN
2010 l_vendor_concat_l2 := l_vendor_country_code;
2011 END IF;
2012
2013 IF l_vendor_concat_l2 IS NOT NULL THEN
2014 l_vendor_concat_l := l_vendor_concat_l1 || newline|| l_vendor_concat_l2;
2015 ELSE l_vendor_concat_l := l_vendor_concat_l1;
2016 END IF;
2017
2018 l_address_xml_sql := 'Select XMLFOREST (''' ||
2019 l_vendor_site_name || '''VENDOR_SITE_NAME , ''' ||
2020 l_vendor_site_id || '''VENDOR_SITE_ID , ''' ||
2021 l_vendor_site_code || '''VENDOR_SITE_CODE , ''' ||
2022 l_vendor_addr_line1 || '''VENDOR_ADDRESS_LINE1 , ''' ||
2023 l_vendor_addr_line2 || '''VENDOR_ADDRESS_LINE2 , ''' ||
2024 l_vendor_addr_line3 || '''VENDOR_ADDRESS_LINE3 , ''' ||
2025 l_vendor_city || '''VENDOR_CITY , ''' ||
2026 l_vendor_province_code || '''VENDOR_PROVINCE_CODE , ''' ||
2027 l_vendor_province || '''VENDOR_PROVINCE , ''' ||
2028 l_vendor_state_code || '''VENDOR_STATE_CODE , ''' ||
2029 l_vendor_state || '''VENDOR_STATE , ''' ||
2030 l_vendor_country_code || '''VENDOR_COUNTRY_CODE , ''' ||
2031 l_vendor_country || '''VENDOR_COUNTRY , ''' ||
2032 l_vendor_zip || '''VENDOR_ZIP , ''' ||
2033 l_vendor_concat_l || '''VENDOR_FADDR ' ||
2034 ' )FROM DUAL';
2035
2036 EXECUTE IMMEDIATE l_address_xml_sql INTO p_hidd_addr_xml ;
2037
2038 RETURN(p_hidd_addr_xml);
2039
2040 EXCEPTION
2041 WHEN No_Data_Found THEN
2042 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2043 WHEN OTHERS THEN
2044 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2045
2046 END get_supplier_addr_details;
2047
2048 PROCEDURE supplier_details_XD
2049 (
2050 x_supplier_id IN NUMBER
2051 ,x_supplier_site_id IN NUMBER
2052 ,x_contact_id IN NUMBER
2053 ,x_org_id IN NUMBER
2054 ,p_doing_bsns_as IN OUT NOCOPY VARCHAR2
2055 ,p_suplr_size IN OUT NOCOPY VARCHAR2
2056 ,p_phy_addrs IN OUT NOCOPY VARCHAR2
2057 ,p_hidd_vendor_id IN OUT NOCOPY NUMBER
2058 ,p_hidd_org_id IN OUT NOCOPY NUMBER
2059 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
2060 ,p_hidd_remit_xml IN OUT NOCOPY VARCHAR
2061 ,p_addrs_dtls IN OUT NOCOPY VARCHAR2
2062 ,p_hidd_addrs_dtls_xml IN OUT NOCOPY VARCHAR2
2063 ,p_supplr_contact IN OUT NOCOPY VARCHAR2
2064 ,p_hidd_contact_xml IN OUT NOCOPY VARCHAR2
2065 ,p_ccr_reg_idictr IN OUT NOCOPY VARCHAR2
2066 ,p_phone_num IN OUT NOCOPY VARCHAR2
2067 ,p_ccr_reg_status IN OUT NOCOPY VARCHAR2
2068 ,p_email IN OUT NOCOPY VARCHAR2
2069 ,p_ccr_exp_reason IN OUT NOCOPY VARCHAR2
2070 ,p_duns_num IN OUT NOCOPY VARCHAR2
2071 ,p_cage_code IN OUT NOCOPY VARCHAR2
2072 ,p_duns_4_num IN OUT NOCOPY VARCHAR2
2073 ,p_tin_num IN OUT NOCOPY VARCHAR2
2074 ,p_eft_indctr IN OUT NOCOPY VARCHAR2
2075 ,p_eft_exc_resn IN OUT NOCOPY VARCHAR2
2076 ,p_socio_ec_info IN OUT NOCOPY VARCHAR2
2077 ,p_orig_splr_name IN OUT NOCOPY VARCHAR2
2078 ) IS
2079
2080 d_proc_name CONSTANT varchar2(50) := 'supplier_details_XD';
2081 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
2082 d_progress NUMBER(6);
2083
2084 TYPE varchar_table IS TABLE OF fnd_lookup_values_vl.meaning%TYPE INDEX BY PLS_INTEGER ;
2085 l_meanings_table varchar_table;
2086
2087 l_doing_bsns_as varchar2(100);
2088 l_suplr_size varchar2(100);
2089 l_phy_addrs varchar2(100);
2090 l_remit_to_addrs NUMBER(6) ;
2091 l_hidd_addrs_dtls_xml varchar2(100);
2092 l_addrs_dtls varchar2(100);
2093 l_supplr_contact varchar2(100);
2094 l_ccr_reg_idictr varchar2(100);
2095 l_phone_num varchar2(100);
2096 l_ccr_reg_status varchar2(100);
2097 l_email varchar2(100);
2098 l_ccr_exp_reason varchar2(100);
2099 l_duns_num varchar2(100);
2100 l_cage_code varchar2(100);
2101 l_duns_4_num varchar2(100);
2102 l_tin_num varchar2(100);
2103 l_eft_indctr varchar2(100);
2104 l_eft_exc_resn varchar2(100);
2105 l_socio_ec_info varchar2(4000);
2106 l_addressCode VARCHAR2(200);
2107 l_addressLine1 VARCHAR2(100);
2108 l_addressLine2 VARCHAR2(100);
2109 l_addressLine3 VARCHAR2(100);
2110 l_city VARCHAR2(100);
2111 l_state VARCHAR2(100);
2112 l_province VARCHAR2(100);
2113 l_country VARCHAR2(100);
2114 l_zipcode VARCHAR2(100);
2115 l_address_xml_sql varchar2(4000);
2116 l_contact_xml_sql VARCHAR2(4000);
2117 l_party_id NUMBER(8);
2118
2119
2120 BEGIN
2121 IF (PO_LOG.d_proc) THEN
2122 PO_LOG.proc_begin(d_module);
2123 PO_LOG.proc_begin(d_module, 'x_supplier_id', x_supplier_id);
2124 PO_LOG.proc_begin(d_module, 'x_supplier_site_id', x_supplier_site_id);
2125 PO_LOG.proc_begin(d_module, 'p_doing_bsns_as', p_doing_bsns_as);
2126 PO_LOG.proc_begin(d_module, 'p_suplr_size', p_suplr_size);
2127 PO_LOG.proc_begin(d_module, 'p_phy_addrs', p_phy_addrs);
2128 PO_LOG.proc_begin(d_module, 'p_remit_to_addrs', p_remit_to_addrs);
2129 PO_LOG.proc_begin(d_module, 'p_hidd_addrs_dtls_xml', p_hidd_addrs_dtls_xml);
2130 PO_LOG.proc_begin(d_module, 'p_addrs_dtls', p_addrs_dtls);
2131 PO_LOG.proc_begin(d_module, 'p_supplr_contact', p_supplr_contact);
2132 PO_LOG.proc_begin(d_module, 'p_ccr_reg_idictr', p_ccr_reg_idictr);
2133 PO_LOG.proc_begin(d_module, 'p_phone_num', p_phone_num);
2134 PO_LOG.proc_begin(d_module, 'p_ccr_reg_status', p_ccr_reg_status);
2135 PO_LOG.proc_begin(d_module, 'p_email', p_email);
2136 PO_LOG.proc_begin(d_module, 'p_ccr_exp_reason', p_ccr_exp_reason);
2137 PO_LOG.proc_begin(d_module, 'p_duns_num', p_duns_num);
2138 PO_LOG.proc_begin(d_module, 'p_cage_code ', p_cage_code );
2139 PO_LOG.proc_begin(d_module, 'p_duns_4_num', p_duns_4_num);
2140 PO_LOG.proc_begin(d_module, 'p_tin_num', p_tin_num);
2141 PO_LOG.proc_begin(d_module, 'p_ccr_reg_idictr', p_ccr_reg_idictr);
2142 PO_LOG.proc_begin(d_module, 'p_eft_exc_resn', p_eft_exc_resn);
2143 PO_LOG.proc_begin(d_module, 'p_socio_ec_info', p_socio_ec_info);
2144 PO_LOG.proc_begin(d_module, 'p_orig_splr_name', p_orig_splr_name);
2145 END IF;
2146
2147
2148 IF G_XLE_SUPPLIER_EVENT_TYPE LIKE 'ALL' THEN
2149
2150 IF x_supplier_id IS NOT NULL THEN
2151
2152 d_progress := 10;
2153 p_hidd_vendor_id := x_supplier_id;
2154 p_hidd_org_id := x_org_id;
2155
2156 BEGIN
2157
2158 SELECT vendor_name
2159 INTO p_orig_splr_name
2160 FROM po_vendors
2161 WHERE vendor_id = x_supplier_id;
2162
2163 SELECT DOING_BUS_AS_NAME,
2164 CAGE_CODE,
2165 DUNS_NUMBER,
2166 SMALL_BUSINESS_CODE,
2167 ADDRESS_LINE1,
2168 ADDRESS_LINE2,
2169 ADDRESS_LINE3,
2170 CITY,
2171 STATE,
2172 ZIP,
2173 PROVINCE,
2174 COUNTRY
2175 INTO l_doing_bsns_as,
2176 l_cage_code,
2177 l_duns_num,
2178 l_suplr_size,
2179 l_addressLine1,
2180 l_addressLine2,
2181 l_addressLine3,
2182 l_city,
2183 l_state,
2184 l_province,
2185 l_country,
2186 l_zipcode
2187 FROM AP_SUPPLIER_SITES_ALL
2188 WHERE vendor_id = x_supplier_id
2189 AND vendor_site_id = x_supplier_site_id;
2190
2191 d_progress := 20;
2192
2193 IF l_addressLine1 IS NOT NULL THEN p_phy_addrs := l_addressLine1 || ',' ; END IF ;
2194 IF l_addressLine2 IS NOT NULL THEN p_phy_addrs := p_phy_addrs ||l_addressLine2 || newline; END IF ;
2195 IF l_addressLine3 IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_addressLine3 || ','; END IF ;
2196 IF l_city IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_city || newline; END IF ;
2197 IF l_state IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_state || ','; END IF ;
2198 IF l_province IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_province || newline; END IF ;
2199 IF l_country IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_country || ','; END IF ;
2200 IF l_zipcode IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_zipcode || newline; END IF ;
2201
2202 IF Length(l_duns_num) = 13 THEN
2203 p_duns_num := SubStr(l_duns_num,1,9);
2204 p_duns_4_num := l_duns_num;
2205 ELSIF Length(l_duns_num) = 9 THEN
2206 p_duns_num := l_duns_num;
2207 p_duns_4_num := NULL ;
2208 END IF;
2209
2210 d_progress := 60;
2211 p_cage_code := l_cage_code;
2212 p_suplr_size := l_suplr_size;
2213 p_doing_bsns_as := l_doing_bsns_as;
2214
2215 BEGIN
2216 SELECT VENDOR_SITE_CODE,
2217 VENDOR_SITE_ID,
2218 ADDRESS_LINE1,
2219 ADDRESS_LINE2,
2220 ADDRESS_LINE3,
2221 CITY,
2222 STATE,
2223 ZIP,
2224 PROVINCE,
2225 COUNTRY
2226 INTO l_addressCode,
2227 p_remit_to_addrs,
2228 l_addressLine1,
2229 l_addressLine2,
2230 l_addressLine3,
2231 l_city,
2232 l_state,
2233 l_province,
2234 l_country,
2235 l_zipcode
2236 from AP_SUPPLIER_SITES_ALL
2237 where pay_site_flag = 'Y'
2238 and vendor_id = x_supplier_id
2239 AND vendor_site_id = x_supplier_site_id;
2240
2241 d_progress := 70;
2242
2243 IF l_addressLine1 IS NOT NULL THEN p_addrs_dtls := l_addressLine1 || ',' ; END IF ;
2244 IF l_addressLine2 IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls ||l_addressLine2 || newline; END IF ;
2245 IF l_addressLine3 IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_addressLine3 || ','; END IF ;
2246 IF l_city IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_city || newline; END IF ;
2247 IF l_state IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_state || ','; END IF ;
2248 IF l_province IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_province || newline; END IF ;
2249 IF l_country IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_country || ','; END IF ;
2250 IF l_zipcode IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_zipcode || newline; END IF ;
2251
2252 EXCEPTION
2253 WHEN No_Data_Found THEN
2254 d_progress := 90;
2255 WHEN OTHERS THEN
2256 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2257 d_progress := 95;
2258 END ;
2259 END ;
2260
2261 BEGIN
2262
2263 p_hidd_addrs_dtls_xml := get_supplier_addr_details(x_supplier_site_id);
2264 p_hidd_remit_xml := p_hidd_addrs_dtls_xml;
2265 d_progress := 80;
2266
2267 EXCEPTION
2268 WHEN No_Data_Found THEN
2269 d_progress := 90;
2270 WHEN OTHERS THEN
2271 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2272 d_progress := 95;
2273 END ;
2274
2275 l_ccr_reg_idictr := pos_util_pkg.IS_SITE_CCR(1.0,NULL,x_supplier_site_id);
2276 IF l_ccr_reg_idictr = 'T' THEN
2277 p_ccr_reg_idictr :='Yes';
2278 ELSE
2279 p_ccr_reg_idictr :='No';
2280 END IF ;
2281 d_progress := 100;
2282 l_ccr_reg_status := pos_util_pkg.IS_CCR_SITE_ACTIVE(1.0,NULL,x_supplier_site_id);
2283
2284 IF l_ccr_reg_status IS NULL OR l_ccr_reg_status = 'F' THEN
2285 l_ccr_reg_status := 'N';
2286 END IF ;
2287
2288 SELECT MEANING
2289 INTO p_ccr_reg_status
2290 FROM FND_LOOKUP_VALUES
2291 WHERE LOOKUP_TYPE='FV_CCR_REG_STATUS'
2292 AND LOOKUP_CODE = l_ccr_reg_status
2293 AND LANGUAGE = UserEnv('Lang');
2294
2295 d_progress := 110;
2296
2297 BEGIN
2298 SELECT party_id
2299 INTO l_party_id
2300 FROM po_vendors
2301 WHERE vendor_id = x_supplier_id;
2302
2303 SELECT flv.meaning
2304 BULK COLLECT INTO l_meanings_table
2305 FROM fnd_lookup_values_vl flv,
2306 POS_BUS_CLASS_ATTR pca
2307 WHERE flv.enabled_flag='Y'
2308 AND flv.start_date_active < sysdate
2309 AND (flv.end_date_active > sysdate or flv.end_date_active is null)
2310 AND flv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
2311 AND flv.lookup_type = pca.lookup_type
2312 AND flv.lookup_code = pca.lookup_code
2313 AND pca.party_id = l_party_id
2314 AND ( pca.end_date_active is null or trunc(pca.end_date_active) > sysdate )
2315 AND pca.status='A'
2316 AND pca.class_status = 'APPROVED'
2317 AND pca.classification_id not in
2318 (
2319 SELECT classification_id
2320 FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
2321 WHERE psm.party_id = pca.party_id
2322 AND psm.mapping_id = pbcr.mapping_id
2323 AND pbcr.request_status = 'PENDING'
2324 AND pbcr.request_type in ( 'ADD', 'UPDATE' )
2325 AND pbcr.classification_id is not null );
2326
2327 p_socio_ec_info := '';
2328 FOR i IN 1..l_meanings_table.Count LOOP
2329 p_socio_ec_info := p_socio_ec_info || l_meanings_table(i) || newline;
2330 END LOOP ;
2331 EXCEPTION
2332 WHEN No_Data_Found THEN
2333 d_progress := 120;
2334 WHEN OTHERS THEN
2335 d_progress := 125;
2336 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2337 END;
2338 BEGIN
2339 IF p_ccr_reg_idictr ='T' THEN
2340 SELECT Taxpayer_id
2341 INTO l_tin_num
2342 FROM FV_CCR_VENDORS
2343 WHERE vendor_id = x_supplier_id;
2344 ELSE
2345 SELECT jgzz_fiscal_code
2346 INTO l_tin_num
2347 FROM HZ_Parties
2348 WHERE PARTY_ID = l_party_id;
2349 d_progress := 130;
2350 END IF;
2351
2352 p_tin_num := l_tin_num;
2353
2354 EXCEPTION
2355 WHEN No_Data_Found THEN
2356 d_progress := 140;
2357 WHEN OTHERS THEN
2358 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2359 END ;
2360 END IF;
2361 END IF ;
2362
2363 IF G_XLE_SUPPLIER_EVENT_TYPE LIKE 'ALL' OR G_XLE_SUPPLIER_EVENT_TYPE LIKE 'SUPPLIER_CONTACT' THEN
2364
2365 G_XLE_SUPPLIER_EVENT_TYPE := 'ALL' ; --reset to default 'ALL' irrespective of the event type
2366
2367 BEGIN
2368 IF x_contact_id IS NOT NULL AND x_supplier_site_id IS NOT NULL THEN
2369 SELECT phone,email_address,
2370 Nvl(PREFIX,PREFIX ||' ')|| Nvl(FIRST_NAME,FIRST_NAME ||' ') || Nvl(MIDDLE_NAME,MIDDLE_NAME||' ') || LAST_NAME
2371 INTO l_phone_num,
2372 l_email,
2373 l_supplr_contact
2374 FROM PO_VENDOR_CONTACTS
2375 WHERE vendor_contact_id = x_contact_id
2376 AND vendor_site_id = x_supplier_site_id;
2377
2378 p_hidd_contact_xml := get_supplier_contact_details(x_contact_id,x_supplier_site_id);
2379 ELSE
2380 p_hidd_contact_xml := NULL;
2381 END IF ;
2382
2383 EXCEPTION
2384 WHEN No_Data_Found THEN
2385 d_progress := 40;
2386 WHEN OTHERS THEN
2387 d_progress := 45;
2388 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2389 END;
2390
2391 p_phone_num := l_phone_num;
2392 p_email := l_email;
2393 p_supplr_contact := l_supplr_contact;
2394 END IF;
2395
2396 d_progress := 150;
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
2400
2401
2402 END supplier_details_XD;
2403
2404 PROCEDURE supplier_details_XSC
2405 (
2406 p_eft_indctr IN OUT NOCOPY VARCHAR2
2407 ,p_eft_exc_resn IN OUT NOCOPY VARCHAR2
2408 ,p_suplr_size IN OUT NOCOPY VARCHAR2
2409 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
2410 ,p_ccr_reg_idictr IN OUT NOCOPY VARCHAR2
2411 ,p_ccr_reg_status IN OUT NOCOPY VARCHAR2
2412 ,p_ccr_exp_reason IN OUT NOCOPY VARCHAR2
2413 ,x_return_status OUT NOCOPY VARCHAR2
2414 ,x_errorcode OUT NOCOPY VARCHAR2
2415 ,x_msg_count OUT NOCOPY VARCHAR2
2416 ,x_msg_data OUT NOCOPY VARCHAR2
2417 )
2418
2419 IS
2420
2421 d_proc_name CONSTANT varchar2(50) := 'supplier_details_XSC';
2422 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
2423 l_msg_count NUMBER(6) := 0;
2424
2425 BEGIN
2426 IF (PO_LOG.d_proc) THEN
2427 PO_LOG.proc_begin(d_module);
2428 PO_LOG.proc_begin(d_module, 'p_eft_indctr', p_eft_indctr);
2429 PO_LOG.proc_begin(d_module, 'p_eft_exc_resn', p_eft_exc_resn);
2430 PO_LOG.proc_begin(d_module, 'p_suplr_size', p_suplr_size);
2431 PO_LOG.proc_begin(d_module, 'p_remit_to_addrs', p_remit_to_addrs);
2432 PO_LOG.proc_begin(d_module, 'p_ccr_exp_reason', p_ccr_exp_reason);
2433 PO_LOG.proc_begin(d_module, 'p_ccr_reg_status', p_ccr_reg_status);
2434 PO_LOG.proc_begin(d_module, 'p_ccr_reg_idictr', p_ccr_reg_idictr);
2435
2436 END IF;
2437
2438 x_return_status := FND_API.G_RET_STS_SUCCESS;
2439 x_errorcode := '0';
2440 x_msg_count := '1';
2441 x_msg_data := 'SUCCESS';
2442
2443 IF p_ccr_reg_idictr ='No' AND p_ccr_exp_reason IS NULL THEN
2444 x_return_status := FND_API.G_RET_STS_ERROR;
2445 x_errorcode := '-1';
2446 l_msg_count := l_msg_count + 1;
2447 x_msg_data := 'A CCR Exception Reason must be entered when the Vendor is not registered within CCR.';
2448 fnd_message.set_name(d_appln_short_name, 'PO_VEN_NOT_REG_CCR_EXC_REASON');
2449 fnd_msg_pub.ADD;
2450 END IF ;
2451 -- rather than checking for 'Inactive' status, we must check for 'Expired' status.
2452 IF p_ccr_reg_status ='Expired' AND p_ccr_exp_reason IS NULL THEN
2453 x_return_status := FND_API.G_RET_STS_ERROR;
2454 x_errorcode := '-1';
2455 l_msg_count := l_msg_count + 1;
2456 x_msg_data := 'A CCR Exception Reason must be provided when the vendors CCR registration has expired.';
2457 fnd_message.set_name(d_appln_short_name, 'PO_REG_EXP_CCR_EXC_REASON');
2458 fnd_msg_pub.ADD ;
2459 END IF ;
2460 IF p_eft_indctr = 'N' AND p_eft_exc_resn IS NULL THEN
2461 x_return_status := FND_API.G_RET_STS_ERROR;
2462 x_errorcode := '-1';
2463 l_msg_count := l_msg_count + 1;
2464 x_msg_data := 'When EFT does not apply, an EFT exclusion reason must be selected.';
2465 fnd_message.set_name(d_appln_short_name, 'PO_NO_EFT_REASON_SELECTED');
2466 fnd_msg_pub.ADD ;
2467 END IF ;
2468
2469 IF l_msg_count > 0 THEN
2470 x_msg_count := l_msg_count;
2471 END IF ;
2472
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2476 x_errorcode := '-2';
2477 x_msg_count := '1';
2478 x_msg_data := 'Exception in addresses_XSC';
2479 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2480
2481 END supplier_details_XSC;
2482
2483
2484
2485 PROCEDURE supplier_details_XLE
2486 (
2487 x_supplier_id IN NUMBER
2488 ,x_supplier_site_id IN NUMBER
2489 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
2490 ,p_addrs_dtls IN OUT NOCOPY VARCHAR2
2491 ,p_hidd_remit_xml IN OUT NOCOPY VARCHAR2
2492 ,p_orig_splr_name IN OUT NOCOPY VARCHAR2
2493 ,x_return_status OUT NOCOPY VARCHAR2
2494 ,x_errorcode OUT NOCOPY VARCHAR2
2495 ,x_msg_count OUT NOCOPY VARCHAR2
2496 ,x_msg_data OUT NOCOPY VARCHAR2
2497 )
2498 IS
2499
2500 d_proc_name CONSTANT varchar2(50) := 'supplier_details_XLE';
2501 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
2502 d_progress NUMBER(6);
2503
2504 l_addressLine1 VARCHAR2(100);
2505 l_addressLine2 VARCHAR2(100);
2506 l_addressLine3 VARCHAR2(100);
2507 l_city VARCHAR2(100);
2508 l_state VARCHAR2(100);
2509 l_province VARCHAR2(100);
2510 l_country VARCHAR2(100);
2511 l_zipcode VARCHAR2(100);
2512 l_address_xml_sql VARCHAR2(4000);
2513 l_addresscode VARCHAR2(100);
2514
2515 BEGIN
2516 IF (PO_LOG.d_proc) THEN
2517 PO_LOG.proc_begin(d_module);
2518 PO_LOG.proc_begin(d_module, 'x_supplier_id', x_supplier_id);
2519 PO_LOG.proc_begin(d_module, 'x_supplier_site_id', x_supplier_site_id);
2520 PO_LOG.proc_begin(d_module, 'p_remit_to_addrs', p_remit_to_addrs);
2521 PO_LOG.proc_begin(d_module, 'p_hidd_addrs_dtls_xml', p_hidd_remit_xml);
2522 PO_LOG.proc_begin(d_module, 'p_addrs_dtls', p_addrs_dtls);
2523 PO_LOG.proc_begin(d_module, 'p_orig_splr_name', p_orig_splr_name);
2524 END IF;
2525
2526
2527 IF p_remit_to_addrs IS NOT NULL THEN
2528 BEGIN
2529 SELECT VENDOR_SITE_CODE,
2530 ADDRESS_LINE1,
2531 ADDRESS_LINE2,
2532 ADDRESS_LINE3,
2533 CITY,
2534 STATE,
2535 ZIP,
2536 PROVINCE,
2537 COUNTRY
2538 INTO l_addresscode,
2539 l_addressLine1,
2540 l_addressLine2,
2541 l_addressLine3,
2542 l_city,
2543 l_state,
2544 l_province,
2545 l_country,
2546 l_zipcode
2547 from AP_SUPPLIER_SITES_ALL
2548 where pay_site_flag = 'Y'
2549 and vendor_id = x_supplier_id
2550 AND VENDOR_SITE_ID = p_remit_to_addrs;
2551
2552 p_addrs_dtls := NULL ;
2553 IF l_addressLine1 IS NOT NULL THEN p_addrs_dtls := l_addressLine1 || ',' ; END IF ;
2554 IF l_addressLine2 IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls ||l_addressLine2 || newline; END IF ;
2555 IF l_addressLine3 IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_addressLine3 || ','; END IF ;
2556 IF l_city IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_city || newline; END IF ;
2557 IF l_state IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_state || ','; END IF ;
2558 IF l_province IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_province || newline; END IF ;
2559 IF l_country IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_country || ','; END IF ;
2560 IF l_zipcode IS NOT NULL THEN p_addrs_dtls := p_addrs_dtls || l_zipcode || newline; END IF ;
2561
2562 EXCEPTION
2563 WHEN No_Data_Found THEN
2564 p_addrs_dtls := NULL ;
2565 WHEN OTHERS THEN
2566 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2567 END ;
2568
2569 BEGIN
2570
2571 p_hidd_remit_xml := get_supplier_addr_details(p_remit_to_addrs);
2572
2573 EXCEPTION
2574 WHEN No_Data_Found THEN
2575 p_addrs_dtls := NULL ;
2576 WHEN OTHERS THEN
2577 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2578
2579 END ;
2580
2581 ELSE
2582 p_addrs_dtls := NULL ;
2583 END IF ;
2584
2585 BEGIN
2586 SELECT vendor_name
2587 INTO p_orig_splr_name
2588 FROM po_vendors
2589 WHERE vendor_id = x_supplier_id;
2590 EXCEPTION
2591 WHEN No_Data_Found THEN
2592 p_orig_splr_name := NULL;
2593 WHEN OTHERS THEN
2594 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2595 END ;
2596
2597 x_return_status := FND_API.G_RET_STS_SUCCESS;
2598 x_errorcode := '0';
2599 x_msg_count := '1';
2600 x_msg_data := 'SUCCESS';
2601
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2605 x_errorcode := '-2';
2606 x_msg_count := '1';
2607 x_msg_data := 'Exception in supplier_details_XLE';
2608 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2609
2610
2611 IF (PO_LOG.d_exc) THEN
2612 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data ||': '|| SQLERRM );
2613 PO_LOG.proc_end(d_module);
2614 END IF;
2615
2616 END supplier_details_XLE;
2617
2618 PROCEDURE supplier_details_XRD
2619 (
2620 po_header_id IN NUMBER
2621 ,draft_id IN NUMBER
2622 ,p_doing_bsns_as IN OUT NOCOPY VARCHAR2
2623 ,p_suplr_size IN OUT NOCOPY VARCHAR2
2624 ,p_phy_addrs IN OUT NOCOPY VARCHAR2
2625 ,p_remit_to_addrs IN OUT NOCOPY NUMBER
2626 ,p_addrs_dtls IN OUT NOCOPY VARCHAR2
2627 ,p_hidd_addrs_dtls_xml IN OUT NOCOPY VARCHAR2
2628 ,p_supplr_contact IN OUT NOCOPY VARCHAR2
2629 ,p_hidd_contact_xml IN OUT NOCOPY VARCHAR2
2630 ,p_ccr_reg_idictr IN OUT NOCOPY VARCHAR2
2631 ,p_phone_num IN OUT NOCOPY VARCHAR2
2632 ,p_ccr_reg_status IN OUT NOCOPY VARCHAR2
2633 ,p_email IN OUT NOCOPY VARCHAR2
2634 ,p_ccr_exp_reason IN OUT NOCOPY VARCHAR2
2635 ,p_duns_num IN OUT NOCOPY VARCHAR2
2636 ,p_cage_code IN OUT NOCOPY VARCHAR2
2637 ,p_duns_4_num IN OUT NOCOPY VARCHAR2
2638 ,p_tin_num IN OUT NOCOPY VARCHAR2
2639 ,p_eft_indctr IN OUT NOCOPY VARCHAR2
2640 ,p_eft_exc_resn IN OUT NOCOPY VARCHAR2
2641 ,p_socio_ec_info IN OUT NOCOPY VARCHAR2
2642 ,p_orig_splr_name IN OUT NOCOPY VARCHAR2
2643 ,x_return_status OUT NOCOPY VARCHAR2
2644 ,x_errorcode OUT NOCOPY VARCHAR2
2645 ,x_msg_count OUT NOCOPY VARCHAR2
2646 ,x_msg_data OUT NOCOPY VARCHAR2
2647
2648 ) IS
2649
2650 d_proc_name CONSTANT varchar2(50) := 'supplier_details_XRD';
2651 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
2652
2653 TYPE varchar_table IS TABLE OF fnd_lookup_values_vl.meaning%TYPE INDEX BY PLS_INTEGER ;
2654 l_meanings_table varchar_table;
2655
2656 l_supplier_id NUMBER(10);
2657 l_supplier_site_id NUMBER(10);
2658 l_contact_id NUMBER(10);
2659 l_doing_bsns_as varchar2(100);
2660 l_suplr_size varchar2(100);
2661 l_phy_addrs varchar2(100);
2662 l_remit_to_addrs NUMBER(6);
2663 l_hidd_addrs_dtls_xml varchar2(100);
2664 l_addrs_dtls varchar2(100);
2665 l_supplr_contact varchar2(100);
2666 l_ccr_reg_idictr varchar2(100);
2667 l_phone_num varchar2(100);
2668 l_ccr_reg_status varchar2(100);
2669 l_email varchar2(100);
2670 l_ccr_exp_reason varchar2(100);
2671 l_duns_num varchar2(100);
2672 l_cage_code varchar2(100);
2673 l_duns_4_num varchar2(100);
2674 l_tin_num varchar2(100);
2675 l_eft_indctr varchar2(100);
2676 l_eft_exc_resn varchar2(100);
2677 l_socio_ec_info varchar2(100);
2678 l_addressLine1 VARCHAR2(100);
2679 l_addressLine2 VARCHAR2(100);
2680 l_addressLine3 VARCHAR2(100);
2681 l_city VARCHAR2(100);
2682 l_state VARCHAR2(100);
2683 l_province VARCHAR2(100);
2684 l_country VARCHAR2(100);
2685 l_zipcode VARCHAR2(100);
2686 l_po_header_id NUMBER(8) := po_header_id ;
2687 l_party_id NUMBER(8);
2688 l_contact_xml_sql VARCHAR2(4000);
2689 l_address_xml_sql VARCHAR2(6000);
2690 d_progress NUMBER(6);
2691 l_clm_special_cont_type VARCHAR2(100);
2692
2693
2694 BEGIN
2695 IF (PO_LOG.d_proc) THEN
2696 PO_LOG.proc_begin(d_module);
2697 PO_LOG.proc_begin(d_module, 'po_header_id', po_header_id);
2698 PO_LOG.proc_begin(d_module, 'draft_id', draft_id);
2699 PO_LOG.proc_begin(d_module, 'p_doing_bsns_as', p_doing_bsns_as);
2700 PO_LOG.proc_begin(d_module, 'p_suplr_size', p_suplr_size);
2701 PO_LOG.proc_begin(d_module, 'p_phy_addrs', p_phy_addrs);
2702 PO_LOG.proc_begin(d_module, 'p_remit_to_addrs', p_remit_to_addrs);
2703 PO_LOG.proc_begin(d_module, 'p_hidd_addrs_dtls_xml', p_hidd_addrs_dtls_xml);
2704 PO_LOG.proc_begin(d_module, 'p_addrs_dtls', p_addrs_dtls);
2705 PO_LOG.proc_begin(d_module, 'p_supplr_contact', p_supplr_contact);
2706 PO_LOG.proc_begin(d_module, 'p_ccr_reg_idictr', p_ccr_reg_idictr);
2707 PO_LOG.proc_begin(d_module, 'p_phone_num', p_phone_num);
2708 PO_LOG.proc_begin(d_module, 'p_ccr_reg_status', p_ccr_reg_status);
2709 PO_LOG.proc_begin(d_module, 'p_email', p_email);
2710 PO_LOG.proc_begin(d_module, 'p_ccr_exp_reason', p_ccr_exp_reason);
2711 PO_LOG.proc_begin(d_module, 'p_duns_num', p_duns_num);
2712 PO_LOG.proc_begin(d_module, 'p_cage_code ', p_cage_code );
2713 PO_LOG.proc_begin(d_module, 'p_duns_4_num', p_duns_4_num);
2714 PO_LOG.proc_begin(d_module, 'p_tin_num', p_tin_num);
2715 PO_LOG.proc_begin(d_module, 'p_ccr_reg_idictr', p_ccr_reg_idictr);
2716 PO_LOG.proc_begin(d_module, 'p_eft_exc_resn', p_eft_exc_resn);
2717 PO_LOG.proc_begin(d_module, 'p_socio_ec_info', p_socio_ec_info);
2718 PO_LOG.proc_begin(d_module, 'p_orig_splr_name', p_orig_splr_name);
2719 END IF;
2720
2721 BEGIN
2722 IF(draft_id IS NULL OR draft_id = -1 OR draft_id = 0) THEN
2723 SELECT vendor_id,vendor_site_id,vendor_contact_id
2724 INTO l_supplier_id,l_supplier_site_id,l_contact_id
2725 FROM po_headers_all
2726 WHERE po_header_id = l_po_header_id;
2727
2728 ELSE
2729 SELECT vendor_id,vendor_site_id,vendor_contact_id
2730 INTO l_supplier_id,l_supplier_site_id,l_contact_id
2731 FROM po_headers_draft_all
2732 WHERE po_header_id = l_po_header_id
2733 AND draft_id = draft_id;
2734
2735 END IF;
2736 EXCEPTION
2737 WHEN No_Data_Found THEN
2738 NULL ;
2739 WHEN OTHERS THEN
2740 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2741 END ;
2742
2743 BEGIN
2744
2745 SELECT vendor_name
2746 INTO p_orig_splr_name
2747 FROM po_vendors
2748 WHERE vendor_id = l_supplier_id;
2749
2750 SELECT DOING_BUS_AS_NAME,
2751 CAGE_CODE,
2752 DUNS_NUMBER,
2753 SMALL_BUSINESS_CODE,
2754 ADDRESS_LINE1,
2755 ADDRESS_LINE2,
2756 ADDRESS_LINE3,
2757 CITY,
2758 STATE,
2759 ZIP,
2760 PROVINCE,
2761 COUNTRY
2762 INTO l_doing_bsns_as,
2763 l_cage_code,
2764 l_duns_num,
2765 l_suplr_size,
2766 l_addressLine1,
2767 l_addressLine2,
2768 l_addressLine3,
2769 l_city,
2770 l_state,
2771 l_province,
2772 l_country,
2773 l_zipcode
2774 FROM AP_SUPPLIER_SITES_ALL
2775 WHERE vendor_id = l_supplier_id
2776 AND vendor_site_id = l_supplier_site_id;
2777
2778 BEGIN
2779
2780 p_hidd_addrs_dtls_xml := get_supplier_addr_details(l_supplier_site_id);
2781
2782
2783 EXCEPTION
2784 WHEN No_Data_Found THEN
2785 p_addrs_dtls := NULL ;
2786 WHEN OTHERS THEN
2787 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2788
2789 END ;
2790
2791 BEGIN
2792
2793 --Bug 14241578 - moved query under begin-exception block as it is likely to throw no data found.
2794 SELECT phone,email_address,
2795 Nvl(PREFIX,PREFIX ||' ')|| Nvl(FIRST_NAME,FIRST_NAME ||' ') || Nvl(MIDDLE_NAME,MIDDLE_NAME||' ') || LAST_NAME
2796 INTO l_phone_num,
2797 l_email,
2798 l_supplr_contact
2799 FROM PO_VENDOR_CONTACTS
2800 WHERE vendor_contact_id = l_contact_id
2801 AND vendor_site_id = l_supplier_site_id;
2802
2803 p_hidd_contact_xml := get_supplier_contact_details(l_contact_id,l_supplier_site_id);
2804
2805 EXCEPTION
2806 WHEN No_Data_Found THEN
2807 d_progress := 40;
2808 p_hidd_contact_xml := NULL; --Bug 14241578 - setting contact xml as null ,when no contact specifed.
2809 WHEN OTHERS THEN
2810 d_progress := 45;
2811 END ;
2812 IF l_addressLine1 IS NOT NULL THEN p_phy_addrs := l_addressLine1 || ',' ; END IF ;
2813 IF l_addressLine2 IS NOT NULL THEN p_phy_addrs := p_phy_addrs ||l_addressLine2 || newline; END IF ;
2814 IF l_addressLine3 IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_addressLine3 || ','; END IF ;
2815 IF l_city IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_city || newline; END IF ;
2816 IF l_state IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_state || ','; END IF ;
2817 IF l_province IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_province || newline; END IF ;
2818 IF l_country IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_country || ','; END IF ;
2819 IF l_zipcode IS NOT NULL THEN p_phy_addrs := p_phy_addrs || l_zipcode || newline; END IF ;
2820
2821 IF Length(l_duns_num) = 13 THEN
2822 p_duns_num := SubStr(l_duns_num,1,9);
2823 p_duns_4_num := l_duns_num;
2824 ELSIF Length(l_duns_num) = 9 THEN
2825 p_duns_num := l_duns_num;
2826 p_duns_4_num := NULL ;
2827 END IF;
2828
2829 p_cage_code := l_cage_code;
2830 p_suplr_size := l_suplr_size;
2831 p_phone_num := l_phone_num;
2832 p_email := l_email;
2833 p_supplr_contact := l_supplr_contact;
2834 p_doing_bsns_as := l_doing_bsns_as;
2835
2836 EXCEPTION
2837 WHEN No_Data_Found THEN
2838 NULL ;
2839 WHEN OTHERS THEN
2840 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2841
2842 END ;
2843 l_ccr_reg_idictr := pos_util_pkg.IS_SITE_CCR(1.0,NULL,l_supplier_site_id);
2844 IF l_ccr_reg_idictr = 'T' THEN
2845 p_ccr_reg_idictr :='Yes';
2846 ELSE
2847 p_ccr_reg_idictr :='No';
2848 END IF ;
2849 l_ccr_reg_status := pos_util_pkg.IS_CCR_SITE_ACTIVE(1.0,NULL,l_supplier_site_id);
2850 IF l_ccr_reg_status IS NULL OR l_ccr_reg_status = 'F' THEN
2851 l_ccr_reg_status := 'N';
2852 END IF ;
2853
2854 SELECT MEANING
2855 INTO p_ccr_reg_status
2856 FROM FND_LOOKUP_VALUES
2857 WHERE LOOKUP_TYPE='FV_CCR_REG_STATUS'
2858 AND LOOKUP_CODE = l_ccr_reg_status
2859 AND LANGUAGE = UserEnv('Lang');
2860
2861 -- Sunset Memo : If supplier ccr registration staus is either inactive or not registered, ccr exception reason must be populated.
2862 -- if Award is of Special Contract Type.
2863 IF l_ccr_reg_status = 'E' OR p_ccr_reg_idictr ='No' THEN
2864 IF(draft_id IS NULL OR draft_id = -1 OR draft_id = 0) THEN
2865 SELECT clm_special_contract_type
2866 INTO l_clm_special_cont_type
2867 FROM po_headers_all poh
2868 WHERE poh.po_header_id = l_po_header_id;
2869 END IF;
2870 -- Get CCR exception reason from global contract type object in PO_DRAFTS_PVT
2871 IF l_clm_special_cont_type IS NOT NULL THEN
2872 p_ccr_exp_reason := PO_CORE_S3.get_ccr_exception_reason(l_clm_special_cont_type);
2873 END IF;
2874 END IF;
2875
2876 BEGIN
2877 SELECT party_id
2878 INTO l_party_id
2879 FROM po_vendors
2880 WHERE vendor_id = l_supplier_id;
2881
2882 SELECT flv.meaning
2883 BULK COLLECT INTO l_meanings_table
2884 FROM fnd_lookup_values_vl flv,
2885 POS_BUS_CLASS_ATTR pca
2886 WHERE flv.enabled_flag='Y'
2887 AND flv.start_date_active < sysdate
2888 AND (flv.end_date_active > sysdate or flv.end_date_active is null)
2889 AND flv.lookup_type = 'POS_BUSINESS_CLASSIFICATIONS'
2890 AND flv.lookup_type = pca.lookup_type
2891 AND flv.lookup_code = pca.lookup_code
2892 AND pca.party_id = l_party_id
2893 AND ( pca.end_date_active is null or trunc(pca.end_date_active) > sysdate )
2894 AND pca.status='A'
2895 AND pca.class_status = 'APPROVED'
2896 AND pca.classification_id not in
2897 (
2898 SELECT classification_id
2899 FROM pos_bus_class_reqs pbcr, pos_supplier_mappings psm
2900 WHERE psm.party_id = pca.party_id
2901 AND psm.mapping_id = pbcr.mapping_id
2902 AND pbcr.request_status = 'PENDING'
2903 AND pbcr.request_type in ( 'ADD', 'UPDATE' )
2904 AND pbcr.classification_id is not null
2905 );
2906
2907 p_socio_ec_info := '';
2908 FOR i IN 1..l_meanings_table.Count LOOP
2909 p_socio_ec_info := p_socio_ec_info || l_meanings_table(i) || newline;
2910 END LOOP ;
2911 EXCEPTION
2912 WHEN No_Data_Found THEN
2913 NULL;
2914 WHEN OTHERS THEN
2915 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2916 END;
2917
2918 BEGIN
2919 IF p_ccr_reg_idictr ='T' THEN
2920 SELECT Taxpayer_id
2921 INTO l_tin_num
2922 FROM FV_CCR_VENDORS
2923 WHERE vendor_id = l_supplier_id;
2924 ELSE
2925 SELECT jgzz_fiscal_code
2926 INTO l_tin_num
2927 FROM HZ_Parties
2928 WHERE PARTY_ID = l_party_id;
2929 END IF;
2930 p_tin_num := l_tin_num;
2931 EXCEPTION
2932 WHEN No_Data_Found THEN
2933 NULL;
2934 WHEN OTHERS THEN
2935 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2936 END ;
2937
2938 x_return_status := FND_API.G_RET_STS_SUCCESS;
2939 x_errorcode := '0';
2940 x_msg_count := '1';
2941 x_msg_data := 'SUCCESS';
2942
2943 EXCEPTION
2944 WHEN OTHERS THEN
2945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2946 x_errorcode := '-2';
2947 x_msg_count := '1';
2948 x_msg_data := 'Exception in supplier_details_XRD';
2949 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
2950
2951 IF (PO_LOG.d_exc) THEN
2952 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data ||': '|| SQLERRM );
2953 PO_LOG.proc_end(d_module);
2954 END IF;
2955
2956 END supplier_details_XRD;
2957
2958 FUNCTION create_address_xml(p_sql_stmt VARCHAR2) RETURN CLOB
2959 IS
2960 queryCtx DBMS_XMLGEN.ctxType;
2961 result CLOB;
2962 BEGIN
2963 queryCtx := DBMS_XMLGEN.newContext(p_sql_stmt);
2964
2965 DBMS_XMLGEN.setRowTag(
2966 queryCtx
2967 , NULL
2968 );
2969 DBMS_XMLGEN.setRowSetTag(
2970 queryCtx
2971 , 'ADDRESS'
2972 );
2973
2974 --DBMS_XMLGEN.useNullAttributeIndicator(queryCtx,TRUE);
2975
2976 result := DBMS_XMLGEN.getXml(queryCtx);
2977
2978 DBMS_XMLGEN.closeContext(queryCtx);
2979 RETURN result;
2980
2981 EXCEPTION
2982 WHEN OTHERS THEN
2983 RETURN NULL;
2984 END create_address_xml;
2985
2986 PROCEDURE po_address_XRD (
2987 x_location IN OUT NOCOPY NUMBER
2988 ,x_addresscode IN OUT NOCOPY NUMBER
2989 ,x_contact_id IN OUT NOCOPY NUMBER
2990 ,x_addressdetails IN OUT NOCOPY VARCHAR2
2991 ,x_contactdetails IN OUT NOCOPY VARCHAR2
2992 ,x_addressdtlsxml IN OUT NOCOPY VARCHAR2
2993 ,x_contactdtlsxml IN OUT NOCOPY VARCHAR2
2994 ,x_hiddenCountry IN OUT NOCOPY VARCHAR2
2995 ,x_hiddenZipCode IN OUT NOCOPY VARCHAR2
2996 ,x_hiddenAddType IN VARCHAR2
2997 ,x_hiddenLKPType IN VARCHAR2
2998 ,x_return_status OUT NOCOPY VARCHAR2
2999 ,x_errorcode OUT NOCOPY VARCHAR2
3000 ,x_msg_count OUT NOCOPY VARCHAR2
3001 ,x_msg_data OUT NOCOPY VARCHAR2
3002 ) IS
3003
3004 d_proc_name CONSTANT varchar2(50) := 'addresses_XRD';
3005 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3006
3007 l_full_name VARCHAR2(1000);
3008 l_position VARCHAR2(1000);
3009 l_phone_number VARCHAR2(50);
3010 l_email VARCHAR2(50);
3011
3012 l_addr_line_1 VARCHAR2(2000);
3013 l_addr_line_2 VARCHAR2(2000);
3014 l_town_or_city VARCHAR2(1000);
3015 l_region2 VARCHAR2(1000);
3016 l_style VARCHAR2(500);
3017 l_postal_code VARCHAR2(50);
3018 l_state VARCHAR2(100);
3019 l_addType varchar2(200) ;
3020 l_functional_area VARCHAR2(100);
3021
3022
3023 BEGIN
3024 IF (PO_LOG.d_proc) THEN
3025 PO_LOG.proc_begin(d_module);
3026 PO_LOG.proc_begin(d_module, 'x_location', x_location);
3027 PO_LOG.proc_begin(d_module, 'x_addresscode', x_addresscode);
3028 PO_LOG.proc_begin(d_module, 'x_contact_id', x_contact_id);
3029 END IF;
3030
3031 IF x_location IS NOT NULL THEN
3032 x_addresscode := x_location;
3033 ELSIF x_addresscode IS NOT NULL THEN
3034 x_location := x_addresscode;
3035 END IF ;
3036
3037 IF x_location IS NOT NULL THEN
3038
3039
3040 l_addType := x_hiddenAddType;
3041 l_functional_area :=NULL;
3042
3043 address_XD(
3044 l_addType
3045 ,x_location
3046 ,x_addresscode
3047 ,x_contact_id
3048 ,x_addressdetails
3049 ,x_contactdetails
3050 ,x_addressdtlsxml
3051 ,x_contactdtlsxml
3052 ,x_hiddenCountry
3053 ,x_hiddenZipCode
3054 ,x_hiddenAddType
3055 ,x_hiddenLKPType
3056 ,l_functional_area
3057 );
3058
3059
3060
3061 x_return_status := FND_API.G_RET_STS_SUCCESS;
3062 x_errorcode := '0';
3063 x_msg_count := '1';
3064 x_msg_data := 'SUCCESS';
3065 END IF;
3066
3067 END po_address_XRD;
3068
3069 PROCEDURE po_ship_info_XRD
3070 (
3071 p_line_loc_id IN NUMBER
3072 ,p_draft_id IN NUMBER
3073 ,x_ship_to_addr IN OUT NOCOPY VARCHAR2
3074 ,p_hidd_ship_addr IN OUT NOCOPY VARCHAR
3075 ,p_hiddenCountry IN OUT NOCOPY VARCHAR2
3076 ,p_hiddenZipCode IN OUT NOCOPY VARCHAR
3077 ,x_return_status OUT NOCOPY VARCHAR2
3078 ,x_errorcode OUT NOCOPY VARCHAR2
3079 ,x_msg_count OUT NOCOPY VARCHAR2
3080 ,x_msg_data OUT NOCOPY VARCHAR2
3081 )IS
3082
3083 l_addr_line_1 VARCHAR2(2000);
3084 l_addr_line_2 VARCHAR2(2000);
3085 l_town_or_city VARCHAR2(1000);
3086 l_region2 VARCHAR2(1000);
3087 l_style VARCHAR2(500);
3088 l_postal_code VARCHAR2(50);
3089 l_state VARCHAR2(100);
3090 l_ship_addr_xml_sql VARCHAR2(6000);
3091
3092 l_ship_to_loc_id NUMBER;
3093
3094 BEGIN
3095
3096 SELECT SHIP_TO_LOCATION_ID
3097 INTO l_ship_to_loc_id
3098 FROM po_line_locations_all
3099 WHERE LINE_LOCATION_ID = p_line_loc_id;
3100
3101 SELECT ADDRESS_LINE_1, ADDRESS_LINE_2, TOWN_OR_CITY, REGION_2, STYLE, POSTAL_CODE,COUNTRY
3102 INTO l_addr_line_1, l_addr_line_2, l_town_or_city, l_region2, l_style, l_postal_code,p_hiddenCountry
3103 FROM HR_LOCATIONS
3104 WHERE LOCATION_ID = l_ship_to_loc_id;
3105
3106 l_state := l_region2;
3107
3108 IF l_addr_line_1 IS NOT NULL THEN
3109 x_ship_to_addr := l_addr_line_1 || newline;
3110 END IF;
3111 IF l_addr_line_2 IS NOT NULL THEN
3112 x_ship_to_addr := x_ship_to_addr || l_addr_line_2 || newline;
3113 END IF;
3114 IF l_town_or_city IS NOT NULL THEN
3115 x_ship_to_addr := x_ship_to_addr || l_town_or_city || ', ';
3116 END IF;
3117 IF l_region2 IS NOT NULL THEN
3118 x_ship_to_addr := x_ship_to_addr || l_state || newline;
3119 END IF;
3120 IF l_style IS NOT NULL THEN
3121 x_ship_to_addr := x_ship_to_addr || l_style || ', ';
3122 END IF;
3123 IF l_postal_code IS NOT NULL THEN
3124 x_ship_to_addr := x_ship_to_addr || l_postal_code;
3125 p_hiddenZipCode := l_postal_code;
3126 END IF;
3127
3128 l_ship_addr_xml_sql := 'SELECT XMLFOREST(HLC.LOCATION_ID ship_to_location_id, HLC.LOCATION_CODE location_name,
3129 HLC.ADDRESS_LINE_1 address_line_1,
3130 HLC.ADDRESS_LINE_2 address_line_2,
3131 HLC.ADDRESS_LINE_3 address_line_3,
3132 hlc.TOWN_OR_CITY city,
3133 hlc.region_1, nvl2(fcl1.meaning, fcl1.meaning, fcl3.meaning) region1_desc,
3134 hlc.region_2, fcl2.meaning region2_desc,
3135 HLC.COUNTRY, NVL(FTE.TERRITORY_SHORT_NAME,HLC.COUNTRY) country_name,
3136 hlc.postal_code,
3137 HLC.TELEPHONE_NUMBER_1 contact_phone, HLC.TELEPHONE_NUMBER_2 contact_fax).getStringVal()
3138 FROM HR_LOCATIONS HLC,
3139 FND_TERRITORIES_TL FTE,
3140 FND_LOOKUP_VALUES FCL1,
3141 FND_LOOKUP_VALUES FCL2,
3142 FND_LOOKUP_VALUES FCL3
3143 WHERE HLC.LOCATION_ID = :1
3144 AND HLC.COUNTRY = FTE.TERRITORY_CODE (+)
3145 AND DECODE(FTE.TERRITORY_CODE, NULL, ''1'', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, ''1'', USERENV(''LANG''))
3146 AND HLC.REGION_1 = FCL1.LOOKUP_CODE (+)
3147 AND HLC.COUNTRY
3148 || ''_PROVINCE'' = FCL1.LOOKUP_TYPE (+)
3149 AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.SECURITY_GROUP_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL1.LOOKUP_TYPE, FCL1.VIEW_APPLICATION_ID))
3150 AND DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.VIEW_APPLICATION_ID) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', 3)
3151 AND DECODE (FCL1.LOOKUP_CODE, NULL, ''1'', FCL1.LANGUAGE) = DECODE(FCL1.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
3152 AND HLC.REGION_2 = FCL2.LOOKUP_CODE (+)
3153 AND HLC.COUNTRY
3154 || ''_STATE'' = FCL2.LOOKUP_TYPE (+)
3155 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.SECURITY_GROUP_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL2.LOOKUP_TYPE, FCL2.VIEW_APPLICATION_ID))
3156 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.VIEW_APPLICATION_ID) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', 3)
3157 AND DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', FCL2.LANGUAGE) = DECODE(FCL2.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))
3158 AND HLC.REGION_1 = FCL3.LOOKUP_CODE (+)
3159 AND HLC.COUNTRY
3160 || ''_COUNTY'' = FCL3.LOOKUP_TYPE (+)
3161 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.SECURITY_GROUP_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'',FND_GLOBAL.LOOKUP_SECURITY_GROUP(FCL3.LOOKUP_TYPE, FCL3.VIEW_APPLICATION_ID))
3162 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.VIEW_APPLICATION_ID) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', 3)
3163 AND DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', FCL3.LANGUAGE) = DECODE(FCL3.LOOKUP_CODE, NULL, ''1'', USERENV(''LANG''))';
3164
3165 EXECUTE IMMEDIATE l_ship_addr_xml_sql INTO p_hidd_ship_addr USING l_ship_to_loc_id;
3166
3167 x_return_status := FND_API.G_RET_STS_SUCCESS;
3168 x_errorcode := '0';
3169 x_msg_count := '1';
3170 x_msg_data := 'SUCCESS';
3171
3172 EXCEPTION
3173 WHEN No_Data_Found THEN
3174 null;
3175 WHEN OTHERS THEN
3176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3177 x_errorcode := '-2';
3178 x_msg_count := '1';
3179 x_msg_data := 'Exception in po_ship_info_XRD';
3180 END po_ship_info_XRD;
3181
3182
3183 --CLM Phase 4 changes
3184
3185 PROCEDURE inspection_XLE
3186 (
3187 p_source_item IN VARCHAR2
3188 ,p_insp_location IN OUT NOCOPY NUMBER
3189 ,p_insp_addresscode IN OUT NOCOPY NUMBER
3190 ,p_insp_addressdetails IN OUT NOCOPY VARCHAR2
3191 ,p_accp_location IN OUT NOCOPY NUMBER
3192 ,p_accp_addresscode IN OUT NOCOPY NUMBER
3193 ,p_accp_addressdetails IN OUT NOCOPY VARCHAR2
3194 ,x_return_status OUT NOCOPY VARCHAR2
3195 ,x_errorcode OUT NOCOPY VARCHAR2
3196 ,x_msg_count OUT NOCOPY VARCHAR2
3197 ,x_msg_data OUT NOCOPY VARCHAR2
3198 )
3199 IS
3200
3201 d_proc_name CONSTANT varchar2(50) := 'inspection_XLE';
3202 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3203
3204 l_addr_line_1 VARCHAR2(2000);
3205 l_addr_line_2 VARCHAR2(2000);
3206 l_town_or_city VARCHAR2(1000);
3207 l_region2 VARCHAR2(1000);
3208 l_style VARCHAR2(500);
3209 l_postal_code VARCHAR2(50);
3210 l_state VARCHAR2(100);
3211 l_attr_bean_name VARCHAR2(250);
3212 l_functional_area VARCHAR2(200);
3213 l_addressType varchar2(200);
3214 l_source VARCHAR2(20);
3215
3216 CURSOR c_get_bean_name(p_attr_name varchar2) IS
3217 SELECT distinct att_assoc.ATTR_GROUP_TYPE || att_assoc.ATTR_GROUP_ID
3218 || attrs.attr_id || 'LOVDisp' Attr_Bean_Name
3219 FROM ego_obj_attr_grp_assocs_v att_assoc , EGO_ATTRS_v attrs
3220 WHERE attrs.ATTR_GROUP_TYPE = att_assoc.ATTR_GROUP_TYPE
3221 AND attrs.ATTR_GROUP_NAME = att_assoc.ATTR_GROUP_NAME
3222 AND attrs.ATTR_GROUP_NAME = 'INSPECT_INFO'
3223 AND ATTR_NAME = p_attr_name;
3224
3225
3226 BEGIN
3227
3228 IF (PO_LOG.d_proc) THEN
3229 PO_LOG.proc_begin(d_module);
3230 PO_LOG.proc_begin(d_module, 'p_source_item', p_source_item);
3231 PO_LOG.proc_begin(d_module, 'p_insp_location', p_insp_location);
3232 PO_LOG.proc_begin(d_module, 'p_insp_addresscode', p_insp_addresscode);
3233 PO_LOG.proc_begin(d_module, 'p_insp_addressdetails', p_insp_addressdetails);
3234 PO_LOG.proc_begin(d_module, 'p_accp_location', p_accp_location);
3235 PO_LOG.proc_begin(d_module, 'p_accp_addresscode', p_accp_addresscode);
3236 PO_LOG.proc_begin(d_module, 'p_accp_addressdetails', p_accp_addressdetails);
3237 END IF;
3238
3239 FOR c_get_bean_name_rec IN c_get_bean_name('INSP_ADD_LOC') LOOP
3240 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
3241 l_attr_bean_name := 'INSP_LOCATION';
3242 END IF;
3243 END LOOP;
3244
3245 FOR c_get_bean_name_rec IN c_get_bean_name('INSP_ADD_CODE') LOOP
3246 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
3247 l_attr_bean_name := 'INSP_ADDRESS_CODE';
3248 END IF;
3249 END LOOP;
3250
3251 FOR c_get_bean_name_rec IN c_get_bean_name('ACCP_ADD_LOC') LOOP
3252 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
3253 l_attr_bean_name := 'ACCP_LOCATION';
3254 END IF;
3255 END LOOP;
3256
3257 FOR c_get_bean_name_rec IN c_get_bean_name('ACCP_ADD_CODE') LOOP
3258 IF p_source_item = c_get_bean_name_rec.Attr_Bean_Name THEN
3259 l_attr_bean_name := 'ACCP_ADDRESS_CODE';
3260 END IF;
3261 END LOOP;
3262
3263 IF (PO_LOG.d_proc) THEN
3264 PO_LOG.proc_begin(d_module, 'l_attr_bean_name', l_attr_bean_name);
3265 END IF;
3266
3267 IF l_attr_bean_name = 'INSP_LOCATION'
3268 THEN
3269
3270 p_insp_addresscode := p_insp_location;
3271 l_source := 'INSPECTION';
3272
3273 IF p_insp_location is not null
3274 THEN
3275
3276 derive_insp_accp_adrs_dtls
3277 (
3278 p_insp_location
3279 ,p_insp_addresscode
3280 ,p_insp_addressdetails
3281 ,p_accp_location
3282 ,p_accp_addresscode
3283 ,p_accp_addressdetails
3284 ,l_source
3285 );
3286
3287 x_return_status := FND_API.G_RET_STS_SUCCESS;
3288 x_errorcode := '0';
3289 x_msg_count := '1';
3290 x_msg_data := 'SUCCESS';
3291
3292 END IF;
3293
3294 ELSIF l_attr_bean_name = 'INSP_ADDRESS_CODE'
3295 THEN
3296
3297 p_insp_location := p_insp_addresscode;
3298 l_source := 'INSPECTION';
3299
3300 IF p_insp_location is not NULL
3301 THEN
3302
3303 derive_insp_accp_adrs_dtls
3304 (
3305 p_insp_location
3306 ,p_insp_addresscode
3307 ,p_insp_addressdetails
3308 ,p_accp_location
3309 ,p_accp_addresscode
3310 ,p_accp_addressdetails
3311 ,l_source
3312 );
3313
3314 x_return_status := FND_API.G_RET_STS_SUCCESS;
3315 x_errorcode := '0';
3316 x_msg_count := '1';
3317 x_msg_data := 'SUCCESS';
3318
3319 END IF;
3320
3321 ELSIF l_attr_bean_name = 'ACCP_LOCATION'
3322 THEN
3323
3324 p_accp_addresscode := p_accp_location;
3325 l_source := 'ACCEPTANCE';
3326
3327 IF p_accp_location IS NOT NULL
3328 THEN
3329
3330 derive_insp_accp_adrs_dtls
3331 (
3332 p_insp_location
3333 ,p_insp_addresscode
3334 ,p_insp_addressdetails
3335 ,p_accp_location
3336 ,p_accp_addresscode
3337 ,p_accp_addressdetails
3338 ,l_source
3339 );
3340
3341 x_return_status := FND_API.G_RET_STS_SUCCESS;
3342 x_errorcode := '0';
3343 x_msg_count := '1';
3344 x_msg_data := 'SUCCESS';
3345
3346 END IF;
3347
3348 ELSIF l_attr_bean_name = 'ACCP_ADDRESS_CODE'
3349 THEN
3350
3351 p_accp_location := p_accp_addresscode;
3352 l_source := 'ACCEPTANCE';
3353
3354 IF p_accp_location IS NOT NULL
3355 THEN
3356
3357 derive_insp_accp_adrs_dtls
3358 (
3359 p_insp_location
3360 ,p_insp_addresscode
3361 ,p_insp_addressdetails
3362 ,p_accp_location
3363 ,p_accp_addresscode
3364 ,p_accp_addressdetails
3365 ,l_source
3366 );
3367
3368 x_return_status := FND_API.G_RET_STS_SUCCESS;
3369 x_errorcode := '0';
3370 x_msg_count := '1';
3371 x_msg_data := 'SUCCESS';
3372 END IF;
3373
3374 ELSE
3375
3376 x_return_status := FND_API.G_RET_STS_ERROR;
3377 x_errorcode := '-1';
3378 x_msg_count := '1';
3379 x_msg_data := 'LOV Event is not from LOCATION/ADDRESS_CODE';
3380
3381 END IF;
3382
3383 EXCEPTION
3384 WHEN OTHERS THEN
3385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3386 x_errorcode := '-2';
3387 x_msg_count := '1';
3388 x_msg_data := 'Exception in addresses_XLE';
3389
3390 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
3391 IF (PO_LOG.d_exc) THEN
3392 PO_LOG.proc_end(d_module,'x_msg_data' , x_msg_data ||': '|| SQLERRM );
3393 PO_LOG.proc_end(d_module);
3394 END IF;
3395
3396 END inspection_XLE;
3397
3398
3399
3400
3401 PROCEDURE derive_insp_accp_adrs_dtls
3402 (
3403 p_insp_location IN OUT nocopy NUMBER
3404 ,p_insp_addresscode IN OUT nocopy NUMBER
3405 ,p_insp_addressdetails IN OUT nocopy VARCHAR2
3406 ,p_accp_location IN OUT nocopy NUMBER
3407 ,p_accp_addresscode IN OUT nocopy NUMBER
3408 ,p_accp_addressdetails IN OUT nocopy VARCHAR2
3409 ,p_source IN VARCHAR2
3410 )
3411 IS
3412 d_proc_name CONSTANT varchar2(50) := 'derive_insp_accp_adrs_dtls';
3413 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3414
3415 l_LOCATION_CODE VARCHAR2(200);
3416 l_ADDRESS_LINE_1 VARCHAR2(200);
3417 l_ADDRESS_LINE_2 VARCHAR2(200);
3418 l_ADDRESS_LINE_3 VARCHAR2(200);
3419 l_TOWN_OR_CITY VARCHAR2(200);
3420 l_REGION_1 VARCHAR2(200);
3421 l_REGION_2 VARCHAR2(200);
3422 l_REGION_3 VARCHAR2(200);
3423 l_TELEPHONE_NUMBER_1 VARCHAR2(200);
3424 l_TELEPHONE_NUMBER_2 VARCHAR2(200);
3425 l_POSTAL_CODE VARCHAR2(200);
3426 l_COUNTRY VARCHAR2(200);
3427 l_PERSON_ID VARCHAR2(200);
3428 l_FIRST_NAME VARCHAR2(200);
3429 l_LAST_NAME VARCHAR2(200);
3430 l_TITLE VARCHAR2(200);
3431 l_FULL_NAME VARCHAR2(200);
3432 l_WORK_TELEPHONE VARCHAR2(200);
3433 l_EMAIL_ADDRESS VARCHAR2(200);
3434 l_POSITION VARCHAR2(200);
3435 l_loc_doddac varchar2(200);
3436 l_addressType VARCHAR2(200);
3437
3438 BEGIN
3439
3440 IF (PO_LOG.d_proc) THEN
3441 PO_LOG.proc_begin(d_module);
3442 PO_LOG.proc_begin(d_module, 'p_insp_location', p_insp_location);
3443 PO_LOG.proc_begin(d_module, 'p_insp_addresscode', p_insp_addresscode);
3444 PO_LOG.proc_begin(d_module, 'p_insp_addressdetails', p_insp_addressdetails);
3445 PO_LOG.proc_begin(d_module, 'p_accp_location', p_accp_location);
3446 PO_LOG.proc_begin(d_module, 'p_accp_addresscode', p_accp_addresscode);
3447 PO_LOG.proc_begin(d_module, 'p_accp_addressdetails', p_accp_addressdetails);
3448 PO_LOG.proc_begin(d_module, 'p_source', p_source);
3449 END IF;
3450
3451 IF p_insp_location is not null AND p_source = 'INSPECTION'
3452 THEN
3453 p_insp_addressdetails := '';
3454
3455 SELECT LOCATION_CODE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,REGION_1,
3456 REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,POSTAL_CODE,COUNTRY
3457 INTO l_LOCATION_CODE,l_ADDRESS_LINE_1,l_ADDRESS_LINE_2,l_ADDRESS_LINE_3,l_TOWN_OR_CITY,l_REGION_1,
3458 l_REGION_2,l_REGION_3,l_TELEPHONE_NUMBER_1,l_TELEPHONE_NUMBER_2,l_POSTAL_CODE,l_COUNTRY
3459 FROM HR_LOCATIONS_ALL
3460 WHERE LOCATION_ID = p_insp_location;
3461
3462 IF l_ADDRESS_LINE_1 IS NOT NULL THEN
3463 p_insp_addressdetails := l_ADDRESS_LINE_1 || newline;
3464 END IF;
3465
3466 IF l_ADDRESS_LINE_2 IS NOT NULL THEN
3467 p_insp_addressdetails := p_insp_addressdetails || l_ADDRESS_LINE_2 || newline;
3468 END IF;
3469
3470 IF l_TOWN_OR_CITY IS NOT NULL THEN
3471 p_insp_addressdetails := p_insp_addressdetails || l_TOWN_OR_CITY || newline;
3472 END IF;
3473
3474 IF l_REGION_2 IS NOT NULL THEN
3475 p_insp_addressdetails := p_insp_addressdetails || l_REGION_2 || newline;
3476 END IF;
3477
3478 IF l_POSTAL_CODE IS NOT NULL THEN
3479 p_insp_addressdetails := p_insp_addressdetails || l_POSTAL_CODE || newline;
3480 END IF;
3481
3482 IF l_COUNTRY IS NOT NULL THEN
3483 p_insp_addressdetails := p_insp_addressdetails || l_COUNTRY || newline;
3484 END IF;
3485
3486 END IF;
3487
3488 IF p_accp_location is not null AND p_source = 'ACCEPTANCE'
3489 THEN
3490 p_accp_addressdetails := '';
3491
3492 SELECT LOCATION_CODE,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3,TOWN_OR_CITY,REGION_1,
3493 REGION_2,REGION_3,TELEPHONE_NUMBER_1,TELEPHONE_NUMBER_2,POSTAL_CODE,COUNTRY
3494 INTO l_LOCATION_CODE,l_ADDRESS_LINE_1,l_ADDRESS_LINE_2,l_ADDRESS_LINE_3,l_TOWN_OR_CITY,l_REGION_1,
3495 l_REGION_2,l_REGION_3,l_TELEPHONE_NUMBER_1,l_TELEPHONE_NUMBER_2,l_POSTAL_CODE,l_COUNTRY
3496 FROM HR_LOCATIONS_ALL
3497 WHERE LOCATION_ID = p_accp_location;
3498
3499 IF l_ADDRESS_LINE_1 IS NOT NULL THEN
3500 p_accp_addressdetails := l_ADDRESS_LINE_1 || newline;
3501 END IF;
3502
3503 IF l_ADDRESS_LINE_2 IS NOT NULL THEN
3504 p_accp_addressdetails := p_accp_addressdetails || l_ADDRESS_LINE_2 || newline;
3505 END IF;
3506
3507 IF l_TOWN_OR_CITY IS NOT NULL THEN
3508 p_accp_addressdetails := p_accp_addressdetails || l_TOWN_OR_CITY || newline;
3509 END IF;
3510
3511 IF l_REGION_2 IS NOT NULL THEN
3512 p_accp_addressdetails := p_accp_addressdetails || l_REGION_2 || newline;
3513 END IF;
3514
3515 IF l_POSTAL_CODE IS NOT NULL THEN
3516 p_accp_addressdetails := p_accp_addressdetails || l_POSTAL_CODE || newline;
3517 END IF;
3518
3519 IF l_COUNTRY IS NOT NULL THEN
3520 p_accp_addressdetails := p_accp_addressdetails || l_COUNTRY || newline;
3521 END IF;
3522
3523 END IF;
3524
3525 EXCEPTION
3526
3527 WHEN No_Data_Found THEN
3528 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': NO DATA FOUND' );
3529
3530 WHEN OTHERS THEN
3531 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name ||': '|| SQLERRM );
3532 IF (PO_LOG.d_exc) THEN
3533 PO_LOG.proc_end(d_module||': '|| SQLERRM );
3534 PO_LOG.proc_end(d_module);
3535 END IF;
3536
3537 END derive_insp_accp_adrs_dtls;
3538
3539
3540
3541 PROCEDURE copy_from_insp_info
3542 (
3543 p_insp_location IN VARCHAR2
3544 ,p_insp_add_location IN NUMBER
3545 ,p_insp_addresscode IN NUMBER
3546 ,p_insp_addressdetails IN VARCHAR2
3547 ,p_accp_location IN OUT nocopy VARCHAR2
3548 ,p_accp_add_location IN OUT nocopy NUMBER
3549 ,p_accp_addresscode IN OUT nocopy NUMBER
3550 ,p_accp_addressdetails IN OUT nocopy VARCHAR2
3551 )
3552
3553 IS
3554
3555 d_proc_name CONSTANT varchar2(50) := 'copy_from_insp_info';
3556 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3557
3558 BEGIN
3559
3560 IF (PO_LOG.d_proc) THEN
3561 PO_LOG.proc_begin(d_module);
3562 PO_LOG.proc_begin(d_module, 'p_insp_location', p_insp_location);
3563 PO_LOG.proc_begin(d_module, 'p_insp_add_location', p_insp_add_location);
3564 PO_LOG.proc_begin(d_module, 'p_insp_addresscode', p_insp_addresscode);
3565 PO_LOG.proc_begin(d_module, 'p_insp_addressdetails', p_insp_addressdetails);
3566 END IF;
3567
3568 p_accp_add_location := p_insp_add_location;
3569 p_accp_addresscode := p_insp_addresscode;
3570 p_accp_addressdetails := p_insp_addressdetails;
3571 p_accp_location := p_insp_location;
3572
3573 END copy_from_insp_info;
3574
3575 PROCEDURE pr_mipr_acc_info_xsc
3576 (
3577 p_funds_info IN VARCHAR2
3578 ,p_amount IN NUMBER
3579 ,p_acceptance_status IN VARCHAR2
3580 ,p_remarks IN VARCHAR2
3581 ,p_obligation_type IN VARCHAR2
3582 ,p_auth_officer IN VARCHAR2
3583 ,p_header_id IN NUMBER
3584 ,x_return_status OUT NOCOPY VARCHAR2
3585 ,x_errorcode OUT NOCOPY VARCHAR2
3586 ,x_msg_count OUT NOCOPY VARCHAR2
3587 ,x_msg_data OUT NOCOPY VARCHAR2
3588 )IS
3589 d_proc_name CONSTANT varchar2(50) := 'pr_mipr_acc_info_xsc';
3590 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3591 l_msg_count number :=0;
3592 l_line_obli_type VARCHAR2(30) := NULL;
3593 l_mipr_type VARCHAR2(15) := NULL;
3594 BEGIN
3595 x_return_status := FND_API.G_RET_STS_SUCCESS;
3596 x_errorcode := '0';
3597 x_msg_count := '1';
3598 x_msg_data := 'SUCCESS';
3599
3600 IF (PO_LOG.d_proc) THEN
3601 PO_LOG.proc_begin(d_module);
3602 PO_LOG.proc_begin(d_module, 'p_funds_info', p_funds_info);
3603 PO_LOG.proc_begin(d_module, 'p_amount', p_amount);
3604 PO_LOG.proc_begin(d_module, 'p_acceptance_status', p_acceptance_status);
3605 PO_LOG.proc_begin(d_module, 'p_remarks', p_remarks);
3606 END IF;
3607
3608 IF p_funds_info IS NOT NULL AND p_amount IS NULL THEN
3609 x_return_status := FND_API.G_RET_STS_ERROR;
3610 x_errorcode := '-1';
3611 l_msg_count := l_msg_count + 1;
3612 x_msg_count := to_char( l_msg_count) ;
3613 x_msg_data := 'Enter a value for Amount if you have selected a value in Funds Information.' ;
3614 fnd_message.set_name(d_appln_short_name,'ICX_AMOUNT_NOT_NULL') ;
3615 fnd_msg_pub.ADD;
3616
3617 END IF;
3618
3619 IF (p_acceptance_status = 'CHANGE' OR p_acceptance_status = 'REJECT') AND p_remarks IS NULL THEN
3620 x_return_status := FND_API.G_RET_STS_ERROR;
3621 x_errorcode := '-1';
3622 l_msg_count := l_msg_count + 1;
3623 x_msg_count := to_char( l_msg_count) ;
3624 x_msg_data := 'Enter the remarks if MIPR Acceptance Status is Rejected with changes. ' ;
3625 fnd_message.set_name(d_appln_short_name,'ICX_REMARKS_NOT_NULL') ;
3626 fnd_msg_pub.ADD;
3627
3628 END IF;
3629
3630 IF p_obligation_type = 'BOTH' THEN
3631 SELECT Nvl(clm_mipr_obligation_type, 'NONE') INTO l_line_obli_type
3632 FROM po_requisition_lines_all
3633 WHERE requisition_header_id = p_header_id AND ROWNUM = 1;
3634
3635 IF l_line_obli_type = 'NONE' THEN
3636 x_return_status := FND_API.G_RET_STS_ERROR;
3637 x_errorcode := '-1';
3638 l_msg_count := l_msg_count + 1;
3639 x_msg_count := to_char( l_msg_count) ;
3640 x_msg_data := 'You have entered MIPR Obligation Type as Both. Please specify Obligation Type for each Line in Lines page.' ;
3641 fnd_message.set_name(d_appln_short_name,'ICX_MIPR_OBLI_NULL_AT_LINES') ;
3642 fnd_msg_pub.ADD;
3643 END IF;
3644 END IF;
3645
3646 select nvl(clm_mipr_type, 'NONE') into l_mipr_type
3647 FROM po_requisition_headers_all
3648 WHERE requisition_header_id = p_header_id AND ROWNUM = 1;
3649
3650 IF l_mipr_type = 'MIPR_OTHERS' THEN
3651 IF p_acceptance_status IS NULL THEN
3652 x_return_status := FND_API.G_RET_STS_ERROR;
3653 x_errorcode := '-1';
3654 l_msg_count := l_msg_count + 1;
3655 x_msg_count := to_char( l_msg_count) ;
3656 x_msg_data := 'MIPR Acceptance Status cannot be Blank' ;
3657 fnd_message.set_name(d_appln_short_name,'ICX_MIPR_ACC_NOT_NULL') ;
3658 fnd_msg_pub.ADD;
3659
3660 END IF;
3661
3662 IF p_obligation_type IS NULL THEN
3663 x_return_status := FND_API.G_RET_STS_ERROR;
3664 x_errorcode := '-1';
3665 l_msg_count := l_msg_count + 1;
3666 x_msg_count := to_char( l_msg_count) ;
3667 x_msg_data := 'MIPR Obligation Type cannot be Blank' ;
3668 fnd_message.set_name(d_appln_short_name,'ICX_MIPR_OBLI_NOT_NULL') ;
3669 fnd_msg_pub.ADD;
3670
3671 END IF;
3672
3673 IF p_auth_officer IS NULL THEN
3674 x_return_status := FND_API.G_RET_STS_ERROR;
3675 x_errorcode := '-1';
3676 l_msg_count := l_msg_count + 1;
3677 x_msg_count := to_char( l_msg_count) ;
3678 x_msg_data := 'MIPR Authorizing Officer cannot be Blank' ;
3679 fnd_message.set_name(d_appln_short_name,'ICX_MIPR_AUTH_NOT_NULL') ;
3680 fnd_msg_pub.ADD;
3681
3682 END IF;
3683 END IF;
3684
3685 IF (PO_LOG.d_proc) THEN
3686 PO_LOG.proc_end(d_module);
3687 PO_LOG.proc_end(d_module, 'x_errorcode', x_errorcode);
3688 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
3689 END IF;
3690
3691 EXCEPTION
3692 WHEN OTHERS THEN
3693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3694 x_errorcode := '-2';
3695 x_msg_count := '1';
3696 x_msg_data := 'Exception in pr_mipr_acc_info_xsc';
3697 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data||': '||SQLERRM );
3698
3699 END pr_mipr_acc_info_xsc;
3700
3701 PROCEDURE closeout_XD
3702 (
3703 po_header_id IN NUMBER
3704 ,draft_id IN NUMBER
3705 ,payment_no IN OUT NOCOPY VARCHAR2
3706 ,payment_date IN OUT NOCOPY DATE
3707 ,x_return_status OUT NOCOPY VARCHAR2
3708 ,x_errorcode OUT NOCOPY VARCHAR2
3709 ,x_msg_count OUT NOCOPY VARCHAR2
3710 ,x_msg_data OUT NOCOPY VARCHAR2
3711 )IS
3712
3713 d_proc_name CONSTANT varchar2(50) := 'closeout_XD';
3714 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3715
3716 l_count number;
3717 l_payment_no varchar2(100);
3718 l_payment_date date;
3719 l_po_header_id number;
3720 BEGIN
3721 l_po_header_id := po_header_id;
3722
3723 IF payment_no IS NULL and payment_date is null THEN
3724 /* all shipment lines are closed for invoice and fully paid */
3725 SELECT Count(1)
3726 INTO l_count
3727 FROM ap_invoice_distributions_all aid,
3728 ap_invoices_all aia,
3729 po_distributions_all pod,
3730 po_line_locations_all poll,
3731 po_headers_all poh
3732 WHERE poh.po_header_id = l_po_header_id
3733 and nvl(poh.authorization_status, 'INCOMPLETE') = 'APPROVED'
3734 and poll.po_header_id = poh.po_header_id
3735 AND nvl(poll.closed_code,'OPEN') <> 'CLOSED FOR INVOICE'
3736 and pod.po_header_id = poll.po_header_id
3737 and aid.po_distribution_id = pod.po_distribution_id
3738 AND aia.invoice_id = aid.invoice_id
3739 AND aia.payment_status_flag <> 'Y';
3740
3741 if (l_count = 0) then
3742 begin
3743 select check_number, check_date
3744 into l_payment_no, l_payment_date
3745 from
3746 (select pay.check_number, pay.check_date
3747 from ap_checks_all pay, ap_invoice_payments_all invpay
3748 where pay.check_id = invpay.check_id
3749 and exists ( select 1
3750 from ap_invoice_distributions_all invdist , po_distributions_all podist
3751 where podist.po_distribution_id = invdist.po_distribution_id
3752 and invdist.invoice_id = invpay.invoice_id
3753 and podist.po_header_id = l_po_header_id
3754 )
3755 order by pay.check_date desc
3756 )
3757 where rownum = 1;
3758 exception
3759 when NO_DATA_FOUND then
3760 l_payment_no := null;
3761 l_payment_date := null;
3762 end;
3763 end if;
3764
3765 payment_no := l_payment_no;
3766 payment_date := l_payment_date;
3767
3768 x_return_status := FND_API.G_RET_STS_SUCCESS;
3769 x_errorcode := '0';
3770 x_msg_count := '1';
3771 x_msg_data := 'SUCCESS';
3772 ELSE
3773 x_return_status := FND_API.G_RET_STS_ERROR;
3774 x_errorcode := '-1';
3775 x_msg_count := '1';
3776 x_msg_data := 'Closeout details not defaulted';
3777 END IF;
3778 EXCEPTION
3779 WHEN OTHERS THEN
3780 payment_no := null;
3781 payment_date := null;
3782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3783 x_errorcode := '-2';
3784 x_msg_count := '1';
3785 x_msg_data := 'Exception in closeout_XD';
3786 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
3787 END closeout_XD;
3788
3789
3790 --<Bug 16421784> : Addind a new procedure
3791 PROCEDURE pon_competitive_info_XV
3792 (
3793
3794 p_set_aside_stat IN VARCHAR2
3795 ,p_set_aside_perc IN VARCHAR2
3796 ,p_set_aside_type IN varchar2
3797 ,p_naics IN varchar2
3798 ,p_size_stand IN varchar2
3799 ,x_return_status OUT NOCOPY VARCHAR2
3800 ,x_errorcode OUT NOCOPY VARCHAR2
3801 ,x_msg_count OUT NOCOPY VARCHAR2
3802 ,x_msg_data OUT NOCOPY VARCHAR2
3803 ,p_auction_header_id IN NUMBER
3804 )
3805 IS
3806 d_proc_name CONSTANT varchar2(50) := 'pon_competitive_info_XV';
3807 d_module CONSTANT varchar2(100):= d_pkg_name ||'.'||d_proc_name;
3808 l_msg_count NUMBER(6) := 0;
3809 l_is_amendment VARCHAR2(1);
3810
3811 BEGIN
3812
3813 x_return_status := FND_API.G_RET_STS_SUCCESS;
3814 x_errorcode := '0';
3815 x_msg_count := '1';
3816 x_msg_data := 'SUCCESS';
3817
3818 BEGIN
3819 SELECT decode(nvl(amendment_number,0),0,'N','Y')
3820 INTO l_is_amendment
3821 FROM pon_auction_headers_all
3822 WHERE auction_Header_id = p_auction_header_id;
3823 EXCEPTION
3824 WHEN No_Data_Found THEN
3825 l_is_amendment := 'N';
3826 END;
3827
3828 IF p_set_aside_stat = 'UNRESTRICTED' AND To_Number(Nvl(p_set_aside_perc,0)) > 0 THEN
3829 x_return_status := FND_API.G_RET_STS_ERROR;
3830 x_errorcode := '-1';
3831 l_msg_count := l_msg_count + 1;
3832 x_msg_data := 'The set aside percentage does not apply on an unrestricted action.';
3833 fnd_message.set_name(d_appln_short_name, 'PO_NO_PER_UNRES_ACTION');
3834 fnd_msg_pub.ADD;
3835 END IF ;
3836 IF p_set_aside_stat = 'SET_ASIDE' AND To_Number(Nvl(p_set_aside_perc,0)) <= 0 THEN
3837 x_return_status := FND_API.G_RET_STS_ERROR;
3838 x_errorcode := '-1';
3839 l_msg_count := l_msg_count + 1;
3840 x_msg_data := 'The Set Aside Percentage must contain a value greater than 0, when the Set Aside Status is ''Set Aside''.';
3841 fnd_message.set_name(d_appln_short_name, 'PO_SET_ASIDE_PER_POSITIVE');
3842 fnd_msg_pub.ADD;
3843 END IF;
3844 IF p_set_aside_stat = 'SET_ASIDE' AND p_set_aside_type IS null THEN
3845 x_return_status := FND_API.G_RET_STS_ERROR;
3846 x_errorcode := '-1';
3847 l_msg_count := l_msg_count + 1;
3848 x_msg_data := 'When the Set Aside Status is ''Set Aside'', you must select a value for the set aside type.';
3849 fnd_message.set_name(d_appln_short_name,'PO_SET_ASIDE_SELECT_TYPE');
3850 fnd_msg_pub.ADD;
3851 END IF;
3852
3853
3854 --this validation should be carried on only for Base Documents/Sols, since it is a read only field in Mods/Amendmenst
3855 IF p_set_aside_stat = 'SET_ASIDE' AND p_set_aside_type = 'SMALL_BUSINESS' AND l_is_amendment = 'N' THEN
3856 x_return_status := FND_API.G_RET_STS_ERROR;
3857 x_errorcode := '-1';
3858 l_msg_count := l_msg_count + 1;
3859 x_msg_data := 'The value ''Small Business'' for Set-Aside is not valid for new Awards/ IDVs.';
3860 fnd_message.set_name(d_appln_short_name,'PO_SMALL_BUSNS_INVALID');
3861 fnd_msg_pub.ADD;
3862 END IF;
3863
3864 IF p_set_aside_stat = 'SET_ASIDE' AND p_naics IS null THEN
3865 x_return_status := FND_API.G_RET_STS_ERROR;
3866 x_errorcode := '-1';
3867 l_msg_count := l_msg_count + 1;
3868 x_msg_data := 'When the Set Aside Status is ''Set Aside'' you must select a value for the NAICS ';
3869 fnd_message.set_name(d_appln_short_name, 'PO_SET_ASIDE_NAICS');
3870 fnd_msg_pub.ADD;
3871 END IF;
3872 IF p_set_aside_stat = 'UNRESTRICTED' AND p_size_stand IS NOT null THEN
3873 x_return_status := FND_API.G_RET_STS_ERROR;
3874 x_errorcode := '-1';
3875 l_msg_count := l_msg_count + 1;
3876 x_msg_data := 'The size standard does not apply on an unrestricted action.';
3877 fnd_message.set_name(d_appln_short_name, 'PO_NO_SIZE_UNRES_ACTION');
3878 fnd_msg_pub.ADD;
3879 END IF;
3880 IF p_set_aside_stat = 'UNRESTRICTED' AND p_naics IS NOT null THEN
3881 x_return_status := FND_API.G_RET_STS_ERROR;
3882 x_errorcode := '-1';
3883 l_msg_count := l_msg_count + 1;
3884 x_msg_data := 'The NAICS designation does not apply on an unrestricted action.';
3885 fnd_message.set_name(d_appln_short_name, 'PO_NO_NAICS_UNRES_ACTION');
3886 fnd_msg_pub.ADD;
3887 END IF;
3888 IF p_set_aside_stat = 'UNRESTRICTED' AND p_set_aside_type IS NOT null THEN
3889 x_return_status := FND_API.G_RET_STS_ERROR;
3890 x_errorcode := '-1';
3891 l_msg_count := l_msg_count + 1;
3892 x_msg_data := 'The set aside type does not apply on an unrestricted action.';
3893 fnd_message.set_name(d_appln_short_name, 'PO_NO_ASIDETYPE_UNRES_ACTION');
3894 fnd_msg_pub.ADD;
3895 END IF;
3896
3897 IF l_msg_count > 0 THEN
3898 x_msg_count := l_msg_count;
3899 END IF ;
3900 EXCEPTION
3901 WHEN OTHERS THEN
3902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3903 x_errorcode := '-2';
3904 x_msg_count := '1';
3905 x_msg_data := 'Exception in pon_competitive_info_XV';
3906 fnd_msg_pub.Add_Exc_Msg ( d_pkg_name, d_proc_name, x_msg_data );
3907 END pon_competitive_info_XV;
3908
3909 END po_fed_field_functions;