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