[Home] [Help]
PACKAGE BODY: APPS.IBU_ORDER_CAPTURE
Source
1 PACKAGE BODY IBU_ORDER_CAPTURE AS
2 /* $Header: ibuordrb.pls 115.43.1159.1 2003/05/23 22:21:46 appldev ship $ */
3 -- ---------------------------------------------------------
4 -- Define global variables and types
5 -- ---------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(100) := 'IBU_ORDER_CAPTURE';
7
8 PROCEDURE CREATE_RETURN (
9 p_api_version_number IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR := FND_API.G_FALSE,
12 p_header_id IN NUMBER,
13 HEADER_REC IN HEADER_REC_TYPE,
14 HEADER_SHIPMENT_REC IN HEADER_SHIPMENT_REC_TYPE,
15 LINE_TBL IN LINE_TBL_TYPE,
16 LINE_DTL_TBL IN LINE_DTL_TBL_TYPE,
17 LINE_SHIPMENT_TBL IN LINE_SHIPMENT_TBL_TYPE,
18 X_MSG_COUNT OUT NOCOPY NUMBER,
19 X_MSG_DATA OUT NOCOPY VARCHAR2,
20 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
21 X_RETURN_HEADER_REC OUT NOCOPY RETURN_HEADER_REC_TYPE,
22 X_RETURN_LINE_TBL OUT NOCOPY RETURN_LINE_TBL_TYPE
23 )
24
25 AS
26 l_api_name VARCHAR2 (30):= 'CREATE_RETURN';
27
28 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
29 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
30 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
31 l_header_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
32 l_line_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
33 l_return_header_rec ASO_ORDER_INT.Order_Header_Rec_Type;
34 l_return_line_tbl ASO_ORDER_INT.Order_Line_Tbl_type;
35 l_control_rec ASO_ORDER_INT.Control_Rec_Type;
36 l_header_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
37 l_header_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
38
39
40 l_header_cursor NUMBER;
41 l_header_cursor_stmt VARCHAR2(4000);
42
43 l_dummy_id NUMBER;
44
45 l_source_document_id NUMBER:= NULL;
46 l_org_id NUMBER:= FND_API.G_MISS_NUM;
47 l_source_document_type_id NUMBER:= NULL;
48 l_pricelist_id NUMBER:= FND_API.G_MISS_NUM;
49 l_transaction_curr_code VARCHAR2 (15):=FND_API.G_MISS_CHAR;
50 l_quote_source_code VARCHAR2(240):=NULL;
51
52 l_line_cursor NUMBER;
53 l_line_cursor_stmt VARCHAR2(4000);
54
55 l_line_org_id NUMBER := FND_API.G_MISS_NUM;
56 l_ship_from_org_id NUMBER := FND_API.G_MISS_NUM;
57 l_inventory_item_id NUMBER := FND_API.G_MISS_NUM;
58 l_ordered_quantity_uom VARCHAR2(3) :=FND_API.G_MISS_CHAR;
59 l_line_price_list_id NUMBER :=FND_API.G_MISS_NUM;
60 l_unit_list_price NUMBER :=FND_API.G_MISS_NUM;
61 l_unit_selling_price NUMBER :=FND_API.G_MISS_NUM;
62
63 l_ibu_ret_error_msg VARCHAR2(4000);
64 l_aso_debug_file VARCHAR2(1000);
65 l_ibu_ret_header_info VARCHAR2(8000);
66 l_ibu_ret_header_prpt_info VARCHAR2(8000);
67 l_ibu_ret_head_ship_info VARCHAR2(8000);
68 l_ibu_ret_head_ship_prpt_info VARCHAR2(8000);
69 l_ibu_ret_line_info VARCHAR2(8000);
70 l_ibu_ret_line_dtl_info VARCHAR2(8000);
71 l_ibu_ret_line_prpt_info VARCHAR2(8000);
72 l_ibu_ret_line_dtl_prpt_info VARCHAR2(8000);
73
74 l_booked_profile_value VARCHAR2(100);
75 l_salesrep_id NUMBER :=FND_API.G_MISS_NUM;
76 l_person_id NUMBER :=FND_API.G_MISS_NUM;
77
78 Cursor Get_order_salesrep_id (p_header_id NUMBER) IS
79 select salesrep_id
80 from OE_ORDER_HEADERS_ALL
81 where header_id = p_header_id;
82
83 Cursor Get_person_id (p_salesrep_id NUMBER) IS
84 select person_id
85 from ra_salesreps
86 where salesrep_id = p_salesrep_id;
87
88 --debug
89 l_x_msg_count NUMBER;
90
91 BEGIN
92
93 -- Turn on ASO debugging
94 aso_debug_pub.SetDebugLevel(10);
95 aso_debug_pub.Initialize;
96 l_aso_debug_file := ASO_DEBUG_PUB.Set_Debug_Mode('FILE');
97 aso_debug_pub.debug_on;
98 l_aso_debug_file := 'debug=' || l_aso_debug_file;
99
100 -- Initialize message list if p_init_msg_list is set to TRUE.
101 IF FND_API.to_Boolean( p_init_msg_list ) THEN
102 null;
103 FND_MSG_PUB.initialize;
104 FND_MSG_PUB.initialize;
105 END IF;
106 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
107
108 FND_MESSAGE.SET_NAME('IBU','IBU_MESSAGE');
109 FND_MESSAGE.SET_TOKEN ('TOKEN', l_aso_debug_file);
110 FND_MSG_PUB.Add;
111
112
113 l_header_cursor_stmt := 'select ord.source_document_id, ord.org_id, ord.source_document_type_id, ord.price_list_id, ord.transactional_curr_code, ord_src.name ';
114 l_header_cursor_stmt := l_header_cursor_stmt || ' from oe_order_headers_all ord, OE_ORDER_SOURCES ord_src where header_id = :v_a_header_id and ord.source_document_type_id= ord_src.order_source_id(+)' ;
115 l_header_cursor := dbms_sql.open_cursor;
116 dbms_sql.parse(l_header_cursor, l_header_cursor_stmt , dbms_sql.NATIVE);
117
118 dbms_sql.bind_variable( l_header_cursor, ':v_a_header_id', p_header_id);
119
120 dbms_sql.define_column (l_header_cursor, 1, l_source_document_id);
121 dbms_sql.define_column (l_header_cursor, 2, l_org_id);
122 dbms_sql.define_column (l_header_cursor, 3, l_source_document_type_id);
123 dbms_sql.define_column (l_header_cursor, 4, l_pricelist_id);
124 dbms_sql.define_column (l_header_cursor, 5, l_transaction_curr_code, 15);
125 dbms_sql.define_column (l_header_cursor, 6, l_quote_source_code, 240 );
126
127
128
129 l_dummy_id := dbms_sql.execute (l_header_cursor);
130
131 if ( dbms_sql.fetch_rows(l_header_cursor)>0 ) then
132 dbms_sql.column_value(l_header_cursor, 1, l_source_document_id);
133 dbms_sql.column_value (l_header_cursor, 2, l_org_id);
134 dbms_sql.column_value (l_header_cursor, 3, l_source_document_type_id);
135 dbms_sql.column_value (l_header_cursor, 4, l_pricelist_id);
136 dbms_sql.column_value (l_header_cursor, 5, l_transaction_curr_code);
137 dbms_sql.column_value (l_header_cursor, 6, l_quote_source_code);
138
139
140 else
141 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
142 FND_MESSAGE.SET_NAME('IBU','IBU_RET_REF_ORD_INVALID');
143 FND_MSG_PUB.Add;
144 RAISE FND_API.G_EXC_ERROR;
145 end if;
146
147 -- validate org_id, pricelist_id, transaction_curr_code
148 if (l_org_id is null OR l_org_id = FND_API.G_MISS_NUM OR l_pricelist_id is null OR l_pricelist_id = FND_API.G_MISS_NUM OR l_transaction_curr_code is null OR l_transaction_curr_code= FND_API.G_MISS_CHAR) then
149 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
150 FND_MESSAGE.SET_NAME('IBU','IBU_RET_REF_ORD_INVALID');
151 FND_MSG_PUB.Add;
152 RAISE FND_API.G_EXC_ERROR;
153 end if;
154
155 --fill l_qte_header_rec
156
157 l_qte_header_rec.quote_header_id := l_source_document_id;
158 l_qte_header_rec.org_id := l_org_id;
159 l_qte_header_rec.quote_source_code := l_quote_source_code;
160 l_qte_header_rec.party_id := HEADER_REC.PARTY_ID;
161 l_qte_header_rec.cust_account_id := HEADER_REC.CUST_ACCOUNT_ID;
162 l_qte_header_rec.org_contact_id := HEADER_REC.ORG_CONTACT_ID;
163 l_qte_header_rec.invoice_to_party_site_id := HEADER_REC.INVOICE_TO_PARTY_SITE_ID;
164 l_qte_header_rec.order_type_id := FND_PROFILE.VALUE_WNPS('ASO_ORDER_TYPE_ID'); --1436
165 l_qte_header_rec.quote_category_code := 'RETURN';
166 l_qte_header_rec.ordered_date := sysdate;
167 l_qte_header_rec.price_list_id := l_pricelist_id;
168 l_qte_header_rec.currency_code := l_transaction_curr_code;
169
170 -- if/else added by mukhan
171 -- obtaining person id from ra_salesreps since Order Capture
172 -- internally translates person id to salesrep id
173
174 OPEN Get_order_salesrep_id (p_header_id);
175 FETCH Get_order_salesrep_id into l_salesrep_id;
176 CLOSE Get_order_salesrep_id ;
177
178 if l_salesrep_id is NULL or l_salesrep_id = FND_API.G_MISS_NUM then
179 if FND_PROFILE.VALUE_WNPS('ASO_DEFAULT_PERSON_ID') is not NULL then
180 OPEN Get_person_id ( FND_PROFILE.VALUE_WNPS('ASO_DEFAULT_PERSON_ID'));
181 FETCH Get_person_id into l_person_id;
182 CLOSE Get_person_id ;
183 end if;
184 else
185 OPEN Get_person_id (l_salesrep_id);
186 FETCH Get_person_id into l_person_id;
187 CLOSE Get_person_id ;
188 end if;
189
190 l_qte_header_rec.employee_person_id := l_person_id;
191
192 l_header_shipment_tbl(1).request_date := sysdate;
193 l_header_shipment_tbl(1).schedule_ship_date := HEADER_SHIPMENT_REC.SCHEDULE_SHIP_DATE;
194 l_header_shipment_tbl(1).ship_to_party_site_id := HEADER_SHIPMENT_REC.SHIP_TO_PARTY_SITE_ID;
195 l_header_shipment_tbl(1).freight_carrier_code := HEADER_SHIPMENT_REC.FREIGHT_CARRIER_CODE;
196
197 l_ibu_ret_header_prpt_info := 'l_qte_header_rec::';
198 aso_debug_pub.add('IBU: l_qte_header_rec::', 1, 'Y');
199
200 if (l_qte_header_rec.quote_header_id <> FND_API.G_MISS_NUM) then
201 l_ibu_ret_header_info := 'quote_header_id = ' || l_qte_header_rec.quote_header_id || ' , ';
202 else
203 l_ibu_ret_header_info := 'quote_header_id = ';
204 end if;
205 aso_debug_pub.add ('IBU: quote_header_id = ' || l_qte_header_rec.quote_header_id , 1, 'Y');
206
207 if (l_qte_header_rec.org_id <> FND_API.G_MISS_NUM) then
208 l_ibu_ret_header_info := 'org_id = ' || l_qte_header_rec.org_id || ' , ';
209 else
210 l_ibu_ret_header_info := 'org_id = ';
211 end if;
212
213 aso_debug_pub.add ('IBU: org_id = ' || l_qte_header_rec.org_id , 1, 'Y');
214
215 if ( l_qte_header_rec.quote_source_code <> FND_API.G_MISS_CHAR) then
216 l_ibu_ret_header_info := 'quote_source_code = ' || l_qte_header_rec.quote_source_code || ' , ';
217 else
218 l_ibu_ret_header_info := 'quote_source_code = ';
219 end if;
220 aso_debug_pub.add ('IBU: quote_source_code = ' || l_qte_header_rec.quote_source_code , 1, 'Y');
221
222 if ( l_qte_header_rec.party_id <> FND_API.G_MISS_NUM ) then
223 l_ibu_ret_header_info := 'party_id = ' || l_qte_header_rec.party_id || ' , ';
224 else
225 l_ibu_ret_header_info := 'party_id = ';
226 end if;
227 aso_debug_pub.add ('IBU: party_id = ' || l_qte_header_rec.party_id , 1, 'Y');
228
229 if ( l_qte_header_rec.cust_account_id <> FND_API.G_MISS_NUM ) then
230 l_ibu_ret_header_info := 'cust_account_id = ' || l_qte_header_rec.cust_account_id || ' , ';
231 else
232 l_ibu_ret_header_info := 'cust_account_id = ';
233 end if;
234 aso_debug_pub.add ('IBU: cust_account_id = ' || l_qte_header_rec.cust_account_id , 1, 'Y');
235
236
237 if ( l_qte_header_rec.org_contact_id <> FND_API.G_MISS_NUM ) then
238 l_ibu_ret_header_info := 'org_contact_id = ' || l_qte_header_rec.org_contact_id || ' , ';
239 else
240 l_ibu_ret_header_info := 'org_contact_id = ';
241 end if;
242 aso_debug_pub.add ('IBU: org_contact_id = ' || l_qte_header_rec.org_contact_id , 1, 'Y');
243
244 if ( l_qte_header_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM ) then
245 l_ibu_ret_header_info := 'inv_to_py_site_id= ' || l_qte_header_rec.invoice_to_party_site_id || ' , ';
246 else
247 l_ibu_ret_header_info := 'inv_to_py_site_id= ';
248 end if;
249 aso_debug_pub.add ('IBU: inv_to_py_site_id= ' || l_qte_header_rec.invoice_to_party_site_id , 1, 'Y');
250
251 if ( l_qte_header_rec.order_type_id <> FND_API.G_MISS_NUM ) then
252 l_ibu_ret_header_info := 'order_type_id = ' || l_qte_header_rec.order_type_id || ' , ';
253 else
254 l_ibu_ret_header_info := 'order_type_id = ';
255 end if;
256 aso_debug_pub.add ('IBU: order_type_id = ' || l_qte_header_rec.order_type_id , 1, 'Y');
257
258 if ( l_qte_header_rec.quote_category_code <> FND_API.G_MISS_CHAR ) then
259 l_ibu_ret_header_info := 'quote_ca_code = ' || l_qte_header_rec.quote_category_code || ' , ';
260 else
261 l_ibu_ret_header_info := 'quote_ca_code = ';
262 end if;
263
264 aso_debug_pub.add ('IBU: quote_ca_code = ' || l_qte_header_rec.quote_category_code , 1, 'Y');
265
266 if ( l_qte_header_rec.ordered_date <> FND_API.G_MISS_DATE ) then
267 l_ibu_ret_header_info := 'ordered_date = ' || l_qte_header_rec.ordered_date || ' , ';
268 else
269 l_ibu_ret_header_info := 'ordered_date = ';
270 end if;
271
272 aso_debug_pub.add ('IBU:ordered_date = ' || l_qte_header_rec.ordered_date , 1, 'Y');
273
274
275
276 if ( l_qte_header_rec.employee_person_id <> FND_API.G_MISS_NUM ) then
277 l_ibu_ret_header_info := 'emp_person_id = ' || l_qte_header_rec.employee_person_id || ' , ';
278 else
279 l_ibu_ret_header_info := 'emp_person_id = ';
280 end if;
281 aso_debug_pub.add ('IBU: emp_person_id = ' || l_qte_header_rec.employee_person_id , 1, 'Y');
282
283 if ( l_qte_header_rec.price_list_id <> FND_API.G_MISS_NUM ) then
284 l_ibu_ret_header_info := 'price_list_id = ' || l_qte_header_rec.price_list_id || ' , ';
285 else
286 l_ibu_ret_header_info := 'price_list_id = ';
287 end if;
288 aso_debug_pub.add ('IBU: price_list_id = ' || l_qte_header_rec.price_list_id , 1, 'Y');
289
290 if ( l_qte_header_rec.currency_code <> FND_API.G_MISS_CHAR) then
291 l_ibu_ret_header_info := 'currency_code = ' || l_qte_header_rec.currency_code || ' , ';
292 else
293 l_ibu_ret_header_info := 'currency_code = ';
294 end if;
295 aso_debug_pub.add ('IBU: currency_code = ' || l_qte_header_rec.currency_code , 1, 'Y');
296
297 l_ibu_ret_head_ship_prpt_info := 'l_header_shipment_tbl(1)::';
298 aso_debug_pub.add ('IBU:l_header_shipment_tbl(1)::' , 1, 'Y');
299
300 if ( l_header_shipment_tbl(1).request_date <> FND_API.G_MISS_DATE) then
301 l_ibu_ret_head_ship_info := 'request_date = ' || l_header_shipment_tbl(1).request_date || ' , ';
302 else
303 l_ibu_ret_head_ship_info := 'request_date = ';
304 end if;
305 aso_debug_pub.add ('IBU: request_date = ' || l_header_shipment_tbl(1).request_date , 1, 'Y');
306
307 if ( l_header_shipment_tbl(1).schedule_ship_date <> FND_API.G_MISS_DATE ) then
308 l_ibu_ret_head_ship_info := 'sch_ship_date = ' || l_header_shipment_tbl(1).schedule_ship_date || ' , ';
309 else
310 l_ibu_ret_head_ship_info := 'sch_ship_date = ';
311 end if;
312 aso_debug_pub.add ('IBU: sch_ship_date = ' || l_header_shipment_tbl(1).schedule_ship_date , 1, 'Y');
313
314 if ( l_header_shipment_tbl(1).ship_to_party_site_id <> FND_API.G_MISS_NUM) then
315 l_ibu_ret_head_ship_info := 'ship_to_py_site_id = ' || l_header_shipment_tbl(1).ship_to_party_site_id || ' , ';
316 else
317 l_ibu_ret_head_ship_info := 'ship_to_py_site_id = ';
318 end if;
319 aso_debug_pub.add ('IBU: ship_to_py_site_id = ' || l_header_shipment_tbl(1).ship_to_party_site_id , 1, 'Y');
320
321 if ( l_header_shipment_tbl(1).freight_carrier_code <> FND_API.G_MISS_CHAR) then
322 l_ibu_ret_head_ship_info := 'freigt_carr_code = ' || l_header_shipment_tbl(1).freight_carrier_code || ' , ';
323 else
324 l_ibu_ret_head_ship_info := 'freigt_carr_code = ';
325 end if;
326 aso_debug_pub.add ('IBU: freigt_carr_code = ' || l_header_shipment_tbl(1).freight_carrier_code , 1, 'Y');
327
328 if (LINE_TBL.count > 0) then
329 for i in LINE_TBL.FIRST..LINE_TBL.LAST loop
330
331 l_line_cursor_stmt := 'select org_id, ship_from_org_id, inventory_item_id, order_quantity_uom, price_list_id, unit_list_price, unit_selling_price from oe_order_lines_all where line_id=:v_line_id';
332 l_line_cursor := dbms_sql.open_cursor;
333 dbms_sql.parse(l_line_cursor,l_line_cursor_stmt , dbms_sql.NATIVE);
334
335 dbms_sql.define_column (l_line_cursor, 1, l_line_org_id );
336 dbms_sql.define_column (l_line_cursor, 2, l_ship_from_org_id );
337 dbms_sql.define_column (l_line_cursor, 3, l_inventory_item_id);
338 dbms_sql.define_column (l_line_cursor, 4, l_ordered_quantity_uom, 3);
339 dbms_sql.define_column (l_line_cursor, 5, l_line_price_list_id);
340 dbms_sql.define_column (l_line_cursor, 6, l_unit_list_price);
341 dbms_sql.define_column (l_line_cursor, 7, l_unit_selling_price);
342
343
344
345 dbms_sql.bind_variable(l_line_cursor, ':v_line_id', LINE_TBL(i).LINE_ID) ;
346
347 l_dummy_id := dbms_sql.execute (l_line_cursor);
348
349 if ( dbms_sql.fetch_rows(l_line_cursor) > 0) then
350 dbms_sql.column_value(l_line_cursor, 1, l_line_org_id);
351 dbms_sql.column_value (l_line_cursor, 2, l_ship_from_org_id );
352 dbms_sql.column_value (l_line_cursor, 3, l_inventory_item_id);
353 dbms_sql.column_value (l_line_cursor, 4, l_ordered_quantity_uom);
354 dbms_sql.column_value (l_line_cursor, 5, l_line_price_list_id);
355 dbms_sql.column_value (l_line_cursor, 6, l_unit_list_price);
356 dbms_sql.column_value (l_line_cursor, 7, l_unit_selling_price);
357
358
359 else
360 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
361 FND_MESSAGE.SET_NAME('IBU','IBU_RET_REF_LINE_INVALID)');
362 FND_MSG_PUB.Add;
363 RAISE FND_API.G_EXC_ERROR;
364 end if;
365
366 -- validate line_org_id, ship_from_org_id, inventory_item_id, rdered_quantity_uom, line_pricelist_id, unit_list_price, unit_selling_price
367 if (l_line_org_id is null OR l_line_org_id = FND_API.G_MISS_NUM or l_inventory_item_id is null or l_inventory_item_id = FND_API.G_MISS_NUM) then
368 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
369 FND_MESSAGE.SET_NAME('IBU','IBU_RET_REF_LINE_INVALID)');
370 FND_MSG_PUB.Add;
371 RAISE FND_API.G_EXC_ERROR;
372 end if;
373
374 if (l_ordered_quantity_uom is null or l_ordered_quantity_uom = FND_API.G_MISS_CHAR or l_line_price_list_id is null or l_line_price_list_id = FND_API.G_MISS_NUM) then
375 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
376 FND_MESSAGE.SET_NAME('IBU','IBU_RET_REF_LINE_INVALID)');
377 FND_MSG_PUB.Add;
378 RAISE FND_API.G_EXC_ERROR;
379 end if;
380
381 if (l_unit_list_price is null or l_unit_list_price = FND_API.G_MISS_NUM or l_unit_selling_price is null or l_unit_selling_price = FND_API.G_MISS_NUM) then
382 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
383 FND_MESSAGE.SET_NAME('IBU','IBU_RET_REF_LINE_INVALID)');
384 FND_MSG_PUB.Add;
385 RAISE FND_API.G_EXC_ERROR;
386 end if;
387
388 --fill in the line related table values
389
390 l_qte_line_tbl (i).operation_code := 'CREATE';
391 l_qte_line_tbl (i).org_id := l_line_org_id;
392 l_qte_line_tbl (i).line_category_code := 'RETURN';
393
394 --l_qte_line_tbl (i).order_line_type_id := FND_PROFILE.VALUE_WNPS('IBU_ORDER_LINE_TYPE_ID');
395 l_qte_line_tbl (i).invoice_to_party_site_id := LINE_TBL(i).INVOICE_TO_PARTY_SITE_ID;
396 l_qte_line_tbl (i).inventory_item_id := l_inventory_item_id;
397 l_qte_line_tbl (i).quantity := LINE_TBL(i).QUANTITY;
398 l_qte_line_tbl (i).uom_code := l_ordered_quantity_uom;
399 l_qte_line_tbl (i).price_list_id := l_line_price_list_id;
400 l_qte_line_tbl (i).line_list_price := l_unit_list_price;
401 l_qte_line_tbl (i).line_quote_price := l_unit_selling_price;
402
403 l_qte_line_dtl_tbl (i).operation_code := 'CREATE';
404 l_qte_line_dtl_tbl (i).qte_line_index := i;
405 l_qte_line_dtl_tbl (i).return_ref_type := 'SALES ORDER';
406 l_qte_line_dtl_tbl (i).return_ref_header_id := p_header_id;
407 l_qte_line_dtl_tbl (i).return_ref_line_id := LINE_TBL(i).LINE_ID;
408 l_qte_line_dtl_tbl (i).return_attribute1 := p_header_id;
409 l_qte_line_dtl_tbl (i).return_attribute2 := LINE_TBL(i).LINE_ID;
410
411 l_qte_line_dtl_tbl (i).return_reason_code := LINE_DTL_TBL(i).RETURN_REASON_CODE;
412
413 l_line_shipment_tbl (i).operation_code := 'CREATE';
414 l_line_shipment_tbl (i).qte_line_index := i;
415 l_line_shipment_tbl (i).quantity := LINE_SHIPMENT_TBL (i).QUANTITY;
416 l_line_shipment_tbl (i).freight_carrier_code := LINE_SHIPMENT_TBL (i).FREIGHT_CARRIER_CODE;
417 l_line_shipment_tbl (i).schedule_ship_date := LINE_SHIPMENT_TBL (i).SCHEDULE_SHIP_DATE;
418 l_line_shipment_tbl (i).request_date := sysdate;
419 l_line_shipment_tbl (i).ship_to_party_site_id := LINE_SHIPMENT_TBL (i).SHIP_TO_PARTY_SITE_ID;
420
421 l_ibu_ret_line_prpt_info := 'l_qte_line_tbl(' || i || '): ';
422 aso_debug_pub.add ('IBU: l_qte_line_tbl(' || i || '): ' , 1, 'Y');
423
424
425 l_ibu_ret_line_prpt_info := '';
426
427 if ( l_qte_line_tbl (i).operation_code <> FND_API.G_MISS_CHAR ) then
428 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'operation_code = ' || l_qte_line_tbl (i).operation_code || ' , ';
429 else
430 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'operation_code = ';
431 end if;
432 aso_debug_pub.add ('IBU: operation_code = ' || l_qte_line_tbl (i).operation_code , 1, 'Y');
433
434 if ( l_qte_line_tbl (i).org_id <> FND_API.G_MISS_NUM ) then
435 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'org_id = ' || l_qte_line_tbl (i).org_id || ' , ';
436 else
437 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'org_id = ' ;
438 end if;
439 aso_debug_pub.add ('IBU: org_id = ' || l_qte_line_tbl (i).org_id , 1, 'Y');
440
441 if ( l_qte_line_tbl (i).line_category_code <> FND_API.G_MISS_CHAR ) then
442 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'line_category_code = ' || l_qte_line_tbl (i).line_category_code || ' , ';
443 else
444 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'line_category_code = ';
445 end if;
446 aso_debug_pub.add ('IBU: line_category_code = ' || l_qte_line_tbl (i).line_category_code , 1, 'Y');
447
448 if ( l_qte_line_tbl (i).order_line_type_id <> FND_API.G_MISS_NUM) then
449 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'ord_li_tp_id = ' || l_qte_line_tbl (i).order_line_type_id || ',';
450 else
451 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'ord_li_tp_id = ';
452 end if;
453 aso_debug_pub.add ('IBU: ord_li_tp_id = ' || l_qte_line_tbl (i).order_line_type_id , 1, 'Y');
454
455 if ( l_qte_line_tbl (i).invoice_to_party_site_id <> FND_API.G_MISS_NUM) then
456 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'inv_to_pty_site_id = ' || l_qte_line_tbl (i).invoice_to_party_site_id || ' , ';
457 else
458 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'inv_to_pty_site_id = ';
459 end if;
460 aso_debug_pub.add ('IBU: inv_to_pty_site_id = ' || l_qte_line_tbl (i).invoice_to_party_site_id , 1, 'Y');
461
462 if ( l_qte_line_tbl (i).inventory_item_id <> FND_API.G_MISS_NUM) then
463 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'inv_item_id = ' || l_qte_line_tbl (i).inventory_item_id || ' , ';
464 else
465 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'inv_item_id = ';
466 end if;
467 aso_debug_pub.add ('IBU: inv_item_id = ' || l_qte_line_tbl (i).inventory_item_id , 1, 'Y');
468
469 if ( l_qte_line_tbl (i).quantity <> FND_API.G_MISS_NUM) then
470 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'quantity = ' || l_qte_line_tbl (i).quantity || ' , ';
471 else
472 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'quantity = ';
473 end if;
474 aso_debug_pub.add ('IBU: quantity = ' || l_qte_line_tbl (i).quantity , 1, 'Y');
475
476 if ( l_qte_line_tbl (i).uom_code <> FND_API.G_MISS_CHAR ) then
477 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'uom_code = ' || l_qte_line_tbl (i).uom_code || ' , ';
478 else
479 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'uom_code = ';
480 end if;
481 aso_debug_pub.add ('IBU: uom_code = ' || l_qte_line_tbl (i).uom_code , 1, 'Y');
482
483 if ( l_qte_line_tbl (i).price_list_id <> FND_API.G_MISS_NUM ) then
484 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'price_list_id = ' || l_qte_line_tbl (i).price_list_id || ' , ';
485 else
486 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'price_list_id = ';
487 end if;
488 aso_debug_pub.add ('IBU: price_list_id = ' || l_qte_line_tbl (i).price_list_id , 1, 'Y');
489
490 if ( l_qte_line_tbl (i).line_list_price <> FND_API.G_MISS_NUM ) then
491 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'line_list_price = ' || l_qte_line_tbl (i).line_list_price || ' , ';
492 else
493 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'line_list_price = ' ;
494 end if;
495 aso_debug_pub.add ('IBU: line_list_price = ' || l_qte_line_tbl (i).line_list_price , 1, 'Y');
496
497 if ( l_qte_line_tbl (i).line_quote_price <> FND_API.G_MISS_NUM ) then
498 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'line_quote_price = ' || l_qte_line_tbl (i).line_quote_price || ' , ';
499 else
500 l_ibu_ret_line_info := l_ibu_ret_line_prpt_info || 'line_quote_price = ';
501 end if;
502 aso_debug_pub.add ('IBU: line_quote_price = ' || l_qte_line_tbl (i).line_quote_price , 1, 'Y');
503
504 l_ibu_ret_line_dtl_prpt_info := 'l_qte_line_dtl_tbl(' || i || '): ';
505 aso_debug_pub.add ('IBU: l_qte_line_dtl_tbl(' || i || '): ' , 1, 'Y');
506
507 l_ibu_ret_line_dtl_prpt_info := '';
508 if ( l_qte_line_dtl_tbl (i).operation_code <> FND_API.G_MISS_CHAR ) then
509 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'operation_code = ' || l_qte_line_dtl_tbl (i).operation_code || ' , ';
510 else
511 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'operation_code = ';
512 end if;
513 aso_debug_pub.add ('IBU: operation_code = ' || l_qte_line_dtl_tbl (i).operation_code , 1, 'Y');
514
515 if ( l_qte_line_dtl_tbl (i).qte_line_index <> FND_API.G_MISS_NUM) then
516 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'qte_line_index = ' || l_qte_line_dtl_tbl (i).qte_line_index || ' , ';
517 else
518 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'qte_line_index = ' ;
519 end if;
520 aso_debug_pub.add ('IBU: qte_line_index = ' || l_qte_line_dtl_tbl (i).qte_line_index , 1, 'Y');
521
522 if ( l_qte_line_dtl_tbl (i).return_ref_type <> FND_API.G_MISS_CHAR ) then
523 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ret_ref_type = ' || l_qte_line_dtl_tbl (i).return_ref_type || ' , ';
524 else
525 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ret_ref_type = ';
526 end if;
527 aso_debug_pub.add ('IBU: ret_ref_type = ' || l_qte_line_dtl_tbl (i).return_ref_type , 1, 'Y');
528
529 if ( l_qte_line_dtl_tbl (i).return_attribute1 <> FND_API.G_MISS_CHAR) then
530 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'return_att1 = ' || l_qte_line_dtl_tbl (i).return_attribute1 || ' , ';
531 else
532 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'return_att1 = ';
533 end if;
534 aso_debug_pub.add ('IBU: return_att1 = ' || l_qte_line_dtl_tbl (i).return_attribute1 , 1, 'Y');
535
536 if ( l_qte_line_dtl_tbl (i).return_attribute2 <> FND_API.G_MISS_CHAR) then
537 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'return_att2 = ' || l_qte_line_dtl_tbl (i).return_attribute2 || ' , ';
538 else
539 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'return_att2 = ';
540 end if;
541 aso_debug_pub.add ('IBU: return_att2 = ' || l_qte_line_dtl_tbl (i).return_attribute2 , 1, 'Y');
542
543 if ( l_qte_line_dtl_tbl (i).return_ref_header_id <> FND_API.G_MISS_NUM) then
544 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ret_head_id_ = ' || l_qte_line_dtl_tbl (i).return_ref_header_id || ' , ';
545 else
546 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ret_head_id_ = ' ;
547 end if;
548 aso_debug_pub.add ('IBU: ret_head_id_ = ' || l_qte_line_dtl_tbl (i).return_ref_header_id , 1, 'Y');
549
550 if ( l_qte_line_dtl_tbl (i).return_ref_line_id <> FND_API.G_MISS_NUM) then
551 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ret_line_id = ' || l_qte_line_dtl_tbl (i).return_ref_line_id || ' , ';
552 else
553 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ret_line_id = ';
554 end if;
555 aso_debug_pub.add ('IBU: ret_line_id = ' || l_qte_line_dtl_tbl (i).return_ref_line_id , 1, 'Y');
556
557
558 if ( l_qte_line_dtl_tbl (i).return_reason_code <> FND_API.G_MISS_CHAR) then
559 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 're_reas_code = ' || l_qte_line_dtl_tbl (i).return_reason_code || ' , ';
560 else
561 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 're_reas_code = ';
562 end if;
563 aso_debug_pub.add ('IBU: re_reas_code = ' || l_qte_line_dtl_tbl (i).return_reason_code , 1, 'Y');
564
565
566
567 l_ibu_ret_line_dtl_prpt_info := 'l_line_shipment_tbl(' || i || '): ';
568 aso_debug_pub.add ('IBU: l_line_shipment_tbl(' || i || '): ' , 1, 'Y');
569
570
571 l_ibu_ret_line_dtl_prpt_info :='';
572
573 if ( l_line_shipment_tbl (i).operation_code <> FND_API.G_MISS_CHAR ) then
574
575 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'op_code = ' || l_line_shipment_tbl (i).operation_code || ' , ';
576 else
577 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'op_code = ';
578 end if;
579 aso_debug_pub.add ('IBU: op_code = ' || l_line_shipment_tbl (i).operation_code , 1, 'Y');
580
581 if ( l_line_shipment_tbl (i).qte_line_index <> FND_API.G_MISS_NUM ) then
582 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'qte_line_index = ' || l_line_shipment_tbl (i).qte_line_index || ' , ';
583 else
584 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'qte_line_index = ';
585 end if;
586 aso_debug_pub.add ('IBU: qte_line_index = ' || l_line_shipment_tbl (i).qte_line_index , 1, 'Y');
587
588 if ( l_line_shipment_tbl (i).quantity <> FND_API.G_MISS_NUM ) then
589 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'quantity = ' || l_line_shipment_tbl (i).quantity || ' , ';
590 else
591 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'quantity = ';
592 end if;
593 aso_debug_pub.add ('IBU: quantity = ' || l_line_shipment_tbl (i).quantity , 1, 'Y');
594
595 if ( l_line_shipment_tbl (i).freight_carrier_code <> FND_API.G_MISS_CHAR) then
596 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'freight_car_code = ' || l_line_shipment_tbl (i).freight_carrier_code || ' , ';
597 else
598 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'freight_car_code = ';
599 end if;
600 aso_debug_pub.add ('IBU: freight_car_code = ' || l_line_shipment_tbl (i).freight_carrier_code , 1, 'Y');
601
602
603 if ( l_line_shipment_tbl (i).schedule_ship_date <> FND_API.G_MISS_DATE) then
604 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'sche_ship_date = ' || l_line_shipment_tbl (i).schedule_ship_date || ' , ';
605 else
606 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'sche_ship_date = ' ;
607 end if;
608 aso_debug_pub.add ('IBU: sche_ship_date = ' || l_line_shipment_tbl (i).schedule_ship_date , 1, 'Y');
609
610 if ( l_line_shipment_tbl (i).request_date <> FND_API.G_MISS_DATE) then
611 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'request_date = ' || l_line_shipment_tbl (i).request_date || ' , ';
612 else
613 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'request_date = ' ;
614 end if;
615 aso_debug_pub.add ('IBU: request_date = ' || l_line_shipment_tbl (i).request_date , 1, 'Y');
616
617 if ( l_line_shipment_tbl (i).ship_to_party_site_id <> FND_API.G_MISS_NUM) then
618 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ship_to_pty_site_id = ' || l_line_shipment_tbl (i).ship_to_party_site_id || ' , ';
619 else
620 l_ibu_ret_line_dtl_info := l_ibu_ret_line_dtl_prpt_info || 'ship_to_pty_site_id = ';
621 end if;
622 aso_debug_pub.add ('IBU: ship_to_pty_site_id = ' || l_line_shipment_tbl (i).ship_to_party_site_id , 1, 'Y');
623
624 end loop;
625 end if;
626 l_booked_profile_value := FND_PROFILE.VALUE_WNPS('ASO_DEFAULT_ORDER_STATE');
627 if ( l_booked_profile_value = 'BOOKED' ) then
628 l_control_rec.book_flag := FND_API.G_TRUE;
629 else
630 l_control_rec.book_flag := FND_API.G_FALSE;
631 end if;
632
633 l_ibu_ret_header_prpt_info := 'bk_flg='|| l_control_rec.book_flag ;
634
635 aso_debug_pub.add ('IBU: ' || l_ibu_ret_header_prpt_info , 1, 'Y');
636
637 aso_order_int.create_order (
638 p_api_version_number => 1.0,
639 p_init_msg_list => FND_API.G_FALSE,
640 p_commit => FND_API.G_FALSE,
641 p_qte_rec => l_qte_header_rec,
642 p_header_shipment_tbl => l_header_shipment_tbl,
643 p_qte_line_tbl => l_qte_line_tbl,
644 p_qte_line_dtl_tbl => l_qte_line_dtl_tbl,
645 p_line_shipment_tbl => l_line_shipment_tbl,
646 p_control_rec => l_control_rec,
647 x_order_header_rec => l_return_header_rec,
648 x_order_line_tbl => l_return_line_tbl,
649 x_return_status => X_RETURN_STATUS,
650 x_msg_count => X_MSG_COUNT,
651 x_msg_data => X_MSG_DATA );
652
653
654
655 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
656
657 RAISE FND_API.G_EXC_ERROR;
658 end if;
659
660 X_RETURN_HEADER_REC.ORDER_NUMBER := l_return_header_rec.order_number;
661 X_RETURN_HEADER_REC.ORDER_HEADER_ID := l_return_header_rec.order_header_id;
662 X_RETURN_HEADER_REC.STATUS := l_return_header_rec.status;
663
664 if ( l_return_line_tbl.count > 0) then
665 for i in l_return_line_tbl.first..l_return_line_tbl.last loop
666 X_RETURN_LINE_TBL (i).ORDER_LINE_ID := l_return_line_tbl (i).order_line_id;
667 X_RETURN_LINE_TBL (i).ORDER_HEADER_ID := l_return_line_tbl (i).order_header_id;
668 X_RETURN_LINE_TBL (i).STATUS := l_return_line_tbl (i).status;
669 end loop;
670 end if;
671
672 -- Standard check of p_commit.
673 IF FND_API.To_Boolean( p_commit ) THEN
674 COMMIT WORK;
675 END IF;
676 -- Standard call to get message count and if count is 1, get message info.
677 FND_MSG_PUB.Count_And_Get
678 (p_count => x_msg_count ,
679 p_data => x_msg_data
680 );
681
682
683 EXCEPTION
684
685 WHEN FND_API.G_EXC_ERROR THEN
686 FND_MESSAGE.SET_NAME('IBU', 'IBU Return Failed' );
687 FND_MSG_PUB.Add;
688 FND_MSG_PUB.Count_And_Get
689 (p_count => x_msg_count ,
690 p_data => x_msg_data
691 );
692 /*
693 WHEN OTHERS THEN
694
695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
696 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
697 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,l_api_name);
698
699 END IF;
700 FND_MESSAGE.SET_NAME('IBU', 'IBU_RET_FAILED' );
701 FND_MSG_PUB.Add;
702 FND_MSG_PUB.Count_And_Get
703 (p_count => x_msg_count ,
704 p_data => x_msg_data
705 );
706 raise;
707 */
708 END CREATE_RETURN;
709
710 PROCEDURE Get_Messages (
711 p_message_count IN NUMBER,
712 x_msgs OUT NOCOPY VARCHAR2)
713 IS
714 l_msg_list VARCHAR2(8000) := '
715 ';
716 l_temp_msg VARCHAR2(2000);
717 l_appl_short_name VARCHAR2(20) ;
718 l_message_name VARCHAR2(30) ;
719
720 l_id NUMBER;
721 l_message_num NUMBER;
722
723 l_msg_count NUMBER;
724 l_msg_data VARCHAR2(2000);
725
726 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
727 SELECT application_id
728 FROM fnd_application_vl
729 WHERE application_short_name = x_short_name;
730
731 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
732 SELECT msg.message_number
733 FROM fnd_new_messages msg, fnd_languages_vl lng
734 WHERE msg.message_name = x_msg
735 and msg.application_id = x_id
736 and lng.LANGUAGE_CODE = msg.language_code
737 and lng.language_id = x_lang_id;
738 BEGIN
739
740 FOR l_count in 1..NVL(p_message_count,0) LOOP
741 if l_count = 1 then
742 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_true);
743 else
744 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
745 end if ;
746 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
747 OPEN Get_Appl_Id (l_appl_short_name);
748 FETCH Get_Appl_Id into l_id;
749 CLOSE Get_Appl_Id;
750
751 l_message_num := NULL;
752 IF l_id is not NULL
753 THEN
754 OPEN Get_Message_Num (l_message_name, l_id,
755 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
756 FETCH Get_Message_Num into l_message_num;
757 CLOSE Get_Message_Num;
758 END IF;
759
760 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
761
762 IF NVL(l_message_num, 0) <> 0
763 THEN
764 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
765 ELSE
766 l_temp_msg := NULL;
767 END IF;
768
769 IF l_count = 1
770 THEN
771 l_msg_list := l_msg_list || l_temp_msg ||
772 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
773 ELSE
774 l_msg_list := l_msg_list || l_temp_msg ||
775 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
776 END IF;
777
778 l_msg_list := l_msg_list || '
779 ';
780 EXIT WHEN length(l_msg_list) > 8000;
781 END LOOP;
782
783 x_msgs := substr(l_msg_list, 0, 8000);
784
785
786 /* l_msg_list:=' ';
787 for l_count in 1..NVL(p_message_count,0) loop
788 l_temp_msg := fnd_msg_pub.get( p_msg_index => l_count,
789 p_encoded => 'F' );
790 l_msg_list := l_msg_list || l_temp_msg;
791 l_msg_list := l_msg_list || '
792 ';
793 EXIT WHEN length(l_msg_list) > 8000;
794 end loop;
795 x_msgs := substr(l_msg_list, 0, 8000);
796 */
797 END Get_Messages;
798
799 FUNCTION GET_RETURN_LINES_TOTAL(
800 P_HEADER_ID IN NUMBER)
801 RETURN NUMBER
802 IS
803 l_Total NUMBER := FND_API.G_MISS_NUM;
804
805 CURSOR ALL_HEADER_LINES (c_header_id NUMBER) IS
806 SELECT LINE_ID, LINE_NUMBER
807 FROM ASO_I_OE_ORDER_LINES_V
808 WHERE LINE_CATEGORY_CODE = 'RETURN'
809 AND HEADER_ID = c_header_id;
810
811 l_line_id number;
812 l_line_no number;
813
814 BEGIN
815 OPEN ALL_HEADER_LINES(P_HEADER_ID);
816 LOOP
817 FETCH ALL_HEADER_LINES INTO l_line_id, l_line_no;
818 EXIT WHEN ALL_HEADER_LINES%NOTFOUND;
819
820 IF (l_Total = FND_API.G_MISS_NUM) THEN
821 l_Total := 0;
822 END IF;
823
824 l_Total := l_Total + OE_OE_TOTALS_SUMMARY.LINE_TOTAL(
825 P_HEADER_ID,
826 l_line_id,
827 l_line_no,
828 NULL
829 );
830
831 END LOOP;
832
833 RETURN (l_Total);
834
835 EXCEPTION
836 WHEN no_data_found THEN
837 l_Total := FND_API.G_MISS_NUM;
838
839 IF ALL_HEADER_LINES%ISOPEN then
840 CLOSE ALL_HEADER_LINES;
841 END IF;
842 return FND_API.G_MISS_NUM;
843
844 WHEN too_many_rows THEN
845 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 IF ALL_HEADER_LINES%ISOPEN then
847 CLOSE ALL_HEADER_LINES;
848 END IF;
849 return FND_API.G_MISS_NUM;
850 WHEN others THEN
851 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 IF ALL_HEADER_LINES%ISOPEN then
853 CLOSE ALL_HEADER_LINES;
854 END IF;
855 return FND_API.G_MISS_NUM;
856
857
858 END GET_RETURN_LINES_TOTAL;
859
860 END IBU_ORDER_CAPTURE;
861