[Home] [Help]
PACKAGE BODY: APPS.INV_XML_REPORTS
Source
1 PACKAGE BODY inv_xml_reports AS
2 /* $Header: INVXREPB.pls 120.6 2006/09/21 14:40:33 jsrivast noship $ */
3 PROCEDURE lot_inventory_report (
4 errbuf OUT NOCOPY VARCHAR2
5 , retcode OUT NOCOPY VARCHAR2
6 , p_organization_id IN NUMBER
7 , p_from_item IN VARCHAR2
8 , p_to_item IN VARCHAR2
9 , p_from_subinv IN VARCHAR2
10 , p_to_subinv IN VARCHAR2) IS
11
12 qryctx DBMS_XMLGEN.ctxhandle;
13 v_result CLOB;
14 QUERY VARCHAR2 (32000);
15 BEGIN
16 QUERY :=
17 'SELECT mp.organization_code ORG_CODE
18 , sysdate REP_DATE, ''' || nvl(p_from_item, ' ') || ''' p_from_item,''' || nvl(p_to_item, ' ') || ''' p_to_item,
19 ''' || nvl(p_from_subinv, ' ') || ''' p_from_subinv,''' || nvl(p_to_subinv, ' ') || ''' p_to_subinv
20 , cursor(select mss.secondary_inventory_name SUBINV
21 ,cursor(select msi.concatenated_segments ITEM_NO
22 , msi.description ITEM_DESC
23 ,cursor(select mln.lot_number LOT
24 , fnd_date.date_to_displayDT(mln.origination_date) ORIG_DATE
25 , fnd_date.date_to_displayDT(mln.expiration_date) EXP_DATE
26 , mil.concatenated_segments LOCATOR
27 , sum(ohd.primary_transaction_quantity) QTY
28 , msi.primary_uom_code UOM
29 , sum(nvl(ohd.secondary_transaction_quantity,0)) SEC_QTY
30 , msi.secondary_uom_code SEC_UOM
31 , mms.status_code STATUS
32 , mln.grade_code GRADE
33 from mtl_lot_numbers mln,
34 mtl_item_locations_kfv mil,
35 mtl_onhand_quantities_detail ohd,
36 mtl_material_statuses_vl mms
37 WHERE msi.inventory_item_id = mln.inventory_item_id
38 AND msi.organization_id = mln.organization_id
39 AND mln.status_id = mms.status_id(+)
40 AND ohd.organization_id = mln.organization_id
41 AND ohd.inventory_item_id = mln.inventory_item_id
42 AND ohd.lot_number = mln.lot_number
43 AND ohd.locator_id = mil.inventory_location_id(+)
44 and ohd.organization_id = mp.organization_id
45 and ohd.subinventory_code = mss.secondary_inventory_name
46 and ohd.organization_id = msi.organization_id
47 and ohd.inventory_item_id = msi.inventory_item_id
48 group by mln.lot_number , mln.origination_date,mln.expiration_date
49 , mil.concatenated_segments
50 , msi.primary_uom_code
51 , msi.secondary_uom_code
52 , mms.status_code
53 , mln.grade_code
54 ) as LOT_DETAILS
55 FROM mtl_system_items_kfv msi
56 WHERE msi.organization_id = mss.organization_id
57 and msi.organization_id = mp.organization_id
58 and msi.lot_control_code = 2';
59
60 IF ( p_from_item IS NOT NULL
61 AND p_to_item IS NOT NULL) THEN
62 QUERY := QUERY
63 || ' AND (msi.concatenated_segments >= '''
64 || p_from_item
65 || ''' AND msi.concatenated_segments <= '''
66 || p_to_item
67 || ''')';
68 ELSIF (p_from_item IS NOT NULL) THEN
69 QUERY := QUERY || ' AND msi.concatenated_segments >= ''' || p_from_item || '''';
70 ELSIF (p_to_item IS NOT NULL) THEN
71 QUERY := QUERY || ' AND msi.concatenated_segments <= ''' || p_to_item || '''';
72 END IF;
73
74 QUERY := QUERY
75 || ' and exists (select '' x '' from mtl_onhand_quantities_detail ohd1
76 where ohd1.organization_id = mp.organization_id
77 and ohd1.subinventory_code = mss.secondary_inventory_name
78 and ohd1.organization_id = msi.organization_id
79 and ohd1.inventory_item_id = msi.inventory_item_id) ) AS ITEM_DETAILS
80 FROM mtl_secondary_inventories mss
81 WHERE mss.organization_id = mp.organization_id';
82
83 IF ( p_from_subinv IS NOT NULL
84 AND p_to_subinv IS NOT NULL) THEN
85 QUERY := QUERY
86 || ' AND (mss.secondary_inventory_name >= '''
87 || p_from_subinv
88 || ''' AND mss.secondary_inventory_name <= '''
89 || p_to_subinv
90 || ''')';
91 ELSIF (p_from_subinv IS NOT NULL) THEN
92 QUERY := QUERY || ' AND mss.secondary_inventory_name >= ''' || p_from_subinv || '''';
93 ELSIF (p_to_subinv IS NOT NULL) THEN
94 QUERY := QUERY || ' AND mss.secondary_inventory_name <= ''' || p_to_subinv || '''';
95 END IF;
96
97 QUERY := QUERY
98 || ' AND EXISTS ( SELECT ''x''
99 FROM mtl_onhand_quantities_detail d
100 WHERE d.organization_id = mss.organization_id
101 AND d.subinventory_code = mss.secondary_inventory_name
102 and d.organization_id = mp.organization_id )) AS SUBINV_DETAILS
103 FROM mtl_parameters mp
104 WHERE mp.organization_id = '
105 || p_organization_id;
106
107 fnd_file.put_line (fnd_file.LOG, query);
108
109 qryctx := DBMS_XMLGEN.newcontext (QUERY);
110
111 LOOP
112 -- now get the result
113 v_result := DBMS_XMLGEN.getxml (qryctx);
114
115 -- if there were no rows processed, then quit
116 IF v_result IS NULL THEN
117 EXIT;
118 END IF;
119
120 xml_transfer (p_xml_clob => v_result);
121 END LOOP;
122
123 --close context
124 DBMS_XMLGEN.closecontext (qryctx);
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure LOT_INVENTORY_REPORT ' || SQLCODE || ' ' || SQLERRM);
129 END lot_inventory_report;
130
131 PROCEDURE lot_master_report (
132 errbuf OUT NOCOPY VARCHAR2
133 , retcode OUT NOCOPY VARCHAR2
134 , p_organization_id IN NUMBER
135 , p_from_item IN VARCHAR2
136 , p_to_item IN VARCHAR2) IS
137
138 qryctx DBMS_XMLGEN.ctxhandle;
139 v_result CLOB;
140 QUERY VARCHAR2 (32000);
141 BEGIN
142 QUERY :=
143 ' SELECT mp.organization_code org,
144 sysdate rep_date,''' || nvl(p_from_item, ' ') || ''' p_from_item,''' || nvl(p_to_item, ' ') || ''' p_to_item ,
145 cursor(select msi.concatenated_segments Item_number
146 , msi.description item_desc
147 , cursor(select mln.lot_number
148 , mln.description
149 , mln.grade_code
150 , mln.expiration_action_code
151 , fnd_date.date_to_displayDT(mln.expiration_action_date) expiration_action_date
152 , mln.origination_type
153 , mfgl.meaning lot_origination
154 , fnd_date.date_to_displayDT(mln.origination_date) origination_date
155 , fnd_date.date_to_displayDT(mln.expiration_date) expiration_date
156 , fnd_date.date_to_displayDT(mln.retest_date) retest_date
157 , fnd_date.date_to_displayDT(mln.maturity_date) maturity_date
158 , fnd_date.date_to_displayDT(mln.hold_date) hold_date
159 , mln.parent_lot_number
160 , mln.vendor_name
161 , mln.supplier_lot_number
162 FROM mtl_lot_numbers mln
163 , mfg_lookups mfgl
164 WHERE msi.organization_id = mln.organization_id
165 AND msi.inventory_item_id = mln.inventory_item_id
166 AND mln.origination_type = mfgl.LOOKUP_CODE(+)
167 AND mfgl.lookup_type(+) = ''MTL_LOT_ORIGINATION_TYPE'') as LOT_DETAILS
168 FROM mtl_system_items_kfv msi
169 WHERE msi.organization_id = mp.organization_id ';
170
171 IF ( p_from_item IS NOT NULL
172 AND p_to_item IS NOT NULL) THEN
173 QUERY := QUERY
174 || ' AND (msi.concatenated_segments >= '''
175 || p_from_item
176 || ''' AND msi.concatenated_segments <= '''
177 || p_to_item
178 || ''')';
179 ELSIF (p_from_item IS NOT NULL) THEN
180 QUERY := QUERY || ' AND msi.concatenated_segments >= ''' || p_from_item || '''';
181 ELSIF (p_to_item IS NOT NULL) THEN
182 QUERY := QUERY || ' AND msi.concatenated_segments <= ''' || p_to_item || '''';
183 END IF;
184
185 QUERY := QUERY
186 || ' and msi.lot_control_code = 2 and exists ( select mln1.inventory_item_id from mtl_lot_numbers mln1 where msi.organization_id = mln1.organization_id
187 AND msi.inventory_item_id = mln1.inventory_item_id and mln1.organization_id = mp.organization_id)
188 ORDER BY msi.concatenated_segments) as ITEM_DETAILS
189 from mtl_parameters mp
190 where mp.organization_id = '
191 || p_organization_id;
192
193 fnd_file.put_line (fnd_file.LOG, query);
194 qryctx := DBMS_XMLGEN.newcontext (QUERY);
195
196 LOOP
197 -- now get the result
198 v_result := DBMS_XMLGEN.getxml (qryctx);
199
200 -- if there were no rows processed, then quit
201 IF v_result IS NULL THEN
202 EXIT;
203 END IF;
204
205 xml_transfer (p_xml_clob => v_result);
206 END LOOP;
207
208 --close context
209 DBMS_XMLGEN.closecontext (qryctx);
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure LOT_MASTER_REPORT ' || SQLCODE || ' ' || SQLERRM);
214 END lot_master_report;
215
216 PROCEDURE mat_status_def_report (
217 errbuf OUT NOCOPY VARCHAR2
218 , retcode OUT NOCOPY VARCHAR2
219 , p_from_status IN VARCHAR2
220 , p_to_status IN VARCHAR2
221 , p_sort_order IN NUMBER) IS
222
223 qryctx DBMS_XMLGEN.ctxhandle;
224 v_result CLOB;
225 QUERY VARCHAR2 (32000);
226 l_order VARCHAR2 (10);
227 l_range VARCHAR2 (170) := NULL;
228 l_order_display VARCHAR2(80);
229 BEGIN
230 IF (p_sort_order = 2) THEN
231 l_order := 'DESC';
232 ELSE
233 l_order := 'ASC';
234 END IF;
235
236 SELECT meaning
237 INTO l_order_display
238 FROM mfg_lookups
239 WHERE lookup_type = 'INV_SRS_ASC_DESC'
240 AND enabled_flag = 'Y'
241 AND lookup_code = NVL(p_sort_order, 1);
242
243 /*
244 IF ( p_from_status IS NOT NULL
245 AND p_to_status IS NOT NULL) THEN
246 l_range := p_from_status || ' - ' || p_to_status;
247 ELSIF (p_from_status IS NOT NULL) THEN
248 l_range := 'FROM STATUS CODE - ' || p_from_status;
249 ELSIF (p_to_status IS NOT NULL) THEN
250 l_range := 'TILL STATUS CODE - ' || p_to_status;
251 ELSE
252 l_range := 'ALL';
253 END IF;
254 */
255
256 QUERY :=
257 'SELECT sysdate rep_date,''' || l_order_display || ''' ORDER_BY,''' ||
258 nvl(p_from_status, ' ') || ''' p_from_status,''' ||
259 nvl(p_to_status, ' ') || ''' p_to_status
260 , mv.status_code
261 , mv.description
262 , ml1.meaning enabled_flag_value
263 , ml2.meaning allow_reservations_flag_value
264 , ml3.meaning include_in_atp_flag_value
265 , ml4.meaning nettable_flag_value
266 , ml5.meaning subinventory_usage_flag_value
267 , ml6.meaning locator_usage_flag_value
268 , ml7.meaning lot_usage_flag_value
269 , ml8.meaning serial_usage_flag_value
270 , cursor (select tx.transaction_description
271 from mtl_status_control_v tx
272 where tx.status_id = mv.status_id and tx.is_allowed = 1
273 order by tx.transaction_description) as allowed_transactions
274 , cursor (select tx.transaction_description
275 from mtl_status_control_v tx
276 where tx.status_id = mv.status_id and tx.is_allowed = 2
277 order by tx.transaction_description) as disallowed_transactions
278 FROM mtl_material_statuses_vl mv
279 , mfg_lookups ml1
280 , mfg_lookups ml2
284 , mfg_lookups ml6
281 , mfg_lookups ml3
282 , mfg_lookups ml4
283 , mfg_lookups ml5
285 , mfg_lookups ml7
286 , mfg_lookups ml8
287 WHERE ml1.lookup_code = mv.enabled_flag
288 and ml1.lookup_type = ''SYS_YES_NO''
289 and ml2.lookup_code = mv.reservable_type
290 and ml2.lookup_type = ''SYS_YES_NO''
291 and ml3.lookup_code = mv.inventory_atp_code
292 and ml3.lookup_type = ''SYS_YES_NO''
293 and ml4.lookup_code = mv.availability_type
294 and ml4.lookup_type = ''SYS_YES_NO''
295 and ml5.lookup_code = mv.zone_control
296 and ml5.lookup_type = ''SYS_YES_NO''
297 and ml6.lookup_code = mv.locator_control
298 and ml6.lookup_type = ''SYS_YES_NO''
299 and ml7.lookup_code = mv.lot_control
300 and ml7.lookup_type = ''SYS_YES_NO''
301 and ml8.lookup_code = mv.serial_control
302 and ml8.lookup_type = ''SYS_YES_NO''';
303
304 IF ( p_from_status IS NOT NULL
305 AND p_to_status IS NOT NULL) THEN
306 QUERY := QUERY
307 || 'AND (mv.status_code >= '''
308 || p_from_status
309 || ''' AND mv.status_code <= '''
310 || p_to_status
311 || ''')';
312 ELSIF (p_from_status IS NOT NULL) THEN
313 QUERY := QUERY || 'AND mv.status_code >= ''' || p_from_status || '''';
314 ELSIF (p_to_status IS NOT NULL) THEN
315 QUERY := QUERY || 'AND mv.status_code <= ''' || p_to_status || '''';
316 END IF;
317
318 QUERY := QUERY || ' ORDER BY mv.status_code ' || l_order;
319
320 fnd_file.put_line (fnd_file.LOG, query);
321
322 qryctx := DBMS_XMLGEN.newcontext (QUERY);
323
324 LOOP
325 -- now get the result
326 v_result := DBMS_XMLGEN.getxml (qryctx);
327
328 -- if there were no rows processed, then quit
329 IF v_result IS NULL THEN
330 EXIT;
331 END IF;
332
333 xml_transfer (p_xml_clob => v_result);
334 END LOOP;
335
336 --close context
337 DBMS_XMLGEN.closecontext (qryctx);
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure MAT_STATUS_DEF_REPORT ' || SQLCODE || ' ' || SQLERRM);
342 END mat_status_def_report;
343
344 PROCEDURE grade_change_history_report (
345 errbuf OUT NOCOPY VARCHAR2
346 , retcode OUT NOCOPY VARCHAR2
347 , p_organization_id IN NUMBER
348 , p_item_id IN NUMBER
349 , p_from_lot IN VARCHAR2
350 , p_to_lot IN VARCHAR2
351 , p_from_date IN VARCHAR2
352 , p_to_date IN VARCHAR2) IS
353
354 qryctx DBMS_XMLGEN.ctxhandle;
355 v_result CLOB;
356 QUERY VARCHAR2 (32000);
357 p_from_dt DATE := fnd_date.canonical_to_date(p_from_date);
358 p_to_dt DATE := fnd_date.canonical_to_date(p_to_date);
359 BEGIN
360 /* Jalaj Srivastava Bug 4998256
361 get the meaning of update_method and not the code */
362 QUERY :=
363 'SELECT sysdate rep_date
364 , ood.organization_code org_code
365 , ood.organization_name org_name
366 , msi.concatenated_segments Item_No
367 , msi.primary_uom_code pri_uom
368 , DECODE(msi.tracking_quantity_ind,''PS'',msi.secondary_uom_code) sec_uom /*Bug#5436402*/
369 , cursor ( select mlgh.lot_number lot_no
370 , msi.default_grade def_grade
371 , mlgh.old_grade_code old_grade
372 , mlgh.new_grade_code new_grade
373 , DECODE (mlgh.from_mobile_apps_flag, ''Y'', ''Mobile'', ''N'', ''Desktop'') updated_from
374 , fnd_date.date_to_displayDT(mlgh.grade_update_date) upd_date
375 , fnd.user_name user_name
376 , ml.meaning upd_method
377 , mlgh.primary_quantity pri_qty
378 , mlgh.secondary_quantity sec_qty
379 , mg1.description from_desc
380 , mg2.description to_desc
381 , mtr.reason_name reason
382 from mtl_lot_grade_history mlgh
383 , mtl_grades_vl mg1
384 , mtl_grades_vl mg2
385 , mtl_transaction_reasons mtr
386 , fnd_user fnd
387 , mfg_lookups ml
388 where msi.organization_id = mlgh.organization_id
389 AND msi.inventory_item_id = mlgh.inventory_item_id
390 AND mlgh.old_grade_code = mg1.grade_code
391 AND mlgh.new_grade_code = mg2.grade_code
392 AND mlgh.created_by = fnd.user_id
393 AND mlgh.update_reason_id = mtr.reason_id(+)
394 AND ml.lookup_type = ''MTL_STATUS_UPDATE_METHOD''
395 AND ml.lookup_code = mlgh.update_method ';
396
397 IF ( p_from_lot IS NOT NULL
398 AND p_to_lot IS NOT NULL) THEN
399 QUERY := QUERY
400 || 'AND (mlgh.lot_number >= '''
401 || p_from_lot
402 || ''' AND mlgh.lot_number <= '''
403 || p_to_lot
404 || ''')';
405 ELSIF (p_from_lot IS NOT NULL) THEN
406 QUERY := QUERY || 'AND mlgh.lot_number >= ''' || p_from_lot || '''';
407 ELSIF (p_to_lot IS NOT NULL) THEN
408 QUERY := QUERY || 'AND mlgh.lot_number <= ''' || p_to_lot || '''';
409 END IF;
410
411 IF ( p_from_date IS NOT NULL
412 AND p_to_date IS NOT NULL) THEN
413 QUERY := QUERY
414 || 'AND (mlgh.grade_update_date >= '''
415 || p_from_dt
416 || ''' AND mlgh.grade_update_date <= '''
417 || p_to_dt
418 || ''')';
422 QUERY := QUERY || 'AND mlgh.grade_update_date <= ''' || p_to_date || '''';
419 ELSIF (p_from_date IS NOT NULL) THEN
420 QUERY := QUERY || 'AND mlgh.grade_update_date >= ''' || p_from_date || '''';
421 ELSIF (p_to_date IS NOT NULL) THEN
423 END IF;
424
425 QUERY := QUERY
426 || ' order by mlgh.lot_number, mlgh.grade_update_date) as Lot_details
427 FROM org_organization_definitions ood,
428 mtl_system_items_kfv msi
429 WHERE ood.organization_id = msi.organization_id
430 AND ood.organization_id = '''
431 || p_organization_id
432 || ''''
433 || '
434 AND msi.inventory_item_id = '''
435 || p_item_id
436 || ''''
437 || '
438 ORDER BY msi.concatenated_segments';
439
440 fnd_file.put_line (fnd_file.LOG, query);
441
442 qryctx := DBMS_XMLGEN.newcontext (QUERY);
443
444 LOOP
445 -- now get the result
446 v_result := DBMS_XMLGEN.getxml (qryctx);
447
448 -- if there were no rows processed, then quit
449 IF v_result IS NULL THEN
450 EXIT;
451 END IF;
452
453 xml_transfer (p_xml_clob => v_result);
454 END LOOP;
455
456 --close context
457 DBMS_XMLGEN.closecontext (qryctx);
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure GRADE_CHANGE_HISTORY_REPORT ' || SQLCODE || ' ' || SQLERRM);
462 END grade_change_history_report;
463
464 /* ***************************************************************
465 * NAME
466 * PROCEDURE - xml_transfer
467 * PARAMETERS
468 * DESCRIPTION
469 * Procedure used provide the XML as output of the concurrent program.
470 * HISTORY
471 * Namit 31Mar05 - Initial Version
472 *************************************************************** */
473 PROCEDURE xml_transfer (
474 p_xml_clob IN CLOB) IS
475
476 l_file CLOB;
477 file_varchar2 VARCHAR2 (4000);
478 l_len NUMBER;
479 l_limit NUMBER;
480 BEGIN
481 l_file := p_xml_clob;
482 l_limit := 1;
483 l_len := DBMS_LOB.getlength (l_file);
484
485 LOOP
486 IF l_len > l_limit THEN
487 file_varchar2 := DBMS_LOB.SUBSTR (l_file, 4000, l_limit);
488 fnd_file.put (fnd_file.output, file_varchar2);
489 --fnd_file.put (fnd_file.LOG, file_varchar2);
490 file_varchar2 := NULL;
491 l_limit := l_limit + 4000;
492 ELSE
493 file_varchar2 := DBMS_LOB.SUBSTR (l_file, 4000, l_limit);
494 fnd_file.put (fnd_file.output, file_varchar2);
495 --fnd_file.put (fnd_file.LOG, file_varchar2);
496 file_varchar2 := NULL;
497 EXIT;
498 END IF;
499 END LOOP;
500 EXCEPTION
501 WHEN OTHERS THEN
502 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure XML_TRANSFER ' || SQLCODE || ' ' || SQLERRM);
503 END xml_transfer;
504 END inv_xml_reports;