[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PRECISION,
MINIMUM_ACCOUNTABLE_UNIT,
CURRENCY_CODE
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = cc;
SELECT minimum_accountable_unit,
precision
INTO l_mau,
l_precision
FROM fnd_currencies
WHERE currency_code = p_currency;
SELECT c.minimum_accountable_unit,
c.precision
INTO l_mau,
l_precision
FROM fnd_currencies c,
gl_sets_of_books sob,
ar_system_parameters sp
WHERE sob.set_of_books_id = sp.set_of_books_id
AND c.currency_code = sob.currency_code;
| are update before or after activity by various modules. In addition
| depending on the bucket which is first choosen to be calculated the
| rounding correction is different and goes to the last bucket. The
| approach by this routine is the most desirable way to do things.
|
| SCOPE - PUBLIC
|
| PARAMETERS
| p_header_acctd_amt IN Header accounted amount to reconcile
| p_base_currency IN Base or functional currency
| p_exchange_rate IN Exchange rate
| p_base_precision IN Base precision
| p_base_min_acc_unit IN Minimum accountable unit
| p_tax_amt IN Tax amount in currency of Transaction
| p_charges_amt IN Charges amount in currency of Transaction
| p_freight_amt IN Freight amount in currency of Transaction
| p_line_amt IN Line amount in currency of Transaction
| p_tax_acctd_amt IN OUT NOCOPY Tax accounted amount in functional currency
| p_charges_acctd_amt IN OUT NOCOPY Charges accounted amount in functional currency
| p_freight_acctd_amt IN OUT NOCOPY Freight accounted amount in functional currency
| p_line_acctd_amt IN OUT NOCOPY Line accounted amount in functional currency
|
| Notes
| Introduced for 11.5 Tax accounting - used by ARALLOCB.pls and ARTWRAPB.pls
*===================================================================================*/
PROCEDURE Set_Buckets(
p_header_acctd_amt IN NUMBER ,
p_base_currency IN fnd_currencies.currency_code%TYPE,
p_exchange_rate IN NUMBER ,
p_base_precision IN NUMBER ,
p_base_min_acc_unit IN NUMBER ,
p_tax_amt IN NUMBER ,
p_charges_amt IN NUMBER ,
p_line_amt IN NUMBER ,
p_freight_amt IN NUMBER ,
p_tax_acctd_amt IN OUT NOCOPY NUMBER ,
p_charges_acctd_amt IN OUT NOCOPY NUMBER ,
p_line_acctd_amt IN OUT NOCOPY NUMBER ,
p_freight_acctd_amt IN OUT NOCOPY NUMBER ) IS
l_run_amt_tot NUMBER;
SELECT patch_level
INTO l_server_patchset_level
FROM FND_PRODUCT_INSTALLATIONS
WHERE application_id = 222 ;
SELECT 'Y'
FROM fnd_descriptive_flexs
WHERE application_id = 222
and descriptive_flexfield_name = p_desc_flex_name;
l_selected_id VARCHAR2(100);
SELECT c.cust_account_id
INTO l_selected_id
FROM hz_cust_accounts c,
hz_customer_profiles cp,
hz_parties party
WHERE c.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
c.account_number = p_value
AND c.party_id = party.party_id;
SELECT cust_acct.cust_account_id
INTO l_selected_id
FROM hz_cust_accounts cust_acct,
hz_customer_profiles cp,
hz_parties party
WHERE cust_acct.cust_account_id = cp.cust_account_id (+)
and cust_acct.party_id = party.party_id
and cp.site_use_id is null
and party.party_name = p_value;
SELECT receipt_method_id
INTO l_selected_id
FROM ar_receipt_methods
WHERE name = p_value;
SELECT ext_bank_account_id
INTO l_selected_id
FROM iby_ext_bank_accounts
WHERE ((bank_account_num = p_value) OR
(bank_account_num_hash1=
iby_security_pkg.Get_Hash(p_value,'F') and
bank_account_num_hash2=
iby_security_pkg.Get_Hash(p_value,'T')
)
);
SELECT ext_bank_account_id
INTO l_selected_id
FROM iby_ext_bank_accounts
WHERE bank_account_name = p_value;
SELECT bank_account_id
INTO l_selected_id
FROM ce_bank_accounts
WHERE bank_account_num = p_value;
SELECT bank_account_id
INTO l_selected_id
FROM ce_bank_accounts
WHERE bank_account_name = p_value;
SELECT currency_code
INTO l_selected_id
FROM fnd_currencies_vl
WHERE name = p_value;
SELECT conversion_type
INTO l_selected_id
FROM gl_daily_conversion_types
WHERE user_conversion_type = p_value ;
debug( 'Value selected. Entity: '||
p_entity || ',' ||
' Value: ' || p_value || ',' ||
'ID: ' || l_selected_id);
RETURN( l_selected_id );
SELECT l.customer_trx_line_id,
l.link_to_cust_trx_line_id link_to_line_id,
l.line_type,
l.extended_amount,
il.promised_commitment_amount
FROM ra_interface_lines_gt il,
ra_customer_trx_lines l
WHERE l.customer_trx_id = l_customer_trx_id
AND l.customer_trx_line_id = il.interface_line_id (+)
ORDER BY l.line_type ;
| PROCEDURE Dynamic_Select
|
| DESCRIPTION
| Executes a dynamic select statement
| Intended for client side calls where dynamic sql is not supported
|
| SCOPE - PUBLIC
|
| PARAMETERS
| p_query IN Dynamically assembled query to be executed
| p_result OUT NOCOPY Container for result column
|
| NOTES
| Only one column can be returned
*==========================================================================*/
PROCEDURE Dynamic_Select(p_query IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2)
IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
debug( 'arp_util.Dynamic_Select()+');
debug( 'arp_util.Dynamic_Select()-');
debug('EXCEPTION: ARP_UTIL.Dynamic_Select');
debug('Dynamic_Select: ' || 'SELECT stmt : '||p_query);
END Dynamic_Select;
select INITIAL_CUSTOMER_TRX_ID, PREVIOUS_CUSTOMER_TRX_ID, TRX_DATE
into l_initial_customer_trx_id, l_previous_customer_trx_id, p_start_date
from RA_CUSTOMER_TRX
where CUSTOMER_TRX_ID = p_customer_trx_id;
select TRX_DATE, ORG_ID
into l_date, l_org_id
from RA_CUSTOMER_TRX
where CUSTOMER_TRX_ID = p_customer_trx_id;
l_select_stmt VARCHAR2(10000);
l_select_stmt := 'SELECT 1
FROM DUAL
WHERE EXISTS( SELECT closing_status
FROM gl_period_statuses g,
gl_sets_of_books b,
ar_system_parameters_all sp
WHERE b.set_of_books_id = g.set_of_books_id
AND g.set_of_books_id = sp.set_of_books_id
AND g.period_type = b.accounted_period_type
AND g.application_id = 222
AND g.adjustment_period_flag = ''N''
AND g.closing_status not in (''P'', ''C'',''W'')
AND ((g.end_date BETWEEN :p_gl_date_from AND :p_gl_date_to)
OR (:p_gl_date_to BETWEEN g.start_date AND g.end_date))
' ||l_sysparam_org_where || ')';
EXECUTE IMMEDIATE l_select_stmt
INTO l_value
USING p_gl_date_from,p_gl_date_to,p_gl_date_to,p_reporting_entity_id,p_reporting_entity_id;
EXECUTE IMMEDIATE l_select_stmt
INTO l_value
USING p_gl_date_from, p_gl_date_to,p_gl_date_to;
l_select_stmt VARCHAR2(10000);
l_select_stmt := 'SELECT 1
FROM DUAL
WHERE EXISTS( SELECT closing_status
FROM gl_period_statuses g,
gl_sets_of_books b,
ar_system_parameters_all sp
WHERE b.set_of_books_id = g.set_of_books_id
AND g.set_of_books_id = sp.set_of_books_id
AND g.period_type = b.accounted_period_type
AND g.application_id = 222
AND g.adjustment_period_flag = ''N''
AND g.closing_status not in (''P'', ''C'',''W'')
AND start_date <= :p_in_as_of_date_low
AND end_date >= :p_in_as_of_date_low
' ||l_sysparam_org_where || ')';
EXECUTE IMMEDIATE l_select_stmt
INTO l_value
USING p_in_as_of_date_low,p_in_as_of_date_low,p_reporting_entity_id,p_reporting_entity_id;
EXECUTE IMMEDIATE l_select_stmt
INTO l_value
USING p_in_as_of_date_low,p_in_as_of_date_low;