1 PACKAGE BODY OE_SCH_FIRM_CONC AS
2 /* $Header: OEXCFDPB.pls 120.4 2006/02/07 22:08:13 rmoharan noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_SCH_FIRM_CONC';
7
8 Function Firm_Eligible(p_line_id IN NUMBER)
9 RETURN BOOLEAN
10 IS
11 l_activity_status_code VARCHAR2(8);
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 --
15 BEGIN
16
17 -- Check for workflow status to be Purchase Release Eligible
18 SELECT ACTIVITY_STATUS
19 INTO l_activity_status_code
20 FROM wf_item_activity_statuses wias, wf_process_activities wpa
21 WHERE wias.item_type = 'OEOL' AND
22 wias.item_key = to_char(p_line_id) AND
23 wias.process_activity = wpa.instance_id AND
24 wpa.activity_name = 'FIRM_ELIGIBLE' AND
25 wias.activity_status = 'NOTIFIED';
26
27 -- Return true since the record exists.
28 RETURN TRUE;
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 IF l_debug_level > 0 THEN
33 oe_debug_pub.add( 'RETURNING FALSE 1 ' , 1 ) ;
34 END IF;
35 RETURN FALSE;
36 WHEN OTHERS THEN
37 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
38 END Firm_Eligible;
39
40 /*-----------------------------------------------------------------
41 PROCEDURE : Request
42 DESCRIPTION: Firm Demand Process Concurrent Request
43 -----------------------------------------------------------------*/
44
45 Procedure Request
46 (ERRBUF OUT NOCOPY VARCHAR2,
47 RETCODE OUT NOCOPY VARCHAR2,
48 -- Moac
49 p_org_id IN NUMBER,
50 p_order_number_low IN NUMBER,
51 p_order_number_high IN NUMBER,
52 p_customer_id IN VARCHAR2,
53 p_order_type IN VARCHAR2,
54 p_line_type_id IN VARCHAR2,
55 p_warehouse IN VARCHAR2,
56 p_inventory_item_id IN VARCHAR2,
57 p_request_date_low IN VARCHAR2,
58 p_request_date_high IN VARCHAR2,
59 p_schedule_ship_date_low IN VARCHAR2,
60 p_schedule_ship_date_high IN VARCHAR2,
61 p_schedule_arrival_date_low IN VARCHAR2,
62 p_schedule_arrival_date_high IN VARCHAR2,
63 p_ordered_date_low IN VARCHAR2,
64 p_ordered_date_high IN VARCHAR2,
65 p_demand_class_code IN VARCHAR2,
66 p_planning_priority IN NUMBER,
67 p_shipment_priority IN VARCHAR2,
68 p_schedule_status IN VARCHAR2
69 )IS
70
71 l_msg_count NUMBER;
72 l_msg_data VARCHAR2(2000) := NULL;
73
74 -- variable for debugging.
75 l_file_val VARCHAR2(80);
76
77
78 -- Moac Changed below cursor to join to oe_order_lines table
79 CURSOR wf_item IS
80 Select item_key, l.org_id
81 From wf_item_activity_statuses wias, wf_process_activities wpa,
82 oe_order_lines l
83 Where wias.item_type = 'OEOL'
84 And wias.process_activity = wpa.instance_id
85 And wpa.activity_item_type = 'OEOL'
86 And wpa.activity_name = 'FIRM_ELIGIBLE'
87 And wias.activity_status = 'NOTIFIED'
88 And wias.item_key = l.line_id
89 order by l.org_id;
90
91 --
92 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
93 --
94
95 l_request_date_low DATE;
96 l_request_date_high DATE;
97 l_schedule_ship_date_low DATE;
98 l_schedule_ship_date_high DATE;
99 l_schedule_arrival_date_low DATE;
100 l_schedule_arrival_date_high DATE;
101 l_ordered_date_low DATE;
102 l_ordered_date_high DATE;
103
104 v_line_id NUMBER;
105 l_sql_stmt VARCHAR2(20900);
106 l_sqlCursor INTEGER;
107 l_dummy NUMBER;
108
109 -- Moac
110 l_single_org BOOLEAN := FALSE;
111 l_old_org_id NUMBER := -99;
112 l_org_id NUMBER;
113
114 BEGIN
115
116 -- When user does not specifiy any parameters, we drive the scheduling
117 -- through workflow. Pick up all the lines which are schedule eligible
118 -- and notified status, call wf_engine to complete the activity.
119
120 -- If value is passed through any of the parameters, then get the header
121 -- and line
122 -- records and call wf_engine.
123
124 oe_debug_pub.add('Starting Progress Firm: ' , 1 ) ;
125
126 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL > '110509' THEN
127 IF p_order_number_low is null AND
128 p_order_number_high is null AND
129 p_customer_id is null AND
130 p_order_type is null AND
131 p_line_type_id is null AND
132 p_warehouse is null AND
133 p_inventory_item_id is null AND
134 p_request_date_low is null AND
135 p_request_date_high is null AND
136 p_schedule_ship_date_low is null AND
137 p_schedule_ship_date_high is null AND
138 p_schedule_arrival_date_low is null AND
139 p_schedule_arrival_date_high is null AND
140 p_ordered_date_low is null AND
141 p_ordered_date_high is null AND
142 p_demand_class_code is null AND
143 p_planning_priority is null AND
144 p_shipment_priority is null AND
145 p_schedule_status is null THEN
146
147 -- MOAC Start
148 IF MO_GLOBAL.get_access_mode = 'S' THEN
149 l_single_org := TRUE;
150 ELSIF p_org_id IS NOT NULL THEN
151 l_single_org := TRUE;
152 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => p_org_id);
153 END IF;
154 -- MOAC End
155
156 FOR k IN wf_item LOOP
157
158 fnd_file.put_line(FND_FILE.LOG, '***** Processing item key '||
159 k.item_key||' *****');
160
161 -- MOAC Start. Set policy context if the OU changes on lines.
162 IF NOT l_single_org and k.org_id <> l_old_org_id then
163 l_old_org_id := k.org_id;
164 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => k.org_id);
165 END IF;
166 -- MOAC End.
167
168 IF l_debug_level > 0 THEN
169 oe_debug_pub.add( 'COMPLETING ACTIVITY FOR : ' || K.ITEM_KEY ,1);
170 END IF;
171 wf_engine.CompleteActivityInternalName
172 ('OEOL',
173 k.item_key,
174 'FIRM_ELIGIBLE',
175 'COMPLETE');
176
177 OE_MSG_PUB.Count_And_Get
178 ( p_count => l_msg_count
179 , p_data => l_msg_data
180 );
181
182
183 FOR I in 1..l_msg_count LOOP
184 l_msg_data := OE_MSG_PUB.Get(I,'F');
185 -- Write Messages in the log file
186 fnd_file.put_line(FND_FILE.LOG, l_msg_data);
187 -- Write the message to the database
188
189 END LOOP;
190
191
192 END LOOP;
193
194
195 ELSE -- If some value is passed then derive based on the header_cur.
196
197
198 IF l_debug_level > 0 THEN
199 OE_DEBUG_PUB.Add('Inside the Firm Demand Concurrent Program',1);
200 END IF;
201
202 SELECT FND_DATE.Canonical_To_Date(p_request_date_low),
203 FND_DATE.Canonical_To_Date(p_request_date_high),
204 FND_DATE.Canonical_To_Date(p_schedule_ship_date_low),
205 FND_DATE.Canonical_To_Date(p_schedule_ship_date_high),
206 FND_DATE.Canonical_To_Date(p_schedule_arrival_date_low),
207 FND_DATE.Canonical_To_Date(p_schedule_arrival_date_high),
208 FND_DATE.Canonical_To_Date(p_ordered_date_low),
209 FND_DATE.Canonical_To_Date(p_ordered_date_high)
210 INTO l_request_date_low,
211 l_request_date_high,
212 l_schedule_ship_date_low,
213 l_schedule_ship_date_high,
214 l_schedule_arrival_date_low,
215 l_schedule_arrival_date_high,
216 l_ordered_date_low,
217 l_ordered_date_high
218 FROM DUAL;
219
220
221 l_sql_stmt := 'SELECT Line_id, l.org_id FROM OE_ORDER_LINES l, OE_ORDER_HEADERS_ALL h ';
222
223 l_sql_stmt := l_sql_stmt|| ' WHERE h.header_id = l.header_id'||
224 ' AND h.open_flag = '||'''Y'''||
225 ' AND NVL(l.cancelled_flag,'||'''N'''||') <> '||'''Y'''||
226 ' AND NVL(l.line_category_code,'||'''ORDER'''||') <> '||'''RETURN''' ;
227
228 IF nvl(p_schedule_status,'ALL') = 'SCHEDULED' THEN
229
230 l_sql_stmt := l_sql_stmt || ' AND l.schedule_status_code is not null';
231
232 ELSIF nvl(p_schedule_status,'ALL') = 'UNSCHEDULED' THEN
233
234 l_sql_stmt := l_sql_stmt || ' AND l.schedule_status_code is null ' ;
235
236 END IF;
237
238 -- Moac Start
239 IF p_org_id is not null THEN
240 l_sql_stmt := l_sql_stmt || ' AND h.org_id = :bindvar_org_id ';
241 END IF;
242 -- Moac End
243
244 IF p_order_number_low is not null THEN
245 l_sql_stmt := l_sql_stmt || ' AND h.ORDER_NUMBER >= :p1 ';
246 END IF;
247 IF p_order_number_high is not null THEN
248 l_sql_stmt := l_sql_stmt || ' AND h.ORDER_NUMBER <= :p2 ';
249 END IF;
250 IF p_customer_id is not null THEN
251 l_sql_stmt := l_sql_stmt || ' AND h.sold_to_org_id = :p3 ';
252 END IF;
253 IF p_order_type is not null THEN
254 l_sql_stmt := l_sql_stmt || ' AND h.order_type_id = :p4 ';
255 END IF;
256 IF p_line_type_id is not null THEN
257 l_sql_stmt := l_sql_stmt || ' AND l.line_type_id = :p5 ';
258 END IF;
259 IF p_warehouse is not null THEN
260 l_sql_stmt := l_sql_stmt || ' AND l.ship_from_org_id = :p6 ';
261 END IF;
262 IF p_inventory_item_id is not null THEN
263 l_sql_stmt := l_sql_stmt || ' AND l.inventory_item_id = :p7 ';
264 END IF;
265 IF p_request_date_low is not null THEN
266 l_sql_stmt := l_sql_stmt || ' AND l.request_date >= :p8 ';
267 END IF;
268 IF p_request_date_high is not null THEN
269 l_sql_stmt := l_sql_stmt || ' AND l.request_date <= :p9 ';
270 END IF;
271 IF p_schedule_ship_date_low is not null THEN
272 l_sql_stmt := l_sql_stmt || ' AND l.schedule_ship_date >= :p10 ';
273 END IF;
274 IF p_schedule_ship_date_high is not null THEN
275 l_sql_stmt := l_sql_stmt || ' AND l.schedule_ship_date <= :p11 ';
276 END IF;
277 IF p_schedule_arrival_date_low is not null THEN
278 l_sql_stmt := l_sql_stmt || ' AND l.schedule_arrival_date >= :p12 ';
279 END IF;
280 IF p_schedule_arrival_date_high is not null THEN
281 l_sql_stmt := l_sql_stmt || ' AND l.schedule_arrival_date <= :p13 ';
282 END IF;
283 IF p_ordered_date_low is not null THEN
284 l_sql_stmt := l_sql_stmt || ' AND h.ordered_date >= :p14 ';
285 END IF;
286 IF p_ordered_date_high is not null THEN
287 l_sql_stmt := l_sql_stmt || ' AND h.ordered_date <= :p15 ';
288 END IF;
289 IF p_demand_class_code is not null THEN
290 l_sql_stmt := l_sql_stmt || ' AND l.demand_class_code = :p16 ';
291 END IF;
292 IF p_planning_priority is not null THEN
293 l_sql_stmt := l_sql_stmt || ' AND l.planning_priority = :p17 ';
294 END IF;
295 IF p_shipment_priority is not null THEN
296 l_sql_stmt := l_sql_stmt || ' AND l.shipment_priority_code = :p18 ';
297 END IF;
298
299 -- Moac Start
300 IF NOT l_single_org THEN
301 l_sql_stmt := l_sql_stmt|| ' Order By h.org_id ';
302 End IF;
303 -- Moac End
304
305 oe_debug_pub.add (l_sql_stmt,1);
306 l_sqlCursor := DBMS_SQL.Open_Cursor;
307
308 DBMS_SQL.PARSE(l_sqlCursor, l_sql_stmt, DBMS_SQL.NATIVE);
309
310 -- Moac Start
311 IF p_org_id IS NOT NULL THEN
312 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':bindvar_org_id',p_org_id);
313 END IF;
314 -- Moac End
315
316 IF p_order_number_low IS NOT NULL THEN
317 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p1',p_order_number_low);
318 END IF;
319 IF p_order_number_high is not null THEN
320 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p2',p_order_number_high);
321 END IF;
322 IF p_customer_id is not null THEN
323 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p3',p_customer_id);
324 END IF;
325 IF p_order_type is not null THEN
326 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p4',p_order_type);
327 END IF;
328 IF p_line_type_id is not null THEN
329 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p5',p_line_type_id);
330 END IF;
331 IF p_warehouse is not null THEN
332 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p6',p_warehouse);
333 END IF;
334 IF p_inventory_item_id is not null THEN
335 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p7',p_inventory_item_id);
336 END IF;
337 IF p_request_date_low is not null THEN
338 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p8',l_request_date_low);
339 END IF;
340 IF p_request_date_high is not null THEN
341 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p9',l_request_date_high);
342 END IF;
343 IF p_schedule_ship_date_low is not null THEN
344 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p10',l_schedule_ship_date_low);
345 END IF;
346 IF p_schedule_ship_date_high is not null THEN
347 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p11',l_schedule_ship_date_high);
348 END IF;
349 IF p_schedule_arrival_date_low is not null THEN
350 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p12',l_schedule_arrival_date_low);
351 END IF;
352 IF p_schedule_arrival_date_high is not null THEN
353 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p13',l_schedule_arrival_date_high);
354 END IF;
355 IF p_ordered_date_low is not null THEN
356 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p14',l_ordered_date_low);
357 END IF;
358 IF p_ordered_date_high is not null THEN
359 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p15',l_ordered_date_high);
360 END IF;
361 IF p_demand_class_code is not null THEN
362 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p16',p_demand_class_code);
363 END IF;
364 IF p_planning_priority is not null THEN
365 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p17',p_planning_priority);
366 END IF;
367 IF p_shipment_priority is not null THEN
368 DBMS_SQL.BIND_VARIABLE(l_sqlCursor,':p18',p_shipment_priority);
369 END IF;
370
371 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,1,v_line_id);
372 DBMS_SQL.DEFINE_COLUMN (l_sqlCursor,2,l_org_id);
373
374 l_dummy := DBMS_SQL.execute(l_sqlCursor);
375
376 LOOP
377
378 IF DBMS_SQL.FETCH_ROWS(l_sqlCursor) = 0 THEN
379 EXIT;
380 END IF;
381
382 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,1,v_line_id);
383 DBMS_SQL.COLUMN_VALUE(l_sqlCursor,2,l_org_id);
384
385 IF Firm_Eligible(p_line_id => v_line_id) THEN
386
387 fnd_file.put_line(FND_FILE.LOG, '***** Processing Line id '||
388 v_line_id||' *****');
389
390 -- Moac Start
391 IF NOT l_single_org and l_org_id <> l_old_org_id THEN
392 l_old_org_id := l_org_id;
393 MO_GLOBAL.set_policy_context(p_access_mode => 'S', p_org_id => l_org_id);
394 END IF;
395 -- Moac End
396
397 IF l_debug_level > 0 THEN
398 oe_debug_pub.add( 'COMPLETING ACTIVITY FOR : ' || v_line_id ,1);
399 END IF;
400
401 wf_engine.CompleteActivityInternalName
402 ('OEOL',
403 to_char(v_line_id),
404 'FIRM_ELIGIBLE',
405 'COMPLETE');
406
407 OE_MSG_PUB.Count_And_Get
408 ( p_count => l_msg_count
409 , p_data => l_msg_data
410 );
411
412 FOR I in 1..l_msg_count loop
413 l_msg_data := OE_MSG_PUB.Get(I,'F');
414 -- Write Messages in the log file
415 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
416 -- Write the message to the database
417 END LOOP;
418 END IF;
419
420 END LOOP;
421
422 DBMS_SQL.CLOSE_CURSOR(l_sqlCursor);
423
424 END IF; -- Main
425
426 END IF;
427 EXCEPTION
428
429 WHEN OTHERS THEN
430
431 oe_debug_pub.add('Error executing Scheduling ' || SQLERRM,1);
432 fnd_file.put_line(FND_FILE.LOG,
433 'Error executing Scheduling, ' || SQLERRM);
434 END Request;
435
436 END OE_SCH_FIRM_CONC;
[Home] [Help]