[Home] [Help]
PACKAGE BODY: APPS.BOM_DIAGUNITTEST_ECODATA
Source
1 package body BOM_DIAGUNITTEST_ECODATA as
2 /* $Header: BOMDGCNB.pls 120.1 2007/12/26 09:46:11 vggarg noship $ */
3 PROCEDURE init is
4 BEGIN
5 null;
6 END init;
7
8 PROCEDURE cleanup IS
9 BEGIN
10 -- test writer could insert special cleanup code here
11 NULL;
12 END cleanup;
13
14 PROCEDURE runtest(inputs IN JTF_DIAG_INPUTTBL,
15 report OUT NOCOPY JTF_DIAG_REPORT,
16 reportClob OUT NOCOPY CLOB) IS
17 reportStr LONG; -- REPORT
18 sqltxt VARCHAR2(9999); -- SQL select statement
19 c_username VARCHAR2(50); -- accept input for username
20 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
21 errStr VARCHAR2(4000); -- error message
22 fixInfo VARCHAR2(4000); -- fix tip
23 isFatal VARCHAR2(50); -- TRUE or FALSE
24 num_rows NUMBER;
25 row_limit NUMBER;
26 l_item_id NUMBER;
27 l_org_id NUMBER;
28 l_eco_name VARCHAR2(10);
29 l_eco_exists NUMBER;
30 l_mco_exists NUMBER;
31 l_org_exists NUMBER;
32 l_ret_status BOOLEAN;
33 l_status VARCHAR2 (1);
34 l_industry VARCHAR2 (1);
35 l_oracle_schema VARCHAR2 (30);
36
37 BEGIN
38 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
39 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
40 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
41
42 /*Initializing local vars */
43 row_limit :=1000; /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
44 l_org_exists :=0; /* Initialize to zero */
45
46 -- accept input
47 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
48 l_eco_name := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ChangeNotice', inputs);
49
50 /* l_org_id is NOT a mandatory input. If it is not entered, then run the scripts for all orgs.
51 However if a value is entered for org_id, then validate it for existence. */
52
53 If l_org_id is not null Then /* validate if input org_id exists*/
54 Begin
55 select 1 into l_org_exists
56 from mtl_parameters
57 where organization_id=l_org_id;
58 Exception
59 When others Then
60 l_org_exists :=0;
61 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Organization Id');
62 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please either provide a valid value for the Organization Id or leave it blank. ');
63 statusStr := 'FAILURE';
64 isFatal := 'TRUE';
65 fixInfo := ' Please review the error message below and take corrective action. ';
66 errStr := ' Invalid value for input field Organization Id ';
67
68 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
69 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
70 End;
71
72 If l_org_exists=0 Then
73 Return;
74 End If;
75 End If; /* End of l_org_id is not null */
76
77 /* Input ECO/MCO name is mandatory.*/
78 If l_eco_name is NULL then
79 JTF_DIAGNOSTIC_COREAPI.Errorprint('Input ECO Name is mandatory.');
80 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please provide a valid value for the ECO Name.');
81 statusStr := 'FAILURE';
82 isFatal := 'TRUE';
83 fixInfo := ' Please review the error message below and take corrective action. ';
84 errStr :=' Invalid value for input field ECO Name. It is a mandatory input. ';
85
86 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
87 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
88 Return;
89
90 Else /*Validate if the input eco/mco name exists */
91 l_eco_exists := 0;
92 l_mco_exists := 0;
93
94 /* Verify if the input corresponds to an existing Eco */
95 Begin
96 Select 1 into l_eco_exists
97 from dual
98 where exists (select 1
99 from eng_engineering_changes
100 where change_notice=l_eco_name
101 and organization_id=nvl(l_org_id,organization_id));
102 Exception
103 When no_data_found then
104 l_eco_exists :=0;
105 When others then
106 null;
107 End;
108
109 /* Verify if the input corresponds to an existing MCO (Mass Change Order) */
110 Begin
111 Select 1 into l_mco_exists
112 from dual
113 where exists (select 1
114 from eng_eng_changes_interface
115 where change_notice=l_eco_name
116 and organization_id=nvl(l_org_id,organization_id));
117 Exception
118 When no_data_found then
119 l_mco_exists :=0;
120 When others then
121 null;
122 End;
123
124
125 If (l_eco_exists = 0) and (l_mco_exists = 0) Then /* no eco or mco exists */
126 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid combination of ECO Name and Organization.');
127 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide valid values for the ECO Name and Organization Id. ');
128 statusStr := 'FAILURE';
129 isFatal := 'TRUE';
130 fixInfo := ' Please review the error message below and take corrective action. ';
131 errStr := ' Invalid values for input fields ECO Name and/or Organization Id. ';
132
133 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
134 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
135 Return;
136
137 Elsif l_eco_exists =1 Then /* run the scripts if the ECO is exists */
138
139 /* Get the application installation info. References to Data Dictionary Objects without schema name
140 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
141 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
142
143 l_ret_status := fnd_installation.get_app_info ('ENG'
144 , l_status
145 , l_industry
146 , l_oracle_schema
147 );
148 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
149
150 /* SQL to fetch ECO Header Details */
151 sqltxt := 'SELECT ' ||
152 ' EEC.CHANGE_NOTICE "CHANGE NOTICE" '||
153 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
154 ' ,EEC.ORGANIZATION_ID "ORGANIZATION ID" '||
155 ' ,EEC.DESCRIPTION "DESCRIPTION" '||
156 ' ,to_char(EEC.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
157 ' ,EEC.LAST_UPDATED_BY "LAST UPDATED BY" '||
158 ' ,to_char(EEC.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
159 ' ,EEC.CREATED_BY "CREATED BY" '||
160 ' ,EEC.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
161 ' ,ECSVL.STATUS_NAME "STATUS" '||
162 ' ,EEC.STATUS_TYPE "STATUS TYPE" '||
163 ' ,to_char(EEC.INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "INITIATION DATE" '||
164 ' ,to_char(EEC.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE" '||
165 ' ,to_char(EEC.CANCELLATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CANCELLATION DATE" '||
166 ' ,EEC.CANCELLATION_COMMENTS "CANCELLATION COMMENTS" '||
167 ' ,ECOT.CHANGE_ORDER_TYPE "CHANGE ORDER TYPE" '||
168 ' ,EEC.CHANGE_ORDER_TYPE_ID "CHANGE ORDER TYPE ID" '||
169 ' ,EEC.REASON_CODE "REASON CODE" '||
170 ' ,ECR.DESCRIPTION "Reason Code Description" '||
171 ' ,EEC.PRIORITY_CODE "PRIORITY CODE" '||
172 ' ,ECP.DESCRIPTION "Priority Code Description" '||
173 ' ,EEC.ESTIMATED_ENG_COST "ESTIMATED ENG COST" '||
174 ' ,EEC.ESTIMATED_MFG_COST "ESTIMATED MFG COST" '||
175 ' ,EEC.REQUESTOR_ID "REQUESTOR ID" '||
176 ' ,EEC.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
177 ' ,EEC.ATTRIBUTE1 "ATTRIBUTE1" '||
178 ' ,EEC.ATTRIBUTE2 "ATTRIBUTE2" '||
179 ' ,EEC.ATTRIBUTE3 "ATTRIBUTE3" '||
180 ' ,EEC.ATTRIBUTE4 "ATTRIBUTE4" '||
181 ' ,EEC.ATTRIBUTE5 "ATTRIBUTE5" '||
182 ' ,EEC.ATTRIBUTE6 "ATTRIBUTE6" '||
183 ' ,EEC.ATTRIBUTE7 "ATTRIBUTE7" '||
184 ' ,EEC.ATTRIBUTE8 "ATTRIBUTE8" '||
185 ' ,EEC.ATTRIBUTE9 "ATTRIBUTE9" '||
186 ' ,EEC.ATTRIBUTE10 "ATTRIBUTE10" '||
187 ' ,EEC.ATTRIBUTE11 "ATTRIBUTE11" '||
188 ' ,EEC.ATTRIBUTE12 "ATTRIBUTE12" '||
189 ' ,EEC.ATTRIBUTE13 "ATTRIBUTE13" '||
190 ' ,EEC.ATTRIBUTE14 "ATTRIBUTE14" '||
191 ' ,EEC.ATTRIBUTE15 "ATTRIBUTE15" '||
192 ' ,EEC.REQUEST_ID "REQUEST ID" '||
193 ' ,EEC.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
194 ' ,EEC.PROGRAM_ID "PROGRAM ID" '||
195 ' ,to_char(EEC.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
196 ' ,DECODE(MLU_AST.MEANING,null,null, '||
197 ' (MLU_AST.MEANING || '' ('' || EEC.APPROVAL_STATUS_TYPE || '')'')) "APPROVAL STATUS" '||
198 ' ,to_char(EEC.APPROVAL_DATE,''DD-MON-YYYY HH24:MI:SS'') "APPROVAL DATE" '||
199 ' ,EEAL.APPROVAL_LIST_NAME "APPROVAL LIST NAME" '||
200 ' ,EEC.APPROVAL_LIST_ID "APPROVAL LIST ID" '||
201 ' ,EEAL.DESCRIPTION "APPROVAL LIST DESCRIPTION" '||
202 ' ,to_char(EEC.APPROVAL_REQUEST_DATE,''DD-MON-YYYY HH24:MI:SS'') "APPROVAL REQUEST DATE" '||
203 ' ,EEC.RESPONSIBLE_ORGANIZATION_ID "RESPONSIBLE ORGANIZATION ID" '||
204 ' ,HOU.NAME "ECO DEPARTMENT" '||
205 ' ,EEC.DDF_CONTEXT "DDF CONTEXT" '||
206 ' ,PPA.PROJECT_NAME "PROJECT NAME" '||
207 ' ,PPA.PROJECT_NUMBER "PROJECT NUMBER" '||
208 ' ,EEC.PROJECT_ID "PROJECT ID" '||
209 ' ,PT.TASK_NAME "TASK NAME" '||
210 ' ,PT.TASK_NUMBER "TASK NUMBER" '||
211 ' ,EEC.TASK_ID "TASK ID" '||
212 ' ,EEC.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
213 ' ,EEC.HIERARCHY_FLAG "HIERARCHY FLAG" '||
214 ' ,EEC.ORGANIZATION_HIERARCHY "ORGANIZATION HIERARCHY" '||
215 ' ,EEC.HIERARCHY_ID "HIERARCHY ID" '||
216 ' ,EEC.CHANGE_MGMT_TYPE_CODE "CHANGE MGMT TYPE CODE" '||
217 ' ,EEC.ASSIGNEE_ID "ASSIGNEE ID" '||
218 ' ,to_char(EEC.NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEED BY DATE" '||
219 ' ,EEC.INTERNAL_USE_ONLY "INTERNAL USE ONLY" '||
220 ' ,EEC.SOURCE_TYPE_CODE "SOURCE TYPE CODE" '||
221 ' ,EEC.SOURCE_ID "SOURCE ID" '||
222 ' ,EEC.SOURCE_NAME "SOURCE NAME" '||
223 ' ,EEC.EFFORT "EFFORT" '||
224 ' ,EEC.CHANGE_NAME "CHANGE NAME" '||
225 ' ,EEC.CHANGE_ID "CHANGE ID" '||
226 ' ,EEC.ROUTE_ID "ROUTE ID" '||
227 ' ,EEC.CHANGE_NOTICE_PREFIX "CHANGE NOTICE PREFIX" '||
228 ' ,EEC.CHANGE_NOTICE_NUMBER "CHANGE NOTICE NUMBER" '||
229 ' ,EEC.OLD_REQUESTOR_ID "OLD REQUESTOR ID" '||
230 ' ,EEC.STATUS_CODE "STATUS CODE" '||
231 ' ,EEC.RESOLUTION "RESOLUTION" '||
232 ' ,EEC.CLASSIFICATION_ID "CLASSIFICATION ID" '||
233 ' ,EEC.PLM_OR_ERP_CHANGE "PLM OR ERP CHANGE" '||
234 ' ,to_char(EEC.EXPIRATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "EXPIRATION DATE" '||
235 ' ,EEC.PROMOTE_STATUS_CODE "PROMOTE STATUS CODE" '||
236 ' ,EEC.IMPLEMENTATION_REQ_ID "IMPLEMENTATION REQ ID" '||
237 ' FROM eng_engineering_changes eec '||
238 ' , eng_change_order_types_v ecot '||
239 ' , MTL_PARAMETERS MP1 '||
240 ' , ENG_CHANGE_REASONS ECR '||
241 ' , ENG_CHANGE_PRIORITIES ECP '||
242 ' , ENG_ECN_APPROVAL_LISTS EEAL '||
243 ' , ENG_CHANGE_STATUSES_VL ECSVL '||
244 ' , PJM_PROJECTS_V PPA '||
245 ' , PA_TASKS PT '||
246 ' , HR_ORGANIZATION_UNITS HOU '||
247 ' , MFG_LOOKUPS MLU_AST '||
248 ' WHERE 1=1 '||
249 ' AND EEC.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
250 ' AND EEC.CHANGE_ORDER_TYPE_ID = ECOT.CHANGE_ORDER_TYPE_ID '||
251 ' AND EEC.STATUS_TYPE = ECSVL.STATUS_CODE(+) '||
252 ' AND EEC.REASON_CODE = ECR.ENG_CHANGE_REASON_CODE(+) '||
253 ' AND ECR.ORGANIZATION_ID(+) = -1 '||
254 ' AND EEC.PRIORITY_CODE = ECP.ENG_CHANGE_PRIORITY_CODE(+) '||
255 ' AND ECP.ORGANIZATION_ID(+) = -1 '||
256 ' AND EEC.APPROVAL_LIST_ID = EEAL.APPROVAL_LIST_ID(+) '||
257 ' AND EEC.APPROVAL_STATUS_TYPE = MLU_AST.LOOKUP_CODE(+) '||
258 ' AND MLU_AST.LOOKUP_TYPE = ''ENG_ECN_APPROVAL_STATUS'' '||
259 ' AND ECOT.CHANGE_MGMT_TYPE_CODE =EEC.CHANGE_MGMT_TYPE_CODE '||
260 ' AND EEC.CHANGE_MGMT_TYPE_CODE=''CHANGE_ORDER'' '||
261 ' AND EEC.PROJECT_ID = PPA.PROJECT_ID(+) '||
262 ' AND EEC.PROJECT_ID = PT.PROJECT_ID(+) '||
263 ' AND EEC.TASK_ID = PT.TASK_ID(+) '||
264 ' AND EEC.RESPONSIBLE_ORGANIZATION_ID = HOU.ORGANIZATION_ID(+) '||
265 ' and eec.change_notice = '''||l_eco_name||''' ';
266
267 if l_org_id is not null then
268 sqltxt :=sqltxt||' and eec.organization_id = '||l_org_id;
269 end if;
270 sqltxt :=sqltxt||' and rownum < '||row_limit;
271 sqltxt :=sqltxt||' order by mp1.organization_code ';
272
273 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Headers ');
274 If (num_rows = row_limit -1 ) Then
275 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
276 End If;
277 statusStr := 'SUCCESS';
278 isFatal := 'FALSE';
279
280 /* End of ECO header details*/
281
282 /* SQL to fetch Eco Revised Items Details */
283 sqltxt := 'SELECT ' ||
284 ' ERI.CHANGE_NOTICE "CHANGE NOTICE" '||
285 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
286 ' ,ERI.ORGANIZATION_ID "ORGANIZATION ID" '||
290 ' ,to_char(ERI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
287 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
288 ' ,ERI.REVISED_ITEM_ID "REVISED ITEM ID" '||
289 ' ,ERI.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
291 ' ,ERI.LAST_UPDATED_BY "LAST UPDATED BY" '||
292 ' ,to_char(ERI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
293 ' ,ERI.CREATED_BY "CREATED BY" '||
294 ' ,ERI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
295 ' ,to_char(ERI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE" '||
296 ' ,ERI.DESCRIPTIVE_TEXT "DESCRIPTIVE TEXT" '||
297 ' ,to_char(ERI.CANCELLATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CANCELLATION DATE" '||
298 ' ,ERI.CANCEL_COMMENTS "CANCEL COMMENTS" '||
299 ' ,ERI.DISPOSITION_TYPE "DISPOSITION TYPE" '||
300 ' ,ERI.NEW_ITEM_REVISION "NEW ITEM REVISION" '||
301 ' ,to_char(ERI.AUTO_IMPLEMENT_DATE,''DD-MON-YYYY HH24:MI:SS'') "AUTO IMPLEMENT DATE" '||
302 ' ,to_char(ERI.EARLY_SCHEDULE_DATE,''DD-MON-YYYY HH24:MI:SS'') "EARLY SCHEDULE DATE" '||
303 ' ,ERI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
304 ' ,ERI.ATTRIBUTE1 "ATTRIBUTE1" '||
305 ' ,ERI.ATTRIBUTE2 "ATTRIBUTE2" '||
306 ' ,ERI.ATTRIBUTE3 "ATTRIBUTE3" '||
307 ' ,ERI.ATTRIBUTE4 "ATTRIBUTE4" '||
308 ' ,ERI.ATTRIBUTE5 "ATTRIBUTE5" '||
309 ' ,ERI.ATTRIBUTE6 "ATTRIBUTE6" '||
310 ' ,ERI.ATTRIBUTE7 "ATTRIBUTE7" '||
311 ' ,ERI.ATTRIBUTE8 "ATTRIBUTE8" '||
312 ' ,ERI.ATTRIBUTE9 "ATTRIBUTE9" '||
313 ' ,ERI.ATTRIBUTE10 "ATTRIBUTE10" '||
314 ' ,ERI.ATTRIBUTE11 "ATTRIBUTE11" '||
315 ' ,ERI.ATTRIBUTE12 "ATTRIBUTE12" '||
316 ' ,ERI.ATTRIBUTE13 "ATTRIBUTE13" '||
317 ' ,ERI.ATTRIBUTE14 "ATTRIBUTE14" '||
318 ' ,ERI.ATTRIBUTE15 "ATTRIBUTE15" '||
319 ' ,ECSVL.STATUS_NAME "STATUS" '||
320 ' ,ERI.STATUS_TYPE "STATUS TYPE" '||
321 ' ,to_char(ERI.SCHEDULED_DATE,''DD-MON-YYYY HH24:MI:SS'') "SCHEDULED DATE" '||
322 ' ,ERI.BILL_SEQUENCE_ID "BILL SEQUENCE ID" '||
323 ' ,DECODE(ERI.MRP_ACTIVE,null,null,1,''Yes (1)'',2,''No (2)'', '||
324 ' ''OTHER ('' || ERI.MRP_ACTIVE || '')'') "MRP ACTIVE" '||
325 ' ,ERI.REQUEST_ID "REQUEST ID" '||
326 ' ,ERI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
327 ' ,ERI.PROGRAM_ID "PROGRAM ID" '||
328 ' ,to_char(ERI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
329 ' ,DECODE(ERI.UPDATE_WIP,null,null,1,''Yes (1)'',2,''No (2)'', '||
330 ' ''OTHER ('' || ERI.UPDATE_WIP || '')'') "UPDATE WIP" '||
331 ' ,DECODE(ERI.USE_UP,null,null,1,''Yes (1)'',2,''No (2)'', '||
332 ' ''OTHER ('' || ERI.USE_UP || '')'') "USE UP" '||
333 ' ,MIF2.PADDED_ITEM_NUMBER "USE UP ITEM NUMBER" '||
334 ' ,ERI.USE_UP_ITEM_ID "USE UP ITEM ID" '||
335 ' ,ERI.USE_UP_PLAN_NAME "USE UP PLAN NAME" '||
336 ' ,ERI.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER" '||
337 ' ,ERI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
338 ' ,ERI.FROM_WIP_ENTITY_ID "FROM WIP ENTITY ID" '||
339 ' ,ERI.TO_WIP_ENTITY_ID "TO WIP ENTITY ID" '||
340 ' ,ERI.FROM_CUM_QTY "FROM CUM QTY" '||
341 ' ,ERI.LOT_NUMBER "LOT NUMBER" '||
342 ' ,ERI.CFM_ROUTING_FLAG "CFM ROUTING FLAG" '||
343 ' ,ERI.COMPLETION_SUBINVENTORY "COMPLETION SUBINVENTORY" '||
344 ' ,ERI.COMPLETION_LOCATOR_ID "COMPLETION LOCATOR ID" '||
345 ' ,ERI.MIXED_MODEL_MAP_FLAG "MIXED MODEL MAP FLAG" '||
346 ' ,ERI.PRIORITY "PRIORITY" '||
347 ' ,DECODE(ERI.CTP_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
348 ' ''OTHER ('' || ERI.CTP_FLAG || '')'') "CTP FLAG" '||
349 ' ,ERI.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID" '||
350 ' ,ERI.NEW_ROUTING_REVISION "NEW ROUTING REVISION" '||
351 ' ,ERI.ROUTING_COMMENT "ROUTING COMMENT" '||
352 ' ,DECODE(ERI.ECO_FOR_PRODUCTION,null,null,1,''Yes (1)'',2,''No (2)'', '||
353 ' ''OTHER ('' || ERI.ECO_FOR_PRODUCTION || '')'') "ECO FOR PRODUCTION" '||
354 ' ,ERI.DESIGNATOR_SELECTION_TYPE "DESIGNATOR SELECTION TYPE" '||
355 ' ,ERI.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR" '||
356 ' ,ERI.TRANSFER_OR_COPY "TRANSFER OR COPY" '||
357 ' ,ERI.TRANSFER_OR_COPY_ITEM "TRANSFER OR COPY ITEM" '||
358 ' ,ERI.TRANSFER_OR_COPY_BILL "TRANSFER OR COPY BILL" '||
359 ' ,ERI.TRANSFER_OR_COPY_ROUTING "TRANSFER OR COPY ROUTING" '||
360 ' ,ERI.COPY_TO_ITEM "COPY TO ITEM" '||
361 ' ,ERI.COPY_TO_ITEM_DESC "COPY TO ITEM DESC" '||
362 ' ,ERI.IMPLEMENTED_ONLY "IMPLEMENTED ONLY" '||
363 ' ,ERI.SELECTION_OPTION "SELECTION OPTION" '||
364 ' ,to_char(ERI.SELECTION_DATE,''DD-MON-YYYY HH24:MI:SS'') "SELECTION DATE" '||
368 ' ,ERI.NEW_ITEM_REVISION_ID "NEW ITEM REVISION ID" '||
365 ' ,ERI.SELECTION_UNIT_NUMBER "SELECTION UNIT NUMBER" '||
366 ' ,ERI.CONCATENATED_COPY_SEGMENTS "CONCATENATED COPY SEGMENTS" '||
367 ' ,ERI.CHANGE_ID "CHANGE ID" '||
369 ' ,ERI.CURRENT_ITEM_REVISION_ID "CURRENT ITEM REVISION ID" '||
370 ' ,ERI.CURRENT_LIFECYCLE_STATE_ID "CURRENT LIFECYCLE STATE ID" '||
371 ' ,ERI.NEW_LIFECYCLE_STATE_ID "NEW LIFECYCLE STATE ID" '||
372 ' ,ERI.STATUS_CODE "STATUS CODE" '||
373 ' ,ERI.FROM_END_ITEM_ID "FROM END ITEM ID" '||
374 ' ,ERI.FROM_END_ITEM_REV_ID "FROM END ITEM REV ID" '||
375 ' ,ERI.FROM_END_ITEM_STRC_REV_ID "FROM END ITEM STRC REV ID" '||
376 ' ,ERI.ENABLE_ITEM_IN_LOCAL_ORG "ENABLE ITEM IN LOCAL ORG" '||
377 ' ,ERI.CREATE_BOM_IN_LOCAL_ORG "CREATE BOM IN LOCAL ORG" '||
378 ' ,ERI.CURRENT_STRUCTURE_REV_ID "CURRENT STRUCTURE REV ID" '||
379 ' ,ERI.NEW_STRUCTURE_REVISION "NEW STRUCTURE REVISION" '||
380 ' ,ERI.PARENT_REVISED_ITEM_SEQ_ID "PARENT REVISED ITEM SEQ ID" '||
381 ' ,ERI.PLAN_LEVEL "PLAN LEVEL" '||
382 ' ,ERI.NEW_REVISION_LABEL "NEW REVISION LABEL" '||
383 ' ,ERI.NEW_REVISION_REASON "NEW REVISION REASON" '||
384 ' ,ERI.NEW_REV_DESCRIPTION "NEW REV DESCRIPTION" '||
385 ' ,ERI.IMPLEMENTATION_REQ_ID "IMPLEMENTATION REQ ID" '||
386 ' FROM ENG_REVISED_ITEMS ERI '||
387 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
388 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
389 ' ,MTL_PARAMETERS MP1 '||
390 ' ,ENG_CHANGE_STATUSES_VL ECSVL '||
391 ' WHERE 1=1 '||
392 ' AND ERI.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
393 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
394 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
395 ' AND ERI.USE_UP_ITEM_ID = MIF2.INVENTORY_ITEM_ID(+) '||
396 ' AND ERI.ORGANIZATION_ID = MIF2.ORGANIZATION_ID(+) '||
397 ' AND ERI.STATUS_TYPE = ECSVL.STATUS_CODE(+) '||
398 ' AND eri.change_notice = '''||l_eco_name||''' ';
399
400 if l_org_id is not null then
401 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
402 end if;
403 sqltxt :=sqltxt||' and rownum < '||row_limit;
404 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number,eri.revised_item_sequence_id';
405
406 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Revised Items ');
407 If (num_rows = row_limit -1 ) Then
408 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
409 End If;
410 statusStr:= 'SUCCESS';
411 isFatal := 'FALSE';
412
413
414 /* End of ECO Revised Items Details*/
415
416 l_ret_status := fnd_installation.get_app_info ('BOM'
417 , l_status
418 , l_industry
419 , l_oracle_schema
420 );
421 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
422
423 /* SQL to fetch revised components from bom_components_b table*/
424 sqltxt := ' SELECT eri.change_notice "CHANGE NOTICE" '||
425 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
426 ' ,eri.organization_id "ORGANIZATION ID" '||
427 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
428 ' ,eri.revised_item_id "REVISED ITEM ID" '||
429 ' ,eri.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
430 ' ,BCB.OPERATION_SEQ_NUM "OPERATION SEQ NUM" '||
431 ' ,MIF2.PADDED_ITEM_NUMBER "COMPONENT ITEM NUMBER" '||
432 ' ,BCB.COMPONENT_ITEM_ID "COMPONENT ITEM ID" '||
433 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
434 ' ,to_char(BCB.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE " '||
435 ' ,BCB.LAST_UPDATED_BY "LAST UPDATED BY" '||
436 ' ,to_char(BCB.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
437 ' ,BCB.CREATED_BY "CREATED BY" '||
438 ' ,BCB.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
439 ' ,BCB.ITEM_NUM "ITEM NUM" '||
440 ' ,BCB.COMPONENT_QUANTITY "COMPONENT QUANTITY" '||
441 ' ,BCB.COMPONENT_YIELD_FACTOR "COMPONENT YIELD FACTOR" '||
442 ' ,BCB.COMPONENT_REMARKS "COMPONENT REMARKS" '||
443 ' ,to_char(BCB.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE " '||
444 ' ,BCB.CHANGE_NOTICE "CHANGE NOTICE" '||
445 ' ,to_char(BCB.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'')"IMPLEMENTATION DATE" '||
446 ' ,to_char(BCB.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE" '||
447 ' ,BCB.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
448 ' ,BCB.ATTRIBUTE1 "ATTRIBUTE1" '||
449 ' ,BCB.ATTRIBUTE2 "ATTRIBUTE2" '||
450 ' ,BCB.ATTRIBUTE3 "ATTRIBUTE3" '||
451 ' ,BCB.ATTRIBUTE4 "ATTRIBUTE4" '||
455 ' ,BCB.ATTRIBUTE8 "ATTRIBUTE8" '||
452 ' ,BCB.ATTRIBUTE5 "ATTRIBUTE5" '||
453 ' ,BCB.ATTRIBUTE6 "ATTRIBUTE6" '||
454 ' ,BCB.ATTRIBUTE7 "ATTRIBUTE7" '||
456 ' ,BCB.ATTRIBUTE9 "ATTRIBUTE9" '||
457 ' ,BCB.ATTRIBUTE10 "ATTRIBUTE10" '||
458 ' ,BCB.ATTRIBUTE11 "ATTRIBUTE11" '||
459 ' ,BCB.ATTRIBUTE12 "ATTRIBUTE12" '||
460 ' ,BCB.ATTRIBUTE13 "ATTRIBUTE13" '||
461 ' ,BCB.ATTRIBUTE14 "ATTRIBUTE14" '||
462 ' ,BCB.ATTRIBUTE15 "ATTRIBUTE15" '||
463 ' ,BCB.PLANNING_FACTOR "PLANNING FACTOR" '||
464 ' ,DECODE(BCB.QUANTITY_RELATED,null,null,1,''Yes (1)'',2,''No (2)'', '||
465 ' ''OTHER ('' || BCB.QUANTITY_RELATED || '')'') "QUANTITY RELATED" '||
466 ' ,DECODE(MLU_SO.MEANING,null,null, '||
467 ' (MLU_SO.MEANING || '' ('' || BCB.SO_BASIS || '')'')) "SO BASIS" '||
468 ' ,DECODE(BCB.OPTIONAL,null,null,1,''Yes (1)'',2,''No (2)'', '||
469 ' ''OTHER ('' || BCB.OPTIONAL || '')'') "OPTIONAL" '||
470 ' ,DECODE(BCB.MUTUALLY_EXCLUSIVE_OPTIONS,null,null,1,''Yes (1)'',2,''No (2)'', '||
471 ' ''OTHER ('' || BCB.MUTUALLY_EXCLUSIVE_OPTIONS || '')'') "MUTUALLY EXCLUSIVE OPTIONS" '||
472 ' ,DECODE(BCB.INCLUDE_IN_COST_ROLLUP,null,null,1,''Yes (1)'',2,''No (2)'', '||
473 ' ''OTHER ('' || BCB.INCLUDE_IN_COST_ROLLUP || '')'') "INCLUDE IN COST ROLLUP" '||
474 ' ,DECODE(BCB.CHECK_ATP,null,null,1,''Yes (1)'',2,''No (2)'', '||
475 ' ''OTHER ('' || BCB.CHECK_ATP || '')'') "CHECK ATP" '||
476 ' ,DECODE(BCB.SHIPPING_ALLOWED,null,null,1,''Yes (1)'',2,''No (2)'', '||
477 ' ''OTHER ('' || BCB.SHIPPING_ALLOWED || '')'') "SHIPPING ALLOWED" '||
478 ' ,DECODE(BCB.REQUIRED_TO_SHIP,null,null,1,''Yes (1)'',2,''No (2)'', '||
479 ' ''OTHER ('' || BCB.REQUIRED_TO_SHIP || '')'') "REQUIRED TO SHIP" '||
480 ' ,DECODE(BCB.REQUIRED_FOR_REVENUE,null,null,1,''Yes (1)'',2,''No (2)'', '||
481 ' ''OTHER ('' || BCB.REQUIRED_FOR_REVENUE || '')'') "REQUIRED FOR REVENUE" '||
482 ' ,DECODE(BCB.INCLUDE_ON_SHIP_DOCS,null,null,1,''Yes (1)'',2,''No (2)'', '||
483 ' ''OTHER ('' || BCB.INCLUDE_ON_SHIP_DOCS || '')'') "INCLUDE ON SHIP DOCS" '||
484 ' ,DECODE(BCB.INCLUDE_ON_BILL_DOCS,null,null,1,''Yes (1)'',2,''No (2)'', '||
485 ' ''OTHER ('' || BCB.INCLUDE_ON_BILL_DOCS || '')'') "INCLUDE ON BILL DOCS" '||
486 ' ,BCB.LOW_QUANTITY "LOW QUANTITY" '||
487 ' ,BCB.HIGH_QUANTITY "HIGH QUANTITY" '||
488 ' ,DECODE(MLU_ACD.MEANING,null,null, '||
489 ' (MLU_ACD.MEANING || '' ('' || BCB.ACD_TYPE || '')'')) "ACD TYPE" '||
490 ' ,BCB.OLD_COMPONENT_SEQUENCE_ID "OLD COMPONENT SEQUENCE ID" '||
491 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
492 ' ,BCB.BILL_SEQUENCE_ID "BILL SEQUENCE ID" '||
493 ' ,BCB.REQUEST_ID "REQUEST ID" '||
494 ' ,BCB.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
495 ' ,BCB.PROGRAM_ID "PROGRAM ID" '||
496 ' ,to_char(BCB.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
497 ' ,DECODE(MLU_WIP.MEANING,null,null, '||
498 ' (MLU_WIP.MEANING || '' ('' || BCB.WIP_SUPPLY_TYPE || '')'')) "WIP SUPPLY TYPE" '||
499 ' ,DECODE(BCB.PICK_COMPONENTS,null,null,1,''Yes (1)'',2,''No (2)'', '||
500 ' ''OTHER ('' || BCB.PICK_COMPONENTS || '')'') "PICK COMPONENTS" '||
501 ' ,BCB.SUPPLY_SUBINVENTORY "SUPPLY SUBINVENTORY" '||
502 ' ,BCB.SUPPLY_LOCATOR_ID "SUPPLY LOCATOR ID" '||
503 ' ,BCB.OPERATION_LEAD_TIME_PERCENT "OPERATION LEAD TIME PERCENT" '||
504 ' ,BCB.COST_FACTOR "COST FACTOR" '||
505 ' ,DECODE(MLU_BIT.MEANING,null,null, '||
506 ' (MLU_BIT.MEANING || '' ('' || BCB.BOM_ITEM_TYPE || '')'')) "BOM ITEM TYPE" '||
507 ' ,BCB.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER" '||
508 ' ,BCB.TO_END_ITEM_UNIT_NUMBER "TO END ITEM UNIT NUMBER" '||
509 ' ,DECODE(ascii(BCB.ORIGINAL_SYSTEM_REFERENCE),''0'', '||
510 ' ''**NULL CHAR**'',BCB.ORIGINAL_SYSTEM_REFERENCE) "ORIGINAL SYSTEM REFERENCE" '||
511 ' ,DECODE(BCB.ECO_FOR_PRODUCTION,null,null,1,''Yes (1)'',2,''No (2)'', '||
512 ' ''OTHER ('' || BCB.ECO_FOR_PRODUCTION || '')'') "ECO FOR PRODUCTION" '||
513 ' ,BCB.ENFORCE_INT_REQUIREMENTS "ENFORCE INT REQUIREMENTS" '||
514 ' ,BCB.COMPONENT_ITEM_REVISION_ID "COMPONENT ITEM REVISION ID" '||
515 ' ,BCB.DELETE_GROUP_NAME "DELETE GROUP NAME" '||
516 ' ,BCB.DG_DESCRIPTION "DG DESCRIPTION" '||
517 ' ,BCB.OPTIONAL_ON_MODEL "OPTIONAL ON MODEL" '||
518 ' ,BCB.PARENT_BILL_SEQ_ID "PARENT BILL SEQ ID" '||
519 ' ,BCB.MODEL_COMP_SEQ_ID "MODEL COMP SEQ ID" '||
520 ' ,BCB.PLAN_LEVEL "PLAN LEVEL" '||
521 ' ,BCB.FROM_BILL_REVISION_ID "FROM BILL REVISION ID" '||
522 ' ,BCB.TO_BILL_REVISION_ID "TO BILL REVISION ID" '||
523 ' ,BCB.AUTO_REQUEST_MATERIAL "AUTO REQUEST MATERIAL" '||
527 ' ,BCB.OBJ_NAME "OBJ NAME" '||
524 ' ,BCB.SUGGESTED_VENDOR_NAME "SUGGESTED VENDOR NAME" '||
525 ' ,BCB.VENDOR_ID "VENDOR ID" '||
526 ' ,BCB.UNIT_PRICE "UNIT PRICE" '||
528 ' ,BCB.PK1_VALUE "PK1 VALUE" '||
529 ' ,BCB.PK2_VALUE "PK2 VALUE" '||
530 ' ,BCB.PK3_VALUE "PK3 VALUE" '||
531 ' ,BCB.PK4_VALUE "PK4 VALUE" '||
532 ' ,BCB.PK5_VALUE "PK5 VALUE" '||
533 ' FROM BOM_COMPONENTS_B BCB, '||
534 ' ENG_REVISED_ITEMS ERI, '||
535 ' MTL_PARAMETERS MP1, '||
536 ' MTL_ITEM_FLEXFIELDS MIF1, '||
537 ' MTL_ITEM_FLEXFIELDS MIF2, '||
538 ' MFG_LOOKUPS MLU_SO, '||
539 ' MFG_LOOKUPS MLU_ACD, '||
540 ' MFG_LOOKUPS MLU_WIP, '||
541 ' MFG_LOOKUPS MLU_BIT '||
542 ' WHERE 1=1 '||
543 ' AND BCB.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID '||
544 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
545 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
546 ' AND BCB.COMPONENT_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
547 ' AND ERI.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
548 ' and MP1.ORGANIZATION_ID = MIF2.ORGANIZATION_ID '||
549 ' and BCB.SO_BASIS=MLU_SO.LOOKUP_CODE(+) AND ''BOM_SO_BASIS''=MLU_SO.LOOKUP_TYPE(+) '||
550 ' AND BCB.ACD_TYPE=MLU_ACD.LOOKUP_CODE(+) AND ''ECG_ACTION''=MLU_ACD.LOOKUP_TYPE(+) '||
551 ' AND BCB.WIP_SUPPLY_TYPE=MLU_WIP.LOOKUP_CODE(+) AND ''WIP_SUPPLY''=MLU_WIP.LOOKUP_TYPE(+) '||
552 ' AND BCB.BOM_ITEM_TYPE=MLU_BIT.LOOKUP_CODE(+) AND ''BOM_ITEM_TYPE''=MLU_BIT.LOOKUP_TYPE(+) '||
553 ' AND eri.change_notice = '''||l_eco_name||''' ';
554
555 if l_org_id is not null then
556 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
557 end if;
558 sqltxt :=sqltxt||' and rownum < '||row_limit;
559 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id, '||
560 ' BCB.operation_seq_num, BCB.item_num';
561
562 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Components ');
563 If (num_rows = row_limit -1 ) Then
564 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
565 End If;
566 statusStr:= 'SUCCESS';
567 isFatal := 'FALSE';
568
569 sqltxt := ' SELECT eri.change_notice "CHANGE NOTICE" '||
570 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
571 ' ,eri.organization_id "ORGANIZATION ID" '||
572 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
573 ' ,BCB.FROM_END_ITEM_REV_ID "FROM END ITEM REV ID" '||
574 ' ,BCB.TO_END_ITEM_REV_ID "TO END ITEM REV ID" '||
575 ' ,BCB.OVERLAPPING_CHANGES "OVERLAPPING CHANGES" '||
576 ' ,BCB.FROM_OBJECT_REVISION_ID "FROM OBJECT REVISION ID" '||
577 ' ,BCB.FROM_MINOR_REVISION_ID "FROM MINOR REVISION ID" '||
578 ' ,BCB.TO_OBJECT_REVISION_ID "TO OBJECT REVISION ID" '||
579 ' ,BCB.TO_MINOR_REVISION_ID "TO MINOR REVISION ID" '||
580 ' ,BCB.FROM_END_ITEM_MINOR_REV_ID "FROM END ITEM MINOR REV ID" '||
581 ' ,BCB.TO_END_ITEM_MINOR_REV_ID "TO END ITEM MINOR REV ID" '||
582 ' ,BCB.COMPONENT_MINOR_REVISION_ID "COMPONENT MINOR REVISION ID" '||
583 ' ,BCB.FROM_STRUCTURE_REVISION_CODE "FROM STRUCTURE REVISION CODE" '||
584 ' ,BCB.TO_STRUCTURE_REVISION_CODE "TO STRUCTURE REVISION CODE" '||
585 ' ,BCB.FROM_END_ITEM_STRC_REV_ID "FROM END ITEM STRC REV ID" '||
586 ' ,BCB.TO_END_ITEM_STRC_REV_ID "TO END ITEM STRC REV ID" '||
587 ' ,BCB.BASIS_TYPE "BASIS TYPE" '||
588 ' ,BCB.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
589 ' FROM BOM_COMPONENTS_B BCB, '||
590 ' ENG_REVISED_ITEMS ERI, '||
591 ' MTL_PARAMETERS MP1, '||
592 ' MTL_ITEM_FLEXFIELDS MIF1, '||
593 ' MTL_ITEM_FLEXFIELDS MIF2, '||
594 ' MFG_LOOKUPS MLU_SO, '||
595 ' MFG_LOOKUPS MLU_ACD, '||
596 ' MFG_LOOKUPS MLU_WIP, '||
597 ' MFG_LOOKUPS MLU_BIT '||
598 ' WHERE 1=1 '||
599 ' AND BCB.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID '||
600 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
601 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
602 ' AND BCB.COMPONENT_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
603 ' AND ERI.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
604 ' and MP1.ORGANIZATION_ID = MIF2.ORGANIZATION_ID '||
605 ' and BCB.SO_BASIS=MLU_SO.LOOKUP_CODE(+) AND ''BOM_SO_BASIS''=MLU_SO.LOOKUP_TYPE(+) '||
606 ' AND BCB.ACD_TYPE=MLU_ACD.LOOKUP_CODE(+) AND ''ECG_ACTION''=MLU_ACD.LOOKUP_TYPE(+) '||
607 ' AND BCB.WIP_SUPPLY_TYPE=MLU_WIP.LOOKUP_CODE(+) AND ''WIP_SUPPLY''=MLU_WIP.LOOKUP_TYPE(+) '||
608 ' AND BCB.BOM_ITEM_TYPE=MLU_BIT.LOOKUP_CODE(+) AND ''BOM_ITEM_TYPE''=MLU_BIT.LOOKUP_TYPE(+) '||
609 ' AND eri.change_notice = '''||l_eco_name||''' ';
610
611 if l_org_id is not null then
615 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id, '||
612 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
613 end if;
614 sqltxt :=sqltxt||' and rownum < '||row_limit;
616 ' BCB.operation_seq_num, BCB.item_num';
617
618 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Components (Contd 1..) ');
619 If (num_rows = row_limit -1 ) Then
620 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
621 End If;
622 statusStr:= 'SUCCESS';
623 isFatal := 'FALSE';
624
625 /* End of revised components from bom components b */
626
627 /* SQL to fetch reference designators from bom_reference_designators table*/
628 sqltxt := ' SELECT ERI.CHANGE_NOTICE "CHANGE NOTICE" '||
629 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
630 ' ,ERI.ORGANIZATION_ID "ORGANIZATION ID" '||
631 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
632 ' ,ERI.REVISED_ITEM_ID "REVISED ITEM ID" '||
633 ' ,ERI.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
634 ' ,MIF2.PADDED_ITEM_NUMBER "COMPONENT ITEM NUMBER" '||
635 ' ,BCB.COMPONENT_ITEM_ID "COMPONENT ITEM ID" '||
636 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
637 ' ,BRD.COMPONENT_REFERENCE_DESIGNATOR "COMPONENT REFERENCE DESIGNATOR" '||
638 ' ,to_char(BRD.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
639 ' ,BRD.LAST_UPDATED_BY "LAST UPDATED BY" '||
640 ' ,to_char(BRD.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
641 ' ,BRD.CREATED_BY "CREATED BY" '||
642 ' ,BRD.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
643 ' ,BRD.REF_DESIGNATOR_COMMENT "REF DESIGNATOR COMMENT" '||
644 ' ,BRD.CHANGE_NOTICE "CHANGE NOTICE" '||
645 ' ,DECODE(BRD.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
646 ' ''OTHER('' || BRD.ACD_TYPE || '')'') "ACD TYPE" '||
647 ' ,BRD.REQUEST_ID "REQUEST ID" '||
648 ' ,BRD.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
649 ' ,BRD.PROGRAM_ID "PROGRAM ID" '||
650 ' ,to_char(BRD.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
651 ' ,BRD.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
652 ' ,BRD.ATTRIBUTE1 "ATTRIBUTE1" '||
653 ' ,BRD.ATTRIBUTE2 "ATTRIBUTE2" '||
654 ' ,BRD.ATTRIBUTE3 "ATTRIBUTE3" '||
655 ' ,BRD.ATTRIBUTE4 "ATTRIBUTE4" '||
656 ' ,BRD.ATTRIBUTE5 "ATTRIBUTE5" '||
657 ' ,BRD.ATTRIBUTE6 "ATTRIBUTE6" '||
658 ' ,BRD.ATTRIBUTE7 "ATTRIBUTE7" '||
659 ' ,BRD.ATTRIBUTE8 "ATTRIBUTE8" '||
660 ' ,BRD.ATTRIBUTE9 "ATTRIBUTE9" '||
661 ' ,BRD.ATTRIBUTE10 "ATTRIBUTE10" '||
662 ' ,BRD.ATTRIBUTE11 "ATTRIBUTE11" '||
663 ' ,BRD.ATTRIBUTE12 "ATTRIBUTE12" '||
664 ' ,BRD.ATTRIBUTE13 "ATTRIBUTE13" '||
665 ' ,BRD.ATTRIBUTE14 "ATTRIBUTE14" '||
666 ' ,BRD.ATTRIBUTE15 "ATTRIBUTE15" '||
667 ' ,BRD.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
668 ' ,BRD.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
669 ' FROM BOM_REFERENCE_DESIGNATORS BRD '||
670 ' ,BOM_COMPONENTS_B BCB '||
671 ' ,ENG_REVISED_ITEMS ERI '||
672 ' ,MTL_PARAMETERS MP1 '||
673 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
674 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
675 ' WHERE 1=1 '||
676 ' AND BCB.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID '||
677 ' AND BRD.COMPONENT_SEQUENCE_ID= BCB.COMPONENT_SEQUENCE_ID '||
678 ' AND ERI.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
679 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
680 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
681 ' AND BCB.COMPONENT_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
682 ' AND MP1.ORGANIZATION_ID = MIF2.ORGANIZATION_ID '||
683 ' AND eri.change_notice = '''||l_eco_name||''' ';
684
685 if l_org_id is not null then
686 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
687 end if;
688 sqltxt :=sqltxt||' and rownum < '||row_limit;
689 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id, '||
690 ' BCB.operation_seq_num, BCB.item_num,mif2.padded_item_number, BCB.component_sequence_id,'||
691 ' brd.component_reference_designator';
692
696 End If;
693 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Reference Designators on Eco Revised Components ');
694 If (num_rows = row_limit -1 ) Then
695 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
697 statusStr:= 'SUCCESS';
698 isFatal := 'FALSE';
699
700 /* End of reference designators from bom_reference_designators */
701
702 /* SQL to fetch substitute components from Bom_substitute_components table*/
703 sqltxt := ' SELECT ERI.CHANGE_NOTICE "CHANGE NOTICE" '||
704 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
705 ' ,ERI.ORGANIZATION_ID "ORGANIZATION ID" '||
706 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
707 ' ,ERI.REVISED_ITEM_ID "REVISED ITEM ID" '||
708 ' ,ERI.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
709 ' ,MIF2.PADDED_ITEM_NUMBER "COMPONENT ITEM NUMBER" '||
710 ' ,BCB.COMPONENT_ITEM_ID "COMPONENT ITEM ID" '||
711 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
712 ' ,MIF3.PADDED_ITEM_NUMBER "SUBSTITUTE ITEM NUMBER" '||
713 ' ,BSCO.SUBSTITUTE_COMPONENT_ID "SUBSTITUTE COMPONENT ID" '||
714 ' ,to_char(BSCO.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
715 ' ,BSCO.LAST_UPDATED_BY "LAST UPDATED BY" '||
716 ' ,to_char(BSCO.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
717 ' ,BSCO.CREATED_BY "CREATED BY" '||
718 ' ,BSCO.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
719 ' ,NVL(BSCO.SUBSTITUTE_ITEM_QUANTITY,0) "SUBSTITUTE ITEM QUANTITY" '||
720 ' ,DECODE(MLU_ACD.MEANING,null,null, '||
721 ' (MLU_ACD.MEANING || '' ('' || BSCO.ACD_TYPE || '')'')) "ACD TYPE" '||
722 ' ,BSCO.CHANGE_NOTICE "CHANGE NOTICE" '||
723 ' ,BSCO.REQUEST_ID "REQUEST ID" '||
724 ' ,BSCO.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
725 ' ,BSCO.PROGRAM_ID "PROGRAM ID" '||
726 ' ,to_char(BSCO.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
727 ' ,BSCO.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
728 ' ,BSCO.ATTRIBUTE1 "ATTRIBUTE1" '||
729 ' ,BSCO.ATTRIBUTE2 "ATTRIBUTE2" '||
730 ' ,BSCO.ATTRIBUTE3 "ATTRIBUTE3" '||
731 ' ,BSCO.ATTRIBUTE4 "ATTRIBUTE4" '||
732 ' ,BSCO.ATTRIBUTE5 "ATTRIBUTE5" '||
733 ' ,BSCO.ATTRIBUTE6 "ATTRIBUTE6" '||
734 ' ,BSCO.ATTRIBUTE7 "ATTRIBUTE7" '||
735 ' ,BSCO.ATTRIBUTE8 "ATTRIBUTE8" '||
736 ' ,BSCO.ATTRIBUTE9 "ATTRIBUTE9" '||
737 ' ,BSCO.ATTRIBUTE10 "ATTRIBUTE10" '||
738 ' ,BSCO.ATTRIBUTE11 "ATTRIBUTE11" '||
739 ' ,BSCO.ATTRIBUTE12 "ATTRIBUTE12" '||
740 ' ,BSCO.ATTRIBUTE13 "ATTRIBUTE13" '||
741 ' ,BSCO.ATTRIBUTE14 "ATTRIBUTE14" '||
742 ' ,BSCO.ATTRIBUTE15 "ATTRIBUTE15" '||
743 ' ,BSCO.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
744 ' ,BSCO.ENFORCE_INT_REQUIREMENTS "ENFORCE INT REQUIREMENTS" '||
745 ' ,BSCO.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
746 ' FROM ENG_REVISED_ITEMS ERI '||
747 ' ,BOM_COMPONENTS_B BCB '||
748 ' ,BOM_SUBSTITUTE_COMPONENTS BSCO '||
749 ' ,MTL_PARAMETERS MP1 '||
750 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
751 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
752 ' ,MTL_ITEM_FLEXFIELDS MIF3 '||
753 ' ,MFG_LOOKUPS MLU_ACD '||
754 ' WHERE 1=1 '||
755 ' AND BCB.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID '||
756 ' AND BSCO.COMPONENT_SEQUENCE_ID= BCB.COMPONENT_SEQUENCE_ID '||
757 ' AND ERI.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
758 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
759 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
760 ' AND BCB.COMPONENT_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
761 ' AND ERI.ORGANIZATION_ID = MIF2.ORGANIZATION_ID '||
762 ' AND BSCO.SUBSTITUTE_COMPONENT_ID = MIF3.INVENTORY_ITEM_ID '||
763 ' AND ERI.ORGANIZATION_ID = MIF3.ORGANIZATION_ID '||
764 ' AND BSCO.ACD_TYPE = MLU_ACD.LOOKUP_CODE(+) '||
765 ' AND ''ECG_ACTION''=MLU_ACD.LOOKUP_TYPE(+) '||
766 ' AND eri.change_notice = '''||l_eco_name||''' ';
767
768 if l_org_id is not null then
769 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
770 end if;
771 sqltxt :=sqltxt||' and rownum < '||row_limit;
772 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, eri.revised_item_sequence_id, '||
773 ' BCB.operation_seq_num, BCB.item_num,mif2.padded_item_number, BCB.component_sequence_id,'||
774 ' bsco.substitute_component_id';
775
776 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Substitute Components on Eco Revised Components');
777 If (num_rows = row_limit -1 ) Then
781 isFatal := 'FALSE';
778 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
779 End If;
780 statusStr:= 'SUCCESS';
782
783 /* End of substitute components from Bom_substitute_components */
784
785 /* SQL to fetch revised operations from Bom_Operation_Sequences table*/
786 sqltxt := ' SELECT ERI.CHANGE_NOTICE "CHANGE NOTICE" '||
787 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
788 ' ,ERI.ORGANIZATION_ID "ORGANIZATION ID" '||
789 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
790 ' ,ERI.REVISED_ITEM_ID "REVISED ITEM ID" '||
791 ' ,BOS.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
792 ' ,BOS.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID" '||
793 ' ,BOS.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID" '||
794 ' ,BOS.OPERATION_SEQ_NUM "OPERATION SEQ NUM" '||
795 ' ,to_char(BOS.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE " '||
796 ' ,BOS.LAST_UPDATED_BY "LAST UPDATED BY" '||
797 ' ,to_char(BOS.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
798 ' ,BOS.CREATED_BY "CREATED BY" '||
799 ' ,BOS.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
800 ' ,BOS.STANDARD_OPERATION_ID "STANDARD OPERATION ID" '||
801 ' ,BOS.DEPARTMENT_ID "DEPARTMENT ID" '||
802 ' ,BOS.OPERATION_LEAD_TIME_PERCENT "OPERATION LEAD TIME PERCENT" '||
803 ' ,BOS.MINIMUM_TRANSFER_QUANTITY "MINIMUM TRANSFER QUANTITY" '||
804 ' ,DECODE(MLU_BCPT.MEANING,null,null, '||
805 ' (MLU_BCPT.MEANING || '' ('' || BOS.COUNT_POINT_TYPE || '')'')) "Count Point Type" '||
806 ' ,BOS.OPERATION_DESCRIPTION "OPERATION DESCRIPTION" '||
807 ' ,to_char(BOS.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE" '||
808 ' ,to_char(BOS.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE" '||
809 ' ,DECODE(BOS.BACKFLUSH_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
810 ' ''OTHER ('' || BOS.BACKFLUSH_FLAG || '')'') "Backflush Flag" '||
811 ' ,DECODE(BOS.OPTION_DEPENDENT_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
812 ' ''OTHER ('' || BOS.OPTION_DEPENDENT_FLAG || '')'') "Option Dependent Flag" '||
813 ' ,BOS.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY " '||
814 ' ,BOS.ATTRIBUTE1 "ATTRIBUTE1" '||
815 ' ,BOS.ATTRIBUTE2 "ATTRIBUTE2" '||
816 ' ,BOS.ATTRIBUTE3 "ATTRIBUTE3" '||
817 ' ,BOS.ATTRIBUTE4 "ATTRIBUTE4" '||
818 ' ,BOS.ATTRIBUTE5 "ATTRIBUTE5" '||
819 ' ,BOS.ATTRIBUTE6 "ATTRIBUTE6" '||
820 ' ,BOS.ATTRIBUTE7 "ATTRIBUTE7" '||
821 ' ,BOS.ATTRIBUTE8 "ATTRIBUTE8" '||
822 ' ,BOS.ATTRIBUTE9 "ATTRIBUTE9" '||
823 ' ,BOS.ATTRIBUTE10 "ATTRIBUTE10" '||
824 ' ,BOS.ATTRIBUTE11 "ATTRIBUTE11" '||
825 ' ,BOS.ATTRIBUTE12 "ATTRIBUTE12" '||
826 ' ,BOS.ATTRIBUTE13 "ATTRIBUTE13" '||
827 ' ,BOS.ATTRIBUTE14 "ATTRIBUTE14" '||
828 ' ,BOS.ATTRIBUTE15 "ATTRIBUTE15" '||
829 ' ,BOS.REQUEST_ID "REQUEST ID" '||
830 ' ,BOS.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
831 ' ,BOS.PROGRAM_ID "PROGRAM ID" '||
832 ' ,to_char(BOS.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
833 ' ,DECODE(MLU_OPT.MEANING,null,null, '||
834 ' (MLU_OPT.MEANING || '' ('' || BOS.OPERATION_TYPE || '')'')) "Operation Type" '||
835 ' ,DECODE(BOS.REFERENCE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
836 ' ''OTHER ('' || BOS.REFERENCE_FLAG || '')'') "Reference Flag" '||
837 ' ,BOS.PROCESS_OP_SEQ_ID "PROCESS OP SEQ ID" '||
838 ' ,BOS.LINE_OP_SEQ_ID "LINE OP SEQ ID" '||
839 ' ,BOS.YIELD "YIELD" '||
840 ' ,BOS.CUMULATIVE_YIELD "CUMULATIVE YIELD" '||
841 ' ,BOS.REVERSE_CUMULATIVE_YIELD "REVERSE CUMULATIVE YIELD" '||
842 ' ,BOS.LABOR_TIME_CALC "LABOR TIME CALC" '||
843 ' ,BOS.MACHINE_TIME_CALC "MACHINE TIME CALC" '||
844 ' ,BOS.TOTAL_TIME_CALC "TOTAL TIME CALC" '||
845 ' ,BOS.LABOR_TIME_USER "LABOR TIME USER" '||
846 ' ,BOS.MACHINE_TIME_USER "MACHINE TIME USER" '||
847 ' ,BOS.TOTAL_TIME_USER "TOTAL TIME USER" '||
848 ' ,BOS.NET_PLANNING_PERCENT "NET PLANNING PERCENT " '||
849 ' ,BOS.X_COORDINATE "X COORDINATE" '||
850 ' ,BOS.Y_COORDINATE "Y COORDINATE" '||
851 ' ,DECODE(BOS.INCLUDE_IN_ROLLUP,null,null,1,''Yes (1)'',2,''No (2)'', '||
852 ' ''OTHER ('' || BOS.INCLUDE_IN_ROLLUP || '')'') "INCLUDE IN ROLLUP" '||
853 ' ,DECODE(BOS.OPERATION_YIELD_ENABLED,null,null,1,''Yes (1)'',2,''No (2)'', '||
854 ' ''OTHER ('' || BOS.OPERATION_YIELD_ENABLED || '')'') "OPERATION YIELD ENABLED" '||
855 ' ,BOS.OLD_OPERATION_SEQUENCE_ID "OLD OPERATION SEQUENCE ID" '||
856 ' ,DECODE(BOS.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
860 ' ,to_char(BOS.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE" '||
857 ' ''OTHER ('' || BOS.ACD_TYPE || '')'') "ACD TYPE" '||
858 ' ,BOS.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
859 ' ,BOS.CHANGE_NOTICE "CHANGE NOTICE" '||
861 ' ,DECODE(BOS.ECO_FOR_PRODUCTION,null,null,1,''Yes (1)'',2,''No (2)'', '||
862 ' ''OTHER ('' || BOS.ECO_FOR_PRODUCTION || '')'') "ECO FOR PRODUCTION " '||
863 ' ,DECODE(MLU_SHT.MEANING,null,null, '||
864 ' (MLU_SHT.MEANING || '' ('' || BOS.SHUTDOWN_TYPE || '')'')) "SHUTDOWN TYPE" '||
865 ' ,BOS.ACTUAL_IPK "ACTUAL IPK" '||
866 ' ,BOS.CRITICAL_TO_QUALITY "CRITICAL TO QUALITY" '||
867 ' ,BOS.VALUE_ADDED "VALUE ADDED" '||
868 ' ,BOS.MACHINE_PROCESS_EFFICIENCY "MACHINE PROCESS EFFICIENCY" '||
869 ' ,BOS.LABOR_PROCESS_EFFICIENCY "LABOR PROCESS EFFICIENCY" '||
870 ' ,BOS.TOTAL_PROCESS_EFFICIENCY "TOTAL PROCESS EFFICIENCY" '||
871 ' ,BOS.LONG_DESCRIPTION "LONG DESCRIPTION" '||
872 ' ,BOS.CONFIG_ROUTING_ID "CONFIG ROUTING ID" '||
873 ' ,BOS.MODEL_OP_SEQ_ID "MODEL OP SEQ ID" '||
874 ' ,BOS.LOWEST_ACCEPTABLE_YIELD "LOWEST ACCEPTABLE YIELD" '||
875 ' ,BOS.USE_ORG_SETTINGS "USE ORG SETTINGS" '||
876 ' ,BOS.QUEUE_MANDATORY_FLAG "QUEUE MANDATORY FLAG" '||
877 ' ,BOS.RUN_MANDATORY_FLAG "RUN MANDATORY FLAG" '||
878 ' ,BOS.TO_MOVE_MANDATORY_FLAG "TO MOVE MANDATORY FLAG" '||
879 ' ,BOS.SHOW_NEXT_OP_BY_DEFAULT "SHOW NEXT OP BY DEFAULT" '||
880 ' ,BOS.SHOW_SCRAP_CODE "SHOW SCRAP CODE" '||
881 ' ,BOS.SHOW_LOT_ATTRIB "SHOW LOT ATTRIB" '||
882 ' ,BOS.TRACK_MULTIPLE_RES_USAGE_DATES "TRACK MULTIPLE RES USAGE DATES" '||
883 ' FROM eng_revised_items eri '||
884 ' ,bom_operation_sequences bos '||
885 ' ,mtl_parameters mp1 '||
886 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
887 ' ,MFG_LOOKUPS MLU_BCPT '||
888 ' ,MFG_LOOKUPS MLU_OPT '||
889 ' ,MFG_LOOKUPS MLU_SHT '||
890 ' WHERE 1=1 '||
891 ' AND bos.revised_item_sequence_id = eri.revised_item_sequence_id '||
892 ' AND eri.organization_id = mp1.organization_id '||
893 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
894 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
895 ' AND bos.count_point_type=mlu_bcpt.lookup_code(+) and ''BOM_COUNT_POINT_TYPE''=mlu_bcpt.lookup_type(+) '||
896 ' AND bos.operation_type=mlu_opt.lookup_code(+) and ''BOM_OPERATION_TYPE''=mlu_opt.lookup_type(+) '||
897 ' AND bos.shutdown_type=mlu_sht.lookup_code(+) and ''BOM_EAM_SHUTDOWN_TYPE''=mlu_sht.lookup_type(+) '||
898 ' AND eri.change_notice = '''||l_eco_name||''' ';
899
900 if l_org_id is not null then
901 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
902 end if;
903 sqltxt :=sqltxt||' and rownum < '||row_limit;
904 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, '||
905 ' eri.revised_item_sequence_id, bos.operation_seq_num ';
906
907 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Operations ');
908 If (num_rows = row_limit -1 ) Then
909 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
910 End If;
911 statusStr:= 'SUCCESS';
912 isFatal := 'FALSE';
913
914 /* End of revised operations from Bom_Operation_Sequences */
915
916 /* SQL to fetch resources on revised operations from bom_operation_resources table*/
917 sqltxt := ' SELECT ERI.CHANGE_NOTICE "CHANGE NOTICE" '||
918 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
919 ' ,ERI.ORGANIZATION_ID "ORGANIZATION ID" '||
920 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
921 ' ,ERI.REVISED_ITEM_ID "REVISED ITEM ID" '||
922 ' ,ERI.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
923 ' ,BOS.OPERATION_SEQ_NUM "OPERATION SEQ NUM" '||
924 ' ,BOS.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID" '||
925 ' ,BOS.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID" '||
926 ' ,BORE.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID" '||
927 ' ,BORE.RESOURCE_SEQ_NUM "RESOURCE SEQ NUM" '||
928 ' ,BR.RESOURCE_CODE "RESOURCE CODE" '||
929 ' ,BORE.RESOURCE_ID "RESOURCE ID" '||
930 ' ,BR.DESCRIPTION "RESOURCE DESCRIPTION" '||
931 ' ,BORE.ACTIVITY_ID "ACTIVITY ID" '||
932 ' ,DECODE(BORE.STANDARD_RATE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
933 ' ''OTHER ('' || BORE.STANDARD_RATE_FLAG || '')'') "STANDARD RATE FLAG" '||
934 ' ,BORE.ASSIGNED_UNITS "ASSIGNED UNITS" '||
935 ' ,BORE.USAGE_RATE_OR_AMOUNT "USAGE RATE OR AMOUNT" '||
936 ' ,BORE.USAGE_RATE_OR_AMOUNT_INVERSE "USAGE RATE OR AMOUNT INVERSE" '||
937 ' ,DECODE(MLU_BT.MEANING,null,null, '||
938 ' (MLU_BT.MEANING || '' ('' || BORE.BASIS_TYPE || '')'')) "BASIS TYPE" '||
939 ' ,DECODE(MLU_SF.MEANING,null,null, '||
943 ' ,to_char(BORE.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
940 ' (MLU_SF.MEANING || '' ('' || BORE.SCHEDULE_FLAG || '')'')) "SCHEDULE FLAG" '||
941 ' ,to_char(BORE.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
942 ' ,BORE.LAST_UPDATED_BY "LAST UPDATED BY" '||
944 ' ,BORE.CREATED_BY "CREATED BY" '||
945 ' ,BORE.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
946 ' ,BORE.RESOURCE_OFFSET_PERCENT "RESOURCE OFFSET PERCENT" '||
947 ' ,DECODE(MLU_ACT.MEANING,null,null, '||
948 ' (MLU_ACT.MEANING || '' ('' || BORE.AUTOCHARGE_TYPE || '')'')) "AUTOCHARGE TYPE" '||
949 ' ,BORE.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
950 ' ,BORE.ATTRIBUTE1 "ATTRIBUTE1" '||
951 ' ,BORE.ATTRIBUTE2 "ATTRIBUTE2" '||
952 ' ,BORE.ATTRIBUTE3 "ATTRIBUTE3" '||
953 ' ,BORE.ATTRIBUTE4 "ATTRIBUTE4" '||
954 ' ,BORE.ATTRIBUTE5 "ATTRIBUTE5" '||
955 ' ,BORE.ATTRIBUTE6 "ATTRIBUTE6" '||
956 ' ,BORE.ATTRIBUTE7 "ATTRIBUTE7" '||
957 ' ,BORE.ATTRIBUTE8 "ATTRIBUTE8" '||
958 ' ,BORE.ATTRIBUTE9 "ATTRIBUTE9" '||
959 ' ,BORE.ATTRIBUTE10 "ATTRIBUTE10" '||
960 ' ,BORE.ATTRIBUTE11 "ATTRIBUTE11" '||
961 ' ,BORE.ATTRIBUTE12 "ATTRIBUTE12" '||
962 ' ,BORE.ATTRIBUTE13 "ATTRIBUTE13" '||
963 ' ,BORE.ATTRIBUTE14 "ATTRIBUTE14" '||
964 ' ,BORE.ATTRIBUTE15 "ATTRIBUTE15" '||
965 ' ,BORE.REQUEST_ID "REQUEST ID" '||
966 ' ,BORE.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
967 ' ,BORE.PROGRAM_ID "PROGRAM ID" '||
968 ' ,to_char(BORE.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
969 ' ,BORE.SCHEDULE_SEQ_NUM "SCHEDULE SEQ NUM" '||
970 ' ,BORE.SUBSTITUTE_GROUP_NUM "SUBSTITUTE GROUP NUM" '||
971 ' ,DECODE(BORE.PRINCIPLE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
972 ' ''OTHER ('' || BORE.PRINCIPLE_FLAG || '')'') "PRINCIPLE FLAG" '||
973 ' ,BORE.SETUP_ID "SETUP ID" '||
974 ' ,BORE.CHANGE_NOTICE "CHANGE NOTICE" '||
975 ' ,DECODE(BORE.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
976 ' ''OTHER ('' || BORE.ACD_TYPE || '')'') "ACD TYPE" '||
977 ' ,BORE.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
978 ' FROM eng_revised_items eri '||
979 ' , bom_operation_sequences bos '||
980 ' , bom_operation_resources bore '||
981 ' , bom_resources br '||
982 ' , MTL_PARAMETERS MP1 '||
983 ' , MTL_ITEM_FLEXFIELDS MIF1 '||
984 ' , MFG_LOOKUPS MLU_BT, MFG_LOOKUPS MLU_SF '||
985 ' , MFG_LOOKUPS MLU_ACT '||
986 ' WHERE 1=1 '||
987 ' AND bos.revised_item_sequence_id = eri.revised_item_sequence_id '||
988 ' AND bore.operation_sequence_id = bos.operation_sequence_id '||
989 ' AND eri.organization_id = mp1.organization_id '||
990 ' AND ERI.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
991 ' AND ERI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
992 ' AND bore.resource_id = br.resource_id '||
993 ' AND BORE.BASIS_TYPE=MLU_BT.LOOKUP_CODE(+) AND ''CST_BASIS''=MLU_BT.LOOKUP_TYPE(+) '||
994 ' AND BORE.SCHEDULE_FLAG=MLU_SF.LOOKUP_CODE(+) AND ''BOM_RESOURCE_SCHEDULE_TYPE''=MLU_SF.LOOKUP_TYPE(+) '||
995 ' AND BORE.AUTOCHARGE_TYPE=MLU_ACT.LOOKUP_CODE(+) AND ''BOM_AUTOCHARGE_TYPE''=MLU_ACT.LOOKUP_TYPE(+) '||
996 ' AND eri.change_notice = '''||l_eco_name||''' ';
997
998 if l_org_id is not null then
999 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
1000 end if;
1001 sqltxt :=sqltxt||' and rownum < '||row_limit;
1002 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, eri.revised_item_sequence_id,'||
1003 ' bos.operation_seq_num, bore.resource_seq_num, br.resource_code ';
1004
1005 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Resources on Eco Revised Operations ');
1006 If (num_rows = row_limit -1 ) Then
1007 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1008 End If;
1009 statusStr:= 'SUCCESS';
1010 isFatal := 'FALSE';
1011
1012 /* End of resources on revised operations from bom_operation_resources table*/
1013
1014 /* Fetch eco revisions from eng_change_order_revisions table*/
1015 sqltxt := ' SELECT '||
1016 ' ECOR.CHANGE_NOTICE "CHANGE NOTICE" '||
1017 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1018 ' ,ECOR.ORGANIZATION_ID "ORGANIZATION ID" '||
1019 ' ,ECOR.REVISION "REVISION" '||
1020 ' ,ECOR.REVISION_ID "REVISION ID" '||
1021 ' ,to_char(ECOR.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1022 ' ,ECOR.LAST_UPDATED_BY "LAST UPDATED BY" '||
1026 ' ,ECOR.COMMENTS "COMMENTS" '||
1023 ' ,to_char(ECOR.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1024 ' ,ECOR.CREATED_BY "CREATED BY" '||
1025 ' ,ECOR.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1027 ' ,ECOR.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1028 ' ,ECOR.ATTRIBUTE1 "ATTRIBUTE1" '||
1029 ' ,ECOR.ATTRIBUTE2 "ATTRIBUTE2" '||
1030 ' ,ECOR.ATTRIBUTE3 "ATTRIBUTE3" '||
1031 ' ,ECOR.ATTRIBUTE4 "ATTRIBUTE4" '||
1032 ' ,ECOR.ATTRIBUTE5 "ATTRIBUTE5" '||
1033 ' ,ECOR.ATTRIBUTE6 "ATTRIBUTE6" '||
1034 ' ,ECOR.ATTRIBUTE7 "ATTRIBUTE7" '||
1035 ' ,ECOR.ATTRIBUTE8 "ATTRIBUTE8" '||
1036 ' ,ECOR.ATTRIBUTE9 "ATTRIBUTE9" '||
1037 ' ,ECOR.ATTRIBUTE10 "ATTRIBUTE10" '||
1038 ' ,ECOR.ATTRIBUTE11 "ATTRIBUTE11" '||
1039 ' ,ECOR.ATTRIBUTE12 "ATTRIBUTE12" '||
1040 ' ,ECOR.ATTRIBUTE13 "ATTRIBUTE13" '||
1041 ' ,ECOR.ATTRIBUTE14 "ATTRIBUTE14" '||
1042 ' ,ECOR.ATTRIBUTE15 "ATTRIBUTE15" '||
1043 ' ,ECOR.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1044 ' ,ECOR.PROGRAM_ID "PROGRAM ID" '||
1045 ' ,to_char(ECOR.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1046 ' ,ECOR.REQUEST_ID "REQUEST ID" '||
1047 ' ,ECOR.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1048 ' ,ECOR.CHANGE_ID "CHANGE ID" '||
1049 ' ,to_char(ECOR.START_DATE,''DD-MON-YYYY HH24:MI:SS'') "START DATE" '||
1050 ' ,to_char(ECOR.END_DATE,''DD-MON-YYYY HH24:MI:SS'') "END DATE" '||
1051 ' FROM eng_change_order_revisions ecor '||
1052 ' ,mtl_parameters mp1 '||
1053 ' WHERE 1=1 '||
1054 ' AND ecor.organization_id = mp1.organization_id '||
1055 ' AND ecor.change_notice = '''||l_eco_name||''' ';
1056
1057 if l_org_id is not null then
1058 sqltxt :=sqltxt||' and ecor.organization_id = '||l_org_id;
1059 end if;
1060 sqltxt :=sqltxt||' and rownum < '||row_limit;
1061 sqltxt :=sqltxt||' order by mp1.organization_code,ecor.revision ';
1062
1063 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revisions');
1064 If (num_rows = row_limit -1 ) Then
1065 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1066 End If;
1067 statusStr:= 'SUCCESS';
1068 isFatal := 'FALSE';
1069
1070 /* End of eco revisions from eng_change_order_revisions table*/
1071
1072 /* Fetch records from eco schedule history from eng_current_scheduled_dates table*/
1073 sqltxt := ' SELECT '||
1074 ' ECSD.CHANGE_NOTICE "CHANGE NOTICE" '||
1075 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1076 ' ,ECSD.ORGANIZATION_ID "ORGANIZATION ID" '||
1077 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
1078 ' ,ECSD.REVISED_ITEM_ID "REVISED ITEM ID" '||
1079 ' ,to_char(ECSD.SCHEDULED_DATE,''DD-MON-YYYY HH24:MI:SS'') "SCHEDULED DATE"'||
1080 ' ,to_char(ECSD.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1081 ' ,ECSD.LAST_UPDATED_BY "LAST UPDATED BY" '||
1082 ' ,to_char(ECSD.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1083 ' ,ECSD.CREATED_BY "CREATED BY" '||
1084 ' ,ECSD.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1085 ' ,ECSD.SCHEDULE_ID "SCHEDULE ID" '||
1086 ' ,ECSD.EMPLOYEE_ID "EMPLOYEE ID" '||
1087 ' ,ECSD.COMMENTS "COMMENTS" '||
1088 ' ,ECSD.REQUEST_ID "REQUEST ID" '||
1089 ' ,ECSD.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1090 ' ,ECSD.PROGRAM_ID "PROGRAM ID" '||
1091 ' ,to_char(ECSD.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1092 ' ,ECSD.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
1093 ' ,ECSD.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1094 ' ,ECSD.CHANGE_ID "CHANGE ID" '||
1095 ' ,ECSD.OLD_EMPLOYEE_ID "OLD EMPLOYEE ID" '||
1096 ' FROM ENG_CURRENT_SCHEDULED_DATES ECSD '||
1097 ' ,MTL_PARAMETERS MP1 '||
1098 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1099 ' WHERE 1=1 '||
1100 ' AND MP1.ORGANIZATION_ID = ECSD.ORGANIZATION_ID '||
1101 ' AND ECSD.REVISED_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
1102 ' AND ECSD.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1103 ' AND ecsd.change_notice = '''||l_eco_name||''' ';
1104
1105 if l_org_id is not null then
1106 sqltxt :=sqltxt||' and ecsd.organization_id = '||l_org_id;
1107 end if;
1108 sqltxt :=sqltxt||' and rownum < '||row_limit;
1109 sqltxt :=sqltxt||' order by mp1.organization_code, '||
1110 ' mif1.padded_item_number, ecsd.scheduled_date ';
1111
1112 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Schedule History ');
1113 If (num_rows = row_limit -1 ) Then
1114 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1115 End If;
1116 statusStr:= 'SUCCESS';
1117 isFatal := 'FALSE';
1118
1119 /* End of eco schedule history from eng_current_scheduled_dates table*/
1120
1121
1122 /* SQL to fetch routing revisions of revised_items from mtl_rtg_item_revisions table*/
1123 sqltxt := ' SELECT ERI.CHANGE_NOTICE "CHANGE NOTICE" '||
1124 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1125 ' ,ERI.ORGANIZATION_ID "ORGANIZATION ID" '||
1129 ' ,MRIR.PROCESS_REVISION "PROCESS REVISION" '||
1126 ' ,MIF1.PADDED_ITEM_NUMBER "REVISED ITEM NUMBER" '||
1127 ' ,ERI.REVISED_ITEM_ID "REVISED ITEM ID" '||
1128 ' ,MRIR.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
1130 ' ,to_char(MRIR.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1131 ' ,MRIR.LAST_UPDATED_BY "LAST UPDATED BY" '||
1132 ' ,to_char(MRIR.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1133 ' ,MRIR.CREATED_BY "CREATED BY" '||
1134 ' ,MRIR.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1135 ' ,MRIR.CHANGE_NOTICE "CHANGE NOTICE" '||
1136 ' ,to_char(MRIR.ECN_INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "ECN INITIATION DATE" '||
1137 ' ,to_char(MRIR.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE" '||
1138 ' ,MRIR.IMPLEMENTED_SERIAL_NUMBER "IMPLEMENTED SERIAL NUMBER" '||
1139 ' ,to_char(MRIR.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE" '||
1140 ' ,MRIR.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1141 ' ,MRIR.ATTRIBUTE1 "ATTRIBUTE1" '||
1142 ' ,MRIR.ATTRIBUTE2 "ATTRIBUTE2" '||
1143 ' ,MRIR.ATTRIBUTE3 "ATTRIBUTE3" '||
1144 ' ,MRIR.ATTRIBUTE4 "ATTRIBUTE4" '||
1145 ' ,MRIR.ATTRIBUTE5 "ATTRIBUTE5" '||
1146 ' ,MRIR.ATTRIBUTE6 "ATTRIBUTE6" '||
1147 ' ,MRIR.ATTRIBUTE7 "ATTRIBUTE7" '||
1148 ' ,MRIR.ATTRIBUTE8 "ATTRIBUTE8" '||
1149 ' ,MRIR.ATTRIBUTE9 "ATTRIBUTE9" '||
1150 ' ,MRIR.ATTRIBUTE10 "ATTRIBUTE10" '||
1151 ' ,MRIR.ATTRIBUTE11 "ATTRIBUTE11" '||
1152 ' ,MRIR.ATTRIBUTE12 "ATTRIBUTE12" '||
1153 ' ,MRIR.ATTRIBUTE13 "ATTRIBUTE13" '||
1154 ' ,MRIR.ATTRIBUTE14 "ATTRIBUTE14" '||
1155 ' ,MRIR.ATTRIBUTE15 "ATTRIBUTE15" '||
1156 ' ,MRIR.REQUEST_ID "REQUEST ID" '||
1157 ' ,MRIR.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1158 ' ,MRIR.PROGRAM_ID "PROGRAM ID" '||
1159 ' ,to_char(MRIR.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1160 ' from eng_revised_items eri '||
1161 ' ,mtl_rtg_item_revisions mrir '||
1162 ' ,MTL_PARAMETERS MP1 '||
1163 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1164 ' where 1=1 '||
1165 ' and mrir.inventory_item_id = eri.revised_item_id '||
1166 ' and mrir.organization_id = eri.organization_id '||
1167 ' and mrir.inventory_item_id= mif1.inventory_item_id '||
1168 ' and mrir.organization_id = mif1.organization_id '||
1169 ' and mif1.organization_id = mp1.organization_id '||
1170 ' AND eri.change_notice = '''||l_eco_name||''' ';
1171
1172 if l_org_id is not null then
1173 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
1174 end if;
1175 sqltxt :=sqltxt||' and rownum < '||row_limit;
1176 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number,eri.revised_item_sequence_id,mrir.process_revision';
1177
1178 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Revisions of Revised Items');
1179 If (num_rows = row_limit -1 ) Then
1180 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1181 End If;
1182 statusStr:= 'SUCCESS';
1183 isFatal := 'FALSE';
1184
1185 /* End of routing revisions of revised_items */
1186
1187 /* Start of Scripts to fetch ECO Workflow related data */
1188
1189 /* Fetch details of the notifications fired from an eco */
1190 sqltxt :=' SELECT '||
1191 ' WFI.ITEM_TYPE "Item Type" '||
1192 ' ,WFI.ITEM_KEY "Item Key" '||
1193 ' ,WFN.NOTIFICATION_ID "Notification Id" '||
1194 ' ,WFN.STATUS "Status" '||
1195 ' ,to_char(WFI.BEGIN_DATE,''DD-MON-YYYY HH24:MI:SS'') "Begin Date" '||
1196 ' FROM WF_ITEMS WFI,WF_NOTIFICATIONS WFN '||
1197 ' WHERE WFI.ITEM_KEY =substr(WFN.context,instr(WFN.context,'':'',1)+1, (instr(WFN.context,'':'',-1,1) - instr(WFN.context,'':'',1)-1)) '||
1198 ' AND WFN.MESSAGE_TYPE = ''ECO_APP'' '||
1199 ' AND WFI.ITEM_TYPE = ''ECO_APP'' ';
1200
1201 if l_org_id is not null then
1202 sqltxt :=sqltxt||' AND SUBSTR(WFI.ITEM_KEY,1,(instr(WFI.ITEM_KEY,''-'',-1,1)-1)) = '''||l_eco_name||'-'||l_org_id||''' ';
1203 else
1204 sqltxt :=sqltxt||' AND SUBSTR(WFI.ITEM_KEY,1,(instr(WFI.ITEM_KEY,''-'',-3,1)-1)) = '''||l_eco_name||''' ';
1205 end if;
1206 sqltxt :=sqltxt||' and rownum < '||row_limit;
1207 sqltxt :=sqltxt||' order by WFI.ITEM_KEY, WFN.NOTIFICATION_ID,WFN.STATUS ';
1208
1209 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Workflow Notifications ');
1210 If (num_rows = row_limit -1 ) Then
1211 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1212 End If;
1213 statusStr := 'SUCCESS';
1214 isFatal := 'FALSE';
1215
1216 /* End of details of the notifications fired from an eco */
1217
1218 /* Fetch details of the notification results fired from an eco */
1219 sqltxt :=' SELECT '||
1220 ' WFNA.NAME "Name" '||
1221 ' ,WFNA.NUMBER_VALUE "Number Value" '||
1222 ' ,WFNA.TEXT_VALUE "Text Value" '||
1223 ' ,to_char(WFNA.DATE_VALUE,''DD-MON-YYYY HH24:MI:SS'') "Date Value" '||
1224 ' ,WFI.ITEM_KEY "Item Key" '||
1225 ' ,WFN.NOTIFICATION_ID "Notification Id" '||
1226 ' ,WFN.GROUP_ID "Group Id" '||
1227 ' ,WFN.MESSAGE_TYPE "Message Type" '||
1231 ' ,WFN.ACCESS_KEY "Access Key" '||
1228 ' ,WFN.MESSAGE_NAME "Message Name" '||
1229 ' ,WFN.RECIPIENT_ROLE "Recipient Role" '||
1230 ' ,WFN.STATUS "Status" '||
1232 ' ,WFN.MAIL_STATUS "Mail Status" '||
1233 ' ,WFN.PRIORITY "Priority" '||
1234 ' ,to_char(WFN.BEGIN_DATE,''DD-MON-YYYY HH24:MI:SS'') "Begin Date" '||
1235 ' ,to_char(WFN.END_DATE,''DD-MON-YYYY HH24:MI:SS'') "End Date" '||
1236 ' ,to_char(WFN.DUE_DATE,''DD-MON-YYYY HH24:MI:SS'') "Due Date" '||
1237 ' ,WFN.RESPONDER "Responder" '||
1238 ' ,WFN.USER_COMMENT "User Comment" '||
1239 ' ,WFN.CALLBACK "Callback" '||
1240 ' ,WFN.CONTEXT "Context" '||
1241 ' ,WFN.ORIGINAL_RECIPIENT "Original Recipient" '||
1242 ' ,WFN.FROM_USER "From User" '||
1243 ' ,WFN.TO_USER "To User" '||
1244 ' ,WFN.SUBJECT "Subject" '||
1245 ' ,WFN.LANGUAGE "Language" '||
1246 ' ,WFN.MORE_INFO_ROLE "More Info Role" '||
1247 ' ,WFN.FROM_ROLE "From Role" '||
1248 ' ,WFN.SECURITY_GROUP_ID "Security Group Id" '||
1249 ' ,WFN.USER_KEY "User Key" '||
1250 ' ,WFN.ITEM_KEY "Item Key" '||
1251 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE1 "Protected Text Attribute1" '||
1252 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE2 "Protected Text Attribute2" '||
1253 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE3 "Protected Text Attribute3" '||
1254 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE4 "Protected Text Attribute4" '||
1255 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE5 "Protected Text Attribute5" '||
1256 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE6 "Protected Text Attribute6" '||
1257 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE7 "Protected Text Attribute7" '||
1258 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE8 "Protected Text Attribute8" '||
1259 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE9 "Protected Text Attribute9" '||
1260 ' ,WFN.PROTECTED_TEXT_ATTRIBUTE10 "Protected Text Attribute10" '||
1261 ' ,WFN.PROTECTED_FORM_ATTRIBUTE1 "Protected Form Attribute1" '||
1262 ' ,WFN.PROTECTED_FORM_ATTRIBUTE2 "Protected Form Attribute2" '||
1263 ' ,WFN.PROTECTED_FORM_ATTRIBUTE3 "Protected Form Attribute3" '||
1264 ' ,WFN.PROTECTED_FORM_ATTRIBUTE4 "Protected Form Attribute4" '||
1265 ' ,WFN.PROTECTED_FORM_ATTRIBUTE5 "Protected Form Attribute5" '||
1266 ' ,WFN.PROTECTED_URL_ATTRIBUTE1 "Protected Url Attribute1" '||
1267 ' ,WFN.PROTECTED_URL_ATTRIBUTE2 "Protected Url Attribute2" '||
1268 ' ,WFN.PROTECTED_URL_ATTRIBUTE3 "Protected Url Attribute3" '||
1269 ' ,WFN.PROTECTED_URL_ATTRIBUTE4 "Protected Url Attribute4" '||
1270 ' ,WFN.PROTECTED_URL_ATTRIBUTE5 "Protected Url Attribute5" '||
1271 ' ,to_char(WFN.PROTECTED_DATE_ATTRIBUTE1,''DD-MON-YYYY HH24:MI:SS'') "Protected Date Attribute1" '||
1272 ' ,to_char(WFN.PROTECTED_DATE_ATTRIBUTE2,''DD-MON-YYYY HH24:MI:SS'') "Protected Date Attribute2" '||
1273 ' ,to_char(WFN.PROTECTED_DATE_ATTRIBUTE3,''DD-MON-YYYY HH24:MI:SS'') "Protected Date Attribute3" '||
1274 ' ,to_char(WFN.PROTECTED_DATE_ATTRIBUTE4,''DD-MON-YYYY HH24:MI:SS'') "Protected Date Attribute4" '||
1275 ' ,to_char(WFN.PROTECTED_DATE_ATTRIBUTE5,''DD-MON-YYYY HH24:MI:SS'') "Protected Date Attribute5" '||
1276 ' ,WFN.PROTECTED_NUMBER_ATTRIBUTE1 "Protected Number Attribute1" '||
1277 ' ,WFN.PROTECTED_NUMBER_ATTRIBUTE2 "Protected Number Attribute2" '||
1278 ' ,WFN.PROTECTED_NUMBER_ATTRIBUTE3 "Protected Number Attribute3" '||
1279 ' ,WFN.PROTECTED_NUMBER_ATTRIBUTE4 "Protected Number Attribute4" '||
1280 ' ,WFN.PROTECTED_NUMBER_ATTRIBUTE5 "Protected Number Attribute5" '||
1281 ' ,WFN.TEXT_ATTRIBUTE1 "Text Attribute1" '||
1282 ' ,WFN.TEXT_ATTRIBUTE2 "Text Attribute2" '||
1283 ' ,WFN.TEXT_ATTRIBUTE3 "Text Attribute3" '||
1284 ' ,WFN.TEXT_ATTRIBUTE4 "Text Attribute4" '||
1285 ' ,WFN.TEXT_ATTRIBUTE5 "Text Attribute5" '||
1286 ' ,WFN.TEXT_ATTRIBUTE6 "Text Attribute6" '||
1287 ' ,WFN.TEXT_ATTRIBUTE7 "Text Attribute7" '||
1288 ' ,WFN.TEXT_ATTRIBUTE8 "Text Attribute8" '||
1289 ' ,WFN.TEXT_ATTRIBUTE9 "Text Attribute9" '||
1290 ' ,WFN.TEXT_ATTRIBUTE10 "Text Attribute10" '||
1291 ' ,WFN.FORM_ATTRIBUTE1 "Form Attribute1" '||
1292 ' ,WFN.FORM_ATTRIBUTE2 "Form Attribute2" '||
1293 ' ,WFN.FORM_ATTRIBUTE3 "Form Attribute3" '||
1294 ' ,WFN.FORM_ATTRIBUTE4 "Form Attribute4" '||
1295 ' ,WFN.FORM_ATTRIBUTE5 "Form Attribute5" '||
1296 ' ,WFN.URL_ATTRIBUTE1 "URL Attribute1" '||
1297 ' ,WFN.URL_ATTRIBUTE2 "URL Attribute2" '||
1298 ' ,WFN.URL_ATTRIBUTE3 "URL Attribute3" '||
1299 ' ,WFN.URL_ATTRIBUTE4 "URL Attribute4" '||
1300 ' ,WFN.URL_ATTRIBUTE5 "URL Attribute5" '||
1301 ' ,to_char(WFN.DATE_ATTRIBUTE1,''DD-MON-YYYY HH24:MI:SS'') "Date Attribute1" '||
1302 ' ,to_char(WFN.DATE_ATTRIBUTE2,''DD-MON-YYYY HH24:MI:SS'') "Date Attribute2" '||
1303 ' ,to_char(WFN.DATE_ATTRIBUTE3,''DD-MON-YYYY HH24:MI:SS'') "Date Attribute3" '||
1304 ' ,to_char(WFN.DATE_ATTRIBUTE4,''DD-MON-YYYY HH24:MI:SS'') "Date Attribute4" '||
1305 ' ,to_char(WFN.DATE_ATTRIBUTE5,''DD-MON-YYYY HH24:MI:SS'') "Date Attribute5" '||
1306 ' ,WFN.NUMBER_ATTRIBUTE1 "Number Attribute1" '||
1307 ' ,WFN.NUMBER_ATTRIBUTE2 "Number Attribute2" '||
1308 ' ,WFN.NUMBER_ATTRIBUTE3 "Number Attribute3" '||
1309 ' ,WFN.NUMBER_ATTRIBUTE4 "Number Attribute4" '||
1310 ' ,WFN.NUMBER_ATTRIBUTE5 "Number Attribute5" '||
1311 ' FROM '||
1312 ' WF_NOTIFICATIONS WFN '||
1313 ' ,WF_NOTIFICATION_ATTRIBUTES WFNA '||
1314 ' ,WF_ITEMS WFI '||
1315 ' WHERE '||
1319 ' AND '||
1316 ' WFI.ITEM_KEY = substr(WFN.context,instr(WFN.context,'':'',1)+1, (instr(WFN.context,'':'',-1,1) - instr(WFN.context,'':'',1)-1)) '||
1317 ' AND '||
1318 ' WFN.MESSAGE_TYPE = ''ECO_APP'' '||
1320 ' WFI.ITEM_TYPE = ''ECO_APP'' '||
1321 ' AND '||
1322 ' WFN.notification_id = WFNA.notification_id '||
1323 ' AND '||
1324 ' WFNA.name = ''RESULT'' ';
1325
1326 if l_org_id is not null then
1327 sqltxt :=sqltxt||' AND SUBSTR(WFI.ITEM_KEY,1,(instr(WFI.ITEM_KEY,''-'',-1,1)-1)) = '''||l_eco_name||'-'||l_org_id||''' ';
1328 else
1329 sqltxt :=sqltxt||' AND SUBSTR(WFI.ITEM_KEY,1,(instr(WFI.ITEM_KEY,''-'',-3,1)-1)) = '''||l_eco_name||''' ';
1330 end if;
1331 sqltxt :=sqltxt||' and rownum < '||row_limit;
1332 sqltxt :=sqltxt||' order by WFI.ITEM_KEY, WFN.NOTIFICATION_ID,WFN.STATUS ';
1333
1334 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Eco Workflow Notification Results ');
1335 If (num_rows = row_limit -1 ) Then
1336 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1337 End If;
1338 statusStr := 'SUCCESS';
1339 isFatal := 'FALSE';
1340
1341
1342 /* End of details of the notification results fired from an eco */
1343
1344 /* Fetch records from eng_ecn_approvers_v view about the ECO Approvers .*/
1345 sqltxt :=' SELECT '||
1346 ' EECV.CHANGE_NOTICE "CHANGE NOTICE", '||
1347 ' MP1.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1348 ' EECV.ORGANIZATION_ID "ORGANIZATION ID", '||
1349 ' EEAL.APPROVAL_LIST_NAME "APPROVAL LIST NAME", '||
1350 ' EEAL.APPROVAL_LIST_ID "APPROVAL LIST ID", '||
1351 ' EEAL.DESCRIPTION "DESCRIPTION (List)", '||
1352 ' to_char(EEAL.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE (List)", '||
1353 ' EEAV.APPROVER_NAME "APPROVER NAME", '||
1354 ' EEAV.SEQUENCE1 "SEQUENCE1", '||
1355 ' EEAV.SEQUENCE2 "SEQUENCE2", '||
1356 ' EEAV.DESCRIPTION "DESCRIPTION (Approver)", '||
1357 ' to_char(EEAV.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE (Approver)",'||
1358 ' EEAV.EMPLOYEE_ID "EMPLOYEE ID" '||
1359 ' from eng_engineering_changes_v eecv, '||
1360 ' eng_ecn_approval_lists eeal, eng_ecn_approvers_v eeav, '||
1361 ' mtl_parameters mp1 '||
1362 ' where eecv.approval_list_id=eeal.approval_list_id '||
1363 ' and eeal.approval_list_id=eeav.approval_list_id '||
1364 ' and eecv.organization_id = mp1.organization_id '||
1365 ' and eecv.change_notice='''||l_eco_name||''' ';
1366
1367 if l_org_id is not null then
1368 sqltxt :=sqltxt||' and eecv.organization_id = '||l_org_id;
1369 end if;
1370 sqltxt :=sqltxt||' and rownum < '||row_limit;
1371 sqltxt :=sqltxt||' order by mp1.organization_code, eeal.approval_list_name, '||
1372 ' eeav.approver_name ';
1373
1374 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Approvers ');
1375 If (num_rows = row_limit -1 ) Then
1376 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1377 End If;
1378 statusStr:= 'SUCCESS';
1379 isFatal := 'FALSE';
1380
1381 /* End of records from eng_ecn_approvers_v view about the ECO Approvers .*/
1382
1383 /* Fetch records from wf_roles table about the ECO Approvers Roles.*/
1384 sqltxt :=' SELECT '||
1385 ' EECV.CHANGE_NOTICE "CHANGE NOTICE" '||
1386 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1387 ' ,EECV.ORGANIZATION_ID "ORGANIZATION ID" '||
1388 ' ,EECV.APPROVAL_LIST_NAME "APPROVAL LIST NAME" '||
1389 ' ,EECV.APPROVAL_LIST_ID "APPROVAL LIST ID" '||
1390 ' ,WFR.NAME "ROLE NAME" '||
1391 ' ,WFR.DISPLAY_NAME "DISPLAY NAME" '||
1392 ' ,WFR.DESCRIPTION "DESCRIPTION" '||
1393 ' ,WFR.NOTIFICATION_PREFERENCE "NOTIFICATION PREFERENCE" '||
1394 ' ,WFR.LANGUAGE "LANGUAGE" '||
1395 ' ,WFR.TERRITORY "TERRITORY" '||
1396 ' ,WFR.EMAIL_ADDRESS "EMAIL ADDRESS" '||
1397 ' ,WFR.FAX "FAX" '||
1398 ' ,WFR.ORIG_SYSTEM "ORIG SYSTEM" '||
1399 ' ,WFR.ORIG_SYSTEM_ID "ORIG SYSTEM ID" '||
1400 ' ,to_char(WFR.START_DATE,''DD-MON-YYYY HH24:MI:SS'') "START DATE" '||
1401 ' ,WFR.STATUS "STATUS" '||
1402 ' ,to_char(WFR.EXPIRATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "EXPIRATION DATE" '||
1403 ' ,WFR.PARTITION_ID "PARTITION ID" '||
1404 ' from eng_engineering_changes_v eecv, '||
1405 ' eng_ecn_approval_lists eeal, wf_roles wfr, '||
1406 ' mtl_parameters mp1 '||
1407 ' where eecv.approval_list_id=eeal.approval_list_id '||
1408 ' and eeal.approval_list_id=wfr.orig_system_id '||
1409 ' and wfr.orig_system=''ENG_LIST'' '||
1410 ' and eecv.organization_id = mp1.organization_id '||
1411 ' and eecv.change_notice='''||l_eco_name||''' ';
1412
1413 if l_org_id is not null then
1414 sqltxt :=sqltxt||' and eecv.organization_id = '||l_org_id;
1415 end if;
1416 sqltxt :=sqltxt||' and rownum < '||row_limit;
1417 sqltxt :=sqltxt||' order by mp1.organization_code, wfr.display_name ';
1418
1419 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Workflow Approver Roles (from wf_roles view) ');
1420 If (num_rows = row_limit -1 ) Then
1421 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1425
1422 End If;
1423 statusStr:= 'SUCCESS';
1424 isFatal := 'FALSE';
1426 /* End of records from wf_roles table about the ECO Approvers Roles*/
1427
1428
1429 /* Fetch records from wf_users table about the ECO Approvers (users on an approval list).*/
1430 sqltxt :=' SELECT '||
1431 ' EECV.CHANGE_NOTICE "CHANGE NOTICE" '||
1432 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1433 ' ,EECV.ORGANIZATION_ID "ORGANIZATION ID" '||
1434 ' ,EECV.APPROVAL_LIST_NAME "APPROVAL LIST NAME" '||
1435 ' ,EECV.APPROVAL_LIST_ID "APPROVAL LIST ID" '||
1436 ' ,WFU.NAME "USER NAME" '||
1437 ' ,WFU.DISPLAY_NAME "DISPLAY NAME" '||
1438 ' ,WFU.DESCRIPTION "DESCRIPTION" '||
1439 ' ,WFU.NOTIFICATION_PREFERENCE "NOTIFICATION PREFERENCE" '||
1440 ' ,WFU.LANGUAGE "LANGUAGE" '||
1441 ' ,WFU.TERRITORY "TERRITORY" '||
1442 ' ,WFU.EMAIL_ADDRESS "EMAIL ADDRESS" '||
1443 ' ,WFU.FAX "FAX" '||
1444 ' ,WFU.ORIG_SYSTEM "ORIG SYSTEM" '||
1445 ' ,WFU.ORIG_SYSTEM_ID "ORIG SYSTEM ID" '||
1446 ' ,to_char(WFU.START_DATE,''DD-MON-YYYY HH24:MI:SS'')"START DATE" '||
1447 ' ,WFU.STATUS "STATUS" '||
1448 ' ,to_char(WFU.EXPIRATION_DATE,''DD-MON-YYYY HH24:MI:SS'')"EXPIRATION DATE" '||
1449 ' ,WFU.PARTITION_ID "PARTITION ID" '||
1450 ' from eng_engineering_changes_v eecv, '||
1451 ' eng_ecn_approvers_v eea , wf_users wfu, '||
1452 ' mtl_parameters mp1 '||
1453 ' where eecv.approval_list_id=eea.approval_list_id '||
1454 ' and eea.employee_id=wfu.orig_system_id '||
1455 ' and eecv.organization_id = mp1.organization_id '||
1456 ' and eecv.change_notice='''||l_eco_name||''' ';
1457
1458 if l_org_id is not null then
1459 sqltxt :=sqltxt||' and eecv.organization_id = '||l_org_id;
1460 end if;
1461 sqltxt :=sqltxt||' and rownum < '||row_limit;
1462 sqltxt :=sqltxt||' order by mp1.organization_code, wfu.display_name ';
1463
1464 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Workflow Approvers (from wf_users view) ');
1465 If (num_rows = row_limit -1 ) Then
1466 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1467 End If;
1468 statusStr:= 'SUCCESS';
1469 isFatal := 'FALSE';
1470 /* End of records from wf_users table about the ECO Approvers.*/
1471
1472
1473 /* Fetch records from wf_user_roles table about the association between users and roles (users on an approval list).*/
1474 sqltxt :=' SELECT '||
1475 ' EECV.CHANGE_NOTICE "CHANGE NOTICE" '||
1476 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1477 ' ,EECV.ORGANIZATION_ID "ORGANIZATION ID" '||
1478 ' ,EECV.APPROVAL_LIST_NAME "APPROVAL LIST NAME" '||
1479 ' ,EECV.APPROVAL_LIST_ID "APPROVAL LIST ID" '||
1480 ' ,EEAV.APPROVER_NAME "APPROVER NAME" '||
1481 ' ,WFUR.USER_NAME "USER NAME" '||
1482 ' ,WFUR.ROLE_NAME "ROLE NAME" '||
1483 ' ,WFUR.USER_ORIG_SYSTEM "USER ORIG SYSTEM" '||
1484 ' ,WFUR.USER_ORIG_SYSTEM_ID "USER ORIG SYSTEM ID" '||
1485 ' ,WFUR.ROLE_ORIG_SYSTEM "ROLE ORIG SYSTEM" '||
1486 ' ,WFUR.ROLE_ORIG_SYSTEM_ID "ROLE ORIG SYSTEM ID" '||
1487 ' ,to_char(WFUR.START_DATE,''DD-MON-YYYY HH24:MI:SS'') "START DATE" '||
1488 ' ,to_char(WFUR.EXPIRATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "EXPIRATION DATE"'||
1489 ' ,WFUR.PARTITION_ID "PARTITION ID" '||
1490 ' ,WFUR.ASSIGNMENT_REASON "ASSIGNMENT REASON" '||
1491 'from wf_user_roles wfur, eng_ecn_approvers_v eeav, '||
1492 ' eng_engineering_changes_v eecv, '||
1493 ' mtl_parameters mp1 '||
1494 'where wfur.user_orig_system_id=eeav.employee_id '||
1495 'and wfur.role_orig_system_id=eeav.approval_list_id '||
1496 'and wfur.role_orig_system=''ENG_LIST'' '||
1497 'and eeav.approval_list_id= eecv.approval_list_id '||
1498 'and eecv.organization_id = mp1.organization_id '||
1499 'and eecv.change_notice='''||l_eco_name||''' ';
1500
1501 if l_org_id is not null then
1502 sqltxt :=sqltxt||' and eecv.organization_id = '||l_org_id;
1503 end if;
1504 sqltxt :=sqltxt||' and rownum < '||row_limit;
1505 sqltxt :=sqltxt||' order by mp1.organization_code, wfur.user_name, wfur.role_name ';
1506
1507 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Association between WF Users, Roles(from wf_user_roles view) ');
1508 If (num_rows = row_limit -1 ) Then
1509 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1510 End If;
1511 statusStr:= 'SUCCESS';
1512 isFatal := 'FALSE';
1513 /* End of records from wf_user_roles table */
1514
1515
1516 /* SQL to fetch organization hierarchy structure*/
1517 sqltxt := ' SELECT EEC.CHANGE_NOTICE "CHANGE NOTICE" '||
1518 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1519 ' ,EEC.ORGANIZATION_ID "ORGANIZATION ID" '||
1520 ' ,OS.NAME "Org Hierarchy Name" '||
1521 ' ,OSV.VERSION_NUMBER "Version Number" '||
1522 ' ,to_char(OSV.DATE_FROM,''DD-MON-YYYY HH24:MI:SS'') "Date From" '||
1523 ' ,to_char(OSV.DATE_TO,''DD-MON-YYYY HH24:MI:SS'') "Date To" '||
1524 ' ,OSE.D_PARENT_NAME "D Parent Name" '||
1525 ' ,OSE.D_CHILD_NAME "D Child Name" '||
1526 ' ,OSE.ORGANIZATION_ID_PARENT "Organization Id Parent" '||
1527 ' ,OSE.ORGANIZATION_ID_CHILD "Organization Id Child" '||
1528 ' ,OSE.BUSINESS_GROUP_ID "Business Group Id" '||
1532 ' FROM PER_ORGANIZATION_STRUCTURES OS '||
1529 ' ,OSE.ORG_STRUCTURE_ELEMENT_ID "Org Structure Element Id" '||
1530 ' ,OSE.ORG_STRUCTURE_VERSION_ID "Org Structure Version Id" '||
1531 ' ,OSE.POSITION_CONTROL_ENABLED_FLAG "Position Control Enabled Flag" '||
1533 ' ,PER_ORG_STRUCTURE_VERSIONS OSV '||
1534 ' ,PER_ORG_STRUCTURE_ELEMENTS_V OSE '||
1535 ' ,ENG_ENGINEERING_CHANGES EEC '||
1536 ' ,mtl_parameters mp1 '||
1537 ' WHERE 1=1 '||
1538 ' AND OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID '||
1539 ' AND OSV.ORG_STRUCTURE_VERSION_ID = OSE.ORG_STRUCTURE_VERSION_ID '||
1540 ' AND OS.NAME = EEC.ORGANIZATION_HIERARCHY '||
1541 ' AND eec.organization_id = mp1.organization_id '||
1542 ' AND eec.change_notice = '''||l_eco_name||''' ';
1543
1544 if l_org_id is not null then
1545 sqltxt :=sqltxt||' and eec.organization_id = '||l_org_id;
1546 end if;
1547 sqltxt :=sqltxt||' and rownum < '||row_limit;
1548 sqltxt :=sqltxt||' order by mp1.organization_code, osv.version_number,ose.d_child_name';
1549
1550 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Organization Hierarchy Structure ');
1551 If (num_rows = row_limit -1 ) Then
1552 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1553 End If;
1554 statusStr:= 'SUCCESS';
1555 isFatal := 'FALSE';
1556
1557 /* End of SQL to fetch organization hierarchy structure*/
1558
1559 /* SQL to fetch use_up details of revised items*/
1560 sqltxt :=' SELECT MP.ORGANIZATION_CODE "Org Code" '||
1561 ' ,MBP.ITEM "Item" '||
1562 ' ,MBP.PLAN_NAME "Plan Name" '||
1563 ' ,to_char(MBP.COMPLETION_DATE,''DD-MON-YYYY HH24:MI:SS'') "Completion Date" '||
1564 ' ,to_char(MBP.USE_UP_DATE,''DD-MON-YYYY HH24:MI:SS'') "Use Up Date" '||
1565 ' ,MBP.ITEM_ID "Item Id" '||
1566 ' ,MBP.ORGANIZATION_ID "Org Id" '||
1567 ' FROM MRP_BOM_PLAN_NAME_LOV_V MBP '||
1568 ' ,MTL_PARAMETERS MP '||
1569 ' WHERE MBP.ORGANIZATION_ID = MP.ORGANIZATION_ID(+) '||
1570 ' AND ( MBP.Organization_Id, MBP.Item_Id) in '||
1571 ' ( '||
1572 ' SELECT eri.organization_id, BCB.component_item_id '||
1573 ' FROM eng_revised_items eri '||
1574 ' ,bom_inventory_components BCB '||
1575 ' WHERE 1=1 '||
1576 ' AND eri.bill_sequence_id = BCB.bill_sequence_id '||
1577 ' AND nvl(BCB.acd_type,1) != 3 '||
1578 ' AND trunc(BCB.effectivity_date) <= mbp.use_up_date '||
1579 ' AND nvl(BCB.disable_date, mbp.use_up_date + 1) > mbp.use_up_date '||
1580 ' AND BCB.implementation_date is NOT NULL '||
1581 ' AND eri.change_notice = '''||l_eco_name||''' ';
1582
1583 if l_org_id is not null then
1584 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
1585 end if;
1586
1587
1588 sqltxt :=sqltxt||' UNION '||
1589 ' SELECT eri.organization_id, bbom.assembly_item_id '||
1590 ' FROM eng_revised_items eri '||
1591 ' ,bom_bill_of_materials bbom '||
1592 ' WHERE 1=1 '||
1593 ' AND eri.revised_item_id = bbom.assembly_item_id '||
1594 ' AND eri.organization_id = bbom.organization_id '||
1595 ' AND eri.bill_sequence_id= bbom.bill_sequence_id '||
1596 ' AND eri.change_notice = '''||l_eco_name||''' ';
1597
1598 if l_org_id is not null then
1599 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
1600 end if;
1601
1602 sqltxt :=sqltxt||' )and rownum < '||row_limit||
1603 ' order by mp.organization_code, mbp.item, mbp.plan_name, mbp.use_up_date ';
1604
1605 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Eco Revised Items Use Up Details ');
1606 If (num_rows = row_limit -1 ) Then
1607 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1608 End If;
1609 statusStr:= 'SUCCESS';
1610 isFatal := 'FALSE';
1611 /* End of SQL to fetch use_up details of revised items*/
1612
1613 <<l_test_end>>
1614 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This data collection script completed as expected <BR/>');
1615 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1616 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1617
1618 Elsif l_mco_exists =1 Then /* run these scripts if the MCO is exists */
1619
1620 /* SQL to fetch mass change order details */
1621 /* Get the application installation info. References to Data Dictionary Objects without schema name
1622 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
1623 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
1624
1625 l_ret_status := fnd_installation.get_app_info ('ENG'
1626 , l_status
1627 , l_industry
1628 , l_oracle_schema
1629 );
1630 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
1631
1632 sqltxt :='SELECT '||
1633 ' EECIV.CHANGE_NOTICE "Mass Change Number" '||
1634 ' ,MP.ORGANIZATION_CODE "Org Code" '||
1635 ' ,EECIV.ORGANIZATION_ID "Org Id" '||
1636 ' ,EECIV.CHANGE_ORDER_TYPE "Change Order Type" '||
1637 ' ,EECIV.DESCRIPTION "Description" '||
1638 ' ,DECODE(ERIIV.INCREMENT_REV,null,null,1,''Yes (1)'',2,''No (2)'', '||
1642 ' ''OTHER ('' || ERIIV.UPDATE_WIP || '')'') "Update Wip" '||
1639 ' ''OTHER ('' || ERIIV.INCREMENT_REV || '')'') "Increment Rev" '||
1640 ' ,ERIIV.FROM_END_ITEM_UNIT_NUMBER "From End Item Unit Number" '||
1641 ' ,DECODE(ERIIV.UPDATE_WIP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1643 ' ,ERIIV.CATEGORY_SET_NAME "Category Set Name" '||
1644 ' ,ERIIV.CATEGORY_FROM "Category From" '||
1645 ' ,ERIIV.CATEGORY_TO "Category To" '||
1646 ' ,ERIIV.ITEM_FROM "Item From" '||
1647 ' ,ERIIV.ITEM_TO "Item To" '||
1648 ' ,ERIIV.ALTERNATE_SELECTION_CODE "Alternate Selection Code" '||
1649 ' ,ERIIV.ALTERNATE_BOM_DESIGNATOR "Alternate Bom Designator" '||
1650 ' ,ERIIV.BASE_ITEM_ID "Base Item Id" '||
1651 ' ,ERIIV.ITEM_TYPE "Item Type" '||
1652 ' ,ERIIV.ORGANIZATION_ID "Org Id" '||
1653 ' ,to_char(ERIIV.SCHEDULED_DATE,''DD-MON-YYYY HH24:MI:SS'') "Scheduled Date" '||
1654 ' ,DECODE(ERIIV.MRP_ACTIVE,null,null,1,''Yes (1)'',2,''No (2)'', '||
1655 ' ''OTHER ('' || ERIIV.MRP_ACTIVE || '')'') "Mrp Active" '||
1656 ' ,DECODE(ERIIV.USE_UP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1657 ' ''OTHER ('' || ERIIV.USE_UP || '')'') "Use Up" '||
1658 ' ,ERIIV.USE_UP_ITEM_ID "Use Up Item Id" '||
1659 ' ,ERIIV.REVISED_ITEM_SEQUENCE_ID "Revised Item Sequence Id" '||
1660 ' ,ERIIV.CATEGORY_SET_ID "Category Set Id" '||
1661 ' ,ERIIV.STRUCTURE_ID "Structure Id" '||
1662 ' ,ERIIV.DDF_CONTEXT "Ddf Context" '||
1663 ' ,ERIIV.USE_UP_PLAN_NAME "Use Up Plan Name" '||
1664 ' ,ERIIV.USE_UP_ITEM_DESCRIPTION "Use Up Item Description" '||
1665 ' ,ERIIV.BASE_DESCRIPTION "Base Description" '||
1666 ' ,ERIIV.ITEM_TYPE_NAME "Item Type Name" '||
1667 ' ,to_char(ERIIV.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "Last Update Date" '||
1668 ' ,ERIIV.LAST_UPDATED_BY "Last Updated By" '||
1669 ' ,to_char(ERIIV.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "Creation Date" '||
1670 ' ,ERIIV.CREATED_BY "Created By" '||
1671 ' ,ERIIV.LAST_UPDATE_LOGIN "Last Update Login" '||
1672 ' FROM '||
1673 ' ENG_ENG_CHANGES_INTERFACE_V EECIV '||
1674 ' ,ENG_REVISED_ITEMS_INTERFACE_V ERIIV '||
1675 ' ,MTL_PARAMETERS MP '||
1676 ' WHERE '||
1677 ' EECIV.CHANGE_NOTICE = ERIIV.CHANGE_NOTICE '||
1678 ' AND EECIV.ORGANIZATION_ID = ERIIV.ORGANIZATION_ID '||
1679 ' AND EECIV.ORGANIZATION_ID = MP.ORGANIZATION_ID '||
1680 ' AND EECIV.change_notice = '''||l_eco_name||''' ';
1681
1682 if l_org_id is not null then
1683 sqltxt :=sqltxt||' and eeciv.organization_id = '||l_org_id;
1684 end if;
1685
1686 sqltxt :=sqltxt||' and rownum < '||row_limit||
1687 ' order by mp.organization_code ';
1688
1689 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Mass Change Orders ');
1690 If (num_rows = row_limit -1 ) Then
1691 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1692 End If;
1693 statusStr:= 'SUCCESS';
1694 isFatal := 'FALSE';
1695
1696 /* End of SQL to fetch mass change order details */
1697
1698 /* SQL to fetch component changes on mass change orders */
1699 sqltxt :='SELECT '||
1700 ' ERI.CHANGE_NOTICE "Mass Change Number" '||
1701 ' ,MP.ORGANIZATION_CODE "Org Code" '||
1702 ' ,ERI.ORGANIZATION_ID "Org Id" '||
1703 ' ,MIF.ITEM_NUMBER "Item" '||
1704 ' ,BICO.OPERATION_SEQ_NUM "Operation Seq Num" '||
1705 ' ,BICO.COMPONENT_ITEM_ID "Component Item Id" '||
1706 ' ,BICO.ITEM_NUM "Item Num" '||
1707 ' ,BICO.COMPONENT_QUANTITY "Component Qty" '||
1708 ' ,BICO.COMPONENT_YIELD_FACTOR "Component Yield Factor" '||
1709 ' ,to_char(BICO.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "Disable Date" '||
1710 ' ,BICO.PLANNING_FACTOR "Planning Factor" '||
1711 ' ,DECODE(BICO.QUANTITY_RELATED,null,null,1,''Yes (1)'',2,''No (2)'', '||
1712 ' ''OTHER ('' || BICO.QUANTITY_RELATED || '')'') "Qty Related" '||
1713 ' ,BICO.SO_BASIS "So Basis" '||
1714 ' ,DECODE(BICO.OPTIONAL,null,null,1,''Yes (1)'',2,''No (2)'', '||
1715 ' ''OTHER ('' || BICO.OPTIONAL || '')'') "Optional" '||
1716 ' ,DECODE(BICO.MUTUALLY_EXCLUSIVE_OPTIONS,null,null,1,''Yes (1)'',2,''No (2)'', '||
1717 ' ''OTHER ('' || BICO.MUTUALLY_EXCLUSIVE_OPTIONS || '')'') "Mutually Exclusive Options" '||
1718 ' ,DECODE(BICO.INCLUDE_IN_COST_ROLLUP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1719 ' ''OTHER ('' || BICO.INCLUDE_IN_COST_ROLLUP || '')'') "Include In Cost Rollup" '||
1720 ' ,DECODE(BICO.CHECK_ATP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1721 ' ''OTHER ('' || BICO.CHECK_ATP || '')'') "Check Atp" '||
1722 ' ,DECODE(BICO.SHIPPING_ALLOWED,null,null,1,''Yes (1)'',2,''No (2)'', '||
1723 ' ''OTHER ('' || BICO.SHIPPING_ALLOWED || '')'') "Shipping Allowed" '||
1724 ' ,DECODE(BICO.REQUIRED_TO_SHIP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1725 ' ''OTHER ('' || BICO.REQUIRED_TO_SHIP || '')'') "Required To Ship" '||
1726 ' ,DECODE(BICO.REQUIRED_FOR_REVENUE,null,null,1,''Yes (1)'',2,''No (2)'', '||
1727 ' ''OTHER ('' || BICO.REQUIRED_FOR_REVENUE || '')'') "Required For Revenue" '||
1728 ' ,DECODE(BICO.INCLUDE_ON_SHIP_DOCS,null,null,1,''Yes (1)'',2,''No (2)'', '||
1729 ' ''OTHER ('' || BICO.INCLUDE_ON_SHIP_DOCS || '')'') "Include On Ship Docs" '||
1730 ' ,DECODE(BICO.INCLUDE_ON_BILL_DOCS,null,null,1,''Yes (1)'',2,''No (2)'', '||
1731 ' ''OTHER ('' || BICO.INCLUDE_ON_BILL_DOCS || '')'') "Include On Bill Docs" '||
1732 ' ,BICO.LOW_QUANTITY "Low Qty" '||
1736 ' ,BICO.OLD_COMPONENT_SEQUENCE_ID "Old Component Sequence Id" '||
1733 ' ,BICO.HIGH_QUANTITY "High Qty" '||
1734 ' ,DECODE(BICO.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
1735 ' ''Other ('' || BICO.ACD_TYPE || '')'') "Acd Type" '||
1737 ' ,BICO.COMPONENT_SEQUENCE_ID "Component Sequence Id" '||
1738 ' ,BICO.WIP_SUPPLY_TYPE "Wip Supply Type" '||
1739 ' ,BICO.SUPPLY_SUBINVENTORY "Supply Subinv" '||
1740 ' ,BICO.SUPPLY_LOCATOR_ID "Supply Locator Id" '||
1741 ' ,BICO.REVISED_ITEM_SEQUENCE_ID "Revised Item Sequence Id" '||
1742 ' ,BICO.COST_FACTOR "Cost Factor" '||
1743 ' ,BICO.DDF_CONTEXT1 "Ddf Context1" '||
1744 ' ,BICO.DDF_CONTEXT2 "Ddf Context2" '||
1745 ' ,BICO.DESCRIPTION "Description" '||
1746 ' ,BICO.PRIMARY_UOM_CODE "Primary Uom Code" '||
1747 ' ,DECODE(MLU_BIT.MEANING,null,null, '||
1748 ' (MLU_BIT.MEANING || '' ('' || BICO.BOM_ITEM_TYPE || '')'')) "BOM ITEM TYPE" '||
1749 ' ,BICO.ATP_COMPONENTS_FLAG "Atp Components Flag" '||
1750 ' ,BICO.REPLENISH_TO_ORDER_FLAG "Replenish To Order Flag" '||
1751 ' ,BICO.DEFAULT_SHIPPABLE "Default Shippable" '||
1752 ' ,BICO.DEFAULT_COST_ROLLUP "Default Cost Rollup" '||
1753 ' ,BICO.DEFAULT_WIP_SUPPLY_TYPE "Default Wip Supply Type" '||
1754 ' ,BICO.DEFAULT_SUPPLY_LOCATOR_ID "Default Supply Locator Id" '||
1755 ' ,BICO.DEFAULT_SUPPLY_SUBINVENTORY "Default Supply Subinv" '||
1756 ' ,BICO.DEFAULT_CHECK_ATP "Default Check Atp" '||
1757 ' ,BICO.ITEM_LOCATOR_CONTROL "Item Locator Control" '||
1758 ' ,BICO.RESTRICT_LOCATORS_FLAG "Restrict Locators Flag" '||
1759 ' ,BICO.RESTRICT_SUBINVENTORIES_FLAG "Restrict Subinventories Flag" '||
1760 ' ,BICO.INVENTORY_ASSET_FLAG "Inv Asset Flag" '||
1761 ' ,BICO.SHIPPABLE_ITEM_FLAG "Shippable Item Flag" '||
1762 ' ,BICO.REVISION "Rev" '||
1763 ' ,BICO.ITEM_TYPE_NAME "Item Type Name" '||
1764 ' ,BICO.ACD_TYPE_NAME "Acd Type Name" '||
1765 ' ,BICO.SUPPLY_TYPE "Supply Type" '||
1766 ' ,BICO.BASIS_TYPE "Basis Type" '||
1767 ' ,BICO.TO_END_ITEM_UNIT_NUMBER "To End Item Unit Number" '||
1768 ' ,to_char(BICO.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "Last Update Date" '||
1769 ' ,BICO.LAST_UPDATED_BY "Last Updated By" '||
1770 ' ,to_char(BICO.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "Creation Date" '||
1771 ' ,BICO.CREATED_BY "Created By" '||
1772 ' ,BICO.LAST_UPDATE_LOGIN "Last Update Login" '||
1773 ' ,BICO.REQUEST_ID "Request Id" '||
1774 ' ,BICO.PROGRAM_APPLICATION_ID "Program Application Id" '||
1775 ' ,BICO.PROGRAM_ID "Program Id" '||
1776 ' ,to_char(BICO.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "Program Update Date" '||
1777 ' FROM '||
1778 ' BOM_INV_COMPS_INTERFACE_V BICO '||
1779 ' ,ENG_REVISED_ITEMS_INTERFACE ERI '||
1780 ' ,MTL_PARAMETERS MP '||
1781 ' ,MTL_ITEM_FLEXFIELDS MIF '||
1782 ' ,MFG_LOOKUPS MLU_BIT '||
1783 ' WHERE 1=1 '||
1784 ' AND BICO.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID '||
1785 ' AND ERI.ORGANIZATION_ID = MP.ORGANIZATION_ID '||
1786 ' AND ERI.ORGANIZATION_ID = MIF.ORGANIZATION_ID '||
1787 ' AND BICO.COMPONENT_ITEM_ID = MIF.INVENTORY_ITEM_ID '||
1788 ' AND BICO.BOM_ITEM_TYPE=MLU_BIT.LOOKUP_CODE(+) AND ''BOM_ITEM_TYPE''=MLU_BIT.LOOKUP_TYPE(+) '||
1789 ' AND ERI.change_notice = '''||l_eco_name||''' ';
1790
1791 if l_org_id is not null then
1792 sqltxt :=sqltxt||' and eri.organization_id = '||l_org_id;
1793 end if;
1794
1795 sqltxt :=sqltxt||' and rownum < '||row_limit||
1796 ' order by mp.organization_code,mif.item_number,bico.operation_seq_num,bico.item_num ';
1797
1798 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Component Changes on Mass Change Orders ');
1799 If (num_rows = row_limit -1 ) Then
1800 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/> Output of the above table is limited to the first '||(row_limit-1)||' rows to prevent an excessively big output file. <BR/>');
1801 End If;
1802 statusStr:= 'SUCCESS';
1803 isFatal := 'FALSE';
1804
1805 /* End of SQL to fetch component changes on mass change orders */
1806
1807 <<l_test_end>>
1808 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This data collection script completed as expected <BR/>');
1809 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1810 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1811
1812 End If; /* if l_eco_exists != 0 or l_mco_exists !=0 */
1813
1814 End If; /* if l_eco_name is not null */
1815
1816 EXCEPTION
1817 when others then
1818 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
1819 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
1820 statusStr := 'FAILURE';
1821 errStr := sqlerrm ||' occurred in script. ';
1822 fixInfo := 'Unexpected Exception in BOMDGCNB.pls';
1823 isFatal := 'FALSE';
1824 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1825 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1826 END runTest;
1827
1828 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
1829 BEGIN
1830 name := 'ECO Data Collection';
1831 END getComponentName;
1832
1833 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
1834 BEGIN
1835 descStr := ' This data collection script collects data about ECO Details. <BR/>
1836 Input for field ChangeNotice is mandatory. ';
1837 END getTestDesc;
1838
1839 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
1840 BEGIN
1841 name := 'ECO Data Collection';
1842 END getTestName;
1843
1844 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
1845 tempDependencies JTF_DIAG_DEPENDTBL;
1846
1847 BEGIN
1848 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
1849 END getDependencies;
1850
1851 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
1852 BEGIN
1853 str := 'FALSE';
1854 END isDependencyPipelined;
1855
1856
1857 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
1858 tempOutput JTF_DIAG_OUTPUTTBL;
1859 BEGIN
1860 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1861 outputValues := tempOutput;
1862 EXCEPTION
1863 when others then
1864 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
1865 END getOutputValues;
1866
1867
1868 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
1869 tempInput JTF_DIAG_INPUTTBL;
1870 BEGIN
1871 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1872 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ChangeNotice','LOV-oracle.apps.bom.diag.lov.ECOLov'); -- Lov name modified to ChangeNotice for bug 6412260
1876 when others then
1873 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov'); -- Lov name modified to OrgId for bug 6412260
1874 defaultInputValues := tempInput;
1875 EXCEPTION
1877 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
1878 END getDefaultTestParams;
1879
1880 Function getTestMode return INTEGER IS
1881 BEGIN
1882 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
1883
1884 END getTestMode;
1885
1886 END BOM_DIAGUNITTEST_ECODATA;