1 PACKAGE BODY JMF_SUBCONTRCT_DIAG_UTIL AS
2 /* $Header: JMFDUSBB.pls 120.0.12010000.2 2010/06/28 06:29:43 abhissri ship $ */
3
4 --+===========================================================================+
5 --| Copyright (c) 2005 Oracle Corporation |
6 --| Redwood Shores, California, USA |
7 --| All rights reserved. |
8 --+===========================================================================+
9 --| |
10 --| FILENAME : JMFDUSBB.pls |
11 --| |
12 --| DESCRIPTION: Package body file for Subcontracting Diagnostics |
13 --| Utility Package |
14 --| |
15 --| HISTORY: |
16 --| 20-DEC-2007 kdevadas Created. |
17 --+===========================================================================+
18
19 --=============================================
20 -- GLOBALS
21 --=============================================
22 --=============================================
23 -- PROCEDURES AND FUNCTIONS
24 --=============================================
25 --========================================================================
26 -- FUNCTION : Check_Profiles PUBLIC
27 -- PARAMETERS: NONE
28 -- COMMENT : This function checks for the profile options applicable to
29 -- Subcontracting and displays the profile options values, if set.
30 -- Returns SUCCESS only if all the profiles are set correctly
31 --========================================================================
32
33 FUNCTION Check_Profiles RETURN VARCHAR2 IS
34 l_statusStr VARCHAR2(10);
35 BEGIN
36 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
37 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>1. CHECKING PROFILES</u>');
38 IF JTF_DIAGNOSTIC_COREAPI.CheckProfile('JMF_SHK_CHARGE_BASED_ENABLED', NULL, NULL, NULL, null) = NULL THEN
39 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
40 END IF;
41 IF JTF_DIAGNOSTIC_COREAPI.CheckProfile('XLA_MO_SECURITY_PROFILE_LEVEL', NULL, NULL, NULL, null) = NULL THEN
42 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
43 END IF ;
44 IF JTF_DIAGNOSTIC_COREAPI.CheckProfile('DEFAULT_ORG_ID', NULL, NULL, NULL, null) = NULL THEN
45 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
46 END IF;
47 JTF_DIAGNOSTIC_COREAPI.BRPrint;
48 RETURN l_statusStr;
49 END Check_Profiles;
50
51 --========================================================================
52 -- FUNCTION : Check_WIP_Parameters PUBLIC
53 -- PARAMETERS: NONE
54 -- COMMENT : This function displays all the Manufacturing Partner
55 -- organizations for which WIP Parameters have not been defined.
56 -- Returns SUCCESS only if all the MP orgs have WIP parameters
57 -- defined.
58 --========================================================================
59 FUNCTION Check_WIP_Parameters RETURN VARCHAR2 IS
60 l_count NUMBER := 0;
61 l_sqltxt VARCHAR2(2000);
62 l_statusStr VARCHAR2(10);
63 BEGIN
64 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
65 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>2. CHECKING WIP PARAMETERS</u>');
66 JTF_DIAGNOSTIC_COREAPI.line_out('WIP Parameters have not been defined for the following MP Organizations:');
67 JTF_DIAGNOSTIC_COREAPI.BRPrint;
68 l_sqltxt := ' SELECT
69 organization_id "MP Organization Id",
70 organization_code "MP Organization Code"
71 FROM MTL_PARAMETERS mp
72 WHERE trading_partner_org_flag = ''Y''
73 AND NOT EXISTS
74 (SELECT 1 FROM WIP_PARAMETERS wp
75 WHERE mp.organization_id = wp.organization_id)';
76
77 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
78 JTF_DIAGNOSTIC_COREAPI.BRPrint;
79 IF l_count >0 THEN
80 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter WIP Parameters for these MP Organizations');
81 JTF_DIAGNOSTIC_COREAPI.BRPrint;
82 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
83 END IF;
84 RETURN l_statusStr;
85
86 END Check_WIP_Parameters;
87
88 --========================================================================
89 -- FUNCTION : Check_Accounting_Periods PUBLIC
90 -- PARAMETERS: NONE
91 -- COMMENT : This function displays all the Manufacturing Partner
92 -- organizations for which Inventory Accounting Periods are
93 -- not open. Returns SUCCESS only if all the MP orgs have open
94 -- accounting periods for the current date.
95 --========================================================================
96 FUNCTION Check_Accounting_Periods RETURN VARCHAR2 IS
97 l_count NUMBER := 0;
98 l_sqltxt VARCHAR2(2000);
99 l_statusStr VARCHAR2(10);
100 BEGIN
101 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
102 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>3. CHECKING ACCOUNTING PERIODS</u>');
103 JTF_DIAGNOSTIC_COREAPI.line_out('Inventory accounting periods are not open in the following MP organizations:');
104 JTF_DIAGNOSTIC_COREAPI.BRPrint;
105 l_sqltxt := ' SELECT
106 organization_id "MP Organization Id",
107 organization_code "MP Organization Code"
108 FROM mtl_parameters mp
109 WHERE trading_partner_org_flag = ''Y''
110 AND NOT EXISTS
111 (SELECT 1
112 FROM org_acct_periods oap
113 WHERE oap.organization_id = mp.organization_id
114 AND (Trunc(period_start_date) < Trunc(SYSDATE)
115 AND Trunc(schedule_close_date) > Trunc(SYSDATE))
116 AND open_flag = ''Y'' )';
117
118 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
119 JTF_DIAGNOSTIC_COREAPI.BRPrint;
120 IF l_count >0 THEN
121 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open accounting periods in these MP organizations');
122 JTF_DIAGNOSTIC_COREAPI.BRPrint;
123 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
124 END IF;
125 RETURN l_statusStr;
126
127 END Check_Accounting_Periods;
128
129
130 --========================================================================
131 -- FUNCTION : Check_Routings PUBLIC
132 -- PARAMETERS: NONE
133 -- COMMENT : This function displays all the Manufacturing Partner
134 -- organizations in which Routings are defined for Outsourced
135 -- Assembly items. For the Subcontracting feature, Routings must
136 -- NOT be defined in the MP org for Outsourced Assemblies.
137 -- Returns SUCCESS only if none of the Outsourced Assemblies have
138 -- routings defined for them
139 --========================================================================
140
141 FUNCTION Check_Routings RETURN VARCHAR2 IS
142 l_count NUMBER := 0;
143 l_sqltxt VARCHAR2(2000);
144 l_statusStr VARCHAR2(10);
145 BEGIN
146 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
147 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>4. CHECKING ROUTINGS</u>');
148 JTF_DIAGNOSTIC_COREAPI.line_out('Routings are defined for the following Outsourced Assembly items in MP organizations:');
149 JTF_DIAGNOSTIC_COREAPI.BRPrint;
150 l_sqltxt := ' SELECT
151 msi.segment1 "Outsourced Assembly",
152 mp.organization_code "Organization Code"
153 FROM
154 mtl_system_items_b msi,
155 mtl_parameters mp
156 WHERE
157 msi.OUTSOURCED_ASSEMBLY = 1
158 AND msi.organization_id = mp.organization_id
159 AND mp.trading_partner_org_flag = ''Y''
160 AND EXISTS
161 (SELECT 1 FROM bom_operational_routings bor
162 WHERE bor.organization_id = msi.organization_id
163 AND bor.assembly_item_id = msi.inventory_item_id)';
164
165
166 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
167 JTF_DIAGNOSTIC_COREAPI.BRPrint;
168 IF l_count >0 THEN
169 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please remove any defined routings for these Outsourced assemblies in MP Organizations');
170 JTF_DIAGNOSTIC_COREAPI.BRPrint;
171 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
172 END IF;
173 RETURN l_statusStr;
174
175 END Check_Routings;
176
177 --========================================================================
178 -- FUNCTION : Check_Shipping_Networks PUBLIC
179 -- PARAMETERS: NONE
180 -- COMMENT : This function displays all the OEM and MP organizations
181 -- between which no shipping network has been defined and
182 -- which have valid subcontracting orders to be processed
183 --========================================================================
184 FUNCTION Check_Shipping_Network RETURN VARCHAR2 IS
185 l_count NUMBER := 0;
186 l_sqltxt VARCHAR2(2000);
187 l_statusStr VARCHAR2(10);
188 BEGIN
189 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
190 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>5. CHECKING SHIPPING NETWORKS</u>');
191 JTF_DIAGNOSTIC_COREAPI.line_out('Shipping network is not defined between the following OEM and MP organizations:');
192 JTF_DIAGNOSTIC_COREAPI.BRPrint;
193 l_sqltxt := ' SELECT DISTINCT
194 (SELECT organization_code FROM mtl_parameters WHERE organization_id = oem_organization_id) "OEM Organization Code",
195 (SELECT organization_code FROM mtl_parameters WHERE organization_id = tp_organization_id) "MP Organization Code"
196 FROM jmf_subcontract_orders jso
197 WHERE NOT EXISTS
198 (SELECT 1 FROM mtl_interorg_parameters mip
199 WHERE mip.from_organization_id = jso.oem_organization_id
200 AND mip.to_organization_id = jso.tp_organization_id
201 AND SUBCONTRACTING_TYPE IS NOT NULL)';
202
203
204 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
205 JTF_DIAGNOSTIC_COREAPI.BRPrint;
206 IF l_count >0 THEN
207 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please define shipping networks between the following OEM and MP organizations');
208 JTF_DIAGNOSTIC_COREAPI.BRPrint;
209 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
210 END IF;
211 RETURN l_statusStr;
212
213 END Check_Shipping_Network;
214
215 --========================================================================
216 -- FUNCTION : Check_Shipping_Methods PUBLIC
217 -- PARAMETERS: NONE
218 -- COMMENT : This function displays all the OEM and MP organizations
219 -- between which no default shipping methods has been defined and
220 -- which have valid subcontracting orders to be processed
221 --========================================================================
222 FUNCTION Check_Shipping_Methods RETURN VARCHAR2 IS
223 l_count NUMBER := 0;
224 l_sqltxt VARCHAR2(2000);
225 l_statusStr VARCHAR2(10);
226 BEGIN
227 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
228 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>6. CHECKING SHIPPING METHODS</u>');
229 JTF_DIAGNOSTIC_COREAPI.line_out('Default shipping method is not defined between the following OEM and MP organizations:');
230 JTF_DIAGNOSTIC_COREAPI.BRPrint;
231 l_sqltxt := ' SELECT DISTINCT
232 (SELECT organization_code FROM mtl_parameters WHERE organization_id = oem_organization_id) "OEM Organization Code",
233 (SELECT organization_code FROM mtl_parameters WHERE organization_id = tp_organization_id) "TP Organization Code"
234 FROM jmf_subcontract_orders jso
235 WHERE NOT EXISTS
236 (SELECT 1 FROM mtl_interorg_ship_methods mism
237 WHERE mism.from_organization_id = jso.oem_organization_id
238 AND mism.to_organization_id = jso.tp_organization_id
239 AND mism.default_flag = 1)
240 UNION
241 SELECT DISTINCT
242 (SELECT organization_code FROM mtl_parameters WHERE organization_id = oem_organization_id) "OEM Organization Code",
243 (SELECT organization_code FROM mtl_parameters WHERE organization_id = tp_organization_id) "TP Organization Code"
244 FROM jmf_subcontract_orders jso
245 WHERE NOT EXISTS
246 (SELECT 1 FROM mtl_interorg_ship_methods mism
247 WHERE mism.from_organization_id = jso.tp_organization_id
248 AND mism.to_organization_id = jso.oem_organization_id
249 AND mism.default_flag = 1)';
250
251 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
252 JTF_DIAGNOSTIC_COREAPI.BRPrint;
253 IF l_count >0 THEN
254 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please define default shipping methods between the following OEM and MP organizations');
255 JTF_DIAGNOSTIC_COREAPI.BRPrint;
256 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
257 END IF;
258 RETURN l_statusStr;
259
260 END Check_Shipping_Methods;
261
262
263 --========================================================================
264 -- FUNCTION : Check_Cust_Supp_Association PUBLIC
265 -- PARAMETERS: NONE
266 -- COMMENT : This function displays all the OEM and MP organizations
267 -- between which have no Customer/Supplier associations defined
268 -- in the organization's inventory information
269 --========================================================================
270 FUNCTION Check_Cust_Supp_Association RETURN VARCHAR2 IS
271 l_count NUMBER := 0;
272 l_sqltxt VARCHAR2(2000);
273 l_statusStr VARCHAR2(10);
274 BEGIN
275 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
276 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>7. CHECKING CUSTOMER SUPPLIER ASSOCIATION</u>');
277
278 -- For OEM organizations
279 JTF_DIAGNOSTIC_COREAPI.line_out('Customer/Supplier Associations have not been defined in the following OEM organizations:');
280 JTF_DIAGNOSTIC_COREAPI.BRPrint;
281 l_sqltxt := ' SELECT
282 ORGANIZATION_CODE "OEM Organization" FROM mtl_parameters mp
283 WHERE Nvl(trading_partner_org_flag, ''N'') = ''N''
284 AND EXISTS(
285 SELECT 1 FROM
286 hr_organization_information hoi
287 WHERE mp.organization_id = hoi.organization_id
288 AND org_information_context = ''Customer/Supplier Association''
289 AND (org_information3 IS NULL
290 OR org_information4 IS NULL) )
291 AND EXISTS
292 ( SELECT 1 FROM mtl_system_items_b msi
293 WHERE msi.organization_id = mp.organization_id
294 AND msi.outsourced_assembly = 1)';
295
296 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
297 JTF_DIAGNOSTIC_COREAPI.BRPrint;
298 IF l_count >0 THEN
299 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please define Customer/Supplier Associations in these OEM organizations');
300 JTF_DIAGNOSTIC_COREAPI.BRPrint;
301 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
302 END IF;
303
304 l_count :=0;
305 -- For MP organizations
306 JTF_DIAGNOSTIC_COREAPI.line_out('Customer/Supplier Associations have not been defined in the following MP organizations:');
307 JTF_DIAGNOSTIC_COREAPI.BRPrint;
308 l_sqltxt := ' SELECT
309 ORGANIZATION_CODE "MP Organization" FROM mtl_parameters mp
310 WHERE Nvl(trading_partner_org_flag, ''N'') = ''Y''
311 AND EXISTS(
312 SELECT 1 FROM
313 hr_organization_information hoi
314 WHERE mp.organization_id = hoi.organization_id
315 AND org_information_context = ''Customer/Supplier Association''
316 AND (org_information1 IS NULL
317 OR org_information2 IS NULL
318 OR org_information4 IS NULL
319 OR org_information4 IS NULL) ) ';
320
321 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
322 JTF_DIAGNOSTIC_COREAPI.BRPrint;
323 IF l_count >0 THEN
324 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please define Customer/Supplier Associations in these MP organizations');
325 JTF_DIAGNOSTIC_COREAPI.BRPrint;
326 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
327 END IF;
328 RETURN l_statusStr;
329
330 END Check_Cust_Supp_Association;
331
332 --------------------------------------------------------------------------------------
333 -- procedure to check if price list has been defined for the subcontracting components
334 --------------------------------------------------------------------------------------
335 --========================================================================
336 -- FUNCTION : Check_Price_List PUBLIC
337 -- PARAMETERS: NONE
338 -- COMMENT : This function displays all the subcontracting components
342 l_count NUMBER := 0;
339 -- which have no price defined in the price list
340 --========================================================================
341 FUNCTION Check_Price_List RETURN VARCHAR2 IS
343 l_sqltxt VARCHAR2(2000);
344 l_statusStr VARCHAR2(10);
345 BEGIN
346 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_SUCCESS;
347 JTF_DIAGNOSTIC_COREAPI.SectionPrint('<u>8. CHECKING PRICE LISTS</u>');
348 JTF_DIAGNOSTIC_COREAPI.line_out('The following subcontracting components are not associated with any price list:');
349 JTF_DIAGNOSTIC_COREAPI.BRPrint;
350 l_sqltxt := ' SELECT DISTINCT
351 SEGMENT1 "Item"
352 FROM mtl_system_items_b msi
353 WHERE subcontracting_component IN (1,2)
354 AND EXISTS
355 ( SELECT 1 FROM mtl_parameters mp
356 WHERE mp.organization_id = msi.organization_id
357 AND Nvl(trading_partner_org_flag, ''N'') = ''Y'')
358 AND NOT EXISTS
359 ( SELECT 1 FROM qp_list_lines
360 WHERE qp_price_list_pvt.get_inventory_item_id(list_line_id) = msi.inventory_item_id)';
361
362 l_count := JTF_DIAGNOSTIC_COREAPI.display_SQL(l_sqltxt,'');
363 JTF_DIAGNOSTIC_COREAPI.BRPrint;
364 IF l_count >0 THEN
365 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please define a valid price for these subcontracting components');
366 JTF_DIAGNOSTIC_COREAPI.BRPrint;
367 l_statusStr := JMF_SUBCONTRCT_DIAG_UTIL.G_STATUS_FAILURE;
368 END IF;
369 RETURN l_statusStr;
370
371 END Check_Price_List;
372
373
374 END JMF_SUBCONTRCT_DIAG_UTIL;
[Home] [Help]