[Home] [Help]
PACKAGE BODY: APPS.BOM_DIAGUNITTEST_IBRDATA
Source
1 package body BOM_DIAGUNITTEST_IBRDATA as
2 /* $Header: BOMDGIBB.pls 120.1 2007/12/26 09:52:38 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_count NUMBER;
29 l_ret_status BOOLEAN;
30 l_status VARCHAR2 (1);
31 l_industry VARCHAR2 (1);
32 l_oracle_schema VARCHAR2 (30);
33
34 CURSOR c_item_valid (cp_n_item_id IN NUMBER, cp_n_org_id IN NUMBER) IS
35 SELECT count(*)
36 FROM mtl_system_items_b
37 WHERE inventory_item_id = cp_n_item_id
38 AND organization_id = nvl(cp_n_org_id,organization_id);
39
40
41 BEGIN
42 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
43 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
44 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
45
46 /*Initializing local vars */
47 row_limit :=1000; /* Set Row Limit to 1000 (i.e.) Max Number of records to be fetched by each sql*/
48 l_count := 0;
49
50 -- accept input
51 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
52 l_item_id :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('ItemId',inputs);
53
54 If l_item_id is NULL then
55 JTF_DIAGNOSTIC_COREAPI.errorprint('Input Item Id is mandatory.');
56 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(' Please provide a valid value for the Item Id.');
57 statusStr := 'FAILURE';
58 isFatal := 'TRUE';
59 fixInfo := ' Please review the error message below and take corrective action. ';
60 errStr := ' Invalid value for input field Item Id. It is a mandatory input.';
61
62 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
63 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
64 Return;
65 Else /* l_item_id is not null */
66 OPEN c_item_valid (l_item_id, l_org_id);
67 FETCH c_item_valid INTO l_count;
68 CLOSE c_item_valid;
69
70 IF (l_count IS NULL) OR (l_count = 0) THEN
71 JTF_DIAGNOSTIC_COREAPI.errorprint('Invalid Item and Organization Combination');
72 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please enter right combination of Item and Organization ');
73 statusStr := 'FAILURE';
74 errStr := 'Invalid Item and Organization Combination';
75 fixInfo := ' Please review the error message below and take corrective action. ';
76 isFatal := 'TRUE';
77 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
78 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
79 RETURN;
80 END IF;
81 End If; /* l_item_id is null */
82
83
84 /* Get the application installation info. References to Data Dictionary Objects without schema name
85 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
86 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
87
88 l_ret_status := fnd_installation.get_app_info ('INV'
89 , l_status
90 , l_industry
91 , l_oracle_schema
92 );
93
94 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
95
96
97 /* Start the diagnostic test scripts */
98 sqltxt := 'SELECT '||
99 ' MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
100 ' ,MSIB.INVENTORY_ITEM_ID "Item ID" '||
101 ' ,MP.ORGANIZATION_CODE "Org Code" '||
102 ' ,MSIB.ORGANIZATION_ID "Org Id" '||
103 ' ,to_char(MSIB.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "Last Update Date" '||
104 ' ,MSIB.LAST_UPDATED_BY "Last Updated By" '||
105 ' ,to_char(MSIB.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "Creation Date" '||
106 ' ,MSIB.CREATED_BY "Created by" '||
107 ' ,MSIB.LAST_UPDATE_LOGIN "Last Update Login" '||
108 ' ,MSIB.SUMMARY_FLAG "Summary Flag" '||
109 ' ,MSIB.ENABLED_FLAG "Enabled Flag" '||
110 ' ,to_char(MSIB.START_DATE_ACTIVE,''DD-MON-YYYY HH24:MI:SS'') "Start Date Active" '||
111 ' ,to_char(MSIB.END_DATE_ACTIVE,''DD-MON-YYYY HH24:MI:SS'') "END DATE ACTIVE" '||
112 ' ,MSIB.DESCRIPTION "DESCRIPTION" '||
113 ' ,MSIB.BUYER_ID "BUYER ID" '||
114 ' ,MSIB.ACCOUNTING_RULE_ID "ACCOUNTING RULE ID" '||
115 ' ,MSIB.INVOICING_RULE_ID "INVOICING RULE ID" '||
116 ' ,MSIB.SEGMENT1 "SEGMENT1" '||
117 ' ,MSIB.SEGMENT2 "SEGMENT2" '||
118 ' ,MSIB.SEGMENT3 "SEGMENT3" '||
119 ' ,MSIB.SEGMENT4 "SEGMENT4" '||
120 ' ,MSIB.SEGMENT5 "SEGMENT5" '||
121 ' ,MSIB.SEGMENT6 "SEGMENT6" '||
122 ' ,MSIB.SEGMENT7 "SEGMENT7" '||
123 ' ,MSIB.SEGMENT8 "SEGMENT8" '||
124 ' ,MSIB.SEGMENT9 "SEGMENT9" '||
125 ' ,MSIB.SEGMENT10 "SEGMENT10" '||
126 ' ,MSIB.SEGMENT11 "SEGMENT11" '||
127 ' ,MSIB.SEGMENT12 "SEGMENT12" '||
128 ' ,MSIB.SEGMENT13 "SEGMENT13" '||
129 ' ,MSIB.SEGMENT14 "SEGMENT14" '||
130 ' ,MSIB.SEGMENT15 "SEGMENT15" '||
131 ' ,MSIB.SEGMENT16 "SEGMENT16" '||
132 ' ,MSIB.SEGMENT17 "SEGMENT17" '||
133 ' ,MSIB.SEGMENT18 "SEGMENT18" '||
134 ' ,MSIB.SEGMENT19 "SEGMENT19" '||
135 ' ,MSIB.SEGMENT20 "SEGMENT20" '||
136 ' ,MSIB.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
137 ' ,MSIB.ATTRIBUTE1 "ATTRIBUTE1" '||
138 ' ,MSIB.ATTRIBUTE2 "ATTRIBUTE2" '||
139 ' ,MSIB.ATTRIBUTE3 "ATTRIBUTE3" '||
140 ' ,MSIB.ATTRIBUTE4 "ATTRIBUTE4" '||
141 ' ,MSIB.ATTRIBUTE5 "ATTRIBUTE5" '||
142 ' ,MSIB.ATTRIBUTE6 "ATTRIBUTE6" '||
143 ' ,MSIB.ATTRIBUTE7 "ATTRIBUTE7" '||
144 ' ,MSIB.ATTRIBUTE8 "ATTRIBUTE8" '||
145 ' ,MSIB.ATTRIBUTE9 "ATTRIBUTE9" '||
146 ' ,MSIB.ATTRIBUTE10 "ATTRIBUTE10" '||
147 ' ,MSIB.ATTRIBUTE11 "ATTRIBUTE11" '||
148 ' ,MSIB.ATTRIBUTE12 "ATTRIBUTE12" '||
149 ' ,MSIB.ATTRIBUTE13 "ATTRIBUTE13" '||
150 ' ,MSIB.ATTRIBUTE14 "ATTRIBUTE14" '||
151 ' ,MSIB.ATTRIBUTE15 "ATTRIBUTE15" '||
152 ' ,MSIB.PURCHASING_ITEM_FLAG "PURCHASING ITEM FLAG" '||
153 ' ,MSIB.SHIPPABLE_ITEM_FLAG "SHIPPABLE ITEM FLAG" '||
154 ' ,MSIB.CUSTOMER_ORDER_FLAG "CUSTOMER ORDER FLAG" '||
155 ' ,MSIB.INTERNAL_ORDER_FLAG "INTERNAL ORDER FLAG" '||
156 ' ,MSIB.SERVICE_ITEM_FLAG "SERVICE ITEM FLAG" '||
157 ' ,MSIB.INVENTORY_ITEM_FLAG "INVENTORY ITEM FLAG" '||
158 ' ,MSIB.ENG_ITEM_FLAG "ENG ITEM FLAG" '||
159 ' ,MSIB.INVENTORY_ASSET_FLAG "INVENTORY ASSET FLAG" '||
160 ' ,MSIB.PURCHASING_ENABLED_FLAG "PURCHASING ENABLED FLAG" '||
161 ' ,MSIB.CUSTOMER_ORDER_ENABLED_FLAG "CUSTOMER ORDER ENABLED FLAG" '||
162 ' ,MSIB.INTERNAL_ORDER_ENABLED_FLAG "INTERNAL ORDER ENABLED FLAG" '||
163 ' ,MSIB.SO_TRANSACTIONS_FLAG "SO TRANSACTIONS FLAG" '||
164 ' ,MSIB.MTL_TRANSACTIONS_ENABLED_FLAG "MTL TRANSACTIONS ENABLED FLAG" '||
165 ' ,MSIB.STOCK_ENABLED_FLAG "STOCK ENABLED FLAG" '||
166 ' ,MSIB.BOM_ENABLED_FLAG "BOM ENABLED FLAG" '||
167 ' ,MSIB.BUILD_IN_WIP_FLAG "BUILD IN WIP FLAG" '||
168 ' ,DECODE(MLU_RQCC.MEANING,null,null, '||
169 ' (MLU_RQCC.MEANING || '' ('' || MSIB.REVISION_QTY_CONTROL_CODE || '')'')) "REVISION QTY CONTROL CODE" '||
170 ' ,MSIB.ITEM_CATALOG_GROUP_ID "ITEM CATALOG GROUP ID" '||
171 ' ,MSIB.CATALOG_STATUS_FLAG "CATALOG STATUS FLAG" '||
172 ' ,MSIB.RETURNABLE_FLAG "RETURNABLE FLAG" '||
173 ' ,MSIB.DEFAULT_SHIPPING_ORG "DEFAULT SHIPPING ORG" '||
174 ' ,MSIB.COLLATERAL_FLAG "COLLATERAL FLAG" '||
175 ' ,MSIB.TAXABLE_FLAG "TAXABLE FLAG" '||
176 ' ,MSIB.PURCHASING_TAX_CODE "PURCHASING TAX CODE" '||
177 ' ,DECODE(PLU_QREC.DISPLAYED_FIELD,null,null, '||
178 ' (PLU_QREC.DISPLAYED_FIELD || '' ('' || MSIB.QTY_RCV_EXCEPTION_CODE || '')'')) "QTY RCV EXCEPTION CODE" '||
179 ' ,MSIB.ALLOW_ITEM_DESC_UPDATE_FLAG "ALLOW ITEM DESC UPDATE FLAG" '||
180 ' ,MSIB.INSPECTION_REQUIRED_FLAG "INSPECTION REQUIRED FLAG" '||
181 ' ,MSIB.RECEIPT_REQUIRED_FLAG "RECEIPT REQUIRED FLAG" '||
182 ' ,MSIB.MARKET_PRICE "MARKET PRICE" '||
183 ' ,MSIB.HAZARD_CLASS_ID "HAZARD CLASS ID" '||
184 ' ,MSIB.RFQ_REQUIRED_FLAG "RFQ REQUIRED FLAG" '||
185 ' ,MSIB.QTY_RCV_TOLERANCE "QTY RCV TOLERANCE" '||
186 ' ,MSIB.LIST_PRICE_PER_UNIT "LIST PRICE PER UNIT" '||
187 ' ,MSIB.UN_NUMBER_ID "UN NUMBER ID" '||
188 ' ,MSIB.PRICE_TOLERANCE_PERCENT "PRICE TOLERANCE PERCENT" '||
189 ' ,MSIB.ASSET_CATEGORY_ID "ASSET CATEGORY ID" '||
190 ' ,MSIB.ROUNDING_FACTOR "ROUNDING FACTOR" '||
191 ' ,MSIB.UNIT_OF_ISSUE "UNIT OF ISSUE" '||
192 ' ,DECODE(PLU_ESLC.DISPLAYED_FIELD,null,null, '||
193 ' (PLU_ESLC.DISPLAYED_FIELD || '' ('' || MSIB.ENFORCE_SHIP_TO_LOCATION_CODE || '')'')) "ENFORCE SHIP TO LOCATION CODE" '||
194 ' ,MSIB.ALLOW_SUBSTITUTE_RECEIPTS_FLAG "ALLOW SUBSTITUTE RECEIPTS FLAG" '||
195 ' ,MSIB.ALLOW_UNORDERED_RECEIPTS_FLAG "ALLOW UNORDERED RECEIPTS FLAG" '||
196 ' ,MSIB.ALLOW_EXPRESS_DELIVERY_FLAG "ALLOW EXPRESS DELIVERY FLAG" '||
197 ' ,MSIB.DAYS_EARLY_RECEIPT_ALLOWED "DAYS EARLY RECEIPT ALLOWED" '||
198 ' ,MSIB.DAYS_LATE_RECEIPT_ALLOWED "DAYS LATE RECEIPT ALLOWED" '||
199 ' ,DECODE(PLU_RDEC.DISPLAYED_FIELD,null,null, '||
200 ' (PLU_RDEC.DISPLAYED_FIELD || '' ('' || MSIB.RECEIPT_DAYS_EXCEPTION_CODE || '')'')) "RECEIPT DAYS EXCEPTION CODE" '||
201 ' ,MSIB.RECEIVING_ROUTING_ID "RECEIVING ROUTING ID" '||
202 ' ,MSIB.INVOICE_CLOSE_TOLERANCE "INVOICE CLOSE TOLERANCE" '||
203 ' ,MSIB.RECEIVE_CLOSE_TOLERANCE "RECEIVE CLOSE TOLERANCE" '||
204 ' FROM mtl_system_items_b msib, mtl_item_flexfields mif '||
205 ' ,mtl_parameters mp '||
206 ' ,MFG_LOOKUPS MLU_RQCC '||
207 ' ,PO_LOOKUP_CODES PLU_QREC '||
208 ' ,PO_LOOKUP_CODES PLU_ESLC '||
209 ' ,PO_LOOKUP_CODES PLU_RDEC '||
210 ' WHERE 1=1 '||
211 ' and msib.inventory_item_id= mif.inventory_item_id '||
212 ' and msib.organization_id = mif.organization_id '||
213 ' and msib.organization_id = mp.organization_id '||
214 ' and mif.organization_id = mp.organization_id '||
215 ' and msib.REVISION_QTY_CONTROL_CODE=MLU_RQCC.LOOKUP_CODE(+) AND ''MTL_ENG_QUANTITY''=MLU_RQCC.LOOKUP_TYPE(+) '||
216 ' and msib.QTY_RCV_EXCEPTION_CODE=PLU_QREC.LOOKUP_CODE(+) AND ''RECEIVING CONTROL LEVEL''=PLU_QREC.LOOKUP_TYPE(+) '||
217 ' and msib.ENFORCE_SHIP_TO_LOCATION_CODE=PLU_ESLC.LOOKUP_CODE(+) AND ''RECEVING CONTROL LEVEL''=PLU_ESLC.LOOKUP_TYPE(+) '||
218 ' and msib.RECEIPT_DAYS_EXCEPTION_CODE=PLU_RDEC.LOOKUP_CODE(+) AND ''RECEIVING CONTROL LEVEL''=PLU_RDEC.LOOKUP_TYPE(+) ';
219
220 if l_org_id is not null then
221 sqltxt :=sqltxt||' and msib.organization_id = '||l_org_id;
222 end if;
223
224 if l_item_id is not null then
225 sqltxt :=sqltxt||' and msib.inventory_item_id = '||l_item_id;
226 sqltxt :=sqltxt||' and rownum < '||row_limit;
227 sqltxt :=sqltxt||' order by mp.organization_code,mif.padded_item_number';
228 end if;
229
233 End If;
230 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Attributes');
231 If (num_rows = row_limit -1 ) Then
232 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/>');
234
235 statusStr := 'SUCCESS';
236 isFatal := 'FALSE';
237
238 sqltxt := ' SELECT ' ||
239 ' MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
240 ' ,MSIB.INVENTORY_ITEM_ID "ITEM ID" '||
241 ' ,MP.ORGANIZATION_CODE "Org Code" '||
242 ' ,MSIB.ORGANIZATION_ID "ORG ID" '||
243 ' ,MSIB.AUTO_LOT_ALPHA_PREFIX "AUTO LOT ALPHA PREFIX" '||
244 ' ,MSIB.START_AUTO_LOT_NUMBER "START AUTO LOT NUMBER" '||
245 ' ,DECODE(MLU_LOTCC.MEANING,null,null, '||
246 ' (MLU_LOTCC.MEANING || '' ('' || MSIB.LOT_CONTROL_CODE || '')'')) "LOT CONTROL CODE" '||
247 ' ,DECODE(MLU_SLC.MEANING,null,null, '||
248 ' (MLU_SLC.MEANING || '' ('' || MSIB.SHELF_LIFE_CODE || '')'')) "SHELF LIFE CODE" '||
249 ' ,MSIB.SHELF_LIFE_DAYS "SHELF LIFE DAYS" '||
250 ' ,DECODE(MLU_SNCC.MEANING,null,null, '||
251 ' (MLU_SNCC.MEANING || '' ('' || MSIB.SERIAL_NUMBER_CONTROL_CODE || '')'')) "SERIAL NUMBER CONTROL CODE" '||
252 ' ,MSIB.START_AUTO_SERIAL_NUMBER "START AUTO SERIAL NUMBER" '||
253 ' ,MSIB.AUTO_SERIAL_ALPHA_PREFIX "AUTO SERIAL ALPHA PREFIX" '||
254 ' ,DECODE(MLU_ST.MEANING,null,null, '||
255 ' (MLU_ST.MEANING || '' ('' || MSIB.SOURCE_TYPE || '')'')) "SOURCE TYPE" '||
256 ' ,MSIB.SOURCE_ORGANIZATION_ID "SOURCE ORGANIZATION ID" '||
257 ' ,MSIB.SOURCE_SUBINVENTORY "SOURCE SUBINVENTORY" '||
258 ' ,MSIB.EXPENSE_ACCOUNT "EXPENSE ACCOUNT" '||
259 ' ,MSIB.ENCUMBRANCE_ACCOUNT "ENCUMBRANCE ACCOUNT" '||
260 ' ,DECODE(MLU_RSIC.MEANING,null,null, '||
261 ' (MLU_RSIC.MEANING || '' ('' || MSIB.RESTRICT_SUBINVENTORIES_CODE || '')'')) "RESTRICT SUBINVENTORIES CODE" '||
262 ' ,MSIB.UNIT_WEIGHT "UNIT WEIGHT" '||
263 ' ,MSIB.WEIGHT_UOM_CODE "WEIGHT UOM CODE" '||
264 ' ,MSIB.VOLUME_UOM_CODE "VOLUME UOM CODE" '||
265 ' ,MSIB.UNIT_VOLUME "UNIT VOLUME" '||
266 ' ,DECODE(MLU_RLC.MEANING,null,null, '||
267 ' (MLU_RLC.MEANING || '' ('' || MSIB.RESTRICT_LOCATORS_CODE || '')'')) "RESTRICT LOCATORS CODE" '||
268 ' ,DECODE(MLU_LCC.MEANING,null,null, '||
269 ' (MLU_LCC.MEANING || '' ('' || MSIB.LOCATION_CONTROL_CODE || '')'')) "LOCATION CONTROL CODE" '||
270 ' ,MSIB.SHRINKAGE_RATE "SHRINKAGE RATE" '||
271 ' ,MSIB.ACCEPTABLE_EARLY_DAYS "ACCEPTABLE EARLY DAYS" '||
272 ' ,DECODE(MLU_PTFC.MEANING,null,null, '||
273 ' (MLU_PTFC.MEANING || '' ('' || MSIB.PLANNING_TIME_FENCE_CODE || '')'')) "PLANNING TIME FENCE CODE" '||
274 ' ,DECODE(MLU_DTFC.MEANING,null,null, '||
275 ' (MLU_DTFC.MEANING || '' ('' || MSIB.DEMAND_TIME_FENCE_CODE || '')'')) "DEMAND TIME FENCE CODE" '||
276 ' ,MSIB.LEAD_TIME_LOT_SIZE "LEAD TIME LOT SIZE" '||
277 ' ,MSIB.STD_LOT_SIZE "STD LOT SIZE" '||
278 ' ,MSIB.CUM_MANUFACTURING_LEAD_TIME "CUM MANUFACTURING LEAD TIME" '||
279 ' ,MSIB.OVERRUN_PERCENTAGE "OVERRUN PERCENTAGE" '||
280 ' ,MSIB.MRP_CALCULATE_ATP_FLAG "MRP CALCULATE ATP FLAG" '||
281 ' ,MSIB.ACCEPTABLE_RATE_INCREASE "ACCEPTABLE RATE INCREASE" '||
282 ' ,MSIB.ACCEPTABLE_RATE_DECREASE "ACCEPTABLE RATE DECREASE" '||
283 ' ,MSIB.CUMULATIVE_TOTAL_LEAD_TIME "CUMULATIVE TOTAL LEAD TIME" '||
284 ' ,MSIB.PLANNING_TIME_FENCE_DAYS "PLANNING TIME FENCE DAYS" '||
285 ' ,MSIB.DEMAND_TIME_FENCE_DAYS "DEMAND TIME FENCE DAYS" '||
286 ' ,DECODE(FLU_EAPF.MEANING,null,null, '||
287 ' (FLU_EAPF.MEANING || '' ('' || MSIB.END_ASSEMBLY_PEGGING_FLAG || '')'')) "END ASSEMBLY PEGGING FLAG" '||
288 ' ,MSIB.REPETITIVE_PLANNING_FLAG "REPETITIVE PLANNING FLAG" '||
289 ' ,MSIB.PLANNING_EXCEPTION_SET "PLANNING EXCEPTION SET" '||
290 ' ,DECODE(MLU_BIT.MEANING,null,null, '||
291 ' (MLU_BIT.MEANING || '' ('' || MSIB.BOM_ITEM_TYPE || '')'')) "BOM ITEM TYPE" '||
292 ' ,MSIB.PICK_COMPONENTS_FLAG "PICK COMPONENTS FLAG" '||
293 ' ,MSIB.REPLENISH_TO_ORDER_FLAG "REPLENISH TO ORDER FLAG" '||
294 ' ,MIF2.PADDED_ITEM_NUMBER "BASE ITEM NUMBER" '||
295 ' ,MSIB.BASE_ITEM_ID "BASE ITEM ID" '||
296 ' ,MSIB.ATP_COMPONENTS_FLAG "ATP COMPONENTS FLAG" '||
297 ' ,MSIB.ATP_FLAG "ATP FLAG" '||
298 ' ,MSIB.FIXED_LEAD_TIME "FIXED LEAD TIME" '||
299 ' ,MSIB.VARIABLE_LEAD_TIME "VARIABLE LEAD TIME" '||
300 ' ,MSIB.WIP_SUPPLY_LOCATOR_ID "WIP SUPPLY LOCATOR ID" '||
301 ' ,DECODE(MLU_WST.MEANING,null,null, '||
302 ' (MLU_WST.MEANING || '' ('' || MSIB.WIP_SUPPLY_TYPE || '')'')) "WIP SUPPLY TYPE" '||
303 ' ,MSIB.WIP_SUPPLY_SUBINVENTORY "WIP SUPPLY SUBINVENTORY" '||
304 ' ,MSIB.OVERCOMPLETION_TOLERANCE_TYPE "OVERCOMPLETION TOLERANCE TYPE" '||
305 ' ,MSIB.OVERCOMPLETION_TOLERANCE_VALUE "OVERCOMPLETION TOLERANCE VALUE" '||
306 ' FROM mtl_system_items_b msib, mtl_item_flexfields mif '||
307 ' ,mtl_parameters mp, mtl_item_flexfields mif2 '||
308 ' ,MFG_LOOKUPS MLU_LOTCC '||
309 ' ,MFG_LOOKUPS MLU_SLC '||
313 ' ,MFG_LOOKUPS MLU_RLC '||
310 ' ,MFG_LOOKUPS MLU_SNCC '||
311 ' ,MFG_LOOKUPS MLU_ST '||
312 ' ,MFG_LOOKUPS MLU_RSIC '||
314 ' ,MFG_LOOKUPS MLU_LCC '||
315 ' ,MFG_LOOKUPS MLU_PTFC '||
316 ' ,MFG_LOOKUPS MLU_DTFC '||
317 ' ,FND_LOOKUPS FLU_EAPF '||
318 ' ,MFG_LOOKUPS MLU_BIT '||
319 ' ,MFG_LOOKUPS MLU_WST '||
320 ' where 1=1 '||
321 ' and msib.inventory_item_id= mif.inventory_item_id '||
322 ' and msib.organization_id = mif.organization_id '||
323 ' and msib.organization_id = mp.organization_id '||
324 ' and mif.organization_id = mp.organization_id '||
325 ' and msib.base_item_id = mif2.inventory_item_id(+) '||
326 ' and msib.organization_id = mif2.organization_id(+) '||
327 ' and msib.LOT_CONTROL_CODE=MLU_LOTCC.LOOKUP_CODE(+) AND ''MTL_LOT_CONTROL''=MLU_LOTCC.LOOKUP_TYPE(+) '||
328 ' and msib.SHELF_LIFE_CODE=MLU_SLC.LOOKUP_CODE(+) AND ''MTL_SHELF_LIFE''=MLU_SLC.LOOKUP_TYPE(+) '||
329 ' and msib.SERIAL_NUMBER_CONTROL_CODE=MLU_SNCC.LOOKUP_CODE(+) AND ''MTL_SERIAL_NUMBER''=MLU_SNCC.LOOKUP_TYPE(+) '||
330 ' and msib.SOURCE_TYPE=MLU_ST.LOOKUP_CODE(+) AND ''MTL_SOURCE_TYPES''=MLU_ST.LOOKUP_TYPE(+) '||
331 ' and msib.RESTRICT_SUBINVENTORIES_CODE=MLU_RSIC.LOOKUP_CODE(+) AND ''MTL_SUBINVENTORY_RESTRICTIONS''=MLU_RSIC.LOOKUP_TYPE(+) '||
332 ' and msib.RESTRICT_LOCATORS_CODE=MLU_RLC.LOOKUP_CODE(+) AND ''MTL_LOCATOR_RESTRICTIONS''=MLU_RLC.LOOKUP_TYPE(+) '||
333 ' and msib.LOCATION_CONTROL_CODE=MLU_LCC.LOOKUP_CODE(+) AND ''MTL_LOCATION_CONTROL''=MLU_LCC.LOOKUP_TYPE(+) '||
334 ' and msib.PLANNING_TIME_FENCE_CODE=MLU_PTFC.LOOKUP_CODE(+) AND ''MTL_TIME_FENCE''=MLU_PTFC.LOOKUP_TYPE(+) '||
335 ' and msib.DEMAND_TIME_FENCE_CODE=MLU_DTFC.LOOKUP_CODE(+) AND ''MTL_ITEM_FENCH''=MLU_DTFC.LOOKUP_TYPE(+) '||
336 ' and msib.END_ASSEMBLY_PEGGING_FLAG=FLU_EAPF.LOOKUP_CODE(+) AND ''ASSEMBLY_PEGGING_CODE''=FLU_EAPF.LOOKUP_TYPE(+) '||
337 ' and msib.BOM_ITEM_TYPE=MLU_BIT.LOOKUP_CODE(+) AND ''BOM_ITEM_TYPE''=MLU_BIT.LOOKUP_TYPE(+) '||
338 ' and msib.WIP_SUPPLY_TYPE=MLU_WST.LOOKUP_CODE(+) AND ''WIP_SUPPLY''=MLU_WST.LOOKUP_TYPE(+) ';
339
340 if l_org_id is not null then
341 sqltxt :=sqltxt||' and msib.organization_id = '||l_org_id;
342 end if;
343
344 if l_item_id is not null then
345 sqltxt :=sqltxt||' and msib.inventory_item_id = '||l_item_id;
346 end if;
347
348 sqltxt :=sqltxt||' and rownum < '||row_limit;
349 sqltxt :=sqltxt||' order by mp.organization_code,mif.padded_item_number';
350
351 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Attributes (Contd 1..)');
352 If (num_rows = row_limit -1 ) Then
353 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/>');
354 End If;
355
356 statusStr := 'SUCCESS';
357 isFatal := 'FALSE';
358
359 sqltxt := ' SELECT ' ||
360 ' MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
361 ' ,MSIB.INVENTORY_ITEM_ID "ITEM ID" '||
362 ' ,MP.ORGANIZATION_CODE "ORG CODE" '||
363 ' ,MSIB.ORGANIZATION_ID "ORG ID" '||
364 ' ,MSIB.PRIMARY_UOM_CODE "PRIMARY UOM CODE" '||
365 ' ,MSIB.PRIMARY_UNIT_OF_MEASURE "PRIMARY UNIT OF MEASURE" '||
366 ' ,DECODE(MLU_AULC.MEANING,null,null, '||
367 ' (MLU_AULC.MEANING || '' ('' || MSIB.ALLOWED_UNITS_LOOKUP_CODE || '')'')) "ALLOWED UNITS LOOKUP CODE" '||
368 ' ,MSIB.COST_OF_SALES_ACCOUNT "COST OF SALES ACCOUNT" '||
369 ' ,MSIB.SALES_ACCOUNT "SALES ACCOUNT" '||
370 ' ,MSIB.DEFAULT_INCLUDE_IN_ROLLUP_FLAG "DEFAULT INCLUDE IN ROLLUP FLAG" '||
371 ' ,MSIB.INVENTORY_ITEM_STATUS_CODE "INVENTORY ITEM STATUS CODE" '||
372 ' ,DECODE(MLU_IPC.MEANING,null,null, '||
373 ' (MLU_IPC.MEANING || '' ('' || MSIB.INVENTORY_PLANNING_CODE || '')'')) "INVENTORY PLANNING CODE" '||
374 ' ,MSIB.PLANNER_CODE "PLANNER CODE" '||
375 ' ,DECODE(MLU_PMBC.MEANING,null,null, '||
376 ' (MLU_PMBC.MEANING || '' ('' || MSIB.PLANNING_MAKE_BUY_CODE || '')'')) "PLANNING MAKE BUY CODE" '||
377 ' ,MSIB.FIXED_LOT_MULTIPLIER "FIXED LOT MULTIPLIER" '||
378 ' ,DECODE(MLU_RCT.MEANING,null,null, '||
379 ' (MLU_RCT.MEANING || '' ('' || MSIB.ROUNDING_CONTROL_TYPE || '')'')) "ROUNDING CONTROL TYPE" '||
380 ' ,MSIB.CARRYING_COST "CARRYING COST" '||
381 ' ,MSIB.POSTPROCESSING_LEAD_TIME "POSTPROCESSING LEAD TIME" '||
382 ' ,MSIB.PREPROCESSING_LEAD_TIME "PREPROCESSING LEAD TIME" '||
383 ' ,MSIB.FULL_LEAD_TIME "FULL LEAD TIME" '||
384 ' ,MSIB.ORDER_COST "ORDER COST" '||
385 ' ,MSIB.MRP_SAFETY_STOCK_PERCENT "MRP SAFETY STOCK PERCENT" '||
386 ' ,DECODE(MLU_MSSC.MEANING,null,null, '||
387 ' (MLU_MSSC.MEANING || '' ('' || MSIB.MRP_SAFETY_STOCK_CODE || '')'')) "MRP SAFETY STOCK CODE" '||
388 ' ,MSIB.MIN_MINMAX_QUANTITY "MIN MINMAX QUANTITY" '||
389 ' ,MSIB.MAX_MINMAX_QUANTITY "MAX MINMAX QUANTITY" '||
390 ' ,MSIB.MINIMUM_ORDER_QUANTITY "MINIMUM ORDER QUANTITY" '||
391 ' ,MSIB.FIXED_ORDER_QUANTITY "FIXED ORDER QUANTITY" '||
392 ' ,MSIB.FIXED_DAYS_SUPPLY "FIXED DAYS SUPPLY" '||
393 ' ,MSIB.MAXIMUM_ORDER_QUANTITY "MAXIMUM ORDER QUANTITY" '||
394 ' ,MSIB.ATP_RULE_ID "ATP RULE ID" '||
395 ' ,MSIB.PICKING_RULE_ID "PICKING RULE ID" '||
396 ' ,MSIB.RESERVABLE_TYPE "RESERVABLE TYPE" '||
397 ' ,MSIB.POSITIVE_MEASUREMENT_ERROR "POSITIVE MEASUREMENT ERROR" '||
401 ' ,to_char(MSIB.ENGINEERING_DATE,''DD-MON-YYYY HH24:MI:SS'') "ENGINEERING DATE" '||
398 ' ,MSIB.NEGATIVE_MEASUREMENT_ERROR "NEGATIVE MEASUREMENT ERROR" '||
399 ' ,MSIB.ENGINEERING_ECN_CODE "ENGINEERING ECN CODE" '||
400 ' ,MSIB.ENGINEERING_ITEM_ID "ENGINEERING ITEM ID" '||
402 ' ,MSIB.SERVICE_STARTING_DELAY "SERVICE STARTING DELAY" '||
403 ' ,MSIB.VENDOR_WARRANTY_FLAG "VENDOR WARRANTY FLAG" '||
404 ' ,MSIB.SERVICEABLE_COMPONENT_FLAG "SERVICEABLE COMPONENT FLAG" '||
405 ' ,MSIB.SERVICEABLE_PRODUCT_FLAG "SERVICEABLE PRODUCT FLAG" '||
406 ' ,MSIB.BASE_WARRANTY_SERVICE_ID "BASE WARRANTY SERVICE ID" '||
407 ' ,MSIB.PAYMENT_TERMS_ID "PAYMENT TERMS ID" '||
408 ' ,MSIB.PREVENTIVE_MAINTENANCE_FLAG "PREVENTIVE MAINTENANCE FLAG" '||
409 ' ,MSIB.PRIMARY_SPECIALIST_ID "PRIMARY SPECIALIST ID" '||
410 ' ,MSIB.SECONDARY_SPECIALIST_ID "SECONDARY SPECIALIST ID" '||
411 ' ,MSIB.SERVICEABLE_ITEM_CLASS_ID "SERVICEABLE ITEM CLASS ID" '||
412 ' ,MSIB.TIME_BILLABLE_FLAG "TIME BILLABLE FLAG" '||
413 ' ,DECODE(CLU_MBF.MEANING,null,null, '||
414 ' (CLU_MBF.MEANING || '' ('' || MSIB.MATERIAL_BILLABLE_FLAG || '')'')) "MATERIAL BILLABLE FLAG" '||
415 ' ,MSIB.EXPENSE_BILLABLE_FLAG "EXPENSE BILLABLE FLAG" '||
416 ' ,MSIB.PRORATE_SERVICE_FLAG "PRORATE SERVICE FLAG" '||
417 ' ,MSIB.COVERAGE_SCHEDULE_ID "COVERAGE SCHEDULE ID" '||
418 ' ,MSIB.SERVICE_DURATION_PERIOD_CODE "SERVICE DURATION PERIOD CODE" '||
419 ' ,MSIB.SERVICE_DURATION "SERVICE DURATION" '||
420 ' ,MSIB.WARRANTY_VENDOR_ID "WARRANTY VENDOR ID" '||
421 ' ,MSIB.MAX_WARRANTY_AMOUNT "MAX WARRANTY AMOUNT" '||
422 ' ,MSIB.RESPONSE_TIME_PERIOD_CODE "RESPONSE TIME PERIOD CODE" '||
423 ' ,MSIB.RESPONSE_TIME_VALUE "RESPONSE TIME VALUE" '||
424 ' ,MSIB.NEW_REVISION_CODE "NEW REVISION CODE" '||
425 ' ,MSIB.INVOICEABLE_ITEM_FLAG "INVOICEABLE ITEM FLAG" '||
426 ' ,MSIB.TAX_CODE "TAX CODE" '||
427 ' ,MSIB.INVOICE_ENABLED_FLAG "INVOICE ENABLED FLAG" '||
428 ' ,MSIB.MUST_USE_APPROVED_VENDOR_FLAG "MUST USE APPROVED VENDOR FLAG" '||
429 ' ,MSIB.REQUEST_ID "REQUEST ID" '||
430 ' ,MSIB.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
431 ' ,MSIB.PROGRAM_ID "PROGRAM ID" '||
432 ' ,to_char(MSIB.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
433 ' ,MSIB.OUTSIDE_OPERATION_FLAG "OUTSIDE OPERATION FLAG" '||
434 ' ,DECODE(PLU_OOUT.DISPLAYED_FIELD,null,null, '||
435 ' (PLU_OOUT.DISPLAYED_FIELD || '' ('' || MSIB.OUTSIDE_OPERATION_UOM_TYPE || '')'')) "OUTSIDE OPERATION UOM TYPE" '||
436 ' ,MSIB.SAFETY_STOCK_BUCKET_DAYS "SAFETY STOCK BUCKET DAYS" '||
437 ' ,DECODE(MLU_ARM.MEANING,null,null, '||
438 ' (MLU_ARM.MEANING || '' ('' || MSIB.AUTO_REDUCE_MPS || '')'')) "AUTO REDUCE MPS" '||
439 ' ,MSIB.COSTING_ENABLED_FLAG "COSTING ENABLED FLAG" '||
440 ' ,MSIB.AUTO_CREATED_CONFIG_FLAG "AUTO CREATED CONFIG FLAG" '||
441 ' ,MSIB.CYCLE_COUNT_ENABLED_FLAG "CYCLE COUNT ENABLED FLAG" '||
442 ' ,DECODE(FCLU_ITT.MEANING,null,null, '||
443 ' (FCLU_ITT.MEANING || '' ('' || MSIB.ITEM_TYPE || '')'')) "ITEM TYPE" '||
444 ' ,MSIB.MODEL_CONFIG_CLAUSE_NAME "MODEL CONFIG CLAUSE NAME" '||
445 ' ,MSIB.SHIP_MODEL_COMPLETE_FLAG "SHIP MODEL COMPLETE FLAG" '||
446 ' ,DECODE(MLU_MPC.MEANING,null,null, '||
447 ' (MLU_MPC.MEANING || '' ('' || MSIB.MRP_PLANNING_CODE || '')'')) "MRP PLANNING CODE" '||
448 ' ,MSIB.RETURN_INSPECTION_REQUIREMENT "RETURN INSPECTION REQUIREMENT" '||
449 ' ,DECODE(MLU_AFOC.MEANING,null,null, '||
450 ' (MLU_AFOC.MEANING || '' ('' || MSIB.ATO_FORECAST_CONTROL || '')'')) "ATO FORECAST CONTROL" '||
451 ' ,DECODE(MLU_RTFC.MEANING,null,null, '||
452 ' (MLU_RTFC.MEANING || '' ('' || MSIB.RELEASE_TIME_FENCE_CODE || '')'')) "RELEASE TIME FENCE CODE" '||
453 ' ,MSIB.RELEASE_TIME_FENCE_DAYS "RELEASE TIME FENCE DAYS" '||
454 ' ,MSIB.CONTAINER_ITEM_FLAG "CONTAINER ITEM FLAG" '||
455 ' ,MSIB.VEHICLE_ITEM_FLAG "VEHICLE ITEM FLAG" '||
456 ' ,MSIB.MAXIMUM_LOAD_WEIGHT "MAXIMUM LOAD WEIGHT" '||
457 ' ,MSIB.MINIMUM_FILL_PERCENT "MINIMUM FILL PERCENT" '||
458 ' ,DECODE(FCLU_CTC.MEANING,null,null, '||
459 ' (FCLU_CTC.MEANING || '' ('' || MSIB.CONTAINER_TYPE_CODE || '')'')) "CONTAINER TYPE CODE" '||
460 ' ,MSIB.INTERNAL_VOLUME "INTERNAL VOLUME" '||
461 ' ,to_char(MSIB.WH_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "WH UPDATE DATE" '||
462 ' ,MSIB.PRODUCT_FAMILY_ITEM_ID "PRODUCT FAMILY ITEM ID" '||
463 ' ,MSIB.GLOBAL_ATTRIBUTE_CATEGORY "GLOBAL ATTRIBUTE CATEGORY" '||
464 ' ,MSIB.GLOBAL_ATTRIBUTE1 "GLOBAL ATTRIBUTE1" '||
465 ' ,MSIB.GLOBAL_ATTRIBUTE2 "GLOBAL ATTRIBUTE2" '||
466 ' ,MSIB.GLOBAL_ATTRIBUTE3 "GLOBAL ATTRIBUTE3" '||
467 ' ,MSIB.GLOBAL_ATTRIBUTE4 "GLOBAL ATTRIBUTE4" '||
468 ' ,MSIB.GLOBAL_ATTRIBUTE5 "GLOBAL ATTRIBUTE5" '||
469 ' FROM mtl_system_items_b msib, mtl_item_flexfields mif '||
470 ' ,mtl_parameters mp '||
471 ' ,MFG_LOOKUPS MLU_AULC '||
472 ' ,MFG_LOOKUPS MLU_IPC '||
473 ' ,MFG_LOOKUPS MLU_PMBC '||
474 ' ,MFG_LOOKUPS MLU_RCT '||
475 ' ,MFG_LOOKUPS MLU_MSSC '||
476 ' ,CS_LOOKUPS CLU_MBF '||
477 ' ,PO_LOOKUP_CODES PLU_OOUT '||
478 ' ,MFG_LOOKUPS MLU_ARM '||
479 ' ,FND_COMMON_LOOKUPS FCLU_ITT '||
480 ' ,MFG_LOOKUPS MLU_MPC '||
481 ' ,MFG_LOOKUPS MLU_AFOC '||
482 ' ,MFG_LOOKUPS MLU_RTFC '||
483 ' ,FND_COMMON_LOOKUPS FCLU_CTC '||
484 ' where 1=1 '||
485 ' and msib.inventory_item_id= mif.inventory_item_id '||
486 ' and msib.organization_id = mif.organization_id '||
490 ' and msib.INVENTORY_PLANNING_CODE=MLU_IPC.LOOKUP_CODE(+) AND ''MTL_MATERIAL_PLANNING''=MLU_IPC.LOOKUP_TYPE(+) '||
487 ' and msib.organization_id = mp.organization_id '||
488 ' and mif.organization_id = mp.organization_id '||
489 ' and msib.ALLOWED_UNITS_LOOKUP_CODE=MLU_AULC.LOOKUP_CODE(+) AND ''MTL_CONVERSION_TYPE''=MLU_AULC.LOOKUP_TYPE(+) '||
491 ' and msib.PLANNING_MAKE_BUY_CODE=MLU_PMBC.LOOKUP_CODE(+) AND ''MTL_PLANNING_MAKE_BUY''=MLU_PMBC.LOOKUP_TYPE(+) '||
492 ' and msib.ROUNDING_CONTROL_TYPE=MLU_RCT.LOOKUP_CODE(+) AND ''MTL_ROUTING''=MLU_RCT.LOOKUP_TYPE(+) '||
493 ' and msib.MRP_SAFETY_STOCK_CODE=MLU_MSSC.LOOKUP_CODE(+) AND ''MTL_SAFETY_STOCK_TYPE''=MLU_MSSC.LOOKUP_TYPE(+) '||
494 ' and msib.MATERIAL_BILLABLE_FLAG=CLU_MBF.LOOKUP_CODE(+) AND ''MTL_SERVICE_BILLABLE_FLAG''=CLU_MBF.LOOKUP_TYPE(+) '||
495 ' and msib.OUTSIDE_OPERATION_UOM_TYPE=PLU_OOUT.LOOKUP_CODE(+) AND ''OUTSIDE OPERATION UOM TYPE''=PLU_OOUT.LOOKUP_TYPE(+) '||
496 ' and msib.AUTO_REDUCE_MPS=MLU_ARM.LOOKUP_CODE(+) AND ''MRP_AUTO_REDUCE_MPS''=MLU_ARM.LOOKUP_TYPE(+) '||
497 ' and msib.ITEM_TYPE=FCLU_ITT.LOOKUP_CODE(+) AND ''ITEM_TYPE''=FCLU_ITT.LOOKUP_TYPE(+) '||
498 ' and msib.MRP_PLANNING_CODE=MLU_MPC.LOOKUP_CODE(+) AND ''MRP_PLANNING_CODE''=MLU_MPC.LOOKUP_TYPE(+) '||
499 ' and msib.ATO_FORECAST_CONTROL=MLU_AFOC.LOOKUP_CODE(+) AND ''MRP_ATO_FORECAST_CONTROL''=MLU_AFOC.LOOKUP_TYPE(+) '||
500 ' and msib.RELEASE_TIME_FENCE_CODE=MLU_RTFC.LOOKUP_CODE(+) AND ''MTL_RELEASE_TIME_FENCE''=MLU_RTFC.LOOKUP_TYPE(+) '||
501 ' and msib.CONTAINER_TYPE_CODE=FCLU_CTC.LOOKUP_CODE(+) AND ''CONTAINER_TYPE_CODE''=FCLU_CTC.LOOKUP_TYPE(+) ';
502
503 if l_org_id is not null then
504 sqltxt :=sqltxt||' and msib.organization_id = '||l_org_id;
505 end if;
506
507 if l_item_id is not null then
508 sqltxt :=sqltxt||' and msib.inventory_item_id = '||l_item_id;
509 end if;
510
511 sqltxt :=sqltxt||' and rownum < '||row_limit;
512 sqltxt :=sqltxt||' order by mp.organization_code,mif.padded_item_number';
513
514 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Attributes (Contd 2..)');
515 If (num_rows = row_limit -1 ) Then
516 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/>');
517 End If;
518 statusStr := 'SUCCESS';
519 isFatal := 'FALSE';
520
521 sqltxt := ' SELECT ' ||
522 ' MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
523 ' ,MSIB.INVENTORY_ITEM_ID "ITEM ID" '||
524 ' ,MP.ORGANIZATION_CODE "ORG CODE" '||
525 ' ,MSIB.ORGANIZATION_ID "ORG ID" '||
526 ' ,MSIB.GLOBAL_ATTRIBUTE6 "GLOBAL ATTRIBUTE6" '||
527 ' ,MSIB.GLOBAL_ATTRIBUTE7 "GLOBAL ATTRIBUTE7" '||
528 ' ,MSIB.GLOBAL_ATTRIBUTE8 "GLOBAL ATTRIBUTE8" '||
529 ' ,MSIB.GLOBAL_ATTRIBUTE9 "GLOBAL ATTRIBUTE9" '||
530 ' ,MSIB.GLOBAL_ATTRIBUTE10 "GLOBAL ATTRIBUTE10" '||
531 ' ,MSIB.PURCHASING_TAX_CODE "PURCHASING TAX CODE" '||
532 ' ,MSIB.OVERCOMPLETION_TOLERANCE_TYPE "OVERCOMPLETION TOLERANCE TYPE" '||
533 ' ,MSIB.OVERCOMPLETION_TOLERANCE_VALUE "OVERCOMPLETION TOLERANCE VALUE" '||
534 ' ,MSIB.EFFECTIVITY_CONTROL "EFFECTIVITY CONTROL" '||
535 ' ,MSIB.CHECK_SHORTAGES_FLAG "CHECK SHORTAGES FLAG" '||
536 ' ,MSIB.OVER_SHIPMENT_TOLERANCE "OVER SHIPMENT TOLERANCE" '||
537 ' ,MSIB.UNDER_SHIPMENT_TOLERANCE "UNDER SHIPMENT TOLERANCE" '||
538 ' ,MSIB.OVER_RETURN_TOLERANCE "OVER RETURN TOLERANCE" '||
539 ' ,MSIB.UNDER_RETURN_TOLERANCE "UNDER RETURN TOLERANCE" '||
540 ' ,MSIB.EQUIPMENT_TYPE "EQUIPMENT TYPE" '||
541 ' ,MSIB.RECOVERED_PART_DISP_CODE "RECOVERED PART DISP CODE" '||
542 ' ,MSIB.DEFECT_TRACKING_ON_FLAG "DEFECT TRACKING ON FLAG" '||
543 ' ,MSIB.USAGE_ITEM_FLAG "USAGE ITEM FLAG" '||
544 ' ,MSIB.EVENT_FLAG "EVENT FLAG" '||
545 ' ,MSIB.ELECTRONIC_FLAG "ELECTRONIC FLAG" '||
546 ' ,MSIB.DOWNLOADABLE_FLAG "DOWNLOADABLE FLAG" '||
547 ' ,MSIB.VOL_DISCOUNT_EXEMPT_FLAG "VOL DISCOUNT EXEMPT FLAG" '||
548 ' ,MSIB.COUPON_EXEMPT_FLAG "COUPON EXEMPT FLAG" '||
549 ' ,MSIB.COMMS_NL_TRACKABLE_FLAG "COMMS NL TRACKABLE FLAG" '||
550 ' ,MSIB.ASSET_CREATION_CODE "ASSET CREATION CODE" '||
551 ' ,MSIB.COMMS_ACTIVATION_REQD_FLAG "COMMS ACTIVATION REQD FLAG" '||
552 ' ,MSIB.ORDERABLE_ON_WEB_FLAG "ORDERABLE ON WEB FLAG" '||
553 ' ,MSIB.BACK_ORDERABLE_FLAG "BACK ORDERABLE FLAG" '||
554 ' ,MSIB.WEB_STATUS "WEB STATUS" '||
555 ' ,MSIB.INDIVISIBLE_FLAG "INDIVISIBLE FLAG" '||
556 ' ,MSIB.DIMENSION_UOM_CODE "DIMENSION UOM CODE" '||
557 ' ,MSIB.UNIT_LENGTH "UNIT LENGTH" '||
558 ' ,MSIB.UNIT_WIDTH "UNIT WIDTH" '||
559 ' ,MSIB.UNIT_HEIGHT "UNIT HEIGHT" '||
560 ' ,MSIB.BULK_PICKED_FLAG "BULK PICKED FLAG" '||
561 ' ,MSIB.LOT_STATUS_ENABLED "LOT STATUS ENABLED" '||
562 ' ,MSIB.DEFAULT_LOT_STATUS_ID "DEFAULT LOT STATUS ID" '||
563 ' ,MSIB.SERIAL_STATUS_ENABLED "SERIAL STATUS ENABLED" '||
564 ' ,MSIB.DEFAULT_SERIAL_STATUS_ID "DEFAULT SERIAL STATUS ID" '||
565 ' ,MSIB.LOT_SPLIT_ENABLED "LOT SPLIT ENABLED" '||
566 ' ,MSIB.LOT_MERGE_ENABLED "LOT MERGE ENABLED" '||
567 ' ,MSIB.INVENTORY_CARRY_PENALTY "INVENTORY CARRY PENALTY" '||
568 ' ,MSIB.OPERATION_SLACK_PENALTY "OPERATION SLACK PENALTY" '||
569 ' ,MSIB.FINANCING_ALLOWED_FLAG "FINANCING ALLOWED FLAG" '||
570 ' ,MSIB.EAM_ITEM_TYPE "EAM ITEM TYPE" '||
571 ' ,MSIB.EAM_ACTIVITY_TYPE_CODE "EAM ACTIVITY TYPE CODE" '||
572 ' ,MSIB.EAM_ACTIVITY_CAUSE_CODE "EAM ACTIVITY CAUSE CODE" '||
573 ' ,MSIB.EAM_ACT_NOTIFICATION_FLAG "EAM ACT NOTIFICATION FLAG" '||
574 ' ,MSIB.EAM_ACT_SHUTDOWN_STATUS "EAM ACT SHUTDOWN STATUS" '||
575 ' ,MSIB.DUAL_UOM_CONTROL "DUAL UOM CONTROL" '||
576 ' ,MSIB.SECONDARY_UOM_CODE "SECONDARY UOM CODE" '||
580 ' ,MSIB.SUBSCRIPTION_DEPEND_FLAG "SUBSCRIPTION DEPEND FLAG" '||
577 ' ,MSIB.DUAL_UOM_DEVIATION_HIGH "DUAL UOM DEVIATION HIGH" '||
578 ' ,MSIB.DUAL_UOM_DEVIATION_LOW "DUAL UOM DEVIATION LOW" '||
579 ' ,MSIB.CONTRACT_ITEM_TYPE_CODE "CONTRACT ITEM TYPE CODE" '||
581 ' ,MSIB.SERV_REQ_ENABLED_CODE "SERV REQ ENABLED CODE" '||
582 ' ,MSIB.SERV_BILLING_ENABLED_FLAG "SERV BILLING ENABLED FLAG" '||
583 ' ,MSIB.SERV_IMPORTANCE_LEVEL "SERV IMPORTANCE LEVEL" '||
584 ' ,MSIB.PLANNED_INV_POINT_FLAG "PLANNED INV POINT FLAG" '||
585 ' ,MSIB.LOT_TRANSLATE_ENABLED "LOT TRANSLATE ENABLED" '||
586 ' ,MSIB.DEFAULT_SO_SOURCE_TYPE "DEFAULT SO SOURCE TYPE" '||
587 ' ,MSIB.CREATE_SUPPLY_FLAG "CREATE SUPPLY FLAG" '||
588 ' ,MSIB.SUBSTITUTION_WINDOW_CODE "SUBSTITUTION WINDOW CODE" '||
589 ' ,MSIB.SUBSTITUTION_WINDOW_DAYS "SUBSTITUTION WINDOW DAYS" '||
590 ' ,MSIB.IB_ITEM_INSTANCE_CLASS "IB ITEM INSTANCE CLASS" '||
591 ' ,MSIB.CONFIG_MODEL_TYPE "CONFIG MODEL TYPE" '||
592 ' ,MSIB.LOT_SUBSTITUTION_ENABLED "LOT SUBSTITUTION ENABLED" '||
593 ' ,MSIB.MINIMUM_LICENSE_QUANTITY "MINIMUM LICENSE QUANTITY" '||
594 ' ,MSIB.EAM_ACTIVITY_SOURCE_CODE "EAM ACTIVITY SOURCE CODE" '||
595 ' ,MSIB.LIFECYCLE_ID "LIFECYCLE ID" '||
596 ' ,MSIB.CURRENT_PHASE_ID "CURRENT PHASE ID" '||
597 ' ,MSIB.OBJECT_VERSION_NUMBER "OBJECT VERSION NUMBER" '||
598 ' ,MSIB.TRACKING_QUANTITY_IND "TRACKING QUANTITY IND" '||
599 ' ,MSIB.ONT_PRICING_QTY_SOURCE "ONT PRICING QTY SOURCE" '||
600 ' ,MSIB.SECONDARY_DEFAULT_IND "SECONDARY DEFAULT IND" '||
601 ' ,MSIB.OPTION_SPECIFIC_SOURCED "OPTION SPECIFIC SOURCED" '||
602 ' ,MSIB.APPROVAL_STATUS "APPROVAL STATUS" '||
603 ' ,MSIB.VMI_MINIMUM_UNITS "VMI MINIMUM UNITS" '||
604 ' ,MSIB.VMI_MINIMUM_DAYS "VMI MINIMUM DAYS" '||
605 ' ,MSIB.VMI_MAXIMUM_UNITS "VMI MAXIMUM UNITS" '||
606 ' ,MSIB.VMI_MAXIMUM_DAYS "VMI MAXIMUM DAYS" '||
607 ' ,MSIB.VMI_FIXED_ORDER_QUANTITY "VMI FIXED ORDER QUANTITY" '||
608 ' ,MSIB.SO_AUTHORIZATION_FLAG "SO AUTHORIZATION FLAG" '||
609 ' ,MSIB.CONSIGNED_FLAG "CONSIGNED FLAG" '||
610 ' ,MSIB.ASN_AUTOEXPIRE_FLAG "ASN AUTOEXPIRE FLAG" '||
611 ' ,MSIB.VMI_FORECAST_TYPE "VMI FORECAST TYPE" '||
612 ' ,MSIB.FORECAST_HORIZON "FORECAST HORIZON" '||
613 ' ,MSIB.EXCLUDE_FROM_BUDGET_FLAG "EXCLUDE FROM BUDGET FLAG" '||
614 ' ,MSIB.DAYS_TGT_INV_SUPPLY "DAYS TGT INV SUPPLY" '||
615 ' ,MSIB.DAYS_TGT_INV_WINDOW "DAYS TGT INV WINDOW" '||
616 ' ,MSIB.DAYS_MAX_INV_SUPPLY "DAYS MAX INV SUPPLY" '||
617 ' ,MSIB.DAYS_MAX_INV_WINDOW "DAYS MAX INV WINDOW" '||
618 ' ,MSIB.DRP_PLANNED_FLAG "DRP PLANNED FLAG" '||
619 ' ,MSIB.CRITICAL_COMPONENT_FLAG "CRITICAL COMPONENT FLAG" '||
620 ' ,MSIB.CONTINOUS_TRANSFER "CONTINOUS TRANSFER" '||
621 ' ,MSIB.CONVERGENCE "CONVERGENCE" '||
622 ' ,MSIB.DIVERGENCE "DIVERGENCE" '||
623 ' ,MSIB.CONFIG_ORGS "CONFIG ORGS" '||
624 ' ,MSIB.CONFIG_MATCH "CONFIG MATCH" '||
625 ' FROM mtl_system_items_b msib , mtl_item_flexfields mif '||
626 ' ,mtl_parameters mp '||
627 ' where 1=1 '||
628 ' and msib.inventory_item_id= mif.inventory_item_id '||
629 ' and msib.organization_id = mif.organization_id '||
630 ' and msib.organization_id = mp.organization_id '||
631 ' and mif.organization_id = mp.organization_id ';
632
633 if l_org_id is not null then
634 sqltxt :=sqltxt||' and msib.organization_id = '||l_org_id;
635 end if;
636
637 if l_item_id is not null then
638 sqltxt :=sqltxt||' and msib.inventory_item_id = '||l_item_id;
639 end if;
640
641 sqltxt :=sqltxt||' and rownum < '||row_limit;
642 sqltxt :=sqltxt||' order by mp.organization_code ,mif.padded_item_number';
643
644 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Attributes (Contd 3..)');
645 If (num_rows = row_limit -1 ) Then
646 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/>');
647 End If;
648 statusStr := 'SUCCESS';
649 isFatal := 'FALSE';
650
651 sqltxt := ' SELECT ' ||
652 ' MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
653 ' ,MSIB.INVENTORY_ITEM_ID "ITEM ID" '||
654 ' ,MP.ORGANIZATION_CODE "ORG CODE" '||
655 ' ,MSIB.ORGANIZATION_ID "ORG ID" '||
656 ' ,MSIB.ATTRIBUTE16 "ATTRIBUTE16" '||
657 ' ,MSIB.ATTRIBUTE17 "ATTRIBUTE17" '||
658 ' ,MSIB.ATTRIBUTE18 "ATTRIBUTE18" '||
659 ' ,MSIB.ATTRIBUTE19 "ATTRIBUTE19" '||
660 ' ,MSIB.ATTRIBUTE20 "ATTRIBUTE20" '||
661 ' ,MSIB.ATTRIBUTE21 "ATTRIBUTE21" '||
662 ' ,MSIB.ATTRIBUTE22 "ATTRIBUTE22" '||
663 ' ,MSIB.ATTRIBUTE23 "ATTRIBUTE23" '||
664 ' ,MSIB.ATTRIBUTE24 "ATTRIBUTE24" '||
665 ' ,MSIB.ATTRIBUTE25 "ATTRIBUTE25" '||
666 ' ,MSIB.ATTRIBUTE26 "ATTRIBUTE26" '||
667 ' ,MSIB.ATTRIBUTE27 "ATTRIBUTE27" '||
668 ' ,MSIB.ATTRIBUTE28 "ATTRIBUTE28" '||
669 ' ,MSIB.ATTRIBUTE29 "ATTRIBUTE29" '||
670 ' ,MSIB.ATTRIBUTE30 "ATTRIBUTE30" '||
671 ' ,MSIB.CAS_NUMBER "CAS NUMBER" '||
672 ' ,MSIB.CHILD_LOT_FLAG "CHILD LOT FLAG" '||
673 ' ,MSIB.CHILD_LOT_PREFIX "CHILD LOT PREFIX" '||
674 ' ,MSIB.CHILD_LOT_STARTING_NUMBER "CHILD LOT STARTING NUMBER" '||
675 ' ,MSIB.CHILD_LOT_VALIDATION_FLAG "CHILD LOT VALIDATION FLAG" '||
676 ' ,MSIB.COPY_LOT_ATTRIBUTE_FLAG "CHILD LOT ATTRIBUTE FLAG" '||
680 ' ,MSIB.GRADE_CONTROL_FLAG "GRADE CONTROL FLAG" '||
677 ' ,MSIB.DEFAULT_GRADE "DEFAULT GRADE" '||
678 ' ,MSIB.EXPIRATION_ACTION_CODE "EXPIRATION ACTION CODE" '||
679 ' ,MSIB.EXPIRATION_ACTION_INTERVAL "EXPIRATION ACTION INTERVAL" '||
681 ' ,MSIB.HAZARDOUS_MATERIAL_FLAG "HAZARDOUS MATERIAL FLAG" '||
682 ' ,MSIB.HOLD_DAYS "HOLD DAYS" '||
683 ' ,MSIB.LOT_DIVISIBLE_FLAG "LOT DIVISIBLE FLAG" '||
684 ' ,MSIB.MATURITY_DAYS "MATURITY DAYS" '||
685 ' ,MSIB.PARENT_CHILD_GENERATION_FLAG "PARENT CHILD GENERATION FLAG" '||
686 ' ,MSIB.PROCESS_COSTING_ENABLED_FLAG "PROCESS COSTING ENABLED FLAG" '||
687 ' ,MSIB.PROCESS_EXECUTION_ENABLED_FLAG"PROCESS EXECUTION ENABLED FLAG" '||
688 ' ,MSIB.PROCESS_QUALITY_ENABLED_FLAG "PROCESS QUALITY ENABLED FLAG" '||
689 ' ,MSIB.PROCESS_SUPPLY_LOCATOR_ID "PROCESS SUPPLY LOCATOR ID" '||
690 ' ,MSIB.PROCESS_SUPPLY_SUBINVENTORY "PROCESS SUPPLY SUBINVENTORY" '||
691 ' ,MSIB.PROCESS_YIELD_LOCATOR_ID "PROCESS YIELD LOCATOR ID" '||
692 ' ,MSIB.PROCESS_YIELD_SUBINVENTORY "PROCESS YIELD SUBINVENTORY" '||
693 ' ,MSIB.RECIPE_ENABLED_FLAG "RECIPE ENABLED FLAG" '||
694 ' ,MSIB.RETEST_INTERVAL "RETEST INTERVAL" '||
695 ' ,MSIB.CHARGE_PERIODICITY_CODE "CHARGE PERIODICITY CODE" '||
696 ' ,MSIB.REPAIR_LEADTIME "REPAIR LEADTIME" '||
697 ' ,MSIB.REPAIR_YIELD "REPAIR YIELD" '||
698 ' ,MSIB.PREPOSITION_POINT "PREPOSITION POINT" '||
699 ' ,MSIB.REPAIR_PROGRAM "REPAIR PROGRAM" '||
700 ' ,MSIB.SUBCONTRACTING_COMPONENT "SUBCONTRACTING COMPONENT" '||
701 ' ,MSIB.OUTSOURCED_ASSEMBLY "OUTSOURCED ASSEMBLY" '||
702 ' FROM mtl_system_items_b msib , mtl_item_flexfields mif '||
703 ' ,mtl_parameters mp '||
704 ' where 1=1 '||
705 ' and msib.inventory_item_id= mif.inventory_item_id '||
706 ' and msib.organization_id = mif.organization_id '||
707 ' and msib.organization_id = mp.organization_id '||
708 ' and mif.organization_id = mp.organization_id ';
709
710 if l_org_id is not null then
711 sqltxt :=sqltxt||' and msib.organization_id = '||l_org_id;
712 end if;
713
714 if l_item_id is not null then
715 sqltxt :=sqltxt||' and msib.inventory_item_id = '||l_item_id;
716 end if;
717
718 sqltxt :=sqltxt||' and rownum < '||row_limit;
719 sqltxt :=sqltxt||' order by mp.organization_code ,mif.padded_item_number';
720
721 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Attributes (Contd 4..)');
722 If (num_rows = row_limit -1 ) Then
723 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/>');
724 End If;
725
726 statusStr := 'SUCCESS';
727 isFatal := 'FALSE';
728
729 /* End of sql to fetch rows in mtl_system_items_b */
730
731 /* SQL to fetch rows in mtl_system_items_tl table */
732 sqltxt := 'SELECT '||
733 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
734 ' ,MSITL.INVENTORY_ITEM_ID "ITEM ID" '||
735 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
736 ' ,MSITL.ORGANIZATION_ID "ORG ID" '||
737 ' ,MSITL.LANGUAGE "LANGUAGE" '||
738 ' ,MSITL.SOURCE_LANG "SOURCE LANG" '||
739 ' ,MSITL.DESCRIPTION "DESCRIPTION" '||
740 ' ,MSITL.LONG_DESCRIPTION "LONG DESCRIPTION" '||
741 ' FROM MTL_SYSTEM_ITEMS_TL MSITL '||
742 ' ,MTL_PARAMETERS MP1 '||
743 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
744 ' WHERE 1=1 '||
745 ' AND MSITL.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
746 ' AND MSITL.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
747 ' AND MSITL.ORGANIZATION_ID = MIF1.ORGANIZATION_ID ';
748
749
750 if l_org_id is not null then
751 sqltxt :=sqltxt||' and msitl.organization_id = '||l_org_id;
752 end if;
753
754 if l_item_id is not null then
755 sqltxt :=sqltxt||' and msitl.inventory_item_id = '||l_item_id;
756 end if;
757
758 sqltxt :=sqltxt||' and rownum < '||row_limit;
759 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, msitl.language';
760
761 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Translation Details');
762 If (num_rows = row_limit -1 ) Then
763 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/>');
764 End If;
765
766 statusStr := 'SUCCESS';
767 isFatal := 'FALSE';
768
769 /* End of mtl_system_items_tl rows */
770
771 /* SQL to fetch item revisions */
772 sqltxt := 'SELECT '||
773 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
774 ' ,MIRB.INVENTORY_ITEM_ID "ITEM ID" '||
775 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
776 ' ,MIRB.ORGANIZATION_ID "ORGANIZATION ID" '||
777 ' ,MIRB.REVISION "REVISION" '||
778 ' ,to_char(MIRB.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
779 ' ,MIRB.LAST_UPDATED_BY "LAST UPDATED BY" '||
780 ' ,to_char(MIRB.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
781 ' ,MIRB.CREATED_BY "CREATED BY" '||
782 ' ,MIRB.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
783 ' ,MIRB.CHANGE_NOTICE "CHANGE NOTICE" '||
784 ' ,to_char(MIRB.ECN_INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "ECN INITIATION DATE" '||
785 ' ,to_char(MIRB.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE" '||
786 ' ,MIRB.IMPLEMENTED_SERIAL_NUMBER "IMPLEMENTED SERIAL NUMBER" '||
787 ' ,to_char(MIRB.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE" '||
788 ' ,MIRB.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
789 ' ,MIRB.ATTRIBUTE1 "ATTRIBUTE1" '||
793 ' ,MIRB.ATTRIBUTE5 "ATTRIBUTE5" '||
790 ' ,MIRB.ATTRIBUTE2 "ATTRIBUTE2" '||
791 ' ,MIRB.ATTRIBUTE3 "ATTRIBUTE3" '||
792 ' ,MIRB.ATTRIBUTE4 "ATTRIBUTE4" '||
794 ' ,MIRB.ATTRIBUTE6 "ATTRIBUTE6" '||
795 ' ,MIRB.ATTRIBUTE7 "ATTRIBUTE7" '||
796 ' ,MIRB.ATTRIBUTE8 "ATTRIBUTE8" '||
797 ' ,MIRB.ATTRIBUTE9 "ATTRIBUTE9" '||
798 ' ,MIRB.ATTRIBUTE10 "ATTRIBUTE10" '||
799 ' ,MIRB.ATTRIBUTE11 "ATTRIBUTE11" '||
800 ' ,MIRB.ATTRIBUTE12 "ATTRIBUTE12" '||
801 ' ,MIRB.ATTRIBUTE13 "ATTRIBUTE13" '||
802 ' ,MIRB.ATTRIBUTE14 "ATTRIBUTE14" '||
803 ' ,MIRB.ATTRIBUTE15 "ATTRIBUTE15" '||
804 ' ,MIRB.REQUEST_ID "REQUEST ID" '||
805 ' ,MIRB.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
806 ' ,MIRB.PROGRAM_ID "PROGRAM ID" '||
807 ' ,to_char(MIRB.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
808 ' ,MIRB.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
809 ' ,MIRB.DESCRIPTION "DESCRIPTION" '||
810 ' ,MIRB.OBJECT_VERSION_NUMBER "OBJECT VERSION NUMBER" '||
811 ' ,MIRB.REVISION_ID "REVISION ID" '||
812 ' ,MIRB.REVISION_LABEL "REVISION LABEL" '||
813 ' ,MIRB.REVISION_REASON "REVISION REASON" '||
814 ' ,MIRB.LIFECYCLE_ID "LIFECYCLE ID" '||
815 ' ,MIRB.CURRENT_PHASE_ID "CURRENT PHASE ID" '||
816 ' FROM MTL_ITEM_REVISIONS_B MIRB '||
817 ' ,MTL_PARAMETERS MP1 '||
818 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
819 ' WHERE 1=1 '||
820 ' AND MIRB.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
821 ' AND MIRB.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
822 ' AND MIRB.ORGANIZATION_ID = MIF1.ORGANIZATION_ID ';
823
824 if l_org_id is not null then
825 sqltxt :=sqltxt||' and mirb.organization_id = '||l_org_id;
826 end if;
827
828 if l_item_id is not null then
829 sqltxt :=sqltxt||' and mirb.inventory_item_id = '||l_item_id;
830 end if;
831
832 sqltxt :=sqltxt||' and rownum < '||row_limit;
833 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, mirb.revision';
834
835 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Revisions');
836 If (num_rows = row_limit -1 ) Then
837 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/>');
838 End If;
839
840 statusStr := 'SUCCESS';
841 isFatal := 'FALSE';
842
843 /* End of item revisions */
844
845 /* SQL to fetch item revisions TL */
846 sqltxt := ' SELECT ' ||
847 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
848 ' ,MIRTL.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
849 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
850 ' ,MIRTL.ORGANIZATION_ID "ORGANIZATION ID" '||
851 ' ,MIRTL.REVISION_ID "REVISION ID" '||
852 ' ,MIRTL.LANGUAGE "LANGUAGE" '||
853 ' ,MIRTL.SOURCE_LANG "SOURCE LANG" '||
854 ' ,MIRTL.DESCRIPTION "DESCRIPTION" '||
855 ' ,to_char(MIRTL.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
856 ' ,MIRTL.CREATED_BY "CREATED BY" '||
857 ' ,to_char(MIRTL.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
858 ' ,MIRTL.LAST_UPDATED_BY "LAST UPDATED BY" '||
859 ' ,MIRTL.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
860 ' FROM MTL_ITEM_REVISIONS_TL MIRTL '||
861 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
862 ' ,MTL_PARAMETERS MP1 '||
863 ' WHERE 1=1 '||
864 ' AND MIRTL.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
865 ' AND MIRTL.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
866 ' AND MIRTL.ORGANIZATION_ID = MIF1.ORGANIZATION_ID ';
867
868
869 if l_org_id is not null then
870 sqltxt :=sqltxt||' and mirtl.organization_id = '||l_org_id;
871 end if;
872
873 if l_item_id is not null then
874 sqltxt :=sqltxt||' and mirtl.inventory_item_id = '||l_item_id;
875 end if;
876
877 sqltxt :=sqltxt||' and rownum < '||row_limit;
878 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, mirtl.revision_id, mirtl.language';
879
880 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Revision Translation Details');
881 If (num_rows = row_limit -1 ) Then
882 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/>');
883 End If;
884
885 statusStr := 'SUCCESS';
886 isFatal := 'FALSE';
887
888 /* End of item revisions TL*/
889
890 /* SQL to fetch item catalog descriptive elements */
891
892 sqltxt := 'SELECT '||
893 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
894 ' ,MDEV.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
895 ' ,MDEV.ELEMENT_NAME "ELEMENT NAME" '||
896 ' ,MDEV.ELEMENT_VALUE "ELEMENT VALUE" '||
897 ' ,to_char(MDEV.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
898 ' ,MDEV.LAST_UPDATED_BY "LAST UPDATED BY" '||
899 ' ,to_char(MDEV.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
900 ' ,MDEV.CREATED_BY "CREATED BY" '||
901 ' ,MDEV.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
902 ' ,MDEV.REQUEST_ID "REQUEST ID" '||
903 ' ,MDEV.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
904 ' ,MDEV.PROGRAM_ID "PROGRAM ID" '||
908 ' ,MDEV.ELEMENT_SEQUENCE "ELEMENT SEQUENCE" '||
905 ' ,to_char(MDEV.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
906 ' ,DECODE(MDEV.DEFAULT_ELEMENT_FLAG,null,null,''Y'',''Yes (Y)'',''N'',''No (N)'','||
907 ' ''OTHER('' || MDEV.DEFAULT_ELEMENT_FLAG || '')'') "DEFAULT ELEMENT FLAG"'||
909 ' FROM MTL_DESCR_ELEMENT_VALUES MDEV '||
910 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
911 ' WHERE 1=1 '||
912 ' AND MDEV.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID ';
913
914
915 /* Catalog elements are associated to items always at the master org level.
916 So the filter on org_id is not required. */
917
918 if l_item_id is not null then
919 sqltxt :=sqltxt||' and mdev.inventory_item_id = '||l_item_id;
920 end if;
921
922 sqltxt :=sqltxt||' and rownum < '||row_limit;
923 sqltxt :=sqltxt||' order by mif1.padded_item_number, mdev.element_name';
924
925 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Catalog Descriptive Elements ');
926 If (num_rows = row_limit -1 ) Then
927 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/>');
928 End If;
929
930 statusStr := 'SUCCESS';
931 isFatal := 'FALSE';
932
933 /* End of item catalog descriptive elements */
934
935 /* SQL to fetch pending item statuses */
936 sqltxt := ' SELECT '||
937 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
938 ' ,MPIS.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
939 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
940 ' ,MPIS.ORGANIZATION_ID "ORGANIZATION ID" '||
941 ' ,MPIS.STATUS_CODE "STATUS CODE" '||
942 ' ,to_char(MPIS.EFFECTIVE_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVE DATE" '||
943 ' ,to_char(MPIS.IMPLEMENTED_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTED DATE"'||
944 ' ,DECODE(MPIS.PENDING_FLAG,null,null,''Y'',''Yes (Y)'',''N'',''No (N)'', '||
945 ' ''OTHER('' || MPIS.PENDING_FLAG || '')'') "PENDING FLAG" '||
946 ' ,to_char(MPIS.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE"'||
947 ' ,MPIS.LAST_UPDATED_BY "LAST UPDATED BY" '||
948 ' ,to_char(MPIS.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'')"CREATION DATE" '||
949 ' ,MPIS.CREATED_BY "CREATED BY" '||
950 ' ,MPIS.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
951 ' ,MPIS.REQUEST_ID "REQUEST ID" '||
952 ' ,MPIS.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
953 ' ,MPIS.PROGRAM_ID "PROGRAM ID" '||
954 ' ,to_char(MPIS.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE"'||
955 ' ,MPIS.REVISION_ID "REVISION ID" '||
956 ' ,MPIS.LIFECYCLE_ID "LIFECYCLE ID" '||
957 ' ,MPIS.PHASE_ID "PHASE ID" '||
958 ' ,MPIS.CHANGE_ID "CHANGE ID" '||
959 ' ,MPIS.CHANGE_LINE_ID "CHANGE LINE ID" '||
960 ' FROM MTL_PENDING_ITEM_STATUS MPIS '||
961 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
962 ' ,MTL_PARAMETERS MP1 '||
963 ' where 1=1 '||
964 ' AND MPIS.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
965 ' AND MPIS.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
966 ' AND MPIS.ORGANIZATION_ID = MP1.ORGANIZATION_ID ';
967
968
969 if l_org_id is not null then
970 sqltxt :=sqltxt||' and mpis.organization_id = '||l_org_id;
971 end if;
972
973 if l_item_id is not null then
974 sqltxt :=sqltxt||' and mpis.inventory_item_id = '||l_item_id;
975 end if;
976
977 sqltxt :=sqltxt||' and rownum < '||row_limit;
978 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, '||
979 ' mpis.effective_date,mpis.status_code ';
980
981 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Pending Statuses ');
982 If (num_rows = row_limit -1 ) Then
983 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/>');
984 End If;
985
986 statusStr := 'SUCCESS';
987 isFatal := 'FALSE';
988 /* End of pending item statuses */
989
990 /* SQL to fetch item cross references details */
991 sqltxt := 'SELECT ' ||
992 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
993 ' ,MCR.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
994 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
995 ' ,MCR.ORGANIZATION_ID "ORGANIZATION ID" '||
996 ' ,MCR.CROSS_REFERENCE_TYPE "CROSS REFERENCE TYPE" '||
997 ' ,MCR.CROSS_REFERENCE "CROSS REFERENCE" '||
998 ' ,to_char(MCR.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
999 ' ,MCR.LAST_UPDATED_BY "LAST UPDATED BY" '||
1000 ' ,to_char(MCR.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1001 ' ,MCR.CREATED_BY "CREATED BY" '||
1002 ' ,MCR.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1003 ' ,MCR.DESCRIPTION "DESCRIPTION" '||
1004 ' ,DECODE(MCR.ORG_INDEPENDENT_FLAG,NULL,NULL,''Y'',''Yes (Y)'',''N'',''No (N)'','||
1005 ' ''OTHER ('' || MCR.ORG_INDEPENDENT_FLAG || '')'') "PENDING FLAG" '||
1006 ' ,MCR.ORG_INDEPENDENT_FLAG "ORG INDEPENDENT FLAG" '||
1007 ' ,MCR.REQUEST_ID "REQUEST ID" '||
1008 ' ,MCR.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1009 ' ,MCR.PROGRAM_ID "PROGRAM ID" '||
1010 ' ,to_char(MCR.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1011 ' ,MCR.ATTRIBUTE1 "ATTRIBUTE1" '||
1012 ' ,MCR.ATTRIBUTE2 "ATTRIBUTE2" '||
1013 ' ,MCR.ATTRIBUTE3 "ATTRIBUTE3" '||
1014 ' ,MCR.ATTRIBUTE4 "ATTRIBUTE4" '||
1015 ' ,MCR.ATTRIBUTE5 "ATTRIBUTE5" '||
1016 ' ,MCR.ATTRIBUTE6 "ATTRIBUTE6" '||
1020 ' ,MCR.ATTRIBUTE10 "ATTRIBUTE10" '||
1017 ' ,MCR.ATTRIBUTE7 "ATTRIBUTE7" '||
1018 ' ,MCR.ATTRIBUTE8 "ATTRIBUTE8" '||
1019 ' ,MCR.ATTRIBUTE9 "ATTRIBUTE9" '||
1021 ' ,MCR.ATTRIBUTE11 "ATTRIBUTE11" '||
1022 ' ,MCR.ATTRIBUTE12 "ATTRIBUTE12" '||
1023 ' ,MCR.ATTRIBUTE13 "ATTRIBUTE13" '||
1024 ' ,MCR.ATTRIBUTE14 "ATTRIBUTE14" '||
1025 ' ,MCR.ATTRIBUTE15 "ATTRIBUTE15" '||
1026 ' ,MCR.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1027 ' ,MCR.UOM_CODE "UOM CODE" '||
1028 ' ,MCR.REVISION_ID "REVISION ID" '||
1029 ' ,MCR.CROSS_REFERENCE_ID "CROSS REFERENCE ID" '||
1030 ' ,MCR.EPC_GTIN_SERIAL "EPC GTIN SERIAL" '||
1031 ' ,MCR.SOURCE_SYSTEM_ID "SOURCE SYSTEM ID" '||
1032 ' ,MCR.START_DATE_ACTIVE "START DATE ACTIVE" '||
1033 ' ,MCR.END_DATE_ACTIVE "END DATE ACTIVE" '||
1034 ' ,MCR.OBJECT_VERSION_NUMBER "OBJECT VERSION NUMBER" '||
1035 ' FROM MTL_CROSS_REFERENCES_B MCR '||
1036 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1037 ' ,MTL_PARAMETERS MP1 '||
1038 ' WHERE 1=1 '||
1039 ' AND MCR.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
1040 ' AND MCR.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1041 ' AND MCR.ORGANIZATION_ID = MP1.ORGANIZATION_ID ';
1042
1043
1044 if l_org_id is not null then
1045 sqltxt :=sqltxt||' and ( ( mcr.organization_id = '||l_org_id||' and org_independent_flag=''N'') '||
1046 ' or ( mcr.organization_id is null and org_independent_flag=''Y'') ) ';
1047 end if;
1048
1049 if l_item_id is not null then
1050 sqltxt :=sqltxt||' and mcr.inventory_item_id = '||l_item_id;
1051 end if;
1052
1053 sqltxt :=sqltxt||' and rownum < '||row_limit;
1054 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, '||
1055 ' mcr.cross_reference_type,mcr.cross_reference ';
1056
1057 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Cross References ');
1058 If (num_rows = row_limit -1 ) Then
1059 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/>');
1060 End If;
1061
1062 statusStr := 'SUCCESS';
1063 isFatal := 'FALSE';
1064 /* End of cross references*/
1065
1066 /* SQL to fetch item translated cross references details */
1067 sqltxt := 'SELECT ' ||
1068 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
1069 ' ,MCRB.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
1070 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1071 ' ,MCRB.ORGANIZATION_ID "ORGANIZATION ID" '||
1072 ' ,MCRB.CROSS_REFERENCE_TYPE "CROSS REFERENCE TYPE" '||
1073 ' ,MCRB.CROSS_REFERENCE "CROSS REFERENCE" '||
1074 ' ,MCRT.CROSS_REFERENCE_ID "CROSS REFERENCE ID" '||
1075 ' ,MCRT.LANGUAGE "LANGUAGE" '||
1076 ' ,MCRT.SOURCE_LANG "SOURCE LANG" '||
1077 ' ,MCRT.DESCRIPTION "DESCRIPTION" '||
1078 ' ,MCRT.CREATION_DATE "CREATION DATE" '||
1079 ' ,MCRT.CREATED_BY "CREATION BY" '||
1080 ' ,MCRT.LAST_UPDATE_DATE "LAST UPDATE DATE" '||
1081 ' ,MCRT.LAST_UPDATED_BY "LAST UPDATED BY" '||
1082 ' ,MCRT.LAST_UPDATE_LOGIN "LAST UPDATED LOGIN" '||
1083 ' FROM MTL_CROSS_REFERENCES_B MCRB '||
1084 ' ,MTL_CROSS_REFERENCES_TL MCRT '||
1085 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1086 ' ,MTL_PARAMETERS MP1 '||
1087 ' WHERE 1=1 '||
1088 ' AND MCRB.CROSS_REFERENCE_ID = MCRT.CROSS_REFERENCE_ID '||
1089 ' AND MCRB.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
1090 ' AND MCRB.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1091 ' AND MCRB.ORGANIZATION_ID = MP1.ORGANIZATION_ID ';
1092
1093 if l_org_id is not null then
1094 sqltxt :=sqltxt||' and ( ( MCRB.organization_id = '||l_org_id||' and org_independent_flag=''N'') '||
1095 ' or ( MCRB.organization_id is null and org_independent_flag=''Y'') ) ';
1096 end if;
1097
1098 if l_item_id is not null then
1099 sqltxt :=sqltxt||' and MCRB.inventory_item_id = '||l_item_id;
1100 end if;
1101
1102 sqltxt :=sqltxt||' and rownum < '||row_limit;
1103 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, '||
1104 ' MCRB.cross_reference_type,MCRB.cross_reference,mcrt.cross_reference_id, mcrt.language ';
1105
1106 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Cross References Transalation Details');
1107 If (num_rows = row_limit -1 ) Then
1108 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/>');
1109 End If;
1110
1111 statusStr := 'SUCCESS';
1112 isFatal := 'FALSE';
1113
1114 /* End of translated cross references*/
1115
1116
1117 /* SQL to fetch customer item xrefs details*/
1118 /* Customer Items xrefs are defined at the master org level. So get the master_org_id of the input org_id.
1119 If no org_id is input then run the script for all master orgs.*/
1120 sqltxt := ' SELECT ' ||
1121 ' MCIX.CUSTOMER_ITEM_NUMBER "CUSTOMER ITEM NUMBER" '||
1122 ' ,MCIX.CUSTOMER_ITEM_ID "CUSTOMER ITEM ID" '||
1123 ' ,MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
1124 ' ,MCIX.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
1125 ' ,MP1.organization_code "MASTER ORGANIZATION CODE" '||
1126 ' ,MCIX.MASTER_ORGANIZATION_ID "MASTER ORGANIZATION ID" '||
1127 ' ,MCIX.RANK "PREFERENCE NUMBER" '||
1128 ' ,DECODE(MCIX.INACTIVE_FLAG,null,null,''Y'',''Yes ( Y )'',''N'',''No ( N )'','||
1129 ' ''OTHER ('' || MCIX.INACTIVE_FLAG || '')'') "INACTIVE FLAG" '||
1133 ' ,MCIX.CREATED_BY "CREATED BY" '||
1130 ' ,to_char(MCIX.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1131 ' ,MCIX.LAST_UPDATED_BY "LAST UPDATED BY" '||
1132 ' ,to_char(MCIX.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1134 ' ,MCIX.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1135 ' ,MCIX.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1136 ' ,MCIX.ATTRIBUTE1 "ATTRIBUTE1" '||
1137 ' ,MCIX.ATTRIBUTE2 "ATTRIBUTE2" '||
1138 ' ,MCIX.ATTRIBUTE3 "ATTRIBUTE3" '||
1139 ' ,MCIX.ATTRIBUTE4 "ATTRIBUTE4" '||
1140 ' ,MCIX.ATTRIBUTE5 "ATTRIBUTE5" '||
1141 ' ,MCIX.ATTRIBUTE6 "ATTRIBUTE6" '||
1142 ' ,MCIX.ATTRIBUTE7 "ATTRIBUTE7" '||
1143 ' ,MCIX.ATTRIBUTE8 "ATTRIBUTE8" '||
1144 ' ,MCIX.ATTRIBUTE9 "ATTRIBUTE9" '||
1145 ' ,MCIX.ATTRIBUTE10 "ATTRIBUTE10" '||
1146 ' ,MCIX.ATTRIBUTE11 "ATTRIBUTE11" '||
1147 ' ,MCIX.ATTRIBUTE12 "ATTRIBUTE12" '||
1148 ' ,MCIX.ATTRIBUTE13 "ATTRIBUTE13" '||
1149 ' ,MCIX.ATTRIBUTE14 "ATTRIBUTE14" '||
1150 ' ,MCIX.ATTRIBUTE15 "ATTRIBUTE15" '||
1151 ' ,MCIX.REQUEST_ID "REQUEST ID" '||
1152 ' ,MCIX.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1153 ' ,MCIX.PROGRAM_ID "PROGRAM ID" '||
1154 ' ,to_char(MCIX.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1155 ' FROM MTL_CUSTOMER_ITEM_XREFS_V mcix '||
1156 ' ,mtl_item_flexfields mif '||
1157 ' ,mtl_parameters mp1 '||
1158 ' where 1=1 '||
1159 ' AND MCIX.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID '||
1160 ' AND MCIX.MASTER_ORGANIZATION_ID = MIF.ORGANIZATION_ID '||
1161 ' AND MCIX.MASTER_ORGANIZATION_ID = MP1.ORGANIZATION_ID ';
1162
1163 if l_org_id is not null then
1164 sqltxt :=sqltxt||' and mcix.master_organization_id = '||
1165 ' (select master_organization_id from mtl_parameters '||
1166 ' where organization_id= '||l_org_id||' ) ';
1167 else /* l_org_id is null */
1168 sqltxt :=sqltxt||' and mcix.master_organization_id in '||
1169 '( select distinct master_organization_id from mtl_parameters )';
1170 end if;
1171
1172 if l_item_id is not null then
1173 sqltxt :=sqltxt||' and mcix.inventory_item_id = '||l_item_id;
1174 end if;
1175
1176 sqltxt :=sqltxt||' and rownum < '||row_limit;
1177 sqltxt :=sqltxt||' order by mp1.organization_code, mif.padded_item_number, mcix.customer_item_number';
1178
1179 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Customer Item Cross References ');
1180 If (num_rows = row_limit -1 ) Then
1181 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/>');
1182 End If;
1183
1184 statusStr := 'SUCCESS';
1185 isFatal := 'FALSE';
1186 /* End of customer item xrefs*/
1187
1188
1189 /* SQL to fetch manufacturer part numbers*/
1190 sqltxt := ' SELECT '||
1191 ' MMPN.MANUFACTURER_NAME "MANUFACTURER NAME" '||
1192 ' ,MMPN.MANUFACTURER_ID "MANUFACTURER ID" '||
1193 ' ,MMPN.MFG_PART_NUM "MFG PART NUM" '||
1194 ' ,MIF.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
1195 ' ,MMPN.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
1196 ' ,MMPN.ITEM_DESCRIPTION "ITEM DESCRIPTION " '||
1197 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE " '||
1198 ' ,MMPN.ORGANIZATION_ID "ORGANIZATION ID" '||
1199 ' ,MMPN.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1200 ' ,MMPN.ATTRIBUTE1 "ATTRIBUTE1" '||
1201 ' ,MMPN.ATTRIBUTE2 "ATTRIBUTE2" '||
1202 ' ,MMPN.ATTRIBUTE3 "ATTRIBUTE3" '||
1203 ' ,MMPN.ATTRIBUTE4 "ATTRIBUTE4" '||
1204 ' ,MMPN.ATTRIBUTE5 "ATTRIBUTE5" '||
1205 ' ,MMPN.ATTRIBUTE6 "ATTRIBUTE6" '||
1206 ' ,MMPN.ATTRIBUTE7 "ATTRIBUTE7" '||
1207 ' ,MMPN.ATTRIBUTE8 "ATTRIBUTE8" '||
1208 ' ,MMPN.ATTRIBUTE9 "ATTRIBUTE9" '||
1209 ' ,MMPN.ATTRIBUTE10 "ATTRIBUTE10" '||
1210 ' ,MMPN.ATTRIBUTE11 "ATTRIBUTE11" '||
1211 ' ,MMPN.ATTRIBUTE12 "ATTRIBUTE12" '||
1212 ' ,MMPN.ATTRIBUTE13 "ATTRIBUTE13" '||
1213 ' ,MMPN.ATTRIBUTE14 "ATTRIBUTE14" '||
1214 ' ,MMPN.ATTRIBUTE15 "ATTRIBUTE15" '||
1215 ' ,MMPN.DESCRIPTION "DESCRIPTION" '||
1216 ' ,to_char(MMPN.START_DATE,''DD-MON-YYYY HH24:MI:SS'') "START DATE" '||
1217 ' ,to_char(MMPN.END_DATE,''DD-MON-YYYY HH24:MI:SS'') "END DATE" '||
1218 ' ,MMPN.FIRST_ARTICLE_STATUS "FIRST ARTICLE STATUS" '||
1219 ' ,MMPN.APPROVAL_STATUS "APPROVAL STATUS" '||
1220 ' FROM MTL_MFG_PART_NUMBERS_ALL_V MMPN '||
1221 ' ,MTL_PARAMETERS MP1 '||
1222 ' ,MTL_ITEM_FLEXFIELDS MIF '||
1223 ' WHERE 1=1 '||
1224 ' AND MMPN.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
1225 ' AND MMPN.INVENTORY_ITEM_ID=MIF.INVENTORY_ITEM_ID '||
1226 ' AND MMPN.ORGANIZATION_ID = MIF.ORGANIZATION_ID ';
1227
1228
1229 /* Mfg part numbers are master org (or Item Master org) specific,
1230 so org filter is applied on master org id of the input org id.
1231 This may not properly handle the case where an organization's
1232 Item Master org is different from its master org. Need to change this filter if necessary*/
1233
1234 if l_org_id is not null then
1235 sqltxt :=sqltxt||' and mmpn.organization_id = '||
1236 ' (select master_organization_id from mtl_parameters '||
1237 ' where organization_id= '||l_org_id||' ) ';
1238 else /* l_org_id is null */
1239 sqltxt :=sqltxt||' and mmpn.organization_id in '||
1240 '( select distinct master_organization_id from mtl_parameters )';
1241 end if;
1242
1243 if l_item_id is not null then
1244 sqltxt :=sqltxt||' and mmpn.inventory_item_id = '||l_item_id;
1245 end if;
1246
1247 sqltxt :=sqltxt||' and rownum < '||row_limit;
1251 If (num_rows = row_limit -1 ) Then
1248 sqltxt :=sqltxt||' order by mp1.organization_code,mmpn.manufacturer_name,mmpn.mfg_part_num ';
1249
1250 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Manufacturer Part Numbers ');
1252 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/>');
1253 End If;
1254
1255 statusStr := 'SUCCESS';
1256 isFatal := 'FALSE';
1257
1258 /* End of manufacturer part numbers*/
1259
1260
1261 /* SQL to fetch item relationships*/
1262 sqltxt := ' SELECT '||
1263 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
1264 ' ,MRI.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
1265 ' ,MRI.INVENTORY_ITEM_DESCRIPTION "ITEM DESCRIPTION " '||
1266 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE " '||
1267 ' ,MRI.ORGANIZATION_ID "ORGANIZATION ID" '||
1268 ' ,MIF2.PADDED_ITEM_NUMBER "RELATED ITEM NUMBER" '||
1269 ' ,MRI.RELATED_ITEM_ID "RELATED ITEM ID" '||
1270 ' ,MRI.RELATED_ITEM_DESCRIPTION "RELATED ITEM DESCRIPTION" '||
1271 ' ,DECODE(MLU_RT.MEANING,null,null, '||
1272 ' (MLU_RT.MEANING ||''('' || MRI.RELATIONSHIP_TYPE_ID|| '')'')) "RELATION TYPE" '||
1273 ' ,MRI.RELATIONSHIP_TYPE_ID "RELATIONSHIP TYPE ID" '||
1274 ' ,DECODE(MRI.RECIPROCAL_FLAG,null,null,''Y'',''Yes ( Y )'',''N'',''No ( N )'', '||
1275 ' ''OTHER ('' || MRI.RECIPROCAL_FLAG || '')'') "RECIPROCAL FLAG" '||
1276 ' ,to_char(MRI.START_DATE,''DD-MON-YYYY HH24:MI:SS'') "START DATE" '||
1277 ' ,to_char(MRI.END_DATE,''DD-MON-YYYY HH24:MI:SS'') "END DATE" '||
1278 ' ,MRI.ATTR_CONTEXT "ATTR CONTEXT" '||
1279 ' ,MRI.ATTR_CHAR1 "ATTR CHAR1" '||
1280 ' ,MRI.ATTR_CHAR2 "ATTR CHAR2" '||
1281 ' ,MRI.ATTR_CHAR3 "ATTR CHAR3" '||
1282 ' ,MRI.ATTR_CHAR4 "ATTR CHAR4" '||
1283 ' ,MRI.ATTR_CHAR5 "ATTR CHAR5" '||
1284 ' ,MRI.ATTR_CHAR6 "ATTR CHAR6" '||
1285 ' ,MRI.ATTR_CHAR7 "ATTR CHAR7" '||
1286 ' ,MRI.ATTR_CHAR8 "ATTR CHAR8" '||
1287 ' ,MRI.ATTR_CHAR9 "ATTR CHAR9" '||
1288 ' ,MRI.ATTR_CHAR10 "ATTR CHAR10" '||
1289 ' ,MRI.ATTR_NUM1 "ATTR NUM1" '||
1290 ' ,MRI.ATTR_NUM2 "ATTR NUM2" '||
1291 ' ,MRI.ATTR_NUM3 "ATTR NUM3" '||
1292 ' ,MRI.ATTR_NUM4 "ATTR NUM4" '||
1293 ' ,MRI.ATTR_NUM5 "ATTR NUM5" '||
1294 ' ,MRI.ATTR_NUM6 "ATTR NUM6" '||
1295 ' ,MRI.ATTR_NUM7 "ATTR NUM7" '||
1296 ' ,MRI.ATTR_NUM8 "ATTR NUM8" '||
1297 ' ,MRI.ATTR_NUM9 "ATTR NUM9" '||
1298 ' ,MRI.ATTR_NUM10 "ATTR NUM10" '||
1299 ' ,to_char(MRI.ATTR_DATE1,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE1" '||
1300 ' ,to_char(MRI.ATTR_DATE2,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE2" '||
1301 ' ,to_char(MRI.ATTR_DATE3,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE3" '||
1302 ' ,to_char(MRI.ATTR_DATE4,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE4" '||
1303 ' ,to_char(MRI.ATTR_DATE5,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE5" '||
1304 ' ,to_char(MRI.ATTR_DATE6,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE6" '||
1305 ' ,to_char(MRI.ATTR_DATE7,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE7" '||
1306 ' ,to_char(MRI.ATTR_DATE8,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE8" '||
1307 ' ,to_char(MRI.ATTR_DATE9,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE9" '||
1308 ' ,to_char(MRI.ATTR_DATE10,''DD-MON-YYYY HH24:MI:SS'') "ATTR DATE10" '||
1309 ' ,DECODE(MRI.PLANNING_ENABLED_FLAG,null,null,''Y'',''Yes ( Y )'',''N'',''No ( N )'', '||
1310 ' ''OTHER ('' || MRI.PLANNING_ENABLED_FLAG || '')'') "PLANNING ENABLED FLAG" '||
1311 ' FROM '||
1312 ' MTL_RELATED_ITEMS_ALL_V MRI '||
1313 ' ,MFG_LOOKUPS MLU_RT '||
1314 ' ,MTL_PARAMETERS MP1 '||
1315 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1316 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
1317 ' where 1=1 '||
1318 ' AND MRI.INVENTORY_ITEM_ID=MIF1.INVENTORY_ITEM_ID '||
1319 ' AND MRI.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1320 ' AND MRI.RELATED_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
1321 ' AND MRI.ORGANIZATION_ID = MIF2.ORGANIZATION_ID '||
1322 ' AND MRI.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
1323 ' AND MRI.relationship_type_id=MLU_RT.LOOKUP_CODE(+) '||
1324 ' AND ''MTL_RELATIONSHIP_TYPES''=MLU_RT.LOOKUP_TYPE(+) ';
1325
1326 /* Item Relationships are master org (or Item Master org) specific,
1327 so org filter is applied on master org id of the input org id.
1328 This may not properly handle the case where an organization's
1329 Item Master org is different from its master org. Need to change this filter if necessary*/
1330
1331 if l_org_id is not null then
1332 sqltxt :=sqltxt||' and mri.organization_id = '||
1333 ' (select master_organization_id from mtl_parameters '||
1334 ' where organization_id= '||l_org_id||' ) ';
1335 else /* l_org_id is null */
1336 sqltxt :=sqltxt||' and mri.organization_id in '||
1337 '( select distinct master_organization_id from mtl_parameters )';
1338 end if;
1339
1340 if l_item_id is not null then
1341 sqltxt :=sqltxt||' and (mri.inventory_item_id = '||l_item_id||
1342 ' or mri.related_item_id = '||l_item_id||' ) ';
1343 end if;
1344
1345 sqltxt :=sqltxt||' and rownum < '||row_limit;
1349 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Relationships ');
1346 sqltxt :=sqltxt||' order by mp1.organization_code,mif1.padded_item_number, '||
1347 ' mif2.padded_item_number,mri.relationship_type_id ';
1348
1350 If (num_rows = row_limit -1 ) Then
1351 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/>');
1352 End If;
1353
1354 statusStr := 'SUCCESS';
1355 isFatal := 'FALSE';
1356
1357 /* End of item relationships*/
1358
1359 /* SQL to fetch item onhand quantities */
1360
1361 sqltxt := ' SELECT mif.padded_item_number "Item Number" '||
1362 ' , moq.inventory_item_id "Item Id" '||
1363 ' , mp1.organization_code "ORGANIZATION CODE"'||
1364 ' , moq.organization_id "Organization Id" '||
1365 ' , SUM( moq.transaction_quantity ) "Txn Qty" '||
1366 ' , moq.subinventory_code "Subinv" '||
1367 ' , mil.concatenated_segments "Locator" '||
1368 ' , moq.locator_id "Locator Id" '||
1369 ' , mil.description "Locator Desc" '||
1370 ' , moq.revision "Revision" '||
1371 ' , moq.lot_number "Lot Number" '||
1372 ' FROM mtl_onhand_quantities_detail moq '||
1373 ' , mtl_item_flexfields mif '||
1374 ' , mtl_item_locations_kfv mil '||
1375 ' , mtl_parameters mp1 '||
1376 ' WHERE 1=1 '||
1377 ' AND moq.inventory_item_id = mif.inventory_item_id(+) '||
1378 ' AND moq.organization_id = mif.organization_id(+) '||
1379 ' AND moq.organization_id = mil.organization_id(+) '||
1380 ' AND moq.locator_id = mil.inventory_location_id(+) '||
1381 ' AND moq.organization_id = mp1.organization_id ';
1382
1383 if l_org_id is not null then
1384 sqltxt :=sqltxt||' and moq.organization_id = '||l_org_id;
1385 end if;
1386
1387 if l_item_id is not null then
1388 sqltxt :=sqltxt||' and moq.inventory_item_id = '||l_item_id;
1389 end if;
1390
1391 sqltxt := sqltxt ||' GROUP BY mif.padded_item_number, moq.inventory_item_id ' ||
1392 ' , mp1.organization_code ,moq.organization_id ' ||
1393 ' , moq.subinventory_code, moq.locator_id ' ||
1394 ' , mil.concatenated_segments, mil.description ' ||
1395 ' , moq.revision, moq.lot_number ' ||
1396 ' ORDER BY mp1.organization_code,mif.padded_item_number ' ||
1397 ' , moq.subinventory_code, moq.locator_id ' ||
1398 ' , mil.concatenated_segments, mil.description ' ||
1399 ' , moq.revision, moq.lot_number ';
1400
1401 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,' Item Onhand Quantity ');
1402
1403 statusStr := 'SUCCESS';
1404 isFatal := 'FALSE';
1405 /* End of item relationships*/
1406
1407 /* SQL to fetch default category sets */
1408 sqltxt := ' SELECT '||
1409 ' MDCS.FUNCTIONAL_AREA_DESC "FUNCTIONAL AREA", '||
1410 ' MDCS.FUNCTIONAL_AREA_ID "FUNCTIONAL AREA ID", '||
1411 ' MDCS.CATEGORY_SET_NAME "CATEGORY SET NAME", '||
1412 ' MDCS.CATEGORY_SET_ID "CATEGORY SET ID", '||
1413 ' to_char(MDCS.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1414 ' MDCS.LAST_UPDATED_BY "LAST UPDATED BY", '||
1415 ' to_char(MDCS.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1416 ' MDCS.CREATED_BY "CREATED BY", '||
1417 ' MDCS.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1418 ' MDCS.REQUEST_ID "REQUEST ID", '||
1419 ' MDCS.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
1420 ' MDCS.PROGRAM_ID "PROGRAM ID", '||
1421 ' to_char(MDCS.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1422 ' FROM MTL_DEFAULT_CATEGORY_SETS_FK_V mdcs where 1=1 ';
1423
1424 sqltxt :=sqltxt||' and rownum < '||row_limit;
1425 sqltxt :=sqltxt||' order by mdcs.functional_area_id,mdcs.category_set_name';
1426
1427 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Default Category Sets ');
1428 If (num_rows = row_limit -1 ) Then
1429 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/>');
1430 End If;
1431
1432 statusStr := 'SUCCESS';
1433 isFatal := 'FALSE';
1434
1435 /* End default category sets */
1436
1437 /* SQL to fetch category sets */
1438 sqltxt := ' SELECT ' ||
1439 ' MCSVL.CATEGORY_SET_NAME "CATEGORY SET NAME" '||
1440 ' ,MCSVL.CATEGORY_SET_ID "CATEGORY SET ID" '||
1441 ' ,FIFSV.ID_FLEX_STRUCTURE_NAME "STRUCTURE NAME" '||
1442 ' ,FIFSV.ID_FLEX_STRUCTURE_CODE "STRUCTURE CODE" '||
1443 ' ,MCSVL.STRUCTURE_ID "STRUCTURE ID" '||
1444 ' ,DECODE(MCSVL.VALIDATE_FLAG,null,null,''Y'',''Yes ( Y )'',''N'',''No ( N )'', '||
1445 ' ''OTHER ('' || MCSVL.VALIDATE_FLAG || '')'') "VALIDATE FLAG" '||
1446 ' ,DECODE(MCSVL.CONTROL_LEVEL,NULL,NULL,1,''Master (1)'',2,''Org (2)'', '||
1447 ' ''OTHER ('' || MCSVL.CONTROL_LEVEL || '')'') "CONTROL LEVEL" '||
1448 ' ,mcv.CATEGORY_CONCAT_SEGS "DEFAULT CATEGORY NAME" '||
1449 ' ,NVL(MCSVL.DEFAULT_CATEGORY_ID,NULL) "DEFAULT CATEGORY ID" '||
1450 ' ,to_char(MCSVL.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1451 ' ,MCSVL.LAST_UPDATED_BY "LAST UPDATED BY" '||
1455 ' ,MCSVL.REQUEST_ID "REQUEST ID" '||
1452 ' ,to_char(MCSVL.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1453 ' ,MCSVL.CREATED_BY "CREATED BY" '||
1454 ' ,MCSVL.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1456 ' ,MCSVL.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1457 ' ,MCSVL.PROGRAM_ID "PROGRAM ID" '||
1458 ' ,to_char(MCSVL.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1459 ' ,DECODE(MCSVL.MULT_ITEM_CAT_ASSIGN_FLAG,null,null,''Y'',''Yes ( Y )'',''N'',''No ( N )'', '||
1460 ' ''OTHER ('' || MCSVL.MULT_ITEM_CAT_ASSIGN_FLAG || '')'') "MULT ITEM CAT ASSIGN FLAG" '||
1461 ' ,DECODE(MCSVL.CONTROL_LEVEL_UPDATEABLE_FLAG,null,null,''Y ( Y )'',''Yes'',''N'',''No ( N )'', '||
1462 ' ''OTHER ('' || MCSVL.CONTROL_LEVEL_UPDATEABLE_FLAG || '')'') "CONTROL LEVEL UPDATEABLE FLAG" '||
1463 ' ,DECODE(MCSVL.MULT_ITEM_CAT_UPDATEABLE_FLAG,null,null,''Y ( Y )'',''Yes'',''N'',''No ( N )'', '||
1464 ' ''OTHER ('' || MCSVL.MULT_ITEM_CAT_UPDATEABLE_FLAG || '')'') "MULT ITEM CAT UPDATEABLE FLAG" '||
1465 ' ,DECODE(MCSVL.HIERARCHY_ENABLED,null,null,''Y ( Y )'',''Yes'',''N'',''No ( N )'', '||
1466 ' ''OTHER ('' || MCSVL.HIERARCHY_ENABLED || '')'') "HIERARCHY ENABLED" '||
1467 ' ,DECODE(MCSVL.VALIDATE_FLAG_UPDATEABLE_FLAG,null,null,''Y ( Y )'',''Yes'',''N '',''No ( N )'', '||
1468 ' ''OTHER ('' || MCSVL.VALIDATE_FLAG_UPDATEABLE_FLAG || '')'') "VALIDATE FLAG UPDATEABLE FLAG" '||
1469 ' FROM mtl_category_sets_vl mcsvl '||
1470 ' ,mtl_categories_v mcv '||
1471 ' ,fnd_id_flex_structures_vl fifsv '||
1472 ' where 1=1 '||
1473 ' AND mcsvl.default_category_id = mcv.category_id(+) '||
1474 ' AND mcsvl.structure_id =fifsv.id_flex_num '||
1475 ' AND fifsv.id_flex_code = ''MCAT'' ';
1476
1477 sqltxt :=sqltxt||' and rownum < '||row_limit;
1478 sqltxt :=sqltxt||' order by mcsvl.category_set_name,fifsv.id_flex_structure_name';
1479
1480 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Category Sets ');
1481 If (num_rows = row_limit -1 ) Then
1482 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/>');
1483 End If;
1484
1485 statusStr := 'SUCCESS';
1486 isFatal := 'FALSE';
1487 /* End category sets */
1488
1489 /* SQL to fetch item category assignments */
1490 sqltxt := ' SELECT ' ||
1491 ' MIF1.PADDED_ITEM_NUMBER "ITEM NUMBER" '||
1492 ' ,MIC.INVENTORY_ITEM_ID "INVENTORY ITEM ID" '||
1493 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1494 ' ,MIC.ORGANIZATION_ID "ORGANIZATION ID" '||
1495 ' ,MIC.CATEGORY_SET_NAME "CATEGORY SET NAME" '||
1496 ' ,MIC.CATEGORY_SET_ID "CATEGORY SET ID" '||
1497 ' ,MIC.CATEGORY_CONCAT_SEGS "CATEGORY NAME" '||
1498 ' ,MIC.CATEGORY_ID "CATEGORY ID" '||
1499 ' ,to_char(MIC.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1500 ' ,MIC.LAST_UPDATED_BY "LAST UPDATED BY" '||
1501 ' ,to_char(MIC.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1502 ' ,MIC.CREATED_BY "CREATED BY" '||
1503 ' ,MIC.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1504 ' ,MIC.REQUEST_ID "REQUEST ID" '||
1505 ' ,MIC.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1506 ' ,MIC.PROGRAM_ID "PROGRAM ID" '||
1507 ' ,to_char(MIC.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1508 ' FROM '||
1509 ' mtl_item_categories_v MIC '||
1510 ' ,mtl_parameters MP1 '||
1511 ' ,mtl_item_flexfields MIF1 '||
1512 ' where 1=1 '||
1513 ' AND MIC.INVENTORY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
1514 ' AND MIC.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1515 ' AND MIF1.ORGANIZATION_ID = MP1.ORGANIZATION_ID ';
1516
1517
1518 if l_org_id is not null then
1519 sqltxt :=sqltxt||' and mic.organization_id = '||l_org_id;
1520 end if;
1521
1522 if l_item_id is not null then
1523 sqltxt :=sqltxt||' and mic.inventory_item_id = '||l_item_id;
1524 end if;
1525
1526 sqltxt :=sqltxt||' and rownum < '||row_limit;
1527 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, '||
1528 ' mic.category_set_name, mic.category_concat_segs ';
1529
1530 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Item Category Assignments ');
1531 If (num_rows = row_limit -1 ) Then
1532 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/>');
1533 End If;
1534
1535 statusStr := 'SUCCESS';
1536 isFatal := 'FALSE';
1537 /* End of item category assignments */
1538
1539
1540 /* Start of BOM, RTG scripts*/
1541 /* Get the application installation info. References to Data Dictionary Objects without schema name
1542 included in WHERE predicate are not allowed (GSCC Check: file.sql.47). Schema name has to be passed
1543 as an input parameter to JTF_DIAGNOSTIC_COREAPI.Column_Exists API. */
1544
1545 l_ret_status := fnd_installation.get_app_info ('BOM'
1546 , l_status
1547 , l_industry
1548 , l_oracle_schema
1549 );
1550 /*JTF_DIAGNOSTIC_COREAPI.Line_Out(' l_oracle_schema: '||l_oracle_schema);*/
1551
1552
1553 /* SQL to Fetch Bill Header Details */
1554 sqltxt := 'SELECT ' ||
1555 ' MIF1.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER" '||
1559 ' ,bsb.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR" '||
1556 ' ,bsb.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID" '||
1557 ' ,MP1.ORGANIZATION_CODE "ORGANIZATION CODE" '||
1558 ' ,bsb.ORGANIZATION_ID "ORGANIZATION ID " '||
1560 ' ,to_char(bsb.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1561 ' ,bsb.LAST_UPDATED_BY "LAST UPDATED BY" '||
1562 ' ,to_char(bsb.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1563 ' ,bsb.CREATED_BY "CREATED BY" '||
1564 ' ,bsb.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1565 ' ,bsb.SPECIFIC_ASSEMBLY_COMMENT "SPECIFIC ASSEMBLY COMMENT" '||
1566 ' ,bsb.PENDING_FROM_ECN "PENDING FROM ECN" '||
1567 ' ,bsb.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1568 ' ,bsb.ATTRIBUTE1 "ATTRIBUTE1" '||
1569 ' ,bsb.ATTRIBUTE2 "ATTRIBUTE2" '||
1570 ' ,bsb.ATTRIBUTE3 "ATTRIBUTE3" '||
1571 ' ,bsb.ATTRIBUTE4 "ATTRIBUTE4" '||
1572 ' ,bsb.ATTRIBUTE5 "ATTRIBUTE5" '||
1573 ' ,bsb.ATTRIBUTE6 "ATTRIBUTE6" '||
1574 ' ,bsb.ATTRIBUTE7 "ATTRIBUTE7" '||
1575 ' ,bsb.ATTRIBUTE8 "ATTRIBUTE8" '||
1576 ' ,bsb.ATTRIBUTE9 "ATTRIBUTE9" '||
1577 ' ,bsb.ATTRIBUTE10 "ATTRIBUTE10" '||
1578 ' ,bsb.ATTRIBUTE11 "ATTRIBUTE11" '||
1579 ' ,bsb.ATTRIBUTE12 "ATTRIBUTE12" '||
1580 ' ,bsb.ATTRIBUTE13 "ATTRIBUTE13" '||
1581 ' ,bsb.ATTRIBUTE14 "ATTRIBUTE14" '||
1582 ' ,bsb.ATTRIBUTE15 "ATTRIBUTE15" '||
1583 ' ,DECODE(bsb.ASSEMBLY_TYPE,NULL,NULL,1,''Manufacturing (1)'', 2, ''Engineering (2)'', '||
1584 ' ''Other ('' ||bsb.ASSEMBLY_TYPE|| '')'') "ASSEMBLY TYPE" '||
1585 ' ,MIF2.PADDED_ITEM_NUMBER "ITEM NUMBER (COMMON)" '||
1586 ' ,NVL(bsb.COMMON_ASSEMBLY_ITEM_ID,bsb.ASSEMBLY_ITEM_ID) "COMMON ASSEMBLY ITEM ID" '||
1587 ' ,MP2.ORGANIZATION_CODE "ORGANIZATION CODE (COMMON)" '||
1588 ' ,NVL(bsb.COMMON_ORGANIZATION_ID,bsb.ORGANIZATION_ID) "COMMON ORGANIZATION ID" '||
1589 ' ,bsb.COMMON_BILL_SEQUENCE_ID "COMMON BILL SEQUENCE ID" '||
1590 ' ,bsb.BILL_SEQUENCE_ID "BILL SEQUENCE ID" '||
1591 ' ,bsb.REQUEST_ID "REQUEST ID" '||
1592 ' ,bsb.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1593 ' ,bsb.PROGRAM_ID "PROGRAM ID" '||
1594 ' ,to_char(bsb.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1595 ' ,bsb.NEXT_EXPLODE_DATE "NEXT EXPLODE DATE" '||
1596 ' ,bsb.PROJECT_ID "PROJECT ID" '||
1597 ' ,bsb.TASK_ID "TASK ID" '||
1598 ' ,bsb.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1599 ' ,bsb.STRUCTURE_TYPE_ID "STRUCTURE TYPE ID" '||
1600 ' ,bsb.IMPLEMENTATION_DATE "IMPLEMENTATION DATE" '||
1601 ' ,bsb.OBJ_NAME "OBJ NAME" '||
1602 ' ,bsb.PK1_VALUE "PK1 VALUE" '||
1603 ' ,bsb.PK2_VALUE "PK2 VALUE" '||
1604 ' ,bsb.PK3_VALUE "PK3 VALUE" '||
1605 ' ,bsb.PK4_VALUE "PK4 VALUE" '||
1606 ' ,bsb.PK5_VALUE "PK5 VALUE" '||
1607 ' ,bsb.EFFECTIVITY_CONTROL "EFFECTIVITY CONTROL" '||
1608 ' ,bsb.IS_PREFERRED "IS PREFERRED" '||
1609 ' ,bsb.SOURCE_BILL_SEQUENCE_ID "SOURCE BILL SEQUENCE ID" '||
1610 ' FROM '||
1611 ' bom_structures_b bsb '||
1612 ' ,mtl_parameters mp1 '||
1613 ' ,mtl_parameters mp2 '||
1614 ' ,mtl_item_flexfields mif1 '||
1615 ' ,mtl_item_flexfields mif2 '||
1616 ' WHERE 1=1 '||
1617 ' AND bsb.assembly_item_id = mif1.inventory_item_id '||
1618 ' AND bsb.organization_id = mif1.organization_id '||
1619 ' AND mif1.organization_id = mp1.organization_id '||
1620 ' AND nvl(bsb.common_assembly_item_id,bsb.assembly_item_id) = mif2.inventory_item_id '||
1621 ' AND nvl(bsb.common_organization_id, bsb.organization_id) = mif2.organization_id '||
1622 ' AND mif2.organization_id = mp2.organization_id ';
1623
1624 if l_org_id is not null then
1625 sqltxt :=sqltxt||' and bsb.organization_id = '||l_org_id;
1626 end if;
1627
1628 if l_item_id is not null then
1629 sqltxt :=sqltxt||' and bsb.assembly_item_id = '||l_item_id;
1630 end if;
1631
1632 sqltxt :=sqltxt||' and rownum < '||row_limit;
1633 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, bsb.alternate_bom_designator';
1634 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Bill Headers ');
1635 If (num_rows = row_limit -1 ) Then
1636 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/>');
1637 End If;
1638 statusStr := 'SUCCESS';
1639 isFatal := 'FALSE';
1640
1641 /* SQL to Fetch Bill Component Details */
1642
1643 sqltxt := ' SELECT '||
1644 ' MIF1.PADDED_ITEM_NUMBER "Item Number (Assembly)" '||
1645 ' ,BSB.ASSEMBLY_ITEM_ID "Assy Item Id" '||
1646 ' ,MP1.ORGANIZATION_CODE "Org Code" '||
1647 ' ,BSB.ORGANIZATION_ID "ORGANIZATION ID" '||
1648 ' ,BSB.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR" '||
1649 ' ,BCB.BILL_SEQUENCE_ID "BILL SEQUENCE ID" '||
1650 ' ,MIF2.PADDED_ITEM_NUMBER "Item Number (COMPONENT)" '||
1651 ' ,BCB.COMPONENT_ITEM_ID "COMPONENT ITEM ID" '||
1652 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
1656 ' ,NVL(BCB.COMPONENT_YIELD_FACTOR,0) "COMPONENT YIELD FACTOR" '||
1653 ' ,BCB.OPERATION_SEQ_NUM "OPERATION SEQ NUM" '||
1654 ' ,BCB.ITEM_NUM "ITEM NUM" '||
1655 ' ,NVL(BCB.COMPONENT_QUANTITY,0) "COMPONENT QUANTITY" '||
1657 ' ,BCB.COMPONENT_REMARKS "COMPONENT REMARKS" '||
1658 ' ,to_char(BCB.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE " '||
1659 ' ,BCB.CHANGE_NOTICE "CHANGE NOTICE" '||
1660 ' ,to_char(BCB.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE" '||
1661 ' ,to_char(BCB.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE" '||
1662 ' ,BCB.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1663 ' ,BCB.ATTRIBUTE1 "ATTRIBUTE1" '||
1664 ' ,BCB.ATTRIBUTE2 "ATTRIBUTE2" '||
1665 ' ,BCB.ATTRIBUTE3 "ATTRIBUTE3" '||
1666 ' ,BCB.ATTRIBUTE4 "ATTRIBUTE4" '||
1667 ' ,BCB.ATTRIBUTE5 "ATTRIBUTE5" '||
1668 ' ,BCB.ATTRIBUTE6 "ATTRIBUTE6" '||
1669 ' ,BCB.ATTRIBUTE7 "ATTRIBUTE7" '||
1670 ' ,BCB.ATTRIBUTE8 "ATTRIBUTE8" '||
1671 ' ,BCB.ATTRIBUTE9 "ATTRIBUTE9" '||
1672 ' ,BCB.ATTRIBUTE10 "ATTRIBUTE10" '||
1673 ' ,BCB.ATTRIBUTE11 "ATTRIBUTE11" '||
1674 ' ,BCB.ATTRIBUTE12 "ATTRIBUTE12" '||
1675 ' ,BCB.ATTRIBUTE13 "ATTRIBUTE13" '||
1676 ' ,BCB.ATTRIBUTE14 "ATTRIBUTE14" '||
1677 ' ,BCB.ATTRIBUTE15 "ATTRIBUTE15" '||
1678 ' ,BCB.PLANNING_FACTOR "PLANNING FACTOR" '||
1679 ' ,DECODE(BCB.QUANTITY_RELATED,null,null,1,''Yes (1)'',2,''No (2)'', '||
1680 ' ''OTHER ('' || BCB.QUANTITY_RELATED || '')'') "QUANTITY RELATED" '||
1681 ' ,DECODE(MLU_SO.MEANING,null,null, '||
1682 ' (MLU_SO.MEANING || ''('' || BCB.SO_BASIS || '')'')) "SO BASIS" '||
1683 ' ,DECODE(BCB.OPTIONAL,null,null,1,''Yes (1)'',2,''No (2)'', '||
1684 ' ''OTHER ('' || BCB.OPTIONAL || '')'') "OPTIONAL" '||
1685 ' ,DECODE(BCB.MUTUALLY_EXCLUSIVE_OPTIONS,null,null,1,''Yes (1)'',2,''No (2)'', '||
1686 ' ''OTHER ('' || BCB.MUTUALLY_EXCLUSIVE_OPTIONS || '')'') "MUTUALLY EXCLUSIVE OPTIONS" '||
1687 ' ,DECODE(BCB.INCLUDE_IN_COST_ROLLUP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1688 ' ''OTHER ('' || BCB.INCLUDE_IN_COST_ROLLUP || '')'') "INCLUDE IN COST ROLLUP" '||
1689 ' ,DECODE(BCB.CHECK_ATP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1690 ' ''OTHER ('' || BCB.CHECK_ATP || '')'') "CHECK ATP" '||
1691 ' ,DECODE(BCB.SHIPPING_ALLOWED,null,null,1,''Yes (1)'',2,''No (2)'', '||
1692 ' ''OTHER ('' || BCB.SHIPPING_ALLOWED || '')'') "SHIPPING ALLOWED" '||
1693 ' ,DECODE(BCB.REQUIRED_TO_SHIP,null,null,1,''Yes (1)'',2,''No (2)'', '||
1694 ' ''OTHER ('' || BCB.REQUIRED_TO_SHIP || '')'') "REQUIRED TO SHIP" '||
1695 ' ,DECODE(BCB.REQUIRED_FOR_REVENUE,null,null,1,''Yes (1)'',2,''No (2)'', '||
1696 ' ''OTHER ('' || BCB.REQUIRED_FOR_REVENUE || '')'') "REQUIRED FOR REVENUE" '||
1697 ' ,DECODE(BCB.INCLUDE_ON_SHIP_DOCS,null,null,1,''Yes (1)'',2,''No (2)'', '||
1698 ' ''OTHER ('' || BCB.INCLUDE_ON_SHIP_DOCS || '')'') "INCLUDE ON SHIP DOCS" '||
1699 ' ,DECODE(BCB.INCLUDE_ON_BILL_DOCS,null,null,1,''Yes (1)'',2,''No (2)'', '||
1700 ' ''OTHER ('' || BCB.INCLUDE_ON_BILL_DOCS || '')'') "INCLUDE ON BILL DOCS" '||
1701 ' ,BCB.LOW_QUANTITY "LOW QUANTITY" '||
1702 ' ,BCB.HIGH_QUANTITY "HIGH QUANTITY" '||
1703 ' ,DECODE(MLU_ACD.MEANING,null,null, '||
1704 ' (MLU_ACD.MEANING || '' ('' || BCB.ACD_TYPE || '')'')) "ACD TYPE" '||
1705 ' ,BCB.OLD_COMPONENT_SEQUENCE_ID "OLD COMPONENT SEQUENCE ID" '||
1706 ' ,DECODE(MLU_WIP.MEANING,null,null, '||
1707 ' (MLU_WIP.MEANING || ''('' || BCB.WIP_SUPPLY_TYPE || '')'')) "WIP SUPPLY TYPE" '||
1708 ' ,BCB.PICK_COMPONENTS "PICK COMPONENTS" '||
1709 ' ,BCB.SUPPLY_SUBINVENTORY "SUPPLY SUBINVENTORY" '||
1710 ' ,BCB.SUPPLY_LOCATOR_ID "SUPPLY LOCATOR ID" '||
1711 ' ,BCB.OPERATION_LEAD_TIME_PERCENT "OPERATION LEAD TIME PERCENT" '||
1712 ' ,BCB.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
1713 ' ,BCB.COST_FACTOR "COST FACTOR" '||
1714 ' ,DECODE(MLU_BIT.MEANING,null,null, '||
1715 ' (MLU_BIT.MEANING || ''('' || BCB.BOM_ITEM_TYPE || '')'')) "BOM ITEM TYPE" '||
1716 ' ,BCB.FROM_END_ITEM_UNIT_NUMBER "FROM END ITEM UNIT NUMBER" '||
1717 ' ,BCB.TO_END_ITEM_UNIT_NUMBER "TO END ITEM UNIT NUMBER" '||
1718 ' ,BCB.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1719 ' ,BCB.ECO_FOR_PRODUCTION "ECO FOR PRODUCTION" '||
1720 ' ,BCB.ENFORCE_INT_REQUIREMENTS "ENFORCE INT REQUIREMENTS" '||
1721 ' ,BCB.COMPONENT_ITEM_REVISION_ID "COMPONENT ITEM REVISION ID" '||
1722 ' ,BCB.DELETE_GROUP_NAME "DELETE GROUP NAME" '||
1723 ' ,BCB.DG_DESCRIPTION "DG DESCRIPTION" '||
1724 ' ,BCB.OPTIONAL_ON_MODEL "OPTIONAL ON MODEL" '||
1725 ' ,BCB.PARENT_BILL_SEQ_ID "PARENT BILL SEQ ID" '||
1726 ' ,BCB.MODEL_COMP_SEQ_ID "MODEL COMP SEQ ID" '||
1727 ' ,BCB.PLAN_LEVEL "PLAN LEVEL" '||
1728 ' ,BCB.FROM_BILL_REVISION_ID "FROM BILL REVISION ID" '||
1729 ' ,BCB.TO_BILL_REVISION_ID "TO BILL REVISION ID" '||
1733 ' ,to_char(BCB.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1730 ' ,BCB.AUTO_REQUEST_MATERIAL "AUTO REQUEST MATERIAL" '||
1731 ' ,to_char(BCB.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE " '||
1732 ' ,BCB.LAST_UPDATED_BY "LAST UPDATED BY" '||
1734 ' ,BCB.CREATED_BY "CREATED BY" '||
1735 ' ,BCB.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1736 ' ,BCB.REQUEST_ID "REQUEST ID" '||
1737 ' ,BCB.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1738 ' ,BCB.PROGRAM_ID "PROGRAM ID" '||
1739 ' ,to_char(BCB.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1740 ' ,BCB.SUGGESTED_VENDOR_NAME "SUGGESTED VENDOR NAME" '||
1741 ' ,BCB.VENDOR_ID "VENDOR ID" '||
1742 ' ,BCB.UNIT_PRICE "UNIT PRICE" '||
1743 ' ,BCB.OBJ_NAME "OBJ NAME" '||
1744 ' ,BCB.PK1_VALUE "PK1 VALUE" '||
1745 ' ,BCB.PK2_VALUE "PK2 VALUE" '||
1746 ' ,BCB.PK3_VALUE "PK3 VALUE" '||
1747 ' ,BCB.PK4_VALUE "PK4 VALUE" '||
1748 ' ,BCB.PK5_VALUE "PK5 VALUE" '||
1749 ' ,BCB.FROM_END_ITEM_REV_ID "FROM END ITEM REV ID" '||
1750 ' ,BCB.TO_END_ITEM_REV_ID "TO END ITEM REV ID" '||
1751 ' ,BCB.OVERLAPPING_CHANGES "OVERLAPPING CHANGES" '||
1752 ' ,BCB.FROM_OBJECT_REVISION_ID "FROM OBJECT REVISION ID" '||
1753 ' ,BCB.FROM_MINOR_REVISION_ID "FROM MINOR REVISION ID" '||
1754 ' ,BCB.TO_OBJECT_REVISION_ID "TO OBJECT REVISION ID" '||
1755 ' ,BCB.TO_MINOR_REVISION_ID "TO MINOR REVISION ID" '||
1756 ' ,BCB.FROM_END_ITEM_MINOR_REV_ID "FROM END ITEM MINOR REV ID" '||
1757 ' ,BCB.TO_END_ITEM_MINOR_REV_ID "TO END ITEM MINOR REV ID" '||
1758 ' ,BCB.COMPONENT_MINOR_REVISION_ID "COMPONENT MINOR REVISION ID" '||
1759 ' ,BCB.FROM_STRUCTURE_REVISION_CODE "FROM STRUCTURE REVISION CODE" '||
1760 ' ,BCB.TO_STRUCTURE_REVISION_CODE "TO STRUCTURE REVISION CODE" '||
1761 ' ,BCB.FROM_END_ITEM_STRC_REV_ID "FROM END ITEM STRC REV ID" '||
1762 ' ,BCB.TO_END_ITEM_STRC_REV_ID "TO END ITEM STRC REV ID" '||
1763 ' ,BCB.BASIS_TYPE "BASIS TYPE" '||
1764 ' ,BCB.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
1765 ' FROM BOM_STRUCTURES_B BSB '||
1766 ' ,BOM_COMPONENTS_B BCB '||
1767 ' ,MTL_PARAMETERS MP1 '||
1768 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1769 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
1770 ' ,MFG_LOOKUPS MLU_SO '||
1771 ' ,MFG_LOOKUPS MLU_ACD '||
1772 ' ,MFG_LOOKUPS MLU_WIP '||
1773 ' ,MFG_LOOKUPS MLU_BIT '||
1774 ' WHERE 1 = 1 '||
1775 ' AND BSB.assembly_item_id = MIF1.inventory_item_id '||
1776 ' AND BSB.organization_id = MIF1.organization_id '||
1777 ' AND MIF1.organization_id = MP1.organization_id '||
1778 ' AND BCB.component_item_id = MIF2.inventory_item_id '||
1779 ' AND BSB.organization_id = MIF2.organization_id '||
1780 ' AND BSB.bill_sequence_id = BCB.bill_sequence_id '||
1781 ' AND BCB.SO_BASIS=MLU_SO.LOOKUP_CODE(+) AND ''BOM_SO_BASIS''=MLU_SO.LOOKUP_TYPE(+) '||
1782 ' AND BCB.ACD_TYPE=MLU_ACD.LOOKUP_CODE(+) AND ''ECG_ACTION''=MLU_ACD.LOOKUP_TYPE(+) '||
1783 ' AND BCB.WIP_SUPPLY_TYPE=MLU_WIP.LOOKUP_CODE(+) AND ''WIP_SUPPLY''=MLU_WIP.LOOKUP_TYPE(+) '||
1784 ' AND BCB.BOM_ITEM_TYPE=MLU_BIT.LOOKUP_CODE(+) AND ''BOM_ITEM_TYPE''=MLU_BIT.LOOKUP_TYPE(+) ';
1785
1786 if l_org_id is not null then
1787 sqltxt :=sqltxt||' and BSB.organization_id = '||l_org_id;
1788 end if;
1789
1790 if l_item_id is not null then
1791 sqltxt :=sqltxt||' and BSB.assembly_item_id = '||l_item_id;
1792 end if;
1793
1794 sqltxt :=sqltxt||' and rownum < '||row_limit;
1795 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, BSB.alternate_bom_designator, BCB.operation_seq_num ,mif2.padded_item_number';
1796 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Bill Components ');
1797 If (num_rows = row_limit -1 ) Then
1798 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/>');
1799 End If;
1800 statusStr := 'SUCCESS';
1801 isFatal := 'FALSE';
1802
1803 /* SQL to Fetch Reference Designators */
1804 sqltxt := ' SELECT '||
1805 ' MIF1.PADDED_ITEM_NUMBER "Item Number (Assembly)" '||
1806 ' ,BSB.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID" '||
1807 ' ,MP1.ORGANIZATION_CODE "Org Code" '||
1808 ' ,BSB.ORGANIZATION_ID "ORGANIZATION ID" '||
1809 ' ,BSB.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR" '||
1810 ' ,MIF2.PADDED_ITEM_NUMBER "Item Number (Component)" '||
1811 ' ,BCB.COMPONENT_ITEM_ID "COMPONENT ITEM ID" '||
1812 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
1813 ' ,BRD.COMPONENT_REFERENCE_DESIGNATOR "COMPONENT REFERENCE DESIGNATOR" '||
1814 ' ,to_char(BRD.LAST_UPDATE_DATE ,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1815 ' ,BRD.LAST_UPDATED_BY "LAST UPDATED BY" '||
1816 ' ,to_char(BRD.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1817 ' ,BRD.CREATED_BY "CREATED BY" '||
1818 ' ,BRD.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1819 ' ,BRD.REF_DESIGNATOR_COMMENT "REF DESIGNATOR COMMENT" '||
1820 ' ,BRD.CHANGE_NOTICE "CHANGE NOTICE" '||
1824 ' ,BRD.REQUEST_ID "REQUEST ID" '||
1821 ' ,BRD.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
1822 ' ,DECODE(BRD.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
1823 ' ''OTHER('' || BRD.ACD_TYPE || '')'') "ACD TYPE" '||
1825 ' ,BRD.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1826 ' ,BRD.PROGRAM_ID "PROGRAM ID" '||
1827 ' ,to_char(BRD.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1828 ' ,BRD.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1829 ' ,BRD.ATTRIBUTE1 "ATTRIBUTE1" '||
1830 ' ,BRD.ATTRIBUTE2 "ATTRIBUTE2" '||
1831 ' ,BRD.ATTRIBUTE3 "ATTRIBUTE3" '||
1832 ' ,BRD.ATTRIBUTE4 "ATTRIBUTE4" '||
1833 ' ,BRD.ATTRIBUTE5 "ATTRIBUTE5" '||
1834 ' ,BRD.ATTRIBUTE6 "ATTRIBUTE6" '||
1835 ' ,BRD.ATTRIBUTE7 "ATTRIBUTE7" '||
1836 ' ,BRD.ATTRIBUTE8 "ATTRIBUTE8" '||
1837 ' ,BRD.ATTRIBUTE9 "ATTRIBUTE9" '||
1838 ' ,BRD.ATTRIBUTE10 "ATTRIBUTE10" '||
1839 ' ,BRD.ATTRIBUTE11 "ATTRIBUTE11" '||
1840 ' ,BRD.ATTRIBUTE12 "ATTRIBUTE12" '||
1841 ' ,BRD.ATTRIBUTE13 "ATTRIBUTE13" '||
1842 ' ,BRD.ATTRIBUTE14 "ATTRIBUTE14" '||
1843 ' ,BRD.ATTRIBUTE15 "ATTRIBUTE15" '||
1844 ' ,BRD.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1845 ' ,BRD.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
1846 ' FROM '||
1847 ' bom_components_b bcb '||
1848 ' ,bom_structures_b bsb '||
1849 ' ,bom_reference_designators brd '||
1850 ' ,MTL_PARAMETERS MP1 '||
1851 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1852 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
1853 ' where 1=1 '||
1854 ' AND bcb.bill_sequence_id = bsb.bill_sequence_id '||
1855 ' AND brd.component_sequence_id = bcb.component_sequence_id '||
1856 ' AND BSB.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
1857 ' AND BSB.ASSEMBLY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
1858 ' AND BSB.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1859 ' AND BCB.COMPONENT_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
1860 ' AND BSB.ORGANIZATION_ID = MIF2.ORGANIZATION_ID ';
1861
1862 if l_org_id is not null then
1863 sqltxt :=sqltxt||' and bsb.organization_id = '||l_org_id;
1864 end if;
1865
1866 if l_item_id is not null then
1867 sqltxt :=sqltxt||' and bsb.assembly_item_id = '||l_item_id;
1868 end if;
1869
1870 sqltxt :=sqltxt||' and rownum < '||row_limit;
1871 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, bsb.alternate_bom_designator, '||
1872 ' bcb.operation_seq_num, bcb.item_num, '||
1873 ' mif2.padded_item_number, brd.component_reference_designator';
1874
1875 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Bill Reference Designators ');
1876 If (num_rows = row_limit -1 ) Then
1877 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/>');
1878 End If;
1879 statusStr := 'SUCCESS';
1880 isFatal := 'FALSE';
1881
1882 /*End of Ref Desgs */
1883
1884 /* SQL to fetch Substitute Component Details */
1885 sqltxt := ' SELECT '||
1886 ' MIF1.PADDED_ITEM_NUMBER "Item Number (Assembly)" '||
1887 ' ,bsb.ASSEMBLY_ITEM_ID "Assy Item Id" '||
1888 ' ,MP1.ORGANIZATION_CODE "Org Code" '||
1889 ' ,bsb.ORGANIZATION_ID "ORGANIZATION ID" '||
1890 ' ,bsb.ALTERNATE_BOM_DESIGNATOR "ALTERNATE BOM DESIGNATOR" '||
1891 ' ,MIF2.PADDED_ITEM_NUMBER "Item Number (Component)" '||
1892 ' ,BCB.COMPONENT_ITEM_ID "COMPONENT ITEM ID" '||
1893 ' ,BCB.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
1894 ' ,MIF3.PADDED_ITEM_NUMBER "Item Number (Substitute)" '||
1895 ' ,BSCO.SUBSTITUTE_COMPONENT_ID "SUBSTITUTE COMPONENT ID" '||
1896 ' ,to_char(BSCO.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE" '||
1897 ' ,BSCO.LAST_UPDATED_BY "LAST UPDATED BY" '||
1898 ' ,to_char(BSCO.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE" '||
1899 ' ,BSCO.CREATED_BY "CREATED BY" '||
1900 ' ,BSCO.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN" '||
1901 ' ,NVL(BSCO.SUBSTITUTE_ITEM_QUANTITY,0) "SUBSTITUTE ITEM QUANTITY" '||
1902 ' ,BSCO.COMPONENT_SEQUENCE_ID "COMPONENT SEQUENCE ID" '||
1903 ' ,DECODE(MLU_ACD.MEANING,null,null, '||
1904 ' (MLU_ACD.MEANING || '' ('' || BSCO.ACD_TYPE || '')'')) "ACD TYPE" '||
1905 ' ,BSCO.CHANGE_NOTICE "CHANGE NOTICE" '||
1906 ' ,BSCO.REQUEST_ID "REQUEST ID" '||
1907 ' ,BSCO.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID" '||
1908 ' ,BSCO.PROGRAM_ID "PROGRAM ID" '||
1909 ' ,to_char(BSCO.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
1910 ' ,BSCO.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY" '||
1911 ' ,BSCO.ATTRIBUTE1 "ATTRIBUTE1" '||
1912 ' ,BSCO.ATTRIBUTE2 "ATTRIBUTE2" '||
1916 ' ,BSCO.ATTRIBUTE6 "ATTRIBUTE6" '||
1913 ' ,BSCO.ATTRIBUTE3 "ATTRIBUTE3" '||
1914 ' ,BSCO.ATTRIBUTE4 "ATTRIBUTE4" '||
1915 ' ,BSCO.ATTRIBUTE5 "ATTRIBUTE5" '||
1917 ' ,BSCO.ATTRIBUTE7 "ATTRIBUTE7" '||
1918 ' ,BSCO.ATTRIBUTE8 "ATTRIBUTE8" '||
1919 ' ,BSCO.ATTRIBUTE9 "ATTRIBUTE9" '||
1920 ' ,BSCO.ATTRIBUTE10 "ATTRIBUTE10" '||
1921 ' ,BSCO.ATTRIBUTE11 "ATTRIBUTE11" '||
1922 ' ,BSCO.ATTRIBUTE12 "ATTRIBUTE12" '||
1923 ' ,BSCO.ATTRIBUTE13 "ATTRIBUTE13" '||
1924 ' ,BSCO.ATTRIBUTE14 "ATTRIBUTE14" '||
1925 ' ,BSCO.ATTRIBUTE15 "ATTRIBUTE15" '||
1926 ' ,BSCO.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
1927 ' ,BSCO.ENFORCE_INT_REQUIREMENTS "ENFORCE INT REQUIREMENTS" '||
1928 ' ,BSCO.COMMON_COMPONENT_SEQUENCE_ID "COMMON COMPONENT SEQUENCE ID" '||
1929 ' FROM '||
1930 ' bom_components_b BCB '||
1931 ' ,bom_structures_b bsb '||
1932 ' ,bom_substitute_components BSCO '||
1933 ' ,MTL_PARAMETERS MP1 '||
1934 ' ,MTL_ITEM_FLEXFIELDS MIF1 '||
1935 ' ,MTL_ITEM_FLEXFIELDS MIF2 '||
1936 ' ,MTL_ITEM_FLEXFIELDS MIF3 '||
1937 ' ,MFG_LOOKUPS MLU_ACD '||
1938 ' where 1=1 '||
1939 ' AND bsb.ORGANIZATION_ID = MP1.ORGANIZATION_ID '||
1940 ' AND bsb.ASSEMBLY_ITEM_ID = MIF1.INVENTORY_ITEM_ID '||
1941 ' AND bsb.ORGANIZATION_ID = MIF1.ORGANIZATION_ID '||
1942 ' AND BCB.COMPONENT_ITEM_ID = MIF2.INVENTORY_ITEM_ID '||
1943 ' AND bsb.ORGANIZATION_ID = MIF2.ORGANIZATION_ID '||
1944 ' AND BCB.BILL_SEQUENCE_ID = bsb.BILL_SEQUENCE_ID '||
1945 ' AND BSCO.COMPONENT_SEQUENCE_ID = BCB.COMPONENT_SEQUENCE_ID '||
1946 ' AND BSCO.SUBSTITUTE_COMPONENT_ID = MIF3.INVENTORY_ITEM_ID '||
1947 ' AND bsb.ORGANIZATION_ID = MIF3.ORGANIZATION_ID '||
1948 ' AND BSCO.ACD_TYPE = MLU_ACD.LOOKUP_CODE(+) '||
1949 ' AND ''ECG_ACTION''=MLU_ACD.LOOKUP_TYPE(+) ';
1950
1951 if l_org_id is not null then
1952 sqltxt :=sqltxt||' and bsb.organization_id = '||l_org_id;
1953 end if;
1954
1955 if l_item_id is not null then
1956 sqltxt :=sqltxt||' and bsb.assembly_item_id = '||l_item_id;
1957 end if;
1958
1959 sqltxt :=sqltxt||' and rownum < '||row_limit;
1960 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, bsb.alternate_bom_designator, '||
1961 ' BCB.operation_seq_num ,mif2.padded_item_number, mif3.padded_item_number';
1962 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Bill Substitute Components ');
1963 If (num_rows = row_limit -1 ) Then
1964 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/>');
1965 End If;
1966 statusStr := 'SUCCESS';
1967 isFatal := 'FALSE';
1968
1969 /* End of Substitute Component Details */
1970
1971 /* SQL to fetch the Routing Header Details */
1972 sqltxt := ' SELECT '||
1973 ' MIF1.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
1974 ' BOR1.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
1975 ' MP1.ORGANIZATION_CODE "ORGANIZATION CODE", '||
1976 ' BOR1.ORGANIZATION_ID "ORGANIZATION ID", '||
1977 ' BOR1.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
1978 ' BOR1.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
1979 ' to_char(BOR1.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
1980 ' BOR1.LAST_UPDATED_BY "LAST UPDATED BY", '||
1981 ' to_char(BOR1.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
1982 ' BOR1.CREATED_BY "CREATED BY", '||
1983 ' BOR1.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
1984 ' (DECODE(BOR1.ROUTING_TYPE,NULL,NULL,1,''Manufacturing (1)'', 2, ''Engineering (2)'', '||
1985 ' ''Other('' ||BOR1.ROUTING_TYPE|| '')'')) "ROUTING TYPE", '||
1986 ' MIF2.PADDED_ITEM_NUMBER "ITEM NUMBER (COMMON)", '||
1987 ' NVL(BOR1.COMMON_ASSEMBLY_ITEM_ID,BOR1.ASSEMBLY_ITEM_ID ) "COMMON ASSEMBLY ITEM ID", '||
1988 ' MP2.ORGANIZATION_CODE "ORGANIZATION CODE (COMMON)", '||
1989 ' NVL(MP2.ORGANIZATION_ID,MP1.ORGANIZATION_ID) "ORGANIZATION ID (COMMON)", '||
1990 ' BOR1.COMMON_ROUTING_SEQUENCE_ID "COMMON ROUTING SEQUENCE ID", '||
1991 ' BOR1.ROUTING_COMMENT "ROUTING COMMENT", '||
1992 ' BOR1.COMPLETION_SUBINVENTORY "COMPLETION SUBINVENTORY", '||
1993 ' BOR1.COMPLETION_LOCATOR_ID "COMPLETION LOCATOR ID", '||
1994 ' BOR1.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
1995 ' BOR1.ATTRIBUTE1 "ATTRIBUTE1", '||
1996 ' BOR1.ATTRIBUTE2 "ATTRIBUTE2", '||
1997 ' BOR1.ATTRIBUTE3 "ATTRIBUTE3", '||
1998 ' BOR1.ATTRIBUTE4 "ATTRIBUTE4", '||
1999 ' BOR1.ATTRIBUTE5 "ATTRIBUTE5", '||
2000 ' BOR1.ATTRIBUTE6 "ATTRIBUTE6", '||
2001 ' BOR1.ATTRIBUTE7 "ATTRIBUTE7", '||
2002 ' BOR1.ATTRIBUTE8 "ATTRIBUTE8", '||
2003 ' BOR1.ATTRIBUTE9 "ATTRIBUTE9", '||
2004 ' BOR1.ATTRIBUTE10 "ATTRIBUTE10", '||
2005 ' BOR1.ATTRIBUTE11 "ATTRIBUTE11", '||
2006 ' BOR1.ATTRIBUTE12 "ATTRIBUTE12", '||
2007 ' BOR1.ATTRIBUTE13 "ATTRIBUTE13", '||
2008 ' BOR1.ATTRIBUTE14 "ATTRIBUTE14", '||
2009 ' BOR1.ATTRIBUTE15 "ATTRIBUTE15", '||
2010 ' BOR1.REQUEST_ID "REQUEST ID", '||
2011 ' BOR1.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
2012 ' BOR1.PROGRAM_ID "PROGRAM ID", '||
2016 ' ''OTHER ('' || BOR1.CFM_ROUTING_FLAG || '')'') "CFM ROUTING FLAG", '||
2013 ' to_char(BOR1.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
2014 ' BOR1.LINE_ID "LINE ID", '||
2015 ' DECODE(BOR1.CFM_ROUTING_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2017 ' DECODE(BOR1.MIXED_MODEL_MAP_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2018 ' ''OTHER ('' || BOR1.MIXED_MODEL_MAP_FLAG || '')'') "MIXED MODEL MAP FLAG", '||
2019 ' BOR1.PRIORITY "PRIORITY", '||
2020 ' BOR1.TOTAL_PRODUCT_CYCLE_TIME "TOTAL PRODUCT CYCLE TIME", '||
2021 ' DECODE(BOR1.CTP_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2022 ' ''OTHER ('' || BOR1.CTP_FLAG || '')'') "CTP FLAG", '||
2023 ' BOR1.PROJECT_ID "PROJECT ID", '||
2024 ' BOR1.TASK_ID "TASK ID", '||
2025 ' BOR1.PENDING_FROM_ECN "PENDING FROM ECN", '||
2026 ' BOR1.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
2027 ' BOR1.SERIALIZATION_START_OP "SERIALIZATION START OP" '||
2028 ' FROM bom_operational_routings bor1, '||
2029 ' mtl_parameters mp1, mtl_item_flexfields mif1, '||
2030 ' bom_operational_routings bor2, '||
2031 ' mtl_parameters mp2, mtl_item_flexfields mif2 '||
2032 ' WHERE 1=1 '||
2033 ' and bor1.assembly_item_id = mif1.inventory_item_id '||
2034 ' and bor1.organization_id = mif1.organization_id '||
2035 ' and mif1.organization_id = mp1.organization_id '||
2036 ' and nvl(bor1.common_routing_sequence_id,bor1.routing_sequence_id) = bor2.routing_sequence_id '||
2037 ' and bor2.assembly_item_id = mif2.inventory_item_id '||
2038 ' and bor2.organization_id = mif2.organization_id '||
2039 ' and mif2.organization_id = mp2.organization_id ';
2040
2041 if l_org_id is not null then
2042 sqltxt :=sqltxt||' and bor1.organization_id = '||l_org_id;
2043 end if;
2044
2045 if l_item_id is not null then
2046 sqltxt :=sqltxt||' and bor1.assembly_item_id = '||l_item_id;
2047 end if;
2048
2049 sqltxt :=sqltxt||' and rownum < '||row_limit;
2050 sqltxt :=sqltxt||' order by mp1.organization_code, mif1.padded_item_number, bor1.alternate_routing_designator';
2051
2052 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Headers ');
2053 If (num_rows = row_limit -1 ) Then
2054 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/>');
2055 End If;
2056 statusStr := 'SUCCESS';
2057 isFatal := 'FALSE';
2058
2059 /* End of Routing Header Details */
2060
2061 /* SQL to fetch the Routing Operation Details */
2062 sqltxt := ' SELECT '||
2063 ' MIF.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
2064 ' BOR.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
2065 ' MP.ORGANIZATION_CODE "ORGANIZATION CODE", '||
2066 ' BOR.ORGANIZATION_ID "ORGANIZATION ID", '||
2067 ' BOR.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
2068 ' BOS.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
2069 ' BOS.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
2070 ' BOS.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
2071 ' to_char(BOS.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE ", '||
2072 ' BOS.LAST_UPDATED_BY "LAST UPDATED BY", '||
2073 ' to_char(BOS.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
2074 ' BOS.CREATED_BY "CREATED BY", '||
2075 ' BOS.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
2076 ' BOS.STANDARD_OPERATION_ID "STANDARD OPERATION ID", '||
2077 ' BOS.DEPARTMENT_ID "DEPARTMENT ID", '||
2078 ' BOS.OPERATION_LEAD_TIME_PERCENT "OPERATION LEAD TIME PERCENT", '||
2079 ' BOS.MINIMUM_TRANSFER_QUANTITY "MINIMUM TRANSFER QUANTITY", '||
2080 ' DECODE(MLU_BCPT.MEANING,null,null, '||
2081 ' (MLU_BCPT.MEANING || '' ('' || BOS.COUNT_POINT_TYPE || '')'')) "Count Point Type", '||
2082 ' BOS.OPERATION_DESCRIPTION "OPERATION DESCRIPTION", '||
2083 ' to_char(BOS.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
2084 ' to_char(BOS.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE", '||
2085 ' DECODE(BOS.BACKFLUSH_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2086 ' ''OTHER ('' || BOS.BACKFLUSH_FLAG || '')'') "Backflush Flag", '||
2087 ' DECODE(BOS.OPTION_DEPENDENT_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2088 ' ''OTHER ('' || BOS.OPTION_DEPENDENT_FLAG || '')'') "Option Dependent Flag", '||
2089 ' BOS.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY ", '||
2090 ' BOS.ATTRIBUTE1 "ATTRIBUTE1", '||
2091 ' BOS.ATTRIBUTE2 "ATTRIBUTE2", '||
2092 ' BOS.ATTRIBUTE3 "ATTRIBUTE3", '||
2093 ' BOS.ATTRIBUTE4 "ATTRIBUTE4", '||
2094 ' BOS.ATTRIBUTE5 "ATTRIBUTE5", '||
2095 ' BOS.ATTRIBUTE6 "ATTRIBUTE6", '||
2096 ' BOS.ATTRIBUTE7 "ATTRIBUTE7", '||
2097 ' BOS.ATTRIBUTE8 "ATTRIBUTE8", '||
2098 ' BOS.ATTRIBUTE9 "ATTRIBUTE9", '||
2099 ' BOS.ATTRIBUTE10 "ATTRIBUTE10", '||
2100 ' BOS.ATTRIBUTE11 "ATTRIBUTE11", '||
2101 ' BOS.ATTRIBUTE12 "ATTRIBUTE12", '||
2102 ' BOS.ATTRIBUTE13 "ATTRIBUTE13", '||
2103 ' BOS.ATTRIBUTE14 "ATTRIBUTE14", '||
2104 ' BOS.ATTRIBUTE15 "ATTRIBUTE15", '||
2105 ' BOS.REQUEST_ID "REQUEST ID", '||
2106 ' BOS.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
2107 ' BOS.PROGRAM_ID "PROGRAM ID", '||
2111 ' DECODE(BOS.REFERENCE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2108 ' to_char(BOS.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
2109 ' DECODE(MLU_OPT.MEANING,null,null, '||
2110 ' (MLU_OPT.MEANING || '' ('' || BOS.OPERATION_TYPE || '')'')) "Operation Type", '||
2112 ' ''OTHER ('' || BOS.REFERENCE_FLAG || '')'') "Reference Flag", '||
2113 ' BOS.PROCESS_OP_SEQ_ID "PROCESS OP SEQ ID", '||
2114 ' BOS.LINE_OP_SEQ_ID "LINE OP SEQ ID", '||
2115 ' BOS.YIELD "YIELD", '||
2116 ' BOS.CUMULATIVE_YIELD "CUMULATIVE YIELD", '||
2117 ' BOS.REVERSE_CUMULATIVE_YIELD "REVERSE CUMULATIVE YIELD", '||
2118 ' BOS.LABOR_TIME_CALC "LABOR TIME CALC", '||
2119 ' BOS.MACHINE_TIME_CALC "MACHINE TIME CALC", '||
2120 ' BOS.TOTAL_TIME_CALC "TOTAL TIME CALC", '||
2121 ' BOS.LABOR_TIME_USER "LABOR TIME USER", '||
2122 ' BOS.MACHINE_TIME_USER "MACHINE TIME USER", '||
2123 ' BOS.TOTAL_TIME_USER "TOTAL TIME USER", '||
2124 ' BOS.NET_PLANNING_PERCENT "NET PLANNING PERCENT ", '||
2125 ' BOS.X_COORDINATE "X COORDINATE", '||
2126 ' BOS.Y_COORDINATE "Y COORDINATE", '||
2127 ' DECODE(BOS.INCLUDE_IN_ROLLUP,null,null,1,''Yes (1)'',2,''No (2)'', '||
2128 ' ''OTHER ('' || BOS.INCLUDE_IN_ROLLUP || '')'') "INCLUDE IN ROLLUP", '||
2129 ' DECODE(BOS.OPERATION_YIELD_ENABLED,null,null,1,''Yes (1)'',2,''No (2)'', '||
2130 ' ''OTHER ('' || BOS.OPERATION_YIELD_ENABLED || '')'') "OPERATION YIELD ENABLED", '||
2131 ' BOS.OLD_OPERATION_SEQUENCE_ID "OLD OPERATION SEQUENCE ID", '||
2132 ' DECODE(BOS.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
2133 ' ''OTHER ('' || BOS.ACD_TYPE || '')'') "ACD TYPE", '||
2134 ' BOS.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID", '||
2135 ' BOS.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
2136 ' BOS.CHANGE_NOTICE "CHANGE NOTICE", '||
2137 ' to_char(BOS.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE", '||
2138 ' DECODE(BOS.ECO_FOR_PRODUCTION,null,null,1,''Yes (1)'',2,''No (2)'', '||
2139 ' ''OTHER ('' || BOS.ECO_FOR_PRODUCTION || '')'') "ECO FOR PRODUCTION ", '||
2140 ' DECODE(MLU_SHT.MEANING,null,null, '||
2141 ' (MLU_SHT.MEANING || '' ('' || BOS.SHUTDOWN_TYPE || '')'')) "SHUTDOWN TYPE", '||
2142 ' BOS.ACTUAL_IPK "ACTUAL IPK", '||
2143 ' BOS.CRITICAL_TO_QUALITY "CRITICAL TO QUALITY", '||
2144 ' BOS.VALUE_ADDED "VALUE ADDED", '||
2145 ' BOS.MACHINE_PROCESS_EFFICIENCY "MACHINE PROCESS EFFICIENCY", '||
2146 ' BOS.LABOR_PROCESS_EFFICIENCY "LABOR PROCESS EFFICIENCY", '||
2147 ' BOS.TOTAL_PROCESS_EFFICIENCY "TOTAL PROCESS EFFICIENCY", '||
2148 ' BOS.LONG_DESCRIPTION "LONG DESCRIPTION" '||
2149 ' ,BOS.CONFIG_ROUTING_ID "CONFIG ROUTING ID" '||
2150 ' ,BOS.MODEL_OP_SEQ_ID "MODEL OP SEQ ID" '||
2151 ' ,BOS.LOWEST_ACCEPTABLE_YIELD "LOWEST ACCEPTABLE YIELD" '||
2152 ' ,BOS.USE_ORG_SETTINGS "USE ORG SETTINGS" '||
2153 ' ,BOS.QUEUE_MANDATORY_FLAG "QUEUE MANDATORY FLAG" '||
2154 ' ,BOS.RUN_MANDATORY_FLAG "RUN MANDATORY FLAG" '||
2155 ' ,BOS.TO_MOVE_MANDATORY_FLAG "TO MOVE MANDATORY FLAG" '||
2156 ' ,BOS.SHOW_NEXT_OP_BY_DEFAULT "SHOW NEXT OP BY DEFAULT" '||
2157 ' ,BOS.SHOW_SCRAP_CODE "SHOW SCRAP CODE" '||
2158 ' ,BOS.SHOW_LOT_ATTRIB "SHOW LOT ATTRIB" '||
2159 ' ,BOS.TRACK_MULTIPLE_RES_USAGE_DATES "TRACK MULTIPLE RES USAGE DATES" '||
2160 ' FROM bom_operational_routings bor, bom_operation_sequences bos, '||
2161 ' MTL_PARAMETERS MP, MTL_ITEM_FLEXFIELDS MIF '||
2162 ' ,MFG_LOOKUPS MLU_BCPT '||
2163 ' ,MFG_LOOKUPS MLU_OPT '||
2164 ' ,MFG_LOOKUPS MLU_SHT '||
2165 ' WHERE 1=1 '||
2166 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
2167 ' and bor.assembly_item_id = mif.inventory_item_id '||
2168 ' and bor.organization_id = mif.organization_id '||
2169 ' and mif.organization_id = mp.organization_id '||
2170 ' and bos.count_point_type=mlu_bcpt.lookup_code(+) and ''BOM_COUNT_POINT_TYPE''=mlu_bcpt.lookup_type(+) '||
2171 ' and bos.operation_type=mlu_opt.lookup_code(+) and ''BOM_OPERATION_TYPE''=mlu_opt.lookup_type(+) '||
2172 ' and bos.shutdown_type=mlu_sht.lookup_code(+) and ''BOM_EAM_SHUTDOWN_TYPE''=mlu_sht.lookup_type(+) ';
2173
2174 if l_org_id is not null then
2175 sqltxt :=sqltxt||' and bor.organization_id = '||l_org_id;
2176 end if;
2177
2178 if l_item_id is not null then
2179 sqltxt :=sqltxt||' and bor.assembly_item_id = '||l_item_id;
2180 end if;
2181
2182 sqltxt :=sqltxt||' and rownum < '||row_limit;
2183 sqltxt :=sqltxt||' order by mp.organization_code, mif.padded_item_number, bor.alternate_routing_designator,'||
2184 ' bos.operation_seq_num ';
2185
2186 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Operations ');
2187 If (num_rows = row_limit -1 ) Then
2188 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/>');
2189 End If;
2190 statusStr := 'SUCCESS';
2191 isFatal := 'FALSE';
2192 /* End of Routing Operation Details */
2193
2194 /* SQL to fetch the Operation Resource Details */
2195 sqltxt := ' SELECT '||
2196 ' MIF.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
2197 ' BOR.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
2198 ' MP.ORGANIZATION_CODE "ORGANIZATION CODE", '||
2199 ' BOR.ORGANIZATION_ID "ORGANIZATION ID", '||
2200 ' BOR.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
2201 ' BOR.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
2205 ' BR.RESOURCE_CODE "RESOURCE CODE", '||
2202 ' BOS.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
2203 ' BORE.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
2204 ' BORE.RESOURCE_SEQ_NUM "RESOURCE SEQ NUM", '||
2206 ' BORE.RESOURCE_ID "RESOURCE ID", '||
2207 ' BR.DESCRIPTION "RESOURCE DESCRIPTION", '||
2208 ' BORE.ACTIVITY_ID "ACTIVITY ID", '||
2209 ' DECODE(BORE.STANDARD_RATE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2210 ' ''OTHER ('' || BORE.STANDARD_RATE_FLAG || '')'') "STANDARD RATE FLAG", '||
2211 ' BORE.ASSIGNED_UNITS "ASSIGNED UNITS", '||
2212 ' BORE.USAGE_RATE_OR_AMOUNT "USAGE RATE OR AMOUNT", '||
2213 ' BORE.USAGE_RATE_OR_AMOUNT_INVERSE "USAGE RATE OR AMOUNT INVERSE", '||
2214 ' DECODE(MLU_BT.MEANING,null,null, '||
2215 ' (MLU_BT.MEANING || '' ('' || BORE.BASIS_TYPE || '')'')) "BASIS TYPE", '||
2216 ' DECODE(MLU_SF.MEANING,null,null, '||
2217 ' (MLU_SF.MEANING || '' ('' || BORE.SCHEDULE_FLAG || '')'')) "SCHEDULE FLAG", '||
2218 ' to_char(BORE.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
2219 ' BORE.LAST_UPDATED_BY "LAST UPDATED BY", '||
2220 ' to_char(BORE.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
2221 ' BORE.CREATED_BY "CREATED BY", '||
2222 ' BORE.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
2223 ' BORE.RESOURCE_OFFSET_PERCENT "RESOURCE OFFSET PERCENT", '||
2224 ' DECODE(MLU_ACT.MEANING,null,null, '||
2225 ' (MLU_ACT.MEANING || '' ('' || BORE.AUTOCHARGE_TYPE || '')'')) "AUTOCHARGE TYPE", '||
2226 ' BORE.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
2227 ' BORE.ATTRIBUTE1 "ATTRIBUTE1", '||
2228 ' BORE.ATTRIBUTE2 "ATTRIBUTE2", '||
2229 ' BORE.ATTRIBUTE3 "ATTRIBUTE3", '||
2230 ' BORE.ATTRIBUTE4 "ATTRIBUTE4", '||
2231 ' BORE.ATTRIBUTE5 "ATTRIBUTE5", '||
2232 ' BORE.ATTRIBUTE6 "ATTRIBUTE6", '||
2233 ' BORE.ATTRIBUTE7 "ATTRIBUTE7", '||
2234 ' BORE.ATTRIBUTE8 "ATTRIBUTE8", '||
2235 ' BORE.ATTRIBUTE9 "ATTRIBUTE9", '||
2236 ' BORE.ATTRIBUTE10 "ATTRIBUTE10", '||
2237 ' BORE.ATTRIBUTE11 "ATTRIBUTE11", '||
2238 ' BORE.ATTRIBUTE12 "ATTRIBUTE12", '||
2239 ' BORE.ATTRIBUTE13 "ATTRIBUTE13", '||
2240 ' BORE.ATTRIBUTE14 "ATTRIBUTE14", '||
2241 ' BORE.ATTRIBUTE15 "ATTRIBUTE15", '||
2242 ' BORE.REQUEST_ID "REQUEST ID", '||
2243 ' BORE.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
2244 ' BORE.PROGRAM_ID "PROGRAM ID", '||
2245 ' to_char(BORE.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
2246 ' BORE.SCHEDULE_SEQ_NUM "SCHEDULE SEQ NUM", '||
2247 ' BORE.SUBSTITUTE_GROUP_NUM "SUBSTITUTE GROUP NUM", '||
2248 ' DECODE(BORE.PRINCIPLE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2249 ' ''OTHER ('' || BORE.PRINCIPLE_FLAG || '')'') "PRINCIPLE FLAG", '||
2250 ' BORE.SETUP_ID "SETUP ID", '||
2251 ' BORE.CHANGE_NOTICE "CHANGE NOTICE", '||
2252 ' DECODE(BORE.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
2253 ' ''OTHER ('' || BORE.ACD_TYPE || '')'') "ACD TYPE", '||
2254 ' BORE.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
2255 ' FROM BOM_OPERATIONAL_ROUTINGS BOR, BOM_OPERATION_SEQUENCES BOS, '||
2256 ' BOM_OPERATION_RESOURCES BORE, bom_resources br, '||
2257 ' MTL_PARAMETERS MP, MTL_ITEM_FLEXFIELDS MIF, '||
2258 ' MFG_LOOKUPS MLU_BT, MFG_LOOKUPS MLU_SF, '||
2259 ' MFG_LOOKUPS MLU_ACT '||
2260 ' WHERE 1=1 '||
2261 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
2262 ' AND bore.operation_sequence_id=bos.operation_sequence_id '||
2263 ' and bor.assembly_item_id = mif.inventory_item_id '||
2264 ' and bor.organization_id = mif.organization_id '||
2265 ' and mif.organization_id = mp.organization_id '||
2266 ' AND bore.resource_id = br.resource_id '||
2267 ' AND BORE.BASIS_TYPE=MLU_BT.LOOKUP_CODE(+) AND ''CST_BASIS''=MLU_BT.LOOKUP_TYPE(+) '||
2268 ' AND BORE.SCHEDULE_FLAG=MLU_SF.LOOKUP_CODE(+) AND ''BOM_RESOURCE_SCHEDULE_TYPE''=MLU_SF.LOOKUP_TYPE(+) '||
2269 ' AND BORE.AUTOCHARGE_TYPE=MLU_ACT.LOOKUP_CODE(+) AND ''BOM_AUTOCHARGE_TYPE''=MLU_ACT.LOOKUP_TYPE(+) ';
2270
2271 if l_org_id is not null then
2272 sqltxt :=sqltxt||' and bor.organization_id = '||l_org_id;
2273 end if;
2274
2275 if l_item_id is not null then
2276 sqltxt :=sqltxt||' and bor.assembly_item_id = '||l_item_id;
2277 end if;
2278
2279 sqltxt :=sqltxt||' and rownum < '||row_limit;
2280 sqltxt :=sqltxt||' order by mp.organization_code, mif.padded_item_number, bor.alternate_routing_designator,'||
2281 ' bos.operation_seq_num, bore.resource_seq_num, br.resource_code ';
2282
2283 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Operation Resources ');
2284 If (num_rows = row_limit -1 ) Then
2285 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/>');
2286 End If;
2287 statusStr := 'SUCCESS';
2288 isFatal := 'FALSE';
2289
2290 /* End of Operation Resource Details */
2291
2292 /* SQL to fetch the Operation Sub Resource Details */
2293 sqltxt := ' SELECT '||
2294 ' MIF.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
2295 ' BOR.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
2296 ' MP.ORGANIZATION_CODE "ORGANIZATION CODE", '||
2300 ' BOS.OPERATION_SEQ_NUM "OPERATION SEQ NUM", '||
2297 ' BOR.ORGANIZATION_ID "ORGANIZATION ID", '||
2298 ' BOR.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
2299 ' BOR.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
2301 ' BSOR.OPERATION_SEQUENCE_ID "OPERATION SEQUENCE ID", '||
2302 ' BSOR.SUBSTITUTE_GROUP_NUM "SUBSTITUTE GROUP NUM", '||
2303 ' BR.RESOURCE_CODE "RESOURCE CODE", '||
2304 ' BSOR.RESOURCE_ID "RESOURCE ID", '||
2305 ' BR.DESCRIPTION "RESOURCE DESCRIPTION", '||
2306 ' BSOR.SCHEDULE_SEQ_NUM "SCHEDULE SEQ NUM", '||
2307 ' BSOR.REPLACEMENT_GROUP_NUM "REPLACEMENT GROUP NUM", '||
2308 ' BSOR.ACTIVITY_ID "ACTIVITY ID", '||
2309 ' DECODE(BSOR.STANDARD_RATE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2310 ' ''OTHER ('' || BSOR.STANDARD_RATE_FLAG || '')'') "STANDARD RATE FLAG", '||
2311 ' BSOR.ASSIGNED_UNITS "ASSIGNED UNITS", '||
2312 ' BSOR.USAGE_RATE_OR_AMOUNT "USAGE RATE OR AMOUNT", '||
2313 ' BSOR.USAGE_RATE_OR_AMOUNT_INVERSE "USAGE RATE OR AMOUNT INVERSE", '||
2314 ' DECODE(MLU_BT.MEANING,null,null, '||
2315 ' (MLU_BT.MEANING || '' ('' || BSOR.BASIS_TYPE || '')'')) "BASIS TYPE", '||
2316 ' DECODE(MLU_SF.MEANING,null,null, '||
2317 ' (MLU_SF.MEANING || '' ('' || BSOR.SCHEDULE_FLAG || '')'')) "SCHEDULE FLAG", '||
2318 ' to_char(BSOR.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
2319 ' BSOR.LAST_UPDATED_BY "LAST UPDATED BY", '||
2320 ' to_char(BSOR.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
2321 ' BSOR.CREATED_BY "CREATED BY", '||
2322 ' BSOR.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
2323 ' BSOR.RESOURCE_OFFSET_PERCENT "RESOURCE OFFSET PERCENT", '||
2324 ' DECODE(MLU_ACT.MEANING,null,null, '||
2325 ' (MLU_ACT.MEANING || '' ('' || BSOR.AUTOCHARGE_TYPE || '')'')) "AUTOCHARGE TYPE", '||
2326 ' BSOR.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
2327 ' BSOR.REQUEST_ID "REQUEST ID", '||
2328 ' BSOR.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
2329 ' BSOR.PROGRAM_ID "PROGRAM ID", '||
2330 ' to_char(BSOR.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
2331 ' BSOR.ATTRIBUTE1 "ATTRIBUTE1", '||
2332 ' BSOR.ATTRIBUTE2 "ATTRIBUTE2", '||
2333 ' BSOR.ATTRIBUTE3 "ATTRIBUTE3", '||
2334 ' BSOR.ATTRIBUTE4 "ATTRIBUTE4", '||
2335 ' BSOR.ATTRIBUTE5 "ATTRIBUTE5", '||
2336 ' BSOR.ATTRIBUTE6 "ATTRIBUTE6", '||
2337 ' BSOR.ATTRIBUTE7 "ATTRIBUTE7", '||
2338 ' BSOR.ATTRIBUTE8 "ATTRIBUTE8", '||
2339 ' BSOR.ATTRIBUTE9 "ATTRIBUTE9", '||
2340 ' BSOR.ATTRIBUTE10 "ATTRIBUTE10", '||
2341 ' BSOR.ATTRIBUTE11 "ATTRIBUTE11", '||
2342 ' BSOR.ATTRIBUTE12 "ATTRIBUTE12", '||
2343 ' BSOR.ATTRIBUTE13 "ATTRIBUTE13", '||
2344 ' BSOR.ATTRIBUTE14 "ATTRIBUTE14", '||
2345 ' BSOR.ATTRIBUTE15 "ATTRIBUTE15", '||
2346 ' DECODE(BSOR.PRINCIPLE_FLAG,null,null,1,''Yes (1)'',2,''No (2)'', '||
2347 ' ''OTHER ('' || BSOR.PRINCIPLE_FLAG || '')'') "PRINCIPLE FLAG", '||
2348 ' BSOR.SETUP_ID "SETUP ID", '||
2349 ' BSOR.CHANGE_NOTICE "CHANGE NOTICE", '||
2350 ' DECODE(BSOR.ACD_TYPE,null,null,1,''Add (1)'',2,''Change (2)'',3,''Delete (3)'', '||
2351 ' ''OTHER ('' || BSOR.ACD_TYPE || '')'') "ACD TYPE", '||
2352 ' BSOR.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE" '||
2353 ' FROM BOM_OPERATIONAL_ROUTINGS BOR, BOM_OPERATION_SEQUENCES BOS, '||
2354 ' BOM_SUB_OPERATION_RESOURCES BSOR, bom_resources br, '||
2355 ' MTL_PARAMETERS MP, MTL_ITEM_FLEXFIELDS MIF, '||
2356 ' MFG_LOOKUPS MLU_BT, MFG_LOOKUPS MLU_SF, '||
2357 ' MFG_LOOKUPS MLU_ACT '||
2358 ' WHERE 1=1 '||
2359 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
2360 ' AND bsor.operation_sequence_id=bos.operation_sequence_id '||
2361 ' and bor.assembly_item_id = mif.inventory_item_id '||
2362 ' and bor.organization_id = mif.organization_id '||
2363 ' and mif.organization_id = mp.organization_id '||
2364 ' and bsor.resource_id = br.resource_id '||
2365 ' AND BSOR.BASIS_TYPE=MLU_BT.LOOKUP_CODE(+) '||
2366 ' AND ''CST_BASIS''=MLU_BT.LOOKUP_TYPE(+) '||
2367 ' AND BSOR.SCHEDULE_FLAG=MLU_SF.LOOKUP_CODE(+) '||
2368 ' AND ''BOM_RESOURCE_SCHEDULE_TYPE''=MLU_SF.LOOKUP_TYPE(+) '||
2369 ' AND BSOR.AUTOCHARGE_TYPE=MLU_ACT.LOOKUP_CODE(+) '||
2370 ' AND ''BOM_AUTOCHARGE_TYPE''=MLU_ACT.LOOKUP_TYPE(+) ';
2371
2372 if l_org_id is not null then
2373 sqltxt :=sqltxt||' and bor.organization_id = '||l_org_id;
2374 end if;
2375
2376 if l_item_id is not null then
2377 sqltxt :=sqltxt||' and bor.assembly_item_id = '||l_item_id;
2378 end if;
2379
2380 sqltxt :=sqltxt||' and rownum < '||row_limit;
2381 sqltxt :=sqltxt||' order by mp.organization_code, mif.padded_item_number, bor.alternate_routing_designator,'||
2382 ' bos.operation_seq_num,bsor.substitute_group_num,bsor.replacement_group_num,br.resource_code';
2383
2384 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Operation Sub Resources ');
2385 If (num_rows = row_limit -1 ) Then
2386 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/>');
2387 End If;
2388 statusStr := 'SUCCESS';
2389 isFatal := 'FALSE';
2390 /* End of Operation Resource Details */
2391
2392 /* SQL to fetch Routing Revision Details */
2393 sqltxt := ' SELECT '||
2394 ' MIF.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
2395 ' MRIR.INVENTORY_ITEM_ID "INVENTORY ITEM ID", '||
2399 ' to_char(MRIR.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
2396 ' MP.ORGANIZATION_CODE "ORGANIZATION CODE", '||
2397 ' MRIR.ORGANIZATION_ID "ORGANIZATION ID", '||
2398 ' MRIR.PROCESS_REVISION "PROCESS REVISION", '||
2400 ' MRIR.LAST_UPDATED_BY "LAST UPDATED BY", '||
2401 ' to_char(MRIR.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
2402 ' MRIR.CREATED_BY "CREATED BY", '||
2403 ' MRIR.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
2404 ' MRIR.CHANGE_NOTICE "CHANGE NOTICE", '||
2405 ' to_char(MRIR.ECN_INITIATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "ECN INITIATION DATE", '||
2406 ' to_char(MRIR.IMPLEMENTATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "IMPLEMENTATION DATE", '||
2407 ' MRIR.IMPLEMENTED_SERIAL_NUMBER "IMPLEMENTED SERIAL NUMBER", '||
2408 ' to_char(MRIR.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
2409 ' MRIR.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
2410 ' MRIR.ATTRIBUTE1 "ATTRIBUTE1", '||
2411 ' MRIR.ATTRIBUTE2 "ATTRIBUTE2", '||
2412 ' MRIR.ATTRIBUTE3 "ATTRIBUTE3", '||
2413 ' MRIR.ATTRIBUTE4 "ATTRIBUTE4", '||
2414 ' MRIR.ATTRIBUTE5 "ATTRIBUTE5", '||
2415 ' MRIR.ATTRIBUTE6 "ATTRIBUTE6", '||
2416 ' MRIR.ATTRIBUTE7 "ATTRIBUTE7", '||
2417 ' MRIR.ATTRIBUTE8 "ATTRIBUTE8", '||
2418 ' MRIR.ATTRIBUTE9 "ATTRIBUTE9", '||
2419 ' MRIR.ATTRIBUTE10 "ATTRIBUTE10", '||
2420 ' MRIR.ATTRIBUTE11 "ATTRIBUTE11", '||
2421 ' MRIR.ATTRIBUTE12 "ATTRIBUTE12", '||
2422 ' MRIR.ATTRIBUTE13 "ATTRIBUTE13", '||
2423 ' MRIR.ATTRIBUTE14 "ATTRIBUTE14", '||
2424 ' MRIR.ATTRIBUTE15 "ATTRIBUTE15", '||
2425 ' MRIR.REQUEST_ID "REQUEST ID", '||
2426 ' MRIR.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
2427 ' MRIR.PROGRAM_ID "PROGRAM ID", '||
2428 ' to_char(MRIR.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE", '||
2429 ' MRIR.REVISED_ITEM_SEQUENCE_ID "REVISED ITEM SEQUENCE ID" '||
2430 ' from mtl_rtg_item_revisions mrir, '||
2431 ' MTL_PARAMETERS MP, MTL_ITEM_FLEXFIELDS MIF '||
2432 ' where 1=1 '||
2433 ' and mrir.inventory_item_id= mif.inventory_item_id '||
2434 ' and mrir.organization_id = mif.organization_id '||
2435 ' and mif.organization_id = mp.organization_id ';
2436
2437 if l_org_id is not null then
2438 sqltxt :=sqltxt||' and mrir.organization_id = '||l_org_id;
2439 end if;
2440
2441 if l_item_id is not null then
2442 sqltxt :=sqltxt||' and mrir.inventory_item_id = '||l_item_id;
2443 end if;
2444
2445 sqltxt :=sqltxt||' and rownum < '||row_limit;
2446 sqltxt :=sqltxt||' order by mp.organization_code, mif.padded_item_number, mrir.process_revision';
2447
2448 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Revisions ');
2449 If (num_rows = row_limit -1 ) Then
2450 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/>');
2451 End If;
2452 statusStr := 'SUCCESS';
2453 isFatal := 'FALSE';
2454
2455 /* End of Routing Revision Details */
2456
2457 /* SQL to fetch the Operation Network Details */
2458 sqltxt := ' SELECT '||
2459 ' MIF.PADDED_ITEM_NUMBER "ASSEMBLY ITEM NUMBER", '||
2460 ' BOR.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID", '||
2461 ' MP.ORGANIZATION_CODE "ORGANIZATION CODE", '||
2462 ' BOR.ORGANIZATION_ID "ORGANIZATION ID", '||
2463 ' BOR.ALTERNATE_ROUTING_DESIGNATOR "ALTERNATE ROUTING DESIGNATOR", '||
2464 ' BOR.ROUTING_SEQUENCE_ID "ROUTING SEQUENCE ID", '||
2465 ' BON.FROM_OP_SEQ_ID "FROM OP SEQ ID", '||
2466 ' BON.TO_OP_SEQ_ID "TO OP SEQ ID", '||
2467 ' BON.TRANSITION_TYPE "TRANSITION TYPE", '||
2468 ' BON.PLANNING_PCT "PLANNING PCT", '||
2469 ' to_char(BON.EFFECTIVITY_DATE,''DD-MON-YYYY HH24:MI:SS'') "EFFECTIVITY DATE", '||
2470 ' to_char(BON.DISABLE_DATE,''DD-MON-YYYY HH24:MI:SS'') "DISABLE DATE", '||
2471 ' BON.CREATED_BY "CREATED BY", '||
2472 ' to_char(BON.CREATION_DATE,''DD-MON-YYYY HH24:MI:SS'') "CREATION DATE", '||
2473 ' BON.LAST_UPDATED_BY "LAST UPDATED BY", '||
2474 ' to_char(BON.LAST_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "LAST UPDATE DATE", '||
2475 ' BON.LAST_UPDATE_LOGIN "LAST UPDATE LOGIN", '||
2476 ' BON.ATTRIBUTE_CATEGORY "ATTRIBUTE CATEGORY", '||
2477 ' BON.ATTRIBUTE1 "ATTRIBUTE1", '||
2478 ' BON.ATTRIBUTE2 "ATTRIBUTE2", '||
2479 ' BON.ATTRIBUTE3 "ATTRIBUTE3", '||
2480 ' BON.ATTRIBUTE4 "ATTRIBUTE4", '||
2481 ' BON.ATTRIBUTE5 "ATTRIBUTE5", '||
2482 ' BON.ATTRIBUTE6 "ATTRIBUTE6", '||
2483 ' BON.ATTRIBUTE7 "ATTRIBUTE7", '||
2484 ' BON.ATTRIBUTE8 "ATTRIBUTE8", '||
2485 ' BON.ATTRIBUTE9 "ATTRIBUTE9", '||
2486 ' BON.ATTRIBUTE10 "ATTRIBUTE10", '||
2487 ' BON.ATTRIBUTE11 "ATTRIBUTE11", '||
2488 ' BON.ATTRIBUTE12 "ATTRIBUTE12", '||
2489 ' BON.ATTRIBUTE13 "ATTRIBUTE13", '||
2490 ' BON.ATTRIBUTE14 "ATTRIBUTE14", '||
2491 ' BON.ATTRIBUTE15 "ATTRIBUTE15", '||
2492 ' BON.ORIGINAL_SYSTEM_REFERENCE "ORIGINAL SYSTEM REFERENCE", '||
2493 ' BON.REQUEST_ID "REQUEST ID", '||
2494 ' BON.PROGRAM_APPLICATION_ID "PROGRAM APPLICATION ID", '||
2495 ' BON.PROGRAM_ID "PROGRAM ID", '||
2496 ' to_char(BON.PROGRAM_UPDATE_DATE,''DD-MON-YYYY HH24:MI:SS'') "PROGRAM UPDATE DATE" '||
2497 ' FROM bom_operational_routings bor, bom_operation_sequences bos, '||
2501 ' AND bos.routing_sequence_id=bor.routing_sequence_id '||
2498 ' bom_operation_networks bon, '||
2499 ' MTL_PARAMETERS MP, MTL_ITEM_FLEXFIELDS MIF '||
2500 ' WHERE 1=1 '||
2502 ' AND bon.to_op_seq_id=bos.operation_sequence_id '||
2503 ' and bor.assembly_item_id = mif.inventory_item_id '||
2504 ' and bor.organization_id = mif.organization_id '||
2505 ' and mif.organization_id = mp.organization_id ';
2506
2507
2508 if l_org_id is not null then
2509 sqltxt :=sqltxt||' and bor.organization_id = '||l_org_id;
2510 end if;
2511
2512 if l_item_id is not null then
2513 sqltxt :=sqltxt||' and bor.assembly_item_id = '||l_item_id;
2514 end if;
2515
2516 sqltxt :=sqltxt||' and rownum < '||row_limit;
2517 sqltxt :=sqltxt||' order by mp.organization_code, mif.padded_item_number, bor.alternate_routing_designator,'||
2518 ' bos.operation_seq_num, bon.from_op_seq_id, bon.to_op_seq_id ';
2519
2520 num_rows:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Routing Operation Networks ');
2521 If (num_rows = row_limit -1 ) Then
2522 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/>');
2523 End If;
2524
2525 statusStr := 'SUCCESS';
2526 isFatal := 'FALSE';
2527 /* End of Operation Network Details */
2528
2529
2530 <<l_test_end>>
2531 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR/><BR/>This data collection script completed as expected <BR/>');
2532 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
2533 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
2534
2535 EXCEPTION
2536 when others then
2537 JTF_DIAGNOSTIC_COREAPI.errorprint('Error: '||sqlerrm);
2538 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('If this error repeats, please contact Oracle Support Services');
2539 statusStr := 'FAILURE';
2540 errStr := sqlerrm ||' occurred in script. ';
2541 fixInfo := 'Unexpected Exception in BOMDGIBB.pls';
2542 isFatal := 'FALSE';
2543 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
2544 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
2545
2546 END runTest;
2547
2548 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
2549 BEGIN
2550 name := 'Items/Bills/Routings Data Collection';
2551 END getComponentName;
2552
2553 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
2554 BEGIN
2555 descStr := ' This data collection script collects data about Items/Bills/Routings details. <BR/>
2556 Input for ItemId field is mandatory. ';
2557 END getTestDesc;
2558
2559 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
2560 BEGIN
2561 name := 'Items/Bills/Routings Data Collection ';
2562 END getTestName;
2563
2564 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
2565 tempDependencies JTF_DIAG_DEPENDTBL;
2566
2567 BEGIN
2568 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
2569 END getDependencies;
2570
2571 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
2572 BEGIN
2573 str := 'FALSE';
2574 END isDependencyPipelined;
2575
2576
2577 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
2578 tempOutput JTF_DIAG_OUTPUTTBL;
2579 BEGIN
2580 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
2581 outputValues := tempOutput;
2582 EXCEPTION
2583 when others then
2584 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
2585 END getOutputValues;
2586
2587
2588 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
2589 tempInput JTF_DIAG_INPUTTBL;
2590 BEGIN
2591 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
2592 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.bom.diag.lov.OrganizationLov');-- Lov name modified to OrgId for bug 6412260
2593 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'ItemId','LOV-oracle.apps.bom.diag.lov.ItemLov');-- Lov name modified to ItemId for bug 6412260
2594 defaultInputValues := tempInput;
2595 EXCEPTION
2596 when others then
2597 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
2598 END getDefaultTestParams;
2599
2600 Function getTestMode return INTEGER IS
2601 BEGIN
2602 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
2603
2604 END getTestMode;
2605
2606 END BOM_DIAGUNITTEST_IBRDATA;