[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select set_of_books_id
into l_books_id
from ar_system_parameters_all
where org_id = p_reporting_entity_id;
SELECT currency_code,
name
INTO l_currency_code,
l_sob_name
FROM gl_sets_of_books
WHERE set_of_books_id = l_books_id;
l_status := 'SELECT trx.invoice_currency_code
,ctt.name transaction_type
,ctt.cust_trx_type_id
,rah.status
,arl.meaning
FROM ra_cust_trx_types_all ctt
,ra_customer_trx_all trx
,ar_transaction_history_all rah
,ar_lookups arl
WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
AND rah.customer_trx_id = trx.customer_trx_id
AND arl.lookup_code = rah.status
AND arl.lookup_type = ''TRANSACTION_HISTORY_STATUS'''||
'AND rah.transaction_history_id = (SELECT MAX(rah1.transaction_history_id)
FROM ar_transaction_history_all rah1
WHERE rah1.trx_date <= to_char(:b_status_date)' ||
l_org_where_rah1 ||
'AND rah1.customer_trx_id = trx.customer_trx_id)' ||
l_org_where_trx ||
l_org_where_ctt ||
l_org_where_rah ||
'AND rah.status <> ''INCOMPLETE'''||
'GROUP BY trx.invoice_currency_code
,ctt.name
,ctt.cust_trx_type_id
,rah.status
,arl.meaning';
l_amount := 'SELECT nvl(ps.amount_due_remaining,0)
,nvl(ps.acctd_amount_due_remaining,0)
,trx.customer_trx_id
,ps.exchange_rate
FROM ra_cust_trx_types_all ctt
,ra_customer_trx_all trx
,ar_transaction_history_all rah
,ar_payment_schedules_all ps
WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
AND rah.customer_trx_id = trx.customer_trx_id
AND rah.transaction_history_id = (SELECT MAX(rah1.transaction_history_id)
FROM ar_transaction_history_all rah1
WHERE rah1.trx_date <= to_char(:b_status_date)' ||
l_org_where_rah1 ||
'AND rah1.customer_trx_id = trx.customer_trx_id)' ||
l_org_where_trx ||
l_org_where_ctt ||
l_org_where_rah ||
l_org_where_ps ||
'AND trx.customer_trx_id = ps.customer_trx_id(+)
AND trx.invoice_currency_code = :b_currency_code '||
'AND rah.status = :b_status_code '||
'AND trx.cust_trx_type_id = :b_transaction_type_id ';
l_applied := 'SELECT nvl(app.amount_applied,0)
FROM ra_customer_trx_all trx,
ar_payment_schedules_all ps,
ar_receivable_applications_all app
WHERE trx.customer_trx_id = ps.customer_trx_id
AND trx.customer_trx_id = app.applied_customer_trx_id
AND app.applied_customer_trx_id = :b_trx_id '||
l_org_where_trx ||
l_org_where_ps ||
l_org_where_app ||
'AND app.status = ''APP'''||
'AND trunc(app.apply_date) > :b_as_of_date ';
| Insert Data into Interface Table |
+------------------------------------------------------------------*/
-- Check if any records exist for the status being inserted
IF l_count > 0 THEN
-- Insert the fetched data into the Interface Table
INSERT INTO ar_br_status_sum_itf
(creation_date
,created_by
,last_update_login
,last_update_date
,last_updated_by
,request_id
,status
,currency
,balance_due
,functional_balance_due
,transaction_type
,count
,functional_currency_code
,organization_name
)
VALUES
(sysdate
,p_user_id
,l_login_id
,sysdate
,p_user_id
,p_request_id
,v_status
,v_currency_code
,l_balance_due
,l_functional_balance_due
,v_transaction_type
,l_count
,l_currency_code
,l_sob_name
);