The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select
period_type,
period_year,
period_num
From gl_period_statuses
Where period_name = p_period_name
And set_of_books_id = p_set_of_books_id
And nvl(adjustment_period_flag,'N') = 'N'
And application_id = (Select application_id
From fnd_application
Where application_short_name = 'SQLGL');
Select user_je_category_name
From gl_je_categories
Where je_category_name = 'IGIITRCC';
Select user_je_source_name
From gl_je_sources
Where je_source_name = 'Internal Trading';
select
h.it_header_id,
l.it_service_line_id,
l.it_line_num,
decode(nvl(l.entered_dr,0),0,l.creation_code_combination_id,l.receiving_code_combination_id) ccid_dr,
decode(nvl(l.entered_dr,0),0,l.entered_cr,l.entered_dr) amount,
decode(nvl(l.entered_cr,0),0,l.creation_code_combination_id,l.receiving_code_combination_id) ccid_cr,
l.encumbrance_flag,
l.encumbered_amount,
l.unencumbered_amount,
p1.start_date gl_encumbered_date,
h.it_period_name,
h.currency_code,
p1.period_num,
p1.period_year,
p1.quarter_num,
h.name, --shsaxena for bug 2948237
l.description,
l.charge_service_id,
h.it_originator_id,
ssv.name
from
igi_itr_charge_headers h,
igi_itr_charge_lines l,
igi_itr_charge_service_ss_v ssv,
gl_period_statuses p1
where p1.period_type = p_period_type
and p1.set_of_books_id = p_set_of_books_id
and nvl(p1.adjustment_period_flag,'N') = 'N'
and p1.application_id = (select application_id
from fnd_application
where application_short_name = 'SQLGL')
and (p1.period_year >= p_start_period_year
and p1.period_year <= p_end_period_year)
and (p1.period_num >= p_start_period_num
and p1.period_num <= p_end_period_num)
and h.it_period_name = p1.period_name
and h.set_of_books_id = p1.set_of_books_id
and l.it_header_id = h.it_header_id
and l.status_flag = 'A'
and nvl(l.posting_flag,'N') = 'N'
and l.charge_service_id = ssv.charge_service_id;
Select
period_name,
period_num,
period_year,
quarter_num
From gl_period_statuses
Where trunc(sysdate) Between trunc(start_date) And trunc(end_date)
And set_of_books_id = p_set_of_books_id
And NVL(adjustment_period_flag,'N') = 'N'
And application_id = (Select application_id
From fnd_application
Where application_short_name = 'SQLGL');
Procedure Gl_Interface_Insert(
p_status IN gl_interface.status%type,
p_set_of_books_id IN gl_interface.set_of_books_id%type,
p_accounting_date IN gl_interface.accounting_date%type,
p_currency_code IN gl_interface.currency_code%type,
p_date_created IN gl_interface.date_created%type,
p_created_by IN gl_interface.created_by%type,
p_actual_flag IN gl_interface.actual_flag%type,
p_user_je_category_name IN gl_interface.user_je_category_name%type,
p_user_je_source_name IN gl_interface.user_je_source_name%type,
p_entered_dr IN gl_interface.entered_dr%type,
p_entered_cr IN gl_interface.entered_cr%type,
p_accounted_dr IN gl_interface.accounted_dr%type,
p_accounted_cr IN gl_interface.accounted_cr%type,
p_transaction_date IN gl_interface.transaction_date%type,
p_reference1 IN gl_interface.reference1%type,
p_reference4 IN gl_interface.reference4%type,
p_reference6 IN gl_interface.reference6%type,
p_reference10 IN gl_interface.reference10%type,
p_reference21 IN gl_interface.reference21%type,
p_reference22 IN gl_interface.reference22%type,
p_period_name IN gl_interface.period_name%type,
p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%type,
p_functional_currency_code IN gl_interface.functional_currency_code%type,
p_code_combination_id IN gl_interface.code_combination_id%type,
p_group_id IN gl_interface.group_id%type);
PROCEDURE Insert_Control_Rec(
p_int_control in glcontrol );
IGI_ITR_FUNDS_CONTROL_PKG.Bc_Packets_Insert(
l_packet_id,
p_set_of_books_id,
l_ccid_dr,
l_amount,
l_period_year,
l_period_num,
l_quarter_num,
l_gl_user_id,
l_itr_enc_type_id,
l_it_service_line_id,
l_charge_service_id,
l_originator_id,
'Internal Trading',
'IGIITRCC',
'E',
l_period_name,
l_currency_code,
l_status_code, -- 'C'or 'P'
'Y', --l_reversal_flag,
'R', -- l_status_flag,
'Y', --l_prevent_encumbrance_flag,
l_charge_name,--shsaxena for bug 2948237
-- l_description,
l_curr_calling_sequence);
Update igi_itr_charge_lines_audit
Set packet_id = l_packet_id
Where it_header_id = l_it_header_id
And it_service_line_id = l_it_service_line_id
And reversal_flag = 'N';
Update igi_itr_charge_lines
Set unencumbered_amount = l_amount,
packet_id = l_packet_id
Where it_header_id = l_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set unencumbered_amount = l_amount,
packet_id = l_packet_id
Where it_header_id = l_it_header_id
And it_service_line_id = l_it_service_line_id
And reversal_flag = 'N';
Gl_interface_insert(
'NEW',
p_set_of_books_id,
l_gl_encumbered_date,
l_currency_code,
sysdate,
l_gl_user_id,
'A',
l_je_category_name,
l_je_source_name,
l_amount,
NULL,
l_amount,
NULL,
l_gl_encumbered_date,
l_je_category_name, -- reference1
l_charge_name, -- reference4 shsaxena for bug 2948237
--l_description, -- reference4
l_je_source_name, -- reference6
l_reference_10, -- reference10 shsaxena for bug 2948237
-- l_description || ' ' || l_it_line_num || ' ' || l_service_name, -- reference10
l_it_header_id, -- reference21
l_it_service_line_id, -- reference22
l_period_name,
l_chart_of_accounts_id,
l_currency_code,
l_ccid_dr,
null );
Gl_Interface_Insert(
'NEW',
p_set_of_books_id,
l_gl_encumbered_date,
l_currency_code,
sysdate,
l_gl_user_id,
'A',
l_je_category_name,
l_je_source_name,
NULL,
l_amount,
NULL,
l_amount,
l_gl_encumbered_date,
l_je_category_name, -- reference1
l_charge_name, --reference4 shsaxena for bug 2948237
--l_description, -- reference4
l_je_source_name, -- reference6
l_reference_10, -- reference10 --shsaxena for bug 2948237
-- l_description || ' ' || l_it_line_num || ' ' ||l_service_name, -- reference10
l_it_header_id, -- reference21
l_it_service_line_id, -- reference22
l_period_name,
l_chart_of_accounts_id,
l_currency_code,
l_ccid_cr,
null);
Update igi_itr_charge_lines
Set posting_flag = 'Y'
Where it_header_id = l_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set posting_flag = 'Y'
Where it_header_id = l_it_header_id
And reversal_flag = 'N';
Gl_interface_insert(
'NEW',
p_set_of_books_id,
l_gl_encumbered_date,
l_currency_code,
sysdate,
l_gl_user_id,
'A',
l_je_category_name,
l_je_source_name,
l_amount,
NULL,
l_amount,
NULL,
l_gl_encumbered_date,
l_je_category_name, -- reference1
l_charge_name, --reference4 shsaxena for bug 2948237
--l_description, -- reference4
l_je_source_name, -- reference6
l_reference_10, -- reference 10 --shsaxena for bug 2948237
-- l_description || ' ' || l_it_line_num || ' ' || l_service_name, -- reference10
l_it_header_id, -- reference21
l_it_service_line_id, -- reference22
l_period_name,
l_chart_of_accounts_id,
l_currency_code,
l_ccid_dr,
null );
Gl_Interface_Insert(
'NEW',
p_set_of_books_id,
l_gl_encumbered_date,
l_currency_code,
sysdate,
l_gl_user_id,
'A',
l_je_category_name,
l_je_source_name,
NULL,
l_amount,
NULL,
l_amount,
l_gl_encumbered_date,
l_je_category_name, -- reference1
l_charge_name, --reference4 shsaxena for bug 2948237
--l_description, -- reference4
l_je_source_name, -- reference6
l_reference_10, --shsaxena for bug 2948237
--l_description || ' ' || l_it_line_num || ' ' ||l_service_name, -- reference10
l_it_header_id, -- reference21
l_it_service_line_id, -- reference22
l_period_name,
l_chart_of_accounts_id,
l_currency_code,
l_ccid_cr,
null);
Update igi_itr_charge_lines
Set posting_flag = 'Y'
Where it_header_id = l_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set posting_flag = 'Y'
Where it_header_id = l_it_header_id
And it_service_line_id = l_it_service_line_id
And reversal_flag = 'N';
Insert_Control_Rec(l_int_control);
Select gl_journal_import_s.Nextval,
p_set_of_books_id,
NULL, -- Narayanan said comment it (GL_INTERFACE_CONTROL_S.nextval,)
'S',
'Internal Trading'
Into
p_int_control.interface_run_id,
p_int_control.set_of_books_id,
p_int_control.group_id,
p_int_control.status,
p_int_control.je_source_name
From sys.dual ;
PROCEDURE Insert_Control_Rec(
p_int_control in glcontrol) IS
l_debug_loc varchar2(30) := 'Insert_Control_Rec';
l_debug_info := 'Inserting into gl_interface_control';
Insert Into gl_interface_control(
je_source_name,
status,
interface_run_id,
group_id,
set_of_books_id)
Values(
p_int_control.je_source_name,
p_int_control.status,
p_int_control.interface_run_id,
p_int_control.group_id,
p_int_control.set_of_books_id);
FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrpb.IGI_ITR_GL_INTERFACE_PKG.Insert_Control_Rec.msg3',TRUE);
End Insert_Control_Rec;
Procedure Gl_Interface_Insert(
p_status IN gl_interface.status%type,
p_set_of_books_id IN gl_interface.set_of_books_id%type,
p_accounting_date IN gl_interface.accounting_date%type,
p_currency_code IN gl_interface.currency_code%type,
p_date_created IN gl_interface.date_created%type,
p_created_by IN gl_interface.created_by%type,
p_actual_flag IN gl_interface.actual_flag%type,
p_user_je_category_name IN gl_interface.user_je_category_name%type,
p_user_je_source_name IN gl_interface.user_je_source_name%type,
p_entered_dr IN gl_interface.entered_dr%type,
p_entered_cr IN gl_interface.entered_cr%type,
p_accounted_dr IN gl_interface.accounted_dr%type,
p_accounted_cr IN gl_interface.accounted_cr%type,
p_transaction_date IN gl_interface.transaction_date%type,
p_reference1 IN gl_interface.reference1%type,
p_reference4 IN gl_interface.reference4%type,
p_reference6 IN gl_interface.reference6%type,
p_reference10 IN gl_interface.reference10%type,
p_reference21 IN gl_interface.reference21%type,
p_reference22 IN gl_interface.reference22%type,
p_period_name IN gl_interface.period_name%type,
p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%type,
p_functional_currency_code IN gl_interface.functional_currency_code%type,
p_code_combination_id IN gl_interface.code_combination_id%type,
p_group_id IN gl_interface.group_id%type) IS
l_debug_loc varchar2(30) := 'GL_interface';
l_debug_info := 'Inserting record into gl_interface';
Insert Into gl_interface(
status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
transaction_date,
reference1,
reference4,
reference6,
reference10,
reference21,
reference22,
period_name,
chart_of_accounts_id,
functional_currency_code,
code_combination_id,
group_id)
Values(
p_status,
p_set_of_books_id,
p_accounting_date,
p_currency_code,
p_date_created,
p_created_by,
p_actual_flag,
p_user_je_category_name,
p_user_je_source_name,
p_entered_dr,
p_entered_cr,
p_accounted_dr,
p_accounted_cr,
p_transaction_date,
p_reference1,
p_reference4,
p_reference6,
p_reference10,
p_reference21,
p_reference22,
p_period_name,
p_chart_of_accounts_id,
p_currency_code,
p_code_combination_id,
p_group_id );
FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrpb.IGI_ITR_GL_INTERFACE_PKG.Gl_Interface_Insert.msg4',TRUE);
End Gl_Interface_Insert;