[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT customer_trx_id
INTO p_customer_trx_id
FROM ra_customer_trx
WHERE trx_number = p_trx_number;
SELECT customer_trx_line_id
INTO p_inv_customer_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_inv_customer_trx_id
AND line_number = p_inv_line_number
AND line_type = 'LINE';
SELECT customer_trx_id
INTO p_inv_customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_inv_customer_trx_line_id
AND line_type = 'LINE';
SELECT ps.payment_schedule_id,
cm.trx_date,
ps.gl_date,
ps.amount_due_remaining,
cm.paying_customer_id,
cm.invoice_currency_code
INTO p_cm_ps_id,
p_cm_trx_date,
p_cm_gl_date,
p_cm_amount_rem,
p_cm_customer_id,
p_cm_currency_code
FROM ra_customer_trx cm,
ar_payment_schedules ps
WHERE ps.customer_trx_id = cm.customer_trx_id
AND cm.customer_trx_id = p_cm_customer_trx_id;
SELECT
ot.customer_id ,
ot.cust_trx_type_id ,
ot.trx_due_date ,
ot.trx_date,
ot.trx_gl_date ,
ot.allow_overapplication_flag ,
ot.natural_application_only_flag ,
ot.creation_sign ,
ot.payment_schedule_id ,
greatest(p_cm_gl_date,ot.trx_gl_date,
decode(pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date,
ot.trx_gl_date)) gl_date,
ot.balance_due_functional,
ot.invoice_currency_code
INTO
p_inv_customer_id ,
p_inv_cust_trx_type_id ,
p_inv_due_date ,
p_inv_trx_date,
p_inv_gl_date ,
p_allow_overappln_flag ,
p_natural_appln_only_flag ,
p_creation_sign ,
l_applied_payment_schedule_id ,
p_app_gl_date, --this is the application gl_date
p_inv_amount_rem,
p_inv_currency_code
FROM
ar_open_trx_v ot
WHERE
ot.customer_trx_id = p_inv_customer_trx_id and
ot.invoice_currency_code = p_cm_currency_code and
ot.status=decode(p_show_closed_invoices,'Y',ot.status,'OP') and
ot.terms_sequence_number = p_installment;
SELECT
ot.customer_id ,
ot.cust_trx_type_id ,
ot.trx_due_date ,
ot.trx_date,
ot.trx_gl_date ,
ot.allow_overapplication_flag ,
ot.natural_application_only_flag ,
ot.creation_sign ,
ot.payment_schedule_id ,
greatest(p_cm_gl_date,ot.trx_gl_date,
decode(pg_profile_appln_gl_date_def,
'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date,
ot.trx_gl_date)) gl_date,
ot.balance_due_functional,
ot.invoice_currency_code
INTO
p_inv_customer_id ,
p_inv_cust_trx_type_id ,
p_inv_due_date ,
p_inv_trx_date,
p_inv_gl_date ,
p_allow_overappln_flag,
p_natural_appln_only_flag,
p_creation_sign,
l_applied_payment_schedule_id,
p_app_gl_date, --this is the defaulted application gl_date
p_inv_amount_rem,
p_inv_currency_code
FROM
ar_open_trx_v ot
WHERE
ot.customer_trx_id = p_inv_customer_trx_id and
ot.invoice_currency_code = p_cm_currency_code and
ot.status=decode(p_show_closed_invoices,'Y',ot.status,'OP') and
ot.terms_sequence_number = p_installment and
ot.customer_id IN (
SELECT rcr.related_cust_account_id
FROM hz_cust_acct_relate rcr
WHERE rcr.status='A' and
rcr.cust_account_id= p_cm_customer_id
and rcr.bill_to_flag = 'Y'
UNION
SELECT p_cm_customer_id
FROM dual
UNION
SELECT rel.related_cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND acc.cust_account_id = p_cm_customer_id
AND p_cm_trx_date BETWEEN effective_start_date
AND effective_end_date
);
SELECT ps.payment_schedule_id
INTO l_inv_ps_id
FROM ra_customer_trx ct,
ar_payment_schedules ps
WHERE ct.customer_trx_id = p_inv_customer_trx_id
AND ct.customer_trx_id = ps.customer_trx_id
--AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
AND ps.terms_sequence_number = p_installment
;
SELECT ps.payment_schedule_id
INTO l_inv_ps_id
FROM ra_customer_trx ct,
ar_payment_schedules ps
WHERE ct.customer_trx_id = p_inv_customer_trx_id
AND ct.customer_trx_id = ps.customer_trx_id
--AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
;
SELECT ra.applied_customer_trx_id, ra.applied_payment_schedule_id, ra.gl_date
FROM ar_receivable_applications ra,
ar_payment_schedules ps
WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
AND ra.receivable_application_id = p_ra_id
AND ra.display = 'Y'
AND ra.status = 'APP'
AND ps.reserved_value IS NULL
AND ps.reserved_type IS NULL;
SELECT receivable_application_id, gl_date
INTO p_receivable_application_id, p_apply_gl_date
FROM ar_receivable_applications ra
WHERE ra.customer_trx_id = p_cm_customer_trx_id
AND ra.applied_payment_schedule_id = p_applied_payment_schedule_id
AND ra.display = 'Y'
AND ra.status = 'APP'
AND ra.application_type = 'CM';
SELECT gl_date, customer_trx_id
INTO l_apply_gl_date, p_cm_customer_trx_id
FROM ar_receivable_applications
WHERE receivable_application_id =
p_receivable_application_id;
SELECT count(*)
INTO l_valid
FROM AR_RECEIVABLE_APPLICATIONS ra
WHERE ra.receivable_application_id = p_receivable_application_id
and ra.display = 'Y'
and ra.status = p_application_type
and ra.application_type = 'CM';
SELECT customer_trx_id, terms_sequence_number
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_payment_schedule_id and
payment_schedule_id >0 and
class in ('INV','DM'); -- Should we include DM, DEP, GUAR, CB?
SELECT terms_sequence_number
INTO p_installment
FROM ar_payment_schedules
WHERE customer_trx_id = p_inv_customer_trx_id;
SELECT customer_trx_id, terms_sequence_number
FROM ar_payment_schedules
WHERE payment_schedule_id = p_applied_payment_schedule_id and
payment_schedule_id >0 and
class in ('INV','DM'); -- Should we include BR, DEP, GUAR, CB?
SELECT gl_date
INTO p_cm_gl_date
FROM ar_payment_schedules
WHERE customer_trx_id = l_cm_customer_trx_id;