[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
NVL(SUM(NVL(line_applied,0) ), 0)
, NVL(SUM(arpcurr.currround((line_applied * acctd_amount_applied_to)/
amount_applied )), 0)
, NVL(SUM(DECODE(SIGN(acctd_amount_applied_from -
acctd_amount_applied_to),
1,
arpcurr.currround(((acctd_amount_applied_from -
acctd_amount_applied_to) *
line_applied)/amount_applied),0)), 0)
, NVL(SUM(DECODE(SIGN(acctd_amount_applied_from -
acctd_amount_applied_to),
-1,
arpcurr.currround(((acctd_amount_applied_from -
acctd_amount_applied_to) * -1 *
line_applied)/amount_applied),0)),
0)
INTO l_amount_applied
,l_acctd_amount_applied
,l_xchange_gain
,l_xchange_loss
FROM ar_receivable_applications app,
ar_cash_receipts cr,
ra_customer_trx cm,
ra_customer_trx inv
WHERE
applied_customer_trx_id = p_customer_trx_id
AND applied_customer_trx_id = inv.customer_trx_id
AND app.status in ('APP')
AND app.cash_receipt_id = cr.cash_receipt_id (+)
AND app.customer_trx_id = cm.customer_trx_id (+)
AND nvl(app.confirmed_flag,'Y') = 'Y'
AND display = 'Y'
AND apply_date <= p_as_of_date
AND amount_applied <> 0
GROUP BY app.set_of_books_id;
SELECT sum(adj.line_adjusted) ,
sum(adj.line_adjusted*ra.exchange_rate)
INTO l_line_adjusted ,
l_acctd_line_adjusted
FROM ar_adjustments_all adj ,
ra_customer_trx_all ra
WHERE adj.customer_trx_id = ra.customer_trx_id
AND adj.customer_trx_id = p_customer_trx_id
AND adj.status='A'
AND adj.apply_date <= p_as_of_date;
SELECT amr.set_of_books_id
, NVL(SUM(NVL(app.line_applied,0) ), 0)
, NVL(SUM(arpcurr.currround((app.line_applied * amr.acctd_amount_applied_to)/
app.amount_applied )), 0)
, NVL(SUM(DECODE(SIGN(amr.acctd_amount_applied_from -
amr.acctd_amount_applied_to),
1,
GL_MC_CURRENCY_PKG.CURRROUND((((amr.acctd_amount_applied_from -
amr.acctd_amount_applied_to) *
app.line_applied)
/app.amount_applied),glr.TARGET_CURRENCY_CODE),0)),0)
, NVL(SUM(DECODE(SIGN(amr.acctd_amount_applied_from -
amr.acctd_amount_applied_to), -1,
GL_MC_CURRENCY_PKG.CURRROUND((((amr.acctd_amount_applied_from -
amr.acctd_amount_applied_to) * -1 *
app.line_applied)/app.amount_applied),glr.TARGET_CURRENCY_CODE),0)),0)
BULK COLLECT INTO
r_rsob_id
,r_amount_applied
,r_acctd_amount_applied
,r_xchange_gain
,r_xchange_loss
FROM ar_receivable_applications app,
ar_mc_receivable_apps amr,
ar_cash_receipts cr,
gl_ledger_relationships glr,
ra_customer_trx cm,
ra_customer_trx inv
WHERE applied_customer_trx_id = p_customer_trx_id
AND applied_customer_trx_id = inv.customer_trx_id
AND app.status in ('APP')
AND app.cash_receipt_id = cr.cash_receipt_id (+)
AND app.customer_trx_id = cm.customer_trx_id (+)
AND nvl(app.confirmed_flag,'Y') = 'Y'
AND app.display = 'Y'
AND apply_date <= p_as_of_date
AND glr.TARGET_LEDGER_ID = amr.set_of_books_id
AND glr.SOURCE_LEDGER_ID = l_psob_id
AND glr.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND glr.application_id = p_application_id
AND target_ledger_category_code = 'ALC'
AND relationship_type_code = 'SUBLEDGER'
AND app.amount_applied <> 0
AND app.receivable_application_id = amr.receivable_application_id
GROUP BY amr.set_of_books_id;
SELECT target_ledger_id
,0
,0
,0
,0
BULK COLLECT INTO
r_rsob_id
,r_amount_applied
,r_acctd_amount_applied
,r_xchange_gain
,r_xchange_loss
FROM gl_ledger_relationships glr
WHERE glr.source_ledger_id = l_psob_id
AND target_ledger_category_code = 'ALC'
AND relationship_type_code = 'SUBLEDGER'
AND glr.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND glr.application_id = p_application_id;