[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_SERIAL_DATE_EVENT_PKG
Source
1 PACKAGE BODY INV_LOT_SERIAL_DATE_EVENT_PKG AS
5 x_errbuf OUT NOCOPY VARCHAR2
2 /* $Header: INVLSEVB.pls 120.2.12020000.2 2012/10/05 11:11:38 skommine ship $ */
3
4 PROCEDURE lot_serial_date_notify_cp (
6 , x_retcode OUT NOCOPY NUMBER
7 , p_organization_id IN NUMBER
8 , p_structure_id IN NUMBER
9 , p_category_id IN NUMBER
10 , p_from_item IN VARCHAR2
11 , p_to_item IN VARCHAR2
12 , p_query_for IN VARCHAR2
13 , p_from_lot IN VARCHAR2
14 , p_to_lot IN VARCHAR2
15 , p_from_serial IN VARCHAR2
16 , p_to_serial IN VARCHAR2
17 , p_attr_context IN VARCHAR2
18 , p_date_type IN VARCHAR2
19 , p_days_in_future IN NUMBER
20 , p_days_in_past IN NUMBER
21 , p_include_zero_balance IN NUMBER
22 ) AS
23 CURSOR cur_lot_template IS
24 SELECT mtp.organization_code
25 , msi.inventory_item_id
26 , msi.concatenated_segments
27 , msi.description
28 , msi.primary_uom_code
29 , msi.secondary_uom_code
30 , mln.lot_number
31 , mln.gen_object_id
32 , ohd.primary_transaction_quantity
33 , ohd.secondary_transaction_quantity
34 , expiration_date date_column
35 FROM mtl_lot_numbers mln
36 , mtl_parameters mtp
37 , mtl_system_items_kfv msi
38 , mtl_onhand_quantities_detail ohd;
39
40 CURSOR cur_serial_template IS
41 SELECT mtp.organization_code
42 , msi.inventory_item_id
43 , msi.concatenated_segments
44 , msi.description
45 , msi.primary_uom_code
46 , msi.secondary_uom_code
47 , msn.serial_number
48 , msn.gen_object_id
49 , msn.current_status
50 , mfgl.meaning serial_status
51 , msn.initialization_date date_column
52 FROM mtl_serial_numbers msn
53 , mtl_parameters mtp
54 , mtl_system_items_kfv msi
55 , mfg_lookups mfgl;
56
57 CURSOR cur_category (
58 p_org_id NUMBER
59 , p_inv_item_id NUMBER
60 , p_catg_id NUMBER
61 ) IS
62 SELECT mdsv.category_set_id
63 , mdsv.category_set_name
64 , mdsv.structure_id
65 , mcv.category_id
66 , mcv.category_concat_segs
67 FROM mtl_default_sets_view mdsv
68 , mtl_categories_v mcv
69 , mtl_item_categories mic
70 WHERE mdsv.functional_area_id = 1
71 AND mdsv.structure_id = mcv.structure_id
72 AND mic.category_set_id = mdsv.category_set_id
73 AND mcv.category_id = mic.category_id
74 AND mic.organization_id = p_org_id
75 AND mic.inventory_item_id = p_inv_item_id
76 AND mic.category_id = p_catg_id;
77
78 CURSOR cur_item_category (
79 p_org_id NUMBER
80 , p_inv_item_id NUMBER
81 ) IS
82 SELECT mdsv.category_set_id
83 , mdsv.category_set_name
84 , mdsv.structure_id
85 , mcv.category_id
86 , mcv.category_concat_segs
87 FROM mtl_default_sets_view mdsv
88 , mtl_categories_v mcv
89 , mtl_item_categories mic
90 WHERE mdsv.functional_area_id = 1
91 AND mdsv.structure_id = mcv.structure_id
92 AND mic.category_set_id = mdsv.category_set_id
93 AND mcv.category_id = mic.category_id
94 AND mic.organization_id = p_org_id
95 AND mic.inventory_item_id = p_inv_item_id;
96
97 CURSOR cur_item_info (
98 p_org_id NUMBER
99 , p_inv_item_id NUMBER
100 ) IS
101 SELECT mtp.process_enabled_flag
102 , msi.process_quality_enabled_flag
103 FROM mtl_parameters mtp, mtl_system_items msi
104 WHERE mtp.organization_id = p_org_id
105 AND mtp.organization_id = msi.organization_id
106 AND msi.inventory_item_id = p_inv_item_id;
107
108 TYPE rc IS REF CURSOR;
109
110 l_cursor rc;
111 l_lot_rec cur_lot_template%ROWTYPE;
112 l_catg_rec cur_category%ROWTYPE;
113 l_item_catg_rec cur_item_category%ROWTYPE;
114 l_serial_rec cur_serial_template%ROWTYPE;
115 l_item_info_rec cur_item_info%ROWTYPE;
116 l_catg_rec_found BOOLEAN;
117 l_lot_column_list VARCHAR2 (2000);
118 l_lot_table_list VARCHAR2 (2000);
119 l_lot_where_clause VARCHAR2 (2000);
120 l_lot_group_by VARCHAR2 (2000);
121 l_serial_column_list VARCHAR2 (2000);
122 l_serial_table_list VARCHAR2 (2000);
123 l_serial_where_clause VARCHAR2 (2000);
124 l_onhand_qty_uom VARCHAR2 (100);
125 l_attr_context fnd_descr_flex_col_usage_vl.descriptive_flex_context_code%TYPE;
126 l_attr_ctxt fnd_descr_flex_col_usage_vl.descriptive_flex_context_code%TYPE;
127 l_user_column_name fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE;
128 l_expiration_action_code mtl_lot_numbers.expiration_action_code%TYPE;
129 l_lookup_type mfg_lookups.lookup_type%TYPE := 'SERIAL_NUM_STATUS';
130 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
131 l_transaction_id NUMBER;
132 l_user_id NUMBER;
133 l_ame_transaction_id VARCHAR2(4000);
134
138
135 l_date_notification_s NUMBER; --Bug#14103573
136
137 BEGIN
139 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Calling LOT_SERIAL_DATE_NOTIFY_CP with values ');
140 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_organization_id : '||to_char(p_organization_id));
141 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_structure_id : '||to_char(p_structure_id));
142 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_category_id : '||to_char(p_category_id));
143 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_from_item : '||p_from_item);
144 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_to_item : '||p_to_item);
145 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_query_for : '||p_query_for);
146 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_from_lot : '||p_from_lot);
147 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_to_lot : '||p_to_lot);
148 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_from_serial : '||p_from_serial);
149 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_to_serial : '||p_to_serial);
150 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_attr_context : '||p_attr_context);
151 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_date_type : '||p_date_type);
152 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_days_in_future : '||to_char(p_days_in_future));
153 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_days_in_past : '||to_char(p_days_in_past));
154 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' p_include_zero_balance : '||to_char(p_include_zero_balance));
155
156 IF p_attr_context = '-999AAAZZZ' THEN
157 l_attr_ctxt := ' ';
158 ELSE
159 l_attr_ctxt := p_attr_context;
160 END IF;
161
162 IF l_attr_ctxt <> ' ' THEN
163 SELECT end_user_column_name
164 INTO l_user_column_name
165 FROM fnd_descr_flex_col_usage_vl
166 WHERE application_id = 401
167 AND descriptive_flexfield_name =
168 DECODE (p_query_for
169 , 'LOT', 'Lot Attributes'
170 , 'Serial Attributes'
171 )
172 AND enabled_flag = 'Y'
173 AND application_column_name = p_date_type
174 AND descriptive_flex_context_code = l_attr_ctxt;
175 ELSE
176 l_user_column_name := p_date_type;
177 END IF;
178
179 IF p_query_for = 'LOT' THEN
180 l_lot_column_list :=
181 ' mtp.organization_code, '
182 || ' msi.inventory_item_id, msi.concatenated_segments, msi.description, '
183 || ' msi.primary_uom_code, msi.secondary_uom_code, mln.lot_number, '
184 || ' mln.gen_object_id, '
185 || ' sum(ohd.primary_transaction_quantity) primary_transaction_quantity, '
186 || ' sum(ohd.secondary_transaction_quantity) secondary_transaction_quantity, '
187 || p_date_type
188 || ' date_column ';
189 l_lot_table_list :=
190 ' mtl_lot_numbers mln, mtl_parameters mtp, mtl_system_items_kfv msi, '
191 || ' mtl_onhand_quantities_detail ohd ';
192 l_lot_where_clause :=
193 ' mtp.organization_id = :b_organization_id '
194 || ' and mtp.organization_id = msi.organization_id '
195 || ' and mln.organization_id = msi.organization_id '
196 || ' and mln.inventory_item_id = msi.inventory_item_id '
197 || ' and mln.organization_id = ohd.organization_id '
198 || ' and mln.inventory_item_id = ohd.inventory_item_id '
199 || ' and mln.lot_number = ohd.lot_number '
200 || ' AND (mln.lot_number >= NVL (:b_from_lot, mln.lot_number) '
201 || ' AND mln.lot_number <= NVL (:b_to_lot, mln.lot_number) ) '
202 || ' AND (msi.concatenated_segments >= NVL(:b_from_item, msi.concatenated_segments) '
203 || ' AND msi.concatenated_segments <= NVL(:b_to_item, msi.concatenated_segments) ) ';
204 l_lot_group_by :=
205 ' msi.inventory_item_id, msi.concatenated_segments, msi.description, '
206 || ' msi.primary_uom_code, msi.secondary_uom_code, '
207 || ' mln.lot_number, mtp.organization_code, mln.gen_object_id, '
208 || p_date_type;
209
210 IF p_days_in_future IS NULL
211 AND p_days_in_past IS NULL THEN
212 l_lot_where_clause :=
213 l_lot_where_clause
214 || ' and trunc(mln.'
215 || p_date_type
216 || ' ) = trunc(sysdate) ';
217 ELSIF p_days_in_future IS NOT NULL
218 AND p_days_in_past IS NOT NULL THEN
219 l_lot_where_clause :=
220 l_lot_where_clause
221 || ' and trunc(mln.'
222 || p_date_type
223 || ' ) between trunc(sysdate) - '
224 || p_days_in_past
225 || ' and trunc(sysdate) + '
226 || p_days_in_future;
227 ELSIF p_days_in_future IS NOT NULL
228 AND p_days_in_past IS NULL THEN
229 l_lot_where_clause :=
230 l_lot_where_clause
231 || ' and trunc(mln.'
232 || p_date_type
233 || ' ) between trunc(sysdate) and trunc(sysdate) + '
234 || p_days_in_future;
235 ELSIF p_days_in_past IS NOT NULL
236 AND p_days_in_future IS NULL THEN
237 l_lot_where_clause :=
238 l_lot_where_clause
239 || ' and trunc(mln.'
240 || p_date_type
241 || ' ) between trunc(sysdate) - '
242 || p_days_in_past
243 || ' and trunc(sysdate) ';
244 END IF;
245
246 IF p_include_zero_balance = 2 THEN
247 l_lot_group_by :=
248 l_lot_group_by
249 || ' having sum(ohd.primary_transaction_quantity) > 0 ';
250 END IF;
251
252 IF l_attr_ctxt IN ('Global Data Elements', ' ') THEN
253 l_attr_context := NULL;
254 l_lot_where_clause :=
255 l_lot_where_clause
256 || ' and ( mln.lot_attribute_category = NVL(:b_attr_context, mln.lot_attribute_category) '
260 l_lot_where_clause :=
257 || ' OR mln.lot_attribute_category is NULL ) ';
258 ELSE
259 l_attr_context := l_attr_ctxt;
261 l_lot_where_clause
262 || ' and mln.lot_attribute_category = :b_attr_context ';
263 END IF;
264 /*Bug#14103573 Adding the sequnce value at the end of the event key*/
265 SELECT MTL_LOT_NOTIF_ITEM_KEY_S.NEXTVAL INTO l_date_notification_s FROM dual;
266 OPEN l_cursor
267 FOR 'select '
268 || l_lot_column_list
269 || ' from '
270 || l_lot_table_list
271 || ' where '
272 || l_lot_where_clause
273 || ' group by '
274 || l_lot_group_by
275 USING p_organization_id
276 , p_from_lot
277 , p_to_lot
278 , p_from_item
279 , p_to_item
280 , l_attr_context;
281
282 LOOP
283 FETCH l_cursor
284 INTO l_lot_rec;
285
286 EXIT WHEN l_cursor%NOTFOUND;
287 l_catg_rec_found := TRUE;
288
289 OPEN cur_item_category (p_organization_id, l_lot_rec.inventory_item_id);
290
291 FETCH cur_item_category
292 INTO l_item_catg_rec;
293
294 CLOSE cur_item_category;
295
296 IF p_category_id IS NOT NULL THEN
297 OPEN cur_category (p_organization_id
298 , l_lot_rec.inventory_item_id
299 , p_category_id
300 );
301
302 FETCH cur_category
303 INTO l_catg_rec;
304
305 CLOSE cur_category;
306
307 IF l_catg_rec.category_id IS NULL THEN
308 l_catg_rec_found := FALSE;
309 END IF;
310 END IF;
311
312 IF p_date_type = 'EXPIRATION_DATE' THEN
313 SELECT expiration_action_code
314 INTO l_expiration_action_code
315 FROM mtl_lot_numbers
316 WHERE lot_number = l_lot_rec.lot_number
317 AND organization_id = p_organization_id
318 AND inventory_item_id = l_lot_rec.inventory_item_id;
319 END IF;
320
321 IF l_catg_rec_found THEN
322 l_onhand_qty_uom :=
323 TO_CHAR (l_lot_rec.primary_transaction_quantity)
324 || ' '
325 || l_lot_rec.primary_uom_code
326 || ' '
327 || TO_CHAR (l_lot_rec.secondary_transaction_quantity)
328 || ' '
329 || l_lot_rec.secondary_uom_code;
330
331 wf_event.addparametertolist ('ORGANIZATION_CODE'
332 , l_lot_rec.organization_code
333 , l_parameter_list
334 );
335 wf_event.addparametertolist ('ORGANIZATION_ID'
336 , p_organization_id
337 , l_parameter_list
338 );
339 wf_event.addparametertolist ('ITEM_NUMBER'
340 , l_lot_rec.concatenated_segments
341 , l_parameter_list
342 );
343 wf_event.addparametertolist ('ITEM_ID'
344 , l_lot_rec.inventory_item_id
345 , l_parameter_list
346 );
347 wf_event.addparametertolist ('ITEM_CATEGORY'
348 , l_item_catg_rec.category_concat_segs
349 , l_parameter_list
350 );
351 wf_event.addparametertolist ('CATEGORY_ID'
352 , l_item_catg_rec.category_id
353 , l_parameter_list
354 );
355 wf_event.addparametertolist ('QUERY_FOR'
356 , p_query_for
357 , l_parameter_list
358 );
359 wf_event.addparametertolist ('LOT_NUMBER'
360 , l_lot_rec.lot_number
361 , l_parameter_list
362 );
363 wf_event.addparametertolist ('SERIAL_NUMBER'
364 , NULL
365 , l_parameter_list
366 );
367 wf_event.addparametertolist ('DATE_CONTEXT'
368 , l_attr_ctxt
369 , l_parameter_list
370 );
371 wf_event.addparametertolist ('DATE_TYPE'
372 , p_date_type
373 , l_parameter_list
374 );
375 wf_event.addparametertolist ('USER_DATE_TYPE'
376 , l_user_column_name
377 , l_parameter_list
378 );
379 wf_event.addparametertolist ('DATE_VALUE'
380 , l_lot_rec.date_column
381 , l_parameter_list
382 );
383 wf_event.addparametertolist ('ACTION_CODE'
384 , l_expiration_action_code
388 , NULL
385 , l_parameter_list
386 );
387 wf_event.addparametertolist ('SERIAL_STATUS'
389 , l_parameter_list
390 );
391 wf_event.addparametertolist ('ONHAND_QTY_UOM'
392 , l_onhand_qty_uom
393 , l_parameter_list
394 );
395 wf_event.addparametertolist ('TRANSACTION_ID'
396 , l_lot_rec.gen_object_id
397 , l_parameter_list
398 );
399 /*
400 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Raising Lot Event with the following parameters : ');
401 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ORGANIZATION_CODE : '||l_lot_rec.organization_code);
402 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ORGANIZATION_ID : '||to_char(p_organization_id));
403 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ITEM_NUMBER : '||l_lot_rec.concatenated_segments);
404 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ITEM_ID : '||to_char(l_lot_rec.inventory_item_id));
405 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ITEM_CATEGORY : '||l_item_catg_rec.category_concat_segs);
406 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' CATEGORY_ID : '||to_char(l_item_catg_rec.category_id));
407 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' QUERY_FOR : '||p_query_for);
408 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' LOT_NUMBER : '||l_lot_rec.lot_number);
409 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' SERIAL_NUMBER : '||NULL);
410 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' DATE_CONTEXT : '||l_attr_ctxt);
411 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' DATE_TYPE : '||p_date_type);
412 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' USER_DATE_TYPE : '||l_user_column_name);
413 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' DATE_VALUE : '||to_char(l_lot_rec.date_column, 'dd-mon-yyyy'));
414 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ACTION_CODE : '||l_expiration_action_code);
415 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' SERIAL_STATUS : '||NULL);
416 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' ONHAND_QTY_UOM : '||l_onhand_qty_uom);
417 */
418
419 fnd_file.put_line (fnd_file.log,
420 l_lot_rec.organization_code
421 || ' '
422 || l_lot_rec.concatenated_segments
423 || ' '
424 || l_lot_rec.lot_number
425 || ' '
426 || l_lot_rec.date_column
427 || ' '
428 || l_date_notification_s
429 );
430
431
432 l_user_id := FND_GLOBAL.USER_ID;
433 wf_event.RAISE
434 (p_event_name => 'oracle.apps.inv.Date.Notification'
435 , p_parameters => l_parameter_list
436 , p_event_key => TO_CHAR (p_organization_id)
437 || '+-?*'
438 || TO_CHAR (l_lot_rec.inventory_item_id)
439 || '+-?*'
440 || l_lot_rec.lot_number
441 || '+-?*'
442 || p_query_for
443 || '+-?*'
444 || l_item_catg_rec.category_id
445 || '+-?*'
446 || l_date_notification_s
447 );
448 l_parameter_list.DELETE;
449
450 OPEN cur_item_info (p_organization_id, l_lot_rec.inventory_item_id);
451
452 FETCH cur_item_info
453 INTO l_item_info_rec;
454
455 CLOSE cur_item_info;
456
457 IF l_item_info_rec.process_enabled_flag = 'Y'
458 AND NVL (l_item_info_rec.process_quality_enabled_flag, 'N') = 'Y' THEN
459
460
461
462 IF p_date_type = 'EXPIRATION_DATE' THEN
463 -- Raise oracle.apps.gmi.lotexpirydate.update and oracle.apps.gmi.lotretestdate.update
464 -- instead of oracle.apps.gmd.lotexpiry and oracle.apps.gmd.lotretest
465 wf_event.RAISE
466 (p_event_name => 'oracle.apps.gmi.lotexpirydate.update'
467 , p_event_key => TO_CHAR (p_organization_id)
468 || '-'
469 || TO_CHAR (l_lot_rec.inventory_item_id)
470 || '-'
471 || l_lot_rec.lot_number
472 || '-'
473 ||l_date_notification_s);
474 ELSIF p_date_type = 'RETEST_DATE' THEN
475 wf_event.RAISE
476 (p_event_name => 'oracle.apps.gmi.lotretestdate.update'
477 , p_event_key => TO_CHAR (p_organization_id)
478 || '-'
479 || TO_CHAR (l_lot_rec.inventory_item_id)
480 || '-'
481 || l_lot_rec.lot_number
482 || '-'
483 ||l_date_notification_s);
484 END IF;
485 END IF;
486 END IF;
487 END LOOP;
488
489 CLOSE l_cursor;
490 ELSIF p_query_for = 'SERIAL' THEN
491 l_serial_column_list :=
492 ' mtp.organization_code, '
493 || ' msi.inventory_item_id, msi.concatenated_segments, msi.description, '
494 || ' msi.primary_uom_code, msi.secondary_uom_code, msn.serial_number, '
495 || ' msn.gen_object_id, '
496 || ' msn.current_status, mfgl.meaning serial_status, '
497 || p_date_type
498 || ' date_column ';
499 l_serial_table_list :=
500 ' mtl_serial_numbers msn, mtl_parameters mtp, mtl_system_items_kfv msi, '
501 || ' mfg_lookups mfgl ';
502 l_serial_where_clause :=
503 ' mtp.organization_id = :b_organization_id '
504 || ' and mtp.organization_id = msi.organization_id '
505 || ' and msn.current_organization_id = msi.organization_id '
506 || ' and msn.inventory_item_id = msi.inventory_item_id '
507 || ' AND (msn.serial_number >= NVL (:b_from_serial, msn.serial_number) '
508 || ' AND msn.serial_number <= NVL (:b_to_serial, msn.serial_number) ) '
509 || ' AND (msi.concatenated_segments >= NVL(:b_from_item, msi.concatenated_segments) '
510 || ' AND msi.concatenated_segments <= NVL(:b_to_item, msi.concatenated_segments) ) '
511 || ' and msn.current_status = mfgl.lookup_code '
512 || ' and mfgl.lookup_type = :b_lookup_type ';
513
514 IF p_days_in_future IS NULL
515 AND p_days_in_past IS NULL THEN
516 l_serial_where_clause :=
517 l_serial_where_clause
518 || ' and trunc(msn.'
519 || p_date_type
520 || ' ) = trunc(sysdate) ';
521 ELSIF p_days_in_future IS NOT NULL
522 AND p_days_in_past IS NOT NULL THEN
523 l_serial_where_clause :=
524 l_serial_where_clause
525 || ' and trunc(msn.'
526 || p_date_type
527 || ' ) between trunc(sysdate) - '
528 || p_days_in_past
529 || ' and trunc(sysdate) + '
530 || p_days_in_future;
531 ELSIF p_days_in_future IS NOT NULL
532 AND p_days_in_past IS NULL THEN
533 l_serial_where_clause :=
534 l_serial_where_clause
535 || ' and trunc(msn.'
536 || p_date_type
537 || ' ) between trunc(sysdate) and trunc(sysdate) + '
538 || p_days_in_future;
539 ELSIF p_days_in_past IS NOT NULL
540 AND p_days_in_future IS NULL THEN
541 l_serial_where_clause :=
542 l_serial_where_clause
543 || ' and trunc(msn.'
544 || p_date_type
545 || ' ) between trunc(sysdate) - '
546 || p_days_in_past
547 || ' and trunc(sysdate) ';
548 END IF;
549
550 IF p_include_zero_balance = 2 THEN
551 l_serial_where_clause :=
552 l_serial_where_clause || ' and msn.current_status = 3 ';
553 END IF;
554
555 IF l_attr_ctxt IN ('Global Data Elements', ' ') THEN
556 l_attr_context := NULL;
557 l_serial_where_clause :=
558 l_serial_where_clause
559 || ' and ( msn.serial_attribute_category = NVL(:b_attr_context, msn.serial_attribute_category) '
560 || ' OR msn.serial_attribute_category is NULL ) ';
561 ELSE
562 l_attr_context := l_attr_ctxt;
563 l_serial_where_clause :=
564 l_serial_where_clause
565 || ' and msn.serial_attribute_category = :b_attr_context ';
566 END IF;
567 /*Bug#14103573 Adding the sequnce value at the end of the event key*/
568 SELECT MTL_LOT_NOTIF_ITEM_KEY_S.NEXTVAL INTO l_date_notification_s FROM dual;
569 OPEN l_cursor
570 FOR 'select '
571 || l_serial_column_list
572 || ' from '
573 || l_serial_table_list
574 || ' where '
575 || l_serial_where_clause
576 USING p_organization_id
577 , p_from_serial
578 , p_to_serial
579 , p_from_item
580 , p_to_item
581 , l_lookup_type
582 , l_attr_context;
583
584 LOOP
585 FETCH l_cursor
586 INTO l_serial_rec;
587
588 EXIT WHEN l_cursor%NOTFOUND;
589 l_catg_rec_found := TRUE;
590
591 OPEN cur_item_category (p_organization_id, l_lot_rec.inventory_item_id);
592
593 FETCH cur_item_category
594 INTO l_item_catg_rec;
595
596 CLOSE cur_item_category;
597
598 IF p_category_id IS NOT NULL THEN
599 OPEN cur_category (p_organization_id
600 , l_serial_rec.inventory_item_id
601 , p_category_id
602 );
603
604 FETCH cur_category
605 INTO l_catg_rec;
606
607 CLOSE cur_category;
608
609 IF l_catg_rec.category_id IS NULL THEN
610 l_catg_rec_found := FALSE;
611 END IF;
612 END IF;
613
614 IF l_catg_rec_found THEN
615 wf_event.addparametertolist ('ORGANIZATION_CODE'
616 , l_serial_rec.organization_code
617 , l_parameter_list
618 );
619 wf_event.addparametertolist ('ORGANIZATION_ID'
620 , p_organization_id
621 , l_parameter_list
622 );
623 wf_event.addparametertolist ('ITEM_NUMBER'
624 , l_serial_rec.concatenated_segments
625 , l_parameter_list
626 );
627 wf_event.addparametertolist ('ITEM_ID'
628 , l_serial_rec.inventory_item_id
629 , l_parameter_list
630 );
631 wf_event.addparametertolist ('ITEM_CATEGORY'
632 , l_item_catg_rec.category_concat_segs
633 , l_parameter_list
634 );
635 wf_event.addparametertolist ('CATEGORY_ID'
636 , l_item_catg_rec.category_id
637 , l_parameter_list
638 );
639 wf_event.addparametertolist ('QUERY_FOR'
640 , p_query_for
641 , l_parameter_list
642 );
643 wf_event.addparametertolist ('LOT_NUMBER'
644 , NULL
645 , l_parameter_list
646 );
647 wf_event.addparametertolist ('SERIAL_NUMBER'
648 , l_serial_rec.serial_number
649 , l_parameter_list
650 );
651 wf_event.addparametertolist ('DATE_CONTEXT'
652 , l_attr_ctxt
653 , l_parameter_list
654 );
655 wf_event.addparametertolist ('DATE_TYPE'
656 , p_date_type
657 , l_parameter_list
658 );
659 wf_event.addparametertolist ('USER_DATE_TYPE'
660 , l_user_column_name
661 , l_parameter_list
662 );
663 wf_event.addparametertolist ('DATE_VALUE'
664 , l_serial_rec.date_column
665 , l_parameter_list
666 );
667 wf_event.addparametertolist ('ACTION_CODE'
668 , NULL
669 , l_parameter_list
670 );
671 wf_event.addparametertolist ('SERIAL_STATUS'
672 , l_serial_rec.serial_status
673 , l_parameter_list
674 );
675 wf_event.addparametertolist ('ONHAND_QTY_UOM'
676 , NULL
677 , l_parameter_list
678 );
679 wf_event.addparametertolist ('TRANSACTION_ID'
680 , l_serial_rec.gen_object_id
681 , l_parameter_list
682 );
683
684 l_user_id := FND_GLOBAL.USER_ID;
685
686 fnd_file.put_line (fnd_file.log,
687 l_serial_rec.organization_code
688 || ' '
689 || l_serial_rec.concatenated_segments
690 || ' '
691 || l_serial_rec.serial_number
692 || ' '
693 || l_serial_rec.date_column
694 || ' '
695 || l_date_notification_s
696 );
697
698 wf_event.RAISE
699 (p_event_name => 'oracle.apps.inv.Date.Notification'
700 , p_parameters => l_parameter_list
701 , p_event_key => TO_CHAR (p_organization_id)
702 || '+-?*'
703 || TO_CHAR (l_serial_rec.inventory_item_id)
704 || '+-?*'
705 || l_serial_rec.serial_number
706 || '+-?*'
707 || p_query_for
708 || '+-?*'
709 || l_item_catg_rec.category_id
710 || '+-?*'
711 || l_date_notification_s
712 );
713
714 l_parameter_list.DELETE;
715 END IF;
716 END LOOP;
717
718 CLOSE l_cursor;
719 END IF;
720
721 END lot_serial_date_notify_cp;
722
723 END INV_LOT_SERIAL_DATE_EVENT_PKG;