declare
TYPE refcursor IS REF CURSOR;
v_cursor REFCURSOR;
v_table_name varchar2(50) := 'jm_adjust_journal_tmp';
v_cur jm_adjust_journal_tmp%rowtype;
v_journal_name VARCHAR2(100);
v_journal_category VARCHAR2(25);
v_company_code VARCHAR2(15);
v_period_name VARCHAR2(15);
v_currency VARCHAR2(15);
v_batch_description VARCHAR2(240);
Begin
open v_cursor for 'select gt1.journal_name,
gt1.journal_category,
gt1.company_code,
gt1.period_name,
gt1.currency,
gt1.batch_description
from ' || v_table_name || ' gt1
WHERE gt1.application_id = 6144
AND gt1.conversion_type IS NOT NULL
GROUP BY gt1.journal_name,
gt1.journal_category,
gt1.company_code,
gt1.period_name,
gt1.currency,
gt1.batch_description
HAVING COUNT(DISTINCT GT1.CONVERSION_TYPE) > 1';
loop
fetch v_cursor
into v_journal_name,
v_journal_category,
v_company_code,
v_period_name,
v_currency,
v_batch_description;
exit when v_cursor%notfound;
EXECUTE IMMEDIATE 'UPDATE ' || v_table_name ||
' gt set gt.err_msg = gt.err_msg ''The conversion type should be same under the same journal name;''' ||
' WHERE gt.application_id = 6144' ||
' AND gt.conversion_type IS NOT NULL' ||
' AND gt.journal_name = v_journal_name' ||
' AND gt.journal_category = v_journal_category' ||
' AND gt.company_code = v_company_code' ||
' AND gt.period_name = v_period_name' ||
' AND gt.currency = v_currency' ||
' AND gt.batch_description = v_batch_description;';
end loop;
End;
TYPE refcursor IS REF CURSOR;
v_cursor REFCURSOR;
v_table_name varchar2(50) := 'jm_adjust_journal_tmp';
v_cur jm_adjust_journal_tmp%rowtype;
v_journal_name VARCHAR2(100);
v_journal_category VARCHAR2(25);
v_company_code VARCHAR2(15);
v_period_name VARCHAR2(15);
v_currency VARCHAR2(15);
v_batch_description VARCHAR2(240);
Begin
open v_cursor for 'select gt1.journal_name,
gt1.journal_category,
gt1.company_code,
gt1.period_name,
gt1.currency,
gt1.batch_description
from ' || v_table_name || ' gt1
WHERE gt1.application_id = 6144
AND gt1.conversion_type IS NOT NULL
GROUP BY gt1.journal_name,
gt1.journal_category,
gt1.company_code,
gt1.period_name,
gt1.currency,
gt1.batch_description
HAVING COUNT(DISTINCT GT1.CONVERSION_TYPE) > 1';
loop
fetch v_cursor
into v_journal_name,
v_journal_category,
v_company_code,
v_period_name,
v_currency,
v_batch_description;
exit when v_cursor%notfound;
EXECUTE IMMEDIATE 'UPDATE ' || v_table_name ||
' gt set gt.err_msg = gt.err_msg ''The conversion type should be same under the same journal name;''' ||
' WHERE gt.application_id = 6144' ||
' AND gt.conversion_type IS NOT NULL' ||
' AND gt.journal_name = v_journal_name' ||
' AND gt.journal_category = v_journal_category' ||
' AND gt.company_code = v_company_code' ||
' AND gt.period_name = v_period_name' ||
' AND gt.currency = v_currency' ||
' AND gt.batch_description = v_batch_description;';
end loop;
End;