4 /*======================= CHV_BUILD_SCHEDULES ===============================*/
1 PACKAGE BODY CHV_BUILD_SCHEDULES as
2 /* $Header: CHVPRSBB.pls 120.2 2006/03/08 23:06:49 ptkumar noship $ */
3
5
6
7 /*=============================================================================
8
9 PROCEDURE NAME: build_schedule()
10
11 =============================================================================*/
12 PROCEDURE build_schedule(p_schedule_category in VARCHAR2,
13 p_autoschedule_flag in VARCHAR2,
14 p_schedule_type in VARCHAR2,
15 p_schedule_subtype in VARCHAR2 DEFAULT null,
16 p_schedule_num in VARCHAR2 DEFAULT null,
17 p_schedule_revision IN NUMBER DEFAULT null,
18 p_horizon_start_date in DATE,
19 p_bucket_pattern_id in NUMBER DEFAULT null,
20 p_multi_org_flag in VARCHAR2 DEFAULT null,
21 p_ship_to_organization_id in NUMBER DEFAULT null,
22 p_mrp_compile_designator in VARCHAR2 DEFAULT null,
23 p_mps_schedule_designator in VARCHAR2 DEFAULT null,
24 p_drp_compile_designator in VARCHAR2 DEFAULT null,
25 p_include_future_releases in VARCHAR2 DEFAULT null,
26 p_autoconfirm_flag in VARCHAR2 DEFAULT null,
27 p_communication_code in VARCHAR2 DEFAULT null,
28 p_vendor_id in NUMBER DEFAULT null,
29 p_vendor_site_id in NUMBER DEFAULT null,
30 p_category_set_id in NUMBER DEFAULT null,
31 p_struct_num in NUMBER DEFAULT null,
32 p_yes_no in VARCHAR2 DEFAULT null,
33 p_category_id in NUMBER DEFAULT null,
34 p_item_org in NUMBER DEFAULT null,
35 p_item_id in NUMBER DEFAULT null,
36 p_scheduler_id in NUMBER DEFAULT null,
37 p_buyer_id in NUMBER DEFAULT null,
38 p_planner_code in VARCHAR2 DEFAULT null,
39 p_owner_id in NUMBER DEFAULT null,
40 p_batch_id in NUMBER DEFAULT null,
41 p_exclude_zero_quantity_lines in VARCHAR2 DEFAULT null) IS
42
43 x_progress VARCHAR2(3) := NULL; -- For debugging purpose
44
45 x_distinct_v_vs_org_id VARCHAR2(240); -- Distinct concatenation of 3 ids
46 -- (for the first parameter in the
47 -- cursor, no real use of it).
48
49 -- Following variables whose values should be retrieved from cursors.
50 x_vendor_id NUMBER;
51 x_vendor_site_id NUMBER;
52 x_organization_id NUMBER;
53 /* Bug# 2933042 - Increased the size of x_organization_name from 60
54 to 240 to make it UTF8 compliant */
55 x_organization_name VARCHAR2(240);
56
57 /* Bug#2823839 Increased the width of the x_vendor_name by replacing
58 ** VARCHAR2(80) with po_vendors.vendor_name type(which is 240) */
59
60 x_vendor_name po_vendors.vendor_name%type;
61 x_vendor_code VARCHAR2(15);
62 x_item_desc VARCHAR2(240);
63 x_bucket_pattern_id NUMBER;
64 x_mrp_compile_designator VARCHAR2(10);
65 x_mps_schedule_designator VARCHAR2(10);
66 x_drp_compile_designator VARCHAR2(10);
67 x_schedule_subtype VARCHAR2(25);
68 x_schedule_type VARCHAR2(25);
69 x_schedule_horizon_start DATE;
70 x_include_future_releases_flag VARCHAR2(1);
71
72 -- Following variables whose values are calculated within the procedure.
73 x_dummy VARCHAR2(1);
74 x_dummy_num1 NUMBER;
75 x_dummy_num2 NUMBER;
76 x_schedule_id NUMBER;
77 x_schedule_num VARCHAR2(20);
78 x_schedule_revision NUMBER;
79 x_user_id NUMBER;
80 x_login_id NUMBER;
81 x_horizon_end_date DATE;
82 x_confirmed_schedule VARCHAR2(1) := '';
83 x_transmission_method VARCHAR2(1) := 'N';
84 x_item_created VARCHAR2(1) := 'N'; -- Indicates if atleast
85 -- one item is created
86 -- for a schedule header.
87
88 x_old_schedule_id NUMBER;
89 x_do_not_send_edi VARCHAR2(1) := 'N';
90
91 x_confirm_source VARCHAR2(15);
92 x_return_number NUMBER;
93 x_ece_path VARCHAR2(80);
94 x_ece_file VARCHAR2(30);
95 x_ece_path_file VARCHAR2(120);
96 x_edi_set VARCHAR2(1) := 'N';
97
98 x_str VARCHAR2(480);
99 x_org_id NUMBER; /* Bug 2616988 fixed. added x_org_id */
100
101 -- 3 PL/SQL tables used for calculating bucket quantities.
102 x_bucket_descriptor_table chv_create_buckets.bkttable;
103 x_bucket_start_date_table chv_create_buckets.bkttable;
104 x_bucket_end_date_table chv_create_buckets.bkttable;
105
106 -- The MRP, MPS, or DRP Designator will optionally be passed in.
107 -- From the WB we will default the designator into the fields
108 -- from the org options. The user has the option to change
109 -- the value of the fields in the form.
110 CURSOR C_ORGS IS
111 SELECT coo.organization_id,
112 p_mrp_compile_designator,
113 p_mps_schedule_designator,
114 p_drp_compile_designator
115 FROM chv_org_options coo
116 WHERE nvl(p_ship_to_organization_id,
117 coo.organization_id) = coo.organization_id;
118
119 -- When we a running in a non-multi-org situation we are going
123 -- situation we are going to create a schedule header and then
120 -- to look through all of the orgs and create schedule headers
121 -- for the first organization and then create schedule headers
122 -- for the next organization. When we are running in a multi-org
124 -- for each schedule header create a schedule item for each
125 -- organization. We need to have a dummy cursor for this.
126 CURSOR C_NOORGS IS
127 SELECT dummy,
128 p_mrp_compile_designator,
129 p_mps_schedule_designator,
130 p_drp_compile_designator
131 FROM dual;
132
133 -- For all schedules, we will create a new schedule header for each
134 -- vendor, vendor site, bucket pattern, schedule type,
135 -- schedule sub type, and org combination.
136 -- The schedule subtype and bucket pattern will always be provided
137 -- from the workbench. We will not use the values from the asl.
138 -- We must have the sub-query to return one record in the situation
139 -- where we have a global record and a local record.
140 CURSOR C_SINGLE_ORG_WB_BPM IS
141 SELECT DISTINCT(paa.vendor_id||paa.vendor_site_id),
142 p_bucket_pattern_id,
143 paa.vendor_id,
144 paa.vendor_site_id,
145 p_schedule_subtype
146 FROM po_asl_attributes_val_v paa
147 WHERE ((paa.using_organization_id = -1 and not exists
148 (SELECT *
149 FROM po_asl_attributes_val_v paa2
150 WHERE paa2.using_organization_id = x_organization_id
151 AND paa2.vendor_id = paa.vendor_id
152 AND paa2.vendor_site_id = paa.vendor_site_id
153 AND paa2.item_id = paa.item_id ))
154 or
155 (using_organization_id = x_organization_id))
156 AND paa.vendor_id = NVL(p_vendor_id, paa.vendor_id)
157 AND paa.vendor_site_id = NVL(p_vendor_site_id, paa.vendor_site_id)
158 AND nvl(p_item_id, paa.item_id) = paa.item_id
159 AND (p_category_set_id is null
160 OR
161 paa.item_id in (
162 select mic.inventory_item_id
163 from mtl_item_categories mic
164 where mic.category_set_id = p_category_set_id
165 and mic.organization_id = x_organization_id
166 and nvl(p_category_id,mic.category_id) = mic.category_id))
167 AND nvl(paa.enable_autoschedule_flag,'N') = 'N'
168 AND ((p_schedule_type = 'PLAN_SCHEDULE'
169 AND paa.enable_plan_schedule_flag = 'Y')
170 OR
171 (p_schedule_type = 'SHIP_SCHEDULE'
172 AND paa.enable_ship_schedule_flag = 'Y'))
173 AND nvl(paa.scheduler_id,-1) =
174 NVL(p_scheduler_id, nvl(paa.scheduler_id,-1))
175 AND (p_planner_code IS NULL
176 OR
177 EXISTS (SELECT 'check if planner exists in mtl_system_items'
178 FROM mtl_system_items msi,mtl_planners mtp
179 WHERE msi.planner_code = p_planner_code
180 AND msi.organization_id = x_organization_id
181 AND mtp.organization_id = x_organization_id
182 AND mtp.planner_code = p_planner_code
183 AND msi.inventory_item_id = paa.item_id))
184 AND (p_buyer_id IS NULL
185 OR
186 EXISTS (SELECT 'check if buyer exists in mtl_system_items'
187 FROM mtl_system_items msi
188 WHERE msi.inventory_item_id = paa.item_id
189 AND msi.organization_id = x_organization_id
190 AND msi.buyer_id = p_buyer_id));
191
192 -- This statement will be run by AutoSchedule. The bucket pattern
193 -- and schedule subtype will always be used from the ASL.
194 CURSOR C_SINGLE_ORG_WB_BP_NOTPROV IS
195 SELECT DISTINCT(paa.vendor_id||paa.vendor_site_id||decode(p_schedule_type,
196 'PLAN_SCHEDULE',paa.plan_bucket_pattern_id,
197 paa.ship_bucket_pattern_id)||decode(p_schedule_type,
198 'PLAN_SCHEDULE',paa.plan_schedule_type,
199 paa.ship_schedule_type)),
200 decode(p_schedule_type,'PLAN_SCHEDULE',paa.plan_bucket_pattern_id,
201 paa.ship_bucket_pattern_id),
202 paa.vendor_id,
203 paa.vendor_site_id,
204 decode(p_schedule_type, 'PLAN_SCHEDULE', paa.plan_schedule_type,
205 paa.ship_schedule_type)
206 FROM po_asl_attributes_val_v paa,
207 chv_bucket_patterns cbp,
208 po_vendor_sites_all povs
209 WHERE ((paa.using_organization_id = -1 and not exists
210 (SELECT *
211 FROM po_asl_attributes_val_v paa2
212 WHERE paa2.using_organization_id = x_organization_id
213 AND paa2.vendor_id = paa.vendor_id
214 AND paa2.vendor_site_id = paa.vendor_site_id
215 AND paa2.item_id = paa.item_id ))
216 or
217 (using_organization_id = x_organization_id))
218 AND paa.vendor_id = NVL(p_vendor_id, paa.vendor_id)
219 AND paa.vendor_site_id = NVL(p_vendor_site_id, paa.vendor_site_id)
220 AND nvl(p_item_id, paa.item_id) = paa.item_id
221 /* Bug 2616988 fixed. added the below three statements so that only those
222 data pertaining to current operating unit will be picked up.
223 */
224 AND povs.vendor_site_id = paa.vendor_site_id
225 AND povs.vendor_id = paa.vendor_id
226 AND povs.org_id = x_org_id
227 AND (p_category_set_id is null
228 OR
229 paa.item_id in (
230 select mic.inventory_item_id
231 from mtl_item_categories mic
232 where mic.category_set_id = p_category_set_id
233 and mic.organization_id = x_organization_id
234 and nvl(p_category_id,mic.category_id) = mic.category_id))
235 AND nvl(paa.enable_autoschedule_flag, 'N') = 'Y'
236 AND ((p_schedule_type = 'PLAN_SCHEDULE'
237 AND paa.enable_plan_schedule_flag = 'Y')
238 OR
239 (p_schedule_type = 'SHIP_SCHEDULE'
243 AND nvl(cbp.inactive_date, sysdate) < sysdate + 1)
240 AND paa.enable_ship_schedule_flag = 'Y'))
241 AND ((p_schedule_type = 'PLAN_SCHEDULE'
242 AND paa.plan_bucket_pattern_id = cbp.bucket_pattern_id
244 OR
245 (p_schedule_type = 'SHIP_SCHEDULE'
246 AND paa.ship_bucket_pattern_id = cbp.bucket_pattern_id
247 AND nvl(cbp.inactive_date, sysdate) < sysdate + 1))
248 AND nvl(paa.scheduler_id,-1) =
249 NVL(p_scheduler_id, nvl(paa.scheduler_id,-1))
250 AND (p_planner_code IS NULL
251 OR
252 EXISTS (SELECT 'check if planner exists in mtl_system_items'
253 FROM mtl_system_items msi,mtl_planners mtp
254 WHERE msi.planner_code = p_planner_code
255 AND msi.inventory_item_id = paa.item_id
256 AND mtp.organization_id = x_organization_id
257 AND mtp.planner_code = p_planner_code
258 AND msi.organization_id = x_organization_id))
259 AND (p_buyer_id IS NULL
260 OR
261 EXISTS (SELECT 'check if buyer exists in mtl_system_items'
262 FROM mtl_system_items msi
263 WHERE msi.inventory_item_id = paa.item_id
264 AND msi.organization_id = x_organization_id
265 AND msi.buyer_id = p_buyer_id));
266
267 -- These cursors will be used only if Autoschedule fails and we need to find the reason
268 -- why it failed.
269
270 CURSOR C_CHECK_ORG IS
271 SELECT paa.vendor_id,
272 paa.vendor_site_id
273 FROM po_asl_attributes_val_v paa
274 WHERE ((paa.using_organization_id = -1 and not exists
275 (SELECT *
276 FROM po_asl_attributes_val_v paa2
277 WHERE paa2.using_organization_id = x_organization_id
278 AND paa2.vendor_id = paa.vendor_id
279 AND paa2.vendor_site_id = paa.vendor_site_id
280 AND paa2.item_id = paa.item_id ))
281 or
282 (using_organization_id = x_organization_id));
283
284 CURSOR C_CHECK_V_VS IS
285 SELECT paa.vendor_id,
286 paa.vendor_site_id
287 FROM po_asl_attributes_val_v paa
288 WHERE ((paa.using_organization_id = -1 and not exists
289 (SELECT *
290 FROM po_asl_attributes_val_v paa2
291 WHERE paa2.using_organization_id = x_organization_id
292 AND paa2.vendor_id = paa.vendor_id
293 AND paa2.vendor_site_id = paa.vendor_site_id
294 AND paa2.item_id = paa.item_id ))
295 or
296 (using_organization_id = x_organization_id))
297 AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
298 AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id);
299
300 CURSOR C_CHECK_V_VS_ITEM IS
301 SELECT paa.vendor_id,
302 paa.vendor_site_id
303 FROM po_asl_attributes_val_v paa
304 WHERE ((paa.using_organization_id = -1 and not exists
305 (SELECT *
306 FROM po_asl_attributes_val_v paa2
307 WHERE paa2.using_organization_id = x_organization_id
308 AND paa2.vendor_id = paa.vendor_id
309 AND paa2.vendor_site_id = paa.vendor_site_id
310 AND paa2.item_id = paa.item_id ))
311 or
312 (using_organization_id = x_organization_id))
313 AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
314 AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id)
315 AND paa.item_id = NVL(p_item_id,paa.item_id);
316
317
318 CURSOR C_CHECK_V_VS_AS_FLAG IS
319 SELECT paa.vendor_id,
320 paa.vendor_site_id
321 FROM po_asl_attributes_val_v paa
322 WHERE ((paa.using_organization_id = -1 and not exists
323 (SELECT *
324 FROM po_asl_attributes_val_v paa2
325 WHERE paa2.using_organization_id = x_organization_id
326 AND paa2.vendor_id = paa.vendor_id
327 AND paa2.vendor_site_id = paa.vendor_site_id
328 AND paa2.item_id = paa.item_id ))
329 or
330 (using_organization_id = x_organization_id))
331 AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
332 AND paa.vendor_site_id = NVL(p_vendor_site_id, paa.vendor_site_id)
333 AND paa.item_id = NVL(p_item_id,paa.item_id)
334 AND ((p_schedule_type = 'PLAN_SCHEDULE'
335 AND paa.enable_plan_schedule_flag = 'Y')
336 OR
337 (p_schedule_type = 'SHIP_SCHEDULE'
338 AND paa.enable_ship_schedule_flag = 'Y'))
339 AND paa.enable_autoschedule_flag = 'Y';
340
341 CURSOR C_CHECK_V_VS_ST_FLAG IS
342 SELECT paa.vendor_id,
343 paa.vendor_site_id
344 FROM po_asl_attributes_val_v paa
345 WHERE ((paa.using_organization_id = -1 and not exists
346 (SELECT *
347 FROM po_asl_attributes_val_v paa2
348 WHERE paa2.using_organization_id = x_organization_id
349 AND paa2.vendor_id = paa.vendor_id
350 AND paa2.vendor_site_id = paa.vendor_site_id
351 AND paa2.item_id = paa.item_id ))
352 or
353 (using_organization_id = x_organization_id))
354 AND paa.vendor_id = NVL(p_vendor_id, paa.vendor_id)
355 AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id)
356 AND paa.item_id = NVL(p_item_id,paa.item_id)
357 AND ((p_schedule_type = 'PLAN_SCHEDULE'
358 AND paa.enable_plan_schedule_flag = 'Y')
359 OR
360 (p_schedule_type = 'SHIP_SCHEDULE'
361 AND paa.enable_ship_schedule_flag = 'Y'));
362
363 CURSOR C_CHECK_BP IS
364 SELECT paa.vendor_id,
368 WHERE ((paa.using_organization_id = -1 and not exists
365 paa.vendor_site_id
366 FROM po_asl_attributes_val_v paa,
367 chv_bucket_patterns cbp
369 (SELECT *
370 FROM po_asl_attributes_val_v paa2
371 WHERE paa2.using_organization_id = x_organization_id
372 AND paa2.vendor_id = paa.vendor_id
373 AND paa2.vendor_site_id = paa.vendor_site_id
374 AND paa2.item_id = paa.item_id ))
375 or
376 (using_organization_id = x_organization_id))
377 AND paa.vendor_id = NVL(p_vendor_id,paa.vendor_id)
378 AND paa.vendor_site_id = NVL(p_vendor_site_id,paa.vendor_site_id)
379 AND paa.item_id = NVL(p_item_id,paa.item_id)
380 AND paa.enable_autoschedule_flag = 'Y'
381 AND ((p_schedule_type = 'PLAN_SCHEDULE'
382 AND paa.enable_plan_schedule_flag = 'Y')
383 OR
384 (p_schedule_type = 'SHIP_SCHEDULE'
385 AND paa.enable_ship_schedule_flag = 'Y'))
386 AND ((p_schedule_type = 'PLAN_SCHEDULE'
387 AND paa.plan_bucket_pattern_id = cbp.bucket_pattern_id
388 AND nvl(cbp.inactive_date, sysdate) < sysdate + 1)
389 OR
390 (p_schedule_type = 'SHIP_SCHEDULE'
391 AND paa.ship_bucket_pattern_id = cbp.bucket_pattern_id
392 AND nvl(cbp.inactive_date, sysdate) < sysdate + 1));
393
394 BEGIN
395
396 ---- dbms_output.put_line('Entering build_schedule');
397
398 -- Get x_user_id and x_login_id from the global variable set.
399 x_user_id := NVL(fnd_global.user_id, 0);
400 x_login_id := NVL(fnd_global.login_id, 0);
401
402 /* Bug 2616988 fixed. added below sql statement to get current
403 operating unit id.
404 */
405
406 x_org_id := PO_MOAC_UTILS_PVT.get_current_org_id; -- <R12 MOAC>
407
408 IF p_multi_org_flag = 'N' OR p_multi_org_flag is NULL THEN
409 OPEN C_ORGS;
410 ELSE
411 OPEN C_NOORGS;
412 END IF;
413
414 LOOP
415
416 IF p_multi_org_flag = 'N' OR p_multi_org_flag is NULL THEN
417 -- For each of the organizations, find the asl attributes record
418 -- that matches.
419 FETCH C_ORGS
420 INTO x_organization_id,
421 x_mrp_compile_designator,
422 x_mps_schedule_designator,
423 x_drp_compile_designator;
424 EXIT WHEN C_ORGS%NOTFOUND;
425
426 ELSE
427
428 x_organization_id := p_ship_to_organization_id;
429
430 -- This will only find one record.
431 FETCH C_NOORGS
432 INTO x_dummy,
433 x_mrp_compile_designator,
434 x_mps_schedule_designator,
435 x_drp_compile_designator;
436 EXIT WHEN C_NOORGS%NOTFOUND;
437
438 END IF;
439
440 ---- dbms_output.put_line('after organizations cursor'||x_organization_id);
441
442 x_progress := '010';
443 -- This cursor is executed from WB
444 IF NVL(p_autoschedule_flag,'N')='N' THEN
445 ---- dbms_output.put_line('Build Schedule: Before open WB - Single Org');
446 OPEN C_SINGLE_ORG_WB_BPM;
447
448 -- This cursor is executed from AutoSchedule
449 ELSIF NVL(p_autoschedule_flag,'N')='Y' THEN
450 ---- dbms_output.put_line('Build Schedule: Before open Auto - Single Org');
451 OPEN C_SINGLE_ORG_WB_BP_NOTPROV;
452
453 END IF; -- end of open cursor
454
455 LOOP
456 ---- dbms_output.put_line('Build_schedule: fetching cursor');
457
458 -- Fetch one row at a time from the appropriate cursor opened above
459 -- Exit the loop at the last row.
460 x_progress := '020';
461 IF NVL(p_autoschedule_flag,'N')='N' THEN
462
463 ---- dbms_output.put_line('sched type'||p_schedule_type);
464 ---- dbms_output.put_line('sched sub type'||p_schedule_subtype);
465 ---- dbms_output.put_line('vendor'||p_vendor_id);
466 ---- dbms_output.put_line('vendor site'||p_vendor_site_id);
467 ---- dbms_output.put_line('bucket pattern'||p_bucket_pattern_id);
468 ---- dbms_output.put_line('org'||x_organization_id);
469 ---- dbms_output.put_line('buyer'||p_buyer_id);
470 ---- dbms_output.put_line('planner'||p_planner_code);
471 ---- dbms_output.put_line('scheduler'||p_scheduler_id);
472
473 FETCH C_SINGLE_ORG_WB_BPM
474 INTO x_distinct_v_vs_org_id,
475 x_bucket_pattern_id,
476 x_vendor_id,
477 x_vendor_site_id,
478 x_schedule_subtype;
479 EXIT WHEN C_SINGLE_ORG_WB_BPM%NOTFOUND;
480
481 ELSIF NVL(p_autoschedule_flag,'N')='Y' THEN
482
483 ---- dbms_output.put_line('Single Org - AutoSchedule');
484 ---- dbms_output.put_line('sched type'||p_schedule_type);
485 ---- dbms_output.put_line('sched sub type'||p_schedule_subtype);
486 ---- dbms_output.put_line('vendor'||p_vendor_id);
487 ---- dbms_output.put_line('vendor site'||p_vendor_site_id);
488 ---- dbms_output.put_line('bucket pattern'||p_bucket_pattern_id);
489 ---- dbms_output.put_line('org'||x_organization_id);
490 ---- dbms_output.put_line('buyer'||p_buyer_id);
491 ---- dbms_output.put_line('planner'||p_planner_code);
492 ---- dbms_output.put_line('scheduler'||p_scheduler_id);
493
494 FETCH C_SINGLE_ORG_WB_BP_NOTPROV
495 INTO x_distinct_v_vs_org_id,
496 x_bucket_pattern_id,
497 x_vendor_id,
498 x_vendor_site_id,
499 x_schedule_subtype;
500 EXIT WHEN C_SINGLE_ORG_WB_BP_NOTPROV%NOTFOUND;
501
502 END IF; -- end of fetch cursor
503
504 ---- dbms_output.put_line('after asl header fetch'||x_vendor_id);
505
506 -- If schedule category is not INQUIRY (NEW or REVISION),
510 x_progress := '030';
507 -- then get x_schedule_num and x_schedule_revision.
508 -- Exception handler within get_schedule_number should take care of any
509 -- error and failure in calculating the number and revision.
511 IF (p_schedule_category <> 'SIMULATION') THEN
512 ---- dbms_output.put_line('Build_schedule: get_schedule_number');
513
514 x_schedule_num := p_schedule_num;
515 get_schedule_number(p_schedule_category,
516 x_vendor_id,
517 x_vendor_site_id,
518 x_schedule_num,
519 x_schedule_revision);
520
521 ---- dbms_output.put_line('Schedule Number = '||x_schedule_num);
522 ---- dbms_output.put_line('Schedule Revision = '||x_schedule_revision);
523
524 END IF;
525
526 -- Create 3 temp bucket tables (descriptor, start_date and end_date)
527 -- and get x_horizon_end_date in the meantime.
528 ---- dbms_output.put_line('Build_schedule: create_bucket_template');
529
530 x_progress := '040';
531
532 chv_create_buckets.create_bucket_template(p_horizon_start_date,
533 p_include_future_releases,
534 x_bucket_pattern_id,
535 x_horizon_end_date,
536 x_bucket_descriptor_table,
537 x_bucket_start_date_table,
538 x_bucket_end_date_table);
539
540 ---- dbms_output.put_line('Build Schedules: end Date'||to_char(x_horizon_end_date,'DD-MON-YYYY'));
541
542 -- Insert a new row into CHV_SCHEDULE_HEADERS.
543 -- Before that, get a new unique schedule header ID.
544 -- Any error will be caught at the exception at the end of the procedure.
545 x_progress := '050';
546 SELECT chv_schedule_headers_s.NEXTVAL
547 INTO x_schedule_id
548 FROM DUAL;
549
550 ---- dbms_output.put_line('Schedule Header id'||x_schedule_id);
551
552 ---- dbms_output.put_line('Build_schedule: insert into chv_schedule_headers');
553
554 ---- dbms_output.put_line('Build_schedule: schedule id'||x_schedule_id);
555 ---- dbms_output.put_line('Build_schedule: vendor id'||x_vendor_id);
556 ---- dbms_output.put_line('Build_schedule: site id'||x_vendor_site_id);
557 ---- dbms_output.put_line('Build_schedule: batch id'||p_batch_id);
558 ---- dbms_output.put_line('Build_schedule: schedule type'||p_schedule_type);
559 ---- dbms_output.put_line('Build_schedule: subtype'||x_schedule_subtype);
560 ---- dbms_output.put_line('Build_schedule: start'||p_horizon_start_date);
561 ---- dbms_output.put_line('Build_schedule: end'||x_horizon_end_date);
562 ---- dbms_output.put_line('Build_schedule: bucket id'||x_bucket_pattern_id);
563 ---- dbms_output.put_line('Build_schedule: owner id'||p_owner_id);
564 ---- dbms_output.put_line('Build_schedule: user id'||x_user_id);
565 ---- dbms_output.put_line('Build_schedule: login id'||x_login_id);
566
567 x_progress := '060';
568 INSERT INTO chv_schedule_headers(schedule_id,
569 vendor_id,
570 vendor_site_id,
571 schedule_type,
572 schedule_subtype,
573 schedule_num,
574 schedule_revision,
575 schedule_horizon_start,
576 schedule_horizon_end,
577 bucket_pattern_id,
578 schedule_owner_id,
579 last_update_date,
580 last_updated_by,
581 creation_date,
582 created_by,
583 organization_id,
584 mps_schedule_designator,
585 mrp_compile_designator,
586 drp_compile_designator,
587 schedule_status,
588 inquiry_flag,
589 include_future_releases_flag,
590 last_update_login,
591 batch_id)
592 VALUES (x_schedule_id,
593 x_vendor_id,
594 x_vendor_site_id,
595 p_schedule_type,
596 x_schedule_subtype,
597 x_schedule_num,
598 x_schedule_revision,
599 p_horizon_start_date,
600 x_horizon_end_date,
601 x_bucket_pattern_id,
602 p_owner_id,
603 SYSDATE, -- last_update_date
604 x_user_id, -- last_updated_by
605 SYSDATE, -- creation_date
606 x_user_id, -- created_by
607 DECODE(p_multi_org_flag, 'N',
608 x_organization_id, '',
609 x_organization_id, ''),
610 x_mps_schedule_designator,
611 x_mrp_compile_designator,
612 x_drp_compile_designator,
613 'IN_PROCESS', -- schedule_status
614 DECODE(p_schedule_category, 'SIMULATION',
615 'Y', 'N'), -- inquiry_flag
619
616 p_include_future_releases,
617 x_login_id, -- last_update_login
618 p_batch_id);
620 -- Create schedule items for this new schedule.
621 ---- dbms_output.put_line('Build_schedule: create_items');
622
623 x_progress := '070';
624 chv_build_schedules.create_items(p_schedule_category,
625 p_autoschedule_flag,
626 p_schedule_type,
627 x_schedule_subtype,
628 x_schedule_id,
629 x_schedule_num,
630 x_schedule_revision,
631 p_horizon_start_date,
632 x_bucket_pattern_id,
633 p_include_future_releases,
634 x_mrp_compile_designator,
635 x_mps_schedule_designator,
636 x_drp_compile_designator,
637 x_organization_id,
638 p_multi_org_flag,
639 x_vendor_id,
640 x_vendor_site_id,
641 p_category_set_id,
642 p_category_id,
643 p_item_id,
644 p_scheduler_id,
645 p_buyer_id,
646 p_planner_code,
647 x_user_id,
648 x_login_id,
649 x_horizon_end_date,
650 x_bucket_descriptor_table,
651 x_bucket_start_date_table,
652 x_bucket_end_date_table,
653 x_item_created,
654 x_old_schedule_id,
655 p_bucket_pattern_id, -- used to determine if the
656 -- we must match the bucket pattern
657 -- in the asl record or not in
658 -- items cursor
659 p_schedule_subtype, -- used to determine if the
660 -- we must match the schedule subtype
661 -- in the asl record or not in
662 -- items cursor
663 p_batch_id);
664
665 -- If we did not find an item to create for the schedule header,
666 -- we need to delete the schedule header. We should never
667 -- create a schedule header without any scheduled items.
668 IF (x_item_created = 'N') THEN
669
670 x_progress := '075';
671
672 ---- dbms_output.put_line('deleteing the schedule header');
673
674 DELETE from chv_schedule_headers
675 where schedule_id = x_schedule_id;
676
677 ELSE
678
679 -- Confirm schedule if it is autoschedule and autoconfirm is set to 'Y'.
680 -- An inquiry schedule cannot be confirmed.
681 x_progress := '080';
682 IF p_autoschedule_flag = 'Y' AND p_autoconfirm_flag = 'Y' and
683 p_schedule_category <> 'SIMULATION' THEN
684 ---- dbms_output.put_line('Build_schedule: confirm_schedules');
685
686 chv_confirm_schedules.confirm_schedule_header(
687 x_schedule_id,
688 p_schedule_type,
689 p_communication_code,
690 x_confirm_source,
691 x_confirmed_schedule);
692
693 END IF;
694
695
696 -- If the schedule is confirmed, we should check the method for
697 -- communicating it to the supplier.
698 IF (x_confirmed_schedule = 'Y' AND
699 p_autoschedule_flag = 'Y' AND
700 (p_communication_code = 'BOTH' or
701 p_communication_code = 'EDI')) THEN
702
703 x_progress := '090';
704
705 -- Select the transmission method for the supplier and site from
706 -- the edi tables.
707 -- direction: O - outbound; I - inbound
708 BEGIN
709
710 SELECT edi_flag
711 INTO x_transmission_method
712 FROM ece_tp_headers eth,
713 ece_tp_details etd,
714 po_vendor_sites pvs,
715 chv_schedule_headers csh
716 WHERE eth.tp_header_id = etd.tp_header_id
717 AND decode(csh.schedule_type, 'PLAN_SCHEDULE', 'SPSO', 'SSSO') =
718 etd.document_id
719 AND eth.tp_header_id = pvs.tp_header_id
720 AND csh.vendor_site_id = pvs.vendor_site_id
721 AND csh.schedule_id = x_schedule_id;
722
723 /*
724 SELECT transmission_method
725 INTO x_transmission_method
726 FROM ECE_CONTROL ECC,
727 CHV_SCHEDULE_HEADERS CSH
728 WHERE CSH.schedule_id = x_schedule_id
729 AND decode(CSH.schedule_type, 'PLAN_SCHEDULE', 'SPSO', 'SSSO')
730 = ECC.DOCUMENT_TYPE
731 AND CSH.vendor_id = ECC.entity_id
732 AND CSH.vendor_site_id = ECC.entity_site_id
733 AND ECC.entity_type = 'SUPPLIER'
734 AND ECC.direction = 'O';
735
736 */
737
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 -- If the supplier is not setup for the edi transaction
741 -- and they want to print and edi it both, set the
742 -- communication code so they can just print it.
743 null;
744
745 WHEN OTHERS THEN raise;
746
747 END;
748
749 END IF; -- IF confirm = y
750
751 x_progress := '100';
752
753
754 -- You can print unconfirmed schedules, but you cannot send
755 -- unconfirmed schedules via edi.
756 -- For each header we will update the communication method.
757 -- At the end of the program we will call the batch job
758 -- to print/send via edi all the progrmas that indicate
759 -- that they should be sent.
760 IF (x_transmission_method = 'Y' AND
761 p_communication_code = 'EDI') THEN
762
763 UPDATE chv_schedule_headers
764 SET communication_code = 'EDI'
768
765 WHERE schedule_id = x_schedule_id;
766
767 x_edi_set := 'Y';
769 ELSIF (x_transmission_method = 'Y' AND
770 p_communication_code = 'BOTH' ) THEN
771
772 UPDATE chv_schedule_headers
773 SET communication_code = 'BOTH'
774 WHERE schedule_id = x_schedule_id;
775
776 x_edi_set := 'Y';
777
778 ELSIF ((x_transmission_method <> 'Y' AND
779 p_communication_code = 'BOTH') OR
780 (p_communication_code = 'PRINT')) THEN
781
782 UPDATE chv_schedule_headers
783 SET communication_code = 'PRINT'
784 WHERE schedule_id = x_schedule_id;
785
786 END IF;
787
788 END IF; -- If item_created = N
789 /* Bug 2775001 fixed. reinitialized the variable x_item_created to 'N'
790 for the next record fetched to create headers and corresponding
791 chv items.
792 */
793 x_item_created := 'N' ;
794 END LOOP; -- end loop of asl fetching cursor
795
796 ---- dbms_output.put_line('Finding reason of autoschedule failure');
797 -- Find the reason why the autoschedule did not build
798 IF NVL(P_autoschedule_flag,'N') = 'Y' THEN
799 IF (C_SINGLE_ORG_WB_BP_NOTPROV%ROWCOUNT = 0 ) THEN
800 fnd_message.set_name('CHV','CHV_AUTOSCHEDULE_FAILED');
801 x_str := fnd_message.get;
802 ---- dbms_output.put_line(x_str);
803 x_str := null;
804 IF p_planner_code IS NOT NULL THEN
805 BEGIN
806 SELECT 'Y' INTO x_dummy
807 FROM mtl_planners mpl
808 WHERE mpl.planner_code = p_planner_code
809 AND mpl.organization_id = x_organization_id
810 AND nvl(mpl.disable_date,sysdate +1 ) > sysdate;
811 EXCEPTION
812 WHEN NO_DATA_FOUND THEN
813 fnd_message.set_name('CHV','CHV_PLANNER_NOT_ACTIVE');
814 x_str := fnd_message.get;
815 ---- dbms_output.put_line(x_str);
816 x_str := null;
817 END;
818 END IF;
819
820 IF p_buyer_id IS NOT NULL THEN
821 BEGIN
822 SELECT 'Y' INTO x_dummy
823 FROM mtl_system_items msi
824 WHERE msi.inventory_item_id = p_item_id
825 AND msi.organization_id = x_organization_id
826 AND msi.buyer_id = p_buyer_id;
827 EXCEPTION
828 WHEN NO_DATA_FOUND THEN
829 fnd_message.set_name('CHV','CHV_BUYER_NOT_ACTIVE');
830 x_str := fnd_message.get;
831 ---- dbms_output.put_line(x_str);
832 x_str := null;
833 END;
834 END IF;
835
836 SELECT organization_name INTO x_organization_name from org_organization_definitions
837 where organization_id = x_organization_id;
838
839 IF p_vendor_id is not null THEN
840 SELECT vendor_name into x_vendor_name from po_vendors where p_vendor_id = vendor_id;
841 ELSE
842 x_vendor_name := 'No Vendor Provided';
843 END IF;
844
845 IF p_vendor_site_id is not null THEN
846 SELECT vendor_site_code into x_vendor_code from po_vendor_sites where p_vendor_site_id = vendor_site_id and p_vendor_id = vendor_id;
847 ELSE
848 x_vendor_code := 'No Vendor Site';
849 END IF;
850
851 IF p_item_id is not null THEN
852 SELECT description into x_item_desc from mtl_system_items where p_item_id = inventory_item_id and organization_id = x_organization_id;
853 ELSE
854 x_item_desc := 'No Item Provided ';
855 END IF;
856
857 -- Check if ASL set for ORG
858
859 OPEN C_CHECK_ORG;
860 LOOP
861 FETCH C_CHECK_ORG INTO
862 x_dummy_num1,
863 x_dummy_num2;
864 EXIT WHEN (C_CHECK_ORG%NOTFOUND OR C_CHECK_ORG%ROWCOUNT > 1);
865 END LOOP;
866 IF C_CHECK_ORG%ROWCOUNT = 0 THEN
867 fnd_message.set_name('CHV','CHV_NO_ASL_FOR_ORG');
868 FND_MESSAGE.SET_TOKEN('ORG',x_organization_name);
869 x_str := fnd_message.get;
870 ---- dbms_output.put_line(x_str);
871 x_str := null;
872 END IF;
873 CLOSE C_CHECK_ORG;
874
875 -- Check if ASL set for Vendor/Vendor Site
876
877 OPEN C_CHECK_V_VS;
878 LOOP
879 FETCH C_CHECK_V_VS INTO
880 x_dummy_num1,
881 x_dummy_num2;
882 EXIT WHEN (C_CHECK_V_VS%NOTFOUND OR C_CHECK_V_VS%ROWCOUNT > 1);
883 END LOOP;
884 IF C_CHECK_V_VS%ROWCOUNT = 0 THEN
885 fnd_message.set_name('CHV','CHV_NO_ASL_FOR_SUPPLIER');
886 FND_MESSAGE.SET_TOKEN('VENDOR',x_vendor_name);
887 FND_MESSAGE.SET_TOKEN('VENDORSITE',x_vendor_code);
888 x_str := fnd_message.get;
889 ---- dbms_output.put_line(x_str);
890 END IF;
891 CLOSE C_CHECK_V_VS;
892
893 -- Check if Vendor Vendor Site Item has ASL entry
894
895 OPEN C_CHECK_V_VS_ITEM;
896 LOOP
897 FETCH C_CHECK_V_VS_ITEM INTO
898 x_dummy_num1,
899 x_dummy_num2;
900 EXIT WHEN (C_CHECK_V_VS_ITEM%NOTFOUND OR C_CHECK_V_VS_ITEM%ROWCOUNT > 1);
901 END LOOP;
902 IF C_CHECK_V_VS_ITEM%ROWCOUNT = 0 THEN
903 fnd_message.set_name('CHV','CHV_NO_ASL_FOR_ITEM');
904 FND_MESSAGE.SET_TOKEN('ITEM',x_item_desc);
905 x_str := fnd_message.get;
909
906 ---- dbms_output.put_line(x_str);
907 END IF;
908 CLOSE C_CHECK_V_VS_ITEM;
910 -- Check if Enable Planning/Shipping Flag is set
911
912 OPEN C_CHECK_V_VS_ST_FLAG;
913 LOOP
914 FETCH C_CHECK_V_VS_ST_FLAG INTO
915 x_dummy_num1,
916 x_dummy_num2;
917 EXIT WHEN (C_CHECK_V_VS_ST_FLAG%NOTFOUND OR C_CHECK_V_VS_ST_FLAG%ROWCOUNT > 1);
918 END LOOP;
919 IF C_CHECK_V_VS_ST_FLAG%ROWCOUNT = 0 THEN
920 IF p_schedule_type = 'PLAN_SCHEDULE' THEN
921 fnd_message.set_name('CHV','CHV_NO_PLANNING_FLAG');
922 FND_MESSAGE.SET_TOKEN('ITEM',x_item_desc);
923 x_str := fnd_message.get;
924 ---- dbms_output.put_line(x_str);
925 ELSE
926 fnd_message.set_name('CHV','CHV_NO_SHIPPING_FLAG');
927 FND_MESSAGE.SET_TOKEN('ITEM',x_item_desc);
928 x_str := fnd_message.get;
929 ---- dbms_output.put_line(x_str);
930 END IF;
931 END IF;
932 CLOSE C_CHECK_V_VS_ST_FLAG;
933
934 -- Check if Autoschedule Flag is Set
935
936 OPEN C_CHECK_V_VS_AS_FLAG;
937 LOOP
938 FETCH C_CHECK_V_VS_AS_FLAG INTO
939 x_dummy_num1,
940 x_dummy_num2;
941 EXIT WHEN (C_CHECK_V_VS_AS_FLAG%NOTFOUND OR C_CHECK_V_VS_AS_FLAG%ROWCOUNT > 1);
942 END LOOP;
943 IF C_CHECK_V_VS_AS_FLAG%ROWCOUNT = 0 THEN
944 fnd_message.set_name('CHV','CHV_AUTOSCHED_FLAG_NOT_SET');
945 FND_MESSAGE.SET_TOKEN('ITEM',x_item_desc);
946 x_str := fnd_message.get;
947 ---- dbms_output.put_line(x_str);
948 ELSE
949 -- Check if Bucket Pattern Active
950
951 OPEN C_CHECK_BP;
952 LOOP
953 FETCH C_CHECK_BP INTO
954 x_dummy_num1,
955 x_dummy_num2;
956 EXIT WHEN (C_CHECK_BP%NOTFOUND OR C_CHECK_BP%ROWCOUNT > 1);
957 END LOOP;
958 IF C_CHECK_BP%ROWCOUNT = 0 THEN
959 fnd_message.set_name('CHV','CHV_BUCKET_PATTERN_DISABLED');
960 FND_MESSAGE.SET_TOKEN('ITEM',x_item_desc);
961 x_str := fnd_message.get;
962 ---- dbms_output.put_line(x_str);
963 END IF;
964 CLOSE C_CHECK_BP;
965 END IF;
966 CLOSE C_CHECK_V_VS_AS_FLAG;
967
968 END IF;
969 END IF;
970
971 IF NVL(p_autoschedule_flag,'N')='N' THEN
972 CLOSE C_SINGLE_ORG_WB_BPM;
973
974 ELSIF NVL(p_autoschedule_flag,'N')='Y' THEN
975 CLOSE C_SINGLE_ORG_WB_BP_NOTPROV;
976
977 END IF;
978
979 END LOOP; -- end of orgs fetching cursor
980
981 IF (p_communication_code = 'BOTH' or
982 p_communication_code = 'PRINT')
983 THEN
984 /* 5075549 fixed. set the org context */
985 fnd_request.set_org_id(x_org_id);
986 IF p_schedule_type = 'PLAN_SCHEDULE' THEN
987
988 ---- dbms_output.put_line('try to print planning schedule');
989 x_return_number := FND_REQUEST.submit_request('PO',
990 'CHVPRSCH',
991 null,
992 null,
993 false,
994 NULL, -- schedule_num
995 NULL, -- schedule_rev
996 'PLAN_SCHEDULE', -- schedule_type
997 NULL, -- schedule sub type
998 NULL, -- horizon start
999 NULL, -- horizon end
1000 NULL, -- vendor name from
1001 NULL, -- vendor name to
1002 NULL, -- vendor site
1003 NULL, -- test print
1004 NULL, -- organization
1005 NULL, -- qyt precision
1006 p_autoschedule_flag, -- autoschedule flag
1007 p_batch_id, -- batch id
1008 p_exclude_zero_quantity_lines, -- exclude zero quantity lines
1009 NULL,
1010 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1011 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1012 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1013 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1014 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1015 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1016 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1017 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1018 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1019 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1020 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1021 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1022
1023 ---- dbms_output.put_line('request_id'||x_return_number);
1024
1025 ELSE /* Submit the Shipping Schedule */
1026
1027 ---- dbms_output.put_line('try to print shipping schedule');
1028 x_return_number := FND_REQUEST.submit_request('PO',
1029 'CHVSHSCH',
1030 null,
1031 null,
1032 false,
1033 NULL, -- schedule_num
1034 NULL, -- schedule_rev
1035 'SHIP_SCHEDULE', -- schedule_type
1036 NULL, -- schedule sub type
1037 NULL, -- horizon start
1038 NULL, -- horizon end
1039 NULL, -- vendor name from
1040 NULL, -- vendor name to
1041 NULL, -- vendor site
1042 NULL, -- test print
1043 NULL, -- organization
1044 NULL, -- qty precision
1045 p_autoschedule_flag, -- autoschedule flag
1046 p_batch_id, -- batch id
1050 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1047 p_exclude_zero_quantity_lines, -- exclude zero quantity lines
1048 NULL,
1049 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1051 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1052 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1053 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1054 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1055 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1056 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1057 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1058 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1059 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1060 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1061
1062 ---- dbms_output.put_line('request_id'||x_return_number);
1063
1064 END IF;
1065 END IF;
1066
1067 -- Call the edi program to edi all schedule headers that have a
1068 -- communication method of EDI or BOTH.
1069 IF (x_edi_set = 'Y') THEN
1070
1071 ---- dbms_output.put_line('try to send via edi');
1072
1073 fnd_profile.get('ECE_OUT_FILE_PATH',x_ece_path);
1074
1075 IF p_schedule_type = 'PLAN_SCHEDULE' THEN
1076
1077 select 'SPSO' || lpad(substr(to_char(ECE_OUTPUT_RUNS_S.nextval),
1078 decode(length(ECE_OUTPUT_RUNS_S.nextval),1,-1,2, -2,3, -3,-4),
1079 4), 4, '0') || '.dat'
1080 into x_ece_file
1081 from dual;
1082
1083 select x_ece_path || x_ece_file
1084 into x_ece_path_file
1085 from dual;
1086
1087 /*Bug 1701675:When lauching ECSPSO/ECSSSO transactions,a extra parameter called
1088 x_ece_path_file is passed to ECSPSO/ECSSSO concurrent program.
1089 This parameter is not in ECSPSO/ECSSSO concurrent program.Hence
1090 removing this parameter from call to the concurrent program
1091 'fnd_request.submit_request'.Also adding a parameter debug_mode to
1092 the call to concurrent program.*/
1093 /* Bug 1955282:Changed the 10th parameter in the call below from
1094 null to chr(0) */
1095
1096 /* Bug 2090899: Passing Batch Id also as one of the parameter as requested
1097 by EDI Team.
1098 */
1099
1100 /* Bug2576335 fixed. Changed the debug mode from '3' to '0' */
1101
1102 x_return_number := FND_REQUEST.submit_request('EC',
1103 'ECSPSO',
1104 null,
1105 null,
1106 false,
1107 x_ece_path, -- file path
1108 x_ece_file, -- file name
1109 --Bug 1701675 x_ece_path_file, -- file path name
1110 0, -- schedule_id
1111 /* 3, --Bug 1701675:Debug Mode Bug2576335.old*/
1112 0, /* Bug2576335.new */
1113 p_batch_id, -- Bug 2090899.
1114 chr(0),-- bug 1955282
1115 NULL,
1116 NULL,
1117 NULL,
1118 NULL,
1119 NULL,
1120 NULL,
1121 NULL,
1122 NULL, NULL, NULL,
1123 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1124 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1125 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1126 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1127 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1128 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1129 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1130 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1131 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1132 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1133 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1134 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1135
1136 ELSE
1137
1138 select 'SSSO' || lpad(substr(to_char(ECE_OUTPUT_RUNS_S.nextval),
1139 decode(length(ECE_OUTPUT_RUNS_S.nextval),1,-1,2, -2,3, -3,-4),
1140 4), 4, '0') || '.dat'
1141 into x_ece_file
1142 from dual;
1143
1144 select x_ece_path || x_ece_file
1145 into x_ece_path_file
1146 from dual;
1147
1148 /* Bug 2090899: Passing Batch Id also as one of the parameter as requested
1149 by EDI Team.
1150 */
1151
1152 x_return_number := FND_REQUEST.submit_request('EC',
1153 'ECSSSO',
1154 null,
1155 null,
1156 false,
1157 x_ece_path, -- file path
1158 x_ece_file, -- file name
1159 --Bug 1701675 x_ece_path_file, -- file path name
1160 0, -- schedule_id
1161 3, --Bug 1701675:Debug Mode
1162 p_batch_id, -- Bug 2090899.
1163 chr(0), -- bug 1955282
1164 NULL,
1165 NULL,
1166 NULL,
1167 NULL,
1168 NULL,
1169 NULL,
1170 NULL,
1171 NULL, NULL, NULL,
1172 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1173 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1174 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1175 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1176 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1177 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1178 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1179 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1180 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1181 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1182 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1183 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1184 END IF;
1185 END IF;
1186
1187
1188 ---- dbms_output.put_line('Build_schedule: closing cursor');
1189
1190 -- Close the appropriate cursor according to p_multi_org_flag and
1191 -- p_autoschedule_flag.
1192 x_progress := '110';
1193
1197 CLOSE C_NOORGS;
1194 IF p_multi_org_flag = 'N' OR p_multi_org_flag is NULL THEN
1195 CLOSE C_ORGS;
1196 ELSE
1198 END IF;
1199
1200
1201 ---- dbms_output.put_line('Exiting build_schedule');
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 ---- dbms_output.put_line('sqlca'||sqlcode);
1206 po_message_s.sql_error('build_schedule', x_progress, sqlcode);
1207 RAISE;
1208
1209 END build_schedule;
1210
1211
1212 /*=============================================================================
1213
1214 PROCEDURE NAME: get_schedule_number()
1215
1216 =============================================================================*/
1217 PROCEDURE get_schedule_number(p_schedule_category in VARCHAR2,
1218 x_vendor_id in NUMBER,
1219 x_vendor_site_id in NUMBER,
1220 x_schedule_num in out NOCOPY VARCHAR2,
1221 x_schedule_revision out NOCOPY NUMBER) IS
1222
1223 x_progress VARCHAR2(3) := NULL;
1224 x_count_l NUMBER := 0;
1225
1226 BEGIN
1227
1228 /* DEBUG Pri B.It is possible that we are going to get duplicate
1229 schedule numbers in this case. What if we are doing
1230 two schedule builds at the same time? Sent email to
1231 Sri. We need to solve this one. */
1232
1233 -- All schedule numbers must be unique within a vendor/venodr site.
1234 -- Schedule numbers are based on sysdate concatenated with a hyphen and a
1235 -- unique sequence as follows: YYYYMMDD-n. Where (n) is the schedule
1236 -- number being created for the vendor/site on a certain day.
1237 -- Also assign revision. Revisions for a new schedule is always 0.
1238 -- All subsequent revisions are incremented by 1 for the vendor/site.
1239 -- No schedule numbers will be generated for inquiry schedules.
1240
1241 IF p_schedule_category = 'NEW' THEN
1242 ---- dbms_output.put_line('Get_schedule_number: get for NEW schedule');
1243
1244 -- Select the next largest number to be used for the next schedule
1245 -- generated today; Set the revision to 0 for a new schedule.
1246 x_progress := '010';
1247
1248 SELECT chv_schedule_headers_s2.NEXTVAL
1249 INTO x_count_l
1250 FROM DUAL;
1251
1252 x_schedule_num := TO_CHAR(SYSDATE, 'YYYYMMDD') || '-' ||
1253 TO_CHAR(x_count_l);
1254 x_schedule_revision := 0;
1255
1256 ELSIF p_schedule_category = 'REVISION' THEN
1257 ---- dbms_output.put_line('Get_schedule_number: get for REVISION schedule');
1258
1259 -- Select the next largest revision number for a given schedule;
1260 x_progress := '020';
1261 SELECT NVL(MAX(schedule_revision),0) + 1
1262 INTO x_count_l
1263 FROM chv_schedule_headers
1264 WHERE schedule_num = x_schedule_num;
1265
1266 x_schedule_revision := x_count_l;
1267
1268 END IF;
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 po_message_s.sql_error('get_schedule_number', x_progress, sqlcode);
1273 RAISE;
1274
1275 END get_schedule_number;
1276
1277
1278 /*=============================================================================
1279
1280 PROCEDURE NAME: create_items()
1281
1282 =============================================================================*/
1283 PROCEDURE create_items (p_schedule_category in VARCHAR2,
1284 p_autoschedule_flag in VARCHAR2,
1285 p_schedule_type in VARCHAR2,
1286 x_schedule_subtype in VARCHAR2,
1287 x_schedule_id in NUMBER,
1288 x_schedule_num in VARCHAR2,
1289 x_schedule_revision in NUMBER,
1290 p_horizon_start_date in DATE,
1291 x_bucket_pattern_id in NUMBER,
1292 p_include_future_releases in VARCHAR2,
1293 x_mrp_compile_designator in VARCHAR2,
1294 x_mps_schedule_designator in VARCHAR2,
1295 x_drp_compile_designator in VARCHAR2,
1296 x_organization_id_l in NUMBER,
1297 p_multi_org_flag in VARCHAR2,
1298 x_vendor_id in NUMBER,
1299 x_vendor_site_id in NUMBER,
1300 p_category_set_id in NUMBER,
1301 p_category_id in NUMBER,
1302 p_item_id in NUMBER,
1303 p_scheduler_id in NUMBER,
1304 p_buyer_id in NUMBER,
1305 p_planner_code in VARCHAR2,
1306 x_user_id in NUMBER,
1307 x_login_id in NUMBER,
1308 x_horizon_end_date in DATE,
1309 x_bucket_descriptor_table in out NOCOPY chv_create_buckets.BKTTABLE,
1310 x_bucket_start_date_table in out NOCOPY chv_create_buckets.BKTTABLE,
1311 x_bucket_end_date_table in out NOCOPY chv_create_buckets.BKTTABLE,
1312 x_item_created in out NOCOPY VARCHAR2,
1313 x_old_schedule_id in NUMBER,
1314 p_bucket_pattern_id in NUMBER,
1315 p_schedule_subtype in VARCHAR2,
1316 p_batch_id in NUMBER
1317 ) IS
1318
1319 x_progress VARCHAR2(3) := NULL;
1320
1321 /* DEBUG x_schedule_num and x_schedule_revision are not used in this procedure,
1322 does load_item_orders need them? Doesn't look like that,
1323 Should we remove them from the parameter list?
1324 Also, should x_horizon_end_date be passed in from build_schedule?
1325 or should it be recalculated in get_cum_info? */
1326 -- Following variables whose values should be retrieved from cursors.
1327 x_asl_id_l NUMBER;
1331 x_organization_id NUMBER;
1328 x_item_id_l NUMBER;
1329 x_enable_cum_flag_l VARCHAR2(1);
1330 x_enable_authorizations_flag_l VARCHAR2(1);
1332 /* Bug# 2933042 - Increased the size of x_organization_name from 60
1333 to 240 to make it UTF8 compliant */
1334 x_organization_name VARCHAR2(240);
1335 x_temp_org_id NUMBER;
1336
1337 -- Following variables whose values are calculated within the procedure.
1338 x_last_receipt_tranx_id_l NUMBER;
1339 x_cum_quantity_received_l NUMBER;
1340 x_cum_qty_received_primary_l NUMBER;
1341 x_cum_period_end_date_l DATE;
1342 x_starting_auth_quantity_l NUMBER;
1343 x_starting_auth_qty_primary_l NUMBER;
1344 x_item_planning_method_l NUMBER;
1345 x_purch_unit_of_measure_l VARCHAR2(25);
1346 x_primary_unit_of_measure_l VARCHAR2(25);
1347 x_primary_uom_code_l VARCHAR2(3);
1348 x_purchasing_uom_code_l VARCHAR2(3);
1349 x_conversion_rate_l NUMBER;
1350 x_plan_designator_l VARCHAR2(10);
1351 x_po_header_id_l NUMBER;
1352 x_po_line_id_l NUMBER;
1353 x_schedule_item_id_l NUMBER;
1354 x_number_of_blanket_agreements NUMBER := 0;
1355 x_past_due NUMBER;
1356 x_past_due_primary NUMBER;
1357 x_plan_lookup VARCHAR2(25);
1358 x_dummy VARCHAR2(1);
1359 x_dummy_num1 NUMBER;
1360 x_dummy_num2 NUMBER;
1361 x_using_org_id_l NUMBER;
1362 x_str VARCHAR2(480);
1363
1364 -- An item must be MRP,MPS, or DRP planned in a ship-to org to be included
1365 -- in the schedule. Note: We have removed this requirement. If
1366 -- the item is setup in the asl we will let you use it. If we
1367 -- had this requirement we wouuld not be able to pick up items which
1368 -- are min/max planned, etc...
1369 -- An item must be purchaseable in the ship-to-org to be included in the
1370 -- schedule. Note: We have removed this requirement for the same
1371 -- reason listed above.
1372 -- An item must be included in the ASL fro the ship-to org to be included in
1373 -- the schedule
1374 -- An item must be Planning/Shipping Schedule Enabled in the ASL for
1375 -- the corresponding schedule type to be included in the schedule
1376 -- AutoSchedule items are not included in NEW schedule builds in
1377 -- the workbench. It does not matter whether building planning
1378 -- or shipping schedules: If AutoSchedule is 'Yes' in the ASL,
1379 -- the item is excluded.
1380
1381 -- Multi-org schedules will create a new schedule item record for each
1382 -- item/organization combination
1383 -- Multiorg shipping schedules are not allowed
1384 -- If build a schedule for one org, this is specified by the user.
1385 -- If building schedules for multiple orgs, the list of orgs is
1386 -- determined by the specified MRP/MPS/DRP plans (each is potentially
1387 -- associated with multiple organizations). In the wb, the user can
1388 -- optionally delete orgs from the composite list before launching
1389 -- the build process.
1390
1391 -- All the qtys will be calculated in the primary and the
1392 -- purchasing uom. Always recalc CUms using the ASL UOM.
1393 -- Supplier Scheduling requires the item in the ASL
1394
1395 -- DEBUG. Logic for category set
1396
1397 -- Note that only one cursor is needed at the item level for the
1398 -- WorkBench and AutoSchedule. This is because we are not selecting
1399 -- distinct.
1400 -- Note that this does not need a correlated sub-query. The max
1401 -- is done in the outer query. ** this has been changed Bug 454811(vpawar)
1402 CURSOR CI_SINGLE_ORG_WB_BPM IS
1403 SELECT paa.using_organization_id,
1404 paa.asl_id,
1405 paa.item_id,
1406 NVL(paa.enable_authorizations_flag, 'N'),
1407 paa.purchasing_unit_of_measure
1408 FROM po_asl_attributes_val_v paa
1409 WHERE ((paa.using_organization_id = -1 and not exists
1410 (SELECT *
1411 FROM po_asl_attributes_val_v paa2
1412 WHERE paa2.using_organization_id = x_organization_id
1413 AND paa2.vendor_id = paa.vendor_id
1414 AND paa2.vendor_site_id = paa.vendor_site_id
1415 AND paa2.item_id = paa.item_id ))
1416 or
1417 (paa.using_organization_id = x_organization_id))
1418 AND paa.asl_id = paa.asl_id
1419 AND paa.vendor_id = x_vendor_id
1420 AND paa.vendor_site_id = x_vendor_site_id
1421 AND nvl(p_item_id, paa.item_id) = paa.item_id
1422 AND exists (select * from mtl_system_items
1423 where inventory_item_id = paa.item_id
1424 and organization_id = x_organization_id) /* Bug 462403 vpawar */
1425 AND (p_category_set_id is null
1426 OR
1427 paa.item_id in (
1428 select mic.inventory_item_id
1429 from mtl_item_categories mic
1430 where mic.category_set_id = p_category_set_id
1431 and mic.organization_id = x_organization_id
1432 and nvl(p_category_id,mic.category_id) = mic.category_id))
1433 AND nvl(p_autoschedule_flag,'N') = nvl(paa.enable_autoschedule_flag, 'N')
1434 AND ((p_schedule_type = 'PLAN_SCHEDULE'
1435 AND paa.enable_plan_schedule_flag = 'Y')
1436 OR
1437 (p_schedule_type = 'SHIP_SCHEDULE'
1438 AND paa.enable_ship_schedule_flag = 'Y'))
1439 AND (nvl(p_autoschedule_flag, 'N') = 'N'
1440 OR
1441 (p_autoschedule_flag = 'Y'
1442 AND
1443 ((p_schedule_type = 'PLAN_SCHEDULE'
1444 AND x_bucket_pattern_id = paa.plan_bucket_pattern_id)
1445 OR
1446 (p_schedule_type = 'SHIP_SCHEDULE'
1447 AND x_bucket_pattern_id = paa.ship_bucket_pattern_id))))
1448 /* Bug 692450 Not checking schedule subtype */
1452 AND
1449 AND (nvl(p_autoschedule_flag, 'N') = 'N'
1450 OR
1451 (p_autoschedule_flag = 'Y'
1453 ((p_schedule_type = 'PLAN_SCHEDULE'
1454 AND x_schedule_subtype = paa.plan_schedule_type)
1455 OR
1456 (p_schedule_type = 'SHIP_SCHEDULE'
1457 AND x_schedule_subtype = paa.ship_schedule_type))))
1458 /* Bug 692450 Not checking schedule subtype */
1459 AND nvl(paa.scheduler_id,-1) =
1460 NVL(p_scheduler_id, nvl(paa.scheduler_id,-1))
1461 AND (p_planner_code IS NULL
1462 OR
1463 EXISTS (SELECT 'check if planner exists in mtl_system_items'
1464 FROM mtl_system_items msi,mtl_planners mtp
1465 WHERE msi.planner_code = p_planner_code
1466 AND msi.inventory_item_id = paa.item_id
1467 AND mtp.organization_id = x_organization_id
1468 AND mtp.planner_code = p_planner_code
1469 AND msi.organization_id = x_organization_id))
1470 AND (p_buyer_id IS NULL
1471 OR
1472 EXISTS (SELECT 'check if buyer exists in mtl_system_items'
1473 FROM mtl_system_items msi
1474 WHERE msi.inventory_item_id = paa.item_id
1475 AND msi.organization_id = x_organization_id
1476 AND msi.buyer_id = p_buyer_id));
1477 /* GROUP BY paa.asl_id,
1478 paa.item_id,
1479 NVL(paa.enable_authorizations_flag, 'N'),
1480 paa.purchasing_unit_of_measure; */
1481
1482 -- DEBUG. Check for disabled org, disabled asl record, anything
1483 -- else that might be disabled
1484
1485 -- Define the cursor to get all items that need to be rebuilt.
1486
1487
1488 -- If this is multi-org, we need to insert an item record for
1489 -- each organization.
1490 CURSOR C_ITEM_ORGS IS
1491 SELECT cso.organization_id
1492 FROM chv_schedule_organizations cso
1493 WHERE cso.batch_id = p_batch_id;
1494
1495 -- We must open a cursor if this is not multi-org. This is a dummy
1496 -- cursor that will find one record.
1497 CURSOR C_ITEM_NOORGS IS
1498 SELECT dummy
1499 FROM sys.dual;
1500
1501 -- Get all orgs currently on the schedule
1502 CURSOR C_ITEM_ORGS_REVISION IS
1503 SELECT distinct csi.organization_id
1504 FROM chv_schedule_items csi
1505 WHERE csi.schedule_id = x_old_schedule_id;
1506
1507 -- Get all records where the flag is set to rebuild.
1508 CURSOR REBUILD IS
1509 SELECT csi.schedule_item_id,
1510 csi.item_id,
1511 decode(csi.item_planning_method,'MRP_PLANNED',3,
1512 'MPS_PLANNED',2,
1513 'DRP_PLANNED',4),
1514 csi.organization_id
1515 FROM chv_schedule_items csi
1516 WHERE csi.schedule_id = x_schedule_id
1517 AND nvl(csi.rebuild_flag, 'N') = 'Y';
1518
1519 -- Get all item records for a schedule.
1520 CURSOR REVISION IS
1521 SELECT csi.schedule_item_id,
1522 csi.organization_id,
1523 csi.item_id,
1524 decode(csi.item_planning_method,'MRP_PLANNED',3,
1525 'MPS_PLANNED',2,
1526 'DRP_PLANNED',4),
1527 csi.purchasing_unit_of_measure
1528 FROM chv_schedule_items csi
1529 WHERE csi.schedule_id = x_old_schedule_id;
1530
1531 -- Get all item records for a schedule and organization.
1532 CURSOR REVISION_MULTI_ORGS IS
1533 SELECT csi.schedule_item_id,
1534 csi.organization_id,
1535 csi.item_id,
1536 decode(csi.item_planning_method,'MRP_PLANNED',3,
1537 'MPS_PLANNED',2,
1538 'DRP_PLANNED',4),
1539 csi.purchasing_unit_of_measure
1540 FROM chv_schedule_items csi
1541 WHERE csi.schedule_id = x_old_schedule_id
1542 AND csi.organization_id = x_organization_id;
1543
1544
1545
1546 BEGIN
1547
1548
1549
1550 ---- dbms_output.put_line('Create_Items: in create items');
1551
1552
1553 -- If this is a item rebuild, it will always be for one oragnization.
1554 -- We do not need to loop through multiple organizations.
1555 IF (p_multi_org_flag = 'N' OR p_multi_org_flag is null OR
1556 p_schedule_category = 'REBUILD') THEN
1557
1558 ---- dbms_output.put_line('Create_Items: before opening C_ITEM_NOORGS');
1559
1560 OPEN C_ITEM_NOORGS;
1561
1562 ELSE
1563
1564 IF (p_schedule_category = 'REVISION') THEN
1565
1566 ---- dbms_output.put_line('Create_items: revision cursor');
1567 OPEN C_ITEM_ORGS_REVISION;
1568
1569 ELSE
1570
1571 ---- dbms_output.put_line('Create_items: no revision cursor');
1572 OPEN C_ITEM_ORGS;
1573
1574 END IF;
1575
1576 END IF;
1577
1578 LOOP
1579
1580
1581 IF (p_multi_org_flag = 'N' OR p_multi_org_flag is NULL
1582 OR p_schedule_category = 'REBUILD') THEN
1583
1584 ---- dbms_output.put_line('Create_Items: before fetching C_ITEM_NOORGS');
1585
1586 -- Do not select any organizations. Just a dummy record.
1587 FETCH C_ITEM_NOORGS
1588 INTO x_dummy;
1589 EXIT WHEN C_ITEM_NOORGS%NOTFOUND;
1590
1591 -- Revision sql statement selects the org. Since we cannot
1592 -- assign a value to a parameter, the workaround is to
1593 -- setup another variable.
1594 x_organization_id := x_organization_id_l;
1595
1596 ELSE
1597
1598 IF (p_schedule_category = 'REVISION') THEN
1599
1600 -- Fetch all orgs currently on schedule.
1601 FETCH C_ITEM_ORGS_REVISION
1602 INTO x_organization_id;
1603 EXIT WHEN C_ITEM_ORGS_REVISION%NOTFOUND;
1604
1605 ELSE
1606
1607 -- Fetch all orgs that the user selected in the WB.
1611
1608 FETCH C_ITEM_ORGS
1609 INTO x_organization_id;
1610 EXIT WHEN C_ITEM_ORGS%NOTFOUND;
1612 END IF;
1613
1614 END IF;
1615
1616
1617 ---- dbms_output.put_line('Create Items: Item'||p_item_id);
1618
1619 ---- dbms_output.put_line('Entering create_items');
1620
1621 -- Open the appropriate cursor based on p_multi_org_flag and
1622 -- p_autoschedule_flag.
1623 x_progress := '010';
1624
1625 IF p_schedule_category = 'REBUILD' THEN
1626 OPEN REBUILD;
1627
1628
1629 ELSIF p_schedule_category = 'REVISION' THEN
1630 IF (p_multi_org_flag = 'N' or p_multi_org_flag is NULL) THEN
1631 OPEN REVISION;
1632 ---- dbms_output.put_line('Create_items: Opening single org cursor');
1633 ELSE
1634 OPEN REVISION_MULTI_ORGS;
1635 ---- dbms_output.put_line('Create_items: Opening Multiorg cursor');
1636 END IF;
1637
1638
1639 ELSE
1640 OPEN CI_SINGLE_ORG_WB_BPM;
1641
1642 END IF;
1643
1644 LOOP
1645 ---- dbms_output.put_line('Create_items: fetching cursor');
1646
1647 -- Fetch one row at a time from the appropriate cursor opened above
1648 -- (based on p_multi_org_flag and p_autoschedule_flag).
1649 -- Exit the loop at the last row
1650 x_progress := '020';
1651 IF p_schedule_category = 'REBUILD' THEN
1652 -- Fetch all items that need to be rebuilt, along with
1653 -- their organization.
1654 FETCH REBUILD
1655 INTO x_schedule_item_id_l,
1656 x_item_id_l,
1657 x_item_planning_method_l,
1658 x_organization_id;
1659 EXIT WHEN REBUILD%NOTFOUND;
1660
1661 -- The options for the item/org/supplier/supplier site from the asl.
1662 SELECT paa.asl_id,
1663 nvl(paa.enable_authorizations_flag,'N'),
1664 paa.purchasing_unit_of_measure,
1665 max(paa.using_organization_id)
1666 INTO x_asl_id_l,
1667 x_enable_authorizations_flag_l,
1668 x_purch_unit_of_measure_l,
1669 x_using_org_id_l
1670 FROM po_asl_attributes_val_v paa
1671 WHERE paa.vendor_id = x_vendor_id
1672 AND paa.vendor_site_id = x_vendor_site_id
1673 AND paa.item_id = x_item_id_l
1674 AND paa.using_organization_id =
1675 (SELECT MAX(paa2.using_organization_id)
1676 FROM po_asl_attributes_val_v paa2
1677 WHERE decode(paa2.using_organization_id, -1,
1678 x_organization_id, paa2.using_organization_id) =
1679 x_organization_id
1680 AND paa2.vendor_id = x_vendor_id
1681 AND paa2.vendor_site_id = x_vendor_site_id
1682 AND paa2.item_id = x_item_id_l)
1683 GROUP BY paa.asl_id, paa.enable_authorizations_flag,
1684 paa.purchasing_unit_of_measure;
1685
1686 -- Get the new primary unit of measure associated with the item
1687 -- in case the primary unit of measure has changed.
1688 SELECT primary_unit_of_measure
1689 INTO x_primary_unit_of_measure_l
1690 FROM MTL_system_items
1691 WHERE organization_id = x_organization_id
1692 AND inventory_item_id = x_item_id_l;
1693
1694 ELSIF p_schedule_category = 'REVISION' THEN
1695 IF (p_multi_org_flag = 'N' or p_multi_org_flag is NULL) THEN
1696 ---- dbms_output.put_line('Create_items: schedule ID'||x_old_schedule_id);
1697
1698 FETCH REVISION
1699 INTO x_schedule_item_id_l,
1700 x_organization_id,
1701 x_item_id_l,
1702 x_item_planning_method_l,
1703 x_purch_unit_of_measure_l;
1704 EXIT WHEN REVISION%NOTFOUND;
1705 ELSE
1706 ---- dbms_output.put_line('Create_items: Mschedule ID'||x_old_schedule_id);
1707 ---- dbms_output.put_line('Create_items: Mschedule ID'||x_organization_id_l);
1708 ---- dbms_output.put_line('Create_items: Mschedule ID'||x_organization_id);
1709 FETCH REVISION_MULTI_ORGS
1710 INTO x_schedule_item_id_l,
1711 x_organization_id,
1712 x_item_id_l,
1713 x_item_planning_method_l,
1714 x_purch_unit_of_measure_l;
1715 EXIT WHEN REVISION_MULTI_ORGS%NOTFOUND;
1716 END IF;
1717
1718 ---- dbms_output.put_line('Create_items: MitemID'||x_item_id_l);
1719 SELECT paa.asl_id,
1720 nvl(paa.enable_authorizations_flag,'N'),
1721 max(paa.using_organization_id)
1722 INTO x_asl_id_l,
1723 x_enable_authorizations_flag_l,
1724 x_using_org_id_l
1725 FROM po_asl_attributes_val_v paa
1726 WHERE paa.vendor_id = x_vendor_id
1727 AND paa.vendor_site_id = x_vendor_site_id
1728 AND paa.item_id = x_item_id_l
1729 AND paa.using_organization_id =
1730 (SELECT MAX(paa2.using_organization_id)
1731 FROM po_asl_attributes_val_v paa2
1732 WHERE decode(paa2.using_organization_id, -1,
1733 x_organization_id, paa2.using_organization_id) =
1734 x_organization_id
1735 AND paa2.vendor_id = x_vendor_id
1736 AND paa2.vendor_site_id = x_vendor_site_id
1737 AND paa2.item_id = x_item_id_l)
1738 GROUP BY paa.asl_id, paa.enable_authorizations_flag;
1739
1740 ELSE
1741 ---- dbms_output.put_line('Create Items: before fetch of WB_BPM');
1742
1743 FETCH CI_SINGLE_ORG_WB_BPM
1744 INTO x_using_org_id_l,
1745 x_asl_id_l,
1746 x_item_id_l,
1747 x_enable_authorizations_flag_l,
1748 x_purch_unit_of_measure_l;
1749 EXIT WHEN CI_SINGLE_ORG_WB_BPM%NOTFOUND;
1750 END IF;
1751
1752
1753 -- Included is the seed data rules for the item_planning_method
1757 -- with the item.
1754 -- 3:MRP; 4:MPS, 6:Not Planned, 7:MRP/DRP, 8:MPS/DRP, 9:DRP
1755 -- If we are rebuilding a schedule, we get the plan type
1756 -- from the schedule header and the planning method associated
1758 ---- dbms_output.put_line ('Create_items: get plan_designator');
1759
1760 IF (p_schedule_category <> 'REBUILD') THEN
1761
1762 -- Select item_planning_method and primary_unit_of_measure from
1763 -- MTL_SYSTEM_ITEMS.
1764 ---- dbms_output.put_line ('Create_items: select from MTL_SYSTEM_ITEMS');
1765
1766 x_progress := '040';
1767 -- DEBUG Prio C. Move to main cursor.
1768 /* SELECT decode(p_schedule_category, 'REVISION', x_item_planning_method_l, */
1769 SELECT mrp_planning_code,
1770 primary_unit_of_measure
1771 INTO x_item_planning_method_l,
1772 x_primary_unit_of_measure_l
1773 FROM MTL_system_items
1774 WHERE organization_id = x_organization_id
1775 AND inventory_item_id = x_item_id_l;
1776
1777 ---- dbms_output.put_line('Create_items: Planning Meth'||x_item_planning_method_l);
1778
1779 x_progress := '050';
1780 -- DEBUG. Beef up comments on this.
1781 IF (x_item_planning_method_l in (3, 7)) THEN
1782 x_plan_designator_l := x_mrp_compile_designator;
1783 x_plan_lookup := 'MRP_PLANNED';
1784 ELSIF (x_item_planning_method_l in (4, 8)) THEN
1785 x_plan_designator_l := x_mps_schedule_designator;
1786 x_plan_lookup := 'MPS_PLANNED';
1787 ELSIF (x_item_planning_method_l = 9) THEN
1788 x_plan_designator_l := x_drp_compile_designator;
1789 x_plan_lookup := 'DRP_PLANNED';
1790 END IF;
1791 END IF;
1792
1793 SELECT nvl(enable_cum_flag,'N')
1794 INTO x_enable_cum_flag_l
1795 FROM chv_org_options
1796 WHERE organization_id = x_organization_id;
1797
1798 -- If we are building a simulation schedule, we will still let
1799 -- you build the schedule if a cum period is not open.
1800 IF (p_schedule_category = 'SIMULATION') THEN
1801 x_enable_cum_flag_l := 'N';
1802 END IF;
1803
1804 -- A CUM period must be open.
1805
1806 IF (x_enable_cum_flag_l = 'Y') THEN
1807 BEGIN
1808
1809 /* Bug 2251090 fixed. In the where clause of the below sql, added
1810 the nvl() statement for cum_period_end_date to take care of null
1811 condition.
1812 */
1813 /* Bug 4485196 fixed. added to_char to below select clause as it was failing with
1814 ORA-6502 error on 10G database.
1815 */
1816 SELECT max(to_char('Y'))
1817 INTO x_enable_cum_flag_l
1818 FROM chv_cum_periods
1819 WHERE organization_id = x_organization_id
1820 AND p_horizon_start_date between
1821 cum_period_start_date and nvl(cum_period_end_date,p_horizon_start_date+1);
1822
1823 IF x_enable_cum_flag_l is null THEN
1824 SELECT organization_name INTO x_organization_name FROM
1825 org_organization_definitions WHERE
1826 organization_id = x_organization_id;
1827 fnd_message.set_name('CHV','CHV_NO_ACTIVE_OPEN_CUM');
1828 FND_MESSAGE.SET_TOKEN('ORG',x_organization_name);
1829 x_str := fnd_message.get;
1830 ---- dbms_output.put_line(x_str);
1831 EXIT;
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN OTHERS THEN raise;
1836
1837 END;
1838
1839 END IF;
1840
1841 -- If cum is enabled, get the cum qty received and the last
1842 -- receipt transaction date.
1843 ---- dbms_output.put_line('Create_items: get_cum_info');
1844
1845 x_progress := '030';
1846 IF x_enable_cum_flag_l = 'Y' THEN
1847 chv_cum_periods_s2.get_cum_info (x_organization_id,
1848 x_vendor_id,
1849 x_vendor_site_id,
1850 x_item_id_l,
1851 p_horizon_start_date,
1852 x_horizon_end_date,
1853 x_purch_unit_of_measure_l,
1854 x_primary_unit_of_measure_l,
1855 x_last_receipt_tranx_id_l,
1856 x_cum_quantity_received_l,
1857 x_cum_qty_received_primary_l,
1858 x_cum_period_end_date_l);
1859 END IF;
1860
1861
1862 -- If p_schedule_category is not REBUILD, insert new chv_schedule_items.
1863 -- Before that, need to get a new unique schedule item id.
1864 IF p_schedule_category <> 'REBUILD' THEN
1865 x_progress := '060';
1866 SELECT chv_schedule_items_s.NEXTVAL
1867 INTO x_schedule_item_id_l
1868 FROM DUAL;
1869
1870 ---- dbms_output.put_line ('Create_items: inserting into chv_schedule_items');
1871 ---- dbms_output.put_line ('Create_items: schedule id'||x_schedule_id);
1872 ---- dbms_output.put_line ('Create_items: schedule_item_id'||x_schedule_item_id_l);
1873 ---- dbms_output.put_line ('Create_items: item id'||x_item_id_l);
1874 ---- dbms_output.put_line ('Create_items: org'||x_organization_id);
1875 ---- dbms_output.put_line ('Create_items: plan'||x_plan_lookup);
1876 ---- dbms_output.put_line ('Create_items: user'||x_user_id);
1877 ---- dbms_output.put_line ('Create_items: login'||x_login_id);
1878
1879 x_progress := '070';
1880
1881 INSERT INTO chv_schedule_items (schedule_id,
1882 schedule_item_id,
1883 organization_id,
1884 item_id,
1888 last_update_date,
1885 item_planning_method,
1886 po_header_id,
1887 po_line_id,
1889 last_updated_by,
1890 creation_date,
1891 created_by,
1892 rebuild_flag,
1893 item_confirm_status,
1894 starting_cum_quantity,
1895 starting_auth_quantity,
1896 starting_cum_qty_primary,
1897 starting_auth_qty_primary,
1898 last_receipt_transaction_id,
1899 purchasing_unit_of_measure,
1900 primary_unit_of_measure,
1901 last_update_login)
1902 VALUES (x_schedule_id,
1903 x_schedule_item_id_l,
1904 x_organization_id,
1905 x_item_id_l,
1906 x_plan_lookup,
1907 x_po_header_id_l,
1908 x_po_line_id_l,
1909 SYSDATE, -- last_update_date
1910 x_user_id, -- last_updated_by
1911 SYSDATE, -- creation_date
1912 x_user_id, -- created_by
1913 'N', -- rebuild_flag
1914 'IN_PROCESS',
1915 x_cum_quantity_received_l,
1916 0, -- starting_auth_qty to be updated later
1917 x_cum_qty_received_primary_l,
1918 0, -- start_auth_qty_prim to be updated later
1919 x_last_receipt_tranx_id_l,
1920 x_purch_unit_of_measure_l,
1921 x_primary_unit_of_measure_l,
1922 x_login_id); -- last_update_login
1923 x_item_created := 'Y';
1924
1925
1926
1927 ELSE -- The schedule type = 'REBUILD'
1928
1929 x_progress := '080';
1930
1931 -- We need to update the primary and purchasing unit of measure
1932 -- on the item in case it has changed since the orginial build.
1933 update chv_schedule_items
1934 set rebuild_flag = 'N',
1935 item_confirm_status = 'IN_PROCESS',
1936 last_updated_by = x_user_id,
1937 last_update_date = sysdate,
1938 last_update_login = x_login_id,
1939 purchasing_unit_of_measure = x_purch_unit_of_measure_l,
1940 primary_unit_of_measure = x_primary_unit_of_measure_l,
1941 starting_cum_quantity = x_cum_quantity_received_l,
1942 starting_cum_qty_primary=x_cum_qty_received_primary_l,
1943 last_receipt_transaction_id=x_last_receipt_tranx_id_l
1944 where schedule_item_id = x_schedule_item_id_l;
1945
1946 x_item_created := 'Y';
1947
1948 --Based on the Item Planning method we need to pass the
1949 --plan designator to load item orders.
1950
1951 IF x_item_planning_method_l = 3 then
1952 x_plan_designator_l := x_mrp_compile_designator ;
1953 ELSIF
1954 x_item_planning_method_l = 2 then
1955 x_plan_designator_l := x_mps_schedule_designator ;
1956 ELSIF
1957 x_item_planning_method_l = 4 then
1958 x_plan_designator_l := x_drp_compile_designator ;
1959 END IF ;
1960
1961 END IF; -- end of if REBUID
1962
1963 ---- dbms_output.put_line ('Create_items: calculating conversion rate.');
1964
1965 -- Get the uom code (3 characters) for the primary unit of measure
1966 x_progress := '080';
1967
1968 BEGIN
1969
1970 SELECT uom_code
1971 INTO x_primary_uom_code_l
1972 FROM mtl_units_of_measure
1973 WHERE unit_of_measure = x_primary_unit_of_measure_l;
1974
1975 EXCEPTION
1976 WHEN NO_DATA_FOUND THEN null;
1977 WHEN OTHERS THEN raise;
1978 END;
1979
1980 -- Get the uom code (3 characters) for the purch unit of measure
1981 x_progress := '090';
1982
1983 BEGIN
1984
1985 SELECT uom_code
1986 INTO x_purchasing_uom_code_l
1987 FROM mtl_units_of_measure
1988 WHERE unit_of_measure = X_purch_unit_of_measure_l;
1989
1990 EXCEPTION
1991 WHEN NO_DATA_FOUND THEN null;
1992 WHEN OTHERS THEN raise;
1993 END;
1994
1995 -- Get the conversion rate for the item going from primary to
1996 -- purchasing unit of measure.
1997 -- precision is to 10 places
1998 x_progress := '100';
1999 inv_convert.inv_um_conversion(x_primary_uom_code_l,
2000 x_purchasing_uom_code_l,
2001 x_item_id_l, x_conversion_rate_l);
2002
2003 ---- dbms_output.put_line('Create_Items: conv rate: P->PO:'||x_conversion_rate_l);
2004
2005 -- Get item's discrete planned orders, requisitions and approved releases.
2006 ---- dbms_output.put_line ('Create_items: load_item_orders');
2007
2008 x_progress := '110';
2009 chv_load_orders.load_item_orders(x_organization_id,
2010 x_schedule_id,
2014 x_item_id_l,
2011 x_schedule_item_id_l,
2012 x_vendor_id,
2013 x_vendor_site_id,
2015 x_purch_unit_of_measure_l,
2016 x_primary_unit_of_measure_l,
2017 x_conversion_rate_l,
2018 p_horizon_start_date,
2019 x_horizon_end_date,
2020 p_include_future_releases,
2021 p_schedule_type,
2022 x_schedule_subtype,
2023 x_plan_designator_l
2024 );
2025
2026 -- Calculate bucket qty
2027 ---- dbms_output.put_line('Create_items: calculate_bucket_qty');
2028 ---- dbms_output.put_line('Create_items: schedule_id'||x_schedule_id);
2029 ---- dbms_output.put_line('Create_items: item id'||x_schedule_item_id_l);
2030 ---- dbms_output.put_line('Create_items: start date'||p_horizon_start_date);
2031 ---- dbms_output.put_line('Create_items: end date'||x_horizon_end_date);
2032 ---- dbms_output.put_line('Create_items: sched type'||p_schedule_type);
2033 ---- dbms_output.put_line('Create_items: cum enabled'||x_enable_cum_flag_l);
2034 ---- dbms_output.put_line('Create_items: cum qty'||x_cum_quantity_received_l);
2035
2036 x_progress := '150';
2037 chv_create_buckets.calculate_buckets( x_schedule_id,
2038 x_schedule_item_id_l,
2039 p_horizon_start_date,
2040 x_horizon_end_date,
2041 x_schedule_subtype,
2042 x_enable_cum_flag_l,
2043 x_cum_quantity_received_l,
2044 x_bucket_descriptor_table,
2045 x_bucket_start_date_table,
2046 x_bucket_end_date_table,
2047 x_past_due,
2048 x_past_due_primary);
2049
2050 -- Logic for getting oldest open Supply Agreement:
2051 -- 1. Get the oldest open supply agreement that has open
2052 -- requirements on the schedule.
2053 -- 2. If there is not a supply agreement on the schedule
2054 -- get the supply agreement that is in the ASL.
2055 -- 3. If there is more than one record on the ASL, get the
2056 -- oldest open one.
2057 -- 4. If there is one record on the ASL. get that one.
2058 -- 5. If there are no ASL records, get the oldest open
2059 -- Supply Agreement.
2060 ---- dbms_output.put_line('Create_items: select po_header and po_line');
2061
2062 x_progress := '115';
2063 /* Bug 2365922 fixed. Appended 'AND' statements to the below sql
2064 to check for vendor_id and vendor_site_id.
2065 */
2066
2067 SELECT min(poh.po_header_id)
2068 INTO x_po_header_id_l
2069 FROM po_headers poh,
2070 chv_item_orders cio,
2071 po_lines pol
2072 WHERE poh.creation_date =
2073 (SELECT min(poh2.creation_date)
2074 FROM po_headers poh2,
2075 po_lines pol2,
2076 chv_item_orders cio2
2077 WHERE cio2.schedule_id = x_schedule_id
2078 AND poh2.po_header_id = cio2.document_header_id
2079 AND cio2.schedule_item_id = x_schedule_item_id_l
2080 AND poh2.po_header_id = pol2.po_header_id
2081 AND pol2.item_id = x_item_id_l
2082 AND cio2.supply_document_type = 'RELEASE')
2083 AND cio.schedule_id = x_schedule_id
2084 AND poh.po_header_id = cio.document_header_id
2085 AND cio.schedule_item_id = x_schedule_item_id_l
2086 AND cio.supply_document_type = 'RELEASE'
2087 AND pol.item_id = x_item_id_l
2088 AND poh.po_header_id = pol.po_header_id
2089 AND poh.vendor_id = x_vendor_id
2090 AND poh.vendor_site_id = x_vendor_site_id ;
2091
2092
2093 IF (x_po_header_id_l is not NULL) THEN
2094
2095 x_progress := '125';
2096
2097 SELECT min(pol.po_line_id)
2098 INTO x_po_line_id_l
2099 FROM po_lines pol
2100 WHERE pol.po_header_id = x_po_header_id_l
2101 AND pol.item_id = x_item_id_l;
2102
2103 ELSE
2104
2105 BEGIN
2106 /* Bug 2365922 fixed. Appended 'AND' statements to the below sql
2107 to check for vendor_id and vendor_site_id.
2108 */
2109
2110 SELECT count(*)
2111 INTO x_number_of_blanket_agreements
2112 FROM po_asl_documents,
2113 po_headers poh
2114 WHERE asl_id = x_asl_id_l
2115 AND using_organization_id = x_using_org_id_l
2116 AND document_type_code = 'BLANKET'
2117 AND poh.po_header_id = document_header_id
2118 AND nvl(poh.supply_agreement_flag,'N') = 'Y'
2119 AND nvl(poh.cancel_flag,'N') = 'N'
2120 AND nvl(poh.closed_code,'OPEN') = 'OPEN'
2121 AND poh.vendor_id = x_vendor_id
2122 AND poh.vendor_site_id = x_vendor_site_id ;
2123
2124 EXCEPTION
2125 WHEN NO_DATA_FOUND then null;
2126 WHEN OTHERS then raise;
2127
2128 END;
2129
2130 x_progress := '120';
2131
2132 IF (x_number_of_blanket_agreements = 1) THEN
2133
2134 ---- dbms_output.put_line('Create_items: #B = 1');
2135 /* Bug 2365922 fixed. Appended 'AND' statements to the below sql
2136 to check for vendor_id and vendor_site_id.
2137 */
2138
2139 SELECT document_header_id,
2140 document_line_id
2144 , po_headers poh
2141 INTO x_po_header_id_l,
2142 x_po_line_id_l
2143 FROM po_asl_documents
2145 WHERE document_type_code = 'BLANKET'
2146 AND poh.po_header_id = document_header_id
2147 AND nvl(poh.supply_agreement_flag,'N') = 'Y'
2148 AND nvl(poh.cancel_flag,'N') = 'N'
2149 AND nvl(poh.closed_code,'OPEN') = 'OPEN'
2150 AND using_organization_id = x_using_org_id_l
2151 AND asl_id = x_asl_id_l
2152 AND poh.vendor_id = x_vendor_id
2153 AND poh.vendor_site_id = x_vendor_site_id ;
2154
2155 ELSIF (x_number_of_blanket_agreements > 1) THEN
2156
2157 ---- dbms_output.put_line('Create_items: #B > 0: asl id'||x_asl_id_l);
2158 ---- dbms_output.put_line('Create_items: sitem id'||x_schedule_item_id_l);
2159 /* Bug 2365922 fixed. Added an 'AND' statement to the below sql
2160 to check for vendor_id and vendor_site_id.
2161 */
2162
2163 SELECT min(poh.po_header_id)
2164 INTO x_po_header_id_l
2165 FROM po_headers poh,
2166 po_lines pol,
2167 po_asl_documents pad
2168 WHERE poh.creation_date =
2169 (SELECT min(poh2.creation_date)
2170 FROM po_headers poh2,
2171 po_lines pol2,
2172 po_asl_documents pad2
2173 WHERE poh2.po_header_id = pad2.document_header_id
2174 AND pad2.asl_id = x_asl_id_l
2175 AND pad2.using_organization_id = x_using_org_id_l
2176 AND poh2.po_header_id = pol2.po_header_id
2177 AND pol2.item_id = x_item_id_l
2178 AND pad2.document_type_code = 'BLANKET')
2179 AND pad.asl_id = x_asl_id_l
2180 AND pad.using_organization_id = x_using_org_id_l
2181 AND poh.po_header_id = pol.po_header_id
2182 AND pol.item_id = x_item_id_l
2183 AND pad.document_type_code = 'BLANKET'
2184 AND poh.vendor_id = x_vendor_id
2185 AND poh.vendor_site_id = x_vendor_site_id ;
2186
2187 x_progress := '125';
2188
2189 ---- dbms_output.put_line('Create_items: select min line id');
2190
2191 SELECT min(pol.po_line_id)
2192 INTO x_po_line_id_l
2193 FROM po_lines pol,
2194 po_asl_documents
2195 WHERE pol.po_header_id = x_po_header_id_l
2196 AND pol.item_id = x_item_id_l
2197 AND pol.po_header_id = document_header_id
2198 AND nvl(pol.cancel_flag,'N') = 'N'
2199 AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED';
2200
2201
2202 ELSE /* There are no asl records */
2203 /* Bug 2365922 fixed. Appended 'AND' statements to the below sql
2204 to check for vendor_id and vendor_site_id.
2205 */
2206
2207 SELECT min(poh.po_header_id)
2208 INTO x_po_header_id_l
2209 FROM po_headers poh,
2210 po_lines pol
2211 WHERE poh.creation_date =
2212 (SELECT min(poh2.creation_date)
2213 FROM po_headers poh2,
2214 po_lines pol2
2215 WHERE poh2.po_header_id = pol2.po_header_id
2216 AND pol2.item_id = x_item_id_l
2217 AND poh2.type_lookup_code = 'BLANKET'
2218 AND poh2.authorization_status = 'APPROVED'
2219 AND nvl(poh2.cancel_flag,'N') = 'N'
2220 AND nvl(poh2.closed_code,'OPEN') = 'OPEN')
2221 AND poh.po_header_id = pol.po_header_id
2222 AND pol.item_id = x_item_id_l
2223 AND poh.type_lookup_code = 'BLANKET'
2224 AND poh.authorization_status = 'APPROVED'
2225 AND nvl(poh.cancel_flag,'N') = 'N'
2226 AND nvl(poh.closed_code,'OPEN') = 'OPEN'
2227 AND poh.vendor_id = x_vendor_id
2228 AND poh.vendor_site_id = x_vendor_site_id ;
2229
2230 x_progress := '125';
2231
2232 ---- dbms_output.put_line('Create_items: select min line id');
2233
2234 SELECT min(pol.po_line_id)
2235 INTO x_po_line_id_l
2236 FROM po_lines pol
2237 WHERE pol.po_header_id = x_po_header_id_l
2238 AND pol.item_id = x_item_id_l
2239 AND nvl(pol.cancel_flag,'N') = 'N'
2240 AND nvl(pol.closed_code,'OPEN') = 'OPEN';
2241
2242
2243 END IF; -- If only one blanket agreement
2244
2245 END IF; -- If no header found
2246
2247 -- Authorizations are calculated only for Planning Schedules if
2248 -- authorizations are enabled in the ASL for the vendor/vendor site/
2249 -- item/org combination.
2250
2251 -- If authorizations are being calculated and CUMS are being tracked,
2252 -- the authorization quanitty starts with the CUM qty received +
2253 -- any past due quantity. If CUMS are not being tracked, the
2254 -- authorizations start with the past due quantity.
2255
2256 ---- dbms_output.put_line('Create_items: calculating starting auth qty');
2257
2258 x_progress := '130';
2259 IF x_enable_authorizations_flag_l = 'Y' AND
2260 p_schedule_type = 'PLAN_SCHEDULE' THEN
2261
2262 x_starting_auth_quantity_l := nvl(x_cum_quantity_received_l,0) +
2263 nvl(x_past_due,0);
2264 x_starting_auth_qty_primary_l := nvl(x_cum_qty_received_primary_l,0) +
2265 nvl(x_past_due_primary,0);
2266
2267 END IF;
2268
2269 -- Update chv_schedule_items with the new po_header_id, po_line_id, and
2270 -- starting authorization quantities.
2271 ---- dbms_output.put_line('Create_items: updating chv_schedule_items');
2272
2273 x_progress := '140';
2274 UPDATE chv_schedule_items
2278 starting_auth_qty_primary = x_starting_auth_qty_primary_l
2275 SET po_header_id = x_po_header_id_l,
2276 po_line_id = x_po_line_id_l,
2277 starting_auth_quantity = x_starting_auth_quantity_l,
2279 WHERE schedule_item_id = x_schedule_item_id_l;
2280
2281
2282 ---- dbms_output.put_line('Create_items: calc_auth_qty');
2283
2284 -- Calculate authorization quantities.
2285 x_progress := '160';
2286 IF x_enable_authorizations_flag_l = 'Y' AND p_schedule_type =
2287 'PLAN_SCHEDULE' THEN
2288
2289 ---- dbms_output.put_line('Create_items: before insert into auths');
2290
2291 chv_create_authorizations.insert_authorizations(x_organization_id,
2292 x_schedule_id,
2293 x_schedule_item_id_l,
2294 x_asl_id_l,
2295 p_horizon_start_date,
2296 x_horizon_end_date,
2297 x_starting_auth_quantity_l,
2298 x_starting_auth_qty_primary_l,
2299 x_cum_quantity_received_l,
2300 x_cum_qty_received_primary_l,
2301 x_cum_period_end_date_l,
2302 x_purch_unit_of_measure_l,
2303 x_primary_unit_of_measure_l,
2304 x_enable_cum_flag_l);
2305 END IF;
2306
2307 x_last_receipt_tranx_id_l := to_number(null);
2308 x_cum_quantity_received_l := to_number(null);
2309 x_cum_qty_received_primary_l := to_number(null);
2310 x_cum_period_end_date_l := to_date(null);
2311
2312 END LOOP;
2313
2314 ---- dbms_output.put_line ('Create_items: here1 cursor');
2315 -- Close the appropriate cursor
2316 x_progress := '170';
2317 IF p_schedule_category = 'REVISION' THEN
2318 IF (p_multi_org_flag = 'N' or p_multi_org_flag is NULL) THEN
2319 CLOSE REVISION;
2320 ELSE
2321 CLOSE REVISION_MULTI_ORGS;
2322 END IF;
2323 ELSIF p_schedule_category = 'REBUILD' THEN
2324 CLOSE REBUILD;
2325 ELSE
2326 CLOSE CI_SINGLE_ORG_WB_BPM;
2327 END IF;
2328
2329 END LOOP; -- organization loop.
2330
2331
2332 ---- dbms_output.put_line ('Create_items: closing cursor');
2333
2334 IF (p_multi_org_flag = 'Y') THEN
2335 IF p_schedule_category = 'REVISION' THEN
2336 CLOSE C_ITEM_ORGS_REVISION;
2337 ELSE
2338 IF p_schedule_category = 'REBUILD' THEN
2339 CLOSE C_ITEM_NOORGS;
2340 ELSE
2341 CLOSE C_ITEM_ORGS;
2342 END IF;
2343 END IF;
2344 ELSE
2345 CLOSE C_ITEM_NOORGS;
2346 END IF;
2347
2348
2349 ---- dbms_output.put_line('Exiting creat_items');
2350
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353 po_message_s.sql_error('create_items', x_progress, sqlcode);
2354 RAISE;
2355
2356
2357 END create_items;
2358
2359 END CHV_BUILD_SCHEDULES;
[Home] [Help]