[Home] [Help]
PACKAGE BODY: APPS.WIP_DIAG_DATA_COLL
Source
1 package body WIP_DIAG_DATA_COLL as
2 /* $Header: WIPDCOLB.pls 120.1.12010000.2 2008/10/02 22:19:47 ntangjee ship $ */
3
4 procedure disc_lot_job (p_wip_entity_id IN NUMBER) is
5 l_dummy number ;
6 row_limit NUMBER;
7 BEGIN
8 row_limit := 1000;
9 sqltxt :=
10 ' select a.wip_entity_id , ' ||
11 ' b.wip_entity_name, ' ||
12 ' decode(b.entity_type,1, ''1=Discrete Job'', ' ||
13 ' 2, ''2=Repetitive Assly'', ' ||
14 ' 3, ''3=Closed Discr Job'', ' ||
15 ' 4, ''4=Flow Schedule'', ' ||
16 ' 5, ''5=Lot Based Job'', ' ||
17 ' b.entity_type) entity_type, ' ||
18 ' a.organization_id, ' ||
19 ' p.organization_code, ' ||
20 ' a.primary_item_id, ' ||
21 ' substrb(m.concatenated_segments, 1, 30) item_name, ' ||
22 ' decode(a.status_type, ' ||
23 ' 1,''Unreleased'', ' ||
24 ' 3, ''Released'', ' ||
25 ' 4, ''Complete'', ' ||
26 ' 5, ''Complete NoCharge'', ' ||
27 ' 6, ''On Hold'', ' ||
28 ' 7, ''Cancelled'', ' ||
29 ' 8, ''Pend Bill Load'', ' ||
30 ' 9, ''Failed Bill Load'', ' ||
31 ' 10, ''Pend Rtg Load'', ' ||
32 ' 11, ''Failed Rtg Load'', ' ||
33 ' 12, ''Closed'', ' ||
34 ' 13, ''Pending- Mass Loaded'', ' ||
35 ' 14, ''Pending Close'', ' ||
36 ' 15, ''Failed Close'', ' ||
37 ' a.status_type) status_type, ' ||
38 ' decode(a.job_type, 1, ''Standard'', ' ||
39 ' 3, ''Non-Standard'', ' ||
40 ' a.job_type) job_type, ' ||
41 ' a.lot_number, ' ||
42 ' a.completion_subinventory, ' ||
43 ' a.completion_locator_id, ' ||
44 ' a.start_quantity, ' ||
45 ' m.primary_uom_code uom_code, ' ||
46 ' a.quantity_completed, ' ||
47 ' a.quantity_scrapped, ' ||
48 ' a.net_quantity, ' ||
49 ' decode(a.wip_supply_type, 1, ''Push'', ' ||
50 ' 2, ''Assembly Pull'', ' ||
51 ' 3, ''Operation Pull'', ' ||
52 ' 4, ''Bulk'', ' ||
53 ' 5, ''Supplier'', ' ||
54 ' 6, ''Phantom'', ' ||
55 ' 7, ''Based on Bill'', ' ||
56 ' a.wip_supply_type) wip_supply_type, ' ||
57 ' a.class_code, ' ||
58 ' a.scheduled_start_date, ' ||
59 ' a.scheduled_completion_date, ' ||
60 ' a.date_released, ' ||
61 ' a.date_completed, ' ||
62 ' a.date_closed, ' ||
63 ' a.creation_date, ' ||
64 ' a.common_bom_sequence_id, ' ||
65 ' a.common_routing_sequence_id, ' ||
66 ' a.bom_revision, ' ||
67 ' a.routing_revision, ' ||
68 ' nvl(a.alternate_bom_designator, ''PRIMARY'') alternate_bom_designator, ' ||
69 ' nvl(a.alternate_routing_designator, ''PRIMARY'') alternate_routing_designator, ' ||
70 ' decode(a.overcompletion_tolerance_type, ' ||
71 ' 1, ''Percent'', ' ||
72 ' 2, ''Amount'') Tol_Type, ' ||
73 ' a.overcompletion_tolerance_value Tol_Value ' ||
74 ' from wip_discrete_jobs a , wip_entities b, mtl_system_items_kfv m, mtl_parameters p ' ||
75 ' where b.wip_entity_id = a.wip_entity_id ' ||
76 ' and b.organization_id = a.organization_id ' ||
77 ' and m.inventory_item_id = a.primary_item_id ' ||
78 ' and m.organization_id = a.organization_id ' ||
79 ' and a.organization_id = p.organization_id ';
80
81 if p_wip_entity_id is not null then
82 sqltxt :=sqltxt||' and b.wip_entity_id = '|| p_wip_entity_id;
83 end if;
84
85 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
86 'JOB HEADER ( WIP DISCRETE JOBS , WIP ENTITIES )',null,'Y',row_limit);
87
88 IF (dummy_num = row_limit) THEN
89 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
90 END IF;
91
92 sqltxt :=
93 'select a.operation_seq_num, ' ||
94 ' a.operation_sequence_id, ' ||
95 ' a.standard_operation_id, ' ||
96 ' bso.operation_code , ' ||
97 ' a.department_id, ' ||
98 ' c.department_code, ' ||
99 ' a.description, ' ||
100 ' a.first_unit_start_date, ' ||
101 ' a.first_unit_completion_date, ' ||
102 ' a.last_unit_start_date, ' ||
103 ' a.last_unit_completion_date, ' ||
104 ' a.scheduled_quantity, ' ||
105 ' a.quantity_in_queue , ' ||
106 ' a.quantity_running , ' ||
107 ' a.quantity_waiting_to_move , ' ||
108 ' a.quantity_rejected , ' ||
109 ' a.quantity_scrapped , ' ||
110 ' a.quantity_completed , ' ||
111 ' a.previous_operation_seq_num, ' ||
112 ' a.next_operation_seq_num, ' ||
113 ' a.count_point_type, ' ||
114 ' decode( a.backflush_flag, 1, ''Yes'', ' ||
115 ' 2, ''No'') backflush_flag, ' ||
116 ' a.minimum_transfer_quantity, ' ||
117 ' a.date_last_moved, ' ||
118 ' a.creation_date ' ||
119 'from wip_operations a, wip_entities b, bom_departments c, bom_standard_operations bso ' ||
120 'where a.wip_entity_id = b.wip_entity_id ' ||
121 'and a.organization_id = b.organization_id ' ||
122 'and a.department_id = c.department_id ' ||
123 'and a.organization_id = c.organization_id ' ||
124 'and a.standard_operation_id = bso.standard_operation_id(+) ' ||
125 'and a.organization_id = bso.organization_id(+) ';
126
127 if p_wip_entity_id is not null then
128 sqltxt :=sqltxt||' and b.wip_entity_id = '|| p_wip_entity_id;
129 sqltxt :=sqltxt||' order by 1 ';
130 end if;
131
132 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
133 'WIP OPERATIONS',null,'Y',row_limit);
134
135 IF (dummy_num = row_limit) THEN
136 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
137 END IF;
138
139 sqltxt :=
140 'select a.wip_entity_id, '||
141 ' a.inventory_item_id, '||
142 ' substrb(m.concatenated_segments, 1, 30) item_name, '||
143 ' a.organization_id, '||
144 ' a.operation_seq_num, '||
145 ' a.component_sequence_id, '||
146 ' a.department_id, '||
147 ' decode(a.wip_supply_type, 1, ''Push'', '||
148 ' 2, ''Assembly Pull'', '||
149 ' 3, ''Operation Pull'', '||
150 ' 4, ''Bulk'', '||
151 ' 5, ''Supplier'', '||
152 ' 6, ''Phantom'', '||
153 ' 7, ''Based on Bill'', '||
154 ' a.wip_supply_type) wip_supply_type, '||
155 ' a.required_quantity, '||
156 ' a.quantity_issued, '||
157 ' a.quantity_per_assembly, '||
158 ' a.supply_subinventory, '||
159 ' a.supply_locator_id, '||
160 ' a.quantity_allocated, '||
161 ' a.quantity_backordered, '||
162 ' a.quantity_relieved, '||
163 ' a.creation_date '||
164 'from wip_requirement_operations a, wip_entities b, mtl_system_items_kfv m '||
165 'where b.wip_entity_id = a.wip_entity_id '||
166 'and b.organization_id = a.organization_id '||
167 'and a.inventory_item_id = m.inventory_item_id '||
168 'and a.organization_id = m.organization_id ';
169
170 if p_wip_entity_id is not null then
171 sqltxt :=sqltxt||' and b.wip_entity_id = '|| p_wip_entity_id;
172 sqltxt :=sqltxt||' order by operation_seq_num,inventory_item_id ';
173 end if;
174
175 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
176 'WIP REQUIREMENT OPERATIONS',null,'Y',row_limit);
177
178 IF (dummy_num = row_limit) THEN
179 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
180 END IF;
181
182 sqltxt :=
183 'select a.operation_seq_num, '||
184 ' a.resource_seq_num, '||
185 ' a.resource_id, '||
186 ' a.autocharge_type, '||
187 ' c.resource_code, '||
188 ' a.uom_code, '||
189 ' decode (a.basis_type, 1, ''Item'', '||
190 ' 2, ''Lot'', '||
191 ' 3, ''Res Units'', '||
192 ' 4, ''Res Value'', '||
193 ' 5, ''Tot Value'', '||
194 ' 6, ''Activity'', '||
195 ' a.BASIS_TYPE) basis_type, '||
196 ' decode(a.scheduled_flag, 1, ''Yes'', '||
197 ' 2, ''No'', '||
198 ' 3, ''Prior'', '||
199 ' 4, ''Next'', '||
200 ' a.scheduled_flag) scheduled_flag, '||
201 ' a.usage_rate_or_amount , '||
202 ' a.start_date, '||
203 ' a.completion_date, '||
204 ' a.applied_resource_units, '||
205 ' a.applied_resource_value, '||
206 ' a.creation_date '||
207 'from wip_operation_resources a, bom_resources c '||
208 'where a.resource_id = c.resource_id '||
209 'and a.organization_id = c.organization_id ';
210
211
212 if p_wip_entity_id is not null then
213 sqltxt :=sqltxt||' and a.wip_entity_id = '|| p_wip_entity_id;
214 sqltxt :=sqltxt||' order by 1,2';
215 end if;
216
217 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
218 'WIP OPERATION RESOURCES',null,'Y',row_limit);
219
220 IF (dummy_num = row_limit) THEN
221 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
222 END IF;
223
224 sqltxt :=
225 'select a.operation_seq_num, '||
226 ' a.resource_seq_num, '||
227 ' c.resource_code, '||
228 ' a.organization_id, '||
229 ' a.repetitive_schedule_id, '||
230 ' a.start_date, '||
231 ' a.completion_date, '||
232 ' a.assigned_units, '||
233 ' a.creation_date '||
234 'from wip_operation_resource_usage a, wip_operation_resources b, bom_resources c '||
235 'where a.wip_entity_id = b.wip_entity_id '||
236 'and a.operation_seq_num = b.operation_seq_num '||
237 'and nvl(a.repetitive_schedule_id,0) = nvl(b.repetitive_schedule_id,0) '||
238 'and a.resource_seq_num = b.resource_seq_num '||
239 'and b.resource_id = c.resource_id '||
240 'and b.organization_id = c.organization_id ';
241
242
243 if p_wip_entity_id is not null then
244 sqltxt :=sqltxt||' and a.wip_entity_id = '|| p_wip_entity_id;
245 sqltxt :=sqltxt||' order by 1,2 ';
246 end if;
247
248 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
249 'WIP OPERATION RESOURCE USAGES',null,'Y',row_limit);
250
251 IF (dummy_num = row_limit) THEN
252 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
253 END IF;
254
255 sqltxt :=
256 'select acct_period_id, '||
257 ' decode(class_type,1,''Standard Discrete'', '||
258 ' 2,''Repetitive Assembly'', '||
259 ' 3,''Asset non-standard'', '||
260 ' 4,''Expense non-standard'', '||
261 ' 5,''Standard Lot Based'', '||
262 ' 6,''EAM'', '||
263 ' 7,''Expense non-standard Lot Based'', '||
264 ' class_type) class_type, '||
265 ' tl_resource_in, '||
266 ' tl_overhead_in, '||
267 ' tl_outside_processing_in, '||
268 ' pl_material_in, '||
269 ' pl_material_overhead_in, '||
270 ' pl_resource_in, '||
271 ' pl_overhead_in, '||
272 ' pl_outside_processing_in, '||
273 ' tl_material_out, '||
274 ' tl_material_overhead_out, '||
275 ' tl_resource_out, '||
276 ' tl_overhead_out, '||
277 ' tl_outside_processing_out, '||
278 ' pl_material_out, '||
279 ' pl_material_overhead_out, '||
280 ' pl_resource_out, '||
281 ' pl_overhead_out, '||
282 ' pl_outside_processing_out, '||
283 ' tl_scrap_in, '||
284 ' tl_scrap_out, '||
285 ' tl_scrap_var, '||
286 ' creation_date, '||
287 ' last_update_date '||
288 'from wip_period_balances ';
289
290 if p_wip_entity_id is not null then
291 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id;
292 sqltxt :=sqltxt||' order by creation_date ';
293 end if;
294
295 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
296 'WIP PERIOD BALANCES',null,'Y',row_limit);
297
298 IF (dummy_num = row_limit) THEN
299 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
300 END IF;
301
302 sqltxt :=
303 'select wmt.transaction_id, '||
304 ' wmt.group_id, '||
305 ' wmt.fm_operation_code, '||
306 ' wmt.fm_operation_seq_num, '||
307 ' decode (wmt.fm_intraoperation_step_type, '||
308 ' 1, ''Queue'', '||
309 ' 2, ''Run'', '||
310 ' 3, ''ToMove'', '||
311 ' 5, ''Scrap'', '||
312 ' wmt.fm_intraoperation_step_type) fm_intraoperation_step_type, '||
313 ' wmt.to_operation_code, '||
314 ' wmt.to_operation_seq_num, '||
315 ' decode (wmt.to_intraoperation_step_type, '||
316 ' 1, ''Queue'', '||
317 ' 2, ''Run'', '||
318 ' 3, ''ToMove'', '||
319 ' 5, ''Scrap'', '||
320 ' wmt.to_intraoperation_step_type) to_intraoperation_step_type, '||
321 ' wmt.transaction_quantity, '||
322 ' wmt.transaction_uom, '||
323 ' wmt.primary_quantity, '||
324 ' wmt.primary_uom, '||
325 ' wmt.source_code, '||
326 ' wmt.source_line_id, '||
327 ' wmt.organization_id, '||
328 ' wmt.primary_item_id, '||
329 ' wmt.transaction_date, '||
330 ' wmt.creation_date, '||
331 ' wmt.acct_period_id, '||
332 ' wmt.wsm_undo_txn_id, ' ||
333 ' wmt.job_quantity_snapshot, '||
334 ' wmt.batch_id, ' ||
335 ' wmt.scrap_account_id '||
336 'from wip_move_transactions wmt '||
337 'where exists (select 1 '||
338 ' from wip_entities we '||
339 ' where we.wip_entity_id = wmt.wip_entity_id '||
340 ' and we.entity_type <> 2 '|| /* Other than repetitive Schedule */
341 ' ) ';
342
343 if p_wip_entity_id is not null then
344 sqltxt :=sqltxt||' and wmt.wip_entity_id = '|| p_wip_entity_id;
345 sqltxt :=sqltxt||' order by 1 ';
346 end if;
347
348 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
349 'WIP MOVE TRANSACTIONS',null,'Y',row_limit);
350
351 IF (dummy_num = row_limit) THEN
352 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
353 END IF;
354
355 sqltxt :=
356 'select wmti.transaction_id, '||
357 ' group_id, '||
358 ' source_code, '||
359 ' source_line_id, '||
360 ' decode(process_phase, '||
361 ' 1, ''Move Valdn'', '||
362 ' 2, ''Move Proc'', '||
363 ' 3, ''BF Setup'', '||
364 ' process_phase) process_phase_meaning, '||
365 ' decode(process_status, '||
366 ' 1, ''Pending'', '||
367 ' 2, ''Running'', '||
368 ' 3, ''Error'', '||
369 ' 4, ''Completed'', '||
370 ' 5, ''Warning'', '||
371 ' process_status) process_status_meaning, '||
372 ' decode(transaction_type, '||
373 ' 1, ''Move'', '||
374 ' 2, ''Complete'', '||
375 ' 3, ''Return'', '||
376 ' transaction_type) transaction_type_meaning, '||
377 ' repetitive_schedule_id, '||
378 ' fm_operation_seq_num, '||
379 ' fm_operation_code, '||
380 ' decode (fm_intraoperation_step_type, '||
381 ' 1, ''Queue'', '||
382 ' 2, ''Run'', '||
383 ' 3, ''ToMove'', '||
384 ' 5, ''Scrap'', '||
385 ' fm_intraoperation_step_type) fm_intraoperation_step_type, '||
386 ' to_operation_seq_num, '||
387 ' to_operation_code, '||
388 ' decode (to_intraoperation_step_type, '||
389 ' 1, ''Queue'', '||
390 ' 2, ''Run'', '||
391 ' 3, ''ToMove'', '||
392 ' 5, ''Scrap'', '||
393 ' to_intraoperation_step_type) to_intraoperation_step_type, '||
394 ' transaction_quantity, '||
395 ' transaction_uom, '||
396 ' primary_quantity, '||
397 ' primary_uom, '||
398 ' organization_id, '||
399 ' primary_item_id, '||
400 ' transaction_date, '||
401 ' wmti.creation_date, '||
402 ' acct_period_id, '||
403 ' scrap_account_id, '||
404 ' overcompletion_transaction_qty, '||
405 ' overcompletion_primary_qty, '||
406 ' overcompletion_transaction_id, '||
407 ' error_column, '||
408 ' error_message '||
409 'from wip_move_txn_interface wmti, '||
410 ' wip_txn_interface_errors wtie '||
411 'where wmti.transaction_id = wtie.transaction_id (+) ';
412
413
414 if p_wip_entity_id is not null then
415 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id;
416 sqltxt :=sqltxt||' order by 1 ';
417 end if;
418
419 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
420 'WIP MOVE TXN INTERFACE',null,'Y',row_limit);
421
422 IF (dummy_num = row_limit) THEN
423 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
424 END IF;
425
426 /*
427 sqltxt :=
428 'select allocation_id, '||
429 ' organization_id, '||
430 ' demand_source_header_id, '||
431 ' demand_source_line, '||
432 ' user_line_num, '||
433 ' demand_source_delivery, '||
434 ' user_delivery, '||
435 ' quantity_allocated, '||
436 ' quantity_completed, '||
437 ' demand_class, '||
438 ' creation_date '||
439 'from wip_so_allocations ';
440
441 if p_wip_entity_id is not null then
442 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ;
443 sqltxt :=sqltxt||' order by allocation_id ';
444 end if;
445
446 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
447 'WIP SO ALLOCATIONS',null,'Y',row_limit);
448
449 IF (dummy_num = row_limit) THEN
450 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
451 END IF;
452 */
453
454 sqltxt :=
455 'select wcti.transaction_id, '||
456 ' wcti.creation_date, '||
457 ' wcti.last_update_date, '||
458 ' wcti.request_id, '||
459 ' source_code, '||
460 ' source_line_id, '||
461 ' decode(process_phase, '||
462 ' 1, ''Res Valdn'', '||
463 ' 2, ''Res Processing'', '||
464 ' 3, ''Job Close'', '||
465 ' 4, ''Prd Close'', '||
466 ' process_phase) process_phase_meaning, '||
467 ' decode(process_status, '||
468 ' 1, ''Pending'', '||
469 ' 2, ''Running'', '||
470 ' 3, ''Error'', '||
471 ' 4, ''Completed'', '||
472 ' 5, ''Warning'', '||
473 ' process_status) process_status_meaning, '||
474 ' decode(transaction_type, '||
475 ' 1, ''Resource'', '||
476 ' 2, ''Overhead'', '||
477 ' 3, ''OSP'', '||
478 ' 4, ''Cost Update'', '||
479 ' 5, ''PrdClose Var'', '||
480 ' 6, ''JobClose Var'', '||
481 ' transaction_type) transaction_type_meaning, '||
482 ' organization_id, '||
483 ' organization_code, '||
484 ' primary_item_id, '||
485 ' transaction_date, '||
486 ' operation_seq_num, '||
487 ' resource_seq_num, '||
488 ' acct_period_id, '||
489 ' resource_id, '||
490 ' decode(resource_type, '||
491 ' 1, ''Machine'', '||
492 ' 2, ''Person'', '||
493 ' 3, ''Space'', '||
494 ' 4, ''Misc'', '||
495 ' 5, ''Amount'', '||
496 ' resource_type) resource_type, '||
497 ' transaction_quantity, '||
498 ' actual_resource_rate, '||
499 ' transaction_uom, '||
500 ' decode(basis_type, '||
501 ' 1, ''Item'', '||
502 ' 2, ''Lot'', '||
503 ' 3, ''Res Units'', '||
504 ' 4, ''Res Value'', '||
505 ' 5, ''Tot Value'', '||
506 ' 6, ''Activity'') basis_type, '||
507 ' move_transaction_id, '||
508 ' completion_transaction_id, '||
509 ' error_column, '||
510 ' error_message '||
511 'from wip_cost_txn_interface wcti, '||
512 ' wip_txn_interface_errors wtie '||
513 'where wcti.transaction_id = wtie.transaction_id (+) ';
514
515 if p_wip_entity_id is not null then
516 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id ;
517 sqltxt :=sqltxt||' order by transaction_id';
518 end if;
519
523 IF (dummy_num = row_limit) THEN
520 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
521 'WIP COST TXN INTERFACE',null,'Y',row_limit);
522
524 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
525 END IF;
526
527 sqltxt :=
528 'select wt.transaction_id, '||
529 ' wt.creation_date, '||
530 ' wt.last_update_date, '||
531 ' wt.request_id, '||
532 ' wt.source_code, '||
533 ' wt.source_line_id, '||
534 ' wt.group_id, '||
535 ' decode(wt.transaction_type, '||
536 ' 1, ''Resource'', '||
537 ' 2, ''Overhead'', '||
538 ' 3, ''OSP'', '||
539 ' 4, ''Cost Update'', '||
540 ' 5, ''PrdClose Var'', '||
541 ' 6, ''JobClose Var'', '||
542 ' wt.transaction_type) transaction_type_meaning, '||
543 ' wt.organization_id, '||
544 ' wt.primary_item_id, '||
545 ' wt.transaction_date, '||
546 ' wt.operation_seq_num, '||
547 ' wt.resource_seq_num, '||
548 ' wt.acct_period_id, '||
549 ' wt.resource_id, '||
550 ' wt.transaction_quantity, '||
551 ' wt.actual_resource_rate, '||
552 ' wt.standard_resource_rate, '||
553 ' wt.transaction_uom, '||
554 ' wt.move_transaction_id, '||
555 ' wt.completion_transaction_id '||
556 'from wip_transactions wt '||
557 'where exists (select 1 '||
558 ' from wip_entities we '||
559 ' where we.wip_entity_id = wt.wip_entity_id '||
560 ' and we.entity_type <> 2) '; /* Other than Repetitive */
561
562 if p_wip_entity_id is not null then
563 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id ;
564 sqltxt :=sqltxt||' order by transaction_id';
565 end if;
566
567 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
568 'WIP TRANSACTIONS',null,'Y',row_limit);
569
570 IF (dummy_num = row_limit) THEN
571 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
572 END IF;
573
574 sqltxt :=
575 'select transaction_interface_id, '||
576 ' transaction_header_id, '||
577 ' source_code, '||
578 ' source_line_id, '||
579 ' source_header_id, '||
580 ' process_flag, '||
581 ' transaction_mode, '||
582 ' lock_flag, '||
583 ' request_id, '||
584 ' inventory_item_id, '||
585 ' organization_id, '||
586 ' transaction_quantity, '||
587 ' primary_quantity, '||
588 ' transaction_uom, '||
589 ' transaction_date, '||
590 ' subinventory_code, '||
591 ' locator_id, '||
592 ' revision, '||
593 ' transaction_source_id, '||
594 ' decode(transaction_source_type_id, '||
595 ' 1, ''PO'', '||
596 ' 2, ''SO'', '||
597 ' 4, ''MoveOrder'', '||
598 ' 5, ''WIP'', '||
599 ' 6, ''AcctAlias'', '||
600 ' 7, ''Int REQ'', '||
601 ' 8, ''Int Order'', '||
602 ' 9, ''CycleCount'', '||
603 ' 10,''PhyCount'', '||
604 ' 11,''StdCostUpd'', '||
605 ' 12, ''RMA'', '||
606 ' 13, ''INV'', '||
607 ' 17, ''Ext REQ'', '||
608 ' transaction_source_type_id) txn_source_meaning, '||
609 ' decode(transaction_action_id, '||
610 ' 1, ''Issue'', '||
611 ' 2, ''Subinv Xfr'', '||
612 ' 3, ''Org Xfr'', '||
613 ' 4, ''Cycle Count Adj'', '||
614 ' 5, ''Plan Xfr'', '||
615 ' 21, ''Intransit Shpmt'', '||
616 ' 24, ''Cost Update'', '||
617 ' 27, ''Receipt'', '||
618 ' 28, ''Stg Xfr'', '||
619 ' 30, ''Wip scrap'', '||
620 ' 31, ''Assy Complete'', '||
621 ' 32, ''Assy return'', '||
622 ' 33, ''-ve CompIssue'', '||
623 ' 34, ''-ve CompReturn'', '||
624 ' 40, ''Inv Lot Split'', '||
625 ' 41, ''Inv Lot Merge'', '||
626 ' 42, ''Inv Lot Translate'', '||
627 ' 42, ''Inv Lot Translate'', '||
628 ' transaction_action_id) txn_action_meaning, '||
629 ' transaction_type_id, '||
630 ' operation_seq_num, '||
631 ' repetitive_line_id, '||
632 ' transfer_organization, '||
633 ' transfer_subinventory, '||
634 ' transfer_locator, '||
635 ' overcompletion_transaction_qty, '||
636 ' overcompletion_primary_qty, '||
637 ' overcompletion_transaction_id, '||
638 ' error_code, '||
639 ' substr(error_explanation,1,100) error_explanation '||
640 'from mtl_transactions_Interface mti '||
641 'where mti.transaction_source_type_id = 5 ';
642
643 if p_wip_entity_id is not null then
644 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id;
645 sqltxt :=sqltxt||' order by transaction_interface_id, transaction_date ';
646 end if;
647
648 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
649 'MTI TRANSACTIONS',null,'Y',row_limit);
650
651 IF (dummy_num = row_limit) THEN
652 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
653 END IF;
654
655 sqltxt :=
656 'select '||
657 ' transaction_interface_id, '||
658 ' source_code, '||
659 ' source_line_id, '||
660 ' request_id, '||
661 ' lot_number, '||
662 ' lot_expiration_date, '||
663 ' transaction_quantity, '||
664 ' primary_quantity, '||
665 ' serial_transaction_temp_id, '||
666 ' process_flag, '||
667 ' error_code '||
671 ' from mtl_transactions_Interface mti '||
668 'from mtl_transaction_lots_interface mtli '||
669 'where mtli.transaction_interface_id in '||
670 ' (select transaction_interface_id '||
672 ' where mti.transaction_source_type_id = 5 ';
673
674
675 if p_wip_entity_id is not null then
676 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id ||')';
677 sqltxt :=sqltxt||' order by lot_expiration_date ';
678 end if;
679
680 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
681 'MTLI TRANSACTIONS',null,'Y',row_limit);
682
683 IF (dummy_num = row_limit) THEN
684 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
685 END IF;
686
687 sqltxt :=
688 'select '||
689 ' transaction_interface_id, '||
690 ' source_code, '||
691 ' source_line_id, '||
692 ' request_id, '||
693 ' vendor_serial_number, '||
694 ' vendor_lot_number, '||
695 ' fm_serial_number, '||
696 ' to_serial_number, '||
697 ' error_code, '||
698 ' process_flag, '||
699 ' parent_serial_number '||
700 'from mtl_serial_numbers_interface msni '||
701 'where msni.transaction_interface_id in '||
702 ' (select transaction_interface_id '||
703 ' from mtl_transactions_Interface mti '||
704 ' where mti.transaction_source_type_id = 5 ';
705
706 if p_wip_entity_id is not null then
707 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id ||')';
708 sqltxt :=sqltxt||' order by fm_serial_number ';
709 end if;
710
711 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
712 'MSNI TRANSACTIONS',null,'Y',row_limit);
713
714 IF (dummy_num = row_limit) THEN
715 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
716 END IF;
717
718 sqltxt :=
719 'select transaction_temp_id, '||
720 ' transaction_header_id, '||
721 ' source_code, '||
722 ' source_line_id, '||
723 ' transaction_mode, '||
724 ' lock_flag, '||
725 ' transaction_date, '||
726 ' transaction_type_id, '||
727 ' decode(transaction_action_id, '||
728 ' 1, ''Issue'', '||
729 ' 2, ''Subinv Xfr'', '||
730 ' 3, ''Org Xfr'', '||
731 ' 4, ''Cycle Count Adj'', '||
732 ' 5, ''Issue'', '||
733 ' 21, ''Intransit Shpmt'', '||
734 ' 24, ''Cost Update'', '||
735 ' 27, ''Receipt'', '||
736 ' 28, ''Stg Xfr'', '||
737 ' 30, ''Wip scrap'', '||
738 ' 31, ''Assy Complete'', '||
739 ' 32, ''Assy return'', '||
740 ' 33, ''-ve CompIssue'', '||
741 ' 34, ''-ve CompReturn'', '||
742 ' 40, ''Inv Lot Split'', '||
743 ' 41, ''Inv Lot Merge'', '||
744 ' 42, ''Inv Lot Translate'', '||
745 ' 42, ''Inv Lot Translate'', '||
746 ' transaction_action_id) txn_action_meaning, '||
747 ' decode(transaction_source_type_id, '||
748 ' 1, ''PO'', '||
749 ' 2, ''SO'', '||
750 ' 4, ''MoveOrder'', '||
751 ' 5, ''WIP'', '||
752 ' 6, ''AcctAlias'', '||
753 ' 7, ''Int REQ'', '||
754 ' 8, ''Int Order'', '||
755 ' 9, ''CycleCount'', '||
756 ' 10,''PhyCount'', '||
757 ' 11,''StdCostUpd'', '||
758 ' 12, ''RMA'', '||
759 ' 13, ''INV'', '||
760 ' 17, ''Ext REQ'', '||
761 ' transaction_source_type_id) txn_source_meaning, '||
762 ' transaction_source_id, '||
763 ' inventory_item_id, '||
764 ' organization_id, '||
765 ' subinventory_code, '||
766 ' locator_id, '||
767 ' revision, '||
768 ' transaction_quantity, '||
769 ' transaction_uom, '||
770 ' primary_quantity, '||
771 ' trx_source_line_id, '||
772 ' trx_source_delivery_id, '||
773 ' overcompletion_transaction_qty, '||
774 ' overcompletion_primary_qty, '||
775 ' overcompletion_transaction_id, '||
776 ' move_transaction_id, '||
777 ' completion_transaction_id, '||
778 ' source_code, '||
779 ' source_line_id, '||
780 ' transfer_organization, '||
781 ' transfer_subinventory, '||
782 ' transfer_to_location, '||
783 ' move_order_line_id, '||
784 ' reservation_id, '||
785 ' creation_date, '||
786 ' last_update_date, '||
787 ' error_code '||
788 'from mtl_material_transactions_temp '||
789 'where transaction_source_type_id = 5 ';
790
791
792 if p_wip_entity_id is not null then
793 sqltxt :=sqltxt||' and transaction_source_id = '|| p_wip_entity_id ;
794 sqltxt :=sqltxt||' order by transaction_temp_id ';
795 end if;
796
797 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
798 'MMTT TRANSACTIONS',null,'Y',row_limit);
799
800 IF (dummy_num = row_limit) THEN
801 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
802 END IF;
803
804 sqltxt :=
805 'select '||
806 ' transaction_temp_id, '||
807 ' transaction_quantity, '||
808 ' primary_quantity, '||
809 ' lot_number, '||
810 ' lot_expiration_date, '||
811 ' serial_transaction_temp_id, '||
812 ' group_header_id, '||
813 ' put_away_rule_id, '||
814 ' pick_rule_id, '||
815 ' request_id, '||
816 ' creation_date, '||
817 ' error_code '||
818 'from mtl_transaction_lots_temp mtlt '||
822 ' where mmtt.transaction_source_type_id = 5 ';
819 'where mtlt.transaction_temp_id in '||
820 ' (select mmtt.transaction_temp_id '||
821 ' from mtl_material_transactions_temp mmtt '||
823
824 if p_wip_entity_id is not null then
825 sqltxt :=sqltxt||' and mmtt.transaction_source_id = '|| p_wip_entity_id ||')' ;
826 sqltxt :=sqltxt||' order by transaction_temp_id, lot_number ';
827 end if;
828
829 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
830 'MTLT TRANSACTIONS',null,'Y',row_limit);
831
832 IF (dummy_num = row_limit) THEN
833 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
834 END IF;
835
836 sqltxt :=
837 'select '||
838 ' transaction_temp_id, '||
839 ' vendor_serial_number, '||
840 ' vendor_lot_number, '||
841 ' fm_serial_number, '||
842 ' to_serial_number, '||
843 ' serial_prefix, '||
844 ' group_header_id, '||
845 ' parent_serial_number, '||
846 ' end_item_unit_number, '||
847 ' request_id, '||
848 ' creation_date, '||
849 ' error_code '||
850 'from mtl_serial_numbers_temp msnt '||
851 'where msnt.transaction_temp_id in '||
852 ' (select mmtt.transaction_temp_id '||
853 ' from mtl_material_transactions_temp mmtt '||
854 ' where mmtt.transaction_source_type_id = 5 ';
855
856 if p_wip_entity_id is not null then
857 sqltxt :=sqltxt||' and mmtt.transaction_source_id = '|| p_wip_entity_id ||')' ;
858 sqltxt :=sqltxt||' order by transaction_temp_id, fm_serial_number ';
859 end if;
860
861 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
862 'MSNT TRANSACTIONS',null,'Y',row_limit);
863
864 IF (dummy_num = row_limit) THEN
865 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
866 END IF;
867
868 sqltxt :=
869 'select DISTINCT '||
870 ' TRL.LINE_ID MOVE_LINE_ID, '||
871 ' TRL.REQUEST_NUMBER MOVE_NUMBER, '||
872 ' TRL.HEADER_ID MV_HDR_ID, '||
873 ' TRL.LINE_NUMBER MV_LINE_NUM, '||
874 ' decode(TRL.LINE_STATUS, '||
875 ' 1, ''Incomplete'', '||
876 ' 2, ''Pend Aprvl'', '||
877 ' 3, ''Approved'', '||
878 ' 4, ''Not Apprvd'', '||
879 ' 5, ''Closed'', '||
880 ' 6, ''Canceled'', '||
881 ' 7, ''Pre Apprvd'', '||
882 ' 8, ''Part Aprvd'') MV_LINE_STAT, '||
883 ' TRL.INVENTORY_ITEM_ID, '||
884 ' TRL.ORGANIZATION_ID, '||
885 ' TRL.REVISION, '||
886 ' TRL.QUANTITY QTY, '||
887 ' TRL.PRIMARY_QUANTITY PRM_QTY, '||
888 ' TRL.QUANTITY_DELIVERED DLVD_QTY, '||
889 ' TRL.QUANTITY_DETAILED DTLD_QTY, '||
890 ' TRL.MOVE_ORDER_TYPE_NAME MOVE_TYPE_NAME, '||
891 ' decode(TRL.TRANSACTION_SOURCE_TYPE_ID,2,''Sales Order'', '||
892 ' 5,''Job or Schedule'', '||
893 ' 13,''Inventory'', '||
894 ' TRL.TRANSACTION_SOURCE_TYPE_ID) txn_source_meaning, '||
895 ' TRL.TRANSACTION_TYPE_NAME transaction_type_meaning, '||
896 ' decode(TRL.TRANSACTION_ACTION_ID, '||
897 ' 1, ''Issue'', '||
898 ' 2, ''Subinv Xfr'', '||
899 ' 3, ''Org Xfr'', '||
900 ' 4, ''Cycle Count Adj'', '||
901 ' 5, ''Plan Xfr'', '||
902 ' 21, ''Intransit Shpmt'', '||
903 ' 24, ''Cost Update'', '||
904 ' 27, ''Receipt'', '||
905 ' 28, ''Stg Xfr'', '||
906 ' 30, ''Wip scrap'', '||
907 ' 31, ''Assy Complete'', '||
908 ' 32, ''Assy return'', '||
909 ' 33, ''-ve CompIssue'', '||
910 ' 34, ''-ve CompReturn'', '||
911 ' 40, ''Inv Lot Split'', '||
912 ' 41, ''Inv Lot Merge'', '||
913 ' 42, ''Inv Lot Translate'', '||
914 ' 42, ''Inv Lot Translate'', '||
915 ' trl.transaction_action_id) txn_action_meaning, '||
916 ' TRL.FROM_SUBINVENTORY_CODE FROM_SUB, '||
917 ' TRL.FROM_LOCATOR_ID FROM_LOC_ID, '||
918 ' TRL.TO_SUBINVENTORY_CODE TO_SUB, '||
919 ' TRL.TO_LOCATOR_ID TO_LOC_ID, '||
920 ' TRL.LOT_NUMBER LOT_NUM, '||
921 ' TRL.TRANSACTION_HEADER_ID TRNS_HEAD_ID, '||
922 ' TRL.CREATION_DATE '||
923 'from MTL_TXN_REQUEST_LINES_V TRL '||
924 'WHERE trl.move_order_type <> 6 '||
925 'AND (trl.txn_source_id, trl.txn_source_line_id) in '||
926 ' (select wdj.wip_entity_id, wro.operation_seq_num '||
927 ' from wip_discrete_jobs wdj, '||
928 ' wip_entities we, '||
929 ' wip_lines wl, '||
930 ' wip_requirement_operations wro '||
931 ' where wdj.wip_entity_id = we.wip_entity_id '||
932 ' and wdj.organization_id = we.organization_id '||
933 ' and wdj.wip_entity_id = wro.wip_entity_id '||
934 ' and wdj.organization_id = wro.organization_id '||
935 ' and wdj.line_id = wl.line_id(+) '||
936 ' and wdj.organization_id = wl.organization_id(+) ';
937
938 if p_wip_entity_id is not null then
939 sqltxt :=sqltxt||' and we.wip_entity_id = '|| p_wip_entity_id ||')' ;
940 sqltxt :=sqltxt||' order by request_number ';
941 end if;
942
943 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
944 'MTL_TXN_REQUEST_LINES_V TRANSACTIONS - MOVE ORDERS',null,'Y',row_limit);
945
949
946 IF (dummy_num = row_limit) THEN
947 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
948 END IF;
950 sqltxt :=
951 'select transaction_id, '||
952 ' transaction_date, '||
953 ' transaction_type_id, '||
954 ' decode(transaction_action_id, '||
955 ' 1, ''Issue'', '||
956 ' 2, ''Subinv Xfr'', '||
957 ' 3, ''Org Xfr'', '||
958 ' 4, ''Cycle Count Adj'', '||
959 ' 5, ''Issue'', '||
960 ' 21, ''Intransit Shpmt'', '||
961 ' 24, ''Cost Update'', '||
962 ' 27, ''Receipt'', '||
963 ' 28, ''Stg Xfr'', '||
964 ' 30, ''Wip scrap'', '||
965 ' 31, ''Assy Complete'', '||
966 ' 32, ''Assy return'', '||
967 ' 33, ''-ve CompIssue'', '||
968 ' 34, ''-ve CompReturn'', '||
969 ' 40, ''Inv Lot Split'', '||
970 ' 41, ''Inv Lot Merge'', '||
971 ' 42, ''Inv Lot Translate'', '||
972 ' 42, ''Inv Lot Translate'', '||
973 ' transaction_action_id) txn_action_meaning, '||
974 ' decode(transaction_source_type_id, '||
975 ' 1, ''PO'', '||
976 ' 2, ''SO'', '||
977 ' 4, ''MoveOrder'', '||
978 ' 5, ''WIP'', '||
979 ' 6, ''AcctAlias'', '||
980 ' 7, ''Int REQ'', '||
981 ' 8, ''Int Order'', '||
982 ' 9, ''CycleCount'', '||
983 ' 10,''PhyCount'', '||
984 ' 11,''StdCostUpd'', '||
985 ' 12, ''RMA'', '||
986 ' 13, ''INV'', '||
987 ' 17, ''Ext REQ'', '||
988 ' transaction_source_type_id) txn_source_meaning, '||
989 ' transaction_source_id, '||
990 ' inventory_item_id, '||
991 ' organization_id, '||
992 ' subinventory_code, '||
993 ' locator_id, '||
994 ' revision, '||
995 ' transaction_quantity, '||
996 ' transaction_uom, '||
997 ' primary_quantity, '||
998 ' trx_source_line_id, '||
999 ' trx_source_delivery_id, '||
1000 ' move_transaction_id, '||
1001 ' completion_transaction_id, '||
1002 ' source_code, '||
1003 ' source_line_id, '||
1004 ' transfer_organization_id, '||
1005 ' transfer_subinventory, '||
1006 ' transfer_locator_id, '||
1007 ' move_order_line_id, '||
1008 ' reservation_id, '||
1009 ' creation_date, '||
1010 ' last_update_date, '||
1011 ' error_code '||
1012 'from mtl_material_transactions '||
1013 'where transaction_source_type_id = 5 '||
1014 'and exists (select 1 '||
1015 ' from wip_entities '||
1016 ' where wip_entity_id = transaction_source_id '||
1017 ' and entity_type <> 2 ) '; /* Other than repetitive schedule */
1018
1019 if p_wip_entity_id is not null then
1020 sqltxt :=sqltxt||' and transaction_source_id = '|| p_wip_entity_id ;
1021 sqltxt :=sqltxt||' order by transaction_id ';
1022 end if;
1023
1024 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1025 'MMT TRANSACTIONS',null,'Y',row_limit);
1026
1027 IF (dummy_num = row_limit) THEN
1028 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1029 END IF;
1030
1031 sqltxt :=
1032 'select '||
1033 ' inventory_item_id, '||
1034 ' lot_number, '||
1035 ' organization_id, '||
1036 ' transaction_id, '||
1037 ' transaction_date, '||
1038 ' creation_date, '||
1039 ' transaction_source_id, '||
1040 ' decode(transaction_source_type_id, '||
1041 ' 1, ''PO'', '||
1042 ' 2, ''SO'', '||
1043 ' 4, ''MoveOrder'', '||
1044 ' 5, ''WIP'', '||
1045 ' 6, ''AcctAlias'', '||
1046 ' 7, ''Int REQ'', '||
1047 ' 8, ''Int Order'', '||
1048 ' 9, ''CycleCount'', '||
1049 ' 10,''PhyCount'', '||
1050 ' 11,''StdCostUpd'', '||
1051 ' 12, ''RMA'', '||
1052 ' 13, ''INV'', '||
1053 ' 17, ''Ext REQ'', '||
1054 ' transaction_source_type_id) txn_source_meaning, '||
1055 ' transaction_quantity, '||
1056 ' primary_quantity, '||
1057 ' serial_transaction_id '||
1058 'from mtl_transaction_lot_numbers mtln '||
1059 'where mtln.transaction_id in '||
1060 ' (select mmt.transaction_id '||
1061 ' from mtl_material_transactions mmt '||
1062 ' where mmt.transaction_source_type_id = 5 ';
1063
1064 if p_wip_entity_id is not null then
1065 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ||')';
1066 sqltxt :=sqltxt||' order by inventory_item_id, lot_number ';
1067 end if;
1068
1069 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1070 'MTLN TRANSACTIONS',null,'Y',row_limit);
1071
1072 IF (dummy_num = row_limit) THEN
1073 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1074 END IF;
1075
1076 sqltxt :=
1077 'select '||
1078 ' inventory_item_id, '||
1079 ' serial_number, '||
1080 ' decode(current_status, '||
1081 ' 1, ''Defined but not used'', '||
1082 ' 3, ''Resides in stores'', '||
1083 ' 4, ''Issued out of stores'', '||
1084 ' 5, ''Resides in intrasit'', '||
1085 ' current_status) current_status_meaning, '||
1086 ' revision, '||
1087 ' lot_number, '||
1088 ' parent_item_id, '||
1089 ' last_transaction_id, '||
1090 ' parent_serial_number, '||
1091 ' end_item_unit_number, '||
1092 ' group_mark_id, '||
1093 ' line_mark_id, '||
1094 ' lot_line_mark_id, '||
1095 ' gen_object_id, '||
1096 ' creation_date '||
1097 'from mtl_serial_numbers msn ';
1098
1099 if p_wip_entity_id is not null then
1103
1100 sqltxt :=sqltxt||' where msn.wip_entity_id = '|| p_wip_entity_id ;
1101 sqltxt :=sqltxt||' order by inventory_item_id, serial_number ';
1102 end if;
1104 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1105 'MSN TRANSACTIONS for WIP Serial Tracking',null,'Y',row_limit);
1106
1107 IF (dummy_num = row_limit) THEN
1108 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1109 END IF;
1110
1111 sqltxt :=
1112 ' select mmt.inventory_item_id, ' ||
1113 ' mmt.transaction_id,' ||
1114 ' mmt.transaction_date,' ||
1115 ' mmt.transaction_source_id,' ||
1116 ' mut.serial_number,' ||
1117 ' mmt.subinventory_code,' ||
1118 ' mmt.locator_id , ' ||
1119 ' mmt.creation_date' ||
1120 ' from mtl_material_transactions mmt,' ||
1121 ' mtl_unit_transactions mut' ||
1122 ' where mmt.transaction_action_id in (1, 27, 33, 34, 30, 31, 32)' ||
1123 ' and mmt.transaction_source_type_id = 5' ||
1124 ' and mut.transaction_id = mmt.transaction_id' ;
1125
1126 if p_wip_entity_id is not null then
1127 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ;
1128 sqltxt :=sqltxt||' order by mmt.inventory_item_id, mut.serial_number ';
1129 end if ;
1130
1131
1132 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1133 'MSN TRANSACTIONS',null,'Y',row_limit);
1134
1135 IF (dummy_num = row_limit) THEN
1136 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1137 END IF;
1138
1139 sqltxt :=
1140 'select header_id, '||
1141 ' source_id, '||
1142 ' source_code, '||
1143 ' completion_status, '||
1144 ' creation_date, '||
1145 ' last_update_date, '||
1146 ' inventory_item_id, '||
1147 ' organization_id, '||
1148 ' primary_quantity, '||
1149 ' transaction_quantity, '||
1150 ' transaction_uom, '||
1151 ' transaction_date, '||
1152 ' transaction_action_id, '||
1153 ' transaction_source_id, '||
1154 ' transaction_source_type_id, '||
1155 ' transaction_type_id, '||
1156 ' transaction_mode, '||
1157 ' acct_period_id, '||
1158 ' subinventory_code, '||
1159 ' locator_id, '||
1160 ' schedule_id, '||
1161 ' repetitive_line_id, '||
1162 ' operation_seq_num, '||
1163 ' cost_group_id, '||
1164 ' lock_flag, '||
1165 ' error_code, '||
1166 ' final_completion_flag, '||
1167 ' completion_transaction_id '||
1168 'from wip_lpn_completions ';
1169
1170
1171 if p_wip_entity_id is not null then
1172 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ;
1173 sqltxt :=sqltxt||' order by header_id ';
1174 end if;
1175
1176 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1177 'WIP LPN COMPLETIONS',null,'Y',row_limit);
1178
1179 IF (dummy_num = row_limit) THEN
1180 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1181 END IF;
1182
1183 sqltxt :=
1184 'select '||
1185 ' RES.RESERVATION_ID RESERV_ID, '||
1186 ' decode(RES.SHIP_READY_FLAG,1,''1=Released'',2,''2=Submitted'',to_char(RES.SHIP_READY_FLAG)) '||
1187 ' SHIP_READY, '||
1188 ' RES.DEMAND_SOURCE_HEADER_ID DS_HEADER_ID, '||
1189 ' RES.DEMAND_SOURCE_LINE_ID DS_LINE_ID, '||
1190 ' RES.DEMAND_SOURCE_DELIVERY DS_DELIVERY, '||
1191 ' RES.INVENTORY_ITEM_ID ITEM_ID, '||
1192 ' RES.RESERVATION_QUANTITY RES_QTY, '||
1193 ' RES.RESERVATION_UOM_CODE RUOM, '||
1194 ' RES.PRIMARY_RESERVATION_QUANTITY PRES_QTY, '||
1195 ' RES.PRIMARY_UOM_CODE PUOM, '||
1196 ' RES.DETAILED_QUANTITY DET_QTY, '||
1197 ' RES.REQUIREMENT_DATE REQUIRD_DATE, '||
1198 ' RES.DEMAND_SOURCE_TYPE_ID DS_TYPE, '||
1199 ' RES.ORGANIZATION_ID ORG_ID, '||
1200 ' RES.SUBINVENTORY_CODE SUBINV, '||
1201 ' RES.LOT_NUMBER LOT, '||
1202 ' RES.REVISION REV, '||
1203 ' RES.LOCATOR_ID LOC_ID, '||
1204 ' RES.SERIAL_NUMBER SERIAL_NUM, '||
1205 ' decode(RES.SUPPLY_SOURCE_TYPE_ID,1,''1=PO'', '||
1206 ' 2,''2=OE'', '||
1207 ' 5,''5=WIP DJ'', '||
1208 ' 7,''7=INT_REQ'', '||
1209 ' 8,''8=INT_OE'', '||
1210 ' 13,''13=INV'', '||
1211 ' 17,''17=REQ'', '||
1212 ' RES.SUPPLY_SOURCE_TYPE_ID) '||
1213 ' SS_TYPE_ID, '||
1214 ' We.WIP_ENTITY_ID WIP_ID, '||
1215 ' decode(JOB.STATUS_TYPE, 1, ''Unreleased'', '||
1216 ' 2, ''Simulated'', '||
1217 ' 3, ''Released'', '||
1218 ' 4, ''Complete'', '||
1219 ' 5, ''Complete-NoCharges'', '||
1220 ' 6, ''OnHold'', '||
1221 ' 7, ''Canceled'', '||
1222 ' 8, ''Pending Bill Load'', '||
1223 ' 9, ''Failed Bill Load'', '||
1227 ' 13, ''Pending-Mass Load'', '||
1224 ' 10, ''Pending Routing Load'', '||
1225 ' 11, ''Failed Routing Load'', '||
1226 ' 12, ''Closed'', '||
1228 ' 14, ''Pending Close'', '||
1229 ' 15, ''Failed Close'', '||
1230 ' 16, ''Pending Scheduling'', '||
1231 ' 17, ''Draft'', '||
1232 ' JOB.STATUS_TYPE ) JOB_STATUS, '||
1233 ' RES.SUPPLY_SOURCE_HEADER_ID SS_HEADER_ID, '||
1234 ' RES.SUPPLY_SOURCE_LINE_DETAIL SS_SOURCE_LINE_DET, '||
1235 ' RES.SUPPLY_SOURCE_LINE_ID SS_SOURCE_LINE, '||
1236 ' RES.PARTIAL_QUANTITIES_ALLOWED ALLOW_PART, '||
1237 ' to_char(RES.CREATION_DATE, ''DD-MON HH24:MI:SS'') CREATE_DATE, '||
1238 ' to_char(RES.LAST_UPDATE_DATE, ''DD-MON HH24:MI:SS'') UPD_DATE '||
1239 'from '||
1240 ' MTL_RESERVATIONS RES, '||
1241 ' WIP_ENTITIES WE, '||
1242 ' WIP_DISCRETE_JOBS JOB '||
1243 'where RES.SUPPLY_SOURCE_HEADER_ID = WE.WIP_ENTITY_ID '||
1244 'and WE.WIP_ENTITY_ID = JOB.WIP_ENTITY_ID ';
1245
1246 if p_wip_entity_id is not null then
1247 sqltxt :=sqltxt||' and we.wip_entity_id = '|| p_wip_entity_id ;
1248 sqltxt :=sqltxt||' order by reservation_id ';
1249 end if;
1250
1251 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1252 'MTL RESERVATIONS',null,'Y',row_limit);
1253
1254 IF (dummy_num = row_limit) THEN
1255 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1256 END IF;
1257
1258 sqltxt :=
1259 'Select '||
1260 'RQI.INTERFACE_SOURCE_LINE_ID wip_entity_id, '||
1261 'RQI.INTERFACE_SOURCE_CODE SRC_CODE, '||
1262 'RQI.AUTHORIZATION_STATUS AUTH_STATUS, '||
1263 'RQI.DELIVER_TO_LOCATION_ID DELIV_LOC, '||
1264 'RQI.PREPARER_ID PREPARER, '||
1265 'RQI.DESTINATION_ORGANIZATION_ID DEST_ORG_ID, '||
1266 'RQI.DESTINATION_TYPE_CODE DEST_TYPE, '||
1267 'RQI.SOURCE_TYPE_CODE SRC_TYPE_CODE, '||
1268 'RQI.ITEM_ID ITEM_ID, '||
1269 'RQI.NEED_BY_DATE NEED_BY, '||
1270 'RQI.QUANTITY QTY, '||
1271 'RQI.UNIT_PRICE PRICE '||
1272 'from '||
1273 ' PO_REQUISITIONS_INTERFACE_ALL RQI '||
1274 'where RQI.INTERFACE_SOURCE_CODE =''WIP'' ';
1275
1276
1277 if p_wip_entity_id is not null then
1278 sqltxt :=sqltxt||' and rqi.interface_source_line_id = '|| p_wip_entity_id ;
1279 end if;
1280
1281 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1282 'PO REQUISITION INTERFACE',null,'Y',row_limit);
1283
1284 IF (dummy_num = row_limit) THEN
1285 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1286 END IF;
1287
1288 sqltxt :=
1289 'select '||
1290 ' POE.INTERFACE_TRANSACTION_ID INTF_TRANS_ID, '||
1291 ' POE.COLUMN_NAME COLUMN_NAME, '||
1292 ' POE.ERROR_MESSAGE ERROR, '||
1293 ' POE.INTERFACE_TYPE INTF_TYPE, '||
1294 ' POE.REQUEST_ID REQUEST_ID, '||
1295 ' POE.TABLE_NAME TABLE_NAME '||
1296 'from '||
1297 ' PO_INTERFACE_ERRORS POE, '||
1298 ' PO_REQUISITIONS_INTERFACE_ALL RQI '||
1299 'where RQI.TRANSACTION_ID = POE.INTERFACE_TRANSACTION_ID ';
1300
1301
1302 if p_wip_entity_id is not null then
1303 sqltxt :=sqltxt||' and rqi.interface_source_line_id = '|| p_wip_entity_id ;
1304 end if;
1305
1306 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1307 'PO INTERFACE ERRORS',null,'Y',row_limit);
1308
1309 IF (dummy_num = row_limit) THEN
1310 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1311 END IF;
1312
1313 sqltxt :=
1314 'select '||
1315 ' RQH.REQUISITION_HEADER_ID REQ_HEADER_ID , '||
1316 ' RQH.SEGMENT1 REQ_NUMBER, '||
1317 ' RQL.REQUISITION_LINE_ID REQ_LINE_ID, '||
1318 ' RQL.LINE_NUM REQ_LINE, '||
1319 ' RQH.INTERFACE_SOURCE_LINE_ID INT_SRC_LINE_ID, '||
1320 ' RQL.WIP_ENTITY_ID WIP_ENTITY_ID, '||
1321 ' RQH.AUTHORIZATION_STATUS AUTH_STATUS, '||
1322 ' RQH.ENABLED_FLAG ENABLED, '||
1323 ' RQH.INTERFACE_SOURCE_CODE SRC_CODE, '||
1324 ' RQH.SUMMARY_FLAG SUMMARY, '||
1325 ' RQH.TRANSFERRED_TO_OE_FLAG XFR_OE_FLAG, '||
1326 ' RQH.TYPE_LOOKUP_CODE REQ_TYPE, '||
1327 ' RQH.WF_ITEM_TYPE ITEM_TYPE, '||
1328 ' RQH.WF_ITEM_KEY ITEM_KEY, '||
1329 ' RQL.ITEM_ID ITEM_ID, '||
1330 ' RQL.UNIT_MEAS_LOOKUP_CODE UOM, '||
1331 ' RQL.UNIT_PRICE PRICE, '||
1332 ' RQL.QUANTITY QTY, '||
1333 ' RQL.QUANTITY_CANCELLED QTY_CNC, '||
1334 ' RQL.QUANTITY_DELIVERED QTY_DLV, '||
1338 ' RQL.ENCUMBERED_FLAG ENC_FLAG , '||
1335 ' RQL.CANCEL_FLAG CANC, '||
1336 ' RQL.DESTINATION_CONTEXT DEST_TYPE, '||
1337 ' RQL.DESTINATION_ORGANIZATION_ID DEST_ORG, '||
1339 ' RQL.LINE_TYPE_ID LINE_TYPE_ID, '||
1340 ' RQL.NEED_BY_DATE NEED_BY, '||
1341 ' RQL.ON_RFQ_FLAG RFQ , '||
1342 ' RQL.SOURCE_TYPE_CODE SRC_TYPE_CODE, '||
1343 ' RQL.SUGGESTED_BUYER_ID BUYER_ID '||
1344 'from '||
1345 ' PO_REQUISITION_HEADERS_ALL RQH, '||
1346 ' PO_REQUISITION_LINES_ALL RQL '||
1347 'where '||
1348 ' RQH.REQUISITION_HEADER_ID = RQL.REQUISITION_HEADER_ID ';
1349
1350
1351 if p_wip_entity_id is not null then
1352 sqltxt :=sqltxt||' and rql.wip_entity_id = '|| p_wip_entity_id ;
1353 sqltxt :=sqltxt||' order by RQH.REQUISITION_HEADER_ID, RQL.REQUISITION_LINE_ID, RQL.ITEM_ID';
1354 end if;
1355
1356 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1357 'PO REQUISITION DETAILS',null,'Y',row_limit);
1358
1359 IF (dummy_num = row_limit) THEN
1360 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1361 END IF;
1362
1363 sqltxt :=
1364 'select '||
1365 ' WFS.item_key REQ_NUM_IK, '||
1366 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
1367 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
1368 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
1369 ' LKP.MEANING ACT_STATUS, '||
1370 ' WFS.NOTIFICATION_ID NOTIF_ID, '||
1371 ' WFS.BEGIN_DATE, '||
1372 ' WFS.END_DATE, '||
1373 ' WFS.ERROR_NAME ERROR '||
1374 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
1375 ' WF_PROCESS_ACTIVITIES WFP, '||
1376 ' WF_ACTIVITIES_VL WFA, '||
1377 ' WF_ACTIVITIES_VL WFA1, '||
1378 ' WF_LOOKUPS LKP '||
1379 'where '||
1380 ' WFS.ITEM_TYPE = ''REQAPPRV'' '||
1381 'and WFS.item_key in (select wf_item_key '||
1382 ' from '||
1383 ' PO_REQUISITION_HEADERS_ALL RQH, '||
1384 ' PO_REQUISITION_LINES_ALL RQL '||
1385 ' where '||
1386 ' RQH.REQUISITION_HEADER_ID = RQL.REQUISITION_HEADER_ID ';
1387
1388 if p_wip_entity_id is not null then
1389 sqltxt :=sqltxt||' and rql.wip_entity_id = '|| p_wip_entity_id ||') ';
1390 end if;
1391
1392 sqltxt :=sqltxt||
1393 ' and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
1394 ' and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
1395 ' and WFP.PROCESS_NAME = WFA.NAME '||
1396 ' and WFP.PROCESS_VERSION = WFA.VERSION '||
1397 ' and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
1398 ' and WFP.ACTIVITY_NAME = WFA1.NAME '||
1399 ' and WFA1.VERSION = '||
1400 ' (select max(VERSION) '||
1401 ' from WF_ACTIVITIES WF2 '||
1402 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
1403 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
1404 ' and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
1405 ' and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS ';
1406
1407 sqltxt :=sqltxt||' order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME';
1408
1409 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1410 'WORKFLOW REQUISITION APPROVAL STATUS',null,'Y',row_limit);
1411
1412 IF (dummy_num = row_limit) THEN
1413 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1414 END IF;
1415
1416 sqltxt :=
1417 'select '||
1418 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
1419 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
1420 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
1421 ' LKP.MEANING ACT_STATUS, '||
1422 ' WFS.ERROR_NAME ERROR_NAME, '||
1423 ' WFS.ERROR_MESSAGE ERROR_MESSAGE, '||
1424 ' WFS.ERROR_STACK ERROR_STACK '||
1425 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
1426 ' WF_PROCESS_ACTIVITIES WFP, '||
1427 ' WF_ACTIVITIES_VL WFA, '||
1428 ' WF_ACTIVITIES_VL WFA1, '||
1429 ' WF_LOOKUPS LKP '||
1430 'where '||
1431 ' WFS.ITEM_TYPE = ''REQAPPRV'' '||
1432 'and WFS.item_key in (select wf_item_key '||
1433 ' from '||
1434 ' PO_REQUISITION_HEADERS_ALL RQH, '||
1435 ' PO_REQUISITION_LINES_ALL RQL '||
1436 ' where '||
1437 ' RQH.REQUISITION_HEADER_ID = RQL.REQUISITION_HEADER_ID ';
1438
1439 if p_wip_entity_id is not null then
1440 sqltxt :=sqltxt||' and rql.wip_entity_id = '|| p_wip_entity_id ||') ';
1441 end if;
1442
1443 sqltxt := sqltxt ||
1444 ' and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
1445 ' and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
1446 ' and WFP.PROCESS_NAME = WFA.NAME '||
1447 ' and WFP.PROCESS_VERSION = WFA.VERSION '||
1448 ' and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
1449 ' and WFP.ACTIVITY_NAME = WFA1.NAME '||
1450 ' and WFA1.VERSION = '||
1451 ' (select max(VERSION) '||
1452 ' from WF_ACTIVITIES WF2 '||
1456 ' and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS '||
1453 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
1454 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
1455 ' and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
1457 ' and WFS.ERROR_NAME is not NULL '||
1458 ' order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME ';
1459
1460 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1461 'WORKFLOW REQUISITION APPROVAL ERRORS',null,'Y',row_limit);
1462
1463 IF (dummy_num = row_limit) THEN
1464 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1465 END IF;
1466
1467 sqltxt :=
1468 'select '||
1469 ' POH.PO_HEADER_ID PO_HEADER_ID, '||
1470 ' POH.SEGMENT1 PO_NUM, '||
1471 ' POL.PO_LINE_ID PO_LINE_ID, '||
1472 ' POL.LINE_NUM PO_LINE, '||
1473 ' POL.LINE_TYPE_ID LINE_TYPE_ID, '||
1474 ' POL.ITEM_ID ITEM_ID, '||
1475 ' POL.QUANTITY QTY, '||
1476 ' POL.UNIT_PRICE PRICE, '||
1477 ' POH.ACCEPTANCE_REQUIRED_FLAG ACCEPT_REQD, '||
1478 ' POH.BILL_TO_LOCATION_ID BILL_TO, '||
1479 ' POH.SHIP_TO_LOCATION_ID SHIP_TO, '||
1480 ' POH.CLOSED_CODE CLS_STAT, '||
1481 ' POH.CONFIRMING_ORDER_FLAG CONF_ORD, '||
1482 ' POH.CURRENCY_CODE CURR, '||
1483 ' POH.ENABLED_FLAG ENABLED, '||
1484 ' POH.FROZEN_FLAG FROZEN, '||
1485 ' POH.SUMMARY_FLAG SUMM, '||
1486 ' POH.TYPE_LOOKUP_CODE TYPE, '||
1487 ' POH.VENDOR_CONTACT_ID VEND_CNCACT, '||
1488 ' POH.VENDOR_ID VEND_ID, '||
1489 ' POH.VENDOR_SITE_ID VEND_SITE, '||
1490 ' POH.WF_ITEM_TYPE ITEM_TYPE, '||
1491 ' POH.WF_ITEM_KEY ITEM_KEY , '||
1492 ' POL.CATEGORY_ID CATEGORY_ID, '||
1493 ' POL.CLOSED_CODE CLS_STAT, '||
1494 ' POL.FIRM_STATUS_LOOKUP_CODE FIRM '||
1495 'from '||
1496 ' PO_HEADERS_ALL POH, '||
1497 ' PO_LINES_ALL POL, '||
1498 ' PO_LINE_LOCATIONS_ALL LL, '||
1499 ' PO_REQUISITION_LINES_ALL PRL, '||
1500 ' PO_REQUISITION_HEADERS_ALL PRH '||
1501 'where PRH.requisition_header_id = PRL.requisition_header_id '||
1502 'and PRL.line_location_id = LL.line_location_id '||
1503 'and LL.PO_HEADER_ID = POH.PO_HEADER_ID '||
1504 'and POL.PO_HEADER_ID = POH.PO_HEADER_ID ';
1505
1506 if p_wip_entity_id is not null then
1507 sqltxt :=sqltxt||' and prl.wip_entity_id = '|| p_wip_entity_id ;
1508 sqltxt :=sqltxt||' order by poh.po_header_id, pol.po_line_id ';
1509 end if;
1510
1511 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1512 'PO DETAILS',null,'Y',row_limit);
1513
1514 IF (dummy_num = row_limit) THEN
1515 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1516 END IF;
1517
1518 sqltxt :=
1519 'select '||
1520 ' WFS.item_key PO_NUM_IK, '||
1521 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
1522 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
1523 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
1524 ' LKP.MEANING ACT_STATUS, '||
1525 ' WFS.NOTIFICATION_ID NOTIF_ID, '||
1526 ' WFS.BEGIN_DATE, '||
1527 ' WFS.END_DATE, '||
1528 ' WFS.ERROR_NAME ERROR '||
1529 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
1530 ' WF_PROCESS_ACTIVITIES WFP, '||
1531 ' WF_ACTIVITIES_VL WFA, '||
1532 ' WF_ACTIVITIES_VL WFA1, '||
1533 ' WF_LOOKUPS LKP '||
1534 'where '||
1535 ' WFS.ITEM_TYPE = ''POAPPRV'' '||
1536 'and WFS.item_key in (select poh.wf_item_key '||
1537 ' from '||
1538 ' PO_HEADERS_ALL POH, '||
1539 ' PO_LINES_ALL POL, '||
1540 ' PO_LINE_LOCATIONS_ALL LL, '||
1541 ' PO_REQUISITION_LINES_ALL PRL, '||
1542 ' PO_REQUISITION_HEADERS_ALL PRH '||
1543 ' where PRH.requisition_header_id = PRL.requisition_header_id ';
1544 if p_wip_entity_id is not null then
1545 sqltxt :=sqltxt||' and prl.wip_entity_id = '|| p_wip_entity_id ;
1546 end if;
1547
1548 sqltxt := sqltxt ||
1549 ' and PRL.line_location_id = LL.line_location_id '||
1550 ' and LL.PO_HEADER_ID = POH.PO_HEADER_ID '||
1551 ' and POL.PO_HEADER_ID = POH.PO_HEADER_ID) '||
1552 'and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
1553 'and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
1554 'and WFP.PROCESS_NAME = WFA.NAME '||
1555 'and WFP.PROCESS_VERSION = WFA.VERSION '||
1556 'and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
1557 'and WFP.ACTIVITY_NAME = WFA1.NAME '||
1558 'and WFA1.VERSION = '||
1559 ' (select max(VERSION) '||
1560 ' from WF_ACTIVITIES WF2 '||
1561 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
1562 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
1563 'and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
1564 'and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS '||
1565 'order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME ';
1566
1567 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1568 'WORKFLOW PURCHASE ORDER APPROVAL STATUS',null,'Y',row_limit);
1572 END IF;
1569
1570 IF (dummy_num = row_limit) THEN
1571 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1573
1574 sqltxt :=
1575 'select '||
1576 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
1577 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
1578 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
1579 ' LKP.MEANING ACT_STATUS, '||
1580 ' WFS.ERROR_NAME ERROR_NAME, '||
1581 ' WFS.ERROR_MESSAGE ERROR_MESSAGE, '||
1582 ' WFS.ERROR_STACK ERROR_STACK '||
1583 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
1584 ' WF_PROCESS_ACTIVITIES WFP, '||
1585 ' WF_ACTIVITIES_VL WFA, '||
1586 ' WF_ACTIVITIES_VL WFA1, '||
1587 ' WF_LOOKUPS LKP '||
1588 'where '||
1589 ' WFS.ITEM_TYPE = ''POAPPRV'' '||
1590 'and WFS.item_key in (select poh.wf_item_key '||
1591 ' from '||
1592 ' PO_HEADERS_ALL POH, '||
1593 ' PO_LINES_ALL POL, '||
1594 ' PO_LINE_LOCATIONS_ALL LL, '||
1595 ' PO_REQUISITION_LINES_ALL PRL, '||
1596 ' PO_REQUISITION_HEADERS_ALL PRH '||
1597 ' where PRH.requisition_header_id = PRL.requisition_header_id ';
1598
1599 if p_wip_entity_id is not null then
1600 sqltxt :=sqltxt||' and prl.wip_entity_id = '|| p_wip_entity_id ;
1601 end if;
1602
1603 sqltxt := sqltxt ||
1604 ' and PRL.line_location_id = LL.line_location_id '||
1605 ' and LL.PO_HEADER_ID = POH.PO_HEADER_ID '||
1606 ' and POL.PO_HEADER_ID = POH.PO_HEADER_ID) '||
1607 'and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
1608 'and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
1609 'and WFP.PROCESS_NAME = WFA.NAME '||
1610 'and WFP.PROCESS_VERSION = WFA.VERSION '||
1611 'and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
1612 'and WFP.ACTIVITY_NAME = WFA1.NAME '||
1613 'and WFA1.VERSION = '||
1614 ' (select max(VERSION) '||
1615 ' from WF_ACTIVITIES WF2 '||
1616 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
1617 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
1618 'and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
1619 'and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS '||
1620 'and WFS.ERROR_NAME is not NULL '||
1621 'order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME ';
1622
1623
1624 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1625 'WORKFLOW PURCHASE APPROVAL ERRORS',null,'Y',row_limit);
1626
1627 IF (dummy_num = row_limit) THEN
1628 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1629 END IF;
1630
1631 sqltxt :=
1632 'select default_discrete_class, '||
1633 ' decode(lot_number_default_type,1,''Job Name'', '||
1634 ' 2,''Based On Inventory Rules'', '||
1635 ' 3,''No Default'', '||
1636 ' lot_number_default_type) lot_number_default_type, '||
1637 ' decode(so_change_response_type,1,''Never'', '||
1638 ' 2,''Always'', '||
1639 ' 3,''When linked 1-1Default'') so_change_response_type, '||
1640 ' decode(mandatory_scrap_flag,1,''Yes'',2,''No'') Mandatory_Scrap_Flag, '||
1641 ' decode(dynamic_operation_insert_flag,1,''Yes'',2,''No'') Dynamic_Oprn_Insert_Flag, '||
1642 ' decode(moves_over_no_move_statuses,1,''Yes'',2,''No'') Moves_Over_No_Move_Status, '||
1643 ' default_pull_supply_subinv, '||
1644 ' default_pull_supply_locator_id, '||
1645 ' decode(backflush_lot_entry_type,1, ''Manual, verify all'', '||
1646 ' 2, ''Receipt Date, Verify all'', '||
1647 ' 3, ''Receipt Date, Verify excepns'', '||
1648 ' 4, ''Expiration Date, verify all'', '||
1649 ' 5, ''Expiration Date, verify excepns'', '||
1650 ' 6, ''Transaction History'', '||
1651 ' backflush_lot_entry_type) Lot_Selection_Method , ' ;
1652
1653 if (release_level = '11.5.10.2' ) then
1654 sqltxt := sqltxt ||
1655 ' decode(alternate_lot_selection_method,1, ''Manual'', '||
1656 ' 2, ''Receipt Date'', '||
1657 ' 4, ''Expiration Date'' , '||
1658 ' alternate_lot_selection_method) Alternate_Lot_Selection_Method, ' ;
1659 end if ;
1660 sqltxt := sqltxt ||
1661
1662 ' decode(allocate_backflush_components,''1'',''Yes'',''2'',''No'') Allocate_Backflush_Comps, '||
1663 ' decode(allow_backflush_qty_change,1,''Yes'',2,''No'') Allow_Backflush_Qty_Change, '||
1664 ' autorelease_days, '||
1665 ' osp_shop_floor_status, '||
1666 ' decode(po_creation_time, 1, ''At Job/Schedule Release'', '||
1667 ' 2, ''At Operation'', '||
1668 ' 3, ''Manual'', '||
1669 ' po_creation_time) PO_Creation_Time, '||
1670 ' default_overcompl_tolerance, '||
1671 ' production_scheduler_id, '||
1672 ' decode(material_constrained,1,''Yes'',2,''No'') Material_Constrained, '||
1673 ' decode(use_finite_scheduler,1,''Yes'',2,''No'') Use_Finite_Scheduler,'||
1674 ' repetitive_variance_type '||
1675 'from wip_parameters '||
1676 'where organization_id = (select organization_id '||
1677 ' from wip_entities ';
1678
1679
1680 if p_wip_entity_id is not null then
1684 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1681 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ||')';
1682 end if;
1683
1685 'WIP PARAMETERS',null,'Y',row_limit);
1686
1687 IF (dummy_num = row_limit) THEN
1688 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1689 END IF;
1690
1691 sqltxt :=
1692 'select a.assembly_item_id, '||
1693 ' substrb(msi.concatenated_segments, 1,30) item_name, '||
1694 -- ' a.organization_id, '||
1695 ' nvl(a.alternate_bom_designator, ''PRIMARY'') alternate_bom_designator, '||
1696 ' a.common_assembly_item_id, '||
1697 ' decode( a.assembly_type,1,''Manufacturing'', '||
1698 ' 2,''Engineering'', '||
1699 ' a.assembly_type) assembly_type, '||
1700 ' a.bill_sequence_id, '||
1701 ' a.common_bill_sequence_id, '||
1702 ' b.operation_seq_num, '||
1703 ' b.component_item_id, '||
1704 ' substrb(msi_comp.concatenated_segments, 1,30) comp_item_name, '||
1705 ' b.component_quantity, '||
1706 ' b.component_yield_factor, '||
1707 ' b.effectivity_date, '||
1708 ' b.implementation_date, '||
1709 ' b.disable_date, '||
1710 ' decode(b.wip_supply_type,1,''Push'', '||
1711 ' 2,''Assembly Pull'', '||
1712 ' 3,''Operation Pull'', '||
1713 ' 4,''Bulk'', '||
1714 ' 5,''Supplier'', '||
1715 ' 6,''Phantom'', '||
1716 ' 7,''Based on Bill'', '||
1717 ' b.wip_supply_type) wip_supply_type, '||
1718 ' b.supply_subinventory, '||
1719 ' b.supply_locator_id, '||
1720 ' b.component_sequence_id '||
1721 'from bom_bill_of_materials a, bom_inventory_components b, '||
1722 ' wip_discrete_jobs wj, mtl_system_items_kfv msi, mtl_system_items_kfv msi_comp '||
1723 'where a.common_bill_sequence_id = b.bill_sequence_id '||
1724 'and a.organization_id = wj.organization_id '||
1725 'and a.assembly_item_id = wj.primary_item_id '||
1726 'and wj.common_bom_sequence_id = a.bill_sequence_id '||
1727 'and msi.inventory_item_id = a.assembly_item_id '||
1728 'and msi.organization_id = a.organization_id ' ||
1729 'and msi_comp.organization_id = a.organization_id ' ||
1730 'and msi_comp.inventory_item_id = b.component_item_id ';
1731
1732 if p_wip_entity_id is not null then
1733 sqltxt :=sqltxt||' and wj.wip_entity_id = '|| p_wip_entity_id ;
1734 sqltxt :=sqltxt||' order by a.bill_sequence_id, a.assembly_item_id, a.alternate_bom_designator, b.component_sequence_id ';
1735 end if;
1736
1737 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1738 'BILL OF MATERIAL',null,'Y',row_limit);
1739
1740 IF (dummy_num = row_limit) THEN
1741 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1742 END IF;
1743
1744 sqltxt :=
1745 'select bor.assembly_item_id, '||
1746 ' substrb(msi.concatenated_segments, 1,30) item_name, '||
1747 ' bor.organization_id, '||
1748 ' nvl(bor.alternate_routing_designator, ''PRIMARY'') alternate_routing_designator, '||
1749 ' bor.routing_sequence_id, '||
1750 ' bor.common_routing_sequence_id, '||
1751 ' bor.common_assembly_item_id, '||
1752 ' bor.completion_subinventory, '||
1753 ' bor.completion_locator_id, '||
1754 ' decode(nvl(bor.cfm_routing_flag,2), '||
1755 ' 1, ''Flow'', '||
1756 ' 2, ''Discrete'', '||
1757 ' 3, ''Network'', '||
1758 ' bor.cfm_routing_flag) cfm_routing_flag, '||
1759 ' decode (bor.routing_type, '||
1760 ' 1, ''Mfg Rtg'', '||
1761 ' 2, ''Engg Rtg'', '||
1762 ' bor.routing_type) routing_type, '||
1763 ' a.operation_sequence_id, '||
1764 ' a.operation_seq_num, '||
1765 ' a.routing_sequence_id, '||
1766 ' a.standard_operation_id, '||
1767 ' b.operation_code, '||
1768 ' a.department_id, '||
1769 ' a.count_point_type, '||
1770 ' a.effectivity_date, '||
1771 ' a.disable_date, '||
1772 ' decode( a.backflush_flag, 1, ''Yes'', '||
1773 ' 2, ''No'') backflush_flag, '||
1774 ' decode( a.option_dependent_flag, 1, ''Yes'', '||
1775 ' 2, ''No'') option_dependent_flag, '||
1776 ' a.yield, '||
1777 ' decode(a.operation_yield_enabled, 1, ''Yes'', '||
1778 ' 2, ''No'', '||
1779 ' a.operation_yield_enabled) operation_yield_enabled '||
1780 'from bom_operation_sequences a, bom_operational_routings bor, wip_discrete_jobs wj, '||
1781 ' bom_standard_operations b , mtl_system_items_kfv msi '||
1782 'where a.routing_sequence_id = bor.common_routing_sequence_id '||
1783 'and wj.organization_id = bor.organization_id '||
1784 'and wj.common_routing_sequence_id = bor.routing_sequence_id '||
1785 'and a.standard_operation_id = b.standard_operation_id(+) ' ||
1786 'and bor.assembly_item_id = msi.inventory_item_id '||
1787 'and bor.organization_id = msi.organization_id ';
1788
1789
1793 end if;
1790 if p_wip_entity_id is not null then
1791 sqltxt :=sqltxt||' and wj.wip_entity_id = '|| p_wip_entity_id ;
1792 sqltxt :=sqltxt||' order by bor.routing_sequence_id, bor.alternate_routing_designator, a.operation_seq_num';
1794
1795 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1796 'ROUTING',null,'Y',row_limit);
1797
1798 IF (dummy_num = row_limit) THEN
1799 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1800 END IF;
1801
1802 sqltxt :=
1803 'SELECT msik.inventory_item_id, '||
1804 ' substr(msik.concatenated_segments, 1, 30) Item, '||
1805 ' msik.outside_operation_flag, '||
1806 ' msik.outside_operation_uom_type, '||
1807 ' wor.operation_seq_num, '||
1808 ' wor.resource_seq_num, '||
1809 ' wor.resource_id, '||
1810 ' br.resource_code, '||
1811 ' decode(wor.autocharge_type , 3, ''PO Move'', 4, ''PO Receipt'') AutoCharge_Type '||
1812 ' FROM mtl_system_items_kfv msik, '||
1813 ' bom_resources br, '||
1814 ' wip_operation_resources wor '||
1815 ' WHERE msik.inventory_item_id = br.purchase_item_id '||
1816 ' AND msik.organization_id = br.organization_id '||
1817 ' AND wor.resource_id = br.resource_id '||
1818 ' AND wor.autocharge_type IN (3,4) '||
1819 ' AND wor.organization_id = br.organization_id ';
1820
1821 if p_wip_entity_id is not null then
1822 sqltxt :=sqltxt||' and wor.wip_entity_id = '|| p_wip_entity_id ;
1823 end if;
1824
1825 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1826 'OSP ITEM DETAILS',null,'Y',row_limit);
1827
1828 IF (dummy_num = row_limit) THEN
1829 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1830 END IF;
1831
1832 sqltxt :=
1833 'select secondary_inventory_name, '||
1834 ' organization_id, '||
1835 ' decode(reservable_type, '||
1836 ' 1, ''Yes'', '||
1837 ' 2, ''No'', '||
1838 ' reservable_type) reserv_type_mng, '||
1839 ' disable_date, '||
1840 ' decode(inventory_atp_code, '||
1841 ' 1, ''Incl in ATP calc'', '||
1842 ' 2, ''Not Incl in ATP calc'', '||
1843 ' inventory_atp_code) inv_atp_code_mng, '||
1844 ' decode(locator_type, '||
1845 ' 1, ''No loc control'', '||
1846 ' 2, ''Prespecified'', '||
1847 ' 3, ''Dynamic'', '||
1848 ' 4, ''Determined at subinv'', '||
1849 ' 5, ''Determined at item'', '||
1850 ' locator_type) locator_type_mng, '||
1851 ' picking_order, '||
1852 ' source_subinventory '||
1853 'from mtl_secondary_inventories '||
1854 'where organization_id = (select organization_id '||
1855 ' from wip_entities ';
1856
1857
1858 if p_wip_entity_id is not null then
1859 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ||')';
1860 sqltxt :=sqltxt||' order by 1';
1861 end if;
1862
1863 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1864 'SUBINVENTORY SETUP',null,'Y',row_limit);
1865
1866 IF (dummy_num = row_limit) THEN
1867 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1868 END IF;
1869
1870 -- Run following diagnostics only for Lot Based jobs
1871 begin
1872 select 1
1873 into l_dummy
1874 from wip_entities
1875 where wip_entity_id = p_wip_entity_id
1876 and entity_type = 5 ; -- LotBased
1877
1878 sqltxt := ' select ' ||
1879 ' new_lot_separator ' ||
1880 ' , job_completion_separator ' ||
1881 ' , allow_backward_move_flag ' ||
1882 ' , delete_backward_from_flag ' ||
1883 ' , transaction_account_id ' ||
1884 ' , plan_code ' ||
1885 ' , op_seq_num_increment ' ||
1886 ' , coproducts_supply_default ' ||
1887 ' , default_acct_class_code ' ||
1888 ' , estimated_scrap_accounting ' ||
1889 ' , inv_lot_txn_enabled ' ||
1890 ' , honor_kanban_size ' ||
1891 ' , charge_jump_from_queue ' ||
1892 ' from wsm_parameters wp, wip_entities we ' ||
1893 ' where wp.organization_id = we.organization_id' ||
1894 ' and we.wip_entity_id = ' || p_wip_entity_id ;
1895
1896 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1897 'OSFM PARAMETER SETUP',null,'Y',row_limit);
1898
1899 IF (dummy_num = row_limit) THEN
1900 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1901 END IF;
1902
1903 sqltxt :=
1904 ' select a.from_seq_num,' ||
1905 ' a.from_operation_code,' ||
1906 ' a.from_op_seq_id,' ||
1907 ' a.to_seq_num,' ||
1908 ' a.to_operation_code,' ||
1909 ' a.to_op_seq_id,' ||
1910 ' decode(a.transition_type,1, ''PRIMARY'',' ||
1911 ' ''ALTERNATE'') transition_type' ||
1912 ' from bom_operation_networks_v a, wip_discrete_jobs wj' ||
1913 ' where wj.wip_entity_id = ' || p_wip_entity_id ||
1914 ' and wj.common_routing_sequence_id = a.routing_sequence_id' ||
1915 ' order by a.transition_type, a.row_id' ;
1916
1920 IF (dummy_num = row_limit) THEN
1917 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1918 'ROUTING NETWORK',null,'Y',row_limit);
1919
1921 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1922 END IF;
1923
1924 sqltxt :=
1925 ' select tm.transaction_id,' ||
1926 ' decode (tm.transaction_type_id, 1, ''Split'',' ||
1927 ' 2, ''Merge'',' ||
1928 ' 3, ''Update Assly'',' ||
1929 ' 4, ''Bonus'',' ||
1930 ' 5, ''Update Routing'',' ||
1931 ' 6, ''Update Qty'',' ||
1932 ' 7, ''Update Lotname'',' ||
1933 ' tm.transaction_type_id ) transaction_type,' ||
1934 ' tm.transaction_date,' ||
1935 ' tm.organization_id,' ||
1936 ' decode (tm.status, 1, ''Pending'',' ||
1937 ' 2, ''Running'',' ||
1938 ' 3, ''Error'',' ||
1939 ' 4, ''Completed'',' ||
1940 ' 5, ''Warning'',' ||
1941 ' status) status,' ||
1942 ' decode (tm.costed, 1, ''Pending'',' ||
1943 ' 3, ''Error'',' ||
1944 ' 4, ''Costed'',' ||
1945 ' tm.costed) costed, ' ||
1946 ' sj.wip_entity_name, ' ||
1947 ' sj.representative_flag,' ||
1948 ' sj.job_start_quantity,' ||
1949 ' sj.operation_seq_num,' ||
1950 ' decode (sj.intraoperation_step, ' ||
1951 ' 1, ''Queue'',' ||
1952 ' 2, ''Run'',' ||
1953 ' 3, ''ToMove'',' ||
1954 ' 5, ''Scrap'',' ||
1955 ' intraoperation_step) intraoperation_step,' ||
1956 ' sj.available_quantity,' ||
1957 ' sj.routing_seq_id,' ||
1958 ' sj.primary_item_id,' ||
1959 ' tm.error_message' ||
1960 ' from wsm_split_merge_transactions tm,' ||
1961 ' wsm_sm_starting_jobs sj,' ||
1962 ' wip_entities w' ||
1963 ' where sj.transaction_id = tm.transaction_id' ||
1964 ' and sj.wip_entity_id = w.wip_entity_id' ||
1965 ' and w.wip_entity_id = ' || p_wip_entity_id ||
1966 ' order by 1' ;
1967
1968 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1969 'WIP Lot Transaction : Starting Lots',null,'Y',row_limit);
1970
1971 IF (dummy_num = row_limit) THEN
1972 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1973 END IF;
1974
1975 sqltxt :=
1976 ' select tm.transaction_id,' ||
1977 ' decode (tm.transaction_type_id, 1, ''Split'',' ||
1978 ' 2, ''Merge'',' ||
1979 ' 3, ''Update Assly'',' ||
1980 ' 4, ''Bonus'',' ||
1981 ' 5, ''Update Routing'',' ||
1982 ' 6, ''Update Qty'',' ||
1983 ' 7, ''Update Lotname'',' ||
1984 ' tm.transaction_type_id ) transaction_type,' ||
1985 ' tm.transaction_date,' ||
1986 ' tm.organization_id,' ||
1987 ' decode (tm.status, 1, ''Pending'',' ||
1988 ' 2, ''Running'',' ||
1989 ' 3, ''Error'',' ||
1990 ' 4, ''Completed'',' ||
1991 ' 5, ''Warning'',' ||
1992 ' tm.status) status,' ||
1993 ' decode (tm.costed, 1, ''Pending'',' ||
1994 ' 3, ''Error'',' ||
1995 ' 4, ''Costed'',' ||
1996 ' tm.costed) costed, ' ||
1997 ' rj.wip_entity_name,' ||
1998 ' rj.primary_item_id,' ||
1999 ' rj.start_quantity,' ||
2000 ' rj.common_bom_sequence_id,' ||
2001 ' rj.common_routing_sequence_id,' ||
2002 ' rj.alternate_bom_designator,' ||
2003 ' rj.alternate_routing_designator,' ||
2004 ' rj.completion_subinventory,' ||
2005 ' rj.completion_locator_id,' ||
2006 ' rj.starting_operation_seq_num,' ||
2007 ' decode (rj.starting_intraoperation_step, ' ||
2008 ' 1, ''Queue'',' ||
2009 ' 2, ''Run'',' ||
2010 ' 3, ''ToMove'',' ||
2011 ' 5, ''Scrap'',' ||
2012 ' rj.starting_intraoperation_step) starting_intraoperation_step,' ||
2013 ' rj.starting_operation_code,' ||
2014 ' rj.starting_std_op_id,' ||
2015 ' tm.error_message' ||
2016 ' from wsm_split_merge_transactions tm,' ||
2017 ' wsm_sm_resulting_jobs rj,' ||
2018 ' wip_entities w' ||
2019 ' where rj.transaction_id = tm.transaction_id' ||
2020 ' and rj.wip_entity_id = w.wip_entity_id' ||
2021 ' and rj.transaction_id in (select wssj.transaction_id' ||
2022 ' from wsm_sm_starting_jobs wssj' ||
2023 ' where wssj.wip_entity_id = ' || p_wip_entity_id ||
2024 ' ) order by transaction_id' ;
2025
2026 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2027 'Resulting Lots',null,'Y',row_limit);
2028
2029 IF (dummy_num = row_limit) THEN
2030 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2031 END IF;
2032
2033 sqltxt :=
2034 ' select tm.transaction_id,' ||
2035 ' decode (tm.transaction_type_id, 1, ''Split'',' ||
2036 ' 2, ''Merge'',' ||
2040 ' 6, ''Update Qty'',' ||
2037 ' 3, ''Update Assly'',' ||
2038 ' 4, ''Bonus'',' ||
2039 ' 5, ''Update Routing'',' ||
2041 ' 7, ''Update Lotname'',' ||
2042 ' tm.transaction_type_id ) transaction_type,' ||
2043 ' tm.transaction_date,' ||
2044 ' tm.organization_id,' ||
2045 ' decode (tm.status, 1, ''Pending'',' ||
2046 ' 2, ''Running'',' ||
2047 ' 3, ''Error'',' ||
2048 ' 4, ''Completed'',' ||
2049 ' 5, ''Warning'',' ||
2050 ' tm.status) status,' ||
2051 ' decode (tm.costed, 1, ''Pending'',' ||
2052 ' 3, ''Error'',' ||
2053 ' 4, ''Costed'',' ||
2054 ' tm.costed) costed, ' ||
2055 ' rj.wip_entity_name,' ||
2056 ' rj.primary_item_id,' ||
2057 ' rj.start_quantity,' ||
2058 ' rj.common_bom_sequence_id,' ||
2059 ' rj.common_routing_sequence_id,' ||
2060 ' rj.alternate_bom_designator,' ||
2061 ' rj.alternate_routing_designator,' ||
2062 ' rj.completion_subinventory,' ||
2063 ' rj.completion_locator_id,' ||
2064 ' rj.starting_operation_seq_num,' ||
2065 ' decode (rj.starting_intraoperation_step, ' ||
2066 ' 1, ''Queue'',' ||
2067 ' 2, ''Run'',' ||
2068 ' 3, ''ToMove'',' ||
2069 ' 5, ''Scrap'',' ||
2070 ' rj.starting_intraoperation_step) starting_intraoperation_step,' ||
2071 ' rj.starting_operation_code,' ||
2072 ' rj.starting_std_op_id,' ||
2073 ' tm.error_message' ||
2074 ' from wsm_split_merge_transactions tm,' ||
2075 ' wsm_sm_resulting_jobs rj,' ||
2076 ' wip_entities w' ||
2077 ' where rj.transaction_id = tm.transaction_id' ||
2078 ' and rj.wip_entity_id = w.wip_entity_id' ||
2079 ' and w.wip_entity_id = ' || p_wip_entity_id ||
2080 ' order by transaction_id' ;
2081
2082 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2083 'WIP Lot Transactions : Resulting Lots',null,'Y',row_limit);
2084
2085 IF (dummy_num = row_limit) THEN
2086 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2087 END IF;
2088
2089 sqltxt :=
2090 ' select tm.transaction_id,' ||
2091 ' decode (tm.transaction_type_id, 1, ''Split'',' ||
2092 ' 2, ''Merge'',' ||
2093 ' 3, ''Update Assly'',' ||
2094 ' 4, ''Bonus'',' ||
2095 ' 5, ''Update Routing'',' ||
2096 ' 6, ''Update Qty'',' ||
2097 ' 7, ''Update Lotname'',' ||
2098 ' tm.transaction_type_id ) transaction_type,' ||
2099 ' tm.transaction_date,' ||
2100 ' tm.organization_id,' ||
2101 ' decode (tm.status, 1, ''Pending'',' ||
2102 ' 2, ''Running'',' ||
2103 ' 3, ''Error'',' ||
2104 ' 4, ''Completed'',' ||
2105 ' 5, ''Warning'',' ||
2106 ' status) status,' ||
2107 ' decode (tm.costed, 1, ''Pending'',' ||
2108 ' 3, ''Error'',' ||
2109 ' 4, ''Costed'',' ||
2110 ' tm.costed) costed, ' ||
2111 ' sj.wip_entity_name, ' ||
2112 ' sj.representative_flag,' ||
2113 ' sj.job_start_quantity,' ||
2114 ' sj.operation_seq_num,' ||
2115 ' decode (sj.intraoperation_step, ' ||
2116 ' 1, ''Queue'',' ||
2117 ' 2, ''Run'',' ||
2118 ' 3, ''ToMove'',' ||
2119 ' 5, ''Scrap'',' ||
2120 ' intraoperation_step) intraoperation_step,' ||
2121 ' sj.available_quantity,' ||
2122 ' sj.routing_seq_id,' ||
2123 ' sj.primary_item_id,' ||
2124 ' tm.error_message' ||
2125 ' from wsm_split_merge_transactions tm,' ||
2126 ' wsm_sm_starting_jobs sj,' ||
2127 ' wip_entities w' ||
2128 ' where sj.transaction_id = tm.transaction_id' ||
2129 ' and sj.wip_entity_id = w.wip_entity_id' ||
2130 ' and sj.transaction_id in (select wsrj.transaction_id' ||
2131 ' from wsm_sm_resulting_jobs wsrj' ||
2132 ' where wsrj.wip_entity_id = ' || p_wip_entity_id ||
2133 ' ) order by transaction_id' ;
2134
2135 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2136 'Starting Lots',null,'Y',row_limit);
2137
2138 IF (dummy_num = row_limit) THEN
2139 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2140 END IF;
2141
2142 exception
2143 when no_data_found then null ;
2144 end ;
2145 END disc_lot_job ;
2146
2147 procedure repetitive (p_wip_entity_id IN NUMBER,
2148 p_line_id IN NUMBER,
2149 p_rep_schedule_id IN NUMBER ) is
2150 row_limit NUMBER;
2151 BEGIN
2152 row_limit := 1000;
2153
2154 sqltxt :=
2155 'select a.wip_entity_id , '||
2156 ' b.wip_entity_name, '||
2157 ' a.repetitive_schedule_id, '||
2158 ' a.line_id, '||
2159 ' decode(b.entity_type,1, ''1=Discrete Job'', '||
2160 ' 2, ''2=Repetitive Assly'', '||
2164 ' b.entity_type) entity_type, '||
2161 ' 3, ''3=Closed Discr Job'', '||
2162 ' 4, ''4=Flow Schedule'', '||
2163 ' 5, ''5=Lot Based Job'', '||
2165 ' a.organization_id, '||
2166 ' p.organization_code, '||
2167 ' wri.primary_item_id, '||
2168 ' decode(a.status_type, '||
2169 ' 1,''Unreleased'', '||
2170 ' 3, ''Released'', '||
2171 ' 4, ''Complete'', '||
2172 ' 5, ''Complete NoCharge'', '||
2173 ' 6, ''On Hold'', '||
2174 ' 7, ''Cancelled'', '||
2175 ' 8, ''Pend Bill Load'', '||
2176 ' 9, ''Failed Bill Load'', '||
2177 ' 10, ''Pend Rtg Load'', '||
2178 ' 11, ''Failed Rtg Load'', '||
2179 ' 12, ''Closed'', '||
2180 ' 13, ''Pending- Mass Loaded'', '||
2181 ' 14, ''Pending Close'', '||
2182 ' 15, ''Failed Close'', '||
2183 ' a.status_type) status_type, '||
2184 ' wri.completion_subinventory, '||
2185 ' wri.completion_locator_id, '||
2186 ' m.primary_uom_code uom_code, '||
2187 ' a.processing_work_days, '||
2188 ' a.daily_production_rate, '||
2189 ' a.quantity_completed, '||
2190 ' decode(wri.wip_supply_type, 1, ''Push'', '||
2191 ' 2, ''Assembly Pull'', '||
2192 ' 3, ''Operation Pull'', '||
2193 ' 4, ''Bulk'', '||
2194 ' 5, ''Supplier'', '||
2195 ' 6, ''Phantom'', '||
2196 ' 7, ''Based on Bill'', '||
2197 ' wri.wip_supply_type) wip_supply_type, '||
2198 ' wri.class_code, '||
2199 ' decode(wri.overcompletion_tolerance_type, '||
2200 ' 1, ''Percent'', '||
2201 ' 2, ''Amount'') Tol_Type, '||
2202 ' wri.overcompletion_tolerance_value Tol_Value, '||
2203 ' a.date_released, '||
2204 ' a.creation_date, '||
2205 ' a.common_bom_sequence_id, '||
2206 ' a.common_routing_sequence_id, '||
2207 ' a.first_unit_start_date, '||
2208 ' a.first_unit_completion_date, '||
2209 ' a.last_unit_start_date, '||
2210 ' a.last_unit_completion_date, '||
2211 ' a.bom_revision, '||
2212 ' a.routing_revision, '||
2213 ' nvl(a.alternate_bom_designator, ''PRIMARY'') alternate_bom_designator, '||
2214 ' nvl(a.alternate_routing_designator, ''PRIMARY'') alternate_routing_designator '||
2215 'from wip_repetitive_schedules a , wip_repetitive_items wri, wip_entities b, mtl_system_items m , mtl_parameters p '||
2216 'where b.wip_entity_id = a.wip_entity_id '||
2217 'and wri.wip_entity_id = a.wip_entity_id '||
2218 'and b.organization_id = a.organization_id '||
2219 'and m.inventory_item_id = b.primary_item_id '||
2220 'and m.organization_id = a.organization_id '||
2221 'and a.organization_id = p.organization_id ';
2222
2223
2224 if p_wip_entity_id is not null then
2225 sqltxt := sqltxt ||' and b.wip_entity_id = '|| p_wip_entity_id;
2226 end if;
2227
2228 if p_line_id is not null then
2229 sqltxt := sqltxt ||' and wri.line_id = '|| p_line_id ;
2230 end if;
2231
2232 if p_rep_schedule_id is not null then
2233 sqltxt := sqltxt ||' and a.repetitive_schedule_id = '|| p_rep_schedule_id;
2234 end if;
2235
2236 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2237 'WIP HEADER ( WIP REPETITIVE SCHEDULES, WIP REPETITIVE ITEMS, WIP ENTITIES )',null,'Y',row_limit);
2238
2239 IF (dummy_num = row_limit) THEN
2240 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2241 END IF;
2242
2243
2244 sqltxt :=
2245 'select a.operation_seq_num, ' ||
2246 ' a.operation_sequence_id, ' ||
2247 ' a.standard_operation_id, ' ||
2248 ' bso.operation_code , ' ||
2249 ' a.department_id, ' ||
2250 ' c.department_code, ' ||
2251 ' a.description, ' ||
2252 ' a.first_unit_start_date, ' ||
2253 ' a.first_unit_completion_date, ' ||
2254 ' a.last_unit_start_date, ' ||
2255 ' a.last_unit_completion_date, ' ||
2256 ' a.quantity_in_queue , ' ||
2257 ' a.quantity_running , ' ||
2258 ' a.quantity_waiting_to_move , ' ||
2259 ' a.quantity_rejected , ' ||
2260 ' a.quantity_scrapped , ' ||
2261 ' a.quantity_completed , ' ||
2262 ' a.previous_operation_seq_num, ' ||
2263 ' a.next_operation_seq_num, ' ||
2264 ' a.count_point_type, ' ||
2265 ' decode( a.backflush_flag, 1, ''Yes'', ' ||
2266 ' 2, ''No'') backflush_flag, ' ||
2267 ' a.minimum_transfer_quantity, ' ||
2268 ' a.date_last_moved, ' ||
2269 ' a.creation_date ' ||
2270 'from wip_operations a, wip_entities b, bom_departments c, bom_standard_operations bso ' ||
2271 'where a.wip_entity_id = b.wip_entity_id ' ||
2272 'and a.organization_id = b.organization_id ' ||
2273 'and a.department_id = c.department_id ' ||
2274 'and a.organization_id = c.organization_id ' ||
2275 'and a.standard_operation_id = bso.standard_operation_id(+) ' ||
2276 'and a.organization_id = bso.organization_id(+) ';
2277
2278 if p_wip_entity_id is not null then
2279 sqltxt :=sqltxt||' and a.wip_entity_id = '|| p_wip_entity_id;
2280 end if;
2281 if p_rep_schedule_id is not null then
2285
2282 sqltxt :=sqltxt||' and a.repetitive_schedule_id = '|| p_rep_schedule_id;
2283 sqltxt :=sqltxt||' order by 1 ';
2284 end if;
2286 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2287 'WIP OPERATIONS',null,'Y',row_limit);
2288
2289 IF (dummy_num = row_limit) THEN
2290 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2291 END IF;
2292
2293 sqltxt :=
2294 'select a.wip_entity_id, '||
2295 ' a.inventory_item_id, '||
2296 ' substrb(m.concatenated_segments, 1, 30) item_name, '||
2297 ' a.organization_id, '||
2298 ' a.operation_seq_num, '||
2299 ' a.component_sequence_id, '||
2300 ' a.department_id, '||
2301 ' decode(a.wip_supply_type, 1, ''Push'', '||
2302 ' 2, ''Assembly Pull'', '||
2303 ' 3, ''Operation Pull'', '||
2304 ' 4, ''Bulk'', '||
2305 ' 5, ''Supplier'', '||
2306 ' 6, ''Phantom'', '||
2307 ' 7, ''Based on Bill'', '||
2308 ' a.wip_supply_type) wip_supply_type, '||
2309 ' a.required_quantity, '||
2310 ' a.quantity_issued, '||
2311 ' a.quantity_per_assembly, '||
2312 ' a.supply_subinventory, '||
2313 ' a.supply_locator_id, '||
2314 ' a.quantity_allocated, '||
2315 ' a.quantity_backordered, '||
2316 ' a.quantity_relieved, '||
2317 ' a.creation_date '||
2318 'from wip_requirement_operations a, wip_entities b, mtl_system_items_kfv m '||
2319 'where b.wip_entity_id = a.wip_entity_id '||
2320 'and b.organization_id = a.organization_id '||
2321 'and a.inventory_item_id = m.inventory_item_id '||
2322 'and a.organization_id = m.organization_id ';
2323
2324 if p_wip_entity_id is not null then
2325 sqltxt :=sqltxt||' and a.wip_entity_id = '|| p_wip_entity_id;
2326 end if;
2327 if p_rep_schedule_id is not null then
2328 sqltxt :=sqltxt||' and a.repetitive_schedule_id = '|| p_rep_schedule_id;
2329 sqltxt :=sqltxt||' order by operation_seq_num,inventory_item_id ';
2330 end if;
2331
2332 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2333 'WIP REQUIREMENT OPERATIONS',null,'Y',row_limit);
2334
2335 IF (dummy_num = row_limit) THEN
2336 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2337 END IF;
2338
2339 sqltxt :=
2340 'select a.operation_seq_num, '||
2341 ' a.resource_seq_num, '||
2342 ' a.resource_id, '||
2343 ' a.autocharge_type, '||
2344 ' c.resource_code, '||
2345 ' a.uom_code, '||
2346 ' decode (a.basis_type, 1, ''Item'', '||
2347 ' 2, ''Lot'', '||
2348 ' 3, ''Res Units'', '||
2349 ' 4, ''Res Value'', '||
2350 ' 5, ''Tot Value'', '||
2351 ' 6, ''Activity'', '||
2352 ' a.BASIS_TYPE) basis_type, '||
2353 ' decode(a.scheduled_flag, 1, ''Yes'', '||
2354 ' 2, ''No'', '||
2355 ' 3, ''Prior'', '||
2356 ' 4, ''Next'', '||
2357 ' a.scheduled_flag) scheduled_flag, '||
2358 ' a.usage_rate_or_amount , '||
2359 ' a.start_date, '||
2360 ' a.completion_date, '||
2361 ' a.applied_resource_units, '||
2362 ' a.applied_resource_value, '||
2363 ' a.creation_date '||
2364 'from wip_operation_resources a, bom_resources c '||
2365 'where a.resource_id = c.resource_id '||
2366 'and a.organization_id = c.organization_id ';
2367
2368
2369 if p_wip_entity_id is not null then
2370 sqltxt :=sqltxt||' and a.wip_entity_id = '|| p_wip_entity_id;
2371 end if;
2372 if p_rep_schedule_id is not null then
2373 sqltxt :=sqltxt||' and a.repetitive_schedule_id = '|| p_rep_schedule_id;
2374 sqltxt :=sqltxt||' order by 1,2';
2375 end if;
2376
2377 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2378 'WIP OPERATION RESOURCES',null,'Y',row_limit);
2379
2380 IF (dummy_num = row_limit) THEN
2381 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2382 END IF;
2383
2384 sqltxt :=
2385 'select a.operation_seq_num, '||
2386 ' a.resource_seq_num, '||
2387 ' c.resource_code, '||
2388 ' a.organization_id, '||
2389 ' a.repetitive_schedule_id, '||
2390 ' a.start_date, '||
2391 ' a.completion_date, '||
2392 ' a.assigned_units, '||
2393 ' a.creation_date '||
2394 'from wip_operation_resource_usage a, wip_operation_resources b, bom_resources c '||
2395 'where a.wip_entity_id = b.wip_entity_id '||
2396 'and a.operation_seq_num = b.operation_seq_num '||
2397 'and nvl(a.repetitive_schedule_id,0) = nvl(b.repetitive_schedule_id,0) '||
2398 'and a.resource_seq_num = b.resource_seq_num '||
2399 'and b.resource_id = c.resource_id '||
2400 'and b.organization_id = c.organization_id ';
2401
2402
2403 if p_wip_entity_id is not null then
2404 sqltxt :=sqltxt||' and a.wip_entity_id = '|| p_wip_entity_id;
2405 end if;
2406 if p_rep_schedule_id is not null then
2407 sqltxt :=sqltxt||' and a.repetitive_schedule_id = '|| p_rep_schedule_id;
2408 sqltxt :=sqltxt||' order by 1,2 ';
2409 end if;
2410
2411 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2415 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2412 'WIP OPERATION RESOURCE USAGES',null,'Y',row_limit);
2413
2414 IF (dummy_num = row_limit) THEN
2416 END IF;
2417
2418 sqltxt :=
2419 'select acct_period_id, '||
2420 ' decode(class_type,1,''Standard Discrete'', '||
2421 ' 2,''Repetitive Assembly'', '||
2422 ' 3,''Asset non-standard'', '||
2423 ' 4,''Expense non-standard'', '||
2424 ' 5,''Standard Lot Based'', '||
2425 ' 6,''EAM'', '||
2426 ' 7,''Expense non-standard Lot Based'', '||
2427 ' class_type) class_type, '||
2428 ' tl_resource_in, '||
2429 ' tl_overhead_in, '||
2430 ' tl_outside_processing_in, '||
2431 ' pl_material_in, '||
2432 ' pl_material_overhead_in, '||
2433 ' pl_resource_in, '||
2434 ' pl_overhead_in, '||
2435 ' pl_outside_processing_in, '||
2436 ' tl_material_out, '||
2437 ' tl_material_overhead_out, '||
2438 ' tl_resource_out, '||
2439 ' tl_overhead_out, '||
2440 ' tl_outside_processing_out, '||
2441 ' pl_material_out, '||
2442 ' pl_material_overhead_out, '||
2443 ' pl_resource_out, '||
2444 ' pl_overhead_out, '||
2445 ' pl_outside_processing_out, '||
2446 ' tl_scrap_in, '||
2447 ' tl_scrap_out, '||
2448 ' tl_scrap_var, '||
2449 ' creation_date, '||
2450 ' last_update_date '||
2451 'from wip_period_balances ';
2452
2453 if p_wip_entity_id is not null then
2454 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id;
2455 end if;
2456 if p_rep_schedule_id is not null then
2457 sqltxt :=sqltxt||' and repetitive_schedule_id = '|| p_rep_schedule_id;
2458 sqltxt :=sqltxt||' order by creation_date ';
2459 end if;
2460
2461 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2462 'WIP PERIOD BALANCES',null,'Y',row_limit);
2463
2464 IF (dummy_num = row_limit) THEN
2465 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2466 END IF;
2467
2468 sqltxt :=
2469 'select wmt.transaction_id, '||
2470 ' wmt.group_id, '||
2471 ' wmt.fm_operation_code, '||
2472 ' wmt.fm_operation_seq_num, '||
2473 ' decode (wmt.fm_intraoperation_step_type, '||
2474 ' 1, ''Queue'', '||
2475 ' 2, ''Run'', '||
2476 ' 3, ''ToMove'', '||
2477 ' 5, ''Scrap'', '||
2478 ' wmt.fm_intraoperation_step_type) fm_intraoperation_step_type, '||
2479 ' wmt.to_operation_code, '||
2480 ' wmt.to_operation_seq_num, '||
2481 ' decode (wmt.to_intraoperation_step_type, '||
2482 ' 1, ''Queue'', '||
2483 ' 2, ''Run'', '||
2484 ' 3, ''ToMove'', '||
2485 ' 5, ''Scrap'', '||
2486 ' wmt.to_intraoperation_step_type) to_intraoperation_step_type, '||
2487 ' wmt.transaction_quantity, '||
2488 ' wmta.transaction_quantity Allocation_Txn_Qty, ' ||
2489 ' wmt.transaction_uom, '||
2490 ' wmt.primary_quantity, '||
2491 ' wmta.primary_quantity Allocation_Primary_Qty, ' ||
2492 ' wmt.primary_uom, '||
2493 ' wmt.source_code, '||
2494 ' wmt.source_line_id, '||
2495 ' wmt.organization_id, '||
2496 ' wmt.primary_item_id, '||
2497 ' wmt.transaction_date, '||
2498 ' wmt.creation_date, '||
2499 ' wmt.acct_period_id, '||
2500 ' wmt.scrap_account_id '||
2501 ' from wip_move_transactions wmt , '||
2502 ' wip_move_txn_allocations wmta '||
2503 'where wmt.transaction_id = wmta.transaction_id ';
2504
2505 if p_wip_entity_id is not null then
2506 sqltxt :=sqltxt||' and wmt.wip_entity_id = '|| p_wip_entity_id;
2507 end if;
2508 if p_rep_schedule_id is not null then
2509 sqltxt :=sqltxt||' and wmta.repetitive_schedule_id = '|| p_rep_schedule_id;
2510 sqltxt :=sqltxt||' order by 1 ';
2511 end if;
2512
2513 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2514 'WIP MOVE TRANSACTIONS AND ALLOCATION',null,'Y',row_limit);
2515
2516 IF (dummy_num = row_limit) THEN
2517 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2518 END IF;
2519
2520 sqltxt :=
2521 'select wmti.transaction_id, '||
2522 ' group_id, '||
2523 ' source_code, '||
2524 ' source_line_id, '||
2525 ' decode(process_phase, '||
2526 ' 1, ''Move Valdn'', '||
2527 ' 2, ''Move Proc'', '||
2528 ' 3, ''BF Setup'', '||
2529 ' process_phase) process_phase_meaning, '||
2530 ' decode(process_status, '||
2531 ' 1, ''Pending'', '||
2532 ' 2, ''Running'', '||
2533 ' 3, ''Error'', '||
2534 ' 4, ''Completed'', '||
2535 ' 5, ''Warning'', '||
2536 ' process_status) process_status_meaning, '||
2537 ' decode(transaction_type, '||
2538 ' 1, ''Move'', '||
2539 ' 2, ''Complete'', '||
2540 ' 3, ''Return'', '||
2541 ' transaction_type) transaction_type_meaning, '||
2542 ' repetitive_schedule_id, '||
2543 ' fm_operation_seq_num, '||
2544 ' fm_operation_code, '||
2545 ' decode (fm_intraoperation_step_type, '||
2549 ' 5, ''Scrap'', '||
2546 ' 1, ''Queue'', '||
2547 ' 2, ''Run'', '||
2548 ' 3, ''ToMove'', '||
2550 ' fm_intraoperation_step_type) fm_intraoperation_step_type, '||
2551 ' to_operation_seq_num, '||
2552 ' to_operation_code, '||
2553 ' decode (to_intraoperation_step_type, '||
2554 ' 1, ''Queue'', '||
2555 ' 2, ''Run'', '||
2556 ' 3, ''ToMove'', '||
2557 ' 5, ''Scrap'', '||
2558 ' to_intraoperation_step_type) to_intraoperation_step_type, '||
2559 ' transaction_quantity, '||
2560 ' transaction_uom, '||
2561 ' primary_quantity, '||
2562 ' primary_uom, '||
2563 ' organization_id, '||
2564 ' primary_item_id, '||
2565 ' transaction_date, '||
2566 ' wmti.creation_date, '||
2567 ' acct_period_id, '||
2568 ' scrap_account_id, '||
2569 ' overcompletion_transaction_qty, '||
2570 ' overcompletion_primary_qty, '||
2571 ' overcompletion_transaction_id, '||
2572 ' error_column, '||
2573 ' error_message '||
2574 'from wip_move_txn_interface wmti, '||
2575 ' wip_txn_interface_errors wtie '||
2576 'where wmti.transaction_id = wtie.transaction_id (+) ';
2577
2578
2579 if p_wip_entity_id is not null then
2580 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id;
2581 sqltxt :=sqltxt||' order by 1 ';
2582 end if;
2583
2584 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2585 'WIP MOVE TXN INTERFACE',null,'Y',row_limit);
2586
2587 IF (dummy_num = row_limit) THEN
2588 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2589 END IF;
2590
2591 /*
2592 sqltxt :=
2593 'select allocation_id, '||
2594 ' organization_id, '||
2595 ' demand_source_header_id, '||
2596 ' demand_source_line, '||
2597 ' user_line_num, '||
2598 ' demand_source_delivery, '||
2599 ' user_delivery, '||
2600 ' quantity_allocated, '||
2601 ' quantity_completed, '||
2602 ' demand_class, '||
2603 ' creation_date '||
2604 'from wip_so_allocations ';
2605
2606 if p_wip_entity_id is not null then
2607 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ;
2608 sqltxt :=sqltxt||' order by allocation_id ';
2609 end if;
2610
2611 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2612 'WIP SO ALLOCATIONS',null,'Y',row_limit);
2613
2614 IF (dummy_num = row_limit) THEN
2615 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2616 END IF;
2617
2618 */
2619
2620 sqltxt :=
2621 'select wcti.transaction_id, '||
2622 ' wcti.creation_date, '||
2623 ' wcti.last_update_date, '||
2624 ' wcti.request_id, '||
2625 ' source_code, '||
2626 ' source_line_id, '||
2627 ' decode(process_phase, '||
2628 ' 1, ''Res Valdn'', '||
2629 ' 2, ''Res Processing'', '||
2630 ' 3, ''Job Close'', '||
2631 ' 4, ''Prd Close'', '||
2632 ' process_phase) process_phase_meaning, '||
2633 ' decode(process_status, '||
2634 ' 1, ''Pending'', '||
2635 ' 2, ''Running'', '||
2636 ' 3, ''Error'', '||
2637 ' 4, ''Completed'', '||
2638 ' 5, ''Warning'', '||
2639 ' process_status) process_status_meaning, '||
2640 ' decode(transaction_type, '||
2641 ' 1, ''Resource'', '||
2642 ' 2, ''Overhead'', '||
2643 ' 3, ''OSP'', '||
2644 ' 4, ''Cost Update'', '||
2645 ' 5, ''PrdClose Var'', '||
2646 ' 6, ''JobClose Var'', '||
2647 ' transaction_type) transaction_type_meaning, '||
2648 ' organization_id, '||
2649 ' organization_code, '||
2650 ' primary_item_id, '||
2651 ' transaction_date, '||
2652 ' operation_seq_num, '||
2653 ' resource_seq_num, '||
2654 ' acct_period_id, '||
2655 ' resource_id, '||
2656 ' decode(resource_type, '||
2657 ' 1, ''Machine'', '||
2658 ' 2, ''Person'', '||
2659 ' 3, ''Space'', '||
2660 ' 4, ''Misc'', '||
2661 ' 5, ''Amount'', '||
2662 ' resource_type) resource_type, '||
2663 ' transaction_quantity, '||
2664 ' actual_resource_rate, '||
2665 ' transaction_uom, '||
2666 ' decode(basis_type, '||
2667 ' 1, ''Item'', '||
2668 ' 2, ''Lot'', '||
2669 ' 3, ''Res Units'', '||
2670 ' 4, ''Res Value'', '||
2671 ' 5, ''Tot Value'', '||
2672 ' 6, ''Activity'') basis_type, '||
2673 ' move_transaction_id, '||
2674 ' completion_transaction_id, '||
2675 ' error_column, '||
2676 ' error_message '||
2677 'from wip_cost_txn_interface wcti, '||
2678 ' wip_txn_interface_errors wtie '||
2679 'where wcti.transaction_id = wtie.transaction_id (+) ';
2680
2681 if p_wip_entity_id is not null then
2682 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id ;
2683 end if;
2684 if p_rep_schedule_id is not null then
2685 sqltxt :=sqltxt||' and repetitive_schedule_id = '|| p_rep_schedule_id ;
2686 sqltxt :=sqltxt||' order by transaction_id';
2687 end if;
2688
2689 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2690 'WIP COST TXN INTERFACE',null,'Y',row_limit);
2691
2692 IF (dummy_num = row_limit) THEN
2696 sqltxt :=
2693 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2694 END IF;
2695
2697 'select wt.transaction_id, '||
2698 ' wt.creation_date, '||
2699 ' wt.last_update_date, '||
2700 ' wt.request_id, '||
2701 ' wt.source_code, '||
2702 ' wt.source_line_id, '||
2703 ' wt.group_id, '||
2704 ' decode(wt.transaction_type, '||
2705 ' 1, ''Resource'', '||
2706 ' 2, ''Overhead'', '||
2707 ' 3, ''OSP'', '||
2708 ' 4, ''Cost Update'', '||
2709 ' 5, ''PrdClose Var'', '||
2710 ' 6, ''JobClose Var'', '||
2711 ' wt.transaction_type) transaction_type_meaning, '||
2712 ' wt.organization_id, '||
2713 ' wt.primary_item_id, '||
2714 ' wt.transaction_date, '||
2715 ' wt.operation_seq_num, '||
2716 ' wt.resource_seq_num, '||
2717 ' wt.acct_period_id, '||
2718 ' wt.resource_id, '||
2719 ' wt.transaction_quantity, '||
2720 ' wta.transaction_quantity Allocation_Txn_Qty, ' ||
2721 ' wt.actual_resource_rate, '||
2722 ' wt.standard_resource_rate, '||
2723 ' wt.transaction_uom, '||
2724 ' wt.move_transaction_id, '||
2725 ' wt.completion_transaction_id '||
2726 'from wip_transactions wt, '||
2727 ' wip_txn_allocations wta '||
2728 'where wt.transaction_id = wta.transaction_id ';
2729
2730 if p_wip_entity_id is not null then
2731 sqltxt :=sqltxt||' and wt.wip_entity_id = '|| p_wip_entity_id ;
2732 end if;
2733 if p_rep_schedule_id is not null then
2734 sqltxt :=sqltxt||' and wta.repetitive_schedule_id = '|| p_rep_schedule_id ;
2735 sqltxt :=sqltxt||' order by transaction_id';
2736 end if;
2737
2738 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2739 'WIP TRANSACTIONS AND ALLOCATIONS',null,'Y',row_limit);
2740
2741 IF (dummy_num = row_limit) THEN
2742 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2743 END IF;
2744
2745 sqltxt :=
2746 'select transaction_interface_id, '||
2747 ' transaction_header_id, '||
2748 ' source_code, '||
2749 ' source_line_id, '||
2750 ' source_header_id, '||
2751 ' process_flag, '||
2752 ' transaction_mode, '||
2753 ' lock_flag, '||
2754 ' request_id, '||
2755 ' inventory_item_id, '||
2756 ' organization_id, '||
2757 ' transaction_quantity, '||
2758 ' primary_quantity, '||
2759 ' transaction_uom, '||
2760 ' transaction_date, '||
2761 ' subinventory_code, '||
2762 ' locator_id, '||
2763 ' revision, '||
2764 ' transaction_source_id, '||
2765 ' decode(transaction_source_type_id, '||
2766 ' 1, ''PO'', '||
2767 ' 2, ''SO'', '||
2768 ' 4, ''MoveOrder'', '||
2769 ' 5, ''WIP'', '||
2770 ' 6, ''AcctAlias'', '||
2771 ' 7, ''Int REQ'', '||
2772 ' 8, ''Int Order'', '||
2773 ' 9, ''CycleCount'', '||
2774 ' 10,''PhyCount'', '||
2775 ' 11,''StdCostUpd'', '||
2776 ' 12, ''RMA'', '||
2777 ' 13, ''INV'', '||
2778 ' 17, ''Ext REQ'', '||
2779 ' transaction_source_type_id) txn_source_meaning, '||
2780 ' decode(transaction_action_id, '||
2781 ' 1, ''Issue'', '||
2782 ' 2, ''Subinv Xfr'', '||
2783 ' 3, ''Org Xfr'', '||
2784 ' 4, ''Cycle Count Adj'', '||
2785 ' 5, ''Plan Xfr'', '||
2786 ' 21, ''Intransit Shpmt'', '||
2787 ' 24, ''Cost Update'', '||
2788 ' 27, ''Receipt'', '||
2789 ' 28, ''Stg Xfr'', '||
2790 ' 30, ''Wip scrap'', '||
2791 ' 31, ''Assy Complete'', '||
2792 ' 32, ''Assy return'', '||
2793 ' 33, ''-ve CompIssue'', '||
2794 ' 34, ''-ve CompReturn'', '||
2795 ' 40, ''Inv Lot Split'', '||
2796 ' 41, ''Inv Lot Merge'', '||
2797 ' 42, ''Inv Lot Translate'', '||
2798 ' 42, ''Inv Lot Translate'', '||
2799 ' transaction_action_id) txn_action_meaning, '||
2800 ' transaction_type_id, '||
2801 ' operation_seq_num, '||
2802 ' repetitive_line_id, '||
2803 ' transfer_organization, '||
2804 ' transfer_subinventory, '||
2805 ' transfer_locator, '||
2806 ' overcompletion_transaction_qty, '||
2807 ' overcompletion_primary_qty, '||
2808 ' overcompletion_transaction_id, '||
2809 ' error_code, '||
2810 ' substr(error_explanation,1,100) error_explanation '||
2811 'from mtl_transactions_Interface mti '||
2812 'where mti.transaction_source_type_id = 5 ';
2813
2814 if p_wip_entity_id is not null then
2815 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id;
2816 sqltxt :=sqltxt||' order by transaction_interface_id, transaction_date ';
2817 end if;
2818
2819 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2820 'MTI TRANSACTIONS',null,'Y',row_limit);
2821
2822 IF (dummy_num = row_limit) THEN
2823 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2824 END IF;
2825
2826 sqltxt :=
2827 'select '||
2828 ' transaction_interface_id, '||
2829 ' source_code, '||
2830 ' source_line_id, '||
2831 ' request_id, '||
2832 ' lot_number, '||
2833 ' lot_expiration_date, '||
2834 ' transaction_quantity, '||
2835 ' primary_quantity, '||
2836 ' serial_transaction_temp_id, '||
2837 ' process_flag, '||
2838 ' error_code '||
2839 'from mtl_transaction_lots_interface mtli '||
2843 ' where mti.transaction_source_type_id = 5 ';
2840 'where mtli.transaction_interface_id in '||
2841 ' (select transaction_interface_id '||
2842 ' from mtl_transactions_Interface mti '||
2844
2845
2846 if p_wip_entity_id is not null then
2847 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id ||')';
2848 sqltxt :=sqltxt||' order by lot_expiration_date ';
2849 end if;
2850
2851 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2852 'MTLI TRANSACTIONS',null,'Y',row_limit);
2853
2854 IF (dummy_num = row_limit) THEN
2855 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2856 END IF;
2857
2858 sqltxt :=
2859 'select '||
2860 ' transaction_interface_id, '||
2861 ' source_code, '||
2862 ' source_line_id, '||
2863 ' request_id, '||
2864 ' vendor_serial_number, '||
2865 ' vendor_lot_number, '||
2866 ' fm_serial_number, '||
2867 ' to_serial_number, '||
2868 ' error_code, '||
2869 ' process_flag, '||
2870 ' parent_serial_number '||
2871 'from mtl_serial_numbers_interface msni '||
2872 'where msni.transaction_interface_id in '||
2873 ' (select transaction_interface_id '||
2874 ' from mtl_transactions_Interface mti '||
2875 ' where mti.transaction_source_type_id = 5 ';
2876
2877 if p_wip_entity_id is not null then
2878 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id ||')';
2879 sqltxt :=sqltxt||' order by fm_serial_number ';
2880 end if;
2881
2882 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2883 'MSNI TRANSACTIONS',null,'Y',row_limit);
2884
2885 IF (dummy_num = row_limit) THEN
2886 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2887 END IF;
2888
2889 sqltxt :=
2890 'select transaction_temp_id, '||
2891 ' transaction_header_id, '||
2892 ' source_code, '||
2893 ' source_line_id, '||
2894 ' transaction_mode, '||
2895 ' lock_flag, '||
2896 ' transaction_date, '||
2897 ' transaction_type_id, '||
2898 ' decode(transaction_action_id, '||
2899 ' 1, ''Issue'', '||
2900 ' 2, ''Subinv Xfr'', '||
2901 ' 3, ''Org Xfr'', '||
2902 ' 4, ''Cycle Count Adj'', '||
2903 ' 5, ''Issue'', '||
2904 ' 21, ''Intransit Shpmt'', '||
2905 ' 24, ''Cost Update'', '||
2906 ' 27, ''Receipt'', '||
2907 ' 28, ''Stg Xfr'', '||
2908 ' 30, ''Wip scrap'', '||
2909 ' 31, ''Assy Complete'', '||
2910 ' 32, ''Assy return'', '||
2911 ' 33, ''-ve CompIssue'', '||
2912 ' 34, ''-ve CompReturn'', '||
2913 ' 40, ''Inv Lot Split'', '||
2914 ' 41, ''Inv Lot Merge'', '||
2915 ' 42, ''Inv Lot Translate'', '||
2916 ' 42, ''Inv Lot Translate'', '||
2917 ' transaction_action_id) txn_action_meaning, '||
2918 ' decode(transaction_source_type_id, '||
2919 ' 1, ''PO'', '||
2920 ' 2, ''SO'', '||
2921 ' 4, ''MoveOrder'', '||
2922 ' 5, ''WIP'', '||
2923 ' 6, ''AcctAlias'', '||
2924 ' 7, ''Int REQ'', '||
2925 ' 8, ''Int Order'', '||
2926 ' 9, ''CycleCount'', '||
2927 ' 10,''PhyCount'', '||
2928 ' 11,''StdCostUpd'', '||
2929 ' 12, ''RMA'', '||
2930 ' 13, ''INV'', '||
2931 ' 17, ''Ext REQ'', '||
2932 ' transaction_source_type_id) txn_source_meaning, '||
2933 ' transaction_source_id, '||
2934 ' inventory_item_id, '||
2935 ' organization_id, '||
2936 ' subinventory_code, '||
2937 ' locator_id, '||
2938 ' revision, '||
2939 ' transaction_quantity, '||
2940 ' transaction_uom, '||
2941 ' primary_quantity, '||
2942 ' trx_source_line_id, '||
2943 ' trx_source_delivery_id, '||
2944 ' overcompletion_transaction_qty, '||
2945 ' overcompletion_primary_qty, '||
2946 ' overcompletion_transaction_id, '||
2947 ' move_transaction_id, '||
2948 ' completion_transaction_id, '||
2949 ' source_code, '||
2950 ' source_line_id, '||
2951 ' transfer_organization, '||
2952 ' transfer_subinventory, '||
2953 ' transfer_to_location, '||
2954 ' move_order_line_id, '||
2955 ' reservation_id, '||
2956 ' creation_date, '||
2957 ' last_update_date, '||
2958 ' error_code '||
2959 'from mtl_material_transactions_temp '||
2960 'where transaction_source_type_id = 5 ';
2961
2962
2963 if p_wip_entity_id is not null then
2964 sqltxt :=sqltxt||' and transaction_source_id = '|| p_wip_entity_id ;
2965 sqltxt :=sqltxt||' order by transaction_temp_id ';
2966 end if;
2967
2968 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
2969 'MMTT TRANSACTIONS',null,'Y',row_limit);
2970
2971 IF (dummy_num = row_limit) THEN
2972 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
2973 END IF;
2974
2975 sqltxt :=
2976 'select '||
2977 ' transaction_temp_id, '||
2978 ' transaction_quantity, '||
2979 ' primary_quantity, '||
2980 ' lot_number, '||
2981 ' lot_expiration_date, '||
2982 ' serial_transaction_temp_id, '||
2983 ' group_header_id, '||
2984 ' put_away_rule_id, '||
2985 ' pick_rule_id, '||
2986 ' request_id, '||
2987 ' creation_date, '||
2988 ' error_code '||
2989 'from mtl_transaction_lots_temp mtlt '||
2993 ' where mmtt.transaction_source_type_id = 5 ';
2990 'where mtlt.transaction_temp_id in '||
2991 ' (select mmtt.transaction_temp_id '||
2992 ' from mtl_material_transactions_temp mmtt '||
2994
2995 if p_wip_entity_id is not null then
2996 sqltxt :=sqltxt||' and mmtt.transaction_source_id = '|| p_wip_entity_id ||')' ;
2997 sqltxt :=sqltxt||' order by transaction_temp_id, lot_number ';
2998 end if;
2999
3000 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3001 'MTLT TRANSACTIONS',null,'Y',row_limit);
3002
3003 IF (dummy_num = row_limit) THEN
3004 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3005 END IF;
3006
3007 sqltxt :=
3008 'select '||
3009 ' transaction_temp_id, '||
3010 ' vendor_serial_number, '||
3011 ' vendor_lot_number, '||
3012 ' fm_serial_number, '||
3013 ' to_serial_number, '||
3014 ' serial_prefix, '||
3015 ' group_header_id, '||
3016 ' parent_serial_number, '||
3017 ' end_item_unit_number, '||
3018 ' request_id, '||
3019 ' creation_date, '||
3020 ' error_code '||
3021 'from mtl_serial_numbers_temp msnt '||
3022 'where msnt.transaction_temp_id in '||
3023 ' (select mmtt.transaction_temp_id '||
3024 ' from mtl_material_transactions_temp mmtt '||
3025 ' where mmtt.transaction_source_type_id = 5 ';
3026
3027 if p_wip_entity_id is not null then
3028 sqltxt :=sqltxt||' and mmtt.transaction_source_id = '|| p_wip_entity_id ||')' ;
3029 sqltxt :=sqltxt||' order by transaction_temp_id, fm_serial_number ';
3030 end if;
3031
3032 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3033 'MSNT TRANSACTIONS',null,'Y',row_limit);
3034
3035 IF (dummy_num = row_limit) THEN
3036 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3037 END IF;
3038
3039 sqltxt :=
3040 'select DISTINCT '||
3041 ' TRL.LINE_ID MOVE_LINE_ID, '||
3042 ' TRL.REQUEST_NUMBER MOVE_NUMBER, '||
3043 ' TRL.HEADER_ID MV_HDR_ID, '||
3044 ' TRL.LINE_NUMBER MV_LINE_NUM, '||
3045 ' decode(TRL.LINE_STATUS, '||
3046 ' 1, ''Incomplete'', '||
3047 ' 2, ''Pend Aprvl'', '||
3048 ' 3, ''Approved'', '||
3049 ' 4, ''Not Apprvd'', '||
3050 ' 5, ''Closed'', '||
3051 ' 6, ''Canceled'', '||
3052 ' 7, ''Pre Apprvd'', '||
3053 ' 8, ''Part Aprvd'') MV_LINE_STAT, '||
3054 ' TRL.INVENTORY_ITEM_ID, '||
3055 ' TRL.ORGANIZATION_ID, '||
3056 ' TRL.REVISION, '||
3057 ' TRL.QUANTITY QTY, '||
3058 ' TRL.PRIMARY_QUANTITY PRM_QTY, '||
3059 ' TRL.QUANTITY_DELIVERED DLVD_QTY, '||
3060 ' TRL.QUANTITY_DETAILED DTLD_QTY, '||
3061 ' TRL.MOVE_ORDER_TYPE_NAME MOVE_TYPE_NAME, '||
3062 ' decode(TRL.TRANSACTION_SOURCE_TYPE_ID,2,''Sales Order'', '||
3063 ' 5,''Job or Schedule'', '||
3064 ' 13,''Inventory'', '||
3065 ' TRL.TRANSACTION_SOURCE_TYPE_ID) txn_source_meaning, '||
3066 ' TRL.TRANSACTION_TYPE_NAME transaction_type_meaning, '||
3067 ' decode(TRL.TRANSACTION_ACTION_ID, '||
3068 ' 1, ''Issue'', '||
3069 ' 2, ''Subinv Xfr'', '||
3070 ' 3, ''Org Xfr'', '||
3071 ' 4, ''Cycle Count Adj'', '||
3072 ' 5, ''Plan Xfr'', '||
3073 ' 21, ''Intransit Shpmt'', '||
3074 ' 24, ''Cost Update'', '||
3075 ' 27, ''Receipt'', '||
3076 ' 28, ''Stg Xfr'', '||
3077 ' 30, ''Wip scrap'', '||
3078 ' 31, ''Assy Complete'', '||
3079 ' 32, ''Assy return'', '||
3080 ' 33, ''-ve CompIssue'', '||
3081 ' 34, ''-ve CompReturn'', '||
3082 ' 40, ''Inv Lot Split'', '||
3083 ' 41, ''Inv Lot Merge'', '||
3084 ' 42, ''Inv Lot Translate'', '||
3085 ' 42, ''Inv Lot Translate'', '||
3086 ' trl.transaction_action_id) txn_action_meaning, '||
3087 ' TRL.FROM_SUBINVENTORY_CODE FROM_SUB, '||
3088 ' TRL.FROM_LOCATOR_ID FROM_LOC_ID, '||
3089 ' TRL.TO_SUBINVENTORY_CODE TO_SUB, '||
3090 ' TRL.TO_LOCATOR_ID TO_LOC_ID, '||
3091 ' TRL.LOT_NUMBER LOT_NUM, '||
3092 ' TRL.TRANSACTION_HEADER_ID TRNS_HEAD_ID, '||
3093 ' TRL.CREATION_DATE '||
3094 'from MTL_TXN_REQUEST_LINES_V TRL '||
3095 'WHERE trl.move_order_type <> 6 '||
3096 'AND (trl.txn_source_id, trl.txn_source_line_id) in '||
3097 ' (select wdj.wip_entity_id, wro.operation_seq_num '||
3098 ' from wip_discrete_jobs wdj, '||
3099 ' wip_entities we, '||
3100 ' wip_lines wl, '||
3101 ' wip_requirement_operations wro '||
3102 ' where wdj.wip_entity_id = we.wip_entity_id '||
3103 ' and wdj.organization_id = we.organization_id '||
3104 ' and wdj.wip_entity_id = wro.wip_entity_id '||
3105 ' and wdj.organization_id = wro.organization_id '||
3106 ' and wdj.line_id = wl.line_id(+) '||
3107 ' and wdj.organization_id = wl.organization_id(+) ';
3108
3109 if p_wip_entity_id is not null then
3110 sqltxt :=sqltxt||' and we.wip_entity_id = '|| p_wip_entity_id ||')' ;
3111 sqltxt :=sqltxt||' order by request_number ';
3112 end if;
3113
3114 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3118 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3115 'MTL_TXN_REQUEST_LINES_V TRANSACTIONS - MOVE ORDERS',null,'Y',row_limit);
3116
3117 IF (dummy_num = row_limit) THEN
3119 END IF;
3120
3121 sqltxt :=
3122 'select mmt.transaction_id, '||
3123 ' mmt.transaction_date, '||
3124 ' mmt.transaction_type_id, '||
3125 ' decode(mmt.transaction_action_id, '||
3126 ' 1, ''Issue'', '||
3127 ' 2, ''Subinv Xfr'', '||
3128 ' 3, ''Org Xfr'', '||
3129 ' 4, ''Cycle Count Adj'', '||
3130 ' 5, ''Issue'', '||
3131 ' 21, ''Intransit Shpmt'', '||
3132 ' 24, ''Cost Update'', '||
3133 ' 27, ''Receipt'', '||
3134 ' 28, ''Stg Xfr'', '||
3135 ' 30, ''Wip scrap'', '||
3136 ' 31, ''Assy Complete'', '||
3137 ' 32, ''Assy return'', '||
3138 ' 33, ''-ve CompIssue'', '||
3139 ' 34, ''-ve CompReturn'', '||
3140 ' 40, ''Inv Lot Split'', '||
3141 ' 41, ''Inv Lot Merge'', '||
3142 ' 42, ''Inv Lot Translate'', '||
3143 ' 42, ''Inv Lot Translate'', '||
3144 ' mmt.transaction_action_id) txn_action_meaning, '||
3145 ' decode(mmt.transaction_source_type_id, '||
3146 ' 1, ''PO'', '||
3147 ' 2, ''SO'', '||
3148 ' 4, ''MoveOrder'', '||
3149 ' 5, ''WIP'', '||
3150 ' 6, ''AcctAlias'', '||
3151 ' 7, ''Int REQ'', '||
3152 ' 8, ''Int Order'', '||
3153 ' 9, ''CycleCount'', '||
3154 ' 10,''PhyCount'', '||
3155 ' 11,''StdCostUpd'', '||
3156 ' 12, ''RMA'', '||
3157 ' 13, ''INV'', '||
3158 ' 17, ''Ext REQ'', '||
3159 ' mmt.transaction_source_type_id) txn_source_meaning, '||
3160 ' mmt.transaction_source_id, '||
3161 ' mmt.inventory_item_id, '||
3162 ' mmt.organization_id, '||
3163 ' mmt.subinventory_code, '||
3164 ' mmt.locator_id, '||
3165 ' mmt.revision, '||
3166 ' mmt.transaction_quantity, '||
3167 ' mmta.transaction_quantity Allocation_Txn_Qty, ' ||
3168 ' mmt.transaction_uom, '||
3169 ' mmt.primary_quantity, '||
3170 ' mmta.primary_quantity Allocation_Primary_Qty, ' ||
3171 ' mmt.trx_source_line_id, '||
3172 ' mmt.trx_source_delivery_id, '||
3173 ' mmt.move_transaction_id, '||
3174 ' mmt.completion_transaction_id, '||
3175 ' mmt.source_code, '||
3176 ' mmt.source_line_id, '||
3177 ' mmt.transfer_organization_id, '||
3178 ' mmt.transfer_subinventory, '||
3179 ' mmt.transfer_locator_id, '||
3180 ' mmt.move_order_line_id, '||
3181 ' mmt.reservation_id, '||
3182 ' mmt.creation_date, '||
3183 ' mmt.last_update_date, '||
3184 ' mmt.error_code '||
3185 ' from mtl_material_transactions mmt, '||
3186 ' mtl_material_txn_allocations mmta ' ||
3187 ' where mmt.transaction_id = mmta.transaction_id ' ||
3188 ' and mmt.transaction_source_type_id = 5 ' ;
3189
3190 if p_wip_entity_id is not null then
3191 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ||
3192 ' and mmta.repetitive_schedule_id = ' || p_rep_schedule_id ;
3193 sqltxt :=sqltxt||' order by transaction_id ';
3194 end if;
3195
3196 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3197 'MMT TRANSACTIONS AND ALLOCATIONS',null,'Y',row_limit);
3198
3199 IF (dummy_num = row_limit) THEN
3200 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3201 END IF;
3202
3203 sqltxt :=
3204 'select '||
3205 ' inventory_item_id, '||
3206 ' lot_number, '||
3207 ' organization_id, '||
3208 ' transaction_id, '||
3209 ' transaction_date, '||
3210 ' creation_date, '||
3211 ' transaction_source_id, '||
3212 ' decode(transaction_source_type_id, '||
3213 ' 1, ''PO'', '||
3214 ' 2, ''SO'', '||
3215 ' 4, ''MoveOrder'', '||
3216 ' 5, ''WIP'', '||
3217 ' 6, ''AcctAlias'', '||
3218 ' 7, ''Int REQ'', '||
3219 ' 8, ''Int Order'', '||
3220 ' 9, ''CycleCount'', '||
3221 ' 10,''PhyCount'', '||
3222 ' 11,''StdCostUpd'', '||
3223 ' 12, ''RMA'', '||
3224 ' 13, ''INV'', '||
3225 ' 17, ''Ext REQ'', '||
3226 ' transaction_source_type_id) txn_source_meaning, '||
3227 ' transaction_quantity, '||
3228 ' primary_quantity, '||
3229 ' serial_transaction_id '||
3230 'from mtl_transaction_lot_numbers mtln '||
3231 'where mtln.transaction_id in '||
3232 ' (select mmt.transaction_id '||
3233 ' from mtl_material_transactions mmt '||
3234 ' where mmt.transaction_source_type_id = 5 ';
3235
3236 if p_wip_entity_id is not null then
3237 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ||')';
3238 sqltxt :=sqltxt||' order by inventory_item_id, lot_number ';
3239 end if;
3240
3241 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3242 'MTLN TRANSACTIONS',null,'Y',row_limit);
3243
3244 IF (dummy_num = row_limit) THEN
3245 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3246 END IF;
3247
3248 /*
3249 sqltxt :=
3250 'select '||
3251 ' inventory_item_id, '||
3252 ' serial_number, '||
3253 ' decode(current_status, '||
3254 ' 1, ''Defined but not used'', '||
3255 ' 3, ''Resides in stores'', '||
3256 ' 4, ''Issued out of stores'', '||
3257 ' 5, ''Resides in intrasit'', '||
3261 ' parent_item_id, '||
3258 ' current_status) current_status_meaning, '||
3259 ' revision, '||
3260 ' lot_number, '||
3262 ' last_transaction_id, '||
3263 ' parent_serial_number, '||
3264 ' end_item_unit_number, '||
3265 ' group_mark_id, '||
3266 ' line_mark_id, '||
3267 ' lot_line_mark_id, '||
3268 ' gen_object_id, '||
3269 ' creation_date '||
3270 'from mtl_serial_numbers msn ';
3271
3272 if p_wip_entity_id is not null then
3273 sqltxt :=sqltxt||' where msn.wip_entity_id = '|| p_wip_entity_id ;
3274 sqltxt :=sqltxt||' order by inventory_item_id, serial_number ';
3275 end if;
3276
3277 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3278 'MSN TRANSACTIONS',null,'Y',row_limit);
3279
3280 IF (dummy_num = row_limit) THEN
3281 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3282 END IF;
3283
3284 */
3285
3286 sqltxt :=
3287 ' select mmt.inventory_item_id, ' ||
3288 ' mmt.transaction_id,' ||
3289 ' mmt.transaction_date,' ||
3290 ' mmt.transaction_source_id,' ||
3291 ' mut.serial_number,' ||
3292 ' mmt.subinventory_code,' ||
3293 ' mmt.locator_id , ' ||
3294 ' mmt.creation_date' ||
3295 ' from mtl_material_transactions mmt,' ||
3296 ' mtl_material_txn_allocations mmta, ' ||
3297 ' mtl_unit_transactions mut' ||
3298 ' where mmt.transaction_id = mmta.transaction_id ' ||
3299 ' and mmt.transaction_action_id in (1, 27, 33, 34, 30, 31, 32)' ||
3300 ' and mmt.transaction_source_type_id = 5' ||
3301 ' and mut.transaction_id = mmt.transaction_id' ;
3302
3303 if p_wip_entity_id is not null then
3304 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ||
3305 ' and mmta.repetitive_schedule_id = ' || p_rep_schedule_id ;
3306 sqltxt :=sqltxt||' order by mmt.inventory_item_id, mut.serial_number ';
3307 end if ;
3308
3309
3310 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3311 'MSN TRANSACTIONS',null,'Y',row_limit);
3312
3313 IF (dummy_num = row_limit) THEN
3314 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3315 END IF;
3316
3317 /*
3318 sqltxt :=
3319 'select header_id, '||
3320 ' source_id, '||
3321 ' source_code, '||
3322 ' completion_status, '||
3323 ' creation_date, '||
3324 ' last_update_date, '||
3325 ' inventory_item_id, '||
3326 ' organization_id, '||
3327 ' primary_quantity, '||
3328 ' transaction_quantity, '||
3329 ' transaction_uom, '||
3330 ' transaction_date, '||
3331 ' transaction_action_id, '||
3332 ' transaction_source_id, '||
3333 ' transaction_source_type_id, '||
3334 ' transaction_type_id, '||
3335 ' transaction_mode, '||
3336 ' acct_period_id, '||
3337 ' subinventory_code, '||
3338 ' locator_id, '||
3339 ' schedule_id, '||
3340 ' repetitive_line_id, '||
3341 ' operation_seq_num, '||
3342 ' cost_group_id, '||
3343 ' lock_flag, '||
3344 ' error_code, '||
3345 ' final_completion_flag, '||
3346 ' completion_transaction_id '||
3347 'from wip_lpn_completions ';
3348
3349
3350 if p_wip_entity_id is not null then
3351 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ;
3352 sqltxt :=sqltxt||' order by header_id ';
3353 end if;
3354
3355 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3356 'WIP LPN COMPLETIONS',null,'Y',row_limit);
3357
3358 IF (dummy_num = row_limit) THEN
3359 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3360 END IF;
3361
3362 */
3363
3364
3365 sqltxt :=
3366 'select '||
3367 ' RES.RESERVATION_ID RESERV_ID, '||
3368 ' decode(RES.SHIP_READY_FLAG,1,''1=Released'',2,''2=Submitted'',to_char(RES.SHIP_READY_FLAG)) '||
3369 ' SHIP_READY, '||
3370 ' RES.DEMAND_SOURCE_HEADER_ID DS_HEADER_ID, '||
3371 ' RES.DEMAND_SOURCE_LINE_ID DS_LINE_ID, '||
3372 ' RES.DEMAND_SOURCE_DELIVERY DS_DELIVERY, '||
3373 ' RES.INVENTORY_ITEM_ID ITEM_ID, '||
3374 ' RES.RESERVATION_QUANTITY RES_QTY, '||
3375 ' RES.RESERVATION_UOM_CODE RUOM, '||
3376 ' RES.PRIMARY_RESERVATION_QUANTITY PRES_QTY, '||
3377 ' RES.PRIMARY_UOM_CODE PUOM, '||
3378 ' RES.DETAILED_QUANTITY DET_QTY, '||
3379 ' RES.REQUIREMENT_DATE REQUIRD_DATE, '||
3380 ' RES.DEMAND_SOURCE_TYPE_ID DS_TYPE, '||
3381 ' RES.ORGANIZATION_ID ORG_ID, '||
3382 ' RES.SUBINVENTORY_CODE SUBINV, '||
3383 ' RES.LOT_NUMBER LOT, '||
3384 ' RES.REVISION REV, '||
3385 ' RES.LOCATOR_ID LOC_ID, '||
3386 ' RES.SERIAL_NUMBER SERIAL_NUM, '||
3387 ' decode(RES.SUPPLY_SOURCE_TYPE_ID,1,''1=PO'', '||
3388 ' 2,''2=OE'', '||
3389 ' 5,''5=WIP DJ'', '||
3390 ' 7,''7=INT_REQ'', '||
3391 ' 8,''8=INT_OE'', '||
3392 ' 13,''13=INV'', '||
3396 ' We.WIP_ENTITY_ID WIP_ID, '||
3393 ' 17,''17=REQ'', '||
3394 ' RES.SUPPLY_SOURCE_TYPE_ID) '||
3395 ' SS_TYPE_ID, '||
3397 ' decode(JOB.STATUS_TYPE, 1, ''Unreleased'', '||
3398 ' 2, ''Simulated'', '||
3399 ' 3, ''Released'', '||
3400 ' 4, ''Complete'', '||
3401 ' 5, ''Complete-NoCharges'', '||
3402 ' 6, ''OnHold'', '||
3403 ' 7, ''Canceled'', '||
3404 ' 8, ''Pending Bill Load'', '||
3405 ' 9, ''Failed Bill Load'', '||
3406 ' 10, ''Pending Routing Load'', '||
3407 ' 11, ''Failed Routing Load'', '||
3408 ' 12, ''Closed'', '||
3409 ' 13, ''Pending-Mass Load'', '||
3410 ' 14, ''Pending Close'', '||
3411 ' 15, ''Failed Close'', '||
3412 ' 16, ''Pending Scheduling'', '||
3413 ' 17, ''Draft'', '||
3414 ' JOB.STATUS_TYPE ) JOB_STATUS, '||
3415 ' JOB.SOURCE_CODE SOURCE_CODE, '||
3416 ' RES.SUPPLY_SOURCE_HEADER_ID SS_HEADER_ID, '||
3417 ' RES.SUPPLY_SOURCE_LINE_DETAIL SS_SOURCE_LINE_DET, '||
3418 ' RES.SUPPLY_SOURCE_LINE_ID SS_SOURCE_LINE, '||
3419 ' RES.PARTIAL_QUANTITIES_ALLOWED ALLOW_PART, '||
3420 ' to_char(RES.CREATION_DATE, ''DD-MON HH24:MI:SS'') CREATE_DATE, '||
3421 ' to_char(RES.LAST_UPDATE_DATE, ''DD-MON HH24:MI:SS'') UPD_DATE '||
3422 'from '||
3423 ' MTL_RESERVATIONS RES, '||
3424 ' WIP_ENTITIES WE, '||
3425 ' WIP_DISCRETE_JOBS JOB '||
3426 'where RES.SUPPLY_SOURCE_HEADER_ID = We.WIP_ENTITY_ID '||
3427 'and We.WIP_ENTITY_ID = JOB.WIP_ENTITY_ID ';
3428
3429 if p_wip_entity_id is not null then
3430 sqltxt :=sqltxt||' and we.wip_entity_id = '|| p_wip_entity_id ;
3431 sqltxt :=sqltxt||' order by reservation_id ';
3432 end if;
3433
3434 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3435 'MTL RESERVATIONS',null,'Y',row_limit);
3436
3437 IF (dummy_num = row_limit) THEN
3438 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3439 END IF;
3440
3441 sqltxt :=
3442 'Select '||
3443 'RQI.INTERFACE_SOURCE_LINE_ID wip_entity_id, '||
3444 'RQI.INTERFACE_SOURCE_CODE SRC_CODE, '||
3445 'RQI.AUTHORIZATION_STATUS AUTH_STATUS, '||
3446 'RQI.DELIVER_TO_LOCATION_ID DELIV_LOC, '||
3447 'RQI.PREPARER_ID PREPARER, '||
3448 'RQI.DESTINATION_ORGANIZATION_ID DEST_ORG_ID, '||
3449 'RQI.DESTINATION_TYPE_CODE DEST_TYPE, '||
3450 'RQI.SOURCE_TYPE_CODE SRC_TYPE_CODE, '||
3451 'RQI.ITEM_ID ITEM_ID, '||
3452 'RQI.NEED_BY_DATE NEED_BY, '||
3453 'RQI.QUANTITY QTY, '||
3454 'RQI.UNIT_PRICE PRICE '||
3455 'from '||
3456 ' PO_REQUISITIONS_INTERFACE_ALL RQI '||
3457 'where RQI.INTERFACE_SOURCE_CODE =''WIP'' ';
3458
3459
3460 if p_wip_entity_id is not null then
3461 sqltxt :=sqltxt||' and rqi.interface_source_line_id = '|| p_wip_entity_id ;
3462 end if;
3463
3464 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3465 'PO REQUISITION INTERFACE',null,'Y',row_limit);
3466
3467 IF (dummy_num = row_limit) THEN
3468 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3469 END IF;
3470
3471 sqltxt :=
3472 'select '||
3473 ' POE.INTERFACE_TRANSACTION_ID INTF_TRANS_ID, '||
3474 ' POE.COLUMN_NAME COLUMN_NAME, '||
3475 ' POE.ERROR_MESSAGE ERROR, '||
3476 ' POE.INTERFACE_TYPE INTF_TYPE, '||
3477 ' POE.REQUEST_ID REQUEST_ID, '||
3478 ' POE.TABLE_NAME TABLE_NAME '||
3479 'from '||
3480 ' PO_INTERFACE_ERRORS POE, '||
3481 ' PO_REQUISITIONS_INTERFACE_ALL RQI '||
3482 'where RQI.TRANSACTION_ID = POE.INTERFACE_TRANSACTION_ID ';
3483
3484
3485 if p_wip_entity_id is not null then
3486 sqltxt :=sqltxt||' and rqi.interface_source_line_id = '|| p_wip_entity_id ;
3487 end if;
3488
3489 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3490 'PO INTERFACE ERRORS',null,'Y',row_limit);
3491
3492 IF (dummy_num = row_limit) THEN
3493 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3494 END IF;
3495
3496 sqltxt :=
3497 'select '||
3498 ' RQH.REQUISITION_HEADER_ID REQ_HEADER_ID , '||
3499 ' RQH.SEGMENT1 REQ_NUMBER, '||
3500 ' RQL.REQUISITION_LINE_ID REQ_LINE_ID, '||
3501 ' RQL.LINE_NUM REQ_LINE, '||
3502 ' RQH.INTERFACE_SOURCE_LINE_ID INT_SRC_LINE_ID, '||
3506 ' RQH.INTERFACE_SOURCE_CODE SRC_CODE, '||
3503 ' RQL.WIP_ENTITY_ID WIP_ENTITY_ID, '||
3504 ' RQH.AUTHORIZATION_STATUS AUTH_STATUS, '||
3505 ' RQH.ENABLED_FLAG ENABLED, '||
3507 ' RQH.SUMMARY_FLAG SUMMARY, '||
3508 ' RQH.TRANSFERRED_TO_OE_FLAG XFR_OE_FLAG, '||
3509 ' RQH.TYPE_LOOKUP_CODE REQ_TYPE, '||
3510 ' RQH.WF_ITEM_TYPE ITEM_TYPE, '||
3511 ' RQH.WF_ITEM_KEY ITEM_KEY, '||
3512 ' RQL.ITEM_ID ITEM_ID, '||
3513 ' RQL.UNIT_MEAS_LOOKUP_CODE UOM, '||
3514 ' RQL.UNIT_PRICE PRICE, '||
3515 ' RQL.QUANTITY QTY, '||
3516 ' RQL.QUANTITY_CANCELLED QTY_CNC, '||
3517 ' RQL.QUANTITY_DELIVERED QTY_DLV, '||
3518 ' RQL.CANCEL_FLAG CANC, '||
3519 ' RQL.DESTINATION_CONTEXT DEST_TYPE, '||
3520 ' RQL.DESTINATION_ORGANIZATION_ID DEST_ORG, '||
3521 ' RQL.ENCUMBERED_FLAG ENC_FLAG , '||
3522 ' RQL.LINE_TYPE_ID LINE_TYPE_ID, '||
3523 ' RQL.NEED_BY_DATE NEED_BY, '||
3524 ' RQL.ON_RFQ_FLAG RFQ , '||
3525 ' RQL.SOURCE_TYPE_CODE SRC_TYPE_CODE, '||
3526 ' RQL.SUGGESTED_BUYER_ID BUYER_ID '||
3527 'from '||
3528 ' PO_REQUISITION_HEADERS_ALL RQH, '||
3529 ' PO_REQUISITION_LINES_ALL RQL '||
3530 'where '||
3531 ' RQH.REQUISITION_HEADER_ID = RQL.REQUISITION_HEADER_ID ';
3532
3533
3534 if p_wip_entity_id is not null then
3535 sqltxt :=sqltxt||' and rql.wip_entity_id = '|| p_wip_entity_id ;
3536 sqltxt :=sqltxt||' order by RQH.REQUISITION_HEADER_ID, RQL.REQUISITION_LINE_ID, RQL.ITEM_ID';
3537 end if;
3538
3539 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3540 'PO REQUISITION DETAILS',null,'Y',row_limit);
3541
3542 IF (dummy_num = row_limit) THEN
3543 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3544 END IF;
3545
3546 sqltxt :=
3547 'select '||
3548 ' WFS.item_key REQ_NUM_IK, '||
3549 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
3550 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
3551 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
3552 ' LKP.MEANING ACT_STATUS, '||
3553 ' WFS.NOTIFICATION_ID NOTIF_ID, '||
3554 ' WFS.BEGIN_DATE, '||
3555 ' WFS.END_DATE, '||
3556 ' WFS.ERROR_NAME ERROR '||
3557 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
3558 ' WF_PROCESS_ACTIVITIES WFP, '||
3559 ' WF_ACTIVITIES_VL WFA, '||
3560 ' WF_ACTIVITIES_VL WFA1, '||
3561 ' WF_LOOKUPS LKP '||
3562 'where '||
3563 ' WFS.ITEM_TYPE = ''REQAPPRV'' '||
3564 'and WFS.item_key in (select wf_item_key '||
3565 ' from '||
3566 ' PO_REQUISITION_HEADERS_ALL RQH, '||
3567 ' PO_REQUISITION_LINES_ALL RQL '||
3568 ' where '||
3569 ' RQH.REQUISITION_HEADER_ID = RQL.REQUISITION_HEADER_ID ';
3570
3571 if p_wip_entity_id is not null then
3572 sqltxt :=sqltxt||' and rql.wip_entity_id = '|| p_wip_entity_id ||') ';
3573 end if;
3574
3575 sqltxt :=sqltxt||
3576 ' and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
3577 ' and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
3578 ' and WFP.PROCESS_NAME = WFA.NAME '||
3579 ' and WFP.PROCESS_VERSION = WFA.VERSION '||
3580 ' and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
3581 ' and WFP.ACTIVITY_NAME = WFA1.NAME '||
3582 ' and WFA1.VERSION = '||
3583 ' (select max(VERSION) '||
3584 ' from WF_ACTIVITIES WF2 '||
3585 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
3586 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
3587 ' and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
3588 ' and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS ';
3589
3590 sqltxt :=sqltxt||' order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME';
3591
3592 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3593 'WORKFLOW REQUISITION APPROVAL STATUS',null,'Y',row_limit);
3594
3595 IF (dummy_num = row_limit) THEN
3596 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3597 END IF;
3598
3599 sqltxt :=
3600 'select '||
3601 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
3602 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
3603 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
3604 ' LKP.MEANING ACT_STATUS, '||
3605 ' WFS.ERROR_NAME ERROR_NAME, '||
3606 ' WFS.ERROR_MESSAGE ERROR_MESSAGE, '||
3607 ' WFS.ERROR_STACK ERROR_STACK '||
3608 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
3609 ' WF_PROCESS_ACTIVITIES WFP, '||
3610 ' WF_ACTIVITIES_VL WFA, '||
3611 ' WF_ACTIVITIES_VL WFA1, '||
3612 ' WF_LOOKUPS LKP '||
3613 'where '||
3614 ' WFS.ITEM_TYPE = ''REQAPPRV'' '||
3615 'and WFS.item_key in (select wf_item_key '||
3616 ' from '||
3620 ' RQH.REQUISITION_HEADER_ID = RQL.REQUISITION_HEADER_ID ';
3617 ' PO_REQUISITION_HEADERS_ALL RQH, '||
3618 ' PO_REQUISITION_LINES_ALL RQL '||
3619 ' where '||
3621
3622 if p_wip_entity_id is not null then
3623 sqltxt :=sqltxt||' and rql.wip_entity_id = '|| p_wip_entity_id ||') ';
3624 end if;
3625
3626 sqltxt := sqltxt ||
3627 ' and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
3628 ' and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
3629 ' and WFP.PROCESS_NAME = WFA.NAME '||
3630 ' and WFP.PROCESS_VERSION = WFA.VERSION '||
3631 ' and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
3632 ' and WFP.ACTIVITY_NAME = WFA1.NAME '||
3633 ' and WFA1.VERSION = '||
3634 ' (select max(VERSION) '||
3635 ' from WF_ACTIVITIES WF2 '||
3636 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
3637 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
3638 ' and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
3639 ' and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS '||
3640 ' and WFS.ERROR_NAME is not NULL '||
3641 ' order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME ';
3642
3643 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3644 'WORKFLOW REQUISITION APPROVAL ERRORS',null,'Y',row_limit);
3645
3646 IF (dummy_num = row_limit) THEN
3647 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3648 END IF;
3649
3650 sqltxt :=
3651 'select '||
3652 ' POH.PO_HEADER_ID PO_HEADER_ID, '||
3653 ' POH.SEGMENT1 PO_NUM, '||
3654 ' POL.PO_LINE_ID PO_LINE_ID, '||
3655 ' POL.LINE_NUM PO_LINE, '||
3656 ' POL.LINE_TYPE_ID LINE_TYPE_ID, '||
3657 ' POL.ITEM_ID ITEM_ID, '||
3658 ' POL.QUANTITY QTY, '||
3659 ' POL.UNIT_PRICE PRICE, '||
3660 ' POH.ACCEPTANCE_REQUIRED_FLAG ACCEPT_REQD, '||
3661 ' POH.BILL_TO_LOCATION_ID BILL_TO, '||
3662 ' POH.SHIP_TO_LOCATION_ID SHIP_TO, '||
3663 ' POH.CLOSED_CODE CLS_STAT, '||
3664 ' POH.CONFIRMING_ORDER_FLAG CONF_ORD, '||
3665 ' POH.CURRENCY_CODE CURR, '||
3666 ' POH.ENABLED_FLAG ENABLED, '||
3667 ' POH.FROZEN_FLAG FROZEN, '||
3668 ' POH.SUMMARY_FLAG SUMM, '||
3669 ' POH.TYPE_LOOKUP_CODE TYPE, '||
3670 ' POH.VENDOR_CONTACT_ID VEND_CNCACT, '||
3671 ' POH.VENDOR_ID VEND_ID, '||
3672 ' POH.VENDOR_SITE_ID VEND_SITE, '||
3673 ' POH.WF_ITEM_TYPE ITEM_TYPE, '||
3674 ' POH.WF_ITEM_KEY ITEM_KEY , '||
3675 ' POL.CATEGORY_ID CATEGORY_ID, '||
3676 ' POL.CLOSED_CODE CLS_STAT, '||
3677 ' POL.FIRM_STATUS_LOOKUP_CODE FIRM '||
3678 'from '||
3679 ' PO_HEADERS_ALL POH, '||
3680 ' PO_LINES_ALL POL, '||
3681 ' PO_LINE_LOCATIONS_ALL LL, '||
3682 ' PO_REQUISITION_LINES_ALL PRL, '||
3683 ' PO_REQUISITION_HEADERS_ALL PRH '||
3684 'where PRH.requisition_header_id = PRL.requisition_header_id '||
3685 'and PRL.line_location_id = LL.line_location_id '||
3686 'and LL.PO_HEADER_ID = POH.PO_HEADER_ID '||
3687 'and POL.PO_HEADER_ID = POH.PO_HEADER_ID ';
3688
3689 if p_wip_entity_id is not null then
3690 sqltxt :=sqltxt||' and prl.wip_entity_id = '|| p_wip_entity_id ;
3691 sqltxt :=sqltxt||' order by poh.po_header_id, pol.po_line_id ';
3692 end if;
3693
3694 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3695 'PO DETAILS',null,'Y',row_limit);
3696
3697 IF (dummy_num = row_limit) THEN
3698 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3699 END IF;
3700
3701 sqltxt :=
3702 'select '||
3703 ' WFS.item_key PO_NUM_IK, '||
3704 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
3705 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
3706 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
3707 ' LKP.MEANING ACT_STATUS, '||
3708 ' WFS.NOTIFICATION_ID NOTIF_ID, '||
3709 ' WFS.BEGIN_DATE, '||
3710 ' WFS.END_DATE, '||
3711 ' WFS.ERROR_NAME ERROR '||
3712 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
3713 ' WF_PROCESS_ACTIVITIES WFP, '||
3714 ' WF_ACTIVITIES_VL WFA, '||
3715 ' WF_ACTIVITIES_VL WFA1, '||
3716 ' WF_LOOKUPS LKP '||
3717 'where '||
3718 ' WFS.ITEM_TYPE = ''POAPPRV'' '||
3719 'and WFS.item_key in (select poh.wf_item_key '||
3720 ' from '||
3721 ' PO_HEADERS_ALL POH, '||
3722 ' PO_LINES_ALL POL, '||
3723 ' PO_LINE_LOCATIONS_ALL LL, '||
3724 ' PO_REQUISITION_LINES_ALL PRL, '||
3725 ' PO_REQUISITION_HEADERS_ALL PRH '||
3726 ' where PRH.requisition_header_id = PRL.requisition_header_id ';
3727 if p_wip_entity_id is not null then
3728 sqltxt :=sqltxt||' and prl.wip_entity_id = '|| p_wip_entity_id ;
3729 end if;
3730
3731 sqltxt := sqltxt ||
3732 ' and PRL.line_location_id = LL.line_location_id '||
3733 ' and LL.PO_HEADER_ID = POH.PO_HEADER_ID '||
3737 'and WFP.PROCESS_NAME = WFA.NAME '||
3734 ' and POL.PO_HEADER_ID = POH.PO_HEADER_ID) '||
3735 'and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
3736 'and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
3738 'and WFP.PROCESS_VERSION = WFA.VERSION '||
3739 'and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
3740 'and WFP.ACTIVITY_NAME = WFA1.NAME '||
3741 'and WFA1.VERSION = '||
3742 ' (select max(VERSION) '||
3743 ' from WF_ACTIVITIES WF2 '||
3744 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
3745 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
3746 'and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
3747 'and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS '||
3748 'order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME ';
3749
3750 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3751 'WORKFLOW PURCHASE ORDER APPROVAL STATUS',null,'Y',row_limit);
3752
3753 IF (dummy_num = row_limit) THEN
3754 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3755 END IF;
3756
3757 sqltxt :=
3758 'select '||
3759 ' WFA.DISPLAY_NAME PROCESS_NAME, '||
3760 ' WFA1.DISPLAY_NAME ACTIVITY_NAME, '||
3761 ' WF_CORE.ACTIVITY_RESULT(WFA1.RESULT_TYPE,WFS.ACTIVITY_RESULT_CODE) RESULT, '||
3762 ' LKP.MEANING ACT_STATUS, '||
3763 ' WFS.ERROR_NAME ERROR_NAME, '||
3764 ' WFS.ERROR_MESSAGE ERROR_MESSAGE, '||
3765 ' WFS.ERROR_STACK ERROR_STACK '||
3766 'from WF_ITEM_ACTIVITY_STATUSES WFS, '||
3767 ' WF_PROCESS_ACTIVITIES WFP, '||
3768 ' WF_ACTIVITIES_VL WFA, '||
3769 ' WF_ACTIVITIES_VL WFA1, '||
3770 ' WF_LOOKUPS LKP '||
3771 'where '||
3772 ' WFS.ITEM_TYPE = ''POAPPRV'' '||
3773 'and WFS.item_key in (select poh.wf_item_key '||
3774 ' from '||
3775 ' PO_HEADERS_ALL POH, '||
3776 ' PO_LINES_ALL POL, '||
3777 ' PO_LINE_LOCATIONS_ALL LL, '||
3778 ' PO_REQUISITION_LINES_ALL PRL, '||
3779 ' PO_REQUISITION_HEADERS_ALL PRH '||
3780 ' where PRH.requisition_header_id = PRL.requisition_header_id ';
3781
3782 if p_wip_entity_id is not null then
3783 sqltxt :=sqltxt||' and prl.wip_entity_id = '|| p_wip_entity_id ;
3784 end if;
3785
3786 sqltxt := sqltxt ||
3787 ' and PRL.line_location_id = LL.line_location_id '||
3788 ' and LL.PO_HEADER_ID = POH.PO_HEADER_ID '||
3789 ' and POL.PO_HEADER_ID = POH.PO_HEADER_ID) '||
3790 'and WFS.PROCESS_ACTIVITY = WFP.INSTANCE_ID '||
3791 'and WFP.PROCESS_ITEM_TYPE = WFA.ITEM_TYPE '||
3792 'and WFP.PROCESS_NAME = WFA.NAME '||
3793 'and WFP.PROCESS_VERSION = WFA.VERSION '||
3794 'and WFP.ACTIVITY_ITEM_TYPE = WFA1.ITEM_TYPE '||
3795 'and WFP.ACTIVITY_NAME = WFA1.NAME '||
3796 'and WFA1.VERSION = '||
3797 ' (select max(VERSION) '||
3798 ' from WF_ACTIVITIES WF2 '||
3799 ' where WF2.ITEM_TYPE = WFP.ACTIVITY_ITEM_TYPE '||
3800 ' and WF2.NAME = WFP.ACTIVITY_NAME) '||
3801 'and LKP.LOOKUP_TYPE = ''WFENG_STATUS'' '||
3802 'and LKP.LOOKUP_CODE = WFS.ACTIVITY_STATUS '||
3803 'and WFS.ERROR_NAME is not NULL '||
3804 'order by WFS.ITEM_KEY, WFS.BEGIN_DATE, EXECUTION_TIME ';
3805
3806
3807 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3808 'WORKFLOW PURCHASE APPROVAL ERRORS ',null,'Y',row_limit);
3809
3810 IF (dummy_num = row_limit) THEN
3811 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3812 END IF;
3813
3814 sqltxt :=
3815 'select default_discrete_class, '||
3816 ' decode(lot_number_default_type,1,''Job Name'', '||
3817 ' 2,''Based On Inventory Rules'', '||
3818 ' 3,''No Default'', '||
3819 ' lot_number_default_type) lot_number_default_type, '||
3820 ' decode(so_change_response_type,1,''Never'', '||
3821 ' 2,''Always'', '||
3822 ' 3,''When linked 1-1Default'') so_change_response_type, '||
3823 ' decode(mandatory_scrap_flag,1,''Yes'',2,''No'') Mandatory_Scrap_Flag, '||
3824 ' decode(dynamic_operation_insert_flag,1,''Yes'',2,''No'') Dynamic_Oprn_Insert_Flag, '||
3825 ' decode(moves_over_no_move_statuses,1,''Yes'',2,''No'') Moves_Over_No_Move_Status, '||
3826 ' default_pull_supply_subinv, '||
3827 ' default_pull_supply_locator_id, '||
3828 ' decode(backflush_lot_entry_type,1, ''Manual, verify all'', '||
3829 ' 2, ''Receipt Date, Verify all'', '||
3830 ' 3, ''Receipt Date, Verify excepns'', '||
3831 ' 4, ''Expiration Date, verify all'', '||
3832 ' 5, ''Expiration Date, verify excepns'', '||
3833 ' 6, ''Transaction History'', '||
3834 ' backflush_lot_entry_type) Lot_Selection_Method , ' ;
3835 if (release_level = '11.5.10.2' ) then
3836 sqltxt := sqltxt ||
3837 ' decode(alternate_lot_selection_method,1, ''Manual'', '||
3838 ' 2, ''Receipt Date'', '||
3839 ' 4, ''Expiration Date'' , '||
3840 ' alternate_lot_selection_method) Alternate_Lot_Selection_Method, ' ;
3841 end if ;
3842 sqltxt := sqltxt ||
3843 ' decode(allocate_backflush_components,''1'',''Yes'',''2'',''No'') Allocate_Backflush_Comps, '||
3844 ' decode(allow_backflush_qty_change,1,''Yes'',2,''No'') Allow_Backflush_Qty_Change, '||
3845 ' autorelease_days, '||
3849 ' 3, ''Manual'', '||
3846 ' osp_shop_floor_status, '||
3847 ' decode(po_creation_time, 1, ''At Job/Schedule Release'', '||
3848 ' 2, ''At Operation'', '||
3850 ' po_creation_time) PO_Creation_Time, '||
3851 ' default_overcompl_tolerance, '||
3852 ' production_scheduler_id, '||
3853 ' decode(material_constrained,1,''Yes'',2,''No'') Material_Constrained, '||
3854 ' decode(use_finite_scheduler,1,''Yes'',2,''No'') Use_Finite_Scheduler,'||
3855 ' repetitive_variance_type '||
3856 'from wip_parameters '||
3857 'where organization_id = (select organization_id '||
3858 ' from wip_entities ';
3859
3860
3861 if p_wip_entity_id is not null then
3862 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ||')';
3863 end if;
3864
3865 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3866 'WIP PARAMETERS',null,'Y',row_limit);
3867
3868 IF (dummy_num = row_limit) THEN
3869 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3870 END IF;
3871
3872 sqltxt :=
3873 'select a.assembly_item_id, '||
3874 ' substrb(msi.concatenated_segments, 1,30) item_name, '||
3875 -- ' a.organization_id, '||
3876 ' nvl(a.alternate_bom_designator, ''PRIMARY'') alternate_bom_designator, '||
3877 ' a.common_assembly_item_id, '||
3878 ' decode( a.assembly_type,1,''Manufacturing'', '||
3879 ' 2,''Engineering'', '||
3880 ' a.assembly_type) assembly_type, '||
3881 ' a.bill_sequence_id, '||
3882 ' a.common_bill_sequence_id, '||
3883 ' b.operation_seq_num, '||
3884 ' b.component_item_id, '||
3885 ' substrb(msi_comp.concatenated_segments, 1,30) comp_item_name, '||
3886 ' b.component_quantity, '||
3887 ' b.component_yield_factor, '||
3888 ' b.effectivity_date, '||
3889 ' b.implementation_date, '||
3890 ' b.disable_date, '||
3891 ' decode(b.wip_supply_type,1,''Push'', '||
3892 ' 2,''Assembly Pull'', '||
3893 ' 3,''Operation Pull'', '||
3894 ' 4,''Bulk'', '||
3895 ' 5,''Supplier'', '||
3896 ' 6,''Phantom'', '||
3897 ' 7,''Based on Bill'', '||
3898 ' b.wip_supply_type) wip_supply_type, '||
3899 ' b.supply_subinventory, '||
3900 ' b.supply_locator_id, '||
3901 ' b.component_sequence_id '||
3902 'from bom_bill_of_materials a, bom_inventory_components b, '||
3903 ' wip_discrete_jobs wj, mtl_system_items_kfv msi, mtl_system_items_kfv msi_comp '||
3904 'where a.common_bill_sequence_id = b.bill_sequence_id '||
3905 'and a.organization_id = wj.organization_id '||
3906 'and a.assembly_item_id = wj.primary_item_id '||
3907 'and wj.common_bom_sequence_id = a.bill_sequence_id '||
3908 'and msi.inventory_item_id = a.assembly_item_id '||
3909 'and msi.organization_id = a.organization_id ' ||
3910 'and msi_comp.organization_id = a.organization_id ' ||
3911 'and msi_comp.inventory_item_id = b.component_item_id ';
3912
3913 if p_wip_entity_id is not null then
3914 sqltxt :=sqltxt||' and wj.wip_entity_id = '|| p_wip_entity_id ;
3915 sqltxt :=sqltxt||' order by a.bill_sequence_id, a.assembly_item_id, a.alternate_bom_designator, b.component_sequence_id ';
3916 end if;
3917
3918 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3919 'BILL OF MATERIAL',null,'Y',row_limit);
3920
3921 IF (dummy_num = row_limit) THEN
3922 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3923 END IF;
3924
3925 sqltxt :=
3926 'select bor.assembly_item_id, '||
3927 ' substrb(msi.concatenated_segments, 1,30) item_name, '||
3928 ' bor.organization_id, '||
3929 ' nvl(bor.alternate_routing_designator, ''PRIMARY'') alternate_routing_designator, '||
3930 ' bor.routing_sequence_id, '||
3931 ' bor.common_routing_sequence_id, '||
3932 ' bor.common_assembly_item_id, '||
3933 ' bor.completion_subinventory, '||
3934 ' bor.completion_locator_id, '||
3935 ' decode(nvl(bor.cfm_routing_flag,2), '||
3936 ' 1, ''Flow'', '||
3937 ' 2, ''Discrete'', '||
3938 ' 3, ''Network'', '||
3939 ' bor.cfm_routing_flag) cfm_routing_flag, '||
3940 ' decode (bor.routing_type, '||
3941 ' 1, ''Mfg Rtg'', '||
3942 ' 2, ''Engg Rtg'', '||
3943 ' bor.routing_type) routing_type, '||
3944 ' a.operation_sequence_id, '||
3945 ' a.operation_seq_num, '||
3946 ' a.routing_sequence_id, '||
3947 ' a.standard_operation_id, '||
3948 ' b.operation_code, '||
3949 ' a.department_id, '||
3950 ' a.count_point_type, '||
3951 ' a.effectivity_date, '||
3952 ' a.disable_date, '||
3953 ' decode( a.backflush_flag, 1, ''Yes'', '||
3954 ' 2, ''No'') backflush_flag, '||
3955 ' decode( a.option_dependent_flag, 1, ''Yes'', '||
3956 ' 2, ''No'') option_dependent_flag, '||
3957 ' a.yield, '||
3958 ' decode(a.operation_yield_enabled, 1, ''Yes'', '||
3959 ' 2, ''No'', '||
3960 ' a.operation_yield_enabled) operation_yield_enabled '||
3961 'from bom_operation_sequences a, bom_operational_routings bor, wip_discrete_jobs wj, '||
3962 ' bom_standard_operations b , mtl_system_items_kfv msi '||
3963 'where a.routing_sequence_id = bor.common_routing_sequence_id '||
3964 'and wj.organization_id = bor.organization_id '||
3965 'and wj.common_routing_sequence_id = bor.routing_sequence_id '||
3966 'and a.standard_operation_id = b.standard_operation_id(+) ' ||
3967 'and bor.assembly_item_id = msi.inventory_item_id '||
3971 if p_wip_entity_id is not null then
3968 'and bor.organization_id = msi.organization_id ';
3969
3970
3972 sqltxt :=sqltxt||' and wj.wip_entity_id = '|| p_wip_entity_id ;
3973 sqltxt :=sqltxt||' order by bor.routing_sequence_id, bor.alternate_routing_designator, a.operation_seq_num';
3974 end if;
3975
3976 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
3977 'ROUTING',null,'Y',row_limit);
3978
3979 IF (dummy_num = row_limit) THEN
3980 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
3981 END IF;
3982
3983 sqltxt :=
3984 'SELECT msik.inventory_item_id, '||
3985 ' substr(msik.concatenated_segments, 1, 30) Item, '||
3986 ' msik.outside_operation_flag, '||
3987 ' msik.outside_operation_uom_type, '||
3988 ' wor.operation_seq_num, '||
3989 ' wor.resource_seq_num, '||
3990 ' wor.resource_id, '||
3991 ' br.resource_code, '||
3992 ' decode(wor.autocharge_type , 3, ''Po Move'', 4, ''PO Receipt'') AutoCharge_Type' ||
3993 ' FROM mtl_system_items_kfv msik, '||
3994 ' bom_resources br, '||
3995 ' wip_operation_resources wor '||
3996 ' WHERE msik.inventory_item_id = br.purchase_item_id '||
3997 ' AND msik.organization_id = br.organization_id '||
3998 ' AND wor.resource_id = br.resource_id '||
3999 ' AND wor.autocharge_type IN (3,4) '||
4000 ' AND wor.organization_id = br.organization_id ';
4001
4002 if p_wip_entity_id is not null then
4003 sqltxt :=sqltxt||' and wor.wip_entity_id = '|| p_wip_entity_id ;
4004 end if;
4005
4006 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4007 'OSP ITEM DETAILS',null,'Y',row_limit);
4008
4009 IF (dummy_num = row_limit) THEN
4010 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4011 END IF;
4012
4013 sqltxt :=
4014 'select secondary_inventory_name, '||
4015 ' organization_id, '||
4016 ' decode(reservable_type, '||
4017 ' 1, ''Yes'', '||
4018 ' 2, ''No'', '||
4019 ' reservable_type) reserv_type_mng, '||
4020 ' disable_date, '||
4021 ' decode(inventory_atp_code, '||
4022 ' 1, ''Incl in ATP calc'', '||
4023 ' 2, ''Not Incl in ATP calc'', '||
4024 ' inventory_atp_code) inv_atp_code_mng, '||
4025 ' decode(locator_type, '||
4026 ' 1, ''No loc control'', '||
4027 ' 2, ''Prespecified'', '||
4028 ' 3, ''Dynamic'', '||
4029 ' 4, ''Determined at subinv'', '||
4030 ' 5, ''Determined at item'', '||
4031 ' locator_type) locator_type_mng, '||
4032 ' picking_order, '||
4033 ' source_subinventory '||
4034 'from mtl_secondary_inventories '||
4035 'where organization_id = (select organization_id '||
4036 ' from wip_entities ';
4037
4038
4039 if p_wip_entity_id is not null then
4040 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ||')';
4041 sqltxt :=sqltxt||' order by 1';
4042 end if;
4043
4044 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4045 'SUBINVENTORY SETUP',null,'Y',row_limit);
4046
4047 IF (dummy_num = row_limit) THEN
4048 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4049 END IF;
4050
4051 END repetitive ;
4052
4053 PROCEDURE flow (p_wip_entity_id IN NUMBER) IS
4054 row_limit NUMBER;
4055 BEGIN
4056
4057 row_limit := 1000;
4058
4059 sqltxt :=
4060 ' select a.wip_entity_id , ' ||
4061 ' a.schedule_number, ' ||
4062 ' decode(b.entity_type,1, ''1=Discrete Job'', ' ||
4063 ' 2, ''2=Repetitive Assly'', ' ||
4064 ' 3, ''3=Closed Discr Job'', ' ||
4065 ' 4, ''4=Flow Schedule'', ' ||
4066 ' 5, ''5=Lot Based Job'', ' ||
4067 ' b.entity_type) entity_type, ' ||
4068 ' decode(a.scheduled_flag, 1, ''Flow Schedule'', 2, ''Work Order-less'', a.scheduled_flag) SchFlag , ' ||
4069 ' a.organization_id, ' ||
4070 ' p.organization_code, ' ||
4071 ' a.primary_item_id, ' ||
4072 ' substrb(m.concatenated_segments, 1, 30) item_name, ' ||
4073 ' decode(a.status, ' ||
4074 ' 1,''Open'', ' ||
4075 ' 2, ''Closed'', ' ||
4076 ' a.status) status , ' ||
4077 ' a.completion_subinventory, ' ||
4078 ' a.completion_locator_id, ' ||
4079 ' a.planned_quantity, ' ||
4080 ' m.primary_uom_code uom_code, ' ||
4081 ' a.quantity_completed, ' ||
4082 ' a.quantity_scrapped, ' ||
4083 ' a.class_code, ' ||
4084 ' a.date_closed, ' ||
4085 ' a.creation_date, ' ||
4086 ' a.bom_revision, ' ||
4087 ' a.routing_revision, ' ||
4088 ' nvl(a.alternate_bom_designator, ''PRIMARY'') alternate_bom_designator, ' ||
4089 ' nvl(a.alternate_routing_designator, ''PRIMARY'') alternate_routing_designator ' ||
4090 ' from wip_flow_schedules a , wip_entities b, mtl_system_items_kfv m, mtl_parameters p ' ||
4091 ' where b.wip_entity_id = a.wip_entity_id ' ||
4092 ' and b.organization_id = a.organization_id ' ||
4093 ' and m.inventory_item_id = a.primary_item_id ' ||
4094 ' and m.organization_id = a.organization_id ' ||
4098 sqltxt :=sqltxt||' and b.wip_entity_id = '|| p_wip_entity_id;
4095 ' and a.organization_id = p.organization_id ';
4096
4097 if p_wip_entity_id is not null then
4099 end if;
4100
4101
4102 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4103 'JOB HEADER ( WIP FLOW SCHEDULES , WIP ENTITIES )',null,'Y',row_limit);
4104
4105 IF (dummy_num = row_limit) THEN
4106 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4107 END IF;
4108
4109 sqltxt :=
4110 'select acct_period_id, '||
4111 ' decode(class_type,1,''Standard Discrete'', '||
4112 ' 2,''Repetitive Assembly'', '||
4113 ' 3,''Asset non-standard'', '||
4114 ' 4,''Expense non-standard'', '||
4115 ' 5,''Standard Lot Based'', '||
4116 ' 6,''EAM'', '||
4117 ' 7,''Expense non-standard Lot Based'', '||
4118 ' class_type) class_type, '||
4119 ' tl_resource_in, '||
4120 ' tl_overhead_in, '||
4121 ' tl_outside_processing_in, '||
4122 ' pl_material_in, '||
4123 ' pl_material_overhead_in, '||
4124 ' pl_resource_in, '||
4125 ' pl_overhead_in, '||
4126 ' pl_outside_processing_in, '||
4127 ' tl_material_out, '||
4128 ' tl_material_overhead_out, '||
4129 ' tl_resource_out, '||
4130 ' tl_overhead_out, '||
4131 ' tl_outside_processing_out, '||
4132 ' pl_material_out, '||
4133 ' pl_material_overhead_out, '||
4134 ' pl_resource_out, '||
4135 ' pl_overhead_out, '||
4136 ' pl_outside_processing_out, '||
4137 ' tl_scrap_in, '||
4138 ' tl_scrap_out, '||
4139 ' tl_scrap_var, '||
4140 ' creation_date, '||
4141 ' last_update_date '||
4142 'from wip_period_balances ';
4143
4144 if p_wip_entity_id is not null then
4145 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id;
4146 sqltxt :=sqltxt||' order by creation_date ';
4147 end if;
4148
4149 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4150 'WIP PERIOD BALANCES',null,'Y',row_limit);
4151
4152 IF (dummy_num = row_limit) THEN
4153 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4154 END IF;
4155
4156 sqltxt :=
4157 'select wcti.transaction_id, '||
4158 ' wcti.creation_date, '||
4159 ' wcti.last_update_date, '||
4160 ' wcti.request_id, '||
4161 ' source_code, '||
4162 ' source_line_id, '||
4163 ' decode(process_phase, '||
4164 ' 1, ''Res Valdn'', '||
4165 ' 2, ''Res Processing'', '||
4166 ' 3, ''Job Close'', '||
4167 ' 4, ''Prd Close'', '||
4168 ' process_phase) process_phase_meaning, '||
4169 ' decode(process_status, '||
4170 ' 1, ''Pending'', '||
4171 ' 2, ''Running'', '||
4172 ' 3, ''Error'', '||
4173 ' 4, ''Completed'', '||
4174 ' 5, ''Warning'', '||
4175 ' process_status) process_status_meaning, '||
4176 ' decode(transaction_type, '||
4177 ' 1, ''Resource'', '||
4178 ' 2, ''Overhead'', '||
4179 ' 3, ''OSP'', '||
4180 ' 4, ''Cost Update'', '||
4181 ' 5, ''PrdClose Var'', '||
4182 ' 6, ''JobClose Var'', '||
4183 ' transaction_type) transaction_type_meaning, '||
4184 ' organization_id, '||
4185 ' organization_code, '||
4186 ' primary_item_id, '||
4187 ' transaction_date, '||
4188 ' operation_seq_num, '||
4189 ' resource_seq_num, '||
4190 ' acct_period_id, '||
4191 ' resource_id, '||
4192 ' decode(resource_type, '||
4193 ' 1, ''Machine'', '||
4194 ' 2, ''Person'', '||
4195 ' 3, ''Space'', '||
4196 ' 4, ''Misc'', '||
4197 ' 5, ''Amount'', '||
4198 ' resource_type) resource_type, '||
4199 ' transaction_quantity, '||
4200 ' actual_resource_rate, '||
4201 ' transaction_uom, '||
4202 ' decode(basis_type, '||
4203 ' 1, ''Item'', '||
4204 ' 2, ''Lot'', '||
4205 ' 3, ''Res Units'', '||
4206 ' 4, ''Res Value'', '||
4207 ' 5, ''Tot Value'', '||
4208 ' 6, ''Activity'') basis_type, '||
4209 ' move_transaction_id, '||
4210 ' completion_transaction_id, '||
4211 ' error_column, '||
4212 ' error_message '||
4213 'from wip_cost_txn_interface wcti, '||
4214 ' wip_txn_interface_errors wtie '||
4215 'where wcti.transaction_id = wtie.transaction_id (+) ';
4216
4217 if p_wip_entity_id is not null then
4218 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id ;
4219 sqltxt :=sqltxt||' order by transaction_id';
4220 end if;
4221
4222 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4223 'WIP COST TXN INTERFACE',null,'Y',row_limit);
4224
4225 IF (dummy_num = row_limit) THEN
4226 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4227 END IF;
4228
4229 sqltxt :=
4230 'select wt.transaction_id, '||
4231 ' wt.creation_date, '||
4232 ' wt.last_update_date, '||
4233 ' wt.request_id, '||
4234 ' wt.source_code, '||
4235 ' wt.source_line_id, '||
4236 ' wt.group_id, '||
4240 ' 3, ''OSP'', '||
4237 ' decode(wt.transaction_type, '||
4238 ' 1, ''Resource'', '||
4239 ' 2, ''Overhead'', '||
4241 ' 4, ''Cost Update'', '||
4242 ' 5, ''PrdClose Var'', '||
4243 ' 6, ''JobClose Var'', '||
4244 ' wt.transaction_type) transaction_type_meaning, '||
4245 ' wt.organization_id, '||
4246 ' wt.primary_item_id, '||
4247 ' wt.transaction_date, '||
4248 ' wt.operation_seq_num, '||
4249 ' wt.resource_seq_num, '||
4250 ' wt.acct_period_id, '||
4251 ' wt.resource_id, '||
4252 ' wt.transaction_quantity, '||
4253 ' wt.actual_resource_rate, '||
4254 ' wt.standard_resource_rate, '||
4255 ' wt.transaction_uom, '||
4256 ' wt.move_transaction_id, '||
4257 ' wt.completion_transaction_id '||
4258 'from wip_transactions wt '||
4259 'where exists (select 1 '||
4260 ' from wip_entities we '||
4261 ' where we.wip_entity_id = wt.wip_entity_id '||
4262 ' and we.entity_type <> 2) '; /* Other than Repetitive */
4263
4264 if p_wip_entity_id is not null then
4265 sqltxt :=sqltxt||' and wip_entity_id = '|| p_wip_entity_id ;
4266 sqltxt :=sqltxt||' order by transaction_id';
4267 end if;
4268
4269 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4270 'WIP TRANSACTIONS',null,'Y',row_limit);
4271
4272 IF (dummy_num = row_limit) THEN
4273 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4274 END IF;
4275
4276 sqltxt :=
4277 'select transaction_interface_id, '||
4278 ' transaction_header_id, '||
4279 ' source_code, '||
4280 ' source_line_id, '||
4281 ' source_header_id, '||
4282 ' process_flag, '||
4283 ' transaction_mode, '||
4284 ' lock_flag, '||
4285 ' request_id, '||
4286 ' inventory_item_id, '||
4287 ' organization_id, '||
4288 ' transaction_quantity, '||
4289 ' primary_quantity, '||
4290 ' transaction_uom, '||
4291 ' transaction_date, '||
4292 ' subinventory_code, '||
4293 ' locator_id, '||
4294 ' revision, '||
4295 ' transaction_source_id, '||
4296 ' decode(transaction_source_type_id, '||
4297 ' 1, ''PO'', '||
4298 ' 2, ''SO'', '||
4299 ' 4, ''MoveOrder'', '||
4300 ' 5, ''WIP'', '||
4301 ' 6, ''AcctAlias'', '||
4302 ' 7, ''Int REQ'', '||
4303 ' 8, ''Int Order'', '||
4304 ' 9, ''CycleCount'', '||
4305 ' 10,''PhyCount'', '||
4306 ' 11,''StdCostUpd'', '||
4307 ' 12, ''RMA'', '||
4308 ' 13, ''INV'', '||
4309 ' 17, ''Ext REQ'', '||
4310 ' transaction_source_type_id) txn_source_meaning, '||
4311 ' decode(transaction_action_id, '||
4312 ' 1, ''Issue'', '||
4313 ' 2, ''Subinv Xfr'', '||
4314 ' 3, ''Org Xfr'', '||
4315 ' 4, ''Cycle Count Adj'', '||
4316 ' 5, ''Plan Xfr'', '||
4317 ' 21, ''Intransit Shpmt'', '||
4318 ' 24, ''Cost Update'', '||
4319 ' 27, ''Receipt'', '||
4320 ' 28, ''Stg Xfr'', '||
4321 ' 30, ''Wip scrap'', '||
4322 ' 31, ''Assy Complete'', '||
4323 ' 32, ''Assy return'', '||
4324 ' 33, ''-ve CompIssue'', '||
4325 ' 34, ''-ve CompReturn'', '||
4326 ' 40, ''Inv Lot Split'', '||
4327 ' 41, ''Inv Lot Merge'', '||
4328 ' 42, ''Inv Lot Translate'', '||
4329 ' 42, ''Inv Lot Translate'', '||
4330 ' transaction_action_id) txn_action_meaning, '||
4331 ' transaction_type_id, '||
4332 ' operation_seq_num, '||
4333 ' repetitive_line_id, '||
4334 ' transfer_organization, '||
4335 ' transfer_subinventory, '||
4336 ' transfer_locator, '||
4337 ' overcompletion_transaction_qty, '||
4338 ' overcompletion_primary_qty, '||
4339 ' overcompletion_transaction_id, '||
4340 ' error_code, '||
4341 ' substr(error_explanation,1,100) error_explanation '||
4342 'from mtl_transactions_Interface mti '||
4343 'where mti.transaction_source_type_id = 5 ';
4344
4345 if p_wip_entity_id is not null then
4346 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id;
4347 sqltxt :=sqltxt||' order by transaction_interface_id, transaction_date ';
4348 end if;
4349
4350 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4351 'MTI TRANSACTIONS',null,'Y',row_limit);
4352
4353 IF (dummy_num = row_limit) THEN
4354 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4355 END IF;
4356
4357 sqltxt :=
4358 'select '||
4359 ' transaction_interface_id, '||
4360 ' source_code, '||
4361 ' source_line_id, '||
4362 ' request_id, '||
4363 ' lot_number, '||
4364 ' lot_expiration_date, '||
4365 ' transaction_quantity, '||
4366 ' primary_quantity, '||
4367 ' serial_transaction_temp_id, '||
4368 ' process_flag, '||
4369 ' error_code '||
4370 'from mtl_transaction_lots_interface mtli '||
4371 'where mtli.transaction_interface_id in '||
4372 ' (select transaction_interface_id '||
4373 ' from mtl_transactions_Interface mti '||
4374 ' where mti.transaction_source_type_id = 5 ';
4375
4376
4377 if p_wip_entity_id is not null then
4378 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id ||')';
4379 sqltxt :=sqltxt||' order by lot_expiration_date ';
4380 end if;
4381
4382 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4383 'MTLI TRANSACTIONS',null,'Y',row_limit);
4384
4388
4385 IF (dummy_num = row_limit) THEN
4386 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4387 END IF;
4389 sqltxt :=
4390 'select '||
4391 ' transaction_interface_id, '||
4392 ' source_code, '||
4393 ' source_line_id, '||
4394 ' request_id, '||
4395 ' vendor_serial_number, '||
4396 ' vendor_lot_number, '||
4397 ' fm_serial_number, '||
4398 ' to_serial_number, '||
4399 ' error_code, '||
4400 ' process_flag, '||
4401 ' parent_serial_number '||
4402 'from mtl_serial_numbers_interface msni '||
4403 'where msni.transaction_interface_id in '||
4404 ' (select transaction_interface_id '||
4405 ' from mtl_transactions_Interface mti '||
4406 ' where mti.transaction_source_type_id = 5 ';
4407
4408 if p_wip_entity_id is not null then
4409 sqltxt :=sqltxt||' and mti.transaction_source_id = '|| p_wip_entity_id ||')';
4410 sqltxt :=sqltxt||' order by fm_serial_number ';
4411 end if;
4412
4413 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4414 'MSNI TRANSACTIONS',null,'Y',row_limit);
4415
4416 IF (dummy_num = row_limit) THEN
4417 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4418 END IF;
4419
4420 sqltxt :=
4421 'select transaction_temp_id, '||
4422 ' transaction_header_id, '||
4423 ' source_code, '||
4424 ' source_line_id, '||
4425 ' transaction_mode, '||
4426 ' lock_flag, '||
4427 ' transaction_date, '||
4428 ' transaction_type_id, '||
4429 ' decode(transaction_action_id, '||
4430 ' 1, ''Issue'', '||
4431 ' 2, ''Subinv Xfr'', '||
4432 ' 3, ''Org Xfr'', '||
4433 ' 4, ''Cycle Count Adj'', '||
4434 ' 5, ''Issue'', '||
4435 ' 21, ''Intransit Shpmt'', '||
4436 ' 24, ''Cost Update'', '||
4437 ' 27, ''Receipt'', '||
4438 ' 28, ''Stg Xfr'', '||
4439 ' 30, ''Wip scrap'', '||
4440 ' 31, ''Assy Complete'', '||
4441 ' 32, ''Assy return'', '||
4442 ' 33, ''-ve CompIssue'', '||
4443 ' 34, ''-ve CompReturn'', '||
4444 ' 40, ''Inv Lot Split'', '||
4445 ' 41, ''Inv Lot Merge'', '||
4446 ' 42, ''Inv Lot Translate'', '||
4447 ' 42, ''Inv Lot Translate'', '||
4448 ' transaction_action_id) txn_action_meaning, '||
4449 ' decode(transaction_source_type_id, '||
4450 ' 1, ''PO'', '||
4451 ' 2, ''SO'', '||
4452 ' 4, ''MoveOrder'', '||
4453 ' 5, ''WIP'', '||
4454 ' 6, ''AcctAlias'', '||
4455 ' 7, ''Int REQ'', '||
4456 ' 8, ''Int Order'', '||
4457 ' 9, ''CycleCount'', '||
4458 ' 10,''PhyCount'', '||
4459 ' 11,''StdCostUpd'', '||
4460 ' 12, ''RMA'', '||
4461 ' 13, ''INV'', '||
4462 ' 17, ''Ext REQ'', '||
4463 ' transaction_source_type_id) txn_source_meaning, '||
4464 ' transaction_source_id, '||
4465 ' inventory_item_id, '||
4466 ' organization_id, '||
4467 ' subinventory_code, '||
4468 ' locator_id, '||
4469 ' revision, '||
4470 ' transaction_quantity, '||
4471 ' transaction_uom, '||
4472 ' primary_quantity, '||
4473 ' trx_source_line_id, '||
4474 ' trx_source_delivery_id, '||
4475 ' overcompletion_transaction_qty, '||
4476 ' overcompletion_primary_qty, '||
4477 ' overcompletion_transaction_id, '||
4478 ' move_transaction_id, '||
4479 ' completion_transaction_id, '||
4480 ' source_code, '||
4481 ' source_line_id, '||
4482 ' transfer_organization, '||
4483 ' transfer_subinventory, '||
4484 ' transfer_to_location, '||
4485 ' move_order_line_id, '||
4486 ' reservation_id, '||
4487 ' creation_date, '||
4488 ' last_update_date, '||
4489 ' error_code '||
4490 'from mtl_material_transactions_temp '||
4491 'where transaction_source_type_id = 5 ';
4492
4493
4494 if p_wip_entity_id is not null then
4495 sqltxt :=sqltxt||' and transaction_source_id = '|| p_wip_entity_id ;
4496 sqltxt :=sqltxt||' order by transaction_temp_id ';
4497 end if;
4498
4499 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4500 'MMTT TRANSACTIONS',null,'Y',row_limit);
4501
4502 IF (dummy_num = row_limit) THEN
4503 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4504 END IF;
4505
4506 sqltxt :=
4507 'select '||
4508 ' transaction_temp_id, '||
4509 ' transaction_quantity, '||
4510 ' primary_quantity, '||
4511 ' lot_number, '||
4512 ' lot_expiration_date, '||
4513 ' serial_transaction_temp_id, '||
4514 ' group_header_id, '||
4515 ' put_away_rule_id, '||
4516 ' pick_rule_id, '||
4517 ' request_id, '||
4518 ' creation_date, '||
4519 ' error_code '||
4520 'from mtl_transaction_lots_temp mtlt '||
4521 'where mtlt.transaction_temp_id in '||
4522 ' (select mmtt.transaction_temp_id '||
4523 ' from mtl_material_transactions_temp mmtt '||
4524 ' where mmtt.transaction_source_type_id = 5 ';
4525
4526 if p_wip_entity_id is not null then
4527 sqltxt :=sqltxt||' and mmtt.transaction_source_id = '|| p_wip_entity_id ||')' ;
4528 sqltxt :=sqltxt||' order by transaction_temp_id, lot_number ';
4529 end if;
4530
4531 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4532 'MTLT TRANSACTIONS',null,'Y',row_limit);
4533
4534 IF (dummy_num = row_limit) THEN
4538 sqltxt :=
4535 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4536 END IF;
4537
4539 'select '||
4540 ' transaction_temp_id, '||
4541 ' vendor_serial_number, '||
4542 ' vendor_lot_number, '||
4543 ' fm_serial_number, '||
4544 ' to_serial_number, '||
4545 ' serial_prefix, '||
4546 ' group_header_id, '||
4547 ' parent_serial_number, '||
4548 ' end_item_unit_number, '||
4549 ' request_id, '||
4550 ' creation_date, '||
4551 ' error_code '||
4552 'from mtl_serial_numbers_temp msnt '||
4553 'where msnt.transaction_temp_id in '||
4554 ' (select mmtt.transaction_temp_id '||
4555 ' from mtl_material_transactions_temp mmtt '||
4556 ' where mmtt.transaction_source_type_id = 5 ';
4557
4558 if p_wip_entity_id is not null then
4559 sqltxt :=sqltxt||' and mmtt.transaction_source_id = '|| p_wip_entity_id ||')' ;
4560 sqltxt :=sqltxt||' order by transaction_temp_id, fm_serial_number ';
4561 end if;
4562
4563 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4564 'MSNT TRANSACTIONS',null,'Y',row_limit);
4565
4566 IF (dummy_num = row_limit) THEN
4567 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4568 END IF;
4569
4570 sqltxt :=
4571 'select DISTINCT '||
4572 ' TRL.LINE_ID MOVE_LINE_ID, '||
4573 ' TRL.REQUEST_NUMBER MOVE_NUMBER, '||
4574 ' TRL.HEADER_ID MV_HDR_ID, '||
4575 ' TRL.LINE_NUMBER MV_LINE_NUM, '||
4576 ' decode(TRL.LINE_STATUS, '||
4577 ' 1, ''Incomplete'', '||
4578 ' 2, ''Pend Aprvl'', '||
4579 ' 3, ''Approved'', '||
4580 ' 4, ''Not Apprvd'', '||
4581 ' 5, ''Closed'', '||
4582 ' 6, ''Canceled'', '||
4583 ' 7, ''Pre Apprvd'', '||
4584 ' 8, ''Part Aprvd'') MV_LINE_STAT, '||
4585 ' TRL.INVENTORY_ITEM_ID, '||
4586 ' TRL.ORGANIZATION_ID, '||
4587 ' TRL.REVISION, '||
4588 ' TRL.QUANTITY QTY, '||
4589 ' TRL.PRIMARY_QUANTITY PRM_QTY, '||
4590 ' TRL.QUANTITY_DELIVERED DLVD_QTY, '||
4591 ' TRL.QUANTITY_DETAILED DTLD_QTY, '||
4592 ' TRL.MOVE_ORDER_TYPE_NAME MOVE_TYPE_NAME, '||
4593 ' decode(TRL.TRANSACTION_SOURCE_TYPE_ID,2,''Sales Order'', '||
4594 ' 5,''Job or Schedule'', '||
4595 ' 13,''Inventory'', '||
4596 ' TRL.TRANSACTION_SOURCE_TYPE_ID) txn_source_meaning, '||
4597 ' TRL.TRANSACTION_TYPE_NAME transaction_type_meaning, '||
4598 ' decode(TRL.TRANSACTION_ACTION_ID, '||
4599 ' 1, ''Issue'', '||
4600 ' 2, ''Subinv Xfr'', '||
4601 ' 3, ''Org Xfr'', '||
4602 ' 4, ''Cycle Count Adj'', '||
4603 ' 5, ''Plan Xfr'', '||
4604 ' 21, ''Intransit Shpmt'', '||
4605 ' 24, ''Cost Update'', '||
4606 ' 27, ''Receipt'', '||
4607 ' 28, ''Stg Xfr'', '||
4608 ' 30, ''Wip scrap'', '||
4609 ' 31, ''Assy Complete'', '||
4610 ' 32, ''Assy return'', '||
4611 ' 33, ''-ve CompIssue'', '||
4612 ' 34, ''-ve CompReturn'', '||
4613 ' 40, ''Inv Lot Split'', '||
4614 ' 41, ''Inv Lot Merge'', '||
4615 ' 42, ''Inv Lot Translate'', '||
4616 ' 42, ''Inv Lot Translate'', '||
4617 ' trl.transaction_action_id) txn_action_meaning, '||
4618 ' TRL.FROM_SUBINVENTORY_CODE FROM_SUB, '||
4619 ' TRL.FROM_LOCATOR_ID FROM_LOC_ID, '||
4620 ' TRL.TO_SUBINVENTORY_CODE TO_SUB, '||
4621 ' TRL.TO_LOCATOR_ID TO_LOC_ID, '||
4622 ' TRL.LOT_NUMBER LOT_NUM, '||
4623 ' TRL.TRANSACTION_HEADER_ID TRNS_HEAD_ID, '||
4624 ' TRL.CREATION_DATE '||
4625 'from MTL_TXN_REQUEST_LINES_V TRL '||
4626 'WHERE trl.move_order_type <> 6 '||
4627 'AND (trl.txn_source_id, trl.txn_source_line_id) in '||
4628 ' (select wdj.wip_entity_id, wro.operation_seq_num '||
4629 ' from wip_flow_schedules wdj, '||
4630 ' wip_entities we, '||
4631 ' wip_lines wl, '||
4632 ' wip_requirement_operations wro '||
4633 ' where wdj.wip_entity_id = we.wip_entity_id '||
4634 ' and wdj.organization_id = we.organization_id '||
4635 ' and wdj.wip_entity_id = wro.wip_entity_id '||
4636 ' and wdj.organization_id = wro.organization_id '||
4637 ' and wdj.line_id = wl.line_id(+) '||
4638 ' and wdj.organization_id = wl.organization_id(+) ';
4639
4640 if p_wip_entity_id is not null then
4641 sqltxt :=sqltxt||' and we.wip_entity_id = '|| p_wip_entity_id ||')' ;
4642 sqltxt :=sqltxt||' order by request_number ';
4643 end if;
4644
4645 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4646 'MTL_TXN_REQUEST_LINES_V TRANSACTIONS - MOVE ORDERS',null,'Y',row_limit);
4647
4648 IF (dummy_num = row_limit) THEN
4649 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4650 END IF;
4651
4652 sqltxt :=
4653 'select transaction_id, '||
4654 ' transaction_date, '||
4655 ' transaction_type_id, '||
4656 ' decode(transaction_action_id, '||
4657 ' 1, ''Issue'', '||
4658 ' 2, ''Subinv Xfr'', '||
4659 ' 3, ''Org Xfr'', '||
4660 ' 4, ''Cycle Count Adj'', '||
4661 ' 5, ''Issue'', '||
4662 ' 21, ''Intransit Shpmt'', '||
4666 ' 30, ''Wip scrap'', '||
4663 ' 24, ''Cost Update'', '||
4664 ' 27, ''Receipt'', '||
4665 ' 28, ''Stg Xfr'', '||
4667 ' 31, ''Assy Complete'', '||
4668 ' 32, ''Assy return'', '||
4669 ' 33, ''-ve CompIssue'', '||
4670 ' 34, ''-ve CompReturn'', '||
4671 ' 40, ''Inv Lot Split'', '||
4672 ' 41, ''Inv Lot Merge'', '||
4673 ' 42, ''Inv Lot Translate'', '||
4674 ' 42, ''Inv Lot Translate'', '||
4675 ' transaction_action_id) txn_action_meaning, '||
4676 ' decode(transaction_source_type_id, '||
4677 ' 1, ''PO'', '||
4678 ' 2, ''SO'', '||
4679 ' 4, ''MoveOrder'', '||
4680 ' 5, ''WIP'', '||
4681 ' 6, ''AcctAlias'', '||
4682 ' 7, ''Int REQ'', '||
4683 ' 8, ''Int Order'', '||
4684 ' 9, ''CycleCount'', '||
4685 ' 10,''PhyCount'', '||
4686 ' 11,''StdCostUpd'', '||
4687 ' 12, ''RMA'', '||
4688 ' 13, ''INV'', '||
4689 ' 17, ''Ext REQ'', '||
4690 ' transaction_source_type_id) txn_source_meaning, '||
4691 ' transaction_source_id, '||
4692 ' inventory_item_id, '||
4693 ' organization_id, '||
4694 ' subinventory_code, '||
4695 ' locator_id, '||
4696 ' revision, '||
4697 ' transaction_quantity, '||
4698 ' transaction_uom, '||
4699 ' primary_quantity, '||
4700 ' trx_source_line_id, '||
4701 ' trx_source_delivery_id, '||
4702 ' move_transaction_id, '||
4703 ' completion_transaction_id, '||
4704 ' source_code, '||
4705 ' source_line_id, '||
4706 ' transfer_organization_id, '||
4707 ' transfer_subinventory, '||
4708 ' transfer_locator_id, '||
4709 ' move_order_line_id, '||
4710 ' reservation_id, '||
4711 ' creation_date, '||
4712 ' last_update_date, '||
4713 ' error_code '||
4714 'from mtl_material_transactions '||
4715 'where transaction_source_type_id = 5 '||
4716 'and exists (select 1 '||
4717 ' from wip_entities '||
4718 ' where wip_entity_id = transaction_source_id '||
4719 ' and entity_type <> 2 ) '; /* Other than repetitive schedule */
4720
4721 if p_wip_entity_id is not null then
4722 sqltxt :=sqltxt||' and transaction_source_id = '|| p_wip_entity_id ;
4723 sqltxt :=sqltxt||' order by transaction_id ';
4724 end if;
4725
4726 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4727 'MMT TRANSACTIONS',null,'Y',row_limit);
4728
4729 IF (dummy_num = row_limit) THEN
4730 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4731 END IF;
4732
4733 sqltxt :=
4734 'select '||
4735 ' inventory_item_id, '||
4736 ' lot_number, '||
4737 ' organization_id, '||
4738 ' transaction_id, '||
4739 ' transaction_date, '||
4740 ' creation_date, '||
4741 ' transaction_source_id, '||
4742 ' decode(transaction_source_type_id, '||
4743 ' 1, ''PO'', '||
4744 ' 2, ''SO'', '||
4745 ' 4, ''MoveOrder'', '||
4746 ' 5, ''WIP'', '||
4747 ' 6, ''AcctAlias'', '||
4748 ' 7, ''Int REQ'', '||
4749 ' 8, ''Int Order'', '||
4750 ' 9, ''CycleCount'', '||
4751 ' 10,''PhyCount'', '||
4752 ' 11,''StdCostUpd'', '||
4753 ' 12, ''RMA'', '||
4754 ' 13, ''INV'', '||
4755 ' 17, ''Ext REQ'', '||
4756 ' transaction_source_type_id) txn_source_meaning, '||
4757 ' transaction_quantity, '||
4758 ' primary_quantity, '||
4759 ' serial_transaction_id '||
4760 'from mtl_transaction_lot_numbers mtln '||
4761 'where mtln.transaction_id in '||
4762 ' (select mmt.transaction_id '||
4763 ' from mtl_material_transactions mmt '||
4764 ' where mmt.transaction_source_type_id = 5 ';
4765
4766 if p_wip_entity_id is not null then
4767 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ||')';
4768 sqltxt :=sqltxt||' order by inventory_item_id, lot_number ';
4769 end if;
4770
4771 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4772 'MTLN TRANSACTIONS',null,'Y',row_limit);
4773
4774 IF (dummy_num = row_limit) THEN
4775 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4776 END IF;
4777
4778 /*
4779 sqltxt :=
4780 'select '||
4781 ' inventory_item_id, '||
4782 ' serial_number, '||
4783 ' decode(current_status, '||
4784 ' 1, ''Defined but not used'', '||
4785 ' 3, ''Resides in stores'', '||
4786 ' 4, ''Issued out of stores'', '||
4787 ' 5, ''Resides in intrasit'', '||
4788 ' current_status) current_status_meaning, '||
4789 ' revision, '||
4790 ' lot_number, '||
4791 ' parent_item_id, '||
4792 ' last_transaction_id, '||
4793 ' parent_serial_number, '||
4794 ' end_item_unit_number, '||
4795 ' group_mark_id, '||
4796 ' line_mark_id, '||
4797 ' lot_line_mark_id, '||
4798 ' gen_object_id, '||
4799 ' creation_date '||
4800 'from mtl_serial_numbers msn ';
4801
4802 if p_wip_entity_id is not null then
4803 sqltxt :=sqltxt||' where msn.wip_entity_id = '|| p_wip_entity_id ;
4804 sqltxt :=sqltxt||' order by inventory_item_id, serial_number ';
4805 end if;
4806
4807 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4808 'MSN TRANSACTIONS',null,'Y',row_limit);
4809
4810 IF (dummy_num = row_limit) THEN
4811 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4812 END IF;
4813
4814 */
4818 ' mmt.transaction_id,' ||
4815
4816 sqltxt :=
4817 ' select mmt.inventory_item_id, ' ||
4819 ' mmt.transaction_date,' ||
4820 ' mmt.transaction_source_id,' ||
4821 ' mut.serial_number,' ||
4822 ' mmt.subinventory_code,' ||
4823 ' mmt.locator_id , ' ||
4824 ' mmt.creation_date' ||
4825 ' from mtl_material_transactions mmt,' ||
4826 ' mtl_unit_transactions mut' ||
4827 ' where mmt.transaction_action_id in (1, 27, 33, 34, 30, 31, 32)' ||
4828 ' and mmt.transaction_source_type_id = 5' ||
4829 ' and mut.transaction_id = mmt.transaction_id' ;
4830
4831 if p_wip_entity_id is not null then
4832 sqltxt :=sqltxt||' and mmt.transaction_source_id = '|| p_wip_entity_id ;
4833 sqltxt :=sqltxt||' order by mmt.inventory_item_id, mut.serial_number ';
4834 end if ;
4835
4836
4837 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4838 'MSN TRANSACTIONS',null,'Y',row_limit);
4839
4840 IF (dummy_num = row_limit) THEN
4841 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4842 END IF;
4843
4844 sqltxt :=
4845 'select header_id, '||
4846 ' source_id, '||
4847 ' source_code, '||
4848 ' completion_status, '||
4849 ' creation_date, '||
4850 ' last_update_date, '||
4851 ' inventory_item_id, '||
4852 ' organization_id, '||
4853 ' primary_quantity, '||
4854 ' transaction_quantity, '||
4855 ' transaction_uom, '||
4856 ' transaction_date, '||
4857 ' transaction_action_id, '||
4858 ' transaction_source_id, '||
4859 ' transaction_source_type_id, '||
4860 ' transaction_type_id, '||
4861 ' transaction_mode, '||
4862 ' acct_period_id, '||
4863 ' subinventory_code, '||
4864 ' locator_id, '||
4865 ' schedule_id, '||
4866 ' repetitive_line_id, '||
4867 ' operation_seq_num, '||
4868 ' cost_group_id, '||
4869 ' lock_flag, '||
4870 ' error_code, '||
4871 ' final_completion_flag, '||
4872 ' completion_transaction_id '||
4873 'from wip_lpn_completions ';
4874
4875
4876 if p_wip_entity_id is not null then
4877 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ;
4878 sqltxt :=sqltxt||' order by header_id ';
4879 end if;
4880
4881 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4882 'WIP LPN COMPLETIONS',null,'Y',row_limit);
4883
4884 IF (dummy_num = row_limit) THEN
4885 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4886 END IF;
4887
4888 sqltxt :=
4889 'select '||
4890 ' RES.RESERVATION_ID RESERV_ID, '||
4891 ' decode(RES.SHIP_READY_FLAG,1,''1=Released'',2,''2=Submitted'',to_char(RES.SHIP_READY_FLAG)) '||
4892 ' SHIP_READY, '||
4893 ' RES.DEMAND_SOURCE_HEADER_ID DS_HEADER_ID, '||
4894 ' RES.DEMAND_SOURCE_LINE_ID DS_LINE_ID, '||
4895 ' RES.DEMAND_SOURCE_DELIVERY DS_DELIVERY, '||
4896 ' RES.INVENTORY_ITEM_ID ITEM_ID, '||
4897 ' RES.RESERVATION_QUANTITY RES_QTY, '||
4898 ' RES.RESERVATION_UOM_CODE RUOM, '||
4899 ' RES.PRIMARY_RESERVATION_QUANTITY PRES_QTY, '||
4900 ' RES.PRIMARY_UOM_CODE PUOM, '||
4901 ' RES.DETAILED_QUANTITY DET_QTY, '||
4902 ' RES.REQUIREMENT_DATE REQUIRD_DATE, '||
4903 ' RES.DEMAND_SOURCE_TYPE_ID DS_TYPE, '||
4904 ' RES.ORGANIZATION_ID ORG_ID, '||
4905 ' RES.SUBINVENTORY_CODE SUBINV, '||
4906 ' RES.LOT_NUMBER LOT, '||
4907 ' RES.REVISION REV, '||
4908 ' RES.LOCATOR_ID LOC_ID, '||
4909 ' RES.SERIAL_NUMBER SERIAL_NUM, '||
4910 ' decode(RES.SUPPLY_SOURCE_TYPE_ID,1,''1=PO'', '||
4911 ' 2,''2=OE'', '||
4912 ' 5,''5=WIP DJ'', '||
4913 ' 7,''7=INT_REQ'', '||
4914 ' 8,''8=INT_OE'', '||
4915 ' 13,''13=INV'', '||
4916 ' 17,''17=REQ'', '||
4917 ' RES.SUPPLY_SOURCE_TYPE_ID) '||
4918 ' SS_TYPE_ID, '||
4919 ' We.WIP_ENTITY_ID WIP_ID, '||
4920 ' decode(JOB.STATUS, 1, ''Open'', '||
4921 ' 2, ''Closed'', '||
4922 ' JOB.STATUS ) STATUS, '||
4923 ' RES.SUPPLY_SOURCE_HEADER_ID SS_HEADER_ID, '||
4924 ' RES.SUPPLY_SOURCE_LINE_DETAIL SS_SOURCE_LINE_DET, '||
4925 ' RES.SUPPLY_SOURCE_LINE_ID SS_SOURCE_LINE, '||
4926 ' RES.PARTIAL_QUANTITIES_ALLOWED ALLOW_PART, '||
4927 ' to_char(RES.CREATION_DATE, ''DD-MON HH24:MI:SS'') CREATE_DATE, '||
4928 ' to_char(RES.LAST_UPDATE_DATE, ''DD-MON HH24:MI:SS'') UPD_DATE '||
4929 'from '||
4930 ' MTL_RESERVATIONS RES, '||
4931 ' WIP_ENTITIES WE, '||
4932 ' WIP_FLOW_SCHEDULES JOB '||
4933 'where RES.SUPPLY_SOURCE_HEADER_ID = We.WIP_ENTITY_ID '||
4934 'and We.WIP_ENTITY_ID = JOB.WIP_ENTITY_ID ';
4935
4936 if p_wip_entity_id is not null then
4937 sqltxt :=sqltxt||' and we.wip_entity_id = '|| p_wip_entity_id ;
4938 sqltxt :=sqltxt||' order by reservation_id ';
4939 end if;
4940
4941 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4942 'MTL RESERVATIONS',null,'Y',row_limit);
4943
4947
4944 IF (dummy_num = row_limit) THEN
4945 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
4946 END IF;
4948 sqltxt :=
4949 'select secondary_inventory_name, '||
4950 ' organization_id, '||
4951 ' decode(reservable_type, '||
4952 ' 1, ''Yes'', '||
4953 ' 2, ''No'', '||
4954 ' reservable_type) reserv_type_mng, '||
4955 ' disable_date, '||
4956 ' decode(inventory_atp_code, '||
4957 ' 1, ''Incl in ATP calc'', '||
4958 ' 2, ''Not Incl in ATP calc'', '||
4959 ' inventory_atp_code) inv_atp_code_mng, '||
4960 ' decode(locator_type, '||
4961 ' 1, ''No loc control'', '||
4962 ' 2, ''Prespecified'', '||
4963 ' 3, ''Dynamic'', '||
4964 ' 4, ''Determined at subinv'', '||
4965 ' 5, ''Determined at item'', '||
4966 ' locator_type) locator_type_mng, '||
4967 ' picking_order, '||
4968 ' source_subinventory '||
4969 'from mtl_secondary_inventories '||
4970 'where organization_id = (select organization_id '||
4971 ' from wip_entities ';
4972
4973
4974 if p_wip_entity_id is not null then
4975 sqltxt :=sqltxt||' where wip_entity_id = '|| p_wip_entity_id ||')';
4976 sqltxt :=sqltxt||' order by 1';
4977 end if;
4978
4979 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
4980 'SUBINVENTORY SETUP',null,'Y',row_limit);
4981 END flow ;
4982
4983 procedure setup (p_org_id IN NUMBER,
4984 report OUT NOCOPY JTF_DIAG_REPORT,
4985 reportClob OUT NOCOPY CLOB) IS
4986
4987 l_user_id varchar2(255);
4988 l_user_name varchar2(255);
4989 l_resp_id varchar2(255);
4990 l_resp_name varchar2(255);
4991 l_appl_id number := 506 ;
4992 l_pov varchar2(60);
4993 l_lvl varchar2(10);
4994 l_po_id_string varchar2(2000) ;
4995 l_user_id_string varchar2(100) ;
4996 l_resp_id_string varchar2(100) ;
4997
4998 l_release_name varchar2(20) ;
4999 l_other_info varchar2(20) ;
5000 l_result boolean ;
5001
5002 reportStr varchar2(2000) ;
5003 l_count number ;
5004
5005 l_url varchar2(255) ;
5006 l_desc varchar2(255) ;
5007
5008
5009 row_limit number;
5010
5011 cursor wip_param_csr is
5012 select *
5013 from wip_parameters
5014 where organization_id = p_org_id ;
5015
5016 wip_param_rec wip_param_csr%ROWTYPE ;
5017
5018 cursor wip_param_v_csr is
5019 select *
5020 from wip_parameters_v
5021 where organization_id = p_org_id ;
5022
5023 wip_param_v_rec wip_param_v_csr%ROWTYPE ;
5024
5025 cursor inv_param_csr is
5026 select *
5027 from mtl_parameters
5028 where organization_id = p_org_id ;
5029
5030 inv_param_rec inv_param_csr%ROWTYPE ;
5031 apps_ver varchar2(20) ;
5032
5033
5034
5035
5036 procedure checkWipProfiles is
5037
5038 profile_val fnd_profile_option_values.profile_option_value%type ;
5039
5040 begin
5041
5042 row_limit := 1000;
5043
5044 l_result := fnd_release.get_release(l_release_name, l_other_info) ;
5045
5046 l_url := 'http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT' || '&' || 'p_id=67009.1' ;
5047 l_desc := 'Oracle Work in Process Documentation - Release 11i' ;
5048
5049 fnd_profile.get('USER_ID', l_user_id);
5050 fnd_profile.get('USER_NAME', l_user_name);
5051 fnd_profile.get('RESP_ID', l_resp_id);
5052 fnd_profile.get('RESP_NAME', l_resp_name);
5053
5054 if l_user_id is null then
5055 l_user_id_string := '0' ;
5056 else
5057 l_user_id_string := to_char(l_user_id) ;
5058 end if ;
5059
5060 if l_resp_id is null then
5061 l_resp_id_string := '0' ;
5062 else
5063 l_resp_id_string := to_char(l_resp_id) ;
5064 end if ;
5065
5066 /*
5067 sqltxt := 'select substr(fpo.user_profile_option_name, 1, 60) Profile ,decode(substr(fpov.profile_option_value, 1, 52), ''1'', ''Yes'', ''2'', ''No'', substr(fpov.profile_option_value,1, 52)) Value, '||
5068
5069 ' decode(fpov.level_id, 10001, ''Site'', 10002, ''Appl'', 10003, ''Resp'', 10004, ''User'', ''None'') lvl '||
5070 ' from fnd_profile_option_values fpov , '||
5071 ' fnd_profile_options_vl fpo ' ||
5072 ' where fpo.application_id = fpov.application_id ' ||
5073 ' and fpo.profile_option_id = fpov.profile_option_id ' ||
5074 ' and (fpov.application_id = 706 '||
5075 ' and fpov.profile_option_id in ( ' ||
5076 ' select fpovl.profile_option_id ' ||
5077 ' from fnd_profile_options_vl fpovl ' ||
5078 ' where fpovl.application_id = 706 ' ||
5079 ' and fpovl.start_date_active <= sysdate ' ||
5080 ' and nvl(fpovl.end_date_active,sysdate) >= sysdate) ' ||
5081 ' or (fpov.application_id = 704 and fpov.profile_option_id = 1260)) ' ||
5082 ' and ((fpov.level_id = 10001 and fpov.level_value = 0) '||
5083 ' or (fpov.level_id = 10002 and fpov.level_value = 706) '||
5084 ' or (fpov.level_id = 10003 and fpov.level_value_application_id = 706 '||
5088
5085 ' and fpov.level_value = to_number( ' || l_resp_id_string || ')) '||
5086 ' or (fpov.level_id = 10004 and fpov.level_value = to_number( ' || l_user_id_string || '))) ' ||
5087 ' order by fpo.user_profile_option_name, fpov.level_id desc ' ;
5089 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
5090 'WIP Profiles',null,'Y',row_limit);
5091
5092 IF (dummy_num = row_limit) THEN
5093 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5094 END IF;
5095
5096 */
5097
5098 reportStr := 'WIP Profiles' ;
5099 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5100 JTF_DIAGNOSTIC_COREAPI.Display_Profiles(706) ;
5101
5102 reportStr := 'MRP Debug Profile' ;
5103 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5104 JTF_DIAGNOSTIC_COREAPI.Display_Profiles(704, 'MRP_DEBUG');
5105
5106
5107
5108 apps_ver := JTF_DIAGNOSTIC_COREAPI.Get_DB_Apps_Version ;
5109
5110 /* Fix for #5757345. in following if */
5111
5112 if (release_level in ('11.5.10' , '11.5.10.1', '11.5.10.2')) then
5113
5114 reportStr := 'FND Profiles' ;
5115 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5116
5117 -- Check FND Profile for WIP Debugging
5118 reportStr := 'FND Profiles - FND: Debug Log Enabled' ;
5119 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5120 JTF_DIAGNOSTIC_COREAPI.Display_Profiles(0, 'AFLOG_ENABLED');
5121
5122 reportStr := 'FND Profiles - FND: Debug Log FileName' ;
5123 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5124 JTF_DIAGNOSTIC_COREAPI.Display_Profiles(0, 'AFLOG_FILENAME');
5125
5126 reportStr := 'FND Profiles - FND: Debug Log Level' ;
5127 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5128 JTF_DIAGNOSTIC_COREAPI.Display_Profiles(0, 'AFLOG_LEVEL');
5129
5130 reportStr := 'FND Profiles - FND: Debug Log Module' ;
5131 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5132 JTF_DIAGNOSTIC_COREAPI.Display_Profiles(0, 'AFLOG_MODULE');
5133
5134 reportStr := 'Following profiles are not used from 11.5.10 onwards' ;
5135 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5136 JTF_DIAGNOSTIC_COREAPI.Tab1Print('TP:WIP Background Shop Floor Material Processing') ;
5137 JTF_DIAGNOSTIC_COREAPI.Tab1Print('TP:WIP Operation Backflush Setup') ;
5138 JTF_DIAGNOSTIC_COREAPI.Tab1Print('TP:WIP Debug File') ;
5139 JTF_DIAGNOSTIC_COREAPI.Tab1Print('TP:WIP Debug Directory') ;
5140 JTF_DIAGNOSTIC_COREAPI.Tab1Print('WIP:Job Name Updatable') ;
5141
5142 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5143
5144 reportStr := 'Following profiles are introduced in 11.5.10' ;
5145 JTF_DIAGNOSTIC_COREAPI.SectionPrint(reportStr) ;
5146
5147 JTF_DIAGNOSTIC_COREAPI.Tab1Print('TP:WIP Work Order-less Completion Transaction Form') ;
5148 JTF_DIAGNOSTIC_COREAPI.Tab1Print('TP:WIP Work Order-less Default Completion Type') ;
5149
5150 end if ;
5151
5152
5153 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5154
5155 profile_val := JTF_DIAGNOSTIC_COREAPI.CheckProfile('WIP_CONC_MESSAGE_LEVEL', l_user_id, l_resp_id, l_appl_id );
5156
5157 if (profile_val <> 0 ) then
5158 -- Check for MRP Debug value. It must be set to yes so that debug messages can be printed.
5159 profile_val := JTF_DIAGNOSTIC_COREAPI.CheckProfile('MRP_DEBUG', l_user_id, l_resp_id, 704);
5160
5161 reportStr := ' MRP: Debug profile must be set to ''Yes'' so that Debug messages in concurrent definition of Job , WIP Mass Load, Autocreate FAS, Leadtime request will be printed' ;
5162 JTF_DIAGNOSTIC_COREAPI.WarningPrint(reportStr);
5163
5164 else
5165 reportStr := 'Debug messages in concurrent definition of Job , WIP Mass Load, Autocreate FAS, Leadtime request will not be printed' ;
5166 JTF_DIAGNOSTIC_COREAPI.WarningPrint(reportStr);
5167 end if ;
5168 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5169
5170
5171
5172 -- Check if Oracle Flow Manufacturing is installed as check profiles as appropriate
5173 -- if (FNDUtility.getInstallStatus(report, 714) = 2) then
5174 profile_val := JTF_DIAGNOSTIC_COREAPI.CheckProfile('WIP_WORKORDERLESS_COMP_FORM_DEFAULT', l_user_id, l_resp_id, l_appl_id );
5175
5176 if (profile_val = 4 ) then
5177 reportStr := ' The profile option ''TP:WIP Work Order-less Default Completion Type'' has not been set.';
5178 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
5179 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('If discrete manufacturing or project manufacturing or flow manufacturing '||
5180 'is planned to be used this profile must be set. Please see the' || l_url , l_desc, 'for more information on how to setup WIP Profile') ;
5181 end if ;
5182
5183 -- end if ;
5184 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5185
5186
5187 profile_val := JTF_DIAGNOSTIC_COREAPI.CheckProfile('WIP_JOB_PREFIX', l_user_id, l_resp_id, l_appl_id , 'No Prefix');
5188 if (profile_val = 4 )then
5189 reportStr := ' The profile option ''WIP:Discrete Job Prefix'' has not been set.';
5190 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
5191 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink('If discrete manufacturing or project manufacturing or flow manufacturing'||
5192 ' is planned to be used this profile must be set. Please see the' , l_url , l_desc ,'for more information on how to setup WIP Profile') ;
5193 end if ;
5197 if (profile_val = 4) then
5194 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5195
5196 profile_val := JTF_DIAGNOSTIC_COREAPI.CheckProfile('WIP_OSP_WF', l_user_id, l_resp_id, l_appl_id );
5198 reportStr := ' The profile option ''WIP:Job Name Updatable'' has not been set.';
5199 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
5200 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('If discrete manufacturing or project manufacturing or flow '||
5201 'manufacturing is planned to be used this profile must be set. Please see the', l_url ,
5202 l_desc,'for more information on how to setup WIP Profile') ;
5203 end if ;
5204 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5205
5206 /*
5207 if (apps_ver = '11.5.9') then
5208 profile_val := JTF_DIAGNOSTIC_COREAPI.CheckProfile('WIP_DEBUG_FILE', l_user_id, l_resp_id, l_appl_id, 'wip.log' );
5209 end if ;
5210 */
5211
5212
5213 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5214
5215 end checkWipProfiles ;
5216
5217 procedure checkWipDetails is
5218 row_limit NUMBER;
5219 begin
5220 row_limit := 1000;
5221
5222 sqltxt := 'SELECT ' ||
5223 'mlu1.meaning cost_method, ' ||
5224 'mlu2.meaning def_lot_numb_typ, ' ||
5225 'mlu3.meaning resp_so_chgs, ' ||
5226 'mlu4.meaning per_variances, ' ||
5227 'mlu5.meaning lot_selection_mth, ' ||
5228 'wip1.default_discrete_class def_disc_cls, ' ||
5229 'wip1.autorelease_days auto_rel_days, ' ||
5230 'wip1.default_pull_supply_subinv supply_subinv, ' ||
5231 'wip1.component_atp_rule_name atp_rule, ' ||
5232 'wip1.cost_type cost_type, ' ||
5233 'wip1.system_option syst_opt, ' ||
5234 'wip1.completion_cost_source_meaning compl_cst_src, ' ||
5235 'decode(wip1.auto_compute_final_completion, ' ||
5236 '1, ' ||
5237 '''Y'', ' ||
5238 '''N'') auto_comp, ' ||
5239 'decode(wip1.dynamic_operation_insert_flag, ' ||
5240 '1, ' ||
5241 '''Y'', ' ||
5242 '''N'') dynamic_ops_ins, ' ||
5243 'decode(wip1.moves_over_no_move_statuses, ' ||
5244 '1, ' ||
5245 '''Y'', ' ||
5246 '''N'') moves_over_no_move, ' ||
5247 'decode(wip1.queue_enabled_flag, ' ||
5248 '1, ' ||
5249 '''Y'', ' ||
5250 '''N'') intra_queue_flg, ' ||
5251 'decode(wip1.run_enabled_flag, ' ||
5252 '1, ' ||
5253 '''Y'', ' ||
5254 '''N'') intra_run_flg, ' ||
5255 'decode(wip1.to_move_enabled_flag, ' ||
5256 '1, ' ||
5257 '''Y'', ' ||
5258 '''N'') intra_tomove_flg, ' ||
5259 'decode(wip1.reject_enabled_flag, ' ||
5260 '1, ' ||
5261 '''Y'', ' ||
5262 '''N'') intra_reject_flg, ' ||
5263 'decode(wip1.scrap_enabled_flag, ' ||
5264 '1, ' ||
5265 '''Y'', ' ||
5266 '''N'') intra_scrap_flg, ' ||
5267 'decode(wip1.mandatory_scrap_flag, ' ||
5268 '1, ' ||
5269 '''Y'', ' ||
5270 '''N'') req_scrap_acct, ' ||
5271 'mtp.organization_code org_code, ' ||
5272 'mpm.organization_code mast_org_code, ' ||
5273 'mpc.organization_code cost_org_code, ' ||
5274 'wip1.default_overcompl_tolerance, ' ||
5275 'wip1.production_scheduler, ' ||
5276 'wip1.shipping_manager, ' ||
5277 'mlu6.meaning use_finite_scheduler, ' ||
5278 'wip1.use_finite_scheduler use_finite_scheduler_code, ' ||
5279 'wip1.horizon_length, ' ||
5280 'wip1.default_scrap_account_id, ' ||
5281 'wip1.simulation_set, ' ||
5282 'wip1.component_atp_rule_name, ' ||
5283 'wip1.osp_shop_floor_status, ' ||
5284 'wip1.po_creation_time, ' ||
5285 'decode(wip1.material_constrained, ' ||
5286 '1, ' ||
5287 '''Resource and Material'', ' ||
5288 '2, ' ||
5289 '''Resource Only'', ' ||
5290 'wip1.material_constrained ) material_constrained, ' ||
5291 'milk.concatenated_segments locator, ' ||
5292 'decode(wip1.lot_verification, ' ||
5293 '0, ' ||
5294 '''All'', ' ||
5295 '1, ' ||
5296 '''Exceptions Only'', ' ||
5297 'wip1.lot_verification ) lot_verification, ' ||
5298 'gcck.concatenated_segments default_scrap_account, ' ||
5299 'mtp.primary_cost_method primary_cost_method_code, ' ||
5300 'wip2.system_option_id ' ||
5301 'FROM ' ||
5302 'mfg_lookups mlu1, ' ||
5303 'mfg_lookups mlu2, ' ||
5304 'mfg_lookups mlu3, ' ||
5305 'mfg_lookups mlu4, ' ||
5306 'mfg_lookups mlu5, ' ||
5307 'mfg_lookups mlu6, ' ||
5308 'mtl_parameters mtp, ' ||
5309 'mtl_parameters mpm, ' ||
5310 'mtl_parameters mpc, ' ||
5314 'gl_code_combinations_kfv gcck ' ||
5311 'wip_parameters_v wip1, ' ||
5312 'wip_parameters wip2, ' ||
5313 'mtl_item_locations_kfv milk, ' ||
5315 'WHERE ' ||
5316 'gcck.code_combination_id(+) = wip1.default_scrap_account_id AND ' ||
5317 'milk.inventory_location_id(+) = wip1.default_pull_supply_locator_id AND ' ||
5318 'mlu1.lookup_type = ''MTL_PRIMARY_COST'' AND ' ||
5319 'mlu1.lookup_code = nvl(mtp.primary_cost_method,-1) AND ' ||
5320 'mlu2.lookup_type = ''WIP_LOT_NUMBER_DEFAULT'' AND ' ||
5321 'mlu2.lookup_code = nvl(wip1.lot_number_default_type,-1) AND ' ||
5322 'mlu3.lookup_type = ''WIP_SO_CHANGE_TYPE'' AND ' ||
5323 'mlu3.lookup_code = nvl(wip1.so_change_response_type,-1) AND ' ||
5324 'mlu4.lookup_type = ''WIP_REPETITIVE_VARIANCE_TYPE'' AND ' ||
5325 'mlu4.lookup_code = nvl(wip1.repetitive_variance_type,-1) AND ' ||
5326 'mlu5.lookup_type = ''WIP_BACKFLUSH_LOT_ENTRY'' AND ' ||
5327 'mlu5.lookup_code = nvl(wip1.backflush_lot_entry_type,-1) AND ' ||
5328 'mlu6.lookup_code = wip1.use_finite_scheduler AND ' ||
5329 'mlu6.lookup_type = ''SYS_YES_NO'' AND ' ||
5330 'mtp.cost_organization_id = mpc.organization_id AND ' ||
5331 'mtp.master_organization_id = mpm.organization_id AND ' ||
5332 'mtp.organization_id = wip1.organization_id AND ' ||
5333 'wip2.rowid = wip1.row_id AND ' ||
5334 'wip1.organization_id = ' || p_org_id ;
5335
5336
5337
5338
5339
5340 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'WIP Parameters SETUP',null,'Y',row_limit);
5341
5342 IF (dummy_num = row_limit) THEN
5343 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5344 END IF;
5345
5346 if dummy_num = 0 then
5347 reportStr := 'There are no work in process parameters defined for this organization';
5348
5349 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr) ;
5350 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please see the' , l_url, l_desc, ' for more information on how to setup these parameter values') ;
5351 else
5352 open wip_param_v_csr ;
5353 fetch wip_param_v_csr into wip_param_v_rec ;
5354 close wip_param_v_csr ;
5355
5356 open wip_param_csr ;
5357 fetch wip_param_csr into wip_param_rec ;
5358 close wip_param_csr ;
5359
5360 open inv_param_csr ;
5361 fetch inv_param_csr into inv_param_rec ;
5362 close inv_param_csr ;
5363
5364 if (inv_param_rec.primary_cost_method = 2 ) then -- Average Costing
5365
5366 JTF_DIAGNOSTIC_COREAPI.SectionPrint('Cost Method Average Parameters' ) ;
5367 JTF_DIAGNOSTIC_COREAPI.Line_Out('Default Completion Cost Source = ' || wip_param_v_rec.completion_cost_source_meaning || '<BR>' );
5368 JTF_DIAGNOSTIC_COREAPI.Line_Out('System Option = ' || wip_param_v_rec.system_option || '<BR>');
5369 JTF_DIAGNOSTIC_COREAPI.Line_Out('Cost Type = ' || wip_param_v_rec.cost_type || '<BR>');
5370
5371
5372 if (wip_param_rec.system_option_id = 2 ) then
5373 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink('This setting will cause lot based resources and overheads to be over-relieved. Select ''Use Actual Resources '' to avoid this problem.<BR> Please see the ', l_url, l_desc , 'for more information') ;
5374 end if ;
5375
5376 if (wip_param_v_rec.completion_cost_source_meaning is null or
5377 wip_param_v_rec.system_option is null or
5378 wip_param_v_rec.cost_type is null or
5379 wip_param_v_rec.auto_compute_final_completion is null) then
5380
5381 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink('Parameters not set for average cost method. Please see the ', l_url, l_desc, ' for information on how to setup WIP parameters') ;
5382 end if ;
5383 end if;
5384 end if ;
5385 end checkWIPDetails ;
5386
5387 procedure checkAtoAttributes is
5388 begin
5389 SELECT count(*)
5390 into l_count
5391 FROM dual
5392 WHERE exists (
5393 SELECT * FROM mtl_system_items mtl
5394 WHERE mtl.replenish_to_order_flag = 'Y'
5395 AND mtl.build_in_wip_flag = 'Y'
5396 AND mtl.wip_supply_type in (1,2,6)
5397 AND mtl.bom_item_type in (1,2,4)
5398 AND mtl.bom_enabled_flag = 'Y'
5399 AND mtl.organization_id = p_org_id) ;
5400
5401
5402 -- JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5403 -- JTF_DIAGNOSTIC_COREAPI.Tab2Print('There are ' || l_count || ' ATO items defined <BR> ');
5404
5405 if l_count = 0 then
5406 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink('Please refer ', 111874.1, ' for information on how to setup ATO parameters') ;
5407 end if;
5408
5409 end CheckAtoAttributes ;
5410
5411 procedure checkCostGroup is
5412
5413
5414 cursor cost_grp_csr is
5415 SELECT cst.cost_group
5416 FROM CST_COST_GROUPS_V cst
5417 WHERE cst.organization_id = p_org_id ;
5418 begin
5419
5420 l_found := 0 ;
5421
5422 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5423
5424 for cost_grp_rec in cost_grp_csr loop
5428
5425 JTF_DIAGNOSTIC_COREAPI.Tab2Print('Cost Group ' || cost_grp_rec.cost_group || ' <BR> ');
5426 l_found := 1 ;
5427 end loop ;
5429 if l_found = 0 then
5430
5431 reportStr := 'No cost group defined for this organization. A cost group must be setup.' ;
5432 reportStr := reportStr || 'Then assign the WIP accounting class needed for the cost group' ;
5433
5434 /*
5435 JTF_DIAGNOSTIC_COREAPI.Tab2Print('No cost group defined for this organization. A cost group must be setup <BR>');
5436 JTF_DIAGNOSTIC_COREAPI.Tab2Print('Then assign the WIP accounting class needed for the cost group<BR>');
5437 */
5438
5439 JTF_DIAGNOSTIC_COREAPI.WarningPrint(reportStr) ;
5440
5441 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink('Please refer to note:', '1079196.6', 'for more information') ;
5442 end if ;
5443
5444 end checkCostGroup ;
5445
5446 procedure checkOpMove is
5447
5448 l_string varchar2(20) ;
5449 cursor op_move_csr is
5450 SELECT wsfsc.shop_floor_status_code ,
5451 wsfsc.status_move_flag ,
5452 ml.meaning ,
5453 wsfsc.status_move_flag status_move_flag_code
5454 FROM wip_shop_floor_status_codes wsfsc ,
5455 mfg_lookups ml
5456 WHERE ml.lookup_type = 'SYS_YES_NO'
5457 AND ml.lookup_code = wsfsc.status_move_flag
5458 AND organization_id = p_org_id ;
5459
5460 begin
5461
5462 l_found := 0 ;
5463
5464 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5465
5466 for op_move_rec in op_move_csr loop
5467 l_found := 1 ;
5468 if op_move_rec.status_move_flag_code = 1 then
5469 l_string := 'Allowed' ;
5470 else
5471 l_string := 'Not Allowed' ;
5472 end if ;
5473
5474 JTF_DIAGNOSTIC_COREAPI.Tab2Print('Operation Moves ' || l_string || ' for shop floor status '
5475 || op_move_rec.shop_floor_status_code);
5476 end loop ;
5477
5478 if l_found = 0 then
5479 reportStr := 'No operation moves allowed with pending move transaction for this organization.';
5480 reportStr := reportStr || 'There is no move shop floor status created or being used. This is the intended functionality if the shop floor status is not used.' ;
5481
5482 JTF_DIAGNOSTIC_COREAPI.WarningPrint(reportStr) ;
5483 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink('Please refer to note', 165224.1, 'for more information on how to setup these parameter values') ;
5484
5485 end if ;
5486 end checkOpMove;
5487
5488 procedure checkEmpRate is
5489 begin
5490
5491 l_count := 0 ;
5492
5493 SELECT count(wipl.employee_id)
5494 into l_count
5495 FROM wip_employee_labor_rates wipl
5496 WHERE wipl.organization_id = p_org_id ;
5497
5498 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5499 JTF_DIAGNOSTIC_COREAPI.Tab2Print('There are ' || l_count || ' employees defined');
5500
5501 if l_count = 0 then
5502
5503 reportStr := 'There are no employee labor rates defined for this organization' ;
5504 JTF_DIAGNOSTIC_COREAPI.WarningPrint(reportStr) ;
5505
5506 reportStr := 'Please define employee labor rates for manual resource charge. ' ;
5507 reportStr := reportStr || 'Please refer to note' ;
5508
5509 JTF_DIAGNOSTIC_COREAPI.ActionWarningLink(reportStr, 157959.1, ' for more information') ;
5510
5511 end if ;
5512
5513
5514 end checkEmpRate ;
5515
5516 procedure checkAccClass is
5517 row_limit number;
5518 begin
5519 row_limit := 1000;
5520 sqltxt :=
5521
5522 ' SELECT ' ||
5523 ' wac.class_code, ' ||
5524 ' wac.organization_id, ' ||
5525 ' ml1.meaning, ' ||
5526 ' wac.description, ' ||
5527 ' wac.disable_date, ' ||
5528 ' gcc1.concatenated_segments material_account, ' ||
5529 ' gcc2.concatenated_segments material_variance_account, ' ||
5530 ' gcc3.concatenated_segments material_overhead_account, ' ||
5531 ' gcc4.concatenated_segments resource_account, ' ||
5532 ' gcc5.concatenated_segments resource_variance_account, ' ||
5533 ' gcc6.concatenated_segments outside_processing_account, ' ||
5534 ' gcc7.concatenated_segments outside_proc_variance_account, ' ||
5535 ' gcc8.concatenated_segments overhead_account, ' ||
5536 ' gcc9.concatenated_segments overhead_variance_account, ' ||
5537 ' gcc10.concatenated_segments std_cost_adjustment_account, ' ||
5538 ' wac.completion_cost_source, ' ||
5539 ' wac.cost_type_id, ' ||
5540 ' gcc11.concatenated_segments bridging_account, ' ||
5541 ' wac.system_option_id, ' ||
5542 ' gcc12.concatenated_segments expense_account, ' ||
5543 ' gcc13.concatenated_segments est_scrap_account, ' ||
5544 ' gcc14.concatenated_segments est_scrap_var_account ' ||
5545 ' FROM ' ||
5546 ' wip_accounting_classes wac, ' ||
5547 ' mfg_lookups ml1, ' ||
5548 ' gl_code_combinations_kfv gcc1, ' ||
5549 ' gl_code_combinations_kfv gcc2, ' ||
5550 ' gl_code_combinations_kfv gcc3, ' ||
5551 ' gl_code_combinations_kfv gcc4, ' ||
5552 ' gl_code_combinations_kfv gcc5, ' ||
5556 ' gl_code_combinations_kfv gcc9, ' ||
5553 ' gl_code_combinations_kfv gcc6, ' ||
5554 ' gl_code_combinations_kfv gcc7, ' ||
5555 ' gl_code_combinations_kfv gcc8, ' ||
5557 ' gl_code_combinations_kfv gcc10, ' ||
5558 ' gl_code_combinations_kfv gcc11, ' ||
5559 ' gl_code_combinations_kfv gcc12, ' ||
5560 ' gl_code_combinations_kfv gcc13, ' ||
5561 ' gl_code_combinations_kfv gcc14 ' ||
5562 ' WHERE ' ||
5563 ' wac.organization_id = ' || p_org_id || ' AND ' ||
5564 ' wac.disable_date is null AND ' ||
5565 ' ml1.lookup_code = wac.class_type AND ' ||
5566 ' ml1.lookup_type = ''WIP_CLASS_TYPE'' AND ' ||
5567 ' gcc1.code_combination_id(+) = wac.material_account AND ' ||
5568 ' gcc2.code_combination_id(+) = wac.material_variance_account AND ' ||
5569 ' gcc3.code_combination_id(+) = wac.material_overhead_account AND ' ||
5570 ' gcc4.code_combination_id(+) = wac.resource_account AND ' ||
5571 ' gcc5.code_combination_id(+) = wac.resource_variance_account AND ' ||
5572 ' gcc6.code_combination_id(+) = wac.outside_processing_account AND ' ||
5573 ' gcc7.code_combination_id(+) = wac.outside_proc_variance_account AND ' ||
5574 ' gcc8.code_combination_id(+) = wac.overhead_account AND ' ||
5575 ' gcc9.code_combination_id(+) = wac.overhead_variance_account AND ' ||
5576 ' gcc10.code_combination_id(+) = wac.std_cost_adjustment_account AND ' ||
5577 ' gcc11.code_combination_id(+) = wac.bridging_account AND ' ||
5578 ' gcc12.code_combination_id(+) = wac.expense_account AND ' ||
5579 ' gcc13.code_combination_id(+) = wac.est_scrap_account AND ' ||
5580 ' gcc14.code_combination_id(+) = wac.est_scrap_var_account' ;
5581
5582 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5583 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'WIP Accounting Classes Setup',null,'Y',row_limit);
5584
5585 IF (dummy_num = row_limit) THEN
5586 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5587 END IF;
5588
5589 if dummy_num = 0 then
5590
5591
5592 reportStr := 'No accounting classes defined for this organization.' ;
5593
5594 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr) ;
5595
5596 reportStr := 'Please define the wip accounting classes for this organization. Please see the';
5597
5598 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink(reportStr, l_url, l_desc ,' for more information') ;
5599
5600 end if ;
5601
5602 end checkAccClass ;
5603
5604 procedure checkBomParams is
5605 row_limit number;
5606 begin
5607 row_limit := 1000;
5608 sqltxt :=
5609 'SELECT ml1.meaning use_phantom_routings ' ||
5610 ',ml2.meaning inherit_phantom_op_seq ' ||
5611 'FROM bom_parameters bp, ' ||
5612 'mfg_lookups ml1, ' ||
5613 'mfg_lookups ml2 ' ||
5614 'WHERE bp.organization_id = ' || p_org_id ||
5615 'AND ml1.lookup_code = bp.use_phantom_routings ' ||
5616 'AND ml1.lookup_type = ''SYS_YES_NO'' ' ||
5617 'AND ml2.lookup_code = bp.inherit_phantom_op_seq ' ||
5618 'AND ml2.lookup_type = ''SYS_YES_NO'' ';
5619
5620 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5621 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'BOM Parameters',null,'Y',row_limit);
5622
5623 IF (dummy_num = row_limit) THEN
5624 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5625 END IF;
5626
5627 end checkBomParams ;
5628
5629 procedure checkWipProdLines is
5630 row_limit number;
5631 begin
5632 row_limit := 1000;
5633 sqltxt :=
5634 'SELECT ' ||
5635 'wl.line_id, ' ||
5636 'wl.organization_id, ' ||
5637 'wl.line_code, ' ||
5638 'wl.description, ' ||
5639 'wl.disable_date, ' ||
5640 'wl.minimum_rate, ' ||
5641 'wl.maximum_rate, ' ||
5642 'wl.fixed_throughput, ' ||
5643 'ml1.meaning, ' ||
5644 'to_char(to_date(wl.start_time, ' ||
5645 '''SSSSS''), ' ||
5646 '''HH24:MI:SS'') start_time, ' ||
5647 'to_char(to_date(wl.stop_time, ' ||
5648 '''SSSSS''), ' ||
5649 '''HH24:MI:SS'') stop_time, ' ||
5650 'wl.scheduling_method_id, ' ||
5651 'wl.atp_rule_id, ' ||
5652 'wl.exception_set_name, ' ||
5653 'mar.rule_name, ' ||
5654 'wl.line_schedule_type ' ||
5655 'FROM ' ||
5656 'wip_lines wl, ' ||
5657 'mfg_lookups ml1, ' ||
5658 'mtl_atp_rules mar ' ||
5659 'WHERE ' ||
5660 'ml1.lookup_code = wl.line_schedule_type AND ' ||
5661 'ml1.lookup_type = ''WIP_LINE_SCHED_TYPE'' AND ' ||
5662 'wl.organization_id = ' || p_org_id || ' AND ' ||
5663 'mar.rule_id(+) = wl.atp_rule_id';
5664
5665 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5666 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Work In Process Production Lines ',null,'Y',row_limit);
5667
5671
5668 IF (dummy_num = row_limit) THEN
5669 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5670 END IF;
5672 if dummy_num = 0 then
5673 JTF_DIAGNOSTIC_COREAPI.Tab2Print('No production lines have been defined <BR>');
5674 end if ;
5675
5676 end checkWipProdLines ;
5677
5678 procedure checkWipSchGrps is
5679 row_limit number;
5680 begin
5681 row_limit := 1000;
5682 sqltxt :=
5683 'SELECT wsg.schedule_group_name ,' ||
5684 ' wsg.description,' ||
5685 ' wsg.inactive_on ' ||
5686 ' FROM wip_schedule_groups wsg ' ||
5687 ' WHERE wsg.organization_id = ' || p_org_id ;
5688
5689 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5690 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'In Process Schedule Groups',null,'Y',row_limit);
5691
5692 IF (dummy_num = row_limit) THEN
5693 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5694 END IF;
5695
5696 end checkWipSchGrps ;
5697
5698 procedure displayConcLibProcess (p_lib_name varchar2) is
5699 row_limit number;
5700 begin
5701 row_limit := 1000;
5702 sqltxt :=
5703 'SELECT ' ||
5704 'substr(fcp.concurrent_processor_name, ' ||
5705 '1, ' ||
5706 '20) NAME, ' ||
5707 'substr(fcq.user_concurrent_queue_name, ' ||
5708 '1, ' ||
5709 '20) USER_NAME, ' ||
5710 'nvl(fcq.target_node, ' ||
5711 '''n/a'') NODE, ' ||
5712 'fcq.running_processes ACTUAL, ' ||
5713 'fcq.max_processes TARGET, ' ||
5714 'nvl(fl1.meaning, ' ||
5715 '''Active'') STATUS, ' ||
5716 'fcq.control_code control_code ' ||
5717 'FROM ' ||
5718 'fnd_concurrent_queues_vl fcq, ' ||
5719 'fnd_application_vl fa, ' ||
5720 'fnd_concurrent_processors fcp, ' ||
5721 'fnd_lookups fl1 ' ||
5722 'WHERE ' ||
5723 'fcp.concurrent_processor_name =''' || p_lib_name || ''' and ' ||
5724 'fcq.enabled_flag = ''Y'' and ' ||
5725 'fl1.lookup_type(+) = ''CP_CONTROL_CODE'' and ' ||
5726 'fcq.control_code = fl1.lookup_code (+) and ' ||
5727 'fa.application_id = fcq.application_id and ' ||
5728 'fcq.application_id = fcp.application_id and ' ||
5729 'fcq.concurrent_processor_id = fcp.concurrent_processor_id ' ||
5730 'ORDER BY decode(fcq.application_id,0,decode(fcq.concurrent_queue_id,1,1,4,2)), ' ||
5731 'sign(fcq.max_processes) desc, ' ||
5732 'fcq.concurrent_queue_name, ' ||
5733 'fcq.application_id';
5734
5735 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5736 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Concurrent Manager',null,'Y',row_limit);
5737
5738 IF (dummy_num = row_limit) THEN
5739 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5740 END IF;
5741
5742 end displayConcLibProcess ;
5743
5744 procedure checkConcProcess (p_conc_name varchar2) is
5745 row_limit number;
5746 begin
5747 row_limit := 1000;
5748 sqltxt :=
5749 'SELECT ' ||
5750 'p.user_concurrent_program_name, ' ||
5751 'r.phase_code, ' ||
5752 'r.actual_completion_date ' ||
5753 'FROM ' ||
5754 'fnd_concurrent_requests r, ' ||
5755 'fnd_concurrent_programs_vl p ' ||
5756 'WHERE ' ||
5757 'p.concurrent_program_name = ''' || p_conc_name || ''' and ' ||
5758 'p.concurrent_program_id = r.concurrent_program_id(+) ' ||
5759 'order by nvl(r.hold_flag,''N''), ' ||
5760 'r.actual_completion_date desc';
5761
5762 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5763 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Concurrent Manager',null,'Y',row_limit);
5764
5765 IF (dummy_num = row_limit) THEN
5766 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5767 END IF;
5768 end checkConcProcess;
5769
5770 procedure docReferences is
5771 reportStr LONG ;
5772 l_doc_url varchar2(255) ;
5773 l_note varchar2(255) ;
5774
5775 begin
5776
5777 l_doc_url := 'http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&' ;
5778
5779 l_note := l_doc_url || 'p_id=415922.1' ;
5780 reportStr := 'Note : <a href= ' || l_url || '> 415922.1 </a>' || ' : RELEASE CONTENT DOCUMENT - Release 12 Discrete Manufacturing - Support Enhanced Version <BR>' ;
5781 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport(reportStr);
5782
5783 end docReferences ;
5784
5785 function get_org(p_org_id in NUMBER) return varchar2 is
5786 l_org_code varchar2(255) ;
5787 begin
5788 select organization_code
5789 into l_org_code
5790 from org_organization_definitions
5791 where organization_id = p_org_id ;
5792
5793 return l_org_code ;
5794 end ;
5795
5796 BEGIN
5797
5798
5799
5800
5801
5802 /*
5803 sqltxt := 'select default_discrete_class,'||
5804 ' decode(lot_number_default_type,'||
5805 ' 1,''Job Name'','||
5806 ' 2,''Based On Inventory Rules'','||
5807 ' 3,''No Default'', lot_number_default_type) lot_number_default_type,'||
5808 ' decode(so_change_response_type,'||
5809 ' 1,''Never'','||
5810 ' 2,''Always'','||
5811 ' 3,''When linked 1-1Default'') so_change_response_type,'||
5812 ' decode(mandatory_scrap_flag,1,''Yes'',2,''No'') Mandatory_Scrap_Flag,'||
5813 ' decode(dynamic_operation_insert_flag,1,''Yes'',2,''No'') Dynamic_Oprn_Insert_Flag,'||
5814 ' decode(moves_over_no_move_statuses,1,''Yes'',2,''No'') Moves_Over_No_Move_Status,'||
5815 ' default_pull_supply_subinv,'||
5816 ' default_pull_supply_locator_id,'||
5817 ' decode(backflush_lot_entry_type,'||
5818 ' 1, ''Manual'','||
5819 ' 2, ''Receipt Date'','||
5820 ' 4, ''Expiration Date'','||
5821 ' 6, ''Transaction History'', backflush_lot_entry_type) Lot_Selection_Method ,' ;
5822 if (l_release_name = '11.5.10') then
5823 null ;
5824 elsif (l_release_name = '11.5.9') then
5825 null ;
5826 elsif (l_release_name = '11.5.10.2') then
5827 sqltxt := sqltxt ||
5828 ' decode(alternate_lot_selection_method,'||
5829 ' 1, ''Manual'','||
5830 ' 2, ''Receipt Date'','||
5831 ' 4, ''Expiration Date'' , alternate_lot_selection_method) Alternate_Lot_Selection_Method,' ;
5832 end if ;
5833
5834 sqltxt := sqltxt ||
5835
5836 ' decode(allocate_backflush_components,''1'',''Yes'',''2'',''No'') Allocate_Backflush_Comps,'||
5837 ' decode(allow_backflush_qty_change,1,''Yes'',2,''No'') Allow_Backflush_Qty_Change,'||
5838 ' decode(repetitive_variance_type,1, ''All Schedules '', 2, ''Cancelled and Complete-No Charges Only'') RepVariance, ' ||
5839 ' autorelease_days,'||
5840 ' osp_shop_floor_status,'||
5841 ' decode(po_creation_time,'||
5842 ' 1, ''At Job/Schedule Release'','||
5843 ' 2, ''At Operation'','||
5844 ' 3, ''Manual'', po_creation_time) PO_Creation_Time,'||
5845 ' default_overcompl_tolerance,'||
5846 ' production_scheduler_id,'||
5847 ' decode(material_constrained,1,''Yes'',2,''No'') Material_Constrained,'||
5848 ' decode(use_finite_scheduler,1,''Yes'',2,''No'') Use_Finite_Scheduler'||
5849 ' from wip_parameters'||
5850 ' where organization_id = ' || p_org_id ;
5851
5852 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
5853 'WIP Parameters SETUP');
5854
5855
5856
5857 */
5858 checkWipProfiles ;
5859 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5860
5861 JTF_DIAGNOSTIC_COREAPI.SectionPrint('Work In Process Organization ' || get_org(p_org_id) ) ;
5862 checkWIPDetails ;
5863 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5864
5865 JTF_DIAGNOSTIC_COREAPI.SectionPrint('Item Attributes for Assemble To Order (ATO) discrete jobs') ;
5866 checkAtoAttributes ;
5867 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5868
5869 JTF_DIAGNOSTIC_COREAPI.SectionPrint('Cost Group Setup for WIP Parameters') ;
5870 checkCostGroup ;
5871 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5872
5873 JTF_DIAGNOSTIC_COREAPI.SectionPrint('Operation moves with Pending Move Transactions') ;
5874 checkOpMove;
5875 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5876
5877 JTF_DIAGNOSTIC_COREAPI.SectionPrint('WIP Employee Labor Rates Definition') ;
5878 checkEmpRate;
5879 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5880
5881 -- JTF_DIAGNOSTIC_COREAPI.SectionPrint('WIP Accounting Classes Setup') ;
5882 checkAccClass;
5883 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5884
5885 -- JTF_DIAGNOSTIC_COREAPI.SectionPrint('BOM Parameters') ;
5886 checkBomParams;
5887 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5888
5889 -- JTF_DIAGNOSTIC_COREAPI.SectionPrint('Work In Process Production Lines') ;
5890 checkWipProdLines;
5891 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5892
5893 -- JTF_DIAGNOSTIC_COREAPI.SectionPrint('In Process Schedule Groups') ;
5894 checkWipSchGrps;
5895 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5896
5897 /*
5898 JTF_DIAGNOSTIC_COREAPI.Tab1Print('MRCLIB') ;
5899 displayConcLibProcess('MRCLIB');
5900 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5901
5902 JTF_DIAGNOSTIC_COREAPI.Tab1Print('MRCRLF') ;
5903 checkConcProcess('MRCRLF');
5904 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5905 */
5906
5907 JTF_DIAGNOSTIC_COREAPI.SectionPrint('References') ;
5908 docReferences;
5909 JTF_DIAGNOSTIC_COREAPI.BRPrint ;
5910
5911 EXCEPTION
5912 WHEN OTHERS then
5913
5914 if wip_param_csr%ISOPEN then
5915 close wip_param_csr ;
5916 end if ;
5917
5918 if wip_param_v_csr%ISOPEN then
5919 close wip_param_v_csr ;
5920 end if ;
5921
5922 if inv_param_csr%ISOPEN then
5923 close inv_param_csr ;
5924 end if ;
5925 END setup ;
5926
5927 procedure Pending_txns (p_org_id IN NUMBER) is
5928 row_limit number;
5929 BEGIN
5930 row_limit := 1000;
5931
5932 JTF_DIAGNOSTIC_COREAPI.Display_table('WIP_COST_TXN_INTERFACE', 'Pending Resource Transactions',
5933 'where organization_id = ' || p_org_id,
5934 'order by transaction_date',
5935 'N'
5936 );
5937 sqltxt :=
5938 'select wi.transaction_date,' ||
5939 ' wi.transaction_id,' ||
5940 ' wi.wip_entity_id,' ||
5941 ' wi.wip_entity_name,' ||
5942 ' wi.process_phase,' ||
5943 ' wi.process_status,' ||
5944 ' wtie.error_column,' ||
5945 ' wtie.error_message ' ||
5946 'from wip_cost_txn_interface wi,' ||
5947 ' wip_txn_interface_errors wtie ' ||
5948 'where wi.transaction_id = wtie.transaction_id ' ||
5949 'and wi.organization_id = ' || p_org_id ||
5950 ' order by wi.transaction_date, wi.wip_entity_id, wi.wip_entity_name' ;
5951
5952 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Pending Resource Transactions Errors',null,'Y',row_limit) ;
5953
5954 IF (dummy_num = row_limit) THEN
5955 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5956 END IF;
5957
5958 JTF_DIAGNOSTIC_COREAPI.Display_table('WIP_MOVE_TXN_INTERFACE', 'Pending Move Transactions',
5959 'where organization_id = ' || p_org_id,
5960 'order by transaction_date',
5961 'N'
5962 );
5963 sqltxt :=
5964 'select wi.transaction_date,' ||
5965 ' wi.transaction_id,' ||
5966 ' wi.wip_entity_id,' ||
5967 ' wi.wip_entity_name,' ||
5968 ' wi.process_phase,' ||
5969 ' wi.process_status,' ||
5970 ' wtie.error_column,' ||
5971 ' wtie.error_message ' ||
5972 'from wip_move_txn_interface wi,' ||
5973 ' wip_txn_interface_errors wtie ' ||
5974 'where wi.transaction_id = wtie.transaction_id ' ||
5975 'and wi.organization_id = ' || p_org_id ||
5976 ' order by wi.transaction_date, wi.wip_entity_id, wi.wip_entity_name' ;
5977
5978 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Pending Move Transactions Errors',null,'Y',row_limit) ;
5979
5980 IF (dummy_num = row_limit) THEN
5981 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
5982 END IF;
5983
5984 JTF_DIAGNOSTIC_COREAPI.Display_table('WIP_JOB_SCHEDULE_INTERFACE', 'Pending Jobs ',
5985 'where organization_id = ' || p_org_id,
5986 'order by group_id, header_id, interface_id',
5987 'N'
5988 );
5989 sqltxt :=
5990 'select wjsi.interface_id,' ||
5991 ' wjsi.group_id,' ||
5992 ' wjsi.wip_entity_id,' ||
5993 ' wjsi.job_name,' ||
5994 ' wjsi.load_type,' ||
5995 ' wjsi.process_phase,' ||
5996 ' wjsi.process_status,' ||
5997 ' wie.error_type,' ||
5998 ' wie.error ' ||
5999 'from wip_job_schedule_interface wjsi,' ||
6000 ' wip_interface_errors wie ' ||
6001 'where wjsi.interface_id = wie.interface_id ' ||
6002 'and wjsi.organization_id = ' || p_org_id ||
6003 ' order by wjsi.group_id, wjsi.interface_id, wjsi.wip_entity_id, wjsi.job_name' ;
6004
6005 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt, 'Pending Jobs Error',null,'Y',row_limit) ;
6006
6007 IF (dummy_num = row_limit) THEN
6008 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
6009 END IF;
6010
6011 JTF_DIAGNOSTIC_COREAPI.Display_table('MTL_TRANSACTIONS_INTERFACE', 'Transactions Open Interface',
6012 'where transaction_source_type_id = 5 and organization_id = ' || p_org_id ||
6013 ' and process_flag = 3',
6014 'order by transaction_date',
6015 'N'
6016 );
6017 JTF_DIAGNOSTIC_COREAPI.Display_table('MTL_MATERIAL_TRANSACTIONS_TEMP', 'Pending Material Transactions',
6018 'where transaction_source_type_id = 5 and organization_id = ' || p_org_id ||
6019 ' and process_flag = ''E'' ' ,
6020 'order by transaction_date',
6021 'N'
6022 );
6023
6024 JTF_DIAGNOSTIC_COREAPI.Display_table('MTL_MATERIAL_TRANSACTIONS', 'Material Transactions',
6025 'where transaction_source_type_id = 5 and organization_id = ' || p_org_id ||
6026 ' and costed_flag = ''E'' ' ,
6027 'order by transaction_date',
6028 'N'
6029 );
6030
6031
6032 /*
6033
6034 sqltxt := 'select * from wip_cost_txn_interface where organization_id = ' || p_org_id ;
6035 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6036 'Pending Resource Transactions');
6037 sqltxt := 'select * from wip_job_schedule_interface where organization_id = ' || p_org_id ;
6038 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6039 'Pending Mass Load Transactions');
6040 sqltxt := 'select * from wip_job_dtls_interface ' ;
6041 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6042 'Mass Load Errors');
6043 sqltxt := 'select * from wip_interface_errors ';
6044 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6045 ' Interface Errors');
6046 sqltxt := 'select * from wip_move_txn_interface where organization_id = ' || p_org_id ;
6047 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6048 'Pending Move Transactions');
6049 sqltxt := 'select * from mtl_transactions_interface where transaction_source_type_id = 5 and organization_id = ' || p_org_id ;
6050 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6051 'Transaction Open Interface (MTI)');
6052 sqltxt := 'select * from mtl_material_transactions_temp where transaction_source_type_id = 5 and organization_id = ' || p_org_id ;
6053 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
6054 'Pending Material Transactions (MMTT)');
6055 */
6056
6057 END Pending_txns ;
6058 BEGIN
6059 -- Get Release level and store it in release_level
6060 l_result := fnd_release.get_release(release_level, other_info) ;
6061
6062 END WIP_DIAG_DATA_COLL ;