[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_RESOURCE_TRANX_PVT
Source
1 PACKAGE BODY AHL_PRD_RESOURCE_TRANX_PVT AS
2 /*$Header: AHLVTRSB.pls 120.18 2008/01/15 00:47:10 sikumar ship $*/
3 --
4 G_PKG_NAME VARCHAR2(30):='AHL_PRD_RESOURCE_TRANX_PVT';
5 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
6 PROCEDURE VALIDATE_RES_TRNX
7 (
8 p_prd_resrc_txn_tbl IN OUT NOCOPY PRD_RESOURCE_TXNS_TBL,
9 x_return_status OUT NOCOPY VARCHAR2
10 )
11 AS
12 l_ctr NUMBER:=0;
13 Cursor ValidWrkDet(C_WORKORDER_ID NUMBER)
14 Is
15 Select wip_entity_id, workorder_name, status_code
16 from AHL_WORKORDERS
17 Where Workorder_id=C_WORKORDER_ID;
18
19 l_wrkrec ValidWrkDet%rowtype;
20
21 -- rroy
22 -- resource validation should be based on resource sequence number
23 -- not resource name, since resource name is not entered if the resource
24 -- is not validated against the lov
25 -- sracha 7/31.
26 -- changing validation to be based on resource name or resource id.
27 -- resource sequence will be derived if exists.
28 /*
29 Cursor GetResourceDet(c_op_seq_num NUMBER,
30 c_org_id NUMBER,
31 c_wo_id NUMBER,
32 c_res_code VARCHAR2)
33 -- c_res_seq_num NUMBER)
34 IS
35 SELECT bomr.resource_code,
36 bomr.resource_type, aor.resource_sequence_num
37 FROM BOM_RESOURCES bomr,
38 AHL_WORKORDER_OPERATIONS awop,
39 AHL_OPERATION_RESOURCES aor
40 WHERE awop.workorder_operation_id = aor.workorder_operation_id(+)
41 AND bomr.resource_id = aor.resource_id(+)
42 AND awop.operation_sequence_num = c_op_seq_num
43 --AND aor.resource_sequence_num = c_res_seq_num
44 AND bomr.resource_code = c_res_code
45 AND awop.workorder_id = c_wo_id
46 AND bomr.organization_id = c_org_id;
47 */
48
49 Cursor GetResourceDet(c_org_id NUMBER,
50 c_res_code VARCHAR2)
51 --
52 IS
53 SELECT bomr.resource_code, bomr.resource_type
54 FROM BOM_RESOURCES bomr
55 WHERE bomr.organization_id = c_org_id
56 and bomr.resource_code = c_res_code;
57
58 /*Select * from bom_resources
59 where resource_code=c_resource_name
60 and organization_id=c_org_id;*/
61 l_res_rec GetResourceDet%rowtype;
62
63 -- Adithya modified the code for Bug # 6326254 - Start
64 CURSOR get_instance_sernum (c_department_id NUMBER,
65 c_serial_number VARCHAR2,
66 c_resource_id NUMBER,
67 c_organization_id NUMBER)
68 IS
69 SELECT instance_id
70 FROM bom_dept_res_instances
71 WHERE department_id in (
72 select
73 distinct nvl(bodres.share_from_dept_id, bodres.department_id)
74 from
75 bom_departments bomdep,
76 bom_department_resources bodres
77 where
78 bodres.department_id = bomdep.department_id and
79 bomdep.department_id = c_department_id and
80 bomdep.organization_id = c_organization_id
81 )
82 and Serial_Number=c_serial_number
83 and Resource_id=c_resource_id;
84
85 -- bug# 4553747 - Fixed employee validation.
86 -- validate employee_num.
87 -- remove dependency on resource requirements.
88 CURSOR chk_valid_emp_csr(p_org_id in number,
89 --p_job_id in number,
90 --p_oper_seq in number,
91 --p_resrc_seq in number,
92 p_resrc_id in number,
93 --p_emp_num in number) -- fix for bug# 6032288.
94 p_emp_num in varchar2)
95 IS
96 SELECT 'x'
97 FROM mtl_employees_current_view pf, bom_resource_employees bre, bom_dept_res_instances bdri
98 --, ahl_pp_requirement_v aprv
99 WHERE --aprv.department_id = bdri.department_id
100 --and aprv.RESOURCE_ID= bdri.resource_id
101 --and
102 bre.instance_id = bdri.instance_id
103 and pf.employee_id=bre.person_id
104 and pf.organization_id = bre.organization_id
105 and bdri.resource_id = p_resrc_id
106 --and aprv.OPERATION_SEQUENCE = p_oper_seq
107 --and aprv.RESOURCE_SEQUENCE = p_resrc_seq
108 --and aprv.RESOURCE_ID = p_resrc_id
109 --and aprv.job_id= p_job_id
110 and bre.organization_id= p_org_id
111 and pf.employee_num = p_emp_num;
112
113 -- validate employee_id.
114 -- remove dependency on resource requirements.
115 CURSOR chk_valid_empid_csr(p_org_id in number,
116 --p_job_id in number,
117 --p_oper_seq in number,
118 --p_resrc_seq in number,
119 p_resrc_id in number,
120 p_emp_id in number)
121
122 IS
123 SELECT 'x'
124 FROM bom_resource_employees bre, bom_dept_res_instances bdri --,
125 --ahl_pp_requirement_v aprv
126 WHERE --aprv.department_id = bdri.department_id
127 --and aprv.RESOURCE_ID= bdri.resource_id
128 --and
129 bre.instance_id = bdri.instance_id
130 and bre.person_id = p_emp_id
131 and bdri.resource_id = p_resrc_id
132 --and aprv.OPERATION_SEQUENCE = p_oper_seq
133 --and aprv.RESOURCE_SEQUENCE = p_resrc_seq
134 --and aprv.RESOURCE_ID = p_resrc_id
135 --and aprv.job_id= p_job_id
136 and bre.organization_id= p_org_id;
137
138 -- rroy
139 -- R12 Tech UIs
140 CURSOR get_wo_release_date(c_wip_entity_id NUMBER)
141 IS
142 SELECT DATE_RELEASED
143 FROM WIP_DISCRETE_JOBS
144 WHERE WIP_ENTITY_ID = c_wip_entity_id;
145
146 /*
147 Cursor get_wo_org_id(c_wo_id number)
148 Is
149 Select vst.organization_id
150 from ahl_workorders wo,
151 ahl_visits_b vst
152 where vst.visit_id = wo.visit_id
153 and wo.workorder_id = c_wo_id;
154 */
155
156 l_release_date DATE;
157 l_inst_id NUMBER;
158 l_return_status VARCHAR2(1);
159 l_org_id NUMBER;
160
161 l_junk VARCHAR2(1);
162
163 l_proc_name VARCHAR2(80) := 'VALIDATE_RES_TRNX';
164
165 l_msg_count NUMBER;
166 l_msg_data VARCHAR2(2000);
167 l_msg_index_out number;
168
169 BEGIN
170 IF G_DEBUG='Y' THEN
171 AHL_DEBUG_PUB.debug( 'Start of procedure',l_proc_name);
172 END IF;
173
174 IF p_prd_resrc_txn_tbl.COUNT >0
175 THEN
176 FOR i in p_prd_resrc_txn_tbl.FIRST..p_prd_resrc_txn_tbl.LAST
177 LOOP
178 IF p_prd_resrc_txn_tbl(I).workorder_id IS NULL OR
179 p_prd_resrc_txn_tbl(I).workorder_id=FND_API.G_MISS_NUM
180 THEN
181 FND_MESSAGE.set_name('AHL','AHL_PRD_WORKORDER_ID_NULL');
182 FND_MSG_PUB.ADD;
183 ELSE
184
185 /* sracha: already queried this info in translate_meaning_to_id procedure.
186 If p_prd_resrc_txn_tbl(I).organization_id is null
187 or p_prd_resrc_txn_tbl(I).organization_id=fnd_api.G_miss_num
188 Then
189 OPEN get_wo_org_id(p_prd_resrc_txn_tbl(I).workorder_id);
190 FETCH get_wo_org_id INTO l_org_id;
191 CLOSE get_wo_org_id;
192 --FND_MESSAGE.set_name('AHL','AHL_PRD_ORGID_NULL');
193 --FND_MSG_PUB.ADD;
194 Else
195 l_org_id := p_prd_resrc_txn_tbl(I).organization_id;
196 End if;
197 **sracha */
198
199 IF (G_DEBUG = 'Y') THEN
200 AHL_DEBUG_PUB.Debug('Before GetResourceDet:' || l_org_id || ':' || p_prd_resrc_txn_tbl(i).resource_name);
201 END IF;
202
203 Open GetResourceDet(--p_prd_resrc_txn_tbl(i).operation_sequence_num,
204 p_prd_resrc_txn_tbl(i).organization_id,
205 --p_prd_resrc_txn_tbl(i).workorder_id,
206 --p_prd_resrc_txn_tbl(i).resource_sequence_num);
207 p_prd_resrc_txn_tbl(i).resource_name);
208 Fetch GetResourceDet into l_res_rec;
209 if GetResourceDet%NotFound
210 Then
211 FND_MESSAGE.set_name('AHL','AHL_PRD_RESOURCE_ID_INVALID');
212 FND_MESSAGE.SET_TOKEN('RES_NAME', p_prd_resrc_txn_tbl(i).resource_name);
213 FND_MSG_PUB.ADD;
214 End if;
215 Close GetResourceDet;
216
217 Open ValidWrkDet(p_prd_resrc_txn_tbl(I).workorder_id);
218 Fetch ValidWrkDet into l_wrkrec;
219 If ValidWrkDet%NOTFOUND
220 THEN
221 FND_MESSAGE.set_name('AHL','AHL_PRD_WORKORDER_ID_INVALID');
222 FND_MESSAGE.SET_TOKEN('RECORD',p_prd_resrc_txn_tbl(I).WORKORDER_ID);
223 FND_MSG_PUB.ADD;
224 Close ValidWrkDet;
225 return;
226 END IF;
227 Close ValidWrkDet;
228
229 -- rroy
230 -- ACL Changes
231 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
232 p_workorder_id => p_prd_resrc_txn_tbl(i).workorder_id,
233 p_ue_id => NULL,
234 p_visit_id => NULL,
235 p_item_instance_id => NULL);
236 IF l_return_status = FND_API.G_TRUE THEN
237 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_RESTXN_UNTLCKD');
238 FND_MESSAGE.Set_Token('WO_NAME', l_wrkrec.workorder_name);
239 FND_MSG_PUB.ADD;
240 IF G_DEBUG='Y' THEN
241 AHL_DEBUG_PUB.debug('Unit is Locked',l_proc_name);
242 END IF;
243 END IF;
244 -- rroy
245 -- ACL Changes
246
247 -- rroy
248 -- R12
249 -- From EAM User Guide
250 -- Workorders which are Unreleased (1), Complete No Charge (5), Closed (12), On Hold (6)
251 -- Cannot be charged resources
252 -- Hence adding these additional statuses to the validation below
253 -- Also, resources can be charged when the workorder status is Complete(4). Hence, removing
254 -- this status from the validation below
255 IF l_wrkrec.STATUS_CODE = '12' OR l_wrkrec.STATUS_CODE = '6'
256 OR l_wrkrec.STATUS_CODE = '13' OR l_wrkrec.STATUS_CODE = '7'
257 OR l_wrkrec.STATUS_CODE = '1' OR l_wrkrec.STATUS_CODE = '5'
258 THEN
259 FND_MESSAGE.set_name('AHL','AHL_PRD_RESOURCE_CANNOTEDIT');
260 FND_MSG_PUB.ADD;
261 return;
262 END IF;
263 END IF; -- work order ID is null
264
265 IF G_DEBUG='Y' THEN
266 AHL_DEBUG_PUB.debug( 'After Work Order ID validation:status is:' || l_wrkrec.STATUS_CODE,l_proc_name);
267 END IF;
268
269 IF p_prd_resrc_txn_tbl(I).workorder_operation_id IS NULL
270 OR p_prd_resrc_txn_tbl(I).workorder_operation_id=FND_API.G_MISS_NUM
271 THEN
272 FND_MESSAGE.set_name('AHL','AHL_PRD_WORKORDER_OP_ID_NULL');
273 FND_MSG_PUB.ADD;
274 END IF;
275
276 -- validate operation sequence.
277 If p_prd_resrc_txn_tbl(I).operation_sequence_num is null
278 or p_prd_resrc_txn_tbl(I).operation_sequence_num=fnd_api.g_miss_num
279 Then
280 FND_MESSAGE.set_name('AHL','AHL_PRD_OPSEQNUM_NULL');
281 FND_MSG_PUB.ADD;
282 Else
283 --Select count(*) into l_ctr
284 Select 1 into l_ctr
285 from AHL_WORKORDER_OPERATIONS A
286 WHERE A.WORKORDER_ID=p_prd_resrc_txn_tbl(I).workorder_id
287 AND A.OPERATION_SEQUENCE_NUM=p_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM;
288
289 IF nvl(l_ctr,0)=0
290 THEN
291 FND_MESSAGE.set_name('AHL','AHL_PRD_INVALID_OP_SEQ_NUM');
292 FND_MESSAGE.SET_TOKEN('RECORD',p_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM);
293 FND_MSG_PUB.ADD;
294 END IF;
295 End if; -- p_prd_resrc_txn_tbl(I).operation_sequence_num is null
296
297 -- Validate resource sequence.
298 If p_prd_resrc_txn_tbl(I).resource_sequence_num is not null
299 and p_prd_resrc_txn_tbl(I).resource_sequence_num<>fnd_api.g_miss_num
300 Then
301 --Select count(*) into l_ctr
302 Select 1 into l_ctr
303 From AHL_OPERATION_RESOURCES A
304 WHERE A.WORKORDER_OPERATION_ID=p_prd_resrc_txn_tbl(I).workorder_OPERATION_id
305 AND A.RESOURCE_SEQUENCE_NUM=p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM;
306
307 IF nvl(l_ctr,0)=0
308 THEN
309 FND_MESSAGE.set_name('AHL','AHL_PRD_RESOURCE_SEQ_INV');
310 FND_MESSAGE.SET_TOKEN('RECORD',p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
311 FND_MSG_PUB.ADD;
312 END IF;
313 End if;
314
315 -- validate department id.
316 If p_prd_resrc_txn_tbl(I).department_id is null or
317 p_prd_resrc_txn_tbl(I).department_id=fnd_api.g_miss_num
318 Then
319 FND_MESSAGE.set_name('AHL','AHL_PRD_TRX_DEPTID_NULL');
320 FND_MESSAGE.SET_TOKEN('OPER_RES',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
321 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
322 FND_MSG_PUB.ADD;
323 End if;
324
325 IF G_DEBUG='Y' THEN
326 AHL_DEBUG_PUB.debug( 'After resource seq/dept validation',l_proc_name);
327 END IF;
328
329 --Validate for employee number when resource is 'Labor'
330 IF l_res_rec.resource_type =2 THEN
331 --Check for employee id
332 IF p_prd_resrc_txn_tbl(I).employee_num IS NULL AND p_prd_resrc_txn_tbl(I).person_id IS NULL
333 THEN
334 FND_MESSAGE.set_name('AHL','AHL_PRD_EMPLOYEE_NULL');
335 FND_MSG_PUB.ADD;
336 ELSIF (p_prd_resrc_txn_tbl(I).person_id IS NULL) THEN -- only when person_id is not there.
337 OPEN chk_valid_emp_csr(p_prd_resrc_txn_tbl(I).organization_id,
338 --p_prd_resrc_txn_tbl(I).workorder_id,
339 --p_prd_resrc_txn_tbl(I).operation_sequence_num,
340 --p_prd_resrc_txn_tbl(I).resource_sequence_num,
341 p_prd_resrc_txn_tbl(I).resource_id,
342 p_prd_resrc_txn_tbl(I).employee_num);
343 FETCH chk_valid_emp_csr INTO l_junk;
344 IF (chk_valid_emp_csr%NOTFOUND) THEN
345 FND_MESSAGE.set_name('AHL','AHL_PRD_EMPNUM_INVALID');
346 FND_MESSAGE.set_token('WRK_ID',l_wrkrec.workorder_name);
347 FND_MESSAGE.set_token('OP_SEQ',p_prd_resrc_txn_tbl(I).operation_sequence_num);
348 --FND_MESSAGE.set_token('RES_SEQ', p_prd_resrc_txn_tbl(I).resource_sequence_num);
349 FND_MESSAGE.set_token('RES_SEQ', p_prd_resrc_txn_tbl(I).resource_name);
350 FND_MSG_PUB.ADD;
351 END IF;
352 CLOSE chk_valid_emp_csr;
353
354 ELSE
355 OPEN chk_valid_empid_csr(p_prd_resrc_txn_tbl(I).organization_id,
356 --p_prd_resrc_txn_tbl(I).workorder_id,
357 --p_prd_resrc_txn_tbl(I).operation_sequence_num,
358 --p_prd_resrc_txn_tbl(I).resource_sequence_num,
359 p_prd_resrc_txn_tbl(I).resource_id,
360 p_prd_resrc_txn_tbl(I).person_id);
361 FETCH chk_valid_empid_csr INTO l_junk;
362 IF (chk_valid_empid_csr%NOTFOUND) THEN
363 FND_MESSAGE.set_name('AHL','AHL_PRD_EMPID_INVALID');
364 FND_MESSAGE.set_token('WRK_ID',l_wrkrec.workorder_name);
368 FND_MSG_PUB.ADD;
365 FND_MESSAGE.set_token('OP_SEQ',p_prd_resrc_txn_tbl(I).operation_sequence_num);
366 --FND_MESSAGE.set_token('RES_SEQ', p_prd_resrc_txn_tbl(I).resource_sequence_num);
367 FND_MESSAGE.set_token('RES_SEQ', p_prd_resrc_txn_tbl(I).resource_name);
369 END IF;
370 CLOSE chk_valid_empid_csr;
371
372 END IF; -- p_prd_resrc_txn_tbl(I).employee_num IS NULL
373
374 END IF; -- l_res_rec.resource_type =2
375
376 IF G_DEBUG='Y' THEN
377 AHL_DEBUG_PUB.debug( 'After employee validation:employee_num:'|| p_prd_resrc_txn_tbl(I).employee_num || ':Person ID:' || p_prd_resrc_txn_tbl(I).person_id,l_proc_name);
378 END IF;
379
380 If p_prd_resrc_txn_tbl(I).uom_code is null
381 Then
382 FND_MESSAGE.set_name('AHL','AHL_PRD_UOM_NULL');
383 FND_MSG_PUB.ADD;
384 End if;
385
386 -- rroy
387 -- R12 Tech UIs
388 -- throw an error if both qty and end date are null
389 If (p_prd_resrc_txn_tbl(I).qty is null OR p_prd_resrc_txn_tbl(I).qty = fnd_api.g_miss_num)
390 AND (p_prd_resrc_txn_tbl(I).end_date IS NULL OR p_prd_resrc_txn_tbl(I).transaction_date IS NULL)
391 THEN
392 FND_MESSAGE.set_name('AHL','AHL_PRD_TRX_QTY_NULL');
393 -- Change the message to reflect that at least one of qty or end date should be given
394 FND_MESSAGE.SET_TOKEN('RECORD',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
395 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
396 FND_MSG_PUB.ADD;
397 --OPER_RES rroy
398 -- R12 Tech UIs
399 -- Negative resource transactions are allowed
400 /*elsif p_prd_resrc_txn_tbl(I).qty <=0
401 then
402 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_QTY_INVALID');
403 FND_MESSAGE.SET_TOKEN('OPER_RES',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
404 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
405 FND_MSG_PUB.ADD;
406 */
407 Else
408 IF (p_prd_resrc_txn_tbl(I).qty is NOT NULL AND p_prd_resrc_txn_tbl(I).end_date IS NOT NULL
409 AND p_prd_resrc_txn_tbl(I).transaction_date IS NOT NULL)
410 THEN
411 FND_MESSAGE.set_name('AHL','AHL_PRD_TRX_QTY_NOT_NULL');
412 FND_MESSAGE.SET_TOKEN('RECORD',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
413 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
414 FND_MSG_PUB.ADD;
415 End if;
416
417 End if;
418
419 IF G_DEBUG='Y' THEN
420 AHL_DEBUG_PUB.debug( 'After quantity validation' ,l_proc_name);
421 END IF;
422
423 -- validate and set txn date or end date.
424 IF (p_prd_resrc_txn_tbl(i).end_date IS NULL) THEN
425 IF (p_prd_resrc_txn_tbl(i).transaction_date) IS NOT NULL THEN
426 p_prd_resrc_txn_tbl(i).end_date := p_prd_resrc_txn_tbl(i).transaction_date + (p_prd_resrc_txn_tbl(I).qty/24);
427
428 IF (G_DEBUG = 'Y') THEN
429 ahl_debug_pub.debug('End Date is null and Txn date is not Null. Txn Date is:' || to_char(p_prd_resrc_txn_tbl(i).transaction_date,'DD-MON-YYYY HH24:MI:SS'));
430 ahl_debug_pub.debug('Calc End Date is:' || to_char(p_prd_resrc_txn_tbl(i).end_date,'DD-MON-YYYY HH24:MI:SS'));
431 END IF;
432
433 IF (p_prd_resrc_txn_tbl(i).end_date > sysdate)
434 THEN
435 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_ENDDT_INVALID');
436 FND_MESSAGE.SET_TOKEN('OPER_RES',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
437 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
438
439 FND_MESSAGE.SET_TOKEN('DATE', to_char(p_prd_resrc_txn_tbl(i).end_date,fnd_date.outputDT_mask));
440 IF (G_DEBUG = 'Y') THEN
441 ahl_debug_pub.debug('End Date > sysdate' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
442 END IF;
443 FND_MSG_PUB.ADD;
444 END IF;
445 END IF; -- (p_prd_resrc_txn_tbl(i).transaction_date)
446 ELSE -- p_prd_resrc_txn_tbl(i).end_date IS NULL
447 IF (p_prd_resrc_txn_tbl(i).transaction_date IS NULL) THEN
448 p_prd_resrc_txn_tbl(i).transaction_date := p_prd_resrc_txn_tbl(i).end_date - (p_prd_resrc_txn_tbl(I).qty/24) ;
449 IF (G_DEBUG = 'Y') THEN
450 ahl_debug_pub.debug('End Date is not null and Txn date is Null. End Date is:' || to_char(p_prd_resrc_txn_tbl(i).End_date,'DD-MON-YYYY HH24:MI:SS'));
451 ahl_debug_pub.debug('Calc Txn Date is:' || to_char(p_prd_resrc_txn_tbl(i).transaction_date,'DD-MON-YYYY HH24:MI:SS'));
452 END IF;
453 END IF;
454 END IF;
455
456
457 -- rroy
458 -- R12 Tech UIs
459 -- Validations for Transaction Date
460 -- transaction date should be less than or equal to sysdate
461 IF p_prd_resrc_txn_tbl(i).transaction_date IS NOT NULL THEN
462 IF p_prd_resrc_txn_tbl(i).transaction_date > sysdate THEN
463 IF (G_DEBUG = 'Y') THEN
464 ahl_debug_pub.debug('Trx Date is greater than sysdate:' || to_char(p_prd_resrc_txn_tbl(i).transaction_date,'DD-MON-YYYY HH24:MI:SS'));
468 FND_MESSAGE.SET_TOKEN('OPER_RES',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
465 END IF;
466
467 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_DT_INVALID');
469 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
470 FND_MESSAGE.SET_TOKEN('DATE', to_char(p_prd_resrc_txn_tbl(i).transaction_date,fnd_date.outputDT_mask));
471 IF (G_DEBUG = 'Y') THEN
472 ahl_debug_pub.debug('Txn Date > sysdate' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
473 END IF;
474 FND_MSG_PUB.ADD;
475 END IF;-- IF p_prd_resrc_txn_tbl(i).transaction_date > sysdate THEN
476
477
478 -- transaction date should be greater than the workorder release date
479 OPEN get_wo_release_date(l_wrkrec.wip_entity_id);
480 FETCH get_wo_release_date INTO l_release_date;
481 CLOSE get_wo_release_date;
482
483 -- Not checking to see that the release date is not null
484 -- because if it is null, then the workorder is not released and
485 -- the resource transactions are not allowed for unreleased workorders in any case.
486 IF p_prd_resrc_txn_tbl(i).transaction_date <
487 nvl(l_release_date, p_prd_resrc_txn_tbl(i).transaction_date - 1)
488 THEN
489 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_DT_RLSDT');
490 FND_MESSAGE.SET_TOKEN('OPER_RES', P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
491 ||p_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
492 FND_MESSAGE.SET_TOKEN('DATE',to_char(l_release_date,fnd_date.outputDT_mask));
493 FND_MESSAGE.set_token('WRK_ID',l_wrkrec.workorder_name);
494 FND_MSG_PUB.ADD;
495 END IF;-- IF p_prd_resrc_txn_tbl(i).transaction_date < l_release_date THEN
496 END IF;-- IF p_prd_resrc_txn_tbl(i).transaction_date IS NOT NULL THEN
497
498 IF G_DEBUG='Y' THEN
499 AHL_DEBUG_PUB.debug( 'After transaction date validation' ,l_proc_name);
500 END IF;
501
502 -- validate serial number.
503 -- Adithya modified the code to take department_id for Bug # 6326254 - Start
504 IF p_prd_resrc_txn_tbl(i).serial_number IS NOT NULL AND
505 p_prd_resrc_txn_tbl(i).serial_number<>FND_API.G_MISS_CHAR
506 THEN
507
508 Open get_instance_sernum (p_prd_resrc_txn_tbl(i).department_id,
509 p_prd_resrc_txn_tbl(i).serial_number,
510 p_prd_resrc_txn_tbl(i).resource_id,
511 p_prd_resrc_txn_tbl(i).organization_id
512 );
513 FETCH get_instance_sernum INTO l_inst_id;
514 IF get_instance_sernum%NOTFOUND
515 THEN
516 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_SERNUM_INVALID');
517 FND_MESSAGE.SET_TOKEN('SERNUMB',P_prd_resrc_txn_tbl(I).serial_number);
518 FND_MSG_PUB.ADD;
519 END IF;
520
521 Close get_instance_sernum;
522 -- Adithya modified the code to take department_id for Bug # 6326254 - End
523 /*
524 ELSE
525 IF (l_res_rec.resource_type = 1) THEN
526 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_MACH_RES_REQD' );
527 FND_MESSAGE.set_token( 'WO_NAME', l_wrkrec.workorder_name );
528 FND_MESSAGE.set_token( 'OP_SEQ', p_prd_resrc_txn_tbl(I).operation_sequence_num );
529 FND_MESSAGE.set_token( 'RES_SEQ', p_prd_resrc_txn_tbl(I).resource_sequence_num );
530 FND_MSG_PUB.add;
531 END IF;*/
532
533 END IF; -- p_prd_resrc_txn_tbl(i).serial_number
534
535 IF G_DEBUG='Y' THEN
536 AHL_DEBUG_PUB.debug( 'After serial number validation' ,l_proc_name);
537 END IF;
538
539 END LOOP;
540 END IF; -- IF p_prd_resrc_txn_tbl.COUNT >0
541 IF G_DEBUG='Y' THEN
542 AHL_DEBUG_PUB.debug( 'End of procedure',l_proc_name);
543 END IF;
544
545 --Adithya added the following debug
546 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
547 p_count => l_msg_count,
548 p_data => l_msg_data);
549 IF l_msg_count > 0 THEN
550 if (l_msg_count = 1) THEN
551
552 IF G_DEBUG='Y' THEN
553 AHL_DEBUG_PUB.debug( l_msg_data,l_proc_name);
554 END IF;
555 else
556 FOR i IN 1..l_msg_count LOOP
557
558 fnd_msg_pub.get(
559 p_encoded => 'F',
560 p_data => l_msg_data,
561 p_msg_index_out => l_msg_index_out);
562 IF G_DEBUG='Y' THEN
563 AHL_DEBUG_PUB.debug( 'Err mesg(i) -'|| i || ' ' || l_msg_data,l_proc_name);
564 END IF;
565
566 end loop;
567 end if;
568 END IF;
569
570 END;
571
572
573 PROCEDURE TRANSLATE_MEANING_TO_ID
574 (
575 p_x_prd_resrc_txn_tbl IN OUT NOCOPY PRD_RESOURCE_TXNS_TBL,
576 x_return_status IN VARCHAR2
577 )
578 AS
579
580 Cursor CurGetOperSeq(c_Oper_seq number,c_work_id number)
581 Is
582 /*
583 Select workorder_operation_id, department_id, department_code
587 */
584 from ahl_workorder_operations_v
585 where operation_sequence_num=c_oper_seq
586 and workorder_id=c_work_id;
588
589 Select awo.workorder_operation_id, wop.department_id, bd.department_code,
590 wo.organization_id
591 from ahl_workorder_tasks_v wo, ahl_workorder_operations awo, bom_departments bd,
592 wip_operations wop
593 where wo.wip_entity_id = wop.wip_entity_id
594 and wop.operation_seq_num = c_Oper_seq
595 and wop.department_id = bd.department_id
596 and wo.workorder_id = awo.workorder_id
597 and awo.operation_sequence_num=c_oper_seq
598 and wo.workorder_id=c_work_id;
599
600 l_oper_rec CurGetOperSeq%rowtype;
601
602
603 Cursor CurGetResSeq(c_workorder_operation_id number,c_res_seq number)
604 Is
605 Select a.resource_id, BR.UNIT_OF_MEASURE UOM_CODE, br.resource_code
606 from ahl_operation_resources a, bom_resources br
607 where a.resource_id = br.resource_id
608 and a.workorder_operation_id = c_workorder_operation_id
609 and resource_sequence_num=c_res_seq;
610
611 l_res_Seq_rec CurGetResSeq%rowtype;
612
613
614 Cursor CurGetDeptdet(c_department_code Varchar2,c_org_id number)
615 Is
616 Select department_id
617 From BOM_DEPARTMENTS
618 Where department_code=C_department_code
619 and organization_id=c_org_id;
620
621 l_deptrec CurGetDeptdet%rowtype;
622
623
624 /*
625 Cursor Curres(WORK_ID NUMBER,C_RES_SEQ NUMBER)
626 Is
627 Select AOR.RESOURCE_ID, BOM.UNIT_OF_MEASURE UOM_CODE
628 FROM AHL_OPERATION_RESOURCES AOR, AHL_WORKORDER_OPERATIONS AWO , BOM_RESOURCES BOM, MFG_LOOKUPS MFG
629 WHERE AOR.RESOURCE_SEQUENCE_NUM = C_RES_SEQ
630 AND AOR.WORKORDER_OPERATION_ID = AWO.WORKORDER_OPERATION_ID
631 AND AWO.WORKORDER_ID = WORK_ID
632 AND AOR.RESOURCE_ID = BOM.RESOURCE_ID
633 AND MFG.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
634 AND MFG.LOOKUP_CODE(+) = BOM.RESOURCE_TYPE;
635 */
636
637 /*Select * -- resource_id, UOM_CODE
638 From AHL_PP_REQUIREMENT_V
639 Where JOB_ID=WORK_ID
640 AND RESOURCE_SEQUENCE=C_RES_SEQ;*/
641
642 Cursor getResID(p_resource_code IN VARCHAR2,
643 p_org_id IN NUMBER,
644 p_dept_id IN NUMBER,
645 p_workorder_operation_id IN NUMBER)
646 Is
647 SELECT BR.RESOURCE_ID, aor.resource_sequence_num, BR.UNIT_OF_MEASURE UOM_CODE
648 FROM BOM_RESOURCES BR, BOM_DEPARTMENT_RESOURCES BDR, ahl_operation_resources aor
649 WHERE BR.RESOURCE_ID = BDR.RESOURCE_ID
650 AND BDR.DEPARTMENT_ID = p_dept_id
651 AND BR.RESOURCE_CODE = p_resource_code
652 AND BR.ORGANIZATION_ID = p_org_id
653 AND aor.resource_id(+) = BDR.resource_id
654 AND aor.workorder_operation_id(+) = p_workorder_operation_id;
655
656 --l_Resrec Curres%rowtype;
657
658 Cursor CurGetActivity(C_ACTIVITY VARCHAR2)
659 Is
660 Select activity_id
661 From CST_ACTIVITIES
662 Where ACTIVITY=C_ACTIVITY;
663
664 Cursor CurGetReason(C_Reason Varchar2)
665 Is
666 Select Reason_id
667 From mtl_transaction_reasons
668 Where reason_name=C_Reason
669 AND NVL(disable_date,SYSDATE+1) >=TRUNC(SYSDATE);
670
671 Cursor CurGetEmployee(C_EMPLOYEE Varchar2, c_org_id number)
672 Is
673 /*
674 SELECT person_id,employee_number,full_name
675 FROM per_all_people_f pf,per_person_types pt
676 WHERE pf.person_type_id = pt.person_type_id
677 AND pt.system_person_type = 'EMP'
678 AND UPPER(pf.employee_number) LIKE UPPER(C_EMPLOYEE)
679 AND TRUNC(SYSDATE) BETWEEN TRUNC(pf.effective_start_date)
680 AND TRUNC(nvl(pf.effective_end_Date,sysdate+1)) ORDER BY 1;
681 */
682
683 -- Bug# 4553747.
684 SELECT employee_id person_id, employee_num employee_number, full_name
685 FROM mtl_employees_current_view
686 WHERE UPPER(employee_num) LIKE UPPER(C_EMPLOYEE)
687 AND organization_id = c_org_id;
688
689 l_emp_rec CurGetEmployee%Rowtype;
690
691 Cursor get_wo_org_id(c_wo_id number)
692 Is
693 Select vst.organization_id
694 from ahl_workorders wo,
695 ahl_visits_b vst
696 where vst.visit_id = wo.visit_id
697 and wo.workorder_id = c_wo_id;
698
699 --Adithya commented out the following code as part of fix for bug# 6326254.
700 /* CURSOR get_instance_sernum (c_department_id NUMBER,c_serial_number VARCHAR2)
701 IS
702 SELECT instance_id
703 FROM bom_dept_res_instances
704 WHERE department_id = c_department_id
705 and Serial_Number=c_serial_number; */
706
707 l_ctr NUMBER:=0;
708
709 l_proc_name VARCHAR2(40) := 'TRANSLATE_MEANING_TO_ID';
710
711 BEGIN
712
713 IF G_DEBUG='Y' THEN
714 AHL_DEBUG_PUB.enable_debug;
715 END IF;
716
717 IF p_x_prd_resrc_txn_tbl.COUNT>0
718 THEN
719 FOR i in p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST
720 LOOP
721 ---
722 IF G_DEBUG='Y' THEN
723 AHL_DEBUG_PUB.debug( ' API Input Dump->',l_proc_name);
724 AHL_DEBUG_PUB.debug( ' workorder_id------->'||p_x_prd_resrc_txn_tbl(I).WORKORDER_ID,l_proc_name);
725 AHL_DEBUG_PUB.debug( ' workorder_Oper_id-->'||p_x_prd_resrc_txn_tbl(I).WORKORDER_operation_id,l_proc_name);
729 AHL_DEBUG_PUB.debug( ' Resource id -->'||p_x_prd_resrc_txn_tbl(I).resource_id,l_proc_name);
726 AHL_DEBUG_PUB.debug( ' operation_sequence->'||p_x_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM,l_proc_name);
727 AHL_DEBUG_PUB.debug( ' resource_sequence-->'||p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_num,l_proc_name);
728 AHL_DEBUG_PUB.debug( ' Organization id -->'||p_x_prd_resrc_txn_tbl(I).organization_id,l_proc_name);
730 AHL_DEBUG_PUB.debug( ' Serial Number------->'||p_x_prd_resrc_txn_tbl(I).serial_number,l_proc_name);
731 AHL_DEBUG_PUB.debug( ' Instance Id------->'||p_x_prd_resrc_txn_tbl(I).instance_id,l_proc_name);
732 AHL_DEBUG_PUB.debug( ' Quantity------->'||p_x_prd_resrc_txn_tbl(I).qty,l_proc_name);
733 AHL_DEBUG_PUB.debug( ' Employee Num------->'||p_x_prd_resrc_txn_tbl(I).employee_num,l_proc_name);
734 AHL_DEBUG_PUB.debug( ' Person ID------->'||p_x_prd_resrc_txn_tbl(I).person_id,l_proc_name);
735 AHL_DEBUG_PUB.debug( ' uom_code------->'||p_x_prd_resrc_txn_tbl(I).uom_code,l_proc_name);
736 AHL_DEBUG_PUB.debug( ' uom_meaning------->'||p_x_prd_resrc_txn_tbl(I).uom_meaning,l_proc_name);
737 END IF;
738 ---
739
740 IF p_x_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM IS NOT NULL AND
741 p_x_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM<>FND_API.G_MISS_NUM
742 THEN
743 Open CurGetOperSeq(p_x_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM,
744 p_x_prd_resrc_txn_tbl(I).WORKORDER_ID);
745
746 FETCH CurGetOperSeq into l_oper_rec;
747
748 IF CurGetOperSeq%NOTFOUND
749 THEN
750 FND_MESSAGE.set_name('AHL','AHL_PRD_OPERATION_SEQ_INV');
751 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_resrc_txn_tbl(I).operation_Sequence_num);
752 FND_MSG_PUB.ADD;
753 Else
754 p_x_prd_resrc_txn_tbl(I).Workorder_operation_id:=l_oper_rec.workorder_operation_id;
755
756 IF (p_x_prd_resrc_txn_tbl(I).organization_id IS NULL) OR
757 (p_x_prd_resrc_txn_tbl(I).organization_id = FND_API.G_MISS_NUM) THEN
758 p_x_prd_resrc_txn_tbl(I).organization_id:=l_oper_rec.organization_id;
759 END IF;
760
761 -- Adithya commented out the code for bug# 6326254. Charge deparment can be different
762 -- from the operation department.
763 -- Adithya added code to default dept id/code if they are null - Bug# 6452479.
764 IF ( p_x_prd_resrc_txn_tbl(I).department_id IS NULL OR
765 p_x_prd_resrc_txn_tbl(I).department_id = FND_API.G_MISS_NUM ) AND
766 ( p_x_prd_resrc_txn_tbl(I).department_code IS NULL OR
767 p_x_prd_resrc_txn_tbl(I).department_code = FND_API.G_MISS_CHAR )
768 THEN
769 IF G_DEBUG='Y' THEN
770 AHL_DEBUG_PUB.debug( 'Defaulting Dept Id and Code',l_proc_name);
771 END IF;
772 p_x_prd_resrc_txn_tbl(I).department_id:=l_oper_rec.department_id;
773 p_x_prd_resrc_txn_tbl(I).department_code:=l_oper_rec.department_code;
774 p_x_prd_resrc_txn_tbl(I).organization_id:=l_oper_rec.organization_id;
775 ELSIF ( p_x_prd_resrc_txn_tbl(I).department_id IS NULL OR
776 p_x_prd_resrc_txn_tbl(I).department_id = FND_API.G_MISS_NUM ) AND
777 ( p_x_prd_resrc_txn_tbl(I).department_code IS NOT NULL AND
778 p_x_prd_resrc_txn_tbl(I).department_code <> FND_API.G_MISS_CHAR )
779 THEN
780 OPEN CurGetDeptdet(p_x_prd_resrc_txn_tbl(I).department_code
781 ,p_x_prd_resrc_txn_tbl(I).organization_id);
782 FETCH CurGetDeptdet INTO p_x_prd_resrc_txn_tbl(I).department_id;
783 IF(CurGetDeptdet%NOTFOUND)THEN
784 FND_MESSAGE.set_name('AHL','AHL_PRD_TRX_DEPT_INV');
785 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_resrc_txn_tbl(I).DEPARTMENT_CODE);
786 FND_MESSAGE.SET_TOKEN('RECORD',nvl(p_x_prd_resrc_txn_tbl(I).Operation_Sequence_num,'')
787 ||'-'||nvl(p_x_prd_resrc_txn_tbl(I).resource_Sequence_num,''));
788 FND_MSG_PUB.ADD;
789 END IF;
790 CLOSE CurGetDeptdet;
791
792 END IF;
793
794 END IF;
795 CLOSE CurGetOperSeq;
796 ELSE
797 FND_MESSAGE.set_name('AHL','AHL_PRD_OPSEQNUM_NULL');
798 FND_MSG_PUB.ADD;
799 END IF; -- p_x_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM
800
801 If p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM is not null and
802 p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM<>fnd_api.g_miss_num
803 Then
804
805 Open CurGetResSeq(p_x_prd_resrc_txn_tbl(I).WORKORDER_OPERATION_ID,
806 p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
807 FETCH CurGetResSeq into l_res_seq_rec;
808 IF CurGetResSeq%NOTFOUND
809 THEN
810 FND_MESSAGE.set_name('AHL','AHL_PRD_RESOURCE_SEQ_INV');
811 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM,false);
812 FND_MSG_PUB.ADD;
813 ELSE
814 p_x_prd_resrc_txn_tbl(I).UOM_CODE:=l_res_seq_rec.UOM_CODE;
815 p_x_prd_resrc_txn_tbl(I).RESOURCE_ID:=l_res_seq_rec.RESOURCE_ID;
819 -- p_x_prd_resrc_txn_tbl(I).WORKORDER_OPERATION_ID,
816 p_x_prd_resrc_txn_tbl(I).RESOURCE_NAME :=l_res_seq_rec.RESOURCE_CODE;
817 /*
818 Open Curres(p_x_prd_resrc_txn_tbl(I).WORKORDER_ID,
820 p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM);
821 FETCH Curres into l_Resrec;
822 IF Curres%NOTFOUND
823 THEN
824 FND_MESSAGE.set_name('AHL','AHL_PRD_OPERATION_SEQ_INV');
825 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_resrc_txn_tbl(I).operation_Sequence_num,false);
826 FND_MSG_PUB.ADD;
827 ELSE
828 p_x_prd_resrc_txn_tbl(I).resource_id:=l_resrec.resource_id;
829 p_x_prd_resrc_txn_tbl(I).UOM_CODE:=l_resrec.UOM_CODE;
830 END IF;
831 CLOSE Curres;
832 */
833 END If; -- CurGetResSeq%NOTFOUND
834 CLOSE CurGetResSeq;
835 Else
836 -- check resource name.
837 IF (p_x_prd_resrc_txn_tbl(I).Resource_Name is not null and
838 p_x_prd_resrc_txn_tbl(I).Resource_Name <> fnd_api.g_miss_char) THEN
839 OPEN getResID(p_x_prd_resrc_txn_tbl(I).resource_name,
840 p_x_prd_resrc_txn_tbl(I).organization_id,
841 p_x_prd_resrc_txn_tbl(I).department_id,
842 p_x_prd_resrc_txn_tbl(I).workorder_operation_id);
843 FETCH getResID INTO p_x_prd_resrc_txn_tbl(I).resource_id,
844 p_x_prd_resrc_txn_tbl(I).resource_sequence_num,
845 p_x_prd_resrc_txn_tbl(I).UOM_CODE;
846 IF (getResID%NOTFOUND) THEN
847 FND_MESSAGE.set_name('AHL','AHL_PP_RESOURCE_NOT_EXISTS');
848 FND_MSG_PUB.ADD;
849 END IF;
850 CLOSE getResID;
851 END IF; -- p_x_prd_resrc_txn_tbl(I).Resource_Name
852
853 End if; -- p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM
854
855 /*
856 IF p_x_prd_resrc_txn_tbl(I).organization_id IS NULL THEN
857 OPEN get_wo_org_id(p_x_prd_resrc_txn_tbl(I).WORKORDER_ID);
858 FETCH get_wo_org_id INTO p_x_prd_resrc_txn_tbl(I).organization_id;
859 CLOSE get_wo_org_id;
860 END IF;
861
862 IF p_x_prd_resrc_txn_tbl(I).department_CODE IS NOT NULL AND
863 p_x_prd_resrc_txn_tbl(I).department_CODE<>FND_API.G_MISS_CHAR
864 THEN
865 OPEN CurGetDeptdet(p_x_prd_resrc_txn_tbl(I).department_CODE,
866 p_x_prd_resrc_txn_tbl(I).organization_id);
867 FETCH CurGetDeptdet into l_deptrec;
868 If CurGetDeptdet%NOTFOUND
869 Then
870 FND_MESSAGE.set_name('AHL','AHL_PRD_TRX_DEPT_INV');
871 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_resrc_txn_tbl(I).DEPARTMENT_CODE);
872 FND_MESSAGE.SET_TOKEN('RECORD',nvl(p_x_prd_resrc_txn_tbl(I).Operation_Sequence_num,'')
873 ||'-'||nvl(p_x_prd_resrc_txn_tbl(I).resource_Sequence_num,''));
874 FND_MSG_PUB.ADD;
875 Else
876 p_x_prd_resrc_txn_tbl(I).DEPARTMENT_ID:=L_DEPTREC.DEPARTMENT_ID;
877 End If;
878 CLOSE CurGetDeptdet;
879 END IF;
880 */
881
882 IF p_x_prd_resrc_txn_tbl(I).ACTIVITY_MEANING IS NOT NULL AND
883 p_x_prd_resrc_txn_tbl(I).ACTIVITY_MEANING<>FND_API.G_MISS_CHAR
884 THEN
885 OPEN CurGetActivity(p_x_prd_resrc_txn_tbl(I).ACTIVITY_MEANING);
886 FETCH CurGetActivity into p_x_prd_resrc_txn_tbl(I).ACTIVITY_ID;
887 If CurGetActivity%NOTFOUND
888 Then
889 FND_MESSAGE.set_name('AHL','AHL_PRD_ACTIVITY_INV');
890 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_resrc_txn_tbl(I).Activity_Meaning);
891 FND_MESSAGE.SET_TOKEN('RECORD',nvl(p_x_prd_resrc_txn_tbl(I).Operation_Sequence_num,
892 '')||'-'||nvl(p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM,''));
893 FND_MSG_PUB.ADD;
894 End If;
895 CLOSE CurGetActivity;
896 END IF;
897
898 IF p_x_prd_resrc_txn_tbl(I).REASON IS NOT NULL AND
899 p_x_prd_resrc_txn_tbl(I).REASON<>FND_API.G_MISS_CHAR
900 THEN
901 OPEN CurGetReason(p_x_prd_resrc_txn_tbl(I).Reason);
902 FETCH CurGetReason into p_x_prd_resrc_txn_tbl(I).REASON_ID;
903 If CurGetReason%NOTFOUND
904 Then
905 FND_MESSAGE.set_name('AHL','AHL_PRD_REASON_INV');
906 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_resrc_txn_tbl(I).Reason,false);
907 FND_MESSAGE.SET_TOKEN('RECORD',nvl(p_x_prd_resrc_txn_tbl(I).Operation_Sequence_num,
908 '')||'-'||nvl(p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_NUM,''),false);
909 FND_MSG_PUB.ADD;
910 End If;
911 CLOSE CurGetReason;
912 END IF;
913
914 --Adithya commented out the following code
915 /*
916 IF p_x_prd_resrc_txn_tbl(I).serial_number IS NOT NULL AND
917 p_x_prd_resrc_txn_tbl(I).serial_number<>FND_API.G_MISS_CHAR
918 THEN
919
920 Open get_instance_sernum (p_x_prd_resrc_txn_tbl(i).department_id,
921 p_x_prd_resrc_txn_tbl(i).serial_number);
922 FETCH get_instance_sernum INTO p_x_prd_resrc_txn_tbl(I).instance_id;
923 IF get_instance_sernum%NOTFOUND
924 THEN
928 END IF;
925 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_SERNUM_INVALID');
926 FND_MESSAGE.SET_TOKEN('SERNUMB',p_x_prd_resrc_txn_tbl(I).serial_number);
927 FND_MSG_PUB.ADD;
929 Close get_instance_sernum;
930
931 END IF;
932 */
933
934
935 If p_x_prd_resrc_txn_tbl(I).EMPLOYEE_NUM is not null and
936 p_x_prd_resrc_txn_tbl(I).EMPLOYEE_NUM<>fnd_api.g_miss_CHAR
937 Then
938 Open CurGetEmployee(p_x_prd_resrc_txn_tbl(I).EMPLOYEE_NUM,p_x_prd_resrc_txn_tbl(I).organization_id);
939 FETCH CurGetEmployee into l_emp_rec;
940 IF CurGetEmployee%NOTFOUND
941 THEN
942 FND_MESSAGE.set_name('AHL','AHL_PRD_EMPNUM_INV');
943 FND_MESSAGE.SET_TOKEN('EMP_NUM',p_x_prd_resrc_txn_tbl(I).EMPLOYEE_NUM);
944 FND_MSG_PUB.ADD;
945 ELSE
946 p_x_prd_resrc_txn_tbl(I).PERSON_ID:=l_emp_rec.person_id;
947 END IF;
948 CLOSE CurGetEmployee;
949 End if;
950
951 IF G_DEBUG='Y' THEN
952 AHL_DEBUG_PUB.debug( ' workorder_id------->'||p_x_prd_resrc_txn_tbl(I).WORKORDER_ID);
953 AHL_DEBUG_PUB.debug( ' workorder_Oper_id-->'||p_x_prd_resrc_txn_tbl(I).WORKORDER_operation_id);
954 AHL_DEBUG_PUB.debug( ' operation_sequence->'||p_x_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM);
955 AHL_DEBUG_PUB.debug( ' resource_sequence-->'||p_x_prd_resrc_txn_tbl(I).RESOURCE_SEQUENCE_num);
956 AHL_DEBUG_PUB.debug( ' Organization id -->'||p_x_prd_resrc_txn_tbl(I).organization_id);
957 AHL_DEBUG_PUB.debug( ' Resource id -->'||p_x_prd_resrc_txn_tbl(I).resource_id);
958 AHL_DEBUG_PUB.debug( ' Serial Number------->'||p_x_prd_resrc_txn_tbl(I).serial_number);
959 AHL_DEBUG_PUB.debug( ' Instance Id------->'||p_x_prd_resrc_txn_tbl(I).instance_id);
960 AHL_DEBUG_PUB.debug( ' Person Id------->'|| p_x_prd_resrc_txn_tbl(I).person_id);
961 AHL_DEBUG_PUB.debug( ' Quantity------->'||p_x_prd_resrc_txn_tbl(I).qty,l_proc_name);
962 AHL_DEBUG_PUB.debug( ' Employee Num------->'||p_x_prd_resrc_txn_tbl(I).employee_num,l_proc_name);
963 AHL_DEBUG_PUB.debug( ' uom_code------->'||p_x_prd_resrc_txn_tbl(I).uom_code,l_proc_name);
964 AHL_DEBUG_PUB.debug( ' uom_meaning------->'||p_x_prd_resrc_txn_tbl(I).uom_meaning,l_proc_name);
965
966 END IF;
967 END LOOP;
968 END IF;
969 END;
970
971
972 PROCEDURE PROCESS_RESOURCE_TXNS
973 (
974 p_api_version IN NUMBER := 1.0,
975 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
976 p_commit IN VARCHAR2 := FND_API.G_FALSE,
977 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
978 p_default IN VARCHAR2 := FND_API.G_FALSE,
979 p_module_type IN VARCHAR2 := NULL,
980 x_return_status OUT NOCOPY VARCHAR2,
981 x_msg_count OUT NOCOPY NUMBER,
982 x_msg_data OUT NOCOPY VARCHAR2,
983 p_x_prd_resrc_txn_tbl IN OUT NOCOPY PRD_RESOURCE_TXNS_TBL
984 )
985 AS
986 l_api_name CONSTANT VARCHAR2(30):= 'PROCESS_RESOURCE_TXNS';
987 l_api_version CONSTANT NUMBER:= 1.0;
988 l_num_rec NUMBER;
989 l_msg_count NUMBER;
990 l_msg_data VARCHAR2(2000);
991 l_return_status VARCHAR2(1);
992 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
993 l_commit VARCHAR2(1):= FND_API.G_FALSE;
994 l_ahl_res_txn_tbl AHL_WIP_JOB_PVT.ahl_res_txn_tbl_type;
995 l_wip_entity_id NUMBER;
996 l_txn_group_id NUMBER;
997 l_ctr NUMBER:=0;
998 l_str_len NUMBER := 0;
999 l_short_mesg VARCHAR2(21) := 'Machine Serial #: ';
1000 l_oper_start_dt DATE;
1001 l_oper_end_dt DATE;
1002
1003 l_Resrc_Require_Tbl AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type;
1004
1005 Cursor GetWipid (C_WORK_ID Number)
1006 Is
1007 Select wip_entity_id
1008 from ahl_workorders
1009 where workorder_id=c_work_id;
1010
1011 Cursor get_oper_res_det(p_workorder_id IN NUMBER,
1012 p_operation_seq_num IN NUMBER)
1013 Is
1014 Select WOP.FIRST_UNIT_START_DATE, WOP.LAST_UNIT_COMPLETION_DATE,
1015 (select nvl(max(resource_seq_num),0) + 10 from wip_operation_resources
1016 where wip_entity_id = WOP.wip_entity_id and operation_seq_num = WOP.operation_seq_num),
1017 WOP.Department_id
1018 From WIP_OPERATIONS WOP, AHL_WORKORDERS AW
1019 Where AW.wip_entity_id = WOP.wip_entity_id
1020 and AW.workorder_id = p_workorder_id
1021 and WOP.operation_seq_num = p_operation_seq_num;
1022
1023 BEGIN
1024 SAVEPOINT PROCESS_RESOURCE_TRANX;
1025
1026 -- Enable Debug
1027
1028 IF G_DEBUG='Y' THEN
1029 -- AHL_DEBUG_PUB.enable_debug;
1030 AHL_DEBUG_PUB.debug('At start of procedure PROCESS_RESOURCE_TRANX');
1031 END IF;
1032
1033
1034
1035 -- Standard call to check for call compatibility.
1036
1037 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1038 p_api_version,
1039 l_api_name,G_PKG_NAME) THEN
1040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041 END IF;
1045 IF FND_API.to_boolean(p_init_msg_list) THEN
1042
1043 -- Initialize message list if p_init_msg_list is set to TRUE.
1044
1046 FND_MSG_PUB.initialize;
1047 END IF;
1048
1049 -- Initialize API return status to success
1050
1051 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1052
1053
1054 -- Debug info.
1055
1056 IF G_DEBUG='Y' THEN
1057 AHL_DEBUG_PUB.debug( 'Enter PROCESS_RESOURCE_TRNX',L_API_NAME);
1058 END IF;
1059 IF p_module_type IN ('JSP','OAF') THEN
1060 IF p_x_prd_resrc_txn_tbl.COUNT>0 THEN
1061 FOR i in p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST LOOP
1062 p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID := NULL;
1063 END LOOP;
1064 END IF;
1065 END IF;
1066 -- Convert meanings to ID values.
1067 TRANSLATE_MEANING_TO_ID
1068 (
1069 p_x_prd_resrc_txn_tbl => p_x_prd_resrc_txn_tbl,
1070 x_return_status =>x_return_status
1071 );
1072
1073 -- check error message.
1074 l_msg_count := FND_MSG_PUB.count_msg;
1075 IF l_msg_count > 0 THEN
1076 X_msg_count := l_msg_count;
1077 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1078 IF G_DEBUG='Y' THEN
1079 AHL_DEBUG_PUB.debug( 'Error Thrown in translate',L_API_NAME);
1080 END IF;
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083
1084
1085 -- Start of API Body
1086
1087 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1088
1089 -- Validate input.
1090 VALIDATE_RES_TRNX
1091 (
1092 p_prd_resrc_txn_tbl => p_x_prd_resrc_txn_tbl,
1093 x_return_status =>x_Return_status
1094 );
1095
1096
1097 -- check errors.
1098 l_msg_count := FND_MSG_PUB.count_msg;
1099 IF l_msg_count > 0 THEN
1100 X_msg_count := l_msg_count;
1101 IF G_DEBUG='Y' THEN
1102 AHL_DEBUG_PUB.debug( 'Error Thrown In Validation',L_API_NAME);
1103 END IF;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106
1107 l_ctr := 0;
1108
1109 -- Create resource requirements if it does not exist.
1110 FOR i IN p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST
1111 LOOP
1112 IF (p_x_prd_resrc_txn_tbl(i).resource_sequence_num is null OR
1113 p_x_prd_resrc_txn_tbl(i).resource_sequence_num = fnd_api.g_miss_num) THEN
1114
1115 l_Resrc_Require_Tbl(l_ctr).workorder_id := p_x_prd_resrc_txn_tbl(i).workorder_id;
1116 l_Resrc_Require_Tbl(l_ctr).operation_seq_number := p_x_prd_resrc_txn_tbl(i).operation_sequence_num;
1117 -- get operation start and end dates.
1118 OPEN get_oper_res_det(p_x_prd_resrc_txn_tbl(i).workorder_id,
1119 p_x_prd_resrc_txn_tbl(i).operation_sequence_num);
1120 FETCH get_oper_res_det INTO l_oper_start_dt, l_oper_end_dt,
1121 p_x_prd_resrc_txn_tbl(i).resource_sequence_num,
1122 -- added to fix bug# 6326254.
1123 p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID;
1124
1125
1126 IF (get_oper_res_det%NOTFOUND) THEN
1127 FND_MESSAGE.set_name('AHL','AHL_PRD_OPERATION_SEQ_INV');
1128 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_resrc_txn_tbl(I).operation_Sequence_num);
1129 FND_MSG_PUB.ADD;
1130 END IF;
1131 CLOSE get_oper_res_det;
1132
1133 IF (G_DEBUG = 'Y') THEN
1134 AHL_DEBUG_PUB.debug('Resource seq number:' || p_x_prd_resrc_txn_tbl(i).resource_sequence_num);
1135 END IF;
1136
1137 l_Resrc_Require_Tbl(l_ctr).resource_seq_number :=
1138 p_x_prd_resrc_txn_tbl(i).resource_sequence_num;
1139 l_Resrc_Require_Tbl(l_ctr).oper_start_date := l_oper_start_dt;
1140 l_Resrc_Require_Tbl(l_ctr).oper_end_date := l_oper_end_dt;
1141 l_Resrc_Require_Tbl(l_ctr).req_start_date := l_oper_start_dt;
1142 l_Resrc_Require_Tbl(l_ctr).req_end_date := l_oper_end_dt;
1143 l_Resrc_Require_Tbl(l_ctr).resource_id := p_x_prd_resrc_txn_tbl(i).resource_id;
1144 l_Resrc_Require_Tbl(l_ctr).resource_Name := p_x_prd_resrc_txn_tbl(i).resource_Name;
1145
1146 l_Resrc_Require_Tbl(l_ctr).uom_code :=substr(p_x_prd_resrc_txn_tbl(i).UOM_CODE,1,3);
1147 -- commented this out as this attribute is now charge department.
1148 --l_Resrc_Require_Tbl(l_ctr).department_id :=p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID;
1149 l_Resrc_Require_Tbl(l_ctr).quantity := 1;
1150
1151 IF p_x_prd_resrc_txn_tbl(i).qty IS NOT NULL THEN
1152 l_Resrc_Require_Tbl(l_ctr).duration := p_x_prd_resrc_txn_tbl(i).qty;
1153 ELSE
1154 -- UOM is hours, so multiplying by 24
1155 l_Resrc_Require_Tbl(l_ctr).duration := ROUND((p_x_prd_resrc_txn_tbl(i).end_date -
1156 p_x_prd_resrc_txn_tbl(i).transaction_date) * 24, 2);
1157 END IF;
1158 l_ctr := l_ctr + 1;
1159 END IF; -- p_x_prd_resrc_txn_tbl(i).resource_sequence_num is null
1163 AHL_PP_RESRC_REQUIRE_PVT.Process_Resrc_Require (
1160 END LOOP;
1161
1162 IF (l_Resrc_Require_tbl.count > 0) THEN
1164 p_api_version => 1.0,
1165 p_init_msg_list => Fnd_Api.G_FALSE,
1166 p_commit => Fnd_Api.G_FALSE,
1167 p_module_type => 'JSP', -- need to pass JSP otherwise EAM api is not called.
1168 p_operation_flag => 'C',
1169 p_interface_flag => NULL,
1170 p_x_Resrc_Require_tbl => l_Resrc_Require_tbl,
1171 x_return_status => l_return_status,
1172 x_msg_count => l_msg_count,
1173 x_msg_data => l_msg_data);
1174
1175 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1176 IF (fnd_log.level_error >= G_DEBUG)THEN
1177 fnd_log.string
1178 (
1179 fnd_log.level_error,
1180 'ahl.plsql.AHL_PRD_RESOURCE_TRANX_PVT.Process_Resource_Txns',
1181 'AHL_PP_RESRC_REQUIRE_PVT.Process_Resrc_Require API returned error'
1182 );
1183 END IF;
1184 RAISE FND_API.G_EXC_ERROR;
1185 END IF;
1186
1187 END IF;
1188
1189 IF G_DEBUG='Y' THEN
1190 AHL_DEBUG_PUB.debug( 'Before Process transactions',L_API_NAME);
1191 AHL_DEBUG_PUB.debug( 'Number of Records'||p_x_prd_resrc_txn_tbl.count,L_API_NAME);
1192 END IF;
1193
1194 l_ctr := 0;
1195 FOR i IN p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST
1196 LOOP
1197
1198 IF G_DEBUG='Y' THEN
1199 AHL_DEBUG_PUB.debug( 'Enter loop ',L_API_NAME);
1200 END IF;
1201
1202 IF p_x_prd_resrc_txn_tbl(i).DML_operation = 'C'
1203 THEN
1204 Open GetWipid (p_x_prd_resrc_txn_tbl(i).workorder_id);
1205 Fetch GetWipid into l_wip_entity_id;
1206 Close GetWipid;
1207
1208 IF G_DEBUG='Y' THEN
1209 AHL_DEBUG_PUB.debug( 'Workorder id'||p_x_prd_resrc_txn_tbl(i).workorder_id,L_API_NAME);
1210 END IF;
1211
1212 l_ahl_res_txn_tbl(l_ctr).wip_entity_id := l_wip_entity_id;
1213 l_ahl_res_txn_tbl(l_ctr).operation_seq_num :=p_x_prd_resrc_txn_tbl(i).Operation_sequence_num;
1214 l_ahl_res_txn_tbl(l_ctr).resource_seq_num :=p_x_prd_resrc_txn_tbl(i).Resource_sequence_num;
1215 l_ahl_res_txn_tbl(l_ctr).resource_id :=p_x_prd_resrc_txn_tbl(i).RESOURCE_ID;
1216 l_ahl_res_txn_tbl(l_ctr).transaction_type :=1;
1217 -- rroy
1218 -- R12 Tech UIs
1219 l_ahl_res_txn_tbl(l_ctr).transaction_date := nvl(p_x_prd_resrc_txn_tbl(i).transaction_date, sysdate);
1220 IF p_x_prd_resrc_txn_tbl(i).qty IS NOT NULL THEN
1221 l_ahl_res_txn_tbl(l_ctr).transaction_quantity := p_x_prd_resrc_txn_tbl(i).qty;
1222 ELSE
1223 l_ahl_res_txn_tbl(l_ctr).transaction_quantity := ROUND((p_x_prd_resrc_txn_tbl(i).end_date - p_x_prd_resrc_txn_tbl(i).transaction_date) * 24, 2);
1224 -- UOM is hours, so multiplying by 24
1225 END IF;
1226 --l_ahl_res_txn_tbl(l_ctr).transaction_quantity :=p_x_prd_resrc_txn_tbl(i).QTY ;
1227
1228 l_ahl_res_txn_tbl(l_ctr).transaction_uom :=substr(p_x_prd_resrc_txn_tbl(i).UOM_CODE,1,3);
1229 l_ahl_res_txn_tbl(l_ctr).department_id :=p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID;
1230 l_ahl_res_txn_tbl(l_ctr).employee_id :=p_x_prd_resrc_txn_tbl(i).PERSON_ID;
1231 l_ahl_res_txn_tbl(l_ctr).activity_id :=p_x_prd_resrc_txn_tbl(i).ACTIVITY_ID;
1232 l_ahl_res_txn_tbl(l_ctr).activity_meaning :=p_x_prd_resrc_txn_tbl(i).ACTIVITY_MEANING;
1233 l_ahl_res_txn_tbl(l_ctr).reason_id :=p_x_prd_resrc_txn_tbl(i).REASON_ID ;
1234 l_ahl_res_txn_tbl(l_ctr).reason :=p_x_prd_resrc_txn_tbl(i).REASON ;
1235
1236 -- bug 3955565
1237 -- the reference field will now contain short message, serial number and reference
1238 -- short message will be of type '; Machine Serial #: ' so that the appended serial number
1239 -- in the reference field makes sense when seen from the WIP UIs.
1240 IF p_x_prd_resrc_txn_tbl(i).serial_number IS NULL THEN
1241 -- if the serial number is null, then simply append a ';' at the end of the ref text
1242 -- to add as a demarking for us
1243 IF p_x_prd_resrc_txn_tbl(i).REFERENCE IS NOT NULL THEN
1244 l_ahl_res_txn_tbl(l_ctr).reference := p_x_prd_resrc_txn_tbl(i).REFERENCE || ';';
1245 END IF;
1246 ELSE
1247 -- if serial number is not null and ref text is null then
1248 -- simply add the serial number with short mesg prefix and without a semi colon
1249 -- 240 is the length of the reference column in the wip_transactions table
1250 IF p_x_prd_resrc_txn_tbl(i).REFERENCE IS NOT NULL THEN
1251 l_str_len := 240 - (length(p_x_prd_resrc_txn_tbl(i).serial_number) + length(l_short_mesg) + 2);
1252 l_ahl_res_txn_tbl(l_ctr).reference := substr(p_x_prd_resrc_txn_tbl(i).REFERENCE, 1, l_str_len) || '; ' || l_short_mesg || p_x_prd_resrc_txn_tbl(i).serial_number;
1253 ELSE
1254 -- ref text is null
1255 -- then simply add the serial number without a ';'
1256 l_ahl_res_txn_tbl(l_ctr).reference := p_x_prd_resrc_txn_tbl(i).REFERENCE || l_short_mesg || p_x_prd_resrc_txn_tbl(i).serial_number;
1260
1257 END IF;
1258 END IF;
1259
1261 l_ahl_res_txn_tbl(l_ctr).serial_number := p_x_prd_resrc_txn_tbl(i).serial_number ;
1262
1263 l_ctr:=l_ctr+1;
1264 End if;
1265
1266 END LOOP;
1267
1268 IF G_DEBUG='Y' THEN
1269 AHL_DEBUG_PUB.debug( 'Before Insert Process transactions',L_API_NAME);
1270
1271 END IF;
1272
1273 IF l_ahl_res_txn_tbl.count >0
1274 Then
1275
1276
1277 AHL_WIP_JOB_PVT.insert_resource_txn
1278 (
1279 p_api_version =>p_api_version,
1280 p_init_msg_list =>L_init_msg_list,
1281 p_commit =>l_commit,
1282 p_validation_level =>p_validation_level,
1283 x_return_status =>x_return_status,
1284 x_msg_count =>l_msg_count,
1285 x_msg_data =>l_msg_data,
1286 p_ahl_res_txn_tbl =>l_ahl_res_txn_tbl
1287 );
1288 End if;
1289
1290 l_msg_count := FND_MSG_PUB.count_msg;
1291 IF l_msg_count > 0 THEN
1292 X_msg_count := l_msg_count;
1293 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294 RAISE FND_API.G_EXC_ERROR;
1295 IF G_DEBUG='Y' THEN
1296 AHL_DEBUG_PUB.debug( 'Validation Errors in interface API');
1297 END IF;
1298
1299 END IF;
1300
1301 --Adithya added
1302 X_msg_count := l_msg_count;
1303
1304 IF FND_API.TO_BOOLEAN(p_commit) THEN
1305 COMMIT;
1306 END IF;
1307
1308 -- Debug info
1309
1310 IF G_DEBUG='Y' THEN
1311 AHL_DEBUG_PUB.debug( 'End of Private api '||l_api_name,'+debug+');
1312
1313 END IF;
1314
1315 IF G_DEBUG='Y' THEN
1316 AHL_DEBUG_PUB.disable_debug;
1317
1318 END IF;
1319
1320 EXCEPTION
1321 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1322 ROLLBACK TO PROCESS_RESOURCE_TRANX;
1323 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1324 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1325 p_count => x_msg_count,
1326 p_data => x_msg_data);
1327
1328 WHEN FND_API.G_EXC_ERROR THEN
1329 ROLLBACK TO PROCESS_RESOURCE_TRANX;
1330 X_return_status := FND_API.G_RET_STS_ERROR;
1331 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1332 p_count => x_msg_count,
1333 p_data => X_msg_data);
1334 WHEN OTHERS THEN
1335 ROLLBACK TO PROCESS_RESOURCE_TRANX;
1336 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1337 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1338 THEN
1339 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1340 p_procedure_name =>l_api_name,
1341 p_error_text =>SUBSTR(SQLERRM,1,240)
1342 );
1343 END IF;
1344 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1345 p_count => x_msg_count,
1346 p_data => X_msg_data);
1347
1348 END;
1349
1350 -- ADDED BY VSUNDARA FOR TRANSIT CHECK ENHANCEMENTS
1351
1352 PROCEDURE VALIDATE_MYWORKORDER_TRNX
1353 (
1354 p_prd_myworkorder_txn_tbl IN PRD_MYWORKORDER_TXNS_TBL,
1355 x_return_status OUT NOCOPY VARCHAR2
1356 )
1357 AS
1358 l_return_status VARCHAR2(1);
1359 l_wo_name VARCHAR2(80);
1360 L_wo_status VARCHAR2(30);
1361
1362 CURSOR get_wo_details(c_workorder_id NUMBER)
1363 IS
1364 SELECT workorder_name
1365 FROM AHL_WORKORDERS
1366 WHERE WORKORDER_ID = c_workorder_id;
1367
1368 BEGIN
1369
1370 IF p_prd_myworkorder_txn_tbl.COUNT >0
1371 THEN
1372 --- Basic Validation..
1373 FOR i IN p_prd_myworkorder_txn_tbl.FIRST..p_prd_myworkorder_txn_tbl.LAST
1374 LOOP
1375 -- rroy
1376 -- R12 Tech UIs
1377 -- Negative resource transactions are allowed starting R12
1378 /*IF p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS IS NOT NULL OR
1379 p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS <> fnd_api.g_miss_num
1380 THEN
1381 IF p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS < 0
1382 THEN
1383 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_QTY_INVALID');
1384 FND_MESSAGE.SET_TOKEN('OPER_RES', p_prd_myworkorder_txn_tbl(i).OPERATION_SEQUENCE||'-'|| p_prd_myworkorder_txn_tbl(i).RESOURCE_SEQUENCE);
1385 FND_MSG_PUB.ADD;
1386
1387 IF G_DEBUG='Y' THEN
1388 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1389 END IF;
1390 END IF;
1391 END IF ;
1392 */
1393 -- rroy
1394 -- R12 Tech UIs
1395
1396 IF p_prd_myworkorder_txn_tbl(i).WORKORDER_ID IS NULL OR
1397 p_prd_myworkorder_txn_tbl(i).WORKORDER_ID = fnd_api.g_miss_num
1398 THEN
1399 FND_MESSAGE.set_name('AHL','AHL_PRD_WORKORDER_ID_NULL');
1400 FND_MSG_PUB.ADD;
1401 IF G_DEBUG='Y' THEN
1405
1402 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1403 END IF;
1404 END IF ;
1406 -- rroy
1407 -- ACL Changes
1408 IF p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS <> 0 THEN
1409 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
1410 p_workorder_id => p_prd_myworkorder_txn_tbl(i).workorder_id,
1411 p_ue_id => NULL,
1412 p_visit_id => NULL,
1413 p_item_instance_id => NULL);
1414 IF l_return_status = FND_API.G_TRUE THEN
1415 OPEN get_wo_details(p_prd_myworkorder_txn_tbl(i).workorder_id);
1416 FETCH get_wo_details INTO l_wo_name;
1417 CLOSE get_wo_details;
1418 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_RESTXN_UNTLCKD');
1419 FND_MESSAGE.Set_Token('WO_NAME', l_wo_name);
1420 FND_MSG_PUB.ADD;
1421 END IF;
1422 END IF;
1423 -- rroy
1424 -- ACL Changes
1425 IF p_prd_myworkorder_txn_tbl(i).OPERATION_SEQUENCE IS NULL OR
1426 p_prd_myworkorder_txn_tbl(i).OPERATION_SEQUENCE = fnd_api.g_miss_num
1427 THEN
1428 FND_MESSAGE.set_name('AHL','AHL_PRD_OPERATION_NULL');
1429 FND_MSG_PUB.ADD;
1430 IF G_DEBUG='Y' THEN
1431 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1432 END IF;
1433 END IF ;
1434
1435 IF p_prd_myworkorder_txn_tbl(i).RESOURCE_SEQUENCE IS NULL OR
1436 p_prd_myworkorder_txn_tbl(i).RESOURCE_SEQUENCE = fnd_api.g_miss_num
1437 THEN
1438 FND_MESSAGE.set_name('AHL','AHL_PRD_RESOURCE_NULL');
1439 FND_MSG_PUB.ADD;
1440 IF G_DEBUG='Y' THEN
1441 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1442 END IF;
1443 END IF ;
1444 END LOOP;
1445 END IF ;
1446
1447 END;
1448
1449
1450 PROCEDURE getOperationRecord
1451 (
1452 p_operation_Id IN NUMBER ,
1453 p_x_operation_rec IN OUT NOCOPY AHL_PRD_OPERATIONS_PVT.PRD_WORKOPERATION_REC,
1454 x_return_status OUT NOCOPY VARCHAR2
1455 )
1456
1457 IS
1458
1459 cursor get_operation_rec(c_operation_id NUMBER)
1460 is
1461 SELECT *
1462 FROM AHL_WORKORDER_OPERATIONS_V
1463 WHERE workorder_operation_id=c_operation_id;
1464
1465 l_operation_rec get_operation_rec%ROWTYPE;
1466
1467 BEGIN
1468
1469 OPEN get_operation_rec(p_operation_Id);
1470 FETCH get_operation_rec INTO l_operation_rec;
1471 CLOSE get_operation_rec;
1472
1473 IF l_operation_rec.scheduled_start_date > SYSDATE THEN
1474 l_operation_rec.actual_start_date := SYSDATE;
1475 ELSE
1476 l_operation_rec.actual_start_date := l_operation_rec.Scheduled_start_date;
1477 END IF;
1478
1479 IF l_operation_rec.scheduled_end_date > SYSDATE THEN
1480 l_operation_rec.actual_end_date := SYSDATE;
1481 ELSE
1482 l_operation_rec.actual_end_date := l_operation_rec.scheduled_end_date;
1483 END IF;
1484
1485 p_x_operation_rec.WORKORDER_OPERATION_ID := l_operation_rec.WORKORDER_OPERATION_ID;
1486 p_x_operation_rec.ACTUAL_START_DATE := l_operation_rec.actual_start_date ;
1487 p_x_operation_rec.ACTUAL_END_DATE := l_operation_rec.actual_end_date;
1488 p_x_operation_rec.OBJECT_VERSION_NUMBER := l_operation_rec.OBJECT_VERSION_NUMBER;
1489 p_x_operation_rec.DML_OPERATION := 'U';
1490 END ;
1491
1492
1493
1494
1495
1496
1497 --- Changes by VSUNDARA For Transit Check
1498
1499 PROCEDURE PROCESS_MYWORKORDER_TXNS
1500 (
1501 p_api_version IN NUMBER := 1.0,
1502 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1503 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1504 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1505 p_default IN VARCHAR2 := FND_API.G_FALSE,
1506 p_module_type IN VARCHAR2 := NULL,
1507 x_return_status OUT NOCOPY VARCHAR2,
1508 x_msg_count OUT NOCOPY NUMBER,
1509 x_msg_data OUT NOCOPY VARCHAR2,
1510 p_x_prd_myworkorder_txn_tbl IN OUT NOCOPY PRD_MYWORKORDER_TXNS_TBL
1511 )
1512
1513 AS
1514
1515 l_api_name CONSTANT VARCHAR2(30):= 'PROCESS_MYWORKORDER_TXNS';
1516 l_api_version CONSTANT NUMBER:= 1.0;
1517 l_num_rec NUMBER;
1518 l_msg_count NUMBER;
1519 l_msg_data VARCHAR2(2000);
1520 l_return_status VARCHAR2(1);
1521 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
1522 l_commit VARCHAR2(1):= FND_API.G_FALSE;
1523 l_ahl_res_txn_tbl AHL_WIP_JOB_PVT.ahl_res_txn_tbl_type;
1524 l_wip_entity_id NUMBER;
1525 l_txn_group_id NUMBER;
1526 l_ctr NUMBER:=0;
1527 l_uom_code VARCHAR2(3);
1528 l_wo_status_code VARCHAR2(30);
1529 l_prd_operation_rec AHL_PRD_OPERATIONS_PVT.PRD_WORKOPERATION_REC;
1530 l_prd_operation_tbl AHL_PRD_OPERATIONS_PVT.PRD_OPERATION_TBL;
1531
1532 Cursor GetWipid (C_WORK_ID Number)
1533 Is
1534 Select wip_entity_id, status_code
1535 from ahl_workorders
1536 where workorder_id=c_work_id;
1537
1538
1539 CURSOR getDefaultUOM
1540 IS
1544 AND UOM_CLASS = 'Time';
1541 SELECT UOM_CODE
1542 FROM MTL_UNITS_OF_MEASURE
1543 WHERE UPPER(UNIT_OF_MEASURE) = UPPER('Hour')
1545
1546
1547 CURSOR getResDetails(p_assignment_id NUMBER)
1548 IS
1549 SELECT
1550 AWAS.ASSIGNMENT_ID,
1551 AWOS.WORKORDER_ID JOB_ID,
1552 AWOP.WORKORDER_OPERATION_ID OPERATION_ID,
1553 AWOS.ORGANIZATION_ID,
1554 AWOP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE,
1555 AOPR.RESOURCE_SEQUENCE_NUM RESOURCE_SEQUENCE,
1556 BOMR.DESCRIPTION RESOURCE_NAME,
1557 BOMR.RESOURCE_TYPE RESOURCE_TYPE_CODE ,
1558 MFGL.MEANING RESOURCE_TYPE_NAME ,
1559 AOPR.RESOURCE_ID,
1560 BOMR.RESOURCE_CODE RESOURCE_CODE ,
1561 PEPF.employee_num EMPLOYEE_NUMBER,
1562 PEPF.FULL_NAME ,
1563 AWOS.DEPARTMENT_NAME,
1564 AWOS.DEPARTMENT_ID,
1565 AWOS.ITEM_INSTANCE_ID
1566 FROM AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDER_TASKS_V AWOS, AHL_WORKORDER_OPERATIONS AWOP,
1567 AHL_OPERATION_RESOURCES AOPR,AHL_DEPARTMENT_SHIFTS ADS, mtl_employees_current_view PEPF, BOM_RESOURCES BOMR, MFG_LOOKUPS MFGL
1568 WHERE AWAS.OPERATION_RESOURCE_ID = AOPR.OPERATION_RESOURCE_ID
1569 AND AWOP.WORKORDER_OPERATION_ID = AOPR.WORKORDER_OPERATION_ID
1570 AND AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
1571 AND AWOS.DEPARTMENT_ID = ADS.DEPARTMENT_ID (+)
1572 AND AWAS.EMPLOYEE_ID = PEPF.EMPLOYEE_ID AND SYSTEM_PERSON_TYPE ='EMP' AND AOPR.RESOURCE_ID = BOMR.RESOURCE_ID
1573 AND MFGL.LOOKUP_CODE(+) = BOMR.RESOURCE_TYPE AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
1574 AND AWAS.ASSIGNMENT_ID = p_assignment_id;
1575
1576 l_res_rec getResDetails%rowtype;
1577
1578 BEGIN
1579 SAVEPOINT PROCESS_MYWORKORDER_TXNS;
1580
1581 -- Enable Debug
1582
1583 IF G_DEBUG='Y' THEN
1584 AHL_DEBUG_PUB.enable_debug;
1585 END IF;
1586
1587
1588
1589 -- Standard call to check for call compatibility.
1590
1591 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1592 p_api_version,
1593 l_api_name,G_PKG_NAME) THEN
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 END IF;
1596
1597 -- Initialize message list if p_init_msg_list is set to TRUE.
1598
1599 IF FND_API.to_boolean(p_init_msg_list) THEN
1600 FND_MSG_PUB.initialize;
1601 END IF;
1602
1603 -- Initialize API return status to success
1604
1605 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1606
1607
1608 -- Debug info.
1609
1610 IF G_DEBUG='Y' THEN
1611 AHL_DEBUG_PUB.debug( 'Enter PROCESS_MYWORKORDER_TXNS',L_API_NAME);
1612 END IF;
1613
1614 VALIDATE_MYWORKORDER_TRNX(
1615 p_prd_myworkorder_txn_tbl => p_x_prd_myworkorder_txn_tbl,
1616 x_return_status =>x_return_status
1617 );
1618
1619
1620 l_msg_count := FND_MSG_PUB.count_msg;
1621 IF l_msg_count > 0 THEN
1622 X_msg_count := l_msg_count;
1623 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1624 IF G_DEBUG='Y' THEN
1625 AHL_DEBUG_PUB.debug( 'Error Thrown in Validate',L_API_NAME);
1626 END IF;
1627 RAISE FND_API.G_EXC_ERROR;
1628 END IF;
1629 -- Get the Defalut UOM Code --- Hr
1630 Open getDefaultUOM;
1631 fetch getDefaultUOM into l_uom_code;
1632 CLOSE getDefaultUOM;
1633
1634 IF p_x_prd_myworkorder_txn_tbl.COUNT > 0 THEN
1635 FOR i IN p_x_prd_myworkorder_txn_tbl.FIRST..p_x_prd_myworkorder_txn_tbl.LAST
1636 LOOP
1637 IF p_x_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS > 0
1638 THEN
1639
1640 IF G_DEBUG='Y' THEN
1641 AHL_DEBUG_PUB.debug( 'Enter loop ',L_API_NAME);
1642 END IF;
1643
1644 OPEN GetWipid (p_x_prd_myworkorder_txn_tbl(i).workorder_id);
1645 FETCH GetWipid into l_wip_entity_id, l_wo_status_code;
1646 CLOSE GetWipid;
1647 IF l_wo_status_code IN ('1', '12', '7', '6', '13', '5') THEN
1648 FND_MESSAGE.set_name('AHL', 'AHL_PRD_RESOURCE_CANNOTEDIT');
1649 FND_MSG_PUB.ADD;
1650 RAISE FND_API.G_EXC_ERROR;
1651 END IF;
1652
1653 Open getResDetails(p_x_prd_myworkorder_txn_tbl(i).ASSIGNMENT_ID);
1654 FETCH getResDetails into l_res_rec;
1655 CLOSE getResDetails;
1656 IF G_DEBUG='Y' THEN
1657 AHL_DEBUG_PUB.debug( 'Workorder id'||p_x_prd_myworkorder_txn_tbl(i).workorder_id,L_API_NAME);
1658 END IF;
1659 l_ahl_res_txn_tbl(l_ctr).operation_seq_num :=l_res_rec.OPERATION_SEQUENCE;
1660 l_ahl_res_txn_tbl(l_ctr).resource_seq_num :=l_res_rec.RESOURCE_SEQUENCE;
1661 l_ahl_res_txn_tbl(l_ctr).resource_id :=l_res_rec.RESOURCE_ID;
1662 l_ahl_res_txn_tbl(l_ctr).transaction_type :=1;
1663 l_ahl_res_txn_tbl(l_ctr).transaction_date :=sysdate;
1664 l_ahl_res_txn_tbl(l_ctr).transaction_quantity :=p_x_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS;
1665 --- Default UOM CODE
1666 l_ahl_res_txn_tbl(l_ctr).transaction_uom := l_uom_code;
1667 l_ahl_res_txn_tbl(l_ctr).department_id :=l_res_rec.DEPARTMENT_ID;
1668 l_ahl_res_txn_tbl(l_ctr).employee_id :=p_x_prd_myworkorder_txn_tbl(i).EMPLOYEE_ID;
1672 END IF;
1669 l_ahl_res_txn_tbl(l_ctr).wip_entity_id := l_wip_entity_id;
1670
1671 l_ctr:=l_ctr+1;
1673 END LOOP;
1674 END IF;
1675
1676 IF G_DEBUG='Y' THEN
1677 AHL_DEBUG_PUB.debug( 'Before Insert Process transactions',L_API_NAME);
1678
1679 END IF;
1680
1681
1682 IF l_ahl_res_txn_tbl.count >0
1683 Then
1684
1685 AHL_WIP_JOB_PVT.insert_resource_txn
1686 (
1687 p_api_version =>p_api_version,
1688 p_init_msg_list =>L_init_msg_list,
1689 p_commit =>l_commit,
1690 p_validation_level =>p_validation_level,
1691 x_return_status =>x_return_status,
1692 x_msg_count =>l_msg_count,
1693 x_msg_data =>l_msg_data,
1694 p_ahl_res_txn_tbl =>l_ahl_res_txn_tbl
1695 );
1696 End if;
1697 l_msg_count := FND_MSG_PUB.count_msg;
1698 IF l_msg_count > 0 THEN
1699 X_msg_count := l_msg_count;
1700 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701 RAISE FND_API.G_EXC_ERROR;
1702 IF G_DEBUG='Y' THEN
1703 AHL_DEBUG_PUB.debug( 'Error in Insert_Resource_Txn API');
1704 END IF;
1705 END IF;
1706
1707 --- Complete the Operation if the Complete Operation is True
1708 l_ctr := 0;
1709 IF p_x_prd_myworkorder_txn_tbl.COUNT > 0 THEN
1710 FOR i IN p_x_prd_myworkorder_txn_tbl.FIRST..p_x_prd_myworkorder_txn_tbl.LAST
1711 LOOP
1712
1713
1714
1715 IF (p_x_prd_myworkorder_txn_tbl(i).OPERATION_COMPLETE IS NOT NULL AND
1716 p_x_prd_myworkorder_txn_tbl(i).OPERATION_COMPLETE <> FND_API.G_MISS_CHAR AND
1717 p_x_prd_myworkorder_txn_tbl(i).OPERATION_COMPLETE = 'Y')
1718 THEN
1719 getOperationRecord(
1720 p_operation_id => p_x_prd_myworkorder_txn_tbl(i).WORKORDER_OPERATION_ID,
1721 p_x_operation_rec => l_prd_operation_rec,
1722 x_return_status => x_return_status);
1723 l_prd_operation_tbl(l_ctr) := l_prd_operation_rec;
1724 l_ctr := l_ctr +1 ;
1725
1726 END IF ;
1727
1728 END LOOP;
1729 END IF;
1730
1731 IF l_prd_operation_tbl.count >0
1732 Then
1733 AHL_PRD_OPERATIONS_PVT.PROCESS_OPERATIONS
1734 (
1735 p_api_version =>1.0,
1736 p_init_msg_list =>FND_API.G_FALSE,
1737 p_commit =>l_commit,
1738 p_validation_level =>p_validation_level,
1739 p_default => FND_API.G_TRUE,
1740 p_module_type => NULL,
1741 p_wip_mass_load_flag => 'N',
1742 x_return_status =>x_return_status,
1743 x_msg_count =>l_msg_count,
1744 x_msg_data =>l_msg_data,
1745 p_x_prd_operation_tbl =>l_prd_operation_tbl
1746 );
1747 End if;
1748 l_msg_count := FND_MSG_PUB.count_msg;
1749 IF l_msg_count > 0 THEN
1750 X_msg_count := l_msg_count;
1751 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752 RAISE FND_API.G_EXC_ERROR;
1753 IF G_DEBUG='Y' THEN
1754 AHL_DEBUG_PUB.debug( 'Error in Process Operations API');
1755 END IF;
1756 END IF;
1757
1758 --- Complete the Operation
1759 IF l_prd_operation_tbl.COUNT > 0 THEN
1760 FOR i IN l_prd_operation_tbl.FIRST..l_prd_operation_tbl.LAST
1761 LOOP
1762 AHL_COMPLETIONS_PVT.complete_operation
1763 (
1764 p_api_version => 1.0,
1765 p_init_msg_list => FND_API.G_TRUE,
1766 p_commit => FND_API.G_FALSE,
1767 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1768 p_default => FND_API.G_FALSE,
1769 p_module_type => NULL,
1770 x_return_status => x_return_status,
1771 x_msg_count => l_msg_count,
1772 x_msg_data => l_msg_data,
1773 p_workorder_operation_id => l_prd_operation_tbl(i).workorder_operation_id,
1774 p_object_version_no => l_prd_operation_tbl(i).object_version_number
1775 );
1776 END LOOP;
1777 END IF;
1778 l_msg_count := FND_MSG_PUB.count_msg;
1779 IF l_msg_count > 0 THEN
1780 X_msg_count := l_msg_count;
1781 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1782 RAISE FND_API.G_EXC_ERROR;
1783 IF G_DEBUG='Y' THEN
1784 AHL_DEBUG_PUB.debug( 'Error in Complete Operation API');
1785 END IF;
1786 END IF;
1787
1788 IF FND_API.TO_BOOLEAN(p_commit) THEN
1789 COMMIT;
1790 END IF;
1791
1792 -- Debug info
1793
1794 IF G_DEBUG='Y' THEN
1795 AHL_DEBUG_PUB.debug( 'End of api '||l_api_name,'+debug+');
1796
1797 END IF;
1798
1799 IF G_DEBUG='Y' THEN
1800 AHL_DEBUG_PUB.disable_debug;
1801 END IF;
1802
1803 EXCEPTION
1804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1805 ROLLBACK TO PROCESS_MYWORKORDER_TXNS;
1806 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1810
1807 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1808 p_count => x_msg_count,
1809 p_data => x_msg_data);
1811 WHEN FND_API.G_EXC_ERROR THEN
1812 ROLLBACK TO PROCESS_MYWORKORDER_TXNS;
1813 X_return_status := FND_API.G_RET_STS_ERROR;
1814 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1815 p_count => x_msg_count,
1816 p_data => X_msg_data);
1817 WHEN OTHERS THEN
1818 ROLLBACK TO PROCESS_MYWORKORDER_TXNS;
1819 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1820 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1821 THEN
1822 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1823 p_procedure_name =>l_api_name,
1824 p_error_text =>SUBSTR(SQLERRM,1,240)
1825 );
1826 END IF;
1827 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1828 p_count => x_msg_count,
1829 p_data => X_msg_data);
1830
1831 END;
1832
1833
1834
1835 FUNCTION Get_transacted_hours
1836 (
1837 p_wip_entity_id IN NUMBER,
1838 p_operation_seq_num IN NUMBER,
1839 p_resource_seq_num IN NUMBER,
1840 p_employee_id IN NUMBER
1841 ) RETURN NUMBER
1842
1843 IS
1844
1845 l_completed_hrs NUMBER ;
1846 l_pending_hrs NUMBER ;
1847 l_return_value NUMBER;
1848
1849
1850 CURSOR get_resource_txns(c_wip_entity_id NUMBER,
1851 c_operation_seq_num NUMBER,
1852 c_resource_seq_num NUMBER,
1853 c_employee_id NUMBER )
1854 IS
1855
1856 SELECT NVL( SUM( transaction_quantity ), 0 )
1857 FROM WIP_TRANSACTIONS
1858 WHERE wip_entity_id = c_wip_entity_id
1859 AND operation_seq_num = c_operation_seq_num
1860 AND resource_seq_num = c_resource_seq_num
1861 AND employee_id = c_employee_id;
1862
1863
1864 CURSOR get_pending_resource_txns( c_wip_entity_id NUMBER,
1865 c_operation_seq_num NUMBER,
1866 c_resource_seq_num NUMBER,
1867 c_employee_id NUMBER )
1868 IS
1869 SELECT NVL( SUM( transaction_quantity ), 0 )
1870 FROM WIP_COST_TXN_INTERFACE
1871 WHERE wip_entity_id = c_wip_entity_id
1872 AND operation_seq_num = c_operation_seq_num
1873 AND resource_seq_num = c_resource_seq_num
1874 AND employee_id = c_employee_id
1875 AND process_status = 1;
1876
1877
1878 BEGIN
1879 OPEN get_resource_txns( p_wip_entity_id,
1880 p_operation_seq_num,
1881 p_resource_seq_num,
1882 p_employee_id );
1883 FETCH get_resource_txns INTO l_completed_hrs;
1884 CLOSE get_resource_txns;
1885
1886 OPEN get_pending_resource_txns( p_wip_entity_id,
1887 p_operation_seq_num,
1888 p_resource_seq_num,
1889 p_employee_id
1890 );
1891 FETCH get_pending_resource_txns INTO l_pending_hrs;
1892 CLOSE get_pending_resource_txns;
1893
1894 l_return_value:=l_completed_hrs+l_pending_hrs;
1895
1896 RETURN l_return_value ;
1897 END ;
1898
1899 /*##################################################################################################*/
1900 --# NAME
1901 --# PROCEDURE: Get_Resource_Txn_Defaults
1902 --# PARAMETERS
1903 --# Standard IN Parameters
1904 --# p_api_version IN NUMBER := 1.0
1905 --# p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1906 --# p_module_type IN VARCHAR2 := NULL
1907 --#
1908 --# Standard OUT Parameters
1909 --# x_return_status OUT NOCOPY VARCHAR2
1910 --# x_msg_count OUT NOCOPY NUMBER
1911 --# x_msg_data OUT NOCOPY VARCHAR2
1912 --#
1913 --# Get_Resource_Txn_Defaults Parameters
1914 --# p_employee_id IN NUMBER
1915 --# p_workorder_id IN NUMBER
1916 --# p_operation_seq_num IN NUMBER
1917 --# p_function_name IN VARCHAR2 - The function name identifying the type of user
1918 --# x_resource_txn_tbl OUT NOCOPY PRD_RESOURCE_TXNS_TBL
1919 --#
1920 --# DESCRIPTION
1921 --# This procedure is used to retrieve the default resource transactions based on the user/function name
1922 --#
1923 --# HISTORY
1924 --# 16-Jun-2005 rroy Created
1925 --###################################################################################################*/
1926
1927 PROCEDURE Get_Resource_Txn_Defaults
1928 (
1929 p_api_version IN NUMBER := 1.0,
1930 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1931 p_module_type IN VARCHAR2 := NULL,
1932 x_return_status OUT NOCOPY VARCHAR2,
1933 x_msg_count OUT NOCOPY NUMBER,
1934 x_msg_data OUT NOCOPY VARCHAR2,
1935 p_employee_id IN NUMBER,
1936 p_workorder_id IN NUMBER,
1937 p_operation_seq_num IN NUMBER,
1938 p_function_name IN VARCHAR2,
1942 l_user_role VARCHAR2(4);
1939 x_resource_txn_tbl OUT NOCOPY PRD_RESOURCE_TXNS_TBL
1940 )
1941 IS
1943 l_employee_id NUMBER;
1944 i NUMBER;
1945 l_employee_name VARCHAR2(240);
1946 l_employee_num VARCHAR2(30);
1947
1948
1949 CURSOR c_get_resource_assgmt_tech(x_workorder_id NUMBER,
1950 x_operation_seq_num NUMBER,
1951 x_employee_id NUMBER)
1952 IS
1953 SELECT APRV.RESOURCE_SEQUENCE,
1954 APRV.RESOURCE_ID,
1955 APRV.RESOURCE_CODE,
1956 APRV.RESOURCE_TYPE_NAME,
1957 APRV.RESOURCE_TYPE_CODE,
1958 APRV.UOM_NAME,
1959 APRV.UOM_CODE,
1960 APRV.DEPARTMENT_ID,
1961 BD.department_code,
1962 APRV.REQUIREMENT_ID
1963 FROM AHL_WORK_ASSIGNMENTS AWAS,
1964 AHL_PP_REQUIREMENT_V APRV,bom_departments BD
1965 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
1966 AND APRV.DEPARTMENT_ID = BD.department_id
1967 AND APRV.JOB_ID = x_workorder_id
1968 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
1969 AND AWAS.EMPLOYEE_ID = x_employee_id;
1970
1971 CURSOR c_get_other_res_assgmt_tech(x_workorder_id NUMBER,
1972 x_operation_seq_num NUMBER)
1973 IS
1974 SELECT APRV.RESOURCE_SEQUENCE,
1975 APRV.RESOURCE_ID,
1976 APRV.RESOURCE_CODE,
1977 APRV.RESOURCE_TYPE_NAME,
1978 APRV.RESOURCE_TYPE_CODE,
1979 APRV.UOM_NAME,
1980 APRV.UOM_CODE,
1981 AWAS.SERIAL_NUMBER,
1982 AWAS.INSTANCE_ID,
1983 APRV.DEPARTMENT_ID,
1984 BD.department_code,
1985 APRV.REQUIREMENT_ID
1986 FROM AHL_WORK_ASSIGNMENTS AWAS,
1987 AHL_PP_REQUIREMENT_V APRV,bom_departments BD
1988 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
1989 AND APRV.DEPARTMENT_ID = BD.department_id
1990 AND APRV.JOB_ID = x_workorder_id
1991 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
1992 AND RESOURCE_TYPE_CODE <> 2;
1993
1994 CURSOR c_get_other_resource_req_tech(x_workorder_id NUMBER,
1995 x_operation_seq_num NUMBER)
1996 IS
1997 SELECT APRV.RESOURCE_SEQUENCE,
1998 APRV.RESOURCE_ID,
1999 APRV.RESOURCE_CODE,
2000 APRV.RESOURCE_TYPE_NAME,
2001 APRV.RESOURCE_TYPE_CODE,
2002 APRV.UOM_NAME,
2003 APRV.UOM_CODE,
2004 APRV.DEPARTMENT_ID,
2005 BD.department_code,
2006 APRV.REQUIREMENT_ID
2007 FROM AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
2008 WHERE APRV.JOB_ID = x_workorder_id
2009 AND APRV.DEPARTMENT_ID = BD.department_id
2010 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
2011 AND APRV.RESOURCE_TYPE_CODE <> 2
2012 AND NOT EXISTS (SELECT ASSIGNMENT_ID
2013 FROM AHL_WORK_ASSIGNMENTS AWAS
2014 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID);
2015
2016 CURSOR c_get_resource_assgmt_data(x_workorder_id NUMBER,
2017 x_operation_seq_num NUMBER)
2018 IS
2019 SELECT APRV.RESOURCE_SEQUENCE,
2020 APRV.RESOURCE_ID,
2021 APRV.RESOURCE_CODE,
2022 APRV.RESOURCE_TYPE_NAME,
2023 APRV.RESOURCE_TYPE_CODE,
2024 APRV.UOM_NAME,
2025 APRV.UOM_CODE,
2026 AWAS.EMPLOYEE_ID,
2027 AWAS.SERIAL_NUMBER,
2028 AWAS.INSTANCE_ID,
2029 APRV.DEPARTMENT_ID,
2030 BD.department_code,
2031 APRV.REQUIREMENT_ID
2032 FROM AHL_WORK_ASSIGNMENTS AWAS,
2033 AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
2034 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
2035 AND APRV.DEPARTMENT_ID = BD.department_id
2036 AND APRV.JOB_ID = x_workorder_id
2037 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num;
2038
2039 CURSOR c_get_resource_req_data(x_workorder_id NUMBER,
2040 x_operation_seq_num NUMBER)
2041 IS
2042 SELECT APRV.RESOURCE_SEQUENCE,
2043 APRV.RESOURCE_ID,
2044 APRV.RESOURCE_CODE,
2045 APRV.RESOURCE_TYPE_NAME,
2046 APRV.RESOURCE_TYPE_CODE,
2047 APRV.UOM_NAME,
2048 APRV.UOM_CODE,
2049 APRV.DEPARTMENT_ID,
2050 BD.department_code,
2051 APRV.REQUIREMENT_ID
2052 FROM AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
2053 WHERE APRV.JOB_ID = x_workorder_id
2054 AND APRV.DEPARTMENT_ID = BD.department_id
2055 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
2056 AND NOT EXISTS (SELECT ASSIGNMENT_ID
2057 FROM AHL_WORK_ASSIGNMENTS AWAS
2058 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID);
2059
2060 CURSOR c_get_emp_details(x_employee_id NUMBER)
2061 IS
2062 SELECT FULL_NAME,
2063 EMPLOYEE_NUMBER
2064 FROM PER_PEOPLE_F
2065 WHERE PERSON_ID = x_employee_id;
2066
2067 l_manual_enabled_profile_value VARCHAR2(1);
2068
2069 BEGIN
2070
2071 -- if the employee id is NULL then get the employee id
2072 -- id of the user who is currently logged in
2073 IF p_employee_id IS NULL THEN
2074 L_employee_id := AHL_PRD_WO_LOGIN_PVT.get_employee_id;
2075 ELSE
2076 L_employee_id := p_employee_id;
2077 END IF;
2078
2079 i:= 0;
2080
2081 l_manual_enabled_profile_value := NVL(fnd_profile.value('AHL_PRD_MANUAL_RES_TXN'), 'N');
2082
2083 IF p_function_name = 'AHL_PRD_TECH_MYWO' OR p_function_name = 'AHL_PRD_TRANSIT_TECH' THEN
2084 IF (l_manual_enabled_profile_value = 'Y') THEN
2085 OPEN c_get_emp_details(l_employee_id);
2086 FETCH c_get_emp_details INTO l_employee_name, l_employee_num;
2087 CLOSE c_get_emp_details;
2088
2092 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2089 FOR res_txn_rec IN c_get_resource_assgmt_tech(p_workorder_id, p_operation_seq_num, l_employee_id)
2090 LOOP
2091 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2093 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2094 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2095 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2096 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2097 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2098 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2099 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2100 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2101 X_resource_txn_tbl(i).person_id := l_employee_id;
2102 X_resource_txn_tbl(i).employee_name := l_employee_name;
2103 X_resource_txn_tbl(i).employee_num := l_employee_num;
2104 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2105 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2106 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2107 i := i + 1;
2108 END LOOP;
2109 END IF; -- IF (l_manual_enabled_profile_value = 'Y') THEN
2110
2111 FOR res_txn_rec IN c_get_other_res_assgmt_tech(p_workorder_id, p_operation_seq_num) LOOP
2112 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2113 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2114 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2115 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2116 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2117 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2118 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2119 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2120 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2121 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2122 X_resource_txn_tbl(i).serial_number := res_txn_rec.serial_number;
2123 X_resource_txn_tbl(i).Instance_id := res_txn_rec.Instance_id;
2124 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2125 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2126 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2127 i := i + 1;
2128 END LOOP;
2129 FOR res_txn_rec IN c_get_other_resource_req_tech(p_workorder_id, p_operation_seq_num) LOOP
2130 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2131 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2132 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2133 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2134 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2135 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2136 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2137 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2138 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2139 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2140 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2141 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2142 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2143 i := i + 1;
2144 END LOOP;
2145 ELSE
2146 FOR res_txn_rec IN c_get_resource_assgmt_data(p_workorder_id, p_operation_seq_num) LOOP
2147 IF res_txn_rec.employee_id IS NOT NULL THEN
2148 OPEN c_get_emp_details(res_txn_rec.employee_id);
2149 FETCH c_get_emp_details INTO l_employee_name, l_employee_num;
2150 CLOSE c_get_emp_details;
2151 END IF;
2152 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2153 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2154 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2155 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2156 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2157 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2158 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2159 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2160 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2161 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2162 X_resource_txn_tbl(i).person_id := res_txn_rec.employee_id;
2163 X_resource_txn_tbl(i).employee_name := l_employee_name;
2164 X_resource_txn_tbl(i).employee_num := l_employee_num;
2165 X_resource_txn_tbl(i).serial_number := res_txn_rec.serial_number;
2166 X_resource_txn_tbl(i).Instance_id := res_txn_rec.Instance_id;
2167 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2168 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2169 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2170 i := i + 1;
2171 END LOOP;
2172 FOR res_txn_rec IN c_get_resource_req_data(p_workorder_id, p_operation_seq_num) LOOP
2173 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2174 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2175 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2176 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2177 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2178 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2179 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2180 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2181 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2182 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2183 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2184 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2185 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2186 i := i + 1;
2187 END LOOP;
2188 END IF;--IF p_user_role = 'TECH' OR p_user_role = 'LINE' THEN
2189
2190
2191 END Get_Resource_Txn_Defaults;
2192
2193 END AHL_PRD_RESOURCE_TRANX_PVT;