[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: insert_authorizations()
=============================================================================*/
PROCEDURE insert_authorizations( p_organization_id IN NUMBER,
p_schedule_id IN NUMBER,
p_schedule_item_id IN NUMBER,
p_asl_id IN NUMBER,
p_horizon_start_date IN DATE,
p_horizon_end_date IN DATE,
p_starting_auth_qty IN NUMBER,
p_starting_auth_qty_primary IN NUMBER,
p_starting_cum_qty IN NUMBER,
p_starting_cum_qty_primary IN NUMBER,
p_cum_period_end_date IN DATE,
p_purch_unit_of_measure IN VARCHAR2,
p_primary_unit_of_measure IN VARCHAR2,
p_enable_cum_flag IN VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
SELECT authorization_code,
authorization_sequence,
timefence_days
FROM chv_authorizations
WHERE reference_type = 'ASL'
AND reference_id = p_asl_id
AND using_organization_id = nvl(p_organization_id, -1);
SELECT sum(order_quantity),
sum(order_quantity_primary)
INTO x_authorization_qty,
x_authorization_qty_primary
FROM chv_item_orders cio
WHERE cio.schedule_id = p_schedule_id
AND cio.schedule_item_id = p_schedule_item_id
AND cio.due_date between p_horizon_start_date and
x_auth_end_date ;
INSERT INTO chv_authorizations(reference_id,
reference_type,
authorization_code,
authorization_sequence,
using_organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
primary_unit_of_measure,
purchasing_unit_of_measure,
timefence_days,
cutoff_date,
schedule_quantity_primary,
schedule_quantity,
last_update_login)
VALUES(p_schedule_item_id,
'SCHEDULE_ITEMS',
x_authorization_code,
x_authorization_sequence,
p_organization_id,
SYSDATE,
x_user_id,
SYSDATE,
x_user_id,
p_primary_unit_of_measure,
p_purch_unit_of_measure,
x_timefence_days,
x_auth_end_date,
nvl(x_authorization_qty_primary,0) +
nvl(p_starting_auth_qty_primary,0),
nvl(x_authorization_qty,0) +
nvl(p_starting_auth_qty,0),
x_login_id);
po_message_s.sql_error('insert_authorizations', x_progress, sqlcode);
END insert_authorizations;
SELECT cau.authorization_code,
cau.authorization_sequence,
cau.primary_unit_of_measure,
cau.purchasing_unit_of_measure,
cau.timefence_days
FROM chv_authorizations cau
WHERE cau.reference_type = 'ASL'
AND cau.reference_id = p_asl_id
AND cau.using_organization_id = nvl(p_organization_id, -1);
SELECT cau.schedule_quantity,
cau.schedule_quantity_primary
INTO x_schedule_quantity,
x_schedule_quantity_primary
FROM chv_authorizations cau
WHERE cau.reference_id = p_schedule_item_id
AND cau.reference_type = 'SCHEDULE_ITEMS'
AND cau.authorization_code = x_authorization_code ;
SELECT high_auth_quantity,
high_auth_qty_primary
INTO x_high_auth_quantity,
x_high_auth_qty_primary
FROM chv_authorizations cau
WHERE cau.reference_id = p_cum_period_item_id
AND cau.reference_type = 'CUM_PERIODS'
AND cau.authorization_code = x_authorization_code ;
UPDATE chv_authorizations
SET high_auth_quantity = x_schedule_quantity,
high_auth_qty_primary = x_schedule_quantity_primary,
high_auth_schedule_item_id = p_schedule_item_id,
schedule_quantity = x_schedule_quantity,
schedule_quantity_primary = x_schedule_quantity_primary,
last_update_date = SYSDATE,
last_updated_by = x_user_id,
last_update_login = x_login_id
WHERE reference_id = p_cum_period_item_id
AND reference_type = 'CUM_PERIODS'
AND authorization_code = x_authorization_code ;
UPDATE chv_authorizations
SET schedule_quantity = x_schedule_quantity,
schedule_quantity_primary = x_schedule_quantity_primary,
last_update_date = SYSDATE,
last_updated_by = x_user_id,
last_update_login = x_login_id
WHERE reference_id = p_cum_period_item_id
AND reference_type = 'CUM_PERIODS'
AND authorization_code = x_authorization_code ;
INSERT INTO chv_authorizations (reference_id,
reference_type,
authorization_code,
authorization_sequence,
last_update_date,
last_updated_by,
creation_date,
created_by,
primary_unit_of_measure,
purchasing_unit_of_measure,
timefence_days,
cutoff_date,
schedule_quantity_primary,
schedule_quantity,
high_auth_qty_primary,
high_auth_quantity,
high_auth_schedule_item_id,
last_update_login)
VALUES (p_cum_period_item_id,
'CUM_PERIODS',
x_authorization_code,
x_authorization_sequence,
SYSDATE,
x_user_id,
SYSDATE,
x_user_id,
x_primary_unit_of_measure,
x_purchasing_unit_of_measure,
x_timefence_days,
NULL,
x_schedule_quantity_primary,
x_schedule_quantity,
x_schedule_quantity_primary,
x_schedule_quantity,
p_schedule_item_id,
x_login_id);