[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_stmt varchar2(8096);
select substr(userenv('LANG'),1,4)
into userenv_lang
from dual;
select language_code
into base_lang
from fnd_languages
where installed_flag = 'B';
select_stmt :=
' select distinct(nvl(rtrim(substr(a_bill_loc.language,1,4)), ''' || base_lang || ''')) language ' || cr ||
build_from_clause;
sql_stmt := select_stmt || cr || p_where1;
sql_stmt := sql_stmt || cr || ' UNION ' || cr || select_stmt || cr || p_where2 ;
insert_stmt varchar2(240);
select_stmt varchar2(8096);
inserted_row_counts INTEGER;
select substr(userenv('LANG'),1,4)
into userenv_lang
from dual;
select language_code
into base_lang
from fnd_languages
where installed_flag = 'B';
insert_stmt := ' insert into ar_bpa_print_requests (request_id, payment_schedule_id,
worker_id, created_by, creation_date,last_updated_by, last_update_date, customer_trx_id)';
select_stmt := ' select ' || m_request_id || ', payment_schedule_id, rownum, 1, sysdate, 1, sysdate, customer_trx_id from '
|| cr ||' ( select ps.payment_schedule_id, trx.customer_trx_id '|| cr || build_from_clause ||
' AND nvl(a_bill_loc.language,' || '''' || base_lang || ''') = ' || '''' || userenv_lang || '''' ;
sql_stmt := insert_stmt || cr || select_stmt || cr || p_where1 || ')';
sql_stmt := sql_stmt || cr || ' UNION ALL ' || cr || select_stmt || cr || p_where2 || ')';
inserted_row_counts := dbms_sql.execute(sql_stmt_c);
fnd_file.put_line( fnd_file.log, 'inserted row count: ' || inserted_row_counts);
IF inserted_row_counts > 0 THEN
divided_worker_counts := ceil(inserted_row_counts/l_job_size);
row_counts_perworker := ceil(inserted_row_counts/divided_worker_counts);
UPDATE ar_bpa_print_requests
SET worker_id = l_worker_id
WHERE request_id = m_request_id
AND worker_id BETWEEN l_low_range AND l_high_range;
request_data => to_char(inserted_row_counts));
SELECT request_id, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = p_request_id;
DELETE FROM ar_bpa_print_requests
WHERE request_id = m_request_id;
select language_code from fnd_languages_vl
where installed_flag in ('I', 'B');
lang_selector VARCHAR2(8096);
select_stmt VARCHAR2(8096);
select_cur INTEGER;
inserted_row_counts INTEGER;
SELECT language_code
INTO base_lang
FROM fnd_languages
WHERE installed_flag = 'B';
lang_selector := ' select distinct(nvl(rtrim(substr(a_bill_loc.language,1,4)), '''
|| base_lang || ''')) language ' || cr || build_from_clause
|| ' AND trx.customer_trx_id in ('|| p_id_list || ' )' ;
lang_selector := ' select distinct(nvl(rtrim(substr(a_bill_loc.language,1,4)), '''
|| base_lang || ''')) language ' || cr || build_from_clause
|| ' AND ps.payment_schedule_id in ('|| p_id_list || ' )' ;
OPEN lang_cv FOR lang_selector;
SELECT nls_language, nls_territory
INTO nls_lang, nls_terr
FROM FND_LANGUAGES
WHERE language_code = lang_code;
select_stmt := ' SELECT ps.payment_schedule_id ' || cr || build_from_clause || cr ||
' AND trx.customer_trx_id in ( ' || p_id_list || ' ) ' || cr ||
' AND nvl(a_bill_loc.language, ''' || base_lang ||''' ) = :lang_code ' || cr ||
' ORDER BY ps.trx_number ' ;
select_stmt := ' SELECT ps.payment_schedule_id ' || cr || build_from_clause || cr ||
' AND ps.payment_schedule_id in ('|| p_id_list || ' ) ' || cr ||
' AND nvl(a_bill_loc.language, ''' || base_lang ||''' ) = :lang_code ' || cr ||
' ORDER BY ps.trx_number ' ;
select_cur := dbms_sql.open_cursor;
dbms_sql.parse( select_cur, select_stmt, dbms_sql.native );
dbms_sql.bind_variable(select_cur,':lang_code', lang_code );
dbms_sql.define_array(select_cur,1,ps_id,500,1 );
ignore := dbms_sql.execute(select_cur);
fetched_row_count := dbms_sql.fetch_rows(select_cur);
dbms_sql.column_value(select_cur,1,ps_id);
dbms_sql.close_cursor(select_cur);
inserted_row_counts := ps_id.COUNT ;
divided_worker_counts := ceil(inserted_row_counts/500);
row_counts_perworker := ceil(inserted_row_counts/divided_worker_counts);
INSERT INTO ar_bpa_print_requests ( request_id,
payment_schedule_id,
worker_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (l_request_id,
ps_id(i),
no_of_workers ,
1,
sysdate,
1,
sysdate);
IF dbms_sql.is_open(select_cur) THEN
dbms_sql.close_cursor(select_cur);
SELECT fnd_concurrent_requests_s.nextval
INTO l_request_id
FROM dual;
INSERT INTO ar_bpa_print_requests (request_id,
payment_schedule_id,
worker_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
(SELECT l_request_id ,
ps.payment_schedule_id,
1,
1,
sysdate,
1,
sysdate
FROM ra_customer_trx ct ,
ar_payment_schedules ps
WHERE ct.customer_Trx_id = ps_id(i)
AND ps.customer_Trx_id = ct.customer_Trx_id);
INSERT INTO ar_bpa_print_requests (request_id,
payment_schedule_id,
worker_id,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES( l_request_id ,
ps_id(i),
1,
1,
sysdate,
1,
sysdate);