[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ap.secondary_set_of_books_id,
gl.chart_of_accounts_id
FROM ap_system_parameters ap,
gl_sets_of_books gl
WHERE ap.set_of_books_id = gl.set_of_books_id;
SELECT je_source_name , user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'Payables'
AND LANGUAGE = USERENV('LANG');
SELECT je_category_name , user_je_category_name
FROM gl_je_categories
WHERE je_category_name = '41'
AND LANGUAGE = USERENV('LANG');
SELECT start_date
FROM gl_period_statuses
WHERE period_name = p_period
AND set_of_books_id = p_secondary_set_of_books_id
AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name ='SQLGL');
SELECT end_date
FROM gl_period_statuses
WHERE period_name = p_period
AND set_of_books_id = p_secondary_set_of_books_id
AND application_id =
(SELECT application_id FROM fnd_application WHERE application_short_name = 'SQLGL');
SELECT
inv.invoice_id INVOICE_ID ,
inv.invoice_date INVOICE_DATE,
inv_dist1.set_of_books_id ACCURAL_SET_OF_BOOKS_ID,
inv_dist1.invoice_distribution_id TAX_DIST_ID,
inv_dist1.dist_code_combination_id TAX_CCID,
chrg.allocated_base_amount TAX_AMOUNT,
inv_dist2.invoice_distribution_id ITEM_DIST_ID,
inv_dist2.dist_code_combination_id ITEM_CCID ,
inv_dist2.org_id ORG_ID,
inv.invoice_currency_code INV_CURRENCY_CODE
FROM
ap_invoice_distributions inv_dist1,
ap_chrg_allocations chrg,
ap_invoice_distributions inv_dist2,
ap_invoices inv
WHERE
inv_dist1.cash_posted_flag = 'Y' AND
inv_dist1.tax_recoverable_flag = 'Y' AND inv_dist1.line_type_lookup_code = 'TAX'
AND inv_dist1.invoice_distribution_id = chrg.charge_dist_id
AND inv_dist2.invoice_distribution_id = chrg.item_dist_id
AND inv.invoice_id = inv_dist1.invoice_id
AND inv.invoice_date BETWEEN start_date AND end_date
AND NOT EXISTS
( SELECT 'Y' FROM IGI_RECOVERABLE_LINES WHERE tax_distribution_id = chrg.charge_dist_id);
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 );
--insert into IGI_RECOVERABLE_LINES
--------------------------------------------------------------------
l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
insert into IGI_RECOVERABLE_LINES
( Invoice_id,
Accounting_date,
Invoice_date,
Inv_Currency_Code,
Accrual_Set_of_books_id,
Request_Id,
Tax_distribution_id,
Tax_ccid,
Tax_amount,
Item_distribution_id,
Item_ccid,
last_updated_by,
last_update_date,
created_by,
Created_date,
Last_update_login)
values ( l_tax_lines.invoice_id,
sysdate,
l_tax_lines.invoice_date,
l_tax_lines.INV_CURRENCY_CODE,
l_tax_lines.ACCURAL_SET_OF_BOOKS_ID,
l_request_id,
l_tax_lines.tax_dist_id,
l_tax_lines.tax_ccid,
l_tax_lines.tax_amount,
l_tax_lines.item_dist_id,
l_tax_lines.item_ccid,
to_number(fnd_profile.value('USER_ID')),
sysdate,
to_number(fnd_profile.value('LOGIN_ID')),
sysdate,
to_number(fnd_profile.value('USER_ID')));
/* For each recoverable tax line identified insert two line into GL Interface
IF the tax amount is positive THEN
Debit the Item Line Code Combination Id with the Tax Line Amount
Credit the Tax Line Code Combination Id with the Tax Line Amount
ELSEIF the tax amount is negative THEN
Debit Tax Line ccid with absolute Tax Line Amount
Credit Item Line ccid with absolute Tax Line Amount
END IF*/
--------------------------------------------------------------------
-- Start(1) bug 2119400 vgadde 23-NOV-2001
IF ( l_tax_lines.tax_amount > 0 ) THEN
-- End(1) bug 2119400 vgadde 23-NOV-2001
-----------------------------------------------------
-- Debit entry for item ccid for positive tax amount
-----------------------------------------------------
Gl_interface_insert(
'NEW',
l_secondary_set_of_books_id,
sysdate,
l_tax_lines.INV_CURRENCY_CODE,
sysdate,
to_number(fnd_profile.value('USER_ID')),
'A',
l_user_je_category_name,
l_user_je_source_name,
abs(l_tax_lines.tax_amount),
NULL,
abs(l_tax_lines.tax_amount),
NULL,
sysdate,
l_je_category_name, -- reference1
NULL, -- reference4
l_je_source_name, -- reference6
NULL, -- reference10
l_tax_lines.invoice_id, -- reference21
l_tax_lines.tax_dist_id, -- reference22
NULL,
l_chart_of_accounts_id,
l_tax_lines.inv_currency_code,
l_tax_lines.item_ccid,
null );
Gl_Interface_Insert(
'NEW',
l_secondary_set_of_books_id,
sysdate,
l_tax_lines.INV_CURRENCY_CODE,
sysdate,
to_number(fnd_profile.value('USER_ID')),
'A',
l_user_je_category_name,
l_user_je_source_name,
NULL,
abs(l_tax_lines.tax_amount),
NULL,
abs(l_tax_lines.tax_amount),
sysdate,
l_je_category_name, -- reference1
NULL, -- reference4
l_je_source_name, -- reference6
NULL, -- reference10
l_tax_lines.invoice_id, -- reference21
l_tax_lines.tax_dist_id, -- reference22
NULL,
l_chart_of_accounts_id,
l_tax_lines.inv_currency_code,
l_tax_lines.tax_ccid,
null );
Gl_interface_insert(
'NEW',
l_secondary_set_of_books_id,
sysdate,
l_tax_lines.INV_CURRENCY_CODE,
sysdate,
to_number(fnd_profile.value('USER_ID')),
'A',
l_user_je_category_name,
l_user_je_source_name,
abs(l_tax_lines.tax_amount),
NULL,
abs(l_tax_lines.tax_amount),
NULL,
sysdate,
l_je_category_name, -- reference1
NULL, -- reference4
l_je_source_name, -- reference6
NULL, -- reference10
l_tax_lines.invoice_id, -- reference21
l_tax_lines.tax_dist_id, -- reference22
NULL,
l_chart_of_accounts_id,
l_tax_lines.inv_currency_code,
l_tax_lines.tax_ccid,
null );
Gl_Interface_Insert(
'NEW',
l_secondary_set_of_books_id,
sysdate,
l_tax_lines.INV_CURRENCY_CODE,
sysdate,
to_number(fnd_profile.value('USER_ID')),
'A',
l_user_je_category_name,
l_user_je_source_name,
NULL,
abs(l_tax_lines.tax_amount),
NULL,
abs(l_tax_lines.tax_amount),
sysdate,
l_je_category_name, -- reference1
NULL, -- reference4
l_je_source_name, -- reference6
NULL, -- reference10
l_tax_lines.invoice_id, -- reference21
l_tax_lines.tax_dist_id, -- reference22
NULL,
l_chart_of_accounts_id,
l_tax_lines.inv_currency_code,
l_tax_lines.item_ccid,
null );
Insert_Control_Rec(l_int_control);
errbuf := 'No records found to process No records inserted into GL INTERFACE';
-- update all the rows after inserting to gl_interface table
-- so that the lines are not consider when run next time
--------------------------------------------------------------------
if l_import_request_id = 0 then
update IGI_RECOVERABLE_LINES
set je_created_flag = 'Y'
where request_id =FND_GLOBAL.CONC_REQUEST_ID ;
WriteLog( '>> IGI_RECOVERABLE_LINES updated ');
Select gl_journal_import_s.Nextval,
p_set_of_books_id,
NULL,
'S',
'Payables'
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';
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);
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 );
End Gl_Interface_Insert;