4 /*======================= CHV_CONFIRM_SCHEDULES =============================*/
1 PACKAGE BODY CHV_CONFIRM_SCHEDULES as
2 /* $Header: CHVPRCSB.pls 115.3 2002/11/26 23:40:01 sbull ship $ */
3
5
6 /*=============================================================================
7
8 PROCEDURE NAME: confirm_schedule_item()
9
10 =============================================================================*/
11 FUNCTION confirm_schedule_item(p_schedule_id in NUMBER,
15 p_organization_id in NUMBER,
12 p_schedule_item_id in NUMBER,
13 p_vendor_id in NUMBER,
14 p_vendor_site_id in NUMBER,
16 p_item_id in NUMBER) return boolean is
17
18 x_progress VARCHAR2(3) := NULL;
19 x_message VARCHAR2(240);
20 x_item_confirm BOOLEAN DEFAULT FALSE;
21
22 x_user_id NUMBER := NVL(fnd_global.user_id, 0);
23 x_login_id NUMBER := NVL(fnd_global.login_id, 0);
24
25 BEGIN
26
27 x_progress := '010';
28
29 -- Call asl action procedure to verify if this schedule item for
30 -- the supplier/site/item/org can be confirmed.
31
32 IF po_asl_sv.check_asl_action('3_SCHEDULE_CONFIRMATION',
33 p_vendor_id,
34 p_vendor_site_id,
35 p_item_id,
36 -1,
37 p_organization_id) = 1 THEN
38
39 x_item_confirm := TRUE;
40
41 --If the above action returned TRUE then the schedule item can be
42 --confirmed. Update the status and the standard who columns.
43
44 x_progress := '020';
45
46 UPDATE chv_schedule_items
47 SET item_confirm_status = 'CONFIRMED',
48 last_update_date = SYSDATE,
49 last_updated_by = x_user_id,
50 last_update_login = x_login_id
51 WHERE schedule_item_id = p_schedule_item_id ;
52
53 ELSE
54
55 --Action returned FALSE that the schedule item cannot be confirmed.
56 --So initialize the message and also create a record in the PO
57 --Interface table.
58
59 x_message := FND_MESSAGE.GET_STRING('CHV', 'CHV_NOT_CONFIRMED');
60
61 x_progress := '030';
62
63 dbms_output.put_line(x_message) ;
64 /* Now since we have the online report as messages we do not need this
65
66 INSERT INTO po_interface_errors(interface_type,
67 interface_transaction_id,
68 column_name,
69 error_message,
70 processing_date,
71 creation_date,
72 created_by,
73 last_update_date,
74 last_updated_by,
75 last_update_login)
76 VALUES('SCHEDULE_BUILD',
77 p_schedule_item_id,
78 'ITEM_CONFIRM_STATUS',
79 x_message,
80 SYSDATE,
81 SYSDATE,
82 x_user_id,
83 SYSDATE,
84 x_user_id,
85 x_login_id);
86
87 */
88 END IF;
89
90 --dbms_output.put_line('confirm schedule item : Exiting') ;
91
92 --Return the boolean value to the calling procedure.
93
94 RETURN(x_item_confirm) ;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 po_message_s.sql_error('confirm_schedule_item', x_progress, sqlcode);
99 RAISE;
100
101 END confirm_schedule_item ;
102 /*=============================================================================
103
104 PROCEDURE NAME: confirm_schedule_header()
105
106 =============================================================================*/
107 PROCEDURE confirm_schedule_header(p_schedule_id in NUMBER,
108 p_schedule_type in VARCHAR2,
109 p_communication_code in VARCHAR2 default null,
110 p_confirm_source in VARCHAR2,
111 p_confirmed IN OUT NOCOPY VARCHAR2) IS
112
113 x_progress VARCHAR2(3) := NULL;
114
115 x_vendor_id NUMBER := 0 ;
116 x_vendor_site_id NUMBER := 0 ;
117 x_item_org_id NUMBER := 0 ;
118 x_schedule_item_id NUMBER := 0 ;
119 x_item_id NUMBER := 0 ;
120 x_schedule_horizon_start DATE ;
121 x_enable_cum_flag VARCHAR2(1) ;
122 x_cum_period_id NUMBER := 0 ;
123 x_cum_period_item_id NUMBER := 0 ;
124 x_asl_id NUMBER := 0 ;
125 x_enable_authorization_flag VARCHAR2(1) ;
126 x_item_confirm BOOLEAN := TRUE;
127 x_item_confirm_status VARCHAR2(25) ;
128 x_purchasing_unit_of_measure VARCHAR2(25) ;
129 x_primary_unit_of_measure VARCHAR2(25) ;
130 x_dummy_org NUMBER := 0 ;
131 x_message VARCHAR2(240) ;
132 x_open_cum_period VARCHAR2(1) := 'N';
133
134 -- X_header_confirm is initialized to 'Y' => to confirm schedule header;
135 -- When any item can not be confirmed, it will be set to 'N', =>
136 -- not to confirm schedule header.
137
138 x_header_confirm VARCHAR2(1) := 'Y';
139
140 x_user_id NUMBER := NVL(fnd_global.user_id, 0);
141 x_login_id NUMBER := NVL(fnd_global.login_id, 0);
142
143
144 CURSOR c_all_schedule_items IS
145 SELECT csh.schedule_horizon_start,
146 csh.vendor_id,
147 csh.vendor_site_id,
151 csi.item_confirm_status,
148 csi.schedule_item_id,
149 csi.item_id,
150 csi.organization_id,
152 csi.primary_unit_of_measure,
153 csi.purchasing_unit_of_measure,
154 coo.enable_cum_flag
155 FROM chv_schedule_items csi,
156 chv_schedule_headers csh,
157 chv_org_options coo
158 WHERE csi.schedule_id = p_schedule_id
159 AND csi.schedule_id = csh.schedule_id
160 AND csi.organization_id = coo.organization_id ;
161
162 BEGIN
163
164 --dbms_output.put_line('Confirm Header: Entering') ;
165
166 x_progress := '010';
167
168 OPEN c_all_schedule_items;
169
170 -- Set a savepoint.if any item belonging to this header can not
171 -- be confirmed then the header can not be confirmed
172 -- and rollback all items belong to this header.
173
174 x_progress := '020';
175
176 SAVEPOINT confirm_schedule_savepoint;
177
178 LOOP
179
180 --dbms_output.put_line('Confirm Header: Looping') ;
181
182 x_progress := '030';
183
184 FETCH c_all_schedule_items
185 INTO x_schedule_horizon_start,
186 x_vendor_id,
187 x_vendor_site_id,
188 x_schedule_item_id,
189 x_item_id,
190 x_item_org_id,
191 x_item_confirm_status,
192 x_primary_unit_of_measure,
193 x_purchasing_unit_of_measure,
194 x_enable_cum_flag ;
195
196 EXIT WHEN c_all_schedule_items%NOTFOUND;
197
198 x_progress := '040';
199
200 IF nvl(x_item_confirm_status,'IN_PROCESS') = 'IN_PROCESS' then
201
202 --dbms_output.put_line('Confirm Header: item_status'||x_item_confirm_status) ;
203
204 x_item_confirm := confirm_schedule_item(p_schedule_id,
205 x_schedule_item_id,
206 x_vendor_id,
207 x_vendor_site_id,
208 x_item_org_id,
209 x_item_id) ;
210
211 END IF ;
212
213 IF x_item_confirm = TRUE and
214 p_schedule_type = 'PLAN_SCHEDULE' THEN
215
216 -- Item is confirmed and the schedule on the schedule header is a
217 -- planning schedule.
218
219 BEGIN
220
221 SELECT 'Y'
222 INTO x_open_cum_period
223 FROM chv_cum_periods ccp
224 WHERE x_schedule_horizon_start between ccp.cum_period_start_date
225 and ccp.cum_period_end_date
226 AND ccp.organization_id = x_item_org_id;
227
228
229 EXCEPTION
230 WHEN NO_DATA_FOUND THEN null;
231 WHEN OTHERS THEN raise;
232
233 END;
234
235
236 IF nvl(x_enable_cum_flag,'N') = 'Y' AND x_open_cum_period = 'Y' THEN
237
238 -- Cums are enabled for this Organization. Select cum details
239 -- in order to update high authorizations for the supplier/site
240 -- item/org.
241
242 --dbms_output.put_line('Confirm Header: select cum period id') ;
243
244 -- Select the open cum period for the organization. The period
245 -- should cover the schedule horizon start date.
246
247 x_progress := '050' ;
248
249 SELECT ccp.cum_period_id
250 INTO x_cum_period_id
251 FROM chv_cum_periods ccp
252 WHERE x_schedule_horizon_start
253 BETWEEN ccp.cum_period_start_date
254 AND ccp.cum_period_end_date
255 AND ccp.organization_id = x_item_org_id;
256
257 --dbms_output.put_line('Confirm Header cum period id:'||to_char(x_cum_period_id)) ;
258
259 -- For the item select asl info with reference to authorizations
260 -- for the supplier/site/item/org.
261
262 x_progress := '060' ;
263
264 SELECT paa.using_organization_id,
265 paa.asl_id,
266 paa.enable_authorizations_flag
267 INTO x_dummy_org,
268 x_asl_id,
269 x_enable_authorization_flag
270 FROM po_asl_attributes_val_v paa
271 WHERE ((paa.using_organization_id = -1 and not exists
272 (SELECT *
273 FROM po_asl_attributes_val_v paa2
274 WHERE paa2.using_organization_id = x_item_org_id
275 AND paa2.vendor_id = x_vendor_id
276 AND paa2.vendor_site_id = x_vendor_site_id
277 AND paa2.item_id = x_item_id))
278 or
279 (paa.using_organization_id = x_item_org_id))
280 AND paa.vendor_id = x_vendor_id
281 AND paa.vendor_site_id = x_vendor_site_id
282 AND paa.item_id = x_item_id;
283 /* GROUP BY paa.asl_id, paa.enable_authorizations_flag; */
284
285 IF x_enable_cum_flag = 'Y' AND
286 p_schedule_type = 'PLAN_SCHEDULE' AND
287 x_enable_authorization_flag = 'Y' THEN
288
289 -- The schedule is a planning schedule, authorizations for the
290 -- supplier/site/item/org are on and the cums are being maintained
291 -- for all the items in the Organization so calculate high authorization
292 -- information.
293
294 BEGIN
295
296 x_progress := '070' ;
297
298 --Select cum period item id for the supplier/site/item/org.
299
300 SELECT cum_period_item_id
301 INTO x_cum_period_item_id
302 FROM chv_cum_period_items
306 AND item_id = x_item_id ;
303 WHERE cum_period_id = x_cum_period_id
304 AND vendor_id = x_vendor_id
305 AND vendor_site_id = x_vendor_site_id
307
308 EXCEPTION WHEN NO_DATA_FOUND then
309
310 x_progress := '080' ;
311
312 -- No cum period item record found so create a new one.
313
314 SELECT chv_cum_period_items_s.nextval
315 INTO x_cum_period_item_id
316 FROM dual ;
317
318 INSERT into chv_cum_period_items(cum_period_item_id,
319 cum_period_id,
320 organization_id,
321 vendor_id,
322 vendor_site_id,
323 item_id,
324 last_update_date,
325 last_updated_by,
326 creation_date,
327 created_by,
328 last_update_login)
329 VALUES(x_cum_period_item_id,
330 x_cum_period_id,
331 x_item_org_id,
332 x_vendor_id,
333 x_vendor_site_id,
334 x_item_id,
335 SYSDATE,
336 x_user_id,
337 SYSDATE,
338 x_user_id,
339 x_login_id) ;
340 WHEN OTHERS then
341 RAISE ;
342 END ;
343
344 x_progress := '080' ;
345
346 --Execute procedure to calculate high authorization quantities for
347 --all the authorization codes for the supplier/site/item/org.
348
349 chv_create_authorizations.calc_high_auth_qty(x_item_org_id,
350 p_schedule_id,
351 x_schedule_item_id,
352 x_vendor_id,
353 x_vendor_site_id,
354 x_item_id,
355 x_asl_id,
356 x_schedule_horizon_start,
357 x_cum_period_item_id);
358 END IF;
359
360 END IF ;
361
362 ELSIF x_item_confirm = FALSE THEN
363
364 ROLLBACK TO confirm_schedule_savepoint;
365
366 x_header_confirm := 'N';
367
368 EXIT;
369
370 END IF ;
371
372 END LOOP;
373
374 CLOSE c_all_schedule_items;
375
376 -- Confirm header is x_header_confirm is set to 'Y'
377
378 IF x_header_confirm = 'Y' THEN
379
380 x_progress := '080' ;
381
382 -- All the items have been successfully confirmed so
383 -- confirm the schedule header.
384
385 UPDATE chv_schedule_headers
386 SET schedule_status = 'CONFIRMED',
387 confirm_date = SYSDATE,
388 last_update_date = SYSDATE,
389 last_updated_by = x_user_id,
390 last_update_login = x_login_id,
391 communication_code = p_communication_code
392 WHERE schedule_id = p_schedule_id;
393
394 END IF;
395
396 p_confirmed := x_header_confirm;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 po_message_s.sql_error('confirm_schedules', x_progress, sqlcode);
401 RAISE;
402
403 END confirm_schedule_header;
404
405 END CHV_CONFIRM_SCHEDULES;
[Home] [Help]