[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dist.invoice_id, dist.set_of_books_id, dist.period_name, dist.amount
, dist.distribution_line_number, dist.dist_code_combination_id
, dist.description, dist.accounting_date, gsob.chart_of_accounts_id
, dist.base_amount, dist.exchange_rate, dist.exchange_date,
dist.exchange_rate_type, gsob.currency_code functional_currency_code
FROM ap_invoice_distributions dist
, gl_sets_of_books gsob
WHERE dist.distribution_line_number = cp_distribution_line_number
AND dist.set_of_books_id = gsob.set_of_books_id
AND dist.invoice_id = cp_invoice_id
;
SELECT ext_dist.*
FROM igi_mpp_ap_invoice_dists_det ext_dist
WHERE ext_dist.distribution_line_number = cp_distribution_line_number
AND ext_dist.invoice_id = cp_invoice_id
ORDER BY ext_dist.mpp_dist_line_number
;
SELECT invoice_currency_code
from ap_invoices
where invoice_id = fp_invoice_id
;
SELECT count('x') ct
FROM igi_mpp_subledger
WHERE invoice_id = p_invoice_id
AND distribution_line_number = p_distribution_line_number
AND reference1 = '0'
;
SELECT 'x'
FROM igi_mpp_subledger
WHERE invoice_id = p_invoice_id
AND distribution_line_number = p_distribution_line_number
AND ( nvl(expense_recognized_flag,'N') = 'Y'
OR nvl(gl_posted_flag,'N') = 'Y' )
;
PROCEDURE InsertIntoSublgr ( p_sublgr in SUBLGR ) IS
l_date DATE;
SELECT accounting_date
FROM ap_invoice_distributions
WHERE invoice_id = fp_invoice_id
AND set_of_books_id = fp_sob_id
AND distribution_line_number =
fp_dist_line_num
;
SELECT start_date, end_date
FROM gl_period_statuses
WHERE application_id = AP_APPLICATION_ID
AND set_of_books_id = fp_sob_id
AND period_name = ( select period_name
from ap_invoice_distributions
where invoice_id = fp_invoice_id AND
distribution_line_number =
fp_dist_line_num ) ;
SELECT start_date, end_date
FROM gl_period_statuses
WHERE application_id = AP_APPLICATION_ID
AND set_of_books_id = fp_sob_id
AND period_name = fp_period_name;
INSERT INTO IGI_MPP_SUBLEDGER
(
invoice_id
,distribution_line_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,subledger_entry_id
,currency_code
,actual_flag
,je_source_name
,je_category_name
,set_of_books_id
,gl_date
,expense_recognized_flag
,gl_posted_flag
,code_combination_id
,accounted_dr
,accounted_cr
,entered_dr
,entered_cr
,currency_conversion_date
,user_currency_conversion_type
,currency_conversion_rate
,period_name
,chart_of_accounts_id
,functional_currency_code
,date_created_in_gl
,je_batch_name
,je_batch_description
,je_header_name
,je_line_description
,reverse_journal_flag
,reversal_period_name
,ussgl_transaction_code
,reference1
,reference2
,reference3
) VALUES (
p_sublgr.invoice_id
,p_sublgr.distribution_line_number
,p_sublgr.last_update_date
,p_sublgr.last_updated_by
,p_sublgr.creation_date
,p_sublgr.created_by
,p_sublgr.last_update_login
,p_sublgr.subledger_entry_id
,p_sublgr.currency_code
,p_sublgr.actual_flag
,p_sublgr.je_source_name
,p_sublgr.je_category_name
,p_sublgr.set_of_books_id
,l_date
,p_sublgr.expense_recognized_flag
,p_sublgr.gl_posted_flag
,p_sublgr.code_combination_id
,p_sublgr.accounted_dr
,p_sublgr.accounted_cr
,p_sublgr.entered_dr
,p_sublgr.entered_cr
,p_sublgr.currency_conversion_date
,p_sublgr.user_currency_conversion_type
,p_sublgr.currency_conversion_rate
,p_sublgr.period_name
,p_sublgr.chart_of_accounts_id
,p_sublgr.functional_currency_code
,p_sublgr.date_created_in_gl
,p_sublgr.je_batch_name
,p_sublgr.je_batch_description
,p_sublgr.je_header_name
,p_sublgr.je_line_description
,p_sublgr.reverse_journal_flag
,p_sublgr.reversal_period_name
,p_sublgr.ussgl_transaction_code
,p_sublgr.reference1
,p_sublgr.reference2
,p_sublgr.reference3
);
SELECT DECODE(p_dr_or_cr,'DR',p_amount,null)
, DECODE(p_dr_or_cr,'DR',null,p_amount)
INTO l_sublgr.entered_dr ,
l_sublgr.entered_cr
FROM SYS.DUAL;
SELECT igi_mpp_subledger_s.nextval
INTO l_sublgr.subledger_entry_id
FROM sys.dual;
InsertIntoSublgr ( l_sublgr );
SELECT DECODE(p_dr_or_cr,'DR',p_ext_dist.amount,null)
, DECODE(p_dr_or_cr,'DR',null,p_ext_dist.amount)
INTO l_sublgr.entered_dr ,
l_sublgr.entered_cr
FROM SYS.DUAL;
SELECT igi_mpp_subledger_s.nextval
INTO l_sublgr.subledger_entry_id
FROM sys.dual;
InsertIntoSublgr ( l_sublgr );
SELECT setup.*
FROM igi_mpp_setup setup
;
l_sublgr.last_update_date := g_date;
l_sublgr.last_updated_by := g_user_id;
l_sublgr.last_update_login := g_login_id;
'>> >> Delete from Subledger Entries...');
delete from igi_mpp_subledger
where invoice_id = l_dist.invoice_id
and distribution_line_number = l_dist.distribution_line_number
;
PROCEDURE Update_MPPSLR_Details
( p_invoice_id in number
, p_distribution_line_number in number
) IS
BEGIN
NULL;
PROCEDURE Delete_MPPSLR_details
( p_invoice_id in number
, p_distribution_line_number in number
)
IS
BEGIN
NULL;