[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
TYPE last_update_date_type
IS TABLE OF ar_gl_acct_balances.last_update_date%TYPE
INDEX BY BINARY_INTEGER;
TYPE last_updated_by_type
IS TABLE OF ar_gl_acct_balances.last_updated_by%TYPE
INDEX BY BINARY_INTEGER;
TYPE last_update_login_type
IS TABLE OF ar_gl_acct_balances.last_update_login%TYPE
INDEX BY BINARY_INTEGER;
SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE attribute_value = 'Y'
AND segment_attribute_type = 'GL_ACCOUNT'
AND id_flex_num in
(SELECT chart_of_accounts_id
FROM gl_sets_of_books sob,
ar_system_parameters sys
WHERE sob.set_of_books_id = sys.set_of_books_id);
PROCEDURE perform_updates IS
l_update_stmt VARCHAR2(32767);
debug('ar_cumulative_balance_report.perform_updates(+)');
l_update_stmt :=
'UPDATE ar_base_gl_acct_balances bal
SET (trx_number, trx_type, trx_date, currency) =
(
SELECT receipt_number, rm.name, receipt_date, currency_code
FROM ' || g_ar_cash_receipts_all || ', '
|| g_ar_receipt_methods || '
WHERE cr.receipt_method_id = rm.receipt_method_id
AND cr.cash_receipt_id = bal.cash_receipt_id
AND rownum = 1
)
WHERE bal.cash_receipt_id IS NOT NULL
AND bal.trx_number IS NULL';
l_update_stmt := l_update_stmt || g_balances_where;
debug(l_update_stmt, 'N');
EXECUTE IMMEDIATE l_update_stmt;
debug('update statement 2(a): ' || SQL%ROWCOUNT);
l_update_stmt :=
'UPDATE ar_gl_acct_balances bal
SET (trx_number, trx_type, trx_date, currency) =
(
SELECT receipt_number, rm.name, receipt_date, currency_code
FROM ' || g_ar_cash_receipts_all || ', '
|| g_ar_receipt_methods || '
WHERE cr.receipt_method_id = rm.receipt_method_id
AND cr.cash_receipt_id = bal.cash_receipt_id
AND rownum = 1
)
WHERE bal.cash_receipt_id IS NOT NULL
AND bal.trx_number IS NULL';
debug(l_update_stmt, 'N');
EXECUTE IMMEDIATE l_update_stmt;
debug('update statement 2(b): ' || SQL%ROWCOUNT);
l_update_stmt :=
'UPDATE ar_base_gl_acct_balances bal
SET (trx_number, trx_type, trx_date, currency) =
(
SELECT trx_number, ctt.name, trx_date, invoice_currency_code
FROM ' || g_ra_customer_trx_all || ', '
|| g_ra_cust_trx_types_all || '
WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
AND trx.customer_trx_id = bal.customer_trx_id
AND rownum = 1
)
WHERE bal.customer_trx_id IS NOT NULL
AND bal.trx_number IS NULL';
l_update_stmt := l_update_stmt || g_balances_where;
debug(l_update_stmt, 'N');
EXECUTE IMMEDIATE l_update_stmt;
debug('update statement 1(a): ' || SQL%ROWCOUNT);
l_update_stmt :=
'UPDATE ar_gl_acct_balances bal
SET (trx_number, trx_type, trx_date, currency) =
(
SELECT trx_number, ctt.name, trx_date, invoice_currency_code
FROM ' || g_ra_customer_trx_all || ', '
|| g_ra_cust_trx_types_all || '
WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
AND trx.customer_trx_id = bal.customer_trx_id
AND rownum = 1
)
WHERE bal.customer_trx_id IS NOT NULL
AND bal.trx_number IS NULL';
debug(l_update_stmt, 'N');
EXECUTE IMMEDIATE l_update_stmt;
debug('update statement 1(b): ' || SQL%ROWCOUNT);
debug('ar_cumulative_balance_report.perform_updates(-)');
debug('EXCEPTION: NO_DATA_FOUND perform_updates');
debug('EXCEPTION: OTHERS perform_updates');
END perform_updates;
PROCEDURE insert_dist_data (
p_start_date DATE,
p_end_date DATE,
p_period_status VARCHAR2) IS
components_tab components_type;
last_update_date_tab last_update_date_type;
last_updated_by_tab last_updated_by_type;
last_update_login_tab last_update_login_type;
SELECT cur.precision
FROM gl_sets_of_books sob,
fnd_currencies cur
WHERE sob.currency_code = cur.currency_code
AND sob.set_of_books_id = arp_standard.sysparm.set_of_books_id;
debug('ar_cumulative_balance_report.insert_dist_data(+)');
'SELECT
MAX(component),
MAX(gl_account) gl_account,
natural_account,
trx_type,
trx_number,
trx_date,
entered_currency,
MAX(activity_gl_date) activity_gl_date,
round(sum(acctd_amt_dr), ' || l_precision || ') acctd_amt_dr,
round(sum(acctd_amt_cr), ' || l_precision || ') acctd_amt_cr,
round(sum(amount_dr), ' || l_precision || ') amount_dr,
round(sum(amount_cr), ' || l_precision || ') amount_cr,
code_combination_id,
customer_trx_id,
cash_receipt_id,
adjustment_id,
max(org_id) org_id,
sysdate creation_date,
' || l_user_id || ' created_by,
sysdate last_update_date,
' || l_user_id || ' last_updated_by,
' || l_user_id || ' last_update_login
FROM
(
-- pick up distributions from the ra_cust_trx_line_gl_dist_all
SELECT
''DIST'' component,
MAX(glc.gl_account) gl_account,
glc.natural_account,
ctt.name trx_type,
trx_number,
trx_date,
invoice_currency_code entered_currency,
MAX(dist.gl_date) activity_gl_date,
sum(DECODE(account_class,
''REC'',decode(sign(acctd_amount),-1,0,acctd_amount),
''REV'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''TAX'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''ROUND'',decode(sign(acctd_amount), -1,abs(acctd_amount),0),
''UNEARN'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''UNBILL'',decode(sign(acctd_amount), -1, abs(acctd_amount),0),
''SUSPENSE'',decode(sign(acctd_amount),-1, abs(acctd_amount),0),0))
acctd_amt_dr,
sum(DECODE(account_class,
''REC'',decode(sign(acctd_amount),-1,abs(acctd_amount),0),
''REV'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''TAX'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''ROUND'',decode(sign(acctd_amount), -1,0,acctd_amount),
''UNEARN'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''UNBILL'',decode(sign(acctd_amount), -1, 0,acctd_amount),
''SUSPENSE'',decode(sign(acctd_amount), -1, 0,acctd_amount),0))
acctd_amt_cr ,
sum(DECODE(account_class,
''REC'',decode(sign(amount), -1 ,0, amount),
''REV'',decode(sign(amount), -1, abs(amount),0),
''TAX'',decode(sign(amount), -1, abs(amount),0),
''ROUND'',decode(sign(amount), -1,abs(amount),0),
''UNEARN'',decode(sign(amount), -1, abs(amount),0),
''UNBILL'',decode(sign(amount), -1, abs(amount),0),
''SUSPENSE'',decode(sign(acctd_amount),-1, abs(acctd_amount),0),0))
amount_dr,
sum(DECODE(account_class,
''REC'',decode(sign(amount), -1 ,abs(amount),0),
''REV'',decode(sign(amount), -1, 0,amount),
''TAX'',decode(sign(amount), -1, 0,amount),
''ROUND'',decode(sign(amount), -1,0,amount),
''UNEARN'',decode(sign(amount), -1, 0,amount),
''UNBILL'',decode(sign(amount), -1, 0,amount),
''SUSPENSE'',decode(sign(amount), -1, 0,amount),0))
amount_cr,
dist.code_combination_id,
dist.customer_trx_id customer_trx_id,
null cash_receipt_id,
null adjustment_id,
max(dist.org_id) org_id
FROM ' ||
g_ra_cust_trx_gl_dist_all || ', ' ||
g_ra_customer_trx_all || ', ' ||
g_ra_cust_trx_types_all ||
', ar_ccid_by_gl_accounts glc
WHERE dist.gl_date BETWEEN :p_start_date AND :p_end_date
AND dist.account_set_flag = ''N''
AND trx.complete_flag = ''Y''
AND dist.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = ctt.cust_trx_type_id
AND dist.code_combination_id = glc.code_combination_id '
|| g_dist_org_where
|| g_trx_org_where
|| g_type_org_where || '
-- AND dist.posting_control_id > 0
GROUP BY
glc.natural_account,
ctt.name,
trx_number,
trx_date,
invoice_currency_code,
dist.code_combination_id,
dist.customer_trx_id,
null,
null
-- pick up distributions from the tables ar_distributions_all for
-- ar_cash_receipt_history
UNION ALL
SELECT
''CRH'' component,
MAX(glc.gl_account) gl_account,
glc.natural_account,
rm.name trx_type,
cr.receipt_number trx_number,
cr.receipt_date trx_date,
cr.currency_code entered_currency,
MAX(crh.gl_date) activity_gl_date,
sum(acctd_amount_dr) acctd_amt_dr,
sum(acctd_amount_cr) acctd_amt_cr,
sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
ard.code_combination_id,
null customer_trx_id,
crh.cash_receipt_id cash_receipt_id,
null adjustment_id,
max(ard.org_id) org_id
FROM ' ||
g_ar_distributions_all || ', ' ||
g_ar_cash_receipt_history_all || ', ' ||
g_ar_cash_receipts_all || ', ' ||
g_ar_receipt_methods || ' , ' ||
'ar_ccid_by_gl_accounts glc
WHERE crh.gl_date between :p_start_date and :p_end_date
AND crh.cash_receipt_history_id = ard.source_id
AND ard.source_table = ''CRH''
AND ard.code_combination_id = glc.code_combination_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id '
|| g_ard_org_where
|| g_crh_org_where
|| g_cr_org_where || '
-- AND crh.posting_control_id > 0
GROUP BY
glc.natural_account,
rm.name,
cr.receipt_number,
cr.receipt_date,
cr.currency_code,
--null,
ard.code_combination_id,
null,
crh.cash_receipt_id,
null
-- pick up distributions from the table ar_distributions_all for
-- receivable_applications_all
UNION ALL
SELECT
''RA'' component,
MAX(glc.gl_account) gl_account,
glc.natural_account,
null trx_type,
null trx_number,
null trx_date,
null entered_currency,
max(ra.gl_date) activity_gl_date,
sum(acctd_amount_dr) acctd_amt_dr,
sum(acctd_amount_cr) acctd_amt_cr,
sum(amount_dr) amt_dr,
sum(amount_cr) amt_cr,
ard.code_combination_id,
decode(ra.application_type,''CASH'',
decode(ra.status, ''APP'', ra.applied_customer_trx_id, null),
''CM'', decode(sign(ra.amount_applied),-1,
decode(ard.amount_dr,null,ra.customer_trx_id,
ra.applied_customer_trx_id),
decode(ard.amount_dr,null,ra.applied_customer_trx_id,
ra.customer_trx_id))) customer_trx_id,
decode(ra.status, ''APP'', to_number(null), ra.cash_receipt_id)
cash_receipt_id,
null adjustment_id,
max(ard.org_id) org_id
FROM ' ||
g_ar_distributions_all || ', ' ||
g_ar_receivable_apps_all || ', ' ||
' ar_ccid_by_gl_accounts glc
WHERE ra.gl_date BETWEEN :p_start_date and :p_end_date
AND ra.receivable_application_id = ard.source_id
AND ard.source_table = ''RA''
AND ard.code_combination_id = glc.code_combination_id '
|| g_ard_org_where
|| g_rec_org_where || '
-- AND ra.posting_control_id > 0
GROUP BY
glc.natural_account,
null,
null,
null,
null,
--null,
ard.code_combination_id,
decode(ra.application_type,''CASH'',
decode(ra.status,''APP'',ra.applied_customer_trx_id,null),
''CM'',decode(sign(ra.amount_applied),-1,
decode(ard.amount_dr,null,ra.customer_trx_id,
ra.applied_customer_trx_id),
decode(ard.amount_dr,null,ra.applied_customer_trx_id,
ra.customer_trx_id))),
decode(ra.status,''APP'',to_number(null),ra.cash_receipt_id),
null
-- pick up distributions from the table ar_distributions_all for
-- ar_misc_cash_distributions
UNION ALL
SELECT
''MCH'' component,
MAX(glc.gl_account) gl_account,
glc.natural_account,
rm.name trx_type,
cr.receipt_number trx_number,
cr.receipt_date trx_date,
cr.currency_code entered_currency,
MAX(mcd.gl_date),
sum(acctd_amount_dr) acctd_amt_dr,
sum(acctd_amount_cr) acctd_amt_cr,
sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
ard.code_combination_id,
null customer_trx_id,
mcd.cash_receipt_id,
null adjustment_id,
max(ard.org_id) org_id
FROM ' ||
g_ar_distributions_all || ', ' ||
g_ar_misc_cash_dists_all || ', ' ||
g_ar_cash_receipts_all || ', ' ||
g_ar_receipt_methods || ', ' ||
' ar_ccid_by_gl_accounts glc
WHERE mcd.gl_date between :p_start_date and :p_end_date
AND mcd.misc_cash_distribution_id = ard.source_id
AND ard.source_table = ''MCD''
AND ard.code_combination_id = glc.code_combination_id
AND mcd.cash_receipt_id = cr.cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id '
|| g_ard_org_where
|| g_mcd_org_where
|| g_cr_org_where || '
-- AND mcd.posting_control_id > 0
GROUP BY
glc.natural_account,
rm.name,
cr.receipt_number,
cr.receipt_date,
cr.currency_code,
--null,
ard.code_combination_id,
null,
mcd.cash_receipt_id,
null
-- pick up distributions from the table ar_distributions_all for
-- ar_adjustments
UNION ALL
SELECT
''ADJ'' component,
MAX(glc.gl_account) gl_account,
glc.natural_account,
ctt.name trx_type,
trx_number,
trx_date,
invoice_currency_code entered_currency,
MAX(adj.gl_date) activity_gl_date,
sum(acctd_amount_dr) acctd_amt_dr,
sum(acctd_amount_cr) acctd_amt_cr,
sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
ard.code_combination_id,
decode(adj.amount,-1,
decode(ard.amount_dr,null, adj.customer_trx_id, null),
decode(ard.amount_cr,null, adj.customer_trx_id, null))
customer_trx_id,
null cash_receipt_id,
decode(adj.amount,-1,
decode(ard.amount_cr,null, adj.adjustment_id, null),
decode(ard.amount_dr,null, adj.adjustment_id, null))
adjustment_id,
max(ard.org_id) org_id
FROM ' ||
g_ar_distributions_all || ', ' ||
g_ar_adjustments_all || ', ' ||
g_ra_customer_trx_all || ', ' ||
g_ra_cust_trx_types_all || ', ' ||
' ar_ccid_by_gl_accounts glc
WHERE adj.gl_date between :p_start_date and :p_end_date
AND adj.adjustment_id = ard.source_id
AND ard.source_table = ''ADJ''
AND ard.code_combination_id = glc.code_combination_id
AND adj.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = ctt.cust_trx_type_id '
|| g_ard_org_where
|| g_adj_org_where
|| g_trx_org_where
|| g_type_org_where || '
-- AND adj.posting_control_id > 0
GROUP BY
glc.natural_account,
ctt.name,
trx_number,
trx_date,
invoice_currency_code,
--null,
ard.code_combination_id,
decode(adj.amount,-1,
decode(ard.amount_dr,null, adj.customer_trx_id, null),
decode(ard.amount_cr,null, adj.customer_trx_id, null)),
null,
decode(adj.amount,-1,
decode(ard.amount_cr,null, adj.adjustment_id, null),
decode(ard.amount_dr,null, adj.adjustment_id, null))
-- pick up distributions from the table ar_distributions_all for
-- ar_transaction_history (BR)
UNION ALL
SELECT
''BR'' component,
MAX(glc.gl_account) gl_account,
glc.natural_account,
null trx_type,
null trx_number,
null trx_date,
null entered_currency,
max(br.gl_date) activity_gl_date,
sum(acctd_amount_dr) acctd_amt_dr,
sum(acctd_amount_cr) acctd_amt_cr,
sum(amount_dr) amt_dr, sum(amount_cr) amt_cr,
ard.code_combination_id,
br.customer_trx_id customer_trx_id,
null cash_receipt_id,
null adjustment_id,
max(ard.org_id) org_id
FROM ' ||
g_ar_distributions_all || ', ' ||
g_ar_transaction_history_all || ', ' ||
' ar_ccid_by_gl_accounts glc
WHERE br.gl_date between :p_start_date and :p_end_date
AND br.transaction_history_id = ard.source_id
AND ard.source_table = ''TH''
AND ard.code_combination_id = glc.code_combination_id '
|| g_ard_org_where
|| g_br_org_where || '
-- AND br.posting_control_id > 0
GROUP BY
glc.natural_account,
null,
null,
null,
null,
--null,
ard.code_combination_id,
customer_trx_id,
null,
null
)
GROUP BY
natural_account,
trx_type,
trx_number,
trx_date,
entered_currency,
-- activity_gl_date,
null,
null,
null,
null,
null,
code_combination_id,
customer_trx_id,
cash_receipt_id,
adjustment_id,
sysdate,
' || l_user_id || ' ,
sysdate,
' || l_user_id || ' ,
' || l_user_id || ' ';
last_update_date_tab,
last_updated_by_tab,
creation_date_tab,
created_by_tab,
last_update_login_tab
LIMIT 1000;
INSERT INTO ar_base_gl_acct_balances
(
component,
gl_account,
natural_account,
trx_type,
trx_number,
trx_date,
currency,
activity_gl_date,
code_combination_id ,
customer_trx_id,
cash_receipt_id,
adjustment_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
acctd_amount_dr,
acctd_amount_cr,
amount_dr,
amount_cr,
org_id
)
VALUES
(
components_tab(i),
gl_account_tab(i),
natural_account_tab(i),
trx_type_tab(i),
trx_number_tab(i),
trx_date_tab(i),
currency_code_tab(i),
gl_date_tab(i),
code_combination_id_tab(i),
customer_trx_id_tab(i),
cash_receipt_id_tab(i),
adjustment_id_tab(i),
last_update_date_tab(i),
last_updated_by_tab(i),
creation_date_tab(i),
created_by_tab(i),
last_update_login_tab(i),
acctd_amount_dr_tab(i),
acctd_amount_cr_tab(i),
amount_dr_tab(i),
amount_cr_tab(i),
org_id_tab(i)
);
INSERT INTO ar_gl_acct_balances
(
component,
gl_account,
natural_account,
trx_type,
trx_number,
trx_date,
currency,
activity_gl_date,
code_combination_id ,
customer_trx_id ,
cash_receipt_id ,
adjustment_id ,
last_update_date,
last_updated_by ,
creation_date ,
created_by ,
last_update_login,
acctd_amount_dr,
acctd_amount_cr,
amount_dr,
amount_cr,
org_id
)
VALUES
(
components_tab(i),
gl_account_tab(i),
natural_account_tab(i),
trx_type_tab(i),
trx_number_tab(i),
trx_date_tab(i),
currency_code_tab(i),
gl_date_tab(i),
code_combination_id_tab(i),
customer_trx_id_tab(i),
cash_receipt_id_tab(i),
adjustment_id_tab(i),
last_update_date_tab(i),
last_updated_by_tab(i),
creation_date_tab(i),
created_by_tab(i),
last_update_login_tab(i),
acctd_amount_dr_tab(i),
acctd_amount_cr_tab(i),
amount_dr_tab(i),
amount_cr_tab(i),
org_id_tab(i)
);
debug('ar_cumulative_balance_report.insert_dist_data(-)');
debug('EXCEPTION: NO_DATA_FOUND insert_dist_data');
debug('EXCEPTION: OTHERS insert_dist_data');
END insert_dist_data;
l_delete_stmt VARCHAR2(200);
DELETE FROM ar_ccid_by_gl_accounts;
debug('number of rows deleted: ' || SQL%ROWCOUNT);
'INSERT INTO ar_ccid_by_gl_accounts
(
code_combination_id,
natural_account,
gl_account,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
(
SELECT
code_combination_id, ' ||
l_segment_name || ' , ' ||
'fnd_flex_ext.get_segs
(
''SQLGL'',
''GL#'', ' ||
p_chart_of_accounts_id || ' ,
code_combination_id) gl_account ' || ',
sysdate,
' || l_user_id || ' ,
sysdate,
' || l_user_id || ' ,
' || l_user_id || '
FROM gl_code_combinations gcc
WHERE gcc.account_type IN (''A'', ''L'')
AND chart_of_accounts_id = ' || p_chart_of_accounts_id || '
AND ' || l_segment_name || ' IS NOT NULL ) ';
debug('number of rows inserted: ' || SQL%ROWCOUNT);
l_sql_stmt := 'SELECT count(*), max(trx_date), max(activity_gl_date)
FROM ar_base_gl_acct_balances bal WHERE 1=1 ' ;
l_delete_stmt VARCHAR2(500);
SELECT MIN(start_date), MAX(end_date)
FROM ar_closed_gl_periods
WHERE closing_status = 'C';
debug( 'Refresh option selected');
INSERT INTO ar_closed_gl_periods
(
period_name,
start_date,
end_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
period_year,
closing_status
)
(
SELECT
period_name,
start_date,
end_date,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
period_year,
closing_status
FROM gl_period_statuses
WHERE adjustment_period_flag = 'N'
AND application_id = 222
AND end_date <= p_gl_as_of_date
AND EXISTS
(
SELECT set_of_books_id
FROM ar_system_parameters
)
AND NOT EXISTS
(
SELECT 'x'
FROM ar_closed_gl_periods
)
);
debug('Done - INSERT INTO ar_closed_gl_periods');
l_delete_stmt := 'DELETE FROM ar_base_gl_acct_balances bal WHERE 1=1 ';
l_delete_stmt := l_delete_stmt || g_balances_where;
debug('Delete Statement: ' || l_delete_stmt);
EXECUTE IMMEDIATE l_delete_stmt;
debug('base (all): number of rows deleted: ' || SQL%ROWCOUNT);
debug('calling insert_dist_data passing status as CLOSED');
insert_dist_data(
p_start_date => l_min_start_date ,
p_end_date => l_max_end_date,
p_period_status => 'CLOSED');
l_delete_stmt := 'DELETE FROM ar_base_gl_acct_balances bal
WHERE nvl(acctd_amount_dr,0) = nvl(acctd_amount_cr,0) ';
l_delete_stmt := l_delete_stmt || g_balances_where;
debug('Delete Statement: ' || l_delete_stmt);
EXECUTE IMMEDIATE l_delete_stmt;
debug('Number of rows deleted: ' || SQL%ROWCOUNT);
l_delete_stmt := 'DELETE FROM ar_gl_acct_balances bal WHERE 1=1 ';
l_delete_stmt := l_delete_stmt || g_balances_where;
EXECUTE IMMEDIATE l_delete_stmt;
debug('number of rows deleted: ' || SQL%ROWCOUNT);
insert_dist_data(
p_start_date => l_max_end_date+1,
p_end_date => p_gl_as_of_date,
p_period_status => 'OPEN');
l_delete_stmt := 'DELETE FROM ar_gl_acct_balances bal
WHERE nvl(acctd_amount_dr,0) = nvl(acctd_amount_cr,0) ';
l_delete_stmt := l_delete_stmt || g_balances_where;
EXECUTE IMMEDIATE l_delete_stmt;
debug('Number of rows deleted: ' || SQL%ROWCOUNT);
SELECT mrc_sob_type_code
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
select set_of_books_id
from ar_system_parameters;
SELECT meaning
FROM ar_lookups
WHERE lookup_type = 'AR_ARXCUABR_REPORTING_FORMAT'
AND lookup_code = p_format;
SELECT meaning
FROM ar_lookups
WHERE lookup_code ='ALL'
AND lookup_type ='ALL';
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_chart_of_accounts_id
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = p_reporting_entity_id;
SELECT sob.name sob_name,
sob.set_of_books_id,
sob.currency_code functional_currency,
sob.chart_of_accounts_id,
substr(hou.name,1,60) organization
INTO l_sob_name,
l_sob_id,
l_functional_currency,
l_chart_of_accounts_id,
l_organization
FROM gl_sets_of_books sob,
ar_system_parameters_all sysparam,
hr_organization_units hou
WHERE sob.set_of_books_id = sysparam.set_of_books_id
AND hou.organization_id = sysparam.org_id
AND sysparam.org_id = p_reporting_entity_id;
perform_updates;
'SELECT
decode (''' || p_reporting_format || ''', ''GL_ACCOUNT'',
gl_account, natural_account) gl_account,
trx_type,
trx_number,
to_char(trx_date,''YYYY-MM-DD'') trx_date,
to_char(max(activity_gl_date),''YYYY-MM-DD'') activity_gl_date,
currency,
decode(sign(sum(nvl(acctd_amount_dr,0))-
sum(nvl(acctd_amount_cr,0))), -1, 0,
sum(nvl(acctd_amount_dr,0))-sum(nvl(acctd_amount_cr,0)))
acctd_amount_dr,
decode(sign(sum(nvl(acctd_amount_cr,0))-
sum(nvl(acctd_amount_dr,0))), -1, 0,
sum(nvl(acctd_amount_cr,0))-sum(nvl(acctd_amount_dr,0)))
acctd_amount_cr,
decode(sign(sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0))), -1, 0,
sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0)))
amount_dr,
decode(sign(sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0))), -1, 0,
sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0)))
amount_cr
FROM ar_base_gl_acct_balances bal,
gl_code_combinations gcc
WHERE bal.code_combination_id = gcc.code_combination_id ';
'SELECT
decode (''' || p_reporting_format || ''', ''GL_ACCOUNT'',
gl_account, natural_account) gl_account,
trx_type,
trx_number,
to_char(trx_date,''YYYY-MM-DD'') trx_date,
to_char(max(activity_gl_date),''YYYY-MM-DD'') activity_gl_date,
currency,
decode(sign(sum(nvl(acctd_amount_dr,0))-
sum(nvl(acctd_amount_cr,0))), -1, 0,
sum(nvl(acctd_amount_dr,0))-sum(nvl(acctd_amount_cr,0)))
acctd_amount_dr,
decode(sign(sum(nvl(acctd_amount_cr,0))-
sum(nvl(acctd_amount_dr,0))), -1, 0,
sum(nvl(acctd_amount_cr,0))-sum(nvl(acctd_amount_dr,0)))
acctd_amount_cr,
decode(sign(sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0))), -1, 0,
sum(nvl(amount_dr,0))-sum(nvl(amount_cr,0)))
amount_dr,
decode(sign(sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0))), -1, 0,
sum(nvl(amount_cr,0))-sum(nvl(amount_dr,0)))
amount_cr
FROM ar_gl_acct_balances bal,
gl_code_combinations gcc
WHERE bal.code_combination_id = gcc.code_combination_id ';