[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.19.12020000.2 2012/12/07 15:30:37 sareepar 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);
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);
368 FND_MSG_PUB.ADD;
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'));
465 END IF;
466
467 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_DT_INVALID');
468 FND_MESSAGE.SET_TOKEN('OPER_RES',P_prd_resrc_txn_tbl(I).OPERATION_SEQUENCE_NUM||'-'
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
584 from ahl_workorder_operations_v
585 where operation_sequence_num=c_oper_seq
586 and workorder_id=c_work_id;
587 */
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);
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);
729 AHL_DEBUG_PUB.debug( ' Resource id -->'||p_x_prd_resrc_txn_tbl(I).resource_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;
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,
819 -- p_x_prd_resrc_txn_tbl(I).WORKORDER_OPERATION_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
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;
928 END IF;
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 -- Fix for Bug # 8892646 - start
1012 Cursor get_oper_res_det(p_workorder_id IN NUMBER,
1013 p_operation_seq_num IN NUMBER)
1014 Is
1015 Select WOP.FIRST_UNIT_START_DATE, WOP.LAST_UNIT_COMPLETION_DATE,
1016 (select nvl(max(resource_seq_num),0) + 10 from wip_operation_resources
1017 where wip_entity_id = WOP.wip_entity_id and operation_seq_num = WOP.operation_seq_num),
1018 WOP.Department_id
1019 From WIP_OPERATIONS WOP, AHL_WORKORDERS AW
1020 Where AW.wip_entity_id = WOP.wip_entity_id
1021 and AW.workorder_id = p_workorder_id
1022 and WOP.operation_seq_num = p_operation_seq_num;
1023
1024 l_max_res_seq_no NUMBER;
1025 j NUMBER;
1026
1027 TYPE RES_SEQ_CT_REC_TYPE IS RECORD (
1028 woid AHL_WORKORDERS.workorder_id%TYPE,
1029 opseqno WIP_OPERATIONS.operation_seq_num%TYPE,
1030 res_seq_ct NUMBER);
1031
1032 TYPE RES_SEQ_TABLE_TYPE IS TABLE OF RES_SEQ_CT_REC_TYPE
1033 INDEX BY BINARY_INTEGER;
1034
1035 res_seq_ct_table RES_SEQ_TABLE_TYPE;
1036 found BOOLEAN;
1037 -- Fix for Bug # 8892646 - end
1038
1039 BEGIN
1040 SAVEPOINT PROCESS_RESOURCE_TRANX;
1041
1042 -- Enable Debug
1043
1044 IF G_DEBUG='Y' THEN
1045 -- AHL_DEBUG_PUB.enable_debug;
1046 AHL_DEBUG_PUB.debug('At start of procedure PROCESS_RESOURCE_TRANX');
1047 END IF;
1048
1049
1050
1051 -- Standard call to check for call compatibility.
1052
1053 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1054 p_api_version,
1055 l_api_name,G_PKG_NAME) THEN
1056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1057 END IF;
1058
1059 -- Initialize message list if p_init_msg_list is set to TRUE.
1060
1061 IF FND_API.to_boolean(p_init_msg_list) THEN
1062 FND_MSG_PUB.initialize;
1063 END IF;
1064
1065 -- Initialize API return status to success
1066
1067 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1068
1069
1070 -- Debug info.
1071
1072 IF G_DEBUG='Y' THEN
1073 AHL_DEBUG_PUB.debug( 'Enter PROCESS_RESOURCE_TRNX',L_API_NAME);
1074 END IF;
1075 IF p_module_type IN ('JSP','OAF') THEN
1076 IF p_x_prd_resrc_txn_tbl.COUNT>0 THEN
1077 FOR i in p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST LOOP
1078 p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID := NULL;
1079 END LOOP;
1080 END IF;
1081 END IF;
1082 -- Convert meanings to ID values.
1083 TRANSLATE_MEANING_TO_ID
1084 (
1085 p_x_prd_resrc_txn_tbl => p_x_prd_resrc_txn_tbl,
1086 x_return_status =>x_return_status
1087 );
1088
1089 -- check error message.
1090 l_msg_count := FND_MSG_PUB.count_msg;
1091 IF l_msg_count > 0 THEN
1092 X_msg_count := l_msg_count;
1093 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 IF G_DEBUG='Y' THEN
1095 AHL_DEBUG_PUB.debug( 'Error Thrown in translate',L_API_NAME);
1096 END IF;
1097 RAISE FND_API.G_EXC_ERROR;
1098 END IF;
1099
1100
1101 -- Start of API Body
1102
1103 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1104
1105 -- Validate input.
1106 VALIDATE_RES_TRNX
1107 (
1108 p_prd_resrc_txn_tbl => p_x_prd_resrc_txn_tbl,
1109 x_return_status =>x_Return_status
1110 );
1111
1112
1113 -- check errors.
1114 l_msg_count := FND_MSG_PUB.count_msg;
1115 IF l_msg_count > 0 THEN
1116 X_msg_count := l_msg_count;
1117 IF G_DEBUG='Y' THEN
1118 AHL_DEBUG_PUB.debug( 'Error Thrown In Validation',L_API_NAME);
1119 END IF;
1120 RAISE FND_API.G_EXC_ERROR;
1121 END IF;
1122
1123 l_ctr := 0;
1124
1125 -- Create resource requirements if it does not exist.
1126 FOR i IN p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST
1127 LOOP
1128 IF (p_x_prd_resrc_txn_tbl(i).resource_sequence_num is null OR
1129 p_x_prd_resrc_txn_tbl(i).resource_sequence_num = fnd_api.g_miss_num) THEN
1130
1131 l_Resrc_Require_Tbl(l_ctr).workorder_id := p_x_prd_resrc_txn_tbl(i).workorder_id;
1132 l_Resrc_Require_Tbl(l_ctr).operation_seq_number := p_x_prd_resrc_txn_tbl(i).operation_sequence_num;
1133 -- get operation start and end dates.
1134 OPEN get_oper_res_det(p_x_prd_resrc_txn_tbl(i).workorder_id,
1135 p_x_prd_resrc_txn_tbl(i).operation_sequence_num);
1136 FETCH get_oper_res_det INTO l_oper_start_dt, l_oper_end_dt,
1137 p_x_prd_resrc_txn_tbl(i).resource_sequence_num,
1138 -- added to fix bug# 6326254.
1139 p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID;
1140
1141
1142 IF (get_oper_res_det%NOTFOUND) THEN
1143 FND_MESSAGE.set_name('AHL','AHL_PRD_OPERATION_SEQ_INV');
1144 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_resrc_txn_tbl(I).operation_Sequence_num);
1145 FND_MSG_PUB.ADD;
1146 END IF;
1147 CLOSE get_oper_res_det;
1148
1149 IF (G_DEBUG = 'Y') THEN
1150 AHL_DEBUG_PUB.debug('Resource seq number: from DB' || p_x_prd_resrc_txn_tbl(i).resource_sequence_num);
1151 END IF;
1152 -- Fix for Bug # 8892646 - start
1153 l_max_res_seq_no := p_x_prd_resrc_txn_tbl(i).resource_sequence_num;
1154 found := FALSE;
1155 FOR j IN 1..res_seq_ct_table.COUNT
1156 LOOP
1157 IF (res_seq_ct_table(j).woid = p_x_prd_resrc_txn_tbl(i).workorder_id AND
1158 res_seq_ct_table(j).opseqno = p_x_prd_resrc_txn_tbl(i).operation_sequence_num )
1159 THEN
1160 found := TRUE;
1161 l_max_res_seq_no := l_max_res_seq_no + res_seq_ct_table(j).res_seq_ct;
1162 res_seq_ct_table(j).res_seq_ct := res_seq_ct_table(j).res_seq_ct + 10;
1163 END IF;
1164 END LOOP;
1165
1166 IF found = FALSE THEN
1167 j := res_seq_ct_table.COUNT +1;
1168 res_seq_ct_table(j).woid := p_x_prd_resrc_txn_tbl(i).workorder_id ;
1169 res_seq_ct_table(j).opseqno := p_x_prd_resrc_txn_tbl(i).operation_sequence_num;
1170 res_seq_ct_table(j).res_seq_ct := 10;
1171 END IF;
1172 p_x_prd_resrc_txn_tbl(i).resource_sequence_num := l_max_res_seq_no;
1173
1174 IF (G_DEBUG = 'Y') THEN
1175 AHL_DEBUG_PUB.debug('Resource seq number: generated' || p_x_prd_resrc_txn_tbl(i).resource_sequence_num);
1176 END IF;
1177
1178 -- Fix for Bug # 8892646 - end
1179 l_Resrc_Require_Tbl(l_ctr).resource_seq_number :=
1180 p_x_prd_resrc_txn_tbl(i).resource_sequence_num;
1181 l_Resrc_Require_Tbl(l_ctr).oper_start_date := l_oper_start_dt;
1182 l_Resrc_Require_Tbl(l_ctr).oper_end_date := l_oper_end_dt;
1183 l_Resrc_Require_Tbl(l_ctr).req_start_date := l_oper_start_dt;
1184 l_Resrc_Require_Tbl(l_ctr).req_end_date := l_oper_end_dt;
1185 l_Resrc_Require_Tbl(l_ctr).resource_id := p_x_prd_resrc_txn_tbl(i).resource_id;
1186 l_Resrc_Require_Tbl(l_ctr).resource_Name := p_x_prd_resrc_txn_tbl(i).resource_Name;
1187
1188 l_Resrc_Require_Tbl(l_ctr).uom_code :=substr(p_x_prd_resrc_txn_tbl(i).UOM_CODE,1,3);
1189 -- commented this out as this attribute is now charge department.
1190 --l_Resrc_Require_Tbl(l_ctr).department_id :=p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID;
1191 l_Resrc_Require_Tbl(l_ctr).quantity := 1;
1192
1193 IF p_x_prd_resrc_txn_tbl(i).qty IS NOT NULL THEN
1194 l_Resrc_Require_Tbl(l_ctr).duration := p_x_prd_resrc_txn_tbl(i).qty;
1195 ELSE
1196 -- UOM is hours, so multiplying by 24
1197 l_Resrc_Require_Tbl(l_ctr).duration := ROUND((p_x_prd_resrc_txn_tbl(i).end_date -
1198 p_x_prd_resrc_txn_tbl(i).transaction_date) * 24, 2);
1199 END IF;
1200 l_ctr := l_ctr + 1;
1201 END IF; -- p_x_prd_resrc_txn_tbl(i).resource_sequence_num is null
1202 END LOOP;
1203
1204 IF (l_Resrc_Require_tbl.count > 0) THEN
1205 AHL_PP_RESRC_REQUIRE_PVT.Process_Resrc_Require (
1206 p_api_version => 1.0,
1207 p_init_msg_list => Fnd_Api.G_FALSE,
1208 p_commit => Fnd_Api.G_FALSE,
1209 p_module_type => 'JSP', -- need to pass JSP otherwise EAM api is not called.
1210 p_operation_flag => 'C',
1211 p_interface_flag => NULL,
1212 p_x_Resrc_Require_tbl => l_Resrc_Require_tbl,
1213 x_return_status => l_return_status,
1214 x_msg_count => l_msg_count,
1215 x_msg_data => l_msg_data);
1216
1217 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1218 IF (fnd_log.level_error >= G_DEBUG)THEN
1219 fnd_log.string
1220 (
1221 fnd_log.level_error,
1222 'ahl.plsql.AHL_PRD_RESOURCE_TRANX_PVT.Process_Resource_Txns',
1223 'AHL_PP_RESRC_REQUIRE_PVT.Process_Resrc_Require API returned error'
1224 );
1225 END IF;
1226 RAISE FND_API.G_EXC_ERROR;
1227 END IF;
1228
1229 END IF;
1230
1231 IF G_DEBUG='Y' THEN
1232 AHL_DEBUG_PUB.debug( 'Before Process transactions',L_API_NAME);
1233 AHL_DEBUG_PUB.debug( 'Number of Records'||p_x_prd_resrc_txn_tbl.count,L_API_NAME);
1234 END IF;
1235
1236 l_ctr := 0;
1237 FOR i IN p_x_prd_resrc_txn_tbl.FIRST..p_x_prd_resrc_txn_tbl.LAST
1238 LOOP
1239
1240 IF G_DEBUG='Y' THEN
1241 AHL_DEBUG_PUB.debug( 'Enter loop ',L_API_NAME);
1242 END IF;
1243
1244 IF p_x_prd_resrc_txn_tbl(i).DML_operation = 'C'
1245 THEN
1246 Open GetWipid (p_x_prd_resrc_txn_tbl(i).workorder_id);
1247 Fetch GetWipid into l_wip_entity_id;
1248 Close GetWipid;
1249
1250 IF G_DEBUG='Y' THEN
1251 AHL_DEBUG_PUB.debug( 'Workorder id'||p_x_prd_resrc_txn_tbl(i).workorder_id,L_API_NAME);
1252 END IF;
1253
1254 l_ahl_res_txn_tbl(l_ctr).wip_entity_id := l_wip_entity_id;
1255 l_ahl_res_txn_tbl(l_ctr).operation_seq_num :=p_x_prd_resrc_txn_tbl(i).Operation_sequence_num;
1256 l_ahl_res_txn_tbl(l_ctr).resource_seq_num :=p_x_prd_resrc_txn_tbl(i).Resource_sequence_num;
1257 l_ahl_res_txn_tbl(l_ctr).resource_id :=p_x_prd_resrc_txn_tbl(i).RESOURCE_ID;
1258 l_ahl_res_txn_tbl(l_ctr).transaction_type :=1;
1259 -- rroy
1260 -- R12 Tech UIs
1261 l_ahl_res_txn_tbl(l_ctr).transaction_date := nvl(p_x_prd_resrc_txn_tbl(i).transaction_date, sysdate);
1262 IF p_x_prd_resrc_txn_tbl(i).qty IS NOT NULL THEN
1263 l_ahl_res_txn_tbl(l_ctr).transaction_quantity := p_x_prd_resrc_txn_tbl(i).qty;
1264 ELSE
1265 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);
1266 -- UOM is hours, so multiplying by 24
1267 END IF;
1268 --l_ahl_res_txn_tbl(l_ctr).transaction_quantity :=p_x_prd_resrc_txn_tbl(i).QTY ;
1269
1270 l_ahl_res_txn_tbl(l_ctr).transaction_uom :=substr(p_x_prd_resrc_txn_tbl(i).UOM_CODE,1,3);
1271 l_ahl_res_txn_tbl(l_ctr).department_id :=p_x_prd_resrc_txn_tbl(i).DEPARTMENT_ID;
1272 l_ahl_res_txn_tbl(l_ctr).employee_id :=p_x_prd_resrc_txn_tbl(i).PERSON_ID;
1273 l_ahl_res_txn_tbl(l_ctr).activity_id :=p_x_prd_resrc_txn_tbl(i).ACTIVITY_ID;
1274 l_ahl_res_txn_tbl(l_ctr).activity_meaning :=p_x_prd_resrc_txn_tbl(i).ACTIVITY_MEANING;
1275 l_ahl_res_txn_tbl(l_ctr).reason_id :=p_x_prd_resrc_txn_tbl(i).REASON_ID ;
1276 l_ahl_res_txn_tbl(l_ctr).reason :=p_x_prd_resrc_txn_tbl(i).REASON ;
1277
1278 -- bug 3955565
1279 -- the reference field will now contain short message, serial number and reference
1280 -- short message will be of type '; Machine Serial #: ' so that the appended serial number
1281 -- in the reference field makes sense when seen from the WIP UIs.
1282 IF p_x_prd_resrc_txn_tbl(i).serial_number IS NULL THEN
1283 -- if the serial number is null, then simply append a ';' at the end of the ref text
1284 -- to add as a demarking for us
1285 IF p_x_prd_resrc_txn_tbl(i).REFERENCE IS NOT NULL THEN
1286 l_ahl_res_txn_tbl(l_ctr).reference := p_x_prd_resrc_txn_tbl(i).REFERENCE || ';';
1287 END IF;
1288 ELSE
1289 -- if serial number is not null and ref text is null then
1290 -- simply add the serial number with short mesg prefix and without a semi colon
1291 -- 240 is the length of the reference column in the wip_transactions table
1292 IF p_x_prd_resrc_txn_tbl(i).REFERENCE IS NOT NULL THEN
1293 l_str_len := 240 - (length(p_x_prd_resrc_txn_tbl(i).serial_number) + length(l_short_mesg) + 2);
1294 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;
1295 ELSE
1296 -- ref text is null
1297 -- then simply add the serial number without a ';'
1298 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;
1299 END IF;
1300 END IF;
1301
1302
1303 l_ahl_res_txn_tbl(l_ctr).serial_number := p_x_prd_resrc_txn_tbl(i).serial_number ;
1304
1305 l_ctr:=l_ctr+1;
1306 End if;
1307
1308 END LOOP;
1309
1310 IF G_DEBUG='Y' THEN
1311 AHL_DEBUG_PUB.debug( 'Before Insert Process transactions',L_API_NAME);
1312
1313 END IF;
1314
1315 IF l_ahl_res_txn_tbl.count >0
1316 Then
1317
1318
1319 AHL_WIP_JOB_PVT.insert_resource_txn
1320 (
1321 p_api_version =>p_api_version,
1322 p_init_msg_list =>L_init_msg_list,
1323 p_commit =>l_commit,
1324 p_validation_level =>p_validation_level,
1325 x_return_status =>x_return_status,
1326 x_msg_count =>l_msg_count,
1327 x_msg_data =>l_msg_data,
1328 p_ahl_res_txn_tbl =>l_ahl_res_txn_tbl
1329 );
1330 End if;
1331
1332 l_msg_count := FND_MSG_PUB.count_msg;
1333 IF l_msg_count > 0 THEN
1334 X_msg_count := l_msg_count;
1335 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336 RAISE FND_API.G_EXC_ERROR;
1337 IF G_DEBUG='Y' THEN
1338 AHL_DEBUG_PUB.debug( 'Validation Errors in interface API');
1339 END IF;
1340
1341 END IF;
1342
1343 --Adithya added
1344 X_msg_count := l_msg_count;
1345
1346 IF FND_API.TO_BOOLEAN(p_commit) THEN
1347 COMMIT;
1348 END IF;
1349
1350 -- Debug info
1351
1352 IF G_DEBUG='Y' THEN
1353 AHL_DEBUG_PUB.debug( 'End of Private api '||l_api_name,'+debug+');
1354
1355 END IF;
1356
1357 IF G_DEBUG='Y' THEN
1358 AHL_DEBUG_PUB.disable_debug;
1359
1360 END IF;
1361
1362 EXCEPTION
1363 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1364 ROLLBACK TO PROCESS_RESOURCE_TRANX;
1365 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1366 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1367 p_count => x_msg_count,
1368 p_data => x_msg_data);
1369
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 ROLLBACK TO PROCESS_RESOURCE_TRANX;
1372 X_return_status := FND_API.G_RET_STS_ERROR;
1373 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1374 p_count => x_msg_count,
1375 p_data => X_msg_data);
1376 WHEN OTHERS THEN
1377 ROLLBACK TO PROCESS_RESOURCE_TRANX;
1378 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1380 THEN
1381 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1382 p_procedure_name =>l_api_name,
1383 p_error_text =>SUBSTR(SQLERRM,1,240)
1384 );
1385 END IF;
1386 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1387 p_count => x_msg_count,
1388 p_data => X_msg_data);
1389
1390 END;
1391
1392 -- ADDED BY VSUNDARA FOR TRANSIT CHECK ENHANCEMENTS
1393
1394 PROCEDURE VALIDATE_MYWORKORDER_TRNX
1395 (
1396 p_prd_myworkorder_txn_tbl IN PRD_MYWORKORDER_TXNS_TBL,
1397 x_return_status OUT NOCOPY VARCHAR2
1398 )
1399 AS
1400 l_return_status VARCHAR2(1);
1401 l_wo_name VARCHAR2(80);
1402 L_wo_status VARCHAR2(30);
1403
1404 CURSOR get_wo_details(c_workorder_id NUMBER)
1405 IS
1406 SELECT workorder_name
1407 FROM AHL_WORKORDERS
1408 WHERE WORKORDER_ID = c_workorder_id;
1409
1410 BEGIN
1411
1412 IF p_prd_myworkorder_txn_tbl.COUNT >0
1413 THEN
1414 --- Basic Validation..
1415 FOR i IN p_prd_myworkorder_txn_tbl.FIRST..p_prd_myworkorder_txn_tbl.LAST
1416 LOOP
1417 -- rroy
1418 -- R12 Tech UIs
1419 -- Negative resource transactions are allowed starting R12
1420 /*IF p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS IS NOT NULL OR
1421 p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS <> fnd_api.g_miss_num
1422 THEN
1423 IF p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS < 0
1424 THEN
1425 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_QTY_INVALID');
1426 FND_MESSAGE.SET_TOKEN('OPER_RES', p_prd_myworkorder_txn_tbl(i).OPERATION_SEQUENCE||'-'|| p_prd_myworkorder_txn_tbl(i).RESOURCE_SEQUENCE);
1427 FND_MSG_PUB.ADD;
1428
1429 IF G_DEBUG='Y' THEN
1430 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1431 END IF;
1432 END IF;
1433 END IF ;
1434 */
1435 -- rroy
1436 -- R12 Tech UIs
1437
1438 IF p_prd_myworkorder_txn_tbl(i).WORKORDER_ID IS NULL OR
1439 p_prd_myworkorder_txn_tbl(i).WORKORDER_ID = fnd_api.g_miss_num
1440 THEN
1441 FND_MESSAGE.set_name('AHL','AHL_PRD_WORKORDER_ID_NULL');
1442 FND_MSG_PUB.ADD;
1443 IF G_DEBUG='Y' THEN
1444 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1445 END IF;
1446 END IF ;
1447
1448 -- rroy
1449 -- ACL Changes
1450 IF p_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS <> 0 THEN
1451 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
1452 p_workorder_id => p_prd_myworkorder_txn_tbl(i).workorder_id,
1453 p_ue_id => NULL,
1454 p_visit_id => NULL,
1455 p_item_instance_id => NULL);
1456 IF l_return_status = FND_API.G_TRUE THEN
1457 OPEN get_wo_details(p_prd_myworkorder_txn_tbl(i).workorder_id);
1458 FETCH get_wo_details INTO l_wo_name;
1459 CLOSE get_wo_details;
1460 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_RESTXN_UNTLCKD');
1461 FND_MESSAGE.Set_Token('WO_NAME', l_wo_name);
1462 FND_MSG_PUB.ADD;
1463 END IF;
1464 END IF;
1465 -- rroy
1466 -- ACL Changes
1467 IF p_prd_myworkorder_txn_tbl(i).OPERATION_SEQUENCE IS NULL OR
1468 p_prd_myworkorder_txn_tbl(i).OPERATION_SEQUENCE = fnd_api.g_miss_num
1469 THEN
1470 FND_MESSAGE.set_name('AHL','AHL_PRD_OPERATION_NULL');
1471 FND_MSG_PUB.ADD;
1472 IF G_DEBUG='Y' THEN
1473 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1474 END IF;
1475 END IF ;
1476
1477 IF p_prd_myworkorder_txn_tbl(i).RESOURCE_SEQUENCE IS NULL OR
1478 p_prd_myworkorder_txn_tbl(i).RESOURCE_SEQUENCE = fnd_api.g_miss_num
1479 THEN
1480 FND_MESSAGE.set_name('AHL','AHL_PRD_RESOURCE_NULL');
1481 FND_MSG_PUB.ADD;
1482 IF G_DEBUG='Y' THEN
1483 AHL_DEBUG_PUB.debug( 'Error in Quantity');
1484 END IF;
1485 END IF ;
1486 END LOOP;
1487 END IF ;
1488
1489 END;
1490
1491
1492 PROCEDURE getOperationRecord
1493 (
1494 p_operation_Id IN NUMBER ,
1495 p_x_operation_rec IN OUT NOCOPY AHL_PRD_OPERATIONS_PVT.PRD_WORKOPERATION_REC,
1496 x_return_status OUT NOCOPY VARCHAR2
1497 )
1498
1499 IS
1500
1501 cursor get_operation_rec(c_operation_id NUMBER)
1502 is
1503 SELECT *
1504 FROM AHL_WORKORDER_OPERATIONS_V
1505 WHERE workorder_operation_id=c_operation_id;
1506
1507 l_operation_rec get_operation_rec%ROWTYPE;
1508
1509 BEGIN
1510
1511 OPEN get_operation_rec(p_operation_Id);
1512 FETCH get_operation_rec INTO l_operation_rec;
1513 CLOSE get_operation_rec;
1514
1515 IF l_operation_rec.scheduled_start_date > SYSDATE THEN
1516 l_operation_rec.actual_start_date := SYSDATE;
1517 ELSE
1518 l_operation_rec.actual_start_date := l_operation_rec.Scheduled_start_date;
1519 END IF;
1520
1521 IF l_operation_rec.scheduled_end_date > SYSDATE THEN
1522 l_operation_rec.actual_end_date := SYSDATE;
1523 ELSE
1524 l_operation_rec.actual_end_date := l_operation_rec.scheduled_end_date;
1525 END IF;
1526
1527 p_x_operation_rec.WORKORDER_OPERATION_ID := l_operation_rec.WORKORDER_OPERATION_ID;
1528 p_x_operation_rec.ACTUAL_START_DATE := l_operation_rec.actual_start_date ;
1529 p_x_operation_rec.ACTUAL_END_DATE := l_operation_rec.actual_end_date;
1530 p_x_operation_rec.OBJECT_VERSION_NUMBER := l_operation_rec.OBJECT_VERSION_NUMBER;
1531 p_x_operation_rec.DML_OPERATION := 'U';
1532 END ;
1533
1534
1535
1536
1537
1538
1539 --- Changes by VSUNDARA For Transit Check
1540
1541 PROCEDURE PROCESS_MYWORKORDER_TXNS
1542 (
1543 p_api_version IN NUMBER := 1.0,
1544 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1545 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1546 p_validation_level IN NUMBER:=FND_API.G_VALID_LEVEL_FULL,
1547 p_default IN VARCHAR2 := FND_API.G_FALSE,
1548 p_module_type IN VARCHAR2 := NULL,
1549 x_return_status OUT NOCOPY VARCHAR2,
1550 x_msg_count OUT NOCOPY NUMBER,
1551 x_msg_data OUT NOCOPY VARCHAR2,
1552 p_x_prd_myworkorder_txn_tbl IN OUT NOCOPY PRD_MYWORKORDER_TXNS_TBL
1553 )
1554
1555 AS
1556
1557 l_api_name CONSTANT VARCHAR2(30):= 'PROCESS_MYWORKORDER_TXNS';
1558 l_api_version CONSTANT NUMBER:= 1.0;
1559 l_num_rec NUMBER;
1560 l_msg_count NUMBER;
1561 l_msg_data VARCHAR2(2000);
1562 l_return_status VARCHAR2(1);
1563 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
1564 l_commit VARCHAR2(1):= FND_API.G_FALSE;
1565 l_ahl_res_txn_tbl AHL_WIP_JOB_PVT.ahl_res_txn_tbl_type;
1566 l_wip_entity_id NUMBER;
1567 l_txn_group_id NUMBER;
1568 l_ctr NUMBER:=0;
1569 l_uom_code VARCHAR2(3);
1570 l_wo_status_code VARCHAR2(30);
1571 l_prd_operation_rec AHL_PRD_OPERATIONS_PVT.PRD_WORKOPERATION_REC;
1572 l_prd_operation_tbl AHL_PRD_OPERATIONS_PVT.PRD_OPERATION_TBL;
1573
1574 Cursor GetWipid (C_WORK_ID Number)
1575 Is
1576 Select wip_entity_id, status_code
1577 from ahl_workorders
1578 where workorder_id=c_work_id;
1579
1580
1581 CURSOR getDefaultUOM
1582 IS
1583 SELECT UOM_CODE
1584 FROM MTL_UNITS_OF_MEASURE
1585 WHERE UPPER(UNIT_OF_MEASURE) = UPPER('Hour')
1586 AND UOM_CLASS = 'Time';
1587
1588
1589 CURSOR getResDetails(p_assignment_id NUMBER)
1590 IS
1591 SELECT
1592 AWAS.ASSIGNMENT_ID,
1593 AWOS.WORKORDER_ID JOB_ID,
1594 AWOP.WORKORDER_OPERATION_ID OPERATION_ID,
1595 AWOS.ORGANIZATION_ID,
1596 AWOP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE,
1597 AOPR.RESOURCE_SEQUENCE_NUM RESOURCE_SEQUENCE,
1598 BOMR.DESCRIPTION RESOURCE_NAME,
1599 BOMR.RESOURCE_TYPE RESOURCE_TYPE_CODE ,
1600 MFGL.MEANING RESOURCE_TYPE_NAME ,
1601 AOPR.RESOURCE_ID,
1602 BOMR.RESOURCE_CODE RESOURCE_CODE ,
1603 PEPF.employee_num EMPLOYEE_NUMBER,
1604 PEPF.FULL_NAME ,
1605 AWOS.DEPARTMENT_NAME,
1606 AWOS.DEPARTMENT_ID,
1607 AWOS.ITEM_INSTANCE_ID
1608 FROM AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDER_TASKS_V AWOS, AHL_WORKORDER_OPERATIONS AWOP,
1609 AHL_OPERATION_RESOURCES AOPR,AHL_DEPARTMENT_SHIFTS ADS, mtl_employees_current_view PEPF, BOM_RESOURCES BOMR, MFG_LOOKUPS MFGL
1610 WHERE AWAS.OPERATION_RESOURCE_ID = AOPR.OPERATION_RESOURCE_ID
1611 AND AWOP.WORKORDER_OPERATION_ID = AOPR.WORKORDER_OPERATION_ID
1612 AND AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
1613 AND AWOS.DEPARTMENT_ID = ADS.DEPARTMENT_ID (+)
1614 AND AWAS.EMPLOYEE_ID = PEPF.EMPLOYEE_ID AND SYSTEM_PERSON_TYPE ='EMP' AND AOPR.RESOURCE_ID = BOMR.RESOURCE_ID
1615 AND MFGL.LOOKUP_CODE(+) = BOMR.RESOURCE_TYPE AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
1616 AND AWAS.ASSIGNMENT_ID = p_assignment_id;
1617
1618 l_res_rec getResDetails%rowtype;
1619
1620 BEGIN
1621 SAVEPOINT PROCESS_MYWORKORDER_TXNS;
1622
1623 -- Enable Debug
1624
1625 IF G_DEBUG='Y' THEN
1626 AHL_DEBUG_PUB.enable_debug;
1627 END IF;
1628
1629
1630
1631 -- Standard call to check for call compatibility.
1632
1633 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1634 p_api_version,
1635 l_api_name,G_PKG_NAME) THEN
1636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1637 END IF;
1638
1639 -- Initialize message list if p_init_msg_list is set to TRUE.
1640
1641 IF FND_API.to_boolean(p_init_msg_list) THEN
1642 FND_MSG_PUB.initialize;
1643 END IF;
1644
1645 -- Initialize API return status to success
1646
1647 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1648
1649
1650 -- Debug info.
1651
1652 IF G_DEBUG='Y' THEN
1653 AHL_DEBUG_PUB.debug( 'Enter PROCESS_MYWORKORDER_TXNS',L_API_NAME);
1654 END IF;
1655
1656 VALIDATE_MYWORKORDER_TRNX(
1657 p_prd_myworkorder_txn_tbl => p_x_prd_myworkorder_txn_tbl,
1658 x_return_status =>x_return_status
1659 );
1660
1661
1662 l_msg_count := FND_MSG_PUB.count_msg;
1663 IF l_msg_count > 0 THEN
1664 X_msg_count := l_msg_count;
1665 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1666 IF G_DEBUG='Y' THEN
1667 AHL_DEBUG_PUB.debug( 'Error Thrown in Validate',L_API_NAME);
1668 END IF;
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671 -- Get the Defalut UOM Code --- Hr
1672 Open getDefaultUOM;
1673 fetch getDefaultUOM into l_uom_code;
1674 CLOSE getDefaultUOM;
1675
1676 IF p_x_prd_myworkorder_txn_tbl.COUNT > 0 THEN
1677 FOR i IN p_x_prd_myworkorder_txn_tbl.FIRST..p_x_prd_myworkorder_txn_tbl.LAST
1678 LOOP
1679 IF p_x_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS > 0
1680 THEN
1681
1682 IF G_DEBUG='Y' THEN
1683 AHL_DEBUG_PUB.debug( 'Enter loop ',L_API_NAME);
1684 END IF;
1685
1686 OPEN GetWipid (p_x_prd_myworkorder_txn_tbl(i).workorder_id);
1687 FETCH GetWipid into l_wip_entity_id, l_wo_status_code;
1688 CLOSE GetWipid;
1689 IF l_wo_status_code IN ('1', '12', '7', '6', '13', '5') THEN
1690 FND_MESSAGE.set_name('AHL', 'AHL_PRD_RESOURCE_CANNOTEDIT');
1691 FND_MSG_PUB.ADD;
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694
1695 Open getResDetails(p_x_prd_myworkorder_txn_tbl(i).ASSIGNMENT_ID);
1696 FETCH getResDetails into l_res_rec;
1697 CLOSE getResDetails;
1698 IF G_DEBUG='Y' THEN
1699 AHL_DEBUG_PUB.debug( 'Workorder id'||p_x_prd_myworkorder_txn_tbl(i).workorder_id,L_API_NAME);
1700 END IF;
1701 l_ahl_res_txn_tbl(l_ctr).operation_seq_num :=l_res_rec.OPERATION_SEQUENCE;
1702 l_ahl_res_txn_tbl(l_ctr).resource_seq_num :=l_res_rec.RESOURCE_SEQUENCE;
1703 l_ahl_res_txn_tbl(l_ctr).resource_id :=l_res_rec.RESOURCE_ID;
1704 l_ahl_res_txn_tbl(l_ctr).transaction_type :=1;
1705 l_ahl_res_txn_tbl(l_ctr).transaction_date :=sysdate;
1706 l_ahl_res_txn_tbl(l_ctr).transaction_quantity :=p_x_prd_myworkorder_txn_tbl(i).TRANSACTED_HOURS;
1707 --- Default UOM CODE
1708 l_ahl_res_txn_tbl(l_ctr).transaction_uom := l_uom_code;
1709 l_ahl_res_txn_tbl(l_ctr).department_id :=l_res_rec.DEPARTMENT_ID;
1710 l_ahl_res_txn_tbl(l_ctr).employee_id :=p_x_prd_myworkorder_txn_tbl(i).EMPLOYEE_ID;
1711 l_ahl_res_txn_tbl(l_ctr).wip_entity_id := l_wip_entity_id;
1712
1713 l_ctr:=l_ctr+1;
1714 END IF;
1715 END LOOP;
1716 END IF;
1717
1718 IF G_DEBUG='Y' THEN
1719 AHL_DEBUG_PUB.debug( 'Before Insert Process transactions',L_API_NAME);
1720
1721 END IF;
1722
1723
1724 IF l_ahl_res_txn_tbl.count >0
1725 Then
1726
1727 AHL_WIP_JOB_PVT.insert_resource_txn
1728 (
1729 p_api_version =>p_api_version,
1730 p_init_msg_list =>L_init_msg_list,
1731 p_commit =>l_commit,
1732 p_validation_level =>p_validation_level,
1733 x_return_status =>x_return_status,
1734 x_msg_count =>l_msg_count,
1735 x_msg_data =>l_msg_data,
1736 p_ahl_res_txn_tbl =>l_ahl_res_txn_tbl
1737 );
1738 End if;
1739 l_msg_count := FND_MSG_PUB.count_msg;
1740 IF l_msg_count > 0 THEN
1741 X_msg_count := l_msg_count;
1742 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1743 RAISE FND_API.G_EXC_ERROR;
1744 IF G_DEBUG='Y' THEN
1745 AHL_DEBUG_PUB.debug( 'Error in Insert_Resource_Txn API');
1746 END IF;
1747 END IF;
1748
1749 --- Complete the Operation if the Complete Operation is True
1750 l_ctr := 0;
1751 IF p_x_prd_myworkorder_txn_tbl.COUNT > 0 THEN
1752 FOR i IN p_x_prd_myworkorder_txn_tbl.FIRST..p_x_prd_myworkorder_txn_tbl.LAST
1753 LOOP
1754
1755
1756
1757 IF (p_x_prd_myworkorder_txn_tbl(i).OPERATION_COMPLETE IS NOT NULL AND
1758 p_x_prd_myworkorder_txn_tbl(i).OPERATION_COMPLETE <> FND_API.G_MISS_CHAR AND
1759 p_x_prd_myworkorder_txn_tbl(i).OPERATION_COMPLETE = 'Y')
1760 THEN
1761 getOperationRecord(
1762 p_operation_id => p_x_prd_myworkorder_txn_tbl(i).WORKORDER_OPERATION_ID,
1763 p_x_operation_rec => l_prd_operation_rec,
1764 x_return_status => x_return_status);
1765 l_prd_operation_tbl(l_ctr) := l_prd_operation_rec;
1766 l_ctr := l_ctr +1 ;
1767
1768 END IF ;
1769
1770 END LOOP;
1771 END IF;
1772
1773 IF l_prd_operation_tbl.count >0
1774 Then
1775 AHL_PRD_OPERATIONS_PVT.PROCESS_OPERATIONS
1776 (
1777 p_api_version =>1.0,
1778 p_init_msg_list =>FND_API.G_FALSE,
1779 p_commit =>l_commit,
1780 p_validation_level =>p_validation_level,
1781 p_default => FND_API.G_TRUE,
1782 p_module_type => NULL,
1783 p_wip_mass_load_flag => 'N',
1784 x_return_status =>x_return_status,
1785 x_msg_count =>l_msg_count,
1786 x_msg_data =>l_msg_data,
1787 p_x_prd_operation_tbl =>l_prd_operation_tbl
1788 );
1789 End if;
1790 l_msg_count := FND_MSG_PUB.count_msg;
1791 IF l_msg_count > 0 THEN
1792 X_msg_count := l_msg_count;
1793 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 RAISE FND_API.G_EXC_ERROR;
1795 IF G_DEBUG='Y' THEN
1796 AHL_DEBUG_PUB.debug( 'Error in Process Operations API');
1797 END IF;
1798 END IF;
1799
1800 --- Complete the Operation
1801 IF l_prd_operation_tbl.COUNT > 0 THEN
1802 FOR i IN l_prd_operation_tbl.FIRST..l_prd_operation_tbl.LAST
1803 LOOP
1804 AHL_COMPLETIONS_PVT.complete_operation
1805 (
1806 p_api_version => 1.0,
1807 p_init_msg_list => FND_API.G_TRUE,
1808 p_commit => FND_API.G_FALSE,
1809 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1810 p_default => FND_API.G_FALSE,
1811 p_module_type => NULL,
1812 x_return_status => x_return_status,
1813 x_msg_count => l_msg_count,
1814 x_msg_data => l_msg_data,
1815 p_workorder_operation_id => l_prd_operation_tbl(i).workorder_operation_id,
1816 p_object_version_no => l_prd_operation_tbl(i).object_version_number
1817 );
1818 END LOOP;
1819 END IF;
1820 l_msg_count := FND_MSG_PUB.count_msg;
1821 IF l_msg_count > 0 THEN
1822 X_msg_count := l_msg_count;
1823 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1824 RAISE FND_API.G_EXC_ERROR;
1825 IF G_DEBUG='Y' THEN
1826 AHL_DEBUG_PUB.debug( 'Error in Complete Operation API');
1827 END IF;
1828 END IF;
1829
1830 IF FND_API.TO_BOOLEAN(p_commit) THEN
1831 COMMIT;
1832 END IF;
1833
1834 -- Debug info
1835
1836 IF G_DEBUG='Y' THEN
1837 AHL_DEBUG_PUB.debug( 'End of api '||l_api_name,'+debug+');
1838
1839 END IF;
1840
1841 IF G_DEBUG='Y' THEN
1842 AHL_DEBUG_PUB.disable_debug;
1843 END IF;
1844
1845 EXCEPTION
1846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1847 ROLLBACK TO PROCESS_MYWORKORDER_TXNS;
1848 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1849 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1850 p_count => x_msg_count,
1851 p_data => x_msg_data);
1852
1853 WHEN FND_API.G_EXC_ERROR THEN
1854 ROLLBACK TO PROCESS_MYWORKORDER_TXNS;
1855 X_return_status := FND_API.G_RET_STS_ERROR;
1856 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1857 p_count => x_msg_count,
1858 p_data => X_msg_data);
1859 WHEN OTHERS THEN
1860 ROLLBACK TO PROCESS_MYWORKORDER_TXNS;
1861 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1862 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1863 THEN
1864 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
1865 p_procedure_name =>l_api_name,
1866 p_error_text =>SUBSTR(SQLERRM,1,240)
1867 );
1868 END IF;
1869 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1870 p_count => x_msg_count,
1871 p_data => X_msg_data);
1872
1873 END;
1874
1875
1876
1877 FUNCTION Get_transacted_hours
1878 (
1879 p_wip_entity_id IN NUMBER,
1880 p_operation_seq_num IN NUMBER,
1881 p_resource_seq_num IN NUMBER,
1882 p_employee_id IN NUMBER
1883 ) RETURN NUMBER
1884
1885 IS
1886
1887 l_completed_hrs NUMBER ;
1888 l_pending_hrs NUMBER ;
1889 l_return_value NUMBER;
1890
1891
1892 CURSOR get_resource_txns(c_wip_entity_id NUMBER,
1893 c_operation_seq_num NUMBER,
1894 c_resource_seq_num NUMBER,
1895 c_employee_id NUMBER )
1896 IS
1897
1898 SELECT NVL( SUM( transaction_quantity ), 0 )
1899 FROM WIP_TRANSACTIONS
1900 WHERE wip_entity_id = c_wip_entity_id
1901 AND operation_seq_num = c_operation_seq_num
1902 AND resource_seq_num = c_resource_seq_num
1903 AND employee_id = c_employee_id;
1904
1905
1906 CURSOR get_pending_resource_txns( c_wip_entity_id NUMBER,
1907 c_operation_seq_num NUMBER,
1908 c_resource_seq_num NUMBER,
1909 c_employee_id NUMBER )
1910 IS
1911 SELECT NVL( SUM( transaction_quantity ), 0 )
1912 FROM WIP_COST_TXN_INTERFACE
1913 WHERE wip_entity_id = c_wip_entity_id
1914 AND operation_seq_num = c_operation_seq_num
1915 AND resource_seq_num = c_resource_seq_num
1916 AND employee_id = c_employee_id
1917 AND process_status = 1;
1918
1919
1920 BEGIN
1921 OPEN get_resource_txns( p_wip_entity_id,
1922 p_operation_seq_num,
1923 p_resource_seq_num,
1924 p_employee_id );
1925 FETCH get_resource_txns INTO l_completed_hrs;
1926 CLOSE get_resource_txns;
1927
1928 OPEN get_pending_resource_txns( p_wip_entity_id,
1929 p_operation_seq_num,
1930 p_resource_seq_num,
1931 p_employee_id
1932 );
1933 FETCH get_pending_resource_txns INTO l_pending_hrs;
1934 CLOSE get_pending_resource_txns;
1935
1936 l_return_value:=l_completed_hrs+l_pending_hrs;
1937
1938 RETURN l_return_value ;
1939 END ;
1940
1941 /*##################################################################################################*/
1942 --# NAME
1943 --# PROCEDURE: Get_Resource_Txn_Defaults
1944 --# PARAMETERS
1945 --# Standard IN Parameters
1946 --# p_api_version IN NUMBER := 1.0
1947 --# p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1948 --# p_module_type IN VARCHAR2 := NULL
1949 --#
1950 --# Standard OUT Parameters
1951 --# x_return_status OUT NOCOPY VARCHAR2
1952 --# x_msg_count OUT NOCOPY NUMBER
1953 --# x_msg_data OUT NOCOPY VARCHAR2
1954 --#
1955 --# Get_Resource_Txn_Defaults Parameters
1956 --# p_employee_id IN NUMBER
1957 --# p_workorder_id IN NUMBER
1958 --# p_operation_seq_num IN NUMBER
1959 --# p_function_name IN VARCHAR2 - The function name identifying the type of user
1960 --# x_resource_txn_tbl OUT NOCOPY PRD_RESOURCE_TXNS_TBL
1961 --#
1962 --# DESCRIPTION
1963 --# This procedure is used to retrieve the default resource transactions based on the user/function name
1964 --#
1965 --# HISTORY
1966 --# 16-Jun-2005 rroy Created
1967 --###################################################################################################*/
1968
1969 PROCEDURE Get_Resource_Txn_Defaults
1970 (
1971 p_api_version IN NUMBER := 1.0,
1972 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1973 p_module_type IN VARCHAR2 := NULL,
1974 x_return_status OUT NOCOPY VARCHAR2,
1975 x_msg_count OUT NOCOPY NUMBER,
1976 x_msg_data OUT NOCOPY VARCHAR2,
1977 p_employee_id IN NUMBER,
1978 p_workorder_id IN NUMBER,
1979 p_operation_seq_num IN NUMBER,
1980 p_function_name IN VARCHAR2,
1981 x_resource_txn_tbl OUT NOCOPY PRD_RESOURCE_TXNS_TBL
1982 )
1983 IS
1984 l_user_role VARCHAR2(4);
1985 l_employee_id NUMBER;
1986 i NUMBER;
1987 l_employee_name VARCHAR2(240);
1988 l_employee_num VARCHAR2(30);
1989
1990
1991 CURSOR c_get_resource_assgmt_tech(x_workorder_id NUMBER,
1992 x_operation_seq_num NUMBER,
1993 x_employee_id NUMBER)
1994 IS
1995 SELECT APRV.RESOURCE_SEQUENCE,
1996 APRV.RESOURCE_ID,
1997 APRV.RESOURCE_CODE,
1998 APRV.RESOURCE_TYPE_NAME,
1999 APRV.RESOURCE_TYPE_CODE,
2000 APRV.UOM_NAME,
2001 APRV.UOM_CODE,
2002 APRV.DEPARTMENT_ID,
2003 BD.department_code,
2004 APRV.REQUIREMENT_ID
2005 FROM AHL_WORK_ASSIGNMENTS AWAS,
2006 AHL_PP_REQUIREMENT_V APRV,bom_departments BD
2007 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
2008 AND APRV.DEPARTMENT_ID = BD.department_id
2009 AND APRV.JOB_ID = x_workorder_id
2010 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
2011 AND AWAS.EMPLOYEE_ID = x_employee_id;
2012
2013 CURSOR c_get_other_res_assgmt_tech(x_workorder_id NUMBER,
2014 x_operation_seq_num NUMBER)
2015 IS
2016 SELECT APRV.RESOURCE_SEQUENCE,
2017 APRV.RESOURCE_ID,
2018 APRV.RESOURCE_CODE,
2019 APRV.RESOURCE_TYPE_NAME,
2020 APRV.RESOURCE_TYPE_CODE,
2021 APRV.UOM_NAME,
2022 APRV.UOM_CODE,
2023 AWAS.SERIAL_NUMBER,
2024 AWAS.INSTANCE_ID,
2025 APRV.DEPARTMENT_ID,
2026 BD.department_code,
2027 APRV.REQUIREMENT_ID
2028 FROM AHL_WORK_ASSIGNMENTS AWAS,
2029 AHL_PP_REQUIREMENT_V APRV,bom_departments BD
2030 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
2031 AND APRV.DEPARTMENT_ID = BD.department_id
2032 AND APRV.JOB_ID = x_workorder_id
2033 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
2034 AND RESOURCE_TYPE_CODE <> 2;
2035
2036 CURSOR c_get_other_resource_req_tech(x_workorder_id NUMBER,
2037 x_operation_seq_num NUMBER)
2038 IS
2039 SELECT APRV.RESOURCE_SEQUENCE,
2040 APRV.RESOURCE_ID,
2041 APRV.RESOURCE_CODE,
2042 APRV.RESOURCE_TYPE_NAME,
2043 APRV.RESOURCE_TYPE_CODE,
2044 APRV.UOM_NAME,
2045 APRV.UOM_CODE,
2046 APRV.DEPARTMENT_ID,
2047 BD.department_code,
2048 APRV.REQUIREMENT_ID
2049 FROM AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
2050 WHERE APRV.JOB_ID = x_workorder_id
2051 AND APRV.DEPARTMENT_ID = BD.department_id
2052 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
2053 AND APRV.RESOURCE_TYPE_CODE <> 2
2054 AND NOT EXISTS (SELECT ASSIGNMENT_ID
2055 FROM AHL_WORK_ASSIGNMENTS AWAS
2056 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID);
2057
2058 CURSOR c_get_resource_assgmt_data(x_workorder_id NUMBER,
2059 x_operation_seq_num NUMBER)
2060 IS
2061 SELECT APRV.RESOURCE_SEQUENCE,
2062 APRV.RESOURCE_ID,
2063 APRV.RESOURCE_CODE,
2064 APRV.RESOURCE_TYPE_NAME,
2065 APRV.RESOURCE_TYPE_CODE,
2066 APRV.UOM_NAME,
2067 APRV.UOM_CODE,
2068 AWAS.EMPLOYEE_ID,
2069 AWAS.SERIAL_NUMBER,
2070 AWAS.INSTANCE_ID,
2071 APRV.DEPARTMENT_ID,
2072 BD.department_code,
2073 APRV.REQUIREMENT_ID
2074 FROM AHL_WORK_ASSIGNMENTS AWAS,
2075 AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
2076 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID
2077 AND APRV.DEPARTMENT_ID = BD.department_id
2078 AND APRV.JOB_ID = x_workorder_id
2079 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num;
2080
2081 CURSOR c_get_resource_req_data(x_workorder_id NUMBER,
2082 x_operation_seq_num NUMBER)
2083 IS
2084 SELECT APRV.RESOURCE_SEQUENCE,
2085 APRV.RESOURCE_ID,
2086 APRV.RESOURCE_CODE,
2087 APRV.RESOURCE_TYPE_NAME,
2088 APRV.RESOURCE_TYPE_CODE,
2089 APRV.UOM_NAME,
2090 APRV.UOM_CODE,
2091 APRV.DEPARTMENT_ID,
2092 BD.department_code,
2093 APRV.REQUIREMENT_ID
2094 FROM AHL_PP_REQUIREMENT_V APRV ,bom_departments BD
2095 WHERE APRV.JOB_ID = x_workorder_id
2096 AND APRV.DEPARTMENT_ID = BD.department_id
2097 AND APRV.OPERATION_SEQUENCE = x_operation_seq_num
2098 AND NOT EXISTS (SELECT ASSIGNMENT_ID
2099 FROM AHL_WORK_ASSIGNMENTS AWAS
2100 WHERE AWAS.OPERATION_RESOURCE_ID = APRV.REQUIREMENT_ID);
2101
2102 CURSOR c_get_emp_details(x_employee_id NUMBER)
2103 IS
2104 SELECT FULL_NAME,
2105 EMPLOYEE_NUMBER
2106 FROM PER_PEOPLE_F
2107 WHERE PERSON_ID = x_employee_id;
2108
2109 l_manual_enabled_profile_value VARCHAR2(1);
2110
2111 BEGIN
2112
2113 -- if the employee id is NULL then get the employee id
2114 -- id of the user who is currently logged in
2115 IF p_employee_id IS NULL THEN
2116 L_employee_id := AHL_PRD_WO_LOGIN_PVT.get_employee_id;
2117 ELSE
2118 L_employee_id := p_employee_id;
2119 END IF;
2120
2121 i:= 0;
2122
2123 l_manual_enabled_profile_value := NVL(fnd_profile.value('AHL_PRD_MANUAL_RES_TXN'), 'N');
2124
2125 IF p_function_name = 'AHL_PRD_TECH_MYWO' OR p_function_name = 'AHL_PRD_TRANSIT_TECH' THEN
2126 IF (l_manual_enabled_profile_value = 'Y') THEN
2127 OPEN c_get_emp_details(l_employee_id);
2128 FETCH c_get_emp_details INTO l_employee_name, l_employee_num;
2129 CLOSE c_get_emp_details;
2130
2131 FOR res_txn_rec IN c_get_resource_assgmt_tech(p_workorder_id, p_operation_seq_num, l_employee_id)
2132 LOOP
2133 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2134 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2135 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2136 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2137 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2138 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2139 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2140 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2141 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2142 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2143 X_resource_txn_tbl(i).person_id := l_employee_id;
2144 X_resource_txn_tbl(i).employee_name := l_employee_name;
2145 X_resource_txn_tbl(i).employee_num := l_employee_num;
2146 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2147 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2148 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2149 i := i + 1;
2150 END LOOP;
2151 END IF; -- IF (l_manual_enabled_profile_value = 'Y') THEN
2152
2153 FOR res_txn_rec IN c_get_other_res_assgmt_tech(p_workorder_id, p_operation_seq_num) LOOP
2154 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2155 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2156 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2157 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2158 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2159 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2160 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2161 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2162 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2163 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2164 X_resource_txn_tbl(i).serial_number := res_txn_rec.serial_number;
2165 X_resource_txn_tbl(i).Instance_id := res_txn_rec.Instance_id;
2166 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2167 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2168 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2169 i := i + 1;
2170 END LOOP;
2171 FOR res_txn_rec IN c_get_other_resource_req_tech(p_workorder_id, p_operation_seq_num) LOOP
2172 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2173 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2174 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2175 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2176 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2177 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2178 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2179 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2180 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2181 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2182 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2183 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2184 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2185 i := i + 1;
2186 END LOOP;
2187 ELSE
2188 FOR res_txn_rec IN c_get_resource_assgmt_data(p_workorder_id, p_operation_seq_num) LOOP
2189 IF res_txn_rec.employee_id IS NOT NULL THEN
2190 OPEN c_get_emp_details(res_txn_rec.employee_id);
2191 FETCH c_get_emp_details INTO l_employee_name, l_employee_num;
2192 CLOSE c_get_emp_details;
2193 END IF;
2194 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2195 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2196 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2197 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2198 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2199 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2200 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2201 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2202 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2203 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2204 X_resource_txn_tbl(i).person_id := res_txn_rec.employee_id;
2205 X_resource_txn_tbl(i).employee_name := l_employee_name;
2206 X_resource_txn_tbl(i).employee_num := l_employee_num;
2207 X_resource_txn_tbl(i).serial_number := res_txn_rec.serial_number;
2208 X_resource_txn_tbl(i).Instance_id := res_txn_rec.Instance_id;
2209 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2210 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2211 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2212 i := i + 1;
2213 END LOOP;
2214 FOR res_txn_rec IN c_get_resource_req_data(p_workorder_id, p_operation_seq_num) LOOP
2215 X_resource_txn_tbl(i).operation_sequence_num := p_operation_seq_num;
2216 X_resource_txn_tbl(i).workorder_id := p_workorder_id;
2217 X_resource_txn_tbl(i).resource_sequence_num := res_txn_rec.resource_sequence;
2218 x_resource_txn_tbl(i).operation_resource_id := res_txn_rec.requirement_id;
2219 X_resource_txn_tbl(i).resource_id := res_txn_rec.resource_id;
2220 X_resource_txn_tbl(i).resource_name := res_txn_rec.resource_CODE;
2221 X_resource_txn_tbl(i).resource_type_name := res_txn_rec.resource_type_name;
2222 X_resource_txn_tbl(i).resource_type_code := res_txn_rec.resource_type_code;
2223 X_resource_txn_tbl(i).uom_code := res_txn_rec.uom_code;
2224 X_resource_txn_tbl(i).uom_meaning := res_txn_rec.uom_name;
2225 x_resource_txn_tbl(i).department_id := res_txn_rec.department_id;
2226 x_resource_txn_tbl(i).department_code := res_txn_rec.department_code;
2227 x_resource_txn_tbl(i).transaction_date := SYSDATE;
2228 i := i + 1;
2229 END LOOP;
2230 END IF;--IF p_user_role = 'TECH' OR p_user_role = 'LINE' THEN
2231
2232
2233 END Get_Resource_Txn_Defaults;
2234
2235 END AHL_PRD_RESOURCE_TRANX_PVT;