[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(pp_component,'DAY' , ( TO_NUMBER(pp_factor)* -1 )+ pp_date
,'WEEK' , ( TO_NUMBER(pp_factor)* -7 )+ pp_date
,'MONTH' , ADD_MONTHS(pp_date,-1*TO_NUMBER(pp_factor))
,'YEAR' , ADD_MONTHS(pp_date, TO_NUMBER(pp_factor)* -12 )
)
INTO ld_date
FROM SYS.DUAL
;
SELECT DECODE(pp_component,'DAY' , ( TO_NUMBER(pp_factor) )+ pp_date
,'WEEK' , ( TO_NUMBER(pp_factor)* 7 )+ pp_date
,'MONTH' , ADD_MONTHS(pp_date,TO_NUMBER(pp_factor))
,'YEAR' , ADD_MONTHS(pp_date, TO_NUMBER(pp_factor)*12 )
)
INTO ld_date
FROM SYS.DUAL
;
SELECT date1, date2, date3, date4
FROM igi_rpi_period_schedules
WHERE schedule_id = pp_schedule_id
AND nvl(enabled_flag,'Y') = 'Y';
SELECT date1, date2, date3, date4
FROM igi_rpi_period_schedules
WHERE schedule_id = pp_schedule_id
AND nvl(enabled_flag,'Y') = 'Y';
SELECT sc.period_name
FROM igi_rpi_standing_charges sc
WHERE sc.standing_charge_id = pp_standing_charge_id
AND sc.set_of_books_id = ( select set_of_books_id
from ar_system_parameters )
AND sc.status = CHARGE_STATUS
;
SELECT nvl(js.schedule_id,0) schedule_id
, jr.component
, jr.factor
FROM igi_rpi_component_periods jr
, igi_rpi_period_schedules js
WHERE jr.period_name = pp_period_name
AND jr.schedule_id = js.schedule_id
AND js.period_name = pp_period_name
UNION
SELECT 0 schedule_id, jr.component, jr.factor
FROM igi_rpi_component_periods jr
WHERE jr.period_name = pp_period_name
AND NOT EXISTS ( select 'x'
from igi_rpi_period_schedules js
where js.period_name = jr.period_name
and js.schedule_id = jr.schedule_id
)
;
SELECT sc.period_name
FROM igi_rpi_standing_charges sc
WHERE sc.standing_charge_id = pp_standing_charge_id
AND sc.set_of_books_id = ( select set_of_books_id
from ar_system_parameters )
AND sc.status = CHARGE_STATUS
;
SELECT nvl(js.schedule_id,-1) schedule_id
, jr.component
, jr.factor
FROM igi_rpi_component_periods jr
, igi_rpi_period_schedules js
WHERE jr.period_name = pp_period_name
AND jr.schedule_id = js.schedule_id
AND js.period_name = pp_period_name
UNION
SELECT 0 schedule_id, jr.component, jr.factor
FROM igi_rpi_component_periods jr
WHERE jr.period_name = pp_period_name
AND NOT EXISTS ( select 'x'
from igi_rpi_period_schedules js
where js.period_name = jr.period_name
and js.schedule_id = jr.schedule_id )
;
PROCEDURE UpdateStandingCharges
( pp_standing_charge_id IN NUMBER
, pp_generate_sequence IN NUMBER )
IS
CURSOR C_UpdateStandingCharges (cp_standing_charge_id IN NUMBER
,cp_generate_sequence IN NUMBER )
IS
SELECT sc.standing_charge_id , sc.rowid sc_rowid, sc.charge_reference,
sc.start_date, sc.standing_charge_date
, sc.end_date , sc.next_due_date , sc.previous_due_date
, sc.advance_arrears_ind
, nvl(sc.date_synchronized_flag,ALREADY_SYNC_STATUS) date_synchronized_flag
FROM igi_rpi_standing_charges sc
WHERE sc.standing_charge_id = cp_standing_charge_id
AND sc.generate_sequence = cp_generate_sequence
AND sc.set_of_books_id = ( select set_of_books_id
from ar_system_parameters )
AND sc.status = CHARGE_STATUS
;
select rowid row_id, start_date
from igi_rpi_line_details
where standing_charge_id = cp_standing_charge_id
and start_date is not null
;
lv_update_sc C_UpdateStandingCharges%ROWTYPE;
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg1',
' Beginning of UpdateStandingCharges...');
FOR lv_update_sc IN C_UpdateStandingCharges (pp_standing_charge_id,pp_generate_sequence)
LOOP
--WriteToLog ( ' ------------------------------------------------------- ');
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg2',
' Standing Charge ID '|| lv_update_sc.standing_charge_id );
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg3',
' Standing Charge Ref '|| lv_update_sc.charge_reference );
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg4',
' Old Next Due Date '|| lv_update_sc.next_due_date );
if lv_update_sc.date_synchronized_flag = SYNCHRONIZED_STATUS
then
ld_new_next_due_date := GetNewNextDate ( lv_update_sc.standing_charge_id
, lv_update_sc.next_due_date
);
IF (lv_update_sc.standing_charge_date IS NOT NULL) THEN
ld_new_sc_date := GetNewNextDate ( lv_update_sc.standing_charge_id
, lv_update_sc.standing_charge_date
);
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg5',
' New Next due Date '|| ld_new_next_due_date );
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg6',
' New GL Date '|| ld_new_sc_date );
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg7',
'New Next due date is null.');
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg8',
'New GL date is null ');
UPDATE IGI_RPI_STANDING_CHARGES
SET next_due_date = ld_new_next_due_date,
standing_charge_date = ld_new_sc_date,
previous_due_date = lv_update_sc.next_due_date,
date_synchronized_flag = ALREADY_SYNC_STATUS
WHERE ROWID = lv_update_sc.sc_rowid
;
FOR l_lines in C_lines( lv_update_sc.standing_charge_id)
LOOP
ld_new_ld_date := GetNewNextDate
( lv_update_sc.standing_charge_id
, l_lines.start_date
);
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg9',
'New Next due date is null.');
UPDATE igi_rpi_line_details
SET start_date = ld_new_ld_date
WHERE rowid = l_lines.row_id
;
FND_LOG.STRING(l_state_level, 'igi.plsql.igirrps.update_standing_charges.Msg10',
'End UpateStandingCharges...');
SELECT sc.standing_charge_id, sc.generate_sequence
, sc.batch_source_id
, sc.charge_reference
FROM igi_rpi_standing_charges sc
WHERE sc.set_of_books_id = p_set_of_books_id
AND sc.standing_charge_id = nvl( p_standing_charge_id
, sc.standing_charge_id )
AND sc.batch_source_id = nvl(p_batch_source_id,
sc.batch_source_id)
AND sc.status = CHARGE_STATUS
AND sc.next_due_date <= p_run_date
and sc.date_synchronized_flag = NOT_SYNC_STATUS
;
SELECT rct.interface_header_attribute1 trx_sc_id
, rct.interface_header_attribute2 trx_seq
, rct.customer_Trx_id
, rct.trx_date
, rsc.next_due_date
, rsc.advance_arrears_ind
, rsc.batch_source_id
, rsc.default_invoicing_rule
from ra_customer_trx rct
, igi_rpi_standing_charges rsc
where rct.batch_source_id = nvl(cp_batch_source_id,
rct.batch_source_id )
and rct.set_of_books_id = p_set_of_books_id
and to_char(rsc.standing_charge_id) = rct.interface_header_attribute1
and to_char(rsc.generate_sequence) = rct.interface_header_attribute2
and rsc.set_of_books_id = p_set_of_books_id
-- AND rsc.standing_charge_id = nvl( p_standing_charge_id
-- , rsc.standing_charge_id )
AND rsc.standing_charge_id = nvl( cp_standing_charge_id
, rsc.standing_charge_id ) /* BUG 3951309 agovil */
AND rsc.batch_source_id = nvl(cp_batch_source_id
, rsc.batch_source_id )
and trunc(rsc.next_due_date) <= trunc(p_run_date)
and rsc.date_synchronized_flag = NOT_SYNC_STATUS
and exists
( select 'x'
from igi_ar_system_options
where rpi_header_context_code = rct.interface_header_context
)
and exists
( select 'x'
from ra_customer_trx_lines rctl
where rctl.customer_trx_id = rct.customer_trx_id
)
;
/*Added additional columns in line_det for retrieving price and effective date to update
the Standing Charges and Price History - RPI Enhancement.*/
cursor line_det ( cp_sc_id in number) is
select line_item_id,
revised_price,
revised_effective_date,
run_id,
org_id,
charge_item_number,
item_id,
price,
current_effective_date
from igi_rpi_line_details_all
where standing_charge_id = cp_sc_id
;
SELECT interface_line_id
FROM ra_interface_lines
WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
;
delete from ra_interface_errors
where interface_line_id = l_xface.interface_line_id
;
' Deleted the errors information.');
delete from ra_interface_salescredits
WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
;
' Deleted the Sales information.');
delete from ra_interface_distributions
WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
;
' Deleted the Distribution information.');
delete from ra_interface_lines
WHERE interface_line_attribute1 = to_char(l_sc.standing_charge_id)
AND interface_line_attribute2 = to_char(l_sc.generate_sequence)
;
' Interface information deleted for '||
l_sc.standing_charge_id );
update igi_rpi_standing_charges
set date_synchronized_flag = ALREADY_SYNC_STATUS
where standing_charge_id = l_sc.standing_charge_id
and generate_sequence = l_sc.generate_sequence
and date_synchronized_flag = NOT_SYNC_STATUS
;
UpdateStandingCharges ( l_trx.trx_sc_id ,l_trx.trx_seq ) ;
Select next_due_date
into l_next_due_date
From igi_rpi_standing_charges
Where standing_charge_id = l_trx.trx_sc_id
And generate_sequence = l_trx.trx_seq
;
and the where condn for next due date in update statement
for Bug NO 2454958 */
IF (trunc(line_rec.revised_effective_date) <= trunc(p_run_date)) THEN
igi_rpi_line_audit_det_all_pkg.insert_row (
x_mode => 'R',
x_rowid => lv_rowid,
x_standing_charge_id => TO_NUMBER (l_trx.trx_sc_id),
x_line_item_id => TO_NUMBER (line_rec.LINE_ITEM_ID),
x_charge_item_number => TO_NUMBER (line_rec.CHARGE_ITEM_NUMBER),
x_item_id => TO_NUMBER (line_rec.ITEM_ID),
x_price => line_rec.REVISED_PRICE,
x_effective_date => line_rec.REVISED_EFFECTIVE_DATE,
x_revised_price => null,
x_revised_effective_date => null,
x_run_id => TO_NUMBER (line_rec.RUN_ID),
x_org_id => TO_NUMBER (line_rec.ORG_ID),
x_previous_price => line_rec.PRICE,
x_previous_effective_date => line_rec.CURRENT_EFFECTIVE_DATE
);
update igi_rpi_line_details_all
set previous_price = price,
previous_effective_date = current_effective_date,
price = revised_price,
current_effective_date = revised_effective_date,
revised_price = '',
revised_effective_date = ''
where line_item_id = line_rec.line_item_id
and trunc(revised_effective_date)
<= trunc(p_run_date);
UpdateStandingCharges ( l_trx.trx_sc_id ,l_trx.trx_seq ) ;