[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, pp_update_effective_date in date
, pp_creation_date in date
, pp_created_by in number
, pp_last_update_date in date
, pp_last_updated_by in number
, pp_last_update_login in number
, pp_option_flag in varchar2
)
IS
CURSOR c_rpi_charge_lines_d Is
SELECT pp_run_id run_id,
standing_charge_id,
line_item_id,
item_id,
price,
org_id, /*MOAC Impact*/
current_effective_date effective_date,
pp_amount change_amount,
pp_percentage_amount change_percent,
pp_incr_decr_flag incr_decr_flag,
pp_update_effective_date update_effective_date,
revised_price,
revised_effective_date,
null updated_price,
null select_flag,
pp_creation_date creation_date,
pp_created_by created_by,
pp_last_update_date last_update_date,
pp_last_updated_by last_updated_by,
pp_last_update_login last_update_login
FROM igi_rpi_line_details lines
WHERE exists ( select item_id
from igi_rpi_items items
where item_code >= pp_item_code_from
AND item_code <= pp_item_code_to
AND items.item_id = lines.item_id
)
AND exists
( select 'x'
from igi_rpi_standing_charges charges
where lines.standing_charge_id = charges.standing_charge_id
and set_of_books_id = ( select set_of_books_id from ar_system_parameters )
)
AND exists
( select 'x'
from igi_rpi_items items
where decode(sign(trunc(nvl(lines.revised_effective_date, (pp_update_effective_date + 1)))
- trunc(pp_update_effective_date)),1, lines.price, lines.revised_price)
= decode(sign(trunc(nvl(items.revised_price_eff_date, (pp_update_effective_date + 1)))
- trunc(pp_update_effective_date)),1, items.price, items.revised_price)
);
SELECT pp_run_id run_id,
standing_charge_id,
line_item_id,
item_id,
price,
org_id, /*MOAC Impact*/
current_effective_date effective_date,
pp_amount change_amount,
pp_percentage_amount change_percent,
pp_incr_decr_flag incr_decr_flag,
pp_update_effective_date update_effective_date,
revised_price,
revised_effective_date,
null updated_price,
null select_flag,
pp_creation_date creation_date,
pp_created_by created_by,
pp_last_update_date last_update_date,
pp_last_updated_by last_updated_by,
pp_last_update_login last_update_login
FROM igi_rpi_line_details lines
WHERE exists ( select item_id
from igi_rpi_items items
where item_code >= pp_item_code_from
AND item_code <= pp_item_code_to
AND items.item_id = lines.item_id
)
AND exists
( select 'x'
from igi_rpi_standing_charges charges
where lines.standing_charge_id = charges.standing_charge_id
and set_of_books_id = ( select set_of_books_id from ar_system_parameters )
)
;
SELECT pp_run_id run_id,
item_code,
item_id,
price,
org_id, /*Added for MOAC Impact*/
price_effective_date effective_date,
pp_amount change_amount,
pp_percentage_amount change_percent,
pp_incr_decr_flag incr_decr_flag,
pp_update_effective_date update_effective_date,
revised_price,
revised_price_eff_date revised_effective_date,
null updated_price,
null select_flag,
pp_creation_date creation_date,
pp_created_by created_by,
pp_last_update_date last_update_date,
pp_last_updated_by last_updated_by,
pp_last_update_login last_update_login
FROM igi_rpi_items
where item_code >= pp_item_code_from
AND item_code <= pp_item_code_to
AND set_of_books_id =
( select set_of_books_id from ar_system_parameters )
;
lv_updated_price igi_rpi_update_lines.updated_price%TYPE;
lv_select_flag igi_rpi_update_lines.select_flag%TYPE;
lv_updated_price := null;
lv_select_flag := 'Y';
IF (l_items.revised_effective_date is not NULL AND l_items.revised_effective_date < l_items.update_effective_date) THEN
IF l_items.incr_decr_flag = 'Y' THEN
lv_updated_price := l_items.revised_price + l_items.change_amount;
lv_updated_price := l_items.revised_price - l_items.change_amount;
ELSIF (l_items.revised_effective_date is NULL AND l_items.effective_date < l_items.update_effective_date) THEN
IF(l_items.incr_decr_flag = 'Y') THEN
lv_updated_price := l_items.price + l_items.change_amount;
lv_updated_price := l_items.price - l_items.change_amount;
IF (l_items.revised_effective_date is not NULL AND l_items.revised_effective_date < l_items.update_effective_date) THEN
IF(l_items.incr_decr_flag = 'Y') THEN
lv_updated_price := l_items.revised_price *(100+ l_items.change_percent)/100;
lv_updated_price := l_items.revised_price *( 100 - l_items.change_percent)/100;
ELSIF (l_items.revised_effective_date is NULL AND l_items.effective_date < l_items.update_effective_date) THEN
IF(l_items.incr_decr_flag = 'Y') THEN
lv_updated_price := l_items.price *(100+ l_items.change_percent)/100;
lv_updated_price := l_items.price *( 100 - l_items.change_percent)/100;
IF ((lv_updated_price is not NULL) AND (lv_updated_price >= 0)) THEN
/* Added ORG_ID in the insert statement*/
insert into igi_rpi_update_lines
( run_id, item_id, price, effective_date,
revised_price, revised_effective_date,
updated_price, select_flag,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, org_id
)
values ( l_items.run_id, l_items.item_id, l_items.price, l_items.effective_date,
l_items.revised_price, l_items.revised_effective_date,
lv_updated_price, lv_select_flag,
l_items.creation_date, l_items.created_by, l_items.last_update_date,
l_items.last_updated_by, l_items.last_update_login, l_items.org_id );
lv_updated_price := null;
lv_select_flag := 'Y';
IF (l_details.revised_effective_date is not NULL AND l_details.revised_effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.revised_price + l_details.change_amount;
lv_updated_price := l_details.revised_price - l_details.change_amount;
ELSIF (l_details.revised_effective_date is NULL AND l_details.effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.price + l_details.change_amount;
lv_updated_price := l_details.price - l_details.change_amount;
IF (l_details.revised_effective_date is not NULL AND l_details.revised_effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.revised_price * (100 + l_details.change_percent)/100;
lv_updated_price := l_details.revised_price * (100 - l_details.change_percent)/100;
ELSIF (l_details.revised_effective_date is NULL AND l_details.effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.price * (100 + l_details.change_percent)/100;
lv_updated_price := l_details.price * (100 - l_details.change_percent)/100;
IF ((lv_updated_price is not NULL) AND (lv_updated_price >= 0)) THEN
/*R12 uptake Added ORG_ID for MOAC Impact Bug No 5905216*/
insert into igi_rpi_update_lines
(run_id, standing_charge_id, line_item_id, item_id, price, effective_date,
revised_price, revised_effective_date,
updated_price, select_flag,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, org_id)
values ( l_details.run_id, l_details.standing_charge_id, l_details.line_item_id,
l_details.item_id, l_details.price, l_details.effective_date,
l_details.revised_price, l_details.revised_effective_date,
lv_updated_price, lv_select_flag,
l_details.creation_date, l_details.created_by,
l_details.last_update_date, l_details.last_updated_by,
l_details.last_update_login, l_details.org_id );
lv_updated_price := null;
lv_select_flag := 'Y';
IF (l_details.revised_effective_date is not NULL AND l_details.revised_effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.revised_price + l_details.change_amount;
lv_updated_price := l_details.revised_price - l_details.change_amount;
ELSIF (l_details.revised_effective_date is NULL AND l_details.effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.price + l_details.change_amount;
lv_updated_price := l_details.price - l_details.change_amount;
IF (l_details.revised_effective_date is not NULL AND l_details.revised_effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.revised_price * (100 + l_details.change_percent)/100;
lv_updated_price := l_details.revised_price * (100 - l_details.change_percent)/100;
ELSIF (l_details.revised_effective_date is NULL AND l_details.effective_date < l_details.update_effective_date) THEN
IF(l_details.incr_decr_flag = 'Y') THEN
lv_updated_price := l_details.price * (100 + l_details.change_percent)/100;
lv_updated_price := l_details.price * (100 - l_details.change_percent)/100;
IF ((lv_updated_price is not NULL) AND (lv_updated_price >= 0)) THEN
/*R12 Uptake Added ORG_ID for MOAC Impact bug No 5905216*/
insert into igi_rpi_update_lines
(run_id, standing_charge_id, line_item_id, item_id, price, effective_date,
revised_price, revised_effective_date,
updated_price, select_flag,
creation_date, created_by, last_update_date, last_updated_by,
last_update_login, org_id)
values ( l_details.run_id, l_details.standing_charge_id, l_details.line_item_id,
l_details.item_id, l_details.price, l_details.effective_date,
l_details.revised_price, l_details.revised_effective_date,
lv_updated_price, lv_select_flag,
l_details.creation_date, l_details.created_by,
l_details.last_update_date, l_details.last_updated_by,
l_details.last_update_login, l_details.org_id );
PROCEDURE UpdatePrice (pp_run_id in number ) IS
Cursor C_rul (cp_run_id in number) IS
SELECT item_id,
standing_charge_id,
line_item_id,
price,
effective_date,
revised_effective_date,
revised_price,
updated_price,
previous_price,
previous_effective_date,
last_updated_by,
last_update_login
FROM igi_rpi_update_lines_all /*MOAC Impact Bug No 5905216*/
WHERE run_id = cp_run_id
and UPPER(select_flag) = UPPER('y') ;
SELECT rowid row_id,
run_id,
item_id_from,
item_id_to,
effective_date,
option_flag,
amount,
percentage_amount,
status,
org_id
FROM igi_rpi_update_hdr_all /*MOAC Impact Bug No 5905216*/
WHERE run_id = pp_run_id ;
SELECT charge_item_number
FROM igi_rpi_line_details
WHERE line_item_id = p_line_item_id
AND standing_charge_id = p_standing_charge_id;
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.updateprice.Msg1',
'Start of Processing of records for Update');
--WriteToLogFile('Updating status of Update Header to ERROR');
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg2',
'Updating status of Update Header to ERROR');
update igi_rpi_update_hdr
set status = 'ERROR'
where rowid = l_ruh.row_id ;
--WriteToLogFile('Update Effective Date :'|| to_char(l_ruh.effective_date));
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg3',
'Item Id :'|| to_char(l_rul.item_id));
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg4',
'Standing Charge Id :'|| to_char(l_rul.Standing_charge_id));
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg5',
'Update Effective Date :'|| to_char(l_ruh.effective_date));
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg6',
'Revised Price :'|| to_char(l_rul.revised_price));
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg7',
'Revised Effective Date :'||to_char(l_rul.revised_effective_date));
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg8',
'Updating revised price of Charge Item');
update igi_rpi_items
set revised_price = l_rul.updated_price,
revised_price_eff_date = l_ruh.effective_date,
last_update_date = sysdate,
last_updated_by = l_rul.last_updated_by,
last_update_login = l_rul.last_update_login
where item_id = l_rul.item_id;
--WriteToLogFile('Inserting record into audit table');
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg9',
'Inserting record into audit table');
igi_rpi_audit_items_all_pkg.insert_row( X_rowid => l_rowid,
X_item_id => l_rul.item_id,
X_price => l_rul.price,
X_effective_date => l_rul.effective_date,
X_revised_effective_date => l_rul.revised_effective_date,
X_revised_price => l_rul.revised_price,
X_run_id => l_ruh.run_id,
/*X_org_id => to_number(l_org_id));*/ /*Bug No 5905216*/
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg10',
'Updating revised price of Charge Item');
update igi_rpi_items
set price = l_rul.revised_price,
price_effective_date = l_rul.revised_effective_date,
revised_price = l_rul.updated_price,
revised_price_eff_date = l_ruh.effective_date,
last_update_date = sysdate,
last_updated_by = l_rul.last_updated_by,
last_update_login = l_rul.last_update_login
where item_id = l_rul.item_id;
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg11',
'Updating revised price of Standing Charge Line Item');
update igi_rpi_line_details
set revised_price = l_rul.updated_price,
revised_effective_date = l_ruh.effective_date,
last_update_date = sysdate,
last_updated_by = l_rul.last_updated_by,
last_update_login = l_rul.last_update_login
where item_id = l_rul.item_id
and line_item_id = l_rul.line_item_id
and standing_charge_id = l_rul.standing_charge_id;
--WriteToLogFile('Insert record into Line Item Price Audit table with the run id');
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg12',
'Insert record into Line Item Price Audit table with the run id');
igi_rpi_line_audit_det_all_pkg.insert_row( X_rowid => l_rowid,
X_standing_charge_id => l_rul.standing_charge_id,
X_line_item_id => l_rul.line_item_id,
X_charge_item_number => l_charge_item_number,
X_item_id => l_rul.item_id,
X_price => l_rul.price,
X_effective_date => l_rul.effective_date,
X_revised_price => l_rul.revised_price,
X_revised_effective_date => l_rul.revised_effective_date,
X_run_id => l_ruh.run_id,
X_org_id => to_number(l_org_id),
X_previous_price => l_rul.previous_price,
X_previous_effective_date => l_rul.previous_effective_date);
--WriteToLogFile('Updating status of Update Header to COMPLETED');
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg13',
'Updating status of Update Header to COMPLETED');
update igi_rpi_update_hdr
set status = 'COMPLETED'
where run_id = l_ruh.run_id ;
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrgpp.update_price.Msg14',
'End of processing of Items for Price update');
END UpdatePrice;
PROCEDURE UpdatePriceCP (
errbuf out NOCOPY varchar2
,retcode out NOCOPY number
,pp_run_id in number
)
IS
BEGIN
UpdatePrice ( pp_run_id ) ;
END UpdatePriceCP;