DBA Data[Home] [Help]

PACKAGE BODY: APPS.JMF_PROCESS_SHIKYU_RCV_TRX_PVT

Source


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;