4 ------------------------------------
1 PACKAGE BODY AHL_OSP_ACCOMP_PVT AS
2 /* $Header: AHLVOSAB.pls 120.0.12020000.2 2012/12/11 07:48:17 sareepar noship $ */
3
5 -- Common constants and variables --
6 ------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_OSP_ACCOMP_PVT';
8 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
9 G_LOG_PREFIX CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME;
10
11 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
12 l_log_statement NUMBER := fnd_log.level_statement;
13 l_log_procedure NUMBER := fnd_log.level_procedure;
14
15
16 ------------------------
17 -- Declare Local Functions --
18 ------------------------
19
20 ------------------------
21 -- Declare Procedures --
22 ------------------------
26 p_accomplishment_date IN DATE,
23 PROCEDURE Create_And_Accomplish_Mr(
24 p_instance_id IN NUMBER,
25 p_mr_header_id IN NUMBER,
27 x_ue_id OUT NOCOPY NUMBER,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2
31 );
32
33 PROCEDURE Accomplish_UMP(
34 p_unit_effectivity_id IN NUMBER,
35 p_accomplishment_date IN DATE,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 );
40
41
42 ------------------------------------------------------------------
43 ----------- BEGIN DEFINITION OF PROCEDURES AND FUNCTIONS ---------
44 ------------------------------------------------------------------
45 ----------------------------------------------------------------------------------------
46 -- Procedure Create_And_Accomplish_Mr
47 -- This API is used to create a single unit effectivity and accomplish it.
48 ----------------------------------------------------------------------------------------
49 PROCEDURE Create_And_Accomplish_Mr(
50 p_instance_id IN NUMBER,
51 p_mr_header_id IN NUMBER,
52 p_accomplishment_date IN DATE,
53 x_ue_id OUT NOCOPY NUMBER,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2)
57 IS
58
59 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_And_Accomplish_Mr';
60 L_FULL_NAME CONSTANT VARCHAR2(100) := G_LOG_PREFIX || '.' || L_API_NAME;
61
62 l_return_status VARCHAR2(1);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000);
65 l_ue_id NUMBER;
66
67 CURSOR get_instance_attrs(c_instance_id NUMBER) IS
68 SELECT serial_number,
69 lot_number,
70 inventory_item_id,
71 last_vld_organization_id,
72 inv_master_organization_id
73 FROM csi_item_instances
74 WHERE instance_id = c_instance_id;
75
76 --Modification by JROTICH for bug#13798727 BEGIN
77 CURSOR get_mr_dtls_csr(c_mr_header_id NUMBER) IS
78 SELECT MR_HEADER_ID,
79 MR_STATUS_CODE,
80 EFFECTIVE_FROM,
81 EFFECTIVE_TO,
82 TITLE
83 FROM AHL_MR_HEADERS_VL
84 WHERE MR_HEADER_ID = c_mr_header_id;
85 l_mr_dtls get_mr_dtls_csr%ROWTYPE;
86 --Modification by JROTICH for bug#13798727 END
87
88
89 BEGIN
90 --Initialize API return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92
93 IF (l_log_procedure >= l_log_current_level) THEN
94 FND_LOG.STRING(l_log_procedure, L_FULL_NAME || '.begin',
95 'At the start of the procedure. p_instance_id = ' || p_instance_id ||
96 ', p_mr_header_id = ' || p_mr_header_id ||
97 ', p_accomplishment_date = ' || p_accomplishment_date);
98 END IF;
99
100 --Modification by JROTICH for bug#13798727 BEGIN
101 OPEN get_mr_dtls_csr(c_mr_header_id => p_mr_header_id);
102 FETCH get_mr_dtls_csr INTO l_mr_dtls;
103 CLOSE get_mr_dtls_csr;
104
105 IF (l_log_statement >= l_log_current_level) THEN
106 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Validating accomplishment date.');
107 END IF;
108 --Validate that the date accomplishment date is between the effective to and from dates of the MR
109 IF (p_accomplishment_date < l_mr_dtls.effective_from OR p_accomplishment_date > nvl(l_mr_dtls.effective_to,SYSDATE)) THEN
110 IF (l_log_statement >= l_log_current_level) THEN
111 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Invalid accomplishment date.');
112 END IF;
113 FND_MESSAGE.set_name('AHL','AHL_OSP_ACC_INV_DATE');
114 FND_MESSAGE.set_token('TITLE',l_mr_dtls.title);
115 FND_MSG_PUB.add;
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 IF (l_log_statement >= l_log_current_level) THEN
120 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Validating MR status.');
121 END IF;
122 --Validate that the mr is in the complete status
123 IF (l_mr_dtls.mr_status_code <> 'COMPLETE' ) THEN
124 IF (l_log_statement >= l_log_current_level) THEN
125 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Invalid MR Status.');
126 END IF;
127 FND_MESSAGE.set_name('AHL','AHL_OSP_ACC_INV_STATUS');
128 FND_MESSAGE.set_token('TITLE',l_mr_dtls.title);
129 FND_MSG_PUB.add;
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132 --Modification by JROTICH for bug#13798727 END
133
134
135
136
137 IF (l_log_statement >= l_log_current_level) THEN
138 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'About to call AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY.');
139 END IF;
140 AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY(
141 p_api_version => 1.0,
142 x_return_status => l_return_status,
143 x_msg_count => l_msg_count,
144 x_msg_data => l_msg_data,
145 p_mr_header_id => p_mr_header_id,
146 p_instance_id => p_instance_id,
147 x_orig_ue_id => l_ue_id);
148 IF (l_log_statement >= l_log_current_level) THEN
152 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
149 fnd_log.string(l_log_statement, L_FULL_NAME, 'Returned from call to AHL_UMP_UNPLANNED_PVT.CREATE_UNIT_EFFECTIVITY. x_return_status = ' || l_return_status || ' and x_orig_ue_id = ' || l_ue_id);
150 END IF;
151
153 x_msg_count := l_msg_count;
154 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
155 x_return_status := l_return_status;
156 ELSE
160 RETURN;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 END IF;
159 -- Unable to create UMP successfully. Return early
161 END IF;
162
163 IF (l_log_statement >= l_log_current_level) THEN
164 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'About to call Accomplish_UMP');
165 END IF;
166 Accomplish_UMP(
167 p_unit_effectivity_id => l_ue_id,
168 p_accomplishment_date => p_accomplishment_date,
169 x_return_status => l_return_status,
170 x_msg_count => l_msg_count,
171 x_msg_data => l_msg_data
172 );
173 IF (l_log_statement >= l_log_current_level) THEN
174 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Returned from call to Accomplish_UMP. x_return_status = ' || l_return_status);
175 END IF;
176
177 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
178 x_msg_count := l_msg_count;
179 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
180 x_return_status := l_return_status;
181 ELSE
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 END IF;
184 ELSE
185 x_return_status := l_return_status; -- Success
186 END IF;
187 -- Return the UE Id created and accomplished
188 x_ue_id := l_ue_id;
189
190 IF (l_log_procedure >= l_log_current_level) THEN
191 FND_LOG.STRING(l_log_procedure, L_FULL_NAME||'.end', 'At the end of the procedure. Returning x_ue_id as ' || x_ue_id);
192 END IF;
193
194 END Create_And_Accomplish_Mr;
195
196 ----------------------------------------------------------------------------------------
197 -- Procedure Accomplish_UMP
198 -- This is a recursive procedure
199 -- It is used accomplish a UMP. It takes care of hierarchies and reset MRs.
200 ----------------------------------------------------------------------------------------
201 PROCEDURE Accomplish_UMP(
202 p_unit_effectivity_id IN NUMBER,
203 p_accomplishment_date IN DATE,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2)
207 IS
208
209 L_API_NAME CONSTANT VARCHAR2(30) := 'Accomplish_UMP';
210 L_FULL_NAME CONSTANT VARCHAR2(100) := G_LOG_PREFIX || '.' || L_API_NAME;
211
212 l_return_status VARCHAR2(1);
213 l_msg_count NUMBER;
214 l_msg_data VARCHAR2(2000);
215 l_mr_id NUMBER;
216 l_instance_id NUMBER;
217 l_ue_ovn NUMBER;
218 i NUMBER;
219
220 l_reset_counter_tbl AHL_COMPLETIONS_PVT.counter_tbl_type;
221 l_mr_rec AHL_COMPLETIONS_PVT.mr_rec_type;
222 l_counter_tbl AHL_COMPLETIONS_PVT.counter_tbl_type;
223
224 TYPE NUM_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
225 l_child_ue_ids_tbl NUM_TBL_TYPE;
226
227 CURSOR get_descendant_UEs_csr IS
228 SELECT related_ue_id
229 FROM AHL_UE_RELATIONSHIPS
230 START WITH ue_id = p_unit_effectivity_id
231 AND relationship_code = 'PARENT'
232 CONNECT BY ue_id = PRIOR related_ue_id
233 AND relationship_code = 'PARENT'
234 ORDER BY LEVEL DESC;
235
236 CURSOR get_ue_dtls_csr(c_unit_effectivity_id IN NUMBER) IS
237 SELECT MR_HEADER_ID,
238 CSI_ITEM_INSTANCE_ID,
239 OBJECT_VERSION_NUMBER
240 FROM ahl_unit_effectivities_b
241 WHERE UNIT_EFFECTIVITY_ID = c_unit_effectivity_id;
242
243 BEGIN
244 --Initialize API return status to success
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 IF (l_log_procedure >= l_log_current_level) THEN
248 FND_LOG.STRING(l_log_procedure, L_FULL_NAME || '.begin',
249 'At the start of the procedure. p_unit_effectivity_id = ' || p_unit_effectivity_id ||
250 ', p_accomplishment_date = ' || p_accomplishment_date);
251 END IF;
252
253 OPEN get_descendant_UEs_csr;
254 FETCH get_descendant_UEs_csr BULK COLLECT INTO l_child_ue_ids_tbl;
255 CLOSE get_descendant_UEs_csr;
256
257 IF (l_log_statement >= l_log_current_level) THEN
258 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'l_child_ue_ids_tbl.COUNT = ' || l_child_ue_ids_tbl.COUNT);
259 END IF;
260 -- Append the current (root) UE Id at the end
261 i := NVL(l_child_ue_ids_tbl.LAST, 0) + 1;
265 i := l_child_ue_ids_tbl.FIRST;
262 l_child_ue_ids_tbl(i) := p_unit_effectivity_id;
263
264 -- Process each Unit Effectivity in the hierarchy (order by descending LEVEL)
266 WHILE (i IS NOT NULL) LOOP
267 IF (l_log_statement >= l_log_current_level) THEN
268 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Processing index ' || i || ', Unit Effectivity Id = ' || l_child_ue_ids_tbl(i));
269 END IF;
270
271 OPEN get_ue_dtls_csr(c_unit_effectivity_id => l_child_ue_ids_tbl(i));
272 FETCH get_ue_dtls_csr INTO l_mr_id, l_instance_id, l_ue_ovn;
273 CLOSE get_ue_dtls_csr;
274
275 -- Get the Reset Counter Readings for all Counters associted with the MR and the Item Instance.
276 IF (l_mr_id IS NOT NULL) THEN
277 l_return_status := AHL_COMPLETIONS_PVT.get_reset_counters(p_mr_header_id => l_mr_id,
278 p_item_instance_id => l_instance_id,
279 p_actual_date => p_accomplishment_date,
280 x_counter_tbl => l_reset_counter_tbl);
281 END IF;
282
283 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
284 RAISE FND_API.G_EXC_ERROR;
285 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288
289 IF (l_reset_counter_tbl IS NOT NULL AND l_reset_counter_tbl.COUNT > 0) THEN
290 IF (l_log_statement >= l_log_current_level) THEN
291 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Resetting Counters...');
292 END IF;
293 -- Reset all the Counters with Reset Values specified in FMP
294 l_mr_rec.mr_header_id := l_mr_id;
295 l_mr_rec.unit_effectivity_id := l_child_ue_ids_tbl(i);
296 l_return_status := AHL_COMPLETIONS_PVT.reset_counters(p_mr_rec => l_mr_rec,
297 p_x_counter_tbl => l_reset_counter_tbl,
298 p_actual_end_date => p_accomplishment_date,
299 x_msg_count => l_msg_count,
300 x_msg_data => l_msg_data);
301
302 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
306 x_msg_data := l_msg_data;
303 IF (l_msg_data IS NOT NULL) THEN
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 x_msg_count := l_msg_count;
307 END IF;
308 RAISE FND_API.G_EXC_ERROR;
309 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311 END IF;
312 END IF; -- There are reset counters
313 l_reset_counter_tbl.DELETE;
314
315 IF (l_log_statement >= l_log_current_level) THEN
316 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Getting CP Counters...');
317 END IF;
318 -- Get the Current Counter Readings for all Counters associted with the Item Instance.
319 l_return_status := AHL_COMPLETIONS_PVT.get_cp_counters(p_item_instance_id => l_instance_id,
320 p_wip_entity_id => null,
321 p_actual_date => p_accomplishment_date,
322 x_counter_tbl => l_counter_tbl);
323 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
324 RAISE FND_API.G_EXC_ERROR;
325 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END IF;
328
332 -- Record Accomplishment in UMP
329 IF (l_log_statement >= l_log_current_level) THEN
330 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Updating UMP...');
331 END IF;
333 l_return_status := AHL_COMPLETIONS_PVT.update_ump(p_unit_effectivity_id => l_child_ue_ids_tbl(i),
334 p_ue_object_version => l_ue_ovn,
335 p_actual_end_date => p_accomplishment_date,
336 p_counter_tbl => l_counter_tbl,
337 p_dml_flag => 'C',
338 x_msg_count => l_msg_count,
339 x_msg_data => l_msg_data);
340
341 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
342 IF (l_msg_data IS NOT NULL) THEN
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344 x_msg_count := l_msg_count;
345 x_msg_data := l_msg_data;
346 END IF;
347 RAISE FND_API.G_EXC_ERROR;
348 ELSIF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 END IF;
351 l_counter_tbl.DELETE;
352
353 i := l_child_ue_ids_tbl.NEXT(i);
354
355 END LOOP; -- For all Unit Effectivities
356
357 IF (l_log_procedure >= l_log_current_level) THEN
358 FND_LOG.STRING(l_log_procedure, L_FULL_NAME||'.end', 'At the end of the procedure.');
359 END IF;
360
361 END Accomplish_UMP;
362
363 ----------------------------------------------------------------------------------------
364 -- Start of Comments --
365 -- Procedure name : accomplish_osp_mrs
366 -- Type : Private
367 -- Function : Procedure to Accomplish one or more MRs associated to a OSP Line.
368 -- The records in the AHL_OSP_ACCOMPLISHMENTS will also be updated
369 -- Will be called from the Service Details UI.
370 -- Pre-reqs :
371 -- Parameters :
372 --
373 -- Standard IN Parameters :
374 -- p_api_version IN NUMBER Default 1.0
375 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
376 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
377 -- Standard OUT Parameters :
378 -- x_return_status OUT VARCHAR2 Required
379 -- x_msg_count OUT NUMBER Required
380 -- x_msg_data OUT VARCHAR2 Required
381 -- process_disposition Parameters:
382 --
383 -- p_accomplishment_id IN NUMBER
384 -- If this parameter is passed, only a single UMP record is created and accomplished.
385 -- p_osp_order_line_id IN NUMBER
386 -- If this parameter is passed without passing the p_accomplishment_id parameter,
387 -- All the unaccomplished MRs assigned to the the OSP Line (and return line)
388 -- will be processed: UMP record created and immediately accomplished
389 --
390 -- Version :
391 -- Initial Version 1.0
392 --
393 -- End of Comments.
394 ----------------------------------------------------------------------------------------
395 PROCEDURE accomplish_osp_mrs(
396 p_api_version IN NUMBER := 1.0,
397 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
398 p_commit IN VARCHAR2 := FND_API.G_FALSE,
399 p_accomplishment_id IN NUMBER,
400 p_osp_order_line_id IN NUMBER,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2)
404 IS
405
406 L_API_NAME CONSTANT VARCHAR2(30) := 'Accomplish_Osp_Mrs';
407 L_API_VERSION CONSTANT NUMBER := 1.0;
408 L_FULL_NAME CONSTANT VARCHAR2(100) := G_LOG_PREFIX || '.' || L_API_NAME;
409 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
410 l_dummy VARCHAR2(1);
411 l_osp_order_line_id NUMBER;
412 i NUMBER;
413 j NUMBER;
414 l_unit_effectivity_id NUMBER;
415 l_msg_count NUMBER;
416 l_msg_data VARCHAR2(2000);
417 l_received_quantity NUMBER;
418
419 TYPE NUM_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
420 l_accomplishment_ids_tbl NUM_TBL_TYPE;
421 l_child_ue_ids_tbl NUM_TBL_TYPE;
422 l_instance_ids_tbl NUM_TBL_TYPE;
423 l_mr_ids_tbl NUM_TBL_TYPE;
424
425 TYPE DATE_TBL_TYPE IS TABLE OF DATE INDEX BY BINARY_INTEGER;
426 l_accomp_dates_tbl DATE_TBL_TYPE;
427
428 CURSOR chk_accomplishment_csr(c_accomplishment_id NUMBER) IS
429 SELECT osp_order_line_id
430 FROM ahl_osp_accomplishments
431 WHERE accomplishment_id = c_accomplishment_id
432 AND status_code = 'OPEN'
433 AND accomplished_date is not null;
434
435 CURSOR chk_osp_line_csr(c_osp_order_line_id NUMBER) IS
436 SELECT 'X'
440 CURSOR get_accomplishments_csr(c_osp_order_line_id NUMBER) IS
437 FROM ahl_osp_order_lines
438 WHERE osp_order_line_id = c_osp_order_line_id;
439
441 SELECT accomplishment_id
442 FROM ahl_osp_accomplishments
443 WHERE osp_order_line_id = c_osp_order_line_id
444 AND status_code = 'OPEN'
445 AND accomplished_date is not null;
446
447 CURSOR get_accomplishment_dtls_csr(c_accomplishment_id NUMBER) IS
448 SELECT item_instance_id,
449 mr_header_id,
450 accomplished_date,
451 object_version_number
452 FROM ahl_osp_accomplishments
453 WHERE accomplishment_id = c_accomplishment_id
454 AND status_code = 'OPEN'
455 AND accomplished_date is not null
456 FOR UPDATE of object_version_number;
457 l_accomplishment_dtls get_accomplishment_dtls_csr%ROWTYPE;
458
459 CURSOR get_received_quantity_csr(c_osp_order_line_id IN NUMBER) IS
460 SELECT OEL.shipped_quantity
461 FROM AHL_OSP_ORDER_LINES OSPL, OE_ORDER_LINES_ALL OEL
462 WHERE OSPL.OSP_ORDER_LINE_ID = c_osp_order_line_id
463 AND OEL.line_id = OSPL.OE_RETURN_LINE_ID;
464
465 CURSOR get_descendant_UEs_csr (c_ue_id IN NUMBER) IS
466 SELECT UER.related_ue_id, UEB.CSI_ITEM_INSTANCE_ID, UEB.MR_HEADER_ID, UEB.ACCOMPLISHED_DATE
467 FROM AHL_UE_RELATIONSHIPS UER,
468 AHL_UNIT_EFFECTIVITIES_B UEB
469 WHERE UER.related_ue_id = UEB.UNIT_EFFECTIVITY_ID
470 START WITH ue_id = c_ue_id
471 AND relationship_code = 'PARENT'
472 CONNECT BY ue_id = PRIOR related_ue_id
473 AND relationship_code = 'PARENT'
474 ORDER BY LEVEL DESC;
475
476
477 BEGIN
478
479 -- Standard start of API savepoint
480 SAVEPOINT accomplish_osp_mrs_pvt;
481 IF (l_log_procedure >= l_log_current_level) THEN
482 FND_LOG.STRING(l_log_procedure, L_FULL_NAME || '.begin', 'Entering Procedure. p_accomplishment_id = ' || p_accomplishment_id ||
483 ', p_osp_order_line_id = ' || p_osp_order_line_id);
484 END IF;
485
486 -- Standard call to check for call compatibility
487 IF NOT FND_API.Compatible_API_Call(L_API_VERSION, p_api_version, L_API_NAME, G_PKG_NAME) THEN
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489 END IF;
490
491 -- Initialize message list if p_init_msg_list is set to TRUE
492 IF FND_API.To_Boolean(p_init_msg_list) THEN
493 FND_MSG_PUB.Initialize;
494 END IF;
495
496 -- Initialize API return status to success
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498
499 -- Begin Processing
500
501 IF (p_accomplishment_id IS NOT NULL) THEN
502 -- Validate that p_accomplishment_id corresponds to an unaccomplished record.
503 OPEN chk_accomplishment_csr(c_accomplishment_id => p_accomplishment_id);
504 FETCH chk_accomplishment_csr INTO l_osp_order_line_id;
505 IF (chk_accomplishment_csr%NOTFOUND) THEN
506 CLOSE chk_accomplishment_csr;
507 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_INV_ACCOM_ID');
508 FND_MESSAGE.set_token('ACCOM_ID', p_accomplishment_id);
509 FND_MSG_PUB.ADD;
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END IF;
512 CLOSE chk_accomplishment_csr;
513
514 -- If OSP Order Line Id is also passed, validate it to ensure that it matches the Accomplishment Id
515 IF ((p_osp_order_line_id IS NOT NULL) AND (l_osp_order_line_id <> p_osp_order_line_id)) THEN
516 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_ACCOM_ID_MISMATCH');
517 FND_MESSAGE.set_token('ACCOM_ID', p_accomplishment_id);
518 FND_MESSAGE.set_token('OSP_LINE_ID', p_osp_order_line_id);
519 FND_MSG_PUB.ADD;
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521 END IF;
522
523 -- Validate that the part has been received against the OSP Line
524 OPEN get_received_quantity_csr(l_osp_order_line_id);
525 FETCH get_received_quantity_csr INTO l_received_quantity;
526 CLOSE get_received_quantity_csr;
527
528 IF (l_received_quantity IS NULL OR l_received_quantity < 1) THEN
529 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_NOT_RECEIVED');
530 FND_MSG_PUB.ADD;
531 RAISE FND_API.G_EXC_ERROR;
532 END IF;
533
534 -- Populate a table of Numbers with the single Accomplishment Id (will have only one record)
535 l_accomplishment_ids_tbl(1) := p_accomplishment_id;
536 ELSE
537 -- Ensure that p_osp_order_line_id is not null.
538 IF (p_osp_order_line_id IS NULL) THEN
539 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_MISSING_PARAMS');
540 FND_MSG_PUB.ADD;
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END IF;
543
544 -- Validate that the OSP Order Line Id is valid
545 OPEN chk_osp_line_csr(c_osp_order_line_id => p_osp_order_line_id);
546 FETCH chk_osp_line_csr INTO l_dummy;
547 IF (chk_osp_line_csr%NOTFOUND) THEN
548 CLOSE chk_osp_line_csr;
549 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_INV_OSP_LINE_ID');
550 FND_MESSAGE.set_token('OSP_LINE_ID', p_osp_order_line_id);
551 FND_MSG_PUB.ADD;
552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
553 END IF;
554 CLOSE chk_osp_line_csr;
555
556 -- Validate that the part has been received against the OSP Line
557 OPEN get_received_quantity_csr(p_osp_order_line_id);
558 FETCH get_received_quantity_csr INTO l_received_quantity;
559 CLOSE get_received_quantity_csr;
560
561 IF (l_received_quantity IS NULL OR l_received_quantity < 1) THEN
562 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_NOT_RECIEVED');
563 FND_MSG_PUB.ADD;
564 RAISE FND_API.G_EXC_ERROR;
565 END IF;
566
567 -- Populate a table of Numbers with the Id of the Open Accomplishments assigned to this OSP Line
571
568 OPEN get_accomplishments_csr(c_osp_order_line_id => p_osp_order_line_id);
569 FETCH get_accomplishments_csr BULK COLLECT INTO l_accomplishment_ids_tbl;
570 CLOSE get_accomplishments_csr;
572 -- Ensure that there is at least one accomplishment to signoff
573 IF (l_accomplishment_ids_tbl.COUNT = 0) THEN
574 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_OSP_NO_ACCOMS');
575 FND_MESSAGE.set_token('OSP_LINE_ID', p_osp_order_line_id);
576 FND_MSG_PUB.ADD;
577 RAISE FND_API.G_EXC_ERROR;
578 END IF;
579 END IF; -- Else (p_accomplishment_id is null)
580
581 i := l_accomplishment_ids_tbl.FIRST;
582 WHILE (i IS NOT NULL) LOOP
583 IF (l_log_statement >= l_log_current_level) THEN
584 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Processing index ' || i || ', Accomplishment Id = ' || l_accomplishment_ids_tbl(i));
585 END IF;
586 -- Retrieve details from AHL_OSP_ACCOMPLISHMENTS and lock the row for update
587 OPEN get_accomplishment_dtls_csr(c_accomplishment_id => l_accomplishment_ids_tbl(i));
588 FETCH get_accomplishment_dtls_csr INTO l_accomplishment_dtls;
589
590 -- Call the helper procedure Create_And_Accomplish_Mr
591 Create_And_Accomplish_Mr(p_instance_id => l_accomplishment_dtls.item_instance_id,
592 p_mr_header_id => l_accomplishment_dtls.mr_header_id,
593 p_accomplishment_date => l_accomplishment_dtls.accomplished_date,
594 x_ue_id => l_unit_effectivity_id,
595 x_return_status => l_return_status,
596 x_msg_count => l_msg_count,
597 x_msg_data => l_msg_data);
598
599 IF (l_log_statement >= l_log_current_level) THEN
600 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Returned from call to Create_And_Accomplish_Mr. x_return_status = ' || l_return_status);
601 END IF;
602
603 IF l_msg_count > 0 OR NVL(l_return_status,'X') <> Fnd_Api.G_RET_STS_SUCCESS THEN
604 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
605 RAISE FND_API.G_EXC_ERROR;
606 ELSE
607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
608 END IF;
609 END IF;
610
611 IF (l_log_statement >= l_log_current_level) THEN
612 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'About to update Accomplishments table for row with accomplishment_id ' || l_accomplishment_ids_tbl(i) ||
613 ' : Setting UNIT_EFFECTIVITY_ID as ' || l_unit_effectivity_id);
614 END IF;
615
616 UPDATE AHL_OSP_ACCOMPLISHMENTS
617 SET UNIT_EFFECTIVITY_ID = l_unit_effectivity_id,
618 STATUS_CODE = 'ACCOMPLISHED',
619 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
620 LAST_UPDATE_DATE = SYSDATE,
621 LAST_UPDATED_BY = FND_GLOBAL.user_id,
622 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
623 WHERE ACCOMPLISHMENT_ID = l_accomplishment_ids_tbl(i);
624
625 CLOSE get_accomplishment_dtls_csr;
626
627 -- If this is a Group MR, insert the additional records into the AHL_OSP_ACCOMPLISHMENTS table
628 OPEN get_descendant_UEs_csr(l_unit_effectivity_id);
629 FETCH get_descendant_UEs_csr BULK COLLECT INTO l_child_ue_ids_tbl, l_instance_ids_tbl, l_mr_ids_tbl, l_accomp_dates_tbl;
630 CLOSE get_descendant_UEs_csr;
631 IF (l_child_ue_ids_tbl IS NOT NULL AND l_child_ue_ids_tbl.COUNT > 0) THEN
632 IF (l_log_statement >= l_log_current_level) THEN
633 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'Accomplishment for Group MR. l_child_ue_ids_tbl.COUNT = ' || l_child_ue_ids_tbl.COUNT);
634 FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'About to insert additional records into AHL_OSP_ACCOMPLISHMENTS.');
635 END IF;
636 FOR j IN l_child_ue_ids_tbl.FIRST..l_child_ue_ids_tbl.LAST LOOP
637 INSERT INTO AHL_OSP_ACCOMPLISHMENTS(ACCOMPLISHMENT_ID,
638 OBJECT_VERSION_NUMBER,
639 LAST_UPDATE_DATE,
640 LAST_UPDATED_BY,
641 CREATION_DATE,
642 CREATED_BY,
643 LAST_UPDATE_LOGIN,
644 OSP_ORDER_LINE_ID,
645 ITEM_INSTANCE_ID,
646 UNIT_EFFECTIVITY_ID,
647 MR_HEADER_ID,
648 PLANNING_TYPE_CODE,
649 STATUS_CODE,
650 ACCOMPLISHED_DATE)
651 VALUES (AHL_OSP_ACCOMPLISHMENTS_S.NEXTVAL,
652 1,
653 SYSDATE,
654 FND_GLOBAL.user_id,
655 SYSDATE,
656 FND_GLOBAL.user_id,
657 FND_GLOBAL.login_id,
658 NVL(p_osp_order_line_id, l_osp_order_line_id),
659 l_instance_ids_tbl(j),
660 l_child_ue_ids_tbl(j),
661 l_mr_ids_tbl(j),
662 'MANUALLY_PLANNED',
663 'ACCOMPLISHED',
664 l_accomp_dates_tbl(j));
665 END LOOP;
666 END IF;
667 l_child_ue_ids_tbl.DELETE;
668 l_instance_ids_tbl.DELETE;
669 l_mr_ids_tbl.DELETE;
670 l_accomp_dates_tbl.DELETE;
674
671 i := l_accomplishment_ids_tbl.NEXT(i);
672
673 END LOOP; -- For all Open Accomplishments
675 -- Standard check of p_commit
676 IF FND_API.TO_BOOLEAN(p_commit) THEN
677 COMMIT WORK;
678 END IF;
679
680 -- Standard call to get message count and if count is 1, get message info
681 FND_MSG_PUB.Count_And_Get
682 ( p_count => x_msg_count,
683 p_data => x_msg_data,
684 p_encoded => fnd_api.g_false
685 );
686
687 IF (l_log_procedure >= l_log_current_level) THEN
688 FND_LOG.STRING(l_log_procedure, L_FULL_NAME || '.end', 'End Procedure');
689 END IF;
690
691 EXCEPTION
692 WHEN FND_API.G_EXC_ERROR THEN
693 Rollback to accomplish_osp_mrs_pvt;
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
696 p_data => x_msg_data,
697 p_encoded => fnd_api.g_false);
698 IF (FND_LOG.LEVEL_EXCEPTION >= l_log_current_level) THEN
699 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_FULL_NAME, 'Execution Exception: ' || x_msg_data);
700 END IF;
701
702 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
703 Rollback to accomplish_osp_mrs_pvt;
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
706 p_data => x_msg_data,
707 p_encoded => fnd_api.g_false);
708
709 IF (FND_LOG.LEVEL_UNEXPECTED >= l_log_current_level) THEN
710 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_FULL_NAME, 'Unexpected Exception: ' || x_msg_data);
711 END IF;
712
713 WHEN OTHERS THEN
714 Rollback to accomplish_osp_mrs_pvt;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
717 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
718 p_procedure_name => l_api_name,
719 p_error_text => SUBSTR(SQLERRM,1,500));
720 END IF;
721 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
722 p_data => x_msg_data,
723 p_encoded => fnd_api.g_false);
724
725 IF (FND_LOG.LEVEL_UNEXPECTED >= l_log_current_level) THEN
726 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_FULL_NAME, 'Other Exception: ' || x_msg_data);
727 END IF;
728
729 END accomplish_osp_mrs;
730
731 ----------------------------------------------------------------------------------------
732 -- Start of Comments --
733 -- Function name : Is_Received_Inst_Same
734 -- Type : Public
735 -- Functionality : Function to determine if the received instance is same as the recorded accomplishment instance.
736 -- It returns FND_API.G_TRUE if same and FND_API.G_FALSE if different.
737 -- It returns FND_API.G_TRUE if there has been no receipt also.
738 -- Pre-reqs :
739 --
740 -- Parameters:
741 --
742 -- p_osp_order_line_id IN NUMBER OSP Order Line Id
743 --
744 -- Version:
745 --
746 -- Initial Version 1.0
747 --
748 -- End of Comments --
749 ----------------------------------------------------------------------------------------
750 FUNCTION Is_Received_Inst_Same (
751 p_osp_order_line_id IN NUMBER
752 )
753 RETURN VARCHAR2 IS
754
755 -- Cursor to get the instances for which accomplishments are recorded
756 CURSOR get_recorded_instances_csr IS
757 SELECT distinct ITEM_INSTANCE_ID
758 FROM AHL_OSP_ACCOMPLISHMENTS
759 WHERE osp_order_line_id = p_osp_order_line_id;
760
761 CURSOR get_received_quantity_csr IS
762 SELECT OEL.shipped_quantity
763 FROM AHL_OSP_ORDER_LINES OSPL, OE_ORDER_LINES_ALL OEL
764 WHERE OSPL.OSP_ORDER_LINE_ID = p_osp_order_line_id
765 AND OEL.line_id = OSPL.OE_RETURN_LINE_ID;
766
767 CURSOR get_received_instance_csr IS
768 SELECT tld.instance_id
769 FROM csi_t_transaction_lines tl,
770 csi_t_txn_line_details tld,
771 ahl_osp_order_lines ospl
772 WHERE ospl.osp_order_line_id = p_osp_order_line_id
773 AND tl.source_transaction_id = ospl.oe_return_line_id
774 AND tl.source_transaction_table = 'OE_ORDER_LINES_ALL'
775 AND tld.transaction_line_id = tl.transaction_line_id;
776
777 CURSOR get_instance_descendants_csr (c_instance_id NUMBER) IS
778 SELECT c_instance_id from DUAL
779 UNION
780 SELECT subject_id from csi_ii_relationships
781 WHERE relationship_type_code = 'COMPONENT-OF'
782 AND NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE
783 AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
784 START WITH object_id = c_instance_id
788 connect by object_id = prior subject_id
785 AND relationship_type_code = 'COMPONENT-OF'
786 AND NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE
787 AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
789 AND relationship_type_code = 'COMPONENT-OF'
790 AND NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE
791 AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
792
793
794 --
795 L_API_NAME CONSTANT VARCHAR2(30) := 'Is_Received_Inst_Same';
796 L_FULL_NAME CONSTANT VARCHAR2(100) := G_LOG_PREFIX || '.' || L_API_NAME;
797
798 l_received_quantity NUMBER;
799 l_received_instance_id NUMBER;
800 TYPE NUM_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
801 l_instance_ids_tbl NUM_TBL_TYPE;
802 l_rcv_instance_ids_tbl NUM_TBL_TYPE;
803 i NUMBER;
804 j NUMBER;
805 l_curr_inst_id NUMBER;
806 l_found_flag BOOLEAN;
807
808 oe_header_id AHL_OSP_ORDERS_B.oe_header_id%TYPE;
809 l_oe_line_id OE_ORDER_LINES_ALL.line_id%TYPE;
810 l_dummy VARCHAR2(1);
811 --
812
813 BEGIN
814 IF (l_log_procedure >= l_log_current_level) THEN
815 FND_LOG.string(l_log_procedure, L_FULL_NAME||'.begin', 'Function called with p_osp_order_line_id: ' || p_osp_order_line_id);
816 END IF;
817
818 OPEN get_received_quantity_csr;
819 FETCH get_received_quantity_csr INTO l_received_quantity;
820 CLOSE get_received_quantity_csr;
821
822 IF (l_received_quantity IS NULL OR l_received_quantity < 1) THEN
823 IF (l_log_statement >= l_log_current_level) THEN
824 FND_LOG.string(l_log_statement, L_FULL_NAME, 'Returned quantity for OSP Order Line is ' || l_received_quantity);
825 END IF;
826 -- Return True if there is no receipt
827 RETURN FND_API.G_TRUE;
828 END IF;
829
830 OPEN get_recorded_instances_csr;
831 FETCH get_recorded_instances_csr BULK COLLECT INTO l_instance_ids_tbl;
832 CLOSE get_recorded_instances_csr;
833
834 IF (l_log_statement >= l_log_current_level) THEN
835 FND_LOG.string(l_log_statement, L_FULL_NAME, 'Recorded instances l_instance_ids_tbl.COUNT = ' || l_instance_ids_tbl.COUNT);
836 END IF;
837 IF (l_instance_ids_tbl.COUNT = 0) THEN
838 IF (l_log_statement >= l_log_current_level) THEN
839 FND_LOG.string(l_log_statement, L_FULL_NAME, 'No recorded accomplishments for the OSP Order Line.');
840 END IF;
841 -- Return True if there is no recorded accomplishments
842 RETURN FND_API.G_TRUE;
843 END IF;
844
845 -- Get the received instance
846 OPEN get_received_instance_csr;
847 FETCH get_received_instance_csr INTO l_received_instance_id;
848 CLOSE get_received_instance_csr;
849 IF (l_log_statement >= l_log_current_level) THEN
850 FND_LOG.string(l_log_statement, L_FULL_NAME, 'Received instance for OSP Order Line is ' || l_received_instance_id);
851 END IF;
852 IF (l_received_instance_id IS NULL) THEN
853 -- Return True if there is no receipt
854 RETURN FND_API.G_TRUE;
855 END IF;
856
857 -- Get the tree of the received instance
858 OPEN get_instance_descendants_csr(l_received_instance_id);
859 FETCH get_instance_descendants_csr BULK COLLECT INTO l_rcv_instance_ids_tbl;
860 CLOSE get_instance_descendants_csr;
861 IF (l_log_statement >= l_log_current_level) THEN
862 FND_LOG.string(l_log_statement, L_FULL_NAME, 'Received Instances l_rcv_instance_ids_tbl.COUNT = ' || l_rcv_instance_ids_tbl.COUNT);
863 END IF;
864
865 IF (l_rcv_instance_ids_tbl.COUNT = 1) THEN
866 IF ((l_instance_ids_tbl.COUNT = 1) AND (l_instance_ids_tbl(l_instance_ids_tbl.FIRST)) = l_received_instance_id) THEN
867 RETURN FND_API.G_TRUE;
868 ELSE
869 RETURN FND_API.G_FALSE;
870 END IF;
871 END IF;
872
873 -- Received instance is an IB Tree
874 i := l_instance_ids_tbl.FIRST;
875 WHILE (i IS NOT NULL) LOOP
876 l_curr_inst_id := l_instance_ids_tbl(i);
877 -- Search for this in the receive instances
878 l_found_flag := false;
879 j := l_rcv_instance_ids_tbl.FIRST;
880 WHILE ((j IS NOT NULL) AND (l_found_flag = false)) LOOP
881 IF (l_rcv_instance_ids_tbl(j) = l_curr_inst_id) THEN
882 l_found_flag := true;
883 END IF;
884 j := l_rcv_instance_ids_tbl.NEXT(j);
885 END LOOP; -- For all received instances
886 IF (l_found_flag = false) THEN
887 IF (l_log_statement >= l_log_current_level) THEN
888 FND_LOG.string(l_log_statement, L_FULL_NAME, 'The instance ' || l_curr_inst_id || ' was not received.');
889 END IF;
890 RETURN FND_API.G_FALSE;
891 END IF;
892
893 i := l_instance_ids_tbl.NEXT(i);
894
895 END LOOP; -- For all recorded instances
896
897 -- All recorded instances were matched
898 RETURN FND_API.G_TRUE;
899
900 END Is_Received_Inst_Same;
901
902
903 END AHL_OSP_ACCOMP_PVT;
[Home] [Help]