1 PACKAGE BODY flm_schedule_report AS
2 /* $Header: FLMFSCHB.pls 115.6 2002/12/12 13:32:15 sjagan ship $ */
3
4 -- Function that returns the revision of the item
5 FUNCTION get_revision(p_org_id NUMBER, p_item_id NUMBER, p_date DATE) return VARCHAR2
6 IS
7 l_rev VARCHAR2(3);
8 BEGIN
9 BOM_REVISIONS.Get_Revision(
10 type => 'PART',
11 eco_status => 'ALL',
12 examine_type => 'ALL',
13 org_id => p_org_id,
14 item_id => p_item_id,
15 rev_date => p_date,
16 itm_rev => l_rev);
17 return l_rev;
18 END get_revision;
19
20 -- Function that determines if the item need to be displayed.
21 -- It returns 1 if the item need to be displayed, 2 otherwise
22 -- This will be called for all item in the bill except for level 1.
23 -- For level 1 component, the report display only the non-phantom
24 -- item.
25 -- The function returns 1 for the item that has the parents with
26 -- the phantom component.
27 FUNCTION display_item(p_level NUMBER,
28 p_sort_order VARCHAR2,
29 p_top_bill_seq_id NUMBER,
30 p_org_id NUMBER)
31 RETURN number IS
32 l_parent_id NUMBER;
33 l_comp_id NUMBER;
34 l_top_id NUMBER;
35 /* Changed the size of l_Comp_type and l_parent_type from 10 to 30 for bug number 2152161 */
36 l_comp_type VARCHAR2(30);
37 l_parent_type VARCHAR2(30);
38 BEGIN
39 select component_item_id, assembly_item_id, top_item_id
40 into l_comp_id, l_parent_id, l_top_id
41 from bom_explosions
42 where top_bill_sequence_id = p_top_bill_seq_id
43 and sort_order = p_sort_order
44 and explosion_type = 'ALL';
45
46 select item_type
47 into l_comp_type
48 from mtl_system_items
49 where inventory_item_id = l_comp_id
50 and organization_id = p_org_id;
51
52 select item_type
53 into l_parent_type
54 from mtl_system_items
55 where inventory_item_id = l_parent_id
56 and organization_id = p_org_id;
57
58 /* Criteria :
59 - Don't show the phantom component itself.
60 - Don't show the item that has parent that doesn't have phantom type, except for
61 component of top_assembly item
62 - Show the item that is part of the phantom item
63 (This phantom item is component of top_assembly item).
64 - Recursively call the display_item for its parent. */
65 if (p_level = 1 AND l_comp_type = 'PH') then
66 return 2;
67 elsif (l_top_id <> l_parent_id AND l_parent_type <> 'PH') then
68 return 2;
69 elsif (l_top_id = l_parent_id AND l_comp_type = 'PH') then
70 return 1;
71 else
72 return(display_item(p_level+1, substr(p_sort_order, 0, length(p_sort_order)-4), p_top_bill_seq_id, p_org_id));
73 end if;
74
75 END display_item;
76 END flm_schedule_report;
[Home] [Help]