[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_RA(p_ae_deleted OUT NOCOPY BOOLEAN);
PROCEDURE Delete_MCD(p_ae_deleted OUT NOCOPY BOOLEAN);
| PUBLIC PROCEDURE Delete_Acct
|
| DESCRIPTION
| Accounting Entry Deletion
| -------------------------
| This procedure is the Accounting Entry deletion routine which
| deletes data associated with Receipts based on event and source
| table.
|
| PARAMETERS
| p_mode IN Document or Accounting Event mode
| p_ae_doc_rec IN Document Record
| p_ae_event_rec IN Event Record
| p_ae_deleted OUT NOCOPY AE Lines deletion status
* ======================================================================*/
PROCEDURE Delete_Acct( p_mode IN VARCHAR2,
p_ae_doc_rec IN OUT NOCOPY ae_doc_rec_type,
p_ae_event_rec IN ae_event_rec_type,
p_ae_deleted OUT NOCOPY BOOLEAN ) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
arp_standard.debug( 'ARP_RECEIPTS_MAIN.Delete_Acct()+');
Delete_RA(p_ae_deleted => p_ae_deleted) ;
| If paired id of deleted UNAPP record is returned if delete is |
| followed by a create for update of a UNAPP record |
+---------------------------------------------------------------*/
p_ae_doc_rec.source_id_old := g_ae_doc_rec.source_id_old ;
Delete_MCD(p_ae_deleted => p_ae_deleted) ;
arp_standard.debug( 'ARP_RECEIPTS_MAIN.Delete_Acct()-');
arp_standard.debug( 'EXCEPTION: ARP_RECEIPTS_MAIN.Delete_Acct');
END Delete_Acct;
| PROCEDURE Delete_RA
|
| DESCRIPTION
| Deletes accounting associated with a Receivable application id
| from the AR_DISTRIBUTIONS table.This routine deletes all records
| matching the input source_id. Note records from child table
| (AR_DISTRIBUTIONS) be deleted first.
|
| PARAMETERS
| p_ae_deleted indicates whether records were deleted
| for source_id
* ======================================================================*/
PROCEDURE Delete_RA(p_ae_deleted OUT NOCOPY BOOLEAN) IS
l_status ar_receivable_applications.status%TYPE;
arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_RA()+');
SELECT ra.status
INTO l_status
FROM ar_receivable_applications ra
WHERE ra.receivable_application_id = g_ae_doc_rec.source_id
/* bug 1454382 : when a receipt application is unapplied, the reversal_gl_date is populated
even when it's only a reversal of the application and not the whole receipt, hence, this
condition was causing an EXCEPTION when the receipt was being deleted.
Fix is to comment out NOCOPY the following line.
AND ra.reversal_gl_date is null --Not rate adjusted or reversed
*/
AND ra.posting_control_id = -3
AND g_ae_doc_rec.source_table = 'RA'
AND nvl(ra.confirmed_flag,'Y') = 'Y' ;
| is necessary as in update mode delete is called first and then create|
+----------------------------------------------------------------------*/
IF l_status = 'UNAPP' THEN
SELECT ard.source_id_secondary
INTO g_ae_doc_rec.source_id_old
FROM ar_distributions ard
where ard.source_id = g_ae_doc_rec.source_id
and ard.source_table = 'RA';
| Delete all accounting for source id and source table combination |
| if valid candidate for deletion |
+-------------------------------------------------------------------*/
-- MRC Trigger Elimination:
DELETE FROM AR_DISTRIBUTIONS
WHERE source_id = g_ae_doc_rec.source_id
AND source_table = 'RA'
RETURNING line_id
BULK COLLECT INTO l_ar_dist_key_value_list;
arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_RA()-');
arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_RA - NO_DATA_FOUND' );
p_ae_deleted := FALSE;
arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Delete_RA - OTHERS');
p_ae_deleted := FALSE;
END Delete_RA;
| PROCEDURE Delete_MCD
|
| DESCRIPTION
| Deletes accounting associated with a Miscellaneous Cash Receipt or
| Payment. Note record from child (AR_DISTRIBUTIONS) table must be
| deleted first.
|
| PARAMETERS
| p_ae_deleted indicates whether records were deleted
| for source_id
* ======================================================================*/
PROCEDURE Delete_MCD(p_ae_deleted OUT NOCOPY BOOLEAN) IS
CURSOR del_misc_rec IS
SELECT mcd.misc_cash_distribution_id misc_dist_id
FROM ar_misc_cash_distributions mcd
WHERE mcd.cash_receipt_id = g_ae_doc_rec.document_id
AND mcd.reversal_gl_date IS NULL --For rate adjustments picks up records with new rate not those reversed
AND mcd.posting_control_id = -3 --Not posted
AND EXISTS (SELECT 'x'
FROM ar_distributions ard
WHERE ard.source_id = mcd.misc_cash_distribution_id
AND ard.source_table = 'MCD');
arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_MCD()+');
p_ae_deleted := FALSE;
DELETE FROM ar_distributions ard
WHERE ard.source_id = l_misc_rec.misc_dist_id
AND ard.source_table = 'MCD'
RETURNING line_id
BULK COLLECT INTO l_ar_dist_key_value_list;
p_ae_deleted := TRUE;
SELECT 'x'
INTO l_dummy
FROM dual
WHERE l_dummy = 'Y';
arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_MCD()-');
arp_standard.debug('ARP_RECEIPTS_MAIN.Delete_MCD - NO_DATA_FOUND' );
p_ae_deleted := FALSE;
arp_standard.debug('EXCEPTION: ARP_RECEIPTS_MAIN.Delete_MCD');
p_ae_deleted := FALSE;
END Delete_MCD;
select ard.line_id,
ard.source_type,
ard.source_id_secondary,
ard.source_type_secondary,
ard.source_table_secondary,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.taxable_entered_cr,
ard.taxable_entered_dr,
ard.taxable_accounted_cr,
ard.taxable_accounted_dr,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_group_code_id,
ard.tax_code_id,
ard.location_segment_id,
ard.tax_link_id,
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
--{2979254 ref_dist_ccid and ref_dist_flag
ard.ref_dist_ccid,
ard.ref_mf_dist_flag,
ard.ref_account_class,
ard.activity_bucket,
--}
--{3377004
ard.from_amount_dr,
ard.from_amount_cr,
ard.from_acctd_amount_dr,
ard.from_acctd_amount_cr,
DECODE(ard.ref_customer_trx_line_id, NULL, 'N',
DECODE(ard.ref_line_id, NULL, 'ADDCTL',
DECODE(line_trx_type.line_type,'CHARGES','ADDCTL',
DECODE(adjsrctp.source_type,'CHARGES','ADDCHRG'
,'FINCHRG','ADDCHRG'
,'ADDCTL')))) WHICH_BUCKET,
line_trx_type.type trx_type
--}
from ar_distributions ard,
ar_distributions adjsrctp,
--{ For CM APP on legacy from 11i
(SELECT tt.type type,
ctl.customer_trx_line_id customer_trx_line_id,
ctl.line_type line_type
FROM ra_customer_trx_lines ctl,
ra_customer_trx trx,
ra_cust_trx_types tt
WHERE ctl.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = tt.cust_trx_type_id
AND tt.org_id = trx.org_id) line_trx_type
--}
where g_ae_sys_rec.sob_type = 'P'
and ard.source_id = g_ae_doc_rec.source_id_old
and ard.source_table = g_ae_doc_rec.source_table
and nvl(ard.source_type_secondary,'X') NOT IN
('ASSIGNMENT_RECONCILE','RECONCILE')
and ard.ref_line_id = adjsrctp.line_id(+) --3377004
and ard.ref_customer_trx_line_id = line_trx_type.customer_trx_line_id(+)
UNION
select ard.line_id,
ard.source_type,
ard.source_id_secondary,
ard.source_type_secondary,
ard.source_table_secondary,
ard.code_combination_id,
ard.amount_dr,
ard.amount_cr,
ard.acctd_amount_dr,
ard.acctd_amount_cr,
ard.taxable_entered_cr,
ard.taxable_entered_dr,
ard.taxable_accounted_cr,
ard.taxable_accounted_dr,
ard.currency_code,
ard.currency_conversion_rate,
ard.currency_conversion_type,
ard.currency_conversion_date,
ard.third_party_id,
ard.third_party_sub_id,
ard.tax_group_code_id,
ard.tax_code_id,
ard.location_segment_id,
ard.tax_link_id,
ard.ref_customer_trx_line_id,
ard.ref_cust_trx_line_gl_dist_id,
ard.ref_line_id,
--{2979254 ref_dist_ccid and ref_dist_flag
ard.ref_dist_ccid,
ard.ref_mf_dist_flag,
ard.ref_account_class,
ard.activity_bucket,
--}
--{3377004
ard.from_amount_dr,
ard.from_amount_cr,
ard.from_acctd_amount_dr,
ard.from_acctd_amount_cr,
DECODE(ard.ref_customer_trx_line_id, NULL, 'N',
DECODE(ard.ref_line_id, NULL, 'ADDCTL',
DECODE(line_trx_type.line_type,'CHARGES','ADDCTL',
DECODE(adjsrctp.source_type,'CHARGES','ADDCHRG'
,'FINCHRG','ADDCHRG'
,'ADDCTL')))) WHICH_BUCKET,
line_trx_type.type trx_type
--}
from ar_distributions ard,
ar_receivable_applications app,
ar_distributions adjsrctp,
-- For CM APP on legacy from 11i
(SELECT tt.type type,
ctl.customer_trx_line_id customer_trx_line_id,
ctl.line_type line_type
FROM ra_customer_trx_lines ctl,
ra_customer_trx trx,
ra_cust_trx_types tt
WHERE ctl.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = tt.cust_trx_type_id
AND tt.org_id = trx.org_id) line_trx_type
--}
where g_ae_sys_rec.sob_type = 'P'
and app.receivable_application_id = g_ae_doc_rec.source_id_old
and ard.source_id = app.link_to_trx_hist_id
and nvl(ard.source_type_secondary,'X') NOT IN
('ASSIGNMENT_RECONCILE','RECONCILE')
and ard.source_table = 'TH' --for Bills Receivable Standard/Factored
and nvl(g_ae_doc_rec.event,'NONE') <> 'RISK_UNELIMINATED'
and ard.ref_line_id = adjsrctp.line_id(+) --3377004
and ard.ref_customer_trx_line_id = line_trx_type.customer_trx_line_id(+)
order by 1;
SELECT trx.upgrade_method trx_upgrade_method,
app.applied_customer_trx_id trx_id,
cm.upgrade_method cm_upgrade_method,
app.customer_trx_id cm_id
FROM ar_receivable_applications app,
ra_customer_trx trx,
ra_customer_trx cm
WHERE app.receivable_application_id = p_app_id
AND app.applied_customer_trx_id = trx.customer_trx_id
AND app.customer_trx_id = cm.customer_trx_id(+);
| as the main select routine for document could be used, however |
| did not want to destabilize any logic in Get_Doc_Entitity_Data |
+-------------------------------------------------------------------*/
CURSOR get_app_details IS
SELECT app.applied_customer_trx_id applied_customer_trx_id,
app.application_type application_type,
app.amount_applied + nvl(app.earned_discount_taken,0)
+ nvl(app.unearned_discount_taken,0) amount_applied ,
nvl(app.acctd_amount_applied_to,0) +
nvl(app.acctd_earned_discount_taken,0) +
nvl(app.acctd_unearned_discount_taken,0) acctd_amount_applied_to,
app.customer_trx_id customer_trx_id,
app.acctd_amount_applied_from acctd_amount_applied_from,
ctinv.invoice_currency_code invoice_currency_code,
ctinv.exchange_rate exchange_rate,
ctinv.exchange_rate_type exchange_rate_type,
ctinv.exchange_date exchange_date,
ctinv.trx_date trx_date,
ctinv.bill_to_customer_id bill_to_customer_id,
ctinv.bill_to_site_use_id bill_to_site_use_id,
ctinv.drawee_id drawee_id,
ctinv.drawee_site_use_id drawee_site_use_id,
ctcm.invoice_currency_code cm_invoice_currency_code,
ctcm.exchange_rate cm_exchange_rate,
ctcm.exchange_rate_type cm_exchange_rate_type,
ctcm.exchange_date cm_exchange_date,
ctcm.trx_date cm_trx_date,
ctcm.bill_to_customer_id cm_bill_to_customer_id,
ctcm.bill_to_site_use_id cm_bill_to_site_use_id
from ar_receivable_applications app ,
ra_customer_trx ctinv,
ra_customer_trx ctcm
where app.receivable_application_id = g_ae_doc_rec.source_id_old
and app.status = 'APP'
and nvl(confirmed_flag,'Y') = 'Y'
and g_ae_doc_rec.source_table = 'RA'
and app.applied_customer_trx_id = ctinv.customer_trx_id
and app.customer_trx_id = ctcm.customer_trx_id (+);
UPDATE ar_receivable_applications
SET upgrade_method = 'R12'
WHERE receivable_application_id = g_ae_doc_rec.source_id;
UPDATE ra_customer_trx_lines
SET AMOUNT_DUE_REMAINING = AMOUNT_DUE_REMAINING + l_ctl_rem_amt.amount_due_remaining(m),
ACCTD_AMOUNT_DUE_REMAINING = ACCTD_AMOUNT_DUE_REMAINING + l_ctl_rem_amt.acctd_amount_due_remaining(m),
CHRG_AMOUNT_REMAINING = CHRG_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_amount_remaining(m),
CHRG_ACCTD_AMOUNT_REMAINING = CHRG_ACCTD_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_acctd_amount_remaining(m)
WHERE customer_trx_line_id = l_ctl_rem_amt.customer_trx_line_id(m);
UPDATE ra_customer_trx_lines_gt
SET AMOUNT_DUE_REMAINING = AMOUNT_DUE_REMAINING + l_ctl_rem_amt.amount_due_remaining(m),
ACCTD_AMOUNT_DUE_REMAINING = ACCTD_AMOUNT_DUE_REMAINING + l_ctl_rem_amt.acctd_amount_due_remaining(m),
CHRG_AMOUNT_REMAINING = CHRG_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_amount_remaining(m),
CHRG_ACCTD_AMOUNT_REMAINING = CHRG_ACCTD_AMOUNT_REMAINING + l_ctl_rem_amt.chrg_acctd_amount_remaining(m)
WHERE customer_trx_line_id = l_ctl_rem_amt.customer_trx_line_id(m);
select ra.receivable_application_id ,
ra.applied_customer_trx_id ,
ra.customer_trx_id ,
ra.applied_payment_schedule_id ,
ra.code_combination_id ,
ra.amount_applied ,
ra.amount_applied_from ,
ra.acctd_amount_applied_to acctd_amount_applied_to,
ra.acctd_amount_applied_from acctd_amount_applied_from,
ra.line_applied ,
ra.tax_applied ,
ra.freight_applied ,
ra.receivables_charges_applied ,
ra.earned_discount_ccid ,
ra.earned_discount_taken ,
ra.acctd_earned_discount_taken acctd_earned_discount_taken,
ra.line_ediscounted ,
ra.tax_ediscounted ,
ra.freight_ediscounted ,
ra.charges_ediscounted ,
ra.unearned_discount_ccid ,
ra.unearned_discount_taken ,
ra.acctd_unearned_discount_taken acctd_unearned_discount_taken,
ra.line_uediscounted ,
ra.tax_uediscounted ,
ra.freight_uediscounted ,
ra.charges_uediscounted ,
ra.status ,
ra.application_type ,
ra.application_ref_id ,
ra.cash_receipt_id ,
ra.reversal_gl_date ,
ra.apply_date ,
ra.confirmed_flag ,
ra.receivables_trx_id ,
ra.cash_receipt_id ,
nvl(cr.currency_code, g_ae_doc_rec.miscel1) ,
nvl(cr.exchange_rate, g_ae_doc_rec.miscel2),
nvl(cr.exchange_rate_type, g_ae_doc_rec.miscel3),
nvl(cr.exchange_date, g_ae_doc_rec.miscel4),
nvl(cr.pay_from_customer, g_ae_doc_rec.miscel5) ,
nvl(cr.customer_site_use_id, g_ae_doc_rec.miscel6),
nvl(cr.remit_bank_acct_use_id,g_ae_doc_rec.miscel7),
nvl(cr.receipt_method_id, g_ae_doc_rec.miscel8) ,
ctinv.invoice_currency_code ,
ctinv.exchange_rate,
ctinv.exchange_rate_type,
ctinv.exchange_date,
ctinv.trx_date ,
ctinv.bill_to_customer_id ,
ctinv.bill_to_site_use_id ,
ctinv.drawee_id ,
ctinv.drawee_site_use_id ,
ctinv.upgrade_method , --Invoice upgrade_method
ctinv.customer_trx_id ,
ctcm.invoice_currency_code ,
ctcm.exchange_rate,
ctcm.exchange_rate_type ,
ctcm.exchange_date,
ctcm.bill_to_customer_id ,
ctcm.bill_to_site_use_id ,
ctcm.upgrade_method , --Cm upgrade_method
ctcm.customer_trx_id ,
ctlgdcm.code_combination_id ,
decode(ra.status,
'APP', decode(
sign(ra.acctd_amount_applied_from -
ra.acctd_amount_applied_to),
-1, g_ae_sys_rec.loss_cc_id,
1, g_ae_sys_rec.gain_cc_id,
''),
'ACTIVITY', decode(
sign(ra.acctd_amount_applied_from -
ra.acctd_amount_applied_to),
-1, g_ae_sys_rec.loss_cc_id,
1, g_ae_sys_rec.gain_cc_id,
'')),
DECODE(g_ae_doc_rec.document_type,'CREDIT_MEMO',ra.code_combination_id,rma.unapplied_ccid) -- Bug 4112494 CM refunds
into p_app_rec.receivable_application_id ,
p_app_rec.applied_customer_trx_id ,
p_app_rec.customer_trx_id ,
p_app_rec.applied_payment_schedule_id ,
p_app_rec.code_combination_id ,
p_app_rec.amount_applied ,
p_app_rec.amount_applied_from ,
p_app_rec.acctd_amount_applied_to ,
p_app_rec.acctd_amount_applied_from ,
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_ccid ,
p_app_rec.earned_discount_taken ,
p_app_rec.acctd_earned_discount_taken ,
p_app_rec.line_ediscounted ,
p_app_rec.tax_ediscounted ,
p_app_rec.freight_ediscounted ,
p_app_rec.charges_ediscounted ,
p_app_rec.unearned_discount_ccid ,
p_app_rec.unearned_discount_taken ,
p_app_rec.acctd_unearned_discount_taken ,
p_app_rec.line_uediscounted ,
p_app_rec.tax_uediscounted ,
p_app_rec.freight_uediscounted ,
p_app_rec.charges_uediscounted ,
p_app_rec.status ,
p_app_rec.application_type ,
p_app_rec.application_ref_id ,
p_app_rec.cash_receipt_id ,
p_app_rec.reversal_gl_date ,
p_app_rec.apply_date ,
p_app_rec.confirmed_flag ,
p_app_rec.receivables_trx_id ,
p_cr_rec.cash_receipt_id ,
p_cr_rec.currency_code ,
p_cr_rec.exchange_rate ,
p_cr_rec.exchange_rate_type ,
p_cr_rec.exchange_date ,
p_cr_rec.pay_from_customer ,
p_cr_rec.customer_site_use_id ,
p_cr_rec.remit_bank_acct_use_id ,
p_cr_rec.receipt_method_id ,
p_cust_inv_rec.invoice_currency_code ,
p_cust_inv_rec.exchange_rate ,
p_cust_inv_rec.exchange_rate_type ,
p_cust_inv_rec.exchange_date ,
p_cust_inv_rec.trx_date ,
p_cust_inv_rec.bill_to_customer_id ,
p_cust_inv_rec.bill_to_site_use_id ,
p_cust_inv_rec.drawee_id ,
p_cust_inv_rec.drawee_site_use_id ,
p_cust_inv_rec.upgrade_method , -- Invoice upgrade_method
p_cust_inv_rec.customer_trx_id ,
p_cust_cm_rec.invoice_currency_code ,
p_cust_cm_rec.exchange_rate ,
p_cust_cm_rec.exchange_rate_type ,
p_cust_cm_rec.exchange_date ,
p_cust_cm_rec.bill_to_customer_id ,
p_cust_cm_rec.bill_to_site_use_id ,
p_cust_cm_rec.upgrade_method , -- CM upgrade_method
p_cust_cm_rec.customer_trx_id ,
p_ctlgd_cm_rec.code_combination_id ,
l_gain_loss_ccid ,
l_cr_unapp_ccid
from ar_receivable_applications ra ,
ar_cash_receipts cr ,
ar_receipt_method_accounts rma ,
ra_customer_trx ctinv ,
ra_customer_trx ctcm ,
ra_cust_trx_line_gl_dist ctlgdcm
where ra.receivable_application_id = g_ae_doc_rec.source_id
and ra.cash_receipt_id = cr.cash_receipt_id (+) --CR UNID, ACC, UNAPP exchange rate Information
and cr.remit_bank_acct_use_id = rma.remit_bank_acct_use_id (+) --UNAPP ccid for receipt
and cr.receipt_method_id = rma.receipt_method_id (+)
and ra.applied_customer_trx_id = ctinv.customer_trx_id (+) --INV REC or On Account CM exchange rate Information
and ra.customer_trx_id = ctcm.customer_trx_id (+) --CM REC exchange rate Information
and ra.customer_trx_id = ctlgdcm.customer_trx_id (+) --CM REC account ccid
and decode(ra.application_type,
'CASH', 'REC',
'CM' , ctlgdcm.account_class) = 'REC'
and decode(ra.application_type,
'CASH', 'Y',
'CM' , ctlgdcm.latest_rec_flag) = 'Y';
select legal_entity_id
into l_le_id
from ra_customer_trx
where customer_trx_id = p_app_rec.applied_customer_trx_id;
select nvl(ed.gl_account_source,'NO_SOURCE') ,
nvl(ed.tax_code_source,'NO_SOURCE') ,
ed.tax_recoverable_flag ,
ed.code_combination_id , --activity gl account earned discount
nvl(edd.asset_tax_code, ed.asset_tax_code) ,
nvl(edd.liability_tax_code, ed.liability_tax_code),
'' ,
'' ,
nvl(uned.gl_account_source,'NO_SOURCE') ,
nvl(uned.tax_code_source,'NO_SOURCE') ,
uned.tax_recoverable_flag ,
uned.code_combination_id , --activity gl account unearned discount
nvl(unedd.asset_tax_code,uned.asset_tax_code),
nvl(unedd.liability_tax_code,uned.liability_tax_code),
'' ,
''
into p_rule_rec.gl_account_source1 , --Earned discounts
p_rule_rec.tax_code_source1 ,
p_rule_rec.tax_recoverable_flag1 ,
p_rule_rec.code_combination_id1 ,
p_rule_rec.asset_tax_code1 ,
p_rule_rec.liability_tax_code1 ,
p_rule_rec.act_tax_non_rec_ccid1 ,
p_rule_rec.act_vat_tax_id1 ,
p_rule_rec.gl_account_source2 , --Unearned discounts
p_rule_rec.tax_code_source2 ,
p_rule_rec.tax_recoverable_flag2 ,
p_rule_rec.code_combination_id2 ,
p_rule_rec.asset_tax_code2 ,
p_rule_rec.liability_tax_code2 ,
p_rule_rec.act_tax_non_rec_ccid2 ,
p_rule_rec.act_vat_tax_id2
from ar_receipt_method_accounts rma,
ar_receivables_trx ed,
ar_rec_trx_le_details edd,
ar_receivables_trx uned,
ar_rec_trx_le_details unedd
where rma.receipt_method_id = p_cr_rec.receipt_method_id
and rma.remit_bank_acct_use_id = p_cr_rec.remit_bank_acct_use_id
and rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
and ed.receivables_trx_id = edd.receivables_trx_id (+)
and edd.legal_entity_id (+) = l_le_id
and rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+)
and uned.receivables_trx_id = unedd.receivables_trx_id (+)
and unedd.legal_entity_id (+) = l_le_id;
select nvl(ed.gl_account_source,'NO_SOURCE') ,
nvl(ed.tax_code_source,'NO_SOURCE') ,
ed.tax_recoverable_flag ,
ed.code_combination_id , --activity gl account earned discount
ed.asset_tax_code ,
ed.liability_tax_code ,
'' ,
'' ,
nvl(uned.gl_account_source,'NO_SOURCE') ,
nvl(uned.tax_code_source,'NO_SOURCE') ,
uned.tax_recoverable_flag ,
uned.code_combination_id , --activity gl account unearned discount
uned.asset_tax_code ,
uned.liability_tax_code ,
'' ,
''
into p_rule_rec.gl_account_source1 , --Earned discounts
p_rule_rec.tax_code_source1 ,
p_rule_rec.tax_recoverable_flag1 ,
p_rule_rec.code_combination_id1 ,
p_rule_rec.asset_tax_code1 ,
p_rule_rec.liability_tax_code1 ,
p_rule_rec.act_tax_non_rec_ccid1 ,
p_rule_rec.act_vat_tax_id1 ,
p_rule_rec.gl_account_source2 , --Unearned discounts
p_rule_rec.tax_code_source2 ,
p_rule_rec.tax_recoverable_flag2 ,
p_rule_rec.code_combination_id2 ,
p_rule_rec.asset_tax_code2 ,
p_rule_rec.liability_tax_code2 ,
p_rule_rec.act_tax_non_rec_ccid2 ,
p_rule_rec.act_vat_tax_id2
from ar_receipt_method_accounts rma,
ar_receivables_trx ed,
ar_receivables_trx uned
where rma.receipt_method_id = p_cr_rec.receipt_method_id
and rma.remit_bank_acct_use_id = p_cr_rec.remit_bank_acct_use_id
and rma.edisc_receivables_trx_id = ed.receivables_trx_id (+)
and rma.unedisc_receivables_trx_id = uned.receivables_trx_id (+) ;
select cr.cash_receipt_id ,
cr.amount ,
cr.vat_tax_id ,
cr.tax_rate ,
cr.currency_code ,
cr.exchange_rate exchange_rate,
cr.exchange_rate_type exchange_rate_type,
cr.exchange_date exchange_date,
cr.pay_from_customer ,
cr.customer_site_use_id ,
decode(avt.tax_rate_id, null, null,
arp_etax_util.get_tax_account(cr.vat_tax_id,
cr.deposit_date,
'TAX',
'TAX_RATE')),
avt.tax_rate_id ,
fc.precision ,
fc.minimum_accountable_unit
into p_cr_rec.cash_receipt_id ,
p_cr_rec.amount ,
p_cr_rec.vat_tax_id ,
p_cr_rec.tax_rate ,
p_cr_rec.currency_code ,
p_cr_rec.exchange_rate ,
p_cr_rec.exchange_rate_type ,
p_cr_rec.exchange_date ,
p_cr_rec.pay_from_customer ,
p_cr_rec.customer_site_use_id ,
p_vat_rec.tax_account_id ,
p_vat_rec.vat_tax_id ,
p_curr_rec.precision ,
p_curr_rec.minimum_accountable_unit
from ar_cash_receipts cr ,
zx_rates_b avt ,
fnd_currencies fc
where cr.cash_receipt_id = g_ae_doc_rec.document_id
and cr.currency_code = fc.currency_code
and cr.vat_tax_id = avt.tax_rate_id (+);
SELECT tax_rate_id
INTO p_act_vat_tax_id
FROM zx_sco_rates
WHERE tax_rate_code = p_asset_tax_code
AND p_apply_date BETWEEN nvl(effective_from, p_apply_date) AND
nvl(effective_to, p_apply_date);
ARP_DET_DIST_PKG.update_from_gt
(p_from_amt => l_app_rec.amount_applied_from,
p_from_acctd_amt => l_app_rec.acctd_amount_applied_from,
p_ae_sys_rec => g_ae_sys_rec,
p_app_rec => l_app_rec,
p_gt_id => p_gt_id,
p_inv_currency => l_cust_inv_rec.invoice_currency_code);
UPDATE ar_receivable_applications SET upgrade_method = 'R12'
WHERE receivable_application_id = l_app_rec.receivable_application_id;
SELECT SUM( abs(nvl(amount_line_items_original,0)) +
abs(nvl(tax_original,0)) +
abs(nvl(discount_original,0)) +
abs(nvl(freight_original,0)) +
abs(nvl(receivables_charges_charged,0)) )
INTO
l_rec_amt
FROM ar_payment_schedules
where customer_trx_id = p_app_rec.applied_customer_trx_id
GROUP BY customer_trx_id;
| Remittance and Factored (with Recourse) we need to update the link id |
| so the last Transaction History Record must be Standard Remitted or |
| pending risk elimination |
+-----------------------------------------------------------------------*/
update ar_receivable_applications
set link_to_trx_hist_id = (select max(th.transaction_history_id)
from ar_transaction_history th
where th.customer_trx_id = p_app_rec.applied_customer_trx_id
and th.event = 'MATURITY_DATE'
and exists (select 'x'
from ar_distributions ard
where ard.source_id = th.transaction_history_id
and ard.source_table = 'TH'))
where receivable_application_id = p_app_rec.receivable_application_id;
SELECT mcd.misc_cash_distribution_id misc_cash_distribution_id ,
mcd.code_combination_id code_combination_id ,
mcd.percent percent ,
mcd.amount amount ,
mcd.acctd_amount acctd_amount
FROM ar_misc_cash_distributions mcd
WHERE mcd.cash_receipt_id = g_ae_doc_rec.document_id
AND g_ae_sys_rec.sob_type = 'P'
AND mcd.reversal_gl_date is null --so we create only new rate adjusted or new mcd records
AND mcd.posting_control_id = -3
AND not exists (select 'x'
from ar_distributions ard
where ard.source_id = mcd.misc_cash_distribution_id
and ard.source_table = 'MCD')
ORDER by misc_cash_distribution_id;
SELECT mcd.misc_cash_distribution_id misc_cash_distribution_id,
mcd.code_combination_id code_combination_id,
mcd.percent percent,
mcd.amount amount,
mcd_mrc.acctd_amount acctd_amount
FROM ar_misc_cash_distributions mcd,
ar_mc_misc_cash_dists mcd_mrc
WHERE mcd.cash_receipt_id = g_ae_doc_rec.document_id
AND mcd.misc_cash_distribution_id = mcd_mrc.misc_cash_distribution_id
AND g_ae_sys_rec.sob_type = 'R'
AND mcd_mrc.set_of_books_id = g_ae_sys_rec.set_of_books_id
AND mcd.reversal_gl_date is null --so we create only new rate adjusted or new mcd records.
AND mcd_mrc.posting_control_id = -3
AND not exists (select 'x'
from ar_mc_distributions_all ard
where ard.source_id = mcd.misc_cash_distribution_id
and ard.source_table = 'MCD'
and ard.set_of_books_id = g_ae_sys_rec.set_of_books_id)
*/
l_ael_line_rec ae_line_rec_type;
select 'x'
into l_dummy
from dual
where 1 = 2;
Update ar_receivable_applications set code_combination_id = l_account
where receivable_application_id = p_ae_line_rec.source_id and status in
('ACC', 'UNID', 'UNAPP', 'ACTIVITY', 'OTHER ACC');