eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FLM_EKB_SUMMARY_UTIL

Source


4 Summary's Page Detail table*/
1 PACKAGE BODY FLM_EKB_SUMMARY_UTIL AS
2 /* $Header: flmeksub.pls 120.6.12020000.2 2012/07/13 11:02:47 sisankar ship $ */
3 /*The following function will retrieve the value of document number for EKanban
5 FUNCTION get_doc_num(p_doc_type IN NUMBER,
6                      p_doc_header_id IN NUMBER,
7                      p_doc_detail_id IN NUMBER)
8 RETURN VARCHAR2
9 IS
10     l_doc_num VARCHAR2(80);
11 Begin
12     IF p_doc_type = 1 THEN  /*PO*/
13       SELECT h.segment1
14       INTO l_doc_num
15       FROM po_distributions_all d,
16            po_headers_all h
17       WHERE d.po_distribution_id =  p_doc_detail_id
18       AND h.po_header_id = d.po_header_id;
19 
20     ELSIF p_doc_type = 2 THEN  /*Blanket Release*/
21       SELECT h.segment1
22       INTO l_doc_num
23       FROM po_distributions_all d,
24            po_headers_all h
25       WHERE d.po_distribution_id =  p_doc_detail_id
26       AND h.po_header_id = d.po_header_id;
27 
28     ELSIF p_doc_type = 3 THEN  /*Internal Req*/
29       SELECT h.segment1
30       INTO l_doc_num
31       FROM po_requisition_headers_all h,
32            po_requisition_lines_all l
33       WHERE l.requisition_line_id =  p_doc_detail_id
34       AND h.requisition_header_id = l.requisition_header_id;
35 
36     ELSIF p_doc_type = 4 THEN  /*Move Order*/
37       SELECT h.request_number
38       INTO l_doc_num
39       FROM mtl_txn_request_headers h,
40            mtl_txn_request_lines l
41       WHERE  l.line_id   = p_doc_detail_id
42       AND    h.header_id = l.header_id;
43 
44     ELSE /* Wip Discrete Job, Rep Schedule, Flow Schedule, Lot job */
45       SELECT h.wip_entity_name
46       INTO l_doc_num
47       FROM wip_entities h
48       WHERE h.wip_entity_id = p_doc_header_id;
49     END IF;
50 
51     return l_doc_num;
52 END get_doc_num;
53 
54 /*The following function will retrieve the creation date of document for a kanban card*/
55 FUNCTION get_doc_creation_date(p_doc_type IN NUMBER,
56                      p_doc_header_id IN NUMBER,
57                      p_doc_detail_id IN NUMBER)
58 RETURN VARCHAR2
59 IS
60     l_doc_creation_date DATE;
61 Begin
62     IF p_doc_type = 1 THEN  /*PO*/
63       SELECT h.creation_date
64       INTO l_doc_creation_date
65       FROM po_distributions_all d,
66            po_headers_all h
67       WHERE d.po_distribution_id =  p_doc_detail_id
68       AND h.po_header_id = d.po_header_id;
69 
70     ELSIF p_doc_type = 2 THEN  /*Blanket Release*/
71       SELECT h.creation_date
72       INTO l_doc_creation_date
73       FROM po_distributions_all d,
74            po_headers_all h
75       WHERE d.po_distribution_id =  p_doc_detail_id
76       AND h.po_header_id = d.po_header_id;
77 
78     ELSIF p_doc_type = 3 THEN  /*Internal Req*/
79       SELECT h.creation_date
80       INTO l_doc_creation_date
81       FROM po_requisition_headers_all h,
82            po_requisition_lines_all l
83       WHERE l.requisition_line_id =  p_doc_detail_id
84       AND h.requisition_header_id = l.requisition_header_id;
85 
86     ELSIF p_doc_type = 4 THEN  /*Move Order*/
87       SELECT h.creation_date
88       INTO l_doc_creation_date
89       FROM mtl_txn_request_headers h,
90            mtl_txn_request_lines l
91       WHERE  l.line_id   = p_doc_detail_id
92       AND    h.header_id = l.header_id;
93 
97       FROM wip_entities h
94     ELSE /* Wip Discrete Job, Rep Schedule, Flow Schedule, Lot job */
95       SELECT h.creation_date
96       INTO l_doc_creation_date
98       WHERE h.wip_entity_id = p_doc_header_id;
99     END IF;
100 
101     return l_doc_creation_date;
102 END get_doc_creation_date;
103 
104 /*The following function will retrieve the Query for KanbanSummaryVO*/
105 FUNCTION get_summary_query(p_org_id IN NUMBER,
106                            p_supplier_id IN NUMBER,
107                            p_supplier_site_id IN NUMBER)
108 RETURN VARCHAR2
109 IS
110 l_outer_stmt varchar2(8000) := 'select pull_sequence_id,
111 item_name,
112 organization_code,
113 destination,
114 source_type,
115 source_type_name,
116 decode(count(*),1,max(src_inner),''Multiple'') source,
117 inv_health_status,
118 inv_health_name,
119 planner_code,
120 buyer_name,
121 max(move_status) move_status,
122 sum(total_inner) number_of_cards';
123 
124 l_inner_stmt varchar2(4000) := 'select mkc.pull_sequence_id,
125 msik.concatenated_segments item_name,
126 msik.planner_code,
127 panv.full_name buyer_name,
128 mp2.organization_code,
129 concat(mkc.subinventory_name,concat('':'', destloc.concatenated_segments)) destination,
130 mkc.source_type source_type,
131 ml.meaning source_type_name,
132 decode(mkc.source_type, 1, mp.organization_code, 2,psv.vendor_name,
133 3,concat(mkc.source_subinventory, concat('':'', srcloc.concatenated_segments)),null) src_inner,
134 mkps.inv_health_status,
135 ml2.meaning inv_health_name,
136 max(mkc.move_status) move_status,
137 count(*) total_inner';
138 
139 l_inner_where varchar2(2000) := ' from mtl_kanban_cards mkc,
140 mtl_kanban_pull_sequences mkps,
141 mtl_item_locations_kfv srcloc,
142 mtl_item_locations_kfv destloc,
143 mfg_lookups ml,
144 mfg_lookups ml2,
145 mtl_parameters mp,
146 mtl_parameters mp2,
147 po_suppliers_val_v psv,
148 mtl_system_items_kfv msik,
149 po_agents_name_v panv
150 where mkc.inventory_item_id = msik.inventory_item_id
151 and mkc.organization_id = msik.organization_id
152 and mp2.organization_id = mkc.organization_id
153 and mkc.pull_sequence_id = mkps.pull_sequence_id
154 and mkc.inventory_item_id = mkps.inventory_item_id
155 and mkc.organization_id = mkps.organization_id
156 and mkc.card_status <> 3
157 and mkps.kanban_plan_id = -1
158 and mkc.subinventory_name = mkps.subinventory_name
159 and ml.lookup_type = ''MTL_KANBAN_SOURCE_TYPE''
160 and ml.lookup_code = mkc.source_type
161 and ml2.lookup_type (+) = ''MTL_KANBAN_INV_HEALTH_STATUS''
162 and ml2.lookup_code (+) = mkps.inv_health_status
163 and nvl(mkc.locator_id, -999) = nvl(mkps.locator_id, -999)
164 and msik.buyer_id = panv.buyer_id (+)
165 and srcloc.inventory_location_id (+)=  mkc.source_locator_id
166 and srcloc.organization_id (+)= mkc.source_organization_id
167 and destloc.inventory_location_id (+)= mkc.locator_id
168 and destloc.organization_id (+)= mkc.organization_id
169 and mp.organization_id (+)= mkc.source_organization_id
170 and psv.vendor_id (+)= mkc.supplier_id ';
171 
172 l_inner_group_by varchar2(500) := ' group by mkc.pull_sequence_id, msik.concatenated_segments,
173 mkc.source_type, mkc.subinventory_name, destloc.concatenated_segments,
174 mp.organization_code,psv.vendor_name, mkc.source_subinventory,
175 srcloc.concatenated_segments,mkps.inv_health_status,msik.planner_code,
176 panv.full_name,ml.meaning, ml2.meaning, mp2.organization_code';
177 
178 l_inner_dynamic varchar2(500) := '';
179 l_summary_query VARCHAR2(8000);
180 l_attr_num number := 1;
181 l_org_id number := null;
182 l_supplier_id number:= null;
183 l_supplier_site_id number:= null;
184 
185 BEGIN
186   l_org_id := p_org_id;
187   l_supplier_id := p_supplier_id;
188   l_supplier_site_id := p_supplier_site_id;
189 -- loop through all the supply status in mfg_lookups
190   for c_statusRec in (select lookup_code
191       from mfg_lookups
192       where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
193       order by lookup_code) loop
194       l_outer_stmt := l_outer_stmt ||',sum(inner_'|| l_attr_num ||') attribute'|| l_attr_num;
195       l_inner_stmt := l_inner_stmt ||',sum(decode(supply_status,'|| c_statusRec.lookup_code || ',1)) inner_' ||  l_attr_num;
196       l_attr_num := l_attr_num + 1;
197   end loop;
198 
199   while l_attr_num <= 20 loop
200       l_outer_stmt := l_outer_stmt ||' ,' || l_attr_num || ' attribute' || l_attr_num;
201       l_attr_num := l_attr_num + 1;
202   end loop;
203 
204   IF l_org_id IS NOT NULL then
205      l_inner_dynamic := l_inner_dynamic || ' and mkc.organization_id = ' || l_org_id;
206   END IF;
207 
208   IF l_supplier_id  IS NOT NULL then
209      l_inner_dynamic := l_inner_dynamic || ' and mkc.supplier_id = '|| l_supplier_id;
210   END IF;
211 
212   IF l_supplier_site_id  IS NOT NULL then
213      l_inner_dynamic := l_inner_dynamic || ' and mkc.supplier_site_id = ' || l_supplier_site_id;
214   END IF;
215 
216   l_inner_stmt := l_inner_stmt || l_inner_where || l_inner_dynamic || l_inner_group_by;
217 
218   l_summary_query := l_outer_stmt || ' from ('|| l_inner_stmt ||') group by pull_sequence_id,
219   item_name, destination, source_type, source_type_name,
220   inv_health_status, inv_health_name, planner_code,buyer_name,organization_code';
221 
222 --dbms_output.put_line(l_summary_query);
223    return l_summary_query;
224 END get_summary_query;
225 
226 
227 
228 
229 /*The following function will retrieve the Query for MultipleSupplierVO*/
230 /*Initially the below method, will only return SQL that display
231 multiple sources for Supplier Type Kanban Cards. However, in bug 12606969,
235 end of the development cycle, I didn't change the signature of the method to ensure
232 there is a change in design requirement where it requires SQL to return multiple
233 sources for Other Source Type Kanban Cards too. Therefore, I modified the below
234 to fulfil the design requirement made in bug 12606969. Given that we are in the
236 stability. Currently, the name of this method is a bit might be misleading.
237 In reality, get_supplier_query() actually will retrieve
238 multiple sources for different Source Type Kanban and NOT supplier type Kanban only*/
239 FUNCTION get_supplier_query(p_org_id IN NUMBER,
240                            p_supplier_id IN NUMBER,
241                            p_supplier_site_id IN NUMBER)
242 RETURN VARCHAR2
243 IS
244 l_outer_stmt varchar2(8000) := 'select pull_sequence_id,
245 item_name,
246 destination,
247 source_type,
248 source_type_name,
249 src_inner,
250 inv_health_status,
251 inv_health_name,
252 planner_code,
253 buyer_name,
254 max(move_status) move_status,
255 sum(total_inner) number_of_cards';
256 
257 l_inner_stmt varchar2(4000) := 'select mkc.pull_sequence_id,
258 msik.concatenated_segments item_name,
259 msik.planner_code,
260 panv.full_name buyer_name,
261 concat(mkc.subinventory_name,concat('':'', destloc.concatenated_segments)) destination,
262 mkc.source_type source_type,
263 ml.meaning source_type_name,
264 decode(mkc.source_type, 1, mp.organization_code, 2,psv.vendor_name,
265 3,concat(mkc.source_subinventory, concat('':'', srcloc.concatenated_segments)),null) src_inner,
266 mkps.inv_health_status,
267 ml2.meaning inv_health_name,
268 max(mkc.move_status) move_status,
269 count(*) total_inner';
270 
271 l_inner_where varchar2(2000) := ' from mtl_kanban_cards mkc,
272 mtl_kanban_pull_sequences mkps,
273 mtl_item_locations_kfv srcloc,
274 mtl_item_locations_kfv destloc,
275 mfg_lookups ml,
276 mfg_lookups ml2,
277 mtl_parameters mp,
278 po_suppliers_val_v psv,
279 mtl_system_items_kfv msik,
280 po_agents_name_v panv
281 where mkc.pull_sequence_id = :PullSeqId
282 and mkc.inventory_item_id = msik.inventory_item_id
283 and mkc.organization_id = msik.organization_id
284 and mkc.pull_sequence_id = mkps.pull_sequence_id
285 and mkc.inventory_item_id = mkps.inventory_item_id
286 and mkc.organization_id = mkps.organization_id
287 and mkc.card_status <> 3
288 and mkps.kanban_plan_id = -1
289 and mkc.subinventory_name = mkps.subinventory_name
290 and ml.lookup_type = ''MTL_KANBAN_SOURCE_TYPE''
291 and ml.lookup_code = mkc.source_type
292 and ml2.lookup_type (+) = ''MTL_KANBAN_INV_HEALTH_STATUS''
293 and ml2.lookup_code (+) = mkps.inv_health_status
294 and nvl(mkc.locator_id, -999) = nvl(mkps.locator_id, -999)
295 and msik.buyer_id = panv.buyer_id (+)
296 and srcloc.inventory_location_id (+)=  mkc.source_locator_id
297 and srcloc.organization_id (+)= mkc.source_organization_id
298 and destloc.inventory_location_id (+)= mkc.locator_id
299 and destloc.organization_id (+)= mkc.organization_id
300 and mp.organization_id (+)= mkc.source_organization_id
301 and psv.vendor_id (+)= mkc.supplier_id ';
302 
303 l_inner_group_by varchar2(500) := ' group by mkc.pull_sequence_id, msik.concatenated_segments,
304 mkc.source_type, mkc.subinventory_name, destloc.concatenated_segments,
305 mp.organization_code,psv.vendor_name, mkc.source_subinventory,
306 srcloc.concatenated_segments,mkps.inv_health_status,msik.planner_code,
307 panv.full_name,ml.meaning, ml2.meaning';
308 
309 l_inner_dynamic varchar2(500) := '';
310 l_summary_query VARCHAR2(8000);
311 l_attr_num number := 1;
312 l_org_id number := null;
313 l_supplier_id number:= null;
314 l_supplier_site_id number:= null;
315 l_supplier_query VARCHAR2(8000);
316 
317 BEGIN
318  l_org_id := p_org_id;
319  l_supplier_id := p_supplier_id;
320  l_supplier_site_id := p_supplier_site_id;
321 -- loop through all the supply status in mfg_lookups
322   for c_statusRec in (select lookup_code
323       from mfg_lookups
324       where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
325       order by lookup_code) loop
326       l_outer_stmt := l_outer_stmt ||',sum(inner_'|| l_attr_num ||') attribute'|| l_attr_num;
327       l_inner_stmt := l_inner_stmt ||',sum(decode(supply_status,'|| c_statusRec.lookup_code || ',1)) inner_' ||  l_attr_num;
328       l_attr_num := l_attr_num + 1;
329   end loop;
330 
331   while l_attr_num <= 20 loop
332       l_outer_stmt := l_outer_stmt ||' ,' || l_attr_num || ' attribute' || l_attr_num;
333       l_attr_num := l_attr_num + 1;
334   end loop;
335 
336   IF l_org_id IS NOT NULL then
337      l_inner_dynamic := l_inner_dynamic || ' and mkc.organization_id = ' || l_org_id;
338   END IF;
339 
340   IF l_supplier_id  IS NOT NULL then
341      l_inner_dynamic := l_inner_dynamic || ' and mkc.supplier_id = '|| l_supplier_id;
342   END IF;
343 
344   IF l_supplier_site_id  IS NOT NULL then
345      l_inner_dynamic := l_inner_dynamic || ' and mkc.supplier_site_id = ' || l_supplier_site_id;
346   END IF;
347 
348   l_inner_stmt := l_inner_stmt || l_inner_where || l_inner_dynamic || l_inner_group_by;
349 
350   l_supplier_query := l_outer_stmt || ' from ('|| l_inner_stmt ||') group by pull_sequence_id,
351    src_inner, item_name, destination, source_type, source_type_name,
352   inv_health_status, inv_health_name, planner_code,buyer_name';
353 
354 --dbms_output.put_line(l_summary_query);
355    return l_supplier_query;
356 END get_supplier_query;
357 
358 
359 
360 
361 /*The following function will retrieve the Query for PullSequenceDetailsVO*/
362 FUNCTION get_pull_seq_details_query(p_org_id IN NUMBER)
363 RETURN VARCHAR2
367 subinventory_name,
364 IS
365 l_outer_stmt varchar2(8000) := 'select pull_sequence_id,
366 item_name,
368 locator,
369 destination,
370 source_type,
371 source_type_name,
372 decode(count(*),1,max(src_inner),''Multiple'') source,
373 inv_health_status,
374 inv_health_name,
375 planner_code,
376 buyer_name,
377 max(move_status) move_status,
378 sum(total_inner) number_of_cards';
379 
380 l_inner_stmt varchar2(4000) := 'select mkps.pull_sequence_id,
381 msik.concatenated_segments item_name,
382 msik.planner_code,
383 panv.full_name buyer_name,
384 concat(mkc.subinventory_name,concat('':'', destloc.concatenated_segments)) destination,
385 mkps.source_type source_type,
386 ml.meaning source_type_name,
387 decode(mkc.source_type, 1, mp.organization_code, 2,psv.vendor_name,
388 3,concat(mkc.source_subinventory, concat('':'', srcloc.concatenated_segments)),null) src_inner,
389 mkps.subinventory_name,
390 milk.concatenated_segments AS locator,
391 mkps.inv_health_status,
392 ml2.meaning inv_health_name,
393 max(mkc.move_status) move_status,
394 count(*) total_inner';
395 
396 l_inner_where varchar2(2000) := ' from mtl_kanban_cards mkc,
397 mtl_kanban_pull_sequences mkps,
398 mtl_item_locations_kfv srcloc,
399 mtl_item_locations_kfv destloc,
400 mfg_lookups ml,
401 mfg_lookups ml2,
402 mtl_parameters mp,
403 po_suppliers_val_v psv,
404 mtl_system_items_kfv msik,
405 po_agents_name_v panv,
406 mtl_item_locations_kfv milk
407 where mkc.inventory_item_id = msik.inventory_item_id(+)
408 and mkc.organization_id = msik.organization_id(+)
409 and mkps.pull_sequence_id = mkc.pull_sequence_id(+)
410 and mkps.inventory_item_id = mkc.inventory_item_id(+)
411 and mkps.organization_id = mkc.organization_id(+)
412 and mkc.card_status <> 3
413 and mkps.kanban_plan_id = -1
414 and mkps.subinventory_name = mkc.subinventory_name(+)
415 and ml.lookup_type = ''MTL_KANBAN_SOURCE_TYPE''
416 and ml.lookup_code = mkps.source_type
417 and ml2.lookup_type = ''MTL_KANBAN_INV_HEALTH_STATUS''
418 and ml2.lookup_code = mkps.inv_health_status
419 and msik.buyer_id = panv.buyer_id (+)
420 and srcloc.inventory_location_id (+)=  mkc.source_locator_id
421 and srcloc.organization_id (+)= mkc.source_organization_id
422 and destloc.inventory_location_id (+)= mkc.locator_id
423 and destloc.organization_id (+)= mkc.organization_id
424 and mp.organization_id (+)= mkc.source_organization_id
425 and psv.vendor_id (+)= mkc.supplier_id
426 and mkps.LOCATOR_ID = milk.inventory_location_id(+)
427 and mkps.organization_id = milk.organization_id(+) ';
428 
429 l_inner_group_by varchar2(500) := ' group by mkps.pull_sequence_id, msik.concatenated_segments,
430 mkps.source_type, mkc.source_type, mkc.subinventory_name, destloc.concatenated_segments,
431 mp.organization_code,psv.vendor_name, mkc.source_subinventory,
432 srcloc.concatenated_segments,mkps.inv_health_status,
433 mkps.subinventory_name, milk.concatenated_segments, msik.planner_code,
434 panv.full_name,ml.meaning, ml2.meaning';
435 
436 l_inner_dynamic varchar2(500) := '';
437 l_details_query VARCHAR2(8000);
438 l_attr_num number := 1;
439 l_org_id number := null;
440 l_supplier_id number:= null;
441 l_supplier_site_id number:= null;
442 
443 BEGIN
444   l_org_id := p_org_id;
445 -- loop through all the supply status in mfg_lookups
446   for c_statusRec in (select lookup_code
447       from mfg_lookups
448       where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
449       order by lookup_code) loop
450       l_outer_stmt := l_outer_stmt ||',sum(inner_'|| l_attr_num ||') attribute'|| l_attr_num;
451       l_inner_stmt := l_inner_stmt ||',sum(decode(supply_status,'|| c_statusRec.lookup_code || ',1)) inner_' ||  l_attr_num;
452       l_attr_num := l_attr_num + 1;
453   end loop;
454 
455   while l_attr_num <= 20 loop
456       l_outer_stmt := l_outer_stmt ||' ,' || l_attr_num || ' attribute' || l_attr_num;
457       l_attr_num := l_attr_num + 1;
458   end loop;
459 
460   IF l_org_id IS NOT NULL then
461      l_inner_dynamic := l_inner_dynamic || ' and mkps.organization_id = ' || l_org_id;
462   END IF;
463 
464   l_inner_stmt := l_inner_stmt || l_inner_where || l_inner_dynamic || l_inner_group_by;
465 
466   l_details_query := l_outer_stmt || ' from ('|| l_inner_stmt ||') group by pull_sequence_id,
467   item_name, subinventory_name, locator, destination, source_type, source_type_name,
468   inv_health_status, inv_health_name, planner_code,buyer_name';
469 
470 --dbms_output.put_line(l_details_query);
471    return l_details_query;
472 END get_pull_seq_details_query;
473 
474 
475 END FLM_EKB_SUMMARY_UTIL;