1 PACKAGE BODY PA_STRUCT_UPGR_PUB AS
2 /* $Header: PAPRUPGB.pls 120.0 2005/05/29 19:58:26 appldev noship $ */
3
4 -- Assumptions:
5 -- This function is called in the display sequence order
6 -- CLEAR_GLOBALS is called before calling this function for a new project. This assumption no longer valid due to fix of Bug No. 4049574.
7
8 -- Bug No. 4049574. Added additional parameter p_project_id.
9 FUNCTION GET_WBS_NUMBER
10 (p_wbs_level IN NUMBER, p_project_id IN NUMBER DEFAULT NULL)
11 RETURN VARCHAR2
12 IS
13 l_prev_wbs_num VARCHAR2(255);
14 l_new_wbs_num VARCHAR2(255);
15 l_pivot NUMBER;
16 l_str1 VARCHAR2(255);
17 l_str2 VARCHAR2(10);
18 BEGIN
19 -- Bug No. 4049574
20 if p_project_id IS NOT NULL then
21 if nvl(G_PROJECT_ID, -111) <> p_project_id then
22 G_PROJECT_ID := p_project_id;
23 PA_STRUCT_UPGR_PUB.CLEAR_GLOBALS;
24 end if;
25 end if;
26 -- End Bug No. 4049574
27
28 if g_wbs_num_tbl.exists(p_wbs_level) then
29 -- Peer to an existing task at this wbs level
30 l_prev_wbs_num := g_wbs_num_tbl(p_wbs_level);
31 l_pivot := instr(l_prev_wbs_num, '.', -1);
32
33 if l_pivot = 0 then
34 -- This is a top level task
35 l_new_wbs_num := to_char(to_number(l_prev_wbs_num) + 1);
36 else
37 l_str1 := substr(l_prev_wbs_num, 1, l_pivot);
38 l_str2 := substr(l_prev_wbs_num, l_pivot + 1);
39
40 -- Increment the wbs number by 1
41 l_str2 := to_char(to_number(l_str2) + 1);
42 l_new_wbs_num := l_str1 || l_str2;
43 end if;
44 else
45 -- First task at this wbs level (for this top task hierarchy)
46 if p_wbs_level = 1 then
47 l_new_wbs_num := '1';
48 else
49 l_prev_wbs_num := g_wbs_num_tbl(p_wbs_level - 1);
50 l_new_wbs_num := l_prev_wbs_num || '.1';
51 end if;
52 end if;
53
54 -- Store the newly calculated wbs_number in the global table
55 g_wbs_num_tbl(p_wbs_level) := l_new_wbs_num;
56
57 -- Need to clear the stored wbs num of child level if
58 -- parent wbs num is updated
59 if p_wbs_level < g_max_wbs_level then
60 if g_wbs_num_tbl.exists(p_wbs_level + 1) then
61 g_wbs_num_tbl.delete(p_wbs_level + 1);
62 end if;
63 end if;
64
65 -- Update g_max_wbs_level
66 if p_wbs_level > g_max_wbs_level then
67 g_max_wbs_level := p_wbs_level;
68 end if;
69
70 return l_new_wbs_num;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 return NULL;
75
76 END GET_WBS_NUMBER;
77
78
79 PROCEDURE CLEAR_GLOBALS
80 IS
81 BEGIN
82
83 g_max_wbs_level := 1;
84 g_wbs_num_tbl.delete;
85
86 --maansari
87 G_DISP_SEQ_TBL.delete;
88 --maansari
89
90 END CLEAR_GLOBALS;
91
92 -- Assumptions:
93 -- This function is called in the display sequence order.
94 -- CLEAR_GLOBALS is called before calling this function for new set of tasks
95 --Usage
96 --This function is used in publish_structure api to use in bulk insert
97 FUNCTION GET_DISP_SEQUENCE
98 (p_display_sequence IN NUMBER)
99 RETURN NUMBER IS
100 l_new_index NUMBER := 0;
101 l_disp_seq NUMBER;
102 BEGIN
103
104 if not g_disp_seq_tbl.exists(1)
105 then
106 If p_display_sequence > 1 or p_display_sequence = 1
107 then
108 l_disp_seq := 1;
109 end if;
110 else
111 l_new_index := g_disp_seq_tbl.count;
112 IF p_display_sequence - g_disp_seq_tbl(g_disp_seq_tbl.count) > 1
113 THEN
114 l_disp_seq := p_display_sequence - ( p_display_sequence - 1 - l_new_index );
115 ELSIF p_display_sequence - g_disp_seq_tbl(g_disp_seq_tbl.count) = 1
116 THEN
117 l_disp_seq := p_display_sequence;
118 END IF;
119 end if;
120 g_disp_seq_tbl(l_new_index+1) := l_disp_seq;
121 return l_disp_seq;
122
123 END GET_DISP_SEQUENCE;
124
125
126
127 END PA_STRUCT_UPGR_PUB;