[Home] [Help]
PACKAGE BODY: APPS.CSP_CUSTOMER_ACCOUNT_PVT
Source
1 PACKAGE BODY CSP_CUSTOMER_ACCOUNT_PVT AS
2 /* $Header: cspvcusb.pls 120.4 2008/03/28 21:40:42 hhaugeru ship $ */
3 /* This procedure is a copy of hzp_cprof_pkg.create_profile_from_class. */
4 PROCEDURE create_profile_from_class
5 ( x_customer_profile_class_id in number,
6 x_customer_profile_id in out nocopy number,
7 x_customer_id in out nocopy number,
8 x_site_use_id in number,
9 x_collector_id out nocopy number,
10 x_collector_name out nocopy varchar2,
11 x_credit_checking out nocopy varchar2,
12 x_tolerance out nocopy number,
13 x_interest_charges out nocopy varchar2,
14 x_charge_on_fin_charge_flag out nocopy varchar2,
15 x_interest_period_days out nocopy number,
16 x_discount_terms out nocopy varchar2,
17 x_discount_grace_days out nocopy number,
18 x_statements out nocopy varchar2,
19 x_statement_cycle_id out nocopy number,
20 x_statement_cycle_name out nocopy varchar2,
21 x_credit_balance_statements out nocopy varchar2,
22 x_standard_terms out nocopy number,
23 x_standard_terms_name out nocopy varchar2,
24 x_override_terms out nocopy varchar2,
25 x_payment_grace_days out nocopy number,
26 x_dunning_letters out nocopy varchar2,
27 x_dunning_letter_set_id out nocopy number,
28 x_dunning_letter_set_name out nocopy varchar2,
29 x_autocash_hierarchy_id out nocopy number,
30 x_autocash_hierarchy_name out nocopy varchar2,
31 x_auto_rec_incl_disputed_flag out nocopy varchar2,
32 x_tax_printing_option out nocopy varchar2,
33 x_grouping_rule_id out nocopy number,
34 x_grouping_rule_name out nocopy varchar2,
35 x_cons_inv_flag out nocopy varchar2,
36 x_cons_inv_type out nocopy varchar2,
37 x_attribute_category out nocopy varchar2,
38 x_attribute1 out nocopy varchar2,
39 x_attribute2 out nocopy varchar2,
40 x_attribute3 out nocopy varchar2,
41 x_attribute4 out nocopy varchar2,
42 x_attribute5 out nocopy varchar2,
43 x_attribute6 out nocopy varchar2,
44 x_attribute7 out nocopy varchar2,
45 x_attribute8 out nocopy varchar2,
46 x_attribute9 out nocopy varchar2,
47 x_attribute10 out nocopy varchar2,
48 x_attribute11 out nocopy varchar2,
49 x_attribute12 out nocopy varchar2,
50 x_attribute13 out nocopy varchar2,
51 x_attribute14 out nocopy varchar2,
52 x_attribute15 out nocopy varchar2,
53 x_jgzz_attribute_category out nocopy varchar2,
54 x_jgzz_attribute1 out nocopy varchar2,
55 x_jgzz_attribute2 out nocopy varchar2,
56 x_jgzz_attribute3 out nocopy varchar2,
57 x_jgzz_attribute4 out nocopy varchar2,
58 x_jgzz_attribute5 out nocopy varchar2,
59 x_jgzz_attribute6 out nocopy varchar2,
60 x_jgzz_attribute7 out nocopy varchar2,
61 x_jgzz_attribute8 out nocopy varchar2,
62 x_jgzz_attribute9 out nocopy varchar2,
63 x_jgzz_attribute10 out nocopy varchar2,
64 x_jgzz_attribute11 out nocopy varchar2,
65 x_jgzz_attribute12 out nocopy varchar2,
66 x_jgzz_attribute13 out nocopy varchar2,
67 x_jgzz_attribute14 out nocopy varchar2,
68 x_jgzz_attribute15 out nocopy varchar2,
69 x_global_attribute_category out nocopy varchar2,
70 x_global_attribute1 out nocopy varchar2,
71 x_global_attribute2 out nocopy varchar2,
72 x_global_attribute3 out nocopy varchar2,
73 x_global_attribute4 out nocopy varchar2,
74 x_global_attribute5 out nocopy varchar2,
75 x_global_attribute6 out nocopy varchar2,
76 x_global_attribute7 out nocopy varchar2,
77 x_global_attribute8 out nocopy varchar2,
78 x_global_attribute9 out nocopy varchar2,
79 x_global_attribute10 out nocopy varchar2,
80 x_global_attribute11 out nocopy varchar2,
81 x_global_attribute12 out nocopy varchar2,
82 x_global_attribute13 out nocopy varchar2,
83 x_global_attribute14 out nocopy varchar2,
84 x_global_attribute15 out nocopy varchar2,
85 x_global_attribute16 out nocopy varchar2,
86 x_global_attribute17 out nocopy varchar2,
87 x_global_attribute18 out nocopy varchar2,
88 x_global_attribute19 out nocopy varchar2,
89 x_global_attribute20 out nocopy varchar2,
90 x_lockbox_matching_option out nocopy varchar2,
91 x_lockbox_matching_name out nocopy varchar2,
92 x_autocash_hierarchy_id_adr out nocopy number,
93 x_autocash_hierarchy_name_adr out nocopy varchar2,
94 x_return_status out nocopy varchar2,
95 x_msg_count out nocopy number,
96 x_msg_data out nocopy varchar2
97 ) is
98
99 --
100 prof_amt_rec HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
101 x_cust_acct_profile_amt_id NUMBER;
102 tmp_var VARCHAR2(2000);
103 i number;
104 tmp_var1 VARCHAR2(2000);
105
106 cursor c_prof_class is
107 select collector_id,
108 collector_name,
109 credit_checking,
110 tolerance,
111 interest_charges,
112 charge_on_finance_charge_flag,
113 interest_period_days,
114 discount_terms,
115 discount_grace_days,
116 statements,
117 statement_cycle_id,
118 statement_cycle_name,
119 credit_balance_statements,
120 standard_terms,
121 standard_terms_name,
122 override_terms,
123 payment_grace_days,
124 dunning_letters,
125 dunning_letter_set_id,
126 dunning_letter_set_name,
127 autocash_hierarchy_id,
128 autocash_hierarchy_name,
129 auto_rec_incl_disputed_flag,
130 tax_printing_option,
131 grouping_rule_id,
132 grouping_rule_name,
133 cons_inv_flag,
134 cons_inv_type,
135 attribute_category,
136 attribute1,
137 attribute2,
138 attribute3,
139 attribute4,
140 attribute5,
141 attribute6,
142 attribute7,
143 attribute8,
144 attribute9,
145 attribute10,
146 attribute11,
147 attribute12,
148 attribute13,
149 attribute14,
150 attribute15,
151 jgzz_attribute_category,
152 jgzz_attribute1,
153 jgzz_attribute2,
154 jgzz_attribute3,
155 jgzz_attribute4,
156 jgzz_attribute5,
157 jgzz_attribute6,
158 jgzz_attribute7,
159 jgzz_attribute8,
160 jgzz_attribute9,
161 jgzz_attribute10,
162 jgzz_attribute11,
163 jgzz_attribute12,
164 jgzz_attribute13,
165 jgzz_attribute14,
166 jgzz_attribute15,
167 global_attribute_category,
168 global_attribute1,
169 global_attribute2,
170 global_attribute3,
171 global_attribute4,
172 global_attribute5,
173 global_attribute6,
174 global_attribute7,
175 global_attribute8,
176 global_attribute9,
177 global_attribute10,
178 global_attribute11,
179 global_attribute12,
180 global_attribute13,
181 global_attribute14,
182 global_attribute15,
183 global_attribute16,
184 global_attribute17,
185 global_attribute18,
186 global_attribute19,
187 global_attribute20,
188 lockbox_matching_option,
189 lockbox_matching_option_name,
190 autocash_hierarchy_id_for_adr,
191 autocash_hierarchy_name_adr
192 from AR_CUSTOMER_PROFILE_CLASSES_V
193 -- from AR_CUST_PROF_CLASSES_TEST_V
194 where customer_profile_class_id = x_customer_profile_class_id;
195
196
197 cursor c_prof_class_amts is
198 select
199 hz_cust_profile_amts_s.nextval,
200 x_customer_profile_id,
201 currency_code,
202 trx_credit_limit,
203 overall_credit_limit,
204 min_dunning_amount,
205 min_dunning_invoice_amount,
206 max_interest_charge,
207 min_statement_amount,
208 auto_rec_min_receipt_amount,
209 interest_rate,
210 attribute_category,
211 attribute1,
212 attribute2,
213 attribute3,
214 attribute4,
215 attribute5,
216 attribute6,
217 attribute7,
218 attribute8,
219 attribute9,
220 attribute10,
221 attribute11,
222 attribute12,
223 attribute13,
224 attribute14,
225 attribute15,
226 min_fc_balance_amount,
227 min_fc_invoice_amount,
228 x_customer_id,
229 x_site_use_id,
230 jgzz_attribute_category,
231 jgzz_attribute1,
232 jgzz_attribute2,
233 jgzz_attribute3,
234 jgzz_attribute4,
235 jgzz_attribute5,
236 jgzz_attribute6,
237 jgzz_attribute7,
238 jgzz_attribute8,
239 jgzz_attribute9,
240 jgzz_attribute10,
241 jgzz_attribute11,
242 jgzz_attribute12,
243 jgzz_attribute13,
244 jgzz_attribute14,
245 jgzz_attribute15,
246 'CSPSHIPAD'
247 from hz_cust_prof_class_amts
248 where profile_class_id = x_customer_profile_class_id;
249
250 --
251 begin
252 --
253 --
254 open c_prof_class;
255 fetch c_prof_class
256 into x_collector_id,
257 x_collector_name,
258 x_credit_checking,
259 x_tolerance,
260 x_interest_charges,
261 x_charge_on_fin_charge_flag,
262 x_interest_period_days,
263 x_discount_terms,
264 x_discount_grace_days,
265 x_statements,
266 x_statement_cycle_id,
267 x_statement_cycle_name,
268 x_credit_balance_statements,
269 x_standard_terms,
270 x_standard_terms_name,
271 x_override_terms,
272 x_payment_grace_days,
273 x_dunning_letters,
274 x_dunning_letter_set_id,
275 x_dunning_letter_set_name,
276 x_autocash_hierarchy_id,
277 x_autocash_hierarchy_name,
278 x_auto_rec_incl_disputed_flag,
279 x_tax_printing_option,
280 x_grouping_rule_id,
281 x_grouping_rule_name,
282 x_cons_inv_flag,
283 x_cons_inv_type,
284 x_attribute_category,
285 x_attribute1,
286 x_attribute2,
287 x_attribute3,
288 x_attribute4,
289 x_attribute5,
290 x_attribute6,
291 x_attribute7,
292 x_attribute8,
293 x_attribute9,
294 x_attribute10,
295 x_attribute11,
296 x_attribute12,
297 x_attribute13,
298 x_attribute14,
299 x_attribute15,
300 x_jgzz_attribute_category,
301 x_jgzz_attribute1,
302 x_jgzz_attribute2,
303 x_jgzz_attribute3,
304 x_jgzz_attribute4,
305 x_jgzz_attribute5,
306 x_jgzz_attribute6,
307 x_jgzz_attribute7,
308 x_jgzz_attribute8,
309 x_jgzz_attribute9,
310 x_jgzz_attribute10,
311 x_jgzz_attribute11,
312 x_jgzz_attribute12,
313 x_jgzz_attribute13,
314 x_jgzz_attribute14,
315 x_jgzz_attribute15,
316 x_global_attribute_category,
317 x_global_attribute1,
318 x_global_attribute2,
319 x_global_attribute3,
320 x_global_attribute4,
321 x_global_attribute5,
322 x_global_attribute6,
323 x_global_attribute7,
324 x_global_attribute8,
325 x_global_attribute9,
326 x_global_attribute10,
327 x_global_attribute11,
328 x_global_attribute12,
329 x_global_attribute13,
330 x_global_attribute14,
331 x_global_attribute15,
332 x_global_attribute16,
333 x_global_attribute17,
334 x_global_attribute18,
335 x_global_attribute19,
336 x_global_attribute20,
337 x_lockbox_matching_option,
338 x_lockbox_matching_name,
339 x_autocash_hierarchy_id_adr,
340 x_autocash_hierarchy_name_adr;
341
342 --
343 if (c_prof_class%NOTFOUND) then
344 close c_prof_class;
345 raise NO_DATA_FOUND;
346 end if;
347 --
348 close c_prof_class;
349 --
350 -- If the customer_profile_id/customers_id is null we need to generate one so we
351 -- can insert rows into ar_customer_profile_amounts.
352 -- Customer_id wil be null when inserting a profile .
353 -- and not null if they are updating an existing profile.
354 --
355 --
356 if ( x_customer_id is null ) then
357 select hz_cust_accounts_s.nextval into x_customer_id from dual;
358 -- x_customer_id := FND_API.G_MISS_NUM;
359 end if;
360 --
361 --
362 If (x_customer_profile_id is null ) then
363 select hz_customer_profiles_s.nextval into x_customer_profile_id from dual;
364 --x_customer_profile_id := FND_API.G_MISS_NUM;
365 end if;
366 --
367 --
368 --
369 --
370 -- Delete only the profile_amounts that match the currency that is
371 -- present in the profile_amounts of the new customer profile class.
372 --
373 delete from hz_cust_profile_amts
374 where cust_account_profile_id = x_customer_profile_id
375 and currency_code in ( select currency_code
376 from hz_cust_prof_class_amts
377 where profile_class_id = x_customer_profile_class_id
378 );
379 --
383 LOOP
380 -- copy profile amount records from class to customer profile
381 --
382 open c_prof_class_amts;
384 Fetch c_prof_class_amts into
385 prof_amt_rec.cust_acct_profile_amt_id,
386 prof_amt_rec.cust_account_profile_id,
387 prof_amt_rec.currency_code,
388 prof_amt_rec.trx_credit_limit,
389 prof_amt_rec.overall_credit_limit,
390 prof_amt_rec.min_dunning_amount,
391 prof_amt_rec.min_dunning_invoice_amount,
392 prof_amt_rec.max_interest_charge,
393 prof_amt_rec.min_statement_amount,
394 prof_amt_rec.auto_rec_min_receipt_amount,
395 prof_amt_rec.interest_rate,
396 prof_amt_rec.attribute_category,
397 prof_amt_rec.attribute1,
398 prof_amt_rec.attribute2,
399 prof_amt_rec.attribute3,
400 prof_amt_rec.attribute4,
401 prof_amt_rec.attribute5,
402 prof_amt_rec.attribute6,
403 prof_amt_rec.attribute7,
404 prof_amt_rec.attribute8,
405 prof_amt_rec.attribute9,
406 prof_amt_rec.attribute10,
407 prof_amt_rec.attribute11,
408 prof_amt_rec.attribute12,
409 prof_amt_rec.attribute13,
410 prof_amt_rec.attribute14,
411 prof_amt_rec.attribute15,
412 prof_amt_rec.min_fc_balance_amount,
413 prof_amt_rec.min_fc_invoice_amount,
414 prof_amt_rec.cust_account_id,
415 prof_amt_rec.site_use_id,
416 prof_amt_rec.jgzz_attribute_category,
417 prof_amt_rec.jgzz_attribute1,
418 prof_amt_rec.jgzz_attribute2,
419 prof_amt_rec.jgzz_attribute3,
420 prof_amt_rec.jgzz_attribute4,
421 prof_amt_rec.jgzz_attribute5,
422 prof_amt_rec.jgzz_attribute6,
423 prof_amt_rec.jgzz_attribute7,
424 prof_amt_rec.jgzz_attribute8,
425 prof_amt_rec.jgzz_attribute9,
426 prof_amt_rec.jgzz_attribute10,
427 prof_amt_rec.jgzz_attribute11,
428 prof_amt_rec.jgzz_attribute12,
429 prof_amt_rec.jgzz_attribute13,
430 prof_amt_rec.jgzz_attribute14,
431 prof_amt_rec.jgzz_attribute15,
432 prof_amt_rec.created_by_module;
433 exit when c_prof_class_amts%notfound;
434
435 if prof_amt_rec.site_use_id is null then
436 prof_amt_rec.site_use_id := -1;
437 end if;
438
439 HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt (
440 p_init_msg_list => FND_API.G_FALSE,
441 p_check_foreign_key => FND_API.G_FALSE,
442 p_cust_profile_amt_rec => prof_amt_rec,
443 x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id,
444 x_return_status => x_return_status,
445 x_msg_count => x_msg_count,
446 x_msg_data => x_msg_data);
447
448 update hz_cust_profile_amts
449 set site_use_id = null
450 where cust_acct_profile_amt_id = x_cust_acct_profile_amt_id
451 and site_use_id = -1;
452
453 End loop;
454 -- insert into tk7 values (x_return_status, x_msg_count, 'profile from class >>>'||x_msg_data);
455 if x_msg_count > 1 then
456 FOR i IN 1..x_msg_count LOOP
457 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
458 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
459 tmp_var1 := tmp_var1 || ' '|| tmp_var;
460 END LOOP;
461 x_msg_data := tmp_var1;
462 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
463 END IF;
464
465
466 close c_prof_class_amts;
467
468
469 --
470 --
471 end create_profile_from_class;
472 PROCEDURE insert_person_row(
473 c_cust_account_id IN OUT NOCOPY NUMBER ,
474 c_party_id IN OUT NOCOPY NUMBER,
475 c_account_number IN OUT NOCOPY VARCHAR2,
476 c_Attribute_Category IN VARCHAR2,
477 c_Attribute1 IN VARCHAR2,
478 c_Attribute2 IN VARCHAR2,
479 c_Attribute3 IN VARCHAR2,
480 c_Attribute4 IN VARCHAR2,
481 c_Attribute5 IN VARCHAR2,
482 c_Attribute6 IN VARCHAR2,
483 c_Attribute7 IN VARCHAR2,
484 c_Attribute8 IN VARCHAR2,
485 c_Attribute9 IN VARCHAR2,
486 c_Attribute10 IN VARCHAR2,
487 c_Attribute11 IN VARCHAR2,
488 c_Attribute12 IN VARCHAR2,
489 c_Attribute13 IN VARCHAR2,
490 c_Attribute14 IN VARCHAR2,
491 c_Attribute15 IN VARCHAR2,
492 c_Attribute16 IN VARCHAR2,
493 c_Attribute17 IN VARCHAR2,
494 c_Attribute18 IN VARCHAR2,
498 c_global_attribute1 IN VARCHAR2,
495 c_Attribute19 IN VARCHAR2,
496 c_Attribute20 IN VARCHAR2,
497 c_global_attribute_category IN VARCHAR2,
499 c_global_attribute2 IN VARCHAR2,
500 c_global_attribute3 IN VARCHAR2,
501 c_global_attribute4 IN VARCHAR2,
502 c_global_attribute5 IN VARCHAR2,
503 c_global_attribute6 IN VARCHAR2,
504 c_global_attribute7 IN VARCHAR2,
505 c_global_attribute8 IN VARCHAR2,
506 c_global_attribute9 IN VARCHAR2,
507 c_global_attribute10 IN VARCHAR2,
508 c_global_attribute11 IN VARCHAR2,
509 c_global_attribute12 IN VARCHAR2,
510 c_global_attribute13 IN VARCHAR2,
511 c_global_attribute14 IN VARCHAR2,
512 c_global_attribute15 IN VARCHAR2,
513 c_global_attribute16 IN VARCHAR2,
514 c_global_attribute17 IN VARCHAR2,
515 c_global_attribute18 IN VARCHAR2,
516 c_global_attribute19 IN VARCHAR2,
517 c_global_attribute20 IN VARCHAR2,
518 c_orig_system_reference IN VARCHAR2,
519 c_status IN VARCHAR2,
520 c_customer_type IN VARCHAR2,
521 c_customer_class_code IN VARCHAR2,
522 c_primary_salesrep_id IN NUMBER ,
523 c_sales_channel_code IN VARCHAR2,
524 c_order_type_id IN NUMBER,
525 c_price_list_id IN NUMBER ,
526 c_category_code IN VARCHAR2,
527 c_reference_use_flag IN VARCHAR2,
528 c_tax_code IN VARCHAR2,
529 c_third_party_flag IN VARCHAR2,
530 c_competitor_flag IN VARCHAR2,
531 c_fob_point IN VARCHAR2,
532 c_tax_header_level_flag IN VARCHAR2,
533 c_tax_rounding_rule IN VARCHAR2,
534 c_account_name IN VARCHAR2,
535 c_freight_term IN VARCHAR2,
536 c_ship_partial IN VARCHAR2,
537 c_ship_via IN VARCHAR2,
538 c_warehouse_id IN NUMBER,
539 c_payment_term_id IN NUMBER ,
540 c_DATES_NEGATIVE_TOLERANCE IN NUMBER,
541 c_DATES_POSITIVE_TOLERANCE IN NUMBER,
542 c_DATE_TYPE_PREFERENCE IN VARCHAR2,
543 c_OVER_SHIPMENT_TOLERANCE IN NUMBER,
544 c_UNDER_SHIPMENT_TOLERANCE IN NUMBER,
545 c_ITEM_CROSS_REF_PREF IN VARCHAR2,
546 c_OVER_RETURN_TOLERANCE IN NUMBER,
547 c_UNDER_RETURN_TOLERANCE IN NUMBER,
548 c_SHIP_SETS_INCLUDE_LINES_FLAG IN VARCHAR2,
549 c_ARRIVALSETS_INCL_LINES_FLAG IN VARCHAR2,
550 c_SCHED_DATE_PUSH_FLAG IN VARCHAR2,
551 c_INVOICE_QUANTITY_RULE IN VARCHAR2,
552 t_party_id IN NUMBER ,
553 t_party_number IN OUT NOCOPY VARCHAR2,
554 t_customer_key IN VARCHAR2,
555 t_Attribute_Category IN VARCHAR2,
556 t_Attribute1 IN VARCHAR2,
557 t_Attribute2 IN VARCHAR2,
558 t_Attribute3 IN VARCHAR2,
559 t_Attribute4 IN VARCHAR2,
560 t_Attribute5 IN VARCHAR2,
561 t_Attribute6 IN VARCHAR2,
562 t_Attribute7 IN VARCHAR2,
563 t_Attribute8 IN VARCHAR2,
564 t_Attribute9 IN VARCHAR2,
565 t_Attribute10 IN VARCHAR2,
566 t_Attribute11 IN VARCHAR2,
567 t_Attribute12 IN VARCHAR2,
568 t_Attribute13 IN VARCHAR2,
569 t_Attribute14 IN VARCHAR2,
570 t_Attribute15 IN VARCHAR2,
574 t_Attribute19 IN VARCHAR2,
571 t_Attribute16 IN VARCHAR2,
572 t_Attribute17 IN VARCHAR2,
573 t_Attribute18 IN VARCHAR2,
575 t_Attribute20 IN VARCHAR2,
576 t_global_attribute_category IN VARCHAR2,
577 t_global_attribute1 IN VARCHAR2,
578 t_global_attribute2 IN VARCHAR2,
579 t_global_attribute3 IN VARCHAR2,
580 t_global_attribute4 IN VARCHAR2,
581 t_global_attribute5 IN VARCHAR2,
582 t_global_attribute6 IN VARCHAR2,
583 t_global_attribute7 IN VARCHAR2,
584 t_global_attribute8 IN VARCHAR2,
585 t_global_attribute9 IN VARCHAR2,
586 t_global_attribute10 IN VARCHAR2,
587 t_global_attribute11 IN VARCHAR2,
588 t_global_attribute12 IN VARCHAR2,
589 t_global_attribute13 IN VARCHAR2,
590 t_global_attribute14 IN VARCHAR2,
591 t_global_attribute15 IN VARCHAR2,
592 t_global_attribute16 IN VARCHAR2,
593 t_global_attribute17 IN VARCHAR2,
594 t_global_attribute18 IN VARCHAR2,
595 t_global_attribute19 IN VARCHAR2,
596 t_global_attribute20 IN VARCHAR2,
597 o_pre_name_adjunct IN VARCHAR2,
598 o_first_name IN VARCHAR2,
599 o_middle_name IN VARCHAR2,
600 o_last_name IN VARCHAR2,
601 o_name_suffix IN VARCHAR2,
602 o_tax_reference IN VARCHAR2,
603 o_taxpayer_id IN VARCHAR2,
604 o_party_name_phonetic IN VARCHAR2,
605 p_cust_account_profile_id IN NUMBER ,
606 p_cust_account_id IN NUMBER ,
607 p_status IN VARCHAR2,
608 p_collector_id IN NUMBER ,
609 p_credit_analyst_id IN NUMBER ,
610 p_credit_checking IN VARCHAR2,
611 p_next_credit_review_date DATE ,
612 p_tolerance IN NUMBER,
613 p_discount_terms IN VARCHAR2,
614 p_dunning_letters IN VARCHAR2,
615 p_interest_charges IN VARCHAR2,
616 p_send_statements IN VARCHAR2,
617 p_credit_balance_statements IN VARCHAR2,
618 p_credit_hold IN VARCHAR2,
619 p_profile_class_id IN NUMBER ,
620 p_site_use_id IN NUMBER ,
621 p_credit_rating IN VARCHAR2,
622 p_risk_code IN VARCHAR2,
623 p_standard_terms IN NUMBER ,
624 p_override_terms IN VARCHAR2,
625 p_dunning_letter_set_id IN NUMBER,
626 p_interest_period_days IN NUMBER,
627 p_payment_grace_days IN NUMBER,
628 p_discount_grace_days IN NUMBER,
629 p_statement_cycle_id IN NUMBER ,
630 p_account_status IN VARCHAR2,
631 p_percent_collectable IN NUMBER ,
632 p_autocash_hierarchy_id IN NUMBER,
633 p_Attribute_Category IN VARCHAR2,
634 p_Attribute1 IN VARCHAR2,
635 p_Attribute2 IN VARCHAR2,
636 p_Attribute3 IN VARCHAR2,
637 p_Attribute4 IN VARCHAR2,
638 p_Attribute5 IN VARCHAR2,
639 p_Attribute6 IN VARCHAR2,
640 p_Attribute7 IN VARCHAR2,
641 p_Attribute8 IN VARCHAR2,
642 p_Attribute9 IN VARCHAR2,
643 p_Attribute10 IN VARCHAR2,
644 p_Attribute11 IN VARCHAR2,
645 p_Attribute12 IN VARCHAR2,
646 p_Attribute13 IN VARCHAR2,
647 p_Attribute14 IN VARCHAR2,
648 p_Attribute15 IN VARCHAR2,
652 p_grouping_rule_id IN NUMBER ,
649 p_auto_rec_incl_disputed_flag IN VARCHAR2,
650 p_tax_printing_option IN VARCHAR2,
651 p_charge_on_fin_charge_flag IN VARCHAR2,
653 p_clearing_days IN NUMBER,
654 p_jgzz_attribute_category IN VARCHAR2,
655 p_jgzz_attribute1 IN VARCHAR2,
656 p_jgzz_attribute2 IN VARCHAR2,
657 p_jgzz_attribute3 IN VARCHAR2,
658 p_jgzz_attribute4 IN VARCHAR2,
659 p_jgzz_attribute5 IN VARCHAR2,
660 p_jgzz_attribute6 IN VARCHAR2,
661 p_jgzz_attribute7 IN VARCHAR2,
662 p_jgzz_attribute8 IN VARCHAR2,
663 p_jgzz_attribute9 IN VARCHAR2,
664 p_jgzz_attribute10 IN VARCHAR2,
665 p_jgzz_attribute11 IN VARCHAR2,
666 p_jgzz_attribute12 IN VARCHAR2,
667 p_jgzz_attribute13 IN VARCHAR2,
668 p_jgzz_attribute14 IN VARCHAR2,
669 p_jgzz_attribute15 IN VARCHAR2,
670 p_global_attribute_category IN VARCHAR2,
671 p_global_attribute1 IN VARCHAR2,
672 p_global_attribute2 IN VARCHAR2,
673 p_global_attribute3 IN VARCHAR2,
674 p_global_attribute4 IN VARCHAR2,
675 p_global_attribute5 IN VARCHAR2,
676 p_global_attribute6 IN VARCHAR2,
677 p_global_attribute7 IN VARCHAR2,
678 p_global_attribute8 IN VARCHAR2,
679 p_global_attribute9 IN VARCHAR2,
680 p_global_attribute10 IN VARCHAR2,
681 p_global_attribute11 IN VARCHAR2,
682 p_global_attribute12 IN VARCHAR2,
683 p_global_attribute13 IN VARCHAR2,
684 p_global_attribute14 IN VARCHAR2,
685 p_global_attribute15 IN VARCHAR2,
686 p_global_attribute16 IN VARCHAR2,
687 p_global_attribute17 IN VARCHAR2,
688 p_global_attribute18 IN VARCHAR2,
689 p_global_attribute19 IN VARCHAR2,
690 p_global_attribute20 IN VARCHAR2,
691 p_cons_inv_flag IN VARCHAR2,
692 p_cons_inv_type IN VARCHAR2,
693 p_autocash_hier_id_for_adr IN NUMBER ,
694 p_lockbox_matching_option IN VARCHAR2,
695 o_person_profile_id in OUT NOCOPY number,
696 x_msg_count OUT NOCOPY NUMBER,
697 x_msg_data OUT NOCOPY varchar2,
698 x_return_status OUT NOCOPY VARCHAR2) is
699
700
701 acct_rec hz_cust_account_v2pub.cust_account_rec_type;
702 party_rec hz_party_v2pub.party_rec_type;
703 person_rec hz_party_v2pub.person_rec_type;
704 prel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
705 prof_rec hz_customer_profile_v2pub .customer_profile_rec_type;
706
707
708 tmp_var VARCHAR2(2000);
709 i number;
710 tmp_var1 VARCHAR2(2000);
711 x_customer_id number;
712 x_cust_account_number VARCHAR2(100);
713 x_party_id number;
714 x_party_number VARCHAR2(100);
715 i_internal_party_id number;
716 i_pr_party_relationship_id NUMBER;
717 i_pr_party_id NUMBER;
718 i_pr_party_number VARCHAR2(100);
719 -- create_internal_party VARCHAR2(1) := 'Y';
720 x_party_rel_id NUMBER;
721 l_object_version_number NUMBER;
722 l_party_object_version_number NUMBER;
723
724 cursor C_REFERENCE_FOR is select relationship_id from
725 hz_relationships where subject_id = c_party_id
726 and relationship_code = 'REFERENCE_FOR'
727 and subject_table_name = 'HZ_PARTIES'
728 AND object_table_name = 'HZ_PARTIES'
729 AND directional_flag = 'F';
730 x_party_last_update_date date;
731 X_PARTY_REL_LAST_UPDATE_DATE date;
732
733 cursor C_PARTNER_OF is select relationship_id from
734 hz_relationships where subject_id = c_party_id
735 and relationship_code = 'PARTNER_OF'
736 and subject_table_name = 'HZ_PARTIES'
737 and object_table_name = 'HZ_PARTIES'
738 and directional_flag = 'F';
739
740 cursor C_COMPETITOR_OF is select relationship_id from
741 hz_relationships where subject_id = c_party_id
742 and relationship_code = 'COMPETITOR_OF'
743 and subject_table_name = 'HZ_PARTIES'
744 and object_table_name = 'HZ_PARTIES'
745 and directional_flag = 'F';
746
747 begin
751 end if;
748 /*
749 if C_Party_Id is not null then
750 create_internal_party := 'N';
752 */
753 acct_rec.cust_account_id := C_Cust_account_Id;
754 -- acct_rec.party_id := C_Party_Id;
755 acct_rec.account_number := C_account_Number;
756 acct_rec.attribute_category := C_Attribute_Category;
757 acct_rec.attribute1 := C_Attribute1;
758 acct_rec.attribute2 := C_Attribute2;
759 acct_rec.attribute3 := C_Attribute3;
760 acct_rec.attribute4 := C_Attribute4;
761 acct_rec.attribute5 := C_Attribute5;
762 acct_rec.attribute6 := C_Attribute6;
763 acct_rec.attribute7 := C_Attribute7;
764 acct_rec.attribute8 := C_Attribute8;
765 acct_rec.attribute9 := C_Attribute9;
766 acct_rec.attribute10 := C_Attribute10;
767 acct_rec.attribute11 := C_Attribute11;
768 acct_rec.attribute12 := C_Attribute12;
769 acct_rec.attribute13 := C_Attribute13;
770 acct_rec.attribute14 := C_Attribute14;
771 acct_rec.attribute15 := C_Attribute15;
772 acct_rec.attribute16 := C_Attribute16;
773 acct_rec.attribute17 := C_Attribute17;
774 acct_rec.attribute18 := C_Attribute18;
775 acct_rec.attribute19 := C_Attribute19;
776 acct_rec.attribute20 := C_Attribute20;
777 acct_rec.global_attribute_category := C_Global_Attribute_Category;
778 acct_rec.global_attribute1 := C_Global_Attribute1;
779 acct_rec.global_attribute2 := C_Global_Attribute2;
780 acct_rec.global_attribute3 := C_Global_Attribute3;
781 acct_rec.global_attribute4 := C_Global_Attribute4;
782 acct_rec.global_attribute5 := C_Global_Attribute5;
783 acct_rec.global_attribute6 := C_Global_Attribute6;
784 acct_rec.global_attribute7 := C_Global_Attribute7;
785 acct_rec.global_attribute8 := C_Global_Attribute8;
786 acct_rec.global_attribute9 := C_Global_Attribute9;
787 acct_rec.global_attribute10 := C_Global_Attribute10;
788 acct_rec.global_attribute11 := C_Global_Attribute11;
789 acct_rec.global_attribute12 := C_Global_Attribute12;
790 acct_rec.global_attribute13 := C_Global_Attribute13;
791 acct_rec.global_attribute14 := C_Global_Attribute14;
792 acct_rec.global_attribute15 := C_Global_Attribute15;
793 acct_rec.global_attribute16 := C_Global_Attribute16;
794 acct_rec.global_attribute17 := C_Global_Attribute17;
795 acct_rec.global_attribute18 := C_Global_Attribute18;
796 acct_rec.global_attribute19 := C_Global_Attribute19;
797 acct_rec.global_attribute20 := C_Global_Attribute20;
798 acct_rec.orig_system_reference := C_Orig_System_Reference;
799 acct_rec.status := C_Status;
800 acct_rec.customer_type := c_customer_type;
801 acct_rec.customer_class_code := C_Customer_Class_Code;
802 acct_rec.primary_salesrep_id := C_Primary_Salesrep_Id;
803 acct_rec.sales_channel_code := C_Sales_Channel_Code;
804 acct_rec.order_type_id := C_Order_Type_Id;
805 acct_rec.price_list_id := C_Price_List_Id;
806 -- acct_rec.category_code := C_Category_Code;
807 -- acct_rec.reference_use_flag := C_Reference_Use_Flag;
808 acct_rec.tax_code := C_Tax_Code;
809 -- acct_rec.third_party_flag := C_Third_Party_Flag;
810 -- acct_rec.competitor_flag := c_competitor_flag;
811 acct_rec.fob_point := C_Fob_Point;
812 acct_rec.freight_term := C_Freight_Term;
813 acct_rec.ship_partial := C_Ship_Partial;
814 acct_rec.ship_via := C_Ship_Via;
815 acct_rec.warehouse_id := C_Warehouse_Id;
816 acct_rec.tax_header_level_flag := C_Tax_Header_Level_Flag;
817 acct_rec.tax_rounding_rule := C_Tax_Rounding_Rule;
818 acct_rec.coterminate_day_month := NULL; --new
819 acct_rec.primary_specialist_id := NULL;
820 acct_rec.secondary_specialist_id := NULL;
821 --acct_rec.geo_code := NULL;
822 --acct_rec.payment_term_id := NULL;
823 acct_rec.account_liable_flag := null;
824 /* acct_rec.current_balance := null ; --new
825 acct_rec.account_established_date := null ; --new
826 acct_rec.account_termination_date := null ; --new
827 acct_rec.account_activation_date := null ; --new
828 acct_rec.department := null; --new
829 acct_rec.held_bill_expiration_date := null ; --new
830 acct_rec.hold_bill_flag := null ; --new
831 acct_rec.realtime_rate_flag := null ; --new
832 acct_rec.acct_life_cycle_status := null ; --new */
833 acct_rec.account_name := c_account_name;
834
835 acct_rec.DATES_NEGATIVE_TOLERANCE := c_DATES_NEGATIVE_TOLERANCE;
836 acct_rec.DATES_POSITIVE_TOLERANCE := c_DATES_POSITIVE_TOLERANCE;
837 acct_rec.DATE_TYPE_PREFERENCE := c_DATE_TYPE_PREFERENCE;
838 acct_rec.OVER_SHIPMENT_TOLERANCE := c_OVER_SHIPMENT_TOLERANCE;
839 acct_rec.UNDER_SHIPMENT_TOLERANCE := c_UNDER_SHIPMENT_TOLERANCE;
840 acct_rec.OVER_RETURN_TOLERANCE := c_OVER_RETURN_TOLERANCE;
841 acct_rec.UNDER_RETURN_TOLERANCE := c_UNDER_RETURN_TOLERANCE;
842 acct_rec.ITEM_CROSS_REF_PREF := c_ITEM_CROSS_REF_PREF;
843 acct_rec.SHIP_SETS_INCLUDE_LINES_FLAG := c_SHIP_SETS_INCLUDE_LINES_FLAG;
844 acct_rec.ARRIVALSETS_INCLUDE_LINES_FLAG := c_ARRIVALSETS_INCL_LINES_FLAG;
845 acct_rec.SCHED_DATE_PUSH_FLAG := c_SCHED_DATE_PUSH_FLAG;
846 acct_rec.INVOICE_QUANTITY_RULE := c_INVOICE_QUANTITY_RULE;
847 /*acct_rec.status_update_date := null ; --new
848 acct_rec.autopay_flag := null; --new
849 acct_rec.notify_flag := null; --new
850 acct_rec.last_batch_id := null; --new
854
851 acct_rec.selling_party_id := null; --new*/
852 acct_rec.created_by_module := 'CSPSHIPAD'; --new
853 acct_rec.application_id := 523 ; --new
855
856 person_rec.party_rec.party_id := c_party_id;
857 person_rec.person_pre_name_adjunct := o_pre_name_adjunct;
858 person_rec.person_first_name := o_first_name;
859 person_rec.person_middle_name := o_middle_name;
860 person_rec.person_last_name := o_last_name;
861 person_rec.person_name_suffix := o_name_suffix;
862 person_rec.jgzz_fiscal_code := o_taxpayer_id;
863 person_rec.person_name_phonetic := o_party_name_phonetic;
864 -- person_rec.tax_reference := o_tax_reference;
865 person_rec.party_rec.party_id := t_party_id;
866 person_rec.party_rec.party_number := t_party_number;
867 person_rec.party_rec.validated_flag := NULL;
868 person_rec.party_rec.orig_system_reference := c_orig_system_reference;
869 --person_rec.party_rec.customer_key := t_customer_key;
870 person_rec.party_rec.attribute_category := t_Attribute_Category;
871 person_rec.party_rec.attribute1 := t_Attribute1;
872 person_rec.party_rec.attribute2 := t_Attribute2;
873 person_rec.party_rec.attribute3 := t_Attribute3;
874 person_rec.party_rec.attribute4 := t_Attribute4;
875 person_rec.party_rec.attribute5 := t_Attribute5;
876 person_rec.party_rec.attribute6 := t_Attribute6;
877 person_rec.party_rec.attribute7 := t_Attribute7;
878 person_rec.party_rec.attribute8 := t_attribute8;
879 person_rec.party_rec.attribute9 := t_Attribute9;
880 person_rec.party_rec.attribute10 := t_Attribute10;
881 person_rec.party_rec.attribute11 := t_Attribute11;
882 person_rec.party_rec.attribute12 := t_Attribute12;
883 person_rec.party_rec.attribute13 := t_Attribute13;
884 person_rec.party_rec.attribute14 := t_Attribute14;
885 person_rec.party_rec.attribute15 := t_Attribute15;
886 person_rec.party_rec.attribute16 := t_Attribute16;
887 person_rec.party_rec.attribute17 := t_Attribute17;
888 person_rec.party_rec.attribute18 := t_Attribute18;
889 person_rec.party_rec.attribute19 := t_Attribute19;
890 person_rec.party_rec.attribute20 := t_Attribute20;
891 /*person_rec.party_rec.global_attribute_category := t_Global_Attribute_Category;
892 person_rec.party_rec.global_attribute1 := t_Global_Attribute1;
893 person_rec.party_rec.global_attribute2 := t_Global_Attribute2;
894 person_rec.party_rec.global_attribute3 := t_Global_Attribute3;
895 person_rec.party_rec.global_attribute4 := t_Global_Attribute4;
896 person_rec.party_rec.global_attribute5 := t_Global_Attribute5;
897 person_rec.party_rec.global_attribute6 := t_Global_Attribute6;
898 person_rec.party_rec.global_attribute7 := t_Global_Attribute7;
899 person_rec.party_rec.global_attribute8 := t_Global_Attribute8;
900 person_rec.party_rec.global_attribute9 := t_Global_Attribute9;
901 person_rec.party_rec.global_attribute10 := t_Global_Attribute10;
902 person_rec.party_rec.global_attribute11 := t_Global_Attribute11;
903 person_rec.party_rec.global_attribute12 := t_Global_Attribute12;
904 person_rec.party_rec.global_attribute13 := t_Global_Attribute13;
905 person_rec.party_rec.global_attribute14 := t_Global_Attribute14;
906 person_rec.party_rec.global_attribute15 := t_Global_Attribute15;
907 person_rec.party_rec.global_attribute16 := t_Global_Attribute16;
908 person_rec.party_rec.global_attribute17 := t_Global_Attribute17;
909 person_rec.party_rec.global_attribute18 := t_Global_Attribute18;
910 person_rec.party_rec.global_attribute19 := t_Global_Attribute19;
911 person_rec.party_rec.global_attribute20 := t_Global_Attribute20;*/
912 person_rec.party_rec.status := null;
913 person_rec.party_rec.category_code := C_Category_Code;
914 -- person_rec.party_rec.reference_use_flag := C_Reference_Use_Flag;
915 -- person_rec.party_rec.third_party_flag := C_Third_Party_Flag;
916 -- person_rec.party_rec.competitor_flag := c_competitor_flag;
917 /*
918 person_rec.party_rec.party_id := c_party_id;
919 person_rec.pre_name_adjunct := o_pre_name_adjunct;
920 person_rec.first_name := o_first_name;
921 person_rec.middle_name := o_middle_name;
922 person_rec.last_name := o_last_name;
923 person_rec.name_suffix := o_name_suffix;
924 person_rec.jgzz_fiscal_code := o_taxpayer_id;
925 person_rec.person_name_phonetic := o_party_name_phonetic;
926 */
927 prof_rec.cust_account_profile_id := p_cust_account_profile_id;
928 prof_rec.cust_account_id := p_cust_account_id;
929 prof_rec.status := p_status;
930 prof_rec.collector_id := p_collector_id;
931 prof_rec.credit_analyst_id := null;
932 prof_rec.credit_checking := p_credit_checking;
933 prof_rec.next_credit_review_date := null;
934 prof_rec.tolerance := p_tolerance;
935 prof_rec.discount_terms := p_discount_terms;
936 prof_rec.dunning_letters := p_dunning_letters;
937 prof_rec.interest_charges := p_interest_charges;
938 prof_rec.send_statements := p_send_statements;
939 prof_rec.credit_balance_statements := p_credit_balance_statements;
940 prof_rec.credit_hold := p_credit_hold;
941 prof_rec.profile_class_id := p_profile_class_id;
945 prof_rec.standard_terms := p_standard_terms;
942 prof_rec.site_use_id := NULL;
943 prof_rec.credit_rating := p_credit_rating;
944 prof_rec.risk_code := p_risk_code;
946 prof_rec.override_terms := p_override_terms;
947 prof_rec.dunning_letter_set_id := p_dunning_letter_set_id;
948 prof_rec.interest_period_days := p_interest_period_days;
949 prof_rec.payment_grace_days := p_payment_grace_days;
950 prof_rec.discount_grace_days := p_discount_grace_days;
951 prof_rec.statement_cycle_id := p_statement_cycle_id;
952 prof_rec.account_status := p_account_status;
953 prof_rec.percent_collectable := p_percent_collectable;
954 prof_rec.autocash_hierarchy_id := p_autocash_hierarchy_id;
955 prof_rec.attribute_category := p_attribute_category;
956 prof_rec.attribute1 := p_attribute1;
957 prof_rec.attribute2 := p_attribute2;
958 prof_rec.attribute3 := p_attribute3;
959 prof_rec.attribute4 := p_attribute4;
960 prof_rec.attribute5 := p_attribute5;
961 prof_rec.attribute6 := p_attribute6;
962 prof_rec.attribute7 := p_attribute7;
963 prof_rec.attribute8 := p_attribute8;
964 prof_rec.attribute9 := p_attribute9;
965 prof_rec.attribute10 := p_attribute10;
966 prof_rec.attribute11 := p_attribute11;
967 prof_rec.attribute12 := p_attribute12;
968 prof_rec.attribute13 := p_attribute13;
969 prof_rec.attribute14 := p_attribute14;
970 prof_rec.attribute15 := p_attribute15;
971 prof_rec.auto_rec_incl_disputed_flag := p_auto_rec_incl_disputed_flag;
972 prof_rec.tax_printing_option := p_tax_printing_option;
973 prof_rec.charge_on_finance_charge_flag := p_charge_on_fin_charge_flag;
974 prof_rec.grouping_rule_id := p_grouping_rule_id;
975 prof_rec.clearing_days := p_clearing_days;
976 prof_rec.jgzz_attribute_category := p_jgzz_attribute_category;
977 prof_rec.jgzz_attribute1 := p_jgzz_attribute1;
978 prof_rec.jgzz_attribute2 := p_jgzz_attribute2;
979 prof_rec.jgzz_attribute3 := p_jgzz_attribute3;
980 prof_rec.jgzz_attribute4 := p_jgzz_attribute4;
981 prof_rec.jgzz_attribute5 := p_jgzz_attribute5;
982 prof_rec.jgzz_attribute6 := p_jgzz_attribute6;
983 prof_rec.jgzz_attribute7 := p_jgzz_attribute7;
984 prof_rec.jgzz_attribute8 := p_jgzz_attribute8;
985 prof_rec.jgzz_attribute9 := p_jgzz_attribute9;
986 prof_rec.jgzz_attribute10 := p_jgzz_attribute10;
987 prof_rec.jgzz_attribute11 := p_jgzz_attribute11;
988 prof_rec.jgzz_attribute12 := p_jgzz_attribute12;
989 prof_rec.jgzz_attribute13 := p_jgzz_attribute13;
990 prof_rec.jgzz_attribute14 := p_jgzz_attribute14;
991 prof_rec.jgzz_attribute15 := p_jgzz_attribute15;
992 prof_rec.global_attribute1 := p_global_attribute1;
993 prof_rec.global_attribute2 := p_global_attribute2;
994 prof_rec.global_attribute3 := p_global_attribute3;
995 prof_rec.global_attribute4 := p_global_attribute4;
996 prof_rec.global_attribute5 := p_global_attribute5;
997 prof_rec.global_attribute6 := p_global_attribute6;
998 prof_rec.global_attribute7 := p_global_attribute7;
999 prof_rec.global_attribute8 := p_global_attribute8;
1000 prof_rec.global_attribute9 := p_global_attribute9;
1001 prof_rec.global_attribute10 := p_global_attribute10;
1002 prof_rec.global_attribute11 := p_global_attribute11;
1003 prof_rec.global_attribute12 := p_global_attribute12;
1004 prof_rec.global_attribute13 := p_global_attribute13;
1005 prof_rec.global_attribute14 := p_global_attribute14;
1006 prof_rec.global_attribute15 := p_global_attribute15;
1007 prof_rec.global_attribute16 := p_global_attribute16;
1008 prof_rec.global_attribute17 := p_global_attribute17;
1009 prof_rec.global_attribute18 := p_global_attribute18;
1010 prof_rec.global_attribute19 := p_global_attribute19;
1011 prof_rec.global_attribute20 := p_global_attribute20;
1012 prof_rec.global_attribute_category := p_global_attribute_category;
1013 prof_rec.cons_inv_flag := p_cons_inv_flag;
1014 prof_rec.cons_inv_type := p_cons_inv_type;
1015 prof_rec.autocash_hierarchy_id_for_adr := p_autocash_hier_id_for_adr;
1016 prof_rec.lockbox_matching_option := p_lockbox_matching_option ;
1017
1018
1019 hz_cust_account_v2pub.create_cust_account(
1020 FND_API.g_false,
1021 acct_rec,
1022 person_rec,
1023 prof_rec,
1024 FND_API.G_FALSE,
1025 x_customer_id,
1026 x_cust_account_number,
1027 x_party_id,
1028 x_party_number,
1029 o_person_profile_id,
1030 x_return_status,
1031 x_msg_count,
1032 x_msg_data
1033 );
1034 c_cust_account_id := x_customer_id;
1035 c_party_id := x_party_id;
1036 t_party_number := x_party_number;
1037 c_account_number := x_cust_account_number;
1038
1042 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1039 -- insert into tk7 values (x_return_status, x_msg_count, 'create per >>>'||x_msg_data);
1040 if x_msg_count > 1 then
1041 FOR i IN 1..x_msg_count LOOP
1043 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1044 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1045 END LOOP;
1046 x_msg_data := tmp_var1;
1047 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1048 END IF;
1049
1050
1051 if x_return_status <> 'S' then
1052 return;
1053 end if;
1054
1055 i_internal_party_id := fnd_profile.value('HZ_INTERNAL_PARTY');
1056 if i_internal_party_id is not null then
1057 -- insert into testqq values (prel_rec.subject_id,prel_rec.object_id);
1058 -- commit;
1059 if C_Reference_Use_Flag is not null then
1060 open C_REFERENCE_FOR;
1061 fetch C_REFERENCE_FOR into x_party_rel_id;
1062 close C_REFERENCE_FOR;
1063 if x_party_rel_id is null then
1064 if C_Reference_Use_Flag = 'Y' then
1065 prel_rec.subject_id := x_party_id;
1066 prel_rec.object_id := i_internal_party_id;
1067 prel_rec.start_date := sysdate;
1068 prel_rec.end_date := null;
1069 prel_rec.relationship_type := 'REFERENCE_FOR';
1070 prel_rec.start_date := sysdate;
1071 prel_rec.created_by_module := 'CSPSHIPAD';
1072 /*HZ_PARTY_PUB.create_party_relationship (
1073 1,
1074 null,
1075 null,
1076 prel_rec,
1077 'N',
1078 x_return_status,
1079 x_msg_count,
1080 x_msg_data,
1081 i_pr_party_relationship_id,
1082 i_pr_party_id,
1083 i_pr_party_number);*/
1084 HZ_RELATIONSHIP_V2PUB.create_relationship (
1085 p_init_msg_list => FND_API.G_FALSE,
1086 p_relationship_rec => prel_rec,
1087 x_relationship_id => i_pr_party_relationship_id,
1088 x_party_id => i_pr_party_id,
1089 x_party_number => i_pr_party_number,
1090 x_return_status => x_return_status,
1091 x_msg_count => x_msg_count,
1092 x_msg_data => x_msg_data);
1093 if x_msg_count > 1 then
1094 FOR i IN 1..x_msg_count LOOP
1095 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1096 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1097 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1098 END LOOP;
1099 x_msg_data := tmp_var1;
1100 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1101 END IF;
1102 end if;
1103 end if;
1104 if x_return_status <> 'S' then
1105 return;
1106 end if;
1107 if x_party_rel_id is not null then
1108 /* Update of party relationship table */
1109 prel_rec.party_rec.party_id := c_party_id;
1110
1111 /* select last_update_date into x_party_rel_last_update_date
1112 from hz_party_relationships where party_relationship_id = x_party_rel_id;
1113
1114 select last_update_date into x_party_last_update_date
1115 from hz_parties where party_id = c_party_id;*/
1116
1117 select object_version_number into l_object_version_number
1118 from hz_relationships where relationship_id = x_party_rel_id;
1119
1120 select object_version_number into l_party_object_version_number
1121 from hz_parties where party_id = c_party_id;
1122
1123 prel_rec.relationship_id := x_party_rel_id;
1124 if C_Reference_Use_Flag = 'N' then
1125 prel_rec.end_date := sysdate;
1126 else
1127 prel_rec.end_date := null;
1128 end if;
1129
1130 HZ_RELATIONSHIP_V2PUB.update_relationship (
1131 p_init_msg_list => FND_API.G_FALSE,
1132 p_relationship_rec => prel_rec ,
1133 p_object_version_number => l_object_version_number,
1134 p_party_object_version_number => l_party_object_version_number,
1135 x_return_status => x_return_status,
1136 x_msg_count =>x_msg_count ,
1137 x_msg_data => x_msg_data);
1138
1139 -- insert into tk7 values (x_return_status, x_msg_count, 'up ofg prf pty >>>'||x_msg_data);
1140 if x_msg_count > 1 then
1141 FOR i IN 1..x_msg_count LOOP
1142 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1143 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1144 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1145 END LOOP;
1146 x_msg_data := tmp_var1;
1147 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1148 END IF;
1149 end if;
1150 end if ; -- C_Reference_Use_Flag is not null
1151
1152
1153 if x_return_status <> 'S' then
1154 return;
1155 end if;
1156
1157 if C_Third_Party_Flag is not null then
1158 open C_PARTNER_OF;
1159 fetch C_PARTNER_OF into x_party_rel_id;
1160 close C_PARTNER_OF;
1161 if x_party_rel_id is null then
1162 if C_Third_Party_Flag = 'Y' then
1166 prel_rec.end_date := null;
1163 prel_rec.subject_id := x_party_id;
1164 prel_rec.object_id := i_internal_party_id;
1165 prel_rec.start_date := sysdate;
1167 prel_rec.relationship_type := 'PARTNER_OF';
1168 prel_rec.created_by_module := 'CSPSHIPAD';
1169 HZ_RELATIONSHIP_V2PUB.create_relationship (
1170 p_init_msg_list => FND_API.G_FALSE,
1171 p_relationship_rec => prel_rec,
1172 x_relationship_id => i_pr_party_relationship_id,
1173 x_party_id => i_pr_party_id,
1174 x_party_number => i_pr_party_number,
1175 x_return_status => x_return_status,
1176 x_msg_count => x_msg_count,
1177 x_msg_data => x_msg_data);
1178 if x_msg_count > 1 then
1179 FOR i IN 1..x_msg_count LOOP
1180 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1181 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1182 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1183 END LOOP;
1184 x_msg_data := tmp_var1;
1185 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1186 END IF;
1187 end if;
1188 end if;
1189 if x_return_status <> 'S' then
1190 return;
1191 end if;
1192 if x_party_rel_id is not null then
1193 /* Update of party relationship table */
1194 prel_rec.party_rec.party_id := c_party_id;
1195
1196 /* select last_update_date into x_party_rel_last_update_date
1197 from hz_party_relationships where party_relationship_id = x_party_rel_id;
1198
1199 select last_update_date into x_party_last_update_date
1200 from hz_parties where party_id = c_party_id;*/
1201
1202 select object_version_number into l_object_version_number
1203 from hz_relationships where relationship_id = x_party_rel_id;
1204
1205 select object_version_number into l_party_object_version_number
1206 from hz_parties where party_id = c_party_id;
1207
1208 prel_rec.relationship_id := x_party_rel_id;
1209 if C_Third_Party_Flag = 'N' then
1210 prel_rec.end_date := sysdate;
1211 else
1212 prel_rec.end_date := null;
1213 end if;
1214 HZ_RELATIONSHIP_V2PUB.update_relationship (
1215 p_init_msg_list => FND_API.G_FALSE,
1216 p_relationship_rec => prel_rec ,
1217 p_object_version_number => l_object_version_number,
1218 p_party_object_version_number => l_party_object_version_number,
1219 x_return_status => x_return_status,
1220 x_msg_count =>x_msg_count ,
1221 x_msg_data => x_msg_data);
1222
1223 -- insert into tk7 values (x_return_status, x_msg_count, 'up ofg prf pty >>>'||x_msg_data);
1224 if x_msg_count > 1 then
1225 FOR i IN 1..x_msg_count LOOP
1226 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1227 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1228 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1229 END LOOP;
1230 x_msg_data := tmp_var1;
1231 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1232 END IF;
1233 end if;
1234 end if ; -- C_Third_Party_Flag is not null
1235
1236
1237
1238 if x_return_status <> 'S' then
1239 return;
1240 end if;
1241
1242
1243 if c_competitor_flag is not null then
1244 open C_COMPETITOR_OF;
1245 fetch C_COMPETITOR_OF into x_party_rel_id;
1246 close C_COMPETITOR_OF;
1247 if x_party_rel_id is null then
1248 if c_competitor_flag = 'Y' then
1249 prel_rec.subject_id := x_party_id;
1250 prel_rec.object_id := i_internal_party_id;
1251 prel_rec.start_date := sysdate;
1252 prel_rec.end_date := null;
1253 prel_rec.relationship_type := 'COMPETITOR_OF';
1254 prel_rec.start_date := sysdate;
1255 prel_rec.created_by_module := 'CSPSHIPAD';
1256 HZ_RELATIONSHIP_V2PUB.create_relationship (
1257 p_init_msg_list => FND_API.G_FALSE,
1258 p_relationship_rec => prel_rec,
1259 x_relationship_id => i_pr_party_relationship_id,
1260 x_party_id => i_pr_party_id,
1261 x_party_number => i_pr_party_number,
1262 x_return_status => x_return_status,
1263 x_msg_count => x_msg_count,
1264 x_msg_data => x_msg_data);
1265 if x_msg_count > 1 then
1266 FOR i IN 1..x_msg_count LOOP
1267 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1268 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1269 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1270 END LOOP;
1271 x_msg_data := tmp_var1;
1272 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1273 END IF;
1274 end if;
1275 end if;
1276 if x_return_status <> 'S' then
1277 return;
1278 end if;
1279 if x_party_rel_id is not null then
1280 /* Update of party relationship table */
1281 prel_rec.party_rec.party_id := c_party_id;
1282
1283 /*select last_update_date,object_version_number into x_party_rel_last_update_date,l_object_version_number
1284 from hz_party_relationships where party_relationship_id = x_party_rel_id;
1285
1286 select last_update_date into x_party_last_update_date
1287 from hz_parties where party_id = c_party_id;*/
1288
1289 select object_version_number into l_object_version_number
1290 from hz_relationships where relationship_id = x_party_rel_id;
1291
1292 select object_version_number into l_party_object_version_number
1293 from hz_parties where party_id = c_party_id;
1294
1295 prel_rec.relationship_id := x_party_rel_id;
1296 if c_competitor_flag = 'N' then
1297 prel_rec.end_date := sysdate;
1298 else
1299 prel_rec.end_date := null;
1300 end if;
1301 /*HZ_PARTY_PUB.update_party_relationship (
1302 1,
1303 null,
1304 null,
1305 prel_rec,
1306 x_party_rel_last_update_date,
1307 x_party_last_update_date,
1308 x_return_status,
1309 x_msg_count,
1310 x_msg_data);*/
1311 HZ_RELATIONSHIP_V2PUB.update_relationship (
1312 p_init_msg_list => FND_API.G_FALSE,
1313 p_relationship_rec => prel_rec ,
1314 p_object_version_number => l_object_version_number,
1315 p_party_object_version_number => l_party_object_version_number,
1316 x_return_status => x_return_status,
1317 x_msg_count =>x_msg_count ,
1318 x_msg_data => x_msg_data);
1319
1320 -- insert into tk7 values (x_return_status, x_msg_count, 'up ofg prf pty >>>'||x_msg_data);
1321 if x_msg_count > 1 then
1322 FOR i IN 1..x_msg_count LOOP
1323 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1324 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var);
1325 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1326 END LOOP;
1327 x_msg_data := tmp_var1;
1328 -- insert into tk7 values (x_return_status, x_msg_count, tmp_var1);
1329 END IF;
1330 end if;
1331 end if ; -- c_competitor_flag is not null
1332
1333 end if;
1334 -- end if; -- for internal party
1335
1336 end insert_person_row;
1337 END CSP_CUSTOMER_ACCOUNT_PVT;