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