[Home] [Help]
PACKAGE BODY: APPS.ASO_CHECK_TCA_PVT
Source
1 PACKAGE BODY ASO_CHECK_TCA_PVT as
2 /* $Header: asovctcb.pls 120.19 2010/12/06 17:18:43 rassharm 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');
336 FND_MESSAGE.Set_Token('ID', to_char(p_qte_rec.party_id), FALSE);
337 FND_MSG_PUB.ADD;
338 END IF;
339 raise FND_API.G_EXC_ERROR;
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,
359 x_msg_count => x_msg_count,
356 p_qte_rec => p_qte_rec,
357 p_header_shipment_tbl => p_header_shipment_tbl,
358 x_return_status => x_return_status,
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;
468
469 IF FND_API.to_boolean(p_init_msg_list) THEN
470 FND_MSG_PUB.initialize;
471 END IF;
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
496 FETCH C_Site_Use INTO l_party_site_use_id;
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);
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' );
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;
588 IF l_invoice_cust_account_id IS NOT NULL AND l_invoice_cust_account_id <> FND_API.G_MISS_NUM THEN
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;
618 aso_debug_pub.add('chk_hdr: invoice_to_party = ' || p_qte_rec.invoice_to_party_id,1, 'N' );
615
616 END IF;
617 IF aso_debug_pub.g_debug_flag = 'Y' THEN
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: '
697 ||p_qte_rec.End_Customer_cust_account_id, 1, 'N');
698 END IF;
699
700
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;
739 FND_MSG_PUB.ADD;
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');
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
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
809 p_header_shipment_tbl(1).ship_to_cust_account_id = FND_API.G_MISS_NUM) THEN
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;
860 AND p_header_shipment_tbl(1).ship_to_party_site_id <> FND_API.G_MISS_NUM THEN
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
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
914
915 END IF; -- shipment tbl count
916
917 IF aso_debug_pub.g_debug_flag = 'Y' THEN
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';
982 l_invoice_contact_party_id NUMBER;
979 l_site_use_id NUMBER;
980 l_invoice_cust_account_id NUMBER;
981 l_End_cust_account_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,
1043 p_api_version,
1044 l_api_name,
1045 g_pkg_name
1046 ) THEN
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
1110 P_line_Shipment_Tbl(j).ship_to_cust_account_id <> FND_API.G_MISS_NUM OR
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
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,
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,
1150 p_cust_party_id => p_qte_line_rec.invoice_to_cust_party_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'
1227 ,x_msg_count => x_msg_count
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
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,
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,
1256 x_msg_count => x_msg_count,
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');
1343 IF p_qte_line_rec.End_Customer_cust_account_id is not NULL AND
1340 END IF;
1341
1342
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
1362 AND p_qte_line_rec.End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
1363
1364 Customer_Account_Site(
1365 p_api_version => 1.0
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,
1458 x_return_status => x_return_status,
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,
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
1470 END IF; -- ship cust_account_id
1471
1472 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1473 aso_debug_pub.add('Check_Tca: p_line_shipment_tbl(j).ship_to_cust_account_id: '||
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
1577 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1574 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1575 P_API_NAME => L_API_NAME
1576 ,P_PKG_NAME => G_PKG_NAME
1578 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1579 ,P_SQLCODE => SQLCODE
1580 ,P_SQLERRM => SQLERRM
1581 ,X_MSG_COUNT => X_MSG_COUNT
1582 ,X_MSG_DATA => X_MSG_DATA
1583 ,X_RETURN_STATUS => X_RETURN_STATUS);
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
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);
1714 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1711 FETCH C_Get_Account_Count INTO l_acct_count;
1712 CLOSE C_Get_Account_Count;
1713
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 -- ER 5484749
1821 IF p_calling_api_flag = 2 AND (l_create_acct_prof = 'NEVER') THEN
1822 x_return_status := FND_API.G_RET_STS_ERROR;
1823 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
1824 FND_MSG_PUB.ADD;
1825 RAISE FND_API.G_EXC_ERROR;
1826 END IF;
1827
1828
1829 IF (l_create_acct_prof = 'ALWAYS') OR
1830 (l_create_acct_prof = 'AS_REQUIRED' AND p_calling_api_flag = 1) OR
1831 (p_calling_api_flag = 2) THEN -- ER 5484749
1832 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1833 aso_debug_pub.add('creating customer account: create_acct_prof: '||l_create_acct_prof,1,'N');
1834 END IF;
1835 IF p_Party_Id is not NULL
1836 AND p_Party_Id <> FND_API.G_MISS_NUM THEN
1837 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1838 aso_debug_pub.add('creating cust account',1,'N');
1839 END IF;
1840 ASO_PARTY_INT.Create_Customer_Account(
1841 p_api_version => 1.0,
1842 P_Party_id => p_Party_Id,
1843 x_return_status => x_return_status,
1844 x_msg_count => x_msg_count,
1848 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1845 x_msg_data => x_msg_data,
1846 x_cust_acct_id => cust_account_id);
1847 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1849 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
1850 FND_MESSAGE.Set_Token('ID', to_char(p_Party_Id), FALSE);
1851 FND_MSG_PUB.ADD;
1852 END IF;
1853 raise FND_API.G_EXC_ERROR;
1854 END IF;
1855 x_Cust_Acct_Id := cust_account_id;
1856 END IF; -- end party if
1857 ELSIF p_calling_api_flag <> 0 THEN -- profile is N raise error
1858 x_return_status := FND_API.G_RET_STS_ERROR;
1859 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1860 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
1861 FND_MESSAGE.Set_Token('ID', to_char(p_Party_Id), FALSE);
1862 FND_MSG_PUB.ADD;
1863 END IF;
1864 raise FND_API.G_EXC_ERROR;
1865 END IF; -- end profile condition
1866
1867 ELSE -- lx_cust_id not null
1868
1869 x_Cust_Acct_Id := lx_cust_id;
1870
1871 END IF; -- lx_cust_id
1872
1873 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1874 aso_debug_pub.add('x_cust_acct_id = '|| x_cust_acct_id,1,'N');
1875 END IF;
1876 FND_MSG_PUB.Count_And_Get
1877 ( p_count => x_msg_count,
1878 p_data => x_msg_data
1879 );
1880
1881 EXCEPTION
1882 WHEN FND_API.G_EXC_ERROR THEN
1883 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1884 P_API_NAME => L_API_NAME
1885 ,P_PKG_NAME => G_PKG_NAME
1886 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1887 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1888 ,P_SQLCODE => SQLCODE
1889 ,P_SQLERRM => SQLERRM
1890 ,X_MSG_COUNT => X_MSG_COUNT
1891 ,X_MSG_DATA => X_MSG_DATA
1892 ,X_RETURN_STATUS => X_RETURN_STATUS);
1893
1894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1895 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1896 P_API_NAME => L_API_NAME
1897 ,P_PKG_NAME => G_PKG_NAME
1898 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1899 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1900 ,P_SQLCODE => SQLCODE
1901 ,P_SQLERRM => SQLERRM
1902 ,X_MSG_COUNT => X_MSG_COUNT
1903 ,X_MSG_DATA => X_MSG_DATA
1904 ,X_RETURN_STATUS => X_RETURN_STATUS);
1905
1906 WHEN OTHERS THEN
1907 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1908 P_API_NAME => L_API_NAME
1909 ,P_PKG_NAME => G_PKG_NAME
1910 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1911 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1912 ,P_SQLCODE => SQLCODE
1913 ,P_SQLERRM => SQLERRM
1914 ,X_MSG_COUNT => X_MSG_COUNT
1915 ,X_MSG_DATA => X_MSG_DATA
1916 ,X_RETURN_STATUS => X_RETURN_STATUS);
1917
1918 END Customer_Account;
1919
1920
1921
1922 PROCEDURE Customer_Account_Site
1923 (
1924 p_api_version IN NUMBER,
1925 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1926 p_commit IN VARCHAR2 := FND_API.g_false,
1927 p_party_site_id IN NUMBER,
1928 p_acct_site_type IN VARCHAR2,
1929 p_cust_account_id IN NUMBER,
1930 x_cust_acct_site_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
1931 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1932 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1933 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1934 x_site_use_id OUT NOCOPY /* file.sql.39 change */ number
1935 )
1936 IS
1937 CURSOR party_cur IS
1938 SELECT a.party_type, a.party_id
1939 from
1940 HZ_PARTIES a, HZ_PARTY_SITES b
1941 where
1942 a.status = 'A'
1943 and b.status = 'A'
1944 and b.party_site_id = p_party_site_id
1945 and b.party_id = a.party_id;
1946
1947 CURSOR active_site_cur IS
1948 select cust_acct_site_id, status
1949 from
1950 hz_cust_acct_sites
1951 where
1952 cust_account_id = p_cust_account_id
1953 and party_site_id = p_party_site_id
1954 and status = 'A';
1955
1956 CURSOR inactive_site_cur IS
1957 select cust_acct_site_id, status
1958 from
1959 hz_cust_acct_sites
1960 where
1961 cust_account_id = p_cust_account_id
1962 and party_site_id = p_party_site_id
1963 and status <> 'A';
1964
1965 CURSOR active_site_use_cur(l_acct_site_id NUMBER, l_site_type VARCHAR2) IS
1966 select site_use_id, status
1967 from
1968 hz_cust_site_uses
1969 where
1970 cust_acct_site_id = l_acct_site_id
1971 and site_use_code = l_site_type
1972 and status = 'A';
1973
1974 CURSOR inactive_site_use_cur(l_acct_site_id NUMBER, l_site_type VARCHAR2) IS
1975 select site_use_id, status
1976 from
1977 hz_cust_site_uses
1978 where
1979 cust_acct_site_id = l_acct_site_id
1980 and site_use_code = l_site_type
1981 and status <> 'A';
1982
1983
1984 /*
1985 CURSOR site_use_cur(cust_acct NUMBER, party_site NUMBER, site_type VARCHAR2) IS
1986 select a.site_use_id, b.status,a.status
1990 b.cust_account_id = cust_acct
1987 from
1988 hz_cust_site_uses a, hz_cust_acct_sites b
1989 where
1991 and b.party_site_id = party_site
1992 and a.cust_acct_site_id = b.cust_acct_site_id
1993 and a.site_use_code = site_type;
1994 */
1995 l_party_id number;
1996 cur_party_id number;
1997 l_cust_account_id number;
1998 cust_acct_site_status varchar2(1);
1999 cust_site_use_status varchar2(1);
2000
2001 CURSOR relationship_cur IS
2002 select a.object_id
2003 from
2004 hz_relationships a, hz_cust_accounts b
2005 where a.party_id = l_party_id
2006 and a.object_id = b.party_id
2007 and b.cust_account_id = p_cust_account_id;
2008
2009 l_api_version CONSTANT NUMBER := 1.0;
2010 l_api_name CONSTANT VARCHAR2(45) := 'Customer_Account_Site';
2011
2012 l_party_type VARCHAR2(30);
2013 l_acct_site_type VARCHAR2(50);
2014 l_site_use_id number;
2015
2016 lx_cust_acct_site_id NUMBER;
2017
2018 BEGIN
2019
2020 ---- Initialize---------------------
2021
2022 SAVEPOINT CUSTOMER_ACCOUNT_SITE_PVT;
2023
2024 IF FND_API.to_boolean(p_init_msg_list) THEN
2025 FND_MSG_PUB.initialize;
2026 END IF;
2027
2028 IF NOT FND_API.compatible_api_call(
2029 l_api_version,
2030 p_api_version,
2031 l_api_name,
2032 g_pkg_name
2033 ) THEN
2034 RAISE FND_API.g_exc_unexpected_error;
2035 END IF;
2036
2037 x_return_status := FND_API.G_RET_STS_SUCCESS;
2038
2039 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2040 aso_debug_pub.add('site type in Customer_Account_Site = ' || p_acct_site_type,1,'N');
2041 END IF;
2042
2043 IF p_acct_site_type = 'END_USER' THEN
2044 l_acct_site_type := 'SHIP_TO';
2045 ELSE
2046 l_acct_site_type := p_acct_site_type;
2047 END IF;
2048
2049 OPEN party_cur;
2050 FETCH party_cur INTO l_party_type, l_party_id;
2051 IF (party_cur%NOTFOUND) THEN
2052 l_party_type := NULL;
2053
2054 x_return_status := FND_API.G_RET_STS_ERROR;
2055 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2056 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
2057 FND_MSG_PUB.ADD;
2058 END IF;
2059 raise FND_API.G_EXC_ERROR;
2060 END IF;
2061 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2062 aso_debug_pub.add('party_type in Customer_Account_Site = ' || l_party_type,1,'N');
2063 END IF;
2064 CLOSE party_cur;
2065
2066 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2067 OPEN relationship_cur;
2068 FETCH relationship_cur INTO cur_party_id;
2069 IF (relationship_cur%NOTFOUND) THEN
2070 cur_party_id := NULL;
2071 x_return_status := FND_API.G_RET_STS_ERROR;
2072 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2073 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
2074 FND_MSG_PUB.ADD;
2075 END IF;
2076 raise FND_API.G_EXC_ERROR;
2077 END IF;
2078 CLOSE relationship_cur;
2079 ELSE
2080 cur_party_id := l_party_id;
2081 END IF;
2082
2083 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2084 aso_debug_pub.add('cur_party_id = ' || cur_party_id,1,'N');
2085 END IF;
2086
2087 -- bug 4947772
2088 OPEN active_site_cur;
2089 FETCH active_site_cur INTO x_cust_acct_site_id, cust_acct_site_status;
2090 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2091 aso_debug_pub.add('x_cust_acct_site_id in Customer_Account_Site = ' || x_cust_acct_site_id,1,'N');
2092 aso_debug_pub.add('Customer_Account_Site: cust_acct_site_status: ' || cust_acct_site_status,1,'N');
2093 END IF;
2094
2095 IF active_site_cur%NOTFOUND THEN
2096
2097 -- this means there are no active sites
2098 OPEN inactive_site_cur;
2099 FETCH inactive_site_cur INTO x_cust_acct_site_id, cust_acct_site_status;
2100
2101 IF inactive_site_cur%FOUND THEN
2102 x_cust_acct_site_id := NULL;
2103 x_site_use_id := NULL;
2104 x_return_status := FND_API.G_RET_STS_ERROR;
2105 IF p_acct_site_type = 'BILL_TO' THEN
2106 FND_MESSAGE.Set_Name('ASO','ASO_INV_CUST_SITE_INACTIVE');
2107 ELSIF p_acct_site_type = 'END_USER' THEN
2108 FND_MESSAGE.Set_Name('ASO','ASO_END_CUST_SITE_INACTIVE');
2109 ELSIF p_acct_site_type = 'SHIP_TO' THEN
2110 FND_MESSAGE.Set_Name('ASO','ASO_SHP_CUST_SITE_INACTIVE');
2111 END IF;
2112 FND_MSG_PUB.ADD;
2113 raise FND_API.G_EXC_ERROR;
2114 END IF;
2115 CLOSE inactive_site_cur;
2116 END IF; -- active_site_cur%NOTFOUND end if
2117 CLOSE active_site_cur;
2118
2119
2120
2121
2122 IF (x_cust_acct_site_id IS NOT NULL) AND
2123 (x_cust_acct_site_id <> FND_API.G_MISS_NUM) THEN
2124
2125
2126 OPEN active_site_use_cur(x_cust_acct_site_id, l_acct_site_type);
2127 FETCH active_site_use_cur INTO x_site_use_id, cust_site_use_status;
2128 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2129 aso_debug_pub.add('Customer_Account_Site: cust_site_use_status: ' || cust_site_use_status,1,'N');
2130 aso_debug_pub.add('Customer_Account_Site: x_site_use_id: ' || x_site_use_id,1,'N');
2131 END IF;
2132 IF (active_site_use_cur%NOTFOUND) THEN
2133 -- this means there are no active site uses
2134 OPEN inactive_site_use_cur(x_cust_acct_site_id, l_acct_site_type);
2138 x_return_status := FND_API.G_RET_STS_ERROR;
2135 FETCH inactive_site_use_cur INTO x_site_use_id, cust_site_use_status;
2136 IF inactive_site_use_cur%FOUND THEN
2137 x_site_use_id := NULL;
2139 IF p_acct_site_type = 'BILL_TO' THEN
2140 FND_MESSAGE.Set_Name('ASO','ASO_INV_CUST_SITE_INACTIVE');
2141 ELSIF p_acct_site_type = 'END_USER' THEN
2142 FND_MESSAGE.Set_Name('ASO','ASO_END_CUST_SITE_INACTIVE');
2143 ELSIF p_acct_site_type = 'SHIP_TO' THEN
2144 FND_MESSAGE.Set_Name('ASO','ASO_SHP_CUST_SITE_INACTIVE');
2145 END IF;
2146 FND_MSG_PUB.ADD;
2147 raise FND_API.G_EXC_ERROR;
2148 ELSE
2149 -- this means that there no site uses , either active or inactive
2150 x_site_use_id := NULL;
2151 END IF;
2152 CLOSE inactive_site_use_cur;
2153 END IF;
2154 CLOSE active_site_use_cur;
2155
2156 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2157 aso_debug_pub.add('site_use_id in Customer_Account_Site = ' || x_site_use_id,1,'N');
2158 END IF;
2159
2160 END IF;
2161
2162 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2163 aso_debug_pub.add('Customer_Account_Site: x_site_use_id: ' || x_site_use_id,1,'N');
2164 END IF;
2165 IF x_site_use_id is NULL OR
2166 x_site_use_id = FND_API.G_MISS_NUM THEN
2167
2168 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2169 aso_debug_pub.add('calling create accsite uses ',1, 'N');
2170 END IF;
2171 ASO_PARTY_INT.Create_ACCT_SITE_USES (
2172 p_api_version => 1.0
2173 ,P_Cust_Account_Id => p_cust_account_id
2174 ,P_Party_Site_Id => p_party_site_id
2175 ,P_cust_acct_site_id => x_cust_acct_site_id
2176 ,P_Acct_Site_type => l_Acct_Site_Type
2177 ,x_cust_acct_site_id => lx_cust_acct_site_id
2178 ,x_return_status => x_return_status
2179 ,x_msg_count => x_msg_count
2180 ,x_msg_data => x_msg_data
2181 ,x_site_use_id => x_site_use_id
2182 );
2183
2184 x_cust_acct_site_id := lx_cust_acct_site_id;
2185
2186 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2187 aso_debug_pub.add('site_use_id after deriving invoice = ' || x_site_use_id,1, 'Y');
2188 END IF;
2189 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2190 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2191 FND_MESSAGE.Set_Name('ASO', 'ASO_ACCT_SITE_USE');
2192 FND_MESSAGE.Set_Token('ID', to_char(p_party_site_id),FALSE);
2193 FND_MSG_PUB.ADD;
2194 END IF;
2195 raise FND_API.G_EXC_ERROR;
2196 END IF;
2197 END IF;
2198
2199 FND_MSG_PUB.Count_And_Get
2200 ( p_count => x_msg_count,
2201 p_data => x_msg_data
2202 );
2203
2204 EXCEPTION
2205 WHEN FND_API.G_EXC_ERROR THEN
2206 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2207 P_API_NAME => L_API_NAME
2208 ,P_PKG_NAME => G_PKG_NAME
2209 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2210 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2211 ,P_SQLCODE => SQLCODE
2212 ,P_SQLERRM => SQLERRM
2213 ,X_MSG_COUNT => X_MSG_COUNT
2214 ,X_MSG_DATA => X_MSG_DATA
2215 ,X_RETURN_STATUS => X_RETURN_STATUS);
2216
2217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2218 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2219 P_API_NAME => L_API_NAME
2220 ,P_PKG_NAME => G_PKG_NAME
2221 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2222 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2223 ,P_SQLCODE => SQLCODE
2224 ,P_SQLERRM => SQLERRM
2225 ,X_MSG_COUNT => X_MSG_COUNT
2226 ,X_MSG_DATA => X_MSG_DATA
2227 ,X_RETURN_STATUS => X_RETURN_STATUS);
2228
2229 WHEN OTHERS THEN
2230 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2231 P_API_NAME => L_API_NAME
2232 ,P_PKG_NAME => G_PKG_NAME
2233 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2234 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2235 ,P_SQLCODE => SQLCODE
2236 ,P_SQLERRM => SQLERRM
2237 ,X_MSG_COUNT => X_MSG_COUNT
2238 ,X_MSG_DATA => X_MSG_DATA
2239 ,X_RETURN_STATUS => X_RETURN_STATUS);
2240
2241 END Customer_Account_Site;
2242
2243
2244
2245 PROCEDURE Cust_Acct_Relationship(
2246 p_api_version IN NUMBER,
2247 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2248 p_commit IN VARCHAR2 := FND_API.g_false,
2249 p_sold_to_cust_account IN NUMBER,
2250 p_related_cust_account IN NUMBER,
2251 p_relationship_type IN VARCHAR2,
2252 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2253 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2254 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2255 ) IS
2256
2257 l_api_version CONSTANT NUMBER := 1.0;
2258 l_api_name CONSTANT VARCHAR2(45) := 'CUST_ACCT_RELATIONSHIP';
2259
2260 -- TRUE if there are matching row for the cust_account_id and relate_cust_account_id.
2261 l_cust_acct_match VARCHAR2(1) := FND_API.G_FALSE;
2262 -- TRUE if the matching rows also match the bill_to_flag or ship_to_flag.
2263 l_cust_acct_flag_match VARCHAR2(1) := FND_API.G_FALSE;
2264
2265 l_bill_to_flag VARCHAR2(1);
2266 l_ship_to_flag VARCHAR2(1);
2270 SELECT bill_to_flag, ship_to_flag, last_update_date
2267 l_last_update_date DATE;
2268
2269 CURSOR c_cust_acct_relate IS
2271 FROM hz_cust_acct_relate
2272 WHERE cust_account_id = p_related_cust_account
2273 AND related_cust_account_id = p_sold_to_cust_account
2274 AND STATUS = 'A';
2275 BEGIN
2276 ---- Initialize---------------------
2277
2278 SAVEPOINT CUST_ACCT_RELATIONSHIP_PVT;
2279
2280 IF FND_API.to_boolean(p_init_msg_list) THEN
2281 FND_MSG_PUB.initialize;
2282 END IF;
2283
2284 IF NOT FND_API.compatible_api_call(
2285 l_api_version,
2286 p_api_version,
2287 l_api_name,
2288 g_pkg_name
2289 ) THEN
2290 RAISE FND_API.g_exc_unexpected_error;
2291 END IF;
2292
2293 x_return_status := FND_API.g_ret_sts_success;
2294 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2295 aso_debug_pub.add('Entering cust acct relationship ',1, 'Y');
2296 END IF;
2297
2298 OPEN c_cust_acct_relate;
2299 FETCH c_cust_acct_relate
2300 INTO l_bill_to_flag, l_ship_to_flag, l_last_update_date;
2301 IF c_cust_acct_relate%FOUND THEN
2302 l_cust_acct_match := FND_API.G_TRUE;
2303 END IF;
2304 CLOSE c_cust_acct_relate;
2305
2306 IF FND_API.TO_Boolean(l_cust_acct_match) THEN
2307 IF (p_relationship_type = 'BILL_TO') AND (l_bill_to_flag = 'Y') THEN
2308 l_cust_acct_flag_match := FND_API.G_TRUE;
2309 ELSIF (p_relationship_type = 'SHIP_TO') AND (l_ship_to_flag = 'Y') THEN
2310 l_cust_acct_flag_match := FND_API.G_TRUE;
2311 END IF;
2312 END IF;
2313
2314 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2315 aso_debug_pub.add('cust acct relationship: l_cust_acct_match is ' || l_cust_acct_match,1, 'N');
2316 END IF;
2317 IF NOT FND_API.TO_Boolean(l_cust_acct_flag_match) THEN
2318 -- no matching rows
2319 IF NOT FND_API.TO_Boolean(l_cust_acct_match) THEN
2320 -- the account ids are not matched.
2321 ASO_PARTY_INT.Create_Cust_Acct_Relationship(
2322
2323 p_api_version => 1.0,
2324 p_sold_to_cust_account => p_sold_to_cust_account,
2325 p_related_cust_account => p_related_cust_account,
2326 p_relationship_type => p_relationship_type,
2327 x_return_status => x_return_status,
2328 x_msg_count => x_msg_count,
2329 x_msg_data => x_msg_data
2330 );
2331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2332 RAISE FND_API.G_EXC_ERROR;
2333 END IF;
2334 ELSE
2335 /*bug 8239850*/
2336 IF FND_API.TO_Boolean(l_cust_acct_match) THEN
2337 IF NOT FND_API.TO_Boolean(l_cust_acct_flag_match) THEN
2338 aso_debug_pub.add('before update_Cust_Acct_Relationship: l_cust_acct_match is ' || l_cust_acct_match,1, 'N');
2339 ASO_PARTY_INT.update_Cust_Acct_Relationship(
2340 p_api_version => 1.0,
2341 p_sold_to_cust_account => p_sold_to_cust_account,
2342 p_related_cust_account => p_related_cust_account,
2343 p_relationship_type => p_relationship_type,
2344 x_return_status => x_return_status,
2345 x_msg_count => x_msg_count,
2346 x_msg_data => x_msg_data
2347 );
2348
2349 end if;
2350 end if;
2351 -- the account ids are matched but bill_to_flag or ship_to_flag is not matched.
2352 -- and the profile is 'Y'.
2353 --- x_return_status := FND_API.G_RET_STS_ERROR;
2354 --IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2355 -- FND_MESSAGE.Set_Name(' + appShortName +', 'ASO_INVALID_ACCT_RELTN');
2356 -- FND_MESSAGE.Set_Token('TYPE', p_relationship_type, FALSE);
2357 -- FND_MSG_PUB.ADD;
2358 -- END IF;
2359 -- RAISE FND_API.G_EXC_ERROR;
2360
2361 END IF;
2362 /*else
2363 x_return_status := FND_API.G_RET_STS_ERROR;
2364 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2365 FND_MESSAGE.Set_Name(' + appShortName +', 'ASO_INVALID_ACCT_RELTN');
2366 FND_MESSAGE.Set_Token('TYPE', p_relationship_type, FALSE);
2367 FND_MSG_PUB.ADD;
2368 END IF;
2369 RAISE FND_API.G_EXC_ERROR; */
2370
2371
2372
2373 END IF; -- l_cust_acct_flag_match
2374
2375
2376 FND_MSG_PUB.Count_And_Get
2377 ( p_count => x_msg_count,
2378 p_data => x_msg_data
2379 );
2380
2381 EXCEPTION
2382 WHEN FND_API.G_EXC_ERROR THEN
2383 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2384 P_API_NAME => L_API_NAME
2385 ,P_PKG_NAME => G_PKG_NAME
2386 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2387 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2388 ,P_SQLCODE => SQLCODE
2389 ,P_SQLERRM => SQLERRM
2390 ,X_MSG_COUNT => X_MSG_COUNT
2391 ,X_MSG_DATA => X_MSG_DATA
2392 ,X_RETURN_STATUS => X_RETURN_STATUS);
2393
2394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2395 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2396 P_API_NAME => L_API_NAME
2397 ,P_PKG_NAME => G_PKG_NAME
2398 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2399 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2400 ,P_SQLCODE => SQLCODE
2401 ,P_SQLERRM => SQLERRM
2402 ,X_MSG_COUNT => X_MSG_COUNT
2403 ,X_MSG_DATA => X_MSG_DATA
2404 ,X_RETURN_STATUS => X_RETURN_STATUS);
2405
2406 WHEN OTHERS THEN
2407 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2408 P_API_NAME => L_API_NAME
2412 ,P_SQLCODE => SQLCODE
2409 ,P_PKG_NAME => G_PKG_NAME
2410 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2411 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2413 ,P_SQLERRM => SQLERRM
2414 ,X_MSG_COUNT => X_MSG_COUNT
2415 ,X_MSG_DATA => X_MSG_DATA
2416 ,X_RETURN_STATUS => X_RETURN_STATUS);
2417
2418 END Cust_Acct_Relationship;
2419
2420
2421 PROCEDURE Cust_Acct_Contact_Addr
2422 (
2423 p_api_version IN NUMBER,
2424 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2425 p_commit IN VARCHAR2 := FND_API.g_false,
2426 p_party_site_id IN NUMBER,
2427 p_role_type IN VARCHAR2,
2428 p_cust_account_id IN NUMBER,
2429 p_party_id IN NUMBER,
2430 p_cust_account_site IN NUMBER,
2431 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2432 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2433 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2434 x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */ number
2435 )
2436 IS
2437 l_contact_id NUMBER;
2438 l_contact_party_id NUMBER;
2439 l_api_version CONSTANT NUMBER := 1.0;
2440 l_api_name CONSTANT VARCHAR2(45) := 'Cust_Acct_Contact_Addr';
2441 l_role_type VARCHAR2(50);
2442
2443 BEGIN
2444 ---- Initialize---------------------
2445 SAVEPOINT CUST_ACCT_CONTACT_ADDR_PVT;
2446
2447 IF FND_API.to_boolean(p_init_msg_list) THEN
2448 FND_MSG_PUB.initialize;
2449 END IF;
2450
2451 IF NOT FND_API.compatible_api_call(
2452 l_api_version,
2453 p_api_version,
2454 l_api_name,
2455 g_pkg_name
2456 ) THEN
2457 RAISE FND_API.g_exc_unexpected_error;
2458 END IF;
2459
2460 x_return_status := FND_API.G_RET_STS_SUCCESS;
2461
2462 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2463 aso_debug_pub.add('Cust_acct_contact_Addr: p_role_type: '||p_role_type,1,'N');
2464 END IF;
2465
2466 IF p_role_type = 'END_USER' THEN
2467 l_role_type := 'SHIP_TO';
2468 ELSE
2469 l_role_type := p_role_type;
2470 END IF;
2471
2472 IF p_party_id is not NULL
2473 AND p_party_id<> FND_API.G_MISS_NUM THEN
2474
2475 IF p_party_site_id is not NULL AND
2476 p_party_site_id <> FND_API.G_MISS_NUM THEN
2477
2478 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2479 aso_debug_pub.add('deriving cust acct role for party id ',1,'N');
2480 END IF;
2481
2482 ASO_MAP_QUOTE_ORDER_INT.get_cust_acct_roles(
2483 p_party_id =>p_party_id
2484 ,p_party_site_id => p_party_site_id
2485 ,p_acct_site_type => l_role_type
2486 ,p_cust_account_id => p_cust_account_id
2487 ,x_return_status => x_return_status
2488 ,x_party_id => l_contact_party_id
2489 ,x_cust_account_role_id => l_contact_id);
2490 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2491 aso_debug_pub.add('status after get cust acct roles in Cust_Acct_Contact_Addr = ' || x_return_status,1,'N');
2492 END IF;
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 IF p_role_type = 'BILL_TO' THEN
2496 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_PARTY_AC_CRS');
2497 ELSIF p_role_type = 'END_USER' THEN
2498 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_END_PARTY_AC_CRS');
2499 ELSIF p_role_type = 'SHIP_TO' THEN
2500 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_SHP_PARTY_AC_CRS');
2501 END IF;
2502 FND_MSG_PUB.ADD;
2503 END IF;
2504 raise FND_API.G_EXC_ERROR;
2505
2506 ELSE -- x_ret_status = success
2507 IF l_contact_party_id <> FND_API.G_MISS_NUM THEN
2508 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2509 aso_debug_pub.add('cust account site = ' ||p_cust_account_site,1,'N');
2510 END IF;
2511 ASO_PARTY_INT.Create_Contact_Role (
2512 p_api_version => 1.0
2513 ,p_party_id => l_contact_party_id
2514 ,p_Cust_account_id => p_cust_account_id
2515 ,p_cust_account_site_id => p_cust_account_site
2516 ,p_responsibility_type => l_role_type
2517 ,p_role_id => l_contact_id
2518 ,x_return_status =>x_return_status
2519 ,x_msg_count => x_msg_count
2520 ,x_msg_data => x_msg_data
2521 ,x_cust_account_role_id => x_cust_account_role_id);
2522
2523 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2524 aso_debug_pub.add('status after create contact role ship = '|| x_return_status,1,'N');
2525 END IF;
2526
2527 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2528 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2529 FND_MESSAGE.Set_Name('ASO', 'ASO_CREATE_CONTACT');
2530 FND_MESSAGE.Set_Token('ID', to_char(
2531 l_contact_party_id), FALSE);
2532 FND_MSG_PUB.ADD;
2533 END IF;
2534 raise FND_API.G_EXC_ERROR;
2535 END IF; -- for creatE_contact_role
2536
2537 END IF; -- for contact_party_id
2538
2539 END IF; -- x_ret_status
2540
2541 END IF; -- for party site
2542
2543 END IF; -- for party id
2544
2545 FND_MSG_PUB.Count_And_Get
2546 ( p_count => x_msg_count,
2547 p_data => x_msg_data
2548 );
2549
2550 EXCEPTION
2551 WHEN FND_API.G_EXC_ERROR THEN
2552 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2556 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2553 P_API_NAME => L_API_NAME
2554 ,P_PKG_NAME => G_PKG_NAME
2555 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2557 ,P_SQLCODE => SQLCODE
2558 ,P_SQLERRM => SQLERRM
2559 ,X_MSG_COUNT => X_MSG_COUNT
2560 ,X_MSG_DATA => X_MSG_DATA
2561 ,X_RETURN_STATUS => X_RETURN_STATUS);
2562
2563 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2564 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2565 P_API_NAME => L_API_NAME
2566 ,P_PKG_NAME => G_PKG_NAME
2567 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2568 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2569 ,P_SQLCODE => SQLCODE
2570 ,P_SQLERRM => SQLERRM
2571 ,X_MSG_COUNT => X_MSG_COUNT
2572 ,X_MSG_DATA => X_MSG_DATA
2573 ,X_RETURN_STATUS => X_RETURN_STATUS);
2574 WHEN OTHERS THEN
2575 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2576 P_API_NAME => L_API_NAME
2577 ,P_PKG_NAME => G_PKG_NAME
2578 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2579 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2580 ,P_SQLCODE => SQLCODE
2581 ,P_SQLERRM => SQLERRM
2582 ,X_MSG_COUNT => X_MSG_COUNT
2583 ,X_MSG_DATA => X_MSG_DATA
2584 ,X_RETURN_STATUS => X_RETURN_STATUS);
2585
2586 END Cust_Acct_Contact_Addr;
2587
2588
2589
2590 PROCEDURE Assign_Customer_Accounts(
2591 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2592 p_qte_header_id IN NUMBER,
2593 p_calling_api_flag IN NUMBER := 0,
2594 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2595 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2596 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2597 )
2598 IS
2599
2600 CURSOR C_Validate_Quote (qte_hdr NUMBER) IS
2601 SELECT 'X'
2602 FROM aso_quote_headers_all
2603 WHERE quote_header_id = qte_hdr;
2604
2605 CURSOR C_Get_Hdr_Acct(qte_hdr NUMBER) IS
2606 SELECT a.cust_account_id, a.invoice_to_cust_account_id, a.cust_party_id,
2607 a.invoice_to_cust_party_id, a.party_id, a.sold_to_party_site_id,
2608 a.invoice_to_party_id, a.invoice_to_party_site_id,
2609 a.End_Customer_party_id, a.End_Customer_party_site_id,
2610 a.End_Customer_cust_party_id, a.End_Customer_cust_account_id,
2611 b.ship_to_party_id, b.ship_to_party_site_id,
2612 b.ship_to_cust_party_id, b.ship_to_cust_account_id,
2613 b.shipment_id
2614 FROM aso_quote_headers_all a, aso_shipments b
2615 WHERE a.quote_header_id = qte_hdr
2616 AND a.quote_header_id = b.quote_header_id
2617 AND b.quote_line_id is NULL;
2618
2619
2620 CURSOR C_Get_Line_Acct(qte_hdr NUMBER) IS
2621 SELECT a.invoice_to_cust_account_id, a.invoice_to_cust_party_id, a.quote_line_id,
2622 a.invoice_to_party_id, a.invoice_to_party_site_id,
2623 a.End_Customer_cust_account_id, a.End_Customer_cust_party_id,
2624 a.End_Customer_party_id, a.End_Customer_party_site_id,
2625 b.ship_to_cust_account_id, b.ship_to_cust_party_id, b.shipment_id,
2626 b.ship_to_party_id, b.ship_to_party_site_id
2627 FROM aso_quote_lines_all a, aso_shipments b
2628 WHERE a.quote_header_id = qte_hdr
2629 AND a.quote_line_id = b.quote_line_id
2630 AND ((a.invoice_to_cust_account_id IS NULL
2631 AND a.invoice_to_cust_party_id IS NOT NULL)
2632 OR (a.End_Customer_cust_account_id is NULL
2633 AND a.End_Customer_cust_party_id IS NOT NULL)
2634 OR (b.ship_to_cust_account_id is NULL
2635 AND b.ship_to_cust_party_id IS NOT NULL));
2636
2637 l_end_cust_acct NUMBER;
2638 l_end_cust_party NUMBER;
2639 l_cust_acct NUMBER;
2640 l_inv_cust_acct NUMBER;
2641 l_cust_party NUMBER;
2642 l_inv_cust_party NUMBER;
2643 l_account_id NUMBER;
2644 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
2645 l_header_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2646 l_qte_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
2647 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
2648 l_header_check_tca_flag VARCHAR(1) := 'N';
2649 l_dummy VARCHAR2(1) := NULL;
2650 l_last_update_date date := SYSDATE;
2651 l_g_user_id number := fnd_global.user_id;
2652 l_g_login_id number := fnd_global.conc_login_id;
2653 l_api_version CONSTANT NUMBER := 1.0;
2654 l_api_name CONSTANT VARCHAR2(45) := 'Assign_Customer_Accounts';
2655
2656 BEGIN
2657
2658 -- Standard Start of API savepoint
2659 SAVEPOINT Assign_Customer_Accounts_PVT;
2660
2661 -- Initialize message list if p_init_msg_list is set to TRUE.
2662 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2663 FND_MSG_PUB.initialize;
2664 END IF;
2665
2666 -- Initialize API return status to success
2667 x_return_status := FND_API.G_RET_STS_SUCCESS;
2668
2669 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2670 aso_debug_pub.add('Assign_Customer_Accounts - Begin p_qte_header_id:'|| p_qte_header_id, 1, 'Y');
2671 END IF;
2672
2673 OPEN C_Validate_Quote (p_qte_header_id);
2674 FETCH C_Validate_Quote into l_dummy;
2675 IF C_Validate_Quote%NOTFOUND THEN
2676 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2677 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
2678 FND_MESSAGE.Set_Token('COLUMN', 'ORIGINAL_QUOTE_ID', FALSE);
2679 FND_MESSAGE.Set_Token('VALUE', TO_CHAR(p_qte_header_id), FALSE);
2680 FND_MSG_PUB.ADD;
2681 END IF;
2682
2683 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2684 aso_debug_pub.add('Assign_Customer_Accounts - Invalid qte_hdr_id', 1, 'Y');
2688 x_return_status := FND_API.G_RET_STS_ERROR;
2685 END IF;
2686
2687 CLOSE C_Validate_Quote;
2689 RAISE FND_API.G_EXC_ERROR;
2690 END IF;
2691 CLOSE C_Validate_Quote;
2692
2693 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2694 aso_debug_pub.add('Assign_Customer_Accounts - After qte_hdr_id check', 1, 'Y');
2695 END IF;
2696
2697 OPEN C_Get_Hdr_Acct(p_qte_header_id);
2698 FETCH C_Get_Hdr_Acct INTO
2699 l_qte_header_rec.cust_account_id,
2700 l_qte_header_rec.invoice_to_cust_account_id,
2701 l_qte_header_rec.cust_party_id,
2702 l_qte_header_rec.invoice_to_cust_party_id,
2703 l_qte_header_rec.party_id,
2704 l_qte_header_rec.sold_to_party_site_id,
2705 l_qte_header_rec.invoice_to_party_id,
2706 l_qte_header_rec.invoice_to_party_site_id,
2707 l_qte_header_rec.End_Customer_party_id,
2708 l_qte_header_rec.End_Customer_party_site_id,
2709 l_qte_header_rec.End_Customer_cust_party_id,
2710 l_qte_header_rec.End_Customer_cust_account_id,
2711 l_header_shipment_tbl(1).ship_to_party_id,
2712 l_header_shipment_tbl(1).ship_to_party_site_id,
2713 l_header_shipment_tbl(1).ship_to_cust_party_id,
2714 l_header_shipment_tbl(1).ship_to_cust_account_id,
2715 l_header_shipment_tbl(1).shipment_id;
2716 CLOSE C_Get_Hdr_Acct;
2717
2718 l_end_cust_acct := l_qte_header_rec.End_Customer_cust_account_id;
2719 l_end_cust_party := l_qte_header_rec.End_Customer_cust_party_id;
2720 l_cust_acct := l_qte_header_rec.cust_account_id;
2721 l_inv_cust_acct := l_qte_header_rec.invoice_to_cust_account_id;
2722 l_cust_party := l_qte_header_rec.cust_party_id;
2723 l_inv_cust_party := l_qte_header_rec.invoice_to_cust_party_id;
2724
2725
2726 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2727 aso_debug_pub.add('Assign_Customer_Accounts: p_qte_header_id: '||p_qte_header_id, 1, 'N');
2728 aso_debug_pub.add('Assign_Customer_Accounts: p_calling_api_flag: '||p_calling_api_flag, 1, 'N');
2729 aso_debug_pub.add('Assign_Customer_Accounts: l_cust_acct: '||l_cust_acct, 1, 'N');
2730 aso_debug_pub.add('Assign_Customer_Accounts: l_inv_cust_acct: '||l_inv_cust_acct, 1, 'N');
2731 aso_debug_pub.add('Assign_Customer_Accounts: l_cust_party: '||l_cust_party, 1, 'N');
2732 aso_debug_pub.add('Assign_Customer_Accounts: l_inv_cust_party: '||l_inv_cust_party, 1, 'N');
2733 aso_debug_pub.add('Assign_Customer_Accounts: l_end_cust_acct: '||l_end_cust_acct, 1, 'N');
2734 aso_debug_pub.add('Assign_Customer_Accounts: l_end_cust_party: '||l_end_cust_party, 1, 'N');
2735 aso_debug_pub.add('l_qte_header_rec.party_id = ' || l_qte_header_rec.party_id,1,'N');
2736 aso_debug_pub.add('l_qte_header_rec.sold_to_party_site_id = '||l_qte_header_rec.sold_to_party_site_id,1,'N');
2737 aso_debug_pub.add('l_qte_header_rec.invoice_to_party_id = ' || l_qte_header_rec.invoice_to_party_id,1,'N');
2738 aso_debug_pub.add('l_qte_header_rec.invoice_to_party_site_id = '|| l_qte_header_rec.invoice_to_party_site_id,1,'N');
2739 aso_debug_pub.add('l_qte_header_rec.End_Customer_party_id = ' || l_qte_header_rec.End_Customer_party_id,1,'N');
2740 aso_debug_pub.add('l_qte_header_rec.End_Customer_party_site_id = '|| l_qte_header_rec.End_Customer_party_site_id,1,'N');
2741 aso_debug_pub.add('l_header_shipment_tbl.count '|| l_header_shipment_tbl.count,1,'N');
2742 IF l_header_shipment_tbl.count > 0 THEN
2743 aso_debug_pub.add('l_header_shipment_tbl(1).shipment_id = '|| l_header_shipment_tbl(1).shipment_id,1,'N');
2744 aso_debug_pub.add('l_header_shipment_tbl(1).ship_to_party_id = '|| l_header_shipment_tbl(1).ship_to_party_id,1,'N');
2745 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');
2746 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');
2747 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');
2748 END IF;
2749 END IF;
2750
2751 IF l_cust_acct IS NULL THEN
2752 l_header_check_tca_flag := 'Y';
2753
2754 IF l_cust_party IS NOT NULL THEN
2755
2756 ASO_CHECK_TCA_PVT.Customer_Account(
2757 p_api_version => 1.0,
2758 p_Party_Id => l_cust_party,
2759 p_calling_api_flag => p_calling_api_flag,
2760 x_Cust_Acct_Id => l_account_id,
2761 x_return_status => x_return_status,
2762 x_msg_count => x_msg_count,
2763 x_msg_data => x_msg_data);
2764
2765 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2766 aso_debug_pub.add('Assign_Customer_Accounts 1: x_return_status: '||x_return_status, 1, 'N');
2767 aso_debug_pub.add('header sold to customer = ' || l_account_id);
2768 END IF;
2769
2770
2771 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2772 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2773 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
2774 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2775 FND_MESSAGE.Set_Token('ID', to_char( l_cust_party), FALSE);
2776 FND_MSG_PUB.ADD;
2777 end if;
2778 END IF;
2779 raise FND_API.G_EXC_ERROR;
2780
2781 ELSE
2782
2783 UPDATE ASO_QUOTE_HEADERS_ALL
2784 SET cust_account_id = l_account_id
2785 ,last_update_date = l_last_update_date
2786 ,last_updated_by = l_g_user_id
2787 ,last_update_login = l_g_login_id
2788 WHERE quote_header_id = p_qte_header_id;
2789 l_qte_header_rec.cust_account_id := l_account_id;
2790
2791
2792 END IF;
2793
2794 END IF;
2795
2796 END IF;
2797 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2798 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2799 END IF;
2800
2801
2802 IF l_inv_cust_acct IS NULL THEN
2803 l_header_check_tca_flag := 'Y';
2804
2805 IF l_inv_cust_party IS NOT NULL THEN
2809 p_Party_Id => l_inv_cust_party,
2806
2807 ASO_CHECK_TCA_PVT.Customer_Account(
2808 p_api_version => 1.0,
2810 p_calling_api_flag => p_calling_api_flag,
2811 x_Cust_Acct_Id => l_account_id,
2812 x_return_status => x_return_status,
2813 x_msg_count => x_msg_count,
2814 x_msg_data => x_msg_data);
2815
2816 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2817 aso_debug_pub.add('Assign_Customer_Accounts 2: x_return_status: '||x_return_status, 1, 'N');
2818 aso_debug_pub.add('header invoice to account = ' || l_account_id,1,'N');
2819 END IF;
2820
2821
2822 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2824 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
2825 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2826 FND_MESSAGE.Set_Token('ID', to_char( l_inv_cust_party), FALSE);
2827 FND_MSG_PUB.ADD;
2828 end if;
2829 END IF;
2830 raise FND_API.G_EXC_ERROR;
2831
2832 ELSE
2833
2834 UPDATE ASO_QUOTE_HEADERS_ALL
2835 SET invoice_to_cust_account_id = l_account_id
2836 ,last_update_date = l_last_update_date
2837 ,last_updated_by = l_g_user_id
2838 ,last_update_login = l_g_login_id
2839
2840 WHERE quote_header_id = p_qte_header_id;
2841 l_qte_header_rec.invoice_to_cust_account_id := l_account_id;
2842
2843
2844 END IF;
2845
2846 END IF;
2847
2848 END IF;
2849 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2850 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2851 END IF;
2852
2853 IF l_end_cust_acct IS NULL THEN
2854 l_header_check_tca_flag := 'Y';
2855
2856 IF l_end_cust_party IS NOT NULL THEN
2857
2858 ASO_CHECK_TCA_PVT.Customer_Account(
2859 p_api_version => 1.0,
2860 p_Party_Id => l_end_cust_party,
2861 p_calling_api_flag => p_calling_api_flag,
2862 x_Cust_Acct_Id => l_account_id,
2863 x_return_status => x_return_status,
2864 x_msg_count => x_msg_count,
2865 x_msg_data => x_msg_data);
2866
2867 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2868 aso_debug_pub.add('Assign_Customer_Accounts 2: x_return_status: '||x_return_status, 1, 'N');
2869 aso_debug_pub.add('header end customer account = ' || l_account_id,1,'N');
2870 END IF;
2871
2872
2873 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2874 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2875 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
2876 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2877 FND_MESSAGE.Set_Token('ID', to_char( l_end_cust_party), FALSE);
2878 FND_MSG_PUB.ADD;
2879 end if;
2880 END IF;
2881 raise FND_API.G_EXC_ERROR;
2882
2883 ELSE
2884
2885 UPDATE ASO_QUOTE_HEADERS_ALL
2886 SET end_customer_cust_account_id = l_account_id
2887 ,last_update_date = l_last_update_date
2888 ,last_updated_by = l_g_user_id
2889 ,last_update_login = l_g_login_id
2890 WHERE quote_header_id = p_qte_header_id;
2891
2892 l_qte_header_rec.end_customer_cust_account_id := l_account_id;
2893
2894
2895 END IF;
2896
2897 END IF;
2898
2899 END IF;
2900 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2901 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2902 END IF;
2903
2904 IF l_header_shipment_tbl.count > 0 THEN
2905
2906 IF l_header_shipment_tbl(1).ship_to_cust_account_id IS NULL THEN
2907 l_header_check_tca_flag := 'Y';
2908 IF l_header_shipment_tbl(1).ship_to_cust_party_id IS NOT NULL THEN
2909
2910 ASO_CHECK_TCA_PVT.Customer_Account(
2911 p_api_version => 1.0,
2912 p_Party_Id => l_header_shipment_tbl(1).ship_to_cust_party_id,
2913 p_calling_api_flag => p_calling_api_flag,
2914 x_Cust_Acct_Id => l_account_id,
2915 x_return_status => x_return_status,
2916 x_msg_count => x_msg_count,
2917 x_msg_data => x_msg_data);
2918
2919 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2920 aso_debug_pub.add('Assign_Customer_Accounts 4: x_return_status: '||x_return_status, 1, 'N');
2921 aso_debug_pub.add('header ship to cust party = '||l_header_shipment_tbl(1).ship_to_cust_party_id,1,'N');
2922 aso_debug_pub.add('header ship to account = ' || l_account_id,1,'N');
2923 END IF;
2924
2925 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2926 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2927 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
2928 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2929 FND_MESSAGE.Set_Token('ID', to_char( l_header_shipment_tbl(1).ship_to_cust_party_id), FALSE);
2930 FND_MSG_PUB.ADD;
2931 END IF;
2932 end if;
2933 raise FND_API.G_EXC_ERROR;
2934
2935 ELSE
2936 UPDATE ASO_SHIPMENTS
2937 SET ship_to_cust_account_id = l_account_id
2938 ,last_update_date = l_last_update_date
2939 ,last_updated_by = l_g_user_id
2940 ,last_update_login = l_g_login_id
2941
2942 WHERE shipment_id = l_header_shipment_tbl(1).shipment_id;
2943
2947 END IF;
2944 l_header_shipment_tbl(1).ship_to_cust_account_id := l_account_id;
2945 END IF;
2946
2948
2949 END IF;
2950
2951 END IF; --l_header_shipment_tbl.count
2952
2953 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2954 aso_debug_pub.add('after header sold_to, l_header_check = ' || l_header_check_tca_flag,1,'N');
2955 END IF;
2956
2957
2958
2959 IF l_header_check_tca_flag = 'Y' then
2960 check_tca(
2961 p_api_version => 1.0,
2962 p_init_msg_list => FND_API.G_FALSE,
2963 P_Qte_Rec => l_qte_header_rec,
2964 p_Header_Shipment_Tbl => l_header_Shipment_Tbl,
2965 x_return_status => x_return_status,
2966 x_msg_count => x_msg_count,
2967 x_msg_data => x_msg_data);
2968 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2969 aso_debug_pub.add('after check tca '||x_return_status, 1, 'N');
2970 END IF;
2971 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2972 RAISE FND_API.G_EXC_ERROR;
2973 END IF;
2974 END IF;
2975
2976 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2977 aso_debug_pub.add('Assign_Customer_Accounts : checking for lines ', 1, 'N');
2978 end if;
2979
2980 FOR Line_Acct IN C_Get_Line_Acct(p_qte_header_id) LOOP
2981
2982 l_qte_line_rec := ASO_QUOTE_PUB.G_MISS_QTE_LINE_REC;
2983 l_line_shipment_tbl := ASO_QUOTE_PUB.G_MISS_Shipment_TBL;
2984
2985 l_qte_line_rec.quote_header_id := p_qte_header_id;
2986 l_qte_line_rec.invoice_to_cust_account_id := Line_Acct.invoice_to_cust_account_id;
2987 l_qte_line_rec.invoice_to_party_site_id := Line_Acct.invoice_to_party_site_id;
2988 l_qte_line_rec.invoice_to_cust_party_id := Line_Acct.invoice_to_cust_party_id;
2989 l_qte_line_rec.invoice_to_party_id := Line_Acct.invoice_to_party_id;
2990 l_qte_line_rec.quote_line_id := Line_Acct.quote_line_id;
2991
2992 l_qte_line_rec.End_Customer_cust_account_id := Line_Acct.End_Customer_cust_account_id;
2993 l_qte_line_rec.End_Customer_party_site_id := Line_Acct.End_Customer_party_site_id;
2994 l_qte_line_rec.End_Customer_cust_party_id := Line_Acct.End_Customer_cust_party_id;
2995 l_qte_line_rec.End_Customer_party_id := Line_Acct.End_Customer_party_id;
2996
2997 l_line_shipment_tbl(1).ship_to_cust_account_id := Line_Acct.ship_to_cust_account_id;
2998 l_line_shipment_tbl(1).ship_to_cust_party_id := Line_Acct.ship_to_cust_party_id;
2999 l_line_shipment_tbl(1).ship_to_party_id := Line_Acct.ship_to_party_id;
3000 l_line_shipment_tbl(1).ship_to_party_site_id := Line_Acct.ship_to_party_site_id;
3001 l_line_shipment_tbl(1).shipment_id := Line_Acct.shipment_id;
3002
3003 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3004 aso_debug_pub.add('l_qte_line_rec.quote_line_id = '|| l_qte_line_rec.quote_line_id,1,'N');
3005 aso_debug_pub.add('l_qte_line_rec.invoice_to_cust_account_id = '|| l_qte_line_rec.invoice_to_cust_account_id,1,'N');
3006 aso_debug_pub.add('l_qte_line_rec.invoice_to_party_site_id = '|| l_qte_line_rec.invoice_to_party_site_id,1,'N');
3007 aso_debug_pub.add('l_qte_line_rec.invoice_to_cust_party_id = ' || l_qte_line_rec.invoice_to_cust_party_id,1,'N');
3008 aso_debug_pub.add('l_qte_line_rec.invoice_to_party_id = ' || l_qte_line_rec.invoice_to_party_id,1,'N');
3009 aso_debug_pub.add('l_qte_line_rec.End_Customer_cust_account_id = '|| l_qte_line_rec.End_Customer_cust_account_id,1,'N');
3010 aso_debug_pub.add('l_qte_line_rec.End_Customer_party_site_id = '|| l_qte_line_rec.End_Customer_party_site_id,1,'N');
3011 aso_debug_pub.add('l_qte_line_rec.End_Customer_cust_party_id = ' || l_qte_line_rec.End_Customer_cust_party_id,1,'N');
3012 aso_debug_pub.add('l_qte_line_rec.End_Customer_party_id = ' || l_qte_line_rec.End_Customer_party_id,1,'N');
3013 aso_debug_pub.add('l_line_shipment_tbl(1).shipment_id = ' || l_line_shipment_tbl(1).shipment_id,1,'N');
3014 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');
3015 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');
3016 aso_debug_pub.add('l_line_shipment_tbl(1).ship_to_party_id = '|| l_line_shipment_tbl(1).ship_to_party_id,1,'N');
3017 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');
3018 end if;
3019
3020
3021 IF l_qte_line_rec.invoice_to_cust_account_id IS NULL THEN
3022 IF l_qte_line_rec.invoice_to_cust_party_id IS NOT NULL THEN
3023
3024 ASO_CHECK_TCA_PVT.Customer_Account(
3025 p_api_version => 1.0,
3026 p_Party_Id => l_qte_line_rec.invoice_to_cust_party_id,
3027 p_calling_api_flag => p_calling_api_flag,
3028 x_Cust_Acct_Id => l_account_id,
3029 x_return_status => x_return_status,
3030 x_msg_count => x_msg_count,
3031 x_msg_data => x_msg_data);
3032
3033 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3034 aso_debug_pub.add('Assign_Customer_Accounts 3: x_return_status: '||x_return_status, 1, 'N');
3035 aso_debug_pub.add('line invoice to customer party = ' ||
3036 l_qte_line_rec.invoice_to_cust_party_id,1,'N');
3037
3038 aso_debug_pub.add('line invoice to customer account = ' ||
3039 l_account_id,1,'N');
3040 END IF;
3041
3042 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3043 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3044 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
3045 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3046 FND_MESSAGE.Set_Token('ID', to_char( l_qte_line_rec.invoice_to_cust_party_id), FALSE);
3047 FND_MSG_PUB.ADD;
3048 end if;
3049 END IF;
3050 raise FND_API.G_EXC_ERROR;
3051
3052 ELSE
3053
3054 UPDATE ASO_QUOTE_LINES_ALL
3055 SET invoice_to_cust_account_id = l_account_id
3056 ,last_update_date = l_last_update_date
3060 WHERE quote_line_id = l_qte_line_rec.quote_line_id;
3057 ,last_updated_by = l_g_user_id
3058 ,last_update_login = l_g_login_id
3059
3061
3062 l_qte_line_rec.invoice_to_cust_account_id := l_account_id;
3063
3064 END IF;
3065
3066 END IF;
3067
3068 END IF;
3069
3070 IF l_qte_line_rec.End_Customer_cust_account_id IS NULL THEN
3071 IF l_qte_line_rec.End_Customer_cust_party_id IS NOT NULL THEN
3072
3073 ASO_CHECK_TCA_PVT.Customer_Account(
3074 p_api_version => 1.0,
3075 p_Party_Id => l_qte_line_rec.End_Customer_cust_party_id,
3076 p_calling_api_flag => p_calling_api_flag,
3077 x_Cust_Acct_Id => l_account_id,
3078 x_return_status => x_return_status,
3079 x_msg_count => x_msg_count,
3080 x_msg_data => x_msg_data);
3081
3082 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3083 aso_debug_pub.add('Assign_Customer_Accounts 3: x_return_status: '||x_return_status, 1, 'N');
3084 aso_debug_pub.add('line End_Customer customer party = ' ||
3085 l_qte_line_rec.End_Customer_cust_party_id,1,'N');
3086
3087 aso_debug_pub.add('line End_Customer customer account = ' ||
3088 l_account_id,1,'N');
3089 END IF;
3090
3091 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3092 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3093 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
3094 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3095 FND_MESSAGE.Set_Token('ID', to_char( l_qte_line_rec.End_Customer_cust_party_id), FALSE);
3096 FND_MSG_PUB.ADD;
3097 end if;
3098 END IF;
3099 raise FND_API.G_EXC_ERROR;
3100
3101 ELSE
3102
3103 UPDATE ASO_QUOTE_LINES_ALL
3104 SET End_Customer_cust_account_id = l_account_id
3105 ,last_update_date = l_last_update_date
3106 ,last_updated_by = l_g_user_id
3107 ,last_update_login = l_g_login_id
3108 WHERE quote_line_id = l_qte_line_rec.quote_line_id;
3109
3110 l_qte_line_rec.End_Customer_cust_account_id := l_account_id;
3111
3112 END IF;
3113
3114 END IF;
3115
3116 END IF;
3117
3118 IF Line_Acct.ship_to_cust_account_id IS NULL THEN
3119
3120 IF Line_Acct.ship_to_cust_party_id IS NOT NULL THEN
3121
3122 ASO_CHECK_TCA_PVT.Customer_Account(
3123 p_api_version => 1.0,
3124 p_Party_Id => Line_Acct.ship_to_cust_party_id,
3125 p_calling_api_flag => p_calling_api_flag,
3126 x_Cust_Acct_Id => l_account_id,
3127 x_return_status => x_return_status,
3128 x_msg_count => x_msg_count,
3129 x_msg_data => x_msg_data);
3130
3131 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3132 aso_debug_pub.add('Assign_Customer_Accounts 4: x_return_status: '||x_return_status, 1, 'N');
3133 aso_debug_pub.add('line ship to customer party = ' ||
3134 Line_Acct.ship_to_cust_party_id,1,'N');
3135
3136 aso_debug_pub.add('line ship to customer account = ' ||
3137 l_account_id,1,'N');
3138 END IF;
3139
3140 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
3141 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3142 if NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED')<>'NEVER' then -- ER 5484749
3143 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
3144 FND_MESSAGE.Set_Token('ID', to_char( Line_Acct.ship_to_cust_party_id), FALSE);
3145 FND_MSG_PUB.ADD;
3146 end if;
3147 END IF;
3148 raise FND_API.G_EXC_ERROR;
3149
3150 ELSE
3151
3152 UPDATE ASO_SHIPMENTS
3153 SET ship_to_cust_account_id = l_account_id
3154 ,last_update_date = l_last_update_date
3155 ,last_updated_by = l_g_user_id
3156 ,last_update_login = l_g_login_id
3157
3158 WHERE shipment_id = Line_Acct.shipment_id;
3159
3160 l_line_shipment_tbl(1).ship_to_cust_account_id := l_account_id;
3161
3162 END IF;
3163
3164 END IF;
3165
3166 END IF;
3167
3168 Check_Line_Account_Info(
3169 p_api_version => 1.0,
3170 p_cust_account_id => l_qte_header_rec.cust_account_id,
3171 p_qte_line_rec => l_qte_line_rec,
3172 p_line_shipment_tbl => l_line_shipment_tbl,
3173 x_return_status => x_return_status,
3174 x_msg_count => x_msg_count,
3175 x_msg_data => x_msg_data);
3176
3177 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3178 aso_debug_pub.add('after check line account info '||x_return_status, 1, 'N
3179 ');
3180 END IF;
3181 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3182 RAISE FND_API.G_EXC_ERROR;
3183 END IF;
3184
3185
3186 END LOOP; -- line_Acct
3187
3188
3189
3190
3191
3192
3193 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3194 aso_debug_pub.add('Assign_Customer_Accounts: End ', 1, 'N');
3195 END IF;
3196
3197 FND_MSG_PUB.Count_And_Get
3198 ( p_count => x_msg_count,
3199 p_data => x_msg_data
3200 );
3201
3202 EXCEPTION
3203 WHEN FND_API.G_EXC_ERROR THEN
3204 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3205 P_API_NAME => L_API_NAME
3206 ,P_PKG_NAME => G_PKG_NAME
3207 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3211 ,X_MSG_COUNT => X_MSG_COUNT
3208 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
3209 ,P_SQLCODE => SQLCODE
3210 ,P_SQLERRM => SQLERRM
3212 ,X_MSG_DATA => X_MSG_DATA
3213 ,X_RETURN_STATUS => X_RETURN_STATUS);
3214
3215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3216 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3217 P_API_NAME => L_API_NAME
3218 ,P_PKG_NAME => G_PKG_NAME
3219 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3220 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
3221 ,P_SQLCODE => SQLCODE
3222 ,P_SQLERRM => SQLERRM
3223 ,X_MSG_COUNT => X_MSG_COUNT
3224 ,X_MSG_DATA => X_MSG_DATA
3225 ,X_RETURN_STATUS => X_RETURN_STATUS);
3226
3227 WHEN OTHERS THEN
3228 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3229 P_API_NAME => L_API_NAME
3230 ,P_PKG_NAME => G_PKG_NAME
3231 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
3232 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
3233 ,P_SQLCODE => SQLCODE
3234 ,P_SQLERRM => SQLERRM
3235 ,X_MSG_COUNT => X_MSG_COUNT
3236 ,X_MSG_DATA => X_MSG_DATA
3237 ,X_RETURN_STATUS => X_RETURN_STATUS);
3238
3239 END Assign_Customer_Accounts;
3240
3241
3242
3243 PROCEDURE Populate_Acct_Party (
3244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3245 p_hdr_cust_acct_id IN NUMBER,
3246 p_hdr_party_id IN NUMBER,
3247 p_party_site_id IN NUMBER,
3248 p_cust_account_id IN OUT NOCOPY NUMBER,
3249 p_cust_party_id IN OUT NOCOPY NUMBER,
3250 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3251 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3252 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3253 )
3254
3255 IS
3256
3257 CURSOR C_Get_Party_From_Acct(acct_id NUMBER) IS
3258 SELECT party_id
3259 FROM HZ_CUST_ACCOUNTS
3260 WHERE cust_account_id = acct_id
3261 AND status = 'A'
3262 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
3263
3264 l_inv_cust_id NUMBER := NULL;
3265
3266 BEGIN
3267
3268 IF p_cust_account_id is not NULL AND
3269 p_cust_account_id <> FND_API.G_MISS_NUM THEN
3270 IF p_cust_party_id IS NULL OR
3271 p_cust_party_id = FND_API.G_MISS_NUM THEN
3272
3273 OPEN C_Get_Party_From_Acct(p_cust_account_id);
3274 FETCH C_Get_Party_From_Acct INTO p_cust_party_id;
3275 CLOSE C_Get_Party_From_Acct;
3276
3277 ELSE
3278
3279 OPEN C_Get_Party_From_Acct(p_cust_account_id);
3280 FETCH C_Get_Party_From_Acct INTO l_inv_cust_id;
3281 CLOSE C_Get_Party_From_Acct;
3282
3283 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3284 aso_debug_pub.add('Check_Hdr_Acct: l_inv_cust_id: '|| l_inv_cust_id, 1, 'N');
3285 END IF;
3286 IF l_inv_cust_id <> p_cust_party_id THEN
3287 x_return_status := FND_API.G_RET_STS_ERROR;
3288 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3289 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
3290 FND_MESSAGE.Set_Token('COLUMN', 'CUST_PARTY_ID',FALSE);
3291 FND_MESSAGE.Set_Token('VALUE', to_char(p_cust_party_id),FALSE);
3292 FND_MSG_PUB.ADD;
3293 END IF;
3294 raise FND_API.G_EXC_ERROR;
3295 END IF;
3296
3297 END IF;
3298
3299 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3300 aso_debug_pub.add('Check_Hdr_Acct: p_cust_party_id:1: '|| p_cust_party_id, 1, 'N');
3301 END IF;
3302
3303 ELSE -- inv_to_cust_acct_id is null
3304
3305 IF p_cust_party_id IS NULL OR
3306 p_cust_party_id = FND_API.G_MISS_NUM THEN
3307 IF p_party_site_id IS NOT NULL AND
3308 p_party_site_id <> FND_API.G_MISS_NUM THEN
3309
3310 p_cust_account_id := p_hdr_cust_acct_id;
3311 p_cust_party_id := p_hdr_party_id;
3312
3313 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3314 aso_debug_pub.add('Check_Hdr_Acct: p_cust_account_id: '|| p_cust_account_id, 1, 'N');
3315 aso_debug_pub.add('Check_Hdr_Acct: p_cust_party_id:2: '|| p_cust_party_id, 1, 'N');
3316 END IF;
3317
3318 END IF;
3319 END IF;
3320
3321 END IF; -- inv_to_cust_acct_id
3322
3323
3324 END Populate_Acct_Party;
3325
3326
3327 PROCEDURE Check_Customer_Accounts(
3328 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3329 p_qte_header_id IN NUMBER,
3330 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3331 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3332 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3333 )
3334 IS
3335
3336 CURSOR C_Validate_Quote (qte_hdr NUMBER) IS
3337 SELECT 'X'
3338 FROM aso_quote_headers_all
3339 WHERE quote_header_id = qte_hdr;
3340
3341 CURSOR C_Get_Hdr_CustAcct (qte_hdr NUMBER) IS
3342 SELECT cust_party_id
3343 FROM aso_quote_headers_all
3344 WHERE quote_header_id = qte_hdr
3345 AND (cust_account_id IS NULL
3346 AND cust_party_id IS NOT NULL);
3347
3348 CURSOR C_Get_Hdr_InvCustAcct (qte_hdr NUMBER) IS
3349 SELECT invoice_to_cust_party_id
3350 FROM aso_quote_headers_all
3351 WHERE quote_header_id = qte_hdr
3355 CURSOR C_Get_Hdr_EndCustAcct (qte_hdr NUMBER) IS
3352 AND (invoice_to_cust_account_id IS NULL
3353 AND invoice_to_cust_party_id IS NOT NULL);
3354
3356 SELECT End_Customer_cust_party_id
3357 FROM aso_quote_headers_all
3358 WHERE quote_header_id = qte_hdr
3359 AND (End_Customer_cust_account_id IS NULL
3360 AND End_Customer_cust_party_id IS NOT NULL);
3361
3362 CURSOR C_Get_Line_Acct (qte_hdr NUMBER) IS
3363 SELECT invoice_to_cust_party_id
3364 FROM aso_quote_lines_all
3365 WHERE quote_header_id = qte_hdr
3366 AND invoice_to_cust_account_id IS NULL
3367 AND invoice_to_cust_party_id IS NOT NULL;
3368
3369 CURSOR C_Get_Line_EndCustAcct (qte_hdr NUMBER) IS
3370 SELECT End_Customer_cust_party_id
3371 FROM aso_quote_lines_all
3372 WHERE quote_header_id = qte_hdr
3373 AND End_Customer_cust_account_id IS NULL
3374 AND End_Customer_cust_party_id IS NOT NULL;
3375
3376 CURSOR C_Get_Ship_Acct (qte_hdr NUMBER) IS
3377 SELECT ship_to_cust_party_id
3378 FROM aso_shipments
3379 WHERE quote_header_id = qte_hdr
3380 AND ship_to_cust_account_id IS NULL
3381 AND ship_to_cust_party_id IS NOT NULL;
3382
3383 CURSOR C_Chk_Party_Acct (pty_id NUMBER) IS
3384 SELECT cust_account_id
3385 FROM hz_cust_accounts
3386 WHERE party_id = pty_id
3387 AND status = 'A'
3388 AND sysdate BETWEEN NVL(account_activation_date, sysdate) AND NVL(account_termination_date, sysdate);
3389
3390 l_create_acct_prof VARCHAR2(30) := NVL(FND_PROFILE.Value('ASO_AUTO_ACCOUNT_CREATE'), 'AS_REQUIRED');
3391 l_dummy VARCHAR2(1) := NULL;
3392 l_party NUMBER := NULL;
3393 l_cust_acct NUMBER := NULL;
3394 l_api_version CONSTANT NUMBER := 1.0;
3395 l_api_name CONSTANT VARCHAR2(45) := 'Check_Customer_Accounts';
3396
3397 BEGIN
3398
3399 -- Standard Start of API savepoint
3400 SAVEPOINT Check_Customer_Accounts_INT;
3401
3402 -- Initialize message list if p_init_msg_list is set to TRUE.
3403 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3404 FND_MSG_PUB.initialize;
3405 END IF;
3406
3407 -- Initialize API return status to success
3408 x_return_status := FND_API.G_RET_STS_SUCCESS;
3409
3410 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3411 aso_debug_pub.add('Check_Customer_Accounts - Begin', 1, 'Y');
3412 END IF;
3413
3414 OPEN C_Validate_Quote (p_qte_header_id);
3415 FETCH C_Validate_Quote into l_dummy;
3416 IF C_Validate_Quote%NOTFOUND THEN
3417 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3418 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
3419 FND_MESSAGE.Set_Token('COLUMN', 'ORIGINAL_QUOTE_ID', FALSE);
3420 FND_MESSAGE.Set_Token('VALUE', TO_CHAR(p_qte_header_id), FALSE);
3421 FND_MSG_PUB.ADD;
3422 END IF;
3423
3424 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3425 aso_debug_pub.add('Check_Customer_Accounts - Invalid qte_hdr_id', 1, 'Y');
3426 END IF;
3427
3428 CLOSE C_Validate_Quote;
3429 x_return_status := FND_API.G_RET_STS_ERROR;
3430 RAISE FND_API.G_EXC_ERROR;
3431 END IF;
3432 CLOSE C_Validate_Quote;
3433
3434 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3435 aso_debug_pub.add('Check_Customer_Accounts - After qte_hdr_id check', 1, 'Y');
3436 aso_debug_pub.add('Check_Customer_Accounts - l_create_acct_prof: '||l_create_acct_prof, 1, 'N');
3437 END IF;
3438
3439 l_party := NULL;
3440 l_cust_acct := NULL;
3441
3442 IF (l_create_acct_prof = 'PLACE_ORDER' or l_create_acct_prof = 'NEVER') THEN -- ER 5484749
3443
3444 OPEN C_Get_Hdr_CustAcct(p_qte_header_id);
3445 FETCH C_Get_Hdr_CustAcct INTO l_party;
3446 CLOSE C_Get_Hdr_CustAcct;
3447
3448 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3449 aso_debug_pub.add('Check_Customer_Accounts:hdr_CustAcct - l_party: '||l_party, 1, 'N');
3450 END IF;
3451
3452 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3453
3454 OPEN C_Chk_Party_Acct (l_party);
3455 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3456 CLOSE C_Chk_Party_Acct;
3457
3458 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3459 aso_debug_pub.add('Check_Customer_Accounts:hdr_CustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3460 END IF;
3461
3462 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3463 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3464 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3465 FND_MSG_PUB.ADD;
3466 END IF;
3467 x_return_status := FND_API.G_RET_STS_ERROR;
3468 RAISE FND_API.G_EXC_ERROR;
3469 END IF;
3470
3471 END IF;
3472
3473 l_party := NULL;
3474 l_cust_acct := NULL;
3475
3476 OPEN C_Get_Hdr_InvCustAcct(p_qte_header_id);
3477 FETCH C_Get_Hdr_InvCustAcct INTO l_party;
3478 CLOSE C_Get_Hdr_InvCustAcct;
3479
3480 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3481 aso_debug_pub.add('Check_Customer_Accounts:hdr_InvCustAcct - l_party: '||l_party, 1, 'N');
3482 END IF;
3483
3484 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3485
3486 OPEN C_Chk_Party_Acct (l_party);
3487 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3488 CLOSE C_Chk_Party_Acct;
3489
3490 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3491 aso_debug_pub.add('Check_Customer_Accounts:hdr_InvCustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3492 END IF;
3493
3494 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3495 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3496 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3497 FND_MSG_PUB.ADD;
3498 END IF;
3499 x_return_status := FND_API.G_RET_STS_ERROR;
3500 RAISE FND_API.G_EXC_ERROR;
3504
3501 END IF;
3502
3503 END IF;
3505 l_party := NULL;
3506 l_cust_acct := NULL;
3507
3508 OPEN C_Get_Hdr_EndCustAcct(p_qte_header_id);
3509 FETCH C_Get_Hdr_EndCustAcct INTO l_party;
3510 CLOSE C_Get_Hdr_EndCustAcct;
3511
3512 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3513 aso_debug_pub.add('Check_Customer_Accounts:hdr_EndCustAcct - l_party: '||l_party, 1, 'N');
3514 END IF;
3515
3516 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3517
3518 OPEN C_Chk_Party_Acct (l_party);
3519 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3520 CLOSE C_Chk_Party_Acct;
3521
3522 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3523 aso_debug_pub.add('Check_Customer_Accounts:hdr_EndCustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3524 END IF;
3525
3526 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3527 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3528 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3529 FND_MSG_PUB.ADD;
3530 END IF;
3531 x_return_status := FND_API.G_RET_STS_ERROR;
3532 RAISE FND_API.G_EXC_ERROR;
3533 END IF;
3534
3535 END IF;
3536
3537
3538 FOR Line_Acct IN C_Get_Line_Acct(p_qte_header_id) LOOP
3539
3540 l_party := NULL;
3541 l_cust_acct := NULL;
3542 l_party := Line_Acct.invoice_to_cust_party_id;
3543
3544 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3545 aso_debug_pub.add('Check_Customer_Accounts:line_Acct - l_party: '||l_party, 1, 'N');
3546 END IF;
3547
3548 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3549
3550 OPEN C_Chk_Party_Acct (l_party);
3551 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3552 CLOSE C_Chk_Party_Acct;
3553
3554 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3555 aso_debug_pub.add('Check_Customer_Accounts:lin_Acct - l_cust_acct: '||l_cust_acct, 1, 'N');
3556 END IF;
3557
3558 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3559 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3560 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3561 FND_MSG_PUB.ADD;
3562 END IF;
3563 x_return_status := FND_API.G_RET_STS_ERROR;
3564 RAISE FND_API.G_EXC_ERROR;
3565 END IF;
3566
3567 END IF;
3568
3569 END LOOP;
3570
3571
3572 FOR Line_EndCustAcct IN C_Get_Line_EndCustAcct(p_qte_header_id) LOOP
3573
3574 l_party := NULL;
3575 l_cust_acct := NULL;
3576 l_party := Line_EndCustAcct.End_Customer_cust_party_id;
3577
3578 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3579 aso_debug_pub.add('Check_Customer_Accounts:line_EndCustAcct - l_party: '||l_party, 1, 'N');
3580 END IF;
3581
3582 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3583
3584 OPEN C_Chk_Party_Acct (l_party);
3585 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3586 CLOSE C_Chk_Party_Acct;
3587
3588 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3589 aso_debug_pub.add('Check_Customer_Accounts:lin_EndCustAcct - l_cust_acct: '||l_cust_acct, 1, 'N');
3590 END IF;
3591
3592 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3593 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3594 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3595 FND_MSG_PUB.ADD;
3596 END IF;
3597 x_return_status := FND_API.G_RET_STS_ERROR;
3598 RAISE FND_API.G_EXC_ERROR;
3599 END IF;
3600
3601 END IF;
3602
3603 END LOOP;
3604
3605
3606 FOR Ship_Acct IN C_Get_Ship_Acct(p_qte_header_id) LOOP
3607
3608 l_party := NULL;
3609 l_cust_acct := NULL;
3610 l_party := Ship_Acct.ship_to_cust_party_id;
3611
3612 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3613 aso_debug_pub.add('Check_Customer_Accounts:shp_Acct - l_party: '||l_party, 1, 'N');
3614 END IF;
3615
3616 IF l_party IS NOT NULL AND l_party <> FND_API.G_MISS_NUM THEN
3617
3618 OPEN C_Chk_Party_Acct (l_party);
3619 FETCH C_Chk_Party_Acct INTO l_cust_acct;
3620 CLOSE C_Chk_Party_Acct;
3621
3622 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3623 aso_debug_pub.add('Check_Customer_Accounts:shp_Acct - l_cust_acct: '||l_cust_acct, 1, 'N');
3624 END IF;
3625
3626 IF l_cust_acct IS NULL OR l_cust_acct = FND_API.G_MISS_NUM THEN
3627 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
3628 FND_MESSAGE.Set_Name('ASO', 'ASO_CANNOT_CREATE_ACCOUNT');
3629 FND_MSG_PUB.ADD;
3630 END IF;
3631 x_return_status := FND_API.G_RET_STS_ERROR;
3632 RAISE FND_API.G_EXC_ERROR;
3633 END IF;
3634
3635 END IF;
3636
3637 END LOOP;
3638
3639 END IF; -- 'Place Order'
3640
3641 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
3642 aso_debug_pub.add('Check_Customer_Accounts: End ', 1, 'N');
3643 END IF;
3644
3645 FND_MSG_PUB.Count_And_Get
3646 ( p_count => x_msg_count,
3647 p_data => x_msg_data
3648 );
3649
3650 EXCEPTION
3651 WHEN FND_API.G_EXC_ERROR THEN
3652 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3653 P_API_NAME => L_API_NAME
3654 ,P_PKG_NAME => G_PKG_NAME
3655 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3656 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3657 ,P_SQLCODE => SQLCODE
3658 ,P_SQLERRM => SQLERRM
3659 ,X_MSG_COUNT => X_MSG_COUNT
3660 ,X_MSG_DATA => X_MSG_DATA
3664 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3661 ,X_RETURN_STATUS => X_RETURN_STATUS);
3662
3663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3665 P_API_NAME => L_API_NAME
3666 ,P_PKG_NAME => G_PKG_NAME
3667 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3668 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3669 ,P_SQLCODE => SQLCODE
3670 ,P_SQLERRM => SQLERRM
3671 ,X_MSG_COUNT => X_MSG_COUNT
3672 ,X_MSG_DATA => X_MSG_DATA
3673 ,X_RETURN_STATUS => X_RETURN_STATUS);
3674
3675 WHEN OTHERS THEN
3676 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
3677 P_API_NAME => L_API_NAME
3678 ,P_PKG_NAME => G_PKG_NAME
3679 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
3680 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
3681 ,P_SQLCODE => SQLCODE
3682 ,P_SQLERRM => SQLERRM
3683 ,X_MSG_COUNT => X_MSG_COUNT
3684 ,X_MSG_DATA => X_MSG_DATA
3685 ,X_RETURN_STATUS => X_RETURN_STATUS);
3686
3687 END Check_Customer_Accounts;
3688
3689
3690
3691 END ASO_CHECK_TCA_PVT;