[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_HZ_COVER_API
Source
1 PACKAGE BODY AR_CMGT_HZ_COVER_API AS
2 /* $Header: ARCMHZCB.pls 120.5.12010000.2 2009/06/11 14:28:18 mraymond ship $ */
3
4 pg_wf_debug VARCHAR2(1) := ar_cmgt_util.get_wf_debug_flag;
5
6 PROCEDURE debug (
7 p_message_name IN VARCHAR2 ) IS
8 BEGIN
9 ar_cmgt_util.wf_debug ('AR_CMGT_HZ_COVER_API',p_message_name );
10 END;
11
12 --This is a local procedure used for debugging purpose and will be removed
13 --when the code is ready to be source controlled.
14 PROCEDURE dump_api_output_data(p_return_status VARCHAR2,
15 p_msg_count NUMBER,
16 p_msg_data VARCHAR2) IS
17 l_count NUMBER;
18 l_msg_data VARCHAR2(2000);
19 l_return_status VArCHAR2(1);
20 l_msg_count NUMBER;
21 BEGIN
22 l_return_status := p_return_status;
23 l_msg_count := p_msg_count;
24 l_msg_data := p_msg_data;
25
26
27
28 IF nvl(l_msg_count,0) > 1 Then
29 LOOP
30
31 IF nvl(l_count,0) < l_msg_count THEN
32
33 l_count := nvl(l_count,0) +1 ;
34 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
35 debug(l_msg_data);
36 ELSE
37 EXIT;
38 END IF;
39
40 END LOOP;
41 ELSE
42 debug(l_msg_data);
43 END IF;
44
45 END dump_api_output_data;
46
47 /*=======================================================================+
48 | Declare PUBLIC Data Types and Variables
49 +=======================================================================*/
50
51 /*=======================================================================+
52 | Declare PUBLIC Exceptions
53 +=======================================================================*/
54
55
56 /*========================================================================
57 | PUBLIC PROCEDURE
58 | update_organization()
59 | DESCRIPTION
60 |
61 | PSEUDO CODE/LOGIC
62 |
63 | PARAMETERS
64 | p_resource_id IN resource_id
65 |
66 | KNOWN ISSUES
67 |
68 | NOTES
69 |
70 | MODIFICATION HISTORY
71 | Date Author Description of Changes
72 |
73 *=======================================================================*/
74 PROCEDURE update_organization(p_party_id NUMBER, --15
75 p_year_established NUMBER,
76 p_employees_total NUMBER,
77 p_url VARCHAR2,
78 p_sic_code_type VARCHAR2,
79 p_sic_code VARCHAR2,
80 p_tax_reference VARCHAR2, --50
81 p_duns_number_c VARCHAR2
82 )
83 IS
84 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
85 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
86 l_party_object_version_number NUMBER;
87 l_profile_id NUMBER;
88 l_return_status VARCHAR2(1);
89 l_msg_count NUMBER;
90 l_msg_data VARCHAR2(2000);
91 l_count NUMBER;
92 BEGIN
93
94 --Initialize the party record structure
95 l_party_rec.party_id := p_party_id;
96 /*
97 l_party_rec.party_number := FND_API.G_MISS_CHAR;
98 l_party_rec.validated_flag := FND_API.G_MISS_CHAR;
99 l_party_rec.orig_system_reference := FND_API.G_MISS_CHAR;
100 l_party_rec.status := FND_API.G_MISS_CHAR;
101 l_party_rec.category_code := FND_API.G_MISS_CHAR;
102 l_party_rec.salutation := FND_API.G_MISS_CHAR;
103 l_party_rec.attribute_category := FND_API.G_MISS_CHAR;
104 l_party_rec.attribute1 := FND_API.G_MISS_CHAR;
105 l_party_rec.attribute2 := FND_API.G_MISS_CHAR;
106 l_party_rec.attribute3 := FND_API.G_MISS_CHAR;
107 l_party_rec.attribute4 := FND_API.G_MISS_CHAR;
108 l_party_rec.attribute5 := FND_API.G_MISS_CHAR;
109 l_party_rec.attribute6 := FND_API.G_MISS_CHAR;
110 l_party_rec.attribute7 := FND_API.G_MISS_CHAR;
111 l_party_rec.attribute8 := FND_API.G_MISS_CHAR;
112 l_party_rec.attribute9 := FND_API.G_MISS_CHAR;
113 l_party_rec.attribute1 := FND_API.G_MISS_CHAR;
114 l_party_rec.attribute11 := FND_API.G_MISS_CHAR;
115 l_party_rec.attribute12 := FND_API.G_MISS_CHAR;
116 l_party_rec.attribute13 := FND_API.G_MISS_CHAR;
117 l_party_rec.attribute14 := FND_API.G_MISS_CHAR;
118 l_party_rec.attribute15 := FND_API.G_MISS_CHAR;
119 l_party_rec.attribute16 := FND_API.G_MISS_CHAR;
120 l_party_rec.attribute17 := FND_API.G_MISS_CHAR;
121 l_party_rec.attribute18 := FND_API.G_MISS_CHAR;
122 l_party_rec.attribute19 := FND_API.G_MISS_CHAR;
123 l_party_rec.attribute20 := FND_API.G_MISS_CHAR;
124 l_party_rec.attribute21 := FND_API.G_MISS_CHAR;
125 l_party_rec.attribute22 := FND_API.G_MISS_CHAR;
126 l_party_rec.attribute23 := FND_API.G_MISS_CHAR;
127 l_party_rec.attribute24 := FND_API.G_MISS_CHAR;
128 */
129
130 --Initialize the organization record structure
131 -- l_organization_rec.organization_name := FND_API.G_MISS_CHAR;
132 l_organization_rec.duns_number_c := p_duns_number_c;
133 /*
134 l_organization_rec.enquiry_duns := FND_API.G_MISS_CHAR;
135 l_organization_rec.ceo_name := FND_API.G_MISS_CHAR;
136 l_organization_rec.ceo_title := FND_API.G_MISS_CHAR;
137 l_organization_rec.principal_name := FND_API.G_MISS_CHAR;
138 l_organization_rec.principal_title := FND_API.G_MISS_CHAR;
139 l_organization_rec.legal_status := FND_API.G_MISS_CHAR;
140 l_organization_rec.control_yr := FND_API.G_MISS_NUM;
141 */
142 l_organization_rec.employees_total := p_employees_total;
143 /*
144 l_organization_rec.hq_branch_ind := FND_API.G_MISS_CHAR;
145 l_organization_rec.branch_flag := FND_API.G_MISS_CHAR;
146 l_organization_rec.oob_ind := FND_API.G_MISS_CHAR;
147 l_organization_rec.line_of_business := FND_API.G_MISS_CHAR;
148 l_organization_rec.cong_dist_code := FND_API.G_MISS_CHAR;
149 */
150 l_organization_rec.sic_code := p_sic_code;
151 /*
152 l_organization_rec.import_ind := FND_API.G_MISS_CHAR;
153 l_organization_rec.export_ind := FND_API.G_MISS_CHAR;
154 l_organization_rec.labor_surplus_ind := FND_API.G_MISS_CHAR;
155 l_organization_rec.debarment_ind := FND_API.G_MISS_CHAR;
156 l_organization_rec.minority_owned_ind := FND_API.G_MISS_CHAR;
157 l_organization_rec.minority_owned_type := FND_API.G_MISS_CHAR;
158 l_organization_rec.woman_owned_ind := FND_API.G_MISS_CHAR;
159 l_organization_rec.disadv_8a_ind := FND_API.G_MISS_CHAR;
160 l_organization_rec.small_bus_ind := FND_API.G_MISS_CHAR;
161 l_organization_rec.rent_own_ind := FND_API.G_MISS_CHAR;
162 l_organization_rec.debarments_count := FND_API.G_MISS_NUM;
163 l_organization_rec.debarments_date := FND_API.G_MISS_DATE;
164 l_organization_rec.failure_score := FND_API.G_MISS_CHAR;
165 l_organization_rec.failure_score_natnl_percentile := FND_API.G_MISS_NUM;
166 l_organization_rec.failure_score_override_code := FND_API.G_MISS_CHAR;
167 l_organization_rec.failure_score_commentary := FND_API.G_MISS_CHAR;
168 l_organization_rec.global_failure_score := FND_API.G_MISS_CHAR;
169 l_organization_rec.db_rating := FND_API.G_MISS_CHAR;
170 l_organization_rec.credit_score := FND_API.G_MISS_CHAR;
171 l_organization_rec.credit_score_commentary := FND_API.G_MISS_CHAR;
172 l_organization_rec.paydex_score := FND_API.G_MISS_CHAR;
173 l_organization_rec.paydex_three_months_ago := FND_API.G_MISS_CHAR;
174 l_organization_rec.paydex_norm := FND_API.G_MISS_CHAR;
175 l_organization_rec.best_time_contact_begin := FND_API.G_MISS_DATE;
176 l_organization_rec.best_time_contact_end := FND_API.G_MISS_DATE;
177 l_organization_rec.organization_name_phonetic := FND_API.G_MISS_CHAR;
178 */
179 l_organization_rec.tax_reference := p_tax_reference;
180 /*
181 l_organization_rec.gsa_indicator_flag := FND_API.G_MISS_CHAR;
182 l_organization_rec.jgzz_fiscal_code := FND_API.G_MISS_CHAR;
183 l_organization_rec.analysis_fy := FND_API.G_MISS_CHAR;
184 l_organization_rec.fiscal_yearend_month := FND_API.G_MISS_CHAR;
185 l_organization_rec.curr_fy_potential_revenue := FND_API.G_MISS_NUM;
186 l_organization_rec.next_fy_potential_revenue := FND_API.G_MISS_NUM;
187 */
188 l_organization_rec.year_established := p_year_established;
189 /*
190 l_organization_rec.mission_statement := FND_API.G_MISS_CHAR;
191 l_organization_rec.organization_type := FND_API.G_MISS_CHAR;
192 l_organization_rec.business_scope := FND_API.G_MISS_CHAR;
193 l_organization_rec.corporation_class := FND_API.G_MISS_CHAR;
194 l_organization_rec.known_as := FND_API.G_MISS_CHAR;
195 l_organization_rec.known_as2 := FND_API.G_MISS_CHAR;
196 l_organization_rec.known_as3 := FND_API.G_MISS_CHAR;
197 l_organization_rec.known_as4 := FND_API.G_MISS_CHAR;
198 l_organization_rec.known_as5 := FND_API.G_MISS_CHAR;
199 l_organization_rec.local_bus_iden_type := FND_API.G_MISS_CHAR;
200 l_organization_rec.local_bus_identifier := FND_API.G_MISS_CHAR;
201 l_organization_rec.pref_functional_currency := FND_API.G_MISS_CHAR;
202 l_organization_rec.registration_type := FND_API.G_MISS_CHAR;
203 l_organization_rec.total_employees_text := FND_API.G_MISS_CHAR;
204 l_organization_rec.total_employees_ind := FND_API.G_MISS_CHAR;
205 l_organization_rec.total_emp_est_ind := FND_API.G_MISS_CHAR;
206 l_organization_rec.total_emp_min_ind := FND_API.G_MISS_CHAR;
207 l_organization_rec.parent_sub_ind := FND_API.G_MISS_CHAR;
208 l_organization_rec.incorp_year := FND_API.G_MISS_NUM;
209 */
210 l_organization_rec.sic_code_type := p_sic_code_type;
211 /*
212 l_organization_rec.public_private_ownership_flag := FND_API.G_MISS_CHAR;
213 l_organization_rec.internal_flag := FND_API.G_MISS_CHAR;
214 l_organization_rec.local_activity_code_type := FND_API.G_MISS_CHAR;
215 l_organization_rec.local_activity_code := FND_API.G_MISS_CHAR;
216 l_organization_rec.emp_at_primary_adr := FND_API.G_MISS_CHAR;
217 l_organization_rec.emp_at_primary_adr_text := FND_API.G_MISS_CHAR;
218 l_organization_rec.emp_at_primary_adr_est_ind := FND_API.G_MISS_CHAR;
219 l_organization_rec.emp_at_primary_adr_min_ind := FND_API.G_MISS_CHAR;
220 l_organization_rec.high_credit := FND_API.G_MISS_NUM;
221 l_organization_rec.avg_high_credit := FND_API.G_MISS_NUM;
222 l_organization_rec.total_payments := FND_API.G_MISS_NUM;
223 l_organization_rec.credit_score_class := FND_API.G_MISS_NUM;
224 l_organization_rec.credit_score_natl_percentile := FND_API.G_MISS_NUM;
225 l_organization_rec.credit_score_incd_default := FND_API.G_MISS_NUM;
226 l_organization_rec.credit_score_age := FND_API.G_MISS_NUM;
227 l_organization_rec.credit_score_date := FND_API.G_MISS_DATE;
228 l_organization_rec.credit_score_commentary2 := FND_API.G_MISS_CHAR;
229 l_organization_rec.credit_score_commentary3 := FND_API.G_MISS_CHAR;
230 l_organization_rec.credit_score_commentary4 := FND_API.G_MISS_CHAR;
231 l_organization_rec.credit_score_commentary5 := FND_API.G_MISS_CHAR;
232 l_organization_rec.credit_score_commentary6 := FND_API.G_MISS_CHAR;
233 l_organization_rec.credit_score_commentary7 := FND_API.G_MISS_CHAR;
234 l_organization_rec.credit_score_commentary8 := FND_API.G_MISS_CHAR;
235 l_organization_rec.credit_score_commentary9 := FND_API.G_MISS_CHAR;
236 l_organization_rec.credit_score_commentary10 := FND_API.G_MISS_CHAR;
237 l_organization_rec.failure_score_class := FND_API.G_MISS_NUM;
238 l_organization_rec.failure_score_incd_default := FND_API.G_MISS_NUM;
239 l_organization_rec.failure_score_age := FND_API.G_MISS_NUM;
240 l_organization_rec.failure_score_date := FND_API.G_MISS_DATE;
241 l_organization_rec.failure_score_commentary2 := FND_API.G_MISS_CHAR;
242 l_organization_rec.failure_score_commentary3 := FND_API.G_MISS_CHAR;
243 l_organization_rec.failure_score_commentary4 := FND_API.G_MISS_CHAR;
244 l_organization_rec.failure_score_commentary5 := FND_API.G_MISS_CHAR;
245 l_organization_rec.failure_score_commentary6 := FND_API.G_MISS_CHAR;
246 l_organization_rec.failure_score_commentary7 := FND_API.G_MISS_CHAR;
247 l_organization_rec.failure_score_commentary8 := FND_API.G_MISS_CHAR;
248 l_organization_rec.failure_score_commentary9 := FND_API.G_MISS_CHAR;
249 l_organization_rec.failure_score_commentary10 := FND_API.G_MISS_CHAR;
250 l_organization_rec.maximum_credit_recommendation := FND_API.G_MISS_NUM;
251 l_organization_rec.maximum_credit_currency_code := FND_API.G_MISS_CHAR;
252 l_organization_rec.displayed_duns_party_id := FND_API.G_MISS_NUM;
253 l_organization_rec.content_source_type := FND_API.G_MISS_CHAR;
254 l_organization_rec.content_source_number := FND_API.G_MISS_CHAR;
255 l_organization_rec.attribute_category := FND_API.G_MISS_CHAR;
256 l_organization_rec.attribute1 := FND_API.G_MISS_CHAR;
257 l_organization_rec.attribute2 := FND_API.G_MISS_CHAR;
258 l_organization_rec.attribute3 := FND_API.G_MISS_CHAR;
259 l_organization_rec.attribute4 := FND_API.G_MISS_CHAR;
260 l_organization_rec.attribute5 := FND_API.G_MISS_CHAR;
261 l_organization_rec.attribute6 := FND_API.G_MISS_CHAR;
262 l_organization_rec.attribute7 := FND_API.G_MISS_CHAR;
263 l_organization_rec.attribute8 := FND_API.G_MISS_CHAR;
264 l_organization_rec.attribute9 := FND_API.G_MISS_CHAR;
265 l_organization_rec.attribute10 := FND_API.G_MISS_CHAR;
266 l_organization_rec.attribute11 := FND_API.G_MISS_CHAR;
267 l_organization_rec.attribute12 := FND_API.G_MISS_CHAR;
268 l_organization_rec.attribute13 := FND_API.G_MISS_CHAR;
269 l_organization_rec.attribute14 := FND_API.G_MISS_CHAR;
270 l_organization_rec.attribute15 := FND_API.G_MISS_CHAR;
271 l_organization_rec.attribute16 := FND_API.G_MISS_CHAR;
272 l_organization_rec.attribute17 := FND_API.G_MISS_CHAR;
273 l_organization_rec.attribute18 := FND_API.G_MISS_CHAR;
274 l_organization_rec.attribute19 := FND_API.G_MISS_CHAR;
275 l_organization_rec.attribute20 := FND_API.G_MISS_CHAR;
279 l_organization_rec.actual_content_source := FND_API.G_MISS_CHAR;
276 l_organization_rec.created_by_module := FND_API.G_MISS_CHAR;
277 l_organization_rec.application_id := FND_API.G_MISS_NUM;
278 l_organization_rec.do_not_confuse_with := FND_API.G_MISS_CHAR;
280 */
281 l_organization_rec.party_rec := l_party_rec;
282
283 hz_party_v2pub.update_organization(
284 p_organization_rec => l_organization_rec,
285 p_party_object_version_number => l_party_object_version_number,
286 x_profile_id => l_profile_id,
287 x_return_status => l_return_status,
288 x_msg_count => l_msg_count,
289 x_msg_data => l_msg_data);
290
291 dump_api_output_data( l_return_status,
292 l_msg_count,
293 l_msg_data);
294 END update_organization;
295
296 PROCEDURE create_party_profile(p_party_id IN NUMBER,
297 p_return_status OUT NOCOPY VARCHAR2)
298 IS
299 l_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
300 l_cust_profile_id NUMBER;
301 l_return_status VARCHAR2(1);
302 l_msg_count NUMBER;
303 l_msg_data VARCHAR2(2000);
304 /* 7233461 - fetch cust_acct level first, and only consider
305 active sites/profiles */
306 CURSOR find_profile_class(p_party_id IN NUMBER)
307 IS
308 select cp.profile_class_id
309 from hz_cust_accounts ca,
310 hz_customer_profiles cp
311 where ca.party_id = p_party_id
312 and ca.cust_account_id = cp.cust_account_id
313 and ca.status = 'A'
314 and cp.status = 'A'
315 order by cp.site_use_id desc;
316
317
318 l_profile_class_id NUMBER;
319 BEGIN
320 IF pg_wf_debug = 'Y'
321 THEN
322 debug('ar_cmgt_hz_cover_api.create_party_profile()+');
323 debug(' p_party_id = ' || p_party_id);
324 END IF;
325
326 /* Check if an account exists for the given party and if that
327 particular account has a profile record.
328 */
329 OPEN find_profile_class(p_party_id);
330
331 FETCH find_profile_class INTO l_profile_class_id;
332
333 CLOSE find_profile_class;
334
335
336 /*If we have not been able to derive the profile class from the accounts beneath
337 the party then we will create the party profile record with the 'DEFAULT'
338 profile class.
339 */
340 IF l_profile_class_id IS NULL THEN
341 l_profile_class_id := 0; --0 is the DEFAULT profile class id
342 IF pg_wf_debug = 'Y'
343 THEN
344 debug(' no cust_profile records found. profile_class_id set to zero');
345 END IF;
346 END IF;
347
348 l_profile_rec.party_id := p_party_id;
349 l_profile_rec.profile_class_id := l_profile_class_id;
350 l_profile_rec.created_by_module := 'OCM';
351 l_profile_rec.application_id := 222;
352 l_profile_rec.credit_checking := 'Y';
353 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
354 p_init_msg_list => FND_API.G_FALSE,
355 p_customer_profile_rec => l_profile_rec,
356 p_create_profile_amt => FND_API.G_TRUE,
357 x_cust_account_profile_id => l_cust_profile_id,
358 x_return_status => l_return_status,
359 x_msg_count => l_msg_count,
360 x_msg_data => l_msg_data );
361
362 /* 7272415 - Raise WF error here if API fails */
363 p_return_status := l_return_status;
364 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
365 THEN
366 IF pg_wf_debug = 'Y'
367 THEN
368 debug('Error in HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile');
369 dump_api_output_data(l_return_status,
370 l_msg_count,
371 l_msg_data);
372 END IF;
373 END IF;
374
375 IF pg_wf_debug = 'Y'
376 THEN
377 debug(' assigned profile_id = ' || l_cust_profile_id);
378 debug(' assigned profile_class_id = ' || l_profile_class_id);
379 debug('ar_cmgt_hz_cover_api.create_party_profile()-');
380 END IF;
381 END create_party_profile;
382
383 END AR_CMGT_HZ_COVER_API;