[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_GRP
Source
1 PACKAGE BODY JMF_SHIKYU_GRP as
2 --$Header: JMFGSHKB.pls 120.14 2010/07/27 12:24:58 skolluku ship $
3
4 --+===========================================================================+
5 --| Copyright (c) 2005 Oracle Corporation |
6 --| Redwood Shores, California, USA |
7 --| All rights reserved. |
8 --+===========================================================================+
9 --| |
10 --| FILENAME : JMFGSHKB.pls |
11 --| |
12 --| DESCRIPTION: Body file of the group package for the Charge |
13 --| Based SHIKYU project. Other teams such as CST, |
14 --| RCV, PO and Financials Globalization will be |
15 --| calling this package to support SHIKYU. |
16 --| |
17 --| FUNCTION/PROCEDURE: Variance_Account |
18 --| Get_Po_Shipment_Osa_Flag |
19 --| Is_Tp_Organization |
20 --| Is_AP_Invoice_Shikyu_Nettable |
21 --| Is_AP_Inv_Shikyu_Nettable_Func |
22 --| Is_So_Line_Shikyu_Enabled |
23 --| Validate_Osa_Flag |
24 --| Get_Shikyu_Attributes |
25 --| |
26 --| HISTORY: |
27 --| 20-APR-2005 vchu Created. |
28 --| 07-JUL-2005 vchu Fixed GSCC errors. |
29 --| 19-SEP-2005 vchu Added Is_AP_Inv_Shikyu_Nettable_Func per |
30 --| request from Financials Globalization. |
31 --| Stubbed out all other procedures to avoid |
32 --| uptake of other dependencies for their local |
33 --| dev and testing environments. Will leap |
34 --| frog the actual code for those stubbed out |
35 --| procedures in the next version. |
36 --| 21-SEP-2005 vchu Leapfrog from revision 120.4 since revision |
37 --| 120.5 is a stubbed our version for |
38 --| Finanicials Globalization that contains the |
39 --| actual implementation of the |
40 --| Is_AP_Invoice_Shikyu_Nettable procedure |
41 --| only. Is_AP_Inv_Shikyu_Nettable_Func is |
42 --| also added to this version. |
43 --| 31-MAY-2006 vchu Fixed 5212998: Performance fix for SQL ID |
44 --| #17703504. Modified the query in |
45 --| Is_So_Line_Shikyu_Enabled to avoid FTS. |
46 --| It's OK to remove the CONNECT BY PRIOR logic |
47 --| since RMA lines do not go beyond one level, |
48 --| and they never reference another RMA line |
49 --| (according to Manish Chavan from OM). |
50 --| 29-SEP-2006 vchu Bug fix for 5574912: Added the distinct |
51 --| keyword to the query in |
52 --| Is_AP_Invoice_Shikyu_Nettable since there |
53 --| could be multiple PO Shipments associated to |
54 --| a single AP Invoice Distribution. We can |
55 --| assume the Outsourced_Assembly flag of all |
56 --| of these PO Shipments would be either 1 (Yes)|
57 --| or 2 (No), since the ERS program creates |
58 --| separate AP invoices for PO Shipments with |
59 --| the Outsourced_Assembly flag checked, and |
60 --| those with the flag unchecked. |
61 --| 03-OCT-2007 kdevadas 12.1 Buy/Sell Subcontracting Changes |
62 --| Reference - GBL_BuySell_TDD.doc |
63 --| Reference - GBL_BuySell_FDD.doc |
64 --| 27-DEC-2007 kdevadas Bug: 6679369 - Get_shikyu_variance_account |
65 --| modified to pass the subcontracting type |
66 --| to Costing for OSA receipts in Std Cost orgs |
67 --+===========================================================================+
68
69 --=============================================
70 -- CONSTANTS
71 --=============================================
72 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'jmf.plsql.' || G_PKG_NAME || '.';
73
74 --=============================================
75 -- GLOBAL VARIABLES
76 --=============================================
77 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
78
79 --===========================================================================
80 -- API NAME : Get_Shikyu_Variance_Account
81 --
82 -- DESCRIPTION:
83 --
84 -- PARAMETERS :
85 -- IN :
86 -- OUT :
87 --
88 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
89 --
90 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
91 --===========================================================================
92 PROCEDURE Get_Shikyu_Variance_Account
93 ( p_api_version IN NUMBER
94 , p_init_msg_list IN VARCHAR2
95 , x_return_status OUT NOCOPY VARCHAR2
96 , x_msg_count OUT NOCOPY NUMBER
97 , x_msg_data OUT NOCOPY VARCHAR2
98 , p_po_shipment_id IN NUMBER
99 , x_variance_account OUT NOCOPY NUMBER
100 , x_subcontracting_type OUT NOCOPY NUMBER -- Bug 6679369
101 )
102 IS
103
104 l_api_name CONSTANT VARCHAR2(30) := 'Get_Shikyu_Variance_Account';
105 l_api_version CONSTANT NUMBER := 1.0;
106
107 /* 12.1 Buy/Sell Subcontracting Changes */
108 l_subcontracting_type VARCHAR2(1);
109 l_oem_organization_id NUMBER;
110 l_tp_organization_id NUMBER;
111 l_ppv_account NUMBER;
112
113 BEGIN
114
115 IF g_fnd_debug = 'Y' AND
116 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
117 THEN
118 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
119 , G_MODULE_PREFIX || l_api_name || '.invoked'
120 , 'Entry');
121 END IF;
122
123 -- Start API initialization
124 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
125 FND_MSG_PUB.initialize;
126 END IF;
127
128 IF NOT FND_API.Compatible_API_Call( l_api_version
129 , p_api_version
130 , l_api_name
131 , G_PKG_NAME
132 )
133 THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138 -- End API initialization
139
140 x_variance_account := NULL;
141
142 -- Joining the subcontracting orders table with the Shipping Networks table
143 -- table to get the Code Combination ID of the SHIKYU Variance Account.
144 -- The Shipping Networks table stores relationships from OEM Organizations
145 -- to MP Organizations
146
147 /* 12.1 - Buy/Sell Subcontracting - Changes */
148 /* For Buy/Sell subcontracting - return the purchase price variance account*/
149 SELECT mip.subcontracting_type, jso.oem_organization_id
150 INTO l_subcontracting_type , l_oem_organization_id
151 FROM mtl_interorg_parameters mip, jmf_subcontract_orders jso
152 WHERE jso.subcontract_po_shipment_id = p_po_shipment_id
153 AND jso.oem_organization_id = mip.from_organization_id
154 AND jso.tp_organization_id = mip.to_organization_id;
155
156
157 /* Bug : 6679369
158 Note : IF Subcontracting TYPE IS Chargeable, x_subcontracting_type will be SET AS 1
159 IF Subcontracting TYPE IS Buy/Sell, x_subcontracting_type will be SET AS 2
160 x_subcontracting_type will be returned to CST as a number rather than a VARCHAR2 as the
161 CST code is written in pro-C and there are technical limitations in passsing the OUT
162 paramter as VARCHAR2. This change has been incorporated after recommendations from
163 the CST team */
164
165 If (l_subcontracting_type = 'B') /* Buy Sell relationship betn OEM and MP */
166 THEN
167 x_subcontracting_type := 2 ; -- Bug 6679369
168 SELECT purchase_price_var_account
169 INTO l_ppv_account
170 FROM mtl_parameters
171 WHERE organization_id = l_oem_organization_id ;
172
173 X_variance_account := l_ppv_account;
174
175 ELSIF (l_subcontracting_type = 'C')
176 THEN
177 /* return the chargeable subcontracting variance account */
178 x_subcontracting_type := 1 ; -- Bug 6679369
179 SELECT mip.shikyu_oem_var_account_id
180 INTO x_variance_account
181 FROM mtl_interorg_parameters mip,
182 jmf_subcontract_orders jso
183 WHERE jso.subcontract_po_shipment_id = p_po_shipment_id
184 AND jso.oem_organization_id = mip.from_organization_id
185 AND jso.tp_organization_id = mip.to_organization_id;
186
187 END IF;
188
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 FND_MSG_PUB.Count_And_Get
192 ( p_count => x_msg_count
193 , p_data => x_msg_data
194 );
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 IF g_fnd_debug = 'Y' AND
197 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
198 THEN
199 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
200 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
201 , 'No relationship exists for the OEM Organization and Manufacturing Organization of the Subcontracting PO');
202 END IF;
203
204 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
205 FND_MSG_PUB.Count_And_Get
206 ( p_count => x_msg_count
207 , p_data => x_msg_data
208 );
209
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211 IF g_fnd_debug = 'Y' AND
212 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
213 THEN
214 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
215 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
216 , 'Exception');
217 END IF;
218
219 WHEN OTHERS THEN
220 FND_MSG_PUB.Count_And_Get
221 ( p_count => x_msg_count
222 , p_data => x_msg_data
223 );
224
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 IF g_fnd_debug = 'Y' AND
227 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
228 THEN
229 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
230 , G_MODULE_PREFIX || l_api_name || '.others_exception'
231 , 'Exception');
232 END IF;
233 END Get_Shikyu_Variance_Account;
234
235 --===========================================================================
236 -- API NAME : Get_Po_Shipment_Osa_Flag
237 --
238 -- DESCRIPTION:
239 --
240 -- PARAMETERS :
241 -- IN :
242 -- OUT :
243 --
244 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
245 --
246 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
247 --===========================================================================
248 PROCEDURE Get_Po_Shipment_Osa_Flag
249 ( p_api_version IN NUMBER
250 , p_init_msg_list IN VARCHAR2
251 , x_return_status OUT NOCOPY VARCHAR2
252 , x_msg_count OUT NOCOPY NUMBER
253 , x_msg_data OUT NOCOPY VARCHAR2
254 , p_po_shipment_id IN NUMBER
255 , x_osa_flag OUT NOCOPY VARCHAR2
256 )
257 IS
258
259 l_api_name CONSTANT VARCHAR2(30) := 'Get_Po_Shipment_Osa_Flag';
260 l_api_version CONSTANT NUMBER := 1.0;
261
262 l_outsourced_assembly NUMBER := NULL;
263
264 l_oem_org_id NUMBER;
265
266 BEGIN
267
268 IF g_fnd_debug = 'Y' AND
269 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
270 THEN
271 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
272 , G_MODULE_PREFIX || l_api_name || '.invoked'
273 , 'Entry');
274 END IF;
275
276 -- Start API initialization
277 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
278 FND_MSG_PUB.initialize;
279 END IF;
280
281 /*
282 IF NOT FND_API.Compatible_API_Call( l_api_version
283 , p_api_version
284 , l_api_name
285 , G_PKG_NAME
286 )
287 THEN
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290 */
291
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293 -- End API initialization
294
295 x_osa_flag := 'N';
296
297 IF g_fnd_debug = 'Y' AND
298 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
299 THEN
300 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
301 , G_MODULE_PREFIX || l_api_name || '.invoked'
302 , 'Before the query: p_po_shipment_id = "' || p_po_shipment_id || '"');
303 END IF;
304
305 -- Selecting the shikyu_osa_item_flag of PO Line
306 -- with the passed in PO Line ID
307 SELECT nvl(outsourced_assembly, 2)
308 INTO l_outsourced_assembly
309 FROM po_line_locations_all poll
310 WHERE poll.line_location_id = p_po_shipment_id;
311
312 IF g_fnd_debug = 'Y' AND
313 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
314 THEN
315 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
316 , G_MODULE_PREFIX || l_api_name || '.invoked'
317 , 'After the query: l_outsourced_assembly = ' || l_outsourced_assembly);
318 END IF;
319
320 IF l_outsourced_assembly = 1
321 THEN
322 x_osa_flag := 'Y';
323 --
324 -- Bug 9835073
325 -- Return 'N' if no subcontracting relation exists
326 -- skolluku
327 --
328 BEGIN
329 SELECT jso.oem_organization_id
330 INTO l_oem_org_id
331 FROM mtl_interorg_parameters mip, jmf_subcontract_orders jso
332 WHERE jso.subcontract_po_shipment_id = p_po_shipment_id
333 AND jso.oem_organization_id = mip.from_organization_id
334 AND jso.tp_organization_id = mip.to_organization_id;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 x_osa_flag := 'N';
338 END;
339 ELSE
340 x_osa_flag := 'N';
341 END IF;
342
343 IF g_fnd_debug = 'Y' AND
344 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
345 THEN
346 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
347 , G_MODULE_PREFIX || l_api_name || '.invoked'
348 , 'Exit: Returning x_osa_flag = "' || x_osa_flag || '", x_return_status = "' || x_return_status || '"');
349 END IF;
350
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 FND_MSG_PUB.Count_And_Get
354 ( p_count => x_msg_count
355 , p_data => x_msg_data
356 );
357 x_msg_data := 'NO_DATA_FOUND: p_po_shipment_id = ' || p_po_shipment_id;
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 IF g_fnd_debug = 'Y' AND
360 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
361 THEN
362 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
363 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
364 , 'The PO Line does not exist.');
365 END IF;
366
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 FND_MSG_PUB.Count_And_Get
369 ( p_count => x_msg_count
370 , p_data => x_msg_data
371 );
372 x_msg_data := 'FND_API.G_EXC_UNEXPECTED_ERROR: p_po_shipment_id = ' || p_po_shipment_id;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 IF g_fnd_debug = 'Y' AND
375 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
376 THEN
377 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
378 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
379 , 'Exception');
380 END IF;
381
382 WHEN OTHERS THEN
383 FND_MSG_PUB.Count_And_Get
384 ( p_count => x_msg_count
385 , p_data => x_msg_data
386 );
387 x_msg_data := 'OTHER EXCEPTIONS: p_po_shipment_id = ' || p_po_shipment_id;
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389 IF g_fnd_debug = 'Y' AND
390 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
391 THEN
392 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
393 , G_MODULE_PREFIX || l_api_name || '.others_exception'
394 , 'Exception');
395 END IF;
396 END Get_Po_Shipment_Osa_Flag;
397
398 --===========================================================================
399 -- API NAME : Is_Tp_Organization
400 --
401 -- DESCRIPTION:
402 --
403 -- PARAMETERS :
404 -- IN :
405 -- OUT :
406 --
407 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
408 --
409 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
410 --===========================================================================
411 PROCEDURE Is_Tp_Organization
412 ( p_api_version IN NUMBER
413 , p_init_msg_list IN VARCHAR2
414 , x_return_status OUT NOCOPY VARCHAR2
415 , x_msg_count OUT NOCOPY NUMBER
416 , x_msg_data OUT NOCOPY VARCHAR2
417 , p_organization_id IN NUMBER
418 , x_is_tp_org_flag OUT NOCOPY VARCHAR2
419 )
420 IS
421
422 l_api_name CONSTANT VARCHAR2(30) := 'Is_Tp_Organization';
423 l_api_version CONSTANT NUMBER := 1.0;
424
425 BEGIN
426
427 IF g_fnd_debug = 'Y' AND
428 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
429 THEN
430 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
431 , G_MODULE_PREFIX || l_api_name || '.invoked'
432 , 'Entry');
433 END IF;
434
435 -- Start API initialization
436 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
437 FND_MSG_PUB.initialize;
438 END IF;
439
440 IF NOT FND_API.Compatible_API_Call( l_api_version
441 , p_api_version
442 , l_api_name
443 , G_PKG_NAME
444 )
445 THEN
446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
447 END IF;
448
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450 -- End API initialization
451
452 SELECT nvl(trading_partner_org_flag, 'N')
453 INTO x_is_tp_org_flag
454 FROM MTL_PARAMETERS
455 WHERE organization_id = p_organization_id;
456
457 EXCEPTION
458 WHEN NO_DATA_FOUND THEN
459 FND_MSG_PUB.Count_And_Get
460 ( p_count => x_msg_count
461 , p_data => x_msg_data
462 );
463 x_return_status := FND_API.G_RET_STS_ERROR;
464 IF g_fnd_debug = 'Y' AND
465 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
466 THEN
467 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
468 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
469 , 'The Inventory Organization does not exist.');
470 END IF;
471
472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
473 FND_MSG_PUB.Count_And_Get
474 ( p_count => x_msg_count
475 , p_data => x_msg_data
476 );
477
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 IF g_fnd_debug = 'Y' AND
480 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
481 THEN
482 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
483 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
484 , 'Exception');
485 END IF;
486
487 WHEN OTHERS THEN
488 FND_MSG_PUB.Count_And_Get
489 ( p_count => x_msg_count
490 , p_data => x_msg_data
491 );
492
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494 IF g_fnd_debug = 'Y' AND
495 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
496 THEN
497 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
498 , G_MODULE_PREFIX || l_api_name || '.others_exception'
499 , 'Exception');
500 END IF;
501 END Is_Tp_Organization;
502
503 --===========================================================================
504 -- API NAME : Is_AP_Invoice_Shikyu_Nettable
505 --
506 -- DESCRIPTION: This is the backtracking API to determine if an AP invoice
507 -- is eligible for SHIKYU Netting. It looks at one of the
508 -- distribution lines of the invoice and backtrack to the
509 -- corresponding PO Shipment to get the OSA_FLAG flag.
510 -- Since ERS would not comingle SHIKYU and non-SHIKYU lines
511 -- into the same AP invoice, an AP invoice would be containing
512 -- all SHIKYU distribution lines and thus would be SHIKYU
513 -- netting eligible if any one of the AP invoice distribution
514 -- lines can be backtracked to a PO Shipment created for an
515 -- Outsourced Assembly item.
516 --
517 -- PARAMETERS :
518 -- IN :
519 -- OUT :
520 --
521 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
522 --
523 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
524 --===========================================================================
525 PROCEDURE Is_AP_Invoice_Shikyu_Nettable
526 ( p_api_version IN NUMBER
527 , p_init_msg_list IN VARCHAR2
528 , x_return_status OUT NOCOPY VARCHAR2
529 , x_msg_count OUT NOCOPY NUMBER
530 , x_msg_data OUT NOCOPY VARCHAR2
531 , p_ap_invoice_id IN NUMBER
532 , x_nettable OUT NOCOPY VARCHAR2
533 )
534 IS
535
536 l_api_name CONSTANT VARCHAR2(30) := 'Is_AP_Invoice_Shikyu_Nettable';
537 l_api_version CONSTANT NUMBER := 1.0;
538
539 l_outsourced_assembly NUMBER := NULL;
540
541 BEGIN
542
543 IF g_fnd_debug = 'Y' AND
544 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
545 THEN
546 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
547 , G_MODULE_PREFIX || l_api_name || '.invoked'
548 , 'Entry');
549 END IF;
550
551 -- Start API initialization
552 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
553 FND_MSG_PUB.initialize;
554 END IF;
555
556 IF NOT FND_API.Compatible_API_Call( l_api_version
557 , p_api_version
558 , l_api_name
559 , G_PKG_NAME
560 )
561 THEN
562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 END IF;
564
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566 -- End API initialization
567
568 x_nettable := 'N';
569
570 -- Selecting the shikyu_osa_item_flag of the underlying PO Line
571 -- of the AP distribution line of the invoice with the passed in
572 -- invoice ID having the smallest distribution_line_number.
573 -- We can conclude whether an invoice is SHIKYU netting
574 -- eligible by examining one of the distribution line of the
575 -- invoice, since ERS would not comingle SHIKYU and
576 -- non-SHIKYU lines.
577
578 -- Bug 5574912: Added the distinct keyword since there can be
579 -- multiple PO Shipments associated to a single AP Invoice
580 -- Distribution. We can assume the Outsourced_Assembly flag
581 -- of all of these PO Shipments would be either 1 (Yes) or
582 -- 2 (No), since the ERS program creates separate AP invoices
583 -- for PO Shipments PO Shipments with the Outsourced_Assembly
584 -- flag checked, and those with the flag unchecked.
585
586 SELECT DISTINCT NVL(plla.outsourced_assembly, 2)
587 INTO l_outsourced_assembly
588 FROM ap_invoice_distributions_all apd,
589 po_distributions_all pda,
590 po_line_locations_all plla
591 WHERE apd.po_distribution_id = pda.po_distribution_id
592 AND pda.line_location_id = plla.line_location_id
593 AND apd.invoice_id = p_ap_invoice_id
594 AND apd.distribution_line_number =
595 (SELECT MIN(distribution_line_number)
596 FROM ap_invoice_distributions_all
597 WHERE invoice_id = p_ap_invoice_id);
598
599 IF l_outsourced_assembly = 1
600 THEN
601 x_nettable := 'Y';
602 ELSE
603 x_nettable := 'N';
604 END IF;
605
606 EXCEPTION
607 WHEN NO_DATA_FOUND THEN
608 FND_MSG_PUB.Count_And_Get
609 ( p_count => x_msg_count
610 , p_data => x_msg_data
611 );
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 IF g_fnd_debug = 'Y' AND
614 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
615 THEN
616 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
617 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
618 , 'The AP Invoice does not exist.');
619 END IF;
620
621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
622 FND_MSG_PUB.Count_And_Get
623 ( p_count => x_msg_count
624 , p_data => x_msg_data
625 );
626
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628 IF g_fnd_debug = 'Y' AND
629 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
630 THEN
631 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
632 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
633 , 'Exception');
634 END IF;
635
636 WHEN OTHERS THEN
637 FND_MSG_PUB.Count_And_Get
638 ( p_count => x_msg_count
639 , p_data => x_msg_data
640 );
641
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 IF g_fnd_debug = 'Y' AND
644 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
645 THEN
646 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
647 , G_MODULE_PREFIX || l_api_name || '.others_exception'
648 , 'Exception');
649 END IF;
650 END Is_AP_Invoice_Shikyu_Nettable;
651
652 --===========================================================================
653 -- API NAME : Is_AP_Inv_Shikyu_Nettable_Func
654 --
655 -- DESCRIPTION: This function calls the Is_AP_Invoice_Shikyu_Nettable
656 -- procedure and return the value passed back to the OUT
657 -- parameter x_nettable.
658 --
659 -- PARAMETERS :
660 -- IN :
661 -- OUT :
662 --
663 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
664 --
665 -- CHANGE HISTORY: 19-Sep-05 VCHU Created.
666 --===========================================================================
667 FUNCTION Is_AP_Inv_Shikyu_Nettable_Func
668 ( p_ap_invoice_id IN NUMBER
669 )
670 RETURN VARCHAR2
671 IS
672 l_return_status VARCHAR2(1);
673 l_msg_count NUMBER;
674 l_msg_data VARCHAR2(2000);
675 l_nettable VARCHAR2(1);
676 BEGIN
677
678 Is_AP_Invoice_Shikyu_Nettable
679 ( p_api_version => 1.0
680 , p_init_msg_list => NULL
681 , x_return_status => l_return_status
682 , x_msg_count => l_msg_count
683 , x_msg_data => l_msg_data
684 , p_ap_invoice_id => p_ap_invoice_id
685 , x_nettable => l_nettable
686 );
687 RETURN l_nettable;
688
689 END Is_AP_Inv_Shikyu_Nettable_Func;
690
691 --===========================================================================
692 -- API NAME : Is_So_Line_Shikyu_Enabled
693 --
694 -- DESCRIPTION:
695 --
696 -- PARAMETERS :
697 -- IN :
698 -- OUT :
699 --
700 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
701 --
702 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
703 --===========================================================================
704 PROCEDURE Is_So_Line_Shikyu_Enabled
705 ( p_api_version IN NUMBER
706 , p_init_msg_list IN VARCHAR2
707 , x_return_status OUT NOCOPY VARCHAR2
708 , x_msg_count OUT NOCOPY NUMBER
709 , x_msg_data OUT NOCOPY VARCHAR2
710 , p_sales_order_line_id IN NUMBER
711 , x_is_enabled OUT NOCOPY VARCHAR2
712 )
713 IS
714
715 l_api_name CONSTANT VARCHAR2(30) := 'Is_So_Line_Shikyu_Enabled';
716 l_api_version CONSTANT NUMBER := 1.0;
717
718 l_shikyu_enabled_so_line_count NUMBER := 0;
719
720 BEGIN
721
722 IF g_fnd_debug = 'Y' AND
723 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
724 THEN
725 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
726 , G_MODULE_PREFIX || l_api_name || '.invoked'
727 , 'Entry');
728 END IF;
729
730 -- Start API initialization
731 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
732 FND_MSG_PUB.initialize;
733 END IF;
734
735 IF NOT FND_API.Compatible_API_Call( l_api_version
736 , p_api_version
737 , l_api_name
738 , G_PKG_NAME
739 )
740 THEN
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
742 END IF;
743
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745 -- End API initialization
746
747 -- Returns 1 if the passed in Sales Order Line ID corresponds to a SHIKYU
748 -- enabled Replenishment SO Line stored in the JMF_SHIKYU_REPLENISHMENTS
749 -- table, or an SO Line splitted from a Replenishment SO Line
750 SELECT count('x')
751 INTO l_shikyu_enabled_so_line_count
752 FROM dual
753 WHERE exists
754 (SELECT 'X'
755 FROM oe_order_lines_all oola,
756 mtl_system_items_b msib,
757 jmf_shikyu_replenishments jsr
758 WHERE oola.inventory_item_id = msib.inventory_item_id
759 AND msib.organization_id = jsr.oem_organization_id
760 AND msib.subcontracting_component in (1, 2)
761 AND jsr.replenishment_so_line_id = oola.line_id
762 AND jsr.replenishment_so_line_id IN
763 (SELECT reference_line_id
764 FROM oe_order_lines_all
765 WHERE line_id = p_sales_order_line_id
766 )
767 );
768
769 -- Assign the boolean value depending on the return count
770 IF l_shikyu_enabled_so_line_count >= 1 THEN
771 x_is_enabled := 'Y';
772 ELSE
773 x_is_enabled := 'N';
774 END IF;
775
776 EXCEPTION
777
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779 FND_MSG_PUB.Count_And_Get
780 ( p_count => x_msg_count
781 , p_data => x_msg_data
782 );
783
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 IF g_fnd_debug = 'Y' AND
786 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
787 THEN
788 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
789 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
790 , 'Exception');
791 END IF;
792
793 WHEN OTHERS THEN
794 FND_MSG_PUB.Count_And_Get
795 ( p_count => x_msg_count
796 , p_data => x_msg_data
797 );
798
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 IF g_fnd_debug = 'Y' AND
801 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
802 THEN
803 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
804 , G_MODULE_PREFIX || l_api_name || '.others_exception'
805 , 'Exception');
806 END IF;
807 END Is_So_Line_Shikyu_Enabled;
808
809 --===========================================================================
810 -- API NAME : Validate_OSA_Flag
811 --
812 -- DESCRIPTION:
813 --
814 -- PARAMETERS :
815 -- IN :
816 -- OUT :
817 --
818 --
819 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
820 --
821 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
822 --===========================================================================
823 PROCEDURE Validate_Osa_Flag
824 ( p_api_version IN NUMBER
825 , p_init_msg_list IN VARCHAR2
826 , x_return_status OUT NOCOPY VARCHAR2
827 , x_msg_count OUT NOCOPY NUMBER
828 , x_msg_data OUT NOCOPY VARCHAR2
829 , p_inventory_item_id IN NUMBER
830 , p_vendor_id IN NUMBER
831 , p_vendor_site_id IN NUMBER
832 , p_ship_to_organization_id IN NUMBER
833 , x_osa_flag OUT NOCOPY VARCHAR2
834 )
835
836 IS
837
838 l_api_name CONSTANT VARCHAR2(30) := 'Validate_OSA_Flag';
839 l_api_version CONSTANT NUMBER := 1.0;
840 l_return_status varchar2(1) := NULL;
841 l_msg_count number := NULL;
842 l_msg_data varchar2(2000) := NULL;
843
844 l_last_billing_date DATE := NULL;
845 l_consigned_billing_cycle NUMBER := NULL;
846
847 l_tp_organization_id
848 MTL_PARAMETERS.organization_id%TYPE := NULL;
849 l_consigned_from_supplier_flag
850 PO_ASL_ATTRIBUTES.CONSIGNED_FROM_SUPPLIER_FLAG%TYPE := NULL;
851 l_enable_vmi_flag
852 PO_ASL_ATTRIBUTES.ENABLE_VMI_FLAG%TYPE := NULL;
853 l_ship_to_org_item_osa_flag
854 MTL_SYSTEM_ITEMS_B.OUTSOURCED_ASSEMBLY%TYPE := NULL;
855 l_tp_org_item_osa_flag
856 MTL_SYSTEM_ITEMS_B.OUTSOURCED_ASSEMBLY%TYPE := NULL;
857
858 BEGIN
859
860 IF g_fnd_debug = 'Y' AND
861 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
862 THEN
863 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
864 , G_MODULE_PREFIX || l_api_name || '.begin'
865 , NULL);
866 END IF;
867
868 -- Start API initialization
869 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
870 FND_MSG_PUB.initialize;
871 END IF;
872
873 IF NOT FND_API.Compatible_API_Call( l_api_version
874 , p_api_version
875 , l_api_name
876 , G_PKG_NAME
877 )
878 THEN
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881
882 x_return_status := FND_API.G_RET_STS_SUCCESS;
883
884 -- End API initialization
885
886 -- Initializes the output osa flag to 'Y'
887 x_osa_flag := 'Y';
888
889 -- Get the outsourced_assembly flag of the item having the passed
890 -- in item id and ship to organization id
891 SELECT msib.outsourced_assembly
892 INTO l_ship_to_org_item_osa_flag
893 FROM mtl_system_items_b msib
897 /* Validation of the item level OSA flag in the Ship to Organization */
894 WHERE msib.inventory_item_id = p_inventory_item_id
895 AND msib.organization_id = p_ship_to_organization_id;
896
898 IF l_ship_to_org_item_osa_flag <> 1
899 THEN
900 x_osa_flag := 'N';
901 END IF;
902
903 IF x_osa_flag <> 'N' AND
904 p_vendor_id IS NOT NULL AND
905 p_vendor_site_id IS NOT NULL
906 THEN
907
908 /* Validation of the item level OSA flag in the Trading Partner Organization */
909 SELECT DISTINCT hoi.organization_id,
910 msib.outsourced_assembly
911 INTO l_tp_organization_id,
912 l_tp_org_item_osa_flag
913 FROM HR_ORGANIZATION_INFORMATION hoi,
914 MTL_SYSTEM_ITEMS_B msib
915 WHERE hoi.org_information_context = 'Customer/Supplier Association'
916 AND hoi.org_information3 = p_vendor_id
917 AND hoi.org_information4 = p_vendor_site_id
918 AND msib.organization_id = hoi.organization_id
919 AND msib.inventory_item_id = p_inventory_item_id;
920
921 IF l_tp_org_item_osa_flag <> 1
922 THEN
923 x_osa_flag := 'N';
924 END IF;
925
926 IF x_osa_flag <> 'N'
927 THEN
928
929 /* Consigned Validation */
930 -- Check if the Supplier/Supplier Site/Ship to Organization/Item
931 -- combination corresponds to a consigned enabled ASL, if yes, set
932 -- the osa_flag to be 'N'
933 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes
934 ( p_api_version => 1.0
935 , p_init_msg_list => NULL
936 , x_return_status => l_return_status
937 , x_msg_count => l_msg_count
938 , x_msg_data => l_msg_data
939 , p_inventory_item_id => p_inventory_item_id
940 , p_vendor_id => p_vendor_id
941 , p_vendor_site_id => p_vendor_site_id
942 , p_using_organization_id => p_ship_to_organization_id
943 , x_consigned_from_supplier_flag => l_consigned_from_supplier_flag
944 , x_enable_vmi_flag => l_enable_vmi_flag
945 , x_last_billing_date => l_last_billing_date
946 , x_consigned_billing_cycle => l_consigned_billing_cycle
947 );
948
949 IF l_consigned_from_supplier_flag = 'Y'
950 THEN
951 x_osa_flag := 'N';
952 END IF;
953
954 END IF;
955
956 END IF;
957
958 IF g_fnd_debug = 'Y' AND
959 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
960 THEN
961 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
962 , G_MODULE_PREFIX || l_api_name || '.end'
963 , NULL);
964 END IF;
965
966 EXCEPTION
967 WHEN NO_DATA_FOUND THEN
968 FND_MSG_PUB.Count_And_Get
969 ( p_count => x_msg_count
970 , p_data => x_msg_data
971 );
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 IF g_fnd_debug = 'Y' AND
974 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
975 THEN
976 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
977 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
978 , 'No Data Found ');
979 END IF;
980
981 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
982 FND_MSG_PUB.Count_And_Get
983 ( p_count => x_msg_count
984 , p_data => x_msg_data
985 );
986
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
988 IF g_fnd_debug = 'Y' AND
989 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
990 THEN
991 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
992 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
993 , 'Exception');
994 END IF;
995
996 WHEN OTHERS THEN
997 FND_MSG_PUB.Count_And_Get
998 ( p_count => x_msg_count
999 , p_data => x_msg_data
1000 );
1001
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1003 IF g_fnd_debug = 'Y' AND
1004 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1005 THEN
1006 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1007 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1008 , 'Exception');
1009 END IF;
1010 END Validate_Osa_Flag;
1011
1012
1013 --===========================================================================
1014 -- API NAME : Get_Shikyu_Attributes
1015 --
1016 -- DESCRIPTION:
1017 --
1018 -- PARAMETERS :
1019 -- IN :
1020 -- OUT :
1021 --
1022 --
1023 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
1024 --
1025 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
1026 --===========================================================================
1027 PROCEDURE Get_Shikyu_Attributes
1028 ( p_api_version IN NUMBER
1029 , p_init_msg_list IN VARCHAR2
1030 , x_return_status OUT NOCOPY VARCHAR2
1031 , x_msg_count OUT NOCOPY NUMBER
1032 , x_msg_data OUT NOCOPY VARCHAR2
1033 , p_organization_id IN NUMBER
1034 , p_item_id IN NUMBER
1035 , x_outsourced_assembly OUT NOCOPY NUMBER
1036 , x_subcontracting_component OUT NOCOPY NUMBER
1037 )
1038 IS
1039
1040 l_api_name CONSTANT VARCHAR2(30) := 'Get_Shikyu_Attributes';
1041 l_api_version CONSTANT NUMBER := 1.0;
1042
1043 BEGIN
1044
1045 IF g_fnd_debug = 'Y' AND
1046 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1050 , 'Entry');
1047 THEN
1048 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1049 , G_MODULE_PREFIX || l_api_name || '.invoked'
1051 END IF;
1052
1053 -- Start API initialization
1054 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1055 FND_MSG_PUB.initialize;
1056 END IF;
1057
1058 IF NOT FND_API.Compatible_API_Call( l_api_version
1059 , p_api_version
1060 , l_api_name
1061 , G_PKG_NAME
1062 )
1063 THEN
1064 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1065 END IF;
1066
1067 x_return_status := FND_API.G_RET_STS_SUCCESS;
1068
1069 -- End API initialization
1070
1071 SELECT outsourced_assembly, subcontracting_component
1072 INTO X_outsourced_assembly, x_subcontracting_component
1073 FROM mtl_system_items_b
1074 WHERE organization_id = p_organization_id
1075 AND inventory_item_id = p_item_id;
1076
1077 EXCEPTION
1078 WHEN NO_DATA_FOUND THEN
1079 FND_MSG_PUB.Count_And_Get
1080 ( p_count => x_msg_count
1081 , p_data => x_msg_data
1082 );
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 IF g_fnd_debug = 'Y' AND
1085 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1086 THEN
1087 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1088 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1089 , 'No Data Found ');
1090 END IF;
1091
1092 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1093 FND_MSG_PUB.Count_And_Get
1094 ( p_count => x_msg_count
1095 , p_data => x_msg_data
1096 );
1097
1098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1099 IF g_fnd_debug = 'Y' AND
1100 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1101 THEN
1102 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1103 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1104 , 'Exception');
1105 END IF;
1106
1107 WHEN OTHERS THEN
1108 FND_MSG_PUB.Count_And_Get
1109 ( p_count => x_msg_count
1110 , p_data => x_msg_data
1111 );
1112
1113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1114 IF g_fnd_debug = 'Y' AND
1115 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1116 THEN
1117 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1118 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1119 , 'Exception');
1120 END IF;
1121 END Get_Shikyu_Attributes;
1122
1123 /* 12.1 Buy/Sell Subcontracting Changes */
1124 --===========================================================================
1125 -- API NAME : Get_subcontracting_type
1126 --
1127 -- DESCRIPTION: This parameters returns the subcontracting type established
1128 -- the OEM AND the MP organizations
1129 --
1130 -- PARAMETERS :
1131 -- IN :
1132 -- OUT : Returns 'B' for Buy/Sell Subcontracting
1133 -- Returns 'C' for Chargeable Subcontracting
1134 -- NULL otherwise
1135 --
1136 -- DESIGN REFERENCES: GBL_BUYSELL_TDD.doc
1137 --
1138 -- CHANGE HISTORY: 03-OCT-07 KDEVADAS Created.
1139 --===========================================================================
1140 FUNCTION Get_Subcontracting_Type
1141 ( p_oem_org_id IN NUMBER
1142 , p_mp_org_id IN NUMBER )
1143 RETURN VARCHAR2 IS
1144
1145 l_subcontracting_type VARCHAR2(1) ;
1146 l_api_name CONSTANT VARCHAR2(30) := 'Get_Subcontracting_Type';
1147 l_api_version CONSTANT NUMBER := 1.0;
1148 l_msg_data VARCHAR2(4000);
1149 l_msg_count NUMBER;
1150
1151 BEGIN
1152
1153 IF g_fnd_debug = 'Y' AND
1154 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1155 THEN
1156 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1157 , G_MODULE_PREFIX || l_api_name || '.invoked'
1158 , 'Entry');
1159 END IF;
1160
1161 l_subcontracting_type := NULL;
1162
1163 IF g_fnd_debug = 'Y' AND
1164 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1165 THEN
1166 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1167 , G_MODULE_PREFIX || l_api_name || '.invoked'
1168 , 'Before the query: subcontracting_type = "' || l_subcontracting_type || '"');
1169 END IF;
1170
1171 -- Start API initialization
1172 FND_MSG_PUB.initialize;
1173
1174
1175 SELECT mip.subcontracting_type
1176 INTO l_subcontracting_type
1177 FROM mtl_interorg_parameters mip
1178 WHERE mip.from_organization_id = p_oem_org_id
1179 AND mip.to_organization_id = p_mp_org_id;
1180
1181 IF g_fnd_debug = 'Y' AND
1182 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1183 THEN
1184 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1185 , G_MODULE_PREFIX || l_api_name || '.invoked'
1186 , 'After the query: subcontracting_type = "' || l_subcontracting_type || '"');
1187 END IF;
1188
1189
1190 RETURN l_subcontracting_type;
1191
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 IF g_fnd_debug = 'Y' AND
1195 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1196 THEN
1197 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1198 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1199 , 'No subcontracting relationship exists between the OEM and the MP');
1200 END IF;
1201 RETURN NULL;
1202 WHEN OTHERS THEN
1203 FND_MSG_PUB.Count_And_Get
1204 ( p_encoded => FND_API.G_FALSE
1205 , p_count => l_msg_count
1206 , p_data => l_msg_data
1207 );
1208 IF g_fnd_debug = 'Y' AND
1209 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1210 THEN
1211 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1212 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1213 , l_msg_data);
1214 END IF;
1215 RETURN NULL;
1216
1217 END Get_Subcontracting_Type;
1218
1219
1220
1221 END JMF_SHIKYU_GRP;