1 PACKAGE BODY JMF_PROCESS_SHIKYU_RCV_TRX_PVT AS
2 -- $Header: JMFVSKTB.pls 120.16 2010/08/12 11:38:39 skolluku ship $
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
350 where transaction_id = l_rcv_pending_trx_tbl(i).transaction_id);
351
352 /*vmutyala added the following code.
353 For independent correction transactions to set the process type depending on parent transaction type
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
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
467 -- transaction list.
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);
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.
578 Bug 4670527 */
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
708 l_osa_primary_uom_receipt_qty := INV_CONVERT.inv_um_convert
709 ( item_id => l_osa_item_id
710 , precision => 5
711 , from_quantity => p_quantity
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
822 , x_return_status => x_return_status);
823 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824 p_status := 'MI_ERROR';
825 END IF;
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.
968 -- Following is logic:
969 -- 1. If Transaction Type is 'CORRECT'
970 -- If quantity positive then it is positive correction
971 -- Perform WIP Assembly Return and reverse back flush
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
1096 , x_return_status => x_return_status);
1097 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1098 p_status := 'AR_ERROR';
1099 END IF;
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;
1226 l_subinventory RCV_TRANSACTIONS.SUBINVENTORY%Type;
1227 l_locator_id NUMBER;
1228 l_project_id NUMBER;
1229
1230 l_returned_qty_parent_txn_uom NUMBER;
1231 -- Bug 9883090
1232 l_vendor_id NUMBER;
1233 l_po_type_lookup_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%Type;
1234 l_open_flag VARCHAR2(2);
1235 l_return_code VARCHAR2(40);
1236 l_return_status BOOLEAN;
1237
1238 BEGIN
1239 Init;
1240 IF g_proc_level >= g_debug_level
1241 THEN
1242 FND_LOG.string(g_proc_level
1243 , G_MODULE_PREFIX || l_api_name || '.begin'
1244 , NULL);
1245 END IF;
1246
1247 -- Start API initialization
1248 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1249 FND_MSG_PUB.initialize;
1250 END IF;
1251
1252 IF NOT FND_API.Compatible_API_Call( l_api_version
1253 , p_api_version
1254 , l_api_name
1255 , G_PKG_NAME
1256 )
1257 THEN
1258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1259 END IF;
1260
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262
1263 -- End API initialization
1264
1265 -- get the replenishment SO line id referenced by the rma line
1266
1267 SELECT JSR.REPLENISHMENT_SO_LINE_ID
1268 INTO l_replen_so_line_id
1269 FROM OE_ORDER_LINES_ALL OOLA , JMF_SHIKYU_REPLENISHMENTS JSR
1270 WHERE OOLA.LINE_ID = p_rma_line_id
1271 AND OOLA.REFERENCE_LINE_ID = JSR.REPLENISHMENT_SO_LINE_ID;
1272
1273
1274 -- get returned quantity and Replenish PO details against replenishment sales order line
1275 l_returned_qty := p_quantity;
1276 l_returned_qty_parent_txn_uom := l_returned_qty;
1277 select jsr.REPLENISHMENT_PO_HEADER_ID,jsr.REPLENISHMENT_PO_LINE_ID, jsr.REPLENISHMENT_PO_SHIPMENT_ID,
1278 jsr.SHIKYU_COMPONENT_ID, jsr.TP_ORGANIZATION_ID, jsr.PRIMARY_UOM,
1279 jsr.ALLOCABLE_PRIMARY_UOM_QUANTITY, oola.SHIPPED_QUANTITY,
1280 oola.ORDER_QUANTITY_UOM
1281 into l_replen_po_header_id, l_replen_po_line_id, l_replen_po_shipment_id,
1282 l_shikyu_component_id, l_tp_organization_id,
1283 l_shikyu_primary_uom, l_allocable_primary_uom_qty,
1284 l_shipped_primary_uom_qty, l_shipped_qty_uom
1285 from JMF_SHIKYU_REPLENISHMENTS jsr, OE_ORDER_LINES_ALL oola
1286 where jsr.REPLENISHMENT_SO_LINE_ID = l_replen_so_line_id
1287 and jsr.REPLENISHMENT_SO_LINE_ID = oola.line_id;
1288
1289 -- If there is no return quantity against Replenishment SO then return.
1290 IF l_returned_qty = 0 THEN
1291 RETURN;
1292 END IF;
1293
1294 -- Perform RTV at MP Organization Replenishment_po_shipment_id
1295
1296 -- get received quantity against Replenish Purchase Shipment
1297 select QUANTITY_RECEIVED
1298 into l_pre_qty_received
1299 from PO_LINE_LOCATIONS_ALL
1300 where LINE_LOCATION_ID = l_replen_po_shipment_id;
1301
1302 -- get UnitOfMeasure from UomCode for Shikyu component
1303 select UNIT_OF_MEASURE
1304 into l_shikyu_unit_of_measure
1305 from MTL_UNITS_OF_MEASURE_VL
1306 where UOM_CODE = l_shikyu_primary_uom;
1307
1308 IF p_status <> 'DA_ERROR' THEN
1309
1310 SELECT rt.transaction_type, rt.transaction_id, rt.subinventory, rt.locator_id, rt.project_id
1311 INTO l_transaction_type, l_parent_transaction_id, l_subinventory, l_locator_id, l_project_id
1312 FROM rcv_transactions rt,
1313 rcv_shipment_lines rsl
1314 WHERE rt.organization_id = l_tp_organization_id
1315 AND rt.po_header_id = l_replen_po_header_id
1316 AND rt.po_line_id = l_replen_po_line_id
1317 AND rt.po_line_location_id = l_replen_po_shipment_id
1318 AND rsl.item_id = l_shikyu_component_id
1319 AND rt.SOURCE_DOCUMENT_CODE ='PO'
1320 AND rt.replenish_order_line_id = l_replen_so_line_id
1321 AND
1322 (
1323 (
1324 RT.TRANSACTION_TYPE IN ('RECEIVE', 'TRANSFER', 'ACCEPT' , 'REJECT', 'MATCH')
1325 AND EXISTS
1326 (
1327 SELECT
1328 'POSTIVE RCV SUPPLY'
1329 FROM RCV_SUPPLY RS
1330 WHERE RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
1331 AND RS.TO_ORG_PRIMARY_QUANTITY >
1332 (
1333 SELECT
1334 NVL(SUM(RTI.PRIMARY_QUANTITY),0)
1335 FROM RCV_TRANSACTIONS_INTERFACE RTI
1336 WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
1337 AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
1338 AND RTI.PROCESSING_STATUS_CODE = 'PENDING'
1339 )
1340 )
1341 )
1342 OR
1343 (
1344 RT.TRANSACTION_TYPE = 'DELIVER'
1345 AND RT.SOURCE_DOCUMENT_CODE <> 'RMA'
1346 )
1347 )
1348 AND NOT EXISTS
1349 (
1350 SELECT
1351 'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
1352 FROM PO_LINE_LOCATIONS_ALL PLL
1353 WHERE PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
1354 AND
1355 (
1356 NVL(PLL.CANCEL_FLAG,'N') = 'Y'
1357 OR NVL(PLL.CLOSED_CODE,'OPEN') = 'FINALLY CLOSED'
1358 OR NVL(PLL.APPROVED_FLAG,'N') <> 'Y'
1359 OR NVL(PLL.MATCHING_BASIS,'QUANTITY') = 'AMOUNT'
1360 OR PLL.PAYMENT_TYPE IS NOT NULL
1361 )
1362 )
1363 AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID;
1364
1365
1366 RCV_QUANTITIES_S.get_available_quantity(
1367 p_transaction_type => 'RETURN TO VENDOR'
1368 ,p_parent_id => l_parent_transaction_id
1369 ,p_receipt_source_code => 'VENDOR'
1370 ,p_parent_transaction_type => l_transaction_type
1371 ,p_grand_parent_id => 0
1372 ,p_correction_type => 'NEGATIVE'
1373 ,p_available_quantity => l_available_quantity
1374 ,p_tolerable_quantity => l_tolerable_quantity
1375 ,p_unit_of_measure => l_unit_of_measure);
1376
1377 -- convert available_quantity into primary uom quantity
1378 IF l_unit_of_measure <> l_shikyu_unit_of_measure THEN
1379 l_returned_qty_parent_txn_uom := INV_CONVERT.inv_um_convert
1380 ( item_id => l_shikyu_component_id
1381 , precision => 5
1382 , from_quantity => l_returned_qty
1383 , from_unit => null
1384 , to_unit => null
1385 , from_name => l_shikyu_unit_of_measure
1386 , to_name => l_unit_of_measure
1387 );
1388 END IF;
1389
1390 IF l_returned_qty_parent_txn_uom > l_available_quantity THEN
1391 raise l_rtv_unsuccessful;
1392 END IF;
1393
1394 -- Bug 9883090
1395 -- Fetch the vendor id and lookup code from the replenishment PO.
1396 -- This will be later passed to the Receiving Interface tables.
1397 -- The lookup code will be used to close the PO after RTV.
1398 -- skolluku
1399 SELECT vendor_id, type_lookup_code
1400 INTO l_vendor_id, l_po_type_lookup_code
1401 FROM po_headers_all
1402 WHERE po_header_id = l_replen_po_header_id;
1403
1404 -- insert into RCV header interface
1405 JMF_SHIKYU_RCV_PVT.process_rcv_header
1406 ( p_vendor_id => l_vendor_id -- Bug 9883090
1407 , p_vendor_site_id => NULL
1408 , p_ship_to_org_id => NULL
1409 , x_rcv_header_id => l_header_interface_id
1410 , x_group_id => l_group_id);
1411
1412 -- insert into RCV transaction interface
1413 JMF_SHIKYU_RCV_PVT.process_rcv_trx
1414 ( p_rcv_header_id => l_header_interface_id
1415 , p_group_id => l_group_id
1416 , p_quantity => l_returned_qty_parent_txn_uom
1417 , p_unit_of_measure => l_unit_of_measure
1418 , p_po_header_id => l_replen_po_header_id
1419 , p_po_line_id => l_replen_po_line_id
1420 , p_po_line_location_id => l_replen_po_shipment_id
1421 , p_transaction_type => 'RETURN TO VENDOR'
1422 , p_parent_transaction_id=> l_parent_transaction_id
1423 , p_from_subinventory => l_subinventory
1424 , p_from_locator_id => l_locator_id
1425 , p_project_id => l_project_id
1426 , p_vendor_id => l_vendor_id) ; -- Bug 9883090. Pass Vendor id to RTI
1427
1428 -- submit concurrent request for Receiving Transaction Processor
1429 -- Bug 9883090
1430 -- Modify the mode from IMMEDIATE to BATCH, since the records
1431 -- are inserted in the RTI as BATCH.
1432 -- skolluku
1433 l_return_number := fnd_request.submit_request(
1434 application => 'PO'
1435 , program => 'RVCTP'
1436 , description => 'Receiving Transaction Processor'
1437 , start_time => SYSDATE
1438 , sub_request => FALSE
1439 , argument1 => 'BATCH'
1440 , argument2 => l_group_id
1441 );
1442
1443 COMMIT;
1444
1445
1446
1447 -- Wait till RTV completes to perform deallocation.
1448 LOOP
1449 IF JMF_SHIKYU_UTIL.Has_worker_completed(l_return_number) THEN
1450 EXIT;
1451 ELSE
1452 DBMS_LOCK.sleep(JMF_SHIKYU_UTIL.G_SLEEP_TIME);
1453 END IF;
1454 END LOOP;
1455
1456 -- Again search for received quantity against Replenish Po Shipment
1457 select QUANTITY_RECEIVED
1458 into l_post_qty_received
1459 from PO_LINE_LOCATIONS_ALL
1460 where LINE_LOCATION_ID = l_replen_po_shipment_id;
1461
1462 -- Check if return happened successfully
1463 IF l_returned_qty_parent_txn_uom <> (l_pre_qty_received - l_post_qty_received) THEN
1464 raise l_rtv_unsuccessful;
1465 END IF;
1466
1467 --
1468 -- Bug 9883090
1469 -- Close the PO if the corresponding SO Line is closed.
1470 -- This is required as the RTV will open the PO again
1471 -- and this is undesirable, as a new set of PO-SO will
1472 -- created later.
1473 -- skolluku
1474 --
1475 SELECT open_flag INTO l_open_flag
1476 FROM oe_order_lines_all
1477 WHERE line_id = l_replen_so_line_id;
1478
1479 IF l_open_flag = 'N' THEN
1480 l_return_status := po_actions.close_po(p_docid => l_replen_po_header_id,
1481 p_doctyp => 'PO',
1482 p_docsubtyp => l_po_type_lookup_code,
1483 p_lineid => l_replen_po_line_id,
1484 p_shipid => l_replen_po_shipment_id,
1485 p_action => 'CLOSE',
1486 p_return_code => l_return_code,
1487 p_conc_flag => 'Y',
1488 p_auto_close => 'N');
1489 END IF;
1490
1491 END IF; --IF p_status <> 'DA_ERROR' THEN
1492 -- Convert shipped_qty to primary uom if required
1493 IF l_shikyu_primary_uom <> l_shipped_qty_uom THEN
1494 l_shipped_primary_uom_qty := INV_CONVERT.inv_um_convert
1495 ( item_id => l_shikyu_component_id
1496 , precision => 5
1497 , from_quantity => l_shipped_primary_uom_qty
1498 , from_unit => l_shipped_qty_uom
1499 , to_unit => l_shikyu_primary_uom
1500 , from_name => null
1501 , to_name => null
1502 );
1503 END IF;
1504
1505 -- Consider case of under Shipment when allocable quantity is more
1506 -- than shipped quantity. Returned quantity will be increased by
1507 -- allocable quantity - shipped quantity.
1508 IF l_allocable_primary_uom_qty > l_shipped_primary_uom_qty THEN
1509 l_returned_qty := l_returned_qty +
1510 (l_allocable_primary_uom_qty - l_shipped_primary_uom_qty );
1511 END IF;
1512 -- Deallocate based on LIFO order of Need By Date of the Subcontracting
1513 -- Orders already allocated to the current Replenishment SO Line
1514 JMF_SHIKYU_ALLOCATION_PVT.Reconcile_Replen_Excess_Qty
1515 ( p_api_version => 1.0
1516 , p_init_msg_list => p_init_msg_list
1517 , x_return_status => x_return_status
1518 , x_msg_count => x_msg_count
1519 , x_msg_data => x_msg_data
1520 , p_replen_order_line_id => l_replen_so_line_id
1521 , p_excess_qty => l_returned_qty
1522 );
1523 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1524 p_status := 'DA_ERROR';
1525 END IF;
1526
1527 IF g_proc_level >= g_debug_level
1528 THEN
1529 FND_LOG.string(g_proc_level
1530 , G_MODULE_PREFIX || l_api_name || '.end'
1531 , NULL);
1532 END IF;
1533
1534 EXCEPTION
1535 WHEN l_rtv_unsuccessful THEN
1536 FND_MSG_PUB.Count_And_Get
1537 ( p_count => x_msg_count
1538 , p_data => x_msg_data
1539 );
1540
1541 x_return_status := FND_API.G_RET_STS_ERROR;
1542 IF g_excep_level >= g_debug_level
1543 THEN
1544 FND_LOG.string(g_excep_level
1545 , G_MODULE_PREFIX || l_api_name || '.RTV_Failed'
1546 , 'Exception - RTV Failed for Replenishment Sales Order Id: '
1547 || l_replen_so_line_id);
1548 END IF;
1549 WHEN NO_DATA_FOUND THEN
1550 FND_MSG_PUB.Count_And_Get
1551 ( p_count => x_msg_count
1552 , p_data => x_msg_data
1553 );
1554
1555 x_return_status := FND_API.G_RET_STS_ERROR;
1556 IF g_excep_level >= g_debug_level
1557 THEN
1558 FND_LOG.string(g_excep_level
1559 , G_MODULE_PREFIX || l_api_name || '.No_Date_Found'
1560 , 'Exception - Replenishment Sales Order Line: ' || l_replen_so_line_id);
1561 END IF;
1562
1563 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1564 FND_MSG_PUB.Count_And_Get
1565 ( p_count => x_msg_count
1566 , p_data => x_msg_data
1567 );
1568
1569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1570 IF g_unexp_level >= g_debug_level
1571 THEN
1572 FND_LOG.string(g_unexp_level
1573 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1574 , 'Exception');
1575 END IF;
1576 WHEN OTHERS THEN
1577 FND_MSG_PUB.Count_And_Get
1578 ( p_count => x_msg_count
1579 , p_data => x_msg_data
1580 );
1581
1582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1583 IF g_unexp_level >= g_debug_level
1584 THEN
1585 FND_LOG.string(g_unexp_level
1586 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1587 , 'Exception');
1588 END IF;
1589 END Process_Component_Return;
1590
1591 /* Private Helper Functions/Procedures */
1592 --========================================================================
1593 -- PROCEDURE : Validate_And_Allocate PRIVATE
1594 -- PARAMETERS: p_init_msg_list IN Standard in parameter message list
1595 -- x_return_flag OUT To indicate whether the required number of
1596 -- replenishments are available or not
1597 -- x_return_status OUT Stadard out parameter for return status
1598 -- (Values E(Error), S(Success), U(Unexpected error))
1599 -- x_msg_count OUT Stadard out parameter for number of messages
1600 -- x_msg_data OUT Stadard out parameter for message
1601 -- (Values E(Error), S(Success), U(Unexpected error))
1602 -- p_po_shipment_id IN Subcontracting PO shipment
1603 -- p_project_id IN project reference
1604 -- p_task_id IN task reference
1605 --
1606 -- COMMENT : This procedure is called from OSA Receipt and OSA Return api
1607 -- before performing WIP completion and Misc issue at Manufacturing
1608 -- Partner organization. It does allocations if required. It raises exception
1609 -- if sufficient allocations are not available.
1610 --Following is logic:
1611 --1. If Subcontracting PO is allocable
1612 -- IF Required quantity of SHIKYU Component at TP site for this subcontract PO >
1613 -- Allocated quantity of SHIKYU Component against that Subcontracting Order
1614 -- Then
1615 -- Find available Replenishment SO for allocation
1616 -- If Enough replenishment SO are not available then error out.
1617 -- perform allocation for SHIKYU Component quantities against OSA Receipt quantity.
1618 --========================================================================
1619 PROCEDURE Validate_And_Allocate
1620 ( p_init_msg_list IN VARCHAR2
1621 , x_return_status OUT NOCOPY VARCHAR2
1622 , x_msg_count OUT NOCOPY NUMBER
1623 , x_msg_data OUT NOCOPY VARCHAR2
1624 , x_return_flag OUT NOCOPY NUMBER
1625 , p_po_shipment_id IN NUMBER
1626 , p_project_id IN NUMBER
1627 , p_task_id IN NUMBER
1628 )
1629 IS
1630
1631 l_remaining_qty NUMBER;
1632 l_actual_qty_allocated NUMBER;
1633 l_shikyu_qty_to_allocate NUMBER;
1634 l_total_allocated_Qty NUMBER;
1635 l_required_quantity NUMBER;
1636 l_allocable_flag JMF_SHIKYU_ALLOCATIONS_V.ALLOCATABLE_FLAG%Type;
1637 l_available_replen_so_qty_tbl JMF_SHIKYU_ALLOCATION_PVT.g_replen_so_qty_tbl_type;
1638 l_adjustment_total NUMBER;
1639 l_interlock_status VARCHAR2(1);
1640 -- custom exceptions
1641 l_not_enough_replen_excep EXCEPTION;
1642 l_not_allocated_completely EXCEPTION;
1643
1644 -- cursor definitions
1645 cursor c_subcontract_po_shikyu_comp is
1646 select jsc.SHIKYU_COMPONENT_ID shikyu_component_id, jso.TP_ORGANIZATION_ID tp_organization_id, jsc.PRIMARY_UOM primary_uom
1647 from JMF_SHIKYU_COMPONENTS jsc, JMF_SUBCONTRACT_ORDERS jso
1648 where jsc.SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id
1649 and jso.subcontract_po_shipment_id = jsc.SUBCONTRACT_PO_SHIPMENT_ID;
1650
1651 BEGIN
1652
1653 x_return_status := FND_API.G_RET_STS_ERROR;
1654 x_return_flag := 0;
1655
1656 SELECT interlock_status
1657 into l_interlock_status
1658 FROM JMF_SUBCONTRACT_ORDERS
1659 WHERE SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
1660
1661 -- AND NVL(project_id, -1)= NVL(p_project_id, -1)
1662 -- AND NVL(task_id, -1)= NVL(p_task_id, -1);
1663
1664 IF l_interlock_status = 'C' THEN
1665 x_return_status := FND_API.G_RET_STS_SUCCESS;
1666 ELSE
1667 -- Check if allocation is needed against OSA receipt
1668 select ALLOCATABLE_FLAG
1669 into l_allocable_flag
1670 from JMF_SHIKYU_ALLOCATIONS_V
1671 where SUBCONTRACT_PO_SHIPMENT_ID = p_po_shipment_id;
1672
1673
1674 -- Call Allocations API to allcoate if all received quantities are not allocated
1675 IF l_allocable_flag = 'Y' THEN
1676 -- Allocate each SHIKYU component of OSA item
1677 FOR l_subcontract_po_shikyu_comp IN c_subcontract_po_shikyu_comp
1678 LOOP
1679
1680 -- Get total allocated quantity for SHIKYU Component.
1681 l_total_allocated_Qty := JMF_SHIKYU_UTIL.Get_Subcontract_Allocated_Qty
1682 (p_po_shipment_id
1683 ,l_subcontract_po_shikyu_comp.shikyu_component_id);
1684
1685 -- Get required quantity of shikyu component.
1686
1687
1688 l_required_quantity := JMF_SHIKYU_WIP_PVT.get_component_quantity
1689 ( p_item_id => l_subcontract_po_shikyu_comp.shikyu_component_id
1690 , p_organization_id => l_subcontract_po_shikyu_comp.tp_organization_id
1691 , p_subcontract_po_shipment_id => p_po_shipment_id
1692 );
1693
1694 /*vmutyala added the following to take into account the consumption adjustments made after receiving partial
1695 quantity. The subcontract PO is fully allocated if l_required_quantity - l_total_allocated_Qty = total adjustments made */
1696 l_adjustment_total := 0;
1697
1698 SELECT nvl(SUM(adjustment), 0)
1699 INTO l_adjustment_total
1700 FROM jmf_shikyu_adjustments
1701 WHERE subcontract_po_shipment_id = p_po_shipment_id
1702 AND shikyu_component_id = l_subcontract_po_shikyu_comp.shikyu_component_id
1703 AND request_id IS NOT NULL;
1704
1705 l_required_quantity := l_required_quantity - l_adjustment_total;
1706
1707
1708 IF (l_required_quantity > l_total_allocated_Qty) THEN
1709
1710 -- Calculate total SHIKYU comsumption by received OSA items
1711 l_shikyu_qty_to_allocate := l_required_quantity - l_total_allocated_Qty;
1712
1713
1714 -- Find available Replenishment Sales Orders for allocation
1715 JMF_SHIKYU_ALLOCATION_PVT.Get_Available_Replenishment_So
1716 ( p_api_version => 1.0
1717 , p_init_msg_list => p_init_msg_list
1718 , x_return_status => x_return_status
1719 , x_msg_count => x_msg_count
1720 , x_msg_data => x_msg_data
1721 , p_subcontract_po_shipment_id => p_po_shipment_id
1722 , p_component_id => l_subcontract_po_shikyu_comp.shikyu_component_id
1723 , p_qty => l_shikyu_qty_to_allocate
1724 , p_include_additional_supply => 'N'
1725 , p_arrived_so_lines_only => 'Y'
1726 , x_available_replen_tbl => l_available_replen_so_qty_tbl
1727 , x_remaining_qty => l_remaining_qty
1728 );
1729
1730 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1731 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1732 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1733 raise FND_API.G_EXC_ERROR;
1734 -- Raise an exception if there is not enough existing replenishments
1735 ELSIF l_remaining_qty > 0 THEN
1736 raise l_not_enough_replen_excep;
1737 -- Allocate if there is enough existing replenishments
1738 END IF;
1739
1740 -- Allocate SHIKYU components from available Replenishments.
1741 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1742 JMF_SHIKYU_ALLOCATION_PVT.Allocate_Quantity
1743 ( p_api_version => 1.0
1744 , p_init_msg_list => p_init_msg_list
1745 , x_return_status => x_return_status
1746 , x_msg_count => x_msg_count
1747 , x_msg_data => x_msg_data
1748 , p_subcontract_po_shipment_id => p_po_shipment_id
1749 , p_component_id => l_subcontract_po_shikyu_comp.shikyu_component_id
1750 , p_qty_to_allocate => l_shikyu_qty_to_allocate
1751 , p_available_replen_tbl => l_available_replen_so_qty_tbl
1752 -- to store the actual qty being returned as OUT parameter
1753 , x_qty_allocated => l_actual_qty_allocated
1754 );
1755
1756 -- Raise an exception if actual quantity allocated is not same as requested quantity.
1757 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1758 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1759 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1760 raise FND_API.G_EXC_ERROR;
1761 ELSIF l_actual_qty_allocated < l_shikyu_qty_to_allocate THEN
1762 raise l_not_allocated_completely;
1763 END IF; -- IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1764 END IF; --IF x_return_status = success THEN
1765 ELSIF l_required_quantity = l_total_allocated_Qty THEN
1766 x_return_status := FND_API.G_RET_STS_SUCCESS;
1767 END IF; --IF (l_required_quantity > l_total_allocated_Qty) THEN
1768
1769
1770 END LOOP; -- FOR l_subcontract_po_shikyu_comp IN c_subcontract_po_shikyu_comp
1771 ELSIF l_allocable_flag = 'N' THEN
1772 x_return_status := FND_API.G_RET_STS_SUCCESS;
1773 END IF; -- IF l_allocable_flag = 'Y' THEN
1774 END IF; -- IF l_interlock_status = 'C' THEN
1775
1776 EXCEPTION
1777 WHEN l_not_enough_replen_excep THEN
1778
1779
1780 x_return_flag := 1;
1781 x_return_status := FND_API.G_RET_STS_ERROR;
1782 WHEN l_not_allocated_completely THEN
1783
1784 x_return_flag := 2;
1785 x_return_status := FND_API.G_RET_STS_ERROR;
1786 WHEN OTHERS THEN
1787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1788 END Validate_And_Allocate;
1789 --=============================================================================
1790 -- PROCEDURE NAME: Init
1791 -- TYPE : PRIVATE
1792 -- PARAMETERS : None
1793 -- DESCRIPTION : Initializes Global Variables.
1794 -- EXCEPTIONS : None
1795 -- CHANGE HISTORY: 23-MAY-05 VCHU Created.
1796 --=============================================================================
1797
1798 PROCEDURE Init
1799 IS
1800 BEGIN
1801
1802 -- initializes the global variables for FND Log
1803
1804 IF g_proc_level IS NULL
1805 THEN
1806 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
1807 END IF; /* IF g_proc_level IS NULL */
1808
1809 IF g_unexp_level IS NULL
1810 THEN
1811 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
1812 END IF; /* IF g_unexp_level IS NULL */
1813
1814 IF g_excep_level IS NULL
1815 THEN
1816 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
1817 END IF; /* IF g_excep_level IS NULL */
1818
1819 IF g_statement_level IS NULL
1820 THEN
1821 g_statement_level := FND_LOG.LEVEL_STATEMENT;
1822 END IF; /* IF g_statement_level IS NULL */
1823
1824 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1825
1826
1827 END Init;
1828
1829 END JMF_PROCESS_SHIKYU_RCV_TRX_PVT;
[Home] [Help]