[Home] [Help]
PACKAGE BODY: APPS.ASO_CONC_REQ_INT
Source
1 PACKAGE BODY ASO_CONC_REQ_INT as
2 /* $Header: asoiprqb.pls 120.5 2006/02/08 11:59:46 skulkarn ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2 (30):= 'ASO_CONC_REQ_INT';
5
6 Procedure Submit_price_tax_req(
7 P_Api_Version_Number IN NUMBER,
8 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
9 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
10 p_control_rec IN ASO_QUOTE_PUB.Control_Rec_Type :=ASO_QUOTE_PUB.G_Miss_Control_Rec,
11 x_request_id OUT NOCOPY /* file.sql.39 change */ Number,
12 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
13 x_msg_count OUT NOCOPY /* file.sql.39 change */ Number,
14 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
15 ) is
16
17 lx_request_id Number;
18 errbuf VARCHAR2(2000);
19 L_API_VERSION CONSTANT NUMBER := 1.0;
20 l_api_name CONSTANT VARCHAR2(30) := 'Submit_price_tax_req';
21 l_header_pricing_event VARCHAR2(10);
22 l_pricing_request_type VARCHAR2(10);
23 l_calculate_tax_flag VARCHAR2(1);
24 l_calc_freight_charge_flag VARCHAR2(1);
25 l_price_mode VARCHAR2(20);
26 l_auto_version_flag VARCHAR2(1);
27 l_copy_task_flag VARCHAR2(1);
28 l_copy_notes_flag VARCHAR2(1);
29 l_copy_att_flag VARCHAR2(1);
30 l_DEPENDENCY_FLAG VARCHAR2(1);
31 l_DEFAULTING_FLAG VARCHAR2(1);
32 l_DEFAULTING_FWK_FLAG VARCHAR2(1);
33 l_APPLICATION_TYPE_CODE VARCHAR2(30);
34 l_pricing_status_indicator VARCHAR2(11);
35 l_tax_status_indicator VARCHAR2(11);
36 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
37 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
38 x_status VARCHAR2(1);
39 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
40 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec;
41 l_hd_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
42 l_hd_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
43 l_hd_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
44 l_hd_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
45 l_hd_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
46 l_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
47 l_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
48 l_hd_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
49 l_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
50 l_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
51 l_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
52 l_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
53 l_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
54 l_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
55 l_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
56 l_Ln_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
57 l_Ln_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
58 l_Ln_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
59 l_Ln_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
60 l_Ln_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
61 l_ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
62 l_ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
63
64 lx_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_line_tbl_type;
65 lx_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
66 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
67 lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
68 lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
69 lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
70 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
71 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
72 lX_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
73 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
74 lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
75 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
76 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
77 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
78 lx_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
79 lx_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
80 lX_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
81 lX_Ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
82 lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
83 lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
84 lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
85 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
86 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
87 l_org_id NUMBER ;
88
89 Begin
90
91 savepoint Submit_price_tax_req_INT;
92
93 -- Standard call to check for call compatibility.
94 IF NOT FND_API.Compatible_API_Call(
95 L_API_VERSION ,
96 P_API_VERSION_NUMBER,
97 L_API_NAME ,
98 G_PKG_NAME
99 ) THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 -- Initialize message list if p_init_msg_list is set to TRUE.
104 IF FND_API.To_Boolean(p_init_msg_list) THEN
105 FND_Msg_Pub.initialize;
106 END IF;
107
108
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 x_msg_count := 0;
112
113 /*******************************************************
114 Submit Batch Request
115 *******************************************************/
116
117 Lock_Exists(
118 p_quote_header_id => p_qte_header_rec.quote_header_id,
119 x_status => x_status);
120
121 if (x_status = FND_API.G_TRUE) then
122 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
123 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
124 FND_MSG_PUB.ADD;
125 END IF;
126
127 raise FND_API.G_EXC_ERROR;
128 end if;
129
130 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
131 aso_debug_pub.add('ASO_CONC_REQ: Submit_request:quote_header_id:'|| p_qte_header_rec.quote_header_id,1,'N');
132 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: header_pricing_event:'|| p_control_rec.header_pricing_event,1,'N');
133 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: pricing_request_type:'|| p_control_rec.pricing_request_type,1,'N');
134 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: calculate_tax_flag:'|| p_control_rec.calculate_tax_flag,1,'N');
135 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: calc_freight_charge_flag:'|| p_control_rec.calculate_freight_charge_flag,1,'N');
136 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: price_mode:'|| p_control_rec.price_mode,1,'N');
137 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: auto_version_flag:'|| p_control_rec.auto_version_flag,1,'N');
138 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: copy_task_flag:'|| p_control_rec.copy_task_flag ,1,'N');
139 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: copy_notes_flag:'|| p_control_rec.copy_notes_flag,1,'N');
140 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: copy_att_flag:'|| p_control_rec.copy_att_flag,1,'N');
141 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: DEPENDENCY_FLAG:'|| p_control_rec.DEPENDENCY_FLAG,1,'N');
142 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: DEFAULTING_FLAG:'|| p_control_rec.DEFAULTING_FLAG,1,'N');
143 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: DEFAULTING_FWK_FLAG:'|| p_control_rec.DEFAULTING_FWK_FLAG,1,'N');
144 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: APPLICATION_TYPE_CODE:'|| p_control_rec.APPLICATION_TYPE_CODE,1,'N');
145 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: pricing status indicator:'|| p_qte_header_rec.Pricing_status_indicator,1,'N');
146 aso_debug_pub.add('ASO_CONC_REQ: Submit_request: tax status indicator:'|| p_qte_header_rec.tax_status_indicator,1,'N');
147 END IF;
148
149 If ((p_control_rec.header_pricing_event = null) or (p_control_rec.header_pricing_event = FND_API.G_MISS_CHAR)) then
150 l_header_pricing_event := ' ';
151 else
152 l_header_pricing_event := p_control_rec.header_pricing_event;
153 end if;
154
155 If ((p_control_rec.pricing_request_type = null) or (p_control_rec.pricing_request_type = FND_API.G_MISS_CHAR)) then
156 l_pricing_request_type := ' ';
157 else
158 l_pricing_request_type := p_control_rec.pricing_request_type;
159 end if;
160
161 If ((p_control_rec.CALCULATE_TAX_FLAG = null) or (p_control_rec.CALCULATE_TAX_FLAG = FND_API.G_MISS_CHAR)) then
162 l_CALCULATE_TAX_FLAG := ' ';
163 else
164 l_CALCULATE_TAX_FLAG := p_control_rec.CALCULATE_TAX_FLAG;
165 end if;
166
167 If ((p_control_rec.calculate_freight_charge_flag = null) or (p_control_rec.calculate_freight_charge_flag = FND_API.G_MISS_CHAR)) then
168 l_calc_freight_charge_flag := ' ';
169 else
170 l_calc_freight_charge_flag := p_control_rec.calculate_freight_charge_flag;
171 end if;
172
173 If ((p_control_rec.auto_version_flag = null) or (p_control_rec.auto_version_flag = FND_API.G_MISS_CHAR)) then
174 l_auto_version_flag := ' ';
175 else
176 l_auto_version_flag := p_control_rec.auto_version_flag;
177 end if;
178
179 If ((p_control_rec.copy_task_flag = null) or (p_control_rec.copy_task_flag = FND_API.G_MISS_CHAR)) then
180 l_copy_task_flag := ' ';
181 else
182 l_copy_task_flag := p_control_rec.copy_task_flag;
183 end if;
184
185 If ((p_control_rec.copy_notes_flag = null) or (p_control_rec.copy_notes_flag = FND_API.G_MISS_CHAR)) then
186 l_copy_notes_flag := ' ';
187 else
188 l_copy_notes_flag := p_control_rec.copy_notes_flag ;
189 end if;
190
191 If ((p_control_rec.copy_att_flag = null) or (p_control_rec.copy_att_flag = FND_API.G_MISS_CHAR)) then
192 l_copy_att_flag := ' ';
193 else
194 l_copy_att_flag := p_control_rec.copy_att_flag;
195 end if;
196
197 --Defaulting Framework changes
198 If ((p_control_rec.DEPENDENCY_FLAG is NULL ) or (p_control_rec.DEPENDENCY_FLAG = FND_API.G_MISS_CHAR)) then
199 l_DEPENDENCY_FLAG := ' ';
200 else
201 l_DEPENDENCY_FLAG := p_control_rec.DEPENDENCY_FLAG;
202 End If;
203
204 If ((p_control_rec.DEFAULTING_FLAG IS NULL) or (p_control_rec.DEFAULTING_FLAG = FND_API.G_MISS_CHAR)) then
205 l_DEFAULTING_FLAG := ' ';
206 else
207 l_DEFAULTING_FLAG := p_control_rec.DEFAULTING_FLAG;
208 End If;
209
210 If ((p_control_rec.DEFAULTING_FWK_FLAG IS NULL) or (p_control_rec.DEFAULTING_FWK_FLAG = FND_API.G_MISS_CHAR)) then
211 l_DEFAULTING_FWK_FLAG := ' ';
212 else
213 l_DEFAULTING_FWK_FLAG := p_control_rec.DEFAULTING_FWK_FLAG;
214 End If;
215
216 If ((p_control_rec.APPLICATION_TYPE_CODE IS NULL) or (p_control_rec.APPLICATION_TYPE_CODE = FND_API.G_MISS_CHAR)) then
217 l_APPLICATION_TYPE_CODE := ' ';
218 else
219 l_APPLICATION_TYPE_CODE := p_control_rec.APPLICATION_TYPE_CODE;
220 End If;
221
222
223
224 If (p_qte_header_rec.Pricing_status_indicator = null) then
225 l_Pricing_status_indicator := ' ';
226 elsif (p_qte_header_rec.Pricing_status_indicator = FND_API.G_MISS_CHAR) then
227 l_Pricing_status_indicator := 'G_MISS_CHAR';
228 else
229 l_Pricing_status_indicator := p_qte_header_rec.Pricing_status_indicator;
230 end if;
231
232 If (p_qte_header_rec.tax_status_indicator = null) then
233 l_tax_status_indicator := ' ';
234 elsif (p_qte_header_rec.tax_status_indicator = FND_API.G_MISS_CHAR) then
235 l_tax_status_indicator := 'G_MISS_CHAR';
236 else
237 l_tax_status_indicator := p_qte_header_rec.tax_status_indicator;
238 end if;
239
240
241 -- Change START
242 -- Release 12 MOAC Changes : Bug 4500739
243 -- Changes Done by : Girish
244 -- Comments : Setting the org id context before submitting the request
245
246 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID;
247 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
248 aso_debug_pub.add('ASO_CONC_REQ: Submit_request:Org ID:'|| l_org_id,1,'N');
249 END IF;
250 fnd_request.set_org_id(l_org_id);
251
252 -- Change END
253
254 lx_request_id := fnd_request.submit_request
255 ( Application => 'ASO',
256 Program => 'ASOBTPRC',
257 Description => null,
258 Start_time => null,
259 Sub_request => FALSE,
260 Argument1 => p_qte_header_rec.quote_header_id,
261 Argument2 => l_header_pricing_event,
262 Argument3 => l_pricing_request_type,
263 Argument4 => l_calculate_tax_flag,
264 Argument5 => l_calc_freight_charge_flag,
265 Argument6 => p_control_rec.price_mode,
266 Argument7 => l_auto_version_flag,
267 Argument8 => l_copy_task_flag,
268 Argument9 => l_copy_notes_flag,
269 Argument10 => l_copy_att_flag,
270 Argument11 => l_pricing_status_indicator,
271 Argument12 => l_tax_status_indicator,
272 Argument13 => l_DEPENDENCY_FLAG,
273 Argument14 => l_DEFAULTING_FLAG,
274 Argument15 => l_DEFAULTING_FWK_FLAG,
275 Argument16 => l_APPLICATION_TYPE_CODE);
276
277
278 if lx_request_id = 0 then
279 fnd_file.put_line(FND_FILE.LOG,'Error in submitting concurrent request');
280 errbuf := FND_MESSAGE.GET;
281 fnd_message.set_name('ASO', errbuf);
282 FND_MSG_PUB.Add;
283 raise FND_API.G_EXC_ERROR;
284 end if;
285
286 /*******************************************************
287 Update quote with Price_request_id
288 *******************************************************/
289
290 l_qte_header_rec := p_qte_header_rec;
291 l_qte_header_rec.price_request_id := lx_request_id;
292 l_qte_header_rec.pricing_status_indicator := FND_API.G_MISS_CHAR;
293 l_qte_header_rec.tax_status_indicator := FND_API.G_MISS_CHAR;
294
295 ASO_QUOTE_PUB.Update_Quote(
296 p_api_version_number => 1.0,
297 p_init_msg_list => FND_API.G_FALSE,
298 p_commit => FND_API.G_FALSE,
299 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
300 p_control_rec => l_control_rec,
301 p_qte_header_rec => l_qte_header_rec,
302 P_hd_Price_Attributes_Tbl => l_hd_Price_Attributes_Tbl,
303 P_hd_Payment_Tbl => l_hd_Payment_Tbl,
304 P_hd_Shipment_Tbl => l_hd_Shipment_Tbl,
305 P_hd_Freight_Charge_Tbl => l_hd_Freight_Charge_Tbl,
306 P_hd_Tax_Detail_Tbl => l_hd_Tax_Detail_Tbl,
307 P_hd_Attr_Ext_Tbl => l_hd_Attr_Ext_Tbl,
308 P_hd_Sales_Credit_Tbl => l_hd_Sales_Credit_Tbl,
309 P_hd_Quote_Party_Tbl => l_hd_Quote_Party_Tbl,
310 P_Qte_Line_Tbl => l_Qte_Line_Tbl,
311 P_Qte_Line_Dtl_Tbl => l_Qte_Line_Dtl_Tbl,
312 P_Line_Attr_Ext_Tbl => l_Line_Attr_Ext_Tbl,
313 P_line_rltship_tbl => l_line_rltship_tbl,
314 P_Price_Adjustment_Tbl => l_Price_Adjustment_Tbl,
315 P_Price_Adj_Attr_Tbl => l_Price_Adj_Attr_Tbl,
316 P_Price_Adj_Rltship_Tbl => l_Price_Adj_Rltship_Tbl,
317 P_Ln_Price_Attributes_Tbl => l_Ln_Price_Attributes_Tbl,
318 P_Ln_Payment_Tbl => l_Ln_Payment_Tbl,
319 P_Ln_Shipment_Tbl => l_Ln_Shipment_Tbl,
320 P_Ln_Freight_Charge_Tbl => l_Ln_Freight_Charge_Tbl,
321 P_Ln_Tax_Detail_Tbl => l_Ln_Tax_Detail_Tbl,
322 P_ln_Sales_Credit_Tbl => l_ln_Sales_Credit_Tbl,
323 P_ln_Quote_Party_Tbl => l_ln_Quote_Party_Tbl,
324 X_Qte_Header_Rec => lx_qte_header_rec,
325 X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
326 X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
327 X_hd_Price_Attributes_Tbl => lx_hd_Price_Attr_Tbl,
328 X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
329 X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
330 X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
331 X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
332 X_hd_Attr_Ext_Tbl => lX_hd_Attr_Ext_Tbl,
333 X_hd_Sales_Credit_Tbl => lx_hd_Sales_Credit_Tbl,
334 X_hd_Quote_Party_Tbl => lx_Quote_Party_Tbl,
335 X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
336 X_line_rltship_tbl => lx_line_rltship_tbl,
337 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
338 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
339 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
340 X_ln_Price_Attributes_Tbl => lx_ln_Price_Attr_Tbl,
341 X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
342 X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,
343 X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
344 X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
345 X_Ln_Sales_Credit_Tbl => lX_Ln_Sales_Credit_Tbl,
346 X_Ln_Quote_Party_Tbl => lX_Ln_Quote_Party_Tbl,
347 X_Return_Status => x_Return_Status,
348 X_Msg_Count => x_Msg_Count,
349 X_Msg_Data => x_Msg_Data);
350
351
352 IF x_return_status = FND_API.G_RET_STS_ERROR then
353 x_request_id := null;
354 raise FND_API.G_EXC_ERROR;
355 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
356 x_request_id := null;
357 raise FND_API.G_EXC_UNEXPECTED_ERROR;
358 END IF;
359
360 x_request_id := lx_request_id;
361 commit work;
362
363 Exception
364
365 WHEN FND_API.G_EXC_ERROR THEN
366 x_return_status := FND_API.G_RET_STS_ERROR;
367
368 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
369 P_API_NAME => L_API_NAME
370 ,P_PKG_NAME => G_PKG_NAME
371 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
372 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
373 ,X_MSG_COUNT => X_MSG_COUNT
374 ,X_MSG_DATA => X_MSG_DATA
375 ,X_RETURN_STATUS => X_RETURN_STATUS);
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379
380
381 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
382 P_API_NAME => L_API_NAME
383 ,P_PKG_NAME => G_PKG_NAME
384 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
385 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
386 ,X_MSG_COUNT => X_MSG_COUNT
387 ,X_MSG_DATA => X_MSG_DATA
388 ,X_RETURN_STATUS => X_RETURN_STATUS);
389
390 WHEN OTHERS THEN
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392
393 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
394 P_API_NAME => L_API_NAME
395 ,P_PKG_NAME => G_PKG_NAME
396 ,P_SQLERRM => sqlerrm
397 ,P_SQLCODE => sqlcode
398 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
399 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
400 ,X_MSG_COUNT => X_MSG_COUNT
401 ,X_MSG_DATA => X_MSG_DATA
402 ,X_RETURN_STATUS => X_RETURN_STATUS);
403
404 end Submit_price_tax_req;
405
406 /*******************************************************
407 Procedure to call pricing
408 ********************************************************/
409
410 procedure batch_pricing(
411 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
412 retcode OUT NOCOPY /* file.sql.39 change */ Number,
413 p_quote_header_id IN Number,
414 p_header_pricing_event IN VARCHAR2,
415 p_pricing_request_type IN VARCHAR2,
416 p_calculate_tax_flag IN VARCHAR2,
417 p_calc_freight_charge_flag IN VARCHAR2,
418 p_price_mode IN VARCHAR2,
419 p_auto_version_flag IN VARCHAR2,
420 p_copy_task_flag IN VARCHAR2,
421 p_copy_notes_flag IN VARCHAR2,
422 p_copy_att_flag IN VARCHAR2,
423 p_PRICING_STATUS_INDICATOR IN VARCHAR2,
424 p_TAX_STATUS_INDICATOR IN VARCHAR2,
425 p_DEPENDENCY_FLAG IN VARCHAR2,
426 p_DEFAULTING_FLAG IN VARCHAR2,
427 p_DEFAULTING_FWK_FLAG IN VARCHAR2,
428 p_APPLICATION_TYPE_CODE IN VARCHAR2
429
430 ) is
431
432 l_api_name CONSTANT VARCHAR2(30) := 'batch_pricing';
433 l_subject VARCHAR2(2000);
434 l_body VARCHAR2(2000);
435 l_quote_name VARCHAR2(240);
436 l_org_id VARCHAR2(240);
437 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type:=ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
438 x_return_status VARCHAR2(1);
439 l_last_update_date DATE;
440 x_msg_count Number;
441 l_request_id Number;
442 x_msg_index Number;
443 x_Msg_Data VARCHAR2(2000);
444 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_Miss_Control_Rec;
445
446 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
447 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
448 lx_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_line_tbl_type;
449 lx_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
450 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
451 lx_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
452 lx_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
453 lx_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
454 lx_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
455 lx_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
456 lX_hd_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
457 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
458 lx_line_rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
459 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
460 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
461 lx_price_adj_rltship_tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
462 lx_hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
463 lx_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
464 lX_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
465 lX_Ln_Quote_Party_Tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
466 lx_ln_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
467 lx_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
468 lx_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
469 lx_ln_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
470 lx_ln_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
471
472 Begin
473
474 SAVEPOINT Batch_Pricing_INT;
475 /*******************************************************
476 Call update quote with pricing parameters
477 ********************************************************/
478 FND_FILE.PUT_LINE(FND_FILE.LOG,'Quote Header Id for Batch Pricing='||p_quote_header_id);
479
480 Begin
481
482 begin
483 SELECT a.quote_name ,a.price_request_id, a.org_id into l_quote_name ,l_request_id, l_org_id
484 FROM ASO_QUOTE_HEADERS_ALL a
485 WHERE a.quote_header_id = p_quote_header_id;
486 end;
487
488 FND_FILE.PUT_LINE(FND_FILE.LOG,l_quote_name);
489
490 exception when no_data_found then
491 x_return_status := FND_API.G_RET_STS_ERROR;
492
493 end;
494
495 begin
496 SELECT a.last_update_date into l_last_update_date
497 FROM ASO_QUOTE_HEADERS_ALL a
498 WHERE a.quote_header_id = p_quote_header_id;
499 end;
500
501
502 l_qte_header_rec.batch_price_flag := FND_API.G_FALSE;
503 l_qte_header_rec.quote_header_id := p_quote_header_id;
504 l_qte_header_rec.last_update_date := l_last_update_date;
505
506 if p_pricing_status_indicator = 'G_MISS_CHAR' then
507 l_qte_header_rec.pricing_status_indicator := FND_API.G_MISS_CHAR;
508 else
509 l_qte_header_rec.pricing_status_indicator := p_pricing_status_indicator;
510 end if;
511
512 if p_tax_status_indicator = 'G_MISS_CHAR' then
513 l_qte_header_rec.tax_status_indicator := FND_API.G_MISS_CHAR;
514 else
515 l_qte_header_rec.tax_status_indicator := p_tax_status_indicator;
516 end if;
517
518 l_control_rec.header_pricing_event := p_header_pricing_event;
519 l_control_rec.pricing_request_type := p_pricing_request_type;
520 l_control_rec.CALCULATE_TAX_FLAG := p_calculate_tax_flag;
521 l_control_rec.calculate_freight_charge_flag := p_calc_freight_charge_flag;
522 l_control_rec.price_mode := p_price_mode ;
523 l_control_rec.auto_version_flag := p_auto_version_flag;
524 l_control_rec.copy_task_flag := p_copy_task_flag;
525 l_control_rec.copy_notes_flag := p_copy_notes_flag;
526 l_control_rec.copy_att_flag := p_copy_att_flag;
527 l_control_rec.dependency_flag := p_dependency_flag;
528 l_control_rec.defaulting_flag := p_defaulting_flag;
529 l_control_rec.defaulting_fwk_flag := p_defaulting_fwk_flag;
530 l_control_rec.application_type_code := p_application_type_code;
531
532
533 mo_global.set_policy_context('S', l_org_id);
534
535 FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Update quote');
536 FND_FILE.PUT_LINE(FND_FILE.LOG,'Org ID : '||l_org_id);
537
538 ASO_QUOTE_PUB.Update_Quote(
539 p_api_version_number => 1.0,
540 p_init_msg_list => FND_API.G_FALSE,
541 p_commit => FND_API.G_false,
542 p_control_rec => l_control_rec,
543 p_qte_header_rec => l_qte_header_rec,
544 X_Qte_Header_Rec => lx_qte_header_rec,
545 X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
546 X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
547 X_hd_Price_Attributes_Tbl => lx_hd_Price_Attr_Tbl,
548 X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
549 X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
550 X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
551 X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
552 X_hd_Attr_Ext_Tbl => lX_hd_Attr_Ext_Tbl,
553 X_hd_Sales_Credit_Tbl => lx_hd_Sales_Credit_Tbl,
554 X_hd_Quote_Party_Tbl => lx_Quote_Party_Tbl,
555 X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
556 X_line_rltship_tbl => lx_line_rltship_tbl,
557 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
558 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
559 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
560 X_ln_Price_Attributes_Tbl => lx_ln_Price_Attr_Tbl,
561 X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
562 X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,
563 X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
564 X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
565 X_Ln_Sales_Credit_Tbl => lX_Ln_Sales_Credit_Tbl,
569 X_Msg_Data => x_Msg_Data);
566 X_Ln_Quote_Party_Tbl => lX_Ln_Quote_Party_Tbl,
567 X_Return_Status => x_Return_Status,
568 X_Msg_Count => x_Msg_Count,
570
571 FND_FILE.PUT_LINE(FND_FILE.LOG,X_Return_Status);
572
573 x_msg_index := 1;
574
575 while x_msg_count > 0 loop
576 x_msg_data := fnd_msg_pub.get(x_msg_index,
577 fnd_api.g_false);
578 FND_FILE.PUT_LINE(FND_FILE.LOG,x_msg_data);
579 x_msg_index := x_msg_index + 1;
580 x_msg_count := x_msg_count - 1;
581 end loop;
582
583 /********************************************************
584 Send Notification on completion
585 *********************************************************/
586
587 If x_return_status = FND_API.G_RET_STS_ERROR then
588 fnd_message.set_name('ASO','ASO_PRICE_REQ_COM_SUB');
589 fnd_message.set_token('QUOTE_NAME',l_quote_name);
590 fnd_message.set_token('REQUEST_ID',l_request_id);
591
592 l_subject := fnd_message.get ;
593
594 FND_FILE.PUT_LINE(FND_FILE.LOG,l_subject);
595
596 fnd_message.set_name('ASO','ASO_PRICE_REQ_COM_ERR_BODY');
597 l_body := fnd_message.get ;
598
599 FND_FILE.PUT_LINE(FND_FILE.LOG,l_body);
600
601 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
602 fnd_message.set_name('ASO','ASO_PRICE_REQ_COM_SUB');
603 fnd_message.set_token('QUOTE_NAME',l_quote_name);
604 fnd_message.set_token('REQUEST_ID',l_request_id);
605
606 l_subject := fnd_message.get ;
607
608 FND_FILE.PUT_LINE(FND_FILE.LOG,l_subject);
609
610 fnd_message.set_name('ASO','ASO_PRICE_REQ_COM_ERR_BODY');
611 l_body := fnd_message.get ;
612
613 FND_FILE.PUT_LINE(FND_FILE.LOG,l_body);
614 else
615 fnd_message.set_name('ASO','ASO_PRICE_REQ_COM_SUB');
616 fnd_message.set_token('QUOTE_NAME',l_quote_name);
617 fnd_message.set_token('REQUEST_ID',l_request_id);
618 l_subject := fnd_message.get ;
619
620 FND_FILE.PUT_LINE(FND_FILE.LOG,l_subject);
621
622 fnd_message.set_name('ASO','ASO_PRICE_REQ_COM_BODY');
623 l_body := fnd_message.get ;
624
625 FND_FILE.PUT_LINE(FND_FILE.LOG,l_body);
626
627 end if;
628
629 IF x_return_status = FND_API.G_RET_STS_ERROR then
630 rollback;
631 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
632 rollback;
633 else
634 commit work;
635 END IF;
636
637 Send_notification(
638 p_quote_header_id => p_quote_header_id,
639 p_subject => l_subject,
640 p_body => l_body);
641
642 FND_FILE.PUT_LINE(FND_FILE.LOG,'After Notification');
643
644 EXCEPTION
645 WHEN FND_API.G_EXC_ERROR THEN
646 x_return_status := FND_API.G_RET_STS_ERROR;
647
648 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
649 P_API_NAME => L_API_NAME
650 ,P_PKG_NAME => G_PKG_NAME
651 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
652 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
653 ,X_MSG_COUNT => X_MSG_COUNT
654 ,X_MSG_DATA => X_MSG_DATA
655 ,X_RETURN_STATUS => X_RETURN_STATUS);
656
657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659
660
661 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
662 P_API_NAME => L_API_NAME
663 ,P_PKG_NAME => G_PKG_NAME
664 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
665 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
666 ,X_MSG_COUNT => X_MSG_COUNT
667 ,X_MSG_DATA => X_MSG_DATA
668 ,X_RETURN_STATUS => X_RETURN_STATUS);
669
670 WHEN OTHERS THEN
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672
673 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
674 P_API_NAME => L_API_NAME
675 ,P_PKG_NAME => G_PKG_NAME
676 ,P_SQLERRM => sqlerrm
677 ,P_SQLCODE => sqlcode
678 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
679 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
680 ,X_MSG_COUNT => X_MSG_COUNT
681 ,X_MSG_DATA => X_MSG_DATA
682 ,X_RETURN_STATUS => X_RETURN_STATUS);
683 end batch_pricing;
684
685
686 /*******************************************************
687 Procedure to cancel request
688 *******************************************************/
689
690
691 Procedure Cancel_price_tax_req(
692 P_Api_Version_Number IN NUMBER,
693 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
694 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
695 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
696 x_msg_count OUT NOCOPY /* file.sql.39 change */ Number,
697 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
698 ) is
699
700 Cursor find_quote_csr(p_quote_header_id IN Number) is
701
702 select a.quote_name,a.price_request_id from aso_quote_headers_all a
703 where a.quote_header_id = p_quote_header_id;
704
705 L_API_VERSION CONSTANT NUMBER := 1.0;
706 l_api_name CONSTANT VARCHAR2(30) := 'Cancel_price_tax_req';
707 l_subject VARCHAR2(2000);
708 l_body VARCHAR2(2000);
709 l_quote_name VARCHAR2(240);
710 l_quote_header_id Number;
711 l_result boolean := TRUE;
712 lx_msg_data VARCHAR2(2000);
713 errbuf VARCHAR2(2000);
714 l_request_id Number;
715
716 Begin
717
718 /*******************************************************
719 Initiate Cancel request
720 *******************************************************/
721 Savepoint Cancel_price_tax_req_INT;
722
723 -- Standard call to check for call compatibility.
724 IF NOT FND_API.Compatible_API_Call(
725 L_API_VERSION ,
726 P_API_VERSION_NUMBER,
727 L_API_NAME ,
728 G_PKG_NAME
729 ) THEN
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731 END IF;
732
733 -- Initialize message list if p_init_msg_list is set to TRUE.
734 IF FND_API.To_Boolean(p_init_msg_list) THEN
735 FND_Msg_Pub.initialize;
736 END IF;
737
738 x_return_status := FND_API.G_RET_STS_SUCCESS;
739
740 x_msg_count := 0;
741
742 Open find_quote_csr(p_qte_header_rec.quote_header_id);
743
744 fetch find_quote_csr into l_quote_name,l_request_id;
745
746 IF (find_quote_csr%NOTFOUND or l_request_id is null) THEN
747 CLOSE find_quote_csr;
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
750 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_NOT_FOUND');
751 FND_MSG_PUB.ADD;
752 END IF;
753
754 raise FND_API.G_EXC_ERROR;
755 END IF;
756
757 close find_quote_csr ;
758
759 l_quote_header_id := p_qte_header_rec.quote_header_id;
760
761 l_result := FND_CONCURRENT.cancel_request(request_id => l_request_id,
762 message => lx_msg_data);
763
764 if not (l_result) then
765 errbuf := FND_MESSAGE.GET;
766 fnd_message.set_name('ASO',errbuf);
767 FND_MSG_PUB.Add;
768 end if;
769
770
771 /*******************************************************
772 Send Notification on completion
773 *******************************************************/
774
775 fnd_message.set_name('ASO','ASO_PRICE_REQ_CANCELED_SUB');
776 fnd_message.set_token('QUOTE_NAME',l_quote_name);
777 fnd_message.set_token('REQUEST_ID',l_request_id);
778
779 l_subject := fnd_message.get ;
780
781 fnd_message.set_name('ASO','ASO_PRICE_REQ_CANCELED_BODY');
782
783 l_body := fnd_message.get ;
784
785 Send_notification(
786 p_quote_header_id => l_quote_header_id,
787 p_subject => l_subject,
788 p_body => l_body);
789
790 if not (l_result) then
791 raise FND_API.G_EXC_ERROR;
792 end if;
793
794 EXCEPTION
795 WHEN FND_API.G_EXC_ERROR THEN
796 x_return_status := FND_API.G_RET_STS_ERROR;
797
798 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
799 P_API_NAME => L_API_NAME
800 ,P_PKG_NAME => G_PKG_NAME
801 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
802 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
803 ,X_MSG_COUNT => X_MSG_COUNT
804 ,X_MSG_DATA => X_MSG_DATA
805 ,X_RETURN_STATUS => X_RETURN_STATUS);
806
807 WHEN OTHERS THEN
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809
810 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
811 P_API_NAME => L_API_NAME
812 ,P_PKG_NAME => G_PKG_NAME
813 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
814 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_INT
815 ,X_MSG_COUNT => X_MSG_COUNT
816 ,X_MSG_DATA => X_MSG_DATA
817 ,X_RETURN_STATUS => X_RETURN_STATUS);
818
819 end Cancel_price_tax_req;
820
821
822 /********************************************************
823 Procedure to get role for sending notification
824 ********************************************************/
825
826 Procedure Get_Workflow_Role(p_user_id IN Number,
827 x_wf_role OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
828 x_notification_preference OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
829
830 l_forward_displayname VARCHAR2(100);
831 l_person_id Number;
832 l_email_address VARCHAR2(100);
833 l_language VARCHAR2(100);
834 l_territory VARCHAR2(100);
835
836 Cursor find_person_id is
837 select employee_id from fnd_user
838 where user_id = p_user_id;
839
840 Begin
841 Open find_person_id;
842
843 fetch find_person_id into l_person_id;
844
845 IF (find_person_id%NOTFOUND or l_person_id is null) THEN
846 wf_directory.GetRoleName(
847 p_orig_system => 'FND_USR',
848 p_orig_system_id => p_user_id,
849 p_name => x_wf_role,
850 p_display_name => l_forward_displayname );
851 else
852 wf_directory.GetRoleName(
853 p_orig_system => 'PER',
854 p_orig_system_id => l_person_id,
855 p_name => x_wf_role,
856 p_display_name => l_forward_displayname );
857 END IF;
858 Close find_person_id ;
859
860 wf_directory.GetRoleInfo(
861 role => x_wf_role,
862 display_name => l_forward_displayname,
863 email_address => l_email_address,
864 notification_preference => x_notification_preference,
865 language => l_language,
866 territory => l_territory);
867
868
869 END Get_Workflow_Role;
870
871
872 /********************************************************
873 Update price_request_id in ASO_QUOTE_HEADERS_ALL
874 ********************************************************/
875
876 procedure Update_price_req_id(p_quote_header_id IN Number) is
877
878 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
879 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
880 l_last_update_date Date := SYSDATE;
881
882 Begin
883
884 Update ASO_QUOTE_HEADERS_ALL
885 set price_request_id = null,
886 last_update_date = l_last_update_date,
887 object_version_number = object_version_number + 1,
888 last_updated_by = G_USER_ID,
889 last_update_login = G_LOGIN_ID
890 where quote_header_id =p_quote_header_id;
891
892 end Update_price_req_id;
893
894 /********************************************************
895 Procedure to send notification
896 ********************************************************/
897
898 Procedure Send_notification(
899 p_quote_header_id IN Number,
900 p_subject IN VARCHAR2,
901 p_body IN VARCHAR2) is
902
903 wf_itemkey_seq INTEGER;
904 wf_itemkey VARCHAR2(30);
905 lx_wf_role VARCHAR2(30);
906 l_user_id Number := FND_GLOBAL.USER_ID;
907 lx_notification_preference VARCHAR2(30);
908 lx_document VARCHAR2(2000);
909 lx_document_type VARCHAR2(2000);
910 l_qte_number number;
911
912 cursor c_get_qte_number is
913 select quote_number
914 from aso_quote_headers_all
915 where quote_header_id = p_quote_header_id;
916
917
918 Begin
919
920 Get_Workflow_Role(
921 p_user_id => l_user_id,
922 x_wf_role => lx_wf_role,
923 x_notification_preference => lx_notification_preference);
924
925
926 select ASO_WFNOTIFICATION_S2.NEXTVAL into wf_itemkey_seq from dual;
927
928 wf_itemkey := 'ASO_NTFY' || wf_itemkey_seq;
929
930
931 wf_engine.CreateProcess( itemtype => 'ASO_NTFY',
932 itemkey => wf_itemkey,
933 process => 'ASO_SEND_NTFY' );
934
935 wf_engine.SetItemAttrText( itemtype => 'ASO_NTFY',
936 itemkey => wf_itemkey,
937 aname => 'ROLE_TO_NOTIFY',
938 avalue => lx_wf_role );
939
940 wf_engine.SetItemAttrText( itemtype => 'ASO_NTFY',
941 itemkey => wf_itemkey,
942 aname => 'NOTIFICATION_SUBJECT',
943 avalue => p_subject );
944
945 wf_engine.SetItemAttrText( itemtype => 'ASO_NTFY',
946 itemkey => wf_itemkey,
947 aname => 'NOTIFICATION_BODY',
948 avalue => p_body );
949
950
951 wf_engine.SetItemAttrText(
952 itemtype => 'ASO_NTFY',
953 itemkey => wf_itemkey,
954 aname => 'SEQID',
955 avalue => wf_itemkey
956 );
957
958
959 wf_engine.SetItemAttrNumber (
960 itemtype => 'ASO_NTFY',
961 itemkey => wf_itemkey,
962 aname => 'QTEHDRID',
963 avalue => p_quote_header_id
964 );
965
966
967 open c_get_qte_number;
968 fetch c_get_qte_number into l_qte_number;
969 close c_get_qte_number;
970
971 wf_engine.SetItemAttrNumber (
972 itemtype => 'ASO_NTFY',
973 itemkey => wf_itemkey,
974 aname => 'QTENUMBER',
975 avalue => l_qte_number
976 );
977
978
979 wf_engine.SetItemOwner( itemtype => 'ASO_NTFY',
980 itemkey => wf_itemkey,
981 owner => lx_wf_role );
982
983
984 wf_engine.StartProcess(itemtype => 'ASO_NTFY',
985 itemkey => wf_itemkey );
986
987
988 Update_price_req_id(p_quote_header_id => p_quote_header_id);
989
990 end send_notification;
991
992 /*************************************************************
993 Procedure to find pending /running request for a given quote
994 **************************************************************/
995
996 Procedure Lock_Exists(p_quote_header_id IN Number,
997 x_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
998
999 l_request_id Number;
1000
1001 Begin
1002
1003 Select price_request_id into l_request_id from aso_quote_headers_all
1004 Where quote_header_id =p_quote_header_id;
1005
1006 If l_request_id is null then
1007 x_status := FND_API.G_FALSE;
1008 Else
1009 x_status := FND_API.G_TRUE;
1010 End if;
1011
1012 end Lock_Exists;
1013
1014 /*************************************************************
1015 Procedure to create quote details URL
1016 **************************************************************/
1017
1018 PROCEDURE quote_detail_url (p_quote_header_id IN Number,
1019 p_display_type IN VARCHAR2,
1020 x_document OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
1021
1022 l_jsp_name VARCHAR2 (1000);
1023 l_url VARCHAR2 (100);
1024 l_attr_desc VARCHAR2 (100);
1025 l_quote_number Number;
1026 l_party_number VARCHAR2(30);
1027 l_party_type VARCHAR2 (30);
1028 p Number;
1029
1030 CURSOR get_quote_details (p_quote_header_id NUMBER ) IS
1031 SELECT qha.quote_number, hca.account_number,
1032 hp.party_type
1033 FROM aso_quote_headers_all qha,
1034 hz_parties hp,
1035 hz_cust_accounts hca
1036 WHERE qha.cust_account_id = hca.cust_account_id(+)
1037 AND hca.party_id = hp.party_id
1038 AND qha.party_id = hca.party_id
1039 AND qha.quote_header_id = p_quote_header_id;
1040
1041 BEGIN
1042
1043 -- get the quote header id
1044 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1045 aso_debug_pub.ADD ('Quote header ID is ' || p_quote_header_id,1,'N');
1046 END IF;
1047
1048 -- get the server address
1049 l_url := fnd_web_config.jsp_agent ();
1050 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1051 aso_debug_pub.ADD ('URL ID is ' || l_url,1,'N');
1052 END IF;
1053
1054 -- get the quote details
1055 OPEN get_quote_details (p_quote_header_id);
1056
1057 FETCH get_quote_details INTO l_quote_number,
1058 l_party_number,
1059 l_party_type;
1060 CLOSE get_quote_details;
1061
1062 -- get the jsp name
1063 l_jsp_name := 'qotSZzpAppsLink.jsp?';
1064 l_jsp_name := l_jsp_name || 'qotFrmMainFile=qotSZzdContainer.jsp';
1065 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotFrmDspFile=qotSCocOverview.jsp';
1066 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotFrmRefFile=qotSCocOverview.jsp';
1067 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotDetCode=QUOTE';
1068 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotPtyType='|| l_party_type;
1069 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotHdrId='|| p_quote_header_id;
1070 if l_party_number is not null then
1071 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotHdrAcctId='||l_party_number;
1072 end if;
1073 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotHdrNbr='|| l_quote_number;
1074 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotReqSetCookie=Y';
1075 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotFromApvlLink=Y';
1076
1077 -- get the attribute label
1078 Begin
1079 fnd_message.set_name('ASO','ASO_QUOTE_DETAILS_URL');
1080 l_attr_desc := fnd_message.get;
1081 end;
1082
1083 -- Create an html text buffer
1084 IF (p_display_type = 'MAILHTML')
1085 THEN
1086 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1087 aso_debug_pub.ADD ('Display type is text/html',1,'N');
1088 END IF;
1089 x_document := '<a href = "'|| l_url|| l_jsp_name|| '">'||l_attr_desc|| '</a>';
1090
1091 END IF;
1092
1093 -- Create a plain text buffer
1094
1095 IF (p_display_type = 'MAILTEXT')
1096 THEN
1097 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1098 aso_debug_pub.ADD ('Display type is text/plain',1,'N');
1099 END IF;
1100 NULL;
1101 END IF;
1102
1103 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1104 aso_debug_pub.ADD ('End QUOTE_DETAIL_URL procedure ',1,'N');
1105 END IF;
1106
1107 EXCEPTION
1108 WHEN OTHERS
1109 THEN
1110 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1111 aso_debug_pub.ADD ('Exception in QUOTE_DETAIL_URL SqlCode :' || SQLERRM,1,'N');
1112 END IF;
1113
1114 RAISE;
1115
1116 END quote_detail_url;
1117
1118 /*************************************************************
1119 Procedure to create quote details URL using Document
1120 **************************************************************/
1121
1122 PROCEDURE qte_detail_url (
1123 document_id IN VARCHAR2,
1124 display_type IN VARCHAR2,
1125 document IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1126 document_type IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1127 ) IS
1128 itemtype VARCHAR2 (30);
1129 itemkey VARCHAR2 (30);
1130 l_quote_header_id NUMBER;
1131 l_jsp_name VARCHAR2 (1000);
1132 l_url VARCHAR2 (100);
1133 l_attr_desc VARCHAR2 (100);
1134 l_quote_number Number;
1135 l_party_number VARCHAR2(30);
1136 l_party_type VARCHAR2 (30);
1137 p Number;
1138 wf_itmkey_seq INTEGER;
1139 wf_itmkey VARCHAR2(30);
1140 l_error VARCHAR2(2000);
1141 l_org_id NUMBER;
1142
1143 CURSOR get_quote_details (p_quote_header_id NUMBER ) IS
1144 SELECT qha.quote_number, hca.account_number,
1145 hp.party_type,qha.org_id
1146 FROM aso_quote_headers_all qha,
1147 hz_parties hp,
1148 hz_cust_accounts hca
1149 WHERE qha.cust_account_id = hca.cust_account_id(+)
1150 AND qha.cust_party_id = hp.party_id
1151 AND qha.quote_header_id =p_quote_header_id;
1152
1153 BEGIN
1154
1155 -- get the server address
1156 l_url := fnd_web_config.jsp_agent ();
1157 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1158 aso_debug_pub.ADD ('URL ID is ' || l_url,1,'N');
1159 END IF;
1160
1161 -- get the quote details
1162 itemtype := 'ASO_NTFY';
1163 itemkey := document_id;
1164
1165
1166 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1167 aso_debug_pub.ADD ('itemtype is ' || itemtype,1,'N');
1168 aso_debug_pub.ADD ('itemkey is ' || itemkey,1,'N');
1169 END IF;
1170
1171 l_quote_header_id := wf_engine.getitemattrnumber (
1172 itemtype => itemtype,
1173 itemkey => itemkey,
1174 aname => 'QUOTEHDRID'
1175 );
1176
1177
1178
1179 OPEN get_quote_details (to_number(l_quote_header_id));
1180
1181 FETCH get_quote_details INTO l_quote_number,
1182 l_party_number,
1183 l_party_type,
1184 l_org_id;
1185 CLOSE get_quote_details;
1186
1187 -- get the jsp name
1188 l_jsp_name := 'qotSZzpAppsLink.jsp?';
1189 l_jsp_name := l_jsp_name || 'qotFrmMainFile=qotSZzdContainer.jsp';
1190 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotFrmDspFile=qotSCocOverview.jsp';
1191 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotFrmRefFile=qotSCocOverview.jsp';
1192 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotDetCode=QUOTE';
1193 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotPtyType='|| l_party_type;
1194 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotHdrId='|| l_quote_header_id;
1195 if l_party_number is not null then
1196 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotHdrAcctId='||l_party_number;
1197 end if;
1198 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotHdrNbr='|| l_quote_number;
1199 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotReqSetCookie=Y';
1200 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotFromApvlLink=Y';
1201 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotApvOrgId='|| l_org_id;
1202 l_jsp_name := l_jsp_name|| fnd_global.local_chr (38)|| 'qotApvNotifId=&#NID';
1203
1204 -- get the attribute label
1205 Begin
1206 fnd_message.set_name('ASO','ASO_QUOTE_DETAILS_URL');
1207 l_attr_desc := fnd_message.get;
1208 end;
1209
1210 -- Create an html text buffer
1211 IF (display_type = 'text/html')
1212 THEN
1213 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1214 aso_debug_pub.ADD ('Display type is text/html',1,'N');
1215 END IF;
1216 document := '<a href = "'|| l_url|| l_jsp_name|| '">'||l_attr_desc|| '</a>';
1217 document_type := 'text/html';
1218
1219 END IF;
1220
1221 -- Create a plain text buffer
1222
1223 IF (display_type = 'text/plain')
1224 THEN
1225 document_type := 'text/plain';
1226 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1227 aso_debug_pub.ADD ('Display type is text/plain',1,'N');
1228 END IF;
1229 NULL;
1230 END IF;
1231
1232 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1233 aso_debug_pub.ADD ('End QUOTE_DETAIL_URL procedure ',1,'N');
1234 END IF;
1235
1236 EXCEPTION
1237 WHEN OTHERS
1238 THEN
1239 wf_core.context('ASO_CONC_REQ_INT', 'QTE_DETAIL_URL', document_id,
1240 document_type, sqlerrm);
1241 RAISE;
1242
1243 END qte_detail_url;
1244
1245
1246
1247 end ASO_CONC_REQ_INT;
1248