[Home] [Help]
PACKAGE BODY: APPS.HZ_ORGANIZATION_PROFILES_PKG
Source
1 PACKAGE BODY HZ_organization_profiles_pkg AS
2 /*$Header: ARHORGTB.pls 120.6 2005/07/26 18:59:40 jhuang ship $ */
3
4 g_miss_content_source_type CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5 g_sst_source_type CONSTANT VARCHAR2(30) := 'SST';
6
7 FUNCTION do_copy_duns_number(
8 p_duns_number_c IN VARCHAR2
9 ) RETURN NUMBER IS
10
11 l_char VARCHAR2(1);
12 l_str HZ_ORGANIZATION_PROFILES.DUNS_NUMBER_C%TYPE;
13
14 BEGIN
15
16 -- if duns_number is null and duns_number_c is not null then get the
17 -- value of duns_number_c, convert it to number and copy it to duns_number
18
19 /* Bug 3435702.This check is done before calling this procedure and as such is redundant.
20 |
21 | IF p_duns_number_c IS NOT NULL AND
22 | p_duns_number_c <> FND_API.G_MISS_CHAR
23 | THEN
24 */
25 FOR i IN 1..LENGTHB(p_duns_number_c) LOOP
26 l_char := SUBSTRB(p_duns_number_c, i, 1);
27 IF (l_char >= '0' AND l_char <= '9') THEN
28 l_str := l_str || l_char;
29 END IF;
30 END LOOP;
31 RETURN TO_NUMBER(l_str);
32 /* END IF;*/
33
34 RETURN NULL;
35
36 END do_copy_duns_number;
37
38
39 PROCEDURE insert_row (
40 x_rowid OUT NOCOPY ROWID,
41 x_organization_profile_id IN OUT NOCOPY NUMBER,
42 x_party_id IN NUMBER,
43 x_organization_name IN VARCHAR2,
44 x_attribute_category IN VARCHAR2,
45 x_attribute1 IN VARCHAR2,
46 x_attribute2 IN VARCHAR2,
47 x_attribute3 IN VARCHAR2,
48 x_attribute4 IN VARCHAR2,
49 x_attribute5 IN VARCHAR2,
50 x_attribute6 IN VARCHAR2,
51 x_attribute7 IN VARCHAR2,
52 x_attribute8 IN VARCHAR2,
53 x_attribute9 IN VARCHAR2,
54 x_attribute10 IN VARCHAR2,
55 x_attribute11 IN VARCHAR2,
56 x_attribute12 IN VARCHAR2,
57 x_attribute13 IN VARCHAR2,
58 x_attribute14 IN VARCHAR2,
59 x_attribute15 IN VARCHAR2,
60 x_attribute16 IN VARCHAR2,
61 x_attribute17 IN VARCHAR2,
62 x_attribute18 IN VARCHAR2,
63 x_attribute19 IN VARCHAR2,
64 x_attribute20 IN VARCHAR2,
65 x_enquiry_duns IN VARCHAR2,
66 x_ceo_name IN VARCHAR2,
67 x_ceo_title IN VARCHAR2,
68 x_principal_name IN VARCHAR2,
69 x_principal_title IN VARCHAR2,
70 x_legal_status IN VARCHAR2,
71 x_control_yr IN NUMBER,
72 x_employees_total IN NUMBER,
73 x_hq_branch_ind IN VARCHAR2,
74 x_branch_flag IN VARCHAR2,
75 x_oob_ind IN VARCHAR2,
76 x_line_of_business IN VARCHAR2,
77 x_cong_dist_code IN VARCHAR2,
78 x_sic_code IN VARCHAR2,
79 x_import_ind IN VARCHAR2,
80 x_export_ind IN VARCHAR2,
81 x_labor_surplus_ind IN VARCHAR2,
82 x_debarment_ind IN VARCHAR2,
83 x_minority_owned_ind IN VARCHAR2,
84 x_minority_owned_type IN VARCHAR2,
85 x_woman_owned_ind IN VARCHAR2,
86 x_disadv_8a_ind IN VARCHAR2,
87 x_small_bus_ind IN VARCHAR2,
88 x_rent_own_ind IN VARCHAR2,
89 x_debarments_count IN NUMBER,
90 x_debarments_date IN DATE,
91 x_failure_score IN VARCHAR2,
92 x_failure_score_override_code IN VARCHAR2,
93 x_failure_score_commentary IN VARCHAR2,
94 x_global_failure_score IN VARCHAR2,
95 x_db_rating IN VARCHAR2,
96 x_credit_score IN VARCHAR2,
97 x_credit_score_commentary IN VARCHAR2,
98 x_paydex_score IN VARCHAR2,
99 x_paydex_three_months_ago IN VARCHAR2,
100 x_paydex_norm IN VARCHAR2,
101 x_best_time_contact_begin IN DATE,
102 x_best_time_contact_end IN DATE,
103 x_organization_name_phonetic IN VARCHAR2,
104 x_tax_reference IN VARCHAR2,
105 x_gsa_indicator_flag IN VARCHAR2,
106 x_jgzz_fiscal_code IN VARCHAR2,
107 x_analysis_fy IN VARCHAR2,
108 x_fiscal_yearend_month IN VARCHAR2,
109 x_curr_fy_potential_revenue IN NUMBER,
110 x_next_fy_potential_revenue IN NUMBER,
111 x_year_established IN NUMBER,
112 x_mission_statement IN VARCHAR2,
113 x_organization_type IN VARCHAR2,
114 x_business_scope IN VARCHAR2,
115 x_corporation_class IN VARCHAR2,
116 x_known_as IN VARCHAR2,
117 x_local_bus_iden_type IN VARCHAR2,
118 x_local_bus_identifier IN VARCHAR2,
119 x_pref_functional_currency IN VARCHAR2,
120 x_registration_type IN VARCHAR2,
121 x_total_employees_text IN VARCHAR2,
122 x_total_employees_ind IN VARCHAR2,
123 x_total_emp_est_ind IN VARCHAR2,
124 x_total_emp_min_ind IN VARCHAR2,
125 x_parent_sub_ind IN VARCHAR2,
126 x_incorp_year IN NUMBER,
127 x_content_source_type IN VARCHAR2,
128 x_content_source_number IN VARCHAR2,
129 x_effective_start_date IN DATE,
130 x_effective_end_date IN DATE,
131 x_sic_code_type IN VARCHAR2,
132 x_public_private_ownership IN VARCHAR2,
133 x_local_activity_code_type IN VARCHAR2,
134 x_local_activity_code IN VARCHAR2,
135 x_emp_at_primary_adr IN VARCHAR2,
136 x_emp_at_primary_adr_text IN VARCHAR2,
137 x_emp_at_primary_adr_est_ind IN VARCHAR2,
138 x_emp_at_primary_adr_min_ind IN VARCHAR2,
139 x_internal_flag IN VARCHAR2,
140 x_high_credit IN NUMBER,
141 x_avg_high_credit IN NUMBER,
142 x_total_payments IN NUMBER,
143 x_known_as2 IN VARCHAR2,
144 x_known_as3 IN VARCHAR2,
145 x_known_as4 IN VARCHAR2,
146 x_known_as5 IN VARCHAR2,
147 x_credit_score_class IN NUMBER,
148 x_credit_score_natl_percentile IN NUMBER,
149 x_credit_score_incd_default IN NUMBER,
150 x_credit_score_age IN NUMBER,
151 x_credit_score_date IN DATE,
152 x_failure_score_class IN NUMBER,
153 x_failure_score_incd_default IN NUMBER,
154 x_failure_score_age IN NUMBER,
155 x_failure_score_date IN DATE,
156 x_failure_score_commentary2 IN VARCHAR2,
157 x_failure_score_commentary3 IN VARCHAR2,
158 x_failure_score_commentary4 IN VARCHAR2,
159 x_failure_score_commentary5 IN VARCHAR2,
160 x_failure_score_commentary6 IN VARCHAR2,
161 x_failure_score_commentary7 IN VARCHAR2,
162 x_failure_score_commentary8 IN VARCHAR2,
163 x_failure_score_commentary9 IN VARCHAR2,
164 x_failure_score_commentary10 IN VARCHAR2,
165 x_credit_score_commentary2 IN VARCHAR2,
166 x_credit_score_commentary3 IN VARCHAR2,
167 x_credit_score_commentary4 IN VARCHAR2,
168 x_credit_score_commentary5 IN VARCHAR2,
169 x_credit_score_commentary6 IN VARCHAR2,
170 x_credit_score_commentary7 IN VARCHAR2,
171 x_credit_score_commentary8 IN VARCHAR2,
172 x_credit_score_commentary9 IN VARCHAR2,
173 x_credit_score_commentary10 IN VARCHAR2,
174 x_maximum_credit_recomm IN NUMBER,
175 x_maximum_credit_currency_code IN VARCHAR2,
176 x_displayed_duns_party_id IN NUMBER,
177 x_failure_score_natnl_perc IN NUMBER,
178 x_duns_number_c IN VARCHAR2,
179 x_bank_or_branch_number IN VARCHAR2 := fnd_api.g_miss_char,
180 x_bank_code IN VARCHAR2 := fnd_api.g_miss_char,
181 x_branch_code IN VARCHAR2 := fnd_api.g_miss_char,
182 x_object_version_number IN NUMBER,
183 x_created_by_module IN VARCHAR2,
184 x_application_id IN NUMBER,
185 x_do_not_confuse_with IN VARCHAR2 := NULL,
186 x_actual_content_source IN VARCHAR2,
187 x_version_number IN NUMBER DEFAULT 1,
188 x_home_country IN VARCHAR2 DEFAULT NULL
189 ) IS
190
191 l_duns_number NUMBER;
192 l_success VARCHAR2(1) := 'N';
193 l_duns_number_c HZ_PARTIES.duns_number_c%type := X_DUNS_NUMBER_C;
194 BEGIN
195
196 IF x_duns_number_c IS NOT NULL AND
197 x_duns_number_c <> FND_API.G_MISS_CHAR
198 THEN
199 l_duns_number := do_copy_duns_number(x_duns_number_c);
200 END IF;
201
202 IF x_duns_number_c IS NOT NULL AND
203 x_duns_number_c <> FND_API.G_MISS_CHAR AND
204 LENGTHB(x_duns_number_c)<9
205 THEN
206 l_duns_number_c:=lpad(x_duns_number_c,9,'0');
207 END IF;
208
209
210 WHILE l_success = 'N' LOOP
211 BEGIN
212 INSERT INTO hz_organization_profiles (
213 organization_profile_id,
214 party_id,
215 organization_name,
216 attribute_category,
217 attribute1,
218 attribute2,
219 attribute3,
220 attribute4,
221 attribute5,
222 attribute6,
223 attribute7,
224 attribute8,
225 attribute9,
226 attribute10,
227 attribute11,
228 attribute12,
229 attribute13,
230 attribute14,
231 attribute15,
232 attribute16,
233 attribute17,
234 attribute18,
235 attribute19,
236 attribute20,
237 enquiry_duns,
238 ceo_name,
239 ceo_title,
240 principal_name,
241 principal_title,
242 legal_status,
243 control_yr,
244 employees_total,
245 hq_branch_ind,
246 branch_flag,
247 oob_ind,
248 line_of_business,
249 cong_dist_code,
250 sic_code,
251 import_ind,
252 export_ind,
253 labor_surplus_ind,
254 debarment_ind,
255 minority_owned_ind,
256 minority_owned_type,
257 woman_owned_ind,
258 disadv_8a_ind,
259 small_bus_ind,
260 rent_own_ind,
261 debarments_count,
262 debarments_date,
263 failure_score,
264 failure_score_override_code,
265 failure_score_commentary,
266 global_failure_score,
267 db_rating,
268 credit_score,
269 credit_score_commentary,
270 paydex_score,
271 paydex_three_months_ago,
272 paydex_norm,
273 best_time_contact_begin,
274 best_time_contact_end,
275 organization_name_phonetic,
276 tax_reference,
277 gsa_indicator_flag,
278 jgzz_fiscal_code,
279 analysis_fy,
283 year_established,
280 fiscal_yearend_month,
281 curr_fy_potential_revenue,
282 next_fy_potential_revenue,
284 mission_statement,
285 organization_type,
286 business_scope,
287 corporation_class,
288 known_as,
289 local_bus_iden_type,
290 local_bus_identifier,
291 pref_functional_currency,
292 registration_type,
293 total_employees_text,
294 total_employees_ind,
295 total_emp_est_ind,
296 total_emp_min_ind,
297 parent_sub_ind,
298 incorp_year,
299 last_update_date,
300 last_updated_by,
301 creation_date,
302 created_by,
303 last_update_login,
304 request_id,
305 program_application_id,
306 program_id,
307 program_update_date,
308 content_source_type,
309 content_source_number,
310 effective_start_date,
311 effective_end_date,
312 sic_code_type,
313 public_private_ownership_flag,
314 local_activity_code_type,
315 local_activity_code,
316 emp_at_primary_adr,
317 emp_at_primary_adr_text,
318 emp_at_primary_adr_est_ind,
319 emp_at_primary_adr_min_ind,
320 internal_flag,
321 high_credit,
322 avg_high_credit,
323 total_payments,
324 known_as2,
325 known_as3,
326 known_as4,
327 known_as5,
328 credit_score_class,
329 credit_score_natl_percentile,
330 credit_score_incd_default,
331 credit_score_age,
332 credit_score_date,
333 failure_score_class,
334 failure_score_incd_default,
335 failure_score_age,
336 failure_score_date,
337 failure_score_commentary2,
338 failure_score_commentary3,
339 failure_score_commentary4,
340 failure_score_commentary5,
341 failure_score_commentary6,
342 failure_score_commentary7,
343 failure_score_commentary8,
344 failure_score_commentary9,
345 failure_score_commentary10,
346 credit_score_commentary2,
347 credit_score_commentary3,
348 credit_score_commentary4,
349 credit_score_commentary5,
350 credit_score_commentary6,
351 credit_score_commentary7,
352 credit_score_commentary8,
353 credit_score_commentary9,
354 credit_score_commentary10,
355 maximum_credit_recommendation,
356 maximum_credit_currency_code,
357 displayed_duns_party_id,
358 failure_score_natnl_percentile,
359 duns_number_c,
360 duns_number,
361 bank_or_branch_number,
362 bank_code,
363 branch_code,
364 object_version_number,
365 created_by_module,
366 application_id,
367 do_not_confuse_with,
368 actual_content_source,
369 version_number,
370 home_country
371 )
372 VALUES (
373 DECODE(x_organization_profile_id,
374 fnd_api.g_miss_num, hz_organization_profiles_s.NEXTVAL,
375 NULL, hz_organization_profiles_s.NEXTVAL,
376 x_organization_profile_id),
377 DECODE(x_party_id, fnd_api.g_miss_num, NULL, x_party_id),
378 DECODE(x_organization_name,
379 fnd_api.g_miss_char, NULL,
380 x_organization_name),
381 DECODE(x_attribute_category,
382 fnd_api.g_miss_char, NULL,
383 x_attribute_category),
384 DECODE(x_attribute1, fnd_api.g_miss_char, NULL, x_attribute1),
385 DECODE(x_attribute2, fnd_api.g_miss_char, NULL, x_attribute2),
386 DECODE(x_attribute3, fnd_api.g_miss_char, NULL, x_attribute3),
387 DECODE(x_attribute4, fnd_api.g_miss_char, NULL, x_attribute4),
388 DECODE(x_attribute5, fnd_api.g_miss_char, NULL, x_attribute5),
389 DECODE(x_attribute6, fnd_api.g_miss_char, NULL, x_attribute6),
390 DECODE(x_attribute7, fnd_api.g_miss_char, NULL, x_attribute7),
391 DECODE(x_attribute8, fnd_api.g_miss_char, NULL, x_attribute8),
392 DECODE(x_attribute9, fnd_api.g_miss_char, NULL, x_attribute9),
393 DECODE(x_attribute10, fnd_api.g_miss_char, NULL, x_attribute10),
394 DECODE(x_attribute11, fnd_api.g_miss_char, NULL, x_attribute11),
395 DECODE(x_attribute12, fnd_api.g_miss_char, NULL, x_attribute12),
396 DECODE(x_attribute13, fnd_api.g_miss_char, NULL, x_attribute13),
397 DECODE(x_attribute14, fnd_api.g_miss_char, NULL, x_attribute14),
398 DECODE(x_attribute15, fnd_api.g_miss_char, NULL, x_attribute15),
399 DECODE(x_attribute16, fnd_api.g_miss_char, NULL, x_attribute16),
400 DECODE(x_attribute17, fnd_api.g_miss_char, NULL, x_attribute17),
401 DECODE(x_attribute18, fnd_api.g_miss_char, NULL, x_attribute18),
402 DECODE(x_attribute19, fnd_api.g_miss_char, NULL, x_attribute19),
403 DECODE(x_attribute20, fnd_api.g_miss_char, NULL, x_attribute20),
404 DECODE(x_enquiry_duns, fnd_api.g_miss_char, NULL, x_enquiry_duns),
405 DECODE(x_ceo_name, fnd_api.g_miss_char, NULL, x_ceo_name),
406 DECODE(x_ceo_title, fnd_api.g_miss_char, NULL, x_ceo_title),
407 DECODE(x_principal_name,
408 fnd_api.g_miss_char, NULL,
409 x_principal_name),
410 DECODE(x_principal_title,
411 fnd_api.g_miss_char, NULL,
415 DECODE(x_employees_total,
412 x_principal_title),
413 DECODE(x_legal_status, fnd_api.g_miss_char, NULL, x_legal_status),
414 DECODE(x_control_yr, fnd_api.g_miss_num, NULL, x_control_yr),
416 fnd_api.g_miss_num, NULL,
417 x_employees_total),
418 DECODE(x_hq_branch_ind, fnd_api.g_miss_char, NULL, x_hq_branch_ind),
419 DECODE(x_branch_flag, fnd_api.g_miss_char, NULL, x_branch_flag),
420 DECODE(x_oob_ind, fnd_api.g_miss_char, NULL, x_oob_ind),
421 DECODE(x_line_of_business,
422 fnd_api.g_miss_char, NULL,
423 x_line_of_business),
424 DECODE(x_cong_dist_code,
425 fnd_api.g_miss_char, NULL,
426 x_cong_dist_code),
427 DECODE(x_sic_code, fnd_api.g_miss_char, NULL, x_sic_code),
428 DECODE(x_import_ind, fnd_api.g_miss_char, NULL, x_import_ind),
429 DECODE(x_export_ind, fnd_api.g_miss_char, NULL, x_export_ind),
430 DECODE(x_labor_surplus_ind,
431 fnd_api.g_miss_char, NULL,
432 x_labor_surplus_ind),
433 DECODE(x_debarment_ind, fnd_api.g_miss_char, NULL, x_debarment_ind),
434 DECODE(x_minority_owned_ind,
435 fnd_api.g_miss_char, NULL,
436 x_minority_owned_ind),
437 DECODE(x_minority_owned_type,
438 fnd_api.g_miss_char, NULL,
439 x_minority_owned_type),
440 DECODE(x_woman_owned_ind,
441 fnd_api.g_miss_char, NULL,
442 x_woman_owned_ind),
443 DECODE(x_disadv_8a_ind, fnd_api.g_miss_char, NULL, x_disadv_8a_ind),
444 DECODE(x_small_bus_ind, fnd_api.g_miss_char, NULL, x_small_bus_ind),
445 DECODE(x_rent_own_ind, fnd_api.g_miss_char, NULL, x_rent_own_ind),
446 DECODE(x_debarments_count,
447 fnd_api.g_miss_num, NULL,
448 x_debarments_count),
449 DECODE(x_debarments_date,
450 fnd_api.g_miss_date, to_date(NULL),
451 x_debarments_date),
452 DECODE(x_failure_score, fnd_api.g_miss_char, NULL, x_failure_score),
453 DECODE(x_failure_score_override_code,
454 fnd_api.g_miss_char, NULL,
455 x_failure_score_override_code),
456 DECODE(x_failure_score_commentary,
457 fnd_api.g_miss_char, NULL,
458 x_failure_score_commentary),
459 DECODE(x_global_failure_score,
460 fnd_api.g_miss_char, NULL,
461 x_global_failure_score),
462 DECODE(x_db_rating, fnd_api.g_miss_char, NULL, x_db_rating),
463 DECODE(x_credit_score, fnd_api.g_miss_char, NULL, x_credit_score),
464 DECODE(x_credit_score_commentary,
465 fnd_api.g_miss_char, NULL,
466 x_credit_score_commentary),
467 DECODE(x_paydex_score,
468 fnd_api.g_miss_char, NULL,
469 x_paydex_score),
470 DECODE(x_paydex_three_months_ago,
471 fnd_api.g_miss_char, NULL,
472 x_paydex_three_months_ago),
473 DECODE(x_paydex_norm, fnd_api.g_miss_char, NULL, x_paydex_norm),
474 DECODE(x_best_time_contact_begin,
475 fnd_api.g_miss_date, TO_DATE(NULL),
476 x_best_time_contact_begin),
477 DECODE(x_best_time_contact_end,
478 fnd_api.g_miss_date, TO_DATE(NULL),
479 x_best_time_contact_end),
480 DECODE(x_organization_name_phonetic,
481 fnd_api.g_miss_char, NULL,
482 x_organization_name_phonetic),
483 DECODE(x_tax_reference, fnd_api.g_miss_char, NULL, x_tax_reference),
484 DECODE(x_gsa_indicator_flag,
485 fnd_api.g_miss_char, NULL,
486 x_gsa_indicator_flag),
487 DECODE(x_jgzz_fiscal_code,
488 fnd_api.g_miss_char, NULL,
489 x_jgzz_fiscal_code),
490 DECODE(x_analysis_fy, fnd_api.g_miss_char, NULL, x_analysis_fy),
491 DECODE(x_fiscal_yearend_month,
492 fnd_api.g_miss_char, NULL,
493 x_fiscal_yearend_month),
494 DECODE(x_curr_fy_potential_revenue,
495 fnd_api.g_miss_num, NULL,
496 x_curr_fy_potential_revenue),
497 DECODE(x_next_fy_potential_revenue,
498 fnd_api.g_miss_num, NULL,
499 x_next_fy_potential_revenue),
500 DECODE(x_year_established,
501 fnd_api.g_miss_num, NULL,
502 x_year_established),
503 DECODE(x_mission_statement,
504 fnd_api.g_miss_char, NULL,
505 x_mission_statement),
506 DECODE(x_organization_type,
507 fnd_api.g_miss_char, NULL,
508 x_organization_type),
509 DECODE(x_business_scope,
510 fnd_api.g_miss_char, NULL,
511 x_business_scope),
512 DECODE(x_corporation_class,
513 fnd_api.g_miss_char, NULL,
514 x_corporation_class),
515 DECODE(x_known_as, fnd_api.g_miss_char, NULL, x_known_as),
516 DECODE(x_local_bus_iden_type,
517 fnd_api.g_miss_char, NULL,
518 x_local_bus_iden_type),
519 DECODE(x_local_bus_identifier,
520 fnd_api.g_miss_char, NULL,
521 x_local_bus_identifier),
522 DECODE(x_pref_functional_currency,
523 fnd_api.g_miss_char, NULL,
524 x_pref_functional_currency),
525 DECODE(x_registration_type,
526 fnd_api.g_miss_char, NULL,
527 x_registration_type),
528 DECODE(x_total_employees_text,
532 fnd_api.g_miss_char, NULL,
529 fnd_api.g_miss_char, NULL,
530 x_total_employees_text),
531 DECODE(x_total_employees_ind,
533 x_total_employees_ind),
534 DECODE(x_total_emp_est_ind,
535 fnd_api.g_miss_char, NULL,
536 x_total_emp_est_ind),
537 DECODE(x_total_emp_min_ind,
538 fnd_api.g_miss_char, NULL,
539 x_total_emp_min_ind),
540 DECODE(x_parent_sub_ind,
541 fnd_api.g_miss_char, NULL,
542 x_parent_sub_ind),
543 DECODE(x_incorp_year, fnd_api.g_miss_num, NULL, x_incorp_year),
544 hz_utility_v2pub.last_update_date,
545 hz_utility_v2pub.last_updated_by,
546 hz_utility_v2pub.creation_date,
547 hz_utility_v2pub.created_by,
548 hz_utility_v2pub.last_update_login,
549 hz_utility_v2pub.request_id,
550 hz_utility_v2pub.program_application_id,
551 hz_utility_v2pub.program_id,
552 hz_utility_v2pub.program_update_date,
553 DECODE(x_content_source_type,
554 fnd_api.g_miss_char, g_miss_content_source_type,
555 NULL, g_miss_content_source_type,
556 x_content_source_type),
557 DECODE(x_content_source_number,
558 fnd_api.g_miss_char, NULL,
559 x_content_source_number),
560 DECODE(x_effective_start_date,
561 fnd_api.g_miss_date, TRUNC(hz_utility_v2pub.creation_date),
562 NULL, TRUNC(hz_utility_v2pub.creation_date),
563 x_effective_start_date),
564 DECODE(x_effective_end_date,
565 fnd_api.g_miss_date, TO_DATE(NULL),
566 x_effective_end_date),
567 DECODE(x_sic_code_type, fnd_api.g_miss_char, NULL, x_sic_code_type),
568 DECODE(x_public_private_ownership,
569 fnd_api.g_miss_char, NULL,
570 x_public_private_ownership),
571 DECODE(x_local_activity_code_type,
572 fnd_api.g_miss_char, NULL,
573 x_local_activity_code_type),
574 DECODE(x_local_activity_code,
575 fnd_api.g_miss_char, NULL,
576 x_local_activity_code),
577 DECODE(x_emp_at_primary_adr,
578 fnd_api.g_miss_char, NULL,
579 x_emp_at_primary_adr),
580 DECODE(x_emp_at_primary_adr_text,
581 fnd_api.g_miss_char, NULL,
582 x_emp_at_primary_adr_text),
583 DECODE(x_emp_at_primary_adr_est_ind,
584 fnd_api.g_miss_char, NULL,
585 x_emp_at_primary_adr_est_ind),
586 DECODE(x_emp_at_primary_adr_min_ind,
587 fnd_api.g_miss_char, NULL,
588 x_emp_at_primary_adr_min_ind),
589 DECODE(x_internal_flag,
590 fnd_api.g_miss_char, 'N',
591 NULL, 'N',
592 x_internal_flag),
593 DECODE(x_high_credit,
594 fnd_api.g_miss_num, NULL,
595 x_high_credit),
596 DECODE(x_avg_high_credit,
597 fnd_api.g_miss_num, NULL,
598 x_avg_high_credit),
599 DECODE(x_total_payments,
600 fnd_api.g_miss_num, NULL, x_total_payments),
601 DECODE(x_known_as2, fnd_api.g_miss_char, NULL, x_known_as2),
602 DECODE(x_known_as3, fnd_api.g_miss_char, NULL, x_known_as3),
603 DECODE(x_known_as4, fnd_api.g_miss_char, NULL, x_known_as4),
604 DECODE(x_known_as5, fnd_api.g_miss_char, NULL, x_known_as5),
605 DECODE(x_credit_score_class,
606 fnd_api.g_miss_num, NULL,
607 x_credit_score_class),
608 DECODE(x_credit_score_natl_percentile,
609 fnd_api.g_miss_num, NULL,
610 x_credit_score_natl_percentile),
611 DECODE(x_credit_score_incd_default,
612 fnd_api.g_miss_num, NULL,
613 x_credit_score_incd_default),
614 DECODE(x_credit_score_age,
615 fnd_api.g_miss_num, NULL,
616 x_credit_score_age),
617 DECODE(x_credit_score_date,
618 fnd_api.g_miss_date, TO_DATE(NULL), x_credit_score_date),
619 DECODE(x_failure_score_class,
620 fnd_api.g_miss_num, NULL,
621 x_failure_score_class),
622 DECODE(x_failure_score_incd_default,
623 fnd_api.g_miss_num, NULL,
624 x_failure_score_incd_default),
625 DECODE(x_failure_score_age,
626 fnd_api.g_miss_num, NULL,
627 x_failure_score_age),
628 DECODE(x_failure_score_date,
629 fnd_api.g_miss_date, TO_DATE(NULL), x_failure_score_date),
630 DECODE(x_failure_score_commentary2,
631 fnd_api.g_miss_char, NULL,
632 x_failure_score_commentary2),
633 DECODE(x_failure_score_commentary3,
634 fnd_api.g_miss_char, NULL,
635 x_failure_score_commentary3),
636 DECODE(x_failure_score_commentary4,
637 fnd_api.g_miss_char, NULL,
638 x_failure_score_commentary4),
639 DECODE(x_failure_score_commentary5,
640 fnd_api.g_miss_char, NULL,
641 x_failure_score_commentary5),
642 DECODE(x_failure_score_commentary6,
643 fnd_api.g_miss_char, NULL,
644 x_failure_score_commentary6),
645 DECODE(x_failure_score_commentary7,
646 fnd_api.g_miss_char, NULL,
647 x_failure_score_commentary7),
648 DECODE(x_failure_score_commentary8,
652 fnd_api.g_miss_char, NULL,
649 fnd_api.g_miss_char, NULL,
650 x_failure_score_commentary8),
651 DECODE(x_failure_score_commentary9,
653 x_failure_score_commentary9),
654 DECODE(x_failure_score_commentary10,
655 fnd_api.g_miss_char, NULL,
656 x_failure_score_commentary10),
657 DECODE(x_credit_score_commentary2,
658 fnd_api.g_miss_char, NULL,
659 x_credit_score_commentary2),
660 DECODE(x_credit_score_commentary3,
661 fnd_api.g_miss_char, NULL,
662 x_credit_score_commentary3),
663 DECODE(x_credit_score_commentary4,
664 fnd_api.g_miss_char, NULL,
665 x_credit_score_commentary4),
666 DECODE(x_credit_score_commentary5,
667 fnd_api.g_miss_char, NULL,
668 x_credit_score_commentary5),
669 DECODE(x_credit_score_commentary6,
670 fnd_api.g_miss_char, NULL,
671 x_credit_score_commentary6),
672 DECODE(x_credit_score_commentary7,
673 fnd_api.g_miss_char, NULL,
674 x_credit_score_commentary7),
675 DECODE(x_credit_score_commentary8,
676 fnd_api.g_miss_char, NULL,
677 x_credit_score_commentary8),
678 DECODE(x_credit_score_commentary9,
679 fnd_api.g_miss_char, NULL,
680 x_credit_score_commentary9),
681 DECODE(x_credit_score_commentary10,
682 fnd_api.g_miss_char, NULL,
683 x_credit_score_commentary10),
684 DECODE(x_maximum_credit_recomm,
685 fnd_api.g_miss_num, NULL,
686 x_maximum_credit_recomm),
687 DECODE(x_maximum_credit_currency_code,
688 fnd_api.g_miss_char, NULL,
689 x_maximum_credit_currency_code),
690 DECODE(x_displayed_duns_party_id,
691 fnd_api.g_miss_num, NULL,
692 x_displayed_duns_party_id),
693 DECODE(x_failure_score_natnl_perc,
694 fnd_api.g_miss_num, NULL,
695 x_failure_score_natnl_perc),
696 DECODE(x_duns_number_c, fnd_api.g_miss_char, NULL,/*Bug 3435702*/ UPPER(l_duns_number_c)),
697 /* Bug 3435702.This is not necessary because if x_duns_number_c is null or
698 fnd_api.g_miss_char , then l_duns_number will be NULL.
699 DECODE(x_duns_number_c, fnd_api.g_miss_char,
700 NULL, NULL, NULL, l_duns_number),*/
701 l_duns_number,
702 DECODE(x_bank_or_branch_number,
703 fnd_api.g_miss_char, NULL,
704 x_bank_or_branch_number),
705 DECODE(x_bank_code, fnd_api.g_miss_char, NULL, x_bank_code),
706 DECODE(x_branch_code, fnd_api.g_miss_char, NULL, x_branch_code),
707 DECODE(x_object_version_number,
708 fnd_api.g_miss_num, NULL,
709 x_object_version_number),
710 DECODE(x_created_by_module,
711 fnd_api.g_miss_char, NULL,
712 x_created_by_module),
713 DECODE(x_application_id, fnd_api.g_miss_num, NULL, x_application_id ),
714 DECODE(x_do_not_confuse_with, fnd_api.g_miss_char, NULL, x_do_not_confuse_with),
715 DECODE(x_actual_content_source,
716 fnd_api.g_miss_char, g_sst_source_type,
717 NULL, g_sst_source_type,
718 x_actual_content_source),
719 DECODE(x_version_number,fnd_api.g_miss_num, NULL,x_version_number),
720 DECODE(x_home_country, fnd_api.g_miss_char, NULL, x_home_country)
721 ) RETURNING
722 ROWID, organization_profile_id
723 INTO
724 x_rowid, x_organization_profile_id;
725
726 l_success := 'Y';
727
728 EXCEPTION
729 WHEN DUP_VAL_ON_INDEX THEN
730 IF INSTRB(SQLERRM, 'HZ_ORGANIZATION_PROFILES_U1') <> 0 OR
731 INSTRB(SQLERRM, 'HZ_ORGANIZATION_PROFILES_PK') <> 0
732 THEN
733 DECLARE
734 l_count NUMBER;
735 l_dummy VARCHAR2(1);
736 -- Bug 2117973: changed select...intos into cursors.
737 CURSOR c_seq IS
738 SELECT hz_organization_profiles_s.NEXTVAL
739 FROM dual;
740 CURSOR c_dupchk IS
741 SELECT 'Y'
742 FROM hz_organization_profiles hop
743 WHERE hop.organization_profile_id = x_organization_profile_id;
744 BEGIN
745 l_count := 1;
746 WHILE l_count > 0 LOOP
747 -- get the next profile ID.
748 OPEN c_seq;
749 FETCH c_seq INTO x_organization_profile_id;
750 IF c_seq%NOTFOUND THEN
751 CLOSE c_seq;
752 RAISE NO_DATA_FOUND;
753 END IF;
754 CLOSE c_seq;
755
756 -- check for dups
757 OPEN c_dupchk;
758 FETCH c_dupchk INTO l_dummy;
759 IF c_dupchk%FOUND THEN
760 -- continue the loop if there is a duplicate record.
761 l_count := 1;
762 ELSE
763 -- terminate the loop if there is no duplicate record.
764 l_count := 0;
765 END IF;
766 CLOSE c_dupchk;
767 END LOOP;
768 END;
769 ELSE
770 RAISE;
771 END IF;
772 END;
773 END LOOP;
774 END insert_row;
775
776 PROCEDURE update_row (
780 x_organization_name IN VARCHAR2,
777 x_rowid IN OUT NOCOPY VARCHAR2,
778 x_organization_profile_id IN NUMBER,
779 x_party_id IN NUMBER,
781 x_attribute_category IN VARCHAR2,
782 x_attribute1 IN VARCHAR2,
783 x_attribute2 IN VARCHAR2,
784 x_attribute3 IN VARCHAR2,
785 x_attribute4 IN VARCHAR2,
786 x_attribute5 IN VARCHAR2,
787 x_attribute6 IN VARCHAR2,
788 x_attribute7 IN VARCHAR2,
789 x_attribute8 IN VARCHAR2,
790 x_attribute9 IN VARCHAR2,
791 x_attribute10 IN VARCHAR2,
792 x_attribute11 IN VARCHAR2,
793 x_attribute12 IN VARCHAR2,
794 x_attribute13 IN VARCHAR2,
795 x_attribute14 IN VARCHAR2,
796 x_attribute15 IN VARCHAR2,
797 x_attribute16 IN VARCHAR2,
798 x_attribute17 IN VARCHAR2,
799 x_attribute18 IN VARCHAR2,
800 x_attribute19 IN VARCHAR2,
801 x_attribute20 IN VARCHAR2,
802 x_enquiry_duns IN VARCHAR2,
803 x_ceo_name IN VARCHAR2,
804 x_ceo_title IN VARCHAR2,
805 x_principal_name IN VARCHAR2,
806 x_principal_title IN VARCHAR2,
807 x_legal_status IN VARCHAR2,
808 x_control_yr IN NUMBER,
809 x_employees_total IN NUMBER,
810 x_hq_branch_ind IN VARCHAR2,
811 x_branch_flag IN VARCHAR2,
812 x_oob_ind IN VARCHAR2,
813 x_line_of_business IN VARCHAR2,
814 x_cong_dist_code IN VARCHAR2,
815 x_sic_code IN VARCHAR2,
816 x_import_ind IN VARCHAR2,
817 x_export_ind IN VARCHAR2,
818 x_labor_surplus_ind IN VARCHAR2,
819 x_debarment_ind IN VARCHAR2,
820 x_minority_owned_ind IN VARCHAR2,
821 x_minority_owned_type IN VARCHAR2,
822 x_woman_owned_ind IN VARCHAR2,
823 x_disadv_8a_ind IN VARCHAR2,
824 x_small_bus_ind IN VARCHAR2,
825 x_rent_own_ind IN VARCHAR2,
826 x_debarments_count IN NUMBER,
827 x_debarments_date IN DATE,
828 x_failure_score IN VARCHAR2,
829 x_failure_score_override_code IN VARCHAR2,
830 x_failure_score_commentary IN VARCHAR2,
831 x_global_failure_score IN VARCHAR2,
832 x_db_rating IN VARCHAR2,
833 x_credit_score IN VARCHAR2,
834 x_credit_score_commentary IN VARCHAR2,
835 x_paydex_score IN VARCHAR2,
836 x_paydex_three_months_ago IN VARCHAR2,
837 x_paydex_norm IN VARCHAR2,
838 x_best_time_contact_begin IN DATE,
839 x_best_time_contact_end IN DATE,
840 x_organization_name_phonetic IN VARCHAR2,
841 x_tax_reference IN VARCHAR2,
842 x_gsa_indicator_flag IN VARCHAR2,
843 x_jgzz_fiscal_code IN VARCHAR2,
844 x_analysis_fy IN VARCHAR2,
845 x_fiscal_yearend_month IN VARCHAR2,
846 x_curr_fy_potential_revenue IN NUMBER,
847 x_next_fy_potential_revenue IN NUMBER,
848 x_year_established IN NUMBER,
849 x_mission_statement IN VARCHAR2,
850 x_organization_type IN VARCHAR2,
851 x_business_scope IN VARCHAR2,
852 x_corporation_class IN VARCHAR2,
853 x_known_as IN VARCHAR2,
854 x_local_bus_iden_type IN VARCHAR2,
855 x_local_bus_identifier IN VARCHAR2,
856 x_pref_functional_currency IN VARCHAR2,
857 x_registration_type IN VARCHAR2,
858 x_total_employees_text IN VARCHAR2,
859 x_total_employees_ind IN VARCHAR2,
860 x_total_emp_est_ind IN VARCHAR2,
861 x_total_emp_min_ind IN VARCHAR2,
862 x_parent_sub_ind IN VARCHAR2,
863 x_incorp_year IN NUMBER,
864 x_content_source_type IN VARCHAR2,
865 x_content_source_number IN VARCHAR2,
866 x_effective_start_date IN DATE,
867 x_effective_end_date IN DATE,
868 x_sic_code_type IN VARCHAR2,
869 x_public_private_ownership IN VARCHAR2,
870 x_local_activity_code_type IN VARCHAR2,
871 x_local_activity_code IN VARCHAR2,
872 x_emp_at_primary_adr IN VARCHAR2,
873 x_emp_at_primary_adr_text IN VARCHAR2,
874 x_emp_at_primary_adr_est_ind IN VARCHAR2,
875 x_emp_at_primary_adr_min_ind IN VARCHAR2,
876 x_internal_flag IN VARCHAR2,
877 x_high_credit IN NUMBER,
881 x_known_as3 IN VARCHAR2,
878 x_avg_high_credit IN NUMBER,
879 x_total_payments IN NUMBER,
880 x_known_as2 IN VARCHAR2,
882 x_known_as4 IN VARCHAR2,
883 x_known_as5 IN VARCHAR2,
884 x_credit_score_class IN NUMBER,
885 x_credit_score_natl_percentile IN NUMBER,
886 x_credit_score_incd_default IN NUMBER,
887 x_credit_score_age IN NUMBER,
888 x_credit_score_date IN DATE,
889 x_failure_score_class IN NUMBER,
890 x_failure_score_incd_default IN NUMBER,
891 x_failure_score_age IN NUMBER,
892 x_failure_score_date IN DATE,
893 x_failure_score_commentary2 IN VARCHAR2,
894 x_failure_score_commentary3 IN VARCHAR2,
895 x_failure_score_commentary4 IN VARCHAR2,
896 x_failure_score_commentary5 IN VARCHAR2,
897 x_failure_score_commentary6 IN VARCHAR2,
898 x_failure_score_commentary7 IN VARCHAR2,
899 x_failure_score_commentary8 IN VARCHAR2,
900 x_failure_score_commentary9 IN VARCHAR2,
901 x_failure_score_commentary10 IN VARCHAR2,
902 x_credit_score_commentary2 IN VARCHAR2,
903 x_credit_score_commentary3 IN VARCHAR2,
904 x_credit_score_commentary4 IN VARCHAR2,
905 x_credit_score_commentary5 IN VARCHAR2,
906 x_credit_score_commentary6 IN VARCHAR2,
907 x_credit_score_commentary7 IN VARCHAR2,
908 x_credit_score_commentary8 IN VARCHAR2,
909 x_credit_score_commentary9 IN VARCHAR2,
910 x_credit_score_commentary10 IN VARCHAR2,
911 x_maximum_credit_recomm IN NUMBER,
912 x_maximum_credit_currency_code IN VARCHAR2,
913 x_displayed_duns_party_id IN NUMBER,
914 x_failure_score_natnl_perc IN NUMBER,
915 x_duns_number_c IN VARCHAR2,
916 x_bank_or_branch_number IN VARCHAR2 := NULL,
917 x_bank_code IN VARCHAR2 := NULL,
918 x_branch_code IN VARCHAR2 := NULL,
919 x_object_version_number IN NUMBER,
920 x_created_by_module IN VARCHAR2,
921 x_application_id IN NUMBER,
922 x_do_not_confuse_with IN VARCHAR2 := NULL,
923 x_actual_content_source IN VARCHAR2 DEFAULT NULL,
924 x_version_number IN NUMBER DEFAULT NULL,
925 x_home_country IN VARCHAR2 DEFAULT NULL
926 ) IS
927
928 l_duns_number NUMBER;
929 l_duns_number_c HZ_PARTIES.duns_number_c%type := X_DUNS_NUMBER_C;
930 BEGIN
931
932 IF x_duns_number_c IS NOT NULL AND
933 x_duns_number_c <> FND_API.G_MISS_CHAR
934 THEN
935 l_duns_number := do_copy_duns_number(x_duns_number_c);
936 END IF;
937
938 IF x_duns_number_c IS NOT NULL AND
939 x_duns_number_c <> FND_API.G_MISS_CHAR AND
940 LENGTHB(x_duns_number_c)<9
941 THEN
942 l_duns_number_c:=lpad(x_duns_number_c,9,'0');
943 END IF;
944
945
946 UPDATE hz_organization_profiles hop
947 SET hop.organization_profile_id =
948 DECODE(x_organization_profile_id,
949 NULL, hop.organization_profile_id,
950 fnd_api.g_miss_num, NULL, x_organization_profile_id),
951 hop.party_id =
952 DECODE(x_party_id,
953 NULL, hop.party_id,
954 fnd_api.g_miss_num, NULL,
955 x_party_id),
956 hop.organization_name =
957 DECODE(x_organization_name,
958 NULL, hop.organization_name,
959 fnd_api.g_miss_char, NULL,
960 x_organization_name),
961 hop.attribute_category =
962 DECODE(x_attribute_category,
963 NULL, hop.attribute_category,
964 fnd_api.g_miss_char, NULL,
965 x_attribute_category),
966 hop.attribute1 =
967 DECODE(x_attribute1,
968 NULL, hop.attribute1,
969 fnd_api.g_miss_char, NULL,
970 x_attribute1),
971 hop.attribute2 =
972 DECODE(x_attribute2,
973 NULL, hop.attribute2,
974 fnd_api.g_miss_char, NULL,
975 x_attribute2),
976 hop.attribute3 =
977 DECODE(x_attribute3,
978 NULL, hop.attribute3,
979 fnd_api.g_miss_char, NULL,
980 x_attribute3),
981 hop.attribute4 =
982 DECODE(x_attribute4,
983 NULL, hop.attribute4,
984 fnd_api.g_miss_char, NULL,
985 x_attribute4),
986 hop.attribute5 =
987 DECODE(x_attribute5,
988 NULL, hop.attribute5,
989 fnd_api.g_miss_char, NULL,
990 x_attribute5),
991 hop.attribute6 =
992 DECODE(x_attribute6,
993 NULL, hop.attribute6,
994 fnd_api.g_miss_char, NULL,
995 x_attribute6),
996 hop.attribute7 =
997 DECODE(x_attribute7,
998 NULL, hop.attribute7,
999 fnd_api.g_miss_char, NULL,
1003 NULL, hop.attribute8,
1000 x_attribute7),
1001 hop.attribute8 =
1002 DECODE(x_attribute8,
1004 fnd_api.g_miss_char, NULL,
1005 x_attribute8),
1006 hop.attribute9 =
1007 DECODE(x_attribute9,
1008 NULL, hop.attribute9,
1009 fnd_api.g_miss_char, NULL,
1010 x_attribute9),
1011 hop.attribute10 =
1012 DECODE(x_attribute10,
1013 NULL, hop.attribute10,
1014 fnd_api.g_miss_char, NULL,
1015 x_attribute10),
1016 hop.attribute11 =
1017 DECODE(x_attribute11,
1018 NULL, hop.attribute11,
1019 fnd_api.g_miss_char, NULL,
1020 x_attribute11),
1021 hop.attribute12 =
1022 DECODE(x_attribute12,
1023 NULL, hop.attribute12,
1024 fnd_api.g_miss_char, NULL,
1025 x_attribute12),
1026 hop.attribute13 =
1027 DECODE(x_attribute13,
1028 NULL, hop.attribute13,
1029 fnd_api.g_miss_char, NULL,
1030 x_attribute13),
1031 hop.attribute14 =
1032 DECODE(x_attribute14,
1033 NULL, hop.attribute14,
1034 fnd_api.g_miss_char, NULL,
1035 x_attribute14),
1036 hop.attribute15 =
1037 DECODE(x_attribute15,
1038 NULL, hop.attribute15,
1039 fnd_api.g_miss_char, NULL,
1040 x_attribute15),
1041 hop.attribute16 =
1042 DECODE(x_attribute16,
1043 NULL, hop.attribute16,
1044 fnd_api.g_miss_char, NULL,
1045 x_attribute16),
1046 hop.attribute17 =
1047 DECODE(x_attribute17,
1048 NULL, hop.attribute17,
1049 fnd_api.g_miss_char, NULL,
1050 x_attribute17),
1051 hop.attribute18 =
1052 DECODE(x_attribute18,
1053 NULL, hop.attribute18,
1054 fnd_api.g_miss_char, NULL,
1055 x_attribute18),
1056 hop.attribute19 =
1057 DECODE(x_attribute19,
1058 NULL, hop.attribute19,
1059 fnd_api.g_miss_char, NULL,
1060 x_attribute19),
1061 hop.attribute20 =
1062 DECODE(x_attribute20,
1063 NULL, hop.attribute20,
1064 fnd_api.g_miss_char, NULL,
1065 x_attribute20),
1066 hop.enquiry_duns =
1067 DECODE(x_enquiry_duns,
1068 NULL, hop.enquiry_duns,
1069 fnd_api.g_miss_char, NULL,
1070 x_enquiry_duns),
1071 hop.ceo_name =
1072 DECODE(x_ceo_name,
1073 NULL, hop.ceo_name,
1074 fnd_api.g_miss_char, NULL,
1075 x_ceo_name),
1076 hop.ceo_title =
1077 DECODE(x_ceo_title,
1078 NULL, hop.ceo_title,
1079 fnd_api.g_miss_char, NULL,
1080 x_ceo_title),
1081 hop.principal_name =
1082 DECODE(x_principal_name,
1083 NULL, hop.principal_name,
1084 fnd_api.g_miss_char, NULL,
1085 x_principal_name),
1086 hop.principal_title =
1087 DECODE(x_principal_title,
1088 NULL, hop.principal_title,
1089 fnd_api.g_miss_char, NULL,
1090 x_principal_title),
1091 hop.legal_status =
1092 DECODE(x_legal_status,
1093 NULL, hop.legal_status,
1094 fnd_api.g_miss_char, NULL,
1095 x_legal_status),
1096 hop.control_yr =
1097 DECODE(x_control_yr,
1098 NULL, hop.control_yr,
1099 fnd_api.g_miss_num, NULL,
1100 x_control_yr),
1101 hop.employees_total =
1102 DECODE(x_employees_total,
1103 NULL, hop.employees_total,
1104 fnd_api.g_miss_num, NULL,
1105 x_employees_total),
1106 hop.hq_branch_ind =
1107 DECODE(x_hq_branch_ind,
1108 NULL, hop.hq_branch_ind,
1109 fnd_api.g_miss_char, NULL,
1110 x_hq_branch_ind),
1111 hop.branch_flag =
1112 DECODE(x_branch_flag,
1113 NULL, hop.branch_flag,
1114 fnd_api.g_miss_char, NULL,
1115 x_branch_flag),
1116 hop.oob_ind =
1117 DECODE(x_oob_ind,
1118 NULL, hop.oob_ind,
1119 fnd_api.g_miss_char, NULL,
1120 x_oob_ind),
1121 hop.line_of_business =
1122 DECODE(x_line_of_business,
1123 NULL, hop.line_of_business,
1124 fnd_api.g_miss_char, NULL,
1125 x_line_of_business),
1126 hop.cong_dist_code =
1127 DECODE(x_cong_dist_code,
1128 NULL, hop.cong_dist_code,
1129 fnd_api.g_miss_char, NULL,
1130 x_cong_dist_code),
1131 hop.sic_code =
1132 DECODE(x_sic_code,
1133 NULL, hop.sic_code,
1134 fnd_api.g_miss_char, NULL,
1135 x_sic_code),
1136 hop.import_ind =
1140 x_import_ind),
1137 DECODE(x_import_ind,
1138 NULL, hop.import_ind,
1139 fnd_api.g_miss_char, NULL,
1141 hop.export_ind =
1142 DECODE(x_export_ind,
1143 NULL, hop.export_ind,
1144 fnd_api.g_miss_char, NULL,
1145 x_export_ind),
1146 hop.labor_surplus_ind =
1147 DECODE(x_labor_surplus_ind,
1148 NULL, hop.labor_surplus_ind,
1149 fnd_api.g_miss_char, NULL,
1150 x_labor_surplus_ind),
1151 hop.debarment_ind =
1152 DECODE(x_debarment_ind,
1153 NULL, hop.debarment_ind,
1154 fnd_api.g_miss_char, NULL,
1155 x_debarment_ind),
1156 hop.minority_owned_ind =
1157 DECODE(x_minority_owned_ind,
1158 NULL, hop.minority_owned_ind,
1159 fnd_api.g_miss_char, NULL,
1160 x_minority_owned_ind),
1161 hop.minority_owned_type =
1162 DECODE(x_minority_owned_type,
1163 NULL, hop.minority_owned_type,
1164 fnd_api.g_miss_char, NULL,
1165 x_minority_owned_type),
1166 hop.woman_owned_ind =
1167 DECODE(x_woman_owned_ind,
1168 NULL, hop.woman_owned_ind,
1169 fnd_api.g_miss_char, NULL,
1170 x_woman_owned_ind),
1171 hop.disadv_8a_ind =
1172 DECODE(x_disadv_8a_ind,
1173 NULL, hop.disadv_8a_ind,
1174 fnd_api.g_miss_char, NULL,
1175 x_disadv_8a_ind),
1176 hop.small_bus_ind =
1177 DECODE(x_small_bus_ind,
1178 NULL, hop.small_bus_ind,
1179 fnd_api.g_miss_char, NULL,
1180 x_small_bus_ind),
1181 hop.rent_own_ind =
1182 DECODE(x_rent_own_ind,
1183 NULL, hop.rent_own_ind,
1184 fnd_api.g_miss_char, NULL,
1185 x_rent_own_ind),
1186 hop.debarments_count =
1187 DECODE(x_debarments_count,
1188 NULL, hop.debarments_count,
1189 fnd_api.g_miss_num, NULL,
1190 x_debarments_count),
1191 hop.debarments_date =
1192 DECODE(x_debarments_date,
1193 NULL, hop.debarments_date,
1194 fnd_api.g_miss_date, NULL,
1195 x_debarments_date),
1196 hop.failure_score =
1197 DECODE(x_failure_score,
1198 NULL, hop.failure_score,
1199 fnd_api.g_miss_char, NULL,
1200 x_failure_score),
1201 hop.failure_score_override_code =
1202 DECODE(x_failure_score_override_code,
1203 NULL, hop.failure_score_override_code,
1204 fnd_api.g_miss_char, NULL,
1205 x_failure_score_override_code),
1206 hop.failure_score_commentary =
1207 DECODE(x_failure_score_commentary,
1208 NULL, hop.failure_score_commentary,
1209 fnd_api.g_miss_char, NULL,
1210 x_failure_score_commentary),
1211 hop.global_failure_score =
1212 DECODE(x_global_failure_score,
1213 NULL, hop.global_failure_score,
1214 fnd_api.g_miss_char, NULL,
1215 x_global_failure_score),
1216 hop.db_rating =
1217 DECODE(x_db_rating,
1218 NULL, hop.db_rating,
1219 fnd_api.g_miss_char, NULL,
1220 x_db_rating),
1221 hop.credit_score =
1222 DECODE(x_credit_score,
1223 NULL, hop.credit_score,
1224 fnd_api.g_miss_char, NULL,
1225 x_credit_score),
1226 hop.credit_score_commentary =
1227 DECODE(x_credit_score_commentary,
1228 NULL, hop.credit_score_commentary,
1229 fnd_api.g_miss_char, NULL,
1230 x_credit_score_commentary),
1231 hop.paydex_score =
1232 DECODE(x_paydex_score,
1233 NULL, hop.paydex_score,
1234 fnd_api.g_miss_char, NULL,
1235 x_paydex_score),
1236 hop.paydex_three_months_ago =
1237 DECODE(x_paydex_three_months_ago,
1238 NULL, hop.paydex_three_months_ago,
1239 fnd_api.g_miss_char, NULL,
1240 x_paydex_three_months_ago),
1241 hop.paydex_norm =
1242 DECODE(x_paydex_norm,
1243 NULL, hop.paydex_norm,
1244 fnd_api.g_miss_char, NULL,
1245 x_paydex_norm),
1246 hop.best_time_contact_begin =
1247 DECODE(x_best_time_contact_begin,
1248 NULL, hop.best_time_contact_begin,
1249 fnd_api.g_miss_date, NULL,
1250 x_best_time_contact_begin),
1251 hop.best_time_contact_end =
1252 DECODE(x_best_time_contact_end,
1253 NULL, hop.best_time_contact_end,
1254 fnd_api.g_miss_date, NULL,
1255 x_best_time_contact_end),
1256 hop.organization_name_phonetic =
1257 DECODE(x_organization_name_phonetic,
1258 NULL, hop.organization_name_phonetic,
1259 fnd_api.g_miss_char, NULL,
1260 x_organization_name_phonetic),
1261 hop.tax_reference =
1262 DECODE(x_tax_reference,
1266 hop.gsa_indicator_flag =
1263 NULL, hop.tax_reference,
1264 fnd_api.g_miss_char, NULL,
1265 x_tax_reference),
1267 DECODE(x_gsa_indicator_flag,
1268 NULL, hop.gsa_indicator_flag,
1269 fnd_api.g_miss_char, NULL,
1270 x_gsa_indicator_flag),
1271 hop.jgzz_fiscal_code =
1272 DECODE(x_jgzz_fiscal_code,
1273 NULL, hop.jgzz_fiscal_code,
1274 fnd_api.g_miss_char, NULL,
1275 x_jgzz_fiscal_code),
1276 hop.analysis_fy =
1277 DECODE(x_analysis_fy,
1278 NULL, hop.analysis_fy,
1279 fnd_api.g_miss_char, NULL,
1280 x_analysis_fy),
1281 hop.fiscal_yearend_month =
1282 DECODE(x_fiscal_yearend_month,
1283 NULL, hop.fiscal_yearend_month,
1284 fnd_api.g_miss_char, NULL,
1285 x_fiscal_yearend_month),
1286 hop.curr_fy_potential_revenue =
1287 DECODE(x_curr_fy_potential_revenue,
1288 NULL, hop.curr_fy_potential_revenue,
1289 fnd_api.g_miss_num, NULL,
1290 x_curr_fy_potential_revenue),
1291 hop.next_fy_potential_revenue =
1292 DECODE(x_next_fy_potential_revenue,
1293 NULL, hop.next_fy_potential_revenue,
1294 fnd_api.g_miss_num, NULL,
1295 x_next_fy_potential_revenue),
1296 hop.year_established =
1297 DECODE(x_year_established,
1298 NULL, hop.year_established,
1299 fnd_api.g_miss_num, NULL,
1300 x_year_established),
1301 hop.mission_statement =
1302 DECODE(x_mission_statement,
1303 NULL, hop.mission_statement,
1304 fnd_api.g_miss_char, NULL,
1305 x_mission_statement),
1306 hop.organization_type =
1307 DECODE(x_organization_type,
1308 NULL, hop.organization_type,
1309 fnd_api.g_miss_char, NULL,
1310 x_organization_type),
1311 hop.business_scope =
1312 DECODE(x_business_scope,
1313 NULL, hop.business_scope,
1314 fnd_api.g_miss_char, NULL,
1315 x_business_scope),
1316 hop.corporation_class =
1317 DECODE(x_corporation_class,
1318 NULL, hop.corporation_class,
1319 fnd_api.g_miss_char, NULL,
1320 x_corporation_class),
1321 hop.known_as =
1322 DECODE(x_known_as,
1323 NULL, hop.known_as,
1324 fnd_api.g_miss_char, NULL,
1325 x_known_as),
1326 hop.local_bus_iden_type =
1327 DECODE(x_local_bus_iden_type,
1328 NULL, hop.local_bus_iden_type,
1329 fnd_api.g_miss_char, NULL,
1330 x_local_bus_iden_type),
1331 hop.local_bus_identifier =
1332 DECODE(x_local_bus_identifier,
1333 NULL, hop.local_bus_identifier,
1334 fnd_api.g_miss_char, NULL,
1335 x_local_bus_identifier),
1336 hop.pref_functional_currency =
1337 DECODE(x_pref_functional_currency,
1338 NULL, hop.pref_functional_currency,
1339 fnd_api.g_miss_char, NULL,
1340 x_pref_functional_currency),
1341 hop.registration_type =
1342 DECODE(x_registration_type,
1343 NULL, hop.registration_type,
1344 fnd_api.g_miss_char, NULL,
1345 x_registration_type),
1346 hop.total_employees_text =
1347 DECODE(x_total_employees_text,
1348 NULL, hop.total_employees_text,
1349 fnd_api.g_miss_char, NULL,
1350 x_total_employees_text),
1351 hop.total_employees_ind =
1352 DECODE(x_total_employees_ind,
1353 NULL, hop.total_employees_ind,
1354 fnd_api.g_miss_char, NULL,
1355 x_total_employees_ind),
1356 hop.total_emp_est_ind =
1357 DECODE(x_total_emp_est_ind,
1358 NULL, hop.total_emp_est_ind,
1359 fnd_api.g_miss_char, NULL,
1360 x_total_emp_est_ind),
1361 hop.total_emp_min_ind =
1362 DECODE(x_total_emp_min_ind,
1363 NULL, hop.total_emp_min_ind,
1364 fnd_api.g_miss_char, NULL,
1365 x_total_emp_min_ind),
1366 hop.parent_sub_ind =
1367 DECODE(x_parent_sub_ind,
1368 NULL, hop.parent_sub_ind,
1369 fnd_api.g_miss_char, NULL,
1370 x_parent_sub_ind),
1371 hop.incorp_year =
1372 DECODE(x_incorp_year,
1373 NULL, hop.incorp_year,
1374 fnd_api.g_miss_num, NULL,
1375 x_incorp_year),
1376 hop.last_update_date = hz_utility_v2pub.last_update_date,
1377 hop.last_updated_by = hz_utility_v2pub.last_updated_by,
1378 hop.creation_date = creation_date,
1379 hop.created_by = created_by,
1380 hop.last_update_login = hz_utility_v2pub.last_update_login,
1381 hop.request_id = hz_utility_v2pub.request_id,
1382 hop.program_application_id =
1383 hz_utility_v2pub.program_application_id,
1384 hop.program_id = hz_utility_v2pub.program_id,
1385 hop.program_update_date = hz_utility_v2pub.program_update_date,
1389 fnd_api.g_miss_char, NULL,
1386 hop.content_source_type =
1387 DECODE(x_content_source_type,
1388 NULL, hop.content_source_type,
1390 x_content_source_type),
1391 hop.content_source_number =
1392 DECODE(x_content_source_number,
1393 NULL, hop.content_source_number,
1394 fnd_api.g_miss_char, NULL,
1395 x_content_source_number),
1396 hop.effective_start_date =
1397 DECODE(x_effective_start_date,
1398 NULL, hop.effective_start_date,
1399 fnd_api.g_miss_date, NULL,
1400 x_effective_start_date),
1401 hop.effective_end_date =
1402 DECODE(x_effective_end_date,
1403 NULL, hop.effective_end_date,
1404 fnd_api.g_miss_date, NULL,
1405 x_effective_end_date),
1406 hop.sic_code_type =
1407 DECODE(x_sic_code_type,
1408 NULL, hop.sic_code_type,
1409 fnd_api.g_miss_char, NULL,
1410 x_sic_code_type),
1411 hop.public_private_ownership_flag =
1412 DECODE(x_public_private_ownership,
1413 NULL, hop.public_private_ownership_flag,
1414 fnd_api.g_miss_char, NULL,
1415 x_public_private_ownership),
1416 hop.local_activity_code_type =
1417 DECODE(x_local_activity_code_type,
1418 NULL, hop.local_activity_code_type,
1419 fnd_api.g_miss_char, NULL,
1420 x_local_activity_code_type),
1421 hop.local_activity_code =
1422 DECODE(x_local_activity_code,
1423 NULL, hop.local_activity_code,
1424 fnd_api.g_miss_char, NULL,
1425 x_local_activity_code),
1426 hop.emp_at_primary_adr =
1427 DECODE(x_emp_at_primary_adr,
1428 NULL, hop.emp_at_primary_adr,
1429 fnd_api.g_miss_char, NULL,
1430 x_emp_at_primary_adr),
1431 hop.emp_at_primary_adr_text =
1432 DECODE(x_emp_at_primary_adr_text,
1433 NULL, hop.emp_at_primary_adr_text,
1434 fnd_api.g_miss_char, NULL,
1435 x_emp_at_primary_adr_text),
1436 hop.emp_at_primary_adr_est_ind =
1437 DECODE(x_emp_at_primary_adr_est_ind,
1438 NULL, hop.emp_at_primary_adr_est_ind,
1439 fnd_api.g_miss_char, NULL,
1440 x_emp_at_primary_adr_est_ind),
1441 hop.emp_at_primary_adr_min_ind =
1442 DECODE(x_emp_at_primary_adr_min_ind,
1443 NULL, hop.emp_at_primary_adr_min_ind,
1444 fnd_api.g_miss_char, NULL,
1445 x_emp_at_primary_adr_min_ind),
1446 hop.internal_flag =
1447 DECODE(x_internal_flag,
1448 NULL, hop.internal_flag,
1449 fnd_api.g_miss_char, 'N',
1450 x_internal_flag),
1451 hop.high_credit =
1452 DECODE(x_high_credit,
1453 NULL, hop.high_credit,
1454 fnd_api.g_miss_num, NULL,
1455 x_high_credit),
1456 hop.avg_high_credit =
1457 DECODE(x_avg_high_credit,
1458 NULL, hop.avg_high_credit,
1459 fnd_api.g_miss_num, NULL,
1460 x_avg_high_credit),
1461 hop.total_payments =
1462 DECODE(x_total_payments,
1463 NULL, hop.total_payments,
1464 fnd_api.g_miss_num, NULL,
1465 x_total_payments),
1466 hop.known_as2 =
1467 DECODE(x_known_as2,
1468 NULL, hop.known_as2,
1469 fnd_api.g_miss_char, NULL,
1470 x_known_as2),
1471 hop.known_as3 =
1472 DECODE(x_known_as3,
1473 NULL, hop.known_as3,
1474 fnd_api.g_miss_char, NULL,
1475 x_known_as3),
1476 hop.known_as4 =
1477 DECODE(x_known_as4,
1478 NULL, hop.known_as4,
1479 fnd_api.g_miss_char, NULL,
1480 x_known_as4),
1481 hop.known_as5 =
1482 DECODE(x_known_as5,
1483 NULL, hop.known_as5,
1484 fnd_api.g_miss_char, NULL,
1485 x_known_as5),
1486 hop.credit_score_class =
1487 DECODE(x_credit_score_class,
1488 NULL, hop.credit_score_class,
1489 fnd_api.g_miss_num, NULL,
1490 x_credit_score_class),
1491 hop.credit_score_natl_percentile =
1492 DECODE(x_credit_score_natl_percentile,
1493 NULL, hop.credit_score_natl_percentile,
1494 fnd_api.g_miss_num, NULL,
1495 x_credit_score_natl_percentile),
1496 hop.credit_score_incd_default =
1497 DECODE(x_credit_score_incd_default,
1498 NULL, hop.credit_score_incd_default,
1499 fnd_api.g_miss_num, NULL,
1500 x_credit_score_incd_default),
1501 hop.credit_score_age =
1502 DECODE(x_credit_score_age,
1503 NULL, hop.credit_score_age,
1504 fnd_api.g_miss_num, NULL,
1505 x_credit_score_age),
1506 hop.credit_score_date =
1507 DECODE(x_credit_score_date,
1508 NULL, hop.credit_score_date,
1509 fnd_api.g_miss_date, NULL,
1513 NULL, hop.failure_score_class,
1510 x_credit_score_date),
1511 hop.failure_score_class =
1512 DECODE(x_failure_score_class,
1514 fnd_api.g_miss_num, NULL,
1515 x_failure_score_class),
1516 hop.failure_score_incd_default =
1517 DECODE(x_failure_score_incd_default,
1518 NULL, hop.failure_score_incd_default,
1519 fnd_api.g_miss_num, NULL,
1520 x_failure_score_incd_default),
1521 hop.failure_score_age =
1522 DECODE(x_failure_score_age,
1523 NULL, hop.failure_score_age,
1524 fnd_api.g_miss_num, NULL,
1525 x_failure_score_age),
1526 hop.failure_score_date =
1527 DECODE(x_failure_score_date,
1528 NULL, hop.failure_score_date,
1529 fnd_api.g_miss_date, NULL,
1530 x_failure_score_date),
1531 hop.failure_score_commentary2 =
1532 DECODE(x_failure_score_commentary2,
1533 NULL, hop.failure_score_commentary2,
1534 fnd_api.g_miss_char, NULL,
1535 x_failure_score_commentary2),
1536 hop.failure_score_commentary3 =
1537 DECODE(x_failure_score_commentary3,
1538 NULL, hop.failure_score_commentary3,
1539 fnd_api.g_miss_char, NULL,
1540 x_failure_score_commentary3),
1541 hop.failure_score_commentary4 =
1542 DECODE(x_failure_score_commentary4,
1543 NULL, hop.failure_score_commentary4,
1544 fnd_api.g_miss_char, NULL,
1545 x_failure_score_commentary4),
1546 hop.failure_score_commentary5 =
1547 DECODE(x_failure_score_commentary5,
1548 NULL, hop.failure_score_commentary5,
1549 fnd_api.g_miss_char, NULL,
1550 x_failure_score_commentary5),
1551 hop.failure_score_commentary6 =
1552 DECODE(x_failure_score_commentary6,
1553 NULL, hop.failure_score_commentary6,
1554 fnd_api.g_miss_char, NULL,
1555 x_failure_score_commentary6),
1556 hop.failure_score_commentary7 =
1557 DECODE(x_failure_score_commentary7,
1558 NULL, hop.failure_score_commentary7,
1559 fnd_api.g_miss_char, NULL,
1560 x_failure_score_commentary7),
1561 hop.failure_score_commentary8 =
1562 DECODE(x_failure_score_commentary8,
1563 NULL, hop.failure_score_commentary8,
1564 fnd_api.g_miss_char, NULL,
1565 x_failure_score_commentary8),
1566 hop.failure_score_commentary9 =
1567 DECODE(x_failure_score_commentary9,
1568 NULL, hop.failure_score_commentary9,
1569 fnd_api.g_miss_char, NULL,
1570 x_failure_score_commentary9),
1571 hop.failure_score_commentary10 =
1572 DECODE(x_failure_score_commentary10,
1573 NULL, hop.failure_score_commentary10,
1574 fnd_api.g_miss_char, NULL,
1575 x_failure_score_commentary10),
1576 hop.credit_score_commentary2 =
1577 DECODE(x_credit_score_commentary2,
1578 NULL, hop.credit_score_commentary2,
1579 fnd_api.g_miss_char, NULL,
1580 x_credit_score_commentary2),
1581 hop.credit_score_commentary3 =
1582 DECODE(x_credit_score_commentary3,
1583 NULL, hop.credit_score_commentary3,
1584 fnd_api.g_miss_char, NULL,
1585 x_credit_score_commentary3),
1586 hop.credit_score_commentary4 =
1587 DECODE(x_credit_score_commentary4,
1588 NULL, hop.credit_score_commentary4,
1589 fnd_api.g_miss_char, NULL,
1590 x_credit_score_commentary4),
1591 hop.credit_score_commentary5 =
1592 DECODE(x_credit_score_commentary5,
1593 NULL, hop.credit_score_commentary5,
1594 fnd_api.g_miss_char, NULL,
1595 x_credit_score_commentary5),
1596 hop.credit_score_commentary6 =
1597 DECODE(x_credit_score_commentary6,
1598 NULL, hop.credit_score_commentary6,
1599 fnd_api.g_miss_char, NULL,
1600 x_credit_score_commentary6),
1601 hop.credit_score_commentary7 =
1602 DECODE(x_credit_score_commentary7,
1603 NULL, hop.credit_score_commentary7,
1604 fnd_api.g_miss_char, NULL,
1605 x_credit_score_commentary7),
1606 hop.credit_score_commentary8 =
1607 DECODE(x_credit_score_commentary8,
1608 NULL, hop.credit_score_commentary8,
1609 fnd_api.g_miss_char, NULL,
1610 x_credit_score_commentary8),
1611 hop.credit_score_commentary9 =
1612 DECODE(x_credit_score_commentary9,
1613 NULL, hop.credit_score_commentary9,
1614 fnd_api.g_miss_char, NULL,
1615 x_credit_score_commentary9),
1616 hop.credit_score_commentary10 =
1617 DECODE(x_credit_score_commentary10,
1618 NULL, hop.credit_score_commentary10,
1619 fnd_api.g_miss_char, NULL,
1620 x_credit_score_commentary10),
1621 hop.maximum_credit_recommendation =
1622 DECODE(x_maximum_credit_recomm,
1623 NULL, hop.maximum_credit_recommendation,
1624 fnd_api.g_miss_num, NULL,
1628 NULL, hop.maximum_credit_currency_code,
1625 x_maximum_credit_recomm),
1626 hop.maximum_credit_currency_code =
1627 DECODE(x_maximum_credit_currency_code,
1629 fnd_api.g_miss_char, NULL,
1630 x_maximum_credit_currency_code),
1631 hop.displayed_duns_party_id =
1632 DECODE(x_displayed_duns_party_id,
1633 NULL, hop.displayed_duns_party_id,
1634 fnd_api.g_miss_num, NULL,
1635 x_displayed_duns_party_id),
1636 hop.failure_score_natnl_percentile =
1637 DECODE(x_failure_score_natnl_perc,
1638 NULL, hop.failure_score_natnl_percentile,
1639 fnd_api.g_miss_num, NULL,
1640 x_failure_score_natnl_perc),
1641 hop.duns_number_c =
1642 DECODE(x_duns_number_c,
1643 NULL, hop.duns_number_c,
1644 fnd_api.g_miss_char, NULL,
1645 /*Bug 3435702*/ UPPER(l_duns_number_c)),
1646 hop.duns_number =
1647 DECODE(x_duns_number_c,
1648 NULL, hop.duns_number,
1649 /*Bug 3435702.This is redundant because if x_duns_number_c = fnd_api.g_miss_char,
1650 then l_duns_number = NULL.
1651 fnd_api.g_miss_char, NULL,
1652 */
1653 l_duns_number ),
1654 hop.object_version_number =
1655 DECODE(x_object_version_number,
1656 NULL, hop.object_version_number,
1657 fnd_api.g_miss_num, NULL,
1658 x_object_version_number),
1659 hop.created_by_module =
1660 DECODE(x_created_by_module,
1661 NULL, hop.created_by_module,
1662 fnd_api.g_miss_char, NULL,
1663 x_created_by_module),
1664 hop.application_id =
1665 DECODE(x_application_id,
1666 NULL, hop.application_id,
1667 fnd_api.g_miss_num, NULL,
1668 x_application_id),
1669 hop.bank_or_branch_number =
1670 DECODE(x_bank_or_branch_number,
1671 NULL, hop.bank_or_branch_number,
1672 fnd_api.g_miss_char, NULL,
1673 x_bank_or_branch_number),
1674 hop.bank_code =
1675 DECODE(x_bank_code,
1676 NULL, hop.bank_code,
1677 fnd_api.g_miss_char, NULL,
1678 x_bank_code),
1679 hop.branch_code =
1680 DECODE(x_branch_code,
1681 NULL, hop.branch_code,
1682 fnd_api.g_miss_char, NULL,
1683 x_branch_code),
1684 hop.do_not_confuse_with =
1685 DECODE(x_do_not_confuse_with,
1686 NULL, hop.do_not_confuse_with,
1687 fnd_api.g_miss_char, NULL,
1688 x_do_not_confuse_with),
1689 hop.actual_content_source =
1690 DECODE(x_actual_content_source,
1691 NULL, hop.actual_content_source,
1692 fnd_api.g_miss_char, NULL,
1693 x_actual_content_source),
1694 hop.version_number =
1695 DECODE(x_version_number,
1696 NULL, hop.version_number,
1697 fnd_api.g_miss_num, NULL,
1698 x_version_number),
1699 hop.home_country =
1700 DECODE(x_home_country,
1701 NULL, hop.home_country,
1702 fnd_api.g_miss_char, NULL,
1703 x_home_country)
1704 WHERE hop.ROWID = x_rowid;
1705
1706 IF (SQL%NOTFOUND) THEN
1707 RAISE NO_DATA_FOUND;
1708 END IF;
1709
1710 END update_row;
1711
1712 PROCEDURE lock_row (
1713 x_rowid IN OUT NOCOPY VARCHAR2,
1714 x_organization_profile_id IN NUMBER,
1715 x_party_id IN NUMBER,
1716 x_organization_name IN VARCHAR2,
1717 x_attribute_category IN VARCHAR2,
1718 x_attribute1 IN VARCHAR2,
1719 x_attribute2 IN VARCHAR2,
1720 x_attribute3 IN VARCHAR2,
1721 x_attribute4 IN VARCHAR2,
1722 x_attribute5 IN VARCHAR2,
1723 x_attribute6 IN VARCHAR2,
1724 x_attribute7 IN VARCHAR2,
1725 x_attribute8 IN VARCHAR2,
1726 x_attribute9 IN VARCHAR2,
1727 x_attribute10 IN VARCHAR2,
1728 x_attribute11 IN VARCHAR2,
1729 x_attribute12 IN VARCHAR2,
1730 x_attribute13 IN VARCHAR2,
1731 x_attribute14 IN VARCHAR2,
1732 x_attribute15 IN VARCHAR2,
1733 x_attribute16 IN VARCHAR2,
1734 x_attribute17 IN VARCHAR2,
1735 x_attribute18 IN VARCHAR2,
1736 x_attribute19 IN VARCHAR2,
1737 x_attribute20 IN VARCHAR2,
1738 x_enquiry_duns IN VARCHAR2,
1739 x_ceo_name IN VARCHAR2,
1740 x_ceo_title IN VARCHAR2,
1741 x_principal_name IN VARCHAR2,
1742 x_principal_title IN VARCHAR2,
1746 x_hq_branch_ind IN VARCHAR2,
1743 x_legal_status IN VARCHAR2,
1744 x_control_yr IN NUMBER,
1745 x_employees_total IN NUMBER,
1747 x_branch_flag IN VARCHAR2,
1748 x_oob_ind IN VARCHAR2,
1749 x_line_of_business IN VARCHAR2,
1750 x_cong_dist_code IN VARCHAR2,
1751 x_sic_code IN VARCHAR2,
1752 x_import_ind IN VARCHAR2,
1753 x_export_ind IN VARCHAR2,
1754 x_labor_surplus_ind IN VARCHAR2,
1755 x_debarment_ind IN VARCHAR2,
1756 x_minority_owned_ind IN VARCHAR2,
1757 x_minority_owned_type IN VARCHAR2,
1758 x_woman_owned_ind IN VARCHAR2,
1759 x_disadv_8a_ind IN VARCHAR2,
1760 x_small_bus_ind IN VARCHAR2,
1761 x_rent_own_ind IN VARCHAR2,
1762 x_debarments_count IN NUMBER,
1763 x_debarments_date IN DATE,
1764 x_failure_score IN VARCHAR2,
1765 x_failure_score_override_code IN VARCHAR2,
1766 x_failure_score_commentary IN VARCHAR2,
1767 x_global_failure_score IN VARCHAR2,
1768 x_db_rating IN VARCHAR2,
1769 x_credit_score IN VARCHAR2,
1770 x_credit_score_commentary IN VARCHAR2,
1771 x_paydex_score IN VARCHAR2,
1772 x_paydex_three_months_ago IN VARCHAR2,
1773 x_paydex_norm IN VARCHAR2,
1774 x_best_time_contact_begin IN DATE,
1775 x_best_time_contact_end IN DATE,
1776 x_organization_name_phonetic IN VARCHAR2,
1777 x_tax_reference IN VARCHAR2,
1778 x_gsa_indicator_flag IN VARCHAR2,
1779 x_jgzz_fiscal_code IN VARCHAR2,
1780 x_analysis_fy IN VARCHAR2,
1781 x_fiscal_yearend_month IN VARCHAR2,
1782 x_curr_fy_potential_revenue IN NUMBER,
1783 x_next_fy_potential_revenue IN NUMBER,
1784 x_year_established IN NUMBER,
1785 x_mission_statement IN VARCHAR2,
1786 x_organization_type IN VARCHAR2,
1787 x_business_scope IN VARCHAR2,
1788 x_corporation_class IN VARCHAR2,
1789 x_known_as IN VARCHAR2,
1790 x_local_bus_iden_type IN VARCHAR2,
1791 x_local_bus_identifier IN VARCHAR2,
1792 x_pref_functional_currency IN VARCHAR2,
1793 x_registration_type IN VARCHAR2,
1794 x_total_employees_text IN VARCHAR2,
1795 x_total_employees_ind IN VARCHAR2,
1796 x_total_emp_est_ind IN VARCHAR2,
1797 x_total_emp_min_ind IN VARCHAR2,
1798 x_parent_sub_ind IN VARCHAR2,
1799 x_incorp_year IN NUMBER,
1800 x_last_update_date IN DATE,
1801 x_last_updated_by IN NUMBER,
1802 x_creation_date IN DATE,
1803 x_created_by IN NUMBER,
1804 x_last_update_login IN NUMBER,
1805 x_request_id IN NUMBER,
1806 x_program_application_id IN NUMBER,
1807 x_program_id IN NUMBER,
1808 x_program_update_date IN DATE,
1809 x_content_source_type IN VARCHAR2,
1810 x_content_source_number IN VARCHAR2,
1811 x_effective_start_date IN DATE,
1812 x_effective_end_date IN DATE,
1813 x_sic_code_type IN VARCHAR2,
1814 x_public_private_ownership IN VARCHAR2,
1815 x_local_activity_code_type IN VARCHAR2,
1816 x_local_activity_code IN VARCHAR2,
1817 x_emp_at_primary_adr IN VARCHAR2,
1818 x_emp_at_primary_adr_text IN VARCHAR2,
1819 x_emp_at_primary_adr_est_ind IN VARCHAR2,
1820 x_emp_at_primary_adr_min_ind IN VARCHAR2,
1821 x_internal_flag IN VARCHAR2,
1822 x_high_credit IN NUMBER,
1823 x_avg_high_credit IN NUMBER,
1824 x_total_payments IN NUMBER,
1825 x_known_as2 IN VARCHAR2,
1826 x_known_as3 IN VARCHAR2,
1827 x_known_as4 IN VARCHAR2,
1828 x_known_as5 IN VARCHAR2,
1829 x_credit_score_class IN NUMBER,
1830 x_credit_score_natl_percentile IN NUMBER,
1831 x_credit_score_incd_default IN NUMBER,
1832 x_credit_score_age IN NUMBER,
1833 x_credit_score_date IN DATE,
1834 x_failure_score_class IN NUMBER,
1835 x_failure_score_incd_default IN NUMBER,
1836 x_failure_score_age IN NUMBER,
1837 x_failure_score_date IN DATE,
1838 x_failure_score_commentary2 IN VARCHAR2,
1839 x_failure_score_commentary3 IN VARCHAR2,
1843 x_failure_score_commentary7 IN VARCHAR2,
1840 x_failure_score_commentary4 IN VARCHAR2,
1841 x_failure_score_commentary5 IN VARCHAR2,
1842 x_failure_score_commentary6 IN VARCHAR2,
1844 x_failure_score_commentary8 IN VARCHAR2,
1845 x_failure_score_commentary9 IN VARCHAR2,
1846 x_failure_score_commentary10 IN VARCHAR2,
1847 x_credit_score_commentary2 IN VARCHAR2,
1848 x_credit_score_commentary3 IN VARCHAR2,
1849 x_credit_score_commentary4 IN VARCHAR2,
1850 x_credit_score_commentary5 IN VARCHAR2,
1851 x_credit_score_commentary6 IN VARCHAR2,
1852 x_credit_score_commentary7 IN VARCHAR2,
1853 x_credit_score_commentary8 IN VARCHAR2,
1854 x_credit_score_commentary9 IN VARCHAR2,
1855 x_credit_score_commentary10 IN VARCHAR2,
1856 x_maximum_credit_recomm IN NUMBER,
1857 x_maximum_credit_currency_code IN VARCHAR2,
1858 x_displayed_duns_party_id IN NUMBER,
1859 x_failure_score_natnl_perc IN NUMBER,
1860 x_duns_number_c IN VARCHAR2,
1861 x_bank_or_branch_number IN VARCHAR2,
1862 x_bank_code IN VARCHAR2,
1863 x_branch_code IN VARCHAR2,
1864 x_object_version_number IN NUMBER,
1865 x_created_by_module IN VARCHAR2,
1866 x_application_id IN NUMBER,
1867 x_do_not_confuse_with IN VARCHAR2,
1868 x_actual_content_source IN VARCHAR2 DEFAULT NULL
1869 ) IS
1870
1871 CURSOR c IS
1872 SELECT *
1873 FROM hz_organization_profiles hop
1874 WHERE hop.ROWID = x_Rowid
1875 FOR UPDATE NOWAIT;
1876 recinfo c%ROWTYPE;
1877
1878 BEGIN
1879
1880 OPEN c;
1881 FETCH c INTO recinfo;
1882 IF (c%NOTFOUND) THEN
1883 CLOSE c;
1884 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1885 app_exception.raise_exception;
1886 END IF;
1887 CLOSE c;
1888
1889 IF (((recinfo.organization_profile_id = x_organization_profile_id)
1890 OR ((recinfo.organization_profile_id IS NULL)
1891 AND (x_organization_profile_id IS NULL)))
1892 AND ((recinfo.party_id = x_party_id)
1893 OR ((recinfo.party_id IS NULL)
1894 AND (x_party_id IS NULL)))
1895 AND ((recinfo.organization_name = x_organization_name)
1896 OR ((recinfo.organization_name IS NULL)
1897 AND (x_organization_name IS NULL)))
1898 AND ((recinfo.attribute_category = x_attribute_category)
1899 OR ((recinfo.attribute_category IS NULL)
1900 AND (x_attribute_category IS NULL)))
1901 AND ((recinfo.attribute1 = x_attribute1)
1902 OR ((recinfo.attribute1 IS NULL)
1903 AND (x_attribute1 IS NULL)))
1904 AND ((recinfo.attribute2 = x_attribute2)
1905 OR ((recinfo.attribute2 IS NULL)
1906 AND (x_attribute2 IS NULL)))
1907 AND ((recinfo.attribute3 = x_attribute3)
1908 OR ((recinfo.attribute3 IS NULL)
1909 AND (x_attribute3 IS NULL)))
1910 AND ((recinfo.attribute4 = x_attribute4)
1911 OR ((recinfo.attribute4 IS NULL)
1912 AND (x_attribute4 IS NULL)))
1913 AND ((recinfo.attribute5 = x_attribute5)
1914 OR ((recinfo.attribute5 IS NULL)
1915 AND (x_attribute5 IS NULL)))
1916 AND ((recinfo.attribute6 = x_attribute6)
1917 OR ((recinfo.attribute6 IS NULL)
1918 AND (x_attribute6 IS NULL)))
1919 AND ((recinfo.attribute7 = x_attribute7)
1920 OR ((recinfo.attribute7 IS NULL)
1921 AND (x_attribute7 IS NULL)))
1922 AND ((recinfo.attribute8 = x_attribute8)
1923 OR ((recinfo.attribute8 IS NULL)
1924 AND (x_attribute8 IS NULL)))
1925 AND ((recinfo.attribute9 = x_attribute9)
1926 OR ((recinfo.attribute9 IS NULL)
1927 AND (x_attribute9 IS NULL)))
1928 AND ((recinfo.attribute10 = x_attribute10)
1929 OR ((recinfo.attribute10 IS NULL)
1930 AND (x_attribute10 IS NULL)))
1931 AND ((recinfo.attribute11 = x_attribute11)
1932 OR ((recinfo.attribute11 IS NULL)
1933 AND (x_attribute11 IS NULL)))
1934 AND ((recinfo.attribute12 = x_attribute12)
1935 OR ((recinfo.attribute12 IS NULL)
1936 AND (x_attribute12 IS NULL)))
1937 AND ((recinfo.attribute13 = x_attribute13)
1938 OR ((recinfo.attribute13 IS NULL)
1939 AND (x_attribute13 IS NULL)))
1940 AND ((recinfo.attribute14 = x_attribute14)
1941 OR ((recinfo.attribute14 IS NULL)
1942 AND (x_attribute14 IS NULL)))
1943 AND ((recinfo.attribute15 = x_attribute15)
1944 OR ((recinfo.attribute15 IS NULL)
1945 AND (x_attribute15 IS NULL)))
1946 AND ((recinfo.attribute16 = x_attribute16)
1947 OR ((recinfo.attribute16 IS NULL)
1948 AND (x_attribute16 IS NULL)))
1949 AND ((recinfo.attribute17 = x_attribute17)
1950 OR ((recinfo.attribute17 IS NULL)
1951 AND (x_attribute17 IS NULL)))
1952 AND ((recinfo.attribute18 = x_attribute18)
1953 OR ((recinfo.attribute18 IS NULL)
1954 AND (x_attribute18 IS NULL)))
1955 AND ((recinfo.attribute19 = x_attribute19)
1956 OR ((recinfo.attribute19 IS NULL)
1957 AND (x_attribute19 IS NULL)))
1961 AND ((recinfo.enquiry_duns = x_enquiry_duns)
1958 AND ((recinfo.attribute20 = x_attribute20)
1959 OR ((recinfo.attribute20 IS NULL)
1960 AND (x_attribute20 IS NULL)))
1962 OR ((recinfo.enquiry_duns IS NULL)
1963 AND (x_enquiry_duns IS NULL)))
1964 AND ((recinfo.ceo_name = x_ceo_name)
1965 OR ((recinfo.ceo_name IS NULL)
1966 AND (x_ceo_name IS NULL)))
1967 AND ((recinfo.ceo_title = x_ceo_title)
1968 OR ((recinfo.ceo_title IS NULL)
1969 AND (x_ceo_title IS NULL)))
1970 AND ((recinfo.principal_name = x_principal_name)
1971 OR ((recinfo.principal_name IS NULL)
1972 AND (x_principal_name IS NULL)))
1973 AND ((recinfo.principal_title = x_principal_title)
1974 OR ((recinfo.principal_title IS NULL)
1975 AND (x_principal_title IS NULL)))
1976 AND ((recinfo.legal_status = x_legal_status)
1977 OR ((recinfo.legal_status IS NULL)
1978 AND (x_legal_status IS NULL)))
1979 AND ((recinfo.control_yr = x_control_yr)
1980 OR ((recinfo.control_yr IS NULL)
1981 AND (x_control_yr IS NULL)))
1982 AND ((recinfo.employees_total = x_employees_total)
1983 OR ((recinfo.employees_total IS NULL)
1984 AND (x_employees_total IS NULL)))
1985 AND ((recinfo.hq_branch_ind = x_hq_branch_ind)
1986 OR ((recinfo.hq_branch_ind IS NULL)
1987 AND (x_hq_branch_ind IS NULL)))
1988 AND ((recinfo.branch_flag = x_branch_flag)
1989 OR ((recinfo.branch_flag IS NULL)
1990 AND (x_branch_flag IS NULL)))
1991 AND ((recinfo.oob_ind = x_oob_ind)
1992 OR ((recinfo.oob_ind IS NULL)
1993 AND (x_oob_ind IS NULL)))
1994 AND ((recinfo.line_of_business = x_line_of_business)
1995 OR ((recinfo.line_of_business IS NULL)
1996 AND (x_line_of_business IS NULL)))
1997 AND ((recinfo.cong_dist_code = x_cong_dist_code)
1998 OR ((recinfo.cong_dist_code IS NULL)
1999 AND (x_cong_dist_code IS NULL)))
2000 AND ((recinfo.sic_code = x_sic_code)
2001 OR ((recinfo.sic_code IS NULL)
2002 AND (x_sic_code IS NULL)))
2003 AND ((recinfo.import_ind = x_import_ind)
2004 OR ((recinfo.import_ind IS NULL)
2005 AND (x_import_ind IS NULL)))
2006 AND ((recinfo.export_ind = x_export_ind)
2007 OR ((recinfo.export_ind IS NULL)
2008 AND (x_export_ind IS NULL)))
2009 AND ((recinfo.labor_surplus_ind = x_labor_surplus_ind)
2010 OR ((recinfo.labor_surplus_ind IS NULL)
2011 AND (x_labor_surplus_ind IS NULL)))
2012 AND ((recinfo.debarment_ind = x_debarment_ind)
2013 OR ((recinfo.debarment_ind IS NULL)
2014 AND (x_debarment_ind IS NULL)))
2015 AND ((recinfo.minority_owned_ind = x_minority_owned_ind)
2016 OR ((recinfo.minority_owned_ind IS NULL)
2017 AND (x_minority_owned_ind IS NULL)))
2018 AND ((recinfo.minority_owned_type = x_minority_owned_type)
2019 OR ((recinfo.minority_owned_type IS NULL)
2020 AND (x_minority_owned_type IS NULL)))
2021 AND ((recinfo.woman_owned_ind = x_woman_owned_ind)
2022 OR ((recinfo.woman_owned_ind IS NULL)
2023 AND (x_woman_owned_ind IS NULL)))
2024 AND ((recinfo.disadv_8a_ind = x_disadv_8a_ind)
2025 OR ((recinfo.disadv_8a_ind IS NULL)
2026 AND (x_disadv_8a_ind IS NULL)))
2027 AND ((recinfo.small_bus_ind = x_small_bus_ind)
2028 OR ((recinfo.small_bus_ind IS NULL)
2029 AND (x_small_bus_ind IS NULL)))
2030 AND ((recinfo.rent_own_ind = x_rent_own_ind)
2031 OR ((recinfo.rent_own_ind IS NULL)
2032 AND (x_rent_own_ind IS NULL)))
2033 AND ((recinfo.debarments_count = x_debarments_count)
2034 OR ((recinfo.debarments_count IS NULL)
2035 AND (x_debarments_count IS NULL)))
2036 AND ((recinfo.debarments_date = x_debarments_date)
2037 OR ((recinfo.debarments_date IS NULL)
2038 AND (x_debarments_date IS NULL)))
2039 AND ((recinfo.failure_score = x_failure_score)
2040 OR ((recinfo.failure_score IS NULL)
2041 AND (x_failure_score IS NULL)))
2042 AND ((recinfo.failure_score_override_code =
2043 x_failure_score_override_code)
2044 OR ((recinfo.failure_score_override_code IS NULL)
2045 AND (x_failure_score_override_code IS NULL)))
2046 AND ((recinfo.failure_score_commentary = x_failure_score_commentary)
2047 OR ((recinfo.failure_score_commentary IS NULL)
2048 AND (x_failure_score_commentary IS NULL)))
2049 AND ((recinfo.global_failure_score = x_global_failure_score)
2050 OR ((recinfo.global_failure_score IS NULL)
2051 AND (x_global_failure_score IS NULL)))
2052 AND ((recinfo.db_rating = x_db_rating)
2053 OR ((recinfo.db_rating IS NULL)
2054 AND (x_db_rating IS NULL)))
2055 AND ((recinfo.credit_score = x_credit_score)
2056 OR ((recinfo.credit_score IS NULL)
2057 AND (x_credit_score IS NULL)))
2058 AND ((recinfo.credit_score_commentary = x_credit_score_commentary)
2059 OR ((recinfo.credit_score_commentary IS NULL)
2060 AND (x_credit_score_commentary IS NULL)))
2061 AND ((recinfo.paydex_score = x_paydex_score)
2062 OR ((recinfo.paydex_score IS NULL)
2063 AND (x_paydex_score IS NULL)))
2064 AND ((recinfo.paydex_three_months_ago = x_paydex_three_months_ago)
2065 OR ((recinfo.paydex_three_months_ago IS NULL)
2069 AND (x_paydex_norm IS NULL)))
2066 AND (x_paydex_three_months_ago IS NULL)))
2067 AND ((recinfo.paydex_norm = x_paydex_norm)
2068 OR ((recinfo.paydex_norm IS NULL)
2070 AND ((recinfo.best_time_contact_begin = x_best_time_contact_begin)
2071 OR ((recinfo.best_time_contact_begin IS NULL)
2072 AND (x_best_time_contact_begin IS NULL)))
2073 AND ((recinfo.best_time_contact_end = x_best_time_contact_end)
2074 OR ((recinfo.best_time_contact_end IS NULL)
2075 AND (x_best_time_contact_end IS NULL)))
2076 AND ((recinfo.organization_name_phonetic =
2077 x_organization_name_phonetic)
2078 OR ((recinfo.organization_name_phonetic IS NULL)
2079 AND (x_organization_name_phonetic IS NULL)))
2080 AND ((recinfo.tax_reference = x_tax_reference)
2081 OR ((recinfo.tax_reference IS NULL)
2082 AND (x_tax_reference IS NULL)))
2083 AND ((recinfo.gsa_indicator_flag = x_gsa_indicator_flag)
2084 OR ((recinfo.gsa_indicator_flag IS NULL)
2085 AND (x_gsa_indicator_flag IS NULL)))
2086 AND ((recinfo.jgzz_fiscal_code = x_jgzz_fiscal_code)
2087 OR ((recinfo.jgzz_fiscal_code IS NULL)
2088 AND (x_jgzz_fiscal_code IS NULL)))
2089 AND ((recinfo.analysis_fy = x_analysis_fy)
2090 OR ((recinfo.analysis_fy IS NULL)
2091 AND (x_analysis_fy IS NULL)))
2092 AND ((recinfo.fiscal_yearend_month = x_fiscal_yearend_month)
2093 OR ((recinfo.fiscal_yearend_month IS NULL)
2094 AND (x_fiscal_yearend_month IS NULL)))
2095 AND ((recinfo.curr_fy_potential_revenue = x_curr_fy_potential_revenue)
2096 OR ((recinfo.curr_fy_potential_revenue IS NULL)
2097 AND (x_curr_fy_potential_revenue IS NULL)))
2098 AND ((recinfo.next_fy_potential_revenue = x_next_fy_potential_revenue)
2099 OR ((recinfo.next_fy_potential_revenue IS NULL)
2100 AND (x_next_fy_potential_revenue IS NULL)))
2101 AND ((recinfo.year_established = x_year_established)
2102 OR ((recinfo.year_established IS NULL)
2103 AND (x_year_established IS NULL)))
2104 AND ((recinfo.mission_statement = x_mission_statement)
2105 OR ((recinfo.mission_statement IS NULL)
2106 AND (x_mission_statement IS NULL)))
2107 AND ((recinfo.organization_type = x_organization_type)
2108 OR ((recinfo.organization_type IS NULL)
2109 AND (x_organization_type IS NULL)))
2110 AND ((recinfo.business_scope = x_business_scope)
2111 OR ((recinfo.business_scope IS NULL)
2112 AND (x_business_scope IS NULL)))
2113 AND ((recinfo.corporation_class = x_corporation_class)
2114 OR ((recinfo.corporation_class IS NULL)
2115 AND (x_corporation_class IS NULL)))
2116 AND ((recinfo.known_as = x_known_as)
2117 OR ((recinfo.known_as IS NULL)
2118 AND (x_known_as IS NULL)))
2119 AND ((recinfo.local_bus_iden_type = x_local_bus_iden_type)
2120 OR ((recinfo.local_bus_iden_type IS NULL)
2121 AND (x_local_bus_iden_type IS NULL)))
2122 AND ((recinfo.local_bus_identifier = x_local_bus_identifier)
2123 OR ((recinfo.local_bus_identifier IS NULL)
2124 AND (x_local_bus_identifier IS NULL)))
2125 AND ((recinfo.pref_functional_currency = x_pref_functional_currency)
2126 OR ((recinfo.pref_functional_currency IS NULL)
2127 AND (x_pref_functional_currency IS NULL)))
2128 AND ((recinfo.registration_type = x_registration_type)
2129 OR ((recinfo.registration_type IS NULL)
2130 AND (x_registration_type IS NULL)))
2131 AND ((recinfo.total_employees_text = x_total_employees_text)
2132 OR ((recinfo.total_employees_text IS NULL)
2133 AND (x_total_employees_text IS NULL)))
2134 AND ((recinfo.total_employees_ind = x_total_employees_ind)
2135 OR ((recinfo.total_employees_ind IS NULL)
2136 AND (x_total_employees_ind IS NULL)))
2137 AND ((recinfo.total_emp_est_ind = x_total_emp_est_ind)
2138 OR ((recinfo.total_emp_est_ind IS NULL)
2139 AND (x_total_emp_est_ind IS NULL)))
2140 AND ((recinfo.total_emp_min_ind = x_total_emp_min_ind)
2141 OR ((recinfo.total_emp_min_ind IS NULL)
2142 AND (x_total_emp_min_ind IS NULL)))
2143 AND ((recinfo.parent_sub_ind = x_parent_sub_ind)
2144 OR ((recinfo.parent_sub_ind IS NULL)
2145 AND (x_parent_sub_ind IS NULL)))
2146 AND ((recinfo.incorp_year = x_incorp_year)
2147 OR ((recinfo.incorp_year IS NULL)
2148 AND (x_incorp_year IS NULL)))
2149 AND ((recinfo.last_update_date = x_last_update_date)
2150 OR ((recinfo.last_update_date IS NULL)
2151 AND (x_last_update_date IS NULL)))
2152 AND ((recinfo.last_updated_by = x_last_updated_by)
2153 OR ((recinfo.last_updated_by IS NULL)
2154 AND (x_last_updated_by IS NULL)))
2155 AND ((recinfo.creation_date = x_creation_date)
2156 OR ((recinfo.creation_date IS NULL)
2157 AND (x_creation_date IS NULL)))
2158 AND ((recinfo.created_by = x_created_by)
2159 OR ((recinfo.created_by IS NULL)
2160 AND (x_created_by IS NULL)))
2161 AND ((recinfo.last_update_login = x_last_update_login)
2162 OR ((recinfo.last_update_login IS NULL)
2163 AND (x_last_update_login IS NULL)))
2164 AND ((recinfo.request_id = x_request_id)
2165 OR ((recinfo.request_id IS NULL)
2166 AND (x_request_id IS NULL)))
2167 AND ((recinfo.program_application_id = x_program_application_id)
2171 OR ((recinfo.program_id IS NULL)
2168 OR ((recinfo.program_application_id IS NULL)
2169 AND (x_program_application_id IS NULL)))
2170 AND ((recinfo.program_id = x_program_id)
2172 AND (x_program_id IS NULL)))
2173 AND ((recinfo.program_update_date = x_program_update_date)
2174 OR ((recinfo.program_update_date IS NULL)
2175 AND (x_program_update_date IS NULL)))
2176 AND ((recinfo.content_source_type = x_content_source_type)
2177 OR ((recinfo.content_source_type IS NULL)
2178 AND (x_content_source_type IS NULL)))
2179 AND ((recinfo.content_source_number = x_content_source_number)
2180 OR ((recinfo.content_source_number IS NULL)
2181 AND (x_content_source_number IS NULL)))
2182 AND ((recinfo.effective_start_date = x_effective_start_date)
2183 OR ((recinfo.effective_start_date IS NULL)
2184 AND (x_effective_start_date IS NULL)))
2185 AND ((recinfo.effective_end_date = x_effective_end_date)
2186 OR ((recinfo.effective_end_date IS NULL)
2187 AND (x_effective_end_date IS NULL)))
2188 AND ((recinfo.sic_code_type = x_sic_code_type)
2189 OR ((recinfo.sic_code_type IS NULL)
2190 AND (x_sic_code_type IS NULL)))
2191 AND ((recinfo.public_private_ownership_flag =
2192 x_public_private_ownership)
2193 OR ((recinfo.public_private_ownership_flag IS NULL)
2194 AND (x_public_private_ownership IS NULL)))
2195 AND ((recinfo.local_activity_code_type = x_local_activity_code_type)
2196 OR ((recinfo.local_activity_code_type IS NULL)
2197 AND (x_local_activity_code_type IS NULL)))
2198 AND ((recinfo.local_activity_code = x_local_activity_code)
2199 OR ((recinfo.local_activity_code IS NULL)
2200 AND (x_local_activity_code IS NULL)))
2201 AND ((recinfo.emp_at_primary_adr = x_emp_at_primary_adr)
2202 OR ((recinfo.emp_at_primary_adr IS NULL)
2203 AND (x_emp_at_primary_adr IS NULL)))
2204 AND ((recinfo.emp_at_primary_adr_text = x_emp_at_primary_adr_text)
2205 OR ((recinfo.emp_at_primary_adr_text IS NULL)
2206 AND (x_emp_at_primary_adr_text IS NULL)))
2207 AND ((recinfo.emp_at_primary_adr_est_ind =
2208 x_emp_at_primary_adr_est_ind)
2209 OR ((recinfo.emp_at_primary_adr_est_ind IS NULL)
2210 AND (x_emp_at_primary_adr_est_ind IS NULL)))
2211 AND ((recinfo.emp_at_primary_adr_min_ind =
2212 x_emp_at_primary_adr_min_ind)
2213 OR ((recinfo.emp_at_primary_adr_min_ind IS NULL)
2214 AND (x_emp_at_primary_adr_min_ind IS NULL)))
2215 AND ((recinfo.internal_flag = x_internal_flag)
2216 OR ((recinfo.internal_flag IS NULL)
2217 AND (x_internal_flag IS NULL)))
2218 AND ((recinfo.high_credit = x_high_credit)
2219 OR ((recinfo.high_credit IS NULL)
2220 AND (x_high_credit IS NULL)))
2221 AND ((recinfo.avg_high_credit = x_avg_high_credit)
2222 OR ((recinfo.avg_high_credit IS NULL)
2223 AND (x_avg_high_credit IS NULL)))
2224 AND ((recinfo.total_payments = x_total_payments)
2225 OR ((recinfo.total_payments IS NULL)
2226 AND (x_total_payments IS NULL)))
2227 AND ((recinfo.known_as2 = x_known_as2)
2228 OR ((recinfo.known_as2 IS NULL)
2229 AND (x_known_as2 IS NULL)))
2230 AND ((recinfo.known_as3 = x_known_as3)
2231 OR ((recinfo.known_as3 IS NULL)
2232 AND (x_known_as3 IS NULL)))
2233 AND ((recinfo.known_as4 = x_known_as4)
2234 OR ((recinfo.known_as4 IS NULL)
2235 AND (x_known_as4 IS NULL)))
2236 AND ((recinfo.known_as5 = x_known_as5)
2237 OR ((recinfo.known_as5 IS NULL)
2238 AND (x_known_as5 IS NULL)))
2239 AND ((recinfo.credit_score_class = x_credit_score_class)
2240 OR ((recinfo.credit_score_class IS NULL)
2241 AND (x_credit_score_class IS NULL)))
2242 AND ((recinfo.credit_score_natl_percentile =
2243 x_credit_score_natl_percentile)
2244 OR ((recinfo.credit_score_natl_percentile IS NULL)
2245 AND (x_credit_score_natl_percentile IS NULL)))
2246 AND ((recinfo.credit_score_incd_default = x_credit_score_incd_default)
2247 OR ((recinfo.credit_score_incd_default IS NULL)
2248 AND (x_credit_score_incd_default IS NULL)))
2249 AND ((recinfo.credit_score_age = x_credit_score_age)
2250 OR ((recinfo.credit_score_age IS NULL)
2251 AND (x_credit_score_age IS NULL)))
2252 AND ((recinfo.credit_score_date = x_credit_score_date)
2253 OR ((recinfo.credit_score_date IS NULL)
2254 AND (x_credit_score_date IS NULL)))
2255 AND ((recinfo.failure_score_class = x_failure_score_class)
2256 OR ((recinfo.failure_score_class IS NULL)
2257 AND (x_failure_score_class IS NULL)))
2258 AND ((recinfo.failure_score_incd_default =
2259 x_failure_score_incd_default)
2260 OR ((recinfo.failure_score_incd_default IS NULL)
2261 AND (x_failure_score_incd_default IS NULL)))
2262 AND ((recinfo.failure_score_age = x_failure_score_age)
2263 OR ((recinfo.failure_score_age IS NULL)
2264 AND (x_failure_score_age IS NULL)))
2265 AND ((recinfo.failure_score_date = x_failure_score_date)
2266 OR ((recinfo.failure_score_date IS NULL)
2267 AND (x_failure_score_date IS NULL)))
2268 AND ((recinfo.failure_score_commentary2 = x_failure_score_commentary2)
2269 OR ((recinfo.failure_score_commentary2 IS NULL)
2270 AND (x_failure_score_commentary2 IS NULL)))
2274 AND ((recinfo.failure_score_commentary4 = x_failure_score_commentary4)
2271 AND ((recinfo.failure_score_commentary3 = x_failure_score_commentary3)
2272 OR ((recinfo.failure_score_commentary3 IS NULL)
2273 AND (x_failure_score_commentary3 IS NULL)))
2275 OR ((recinfo.failure_score_commentary4 IS NULL)
2276 AND (x_failure_score_commentary4 IS NULL)))
2277 AND ((recinfo.failure_score_commentary5 = x_failure_score_commentary5)
2278 OR ((recinfo.failure_score_commentary5 IS NULL)
2279 AND (x_failure_score_commentary5 IS NULL)))
2280 AND ((recinfo.failure_score_commentary6 = x_failure_score_commentary6)
2281 OR ((recinfo.failure_score_commentary6 IS NULL)
2282 AND (x_failure_score_commentary6 IS NULL)))
2283 AND ((recinfo.failure_score_commentary7 = x_failure_score_commentary7)
2284 OR ((recinfo.failure_score_commentary7 IS NULL)
2285 AND (x_failure_score_commentary7 IS NULL)))
2286 AND ((recinfo.failure_score_commentary8 = x_failure_score_commentary8)
2287 OR ((recinfo.failure_score_commentary8 IS NULL)
2288 AND (x_failure_score_commentary8 IS NULL)))
2289 AND ((recinfo.failure_score_commentary9 = x_failure_score_commentary9)
2290 OR ((recinfo.failure_score_commentary9 IS NULL)
2291 AND (x_failure_score_commentary9 IS NULL)))
2292 AND ((recinfo.failure_score_commentary10 =
2293 x_failure_score_commentary10)
2294 OR ((recinfo.failure_score_commentary10 IS NULL)
2295 AND (x_failure_score_commentary10 IS NULL)))
2296 AND ((recinfo.credit_score_commentary2 = x_credit_score_commentary2)
2297 OR ((recinfo.credit_score_commentary2 IS NULL)
2298 AND (x_credit_score_commentary2 IS NULL)))
2299 AND ((recinfo.credit_score_commentary3 = x_credit_score_commentary3)
2300 OR ((recinfo.credit_score_commentary3 IS NULL)
2301 AND (x_credit_score_commentary3 IS NULL)))
2302 AND ((recinfo.credit_score_commentary4 = x_credit_score_commentary4)
2303 OR ((recinfo.credit_score_commentary4 IS NULL)
2304 AND (x_credit_score_commentary4 IS NULL)))
2305 AND ((recinfo.credit_score_commentary5 = x_credit_score_commentary5)
2306 OR ((recinfo.credit_score_commentary5 IS NULL)
2307 AND (x_credit_score_commentary5 IS NULL)))
2308 AND ((recinfo.credit_score_commentary6 = x_credit_score_commentary6)
2309 OR ((recinfo.credit_score_commentary6 IS NULL)
2310 AND (x_credit_score_commentary6 IS NULL)))
2311 AND ((recinfo.credit_score_commentary7 = x_credit_score_commentary7)
2312 OR ((recinfo.credit_score_commentary7 IS NULL)
2313 AND (x_credit_score_commentary7 IS NULL)))
2314 AND ((recinfo.credit_score_commentary8 = x_credit_score_commentary8)
2315 OR ((recinfo.credit_score_commentary8 IS NULL)
2316 AND (x_credit_score_commentary8 IS NULL)))
2317 AND ((recinfo.credit_score_commentary9 = x_credit_score_commentary9)
2318 OR ((recinfo.credit_score_commentary9 IS NULL)
2319 AND (x_credit_score_commentary9 IS NULL)))
2320 AND ((recinfo.credit_score_commentary10 = x_credit_score_commentary10)
2321 OR ((recinfo.credit_score_commentary10 IS NULL)
2322 AND (x_credit_score_commentary10 IS NULL)))
2323 AND ((recinfo.maximum_credit_recommendation = x_maximum_credit_recomm)
2324 OR ((recinfo.maximum_credit_recommendation IS NULL)
2325 AND (x_maximum_credit_recomm IS NULL)))
2326 AND ((recinfo.maximum_credit_currency_code =
2327 x_maximum_credit_currency_code)
2328 OR ((recinfo.maximum_credit_currency_code IS NULL)
2329 AND (x_maximum_credit_currency_code IS NULL)))
2330 AND ((recinfo.displayed_duns_party_id = x_displayed_duns_party_id)
2331 OR ((recinfo.displayed_duns_party_id IS NULL)
2332 AND (x_displayed_duns_party_id IS NULL)))
2333 AND ((recinfo.failure_score_natnl_percentile =
2334 x_failure_score_natnl_perc)
2335 OR ((recinfo.failure_score_natnl_percentile IS NULL)
2336 AND (x_failure_score_natnl_perc IS NULL)))
2337 AND ((recinfo.duns_number_c = x_duns_number_c)
2338 OR ((recinfo.duns_number_c IS NULL)
2339 AND (x_duns_number_c IS NULL)))
2340 AND ((recinfo.bank_or_branch_number = x_bank_or_branch_number)
2341 OR ((recinfo.bank_or_branch_number IS NULL)
2342 AND (x_bank_or_branch_number IS NULL)))
2343 AND ((recinfo.bank_code = x_bank_code)
2344 OR ((recinfo.bank_code IS NULL)
2345 AND (x_bank_code IS NULL)))
2346 AND ((recinfo.branch_code = x_branch_code)
2347 OR ((recinfo.branch_code IS NULL)
2348 AND (x_branch_code IS NULL)))
2349 AND ((recinfo.object_version_number = x_object_version_number)
2350 OR ((recinfo.object_version_number IS NULL)
2351 AND (x_object_version_number IS NULL)))
2352 AND ((recinfo.created_by_module = x_created_by_module)
2353 OR ((recinfo.created_by_module IS NULL)
2354 AND (x_created_by_module IS NULL)))
2355 AND ((recinfo.application_id = x_application_id)
2356 OR ((recinfo.application_id IS NULL)
2357 AND (x_application_id IS NULL)))
2358 AND ((recinfo.do_not_confuse_with = x_do_not_confuse_with)
2359 OR ((recinfo.do_not_confuse_with IS NULL)
2360 AND (x_do_not_confuse_with IS NULL)))
2361 AND ((recinfo.actual_content_source = x_actual_content_source)
2362 OR ((recinfo.actual_content_source IS NULL)
2363 AND (x_actual_content_source IS NULL))))
2364 THEN
2365 RETURN;
2366 ELSE
2367 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
2371 END lock_row;
2368 app_exception.raise_exception;
2369 END IF;
2370
2372
2373 PROCEDURE select_row (
2374 x_organization_profile_id IN OUT NOCOPY NUMBER,
2375 x_party_id OUT NOCOPY NUMBER,
2376 x_organization_name OUT NOCOPY VARCHAR2,
2377 x_attribute_category OUT NOCOPY VARCHAR2,
2378 x_attribute1 OUT NOCOPY VARCHAR2,
2379 x_attribute2 OUT NOCOPY VARCHAR2,
2380 x_attribute3 OUT NOCOPY VARCHAR2,
2381 x_attribute4 OUT NOCOPY VARCHAR2,
2382 x_attribute5 OUT NOCOPY VARCHAR2,
2383 x_attribute6 OUT NOCOPY VARCHAR2,
2384 x_attribute7 OUT NOCOPY VARCHAR2,
2385 x_attribute8 OUT NOCOPY VARCHAR2,
2386 x_attribute9 OUT NOCOPY VARCHAR2,
2387 x_attribute10 OUT NOCOPY VARCHAR2,
2388 x_attribute11 OUT NOCOPY VARCHAR2,
2389 x_attribute12 OUT NOCOPY VARCHAR2,
2390 x_attribute13 OUT NOCOPY VARCHAR2,
2391 x_attribute14 OUT NOCOPY VARCHAR2,
2392 x_attribute15 OUT NOCOPY VARCHAR2,
2393 x_attribute16 OUT NOCOPY VARCHAR2,
2394 x_attribute17 OUT NOCOPY VARCHAR2,
2395 x_attribute18 OUT NOCOPY VARCHAR2,
2396 x_attribute19 OUT NOCOPY VARCHAR2,
2397 x_attribute20 OUT NOCOPY VARCHAR2,
2398 x_enquiry_duns OUT NOCOPY VARCHAR2,
2399 x_ceo_name OUT NOCOPY VARCHAR2,
2400 x_ceo_title OUT NOCOPY VARCHAR2,
2401 x_principal_name OUT NOCOPY VARCHAR2,
2402 x_principal_title OUT NOCOPY VARCHAR2,
2403 x_legal_status OUT NOCOPY VARCHAR2,
2404 x_control_yr OUT NOCOPY NUMBER,
2405 x_employees_total OUT NOCOPY NUMBER,
2406 x_hq_branch_ind OUT NOCOPY VARCHAR2,
2407 x_branch_flag OUT NOCOPY VARCHAR2,
2408 x_oob_ind OUT NOCOPY VARCHAR2,
2409 x_line_of_business OUT NOCOPY VARCHAR2,
2410 x_cong_dist_code OUT NOCOPY VARCHAR2,
2411 x_sic_code OUT NOCOPY VARCHAR2,
2412 x_import_ind OUT NOCOPY VARCHAR2,
2413 x_export_ind OUT NOCOPY VARCHAR2,
2414 x_labor_surplus_ind OUT NOCOPY VARCHAR2,
2415 x_debarment_ind OUT NOCOPY VARCHAR2,
2416 x_minority_owned_ind OUT NOCOPY VARCHAR2,
2417 x_minority_owned_type OUT NOCOPY VARCHAR2,
2418 x_woman_owned_ind OUT NOCOPY VARCHAR2,
2419 x_disadv_8a_ind OUT NOCOPY VARCHAR2,
2420 x_small_bus_ind OUT NOCOPY VARCHAR2,
2421 x_rent_own_ind OUT NOCOPY VARCHAR2,
2422 x_debarments_count OUT NOCOPY NUMBER,
2423 x_debarments_date OUT NOCOPY DATE,
2424 x_failure_score OUT NOCOPY VARCHAR2,
2425 x_failure_score_override_code OUT NOCOPY VARCHAR2,
2426 x_failure_score_commentary OUT NOCOPY VARCHAR2,
2427 x_global_failure_score OUT NOCOPY VARCHAR2,
2428 x_db_rating OUT NOCOPY VARCHAR2,
2429 x_credit_score OUT NOCOPY VARCHAR2,
2430 x_credit_score_commentary OUT NOCOPY VARCHAR2,
2431 x_paydex_score OUT NOCOPY VARCHAR2,
2432 x_paydex_three_months_ago OUT NOCOPY VARCHAR2,
2433 x_paydex_norm OUT NOCOPY VARCHAR2,
2434 x_best_time_contact_begin OUT NOCOPY DATE,
2435 x_best_time_contact_end OUT NOCOPY DATE,
2436 x_organization_name_phonetic OUT NOCOPY VARCHAR2,
2437 x_tax_reference OUT NOCOPY VARCHAR2,
2438 x_gsa_indicator_flag OUT NOCOPY VARCHAR2,
2439 x_jgzz_fiscal_code OUT NOCOPY VARCHAR2,
2440 x_analysis_fy OUT NOCOPY VARCHAR2,
2441 x_fiscal_yearend_month OUT NOCOPY VARCHAR2,
2442 x_curr_fy_potential_revenue OUT NOCOPY NUMBER,
2443 x_next_fy_potential_revenue OUT NOCOPY NUMBER,
2444 x_year_established OUT NOCOPY NUMBER,
2445 x_mission_statement OUT NOCOPY VARCHAR2,
2446 x_organization_type OUT NOCOPY VARCHAR2,
2447 x_business_scope OUT NOCOPY VARCHAR2,
2448 x_corporation_class OUT NOCOPY VARCHAR2,
2449 x_known_as OUT NOCOPY VARCHAR2,
2450 x_local_bus_iden_type OUT NOCOPY VARCHAR2,
2451 x_local_bus_identifier OUT NOCOPY VARCHAR2,
2452 x_pref_functional_currency OUT NOCOPY VARCHAR2,
2453 x_registration_type OUT NOCOPY VARCHAR2,
2454 x_total_employees_text OUT NOCOPY VARCHAR2,
2455 x_total_employees_ind OUT NOCOPY VARCHAR2,
2456 x_total_emp_est_ind OUT NOCOPY VARCHAR2,
2457 x_total_emp_min_ind OUT NOCOPY VARCHAR2,
2458 x_parent_sub_ind OUT NOCOPY VARCHAR2,
2459 x_incorp_year OUT NOCOPY NUMBER,
2463 x_effective_end_date OUT NOCOPY DATE,
2460 x_content_source_type OUT NOCOPY VARCHAR2,
2461 x_content_source_number OUT NOCOPY VARCHAR2,
2462 x_effective_start_date OUT NOCOPY DATE,
2464 x_sic_code_type OUT NOCOPY VARCHAR2,
2465 x_public_private_ownership OUT NOCOPY VARCHAR2,
2466 x_local_activity_code_type OUT NOCOPY VARCHAR2,
2467 x_local_activity_code OUT NOCOPY VARCHAR2,
2468 x_emp_at_primary_adr OUT NOCOPY VARCHAR2,
2469 x_emp_at_primary_adr_text OUT NOCOPY VARCHAR2,
2470 x_emp_at_primary_adr_est_ind OUT NOCOPY VARCHAR2,
2471 x_emp_at_primary_adr_min_ind OUT NOCOPY VARCHAR2,
2472 x_internal_flag OUT NOCOPY VARCHAR2,
2473 x_high_credit OUT NOCOPY NUMBER,
2474 x_avg_high_credit OUT NOCOPY NUMBER,
2475 x_total_payments OUT NOCOPY NUMBER,
2476 x_known_as2 OUT NOCOPY VARCHAR2,
2477 x_known_as3 OUT NOCOPY VARCHAR2,
2478 x_known_as4 OUT NOCOPY VARCHAR2,
2479 x_known_as5 OUT NOCOPY VARCHAR2,
2480 x_credit_score_class OUT NOCOPY NUMBER,
2481 x_credit_score_natl_percentile OUT NOCOPY NUMBER,
2482 x_credit_score_incd_default OUT NOCOPY NUMBER,
2483 x_credit_score_age OUT NOCOPY NUMBER,
2484 x_credit_score_date OUT NOCOPY DATE,
2485 x_failure_score_class OUT NOCOPY NUMBER,
2486 x_failure_score_incd_default OUT NOCOPY NUMBER,
2487 x_failure_score_age OUT NOCOPY NUMBER,
2488 x_failure_score_date OUT NOCOPY DATE,
2489 x_failure_score_commentary2 OUT NOCOPY VARCHAR2,
2490 x_failure_score_commentary3 OUT NOCOPY VARCHAR2,
2491 x_failure_score_commentary4 OUT NOCOPY VARCHAR2,
2492 x_failure_score_commentary5 OUT NOCOPY VARCHAR2,
2493 x_failure_score_commentary6 OUT NOCOPY VARCHAR2,
2494 x_failure_score_commentary7 OUT NOCOPY VARCHAR2,
2495 x_failure_score_commentary8 OUT NOCOPY VARCHAR2,
2496 x_failure_score_commentary9 OUT NOCOPY VARCHAR2,
2497 x_failure_score_commentary10 OUT NOCOPY VARCHAR2,
2498 x_credit_score_commentary2 OUT NOCOPY VARCHAR2,
2499 x_credit_score_commentary3 OUT NOCOPY VARCHAR2,
2500 x_credit_score_commentary4 OUT NOCOPY VARCHAR2,
2501 x_credit_score_commentary5 OUT NOCOPY VARCHAR2,
2502 x_credit_score_commentary6 OUT NOCOPY VARCHAR2,
2503 x_credit_score_commentary7 OUT NOCOPY VARCHAR2,
2504 x_credit_score_commentary8 OUT NOCOPY VARCHAR2,
2505 x_credit_score_commentary9 OUT NOCOPY VARCHAR2,
2506 x_credit_score_commentary10 OUT NOCOPY VARCHAR2,
2507 x_maximum_credit_recomm OUT NOCOPY NUMBER,
2508 x_maximum_credit_currency_code OUT NOCOPY VARCHAR2,
2509 x_displayed_duns_party_id OUT NOCOPY NUMBER,
2510 x_failure_score_natnl_perc OUT NOCOPY NUMBER,
2511 x_duns_number_c OUT NOCOPY VARCHAR2,
2512 x_bank_or_branch_number OUT NOCOPY VARCHAR2,
2513 x_bank_code OUT NOCOPY VARCHAR2,
2514 x_branch_code OUT NOCOPY VARCHAR2,
2515 x_created_by_module OUT NOCOPY VARCHAR2,
2516 x_application_id OUT NOCOPY NUMBER,
2517 x_do_not_confuse_with OUT NOCOPY VARCHAR2,
2518 x_actual_content_source OUT NOCOPY VARCHAR2,
2519 x_home_country OUT NOCOPY VARCHAR2
2520 ) IS
2521 CURSOR c_orgprof IS
2522 SELECT NVL(hop.organization_profile_id, fnd_api.g_miss_num),
2523 NVL(hop.party_id, fnd_api.g_miss_num),
2524 NVL(hop.organization_name, fnd_api.g_miss_char),
2525 NVL(hop.attribute_category, fnd_api.g_miss_char),
2526 NVL(hop.attribute1, fnd_api.g_miss_char),
2527 NVL(hop.attribute2, fnd_api.g_miss_char),
2528 NVL(hop.attribute3, fnd_api.g_miss_char),
2529 NVL(hop.attribute4, fnd_api.g_miss_char),
2530 NVL(hop.attribute5, fnd_api.g_miss_char),
2531 NVL(hop.attribute6, fnd_api.g_miss_char),
2532 NVL(hop.attribute7, fnd_api.g_miss_char),
2533 NVL(hop.attribute8, fnd_api.g_miss_char),
2534 NVL(hop.attribute9, fnd_api.g_miss_char),
2535 NVL(hop.attribute10, fnd_api.g_miss_char),
2536 NVL(hop.attribute11, fnd_api.g_miss_char),
2537 NVL(hop.attribute12, fnd_api.g_miss_char),
2538 NVL(hop.attribute13, fnd_api.g_miss_char),
2539 NVL(hop.attribute14, fnd_api.g_miss_char),
2540 NVL(hop.attribute15, fnd_api.g_miss_char),
2541 NVL(hop.attribute16, fnd_api.g_miss_char),
2542 NVL(hop.attribute17, fnd_api.g_miss_char),
2543 NVL(hop.attribute18, fnd_api.g_miss_char),
2544 NVL(hop.attribute19, fnd_api.g_miss_char),
2545 NVL(hop.attribute20, fnd_api.g_miss_char),
2546 NVL(hop.enquiry_duns, fnd_api.g_miss_char),
2547 NVL(hop.ceo_name, fnd_api.g_miss_char),
2548 NVL(hop.ceo_title, fnd_api.g_miss_char),
2549 NVL(hop.principal_name, fnd_api.g_miss_char),
2550 NVL(hop.principal_title, fnd_api.g_miss_char),
2551 NVL(hop.legal_status, fnd_api.g_miss_char),
2552 NVL(hop.control_yr, fnd_api.g_miss_num),
2553 NVL(hop.employees_total, fnd_api.g_miss_num),
2557 NVL(hop.line_of_business, fnd_api.g_miss_char),
2554 NVL(hop.hq_branch_ind, fnd_api.g_miss_char),
2555 NVL(hop.branch_flag, fnd_api.g_miss_char),
2556 NVL(hop.oob_ind, fnd_api.g_miss_char),
2558 NVL(hop.cong_dist_code, fnd_api.g_miss_char),
2559 NVL(hop.sic_code, fnd_api.g_miss_char),
2560 NVL(hop.import_ind, fnd_api.g_miss_char),
2561 NVL(hop.export_ind, fnd_api.g_miss_char),
2562 NVL(hop.labor_surplus_ind, fnd_api.g_miss_char),
2563 NVL(hop.debarment_ind, fnd_api.g_miss_char),
2564 NVL(hop.minority_owned_ind, fnd_api.g_miss_char),
2565 NVL(hop.minority_owned_type, fnd_api.g_miss_char),
2566 NVL(hop.woman_owned_ind, fnd_api.g_miss_char),
2567 NVL(hop.disadv_8a_ind, fnd_api.g_miss_char),
2568 NVL(hop.small_bus_ind, fnd_api.g_miss_char),
2569 NVL(hop.rent_own_ind, fnd_api.g_miss_char),
2570 NVL(hop.debarments_count, fnd_api.g_miss_num),
2571 NVL(hop.debarments_date, fnd_api.g_miss_date),
2572 NVL(hop.failure_score, fnd_api.g_miss_char),
2573 NVL(hop.failure_score_override_code, fnd_api.g_miss_char),
2574 NVL(hop.failure_score_commentary, fnd_api.g_miss_char),
2575 NVL(hop.global_failure_score, fnd_api.g_miss_char),
2576 NVL(hop.db_rating, fnd_api.g_miss_char),
2577 NVL(hop.credit_score, fnd_api.g_miss_char),
2578 NVL(hop.credit_score_commentary, fnd_api.g_miss_char),
2579 NVL(hop.paydex_score, fnd_api.g_miss_char),
2580 NVL(hop.paydex_three_months_ago, fnd_api.g_miss_char),
2581 NVL(hop.paydex_norm, fnd_api.g_miss_char),
2582 NVL(hop.best_time_contact_begin, fnd_api.g_miss_date),
2583 NVL(hop.best_time_contact_end, fnd_api.g_miss_date),
2584 NVL(hop.organization_name_phonetic, fnd_api.g_miss_char),
2585 NVL(hop.tax_reference, fnd_api.g_miss_char),
2586 NVL(hop.gsa_indicator_flag, fnd_api.g_miss_char),
2587 NVL(hop.jgzz_fiscal_code, fnd_api.g_miss_char),
2588 NVL(hop.analysis_fy, fnd_api.g_miss_char),
2589 NVL(hop.fiscal_yearend_month, fnd_api.g_miss_char),
2590 NVL(hop.curr_fy_potential_revenue, fnd_api.g_miss_num),
2591 NVL(hop.next_fy_potential_revenue, fnd_api.g_miss_num),
2592 NVL(hop.year_established, fnd_api.g_miss_num),
2593 NVL(hop.mission_statement, fnd_api.g_miss_char),
2594 NVL(hop.organization_type, fnd_api.g_miss_char),
2595 NVL(hop.business_scope, fnd_api.g_miss_char),
2596 NVL(hop.corporation_class, fnd_api.g_miss_char),
2597 NVL(hop.known_as, fnd_api.g_miss_char),
2598 NVL(hop.local_bus_iden_type, fnd_api.g_miss_char),
2599 NVL(hop.local_bus_identifier, fnd_api.g_miss_char),
2600 NVL(hop.pref_functional_currency, fnd_api.g_miss_char),
2601 NVL(hop.registration_type, fnd_api.g_miss_char),
2602 NVL(hop.total_employees_text, fnd_api.g_miss_char),
2603 NVL(hop.total_employees_ind, fnd_api.g_miss_char),
2604 NVL(hop.total_emp_est_ind, fnd_api.g_miss_char),
2605 NVL(hop.total_emp_min_ind, fnd_api.g_miss_char),
2606 NVL(hop.parent_sub_ind, fnd_api.g_miss_char),
2607 NVL(hop.incorp_year, fnd_api.g_miss_num),
2608 NVL(hop.content_source_type, fnd_api.g_miss_char),
2609 NVL(hop.content_source_number, fnd_api.g_miss_char),
2610 NVL(hop.effective_start_date, fnd_api.g_miss_date),
2611 NVL(hop.effective_end_date, fnd_api.g_miss_date),
2612 NVL(hop.sic_code_type, fnd_api.g_miss_char),
2613 NVL(hop.public_private_ownership_flag, fnd_api.g_miss_char),
2614 NVL(hop.local_activity_code_type, fnd_api.g_miss_char),
2615 NVL(hop.local_activity_code, fnd_api.g_miss_char),
2616 NVL(hop.emp_at_primary_adr, fnd_api.g_miss_char),
2617 NVL(hop.emp_at_primary_adr_text, fnd_api.g_miss_char),
2618 NVL(hop.emp_at_primary_adr_est_ind, fnd_api.g_miss_char),
2619 NVL(hop.emp_at_primary_adr_min_ind, fnd_api.g_miss_char),
2620 NVL(hop.internal_flag, fnd_api.g_miss_char),
2621 NVL(hop.high_credit, fnd_api.g_miss_num),
2622 NVL(hop.avg_high_credit, fnd_api.g_miss_num),
2623 NVL(hop.total_payments, fnd_api.g_miss_num),
2624 NVL(hop.known_as2, fnd_api.g_miss_char),
2625 NVL(hop.known_as3, fnd_api.g_miss_char),
2626 NVL(hop.known_as4, fnd_api.g_miss_char),
2627 NVL(hop.known_as5, fnd_api.g_miss_char),
2628 NVL(hop.credit_score_class, fnd_api.g_miss_num),
2629 NVL(hop.credit_score_natl_percentile, fnd_api.g_miss_num),
2630 NVL(hop.credit_score_incd_default, fnd_api.g_miss_num),
2631 NVL(hop.credit_score_age, fnd_api.g_miss_num),
2632 NVL(hop.credit_score_date, fnd_api.g_miss_date),
2633 NVL(hop.failure_score_class, fnd_api.g_miss_num),
2634 NVL(hop.failure_score_incd_default, fnd_api.g_miss_num),
2635 NVL(hop.failure_score_age, fnd_api.g_miss_num),
2636 NVL(hop.failure_score_date, fnd_api.g_miss_date),
2637 NVL(hop.failure_score_commentary2, fnd_api.g_miss_char),
2638 NVL(hop.failure_score_commentary3, fnd_api.g_miss_char),
2639 NVL(hop.failure_score_commentary4, fnd_api.g_miss_char),
2640 NVL(hop.failure_score_commentary5, fnd_api.g_miss_char),
2641 NVL(hop.failure_score_commentary6, fnd_api.g_miss_char),
2642 NVL(hop.failure_score_commentary7, fnd_api.g_miss_char),
2643 NVL(hop.failure_score_commentary8, fnd_api.g_miss_char),
2644 NVL(hop.failure_score_commentary9, fnd_api.g_miss_char),
2645 NVL(hop.failure_score_commentary10, fnd_api.g_miss_char),
2646 NVL(hop.credit_score_commentary2, fnd_api.g_miss_char),
2650 NVL(hop.credit_score_commentary6, fnd_api.g_miss_char),
2647 NVL(hop.credit_score_commentary3, fnd_api.g_miss_char),
2648 NVL(hop.credit_score_commentary4, fnd_api.g_miss_char),
2649 NVL(hop.credit_score_commentary5, fnd_api.g_miss_char),
2651 NVL(hop.credit_score_commentary7, fnd_api.g_miss_char),
2652 NVL(hop.credit_score_commentary8, fnd_api.g_miss_char),
2653 NVL(hop.credit_score_commentary9, fnd_api.g_miss_char),
2654 NVL(hop.credit_score_commentary10, fnd_api.g_miss_char),
2655 NVL(hop.maximum_credit_recommendation, fnd_api.g_miss_num),
2656 NVL(hop.maximum_credit_currency_code, fnd_api.g_miss_char),
2657 NVL(hop.displayed_duns_party_id, fnd_api.g_miss_num),
2658 NVL(hop.failure_score_natnl_percentile, fnd_api.g_miss_num),
2659 NVL(hop.duns_number_c, fnd_api.g_miss_char),
2660 NVL(hop.bank_or_branch_number, fnd_api.g_miss_char),
2661 NVL(hop.bank_code, fnd_api.g_miss_char),
2662 NVL(hop.branch_code, fnd_api.g_miss_char),
2663 NVL(hop.created_by_module, fnd_api.g_miss_char),
2664 NVL(hop.application_id, fnd_api.g_miss_num),
2665 NVL(hop.do_not_confuse_with, fnd_api.g_miss_char),
2666 NVL(hop.actual_content_source, fnd_api.g_miss_char),
2667 NVL(hop.home_country, fnd_api.g_miss_char)
2668 FROM hz_organization_profiles hop
2669 WHERE hop.organization_profile_id = x_organization_profile_id;
2670 BEGIN
2671 OPEN c_orgprof;
2672 FETCH c_orgprof
2673 INTO x_organization_profile_id,
2674 x_party_id,
2675 x_organization_name,
2676 x_attribute_category,
2677 x_attribute1,
2678 x_attribute2,
2679 x_attribute3,
2680 x_attribute4,
2681 x_attribute5,
2682 x_attribute6,
2683 x_attribute7,
2684 x_attribute8,
2685 x_attribute9,
2686 x_attribute10,
2687 x_attribute11,
2688 x_attribute12,
2689 x_attribute13,
2690 x_attribute14,
2691 x_attribute15,
2692 x_attribute16,
2693 x_attribute17,
2694 x_attribute18,
2695 x_attribute19,
2696 x_attribute20,
2697 x_enquiry_duns,
2698 x_ceo_name,
2699 x_ceo_title,
2700 x_principal_name,
2701 x_principal_title,
2702 x_legal_status,
2703 x_control_yr,
2704 x_employees_total,
2705 x_hq_branch_ind,
2706 x_branch_flag,
2707 x_oob_ind,
2708 x_line_of_business,
2709 x_cong_dist_code,
2710 x_sic_code,
2711 x_import_ind,
2712 x_export_ind,
2713 x_labor_surplus_ind,
2714 x_debarment_ind,
2715 x_minority_owned_ind,
2716 x_minority_owned_type,
2717 x_woman_owned_ind,
2718 x_disadv_8a_ind,
2719 x_small_bus_ind,
2720 x_rent_own_ind,
2721 x_debarments_count,
2722 x_debarments_date,
2723 x_failure_score,
2724 x_failure_score_override_code,
2725 x_failure_score_commentary,
2726 x_global_failure_score,
2727 x_db_rating,
2728 x_credit_score,
2729 x_credit_score_commentary,
2730 x_paydex_score,
2731 x_paydex_three_months_ago,
2732 x_paydex_norm,
2733 x_best_time_contact_begin,
2734 x_best_time_contact_end,
2735 x_organization_name_phonetic,
2736 x_tax_reference,
2737 x_gsa_indicator_flag,
2738 x_jgzz_fiscal_code,
2739 x_analysis_fy,
2740 x_fiscal_yearend_month,
2741 x_curr_fy_potential_revenue,
2742 x_next_fy_potential_revenue,
2743 x_year_established,
2744 x_mission_statement,
2745 x_organization_type,
2746 x_business_scope,
2747 x_corporation_class,
2748 x_known_as,
2749 x_local_bus_iden_type,
2750 x_local_bus_identifier,
2751 x_pref_functional_currency,
2752 x_registration_type,
2753 x_total_employees_text,
2754 x_total_employees_ind,
2755 x_total_emp_est_ind,
2756 x_total_emp_min_ind,
2757 x_parent_sub_ind,
2758 x_incorp_year,
2759 x_content_source_type,
2760 x_content_source_number,
2761 x_effective_start_date,
2762 x_effective_end_date,
2763 x_sic_code_type,
2764 x_public_private_ownership,
2765 x_local_activity_code_type,
2766 x_local_activity_code,
2767 x_emp_at_primary_adr,
2768 x_emp_at_primary_adr_text,
2769 x_emp_at_primary_adr_est_ind,
2770 x_emp_at_primary_adr_min_ind,
2771 x_internal_flag,
2772 x_high_credit,
2773 x_avg_high_credit,
2774 x_total_payments,
2775 x_known_as2,
2776 x_known_as3,
2777 x_known_as4,
2778 x_known_as5,
2779 x_credit_score_class,
2780 x_credit_score_natl_percentile,
2781 x_credit_score_incd_default,
2782 x_credit_score_age,
2783 x_credit_score_date,
2784 x_failure_score_class,
2785 x_failure_score_incd_default,
2786 x_failure_score_age,
2787 x_failure_score_date,
2788 x_failure_score_commentary2,
2789 x_failure_score_commentary3,
2790 x_failure_score_commentary4,
2791 x_failure_score_commentary5,
2795 x_failure_score_commentary9,
2792 x_failure_score_commentary6,
2793 x_failure_score_commentary7,
2794 x_failure_score_commentary8,
2796 x_failure_score_commentary10,
2797 x_credit_score_commentary2,
2798 x_credit_score_commentary3,
2799 x_credit_score_commentary4,
2800 x_credit_score_commentary5,
2801 x_credit_score_commentary6,
2802 x_credit_score_commentary7,
2803 x_credit_score_commentary8,
2804 x_credit_score_commentary9,
2805 x_credit_score_commentary10,
2806 x_maximum_credit_recomm,
2807 x_maximum_credit_currency_code,
2808 x_displayed_duns_party_id,
2809 x_failure_score_natnl_perc,
2810 x_duns_number_c,
2811 x_bank_or_branch_number,
2812 x_bank_code,
2813 x_branch_code,
2814 x_created_by_module,
2815 x_application_id,
2816 x_do_not_confuse_with,
2817 x_actual_content_source,
2818 x_home_country;
2819 IF c_orgprof%NOTFOUND THEN
2820 CLOSE c_orgprof;
2821 fnd_message.set_name('AR', 'HZ_API_NO_RECORD');
2822 fnd_message.set_token('RECORD', 'organization_rec');
2823 fnd_message.set_token('VALUE', TO_CHAR(x_organization_profile_id));
2824 fnd_msg_pub.add;
2825 RAISE fnd_api.g_exc_error;
2826 END IF;
2827 CLOSE c_orgprof;
2828 END select_row;
2829
2830 PROCEDURE delete_row (x_organization_profile_id IN NUMBER) IS
2831 BEGIN
2832
2833 DELETE FROM hz_organization_profiles hop
2834 WHERE hop.organization_profile_id = x_organization_profile_id;
2835
2836 IF (SQL%NOTFOUND) THEN
2837 RAISE NO_DATA_FOUND;
2838 END IF;
2839
2840 END Delete_Row;
2841
2842 END HZ_organization_profiles_pkg;