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