[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_ATTR_LOAD_PUB
Source
1 PACKAGE BODY PV_PARTNER_ATTR_LOAD_PUB AS
2 /* $Header: pvxpldpb.pls 120.6 2005/11/11 15:30 amaram noship $ */
3 -- Start of Comments
4 --
5 -- Funtion name : Write_Log
6 -- Type : Private
7 -- Function :
8 --
9 --
10 -- Pre-reqs :
11 --
12 -- Paramaeters :
13 -- IN :
14 --
15 -- OUT :
16 --
17 -- Version :
18 -- Initial version 1.0
19 --
20 -- Notes: Commonly used to routine to log all the messages
21 --
22 --
23 --
24 -- End of Comments
25
26 --log file declaration
27 L_LOG_FILE utl_file.file_type;
28 TYPE l_errors_tbl_type IS TABLE OF varchar2(3000) INDEX BY BINARY_INTEGER;
29 l_errors_tbl l_errors_tbl_type;
30 l_error_count number;
31
32
33 /*********SHOME'S CODE HERE ************/
34
35 /*
36 procedure validate_attribute
37 ( in_table in PV_PARTNER_ATTR_LOAD_PUB.attr_details_tbl_type ,
38 out_table out nocopy PV_PARTNER_ATTR_LOAD_PUB.attr_details_tbl_type ,
39 err_table out nocopy error_tbl_type)
40
41 is
42 l_attr_value varchar2(2000);
43 l_attr_val_ext varchar2(4000);
44 l_attribute_id number;
45
46 l_target_date date := null;
47 l_target_number number := 0;
48 l_str_length number := null;
49
50 l_date_format varchar2(100);
51 l_display_style varchar2(100);
52 l_attribute_type varchar2(100);
53 l_char_width number;
54 l_dec_pts number;
55
56 l_no_format varchar2(30) := null;
57
58 l_attr_value_table PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
59
60 l_out_val_table PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
61
62 l_v1_row pls_integer;
63 l_v2_row pls_integer;
64
65 l_temp_flag number := 0;
66
67 l_nth_err_val binary_integer := 1;
68 l_nth_out_val binary_integer := 1;
69 l_nth_out_attr_val binary_integer := 1;
70
71 l_attr_val_count binary_integer;
72
73 l_code_ret_flag boolean;
74
75
76
77 begin
78 l_v1_row := in_table.first;
79 loop
80 exit when l_v1_row is null;
81 l_attribute_id := in_table(l_v1_row).attribute_id;
82
83 l_attr_value_table := in_table(l_v1_row).attr_values_tbl;
84
85 l_attr_val_count := l_attr_value_table.count;
86
87
88 select display_style, attribute_type, character_width, decimal_points
89 into l_display_style, l_attribute_type, l_char_width, l_dec_pts
90 from pv_attributes_vl
91 where attribute_id = l_attribute_id;
92
93 if (l_display_style = 'DATE') then
94 begin
95 select fnd_profile.value('ICX_DATE_FORMAT_MASK') into l_date_format from dual;
96 l_target_date := to_date(l_attr_value_table(1).attr_value,l_date_format);
97 exception
98 when others then
99 l_target_date := null;
100 fnd_message.set_name('PV', 'PV_INVALID_DATE_MSG');
101 fnd_message.set_token('ATTRID', l_attribute_id);
102 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
103 l_nth_err_val := l_nth_err_val + 1;
104 end;
105 if l_target_date is not null then
106 out_table (l_nth_out_val).attribute_id := l_attribute_id;
107 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
108 l_nth_out_val := l_nth_out_val + 1;
109 end if;
110 end if;
111
112
113 if (l_display_style = 'NUMBER') then
114 begin
115 l_target_number := to_number(l_attr_value_table(1).attr_value);
116 exception
117 when others then
118 l_target_number := null;
119 fnd_message.set_name('PV', 'PV_ONLY_NUM_MSG');
120 fnd_message.set_token('ATTRID', l_attribute_id);
121 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
122 l_nth_err_val := l_nth_err_val + 1;
123 end;
124 if l_target_number is not null then
125 out_table (l_nth_out_val).attribute_id := l_attribute_id;
126 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
127 l_nth_out_val := l_nth_out_val + 1;
128 end if;
129 end if;
130
131 if (l_display_style = 'SINGLE' or l_display_style = 'RADIO' ) then
132 if (l_attr_val_count > 1) then
133 fnd_message.set_name('PV', 'PV_ONE_VAL_MSG');
134 fnd_message.set_token('ATTRID', l_attribute_id);
135 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
136 l_nth_err_val := l_nth_err_val + 1;
137 else
138 l_code_ret_flag := validate_codes(l_attribute_id,l_attr_value_table(1).attr_value);
139 if l_code_ret_flag = true then
140 out_table (l_nth_out_val).attribute_id := l_attribute_id;
141 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
142 l_nth_out_val := l_nth_out_val + 1;
143 else
144 fnd_message.set_name('PV', 'PV_SINGLE_INVALID_VAL_MSG');
145 fnd_message.set_token('ATTRID', l_attribute_id);
146 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
147 l_nth_err_val := l_nth_err_val + 1;
148 end if;
149 end if;
150 end if;
151
152
153 if (l_display_style = 'CHECK' or l_display_style = 'MULTI' ) then
154 l_v2_row := l_attr_value_table.first;
155 loop
156 exit when l_v2_row is null;
157 l_attr_value := l_attr_value_table(l_v2_row).attr_value;
158 l_code_ret_flag := validate_codes(l_attribute_id,l_attr_value);
159 if l_code_ret_flag = true then
160 l_temp_flag := 1;
161 else
162 l_temp_flag := 0;
163 exit;
164 end if;
165 l_v2_row := l_attr_value_table.next(l_v2_row);
166 end loop;
167 if l_temp_flag = 1 then
168 out_table (l_nth_out_val).attribute_id := l_attribute_id;
169 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
170 l_nth_out_val := l_nth_out_val + 1;
171 else
172 fnd_message.set_name('PV', 'PV_MULTI_INVALID_VAL_MSG');
173 fnd_message.set_token('ATTRID', l_attribute_id);
174 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
175 l_nth_err_val := l_nth_err_val + 1;
176 end if;
177 end if;
178
179 if l_attribute_type = 'DROPDOWN' and l_display_style = 'PERCENTAGE' then
180 l_v2_row := l_attr_value_table.first;
181 loop
182 exit when l_v2_row is null;
183 l_attr_value := l_attr_value_table(l_v2_row).attr_value;
184 l_attr_val_ext := l_attr_value_table(l_v2_row).attr_value_extn;
185
186 if l_attr_value is null then
187 l_temp_flag := 0;
188 exit;
189 else
190 l_code_ret_flag := validate_codes(l_attribute_id,l_attr_value);
191
192 if l_code_ret_flag = false then
193 l_temp_flag := 0;
194 exit;
195 else
196 l_temp_flag := 1;
197 if l_attr_val_ext is not null then
198 begin
199 l_target_number := l_target_number + to_number(l_attr_val_ext);
200 l_out_val_table(l_nth_out_attr_val).attr_value := l_attr_value;
201 l_out_val_table(l_nth_out_attr_val).attr_value_extn := l_attr_val_ext;
202 l_nth_out_attr_val := l_nth_out_attr_val + 1;
203 exception
204 when others then
205 fnd_message.set_name('PV', 'PV_ONLY_NUM_MSG');
206 fnd_message.set_token('ATTRID', l_attribute_id);
207 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
208 l_nth_err_val := l_nth_err_val + 1;
209 end;
210 end if;
211 end if;
212 end if;
213 l_v2_row := l_attr_value_table.next(l_v2_row);
214 end loop;
215 if l_temp_flag = 1 then
216 if l_target_number > 100 then
217 fnd_message.set_name('PV', 'PV_VAL_EXCDS_100_MSG');
218 fnd_message.set_token('ATTRID', l_attribute_id);
219 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
220 l_nth_err_val := l_nth_err_val + 1;
221 else
222 if l_out_val_table.count > 0 then
223 out_table (l_nth_out_val).attribute_id := l_attribute_id;
224 out_table (l_nth_out_val).attr_values_tbl := l_out_val_table;
225 l_nth_out_val := l_nth_out_val + 1;
226 end if;
227 end if;
228 else
229 fnd_message.set_name('PV', 'PV_MULTI_INVALID_VAL_MSG');
230 fnd_message.set_token('ATTRID', l_attribute_id);
231 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
232 l_nth_err_val := l_nth_err_val + 1;
233 end if;
234 end if;
235
236 if l_display_style = 'STRING' then
237 l_str_length := length(l_attr_value_table(1).attr_value);
238 if l_char_width is not null and l_str_length > l_char_width then
239 fnd_message.set_name('PV', 'PV_LONG_STR_MSG');
240 fnd_message.set_token('ATTRID', l_attribute_id);
241 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
242 l_nth_err_val := l_nth_err_val + 1;
243 else
244 out_table (l_nth_out_val).attribute_id := l_attribute_id;
245 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
246 l_nth_out_val := l_nth_out_val + 1;
247 end if;
248 end if;
249
250 if l_display_style = 'CURRENCY' then
251 if l_dec_pts is not null then
252 for i in 1..l_dec_pts loop
253 l_no_format := l_no_format ||'9';
254 end loop;
255 l_no_format := trim(l_no_format||'.99');
256
257 begin
258 l_target_number := to_number(l_attr_value_table(1).attr_value,l_no_format);
259 exception
260 when others then
261 l_target_number := null;
262 fnd_message.set_name('PV', 'PV_ONLY_NUM_MSG');
263 fnd_message.set_token('ATTRID', l_attribute_id);
264 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
265 l_nth_err_val := l_nth_err_val + 1;
266 end;
267 if l_target_number is not null then
268 out_table (l_nth_out_val).attribute_id := l_attribute_id;
269 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
270 l_nth_out_val := l_nth_out_val + 1;
271 end if;
272 else
273 begin
274 l_target_number := to_number(l_attr_value_table(1).attr_value);
275 exception
276 when others then
277 l_target_number := null;
278 fnd_message.set_name('PV', 'PV_ONLY_NUM_MSG');
279 fnd_message.set_token('ATTRID', l_attribute_id);
280 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
281 l_nth_err_val := l_nth_err_val + 1;
282 end;
283 if l_target_number is not null then
284 out_table (l_nth_out_val).attribute_id := l_attribute_id;
285 out_table (l_nth_out_val).attr_values_tbl := l_attr_value_table;
286 l_nth_out_val := l_nth_out_val + 1;
287 end if;
288 end if;
289 end if;
290
291 l_v1_row := in_table.next(l_v1_row);
292 end loop;
293
294 exception
295 when others then
296 fnd_message.set_name('PV', 'PV_VALIDATE_GEN_ERROR');
297 err_table (l_nth_err_val).error_desc := substrb(fnd_message.get, 1, 1000);
298 l_nth_err_val := l_nth_err_val + 1;
299 end;
300
301
302 function validate_codes
303 (
304 in_attribute_id in number,
305 in_attr_value in varchar2
306 )
307 return boolean
308 is
309
310 l_attr_code varchar2(100);
311 l_ret_value boolean := false;
312
313 cursor get_attribute_codes(attr_id number) is
314 select attr_code
315 from pv_attribute_codes_vl
316 where attribute_id = attr_id;
317
318 begin
319 if in_attr_value is not null then
320 open get_attribute_codes(in_attribute_id);
321 loop
322 fetch get_attribute_codes into l_attr_code;
326 if (get_attribute_codes%rowcount <> 0) then
323 if (get_attribute_codes%rowcount = 0) then
324 exit;
325 else
327 if (get_attribute_codes%notfound) then
328 exit;
329 else
330 if (in_attr_value = l_attr_code) then
331 l_ret_value := true ;
332 exit;
333 end if;
334 end if;
335 end if;
336 end if;
337 end loop;
338 close get_attribute_codes;
339 else
340 if in_attr_value is null then
341 l_ret_value := true ;
342 end if;
343 end if;
344 return l_ret_value;
345 exception
346 when others then
347 return false;
348 end;
349
350 */
351 /*********SHOME'S CODE ENDS HERE ***************************/
352
353 /*
354 PROCEDURE Write_Log(p_log_file utl_file.file_type, p_msg varchar2) IS
355 BEGIN
356
357 FND_FILE.put(p_which, p_mssg);
358 FND_FILE.NEW_LINE(p_which, 1);
359
360 -- utl_file.put_line(p_log_file, p_msg);
361 --dbms_output.put_line(' ');
362
363
364 END Write_Log;
365 */
366 -- Start of Comments
367 --
368 -- Funtion name : Write_Log
369 -- Type : Private
370 -- Function :
371 --
372 --
373 -- Pre-reqs :
374 --
375 -- Paramaeters :
376 -- IN :
377 --
378 -- OUT :
379 --
380 -- Version :
381 -- Initial version 1.0
382 --
383 -- Notes: Commonly used to routine to log all the messages
384 --
385 --
386 --
387 -- End of Comments
388
389 PROCEDURE Write_Error IS
390
391 BEGIN
392 --dbms_output.put_line('Error Count: ' || l_errors_tbl.count);
393 if l_errors_tbl.count > 0 then
394 for i in 1..l_errors_tbl.count
395 loop
396 utl_file.put_line(L_LOG_FILE, rpad(' ',100) || l_errors_tbl(i));
397 --dbms_output.put_line('Error: ' || l_errors_tbl(i));
398 end loop;
399 l_errors_tbl.delete;
400 end if;
401
402 END Write_Error;
403
404 -- Start of Comments
405 --
406 -- Funtion name : Validate_Party
407 -- Type : Private
408 -- Function :
409 --
410 --
411 -- Pre-reqs :
412 --
413 -- Paramaeters :
414 -- IN :
415 --
416 -- OUT :
417 --
418 -- Version :
419 -- Initial version 1.0
420 --
421 -- Notes: Validate all the information passed related to party.
422 --
423 --
424 --
425 -- End of Comments
426 Procedure Validate_Party(
427 p_party_id IN NUMBER,
428 p_orig_sys IN varchar2,
429 p_orig_sys_ref IN varchar2,
430 p_partner_id IN NUMBER,
431 p_partner_name IN varchar2,
432 p_type IN varchar2,
433 x_party_id OUT NOCOPY NUMBER,
434 x_exit_partner OUT NOCOPY varchar2
435 )
436 IS
437
438 CURSOR l_valid_OSR(cv_orig_system IN VARCHAR2, cv_orig_system_ref IN VARCHAR2) IS
439 select
440 'Y',
441 hz_parties.party_id
442
443 from
444 hz_orig_sys_references,
445 hz_parties
446
447 where
448 hz_orig_sys_references.owner_table_id = hz_parties.party_id and
449 hz_parties.party_type = 'ORGANIZATION' AND
450 hz_orig_sys_references.orig_system = cv_orig_system and
451 hz_orig_sys_references.orig_system_reference = cv_orig_system_ref and
452 hz_orig_sys_references.owner_table_name = 'HZ_PARTIES' AND
453 hz_parties.status = 'A';
454
455
456 cursor l_valid_POSR(cv_party_id IN NUMBER, cv_osr in varchar2) IS
457 select
458 'Y'
459 FROM
460 hz_parties
461 where
462 party_id = cv_party_id and
463 orig_system_reference = cv_osr and
464 party_type = 'ORGANIZATION' and
465 status = 'A';
466
467
468 cursor l_valid_PartyId(cv_party_id IN NUMBER) IS
469 select
470 'Y'
471 FROM
472 hz_parties
473 where
474 party_id = cv_party_id and
475 party_type = 'ORGANIZATION' and
476 status = 'A';
477
478
479 cursor l_valid_Party(cv_party_id IN NUMBER) IS
480 select
481 'Y'
482 FROM
483 hz_parties
484 where
485 party_id = cv_party_id and
486 party_type = 'ORGANIZATION' and
487 status = 'A';
488
489 cursor l_valid_POSOSR(cv_party_id IN NUMBER, cv_os IN VARCHAR, cv_osr IN VARCHAR) IS
490 select
491 'Y'
492 from
493 hz_orig_sys_references,
494 hz_parties
495
496 where
500 hz_orig_sys_references.orig_system = cv_os and
497 hz_parties.party_id = cv_party_id and
498 hz_orig_sys_references.owner_table_id = hz_parties.party_id and
499 hz_parties.party_type = 'ORGANIZATION' AND
501 hz_orig_sys_references.orig_system_reference = cv_osr and
502 hz_orig_sys_references.owner_table_name = 'HZ_PARTIES' AND
503 hz_parties.status = 'A';
504
505 cursor l_valid_partner_id(cv_partner_id IN NUMBER, cv_party_id IN Number) is
506 select
507 'Y'
508 from
509 pv_partner_profiles
510 where
511 partner_id = cv_partner_id and
512 partner_party_id = cv_party_id;
513 -- and status = 'A';
514
515
516 cursor l_valid_PartnerParty(cv_partner_id number) IS
517 select
518 'Y',
519 hz_parties.party_id
520
521 from
522 pv_partner_profiles,
523 hz_parties
524
525 where
526 partner_id = cv_partner_id and
527 -- pv_partner_profiles.status = 'A' and
528 party_id = partner_party_id and
529 hz_parties.status = 'A' and
530 hz_parties.party_type = 'ORGANIZATION';
531
532
533 l_valid varchar2(1);
534 l_party_id NUMBER;
535
536 BEGIN
537
538 --dbms_output.put_line('Processing party validation ');
539 x_exit_partner := 'FALSE';
540 l_party_id := p_party_id;
541 l_valid := 'N';
542 if (p_party_id is null AND p_orig_sys is null and p_orig_sys_ref is null and p_partner_id is null) then
543 --dbms_output.put_line('not sure how i got here ');
544 fnd_message.set_name('PV', 'PV_IMP_PARTY_IDENT_REQ');
545 fnd_message.set_token( 'TYPE', p_type);
546 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
547 l_error_count := l_error_count +1;
548 x_exit_partner := 'TRUE';
549 --dbms_output.put_line('Party information is required. Please provide this information to process further');
550 elsif (p_party_id is not null and p_orig_sys_ref is not null) and (p_orig_sys is null) then
551 l_valid := 'N';
552 Open l_valid_POSR(p_party_id, p_orig_sys_ref);
553 fetch l_valid_POSR into l_valid;
554 close l_valid_POSR;
555 if not l_valid = 'Y' then
556 fnd_message.set_name('PV', 'PV_IMP_PARTY_REF');
557 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
558 l_error_count := l_error_count +1;
559 --dbms_output.put_line('Party Id passed does not match the reference passed. Please check your data ');
560 x_exit_partner := 'TRUE';
561 end if;
562 elsif (p_party_id is null and p_orig_sys_ref is null and p_orig_sys is not null) then
563
564 l_valid := 'N';
565 fnd_message.set_name('PV', 'PV_IMP_PARTY_IDENT_REQ');
566 fnd_message.set_token( 'TYPE', p_type);
567 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
568 l_error_count := l_error_count +1;
569 x_exit_partner := 'TRUE';
570 /* dbms_output.put_line('Party identification information is required. Please provide either
571 1. Party Id (OR)
572 2. Original System and Original System Reference');
573 */
574 elsif (p_party_id is null and (p_orig_sys_ref is not null and p_orig_sys is not null)) then
575 l_valid := 'N';
576 Open l_valid_OSR(p_orig_sys, p_orig_sys_ref);
577 fetch l_valid_OSR into l_valid,l_party_id;
578 close l_valid_OSR;
579 if not l_valid = 'Y' then
580 fnd_message.set_name('PV', 'PV_IMP_CUST_NOT_FOUND');
581 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
582 l_error_count := l_error_count +1;
583 -- dbms_output.put_line('Party not found as a Customer. Please ensure that it is present in the TCA table');
584 x_exit_partner := 'TRUE';
585 end if;
586 elsif (p_party_id is not null AND p_orig_sys is not null and p_orig_sys_ref is not null) then
587 l_valid := 'N';
588 Open l_valid_POSOSR(p_party_id,p_orig_sys,p_orig_sys_ref);
589 fetch l_valid_POSOSR into l_valid;
590 close l_valid_POSOSR;
591 if not l_valid = 'Y' then
592 fnd_message.set_name('PV', 'PV_IMP_PARTY_OSR');
593 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
594 l_error_count := l_error_count +1;
595 -- dbms_output.put_line('Party Id and party references does not match. Please check your data ');
596 x_exit_partner := 'TRUE';
597 end if;
598 elsif (p_party_id is not null) then
599 l_valid := 'N';
600 Open l_valid_PartyId(p_party_id);
601 fetch l_valid_PartyId into l_valid;
602 close l_valid_PartyId;
603 if not l_valid = 'Y' then
604 fnd_message.set_name('PV', 'PV_IMP_CUST_NOT_FOUND');
605 fnd_message.set_token( 'PARTYNAME', p_partner_name);
606 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
607 l_error_count := l_error_count +1;
608 --dbms_output.put_line('Party not found as a Customer. Please ensure that it is present in the TCA table');
609 x_exit_partner := 'TRUE';
610 end if;
611 elsif (p_partner_id is not null) then
612 --dbms_output.put_line('getting into the partner id check ');
613 --dbms_output.put_line('partner id being checked ' || p_partner_id);
614 l_valid := 'N';
618 --dbms_output.put_line('valid partner ' || l_valid);
615 Open l_valid_PartnerParty(p_partner_id);
616 fetch l_valid_PartnerParty into l_valid,l_party_id;
617 close l_valid_PartnerParty;
619 if not l_valid = 'Y' then
620 fnd_message.set_name('PV', 'PV_IMP_PARTY_IDENT_REQ');
621 fnd_message.set_token( 'TYPE', p_type);
622 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
623 l_error_count := l_error_count +1;
624 /*dbms_output.put_line('Party identification information is required. Please provide either
625 1. Party Id (OR)
626 2. Original System and Original System Reference');
627 */
628 x_exit_partner := 'TRUE';
629 end if;
630 --dbms_output.put_line('found the party ' || l_party_id);
631
632 else
633 --dbms_output.put_line('not sure how i got here ');
634 fnd_message.set_name('PV', 'PV_IMP_PARTY_IDENT_REQ');
635 fnd_message.set_token( 'TYPE', p_type);
636 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
637 l_error_count := l_error_count +1;
638 x_exit_partner := 'TRUE';
639 --dbms_output.put_line('Party information is required. Please provide this information to process further');
640 end if;
641
642 if (p_partner_id is not null) and (l_party_id is not null) then
643 l_valid := 'N';
644
645 open l_valid_partner_id(p_partner_id, l_party_id);
646 fetch l_valid_partner_id into l_valid;
647 close l_valid_partner_id;
648 if not l_valid = 'Y' then
649 fnd_message.set_name('PV', 'PV_IMP_INVALID_PTNR');
650 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
651 l_error_count := l_error_count +1;
652 --dbms_output.put_line('invalid partner passed');
653 x_exit_partner := 'TRUE';
654 end if;
655
656 end if;
657 x_party_id := l_party_id;
658
659
660 END Validate_Party;
661
662
663 -- Start of Comments
664 --
665 -- Funtion name : Validate_And_Create_Partner
666 -- Type : Private
667 -- Function :
668 --
669 --
670 -- Pre-reqs :
671 --
672 -- Paramaeters :
673 -- IN :
674 --
675 -- OUT :
676 --
677 -- Version :
678 -- Initial version 1.0
679 --
680 -- Notes: Validate all the required fields and business validations for the
681 -- attributes passed. If the validations are successful create the
682 -- partner and its attributes
683 --
684 -- End of Comments
685 Procedure Validate_And_Create_Partner(
686 p_party_id IN NUMBER,
687 p_partner_details_rec IN partner_details_rec_type,
688 x_partner_id OUT NOCOPY NUMBER,
689 x_exit_partner OUT NOCOPY varchar2
690 )
691 IS
692
693 CURSOR l_get_party_id(cv_orig_system IN VARCHAR2, cv_orig_system_ref IN VARCHAR2) IS
694 SELECT
695 hz_parties.party_id
696
697 from
698 hz_orig_sys_references,
699 hz_parties
700
701 where
702 hz_orig_sys_references.owner_table_id = hz_parties.party_id and
703 hz_parties.party_type = 'ORGANIZATION' AND
704 hz_orig_sys_references.orig_system = cv_orig_system and
705 hz_orig_sys_references.orig_system_reference = cv_orig_system_ref and
706 hz_orig_sys_references.owner_table_name = 'HZ_PARTIES' AND
707 hz_parties.status = 'A';
708
709 CURSOR l_get_partner_id(cv_party_id IN NUMBER) IS
710 SELECT
711 partner_id
712 FROM
713 pv_partner_profiles
714
715 WHERE
716 partner_party_id = cv_party_id;
717 -- and status = 'A';
718
719
720 CURSOR l_get_glbl_member_type(cv_partner_id IN NUMBER) is
721 SELECT
722 attr_value
723 from
724 pv_enty_attr_values
725 where
726 attribute_id = 6 and
727 latest_flag = 'Y' and
728 entity_id = cv_partner_id;
729
730
731 CURSOR l_check_acct_exists(cv_party_id IN NUMBER) is
732 SELECT
733 'Y'
734 from
735 hz_cust_accounts
736 where
737 party_id = cv_party_id and
738 status = 'A';
739
740
741 CURSOR C_party_info (l_party_id NUMBER) IS
742 SELECT
743 party_type, party_name
744 FROM
745 hz_parties
746 WHERE
747 party_id = l_party_id;
748
749
750 CURSOR C_acct_number IS
751 --SELECT pv_account_number_s.nextval FROM dual;
752 SELECT 1000 from dual;
753
754
755 l_bound Number;
756 u_bound Number;
757 l_has_partner_type varchar2(1);
758 l_has_member_type varchar2(1);
759 l_is_subsidiary varchar2(1);
760 l_attribute_details_tbl attr_details_tbl_type;
761 l_attr_values_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type; --attr_values_tbl_type;
762 l_party_id number;
763 l_partner_id number;
764 l_partner_name varchar2(360);
765 l_gbl_orig_system varchar2(30);
766 l_gbl_orig_system_ref varchar2(255);
770 l_partner_member_type varchar2(500);
767 l_gbl_party_id number;
768 l_gbl_partner_id number;
769 l_partner_types_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
771 l_member_type varchar2(500);
772 l_default_resp_id NUMBER;
773 l_resp_map_rule_id NUMBER;
774 l_group_id NUMBER;
775 l_gbl_partner_name varchar(360);
776 l_out_gbl_party_id NUMBER;
777
778
779 l_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
780 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
781 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
782 l_cust_account_id NUMBER;
783 l_account_number NUMBER;
784 l_party_number NUMBER;
785 l_profile_id NUMBER;
786 l_account_exists varchar2(1);
787 l_gen_cust_num VARCHAR2(1);
788
789 l_return_status VARCHAR2(1);
790 l_msg_count NUMBER;
791 l_msg_data VARCHAR(2000);
792
793
794
795 BEGIN
796 --dbms_output.put_line('let me check');
797
798 l_has_partner_type := 'N';
799 l_has_member_type := 'N';
800 l_is_subsidiary := 'N';
801 l_party_id := p_party_id;
802 x_exit_partner := 'FALSE';
803
804 l_attribute_details_tbl := p_partner_details_rec.attribute_details_tbl;
805
806
807
808 l_bound := l_attribute_details_tbl.first;
809 u_bound := l_attribute_details_tbl.last;
810
811
812 for i in l_bound..u_bound
813 loop
814 if l_attribute_details_tbl(i).attribute_id = 3 then
815 l_has_partner_type := 'Y';
816 l_attr_values_tbl := l_attribute_details_tbl(i).attr_values_tbl;
817 for j in l_attr_values_tbl.first..l_attr_values_tbl.last
818 loop
819 l_partner_types_tbl(1).attr_value := l_attr_values_tbl(j).attr_value;
820 l_partner_types_tbl(1).attr_value_extn := 'Y';
821 end loop;
822 end if;
823 if l_attribute_details_tbl(i).attribute_id = 6 then
824 l_has_member_type := 'Y';
825 l_attr_values_tbl := l_attribute_details_tbl(i).attr_values_tbl;
826 for j in l_attr_values_tbl.first..l_attr_values_tbl.last
827 loop
828 l_partner_member_type := l_attr_values_tbl(j).attr_value;
829 if l_attr_values_tbl(j).attr_value = 'SUBSIDIARY' then
830 l_is_subsidiary := 'Y';
831 end if;
832 end loop;
833 end if;
834
835 end loop;
836 --dbms_output.put_line('exit partner status ' || x_exit_partner);
837 if NOT l_has_partner_type = 'Y' then
838 fnd_message.set_name('PV', 'PV_IMP_REQ_DATA');
839 fnd_message.set_token( 'PARAM', 'Partner Type');
840 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
841 l_error_count := l_error_count +1;
842 --dbms_output.put_line('Partner Type' || 'is missing ');
843 x_exit_partner := 'TRUE';
844 end if;
845
846 if NOT l_has_member_type = 'Y' then
847 fnd_message.set_name('PV', 'PV_IMP_REQ_DATA');
848 fnd_message.set_token( 'PARAM', 'Member Type');
849 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
850 l_error_count := l_error_count +1;
851 --dbms_output.put_line('Member Type' || 'is missing ');
852 x_exit_partner := 'TRUE';
853 end if;
854
855 if l_is_subsidiary = 'Y' then
856
857 l_gbl_orig_system := p_partner_details_rec.gbl_orig_system;
858 l_gbl_orig_system_ref := p_partner_details_rec.gbl_orig_system_ref;
859 l_gbl_party_id := p_partner_details_rec.gbl_party_id;
860 l_partner_name := p_partner_details_rec.partner_name;
861 l_gbl_partner_id := p_partner_details_rec.gbl_partner_id;
862
863 Validate_Party(
864 p_party_id => l_gbl_party_id,
865 p_orig_sys => l_gbl_orig_system,
866 p_orig_sys_ref => l_gbl_orig_system_ref,
867 p_partner_id => l_gbl_partner_id,
868 p_partner_name => l_partner_name,
869 p_type => 'Global ',
870 x_party_id => l_out_gbl_party_id,
871 x_exit_partner => x_exit_partner
872 );
873 l_gbl_party_id := l_out_gbl_party_id;
874
875 if x_exit_partner <> 'TRUE' then
876 OPEN l_get_partner_id(l_gbl_party_id);
877 FETCH l_get_partner_id into l_gbl_partner_id;
878 close l_get_partner_id;
879 if l_gbl_partner_id is null then
880 fnd_message.set_name('PV', 'PV_IMP_GLBL_PARTY');
881 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
882 l_error_count := l_error_count +1;
883 --dbms_output.put_line('Global partner does not exist');
884 x_exit_partner := 'TRUE';
885 end if;
886 end if;
887
888 if x_exit_partner <> 'TRUE' then
889 OPEN l_get_glbl_member_type(l_gbl_partner_id);
890 FETCH l_get_glbl_member_type into l_member_type;
891 CLOSE l_get_glbl_member_type;
892 if l_member_type <> 'GLOBAL' then
893 fnd_message.set_name('PV', 'PV_IMP_NOT_GLBL_PTNR');
897 x_exit_partner := 'TRUE';
894 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
895 l_error_count := l_error_count +1;
896 --dbms_output.put_line('Global partner exists but not of type Global');
898 end if;
899 end if;
900
901
902 end if;
903
904
905
906 --dbms_output.put_line('Valid Partner?? ' || x_exit_partner );
907 if x_exit_partner <> 'TRUE' then
908
909 --dbms_output.put_line('Account Creation' );
910 --dbms_output.put_line('Party Id: ' || l_party_id);
911 l_account_exists := 'N';
912 open l_check_acct_exists(l_party_id);
913 fetch l_check_acct_exists into l_account_exists;
914 close l_check_acct_exists;
915
916 --dbms_output.put_line('account exists? ' || l_account_exists);
917 if l_account_exists <> 'Y' then
918
919
920 --dbms_output.put_line('trying to gen num ');
921 -- if needed generate account_number.
922 -- SELECT generate_customer_number INTO l_gen_cust_num FROM ar_system_parameters;
923
924 -- typically should be set to 'Y' if no we will try to create a new one.
925 -- however, this could error out
926 -- Is this needed????
927 --dbms_output.put_line('Generate cust num ' || l_gen_cust_num);
928 IF l_gen_cust_num = 'N' THEN
929
930 OPEN C_acct_number;
931 FETCH C_acct_number into l_account_rec.account_number;
932 CLOSE C_acct_number;
933
934 l_account_rec.account_number := 'PV'|| l_account_rec.account_number;
935
936 END IF;
937 --dbms_output.put_line('account num ' || l_account_rec.account_number);
938
939 l_account_rec.Created_by_Module := 'PV';
940 l_account_rec.application_id := 691;
941 l_organization_rec.Created_by_Module := 'PV';
942 l_cust_profile_rec.Created_by_Module := 'PV';
943 l_cust_profile_rec.application_id := 691;
944 l_organization_rec.party_rec.party_Id := l_party_id;
945 l_organization_rec.application_id := 691;
946 l_account_rec.account_name := 'System Generated Account';
947
948
949 HZ_CUST_ACCOUNT_V2PUB.create_cust_account
950 (
951 p_init_msg_list => FND_API.G_FALSE,
952 p_cust_account_rec => l_account_rec,
953 p_organization_rec => l_organization_rec,
954 p_customer_profile_rec => l_cust_profile_rec,
955 p_create_profile_amt => FND_API.G_TRUE,
956 x_cust_account_id => l_cust_account_id,
957 x_account_number => l_account_number,
958 x_party_id => l_party_id,
959 x_party_number => l_party_number,
960 x_profile_id => l_profile_id,
961 x_return_status => l_return_status,
962 x_msg_count => l_msg_count,
963 x_msg_data => l_msg_data
964 );
965
966
970 FOR l_msg_index IN 1..l_msg_count LOOP
967 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
968 --dbms_output.put_line('Error Occured: ');
969 --dbms_output.put_line('Error: ' || l_msg_data);
971 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_index));
972 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
973 l_error_count := l_error_count +1;
974 --dbms_output.put_line('Error Details: ');
975 --dbms_output.put_line(substr(apps.fnd_message.get,1,254));
976
977 END LOOP;
978 x_exit_partner := 'TRUE';
979 END IF;
980 --dbms_output.put_line('RETURN STATUS ' || l_return_status);
981 --dbms_output.put_line('MESSAGE ' || l_msg_data);
982 end if;
983 l_party_id := p_party_id;
984
985 if x_exit_partner <> 'TRUE' then
986 --dbms_output.put_line('cALLING CREATE RELATIONSHIP ');
987 --dbms_output.put_line('party id: ' || l_party_id);
988 --dbms_output.put_line('member type: ' || l_partner_member_type);
989
990
991 begin
992 PV_PARTNER_UTIL_PVT.Create_Relationship(
993 p_api_version_number => 1.0
994 ,p_init_msg_list => FND_API.G_FALSE
995 ,p_commit => FND_API.G_FALSE
996 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
997 ,x_return_status => l_return_status
998 ,x_msg_data => l_msg_data
999 ,x_msg_count => l_msg_count
1000 ,p_party_id => l_party_id
1001 ,p_partner_types_tbl => l_partner_types_tbl
1002 ,p_vad_partner_id => Null
1003 ,p_member_type => l_partner_member_type
1004 ,p_global_partner_id => l_gbl_partner_id
1005 ,x_partner_id => l_partner_id
1006 ,x_default_resp_id => l_default_resp_id
1007 ,x_resp_map_rule_id => l_resp_map_rule_id
1008 ,x_group_id => l_group_id
1009 );
1010
1011 EXCEPTION
1012
1013 WHEN FND_API.g_exc_error THEN
1014 l_return_status := FND_API.g_ret_sts_error;
1015 FND_MSG_PUB.count_and_get (
1016 p_encoded => FND_API.g_false
1017 ,p_count => l_msg_count
1018 ,p_data => l_msg_data
1019 );
1020 --dbms_output.put_line('Message : ' || l_msg_data );
1021 WHEN FND_API.g_exc_unexpected_error THEN
1022 l_return_status := FND_API.g_ret_sts_unexp_error ;
1023 FND_MSG_PUB.count_and_get (
1024 p_encoded => FND_API.g_false
1025 ,p_count => l_msg_count
1026 ,p_data => l_msg_data
1027 );
1028 --dbms_output.put_line('Message : ' || l_msg_data );
1029
1030 WHEN OTHERS THEN
1031 --dbms_output.put_line('Exception ' || sqlerrm);
1032 l_return_status := FND_API.g_ret_sts_unexp_error ;
1033 FND_MSG_PUB.count_and_get (
1034 p_encoded => FND_API.g_false
1035 ,p_count => l_msg_count
1036 ,p_data => l_msg_data
1037 );
1038
1039 END;
1040 --dbms_output.put_line('Return status for Create_Rela : ' || l_return_status);
1041 --dbms_output.put_line('Message: ' || l_msg_data);
1042
1043 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1044 --dbms_output.put_line('Error Occured: ');
1045 --dbms_output.put_line('Error: ' || l_msg_data);
1046 FOR l_msg_index IN 1..l_msg_count LOOP
1047 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_index));
1048 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1049 l_error_count := l_error_count +1;
1050 --dbms_output.put_line('Error Details: ');
1051 --dbms_output.put_line(substr(apps.fnd_message.get,1,254));
1052 END LOOP;
1053 x_exit_partner := 'TRUE';
1054 -- p_partner_details_rec.processed := 'N';
1055 END IF;
1056 --dbms_output.put_line('Partner ID: ' || l_partner_id);
1057 --dbms_output.put_line('Exit Partner : ' || x_exit_partner);
1058 x_partner_id := l_partner_id;
1059 end if;
1060 end if;
1061 --dbms_output.put_line('SUCCESSFULLY ENDED PARTNER CREATION ');
1062 END Validate_And_Create_Partner;
1063
1064 -- Start of Comments
1065 --
1066 -- Funtion name : Validate_Update_Attributes
1067 -- Type : Private
1068 -- Function :
1069 --
1070 --
1071 -- Pre-reqs :
1072 --
1073 -- Paramaeters :
1074 -- IN :
1075 --
1076 -- OUT :
1077 --
1078 -- Version :
1079 -- Initial version 1.0
1080 --
1081 -- Notes: Validate all the passed data for an existing partner attributes
1082 --
1083 --
1084 --
1085 -- End of Comments
1086 Procedure Validate_Update_Attributes(
1087 p_partner_details_rec IN partner_details_rec_type,
1088 p_partner_id IN NUMBER,
1089 x_exit_partner OUT NOCOPY varchar2
1090 )
1091 IS
1092
1093
1094 CURSOR l_get_partner_type(cv_partner_id IN NUMBER) IS
1095 select
1096 attr_value
1097 from
1098 pv_enty_attr_values
1099 where
1100 entity_id = cv_partner_id and
1101 attribute_id = 3 and
1102 latest_flag = 'Y';
1103
1104
1105
1106 l_bound Number;
1107 u_bound Number;
1108 l_has_partner_type varchar2(1);
1109 l_has_member_type varchar2(1);
1110 l_new_partner_type varchar2(500);
1111 l_attribute_details_tbl attr_details_tbl_type;
1112 l_attr_values_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type; --attr_values_tbl_type;
1113 l_partner_id number;
1114 l_current_partner_type varchar2(500);
1115
1116
1117
1118 BEGIN
1119 --dbms_output.put_line('let me check');
1120 l_attribute_details_tbl := p_partner_details_rec.attribute_details_tbl;
1121 l_bound := l_attribute_details_tbl.first;
1122 u_bound := l_attribute_details_tbl.last;
1123 x_exit_partner := 'FALSE';
1124
1125
1126 for i in l_bound..u_bound
1127 loop
1128 if l_attribute_details_tbl(i).attribute_id = 6 then
1129 l_has_member_type := 'Y';
1130 end if;
1131 if l_attribute_details_tbl(i).attribute_id = 3 then
1132 l_has_partner_type := 'Y';
1133 l_attr_values_tbl := l_attribute_details_tbl(i).attr_values_tbl;
1134 for j in l_attr_values_tbl.first..l_attr_values_tbl.last
1135 loop
1136 l_new_partner_type := l_attr_values_tbl(j).attr_value;
1137 end loop;
1138 end if;
1139 end loop;
1140
1141 if l_has_member_type = 'Y' then
1142 fnd_message.set_name('PV', 'PV_IMP_UPD_MEM_TYP');
1143 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1144 l_error_count := l_error_count +1;
1145 x_exit_partner := 'TRUE';
1146 --dbms_output.put_line('updating member type is not allowed while importing');
1147 end if;
1148
1149 if l_has_partner_type = 'Y' then
1150
1151 OPEN l_get_partner_type(p_partner_id);
1152 FETCH l_get_partner_type into l_current_partner_type;
1153 close l_get_partner_type;
1154 --dbms_output.put_line('Current partner type ' || l_current_partner_type);
1155 --dbms_output.put_line('new partner type ' || l_new_partner_type);
1156 if l_current_partner_type = 'VAD' or l_new_partner_type = 'VAD' then
1157 if not (l_current_partner_type = 'VAD' AND l_new_partner_type = 'VAD') then
1158 fnd_message.set_name('PV', 'PV_IMP_UPD_PTNR_TYP');
1159 fnd_message.set_token( 'FROM', l_current_partner_type );
1160 fnd_message.set_token( 'TO', l_new_partner_type );
1161 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1162 l_error_count := l_error_count +1;
1163 x_exit_partner := 'TRUE';
1164 --dbms_output.put_line('Cannot update partner type to VAD');
1165 end if;
1166 end if;
1167
1168 end if;
1169
1170 END Validate_Update_Attributes;
1171
1172 -- Start of Comments
1173 --
1174 -- Funtion name : Upsert_Attr_Values
1175 -- Type : Private
1176 -- Function :
1177 --
1178 --
1179 -- Pre-reqs :
1180 --
1181 -- Paramaeters :
1182 -- IN :
1183 --
1184 -- OUT :
1185 --
1186 -- Version :
1187 -- Initial version 1.0
1188 --
1189 -- Notes: Upsert all the attributes for the partner, also take care of
1190 -- creating the channel team and update the partner profile based
1191 -- on business rules.
1192 --
1193 -- End of Comments
1194
1195 PROCEDURE Complete_Prtnr_Prfls_Rec(
1196 p_prtnr_prfls_rec IN PVX_PRTNR_PRFLS_PVT.prtnr_prfls_rec_type
1197 ,x_complete_rec OUT NOCOPY PVX_PRTNR_PRFLS_PVT.prtnr_prfls_rec_type
1198 )
1199 IS
1200
1201 CURSOR c_prtnr_prfls IS
1202 SELECT
1203 partner_profile_id
1204 ,last_update_date
1205 ,last_updated_by
1206 ,creation_date
1207 ,created_by
1208 ,last_update_login
1209 ,object_version_number
1210 ,partner_id
1211 ,target_revenue_amt
1212 ,actual_revenue_amt
1213 ,target_revenue_pct
1214 ,actual_revenue_pct
1215 ,orig_system_reference
1216 ,orig_system_type
1217 ,capacity_size
1218 ,capacity_amount
1219 ,auto_match_allowed_flag
1220 ,purchase_method
1221 ,cm_id
1222 ,ph_support_rep
1223 --,security_group_id
1224 ,lead_sharing_status
1225 ,lead_share_appr_flag
1226 ,partner_relationship_id
1227 ,partner_level
1228 ,preferred_vad_id
1229 ,partner_group_id
1233 ,sales_partner_flag
1230 ,partner_resource_id
1231 ,partner_group_number
1232 ,partner_resource_number
1234 ,indirectly_managed_flag
1235 ,channel_marketing_manager
1236 ,related_partner_id
1237 ,max_users
1238 ,partner_party_id
1239 ,status
1240
1241 FROM PV_PARTNER_PROFILES
1242 WHERE partner_profile_id = p_prtnr_prfls_rec.partner_profile_id;
1243
1244 l_prtnr_prfls_rec PVX_PRTNR_PRFLS_PVT.prtnr_prfls_rec_type;
1245
1246 BEGIN
1247
1248 x_complete_rec := p_prtnr_prfls_rec;
1249
1250 OPEN c_prtnr_prfls;
1251 FETCH c_prtnr_prfls INTO l_prtnr_prfls_rec;
1252 IF c_prtnr_prfls%NOTFOUND THEN
1253 CLOSE c_prtnr_prfls;
1254 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1255 FND_MESSAGE.set_name('PV', 'PV_NO_RECORD_FOUND');
1256 FND_MSG_PUB.add;
1257 END IF;
1258 RAISE FND_API.g_exc_error;
1259 END IF;
1260 CLOSE c_prtnr_prfls;
1261
1262 IF p_prtnr_prfls_rec.partner_id is null THEN
1263 x_complete_rec.partner_id := l_prtnr_prfls_rec.partner_id;
1264 END IF;
1265
1266 IF p_prtnr_prfls_rec.target_revenue_amt is null THEN
1267 x_complete_rec.target_revenue_amt := l_prtnr_prfls_rec.target_revenue_amt;
1268 END IF;
1269
1270 IF p_prtnr_prfls_rec.actual_revenue_amt is null THEN
1271 x_complete_rec.actual_revenue_amt := l_prtnr_prfls_rec.actual_revenue_amt;
1272 END IF;
1273
1274 IF p_prtnr_prfls_rec.target_revenue_pct is null THEN
1275 x_complete_rec.target_revenue_pct := l_prtnr_prfls_rec.target_revenue_pct;
1276 END IF;
1277
1278 IF p_prtnr_prfls_rec.actual_revenue_pct is null THEN
1279 x_complete_rec.actual_revenue_pct := l_prtnr_prfls_rec.actual_revenue_pct;
1280 END IF;
1281
1282 IF p_prtnr_prfls_rec.orig_system_reference is null THEN
1283 x_complete_rec.orig_system_reference := l_prtnr_prfls_rec.orig_system_reference;
1284 END IF;
1285
1286 IF p_prtnr_prfls_rec.orig_system_type is null THEN
1287 x_complete_rec.orig_system_type := l_prtnr_prfls_rec.orig_system_type;
1288 END IF;
1289
1290 IF p_prtnr_prfls_rec.capacity_size is null THEN
1291 x_complete_rec.capacity_size := l_prtnr_prfls_rec.capacity_size;
1292 END IF;
1293
1294 IF p_prtnr_prfls_rec.capacity_amount is null THEN
1295 x_complete_rec.capacity_amount := l_prtnr_prfls_rec.capacity_amount;
1296 END IF;
1297
1298 IF p_prtnr_prfls_rec.auto_match_allowed_flag is null THEN
1299 x_complete_rec.auto_match_allowed_flag := l_prtnr_prfls_rec.auto_match_allowed_flag;
1300 END IF;
1301
1302 IF p_prtnr_prfls_rec.purchase_method is null THEN
1303 x_complete_rec.purchase_method := l_prtnr_prfls_rec.purchase_method;
1304 END IF;
1305
1306 IF p_prtnr_prfls_rec.cm_id is null THEN
1307 x_complete_rec.cm_id := l_prtnr_prfls_rec.cm_id;
1308 END IF;
1309
1310 IF p_prtnr_prfls_rec.ph_support_rep is null THEN
1311 x_complete_rec.ph_support_rep := l_prtnr_prfls_rec.ph_support_rep;
1312 END IF;
1313
1314 IF p_prtnr_prfls_rec.object_version_number is null THEN
1315 x_complete_rec.object_version_number := l_prtnr_prfls_rec.object_version_number;
1316 END IF;
1317
1318 IF p_prtnr_prfls_rec.lead_sharing_status is null THEN
1319 x_complete_rec.lead_sharing_status := l_prtnr_prfls_rec.lead_sharing_status;
1320 END IF;
1321
1322 IF p_prtnr_prfls_rec.lead_share_appr_flag is null THEN
1323 x_complete_rec.lead_share_appr_flag := l_prtnr_prfls_rec.lead_share_appr_flag;
1324 END IF;
1325
1326 IF p_prtnr_prfls_rec.partner_relationship_id is null THEN
1327 x_complete_rec.partner_relationship_id := l_prtnr_prfls_rec.partner_relationship_id;
1328 END IF;
1329
1330 IF p_prtnr_prfls_rec.partner_level is null THEN
1331 x_complete_rec.partner_level := l_prtnr_prfls_rec.partner_level;
1332 END IF;
1333
1334 IF p_prtnr_prfls_rec.preferred_vad_id is null THEN
1335 x_complete_rec.preferred_vad_id := l_prtnr_prfls_rec.preferred_vad_id;
1336 END IF;
1337
1338 IF p_prtnr_prfls_rec.partner_group_id is null THEN
1339 x_complete_rec.partner_group_id := l_prtnr_prfls_rec.partner_group_id;
1340 END IF;
1341
1342 IF p_prtnr_prfls_rec.partner_resource_id is null THEN
1343 x_complete_rec.partner_resource_id := l_prtnr_prfls_rec.partner_resource_id;
1344 END IF;
1345
1346 IF p_prtnr_prfls_rec.partner_group_number is null THEN
1347 x_complete_rec.partner_group_number := l_prtnr_prfls_rec.partner_group_number;
1348 END IF;
1349
1350 IF p_prtnr_prfls_rec.partner_resource_number is null THEN
1351 x_complete_rec.partner_resource_number := l_prtnr_prfls_rec.partner_resource_number;
1352 END IF;
1353
1354 IF p_prtnr_prfls_rec.sales_partner_flag is null THEN
1355 x_complete_rec.sales_partner_flag := l_prtnr_prfls_rec.sales_partner_flag;
1356 END IF;
1357
1358 IF p_prtnr_prfls_rec.indirectly_managed_flag is null THEN
1359 x_complete_rec.indirectly_managed_flag := l_prtnr_prfls_rec.indirectly_managed_flag;
1360 END IF;
1361
1362 IF p_prtnr_prfls_rec.channel_marketing_manager is null THEN
1363 x_complete_rec.channel_marketing_manager := l_prtnr_prfls_rec.channel_marketing_manager;
1364 END IF;
1365
1366 IF p_prtnr_prfls_rec.related_partner_id is null THEN
1367 x_complete_rec.related_partner_id := l_prtnr_prfls_rec.related_partner_id;
1368 END IF;
1369
1370 IF p_prtnr_prfls_rec.max_users is null THEN
1371 x_complete_rec.max_users := l_prtnr_prfls_rec.max_users;
1372 END IF;
1373
1374 IF p_prtnr_prfls_rec.partner_party_id is null THEN
1375 x_complete_rec.partner_party_id := l_prtnr_prfls_rec.partner_party_id;
1376 END IF;
1377
1378
1382 --
1379 END Complete_Prtnr_Prfls_Rec;
1380
1381 -- Start of Comments
1383 -- Funtion name : Upsert_Attr_Values
1384 -- Type : Private
1385 -- Function :
1386 --
1387 --
1388 -- Pre-reqs :
1389 --
1390 -- Paramaeters :
1391 -- IN :
1392 --
1393 -- OUT :
1394 --
1395 -- Version :
1396 -- Initial version 1.0
1397 --
1398 -- Notes: Upsert all the attributes for the partner, also take care of
1399 -- creating the channel team and update the partner profile based
1400 -- on business rules.
1401 --
1402 -- End of Comments
1403 PROCEDURE Upsert_Attr_Values (
1404 p_entity_id IN NUMBER
1405 ,p_partner_attrs_tbl IN attr_details_tbl_type
1406 ,p_mode IN VARCHAR2
1407 ,x_exit_partner OUT NOCOPY VARCHAR2
1408
1409 ) IS
1410
1411 l_bound NUMBER;
1412 u_bound NUMBER;
1413 l_partner_types_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type;
1414 l_attr_values_tbl PV_ENTY_ATTR_VALUE_PUB.attr_value_tbl_type; --attr_values_tbl_type;
1415 l_attribute_id NUMBER;
1416 l_version NUMBER;
1417
1418 l_return_status VARCHAR2(1);
1419 l_msg_count NUMBER;
1420 l_msg_data VARCHAR(2000);
1421
1422 l_current_partner_level VARCHAR2(30);
1423 l_new_partner_level VARCHAR2(30);
1424 l_prtnr_prfls_rec PVX_PRTNR_PRFLS_PVT.prtnr_prfls_rec_type;
1425 l_complete_prtnr_prfls_rec PVX_PRTNR_PRFLS_PVT.prtnr_prfls_rec_type;
1426 l_partner_profile_id NUMBER;
1427
1428 l_update_channel_team VARCHAR2(1);
1429 l_prtnr_qflr_flg_rec PV_TERR_ASSIGN_PUB.prtnr_qflr_flg_rec_type;
1430 l_prtnr_access_id_tbl PV_TERR_ASSIGN_PUB.prtnr_aces_tbl_type;
1431 l_log_params_tbl PVX_UTILITY_PVT.log_params_tbl_type;
1432 l_current_partner_type varchar2(30);
1433 l_new_partner_type varchar2(30);
1434 l_update_history VARCHAR2(1);
1435 l_new_partner_level_id number;
1436
1437
1438
1439 CURSOR l_get_attr_version(cv_entity_id IN NUMBER, cv_attr_id IN Number) IS
1440 SELECT
1441 max(version)
1442 FROM
1443 pv_enty_attr_values
1444 WHERE
1445 attribute_id = cv_attr_id and
1446 entity_id = cv_entity_id;
1447
1448
1449 CURSOR l_get_partner_level(cv_partner_id IN number) is
1450 select
1451 attr_code
1452 from
1453 pv_partner_profiles val,
1454 pv_attribute_codes_b cod
1455 where
1456 cod.attr_code_id = val.partner_level and
1457 val.partner_id = cv_partner_id;
1458
1459
1460 CURSOR l_get_partner_level_id(cv_partner_level_cd IN varchar2) IS
1461 select
1462 attr_code_id
1463 from
1464 pv_attribute_codes_b
1465 where
1466 attr_code = cv_partner_level_cd;
1467
1468 CURSOR l_get_partner_profile_id(cv_partner_id IN varchar2) IS
1469 select
1470 partner_profile_id
1471 from
1472 pv_partner_profiles
1473 where
1474 partner_id = cv_partner_id;
1475
1476 CURSOR l_get_partner_type(cv_partner_id IN NUMBER) IS
1477 select
1478 attr_value
1479 from
1480 pv_enty_attr_values
1481 where
1482 entity_id = cv_partner_id and
1483 attribute_id = 3 and
1484 latest_flag = 'Y';
1485
1486 BEGIN
1487
1488 --dbms_output.put_line('Processing Upsert ');
1489
1490 l_bound := p_partner_attrs_tbl.first;
1491 u_bound := p_partner_attrs_tbl.last;
1492
1493 l_current_partner_type := null;
1494 OPEN l_get_partner_type(p_entity_id);
1495 fetch l_get_partner_type into l_current_partner_type;
1496 close l_get_partner_type;
1497
1498 for i in l_bound..u_bound
1499 loop
1500
1501 l_attribute_id := p_partner_attrs_tbl(i).attribute_id;
1502 l_attr_values_tbl := p_partner_attrs_tbl(i).attr_values_tbl;
1503
1504 for j in l_attr_values_tbl.first..l_attr_values_tbl.last
1505 loop
1506 l_partner_types_tbl(j).attr_value := l_attr_values_tbl(j).attr_value;
1507 l_partner_types_tbl(j).attr_value_extn := l_attr_values_tbl(j).attr_value_extn;
1508
1509 if l_attribute_id = 19 then
1510 l_new_partner_level := l_attr_values_tbl(j).attr_value;
1511 end if;
1512 if l_attribute_id = 3 then
1513 l_new_partner_type := l_attr_values_tbl(j).attr_value;
1514 l_partner_types_tbl(j).attr_value_extn := 'Y';
1515 if l_current_partner_type <> l_new_partner_type then
1516 l_update_channel_team := 'Y';
1517 l_update_history := 'Y';
1518 end if;
1519 end if;
1520 end loop;
1521
1522 l_version := null;
1523 OPEN l_get_attr_version(p_entity_id,p_partner_attrs_tbl(i).attribute_id);
1524 FETCH l_get_attr_version INTO l_version;
1525 close l_get_attr_version;
1526
1527 if l_version is null then
1528 l_version := 0;
1529 end if;
1530
1531 --dbms_output.put_line('Finished setting channel team flag ');
1535 --dbms_output.put_line('ATTR VALUE EXTN: ' || l_partner_types_tbl(1).attr_value_extn);
1532 IF NOT (p_mode = 'CREATE' AND (l_attribute_id = 3 or l_attribute_id = 6)) THEN
1533 If l_attribute_id <> 19 then
1534 --dbms_output.put_line('Calling upsert for : ' || l_attribute_id);
1536 PV_ENTY_ATTR_VALUE_PUB.Upsert_Attr_Value (
1537 p_api_version_number=> 1.0
1538 ,p_init_msg_list => FND_API.g_true
1539 ,p_commit => FND_API.g_false
1540 ,p_validation_level => FND_API.g_valid_level_full
1541 ,x_return_status => l_return_status
1542 ,x_msg_count => l_msg_count
1543 ,x_msg_data => l_msg_data
1544 ,p_attribute_id => l_attribute_id
1545 ,p_entity => 'PARTNER'
1546 ,p_entity_id => p_entity_id
1547 ,p_version => l_version
1548 ,p_attr_val_tbl => l_partner_types_tbl
1549 );
1550
1551 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1552 --FOR l_msg_index IN 1..l_msg_count LOOP
1553 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_count));
1554 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1555 l_error_count := l_error_count +1;
1556 --dbms_output.put_line(substr(apps.fnd_message.get,1,254));
1557 -- END LOOP;
1558 x_exit_partner := 'TRUE';
1559 l_update_channel_team := 'N';
1560 END IF;
1561 end if;
1562 end if;
1563
1564 if (l_attribute_id = 19) then
1565 OPEN l_get_partner_level(p_entity_id);
1566 FETCH l_get_partner_level into l_current_partner_level;
1567
1568 if l_get_partner_level%notfound then
1569 l_current_partner_level := 'N/A';
1570 end if;
1571
1572 close l_get_partner_level;
1573 if l_new_partner_level <> l_current_partner_level then
1574
1575 l_partner_profile_id := null;
1576 l_new_partner_level_id := null;
1577
1578 OPEN l_get_partner_profile_id(p_entity_id);
1579 FETCH l_get_partner_profile_id into l_partner_profile_id;
1580 CLOSE l_get_partner_profile_id;
1581
1582
1583 OPEN l_get_partner_level_id(l_new_partner_level);
1584 FETCH l_get_partner_level_id into l_new_partner_level_id;
1585 close l_get_partner_level_id;
1586
1587 l_prtnr_prfls_rec.partner_profile_id := l_partner_profile_id;
1588
1589 BEGIN
1590
1591 Complete_Prtnr_Prfls_Rec(
1592 p_prtnr_prfls_rec => l_prtnr_prfls_rec
1593 ,x_complete_rec => l_complete_prtnr_prfls_rec);
1594
1595 l_complete_prtnr_prfls_rec.partner_level := l_new_partner_level_id;
1596 PVX_PRTNR_PRFLS_PVT.Update_Prtnr_Prfls(
1597 p_api_version => 1.0
1598 ,p_init_msg_list => FND_API.g_true
1599 ,p_commit => FND_API.g_false
1600 ,p_validation_level => FND_API.g_valid_level_full
1601 ,x_return_status => l_return_status
1602 ,x_msg_count => l_msg_count
1603 ,x_msg_data => l_msg_data
1604 ,p_prtnr_prfls_rec => l_complete_prtnr_prfls_rec);
1605
1606 EXCEPTION
1607
1608 WHEN FND_API.g_exc_error THEN
1609 l_return_status := FND_API.g_ret_sts_error;
1610 FND_MSG_PUB.count_and_get (
1611 p_encoded => FND_API.g_false
1612 ,p_count => l_msg_count
1613 ,p_data => l_msg_data
1614 );
1615 --dbms_output.put_line('Message : ' || l_msg_data );
1616 WHEN FND_API.g_exc_unexpected_error THEN
1617 l_return_status := FND_API.g_ret_sts_unexp_error ;
1618 FND_MSG_PUB.count_and_get (
1619 p_encoded => FND_API.g_false
1620 ,p_count => l_msg_count
1621 ,p_data => l_msg_data
1622 );
1623 --dbms_output.put_line('Message : ' || l_msg_data );
1624 WHEN OTHERS THEN
1625 l_return_status := FND_API.g_ret_sts_unexp_error ;
1626 FND_MSG_PUB.count_and_get (
1627 p_encoded => FND_API.g_false
1628 ,p_count => l_msg_count
1629 ,p_data => l_msg_data
1630 );
1631 END;
1632
1633 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1634 FOR l_msg_index IN 1..l_msg_count LOOP
1638 --dbms_output.put_line(substr(apps.fnd_message.get,1,254));
1635 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_index));
1636 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1637 l_error_count := l_error_count +1;
1639 END LOOP;
1640 x_exit_partner := 'TRUE';
1641 else
1642 l_update_channel_team := 'Y';
1643 end if;
1644 end if;
1645 end if;
1646
1647 end loop;
1648
1649
1650 if l_update_channel_team = 'Y' then
1651
1652 PV_TERR_ASSIGN_PUB.Update_Channel_Team
1653 (
1654 p_api_version_number => 1.0,
1655 p_init_msg_list => FND_API.g_true,
1656 p_commit => FND_API.g_false,
1657 p_validation_level => FND_API.g_valid_level_full,
1658 x_return_status => l_return_status,
1659 x_msg_count => l_msg_count,
1660 x_msg_data => l_msg_data,
1661 p_partner_id => p_entity_id,
1662 p_vad_partner_id => Null,
1663 p_mode => 'UPDATE',
1664 p_login_user => Null,
1665 p_upd_prtnr_qflr_flg_rec => l_prtnr_qflr_flg_rec,
1666 x_prtnr_access_id_tbl => l_prtnr_access_id_tbl
1667 );
1668
1669 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1670 FOR l_msg_index IN 1..l_msg_count LOOP
1671 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_index));
1672 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1673 l_error_count := l_error_count +1;
1674 --dbms_output.put_line(substr(apps.fnd_message.get,1,254));
1675 END LOOP;
1676 x_exit_partner := 'TRUE';
1677 END IF;
1678 end if;
1679
1680 IF l_update_history = 'Y' then
1681
1682 --dbms_output.put_line('udpating history ');
1683 l_log_params_tbl(1).param_name := 'ORIGINAL';
1684 l_log_params_tbl(1).param_value := l_current_partner_type;
1685 l_log_params_tbl(2).param_name := 'CURRENT';
1686 l_log_params_tbl(2).param_value := l_new_partner_type;
1687
1688 PVX_UTILITY_PVT.create_history_log(
1689 p_arc_history_for_entity_code => 'GENERAL',
1690 p_history_for_entity_id => p_entity_id,
1691 p_history_category_code => 'PARTNER',
1692 p_message_code => 'PV_PRIMARY_PARTNER_TYPE_CHANGE',
1693 p_partner_id => p_entity_id,
1694 p_access_level_flag => 'V',
1695 p_interaction_level => PVX_Utility_PVT.G_INTERACTION_LEVEL_50,
1696 p_comments => Null,
1697 p_log_params_tbl => l_log_params_tbl,
1698 p_init_msg_list => FND_API.g_true,
1699 p_commit => FND_API.g_false,
1700 x_return_status => l_return_status,
1701 x_msg_count => l_msg_count,
1702 x_msg_data => l_msg_data
1703 );
1704 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1705 FOR l_msg_index IN 1..l_msg_count LOOP
1706 apps.fnd_message.set_encoded(apps.fnd_msg_pub.get(l_msg_index));
1707 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1708 l_error_count := l_error_count +1;
1709 --dbms_output.put_line(substr(apps.fnd_message.get,1,254));
1710 END LOOP;
1711 x_exit_partner := 'TRUE';
1712 END IF;
1713 end if;
1714
1715 --dbms_output.put_line('Getting out of upsert ');
1716
1717 END Upsert_Attr_Values;
1718
1719 -- Start of Comments
1720 --
1721 -- Funtion name : Load_Partners
1722 -- Type : Public
1723 -- Function :
1724 --
1725 --
1726 -- Pre-reqs :
1727 --
1728 -- Paramaeters :
1729 -- IN :
1730 --
1731 -- OUT :
1732 --
1733 -- Version :
1734 -- Initial version 1.0
1735 --
1736 -- Notes: Public API to load all the partners and their attributes. API will
1737 -- do all the business validations and log all the messages.
1738 --
1739 --
1740 -- End of Comments
1741 PROCEDURE Load_Partners
1742 (
1743 p_api_version_number IN NUMBER
1744 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1745 ,p_mode IN VARCHAR2
1746 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1747 ,x_return_status OUT NOCOPY VARCHAR2
1748 ,x_msg_data OUT NOCOPY VARCHAR2
1749 ,x_msg_count OUT NOCOPY NUMBER
1750 ,p_partner_details_tbl IN partner_details_tbl_type
1751 ,p_update_if_exists IN varchar2
1752 ,p_data_block_size IN number
1753 ,x_file_name OUT NOCOPY varchar2
1754 ,x_partner_output_tbl OUT NOCOPY partner_output_tbl_type) IS
1755
1756
1757 -- Get the value of Profile PV_IMPORT_COMMIT_SIZE.
1758 CURSOR l_get_commit_size_csr(cv_profile_name IN VARCHAR2) IS
1762 SELECT
1759 SELECT nvl(fnd_profile.value(cv_profile_name),0) from dual;
1760
1761 CURSOR l_get_party_id(cv_orig_system IN VARCHAR2, cv_orig_system_ref IN VARCHAR2) IS
1763 HZ_PARTIES.PARTY_ID
1764
1765 FROM
1766 HZ_ORIG_SYS_REFERENCES,
1767 HZ_PARTIES
1768
1769 WHERE
1770 HZ_ORIG_SYS_REFERENCES.OWNER_TABLE_ID = HZ_PARTIES.PARTY_ID AND
1771 HZ_PARTIES.PARTY_TYPE = 'ORGANIZATION' AND
1772 HZ_ORIG_SYS_REFERENCES.orig_system = cv_orig_system AND
1773 HZ_ORIG_SYS_REFERENCES.orig_system_reference = cv_orig_system_ref AND
1774 HZ_ORIG_SYS_REFERENCES.owner_table_name = 'HZ_PARTIES' AND
1775 HZ_PARTIES.STATUS = 'A';
1776
1777
1778 CURSOR l_get_partner_id(cv_party_id IN NUMBER) IS
1779 SELECT
1780 partner_id
1781
1782 FROM
1783 pv_partner_profiles
1784
1785 WHERE
1786 partner_party_id = cv_party_id;
1787 -- and status = 'A';
1788
1789
1790 CURSOR l_get_file_dir IS
1791 select
1792 trim(substr(value,0,(instr(value,',') - 1))),
1793 trim(substr(value,(instr(value,',') + 1)))
1794 from v$parameter where name = 'utl_file_dir';
1795
1796
1797 l_data_block_size NUMBER;
1798 l_partnersProcessedCount Number;
1799 l_orig_system VARCHAR2(30);
1800 l_orig_system_ref VARCHAR2(250);
1801 l_partner_name VARCHAR2(360);
1802 l_attributes_count NUMBER;
1803 l_exit_partner VARCHAR2(10);
1804 l_lower_limit NUMBER;
1805 l_upper_limit NUMBER;
1806 l_batch_count NUMBER;
1807 l_party_id NUMBER;
1808 l_out_party_id NUMBER;
1809 l_partner_id NUMBER;
1810 l_out_partner_id NUMBER;
1811 l_mode VARCHAR2(10);
1812 l_running_mode varchar2(20);
1813 l_update_if_exists VARCHAR2(1);
1814 l_status varchar2(10);
1815 l_NS varchar2(20);
1816 l_attribute_details_tbl attr_details_tbl_type;
1817 -- l_attr_error_tbl error_tbl_type;
1818
1819 l_return_status VARCHAR2(1);
1820 l_msg_count NUMBER;
1821 l_msg_data VARCHAR(2000);
1822
1823 l_file_name varchar2(20);
1824 l_log_dir varchar2(100);
1825 l_out_dir varchar2(100);
1826 l_prof varchar2(1);
1827
1828 BEGIN
1829
1830 BEGIN
1831 l_prof := fnd_profile.value('HZ_EXECUTE_API_CALLOUTS');
1832 if l_prof <> 'N' then
1833 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS','N');
1834 end if;
1835
1836 IF p_mode is null then
1837 l_running_mode := 'EVALUATION';
1838 else
1839 l_running_mode := p_mode;
1840 END IF;
1841 l_partnersProcessedCount := 0;
1842 select to_char(systimestamp,'yyddmmsssss') || '.log' into l_file_name from dual;
1843 -- l_file_name := 'myLogFile.log';
1844 open l_get_file_dir;
1845 fetch l_get_file_dir into l_out_dir, l_log_dir;
1846 close l_get_file_dir;
1847
1848 -- UTL_FILE.fclose_all;
1849 l_log_file := utl_file.fopen(trim(l_out_dir), l_file_name, 'w',32767);
1850
1851
1852 /*
1853 Check if the commit size is passed. If not take it from the profile setting.
1854 if the profile is also not set then hard code the value to 50
1855 */
1856 if (p_data_block_size is Null or p_data_block_size = 0) then
1857 l_data_block_size := g_data_block_size;
1858 else
1859 l_data_block_size := p_data_block_size;
1860 end if;
1861 --dbms_output.put_line('Commit Size ' || l_data_block_size);
1862
1863 if p_partner_details_tbl.Count > 0 then
1864
1865 l_batch_count := ceil(p_partner_details_tbl.Count/l_data_block_size);
1866 l_lower_limit := 1;
1867 l_upper_limit := ceil(p_partner_details_tbl.Count/l_batch_count);
1868
1869 for batch_ident in 1..l_batch_count
1870 loop
1871 if batch_ident <> 1 then
1872 l_lower_limit := l_upper_limit + 1;
1873 l_upper_limit := ceil(batch_ident*(p_partner_details_tbl.Count/l_batch_count));
1874 end if;
1875
1876 fnd_message.set_name('PV', 'PV_IMP_SUMMARY');
1877 utl_file.put_line(L_LOG_FILE, substrb(fnd_message.get, 1, 1000));
1878
1879 SAVEPOINT Batch;
1880 for partner_ident in l_lower_limit..l_upper_limit
1881 loop
1882 SAVEPOINT Partner;
1883 l_exit_partner := 'FALSE';
1884 l_error_count := 1;
1885
1886 l_orig_system := p_partner_details_tbl(partner_ident).orig_system;
1887 l_orig_system_ref := p_partner_details_tbl(partner_ident).orig_system_ref;
1888 l_partner_name := p_partner_details_tbl(partner_ident).partner_name;
1889 l_attributes_count := p_partner_details_tbl(partner_ident).attribute_details_tbl.count();
1890 l_party_id := p_partner_details_tbl(partner_ident).party_id;
1894 Validate_Party(
1891 l_partner_id := p_partner_details_tbl(partner_ident).partner_id;
1892
1893
1895 p_party_id => l_party_id,
1896 p_orig_sys => l_orig_system,
1897 p_orig_sys_ref => l_orig_system_ref,
1898 p_partner_id => l_partner_id,
1899 p_partner_name => l_partner_name,
1900 p_type => Null,
1901 x_party_id => l_out_party_id,
1902 x_exit_partner => l_exit_partner
1903 );
1904
1905 l_party_id := l_out_party_id;
1906
1907 --dbms_output.put_line('out of validate party ');
1908 if l_partner_name is null OR (nvl(length(trim(l_partner_name)),0) = 0) then
1909 l_exit_partner := 'TRUE';
1910 fnd_message.set_name('PV', 'PV_IMP_REQ_DATA');
1911 fnd_message.set_token( 'PARAM', 'Partner Name');
1912 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1913 l_error_count := l_error_count +1;
1914
1915 --dbms_output.put_line('l_partner_name missing ');
1916 end if;
1917 --dbms_output.put_line('done checking parnter name ');
1918 if l_attributes_count = 0 then
1919 fnd_message.set_name('PV', 'PV_IMP_REQ_DATA');
1920 fnd_message.set_token( 'PARAM', 'Partner attributes' );
1921 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1922 l_error_count := l_error_count +1;
1923 l_exit_partner := 'TRUE';
1924 --dbms_output.put_line('l_attributes_count missing ');
1925 end if;
1926 --dbms_output.put_line('done checking attr count ');
1927 if l_exit_partner = 'TRUE' then
1928 goto end_of_partner_loop;
1929 end if;
1930 OPEN l_get_partner_id(l_party_id);
1931 FETCH l_get_partner_id into l_partner_id;
1932 if l_get_partner_id%found then
1933 if l_get_partner_id%rowcount > 1 then
1934 if p_partner_details_tbl(partner_ident).partner_id is null then
1935
1936 fnd_message.set_name('PV', 'PV_IMP_MULT_PTNR');
1937 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1938 l_error_count := l_error_count +1;
1939 l_exit_partner := 'TRUE';
1940 goto end_of_partner_loop;
1941 else
1942 l_partner_id := p_partner_details_tbl(partner_ident).partner_id;
1943 end if;
1944 end if;
1945 l_mode := 'UPDATE';
1946 else
1947 l_mode := 'CREATE';
1948 end if;
1949 close l_get_partner_id;
1950 --dbms_output.put_line('finished gettting partner id ');
1951 if l_mode = 'CREATE' then
1952
1953 Validate_And_Create_Partner(
1954 p_party_id => l_party_id,
1955 p_partner_details_rec => p_partner_details_tbl(partner_ident),
1956 x_partner_id => l_out_partner_id,
1957 x_exit_partner => l_exit_partner);
1958
1959 l_partner_id := l_out_partner_id;
1960 --dbms_output.put_line('Created Partner Id ' || l_partner_id);
1961
1962
1963
1964 else
1965
1966 if p_partner_details_tbl(partner_ident).Update_If_Exists is null then
1967 l_update_if_exists := p_partner_details_tbl(partner_ident).Update_If_Exists;
1968 else
1969 l_update_if_exists := p_update_if_exists;
1970 end if;
1971
1972 if NOT (l_update_if_exists = 'Y') then
1973 fnd_message.set_name('PV', 'PV_IMP_PTNR_EXISTS');
1974 l_errors_tbl(l_error_count) := substrb(fnd_message.get, 1, 1000);
1975 l_error_count := l_error_count +1;
1976 --dbms_output.put_line('Partner attributes already exists.');
1977 goto end_of_partner_loop;
1978 end if;
1979 --dbms_output.put_line('calling update for attrs');
1980 Validate_Update_Attributes(
1981 p_partner_details_rec => p_partner_details_tbl(partner_ident),
1982 p_partner_id => l_partner_id,
1983 x_exit_partner => l_exit_partner);
1984
1985 end if;
1986
1987 /************************** Invoke Shome's attr validation routine */
1988 /*
1989 validate_attribute
1993 if l_attr_error_tbl.count > 0 then
1990 ( in_table => p_partner_details_tbl(partner_ident).attribute_details_tbl,
1991 out_table => l_attribute_details_tbl,
1992 err_table => l_attr_error_tbl );
1994 l_exit_partner := 'TRUE';
1995 for i in 1..l_attr_error_tbl.count
1996 loop
1997 l_errors_tbl(l_error_count) := l_attr_error_tbl(i).error_desc;
1998 l_error_count := l_error_count + 1;
1999 end loop;
2000 end if;
2001 */
2002 /*****************************Ends here*************************/
2003
2004
2005 --dbms_output.put_line('l_exit_partner: ' || l_exit_partner);
2006 if l_exit_partner = 'TRUE' then
2007 Rollback TO Partner;
2008 goto end_of_partner_loop;
2009 end if;
2010
2011
2012 --dbms_output.put_line('Calling upsert attrs ');
2013 Upsert_Attr_Values (
2014 p_entity_id => l_partner_id,
2015 --p_partner_attrs_tbl => l_attribute_details_tbl, --p_partner_details_tbl(partner_ident).attribute_details_tbl,
2016 p_partner_attrs_tbl => p_partner_details_tbl(partner_ident).attribute_details_tbl,
2017 p_mode => l_mode,
2018 x_exit_partner => l_exit_partner);
2019
2020 if l_exit_partner = 'TRUE' then
2021 Rollback TO Partner;
2022 goto end_of_partner_loop;
2023 end if;
2024
2025 <<end_of_partner_loop>>
2026
2027 if l_exit_partner = 'TRUE' then
2028 l_status := 'ERROR';
2029 elsif l_mode = 'CREATE' THEN
2030 l_status := 'CREATED';
2031 else
2032 l_status := 'UPDATED';
2033 end if;
2034
2035 if l_running_mode = 'EXECUTION' then
2036 l_partnersProcessedCount := l_partnersProcessedCount + 1;
2037 x_partner_output_tbl(l_partnersProcessedCount).orig_system := l_orig_system;
2038 x_partner_output_tbl(l_partnersProcessedCount).orig_system_ref := l_orig_system_ref;
2039 x_partner_output_tbl(l_partnersProcessedCount).party_id := l_party_id;
2040 x_partner_output_tbl(l_partnersProcessedCount).partner_id := l_partner_id;
2041 x_partner_output_tbl(l_partnersProcessedCount).return_status := l_status;
2042 end if;
2043
2044 --dbms_output.put_line('writing partner id ' || l_partner_id );
2045 fnd_message.set_name('PV', 'PV_IMP_NOT_SUPL');
2046 l_ns := substrb(fnd_message.get, 1, 1000);
2047 utl_file.put_line(L_LOG_FILE, rpad(nvl(l_partner_name,l_ns),30) || rpad(nvl(l_orig_system,l_ns),20) || rpad(nvl(l_orig_system_ref,l_ns),20) || rpad(nvl(to_char(l_party_id),l_ns),20) ||
2048 rpad(nvl(to_char(l_partner_id),l_ns),20) || rpad(nvl(l_status,l_ns),20));
2049 Write_Error();
2050 utl_file.put_line(L_LOG_FILE, '------------------------------------------------------------------------------------------------------------------------------------------------------');
2051 end loop;
2052
2053 if l_running_mode = 'EXECUTION' then
2054 commit;
2055 --dbms_output.put_line('Commiting ');
2056 else
2057 --dbms_output.put_line('Rolling back ');
2058 Rollback to Batch;
2059
2060 end if;
2061
2062 end loop;
2063 else
2064 fnd_message.set_name('PV', 'PV_IMP_NO_PARTNER');
2065 utl_file.put_line(L_LOG_FILE, substrb(fnd_message.get, 1, 1000));
2066 --dbms_output.put_line('No partner data has been passed');
2067 end if;
2068
2069 if l_prof <> 'N' then
2070 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS',l_prof);
2071 end if;
2072
2073 x_file_name := l_out_dir || '/' || l_file_name;
2074 --dbms_output.put_line('output file name ' || x_file_name);
2075 utl_file.fclose(l_log_file);
2076
2077
2078 EXCEPTION
2079 when utl_file.invalid_path then
2080 raise_application_error(-20100,'Invalid Path');
2081 when utl_file.invalid_mode then
2082 raise_application_error(-20101,'Invalid Mode');
2083 when utl_file.invalid_operation then
2084 raise_application_error(-20102,'Invalid Operation');
2085 when utl_file.invalid_filehandle then
2086 raise_application_error(-20103,'Invalid FileHandle');
2087 when utl_file.write_error then
2088 utl_file.fclose(l_log_file);
2089 raise_application_error(-20104,'Write Error');
2090 when utl_file.read_error then
2091 raise_application_error(-20105,'Read Error');
2092 when utl_file.internal_error then
2093 raise_application_error(-20106,'Internal Error');
2094
2095 WHEN OTHERS THEN
2096 --dbms_output.put_line('Exception Occured ' || SQLERRM);
2097 ROLLBACK;
2098 utl_file.put_line(L_LOG_FILE,SQLERRM);
2099 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
2100 utl_file.put_line(L_LOG_FILE,Substr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1,1000));
2101 --dbms_output.put_line('error handling ');
2102 END LOOP;
2103 utl_file.fclose(l_log_file);
2104
2105
2106 END;
2107
2108
2109 END Load_Partners;
2110
2111 END PV_PARTNER_ATTR_LOAD_PUB;
2112
2113
2114