[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rtl.relative_amount/rt.base_amount apportion_ratio,
rt.first_installment_code
FROM ra_terms rt ,
ra_terms_lines rtl
WHERE rt.term_id = rtl.term_id
AND rtl.term_id = p_term_id
AND rtl.sequence_num = p_terms_sequence_number;
FOR i IN( SELECT *
FROM ar_payment_schedules_all
WHERE customer_trx_id = p_customer_trx_id
ORDER BY payment_schedule_id)
LOOP
ln_apportion_ratio := null;
UPDATE ar_payment_schedules_all
SET amount_due_original = p_gl_rec_amount * ln_apportion_ratio,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = p_customer_trx_id
AND payment_schedule_id = i.payment_schedule_id;
'UPDATE ar_payment_schedules_all
SET amount_due_original = '||p_gl_rec_amount||' * '||ln_apportion_ratio||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||p_customer_trx_id||'
AND payment_schedule_id = '||i.payment_schedule_id||';';
UPDATE ar_payment_schedules_all
SET tax_original = p_gl_tax_amount * ln_apportion_ratio,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = p_customer_trx_id
AND payment_schedule_id = i.payment_schedule_id;
'UPDATE ar_payment_schedules_all
SET tax_original = '||p_gl_tax_amount||' * '||ln_apportion_ratio||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||p_customer_trx_id||'
AND payment_schedule_id = '||i.payment_schedule_id||';';
UPDATE ar_payment_schedules_all
SET freight_original = p_gl_freight_amount * ln_apportion_ratio,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = p_customer_trx_id
AND payment_schedule_id = i.payment_schedule_id;
'UPDATE ar_payment_schedules_all
SET freight_original = '||p_gl_freight_amount||' * '||ln_apportion_ratio||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||p_customer_trx_id||'
AND payment_schedule_id = '||i.payment_schedule_id||';';
FOR i IN( SELECT *
FROM ar_payment_schedules_all
WHERE customer_trx_id = p_previous_trx_id
ORDER BY payment_schedule_id)
LOOP
calc_term_apportion_ratio(p_invoice_type => i.class ,
p_term_id => i.term_id ,
p_terms_sequence_number => i.terms_sequence_number ,
p_apportion_ratio => ln_apportion_ratio ,
p_first_installment_code => lv_first_installment_code ,
p_process_status => lv_process_status ,
p_process_message => lv_process_message
);
UPDATE ar_receivable_applications_all
SET amount_applied = p_arps_ado * ln_apportion_ratio,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = p_customer_trx_id -- CM
AND applied_customer_trx_id = p_previous_trx_id -- INV
AND applied_payment_schedule_id = i.payment_schedule_id -- payment_schedule_id of INV
AND display = 'Y'
AND status = 'APP'
AND application_type = 'CM';
'UPDATE ar_receivable_applications_all
SET amount_applied = '||p_arps_ado||' * '||ln_apportion_ratio||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||p_customer_trx_id||'
AND applied_customer_trx_id = '||p_previous_trx_id||'
AND applied_payment_schedule_id = '||i.payment_schedule_id||'
AND display = '||''''||'Y'||''''||'
AND status = '||''''||'APP'||''''||'
AND application_type = '||''''||'CM'||''''||';';
UPDATE ar_receivable_applications_all
SET tax_applied = p_arps_to * ln_apportion_ratio,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = p_customer_trx_id -- CM
AND applied_customer_trx_id = p_previous_trx_id -- INV
AND applied_payment_schedule_id = i.payment_schedule_id -- payment_schedule_id of INV
AND display = 'Y'
AND status = 'APP'
AND application_type = 'CM';
'UPDATE ar_receivable_applications_all
SET tax_applied = '||p_arps_to||' * '||ln_apportion_ratio||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||p_customer_trx_id||'
AND applied_customer_trx_id = '||p_previous_trx_id||'
AND applied_payment_schedule_id = '||i.payment_schedule_id||'
AND display = '||''''||'Y'||''''||'
AND status = '||''''||'APP'||''''||'
AND application_type = '||''''||'CM'||''''||';';
UPDATE ar_receivable_applications_all
SET freight_applied = p_arps_fo * ln_apportion_ratio,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = p_customer_trx_id -- CM
AND applied_customer_trx_id = p_previous_trx_id -- INV
AND applied_payment_schedule_id = i.payment_schedule_id -- payment_schedule_id of INV
AND display = 'Y'
AND status = 'APP'
AND application_type = 'CM';
'UPDATE ar_receivable_applications_all
SET freight_applied = '||p_arps_fo||' * '||ln_apportion_ratio||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||p_customer_trx_id||'
AND applied_customer_trx_id = '||p_previous_trx_id||'
AND applied_payment_schedule_id = '||i.payment_schedule_id||'
AND display = '||''''||'Y'||''''||'
AND status = '||''''||'APP'||''''||'
AND application_type = '||''''||'CM'||''''||';';
SELECT 1
FROM ra_customer_trx_lines_all rctl,
ar_vat_tax_all avtl
WHERE rctl.vat_tax_id = avtl.vat_tax_id
AND rctl.org_id = avtl.org_id
AND rctl.customer_trx_id = cp_customer_trx_id
AND avtl.tax_code <> jai_constants.tax_code_localization--'Localization' --Added by Bgowrava for Bug#5484865
AND rctl.org_id = rctl.org_id
AND rctl.line_type IN ('TAX','FREIGHT') ;
SELECT 1
FROM ra_cust_trx_line_gl_dist_all gl_dist,
ra_customer_trx_all rctx
WHERE rctx.customer_trx_id = gl_dist.customer_trx_id
AND rctx.invoicing_rule_id IS NOT NULL
AND gl_dist.account_class = 'REC'
AND gl_dist.account_set_flag = 'N'
AND gl_dist.latest_rec_flag = 'Y'
AND gl_dist.customer_trx_id = cp_customer_trx_id;
SELECT count(*)
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ra_cust_trx_types_all rctta
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND rctta.type = 'CM'
AND rctla. previous_customer_trx_id IS NOT NULL
AND rcta.customer_trx_id = cp_customer_trx_id;
SELECT 1
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_set_flag = 'N'
AND posting_control_id <> -3
AND rownum = 1;
p_process_message := 'Invoice lines have taxes other than localization type of tax. Please delete it and reprocess the invoice';
SELECT rcta.customer_trx_id,
rcta.previous_customer_trx_id,
rcta.set_of_books_id ,
rctta.type
FROM ra_customer_trx_all rcta ,
JAI_AR_TRXS jrcta ,
ra_cust_trx_types_all rctta /* added by aiyer to check that only INV and CM type of transactions are picked up */
WHERE rcta.customer_trx_id = jrcta.customer_trx_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND nvl(rctta.type,'###') IN ('INV','CM')
AND jrcta.customer_trx_id = NVL(cp_customer_trx_id, jrcta.customer_trx_id)
AND trunc(rcta.trx_date) BETWEEN NVL(cp_start_date, trunc(rcta.trx_date)) AND NVL(cp_end_date, trunc(rcta.trx_date))
AND nvl(rcta.complete_flag,'N') = 'Y'
ORDER BY rcta.customer_trx_id;
SELECT 1
FROM ra_cust_trx_line_gl_dist_all gl_dist,
ra_customer_trx_all rctx
WHERE rctx.customer_trx_id = gl_dist.customer_trx_id
AND rctx.invoicing_rule_id IS NOT NULL
AND gl_dist.account_class = 'REC'
AND gl_dist.account_set_flag = 'N'
AND gl_dist.latest_rec_flag = 'Y'
AND gl_dist.customer_trx_id = cp_customer_trx_id;
SELECT 1
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND account_set_flag = 'N'
AND account_class = 'REC'
AND latest_rec_flag = 'Y'
AND posting_control_id = -3;
SELECT NVL(SUM(amount_due_remaining),0) amount_due_remaining,
NVL(SUM(amount_due_original),0) amount_due_original,
NVL(SUM(tax_original),0) tax_original,
NVL(SUM(freight_original),0) freight_original,
NVL(SUM(tax_remaining),0) tax_remaining,
NVL(SUM(freight_remaining),0) freight_remaining,
NVL(SUM(amount_applied),0) amount_applied,
NVL(SUM(amount_credited),0) amount_credited,
NVL(SUM(amount_line_items_original),0) amount_line_items_original,
NVL(SUM(amount_line_items_remaining),0) amount_line_items_remaining,
NVL(SUM(acctd_amount_due_remaining),0) acctd_amount_due_remaining,
NVL(SUM( NVL(amount_due_remaining,0) * NVL(exchange_rate,1) ),0) acctd_amount_due_remain_calc
FROM ar_payment_schedules_all
WHERE customer_trx_id = cp_customer_trx_id;
SELECT status,
gl_date_closed,
NVL(amount_due_remaining,0) amount_due_remaining,
NVL(acctd_amount_due_remaining,0) acctd_amount_due_remaining,
payment_schedule_id,
exchange_rate
FROM ar_payment_schedules_all
WHERE customer_trx_id = cp_customer_trx_id;
SELECT NVL(SUM(amount),0) amount,
NVL(SUM(acctd_amount),0) acctd_amount
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = cp_customer_trx_id
AND (
( account_class = 'REC'
AND latest_rec_flag = 'Y'
)
OR
(account_class <> 'REC')
)
AND account_class = NVL(cp_account_class, account_class);
SELECT rctl.customer_trx_id,
jrcttl.customer_trx_line_id,
jrcttl.tax_amount
FROM JAI_AR_TRX_TAX_LINES jrcttl,
JAI_AR_TRX_LINES jrctl,
ra_customer_trx_lines_all rctl
WHERE jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
AND jrcttl.customer_Trx_line_id = rctl.customer_trx_line_id
AND nvl(jrcttl.tax_amount,0) <> nvl(rctl.extended_amount,0)
AND rctl.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_id = cp_customer_trx_id
AND rctl.line_type IN ('TAX','FREIGHT');
SELECT gl_dist.customer_trx_id,
jrcttl.customer_trx_line_id,
jrcttl.tax_amount ,
jrcttl.func_tax_amount
FROM JAI_AR_TRX_TAX_LINES jrcttl,
JAI_AR_TRX_LINES jrctl,
ra_cust_trx_line_gl_dist_all gl_dist
WHERE jrcttl.link_to_cust_trx_line_id = jrctl.customer_trx_line_id
AND jrcttl.customer_Trx_line_id = gl_dist.customer_trx_line_id
AND (ROUND(nvl(jrcttl.tax_amount,0)) <> ROUND(nvl(gl_dist.amount,0))
AND -- Need to check further if there is a way out in case tax amount in ja tax table itself is wrong ???
ROUND(nvl(jrcttl.func_tax_amount,0)) <> ROUND(nvl(gl_dist.acctd_amount,0))
)
AND gl_dist.customer_trx_id = jrctl.customer_trx_id
AND jrctl.customer_trx_id = cp_customer_trx_id
AND gl_dist.account_class IN ('TAX','FREIGHT');
SELECT
gl_dist.customer_trx_line_id ,
gl_dist.amount amount
FROM ra_cust_trx_line_gl_dist_all gl_dist,
ra_customer_trx_lines_all rctl
WHERE gl_dist.customer_trx_id = rctl.customer_trx_id
AND gl_dist.customer_trx_line_id = rctl.customer_trx_line_id
AND gl_dist.account_class IN ('TAX','FREIGHT')
AND rctl.line_type IN ('TAX','FREIGHT')
AND ROUND(nvl(gl_dist.amount,0)) <> ROUND(nvl(extended_amount,0))
AND gl_dist.customer_trx_id = cp_customer_trx_id ;
SELECT NVL(SUM(line_applied),0) line_applied,
NVL(SUM(tax_applied),0) tax_applied,
NVL(SUM(freight_applied),0) freight_applied,
NVL(SUM(amount_applied),0) amount_applied,
NVL(SUM(acctd_amount_applied_from),0) acctd_amount_applied_from,
NVL(SUM(acctd_amount_applied_to),0) acctd_amount_applied_to,
NVL(SUM( NVL(amount_applied,0) * cp_exchange_rate),0) acctd_amount_applied_from_calc,
NVL(SUM( NVL(amount_applied,0) * cp_exchange_rate_prev),0) acctd_amount_applied_to_calc
FROM ar_receivable_applications_all
WHERE customer_trx_id = cp_customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP';
SELECT application_type
FROM ar_receivable_applications_all
WHERE applied_customer_trx_id = cp_customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP';
SELECT NVL(SUM(line_applied),0) line_applied,
NVL(SUM(tax_applied),0) tax_applied,
NVL(SUM(freight_applied),0) freight_applied,
NVL(SUM(amount_applied),0) amount_applied
FROM ar_receivable_applications_all
WHERE applied_customer_trx_id = cp_customer_trx_id
AND application_type IN ('CM' ,'CASH')
AND display = 'Y'
AND status = 'APP';
SELECT NVL(SUM(line_applied),0) line_applied,
NVL(SUM(tax_applied),0) tax_applied,
NVL(SUM(freight_applied),0) freight_applied,
NVL(SUM(amount_applied),0) amount_applied
FROM ar_receivable_applications_all
WHERE applied_customer_trx_id = cp_customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP';
SELECT NVL(exchange_rate,1) exchange_rate,
set_of_books_id
FROM ra_customer_trx_all
WHERE customer_trx_id = cp_customer_trx_id;
SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,Value,SUBSTR (value,1,INSTR(value,',') -1)) utl_location
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT jrctl.customer_trx_line_id,
nvl(sum(jrcttl.tax_amount),0) tax_amount
FROM JAI_AR_TRX_LINES jrctl,
JAI_AR_TRX_TAX_LINES jrcttl
WHERE jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id
AND jrctl.customer_trx_id = cp_customer_trx_id
GROUP BY jrctl.customer_trx_line_id
HAVING ROUND(nvl(sum(jrcttl.tax_amount),0)) <>( SELECT ROUND(NVL(tax_amount,0))
FROM JAI_AR_TRX_LINES a
WHERE a.customer_trx_line_id = jrctl.customer_trx_line_id);
SELECT jtrx.customer_trx_id ,
NVL(SUM(jrctl.tax_amount),0) tax_amount
FROM JAI_AR_TRX_LINES jrctl,
JAI_AR_TRXS jtrx
WHERE jrctl.customer_trx_id = cp_customer_trx_id
AND jrctl.customer_trx_id = jtrx.customer_trx_id
GROUP BY jtrx.customer_trx_id
HAVING ROUND(NVL(SUM(jrctl.tax_amount),0)) <> ( SELECT ROUND(NVL(tax_amount,0))
FROM JAI_AR_TRXS a
WHERE a.customer_trx_id = jtrx.customer_trx_id);
UPDATE ra_customer_trx_lines_all
SET extended_amount = i.tax_amount,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_line_id = i.customer_trx_line_id
AND customer_trx_id = i.customer_trx_id;
'UPDATE ra_customer_trx_lines_all'||fnd_global.local_chr(10)||
'SET extended_amount = '||i.tax_amount||','||fnd_global.local_chr(10)||
' last_update_date = sysdate,'||fnd_global.local_chr(10)||
' last_updated_by = '||gn_bug_no||fnd_global.local_chr(10)||
'WHERE customer_trx_line_id = '||i.customer_trx_line_id||fnd_global.local_chr(10)||
'AND customer_trx_id = '||i.customer_trx_id||';';
UPDATE ra_cust_trx_line_gl_dist_all
SET amount = i.tax_amount,
acctd_amount = round(i.func_tax_amount,ln_precision),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_line_id = i.customer_trx_line_id
AND customer_trx_id = i.customer_trx_id;
'UPDATE ra_cust_trx_line_gl_dist_all'||fnd_global.local_chr(10)||
'SET amount = '||i.tax_amount||','||fnd_global.local_chr(10)||
' acctd_amount = round('||i.func_tax_amount||','||ln_precision||'),'||fnd_global.local_chr(10)||
' last_update_date = sysdate,'||fnd_global.local_chr(10)||
' last_updated_by = '||gn_bug_no||fnd_global.local_chr(10)||
'WHERE customer_trx_line_id = '||i.customer_trx_line_id||fnd_global.local_chr(10)||
'AND customer_trx_id = '||i.customer_trx_id||';';
UPDATE ra_cust_trx_line_gl_dist_all rec
SET amount = (SELECT nvl(sum(amount),0)
FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
AND rev_tax_frt.account_class <> 'REC'
),
acctd_amount = (SELECT nvl(sum(acctd_amount),0)
FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
AND rev_tax_frt.account_class <> 'REC'
),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND customer_trx_id = rec_get_cust_trx.customer_trx_id;
'UPDATE ra_cust_trx_line_gl_dist_all rec
SET amount = (SELECT nvl(sum(amount),0)
FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
AND rev_tax_frt.account_class <> '||''''||'REC'||''''||
'),
acctd_amount = (SELECT nvl(sum(acctd_amount),0)
FROM ra_cust_trx_line_gl_dist_all rev_tax_frt
WHERE rec.customer_trx_id = rev_tax_frt.customer_trx_id
AND rev_tax_frt.account_class <> '||''''||'REC'||''''||
'),
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE rec.account_class = '||''''||'REC'||''''||'
AND rec.latest_rec_flag = '||''''||'Y'||''''||'
AND customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
UPDATE ra_customer_trx_lines_all
SET extended_amount = rec_gl_dist_rctl.amount,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND customer_trx_line_id = rec_gl_dist_rctl.customer_trx_line_id;
'UPDATE ra_customer_trx_lines_all
SET extended_amount = '||rec_gl_dist_rctl.amount||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND customer_trx_line_id = '||rec_gl_dist_rctl.customer_trx_line_id||';';
|| rectified as a part of common (3,4,5). No seperate update required
*/
END IF;
|| rectified as a part of CM (1) No seperate update required
*/
END IF;
UPDATE ar_payment_schedules_all
SET amount_due_remaining = 0,
acctd_amount_due_remaining = 0,
tax_remaining = 0,
freight_remaining = 0,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id;
'UPDATE ar_payment_schedules_all
SET amount_due_remaining = 0,
acctd_amount_due_remaining = 0,
tax_remaining = 0,
freight_remaining = 0,
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
UPDATE ar_payment_schedules_all
SET amount_applied = amount_due_original,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id;
'UPDATE ar_payment_schedules_all
SET amount_applied = amount_due_original,
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||
' WHERE customer_trx_id = '|| rec_get_cust_trx.customer_trx_id||';';
||IF CM has more than one applications then manual steps needs to be carried out else update and generate log
||For Single line -> ADO, FO,TO have already been corrected . Now set TA = TO, FA = FO
*/
IF lv_generate_log = 'Y' THEN
IF ln_error_cnt = 0 THEN
jai_cmn_utils_pkg.print_log(lv_log_filename, fnd_global.local_chr(10));
UPDATE ar_receivable_applications_all
SET amount_applied = NVL(line_applied,0) + NVL(tax_applied,0) + NVL(freight_applied,0),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP';
'UPDATE ar_receivable_applications_all
SET amount_applied = NVL(line_applied,0) + NVL(tax_applied,0) + NVL(freight_applied,0),
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND application_type = '||''''||'CM'||''''||'
AND display = '||''''||'Y'||''''||'
AND status = '||''''||'APP'||''''||';';
UPDATE ar_receivable_applications_all
SET acctd_amount_applied_from = ROUND(amount_applied * rec_ra_customer_trx.exchange_rate, ln_precision),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP';
'UPDATE ar_receivable_applications_all
SET acctd_amount_applied_from = ROUND(amount_applied * '||rec_ra_customer_trx.exchange_rate||','|| ln_precision||'),
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND application_type = '||''''||'CM'||''''||'
AND display = '||''''||'Y'||''''||'
AND status = '||''''||'APP'||''''||';';
UPDATE ar_receivable_applications_all
SET acctd_amount_applied_to = ROUND(amount_applied * rec_ra_customer_trx_prev.exchange_rate, ln_precision),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND application_type = 'CM'
AND display = 'Y'
AND status = 'APP';
'UPDATE ar_receivable_applications_all
SET acctd_amount_applied_to = ROUND(amount_applied * '||rec_ra_customer_trx_prev.exchange_rate||','|| ln_precision||'),
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND application_type = '||''''||'CM'||''''||'
AND display = '||''''||'Y'||''''||'
AND status = '||''''||'APP'||''''||';';
UPDATE ar_payment_schedules_all inv_arps
SET tax_remaining = nvl(tax_original,0) - ( SELECT
nvl(sum(tax_applied),0) tot_tax_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.display = 'Y'
AND reca.status = 'APP'
) ,
freight_remaining = nvl(freight_original,0) - ( SELECT
nvl(sum(freight_applied),0) tot_frt_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.display = 'Y'
AND reca.status = 'APP'
) ,
amount_due_remaining = nvl(amount_due_original,0) - ( SELECT
nvl(sum(amount_applied),0) tot_amt_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.display = 'Y'
AND reca.status = 'APP'
) ,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE
customer_trx_id = rec_get_cust_trx.customer_trx_id;
'UPDATE ar_payment_schedules_all inv_arps
SET tax_remaining = nvl(tax_original,0) - ( SELECT
nvl(sum(tax_applied),0) tot_tax_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.display = '||''''||'Y'||''''||'
AND reca.status = '||''''||'APP'||''''||'
) ,
freight_remaining = nvl(freight_original,0) - ( SELECT
nvl(sum(freight_applied),0) tot_frt_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.display = '||''''||'Y'||''''||'
AND reca.status = '||''''||'APP'||''''||'
) ,
amount_due_remaining = nvl(amount_due_original,0) - ( SELECT
nvl(sum(amount_applied),0) tot_amt_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.display = '||''''||'Y'||''''||'
AND reca.status = '||''''||'APP'||''''||'
) ,
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE
customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
UPDATE ar_payment_schedules_all inv_arps
SET amount_credited = (SELECT
(nvl(sum(amount_applied),0) * (-1) )tot_amt_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.application_type = 'CM'
AND reca.display = 'Y'
AND reca.status = 'APP'
) ,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE
customer_trx_id = rec_get_cust_trx.customer_trx_id;
'UPDATE ar_payment_schedules_all inv_arps
SET amount_credited = (SELECT
(nvl(sum(amount_applied),0) * (-1) )tot_amt_applied
FROM
ar_receivable_applications_all reca
WHERE
reca.applied_payment_schedule_id = inv_arps.payment_schedule_id
AND reca.applied_customer_trx_id = inv_arps.customer_trx_id
AND reca.application_type = '||''''||'CM'||''''||'
AND reca.display = '||''''||'Y'||''''||'
AND reca.status = '||''''||'APP'||'
) ,
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE
customer_trx_id = '||rec_get_cust_trx.customer_trx_id||';';
UPDATE ar_payment_schedules_all
SET acctd_amount_due_remaining = ROUND(rec_payment_schedules.amount_due_remaining * rec_payment_schedules.exchange_rate, ln_precision),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND payment_schedule_id = rec_payment_schedules.payment_schedule_id;
'UPDATE ar_payment_schedules_all
SET acctd_amount_due_remaining = ROUND('||rec_payment_schedules.amount_due_remaining||' * '||rec_payment_schedules.exchange_rate||', '||ln_precision||'),
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND payment_schedule_id = '||rec_payment_schedules.payment_schedule_id||';';
UPDATE ar_payment_schedules_all
SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')) ,
actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND payment_schedule_id = rec_payment_schedules.payment_schedule_id;
'UPDATE ar_payment_schedules_all
SET status = DECODE (amount_due_remaining, 0, '||''''||'CL'||''''||', '||''''||'OP'||''''||'),
gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND payment_schedule_id = '||rec_payment_schedules.payment_schedule_id||';';
UPDATE ar_payment_schedules_all
SET status = DECODE (amount_due_remaining, 0, 'CL', 'OP'),
gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')) ,
actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('31/12/4712','DD/MM/YYYY')),
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_id = rec_get_cust_trx.customer_trx_id
AND payment_schedule_id = rec_payment_schedules.payment_schedule_id;
'UPDATE ar_payment_schedules_all
SET status = DECODE (amount_due_remaining, 0, '||''''||'CL'||''''||', '||''''||'OP'||''''||'),
gl_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
actual_date_closed = DECODE (amount_due_remaining, 0, SYSDATE, TO_DATE('||''''||'31/12/4712'||''''||','||''''||'DD/MM/YYYY'||''''||')) ,
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_id = '||rec_get_cust_trx.customer_trx_id||'
AND payment_schedule_id = '||rec_payment_schedules.payment_schedule_id||';';
UPDATE JAI_AR_TRX_LINES
SET tax_amount = rec_cur_sync_il_line_tax.tax_amount ,
total_amount = line_amount + rec_cur_sync_il_line_tax.tax_amount,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE customer_trx_line_id = rec_cur_sync_il_line_tax.customer_trx_line_id;
'UPDATE JAI_AR_TRX_LINES
SET tax_amount = '||rec_cur_sync_il_line_tax.tax_amount||' ,
total_amount = line_amount + '||rec_cur_sync_il_line_tax.tax_amount||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE customer_trx_line_id = '||rec_cur_sync_il_line_tax.customer_trx_line_id||';';
UPDATE JAI_AR_TRXS
SET tax_amount = rec_sync_il_hdr_tax.tax_amount ,
total_amount = line_amount + rec_sync_il_hdr_tax.tax_amount,
last_update_date = sysdate,
last_updated_by = gn_bug_no
WHERE CUSTOMER_TRX_ID = rec_sync_il_hdr_tax.customer_trx_id;
'UPDATE JAI_AR_TRXS
SET tax_amount = '||rec_sync_il_hdr_tax.tax_amount||' ,
total_amount = line_amount + '||rec_sync_il_hdr_tax.tax_amount||',
last_update_date = sysdate,
last_updated_by = '||gn_bug_no||'
WHERE CUSTOMER_TRX_ID = '||rec_sync_il_hdr_tax.customer_trx_id||';';