[Home] [Help]
PACKAGE BODY: APPS.JMF_SHIKYU_GRP
Source
1 PACKAGE BODY JMF_SHIKYU_GRP as
2 --$Header: JMFGSHKB.pls 120.13 2007/12/28 09:18:18 kdevadas 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 BEGIN
265
266 IF g_fnd_debug = 'Y' AND
267 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
268 THEN
269 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
270 , G_MODULE_PREFIX || l_api_name || '.invoked'
271 , 'Entry');
272 END IF;
273
274 -- Start API initialization
275 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
276 FND_MSG_PUB.initialize;
277 END IF;
278
279 /*
280 IF NOT FND_API.Compatible_API_Call( l_api_version
281 , p_api_version
282 , l_api_name
283 , G_PKG_NAME
284 )
285 THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288 */
289
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291 -- End API initialization
292
293 x_osa_flag := 'N';
294
295 IF g_fnd_debug = 'Y' AND
296 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
297 THEN
298 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
299 , G_MODULE_PREFIX || l_api_name || '.invoked'
300 , 'Before the query: p_po_shipment_id = "' || p_po_shipment_id || '"');
301 END IF;
302
303 -- Selecting the shikyu_osa_item_flag of PO Line
304 -- with the passed in PO Line ID
305 SELECT nvl(outsourced_assembly, 2)
306 INTO l_outsourced_assembly
307 FROM po_line_locations_all poll
308 WHERE poll.line_location_id = p_po_shipment_id;
309
310 IF g_fnd_debug = 'Y' AND
311 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
312 THEN
313 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
314 , G_MODULE_PREFIX || l_api_name || '.invoked'
315 , 'After the query: l_outsourced_assembly = ' || l_outsourced_assembly);
316 END IF;
317
318 IF l_outsourced_assembly = 1
319 THEN
320 x_osa_flag := 'Y';
321 ELSE
322 x_osa_flag := 'N';
323 END IF;
324
325 IF g_fnd_debug = 'Y' AND
326 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
327 THEN
328 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
329 , G_MODULE_PREFIX || l_api_name || '.invoked'
330 , 'Exit: Returning x_osa_flag = "' || x_osa_flag || '", x_return_status = "' || x_return_status || '"');
331 END IF;
332
333 EXCEPTION
334 WHEN NO_DATA_FOUND THEN
335 FND_MSG_PUB.Count_And_Get
336 ( p_count => x_msg_count
337 , p_data => x_msg_data
338 );
339 x_msg_data := 'NO_DATA_FOUND: p_po_shipment_id = ' || p_po_shipment_id;
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 IF g_fnd_debug = 'Y' AND
342 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
343 THEN
344 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
345 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
346 , 'The PO Line does not exist.');
347 END IF;
348
349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count
352 , p_data => x_msg_data
353 );
354 x_msg_data := 'FND_API.G_EXC_UNEXPECTED_ERROR: p_po_shipment_id = ' || p_po_shipment_id;
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 IF g_fnd_debug = 'Y' AND
357 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
358 THEN
359 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
360 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
361 , 'Exception');
362 END IF;
363
364 WHEN OTHERS THEN
365 FND_MSG_PUB.Count_And_Get
366 ( p_count => x_msg_count
367 , p_data => x_msg_data
368 );
369 x_msg_data := 'OTHER EXCEPTIONS: p_po_shipment_id = ' || p_po_shipment_id;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 IF g_fnd_debug = 'Y' AND
372 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
373 THEN
374 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
375 , G_MODULE_PREFIX || l_api_name || '.others_exception'
376 , 'Exception');
377 END IF;
378 END Get_Po_Shipment_Osa_Flag;
379
380 --===========================================================================
381 -- API NAME : Is_Tp_Organization
382 --
383 -- DESCRIPTION:
384 --
385 -- PARAMETERS :
386 -- IN :
387 -- OUT :
388 --
389 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
390 --
391 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
392 --===========================================================================
393 PROCEDURE Is_Tp_Organization
394 ( p_api_version IN NUMBER
395 , p_init_msg_list IN VARCHAR2
396 , x_return_status OUT NOCOPY VARCHAR2
397 , x_msg_count OUT NOCOPY NUMBER
398 , x_msg_data OUT NOCOPY VARCHAR2
399 , p_organization_id IN NUMBER
400 , x_is_tp_org_flag OUT NOCOPY VARCHAR2
401 )
402 IS
403
404 l_api_name CONSTANT VARCHAR2(30) := 'Is_Tp_Organization';
405 l_api_version CONSTANT NUMBER := 1.0;
406
407 BEGIN
408
409 IF g_fnd_debug = 'Y' AND
410 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
411 THEN
412 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
413 , G_MODULE_PREFIX || l_api_name || '.invoked'
414 , 'Entry');
415 END IF;
416
417 -- Start API initialization
418 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
419 FND_MSG_PUB.initialize;
420 END IF;
421
422 IF NOT FND_API.Compatible_API_Call( l_api_version
423 , p_api_version
424 , l_api_name
425 , G_PKG_NAME
429 END IF;
426 )
427 THEN
428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432 -- End API initialization
433
434 SELECT nvl(trading_partner_org_flag, 'N')
435 INTO x_is_tp_org_flag
436 FROM MTL_PARAMETERS
437 WHERE organization_id = p_organization_id;
438
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 FND_MSG_PUB.Count_And_Get
442 ( p_count => x_msg_count
443 , p_data => x_msg_data
444 );
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 IF g_fnd_debug = 'Y' AND
447 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
448 THEN
449 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
450 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
451 , 'The Inventory Organization does not exist.');
452 END IF;
453
454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
455 FND_MSG_PUB.Count_And_Get
456 ( p_count => x_msg_count
457 , p_data => x_msg_data
458 );
459
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461 IF g_fnd_debug = 'Y' AND
462 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
463 THEN
464 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
465 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
466 , 'Exception');
467 END IF;
468
469 WHEN OTHERS THEN
470 FND_MSG_PUB.Count_And_Get
471 ( p_count => x_msg_count
472 , p_data => x_msg_data
473 );
474
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 IF g_fnd_debug = 'Y' AND
477 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
478 THEN
479 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
480 , G_MODULE_PREFIX || l_api_name || '.others_exception'
481 , 'Exception');
482 END IF;
483 END Is_Tp_Organization;
484
485 --===========================================================================
486 -- API NAME : Is_AP_Invoice_Shikyu_Nettable
487 --
488 -- DESCRIPTION: This is the backtracking API to determine if an AP invoice
489 -- is eligible for SHIKYU Netting. It looks at one of the
490 -- distribution lines of the invoice and backtrack to the
491 -- corresponding PO Shipment to get the OSA_FLAG flag.
492 -- Since ERS would not comingle SHIKYU and non-SHIKYU lines
493 -- into the same AP invoice, an AP invoice would be containing
494 -- all SHIKYU distribution lines and thus would be SHIKYU
495 -- netting eligible if any one of the AP invoice distribution
496 -- lines can be backtracked to a PO Shipment created for an
497 -- Outsourced Assembly item.
498 --
499 -- PARAMETERS :
500 -- IN :
501 -- OUT :
502 --
503 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
504 --
505 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
506 --===========================================================================
507 PROCEDURE Is_AP_Invoice_Shikyu_Nettable
508 ( p_api_version IN NUMBER
509 , p_init_msg_list IN VARCHAR2
510 , x_return_status OUT NOCOPY VARCHAR2
511 , x_msg_count OUT NOCOPY NUMBER
512 , x_msg_data OUT NOCOPY VARCHAR2
513 , p_ap_invoice_id IN NUMBER
514 , x_nettable OUT NOCOPY VARCHAR2
515 )
516 IS
517
518 l_api_name CONSTANT VARCHAR2(30) := 'Is_AP_Invoice_Shikyu_Nettable';
519 l_api_version CONSTANT NUMBER := 1.0;
520
521 l_outsourced_assembly NUMBER := NULL;
522
523 BEGIN
524
525 IF g_fnd_debug = 'Y' AND
526 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
527 THEN
528 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
529 , G_MODULE_PREFIX || l_api_name || '.invoked'
530 , 'Entry');
531 END IF;
532
533 -- Start API initialization
534 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537
538 IF NOT FND_API.Compatible_API_Call( l_api_version
539 , p_api_version
540 , l_api_name
541 , G_PKG_NAME
542 )
543 THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END IF;
546
547 x_return_status := FND_API.G_RET_STS_SUCCESS;
548 -- End API initialization
549
550 x_nettable := 'N';
551
552 -- Selecting the shikyu_osa_item_flag of the underlying PO Line
553 -- of the AP distribution line of the invoice with the passed in
554 -- invoice ID having the smallest distribution_line_number.
555 -- We can conclude whether an invoice is SHIKYU netting
556 -- eligible by examining one of the distribution line of the
557 -- invoice, since ERS would not comingle SHIKYU and
558 -- non-SHIKYU lines.
559
560 -- Bug 5574912: Added the distinct keyword since there can be
564 -- 2 (No), since the ERS program creates separate AP invoices
561 -- multiple PO Shipments associated to a single AP Invoice
562 -- Distribution. We can assume the Outsourced_Assembly flag
563 -- of all of these PO Shipments would be either 1 (Yes) or
565 -- for PO Shipments PO Shipments with the Outsourced_Assembly
566 -- flag checked, and those with the flag unchecked.
567
568 SELECT DISTINCT NVL(plla.outsourced_assembly, 2)
569 INTO l_outsourced_assembly
570 FROM ap_invoice_distributions_all apd,
571 po_distributions_all pda,
572 po_line_locations_all plla
573 WHERE apd.po_distribution_id = pda.po_distribution_id
574 AND pda.line_location_id = plla.line_location_id
575 AND apd.invoice_id = p_ap_invoice_id
576 AND apd.distribution_line_number =
577 (SELECT MIN(distribution_line_number)
578 FROM ap_invoice_distributions_all
579 WHERE invoice_id = p_ap_invoice_id);
580
581 IF l_outsourced_assembly = 1
582 THEN
583 x_nettable := 'Y';
584 ELSE
585 x_nettable := 'N';
586 END IF;
587
588 EXCEPTION
589 WHEN NO_DATA_FOUND THEN
590 FND_MSG_PUB.Count_And_Get
591 ( p_count => x_msg_count
592 , p_data => x_msg_data
593 );
594 x_return_status := FND_API.G_RET_STS_ERROR;
595 IF g_fnd_debug = 'Y' AND
596 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
597 THEN
598 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
599 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
600 , 'The AP Invoice does not exist.');
601 END IF;
602
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 FND_MSG_PUB.Count_And_Get
605 ( p_count => x_msg_count
606 , p_data => x_msg_data
607 );
608
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
610 IF g_fnd_debug = 'Y' AND
611 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
612 THEN
613 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
614 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
615 , 'Exception');
616 END IF;
617
618 WHEN OTHERS THEN
619 FND_MSG_PUB.Count_And_Get
620 ( p_count => x_msg_count
621 , p_data => x_msg_data
622 );
623
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
625 IF g_fnd_debug = 'Y' AND
626 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
627 THEN
628 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
629 , G_MODULE_PREFIX || l_api_name || '.others_exception'
630 , 'Exception');
631 END IF;
632 END Is_AP_Invoice_Shikyu_Nettable;
633
634 --===========================================================================
635 -- API NAME : Is_AP_Inv_Shikyu_Nettable_Func
636 --
637 -- DESCRIPTION: This function calls the Is_AP_Invoice_Shikyu_Nettable
638 -- procedure and return the value passed back to the OUT
639 -- parameter x_nettable.
640 --
641 -- PARAMETERS :
642 -- IN :
643 -- OUT :
644 --
645 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
646 --
647 -- CHANGE HISTORY: 19-Sep-05 VCHU Created.
648 --===========================================================================
649 FUNCTION Is_AP_Inv_Shikyu_Nettable_Func
650 ( p_ap_invoice_id IN NUMBER
651 )
652 RETURN VARCHAR2
653 IS
654 l_return_status VARCHAR2(1);
655 l_msg_count NUMBER;
656 l_msg_data VARCHAR2(2000);
657 l_nettable VARCHAR2(1);
658 BEGIN
659
660 Is_AP_Invoice_Shikyu_Nettable
661 ( p_api_version => 1.0
662 , p_init_msg_list => NULL
663 , x_return_status => l_return_status
664 , x_msg_count => l_msg_count
665 , x_msg_data => l_msg_data
666 , p_ap_invoice_id => p_ap_invoice_id
667 , x_nettable => l_nettable
668 );
669 RETURN l_nettable;
670
671 END Is_AP_Inv_Shikyu_Nettable_Func;
672
673 --===========================================================================
674 -- API NAME : Is_So_Line_Shikyu_Enabled
675 --
676 -- DESCRIPTION:
677 --
678 -- PARAMETERS :
679 -- IN :
680 -- OUT :
681 --
682 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
683 --
684 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
685 --===========================================================================
686 PROCEDURE Is_So_Line_Shikyu_Enabled
687 ( p_api_version IN NUMBER
688 , p_init_msg_list IN VARCHAR2
689 , x_return_status OUT NOCOPY VARCHAR2
690 , x_msg_count OUT NOCOPY NUMBER
691 , x_msg_data OUT NOCOPY VARCHAR2
692 , p_sales_order_line_id IN NUMBER
693 , x_is_enabled OUT NOCOPY VARCHAR2
694 )
695 IS
696
697 l_api_name CONSTANT VARCHAR2(30) := 'Is_So_Line_Shikyu_Enabled';
698 l_api_version CONSTANT NUMBER := 1.0;
699
700 l_shikyu_enabled_so_line_count NUMBER := 0;
701
702 BEGIN
703
707 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
704 IF g_fnd_debug = 'Y' AND
705 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
706 THEN
708 , G_MODULE_PREFIX || l_api_name || '.invoked'
709 , 'Entry');
710 END IF;
711
712 -- Start API initialization
713 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 IF NOT FND_API.Compatible_API_Call( l_api_version
718 , p_api_version
719 , l_api_name
720 , G_PKG_NAME
721 )
722 THEN
723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724 END IF;
725
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727 -- End API initialization
728
729 -- Returns 1 if the passed in Sales Order Line ID corresponds to a SHIKYU
730 -- enabled Replenishment SO Line stored in the JMF_SHIKYU_REPLENISHMENTS
731 -- table, or an SO Line splitted from a Replenishment SO Line
732 SELECT count('x')
733 INTO l_shikyu_enabled_so_line_count
734 FROM dual
735 WHERE exists
736 (SELECT 'X'
737 FROM oe_order_lines_all oola,
738 mtl_system_items_b msib,
739 jmf_shikyu_replenishments jsr
740 WHERE oola.inventory_item_id = msib.inventory_item_id
741 AND msib.organization_id = jsr.oem_organization_id
742 AND msib.subcontracting_component in (1, 2)
743 AND jsr.replenishment_so_line_id = oola.line_id
744 AND jsr.replenishment_so_line_id IN
745 (SELECT reference_line_id
746 FROM oe_order_lines_all
747 WHERE line_id = p_sales_order_line_id
748 )
749 );
750
751 -- Assign the boolean value depending on the return count
752 IF l_shikyu_enabled_so_line_count >= 1 THEN
753 x_is_enabled := 'Y';
754 ELSE
755 x_is_enabled := 'N';
756 END IF;
757
758 EXCEPTION
759
760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
761 FND_MSG_PUB.Count_And_Get
762 ( p_count => x_msg_count
763 , p_data => x_msg_data
764 );
765
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
767 IF g_fnd_debug = 'Y' AND
768 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
769 THEN
770 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
771 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
772 , 'Exception');
773 END IF;
774
775 WHEN OTHERS THEN
776 FND_MSG_PUB.Count_And_Get
777 ( p_count => x_msg_count
778 , p_data => x_msg_data
779 );
780
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 IF g_fnd_debug = 'Y' AND
783 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
784 THEN
785 FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
786 , G_MODULE_PREFIX || l_api_name || '.others_exception'
787 , 'Exception');
788 END IF;
789 END Is_So_Line_Shikyu_Enabled;
790
791 --===========================================================================
792 -- API NAME : Validate_OSA_Flag
793 --
794 -- DESCRIPTION:
795 --
796 -- PARAMETERS :
797 -- IN :
798 -- OUT :
799 --
800 --
801 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
802 --
803 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
804 --===========================================================================
805 PROCEDURE Validate_Osa_Flag
806 ( p_api_version IN NUMBER
807 , p_init_msg_list IN VARCHAR2
808 , x_return_status OUT NOCOPY VARCHAR2
809 , x_msg_count OUT NOCOPY NUMBER
810 , x_msg_data OUT NOCOPY VARCHAR2
811 , p_inventory_item_id IN NUMBER
812 , p_vendor_id IN NUMBER
813 , p_vendor_site_id IN NUMBER
814 , p_ship_to_organization_id IN NUMBER
815 , x_osa_flag OUT NOCOPY VARCHAR2
816 )
817
818 IS
819
820 l_api_name CONSTANT VARCHAR2(30) := 'Validate_OSA_Flag';
821 l_api_version CONSTANT NUMBER := 1.0;
822 l_return_status varchar2(1) := NULL;
823 l_msg_count number := NULL;
824 l_msg_data varchar2(2000) := NULL;
825
826 l_last_billing_date DATE := NULL;
827 l_consigned_billing_cycle NUMBER := NULL;
828
829 l_tp_organization_id
830 MTL_PARAMETERS.organization_id%TYPE := NULL;
831 l_consigned_from_supplier_flag
832 PO_ASL_ATTRIBUTES.CONSIGNED_FROM_SUPPLIER_FLAG%TYPE := NULL;
833 l_enable_vmi_flag
834 PO_ASL_ATTRIBUTES.ENABLE_VMI_FLAG%TYPE := NULL;
835 l_ship_to_org_item_osa_flag
836 MTL_SYSTEM_ITEMS_B.OUTSOURCED_ASSEMBLY%TYPE := NULL;
837 l_tp_org_item_osa_flag
838 MTL_SYSTEM_ITEMS_B.OUTSOURCED_ASSEMBLY%TYPE := NULL;
839
840 BEGIN
841
842 IF g_fnd_debug = 'Y' AND
843 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
844 THEN
845 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
846 , G_MODULE_PREFIX || l_api_name || '.begin'
847 , NULL);
848 END IF;
849
850 -- Start API initialization
851 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
852 FND_MSG_PUB.initialize;
853 END IF;
854
855 IF NOT FND_API.Compatible_API_Call( l_api_version
856 , p_api_version
857 , l_api_name
858 , G_PKG_NAME
859 )
860 THEN
861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
862 END IF;
863
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865
866 -- End API initialization
867
868 -- Initializes the output osa flag to 'Y'
869 x_osa_flag := 'Y';
870
871 -- Get the outsourced_assembly flag of the item having the passed
872 -- in item id and ship to organization id
873 SELECT msib.outsourced_assembly
874 INTO l_ship_to_org_item_osa_flag
875 FROM mtl_system_items_b msib
876 WHERE msib.inventory_item_id = p_inventory_item_id
877 AND msib.organization_id = p_ship_to_organization_id;
878
879 /* Validation of the item level OSA flag in the Ship to Organization */
880 IF l_ship_to_org_item_osa_flag <> 1
881 THEN
882 x_osa_flag := 'N';
883 END IF;
884
885 IF x_osa_flag <> 'N' AND
886 p_vendor_id IS NOT NULL AND
887 p_vendor_site_id IS NOT NULL
888 THEN
889
890 /* Validation of the item level OSA flag in the Trading Partner Organization */
891 SELECT DISTINCT hoi.organization_id,
892 msib.outsourced_assembly
893 INTO l_tp_organization_id,
894 l_tp_org_item_osa_flag
895 FROM HR_ORGANIZATION_INFORMATION hoi,
896 MTL_SYSTEM_ITEMS_B msib
897 WHERE hoi.org_information_context = 'Customer/Supplier Association'
898 AND hoi.org_information3 = p_vendor_id
899 AND hoi.org_information4 = p_vendor_site_id
900 AND msib.organization_id = hoi.organization_id
901 AND msib.inventory_item_id = p_inventory_item_id;
902
903 IF l_tp_org_item_osa_flag <> 1
904 THEN
905 x_osa_flag := 'N';
906 END IF;
907
908 IF x_osa_flag <> 'N'
909 THEN
910
911 /* Consigned Validation */
912 -- Check if the Supplier/Supplier Site/Ship to Organization/Item
913 -- combination corresponds to a consigned enabled ASL, if yes, set
914 -- the osa_flag to be 'N'
915 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes
916 ( p_api_version => 1.0
917 , p_init_msg_list => NULL
918 , x_return_status => l_return_status
919 , x_msg_count => l_msg_count
920 , x_msg_data => l_msg_data
921 , p_inventory_item_id => p_inventory_item_id
922 , p_vendor_id => p_vendor_id
923 , p_vendor_site_id => p_vendor_site_id
924 , p_using_organization_id => p_ship_to_organization_id
925 , x_consigned_from_supplier_flag => l_consigned_from_supplier_flag
926 , x_enable_vmi_flag => l_enable_vmi_flag
927 , x_last_billing_date => l_last_billing_date
931 IF l_consigned_from_supplier_flag = 'Y'
928 , x_consigned_billing_cycle => l_consigned_billing_cycle
929 );
930
932 THEN
933 x_osa_flag := 'N';
934 END IF;
935
936 END IF;
937
938 END IF;
939
940 IF g_fnd_debug = 'Y' AND
941 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
942 THEN
943 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
944 , G_MODULE_PREFIX || l_api_name || '.end'
945 , NULL);
946 END IF;
947
948 EXCEPTION
949 WHEN NO_DATA_FOUND THEN
950 FND_MSG_PUB.Count_And_Get
951 ( p_count => x_msg_count
952 , p_data => x_msg_data
953 );
954 x_return_status := FND_API.G_RET_STS_ERROR;
955 IF g_fnd_debug = 'Y' AND
956 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
957 THEN
958 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
959 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
960 , 'No Data Found ');
961 END IF;
962
963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
964 FND_MSG_PUB.Count_And_Get
965 ( p_count => x_msg_count
966 , p_data => x_msg_data
967 );
968
969 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
970 IF g_fnd_debug = 'Y' AND
971 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
972 THEN
973 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
974 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
975 , 'Exception');
976 END IF;
977
978 WHEN OTHERS THEN
979 FND_MSG_PUB.Count_And_Get
980 ( p_count => x_msg_count
981 , p_data => x_msg_data
982 );
983
984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
985 IF g_fnd_debug = 'Y' AND
986 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
987 THEN
988 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
989 , G_MODULE_PREFIX || l_api_name || '.others_exception'
990 , 'Exception');
991 END IF;
992 END Validate_Osa_Flag;
993
994
995 --===========================================================================
996 -- API NAME : Get_Shikyu_Attributes
997 --
998 -- DESCRIPTION:
999 --
1000 -- PARAMETERS :
1001 -- IN :
1002 -- OUT :
1003 --
1004 --
1005 -- DESIGN REFERENCES: SHIKYU_GRP_API_TD.doc
1006 --
1007 -- CHANGE HISTORY: 21-Apr-05 VCHU Created.
1008 --===========================================================================
1009 PROCEDURE Get_Shikyu_Attributes
1010 ( p_api_version IN NUMBER
1011 , p_init_msg_list IN VARCHAR2
1012 , x_return_status OUT NOCOPY VARCHAR2
1013 , x_msg_count OUT NOCOPY NUMBER
1014 , x_msg_data OUT NOCOPY VARCHAR2
1015 , p_organization_id IN NUMBER
1016 , p_item_id IN NUMBER
1017 , x_outsourced_assembly OUT NOCOPY NUMBER
1018 , x_subcontracting_component OUT NOCOPY NUMBER
1019 )
1020 IS
1021
1022 l_api_name CONSTANT VARCHAR2(30) := 'Get_Shikyu_Attributes';
1023 l_api_version CONSTANT NUMBER := 1.0;
1024
1025 BEGIN
1026
1027 IF g_fnd_debug = 'Y' AND
1028 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1029 THEN
1030 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1031 , G_MODULE_PREFIX || l_api_name || '.invoked'
1032 , 'Entry');
1033 END IF;
1034
1035 -- Start API initialization
1036 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1037 FND_MSG_PUB.initialize;
1038 END IF;
1039
1040 IF NOT FND_API.Compatible_API_Call( l_api_version
1041 , p_api_version
1042 , l_api_name
1043 , G_PKG_NAME
1044 )
1045 THEN
1046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1047 END IF;
1048
1049 x_return_status := FND_API.G_RET_STS_SUCCESS;
1050
1051 -- End API initialization
1052
1053 SELECT outsourced_assembly, subcontracting_component
1054 INTO X_outsourced_assembly, x_subcontracting_component
1055 FROM mtl_system_items_b
1056 WHERE organization_id = p_organization_id
1057 AND inventory_item_id = p_item_id;
1058
1059 EXCEPTION
1060 WHEN NO_DATA_FOUND THEN
1061 FND_MSG_PUB.Count_And_Get
1062 ( p_count => x_msg_count
1066 IF g_fnd_debug = 'Y' AND
1063 , p_data => x_msg_data
1064 );
1065 x_return_status := FND_API.G_RET_STS_ERROR;
1067 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1068 THEN
1069 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1070 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1071 , 'No Data Found ');
1072 END IF;
1073
1074 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1075 FND_MSG_PUB.Count_And_Get
1076 ( p_count => x_msg_count
1077 , p_data => x_msg_data
1078 );
1079
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081 IF g_fnd_debug = 'Y' AND
1082 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1083 THEN
1084 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1085 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1086 , 'Exception');
1087 END IF;
1088
1089 WHEN OTHERS THEN
1090 FND_MSG_PUB.Count_And_Get
1091 ( p_count => x_msg_count
1092 , p_data => x_msg_data
1093 );
1094
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1096 IF g_fnd_debug = 'Y' AND
1097 FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1098 THEN
1099 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1100 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1101 , 'Exception');
1102 END IF;
1103 END Get_Shikyu_Attributes;
1104
1105 /* 12.1 Buy/Sell Subcontracting Changes */
1106 --===========================================================================
1107 -- API NAME : Get_subcontracting_type
1108 --
1109 -- DESCRIPTION: This parameters returns the subcontracting type established
1110 -- the OEM AND the MP organizations
1111 --
1112 -- PARAMETERS :
1113 -- IN :
1114 -- OUT : Returns 'B' for Buy/Sell Subcontracting
1115 -- Returns 'C' for Chargeable Subcontracting
1116 -- NULL otherwise
1117 --
1118 -- DESIGN REFERENCES: GBL_BUYSELL_TDD.doc
1119 --
1120 -- CHANGE HISTORY: 03-OCT-07 KDEVADAS Created.
1121 --===========================================================================
1122 FUNCTION Get_Subcontracting_Type
1123 ( p_oem_org_id IN NUMBER
1124 , p_mp_org_id IN NUMBER )
1125 RETURN VARCHAR2 IS
1126
1127 l_subcontracting_type VARCHAR2(1) ;
1128 l_api_name CONSTANT VARCHAR2(30) := 'Get_Subcontracting_Type';
1129 l_api_version CONSTANT NUMBER := 1.0;
1130 l_msg_data VARCHAR2(4000);
1131 l_msg_count NUMBER;
1132
1133 BEGIN
1134
1135 IF g_fnd_debug = 'Y' AND
1136 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1137 THEN
1138 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1139 , G_MODULE_PREFIX || l_api_name || '.invoked'
1140 , 'Entry');
1141 END IF;
1142
1143 l_subcontracting_type := NULL;
1144
1145 IF g_fnd_debug = 'Y' AND
1146 FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1147 THEN
1148 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1149 , G_MODULE_PREFIX || l_api_name || '.invoked'
1150 , 'Before the query: subcontracting_type = "' || l_subcontracting_type || '"');
1151 END IF;
1152
1153 -- Start API initialization
1154 FND_MSG_PUB.initialize;
1155
1156
1157 SELECT mip.subcontracting_type
1158 INTO l_subcontracting_type
1159 FROM mtl_interorg_parameters mip
1160 WHERE mip.from_organization_id = p_oem_org_id
1161 AND mip.to_organization_id = p_mp_org_id;
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 , 'After the query: subcontracting_type = "' || l_subcontracting_type || '"');
1169 END IF;
1170
1171
1172 RETURN l_subcontracting_type;
1173
1174 EXCEPTION
1175 WHEN NO_DATA_FOUND THEN
1176 IF g_fnd_debug = 'Y' AND
1177 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1178 THEN
1179 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1180 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1181 , 'No subcontracting relationship exists between the OEM and the MP');
1182 END IF;
1183 RETURN NULL;
1184 WHEN OTHERS THEN
1185 FND_MSG_PUB.Count_And_Get
1186 ( p_encoded => FND_API.G_FALSE
1187 , p_count => l_msg_count
1188 , p_data => l_msg_data
1189 );
1190 IF g_fnd_debug = 'Y' AND
1191 FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
1192 THEN
1193 FND_LOG.string( FND_LOG.LEVEL_EXCEPTION
1194 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1195 , l_msg_data);
1196 END IF;
1197 RETURN NULL;
1198
1199 END Get_Subcontracting_Type;
1200
1201
1202
1203 END JMF_SHIKYU_GRP;