[Home] [Help]
PACKAGE BODY: APPS.CHV_CREATE_AUTHORIZATIONS
Source
1 PACKAGE BODY CHV_CREATE_AUTHORIZATIONS as
2 /* $Header: CHVPRAUB.pls 115.1 99/07/17 01:29:32 porting sh $ */
3
4 /*====================== CHV_CREATE_AUTHORIZATIONS ==========================*/
5
6 /*=============================================================================
7
8 PROCEDURE NAME: insert_authorizations()
9
10 =============================================================================*/
11 PROCEDURE insert_authorizations( p_organization_id IN NUMBER,
12 p_schedule_id IN NUMBER,
13 p_schedule_item_id IN NUMBER,
14 p_asl_id IN NUMBER,
15 p_horizon_start_date IN DATE,
16 p_horizon_end_date IN DATE,
17 p_starting_auth_qty IN NUMBER,
18 p_starting_auth_qty_primary IN NUMBER,
19 p_starting_cum_qty IN NUMBER,
20 p_starting_cum_qty_primary IN NUMBER,
21 p_cum_period_end_date IN DATE,
22 p_purch_unit_of_measure IN VARCHAR2,
23 p_primary_unit_of_measure IN VARCHAR2,
24 p_enable_cum_flag IN VARCHAR2) IS
25
26 x_progress VARCHAR2(3) := NULL;
27 x_authorization_code VARCHAR2(25);
28 x_authorization_sequence NUMBER := 0 ;
29 x_timefence_days NUMBER := 0 ;
30
31 x_auth_end_date DATE;
32 x_authorization_qty NUMBER := 0 ;
33 x_authorization_qty_primary NUMBER := 0 ;
34
35 x_user_id NUMBER := 0 ;
36 x_login_id NUMBER := 0 ;
37
38 -- get the authorizations that are specified in the chv_authorizations
39 -- for the supplier/site/item/org.
40
41 CURSOR x_asl_auth_codes IS
42 SELECT authorization_code,
43 authorization_sequence,
44 timefence_days
45 FROM chv_authorizations
46 WHERE reference_type = 'ASL'
47 AND reference_id = p_asl_id
48 AND using_organization_id = nvl(p_organization_id, -1);
49
50 BEGIN
51
52 x_progress := '010';
53
54 -- Get x_user_id and x_login_id from the global variable set.
55
56 x_user_id := NVL(fnd_global.user_id, 0);
57 x_login_id := NVL(fnd_global.login_id, 0);
58
59 OPEN x_asl_auth_codes;
60
61 LOOP
62
63 x_progress := '020';
64
65 FETCH x_asl_auth_codes
66 INTO x_authorization_code,
67 x_authorization_sequence,
68 x_timefence_days;
69
70 EXIT WHEN x_asl_auth_codes%NOTFOUND;
71
72 -- Calculate authorization end date for each authorization based on the
73 -- least of (p_horizon_end_date, cum_period_end_date and
74 -- horizon_start_date + timefence_days) If cum period end date is null
75 -- then auth end date is the least of horizon start date + timefence and
76 -- horizon end date.
77
78 x_authorization_qty := 0 ;
79 x_authorization_qty_primary := 0 ;
80
81 x_auth_end_date := LEAST( p_horizon_end_date,
82 (p_horizon_start_date + (x_timefence_days-1)),
83 nvl(p_cum_period_end_date,LEAST((p_horizon_start_date + (x_timefence_days-1)),
84 p_horizon_end_date))
85 ) ;
86
87 -- Get authorization quantities in purchasing and primary UOM's from
88 -- CHV_ITEM_ORDERS based on the authorization end date calculated above
89 -- for each authorization
90
91 x_progress := '030';
92
93 BEGIN
94
95 --May not find any records for the given authorization within the
96 --time fence. No need to raise an exception. Authorization for
97 --the schedule item will be created with zero quantity.
98
99 SELECT sum(order_quantity),
100 sum(order_quantity_primary)
101 INTO x_authorization_qty,
102 x_authorization_qty_primary
103 FROM chv_item_orders cio
104 WHERE cio.schedule_id = p_schedule_id
105 AND cio.schedule_item_id = p_schedule_item_id
106 AND cio.due_date between p_horizon_start_date and
107 x_auth_end_date ;
108
109 EXCEPTION
110 WHEN OTHERS THEN null ;
111
112 END ;
113
114 --dbms_output.put_line('Auth Code:'||x_authorization_code) ;
115 --dbms_output.put_line('Time Fence:'||to_char(x_timefence_days)) ;
116 --dbms_output.put_line('Auth End:'||to_char(x_auth_end_date,'DD-MON-YYYY')) ;
117 --dbms_output.put_line('Pri Qty:'||to_char(x_authorization_qty)) ;
118 --dbms_output.put_line('Pur Qty:'||to_char(x_authorization_qty_primary)) ;
119
120 -- Insert into CHV_AUTHORIZATIONS table the authorization code date.
121
122 x_progress := '040';
123
124 INSERT INTO chv_authorizations(reference_id,
125 reference_type,
126 authorization_code,
127 authorization_sequence,
128 using_organization_id,
129 last_update_date,
130 last_updated_by,
131 creation_date,
132 created_by,
133 primary_unit_of_measure,
134 purchasing_unit_of_measure,
135 timefence_days,
136 cutoff_date,
137 schedule_quantity_primary,
138 schedule_quantity,
139 last_update_login)
140 VALUES(p_schedule_item_id,
141 'SCHEDULE_ITEMS',
142 x_authorization_code,
143 x_authorization_sequence,
144 p_organization_id,
145 SYSDATE,
146 x_user_id,
147 SYSDATE,
148 x_user_id,
149 p_primary_unit_of_measure,
150 p_purch_unit_of_measure,
151 x_timefence_days,
152 x_auth_end_date,
153 nvl(x_authorization_qty_primary,0) +
154 nvl(p_starting_auth_qty_primary,0),
155 nvl(x_authorization_qty,0) +
156 nvl(p_starting_auth_qty,0),
157 x_login_id);
158 END LOOP;
159
160 x_progress := '050';
161 CLOSE x_asl_auth_codes;
162
163 EXCEPTION
164 WHEN OTHERS THEN
165 po_message_s.sql_error('insert_authorizations', x_progress, sqlcode);
166 RAISE;
167
168 END insert_authorizations;
169
170 /*=============================================================================
171
172 PROCEDURE NAME: calc_high_auth_qty()
173
174 =============================================================================*/
175 PROCEDURE calc_high_auth_qty(p_organization_id IN NUMBER,
176 p_schedule_id IN NUMBER,
177 p_schedule_item_id IN NUMBER,
178 p_vendor_id IN NUMBER,
179 p_vendor_site_id IN NUMBER,
180 p_item_id IN NUMBER,
181 p_asl_id IN NUMBER,
182 p_horizon_start_date IN DATE,
183 p_cum_period_item_id IN NUMBER) IS
184
185 x_progress VARCHAR2(3) := NULL;
186
187 x_authorization_code VARCHAR2(25);
188 x_primary_unit_of_measure VARCHAR2(25);
189 x_purchasing_unit_of_measure VARCHAR2(25);
190 x_authorization_sequence NUMBER := 0 ;
191 x_timefence_days NUMBER := 0 ;
192 x_schedule_quantity NUMBER := 0 ;
193 x_schedule_quantity_primary NUMBER := 0 ;
194 x_high_auth_quantity NUMBER := 0 ;
195 x_high_auth_qty_primary NUMBER := 0 ;
196 x_user_id NUMBER := 0 ;
197 x_login_id NUMBER := 0 ;
198
199 -- Get all the authorization codes from chv_authorizations for this x_asl_id
200 -- for the supplier/site/item/org.
201
202 CURSOR c_auth_codes IS
203
204 SELECT cau.authorization_code,
205 cau.authorization_sequence,
206 cau.primary_unit_of_measure,
207 cau.purchasing_unit_of_measure,
208 cau.timefence_days
209 FROM chv_authorizations cau
210 WHERE cau.reference_type = 'ASL'
211 AND cau.reference_id = p_asl_id
212 AND cau.using_organization_id = nvl(p_organization_id, -1);
213
214 BEGIN
215
216 -- Get user id and login id from the global variable set.
217
218 x_user_id := NVL(fnd_global.user_id, 0);
219 x_login_id := NVL(fnd_global.login_id, 0);
220
221 x_progress := '010';
222
223 OPEN c_auth_codes;
224
225 LOOP
226
227 x_progress := '020';
228
229 FETCH c_auth_codes
230 INTO x_authorization_code,
231 x_authorization_sequence,
232 x_primary_unit_of_measure,
233 x_purchasing_unit_of_measure,
234 x_timefence_days;
235
236 EXIT WHEN c_auth_codes%NOTFOUND;
237
238 -- Select schedule(current) authorization quantity from chv_authorizations
239 -- for every authorization code retreived in the above cursor
240 -- for the supplier/site/item/org.
241
242 x_progress := '030';
243
244 SELECT cau.schedule_quantity,
245 cau.schedule_quantity_primary
246 INTO x_schedule_quantity,
247 x_schedule_quantity_primary
248 FROM chv_authorizations cau
249 WHERE cau.reference_id = p_schedule_item_id
250 AND cau.reference_type = 'SCHEDULE_ITEMS'
251 AND cau.authorization_code = x_authorization_code ;
252
253 -- Select high authorization information for the cum period for each
254 -- authorization for the supplier/site/item/org.
255
256 x_progress := '040';
257
258 BEGIN
259
260 SELECT high_auth_quantity,
261 high_auth_qty_primary
262 INTO x_high_auth_quantity,
263 x_high_auth_qty_primary
264 FROM chv_authorizations cau
265 WHERE cau.reference_id = p_cum_period_item_id
266 AND cau.reference_type = 'CUM_PERIODS'
267 AND cau.authorization_code = x_authorization_code ;
268
269 -- If this schedule(current) authorization quantity is over previous
270 -- high authorization quantities then update chv_authorizations
271 -- with this new qty and the schedule id.
272
273 x_progress := '050';
274
275 IF x_schedule_quantity_primary > x_high_auth_qty_primary THEN
276
277 UPDATE chv_authorizations
278 SET high_auth_quantity = x_schedule_quantity,
279 high_auth_qty_primary = x_schedule_quantity_primary,
280 high_auth_schedule_item_id = p_schedule_item_id,
281 schedule_quantity = x_schedule_quantity,
282 schedule_quantity_primary = x_schedule_quantity_primary,
283 last_update_date = SYSDATE,
284 last_updated_by = x_user_id,
285 last_update_login = x_login_id
286 WHERE reference_id = p_cum_period_item_id
287 AND reference_type = 'CUM_PERIODS'
288 AND authorization_code = x_authorization_code ;
289
290 ELSE
291
292 UPDATE chv_authorizations
293 SET schedule_quantity = x_schedule_quantity,
294 schedule_quantity_primary = x_schedule_quantity_primary,
295 last_update_date = SYSDATE,
296 last_updated_by = x_user_id,
297 last_update_login = x_login_id
298 WHERE reference_id = p_cum_period_item_id
299 AND reference_type = 'CUM_PERIODS'
300 AND authorization_code = x_authorization_code ;
301
302 END IF;
303
304 EXCEPTION
305 WHEN NO_DATA_FOUND then
306
307 -- If this is the first schedule(current) authorization for the
308 -- cum period insert a new high authorization record.
309
310 x_progress := '60' ;
311
312 INSERT INTO chv_authorizations (reference_id,
313 reference_type,
314 authorization_code,
315 authorization_sequence,
316 last_update_date,
317 last_updated_by,
318 creation_date,
319 created_by,
320 primary_unit_of_measure,
321 purchasing_unit_of_measure,
322 timefence_days,
323 cutoff_date,
324 schedule_quantity_primary,
325 schedule_quantity,
329 last_update_login)
326 high_auth_qty_primary,
327 high_auth_quantity,
328 high_auth_schedule_item_id,
330 VALUES (p_cum_period_item_id,
331 'CUM_PERIODS',
332 x_authorization_code,
333 x_authorization_sequence,
334 SYSDATE,
335 x_user_id,
336 SYSDATE,
337 x_user_id,
338 x_primary_unit_of_measure,
339 x_purchasing_unit_of_measure,
340 x_timefence_days,
341 NULL,
342 x_schedule_quantity_primary,
343 x_schedule_quantity,
344 x_schedule_quantity_primary,
345 x_schedule_quantity,
346 p_schedule_item_id,
347 x_login_id);
348 END ;
349
350 END LOOP;
351
352 CLOSE c_auth_codes;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 po_message_s.sql_error('calc_high_auth_qty', x_progress, sqlcode);
357 RAISE;
358
359 END CALC_HIGH_AUTH_QTY;
360
361 END chv_create_authorizations;