[Home] [Help]
PACKAGE BODY: APPS.EAM_WOLCOSTING_PUB
Source
1 PACKAGE BODY eam_wolcosting_pub AS
2 /* $Header: EAMWWOHB.pls 115.7 2004/04/02 03:35:41 samjain noship $ */
3 -- Start of comments
4 -- API name : insert_into_snapshot_pub
5 -- Type : Private.
6 -- Function : Insert the hierarchy into the CST_EAM_HIERARCHY_SNAPSHOT table.
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN p_api_version IN NUMBER
10 -- p_init_msg_list VARCHAR2:= FND_API.G_FALSE
11 -- p_commit VARCHAR2:= FND_API.G_FALSE
12 -- p_validation_level NUMBER:= FND_API.G_VALID_LEVEL_FULL
13 -- p_wip_entity_id NUMBER
14 -- p_object_type NUMBER
15 -- p_parent_object_type NUMBER
16 -- p_org_id NUMBER
17 -- OUT x_group_id NOCOPY NUMBER,
18 -- x_return_status NOCOPY VARCHAR2
19 -- x_msg_count NOCOPY NUMBER
20 -- x_msg_data NOCOPY VARCHAR2
21 -- Notes : None
22 --
23 -- End of comments
24
25 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_wolcosting_pub';
26 g_debug CONSTANT VARCHAR2(1):=NVL(fnd_profile.value('APPS_DEBUG'),'N');
27
28 l_data_error_rollupCost VARCHAR2(2000);
29 l_index_error_rollupCost NUMBER;
30 l_msg_data VARCHAR2(2000);
31
32 --Bug3544656: Added a parameter to pass the relationship type
33
34 PROCEDURE insert_into_snapshot_pub(
35 p_api_version IN NUMBER ,
36 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
37 p_commit IN VARCHAR2:= FND_API.G_FALSE,
38 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
39 p_wip_entity_id IN NUMBER,
40 p_object_type IN NUMBER,
41 p_parent_object_type IN NUMBER,
42 x_group_id OUT NOCOPY NUMBER,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER ,
45 x_msg_data OUT NOCOPY VARCHAR2 ,
46 p_org_id IN NUMBER,
47 p_relationship_type IN NUMBER :=3)
48 IS
49 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_snapshot_pub';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
52 l_group_id NUMBER(15);
53 l_count_child_object_id NUMBER(15);
54
55 --Bug3544656: Modified the cursor definition to pick the relationship type being passed.
56
57 CURSOR c_hierarchy (l_group_id NUMBER) IS
58 SELECT
59 l_group_id,
60 CHILD_OBJECT_ID,
61 p_object_type child_object_type,
62 PARENT_OBJECT_ID,
63 p_parent_object_type parent_object_type,
64 level,
65 sysdate last_update_date,
66 FND_GLOBAL.USER_ID last_updated_by,
67 sysdate creation_date,
68 FND_GLOBAL.USER_ID created_by,
69 null request_id,
70 FND_GLOBAL.PROG_APPL_ID prog_appl_id ,
71 null last_update_login
72 FROM EAM_WO_RELATIONSHIPS ewr
73 WHERE
74 ewr.parent_relationship_type = p_relationship_type
75 START WITH ewr.parent_object_id = p_wip_entity_id AND ewr.parent_relationship_type = p_relationship_type
76 CONNECT BY ewr.parent_object_id = PRIOR ewr.child_object_id AND ewr.parent_relationship_type = p_relationship_type ;
77
78
79 BEGIN
80 -- Standard Start of API savepoint
81 SAVEPOINT insert_into_snapshot_pub;
82
83 -- Standard call to check for call compatibility.
84 IF NOT FND_API.compatible_api_call(
85 l_api_version
86 ,p_api_version
87 ,l_api_name
88 ,g_pkg_name) THEN
89 RAISE fnd_api.g_exc_unexpected_error;
90 END IF;
91
92 -- Initialize message list if p_init_msg_list is set to TRUE.
93 IF FND_API.to_boolean(p_init_msg_list) THEN
94 FND_MSG_PUB.initialize;
95 END IF;
96
97 -- Initialize API return status to success
98 x_return_status := FND_API.g_ret_sts_success;
99
100 -- API body
101
102 -- Insert the data into the cst_eam_hierarchy_snapshot table
103
104 SELECT MTL_EAM_ASSET_ACTIVITIES_S.nextval INTO x_group_id
105 FROM DUAL;
106
107 -- Insert only the top level workorder in questin as level 0 for rollup cost.
108
109
110 INSERT INTO CST_EAM_HIERARCHY_SNAPSHOT(
111 GROUP_ID,
112 OBJECT_ID,
113 OBJECT_TYPE,
114 PARENT_OBJECT_ID,
115 PARENT_OBJECT_TYPE ,
116 LEVEL_NUM,
117 LAST_UPDATE_DATE,
118 LAST_UPDATED_BY,
119 CREATION_DATE,
120 CREATED_BY,
121 REQUEST_ID,
122 PROGRAM_APPLICATION_ID,
123 LAST_UPDATE_LOGIN)
124 SELECT
125 x_group_id,
126 p_wip_entity_id AS object_id, -- the starting parent needs to be its child with level 0
127 p_object_type,
128 p_wip_entity_id AS parent_object_id,
129 p_parent_object_type,
130 0, -- top level parent should be at level 0
131 sysdate,
132 FND_GLOBAL.USER_ID,
133 sysdate,
134 FND_GLOBAL.USER_ID,
135 null,
136 FND_GLOBAL.PROG_APPL_ID,
137 null
138 FROM DUAL;
139
140
141
142 -- Insert the child WO and the relation with levels 1,2,3...
143 FOR c_hierarchy_row IN c_hierarchy(x_group_id)
144 LOOP
145 /* CHECK TO AVOID DUPLICATION OF THE SAME WORKORDER.
146 * IF NOT ALREADY INSERTED THEN ONLY IT SHOULD BE INSERTED
147 */
148
149 INSERT INTO CST_EAM_HIERARCHY_SNAPSHOT(
150 GROUP_ID,
151 OBJECT_ID,
152 OBJECT_TYPE,
153 PARENT_OBJECT_ID,
154 PARENT_OBJECT_TYPE ,
155 LEVEL_NUM,
156 LAST_UPDATE_DATE,
157 LAST_UPDATED_BY,
158 CREATION_DATE,
159 CREATED_BY,
160 REQUEST_ID,
161 PROGRAM_APPLICATION_ID,
162 LAST_UPDATE_LOGIN)
163 VALUES
164 (
165 c_hierarchy_row.l_group_id,
166 c_hierarchy_row.CHILD_OBJECT_ID,
167 c_hierarchy_row.child_object_type,
168 c_hierarchy_row.PARENT_OBJECT_ID,
169 c_hierarchy_row.parent_object_type,
170 c_hierarchy_row.level,
171 c_hierarchy_row.last_update_date,
172 c_hierarchy_row.last_updated_by,
173 c_hierarchy_row.creation_date,
174 c_hierarchy_row.created_by,
175 c_hierarchy_row.request_id,
176 c_hierarchy_row.prog_appl_id ,
177 c_hierarchy_row.last_update_login
178 );
179
180 END LOOP;
181
182 FND_MSG_PUB.Add_Exc_Msg
183 ( p_pkg_name => G_PKG_NAME ,
184 p_procedure_name => l_api_name ,
185 p_error_text => 'Inserted data into snapshot table. Calling the API for rollup'
186 );
187
188 /* Calling the API of the costing to calculate the cumulative costs */
189 CST_eamCost_PUB.Rollup_WorkOrderCost(
190 p_api_version => 1.0,
191 p_group_id => x_group_id,
192 p_organization_id => p_org_id,
193 p_user_id => FND_GLOBAL.USER_ID,
194 p_prog_appl_id => FND_GLOBAL.PROG_APPL_ID,
195 x_return_status => x_return_status,
196 p_init_msg_list => FND_API.G_TRUE,
197 p_commit => FND_API.G_TRUE );
198
199 FND_MSG_PUB.Add_Exc_Msg
200 ( p_pkg_name => G_PKG_NAME ,
201 p_procedure_name => l_api_name ,
202 p_error_text => 'Called the API'
203 );
204
205
206 -- End of API body
207
208 -- Standard check of p_commit.
209 IF FND_API.TO_BOOLEAN(p_commit) THEN
210 COMMIT WORK;
211 FND_MSG_PUB.Add_Exc_Msg
212 ( p_pkg_name => G_PKG_NAME ,
213 p_procedure_name => l_api_name ,
214 p_error_text => 'Committed the entry into hierarchy snapshot and rollup costs table'
215 );
216 END IF;
217
218 -- See all the messages generated and stored into the msg table
219
220 FND_MSG_PUB.count_and_get(
221 p_encoded => FND_API.G_FALSE,
222 p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225
226 IF x_msg_count > 0
227 THEN
228 FOR indexCount IN 1 ..x_msg_count
229 LOOP
230 l_msg_data := FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
231 -- DBMS_OUTPUT.PUT_LINE(indexCount ||'-'||l_msg_data);
232 END LOOP;
233 END IF;
234
235
236 EXCEPTION
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238 ROLLBACK TO insert_into_snapshot_pub;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
240 FND_MSG_PUB.count_and_get(
241 p_encoded => FND_API.G_FALSE,
242 p_count => x_msg_count,
243 p_data => x_msg_data
244 );
245
246 IF x_msg_count > 0
247 THEN
248 FOR indexCount IN 1 ..x_msg_count
249 LOOP
250 l_msg_data := FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
251 -- DBMS_OUTPUT.PUT_LINE(indexCount ||'-'||l_msg_data);
252 END LOOP;
253 END IF;
254
255 WHEN OTHERS THEN
256 ROLLBACK TO insert_into_snapshot_pub;
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
258 IF FND_MSG_PUB.Check_Msg_Level
259 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
260 THEN
261 FND_MSG_PUB.Add_Exc_Msg
262 ( G_PKG_NAME ,
263 l_api_name
264 );
265 END IF;
266 FND_MSG_PUB.count_and_get(
267 p_encoded => FND_API.G_FALSE,
268 p_count => x_msg_count,
269 p_data => x_msg_data
270 );
271 IF x_msg_count > 0
272 THEN
273 FOR indexCount IN 1 ..x_msg_count
274 LOOP
275 l_msg_data := FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
276 --DBMS_OUTPUT.PUT_LINE(indexCount ||'-'||l_msg_data);
277 END LOOP;
278 END IF;
279
280 END insert_into_snapshot_pub;
281 END eam_wolcosting_pub;