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