[Home] [Help]
PACKAGE BODY: APPS.INV_SHORTSTATEMENT_PVT
Source
1 PACKAGE BODY INV_ShortStatement_PVT AS
2 /* $Header: INVSSTMB.pls 120.2 2006/06/23 00:11:11 stdavid noship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ShortStatement_PVT';
4 -- Start OF comments
5 -- API name : BuildDetail
6 -- TYPE : Private
7 -- Pre-reqs : None
8 -- FUNCTION :
9 -- Parameters:
10 -- IN :
11 -- p_api_version IN NUMBER (required)
12 -- API Version of this procedure
13 --
14 -- p_init_msg_list IN VARCHAR2 (optional)
15 -- DEFAULT = FND_API.G_FALSE,
16 --
17 -- OUT :
18 -- x_return_status OUT NUMBER
19 -- Result of all the operations
20 --
21 -- x_msg_count OUT NUMBER,
22 --
23 -- x_msg_data OUT VARCHAR2,
24 --
25 -- x_short_stat_detail OUT LONG
26 -- Detail shortage statement
27 --
28 -- Version: Current Version 1.0
29 -- Changed : Nothing
30 -- No Previous Version 0.0
31 -- Initial version 1.0
32 -- Notes :
33 -- END OF comments
34 PROCEDURE BuildDetail (
35 p_api_version IN NUMBER ,
36 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
37 x_return_status IN OUT NOCOPY VARCHAR2,
38 x_msg_count IN OUT NOCOPY NUMBER,
39 x_msg_data IN OUT NOCOPY VARCHAR2,
40 p_organization_id IN NUMBER,
41 p_check_wip_flag IN NUMBER,
42 p_check_oe_flag IN NUMBER,
43 p_wip_rel_jobs_flag IN NUMBER,
44 p_wip_days_overdue_rel_jobs IN NUMBER,
45 p_wip_unrel_jobs_flag IN NUMBER,
46 p_wip_days_overdue_unrel_jobs IN NUMBER,
47 p_wip_hold_jobs_flag IN NUMBER,
48 p_wip_rel_rep_flag IN NUMBER,
49 p_wip_days_overdue_rel_rep IN NUMBER,
50 p_wip_unrel_rep_flag IN NUMBER,
51 p_wip_days_overdue_unrel_rep IN NUMBER,
52 p_wip_hold_rep_flag IN NUMBER,
53 p_wip_req_date_jobs_flag IN NUMBER,
54 p_wip_curr_op_jobs_flag IN NUMBER,
55 p_wip_prev_op_jobs_flag IN NUMBER,
56 p_wip_req_date_rep_flag IN NUMBER,
57 p_wip_curr_op_rep_flag IN NUMBER,
58 p_wip_prev_op_rep_flag IN NUMBER,
59 p_wip_excl_bulk_comp_flag IN NUMBER,
60 p_wip_excl_supplier_comp_flag IN NUMBER,
61 p_wip_excl_pull_comp_flag IN NUMBER,
62 x_short_stat_detail OUT NOCOPY LONG
63 )
64 IS
65 L_api_version CONSTANT NUMBER := 1.0;
66 L_api_name CONSTANT VARCHAR2(30) := 'BuildDetail';
67 L_Statement LONG;
68 L_First BOOLEAN := TRUE;
69 L_Operator VARCHAR2(10);
70 L_String VARCHAR2(255);
71 L_Check_jobs NUMBER;
72 L_Check_rep NUMBER;
73 L_Order_System VARCHAR2(10); -- The type of order entry system which is installed.
74 BEGIN
75 -- Standard Call to check for call compatibility
76 IF NOT FND_API.Compatible_API_Call(l_api_version
77 , p_api_version
78 , l_api_name
79 , G_PKG_NAME) THEN
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81 END IF;
82 --
83 -- Initialize message list if p_init_msg_list is set to true
84 IF FND_API.to_Boolean(p_init_msg_list) THEN
85 FND_MSG_PUB.initialize;
86 END IF;
87 --
88 -- Initialize API return status to access
89 x_return_status := FND_API.G_RET_STS_SUCCESS;
90 --
91 -- For WIP see whether jobs and/or schedules should be included
92 IF ( p_wip_rel_jobs_flag = 1 OR p_wip_unrel_jobs_flag = 1 OR
93 p_wip_hold_jobs_flag = 1 ) THEN
94 L_Check_jobs := 1;
95 ELSE
96 L_Check_jobs := 2;
97 END IF;
98 IF ( p_wip_rel_rep_flag = 1 OR p_wip_unrel_rep_flag = 1 OR
99 p_wip_hold_rep_flag = 1 ) THEN
100 L_Check_rep := 1;
101 ELSE
102 L_Check_rep := 2;
103 END IF;
104 --
105 -- Determine whether Order Entry or Order Management is installed.
106 L_Order_System := OE_INSTALL.Get_Active_Product;
107 --
108 L_Statement := 'DECLARE '||FND_GLOBAL.Newline||
109 ' L_Organization_id NUMBER; '||FND_GLOBAL.Newline||
110 ' L_Inventory_item_id NUMBER; '||FND_GLOBAL.Newline||
111 ' L_Seq_num NUMBER; '||FND_GLOBAL.Newline||
112 'BEGIN '||FND_GLOBAL.Newline||
113 ' L_Organization_id := :organization_id; '||FND_GLOBAL.Newline||
114 ' L_Inventory_item_id := :inventory_item_id; '||FND_GLOBAL.Newline||
115 ' L_Seq_num := :seq_num; '||FND_GLOBAL.Newline;
116 IF p_check_wip_flag = 1 AND ( L_Check_jobs = 1 OR L_Check_rep = 1 ) THEN
117 -- build statement
118 -- select clause and general from clause
119 L_Statement := L_Statement||
120 'BEGIN '||FND_GLOBAL.Newline||
121 'INSERT INTO mtl_short_chk_temp '||FND_GLOBAL.Newline||
122 '( seq_num '||FND_GLOBAL.Newline||
123 ' ,organization_id '||FND_GLOBAL.Newline||
124 ' ,inventory_item_id '||FND_GLOBAL.Newline||
125 ' ,quantity_open '||FND_GLOBAL.Newline||
126 ' ,uom_code '||FND_GLOBAL.Newline||
127 ' ,object_type '||FND_GLOBAL.Newline||
128 ' ,object_id '||FND_GLOBAL.Newline||
129 ' ,object_detail_id '||FND_GLOBAL.Newline||
130 ' ,last_updated_by '||FND_GLOBAL.Newline||
131 ' ,last_update_login '||FND_GLOBAL.Newline||
132 ' ,last_update_date '||FND_GLOBAL.Newline||
133 ' ,created_by '||FND_GLOBAL.Newline||
134 ' ,creation_date '||FND_GLOBAL.Newline||
135 ') '||FND_GLOBAL.Newline;
136 --
137 -- Jobs with p_inventory_item_id not null
138 --
139 IF L_Check_jobs = 1 THEN
140 L_Statement := L_Statement||
141 'SELECT '||FND_GLOBAL.Newline||
142 ' L_Seq_num '||FND_GLOBAL.Newline||
143 ',WRO.organization_id '||FND_GLOBAL.Newline||
144 ',WRO.inventory_item_id '||FND_GLOBAL.Newline||
145 ',WRO.required_quantity-WRO.quantity_issued '||FND_GLOBAL.Newline||
146 ',MSI.primary_uom_code '||FND_GLOBAL.Newline||
147 ',1 '||FND_GLOBAL.Newline||
148 ',WRO.wip_entity_id '||FND_GLOBAL.Newline||
149 ',WRO.operation_seq_num '||FND_GLOBAL.Newline||
150 ',0 '||FND_GLOBAL.Newline||
151 ',-1 '||FND_GLOBAL.Newline||
152 ',sysdate '||FND_GLOBAL.Newline||
153 ',0 '||FND_GLOBAL.Newline||
154 ',sysdate '||FND_GLOBAL.Newline||
155 'FROM wip_entities WE'||FND_GLOBAL.Newline||
156 ',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
157 ',mtl_system_items MSI '||FND_GLOBAL.Newline;
158 -- general where clause
159 L_Statement := L_Statement||
160 'WHERE '||FND_GLOBAL.Newline||
161 ' L_Inventory_item_id IS NOT NULL '||FND_GLOBAL.Newline||
162 'AND WRO.inventory_item_id=MSI.inventory_item_id '||FND_GLOBAL.Newline||
163 'AND WRO.organization_id=MSI.organization_id '||FND_GLOBAL.Newline||
164 'AND NVL(MSI.check_shortages_flag,'||''''||'N'||''''||')='||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
165 'AND WRO.wip_entity_id=WE.wip_entity_id '||FND_GLOBAL.Newline||
166 'AND WRO.organization_id=L_Organization_id '||FND_GLOBAL.Newline||
167 'AND WRO.inventory_item_id=L_Inventory_item_id '||FND_GLOBAL.Newline||
168 'AND WRO.repetitive_schedule_id IS NULL '||FND_GLOBAL.Newline||
169 'AND WRO.operation_seq_num>0 '||FND_GLOBAL.Newline||
170 'AND WRO.required_quantity>0 '||FND_GLOBAL.Newline||
171 'AND WRO.quantity_issued>=0 '||FND_GLOBAL.Newline||
172 'AND WRO.required_quantity>WRO.quantity_issued '||FND_GLOBAL.Newline;
173 -- where clause: hold jobs
174 IF p_wip_hold_jobs_flag = 1 THEN
175 IF L_First THEN
176 L_First := FALSE;
177 L_Operator := 'AND ( ';
178 ELSE L_Operator := 'OR ';
179 END IF;
180 L_Statement := L_Statement||L_Operator||
181 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
182 ' AND EXISTS'||FND_GLOBAL.Newline||
183 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
184 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
185 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
186 ' AND WDJ.status_type=6))'||FND_GLOBAL.Newline;
187 END IF;
188 -- where clause: released jobs (for days overdue)
189 IF p_wip_rel_jobs_flag = 1 THEN
190 IF L_First THEN
191 L_First := FALSE;
192 L_Operator := 'AND ( ';
193 ELSE L_Operator := 'OR ';
194 END IF;
195 L_Statement := L_Statement||L_Operator||
196 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
197 ' AND EXISTS'||FND_GLOBAL.Newline||
198 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
199 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
200 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
201 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
202 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
203 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
204 ' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
205 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
206 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
207 ' AND WDJ.status_type=3'||FND_GLOBAL.Newline||
208 ' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
209 ' AND BCD1.calendar_date+NVL('||
210 TO_CHAR(p_wip_days_overdue_rel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
211 ' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
212 END IF;
213 -- where clause: unreleased jobs (for days overdue)
214 IF p_wip_unrel_jobs_flag = 1 THEN
215 IF L_First THEN
216 L_First := FALSE;
217 L_Operator := 'AND ( ';
218 ELSE L_Operator := 'OR ';
219 END IF;
220 L_Statement := L_Statement||L_Operator||
221 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
222 ' AND EXISTS'||FND_GLOBAL.Newline||
223 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
224 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
225 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
226 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
227 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
228 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
229 ' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
230 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
231 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
232 ' AND WDJ.status_type=1'||FND_GLOBAL.Newline||
233 ' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
234 ' AND BCD1.calendar_date+NVL('||
235 TO_CHAR(p_wip_days_overdue_unrel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
236 ' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
237 END IF;
238 IF NOT L_First THEN
239 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
240 L_First := TRUE;
241 END IF;
242 -- where clause: parameter required date
243 IF p_wip_req_date_jobs_flag = 1 THEN
244 IF L_First THEN
245 L_First := FALSE;
246 L_Operator := 'AND ( ';
247 ELSE L_Operator := 'OR ';
248 END IF;
249 L_Statement := L_Statement||L_Operator||
250 ' WRO.date_required<sysdate '||FND_GLOBAL.Newline;
251 END IF;
252 -- where clause: parameter current operation
253 IF p_wip_curr_op_jobs_flag = 1 THEN
254 IF L_First THEN
255 L_First := FALSE;
256 L_Operator := 'AND ( ';
257 ELSE L_Operator := 'OR ';
258 END IF;
259 L_Statement := L_Statement||L_Operator||
260 ' EXISTS'||FND_GLOBAL.Newline||
261 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
262 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
263 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
264 ' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
265 ' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
266 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
267 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
268 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
269 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
270 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
271 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
272 END IF;
273 -- where clause: parameter previous operation
274 IF p_wip_prev_op_jobs_flag = 1 THEN
275 IF L_First THEN
276 L_First := FALSE;
277 L_Operator := 'AND ( ';
278 ELSE L_Operator := 'OR ';
279 END IF;
280 L_Statement := L_Statement||L_Operator||
281 ' EXISTS'||FND_GLOBAL.Newline||
282 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
283 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
284 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
285 ' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
286 ' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
287 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
288 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
289 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
290 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
291 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
292 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
293 END IF;
294 IF NOT L_First THEN
295 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
296 L_First := TRUE;
297 END IF;
298 -- where clause: parameter bulk components
299 IF p_wip_excl_bulk_comp_flag = 1 THEN
300 L_Statement := L_Statement||
301 ' AND WRO.wip_supply_type<>4 '||FND_GLOBAL.Newline;
302 END IF;
303 -- where clause: parameter supplier components
304 IF p_wip_excl_supplier_comp_flag = 1 THEN
305 L_Statement := L_Statement||
306 ' AND WRO.wip_supply_type<>5 '||FND_GLOBAL.Newline;
307 END IF;
308 -- where clause: parameter pull components
309 IF p_wip_excl_pull_comp_flag = 1 THEN
310 L_Statement := L_Statement||
311 ' AND WRO.wip_supply_type NOT IN (2,3) '||FND_GLOBAL.Newline;
312 END IF;
313 END IF;
314 --
315 -- Jobs with p_inventory_item_id null
316 --
317 IF L_Check_jobs = 1 THEN
318 L_Statement := L_Statement||
319 'UNION ALL'||FND_GLOBAL.Newline||
320 'SELECT '||FND_GLOBAL.Newline||
321 ' L_Seq_num '||FND_GLOBAL.Newline||
322 ',WRO.organization_id '||FND_GLOBAL.Newline||
323 ',WRO.inventory_item_id '||FND_GLOBAL.Newline||
324 ',WRO.required_quantity-WRO.quantity_issued '||FND_GLOBAL.Newline||
325 ',MSI.primary_uom_code '||FND_GLOBAL.Newline||
326 ',1 '||FND_GLOBAL.Newline||
327 ',WRO.wip_entity_id '||FND_GLOBAL.Newline||
328 ',WRO.operation_seq_num '||FND_GLOBAL.Newline||
329 ',0 '||FND_GLOBAL.Newline||
330 ',-1 '||FND_GLOBAL.Newline||
331 ',sysdate '||FND_GLOBAL.Newline||
332 ',0 '||FND_GLOBAL.Newline||
333 ',sysdate '||FND_GLOBAL.Newline||
334 'FROM wip_entities WE'||FND_GLOBAL.Newline||
335 ',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
336 ',mtl_system_items MSI '||FND_GLOBAL.Newline;
337 -- general where clause
338 L_Statement := L_Statement||
339 'WHERE '||FND_GLOBAL.Newline||
340 ' L_Inventory_item_id IS NULL '||FND_GLOBAL.Newline||
341 'AND WRO.inventory_item_id=MSI.inventory_item_id '||FND_GLOBAL.Newline||
342 'AND WRO.organization_id=MSI.organization_id '||FND_GLOBAL.Newline||
343 'AND NVL(MSI.check_shortages_flag,'||''''||'N'||''''||')='||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
344 'AND WRO.wip_entity_id=WE.wip_entity_id '||FND_GLOBAL.Newline||
345 'AND WRO.organization_id=L_Organization_id '||FND_GLOBAL.Newline||
346 'AND WRO.repetitive_schedule_id IS NULL '||FND_GLOBAL.Newline||
347 'AND WRO.operation_seq_num>0 '||FND_GLOBAL.Newline||
348 'AND WRO.required_quantity>0 '||FND_GLOBAL.Newline||
349 'AND WRO.quantity_issued>=0 '||FND_GLOBAL.Newline||
350 'AND WRO.required_quantity>WRO.quantity_issued '||FND_GLOBAL.Newline;
351 -- where clause: hold jobs
352 IF p_wip_hold_jobs_flag = 1 THEN
353 IF L_First THEN
354 L_First := FALSE;
358 L_Statement := L_Statement||L_Operator||
355 L_Operator := 'AND ( ';
356 ELSE L_Operator := 'OR ';
357 END IF;
359 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
360 ' AND EXISTS'||FND_GLOBAL.Newline||
361 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
362 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
363 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
364 ' AND WDJ.status_type=6))'||FND_GLOBAL.Newline;
365 END IF;
366 -- where clause: released jobs (for days overdue)
367 IF p_wip_rel_jobs_flag = 1 THEN
368 IF L_First THEN
369 L_First := FALSE;
370 L_Operator := 'AND ( ';
371 ELSE L_Operator := 'OR ';
372 END IF;
373 L_Statement := L_Statement||L_Operator||
374 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
375 ' AND EXISTS'||FND_GLOBAL.Newline||
376 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
377 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
378 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
379 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
380 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
381 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
382 ' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
383 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
384 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
385 ' AND WDJ.status_type=3'||FND_GLOBAL.Newline||
386 ' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
387 ' AND BCD1.calendar_date+NVL('||
388 TO_CHAR(p_wip_days_overdue_rel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
389 ' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
390 END IF;
391 -- where clause: unreleased jobs (for days overdue)
392 IF p_wip_unrel_jobs_flag = 1 THEN
393 IF L_First THEN
394 L_First := FALSE;
395 L_Operator := 'AND ( ';
396 ELSE L_Operator := 'OR ';
397 END IF;
398 L_Statement := L_Statement||L_Operator||
399 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
400 ' AND EXISTS'||FND_GLOBAL.Newline||
401 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
402 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
403 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
404 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
405 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
406 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
407 ' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
408 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
409 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
410 ' AND WDJ.status_type=1'||FND_GLOBAL.Newline||
411 ' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
412 ' AND BCD1.calendar_date+NVL('||
413 TO_CHAR(p_wip_days_overdue_unrel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
414 ' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
415 END IF;
416 IF NOT L_First THEN
417 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
418 L_First := TRUE;
419 END IF;
420 -- where clause: parameter required date
421 IF p_wip_req_date_jobs_flag = 1 THEN
422 IF L_First THEN
423 L_First := FALSE;
424 L_Operator := 'AND ( ';
425 ELSE L_Operator := 'OR ';
426 END IF;
427 L_Statement := L_Statement||L_Operator||
428 ' WRO.date_required<sysdate '||FND_GLOBAL.Newline;
429 END IF;
430 -- where clause: parameter current operation
431 IF p_wip_curr_op_jobs_flag = 1 THEN
432 IF L_First THEN
433 L_First := FALSE;
434 L_Operator := 'AND ( ';
435 ELSE L_Operator := 'OR ';
436 END IF;
437 L_Statement := L_Statement||L_Operator||
438 ' EXISTS'||FND_GLOBAL.Newline||
439 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
440 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
441 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
442 ' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
443 ' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
444 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
445 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
446 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
447 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
448 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
449 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
450 END IF;
451 -- where clause: parameter previous operation
452 IF p_wip_prev_op_jobs_flag = 1 THEN
453 IF L_First THEN
454 L_First := FALSE;
455 L_Operator := 'AND ( ';
456 ELSE L_Operator := 'OR ';
457 END IF;
458 L_Statement := L_Statement||L_Operator||
459 ' EXISTS'||FND_GLOBAL.Newline||
460 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
461 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
462 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
463 ' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
464 ' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
465 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
466 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
470 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
467 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
468 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
469 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
471 END IF;
472 IF NOT L_First THEN
473 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
474 L_First := TRUE;
475 END IF;
476 -- where clause: parameter bulk components
477 IF p_wip_excl_bulk_comp_flag = 1 THEN
478 L_Statement := L_Statement||
479 ' AND WRO.wip_supply_type<>4 '||FND_GLOBAL.Newline;
480 END IF;
481 -- where clause: parameter supplier components
482 IF p_wip_excl_supplier_comp_flag = 1 THEN
483 L_Statement := L_Statement||
484 ' AND WRO.wip_supply_type<>5 '||FND_GLOBAL.Newline;
485 END IF;
486 -- where clause: parameter pull components
487 IF p_wip_excl_pull_comp_flag = 1 THEN
488 L_Statement := L_Statement||
489 ' AND WRO.wip_supply_type NOT IN (2,3) '||FND_GLOBAL.Newline;
490 END IF;
491 END IF;
492 --
493 -- Schedules with p_inventory_item_id not null
494 --
495 IF L_Check_rep = 1 AND L_Check_jobs = 1 THEN
496 L_Statement := L_Statement||
497 'UNION ALL '||FND_GLOBAL.Newline;
498 END IF;
499 IF L_Check_rep = 1 THEN
500 L_Statement := L_Statement||
501 'SELECT '||FND_GLOBAL.Newline||
502 ' L_Seq_num '||FND_GLOBAL.Newline||
503 ',WRO.organization_id '||FND_GLOBAL.Newline||
504 ',WRO.inventory_item_id '||FND_GLOBAL.Newline||
505 ',INV_ShortCheckExec_PVT.get_rep_curr_open_qty '||FND_GLOBAL.Newline||
506 ' ( WRO.organization_id '||FND_GLOBAL.Newline||
507 ' , WRS.wip_entity_id '||FND_GLOBAL.Newline||
508 ' , WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
509 ' , WRS.first_unit_start_date '||FND_GLOBAL.Newline||
510 ' , WRS.processing_work_days '||FND_GLOBAL.Newline||
511 ' , WRO.operation_seq_num '||FND_GLOBAL.Newline||
512 ' , WRO.inventory_item_id '||FND_GLOBAL.Newline||
513 ' , WRO.quantity_issued '||FND_GLOBAL.Newline||
514 ' ) '||FND_GLOBAL.Newline||
515 ',MSI.primary_uom_code '||FND_GLOBAL.Newline||
516 ',2 '||FND_GLOBAL.Newline||
517 ',WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
518 ',WRO.operation_seq_num '||FND_GLOBAL.Newline||
519 ',0 '||FND_GLOBAL.Newline||
520 ',-1 '||FND_GLOBAL.Newline||
521 ',sysdate '||FND_GLOBAL.Newline||
522 ',0 '||FND_GLOBAL.Newline||
523 ',sysdate '||FND_GLOBAL.Newline||
524 'FROM wip_repetitive_schedules WRS'||FND_GLOBAL.Newline||
525 ',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
526 ',mtl_system_items MSI '||FND_GLOBAL.Newline;
527 -- general where clause
528 L_Statement := L_Statement||
529 'WHERE '||FND_GLOBAL.Newline||
530 ' L_Inventory_item_id IS NOT NULL '||FND_GLOBAL.Newline||
531 'AND WRO.inventory_item_id=MSI.inventory_item_id '||FND_GLOBAL.Newline||
532 'AND WRO.organization_id=MSI.organization_id '||FND_GLOBAL.Newline||
533 'AND NVL(MSI.check_shortages_flag,'||''''||'N'||''''||')='||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
534 'AND WRO.repetitive_schedule_id=WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
535 'AND WRO.organization_id=L_Organization_id '||FND_GLOBAL.Newline||
536 'AND WRO.inventory_item_id=L_Inventory_item_id '||FND_GLOBAL.Newline||
537 'AND WRO.operation_seq_num>0 '||FND_GLOBAL.Newline||
538 'AND WRO.required_quantity>0 '||FND_GLOBAL.Newline||
539 'AND WRO.quantity_issued>=0 '||FND_GLOBAL.Newline||
540 'AND WRO.required_quantity>WRO.quantity_issued '||FND_GLOBAL.Newline;
541 -- where clause: hold schedules
542 IF p_wip_hold_rep_flag = 1 THEN
543 IF L_First THEN
544 L_First := FALSE;
545 L_Operator := 'AND ( ';
546 ELSE L_Operator := 'OR ';
547 END IF;
548 L_Statement := L_Statement||L_Operator||
549 ' WRS.status_type=6 '||FND_GLOBAL.Newline;
550 END IF;
551 -- where clause: released schedules (for days overdue)
552 IF p_wip_rel_rep_flag = 1 THEN
553 IF L_First THEN
554 L_First := FALSE;
555 L_Operator := 'AND ( ';
556 ELSE L_Operator := 'OR ';
557 END IF;
558 L_Statement := L_Statement||L_Operator||
559 ' EXISTS'||FND_GLOBAL.Newline||
560 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
561 ' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
562 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
563 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
564 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
565 ' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
566 ' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
567 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
568 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
569 ' AND WRS2.status_type=3'||FND_GLOBAL.Newline||
570 ' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
571 ' AND BCD1.calendar_date+NVL('||
572 TO_CHAR(p_wip_days_overdue_rel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
576 IF p_wip_unrel_rep_flag = 1 THEN
573 ' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
574 END IF;
575 -- where clause: unreleased schedules (for days overdue)
577 IF L_First THEN
578 L_First := FALSE;
579 L_Operator := 'AND ( ';
580 ELSE L_Operator := 'OR ';
581 END IF;
582 L_Statement := L_Statement||L_Operator||
583 ' EXISTS'||FND_GLOBAL.Newline||
584 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
585 ' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
586 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
587 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
588 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
589 ' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
590 ' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
591 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
592 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
593 ' AND WRS2.status_type=1'||FND_GLOBAL.Newline||
594 ' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
595 ' AND BCD1.calendar_date+NVL('||
596 TO_CHAR(p_wip_days_overdue_unrel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
597 ' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
598 END IF;
599 IF NOT L_First THEN
600 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
601 L_First := TRUE;
602 END IF;
603 -- where clause: parameter required date
604 IF p_wip_req_date_rep_flag = 1 THEN
605 IF L_First THEN
606 L_First := FALSE;
607 L_Operator := 'AND ( ';
608 ELSE L_Operator := 'OR ';
609 END IF;
610 L_Statement := L_Statement||L_Operator||
611 ' WRO.date_required<sysdate '||FND_GLOBAL.Newline;
612 END IF;
613 -- where clause: parameter current operation
614 IF p_wip_curr_op_rep_flag = 1 THEN
615 IF L_First THEN
616 L_First := FALSE;
617 L_Operator := 'AND ( ';
618 ELSE L_Operator := 'OR ';
619 END IF;
620 L_Statement := L_Statement||L_Operator||
621 ' EXISTS'||FND_GLOBAL.Newline||
622 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
623 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
624 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
625 ' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
626 ' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
627 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
628 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
629 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
630 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
631 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
632 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
633 END IF;
634 -- where clause: parameter previous operation
635 IF p_wip_prev_op_rep_flag = 1 THEN
636 IF L_First THEN
637 L_First := FALSE;
638 L_Operator := 'AND ( ';
639 ELSE L_Operator := 'OR ';
640 END IF;
641 L_Statement := L_Statement||L_Operator||
642 ' EXISTS'||FND_GLOBAL.Newline||
643 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
644 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
645 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
646 ' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
647 ' AND WO.repetitive_schedule_id IS NULL '||FND_GLOBAL.Newline||
648 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
649 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
650 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
651 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
652 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
653 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
654 END IF;
655 IF NOT L_First THEN
656 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
657 L_First := TRUE;
658 END IF;
659 -- where clause: parameter bulk components
660 IF p_wip_excl_bulk_comp_flag = 1 THEN
661 L_Statement := L_Statement||
662 ' AND WRO.wip_supply_type<>4 '||FND_GLOBAL.Newline;
663 END IF;
664 -- where clause: parameter supplier components
665 IF p_wip_excl_supplier_comp_flag = 1 THEN
666 L_Statement := L_Statement||
667 ' AND WRO.wip_supply_type<>5 '||FND_GLOBAL.Newline;
668 END IF;
669 -- where clause: parameter pull components
670 IF p_wip_excl_pull_comp_flag = 1 THEN
671 L_Statement := L_Statement||
672 ' AND WRO.wip_supply_type NOT IN (2,3) '||FND_GLOBAL.Newline;
673 END IF;
674 END IF;
675 --
676 -- Schedules with p_inventory_item_id null
677 --
678 IF L_Check_rep = 1 THEN
679 L_Statement := L_Statement||
680 'UNION ALL'||FND_GLOBAL.Newline||
681 'SELECT '||FND_GLOBAL.Newline||
682 ' L_Seq_num '||FND_GLOBAL.Newline||
683 ',WRO.organization_id '||FND_GLOBAL.Newline||
684 ',WRO.inventory_item_id '||FND_GLOBAL.Newline||
685 ',INV_ShortCheckExec_PVT.get_rep_curr_open_qty '||FND_GLOBAL.Newline||
686 ' ( WRO.organization_id '||FND_GLOBAL.Newline||
690 ' , WRS.processing_work_days '||FND_GLOBAL.Newline||
687 ' , WRS.wip_entity_id '||FND_GLOBAL.Newline||
688 ' , WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
689 ' , WRS.first_unit_start_date '||FND_GLOBAL.Newline||
691 ' , WRO.operation_seq_num '||FND_GLOBAL.Newline||
692 ' , WRO.inventory_item_id '||FND_GLOBAL.Newline||
693 ' , WRO.quantity_issued '||FND_GLOBAL.Newline||
694 ' ) '||FND_GLOBAL.Newline||
695 ',MSI.primary_uom_code '||FND_GLOBAL.Newline||
696 ',2 '||FND_GLOBAL.Newline||
697 ',WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
698 ',WRO.operation_seq_num '||FND_GLOBAL.Newline||
699 ',0 '||FND_GLOBAL.Newline||
700 ',-1 '||FND_GLOBAL.Newline||
701 ',sysdate '||FND_GLOBAL.Newline||
702 ',0 '||FND_GLOBAL.Newline||
703 ',sysdate '||FND_GLOBAL.Newline||
704 'FROM wip_repetitive_schedules WRS'||FND_GLOBAL.Newline||
705 ',wip_requirement_operations WRO '||FND_GLOBAL.Newline||
706 ',mtl_system_items MSI '||FND_GLOBAL.Newline;
707 -- general where clause
708 L_Statement := L_Statement||
709 'WHERE '||FND_GLOBAL.Newline||
710 ' L_Inventory_item_id IS NULL '||FND_GLOBAL.Newline||
711 'AND WRO.inventory_item_id=MSI.inventory_item_id '||FND_GLOBAL.Newline||
712 'AND WRO.organization_id=MSI.organization_id '||FND_GLOBAL.Newline||
713 'AND NVL(MSI.check_shortages_flag,'||''''||'N'||''''||')='||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
714 'AND WRS.repetitive_schedule_id=WRO.repetitive_schedule_id '||FND_GLOBAL.Newline||
715 'AND WRO.organization_id=L_Organization_id '||FND_GLOBAL.Newline||
716 'AND WRO.operation_seq_num>0 '||FND_GLOBAL.Newline||
717 'AND WRO.required_quantity>0 '||FND_GLOBAL.Newline||
718 'AND WRO.quantity_issued>=0 '||FND_GLOBAL.Newline||
719 'AND WRO.required_quantity>WRO.quantity_issued '||FND_GLOBAL.Newline;
720 -- where clause: hold schedules
721 IF p_wip_hold_rep_flag = 1 THEN
722 IF L_First THEN
723 L_First := FALSE;
724 L_Operator := 'AND ( ';
725 ELSE L_Operator := 'OR ';
726 END IF;
727 L_Statement := L_Statement||L_Operator||
728 ' WRS.status_type=6 '||FND_GLOBAL.Newline;
729 END IF;
730 -- where clause: released schedules (for days overdue)
731 IF p_wip_rel_rep_flag = 1 THEN
732 IF L_First THEN
733 L_First := FALSE;
734 L_Operator := 'AND ( ';
735 ELSE L_Operator := 'OR ';
736 END IF;
737 L_Statement := L_Statement||L_Operator||
738 ' EXISTS'||FND_GLOBAL.Newline||
739 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
740 ' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
741 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
742 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
743 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
744 ' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
745 ' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
746 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
747 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
748 ' AND WRS2.status_type=3'||FND_GLOBAL.Newline||
749 ' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
750 ' AND BCD1.calendar_date+NVL('||
751 TO_CHAR(p_wip_days_overdue_rel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
752 ' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
753 END IF;
754 -- where clause: unreleased schedules (for days overdue)
755 IF p_wip_unrel_rep_flag = 1 THEN
756 IF L_First THEN
757 L_First := FALSE;
758 L_Operator := 'AND ( ';
759 ELSE L_Operator := 'OR ';
760 END IF;
761 L_Statement := L_Statement||L_Operator||
762 ' EXISTS'||FND_GLOBAL.Newline||
763 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
764 ' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
765 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
766 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
767 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
768 ' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
769 ' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
770 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
771 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
772 ' AND WRS2.status_type=1'||FND_GLOBAL.Newline||
773 ' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
774 ' AND BCD1.calendar_date+NVL('||
775 TO_CHAR(p_wip_days_overdue_unrel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
776 ' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
777 END IF;
778 IF NOT L_First THEN
779 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
780 L_First := TRUE;
781 END IF;
782 -- where clause: parameter required date
783 IF p_wip_req_date_rep_flag = 1 THEN
784 IF L_First THEN
785 L_First := FALSE;
786 L_Operator := 'AND ( ';
787 ELSE L_Operator := 'OR ';
788 END IF;
789 L_Statement := L_Statement||L_Operator||
790 ' WRO.date_required<sysdate '||FND_GLOBAL.Newline;
791 END IF;
795 L_First := FALSE;
792 -- where clause: parameter current operation
793 IF p_wip_curr_op_rep_flag = 1 THEN
794 IF L_First THEN
796 L_Operator := 'AND ( ';
797 ELSE L_Operator := 'OR ';
798 END IF;
799 L_Statement := L_Statement||L_Operator||
800 ' EXISTS'||FND_GLOBAL.Newline||
801 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
802 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
803 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
804 ' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
805 ' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
806 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
807 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
808 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
809 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
810 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
811 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
812 END IF;
813 -- where clause: parameter previous operation
814 IF p_wip_prev_op_rep_flag = 1 THEN
815 IF L_First THEN
816 L_First := FALSE;
817 L_Operator := 'AND ( ';
818 ELSE L_Operator := 'OR ';
819 END IF;
820 L_Statement := L_Statement||L_Operator||
821 ' EXISTS'||FND_GLOBAL.Newline||
822 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
823 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
824 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
825 ' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
826 ' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
827 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
828 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
829 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
830 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
831 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
832 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
833 END IF;
834 IF NOT L_First THEN
835 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
836 END IF;
837 -- where clause: parameter bulk components
838 IF p_wip_excl_bulk_comp_flag = 1 THEN
839 L_Statement := L_Statement||
840 ' AND WRO.wip_supply_type<>4 '||FND_GLOBAL.Newline;
841 END IF;
842 -- where clause: parameter supplier components
843 IF p_wip_excl_supplier_comp_flag = 1 THEN
844 L_Statement := L_Statement||
845 ' AND WRO.wip_supply_type<>5 '||FND_GLOBAL.Newline;
846 END IF;
847 -- where clause: parameter pull components
848 IF p_wip_excl_pull_comp_flag = 1 THEN
849 L_Statement := L_Statement||
850 ' AND WRO.wip_supply_type NOT IN (2,3) '||FND_GLOBAL.Newline;
851 END IF;
852 END IF;
853 L_Statement := L_Statement||
854 '; '||FND_GLOBAL.Newline||
855 'EXCEPTION '||FND_GLOBAL.Newline||
856 ' WHEN OTHERS THEN NULL; '||FND_GLOBAL.Newline||
857 'END; '||FND_GLOBAL.Newline;
858 END IF;
859 IF p_check_oe_flag = 1 THEN
860 -- build statement
861 -- Since there exist no shortage parameter for order entry
862 -- we do not have to build a parameter dependent statement
863 IF L_Order_System = 'OE' OR L_Order_System = 'ONT' THEN
864 L_Statement := L_Statement ||
865 'BEGIN '||FND_GLOBAL.Newline||
866 'INSERT INTO mtl_short_chk_temp '||FND_GLOBAL.Newline||
867 '( seq_num '||FND_GLOBAL.Newline||
868 ' ,organization_id '||FND_GLOBAL.Newline||
869 ' ,inventory_item_id '||FND_GLOBAL.Newline||
870 ' ,quantity_open '||FND_GLOBAL.Newline||
871 ' ,uom_code '||FND_GLOBAL.Newline||
872 ' ,object_type '||FND_GLOBAL.Newline||
873 ' ,object_id '||FND_GLOBAL.Newline||
874 ' ,object_detail_id '||FND_GLOBAL.Newline||
875 ' ,last_updated_by '||FND_GLOBAL.Newline||
876 ' ,last_update_login '||FND_GLOBAL.Newline||
877 ' ,last_update_date '||FND_GLOBAL.Newline||
878 ' ,created_by '||FND_GLOBAL.Newline||
879 ' ,creation_date '||FND_GLOBAL.Newline||
880 ') '||FND_GLOBAL.Newline;
881 END IF;
882 IF L_Order_System = 'OE' THEN
883 L_Statement := L_Statement ||
884 'SELECT '||FND_GLOBAL.Newline||
885 ' L_Seq_num '||FND_GLOBAL.Newline||
886 ' ,SPL.warehouse_id '||FND_GLOBAL.Newline||
887 ' ,SPL.inventory_item_id '||FND_GLOBAL.Newline||
888 ' ,SUM(SPLD.requested_quantity-NVL(SPLD.shipped_quantity,0)) '||FND_GLOBAL.Newline||
889 ' ,SL.unit_code '||FND_GLOBAL.Newline||
890 ' ,4 '||FND_GLOBAL.Newline||
891 ' ,SH.header_id '||FND_GLOBAL.Newline||
892 ' ,SL.line_id '||FND_GLOBAL.Newline||
893 ' ,0 '||FND_GLOBAL.Newline||
894 ' ,-1 '||FND_GLOBAL.Newline||
895 ' ,sysdate '||FND_GLOBAL.Newline||
896 ' ,0 '||FND_GLOBAL.Newline||
897 ' ,sysdate '||FND_GLOBAL.Newline||
898 'FROM so_headers SH '||FND_GLOBAL.Newline||
899 ' ,so_lines SL '||FND_GLOBAL.Newline||
900 ' ,so_line_details SLD '||FND_GLOBAL.Newline||
901 ' ,so_picking_lines SPL '||FND_GLOBAL.Newline||
902 ' ,so_picking_line_details SPLD '||FND_GLOBAL.Newline||
903 ' ,mtl_system_items MSI '||FND_GLOBAL.Newline||
907 'AND SPLD.requested_quantity > NVL(SPLD.shipped_quantity,0) '||FND_GLOBAL.Newline||
904 'WHERE SPL.picking_header_id = 0 '||FND_GLOBAL.Newline||
905 'AND SPL.picking_line_id = SPLD.picking_line_id '||FND_GLOBAL.Newline||
906 'AND NVL(SPLD.released_flag,'||''''||'N'||''''||') = '||''''||'N'||''''||' '||FND_GLOBAL.Newline||
908 'AND SL.line_id = SPL.order_line_id '||FND_GLOBAL.Newline||
909 'AND SH.header_id = SL.header_id '||FND_GLOBAL.Newline||
910 'AND SLD.line_id = SL.line_id '||FND_GLOBAL.Newline||
911 'AND SL.ordered_quantity > NVL(SL.cancelled_quantity,0) '||FND_GLOBAL.Newline||
912 'AND (SPL.inventory_item_id = L_Inventory_item_id '||FND_GLOBAL.Newline||
913 ' OR L_Inventory_item_id IS NULL) '||FND_GLOBAL.Newline||
914 'AND SPL.warehouse_id = L_Organization_id '||FND_GLOBAL.Newline||
915 'AND SPL.inventory_item_id = MSI.inventory_item_id '||FND_GLOBAL.Newline||
916 'AND SPL.warehouse_id = MSI.organization_id '||FND_GLOBAL.Newline||
917 'AND NVL(MSI.check_shortages_flag,'||''''||'N'||''''||') = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
918 'AND SL.service_parent_line_id IS NULL '||FND_GLOBAL.Newline||
919 'AND SL.open_flag = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
920 'AND SLD.released_flag = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
921 'GROUP BY '||FND_GLOBAL.Newline||
922 ' L_Seq_num '||FND_GLOBAL.Newline||
923 ' ,SPL.warehouse_id '||FND_GLOBAL.Newline||
924 ' ,SPL.inventory_item_id '||FND_GLOBAL.Newline||
925 ' ,SL.unit_code '||FND_GLOBAL.Newline||
926 ' ,4 '||FND_GLOBAL.Newline||
927 ' ,SH.header_id '||FND_GLOBAL.Newline||
928 ' ,SL.line_id '||FND_GLOBAL.Newline||
929 ' ,0 '||FND_GLOBAL.Newline||
930 ' ,-1 '||FND_GLOBAL.Newline||
931 ' ,sysdate '||FND_GLOBAL.Newline||
932 ' ,0 '||FND_GLOBAL.Newline||
933 ' ,sysdate; '||FND_GLOBAL.Newline||
934 'EXCEPTION '||FND_GLOBAL.Newline||
935 ' WHEN OTHERS THEN NULL; '||FND_GLOBAL.Newline||
936 'END; '||FND_GLOBAL.Newline;
937 ELSE -- Order Management is installed
938 L_Statement := L_Statement ||
939 'SELECT '||FND_GLOBAL.Newline||
940 ' L_Seq_num '||FND_GLOBAL.Newline||
941 ' ,wdd.organization_id '||FND_GLOBAL.Newline||
942 ' ,wdd.inventory_item_id '||FND_GLOBAL.Newline||
943 ' ,sum(wdd.requested_quantity) '||FND_GLOBAL.Newline||
944 ' ,wdd.requested_quantity_uom '||FND_GLOBAL.Newline||
945 ' ,4 '||FND_GLOBAL.Newline||
946 ' ,wdd.source_header_id '||FND_GLOBAL.Newline||
947 ' ,wdd.source_line_id '||FND_GLOBAL.Newline||
948 ' ,0 '||FND_GLOBAL.Newline||
949 ' ,-1 '||FND_GLOBAL.Newline||
950 ' ,sysdate '||FND_GLOBAL.Newline||
951 ' ,0 '||FND_GLOBAL.Newline||
952 ' ,sysdate '||FND_GLOBAL.Newline||
953 'FROM wsh_delivery_details_ob_grp_v wdd '||FND_GLOBAL.Newline||
954 'WHERE wdd.released_status = '|| '''' ||'B' || '''' || FND_GLOBAL.Newline||
955 -- Fix bug 2115784, Notifications are sent to all planners
956 -- Added the following two line to make sure the shortage_temp records
957 -- are inserted only for specified org and item. Therefore, only the buyers
958 -- for the specified item will be notified.
959 -- Bug 2640828. Added nvl around inventory_item_id and
960 -- organization_id
961 ' AND wdd.inventory_item_id = nvl(L_Inventory_item_id ,wdd.inventory_item_id)'||FND_GLOBAL.Newline||
962 ' AND wdd.organization_id = nvl(L_Organization_id ,wdd.organization_id)'||FND_GLOBAL.Newline||
963 ' GROUP BY ' ||
964 ' L_Seq_num '||FND_GLOBAL.Newline||
965 ' ,wdd.organization_id '||FND_GLOBAL.Newline||
966 ' ,wdd.inventory_item_id '||FND_GLOBAL.Newline||
967 ' ,wdd.requested_quantity_uom '||FND_GLOBAL.Newline||
968 ' ,4 '||FND_GLOBAL.Newline||
969 ' ,wdd.source_header_id '||FND_GLOBAL.Newline||
970 ' ,wdd.source_line_id '||FND_GLOBAL.Newline||
971 ' ,0 '||FND_GLOBAL.Newline||
972 ' ,-1 '||FND_GLOBAL.Newline||
973 ' ,sysdate '||FND_GLOBAL.Newline||
974 ' ,0 '||FND_GLOBAL.Newline||
975 ' ,sysdate; ' || FND_GLOBAL.Newline||
976 'EXCEPTION '||FND_GLOBAL.Newline||
977 ' WHEN OTHERS THEN NULL; '||FND_GLOBAL.Newline||
978 'END; '||FND_GLOBAL.Newline;
979 --MR ' ,wdd.requested_quantity '||FND_GLOBAL.Newline||
980 END IF;
981 END IF;
982 L_Statement := L_Statement||
983 'COMMIT; '||FND_GLOBAL.Newline||
984 'END; '||FND_GLOBAL.Newline;
985 x_short_stat_detail := L_Statement;
986 --
987 -- Standard call to get message count and if count is 1, get message info
988 FND_MSG_PUB.Count_And_Get
989 (p_count => x_msg_count
990 , p_data => x_msg_data);
991 EXCEPTION
992 WHEN FND_API.G_EXC_ERROR THEN
993 --
994 x_return_status := FND_API.G_RET_STS_ERROR;
995 --
996 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
997 , p_data => x_msg_data);
998 --
999 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1000 --
1001 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1002 --
1003 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1004 , p_data => x_msg_data);
1005 --
1006 WHEN OTHERS THEN
1007 --
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1009 --
1010 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1014 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1011 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1012 END IF;
1013 --
1015 , p_data => x_msg_data);
1016 END;
1017 -- Start OF comments
1018 -- API name : BuildSummary
1019 -- TYPE : Private
1020 -- Pre-reqs : None
1021 -- FUNCTION :
1022 -- Parameters:
1023 -- IN :
1024 -- p_api_version IN NUMBER (required)
1025 -- API Version of this procedure
1026 --
1027 -- p_init_msg_list IN VARCHAR2 (optional)
1028 -- DEFAULT = FND_API.G_FALSE,
1029 --
1030 -- OUT :
1031 -- x_return_status OUT NUMBER
1032 -- Result of all the operations
1033 --
1034 -- x_msg_count OUT NUMBER,
1035 --
1036 -- x_msg_data OUT VARCHAR2,
1037 --
1038 -- x_short_stat_sum OUT LONG
1039 -- Summary shortage statement
1040 --
1041 -- Version: Current Version 1.0
1042 -- Changed : Nothing
1043 -- No Previous Version 0.0
1044 -- Initial version 1.0
1045 -- Notes :
1046 -- END OF comments
1047 PROCEDURE BuildSummary (
1048 p_api_version IN NUMBER ,
1049 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1050 x_return_status IN OUT NOCOPY VARCHAR2,
1051 x_msg_count IN OUT NOCOPY NUMBER,
1052 x_msg_data IN OUT NOCOPY VARCHAR2,
1053 p_organization_id IN NUMBER,
1054 p_check_wip_flag IN NUMBER,
1055 p_check_oe_flag IN NUMBER,
1056 p_wip_rel_jobs_flag IN NUMBER,
1057 p_wip_days_overdue_rel_jobs IN NUMBER,
1058 p_wip_unrel_jobs_flag IN NUMBER,
1059 p_wip_days_overdue_unrel_jobs IN NUMBER,
1060 p_wip_hold_jobs_flag IN NUMBER,
1061 p_wip_rel_rep_flag IN NUMBER,
1062 p_wip_days_overdue_rel_rep IN NUMBER,
1063 p_wip_unrel_rep_flag IN NUMBER,
1064 p_wip_days_overdue_unrel_rep IN NUMBER,
1065 p_wip_hold_rep_flag IN NUMBER,
1066 p_wip_req_date_jobs_flag IN NUMBER,
1067 p_wip_curr_op_jobs_flag IN NUMBER,
1068 p_wip_prev_op_jobs_flag IN NUMBER,
1069 p_wip_req_date_rep_flag IN NUMBER,
1070 p_wip_curr_op_rep_flag IN NUMBER,
1071 p_wip_prev_op_rep_flag IN NUMBER,
1072 p_wip_excl_bulk_comp_flag IN NUMBER,
1073 p_wip_excl_supplier_comp_flag IN NUMBER,
1074 p_wip_excl_pull_comp_flag IN NUMBER,
1075 x_short_stat_sum OUT NOCOPY LONG
1076 )
1077 IS
1078 L_api_version CONSTANT NUMBER := 1.0;
1079 L_api_name CONSTANT VARCHAR2(30) := 'BuildSummary';
1080 L_Statement LONG;
1081 L_First BOOLEAN := TRUE;
1082 L_Operator VARCHAR2(10);
1083 L_String VARCHAR2(255);
1084 L_Check_jobs NUMBER;
1085 L_Check_rep NUMBER;
1086 L_Order_System VARCHAR2(10); -- The type of order entry system which is installed.
1087 BEGIN
1088 -- Standard Call to check for call compatibility
1089 IF NOT FND_API.Compatible_API_Call(l_api_version
1090 , p_api_version
1091 , l_api_name
1092 , G_PKG_NAME) THEN
1093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1094 END IF;
1095 --
1096 -- Initialize message list if p_init_msg_list is set to true
1097 IF FND_API.to_Boolean(p_init_msg_list) THEN
1098 FND_MSG_PUB.initialize;
1099 END IF;
1100 --
1101 -- Initialize API return status to access
1102 x_return_status := FND_API.G_RET_STS_SUCCESS;
1103 --
1104 -- For WIP see whether jobs and/or schedules should be included
1105 IF ( p_wip_rel_jobs_flag = 1 OR p_wip_unrel_jobs_flag = 1 OR
1106 p_wip_hold_jobs_flag = 1 ) THEN
1107 L_Check_jobs := 1;
1108 ELSE
1109 L_Check_jobs := 2;
1110 END IF;
1111 IF ( p_wip_rel_rep_flag = 1 OR p_wip_unrel_rep_flag = 1 OR
1112 p_wip_hold_rep_flag = 1 ) THEN
1113 L_Check_rep := 1;
1114 ELSE
1115 L_Check_rep := 2;
1116 END IF;
1117 --
1118 -- Determine whether Order Entry or Order Management is installed.
1119 L_Order_System := OE_INSTALL.Get_Active_Product;
1120 --
1121 L_Statement := 'DECLARE '||FND_GLOBAL.Newline||
1122 ' L_Organization_id NUMBER; '||FND_GLOBAL.Newline||
1123 ' L_Inventory_item_id NUMBER; '||FND_GLOBAL.Newline||
1124 ' L_WIP_short_quantity NUMBER; '||FND_GLOBAL.Newline||
1125 ' L_OE_short_quantity NUMBER; '||FND_GLOBAL.Newline||
1126 ' L_WIP_jobs_short_quantity NUMBER := 0; '||FND_GLOBAL.Newline||
1127 ' L_WIP_rep_short_quantity NUMBER := 0; '||FND_GLOBAL.Newline||
1128 'BEGIN '||FND_GLOBAL.Newline||
1129 ' L_Organization_id := :organization_id; '||FND_GLOBAL.Newline||
1130 ' L_Inventory_item_id := :inventory_item_id; '||FND_GLOBAL.Newline||
1131 ' L_WIP_short_quantity := :wip_short_quantity; '||FND_GLOBAL.Newline||
1132 ' L_OE_short_quantity := :oe_short_quantity; '||FND_GLOBAL.Newline;
1133 IF p_check_wip_flag = 1 AND ( L_Check_jobs = 1 OR L_Check_rep =1 ) THEN
1134 --
1135 -- Jobs
1136 --
1137 IF L_Check_jobs = 1 THEN
1138 -- build statement
1139 -- select clause and general from clause
1140 L_Statement := L_Statement||
1141 'SELECT '||
1142 'NVL(SUM(WRO.required_quantity-WRO.quantity_issued),0) '||FND_GLOBAL.Newline||
1143 'INTO '||FND_GLOBAL.Newline||
1144 ' L_WIP_jobs_short_quantity '||FND_GLOBAL.Newline||
1145 'FROM wip_entities WE'||FND_GLOBAL.Newline||
1146 ',wip_requirement_operations WRO '||FND_GLOBAL.Newline;
1150 'AND WRO.organization_id=L_Organization_id '||FND_GLOBAL.Newline||
1147 -- general where clause
1148 L_Statement := L_Statement||
1149 'WHERE WRO.wip_entity_id=WE.wip_entity_id '||FND_GLOBAL.Newline||
1151 'AND WRO.inventory_item_id=L_Inventory_item_id '||FND_GLOBAL.Newline||
1152 'AND WRO.operation_seq_num>0 '||FND_GLOBAL.Newline||
1153 'AND WRO.required_quantity>0 '||FND_GLOBAL.Newline||
1154 'AND WRO.quantity_issued>=0 '||FND_GLOBAL.Newline||
1155 'AND WRO.required_quantity>WRO.quantity_issued '||FND_GLOBAL.Newline;
1156 -- where clause: hold jobs
1157 IF p_wip_hold_jobs_flag = 1 THEN
1158 IF L_First THEN
1159 L_First := FALSE;
1160 L_Operator := 'AND ( ';
1161 ELSE L_Operator := 'OR ';
1162 END IF;
1163 L_Statement := L_Statement||L_Operator||
1164 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
1165 ' AND EXISTS'||FND_GLOBAL.Newline||
1166 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1167 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
1168 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1169 ' AND WDJ.status_type=6))'||FND_GLOBAL.Newline;
1170 END IF;
1171 -- where clause: released jobs (for days overdue)
1172 IF p_wip_rel_jobs_flag = 1 THEN
1173 IF L_First THEN
1174 L_First := FALSE;
1175 L_Operator := 'AND ( ';
1176 ELSE L_Operator := 'OR ';
1177 END IF;
1178 L_Statement := L_Statement||L_Operator||
1179 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
1180 ' AND EXISTS'||FND_GLOBAL.Newline||
1181 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1182 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
1183 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
1184 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
1185 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
1186 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1187 ' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
1188 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
1189 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
1190 ' AND WDJ.status_type=3'||FND_GLOBAL.Newline||
1191 ' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
1192 ' AND BCD1.calendar_date+NVL('||
1193 TO_CHAR(p_wip_days_overdue_rel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
1194 ' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
1195 END IF;
1196 -- where clause: unreleased jobs (for days overdue)
1197 IF p_wip_unrel_jobs_flag = 1 THEN
1198 IF L_First THEN
1199 L_First := FALSE;
1200 L_Operator := 'AND ( ';
1201 ELSE L_Operator := 'OR ';
1202 END IF;
1203 L_Statement := L_Statement||L_Operator||
1204 ' (WRO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
1205 ' AND EXISTS'||FND_GLOBAL.Newline||
1206 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1207 ' FROM wip_discrete_jobs WDJ'||FND_GLOBAL.Newline||
1208 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
1209 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
1210 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
1211 ' WHERE WDJ.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1212 ' AND WDJ.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
1213 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
1214 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
1215 ' AND WDJ.status_type=1'||FND_GLOBAL.Newline||
1216 ' AND TRUNC(WDJ.scheduled_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
1217 ' AND BCD1.calendar_date+NVL('||
1218 TO_CHAR(p_wip_days_overdue_unrel_jobs)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
1219 ' AND BCD2.calendar_date<=sysdate)) '||FND_GLOBAL.Newline;
1220 END IF;
1221 IF NOT L_First THEN
1222 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
1223 L_First := TRUE;
1224 END IF;
1225 -- where clause: parameter required date
1226 IF p_wip_req_date_jobs_flag = 1 THEN
1227 IF L_First THEN
1228 L_First := FALSE;
1229 L_Operator := 'AND ( ';
1230 ELSE L_Operator := 'OR ';
1231 END IF;
1232 L_Statement := L_Statement||L_Operator||
1233 ' WRO.date_required<sysdate '||FND_GLOBAL.Newline;
1234 END IF;
1235 -- where clause: parameter current operation
1236 IF p_wip_curr_op_jobs_flag = 1 THEN
1237 IF L_First THEN
1238 L_First := FALSE;
1239 L_Operator := 'AND ( ';
1240 ELSE L_Operator := 'OR ';
1241 END IF;
1242 L_Statement := L_Statement||L_Operator||
1243 ' EXISTS'||FND_GLOBAL.Newline||
1244 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1245 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
1246 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1247 ' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
1248 ' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
1249 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
1250 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
1251 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
1252 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
1253 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
1254 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
1255 END IF;
1256 -- where clause: parameter previous operation
1257 IF p_wip_prev_op_jobs_flag = 1 THEN
1258 IF L_First THEN
1262 END IF;
1259 L_First := FALSE;
1260 L_Operator := 'AND ( ';
1261 ELSE L_Operator := 'OR ';
1263 L_Statement := L_Statement||L_Operator||
1264 ' EXISTS'||FND_GLOBAL.Newline||
1265 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1266 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
1267 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1268 ' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
1269 ' AND WO.repetitive_schedule_id IS NULL'||FND_GLOBAL.Newline||
1270 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
1271 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
1272 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
1273 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
1274 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
1275 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
1276 END IF;
1277 IF NOT L_First THEN
1278 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
1279 L_First := TRUE;
1280 END IF;
1281 -- where clause: parameter bulk components
1282 IF p_wip_excl_bulk_comp_flag = 1 THEN
1283 L_Statement := L_Statement||
1284 ' AND WRO.wip_supply_type<>4 '||FND_GLOBAL.Newline;
1285 END IF;
1286 -- where clause: parameter supplier components
1287 IF p_wip_excl_supplier_comp_flag = 1 THEN
1288 L_Statement := L_Statement||
1289 ' AND WRO.wip_supply_type<>5 '||FND_GLOBAL.Newline;
1290 END IF;
1291 -- where clause: parameter pull components
1292 IF p_wip_excl_pull_comp_flag = 1 THEN
1293 L_Statement := L_Statement||
1294 ' AND WRO.wip_supply_type NOT IN (2,3) '||FND_GLOBAL.Newline;
1295 END IF;
1296 L_Statement := L_Statement||
1297 '; '||FND_GLOBAL.Newline;
1298 END IF;
1299 --
1300 -- Schedules
1301 --
1302 IF L_Check_rep = 1 THEN
1303 -- build statement
1304 -- select clause and general from clause
1305 L_Statement := L_Statement||
1306 'SELECT'||FND_GLOBAL.Newline||
1307 'NVL(SUM(INV_ShortCheckExec_PVT.get_rep_curr_open_qty '||FND_GLOBAL.Newline||
1308 ' ( WRO.organization_id '||FND_GLOBAL.Newline||
1309 ' , WRS.wip_entity_id '||FND_GLOBAL.Newline||
1310 ' , WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
1311 ' , WRS.first_unit_start_date '||FND_GLOBAL.Newline||
1312 ' , WRS.processing_work_days '||FND_GLOBAL.Newline||
1313 ' , WRO.operation_seq_num '||FND_GLOBAL.Newline||
1314 ' , WRO.inventory_item_id '||FND_GLOBAL.Newline||
1315 ' , WRO.quantity_issued '||FND_GLOBAL.Newline||
1316 ' ) '||FND_GLOBAL.Newline||
1317 ' ) '||FND_GLOBAL.Newline||
1318 ' , 0 '||FND_GLOBAL.Newline||
1319 ' ) '||FND_GLOBAL.Newline||
1320 'INTO '||FND_GLOBAL.Newline||
1321 ' L_WIP_rep_short_quantity '||FND_GLOBAL.Newline||
1322 'FROM wip_repetitive_schedules WRS'||FND_GLOBAL.Newline||
1323 ',wip_requirement_operations WRO '||FND_GLOBAL.Newline;
1324 -- general where clause
1325 L_Statement := L_Statement||
1326 'WHERE WRO.repetitive_schedule_id=WRS.repetitive_schedule_id '||FND_GLOBAL.Newline||
1327 'AND WRO.wip_entity_id=WRS.wip_entity_id '||FND_GLOBAL.Newline||
1328 'AND WRO.organization_id=L_Organization_id '||FND_GLOBAL.Newline||
1329 'AND WRO.inventory_item_id=L_Inventory_item_id '||FND_GLOBAL.Newline||
1330 'AND WRO.operation_seq_num>0 '||FND_GLOBAL.Newline||
1331 'AND WRO.required_quantity>0 '||FND_GLOBAL.Newline||
1332 'AND WRO.quantity_issued>=0 '||FND_GLOBAL.Newline||
1333 'AND WRO.required_quantity>WRO.quantity_issued '||FND_GLOBAL.Newline;
1334 -- where clause: hold schedules
1335 IF p_wip_hold_rep_flag = 1 THEN
1336 IF L_First THEN
1337 L_First := FALSE;
1338 L_Operator := 'AND ( ';
1339 ELSE L_Operator := 'OR ';
1340 END IF;
1341 L_Statement := L_Statement||L_Operator||
1342 ' WRS.status_type=6 '||FND_GLOBAL.Newline;
1343 END IF;
1344 -- where clause: released schedules (for days overdue)
1345 IF p_wip_rel_rep_flag = 1 THEN
1346 IF L_First THEN
1347 L_First := FALSE;
1348 L_Operator := 'AND ( ';
1349 ELSE L_Operator := 'OR ';
1350 END IF;
1351 L_Statement := L_Statement||L_Operator||
1352 ' EXISTS'||FND_GLOBAL.Newline||
1353 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1354 ' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
1355 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
1356 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
1357 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
1358 ' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
1359 ' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
1360 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
1361 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
1362 ' AND WRS2.status_type=3'||FND_GLOBAL.Newline||
1363 ' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
1364 ' AND BCD1.calendar_date+NVL('||
1365 TO_CHAR(p_wip_days_overdue_rel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
1366 ' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
1367 END IF;
1371 L_First := FALSE;
1368 -- where clause: unreleased schedules (for days overdue)
1369 IF p_wip_unrel_rep_flag = 1 THEN
1370 IF L_First THEN
1372 L_Operator := 'AND ( ';
1373 ELSE L_Operator := 'OR ';
1374 END IF;
1375 L_Statement := L_Statement||L_Operator||
1376 ' EXISTS'||FND_GLOBAL.Newline||
1377 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1378 ' FROM wip_repetitive_schedules WRS2'||FND_GLOBAL.Newline||
1379 ' ,mtl_parameters MP'||FND_GLOBAL.Newline||
1380 ' ,bom_calendar_dates BCD1'||FND_GLOBAL.Newline||
1381 ' ,bom_calendar_dates BCD2'||FND_GLOBAL.Newline||
1382 ' WHERE WRS2.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
1383 ' AND WRS2.organization_id=MP.organization_id'||FND_GLOBAL.Newline||
1384 ' AND MP.calendar_code=BCD1.calendar_code'||FND_GLOBAL.Newline||
1385 ' AND MP.calendar_code=BCD2.calendar_code'||FND_GLOBAL.Newline||
1386 ' AND WRS2.status_type=1'||FND_GLOBAL.Newline||
1387 ' AND TRUNC(WRS2.first_unit_start_date)=BCD1.calendar_date'||FND_GLOBAL.Newline||
1388 ' AND BCD1.calendar_date+NVL('||
1389 TO_CHAR(p_wip_days_overdue_unrel_rep)||',0)=BCD2.calendar_date'||FND_GLOBAL.Newline||
1390 ' AND BCD2.calendar_date<=sysdate) '||FND_GLOBAL.Newline;
1391 END IF;
1392 IF NOT L_First THEN
1393 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
1394 L_First := TRUE;
1395 END IF;
1396 -- where clause: parameter required date
1397 IF p_wip_req_date_rep_flag = 1 THEN
1398 IF L_First THEN
1399 L_First := FALSE;
1400 L_Operator := 'AND ( ';
1401 ELSE L_Operator := 'OR ';
1402 END IF;
1403 L_Statement := L_Statement||L_Operator||
1404 ' WRO.date_required<sysdate '||FND_GLOBAL.Newline;
1405 END IF;
1406 -- where clause: parameter current operation
1407 IF p_wip_curr_op_rep_flag = 1 THEN
1408 IF L_First THEN
1409 L_First := FALSE;
1410 L_Operator := 'AND ( ';
1411 ELSE L_Operator := 'OR ';
1412 END IF;
1413 L_Statement := L_Statement||L_Operator||
1414 ' EXISTS'||FND_GLOBAL.Newline||
1415 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1416 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
1417 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1418 ' AND WO.operation_seq_num>=WRO.operation_seq_num'||FND_GLOBAL.Newline||
1419 ' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
1420 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
1421 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
1422 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
1423 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
1424 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
1425 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
1426 END IF;
1427 -- where clause: parameter previous operation
1428 IF p_wip_prev_op_rep_flag = 1 THEN
1429 IF L_First THEN
1430 L_First := FALSE;
1431 L_Operator := 'AND ( ';
1432 ELSE L_Operator := 'OR ';
1433 END IF;
1434 L_Statement := L_Statement||L_Operator||
1435 ' EXISTS'||FND_GLOBAL.Newline||
1436 ' (SELECT '||''''||'X'||''''||' '||FND_GLOBAL.Newline||
1437 ' FROM wip_operations WO'||FND_GLOBAL.Newline||
1438 ' WHERE WO.wip_entity_id=WRO.wip_entity_id'||FND_GLOBAL.Newline||
1439 ' AND WO.next_operation_seq_num=WRO.operation_seq_num'||FND_GLOBAL.Newline||
1440 ' AND WO.repetitive_schedule_id=WRO.repetitive_schedule_id'||FND_GLOBAL.Newline||
1441 ' AND (WO.quantity_in_queue>0'||FND_GLOBAL.Newline||
1442 ' OR WO.quantity_running>0'||FND_GLOBAL.Newline||
1443 ' OR WO.quantity_waiting_to_move>0'||FND_GLOBAL.Newline||
1444 ' OR WO.quantity_rejected>0'||FND_GLOBAL.Newline||
1445 ' OR WO.quantity_scrapped>0'||FND_GLOBAL.Newline||
1446 ' OR WO.quantity_completed>0))'||FND_GLOBAL.Newline;
1447 END IF;
1448 IF NOT L_First THEN
1449 L_Statement := L_Statement||')'||FND_GLOBAL.Newline;
1450 END IF;
1451 -- where clause: parameter bulk components
1452 IF p_wip_excl_bulk_comp_flag = 1 THEN
1453 L_Statement := L_Statement||
1454 ' AND WRO.wip_supply_type<>4 '||FND_GLOBAL.Newline;
1455 END IF;
1456 -- where clause: parameter supplier components
1457 IF p_wip_excl_supplier_comp_flag = 1 THEN
1458 L_Statement := L_Statement||
1459 ' AND WRO.wip_supply_type<>5 '||FND_GLOBAL.Newline;
1460 END IF;
1461 -- where clause: parameter pull components
1462 IF p_wip_excl_pull_comp_flag = 1 THEN
1463 L_Statement := L_Statement||
1464 ' AND WRO.wip_supply_type NOT IN (2,3) '||FND_GLOBAL.Newline;
1465 END IF;
1466 L_Statement := L_Statement||
1467 '; '||FND_GLOBAL.Newline;
1468 END IF;
1469 L_Statement := L_Statement||
1470 ':wip_short_quantity := L_WIP_jobs_short_quantity '||FND_GLOBAL.Newline||
1471 ' + L_WIP_rep_short_quantity; '||FND_GLOBAL.Newline;
1472 END IF;
1473 IF p_check_oe_flag = 1 THEN
1474 -- build statement
1475 -- Since there exist no shortage parameters for order entry
1476 -- we do not have to build a parameter dependent statement
1477 IF L_Order_System = 'OE' OR L_Order_System = 'ONT' THEN
1478 IF L_Order_System = 'OE' THEN
1479 L_Statement := L_Statement ||
1480 'SELECT '||FND_GLOBAL.Newline||
1484 ' SPL.inventory_item_id '||FND_GLOBAL.Newline||
1481 ' NVL(SUM(DECODE(SL.unit_code, '||FND_GLOBAL.Newline||
1482 ' MSI.primary_uom_code, (SPLD.requested_quantity - NVL(SPLD.shipped_quantity,0)), '||FND_GLOBAL.Newline||
1483 ' INV_CONVERT.INV_UM_CONVERT ( '||FND_GLOBAL.Newline||
1485 ' ,NULL '||FND_GLOBAL.Newline||
1486 ' ,(SPLD.requested_quantity - NVL(SPLD.shipped_quantity,0))'||FND_GLOBAL.Newline||
1487 ' ,SL.unit_code '||FND_GLOBAL.Newline||
1488 ' ,MSI.primary_uom_code '||FND_GLOBAL.Newline||
1489 ' ,NULL '||FND_GLOBAL.Newline||
1490 ' ,NULL ) ) ),0) '||FND_GLOBAL.Newline||
1491 'INTO '||FND_GLOBAL.Newline||
1492 ' :oe_short_quantity '||FND_GLOBAL.Newline||
1493 'FROM so_headers SH '||FND_GLOBAL.Newline||
1494 ' ,so_lines SL '||FND_GLOBAL.Newline||
1495 ' ,so_line_details SLD '||FND_GLOBAL.Newline||
1496 ' ,so_picking_lines SPL '||FND_GLOBAL.Newline||
1497 ' ,so_picking_line_details SPLD '||FND_GLOBAL.Newline||
1498 ' ,mtl_system_items MSI '||FND_GLOBAL.Newline||
1499 'WHERE SPL.picking_header_id = 0 '||FND_GLOBAL.Newline||
1500 'AND SPL.picking_line_id = SPLD.picking_line_id '||FND_GLOBAL.Newline||
1501 'AND NVL(SPLD.released_flag,'||''''||'N'||''''||') = '||''''||'N'||''''||' '||FND_GLOBAL.Newline||
1502 'AND SPLD.requested_quantity > NVL(SPLD.shipped_quantity,0) '||FND_GLOBAL.Newline||
1503 'AND SL.line_id = SPL.order_line_id '||FND_GLOBAL.Newline||
1504 'AND SH.header_id = SL.header_id '||FND_GLOBAL.Newline||
1505 'AND SLD.line_id = SL.line_id '||FND_GLOBAL.Newline||
1506 'AND SL.ordered_quantity > NVL(SL.cancelled_quantity,0) '||FND_GLOBAL.Newline||
1507 'AND SPL.inventory_item_id = MSI.inventory_item_id '||FND_GLOBAL.Newline||
1508 'AND MSI.organization_id = L_Organization_id '||FND_GLOBAL.Newline||
1509 'AND SPL.inventory_item_id = L_Inventory_item_id '||FND_GLOBAL.Newline||
1510 'AND SPLD.warehouse_id = L_Organization_id '||FND_GLOBAL.Newline||
1511 'AND SL.service_parent_line_id IS NULL '||FND_GLOBAL.Newline||
1512 'AND SL.open_flag = '||''''||'Y'||''''||' '||FND_GLOBAL.Newline||
1513 'AND SLD.released_flag = '||''''||'Y'||''''||'; '||FND_GLOBAL.Newline;
1514 ELSE -- Order management system is installed
1515 L_Statement := L_Statement ||
1516 'SELECT '||FND_GLOBAL.Newline||
1517 ' NVL(sum(DECODE(wdd.requested_quantity_uom, '||FND_GLOBAL.Newline||
1518 ' MSI.primary_uom_code, wdd.requested_quantity, '||FND_GLOBAL.Newline||
1519 ' INV_CONVERT.INV_UM_CONVERT ( '||FND_GLOBAL.Newline||
1520 ' wdd.inventory_item_id '||FND_GLOBAL.Newline||
1521 ' ,NULL '||FND_GLOBAL.Newline||
1522 ' ,wdd.requested_quantity '||FND_GLOBAL.Newline||
1523 ' ,wdd.requested_quantity_uom '||FND_GLOBAL.Newline||
1524 ' ,MSI.primary_uom_code '||FND_GLOBAL.Newline||
1525 ' ,NULL '||FND_GLOBAL.Newline||
1526 ' ,NULL ) ) ),0) '||FND_GLOBAL.Newline||
1527 'INTO '||FND_GLOBAL.Newline||
1528 ' :oe_short_quantity '||FND_GLOBAL.Newline||
1529 'FROM wsh_delivery_details_ob_grp_v wdd '||FND_GLOBAL.Newline||
1530 ' ,mtl_system_items MSI '||FND_GLOBAL.Newline||
1531 'WHERE wdd.inventory_item_id = L_Inventory_item_id '||FND_GLOBAL.Newline||
1532 'AND MSI.inventory_item_id = wdd.inventory_item_id '||FND_GLOBAL.Newline||
1533 'AND MSI.organization_id = L_Organization_id '||FND_GLOBAL.Newline||
1534 'AND wdd.organization_id = L_Organization_id '||FND_GLOBAL.Newline||
1535 -- Fix bug 2101710, short alert appears even for not backordered sales order
1536 -- added the following line to query only the backordered lines.
1537 -- this is to make the where clauses consistent with the detail statements
1538 'AND wdd.released_status = '|| '''' ||'B' || '''' || FND_GLOBAL.Newline||
1539 'AND (wdd.requested_quantity IS NOT NULL ' ||FND_GLOBAL.Newline||
1540 'AND wdd.requested_quantity > 0); '||FND_GLOBAL.Newline;
1541 --'AND MOL.quantity - NVL(MOL.quantity_detailed,0) > 0; '||FND_GLOBAL.Newline;
1542 END IF;
1543 END IF;
1544 END IF;
1545 L_Statement := L_Statement||
1546 'END; '||FND_GLOBAL.Newline;
1547 x_short_stat_sum := L_Statement;
1548 --
1549 -- Standard call to get message count and if count is 1, get message info
1550 FND_MSG_PUB.Count_And_Get
1551 (p_count => x_msg_count
1552 , p_data => x_msg_data);
1553 EXCEPTION
1554 WHEN FND_API.G_EXC_ERROR THEN
1555 --
1556 x_return_status := FND_API.G_RET_STS_ERROR;
1557 --
1558 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1559 , p_data => x_msg_data);
1560 --
1561 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1562 --
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 --
1565 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1566 , p_data => x_msg_data);
1567 --
1568 WHEN OTHERS THEN
1569 --
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1571 --
1572 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1573 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1574 END IF;
1575 --
1576 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1577 , p_data => x_msg_data);
1578 END;
1579 -- Start OF comments
1580 -- API name : InsertUpdate
1581 -- TYPE : Private
1582 -- Pre-reqs : None
1583 -- FUNCTION :
1584 -- Parameters:
1585 -- IN :
1586 -- p_api_version IN NUMBER (required)
1590 -- DEFAULT = FND_API.G_FALSE,
1587 -- API Version of this procedure
1588 --
1589 -- p_init_msg_list IN VARCHAR2 (optional)
1591 --
1592 -- OUT :
1593 -- x_return_status OUT NUMBER
1594 -- Result of all the operations
1595 --
1596 -- x_msg_count OUT NUMBER,
1597 --
1598 -- x_msg_data OUT VARCHAR2,
1599 --
1600 -- Version: Current Version 1.0
1601 -- Changed : Nothing
1602 -- No Previous Version 0.0
1603 -- Initial version 1.0
1604 -- Notes :
1605 -- END OF comments
1606 PROCEDURE InsertUpdate (
1607 p_api_version IN NUMBER ,
1608 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1609 x_return_status IN OUT NOCOPY VARCHAR2,
1610 x_msg_count IN OUT NOCOPY NUMBER,
1611 x_msg_data IN OUT NOCOPY VARCHAR2,
1612 p_organization_id IN NUMBER,
1613 p_short_stat_sum IN LONG,
1614 p_short_stat_detail IN LONG
1615 )
1616 IS
1617 CURSOR crsStatement ( p_organization_id NUMBER,
1618 p_detail_sum_flag NUMBER ) IS
1619 SELECT 1
1620 FROM mtl_short_chk_statements
1621 WHERE organization_id = p_organization_id
1622 AND detail_sum_flag = p_detail_sum_flag;
1623 --
1624 L_api_version CONSTANT NUMBER := 1.0;
1625 L_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate';
1626 L_Object_Exists NUMBER;
1627 BEGIN
1628 -- Standard Call to check for call compatibility
1629 IF NOT FND_API.Compatible_API_Call(l_api_version
1630 , p_api_version
1631 , l_api_name
1632 , G_PKG_NAME) THEN
1633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1634 END IF;
1635 --
1636 -- Initialize message list if p_init_msg_list is set to true
1637 IF FND_API.to_Boolean(p_init_msg_list) THEN
1638 FND_MSG_PUB.initialize;
1639 END IF;
1640 --
1641 -- Initialize API return status to access
1642 x_return_status := FND_API.G_RET_STS_SUCCESS;
1643 --
1644 -- Insert/Update of shortage statement table
1645 -- First the detail statement
1646 OPEN crsStatement ( p_organization_id,
1647 1 );
1648 FETCH crsStatement INTO L_Object_Exists;
1649 IF crsStatement%NOTFOUND THEN
1650 INSERT INTO mtl_short_chk_statements (
1651 organization_id,
1652 detail_sum_flag,
1653 short_statement,
1654 last_updated_by,
1655 last_update_login,
1656 last_update_date,
1657 created_by,
1658 creation_date
1659 )
1660 VALUES (
1661 p_organization_id,
1662 1,
1663 p_short_stat_detail,
1664 FND_GLOBAL.USER_ID,
1665 FND_GLOBAL.LOGIN_ID,
1666 sysdate,
1667 FND_GLOBAL.USER_ID,
1668 sysdate
1669 );
1670 ELSE
1671 UPDATE mtl_short_chk_statements
1672 SET short_statement = p_short_stat_detail,
1673 last_update_date = sysdate,
1674 last_updated_by = FND_GLOBAL.USER_ID,
1675 last_update_login = FND_GLOBAL.LOGIN_ID
1676 WHERE organization_id = p_organization_id
1677 AND detail_sum_flag = 1;
1678 END IF;
1679 CLOSE crsStatement;
1680 -- Then the summary statement
1681 OPEN crsStatement ( p_organization_id,
1682 2 );
1683 FETCH crsStatement INTO L_Object_Exists;
1684 IF crsStatement%NOTFOUND THEN
1685 INSERT INTO mtl_short_chk_statements (
1686 organization_id,
1687 detail_sum_flag,
1688 short_statement,
1689 last_updated_by,
1690 last_update_login,
1691 last_update_date,
1692 created_by,
1693 creation_date
1694 )
1695 VALUES (
1696 p_organization_id,
1697 2,
1698 p_short_stat_sum,
1699 FND_GLOBAL.USER_ID,
1700 FND_GLOBAL.LOGIN_ID,
1701 sysdate,
1702 FND_GLOBAL.USER_ID,
1703 sysdate
1704 );
1705 ELSE
1706 UPDATE mtl_short_chk_statements
1707 SET short_statement = p_short_stat_sum,
1708 last_update_date = sysdate,
1709 last_updated_by = FND_GLOBAL.USER_ID,
1710 last_update_login = FND_GLOBAL.LOGIN_ID
1711 WHERE organization_id = p_organization_id
1712 AND detail_sum_flag = 2;
1713 END IF;
1714 CLOSE crsStatement;
1715 --
1716 -- Standard call to get message count and if count is 1, get message info
1717 FND_MSG_PUB.Count_And_Get
1718 (p_count => x_msg_count
1719 , p_data => x_msg_data);
1720 EXCEPTION
1721 WHEN FND_API.G_EXC_ERROR THEN
1722 --
1723 x_return_status := FND_API.G_RET_STS_ERROR;
1724 --
1725 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1726 , p_data => x_msg_data);
1727 --
1728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1729 --
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731 --
1732 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1733 , p_data => x_msg_data);
1734 --
1735 WHEN OTHERS THEN
1736 --
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1738 --
1739 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1740 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1741 END IF;
1742 --
1743 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1744 , p_data => x_msg_data);
1745 END;
1749 -- Pre-reqs : None
1746 -- Start OF comments
1747 -- API name : StartBuild
1748 -- TYPE : Private
1750 -- FUNCTION :
1751 -- Parameters:
1752 -- IN :
1753 -- p_api_version IN NUMBER (required)
1754 -- API Version of this procedure
1755 --
1756 -- p_init_msg_list IN VARCHAR2 (optional)
1757 -- DEFAULT = FND_API.G_FALSE,
1758 --
1759 -- p_commit IN VARCHAR2 (optional)
1760 -- DEFAULT = FND_API.G_FALSE
1761 --
1762 -- OUT :
1763 -- x_return_status OUT NUMBER
1764 -- Result of all the operations
1765 --
1766 -- x_msg_count OUT NUMBER,
1767 --
1768 -- x_msg_data OUT VARCHAR2,
1769 --
1770 -- x_short_stat_sum OUT LONG,
1771 -- Summary shortage statement
1772 --
1773 -- x_short_stat_detail OUT LONG
1774 -- Detail shortage statement
1775 --
1776 -- Version: Current Version 1.0
1777 -- Changed : Nothing
1778 -- No Previous Version 0.0
1779 -- Initial version 1.0
1780 -- Notes :
1781 -- END OF comments
1782 PROCEDURE StartBuild (
1783 p_api_version IN NUMBER ,
1784 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1785 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1786 x_return_status IN OUT NOCOPY VARCHAR2,
1787 x_msg_count IN OUT NOCOPY NUMBER,
1788 x_msg_data IN OUT NOCOPY VARCHAR2,
1789 p_organization_id IN NUMBER,
1790 p_check_wip_flag IN NUMBER,
1791 p_check_oe_flag IN NUMBER,
1792 p_wip_rel_jobs_flag IN NUMBER,
1793 p_wip_days_overdue_rel_jobs IN NUMBER,
1794 p_wip_unrel_jobs_flag IN NUMBER,
1795 p_wip_days_overdue_unrel_jobs IN NUMBER,
1796 p_wip_hold_jobs_flag IN NUMBER,
1797 p_wip_rel_rep_flag IN NUMBER,
1798 p_wip_days_overdue_rel_rep IN NUMBER,
1799 p_wip_unrel_rep_flag IN NUMBER,
1800 p_wip_days_overdue_unrel_rep IN NUMBER,
1801 p_wip_hold_rep_flag IN NUMBER,
1802 p_wip_req_date_jobs_flag IN NUMBER,
1803 p_wip_curr_op_jobs_flag IN NUMBER,
1804 p_wip_prev_op_jobs_flag IN NUMBER,
1805 p_wip_req_date_rep_flag IN NUMBER,
1806 p_wip_curr_op_rep_flag IN NUMBER,
1807 p_wip_prev_op_rep_flag IN NUMBER,
1808 p_wip_excl_bulk_comp_flag IN NUMBER,
1809 p_wip_excl_supplier_comp_flag IN NUMBER,
1810 p_wip_excl_pull_comp_flag IN NUMBER
1811 )
1812 IS
1813 L_api_version CONSTANT NUMBER := 1.0;
1814 L_api_name CONSTANT VARCHAR2(30) := 'StartBuild';
1815 L_Short_stat_sum LONG;
1816 L_Short_stat_detail LONG;
1817 BEGIN
1818 -- Standard Call to check for call compatibility
1819 IF NOT FND_API.Compatible_API_Call(l_api_version
1820 , p_api_version
1821 , l_api_name
1822 , G_PKG_NAME) THEN
1823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1824 END IF;
1825 --
1826 -- Initialize message list if p_init_msg_list is set to true
1827 IF FND_API.to_Boolean(p_init_msg_list) THEN
1828 FND_MSG_PUB.initialize;
1829 END IF;
1830 --
1831 -- Initialize API return status to access
1832 x_return_status := FND_API.G_RET_STS_SUCCESS;
1833 --
1834 BuildDetail (
1835 p_api_version => 1.0,
1836 p_init_msg_list => p_init_msg_list,
1837 x_return_status => x_return_status,
1838 x_msg_count => x_msg_count,
1839 x_msg_data => x_msg_data,
1840 p_organization_id => p_organization_id,
1841 p_check_wip_flag => p_check_wip_flag,
1842 p_check_oe_flag => p_check_oe_flag,
1843 p_wip_rel_jobs_flag => p_wip_rel_jobs_flag,
1844 p_wip_days_overdue_rel_jobs => p_wip_days_overdue_rel_jobs,
1845 p_wip_unrel_jobs_flag => p_wip_unrel_jobs_flag,
1846 p_wip_days_overdue_unrel_jobs => p_wip_days_overdue_unrel_jobs,
1847 p_wip_hold_jobs_flag => p_wip_hold_jobs_flag,
1848 p_wip_rel_rep_flag => p_wip_rel_rep_flag,
1849 p_wip_days_overdue_rel_rep => p_wip_days_overdue_rel_rep,
1850 p_wip_unrel_rep_flag => p_wip_unrel_rep_flag,
1851 p_wip_days_overdue_unrel_rep => p_wip_days_overdue_unrel_rep,
1852 p_wip_hold_rep_flag => p_wip_hold_rep_flag,
1853 p_wip_req_date_jobs_flag => p_wip_req_date_jobs_flag,
1854 p_wip_curr_op_jobs_flag => p_wip_curr_op_jobs_flag,
1855 p_wip_prev_op_jobs_flag => p_wip_prev_op_jobs_flag,
1856 p_wip_req_date_rep_flag => p_wip_req_date_rep_flag,
1857 p_wip_curr_op_rep_flag => p_wip_curr_op_rep_flag,
1858 p_wip_prev_op_rep_flag => p_wip_prev_op_rep_flag,
1859 p_wip_excl_bulk_comp_flag => p_wip_excl_bulk_comp_flag,
1860 p_wip_excl_supplier_comp_flag => p_wip_excl_supplier_comp_flag,
1861 p_wip_excl_pull_comp_flag => p_wip_excl_pull_comp_flag,
1862 x_short_stat_detail => L_Short_stat_detail
1863 );
1864 BuildSummary (
1865 p_api_version => 1.0,
1866 p_init_msg_list => p_init_msg_list,
1867 x_return_status => x_return_status,
1868 x_msg_count => x_msg_count,
1869 x_msg_data => x_msg_data,
1870 p_organization_id => p_organization_id,
1871 p_check_wip_flag => p_check_wip_flag,
1872 p_check_oe_flag => p_check_oe_flag,
1873 p_wip_rel_jobs_flag => p_wip_rel_jobs_flag,
1874 p_wip_days_overdue_rel_jobs => p_wip_days_overdue_rel_jobs,
1875 p_wip_unrel_jobs_flag => p_wip_unrel_jobs_flag,
1876 p_wip_days_overdue_unrel_jobs => p_wip_days_overdue_unrel_jobs,
1877 p_wip_hold_jobs_flag => p_wip_hold_jobs_flag,
1878 p_wip_rel_rep_flag => p_wip_rel_rep_flag,
1879 p_wip_days_overdue_rel_rep => p_wip_days_overdue_rel_rep,
1880 p_wip_unrel_rep_flag => p_wip_unrel_rep_flag,
1881 p_wip_days_overdue_unrel_rep => p_wip_days_overdue_unrel_rep,
1882 p_wip_hold_rep_flag => p_wip_hold_rep_flag,
1883 p_wip_req_date_jobs_flag => p_wip_req_date_jobs_flag,
1884 p_wip_curr_op_jobs_flag => p_wip_curr_op_jobs_flag,
1885 p_wip_prev_op_jobs_flag => p_wip_prev_op_jobs_flag,
1886 p_wip_req_date_rep_flag => p_wip_req_date_rep_flag,
1887 p_wip_curr_op_rep_flag => p_wip_curr_op_rep_flag,
1888 p_wip_prev_op_rep_flag => p_wip_prev_op_rep_flag,
1889 p_wip_excl_bulk_comp_flag => p_wip_excl_bulk_comp_flag,
1890 p_wip_excl_supplier_comp_flag => p_wip_excl_supplier_comp_flag,
1891 p_wip_excl_pull_comp_flag => p_wip_excl_pull_comp_flag,
1892 x_short_stat_sum => L_Short_stat_sum
1893 );
1894 InsertUpdate (
1895 p_api_version => 1.0,
1896 p_init_msg_list => p_init_msg_list ,
1897 x_return_status => x_return_status,
1898 x_msg_count => x_msg_count,
1899 x_msg_data => x_msg_data,
1900 p_organization_id => p_organization_id,
1901 p_short_stat_sum => L_Short_stat_sum,
1902 p_short_stat_detail => L_Short_stat_detail
1903 );
1904 --
1905 -- Standard check of p_commit
1906 IF FND_API.to_Boolean(p_commit) THEN
1907 COMMIT;
1908 END IF;
1909 -- Standard call to get message count and if count is 1, get message info
1910 FND_MSG_PUB.Count_And_Get
1911 (p_count => x_msg_count
1912 , p_data => x_msg_data);
1913 EXCEPTION
1914 WHEN FND_API.G_EXC_ERROR THEN
1915 --
1916 x_return_status := FND_API.G_RET_STS_ERROR;
1917 --
1918 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1919 , p_data => x_msg_data);
1920 --
1921 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1922 --
1923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1924 --
1925 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1926 , p_data => x_msg_data);
1927 --
1928 WHEN OTHERS THEN
1929 --
1930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1931 --
1932 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1933 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1934 END IF;
1935 --
1936 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1937 , p_data => x_msg_data);
1938 END;
1939 END INV_ShortStatement_PVT;