1 PACKAGE BODY CST_RES_COST_IMPORT_INTERFACE as
2 /* $Header: CSTRIMPB.pls 120.0 2005/05/25 05:20:23 appldev noship $ */
3
4 PROCEDURE Validate_resource_costs (Error_number OUT NOCOPY NUMBER
5 ,i_group_id IN NUMBER
6 ,i_new_csttype IN VARCHAR2
7 ,i_del_option IN NUMBER
8 ,i_run_option IN NUMBER
9 ) as
10
11 l_org_id NUMBER := 0;
12 SEQ_NEXTVAL NUMBER :=0;
13 l_stmt_no NUMBER := 0;
14 i_count NUMBER := 0;
15 l_count NUMBER := 0;
16 l_cost_type_id NUMBER;
17 CONC_REQUEST BOOLEAN;
18 BEGIN
19
20 SEQ_NEXTVAL := i_group_id;
21 Error_number := 0;
22 /* check for both the organization_id and code to be null */
23 fnd_file.put_line(fnd_file.log,'--------entering the validate_resource_costs procedure----------');
24
25 Update CST_RESOURCE_COSTS_INTERFACE crci
26 SET error_flag = 'E',
27 error_code = 'CST_NULL_ORGANIZATION',
28 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ORGANIZATION'),1,240)
29 where (Organization_id is null AND organization_code is null)
30 AND error_flag is null
31 AND crci.group_id = SEQ_NEXTVAL;
32
33 l_stmt_no := 10;
34 fnd_file.put_line(fnd_file.log,'done checking for null organization ID and code ');
35
36 /* check to see if the input organization_id or code is valid */
37
38 Update CST_RESOURCE_COSTS_INTERFACE crci
39 SET error_flag = 'E',
40 error_code = 'CST_INVALID_ORGANIZATION',
41 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ORGANIZATION'),1,240)
42 WHERE crci.error_flag is null
43 AND crci.group_id = SEQ_NEXTVAL
44 AND NOT EXISTS (select 1 from mtl_parameters mp
45 where NVL(crci.organization_id,mp.organization_id) = mp.organization_id
46 AND NVL(crci.organization_code,mp.organization_code) = mp.organization_code
47 );
48
49 l_stmt_no := 20;
50 fnd_file.put_line(fnd_file.log,'done checking for the organization to be invalid');
51
52 /* Get the organization_id from the code */
53
54 Update CST_RESOURCE_COSTS_INTERFACE crci
55 SET organization_id = (select organization_id
56 FROM mtl_parameters mp
57 WHERE mp.organization_code = crci.organization_code
58 )
59 WHERE crci.organization_id is null
60 AND crci.error_flag is null
61 AND crci.group_id = SEQ_NEXTVAL;
62
63 /* OPM INVCONV project to bypass all process orgs in Discrete programs
64 ** umoogala 09-nov-2004 Bug# 3980701
65 **/
66
67 l_stmt_no := 30;
68
69 Update CST_RESOURCE_COSTS_INTERFACE ct
70 SET error_flag = 'E',
71 error_code = 'CST_PROCESS_ORG_ERROR',
72 error_explanation =
73 substrb(fnd_message.get_string(
74 'GMF','GMF_PROCESS_ORG_ERROR'),1,240)
75 WHERE ct.error_flag is null
76 AND ct.group_id = SEQ_NEXTVAL
77 AND EXISTS (select 'This is a process manufacturing org'
78 from mtl_parameters mp
79 where mp.organization_id = ct.organization_id
80 AND NVL(mp.process_enabled_flag, 'N') = 'Y'
81 )
82 ;
83
84 /* End OPM INVCONV changes */
85
86 l_stmt_no := 30;
87 fnd_file.put_line(fnd_file.log,'done getting the organization_id from the code if it has not been provided');
88
89 /* Set the unique transaction_id for each row */
90
91 Update CST_RESOURCE_COSTS_INTERFACE crci
92 SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,
93 error_code =null,
94 error_explanation = null,
95 request_id = FND_GLOBAL.CONC_REQUEST_ID,
96 program_application_id = FND_GLOBAL.PROG_APPL_ID,
97 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
98 program_update_date = sysdate,
99 process_flag = 2
100 where group_id=SEQ_NEXTVAL
101 AND error_flag is null
102 AND process_flag = 1;
103
104
105 l_stmt_no := 50;
106 fnd_file.put_line(fnd_file.log,'done assiging unique transaction_id to every row');
107 COMMIT;
108
109 /* check for the organization to be a costing org */
110
111 UPDATE CST_RESOURCE_COSTS_INTERFACE crci
112 set crci.Error_flag = 'E',
113 crci.Error_code = 'CST_NOT_COSTINGORG',
114 crci.Error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_COSTINGORG'),1,240)
115 WHERE crci.group_id = SEQ_NEXTVAL
116 AND crci.error_flag is null
117 AND EXISTS ( Select 1 from mtl_parameters mp
118 WHERE mp.cost_organization_id <> mp.organization_id
119 AND mp.organization_id = crci.organization_id);
120
121 l_stmt_no := 55;
122 fnd_file.put_line(fnd_file.log,'done checking for costing org or not');
123
124
125 /* now set teh cost type and cost type id */
126
127 Update CST_RESOURCE_COSTS_INTERFACE crci
128 SET crci.cost_type_id = (select cost_type_id from CST_COST_TYPES cct
129 WHERE cct.cost_type = i_new_csttype
130 ),
131 crci.cost_type = i_new_csttype
132 WHERE crci.group_id = SEQ_NEXTVAL
133 AND crci.error_flag is null;
134
135
136 l_stmt_no := 80;
137
138
139 /* check for resource_id and resource_code to be null */
140
141 Update CST_RESOURCE_COSTS_INTERFACE crci
142 SET crci.error_flag = 'E',
143 crci.error_code = 'CST_NULL_SUBELEMENT',
144 crci.error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_SUBELEMENT'),1,240)
145 WHERE crci.error_flag is null
146 AND crci.group_id = SEQ_NEXTVAL
147 AND (crci.resource_id is null AND crci.resource_code is null);
148
149 l_stmt_no := 90;
150 fnd_file.put_line(fnd_file.log,'done checking for null sub element');
151
152 /* check if the entered resource_id or code is actually valid and also that functional currency flag is not = 1 */
153 Update CST_RESOURCE_COSTS_INTERFACE crci set
154 error_flag = 'E',
155 error_code = 'CST_INVALID_RESOURCE',
156 error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_RESOURCE'),1,240)
157 WHERE crci.error_flag is null
158 AND crci.group_id = SEQ_NEXTVAL
159 AND NOT EXISTS (select 1 from bom_resources bm
160 WHERE NVL(crci.resource_id,bm.resource_id)=bm.resource_id
161 AND NVL(crci.resource_code,bm.resource_code)=bm.resource_code
162 AND (bm.cost_element_id = 3 OR bm.cost_element_id = 4)
163 AND bm.functional_currency_flag <> 1
164 AND crci.organization_id = bm.organization_id
165 );
166
167
168 l_stmt_no := 100;
169
170
171 Update CST_RESOURCE_COSTS_INTERFACE crci set
172 crci.resource_id = (select bm.resource_id from bom_resources bm
173 WHERE bm.resource_code = crci.resource_code
174 AND (bm.cost_element_id = 3 or bm.cost_element_id = 4)
175 AND bm.functional_currency_flag <> 1
176 AND bm.organization_id = crci.organization_id)
177 WHERE crci.error_flag is null
178 AND crci.resource_id is null
179 AND crci.group_id = SEQ_NEXTVAL;
180
181 fnd_file.put_line(fnd_file.log,'done setting the resource_id if it has not been provided');
182 l_stmt_no := 105;
183
184 /* check for the validity date of the resource_id */
185
186 Update CST_RESOURCE_COSTS_INTERFACE crci
187 set crci.error_flag = 'E',
188 crci.error_code = 'CST_EXP_SUBELEMENT',
189 crci.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
190 where crci.error_flag is null
191 AND crci.group_id = SEQ_NEXTVAL
192 AND EXISTS ( select 1 from BOM_RESOURCES bm
193 WHERE bm.organization_id = crci.organization_id
194 AND (bm.cost_element_id = 3 OR bm.cost_element_id = 4)
195 AND bm.resource_id = crci.resource_id
196 AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2)));
197
198 fnd_file.put_line(fnd_file.log,'done checking for the validity date and allow costs flag of resource id');
199 l_stmt_no := 106;
200
201
202 /* check for the resource rate that is provided to be not null */
203
204 UPDATE CST_RESOURCE_COSTS_INTERFACE crci
205 set error_flag = 'E',
206 error_code = 'CST_NULL_RESRT',
207 error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_RESRT'),1,240)
208 where crci.resource_rate is null
209 and crci.error_flag is null
210 and crci.group_id = SEQ_NEXTVAL;
211
212
213 Update CST_RESOURCE_COSTS_INTERFACE crci
214 set process_flag = 3
215 WHERE process_flag = 2
216 AND group_id = SEQ_NEXTVAL
217 AND error_flag is null;
218
219 COMMIT;
220
221 /* check for the duplicate rows in the interface table*/
222
223 Update cst_resource_costs_interface crci
224 set crci.error_flag = 'E',
225 crci.error_code = 'CST_DUPL_ROWS',
226 crci.error_explanation = substrb(fnd_message.get_string('BOM','CST_DUPL_ROWS'),1,240)
227 where crci.error_flag is null
228 AND crci.group_id = SEQ_NEXTVAL
229 AND EXISTS (select 1 from cst_resource_costs_interface crci2
230 WHERE crci.resource_id = crci2.resource_id
231 AND crci.organization_id = crci2.organization_id
232 AND crci.cost_type_id = crci2.cost_type_id
233 AND crci.rowid <> crci2.rowid
234 AND crci2.group_id = SEQ_NEXTVAL);
235
236 fnd_file.put_line(fnd_file.log,'done checking for duplicate rows');
237
238 Update CST_RESOURCE_COSTS_INTERFACE crci
239 set process_flag = 4
240 WHERE crci.process_flag = 3
241 AND crci.error_flag is null
242 AND crci.group_id = SEQ_NEXTVAL;
243
244 COMMIT;
245
246 l_stmt_no := 110;
247
248 /* Now begin inserting rows into CST_RESOURCE_COSTS */
249
250 /* first check the run option */
251
252 if i_run_option = 2 then
253 l_stmt_no := 115;
254
255 Delete from CST_RESOURCE_COSTS crc
256 where EXISTS (select 1 from CST_RESOURCE_COSTS_INTERFACE crci
257 where crci.organization_id = crc.organization_id
258 AND crci.cost_type_id = crc.cost_type_id
259 AND crci.resource_id = crc.resource_id
260 AND crci.error_flag is null
261 AND crci.group_id = SEQ_NEXTVAL);
262
263 elsif i_run_option = 1 then
264
265 UPDATE CST_RESOURCE_COSTS_INTERFACE crci set
266 error_flag ='E',
267 error_code = 'CST_CANT_INSERT',
268 error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
269 where crci.error_flag is null
270 AND crci.group_id = SEQ_NEXTVAL
271 AND EXISTS (Select 1 from CST_RESOURCE_COSTS crc
272 where crc.organization_id = crci.organization_id
273 AND crc.cost_type_id = crci.cost_type_id
274 AND crc.resource_id = crci.resource_id
275 );
276 fnd_file.put_line(fnd_file.log,'done deleting or erroring out rows as per run option');
277 end if;
278
279 l_stmt_no := 117;
280
281
282 INSERT INTO CST_RESOURCE_COSTS(Resource_id,
283 cost_type_id,
284 last_update_date,
285 last_updated_by,
286 creation_date,
287 created_by,
288 organization_id,
289 Resource_rate,
290 request_id,
291 program_application_id,
292 program_id,
293 program_update_date,
294 Attribute_category,
295 attribute1,
296 attribute2,
297 attribute3,
298 attribute4,
299 attribute5,
300 attribute6,
301 attribute7,
302 attribute8,
303 attribute9,
304 attribute10,
305 attribute11,
306 attribute12,
307 attribute13,
308 attribute14,
309 attribute15)
310 SELECT resource_id,
311 cost_type_id,
312 sysdate,
313 FND_GLOBAL.USER_ID,
314 sysdate,
315 FND_GLOBAL.USER_ID,
316 organization_id,
317 resource_rate,
318 FND_GLOBAL.CONC_REQUEST_ID,
319 FND_GLOBAL.PROG_APPL_ID,
320 FND_GLOBAL.CONC_PROGRAM_ID,
321 sysdate,
322 attribute_category,
323 attribute1,
324 attribute2,
325 attribute3,
326 attribute4,
327 attribute5,
328 attribute6,
329 attribute7,
330 attribute8,
331 attribute9,
332 attribute10,
333 attribute11,
334 attribute12,
335 attribute13,
336 attribute14,
337 attribute15
338 FROM CST_RESOURCE_COSTS_INTERFACE
339 WHERE error_flag is null
340 AND group_id = SEQ_NEXTVAL;
341 fnd_file.put_line(fnd_file.log,'done inserting ' || to_char(SQL%ROWCOUNT) ||' rows into CST_RESOURCE_COSTS');
342
343 l_stmt_no := 120;
344
345 UPDATE CST_RESOURCE_COSTS_INTERFACE
346 set process_flag = 5
347 where process_flag = 4
348 and error_flag is null
349 and group_id = SEQ_NEXTVAL;
350
351 IF i_del_option = 1 then
352 delete from CST_RESOURCE_COSTS_INTERFACE
353 WHERE process_flag = 5
354 AND error_flag is null
355 AND group_id = SEQ_NEXTVAL;
356
357 fnd_file.put_line(fnd_file.log,'done deleting ' || to_char(SQL%ROWCOUNT) || ' rows that were successfully processed ');
358 END IF;
359 COMMIT;
360
361 fnd_file.put_line(fnd_file.log,'--------done , exiting Validate_resource_costs--------');
362 EXCEPTION
363
364 when others then
365 rollback;
366 fnd_file.put_line(fnd_file.log,'Validate_resource_costs(' || to_char(l_stmt_no) ||'),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
367 Error_number := 1;
368 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
369
370 END Validate_resource_costs;
371
372 /*This procedure Start_res_cost_import_process is the Starting point for the process and calls the appropriate procedures after verifying that there are rows to process in the interface table */
373
374
375 Procedure Start_res_cost_import_process(Error_number OUT NOCOPY NUMBER
376 ,i_Next_value IN VARCHAR2
377 ,i_grp_id IN NUMBER
378 ,i_cst_type IN VARCHAR2
379 ,i_del_option IN NUMBER
380 ,i_run_option IN NUMBER) is
381
382 Err NUMBER := 0;
383 CONC_REQUEST BOOLEAN;
384 l_crci_count NUMBER := 0;
385 CST_STOP_EXCEPTION EXCEPTION;
386 i_count NUMBER;
387 BEGIN
388 IF i_Next_value is null then
389 UPDATE CST_RESOURCE_COSTS_INTERFACE crci
390 SET group_id = i_grp_id
391 where process_flag = 1
392 AND error_flag is null;
393 END IF;
394
395 Select count(*) into l_crci_count
396 FROM CST_RESOURCE_COSTS_INTERFACE crci
397 where crci.group_id = i_grp_id
398 AND crci.error_flag is null
399 AND crci.process_flag = 1
400 AND rownum =1;
401
402 If l_crci_count = 0 then
403 fnd_file.put_line(fnd_file.log,'no rows to process in CST_RESOURCE_COSTS_INTERFACE,quitting.....');
404 return;
405 end If;
406
407 Validate_resource_costs(Err,i_grp_id,i_cst_type,i_del_option,i_run_option);
408
409 IF Err = 1 then
410 Error_number := 1;
411 raise CST_STOP_EXCEPTION;
412 END IF;
413
414 select count(*) into i_count from CST_RESOURCE_COSTS_INTERFACE
415 where group_id = i_grp_id
416 and error_flag ='E';
417
418 IF i_count > 0 then
422
419 CONC_REQUEST := fnd_concurrent.set_completion_status('WARNING',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
420 fnd_file.put_line(fnd_file.log,(fnd_message.get_string('BOM','CST_MSG_CRCI')));
421 END IF;
423 EXCEPTION
424 when others then
425 rollback;
426 fnd_file.put_line(fnd_file.log,'CST_RES_IMPORT_PROCESS.Start_res_cost_import_process() Exception occured');
427 CONC_REQUEST := fnd_concurrent.set_completion_status('ERROR',substrb((fnd_message.get_string('BOM','CST_EXCEPTION_OCCURED')),1,240));
428
429
430 END Start_res_cost_import_process;
431
432
433 END CST_RES_COST_IMPORT_INTERFACE;
[Home] [Help]