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