[Home] [Help]
PACKAGE BODY: APPS.WSM_JOBCOPIES_PUB
Source
1 PACKAGE BODY WSM_JobCopies_PUB AS
2 /* $Header: WSMPCPYB.pls 120.4.12020000.2 2012/07/11 09:04:42 sisankar ship $ */
3
4
5
6 /****************************
7 * *
8 * Refresh_JobCopies *
9 * *
10 /***************************/
11
12 PROCEDURE Refresh_JobCopies (x_err_buf OUT NOCOPY VARCHAR2,
13 x_err_code OUT NOCOPY NUMBER,
14 p_refresh_all_open_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
15 p_from_job_name IN VARCHAR2,
16 p_to_job_name IN VARCHAR2,
17 p_job_assembly_id IN NUMBER,
18 p_job_type IN NUMBER,
19 p_bill_item_id IN NUMBER,
20 p_alt_bom_designator IN VARCHAR2,
21 p_rtg_item_id IN NUMBER,
22 p_alt_rtg_designator IN VARCHAR2,
23 p_select_jobs_by_status IN NUMBER, -- 1=Yes, 2=No, Default=1
24 p_rel_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
25 p_unrel_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
26 p_onhold_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
27 p_complete_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
28 p_closed_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
29 p_cancelled_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
30 -- Added to fix bug #3483253 --
31 p_org_id IN NUMBER,
32 p_rout_rev_basis IN NUMBER, /* 1= Job revision Date,2=New revision Date-All jobs,3=New revision Date-Job Revision date less than new date ; */
33 p_dummy IN NUMBER, ---- this dummy parameter is added to conditionally enable and disable New Routing Revision Date Parameter
34 p_new_rev_date_rou IN VARCHAR2,-- Added for 12.1 Refresh Bom/Routing Revision Date Project
35 p_bom_rev_basis IN NUMBER, /* 1= Job revision Date,2=New revision Date-All jobs,3=New revision Date-Job Revision date less than new date ; */
36 p_dummy2 IN NUMBER, -- Added for 12.1 Refresh Bom/Routing Revision Date Project to enable/disable the New BOM Revision Date parameter dynamically.
37 p_new_rev_date_bom IN VARCHAR2 -- Added for 12.1 Refresh Bom/Routing Revision Date Project
38 )
39 IS
40
41 l_stmt_num NUMBER := 0;
42 l_cmn_bill_seq_id NUMBER;
43 l_cmn_rtg_seq_id NUMBER;
44
45 l_from_job_name VARCHAR2(240);
46 l_to_job_name VARCHAR2(240);
47 l_job_type NUMBER;
48 l_job_assembly_id NUMBER;
49 l_bill_item_id NUMBER;
50 l_alt_bom_designator VARCHAR2(10);
51 l_rtg_item_id NUMBER;
52 l_alt_rtg_designator VARCHAR2(10);
53 l_select_jobs_by_status NUMBER;
54 l_rel_jobs NUMBER;
55 l_unrel_jobs NUMBER;
56 l_onhold_jobs NUMBER;
57 l_complete_jobs NUMBER;
58 l_closed_jobs NUMBER;
59 l_cancelled_jobs NUMBER;
60 l_temp NUMBER;
61 l_msg VARCHAR2(2000);
62 l_count NUMBER;
63 l_acct_period_id NUMBER := 0; -- Added to fix bug #3958411
64 l_rout_rev_basis NUMBER; -- Added for 12.1 Refresh Bom/Routing Revision Date Project
65 l_new_rev_date_rou DATE; -- Added for 12.1 Refresh Bom/Routing Revision Date Project
66 l_bom_rev_basis NUMBER; -- Added for 12.1 Refresh Bom/Routing Revision Date Project
67 l_new_rev_date_bom DATE; -- Added for 12.1 Refresh Bom/Routing Revision Date Project
68 l_rtg_revision WIP_DISCRETE_JOBS.ROUTING_REVISION%TYPE; -- Added for 12.1 Refresh Bom/Routing Revision Date Project
69 l_bom_revision WIP_DISCRETE_JOBS.BOM_REVISION%TYPE; -- Added for 12.1 Refresh Bom/Routing Revision Date Project
70 l_timezone_enabled boolean := ( fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y' AND
71 fnd_profile.value('CLIENT_TIMEZONE_ID') IS NOT NULL AND
72 fnd_profile.value('SERVER_TIMEZONE_ID') IS NOT NULL AND
73 fnd_profile.value('CLIENT_TIMEZONE_ID') <>
74 fnd_profile.value('SERVER_TIMEZONE_ID'));
75
76 l_err_count number := 0;
77
78 type t_num_tbl is table of NUMBER index by binary_integer;
79 l_num_tbl t_num_tbl;
80
81 CURSOR refresh_jobs IS
82 SELECT wdj.wip_entity_id,
83 we.wip_entity_name,
84 wdj.organization_id,
85 wdj.primary_item_id,
86 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.routing_reference_id) routing_item_id, -- Fix for bug #3347947
87 wdj.alternate_routing_designator alt_rtg_desig,-- Fix for bug #3347947
88 wdj.common_routing_sequence_id,
89 --nvl(wdj.routing_revision_date, sysdate) routing_revision_date,
90 decode(l_rout_rev_basis,2,l_new_rev_date_rou,nvl(wdj.routing_revision_date, sysdate)) routing_revision_date,
91 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id) bill_item_id,-- Fix for bug #3347947
92 wdj.alternate_bom_designator alt_bom_desig,
93 WSMPUTIL.GET_JOB_BOM_SEQ_ID(wdj.wip_entity_id) bill_sequence_id,-- Fix for bug #3286849
94 wdj.common_bom_sequence_id,
95 -- wdj.bom_revision_date, --commented for 12.1 refresh bom/routing revision date project
96 decode(l_bom_rev_basis,2,l_new_rev_date_bom,wdj.bom_revision_date) bom_revision_date,
97 wdj.wip_supply_type,
98 wdj.status_type
99 FROM wip_discrete_jobs wdj,
100 wip_entities we
101 WHERE we.organization_id = p_org_id
102 AND we.organization_id = wdj.organization_id
103 AND we.wip_entity_id = wdj.wip_entity_id
104 AND we.entity_type in (5, 8)
105 AND we.wip_entity_name between
106 nvl(l_from_job_name, we.wip_entity_name)
107 and nvl(l_to_job_name, we.wip_entity_name)
108 AND wdj.job_type = nvl(l_job_type, wdj.job_type)
109 AND wdj.primary_item_id = nvl(l_job_assembly_id, wdj.primary_item_id)
110 AND nvl(wdj.common_bom_sequence_id, -1) =
111 nvl(l_cmn_bill_seq_id, nvl(wdj.common_bom_sequence_id, -1))
112 AND wdj.common_routing_sequence_id =
113 nvl(l_cmn_rtg_seq_id, wdj.common_routing_sequence_id)
114 -- Start : Fix for bug #3483253 --
115 -- Changed following condition to allow upgrading all statuses --
116 AND ( wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_unrel_jobs ,1, 1, 0), wdj.status_type)
117 OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_rel_jobs ,1, 3, 0), wdj.status_type)
118 OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_complete_jobs ,1, 4, 0), wdj.status_type)
119 OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_onhold_jobs ,1, 6, 0), wdj.status_type)
120 OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_closed_jobs ,1,12, 0), wdj.status_type)
121 OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_cancelled_jobs,1, 7, 0), wdj.status_type)
122 );
123
124 BEGIN
125
126 l_stmt_num := 10;
127
128 g_debug := FND_PROFILE.VALUE('MRP_DEBUG');
129
130 fnd_file.put_line(fnd_file.log, 'Parameters to Refresh_JobCopies are :');
131 fnd_file.put_line(fnd_file.log, ' refresh_all_open_jobs ='||p_refresh_all_open_jobs);
132 fnd_file.put_line(fnd_file.log, ', from_job_name ='||p_from_job_name );
133 fnd_file.put_line(fnd_file.log, ', to_job_name ='||p_to_job_name );
134 fnd_file.put_line(fnd_file.log, ', job_type ='||p_job_type );
135 fnd_file.put_line(fnd_file.log, ', job_assembly_id ='||p_job_assembly_id );
136 fnd_file.put_line(fnd_file.log, ', bill_item_id ='||p_bill_item_id );
137 fnd_file.put_line(fnd_file.log, ', alt_bom_designator ='||p_alt_bom_designator );
138 fnd_file.put_line(fnd_file.log, ', rtg_item_id ='||p_rtg_item_id );
139 fnd_file.put_line(fnd_file.log, ', alt_rtg_designator ='||p_alt_rtg_designator );
140 fnd_file.put_line(fnd_file.log, ', select_jobs_by_status ='||p_select_jobs_by_status);
141 fnd_file.put_line(fnd_file.log, ', rel_jobs ='||p_rel_jobs );
142 fnd_file.put_line(fnd_file.log, ', unrel_jobs ='||p_unrel_jobs );
143 fnd_file.put_line(fnd_file.log, ', onhold_jobs ='||p_onhold_jobs );
144 fnd_file.put_line(fnd_file.log, ', complete_jobs ='||p_complete_jobs );
145 fnd_file.put_line(fnd_file.log, ', closed_jobs ='||p_closed_jobs );
146 fnd_file.put_line(fnd_file.log, ', cancelled_jobs ='||p_cancelled_jobs );
147 fnd_file.put_line(fnd_file.log, ', org_id ='||p_org_id );
148 fnd_file.put_line(fnd_file.log, ', refresh_routing_based_on ='||p_rout_rev_basis ); --Added for 12.1 Refresh Bom/Routing Revision Date Project
149 fnd_file.put_line(fnd_file.log, ', New_revision_date_rou ='|| p_new_rev_date_rou ); --Added for 12.1 Refresh Bom/Routing Revision Date Project
150 fnd_file.put_line(fnd_file.log, ', refresh_bom_based_on ='||p_bom_rev_basis ); --Added for 12.1 Refresh Bom/Routing Revision Date Project
151 fnd_file.put_line(fnd_file.log, ', New_revision_date_bom ='|| p_new_rev_date_bom ); --Added for 12.1 Refresh Bom/Routing Revision Date Project
152
153
154
155
156
157 IF (WSMPUTIL.CREATE_LBJ_COPY_RTG_PROFILE(p_org_id) = 2) THEN
158 --"Profile 'WSM: Create Lot Based Jobs Copy Routing' is set to NO. Cannot refresh Job Copies. "
159 fnd_message.set_name('WSM', 'WSM_USE_COPY_NOT_SET_ERR');
160 fnd_file.put_line(fnd_file.log, fnd_message.get);
161
162 return;
163 ELSE
164 fnd_file.put_line(fnd_file.log, 'Refreshing the jobs...'); -- VJ remove
165 END IF;
166
167 l_stmt_num := 20;
168
169 IF (p_refresh_all_open_jobs = 2) -- No
170 AND (p_from_job_name IS NULL)
171 AND (p_to_job_name IS NULL)
172 AND (p_job_type IS NULL)
173 AND (p_job_assembly_id IS NULL)
174 AND (p_bill_item_id IS NULL)
175 AND (p_rtg_item_id IS NULL)
176 AND ((p_select_jobs_by_status = 1) OR (p_select_jobs_by_status IS NULL))-- Yes
177 AND ((p_rel_jobs = 2) OR (p_rel_jobs IS NULL)) -- No
178 AND ((p_unrel_jobs = 2) OR (p_unrel_jobs IS NULL)) -- No
179 AND ((p_onhold_jobs = 2) OR (p_onhold_jobs IS NULL)) -- No
180 AND ((p_complete_jobs = 2) OR (p_complete_jobs IS NULL)) -- No
181 AND ((p_closed_jobs = 2) OR (p_closed_jobs IS NULL)) -- No
182 AND ((p_cancelled_jobs = 2) OR (p_cancelled_jobs IS NULL)) -- No
183 -- Added to fix bug #3483253 --
184 THEN
185 --"Based on the concurrent request parameters, Job Copies for no jobs were refreshed. "
186 fnd_message.set_name('WSM', 'WSM_NO_JOBS_TO_REFR');
187 fnd_file.put_line(fnd_file.log, fnd_message.get);
188
189 return;
190 END IF;
191
192
193 l_from_job_name := p_from_job_name;
194 l_to_job_name := p_to_job_name;
195 l_job_type := p_job_type;
196 l_job_assembly_id := p_job_assembly_id;
197 l_bill_item_id := p_bill_item_id;
198 l_alt_bom_designator := p_alt_bom_designator;
199 l_rtg_item_id := p_rtg_item_id;
200 l_alt_rtg_designator := p_alt_rtg_designator;
201 l_select_jobs_by_status := nvl(p_select_jobs_by_status, 1);
202 l_rel_jobs := nvl(p_rel_jobs, 2);
203 l_unrel_jobs := nvl(p_unrel_jobs, 2);
204 l_onhold_jobs := nvl(p_onhold_jobs, 2);
205 l_complete_jobs := nvl(p_complete_jobs, 2);
206 l_closed_jobs := nvl(p_closed_jobs, 2);
207 l_cancelled_jobs := nvl(p_cancelled_jobs, 2);
208 l_rout_rev_basis := p_rout_rev_basis; --Added for 12.1 Refresh Bom/Routing Revision Date Project
209 l_bom_rev_basis := p_bom_rev_basis; --Added for 12.1 Refresh Bom/Routing Revision Date Project
210 l_rtg_revision := NULL; --Added for Refresh Bom/Routing Revision Date Project
211 l_bom_revision := NULL; --Added for Refresh Bom/Routing Revision Date Project
212
213
214 IF (p_refresh_all_open_jobs = 1) -- Yes
215 THEN
216 --"Based on the concurrent request parameter 'Refresh All Open Jobs', all other parameters are ignored."
217 fnd_message.set_name('WSM', 'WSM_REFR_PARAMS_IGNORED');
218 fnd_file.put_line(fnd_file.log, fnd_message.get);
219
220 -- Set parameters to their default values
221 l_from_job_name := NULL;
222 l_to_job_name := NULL;
223 l_job_type := NULL;
224 l_job_assembly_id := NULL;
225 l_bill_item_id := NULL;
226 l_alt_bom_designator := NULL;
227 l_cmn_bill_seq_id := NULL;
228 l_rtg_item_id := NULL;
229 l_alt_rtg_designator := NULL;
230 l_cmn_rtg_seq_id := NULL;
231 l_select_jobs_by_status := 1;
232 l_rel_jobs := 1;
233 l_unrel_jobs := 1;
234 l_onhold_jobs := 1;
235 l_complete_jobs := 1;
236 l_closed_jobs := 1;
237 l_cancelled_jobs := 1;
238
239 ELSE --IF (p_refresh_all_open_jobs = 0) -- No
240
241 l_stmt_num := 30;
242
243 IF (p_bill_item_id IS NOT NULL) THEN
244 -- Get the common bill sequence id
245 BEGIN
246 SELECT common_bill_sequence_id
247 INTO l_cmn_bill_seq_id
248 FROM bom_bill_of_materials
249 WHERE organization_id = p_org_id
250 AND assembly_item_id = l_bill_item_id
251 AND nvl(alternate_bom_designator, '-1') =
252 nvl(l_alt_bom_designator, '-1');
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
256 fnd_message.set_token('FLD_NAME', 'Alternate Bill Designator');
257 fnd_file.put_line(fnd_file.log, fnd_message.get);
258 return;
259
260 WHEN OTHERS THEN
261 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
262 fnd_message.set_token('FLD_NAME', 'Alternate Bill Designator');
263 fnd_file.put_line(fnd_file.log, fnd_message.get);
264 return;
265 END;
266 END IF;
267
268 l_stmt_num := 40;
269
270 IF (p_rtg_item_id IS NOT NULL) THEN
271 -- Get the common routing sequence id
272 BEGIN
273 SELECT common_routing_sequence_id
274 INTO l_cmn_rtg_seq_id
275 FROM bom_operational_routings
276 WHERE organization_id = p_org_id
277 AND assembly_item_id = l_rtg_item_id
278 AND nvl(alternate_routing_designator, '-1') =
279 nvl(l_alt_rtg_designator, '-1');
280 EXCEPTION
281 WHEN NO_DATA_FOUND THEN
282 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
283 fnd_message.set_token('FLD_NAME', 'Alternate Routing Designator');
284 fnd_file.put_line(fnd_file.log, fnd_message.get);
285 return;
286
287 WHEN OTHERS THEN
288 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
289 fnd_message.set_token('FLD_NAME', 'Alternate Routing Designator');
290 fnd_file.put_line(fnd_file.log, fnd_message.get);
291 return;
292 END;
293 END IF;
294
295 IF (l_select_jobs_by_status = 2) THEN
296 -- =No, this implies select all statuses
297 l_rel_jobs := 1;
298 l_unrel_jobs := 1;
299 l_onhold_jobs := 1;
300 l_complete_jobs := 1;
301 l_closed_jobs := 1;
302 l_cancelled_jobs := 1;
303 END IF;
304 END IF;
305
306 /* Added for 12.1 Refresh Bom/Routing Revision Date Project to change the new revision date for BOM or Routing to Server Time Zone */
307 IF(l_rout_rev_basis IN (2,3)) THEN
308 IF (l_timezone_enabled) THEN
309 fnd_file.put_line(fnd_file.log, 'Timezone Convension is enabled and calling adjust_datetime API ');
310 l_new_rev_date_rou := fnd_timezone_pub.adjust_datetime( date_time => fnd_conc_date.string_to_date(p_new_rev_date_rou),
311 from_tz => fnd_timezones.get_client_timezone_code,
312 to_tz => fnd_timezones.get_server_timezone_code);
313 ELSE
314 fnd_file.put_line(fnd_file.log, 'Timezone Conversion is not enabled');
315 l_new_rev_date_rou := fnd_conc_date.string_to_date(p_new_rev_date_rou);
316 END IF;
317 END IF;
318 IF(l_bom_rev_basis IN (2,3)) THEN
319 IF (l_timezone_enabled) THEN
320 l_new_rev_date_bom := fnd_timezone_pub.adjust_datetime(date_time => fnd_conc_date.string_to_date(p_new_rev_date_bom),
321 from_tz => fnd_timezones.get_client_timezone_code,
322 to_tz => fnd_timezones.get_server_timezone_code);
323 ELSE
324 l_new_rev_date_bom := fnd_conc_date.string_to_date(p_new_rev_date_bom);
325 END IF;
326 END IF;
327
328 l_stmt_num := 50;
329
330 IF (g_debug = 'Y') THEN
331 SELECT count(*)
332 INTO l_count
333 FROM wip_discrete_jobs wdj,
334 wip_entities we
335 WHERE we.organization_id = p_org_id
336 AND we.organization_id = wdj.organization_id
337 AND we.wip_entity_id = wdj.wip_entity_id
338 AND we.entity_type in (5, 8)
339 AND we.wip_entity_name between
340 nvl(l_from_job_name, we.wip_entity_name)
341 and nvl(l_to_job_name, we.wip_entity_name)
342 AND wdj.job_type = nvl(l_job_type, wdj.job_type)
343 AND wdj.primary_item_id = nvl(l_job_assembly_id, wdj.primary_item_id)
344 AND nvl(wdj.common_bom_sequence_id, -1) =
345 nvl(l_cmn_bill_seq_id, nvl(wdj.common_bom_sequence_id, -1))
346 AND wdj.common_routing_sequence_id =
347 nvl(l_cmn_rtg_seq_id, wdj.common_routing_sequence_id)
348 AND (wdj.status_type = decode (l_select_jobs_by_status,
349 1, decode(l_unrel_jobs, 1, 1, 0),
350 wdj.status_type)
351 OR wdj.status_type = decode (l_select_jobs_by_status,
352 1, decode(l_rel_jobs, 1, 3, 0),
353 wdj.status_type)
354 OR wdj.status_type = decode (l_select_jobs_by_status,
355 1, decode(l_complete_jobs, 1, 4, 0),
356 wdj.status_type)
357 OR wdj.status_type = decode (l_select_jobs_by_status,
358 1, decode(l_onhold_jobs, 1, 6, 0),
359 wdj.status_type)
360 OR wdj.status_type = decode (l_select_jobs_by_status,
361 1, decode(l_closed_jobs, 1, 12, 0),
362 wdj.status_type)
363 OR wdj.status_type = decode (l_select_jobs_by_status,
364 1, decode(l_cancelled_jobs, 1, 7, 0),
365 wdj.status_type)
366
367 );
368 fnd_file.put_line(fnd_file.log, l_count||' jobs will be refreshed');
369 END IF;
370
371 l_stmt_num := 60;
372
373 IF (g_debug = 'Y') THEN
374 fnd_file.put_line(fnd_file.log, 'Variables used in refresh_jobs cursor are :');
375 fnd_file.put_line(fnd_file.log, ' l_from_job_name ='||l_from_job_name);
376 fnd_file.put_line(fnd_file.log, ', l_to_job_name ='||l_to_job_name);
377 fnd_file.put_line(fnd_file.log, ', l_job_type ='||l_job_type);
378 fnd_file.put_line(fnd_file.log, ', l_job_assembly_id ='||l_job_assembly_id);
379 fnd_file.put_line(fnd_file.log, ', l_cmn_bill_seq_id ='||l_cmn_bill_seq_id);
380 fnd_file.put_line(fnd_file.log, ', l_cmn_rtg_seq_id ='||l_cmn_rtg_seq_id);
381 fnd_file.put_line(fnd_file.log, ', l_unrel_jobs ='||l_unrel_jobs);
382 fnd_file.put_line(fnd_file.log, ', l_rel_jobs ='||l_rel_jobs);
383 fnd_file.put_line(fnd_file.log, ', l_complete_jobs ='||l_complete_jobs);
384 fnd_file.put_line(fnd_file.log, ', l_onhold_jobs ='||l_onhold_jobs);
385 fnd_file.put_line(fnd_file.log, ', l_closed_jobs ='||l_closed_jobs);
386 fnd_file.put_line(fnd_file.log, ', l_cancelled_jobs ='||l_cancelled_jobs);
387 fnd_file.put_line(fnd_file.log, ', l_rout_rev_basis ='||l_rout_rev_basis ); --Added for 12.1 Refresh Bom/Routing Revision Date project
388 fnd_file.put_line(fnd_file.log, ', l_new_rev_date_rou ='|| to_char(l_new_rev_date_rou,'DD-MON-YYYY HH24:MI:SS') ); --Added for 12.1 Refresh Bom/Routing Revision Date project
389 fnd_file.put_line(fnd_file.log, ', l_bom_rev_basis ='||l_bom_rev_basis ); --Added for 12.1 Refresh Bom/Routing Revision Date project
390 fnd_file.put_line(fnd_file.log, ', l_new_rev_date_bom ='|| to_char(l_new_rev_date_bom,'DD-MON-YYYY HH24:MI:SS') ); --Added for 12.1 Refresh Bom/Routing Revision Date project
391
392 END IF;
393
394 FOR cur_refresh_jobs IN refresh_jobs
395 LOOP
396
397 x_err_code := 0;
398 x_err_buf := NULL;
399
400 l_stmt_num := 70;
401
402 --*****************************************************************************************************
403 /* Added for 12.1 Refresh Bom/Routing Revision Date Project to change the revision_date for BOM or
404 routing if user selects to apply New Revision Date to only jobs with revision date less than New Revision date */
405
406 IF (l_rout_rev_basis = 3 AND cur_refresh_jobs.routing_revision_date <= l_new_rev_date_rou ) THEN
407
408 cur_refresh_jobs.routing_revision_date := l_new_rev_date_rou;
409
410 END IF;
411
412
413 IF (l_bom_rev_basis = 3 AND nvl(cur_refresh_jobs.bom_revision_date,l_new_rev_date_bom) <= l_new_rev_date_bom ) THEN
414
415 cur_refresh_jobs.bom_revision_date := l_new_rev_date_bom;
416
417 END IF;
418
419 --*******************************************************************************************************
420
421
422 -- Fix for bug #3677276 : Moved update wo.wsm_op_seq_num stmt
423 -- to inside create_jobcopies.
424
425 -- Start : Fix for bug #3958411 --
426 l_acct_period_id := -1; -- A valid value
427
428 IF (cur_refresh_jobs.status_type = 12) THEN -- Closed job
429 -- Check if it is in an open accounting period.
430 l_acct_period_id := 0;
431
432 BEGIN
433 l_stmt_num := 75;
434 SELECT OAP.acct_period_id
435 INTO l_acct_period_id
436 FROM ORG_ACCT_PERIODS OAP,
437 WIP_DISCRETE_JOBS WDJ
438 WHERE WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
439 AND WDJ.ORGANIZATION_ID = cur_refresh_jobs.organization_id
440 AND OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
441 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (WDJ.DATE_CLOSED,
442 wdj.organization_id)
443 BETWEEN OAP.PERIOD_START_DATE AND OAP.SCHEDULE_CLOSE_DATE
444 AND OAP.OPEN_FLAG = 'Y';
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 l_acct_period_id := 0;
449
450 WHEN OTHERS THEN
451 l_acct_period_id := 0;
452 END;
453
454 IF (l_acct_period_id <> 0) THEN -- Acct period found
455 l_acct_period_id := -1; -- Make it a valid value
456 END IF;
457 END IF;
458
459 IF (l_acct_period_id = -1) THEN -- {
460
461 -- End : Fix for bug #3958411 --
462
463 l_stmt_num := 80;
464 -- Modified for 11064947.
465 Begin
466 l_stmt_num := 81;
467 -- Explicitly Lock the job details for Bug 12409063.
468 /*
469 select 1 bulk collect into l_num_tbl
470 from WIP_REQUIREMENT_OPERATIONS WRO,
471 WIP_OPERATION_RESOURCE_USAGE WORU,
472 WIP_SUB_OPERATION_RESOURCES WSOR,
473 WIP_OPERATION_RESOURCES WOR,
474 WIP_OPERATIONS WO,
475 WIP_DISCRETE_JOBS WDJ,
476 WSM_COPY_OPERATIONS WCO,
477 WSM_COPY_OP_NETWORKS WCON,
478 WSM_COPY_OP_RESOURCES WCOR,
479 WSM_COPY_OP_RESOURCE_USAGE WCORU,
480 WSM_COPY_REQUIREMENT_OPS WCRO,
481 WSM_LOT_BASED_JOBS WLBJ,
482 WIP_OPERATION_YIELDS WOY
483 where WO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
484 and WOR.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID
485 and WOR.OPERATION_SEQ_NUM (+) = WO.OPERATION_SEQ_NUM
486 and WSOR.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID
487 and WSOR.OPERATION_SEQ_NUM (+) = WO.OPERATION_SEQ_NUM
488 and WORU.WIP_ENTITY_ID (+) = WOR.WIP_ENTITY_ID
489 and WORU.OPERATION_SEQ_NUM (+) = WOR.OPERATION_SEQ_NUM
490 and WORU.RESOURCE_SEQ_NUM (+) = WOR.RESOURCE_SEQ_NUM
491 and WRO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
492 and WCO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
493 and WCON.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
494 and WCOR.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
495 and WCORU.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
496 and WCRO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
497 and WLBJ.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
498 and WOY.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
499 and WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
500 for update NOWAIT;
501 */
502
503 /* select 1 bulk collect into l_num_tbl
504 from WIP_OPERATION_RESOURCES WOR,
505 WIP_OPERATIONS WO,
506 WIP_DISCRETE_JOBS WDJ,
507 WSM_COPY_OPERATIONS WCO,
508 WSM_COPY_OP_RESOURCES WCOR
509 where WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
510 and WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
511 and WOR.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
512 and WOR.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
513 and WCO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
514 and WCO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
515 and WCOR.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
516 and WCOR.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
517 and WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
518 and WDJ.ORGANIZATION_ID = cur_refresh_jobs.organization_id
519 for update NOWAIT;
520 */
521
522 select 1 bulk collect into l_num_tbl
523 from WIP_DISCRETE_JOBS WDJ
524 where WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
525 and WDJ.ORGANIZATION_ID = cur_refresh_jobs.organization_id
526 for update NOWAIT;
527 l_num_tbl.delete;
528 select 1 bulk collect into l_num_tbl
529 from WIP_OPERATIONS WO
530 where WO.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
531 and WO.ORGANIZATION_ID = cur_refresh_jobs.organization_id
532 for update NOWAIT;
533 l_num_tbl.delete;
534 select 1 bulk collect into l_num_tbl
535 from WIP_OPERATION_RESOURCES WOR
536 where WOR.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
537 and WOR.ORGANIZATION_ID = cur_refresh_jobs.organization_id
538 for update NOWAIT;
539 l_num_tbl.delete;
540 select 1 bulk collect into l_num_tbl
541 from WIP_REQUIREMENT_OPERATIONS WRO
542 where WRO.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
543 and WRO.ORGANIZATION_ID = cur_refresh_jobs.organization_id
544 for update NOWAIT;
545 l_num_tbl.delete;
546
547 l_stmt_num := 82;
548 WSM_JobCopies_PVT.Create_JobCopies -- Call #1
549 (
550 x_err_buf => x_err_buf,
551 x_err_code => x_err_code,
552 p_wip_entity_id => cur_refresh_jobs.wip_entity_id,
553 p_org_id => cur_refresh_jobs.organization_id,
554 p_primary_item_id => cur_refresh_jobs.primary_item_id,
555 p_routing_item_id => cur_refresh_jobs.routing_item_id,-- Fix for bug #3347947
556 p_alt_rtg_desig => cur_refresh_jobs.alt_rtg_desig,-- Fix for bug #3347947
557 p_rtg_seq_id => NULL,-- Will be NULL till reqd for some functionality
558 p_common_rtg_seq_id => cur_refresh_jobs.common_routing_sequence_id,
559 p_rtg_rev_date => cur_refresh_jobs.routing_revision_date,
560 p_bill_item_id => cur_refresh_jobs.bill_item_id,-- Fix for bug #3347947
561 p_alt_bom_desig => cur_refresh_jobs.alt_bom_desig,
562 p_bill_seq_id => cur_refresh_jobs.bill_sequence_id,-- To fix bug #3286849
563 p_common_bill_seq_id => cur_refresh_jobs.common_bom_sequence_id,
564 p_bom_rev_date => cur_refresh_jobs.bom_revision_date,
565 p_wip_supply_type => cur_refresh_jobs.wip_supply_type,
566 p_last_update_date => sysdate,
567 p_last_updated_by => fnd_global.user_id,
568 p_last_update_login => fnd_global.login_id,
569 p_creation_date => sysdate,
570 p_created_by => fnd_global.user_id,
571 p_request_id => fnd_global.conc_request_id,
572 p_program_app_id => fnd_global.prog_appl_id,
573 p_program_id => fnd_global.conc_program_id,
574 p_program_update_date => sysdate,
575 p_inf_sch_flag => 'Y',
576 p_inf_sch_mode => NULL,
577 p_inf_sch_date => NULL
578 );
579 l_stmt_num := 83;
580 Exception
581 when others then
582 x_err_buf := sqlerrm(sqlcode);
583 x_err_code := -100;
584 fnd_file.put_line(fnd_file.log, ' In Exception after Create_JobCopies Call for job : '|| cur_refresh_jobs.wip_entity_name ||' Error '||x_err_buf );
585 end;
586 l_stmt_num := 84;
587 -- MES:Populate CURRENT_RTG_OP_SEQ_NUM
588 IF (x_err_code = 0) OR (x_err_code IS NULL) OR (x_err_code = -1) THEN
589
590 update wsm_lot_based_jobs wsm
591 set wsm.current_rtg_op_seq_num = null
592 where wsm.wip_entity_id = cur_refresh_jobs.wip_entity_id
593 and not exists (select 1 from wsm_copy_operations wco
594 where wco.operation_seq_num = nvl(wsm.current_job_op_seq_num,-1)
595 and wco.wip_entity_id = cur_refresh_jobs.wip_entity_id);
596 -- END IF;
597 --*************************************************************************************************************
598 /* Added for 12.1 Refresh Bom/Routing Revision Date Project to change the revision_date for BOM or
599 routing in WDJ if user selects new revision date */
600
601 IF (l_rout_rev_basis IN (2,3)) OR (l_bom_rev_basis IN (2,3)) THEN
602
603 IF (l_rout_rev_basis IN (2,3)) THEN
604 BEGIN
605 wip_revisions.routing_revision( p_organization_id => cur_refresh_jobs.organization_id,
606 p_item_id => cur_refresh_jobs.routing_item_id,
607 p_revision => l_rtg_revision,
608 p_revision_date => cur_refresh_jobs.routing_revision_date,
609 p_start_date => cur_refresh_jobs.routing_revision_date
610 );
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 fnd_file.put_line(fnd_file.log, 'cannot refresh job '|| cur_refresh_jobs.wip_entity_name ||' as there is no valid routing revision at new routing revision date' );
615 x_err_code := -2;
616
617 END;
618 END IF;
619
620 IF (l_bom_rev_basis IN (2,3)) THEN
621 BEGIN
622 wip_revisions.bom_revision( p_organization_id => cur_refresh_jobs.organization_id,
623 p_item_id => cur_refresh_jobs.bill_item_id,
624 p_revision => l_bom_revision,
625 p_revision_date => cur_refresh_jobs.bom_revision_date,
626 p_start_date => cur_refresh_jobs.bom_revision_date
627 );
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 fnd_file.put_line(fnd_file.log, 'cannot refresh job '|| cur_refresh_jobs.wip_entity_name ||' as there is no valid bom revision at new bom revision date' );
632 x_err_code := -2;
633
634 END;
635 END IF;
636
637
638 UPDATE WIP_DISCRETE_JOBS
639 SET routing_revision_date = cur_refresh_jobs.routing_revision_date,
640 routing_revision = nvl(l_rtg_revision,routing_revision),
641 bom_revision_date = cur_refresh_jobs.bom_revision_date,
642 bom_revision = nvl(l_bom_revision,bom_revision)
643 where wip_entity_id = cur_refresh_jobs.wip_entity_id;
644
645 END IF;
646 --**************************************************************************************************************
647 END IF;
648 IF (x_err_code = 0) OR (x_err_code IS NULL)THEN
649 l_stmt_num := 90;
650 --"Organization: ORG_NAME Job: JOB_NAME"
651 fnd_message.set_name('WSM', 'WSM_JOB_LIST');
652 fnd_message.set_token('ORG_NAME', cur_refresh_jobs.organization_id);
653 fnd_message.set_token('JOB_NAME', cur_refresh_jobs.wip_entity_name||
654 '('||cur_refresh_jobs.wip_entity_id||')');
655 l_msg := fnd_message.get;
656
657 --"Job Copies STATUS Refreshed."
658 fnd_message.set_name('WSM', 'WSM_REFR_STATUS');
659 fnd_message.set_token('STATUS', NULL);
660 fnd_file.put_line(fnd_file.log, l_msg||' '||fnd_message.get
661 );
662
663 commit; -- Added to fix bug #3465125
664
665 ELSIF (x_err_code = -1) THEN -- Warning
666 l_stmt_num := 100;
667 --"Organization: ORG_NAME Job: JOB_NAME"
668 fnd_message.set_name('WSM', 'WSM_JOB_LIST');
669 fnd_message.set_token('ORG_NAME', cur_refresh_jobs.organization_id);
670 fnd_message.set_token('JOB_NAME', cur_refresh_jobs.wip_entity_name||
671 '('||cur_refresh_jobs.wip_entity_id||')');
672 l_msg := fnd_message.get;
673
674 --"Job Copies STATUS Refreshed."
675 fnd_message.set_name('WSM', 'WSM_REFR_STATUS');
676 fnd_message.set_token('STATUS', NULL);
677 fnd_file.put_line(fnd_file.log, l_msg||' '||fnd_message.get||' '||x_err_buf
678 );
679
680 commit; -- Added to fix bug #3465125
681
682 ELSE -- Error
683 l_stmt_num := 110;
684 --"Organization: ORG_NAME Job: JOB_NAME"
685 fnd_message.set_name('WSM', 'WSM_JOB_LIST');
686 fnd_message.set_token('ORG_NAME', cur_refresh_jobs.organization_id);
687 fnd_message.set_token('JOB_NAME', cur_refresh_jobs.wip_entity_name||
688 '('||cur_refresh_jobs.wip_entity_id||')');
689 l_msg := fnd_message.get;
690
691 --"Job Copies STATUS Refreshed."
692 fnd_message.set_name('WSM', 'WSM_REFR_STATUS');
693 fnd_message.set_token('STATUS', 'Not');
694 fnd_file.put_line(fnd_file.log, l_msg||' '||fnd_message.get||' '||x_err_buf
695 );
696 -- Modified for 11064947.
697 l_err_count := l_err_count+1;
698 rollback; -- Added to fix bug #3465125
699
700 l_stmt_num := 120;
701
702 BEGIN
703 SELECT 1
704 INTO l_temp
705 FROM WSM_LOT_BASED_JOBS
706 WHERE wip_entity_id = cur_refresh_jobs.wip_entity_id;
707
708 EXCEPTION
709 WHEN NO_DATA_FOUND THEN
710 l_stmt_num := 130;
711 INSERT into WSM_LOT_BASED_JOBS
712 (WIP_ENTITY_ID,
713 ORGANIZATION_ID,
714 ON_REC_PATH,
715 INTERNAL_COPY_TYPE,
716 COPY_PARENT_WIP_ENTITY_ID,
717 INFINITE_SCHEDULE,
718 ROUTING_REFRESH_DATE,
719 LAST_UPDATE_DATE,
720 LAST_UPDATED_BY,
721 LAST_UPDATE_LOGIN,
722 CREATION_DATE,
723 CREATED_BY,
724 REQUEST_ID,
725 PROGRAM_APPLICATION_ID,
726 PROGRAM_ID,
727 PROGRAM_UPDATE_DATE
728 )
729 VALUES
730 (cur_refresh_jobs.wip_entity_id,
731 cur_refresh_jobs.organization_id,
732 'N', -- ON_REC_PATH
733 3, -- INTERNAL_COPY_TYPE : -- Copies not existing due to Upgrade
734 -- and incorrect due to Refresh
735 NULL, -- COPY_PARENT_WIP_ENTITY_ID
736 NULL, -- INFINITE_SCHEDULE
737 SYSDATE, -- ROUTING_REFRESH_DATE
738 sysdate,
739 fnd_global.user_id,
740 fnd_global.login_id,
741 sysdate,
742 fnd_global.user_id,
743 fnd_global.conc_request_id,
744 fnd_global.prog_appl_id,
745 fnd_global.conc_program_id,
746 sysdate
747 );
748 commit;
749
750 WHEN OTHERS THEN
751 NULL;
752 END;
753
754 END IF;
755
756 END IF; --IF (l_acct_period_id = -1) } Added to fix bug #3958411
757
758 END LOOP;
759
760 l_stmt_num := 140;
761 -- Modified for 11064947.
762 if l_err_count = 0 then
763 x_err_code := 0;
764 x_err_buf := NULL;
765 else
766 x_err_code := 1;
767 x_err_buf := 'The Refresh program could not refresh some jobs. Please check the log file for details';
768 end if;
769
770 EXCEPTION
771 WHEN others THEN
772 x_err_code := SQLCODE;
773 x_err_buf := 'Refresh_JobCopies('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
774 fnd_file.put_line(fnd_file.log, x_err_buf);
775
776 END Refresh_JobCopies;
777
778
779 END WSM_JobCopies_PUB;