[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| then update the global variable g_payschedule_rec |
| Return a record ar_receivable_apps_gt type with the amount info |
+-----------------------------------------------------------------------------*/
PROCEDURE upd_inv_ps(
p_app_level IN VARCHAR2,
--
p_source_data_key1 IN VARCHAR2,
p_source_data_key2 IN VARCHAR2,
p_source_data_key3 IN VARCHAR2,
p_source_data_key4 IN VARCHAR2,
p_source_data_key5 IN VARCHAR2,
--
p_ctl_id IN NUMBER,
--
p_line_applied IN NUMBER,
p_tax_applied IN NUMBER,
p_freight_applied IN NUMBER,
p_charges_applied IN NUMBER,
--
p_line_ediscounted IN NUMBER,
p_tax_ediscounted IN NUMBER,
p_freight_ediscounted IN NUMBER,
p_charges_ediscounted IN NUMBER,
--
p_line_uediscounted IN NUMBER,
p_tax_uediscounted IN NUMBER,
p_freight_uediscounted IN NUMBER,
p_charges_uediscounted IN NUMBER,
p_ps_rec IN ar_payment_schedules%ROWTYPE,
--
x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2);
| Procedure insert_rapps_p |
+-----------------------------------------------------------------------------+
| Parameter : |
| p_app_rec variable of type ar_receivable_apps_gt |
+-----------------------------------------------------------------------------+
| Action : insert p_rec_apps in ar_receivable_apps_gt |
+-----------------------------------------------------------------------------*/
PROCEDURE insert_rapps_p
(p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2);
| Procedure delete_application |
+-----------------------------------------------------------------------------+
| Parameter : |
| p_app_rec variable of type ar_receivable_apps_gt |
+-----------------------------------------------------------------------------+
| Action : |
| 1) Call res_inv_ps to restore payment schedule |
| 2) Call res_ctl_rem_amt_for_app to restore the ra_customer_trx_lines_gt |
| amounts |
| 3) Delete the record from ar_receivable_apps_gt |
+-----------------------------------------------------------------------------*/
PROCEDURE delete_application
(p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2);
SELECT SUM( DECODE (activity_bucket,'APP_LINE',amt,0)),
SUM( DECODE (activity_bucket,'APP_TAX' ,amt,0)),
SUM( DECODE (activity_bucket,'APP_FRT' ,amt,0)),
SUM( DECODE (activity_bucket,'APP_CHRG',amt,0)),
SUM( DECODE (activity_bucket,'ED_LINE' ,amt,0)),
SUM( DECODE (activity_bucket,'ED_TAX' ,amt,0)),
SUM( DECODE (activity_bucket,'ED_FRT' ,amt,0)),
SUM( DECODE (activity_bucket,'ED_CHRG' ,amt,0)),
SUM( DECODE (activity_bucket,'UNED_LINE' ,amt,0)),
SUM( DECODE (activity_bucket,'UNED_TAX' ,amt,0)),
SUM( DECODE (activity_bucket,'UNED_FRT' ,amt,0)),
SUM( DECODE (activity_bucket,'UNED_CHRG' ,amt,0))
FROM (SELECT ctl.line_type,
ctl.customer_trx_line_id,
ctl.link_to_cust_trx_line_id,
NVL(ctl.link_to_cust_trx_line_id,ctl.customer_trx_line_id),
amt_tab.amt,
amt_tab.activity_bucket,
amt_tab.ref_account_class
FROM ra_customer_trx_lines_all ctl,
(select SUM(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) amt,
ard.activity_bucket activity_bucket,
ard.ref_account_class ref_account_class,
ard.ref_customer_trx_line_id ref_customer_trx_line_id
from ar_distributions_all ard
WHERE ard.source_table = 'RA'
AND ard.source_id IN
(select receivable_application_id
from ar_receivable_applications_all
where applied_customer_trx_id = g_customer_trx.customer_trx_id)
GROUP BY ard.activity_bucket,
ard.ref_account_class,
ard.ref_customer_trx_line_id) amt_tab
WHERE ctl.customer_trx_line_id = amt_tab.ref_customer_trx_line_id);
SELECT SUM( DECODE (activity_bucket,'APP_LINE',amt,0)),
SUM( DECODE (activity_bucket,'APP_TAX' ,amt,0)),
SUM( DECODE (activity_bucket,'ED_LINE' ,amt,0)),
SUM( DECODE (activity_bucket,'ED_TAX' ,amt,0)),
SUM( DECODE (activity_bucket,'UNED_LINE' ,amt,0)),
SUM( DECODE (activity_bucket,'UNED_TAX' ,amt,0))
FROM (
SELECT ctl.line_type,
ctl.customer_trx_line_id,
ctl.link_to_cust_trx_line_id,
NVL(ctl.link_to_cust_trx_line_id,ctl.customer_trx_line_id),
amt_tab.amt,
amt_tab.activity_bucket,
amt_tab.ref_account_class,
ctl.source_data_key1,
ctl.source_data_key2,
ctl.source_data_key3,
ctl.source_data_key4,
ctl.source_data_key5
FROM ra_customer_trx_lines_gt ctl,
(select SUM(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) amt,
ard.activity_bucket activity_bucket,
ard.ref_account_class ref_account_class,
ard.ref_customer_trx_line_id ref_customer_trx_line_id
from ar_distributions_all ard
WHERE ard.source_table = 'RA'
AND ard.source_id IN
(select receivable_application_id
from ar_receivable_applications_all
where applied_customer_trx_id = g_customer_trx.customer_trx_id)
GROUP BY ard.activity_bucket,
ard.ref_account_class,
ard.ref_customer_trx_line_id) amt_tab
WHERE ctl.customer_trx_line_id = amt_tab.ref_customer_trx_line_id
AND ctl.source_data_key1 = NVL(p_source_data_key1,'00')
AND ctl.source_data_key2 = NVL(p_source_data_key2,'00')
AND ctl.source_data_key3 = NVL(p_source_data_key3,'00')
AND ctl.source_data_key4 = NVL(p_source_data_key4,'00')
AND ctl.source_data_key5 = NVL(p_source_data_key5,'00'));
SELECT app_line,
app_tax,
ed_line,
ed_tax,
uned_line,
uned_tax
FROM(
(SELECT SUM( DECODE (activity_bucket,'APP_LINE',amt,0)) app_line,
SUM( DECODE (activity_bucket,'APP_TAX' ,amt,0)) app_tax,
SUM( DECODE (activity_bucket,'ED_LINE' ,amt,0)) ed_line,
SUM( DECODE (activity_bucket,'ED_TAX' ,amt,0)) ed_tax,
SUM( DECODE (activity_bucket,'UNED_LINE' ,amt,0)) uned_line,
SUM( DECODE (activity_bucket,'UNED_TAX' ,amt,0)) uned_tax,
log_line_id log_line_id
FROM (SELECT ctl.line_type,
ctl.customer_trx_line_id,
ctl.link_to_cust_trx_line_id,
NVL(ctl.link_to_cust_trx_line_id,ctl.customer_trx_line_id) log_line_id,
amt_tab.amt,
amt_tab.activity_bucket,
amt_tab.ref_account_class,
ctl.source_data_key1,
ctl.source_data_key2,
ctl.source_data_key3,
ctl.source_data_key4,
ctl.source_data_key5
FROM ra_customer_trx_lines_gt ctl,
(select SUM(NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0)) amt,
ard.activity_bucket activity_bucket,
ard.ref_account_class ref_account_class,
ard.ref_customer_trx_line_id ref_customer_trx_line_id
from ar_distributions_all ard
WHERE ard.source_table = 'RA'
AND ard.source_id IN
(select receivable_application_id
from ar_receivable_applications_all
where applied_customer_trx_id = g_customer_trx.customer_trx_id)
GROUP BY ard.activity_bucket,
ard.ref_account_class,
ard.ref_customer_trx_line_id) amt_tab
WHERE ctl.customer_trx_line_id = amt_tab.ref_customer_trx_line_id )
GROUP BY log_line_id)) log_line_tab
WHERE log_line_tab.log_line_id = p_log_line_id;
SELECT *
FROM ar_payment_schedules
WHERE class in ('INV','DM') /* Bug 5189370 */
AND customer_trx_id = g_customer_trx.customer_trx_id
AND status = 'OP';
SELECT *
FROM ar_payment_schedules
WHERE class = 'PMT'
AND cash_receipt_id = p_cr_id
AND status = 'OP';
| then update the global variable g_payschedule_rec |
| Return a record ar_receivable_apps_gt type with the amount info |
+-----------------------------------------------------------------------------*/
PROCEDURE upd_inv_ps(
p_app_level IN VARCHAR2,
--
p_source_data_key1 IN VARCHAR2,
p_source_data_key2 IN VARCHAR2,
p_source_data_key3 IN VARCHAR2,
p_source_data_key4 IN VARCHAR2,
p_source_data_key5 IN VARCHAR2,
--
p_ctl_id IN NUMBER,
--
p_line_applied IN NUMBER,
p_tax_applied IN NUMBER,
p_freight_applied IN NUMBER,
p_charges_applied IN NUMBER,
--
p_line_ediscounted IN NUMBER,
p_tax_ediscounted IN NUMBER,
p_freight_ediscounted IN NUMBER,
p_charges_ediscounted IN NUMBER,
--
p_line_uediscounted IN NUMBER,
p_tax_uediscounted IN NUMBER,
p_freight_uediscounted IN NUMBER,
p_charges_uediscounted IN NUMBER,
p_ps_rec IN ar_payment_schedules%ROWTYPE,
--
x_app_rec OUT NOCOPY ar_receivable_apps_gt%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
l_amount_applied NUMBER := 0;
SELECT nvl(AMOUNT_DUE_REMAINING,AMOUNT_DUE_ORIGINAL)
INTO l_amount_due_original
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_ctl_id;
insert_rapps_p(p_app_rec => l_apps_rec,
x_return_status => x_return_status);
| Procedure insert_rapps_p |
+-----------------------------------------------------------------------------+
| Parameter : |
| p_app_rec variable of type ar_receivable_apps_gt |
+-----------------------------------------------------------------------------+
| Action : insert p_rec_apps in ar_receivable_apps_gt |
+-----------------------------------------------------------------------------*/
PROCEDURE insert_rapps_p
(p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
arp_standard.debug('insert_rapps_p +');
INSERT INTO ar_receivable_apps_gt
(GT_ID
,app_level
,source_data_key1
,source_data_key2
,source_data_key3
,source_data_key4
,source_data_key5
,ctl_id
,RECEIVABLE_APPLICATION_ID
,AMOUNT_APPLIED
,CODE_COMBINATION_ID
,SET_OF_BOOKS_ID
,APPLICATION_TYPE
,PAYMENT_SCHEDULE_ID
,CASH_RECEIPT_ID
,APPLIED_CUSTOMER_TRX_ID
,APPLIED_CUSTOMER_TRX_LINE_ID
,APPLIED_PAYMENT_SCHEDULE_ID
,CUSTOMER_TRX_ID
,LINE_APPLIED
,TAX_APPLIED
,FREIGHT_APPLIED
,RECEIVABLES_CHARGES_APPLIED
,EARNED_DISCOUNT_TAKEN
,UNEARNED_DISCOUNT_TAKEN
,APPLICATION_RULE
,ACCTD_AMOUNT_APPLIED_FROM
,ACCTD_AMOUNT_APPLIED_TO
,ACCTD_EARNED_DISCOUNT_TAKEN
,EARNED_DISCOUNT_CCID
,UNEARNED_DISCOUNT_CCID
,ACCTD_UNEARNED_DISCOUNT_TAKEN
,ORG_ID
,AMOUNT_APPLIED_FROM
,RULE_SET_ID
,LINE_EDISCOUNTED
,TAX_EDISCOUNTED
,FREIGHT_EDISCOUNTED
,CHARGES_EDISCOUNTED
,LINE_UEDISCOUNTED
,TAX_UEDISCOUNTED
,FREIGHT_UEDISCOUNTED
,CHARGES_UEDISCOUNTED) VALUES
(p_app_rec.GT_ID
,p_app_rec.app_level
,p_app_rec.source_data_key1
,p_app_rec.source_data_key2
,p_app_rec.source_data_key3
,p_app_rec.source_data_key4
,p_app_rec.source_data_key5
,p_app_rec.ctl_id
,p_app_rec.RECEIVABLE_APPLICATION_ID
,p_app_rec.AMOUNT_APPLIED
,p_app_rec.CODE_COMBINATION_ID
,p_app_rec.SET_OF_BOOKS_ID
,p_app_rec.APPLICATION_TYPE
,p_app_rec.PAYMENT_SCHEDULE_ID
,p_app_rec.CASH_RECEIPT_ID
,p_app_rec.APPLIED_CUSTOMER_TRX_ID
,p_app_rec.APPLIED_CUSTOMER_TRX_LINE_ID
,p_app_rec.APPLIED_PAYMENT_SCHEDULE_ID
,p_app_rec.CUSTOMER_TRX_ID
,p_app_rec.LINE_APPLIED
,p_app_rec.TAX_APPLIED
,p_app_rec.FREIGHT_APPLIED
,p_app_rec.RECEIVABLES_CHARGES_APPLIED
,p_app_rec.EARNED_DISCOUNT_TAKEN
,p_app_rec.UNEARNED_DISCOUNT_TAKEN
,p_app_rec.APPLICATION_RULE
,p_app_rec.ACCTD_AMOUNT_APPLIED_FROM
,p_app_rec.ACCTD_AMOUNT_APPLIED_TO
,p_app_rec.ACCTD_EARNED_DISCOUNT_TAKEN
,p_app_rec.EARNED_DISCOUNT_CCID
,p_app_rec.UNEARNED_DISCOUNT_CCID
,p_app_rec.ACCTD_UNEARNED_DISCOUNT_TAKEN
,p_app_rec.ORG_ID
,p_app_rec.AMOUNT_APPLIED_FROM
,p_app_rec.RULE_SET_ID
,p_app_rec.LINE_EDISCOUNTED
,p_app_rec.TAX_EDISCOUNTED
,p_app_rec.FREIGHT_EDISCOUNTED
,p_app_rec.CHARGES_EDISCOUNTED
,p_app_rec.LINE_UEDISCOUNTED
,p_app_rec.TAX_UEDISCOUNTED
,p_app_rec.FREIGHT_UEDISCOUNTED
,p_app_rec.CHARGES_UEDISCOUNTED);
arp_standard.debug('insert_rapps_p -');
arp_standard.debug('EXCEPTION insert_rapps_p OTHERS:'||SQLERRM);
FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS insert_rapps_p:'||SQLERRM );
END insert_rapps_p;
SELECT SUM(DECODE(b.ref_account_class,
'REV',
DECODE(b.REF_DET_ID,NULL,b.AMOUNT,0),
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE AMOUNT_DUE_REMAINING
SUM(DECODE(b.ref_account_class,
'REV',
DECODE(b.REF_DET_ID,NULL,b.ACCTD_AMOUNT,0),
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE ACCTD_AMOUNT_DUE_REMAINING
SUM(DECODE(b.ref_account_class,
'REV',
DECODE(b.REF_DET_ID,NULL,0,
DECODE(b.SOURCE_TYPE,'FREIGHT',b.AMOUNT,0)),
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE FRT_ADJ_REMAINING
SUM(DECODE(b.ref_account_class,
'REV',
DECODE(b.REF_DET_ID,NULL,0,
DECODE(b.SOURCE_TYPE,'FREIGHT',b.ACCTD_AMOUNT,0)),
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE FRT_ADJ_ACCTD_REMAINING
SUM(DECODE(b.ref_account_class,
'REV',
DECODE(b.REF_DET_ID,NULL,0,
DECODE(b.SOURCE_TYPE,'CHARGES',b.AMOUNT,0)),
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE CHRG_ADJ_REMAINING
SUM(DECODE(b.ref_account_class,
'REV',
DECODE(b.REF_DET_ID,NULL,0,
DECODE(b.SOURCE_TYPE,'CHARGES',b.ACCTD_AMOUNT,0)),
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR REV LINE CHRG_ADJ_ACCTD_REMAINING
SUM(DECODE(b.ref_account_class,
'TAX',
b.AMOUNT,
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR TAX
SUM(DECODE(b.ref_account_class,
'TAX',
b.ACCTD_AMOUNT,
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR ACCTD TAX
SUM(DECODE(b.ref_account_class,
'FREIGHT',
b.AMOUNT,
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR FREIGHT
SUM(DECODE(b.ref_account_class,
'FREIGHT',
b.ACCTD_AMOUNT,
0))
OVER (PARTITION BY b.ref_customer_trx_line_id), -- FOR ACCTD FREIGHT
b.REF_CUSTOMER_TRX_LINE_ID,
c.line_type
FROM AR_LINE_APP_DETAIL_GT b,
ra_customer_trx_lines_gt c
WHERE b.gt_id = p_app_rec.gt_id
AND b.app_level = p_app_rec.app_level
AND b.REF_CUSTOMER_TRX_LINE_ID = c.customer_trx_line_id;
UPDATE ra_customer_trx_lines_gt
SET AMOUNT_DUE_REMAINING =
DECODE(l_line_type_tab(i),
'LINE', AMOUNT_DUE_REMAINING + l_rev_amt_rem_tab(i),
'FREIGHT',AMOUNT_DUE_REMAINING + l_frt_amt_rem_tab(i),
'TAX', AMOUNT_DUE_REMAINING + l_tax_amt_rem_tab(i),
AMOUNT_DUE_REMAINING),
ACCTD_AMOUNT_DUE_REMAINING =
DECODE(l_line_type_tab(i),
'LINE', ACCTD_AMOUNT_DUE_REMAINING + l_rev_acctd_amt_rem_tab(i),
'FREIGHT',ACCTD_AMOUNT_DUE_REMAINING + l_frt_acctd_amt_rem_tab(i),
'TAX', ACCTD_AMOUNT_DUE_REMAINING + l_tax_acctd_amt_rem_tab(i),
ACCTD_AMOUNT_DUE_REMAINING),
FRT_ADJ_REMAINING =
FRT_ADJ_REMAINING + l_frt_adj_amt_rem_tab(i),
FRT_ADJ_ACCTD_REMAINING =
FRT_ADJ_ACCTD_REMAINING + l_frt_adj_acctd_amt_rem_tab(i),
CHRG_AMOUNT_REMAINING =
CHRG_AMOUNT_REMAINING + l_chrg_adj_amt_rem_tab(i),
CHRG_ACCTD_AMOUNT_REMAINING =
CHRG_ACCTD_AMOUNT_REMAINING + l_chrg_adj_acctd_amt_rem_tab(i)
WHERE customer_trx_line_id = l_ctl_id_tab(i);
| Procedure delete_application |
+-----------------------------------------------------------------------------+
| Parameter : |
| p_app_rec variable of type ar_receivable_apps_gt |
+-----------------------------------------------------------------------------+
| Action : |
| 1) Call res_inv_ps to restore payment schedule |
| 2) Call res_ctl_rem_amt_for_app to restore the ra_customer_trx_lines_gt |
| amounts |
| 3) Delete the record from ar_receivable_apps_gt |
+-----------------------------------------------------------------------------*/
PROCEDURE delete_application
(p_app_rec IN ar_receivable_apps_gt%ROWTYPE,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
arp_standard.debug('delete_application +');
DELETE FROM ar_receivable_apps_gt
WHERE gt_id = p_app_rec.gt_id
AND app_level = p_app_rec.app_level;
DELETE FROM AR_LINE_APP_DETAIL_GT
WHERE gt_id = p_app_rec.gt_id
AND app_level = p_app_rec.app_level;
arp_standard.debug('delete_application -');
arp_standard.debug('EXCEPTION delete_application OTHERS:'||SQLERRM);
FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS delete_application:'||SQLERRM );
END delete_application;
SELECT *
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT *
FROM ar_cash_receipts
WHERE cash_receipt_id = p_cash_receipt_id;
SELECT sob.set_of_books_id,
sob.chart_of_accounts_id,
sob.currency_code,
c.precision,
c.minimum_accountable_unit,
sysp.code_combination_id_gain,
sysp.code_combination_id_loss,
sysp.code_combination_id_round
FROM ar_system_parameters sysp,
gl_sets_of_books sob,
fnd_currencies c
WHERE sob.set_of_books_id = sysp.set_of_books_id
AND sob.currency_code = c.currency_code;
SELECT rma.unapplied_ccid
, ed.code_combination_id
, uned.code_combination_id
, rma.unidentified_ccid
, rma.receipt_clearing_ccid
, rma.remittance_ccid
, rma.cash_ccid
, rma.on_account_ccid
, rma.factor_ccid
, ctlgd.code_combination_id
FROM ar_cash_receipts cr
, ar_cash_receipt_history crh
, ar_receipt_methods rm
, ce_bank_acct_uses aba
, ce_bank_branches_v bp
, ce_bank_accounts cba
, ar_receipt_method_accounts rma
, ar_receivables_trx ed
, ar_receivables_trx uned
, ra_cust_trx_line_gl_dist ctlgd
WHERE cr.cash_receipt_id = p_cash_receipt_id
AND cr.cash_receipt_id = crh.cash_receipt_id
AND crh.current_record_flag = 'Y'
AND rm.receipt_method_id = cr.receipt_method_id
AND cr.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
AND bp.branch_party_id = cba.bank_branch_id
AND rma.remit_bank_acct_use_id = aba.bank_acct_use_id
AND rma.receipt_method_id = rm.receipt_method_id
AND rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
AND rma.unedisc_receivables_trx_id= uned.receivables_trx_id (+)
AND ctlgd.customer_trx_id = p_customer_trx_id
AND ctlgd.account_class = 'REC';
SELECT ar_receivable_applications_s.nextval
INTO g_app_ra_id
FROM dual;
SELECT *
FROM ar_receivable_apps_gt
WHERE app_level = 'TRANSACTION';
SELECT *
FROM ar_receivable_apps_gt
WHERE app_level = 'GROUP'
AND source_data_key1 = p_source_data_key1
AND source_data_key2 = p_source_data_key2
AND source_data_key3 = p_source_data_key3
AND source_data_key4 = p_source_data_key4
AND source_data_key5 = p_source_data_key5;
SELECT *
FROM ar_receivable_apps_gt
WHERE app_level = 'LINE'
AND ctl_id = p_ctl_id;
| 2) If found then call delete_application |
| 3) Call apply to do the application |
+-----------------------------------------------------------------------------*/
PROCEDURE application_execute
( p_app_level IN VARCHAR2,
p_source_data_key1 IN VARCHAR2,
p_source_data_key2 IN VARCHAR2,
p_source_data_key3 IN VARCHAR2,
p_source_data_key4 IN VARCHAR2,
p_source_data_key5 IN VARCHAR2,
p_ctl_id IN NUMBER,
--
p_line_applied IN NUMBER,
p_tax_applied IN NUMBER,
p_freight_applied IN NUMBER,
p_charges_applied IN NUMBER,
--
p_line_ediscounted IN NUMBER,
p_tax_ediscounted IN NUMBER,
p_freight_ediscounted IN NUMBER,
p_charges_ediscounted IN NUMBER,
--
p_line_uediscounted IN NUMBER,
p_tax_uediscounted IN NUMBER,
p_freight_uediscounted IN NUMBER,
p_charges_uediscounted IN NUMBER,
--
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
cur_gt_id VARCHAR2(30);
SAVEPOINT first_delete_then_apply;
delete_application
(p_app_rec => l_app_rec,
x_return_status => x_return_status);
ROLLBACK TO first_delete_then_apply;
ROLLBACK TO first_delete_then_apply;
arp_standard.debug('EXCEPTION first_delete_then_apply unexpected_error - p_app_level:'
||p_app_level||' - p_source_data_key1 :'||p_source_data_key1 ||' - p_ctl_id :'||p_ctl_id);
FND_MESSAGE.SET_TOKEN( 'TEXT', 'Unexpected first_delete_then_apply - p_app_level:'
||p_app_level||' - p_source_data_key1 :'||p_source_data_key1 ||' - p_ctl_id :'||p_ctl_id);
ROLLBACK TO first_delete_then_apply;
arp_standard.debug('EXCEPTION first_delete_then_apply OTHERS:'||SQLERRM);
FND_MESSAGE.SET_TOKEN( 'TEXT', 'OTHERS first_delete_then_apply:'||SQLERRM );
SELECT SUM(AMOUNT_APPLIED),
SUM(AMOUNT_APPLIED_FROM),
SUM(EARNED_DISCOUNT_TAKEN),
SUM(UNEARNED_DISCOUNT_TAKEN),
SUM(LINE_APPLIED),
SUM(TAX_APPLIED),
SUM(FREIGHT_APPLIED),
SUM(RECEIVABLES_CHARGES_APPLIED),
SUM(EARNED_DISCOUNT_TAKEN),
SUM(UNEARNED_DISCOUNT_TAKEN),
MAX(ACCTD_AMOUNT_APPLIED_FROM),
SUM(ACCTD_AMOUNT_APPLIED_TO),
SUM(ACCTD_EARNED_DISCOUNT_TAKEN),
SUM(ACCTD_UNEARNED_DISCOUNT_TAKEN),
MAX(AMOUNT_APPLIED_FROM),
SUM(LINE_EDISCOUNTED),
SUM(TAX_EDISCOUNTED),
SUM(FREIGHT_EDISCOUNTED),
SUM(CHARGES_EDISCOUNTED),
SUM(LINE_UEDISCOUNTED),
SUM(TAX_UEDISCOUNTED),
SUM(FREIGHT_UEDISCOUNTED),
SUM(CHARGES_UEDISCOUNTED),
MAX(receivable_application_id)
FROM ar_receivable_apps_gt
WHERE gt_id = p_gt_id;
SELECT SUM(AMOUNT_APPLIED),
SUM(AMOUNT_APPLIED_FROM),
SUM(EARNED_DISCOUNT_TAKEN),
SUM(UNEARNED_DISCOUNT_TAKEN),
SUM(LINE_APPLIED),
SUM(TAX_APPLIED),
SUM(FREIGHT_APPLIED),
SUM(RECEIVABLES_CHARGES_APPLIED),
SUM(EARNED_DISCOUNT_TAKEN),
SUM(UNEARNED_DISCOUNT_TAKEN),
MAX(ACCTD_AMOUNT_APPLIED_FROM),
SUM(ACCTD_AMOUNT_APPLIED_TO),
SUM(ACCTD_EARNED_DISCOUNT_TAKEN),
SUM(ACCTD_UNEARNED_DISCOUNT_TAKEN),
MAX(AMOUNT_APPLIED_FROM),
SUM(LINE_EDISCOUNTED),
SUM(TAX_EDISCOUNTED),
SUM(FREIGHT_EDISCOUNTED),
SUM(CHARGES_EDISCOUNTED),
SUM(LINE_UEDISCOUNTED),
SUM(TAX_UEDISCOUNTED),
SUM(FREIGHT_UEDISCOUNTED),
SUM(CHARGES_UEDISCOUNTED),
MAX(receivable_application_id)
FROM ar_receivable_apps_gt
WHERE applied_customer_trx_id = g_customer_trx.customer_trx_id;
UPDATE ar_line_app_detail_gt
SET gt_id = USERENV('SESSIONID')
WHERE gt_id LIKE USERENV('SESSIONID')||'%';
UPDATE ar_receivable_apps_gt
SET gt_id = USERENV('SESSIONID')
WHERE gt_id LIKE USERENV('SESSIONID')||'%';
arp_det_dist_pkg.final_update_inv_ctl_rem_orig
(p_customer_trx => g_customer_trx);
arp_ps_pkg.update_p( g_payschedule_trx);
arp_det_dist_pkg.final_update_inv_ctl_rem_orig(p_customer_trx =>g_customer_trx);
DELETE FROM ra_customer_trx_lines_gt WHERE customer_trx_id = g_customer_trx.customer_trx_id;
DELETE FROM ra_ar_gt WHERE gt_id = TO_CHAR(USERENV('SESSIONID'));
DELETE FROM ar_line_app_detail_gt WHERE gt_id = TO_CHAR(USERENV('SESSIONID'));
DELETE FROM ar_receivable_apps_gt where gt_id = TO_CHAR(USERENV('SESSIONID')); /* 5438627 */
DELETE FROM ar_ae_alloc_rec_gt where ae_id = TO_CHAR(USERENV('SESSIONID')); /* 5438627 */
SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
FROM ra_customer_trx_lines_gt
WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id;
SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
FROM ra_customer_trx_lines_gt
WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id
AND DECODE(line_type,'LINE',customer_trx_line_id, LINK_TO_CUST_TRX_LINE_ID) = p_ctl_id;
SELECT SUM(DECODE(line_type,'LINE',NVL(AMOUNT_DUE_REMAINING,0))) line_rem,
SUM(DECODE(line_type,'TAX' ,NVL(AMOUNT_DUE_REMAINING,0))) tax_rem,
SUM(DECODE(line_type,'LINE',NVL(CHRG_AMOUNT_REMAINING,0))) chrg_rem,
SUM(DECODE(line_type,'LINE',NVL(FRT_ADJ_REMAINING,0))) +
SUM(DECODE(line_type,'FREIGHT',NVL(AMOUNT_DUE_REMAINING,0))) frt_rem
FROM ra_customer_trx_lines_gt
WHERE CUSTOMER_TRX_ID = g_customer_trx.customer_trx_id
AND source_data_key1 = NVL(p_source_data_key1,'00')
AND source_data_key2 = NVL(p_source_data_key2,'00')
AND source_data_key3 = NVL(p_source_data_key3,'00')
AND source_data_key4 = NVL(p_source_data_key4,'00')
AND source_data_key5 = NVL(p_source_data_key5,'00');
SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
SUM(NVL(TAX_APPLIED,0)) app_tax,
SUM(NVL(FREIGHT_APPLIED,0)) app_frt,
SUM(NVL(RECEIVABLES_CHARGES_APPLIED,0)) app_chrg,
SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
SUM(NVL(FREIGHT_EDISCOUNTED,0)) ed_frt,
SUM(NVL(CHARGES_EDISCOUNTED,0)) ed_chrg,
SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax,
SUM(NVL(FREIGHT_UEDISCOUNTED,0)) uned_frt,
SUM(NVL(CHARGES_UEDISCOUNTED,0)) uned_chrg
FROM ar_receivable_apps_gt;
SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
SUM(NVL(TAX_APPLIED,0)) app_tax,
SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax
FROM ar_receivable_apps_gt
WHERE SOURCE_DATA_KEY1 = NVL(p_source_data_key1,'00')
AND SOURCE_DATA_KEY2 = NVL(p_source_data_key2,'00')
AND SOURCE_DATA_KEY3 = NVL(p_source_data_key3,'00')
AND SOURCE_DATA_KEY4 = NVL(p_source_data_key4,'00')
AND SOURCE_DATA_KEY5 = NVL(p_source_data_key5,'00');
SELECT SUM(NVL(LINE_APPLIED,0)) app_line,
SUM(NVL(TAX_APPLIED,0)) app_tax,
SUM(NVL(LINE_EDISCOUNTED,0)) ed_line,
SUM(NVL(TAX_EDISCOUNTED,0)) ed_tax,
SUM(NVL(LINE_UEDISCOUNTED,0)) uned_line,
SUM(NVL(TAX_UEDISCOUNTED,0)) uned_tax
FROM ar_receivable_apps_gt
WHERE CTL_ID = p_ctl_id;
SELECT DECODE(line_type,'LINE','OK',line_type)
FROM ra_customer_trx_lines_gt
WHERE customer_trx_line_id = p_ctl_id;
SELECT SUM(DECODE(p_activity,
'APP' ,DECODE(p_line_type,'LINE', NVL(line_amount,0),
'TAX' , NVL(tax_amount,0) ,0),
'ADJ' ,DECODE(p_line_type,'LINE', NVL(line_amount,0),
'TAX' , NVL(tax_amount,0) ,0),
'ED' ,DECODE(p_line_type,'LINE', NVL(ed_line_amount,0),
'TAX' , NVL(ed_tax_amount,0) ,0),
'UNED' ,DECODE(p_line_type,'LINE', NVL(uned_line_amount,0),
'TAX' , NVL(uned_tax_amount,0) ,0),0))
FROM ar_line_dist_interface_gt
WHERE customer_trx_id = p_customer_trx_id
AND gt_id = p_gt_id;
SELECT DECODE(p_activity,'APP' ,DECODE(line_type,'LINE',line_amount , 'TAX',tax_amount,NULL),
'ED' ,DECODE(line_type,'LINE',ed_line_amount , 'TAX',ed_tax_amount,NULL),
'UNED',DECODE(line_type,'LINE',uned_line_amount, 'TAX',uned_tax_amount,NULL),NULL)
FROM ar_line_dist_interface_gt
WHERE customer_trx_id = p_customer_trx_id
AND customer_trx_line_id = p_customer_trx_line_id
AND gt_id = p_gt_id
AND line_type = p_line_type;
SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
CASE WHEN p_line_flag = 'INTERFACE' THEN SUM(NVL(line_amount,0)) ELSE NULL END
,CASE WHEN p_tax_flag = 'INTERFACE' THEN SUM(NVL(tax_amount,0)) ELSE NULL END
FROM ar_line_dist_interface_gt
WHERE gt_id = p_gt_id
AND customer_trx_id = p_customer_trx.customer_trx_id
AND source_table = 'ADJ';
SELECT /*+INDEX (ar_line_dist_interface_gt ar_line_dist_interface_gt_n1)*/
CASE WHEN p_line_flag = 'INTERFACE' THEN SUM(NVL(line_amount,0)) ELSE NULL END
,CASE WHEN p_tax_flag = 'INTERFACE' THEN SUM(NVL(tax_amount,0)) ELSE NULL END
,CASE WHEN p_ed_line_flag = 'INTERFACE' THEN SUM(NVL(ed_line_amount,0)) ELSE NULL END
,CASE WHEN p_ed_tax_flag = 'INTERFACE' THEN SUM(NVL(ed_tax_amount,0)) ELSE NULL END
,CASE WHEN p_uned_line_flag = 'INTERFACE' THEN SUM(NVL(uned_line_amount,0)) ELSE NULL END
,CASE WHEN p_uned_tax_flag = 'INTERFACE' THEN SUM(NVL(uned_tax_amount,0)) ELSE NULL END
FROM ar_line_dist_interface_gt
WHERE gt_id = p_gt_id
AND customer_trx_id = p_customer_trx.customer_trx_id
AND source_table = 'RA';