DBA Data[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