[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select
A.rowid,
A.it_service_line_id,
decode(nvl(A.entered_dr,0),0,A.creation_code_combination_id,A.receiving_code_combination_id),
decode(nvl(A.entered_dr,0),0,A.entered_cr,A.entered_dr) amount,
P1.start_date gl_encumbered_date,
H.it_period_name,
H.currency_code,
P1.period_num,
P1.period_year,
P1.quarter_num,
A.reversal_flag,
A.packet_id,
H.name,
A.description,
A.charge_service_id,
A.status_flag,
A.prevent_encumbrance_flag,
H.it_originator_id
From
igi_itr_charge_headers H,
igi_itr_charge_lines L,
igi_itr_charge_lines_audit A,
gl_period_statuses P1
Where H.it_header_id = p_it_header_id
And L.it_header_id = H.it_header_id
And A.it_header_id = L.it_header_id
And L.it_service_line_id = nvl(p_it_service_line_id, L.it_service_line_id)
And A.it_service_line_id = L.it_service_line_id
And (nvl(A.status_flag,'P') = 'F'
Or (nvl(A.status_flag,'P') = 'L' and A.encumbrance_flag = 'Y' and nvl(A.unencumbered_amount,0) = 0)
Or nvl(A.status_flag,'P') = 'P'
Or nvl(A.status_flag,'P') = 'C'
Or nvl(A.status_flag,'P') = 'U'
Or nvl(A.status_flag,'P') = 'R'
Or nvl(A.status_flag,'P') = 'J'
Or (A.encumbrance_flag = 'Y' and nvl(A.prevent_encumbrance_flag,'N') = 'Y'))
And nvl(A.reversal_flag,'N') = 'N'
And H.set_of_books_id = p_set_of_books_id
And H.it_period_name = P1.period_name
And P1.set_of_books_id = H.set_of_books_id
And NVL(P1.adjustment_period_flag,'N') = 'N'
And P1.application_id = (Select F1.application_id
From fnd_application F1
Where F1.application_short_name = 'SQLGL');
Procedure Itr_Enc_Update(
p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
p_status_flag IN varchar2,
p_prevent_encumbrance_flag IN varchar2,
p_packet_id IN number,
p_fc_result_code IN varchar2,
p_rowid IN varchar2);
Select nvl(use_encumbrance_flag,'N')
Into l_enc_enabled
From igi_itr_charge_setup
Where set_of_books_id = p_set_of_books_id;
Select gl_bc_packets_s.nextval
Into p_packet_id
From sys.dual;
Select je_category_name
From gl_je_categories
Where user_je_category_name = 'Cross Charges';
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');
Bc_Packets_Insert(
l_packet_id,
p_set_of_books_id,
l_ccid,
p_reversal_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_it_originator_id,
'Internal Trading',
l_je_category_name,
'E',
l_period_name,
l_currency_code,
l_status_code, -- 'C'or 'P'
'Y', -- l_reversal_flag,
l_status_flag,
l_prevent_encumbrance_flag,
l_charge_name,--shsaxena for bug 2948237
-- l_description,
l_curr_calling_sequence);
Bc_Packets_Insert(l_packet_id,
p_set_of_books_id,
l_ccid,
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_it_originator_id,
'Internal Trading',
l_je_category_name,
'E',
l_period_name,
l_currency_code,
l_status_code, -- 'C'or 'P'
l_reversal_flag,
l_status_flag,
l_prevent_encumbrance_flag,
l_charge_name, --shsaxena for bug 2948237
-- l_description,
l_curr_calling_sequence);
Update igi_itr_charge_lines
Set failed_funds_lookup_code = 'N',
status_flag = 'N',
encumbrance_flag = 'Y',
encumbered_amount = l_amount,
gl_encumbered_date = l_gl_encumbered_date,
gl_encumbered_period_name = l_period_name,
unencumbered_amount = NULL,
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set failed_funds_lookup_code = 'N',
status_flag = 'N',
encumbrance_flag = 'Y',
encumbered_amount = l_amount,
gl_encumbered_date = l_gl_encumbered_date,
gl_encumbered_period_name = l_period_name,
unencumbered_amount = NULL,
--packet_id = NULL
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id
And rowid = l_rowid;
Update igi_itr_charge_lines_audit
Set unencumbered_amount = p_reversal_amount,
reversal_flag = 'O',
-- obselete so it doesn not get picked up in the cursor select again ,
-- problem in multiple modifications [N]
--packet_id = NULL
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id
And reversal_flag = 'Y';
Update igi_itr_charge_lines
Set unencumbered_amount = l_amount * -1,
encumbrance_flag = 'N',
gl_cancelled_date = l_gl_encumbered_date,
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set unencumbered_amount = l_amount * -1,
encumbrance_flag = 'N',
gl_cancelled_date = l_gl_encumbered_date,
--packet_id = NULL
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id
And rowid = l_rowid;
Update igi_itr_charge_lines
Set failed_funds_lookup_code = 'N',
status_flag = 'U',
prevent_encumbrance_flag = 'N',
unencumbered_amount = l_amount,
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set failed_funds_lookup_code = 'N',
prevent_encumbrance_flag = 'N',
status_flag = 'U',
unencumbered_amount = l_amount,
--packet_id = NULL
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id
And rowid = l_rowid;
Update igi_itr_charge_lines
Set failed_funds_lookup_code = 'N',
status_flag = 'N',
encumbrance_flag = 'Y',
encumbered_amount = l_amount,
gl_encumbered_date = l_gl_encumbered_date,
gl_encumbered_period_name = l_period_name,
unencumbered_amount = NULL,
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set failed_funds_lookup_code = 'N',
status_flag = 'N',
encumbrance_flag = 'Y',
encumbered_amount = l_amount,
gl_encumbered_date = l_gl_encumbered_date,
gl_encumbered_period_name = l_period_name,
unencumbered_amount = NULL,
--packet_id = NULL
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id
And rowid = l_rowid;
Update igi_itr_charge_lines
Set status_flag = 'C',
failed_funds_lookup_code = 'N',
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id;
Update igi_itr_charge_lines_audit
Set status_flag = 'C',
failed_funds_lookup_code = 'N',
--packet_id = NULL
packet_id = l_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = l_it_service_line_id
And rowid = l_rowid;
Select
nvl(gls.chart_of_accounts_id, -1),
nvl(igi.encumbrance_type_id, -1)
Into
p_chart_of_accounts_id,
p_itr_enc_type_id
From
igi_itr_charge_setup igi,
gl_sets_of_books gls
Where gls.set_of_books_id = p_set_of_books_id
And igi.set_of_books_id(+) = gls.set_of_books_id;
Procedure Bc_Packets_Insert(
p_packet_id IN gl_bc_packets.packet_id%type,
p_set_of_books_id IN gl_bc_packets.ledger_id%type,
p_ccid IN gl_bc_packets.code_combination_id%type,
p_amount IN gl_bc_packets.entered_dr%type,
p_period_year IN gl_bc_packets.period_year%type,
p_period_num IN gl_bc_packets.period_num%type,
p_quarter_num IN gl_bc_packets.quarter_num%type,
p_gl_user IN gl_bc_packets.last_updated_by%type,
p_enc_type_id IN gl_bc_packets.encumbrance_type_id%type,
p_ref2 IN gl_bc_packets.reference2%type,
p_ref4 IN gl_bc_packets.reference4%type,
p_ref5 IN gl_bc_packets.reference5%type,
p_je_source IN gl_bc_packets.je_source_name%type,
p_je_category IN gl_bc_packets.je_category_name%type,
p_actual_flag IN gl_bc_packets.actual_flag%type,
p_period_name IN gl_bc_packets.period_name%type,
p_base_currency_code IN gl_bc_packets.currency_code%type,
p_status_code IN gl_bc_packets.status_code%type,
p_reversal_flag IN igi_itr_charge_lines_audit.reversal_flag%type,
p_status_flag IN igi_itr_charge_lines.status_flag%type,
p_prevent_encumbrance_flag IN igi_itr_charge_lines.prevent_encumbrance_flag%type,
p_charge_name IN igi_itr_charge_headers.name%type, --shsaxena for bug 2948237
--p_description IN varchar2,
p_calling_sequence IN varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION;
l_debug_loc varchar2(30) := 'Bc_Packets_Insert';
l_debug_info := 'Inserting record into gl_bc_packets';
/* Start of changes for bug#6028574 to insert into manadatory columns of gl_bc_packets introduced in r12. */
BEGIN
SELECT s.audsid, s.serial# into l_session_id, l_serial_id
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.audsid = USERENV('SESSIONID');
Insert Into gl_bc_packets (
packet_id, ledger_id, je_source_name,
je_category_name, code_combination_id, actual_flag,
period_name, period_year, period_num,
quarter_num, currency_code, status_code,
last_update_date, last_updated_by, encumbrance_type_id,
entered_dr, entered_cr, accounted_dr,
accounted_cr, reference2, reference4,
reference5, je_line_description, session_id,
serial_id, application_id)
Values(
p_packet_id, p_set_of_books_id, p_je_source,
p_je_category, p_ccid, p_actual_flag,
p_period_name, p_period_year, p_period_num,
p_quarter_num, p_base_currency_code, p_status_code,
sysdate, p_gl_user, p_enc_type_id,
l_ins_dr, l_ins_cr, l_ins_dr,
l_ins_cr, p_ref2, p_ref4,
p_ref5, p_charge_name, l_session_id,
l_serial_id, 101); --shsaxena for bug 2948237 -- p_description);
FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.BC_Packets_Insert.msg6',TRUE);
End BC_Packets_Insert;
Itr_Enc_Update(
p_it_header_id,
p_it_service_line_id,
p_status_flag,
p_prevent_encumbrance_flag,
p_packet_id,
l_fc_result_code,
p_rowid);
Procedure Itr_Enc_Update(
p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
p_status_flag IN varchar2,
p_prevent_encumbrance_flag IN varchar2,
p_packet_id IN number,
p_fc_result_code IN varchar2,
p_rowid IN varchar2) IS
l_debug_loc varchar2(30) := 'Itr_Enc_Update';
Update igi_itr_charge_lines
Set failed_funds_lookup_code = 'Y',
packet_id = p_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = p_it_service_line_id;
Update igi_itr_charge_lines_audit
Set failed_funds_lookup_code = 'Y',
packet_id = p_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = p_it_service_line_id
And rowid = p_rowid;
Update igi_itr_charge_lines
Set failed_funds_lookup_code = 'Y',
status_flag = 'F',
packet_id = p_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = p_it_service_line_id;
Update igi_itr_charge_lines_audit
Set failed_funds_lookup_code = 'Y',
status_flag = 'F',
packet_id = p_packet_id
Where it_header_id = p_it_header_id
And it_service_line_id = p_it_service_line_id
And rowid = p_rowid;
FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Itr_Enc_Update.msg8',TRUE);
End Itr_Enc_Update;
Select l.lookup_code
Into p_fc_result_code
From gl_lookups l
Where lookup_type = 'FUNDS_CHECK_RESULT_CODE'
And Exists (Select 'x'
From gl_bc_packets bc
Where result_code like 'F%'
And bc.result_code = l.lookup_code
And packet_id = p_packet_id)
And rownum = 1;