[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT GL_JOURNAL_IMPORT_S.nextval
, sp.set_of_books_id
, GL_INTERFACE_CONTROL_S.nextval
, 'S'
INTO p_glint_control.interface_run_id
, p_glint_control.set_of_books_id
, p_glint_control.group_id
, p_glint_control.status
FROM ap_system_parameters sp
;
select je_source_name
into p_glint_control.je_source_name
from igi_mpp_setup
;
PROCEDURE InsertControlRec ( p_glint_control in GLINT_CONTROL )
IS
BEGIN
INSERT INTO gl_interface_control
( je_source_name
, status
, interface_run_id
, group_id
, set_of_books_id)
VALUES
( p_glint_control.je_source_name
, p_glint_control.status
, p_glint_control.interface_run_id
, p_glint_control.group_id
, p_glint_control.set_of_books_id
);
PROCEDURE InsertInterfaceRec ( l_glint IN GLINT ) IS
BEGIN
--bug 3199481 fnd logging changes: sdixit: start block
IF (l_state_level >= l_debug_level ) THEN
FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
'>> >> **** Accounting date '||l_glint.accounting_date);
INSERT INTO GL_INTERFACE
(
status -- not null
,set_of_books_id -- not null
,accounting_date -- not null
,currency_code -- not null
,date_created -- not null
,created_by -- not null
,actual_flag -- not null
,user_je_category_name -- not null
,user_je_source_name -- not null
,currency_conversion_date
,encumbrance_type_id
,budget_version_id
,user_currency_conversion_type
,currency_conversion_rate
,entered_dr
,entered_cr
,accounted_dr
,accounted_cr
,transaction_date
,reference1
,reference2
,reference3
,reference4
,period_name
,chart_of_accounts_id
,functional_currency_code
,code_combination_id
,group_id
) VALUES
(
'NEW' -- not null
,l_glint.set_of_books_id -- not null
,l_glint.accounting_date -- not null
,l_glint.currency_code -- not null
,l_glint.date_created -- not null
,l_glint.created_by -- not null
,l_glint.actual_flag -- not null
,l_glint.user_je_category_name -- not null
,l_glint.user_je_source_name -- not null
,l_glint.currency_conversion_date
,l_glint.encumbrance_type_id
,l_glint.budget_version_id
,l_glint.user_currency_conversion_type
,l_glint.currency_conversion_rate
,l_glint.entered_dr
,l_glint.entered_cr
,l_glint.accounted_dr
,l_glint.accounted_cr
,l_glint.transaction_date
,l_glint.reference1
,l_glint.reference2
,l_glint.reference3
,l_glint.reference4
,l_glint.period_name
,l_glint.chart_of_accounts_id
,l_glint.functional_currency_code
,l_glint.code_combination_id
,l_glint.group_id
)
;
SELECT slgr.*
FROM igi_mpp_subledger slgr
WHERE slgr.period_name = p_period_name
AND slgr.set_of_books_id = p_sob_id
AND slgr.expense_recognized_flag = 'Y'
AND slgr.gl_posted_flag = 'N'
;
SELECT user_je_source_name
INTO l_user_je_source_name
FROM gl_je_sources
WHERE je_source_name = p_slgr.je_source_name
;
SELECT user_je_category_name
INTO l_user_je_category_name
FROM gl_je_categories
WHERE je_category_name = p_slgr.je_category_name
;
SELECT p_slgr.invoice_id
, p_slgr.distribution_line_number
, p_slgr.subledger_entry_id
, p_slgr.currency_code
, p_slgr.actual_flag
, l_user_je_source_name
, l_user_je_category_name
, p_slgr.set_of_books_id
, p_slgr.gl_date
, p_slgr.code_combination_id
, p_slgr.accounted_dr
, p_slgr.accounted_cr
, p_slgr.entered_dr
, p_slgr.entered_cr
, p_slgr.currency_conversion_date
, p_slgr.user_currency_conversion_type
, p_slgr.currency_conversion_rate
, p_slgr.period_name
, p_slgr.chart_of_accounts_id
, p_slgr.functional_currency_code
, p_slgr.reference1
, p_slgr.reference2
, p_slgr.reference3
, g_date
, g_user_id
INTO
l_glint.reference4
, l_glint.reference5
, l_glint.reference6
, l_glint.currency_code
, l_glint.actual_flag
, l_glint.user_je_source_name
, l_glint.user_je_category_name
, l_glint.set_of_books_id
, l_glint.accounting_date
, l_glint.code_combination_id
, l_glint.accounted_dr
, l_glint.accounted_cr
, l_glint.entered_dr
, l_glint.entered_cr
, l_glint.currency_conversion_date
, l_glint.user_currency_conversion_type
, l_glint.currency_conversion_rate
, l_glint.period_name
, l_glint.chart_of_accounts_id
, l_glint.functional_currency_code
, l_glint.reference1
, l_glint.reference2
, l_glint.reference3
, l_glint.date_created
, l_glint.created_by
FROM SYS.DUAL
;
InsertInterfaceRec ( l_glint ) ;
FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
'>> >> >> Built the interface info... ' );
UPDATE igi_mpp_subledger
SET gl_posted_flag = 'Y'
, date_created_in_gl = g_date
WHERE subledger_entry_id = l_slgr.subledger_entry_id
AND nvl(gl_posted_flag,'N') = 'N'
AND expense_recognized_flag = 'Y'
;
UPDATE igi_mpp_ap_invoice_dists_det
SET gl_posted_flag = 'Y'
, gl_posted_date = g_date
WHERE invoice_id = l_slgr.invoice_id
AND distribution_line_number =
l_slgr.distribution_line_number
AND period_name = l_slgr.period_name
AND NVL(gl_posted_flag,'N') = 'N'
AND expense_recognized_flag = 'Y'
and EXISTS
( SELECT 'x'
FROM igi_mpp_subledger
WHERE gl_posted_flag = 'Y'
AND expense_recognized_flag = 'Y'
AND invoice_id = l_slgr.invoice_id
AND distribution_line_number =
l_slgr.distribution_line_number
AND period_name = l_slgr.period_name
)
;
FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
'>> >> >> Marked as posted... ' );
SELECT start_date
from gl_period_statuses
where set_of_books_id = p_set_of_books_id
and application_id = 200
and adjustment_period_flag = 'N'
and effective_period_num = p_start_period_eff_num
;
SELECT end_date
from gl_period_statuses
where set_of_books_id = p_set_of_books_id
and application_id = 200
and adjustment_period_flag = 'N'
and effective_period_num = p_end_period_eff_num
;
SELECT period_name, set_of_books_id
from gl_period_statuses
where set_of_books_id = p_set_of_books_id
and application_id = 200
and adjustment_period_flag = 'N'
and effective_period_num between p_start_period_eff_num and
p_end_period_eff_num
order by effective_period_num
;
SELECT DISTINCT currency_code
FROM igi_mpp_subledger
WHERE period_name = cp_period_name
and set_of_books_id = cp_sob_id
;
SELECT currency_code
, SUM( nvl( accounted_dr, 0) ) sum_accounted_dr
, SUM( nvl( accounted_cr, 0) ) sum_accounted_cr
, SUM( nvl( entered_dr, 0) ) sum_entered_dr
, SUM( nvl( entered_cr, 0) ) sum_entered_cr
FROM igi_mpp_subledger
WHERE period_name = cp_period_name
AND expense_recognized_flag = 'Y'
AND gl_posted_flag = 'N'
AND seT_of_books_id = cp_sob_id
AND currency_code = cp_currency_code
GROUP BY currency_code
;
InsertControlRec ( l_glint_control ) ;
FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
'>> >> Totals unbalanced for Currency '|| l_currency.currency_code );
FND_LOG.STRING (l_state_level , 'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',
'END (Normal) Transfer to GL.');
FND_LOG.MESSAGE ( l_unexp_level,'igi.pls.igipmgtb.IGIPMGLT.InsertInterfaceRec',TRUE);