[Home] [Help]
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,
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:
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,
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,
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:
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:
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';
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:
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:
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:
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,
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,
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,