[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gsob.currency_code
FROM gl_sets_of_books gsob
WHERE set_of_books_id = cp_sob_id;
SELECT set_of_books_id
FROM ar_system_parameters;
SELECT name
FROM gl_sets_of_books
where set_of_books_id = ( select set_of_books_id from ar_system_parameters );
select set_of_books_id, org_id, accounting_method,
sysdate creation_date, sysdate last_update_date,
-1 last_updated_by, -1 created_by, -1 last_update_login
from ar_system_parameters_all aspa
where not exists ( select 'Already set up'
from igi_ar_system_options_all
where set_of_books_id = aspa.set_of_books_id
and org_id = aspa.org_id )
;
insert into igi_ar_system_options_all ( set_of_books_id
, accounting_method
, org_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values (l_asp.set_of_books_id,
l_asp.accounting_method
, l_asp.org_id
, g_date
, g_user_id
, g_date
, g_user_id
, g_login_id
);
DELETE FROM igi_ar_system_options_all a
WHERE NOT EXISTS
(
SELECT 'X'
FROM ar_system_parameters_all
WHERE org_id = a.org_id
AND set_of_books_id = a.set_of_books_id
);
select dunning_letter_set_id
from ar_dunning_letter_sets core
where not exists
( select 'x'
from igi_dun_letter_set_cur igi
where igi.dunning_letter_set_id =
core.dunning_letter_set_id
)
;
select currency_code
from gl_sets_of_books
where set_of_books_id in ( select set_of_books_id
from ar_system_parameters
)
;
select customer_id
from ra_customers
;
select distinct acpa.currency_code, acp.dunning_letter_set_id
from ar_customer_profile_amounts acpa
, ar_customer_profiles acp
where acpa.customer_id = cp_customer_id
and acp.customer_id = acpa.customer_id
and acp.dunning_letter_set_id is not null
and exists
(
select 'x'
from igi_dun_letter_sets idls
where idls.dunning_letter_set_id =
acp.dunning_letter_set_id
)
;
cursor c_delete_currency ( cp_customer_id in number,
cp_letter_set_id in number) is
select currency_code
from igi_dun_letter_set_cur
where dunning_letter_set_id = cp_letter_set_id
and currency_code not in
(
select currency_code
from ar_customer_profile_amounts
where customer_profile_id
in (
select distinct customer_profile_id
from ar_customer_profiles
where customer_id = cp_customer_id
)
and currency_code is not null
union
select currency_code
from gl_sets_of_books
where set_of_books_id =
( select set_of_books_id
from ar_system_parameters
)
)
;
INSERT INTO igi_dun_letter_set_cur
( dunning_letter_set_id
, currency_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
SELECT
l_letters.dunning_letter_set_id
, l_dc.currency_code
, g_user_id
, g_date
, g_user_id
, g_date
, g_login_id
FROM SYS.DUAL
WHERE NOT EXISTS
( SELECT 'x'
FROM igi_dun_letter_set_cur
WHERE dunning_letter_set_id =
l_letters.dunning_letter_set_id
AND currency_code =
l_dc.currency_code
)
;
INSERT INTO igi_dun_letter_set_cur
( dunning_letter_set_id
, currency_code
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
SELECT
l_prof.dunning_letter_set_id
, l_prof.currency_code
, g_user_id
, g_date
, g_user_id
, g_date
, g_login_id
FROM SYS.DUAL
WHERE NOT EXISTS
( SELECT 'x'
FROM igi_dun_letter_set_cur
WHERE dunning_letter_set_id =
l_prof.dunning_letter_set_id
AND currency_code =
l_prof.currency_code
)
;
WriteToLog('Inserting Currency '||l_prof.currency_code );
select dunning_letter_set_id,
'Y' use_dunning_flag,
'N' charge_per_invoice_flag
from ar_dunning_letter_sets ardls
where not exists ( select 'Already set up'
from igi_dun_letter_sets
where dunning_letter_set_id = ardls.dunning_letter_set_id);
insert into igi_dun_letter_sets (
dunning_letter_set_id,
use_dunning_flag,
charge_per_invoice_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) VALUES (
dlsrec.dunning_letter_set_id,
dlsrec.use_dunning_flag,
dlsrec.charge_per_invoice_flag,
g_user_id,
g_date,
g_user_id,
g_date,
g_login_id
);
PROCEDURE UpdateBlankCustLetters IS
CURSOR C_dlsl IS
SELECT IDLSL.*
FROM igi_dun_letter_Set_lines IDLSL
;
SELECT IDCLSL.rowid row_id , IDCLSL.*
FROM igi_dun_cust_letter_set_lines IDCLSL
WHERE IDCLSL.dunning_letter_set_id = p_dunning_letter_Set_id
AND IDCLSL.dunning_line_num = p_dunning_line_num
AND IDCLSL.dunning_letter_id = p_dunning_letter_id
AND IDCLSL.currency_code = p_currency_code
;
UPDATE igi_dun_cust_letter_set_lines
SET letter_charge_amount = l_dlsl.letter_charge_amount
WHERE ROWID = l_dlscl.row_id
;
UPDATE igi_dun_cust_letter_set_lines
SET invoice_charge_amount = l_dlsl.invoice_charge_amount
WHERE ROWID = l_dlscl.row_id
;
SELECT acp.dunning_letter_set_id, acp.customer_id, acp.site_use_id,
acp.customer_profile_class_id, acp.customer_profile_id
from igi_dun_customer_profile_v acp, igi_dun_cust_prof idcp
where acp.customer_profile_id = idcp.customer_profile_id
and acp.dunning_letter_set_id is not null
;
SELECT idlsc.currency_code
from igi_dun_letter_Set_cur idlsc
where idlsc.dunning_letter_set_id = cp_dunning_letter_set_id
;
SELECT igclsl.dunning_letter_id
, igclsl.dunning_line_num
, igclsl.currency_code
, igclsl.letter_charge_amount
, igclsl.invoice_charge_amount
from igi_dun_letter_set_lines igclsl
where igclsl.dunning_letter_set_id = cp_dunning_letter_set_id
and igclsl.currency_code = cp_currency_code
;
CURSOR c_exists IS select 'x'
from igi_dun_cust_letter_set_lines
where customer_profile_id = cp_customer_profile_id
and dunning_letter_set_id = cp_dunning_letter_set_id
and not exists
( select 'x'
from igi_dun_cust_letter_set_cur cls
where customer_profile_id = cp_customer_profile_id
and not exists
( select currency_code
from igi_dun_letter_Set_cur
where dunning_letter_set_id = cp_dunning_letter_set_id
and currency_code = cls.currency_code
)
)
;
delete from igi_dun_cust_letter_set_lines lines
WHERE
NOT exists ( SELECT 'Y' FROM IGI_DUN_CUSTOMER_PROFILE_V PROF
where LINES.CUSTOMER_ID = PROF.CUSTOMER_ID
and LINES.CUSTOMER_PROFILE_ID = PROF.CUSTOMER_PROFILE_ID
and NVL(LINES.SITE_USE_ID,-1) = NVL(PROF.SITE_USE_ID,-1)
and NVL(LINES.CUSTOMER_PROFILE_CLASS_ID, -1) = NVL(PROF.CUSTOMER_PROFILE_CLASS_ID,-1));
cursor c_delete is
select rowid row_id
from igi_dun_cust_letter_set_lines idclsl
where dunning_letter_set_id = l_profile.dunning_letter_set_id
and customer_profile_id = l_profile.customer_profile_id
and (dunning_letter_set_id, dunning_line_num,
dunning_letter_id, currency_code)
not in (
select dunning_letter_set_id
, dunning_line_num
, dunning_letter_id
, currency_code
from igi_dun_letter_set_lines idlsl
where idlsl.dunning_letter_set_id =
idclsl.dunning_letter_set_id
) ;
for l_rowid in c_delete loop
delete from igi_dun_cust_letter_set_lines
where rowid = l_rowid.row_id
;
cursor c_delete is
select rowid row_id
from igi_dun_cust_letter_set_cur idclsl
where customer_profile_id = l_profile.customer_profile_id
and (customer_profile_id, currency_code)
not in (
select customer_profile_id
, currency_code
from igi_dun_cust_letter_set_lines idlsl
where customer_profile_id = l_profile.customer_profile_id
) ;
for l_rowid in c_delete loop
delete from igi_dun_cust_letter_set_cur
where rowid = l_rowid.row_id
;
insert into igi_dun_cust_letter_set_cur (
customer_profile_id,
currency_code,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login )
select
l_profile.customer_profile_id
, l_currency.currency_code
, g_user_id
, g_date
, g_date
, g_user_id
, g_login_id
from sys.dual
where not exists
( select 'x'
from igi_dun_cust_letter_set_cur
where customer_profile_id = l_profile.customer_profile_id
and currency_code = l_currency.currency_code
)
;
insert into igi_dun_cust_letter_set_lines (
customer_profile_id,
customer_profile_class_id,
customer_id,
site_use_id,
dunning_letter_set_id,
dunning_line_num,
dunning_letter_id,
currency_code,
letter_charge_amount,
invoice_charge_amount,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login
)
select l_profile.customer_profile_id
, l_profile.customer_profile_class_id
, l_profile.customer_id
, l_profile.site_use_id
, l_profile.dunning_letter_set_id
, l_lines.dunning_line_num
, l_lines.dunning_letter_id
, l_lines.currency_code
, l_lines.letter_charge_amount
, l_lines.invoice_charge_amount
, g_user_id
, g_date
, g_date
, g_user_id
, g_login_id
from sys.dual
where not exists
( select 'x'
from igi_dun_cust_letter_set_lines
where customer_profile_id =
l_profile.customer_profile_id
and customer_profile_class_id = l_profile.customer_profile_class_id
and dunning_letter_set_id = l_profile.dunning_letter_set_id
and dunning_line_num = l_lines.dunning_line_num
and currency_code = l_lines.currency_code
)
;
select customer_profile_id,
'Y' use_dunning_flag,
'A' dunning_charge_type
from ar_customer_profiles arcp
where not exists (select 'Already set up'
from igi_dun_cust_prof
where customer_profile_id = arcp.customer_profile_id);
insert into igi_dun_cust_prof(
customer_profile_id,
use_dunning_flag,
dunning_charge_type,
created_by,
creation_date,
last_updated_by,
lasT_update_date,
last_update_login
) SELECT
arcprec.customer_profile_id,
arcprec.use_dunning_flag,
arcprec.dunning_charge_type,
g_user_id,
g_date,
g_user_id,
g_date,
g_login_id
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT 'x'
FROM igi_dun_cust_prof
WHERE customer_profile_id
= arcprec.customer_profile_id
)
;
select ardlsl.dunning_letter_set_id,
dunning_line_num,
dunning_letter_id,
igicur.currency_code,
'Y' use_dunning_flag,
'N' charge_per_invoice_flag
from ar_dunning_letter_set_lines ardlsl,
igi_dun_letter_set_cur igicur
where ardlsl.dunning_letter_set_id = igicur.dunning_letter_set_id
and exists ( select 'Already set up'
from igi_dun_letter_sets
where dunning_letter_set_id = ardlsl.dunning_letter_set_id)
and ( ardlsl.dunning_letter_set_id, ardlsl.dunning_line_num,
ardlsl.dunning_letter_id, igicur.currency_code)
not in ( select dunning_letter_set_id, dunning_line_num,
dunning_letter_id, currency_code
from igi_dun_letter_set_lines )
;
delete from igi_dun_letter_set_lines igi
where (igi.dunning_letter_set_id,
igi.dunning_letter_id,
igi.dunning_line_num ) not in (
Select ar.dunning_letter_set_id,
ar.dunning_letter_id,
ar.dunning_line_num
from ar_dunning_letter_set_lines ar);
delete from igi_dun_cust_letter_set_lines igi
where (igi.dunning_letter_set_id,
igi.dunning_letter_id,
igi.dunning_line_num ) not in (
Select ar.dunning_letter_set_id,
ar.dunning_letter_id,
ar.dunning_line_num
from ar_dunning_letter_set_lines ar);
insert into igi_dun_letter_set_lines (
dunning_letter_set_id,
dunning_line_num,
dunning_letter_id,
currency_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) SELECT
dlsrec.dunning_letter_set_id,
dlsrec.dunning_line_num,
dlsrec.dunning_letter_id,
dlsrec.currency_code,
g_user_id,
g_date,
g_user_id,
g_date,
g_login_id
FROM SYS.DUAL
WHERE NOT EXISTS
( SELECT 'x'
FROM igi_dun_letter_Set_lines
WHERE dunning_letter_set_id = dlsrec.dunning_letter_set_id
AND dunning_line_num = dlsrec.dunning_line_num
AND currency_code = dlsrec.currency_code
);
select name, status, customer_profile_class_id, dunning_letters,
sysdate creation_date, sysdate last_update_date,
-1 last_updated_by, -1 created_by, -1 last_update_login
from ar_customer_profile_classes acpc
where not exists ( select 'Already set up'
from igi_dun_cust_prof_class
where customer_profile_class_id = acpc.customer_profile_class_id
)
;
insert into igi_dun_cust_prof_class ( customer_profile_class_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, dunning_charge_type
, use_dunning_flag
)
values ( l_acp.customer_profile_class_id
, l_acp.creation_date
, l_acp.created_by
, l_acp.last_update_date
, l_acp.last_updated_by
, l_acp.last_update_login
, 'A'
, 'Y'
);
fnd_flex_dsc_api.delete_context( appl_short_name => lv_appl_short_name
, flexfield_name => lv_header_txn_flex
, context => pp_header_txn_context
);
fnd_flex_dsc_api.delete_context( appl_short_name => lv_appl_short_name
, flexfield_name => lv_line_txn_flex
, context => pp_line_txn_context
);
SELECT rpi_header_context_code
, rpi_header_charge_id
, rpi_header_generate_seq
, rpi_line_context_code
, rpi_line_charge_id
, rpi_line_generate_seq
, rpi_line_charge_line_num
, rpi_line_price_break_num
, USERENV('LANG') language
from igi_ar_system_options;
UpdateBlankCustLetters ;