[Home] [Help]
PACKAGE BODY: APPS.WIP_DIAG_JOB_SCH_HC
Source
1 package body WIP_DIAG_JOB_SCH_HC as
2 /* $Header: WIPDDEFB.pls 120.0.12020000.3 2012/11/29 05:42:22 sisankar ship $ */
3 PROCEDURE invalid_job_def_job(inputs IN JTF_DIAG_INPUTTBL,
4 report OUT NOCOPY JTF_DIAG_REPORT,
5 reportClob OUT NOCOPY CLOB) IS
6 reportStr LONG; -- REPORT
7 sqltxt VARCHAR2(9999); -- SQL select statement
8 c_username VARCHAR2(50); -- accept input for username
9 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
10 errStr VARCHAR2(4000); -- error message
11 fixInfo VARCHAR2(4000); -- fix tip
12 isFatal VARCHAR2(50); -- TRUE or FALSE
13 dummy_num NUMBER;
14 row_limit NUMBER;
15 l_job_id NUMBER;
16 l_org_id NUMBER;
17 l_cutoff_date VARCHAR2(50);
18 we_dyn_where_clause VARCHAR2(1000):= null;
19 wdj_dyn_where_clause VARCHAR2(1000) := null;
20 wo_dyn_where_clause VARCHAR2(1000) := null;
21 wdj_atc_where_clause VARCHAR2(1000):= null;
22 --Bug#15877953: Fetching the columns using user_synonyms
23 CURSOR l_trail_space(l_table_owner varchar2) IS SELECT col.column_name
24 FROM user_synonyms syn, all_tab_columns col
25 WHERE syn.synonym_name = 'WIP_DISCRETE_JOBS'
26 AND syn.table_owner=l_table_owner
27 AND col.owner=syn.table_owner
28 AND col.table_name = syn.table_name
29 AND col.data_type = 'VARCHAR2';
30
31 a NUMBER:=0;
32 sqltext VARCHAR2(9999) :=NULL;
33 sqltext1 VARCHAR2(9999):=NULL;
34 sqltext2 VARCHAR2(9999):= NULL;
35 l_return_status boolean;
36 p_status varchar2(3);
37 p_industry varchar2(30);
38 p_table_owner varchar2(30);
39 BEGIN
40 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
41 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
42 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
43 row_limit := 1000;
44 -- accept input
45 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Organization Id',inputs);
46 l_job_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Job Id',inputs);
47 --l_cutoff_date := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('CutoffDate',inputs);
48
49 if l_org_id is not null then
50 we_dyn_where_clause := ' we.organization_id = '|| l_org_id || ' AND ';
51 wdj_dyn_where_clause := ' wdj.organization_id = '|| l_org_id || ' AND ';
52 wo_dyn_where_clause := ' wo.organization_id = '|| l_org_id || ' AND ';
53 end if;
54 if l_job_id is not null then
55 we_dyn_where_clause := we_dyn_where_clause || ' we.wip_entity_id = '|| l_job_id || ' AND ';
56 wdj_dyn_where_clause := wdj_dyn_where_clause || ' wdj.wip_entity_id = '|| l_job_id || ' AND ';
57 wo_dyn_where_clause := wo_dyn_where_clause || ' wo.wip_entity_id = '|| l_job_id || ' AND ';
58 end if;
59 if l_cutoff_date is not null then
60 wo_dyn_where_clause := wo_dyn_where_clause || ' creation_date > Trunc(To_Date(' || l_cutoff_date || ',''dd-mon-yyyy'')) AND ';
61 end if;
62
63
64 -- 1 This script will identify all released jobs that do not have Released Date populated.
65 sqltxt :=
66 'select we.wip_entity_name Job , we.wip_entity_id JobId, we.organization_id OrganizationID, '||
67 ' decode(wdj.status_type, '||
68 ' 1,''Unreleased'', '||
69 ' 3, ''Released'', '||
70 ' 4, ''Complete'', '||
71 ' 5, ''Complete NoCharge'', '||
72 ' 6, ''On Hold'', '||
73 ' 7, ''Cancelled'', '||
74 ' 8, ''Pend Bill Load'', '||
75 ' 9, ''Failed Bill Load'', '||
76 ' 10, ''Pend Rtg Load'', '||
77 ' 11, ''Failed Rtg Load'', '||
78 ' 12, ''Closed'', '||
79 ' 13, ''Pending- Mass Loaded'', '||
80 ' 14, ''Pending Close'', '||
81 ' 15, ''Failed Close'', '||
82 ' wdj.status_type) Status, wdj.DATE_RELEASED, wdj.DATE_CLOSED '||
83 'from wip_entities we, '||
84 ' wip_discrete_jobs wdj '||
85 'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
86 'and wdj.date_released is null '||
87 'and WDJ.STATUS_TYPE IN (3, 4, 5, 6, 14, 15) ';
88
89 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
90 'Jobs/Schedules with invalid Release Date.',true,null,'Y',row_limit);
91
92 IF (dummy_num = row_limit) THEN
93 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
94 END IF;
95
96 IF (dummy_num > 0) THEN
97 reportStr := 'The rows returned above signify that there are released jobs with null date released.';
98 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
99 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
100 END IF;
101
102
103 -- 2 This script will identify all jobs where quantity completed on job is not in sync with Inventory
104 sqltxt :=
105 'select substr(we.wip_entity_name, 1,15) Job, wdj.wip_entity_id JobId, we.organization_id OrganizationID, wdj.primary_item_id ItemId, wdj.start_quantity, '||
106 ' wdj.quantity_completed, wdj.quantity_scrapped, wdj.net_quantity, wdj.creation_date '||
107 'from wip_discrete_jobs wdj, wip_entities we '||
108 'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
109 'and exists (select 1 '||
110 ' from mtl_material_transactions mmt '||
111 ' where mmt.transaction_source_type_id = 5 '||
112 ' and mmt.transaction_source_id = wdj.wip_entity_id '||
113 ' and mmt.organization_id = wdj.organization_id '||
114 ' and mmt.inventory_item_id = wdj.primary_item_id '||
115 ' and mmt.transaction_action_id in (31,32)) '||
116 'and ((select sum(mmt.primary_quantity) '|| /*Bug 6049344: Replaced trx qty with primary qty*/
117 ' from mtl_material_transactions mmt '||
118 ' where mmt.transaction_source_type_id = 5 '||
119 ' and mmt.transaction_source_id = wdj.wip_entity_id '||
120 ' and mmt.organization_id = wdj.organization_id '||
121 ' and mmt.inventory_item_id = wdj.primary_item_id '||
122 ' and mmt.transaction_action_id in (31,32)) - quantity_completed) >0.00001 order by we.wip_entity_id '; /*FP of Bug#12727085 (Bug#13643469 ):WIP supports upto 6 decimals, while INV supports only upto 5 decimals.
123 Hence, we are checking for difference. */
124
125 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
126 'Jobs/Schedules with invalid Job Quantity Completed',true,null,'Y',row_limit);
127
128 IF (dummy_num = row_limit) THEN
129 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
130 END IF;
131
132 IF (dummy_num > 0) THEN
133 reportStr := 'The rows returned above signify that there are jobs where quantity completed on job is not in sync with Inventory.';
134 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
135 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open a service request against Oracle Work in Process for the data-fix and root-cause, and upload the output of this diagnostic test. <BR> <BR>');
136 END IF;
137
138
139 -- 3 This script will identify all jobs where Resource Start and End Dates falls outside of Operation Start and End Dates
140 sqltxt :=
141 ' select substr(we.wip_entity_name, 1, 30) Job,'||
142 ' wop.wip_entity_id JobId, '||
143 ' wop.organization_id OrganizationID, wop.repetitive_schedule_id ScheduleId, '||
144 ' wop.operation_seq_num, '||
145 ' wor.resource_id, '||
146 ' wor.resource_seq_num, '||
147 ' wop.first_unit_start_date Operation_Start, '||
148 ' wop.last_unit_completion_date Operation_Completion, '||
149 ' wor.start_date Resource_Start, '||
150 ' wor.completion_date Resource_Completion'||
151 ' from wip_operation_resources wor, '||
152 ' wip_operations wop, '||
153 ' wip_entities we '||
154 ' where ' || we_dyn_where_clause || ' wop.wip_entity_id = wor.wip_entity_id '||
155 ' and wop.organization_id = wor.organization_id '||
156 ' and wop.operation_seq_num = wor.operation_seq_num '||
157 ' and nvl(wor.REPETITIVE_SCHEDULE_ID, -1) = nvl(wop.REPETITIVE_SCHEDULE_ID, -1) '||
158 ' and we.wip_entity_id = wop.wip_entity_id '||
159 ' and ( (wop.first_unit_start_date > wor.start_date) '||
160 ' or '||
161 ' (wop.last_unit_completion_date < wor.completion_date)) order by wop.organization_id, wop.wip_entity_id ';
162
163 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
164 'Jobs/Schedules with invalid Job Resource Start and End Date. ',true,null,'Y',row_limit);
165
166 IF (dummy_num = row_limit) THEN
167 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
168 END IF;
169
170 IF (dummy_num > 0) THEN
171 reportStr := 'The rows returned above signify that there are resources on Jobs/Schedules where Start and End Dates falls outside of Operation Start and End Dates.';
172 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
173 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
174 END IF;
175
176 --4 This script will identify all jobs where the quantity issued of material requirements is not in sync with Inventory
177 sqltxt :=
178 'select substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, we.organization_id OrganizationID, wro.inventory_item_id, '||
179 ' wro.operation_seq_num, wro.quantity_per_assembly, wro.required_quantity, '||
180 ' wro.quantity_issued, Sum(mmt.primary_quantity)*(-1) Inventory_Quantity '||
181 'from wip_discrete_jobs wdj, wip_entities we, '||
182 ' wip_requirement_operations wro, mtl_material_transactions mmt '||
183 'where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
184 'AND wdj.organization_id = we.organization_id '||
185 'AND wdj.wip_entity_id = wro.wip_entity_id '||
186 'AND wdj.organization_id = wro.organization_id '||
187 'AND wdj.wip_entity_id = mmt.transaction_source_id '||
188 'AND wdj.organization_id = mmt.organization_id '||
189 'AND mmt.transaction_source_type_id = 5 '||
190 'AND mmt.operation_seq_num = wro.operation_seq_num '||
191 'AND mmt.inventory_item_id = wro.inventory_item_id '||
192 'AND mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
193 ' HAVING (Sum(mmt.primary_quantity) - wro.quantity_issued*(-1)) >0.00001 '|| --FP Bug# 13643469 Bug#12727085 WIP supports upto 6 decimals, while INV supports only upto 5 decimals. Hence, we are checking for difference.
194 'GROUP BY wdj.wip_entity_id, substr(we.wip_entity_name,1,15), we.organization_id, wro.inventory_item_id, wro.operation_seq_num, '||
195 ' wro.quantity_per_assembly, wro.required_quantity, wro.quantity_issued ';
196
197 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
198 'Jobs with Quantity_Issued in material requirements not in sync with Inventory.',true,null,'Y',row_limit);
199
200 IF (dummy_num = row_limit) THEN
201 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
202 END IF;
203
204 IF (dummy_num > 0) THEN
205 reportStr := 'The rows returned above signify that there are jobs where issued quantity of material requirements is not in sync with Inventory.';
206 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
207 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
208 END IF;
209
210 --5 This script will identify unreleased jobs that have quantities on operation.
211 sqltxt :=
212 'SELECT distinct substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
213 ' wdj.organization_id OrganizationID, '||
214 ' decode(wdj.status_type, '||
215 ' 1,''Unreleased'', '||
216 ' 3, ''Released'', '||
217 ' 4, ''Complete'', '||
218 ' 5, ''Complete NoCharge'', '||
219 ' 6, ''On Hold'', '||
220 ' 7, ''Cancelled'', '||
221 ' 8, ''Pend Bill Load'', '||
222 ' 9, ''Failed Bill Load'', '||
223 ' 10, ''Pend Rtg Load'', '||
224 ' 11, ''Failed Rtg Load'', '||
225 ' 12, ''Closed'', '||
226 ' 13, ''Pending- Mass Loaded'', '||
227 ' 14, ''Pending Close'', '||
228 ' 15, ''Failed Close'', '||
229 ' wdj.status_type) Status '||
230 'FROM wip_discrete_jobs wdj, wip_entities we, '||
231 ' wip_operations wo '||
232 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
233 'AND we.organization_id = wdj.organization_id '||
234 'AND wdj.status_type = 1 '||
235 'AND wdj.wip_entity_id = wo.wip_entity_id '||
236 'AND wdj.organization_id = wo.organization_id '||
237 'AND (wo.quantity_in_queue <> 0 '||
238 ' OR wo.quantity_running <> 0 '||
239 ' OR wo.quantity_waiting_to_move <> 0 '||
240 ' OR wo.quantity_scrapped <> 0 '||
241 ' OR wo.quantity_rejected <> 0 '||
242 ' OR wo.quantity_completed <> 0) ';
243
244 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
245 'Unreleased jobs that have quantities on operation.',true,null,'Y',row_limit);
246
247 IF (dummy_num = row_limit) THEN
248 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
249 END IF;
250
251 IF (dummy_num > 0) THEN
252 reportStr := 'The rows returned above signify that there are unreleased jobs that have quantities on operation.';
253 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
254 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
255 END IF;
256
257 --6 This script will identify all released jobs that do not have any quantites on any operation.
258 sqltxt :=
259 'SELECT distinct substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
260 ' wdj.organization_id OrganizationID, ' ||
261 ' decode(wdj.status_type, '||
262 ' 1,''Unreleased'', '||
263 ' 3, ''Released'', '||
264 ' 4, ''Complete'', '||
265 ' 5, ''Complete NoCharge'', '||
266 ' 6, ''On Hold'', '||
267 ' 7, ''Cancelled'', '||
268 ' 8, ''Pend Bill Load'', '||
269 ' 9, ''Failed Bill Load'', '||
270 ' 10, ''Pend Rtg Load'', '||
271 ' 11, ''Failed Rtg Load'', '||
272 ' 12, ''Closed'', '||
273 ' 13, ''Pending- Mass Loaded'', '||
274 ' 14, ''Pending Close'', '||
275 ' 15, ''Failed Close'', '||
276 ' wdj.status_type) Status '||
277 'FROM wip_discrete_jobs wdj, wip_entities we, '||
278 ' wip_operations wo '||
279 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
280 'AND we.organization_id = wdj.organization_id '||
281 'AND wdj.status_type IN (3,4) '||
282 'AND wdj.wip_entity_id = wo.wip_entity_id '||
283 'AND wdj.organization_id = wo.organization_id '||
284 'AND wo.quantity_in_queue = 0 '||
285 'AND wo.quantity_running = 0 '||
286 'AND wo.quantity_waiting_to_move = 0 '||
287 'AND wo.quantity_scrapped = 0 '||
288 'AND wo.quantity_rejected = 0 '||
289 'AND wo.quantity_completed = 0 ' ||
290 ' AND ( wo.PREVIOUS_OPERATION_SEQ_NUM is null /*for first operation*/' ||
291 ' OR 0 >= ' ||
292 ' (Select sum(wo1.quantity_in_queue) + sum(wo1.quantity_running) + ' ||
293 ' sum(quantity_waiting_to_move) + sum(quantity_scrapped) + sum(quantity_rejected) + '||
294 ' sum(quantity_completed) from wip_operations wo1 ' ||
295 ' WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
296 'AND wo1.organization_id = wo.organization_id '||
297 'AND wo1.wip_entity_id = wo.wip_entity_id '||
298 'AND wo1.OPERATION_SEQ_NUM <= wo.PREVIOUS_OPERATION_SEQ_NUM ))';
299
300 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
301 'Released jobs that do not have any quantites on any operation.',true,null,'Y',row_limit);
302
303 IF (dummy_num = row_limit) THEN
304 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
305 END IF;
306
307 IF (dummy_num > 0) THEN
308 reportStr := 'The rows returned above signify that there are released jobs that do not have any quantites on any operation.';
309 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
310 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
311 END IF;
312
313
314 --6.5 This script identifies all jobs that have operation quantities not in sync with move transaction quantities
315 sqltxt := ' SELECT substr(we.wip_entity_name, 1,20) Job, wop.wip_entity_id JobId , wop.organization_id, wop.operation_seq_num ' ||
316 ' FROM WIP_OPERATIONS wop , wip_entities we, wip_discrete_jobs wdj ' ||
317 ' WHERE ' || wdj_dyn_where_clause ||
318 ' we.wip_entity_id = wdj.wip_entity_id ' ||
319 ' and wdj.wip_entity_id = wop.wip_entity_id ' ||
320 ' and ( ' ||
321 ' ( wop.quantity_in_queue ' ||
322 ' - Decode(Nvl(wop.PREVIOUS_OPERATION_SEQ_NUM, 0), 0, wdj.start_quantity,0) ' ||
323 ' - ( SELECT Decode(Nvl(wop.PREVIOUS_OPERATION_SEQ_NUM, 0), 0,Sum(Nvl(OVERCOMPLETION_PRIMARY_QTY,0)),0) ' ||
324 ' FROM wip_move_transactions wmt2 ' ||
325 ' WHERE wmt2.wip_entity_id = wop.wip_entity_id ) ' ||
326 ' <> ((SELECT SUM( DECODE(wop.operation_seq_num, ' ||
327 ' wmt_rec.fm_operation_seq_num, ' ||
328 ' -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
329 ' 1,ROUND(wmt_rec.primary_quantity,6), ' ||
330 ' 2,0,3,0,4,0,5,0 ' ||
331 ' ),0) + ' ||
332 ' DECODE(wop.operation_seq_num, ' ||
333 ' wmt_rec.to_operation_seq_num, ' ||
334 ' DECODE(wmt_rec.to_intraoperation_step_type, ' ||
335 ' 1,ROUND(wmt_rec.primary_quantity,6), ' ||
336 ' 2,0,3,0,4,0,5,0),0) ' ||
337 ' ) ' ||
338 ' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
339 ' WHERE wop1.rowid = wop.ROWID ' ||
340 ' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
341 ' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
342 ' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num) ) ' ||
343 ' ) ' ||
344 ' ) ' ||
345 ' OR ' ||
346 ' ( ' ||
347 ' wop.quantity_running <> ' ||
348 ' (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
349 ' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
350 ' 1,0, ' ||
351 ' 2,ROUND(wmt_rec.primary_quantity,6), ' ||
352 ' 3,0,4,0,5,0),0) + ' ||
353 ' DECODE(wop.operation_seq_num, ' ||
354 ' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
355 ' 1,0, ' ||
356 ' 2,ROUND(wmt_rec.primary_quantity,6), ' ||
357 ' 3,0,4,0,5,0),0) ) ' ||
358 ' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
359 ' WHERE wop1.rowid = wop.ROWID ' ||
360 ' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
361 ' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
362 ' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num)) ' ||
363 ' ) ' ||
364 ' OR ' ||
365 ' ( ' ||
366 ' wop.quantity_waiting_to_move ' ||
367 ' + Decode(Nvl(wop.next_operation_seq_num, 0), 0, wdj.quantity_completed , 0) ' ||
368 ' <> (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
369 ' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
370 ' 1,0,2,0, ' ||
371 ' 3,ROUND(wmt_rec.primary_quantity,6), ' ||
372 ' 4,0,5,0),0) + ' ||
373 ' DECODE(wop.operation_seq_num, ' ||
374 ' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
375 ' 1,0,2,0, ' ||
376 ' 3,ROUND(wmt_rec.primary_quantity,6), ' ||
377 ' 4,0,5,0),0) ) ' ||
378 ' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
379 ' WHERE wop1.rowid = wop.ROWID ' ||
380 ' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
381 ' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
382 ' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num)) ' ||
383 ' ) ' ||
384 ' OR ' ||
385 ' ( ' ||
386 ' wop.quantity_rejected <> ' ||
387 ' (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
388 ' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
389 ' 1,0,2,0,3,0, ' ||
390 ' 4,ROUND(wmt_rec.primary_quantity,6), ' ||
391 ' 5,0),0) + ' ||
392 ' DECODE(wop.operation_seq_num, ' ||
393 ' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
394 ' 1,0,2,0,3,0, ' ||
395 ' 4,ROUND(wmt_rec.primary_quantity,6), ' ||
396 ' 5,0),0) ) ' ||
397 ' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
398 ' WHERE wop1.rowid = wop.ROWID ' ||
399 ' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
400 ' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
401 ' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num) ) ' ||
402 ' ) ' ||
403 ' OR ' ||
404 ' ( ' ||
405 ' wop.quantity_scrapped <> ' ||
406 ' (SELECT SUM(DECODE(wop.operation_seq_num, ' ||
407 ' wmt_rec.fm_operation_seq_num, -1*DECODE(wmt_rec.fm_intraoperation_step_type, ' ||
408 ' 1,0,2,0,3,0,4,0, ' ||
409 ' 5,ROUND(wmt_rec.primary_quantity,6)),0) + ' ||
410 ' DECODE(wop.operation_seq_num, ' ||
411 ' wmt_rec.to_operation_seq_num, DECODE(wmt_rec.to_intraoperation_step_type, ' ||
412 ' 1,0,2,0,3,0,4,0, ' ||
413 ' 5,ROUND(wmt_rec.primary_quantity,6)),0) ) ' ||
414 ' FROM WIP_OPERATIONS wop1 , wip_move_transactions wmt_rec ' ||
415 ' WHERE wop1.rowid = wop.ROWID ' ||
416 ' AND wop.wip_entity_id = wmt_rec.wip_entity_id ' ||
417 ' AND (wop1.operation_seq_num = wmt_rec.fm_operation_seq_num ' ||
418 ' OR wop1.operation_seq_num = wmt_rec.to_operation_seq_num)) ' ||
419 ' ) ' ||
420 ' OR ' ||
421 ' ( wop.quantity_completed ' ||
422 ' <> (SELECT NVL(SUM(wti.primary_quantity * ' ||
423 ' DECODE(sign(wti.to_operation_seq_num-wti.fm_operation_seq_num), ' ||
424 ' 0,DECODE(sign(wti.fm_intraoperation_step_type-2), ' ||
425 ' 0,DECODE(sign(wti.to_intraoperation_step_type-2), ' ||
426 ' 0,-1, ' ||
427 ' -1,-1, ' ||
428 ' 1,1), ' ||
429 ' -1,DECODE(sign(wti.to_intraoperation_step_type-2), ' ||
430 ' 0,-1,-1,-1,1,1), ' ||
431 ' 1,-1), ' ||
432 ' 1, 1, ' ||
433 ' -1,-1)),0) ' ||
434 ' FROM WIP_OPERATIONS wop1, WIP_MOVE_TRANSACTIONS wti ' ||
435 ' WHERE wop1.rowid = wop.rowid ' ||
436 ' AND wop1.organization_id = wti.organization_id ' ||
437 ' AND wop1.wip_entity_id = wti.wip_entity_id ' ||
438 ' AND ( ' ||
439 ' (wop1.operation_seq_num >= wti.fm_operation_seq_num ' ||
440 ' + DECODE(sign(wti.fm_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
441 ' AND wop1.operation_seq_num < wti.to_operation_seq_num ' ||
442 ' + DECODE(sign(wti.to_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
443 ' AND (wti.to_operation_seq_num > wti.fm_operation_seq_num ' ||
444 ' OR (wti.to_operation_seq_num = wti.fm_operation_seq_num ' ||
445 ' AND wti.fm_intraoperation_step_type<=2 ' ||
446 ' AND wti.to_intraoperation_step_type>2)) ' ||
447 ' AND (wop1.count_point_type < 3 ' ||
448 ' OR wop1.operation_seq_num = wti.fm_operation_seq_num ' ||
449 ' OR (wop1.operation_seq_num = wti.to_operation_seq_num ' ||
450 ' AND wti.to_intraoperation_step_type > 2))) ' ||
451 ' OR ' ||
452 ' (wop1.operation_seq_num < wti.fm_operation_seq_num ' ||
453 ' + DECODE(sign(wti.fm_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
454 ' AND wop1.operation_seq_num >= wti.to_operation_seq_num ' ||
455 ' + DECODE(sign(wti.to_intraoperation_step_type-2), 0,0,-1,0,1,1) ' ||
456 ' AND (wti.fm_operation_seq_num > wti.to_operation_seq_num ' ||
457 ' OR (wti.fm_operation_seq_num = wti.to_operation_seq_num ' ||
458 ' AND wti.to_intraoperation_step_type<=2 ' ||
459 ' AND wti.fm_intraoperation_step_type>2)) ' ||
460 ' AND (wop1.count_point_type < 3 ' ||
461 ' OR (wop1.operation_seq_num = wti.to_operation_seq_num and wop1.count_point_type < 3 ) ' ||
462 ' OR (wop1.operation_seq_num = wti.fm_operation_seq_num ' ||
463 ' AND wti.fm_intraoperation_step_type > 2))) ' ||
464 ' )) ' ||
465 ' ) ' ||
466 ' ) ' ;
467
468 dummy_num := JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
469 'Jobs that have operation quantities not in sync with move transaction quantities.',true,null,'Y',row_limit);
470
471 IF (dummy_num = row_limit) THEN
472 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
473 END IF;
474
475 if dummy_num > 0 then
476 reportStr := 'The rows returned above signify that Operation quantites are not in sync with Move Transactions. ';
477 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
478 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
479 end if;
480
481 --7 This script identifies all records that are Orphan in the WIP tables.
482 sqltxt :=
483 'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_operations data exists'' Message '||
484 ' FROM wip_operations wo ' ||
485 'WHERE ' || wo_dyn_where_clause ||
486 ' NOT EXISTS (SELECT 1 FROM wip_entities we '||
487 ' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
488 ' AND wo.organization_id = we.organization_id) '||
489 'UNION '||
490 'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_requirement_operations data exists'' Message FROM wip_requirement_operations wo '||
491 'WHERE ' || wo_dyn_where_clause ||
492 ' NOT EXISTS (SELECT 1 FROM wip_entities we '||
493 ' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
494 ' AND wo.organization_id = we.organization_id) '||
495 'UNION '||
496 'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_operation_resources data exists'' Message FROM wip_operation_resources wo '||
497 'WHERE ' || wo_dyn_where_clause ||
498 ' NOT EXISTS (SELECT 1 FROM wip_entities we '||
499 ' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
500 ' AND wo.organization_id = we.organization_id)'||
501 'UNION '||
502 'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_period_balances data exists'' Message FROM wip_period_balances wo '||
503 'WHERE ' || wo_dyn_where_clause ||
504 ' NOT EXISTS (SELECT 1 FROM wip_entities we '||
505 ' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
506 ' AND wo.organization_id = we.organization_id) '||
507 'UNION '||
508 'SELECT wo.wip_entity_id JobId, wo.organization_id OrganizationID, ''Orphan wip_discrete_jobs data exists'' Message FROM wip_discrete_jobs wo '||
509 'WHERE ' || wo_dyn_where_clause ||
510 ' NOT EXISTS (SELECT 1 FROM wip_entities we '||
511 ' WHERE ' || we_dyn_where_clause || ' wo.wip_entity_id = we.wip_entity_id '||
512 ' AND wo.organization_id = we.organization_id) ';
513
514 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
515 'Records Orphan in the WIP tables.',true,null,'Y',row_limit);
516
517 IF (dummy_num = row_limit) THEN
518 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
519 END IF;
520
521 IF (dummy_num > 0) THEN
522 reportStr := 'The rows returned above signify that there are orphan records exist in WIP table(s). ';
523 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
524 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
525 END IF;
526
527
528 -- 8 This script identifies all the jobs that have multiple PO Move resources in an operation.
529 sqltxt :=
530 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
531 ' wdj.organization_id OrganizationID, wo.operation_seq_num, Count(*) '||
532 'FROM wip_discrete_jobs wdj, wip_entities we, '||
533 ' wip_operations wo, wip_operation_resources wor '||
534 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
535 'AND we.organization_id = wdj.organization_id '||
536 'AND wdj.status_type IN (1,3) '||
537 'AND wdj.wip_entity_id = wo.wip_entity_id '||
538 'AND wdj.organization_id = wo.organization_id '||
539 'AND wo.wip_entity_id = wor.wip_entity_id '||
540 'AND wo.organization_id = wor.organization_id '||
541 'AND wo.operation_seq_num = wor.operation_seq_num '||
542 'AND wor.autocharge_type = 4 '||
543 'HAVING Count(*) > 1 '||
544 'GROUP BY wdj.wip_entity_id, substr(we.wip_entity_name,1,15), '||
545 ' wdj.organization_id, wo.operation_seq_num ';
546
547 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
548 'Jobs that have multiple PO Move resources in an operation.',true,null,'Y',row_limit);
549
550 IF (dummy_num = row_limit) THEN
551 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
552 END IF;
553
554 IF (dummy_num > 0) THEN
555 reportStr := 'The rows returned above signify that there are multiple PO move resources in an operation.';
556 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
557 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
558 END IF;
559
560
561 --9 This script check if there are any trailing spaces in the text fields on Discrete Job for a particular Job.
562 IF ( l_job_Id IS NOT NULL ) THEN
563
564 BEGIN
565 l_return_status := FND_INSTALLATION.GET_APP_INFO( application_short_name => 'WIP', status => p_status, industry=> p_industry, oracle_schema => p_table_owner);
566
567 sqltxt:='SELECT 1 from dual where 1=2';
568
569 FOR i IN l_trail_space(p_table_owner) loop
570 sqltext1:= 'SELECT nvl(Min('||
571 1 ||
572 ' ),0) FROM wip_discrete_jobs wdj WHERE ' ||
573 'Length('||
574 i.column_name||
575 ')<>Length(RTrim('||
576 i.column_name||
577 '))AND '||
578 wdj_dyn_where_clause||
579 '1=1';
580
581 EXECUTE IMMEDIATE sqltext1 INTO a ;
582
583 IF a=1 THEN
584
585 sqltext:= sqltext||
586 'column_name "Column Name" ,'||
587 i.column_name||
588 ' "Value"'||
589 ', length('||
590 i.column_name||
591 ') "Length in Database"'||
592 ',length(Rtrim('||
593 i.column_name||
594 ')) "Actual Length",';
595
596 wdj_atc_where_clause:=' atc.column_name='''||
597 i.column_name ||
598 ''' and atc.table_name=syn.table_name and syn.synonym_name='||
599 '''WIP_DISCRETE_JOBS'''||
600 ' and atc.owner=syn.table_owner AND syn.table_owner= '''||
601 p_table_owner||
602 '''';
603
604 a:=0;
605
606 IF Length(sqltext)>0 THEN
607
608 sqltext:=RTrim(sqltext,',');
609 sqltext:='select '||
610 sqltext||
611 ' from wip_discrete_jobs wdj,all_tab_columns atc,user_synonyms syn where '|| --Bug#15877953:Added user_synonyms
612 wdj_dyn_where_clause||
613 wdj_atc_where_clause;
614
615
616 IF(Length(sqltext2) > 0) THEN
617 sqltext2 := sqltext2||
618 ' union ' ||
619 sqltext;
620 ELSE
621 sqltext2 := sqltext;
622 END IF;
623
624 sqltext:=NULL;
625
626 END IF;
627
628 END IF;
629 END LOOP;
630 END;
631 if sqltext2 is not null then
632 sqltxt := sqltext2;
633 end if;
634
635 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
636 'Trailing spaces in the text fields for a Discrete Job',true,null,'Y',row_limit);
637
638 IF (dummy_num > 0) THEN
639 reportStr := 'The rows returned above signify that there are trailing spaces in the text fields for Discrete Job.';
640 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
641 JTF_DIAGNOSTIC_COREAPI.ActionErrorLink('Please refer to Metalink note ', 402202.1, 'to get the root-cause patch and steps to correct the data.<BR><BR>');
642 END IF;
643 END IF;
644
645 --COMPLETION SUBINVENTORY / LOCATOR
646 -----------------------------------
647
648 -- 10. invalid completion subinventory
649 sqltxt :=
650 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
651 ' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
652 'FROM wip_discrete_jobs wdj, wip_entities we '||
653 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
654 'AND we.organization_id = wdj.organization_id '||
655 'AND wdj.status_type IN (1,3,4) '||
656 'AND wdj.completion_subinventory IS NOT NULL '||
657 'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_inventories mi '||
658 ' WHERE wdj.completion_subinventory = mi.secondary_inventory_name '||
659 ' AND wdj.organization_id = mi.organization_id '||
660 ' AND mi.secondary_inventory_name <> ''AX_INTRANS'' AND Nvl(mi.disable_date,Trunc(SYSDATE+1)) > Trunc(SYSDATE)) ';
661
662 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
663 'Jobs/Schedules with invalid Completion Subinventory',true,null,'Y',row_limit);
664
665 IF (dummy_num = row_limit) THEN
666 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
667 END IF;
668
669 IF (dummy_num > 0) THEN
670 reportStr := 'The rows returned above signify that there are jobs having invalid completion subinventory.';
671 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
672 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
673 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
674 END IF;
675
676
677 --11. completion subinventory not valid for the assembly. assembly has "restrict subinventories" enabled and
678 -- this subinventory is not part of it.
679 sqltxt :=
680 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, wdj.organization_id OrganizationID, msik.concatenated_segments, '||
681 ' wdj.completion_subinventory '||
682 'FROM wip_discrete_jobs wdj, wip_entities we, mtl_system_items_kfv msik '||
683 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
684 'AND we.organization_id = wdj.organization_id '||
685 'AND wdj.status_type IN (1,3,4) '||
686 'AND msik.inventory_item_id = wdj.primary_item_id '||
687 'AND msik.organization_id = wdj.organization_id '||
688 'AND msik.restrict_subinventories_code = 1 '||
689 'AND wdj.completion_subinventory IS NOT NULL '||
690 'AND NOT EXISTS (SELECT 1 FROM mtl_item_sub_inventories mi '||
691 ' WHERE wdj.completion_subinventory = mi.secondary_inventory '||
692 ' AND wdj.organization_id = mi.organization_id '||
693 ' AND wdj.primary_item_id = mi.inventory_item_id) ';
694
695 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
696 'Jobs/Schedules with invalid Completion Subinventory - Not part of "Restricted Subinventories"',true,null,'Y',row_limit);
697
698 IF (dummy_num = row_limit) THEN
699 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
700 END IF;
701
702 IF (dummy_num > 0) THEN
703 reportStr := 'The rows returned above signify that there are jobs having completion subinventory that is not part of "Restricted Subinventories". ';
704 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
705 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
706 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
707 END IF;
708
709 -- 12. completion subinventory has invalid material status
710 sqltxt :=
711 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
712 ' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
713 'FROM wip_discrete_jobs wdj, wip_entities we '||
714 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
715 'AND we.organization_id = wdj.organization_id '||
716 'AND wdj.status_type IN (1,3,4) '||
717 'AND wdj.completion_subinventory IS NOT NULL '||
718 'AND inv_material_status_grp.is_status_applicable( '||
719 ' NULL, NULL, 44, NULL, NULL, '||
720 ' wdj.organization_id, wdj.primary_item_id, '||
721 ' wdj.completion_subinventory, '||
722 ' NULL, NULL, NULL, ''Z'') <> ''Y'' ';
723
724 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
725 'Jobs/Schedules with invalid Completion Subinventory - Invalid Material Status',true,null,'Y',row_limit);
726
727 IF (dummy_num = row_limit) THEN
728 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
729 END IF;
730
731 IF (dummy_num > 0) THEN
732 reportStr := 'The rows returned above signify that there are jobs having completion subinventory with invalid material status.';
733 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
734 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
735 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
736 END IF;
737
738 -- 13. completion locator missing
739 sqltxt :=
740 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
741 ' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
742 'FROM wip_discrete_jobs wdj, wip_entities we '||
743 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
744 'AND we.organization_id = wdj.organization_id '||
745 'AND wdj.status_type IN (1,3,4) '||
746 'AND wdj.completion_subinventory IS NOT NULL '||
747 'AND wma_special_lovs.locatorControl(wdj.organization_id, '||
748 ' wdj.completion_subinventory, '||
749 ' wdj.primary_item_id) <> 1 '||
750 'AND completion_locator_id IS NULL ';
751
752 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
753 'Jobs/Schedules with missing Completion Locator',true,null,'Y',row_limit);
754
755 IF (dummy_num = row_limit) THEN
756 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
757 END IF;
758
759 IF (dummy_num > 0) THEN
760 reportStr := 'The rows returned above signify that there are jobs with null completion locator.';
761 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
762 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
763 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
764 END IF;
765
766 -- 14. completion locator was supposed to be null but populated
767 sqltxt :=
768 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
769 ' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
770 'FROM wip_discrete_jobs wdj, wip_entities we '||
771 'WHERE ' || we_dyn_where_clause || 'we.wip_entity_id = wdj.wip_entity_id '||
772 'AND we.organization_id = wdj.organization_id '||
773 'AND wdj.status_type IN (1,3,4) '||
774 'AND wdj.completion_subinventory IS NOT NULL '||
775 'AND wma_special_lovs.locatorControl(wdj.organization_id, '||
776 ' wdj.completion_subinventory, '||
777 ' wdj.primary_item_id) = 1 '||
778 'AND completion_locator_id IS NOT NULL ';
779
780 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
781 'Jobs/Schedules with non locator controlled Completion Subinventory but Completion Locator populated.',true,null,'Y',row_limit);
782
783 IF (dummy_num = row_limit) THEN
784 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
785 END IF;
786
787 IF (dummy_num > 0) THEN
788 reportStr := 'The rows returned above signify that there are jobs with non locator controlled Completion Subinventory but Completion Locator populated.';
789 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
790 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
791 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
792 END IF;
793
794 --15. completion subinventory NULL but completion locator populated
795 sqltxt :=
796 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
797 ' wdj.organization_id OrganizationID, wdj.completion_subinventory '||
798 'FROM wip_discrete_jobs wdj, wip_entities we '||
799 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
800 'AND we.organization_id = wdj.organization_id '||
801 'AND wdj.status_type IN (1,3,4) '||
802 'AND wdj.completion_subinventory IS NULL '||
803 'AND wdj.completion_locator_id IS NOT NULL ';
804
805 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
806 'Jobs/Schedules with a completion locator but no Completion Subinventory.',true,null,'Y',row_limit);
807
808 IF (dummy_num = row_limit) THEN
809 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
810 END IF;
811
812 IF (dummy_num > 0) THEN
813 reportStr := 'The rows returned above signify that there are jobs with a completion locator but no Completion Subinventory.';
814 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
815 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
816 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
817 END IF;
818
819 -- 16. completion locator not valid
820 sqltxt :=
821 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
822 ' wdj.organization_id OrganizationID, wdj.completion_subinventory, '||
823 ' inv_project.get_locator(wdj.completion_locator_id, '||
824 ' wdj.organization_id) Locator '||
825 'FROM wip_discrete_jobs wdj, wip_entities we '||
826 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
827 'AND we.organization_id = wdj.organization_id '||
828 'AND wdj.status_type IN (1,3,4) '||
829 'AND wdj.completion_subinventory IS NOT NULL '||
830 'AND wdj.completion_locator_id IS NOT NULL '||
831 'AND NOT EXISTS (SELECT 1 FROM mtl_item_locations mil '||
832 ' WHERE wdj.completion_locator_id = mil.inventory_location_id '||
833 ' AND wdj.organization_id = mil.organization_id '||
834 ' AND wdj.completion_subinventory = mil.subinventory_code '||
835 ' AND Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)) ';
836
837 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
838 'Jobs/Schedules with invalid Completion Locator ',true,null,'Y',row_limit);
839
840 IF (dummy_num = row_limit) THEN
841 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
842 END IF;
843
844 IF (dummy_num > 0) THEN
845 reportStr := 'The rows returned above signify that there are jobs having invalid completion locator.';
846 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
847 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
848 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
849 END IF;
850
851 -- 17. completion locator not valid for the assembly. assembly has "restrict locators" enabled and
852 -- this locator is not part of it.
853 sqltxt :=
854 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
855 ' wdj.organization_id OrganizationID, wdj.completion_subinventory, msik.concatenated_segments, '||
856 ' inv_project.get_locator(wdj.completion_locator_id, wdj.organization_id) Locator '||
857 'FROM wip_discrete_jobs wdj, wip_entities we, mtl_system_items_kfv msik '||
858 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
859 'AND we.organization_id = wdj.organization_id '||
860 'AND wdj.status_type IN (1,3,4) '||
861 'AND msik.inventory_item_id = wdj.primary_item_id '||
862 'AND msik.organization_id = wdj.organization_id '||
863 'AND msik.restrict_locators_code = 1 '||
864 'AND wdj.completion_subinventory IS NOT NULL '||
865 'AND wdj.completion_locator_id IS NOT NULL '||
866 'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_locators msl '||
867 ' WHERE wdj.completion_locator_id = msl.secondary_locator '||
868 ' AND wdj.organization_id = msl.organization_id '||
869 ' AND wdj.primary_item_id = msl.inventory_item_id) ';
870
871 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
872 'Jobs/Schedules with invalid Completion Locator - Not part of "Restricted Locators"',true,null,'Y',row_limit);
873
874 IF (dummy_num = row_limit) THEN
875 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
876 END IF;
877
878 IF (dummy_num > 0) THEN
879 reportStr := 'The rows returned above signify that there are jobs having completion locator that is not part of "Restricted Locators".';
880 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
881 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
882 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
883 END IF;
884
885 -- 18.completion locator has invalid material status
886 sqltxt :=
887 'SELECT substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, '||
888 ' wdj.organization_id OrganizationID, wdj.completion_subinventory, '||
889 ' inv_project.get_locator(wdj.completion_locator_id, wdj.organization_id) Locator '||
890 'FROM wip_discrete_jobs wdj, wip_entities we '||
891 'WHERE ' || we_dyn_where_clause || ' we.wip_entity_id = wdj.wip_entity_id '||
892 'AND we.organization_id = wdj.organization_id '||
893 'AND wdj.status_type IN (1,3,4) '||
894 'AND wdj.completion_subinventory IS NOT NULL '||
895 'AND wdj.completion_locator_id IS NOT NULL '||
896 'AND inv_material_status_grp.is_status_applicable( '||
897 ' NULL, NULL, 44, NULL, NULL, '||
898 ' wdj.organization_id, wdj.primary_item_id, '||
899 ' wdj.completion_subinventory, wdj.completion_locator_id, '||
900 ' NULL, NULL, ''Z'') <> ''Y'' ';
901
902 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
903 'Jobs/Schedules with invalid Completion Locator - Invalid Material Status',true,null,'Y',row_limit);
904
905 IF (dummy_num = row_limit) THEN
906 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
907 END IF;
908
909 IF (dummy_num > 0) THEN
910 reportStr := 'The rows returned above signify that there are jobs having completion locator with invalid material status.';
911 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
912 reportStr := 'Please query up each job in the discrete job form, and update the completion subinventory/locator of the problematic job to a valid value.<BR> <BR> ';
913 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
914 END IF;
915
916 --SUPPLY SUBINVENTORY / LOCATOR
917 -----------------------------
918
919 --19. invalid supply subinventory
920 sqltxt :=
921 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, '||
922 ' wro.operation_seq_num,wro.inventory_item_id,wro.supply_subinventory '||
923 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
924 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
925 'AND wro.organization_id = wdj.organization_id '||
926 'AND wdj.status_type IN (1,3,4) '||
927 'AND wro.supply_subinventory IS NOT NULL '||
928 'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_inventories mi '||
929 ' WHERE wro.supply_subinventory = mi.secondary_inventory_name '||
930 ' AND wro.organization_id = mi.organization_id '||
931 ' AND mi.secondary_inventory_name <> ''AX_INTRANS'' '||
932 ' AND Nvl(mi.disable_date,Trunc(SYSDATE+1)) > Trunc(SYSDATE)) ';
933
934 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
935 'Jobs/Schedules with invalid Supply Subinventory',true,null,'Y',row_limit);
936
937 IF (dummy_num = row_limit) THEN
938 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
939 END IF;
940
941 IF (dummy_num > 0) THEN
942 reportStr := 'The rows returned above signify that there are jobs having invalid supply subinventory.';
943 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
944 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
945 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
946 END IF;
947
948 --20. supply subinventory not valid for the component. component has "restrict subinventories" enabled and
949 -- this subinventory is not part of it.
950 sqltxt :=
951 'SELECT wro.wip_entity_id JobId,wro.organization_id OrganizationID, msik.concatenated_segments, '||
952 ' wro.operation_seq_num,wro.inventory_item_id,wro.supply_subinventory '||
953 'FROM wip_discrete_jobs wdj,wip_requirement_operations wro, mtl_system_items_kfv msik '||
954 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
955 'AND wro.organization_id = wdj.organization_id '||
956 'AND wdj.status_type IN (1,3,4) '||
957 'AND msik.inventory_item_id = wro.inventory_item_id '||
958 'AND msik.organization_id = wro.organization_id '||
959 'AND msik.restrict_subinventories_code = 1 '||
960 'AND wro.supply_subinventory IS NOT NULL '||
961 'AND NOT EXISTS (SELECT 1 FROM mtl_item_sub_inventories mi '||
962 ' WHERE wro.supply_subinventory = mi.secondary_inventory '||
963 ' AND wro.organization_id = mi.organization_id '||
964 ' AND wro.inventory_item_id = mi.inventory_item_id) ';
965
966 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
967 'Jobs/Schedules with invalid Supply Subinventory - Not part of '||
968 ' "Restricted Subinventories".',true,null,'Y',row_limit);
969
970 IF (dummy_num = row_limit) THEN
971 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
972 END IF;
973
974 IF (dummy_num > 0) THEN
975 reportStr := 'The rows returned above signify that there are jobs having supply subinventory that is not part of "Restricted Subinventories".';
976
977 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
978 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
979 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
980 END IF;
981
982 --21. supply subinventory has invalid material status
983 sqltxt :=
984 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, '||
985 ' wro.operation_seq_num,wro.inventory_item_id,wro.supply_subinventory '||
986 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
987 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
988 'AND wro.organization_id = wdj.organization_id '||
989 'AND wdj.status_type IN (1,3,4) '||
990 'AND wro.supply_subinventory IS NOT NULL '||
991 'AND inv_material_status_grp.is_status_applicable( '||
992 ' NULL, NULL, 44, NULL, NULL, '||
993 ' wro.organization_id, wro.inventory_item_id, '||
994 ' wro.supply_subinventory, '||
995 ' NULL, NULL, NULL, ''Z'') <> ''Y'' ';
996
997 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
998 'Jobs/Schedules with invalid Supply Subinventory - Invalid Material Status',true,null,'Y',row_limit);
999
1000 IF (dummy_num = row_limit) THEN
1001 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1002 END IF;
1003
1004 IF (dummy_num > 0) THEN
1005 reportStr := 'The rows returned above signify that there are jobs having supply subinventory with invalid material status.';
1006 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1007 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1008 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1009 END IF;
1010
1011 --22. supply locator locator missing
1012 sqltxt :=
1013 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
1014 ' wro.inventory_item_id,wro.supply_subinventory,wro.supply_locator_id '||
1015 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
1016 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
1017 'AND wro.organization_id = wdj.organization_id '||
1018 'AND wdj.status_type IN (1,3,4) '||
1019 'AND wro.supply_subinventory IS NOT NULL '||
1020 'AND wma_special_lovs.locatorControl(wro.organization_id, '||
1021 ' wro.supply_subinventory, '||
1022 ' wro.inventory_item_id) <> 1 '||
1023 'AND wro.supply_locator_id IS NULL ';
1024
1025 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1026 'Jobs/Schedules with Supply Locator Missing',true,null,'Y',row_limit);
1027
1028 IF (dummy_num = row_limit) THEN
1029 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1030 END IF;
1031
1032 IF (dummy_num > 0) THEN
1033 reportStr := 'The rows returned above signify that there are jobs where supply locator is missing.';
1034 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1035 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1036 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1037 END IF;
1038
1039 --23. supply locator was supposed to be null but populated
1040 sqltxt :=
1041 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
1042 ' wro.inventory_item_id,wro.supply_subinventory,wro.supply_locator_id '||
1043 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
1044 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
1045 'AND wro.organization_id = wdj.organization_id '||
1046 'AND wdj.status_type IN (1,3,4) '||
1047 'AND wro.supply_subinventory IS NOT NULL '||
1048 'AND wma_special_lovs.locatorControl(wro.organization_id, '||
1049 ' wro.supply_subinventory, '||
1050 ' wro.inventory_item_id) = 1 '||
1051 'AND wro.supply_locator_id IS NOT NULL ';
1052
1053 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1054 'Jobs/Schedules with non locator controlled Supply Subinventory but Supply Locator populated.',true,null,'Y',row_limit);
1055
1056 IF (dummy_num = row_limit) THEN
1057 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1058 END IF;
1059
1060 IF (dummy_num > 0) THEN
1061 reportStr := 'The rows returned above signify that there are jobs with non locator controlled Supply Subinventory but Supply Locator populated.';
1062 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1063 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1064 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1065 END IF;
1066
1067 --24. supply subinventory NULL but supply locator populated
1068 sqltxt :=
1069 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
1070 ' wro.inventory_item_id,wro.supply_subinventory,wro.supply_locator_id '||
1071 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
1072 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
1073 'AND wro.organization_id = wdj.organization_id '||
1074 'AND wdj.status_type IN (1,3,4) '||
1075 'AND wro.supply_subinventory IS NULL '||
1076 'AND wro.supply_locator_id IS NOT NULL ';
1077
1078 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1079 'Jobs/Schedules with a Supply Locator but no Supply Subinventory',true,null,'Y',row_limit);
1080
1081 IF (dummy_num = row_limit) THEN
1082 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1083 END IF;
1084
1085 IF (dummy_num > 0) THEN
1086 reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but supply subinventory is null.';
1087 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1088 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1089 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1090 END IF;
1091
1092 --25. supply locator not valid
1093 sqltxt :=
1094 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
1095 ' wro.inventory_item_id,wro.supply_subinventory, '||
1096 ' inv_project.get_locator(wro.supply_locator_id, '||
1097 ' wro.organization_id) Locator '||
1098 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
1099 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
1100 'AND wro.organization_id = wdj.organization_id '||
1101 'AND wdj.status_type IN (1,3,4) '||
1102 'AND wro.supply_subinventory IS NOT NULL '||
1103 'AND wro.supply_locator_id IS NOT NULL '||
1104 'AND NOT EXISTS (SELECT 1 FROM mtl_item_locations mil '||
1105 ' WHERE wro.supply_locator_id = mil.inventory_location_id '||
1106 ' AND wro.organization_id = mil.organization_id '||
1107 ' AND wro.supply_subinventory = mil.subinventory_code '||
1108 ' AND Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)) ';
1109
1110 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1111 'Jobs/Schedules with invalid Supply Locator ',true,null,'Y',row_limit);
1112
1113 IF (dummy_num = row_limit) THEN
1114 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1115 END IF;
1116
1117 IF (dummy_num > 0) THEN
1118 reportStr := 'The rows returned above signify that there are jobs where supply locator is not valid.';
1119 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1120 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1121 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1122 END IF;
1123
1124 --26. Supply locator not valid for the component. component has "restrict locators" enabled and
1125 -- this locator is not part of it.
1126 sqltxt :=
1127 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
1128 ' wro.inventory_item_id,wro.supply_subinventory, msik.concatenated_segments, '||
1129 ' inv_project.get_locator(wro.supply_locator_id, wro.organization_id) Locator '||
1130 'FROM wip_discrete_jobs wdj,wip_requirement_operations wro, mtl_system_items_kfv msik '||
1131 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
1132 'AND wro.organization_id = wdj.organization_id '||
1133 'AND wdj.status_type IN (1,3,4) '||
1134 'AND msik.inventory_item_id = wro.inventory_item_id '||
1135 'AND msik.organization_id = wro.organization_id '||
1136 'AND msik.restrict_locators_code = 1 '||
1137 'AND wro.supply_subinventory IS NOT NULL '||
1138 'AND wro.supply_locator_id IS NOT NULL '||
1139 'AND NOT EXISTS (SELECT 1 FROM mtl_secondary_locators msl '||
1140 ' WHERE wro.supply_locator_id = msl.secondary_locator '||
1141 ' AND wro.organization_id = msl.organization_id '||
1142 ' AND wro.inventory_item_id = msl.inventory_item_id) ';
1143
1144 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1145 'Jobs/Schedules with invalid Supply Locator - Not part of "Restricted Locators"',true,null,'Y',row_limit);
1146
1147 IF (dummy_num = row_limit) THEN
1148 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1149 END IF;
1150
1151 IF (dummy_num > 0) THEN
1152 reportStr := 'The rows returned above signify that there are jobs where supply locator is populated but it is not part of "Restricted Locators".';
1153 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1154 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1155 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1156 END IF;
1157
1158 --27. supply locator has invalid material status
1159 sqltxt :=
1160 'SELECT wro.wip_entity_id JobId, wro.organization_id OrganizationID, wro.operation_seq_num, '||
1161 ' wro.inventory_item_id,wro.supply_subinventory, '||
1162 ' inv_project.get_locator(wro.supply_locator_id, wro.organization_id) Locator '||
1163 'FROM wip_discrete_jobs wdj, wip_requirement_operations wro '||
1164 'WHERE ' || wdj_dyn_where_clause || ' wro.wip_entity_id = wdj.wip_entity_id '||
1165 'AND wro.organization_id = wdj.organization_id '||
1166 'AND wdj.status_type IN (1,3,4) '||
1167 'AND wro.supply_subinventory IS NOT NULL '||
1168 'AND wro.supply_locator_id IS NOT NULL '||
1169 'AND inv_material_status_grp.is_status_applicable( '||
1170 ' NULL, NULL, 44, NULL, NULL, '||
1171 ' wro.organization_id, wro.inventory_item_id, '||
1172 ' wro.supply_subinventory, wro.supply_locator_id, '||
1173 ' NULL, NULL, ''Z'') <> ''Y'' ';
1174
1175 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1176 'Jobs/Schedules invalid Supply Locator - Invalid Material Status',true,null,'Y',row_limit);
1177
1178 IF (dummy_num = row_limit) THEN
1179 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1180 END IF;
1181
1182 IF (dummy_num > 0) THEN
1183 reportStr := 'The rows returned above signify that there are jobs having supply locator with invalid material status.';
1184 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1185 reportStr := 'Please query up each job in the material requirements form, and update the supply subinventory/locator of the problematic component to a valid value.<BR><BR>';
1186 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1187 END IF;
1188
1189 statusStr := 'SUCCESS';
1190 isFatal := 'FALSE';
1191 fixInfo := 'OK';
1192 errStr :='No Error!';
1193
1194 <<l_test_end>>
1195 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1196 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1197
1198 END invalid_job_def_job;
1199
1200 PROCEDURE failed_job_close_job(inputs IN JTF_DIAG_INPUTTBL,
1201 report OUT NOCOPY JTF_DIAG_REPORT,
1202 reportClob OUT NOCOPY CLOB) IS
1203 reportStr LONG; -- REPORT
1204 sqltxt VARCHAR2(9999); -- SQL select statement
1205 c_username VARCHAR2(50); -- accept input for username
1206 statusStr VARCHAR2(50); -- SUCCESS or FAILURE
1207 errStr VARCHAR2(4000); -- error message
1208 fixInfo VARCHAR2(4000); -- fix tip
1209 isFatal VARCHAR2(50); -- TRUE or FALSE
1210 dummy_num NUMBER;
1211 row_limit NUMBER;
1212 l_job_id NUMBER;
1213 l_org_id NUMBER;
1214 we_dyn_where_clause VARCHAR2(1000):= null;
1215 wdj_dyn_where_clause VARCHAR2(1000) := null;
1216 wg_dyn_where_clause VARCHAR2(1000) := null; -- where caluse for generic source
1217 wti_dyn_where_clause VARCHAR2(1000) := null; -- where caluse for imventory tables
1218 l_check_failed_close_jobs varchar2(2000);
1219 BEGIN
1220 JTF_DIAGNOSTIC_ADAPTUTIL.setUpVars;
1221 JTF_DIAGNOSTIC_ADAPTUTIL.addStringToReport('@html');
1222 JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
1223 row_limit := 1000;
1224 -- accept input
1225 l_org_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Organization Id',inputs);
1226 l_job_id := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('Job Id',inputs);
1227 --l_cutoff_date := JTF_DIAGNOSTIC_ADAPTUTIL.getInputValue('CutoffDate',inputs);
1228
1229 if l_org_id is not null then
1230 we_dyn_where_clause := ' we.organization_id = '|| l_org_id || ' AND ';
1231 wdj_dyn_where_clause := ' wdj.organization_id = '|| l_org_id || ' AND ';
1232 wti_dyn_where_clause := ' organization_id = '|| l_org_id || ' AND ';
1233 wg_dyn_where_clause := ' organization_id = '|| l_org_id || ' AND ';
1234 end if;
1235 if l_job_id is not null then
1236 we_dyn_where_clause := we_dyn_where_clause || ' we.wip_entity_id = '|| l_job_id || ' AND ';
1237 wdj_dyn_where_clause := wdj_dyn_where_clause || ' wdj.wip_entity_id = '|| l_job_id || ' AND ';
1238 wti_dyn_where_clause := wti_dyn_where_clause || ' transaction_source_id = '|| l_job_id || ' AND ';
1239 wg_dyn_where_clause := wg_dyn_where_clause || ' wip_entity_id = '|| l_job_id || ' AND ';
1240 end if;
1241
1242
1243 l_check_failed_close_jobs :=
1244 ' (select wdj.wip_entity_id from wip_discrete_jobs wdj' ||
1245 ' where ' || wdj_dyn_where_clause || ' wdj.status_type = 15) ';
1246
1247 --1. This script will check for jobs that have failed in job close process due to pending transactions
1248 sqltxt :=
1249 ' select wip_entity_id JobID, organization_id OrganizationID, ''Pending MOVE Transactions Exists'' "Pending Txns.." '||
1250 ' from wip_move_txn_interface '||
1251 ' where ' || wg_dyn_where_clause || ' wip_entity_id in ' || l_check_failed_close_jobs ||
1252 ' UNION ALL '||
1253 ' select wip_entity_id JobID, organization_id OrganizationID, ''Pending RESOURCE Transactions Exists'' "Pending Txns.." '||
1254 ' from wip_cost_txn_interface '||
1255 ' where ' || wg_dyn_where_clause || ' wip_entity_id in ' || l_check_failed_close_jobs ||
1256 ' UNION ALL '||
1257 ' select transaction_source_id JobID, organization_id OrganizationID, ''Pending UNCOSTED Material Transactions Exists'' "Pending Txns.." '||
1258 ' from mtl_material_transactions '||
1259 ' where ' || wti_dyn_where_clause || ' transaction_source_type_id = 5 '||
1260 ' and costed_flag in (''N'',''E'') '||
1261 ' and transaction_source_id in ' || l_check_failed_close_jobs ||
1262 ' UNION ALL '||
1263 ' select transaction_source_id JobID, organization_id OrganizationID, ''Pending Material Transactions Exists'' "Pending Txns.." '||
1264 ' from mtl_material_transactions_temp mmtt '||
1265 ' where ' || wti_dyn_where_clause || ' transaction_source_type_id = 5 '||
1266 ' and transaction_source_id not in ( '||
1267 ' select txn_source_id '||
1268 ' from mtl_txn_request_lines '||
1269 ' where txn_source_id = mmtt.transaction_source_id '||
1270 ' and organization_id = mmtt.organization_id '||
1271 ' and line_status = 9) '||
1272 ' and transaction_source_id in ' || l_check_failed_close_jobs ||
1273 ' UNION ALL '||
1274 ' select wip_entity_id JobID, organization_id OrganizationID, ''Pending Operation Yields Exists'' "Pending Txns.." '||
1275 ' from wip_operation_yields '||
1276 ' where ' || wg_dyn_where_clause || ' status IN (1, 3) '||
1277 ' and wip_entity_id in ' || l_check_failed_close_jobs ||
1278 ' UNION ALL '||
1279 ' select we.wip_entity_id JobID, we.organization_id OrganizationID, ''Pending PUT-AWAY Transactions Exists'' "Pending Txns.." '||
1280 ' from wip_lpn_completions we, '||
1281 ' wms_license_plate_numbers lpn '||
1282 ' where ' || we_dyn_where_clause || ' we.lpn_id = lpn.lpn_id '||
1283 ' and lpn.lpn_context = 2 '||
1284 ' and wip_entity_id in ' || l_check_failed_close_jobs ;
1285
1286 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1287 'Jobs failed in job close process due to pending transactions',true,null,'Y',row_limit);
1288
1289 IF (dummy_num = row_limit) THEN
1290 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1291 END IF;
1292
1293 IF (dummy_num > 0) THEN
1294 reportStr := 'The rows returned above signify that jobs are failed to close due to pending transactions.';
1295 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1296 reportStr := 'Check the output and process the pending transactions against the job so that job can be closed.<BR><BR>';
1297 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint(reportStr);
1298 END IF;
1299
1300 --2 Jobs that are not closed but entity_type updated
1301 sqltxt :=
1302 ' Select substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, wdj.organization_id OrganizationID, '||
1303 ' decode(wdj.status_type, '||
1304 ' 1,''Unreleased'', '||
1305 ' 3, ''Released'', '||
1306 ' 4, ''Complete'', '||
1307 ' 5, ''Complete NoCharge'', '||
1308 ' 6, ''On Hold'', '||
1309 ' 7, ''Cancelled'', '||
1310 ' 8, ''Pend Bill Load'', '||
1311 ' 9, ''Failed Bill Load'', '||
1312 ' 10, ''Pend Rtg Load'', '||
1313 ' 11, ''Failed Rtg Load'', '||
1314 ' 12, ''Closed'', '||
1315 ' 13, ''Pending- Mass Loaded'', '||
1316 ' 14, ''Pending Close'', '||
1317 ' 15, ''Failed Close'', '||
1318 ' wdj.status_type) status_type, '||
1319 ' decode(entity_type,1, ''1=Discrete Job'', '||
1320 ' 2, ''2=Repetitive Assly'', '||
1321 ' 3, ''3=Closed Discr Job'', '||
1322 ' 4, ''4=Flow Schedule'', '||
1323 ' 5, ''5=Lot Based Job'', '||
1324 ' entity_type) entity_type, '||
1325 ' wdj.creation_date, '||
1326 ' wdj.date_released, '||
1327 ' wdj.date_completed '||
1328 ' from wip_discrete_jobs wdj, '||
1329 ' wip_entities we '||
1330 ' where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
1331 ' and wdj.organization_id = we.organization_id '||
1332 ' and wdj.status_type <> 12 '||
1333 ' and we.entity_type in (3,7,8) -- closed DJ, closed EAM, closed LBJ '||
1334 ' order by 1,2 ' ;
1335
1336 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1337 'Jobs that are not closed but its entity_type updated to closed',true,null,'Y',row_limit);
1338
1339 IF (dummy_num = row_limit) THEN
1340 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1341 END IF;
1342
1343 IF (dummy_num > 0) THEN
1344 reportStr := 'The rows returned above signify that there are jobs that are not closed but its entity_type updated to closed.';
1345 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1346 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open a service request against Oracle Work in Process for the data-fix and root-cause, and upload the output of this diagnostic test .<BR> <BR>');
1347 END IF;
1348
1349 --3 Jobs that are closed but entity_type not updated
1350 sqltxt :=
1351 ' select substr(we.wip_entity_name,1,15) Job, wdj.wip_entity_id JobId, wdj.organization_id OrganizationID, '||
1352 ' decode(wdj.status_type, '||
1353 ' 1,''Unreleased'', '||
1354 ' 3, ''Released'', '||
1355 ' 4, ''Complete'', '||
1356 ' 5, ''Complete NoCharge'', '||
1357 ' 6, ''On Hold'', '||
1358 ' 7, ''Cancelled'', '||
1359 ' 8, ''Pend Bill Load'', '||
1360 ' 9, ''Failed Bill Load'', '||
1361 ' 10, ''Pend Rtg Load'', '||
1362 ' 11, ''Failed Rtg Load'', '||
1363 ' 12, ''Closed'', '||
1364 ' 13, ''Pending- Mass Loaded'', '||
1365 ' 14, ''Pending Close'', '||
1366 ' 15, ''Failed Close'', '||
1367 ' wdj.status_type) status_type, '||
1368 ' decode(entity_type,1, ''1=Discrete Job'', '||
1369 ' 2, ''2=Repetitive Assly'', '||
1370 ' 3, ''3=Closed Discr Job'', '||
1371 ' 4, ''4=Flow Schedule'', '||
1372 ' 5, ''5=Lot Based Job'', '||
1373 ' entity_type) entity_type, '||
1374 ' wdj.creation_date, '||
1375 ' wdj.date_released, '||
1376 ' wdj.date_completed '||
1377 ' from wip_discrete_jobs wdj, '||
1378 ' wip_entities we '||
1379 ' where ' || we_dyn_where_clause || ' wdj.wip_entity_id = we.wip_entity_id '||
1380 ' and wdj.organization_id = we.organization_id '||
1381 ' and wdj.status_type = 12 '||
1382 ' and we.entity_type not in (3,7,8) '||
1383 ' order by 1,2';
1384
1385 dummy_num:= JTF_DIAGNOSTIC_COREAPI.display_sql(sqltxt,
1386 'Jobs that are closed but its entity_type not updated to closed',true,null,'Y',row_limit);
1387
1388 IF (dummy_num = row_limit) THEN
1389 JTF_DIAGNOSTIC_COREAPI.Line_Out('<BR> Output limited to the first '|| row_limit || ' rows to prevent an excessively large output file. <BR>');
1390 END IF;
1391
1392 IF (dummy_num > 0) THEN
1393 reportStr := 'The rows returned above signify that there are jobs that are closed but its entity_type not updated to closed.';
1394 JTF_DIAGNOSTIC_COREAPI.ErrorPrint(reportStr);
1395 JTF_DIAGNOSTIC_COREAPI.ActionErrorPrint('Please open a service request against Oracle Work in Process for the data-fix and root-cause, and upload the output of this diagnostic test.<BR> <BR>');
1396 END IF;
1397
1398 statusStr := 'SUCCESS';
1399 isFatal := 'FALSE';
1400 fixInfo := 'OK';
1401 errStr :='No Error!';
1402
1403 <<l_test_end>>
1404 report := JTF_DIAGNOSTIC_ADAPTUTIL.constructReport(statusStr,errStr,fixInfo,isFatal);
1405 reportClob := JTF_DIAGNOSTIC_ADAPTUTIL.getReportClob;
1406
1407 END failed_job_close_job;
1408
1409 END;