eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OE_BATCH_PRICING

Source


1 PACKAGE BODY OE_BATCH_PRICING AS
2 /* $Header: OEXBPRIB.pls 120.0.12010000.4 2008/11/28 09:31:15 smanian noship $ */
3 
4 PROCEDURE PRICE
5 (
6   ERRBUF			OUT NOCOPY	VARCHAR2,
7   RETCODE			OUT NOCOPY	VARCHAR2,
8   p_preview_mode		IN		VARCHAR2,
9   p_pricing_level		IN		VARCHAR2,
10   p_dummy			IN		VARCHAR2,
11   p_org_id			IN		NUMBER,
12   p_order_number_low		IN		NUMBER,
13   p_order_number_high		IN		NUMBER,
14   p_order_type_id		IN		NUMBER,
15   p_line_type_id		IN		NUMBER,
16   p_customer_id			IN		NUMBER,
17   p_ship_to_org_id		IN		NUMBER,
18   p_invoice_to_org_id		IN		NUMBER,
19   p_customer_class_code		IN		VARCHAR2,
20   p_salesrep_id			IN		NUMBER,
21   p_price_list_id		IN		NUMBER,
22   p_inventory_item_id		IN		NUMBER,
23   p_item_category_id		IN		NUMBER,
24   p_ship_from_org_id		IN		NUMBER,
25   p_order_date_low		IN		VARCHAR2,
26   p_order_date_high		IN		VARCHAR2,
27   p_order_creation_date_low	IN		VARCHAR2,
28   p_order_creation_date_high	IN		VARCHAR2,
29   p_line_creation_date_low	IN		VARCHAR2,
30   p_line_creation_date_high	IN		VARCHAR2,
31   p_booked_date_low		IN		VARCHAR2,
32   p_booked_date_high		IN		VARCHAR2,
33   p_pricing_date_low		IN		VARCHAR2,
34   p_pricing_date_high		IN		VARCHAR2,
35   p_schedule_ship_date_low	IN		VARCHAR2,
36   p_schedule_ship_date_high	IN		VARCHAR2,
37   p_booked_orders		IN		VARCHAR2,
38   p_header_id			IN		NUMBER	DEFAULT NULL,
39   p_line_count			IN		NUMBER  DEFAULT NULL,
40   p_line_list			IN		VARCHAR2 DEFAULT NULL
41 )
42 IS
43 	l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
44 
45 	l_lines_count	NUMBER := 0;
46 	l_lines_list	VARCHAR2(4000);
47 	l_submit_request BOOLEAN := FALSE;
48 
49 	l_child_request number;
50 	l_req_data               VARCHAR2(10);
51 	l_req_data_counter       NUMBER :=0;
52 
53 
54 	l_request_id	number;
55 	l_user_id	number;
56 	l_resp_id	number;
57 	l_resp_appl_id	number;
58 
59 	l_return_status	VARCHAR2(1);
60 	l_msg_count	NUMBER;
61 	l_msg_data	VARCHAR2(4000);
62 
63 	l_order_date_low DATE;
64 	l_order_date_high DATE;
65 	l_order_creation_date_low DATE;
66 	l_order_creation_date_high DATE;
67 	l_line_creation_date_low DATE;
68 	l_line_creation_date_high DATE;
69 	l_booked_date_low DATE;
70 	l_booked_date_high DATE;
71 	l_pricing_date_low DATE;
72 	l_pricing_date_high DATE;
73 	l_schedule_ship_date_low DATE;
74 	l_schedule_ship_date_high DATE;
75 
76 
77 	l_preview	varchar2(1000);
78 	l_subtotal	number;
79 	l_discount	number;
80 	l_charges	number;
81 	l_tax		number;
82 
83 
84 	/* Main cursor query to fetch the orders matching input criteria */
85 	CURSOR C_ORDERS
86 	IS
87 	select	h.org_id, h.header_id, 0 line_id, 0 line_number, 0 shipment_number, h.order_number, party.party_name account_name, tt.name order_type, h.transactional_curr_code
88 	from	oe_order_headers h,
89            	hz_parties party,
90 		hz_cust_accounts cust,
91 		oe_transaction_types_tl tt
92 	where	h.open_flag = 'Y'
93 	and	h.order_number between nvl(p_order_number_low, h.order_number) and nvl(p_order_number_high, h.order_number)
94 	and	h.order_type_id = nvl(p_order_type_id, h.order_type_id)
95 	and	NVL(h.sold_to_org_id,-1) = nvl(p_customer_id, NVL(h.sold_to_org_id,-1))
96 	and	nvl(h.ship_to_org_id, -1) = nvl(p_ship_to_org_id, nvl(h.ship_to_org_id, -1))
97 	and	nvl(h.invoice_to_org_id, -1) = nvl(p_invoice_to_org_id, nvl(h.invoice_to_org_id, -1))
98 	and	nvl(h.salesrep_id, -1) = nvl(p_salesrep_id, nvl(h.salesrep_id, -1))
99 	and	h.ordered_date between nvl(l_order_date_low, h.ordered_date) and nvl(l_order_date_high, h.ordered_date)
100 	and	h.creation_date between nvl(l_order_creation_date_low, h.creation_date) and nvl(l_order_creation_date_high, h.creation_date)
101 	and	nvl(h.booked_date, sysdate) between nvl(l_booked_date_low, nvl(h.booked_date, sysdate)) and nvl(l_booked_date_high, nvl(h.booked_date, sysdate))
102 	and	h.booked_flag = decode(p_booked_orders, 'Y', 'Y', 'N', 'N', NULL, h.booked_flag)
103         and	h.sold_to_org_id        =  cust.cust_account_id(+)
104        	and     cust.party_id           = party.party_id (+)
105 	and	nvl(cust.customer_class_code, '-1') = nvl(p_customer_class_code, nvl(cust.customer_class_code, '-1'))
106 	and	NVL(h.price_list_id,-1) = nvl(p_price_list_id, NVL(h.price_list_id,-1))
107 	and	tt.transaction_type_id = h.order_type_id
108 	and	tt.language = userenv('LANG')
109 	order by h.org_id, h.header_id;
110 
111 	/* Main cursor query to fetch the lines matching input criteria */
112 	CURSOR C_LINES
113 	IS
114 	select	l.org_id, l.header_id, l.line_id, l.line_number, l.shipment_number, h.order_number, party.party_name account_name, tt.name order_type, h.transactional_curr_code
115 	from	oe_order_headers h,
116 		oe_order_lines l,
117 		mtl_item_categories ic,
118 		mtl_default_category_sets cs,
119 		hz_cust_accounts cust,
120 		hz_parties party,
121 		oe_transaction_types_tl tt
122 	where	h.open_flag = 'Y'
123 	and	h.order_number between nvl(p_order_number_low, h.order_number) and nvl(p_order_number_high, h.order_number)
124 	and	h.order_type_id = nvl(p_order_type_id, h.order_type_id)
125 	and	NVL(h.sold_to_org_id,-1) = nvl(p_customer_id, NVL(h.sold_to_org_id,-1))
126 	and	h.ordered_date between nvl(l_order_date_low, h.ordered_date) and nvl(l_order_date_high, h.ordered_date)
127 	and	h.creation_date between nvl(l_order_creation_date_low, h.creation_date) and nvl(l_order_creation_date_high, h.creation_date)
128 	and	nvl(h.booked_date, sysdate) between nvl(l_booked_date_low, nvl(h.booked_date, sysdate)) and nvl(l_booked_date_high, nvl(h.booked_date, sysdate))
129 	and	h.booked_flag = decode(p_booked_orders, 'Y', 'Y', 'N', 'N', NULL, h.booked_flag)
130 	and	l.header_id = h.header_id
131 	and	l.line_type_id = nvl(p_line_type_id, l.line_type_id)
132 	and	l.open_flag = 'Y'
133 	and	NVL(l.price_list_id,-1) = nvl(p_price_list_id, NVL(l.price_list_id,-1))
134 	and	nvl(l.ship_to_org_id, -1) = nvl(p_ship_to_org_id, nvl(l.ship_to_org_id, -1))
135 	and	nvl(l.invoice_to_org_id, -1) = nvl(p_invoice_to_org_id, nvl(l.invoice_to_org_id, -1))
136 	and	nvl(l.salesrep_id, -1) = nvl(p_salesrep_id, nvl(l.salesrep_id, -1))
137 	and	NVL(l.ship_from_org_id,-1) = nvl(p_ship_from_org_id, NVL(l.ship_from_org_id,-1))
138 	and	l.inventory_item_id = nvl(p_inventory_item_id, l.inventory_item_id)
139 	and	l.creation_date between nvl(l_line_creation_date_low, l.creation_date) and nvl(l_line_creation_date_high, l.creation_date)
140 	and	nvl(l.pricing_date, sysdate) between nvl(l_pricing_date_low, nvl(l.pricing_date, sysdate)) and nvl(l_pricing_date_high, nvl(l.pricing_date, sysdate))
141 	and	nvl(l.schedule_ship_date, sysdate) between nvl(l_schedule_ship_date_low, nvl(l.schedule_ship_date, sysdate)) and nvl(l_schedule_ship_date_high, nvl(l.schedule_ship_date, sysdate))
142 	and	ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',l.org_id)
143 	and	ic.inventory_item_id = l.inventory_item_id
144 	and	ic.category_set_id = cs.category_set_id
145 	and	ic.category_id = nvl(p_item_category_id, ic.category_id)
146 	and	cs.functional_area_id = 7
147 	and	nvl(cust.customer_class_code, '-1') = nvl(p_customer_class_code, nvl(cust.customer_class_code, '-1'))
148 	and	h.sold_to_org_id        =  cust.cust_account_id(+)
149         and     cust.party_id           = party.party_id (+)
150 	and	tt.transaction_type_id = h.order_type_id
151 	and	tt.language = userenv('LANG')
152 	order by l.org_id, l.header_id, l.line_number, l.shipment_number, l.option_number;
153 
154 	TYPE hdr_lines_tbl_type IS TABLE OF C_LINES%rowtype INDEX BY BINARY_INTEGER;
155 
156 	l_hdr_lines_tbl hdr_lines_tbl_type;
157 
158 BEGIN
159 
160 	FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
161 	l_req_data := fnd_conc_global.request_data;
162 
163 	IF l_debug_level > 0 THEN
164 		oe_debug_pub.add('REQUEST ID : '|| l_request_id, 1);
165 		oe_debug_pub.add('Request Data : ' || nvl(l_req_data, -99), 1);
166 	END IF;
167 
168 	/* l_req_data is NULL means this is the Parent Request, being executed for first time.
169 
170 	   l_req_data is NOT NULL, means this is the second time the Parent Request is being executed,
171 	   when being re-invoked from Paused status. This happens after all the child requests complete
172 	   their execution. l_req_data will represent the number of child requests that were submitted.
173 	   In this case, we need to simply exit the program without doing anything, as all the child
174 	   requests have completed their execution and no further processing needs to be done.
175 
176 
177 	   When Parent Request is submitted, the p_header_id parameter will be NULL.
178 	   When we submit the Child Request, we always pass the p_header_id value.
179 	   This will help us in distinguishing if this is a Parent Request call or  Child Request call,
180 	   we can base our processing logic accordingly.
181 
182 	   We are submitting one child request for each Header, to optimally use the built in
183 	   parallel processing logic of the Concurrent Manager.
184 
185 	*/
186 
187 	if (l_req_data is null and p_header_id IS NULL) then
188 	/* Header Id is NULL, means this is the parent request */
189 
190 		fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
191 
192 		IF l_debug_level > 0 THEN
193 			oe_debug_pub.add('Entering OE_BATCH_PRICING.PRICE', 1);
194 		END IF;
195 
196 		ERRBUF  := 'Batch Pricing Request completed successfully';
197 		RETCODE := 0;
198 
199 		IF l_debug_level > 0 THEN
200 			oe_debug_pub.add('Parameters are : ', 5);
201 			oe_debug_pub.add('...Pricing Level : ' || p_pricing_level, 5);
202 			oe_debug_pub.add('...Preview Mode : ' || p_preview_mode, 5);
203 			oe_debug_pub.add('...Operating Unit : ' || p_org_id, 5);
204 			oe_debug_pub.add('...Order Number (Low) : ' || p_order_number_low, 5);
205 			oe_debug_pub.add('...Order Number (High) : ' || p_order_number_high, 5);
206 			oe_debug_pub.add('...Order Type : ' || p_order_type_id, 5);
207 			oe_debug_pub.add('...Line Type : ' || p_line_type_id, 5);
208 			oe_debug_pub.add('...Customer : ' || p_customer_id, 5);
209 			oe_debug_pub.add('...Ship To Location : ' || p_ship_to_org_id, 5);
210 			oe_debug_pub.add('...Bill To Location : ' || p_invoice_to_org_id, 5);
211 			oe_debug_pub.add('...Customer Class : ' || p_customer_class_code, 5);
212 			oe_debug_pub.add('...Salesrep : ' || p_salesrep_id, 5);
213 			oe_debug_pub.add('...Pricelist : ' || p_price_list_id, 5);
214 			oe_debug_pub.add('...Inventory Item : ' || p_inventory_item_id, 5);
215 			oe_debug_pub.add('...Item Category : ' || p_item_category_id, 5);
216 			oe_debug_pub.add('...Warehouse : ' || p_ship_from_org_id, 5);
217 			oe_debug_pub.add('...Order Date (Low) : ' || p_order_date_low, 5);
218 			oe_debug_pub.add('...Order Date (High) : ' || p_order_date_high, 5);
219 			oe_debug_pub.add('...Order Creation Date (Low) : ' || p_order_creation_date_low, 5);
220 			oe_debug_pub.add('...Order Creation Date (High) : ' || p_order_creation_date_high, 5);
221 			oe_debug_pub.add('...Line Creation Date (Low) : ' || p_line_creation_date_low, 5);
222 			oe_debug_pub.add('...Line Creation Date (High) : ' || p_line_creation_date_high, 5);
223 			oe_debug_pub.add('...Booked Date (Low) : ' || p_booked_date_low, 5);
224 			oe_debug_pub.add('...Booked Date (High) : ' || p_booked_date_high, 5);
225 			oe_debug_pub.add('...Pricing Date (Low) : ' || p_pricing_date_low, 5);
226 			oe_debug_pub.add('...Pricing Date (High) : ' || p_pricing_date_high, 5);
227 			oe_debug_pub.add('...Schedule Ship Date (Low) : ' || p_schedule_ship_date_low, 5);
228 			oe_debug_pub.add('...Schedule Ship Date (High) : ' || p_schedule_ship_date_high, 5);
229 			oe_debug_pub.add('...Booked Orders : ' || p_booked_orders, 5);
230 		END IF;
231 
232 		fnd_file.put_line(FND_FILE.OUTPUT, 'Parameters are : ');
233 		fnd_file.put_line(FND_FILE.OUTPUT, '...Pricing Level : ' || p_pricing_level);
234 		fnd_file.put_line(FND_FILE.OUTPUT, '...Preview Mode : ' || p_preview_mode);
235 		fnd_file.put_line(FND_FILE.OUTPUT, '...Operating Unit : ' || p_org_id);
236 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Number (Low) : ' || p_order_number_low);
237 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Number (High) : ' || p_order_number_high);
238 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Type : ' || p_order_type_id);
239 		fnd_file.put_line(FND_FILE.OUTPUT, '...Line Type : ' || p_line_type_id);
240 		fnd_file.put_line(FND_FILE.OUTPUT, '...Customer : ' || p_customer_id);
241 		fnd_file.put_line(FND_FILE.OUTPUT, '...Ship To Location : ' || p_ship_to_org_id);
242 		fnd_file.put_line(FND_FILE.OUTPUT, '...Bill To Location : ' || p_invoice_to_org_id);
243 		fnd_file.put_line(FND_FILE.OUTPUT, '...Customer Class : ' || p_customer_class_code);
244 		fnd_file.put_line(FND_FILE.OUTPUT, '...Salesrep : ' || p_salesrep_id);
245 		fnd_file.put_line(FND_FILE.OUTPUT, '...Pricelist : ' || p_price_list_id);
246 		fnd_file.put_line(FND_FILE.OUTPUT, '...Inventory Item : ' || p_inventory_item_id);
247 		fnd_file.put_line(FND_FILE.OUTPUT, '...Item Category : ' || p_item_category_id);
248 		fnd_file.put_line(FND_FILE.OUTPUT, '...Warehouse : ' || p_ship_from_org_id);
249 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Date (Low) : ' || p_order_date_low);
250 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Date (High) : ' || p_order_date_high);
251 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Creation Date (Low) : ' || p_order_creation_date_low);
252 		fnd_file.put_line(FND_FILE.OUTPUT, '...Order Creation Date (High) : ' || p_order_creation_date_high);
253 		fnd_file.put_line(FND_FILE.OUTPUT, '...Line Creation Date (Low) : ' || p_line_creation_date_low);
254 		fnd_file.put_line(FND_FILE.OUTPUT, '...Line Creation Date (High) : ' || p_line_creation_date_high);
255 		fnd_file.put_line(FND_FILE.OUTPUT, '...Booked Date (Low) : ' || p_booked_date_low);
256 		fnd_file.put_line(FND_FILE.OUTPUT, '...Booked Date (High) : ' || p_booked_date_high);
257 		fnd_file.put_line(FND_FILE.OUTPUT, '...Pricing Date (Low) : ' || p_pricing_date_low);
258 		fnd_file.put_line(FND_FILE.OUTPUT, '...Pricing Date (High) : ' || p_pricing_date_high);
259 		fnd_file.put_line(FND_FILE.OUTPUT, '...Schedule Ship Date (Low) : ' || p_schedule_ship_date_low);
260 		fnd_file.put_line(FND_FILE.OUTPUT, '...Schedule Ship Date (High) : ' || p_schedule_ship_date_high);
261 		fnd_file.put_line(FND_FILE.OUTPUT, '...Booked Orders : ' || p_booked_orders);
262 
263 		IF l_debug_level > 0 THEN
264 			oe_debug_pub.add('Before converting Canonical to Date', 5);
265 		END IF;
266 
267 		/* Convert Strings to Dates */
268 		select	FND_DATE.Canonical_To_Date(p_order_date_low),
269 			FND_DATE.Canonical_To_Date(p_order_date_high),
270 			FND_DATE.Canonical_To_Date(p_order_creation_date_low),
271 			FND_DATE.Canonical_To_Date(p_order_creation_date_high),
272 			FND_DATE.CHARDT_TO_DATE(p_line_creation_date_low),
273 			FND_DATE.CHARDT_TO_DATE(p_line_creation_date_high),
274 			FND_DATE.Canonical_To_Date(p_booked_date_low),
275 			FND_DATE.Canonical_To_Date(p_booked_date_high),
276 			FND_DATE.CHARDT_TO_DATE(p_pricing_date_low),
277 			FND_DATE.CHARDT_TO_DATE(p_pricing_date_high),
278 			FND_DATE.CHARDT_TO_DATE(p_schedule_ship_date_low),
279 			FND_DATE.CHARDT_TO_DATE(p_schedule_ship_date_high)
280 		into	l_order_date_low,
281 			l_order_date_high,
282 			l_order_creation_date_low,
283 			l_order_creation_date_high,
284 			l_line_creation_date_low,
285 			l_line_creation_date_high,
286 			l_booked_date_low,
287 			l_booked_date_high,
288 			l_pricing_date_low,
289 			l_pricing_date_high,
290 			l_schedule_ship_date_low,
291 			l_schedule_ship_date_high
292 		from	dual;
293 
294 		IF l_debug_level > 0 THEN
295 			oe_debug_pub.add('After converting Canonical to Date', 5);
296 		END IF;
297 
298 		IF l_debug_level > 0 THEN
299 			oe_debug_pub.add('Fetching data from cursor', 5);
300 		END IF;
301 
302 		IF p_pricing_level = 'ORDER' THEN
303 			/* Fetch the orders matching criteria */
304 			OPEN C_ORDERS;
305 			FETCH C_ORDERS BULK COLLECT INTO l_hdr_lines_tbl;
306 			CLOSE C_ORDERS;
307 		ELSIF p_pricing_level = 'LINE' THEN
308 			/* Fetch the lines matching criteria */
309 			OPEN C_LINES;
310 			FETCH C_LINES BULK COLLECT INTO l_hdr_lines_tbl;
311 			CLOSE C_LINES;
312 		END IF;
313 
314 		IF l_debug_level > 0 THEN
315 			oe_debug_pub.add('No of records fetched : ' || l_hdr_lines_tbl.count, 1);
316 		END IF;
317 
318 		fnd_file.put_line(FND_FILE.OUTPUT, 'No of Order(s) / Line(s) effected : ' || l_hdr_lines_tbl.count);
319 
320 		IF l_hdr_lines_tbl.count > 0 THEN
321 
322 			IF p_preview_mode = 'Y' THEN
323 				fnd_file.put_line(FND_FILE.OUTPUT, '');
324 				fnd_file.put_line(FND_FILE.OUTPUT, '');
325 				fnd_file.put_line(FND_FILE.OUTPUT, '');
326 				fnd_file.put_line(FND_FILE.OUTPUT, 'Following Order(s) / Line(s) will get effected due to this Batch Pricing request :');
327 				fnd_file.put_line(FND_FILE.OUTPUT, '');
328 
329 				IF p_pricing_level = 'ORDER' THEN
330 					l_preview := 	lpad('Order Number', 15, ' ') || '  ' ||
331 							rpad('Order Type', 30, ' ') || '  ' ||
332 							lpad('Org Id', 6, ' ') || '  ' ||
333 							rpad('Customer Name', 35, ' ') || '  ' ||
334 							lpad('Order Amount', 30, ' ');
335 				ELSIF p_pricing_level = 'LINE' THEN
336 					l_preview := 	lpad('Order Number', 15, ' ') || '  ' ||
337 							rpad('Order Type', 30, ' ') || '  ' ||
338 							lpad('Org Id', 6, ' ') || '  ' ||
339 							rpad('Customer Name', 35, ' ') || '  ' ||
340 							rpad('Line Number', 12, ' ') || '  ' ||
341 							lpad('Line Amount', 30, ' ');
342 				END IF;
343 
344 				fnd_file.put_line(FND_FILE.OUTPUT, l_preview);
345 			END IF;
346 
347 			for i in l_hdr_lines_tbl.first .. l_hdr_lines_tbl.last loop
348 
349 				IF p_pricing_level = 'ORDER' THEN
350 					IF p_preview_mode = 'Y' THEN
351 
352 						OE_OE_TOTALS_SUMMARY.Order_Totals
353 									      (
354 									      p_header_id=>l_hdr_lines_tbl(i).header_id,
355 									      p_subtotal =>l_subtotal,
356 									      p_discount =>l_discount,
357 									      p_charges  =>l_charges,
358 									      p_tax      =>l_tax
359 									      );
360 
361 
362 						l_preview := 	lpad(l_hdr_lines_tbl(i).order_number, 15, ' ') || '  ' ||
363 								rpad(l_hdr_lines_tbl(i).order_type, 30, ' ') || '  ' ||
364 								lpad(l_hdr_lines_tbl(i).org_id, 6, ' ') || '  ' ||
365 								rpad(NVL(substr(l_hdr_lines_tbl(i).account_name, 1, 30),'   '), 35, ' ') || '  ' ||
366 								lpad(to_char(l_subtotal + l_charges + l_tax, 'FM999999999999999999D00'), 30, ' ') || ' ' ||
367 								l_hdr_lines_tbl(i).transactional_curr_code;
368 						fnd_file.put_line(FND_FILE.OUTPUT, l_preview);
369 					END IF;
370 
371 					l_submit_request := TRUE;
372 
373 				ELSIF p_pricing_level = 'LINE' THEN
374 
375 					l_lines_count := l_lines_count + 1;
376 
377 					if l_lines_count = 1 then
378 						IF p_preview_mode = 'Y' THEN
379 							l_subtotal := OE_OE_TOTALS_SUMMARY.LINE_TOTAL(
380 										l_hdr_lines_tbl(i).header_id,
381 										l_hdr_lines_tbl(i).line_id,
382 										l_hdr_lines_tbl(i).line_number,
383 										l_hdr_lines_tbl(i).shipment_number
384 										);
385 							l_preview := 	lpad(l_hdr_lines_tbl(i).order_number, 15, ' ') || '  ' ||
386 									rpad(l_hdr_lines_tbl(i).order_type, 30, ' ') || '  ' ||
387 									lpad(l_hdr_lines_tbl(i).org_id, 6, ' ') || '  ' ||
388 									rpad(NVL(substr(l_hdr_lines_tbl(i).account_name, 1, 30),'   '), 35, ' ') || '  ' ||
389 									rpad(OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(l_hdr_lines_tbl(i).line_id), 12, ' ') || '  ' ||
390 									lpad(to_char(l_subtotal, 'FM999999999999999999D00'), 30, ' ') || ' ' ||
391 									l_hdr_lines_tbl(i).transactional_curr_code;
392 							fnd_file.put_line(FND_FILE.OUTPUT, l_preview);
393 						END IF;
394 
395 						l_lines_list := l_hdr_lines_tbl(i).line_id;
396 
397 					else
398 						IF p_preview_mode = 'Y' THEN
399 							l_subtotal := OE_OE_TOTALS_SUMMARY.LINE_TOTAL(
400 										l_hdr_lines_tbl(i).header_id,
401 										l_hdr_lines_tbl(i).line_id,
402 										l_hdr_lines_tbl(i).line_number,
403 										l_hdr_lines_tbl(i).shipment_number
404 										);
405 							l_preview := 	lpad(' ', 15, ' ') || '  ' ||
406 									rpad(' ', 30, ' ') || '  ' ||
407 									lpad(' ', 6, ' ') || '  ' ||
408 									rpad(' ', 35, ' ') || '  ' ||
409 									rpad(OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(l_hdr_lines_tbl(i).line_id), 12, ' ') || '  ' ||
410 									lpad(to_char(l_subtotal, 'FM999999999999999999D00'), 30, ' ') || ' ' ||
411 									l_hdr_lines_tbl(i).transactional_curr_code;
412 							fnd_file.put_line(FND_FILE.OUTPUT, l_preview);
413 						END IF;
414 
415 						l_lines_list := l_lines_list || ',' || l_hdr_lines_tbl(i).line_id;
416 
417 					end if; -- l_lines_count = 1
418 
419 					if i <>  l_hdr_lines_tbl.last then
420 						if l_hdr_lines_tbl(i).header_id <> l_hdr_lines_tbl(i+1).header_id then
421 							l_submit_request := TRUE;
422 						end if;
423 					else
424 						l_submit_request := TRUE;
425 					end if; -- i <>  l_hdr_lines_tbl.last
426 
427 				END IF; -- p_pricing_level
428 
429 				if (l_submit_request) then
430 					/* Submit Child Request only if Preview Mode is No */
431 
432 					IF p_preview_mode <> 'Y' THEN
433 
434 						l_req_data_counter := l_req_data_counter + 1;
435 
436 						l_child_request := FND_REQUEST.SUBMIT_REQUEST('ONT', 'OMBATCHPRICE', 'Batch Pricing Child Request For Order : ' || to_char(l_hdr_lines_tbl(i).order_number) || ' ' || to_char(l_req_data_counter),
437 												NULL, TRUE,
438 												p_preview_mode,			-- p_preview_mode,
439 												p_pricing_level,		-- p_pricing_level,
440 												p_dummy,			-- p_dummy,
441 												l_hdr_lines_tbl(i).org_id,	-- p_org_id,
442 												null,				-- p_order_number_low,
443 												null,				-- p_order_number_high,
444 												null,				-- p_order_type_id,
445 												null,				-- p_line_type_id,
446 												null,				-- p_customer_id,
447 												null,				-- p_ship_to_org_id,
448 												null,				-- p_invoice_to_org_id,
449 												null,				-- p_customer_class_code,
450 												null,				-- p_salesrep_id,
451 												null,				-- p_price_list_id,
452 												null,				-- p_inventory_item_id,
453 												null,				-- p_item_category_id,
454 												null,				-- p_ship_from_org_id,
455 												null,				-- p_order_date_low,
456 												null,				-- p_order_date_high,
457 												null,				-- p_order_creation_date_low,
458 												null,				-- p_order_creation_date_high,
459 												null,				-- p_line_creation_date_low,
460 												null,				-- p_line_creation_date_high,
461 												null,				-- p_booked_date_low,
462 												null,				-- p_booked_date_high,
463 												null,				-- p_pricing_date_low,
464 												null,				-- p_pricing_date_high,
465 												null,				-- p_schedule_ship_date_low,
466 												null,				-- p_schedule_ship_date_high,
467 												null,				-- p_booked_orders,
468 												l_hdr_lines_tbl(i).header_id,	-- p_header_id,
469 												l_lines_count,			-- p_line_count,
470 												l_lines_list			-- p_line_list
471 												);
472 
473 						if l_debug_level > 0 then
474 							oe_debug_pub.add('Submitted Child Request Id : ' || l_child_request || ', for Order : ' || l_hdr_lines_tbl(i).order_number, 1);
475 						end if;
476 
477 						fnd_file.put_line(FND_FILE.OUTPUT, 'Submitted Child Request Id : ' || l_child_request || ', for Order : ' || l_hdr_lines_tbl(i).order_number);
478 
479 					END IF; -- p_preview_mode <> 'Y'
480 
481 					/* Reset Loop Variables */
482 					l_lines_count := 0;
483 					l_lines_list := null;
484 					l_submit_request := FALSE;
485 				end if; -- l_submit_request
486 
487 			end loop; -- PL/SQL Table Loop
488 
489 			-- Set the status of parent request to Paused only if a child request has been submitted.
490 			-- If preview mode is Yes, then child requests are not submitted, hence no need to pause the parent request.
491 			if l_req_data_counter > 0 then
492 				fnd_conc_global.set_req_globals(conc_status  => 'PAUSED', request_data => to_char(l_req_data_counter));
493 			end if;
494 
495 			errbuf  := 'Sub-Request ' || to_char(l_req_data_counter) || 'submitted!';
496 			retcode := 0;
497 
498 			if l_debug_level > 0 then
499 				oe_debug_pub.add('No of child requests submitted : ' || l_req_data_counter, 1);
500 			end if;
501 
502 			fnd_file.put_line(FND_FILE.OUTPUT, '');
503 			fnd_file.put_line(FND_FILE.OUTPUT, 'No of child requests submitted : ' || l_req_data_counter);
504 
505 		END IF; -- IF l_hdr_lines_tbl.count > 0
506 
507 		commit;
508 
509 		IF l_debug_level > 0 THEN
510 			oe_debug_pub.add('Exiting OE_BATCH_PRICING.PRICE', 1);
511 		END IF;
512 	ELSIF p_header_id is not null THEN
513 	/* p_header_id is not null means, this is the child request */
514 
515 		IF l_debug_level > 0 THEN
516 			oe_debug_pub.add('Entering OE_BATCH_PRICING.PRICE FOR CHILD_REQUEST', 1);
517 		END IF;
518 
519 		ERRBUF  := 'Batch Pricing Child Request completed successfully';
520 		RETCODE := 0;
521 
522 		IF l_debug_level > 0 THEN
523 			oe_debug_pub.add('Fetching current user/resp context', 5);
524 		END IF;
525 
526 		FND_PROFILE.Get('USER_ID', l_user_id);
527 		FND_PROFILE.Get('RESP_ID', l_resp_id);
528 		FND_PROFILE.Get('RESP_APPL_ID', l_resp_appl_id);
529 
530 		IF l_debug_level > 0 THEN
531 			oe_debug_pub.add('... Request ID : ' || l_request_id || '; USER_ID : ' || l_user_id || '; RESP_ID : ' || l_resp_id || '; APPL_ID : ' || l_resp_appl_id, 5);
532 		END IF;
533 
534 		fnd_global.apps_initialize(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);
535 
536 		IF p_org_id IS NOT NULL THEN
537 			MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id  => p_org_id);
538 		END IF;
539 
540 		IF l_debug_level > 0 THEN
541 			oe_debug_pub.add('Before calling OE_ORDER_ADJ_PVT.Price_Action', 1);
542 			oe_debug_pub.add('...Pricing Level : ' || p_pricing_level, 5);
543 			oe_debug_pub.add('...Operating Unit : ' || p_org_id, 5);
544 			oe_debug_pub.add('...Header Id : ' || p_header_id, 5);
545 			oe_debug_pub.add('...Lines Count :' || p_line_count || '; List : ' || p_line_list, 5);
546 		END IF;
547 
548 		IF p_pricing_level = 'ORDER' THEN
549 			OE_ORDER_ADJ_PVT.price_action
550 			(
551 				p_header_count          =>      1,
552 				p_header_list           =>      p_header_id,
553 				p_line_count            =>      0,
554 				p_line_list             =>      NULL,
555 				p_price_level           =>      p_pricing_level,
556 				x_return_status         =>      l_return_status,
557 				x_msg_count             =>      l_msg_count,
558 				x_msg_data              =>      l_msg_data
559 			);
560 		ELSIF p_pricing_level = 'LINE' THEN
561 			OE_ORDER_ADJ_PVT.price_action
562 			(
563 				p_header_count          =>      0,
564 				p_header_list           =>      null,
565 				p_line_count            =>      p_line_count,
566 				p_line_list             =>      p_line_list,
567 				p_price_level           =>      p_pricing_level,
568 				x_return_status         =>      l_return_status,
569 				x_msg_count             =>      l_msg_count,
570 				x_msg_data              =>      l_msg_data
571 			);
572 		END IF; -- p_pricing_level
573 
574 		IF l_debug_level > 0 THEN
575 			oe_debug_pub.add('After calling OE_ORDER_ADJ_PVT.Price_Action; Status : ' || l_return_status, 1);
576 		END IF;
577 
578 		IF l_return_status = 'S' THEN
579 			commit;
580 		ELSE
581 			rollback;
582 		END IF;
583 
584 		IF l_debug_level > 0 THEN
585 			oe_debug_pub.add('Exiting OE_BATCH_PRICING.PRICE FOR CHILD_REQUEST', 1);
586 		END IF;
587 
588 	END IF;	-- Child Request
589 Exception
590 When OTHERS Then
591 	oe_debug_pub.add('Others error in OE_BATCH_PRICING.PRICE : ' || SQLERRM, 1);
592 	rollback;
593 
594 END PRICE;
595 
596 END OE_BATCH_PRICING;