[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| delete_mcd_rec |
| |
| DESCRIPTION |
| Deletes a miscellaneous distribution record from |
| ar_misc_cash_distributions. |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 28-SEP-1995 OSTEINME created |
| 18-JAN-2001 ANUJ Modified |
| |
+===========================================================================*/
PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
PROCEDURE delete_mcd_rec(
p_mcd_id IN
ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
p_form_name IN varchar2,
p_form_version IN varchar2
) IS
l_ard_line_id ar_distributions.line_id%TYPE;-- for 1543658
arp_standard.debug('arp_process_receipts.delete_mcd_rec()+');
SELECT line_id into l_ard_line_id
FROM ar_distributions
WHERE source_id = p_mcd_id AND
source_table = 'MCD' AND
source_type ='MISCCASH' ;
ARP_DISTRIBUTIONS_PKG.delete_p(l_ard_line_id);
SELECT * into l_ard_rec
FROM ar_distributions
WHERE source_id = p_mcd_id AND
source_table = 'MCD' AND
source_type ='TAX' AND
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = ar_distributions.source_id_secondary
and reversal_gl_date is null);
select nvl(min(source_id),0) into l_ard_tax_source_id
FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type ='MISCCASH' AND
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = l_ard_rec.source_id_secondary
and reversal_gl_date is null);
ARP_DISTRIBUTIONS_PKG.update_p(l_ard_rec);
arp_standard.debug('delete_mcd_rec: ' || 'Distribution TAX line does not exists');
arp_standard.debug('delete_mcd_rec: ' || 'Distribution line does not exists');
arp_misc_cash_dist_pkg.delete_p(p_mcd_id);
arp_standard.debug('arp_process_receipts.delete_mcd_rec()-');
END delete_mcd_rec;
| insert_mcd_rec |
| |
| DESCRIPTION |
| Inserts a misc cash distribution record into ar_misc_cash_distributions|
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 28-SEP-1995 OSTEINME created |
| 18-JAN-2001 ANUJ Modified |
| |
+===========================================================================*/
PROCEDURE insert_mcd_rec(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_percent IN ar_misc_cash_distributions.percent%TYPE,
p_amount IN ar_misc_cash_distributions.amount%TYPE,
p_comments IN ar_misc_cash_distributions.comments%TYPE,
p_apply_date IN ar_misc_cash_distributions.apply_date%TYPE,
p_code_combination_id IN ar_misc_cash_distributions.code_combination_id%TYPE,
p_attribute_category IN ar_misc_cash_distributions.attribute_category%TYPE,
p_attribute1 IN ar_misc_cash_distributions.attribute1%TYPE,
p_attribute2 IN ar_misc_cash_distributions.attribute2%TYPE,
p_attribute3 IN ar_misc_cash_distributions.attribute3%TYPE,
p_attribute4 IN ar_misc_cash_distributions.attribute4%TYPE,
p_attribute5 IN ar_misc_cash_distributions.attribute5%TYPE,
p_attribute6 IN ar_misc_cash_distributions.attribute6%TYPE,
p_attribute7 IN ar_misc_cash_distributions.attribute7%TYPE,
p_attribute8 IN ar_misc_cash_distributions.attribute8%TYPE,
p_attribute9 IN ar_misc_cash_distributions.attribute9%TYPE,
p_attribute10 IN ar_misc_cash_distributions.attribute10%TYPE,
p_attribute11 IN ar_misc_cash_distributions.attribute11%TYPE,
p_attribute12 IN ar_misc_cash_distributions.attribute12%TYPE,
p_attribute13 IN ar_misc_cash_distributions.attribute13%TYPE,
p_attribute14 IN ar_misc_cash_distributions.attribute14%TYPE,
p_attribute15 IN ar_misc_cash_distributions.attribute15%TYPE,
p_acctd_amount IN ar_misc_cash_distributions.acctd_amount%TYPE,
p_ussgl_tc IN ar_misc_cash_distributions.ussgl_transaction_code%TYPE,
p_mcd_id OUT NOCOPY ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
p_form_name IN varchar2,
p_form_version IN varchar2,
p_amount_ard IN ar_distributions.amount_dr%TYPE,--for 1543658
p_acctd_amount_ard IN ar_distributions.acctd_amount_dr%TYPE --for 1543658
) IS
l_mcd_rec ar_misc_cash_distributions%ROWTYPE;
SELECT *
FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type ='MISCCASH' and
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = p_cash_receipt_id and
misc_cash_distribution_id <> p_mcd_id
and reversal_gl_date is null );
update_flag CHAR(1);
arp_standard.debug('arp_process_receipts.insert_mcd_rec()+');
select cr.cash_receipt_id ,
cr.amount ,
cr.vat_tax_id ,
cr.tax_rate ,
cr.currency_code ,
cr.exchange_rate ,
cr.exchange_rate_type ,
cr.exchange_date ,
cr.pay_from_customer , --third_party_id
cr.customer_site_use_id , --third_party_sub_id
avt.tax_account_id ,
avt.vat_tax_id ,
fc.precision ,
fc.minimum_accountable_unit
into l_cr_rec.cash_receipt_id ,
l_cr_rec.amount ,
l_cr_rec.vat_tax_id ,
l_cr_rec.tax_rate ,
l_cr_rec.currency_code ,
l_cr_rec.exchange_rate ,
l_cr_rec.exchange_rate_type ,
l_cr_rec.exchange_date ,
l_cr_rec.pay_from_customer , --third_party_id
l_cr_rec.customer_site_use_id , --third_party_sub_id
l_tax_account_id , --code_combination_id for tax
l_vat_tax_id , --tax_code_id
l_precision ,
l_min_unit
from ar_cash_receipts cr,
ar_vat_tax avt,
fnd_currencies fc
where cr.cash_receipt_id = p_cash_receipt_id
and cr.currency_code = fc.currency_code
and cr.vat_tax_id = avt.vat_tax_id(+);
SELECT sum(amount), sum(percent)
INTO l_amount_total, l_percent_total
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id
AND reversal_gl_date IS NULL;
arp_misc_cash_dist_pkg.insert_p(l_mcd_rec, l_mcd_id);
arp_standard.debug( 'Insert Misc Cash Receipt Distribution start');
select * into l_ard_tax_rec
from ar_distributions
where source_type ='TAX' and
source_table ='MCD' and
source_type_secondary = 'MISCCASH' and
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = l_cr_rec.cash_receipt_id
and reversal_gl_date is null
UNION
select 0 from dual);
DELETE FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type ='TAX' and
source_id = 0
RETURNING line_id
BULK COLLECT into l_ar_dist_key_value_list;
arp_standard.debug( 'NO INSERT, TAX IS STORED WITH FIRST misc_cash_distribution_id');
update_flag:='N';
update_flag:='Y';
SELECT count(*) cnt into l_ard_cnt
FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type ='MISCCASH' and
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = l_cr_rec.cash_receipt_id
and reversal_gl_date is null );
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
IF update_flag='Y' THEN
FOR l_ctr IN ard_tbl_tbl.FIRST .. ard_tbl_tbl.LAST LOOP
ARP_DISTRIBUTIONS_PKG.insert_p(ard_tbl_tbl(l_ctr),l_ard_line_id);
ard_tbl_tbl.delete;
tax_link_id must be assigned before inserting the new record */
if l_ard_tax_rec_flag ='Y' then
l_ard_rec.tax_link_id := 1;
ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_rec,l_ard_line_id);
ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_tax_rec,l_ard_line_id);
arp_standard.debug( 'Insert Misc Cash Receipt Distribution start');
arp_misc_cash_dist_pkg.insert_p(l_mcd_rec, l_mcd_id);
arp_standard.debug('arp_process_receipts.insert_mcd_rec()-');
END insert_mcd_rec;
| update_mcd_rec |
| |
| DESCRIPTION |
| updates a record in ar_misc_cash_distributions |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 02-OCT-1995 OSTEINME created |
| 29-SEP-1998 GJWANG Bug fix: 737949 Remove code setting posting|
| control id to -3 when update |
| 18-JAN-2001 ANUJ Modified for 1543658 |
+===========================================================================*/
PROCEDURE update_mcd_rec(
p_misc_cash_distribution_id
IN ar_misc_cash_distributions.misc_cash_distribution_id%TYPE,
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_gl_date IN ar_misc_cash_distributions.gl_date%TYPE,
p_percent IN ar_misc_cash_distributions.percent%TYPE,
p_amount IN ar_misc_cash_distributions.amount%TYPE,
p_comments IN ar_misc_cash_distributions.comments%TYPE,
p_apply_date IN ar_misc_cash_distributions.apply_date%TYPE,
p_code_combination_id IN ar_misc_cash_distributions.code_combination_id%TYPE,
p_attribute_category IN ar_misc_cash_distributions.attribute_category%TYPE,
p_attribute1 IN ar_misc_cash_distributions.attribute1%TYPE,
p_attribute2 IN ar_misc_cash_distributions.attribute2%TYPE,
p_attribute3 IN ar_misc_cash_distributions.attribute3%TYPE,
p_attribute4 IN ar_misc_cash_distributions.attribute4%TYPE,
p_attribute5 IN ar_misc_cash_distributions.attribute5%TYPE,
p_attribute6 IN ar_misc_cash_distributions.attribute6%TYPE,
p_attribute7 IN ar_misc_cash_distributions.attribute7%TYPE,
p_attribute8 IN ar_misc_cash_distributions.attribute8%TYPE,
p_attribute9 IN ar_misc_cash_distributions.attribute9%TYPE,
p_attribute10 IN ar_misc_cash_distributions.attribute10%TYPE,
p_attribute11 IN ar_misc_cash_distributions.attribute11%TYPE,
p_attribute12 IN ar_misc_cash_distributions.attribute12%TYPE,
p_attribute13 IN ar_misc_cash_distributions.attribute13%TYPE,
p_attribute14 IN ar_misc_cash_distributions.attribute14%TYPE,
p_attribute15 IN ar_misc_cash_distributions.attribute15%TYPE,
p_acctd_amount IN ar_misc_cash_distributions.acctd_amount%TYPE,
p_ussgl_tc IN ar_misc_cash_distributions.ussgl_transaction_code%TYPE,
p_form_name IN varchar2,
p_form_version IN varchar2,
p_amount_ard IN ar_distributions.amount_dr%TYPE,--for 1543658
p_acctd_amount_ard IN ar_distributions.acctd_amount_dr%TYPE --for 1543658
) IS
l_mcd_rec ar_misc_cash_distributions%ROWTYPE;
SELECT *
FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type ='MISCCASH' and
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = p_cash_receipt_id and
misc_cash_distribution_id <> p_mcd_id
and reversal_gl_date is null);
update_flag CHAR(1);
arp_standard.debug('arp_process_receipts.update_mcd_rec()+');
select cr.cash_receipt_id ,
cr.amount ,
cr.vat_tax_id ,
cr.tax_rate ,
cr.currency_code ,
cr.exchange_rate ,
cr.exchange_rate_type ,
cr.exchange_date ,
cr.pay_from_customer , --third_party_id
cr.customer_site_use_id , --third_party_sub_id
avt.tax_account_id ,
avt.vat_tax_id ,
fc.precision ,
fc.minimum_accountable_unit
into l_cr_rec.cash_receipt_id ,
l_cr_rec.amount ,
l_cr_rec.vat_tax_id ,
l_cr_rec.tax_rate ,
l_cr_rec.currency_code ,
l_cr_rec.exchange_rate ,
l_cr_rec.exchange_rate_type ,
l_cr_rec.exchange_date ,
l_cr_rec.pay_from_customer , --third_party_id
l_cr_rec.customer_site_use_id , --third_party_sub_id
l_tax_account_id , --code_combination_id for tax
l_vat_tax_id , --tax_code_id
l_precision ,
l_min_unit
from ar_cash_receipts cr,
ar_vat_tax avt,
fnd_currencies fc
where cr.cash_receipt_id = p_cash_receipt_id
and cr.currency_code = fc.currency_code
and cr.vat_tax_id = avt.vat_tax_id(+);
SELECT sum(amount), sum(percent)
INTO l_amount_total, l_percent_total
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id
AND reversal_gl_date IS NULL;
SELECT amount,percent
INTO l_amount_current, l_percent_current
FROM ar_misc_cash_distributions
WHERE misc_cash_distribution_id = p_misc_cash_distribution_id;
arp_misc_cash_dist_pkg.update_p(l_mcd_rec);
arp_standard.debug('Update Misc Cash Receipt Distribution start');
select * into l_ard_tax_rec
from ar_distributions
where source_type ='TAX' and
source_table ='MCD' and
source_type_secondary = 'MISCCASH' and
source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = l_mcd_rec.cash_receipt_id
and reversal_gl_date is null
UNION
select 0 from dual);
arp_standard.debug('NO INSERT, TAX IS STORED WITH FIRST misc_cash_distribution_id');
update_flag:='N';
update_flag:='Y';
SELECT count(*) cnt into l_ard_cnt
FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type ='MISCCASH' and
source_id in ( select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = l_cr_rec.cash_receipt_id
and reversal_gl_date is null );
arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
IF update_flag='Y' THEN
FOR l_ctr IN ard_tbl_tbl.FIRST .. ard_tbl_tbl.LAST LOOP
ARP_DISTRIBUTIONS_PKG.insert_p(ard_tbl_tbl(l_ctr),l_ard_line_id);
ard_tbl_tbl.delete;
tax_link_id must be assigned before inserting the new record */
if l_ard_tax_rec_flag = 'Y' then
l_ard_rec.tax_link_id := 1;
ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_rec,l_ard_line_id);
ARP_DISTRIBUTIONS_PKG.insert_p(l_ard_tax_rec,l_ard_line_id);
arp_standard.debug('Update Misc Cash Receipt Distribution end');
arp_misc_cash_dist_pkg.update_p(l_mcd_rec);
Update the receipt version number */
arp_cash_receipts_pkg.update_version_number(p_cash_receipt_id);
arp_standard.debug('arp_process_receipts.update_mcd_rec()-');
END update_mcd_rec;
SELECT count(*) cnt into l_ard_cnt
FROM ar_distributions
WHERE source_table = 'MCD' AND
source_type = 'MISCCASH' and
source_id = p_misc_cash_distribution_id ;
SELECT decode(sign(p_amount), 1, amount_cr,
0, amount_cr,
-1, amount_dr )
INTO l_dist_rec_amt
FROM ar_distributions
WHERE source_id = p_misc_cash_distribution_id
AND source_table = 'MCD'
AND source_type = 'MISCCASH'
FOR UPDATE OF source_id NOWAIT;
| we don't take care of rounding while insert or updation new MCD |
| in Distribution window |
| |
| SCOPE - PRIVATE |
| |
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS |
| IN: |
| OUT: |
| |
| RETURNS |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| |
| 18-JAN-2001 ANUJ Created for 1543658 |
| 19-Sep-2001 Debbie Jancis Added hook for mrc engine for |
| to process update information |
+===========================================================================*/
PROCEDURE round_correction_mcd_rec(
p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE,
p_flag OUT NOCOPY NUMBER
) IS
l_min_unit NUMBER;
SELECT *
from ar_misc_cash_distributions
where cash_receipt_id = l_cash_receipt_id
and reversal_gl_date is null;
update_flag Char(1);
select cr.cash_receipt_id ,
cr.amount ,
cr.vat_tax_id ,
cr.tax_rate ,
cr.type ,
cr.currency_code ,
cr.exchange_rate ,
cr.exchange_rate_type ,
cr.exchange_date ,
cr.pay_from_customer , --third_party_id
cr.customer_site_use_id , --third_party_sub_id
fc.precision ,
fc.minimum_accountable_unit
into l_cr_rec.cash_receipt_id ,
l_cr_rec.amount ,
l_cr_rec.vat_tax_id ,
l_cr_rec.tax_rate ,
l_cr_rec.type ,
l_cr_rec.currency_code ,
l_cr_rec.exchange_rate ,
l_cr_rec.exchange_rate_type ,
l_cr_rec.exchange_date ,
l_cr_rec.pay_from_customer , --third_party_id
l_cr_rec.customer_site_use_id , --third_party_sub_id
l_precision ,
l_min_unit
from ar_cash_receipts cr,
fnd_currencies fc
where cr.cash_receipt_id = p_cash_receipt_id
and cr.currency_code = fc.currency_code;
update_flag:='Y';
IF update_flag='Y' THEN
FOR l_ctr IN mcd_tbl_tbl.FIRST .. mcd_tbl_tbl.LAST LOOP
arp_misc_cash_dist_pkg.update_p(mcd_tbl_tbl(l_ctr));
mcd_tbl_tbl.delete;
SELECT NVL(l_cr_rec.amount, 0) -
NVL(SUM(amount),0)
,
NVL(l_acctd_amount,0) -
NVL(SUM(acctd_amount),0)
INTO l_rounding_diff,
l_acctd_rounding_diff
FROM ar_misc_cash_distributions
WHERE cash_receipt_id = p_cash_receipt_id
and reversal_gl_date is null;
UPDATE ar_misc_cash_distributions
SET amount = amount + l_rounding_diff,
acctd_amount = acctd_amount + l_acctd_rounding_diff
WHERE cash_receipt_id = p_cash_receipt_id
and reversal_gl_date is null
AND ROWNUM = 1
RETURNING misc_cash_distribution_id
BULK COLLECT INTO l_misc_cash_key_value_list;
SELECT NVL(SUM(acctd_amount_cr),0),
NVL(SUM(acctd_amount_dr),0)
INTO l_ard_acctd_cr,
l_ard_acctd_dr
FROM ar_distributions
where source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = p_cash_receipt_id
and reversal_gl_date is null ) and
source_table = 'MCD' ;
UPDATE ar_distributions
SET acctd_amount_dr = acctd_amount_dr + l_ard_acctd_dr_rounding_diff
WHERE source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = p_cash_receipt_id
and reversal_gl_date is null )
AND ROWNUM = 1
AND source_table = 'MCD'
AND source_type ='MISCCASH'
AND acctd_amount_dr is not null ; /* Added for bug 2278738 */
UPDATE ar_distributions
SET acctd_amount_cr = acctd_amount_cr + l_ard_acctd_cr_rounding_diff
WHERE source_id in (select misc_cash_distribution_id
from ar_misc_cash_distributions
where cash_receipt_id = p_cash_receipt_id
and reversal_gl_date is null)
AND ROWNUM = 1
AND source_table = 'MCD'
AND source_type ='MISCCASH'
AND acctd_amount_cr is not null ; /* Added for bug 2278738 */
in ora 1422. Used MIN to select source_id. */
FUNCTION misc_cash_tax_line_ccid_in_ard(
p_cash_receipt_id IN number) return NUMBER IS
return_value number;
select code_combination_id into return_value
from ar_distributions
where source_id in (select MIN(misc_cash_distribution_id)
from ar_misc_cash_distributions
where cash_receipt_id=p_cash_receipt_id)
and SOURCE_TABLE ='MCD'
and SOURCE_TYPE = 'TAX';