[Home] [Help]
PACKAGE BODY: APPS.AMW_SIGNIFICANT_ELEMENTS_PKG
Source
1 PACKAGE BODY AMW_SIGNIFICANT_ELEMENTS_PKG as
2 /* $Header: amwvsigb.pls 120.0 2005/05/31 22:13:47 appldev noship $ */
3
4
5 -- ===============================================================
6 -- Package name
7 -- AMW_SIGNIFICANT_ELEMENTS_PKG
8 -- Purpose
9 --
10 -- History
11 -- 12/18/2003 tsho Creates
12 -- ===============================================================
13
14
15
16 -- ===============================================================
17 -- Function name
18 -- ELEMENT_PRESENT
19 -- Purpose
20 -- return 'Y' if there's element for the specified object_id;
21 -- return 'N' otherwise.
22 -- ===============================================================
23 FUNCTION ELEMENT_PRESENT (
24 P_OBJECT_ID IN NUMBER,
25 P_OBJECT_TYPE IN VARCHAR2,
26 P_ELEMENT_CODE IN VARCHAR2
27 ) RETURN VARCHAR2
28 IS
29
30 n number;
31
32 BEGIN
33 select count(*)
34 into n
35 from AMW_SIGNIFICANT_ELEMENTS
36 where pk1 = P_OBJECT_ID
37 and object_type = P_OBJECT_TYPE
38 and element_code = P_ELEMENT_CODE;
39
40 if n > 0 then
41 return 'Y';
42 else
43 return 'N';
44 end if;
45
46 END ELEMENT_PRESENT;
47
48
49
50 -- ===============================================================
51 -- Procedure name
52 -- PROCESS_ELEMENTS
53 -- Purpose
54 -- update the elements for specified object_id
55 -- Notes
56 -- OBJECT_TYPE = 'PROCESS' with PK1 = PROCESS_REV_ID
57 -- OBJECT_TYPE = 'PROCESS_ORG' with PK1 = PROCESS_ORGANIZATION_ID
58 -- ===============================================================
59 PROCEDURE PROCESS_ELEMENTS (
60 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
61 p_commit IN VARCHAR2 := FND_API.G_FALSE,
62 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
63 p_select_flag IN VARCHAR2,
64 p_object_id IN NUMBER,
65 p_object_type IN VARCHAR2,
66 p_element_code IN VARCHAR2,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2
70 )
71 IS
72
73 l_creation_date date;
74 l_created_by number;
75 l_last_update_date date;
76 l_last_updated_by number;
77 l_last_update_login number;
78 l_significant_element_id number;
79 l_object_version_number number;
80
81 BEGIN
82
83 -- create savepoint if p_commit is true
84 IF p_commit = FND_API.G_TRUE THEN
85 SAVEPOINT PROCESS_ELEMENTS_SAVE;
86 END IF;
87
88 -- initialize message list if p_init_msg_list is set to true
89 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
90 fnd_msg_pub.initialize;
91 end if;
92
93 -- initialize return status to success
94 x_return_status := fnd_api.g_ret_sts_success;
95
96 delete from AMW_SIGNIFICANT_ELEMENTS
97 where pk1 = p_object_id
98 and object_type = p_object_type
99 and element_code = p_element_code;
100
101 if (p_select_flag = 'Y') then
102 l_creation_date := SYSDATE;
103 l_created_by := FND_GLOBAL.USER_ID;
104 l_last_update_date := SYSDATE;
105 l_last_updated_by := FND_GLOBAL.USER_ID;
106 l_last_update_login := FND_GLOBAL.USER_ID;
107 l_object_version_number := 1;
108
109 select AMW_SIGNIFICANT_ELEMENT_S.nextval into l_significant_element_id from dual;
110
111 insert into AMW_SIGNIFICANT_ELEMENTS (
112 significant_element_id,
113 object_type,
114 pk1,
115 element_code,
116 creation_date,
117 created_by,
118 last_update_date,
119 last_updated_by,
120 last_update_login,
121 object_version_number
122 ) values (
123 l_significant_element_id,
124 p_object_type,
125 p_object_id,
126 p_element_code,
127 l_creation_date,
128 l_created_by,
129 l_last_update_date,
130 l_last_updated_by,
131 l_last_update_login,
132 l_object_version_number
133 );
134
135 end if;
136
137 -- Standard check for p_commit
138 IF FND_API.to_Boolean( p_commit ) THEN
139 COMMIT WORK;
140 END IF;
141
142 EXCEPTION
143 WHEN FND_API.G_EXC_ERROR THEN
144 IF FND_API.to_Boolean( p_commit ) THEN
145 ROLLBACK TO PROCESS_ELEMENTS_SAVE;
146 END IF;
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 fnd_msg_pub.count_and_get(p_count => x_msg_count,
149 p_data => x_msg_data);
150 WHEN OTHERS THEN
151 IF FND_API.to_Boolean( p_commit ) THEN
152 ROLLBACK TO PROCESS_ELEMENTS_SAVE;
153 END IF;
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_SIGNIFICANT_ELEMENTS_PKG',
156 p_procedure_name => 'PROCESS_ELEMENTS',
157 p_error_text => SUBSTRB(SQLERRM,1,240));
158
159 fnd_msg_pub.count_and_get(p_count => x_msg_count,
160 p_data => x_msg_data);
161
162 END PROCESS_ELEMENTS;
163
164
165 -- ===============================================================
166 -- Function name
167 -- ELEMENT_PRESENT_IN_LATEST
168 -- Purpose
169 -- return 'Y' if there's element for the specified object_id;
170 -- return 'N' otherwise.
171 -- Created nirmakum
172 -- Reason AMW.D, for knowing if there is a latest association of a significant element
173 -- to a process
174 -- ===============================================================
175
176 FUNCTION ELEMENT_PRESENT_IN_LATEST (
177 P_OBJECT_ID IN NUMBER,
178 P_OBJECT_TYPE IN VARCHAR2,
179 P_ELEMENT_CODE IN VARCHAR2
180 ) RETURN VARCHAR2
181 IS
182 n pls_integer;
183 BEGIN
184 select 1 into n
185 from AMW_SIGNIFICANT_ELEMENTS
186 where pk1 = P_OBJECT_ID
187 and object_type = P_OBJECT_TYPE
188 and element_code = P_ELEMENT_CODE
189 and deletion_date is null;
190
191 return 'Y';
192 exception
193 when no_data_found then
194 return 'N';
195 when too_many_rows then
196 return 'Y';
197 end ELEMENT_PRESENT_IN_LATEST;
198
199
200 -- ----------------------------------------------------------------------
201
202 FUNCTION ELEMENT_PRESENT_IN_REVISION (
203 P_OBJECT_ID IN NUMBER,
204 P_OBJECT_TYPE IN VARCHAR2,
205 P_ELEMENT_CODE IN VARCHAR2
206 ) RETURN VARCHAR2
207 IS
208 n pls_integer;
209 BEGIN
210 select 1 into n
211 from AMW_SIGNIFICANT_ELEMENTS ASE,
212 AMW_PROCESS AP
213 where AP.process_rev_id = P_OBJECT_ID
214 and ASE.pk1 = AP.PROCESS_ID
215 and ASE.object_type = P_OBJECT_TYPE
216 and ASE.element_code = P_ELEMENT_CODE
217 -- and ASE.approval_date <= AP.approval_end_date
218 and (ASE.deletion_approval_date IS NULL OR ASE.deletion_approval_date > AP.approval_end_date)
219 and (ASE.approval_date <= AP.approval_end_date OR AP.approval_end_date is null);
220
221
222
223 return 'Y';
224 exception
225 when no_data_found then
226 return 'N';
227 when too_many_rows then
228 return 'Y';
229 end ELEMENT_PRESENT_IN_REVISION;
230
231 END AMW_SIGNIFICANT_ELEMENTS_PKG;