[Home] [Help]
PACKAGE BODY: APPS.ASO_CHECK_TCA_PVT
Source
1 PACKAGE BODY ASO_CHECK_TCA_PVT as
2 /* $Header: asovctcb.pls 120.13 2006/09/07 18:13:10 bmishra ship $ */
3 -- Start of Comments
4 -- Package name : ASO_CHECK_TCA_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_CHECK_TCA_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovctcb.pls';
13
14 PROCEDURE check_tca(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
17 P_Qte_Rec IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Header_Rec_Type,
18 P_Header_Shipment_Tbl IN OUT NOCOPY ASO_QUOTE_PUB.Shipment_Tbl_Type,
19 P_Operation_Code IN VARCHAR2 := FND_API.G_MISS_CHAR,
20 p_application_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
21 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
23 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
24 )
25 IS
26
27 CURSOR C_Get_Party_From_Acct(acct_id NUMBER) IS
28 SELECT party_id
29 FROM HZ_CUST_ACCOUNTS
30 WHERE cust_account_id = acct_id
31 AND status = 'A'
32 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
33
34 CURSOR C_Party_Type(pty_id NUMBER) IS
35 SELECT party_type
36 FROM HZ_PARTIES
37 WHERE party_id = pty_id;
38
39 CURSOR C_Get_Cust_Party(pty_id NUMBER) IS
40 SELECT object_id
41 FROM HZ_RELATIONSHIPS
42 WHERE party_id = pty_id
43 AND object_type = 'ORGANIZATION'
44 AND object_table_name = 'HZ_PARTIES';
45
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2(45) := 'Check_tca';
48 l_org_contact NUMBER;
49 l_org_contact_party_id NUMBER;
50 l_sold_to_contact_id NUMBER;
51 lx_cust_account_id NUMBER;
52 l_party_type VARCHAR2(30);
53
54 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
55 l_shipment_rec ASO_QUOTE_PUB.shipment_rec_type := ASO_QUOTE_PUB.G_MISS_SHIPMENT_REC;
56
57 BEGIN
58
59 SAVEPOINT CHECK_TCA_PVT;
60
61 IF FND_API.to_boolean(p_init_msg_list) THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64
65 IF NOT FND_API.compatible_api_call(
66 l_api_version,
67 p_api_version,
68 l_api_name,
69 g_pkg_name
70 ) THEN
71 RAISE FND_API.g_exc_unexpected_error;
72 END IF;
73
74 x_return_status := FND_API.g_ret_sts_success;
75
76 IF aso_debug_pub.g_debug_flag = 'Y' THEN
77 aso_debug_pub.add('Check_Tca: begin', 1, 'N');
78 aso_debug_pub.add('Check_Tca: p_qte_rec.party_id: '||p_qte_rec.party_id, 1, 'N');
79 aso_debug_pub.add('Check_Tca: p_qte_rec.cust_party_id: '||p_qte_rec.cust_party_id, 1, 'N');
80 aso_debug_pub.add('Check_Tca: p_qte_rec.cust_account_id: '||p_qte_rec.cust_account_id, 1, 'N');
81 END IF;
82
83 IF (p_application_type_code = 'QUOTING HTML' AND p_operation_code = 'UPDATE') THEN
84 l_qte_header_rec := ASO_UTILITY_PVT.query_header_row (p_qte_rec.quote_header_id);
85
86 IF p_qte_rec.party_id <> FND_API.G_MISS_NUM OR
87 p_qte_rec.cust_party_id <> FND_API.G_MISS_NUM OR
88 p_qte_rec.cust_account_id <> FND_API.G_MISS_NUM OR
89 p_qte_rec.sold_to_party_site_id <> FND_API.G_MISS_NUM AND
90 p_qte_rec.org_contact_id <> FND_API.G_MISS_NUM THEN
91
92 IF p_qte_rec.party_id = FND_API.G_MISS_NUM THEN
93 p_qte_rec.party_id := l_qte_header_rec.party_id;
94 END IF;
95 IF p_qte_rec.cust_party_id = FND_API.G_MISS_NUM THEN
96 p_qte_rec.cust_party_id := l_qte_header_rec.cust_party_id;
97 END IF;
98 IF p_qte_rec.cust_account_id = FND_API.G_MISS_NUM THEN
99 p_qte_rec.cust_account_id := l_qte_header_rec.cust_account_id;
100 END IF;
101 IF p_qte_rec.sold_to_party_site_id = FND_API.G_MISS_NUM THEN
102 p_qte_rec.sold_to_party_site_id := l_qte_header_rec.sold_to_party_site_id;
103 END IF;
104
105 IF p_qte_rec.org_contact_id = FND_API.G_MISS_NUM THEN
106 p_qte_rec.org_contact_id := l_qte_header_rec.org_contact_id;
107 END IF;
108
109 END IF;
110
111 IF p_qte_rec.invoice_to_party_id <> FND_API.G_MISS_NUM OR
112 p_qte_rec.invoice_to_cust_party_id <> FND_API.G_MISS_NUM OR
113 p_qte_rec.invoice_to_cust_account_id <> FND_API.G_MISS_NUM OR
114 p_qte_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM THEN
115
116 IF p_qte_rec.invoice_to_party_id = FND_API.G_MISS_NUM THEN
117 p_qte_rec.invoice_to_party_id := l_qte_header_rec.invoice_to_party_id;
118 END IF;
119 IF p_qte_rec.invoice_to_cust_party_id = FND_API.G_MISS_NUM THEN
120 p_qte_rec.invoice_to_cust_party_id := l_qte_header_rec.invoice_to_cust_party_id;
121 END IF;
122 IF p_qte_rec.invoice_to_cust_account_id = FND_API.G_MISS_NUM THEN
123 p_qte_rec.invoice_to_cust_account_id := l_qte_header_rec.invoice_to_cust_account_id;
124 END IF;
125 IF p_qte_rec.invoice_to_party_site_id = FND_API.G_MISS_NUM THEN
126 p_qte_rec.invoice_to_party_site_id := l_qte_header_rec.invoice_to_party_site_id;
127 END IF;
128 END IF;
129
130 IF p_qte_rec.End_Customer_party_id <> FND_API.G_MISS_NUM OR
131 p_qte_rec.End_Customer_cust_party_id <> FND_API.G_MISS_NUM OR
132 p_qte_rec.End_Customer_cust_account_id <> FND_API.G_MISS_NUM OR
133 p_qte_rec.End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
134
135 IF p_qte_rec.End_Customer_party_id = FND_API.G_MISS_NUM THEN
136 p_qte_rec.End_Customer_party_id := l_qte_header_rec.End_Customer_party_id;
137 END IF;
138 IF p_qte_rec.End_Customer_cust_party_id = FND_API.G_MISS_NUM THEN
139 p_qte_rec.End_Customer_cust_party_id := l_qte_header_rec.End_Customer_cust_party_id;
140 END IF;
141 IF p_qte_rec.End_Customer_cust_account_id = FND_API.G_MISS_NUM THEN
142 p_qte_rec.End_Customer_cust_account_id := l_qte_header_rec.End_Customer_cust_account_id;
143 END IF;
144 IF p_qte_rec.End_Customer_party_site_id = FND_API.G_MISS_NUM THEN
145 p_qte_rec.End_Customer_party_site_id := l_qte_header_rec.End_Customer_party_site_id;
146 END IF;
147 END IF;
148
149 IF P_Header_Shipment_Tbl.count > 0 THEN
150 IF P_Header_Shipment_Tbl(1).operation_code = 'UPDATE' AND
151 (P_Header_Shipment_Tbl(1).shipment_id IS NOT NULL AND P_Header_Shipment_Tbl(1).shipment_id <> FND_API.G_MISS_NUM) THEN
152 IF P_Header_Shipment_Tbl(1).ship_to_party_id <> FND_API.G_MISS_NUM OR
153 P_Header_Shipment_Tbl(1).ship_to_cust_party_id <> FND_API.G_MISS_NUM OR
154 P_Header_Shipment_Tbl(1).ship_to_cust_account_id <> FND_API.G_MISS_NUM OR
155 P_Header_Shipment_Tbl(1).ship_to_party_site_id <> FND_API.G_MISS_NUM THEN
156
157 l_shipment_rec := ASO_UTILITY_PVT.query_shipment_row (P_Header_Shipment_Tbl(1).shipment_id);
158 IF P_Header_Shipment_Tbl(1).ship_to_cust_party_id = FND_API.G_MISS_NUM THEN
159 P_Header_Shipment_Tbl(1).ship_to_cust_party_id := l_shipment_rec.ship_to_cust_party_id;
160 END IF;
161 IF P_Header_Shipment_Tbl(1).ship_to_cust_account_id = FND_API.G_MISS_NUM THEN
162 P_Header_Shipment_Tbl(1).ship_to_cust_account_id := l_shipment_rec.ship_to_cust_account_id;
163 END IF;
164 IF P_Header_Shipment_Tbl(1).ship_to_party_id = FND_API.G_MISS_NUM THEN
165 P_Header_Shipment_Tbl(1).ship_to_party_id := l_shipment_rec.ship_to_party_id;
166 END IF;
167 IF P_Header_Shipment_Tbl(1).ship_to_party_site_id = FND_API.G_MISS_NUM THEN
168 P_Header_Shipment_Tbl(1).ship_to_party_site_id := l_shipment_rec.ship_to_party_site_id;
169 END IF;
170 END IF;
171 END IF;
172 END IF;
173
174 END IF; -- UPDATE
175
176 IF p_qte_rec.party_id IS NULL OR p_qte_rec.party_id = FND_API.G_MISS_NUM THEN
177
178 IF p_qte_rec.cust_party_id IS NOT NULL AND p_qte_rec.cust_party_id <> FND_API.G_MISS_NUM THEN
179
180 p_qte_rec.party_id := p_qte_rec.cust_party_id;
181
182 ELSIF p_qte_rec.cust_account_id IS NOT NULL AND p_qte_rec.cust_account_id <> FND_API.G_MISS_NUM THEN
183
184 OPEN C_Get_Party_From_Acct(p_qte_rec.cust_account_id);
185 FETCH C_Get_Party_From_Acct INTO p_qte_rec.party_id;
186 IF C_Get_Party_From_Acct%NOTFOUND THEN
187 x_Return_Status := FND_API.G_RET_STS_ERROR;
188 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
189 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
190 FND_MESSAGE.Set_Token('COLUMN', 'CUST_ACCOUNT_ID', FALSE);
191 FND_MESSAGE.Set_Token('VALUE', to_char(p_qte_rec.cust_account_id), FALSE);
192 FND_MSG_PUB.ADD;
193 END IF;
194 CLOSE C_Get_Party_From_Acct;
195 raise FND_API.G_EXC_ERROR;
196 END IF;
197 CLOSE C_Get_Party_From_Acct;
198
199 END IF;
200
201 END IF; -- party_id is null
202
203 IF aso_debug_pub.g_debug_flag = 'Y' THEN
204 aso_debug_pub.add('Check_Tca: p_qte_rec.party_id: '||p_qte_rec.party_id, 1, 'N');
205 END IF;
206
207 IF p_qte_rec.party_id IS NOT NULL AND p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
208
209 IF p_qte_rec.cust_party_id IS NULL OR p_qte_rec.cust_party_id = FND_API.G_MISS_NUM THEN
210
211 OPEN C_Party_Type(p_qte_rec.party_id);
212 FETCH C_Party_Type INTO l_party_type;
213 CLOSE C_Party_Type;
214
215 IF aso_debug_pub.g_debug_flag = 'Y' THEN
216 aso_debug_pub.add('Check_Tca: l_party_type: '||l_party_type, 1, 'N');
217 END IF;
218
219 IF l_party_type = 'PERSON' OR l_party_type = 'ORGANIZATION' THEN
220
221 p_qte_rec.cust_party_id := p_qte_rec.party_id;
222
223 ELSIF l_party_type = 'PARTY_RELATIONSHIP' THEN
224
225 OPEN C_Get_Cust_Party(p_qte_rec.party_id);
226 FETCH C_Get_Cust_Party INTO p_qte_rec.cust_party_id;
227 CLOSE C_Get_Cust_Party;
228
229 END IF;
230
231 END IF; -- cust_party_id
232
233 IF aso_debug_pub.g_debug_flag = 'Y' THEN
234 aso_debug_pub.add('Check_Tca: p_qte_rec.cust_party_id: '||p_qte_rec.cust_party_id, 1, 'N');
235 END IF;
236
237 IF p_qte_rec.cust_account_id IS NULL OR p_qte_rec.cust_account_id = FND_API.G_MISS_NUM THEN
238
239 IF aso_debug_pub.g_debug_flag = 'Y' THEN
240 aso_debug_pub.add('Check_Tca: before customer account:p_qte_rec.party_id '||p_qte_rec.party_id, 1, 'N');
241 END IF;
242
243 Customer_Account(
244 p_api_version => 1.0,
245 p_Party_Id => p_qte_rec.cust_party_id,
246 p_calling_api_flag => 0,
247 x_Cust_Acct_Id => p_qte_rec.cust_account_id,
248 x_return_status => x_return_status,
249 x_msg_count => x_msg_count,
250 x_msg_data => x_msg_data);
251
252 IF aso_debug_pub.g_debug_flag = 'Y' THEN
253 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '||x_Return_Status, 1, 'N');
254 END IF;
255 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
256 raise FND_API.G_EXC_ERROR;
257 END IF;
258
259 END IF; -- cust_account_id
260
261 IF aso_debug_pub.g_debug_flag = 'Y' THEN
262 aso_debug_pub.add('Check_Tca: p_qte_rec.cust_account_id: '||p_qte_rec.cust_account_id, 1, 'N');
263 END IF;
264
265 END IF; -- party_id is not null
266
267 lx_cust_account_id := p_qte_rec.cust_account_id;
268
269 IF lx_cust_account_id IS NOT NULL AND lx_cust_account_id <> FND_API.G_MISS_NUM THEN
270 IF aso_debug_pub.g_debug_flag = 'Y' THEN
271 aso_debug_pub.add('before org contact', 1, 'N');
272 END IF;
273 IF p_qte_rec.party_id is not null and
274 p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
275
276 IF aso_debug_pub.g_debug_flag = 'Y' THEN
277 aso_debug_pub.add('before org contact:p_qte_rec.org_contact_id: '||p_qte_rec.org_contact_id, 1, 'N');
278 END IF;
279 IF p_qte_rec.org_contact_id is NULL OR
280 p_qte_rec.org_contact_id = FND_API.G_MISS_NUM THEN
281 ASO_MAP_QUOTE_ORDER_INT.get_org_contact(p_party_id => p_qte_rec.party_id,
282 x_org_contact => l_org_contact);
283 IF aso_debug_pub.g_debug_flag = 'Y' THEN
284 aso_debug_pub.add('after org contact:l_org_contact: '||l_org_contact, 1, 'N');
285 END IF;
286 ELSE
287 l_org_contact := p_qte_rec.org_contact_id;
288 END IF;
289 IF l_org_contact is not NULL AND l_org_contact <> FND_API.G_MISS_NUM THEN
290 IF aso_debug_pub.g_debug_flag = 'Y' THEN
291 aso_debug_pub.add('deriving org_contact_role:l_org_contact: ' || l_org_contact,1, 'N');
292 END IF;
293 ASO_MAP_QUOTE_ORDER_INT.get_org_contact_role(
294 p_Org_Contact_Id => l_org_contact
295 ,p_Cust_account_id => lx_cust_account_id
296 ,x_return_status => x_return_status
297 ,x_party_id => l_org_contact_party_id
298 ,x_cust_account_role_id => l_sold_to_contact_id
299 );
300 IF aso_debug_pub.g_debug_flag = 'Y' THEN
301 aso_debug_pub.add('after get org contact. l_org_contact_party_id = ' || l_org_contact_party_id ,1, 'N');
302 aso_debug_pub.add('after get org contact. sold_to_contact_id = ' || l_sold_to_contact_id ,1, 'N');
303 END IF;
304 if p_qte_rec.org_contact_id is not null and p_qte_rec.org_contact_id <> FND_API.G_MISS_NUM then
305 p_qte_rec.party_id := l_org_contact_party_id;
306 end if;
307 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
308 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
309 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_ORG_CON_ACT_CRS');
310 -- FND_MESSAGE.Set_Token('ID', to_char(p_qte_rec.org_contact_id),FALSE);
311 FND_MSG_PUB.ADD;
312 END IF;
313 raise FND_API.G_EXC_ERROR;
314 END IF;
315 IF l_sold_to_contact_id is NULL OR
316 l_sold_to_contact_id = FND_API.G_MISS_NUM THEN
317 IF aso_debug_pub.g_debug_flag = 'Y' THEN
318 aso_debug_pub.add('calling create contact role for org contact ',1, 'N');
319 END IF;
320 ASO_PARTY_INT.Create_Contact_Role (
321 p_api_version => 1.0
322 ,p_party_id =>l_org_contact_party_id
323 ,p_Cust_account_id => lx_cust_account_id
324 ,x_return_status => x_return_status
325 ,x_msg_count => x_msg_count
326 ,x_msg_data => x_msg_data
327 ,x_cust_account_role_id => l_sold_to_contact_id
328 );
329 IF aso_debug_pub.g_debug_flag = 'Y' THEN
330 aso_debug_pub.add('after create contact role. sold_to_contact_id = ' || l_sold_to_contact_id ,1, 'N');
331 aso_debug_pub.add('after create contact role. x_return_status = ' || x_return_status ,1, 'N');
332 END IF;
333 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
334 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
335 FND_MESSAGE.Set_Name('ASO', 'ASO_ORG_CONTACT');
339 raise FND_API.G_EXC_ERROR;
336 FND_MESSAGE.Set_Token('ID', to_char(p_qte_rec.party_id), FALSE);
337 FND_MSG_PUB.ADD;
338 END IF;
340 END IF;
341 END IF;
342
343 END IF;
344
345 END IF;
346
347 END IF; -- lx_cust_account not null
348
349 IF aso_debug_pub.g_debug_flag = 'Y' THEN
350 aso_debug_pub.add('before check hdr info ',1, 'N');
351 END IF;
352 check_header_account_info(
353 p_api_version => 1.0,
354 p_init_msg_list => p_init_msg_list,
355 p_cust_account_id => lx_cust_account_id,
356 p_qte_rec => p_qte_rec,
357 p_header_shipment_tbl => p_header_shipment_tbl,
358 x_return_status => x_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data );
361
362 IF aso_debug_pub.g_debug_flag = 'Y' THEN
363 aso_debug_pub.add('after check hdr info:x_return_status: '||x_return_status,1, 'N');
364 END IF;
365 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
366 raise FND_API.G_EXC_ERROR;
367 END IF;
368
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373
374 EXCEPTION
375 WHEN FND_API.G_EXC_ERROR THEN
376 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
377 P_API_NAME => L_API_NAME
378 ,P_PKG_NAME => G_PKG_NAME
379 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
380 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
381 ,P_SQLCODE => SQLCODE
382 ,P_SQLERRM => SQLERRM
383 ,X_MSG_COUNT => X_MSG_COUNT
384 ,X_MSG_DATA => X_MSG_DATA
385 ,X_RETURN_STATUS => X_RETURN_STATUS);
386
387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
388 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
389 P_API_NAME => L_API_NAME
390 ,P_PKG_NAME => G_PKG_NAME
391 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
392 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
393 ,P_SQLCODE => SQLCODE
394 ,P_SQLERRM => SQLERRM
395 ,X_MSG_COUNT => X_MSG_COUNT
396 ,X_MSG_DATA => X_MSG_DATA
397 ,X_RETURN_STATUS => X_RETURN_STATUS);
398
399 WHEN OTHERS THEN
400 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
401 P_API_NAME => L_API_NAME
402 ,P_PKG_NAME => G_PKG_NAME
403 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
404 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
405 ,P_SQLCODE => SQLCODE
406 ,P_SQLERRM => SQLERRM
407 ,X_MSG_COUNT => X_MSG_COUNT
408 ,X_MSG_DATA => X_MSG_DATA
409 ,X_RETURN_STATUS => X_RETURN_STATUS);
410
411 END check_tca;
412
413
414 PROCEDURE check_header_account_info(
415 p_api_version IN NUMBER,
416 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
417 p_cust_account_id IN NUMBER,
418 P_Qte_Rec IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Header_Rec_Type,
419 P_Header_Shipment_Tbl IN OUT NOCOPY ASO_QUOTE_PUB.Shipment_Tbl_Type,
420 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
421 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
422 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
423 IS
424
425 CURSOR get_cust_acct_site_id(l_site_use_id number) IS
426 select cust_acct_site_id from hz_cust_site_uses
427 where site_use_id = l_site_use_id;
428
429 CURSOR C_Site_Use(l_sold_to_party_site NUMBER) IS
430 SELECT party_site_use_id
431 FROM hz_party_site_uses
432 WHERE party_site_id = l_sold_to_party_site
433 AND site_use_type = 'SOLD_TO';
434
435 CURSOR c_get_cust_account_id IS
436 SELECT invoice_to_cust_account_id
437 FROM aso_quote_headers_all
438 WHERE Quote_header_id = P_Qte_Rec.quote_header_id;
439
440
441 l_api_version CONSTANT NUMBER := 1.0;
442 l_api_name CONSTANT VARCHAR2(45) := 'Check_Header_Account_Info';
443 l_site_use_id NUMBER;
444 l_invoice_cust_account_id NUMBER;
445 l_End_cust_account_id NUMBER;
446 l_invoice_contact_party_id NUMBER;
447 l_invoice_to_contact_id NUMBER;
448 l_End_Customer_contact_id NUMBER;
449 l_ship_cust_account_id NUMBER;
450 l_invoice_cust_account_site NUMBER;
451 l_ship_contact_party_id NUMBER;
452 l_ship_to_contact_id NUMBER;
453 l_ship_cust_account_site NUMBER;
454 l_invoice_to_org_id NUMBER;
455 l_End_Customer_org_id NUMBER;
456 l_ship_to_org_id NUMBER;
457 l_invoice_to_party_id NUMBER;
458 l_ship_to_party_id NUMBER;
459
460 l_party_site_use_id NUMBER;
461 l_inv_cust_acct_site_id NUMBER;
462 l_end_cust_acct_site_id NUMBER;
463 l_shp_cust_acct_site_id NUMBER;
464
465 BEGIN
466
467 SAVEPOINT CHECK_HEADER_ACCOUNT_INFO_PVT;
471 END IF;
468
469 IF FND_API.to_boolean(p_init_msg_list) THEN
470 FND_MSG_PUB.initialize;
472
473 IF NOT FND_API.compatible_api_call(
474 l_api_version,
475 p_api_version,
476 l_api_name,
477 g_pkg_name
478 ) THEN
479 RAISE FND_API.g_exc_unexpected_error;
480 END IF;
481
482 x_return_status := FND_API.g_ret_sts_success;
483
484 IF aso_debug_pub.g_debug_flag = 'Y' THEN
485 aso_debug_pub.add('Check_Hdr_Acct: begin ', 1, 'Y');
486 aso_debug_pub.add('Check_Hdr_Acct: before sold to party site id '|| p_qte_rec.sold_to_party_site_id, 1, 'N');
487 aso_debug_pub.add('Check_Hdr_Acct: p_qte_rec.invoice_to_cust_account_id: '|| p_qte_rec.invoice_to_cust_account_id, 1, 'N');
488 aso_debug_pub.add('Check_Hdr_Acct: p_qte_rec.invoice_to_cust_party_id: '|| p_qte_rec.invoice_to_cust_party_id, 1, 'N');
489 aso_debug_pub.add('Check_Hdr_Acct: p_qte_rec.invoice_to_party_site_id: '|| p_qte_rec.invoice_to_party_site_id, 1, 'N');
490 END IF;
491
492 IF p_qte_rec.sold_to_party_site_id is not NULL
493 AND p_qte_rec.sold_to_party_site_id <> FND_API.G_MISS_NUM THEN
494
495 OPEN C_Site_Use(p_qte_rec.sold_to_party_site_id);
496 FETCH C_Site_Use INTO l_party_site_use_id;
497 IF aso_debug_pub.g_debug_flag = 'Y' THEN
498 aso_debug_pub.add('chk_hdr:party_site_use_id = ' || l_party_site_use_id,1,'N');
499 END IF;
500 IF C_Site_Use%NOTFOUND THEN
501 ASO_PARTY_INT.Create_Party_Site_Use(
502 p_api_version => 1.0,
503 p_party_site_id => p_qte_rec.sold_to_party_site_id,
504 p_party_site_use_type => 'SOLD_TO',
505 x_party_site_use_id => l_party_site_use_id,
506 x_return_status => x_return_status,
507 x_msg_count => x_msg_count,
508 x_msg_data => x_msg_data);
509
510 IF aso_debug_pub.g_debug_flag = 'Y' THEN
511 aso_debug_pub.add('chk_hdr:party_site_use_id after sold_to = ' || l_party_site_use_id,1,'N');
512 END IF;
513 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
514 raise FND_API.G_EXC_ERROR;
515 END IF;
516
517 END IF;
518
519 END IF; -- sold_to_party_site
520
521 ASO_CHECK_TCA_PVT.Populate_Acct_Party (
522 p_hdr_cust_acct_id => p_qte_rec.cust_account_id,
523 p_hdr_party_id => p_qte_rec.cust_party_id,
524 p_party_site_id => p_qte_rec.invoice_to_party_site_id,
525 p_cust_account_id => p_qte_rec.invoice_to_cust_account_id,
526 p_cust_party_id => p_qte_rec.invoice_to_cust_party_id,
527 x_return_status => x_return_status,
528 x_msg_count => x_msg_count,
529 x_msg_data => x_msg_data );
530
531 IF aso_debug_pub.g_debug_flag = 'Y' THEN
532 aso_debug_pub.add('chk_hdr:after populate_acct_party: ' || x_return_status,1,'N');
533 END IF;
534 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
535 raise FND_API.G_EXC_ERROR;
536 END IF;
537
538 IF (p_qte_rec.invoice_to_cust_party_id is NOT NULL AND
539 p_qte_rec.invoice_to_cust_party_id <> FND_API.G_MISS_NUM) AND
540 (p_qte_rec.invoice_to_cust_account_id IS NULL OR
541 p_qte_rec.invoice_to_cust_account_id = FND_API.G_MISS_NUM) THEN
542
543
544 IF aso_debug_pub.g_debug_flag = 'Y' THEN
545 aso_debug_pub.add('Check_Tca: before customer account:p_qte_rec.invoice_to_cust_party_id '
546 ||p_qte_rec.invoice_to_cust_party_id, 1, 'N');
547 END IF;
548 Customer_Account(
549 p_api_version => 1.0,
550 p_Party_Id => p_qte_rec.invoice_to_cust_party_id,
551 p_calling_api_flag => 0,
552 x_Cust_Acct_Id => p_qte_rec.invoice_to_cust_account_id,
553 x_return_status => x_return_status,
554 x_msg_count => x_msg_count,
555 x_msg_data => x_msg_data);
556
557 IF aso_debug_pub.g_debug_flag = 'Y' THEN
558 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '
559 ||x_Return_Status, 1, 'N');
560 END IF;
561 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
562 raise FND_API.G_EXC_ERROR;
563 END IF;
564
565 END IF; -- invoice_to_cust_account_id
566
567 IF aso_debug_pub.g_debug_flag = 'Y' THEN
568 aso_debug_pub.add('Check_Tca: p_qte_rec.invoice_to_cust_account_id: '
569 ||p_qte_rec.invoice_to_cust_account_id, 1, 'N');
570 END IF;
571
572
573 IF p_qte_rec.invoice_to_cust_account_id is not NULL AND
574 p_qte_rec.invoice_to_cust_account_id <> FND_API.G_MISS_NUM THEN
575 l_invoice_cust_account_id := p_qte_rec.invoice_to_cust_account_id;
576 ELSE
577 IF p_qte_rec.invoice_to_cust_account_id = FND_API.G_MISS_NUM THEN
578 OPEN c_get_cust_account_id;
579 FETCH c_get_cust_account_id INTO l_invoice_cust_account_id;
580 CLOSE c_get_cust_account_id;
581 END IF;
582 END IF;
583 IF aso_debug_pub.g_debug_flag = 'Y' THEN
584 aso_debug_pub.add('chk_hdr:l_invoice_cust_acccount_id = ' || l_invoice_cust_account_id,1,'N' );
588 IF l_invoice_cust_account_id IS NOT NULL AND l_invoice_cust_account_id <> FND_API.G_MISS_NUM THEN
585 aso_debug_pub.add('chk_hdr:before invoice to party site id '|| p_qte_rec.invoice_to_party_site_id, 1, 'Y');
586 aso_debug_pub.add('chk_hdr:invoice_to_cust_account = ' || p_qte_rec.invoice_to_cust_account_id,1,'N');
587 END IF;
589
590 IF p_qte_rec.invoice_to_party_site_id is not NULL
591 AND p_qte_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM THEN
592
593 Customer_Account_Site(
594 p_api_version => 1.0
595 ,p_party_site_id => p_qte_rec.invoice_to_party_site_id
596 ,p_acct_site_type => 'BILL_TO'
597 ,p_cust_account_id => l_invoice_cust_account_id
598 ,x_cust_acct_site_id => l_inv_cust_acct_site_id
599 ,x_return_status => x_return_status
600 ,x_msg_count => x_msg_count
601 ,x_msg_data => x_msg_data
602 ,x_site_use_id => l_invoice_to_org_id);
603
604 IF aso_debug_pub.g_debug_flag = 'Y' THEN
605 aso_debug_pub.add('chk_hdr:site_use_id after deriving invoice = ' || l_invoice_to_org_id,1,'N');
606 aso_debug_pub.add('chk_hdr:cust_acct_site_id after deriving invoice = ' || l_inv_cust_acct_site_id,1,'N');
607 END IF;
608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
609 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
610 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
611 FND_MSG_PUB.ADD;
612 END IF;
613 raise FND_API.G_EXC_ERROR;
614 END IF;
615
616 END IF;
617 IF aso_debug_pub.g_debug_flag = 'Y' THEN
618 aso_debug_pub.add('chk_hdr: invoice_to_party = ' || p_qte_rec.invoice_to_party_id,1, 'N' );
619 aso_debug_pub.add('chk_hdr: invoice_to_party_site = ' || p_qte_rec.invoice_to_party_site_id,1, 'N' );
620 aso_debug_pub.add('chk_hdr: before Cust_Acct_Contact_Addr:l_invoice_cust_account_id: '||l_invoice_cust_account_id,1,'N');
621 END IF;
622
623 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
624 p_api_version => 1.0,
625 p_party_site_id => p_qte_rec.invoice_to_party_site_id,
626 p_role_type => 'BILL_TO',
627 p_cust_account_id => l_invoice_cust_account_id,
628 p_party_id => p_qte_rec.invoice_to_party_id,
629 p_cust_account_site => l_inv_cust_acct_site_id,
630 x_return_status => x_return_status,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data,
633 x_cust_account_role_id => l_invoice_to_contact_id);
634
635 IF aso_debug_pub.g_debug_flag = 'Y' THEN
636 aso_debug_pub.add('mapq line: after Cust_Acct_Contact_Addr:l_invoice_to_contact_id: '||l_invoice_to_contact_id,1,'N');
637 END IF;
638
639 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
640 raise FND_API.G_EXC_ERROR;
641 END IF;
642
643 END IF; -- l_invoice_cust_account_id not null
644
645 IF aso_debug_pub.g_debug_flag = 'Y' THEN
646 aso_debug_pub.add('chk_hdr:beginning of mapping for header end user ', 1, 'N' );
647 END IF;
648
649 ASO_CHECK_TCA_PVT.Populate_Acct_Party (
650 p_hdr_cust_acct_id => p_qte_rec.cust_account_id,
651 p_hdr_party_id => p_qte_rec.cust_party_id,
652 p_party_site_id => p_qte_rec.End_Customer_party_site_id,
653 p_cust_account_id => p_qte_rec.End_Customer_cust_account_id,
654 p_cust_party_id => p_qte_rec.End_Customer_cust_party_id,
655 x_return_status => x_return_status,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data );
658
659 IF aso_debug_pub.g_debug_flag = 'Y' THEN
660 aso_debug_pub.add('chk_hdr:after populate_acct_party: ' || x_return_status,1,'N');
661 END IF;
662 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
663 raise FND_API.G_EXC_ERROR;
664 END IF;
665
666 IF (p_qte_rec.End_Customer_cust_party_id is NOT NULL AND
667 p_qte_rec.End_Customer_cust_party_id <> FND_API.G_MISS_NUM) AND
668 (p_qte_rec.End_Customer_cust_account_id IS NULL OR
669 p_qte_rec.End_Customer_cust_account_id = FND_API.G_MISS_NUM) THEN
670
671
672 IF aso_debug_pub.g_debug_flag = 'Y' THEN
673 aso_debug_pub.add('Check_Tca: before customer account:p_qte_rec.End_Customer_cust_party_id '
674 ||p_qte_rec.End_Customer_cust_party_id, 1, 'N');
675 END IF;
676 Customer_Account(
677 p_api_version => 1.0,
678 p_Party_Id => p_qte_rec.End_Customer_cust_party_id,
679 p_calling_api_flag => 0,
680 x_Cust_Acct_Id => p_qte_rec.End_Customer_cust_account_id,
681 x_return_status => x_return_status,
682 x_msg_count => x_msg_count,
683 x_msg_data => x_msg_data);
684
685 IF aso_debug_pub.g_debug_flag = 'Y' THEN
686 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '
687 ||x_Return_Status, 1, 'N');
688 END IF;
689 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
690 raise FND_API.G_EXC_ERROR;
691 END IF;
692
693 END IF; -- End_Customer_cust_account_id
694
695 IF aso_debug_pub.g_debug_flag = 'Y' THEN
696 aso_debug_pub.add('Check_Tca: p_qte_rec.End_Customer_cust_account_id: '
700
697 ||p_qte_rec.End_Customer_cust_account_id, 1, 'N');
698 END IF;
699
701 IF p_qte_rec.End_Customer_cust_account_id is not NULL AND
702 p_qte_rec.End_Customer_cust_account_id <> FND_API.G_MISS_NUM THEN
703 l_End_cust_account_id := p_qte_rec.End_Customer_cust_account_id;
704 ELSE
705 IF p_qte_rec.End_Customer_cust_account_id = FND_API.G_MISS_NUM THEN
706 OPEN c_get_cust_account_id;
707 FETCH c_get_cust_account_id INTO l_End_cust_account_id;
708 CLOSE c_get_cust_account_id;
709 END IF;
710 END IF;
711 IF aso_debug_pub.g_debug_flag = 'Y' THEN
712 aso_debug_pub.add('chk_hdr:l_End_Customer_cust_acccount_id = ' || l_End_cust_account_id,1,'N' );
713 aso_debug_pub.add('chk_hdr:before End_Customer party site id '|| p_qte_rec.End_Customer_party_site_id, 1, 'Y');
714 aso_debug_pub.add('chk_hdr:End_Customer_cust_account = ' || p_qte_rec.End_Customer_cust_account_id,1,'N');
715 END IF;
716 IF l_End_cust_account_id IS NOT NULL AND l_End_cust_account_id <> FND_API.G_MISS_NUM THEN
717
718 IF p_qte_rec.End_Customer_party_site_id is not NULL
719 AND p_qte_rec.End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
720
721 Customer_Account_Site(
722 p_api_version => 1.0
723 ,p_party_site_id => p_qte_rec.End_Customer_party_site_id
724 ,p_acct_site_type => 'END_USER'
725 ,p_cust_account_id => l_End_cust_account_id
726 ,x_cust_acct_site_id => l_end_cust_acct_site_id
727 ,x_return_status => x_return_status
728 ,x_msg_count => x_msg_count
729 ,x_msg_data => x_msg_data
730 ,x_site_use_id => l_End_Customer_org_id);
731
732 IF aso_debug_pub.g_debug_flag = 'Y' THEN
733 aso_debug_pub.add('chk_hdr:site_use_id after deriving End_Customer = ' || l_End_Customer_org_id,1,'N');
734 aso_debug_pub.add('chk_hdr:cust_acct_site_id after deriving End_Customer = ' || l_end_cust_acct_site_id,1,'N');
735 END IF;
736 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
737 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
738 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_END_SITE_AC_CRS');
739 FND_MSG_PUB.ADD;
740 END IF;
741 raise FND_API.G_EXC_ERROR;
742 END IF;
743
744 END IF;
745 IF aso_debug_pub.g_debug_flag = 'Y' THEN
746 aso_debug_pub.add('chk_hdr: End_Customer_to_party = ' || p_qte_rec.End_Customer_party_id,1, 'N' );
747 aso_debug_pub.add('chk_hdr: End_Customer_party_site = ' || p_qte_rec.End_Customer_party_site_id,1, 'N' );
748 aso_debug_pub.add('chk_hdr: before Cust_Acct_Contact_Addr:l_End_cust_account_id: '||l_End_cust_account_id,1,'N');
749 END IF;
750
751 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
752 p_api_version => 1.0,
753 p_party_site_id => p_qte_rec.End_Customer_party_site_id,
754 p_role_type => 'END_USER',
755 p_cust_account_id => l_End_cust_account_id,
756 p_party_id => p_qte_rec.End_Customer_party_id,
757 p_cust_account_site => l_end_cust_acct_site_id,
758 x_return_status => x_return_status,
759 x_msg_count => x_msg_count,
760 x_msg_data => x_msg_data,
761 x_cust_account_role_id => l_End_Customer_contact_id);
762
763 IF aso_debug_pub.g_debug_flag = 'Y' THEN
764 aso_debug_pub.add('mapq line: after Cust_Acct_Contact_Addr:l_End_Customer_contact_id: '||l_End_Customer_contact_id,1,'N');
765 END IF;
766
767 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
768 raise FND_API.G_EXC_ERROR;
769 END IF;
770
771 END IF; -- l_End_cust_account_id not null
772
773 IF aso_debug_pub.g_debug_flag = 'Y' THEN
774 aso_debug_pub.add('chk_hdr:beginning of mapping for header shipping ', 1, 'N' );
775 END IF;
776
777 IF p_header_shipment_tbl.count > 0 THEN
778 -- OM takes in only one shipment at the header level
779
780 IF aso_debug_pub.g_debug_flag = 'Y' THEN
781 aso_debug_pub.add('Check_Hdr_Acct: p_header_shipment_tbl(1).ship_to_cust_account_id: '
782 || p_header_shipment_tbl(1).ship_to_cust_account_id, 1, 'N');
783 aso_debug_pub.add('Check_Hdr_Acct: p_header_shipment_tbl(1).ship_to_cust_party_id: '
784 || p_header_shipment_tbl(1).ship_to_cust_party_id, 1, 'N');
785 aso_debug_pub.add('Check_Hdr_Acct: p_header_shipment_tbl(1).ship_to_party_site_id: '
786 || p_header_shipment_tbl(1).ship_to_party_site_id, 1, 'N');
787 END IF;
788
789 ASO_CHECK_TCA_PVT.Populate_Acct_Party (
790 p_hdr_cust_acct_id => p_qte_rec.cust_account_id,
791 p_hdr_party_id => p_qte_rec.cust_party_id,
792 p_party_site_id => p_header_shipment_tbl(1).ship_to_party_site_id,
793 p_cust_account_id => p_header_shipment_tbl(1).ship_to_cust_account_id,
794 p_cust_party_id => p_header_shipment_tbl(1).ship_to_cust_party_id,
795 x_return_status => x_return_status,
796 x_msg_count => x_msg_count,
797 x_msg_data => x_msg_data );
798
799 IF aso_debug_pub.g_debug_flag = 'Y' THEN
800 aso_debug_pub.add('chk_hdr:(ship)after populate_acct_party: ' || x_return_status,1,'N');
801 END IF;
802 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
803 raise FND_API.G_EXC_ERROR;
804 END IF;
805
809 p_header_shipment_tbl(1).ship_to_cust_account_id = FND_API.G_MISS_NUM) THEN
806 IF (p_header_shipment_tbl(1).ship_to_cust_party_id IS NOT NULL AND
807 p_header_shipment_tbl(1).ship_to_cust_party_id <> FND_API.G_MISS_NUM) AND
808 (p_header_shipment_tbl(1).ship_to_cust_account_id IS NULL OR
810
811
812 IF aso_debug_pub.g_debug_flag = 'Y' THEN
813 aso_debug_pub.add('Check_Tca: before customer account:p_header_shipment_tbl(1).
814 ship_to_cust_party_id '||p_header_shipment_tbl(1).ship_to_cust_party_id
815 , 1, 'N');
816 END IF;
817 Customer_Account(
818 p_api_version => 1.0,
819 p_Party_Id => p_header_shipment_tbl(1).ship_to_cust_party_id,
820 p_calling_api_flag => 0,
821 x_Cust_Acct_Id => p_header_shipment_tbl(1).ship_to_cust_account_id,
822 x_return_status => x_return_status,
823 x_msg_count => x_msg_count,
824 x_msg_data => x_msg_data);
825
826 IF aso_debug_pub.g_debug_flag = 'Y' THEN
827 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '
828 ||x_Return_Status, 1, 'N');
829 END IF;
830 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
831 raise FND_API.G_EXC_ERROR;
832 END IF;
833
834 END IF; -- cust_account_id
835
836 IF aso_debug_pub.g_debug_flag = 'Y' THEN
837 aso_debug_pub.add('Check_Tca: p_header_shipment_tbl(1).ship_to_cust_account_id: '||
838 p_header_shipment_tbl(1).ship_to_cust_account_id, 1, 'N');
839 END IF;
840
841
842 IF p_header_shipment_tbl(1).ship_to_cust_account_id is not NULL AND
843 p_header_shipment_tbl(1).ship_to_cust_account_id <> FND_API.G_MISS_NUM THEN
844 l_ship_cust_account_id := p_header_shipment_tbl(1).ship_to_cust_account_id;
845 ELSE
846 IF p_header_shipment_tbl(1).ship_to_cust_account_id = FND_API.G_MISS_NUM THEN
847 OPEN c_get_cust_account_id;
848 FETCH c_get_cust_account_id INTO l_ship_cust_account_id;
849 CLOSE c_get_cust_account_id;
850 END IF;
851 END IF;
852
853 IF aso_debug_pub.g_debug_flag = 'Y' THEN
854 aso_debug_pub.add('chk_hdr:ship cust acccount = ' || l_ship_cust_account_id ,1, 'N');
855 aso_debug_pub.add('chk_hdr:ship to party site = ' || p_header_shipment_tbl(1).ship_to_party_site_id, 1, 'N');
856 END IF;
857 IF l_ship_cust_account_id IS NOT NULL AND l_ship_cust_account_id <> FND_API.G_MISS_NUM THEN
858
859 IF p_header_shipment_tbl(1).ship_to_party_site_id is not NULL
860 AND p_header_shipment_tbl(1).ship_to_party_site_id <> FND_API.G_MISS_NUM THEN
861
862 Customer_Account_Site(
863 p_api_version => 1.0
864 ,p_party_site_id => p_header_shipment_tbl(1).ship_to_party_site_id
865 ,p_acct_site_type => 'SHIP_TO'
866 ,p_cust_account_id => l_ship_cust_account_id
867 ,x_cust_acct_site_id => l_shp_cust_acct_site_id
868 ,x_return_status => x_return_status
869 ,x_msg_count => x_msg_count
870 ,x_msg_data => x_msg_data
871 ,x_site_use_id => l_ship_to_org_id);
872
873 IF aso_debug_pub.g_debug_flag = 'Y' THEN
874 aso_debug_pub.add('chk_hdr:ship to org after deriving = ' || l_ship_to_org_id, 1, 'Y');
875 aso_debug_pub.add('chk_hdr:ship cust acct site after deriving = ' || l_shp_cust_acct_site_id, 1, 'Y');
876 END IF;
877 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
878 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
879 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_SHP_SITE_AC_CRS');
880 -- FND_MESSAGE.Set_Token('ID', to_char(p_header_shipment_tbl(1).ship_to_party_site_id),FALSE);
881 FND_MSG_PUB.ADD;
882 END IF;
883 raise FND_API.G_EXC_ERROR;
884 END IF;
885
886 END IF;
887
888 IF aso_debug_pub.g_debug_flag = 'Y' THEN
889 aso_debug_pub.add('chk_hdr:beginning of map 2:ship_party_id:' || p_header_shipment_tbl(1).ship_to_party_id,1,'N');
890 aso_debug_pub.add('chk_hdr: before Cust_Acct_Contact_Addr:l_ship_cust_account_id: '||l_ship_cust_account_id,1,'N');
891 END IF;
892
893 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
894 p_api_version => 1.0,
895 p_party_site_id => p_header_shipment_tbl(1).ship_to_party_site_id,
896 p_role_type => 'SHIP_TO',
897 p_cust_account_id => l_ship_cust_account_id,
898 p_party_id => p_header_shipment_tbl(1).ship_to_party_id,
899 p_cust_account_site => l_shp_cust_acct_site_id,
900 x_return_status => x_return_status,
901 x_msg_count => x_msg_count,
902 x_msg_data => x_msg_data,
903 x_cust_account_role_id => l_ship_to_contact_id);
904
905 IF aso_debug_pub.g_debug_flag = 'Y' THEN
906 aso_debug_pub.add('chk_hdr: after Cust_Acct_Contact_Addr:l_ship_to_contact_id: '||l_ship_to_contact_id,1,'N');
907 aso_debug_pub.add('chk_hdr: after Cust_Acct_Contact_Addr:l_shp_cust_acct_site_id: '||l_shp_cust_acct_site_id,1,'N');
908 END IF;
909 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
910 raise FND_API.G_EXC_ERROR;
911 END IF;
912
913 END IF; -- l_ship_cust_account_id is not null
917 IF aso_debug_pub.g_debug_flag = 'Y' THEN
914
915 END IF; -- shipment tbl count
916
918 aso_debug_pub.add('end chk_hdr:after map 2 for header',1,'N');
919 END IF;
920
921 FND_MSG_PUB.Count_And_Get
922 ( p_count => x_msg_count,
923 p_data => x_msg_data
924 );
925
926 EXCEPTION
927 WHEN FND_API.G_EXC_ERROR THEN
928 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
929 P_API_NAME => L_API_NAME
930 ,P_PKG_NAME => G_PKG_NAME
931 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
932 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
933 ,P_SQLCODE => SQLCODE
934 ,P_SQLERRM => SQLERRM
935 ,X_MSG_COUNT => X_MSG_COUNT
936 ,X_MSG_DATA => X_MSG_DATA
937 ,X_RETURN_STATUS => X_RETURN_STATUS);
938
939 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
940 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
941 P_API_NAME => L_API_NAME
942 ,P_PKG_NAME => G_PKG_NAME
943 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
944 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
945 ,P_SQLCODE => SQLCODE
946 ,P_SQLERRM => SQLERRM
947 ,X_MSG_COUNT => X_MSG_COUNT
948 ,X_MSG_DATA => X_MSG_DATA
949 ,X_RETURN_STATUS => X_RETURN_STATUS);
950
951 WHEN OTHERS THEN
952 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
953 P_API_NAME => L_API_NAME
954 ,P_PKG_NAME => G_PKG_NAME
955 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
956 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
957 ,P_SQLCODE => SQLCODE
958 ,P_SQLERRM => SQLERRM
959 ,X_MSG_COUNT => X_MSG_COUNT
960 ,X_MSG_DATA => X_MSG_DATA
961 ,X_RETURN_STATUS => X_RETURN_STATUS);
962
963 END check_header_account_info;
964
965
966 PROCEDURE check_line_account_info(
967 p_api_version IN NUMBER,
968 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
969 p_cust_account_id IN NUMBER,
970 P_Qte_Line_Rec IN OUT NOCOPY ASO_QUOTE_PUB.Qte_Line_Rec_Type,
971 P_Line_Shipment_Tbl IN OUT NOCOPY ASO_QUOTE_PUB.Shipment_Tbl_Type,
972 p_application_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
973 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
974 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
975 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
976 IS
977 l_api_version CONSTANT NUMBER := 1.0;
978 l_api_name CONSTANT VARCHAR2(45) := 'Check_Line_Account_Info';
979 l_site_use_id NUMBER;
980 l_invoice_cust_account_id NUMBER;
981 l_End_cust_account_id NUMBER;
982 l_invoice_contact_party_id NUMBER;
983 l_invoice_to_contact_id NUMBER;
984 l_End_Customer_contact_id NUMBER;
985 l_ship_cust_account_id NUMBER;
986 l_invoice_cust_account_site NUMBER;
987 l_End_cust_account_site NUMBER;
988 l_ship_contact_party_id NUMBER;
989 l_ship_to_contact_id NUMBER;
990 l_ship_cust_account_site NUMBER;
991 l_invoice_to_org_id NUMBER;
992 l_End_Customer_org_id NUMBER;
993 l_inv_cust_acct_site_id NUMBER;
994 l_end_cust_acct_site_id NUMBER;
995 l_shp_cust_acct_site_id NUMBER;
996
997 l_cust_party_id NUMBER := NULL;
998 l_cust_acct_id NUMBER := NULL;
999 j NUMBER := 1;
1000
1001 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type := ASO_QUOTE_PUB.G_Miss_Qte_Line_Rec;
1002 l_shipment_rec ASO_QUOTE_PUB.shipment_rec_type := ASO_QUOTE_PUB.G_MISS_SHIPMENT_REC;
1003
1004 CURSOR get_cust_acct_site_id(l_site_use_id number) IS
1005 select cust_acct_site_id from hz_cust_site_uses
1006 where site_use_id = l_site_use_id;
1007
1008 CURSOR C_Get_Sold_To_Info(qte_hdr NUMBER) IS
1009 SELECT cust_account_id, cust_party_id
1010 FROM ASO_QUOTE_HEADERS_ALL
1011 WHERE quote_header_id = qte_hdr;
1012
1013 CURSOR C_Get_Party_From_Acct(acct_id NUMBER) IS
1014 SELECT party_id
1015 FROM HZ_CUST_ACCOUNTS
1016 WHERE cust_account_id = acct_id
1017 AND status = 'A'
1018 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
1019
1020 CURSOR C_line_cust IS
1021 SELECT invoice_to_cust_account_id,end_customer_cust_account_id
1022 FROM ASO_QUOTE_LINES_ALL
1023 WHERE quote_line_id = P_Qte_Line_Rec.quote_line_id;
1024
1025 CURSOR C_ship_cust(l_shipment_id NUMBER) IS
1026 SELECT ship_to_cust_account_id
1027 FROM ASO_SHIPMENTS
1028 WHERE shipment_id = l_shipment_id;
1029
1030
1031
1032
1033 BEGIN
1034
1035 SAVEPOINT CHECK_LINE_ACCOUNT_INFO_PVT;
1036
1037 IF FND_API.to_boolean(p_init_msg_list) THEN
1038 FND_MSG_PUB.initialize;
1039 END IF;
1040
1041 IF NOT FND_API.compatible_api_call(
1042 l_api_version,
1046 ) THEN
1043 p_api_version,
1044 l_api_name,
1045 g_pkg_name
1047 RAISE FND_API.g_exc_unexpected_error;
1048 END IF;
1049
1050 x_return_status := FND_API.g_ret_sts_success;
1051
1052 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1053 aso_debug_pub.add('line account info:begin ',1,'Y');
1054 aso_debug_pub.add('line account info:P_line_Shipment_Tbl.count: '||P_line_Shipment_Tbl.count,1,'N');
1055 END IF;
1056
1057 FOR j in 1..P_line_Shipment_Tbl.count LOOP
1058
1059 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1060 aso_debug_pub.add('line account info:p_qte_line_rec.invoice_to_cust_account_id: '||p_qte_line_rec.invoice_to_cust_account_id,1,'N');
1061 aso_debug_pub.add('line account info:p_qte_line_rec.invoice_to_cust_party_id: '||p_qte_line_rec.invoice_to_cust_party_id,1,'N');
1062 aso_debug_pub.add('line account info:p_qte_line_rec.invoice_to_party_site_id: '||p_qte_line_rec.invoice_to_party_site_id,1,'N');
1063 END IF;
1064
1065 IF (P_Qte_Line_Rec.operation_code = 'UPDATE' AND P_Application_Type_Code = 'QUOTING HTML') THEN
1066 l_qte_line_rec := ASO_UTILITY_PVT.query_qte_line_row (P_Qte_Line_Rec.quote_line_id);
1067
1068 IF P_Qte_Line_Rec.invoice_to_party_id <> FND_API.G_MISS_NUM OR
1069 P_Qte_Line_Rec.invoice_to_cust_party_id <> FND_API.G_MISS_NUM OR
1070 P_Qte_Line_Rec.invoice_to_cust_account_id <> FND_API.G_MISS_NUM OR
1071 P_Qte_Line_Rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM THEN
1072
1073 IF P_Qte_Line_Rec.invoice_to_party_id = FND_API.G_MISS_NUM THEN
1074 P_Qte_Line_Rec.invoice_to_party_id := l_qte_line_rec.invoice_to_party_id;
1075 END IF;
1076 IF P_Qte_Line_Rec.invoice_to_cust_party_id = FND_API.G_MISS_NUM THEN
1077 P_Qte_Line_Rec.invoice_to_cust_party_id := l_qte_line_rec.invoice_to_cust_party_id;
1078 END IF;
1079 IF P_Qte_Line_Rec.invoice_to_cust_account_id = FND_API.G_MISS_NUM THEN
1080 P_Qte_Line_Rec.invoice_to_cust_account_id := l_qte_line_rec.invoice_to_cust_account_id;
1081 END IF;
1082 IF P_Qte_Line_Rec.invoice_to_party_site_id = FND_API.G_MISS_NUM THEN
1083 P_Qte_Line_Rec.invoice_to_party_site_id := l_qte_line_rec.invoice_to_party_site_id;
1084 END IF;
1085 END IF;
1086
1087 IF P_Qte_Line_Rec.End_Customer_party_id <> FND_API.G_MISS_NUM OR
1088 P_Qte_Line_Rec.End_Customer_cust_party_id <> FND_API.G_MISS_NUM OR
1089 P_Qte_Line_Rec.End_Customer_cust_account_id <> FND_API.G_MISS_NUM OR
1090 P_Qte_Line_Rec.End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
1091
1092 IF P_Qte_Line_Rec.End_Customer_party_id = FND_API.G_MISS_NUM THEN
1093 P_Qte_Line_Rec.End_Customer_party_id := l_qte_line_rec.End_Customer_party_id;
1094 END IF;
1095 IF P_Qte_Line_Rec.End_Customer_cust_party_id = FND_API.G_MISS_NUM THEN
1096 P_Qte_Line_Rec.End_Customer_cust_party_id := l_qte_line_rec.End_Customer_cust_party_id;
1097 END IF;
1098 IF P_Qte_Line_Rec.End_Customer_cust_account_id = FND_API.G_MISS_NUM THEN
1099 P_Qte_Line_Rec.End_Customer_cust_account_id := l_qte_line_rec.End_Customer_cust_account_id;
1100 END IF;
1101 IF P_Qte_Line_Rec.End_Customer_party_site_id = FND_API.G_MISS_NUM THEN
1102 P_Qte_Line_Rec.End_Customer_party_site_id := l_qte_line_rec.End_Customer_party_site_id;
1103 END IF;
1104 END IF;
1105
1106 IF P_line_Shipment_Tbl(j).operation_code = 'UPDATE' THEN
1107 IF P_line_Shipment_Tbl(j).shipment_id IS NOT NULL AND P_line_Shipment_Tbl(j).shipment_id <> FND_API.G_MISS_NUM THEN
1108 IF P_line_Shipment_Tbl(j).ship_to_party_id <> FND_API.G_MISS_NUM OR
1109 P_line_Shipment_Tbl(j).ship_to_cust_party_id <> FND_API.G_MISS_NUM OR
1110 P_line_Shipment_Tbl(j).ship_to_cust_account_id <> FND_API.G_MISS_NUM OR
1111 P_line_Shipment_Tbl(j).ship_to_party_site_id <> FND_API.G_MISS_NUM THEN
1112
1113 l_shipment_rec := ASO_UTILITY_PVT.query_shipment_row (P_line_Shipment_Tbl(j).shipment_id);
1114 IF P_line_Shipment_Tbl(j).ship_to_party_id = FND_API.G_MISS_NUM THEN
1115 P_line_Shipment_Tbl(j).ship_to_party_id := l_shipment_rec.ship_to_party_id;
1116 END IF;
1117 IF P_line_Shipment_Tbl(j).ship_to_cust_party_id = FND_API.G_MISS_NUM THEN
1118 P_line_Shipment_Tbl(j).ship_to_cust_party_id := l_shipment_rec.ship_to_cust_party_id;
1119 END IF;
1120 IF P_line_Shipment_Tbl(j).ship_to_cust_account_id = FND_API.G_MISS_NUM THEN
1121 P_line_Shipment_Tbl(j).ship_to_cust_account_id := l_shipment_rec.ship_to_cust_account_id;
1122 END IF;
1123 IF P_line_Shipment_Tbl(j).ship_to_party_site_id = FND_API.G_MISS_NUM THEN
1124 P_line_Shipment_Tbl(j).ship_to_party_site_id := l_shipment_rec.ship_to_party_site_id;
1125 END IF;
1126 END IF;
1127 END IF;
1128 END IF;
1129
1130 END IF; -- UPDATE
1131
1132 /* bug5132989
1133 l_cust_acct_id := p_qte_line_rec.invoice_to_cust_account_id;
1134 l_cust_party_id := p_qte_line_rec.invoice_to_cust_party_id;
1135
1136
1137 IF l_cust_acct_id IS NULL OR l_cust_party_id IS NULL THEN
1138
1139 OPEN C_Get_Sold_To_Info(p_qte_line_rec.quote_header_id);
1140 FETCH C_Get_Sold_To_Info INTO l_cust_acct_id, l_cust_party_id;
1141 CLOSE C_Get_Sold_To_Info;
1142
1143 END IF;
1144
1145 ASO_CHECK_TCA_PVT.Populate_Acct_Party (
1146 p_hdr_cust_acct_id => l_cust_acct_id,
1150 p_cust_party_id => p_qte_line_rec.invoice_to_cust_party_id,
1147 p_hdr_party_id => l_cust_party_id,
1148 p_party_site_id => p_qte_line_rec.invoice_to_party_site_id,
1149 p_cust_account_id => p_qte_line_rec.invoice_to_cust_account_id,
1151 x_return_status => x_return_status,
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data );
1154
1155 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1156 aso_debug_pub.add('chk_lin:after populate_acct_party: ' || x_return_status,1,'N');
1157 END IF;
1158 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1159 raise FND_API.G_EXC_ERROR;
1160 END IF;
1161 */
1162
1163 IF (p_qte_line_rec.invoice_to_cust_party_id IS NOT NULL AND
1164 p_qte_line_rec.invoice_to_cust_party_id <> FND_API.G_MISS_NUM) AND
1165 (p_qte_line_rec.invoice_to_cust_account_id IS NULL OR
1166 p_qte_line_rec.invoice_to_cust_account_id = FND_API.G_MISS_NUM) THEN
1167
1168
1169 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1170 aso_debug_pub.add('Check_Tca: before customer account: p_qte_line_rec.invoice_to_cust_party_id '
1171 || p_qte_line_rec.invoice_to_cust_party_id, 1, 'N')
1172 ;
1173 END IF;
1174 Customer_Account(
1175 p_api_version => 1.0,
1176 p_Party_Id => p_qte_line_rec.invoice_to_cust_party_id,
1177 p_calling_api_flag => 0,
1178 x_Cust_Acct_Id => p_qte_line_rec.invoice_to_cust_account_id,
1179 x_return_status => x_return_status,
1180 x_msg_count => x_msg_count,
1181 x_msg_data => x_msg_data);
1182
1183 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1184 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '
1185 ||x_Return_Status, 1, 'N');
1186 END IF;
1187 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1188 raise FND_API.G_EXC_ERROR;
1189 END IF;
1190
1191 END IF; -- invoice_to_cust_account_id
1192 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1193 aso_debug_pub.add('Check_Tca: p_qte_line_rec.invoice_to_cust_account_id: '
1194 ||p_qte_line_rec.invoice_to_cust_account_id, 1, 'N');
1195 END IF;
1196
1197
1198 IF p_qte_line_rec.invoice_to_cust_account_id is not NULL AND
1199 p_qte_line_rec.invoice_to_cust_account_id <> FND_API.G_MISS_NUM THEN
1200 l_invoice_cust_account_id := p_qte_line_rec.invoice_to_cust_account_id;
1201 ELSE
1202 IF p_qte_line_rec.invoice_to_cust_account_id = FND_API.G_MISS_NUM THEN
1203 OPEN C_line_cust;
1204 FETCH C_line_cust INTO l_invoice_cust_account_id,l_End_cust_account_id;
1205 CLOSE C_line_cust;
1206 END IF;
1207 END IF;
1208
1209 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1210 aso_debug_pub.add('chk_ln: l_invoice_cust_account_id: '||l_invoice_cust_account_id,1,'N');
1211 aso_debug_pub.add('chk_ln: l_End_cust_account_id: '||l_End_cust_account_id,1,'N');
1212 aso_debug_pub.add('line account info: inside shipment loop:party_site: '||p_qte_line_rec.invoice_to_party_site_id,1,'N');
1213 END IF;
1214
1215 IF l_invoice_cust_account_id IS NOT NULL AND l_invoice_cust_account_id <> FND_API.G_MISS_NUM THEN
1216
1217 IF p_qte_line_rec.invoice_to_party_site_id is not NULL
1218 AND p_qte_line_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM THEN
1219
1220 Customer_Account_Site(
1221 p_api_version => 1.0
1222 ,p_party_site_id => p_qte_line_rec.invoice_to_party_site_id
1223 ,p_acct_site_type => 'BILL_TO'
1224 ,p_cust_account_id => l_invoice_cust_account_id
1225 ,x_cust_acct_site_id => l_inv_cust_acct_site_id
1226 ,x_return_status => x_return_status
1227 ,x_msg_count => x_msg_count
1228 ,x_msg_data => x_msg_data
1229 ,x_site_use_id => l_site_use_id);
1230
1231 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1232 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1233 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
1234 FND_MSG_PUB.ADD;
1235 END IF;
1236 raise FND_API.G_EXC_ERROR;
1237 END IF;
1238 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1239 aso_debug_pub.add('line account info: site use after cust account site: '||l_site_use_id,1,'N');
1240 aso_debug_pub.add('line account info: cust_acct_site after cust account site: '||l_inv_cust_acct_site_id,1,'N');
1241 END IF;
1242 END IF;
1243
1244 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1245 aso_debug_pub.add('chk_ln: before Cust_Acct_Contact_Addr:l_invoice_cust_account_id: '||l_invoice_cust_account_id,1,'N');
1246 END IF;
1247
1248 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
1249 p_api_version => 1.0,
1250 p_party_site_id => p_qte_line_rec.invoice_to_party_site_id,
1251 p_role_type => 'BILL_TO',
1252 p_cust_account_id => l_invoice_cust_account_id,
1256 x_msg_count => x_msg_count,
1253 p_party_id => p_qte_line_rec.invoice_to_party_id,
1254 p_cust_account_site => l_inv_cust_acct_site_id,
1255 x_return_status => x_return_status,
1257 x_msg_data => x_msg_data,
1258 x_cust_account_role_id => l_invoice_to_contact_id);
1259
1260 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1261 aso_debug_pub.add('chk_ln: after Cust_Acct_Contact_Addr:l_invoice_to_contact_id: '||l_invoice_to_contact_id,1,'N');
1262 aso_debug_pub.add('chk_ln: after Cust_Acct_Contact_Addr:l_inv_cust_acct_site_id: '||l_inv_cust_acct_site_id,1,'N');
1263 END IF;
1264
1265 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1266 raise FND_API.G_EXC_ERROR;
1267 END IF;
1268
1269 END IF; -- l_invoice_cust_account_id is not null
1270
1271
1272 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1273 aso_debug_pub.add('line account info:p_qte_line_rec.End_Customer_cust_account_id: '||p_qte_line_rec.End_Customer_cust_account_id,1,'N');
1274 aso_debug_pub.add('line account info:p_qte_line_rec.End_Customer_cust_party_id: '||p_qte_line_rec.End_Customer_cust_party_id,1,'N');
1275 aso_debug_pub.add('line account info:p_qte_line_rec.End_Customer_party_site_id: '||p_qte_line_rec.End_Customer_party_site_id,1,'N');
1276 END IF;
1277
1278 /*--bug 5132989
1279 l_cust_acct_id := p_qte_line_rec.End_Customer_cust_account_id;
1280 l_cust_party_id := p_qte_line_rec.End_Customer_cust_party_id;
1281
1282 IF l_cust_acct_id IS NULL AND l_cust_party_id IS NOT NULL AND l_cust_party_id <> FND_API.G_MISS_NUM THEN
1283
1284 OPEN C_Get_Sold_To_Info(p_qte_line_rec.quote_header_id);
1285 FETCH C_Get_Sold_To_Info INTO l_cust_acct_id, l_cust_party_id;
1286 CLOSE C_Get_Sold_To_Info;
1287
1288
1289 ASO_CHECK_TCA_PVT.Populate_Acct_Party (
1290 p_hdr_cust_acct_id => l_cust_acct_id,
1291 p_hdr_party_id => l_cust_party_id,
1292 p_party_site_id => p_qte_line_rec.End_Customer_party_site_id,
1293 p_cust_account_id => p_qte_line_rec.End_Customer_cust_account_id,
1294 p_cust_party_id => p_qte_line_rec.End_Customer_cust_party_id,
1295 x_return_status => x_return_status,
1296 x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data );
1298
1299 END IF;
1300 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1301 aso_debug_pub.add('chk_lin:after populate_acct_party: ' || x_return_status,1,'N');
1302 END IF;
1303 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1304 raise FND_API.G_EXC_ERROR;
1305 END IF;
1306
1307 */
1308 IF (p_qte_line_rec.End_Customer_cust_party_id IS NOT NULL AND
1309 p_qte_line_rec.End_Customer_cust_party_id <> FND_API.G_MISS_NUM) AND
1310 (p_qte_line_rec.End_Customer_cust_account_id IS NULL OR
1311 p_qte_line_rec.End_Customer_cust_account_id = FND_API.G_MISS_NUM) THEN
1312
1313
1314 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1315 aso_debug_pub.add('Check_Tca: before customer account: p_qte_line_rec.End_Customer_cust_party_id '
1316 || p_qte_line_rec.End_Customer_cust_party_id, 1, 'N')
1317 ;
1318 END IF;
1319 Customer_Account(
1320 p_api_version => 1.0,
1321 p_Party_Id => p_qte_line_rec.End_Customer_cust_party_id,
1322 p_calling_api_flag => 0,
1323 x_Cust_Acct_Id => p_qte_line_rec.End_Customer_cust_account_id,
1324 x_return_status => x_return_status,
1325 x_msg_count => x_msg_count,
1326 x_msg_data => x_msg_data);
1327
1328 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1329 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '
1330 ||x_Return_Status, 1, 'N');
1331 END IF;
1332 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1333 raise FND_API.G_EXC_ERROR;
1334 END IF;
1335
1336 END IF; -- End_Customer_cust_account_id
1337 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1338 aso_debug_pub.add('Check_Tca: p_qte_line_rec.End_Customer_cust_account_id: '
1339 ||p_qte_line_rec.End_Customer_cust_account_id, 1, 'N');
1340 END IF;
1341
1342
1343 IF p_qte_line_rec.End_Customer_cust_account_id is not NULL AND
1344 p_qte_line_rec.End_Customer_cust_account_id <> FND_API.G_MISS_NUM THEN
1345 l_End_cust_account_id := p_qte_line_rec.End_Customer_cust_account_id;
1346 ELSE
1347 IF p_qte_line_rec.End_Customer_cust_account_id = FND_API.G_MISS_NUM THEN
1348 OPEN C_line_cust;
1349 FETCH C_line_cust INTO l_invoice_cust_account_id,l_End_cust_account_id;
1350 CLOSE C_line_cust;
1351 END IF;
1352 END IF;
1353
1354 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1355 aso_debug_pub.add('chk_ln: l_End_cust_account_id: '||l_End_cust_account_id,1,'N');
1356 aso_debug_pub.add('line account info: inside shipment loop:party_site: '||p_qte_line_rec.End_Customer_party_site_id,1,'N');
1357 END IF;
1358
1359 IF l_End_cust_account_id IS NOT NULL AND l_End_cust_account_id <> FND_API.G_MISS_NUM THEN
1360
1361 IF p_qte_line_rec.End_Customer_party_site_id is not NULL
1365 p_api_version => 1.0
1362 AND p_qte_line_rec.End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
1363
1364 Customer_Account_Site(
1366 ,p_party_site_id => p_qte_line_rec.End_Customer_party_site_id
1367 ,p_acct_site_type => 'END_USER'
1368 ,p_cust_account_id => l_End_cust_account_id
1369 ,x_cust_acct_site_id => l_end_cust_acct_site_id
1370 ,x_return_status => x_return_status
1371 ,x_msg_count => x_msg_count
1372 ,x_msg_data => x_msg_data
1373 ,x_site_use_id => l_site_use_id);
1374
1375 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1376 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1377 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_END_SITE_AC_CRS');
1378 FND_MSG_PUB.ADD;
1379 END IF;
1380 raise FND_API.G_EXC_ERROR;
1381 END IF;
1382 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1383 aso_debug_pub.add('line account info: site use after cust account site: '||l_site_use_id,1,'N');
1384 aso_debug_pub.add('line account info: cust_acct_site after cust account site: '||l_end_cust_acct_site_id,1,'N');
1385 END IF;
1386 END IF;
1387
1388 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1389 aso_debug_pub.add('chk_ln: before Cust_Acct_Contact_Addr:l_End_cust_account_id: '||l_End_cust_account_id,1,'N');
1390 END IF;
1391
1392 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
1393 p_api_version => 1.0,
1394 p_party_site_id => p_qte_line_rec.End_Customer_party_site_id,
1395 p_role_type => 'END_USER',
1396 p_cust_account_id => l_End_cust_account_id,
1397 p_party_id => p_qte_line_rec.End_Customer_party_id,
1398 p_cust_account_site => l_end_cust_acct_site_id,
1399 x_return_status => x_return_status,
1400 x_msg_count => x_msg_count,
1401 x_msg_data => x_msg_data,
1402 x_cust_account_role_id => l_End_Customer_contact_id);
1403
1404 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1405 aso_debug_pub.add('chk_ln: after Cust_Acct_Contact_Addr:l_End_Customer_contact_id: '||l_End_Customer_contact_id,1,'N');
1406 aso_debug_pub.add('chk_ln: after Cust_Acct_Contact_Addr:l_end_cust_acct_site_id: '||l_end_cust_acct_site_id,1,'N');
1407 END IF;
1408
1409 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1410 raise FND_API.G_EXC_ERROR;
1411 END IF;
1412
1413 END IF; -- l_End_cust_account_id is not null
1414
1415 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1416 aso_debug_pub.add('chk_ln: p_line_shipment_tbl(j).ship_to_cust_account_id: '
1417 ||p_line_shipment_tbl(j).ship_to_cust_account_id,1,'N');
1418 aso_debug_pub.add('chk_ln: p_line_shipment_tbl(j).ship_to_cust_party_id: '
1419 ||p_line_shipment_tbl(j).ship_to_cust_party_id,1,'N');
1420 aso_debug_pub.add('chk_ln: p_line_shipment_tbl(j).ship_to_party_site_id: '
1421 ||p_line_shipment_tbl(j).ship_to_party_site_id,1,'N');
1422 END IF;
1423 -- bug 5132989
1424 /*
1425 ASO_CHECK_TCA_PVT.Populate_Acct_Party (
1426 p_hdr_cust_acct_id => l_cust_acct_id,
1427 p_hdr_party_id => l_cust_party_id,
1428 p_party_site_id => p_line_shipment_tbl(j).ship_to_party_site_id,
1429 p_cust_account_id => p_line_shipment_tbl(j).ship_to_cust_account_id,
1430 p_cust_party_id => p_line_shipment_tbl(j).ship_to_cust_party_id,
1431 x_return_status => x_return_status,
1432 x_msg_count => x_msg_count,
1433 x_msg_data => x_msg_data );
1434
1435 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1436 aso_debug_pub.add('chk_lin:(ship)after populate_acct_party: ' || x_return_status,1,'N');
1437 END IF;
1438 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1439 raise FND_API.G_EXC_ERROR;
1440 END IF;
1441 */
1442 IF (p_line_shipment_tbl(j).ship_to_cust_party_id IS NOT NULL AND
1443 p_line_shipment_tbl(j).ship_to_cust_party_id <> FND_API.G_MISS_NUM) AND
1444 (p_line_shipment_tbl(j).ship_to_cust_account_id IS NULL OR
1445 p_line_shipment_tbl(j).ship_to_cust_account_id = FND_API.G_MISS_NUM) THEN
1446
1447
1448 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1449 aso_debug_pub.add('Check_Tca: before customer account:p_header_shipment_tbl(j).
1450 ship_to_cust_party_id '||p_line_shipment_tbl(j).ship_to_cust_party_id,
1451 1, 'N');
1452 END IF;
1453 Customer_Account(
1454 p_api_version => 1.0,
1455 p_Party_Id => p_line_shipment_tbl(j).ship_to_cust_party_id,
1456 p_calling_api_flag => 0,
1457 x_Cust_Acct_Id => p_line_shipment_tbl(j).ship_to_cust_account_id,
1458 x_return_status => x_return_status,
1459 x_msg_count => x_msg_count,
1460 x_msg_data => x_msg_data);
1461
1462 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1463 aso_debug_pub.add('Check_Tca: after customer account:x_Return_Status '
1464 ||x_Return_Status, 1, 'N');
1465 END IF;
1466 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1467 raise FND_API.G_EXC_ERROR;
1468 END IF;
1469
1473 aso_debug_pub.add('Check_Tca: p_line_shipment_tbl(j).ship_to_cust_account_id: '||
1470 END IF; -- ship cust_account_id
1471
1472 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1474 p_line_shipment_tbl(j).ship_to_cust_account_id, 1, 'N');
1475 END IF;
1476
1477 IF p_line_shipment_tbl(j).ship_to_cust_account_id is not NULL AND
1478 p_line_shipment_tbl(j).ship_to_cust_account_id <> FND_API.G_MISS_NUM THEN
1479 l_ship_cust_account_id := p_line_shipment_tbl(j).ship_to_cust_account_id;
1480 ELSE
1481 IF p_line_shipment_tbl(j).ship_to_cust_account_id = FND_API.G_MISS_NUM THEN
1482 OPEN C_ship_cust(p_line_shipment_tbl(j).shipment_id);
1483 FETCH C_ship_cust INTO l_ship_cust_account_id ;
1484 CLOSE C_ship_cust;
1485 END IF;
1486 END IF;
1487
1488 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1489 aso_debug_pub.add('line acct info: before cust_acct_site:l_ship_cust_account_id: '||l_ship_cust_account_id,1,'N');
1490 END IF;
1491
1492 IF l_ship_cust_account_id IS NOT NULL AND l_ship_cust_account_id <> FND_API.G_MISS_NUM THEN
1493 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1494 aso_debug_pub.add('line acct info: before cust_acct_site:p_line_shipment_tbl(j).ship_to_party_site_id: '||p_line_shipment_tbl(j).ship_to_party_site_id,1,'N');
1495 END IF;
1496 IF p_line_shipment_tbl(j).ship_to_party_site_id is not NULL AND
1497 p_line_shipment_tbl(j).ship_to_party_site_id <> FND_API.G_MISS_NUM THEN
1498
1499 Customer_Account_Site(
1500 p_api_version => 1.0
1501 ,p_party_site_id => p_line_shipment_tbl(j).ship_to_party_site_id
1502 ,p_acct_site_type => 'SHIP_TO'
1503 ,p_cust_account_id => l_ship_cust_account_id
1504 ,x_cust_acct_site_id => l_shp_cust_acct_site_id
1505 ,x_return_status => x_return_status
1506 ,x_msg_count => x_msg_count
1507 ,x_msg_data => x_msg_data
1508 ,x_site_use_id => l_site_use_id);
1509
1510 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1511 aso_debug_pub.add('line acct info: after cust_acct_site:l_site_use_id: '||l_site_use_id,1,'N');
1512 aso_debug_pub.add('line acct info: after cust_acct_site:l_shp_cust_acct_site_id: '||l_shp_cust_acct_site_id,1,'N');
1513 END IF;
1514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1515 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1516 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
1517 -- FND_MESSAGE.Set_Token('ID', to_char(p_line_shipment_tbl(j).ship_to_party_site_id),FALSE);
1518 FND_MSG_PUB.ADD;
1519 END IF;
1520 raise FND_API.G_EXC_ERROR;
1521 END IF;
1522
1523 END IF;
1524
1525 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1526 aso_debug_pub.add('chk_ln: before Cust_Acct_Contact_Addr:l_ship_cust_account_id: '||l_ship_cust_account_id,1,'N');
1527 END IF;
1528
1529 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
1530 p_api_version => 1.0,
1531 p_party_site_id => p_line_shipment_tbl(j).ship_to_party_site_id,
1532 p_role_type => 'SHIP_TO',
1533 p_cust_account_id => l_ship_cust_account_id,
1534 p_party_id => p_line_shipment_tbl(j).ship_to_party_id,
1535 p_cust_account_site => l_shp_cust_acct_site_id,
1536 x_return_status => x_return_status,
1537 x_msg_count => x_msg_count,
1538 x_msg_data => x_msg_data,
1539 x_cust_account_role_id => l_ship_to_contact_id);
1540
1541 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1542 aso_debug_pub.add('chk_ln: after Cust_Acct_Contact_Addr:l_ship_to_contact_id: '||l_ship_to_contact_id,1,'N');
1543 aso_debug_pub.add('chk_ln: after Cust_Acct_Contact_Addr:l_shp_cust_acct_site_id: '||l_shp_cust_acct_site_id,1,'N');
1544 END IF;
1545
1546 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1547 raise FND_API.G_EXC_ERROR;
1548 END IF;
1549
1550 END IF; -- l_ship_cust_account_id is not null
1551
1552 END LOOP; -- for shipment
1553
1554
1555 FND_MSG_PUB.Count_And_Get
1556 ( p_count => x_msg_count,
1557 p_data => x_msg_data
1558 );
1559
1560 EXCEPTION
1561 WHEN FND_API.G_EXC_ERROR THEN
1562 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1563 P_API_NAME => L_API_NAME
1564 ,P_PKG_NAME => G_PKG_NAME
1565 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1566 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1567 ,P_SQLCODE => SQLCODE
1568 ,P_SQLERRM => SQLERRM
1569 ,X_MSG_COUNT => X_MSG_COUNT
1570 ,X_MSG_DATA => X_MSG_DATA
1571 ,X_RETURN_STATUS => X_RETURN_STATUS);
1572
1573 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1574 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1575 P_API_NAME => L_API_NAME
1576 ,P_PKG_NAME => G_PKG_NAME
1577 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1578 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1579 ,P_SQLCODE => SQLCODE
1583 ,X_RETURN_STATUS => X_RETURN_STATUS);
1580 ,P_SQLERRM => SQLERRM
1581 ,X_MSG_COUNT => X_MSG_COUNT
1582 ,X_MSG_DATA => X_MSG_DATA
1584
1585 WHEN OTHERS THEN
1586 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1587 P_API_NAME => L_API_NAME
1588 ,P_PKG_NAME => G_PKG_NAME
1589 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1590 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1591 ,P_SQLCODE => SQLCODE
1592 ,P_SQLERRM => SQLERRM
1593 ,X_MSG_COUNT => X_MSG_COUNT
1594 ,X_MSG_DATA => X_MSG_DATA
1595 ,X_RETURN_STATUS => X_RETURN_STATUS);
1596
1597 END check_line_account_info;
1598
1599
1600 PROCEDURE Customer_Account(
1601 p_api_version IN NUMBER,
1602 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1603 p_commit IN VARCHAR2 := FND_API.g_false,
1604 p_Party_Id IN NUMBER,
1605 p_error_ret IN VARCHAR2 := FND_API.G_TRUE,
1606 p_calling_api_flag IN NUMBER := 0,
1607 x_Cust_Acct_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1608 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1609 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1610 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1611 IS
1612
1613 CURSOR C_Get_Account_Count(l_party_id NUMBER) IS
1614 SELECT count(rowid)
1615 FROM hz_cust_accounts
1616 WHERE party_id = l_party_id
1617 AND status = 'A'
1618 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
1619
1620 CURSOR C_Get_Account_User_Count(l_party_id NUMBER) IS
1621 SELECT count(a.rowid)
1622 from hz_cust_accounts a, hz_relationships b
1623 where a.party_id = b.object_id
1624 and a.status = 'A'
1625 AND sysdate BETWEEN NVL(a.account_activation_date, sysdate) AND NVL(a.account_termination_date, sysdate)
1626 and b.party_id = l_party_id
1627 and b.subject_table_name = 'HZ_PARTIES'
1628 and b.subject_type = 'PERSON';
1629
1630 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
1631 SELECT cust_account_id
1632 FROM hz_cust_accounts
1633 WHERE party_id = l_Party_Id
1634 AND status = 'A'
1635 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate)
1636 ORDER BY creation_date ASC;
1637
1638 CURSOR party_rec(l_party_id NUMBER) IS
1639 select party_type
1640 from hz_parties
1641 where party_id = l_party_id;
1642
1643 CURSOR party_rel_cur(l_party_id number) IS
1644 select object_id
1645 from hz_relationships
1646 where party_id = l_party_id
1647 and object_table_name = 'HZ_PARTIES'
1648 and subject_table_name = 'HZ_PARTIES'
1649 and subject_type = 'PERSON';
1650
1651 CURSOR account_user_cur(l_party_id NUMBER) IS
1652 select a.cust_account_id
1653 from hz_cust_accounts a, hz_relationships b
1654 where a.party_id = b.object_id
1655 and a.status = 'A'
1656 AND sysdate BETWEEN NVL(a.account_activation_date, sysdate) AND NVL(a.account_termination_date, sysdate)
1657 and b.party_id = l_party_id
1658 and b.subject_table_name = 'HZ_PARTIES'
1659 and b.subject_type = 'PERSON'
1660 ORDER BY a.creation_date ASC;
1661
1662 l_acct_count NUMBER := 0;
1663 lx_cust_id NUMBER := NULL;
1664 cust_account_id NUMBER;
1665 l_return_status VARCHAR2(1);
1666 l_party_type VARCHAR2(30);
1667 l_api_version CONSTANT NUMBER := 1.0;
1668 l_api_name CONSTANT VARCHAR2(45) := 'Customer_Account';
1669 l_object_party_id NUMBER;
1670 l_create_acct_prof VARCHAR2(30) := NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED');
1671
1672 BEGIN
1673
1674 ---- Initialize---------------------
1675
1676 SAVEPOINT CUSTOMER_ACCOUNT_PVT;
1677
1678 IF FND_API.to_boolean(p_init_msg_list) THEN
1679 FND_MSG_PUB.initialize;
1680 END IF;
1681
1682 IF NOT FND_API.compatible_api_call(
1683 l_api_version,
1684 p_api_version,
1685 l_api_name,
1686 g_pkg_name
1687 ) THEN
1688 RAISE FND_API.g_exc_unexpected_error;
1689 END IF;
1690
1691 x_return_status := FND_API.G_RET_STS_SUCCESS;
1692
1693 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1694 aso_debug_pub.add('Chk_TCA.Customer_Account Begin ',1,'N');
1695 aso_debug_pub.add('Customer_Account: p_party_id '||p_party_id,1,'N');
1696 aso_debug_pub.add('Customer_Account: p_error_ret '||p_error_ret,1,'N');
1697 aso_debug_pub.add('Customer_Account: p_calling_api_flag '||p_calling_api_flag,1,'N');
1698 END IF;
1699
1700 OPEN party_rec(p_party_id);
1701 FETCH party_rec INTO l_party_type;
1702 CLOSE party_rec;
1703
1704 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1705 aso_debug_pub.add('Cust_Acct:party type for sold_to = '|| l_party_type,1,'N');
1706 END IF;
1707 IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
1708
1712 CLOSE C_Get_Account_Count;
1709 IF p_error_ret = FND_API.G_TRUE AND p_calling_api_flag NOT IN (1,2) THEN
1710 OPEN C_Get_Account_Count(p_party_id);
1711 FETCH C_Get_Account_Count INTO l_acct_count;
1713
1714 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1715 aso_debug_pub.add('Cust_Acct:l_acct_count: '|| l_acct_count,1,'N');
1716 END IF;
1717 IF l_acct_count > 1 THEN
1718 x_return_status := FND_API.G_RET_STS_ERROR;
1719 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
1720 FND_MESSAGE.Set_Token('ID', to_char(p_Party_Id), FALSE);
1721 FND_MSG_PUB.ADD;
1722 RAISE FND_API.G_EXC_ERROR;
1723 END IF;
1724 END IF;
1725
1726 -- derive customer account
1727 OPEN C_get_cust_id_from_party_id(p_Party_Id);
1728 FETCH C_get_cust_id_from_party_id INTO lx_cust_id;
1729 CLOSE C_get_cust_id_from_party_id;
1730
1731 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1732 aso_debug_pub.add('Cust_Acct:cust acct id for sold_to = '|| lx_cust_id,1,'N');
1733 END IF;
1734
1735 ELSIF l_party_type = 'PARTY_RELATIONSHIP' THEN -- party_type
1736
1737 IF p_error_ret = FND_API.G_TRUE AND p_calling_api_flag NOT IN (1,2) THEN
1738 OPEN C_Get_Account_User_Count(p_party_id);
1739 FETCH C_Get_Account_User_Count INTO l_acct_count;
1740 CLOSE C_Get_Account_User_Count;
1741
1742 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1743 aso_debug_pub.add('Cust_Acct:l_acct_count: '|| l_acct_count,1,'N');
1744 END IF;
1745 IF l_acct_count > 1 THEN
1746 x_return_status := FND_API.G_RET_STS_ERROR;
1747 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
1748 FND_MESSAGE.Set_Token('ID', to_char(p_Party_Id), FALSE);
1749 FND_MSG_PUB.ADD;
1750 RAISE FND_API.G_EXC_ERROR;
1751 END IF;
1752 END IF;
1753
1754 OPEN account_user_cur(p_party_id);
1755 FETCH account_user_cur INTO lx_cust_id;
1756 CLOSE account_user_cur;
1757
1758 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1759 aso_debug_pub.add('cust acct id for sold_to = '|| lx_cust_id,1,'N');
1760 END IF;
1761 END IF; -- party_type
1762
1763 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1764 aso_debug_pub.add('checking for object id of relationship',1,'N');
1765 aso_debug_pub.add('lx_cust_id in rel = ' || lx_cust_id,1,'N');
1766 END IF;
1767
1768 IF lx_cust_id is NULL OR lx_cust_id = FND_API.G_MISS_NUM THEN
1769
1770 OPEN party_rel_cur(p_party_id);
1771 FETCH party_rel_cur INTO l_object_party_id;
1772 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1773 aso_debug_pub.add('object_id = ' || l_object_party_id,1,'N');
1774 END IF;
1775 IF party_rel_cur%NOTFOUND THEN
1776 -- x_return_status := FND_API.G_RET_STS_ERROR;
1777 CLOSE party_rel_cur;
1778 ELSE
1779 IF p_error_ret = FND_API.G_TRUE AND p_calling_api_flag NOT IN (1,2) THEN
1780 OPEN C_Get_Account_Count(l_object_party_id);
1781 FETCH C_Get_Account_Count INTO l_acct_count;
1782 CLOSE C_Get_Account_Count;
1783 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1784 aso_debug_pub.add('Cust_Acct:l_acct_count: '|| l_acct_count,1,'N');
1785 END IF;
1786
1787 IF l_acct_count > 1 THEN
1788 x_return_status := FND_API.G_RET_STS_ERROR;
1789 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
1790 FND_MESSAGE.Set_Token('ID', to_char(l_object_party_id), FALSE);
1791 FND_MSG_PUB.ADD;
1792 RAISE FND_API.G_EXC_ERROR;
1793 END IF;
1794 END IF;
1795
1796 OPEN account_user_cur(l_object_party_id);
1797 FETCH account_user_cur INTO lx_cust_id;
1798 CLOSE account_user_cur;
1799 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1800 aso_debug_pub.add('cust acct id for sold_to = '|| lx_cust_id,1,'N');
1801 END IF;
1802
1803 END IF; -- party_rel_cur
1804
1805 END IF; -- lx_cust_id
1806 -- create customer account
1807 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1808 aso_debug_pub.add('creating customer account',1,'N');
1809 END IF;
1810 IF lx_cust_id IS NULL OR lx_cust_id = FND_API.G_MISS_NUM THEN
1811 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1812 aso_debug_pub.add('Cust_Acct:l_create_acct_prof: '|| l_create_acct_prof,1,'N');
1813 END IF;
1814 IF p_calling_api_flag = 1 AND (l_create_acct_prof = 'PLACE_ORDER') THEN
1815 x_return_status := FND_API.G_RET_STS_ERROR;
1816 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
1817 FND_MSG_PUB.ADD;
1818 RAISE FND_API.G_EXC_ERROR;
1819 END IF;
1820
1821 IF (l_create_acct_prof = 'ALWAYS') OR
1822 (l_create_acct_prof = 'AS_REQUIRED' AND p_calling_api_flag = 1) OR
1823 (p_calling_api_flag = 2) THEN
1824 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1825 aso_debug_pub.add('creating customer account: create_acct_prof: '||l_create_acct_prof,1,'N');
1826 END IF;
1827 IF p_Party_Id is not NULL
1828 AND p_Party_Id <> FND_API.G_MISS_NUM THEN
1829 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1830 aso_debug_pub.add('creating cust account',1,'N');
1831 END IF;
1832 ASO_PARTY_INT.Create_Customer_Account(
1833 p_api_version => 1.0,
1837 x_msg_data => x_msg_data,
1834 P_Party_id => p_Party_Id,
1835 x_return_status => x_return_status,
1836 x_msg_count => x_msg_count,
1838 x_cust_acct_id => cust_account_id);
1839 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1840 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1841 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
1842 FND_MESSAGE.Set_Token('ID', to_char(p_Party_Id), FALSE);
1843 FND_MSG_PUB.ADD;
1844 END IF;
1845 raise FND_API.G_EXC_ERROR;
1846 END IF;
1847 x_Cust_Acct_Id := cust_account_id;
1848 END IF; -- end party if
1849 ELSIF p_calling_api_flag <> 0 THEN -- profile is N raise error
1850 x_return_status := FND_API.G_RET_STS_ERROR;
1851 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1852 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
1853 FND_MESSAGE.Set_Token('ID', to_char(p_Party_Id), FALSE);
1854 FND_MSG_PUB.ADD;
1855 END IF;
1856 raise FND_API.G_EXC_ERROR;
1857 END IF; -- end profile condition
1858
1859 ELSE -- lx_cust_id not null
1860
1861 x_Cust_Acct_Id := lx_cust_id;
1862
1863 END IF; -- lx_cust_id
1864
1865 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1866 aso_debug_pub.add('x_cust_acct_id = '|| x_cust_acct_id,1,'N');
1867 END IF;
1868 FND_MSG_PUB.Count_And_Get
1869 ( p_count => x_msg_count,
1870 p_data => x_msg_data
1871 );
1872
1873 EXCEPTION
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1876 P_API_NAME => L_API_NAME
1877 ,P_PKG_NAME => G_PKG_NAME
1878 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1879 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1880 ,P_SQLCODE => SQLCODE
1881 ,P_SQLERRM => SQLERRM
1882 ,X_MSG_COUNT => X_MSG_COUNT
1883 ,X_MSG_DATA => X_MSG_DATA
1884 ,X_RETURN_STATUS => X_RETURN_STATUS);
1885
1886 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1887 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1888 P_API_NAME => L_API_NAME
1889 ,P_PKG_NAME => G_PKG_NAME
1890 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1891 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1892 ,P_SQLCODE => SQLCODE
1893 ,P_SQLERRM => SQLERRM
1894 ,X_MSG_COUNT => X_MSG_COUNT
1895 ,X_MSG_DATA => X_MSG_DATA
1896 ,X_RETURN_STATUS => X_RETURN_STATUS);
1897
1898 WHEN OTHERS THEN
1899 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1900 P_API_NAME => L_API_NAME
1901 ,P_PKG_NAME => G_PKG_NAME
1902 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1903 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1904 ,P_SQLCODE => SQLCODE
1905 ,P_SQLERRM => SQLERRM
1906 ,X_MSG_COUNT => X_MSG_COUNT
1907 ,X_MSG_DATA => X_MSG_DATA
1908 ,X_RETURN_STATUS => X_RETURN_STATUS);
1909
1910 END Customer_Account;
1911
1912
1913
1914 PROCEDURE Customer_Account_Site
1915 (
1916 p_api_version IN NUMBER,
1917 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1918 p_commit IN VARCHAR2 := FND_API.g_false,
1919 p_party_site_id IN NUMBER,
1920 p_acct_site_type IN VARCHAR2,
1921 p_cust_account_id IN NUMBER,
1922 x_cust_acct_site_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1923 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1924 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1925 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1926 x_site_use_id OUT NOCOPY /* file.sql.39 change */ number
1927 )
1928 IS
1929 CURSOR party_cur IS
1930 SELECT a.party_type, a.party_id
1931 from
1932 HZ_PARTIES a, HZ_PARTY_SITES b
1933 where
1934 a.status = 'A'
1935 and b.status = 'A'
1936 and b.party_site_id = p_party_site_id
1937 and b.party_id = a.party_id;
1938
1939 CURSOR active_site_cur IS
1940 select cust_acct_site_id, status
1941 from
1942 hz_cust_acct_sites
1943 where
1944 cust_account_id = p_cust_account_id
1945 and party_site_id = p_party_site_id
1946 and status = 'A';
1947
1948 CURSOR inactive_site_cur IS
1949 select cust_acct_site_id, status
1950 from
1951 hz_cust_acct_sites
1952 where
1953 cust_account_id = p_cust_account_id
1954 and party_site_id = p_party_site_id
1955 and status <> 'A';
1956
1957 CURSOR active_site_use_cur(l_acct_site_id NUMBER, l_site_type VARCHAR2) IS
1958 select site_use_id, status
1959 from
1960 hz_cust_site_uses
1961 where
1962 cust_acct_site_id = l_acct_site_id
1963 and site_use_code = l_site_type
1967 select site_use_id, status
1964 and status = 'A';
1965
1966 CURSOR inactive_site_use_cur(l_acct_site_id NUMBER, l_site_type VARCHAR2) IS
1968 from
1969 hz_cust_site_uses
1970 where
1971 cust_acct_site_id = l_acct_site_id
1972 and site_use_code = l_site_type
1973 and status <> 'A';
1974
1975
1976 /*
1977 CURSOR site_use_cur(cust_acct NUMBER, party_site NUMBER, site_type VARCHAR2) IS
1978 select a.site_use_id, b.status,a.status
1979 from
1980 hz_cust_site_uses a, hz_cust_acct_sites b
1981 where
1982 b.cust_account_id = cust_acct
1983 and b.party_site_id = party_site
1984 and a.cust_acct_site_id = b.cust_acct_site_id
1985 and a.site_use_code = site_type;
1986 */
1987 l_party_id number;
1988 cur_party_id number;
1989 l_cust_account_id number;
1990 cust_acct_site_status varchar2(1);
1991 cust_site_use_status varchar2(1);
1992
1993 CURSOR relationship_cur IS
1994 select a.object_id
1995 from
1996 hz_relationships a, hz_cust_accounts b
1997 where a.party_id = l_party_id
1998 and a.object_id = b.party_id
1999 and b.cust_account_id = p_cust_account_id;
2000
2001 l_api_version CONSTANT NUMBER := 1.0;
2002 l_api_name CONSTANT VARCHAR2(45) := 'Customer_Account_Site';
2003
2004 l_party_type VARCHAR2(30);
2005 l_acct_site_type VARCHAR2(50);
2006 l_site_use_id number;
2007
2008 lx_cust_acct_site_id NUMBER;
2009
2010 BEGIN
2011
2012 ---- Initialize---------------------
2013
2014 SAVEPOINT CUSTOMER_ACCOUNT_SITE_PVT;
2015
2016 IF FND_API.to_boolean(p_init_msg_list) THEN
2017 FND_MSG_PUB.initialize;
2018 END IF;
2019
2020 IF NOT FND_API.compatible_api_call(
2021 l_api_version,
2022 p_api_version,
2023 l_api_name,
2024 g_pkg_name
2025 ) THEN
2026 RAISE FND_API.g_exc_unexpected_error;
2027 END IF;
2028
2029 x_return_status := FND_API.G_RET_STS_SUCCESS;
2030
2031 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2032 aso_debug_pub.add('site type in Customer_Account_Site = ' || p_acct_site_type,1,'N');
2033 END IF;
2034
2035 IF p_acct_site_type = 'END_USER' THEN
2036 l_acct_site_type := 'SHIP_TO';
2037 ELSE
2038 l_acct_site_type := p_acct_site_type;
2039 END IF;
2040
2041 OPEN party_cur;
2042 FETCH party_cur INTO l_party_type, l_party_id;
2043 IF (party_cur%NOTFOUND) THEN
2044 l_party_type := NULL;
2045
2046 x_return_status := FND_API.G_RET_STS_ERROR;
2047 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2048 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
2049 FND_MSG_PUB.ADD;
2050 END IF;
2051 raise FND_API.G_EXC_ERROR;
2052 END IF;
2053 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2054 aso_debug_pub.add('party_type in Customer_Account_Site = ' || l_party_type,1,'N');
2055 END IF;
2056 CLOSE party_cur;
2057
2058 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2059 OPEN relationship_cur;
2060 FETCH relationship_cur INTO cur_party_id;
2061 IF (relationship_cur%NOTFOUND) THEN
2062 cur_party_id := NULL;
2063 x_return_status := FND_API.G_RET_STS_ERROR;
2064 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2065 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
2066 FND_MSG_PUB.ADD;
2067 END IF;
2068 raise FND_API.G_EXC_ERROR;
2069 END IF;
2070 CLOSE relationship_cur;
2071 ELSE
2072 cur_party_id := l_party_id;
2073 END IF;
2074
2075 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2076 aso_debug_pub.add('cur_party_id = ' || cur_party_id,1,'N');
2077 END IF;
2078
2079 -- bug 4947772
2080 OPEN active_site_cur;
2081 FETCH active_site_cur INTO x_cust_acct_site_id, cust_acct_site_status;
2082 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2083 aso_debug_pub.add('x_cust_acct_site_id in Customer_Account_Site = ' || x_cust_acct_site_id,1,'N');
2084 aso_debug_pub.add('Customer_Account_Site: cust_acct_site_status: ' || cust_acct_site_status,1,'N');
2085 END IF;
2086
2087 IF active_site_cur%NOTFOUND THEN
2088
2089 -- this means there are no active sites
2090 OPEN inactive_site_cur;
2091 FETCH inactive_site_cur INTO x_cust_acct_site_id, cust_acct_site_status;
2092
2093 IF inactive_site_cur%FOUND THEN
2094 x_cust_acct_site_id := NULL;
2095 x_site_use_id := NULL;
2096 x_return_status := FND_API.G_RET_STS_ERROR;
2097 IF p_acct_site_type = 'BILL_TO' THEN
2098 FND_MESSAGE.Set_Name('ASO','ASO_INV_CUST_SITE_INACTIVE');
2099 ELSIF p_acct_site_type = 'END_USER' THEN
2100 FND_MESSAGE.Set_Name('ASO','ASO_END_CUST_SITE_INACTIVE');
2101 ELSIF p_acct_site_type = 'SHIP_TO' THEN
2102 FND_MESSAGE.Set_Name('ASO','ASO_SHP_CUST_SITE_INACTIVE');
2103 END IF;
2104 FND_MSG_PUB.ADD;
2105 raise FND_API.G_EXC_ERROR;
2106 END IF;
2110
2107 CLOSE inactive_site_cur;
2108 END IF; -- active_site_cur%NOTFOUND end if
2109 CLOSE active_site_cur;
2111
2112
2113
2114 IF (x_cust_acct_site_id IS NOT NULL) AND
2115 (x_cust_acct_site_id <> FND_API.G_MISS_NUM) THEN
2116
2117
2118 OPEN active_site_use_cur(x_cust_acct_site_id, l_acct_site_type);
2119 FETCH active_site_use_cur INTO x_site_use_id, cust_site_use_status;
2120 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2121 aso_debug_pub.add('Customer_Account_Site: cust_site_use_status: ' || cust_site_use_status,1,'N');
2122 aso_debug_pub.add('Customer_Account_Site: x_site_use_id: ' || x_site_use_id,1,'N');
2123 END IF;
2124 IF (active_site_use_cur%NOTFOUND) THEN
2125 -- this means there are no active site uses
2126 OPEN inactive_site_use_cur(x_cust_acct_site_id, l_acct_site_type);
2127 FETCH inactive_site_use_cur INTO x_site_use_id, cust_site_use_status;
2128 IF inactive_site_use_cur%FOUND THEN
2129 x_site_use_id := NULL;
2130 x_return_status := FND_API.G_RET_STS_ERROR;
2131 IF p_acct_site_type = 'BILL_TO' THEN
2132 FND_MESSAGE.Set_Name('ASO','ASO_INV_CUST_SITE_INACTIVE');
2133 ELSIF p_acct_site_type = 'END_USER' THEN
2134 FND_MESSAGE.Set_Name('ASO','ASO_END_CUST_SITE_INACTIVE');
2135 ELSIF p_acct_site_type = 'SHIP_TO' THEN
2136 FND_MESSAGE.Set_Name('ASO','ASO_SHP_CUST_SITE_INACTIVE');
2137 END IF;
2138 FND_MSG_PUB.ADD;
2139 raise FND_API.G_EXC_ERROR;
2140 ELSE
2141 -- this means that there no site uses , either active or inactive
2142 x_site_use_id := NULL;
2143 END IF;
2144 CLOSE inactive_site_use_cur;
2145 END IF;
2146 CLOSE active_site_use_cur;
2147
2148 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2149 aso_debug_pub.add('site_use_id in Customer_Account_Site = ' || x_site_use_id,1,'N');
2150 END IF;
2151
2152 END IF;
2153
2154 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2155 aso_debug_pub.add('Customer_Account_Site: x_site_use_id: ' || x_site_use_id,1,'N');
2156 END IF;
2157 IF x_site_use_id is NULL OR
2158 x_site_use_id = FND_API.G_MISS_NUM THEN
2159
2160 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2161 aso_debug_pub.add('calling create accsite uses ',1, 'N');
2162 END IF;
2163 ASO_PARTY_INT.Create_ACCT_SITE_USES (
2164 p_api_version => 1.0
2165 ,P_Cust_Account_Id => p_cust_account_id
2166 ,P_Party_Site_Id => p_party_site_id
2167 ,P_cust_acct_site_id => x_cust_acct_site_id
2168 ,P_Acct_Site_type => l_Acct_Site_Type
2169 ,x_cust_acct_site_id => lx_cust_acct_site_id
2170 ,x_return_status => x_return_status
2171 ,x_msg_count => x_msg_count
2172 ,x_msg_data => x_msg_data
2173 ,x_site_use_id => x_site_use_id
2174 );
2175
2176 x_cust_acct_site_id := lx_cust_acct_site_id;
2177
2178 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2179 aso_debug_pub.add('site_use_id after deriving invoice = ' || x_site_use_id,1, 'Y');
2180 END IF;
2181 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2182 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2183 FND_MESSAGE.Set_Name('ASO', 'ASO_ACCT_SITE_USE');
2184 FND_MESSAGE.Set_Token('ID', to_char(p_party_site_id),FALSE);
2185 FND_MSG_PUB.ADD;
2186 END IF;
2187 raise FND_API.G_EXC_ERROR;
2188 END IF;
2189 END IF;
2190
2191 FND_MSG_PUB.Count_And_Get
2192 ( p_count => x_msg_count,
2193 p_data => x_msg_data
2194 );
2195
2196 EXCEPTION
2197 WHEN FND_API.G_EXC_ERROR THEN
2198 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2199 P_API_NAME => L_API_NAME
2200 ,P_PKG_NAME => G_PKG_NAME
2201 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2202 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2203 ,P_SQLCODE => SQLCODE
2204 ,P_SQLERRM => SQLERRM
2205 ,X_MSG_COUNT => X_MSG_COUNT
2206 ,X_MSG_DATA => X_MSG_DATA
2207 ,X_RETURN_STATUS => X_RETURN_STATUS);
2208
2209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2210 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2211 P_API_NAME => L_API_NAME
2212 ,P_PKG_NAME => G_PKG_NAME
2213 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2214 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2215 ,P_SQLCODE => SQLCODE
2216 ,P_SQLERRM => SQLERRM
2217 ,X_MSG_COUNT => X_MSG_COUNT
2218 ,X_MSG_DATA => X_MSG_DATA
2219 ,X_RETURN_STATUS => X_RETURN_STATUS);
2220
2221 WHEN OTHERS THEN
2222 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2223 P_API_NAME => L_API_NAME
2224 ,P_PKG_NAME => G_PKG_NAME
2225 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2226 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2230 ,X_MSG_DATA => X_MSG_DATA
2227 ,P_SQLCODE => SQLCODE
2228 ,P_SQLERRM => SQLERRM
2229 ,X_MSG_COUNT => X_MSG_COUNT
2231 ,X_RETURN_STATUS => X_RETURN_STATUS);
2232
2233 END Customer_Account_Site;
2234
2235
2236
2237 PROCEDURE Cust_Acct_Relationship(
2238 p_api_version IN NUMBER,
2239 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2240 p_commit IN VARCHAR2 := FND_API.g_false,
2241 p_sold_to_cust_account IN NUMBER,
2242 p_related_cust_account IN NUMBER,
2243 p_relationship_type IN VARCHAR2,
2244 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2245 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2246 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2247 ) IS
2248
2249 l_api_version CONSTANT NUMBER := 1.0;
2250 l_api_name CONSTANT VARCHAR2(45) := 'CUST_ACCT_RELATIONSHIP';
2251
2252 -- TRUE if there are matching row for the cust_account_id and relate_cust_account_id.
2253 l_cust_acct_match VARCHAR2(1) := FND_API.G_FALSE;
2254 -- TRUE if the matching rows also match the bill_to_flag or ship_to_flag.
2255 l_cust_acct_flag_match VARCHAR2(1) := FND_API.G_FALSE;
2256
2257 l_bill_to_flag VARCHAR2(1);
2258 l_ship_to_flag VARCHAR2(1);
2259 l_last_update_date DATE;
2260
2261 CURSOR c_cust_acct_relate IS
2262 SELECT bill_to_flag, ship_to_flag, last_update_date
2263 FROM hz_cust_acct_relate
2264 WHERE cust_account_id = p_related_cust_account
2265 AND related_cust_account_id = p_sold_to_cust_account;
2266
2267 BEGIN
2268 ---- Initialize---------------------
2269
2270 SAVEPOINT CUST_ACCT_RELATIONSHIP_PVT;
2271
2272 IF FND_API.to_boolean(p_init_msg_list) THEN
2273 FND_MSG_PUB.initialize;
2274 END IF;
2275
2276 IF NOT FND_API.compatible_api_call(
2277 l_api_version,
2278 p_api_version,
2279 l_api_name,
2280 g_pkg_name
2281 ) THEN
2282 RAISE FND_API.g_exc_unexpected_error;
2283 END IF;
2284
2285 x_return_status := FND_API.g_ret_sts_success;
2286 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2287 aso_debug_pub.add('Entering cust acct relationship ',1, 'Y');
2288 END IF;
2289
2290 OPEN c_cust_acct_relate;
2291 FETCH c_cust_acct_relate
2292 INTO l_bill_to_flag, l_ship_to_flag, l_last_update_date;
2293 IF c_cust_acct_relate%FOUND THEN
2294 l_cust_acct_match := FND_API.G_TRUE;
2295 END IF;
2296 CLOSE c_cust_acct_relate;
2297
2298 IF FND_API.TO_Boolean(l_cust_acct_match) THEN
2299 IF p_relationship_type = 'BILL_TO' AND l_bill_to_flag = 'Y' THEN
2300 l_cust_acct_flag_match := FND_API.G_TRUE;
2301 ELSIF p_relationship_type = 'SHIP_TO' AND l_ship_to_flag = 'Y' THEN
2302 l_cust_acct_flag_match := FND_API.G_TRUE;
2303 END IF;
2304 END IF;
2305
2306 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2307 aso_debug_pub.add('cust acct relationship: l_cust_acct_match is ' || l_cust_acct_match,1, 'N');
2308 END IF;
2309 IF NOT FND_API.TO_Boolean(l_cust_acct_flag_match) THEN
2310 -- no matching rows
2311 IF NOT FND_API.TO_Boolean(l_cust_acct_match) THEN
2312 -- the account ids are not matched.
2313 ASO_PARTY_INT.Create_Cust_Acct_Relationship(
2314 -- Create_Cust_Acct_Relationship(
2315 p_api_version => 1.0,
2316 p_sold_to_cust_account => p_sold_to_cust_account,
2317 p_related_cust_account => p_related_cust_account,
2318 p_relationship_type => p_relationship_type,
2319 x_return_status => x_return_status,
2320 x_msg_count => x_msg_count,
2321 x_msg_data => x_msg_data
2322 );
2323 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2324 RAISE FND_API.G_EXC_ERROR;
2325 END IF;
2326 ELSE
2327 -- the account ids are matched but bill_to_flag or ship_to_flag is not matched.
2328 -- and the profile is 'Y'.
2329 x_return_status := FND_API.G_RET_STS_ERROR;
2330 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2331 FND_MESSAGE.Set_Name(' + appShortName +', 'ASO_INVALID_ACCT_RELTN');
2332 FND_MESSAGE.Set_Token('TYPE', p_relationship_type, FALSE);
2333 FND_MSG_PUB.ADD;
2334 END IF;
2335 RAISE FND_API.G_EXC_ERROR;
2336
2337 END IF;
2338
2339 END IF; -- l_cust_acct_flag_match
2340
2341
2342 FND_MSG_PUB.Count_And_Get
2343 ( p_count => x_msg_count,
2344 p_data => x_msg_data
2345 );
2346
2347 EXCEPTION
2348 WHEN FND_API.G_EXC_ERROR THEN
2349 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2350 P_API_NAME => L_API_NAME
2351 ,P_PKG_NAME => G_PKG_NAME
2352 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2353 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2354 ,P_SQLCODE => SQLCODE
2355 ,P_SQLERRM => SQLERRM
2356 ,X_MSG_COUNT => X_MSG_COUNT
2357 ,X_MSG_DATA => X_MSG_DATA
2358 ,X_RETURN_STATUS => X_RETURN_STATUS);
2359
2360 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2361 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2362 P_API_NAME => L_API_NAME
2363 ,P_PKG_NAME => G_PKG_NAME
2367 ,P_SQLERRM => SQLERRM
2364 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2365 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2366 ,P_SQLCODE => SQLCODE
2368 ,X_MSG_COUNT => X_MSG_COUNT
2369 ,X_MSG_DATA => X_MSG_DATA
2370 ,X_RETURN_STATUS => X_RETURN_STATUS);
2371
2372 WHEN OTHERS THEN
2373 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2374 P_API_NAME => L_API_NAME
2375 ,P_PKG_NAME => G_PKG_NAME
2376 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2377 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2378 ,P_SQLCODE => SQLCODE
2379 ,P_SQLERRM => SQLERRM
2380 ,X_MSG_COUNT => X_MSG_COUNT
2381 ,X_MSG_DATA => X_MSG_DATA
2382 ,X_RETURN_STATUS => X_RETURN_STATUS);
2383
2384 END Cust_Acct_Relationship;
2385
2386
2387 PROCEDURE Cust_Acct_Contact_Addr
2388 (
2389 p_api_version IN NUMBER,
2390 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2391 p_commit IN VARCHAR2 := FND_API.g_false,
2392 p_party_site_id IN NUMBER,
2393 p_role_type IN VARCHAR2,
2394 p_cust_account_id IN NUMBER,
2395 p_party_id IN NUMBER,
2396 p_cust_account_site IN NUMBER,
2397 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2398 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2399 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2400 x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */ number
2401 )
2402 IS
2403 l_contact_id NUMBER;
2404 l_contact_party_id NUMBER;
2405 l_api_version CONSTANT NUMBER := 1.0;
2406 l_api_name CONSTANT VARCHAR2(45) := 'Cust_Acct_Contact_Addr';
2407 l_role_type VARCHAR2(50);
2408
2409 BEGIN
2410 ---- Initialize---------------------
2411 SAVEPOINT CUST_ACCT_CONTACT_ADDR_PVT;
2412
2413 IF FND_API.to_boolean(p_init_msg_list) THEN
2414 FND_MSG_PUB.initialize;
2415 END IF;
2416
2417 IF NOT FND_API.compatible_api_call(
2418 l_api_version,
2419 p_api_version,
2420 l_api_name,
2421 g_pkg_name
2422 ) THEN
2423 RAISE FND_API.g_exc_unexpected_error;
2424 END IF;
2425
2426 x_return_status := FND_API.G_RET_STS_SUCCESS;
2427
2428 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2429 aso_debug_pub.add('Cust_acct_contact_Addr: p_role_type: '||p_role_type,1,'N');
2430 END IF;
2431
2432 IF p_role_type = 'END_USER' THEN
2433 l_role_type := 'SHIP_TO';
2434 ELSE
2435 l_role_type := p_role_type;
2436 END IF;
2437
2438 IF p_party_id is not NULL
2439 AND p_party_id<> FND_API.G_MISS_NUM THEN
2440
2441 IF p_party_site_id is not NULL AND
2442 p_party_site_id <> FND_API.G_MISS_NUM THEN
2443
2444 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2445 aso_debug_pub.add('deriving cust acct role for party id ',1,'N');
2446 END IF;
2447
2448 ASO_MAP_QUOTE_ORDER_INT.get_cust_acct_roles(
2449 p_party_id =>p_party_id
2450 ,p_party_site_id => p_party_site_id
2451 ,p_acct_site_type => l_role_type
2452 ,p_cust_account_id => p_cust_account_id
2453 ,x_return_status => x_return_status
2454 ,x_party_id => l_contact_party_id
2455 ,x_cust_account_role_id => l_contact_id);
2456 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2457 aso_debug_pub.add('status after get cust acct roles in Cust_Acct_Contact_Addr = ' || x_return_status,1,'N');
2458 END IF;
2459 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2460 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2461 IF p_role_type = 'BILL_TO' THEN
2462 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_PARTY_AC_CRS');
2463 ELSIF p_role_type = 'END_USER' THEN
2464 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_END_PARTY_AC_CRS');
2465 ELSIF p_role_type = 'SHIP_TO' THEN
2466 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_SHP_PARTY_AC_CRS');
2467 END IF;
2468 FND_MSG_PUB.ADD;
2469 END IF;
2470 raise FND_API.G_EXC_ERROR;
2471
2472 ELSE -- x_ret_status = success
2473 IF l_contact_party_id <> FND_API.G_MISS_NUM THEN
2474 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2475 aso_debug_pub.add('cust account site = ' ||p_cust_account_site,1,'N');
2476 END IF;
2477 ASO_PARTY_INT.Create_Contact_Role (
2478 p_api_version => 1.0
2479 ,p_party_id => l_contact_party_id
2480 ,p_Cust_account_id => p_cust_account_id
2481 ,p_cust_account_site_id => p_cust_account_site
2482 ,p_responsibility_type => l_role_type
2483 ,p_role_id => l_contact_id
2484 ,x_return_status =>x_return_status
2485 ,x_msg_count => x_msg_count
2486 ,x_msg_data => x_msg_data
2487 ,x_cust_account_role_id => x_cust_account_role_id);
2488
2489 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2490 aso_debug_pub.add('status after create contact role ship = '|| x_return_status,1,'N');
2491 END IF;
2492
2496 FND_MESSAGE.Set_Token('ID', to_char(
2493 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2494 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2495 FND_MESSAGE.Set_Name('ASO', 'ASO_CREATE_CONTACT');
2497 l_contact_party_id), FALSE);
2498 FND_MSG_PUB.ADD;
2499 END IF;
2500 raise FND_API.G_EXC_ERROR;
2501 END IF; -- for creatE_contact_role
2502
2503 END IF; -- for contact_party_id
2504
2505 END IF; -- x_ret_status
2506
2507 END IF; -- for party site
2508
2509 END IF; -- for party id
2510
2511 FND_MSG_PUB.Count_And_Get
2512 ( p_count => x_msg_count,
2513 p_data => x_msg_data
2514 );
2515
2516 EXCEPTION
2517 WHEN FND_API.G_EXC_ERROR THEN
2518 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2519 P_API_NAME => L_API_NAME
2520 ,P_PKG_NAME => G_PKG_NAME
2521 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2522 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2523 ,P_SQLCODE => SQLCODE
2524 ,P_SQLERRM => SQLERRM
2525 ,X_MSG_COUNT => X_MSG_COUNT
2526 ,X_MSG_DATA => X_MSG_DATA
2527 ,X_RETURN_STATUS => X_RETURN_STATUS);
2528
2529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2530 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2531 P_API_NAME => L_API_NAME
2532 ,P_PKG_NAME => G_PKG_NAME
2533 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2534 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2535 ,P_SQLCODE => SQLCODE
2536 ,P_SQLERRM => SQLERRM
2537 ,X_MSG_COUNT => X_MSG_COUNT
2538 ,X_MSG_DATA => X_MSG_DATA
2539 ,X_RETURN_STATUS => X_RETURN_STATUS);
2540 WHEN OTHERS THEN
2541 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2542 P_API_NAME => L_API_NAME
2543 ,P_PKG_NAME => G_PKG_NAME
2544 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2545 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2546 ,P_SQLCODE => SQLCODE
2547 ,P_SQLERRM => SQLERRM
2548 ,X_MSG_COUNT => X_MSG_COUNT
2549 ,X_MSG_DATA => X_MSG_DATA
2550 ,X_RETURN_STATUS => X_RETURN_STATUS);
2551
2552 END Cust_Acct_Contact_Addr;
2553
2554
2555
2556 PROCEDURE Assign_Customer_Accounts(
2557 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2558 p_qte_header_id IN NUMBER,
2559 p_calling_api_flag IN NUMBER := 0,
2560 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2561 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2562 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2563 )
2564 IS
2565
2566 CURSOR C_Validate_Quote (qte_hdr NUMBER) IS
2567 SELECT 'X'
2568 FROM aso_quote_headers_all
2569 WHERE quote_header_id = qte_hdr;
2570
2571 CURSOR C_Get_Hdr_Acct(qte_hdr NUMBER) IS
2572 SELECT a.cust_account_id, a.invoice_to_cust_account_id, a.cust_party_id,
2573 a.invoice_to_cust_party_id, a.party_id, a.sold_to_party_site_id,
2574 a.invoice_to_party_id, a.invoice_to_party_site_id,
2575 a.End_Customer_party_id, a.End_Customer_party_site_id,
2576 a.End_Customer_cust_party_id, a.End_Customer_cust_account_id,
2577 b.ship_to_party_id, b.ship_to_party_site_id,
2578 b.ship_to_cust_party_id, b.ship_to_cust_account_id,
2579 b.shipment_id
2580 FROM aso_quote_headers_all a, aso_shipments b
2581 WHERE a.quote_header_id = qte_hdr
2582 AND a.quote_header_id = b.quote_header_id
2583 AND b.quote_line_id is NULL;
2584
2585
2586 CURSOR C_Get_Line_Acct(qte_hdr NUMBER) IS
2587 SELECT a.invoice_to_cust_account_id, a.invoice_to_cust_party_id, a.quote_line_id,
2588 a.invoice_to_party_id, a.invoice_to_party_site_id,
2589 a.End_Customer_cust_account_id, a.End_Customer_cust_party_id,
2590 a.End_Customer_party_id, a.End_Customer_party_site_id,
2591 b.ship_to_cust_account_id, b.ship_to_cust_party_id, b.shipment_id,
2592 b.ship_to_party_id, b.ship_to_party_site_id
2593 FROM aso_quote_lines_all a, aso_shipments b
2594 WHERE a.quote_header_id = qte_hdr
2595 AND a.quote_line_id = b.quote_line_id
2596 AND ((a.invoice_to_cust_account_id IS NULL
2597 AND a.invoice_to_cust_party_id IS NOT NULL)
2598 OR (a.End_Customer_cust_account_id is NULL
2599 AND a.End_Customer_cust_party_id IS NOT NULL)
2600 OR (b.ship_to_cust_account_id is NULL
2601 AND b.ship_to_cust_party_id IS NOT NULL));
2602
2603 l_end_cust_acct NUMBER;
2604 l_end_cust_party NUMBER;
2605 l_cust_acct NUMBER;
2606 l_inv_cust_acct NUMBER;
2607 l_cust_party NUMBER;
2608 l_inv_cust_party NUMBER;
2609 l_account_id NUMBER;
2610 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
2611 l_header_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2612 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
2613 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2614 l_header_check_tca_flag VARCHAR(1) := 'N';
2615 l_dummy VARCHAR2(1) := NULL;
2616 l_last_update_date date := SYSDATE;
2617 l_g_user_id number := fnd_global.user_id;
2618 l_g_login_id number := fnd_global.conc_login_id;
2619 l_api_version CONSTANT NUMBER := 1.0;
2620 l_api_name CONSTANT VARCHAR2(45) := 'Assign_Customer_Accounts';
2621
2622 BEGIN
2623
2624 -- Standard Start of API savepoint
2625 SAVEPOINT Assign_Customer_Accounts_PVT;
2626
2627 -- Initialize message list if p_init_msg_list is set to TRUE.
2631
2628 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2629 FND_MSG_PUB.initialize;
2630 END IF;
2632 -- Initialize API return status to success
2633 x_return_status := FND_API.G_RET_STS_SUCCESS;
2634
2635 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2636 aso_debug_pub.add('Assign_Customer_Accounts - Begin p_qte_header_id:'|| p_qte_header_id, 1, 'Y');
2637 END IF;
2638
2639 OPEN C_Validate_Quote (p_qte_header_id);
2640 FETCH C_Validate_Quote into l_dummy;
2641 IF C_Validate_Quote%NOTFOUND THEN
2642 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2643 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
2644 FND_MESSAGE.Set_Token('COLUMN', 'ORIGINAL_QUOTE_ID', FALSE);
2645 FND_MESSAGE.Set_Token('VALUE', TO_CHAR(p_qte_header_id), FALSE);
2646 FND_MSG_PUB.ADD;
2647 END IF;
2648
2649 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2650 aso_debug_pub.add('Assign_Customer_Accounts - Invalid qte_hdr_id', 1, 'Y');
2651 END IF;
2652
2653 CLOSE C_Validate_Quote;
2654 x_return_status := FND_API.G_RET_STS_ERROR;
2655 RAISE FND_API.G_EXC_ERROR;
2656 END IF;
2657 CLOSE C_Validate_Quote;
2658
2659 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2660 aso_debug_pub.add('Assign_Customer_Accounts - After qte_hdr_id check', 1, 'Y');
2661 END IF;
2662
2663 OPEN C_Get_Hdr_Acct(p_qte_header_id);
2664 FETCH C_Get_Hdr_Acct INTO
2665 l_qte_header_rec.cust_account_id,
2666 l_qte_header_rec.invoice_to_cust_account_id,
2667 l_qte_header_rec.cust_party_id,
2668 l_qte_header_rec.invoice_to_cust_party_id,
2669 l_qte_header_rec.party_id,
2670 l_qte_header_rec.sold_to_party_site_id,
2671 l_qte_header_rec.invoice_to_party_id,
2672 l_qte_header_rec.invoice_to_party_site_id,
2673 l_qte_header_rec.End_Customer_party_id,
2674 l_qte_header_rec.End_Customer_party_site_id,
2675 l_qte_header_rec.End_Customer_cust_party_id,
2676 l_qte_header_rec.End_Customer_cust_account_id,
2677 l_header_shipment_tbl(1).ship_to_party_id,
2678 l_header_shipment_tbl(1).ship_to_party_site_id,
2679 l_header_shipment_tbl(1).ship_to_cust_party_id,
2680 l_header_shipment_tbl(1).ship_to_cust_account_id,
2681 l_header_shipment_tbl(1).shipment_id;
2682 CLOSE C_Get_Hdr_Acct;
2683
2684 l_end_cust_acct := l_qte_header_rec.End_Customer_cust_account_id;
2685 l_end_cust_party := l_qte_header_rec.End_Customer_cust_party_id;
2686 l_cust_acct := l_qte_header_rec.cust_account_id;
2687 l_inv_cust_acct := l_qte_header_rec.invoice_to_cust_account_id;
2688 l_cust_party := l_qte_header_rec.cust_party_id;
2689 l_inv_cust_party := l_qte_header_rec.invoice_to_cust_party_id;
2690
2691
2692 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2693 aso_debug_pub.add('Assign_Customer_Accounts: p_qte_header_id: '||p_qte_header_id, 1, 'N');
2694 aso_debug_pub.add('Assign_Customer_Accounts: p_calling_api_flag: '||p_calling_api_flag, 1, 'N');
2695 aso_debug_pub.add('Assign_Customer_Accounts: l_cust_acct: '||l_cust_acct, 1, 'N');
2696 aso_debug_pub.add('Assign_Customer_Accounts: l_inv_cust_acct: '||l_inv_cust_acct, 1, 'N');
2697 aso_debug_pub.add('Assign_Customer_Accounts: l_cust_party: '||l_cust_party, 1, 'N');
2698 aso_debug_pub.add('Assign_Customer_Accounts: l_inv_cust_party: '||l_inv_cust_party, 1, 'N');
2699 aso_debug_pub.add('Assign_Customer_Accounts: l_end_cust_acct: '||l_end_cust_acct, 1, 'N');
2700 aso_debug_pub.add('Assign_Customer_Accounts: l_end_cust_party: '||l_end_cust_party, 1, 'N');
2701 aso_debug_pub.add('l_qte_header_rec.party_id = ' || l_qte_header_rec.party_id,1,'N');
2702 aso_debug_pub.add('l_qte_header_rec.sold_to_party_site_id = '||l_qte_header_rec.sold_to_party_site_id,1,'N');
2703 aso_debug_pub.add('l_qte_header_rec.invoice_to_party_id = ' || l_qte_header_rec.invoice_to_party_id,1,'N');
2704 aso_debug_pub.add('l_qte_header_rec.invoice_to_party_site_id = '|| l_qte_header_rec.invoice_to_party_site_id,1,'N');
2705 aso_debug_pub.add('l_qte_header_rec.End_Customer_party_id = ' || l_qte_header_rec.End_Customer_party_id,1,'N');
2706 aso_debug_pub.add('l_qte_header_rec.End_Customer_party_site_id = '|| l_qte_header_rec.End_Customer_party_site_id,1,'N');
2707 aso_debug_pub.add('l_header_shipment_tbl.count '|| l_header_shipment_tbl.count,1,'N');
2708 IF l_header_shipment_tbl.count > 0 THEN
2709 aso_debug_pub.add('l_header_shipment_tbl(1).shipment_id = '|| l_header_shipment_tbl(1).shipment_id,1,'N');
2710 aso_debug_pub.add('l_header_shipment_tbl(1).ship_to_party_id = '|| l_header_shipment_tbl(1).ship_to_party_id,1,'N');
2711 aso_debug_pub.add('l_header_shipment_tbl(1).ship_to_party_site_id = '|| l_header_shipment_tbl(1).ship_to_party_site_id,1,'N');
2712 aso_debug_pub.add('l_header_shipment_tbl(1).ship_to_cust_party_id = '||l_header_shipment_tbl(1).ship_to_cust_party_id,1,'N');
2713 aso_debug_pub.add('l_header_shipment_tbl(1).ship_to_cust_account_id = '|| l_header_shipment_tbl(1).ship_to_cust_account_id,1,'N');
2714 END IF;
2715 END IF;
2716
2717 IF l_cust_acct IS NULL THEN
2718 l_header_check_tca_flag := 'Y';
2719
2720 IF l_cust_party IS NOT NULL THEN
2721
2722 ASO_CHECK_TCA_PVT.Customer_Account(
2723 p_api_version => 1.0,
2724 p_Party_Id => l_cust_party,
2725 p_calling_api_flag => p_calling_api_flag,
2726 x_Cust_Acct_Id => l_account_id,
2727 x_return_status => x_return_status,
2728 x_msg_count => x_msg_count,
2729 x_msg_data => x_msg_data);
2730
2731 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2732 aso_debug_pub.add('Assign_Customer_Accounts 1: x_return_status: '||x_return_status, 1, 'N');
2733 aso_debug_pub.add('header sold to customer = ' || l_account_id);
2734 END IF;
2735
2736
2737 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2738 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2739 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2740 FND_MESSAGE.Set_Token('ID', to_char( l_cust_party), FALSE);
2741 FND_MSG_PUB.ADD;
2742 END IF;
2746
2743 raise FND_API.G_EXC_ERROR;
2744
2745 ELSE
2747 UPDATE ASO_QUOTE_HEADERS_ALL
2748 SET cust_account_id = l_account_id
2749 ,last_update_date = l_last_update_date
2750 ,last_updated_by = l_g_user_id
2751 ,last_update_login = l_g_login_id
2752 WHERE quote_header_id = p_qte_header_id;
2753 l_qte_header_rec.cust_account_id := l_account_id;
2754
2755
2756 END IF;
2757
2758 END IF;
2759
2760 END IF;
2761 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2762 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2763 END IF;
2764
2765
2766 IF l_inv_cust_acct IS NULL THEN
2767 l_header_check_tca_flag := 'Y';
2768
2769 IF l_inv_cust_party IS NOT NULL THEN
2770
2771 ASO_CHECK_TCA_PVT.Customer_Account(
2772 p_api_version => 1.0,
2773 p_Party_Id => l_inv_cust_party,
2774 p_calling_api_flag => p_calling_api_flag,
2775 x_Cust_Acct_Id => l_account_id,
2776 x_return_status => x_return_status,
2777 x_msg_count => x_msg_count,
2778 x_msg_data => x_msg_data);
2779
2780 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2781 aso_debug_pub.add('Assign_Customer_Accounts 2: x_return_status: '||x_return_status, 1, 'N');
2782 aso_debug_pub.add('header invoice to account = ' || l_account_id,1,'N');
2783 END IF;
2784
2785
2786 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2787 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2788 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2789 FND_MESSAGE.Set_Token('ID', to_char( l_inv_cust_party), FALSE);
2790 FND_MSG_PUB.ADD;
2791 END IF;
2792 raise FND_API.G_EXC_ERROR;
2793
2794 ELSE
2795
2796 UPDATE ASO_QUOTE_HEADERS_ALL
2797 SET invoice_to_cust_account_id = l_account_id
2798 ,last_update_date = l_last_update_date
2799 ,last_updated_by = l_g_user_id
2800 ,last_update_login = l_g_login_id
2801
2802 WHERE quote_header_id = p_qte_header_id;
2803 l_qte_header_rec.invoice_to_cust_account_id := l_account_id;
2804
2805
2806 END IF;
2807
2808 END IF;
2809
2810 END IF;
2811 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2812 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2813 END IF;
2814
2815 IF l_end_cust_acct IS NULL THEN
2816 l_header_check_tca_flag := 'Y';
2817
2818 IF l_end_cust_party IS NOT NULL THEN
2819
2820 ASO_CHECK_TCA_PVT.Customer_Account(
2821 p_api_version => 1.0,
2822 p_Party_Id => l_end_cust_party,
2823 p_calling_api_flag => p_calling_api_flag,
2824 x_Cust_Acct_Id => l_account_id,
2825 x_return_status => x_return_status,
2826 x_msg_count => x_msg_count,
2827 x_msg_data => x_msg_data);
2828
2829 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2830 aso_debug_pub.add('Assign_Customer_Accounts 2: x_return_status: '||x_return_status, 1, 'N');
2831 aso_debug_pub.add('header end customer account = ' || l_account_id,1,'N');
2832 END IF;
2833
2834
2835 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2836 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2837 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2838 FND_MESSAGE.Set_Token('ID', to_char( l_end_cust_party), FALSE);
2839 FND_MSG_PUB.ADD;
2840 END IF;
2841 raise FND_API.G_EXC_ERROR;
2842
2843 ELSE
2844
2845 UPDATE ASO_QUOTE_HEADERS_ALL
2846 SET end_customer_cust_account_id = l_account_id
2847 ,last_update_date = l_last_update_date
2848 ,last_updated_by = l_g_user_id
2849 ,last_update_login = l_g_login_id
2850 WHERE quote_header_id = p_qte_header_id;
2851
2852 l_qte_header_rec.end_customer_cust_account_id := l_account_id;
2853
2854
2855 END IF;
2856
2857 END IF;
2858
2859 END IF;
2860 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2861 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2862 END IF;
2863
2864 IF l_header_shipment_tbl.count > 0 THEN
2865
2866 IF l_header_shipment_tbl(1).ship_to_cust_account_id IS NULL THEN
2867 l_header_check_tca_flag := 'Y';
2868 IF l_header_shipment_tbl(1).ship_to_cust_party_id IS NOT NULL THEN
2869
2870 ASO_CHECK_TCA_PVT.Customer_Account(
2871 p_api_version => 1.0,
2872 p_Party_Id => l_header_shipment_tbl(1).ship_to_cust_party_id,
2873 p_calling_api_flag => p_calling_api_flag,
2874 x_Cust_Acct_Id => l_account_id,
2875 x_return_status => x_return_status,
2876 x_msg_count => x_msg_count,
2877 x_msg_data => x_msg_data);
2878
2879 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2880 aso_debug_pub.add('Assign_Customer_Accounts 4: x_return_status: '||x_return_status, 1, 'N');
2881 aso_debug_pub.add('header ship to cust party = '||l_header_shipment_tbl(1).ship_to_cust_party_id,1,'N');
2882 aso_debug_pub.add('header ship to account = ' || l_account_id,1,'N');
2883 END IF;
2884
2885 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2886 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2887 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2888 FND_MESSAGE.Set_Token('ID', to_char( l_header_shipment_tbl(1).ship_to_cust_party_id), FALSE);
2892
2889 FND_MSG_PUB.ADD;
2890 END IF;
2891 raise FND_API.G_EXC_ERROR;
2893 ELSE
2894 UPDATE ASO_SHIPMENTS
2895 SET ship_to_cust_account_id = l_account_id
2896 ,last_update_date = l_last_update_date
2897 ,last_updated_by = l_g_user_id
2898 ,last_update_login = l_g_login_id
2899
2900 WHERE shipment_id = l_header_shipment_tbl(1).shipment_id;
2901
2902 l_header_shipment_tbl(1).ship_to_cust_account_id := l_account_id;
2903 END IF;
2904
2905 END IF;
2906
2907 END IF;
2908
2909 END IF; --l_header_shipment_tbl.count
2910
2911 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2912 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2913 END IF;
2914
2915
2916
2917 IF l_header_check_tca_flag = 'Y' then
2918 check_tca(
2919 p_api_version => 1.0,
2920 p_init_msg_list => FND_API.G_FALSE,
2921 P_Qte_Rec => l_qte_header_rec,
2922 p_Header_Shipment_Tbl => l_header_Shipment_Tbl,
2923 x_return_status => x_return_status,
2924 x_msg_count => x_msg_count,
2925 x_msg_data => x_msg_data);
2926 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2927 aso_debug_pub.add('after check tca '||x_return_status, 1, 'N');
2928 END IF;
2929 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2930 RAISE FND_API.G_EXC_ERROR;
2931 END IF;
2932 END IF;
2933
2934 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2935 aso_debug_pub.add('Assign_Customer_Accounts : checking for lines ', 1, 'N');
2936 end if;
2937
2938 FOR Line_Acct IN C_Get_Line_Acct(p_qte_header_id) LOOP
2939
2940 l_qte_line_rec := ASO_QUOTE_PUB.G_MISS_QTE_LINE_REC;
2941 l_line_shipment_tbl := ASO_QUOTE_PUB.G_MISS_Shipment_TBL;
2942
2943 l_qte_line_rec.quote_header_id := p_qte_header_id;
2944 l_qte_line_rec.invoice_to_cust_account_id := Line_Acct.invoice_to_cust_account_id;
2945 l_qte_line_rec.invoice_to_party_site_id := Line_Acct.invoice_to_party_site_id;
2946 l_qte_line_rec.invoice_to_cust_party_id := Line_Acct.invoice_to_cust_party_id;
2947 l_qte_line_rec.invoice_to_party_id := Line_Acct.invoice_to_party_id;
2948 l_qte_line_rec.quote_line_id := Line_Acct.quote_line_id;
2949
2950 l_qte_line_rec.End_Customer_cust_account_id := Line_Acct.End_Customer_cust_account_id;
2951 l_qte_line_rec.End_Customer_party_site_id := Line_Acct.End_Customer_party_site_id;
2952 l_qte_line_rec.End_Customer_cust_party_id := Line_Acct.End_Customer_cust_party_id;
2953 l_qte_line_rec.End_Customer_party_id := Line_Acct.End_Customer_party_id;
2954
2955 l_line_shipment_tbl(1).ship_to_cust_account_id := Line_Acct.ship_to_cust_account_id;
2956 l_line_shipment_tbl(1).ship_to_cust_party_id := Line_Acct.ship_to_cust_party_id;
2957 l_line_shipment_tbl(1).ship_to_party_id := Line_Acct.ship_to_party_id;
2958 l_line_shipment_tbl(1).ship_to_party_site_id := Line_Acct.ship_to_party_site_id;
2959 l_line_shipment_tbl(1).shipment_id := Line_Acct.shipment_id;
2960
2961 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2962 aso_debug_pub.add('l_qte_line_rec.quote_line_id = '|| l_qte_line_rec.quote_line_id,1,'N');
2963 aso_debug_pub.add('l_qte_line_rec.invoice_to_cust_account_id = '|| l_qte_line_rec.invoice_to_cust_account_id,1,'N');
2964 aso_debug_pub.add('l_qte_line_rec.invoice_to_party_site_id = '|| l_qte_line_rec.invoice_to_party_site_id,1,'N');
2965 aso_debug_pub.add('l_qte_line_rec.invoice_to_cust_party_id = ' || l_qte_line_rec.invoice_to_cust_party_id,1,'N');
2966 aso_debug_pub.add('l_qte_line_rec.invoice_to_party_id = ' || l_qte_line_rec.invoice_to_party_id,1,'N');
2967 aso_debug_pub.add('l_qte_line_rec.End_Customer_cust_account_id = '|| l_qte_line_rec.End_Customer_cust_account_id,1,'N');
2968 aso_debug_pub.add('l_qte_line_rec.End_Customer_party_site_id = '|| l_qte_line_rec.End_Customer_party_site_id,1,'N');
2969 aso_debug_pub.add('l_qte_line_rec.End_Customer_cust_party_id = ' || l_qte_line_rec.End_Customer_cust_party_id,1,'N');
2970 aso_debug_pub.add('l_qte_line_rec.End_Customer_party_id = ' || l_qte_line_rec.End_Customer_party_id,1,'N');
2971 aso_debug_pub.add('l_line_shipment_tbl(1).shipment_id = ' || l_line_shipment_tbl(1).shipment_id,1,'N');
2972 aso_debug_pub.add('l_line_shipment_tbl(1).ship_to_cust_account_id = '|| l_line_shipment_tbl(1).ship_to_cust_account_id,1,'N');
2973 aso_debug_pub.add('l_line_shipment_tbl(1).ship_to_cust_party_id = '|| l_line_shipment_tbl(1).ship_to_cust_party_id,1,'N');
2974 aso_debug_pub.add('l_line_shipment_tbl(1).ship_to_party_id = '|| l_line_shipment_tbl(1).ship_to_party_id,1,'N');
2975 aso_debug_pub.add('l_line_shipment_tbl(1).ship_to_party_site_id = '|| l_line_shipment_tbl(1).ship_to_party_site_id,1,'N');
2976 end if;
2977
2978
2979 IF l_qte_line_rec.invoice_to_cust_account_id IS NULL THEN
2980 IF l_qte_line_rec.invoice_to_cust_party_id IS NOT NULL THEN
2981
2982 ASO_CHECK_TCA_PVT.Customer_Account(
2983 p_api_version => 1.0,
2984 p_Party_Id => l_qte_line_rec.invoice_to_cust_party_id,
2985 p_calling_api_flag => p_calling_api_flag,
2986 x_Cust_Acct_Id => l_account_id,
2987 x_return_status => x_return_status,
2988 x_msg_count => x_msg_count,
2989 x_msg_data => x_msg_data);
2990
2991 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2992 aso_debug_pub.add('Assign_Customer_Accounts 3: x_return_status: '||x_return_status, 1, 'N');
2993 aso_debug_pub.add('line invoice to customer party = ' ||
2994 l_qte_line_rec.invoice_to_cust_party_id,1,'N');
2995
2996 aso_debug_pub.add('line invoice to customer account = ' ||
2997 l_account_id,1,'N');
2998 END IF;
2999
3000 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3001 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3002 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3006 raise FND_API.G_EXC_ERROR;
3003 FND_MESSAGE.Set_Token('ID', to_char( l_qte_line_rec.invoice_to_cust_party_id), FALSE);
3004 FND_MSG_PUB.ADD;
3005 END IF;
3007
3008 ELSE
3009
3010 UPDATE ASO_QUOTE_LINES_ALL
3011 SET invoice_to_cust_account_id = l_account_id
3012 ,last_update_date = l_last_update_date
3013 ,last_updated_by = l_g_user_id
3014 ,last_update_login = l_g_login_id
3015
3016 WHERE quote_line_id = l_qte_line_rec.quote_line_id;
3017
3018 l_qte_line_rec.invoice_to_cust_account_id := l_account_id;
3019
3020 END IF;
3021
3022 END IF;
3023
3024 END IF;
3025
3026 IF l_qte_line_rec.End_Customer_cust_account_id IS NULL THEN
3027 IF l_qte_line_rec.End_Customer_cust_party_id IS NOT NULL THEN
3028
3029 ASO_CHECK_TCA_PVT.Customer_Account(
3030 p_api_version => 1.0,
3031 p_Party_Id => l_qte_line_rec.End_Customer_cust_party_id,
3032 p_calling_api_flag => p_calling_api_flag,
3033 x_Cust_Acct_Id => l_account_id,
3034 x_return_status => x_return_status,
3035 x_msg_count => x_msg_count,
3036 x_msg_data => x_msg_data);
3037
3038 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3039 aso_debug_pub.add('Assign_Customer_Accounts 3: x_return_status: '||x_return_status, 1, 'N');
3040 aso_debug_pub.add('line End_Customer customer party = ' ||
3041 l_qte_line_rec.End_Customer_cust_party_id,1,'N');
3042
3043 aso_debug_pub.add('line End_Customer customer account = ' ||
3044 l_account_id,1,'N');
3045 END IF;
3046
3047 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3048 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3049 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3050 FND_MESSAGE.Set_Token('ID', to_char( l_qte_line_rec.End_Customer_cust_party_id), FALSE);
3051 FND_MSG_PUB.ADD;
3052 END IF;
3053 raise FND_API.G_EXC_ERROR;
3054
3055 ELSE
3056
3057 UPDATE ASO_QUOTE_LINES_ALL
3058 SET End_Customer_cust_account_id = l_account_id
3059 ,last_update_date = l_last_update_date
3060 ,last_updated_by = l_g_user_id
3061 ,last_update_login = l_g_login_id
3062 WHERE quote_line_id = l_qte_line_rec.quote_line_id;
3063
3064 l_qte_line_rec.End_Customer_cust_account_id := l_account_id;
3065
3066 END IF;
3067
3068 END IF;
3069
3070 END IF;
3071
3072 IF Line_Acct.ship_to_cust_account_id IS NULL THEN
3073
3074 IF Line_Acct.ship_to_cust_party_id IS NOT NULL THEN
3075
3076 ASO_CHECK_TCA_PVT.Customer_Account(
3077 p_api_version => 1.0,
3078 p_Party_Id => Line_Acct.ship_to_cust_party_id,
3079 p_calling_api_flag => p_calling_api_flag,
3080 x_Cust_Acct_Id => l_account_id,
3081 x_return_status => x_return_status,
3082 x_msg_count => x_msg_count,
3083 x_msg_data => x_msg_data);
3084
3085 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3086 aso_debug_pub.add('Assign_Customer_Accounts 4: x_return_status: '||x_return_status, 1, 'N');
3087 aso_debug_pub.add('line ship to customer party = ' ||
3088 Line_Acct.ship_to_cust_party_id,1,'N');
3089
3090 aso_debug_pub.add('line ship to customer account = ' ||
3091 l_account_id,1,'N');
3092 END IF;
3093
3094 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3095 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3096 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3097 FND_MESSAGE.Set_Token('ID', to_char( Line_Acct.ship_to_cust_party_id), FALSE);
3098 FND_MSG_PUB.ADD;
3099 END IF;
3100 raise FND_API.G_EXC_ERROR;
3101
3102 ELSE
3103
3104 UPDATE ASO_SHIPMENTS
3105 SET ship_to_cust_account_id = l_account_id
3106 ,last_update_date = l_last_update_date
3107 ,last_updated_by = l_g_user_id
3108 ,last_update_login = l_g_login_id
3109
3110 WHERE shipment_id = Line_Acct.shipment_id;
3111
3112 l_line_shipment_tbl(1).ship_to_cust_account_id := l_account_id;
3113
3114 END IF;
3115
3116 END IF;
3117
3118 END IF;
3119
3120 Check_Line_Account_Info(
3121 p_api_version => 1.0,
3122 p_cust_account_id => l_qte_header_rec.cust_account_id,
3123 p_qte_line_rec => l_qte_line_rec,
3124 p_line_shipment_tbl => l_line_shipment_tbl,
3125 x_return_status => x_return_status,
3126 x_msg_count => x_msg_count,
3127 x_msg_data => x_msg_data);
3128
3129 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3130 aso_debug_pub.add('after check line account info '||x_return_status, 1, 'N
3131 ');
3132 END IF;
3133 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3134 RAISE FND_API.G_EXC_ERROR;
3135 END IF;
3136
3137
3138 END LOOP; -- line_Acct
3139
3140
3141
3142
3143
3144
3145 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3146 aso_debug_pub.add('Assign_Customer_Accounts: End ', 1, 'N');
3147 END IF;
3148
3149 FND_MSG_PUB.Count_And_Get
3150 ( p_count => x_msg_count,
3151 p_data => x_msg_data
3152 );
3153
3154 EXCEPTION
3155 WHEN FND_API.G_EXC_ERROR THEN
3156 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3157 P_API_NAME => L_API_NAME
3161 ,P_SQLCODE => SQLCODE
3158 ,P_PKG_NAME => G_PKG_NAME
3159 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3160 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
3162 ,P_SQLERRM => SQLERRM
3163 ,X_MSG_COUNT => X_MSG_COUNT
3164 ,X_MSG_DATA => X_MSG_DATA
3165 ,X_RETURN_STATUS => X_RETURN_STATUS);
3166
3167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3168 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3169 P_API_NAME => L_API_NAME
3170 ,P_PKG_NAME => G_PKG_NAME
3171 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3172 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
3173 ,P_SQLCODE => SQLCODE
3174 ,P_SQLERRM => SQLERRM
3175 ,X_MSG_COUNT => X_MSG_COUNT
3176 ,X_MSG_DATA => X_MSG_DATA
3177 ,X_RETURN_STATUS => X_RETURN_STATUS);
3178
3179 WHEN OTHERS THEN
3180 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3181 P_API_NAME => L_API_NAME
3182 ,P_PKG_NAME => G_PKG_NAME
3183 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
3184 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
3185 ,P_SQLCODE => SQLCODE
3186 ,P_SQLERRM => SQLERRM
3187 ,X_MSG_COUNT => X_MSG_COUNT
3188 ,X_MSG_DATA => X_MSG_DATA
3189 ,X_RETURN_STATUS => X_RETURN_STATUS);
3190
3191 END Assign_Customer_Accounts;
3192
3193
3194
3195 PROCEDURE Populate_Acct_Party (
3196 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3197 p_hdr_cust_acct_id IN NUMBER,
3198 p_hdr_party_id IN NUMBER,
3199 p_party_site_id IN NUMBER,
3200 p_cust_account_id IN OUT NOCOPY NUMBER,
3201 p_cust_party_id IN OUT NOCOPY NUMBER,
3202 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3203 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3204 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3205 )
3206
3207 IS
3208
3209 CURSOR C_Get_Party_From_Acct(acct_id NUMBER) IS
3210 SELECT party_id
3211 FROM HZ_CUST_ACCOUNTS
3212 WHERE cust_account_id = acct_id
3213 AND status = 'A'
3214 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
3215
3216 l_inv_cust_id NUMBER := NULL;
3217
3218 BEGIN
3219
3220 IF p_cust_account_id is not NULL AND
3221 p_cust_account_id <> FND_API.G_MISS_NUM THEN
3222 IF p_cust_party_id IS NULL OR
3223 p_cust_party_id = FND_API.G_MISS_NUM THEN
3224
3225 OPEN C_Get_Party_From_Acct(p_cust_account_id);
3226 FETCH C_Get_Party_From_Acct INTO p_cust_party_id;
3227 CLOSE C_Get_Party_From_Acct;
3228
3229 ELSE
3230
3231 OPEN C_Get_Party_From_Acct(p_cust_account_id);
3232 FETCH C_Get_Party_From_Acct INTO l_inv_cust_id;
3233 CLOSE C_Get_Party_From_Acct;
3234
3235 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3236 aso_debug_pub.add('Check_Hdr_Acct: l_inv_cust_id: '|| l_inv_cust_id, 1, 'N');
3237 END IF;
3238 IF l_inv_cust_id <> p_cust_party_id THEN
3239 x_return_status := FND_API.G_RET_STS_ERROR;
3240 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3241 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
3242 FND_MESSAGE.Set_Token('COLUMN', 'CUST_PARTY_ID',FALSE);
3243 FND_MESSAGE.Set_Token('VALUE', to_char(p_cust_party_id),FALSE);
3244 FND_MSG_PUB.ADD;
3245 END IF;
3246 raise FND_API.G_EXC_ERROR;
3247 END IF;
3248
3249 END IF;
3250
3251 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3252 aso_debug_pub.add('Check_Hdr_Acct: p_cust_party_id:1: '|| p_cust_party_id, 1, 'N');
3253 END IF;
3254
3255 ELSE -- inv_to_cust_acct_id is null
3256
3257 IF p_cust_party_id IS NULL OR
3258 p_cust_party_id = FND_API.G_MISS_NUM THEN
3259 IF p_party_site_id IS NOT NULL AND
3260 p_party_site_id <> FND_API.G_MISS_NUM THEN
3261
3262 p_cust_account_id := p_hdr_cust_acct_id;
3263 p_cust_party_id := p_hdr_party_id;
3264
3265 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3266 aso_debug_pub.add('Check_Hdr_Acct: p_cust_account_id: '|| p_cust_account_id, 1, 'N');
3267 aso_debug_pub.add('Check_Hdr_Acct: p_cust_party_id:2: '|| p_cust_party_id, 1, 'N');
3268 END IF;
3269
3270 END IF;
3271 END IF;
3272
3273 END IF; -- inv_to_cust_acct_id
3274
3275
3276 END Populate_Acct_Party;
3277
3278
3279 PROCEDURE Check_Customer_Accounts(
3280 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3281 p_qte_header_id IN NUMBER,
3282 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3283 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3284 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3285 )
3286 IS
3287
3288 CURSOR C_Validate_Quote (qte_hdr NUMBER) IS
3289 SELECT 'X'
3290 FROM aso_quote_headers_all
3291 WHERE quote_header_id = qte_hdr;
3292
3293 CURSOR C_Get_Hdr_CustAcct (qte_hdr NUMBER) IS
3294 SELECT cust_party_id
3295 FROM aso_quote_headers_all
3296 WHERE quote_header_id = qte_hdr
3297 AND (cust_account_id IS NULL
3298 AND cust_party_id IS NOT NULL);
3299
3300 CURSOR C_Get_Hdr_InvCustAcct (qte_hdr NUMBER) IS
3304 AND (invoice_to_cust_account_id IS NULL
3301 SELECT invoice_to_cust_party_id
3302 FROM aso_quote_headers_all
3303 WHERE quote_header_id = qte_hdr
3305 AND invoice_to_cust_party_id IS NOT NULL);
3306
3307 CURSOR C_Get_Hdr_EndCustAcct (qte_hdr NUMBER) IS
3308 SELECT End_Customer_cust_party_id
3309 FROM aso_quote_headers_all
3310 WHERE quote_header_id = qte_hdr
3311 AND (End_Customer_cust_account_id IS NULL
3312 AND End_Customer_cust_party_id IS NOT NULL);
3313
3314 CURSOR C_Get_Line_Acct (qte_hdr NUMBER) IS
3315 SELECT invoice_to_cust_party_id
3316 FROM aso_quote_lines_all
3317 WHERE quote_header_id = qte_hdr
3318 AND invoice_to_cust_account_id IS NULL
3319 AND invoice_to_cust_party_id IS NOT NULL;
3320
3321 CURSOR C_Get_Line_EndCustAcct (qte_hdr NUMBER) IS
3322 SELECT End_Customer_cust_party_id
3323 FROM aso_quote_lines_all
3324 WHERE quote_header_id = qte_hdr
3325 AND End_Customer_cust_account_id IS NULL
3326 AND End_Customer_cust_party_id IS NOT NULL;
3327
3328 CURSOR C_Get_Ship_Acct (qte_hdr NUMBER) IS
3332 AND ship_to_cust_account_id IS NULL
3329 SELECT ship_to_cust_party_id
3330 FROM aso_shipments
3331 WHERE quote_header_id = qte_hdr
3333 AND ship_to_cust_party_id IS NOT NULL;
3334
3335 CURSOR C_Chk_Party_Acct (pty_id NUMBER) IS
3336 SELECT cust_account_id
3337 FROM hz_cust_accounts
3338 WHERE party_id = pty_id
3339 AND status = 'A'
3340 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
3341
3342 l_create_acct_prof VARCHAR2(30) := NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED');
3343 l_dummy VARCHAR2(1) := NULL;
3344 l_party NUMBER := NULL;
3345 l_cust_acct NUMBER := NULL;
3346 l_api_version CONSTANT NUMBER := 1.0;
3347 l_api_name CONSTANT VARCHAR2(45) := 'Check_Customer_Accounts';
3348
3349 BEGIN
3350
3351 -- Standard Start of API savepoint
3352 SAVEPOINT Check_Customer_Accounts_INT;
3353
3354 -- Initialize message list if p_init_msg_list is set to TRUE.
3355 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3356 FND_MSG_PUB.initialize;
3357 END IF;
3358
3359 -- Initialize API return status to success
3360 x_return_status := FND_API.G_RET_STS_SUCCESS;
3361
3362 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3363 aso_debug_pub.add('Check_Customer_Accounts - Begin', 1, 'Y');
3364 END IF;
3365
3366 OPEN C_Validate_Quote (p_qte_header_id);
3367 FETCH C_Validate_Quote into l_dummy;
3368 IF C_Validate_Quote%NOTFOUND THEN
3369 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3370 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
3371 FND_MESSAGE.Set_Token('COLUMN', 'ORIGINAL_QUOTE_ID', FALSE);
3372 FND_MESSAGE.Set_Token('VALUE', TO_CHAR(p_qte_header_id), FALSE);
3373 FND_MSG_PUB.ADD;
3374 END IF;
3375
3376 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3377 aso_debug_pub.add('Check_Customer_Accounts - Invalid qte_hdr_id', 1, 'Y');
3378 END IF;
3379
3380 CLOSE C_Validate_Quote;
3381 x_return_status := FND_API.G_RET_STS_ERROR;
3382 RAISE FND_API.G_EXC_ERROR;
3383 END IF;
3384 CLOSE C_Validate_Quote;
3385
3386 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3387 aso_debug_pub.add('Check_Customer_Accounts - After qte_hdr_id check', 1, 'Y');
3388 aso_debug_pub.add('Check_Customer_Accounts - l_create_acct_prof: '||l_create_acct_prof, 1, 'N');
3389 END IF;
3390
3391 l_party := NULL;
3392 l_cust_acct := NULL;
3393
3394 IF l_create_acct_prof = 'PLACE_ORDER' THEN
3395
3396 OPEN C_Get_Hdr_CustAcct(p_qte_header_id);
3397 FETCH C_Get_Hdr_CustAcct INTO l_party;
3398 CLOSE C_Get_Hdr_CustAcct;
3399
3400 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3401 aso_debug_pub.add('Check_Customer_Accounts:hdr_CustAcct - l_party: '||l_party, 1, 'N');
3402 END IF;
3403
3404 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3405
3406 OPEN C_Chk_Party_Acct (l_party);
3407 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3408 CLOSE C_Chk_Party_Acct;
3409
3410 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3411 aso_debug_pub.add('Check_Customer_Accounts:hdr_CustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3412 END IF;
3413
3414 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3415 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3416 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3417 FND_MSG_PUB.ADD;
3418 END IF;
3419 x_return_status := FND_API.G_RET_STS_ERROR;
3420 RAISE FND_API.G_EXC_ERROR;
3421 END IF;
3422
3423 END IF;
3424
3425 l_party := NULL;
3426 l_cust_acct := NULL;
3427
3428 OPEN C_Get_Hdr_InvCustAcct(p_qte_header_id);
3429 FETCH C_Get_Hdr_InvCustAcct INTO l_party;
3430 CLOSE C_Get_Hdr_InvCustAcct;
3431
3432 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3433 aso_debug_pub.add('Check_Customer_Accounts:hdr_InvCustAcct - l_party: '||l_party, 1, 'N');
3434 END IF;
3435
3436 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3437
3438 OPEN C_Chk_Party_Acct (l_party);
3439 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3440 CLOSE C_Chk_Party_Acct;
3441
3442 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3443 aso_debug_pub.add('Check_Customer_Accounts:hdr_InvCustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3444 END IF;
3445
3446 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3447 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3448 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3449 FND_MSG_PUB.ADD;
3450 END IF;
3451 x_return_status := FND_API.G_RET_STS_ERROR;
3452 RAISE FND_API.G_EXC_ERROR;
3453 END IF;
3454
3455 END IF;
3456
3457 l_party := NULL;
3458 l_cust_acct := NULL;
3459
3460 OPEN C_Get_Hdr_EndCustAcct(p_qte_header_id);
3461 FETCH C_Get_Hdr_EndCustAcct INTO l_party;
3462 CLOSE C_Get_Hdr_EndCustAcct;
3463
3464 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3465 aso_debug_pub.add('Check_Customer_Accounts:hdr_EndCustAcct - l_party: '||l_party, 1, 'N');
3466 END IF;
3467
3468 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3469
3470 OPEN C_Chk_Party_Acct (l_party);
3471 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3472 CLOSE C_Chk_Party_Acct;
3473
3474 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3475 aso_debug_pub.add('Check_Customer_Accounts:hdr_EndCustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3476 END IF;
3477
3478 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3479 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3483 x_return_status := FND_API.G_RET_STS_ERROR;
3480 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3481 FND_MSG_PUB.ADD;
3482 END IF;
3484 RAISE FND_API.G_EXC_ERROR;
3485 END IF;
3486
3487 END IF;
3488
3489
3490 FOR Line_Acct IN C_Get_Line_Acct(p_qte_header_id) LOOP
3491
3492 l_party := NULL;
3493 l_cust_acct := NULL;
3494 l_party := Line_Acct.invoice_to_cust_party_id;
3495
3496 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3497 aso_debug_pub.add('Check_Customer_Accounts:line_Acct - l_party: '||l_party, 1, 'N');
3498 END IF;
3499
3500 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3501
3502 OPEN C_Chk_Party_Acct (l_party);
3503 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3504 CLOSE C_Chk_Party_Acct;
3505
3506 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3507 aso_debug_pub.add('Check_Customer_Accounts:lin_Acct - l_cust_acct: '||l_cust_acct, 1, 'N');
3508 END IF;
3509
3510 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3511 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3512 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3513 FND_MSG_PUB.ADD;
3514 END IF;
3515 x_return_status := FND_API.G_RET_STS_ERROR;
3516 RAISE FND_API.G_EXC_ERROR;
3517 END IF;
3518
3519 END IF;
3520
3521 END LOOP;
3522
3523
3524 FOR Line_EndCustAcct IN C_Get_Line_EndCustAcct(p_qte_header_id) LOOP
3525
3526 l_party := NULL;
3527 l_cust_acct := NULL;
3528 l_party := Line_EndCustAcct.End_Customer_cust_party_id;
3529
3530 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3531 aso_debug_pub.add('Check_Customer_Accounts:line_EndCustAcct - l_party: '||l_party, 1, 'N');
3532 END IF;
3533
3534 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3535
3536 OPEN C_Chk_Party_Acct (l_party);
3537 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3538 CLOSE C_Chk_Party_Acct;
3539
3540 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3541 aso_debug_pub.add('Check_Customer_Accounts:lin_EndCustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3542 END IF;
3543
3544 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3545 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3546 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3547 FND_MSG_PUB.ADD;
3548 END IF;
3549 x_return_status := FND_API.G_RET_STS_ERROR;
3550 RAISE FND_API.G_EXC_ERROR;
3551 END IF;
3552
3553 END IF;
3554
3555 END LOOP;
3556
3557
3558 FOR Ship_Acct IN C_Get_Ship_Acct(p_qte_header_id) LOOP
3559
3560 l_party := NULL;
3561 l_cust_acct := NULL;
3562 l_party := Ship_Acct.ship_to_cust_party_id;
3563
3564 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3565 aso_debug_pub.add('Check_Customer_Accounts:shp_Acct - l_party: '||l_party, 1, 'N');
3566 END IF;
3567
3568 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3569
3570 OPEN C_Chk_Party_Acct (l_party);
3571 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3572 CLOSE C_Chk_Party_Acct;
3573
3574 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3575 aso_debug_pub.add('Check_Customer_Accounts:shp_Acct - l_cust_acct: '||l_cust_acct, 1, 'N');
3576 END IF;
3577
3578 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3579 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3580 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3581 FND_MSG_PUB.ADD;
3582 END IF;
3583 x_return_status := FND_API.G_RET_STS_ERROR;
3584 RAISE FND_API.G_EXC_ERROR;
3585 END IF;
3586
3587 END IF;
3588
3589 END LOOP;
3590
3591 END IF; -- 'Place Order'
3592
3593 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3594 aso_debug_pub.add('Check_Customer_Accounts: End ', 1, 'N');
3595 END IF;
3596
3597 FND_MSG_PUB.Count_And_Get
3598 ( p_count => x_msg_count,
3599 p_data => x_msg_data
3600 );
3601
3602 EXCEPTION
3603 WHEN FND_API.G_EXC_ERROR THEN
3604 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3605 P_API_NAME => L_API_NAME
3606 ,P_PKG_NAME => G_PKG_NAME
3607 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3608 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3609 ,P_SQLCODE => SQLCODE
3610 ,P_SQLERRM => SQLERRM
3611 ,X_MSG_COUNT => X_MSG_COUNT
3612 ,X_MSG_DATA => X_MSG_DATA
3613 ,X_RETURN_STATUS => X_RETURN_STATUS);
3614
3615 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3616 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3617 P_API_NAME => L_API_NAME
3618 ,P_PKG_NAME => G_PKG_NAME
3619 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3620 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3621 ,P_SQLCODE => SQLCODE
3622 ,P_SQLERRM => SQLERRM
3623 ,X_MSG_COUNT => X_MSG_COUNT
3624 ,X_MSG_DATA => X_MSG_DATA
3625 ,X_RETURN_STATUS => X_RETURN_STATUS);
3626
3627 WHEN OTHERS THEN
3628 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3629 P_API_NAME => L_API_NAME
3630 ,P_PKG_NAME => G_PKG_NAME
3631 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
3632 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3633 ,P_SQLCODE => SQLCODE
3634 ,P_SQLERRM => SQLERRM
3635 ,X_MSG_COUNT => X_MSG_COUNT
3636 ,X_MSG_DATA => X_MSG_DATA
3637 ,X_RETURN_STATUS => X_RETURN_STATUS);
3638
3639 END Check_Customer_Accounts;
3640
3641
3642
3643 END ASO_CHECK_TCA_PVT;