[Home] [Help]
PACKAGE BODY: APPS.CHV_REBUILD_SCHEDULES
Source
1 PACKAGE BODY CHV_REBUILD_SCHEDULES as
2 /* $Header: CHVPRRBB.pls 115.0 99/07/17 01:30:16 porting ship $ */
3
4 /*======================= CHV_REBUILD_SCHEDULE===============================*/
5
6 /*=============================================================================
7
8 PROCEDURE NAME: rebuild_scheduled_items()
9
10 =============================================================================*/
11 PROCEDURE rebuild_item(
12 p_schedule_id in NUMBER,
13 p_autoconfirm_flag in VARCHAR2,
14 p_print_flag in VARCHAR2 DEFAULT null) IS
15
16 x_progress VARCHAR2(3) := NULL; -- For debugging purpose
17
18 -- Following variables whose values should be retrieved from schedule select
19 x_vendor_id NUMBER;
20 x_vendor_site_id NUMBER;
21 x_organization_id NUMBER;
22 x_bucket_pattern_id NUMBER;
23 x_mrp_compile_designator VARCHAR2(10);
24 x_mps_schedule_designator VARCHAR2(10);
25 x_drp_compile_designator VARCHAR2(10);
26 x_schedule_subtype VARCHAR2(25);
27 x_schedule_type VARCHAR2(25);
28 x_horizon_start_date DATE;
29 x_schedule_revision NUMBER;
30 x_schedule_num VARCHAR2(25);
31 x_include_future_releases VARCHAR2(1);
32 x_item_created VARCHAR2(1);
33 x_schedule_item_id NUMBER;
34 x_multi_org_flag VARCHAR2(1);
35 x_batch_id NUMBER;
36
37 -- Following variables whose values are calculated within the procedure.
38
39 x_user_id NUMBER;
40 x_login_id NUMBER;
41 x_horizon_end_date DATE;
42
43 -- 3 PL/SQL tables used for calculating bucket quantities.
44 x_bucket_descriptor_table chv_create_buckets.bkttable;
45 x_bucket_start_date_table chv_create_buckets.bkttable;
46 x_bucket_end_date_table chv_create_buckets.bkttable;
47
48 x_bucket_count BINARY_INTEGER := 1; -- DEBUG need this?
49
50 -- We will be getting the item_planning_method in chv_schedule_items
51 -- in the create_items routine.
52 CURSOR ITEMS IS
53 SELECT schedule_item_id
54 FROM chv_schedule_items
55 WHERE schedule_id = p_schedule_id
56 AND rebuild_flag = 'Y';
57
58 BEGIN
59
60 dbms_output.put_line('Entering rebuild_schedule');
61
62 -- Get x_user_id and x_login_id from the global variable set.
63 x_user_id := NVL(fnd_global.user_id, 0);
64 x_login_id := NVL(fnd_global.login_id, 0);
65
66 x_progress := '010';
67
68 -- Find the Schedule Header that we are rebuilding. We will be using
69 -- information stored at the schedule header level to determine what
70 -- data is created at the lower level.
71 SELECT bucket_pattern_id,
72 vendor_id,
73 vendor_site_id,
74 organization_id,
75 mrp_compile_designator,
76 mps_schedule_designator,
77 drp_compile_designator,
78 schedule_subtype,
79 schedule_type,
80 schedule_horizon_start,
81 include_future_releases_flag
82 INTO x_bucket_pattern_id,
83 x_vendor_id,
84 x_vendor_site_id,
85 x_organization_id,
86 x_mrp_compile_designator,
87 x_mps_schedule_designator,
88 x_drp_compile_designator,
89 x_schedule_subtype,
90 x_schedule_type,
91 x_horizon_start_date,
92 x_include_future_releases
93 FROM chv_schedule_headers
94 WHERE schedule_id = p_schedule_id;
95
96 -- Note We need to account for that the plans may no longer
97 -- exist. They may have been deleted by the planning system.
98 -- The code should be ok and not fail if this happens. Leaving
99 -- this comment here, so we are aware of the issue.
100
101 -- Create 3 temp bucket tables (descriptor, start_date and end_date)
102 -- and get x_horizon_end_date in the meantime.
103 dbms_output.put_line('Build_schedule: create_bucket_template');
104
105 x_progress := '020';
106
107 chv_create_buckets.create_bucket_template(x_horizon_start_date,
108 x_include_future_releases,
109 x_bucket_pattern_id,
110 x_horizon_end_date,
111 x_bucket_descriptor_table,
112 x_bucket_start_date_table,
113 x_bucket_end_date_table);
114
115 dbms_output.put_line('Build Schedules: end Date'||x_horizon_end_date);
116
117
118 x_progress := '030';
119
120 OPEN ITEMS;
121
122 LOOP
123
124 x_progress := '040';
125
126 FETCH ITEMS INTO
127 x_schedule_item_id;
128 EXIT WHEN ITEMS%NOTFOUND;
129
130
131 x_progress := '050';
132
133 -- Delete the item that we are rebuilding
134 BEGIN
135
136 DELETE FROM chv_item_orders
137 WHERE schedule_id = p_schedule_id
138 AND schedule_item_id = x_schedule_item_id;
139
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN null;
142 WHEN OTHERS THEN raise;
143
144 END;
145
146 x_progress := '060';
147
148 -- Delete the corresponding records in chv_horizontal_schedules.
149 BEGIN
150
151 DELETE FROM chv_horizontal_schedules
152 WHERE schedule_id = p_schedule_id
153 AND schedule_item_id = x_schedule_item_id;
154
155 EXCEPTION
156
157 WHEN NO_DATA_FOUND THEN null;
158 WHEN OTHERS THEN raise;
159
160 END;
161
162 x_progress := '070';
163
164 -- Delete the corresponding records in authorizations.
165 BEGIN
166
167 DELETE from chv_authorizations
168 WHERE reference_id = x_schedule_item_id
169 AND reference_type = 'SCHEDULE_ITEMS';
170
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN null;
173 WHEN OTHERS THEN raise;
174
175 END;
176
177 -- If there is no organization at the header level, we are
178 -- building a multi-org schedule.
179 IF (x_organization_id is NULL) THEN
180 x_multi_org_flag := 'Y';
181 ELSE
182 x_multi_org_flag := 'N';
183 END IF;
184
185 x_progress := '080';
186
187 chv_build_schedules.create_items('REBUILD',
188 'N',
189 x_schedule_type,
190 x_schedule_subtype,
191 p_schedule_id,
192 null,
193 null,
194 x_horizon_start_date,
195 null,
196 x_include_future_releases,
197 x_mrp_compile_designator,
198 x_mps_schedule_designator,
199 x_drp_compile_designator,
200 x_organization_id,
201 x_multi_org_flag,
202 x_vendor_id,
203 x_vendor_site_id,
204 null,
205 null,
206 null,
207 null,
208 null,
209 null,
210 x_user_id,
211 x_login_id,
212 x_horizon_end_date,
213 x_bucket_descriptor_table,
214 x_bucket_start_date_table,
215 x_bucket_end_date_table,
216 x_item_created,
217 null,
218 x_bucket_pattern_id,
219 x_schedule_subtype,
220 x_batch_id);
221
222 END LOOP; -- end loop of fetching cursor
223
224 CLOSE ITEMS;
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 CLOSE ITEMS;
229 po_message_s.sql_error('rebuild_schedule', x_progress, sqlcode);
230 RAISE;
231
232 END rebuild_item;
233
234 END CHV_REBUILD_SCHEDULES;