[Home] [Help]
PACKAGE BODY: APPS.ASO_COPY_QUOTE_PUB
Source
1 PACKAGE BODY ASO_COPY_QUOTE_PUB as
2 /* $Header: asopcpyb.pls 120.1.12010000.5 2010/04/30 05:21:32 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_COPY_QUOTE_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_Copy_Quote_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asopcpyb.pls';
13
14
15 PROCEDURE Copy_Quote(
16 P_Api_Version_Number IN NUMBER,
17 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
18 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
19 P_Copy_Quote_Header_Rec IN ASO_COPY_QUOTE_PUB.Copy_Quote_Header_Rec_Type
20 := ASO_COPY_QUOTE_PUB.G_MISS_Copy_Quote_Header_Rec,
21 P_Copy_Quote_Control_Rec IN ASO_COPY_QUOTE_PUB.Copy_Quote_Control_Rec_Type
22 := ASO_COPY_QUOTE_PUB.G_MISS_Copy_Quote_Control_Rec,
23 /* Code change for Quoting Usability Sun ER Start */
24 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec,
25 P_Hd_Shipment_Rec IN ASO_QUOTE_PUB.Shipment_Rec_Type := ASO_QUOTE_PUB.G_MISS_Shipment_Rec,
26 P_hd_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type := ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL,
27 P_hd_Tax_Detail_Tbl IN ASO_QUOTE_PUB.Tax_Detail_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Tax_Detail_Tbl,
28 /* Code change for Quoting Usability Sun ER End */
29 X_Qte_Header_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
30 X_Qte_Number OUT NOCOPY /* file.sql.39 change */ NUMBER,
31 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
32 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
33 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
34 IS
35
36 l_api_version NUMBER := 1.0;
37 l_api_name VARCHAR2(50) := 'Copy_Quote';
38
39 BEGIN
40
41 -- Standard Start of API savepoint
42 SAVEPOINT Copy_Quote_PUB;
43
44 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
45
46 -- Standard call to check for call compatibility.
47 IF NOT FND_API.Compatible_API_Call ( l_api_version,
48 p_api_version_number,
49 l_api_name,
50 G_PKG_NAME)
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55
56 -- Initialize message list if p_init_msg_list is set to TRUE.
57 IF FND_API.to_Boolean( p_init_msg_list )
58 THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61
62 -- Initialize API return status to SUCCESS
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 --
66 -- API body
67 --
68
69 ASO_COPY_QUOTE_PVT.Copy_Quote(
70 P_Api_Version_Number => P_Api_Version_Number,
71 P_Init_Msg_List => P_Init_Msg_List,
72 P_Commit => P_Commit,
73 P_Copy_Quote_Header_Rec => P_Copy_Quote_Header_Rec,
74 P_Copy_Quote_Control_Rec => P_Copy_Quote_Control_Rec,
75 /* Code change for Quoting Usability Sun ER Start */
76 P_Qte_Header_Rec => P_Qte_Header_Rec,
77 P_Hd_Shipment_Rec => P_Hd_Shipment_Rec,
78 P_hd_Payment_Tbl => P_hd_Payment_Tbl,
79 P_hd_Tax_Detail_Tbl => P_hd_Tax_Detail_Tbl,
80 /* Code change for Quoting Usability Sun ER End */
81 X_Qte_Header_Id => X_Qte_Header_Id,
82 X_Qte_Number => X_Qte_Number,
83 X_Return_Status => X_Return_Status,
84 X_Msg_Count => X_Msg_Count,
85 X_Msg_Data => X_Msg_Data );
86
87 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
90 RAISE FND_API.G_EXC_ERROR;
91 END IF;
92
93 --
94 -- End of API body.
95 --
96
97 -- Standard check for p_commit
98 IF FND_API.to_Boolean( p_commit )
99 THEN
100 COMMIT WORK;
101 END IF;
102
103
104 -- Debug Message
105 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Public API: ' || l_api_name || 'end');
106
107
108 -- Standard call to get message count and if count is 1, get message info.
109 FND_MSG_PUB.Count_And_Get
110 ( p_count => x_msg_count,
111 p_data => x_msg_data
112 );
113
114 EXCEPTION
115 WHEN FND_API.G_EXC_ERROR THEN
116 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
117 P_API_NAME => L_API_NAME
118 ,P_PKG_NAME => G_PKG_NAME
119 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
120 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
121 ,X_MSG_COUNT => X_MSG_COUNT
122 ,X_MSG_DATA => X_MSG_DATA
123 ,X_RETURN_STATUS => X_RETURN_STATUS);
124
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
127 P_API_NAME => L_API_NAME
128 ,P_PKG_NAME => G_PKG_NAME
129 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
130 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
131 ,X_MSG_COUNT => X_MSG_COUNT
132 ,X_MSG_DATA => X_MSG_DATA
133 ,X_RETURN_STATUS => X_RETURN_STATUS);
134
135 WHEN OTHERS THEN
136 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
137 P_API_NAME => L_API_NAME
138 ,P_PKG_NAME => G_PKG_NAME
139 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
140 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
141 ,X_MSG_COUNT => X_MSG_COUNT
142 ,X_MSG_DATA => X_MSG_DATA
143 ,X_RETURN_STATUS => X_RETURN_STATUS);
144
145 END Copy_Quote;
146
147
148 PROCEDURE Copy_Line(
149 P_Api_Version_Number IN NUMBER,
150 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
151 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
152 P_Qte_Header_Id IN NUMBER,
153 P_Qte_Line_Id IN NUMBER := NULL,
154 P_Copy_Quote_Control_Rec IN ASO_COPY_QUOTE_PUB.Copy_Quote_Control_Rec_Type,
155 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
156 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type,
157 X_Qte_Line_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
158 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
159 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
160 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
161
162 IS
163
164
165 l_api_version NUMBER := 1.0;
166 l_api_name VARCHAR2(50) := 'Copy_Line';
167 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
168 l_Copy_Quote_Control_Rec ASO_COPY_QUOTE_PUB.Copy_Quote_Control_Rec_Type;
169
170 -- ER 3177722
171 lx_config_tbl ASO_QUOTE_PUB.Config_Vaild_Tbl_Type;
172 l_copy_config_profile varchar2(1):=nvl(fnd_profile.value('ASO_COPY_CONFIG_EFF_DATE'),'Y');
173 l_item_type_code varchar2(30);
174
175
176 BEGIN
177
178 -- Standard Start of API savepoint
179 SAVEPOINT Copy_Line_PUB;
180
181 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
182
183 -- Standard call to check for call compatibility.
184 IF NOT FND_API.Compatible_API_Call ( l_api_version,
185 p_api_version_number,
186 l_api_name,
187 G_PKG_NAME)
188 THEN
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191
192
193 -- Initialize message list if p_init_msg_list is set to TRUE.
194 IF FND_API.to_Boolean( p_init_msg_list )
195 THEN
196 FND_MSG_PUB.initialize;
197 END IF;
198
199 -- Initialize API return status to SUCCESS
200 x_return_status := FND_API.G_RET_STS_SUCCESS;
201
202 --
203 -- API body
204 --
205
206 IF ( P_Qte_Line_Id IS NULL OR P_Qte_Line_Id = FND_API.G_MISS_NUM ) THEN
207 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
208 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
209 FND_MESSAGE.Set_Token('COLUMN', 'P_Qte_Line_Id', FALSE);
210 FND_MSG_PUB.ADD;
211 END IF;
212 raise FND_API.G_EXC_ERROR;
213 End if;
214
215 IF aso_debug_pub.g_debug_flag = 'Y' THEN
216 aso_debug_pub.ADD ( ' Calling Copy_Line_Rows API ' , 1 , 'N' );
217 END IF;
218
219 l_qte_header_rec := P_Qte_Header_Rec;
220
221 l_qte_header_rec.batch_price_flag := FND_API.G_TRUE;
222
223 l_Copy_Quote_Control_Rec := P_Copy_Quote_Control_Rec;
224
225 -- change for sales supp enhancement as per bug 2940126
226 l_Copy_Quote_Control_Rec.New_Version := FND_API.G_TRUE;
227
228
229 ASO_COPY_QUOTE_PVT.Copy_Line_Rows (
230 P_Api_Version_Number => P_Api_Version_Number,
231 P_Init_Msg_List => P_Init_Msg_List,
232 P_Commit => P_Commit,
233 P_Qte_Header_Id => P_Qte_Header_Id,
234 P_New_Qte_Header_Id => P_Qte_Header_Id,
235 P_Qte_Line_Id => P_Qte_Line_Id,
236 P_Price_Index_Link_Tbl => ASO_QUOTE_HEADERS_PVT.G_MISS_LINK_TBL,
237 P_Copy_Quote_Control_Rec => l_Copy_Quote_Control_Rec,
238 P_Qte_Header_Rec => l_qte_header_rec,
239 P_Control_Rec => P_Control_Rec,
240 X_Qte_Line_Id => X_Qte_Line_Id,
241 X_Return_Status => X_Return_Status,
242 X_Msg_Count => X_Msg_Count,
243 X_Msg_Data => X_Msg_Data);
244
245 IF aso_debug_pub.g_debug_flag = 'Y' THEN
246 aso_debug_pub.ADD ( ' After calling Copy_Line_rows API ' , 1 , 'N' );
247 END IF;
248
249 -- ER 3177722
250
251 if (x_return_status =FND_API.G_RET_STS_SUCCESS) then
252
253 if l_copy_config_profile='N' then
254 IF aso_debug_pub.g_debug_flag = 'Y' THEN
255 aso_debug_pub.add('Copy_Line -before ASO_QUOTE_PUB.validate_model_configuration l_quote_header_rec: '||P_Qte_Header_Id, 1, 'N');
256 aso_debug_pub.add('Copy_Line -before ASO_QUOTE_PUB.validate_model_configuration X_Qte_Line_Id: '||X_Qte_Line_Id, 1, 'N');
257 end if;
258 select item_type_code into l_item_type_code
259 from aso_Quote_lines_all
260 where quote_line_id=X_Qte_Line_Id;
261 IF aso_debug_pub.g_debug_flag = 'Y' THEN
262 aso_debug_pub.add('Copy_Line -before ASO_QUOTE_PUB.validate_model_configuration l_item_type_code: '||l_item_type_code, 1, 'N');
263 end if;
264
265 if l_item_type_code='MDL' then
266
267 ASO_QUOTE_PUB.validate_model_configuration
268 (
269 P_Api_Version_Number => 1.0,
270 P_Init_Msg_List => FND_API.G_FALSE,
271 P_Commit => FND_API.G_FALSE,
272 P_Quote_header_id =>P_Qte_Header_Id,
273 p_Quote_line_id => X_Qte_Line_Id,
274 P_UPDATE_QUOTE =>'T',
275 P_CONFIG_EFFECTIVE_DATE => sysdate,
276 P_CONFIG_model_lookup_DATE => sysdate,
277 X_Config_tbl => lx_config_tbl,
278 X_Return_Status => x_return_status,
282
279 X_Msg_Count => x_msg_count,
280 X_Msg_Data => x_msg_data
281 );
283 /*if (x_Return_Status=FND_API.G_RET_STS_SUCCESS) and (lx_config_tbl.count>0) then
284 commit work;
285 end if;
286 */
287
288 IF aso_debug_pub.g_debug_flag = 'Y' THEN
289 aso_debug_pub.add('Copy_Line -After ASO_QUOTE_PUB.validate_model_configuration return status: '||x_Return_Status, 1, 'N');
290 aso_debug_pub.add('Copy_Line -After ASO_QUOTE_PUB.validate_model_configuration lx_config_tbl: '||lx_config_tbl.count, 1, 'N');
291 aso_debug_pub.add('Copy_Line -After ASO_QUOTE_PUB.validate_model_configuration x_msg_count: '||x_msg_count, 1, 'N');
292 END IF;
293 END IF; -- MDL
294 end if; -- profile
295 end if; -- in case success
296
297 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 --
304 -- End of API body.
305 --
306
307 -- Standard check for p_commit
308 IF FND_API.to_Boolean( p_commit )
309 THEN
310 COMMIT WORK;
311 END IF;
312
313
314 -- Debug Message
315 ASO_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Public API: ' || l_api_name || 'end');
316
317
318 -- Standard call to get message count and if count is 1, get message info.
319 FND_MSG_PUB.Count_And_Get
320 ( p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 EXCEPTION
325 WHEN FND_API.G_EXC_ERROR THEN
326 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
327 P_API_NAME => L_API_NAME
328 ,P_PKG_NAME => G_PKG_NAME
329 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
330 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
331 ,X_MSG_COUNT => X_MSG_COUNT
332 ,X_MSG_DATA => X_MSG_DATA
333 ,X_RETURN_STATUS => X_RETURN_STATUS);
334
335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
336 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
337 P_API_NAME => L_API_NAME
338 ,P_PKG_NAME => G_PKG_NAME
339 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
340 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
341 ,X_MSG_COUNT => X_MSG_COUNT
342 ,X_MSG_DATA => X_MSG_DATA
343 ,X_RETURN_STATUS => X_RETURN_STATUS);
344
345 WHEN OTHERS THEN
346 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
347 P_API_NAME => L_API_NAME
348 ,P_PKG_NAME => G_PKG_NAME
349 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
350 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB
351 ,X_MSG_COUNT => X_MSG_COUNT
352 ,X_MSG_DATA => X_MSG_DATA
353 ,X_RETURN_STATUS => X_RETURN_STATUS);
354
355 END Copy_Line;
356
357 PROCEDURE Copy_Line(
358 P_Api_Version_Number IN NUMBER,
359 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
360 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
361 P_Qte_Header_Id IN NUMBER,
362 P_Qte_Line_Id IN NUMBER := NULL,
363 P_Copy_Quote_Control_Rec IN ASO_COPY_QUOTE_PUB.Copy_Quote_Control_Rec_Type,
364 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
365 P_Control_Rec IN ASO_QUOTE_PUB.Control_Rec_Type,
366 X_Qte_Line_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
367 X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
368 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
369 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
370 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
371
372 IS
373
374 l_header_id NUMBER;
375 Cursor c_get_header_id (l_line_id NUMBER) IS
376 Select quote_header_id
377 from aso_quote_lines_all
378 where quote_line_id = l_line_id;
379 BEGIN
380
381 ASO_COPY_QUOTE_PUB.Copy_Line(
382 P_Api_Version_Number => P_Api_Version_Number,
383 P_Init_Msg_List => P_Init_Msg_List,
384 P_Commit => P_Commit,
385 P_Qte_Header_Id => P_Qte_Header_Id,
386 P_Qte_Line_Id => P_Qte_Line_Id,
387 P_Copy_Quote_Control_Rec => P_Copy_Quote_Control_Rec,
388 P_Qte_Header_Rec => P_Qte_Header_Rec,
389 P_Control_Rec => P_Control_Rec,
390 X_Qte_Line_Id => X_Qte_Line_Id,
391 X_Return_Status => X_Return_Status,
392 X_Msg_Count => X_Msg_Count,
393 X_Msg_Data => X_Msg_Data );
394
395 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
396 OPEN c_get_header_id(X_Qte_Line_Id);
397 Fetch c_get_header_id INTO l_header_id;
398 Close c_get_header_id;
399
400 X_Qte_Header_Rec := ASO_UTILITY_PVT.Query_Header_Row(l_header_id);
401
402 END IF;
403
404 END Copy_Line;
405
406 End ASO_COPY_QUOTE_PUB;