1 PACKAGE BODY JMF_PROCESS_SHIKYU_RCV_TRX_PVT AS
2 -- $Header: JMFVSKTB.pls 120.14 2006/11/23 15:22:33 vmutyala noship $
3
4 --=============================================
5 -- CONSTANTS
6 --=============================================
7 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'jmf.plsql.' || G_PKG_NAME || '.';
8
9 --=============================================
10 -- GLOBAL VARIABLES
11 --=============================================
12
13 g_debug_level NUMBER := NULL;
14 g_proc_level NUMBER := NULL;
15 g_unexp_level NUMBER := NULL;
16 g_excep_level NUMBER := NULL;
17 g_statement_level NUMBER := NULL;
18
19 PROCEDURE Init;
20 PROCEDURE Validate_And_Allocate
21 ( p_init_msg_list IN VARCHAR2
22 , x_return_status OUT NOCOPY VARCHAR2
23 , x_msg_count OUT NOCOPY NUMBER
24 , x_msg_data OUT NOCOPY VARCHAR2
25 , x_return_flag OUT NOCOPY NUMBER
26 , p_po_shipment_id IN NUMBER
27 , p_project_id IN NUMBER
28 , p_task_id IN NUMBER
29 );
30
31 --========================================================================
32 -- PROCEDURE : Process_Shikyu_Rcv_trx PUBLIC
33 -- PARAMETERS: p_api_version IN Standard in parameter API version
34 -- p_init_msg_list IN Standard in parameter message list
35 -- p_request_id IN Request Id
36 -- p_group_id IN Group Id
37 -- x_return_status OUT Stadard out parameter for return status
38 -- (Values E(Error), S(Success), U(Unexpected error))
39 --
40 -- COMMENT : This concurrent program will be called to process OSA Receipt,
41 -- OSA Return and RTV of SHIKYU Components at MP site. RTV of SHIKYU
42 -- Component is triggered by SHIKYU RMA at OEM site.
43 -- Following is core logic:
44 --1. Get all records from history table rcv_transactions corresponding to records in staging table.
45 --
46 --2. Club correction related records to their parent records if parent transactions are also present in staging table.
47 --
48 -- Currently only correction transaction are clubbed parent records
49 --
50 --3. Process records after clubbing.
51 -- If source document code is 'PO' then
52 -- {
53 -- If transaction type is 'RECEIVE' then
54 -- call OSA Receipt( poShipmentLineId )
55 -- else if transaction type is 'RETURN TO VENDOR' then
56 -- call OSA Return( poShipmentLineId )
57 -- else if transaction type is 'CORRECT' and parent Transaction Type in ('RECEIVE', 'DELIVER') then
58 -- call OSA Receipt( poShipmentLineId )
59 -- else if transaction type is 'CORRECT' and parent Transaction Type is 'RETURN TO VENDOR' then
60 -- call OSA Return( poShipmentLineId )
61 -- }
62 -- else source document code is 'RMA'
63 -- {
64 -- if transaction type is 'RECEIVE' then
65 -- call ProcessComponentReturn( OeOrderLineId )
66 -- -- we're not supporting corrections or returns against RMA
67 -- -- so other transaction types are not considered.
68 --}
69 --========================================================================
70 PROCEDURE Process_Shikyu_Rcv_trx(
71 p_api_version IN NUMBER,
72 p_init_msg_list IN VARCHAR2,
73 p_request_id NUMBER,
74 p_group_id NUMBER,
75 x_return_status OUT NOCOPY VARCHAR2
76 ) IS
77 -- p_request_id and p_group_id are optional parameters that can be specified to limit the scope of transactions to process
78
79 -- Define a record type to contain required transaction details from rcv_transactions.
80 TYPE rcv_pending_trx_rec IS RECORD
81 ( transaction_id rcv_transactions.transaction_id%Type
82 , source_document_code rcv_transactions.source_document_code%Type
83 , transaction_type rcv_transactions.transaction_type%Type
84 , primary_quantity rcv_transactions.primary_quantity%Type
85 , primary_unit_of_measure rcv_transactions.primary_unit_of_measure%Type
86 , parent_transaction_id rcv_transactions.parent_transaction_id%Type
87 , po_line_location_id rcv_transactions.po_line_location_id%Type
88 , project_id rcv_transactions.project_id%Type
89 , task_id rcv_transactions.task_id%Type
90 , oe_order_line_id rcv_transactions.oe_order_line_id%Type
91 , process_type VARCHAR2(10)
92 , clubbed_transaction_id rcv_transactions.transaction_id%Type
93 , error_status rcv_staging_table.status%Type
94 );
95
96 -- Define a table of above record type
97 TYPE rcv_pending_trx_tbl IS TABLE OF rcv_pending_trx_rec
98 INDEX BY BINARY_INTEGER;
99 l_rcv_pending_trx_tbl rcv_pending_trx_tbl;
100 l_rcv_pending_clubbed_trx_tbl rcv_pending_trx_tbl;
101
102 rcv_success_trx_ids DBMS_SQL.number_table;
103 rcv_error_trx_ids DBMS_SQL.number_table;
104 l_net_Quantity NUMBER;
105 l_primary_uom_code VARCHAR2(3);
106 l_exists BOOLEAN;
107 l_parent_transaction_type rcv_transactions.transaction_type%Type;
108 l_error_trx_index NUMBER;
109
110 -- Define standard variables.
111 l_msg_count NUMBER;
112 l_msg_data VARCHAR2(2000);
113 l_api_name CONSTANT VARCHAR2(30) := 'Process_Shikyu_Rcv_trx';
114 l_api_version CONSTANT NUMBER := 1.0;
115
116 -- vmutyala changed the cursor get_rcv_transactions to fetch transactions from
117 -- rcv_transactions and rcv_staging_table only if the Shipment_ID of the
118 -- transaction in RCV_STAGING_TABLE exists in jmf_subcontract_orders Bug 4670527
119
120
121
122 CURSOR get_rcv_transactions_case1 IS
123 SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
124 rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
125 rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
126 rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
127 FROM rcv_transactions rt, rcv_staging_table rst
128 WHERE rst.transaction_id = rt.transaction_id
129 AND rst.transaction_request_id = p_request_id
130 AND rst.transaction_group_id = p_group_id
131 AND rst.team = g_team_name
132 AND EXISTS(
133 SELECT 1
134 FROM jmf_subcontract_orders jso
135 WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
136 AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
137 UNION
138 SELECT 1
139 FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
140 WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
141 AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
142 AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
143 )
144 ORDER BY rt.transaction_id;
145
146
147 CURSOR get_rcv_transactions_case2 IS
148 SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
149 rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
150 rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
151 rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
152 FROM rcv_transactions rt, rcv_staging_table rst
153 WHERE rst.transaction_id = rt.transaction_id
154 AND rst.transaction_request_id = p_request_id
155 AND rst.team = g_team_name
156 AND EXISTS(
157 SELECT 1
158 FROM jmf_subcontract_orders jso
159 WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
160 AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
161 UNION
162 SELECT 1
163 FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
164 WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
165 AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
166 AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
167 )
168 ORDER BY rt.transaction_id;
169
170 CURSOR get_rcv_transactions_case3 IS
171 SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
172 rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
173 rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
174 rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
175 FROM rcv_transactions rt, rcv_staging_table rst
176 WHERE rst.transaction_id = rt.transaction_id
177 AND rst.transaction_group_id = p_group_id
178 AND rst.team = g_team_name
179 AND EXISTS(
180 SELECT 1
181 FROM jmf_subcontract_orders jso
182 WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
183 AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
184 UNION
185 SELECT 1
186 FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
187 WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
188 AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
189 AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
190 )
191 ORDER BY rt.transaction_id;
192
193 CURSOR get_rcv_transactions_case4 IS
194 SELECT distinct (rt.TRANSACTION_ID), rt.SOURCE_DOCUMENT_CODE,
195 rt.TRANSACTION_TYPE, rt.PRIMARY_QUANTITY, rt.PRIMARY_UNIT_OF_MEASURE,
196 rt.PARENT_TRANSACTION_ID,rt.PO_LINE_LOCATION_ID,
197 rt.PROJECT_ID, rt.TASK_ID, rt.OE_ORDER_LINE_ID, null, null, rst.status
198 FROM rcv_transactions rt, rcv_staging_table rst
199 WHERE rst.transaction_id = rt.transaction_id
200 AND rst.team = g_team_name
201 AND EXISTS(
202 SELECT 1
203 FROM jmf_subcontract_orders jso
204 WHERE rt.SOURCE_DOCUMENT_CODE = 'PO'
205 AND jso.SUBCONTRACT_PO_SHIPMENT_ID = rt.PO_LINE_LOCATION_ID
206 UNION
207 SELECT 1
208 FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
209 WHERE rt.SOURCE_DOCUMENT_CODE = 'RMA'
210 AND rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
211 AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID
212 )
213 ORDER BY rt.transaction_id;
214
215 /* note: the cursor purposely does not distinguish between PENDING and ERROR rows so that the user can fix a problem and then rerun this process and attempt again to process this row */
216
217 BEGIN
218 Init;
219 IF g_proc_level >= g_debug_level
220 THEN
221 FND_LOG.string(g_proc_level
222 , G_MODULE_PREFIX || l_api_name || '.begin'
223 , NULL);
224 END IF;
225
226 -- Start API initialization
227 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 IF NOT FND_API.Compatible_API_Call( l_api_version
232 , p_api_version
233 , l_api_name
234 , G_PKG_NAME
235 )
236 THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239 /* vmutyala changed the following initialization for Bug 4670527 */
240
241 x_return_status := FND_API.G_RET_STS_ERROR;
242
243 -- End API initialization
244
245 -- collect relevant records from rcv_transactions in table data type.
246
247 IF p_request_id is NOT NULL and p_group_id is NOT NULL then
248 OPEN get_rcv_transactions_case1;
249 FETCH get_rcv_transactions_case1 BULK COLLECT INTO l_rcv_pending_trx_tbl;
250 CLOSE get_rcv_transactions_case1;
251
252 ELSIF p_request_id is NOT NULL then
253 OPEN get_rcv_transactions_case2;
254 FETCH get_rcv_transactions_case2 BULK COLLECT INTO l_rcv_pending_trx_tbl;
255 CLOSE get_rcv_transactions_case2;
256
257 ELSIF p_group_id is NOT NULL then
258 OPEN get_rcv_transactions_case3;
259 FETCH get_rcv_transactions_case3 BULK COLLECT INTO l_rcv_pending_trx_tbl;
260 CLOSE get_rcv_transactions_case3;
261
262 ELSE
263 OPEN get_rcv_transactions_case4;
264 FETCH get_rcv_transactions_case4 BULK COLLECT INTO l_rcv_pending_trx_tbl;
265 CLOSE get_rcv_transactions_case4;
266
267 END IF;
268
269
270 IF g_statement_level >= g_debug_level THEN
271 FND_LOG.string(g_statement_level
272 , G_MODULE_PREFIX || l_api_name
273 , 'Read transaction records successfully. Start clubbing Correction records.');
274 END IF;
275 -- Process all records from staging table corresponding to team JMF
276 FOR i IN 1 .. l_rcv_pending_trx_tbl.COUNT LOOP
277 -- Get primary UOM code.
278 select UOM_CODE
279 into l_primary_uom_code
280 from MTL_UNITS_OF_MEASURE_VL
281 where UNIT_OF_MEASURE = l_rcv_pending_trx_tbl(i).primary_unit_of_measure;
282
283 IF ( l_rcv_pending_trx_tbl(i).transaction_type = 'RECEIVE' OR
284 l_rcv_pending_trx_tbl(i).transaction_type = 'DELIVER' OR
285 l_rcv_pending_trx_tbl(i).transaction_type = 'RETURN TO VENDOR') THEN
286
287 -- Clubbing is not required if it is Receive or Return transaction.
288 l_rcv_pending_clubbed_trx_tbl(l_rcv_pending_clubbed_trx_tbl.COUNT+1) :=
289 l_rcv_pending_trx_tbl(i);
290
291 ELSIF l_rcv_pending_trx_tbl(i).transaction_type = 'CORRECT' THEN
292 l_exists := FALSE;
293 -- Clubbing is required if it is Correction transaction.
294 FOR k IN 1 .. l_rcv_pending_clubbed_trx_tbl.COUNT LOOP
295 IF (l_rcv_pending_clubbed_trx_tbl(k).transaction_id =
296 l_rcv_pending_trx_tbl(i).parent_transaction_id AND
297 l_rcv_pending_clubbed_trx_tbl(k).po_line_location_id =
298 l_rcv_pending_trx_tbl(i).po_line_location_id) THEN
299
300 l_exists := TRUE;
301 -- Note the transactionId with which it is clubbed to maintain
302 -- proper status in staging table.
303 l_rcv_pending_trx_tbl(i).clubbed_transaction_id :=
304 l_rcv_pending_clubbed_trx_tbl(k).transaction_id;
305
306 l_parent_transaction_type := l_rcv_pending_clubbed_trx_tbl(k).transaction_type;
307 l_net_Quantity :=
308 l_rcv_pending_clubbed_trx_tbl(k).primary_quantity +
309 l_rcv_pending_trx_tbl(i).primary_quantity;
310
311 -- If net quantity is less than 0 then clubbed transaction will be a
312 -- negative correction.
313 IF l_net_Quantity < 0 THEN
314 l_rcv_pending_clubbed_trx_tbl(k) := l_rcv_pending_trx_tbl(i);
315 END IF;
316 l_rcv_pending_clubbed_trx_tbl(k).primary_quantity := l_net_Quantity;
317
318
319 -- Find process_type based on parent transaction type.
320 IF l_rcv_pending_clubbed_trx_tbl(k).transaction_type = 'CORRECT' THEN
321 IF l_parent_transaction_type = 'RECEIVE' OR
322 l_parent_transaction_type = 'DELIVER' THEN
323 l_rcv_pending_clubbed_trx_tbl(k).process_type := 'RECEIPT' ;
324 ELSIF l_parent_transaction_type = 'RETURN TO VENDOR' THEN
325 l_rcv_pending_clubbed_trx_tbl(k).process_type := 'RETURN';
326 END IF;
327 END IF; -- IF l_rcv_pending_clubbed_trx_tbl(k).transaction_type = 'CORRECT' THEN
328 END IF; -- IF (l_rcv_pending_clubbed_trx_tbl(k).transaction_id =
329 END LOOP; -- FOR k IN 1 .. l_rcv_pending_clubbed_trx_tbl.COUNT LOOP
330
331 -- If clubbing is not done then add correction transaction to clubbed
332 -- transaction list.
333 /* vmutyala modified the following code. Previously independent correction transactions whose
334 parent transactions are deleted from the staging table because of a successful run were inserted
335 into clubbed transactions but not processed later neither in osa receipt nor in return
336 because of conditions in if statements. The condition is that if transaction type is 'CORRECT'
337 and process type is one of 'RECEIPT' or 'RETURN' then process the transaction. Process type of
338 the above mentioned transactions was not being set in the commented code. Bug 4670527 */
339
340
341 IF NOT l_exists THEN
342 l_rcv_pending_clubbed_trx_tbl(l_rcv_pending_clubbed_trx_tbl.COUNT+1) :=
343 l_rcv_pending_trx_tbl(i);
344 select transaction_type
345 into l_parent_transaction_type
346 from rcv_transactions
347 where transaction_id =
348 (select PARENT_TRANSACTION_ID
349 from rcv_transactions
353 For independent correction transactions to set the process type depending on parent transaction type
350 where transaction_id = l_rcv_pending_trx_tbl(i).transaction_id);
351
352 /*vmutyala added the following code.
354 Bug 4670527*/
355
356 IF (l_parent_transaction_type = 'RECEIVE' OR l_parent_transaction_type = 'DELIVER') THEN
357 l_rcv_pending_clubbed_trx_tbl(l_rcv_pending_clubbed_trx_tbl.COUNT).process_type :=
358 'RECEIPT';
359 ELSIF (l_parent_transaction_type = 'RETURN TO VENDOR') THEN
360 l_rcv_pending_clubbed_trx_tbl(l_rcv_pending_clubbed_trx_tbl.COUNT).process_type :=
361 'RETURN';
362 END IF; -- IF (l_parent_transaction_type = 'RECEIVE'
363 END IF; -- IF NOT l_exists
364
365 END IF; -- IF ( l_rcv_pending_trx_tbl(i).transaction_type = 'RECEIVE' OR
366
367
368 END LOOP; -- FOR i IN 1 .. l_rcv_pending_trx_tbl.COUNT LOOP
369
370 IF g_statement_level >= g_debug_level THEN
371 FND_LOG.string(g_statement_level
372 , G_MODULE_PREFIX || l_api_name
373 , 'Clubbed Correction records successfully.');
374 END IF;
375
376 -- process clubbed transactions based on transaction_type.
377 FOR i IN 1 .. l_rcv_pending_clubbed_trx_tbl.COUNT LOOP
378 BEGIN
379 IF l_rcv_pending_clubbed_trx_tbl(i).primary_quantity = 0 THEN
380 x_return_status := fnd_api.g_ret_sts_success;
381 ELSIF l_rcv_pending_clubbed_trx_tbl(i).source_document_code = 'PO' THEN
382 IF (l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'RECEIVE' OR
383 l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'DELIVER' OR
384 (l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'CORRECT' AND
385 l_rcv_pending_clubbed_trx_tbl(i).process_type = 'RECEIPT' )) THEN
386
387 IF g_statement_level >= g_debug_level THEN
388 FND_LOG.string(g_statement_level
389 , G_MODULE_PREFIX || l_api_name
390 , 'Perform OSA Receipt for transaction : '||
391 l_rcv_pending_clubbed_trx_tbl(i).transaction_id);
392 END IF;
393
394 -- Call OSA Receipt API
395 JMF_PROCESS_SHIKYU_RCV_TRX_PVT.Process_Osa_Receipt(
396 p_api_version => 1.0
397 , p_init_msg_list => p_init_msg_list
398 , x_return_status => x_return_status
399 , x_msg_count => l_msg_count
400 , x_msg_data => l_msg_data
401 , p_po_shipment_id =>l_rcv_pending_clubbed_trx_tbl(i).po_line_location_id
402 , p_quantity => l_rcv_pending_clubbed_trx_tbl(i).primary_quantity
403 , p_uom => l_primary_uom_code
404 , p_transaction_type => l_rcv_pending_clubbed_trx_tbl(i).transaction_type
405 , p_project_id => l_rcv_pending_clubbed_trx_tbl(i).project_id
406 , p_task_id => l_rcv_pending_clubbed_trx_tbl(i).task_id
407 , p_status => l_rcv_pending_clubbed_trx_tbl(i).error_status
408 );
409 ELSIF (l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'RETURN TO VENDOR' OR
410 (l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'CORRECT' AND
411 l_rcv_pending_clubbed_trx_tbl(i).process_type = 'RETURN')) THEN
412
413 IF g_statement_level >= g_debug_level THEN
414 FND_LOG.string(g_statement_level
415 , G_MODULE_PREFIX || l_api_name
416 , 'Perform OSA Return for transaction : '||
417 l_rcv_pending_clubbed_trx_tbl(i).transaction_id);
418 END IF;
419
420 -- Call OSA Return API
421 JMF_PROCESS_SHIKYU_RCV_TRX_PVT.Process_Osa_Return(
422 p_api_version => 1.0
423 , p_init_msg_list => p_init_msg_list
424 , x_return_status => x_return_status
425 , x_msg_count => l_msg_count
426 , x_msg_data => l_msg_data
427 , p_po_shipment_id =>l_rcv_pending_clubbed_trx_tbl(i).po_line_location_id
428 , p_quantity => l_rcv_pending_clubbed_trx_tbl(i).primary_quantity
429 , p_uom => l_primary_uom_code
430 , p_transaction_type => l_rcv_pending_clubbed_trx_tbl(i).transaction_type
431 , p_project_id => l_rcv_pending_clubbed_trx_tbl(i).project_id
432 , p_task_id => l_rcv_pending_clubbed_trx_tbl(i).task_id
433 , p_status => l_rcv_pending_clubbed_trx_tbl(i).error_status
434 );
435 END IF;
436 ELSIF l_rcv_pending_clubbed_trx_tbl(i).source_document_code = 'RMA' THEN
437 IF (l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'RECEIVE' OR
438 l_rcv_pending_clubbed_trx_tbl(i).transaction_type = 'DELIVER') THEN
439
440 IF g_statement_level >= g_debug_level THEN
441 FND_LOG.string(g_statement_level
442 , G_MODULE_PREFIX || l_api_name
443 , 'Perform SHIKYU RTV for transaction : '||
444 l_rcv_pending_clubbed_trx_tbl(i).transaction_id);
445 END IF;
446
447 -- Call Component Return API
448 JMF_PROCESS_SHIKYU_RCV_TRX_PVT.Process_Component_Return(
449 p_api_version => 1.0
450 , p_init_msg_list => p_init_msg_list
451 , x_return_status => x_return_status
452 , x_msg_count => l_msg_count
453 , x_msg_data => l_msg_data
454 , p_rma_line_id => l_rcv_pending_clubbed_trx_tbl(i).oe_order_line_id
455 , p_quantity => l_rcv_pending_clubbed_trx_tbl(i).primary_quantity
456 , p_uom => l_primary_uom_code
457 , p_status => l_rcv_pending_clubbed_trx_tbl(i).error_status
458 );
459 END IF;
460
461 -- Since corrections or Return against SHIKYU RMA are not supported
462 -- so other transaction types are not included
463
467 -- transaction list.
464 END IF; -- IF l_rcv_pending_clubbed_trx_tbl.quantity = 0 THEN
465
466 -- If return status is not successful then add transaction in error
468 /*vmutyala changed the following code to make a note of the transactions which are successful along with the erroneous ones Bug 4670527*/
469
470 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
471 rcv_success_trx_ids(rcv_success_trx_ids.COUNT + 1) :=
472 l_rcv_pending_clubbed_trx_tbl(i).transaction_id;
473 ELSE
474 rcv_error_trx_ids(rcv_error_trx_ids.COUNT + 1) := i;
475 IF l_rcv_pending_clubbed_trx_tbl(i).error_status = 'PENDING' THEN
476 l_rcv_pending_clubbed_trx_tbl(i).error_status := 'ERROR';
477 END IF;
478 -- log error message here if so desired
479 END IF;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 rcv_error_trx_ids(rcv_error_trx_ids.COUNT + 1) := i;
484
485 END;
486 END LOOP; -- FOR i IN 1 .. l_rcv_pending_clubbed_trx_tbl.COUNT LOOP
487
488 -- If even a single transaction fails then return status should be Error.
489 IF (rcv_error_trx_ids.COUNT > 0) THEN
490 x_return_status := fnd_api.g_ret_sts_error;
491 ELSE
492 x_return_status := fnd_api.g_ret_sts_success;
493 END IF;
494
495 -- Find related failed records in case of clubbing.
496 IF (rcv_error_trx_ids.COUNT > 0) THEN
497 FOR i IN 1 .. l_rcv_pending_trx_tbl.COUNT LOOP
498 IF (l_rcv_pending_trx_tbl(i).clubbed_transaction_id IS NOT NULL) THEN
499 FOR k IN 1 .. rcv_error_trx_ids.COUNT LOOP
500 IF (l_rcv_pending_clubbed_trx_tbl(rcv_error_trx_ids(k)).transaction_id = l_rcv_pending_trx_tbl(i).transaction_id) THEN
501 UPDATE rcv_staging_table
502 SET status = 'CL_ERROR'
503 , LAST_UPDATE_DATE = sysdate
504 , LAST_UPDATED_BY = FND_GLOBAL.user_id
505 , LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
506 WHERE transaction_id = l_rcv_pending_trx_tbl(i).clubbed_transaction_id
507 AND team = g_team_name;
508 ELSIF (l_rcv_pending_clubbed_trx_tbl(rcv_error_trx_ids(k)).transaction_id = l_rcv_pending_trx_tbl(i).clubbed_transaction_id) THEN
509 UPDATE rcv_staging_table
510 SET status = 'CL_ERROR'
511 , LAST_UPDATE_DATE = sysdate
512 , LAST_UPDATED_BY = FND_GLOBAL.user_id
513 , LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
514 WHERE transaction_id = l_rcv_pending_trx_tbl(i).transaction_id
515 AND team = g_team_name;
516 END IF;
517 END LOOP; -- FORALL k IN 1 .. rcv_error_trx_ids.COUNT LOOP
518 END IF; -- IF (l_rcv_pending_trx_tbl(k).clubbed_transaction_id) IS NOT NULL) THEN
519 END LOOP; -- FOR i IN 1 .. l_rcv_pending_trx_tbl.COUNT LOOP
520 END IF; -- IF (rcv_error_trx_ids.COUNT > 0) THEN
521
522 /* vmutyala added the following code to find related success records in case of clubbing Bug 4670527*/
523 -- Find related success records in case of clubbing.
524 IF (rcv_success_trx_ids.COUNT > 0) THEN
525 FOR i IN 1 .. l_rcv_pending_trx_tbl.COUNT LOOP
526 IF (l_rcv_pending_trx_tbl(i).clubbed_transaction_id IS NOT NULL) THEN
527 FOR k IN 1 .. rcv_success_trx_ids.COUNT LOOP
528 IF (rcv_success_trx_ids(k) = l_rcv_pending_trx_tbl(i).transaction_id) THEN
529 rcv_success_trx_ids(rcv_success_trx_ids.COUNT + 1)
530 := l_rcv_pending_trx_tbl(i).clubbed_transaction_id;
531 ELSIF (rcv_success_trx_ids(k) = l_rcv_pending_trx_tbl(i).clubbed_transaction_id) THEN
532 rcv_success_trx_ids(rcv_success_trx_ids.COUNT + 1)
533 := l_rcv_pending_trx_tbl(i).transaction_id;
534 END IF;
535 END LOOP; -- FORALL k IN 1 .. rcv_success_trx_ids.COUNT LOOP
536 END IF; -- IF (l_rcv_pending_trx_tbl(k).clubbed_transaction_id) IS NOT NULL) THEN
537 END LOOP; -- FOR i IN 1 .. l_rcv_pending_trx_tbl.COUNT LOOP
538 END IF; -- IF (rcv_success_trx_ids.COUNT > 0) THEN
539
540 -- Update status in staging table.
541 FOR i IN 1 .. rcv_error_trx_ids.COUNT LOOP
542 l_error_trx_index := rcv_error_trx_ids(i);
543 UPDATE rcv_staging_table
544 SET status = l_rcv_pending_clubbed_trx_tbl(l_error_trx_index).error_status
545 , LAST_UPDATE_DATE = sysdate
546 , LAST_UPDATED_BY = FND_GLOBAL.user_id
547 , LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
548 WHERE transaction_id = l_rcv_pending_clubbed_trx_tbl(l_error_trx_index).transaction_id
549 AND team = g_team_name;
550 END LOOP;
551 /* vmutyala changed the following code to delete successful records which might have failed in previous attempts Bug 4670527*/
552 -- Delete successful transactions from staging table.
553
554
555 FORALL i IN 1 .. rcv_success_trx_ids.COUNT
556 DELETE rcv_staging_table
557 WHERE transaction_id = rcv_success_trx_ids(i)
558 AND team = g_team_name;
559
560 /* vmutyala added the following query to delete the 'PO' records which are not processed and whose supplier org is not a trading partner org
561 Bug 4670527 */
562 delete rcv_staging_table
563 where transaction_id IN
564 (select distinct(rst.transaction_id)
565 from hr_organization_information hoi, rcv_transactions rt, rcv_staging_table rst, mtl_parameters mp
566 where rt.SOURCE_DOCUMENT_CODE = 'PO'
567 and hoi.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
568 and hoi.ORG_INFORMATION3 = to_char(rt.vendor_id)
569 and hoi.ORG_INFORMATION4 = to_char(rt.vendor_site_id)
570 and rt.transaction_id = rst.transaction_id
571 and hoi.organization_id = mp.organization_id
572 and (mp.trading_partner_org_flag is NULL OR mp.trading_partner_org_flag = 'N')
573 and rst.status = 'PENDING'
574 and rst.team = g_team_name);
578 Bug 4670527 */
575 /* vmutyala added the following query to delete the 'RMA' records which are not processed and
576 a corresponding return reference id doesn't exist in OE_ORDER_LINES_ALL or even if it exists, a corresponding
577 replenishent so line id doesn't exist in JMF_SHIKYU_REPLENISHMENTS.
579 delete rcv_staging_table
580 where transaction_id IN
581 (select distinct(rst.transaction_id)
582 from OE_ORDER_LINES_ALL OOLA, rcv_transactions rt, rcv_staging_table rst
583 where rt.SOURCE_DOCUMENT_CODE = 'RMA'
584 and rt.OE_ORDER_LINE_ID = OOLA.LINE_ID
585 and (OOLA.REFERENCE_LINE_ID is NULL OR NOT EXISTS (select 1 from JMF_SHIKYU_REPLENISHMENTS
586 where REPLENISHMENT_SO_LINE_ID=
587 OOLA.REFERENCE_LINE_ID))
588 and rt.transaction_id = rst.transaction_id
589 and rst.status = 'PENDING'
590 and rst.team = g_team_name);
591
592 IF g_proc_level >= g_debug_level THEN
593 FND_LOG.string(g_proc_level
594 , G_MODULE_PREFIX || l_api_name || '.end'
595 , NULL);
596 END IF;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
601 END Process_Shikyu_Rcv_trx;
602
603
604 --========================================================================
605 -- PROCEDURE : Process_Osa_Receipt PUBLIC
606 -- PARAMETERS: p_api_version IN Standard in parameter API version
607 -- p_init_msg_list IN Standard in parameter message list
608 -- x_return_status OUT Stadard out parameter for return status
609 -- (Values E(Error), S(Success), U(Unexpected error))
610 -- x_msg_count OUT Stadard out parameter for number of messages
611 -- x_msg_data OUT Stadard out parameter for message
612 -- p_po_shipment_id IN Subcontracting PO shipment
613 -- p_quantity IN Received quantity
614 -- p_uom IN UOM of received quantity
615 -- p_transaction_type IN Transaction Type
616 -- p_project_id IN Project reference
617 -- p_task_id IN Task reference
618 -- COMMENT : This procedure is called after receipt of Outsourced Assembly
619 -- Item to perform WIP completion and Misc issue at Manufacturing
620 -- Partner organization. It does allocations if required.
621 --Following is logic:
622 --1. For receipts and positive corrections
623 -- Validate if all shikyu components are fully allocated and raise exception if not
624 --2. If Transaction Type is 'CORRECT'
625 -- If quantity positive then it is positive correction
626 -- Perform WIP Completion and back flush
627 -- Perform Misc Issue
628 -- If quantity is negative then it is negative correction.
629 -- Perform WIP Assembly Return and reverse back flush
630 -- Perform Misc Recceipt
631 --3. If Transaction Type is 'RECEIVE' or 'DELIVER'
632 -- Perform WIP Completion and back flush
633 -- Perform Misc Issue
634 --4. Update Interlock_status in JMF_SUBCONTRACT_ORDERS with either 'C' or 'E'
635 -- in case of success or error respectively.
636 --========================================================================
637 PROCEDURE Process_Osa_Receipt
638 ( p_api_version IN NUMBER
639 , p_init_msg_list IN VARCHAR2
640 , x_return_status OUT NOCOPY VARCHAR2
641 , x_msg_count OUT NOCOPY NUMBER
642 , x_msg_data OUT NOCOPY VARCHAR2
643 , p_po_shipment_id IN NUMBER
644 , p_quantity IN NUMBER
645 , p_uom IN VARCHAR2
646 , p_transaction_type IN VARCHAR2
647 , p_project_id IN NUMBER
648 , p_task_id IN NUMBER
649 , p_status IN OUT NOCOPY VARCHAR2
650 )
651 IS
652
653 l_api_name CONSTANT VARCHAR2(30) := 'Process_Osa_Receipt';
654 l_api_version CONSTANT NUMBER := 1.0;
655
656
657 l_osa_item_primary_uom VARCHAR2(3);
658 l_rcv_uom MTL_UNITS_OF_MEASURE_VL.UOM_CODE%Type;
659 l_osa_primary_uom_receipt_qty NUMBER;
660
661 l_osa_item_id NUMBER;
662 l_return_flag NUMBER;
663
664 -- custom exceptions
665 l_not_enough_replen_excep EXCEPTION;
666 l_not_allocated_completely EXCEPTION;
667
668
669
670 BEGIN
671 Init;
672 IF g_proc_level >= g_debug_level
673 THEN
674 FND_LOG.string(g_proc_level
675 , G_MODULE_PREFIX || l_api_name || '.begin'
676 , NULL);
677 END IF;
678
679 -- Start API initialization
680 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
681 FND_MSG_PUB.initialize;
682 END IF;
683
684 IF NOT FND_API.Compatible_API_Call( l_api_version
685 , p_api_version
686 , l_api_name
687 , G_PKG_NAME
688 )
689 THEN
690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
691 END IF;
692
693 x_return_status := FND_API.G_RET_STS_ERROR;
694
695 -- End API initialization
696
697 -- These conversions to primary UOM are done in caller program to avoid same in
698 -- called programs like Misc Issue/Return or WIP Completion/Return .
699
700 -- Get primary UOM of OSA item.
701 select JSO.OSA_ITEM_ID, JMF_SHIKYU_UTIL.Get_Primary_Uom_Code(JSO.OSA_ITEM_ID, JSO.OEM_ORGANIZATION_ID)
702 into l_osa_item_id, l_osa_item_primary_uom
703 from JMF_SUBCONTRACT_ORDERS JSO
704 where JSO.SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
705
706 -- Convert received quantity into primary UOM
707 IF (l_osa_item_primary_uom <> p_uom) THEN
711 , from_quantity => p_quantity
708 l_osa_primary_uom_receipt_qty := INV_CONVERT.inv_um_convert
709 ( item_id => l_osa_item_id
710 , precision => 5
712 , from_unit => p_uom
713 , to_unit => l_osa_item_primary_uom
714 , from_name => null
715 , to_name => null
716 );
717 ELSE
718 l_osa_primary_uom_receipt_qty := p_quantity;
719 END IF;
720
721
722 -- Call Allocation steps only for OSA Receipt and its positive corrections.
723 IF p_quantity > 0 THEN
724 Validate_And_Allocate( p_init_msg_list => p_init_msg_list
725 , x_return_status => x_return_status
726 , x_msg_count => x_msg_count
727 , x_msg_data => x_msg_data
728 , x_return_flag => l_return_flag
729 , p_po_shipment_id => p_po_shipment_id
730 , p_project_id => p_project_id
731 , p_task_id => p_task_id
732 );
733 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
734 raise FND_API.G_EXC_UNEXPECTED_ERROR;
735 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
736 IF l_return_flag = 0 THEN
737 raise FND_API.G_EXC_ERROR;
738 ELSIF l_return_flag = 1 THEN
739 raise l_not_enough_replen_excep;
740 ELSE
741 raise l_not_allocated_completely;
742 END IF; --IF l_return_flag = 0 THEN
743 END IF; -- IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
744
745 END IF; -- IF p_quantity > 0 THEN
746
747 /* ERROR status means that the program has failed in the previous run before or in Validate and allocate. If the issue
748 is resolved the program control reaches this point so unless we revert the status, the corresponding operations will not be
749 performed and the record from the staging table will be removed since return status is success. Reverting status will make sure
750 that the corresponding operations are performed */
751
752 IF p_status = 'ERROR' THEN
753 p_status := 'PENDING';
754 END IF;
755
756 -- Perform WIP Completion/Return and Misc Issue/Receipt based on transaction type.
757 IF p_transaction_type = 'CORRECT' THEN
758 -- If correction is positive
759 IF p_quantity > 0 THEN
760 IF p_status = 'PENDING' OR p_status = 'WC_ERROR' THEN
761 JMF_SHIKYU_INV_PVT.Process_WIP_Completion(p_subcontract_po_shipment_id => p_po_shipment_id
762 , p_osa_quantity => l_osa_primary_uom_receipt_qty
763 , p_uom => l_osa_item_primary_uom
764 , x_return_status => x_return_status);
765 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
766 p_status := 'WC_ERROR';
767 ELSE
768 p_status := 'PENDING'; --so that next operation can be performed.
769 END IF;
770 END IF;
771 IF p_status = 'PENDING' OR p_status = 'MI_ERROR' THEN
772 JMF_SHIKYU_INV_PVT.Process_Misc_Issue(p_subcontract_po_shipment_id => p_po_shipment_id
773 , p_osa_quantity => l_osa_primary_uom_receipt_qty
774 , p_uom => l_osa_item_primary_uom
775 , x_return_status => x_return_status);
776 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
777 p_status := 'MI_ERROR';
778 END IF;
779 END IF;
780
781 -- If correction is negative
782 ELSIF p_quantity < 0 THEN
783 IF p_status = 'PENDING' OR p_status = 'MR_ERROR' THEN
784 JMF_SHIKYU_INV_PVT.Process_Misc_rcpt(p_subcontract_po_shipment_id => p_po_shipment_id
785 , p_osa_quantity => ABS(l_osa_primary_uom_receipt_qty)
786 , p_uom => l_osa_item_primary_uom
787 , x_return_status => x_return_status);
788 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
789 p_status := 'MR_ERROR';
790 ELSE
791 p_status := 'PENDING'; --so that next operation can be performed.
792 END IF;
793 END IF;
794 IF p_status = 'PENDING' OR p_status = 'AR_ERROR' THEN
795 JMF_SHIKYU_INV_PVT.Process_WIP_Assy_Return(p_subcontract_po_shipment_id => p_po_shipment_id
796 , p_osa_quantity => ABS(l_osa_primary_uom_receipt_qty)
797 , p_uom => l_osa_item_primary_uom
798 , x_return_status => x_return_status);
799 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
800 p_status := 'AR_ERROR';
801 END IF;
802 END IF;
803 END IF; -- IF p_quantity > 0 THEN
804
805 -- If it is normal OSA receipt
806 ELSE
807 IF p_status = 'PENDING' OR p_status = 'WC_ERROR' THEN
808 JMF_SHIKYU_INV_PVT.Process_WIP_Completion(p_subcontract_po_shipment_id => p_po_shipment_id
809 , p_osa_quantity => l_osa_primary_uom_receipt_qty
810 , p_uom => l_osa_item_primary_uom
811 , x_return_status => x_return_status);
812 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
813 p_status := 'WC_ERROR';
814 ELSE
815 p_status := 'PENDING'; --so that next operation can be performed.
816 END IF;
817 END IF;
818 IF p_status = 'PENDING' OR p_status = 'MI_ERROR' THEN
819 JMF_SHIKYU_INV_PVT.Process_Misc_Issue(p_subcontract_po_shipment_id => p_po_shipment_id
820 , p_osa_quantity => l_osa_primary_uom_receipt_qty
821 , p_uom => l_osa_item_primary_uom
825 END IF;
822 , x_return_status => x_return_status);
823 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824 p_status := 'MI_ERROR';
826 END IF;
827 END IF; -- IF p_transaction_type = 'CORRECT' THEN
828
829 -- If any of the above activity is unsuccessful then throw exception
830 -- so that interlock SHIKYU status can be updated with Error.
831 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
832 raise FND_API.G_EXC_UNEXPECTED_ERROR;
833 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
834 raise FND_API.G_EXC_ERROR;
835 END IF;
836
837 -- Update Interlock Shikyu status as Completed in JMF_SUBCONTRACT_ORDERS
838 UPDATE JMF_SUBCONTRACT_ORDERS SET interlock_status = 'C'
839 , LAST_UPDATE_DATE = sysdate
840 , LAST_UPDATED_BY = FND_GLOBAL.user_id
841 , LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
842 WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
843
844 -- AND NVL(project_id, -1)= NVL(p_project_id, -1)
845 -- AND NVL(task_id, -1)= NVL(p_task_id, -1);
846
847
848 IF g_proc_level >= g_debug_level
849 THEN
850 FND_LOG.string(g_proc_level
851 , G_MODULE_PREFIX || l_api_name || '.end'
852 , NULL);
853 END IF;
854
855 EXCEPTION
856 WHEN l_not_enough_replen_excep THEN
857 FND_MSG_PUB.Count_And_Get
858 ( p_count => x_msg_count
859 , p_data => x_msg_data
860 );
861 x_return_status := FND_API.G_RET_STS_ERROR;
862
863
864 IF g_excep_level >= g_debug_level
865 THEN
866 FND_LOG.string(g_excep_level
867 , G_MODULE_PREFIX || l_api_name
868 , 'Exception - Subcontract Purchase Order Shipment: ' || p_po_shipment_id ||
869 ' - Not found enough Replenishment Sales Orders');
870 END IF;
871 WHEN l_not_allocated_completely THEN
872 FND_MSG_PUB.Count_And_Get
873 ( p_count => x_msg_count
874 , p_data => x_msg_data
875 );
876 x_return_status := FND_API.G_RET_STS_ERROR;
877
878
879 IF g_excep_level >= g_debug_level
880 THEN
881 FND_LOG.string(g_excep_level
882 , G_MODULE_PREFIX || l_api_name
883 , 'Exception - Subcontract Purchase Order Shipment: ' || p_po_shipment_id ||
884 ' - Could not allocate all received quantities.');
885 END IF;
886 WHEN NO_DATA_FOUND THEN
887 FND_MSG_PUB.Count_And_Get
888 ( p_count => x_msg_count
889 , p_data => x_msg_data
890 );
891
892 x_return_status := FND_API.G_RET_STS_ERROR;
893
894
895
896 IF g_excep_level >= g_debug_level
897 THEN
898 FND_LOG.string(g_excep_level
899 , G_MODULE_PREFIX || l_api_name || '.No Date Found'
900 , 'Exception - Subcontract Purchase Order Shipment: ' || p_po_shipment_id);
901 END IF;
902
903 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
904 FND_MSG_PUB.Count_And_Get
905 ( p_count => x_msg_count
906 , p_data => x_msg_data
907 );
908
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910
911
912 IF g_unexp_level >= g_debug_level
913 THEN
914 FND_LOG.string(g_unexp_level
915 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
916 , 'Exception');
917 END IF;
918 WHEN FND_API.G_EXC_ERROR THEN
919 FND_MSG_PUB.Count_And_Get
920 ( p_count => x_msg_count
921 , p_data => x_msg_data
922 );
923
924 x_return_status := FND_API.G_RET_STS_ERROR;
925
926
927 IF g_unexp_level >= g_debug_level
928 THEN
929 FND_LOG.string(g_unexp_level
930 , G_MODULE_PREFIX || l_api_name || '.Exception'
931 , 'Exception');
932 END IF;
933 WHEN OTHERS THEN
934 FND_MSG_PUB.Count_And_Get
935 ( p_count => x_msg_count
936 , p_data => x_msg_data
937 );
938
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940
941 IF g_unexp_level >= g_debug_level
942 THEN
943 FND_LOG.string(g_unexp_level
944 , G_MODULE_PREFIX || l_api_name || '.others_exception'
945 , 'Exception');
946 END IF;
947
948 END Process_Osa_Receipt;
949
950
951 --========================================================================
952 -- PROCEDURE : Process_Osa_Return PUBLIC
953 -- PARAMETERS: p_api_version IN Standard in parameter API version
954 -- p_init_msg_list IN Standard in parameter message list
955 -- x_return_status OUT Stadard out parameter for return status
956 -- (Values E(Error), S(Success), U(Unexpected error))
957 -- x_msg_count OUT Stadard out parameter for number of messages
958 -- x_msg_data OUT Stadard out parameter for message
959 -- p_po_shipment_id IN Subcontracting PO shipment
960 -- p_quantity IN Received quantity
961 -- p_uom IN UOM of received quantity
962 -- p_transaction_type IN Transaction Type
963 -- p_project_id IN Project reference
964 -- p_task_id IN Task reference
965 -- COMMENT : This procedure is called after return of Outsourced Assembly
966 -- Item to Supplier to perform WIP assembly return and Misc receipt at
967 -- Manufacturing Partner organization.
971 -- Perform WIP Assembly Return and reverse back flush
968 -- Following is logic:
969 -- 1. If Transaction Type is 'CORRECT'
970 -- If quantity positive then it is positive correction
972 -- Perform Misc Recceipt
973 -- If quantity is negative then it is negative correction.
974 -- validate if the shikyu components are fully allocated
975 -- and allocate if needed and raise exception if unable to allocate.
976 -- Perform WIP Completion and back flush
977 -- Perform Misc Issue
978 -- 2.If Transaction Type 'RETRUN TO VENDOR'
979 -- Perform WIP Assembly Return and reverse back flush
980 -- Perform Misc Recceipt
981 --========================================================================
982 PROCEDURE Process_Osa_Return
983 ( p_api_version IN NUMBER
984 , p_init_msg_list IN VARCHAR2
985 , x_return_status OUT NOCOPY VARCHAR2
986 , x_msg_count OUT NOCOPY NUMBER
987 , x_msg_data OUT NOCOPY VARCHAR2
988 , p_po_shipment_id IN NUMBER
989 , p_quantity IN NUMBER
990 , p_uom IN VARCHAR2
991 , p_transaction_type IN VARCHAR2
992 , p_project_id IN NUMBER
993 , p_task_id IN NUMBER
994 , p_status IN OUT NOCOPY VARCHAR2
995 )
996
997 IS
998
999 l_api_name CONSTANT VARCHAR2(30) := 'Process_Osa_Return';
1000 l_api_version CONSTANT NUMBER := 1.0;
1001
1002 BEGIN
1003 Init;
1004 IF g_proc_level >= g_debug_level
1005 THEN
1006 FND_LOG.string(g_proc_level
1007 , G_MODULE_PREFIX || l_api_name || '.begin'
1008 , NULL);
1009 END IF;
1010
1011 -- Start API initialization
1012 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1013 FND_MSG_PUB.initialize;
1014 END IF;
1015
1016 IF NOT FND_API.Compatible_API_Call( l_api_version
1017 , p_api_version
1018 , l_api_name
1019 , G_PKG_NAME
1020 )
1021 THEN
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 END IF;
1024
1025 x_return_status := FND_API.G_RET_STS_ERROR;
1026
1027 -- End API initialization
1028
1029 -- Perform WIP Completion/Return and Misc Issue/Receipt based on transaction type.
1030 IF p_transaction_type = 'CORRECT' THEN
1031
1032 -- If correction is positive
1033 IF p_quantity > 0 THEN
1034 IF p_status = 'PENDING' OR p_status = 'MR_ERROR' THEN
1035 JMF_SHIKYU_INV_PVT.Process_Misc_rcpt(p_subcontract_po_shipment_id => p_po_shipment_id
1036 , p_osa_quantity => p_quantity
1037 , p_uom => p_uom
1038 , x_return_status => x_return_status);
1039 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1040 p_status := 'MR_ERROR';
1041 ELSE
1042 p_status := 'PENDING'; --so that next operation can be performed.
1043 END IF;
1044 END IF;
1045 IF p_status = 'PENDING' OR p_status = 'AR_ERROR' THEN
1046 JMF_SHIKYU_INV_PVT.Process_WIP_Assy_Return(p_subcontract_po_shipment_id => p_po_shipment_id
1047 , p_osa_quantity => p_quantity
1048 , p_uom => p_uom
1049 , x_return_status => x_return_status);
1050 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1051 p_status := 'AR_ERROR';
1052 END IF;
1053 END IF;
1054
1055 -- If correction is negative
1056 ELSIF p_quantity < 0 THEN
1057 IF p_status = 'PENDING' OR p_status = 'WC_ERROR' THEN
1058 JMF_SHIKYU_INV_PVT.Process_WIP_Completion(p_subcontract_po_shipment_id => p_po_shipment_id
1059 , p_osa_quantity => ABS(p_quantity)
1060 , p_uom => p_uom
1061 , x_return_status => x_return_status);
1062 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1063 p_status := 'WC_ERROR';
1064 ELSE
1065 p_status := 'PENDING'; --so that next operation can be performed.
1066 END IF;
1067 END IF;
1068 IF p_status = 'PENDING' OR p_status = 'MI_ERROR' THEN
1069 JMF_SHIKYU_INV_PVT.Process_Misc_Issue(p_subcontract_po_shipment_id => p_po_shipment_id
1070 , p_osa_quantity => ABS(p_quantity)
1071 , p_uom => p_uom
1072 , x_return_status => x_return_status);
1073 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1074 p_status := 'MI_ERROR';
1075 END IF;
1076 END IF;
1077 END IF; -- IF p_quantity > 0 THEN
1078
1079 -- If it is normal OSA return
1080 ELSIF p_transaction_type = 'RETURN TO VENDOR' THEN
1081 IF p_status = 'PENDING' OR p_status = 'MR_ERROR' THEN
1082 JMF_SHIKYU_INV_PVT.Process_Misc_rcpt(p_subcontract_po_shipment_id => p_po_shipment_id
1083 , p_osa_quantity => p_quantity
1084 , p_uom => p_uom
1085 , x_return_status => x_return_status);
1086 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1087 p_status := 'MR_ERROR';
1088 ELSE
1089 p_status := 'PENDING'; --so that next operation can be performed.
1090 END IF;
1091 END IF;
1092 IF p_status = 'PENDING' OR p_status = 'AR_ERROR' THEN
1093 JMF_SHIKYU_INV_PVT.Process_WIP_Assy_Return(p_subcontract_po_shipment_id => p_po_shipment_id
1094 , p_osa_quantity => p_quantity
1095 , p_uom => p_uom
1099 END IF;
1096 , x_return_status => x_return_status);
1097 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1098 p_status := 'AR_ERROR';
1100 END IF;
1101 END IF; -- IF p_transaction_type = 'CORRECT' THEN
1102
1103 IF g_proc_level >= g_debug_level
1104 THEN
1105 FND_LOG.string(g_proc_level
1106 , G_MODULE_PREFIX || l_api_name || '.end'
1107 , NULL);
1108 END IF;
1109
1110 EXCEPTION
1111
1112 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1113 FND_MSG_PUB.Count_And_Get
1114 ( p_count => x_msg_count
1115 , p_data => x_msg_data
1116 );
1117
1118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1119 IF g_unexp_level >= g_debug_level
1120 THEN
1121 FND_LOG.string(g_unexp_level
1122 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1123 , 'Exception');
1124 END IF;
1125
1126 WHEN OTHERS THEN
1127 FND_MSG_PUB.Count_And_Get
1128 ( p_count => x_msg_count
1129 , p_data => x_msg_data
1130 );
1131
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1133 IF g_unexp_level >= g_debug_level
1134 THEN
1135 FND_LOG.string(g_unexp_level
1136 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1137 , 'Exception');
1138 END IF;
1139
1140 END Process_Osa_Return;
1141
1142 --========================================================================
1143 -- PROCEDURE : Process_Component_Return PUBLIC
1144 -- PARAMETERS: p_api_version IN Standard in parameter API version
1145 -- p_init_msg_list IN Standard in parameter message list
1146 -- x_return_status OUT Stadard out parameter for return status
1147 -- (Values E(Error), S(Success), U(Unexpected error))
1148 -- x_msg_count OUT Stadard out parameter for number of messages
1149 -- x_msg_data OUT Stadard out parameter for message
1150 -- p_rma_line_id IN RMA line id
1151 -- p_quantity IN Received quantity
1152 -- p_uom IN UOM of received quantity
1153 -- COMMENT : This procedure is called after SHIKYU RMA at Subcontracting
1154 -- Organizaiton. It initiates RTV transaction at MP Organization.
1155 -- It also deallocates returned quantities.
1156 --Follwing is logic:
1157 --1. Find all records eligible for SHIKYU RTV at MP site
1158 ---2. Perform SHIKYU RTV at MP site
1159 -- A. Enter records into rcv_headers_interface
1160 -- B. Enter records into rcv_transactions_interface
1161 -- C. Submit RVCTP ( Receiving Transaction Processor)
1162 -- D. Wait till concurrent request completes
1163 -- E. Confirm that RTV happened succefully. Received quantity against
1164 -- replenishment PO should be reduced by RTV quantity.
1165 --3. Following logic is part of RTY component return which will be handled
1166 -- by JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Replen_Excess_Qty
1167 --
1168 -- A. Deallocate returned quantity associated with Replenishment SO Line.
1169 -- if SHIKYU RMA is for all quantities(Shipped Qty = Returned Qty for Replenishment SO Line)
1170 -- then delete allocations.
1171 -- if partial quantity is returned then find corresponding Subcontracting PO in LIFO
1172 -- manner based on NeedByDate.
1173 -- then reduce allocation
1174 -- if there are multiple Subcontracting PO with same NeedByDate then pick
1175 -- po no/Line no/Shipment in descending order.
1176 -- then reduce allocation
1177 --
1178 -- B. Reallocate to Subcontracting PO from available replenishments,
1179 -- If allocable replenishment is not present then create a new one and allocate.
1180 --========================================================================
1181 PROCEDURE Process_Component_Return
1182 ( p_api_version IN NUMBER
1183 , p_init_msg_list IN VARCHAR2
1184 , x_return_status OUT NOCOPY VARCHAR2
1185 , x_msg_count OUT NOCOPY NUMBER
1186 , x_msg_data OUT NOCOPY VARCHAR2
1187 , p_rma_line_id IN NUMBER
1188 , p_quantity IN NUMBER
1189 , p_uom IN VARCHAR2
1190 , p_status IN OUT NOCOPY VARCHAR2
1191 )
1192 IS
1193
1194 l_api_name CONSTANT VARCHAR2(30) := 'Process_Component_Return';
1195 l_api_version CONSTANT NUMBER := 1.0;
1196
1197 l_returned_qty NUMBER;
1198 l_replen_po_header_id NUMBER;
1199 l_replen_po_line_id NUMBER;
1200 l_replen_po_shipment_id NUMBER;
1201 l_replen_so_line_id NUMBER;
1202
1203 l_tp_organization_id NUMBER;
1204 l_available_quantity NUMBER;
1205 l_tolerable_quantity NUMBER;
1206 l_unit_of_measure MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%Type;
1207 l_shikyu_component_id NUMBER;
1208 l_return_number NUMBER;
1209 l_group_id NUMBER;
1210 l_header_interface_id NUMBER;
1211 l_allocable_primary_uom_qty NUMBER;
1212 l_shipped_primary_uom_qty NUMBER;
1213 l_shikyu_primary_uom JMF_SHIKYU_REPLENISHMENTS.PRIMARY_UOM%Type;
1214 l_shipped_qty_uom OE_ORDER_LINES_ALL.ORDER_QUANTITY_UOM%Type;
1215 l_shikyu_unit_of_measure MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%Type;
1216
1217 l_pre_qty_received NUMBER;
1218 l_post_qty_received NUMBER;
1219
1220 l_rtv_unsuccessful EXCEPTION;
1221
1222 l_workers JMF_SHIKYU_UTIL.g_request_tbl_type;
1223
1224 l_transaction_type RCV_TRANSACTIONS.TRANSACTION_TYPE%Type;
1225 l_parent_transaction_id NUMBER;
1229
1226 l_subinventory RCV_TRANSACTIONS.SUBINVENTORY%Type;
1227 l_locator_id NUMBER;
1228 l_project_id NUMBER;
1230 l_returned_qty_parent_txn_uom NUMBER;
1231
1232 BEGIN
1233 Init;
1234 IF g_proc_level >= g_debug_level
1235 THEN
1236 FND_LOG.string(g_proc_level
1237 , G_MODULE_PREFIX || l_api_name || '.begin'
1238 , NULL);
1239 END IF;
1240
1241 -- Start API initialization
1242 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1243 FND_MSG_PUB.initialize;
1244 END IF;
1245
1246 IF NOT FND_API.Compatible_API_Call( l_api_version
1247 , p_api_version
1248 , l_api_name
1249 , G_PKG_NAME
1250 )
1251 THEN
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253 END IF;
1254
1255 x_return_status := FND_API.G_RET_STS_ERROR;
1256
1257 -- End API initialization
1258
1259 -- get the replenishment SO line id referenced by the rma line
1260
1261 SELECT JSR.REPLENISHMENT_SO_LINE_ID
1262 INTO l_replen_so_line_id
1263 FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
1264 WHERE OOLA.LINE_ID = p_rma_line_id
1265 AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID;
1266
1267
1268 -- get returned quantity and Replenish PO details against replenishment sales order line
1269 l_returned_qty := p_quantity;
1270 l_returned_qty_parent_txn_uom := l_returned_qty;
1271 select jsr.REPLENISHMENT_PO_HEADER_ID,jsr.REPLENISHMENT_PO_LINE_ID, jsr.REPLENISHMENT_PO_SHIPMENT_ID,
1272 jsr.SHIKYU_COMPONENT_ID, jsr.TP_ORGANIZATION_ID, jsr.PRIMARY_UOM,
1273 jsr.ALLOCABLE_PRIMARY_UOM_QUANTITY, oola.SHIPPED_QUANTITY,
1274 oola.ORDER_QUANTITY_UOM
1275 into l_replen_po_header_id, l_replen_po_line_id, l_replen_po_shipment_id,
1276 l_shikyu_component_id, l_tp_organization_id,
1277 l_shikyu_primary_uom, l_allocable_primary_uom_qty,
1278 l_shipped_primary_uom_qty, l_shipped_qty_uom
1279 from JMF_SHIKYU_REPLENISHMENTS jsr, OE_ORDER_LINES_ALL oola
1280 where jsr.REPLENISHMENT_SO_LINE_ID = l_replen_so_line_id
1281 and jsr.REPLENISHMENT_SO_LINE_ID = oola.line_id;
1282
1283 -- If there is no return quantity against Replenishment SO then return.
1284 IF l_returned_qty = 0 THEN
1285 RETURN;
1286 END IF;
1287
1288 -- Perform RTV at MP Organization Replenishment_po_shipment_id
1289
1290 -- get received quantity against Replenish Purchase Shipment
1291 select QUANTITY_RECEIVED
1292 into l_pre_qty_received
1293 from PO_LINE_LOCATIONS_ALL
1294 where LINE_LOCATION_ID = l_replen_po_shipment_id;
1295
1296 -- get UnitOfMeasure from UomCode for Shikyu component
1297 select UNIT_OF_MEASURE
1298 into l_shikyu_unit_of_measure
1299 from MTL_UNITS_OF_MEASURE_VL
1300 where UOM_CODE = l_shikyu_primary_uom;
1301
1302 IF p_status <> 'DA_ERROR' THEN
1303
1304 SELECT rt.transaction_type, rt.transaction_id, rt.subinventory, rt.locator_id, rt.project_id
1305 INTO l_transaction_type, l_parent_transaction_id, l_subinventory, l_locator_id, l_project_id
1306 FROM rcv_transactions rt,
1307 rcv_shipment_lines rsl
1308 WHERE rt.organization_id = l_tp_organization_id
1309 AND rt.po_header_id = l_replen_po_header_id
1310 AND rt.po_line_id = l_replen_po_line_id
1311 AND rt.po_line_location_id = l_replen_po_shipment_id
1312 AND rsl.item_id = l_shikyu_component_id
1313 AND rt.SOURCE_DOCUMENT_CODE ='PO'
1314 AND rt.replenish_order_line_id = l_replen_so_line_id
1315 AND
1316 (
1317 (
1318 RT.TRANSACTION_TYPE IN ('RECEIVE', 'TRANSFER', 'ACCEPT' , 'REJECT', 'MATCH')
1319 AND EXISTS
1320 (
1321 SELECT
1322 'POSTIVE RCV SUPPLY'
1323 FROM RCV_SUPPLY RS
1324 WHERE RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
1325 AND RS.TO_ORG_PRIMARY_QUANTITY >
1326 (
1327 SELECT
1328 NVL(SUM(RTI.PRIMARY_QUANTITY),0)
1329 FROM RCV_TRANSACTIONS_INTERFACE RTI
1330 WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
1331 AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
1332 AND RTI.PROCESSING_STATUS_CODE = 'PENDING'
1333 )
1334 )
1335 )
1336 OR
1337 (
1338 RT.TRANSACTION_TYPE = 'DELIVER'
1339 AND RT.SOURCE_DOCUMENT_CODE <> 'RMA'
1340 )
1341 )
1342 AND NOT EXISTS
1343 (
1344 SELECT
1345 'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
1346 FROM PO_LINE_LOCATIONS_ALL PLL
1347 WHERE PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
1348 AND
1349 (
1350 NVL(PLL.CANCEL_FLAG,'N') = 'Y'
1351 OR NVL(PLL.CLOSED_CODE,'OPEN') = 'FINALLY CLOSED'
1352 OR NVL(PLL.APPROVED_FLAG,'N') <> 'Y'
1353 OR NVL(PLL.MATCHING_BASIS,'QUANTITY') = 'AMOUNT'
1354 OR PLL.PAYMENT_TYPE IS NOT NULL
1355 )
1356 )
1357 AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID;
1358
1359
1360 RCV_QUANTITIES_S.get_available_quantity(
1361 p_transaction_type => 'RETURN TO VENDOR'
1362 ,p_parent_id => l_parent_transaction_id
1363 ,p_receipt_source_code => 'VENDOR'
1364 ,p_parent_transaction_type => l_transaction_type
1365 ,p_grand_parent_id => 0
1366 ,p_correction_type => 'NEGATIVE'
1367 ,p_available_quantity => l_available_quantity
1368 ,p_tolerable_quantity => l_tolerable_quantity
1372 IF l_unit_of_measure <> l_shikyu_unit_of_measure THEN
1369 ,p_unit_of_measure => l_unit_of_measure);
1370
1371 -- convert available_quantity into primary uom quantity
1373 l_returned_qty_parent_txn_uom := INV_CONVERT.inv_um_convert
1374 ( item_id => l_shikyu_component_id
1375 , precision => 5
1376 , from_quantity => l_returned_qty
1377 , from_unit => null
1378 , to_unit => null
1379 , from_name => l_shikyu_unit_of_measure
1380 , to_name => l_unit_of_measure
1381 );
1382 END IF;
1383
1384 IF l_returned_qty_parent_txn_uom > l_available_quantity THEN
1385 raise l_rtv_unsuccessful;
1386 END IF;
1387
1388 -- insert into RCV header interface
1389 JMF_SHIKYU_RCV_PVT.process_rcv_header
1390 ( p_vendor_id => NULL --l_rtv_details.vendor_id
1391 , p_vendor_site_id => NULL
1392 , p_ship_to_org_id => NULL
1393 , x_rcv_header_id => l_header_interface_id
1394 , x_group_id => l_group_id);
1395
1396 -- insert into RCV transaction interface
1397 JMF_SHIKYU_RCV_PVT.process_rcv_trx
1398 ( p_rcv_header_id => l_header_interface_id
1399 , p_group_id => l_group_id
1400 , p_quantity => l_returned_qty_parent_txn_uom
1401 , p_unit_of_measure => l_unit_of_measure
1402 , p_po_header_id => l_replen_po_header_id
1403 , p_po_line_id => l_replen_po_line_id
1404 , p_po_line_location_id => l_replen_po_shipment_id
1405 , p_transaction_type => 'RETURN TO VENDOR'
1406 , p_parent_transaction_id=> l_parent_transaction_id
1407 , p_from_subinventory => l_subinventory
1408 , p_from_locator_id => l_locator_id
1409 , p_project_id => l_project_id) ;
1410
1411 -- submit concurrent request for Receiving Transaction Processor
1412 l_return_number := fnd_request.submit_request(
1413 application => 'PO'
1414 , program => 'RVCTP'
1415 , description => 'Receiving Transaction Processor'
1416 , start_time => SYSDATE
1417 , sub_request => FALSE
1418 , argument1 => 'IMMEDIATE'
1419 , argument2 => l_group_id
1420 );
1421
1422 COMMIT;
1423
1424
1425
1426 -- Wait till RTV completes to perform deallocation.
1427 LOOP
1428 IF JMF_SHIKYU_UTIL.Has_worker_completed(l_return_number) THEN
1429 EXIT;
1430 ELSE
1431 DBMS_LOCK.sleep(JMF_SHIKYU_UTIL.G_SLEEP_TIME);
1432 END IF;
1433 END LOOP;
1434
1435 -- Again search for received quantity against Replenish Po Shipment
1436 select QUANTITY_RECEIVED
1437 into l_post_qty_received
1438 from PO_LINE_LOCATIONS_ALL
1439 where LINE_LOCATION_ID = l_replen_po_shipment_id;
1440
1441 -- Check if return happened successfully
1442 IF l_returned_qty_parent_txn_uom <> (l_pre_qty_received - l_post_qty_received) THEN
1443 raise l_rtv_unsuccessful;
1444 END IF;
1445
1446 END IF; --IF p_status <> 'DA_ERROR' THEN
1447 -- Convert shipped_qty to primary uom if required
1448 IF l_shikyu_primary_uom <> l_shipped_qty_uom THEN
1449 l_shipped_primary_uom_qty := INV_CONVERT.inv_um_convert
1450 ( item_id => l_shikyu_component_id
1451 , precision => 5
1452 , from_quantity => l_shipped_primary_uom_qty
1453 , from_unit => l_shipped_qty_uom
1454 , to_unit => l_shikyu_primary_uom
1455 , from_name => null
1456 , to_name => null
1457 );
1458 END IF;
1459
1460 -- Consider case of under Shipment when allocable quantity is more
1461 -- than shipped quantity. Returned quantity will be increased by
1462 -- allocable quantity - shipped quantity.
1463 IF l_allocable_primary_uom_qty > l_shipped_primary_uom_qty THEN
1464 l_returned_qty := l_returned_qty +
1465 (l_allocable_primary_uom_qty - l_shipped_primary_uom_qty );
1466 END IF;
1467 -- Deallocate based on LIFO order of Need By Date of the Subcontracting
1468 -- Orders already allocated to the current Replenishment SO Line
1469 JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Replen_Excess_Qty
1470 ( p_api_version => 1.0
1471 , p_init_msg_list => p_init_msg_list
1472 , x_return_status => x_return_status
1473 , x_msg_count => x_msg_count
1474 , x_msg_data => x_msg_data
1475 , p_replen_order_line_id => l_replen_so_line_id
1476 , p_excess_qty => l_returned_qty
1477 );
1478 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1479 p_status := 'DA_ERROR';
1480 END IF;
1481
1482 IF g_proc_level >= g_debug_level
1483 THEN
1484 FND_LOG.string(g_proc_level
1485 , G_MODULE_PREFIX || l_api_name || '.end'
1486 , NULL);
1487 END IF;
1488
1489 EXCEPTION
1490 WHEN l_rtv_unsuccessful THEN
1491 FND_MSG_PUB.Count_And_Get
1492 ( p_count => x_msg_count
1493 , p_data => x_msg_data
1494 );
1495
1496 x_return_status := FND_API.G_RET_STS_ERROR;
1497 IF g_excep_level >= g_debug_level
1498 THEN
1499 FND_LOG.string(g_excep_level
1500 , G_MODULE_PREFIX || l_api_name || '.RTV_Failed'
1501 , 'Exception - RTV Failed for Replenishment Sales Order Id: '
1502 || l_replen_so_line_id);
1503 END IF;
1504 WHEN NO_DATA_FOUND THEN
1508 );
1505 FND_MSG_PUB.Count_And_Get
1506 ( p_count => x_msg_count
1507 , p_data => x_msg_data
1509
1510 x_return_status := FND_API.G_RET_STS_ERROR;
1511 IF g_excep_level >= g_debug_level
1512 THEN
1513 FND_LOG.string(g_excep_level
1514 , G_MODULE_PREFIX || l_api_name || '.No_Date_Found'
1515 , 'Exception - Replenishment Sales Order Line: ' || l_replen_so_line_id);
1516 END IF;
1517
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 FND_MSG_PUB.Count_And_Get
1520 ( p_count => x_msg_count
1521 , p_data => x_msg_data
1522 );
1523
1524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1525 IF g_unexp_level >= g_debug_level
1526 THEN
1527 FND_LOG.string(g_unexp_level
1528 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1529 , 'Exception');
1530 END IF;
1531 WHEN OTHERS THEN
1532 FND_MSG_PUB.Count_And_Get
1533 ( p_count => x_msg_count
1534 , p_data => x_msg_data
1535 );
1536
1537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1538 IF g_unexp_level >= g_debug_level
1539 THEN
1540 FND_LOG.string(g_unexp_level
1541 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1542 , 'Exception');
1543 END IF;
1544 END Process_Component_Return;
1545
1546 /* Private Helper Functions/Procedures */
1547 --========================================================================
1548 -- PROCEDURE : Validate_And_Allocate PRIVATE
1549 -- PARAMETERS: p_init_msg_list IN Standard in parameter message list
1550 -- x_return_flag OUT To indicate whether the required number of
1551 -- replenishments are available or not
1552 -- x_return_status OUT Stadard out parameter for return status
1553 -- (Values E(Error), S(Success), U(Unexpected error))
1554 -- x_msg_count OUT Stadard out parameter for number of messages
1555 -- x_msg_data OUT Stadard out parameter for message
1556 -- (Values E(Error), S(Success), U(Unexpected error))
1557 -- p_po_shipment_id IN Subcontracting PO shipment
1558 -- p_project_id IN project reference
1559 -- p_task_id IN task reference
1560 --
1561 -- COMMENT : This procedure is called from OSA Receipt and OSA Return api
1562 -- before performing WIP completion and Misc issue at Manufacturing
1563 -- Partner organization. It does allocations if required. It raises exception
1564 -- if sufficient allocations are not available.
1565 --Following is logic:
1566 --1. If Subcontracting PO is allocable
1567 -- IF Required quantity of SHIKYU Component at TP site for this subcontract PO >
1568 -- Allocated quantity of SHIKYU Component against that Subcontracting Order
1569 -- Then
1570 -- Find available Replenishment SO for allocation
1571 -- If Enough replenishment SO are not available then error out.
1572 -- perform allocation for SHIKYU Component quantities against OSA Receipt quantity.
1573 --========================================================================
1574 PROCEDURE Validate_And_Allocate
1575 ( p_init_msg_list IN VARCHAR2
1576 , x_return_status OUT NOCOPY VARCHAR2
1577 , x_msg_count OUT NOCOPY NUMBER
1578 , x_msg_data OUT NOCOPY VARCHAR2
1579 , x_return_flag OUT NOCOPY NUMBER
1580 , p_po_shipment_id IN NUMBER
1581 , p_project_id IN NUMBER
1582 , p_task_id IN NUMBER
1583 )
1584 IS
1585
1586 l_remaining_qty NUMBER;
1587 l_actual_qty_allocated NUMBER;
1588 l_shikyu_qty_to_allocate NUMBER;
1589 l_total_allocated_Qty NUMBER;
1590 l_required_quantity NUMBER;
1591 l_allocable_flag JMF_SHIKYU_ALLOCATIONS_V.ALLOCATABLE_FLAG%Type;
1592 l_available_replen_so_qty_tbl JMF_SHIKYU_ALLOCATION_PVT.g_replen_so_qty_tbl_type;
1593 l_adjustment_total NUMBER;
1594 l_interlock_status VARCHAR2(1);
1595 -- custom exceptions
1596 l_not_enough_replen_excep EXCEPTION;
1597 l_not_allocated_completely EXCEPTION;
1598
1599 -- cursor definitions
1600 cursor c_subcontract_po_shikyu_comp is
1601 select jsc.SHIKYU_COMPONENT_ID shikyu_component_id, jso.TP_ORGANIZATION_ID tp_organization_id, jsc.PRIMARY_UOM primary_uom
1602 from JMF_SHIKYU_COMPONENTS jsc, JMF_SUBCONTRACT_ORDERS jso
1603 where jsc.SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id
1604 and jso.subcontract_po_shipment_id = jsc.SUBCONTRACT_PO_SHIPMENT_ID;
1605
1606 BEGIN
1607
1608 x_return_status := FND_API.G_RET_STS_ERROR;
1609 x_return_flag := 0;
1610
1611 SELECT interlock_status
1612 into l_interlock_status
1613 FROM JMF_SUBCONTRACT_ORDERS
1614 WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
1615
1616 -- AND NVL(project_id, -1)= NVL(p_project_id, -1)
1617 -- AND NVL(task_id, -1)= NVL(p_task_id, -1);
1618
1619 IF l_interlock_status = 'C' THEN
1620 x_return_status := FND_API.G_RET_STS_SUCCESS;
1621 ELSE
1622 -- Check if allocation is needed against OSA receipt
1623 select ALLOCATABLE_FLAG
1624 into l_allocable_flag
1625 from JMF_SHIKYU_ALLOCATIONS_V
1626 where SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
1627
1628
1629 -- Call Allocations API to allcoate if all received quantities are not allocated
1630 IF l_allocable_flag = 'Y' THEN
1631 -- Allocate each SHIKYU component of OSA item
1632 FOR l_subcontract_po_shikyu_comp IN c_subcontract_po_shikyu_comp
1633 LOOP
1634
1635 -- Get total allocated quantity for SHIKYU Component.
1636 l_total_allocated_Qty := JMF_SHIKYU_UTIL.Get_Subcontract_Allocated_Qty
1640 -- Get required quantity of shikyu component.
1637 (p_po_shipment_id
1638 ,l_subcontract_po_shikyu_comp.shikyu_component_id);
1639
1641
1642
1643 l_required_quantity := JMF_SHIKYU_WIP_PVT.get_component_quantity
1644 ( p_item_id => l_subcontract_po_shikyu_comp.shikyu_component_id
1645 , p_organization_id => l_subcontract_po_shikyu_comp.tp_organization_id
1646 , p_subcontract_po_shipment_id => p_po_shipment_id
1647 );
1648
1649 /*vmutyala added the following to take into account the consumption adjustments made after receiving partial
1650 quantity. The subcontract PO is fully allocated if l_required_quantity - l_total_allocated_Qty = total adjustments made */
1651 l_adjustment_total := 0;
1652
1653 SELECT nvl(SUM(adjustment), 0)
1654 INTO l_adjustment_total
1655 FROM jmf_shikyu_adjustments
1656 WHERE subcontract_po_shipment_id = p_po_shipment_id
1657 AND shikyu_component_id = l_subcontract_po_shikyu_comp.shikyu_component_id
1658 AND request_id IS NOT NULL;
1659
1660 l_required_quantity := l_required_quantity - l_adjustment_total;
1661
1662
1663 IF (l_required_quantity > l_total_allocated_Qty) THEN
1664
1665 -- Calculate total SHIKYU comsumption by received OSA items
1666 l_shikyu_qty_to_allocate := l_required_quantity - l_total_allocated_Qty;
1667
1668
1669 -- Find available Replenishment Sales Orders for allocation
1670 JMF_SHIKYU_ALLOCATION_PVT.Get_Available_Replenishment_So
1671 ( p_api_version => 1.0
1672 , p_init_msg_list => p_init_msg_list
1673 , x_return_status => x_return_status
1674 , x_msg_count => x_msg_count
1675 , x_msg_data => x_msg_data
1676 , p_subcontract_po_shipment_id => p_po_shipment_id
1677 , p_component_id => l_subcontract_po_shikyu_comp.shikyu_component_id
1678 , p_qty => l_shikyu_qty_to_allocate
1679 , p_include_additional_supply => 'N'
1680 , p_arrived_so_lines_only => 'Y'
1681 , x_available_replen_tbl => l_available_replen_so_qty_tbl
1682 , x_remaining_qty => l_remaining_qty
1683 );
1684
1685 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1686 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1687 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1688 raise FND_API.G_EXC_ERROR;
1689 -- Raise an exception if there is not enough existing replenishments
1690 ELSIF l_remaining_qty > 0 THEN
1691 raise l_not_enough_replen_excep;
1692 -- Allocate if there is enough existing replenishments
1693 END IF;
1694
1695 -- Allocate SHIKYU components from available Replenishments.
1696 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1697 JMF_SHIKYU_ALLOCATION_PVT.Allocate_Quantity
1698 ( p_api_version => 1.0
1699 , p_init_msg_list => p_init_msg_list
1700 , x_return_status => x_return_status
1701 , x_msg_count => x_msg_count
1702 , x_msg_data => x_msg_data
1703 , p_subcontract_po_shipment_id => p_po_shipment_id
1704 , p_component_id => l_subcontract_po_shikyu_comp.shikyu_component_id
1705 , p_qty_to_allocate => l_shikyu_qty_to_allocate
1706 , p_available_replen_tbl => l_available_replen_so_qty_tbl
1707 -- to store the actual qty being returned as OUT parameter
1708 , x_qty_allocated => l_actual_qty_allocated
1709 );
1710
1711 -- Raise an exception if actual quantity allocated is not same as requested quantity.
1712 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1713 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1714 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1715 raise FND_API.G_EXC_ERROR;
1716 ELSIF l_actual_qty_allocated < l_shikyu_qty_to_allocate THEN
1717 raise l_not_allocated_completely;
1718 END IF; -- IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1719 END IF; --IF x_return_status = success THEN
1720 ELSIF l_required_quantity = l_total_allocated_Qty THEN
1721 x_return_status := FND_API.G_RET_STS_SUCCESS;
1722 END IF; --IF (l_required_quantity > l_total_allocated_Qty) THEN
1723
1724
1725 END LOOP; -- FOR l_subcontract_po_shikyu_comp IN c_subcontract_po_shikyu_comp
1726 ELSIF l_allocable_flag = 'N' THEN
1727 x_return_status := FND_API.G_RET_STS_SUCCESS;
1728 END IF; -- IF l_allocable_flag = 'Y' THEN
1729 END IF; -- IF l_interlock_status = 'C' THEN
1730
1731 EXCEPTION
1732 WHEN l_not_enough_replen_excep THEN
1733
1734
1735 x_return_flag := 1;
1736 x_return_status := FND_API.G_RET_STS_ERROR;
1737 WHEN l_not_allocated_completely THEN
1738
1739 x_return_flag := 2;
1740 x_return_status := FND_API.G_RET_STS_ERROR;
1741 WHEN OTHERS THEN
1742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1743 END Validate_And_Allocate;
1744 --=============================================================================
1745 -- PROCEDURE NAME: Init
1746 -- TYPE : PRIVATE
1747 -- PARAMETERS : None
1748 -- DESCRIPTION : Initializes Global Variables.
1749 -- EXCEPTIONS : None
1750 -- CHANGE HISTORY: 23-MAY-05 VCHU Created.
1751 --=============================================================================
1752
1753 PROCEDURE Init
1754 IS
1755 BEGIN
1756
1757 -- initializes the global variables for FND Log
1758
1759 IF g_proc_level IS NULL
1760 THEN
1761 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
1762 END IF; /* IF g_proc_level IS NULL */
1763
1764 IF g_unexp_level IS NULL
1765 THEN
1766 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
1767 END IF; /* IF g_unexp_level IS NULL */
1768
1769 IF g_excep_level IS NULL
1770 THEN
1771 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
1772 END IF; /* IF g_excep_level IS NULL */
1773
1774 IF g_statement_level IS NULL
1775 THEN
1776 g_statement_level := FND_LOG.LEVEL_STATEMENT;
1777 END IF; /* IF g_statement_level IS NULL */
1778
1779 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1780
1781
1782 END Init;
1783
1784 END JMF_PROCESS_SHIKYU_RCV_TRX_PVT;