[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_UNCST
Source
1 package body INV_DIAG_UNCST as
2 /* $Header: INVDTA1B.pls 120.0.12000000.1 2007/06/22 01:30:37 musinha 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 dummy_num NUMBER;
25 row_limit NUMBER;
26 l_txn_id NUMBER;
27 l_org_id NUMBER;
28 l_acct_period_id NUMBER :=null;
29 l_acct_period varchar2(15);
30 l_proc_flag varchar2(1);
31 BEGIN
32 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
33 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
34 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
35 -- accept input
36 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('OrgId',inputs);
37 l_acct_period :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('AcctPeriod',inputs);
38 l_proc_flag :=JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('CostFlag',inputs);
39 row_limit :=INV_DIAG_GRP.g_max_row;
40
41 if l_org_id is not null and l_acct_period is not null then
42 begin
43 SELECT acct_period_id
44 into l_acct_period_id
45 FROM org_acct_periods
46 WHERE organization_id = l_org_id
47 AND period_name = l_acct_period;
48 exception
49 when others then
50 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input Parameters. ');
51 errStr := 'Invalid Account period '||SQLCODE||' '||substrb(sqlerrm,1,1000);
52 fixInfo := 'Enter a valid account period';
53 statusStr := 'FAILURE';
54 isFatal := 'SUCCESS';
55 goto l_test_end;
56 end;
57 end if;
58 -- l_txn_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('testout',inputs);
59
60 sqltxt :=' SELECT mp.organization_code "Organization Code" '||
61 ' , mmt.organization_id "Organization Id" '||
62 ' , DECODE( TO_CHAR( mp.primary_cost_method ) '||
63 ' , ''1'',''Standard'' '||
64 ' , ''2'',''Average'' '||
65 ' , ''3'',''Periodic Average'' '||
66 ' , ''4'',''Periodic Incremental LIFO'' '||
67 ' , ''5'',''LIFO'' '||
68 ' , ''6'',''FIFO'' '||
69 ' , TO_CHAR( mp.primary_cost_method ) ) '||
70 ' "PrimaryCost Method" '||
71 ' , oap.period_name "Period Name" '||
72 ' , mmt.acct_period_id "Period Id" '||
73 ' , mmt.costed_flag "Costed Flag" '||
74 ' , COUNT(*) "Count" '||
75 'FROM mtl_material_transactions mmt, mtl_parameters mp '||
76 ' , org_acct_periods oap '||
77 'WHERE mmt.organization_id = mp.organization_id '||
78 ' AND mmt.acct_period_id = oap.acct_period_id(+) '||
79 ' AND costed_flag IS NOT NULL '||
80 'GROUP BY mp.organization_code, mmt.organization_id '||
81 ' , mp.primary_cost_method '||
82 ' , oap.period_name, mmt.acct_period_id '||
83 ' , mmt.costed_flag '||
84 ' ORDER BY mp.organization_code, mmt.organization_id '||
85 ' , mmt.acct_period_id, mmt.costed_flag ';
86
87 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Uncosted Transactions in ALL Orgs and ALL Periods ');
88
89 statusStr := 'SUCCESS';
90 isFatal := 'FALSE';
91
92 sqltxt :='SELECT DISTINCT oap.period_name "Period Name" '||
93 ' , oap.acct_period_id "Period Id" '||
94 ' , TO_CHAR( oap.period_start_date,''DD-MON-RR'') "Start Date" '||
95 ' , TO_CHAR( oap.schedule_close_date,''DD-MON-RR'') "Scheduled Close Date" '||
96 ' , TO_CHAR( oap.period_close_date,''DD-MON-RR'') "Close Date" '||
97 ' , oap.open_flag "Open Flag" '||
98 'FROM org_acct_periods oap '||
99 ' , mtl_material_transactions mmt '||
100 'WHERE mmt.costed_flag IS NOT NULL '||
101 ' AND oap.organization_id = mmt.organization_id '||
102 ' AND (mmt.acct_period_id IS NULL OR mmt.acct_period_id=-1) '||
103 ' AND mmt.transaction_date '||
104 ' BETWEEN oap.period_start_date AND oap.schedule_close_date ';
105 if l_org_id is not null then
106 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
107 end if;
108 sqltxt:=sqltxt||' ORDER BY TO_CHAR( oap.period_start_date,''DD-MON-RR'')';
109
110 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Related Period Information');
111
112 statusStr := 'SUCCESS';
113 isFatal := 'FALSE';
114
115
116 sqltxt := 'SELECT mmt.transaction_id "Txn Id" '||
117 ' , mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)" '||
118 ' , mmt.transaction_date "Txn Date" '||
119 ' , mmt.acct_period_id "Period Id" '||
120 ' , mmt.transaction_quantity "Txn Qty" '||
121 ' , mmt.primary_quantity "Prim Qty" '||
122 ' , mmt.transaction_uom "Uom" '||
123 ' , tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)" '||
124 ' , mmt.subinventory_code "Subinv" '||
125 ' , mmt.locator_id "Stock Locator" '||
126 ' , mmt.revision "Rev" '||
127 ' , mmt.costed_flag "Costed Flag" '||
128 ' ,(SELECT COUNT(*) FROM mtl_cst_actual_cost_details '||
129 ' WHERE transaction_id=mmt.transaction_id) "Actual Cost Records" '||
130 ' , distribution_account_id "Distrib Account Id" '||
131 ' , mmt.cost_group_id "Cost Group Id" '||
132 ' , mmt.transfer_cost_group_id "Transfer Cost Group Id" '||
133 ' , mmt.flow_schedule "Flow Schedule" '||
134 ' , mmt.transaction_group_id "Txn Group Id" '||
135 ' , mmt.transaction_set_id "Txn Set Id" '||
136 ' , mmt.transaction_cost "Txn Cost" '||
137 ' , mmt.creation_date "Created" '||
138 ' , mmt.last_update_date "Last Updated" '||
139 ' , ml.meaning || '' ('' ||mmt.transaction_action_id|| '')'' '||
140 ' "Txn Action (Id)" '||
141 ' , mmt.completion_transaction_id "Completion Txn Id" '||
142 ' , st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" '||
143 ' , mmt.transaction_source_id "Txn Source Id" '||
144 ' , mmt.transaction_source_name "Txn Source" '||
145 ' , mmt.source_code "Source|Code" '||
146 ' , mmt.source_line_id "Source Line Id" '||
147 ' , mmt.request_id "Txn Request Id" '||
148 ' , mmt.operation_seq_num "Operation|Seq Num" '||
149 ' , mmt.transfer_transaction_id "Transfer Txn Id" '||
150 ' , mmt.transfer_organization_id "Transfer Organization Id" '||
151 ' , mmt.transfer_subinventory "Transfer Subinv" '||
152 ' , mmt.shipment_number '||
153 ' , mmt.error_code "Error Code" '||
154 ' , mmt.error_explanation "Error Explanation" '||
155 ' FROM mtl_material_transactions mmt '||
156 ' , mtl_item_flexfields mif '||
157 ' , mtl_transaction_types tt '||
158 ' , mtl_txn_source_types st '||
159 ' , mfg_lookups ml '||
160 'WHERE (mmt.acct_period_id IS NULL OR mmt.acct_period_id=-1) '||
161 ' AND mmt.inventory_item_id = mif.inventory_item_id(+) '||
162 ' AND mmt.organization_id = mif.organization_id(+) '||
163 ' AND mmt.transaction_type_id = tt.transaction_type_id(+) '||
164 ' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+) '||
165 ' AND mmt.transaction_action_id=ml.lookup_code '||
166 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' '||
167 ' AND costed_flag IS NOT NULL ';
168 if l_org_id is not null then
169 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
170 end if;
171 sqltxt :=sqltxt||' ORDER BY costed_flag, transaction_id';
172
173 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Uncosted Txn in MMT with an invalid ACCT_PERIOD_ID in Organization');
174
175 statusStr := 'SUCCESS';
176 isFatal := 'FALSE';
177
178
179 sqltxt := 'SELECT mp.primary_cost_method '||
180 ' , DECODE( TO_CHAR( mp.primary_cost_method ), ''1'',''Standard'' '||
181 ' , ''2'',''Average'' '||
182 ' , ''3'',''Periodic Average'' '||
183 ' , ''4'',''Periodic Incremental LIFO'' '||
184 ' , ''5'',''LIFO'' '||
185 ' , ''6'',''FIFO'' '||
186 ' , TO_CHAR( mp.primary_cost_method ) ) "Primary Cost Method" '||
187 ' , NVL( br.resource_code , ''null'' ) "Resource Code" '||
188 ' , mp.default_cost_group_id "Default Cost Group" '||
189 ' , ccg.cost_group '||
190 ' , mp.pm_cost_collection_enabled '||
191 ' , DECODE( TO_CHAR( mp.pm_cost_collection_enabled ), ''1'', ''Yes'' '||
192 ' , ''2'', ''No'' '||
193 ' , TO_CHAR( mp.pm_cost_collection_enabled ) ) "Project Cost Collect Enabled" '||
194 ' , mp.project_reference_enabled '||
195 ' , DECODE( TO_CHAR( NVL(mp.project_reference_enabled, 2)),''1'', ''Yes'' '||
196 ' , ''2'', ''No'' '||
197 ' , TO_CHAR( mp.project_reference_enabled ) ) "Project Reference Enabled" '||
198 ' , material_account '||
199 ', outside_processing_account '||
200 ', material_overhead_account '||
201 ', resource_account '||
202 ', overhead_account '||
203 ', expense_account '||
204 ', mp.cost_cutoff_date, mp.cost_group_accounting '||
205 'FROM mtl_parameters mp, bom_resources br, cst_cost_groups ccg '||
206 'WHERE mp.default_material_cost_id = br.resource_id(+) '||
207 ' AND mp.default_cost_group_id = ccg.cost_group_id(+)';
208
209 if l_org_id is not null then
210 sqltxt :=sqltxt||' and mp.organization_id = '||l_org_id;
211 end if;
212
213 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Cost-related Parameters from MTL_PARAMETERS');
214
215 statusStr := 'SUCCESS';
216 isFatal := 'FALSE';
217
218
219 sqltxt :='SELECT ( SELECT organization_code '||
220 ' FROM mtl_parameters '||
221 ' WHERE organization_id=from_organization_id ) || '' ( '' '||
222 ' ||from_organization_id|| '' )'' "From Organization" '||
223 ', ( SELECT organization_code '||
224 ' FROM mtl_parameters '||
225 ' WHERE organization_id=to_organization_id ) || '' ( '' '||
226 ' ||to_organization_id|| '' )'' "To |Organization" '||
227 ' , DECODE( intransit_type, '||
228 ' 1, ''Direct'', '||
229 ' 2, ''Intransit'', '||
230 ' intransit_type ) "Intransit Type" '||
231 ' , DECODE( fob_point, '||
232 ' 1, ''Shipment'', '||
233 ' 2, ''Receipt'', '||
234 ' fob_point ) "FOB Point" '||
235 ' , DECODE( internal_order_required_flag, '||
236 ' 1, ''Yes'', '||
237 ' 2, ''No'', '||
238 ' internal_order_required_flag ) '||
239 ' "Internal Order|Required Flag" '||
240 ' , DECODE( matl_interorg_transfer_code, '||
241 ' 1, ''No Transfer Charges'', '||
242 ' 2, ''Requested added value'', '||
243 ' 3, ''Requested % of Txn Value'', '||
244 ' 4, ''Predefined % of Txn Value'', '||
245 ' matl_interorg_transfer_code ) '||
246 ' "Matl Interorg|Transfer Code" '||
247 ' , DECODE( elemental_visibility_enabled, '||
248 ' ''Y'', ''Yes'', '||
249 ' ''N'', ''No'', '||
250 ' elemental_visibility_enabled) '||
251 ' "Elemental|Visibility|Enabled" '||
252 ' , interorg_trnsfr_charge_percent "Interorg Transfer Charge %" '||
253 ' , intransit_inv_account "Intransit Inv Account" '||
254 ' , interorg_transfer_cr_account "Interorg Transfer CR Account" '||
255 ' , interorg_receivables_account "Interorg|Receivables Account" '||
256 ' , interorg_payables_account "Interorg Payables Account" '||
257 ' , interorg_price_var_account "Interorg Price Var Account" '||
258 ' FROM mtl_interorg_parameters ';
259 if l_org_id is not null then
260 sqltxt :=sqltxt||' WHERE ( from_organization_id = '||l_org_id||
261 ' OR to_organization_id = '||l_org_id||')';
262 end if;
263
264 sqltxt:=sqltxt||' ORDER BY 1, 2 ';
265
266 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Interorganization Relationships');
267
268 statusStr := 'SUCCESS';
269 isFatal := 'FALSE';
270
271 sqltxt :='SELECT ccg.cost_group "Cost Group" '||
272 ' , ccg.cost_group_id "Cost Group Id" '||
273 ' , ml.meaning '||
274 ' ||'' (''||ccg.cost_group_type||'')'' "Cost Group Type" '||
275 ' , ccg.description "Description" '||
276 ' , ccg.disable_date "Disable Date" '||
277 'FROM cst_cost_groups ccg, mfg_lookups ml '||
278 'WHERE ml.lookup_type = ''CST_COST_GROUP_TYPE'' '||
279 ' AND ccg.cost_group_type = ml.lookup_code ' ;
280 if l_org_id is not null then
281 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
282 end if;
283 sqltxt :=sqltxt||' ORDER BY cost_group';
284
288 isFatal := 'FALSE';
285 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Cost Groups ');
286
287 statusStr := 'SUCCESS';
289
290 sqltxt :='SELECT cost_group_id '||
291 ' , material_account '||
292 ' , material_overhead_account '||
293 ' , resource_account '||
294 ' , overhead_account '||
295 ' , outside_processing_account '||
296 ' , average_cost_var_account '||
297 ' , encumbrance_account '||
298 ' , expense_account '||
299 ' , payback_mat_var_account '||
300 ' , payback_res_var_account '||
301 ' , payback_osp_var_account '||
302 ' , payback_moh_var_account '||
303 ' , payback_ovh_var_account '||
304 ' FROM cst_cost_group_accounts ccga' ;
305 if l_org_id is not null then
306 sqltxt :=sqltxt||' where organization_id = '||l_org_id;
307 end if;
308 sqltxt :=sqltxt||' ORDER BY cost_group_id';
309 sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;
310
311 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Cost Group Accounts ');
312
313 statusStr := 'SUCCESS';
314 isFatal := 'FALSE';
315
316 sqltxt :=' SELECT owner "Owner", object_name "Name", object_type "Type" '||
317 ' , status "Status" '||
318 ' , last_ddl_time "Last Compile Date" '||
319 ' , created "Creation Date" '||
320 'FROM all_objects '||
321 'WHERE status=''INVALID'' '||
322 ' AND object_name LIKE ''CST%'' '||
323 ' AND owner LIKE ''%'' '||
324 'ORDER BY object_name, object_type ';
325
326 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Invalid Costing database Objects');
327
328 statusStr := 'SUCCESS';
329 isFatal := 'FALSE';
330
331 sqltxt :='SELECT b.user_profile_option_name "Long Name" '||
332 ' , a.profile_option_name "Short Name" '||
333 ' , DECODE( c.level_id, 10001, ''Site'' '||
334 ' , 10002, ''Application'' '||
335 ' , 10003, ''Responsibility'' '||
336 ' , 10004, ''User'' '||
337 ' , ''Unknown'') "Level" '||
338 ' , DECODE( c.level_id, 10001, ''Site'' '||
339 ' , 10002, NVL(h.application_short_name, '||
340 ' TO_CHAR( c.level_value)) '||
341 ' , 10003, NVL(g.responsibility_name, '||
342 ' TO_CHAR( c.level_value)) '||
343 ' , 10004, NVL(e.user_name, '||
344 ' TO_CHAR(c.level_value)) '||
345 ' , ''Unknown'') "Level Value" '||
346 ' , c.profile_option_value "Profile Value" '||
347 ' , TO_CHAR( c.last_update_date,''DD-MON-YYYY HH24:MI'') '||
348 ' "Updated Date" '||
349 ' , NVL( d.user_name, TO_CHAR( c.last_updated_by)) "Updated By" '||
350 'FROM fnd_profile_options a '||
351 ' , fnd_profile_options_vl b '||
352 ' , fnd_profile_option_values c '||
353 ' , fnd_user d , fnd_user e '||
354 ' , fnd_responsibility_vl g '||
355 ' , fnd_application h '||
356 'WHERE a.profile_option_name = b.profile_option_name '||
357 ' AND a.profile_option_id = c.profile_option_id '||
358 ' AND a.application_id = c.application_id '||
359 ' AND c.last_updated_by = d.user_id (+) '||
360 ' AND c.level_value = e.user_id (+) '||
361 ' AND c.level_value = g.responsibility_id (+) '||
362 ' AND c.level_value = h.application_id (+) '||
363 ' AND ( a.profile_option_name LIKE ''CST%'' '||
364 ' OR '||
365 ' a.profile_option_name IN ( '||
366 ' ''HR_CROSS_BUSINESS_GROUP'' , '||
367 ' ''INV:EXPENSE_TO_ASSET_TRANSFER'' , '||
368 ' ''INVTP_COSTGROUP_TXN'' , '||
369 ' ''MRP_DEBUG'' , '||
370 ' ''MRP_TRACE'', '||
371 ' ''UPDATE_AVG_TXN'' ) ) '||
372 ' ORDER BY b.user_profile_option_name, c.level_id';
373
374 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Related Costing Profile Options');
375
376 statusStr := 'SUCCESS';
377 isFatal := 'FALSE';
378
379 sqltxt :='SELECT COUNT(*) '||
380 'FROM mtl_material_transactions ' ;
381
382 if nvl(l_proc_flag, 'A') <> 'E' then
383 sqltxt := sqltxt||'WHERE costed_flag <> ''Y'' ';
384 else
385 sqltxt := sqltxt||'WHERE costed_flag = ''E'' ';
386 end if;
387
388 if l_org_id is not null then
389 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
390 end if;
391
392 if l_acct_period_id is not null then
393 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
394 end if;
395
396 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Count uncosted records in MMT');
397
398 statusStr := 'SUCCESS';
399 isFatal := 'FALSE';
400
401 sqltxt :='SELECT error_code "Error Code" '||
402 ' , error_explanation "Error Explanation" '||
403 ' , costed_flag "Costed|Flag" '||
404 ' , COUNT(*) "Count" '||
405 'FROM mtl_material_transactions '||
406 'WHERE costed_flag IS NOT NULL ';
407
408 if l_org_id is not null then
409 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
410 end if;
411
412 if l_acct_period_id is not null then
413 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
414 end if;
415
416 sqltxt := sqltxt||'GROUP BY error_code, error_explanation, costed_flag';
417
421 isFatal := 'FALSE';
418 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Distinct Errors in MMT ');
419
420 statusStr := 'SUCCESS';
422
423 sqltxt :='SELECT costed_flag "Costed Flag" '||
424 ' , COUNT(*) "Count" '||
425 'FROM mtl_material_transactions '||
426 'WHERE costed_flag IS NOT NULL ';
427 if l_org_id is not null then
428 sqltxt :=sqltxt||' and organization_id = '||l_org_id;
429 end if;
430
431 if l_acct_period_id is not null then
432 sqltxt := sqltxt||' and acct_period_id = '||l_acct_period_id;
433 end if;
434
435 sqltxt := sqltxt||'GROUP BY costed_flag';
436 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Count of distinct costed_flag in MMT ');
437
438 statusStr := 'SUCCESS';
439 isFatal := 'FALSE';
440
441
442 sqltxt :='SELECT mmt.costed_flag "Costed Flag" '||
443 ' , tt.transaction_type_name '||
444 ' || '' ( '' || mmt.transaction_type_id || '' )'' "Txn Type (Id)" '||
445 ' , ml.meaning '||
446 ' || '' ( '' || mmt.transaction_action_id || '' )'' '||
447 ' "Txn Action (Id)" '||
448 ' , st.transaction_source_type_name '||
449 ' || '' ( '' || mmt.transaction_source_type_id || '' )'' '||
450 ' "Txn Source Type (Id)" '||
451 ' , COUNT(*) "Count" '||
452 'FROM mtl_material_transactions mmt, mtl_transaction_types tt '||
453 ' , mfg_lookups ml, mtl_txn_source_types st '||
454 'WHERE mmt.transaction_type_id = tt.transaction_type_id(+) '||
455 ' AND mmt.transaction_action_id = ml.lookup_code '||
456 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' '||
457 ' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+) ' ;
458 if nvl(l_proc_flag, 'A') <> 'E' then
459 sqltxt := sqltxt||' and mmt.costed_flag <> ''Y'' ';
460 else
461 sqltxt := sqltxt||' and mmt.costed_flag = ''E'' ';
462 end if;
463
464
465 if l_org_id is not null then
466 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
467 end if;
468
469 if l_acct_period_id is not null then
470 sqltxt := sqltxt||' and mmt.acct_period_id = '||l_acct_period_id;
471 end if;
472
473 sqltxt :=sqltxt||' GROUP BY mmt.costed_flag, tt.transaction_type_name '||
474 ' , mmt.transaction_type_id '||
475 ' , ml.meaning '||
476 ' , mmt.transaction_action_id '||
477 ' , st.transaction_source_type_name '||
478 ' , mmt.transaction_source_type_id '||
479 'ORDER BY 1, 2, 3 ';
480
481
482 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Transactions Type Information of UnCosted Txns ');
483
484 statusStr := 'SUCCESS';
485 isFatal := 'FALSE';
486
487
488 sqltxt :='SELECT mmt.transaction_id "Txn Id" '||
489 ' , mmt.costed_flag "Costed Flag" '||
490 ' , mif.item_number '||
491 ' ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)" '||
492 ' , mta.transaction_date "Txn Date" '||
493 ' , mta.transaction_value "Txn Value" '||
494 ' , mta.base_transaction_value "Base Txn Value" '||
495 ' , mta.gl_batch_id "GL Batch Id" '||
496 'FROM mtl_material_transactions mmt '||
497 ' , mtl_item_flexfields mif '||
498 ' , mtl_transaction_accounts mta '||
499 'WHERE mmt.inventory_item_id = mif.inventory_item_id(+) '||
500 ' AND mmt.organization_id = mif.organization_id(+) '||
501 ' AND mmt.transaction_id = mta.transaction_id';
502
503 if nvl(l_proc_flag, 'A') <> 'E' then
504 sqltxt := sqltxt||' and mmt.costed_flag <> ''Y'' ';
505 else
506 sqltxt := sqltxt||' and mmt.costed_flag = ''E'' ';
507 end if;
508
509
510 if l_org_id is not null then
511 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
512 end if;
513 if l_acct_period_id is not null then
514 sqltxt := sqltxt||' and mmt.acct_period_id = '||l_acct_period_id;
515 end if;
516
517 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Account transactions ( if already created ) of uncosted Txn');
518
519 statusStr := 'SUCCESS';
520 isFatal := 'FALSE';
521
522
523 sqltxt :='SELECT DISTINCT( msi.secondary_inventory_name ) "Name" '||
524 ' , msi.description "Description" '||
525 ' , msi.disable_date "Disable|Date" '||
526 ' , DECODE( msi.asset_inventory, 1, ''Yes'', '||
527 ' 2, ''No'', '||
528 ' msi.asset_inventory ) "Asset|Inventory" '||
529 ' , msi.default_cost_group_id "Default|Cost Group Id" '||
530 ' , DECODE( msi.reservable_type, 1, ''Yes'', '||
531 ' 2, ''No'', '||
532 ' msi.reservable_type) "Reservable|Type" '||
533 ' , DECODE( msi.inventory_atp_code, 1, ''Inventory included in atp calculation'', '||
534 ' 2, ''Inventory not included in atp calculation'', '||
535 ' msi.inventory_atp_code ) "Inventory|ATP Code" '||
536 ' , DECODE( msi.quantity_tracked, 1, ''Yes'', '||
537 ' 2, ''No'', '||
538 ' msi.quantity_tracked ) "Quantity|Tracked" '||
539 'FROM mtl_material_transactions mmt '||
540 ' , mtl_secondary_inventories msi '||
541 'WHERE mmt.organization_id = msi.organization_id '||
542 ' AND '||
543 ' ( msi.secondary_inventory_name = mmt.subinventory_code '||
544 ' OR '||
545 ' msi.secondary_inventory_name = mmt.transfer_subinventory '||
546 ' )';
547 if nvl(l_proc_flag, 'A') <> 'E' then
551 end if;
548 sqltxt := sqltxt||' and mmt.costed_flag <> ''Y'' ';
549 else
550 sqltxt := sqltxt||' and mmt.costed_flag = ''E'' ';
552
553 if l_org_id is not null then
554 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
555 end if;
556 if l_acct_period_id is not null then
557 sqltxt := sqltxt||' and mmt.acct_period_id = '||l_acct_period_id;
558 end if;
559
560
561 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, ' Subinventory Information of Txns in MMT');
562
563 statusStr := 'SUCCESS';
564 isFatal := 'FALSE';
565
566
567 sqltxt :='SELECT DISTINCT mif.item_number "Item Number" '||
568 ' , mmt.inventory_item_id "Item Id" '||
569 ' , mif.description "Description" '||
570 ' , mif.last_update_date "Last updated" '||
571 ' , mif.inventory_item_flag "Inventory Item Flag" '||
572 ' , mif.inventory_asset_flag "Inventory Asset Flag" '||
573 ' , ( SELECT DISTINCT '||
574 ' DECODE( TO_CHAR( cic.inventory_asset_flag ) '||
575 ' , ''1'', ''Y'' '||
576 ' , ''2'', ''N'' '||
577 ' , cic.inventory_asset_flag ) '||
578 ' || '' ('' ||cic.inventory_asset_flag|| '')'' '||
579 ' FROM cst_item_costs cic '||
580 ' , mtl_parameters mp '||
581 ' WHERE cic.cost_type_id = mp.primary_cost_method' ;
582 if l_org_id is not null then
583 sqltxt :=sqltxt||' and cic.organization_id = '||l_org_id||' and mp.organization_id = '||l_org_id;
584 end if;
585 sqltxt:=sqltxt||' AND cic.inventory_item_id = mmt.inventory_item_id ) '||
586 ' "Costing Asset Flag" '||
587 ' , mif.inventory_item_status_code "Inventory Item Status Code" '||
588 ' , mif.costing_enabled_flag "Costing Enabled Flag" '||
589 ' , mif.default_include_in_rollup_flag "Default Include|In Rollup Flag" '||
590 ' , mif.enabled_flag "Enabled Flag" '||
591 ' , mif.start_date_active "Start Date Active" '||
592 ' , mif.end_date_active "End Date Active" '||
593 ' , DECODE( TO_CHAR( NVL( mif.revision_qty_control_code,1 ) ) '||
594 ' , ''1'',''N'', ''2'', ''Y'' '||
595 ' , mif.revision_qty_control_code) "Revision|Controlled" '||
596 'FROM mtl_material_transactions mmt '||
597 ' , mtl_item_flexfields mif '||
598 'WHERE mmt.organization_id = mif.organization_id '||
599 ' AND mmt.inventory_item_id = mif.inventory_item_id';
600 if nvl(l_proc_flag, 'A') <> 'E' then
601 sqltxt := sqltxt||' and mmt.costed_flag <> ''Y'' ';
602 else
603 sqltxt := sqltxt||' and mmt.costed_flag = ''E'' ';
604 end if;
605
606
607 if l_org_id is not null then
608 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
609 end if;
610 if l_acct_period_id is not null then
611 sqltxt := sqltxt||' and mmt.acct_period_id = '||l_acct_period_id;
612 end if;
613 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Item Information of Txns in MMT');
614
615 statusStr := 'SUCCESS';
616 isFatal := 'FALSE';
617
618
619 sqltxt :='SELECT mmt.transaction_id "Txn Id" '||
620 ' , mif.item_number ||'' (''|| mmt.inventory_item_id ||'')'' "Item (Id)" '||
621 ' , mmt.transaction_date "Txn Date" '||
622 ' , mmt.transaction_quantity "Txn Qty" '||
623 ' , mmt.primary_quantity "Prim Qty" '||
624 ' , mmt.transaction_uom "Uom" '||
625 ' , tt.transaction_type_name ||'' (''||mmt.transaction_type_id||'')'' "Txn Type (Id)" '||
626 ' , mmt.subinventory_code "Subinv" '||
627 ' , mmt.locator_id "Stock Locator" '||
628 ' , mmt.revision "Rev" '||
629 ' , mmt.costed_flag "Costed Flag" '||
630 ' ,(SELECT COUNT(*) FROM mtl_cst_actual_cost_details '||
631 ' WHERE transaction_id=mmt.transaction_id) "Actual Cost Records" '||
632 ' , distribution_account_id "Distrib Account|Id" '||
633 ' , mmt.cost_group_id "Cost Group Id" '||
634 ' , mmt.transfer_cost_group_id "Transfer Cost Group Id" '||
635 ' , mmt.flow_schedule "Flow Schedule" '||
636 ' , mmt.transaction_group_id "Txn Group Id" '||
637 ' , mmt.transaction_set_id "Txn Set Id" '||
638 ' , mmt.transaction_cost "Txn Cost" '||
639 ' , mmt.creation_date "Created" '||
640 ' , mmt.last_update_date "Last Updated" '||
641 ' , ml.meaning || '' ('' ||mmt.transaction_action_id|| '')'' '||
642 ' "Txn Action (Id)" '||
643 ' , mmt.completion_transaction_id "Completion Txn Id" '||
644 ' , st.transaction_source_type_name ||'' (''|| mmt.transaction_source_type_id ||'')'' "Txn Source Type (Id)" '||
645 ' , mmt.transaction_source_id "Txn Source Id" '||
646 ' , mmt.transaction_source_name "Txn Source" '||
647 ' , mmt.source_code "Source Code" '||
648 ' , mmt.source_line_id "Source Line Id" '||
649 ' , mmt.request_id "Txn Request Id" '||
650 ' , mmt.operation_seq_num "Operation Seq Num" '||
651 ' , mmt.transfer_transaction_id "Transfer Txn Id" '||
652 ' , mmt.transfer_organization_id "Transfer|Organization Id" '||
653 ' , mmt.transfer_subinventory "Transfer Subinv" '||
654 ' , mmt.shipment_number '||
655 ' , mmt.error_code "Error Code" '||
656 ' , mmt.error_explanation "Error Explanation" '||
657 'FROM mtl_material_transactions mmt '||
658 ' , mtl_item_flexfields mif '||
659 ' , mtl_transaction_types tt '||
660 ' , mtl_txn_source_types st '||
661 ' , mfg_lookups ml '||
662 'WHERE mmt.inventory_item_id = mif.inventory_item_id(+) '||
663 ' AND mmt.organization_id = mif.organization_id(+) '||
664 ' AND mmt.transaction_type_id = tt.transaction_type_id(+) '||
665 ' AND mmt.transaction_source_type_id = st.transaction_source_type_id(+) '||
666 ' AND mmt.transaction_action_id=ml.lookup_code '||
667 ' AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' ';
668
669 if nvl(l_proc_flag, 'A') <> 'E' then
670 sqltxt := sqltxt||' and mmt.costed_flag <> ''Y'' ';
671 else
672 sqltxt := sqltxt||' and mmt.costed_flag = ''E'' ';
673 end if;
674
675 if l_org_id is not null then
676 sqltxt :=sqltxt||' and mmt.organization_id = '||l_org_id;
677 end if;
678 if l_acct_period_id is not null then
679 sqltxt := sqltxt||' and mmt.acct_period_id = '||l_acct_period_id;
680 end if;
681
682
683 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Uncosted records in MMT');
684
685 statusStr := 'SUCCESS';
686 isFatal := 'FALSE';
687
688
689 sqltxt :='SELECT o.name "Name" '||
690 ' , DECODE( o.type#, 9, ''Spec'', 11, ''Body'', o.type# ) "Type" '||
691 ' , SUBSTR( s.source, INSTR( s.source, ''$Header'',1 ,1)+9, 12 ) "Filename" '||
692 ' , SUBSTR( s.source , '||
693 ' INSTR( s.source ,''.'',10,1)+5, '||
694 ' INSTR( s.source ,'' '',10,3)- '||
695 ' INSTR( s.source ,'' '',10,2) ) "Version" '||
696 ' , DECODE( o.status, 0, ''NA'', 1, ''VALID'', ''INVALID'' ) "Status" '||
697 'FROM sys.source$ s, sys.obj$ o, sys.user$ u '||
698 'WHERE u.name = ''APPS'' '||
699 ' AND o.owner# = u.user# '||
700 ' AND s.obj# = o.obj# '||
701 ' AND s.line = 2 '||
702 ' AND s.source like ''%Header: %'' '||
703 ' AND o.name IN ( ''CSTPACDP'', ''CSTPACIN'', ''CSTPACHK'', ''CSTPACVP'', ''CSTPAVCP'' '||
704 ' , ''CSTPPACQ'', ''CSTPPAHK'' , ''CSTPAPBR'', ''CSTPAPPR'' '||
705 ' , ''INV_COST_GROUP_PVT'', ''INV_COST_GROUP_UPDATE'', ''INV_WWACST'' ) '||
706 ' ORDER BY o.name, o.type#';
707
708 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Version of relevant Packages');
709
710 statusStr := 'SUCCESS';
711 isFatal := 'FALSE';
712 fixInfo := 'OK';
713 errStr :='No Error!';
714
715
716 /**
717 else
718 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Invalid Input parameters');
719 statusStr := 'FAILURE';
720 errStr := 'org_id null';
721 fixInfo := 'Org or OrdID input is required ';
722 isFatal := 'SUCCESS';
723 end if;
724 **/
725 -- construct report
726 <<l_test_end>>
727 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
728 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
729 END runTest;
730
731 PROCEDURE getComponentName(name OUT NOCOPY VARCHAR2) IS
732 BEGIN
733 name := 'UnCosted Transactions';
734 END getComponentName;
735
736 PROCEDURE getTestDesc(descStr OUT NOCOPY VARCHAR2) IS
737 BEGIN
738 descStr := 'Get UnCosted Transactions Information';
739 END getTestDesc;
740
741 PROCEDURE getTestName(name OUT NOCOPY VARCHAR2) IS
742 BEGIN
743 name := 'UnCosted Transactions';
744 END getTestName;
745
746 PROCEDURE getDependencies (package_names OUT NOCOPY JTF_DIAG_DEPENDTBL) IS
747 tempDependencies JTF_DIAG_DEPENDTBL;
748
749 BEGIN
750 package_names := JTF_DIAGNOSTIC_ADAPTUTIL.initDependencyTable;
751 END getDependencies;
752
753 PROCEDURE isDependencyPipelined (str OUT NOCOPY VARCHAR2) IS
754 BEGIN
755 str := 'FALSE';
756 END isDependencyPipelined;
757
758
759 PROCEDURE getOutputValues(outputValues OUT NOCOPY JTF_DIAG_OUTPUTTBL) IS
760 tempOutput JTF_DIAG_OUTPUTTBL;
761 BEGIN
762 tempOutput := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
763 outputValues := tempOutput;
764 EXCEPTION
765 when others then
766 outputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initOutputTable;
767 END getOutputValues;
768
769
770 PROCEDURE getDefaultTestParams(defaultInputValues OUT NOCOPY JTF_DIAG_INPUTTBL) IS
771 tempInput JTF_DIAG_INPUTTBL;
772 BEGIN
773 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
774 --tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'testout','');
775 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'OrgId','LOV-oracle.apps.inv.diag.lov.OrganizationLov');
776 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'AcctPeriod','LOV-oracle.apps.inv.diag.lov.PeriodLov');
777 tempInput := JTF_DIAGNOSTIC_ADAPTUTIL.addInput(tempInput,'CostFlag','LOV-oracle.apps.inv.diag.lov.ErroredAllLov');
778 defaultInputValues := tempInput;
779 EXCEPTION
780 when others then
781 defaultInputValues := JTF_DIAGNOSTIC_ADAPTUTIL.initinputtable;
782 END getDefaultTestParams;
783
784 Function getTestMode return INTEGER IS
785 BEGIN
786 return JTF_DIAGNOSTIC_ADAPTUTIL.ADVANCED_MODE;
787
788 END getTestMode;
789
790 END;