[Home] [Help]
PACKAGE BODY: APPS.BOM_DIAGUNITTEST_INTFDATA
Source
1 package body BOM_DIAGUNITTEST_INTFDATA as
2 /* $Header: BOMDGINB.pls 120.1 2007/12/26 09:53:53 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_type VARCHAR2(40);
29 l_ret_status BOOLEAN;
30 l_status VARCHAR2 (1);
31 l_industry VARCHAR2 (1);
32 l_oracle_schema VARCHAR2 (30);
33 l_table_exists NUMBER;
34
35 BEGIN
36 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
37 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
38 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
39
40 /* set limit of records to be fetched*/
41 row_limit :=1000;
42
43 -- accept input
44 l_type := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Type',inputs);
45 /* Allowed values for input parameter type
46 1 - Inv
47 2 - Bom
48 3 - Rtg
49 4 - Eng
50 */
51
52 /* Vaaidate input values for TYPE */
53 If (l_type is null) or ( upper(l_type) not in ('INV', 'BOM','RTG','ENG')) Then
54 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid input Type');
55 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide a valid value for the input field Type. ');
56 statusStr := 'FAILURE';
57 isFatal := 'TRUE';
58 fixInfo := ' Please review the error message below and take corrective action. ';
59 errStr := ' Invalid value for input field Type. ';
60
61 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
62 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
63
64 Else /* valid value for input type */
65 If upper(l_type)='INV' Then
66 /* Items Interface Details */
67
68 /* Get the application installation info. References to Data Dictionary Objects without schema name
69 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
70 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
71
72 l_ret_status := fnd_installation.get_app_info ('INV'
73 , l_status
74 , l_industry
75 , l_oracle_schema
76 );
77
78 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
79
80
81 /* SQL to fetch records from mtl_system_items_interface */
82 sqltxt := 'SELECT ' ||
83 ' MSII.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
84 ' MSII.ORGANIZATION_ID "ORGANIZATION ID", '||
85 ' to_char(MSII.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
86 ' MSII.LAST_UPDATED_BY "LAST UPDATED BY", '||
87 ' to_char(MSII.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
88 ' MSII.CREATED_BY "CREATED BY", '||
89 ' MSII.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
90 ' MSII.SUMMARY_FLAG "SUMMARY FLAG", '||
91 ' MSII.ENABLED_FLAG "ENABLED FLAG", '||
92 ' to_char(MSII.START_DATE_ACTIVE,''DD-MON-YYYY HH24:MI:SS'') "START DATE ACTIVE", '||
93 ' to_char(MSII.END_DATE_ACTIVE,''DD-MON-YYYY HH24:MI:SS'') "END DATE ACTIVE", '||
94 ' MSII.DESCRIPTION "DESCRIPTION", '||
95 ' MSII.BUYER_ID "BUYER ID", '||
96 ' MSII.ACCOUNTING_RULE_ID "ACCOUNTING RULE ID", '||
97 ' MSII.INVOICING_RULE_ID "INVOICING RULE ID", '||
98 ' MSII.SEGMENT1 "SEGMENT1", '||
99 ' MSII.SEGMENT2 "SEGMENT2", '||
100 ' MSII.SEGMENT3 "SEGMENT3", '||
101 ' MSII.SEGMENT4 "SEGMENT4", '||
102 ' MSII.SEGMENT5 "SEGMENT5", '||
103 ' MSII.SEGMENT6 "SEGMENT6", '||
104 ' MSII.SEGMENT7 "SEGMENT7", '||
105 ' MSII.SEGMENT8 "SEGMENT8", '||
106 ' MSII.SEGMENT9 "SEGMENT9", '||
107 ' MSII.SEGMENT10 "SEGMENT10", '||
108 ' MSII.SEGMENT11 "SEGMENT11", '||
109 ' MSII.SEGMENT12 "SEGMENT12", '||
110 ' MSII.SEGMENT13 "SEGMENT13", '||
111 ' MSII.SEGMENT14 "SEGMENT14", '||
112 ' MSII.SEGMENT15 "SEGMENT15", '||
113 ' MSII.SEGMENT16 "SEGMENT16", '||
114 ' MSII.SEGMENT17 "SEGMENT17", '||
115 ' MSII.SEGMENT18 "SEGMENT18", '||
116 ' MSII.SEGMENT19 "SEGMENT19", '||
117 ' MSII.SEGMENT20 "SEGMENT20", '||
118 ' MSII.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
119 ' MSII.ATTRIBUTE1 "ATTRIBUTE1", '||
120 ' MSII.ATTRIBUTE2 "ATTRIBUTE2", '||
121 ' MSII.ATTRIBUTE3 "ATTRIBUTE3", '||
122 ' MSII.ATTRIBUTE4 "ATTRIBUTE4", '||
123 ' MSII.ATTRIBUTE5 "ATTRIBUTE5", '||
124 ' MSII.ATTRIBUTE6 "ATTRIBUTE6", '||
125 ' MSII.ATTRIBUTE7 "ATTRIBUTE7", '||
126 ' MSII.ATTRIBUTE8 "ATTRIBUTE8", '||
127 ' MSII.ATTRIBUTE9 "ATTRIBUTE9", '||
128 ' MSII.ATTRIBUTE10 "ATTRIBUTE10", '||
129 ' MSII.ATTRIBUTE11 "ATTRIBUTE11", '||
130 ' MSII.ATTRIBUTE12 "ATTRIBUTE12", '||
131 ' MSII.ATTRIBUTE13 "ATTRIBUTE13", '||
132 ' MSII.ATTRIBUTE14 "ATTRIBUTE14", '||
133 ' MSII.ATTRIBUTE15 "ATTRIBUTE15", '||
134 ' MSII.PURCHASING_ITEM_FLAG "PURCHASING ITEM FLAG", '||
135 ' MSII.SHIPPABLE_ITEM_FLAG "SHIPPABLE ITEM FLAG", '||
136 ' MSII.CUSTOMER_ORDER_FLAG "CUSTOMER ORDER FLAG", '||
137 ' MSII.INTERNAL_ORDER_FLAG "INTERNAL ORDER FLAG", '||
138 ' MSII.SERVICE_ITEM_FLAG "SERVICE ITEM FLAG", '||
139 ' MSII.INVENTORY_ITEM_FLAG "INVENTORY ITEM FLAG", '||
140 ' MSII.ENG_ITEM_FLAG "ENG ITEM FLAG", '||
141 ' MSII.INVENTORY_ASSET_FLAG "INVENTORY ASSET FLAG", '||
142 ' MSII.PURCHASING_ENABLED_FLAG "PURCHASING ENABLED FLAG", '||
143 ' MSII.CUSTOMER_ORDER_ENABLED_FLAG "CUSTOMER ORDER ENABLED FLAG", '||
144 ' MSII.INTERNAL_ORDER_ENABLED_FLAG "INTERNAL ORDER ENABLED FLAG", '||
145 ' MSII.SO_TRANSACTIONS_FLAG "SO TRANSACTIONS FLAG", '||
146 ' MSII.MTL_TRANSACTIONS_ENABLED_FLAG "MTL TRANSACTIONS ENABLED FLAG", '||
147 ' MSII.STOCK_ENABLED_FLAG "STOCK ENABLED FLAG", '||
148 ' MSII.BOM_ENABLED_FLAG "BOM ENABLED FLAG", '||
149 ' MSII.BUILD_IN_WIP_FLAG "BUILD IN WIP FLAG", '||
150 ' MSII.REVISION_QTY_CONTROL_CODE "REVISION QTY CONTROL CODE", '||
151 ' MSII.ITEM_CATALOG_GROUP_ID "ITEM CATALOG GROUP ID", '||
152 ' MSII.CATALOG_STATUS_FLAG "CATALOG STATUS FLAG", '||
153 ' MSII.CHECK_SHORTAGES_FLAG "CHECK SHORTAGES FLAG", '||
154 ' MSII.RETURNABLE_FLAG "RETURNABLE FLAG", '||
155 ' MSII.DEFAULT_SHIPPING_ORG "DEFAULT SHIPPING ORG", '||
156 ' MSII.COLLATERAL_FLAG "COLLATERAL FLAG", '||
157 ' MSII.TAXABLE_FLAG "TAXABLE FLAG", '||
158 ' MSII.QTY_RCV_EXCEPTION_CODE "QTY RCV EXCEPTION CODE", '||
159 ' MSII.ALLOW_ITEM_DESC_UPDATE_FLAG "ALLOW ITEM DESC UPDATE FLAG", '||
160 ' MSII.INSPECTION_REQUIRED_FLAG "INSPECTION REQUIRED FLAG", '||
161 ' MSII.RECEIPT_REQUIRED_FLAG "RECEIPT REQUIRED FLAG", '||
162 ' MSII.MARKET_PRICE "MARKET PRICE", '||
163 ' MSII.HAZARD_CLASS_ID "HAZARD CLASS ID", '||
164 ' MSII.RFQ_REQUIRED_FLAG "RFQ REQUIRED FLAG", '||
165 ' MSII.QTY_RCV_TOLERANCE "QTY RCV TOLERANCE", '||
166 ' MSII.LIST_PRICE_PER_UNIT "LIST PRICE PER UNIT", '||
167 ' MSII.UN_NUMBER_ID "UN NUMBER ID", '||
168 ' MSII.PRICE_TOLERANCE_PERCENT "PRICE TOLERANCE PERCENT", '||
169 ' MSII.ASSET_CATEGORY_ID "ASSET CATEGORY ID", '||
170 ' MSII.ROUNDING_FACTOR "ROUNDING FACTOR", '||
171 ' MSII.UNIT_OF_ISSUE "UNIT OF ISSUE", '||
172 ' MSII.ENFORCE_SHIP_TO_LOCATION_CODE "ENFORCE SHIP TO LOCATION CODE", '||
173 ' MSII.ALLOW_SUBSTITUTE_RECEIPTS_FLAG "ALLOW SUBSTITUTE RECEIPTS FLAG", '||
174 ' MSII.ALLOW_UNORDERED_RECEIPTS_FLAG "ALLOW UNORDERED RECEIPTS FLAG", '||
175 ' MSII.ALLOW_EXPRESS_DELIVERY_FLAG "ALLOW EXPRESS DELIVERY FLAG", '||
176 ' MSII.DAYS_EARLY_RECEIPT_ALLOWED "DAYS EARLY RECEIPT ALLOWED", '||
177 ' MSII.DAYS_LATE_RECEIPT_ALLOWED "DAYS LATE RECEIPT ALLOWED", '||
178 ' MSII.RECEIPT_DAYS_EXCEPTION_CODE "RECEIPT DAYS EXCEPTION CODE", '||
179 ' MSII.RECEIVING_ROUTING_ID "RECEIVING ROUTING ID", '||
180 ' MSII.INVOICE_CLOSE_TOLERANCE "INVOICE CLOSE TOLERANCE", '||
181 ' MSII.RECEIVE_CLOSE_TOLERANCE "RECEIVE CLOSE TOLERANCE", '||
182 ' MSII.AUTO_LOT_ALPHA_PREFIX "AUTO LOT ALPHA PREFIX", '||
183 ' MSII.START_AUTO_LOT_NUMBER "START AUTO LOT NUMBER", '||
184 ' MSII.LOT_CONTROL_CODE "LOT CONTROL CODE", '||
185 ' MSII.SHELF_LIFE_CODE "SHELF LIFE CODE", '||
186 ' MSII.SHELF_LIFE_DAYS "SHELF LIFE DAYS", '||
187 ' MSII.SERIAL_NUMBER_CONTROL_CODE "SERIAL NUMBER CONTROL CODE", '||
188 ' MSII.START_AUTO_SERIAL_NUMBER "START AUTO SERIAL NUMBER", '||
189 ' MSII.AUTO_SERIAL_ALPHA_PREFIX "AUTO SERIAL ALPHA PREFIX", '||
190 ' MSII.SOURCE_TYPE "SOURCE TYPE", '||
191 ' MSII.SOURCE_ORGANIZATION_ID "SOURCE ORGANIZATION ID", '||
192 ' MSII.SOURCE_SUBINVENTORY "SOURCE SUBINVENTORY", '||
193 ' MSII.EXPENSE_ACCOUNT "EXPENSE ACCOUNT", '||
194 ' MSII.ENCUMBRANCE_ACCOUNT "ENCUMBRANCE ACCOUNT", '||
195 ' MSII.RESTRICT_SUBINVENTORIES_CODE "RESTRICT SUBINVENTORIES CODE", '||
196 ' MSII.UNIT_WEIGHT "UNIT WEIGHT", '||
197 ' MSII.WEIGHT_UOM_CODE "WEIGHT UOM CODE", '||
198 ' MSII.VOLUME_UOM_CODE "VOLUME UOM CODE", '||
199 ' MSII.UNIT_VOLUME "UNIT VOLUME" '||
200 ' from mtl_system_items_interface msii where 1=1 ';
201
202 sqltxt :=sqltxt||' and rownum < '||row_limit;
203 sqltxt :=sqltxt||' order by msii.inventory_item_id, msii.organization_id ';
204
205 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_system_items_interface table ');
206 If (num_rows = row_limit -1 ) Then
207 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/>');
208 End If;
209 statusStr := 'SUCCESS';
210 isFatal := 'FALSE';
211
212 sqltxt := 'SELECT ' ||
213 ' MSII.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
214 ' MSII.ORGANIZATION_ID "ORGANIZATION ID", '||
215 ' MSII.RESTRICT_LOCATORS_CODE "RESTRICT LOCATORS CODE", '||
216 ' MSII.LOCATION_CONTROL_CODE "LOCATION CONTROL CODE", '||
217 ' MSII.SHRINKAGE_RATE "SHRINKAGE RATE", '||
218 ' MSII.ACCEPTABLE_EARLY_DAYS "ACCEPTABLE EARLY DAYS", '||
219 ' MSII.PLANNING_TIME_FENCE_CODE "PLANNING TIME FENCE CODE", '||
220 ' MSII.DEMAND_TIME_FENCE_CODE "DEMAND TIME FENCE CODE", '||
221 ' MSII.LEAD_TIME_LOT_SIZE "LEAD TIME LOT SIZE", '||
222 ' MSII.STD_LOT_SIZE "STD LOT SIZE", '||
223 ' MSII.CUM_MANUFACTURING_LEAD_TIME "CUM MANUFACTURING LEAD TIME", '||
224 ' MSII.OVERRUN_PERCENTAGE "OVERRUN PERCENTAGE", '||
225 ' MSII.MRP_CALCULATE_ATP_FLAG "MRP CALCULATE ATP FLAG", '||
226 ' MSII.ACCEPTABLE_RATE_INCREASE "ACCEPTABLE RATE INCREASE", '||
227 ' MSII.ACCEPTABLE_RATE_DECREASE "ACCEPTABLE RATE DECREASE", '||
228 ' MSII.CUMULATIVE_TOTAL_LEAD_TIME "CUMULATIVE TOTAL LEAD TIME", '||
229 ' MSII.PLANNING_TIME_FENCE_DAYS "PLANNING TIME FENCE DAYS", '||
230 ' MSII.DEMAND_TIME_FENCE_DAYS "DEMAND TIME FENCE DAYS", '||
231 ' MSII.END_ASSEMBLY_PEGGING_FLAG "END ASSEMBLY PEGGING FLAG", '||
232 ' MSII.REPETITIVE_PLANNING_FLAG "REPETITIVE PLANNING FLAG", '||
233 ' MSII.PLANNING_EXCEPTION_SET "PLANNING EXCEPTION SET", '||
234 ' MSII.BOM_ITEM_TYPE "BOM ITEM TYPE", '||
235 ' MSII.PICK_COMPONENTS_FLAG "PICK COMPONENTS FLAG", '||
236 ' MSII.REPLENISH_TO_ORDER_FLAG "REPLENISH TO ORDER FLAG", '||
237 ' MSII.BASE_ITEM_ID "BASE ITEM ID", '||
238 ' MSII.ATP_COMPONENTS_FLAG "ATP COMPONENTS FLAG", '||
242 ' MSII.WIP_SUPPLY_LOCATOR_ID "WIP SUPPLY LOCATOR ID", '||
239 ' MSII.ATP_FLAG "ATP FLAG", '||
240 ' MSII.FIXED_LEAD_TIME "FIXED LEAD TIME", '||
241 ' MSII.VARIABLE_LEAD_TIME "VARIABLE LEAD TIME", '||
243 ' MSII.WIP_SUPPLY_TYPE "WIP SUPPLY TYPE", '||
244 ' MSII.WIP_SUPPLY_SUBINVENTORY "WIP SUPPLY SUBINVENTORY", '||
245 ' MSII.PRIMARY_UOM_CODE "PRIMARY UOM CODE", '||
246 ' MSII.PRIMARY_UNIT_OF_MEASURE "PRIMARY UNIT OF MEASURE", '||
247 ' MSII.ALLOWED_UNITS_LOOKUP_CODE "ALLOWED UNITS LOOKUP CODE", '||
248 ' MSII.COST_OF_SALES_ACCOUNT "COST OF SALES ACCOUNT", '||
249 ' MSII.SALES_ACCOUNT "SALES ACCOUNT", '||
250 ' MSII.DEFAULT_INCLUDE_IN_ROLLUP_FLAG "DEFAULT INCLUDE IN ROLLUP FLAG", '||
251 ' MSII.INVENTORY_ITEM_STATUS_CODE "INVENTORY ITEM STATUS CODE", '||
252 ' MSII.INVENTORY_PLANNING_CODE "INVENTORY PLANNING CODE", '||
253 ' MSII.PLANNER_CODE "PLANNER CODE", '||
254 ' MSII.PLANNING_MAKE_BUY_CODE "PLANNING MAKE BUY CODE", '||
255 ' MSII.FIXED_LOT_MULTIPLIER "FIXED LOT MULTIPLIER", '||
256 ' MSII.ROUNDING_CONTROL_TYPE "ROUNDING CONTROL TYPE", '||
257 ' MSII.CARRYING_COST "CARRYING COST", '||
258 ' MSII.POSTPROCESSING_LEAD_TIME "POSTPROCESSING LEAD TIME", '||
259 ' MSII.PREPROCESSING_LEAD_TIME "PREPROCESSING LEAD TIME", '||
260 ' MSII.FULL_LEAD_TIME "FULL LEAD TIME", '||
261 ' MSII.ORDER_COST "ORDER COST", '||
262 ' MSII.MRP_SAFETY_STOCK_PERCENT "MRP SAFETY STOCK PERCENT", '||
263 ' MSII.MRP_SAFETY_STOCK_CODE "MRP SAFETY STOCK CODE", '||
264 ' MSII.MIN_MINMAX_QUANTITY "MIN MINMAX QUANTITY", '||
265 ' MSII.MAX_MINMAX_QUANTITY "MAX MINMAX QUANTITY", '||
266 ' MSII.MINIMUM_ORDER_QUANTITY "MINIMUM ORDER QUANTITY", '||
267 ' MSII.FIXED_ORDER_QUANTITY "FIXED ORDER QUANTITY", '||
268 ' MSII.FIXED_DAYS_SUPPLY "FIXED DAYS SUPPLY", '||
269 ' MSII.MAXIMUM_ORDER_QUANTITY "MAXIMUM ORDER QUANTITY", '||
270 ' MSII.ATP_RULE_ID "ATP RULE ID", '||
271 ' MSII.PICKING_RULE_ID "PICKING RULE ID", '||
272 ' MSII.RESERVABLE_TYPE "RESERVABLE TYPE", '||
273 ' MSII.POSITIVE_MEASUREMENT_ERROR "POSITIVE MEASUREMENT ERROR", '||
274 ' MSII.NEGATIVE_MEASUREMENT_ERROR "NEGATIVE MEASUREMENT ERROR", '||
275 ' MSII.ENGINEERING_ECN_CODE "ENGINEERING ECN CODE", '||
276 ' MSII.ENGINEERING_ITEM_ID "ENGINEERING ITEM ID", '||
277 ' to_char(MSII.ENGINEERING_DATE,''DD-MON-YYYY HH24:MI:SS'') "ENGINEERING DATE", '||
278 ' MSII.SERVICE_STARTING_DELAY "SERVICE STARTING DELAY", '||
279 ' MSII.VENDOR_WARRANTY_FLAG "VENDOR WARRANTY FLAG", '||
280 ' MSII.SERVICEABLE_COMPONENT_FLAG "SERVICEABLE COMPONENT FLAG", '||
281 ' MSII.SERVICEABLE_PRODUCT_FLAG "SERVICEABLE PRODUCT FLAG", '||
282 ' MSII.BASE_WARRANTY_SERVICE_ID "BASE WARRANTY SERVICE ID", '||
283 ' MSII.PAYMENT_TERMS_ID "PAYMENT TERMS ID", '||
284 ' MSII.PREVENTIVE_MAINTENANCE_FLAG "PREVENTIVE MAINTENANCE FLAG", '||
285 ' MSII.PRIMARY_SPECIALIST_ID "PRIMARY SPECIALIST ID", '||
286 ' MSII.SECONDARY_SPECIALIST_ID "SECONDARY SPECIALIST ID", '||
287 ' MSII.SERVICEABLE_ITEM_CLASS_ID "SERVICEABLE ITEM CLASS ID", '||
288 ' MSII.TIME_BILLABLE_FLAG "TIME BILLABLE FLAG", '||
289 ' MSII.MATERIAL_BILLABLE_FLAG "MATERIAL BILLABLE FLAG", '||
290 ' MSII.EXPENSE_BILLABLE_FLAG "EXPENSE BILLABLE FLAG", '||
291 ' MSII.PRORATE_SERVICE_FLAG "PRORATE SERVICE FLAG", '||
292 ' MSII.COVERAGE_SCHEDULE_ID "COVERAGE SCHEDULE ID", '||
293 ' MSII.SERVICE_DURATION_PERIOD_CODE "SERVICE DURATION PERIOD CODE", '||
294 ' MSII.SERVICE_DURATION "SERVICE DURATION", '||
295 ' MSII.WARRANTY_VENDOR_ID "WARRANTY VENDOR ID", '||
296 ' MSII.MAX_WARRANTY_AMOUNT "MAX WARRANTY AMOUNT", '||
297 ' MSII.RESPONSE_TIME_PERIOD_CODE "RESPONSE TIME PERIOD CODE", '||
298 ' MSII.RESPONSE_TIME_VALUE "RESPONSE TIME VALUE", '||
299 ' MSII.NEW_REVISION_CODE "NEW REVISION CODE", '||
300 ' MSII.INVOICEABLE_ITEM_FLAG "INVOICEABLE ITEM FLAG", '||
301 ' MSII.TAX_CODE "TAX CODE", '||
302 ' MSII.INVOICE_ENABLED_FLAG "INVOICE ENABLED FLAG", '||
303 ' MSII.MUST_USE_APPROVED_VENDOR_FLAG "MUST USE APPROVED VENDOR FLAG", '||
304 ' MSII.REQUEST_ID "REQUEST ID", '||
305 ' MSII.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
306 ' MSII.PROGRAM_ID "PROGRAM ID", '||
307 ' to_char(MSII.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
308 ' MSII.OUTSIDE_OPERATION_FLAG "OUTSIDE OPERATION FLAG", '||
309 ' MSII.OUTSIDE_OPERATION_UOM_TYPE "OUTSIDE OPERATION UOM TYPE", '||
310 ' MSII.SAFETY_STOCK_BUCKET_DAYS "SAFETY STOCK BUCKET DAYS", '||
311 ' MSII.AUTO_REDUCE_MPS "AUTO REDUCE MPS", '||
312 ' MSII.COSTING_ENABLED_FLAG "COSTING ENABLED FLAG", '||
313 ' MSII.CYCLE_COUNT_ENABLED_FLAG "CYCLE COUNT ENABLED FLAG", '||
314 ' MSII.DEMAND_SOURCE_LINE "DEMAND SOURCE LINE", '||
315 ' MSII.COPY_ITEM_ID "COPY ITEM ID", '||
316 ' MSII.SET_ID "SET ID", '||
317 ' MSII.REVISION "REVISION", '||
318 ' MSII.AUTO_CREATED_CONFIG_FLAG "AUTO CREATED CONFIG FLAG", '||
319 ' MSII.ITEM_TYPE "ITEM TYPE", '||
320 ' MSII.MODEL_CONFIG_CLAUSE_NAME "MODEL CONFIG CLAUSE NAME", '||
321 ' MSII.SHIP_MODEL_COMPLETE_FLAG "SHIP MODEL COMPLETE FLAG", '||
322 ' MSII.MRP_PLANNING_CODE "MRP PLANNING CODE", '||
323 ' MSII.RETURN_INSPECTION_REQUIREMENT "RETURN INSPECTION REQUIREMENT", '||
327 ' MSII.PROCESS_FLAG "PROCESS FLAG", '||
324 ' MSII.DEMAND_SOURCE_TYPE "DEMAND SOURCE TYPE", '||
325 ' MSII.DEMAND_SOURCE_HEADER_ID "DEMAND SOURCE HEADER ID", '||
326 ' MSII.TRANSACTION_ID "TRANSACTION ID", '||
328 ' MSII.ORGANIZATION_CODE "ORGANIZATION CODE", '||
329 ' MSII.ITEM_NUMBER "ITEM NUMBER", '||
330 ' MSII.COPY_ITEM_NUMBER "COPY ITEM NUMBER", '||
331 ' MSII.TEMPLATE_ID "TEMPLATE ID", '||
332 ' MSII.TEMPLATE_NAME "TEMPLATE NAME", '||
333 ' MSII.COPY_ORGANIZATION_ID "COPY ORGANIZATION ID", '||
334 ' MSII.COPY_ORGANIZATION_CODE "COPY ORGANIZATION CODE" '||
335 ' from mtl_system_items_interface msii where 1=1 ';
336
337 sqltxt :=sqltxt||' and rownum < '||row_limit;
338 sqltxt :=sqltxt||' order by msii.inventory_item_id, msii.organization_id ';
339
340 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_system_items_interface table (Contd 1..)');
341 If (num_rows = row_limit -1 ) Then
342 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/>');
343 End If;
344
345 statusStr := 'SUCCESS';
346 isFatal := 'FALSE';
347
348 sqltxt := 'SELECT ' ||
349 ' MSII.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
350 ' MSII.ORGANIZATION_ID "ORGANIZATION ID", '||
351 ' MSII.ATO_FORECAST_CONTROL "ATO FORECAST CONTROL", '||
352 ' MSII.TRANSACTION_TYPE "TRANSACTION TYPE", '||
353 ' MSII.MATERIAL_COST "MATERIAL COST", '||
354 ' MSII.MATERIAL_SUB_ELEM "MATERIAL SUB ELEM", '||
355 ' MSII.MATERIAL_OH_RATE "MATERIAL OH RATE", '||
356 ' MSII.MATERIAL_OH_SUB_ELEM "MATERIAL OH SUB ELEM", '||
357 ' MSII.MATERIAL_SUB_ELEM_ID "MATERIAL SUB ELEM ID", '||
358 ' MSII.MATERIAL_OH_SUB_ELEM_ID "MATERIAL OH SUB ELEM ID", '||
359 ' MSII.RELEASE_TIME_FENCE_CODE "RELEASE TIME FENCE CODE", '||
360 ' MSII.RELEASE_TIME_FENCE_DAYS "RELEASE TIME FENCE DAYS", '||
361 ' MSII.CONTAINER_ITEM_FLAG "CONTAINER ITEM FLAG", '||
362 ' MSII.VEHICLE_ITEM_FLAG "VEHICLE ITEM FLAG", '||
363 ' MSII.MAXIMUM_LOAD_WEIGHT "MAXIMUM LOAD WEIGHT", '||
364 ' MSII.MINIMUM_FILL_PERCENT "MINIMUM FILL PERCENT", '||
365 ' MSII.CONTAINER_TYPE_CODE "CONTAINER TYPE CODE", '||
366 ' MSII.INTERNAL_VOLUME "INTERNAL VOLUME", '||
367 ' MSII.SET_PROCESS_ID "SET PROCESS ID", '||
368 ' to_char(MSII.WH_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "WH UPDATE DATE", '||
369 ' MSII.PRODUCT_FAMILY_ITEM_ID "PRODUCT FAMILY ITEM ID", '||
370 ' MSII.PURCHASING_TAX_CODE "PURCHASING TAX CODE", '||
371 ' MSII.OVERCOMPLETION_TOLERANCE_TYPE "OVERCOMPLETION TOLERANCE TYPE", '||
372 ' MSII.OVERCOMPLETION_TOLERANCE_VALUE "OVERCOMPLETION TOLERANCE VALUE", '||
373 ' MSII.EFFECTIVITY_CONTROL "EFFECTIVITY CONTROL", '||
374 ' MSII.GLOBAL_ATTRIBUTE_CATEGORY "GLOBAL ATTRIBUTE CATEGORY", '||
375 ' MSII.GLOBAL_ATTRIBUTE1 "GLOBAL ATTRIBUTE1", '||
376 ' MSII.GLOBAL_ATTRIBUTE2 "GLOBAL ATTRIBUTE2", '||
377 ' MSII.GLOBAL_ATTRIBUTE3 "GLOBAL ATTRIBUTE3", '||
378 ' MSII.GLOBAL_ATTRIBUTE4 "GLOBAL ATTRIBUTE4", '||
379 ' MSII.GLOBAL_ATTRIBUTE5 "GLOBAL ATTRIBUTE5", '||
380 ' MSII.GLOBAL_ATTRIBUTE6 "GLOBAL ATTRIBUTE6", '||
381 ' MSII.GLOBAL_ATTRIBUTE7 "GLOBAL ATTRIBUTE7", '||
382 ' MSII.GLOBAL_ATTRIBUTE8 "GLOBAL ATTRIBUTE8", '||
383 ' MSII.GLOBAL_ATTRIBUTE9 "GLOBAL ATTRIBUTE9", '||
384 ' MSII.GLOBAL_ATTRIBUTE10 "GLOBAL ATTRIBUTE10", '||
385 ' MSII.OVER_SHIPMENT_TOLERANCE "OVER SHIPMENT TOLERANCE", '||
386 ' MSII.UNDER_SHIPMENT_TOLERANCE "UNDER SHIPMENT TOLERANCE", '||
387 ' MSII.OVER_RETURN_TOLERANCE "OVER RETURN TOLERANCE", '||
388 ' MSII.UNDER_RETURN_TOLERANCE "UNDER RETURN TOLERANCE", '||
389 ' MSII.EQUIPMENT_TYPE "EQUIPMENT TYPE", '||
390 ' MSII.RECOVERED_PART_DISP_CODE "RECOVERED PART DISP CODE", '||
391 ' MSII.DEFECT_TRACKING_ON_FLAG "DEFECT TRACKING ON FLAG", '||
392 ' MSII.USAGE_ITEM_FLAG "USAGE ITEM FLAG", '||
393 ' MSII.EVENT_FLAG "EVENT FLAG", '||
394 ' MSII.ELECTRONIC_FLAG "ELECTRONIC FLAG", '||
395 ' MSII.DOWNLOADABLE_FLAG "DOWNLOADABLE FLAG", '||
396 ' MSII.VOL_DISCOUNT_EXEMPT_FLAG "VOL DISCOUNT EXEMPT FLAG", '||
397 ' MSII.COUPON_EXEMPT_FLAG "COUPON EXEMPT FLAG", '||
398 ' MSII.COMMS_NL_TRACKABLE_FLAG "COMMS NL TRACKABLE FLAG", '||
399 ' MSII.ASSET_CREATION_CODE "ASSET CREATION CODE", '||
400 ' MSII.COMMS_ACTIVATION_REQD_FLAG "COMMS ACTIVATION REQD FLAG", '||
401 ' MSII.ORDERABLE_ON_WEB_FLAG "ORDERABLE ON WEB FLAG", '||
402 ' MSII.BACK_ORDERABLE_FLAG "BACK ORDERABLE FLAG", '||
403 ' MSII.WEB_STATUS "WEB STATUS", '||
404 ' MSII.INDIVISIBLE_FLAG "INDIVISIBLE FLAG", '||
405 ' MSII.LONG_DESCRIPTION "LONG DESCRIPTION", '||
406 ' MSII.DIMENSION_UOM_CODE "DIMENSION UOM CODE", '||
407 ' MSII.UNIT_LENGTH "UNIT LENGTH", '||
408 ' MSII.UNIT_WIDTH "UNIT WIDTH", '||
409 ' MSII.UNIT_HEIGHT "UNIT HEIGHT", '||
410 ' MSII.BULK_PICKED_FLAG "BULK PICKED FLAG", '||
411 ' MSII.LOT_STATUS_ENABLED "LOT STATUS ENABLED", '||
412 ' MSII.DEFAULT_LOT_STATUS_ID "DEFAULT LOT STATUS ID", '||
413 ' MSII.SERIAL_STATUS_ENABLED "SERIAL STATUS ENABLED", '||
414 ' MSII.DEFAULT_SERIAL_STATUS_ID "DEFAULT SERIAL STATUS ID", '||
418 ' MSII.OPERATION_SLACK_PENALTY "OPERATION SLACK PENALTY", '||
415 ' MSII.LOT_SPLIT_ENABLED "LOT SPLIT ENABLED", '||
416 ' MSII.LOT_MERGE_ENABLED "LOT MERGE ENABLED", '||
417 ' MSII.INVENTORY_CARRY_PENALTY "INVENTORY CARRY PENALTY", '||
419 ' MSII.FINANCING_ALLOWED_FLAG "FINANCING ALLOWED FLAG", '||
420 ' MSII.EAM_ITEM_TYPE "EAM ITEM TYPE", '||
421 ' MSII.EAM_ACTIVITY_TYPE_CODE "EAM ACTIVITY TYPE CODE", '||
422 ' MSII.EAM_ACTIVITY_CAUSE_CODE "EAM ACTIVITY CAUSE CODE", '||
423 ' MSII.EAM_ACT_NOTIFICATION_FLAG "EAM ACT NOTIFICATION FLAG", '||
424 ' MSII.EAM_ACT_SHUTDOWN_STATUS "EAM ACT SHUTDOWN STATUS", '||
425 ' MSII.DUAL_UOM_CONTROL "DUAL UOM CONTROL", '||
426 ' MSII.SECONDARY_UOM_CODE "SECONDARY UOM CODE", '||
427 ' MSII.DUAL_UOM_DEVIATION_HIGH "DUAL UOM DEVIATION HIGH", '||
428 ' MSII.DUAL_UOM_DEVIATION_LOW "DUAL UOM DEVIATION LOW", '||
429 ' MSII.CONTRACT_ITEM_TYPE_CODE "CONTRACT ITEM TYPE CODE", '||
430 ' MSII.SUBSCRIPTION_DEPEND_FLAG "SUBSCRIPTION DEPEND FLAG", '||
431 ' MSII.SERV_REQ_ENABLED_CODE "SERV REQ ENABLED CODE", '||
432 ' MSII.SERV_BILLING_ENABLED_FLAG "SERV BILLING ENABLED FLAG", '||
433 ' MSII.SERV_IMPORTANCE_LEVEL "SERV IMPORTANCE LEVEL", '||
434 ' MSII.PLANNED_INV_POINT_FLAG "PLANNED INV POINT FLAG", '||
435 ' MSII.LOT_TRANSLATE_ENABLED "LOT TRANSLATE ENABLED", '||
436 ' MSII.DEFAULT_SO_SOURCE_TYPE "DEFAULT SO SOURCE TYPE", '||
437 ' MSII.CREATE_SUPPLY_FLAG "CREATE SUPPLY FLAG", '||
438 ' MSII.SUBSTITUTION_WINDOW_CODE "SUBSTITUTION WINDOW CODE", '||
439 ' MSII.SUBSTITUTION_WINDOW_DAYS "SUBSTITUTION WINDOW DAYS", '||
440 ' MSII.IB_ITEM_INSTANCE_CLASS "IB ITEM INSTANCE CLASS", '||
441 ' MSII.CONFIG_MODEL_TYPE "CONFIG MODEL TYPE", '||
442 ' MSII.LOT_SUBSTITUTION_ENABLED "LOT SUBSTITUTION ENABLED", '||
443 ' MSII.MINIMUM_LICENSE_QUANTITY "MINIMUM LICENSE QUANTITY", '||
444 ' MSII.EAM_ACTIVITY_SOURCE_CODE "EAM ACTIVITY SOURCE CODE", '||
445 ' MSII.LIFECYCLE_ID "LIFECYCLE ID", '||
446 ' MSII.CURRENT_PHASE_ID "CURRENT PHASE ID" '||
447 ' ,MSII.TRACKING_QUANTITY_IND "TRACKING QUANTITY IND" '||
448 ' ,MSII.ONT_PRICING_QTY_SOURCE "ONT PRICING QTY SOURCE" '||
449 ' ,MSII.SECONDARY_DEFAULT_IND "SECONDARY DEFAULT IND" '||
450 ' ,MSII.VMI_MINIMUM_UNITS "VMI MINIMUM UNITS" '||
451 ' ,MSII.VMI_MINIMUM_DAYS "VMI MINIMUM DAYS" '||
452 ' ,MSII.VMI_MAXIMUM_UNITS "VMI MAXIMUM UNITS" '||
453 ' ,MSII.VMI_MAXIMUM_DAYS "VMI MAXIMUM DAYS" '||
454 ' ,MSII.VMI_FIXED_ORDER_QUANTITY "VMI FIXED ORDER QUANTITY" '||
455 ' ,MSII.SO_AUTHORIZATION_FLAG "SO AUTHORIZATION FLAG" '||
456 ' ,MSII.CONSIGNED_FLAG "CONSIGNED FLAG" '||
457 ' ,MSII.ASN_AUTOEXPIRE_FLAG "ASN AUTOEXPIRE FLAG" '||
458 ' ,MSII.VMI_FORECAST_TYPE "VMI FORECAST TYPE" '||
459 ' ,MSII.FORECAST_HORIZON "FORECAST HORIZON" '||
460 ' ,MSII.EXCLUDE_FROM_BUDGET_FLAG "EXCLUDE FROM BUDGET FLAG" '||
461 ' ,MSII.DAYS_TGT_INV_SUPPLY "DAYS TGT INV SUPPLY" '||
462 ' ,MSII.DAYS_TGT_INV_WINDOW "DAYS TGT INV WINDOW" '||
463 ' ,MSII.DAYS_MAX_INV_SUPPLY "DAYS MAX INV SUPPLY" '||
464 ' ,MSII.DAYS_MAX_INV_WINDOW "DAYS MAX INV WINDOW" '||
465 ' ,MSII.DRP_PLANNED_FLAG "DRP PLANNED FLAG" '||
466 ' ,MSII.CRITICAL_COMPONENT_FLAG "CRITICAL COMPONENT FLAG" '||
467 ' ,MSII.CONTINOUS_TRANSFER "CONTINOUS TRANSFER" '||
468 ' ,MSII.CONVERGENCE "CONVERGENCE" '||
469 ' ,MSII.DIVERGENCE "DIVERGENCE" '||
470 ' ,MSII.CONFIG_ORGS "CONFIG ORGS" '||
471 ' ,MSII.CONFIG_MATCH "CONFIG MATCH" '||
472 ' from mtl_system_items_interface msii where 1=1 ';
473
474 sqltxt :=sqltxt||' and rownum < '||row_limit;
475 sqltxt :=sqltxt||' order by msii.inventory_item_id, msii.organization_id ';
476
477 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_system_items_interface table (Contd 2..) ');
478 If (num_rows = row_limit -1 ) Then
479 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/>');
480 End If;
481 statusStr := 'SUCCESS';
482 isFatal := 'FALSE';
483
484 sqltxt := 'SELECT ' ||
485 ' MSII.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
486 ' MSII.ORGANIZATION_ID "ORGANIZATION ID", '||
487 ' MSII.ATTRIBUTE16 "ATTRIBUTE16", '||
488 ' MSII.ATTRIBUTE17 "ATTRIBUTE17", '||
489 ' MSII.ATTRIBUTE18 "ATTRIBUTE18", '||
490 ' MSII.ATTRIBUTE19 "ATTRIBUTE19", '||
491 ' MSII.ATTRIBUTE20 "ATTRIBUTE20", '||
492 ' MSII.ATTRIBUTE21 "ATTRIBUTE21", '||
493 ' MSII.ATTRIBUTE22 "ATTRIBUTE22", '||
494 ' MSII.ATTRIBUTE23 "ATTRIBUTE23", '||
495 ' MSII.ATTRIBUTE24 "ATTRIBUTE24", '||
496 ' MSII.ATTRIBUTE25 "ATTRIBUTE25", '||
497 ' MSII.ATTRIBUTE26 "ATTRIBUTE26", '||
498 ' MSII.ATTRIBUTE27 "ATTRIBUTE27", '||
499 ' MSII.ATTRIBUTE28 "ATTRIBUTE28", '||
500 ' MSII.ATTRIBUTE29 "ATTRIBUTE29", '||
501 ' MSII.ATTRIBUTE30 "ATTRIBUTE30", '||
502 ' MSII.CAS_NUMBER "CAS NUMBER", '||
503 ' MSII.CHILD_LOT_FLAG "CHILD LOT FLAG", '||
504 ' MSII.CHILD_LOT_PREFIX "CHILD LOT PREFIX", '||
505 ' MSII.CHILD_LOT_STARTING_NUMBER "CHILD LOT STARTING NUMBER", '||
506 ' MSII.CHILD_LOT_VALIDATION_FLAG "CHILD LOT VALIDATION FLAG", '||
507 ' MSII.COPY_LOT_ATTRIBUTE_FLAG "COPY LOT ATTRIBUTE FLAG", '||
511 ' MSII.GRADE_CONTROL_FLAG "GRADE CONTROL FLAG", '||
508 ' MSII.DEFAULT_GRADE "DEFAULT GRADE", '||
509 ' MSII.EXPIRATION_ACTION_CODE "EXPIRATION ACTION CODE", '||
510 ' MSII.EXPIRATION_ACTION_INTERVAL "EXPIRATION ACTION INTERVAL", '||
512 ' MSII.HAZARDOUS_MATERIAL_FLAG "HAZARDOUS MATERIAL FLAG", '||
513 ' MSII.HOLD_DAYS "HOLD DAYS", '||
514 ' MSII.LOT_DIVISIBLE_FLAG "LOT DIVISIBLE FLAG", '||
515 ' MSII.MATURITY_DAYS "MATURITY DAYS", '||
516 ' MSII.PARENT_CHILD_GENERATION_FLAG "PARENT CHILD GENERATION FLAG", '||
517 ' MSII.PROCESS_COSTING_ENABLED_FLAG "PROCESS COSTING ENABLED FLAG", '||
518 ' MSII.PROCESS_EXECUTION_ENABLED_FLAG"PROCESS EXECUTION ENABLED FLAG", '||
519 ' MSII.PROCESS_QUALITY_ENABLED_FLAG "PROCESS_QUALITY_ENABLED FLAG", '||
520 ' MSII.PROCESS_SUPPLY_LOCATOR_ID "PROCESS SUPPLY LOCATOR ID", '||
521 ' MSII.PROCESS_SUPPLY_SUBINVENTORY "PROCESS SUPPLY SUBINVENTORY", '||
522 ' MSII.PROCESS_YIELD_LOCATOR_ID "PROCESS YIELD LOCATOR ID", '||
523 ' MSII.PROCESS_YIELD_SUBINVENTORY "PROCESS YIELD SUBINVENTORY", '||
524 ' MSII.RECIPE_ENABLED_FLAG "RECIPE ENABLED FLAG", '||
525 ' MSII.RETEST_INTERVAL "RETEST INTERVAL", '||
526 ' MSII.CHARGE_PERIODICITY_CODE "CHARGE PERIODICITY CODE", '||
527 ' MSII.REPAIR_LEADTIME "REPAIR LEADTIME", '||
528 ' MSII.REPAIR_YIELD "REPAIR YIELD", '||
529 ' MSII.PREPOSITION_POINT "PREPOSITION POINT", '||
530 ' MSII.REPAIR_PROGRAM "REPAIR PROGRAM", '||
531 ' MSII.SUBCONTRACTING_COMPONENT "SUBCONTRACTING COMPONENT", '||
532 ' MSII.OUTSOURCED_ASSEMBLY "OUTSOURCED ASSEMBLY", '||
533 ' MSII.SOURCE_SYSTEM_ID "SOURCE SYSTEM ID", '||
534 ' MSII.SOURCE_SYSTEM_REFERENCE "SOURCE SYSTEM REFERENCE", '||
535 ' MSII.SOURCE_SYSTEM_REFERENCE_DESC "SOURCE SYSTEM REFERENCE DESC", '||
536 ' MSII.GLOBAL_TRADE_ITEM_NUMBER "GLOBAL TRADE ITEM NUMBER", '||
537 ' MSII.CONFIRM_STATUS "CONFIRM STATUS", '||
538 ' MSII.CHANGE_ID "CHANGE ID", '||
539 ' MSII.CHANGE_LINE_ID "CHANGE LINE ID", '||
540 ' MSII.ITEM_CATALOG_GROUP_NAME "ITEM CATALOG GROUP NAME", '||
541 ' MSII.REVISION_IMPORT_POLICY "REVISION IMPORT POLICY", '||
542 ' MSII.GTIN_DESCRIPTION "GTIN DESCRIPTION", '||
543 ' MSII.INTERFACE_TABLE_UNIQUE_ID "INTERFACE TABLE UNIQUE ID" '||
544 ' from mtl_system_items_interface msii where 1=1 ';
545
546 sqltxt :=sqltxt||' and rownum < '||row_limit;
547 sqltxt :=sqltxt||' order by msii.inventory_item_id, msii.organization_id ';
548
549 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_system_items_interface table (Contd 3..) ');
550 If (num_rows = row_limit -1 ) Then
551 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/>');
552 End If;
553 statusStr := 'SUCCESS';
554 isFatal := 'FALSE';
555
556 /* End of mtl_system_items_interface */
557
558 /* SQL to fetch records from mtl_item_revisions_interface */
559 sqltxt := 'SELECT ' ||
560 ' MIRI.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
561 ' MIRI.ORGANIZATION_ID "ORGANIZATION ID", '||
562 ' MIRI.REVISION "REVISION", '||
563 ' to_char(MIRI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
564 ' MIRI.LAST_UPDATED_BY "LAST UPDATED BY", '||
565 ' to_char(MIRI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
566 ' MIRI.CREATED_BY "CREATED BY", '||
567 ' MIRI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
568 ' MIRI.CHANGE_NOTICE "CHANGE NOTICE", '||
569 ' to_char(MIRI.ECN_INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "ECN INITIATION DATE",'||
570 ' to_char(MIRI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE",'||
571 ' MIRI.IMPLEMENTED_SERIAL_NUMBER "IMPLEMENTED SERIAL NUMBER", '||
572 ' to_char(MIRI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
573 ' MIRI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
574 ' MIRI.ATTRIBUTE1 "ATTRIBUTE1", '||
575 ' MIRI.ATTRIBUTE2 "ATTRIBUTE2", '||
576 ' MIRI.ATTRIBUTE3 "ATTRIBUTE3", '||
577 ' MIRI.ATTRIBUTE4 "ATTRIBUTE4", '||
578 ' MIRI.ATTRIBUTE5 "ATTRIBUTE5", '||
579 ' MIRI.ATTRIBUTE6 "ATTRIBUTE6", '||
580 ' MIRI.ATTRIBUTE7 "ATTRIBUTE7", '||
581 ' MIRI.ATTRIBUTE8 "ATTRIBUTE8", '||
582 ' MIRI.ATTRIBUTE9 "ATTRIBUTE9", '||
583 ' MIRI.ATTRIBUTE10 "ATTRIBUTE10", '||
584 ' MIRI.ATTRIBUTE11 "ATTRIBUTE11", '||
585 ' MIRI.ATTRIBUTE12 "ATTRIBUTE12", '||
586 ' MIRI.ATTRIBUTE13 "ATTRIBUTE13", '||
587 ' MIRI.ATTRIBUTE14 "ATTRIBUTE14", '||
588 ' MIRI.ATTRIBUTE15 "ATTRIBUTE15", '||
589 ' MIRI.REQUEST_ID "REQUEST ID", '||
590 ' MIRI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
591 ' MIRI.PROGRAM_ID "PROGRAM ID", '||
592 ' to_char(MIRI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
593 ' MIRI.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID", '||
594 ' MIRI.DESCRIPTION "DESCRIPTION", '||
595 ' MIRI.ITEM_NUMBER "ITEM NUMBER", '||
596 ' MIRI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
597 ' MIRI.TRANSACTION_ID "TRANSACTION ID", '||
598 ' MIRI.PROCESS_FLAG "PROCESS FLAG", '||
599 ' MIRI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
600 ' MIRI.SET_PROCESS_ID "SET PROCESS ID", '||
601 ' MIRI.REVISION_ID "REVISION ID", '||
602 ' MIRI.REVISION_LABEL "REVISION LABEL", '||
603 ' MIRI.REVISION_REASON "REVISION REASON", '||
607 ' MIRI.SOURCE_SYSTEM_REFERENCE "SOURCE SYSTEM REFERENCE" , '||
604 ' MIRI.LIFECYCLE_ID "LIFECYCLE ID", '||
605 ' MIRI.CURRENT_PHASE_ID "CURRENT PHASE ID", '||
606 ' MIRI.SOURCE_SYSTEM_ID "SOURCE SYSTEM ID", '||
608 ' MIRI.CHANGE_ID "CHANGE ID", '||
609 ' MIRI.INTERFACE_TABLE_UNIQUE_ID "INTERFACE TABLE UNIQUE ID", '||
610 ' MIRI.TEMPLATE_ID "TEMPLATE ID", '||
611 ' MIRI.TEMPLATE_NAME "TEMPLATE NAME" '||
612 ' from mtl_item_revisions_interface miri where 1=1 ';
613
614 sqltxt :=sqltxt||' and rownum < '||row_limit;
615 sqltxt :=sqltxt||' order by miri.inventory_item_id, miri.organization_id, miri.revision ';
616
617 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_item_revisions_interface table ');
618 If (num_rows = row_limit -1 ) Then
619 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/>');
620 End If;
621 statusStr := 'SUCCESS';
622 isFatal := 'FALSE';
623
624 /* End of mtl_item_revisions_interface */
625
626
627 /* SQL to fetch records from mtl_rtg_item_revs_interface */
628
629 sqltxt := 'SELECT ' ||
630 ' MRIRI.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
631 ' MRIRI.ORGANIZATION_ID "ORGANIZATION ID", '||
632 ' MRIRI.PROCESS_REVISION "PROCESS REVISION", '||
633 ' to_char(MRIRI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
634 ' MRIRI.LAST_UPDATED_BY "LAST UPDATED BY", '||
635 ' to_char(MRIRI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
636 ' MRIRI.CREATED_BY "CREATED BY", '||
637 ' MRIRI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
638 ' MRIRI.CHANGE_NOTICE "CHANGE NOTICE", '||
639 ' to_char(MRIRI.ECN_INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "ECN INITIATION DATE",'||
640 ' to_char(MRIRI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE",'||
641 ' MRIRI.IMPLEMENTED_SERIAL_NUMBER "IMPLEMENTED SERIAL NUMBER", '||
642 ' to_char(MRIRI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
643 ' MRIRI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
644 ' MRIRI.ATTRIBUTE1 "ATTRIBUTE1", '||
645 ' MRIRI.ATTRIBUTE2 "ATTRIBUTE2", '||
646 ' MRIRI.ATTRIBUTE3 "ATTRIBUTE3", '||
647 ' MRIRI.ATTRIBUTE4 "ATTRIBUTE4", '||
648 ' MRIRI.ATTRIBUTE5 "ATTRIBUTE5", '||
649 ' MRIRI.ATTRIBUTE6 "ATTRIBUTE6", '||
650 ' MRIRI.ATTRIBUTE7 "ATTRIBUTE7", '||
651 ' MRIRI.ATTRIBUTE8 "ATTRIBUTE8", '||
652 ' MRIRI.ATTRIBUTE9 "ATTRIBUTE9", '||
653 ' MRIRI.ATTRIBUTE10 "ATTRIBUTE10", '||
654 ' MRIRI.ATTRIBUTE11 "ATTRIBUTE11", '||
655 ' MRIRI.ATTRIBUTE12 "ATTRIBUTE12", '||
656 ' MRIRI.ATTRIBUTE13 "ATTRIBUTE13", '||
657 ' MRIRI.ATTRIBUTE14 "ATTRIBUTE14", '||
658 ' MRIRI.ATTRIBUTE15 "ATTRIBUTE15", '||
659 ' MRIRI.REQUEST_ID "REQUEST ID", '||
660 ' MRIRI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
661 ' MRIRI.PROGRAM_ID "PROGRAM ID", '||
662 ' to_char(MRIRI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
663 ' MRIRI.INVENTORY_ITEM_NUMBER "INVENTORY ITEM NUMBER", '||
664 ' MRIRI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
665 ' MRIRI.TRANSACTION_ID "TRANSACTION ID", '||
666 ' MRIRI.PROCESS_FLAG "PROCESS FLAG", '||
667 ' MRIRI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
668 ' MRIRI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
669 ' from mtl_rtg_item_revs_interface mriri where 1=1 ';
670
671 sqltxt :=sqltxt||' and rownum < '||row_limit;
672 sqltxt :=sqltxt||' order by mriri.inventory_item_id, mriri.organization_id, mriri.process_revision ';
673
674 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_rtg_item_revs_interface table ');
675 If (num_rows = row_limit -1 ) Then
676 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/>');
677 End If;
678 statusStr := 'SUCCESS';
679 isFatal := 'FALSE';
680
681 /* End of mtl_rtg_item_revs_interface */
682
683
684 /* SQL to fetch records from mtl_item_categories_interface */
685 sqltxt := 'SELECT ' ||
686 ' MICI.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
687 ' MICI.CATEGORY_SET_ID "CATEGORY SET ID", '||
688 ' MICI.CATEGORY_ID "CATEGORY ID", '||
689 ' to_char(MICI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE",'||
690 ' MICI.LAST_UPDATED_BY "LAST UPDATED BY", '||
691 ' to_char(MICI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
692 ' MICI.CREATED_BY "CREATED BY", '||
693 ' MICI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
694 ' MICI.REQUEST_ID "REQUEST ID", '||
695 ' MICI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
696 ' MICI.PROGRAM_ID "PROGRAM ID", '||
697 ' to_char(MICI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
698 ' MICI.ORGANIZATION_ID "ORGANIZATION ID", '||
699 ' MICI.TRANSACTION_ID "TRANSACTION ID", '||
700 ' MICI.PROCESS_FLAG "PROCESS FLAG", '||
701 ' MICI.CATEGORY_SET_NAME "CATEGORY SET NAME", '||
702 ' MICI.CATEGORY_NAME "CATEGORY NAME", '||
703 ' MICI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
704 ' MICI.ITEM_NUMBER "ITEM NUMBER", '||
705 ' MICI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
709 ' MICI.SOURCE_SYSTEM_ID "SOURCE SYSTEM ID", '||
706 ' MICI.SET_PROCESS_ID "SET PROCESS ID", '||
707 ' MICI.OLD_CATEGORY_ID "OLD CATEGORY ID", '||
708 ' MICI.OLD_CATEGORY_NAME "OLD CATEGORY NAME", '||
710 ' MICI.SOURCE_SYSTEM_REFERENCE "SOURCE SYSTEM REFERENCE", '||
711 ' MICI.CHANGE_ID "CHANGE ID", '||
712 ' MICI.CHANGE_LINE_ID "CHANGE LINE ID" '||
713 ' from mtl_item_categories_interface mici where 1=1 ';
714
715 sqltxt :=sqltxt||' and rownum < '||row_limit;
716 sqltxt :=sqltxt||' order by mici.inventory_item_id, mici.organization_id, '||
717 ' mici.category_set_id, mici.category_id ';
718
719 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_item_categories_interface table ');
720 If (num_rows = row_limit -1 ) Then
721 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/>');
722 End If;
723 statusStr := 'SUCCESS';
724 isFatal := 'FALSE';
725 /* End of mtl_item_categories_interface */
726 /* End of l_type = items */
727
728 Elsif upper(l_type) in ('BOM','RTG','ENG') Then
729
730 If upper(l_type) = 'BOM' Then
731 /* Fetch data from tables exclusive to bom */
732
733 /* Get the application installation info. References to Data Dictionary Objects without schema name
734 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
735 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
736
737 l_ret_status := fnd_installation.get_app_info ('BOM'
738 , l_status
739 , l_industry
740 , l_oracle_schema
741 );
742
743 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
744
745 /* SQL to fetch from bom_bill_of_mtls_interface table */
746 sqltxt := 'SELECT ' ||
747 ' BBMI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
748 ' BBMI.ORGANIZATION_ID "ORGANIZATION ID", '||
749 ' BBMI.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR", '||
750 ' to_char(BBMI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
751 ' BBMI.LAST_UPDATED_BY "LAST UPDATED BY", '||
752 ' to_char(BBMI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
753 ' BBMI.CREATED_BY "CREATED BY", '||
754 ' BBMI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
755 ' BBMI.COMMON_ASSEMBLY_ITEM_ID "COMMON ASSEMBLY ITEM ID", '||
756 ' BBMI.SPECIFIC_ASSEMBLY_COMMENT "SPECIFIC ASSEMBLY COMMENT", '||
757 ' BBMI.PENDING_FROM_ECN "PENDING FROM ECN", '||
758 ' BBMI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
759 ' BBMI.ATTRIBUTE1 "ATTRIBUTE1", '||
760 ' BBMI.ATTRIBUTE2 "ATTRIBUTE2", '||
761 ' BBMI.ATTRIBUTE3 "ATTRIBUTE3", '||
762 ' BBMI.ATTRIBUTE4 "ATTRIBUTE4", '||
763 ' BBMI.ATTRIBUTE5 "ATTRIBUTE5", '||
764 ' BBMI.ATTRIBUTE6 "ATTRIBUTE6", '||
765 ' BBMI.ATTRIBUTE7 "ATTRIBUTE7", '||
766 ' BBMI.ATTRIBUTE8 "ATTRIBUTE8", '||
767 ' BBMI.ATTRIBUTE9 "ATTRIBUTE9", '||
768 ' BBMI.ATTRIBUTE10 "ATTRIBUTE10", '||
769 ' BBMI.ATTRIBUTE11 "ATTRIBUTE11", '||
770 ' BBMI.ATTRIBUTE12 "ATTRIBUTE12", '||
771 ' BBMI.ATTRIBUTE13 "ATTRIBUTE13", '||
772 ' BBMI.ATTRIBUTE14 "ATTRIBUTE14", '||
773 ' BBMI.ATTRIBUTE15 "ATTRIBUTE15", '||
774 ' BBMI.ASSEMBLY_TYPE "ASSEMBLY TYPE", '||
775 ' BBMI.COMMON_BILL_SEQUENCE_ID "COMMON BILL SEQUENCE ID", '||
776 ' BBMI.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
777 ' BBMI.REQUEST_ID "REQUEST ID", '||
778 ' BBMI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
779 ' BBMI.PROGRAM_ID "PROGRAM ID", '||
780 ' to_char(BBMI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
781 ' BBMI.DEMAND_SOURCE_LINE "DEMAND SOURCE LINE", '||
782 ' BBMI.SET_ID "SET ID", '||
783 ' BBMI.COMMON_ORGANIZATION_ID "COMMON ORGANIZATION ID", '||
784 ' BBMI.DEMAND_SOURCE_TYPE "DEMAND SOURCE TYPE", '||
785 ' BBMI.DEMAND_SOURCE_HEADER_ID "DEMAND SOURCE HEADER ID", '||
786 ' BBMI.TRANSACTION_ID "TRANSACTION ID", '||
787 ' BBMI.PROCESS_FLAG "PROCESS FLAG", '||
788 ' BBMI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
789 ' BBMI.COMMON_ORG_CODE "COMMON ORG CODE", '||
790 ' BBMI.ITEM_NUMBER "ITEM NUMBER", '||
791 ' BBMI.COMMON_ITEM_NUMBER "COMMON ITEM NUMBER", '||
792 ' to_char(BBMI.NEXT_EXPLODE_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEXT EXPLODE DATE", '||
793 ' BBMI.REVISION "REVISION", '||
794 ' BBMI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
795 ' BBMI.DELETE_GROUP_NAME "DELETE GROUP NAME", '||
796 ' BBMI.DG_DESCRIPTION "DG DESCRIPTION", '||
797 ' BBMI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
798 ' ,to_char(BBMI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE " '||
799 ' ,BBMI.OBJ_NAME "OBJ NAME" '||
800 ' ,BBMI.PK1_VALUE "PK1 VALUE" '||
801 ' ,BBMI.PK2_VALUE "PK2 VALUE" '||
802 ' ,BBMI.PK3_VALUE "PK3 VALUE" '||
803 ' ,BBMI.PK4_VALUE "PK4 VALUE" '||
804 ' ,BBMI.PK5_VALUE "PK5 VALUE" '||
805 ' ,BBMI.STRUCTURE_TYPE_NAME "STRUCTURE_TYPE_NAME" '||
806 ' ,BBMI.STRUCTURE_TYPE_ID "STRUCTURE_TYPE_ID" '||
807 ' ,BBMI.EFFECTIVITY_CONTROL "EFFECTIVITY CONTROL" '||
808 ' ,BBMI.RETURN_STATUS "RETURN STATUS" '||
809 ' ,BBMI.IS_PREFERRED "IS PREFERRED" '||
810 ' ,BBMI.SOURCE_SYSTEM_REFERENCE "SOURCE SYSTEM REFERENCE" '||
811 ' ,BBMI.SOURCE_SYSTEM_REFERENCE_DESC"SOURCE SYSTEM REFERENCE DESC" '||
812 ' ,BBMI.BATCH_ID "BATCH ID" '||
816 ' ,BBMI.PRIMARY_UNIT_OF_MEASURE "PRIMARY UNIT OF MEASURE" '||
813 ' ,BBMI.CHANGE_ID "CHANGE ID" '||
814 ' ,BBMI.CATALOG_CATEGORY_NAME "CATALOG CATEGORY NAME" '||
815 ' ,BBMI.ITEM_CATALOG_GROUP_ID "ITEM CATALOG GROUP ID" '||
817 ' ,BBMI.ITEM_DESCRIPTION "ITEM DESCRIPTION" '||
818 ' ,BBMI.TEMPLATE_NAME "TEMPLATE NAME" '||
819 ' ,BBMI.SOURCE_BILL_SEQUENCE_ID "SOURCE BILL SEQUENCE ID" '||
820 ' ,BBMI.ENABLE_ATTRS_UPDATE "ENABLE ATTRS UPDATE" '||
821 ' ,BBMI.INTERFACE_TABLE_UNIQUE_ID "INTERFACE TABLE UNIQUE ID" '||
822 ' from bom_bill_of_mtls_interface bbmi where 1=1 ';
823
824 sqltxt :=sqltxt||' and rownum < '||row_limit;
825 sqltxt :=sqltxt||' order by bbmi.assembly_item_id, bbmi.organization_id, bbmi.alternate_bom_designator';
826
827 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_bill_of_mtls_interface table ');
828 If (num_rows = row_limit -1 ) Then
829 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/>');
830 End If;
831 statusStr := 'SUCCESS';
832 isFatal := 'FALSE';
833 /* End of bom_bill_of_mtls_interface */
834
835 /* End of tables exclusive to bom */
836
837 ElsIf upper(l_type) = 'RTG' Then
838 /* Fetch tables exclusive to rtg */
839
840 /* Get the application installation info. References to Data Dictionary Objects without schema name
841 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
842 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
843
844 l_ret_status := fnd_installation.get_app_info ('BOM'
845 , l_status
846 , l_industry
847 , l_oracle_schema
848 );
849
850 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
851
852 /* SQL to fetch records from bom_op_routings_interface table */
853 sqltxt := 'SELECT ' ||
854 ' BORI.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
855 ' BORI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
856 ' BORI.ORGANIZATION_ID "ORGANIZATION ID", '||
857 ' BORI.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
858 ' to_char(BORI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE",'||
859 ' BORI.LAST_UPDATED_BY "LAST UPDATED BY", '||
860 ' to_char(BORI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
861 ' BORI.CREATED_BY "CREATED BY", '||
862 ' BORI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
863 ' BORI.ROUTING_TYPE "ROUTING TYPE", '||
864 ' BORI.COMMON_ASSEMBLY_ITEM_ID "COMMON ASSEMBLY ITEM ID", '||
865 ' BORI.COMMON_ROUTING_SEQUENCE_ID "COMMON ROUTING SEQUENCE ID", '||
866 ' BORI.ROUTING_COMMENT "ROUTING COMMENT", '||
867 ' BORI.COMPLETION_SUBINVENTORY "COMPLETION SUBINVENTORY", '||
868 ' BORI.COMPLETION_LOCATOR_ID "COMPLETION LOCATOR ID", '||
869 ' BORI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
870 ' BORI.ATTRIBUTE1 "ATTRIBUTE1", '||
871 ' BORI.ATTRIBUTE2 "ATTRIBUTE2", '||
872 ' BORI.ATTRIBUTE3 "ATTRIBUTE3", '||
873 ' BORI.ATTRIBUTE4 "ATTRIBUTE4", '||
874 ' BORI.ATTRIBUTE5 "ATTRIBUTE5", '||
875 ' BORI.ATTRIBUTE6 "ATTRIBUTE6", '||
876 ' BORI.ATTRIBUTE7 "ATTRIBUTE7", '||
877 ' BORI.ATTRIBUTE8 "ATTRIBUTE8", '||
878 ' BORI.ATTRIBUTE9 "ATTRIBUTE9", '||
879 ' BORI.ATTRIBUTE10 "ATTRIBUTE10", '||
880 ' BORI.ATTRIBUTE11 "ATTRIBUTE11", '||
881 ' BORI.ATTRIBUTE12 "ATTRIBUTE12", '||
882 ' BORI.ATTRIBUTE13 "ATTRIBUTE13", '||
883 ' BORI.ATTRIBUTE14 "ATTRIBUTE14", '||
884 ' BORI.ATTRIBUTE15 "ATTRIBUTE15", '||
885 ' BORI.REQUEST_ID "REQUEST ID", '||
886 ' BORI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
887 ' BORI.PROGRAM_ID "PROGRAM ID", '||
888 ' to_char(BORI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
889 ' BORI.DEMAND_SOURCE_LINE "DEMAND SOURCE LINE", '||
890 ' BORI.SET_ID "SET ID", '||
891 ' BORI.PROCESS_REVISION "PROCESS REVISION", '||
892 ' BORI.DEMAND_SOURCE_TYPE "DEMAND SOURCE TYPE", '||
893 ' BORI.DEMAND_SOURCE_HEADER_ID "DEMAND SOURCE HEADER ID", '||
894 ' BORI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
895 ' BORI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
896 ' BORI.COMMON_ITEM_NUMBER "COMMON ITEM NUMBER", '||
897 ' BORI.LOCATION_NAME "LOCATION NAME", '||
898 ' BORI.TRANSACTION_ID "TRANSACTION ID", '||
899 ' BORI.PROCESS_FLAG "PROCESS FLAG", '||
900 ' BORI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
901 ' BORI.LINE_ID "LINE ID", '||
902 ' BORI.LINE_CODE "LINE CODE", '||
903 ' BORI.MIXED_MODEL_MAP_FLAG "MIXED MODEL MAP FLAG", '||
904 ' BORI.PRIORITY "PRIORITY", '||
905 ' BORI.CFM_ROUTING_FLAG "CFM ROUTING FLAG", '||
906 ' BORI.TOTAL_PRODUCT_CYCLE_TIME "TOTAL PRODUCT CYCLE TIME", '||
907 ' BORI.CTP_FLAG "CTP FLAG", '||
908 ' BORI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
909 ' BORI.SERIALIZATION_START_OP "SERIALIZATION START OP", '||
910 ' BORI.DELETE_GROUP_NAME "DELETE GROUP NAME", '||
911 ' BORI.DG_DESCRIPTION "DG DESCRIPTION", '||
912 ' BORI.BATCH_ID "BATCH ID" '||
913 ' from bom_op_routings_interface bori where 1=1 ';
914
915 sqltxt :=sqltxt||' and rownum < '||row_limit;
916 sqltxt :=sqltxt||' order by bori.assembly_item_id, bori.organization_id, bori.alternate_routing_designator';
920 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/>');
917
918 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_op_routings_interface table ');
919 If (num_rows = row_limit -1 ) Then
921 End If;
922 statusStr := 'SUCCESS';
923 isFatal := 'FALSE';
924
925 /* End of bom_op_routings_interface */
926
927 /* SQL to fetch records from bom_op_networks_interface table */
928 sqltxt := 'SELECT ' ||
929 ' BONI.FROM_OP_SEQ_ID "FROM OP SEQ ID", '||
930 ' BONI.TO_OP_SEQ_ID "TO OP SEQ ID", '||
931 ' BONI.TRANSITION_TYPE "TRANSITION TYPE", '||
932 ' BONI.PLANNING_PCT "PLANNING PCT", '||
933 ' BONI.OPERATION_TYPE "OPERATION TYPE", '||
934 ' to_char(BONI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
935 ' BONI.LAST_UPDATED_BY "LAST UPDATED BY", '||
936 ' to_char(BONI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
937 ' BONI.CREATED_BY "CREATED BY", '||
938 ' BONI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
939 ' BONI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
940 ' BONI.ATTRIBUTE1 "ATTRIBUTE1", '||
941 ' BONI.ATTRIBUTE2 "ATTRIBUTE2", '||
942 ' BONI.ATTRIBUTE3 "ATTRIBUTE3", '||
943 ' BONI.ATTRIBUTE4 "ATTRIBUTE4", '||
944 ' BONI.ATTRIBUTE5 "ATTRIBUTE5", '||
945 ' BONI.ATTRIBUTE6 "ATTRIBUTE6", '||
946 ' BONI.ATTRIBUTE7 "ATTRIBUTE7", '||
947 ' BONI.ATTRIBUTE8 "ATTRIBUTE8", '||
948 ' BONI.ATTRIBUTE9 "ATTRIBUTE9", '||
949 ' BONI.ATTRIBUTE10 "ATTRIBUTE10", '||
950 ' BONI.ATTRIBUTE11 "ATTRIBUTE11", '||
951 ' BONI.ATTRIBUTE12 "ATTRIBUTE12", '||
952 ' BONI.ATTRIBUTE13 "ATTRIBUTE13", '||
953 ' BONI.ATTRIBUTE14 "ATTRIBUTE14", '||
954 ' BONI.ATTRIBUTE15 "ATTRIBUTE15", '||
955 ' BONI.FROM_X_COORDINATE "FROM X COORDINATE", '||
956 ' BONI.TO_X_COORDINATE "TO X COORDINATE", '||
957 ' BONI.FROM_Y_COORDINATE "FROM Y COORDINATE", '||
958 ' BONI.TO_Y_COORDINATE "TO Y COORDINATE", '||
959 ' BONI.FROM_OP_SEQ_NUMBER "FROM OP SEQ NUMBER", '||
960 ' BONI.TO_OP_SEQ_NUMBER "TO OP SEQ NUMBER", '||
961 ' to_char(BONI.FROM_START_EFFECTIVE_DATE,''DD-MON-YYYY HH24:MI:SS'') "FROM START EFFECTIVE DATE", '||
962 ' to_char(BONI.TO_START_EFFECTIVE_DATE,''DD-MON-YYYY HH24:MI:SS'') "TO START EFFECTIVE DATE", '||
963 ' BONI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
964 ' BONI.PROGRAM_ID "PROGRAM ID", '||
965 ' to_char(BONI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
966 ' BONI.NEW_FROM_OP_SEQ_NUMBER "NEW FROM OP SEQ NUMBER", '||
967 ' BONI.NEW_TO_OP_SEQ_NUMBER "NEW TO OP SEQ NUMBER", '||
968 ' to_char(BONI.NEW_FROM_START_EFFECTIVE_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEW FROM START EFFECTIVE DATE", '||
969 ' to_char(BONI.NEW_TO_START_EFFECTIVE_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEW TO START EFFECTIVE DATE", '||
970 ' BONI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
971 ' BONI.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
972 ' BONI.ORGANIZATION_ID "ORGANIZATION ID", '||
973 ' BONI.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
974 ' BONI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
975 ' BONI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
976 ' BONI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
977 ' BONI.TRANSACTION_ID "TRANSACTION ID", '||
978 ' BONI.PROCESS_FLAG "PROCESS FLAG", '||
979 ' BONI.TRANSACTION_TYPE "TRANSACTION TYPE" '||
980 ' ,BONI.REQUEST_ID "REQUEST ID" '||
981 ' ,BONI.BATCH_ID "BATCH ID" '||
982 ' from bom_op_networks_interface boni where 1=1 ';
983
984 sqltxt :=sqltxt||' and rownum < '||row_limit;
985 sqltxt :=sqltxt||' order by boni.assembly_item_id, boni.organization_id, boni.alternate_routing_designator,'||
986 ' boni.from_op_seq_id, boni.to_op_seq_id ';
987
988 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_op_networks_interface table ');
989 If (num_rows = row_limit -1 ) Then
990 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/>');
991 End If;
992 statusStr := 'SUCCESS';
993 isFatal := 'FALSE';
994
995 /* End of bom_op_networks_interface */
996
997 /* End of tables exclusive to rtg */
998
999 ElsIf upper(l_type) = 'ENG' Then
1000 /* Fetch tables exclusive to eng */
1001 /* SQL to fetch records from eng_eng_changes_interface table */
1002 sqltxt := 'SELECT ' ||
1003 ' EECI.CHANGE_NOTICE "CHANGE NOTICE", '||
1004 ' EECI.ORGANIZATION_ID "ORGANIZATION ID", '||
1005 ' to_char(EECI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1006 ' EECI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1007 ' to_char(EECI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1008 ' EECI.CREATED_BY "CREATED BY", '||
1009 ' EECI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1010 ' EECI.DESCRIPTION "DESCRIPTION", '||
1011 ' EECI.STATUS_TYPE "STATUS TYPE", '||
1012 ' to_char(EECI.INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "INITIATION DATE", '||
1013 ' to_char(EECI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE", '||
1014 ' to_char(EECI.CANCELLATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CANCELLATION DATE", '||
1015 ' EECI.CANCELLATION_COMMENTS "CANCELLATION COMMENTS", '||
1016 ' EECI.PRIORITY_CODE "PRIORITY CODE", '||
1017 ' EECI.REASON_CODE "REASON CODE", '||
1018 ' EECI.ESTIMATED_ENG_COST "ESTIMATED ENG COST", '||
1019 ' EECI.ESTIMATED_MFG_COST "ESTIMATED MFG COST", '||
1020 ' EECI.REQUESTOR_ID "REQUESTOR ID", '||
1024 ' EECI.ATTRIBUTE3 "ATTRIBUTE3", '||
1021 ' EECI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1022 ' EECI.ATTRIBUTE1 "ATTRIBUTE1", '||
1023 ' EECI.ATTRIBUTE2 "ATTRIBUTE2", '||
1025 ' EECI.ATTRIBUTE4 "ATTRIBUTE4", '||
1026 ' EECI.ATTRIBUTE5 "ATTRIBUTE5", '||
1027 ' EECI.ATTRIBUTE6 "ATTRIBUTE6", '||
1028 ' EECI.ATTRIBUTE7 "ATTRIBUTE7", '||
1029 ' EECI.ATTRIBUTE8 "ATTRIBUTE8", '||
1030 ' EECI.ATTRIBUTE9 "ATTRIBUTE9", '||
1031 ' EECI.ATTRIBUTE10 "ATTRIBUTE10", '||
1032 ' EECI.ATTRIBUTE11 "ATTRIBUTE11", '||
1033 ' EECI.ATTRIBUTE12 "ATTRIBUTE12", '||
1034 ' EECI.ATTRIBUTE13 "ATTRIBUTE13", '||
1035 ' EECI.ATTRIBUTE14 "ATTRIBUTE14", '||
1036 ' EECI.ATTRIBUTE15 "ATTRIBUTE15", '||
1037 ' EECI.REQUEST_ID "REQUEST ID", '||
1038 ' EECI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1039 ' EECI.PROGRAM_ID "PROGRAM ID", '||
1040 ' to_char(EECI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
1041 ' EECI.APPROVAL_STATUS_TYPE "APPROVAL STATUS TYPE", '||
1042 ' to_char(EECI.APPROVAL_DATE,''DD-MON-YYYY HH24:MI:SS'') "APPROVAL DATE", '||
1043 ' EECI.APPROVAL_LIST_ID "APPROVAL LIST ID", '||
1044 ' EECI.APPROVAL_LIST_NAME "APPROVAL LIST NAME", '||
1045 ' EECI.CHANGE_ORDER_TYPE_ID "CHANGE ORDER TYPE ID", '||
1046 ' EECI.CHANGE_ORDER_TYPE "CHANGE ORDER TYPE", '||
1047 ' EECI.RESPONSIBLE_ORGANIZATION_ID "RESPONSIBLE ORGANIZATION ID", '||
1048 ' EECI.SET_ID "SET ID", '||
1049 ' to_char(EECI.APPROVAL_REQUEST_DATE,''DD-MON-YYYY HH24:MI:SS'') "APPROVAL REQUEST DATE",'||
1050 ' EECI.DDF_CONTEXT "DDF CONTEXT", '||
1051 ' EECI.CO_CREATED "CO CREATED", '||
1052 ' EECI.TRANSACTION_ID "TRANSACTION ID", '||
1053 ' EECI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1054 ' EECI.PROCESS_FLAG "PROCESS FLAG", '||
1055 ' EECI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1056 ' EECI.RESPONSIBLE_ORG_CODE "RESPONSIBLE ORG CODE", '||
1057 ' EECI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1058 ' EECI.CHANGE_ID "CHANGE ID", '||
1059 ' EECI.STATUS_NAME "STATUS NAME", '||
1060 ' EECI.REQUESTOR_USER_NAME "REQUESTOR USER NAME", '||
1061 ' EECI.APPROVAL_STATUS_NAME "APPROVAL STATUS NAME", '||
1062 ' EECI.CHANGE_MGMT_TYPE_CODE "CHANGE MGMT TYPE CODE", '||
1063 ' EECI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1064 ' EECI.ORGANIZATION_HIERARCHY "ORGANIZATION HIERARCHY", '||
1065 ' EECI.ASSIGNEE_NAME "ASSIGNEE NAME", '||
1066 ' EECI.SOURCE_TYPE_NAME "SOURCE TYPE NAME", '||
1067 ' EECI.SOURCE_NAME "SOURCE NAME", '||
1068 ' to_char(EECI.NEED_BY_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEED BY DATE",'||
1069 ' EECI.EFFORT "EFFORT", '||
1070 ' EECI.CHANGE_NAME "CHANGE NAME", '||
1071 ' EECI.CHANGE_NOTICE_PREFIX "CHANGE NOTICE PREFIX", '||
1072 ' EECI.CHANGE_NOTICE_NUMBER "CHANGE NOTICE NUMBER", '||
1073 ' EECI.ECO_DEPARTMENT_NAME "ECO DEPARTMENT NAME", '||
1074 ' EECI.INTERNAL_USE_ONLY "INTERNAL USE ONLY", '||
1075 ' EECI.CHANGE_MGMT_TYPE_NAME "CHANGE MGMT TYPE NAME", '||
1076 ' EECI.PROJECT_NAME "PROJECT NAME", '||
1077 ' EECI.TASK_NUMBER "TASK NUMBER", '||
1078 ' EECI.PK1_NAME "PK1 NAME", '||
1079 ' EECI.PK2_NAME "PK2 NAME", '||
1080 ' EECI.PK3_NAME "PK3 NAME", '||
1081 ' EECI.PLM_OR_ERP_CHANGE "PLM OR ERP CHANGE", '||
1082 ' EECI.ASSIGNEE_ID "ASSIGNEE ID", '||
1083 ' EECI.CLASSIFICATION_ID "CLASSIFICATION ID", '||
1084 ' EECI.HIERARCHY_ID "HIERARCHY ID", '||
1085 ' EECI.SOURCE_ID "SOURCE ID", '||
1086 ' EECI.SOURCE_TYPE_CODE "SOURCE TYPE CODE", '||
1087 ' EECI.EMPLOYEE_NUMBER "EMPLOYEE NUMBER" '||
1088 ' from eng_eng_changes_interface eeci where 1=1 ';
1089
1090 sqltxt :=sqltxt||' and rownum < '||row_limit;
1091 sqltxt :=sqltxt||' order by eeci.change_notice, eeci.organization_id ';
1092
1093 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in eng_eng_changes_interface table ');
1094 If (num_rows = row_limit -1 ) Then
1095 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/>');
1096 End If;
1097 statusStr := 'SUCCESS';
1098 isFatal := 'FALSE';
1099
1100 /* End of eng_eng_changes_interface */
1101
1102 /* SQL to fetch records from eng_revised_items_interface table */
1103 sqltxt := 'SELECT ' ||
1104 ' ERII.CHANGE_NOTICE "CHANGE NOTICE", '||
1105 ' ERII.ORGANIZATION_ID "ORGANIZATION ID", '||
1106 ' ERII.REVISED_ITEM_ID "REVISED ITEM ID", '||
1107 ' to_char(ERII.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE",'||
1108 ' ERII.LAST_UPDATED_BY "LAST UPDATED BY", '||
1109 ' to_char(ERII.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1110 ' ERII.CREATED_BY "CREATED BY", '||
1111 ' ERII.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1112 ' to_char(ERII.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'')"IMPLEMENTATION DATE", '||
1113 ' ERII.DESCRIPTIVE_TEXT "DESCRIPTIVE TEXT", '||
1114 ' to_char(ERII.CANCELLATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CANCELLATION DATE", '||
1115 ' ERII.CANCEL_COMMENTS "CANCEL COMMENTS", '||
1116 ' ERII.DISPOSITION_TYPE "DISPOSITION TYPE", '||
1117 ' ERII.NEW_ITEM_REVISION "NEW ITEM REVISION", '||
1118 ' to_char(ERII.AUTO_IMPLEMENT_DATE,''DD-MON-YYYY HH24:MI:SS'') "AUTO IMPLEMENT DATE", '||
1119 ' to_char(ERII.EARLY_SCHEDULE_DATE,''DD-MON-YYYY HH24:MI:SS'') "EARLY SCHEDULE DATE", '||
1120 ' ERII.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1121 ' ERII.ATTRIBUTE1 "ATTRIBUTE1", '||
1122 ' ERII.ATTRIBUTE2 "ATTRIBUTE2", '||
1123 ' ERII.ATTRIBUTE3 "ATTRIBUTE3", '||
1127 ' ERII.ATTRIBUTE7 "ATTRIBUTE7", '||
1124 ' ERII.ATTRIBUTE4 "ATTRIBUTE4", '||
1125 ' ERII.ATTRIBUTE5 "ATTRIBUTE5", '||
1126 ' ERII.ATTRIBUTE6 "ATTRIBUTE6", '||
1128 ' ERII.ATTRIBUTE8 "ATTRIBUTE8", '||
1129 ' ERII.ATTRIBUTE9 "ATTRIBUTE9", '||
1130 ' ERII.ATTRIBUTE10 "ATTRIBUTE10", '||
1131 ' ERII.ATTRIBUTE11 "ATTRIBUTE11", '||
1132 ' ERII.ATTRIBUTE12 "ATTRIBUTE12", '||
1133 ' ERII.ATTRIBUTE13 "ATTRIBUTE13", '||
1134 ' ERII.ATTRIBUTE14 "ATTRIBUTE14", '||
1135 ' ERII.ATTRIBUTE15 "ATTRIBUTE15", '||
1136 ' ERII.STATUS_TYPE "STATUS TYPE", '||
1137 ' to_char(ERII.SCHEDULED_DATE,''DD-MON-YYYY HH24:MI:SS'') "SCHEDULED DATE", '||
1138 ' ERII.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
1139 ' ERII.MRP_ACTIVE "MRP ACTIVE", '||
1140 ' ERII.REQUEST_ID "REQUEST ID", '||
1141 ' ERII.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1142 ' ERII.PROGRAM_ID "PROGRAM ID", '||
1143 ' to_char(ERII.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1144 ' ERII.UPDATE_WIP "UPDATE WIP", '||
1145 ' ERII.USE_UP "USE UP", '||
1146 ' ERII.USE_UP_ITEM_ID "USE UP ITEM ID", '||
1147 ' ERII.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID", '||
1148 ' ERII.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR", '||
1149 ' ERII.CATEGORY_SET_ID "CATEGORY SET ID", '||
1150 ' ERII.STRUCTURE_ID "STRUCTURE ID", '||
1151 ' ERII.ITEM_FROM "ITEM FROM", '||
1152 ' ERII.ITEM_TO "ITEM TO", '||
1153 ' ERII.CATEGORY_FROM "CATEGORY FROM", '||
1154 ' ERII.CATEGORY_TO "CATEGORY TO", '||
1155 ' ERII.DDF_CONTEXT "DDF CONTEXT", '||
1156 ' ERII.INCREMENT_REV "INCREMENT REV", '||
1157 ' ERII.ITEM_TYPE "ITEM TYPE", '||
1158 ' ERII.USE_UP_PLAN_NAME "USE UP PLAN NAME", '||
1159 ' ERII.ALTERNATE_SELECTION_CODE "ALTERNATE SELECTION CODE", '||
1160 ' ERII.TRANSACTION_ID "TRANSACTION ID", '||
1161 ' ERII.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1162 ' ERII.PROCESS_FLAG "PROCESS FLAG", '||
1163 ' ERII.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1164 ' ERII.REQUESTOR_ID "REQUESTOR ID", '||
1165 ' ERII.COMMENTS "COMMENTS", '||
1166 ' ERII.REVISED_ITEM_NUMBER "REVISED ITEM NUMBER", '||
1167 ' ERII.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1168 ' ERII.USE_UP_ITEM_NUMBER "USE UP ITEM NUMBER", '||
1169 ' ERII.APPROVAL_LIST_NAME "APPROVAL LIST NAME", '||
1170 ' ERII.BASE_ITEM_ID "BASE ITEM ID", '||
1171 ' ERII.ENG_REVISED_ITEMS_IFCE_KEY "ENG REVISED ITEMS IFCE KEY", '||
1172 ' ERII.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1173 ' ERII.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER", '||
1174 ' ERII.NEW_RTG_REVISION "NEW RTG REVISION", '||
1175 ' ERII.FROM_END_ITEM_REV_ID "FROM END ITEM REV ID", '||
1176 ' ERII.FROM_END_ITEM_NUMBER "FROM END ITEM NUMBER", '||
1177 ' ERII.FROM_END_ITEM_REVISION "FROM END ITEM REVISION", '||
1178 ' ERII.PARENT_REVISED_ITEM_NAME "PARENT REVISED ITEM NAME", '||
1179 ' ERII.PARENT_ALTERNATE_NAME "PARENT ALTERNATE NAME", '||
1180 ' ERII.UPDATED_ITEM_REVISION "UPDATED ITEM REVISION", '||
1181 ' to_char(ERII.NEW_SCHEDULED_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEW SCHEDULED DATE", '||
1182 ' ERII.FROM_ITEM_REVISION "FROM ITEM REVISION", '||
1183 ' ERII.NEW_REVISION_LABEL "NEW REVISION LABEL", '||
1184 ' ERII.NEW_REVISED_ITEM_REV_DESC "NEW REVISED ITEM REV DESC", '||
1185 ' ERII.NEW_REVISION_REASON "NEW REVISION REASON" '||
1186 ' from eng_revised_items_interface erii where 1=1 ';
1187
1188 sqltxt :=sqltxt||' and rownum < '||row_limit;
1189 sqltxt :=sqltxt||' order by erii.change_notice,erii.organization_id, '||
1190 ' erii.revised_item_id,erii.revised_item_sequence_id ';
1191
1192 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in eng_revised_items_interface table ');
1193 If (num_rows = row_limit -1 ) Then
1194 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/>');
1195 End If;
1196 statusStr := 'SUCCESS';
1197 isFatal := 'FALSE';
1198
1199 /* End of eng_revised_items_interface */
1200
1201 /* SQL to fetch records from eng_eco_revisions_interface table */
1202 sqltxt := 'SELECT ' ||
1203 ' EERI.REVISION_ID "REVISION ID", '||
1204 ' EERI.CHANGE_NOTICE "CHANGE NOTICE", '||
1205 ' EERI.ORGANIZATION_ID "ORGANIZATION ID", '||
1206 ' EERI.REVISION "REVISION", '||
1207 ' to_char(EERI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'')"LAST UPDATE DATE",'||
1208 ' EERI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1209 ' to_char(EERI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1210 ' EERI.CREATED_BY "CREATED BY", '||
1211 ' EERI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1212 ' EERI.COMMENTS "COMMENTS", '||
1213 ' EERI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1214 ' EERI.ATTRIBUTE1 "ATTRIBUTE1", '||
1215 ' EERI.ATTRIBUTE2 "ATTRIBUTE2", '||
1216 ' EERI.ATTRIBUTE3 "ATTRIBUTE3", '||
1217 ' EERI.ATTRIBUTE4 "ATTRIBUTE4", '||
1218 ' EERI.ATTRIBUTE5 "ATTRIBUTE5", '||
1219 ' EERI.ATTRIBUTE6 "ATTRIBUTE6", '||
1220 ' EERI.ATTRIBUTE7 "ATTRIBUTE7", '||
1221 ' EERI.ATTRIBUTE8 "ATTRIBUTE8", '||
1222 ' EERI.ATTRIBUTE9 "ATTRIBUTE9", '||
1223 ' EERI.ATTRIBUTE10 "ATTRIBUTE10", '||
1224 ' EERI.ATTRIBUTE11 "ATTRIBUTE11", '||
1225 ' EERI.ATTRIBUTE12 "ATTRIBUTE12", '||
1226 ' EERI.ATTRIBUTE13 "ATTRIBUTE13", '||
1227 ' EERI.ATTRIBUTE14 "ATTRIBUTE14", '||
1231 ' to_char(EERI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1228 ' EERI.ATTRIBUTE15 "ATTRIBUTE15", '||
1229 ' EERI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1230 ' EERI.PROGRAM_ID "PROGRAM ID", '||
1232 ' EERI.REQUEST_ID "REQUEST ID", '||
1233 ' EERI.TRANSACTION_ID "TRANSACTION ID", '||
1234 ' EERI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1235 ' EERI.PROCESS_FLAG "PROCESS FLAG", '||
1236 ' EERI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1237 ' EERI.NEW_REVISION "NEW REVISION", '||
1238 ' EERI.ENG_ECO_REVISIONS_IFCE_KEY "ENG ECO REVISIONS IFCE KEY", '||
1239 ' EERI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1240 ' EERI.CHANGE_ID "CHANGE ID" '||
1241 ' from eng_eco_revisions_interface eeri where 1=1 ';
1242
1243 sqltxt :=sqltxt||' and rownum < '||row_limit;
1244 sqltxt :=sqltxt||' order by eeri.organization_id, eeri.change_notice,eeri.revision ';
1245
1246 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in eng_eco_revisions_interface table ');
1247 If (num_rows = row_limit -1 ) Then
1248 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/>');
1249 End If;
1250 statusStr := 'SUCCESS';
1251 isFatal := 'FALSE';
1252
1253 /* End of eng_eco_revisions_interface */
1254
1255 /* End of tables exclusive to eng */
1256 End If; /* End of exclusive tables */
1257
1258 If upper(l_type) in ('BOM','ENG') Then
1259 /* Fetch tables common to bom and eng */
1260
1261 /* Get the application installation info. References to Data Dictionary Objects without schema name
1262 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
1263 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
1264
1265 l_ret_status := fnd_installation.get_app_info ('BOM'
1266 , l_status
1267 , l_industry
1268 , l_oracle_schema
1269 );
1270
1271 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
1272
1273 /* SQL to fetch records from bom_inventory_comps_interface */
1274 sqltxt := 'SELECT ' ||
1275 ' BICI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1276 ' BICI.COMPONENT_ITEM_ID "COMPONENT ITEM ID", '||
1277 ' to_char(BICI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1278 ' BICI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1279 ' to_char(BICI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1280 ' BICI.CREATED_BY "CREATED BY", '||
1281 ' BICI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1282 ' BICI.ITEM_NUM "ITEM NUM", '||
1283 ' BICI.COMPONENT_QUANTITY "COMPONENT QUANTITY", '||
1284 ' BICI.COMPONENT_YIELD_FACTOR "COMPONENT YIELD FACTOR", '||
1285 ' BICI.COMPONENT_REMARKS "COMPONENT REMARKS", '||
1286 ' to_char(BICI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1287 ' BICI.CHANGE_NOTICE "CHANGE NOTICE", '||
1288 ' to_char(BICI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE", '||
1289 ' to_char(BICI.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE", '||
1290 ' BICI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1291 ' BICI.ATTRIBUTE1 "ATTRIBUTE1", '||
1292 ' BICI.ATTRIBUTE2 "ATTRIBUTE2", '||
1293 ' BICI.ATTRIBUTE3 "ATTRIBUTE3", '||
1294 ' BICI.ATTRIBUTE4 "ATTRIBUTE4", '||
1295 ' BICI.ATTRIBUTE5 "ATTRIBUTE5", '||
1296 ' BICI.ATTRIBUTE6 "ATTRIBUTE6", '||
1297 ' BICI.ATTRIBUTE7 "ATTRIBUTE7", '||
1298 ' BICI.ATTRIBUTE8 "ATTRIBUTE8", '||
1299 ' BICI.ATTRIBUTE9 "ATTRIBUTE9", '||
1300 ' BICI.ATTRIBUTE10 "ATTRIBUTE10", '||
1301 ' BICI.ATTRIBUTE11 "ATTRIBUTE11", '||
1302 ' BICI.ATTRIBUTE12 "ATTRIBUTE12", '||
1303 ' BICI.ATTRIBUTE13 "ATTRIBUTE13", '||
1304 ' BICI.ATTRIBUTE14 "ATTRIBUTE14", '||
1305 ' BICI.ATTRIBUTE15 "ATTRIBUTE15", '||
1306 ' BICI.PLANNING_FACTOR "PLANNING FACTOR", '||
1307 ' BICI.QUANTITY_RELATED "QUANTITY RELATED", '||
1308 ' BICI.SO_BASIS "SO BASIS", '||
1309 ' BICI.OPTIONAL "OPTIONAL", '||
1310 ' BICI.MUTUALLY_EXCLUSIVE_OPTIONS "MUTUALLY EXCLUSIVE OPTIONS", '||
1311 ' BICI.INCLUDE_IN_COST_ROLLUP "INCLUDE IN COST ROLLUP", '||
1312 ' BICI.CHECK_ATP "CHECK ATP", '||
1313 ' BICI.SHIPPING_ALLOWED "SHIPPING ALLOWED", '||
1314 ' BICI.REQUIRED_TO_SHIP "REQUIRED TO SHIP", '||
1315 ' BICI.REQUIRED_FOR_REVENUE "REQUIRED FOR REVENUE", '||
1316 ' BICI.INCLUDE_ON_SHIP_DOCS "INCLUDE ON SHIP DOCS", '||
1317 ' BICI.LOW_QUANTITY "LOW QUANTITY", '||
1318 ' BICI.HIGH_QUANTITY "HIGH QUANTITY", '||
1319 ' BICI.ACD_TYPE "ACD TYPE", '||
1320 ' BICI.OLD_COMPONENT_SEQUENCE_ID "OLD COMPONENT SEQUENCE ID", '||
1321 ' BICI.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID", '||
1322 ' BICI.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
1323 ' BICI.REQUEST_ID "REQUEST ID", '||
1324 ' BICI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1325 ' BICI.PROGRAM_ID "PROGRAM ID", '||
1326 ' to_char(BICI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1327 ' BICI.WIP_SUPPLY_TYPE "WIP SUPPLY TYPE", '||
1328 ' BICI.SUPPLY_SUBINVENTORY "SUPPLY SUBINVENTORY", '||
1329 ' BICI.SUPPLY_LOCATOR_ID "SUPPLY LOCATOR ID", '||
1330 ' BICI.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID", '||
1331 ' BICI.MODEL_COMP_SEQ_ID "MODEL COMP SEQ ID", '||
1332 ' BICI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1333 ' BICI.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR", '||
1334 ' BICI.ORGANIZATION_ID "ORGANIZATION ID", '||
1338 ' BICI.REVISED_ITEM_NUMBER "REVISED ITEM NUMBER", '||
1335 ' BICI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1336 ' BICI.COMPONENT_ITEM_NUMBER "COMPONENT ITEM NUMBER", '||
1337 ' BICI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1339 ' BICI.LOCATION_NAME "LOCATION NAME", '||
1340 ' BICI.REFERENCE_DESIGNATOR "REFERENCE DESIGNATOR", '||
1341 ' BICI.SUBSTITUTE_COMP_ID "SUBSTITUTE COMP ID", '||
1342 ' BICI.SUBSTITUTE_COMP_NUMBER "SUBSTITUTE COMP NUMBER", '||
1343 ' BICI.TRANSACTION_ID "TRANSACTION ID", '||
1344 ' BICI.PROCESS_FLAG "PROCESS FLAG", '||
1345 ' BICI.BOM_ITEM_TYPE "BOM ITEM TYPE", '||
1346 ' BICI.OPERATION_LEAD_TIME_PERCENT "OPERATION LEAD TIME PERCENT", '||
1347 ' BICI.COST_FACTOR "COST FACTOR", '||
1348 ' BICI.INCLUDE_ON_BILL_DOCS "INCLUDE ON BILL DOCS", '||
1349 ' BICI.PICK_COMPONENTS "PICK COMPONENTS", '||
1350 ' BICI.DDF_CONTEXT1 "DDF CONTEXT1", '||
1351 ' BICI.DDF_CONTEXT2 "DDF CONTEXT2", '||
1352 ' BICI.NEW_OPERATION_SEQ_NUM "NEW OPERATION SEQ NUM", '||
1353 ' BICI.OLD_OPERATION_SEQ_NUM "OLD OPERATION SEQ NUM", '||
1354 ' to_char(BICI.NEW_EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEW EFFECTIVITY DATE", '||
1355 ' to_char(BICI.OLD_EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "OLD EFFECTIVITY DATE", '||
1356 ' BICI.ASSEMBLY_TYPE "ASSEMBLY TYPE", '||
1357 ' BICI.INTERFACE_ENTITY_TYPE "INTERFACE ENTITY TYPE" '||
1358 ' from bom_inventory_comps_interface bici where 1=1 ';
1359
1360 sqltxt :=sqltxt||' and rownum < '||row_limit;
1361 sqltxt :=sqltxt||' order by bici.operation_seq_num, bici.component_item_id';
1362
1363 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_inventory_comps_interface table ');
1364 If (num_rows = row_limit -1 ) Then
1365 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/>');
1366 End If;
1367 statusStr := 'SUCCESS';
1368 isFatal := 'FALSE';
1369
1370
1371 sqltxt := 'SELECT ' ||
1372 ' BICI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1373 ' BICI.COMPONENT_ITEM_ID "COMPONENT ITEM ID", '||
1374 ' BICI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1375 ' BICI.BOM_INVENTORY_COMPS_IFCE_KEY "BOM INVENTORY COMPS IFCE KEY", '||
1376 ' BICI.ENG_REVISED_ITEMS_IFCE_KEY "ENG REVISED ITEMS IFCE KEY", '||
1377 ' BICI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1378 ' BICI.TO_END_ITEM_UNIT_NUMBER "TO END ITEM UNIT NUMBER", '||
1379 ' BICI.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER", '||
1380 ' BICI.NEW_FROM_END_ITEM_UNIT_NUMBER "NEW FROM END ITEM UNIT NUMBER", '||
1381 ' BICI.DELETE_GROUP_NAME "DELETE GROUP NAME", '||
1382 ' BICI.DG_DESCRIPTION "DG DESCRIPTION", '||
1383 ' BICI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1384 ' BICI.ENFORCE_INT_REQUIREMENTS "ENFORCE INT REQUIREMENTS", '||
1385 ' BICI.OPTIONAL_ON_MODEL "OPTIONAL ON MODEL", '||
1386 ' BICI.PARENT_BILL_SEQ_ID "PARENT BILL SEQ ID", '||
1387 ' BICI.PLAN_LEVEL "PLAN LEVEL", '||
1388 ' BICI.AUTO_REQUEST_MATERIAL "AUTO REQUEST MATERIAL" '||
1389 ' ,BICI.SUGGESTED_VENDOR_NAME "SUGGESTED VENDOR NAME" '||
1390 ' ,BICI.UNIT_PRICE "UNIT PRICE" '||
1391 ' ,BICI.NEW_REVISED_ITEM_REVISION "NEW REVISED ITEM REVISION", '||
1392 ' BICI.BASIS_TYPE "BASIS TYPE", '||
1393 ' BICI.INVERSE_QUANTITY "INVERSE QUANTITY", '||
1394 ' BICI.OBJ_NAME "OBJ NAME", '||
1395 ' BICI.PK1_VALUE "PK1 VALUE", '||
1396 ' BICI.PK2_VALUE "PK2 VALUE", '||
1397 ' BICI.PK3_VALUE "PK3 VALUE", '||
1398 ' BICI.PK4_VALUE "PK4 VALUE", '||
1399 ' BICI.PK5_VALUE "PK5 VALUE", '||
1400 ' BICI.FROM_OBJECT_REVISION_CODE "FROM OBJECT REVISION CODE", '||
1401 ' BICI.FROM_OBJECT_REVISION_ID "FROM OBJECT REVISION ID", '||
1402 ' BICI.TO_OBJECT_REVISION_CODE "TO OBJECT REVISION CODE", '||
1403 ' BICI.TO_OBJECT_REVISION_ID "TO OBJECT REVISION ID", '||
1404 ' BICI.FROM_MINOR_REVISION_CODE "FROM MINOR REVISION CODE", '||
1405 ' BICI.FROM_MINOR_REVISION_ID "FROM MINOR REVISION ID", '||
1406 ' BICI.TO_MINOR_REVISION_CODE "TO MINOR REVISION CODE", '||
1407 ' BICI.TO_MINOR_REVISION_ID "TO MINOR REVISION ID", '||
1408 ' BICI.FROM_END_ITEM_MINOR_REV_CODE "FROM END ITEM MINOR REV CODE", '||
1409 ' BICI.FROM_END_ITEM_MINOR_REV_ID "FROM END ITEM MINOR REV ID", '||
1410 ' BICI.TO_END_ITEM_MINOR_REV_CODE "TO END ITEM MINOR REV CODE", '||
1411 ' BICI.TO_END_ITEM_MINOR_REV_ID "TO END ITEM MINOR REV ID", '||
1412 ' BICI.RETURN_STATUS "RETURN STATUS", '||
1413 ' BICI.FROM_END_ITEM "FROM END ITEM", '||
1414 ' BICI.FROM_END_ITEM_ID "FROM END ITEM ID", '||
1415 ' BICI.FROM_END_ITEM_REV_CODE "FROM END ITEM REV CODE", '||
1416 ' BICI.FROM_END_ITEM_REV_ID "FROM END ITEM REV ID", '||
1417 ' BICI.TO_END_ITEM_REV_CODE "TO END ITEM REV CODE", '||
1418 ' BICI.TO_END_ITEM_REV_ID "TO END ITEM REV ID", '||
1419 ' BICI.COMPONENT_REVISION_CODE "COMPONENT REVISION CODE", '||
1420 ' BICI.COMPONENT_REVISION_ID "COMPONENT REVISION ID", '||
1421 ' BICI.BATCH_ID "BATCH ID", '||
1422 ' BICI.COMP_SOURCE_SYSTEM_REFERENCE "COMP SOURCE SYSTEM REFERENCE", '||
1423 ' BICI.COMP_SOURCE_SYSTEM_REFER_DESC "COMP SOURCE SYSTEM REFER DESC", '||
1424 ' BICI.PARENT_SOURCE_SYSTEM_REFERENCE "PARENT SOURCE SYSTEM REFERENCE", '||
1425 ' BICI.CATALOG_CATEGORY_NAME "CATALOG CATEGORY NAME", '||
1426 ' BICI.ITEM_CATALOG_GROUP_ID "ITEM CATALOG GROUP ID", '||
1427 ' BICI.CHANGE_ID "CHANGE ID", '||
1428 ' BICI.TEMPLATE_NAME "TEMPLATE NAME", '||
1429 ' BICI.PRIMARY_UNIT_OF_MEASURE "PRIMARY UNIT OF MEASURE", '||
1430 ' BICI.ITEM_DESCRIPTION "ITEM DESCRIPTION", '||
1431 ' BICI.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID", '||
1435 ' BICI.PARENT_REVISION_ID "PARENT REVISION ID" '||
1432 ' BICI.CHANGE_TRANSACTION_TYPE "CHANGE TRANSACTION TYPE", '||
1433 ' BICI.INTERFACE_TABLE_UNIQUE_ID "INTERFACE TABLE UNIQUE ID", '||
1434 ' BICI.PARENT_REVISION_CODE "PARENT REVISION CODE", '||
1436 ' from bom_inventory_comps_interface bici where 1=1 ';
1437
1438 sqltxt :=sqltxt||' and rownum < '||row_limit;
1439 sqltxt :=sqltxt||' order by bici.operation_seq_num, bici.component_item_id';
1440
1441 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_inventory_comps_interface table (Contd 1..) ');
1442 If (num_rows = row_limit -1 ) Then
1443 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/>');
1444 End If;
1445 statusStr := 'SUCCESS';
1446 isFatal := 'FALSE';
1447
1448
1449 /* End of bom_inventory_comps_interface */
1450
1451 /* SQL to fetch records from bom_ref_desgs_interface table */
1452 sqltxt := 'SELECT ' ||
1453 ' BRDI.COMPONENT_REFERENCE_DESIGNATOR "COMPONENT REFERENCE DESIGNATOR", '||
1454 ' to_char(BRDI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1455 ' BRDI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1456 ' to_char(BRDI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1457 ' BRDI.CREATED_BY "CREATED BY", '||
1458 ' BRDI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1459 ' BRDI.REF_DESIGNATOR_COMMENT "REF DESIGNATOR COMMENT", '||
1460 ' BRDI.CHANGE_NOTICE "CHANGE NOTICE", '||
1461 ' BRDI.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID", '||
1462 ' BRDI.ACD_TYPE "ACD TYPE", '||
1463 ' BRDI.REQUEST_ID "REQUEST ID", '||
1464 ' BRDI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1465 ' BRDI.PROGRAM_ID "PROGRAM ID", '||
1466 ' to_char(BRDI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1467 ' BRDI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1468 ' BRDI.ATTRIBUTE1 "ATTRIBUTE1", '||
1469 ' BRDI.ATTRIBUTE2 "ATTRIBUTE2", '||
1470 ' BRDI.ATTRIBUTE3 "ATTRIBUTE3", '||
1471 ' BRDI.ATTRIBUTE4 "ATTRIBUTE4", '||
1472 ' BRDI.ATTRIBUTE5 "ATTRIBUTE5", '||
1473 ' BRDI.ATTRIBUTE6 "ATTRIBUTE6", '||
1474 ' BRDI.ATTRIBUTE7 "ATTRIBUTE7", '||
1475 ' BRDI.ATTRIBUTE8 "ATTRIBUTE8", '||
1476 ' BRDI.ATTRIBUTE9 "ATTRIBUTE9", '||
1477 ' BRDI.ATTRIBUTE10 "ATTRIBUTE10", '||
1478 ' BRDI.ATTRIBUTE11 "ATTRIBUTE11", '||
1479 ' BRDI.ATTRIBUTE12 "ATTRIBUTE12", '||
1480 ' BRDI.ATTRIBUTE13 "ATTRIBUTE13", '||
1481 ' BRDI.ATTRIBUTE14 "ATTRIBUTE14", '||
1482 ' BRDI.ATTRIBUTE15 "ATTRIBUTE15", '||
1483 ' BRDI.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
1484 ' BRDI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1485 ' BRDI.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR", '||
1486 ' BRDI.ORGANIZATION_ID "ORGANIZATION ID", '||
1487 ' BRDI.COMPONENT_ITEM_ID "COMPONENT ITEM ID", '||
1488 ' BRDI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1489 ' to_char(BRDI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1490 ' BRDI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1491 ' BRDI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1492 ' BRDI.COMPONENT_ITEM_NUMBER "COMPONENT ITEM NUMBER", '||
1493 ' BRDI.TRANSACTION_ID "TRANSACTION ID", '||
1494 ' BRDI.PROCESS_FLAG "PROCESS FLAG", '||
1495 ' BRDI.NEW_DESIGNATOR "NEW DESIGNATOR", '||
1496 ' BRDI.INTERFACE_ENTITY_TYPE "INTERFACE ENTITY TYPE", '||
1497 ' BRDI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1498 ' BRDI.BOM_REF_DESGS_IFCE_KEY "BOM REF DESGS IFCE KEY", '||
1499 ' BRDI.BOM_INVENTORY_COMPS_IFCE_KEY "BOM INVENTORY COMPS IFCE KEY", '||
1500 ' BRDI.ENG_REVISED_ITEMS_IFCE_KEY "ENG REVISED ITEMS IFCE KEY", '||
1501 ' BRDI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1502 ' BRDI.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER", '||
1503 ' BRDI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1504 ' ,BRDI.NEW_REVISED_ITEM_REVISION "NEW REVISED ITEM REVISION" '||
1505 ' ,BRDI.RETURN_STATUS "RETURN STATUS" '||
1506 ' ,BRDI.BATCH_ID "BATCH ID" '||
1507 ' ,BRDI.COMP_SOURCE_SYSTEM_REFERENCE "COMP SOURCE SYSTEM REFERENCE" '||
1508 ' ,BRDI.PARENT_SOURCE_SYSTEM_REFERENCE "PARENT SOURCE SYSTEM REFERENCE" '||
1509 ' ,BRDI.CHANGE_ID "CHANGE ID" '||
1510 ' ,BRDI.CHANGE_TRANSACTION_TYPE "CHANGE TRANSACTION TYPE" '||
1511 ' ,BRDI.INTERFACE_TABLE_UNIQUE_ID "INTERFACE TABLE UNIQUE ID" '||
1512 ' ,BRDI.ASSEMBLY_ITEM_REVISION_CODE "ASSEMBLY ITEM REVISION CODE" '||
1513 ' ,BRDI.ASSEMBLY_ITEM_REVISION_ID "ASSEMBLY ITEM REVISION ID" '||
1514 ' from bom_ref_desgs_interface brdi where 1=1 ';
1515
1516 sqltxt :=sqltxt||' and rownum < '||row_limit;
1517 sqltxt :=sqltxt||' order by brdi.component_reference_designator ';
1518
1519 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_ref_desgs_interface table ');
1520 If (num_rows = row_limit -1 ) Then
1521 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/>');
1522 End If;
1523 statusStr := 'SUCCESS';
1524 isFatal := 'FALSE';
1525 /* End of bom_ref_desgs_interface */
1526
1527 /* SQL to fetch records frombom_sub_comps_interface table */
1528 sqltxt := 'SELECT ' ||
1529 ' BSCI.SUBSTITUTE_COMPONENT_ID "SUBSTITUTE COMPONENT ID", '||
1530 ' to_char(BSCI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1531 ' BSCI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1532 ' to_char(BSCI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1533 ' BSCI.CREATED_BY "CREATED BY", '||
1537 ' BSCI.ACD_TYPE "ACD TYPE", '||
1534 ' BSCI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1535 ' BSCI.SUBSTITUTE_ITEM_QUANTITY "SUBSTITUTE ITEM QUANTITY", '||
1536 ' BSCI.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID", '||
1538 ' BSCI.CHANGE_NOTICE "CHANGE NOTICE", '||
1539 ' BSCI.REQUEST_ID "REQUEST ID", '||
1540 ' BSCI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1541 ' BSCI.PROGRAM_ID "PROGRAM ID", '||
1542 ' to_char(BSCI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1543 ' BSCI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1544 ' BSCI.ATTRIBUTE1 "ATTRIBUTE1", '||
1545 ' BSCI.ATTRIBUTE2 "ATTRIBUTE2", '||
1546 ' BSCI.ATTRIBUTE3 "ATTRIBUTE3", '||
1547 ' BSCI.ATTRIBUTE4 "ATTRIBUTE4", '||
1548 ' BSCI.ATTRIBUTE5 "ATTRIBUTE5", '||
1549 ' BSCI.ATTRIBUTE6 "ATTRIBUTE6", '||
1550 ' BSCI.ATTRIBUTE7 "ATTRIBUTE7", '||
1551 ' BSCI.ATTRIBUTE8 "ATTRIBUTE8", '||
1552 ' BSCI.ATTRIBUTE9 "ATTRIBUTE9", '||
1553 ' BSCI.ATTRIBUTE10 "ATTRIBUTE10", '||
1554 ' BSCI.ATTRIBUTE11 "ATTRIBUTE11", '||
1555 ' BSCI.ATTRIBUTE12 "ATTRIBUTE12", '||
1556 ' BSCI.ATTRIBUTE13 "ATTRIBUTE13", '||
1557 ' BSCI.ATTRIBUTE14 "ATTRIBUTE14", '||
1558 ' BSCI.ATTRIBUTE15 "ATTRIBUTE15", '||
1559 ' BSCI.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
1560 ' BSCI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1561 ' BSCI.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR", '||
1562 ' BSCI.ORGANIZATION_ID "ORGANIZATION ID", '||
1563 ' BSCI.COMPONENT_ITEM_ID "COMPONENT ITEM ID", '||
1564 ' BSCI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1565 ' to_char(BSCI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1566 ' BSCI.TRANSACTION_ID "TRANSACTION ID", '||
1567 ' BSCI.PROCESS_FLAG "PROCESS FLAG", '||
1568 ' BSCI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1569 ' BSCI.SUBSTITUTE_COMP_NUMBER "SUBSTITUTE COMP NUMBER", '||
1570 ' BSCI.COMPONENT_ITEM_NUMBER "COMPONENT ITEM NUMBER", '||
1571 ' BSCI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1572 ' BSCI.NEW_SUB_COMP_ID "NEW SUB COMP ID", '||
1573 ' BSCI.NEW_SUB_COMP_NUMBER "NEW SUB COMP NUMBER", '||
1574 ' BSCI.INTERFACE_ENTITY_TYPE "INTERFACE ENTITY TYPE", '||
1575 ' BSCI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1576 ' BSCI.BOM_SUB_COMPS_IFCE_KEY "BOM SUB COMPS IFCE KEY", '||
1577 ' BSCI.BOM_INVENTORY_COMPS_IFCE_KEY "BOM INVENTORY COMPS IFCE KEY", '||
1578 ' BSCI.ENG_REVISED_ITEMS_IFCE_KEY "ENG REVISED ITEMS IFCE KEY", '||
1579 ' BSCI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1580 ' BSCI.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER", '||
1581 ' BSCI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1582 ' BSCI.ENFORCE_INT_REQUIREMENTS "ENFORCE INT REQUIREMENTS" '||
1583 ' ,BSCI.NEW_REVISED_ITEM_REVISION "NEW REVISED ITEM REVISION" '||
1584 ' ,BSCI.RETURN_STATUS "RETURN STATUS" '||
1585 ' ,BSCI.BATCH_ID "BATCH ID" '||
1586 ' ,BSCI.COMP_SOURCE_SYSTEM_REFERENCE "COMP SOURCE SYSTEM REFERENCE" '||
1587 ' ,BSCI.PARENT_SOURCE_SYSTEM_REFERENCE "PARENT SOURCE SYSTEM REFERENCE" '||
1588 ' ,BSCI.SUBCOM_SOURCE_SYSTEM_REFERENCE "SUBCOM SOURCE SYSTEM REFERENCE" '||
1589 ' ,BSCI.CHANGE_ID "CHANGE ID" '||
1590 ' ,BSCI.SUB_COMP_INVERSE_QUANTITY "SUB COMP INVERSE QUANTITY" '||
1591 ' ,BSCI.CHANGE_TRANSACTION_TYPE "CHANGE TRANSACTION TYPE" '||
1592 ' ,BSCI.INTERFACE_TABLE_UNIQUE_ID "INTERFACE TABLE UNIQUE ID" '||
1593 ' ,BSCI.ASSEMBLY_ITEM_REVISION_CODE "ASSEMBLY ITEM REVISION CODE" '||
1594 ' ,BSCI.ASSEMBLY_ITEM_REVISION_ID "ASSEMBLY ITEM REVISION ID" '||
1595 ' from bom_sub_comps_interface bsci where 1=1 ';
1596
1597 sqltxt :=sqltxt||' and rownum < '||row_limit;
1598 sqltxt :=sqltxt||' order by bsci.substitute_component_id ';
1599
1600 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_sub_comps_interface table ');
1601 If (num_rows = row_limit -1 ) Then
1602 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/>');
1603 End If;
1604 statusStr := 'SUCCESS';
1605 isFatal := 'FALSE';
1606 /* End of bom_sub_comps_interface */
1607
1608 /* SQL to fetch records from bom_component_ops_interface table */
1609 sqltxt := 'SELECT ' ||
1610 ' BCOI.COMP_OPERATION_SEQ_ID "COMP OPERATION SEQ ID", '||
1611 ' BCOI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1612 ' BCOI.ADDITIONAL_OPERATION_SEQ_NUM "ADDITIONAL OPERATION SEQ NUM", '||
1613 ' BCOI.NEW_ADDITIONAL_OP_SEQ_NUM "NEW ADDITIONAL OP SEQ NUM", '||
1614 ' BCOI.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
1615 ' to_char(BCOI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1616 ' BCOI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1617 ' to_char(BCOI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1618 ' BCOI.CREATED_BY "CREATED BY", '||
1619 ' BCOI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1620 ' BCOI.REQUEST_ID "REQUEST ID", '||
1621 ' to_char(BCOI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1622 ' BCOI.PROGRAM_ID "PROGRAM ID", '||
1623 ' BCOI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1624 ' BCOI.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID", '||
1625 ' BCOI.BILL_SEQUENCE_ID "BILL SEQUENCE ID", '||
1626 ' BCOI.CONSUMING_OPERATION_FLAG "CONSUMING OPERATION FLAG", '||
1627 ' BCOI.CONSUMPTION_QUANTITY "CONSUMPTION QUANTITY", '||
1628 ' BCOI.SUPPLY_SUBINVENTORY "SUPPLY SUBINVENTORY", '||
1632 ' BCOI.ATTRIBUTE1 "ATTRIBUTE1", '||
1629 ' BCOI.SUPPLY_LOCATOR_ID "SUPPLY LOCATOR ID", '||
1630 ' BCOI.WIP_SUPPLY_TYPE "WIP SUPPLY TYPE", '||
1631 ' BCOI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1633 ' BCOI.ATTRIBUTE2 "ATTRIBUTE2", '||
1634 ' BCOI.ATTRIBUTE3 "ATTRIBUTE3", '||
1635 ' BCOI.ATTRIBUTE4 "ATTRIBUTE4", '||
1636 ' BCOI.ATTRIBUTE5 "ATTRIBUTE5", '||
1637 ' BCOI.ATTRIBUTE6 "ATTRIBUTE6", '||
1638 ' BCOI.ATTRIBUTE7 "ATTRIBUTE7", '||
1639 ' BCOI.ATTRIBUTE8 "ATTRIBUTE8", '||
1640 ' BCOI.ATTRIBUTE9 "ATTRIBUTE9", '||
1641 ' BCOI.ATTRIBUTE10 "ATTRIBUTE10", '||
1642 ' BCOI.ATTRIBUTE11 "ATTRIBUTE11", '||
1643 ' BCOI.ATTRIBUTE12 "ATTRIBUTE12", '||
1644 ' BCOI.ATTRIBUTE13 "ATTRIBUTE13", '||
1645 ' BCOI.ATTRIBUTE14 "ATTRIBUTE14", '||
1646 ' BCOI.ATTRIBUTE15 "ATTRIBUTE15", '||
1647 ' BCOI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1648 ' BCOI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1649 ' BCOI.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR", '||
1650 ' BCOI.ORGANIZATION_ID "ORGANIZATION ID", '||
1651 ' BCOI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1652 ' BCOI.COMPONENT_ITEM_ID "COMPONENT ITEM ID", '||
1653 ' BCOI.COMPONENT_ITEM_NUMBER "COMPONENT ITEM NUMBER", '||
1654 ' to_char(BCOI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1655 ' BCOI.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER", '||
1656 ' BCOI.TO_END_ITEM_UNIT_NUMBER "TO END ITEM UNIT NUMBER", '||
1657 ' BCOI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1658 ' BCOI.TRANSACTION_ID "TRANSACTION ID", '||
1659 ' BCOI.PROCESS_FLAG "PROCESS FLAG", '||
1660 ' BCOI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1661 ' BCOI.RETURN_STATUS "RETURN STATUS", '||
1662 ' BCOI.BATCH_ID "BATCH ID", '||
1663 ' BCOI.COMP_SOURCE_SYSTEM_REFERENCE "COMP SOURCE SYSTEM REFERENCE", '||
1664 ' BCOI.PARENT_SOURCE_SYSTEM_REFERENCE "PARENT SOURCE SYSTEM REFERENCE", '||
1665 ' BCOI.ASSEMBLY_ITEM_REVISION_CODE "ASSEMBLY ITEM REVISION CODE", '||
1666 ' BCOI.ASSEMBLY_ITEM_REVISION_ID "ASSEMBLY ITEM REVISION ID" '||
1667 ' from bom_component_ops_interface bcoi where 1=1 ';
1668
1669 sqltxt :=sqltxt||' and rownum < '||row_limit;
1670 sqltxt :=sqltxt||' order by bcoi.comp_operation_seq_id ';
1671
1672 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_component_ops_interface table ');
1673 If (num_rows = row_limit -1 ) Then
1674 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/>');
1675 End If;
1676 statusStr := 'SUCCESS';
1677 isFatal := 'FALSE';
1678 /* End of bom_component_ops_interface */
1679
1680 End If; /* End of tables common to bom and eng */
1681
1682
1683 If upper(l_type) in ('RTG','ENG') Then
1684 /* Fetch tables common to rtg and eng */
1685
1686 /* Get the application installation info. References to Data Dictionary Objects without schema name
1687 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
1688 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
1689
1690 l_ret_status := fnd_installation.get_app_info ('BOM'
1691 , l_status
1692 , l_industry
1693 , l_oracle_schema
1694 );
1695
1696 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
1697
1698 /* SQL to fetch records from bom_op_sequences_interface table */
1699 sqltxt := 'SELECT ' ||
1700 ' BOSI.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
1701 ' BOSI.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
1702 ' BOSI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1703 ' to_char(BOSI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1704 ' BOSI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1705 ' to_char(BOSI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1706 ' BOSI.CREATED_BY "CREATED BY", '||
1707 ' BOSI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1708 ' BOSI.STANDARD_OPERATION_ID "STANDARD OPERATION ID", '||
1709 ' BOSI.DEPARTMENT_ID "DEPARTMENT ID", '||
1710 ' BOSI.OPERATION_LEAD_TIME_PERCENT "OPERATION LEAD TIME PERCENT", '||
1711 ' BOSI.RUN_TIME_OVERLAP_PERCENT "RUN TIME OVERLAP PERCENT", '||
1712 ' BOSI.MINIMUM_TRANSFER_QUANTITY "MINIMUM TRANSFER QUANTITY", '||
1713 ' BOSI.COUNT_POINT_TYPE "COUNT POINT TYPE", '||
1714 ' BOSI.OPERATION_DESCRIPTION "OPERATION DESCRIPTION", '||
1715 ' to_char(BOSI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1716 ' BOSI.CHANGE_NOTICE "CHANGE NOTICE", '||
1717 ' to_char(BOSI.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE", '||
1718 ' to_char(BOSI.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE", '||
1719 ' BOSI.BACKFLUSH_FLAG "BACKFLUSH FLAG", '||
1720 ' BOSI.OPTION_DEPENDENT_FLAG "OPTION DEPENDENT FLAG", '||
1721 ' BOSI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1722 ' BOSI.ATTRIBUTE1 "ATTRIBUTE1", '||
1723 ' BOSI.ATTRIBUTE2 "ATTRIBUTE2", '||
1724 ' BOSI.ATTRIBUTE3 "ATTRIBUTE3", '||
1725 ' BOSI.ATTRIBUTE4 "ATTRIBUTE4", '||
1726 ' BOSI.ATTRIBUTE5 "ATTRIBUTE5", '||
1727 ' BOSI.ATTRIBUTE6 "ATTRIBUTE6", '||
1728 ' BOSI.ATTRIBUTE7 "ATTRIBUTE7", '||
1729 ' BOSI.ATTRIBUTE8 "ATTRIBUTE8", '||
1733 ' BOSI.ATTRIBUTE12 "ATTRIBUTE12", '||
1730 ' BOSI.ATTRIBUTE9 "ATTRIBUTE9", '||
1731 ' BOSI.ATTRIBUTE10 "ATTRIBUTE10", '||
1732 ' BOSI.ATTRIBUTE11 "ATTRIBUTE11", '||
1734 ' BOSI.ATTRIBUTE13 "ATTRIBUTE13", '||
1735 ' BOSI.ATTRIBUTE14 "ATTRIBUTE14", '||
1736 ' BOSI.ATTRIBUTE15 "ATTRIBUTE15", '||
1737 ' BOSI.REQUEST_ID "REQUEST ID", '||
1738 ' BOSI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1739 ' BOSI.PROGRAM_ID "PROGRAM ID", '||
1740 ' to_char(BOSI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1741 ' BOSI.MODEL_OP_SEQ_ID "MODEL OP SEQ ID", '||
1742 ' BOSI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1743 ' BOSI.ORGANIZATION_ID "ORGANIZATION ID", '||
1744 ' BOSI.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
1745 ' BOSI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1746 ' BOSI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1747 ' BOSI.DEPARTMENT_CODE "DEPARTMENT CODE", '||
1748 ' BOSI.OPERATION_CODE "OPERATION CODE", '||
1749 ' BOSI.RESOURCE_ID1 "RESOURCE ID1", '||
1750 ' BOSI.RESOURCE_ID2 "RESOURCE ID2", '||
1751 ' BOSI.RESOURCE_ID3 "RESOURCE ID3", '||
1752 ' BOSI.RESOURCE_CODE1 "RESOURCE CODE1", '||
1753 ' BOSI.RESOURCE_CODE2 "RESOURCE CODE2", '||
1754 ' BOSI.RESOURCE_CODE3 "RESOURCE CODE3", '||
1755 ' BOSI.INSTRUCTION_CODE1 "INSTRUCTION CODE1", '||
1756 ' BOSI.INSTRUCTION_CODE2 "INSTRUCTION CODE2", '||
1757 ' BOSI.INSTRUCTION_CODE3 "INSTRUCTION CODE3", '||
1758 ' BOSI.TRANSACTION_ID "TRANSACTION ID", '||
1759 ' BOSI.PROCESS_FLAG "PROCESS FLAG", '||
1760 ' BOSI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1761 ' BOSI.NEW_OPERATION_SEQ_NUM "NEW OPERATION SEQ NUM", '||
1762 ' to_char(BOSI.NEW_EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "NEW EFFECTIVITY DATE", '||
1763 ' BOSI.ASSEMBLY_TYPE "ASSEMBLY TYPE", '||
1764 ' BOSI.OPERATION_TYPE "OPERATION TYPE", '||
1765 ' BOSI.REFERENCE_FLAG "REFERENCE FLAG", '||
1766 ' BOSI.PROCESS_OP_SEQ_ID "PROCESS OP SEQ ID", '||
1767 ' BOSI.LINE_OP_SEQ_ID "LINE OP SEQ ID", '||
1768 ' BOSI.YIELD "YIELD", '||
1769 ' BOSI.CUMULATIVE_YIELD "CUMULATIVE YIELD", '||
1770 ' BOSI.REVERSE_CUMULATIVE_YIELD "REVERSE CUMULATIVE YIELD", '||
1771 ' BOSI.LABOR_TIME_CALC "LABOR TIME CALC", '||
1772 ' BOSI.MACHINE_TIME_CALC "MACHINE TIME CALC", '||
1773 ' BOSI.TOTAL_TIME_CALC "TOTAL TIME CALC", '||
1774 ' BOSI.LABOR_TIME_USER "LABOR TIME USER", '||
1775 ' BOSI.MACHINE_TIME_USER "MACHINE TIME USER", '||
1776 ' BOSI.TOTAL_TIME_USER "TOTAL TIME USER", '||
1777 ' BOSI.NET_PLANNING_PERCENT "NET PLANNING PERCENT", '||
1778 ' BOSI.INCLUDE_IN_ROLLUP "INCLUDE IN ROLLUP", '||
1779 ' BOSI.OPERATION_YIELD_ENABLED "OPERATION YIELD ENABLED", '||
1780 ' BOSI.PROCESS_SEQ_NUMBER "PROCESS SEQ NUMBER", '||
1781 ' BOSI.PROCESS_CODE "PROCESS CODE", '||
1782 ' BOSI.LINE_OP_SEQ_NUMBER "LINE OP SEQ NUMBER", '||
1783 ' BOSI.LINE_OP_CODE "LINE OP CODE", '||
1784 ' BOSI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1785 ' BOSI.SHUTDOWN_TYPE "SHUTDOWN TYPE", '||
1786 ' BOSI.LONG_DESCRIPTION "LONG DESCRIPTION", '||
1787 ' BOSI.DELETE_GROUP_NAME "DELETE GROUP NAME", '||
1788 ' BOSI.DG_DESCRIPTION "DG DESCRIPTION", '||
1789 ' BOSI.NEW_ROUTING_REVISION "NEW ROUTING REVISION", '||
1790 ' BOSI.ACD_TYPE "ACD TYPE", '||
1791 ' to_char(BOSI.OLD_START_EFFECTIVE_DATE,''DD-MON-YYYY HH24:MI:SS'') "OLD START EFFECTIVE DATE", '||
1792 ' BOSI.CANCEL_COMMENTS "CANCEL COMMENTS", '||
1793 ' BOSI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1794 ' BOSI.ENG_REVISED_ITEMS_IFCE_KEY "ENG REVISED ITEMS IFCE KEY", '||
1795 ' BOSI.BOM_REV_OP_IFCE_KEY "BOM REV OP IFCE KEY" '||
1796 ' ,BOSI.NEW_REVISED_ITEM_REVISION "NEW REVISED ITEM REVISION" '||
1797 ' ,BOSI.BATCH_ID "BATCH ID" '||
1798 ' from bom_op_sequences_interface bosi where 1=1 ';
1799
1800 sqltxt :=sqltxt||' and rownum < '||row_limit;
1801 sqltxt :=sqltxt||' order by bosi.operation_sequence_id ';
1802
1803 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_op_sequences_interface table ');
1804 If (num_rows = row_limit -1 ) Then
1805 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/>');
1806 End If;
1807 statusStr := 'SUCCESS';
1808 isFatal := 'FALSE';
1809
1810 /* End of bom_op_sequences_interface */
1811
1812 /* SQL to fetch records from bom_op_resources_interface bori table */
1813 sqltxt := 'SELECT ' ||
1814 ' BORI.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
1815 ' BORI.RESOURCE_SEQ_NUM "RESOURCE SEQ NUM", '||
1816 ' BORI.RESOURCE_ID "RESOURCE ID", '||
1817 ' BORI.ACTIVITY_ID "ACTIVITY ID", '||
1818 ' BORI.STANDARD_RATE_FLAG "STANDARD RATE FLAG", '||
1819 ' BORI.ASSIGNED_UNITS "ASSIGNED UNITS", '||
1820 ' BORI.USAGE_RATE_OR_AMOUNT "USAGE RATE OR AMOUNT", '||
1821 ' BORI.USAGE_RATE_OR_AMOUNT_INVERSE "USAGE RATE OR AMOUNT INVERSE", '||
1822 ' BORI.BASIS_TYPE "BASIS TYPE", '||
1823 ' BORI.SCHEDULE_FLAG "SCHEDULE FLAG", '||
1827 ' BORI.CREATED_BY "CREATED BY", '||
1824 ' to_char(BORI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1825 ' BORI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1826 ' to_char(BORI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1828 ' BORI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1829 ' BORI.RESOURCE_OFFSET_PERCENT "RESOURCE OFFSET PERCENT", '||
1830 ' BORI.AUTOCHARGE_TYPE "AUTOCHARGE TYPE", '||
1831 ' BORI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1832 ' BORI.ATTRIBUTE1 "ATTRIBUTE1", '||
1833 ' BORI.ATTRIBUTE2 "ATTRIBUTE2", '||
1834 ' BORI.ATTRIBUTE3 "ATTRIBUTE3", '||
1835 ' BORI.ATTRIBUTE4 "ATTRIBUTE4", '||
1836 ' BORI.ATTRIBUTE5 "ATTRIBUTE5", '||
1837 ' BORI.ATTRIBUTE6 "ATTRIBUTE6", '||
1838 ' BORI.ATTRIBUTE7 "ATTRIBUTE7", '||
1839 ' BORI.ATTRIBUTE8 "ATTRIBUTE8", '||
1840 ' BORI.ATTRIBUTE9 "ATTRIBUTE9", '||
1841 ' BORI.ATTRIBUTE10 "ATTRIBUTE10", '||
1842 ' BORI.ATTRIBUTE11 "ATTRIBUTE11", '||
1843 ' BORI.ATTRIBUTE12 "ATTRIBUTE12", '||
1844 ' BORI.ATTRIBUTE13 "ATTRIBUTE13", '||
1845 ' BORI.ATTRIBUTE14 "ATTRIBUTE14", '||
1846 ' BORI.ATTRIBUTE15 "ATTRIBUTE15", '||
1847 ' BORI.REQUEST_ID "REQUEST ID", '||
1848 ' BORI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1849 ' BORI.PROGRAM_ID "PROGRAM ID", '||
1850 ' to_char(BORI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE",'||
1851 ' BORI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1852 ' BORI.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
1853 ' BORI.ORGANIZATION_ID "ORGANIZATION ID", '||
1854 ' BORI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1855 ' to_char(BORI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1856 ' BORI.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
1857 ' BORI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1858 ' BORI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1859 ' BORI.RESOURCE_CODE "RESOURCE CODE", '||
1860 ' BORI.ACTIVITY "ACTIVITY", '||
1861 ' BORI.TRANSACTION_ID "TRANSACTION ID", '||
1862 ' BORI.PROCESS_FLAG "PROCESS FLAG", '||
1863 ' BORI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1864 ' BORI.NEW_RESOURCE_SEQ_NUM "NEW RESOURCE SEQ NUM", '||
1865 ' BORI.OPERATION_TYPE "OPERATION TYPE", '||
1866 ' BORI.PRINCIPLE_FLAG "PRINCIPLE FLAG", '||
1867 ' BORI.SCHEDULE_SEQ_NUM "SCHEDULE SEQ NUM", '||
1868 ' BORI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1869 ' BORI.SETUP_CODE "SETUP CODE", '||
1870 ' BORI.ECO_NAME "ECO NAME", '||
1871 ' BORI.NEW_ROUTING_REVISION "NEW ROUTING REVISION", '||
1872 ' BORI.ACD_TYPE "ACD TYPE", '||
1873 ' BORI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY", '||
1874 ' BORI.ENG_REVISED_ITEMS_IFCE_KEY "ENG REVISED ITEMS IFCE KEY", '||
1875 ' BORI.BOM_REV_OP_IFCE_KEY "BOM REV OP IFCE KEY", '||
1876 ' BORI.BOM_REV_OP_RES_IFCE_KEY "BOM REV OP RES IFCE KEY" '||
1877 ' ,BORI.NEW_REVISED_ITEM_REVISION "NEW REVISED ITEM REVISION" '||
1878 ' ,BORI.SUBSTITUTE_GROUP_NUM "SUBSTITUTE GROUP NUM" '||
1879 ' ,BORI.BATCH_ID "BATCH ID" '||
1880 ' from bom_op_resources_interface bori where 1=1 ';
1881
1882 sqltxt :=sqltxt||' and rownum < '||row_limit;
1883 sqltxt :=sqltxt||' order by bori.operation_sequence_id,bori.resource_seq_num ';
1884
1885 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_op_resources_interface table ');
1886 If (num_rows = row_limit -1 ) Then
1887 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/>');
1888 End If;
1889 statusStr := 'SUCCESS';
1890 isFatal := 'FALSE';
1891
1892 /* End of bom_op_resources_interface */
1893
1894 /* SQL to fetch records from bom_sub_op_resources_interface table */
1895 sqltxt := 'SELECT ' ||
1896 ' BSORI.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
1897 ' BSORI.SUBSTITUTE_GROUP_NUM "SUBSTITUTE GROUP NUM", '||
1898 ' BSORI.SCHEDULE_SEQ_NUM "SCHEDULE SEQ NUM", '||
1899 ' BSORI.REPLACEMENT_GROUP_NUM "REPLACEMENT GROUP NUM", '||
1900 ' BSORI.RESOURCE_ID "RESOURCE ID", '||
1901 ' BSORI.ACTIVITY_ID "ACTIVITY ID", '||
1902 ' BSORI.OPERATION_TYPE "OPERATION TYPE", '||
1903 ' BSORI.STANDARD_RATE_FLAG "STANDARD RATE FLAG", '||
1904 ' BSORI.ASSIGNED_UNITS "ASSIGNED UNITS", '||
1905 ' BSORI.USAGE_RATE_OR_AMOUNT "USAGE RATE OR AMOUNT", '||
1906 ' BSORI.USAGE_RATE_OR_AMOUNT_INVERSE "USAGE RATE OR AMOUNT INVERSE", '||
1907 ' BSORI.BASIS_TYPE "BASIS TYPE", '||
1908 ' BSORI.SCHEDULE_FLAG "SCHEDULE FLAG", '||
1909 ' to_char(BSORI.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1910 ' BSORI.LAST_UPDATED_BY "LAST UPDATED BY", '||
1911 ' to_char(BSORI.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1912 ' BSORI.CREATED_BY "CREATED BY", '||
1913 ' BSORI.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1914 ' BSORI.RESOURCE_OFFSET_PERCENT "RESOURCE OFFSET PERCENT", '||
1915 ' BSORI.AUTOCHARGE_TYPE "AUTOCHARGE TYPE", '||
1916 ' BSORI.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1917 ' BSORI.ATTRIBUTE1 "ATTRIBUTE1", '||
1918 ' BSORI.ATTRIBUTE2 "ATTRIBUTE2", '||
1919 ' BSORI.ATTRIBUTE3 "ATTRIBUTE3", '||
1920 ' BSORI.ATTRIBUTE4 "ATTRIBUTE4", '||
1921 ' BSORI.ATTRIBUTE5 "ATTRIBUTE5", '||
1922 ' BSORI.ATTRIBUTE6 "ATTRIBUTE6", '||
1926 ' BSORI.ATTRIBUTE10 "ATTRIBUTE10", '||
1923 ' BSORI.ATTRIBUTE7 "ATTRIBUTE7", '||
1924 ' BSORI.ATTRIBUTE8 "ATTRIBUTE8", '||
1925 ' BSORI.ATTRIBUTE9 "ATTRIBUTE9", '||
1927 ' BSORI.ATTRIBUTE11 "ATTRIBUTE11", '||
1928 ' BSORI.ATTRIBUTE12 "ATTRIBUTE12", '||
1929 ' BSORI.ATTRIBUTE13 "ATTRIBUTE13", '||
1930 ' BSORI.ATTRIBUTE14 "ATTRIBUTE14", '||
1931 ' BSORI.ATTRIBUTE15 "ATTRIBUTE15", '||
1932 ' BSORI.REQUEST_ID "REQUEST ID", '||
1933 ' BSORI.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1934 ' BSORI.PROGRAM_ID "PROGRAM ID", '||
1935 ' to_char(BSORI.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1936 ' BSORI.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1937 ' BSORI.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
1938 ' BSORI.ORGANIZATION_ID "ORGANIZATION ID", '||
1939 ' BSORI.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
1940 ' to_char(BSORI.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
1941 ' BSORI.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
1942 ' BSORI.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1943 ' BSORI.ASSEMBLY_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1944 ' BSORI.SUB_RESOURCE_CODE "SUB RESOURCE CODE", '||
1945 ' BSORI.ACTIVITY "ACTIVITY", '||
1946 ' BSORI.TRANSACTION_ID "TRANSACTION ID", '||
1947 ' BSORI.PROCESS_FLAG "PROCESS FLAG", '||
1948 ' BSORI.TRANSACTION_TYPE "TRANSACTION TYPE", '||
1949 ' BSORI.NEW_SUB_RESOURCE_CODE "NEW SUB RESOURCE CODE", '||
1950 ' BSORI.PRINCIPLE_FLAG "PRINCIPLE FLAG", '||
1951 ' BSORI.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
1952 ' BSORI.SETUP_CODE "SETUP CODE", '||
1953 ' BSORI.ECO_NAME "ECO NAME", '||
1954 ' BSORI.NEW_REVISED_ITEM_REVISION "NEW REVISED ITEM REVISION", '||
1955 ' BSORI.NEW_ROUTING_REVISION "NEW ROUTING REVISION", '||
1956 ' BSORI.ACD_TYPE "ACD TYPE" '||
1957 ' ,BSORI.NEW_REPLACEMENT_GROUP_NUM "NEW REPLACEMENT GROUP NUM" '||
1958 ' ,BSORI.ENG_CHANGES_IFCE_KEY "ENG CHANGES IFCE KEY" '||
1959 ' ,BSORI.BATCH_ID "BATCH ID" '||
1960 ' ,BSORI.NEW_BASIS_TYPE "NEW BASIS TYPE" '||
1961 ' from bom_sub_op_resources_interface bsori where 1=1 ';
1962
1963 sqltxt :=sqltxt||' and rownum < '||row_limit;
1964 sqltxt :=sqltxt||' order by bsori.operation_sequence_id,bsori.substitute_group_num, '||
1965 ' bsori.schedule_seq_num, bsori.replacement_group_num, bsori.resource_id ';
1966
1967 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in bom_sub_op_resources_interface table ');
1968 If (num_rows = row_limit -1 ) Then
1969 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/>');
1970 End If;
1971 statusStr := 'SUCCESS';
1972 isFatal := 'FALSE';
1973 /* End of bom_sub_op_resources_interface */
1974
1975 End If; /* End of tables common to rtg and eng */
1976
1977 End If; /* end of l_type in either in inv or (bom/rtg/eng) */
1978
1979 /* SQL to fetch records from mtl_interface_errors table.*/
1980 sqltxt := 'SELECT ' ||
1981 ' MIE.ORGANIZATION_ID "ORGANIZATION ID", '||
1982 ' MIE.TRANSACTION_ID "TRANSACTION ID", '||
1983 ' MIE.UNIQUE_ID "UNIQUE ID", '||
1984 ' to_char(MIE.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE",'||
1985 ' MIE.LAST_UPDATED_BY "LAST UPDATED BY", '||
1986 ' to_char(MIE.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1987 ' MIE.CREATED_BY "CREATED BY", '||
1988 ' MIE.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1989 ' MIE.TABLE_NAME "TABLE NAME", '||
1990 ' MIE.MESSAGE_NAME "MESSAGE NAME", '||
1991 ' MIE.COLUMN_NAME "COLUMN NAME", '||
1992 ' MIE.REQUEST_ID "REQUEST ID", '||
1993 ' MIE.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1994 ' MIE.PROGRAM_ID "PROGRAM ID", '||
1995 ' to_char(MIE.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
1996 ' MIE.ERROR_MESSAGE "ERROR MESSAGE", '||
1997 ' MIE.ENTITY_IDENTIFIER "ENTITY IDENTIFIER", '||
1998 ' MIE.BO_IDENTIFIER "BO IDENTIFIER" , '||
1999 ' MIE.MESSAGE_TYPE "MESSAGE TYPE" '||
2000 ' from mtl_interface_errors mie where 1=1 ';
2001
2002 sqltxt :=sqltxt||' and rownum < '||row_limit;
2003 sqltxt :=sqltxt||' order by mie.organization_id,mie.transaction_id, mie.unique_id ';
2004
2005 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Records in mtl_interface_errors table ');
2006 If (num_rows = row_limit -1 ) Then
2007 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/>');
2008 End If;
2009 statusStr := 'SUCCESS';
2010 isFatal := 'FALSE';
2011
2012 /* End of mtl_interface_errors table.*/
2013
2014 <<l_test_end>>
2015 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This data collection script completed as expected <BR/>');
2016 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
2017 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
2018 End If; /* l_type is valid */
2019
2020 EXCEPTION
2021 when others then
2022 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
2023 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
2024 statusStr := 'FAILURE';
2025 errStr := sqlerrm ||' occurred in script. ';
2026 fixInfo := 'Unexpected Exception in BOMDGINB.pls';
2027 isFatal := 'FALSE';
2028 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
2032 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
2029 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
2030 END runTest;
2031
2033 BEGIN
2034 name := 'Items/Bills/Routings Interface Data Collection';
2035 END getComponentName;
2036
2037 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
2038 BEGIN
2039 descStr := ' <pre>This data collection script collects data about Items/Bills/Routings Interface Details. <BR/> '||
2040 ' Type is a mandatory Input field. <BR/>'||
2041 ' Note: Please truncate the following interface tables before doing the test cases. <BR/>'||
2042 ' Then load the test records into the requisite import tables.<BR/>'||
2043 ' For INV - mtl_system_items_interface, mtl_item_revisions_interface,<BR/>'||
2044 ' mtl_rtg_item_revs_interface, mtl_item_categories_interface <BR/>'||
2045 ' For BOM - bom_bill_of_mtls_interface,bom_inventory_comps_interface,bom_ref_desgs_interface,<BR/>'||
2046 ' bom_sub_comps_interface,bom_component_ops_interface<BR/>'||
2047 ' For RTG - bom_op_routings_interface, bom_op_sequences_interface, bom_op_resources_interface,<BR/>'||
2048 ' bom_sub_op_resources_interface, bom_op_networks_interface <BR/>'||
2049 ' Please truncate mtl_interface_errors table for all the cases.<BR/>'||
2050 ' Please run this script before and after Import process.</pre>';
2051 /* ' For ENG - eng_eng_changes_interface, eng_revised_items_interface, eng_eco_revisions_interface<BR/>'|| */
2052 END getTestDesc;
2053
2054 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
2055 BEGIN
2056 name := 'Items/Bills/Routings Interface Data Collection';
2057 END getTestName;
2058
2059 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
2060 tempDependencies JTF_DIAG_DEPENDTBL;
2061
2062 BEGIN
2063 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
2064 END getDependencies;
2065
2066 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
2067 BEGIN
2068 str := 'FALSE';
2069 END isDependencyPipelined;
2070
2071
2072 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
2073 tempOutput JTF_DIAG_OUTPUTTBL;
2074 BEGIN
2075 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
2076 outputValues := tempOutput;
2077 EXCEPTION
2078 when others then
2079 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
2080 END getOutputValues;
2081
2082
2083 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
2084 tempInput JTF_DIAG_INPUTTBL;
2085 BEGIN
2086 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
2087 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'Type','LOV-oracle.apps.bom.diag.lov.InterfaceRecLov');-- Lov name modified to Type for bug 6412260
2088 defaultInputValues := tempInput;
2089 EXCEPTION
2090 when others then
2091 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
2092 END getDefaultTestParams;
2093
2094 Function getTestMode return INTEGER IS
2095 BEGIN
2096 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
2097
2098 END getTestMode;
2099
2100 END BOM_DIAGUNITTEST_INTFDATA;