[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.insert_batch()');
SELECT NVL(copy_doc_number_flag, 'N'),
NVL(allow_duplicate_trx_num_flag, 'N')
FROM ra_batch_sources
WHERE batch_source_id = p_batch_source_id;
SELECT 'Y' -- already exists in the transaction table
FROM ra_customer_trx
WHERE batch_source_id = p_batch_source_id
AND trx_number = p_trx_number
AND customer_trx_id <> NVL(p_customer_trx_id, -99)
UNION
SELECT 'Y' -- already exists in the interim table
FROM ra_recur_interim ri,
ra_customer_trx ct
WHERE ct.customer_trx_id = ri.customer_trx_id
AND ct.batch_source_id = p_batch_source_id
AND ri.trx_number = p_trx_number
AND NVL(ri.new_customer_trx_id, -98) <> NVL(p_customer_trx_id, -99)
UNION
SELECT 'Y' -- already exists in the interface table
FROM ra_batch_sources bs,
ra_interface_lines ril
WHERE ril.batch_source_name = bs.name
AND bs.batch_source_id = p_batch_source_id
AND ril.trx_number = p_trx_number
AND ril.customer_trx_id <> NVL(p_customer_trx_id, -99);
SELECT 'Y' --already exists
INTO l_temp
FROM ra_recur_interim ri,
ra_customer_trx ct
WHERE ct.customer_trx_id = ri.customer_trx_id
AND ct.cust_trx_type_id = p_cust_trx_type_id
AND ri.doc_sequence_value = p_doc_sequence_value
AND NVL(ri.new_customer_trx_id, -98)
<> NVL(p_customer_trx_id, -99)
UNION
SELECT 'Y'
FROM ra_cust_trx_types ctt,
ra_interface_lines ril
WHERE ril.cust_trx_type_name = ctt.name(+)
AND NVL(ril.cust_trx_type_id,
ctt.cust_trx_type_id) = p_cust_trx_type_id
AND ril.document_number = p_doc_sequence_value
AND ril.customer_trx_id <> NVL(p_customer_trx_id, -99);
SELECT count(*)
INTO l_count
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_number = p_line_number
AND line_type = 'LINE'
AND customer_trx_line_id <> nvl(p_customer_trx_line_id, -100);
| Checks to see if the line that is about to be deleted is the last line |
| on the transaction. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id |
| p_display_message |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 25-JUL-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE check_has_one_line( p_customer_trx_id IN NUMBER,
p_display_message IN varchar2 default 'Y' )
IS
l_count number;
SELECT count(*)
INTO l_count
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND link_to_cust_trx_line_id is NULL;
| because the payment schedules have not yet been updated. |
+-------------------------------------------------------------------*/
arp_trx_util.get_summary_trx_balances( p_customer_trx_id,
p_trx_open_receivables_flag,
l_line_original,
l_line_remaining,
l_tax_original,
l_tax_remaining,
l_freight_original,
l_freight_remaining,
l_charges_original,
l_charges_remaining,
l_total_original,
l_total_remaining );
SELECT SUM(
DECODE( ctl.line_type,
'TAX', 0,
'FREIGHT', 0,
ctl.extended_amount
)
),
SUM(
DECODE( ctl.line_type,
'TAX', ctl.extended_amount,
0 )
),
SUM(
DECODE( ctl.line_type,
'FREIGHT', ctl.extended_amount,
0 )
)
INTO l_new_line,
l_new_tax,
l_new_freight
FROM ra_customer_trx_lines ctl
WHERE customer_trx_id = p_customer_trx_id;
select nvl(sum(amount),0),nvl(sum(line_adjusted),0),nvl(sum(tax_adjusted),0),nvl(sum(freight_adjusted),0)
into l_commit_adj_amount,l_commit_line_amount,l_commit_tax_amount,l_commit_frt_amount
from ar_adjustments
where customer_trx_id = p_previous_customer_trx_id
and receivables_trx_id = -1;
| 08-Sep-97 Debbie Jancis Modified the select when |
| pay_unrelated_invoice_flag is N to fix |
| defaulting problem in Bug 462569. |
+===========================================================================*/
FUNCTION validate_paying_customer( p_paying_customer_id IN NUMBER,
p_trx_date IN date,
p_bill_to_customer_id IN NUMBER,
p_ct_prev_paying_customer_id IN NUMBER,
p_currency_code IN varchar2,
p_pay_unrelated_invoices_flag IN varchar2,
p_ct_prev_trx_date IN date)
RETURN BOOLEAN
IS
l_paying_customer_is_valid varchar2(1);
SELECT 'Y'
INTO l_paying_customer_is_valid
FROM hz_cust_accounts cust_acct
WHERE cust_acct.cust_account_id = p_paying_customer_id
AND (
cust_acct.cust_account_id = p_ct_prev_paying_customer_id
OR
(
cust_acct.status = 'A'
)
);
SELECT 'Y'
INTO l_paying_customer_is_valid
FROM hz_cust_accounts cust_acct
WHERE cust_acct.cust_account_id = p_paying_customer_id
AND (
cust_acct.cust_account_id = p_ct_prev_paying_customer_id
OR
(
cust_acct.status = 'A'
)
)
AND EXISTS
(
SELECT 'X'
FROM hz_cust_acct_relate cr
WHERE cr.related_cust_account_id = p_bill_to_customer_id
AND cr.status = 'A'
AND cr.bill_to_flag = 'Y'
AND CUST_ACCT.CUST_ACCOUNT_ID = CR.CUST_ACCOUNT_ID
UNION ALL
SELECT 'X'
FROM dual
where cust_acct.cust_account_id = TO_NUMBER(p_ct_prev_paying_customer_id)
UNION ALL
SELECT 'X'
FROM dual
WHERE cust_acct.cust_account_id =TO_NUMBER(p_bill_to_customer_id)
UNION ALL
SELECT 'X'
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND rel.related_cust_account_id = p_bill_to_customer_id
AND p_ct_prev_trx_date BETWEEN effective_start_date
AND effective_end_date
AND CUST_ACCT.CUST_ACCOUNT_ID = ACC.CUST_ACCOUNT_ID
);
| This cursor is used to branch code into a correct select statement |
| in the payment method validation. |
+--------------------------------------------------------------------*/
CURSOR receipt_creation_method_cur IS
SELECT arc.creation_method_code
FROM ar_receipt_methods arm,
ar_receipt_classes arc
WHERE arm.receipt_class_id = arc.receipt_class_id
AND arm.receipt_method_id = p_receipt_method_id;
SELECT 'invoice date is ok',
arp_bal_util.get_commitment_balance(
p_initial_customer_trx_id,
tt.TYPE,
l_so_source_code,
'N')
INTO l_temp,
l_commit_bal
FROM ra_customer_trx t,
ra_cust_trx_types tt
WHERE t.customer_trx_id = p_initial_customer_trx_id
AND t.cust_trx_type_id = tt.cust_trx_type_id
AND p_trx_date
BETWEEN NVL( t.start_date_commitment, p_trx_date )
AND NVL( t.end_date_commitment, p_trx_date );
SELECT 'invoice date is ok'
INTO l_temp
FROM so_agreements
WHERE agreement_id = p_agreement_id
AND p_trx_date BETWEEN NVL(TRUNC(start_date_active),
p_trx_date )
AND NVL(TRUNC(end_date_active),
p_trx_date);
SELECT 'invoice date is ok'
INTO l_temp
FROM ra_batch_sources
WHERE batch_source_id = p_batch_source_id
AND p_trx_date BETWEEN NVL(start_date, p_trx_date)
AND NVL(end_date, p_trx_date);
SELECT tax_calculation_flag
INTO l_temp
FROM ra_cust_trx_types
WHERE CUST_TRX_TYPE_ID = p_cust_trx_type_id
AND p_trx_date BETWEEN START_DATE
AND NVL(END_DATE, p_trx_date);
SELECT 'invoice date is ok'
INTO l_temp
FROM ra_terms
WHERE term_id = p_term_id
AND p_trx_date BETWEEN START_DATE_ACTIVE
AND NVL(END_DATE_ACTIVE, p_trx_date);
SELECT 'invoice date is ok'
INTO l_temp
FROM ORG_FREIGHT
WHERE freight_code = p_ship_method_code
AND organization_id =
to_number(oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id))
AND p_trx_date < NVL(TRUNC(DISABLE_DATE), p_trx_date + 1);
SELECT 'reason code is ok'
INTO l_temp
FROM ar_lookups
WHERE lookup_type = 'CREDIT_MEMO_REASON'
AND lookup_code = p_reason_code
AND p_trx_date
BETWEEN NVL( start_date_active, p_trx_date )
AND NVL( end_date_active, p_trx_date );
SELECT 'status code is ok'
INTO l_temp
FROM ar_lookups
WHERE lookup_type = 'INVOICE_TRX_STATUS'
AND lookup_code = p_status_trx
AND p_trx_date
BETWEEN NVL( start_date_active, p_trx_date )
AND NVL( end_date_active, p_trx_date );
SELECT 'invoice date is ok'
INTO l_temp
FROM ra_salesreps
WHERE salesrep_id = p_primary_salesrep_id
AND p_trx_date BETWEEN NVL(start_date_active,
p_trx_date)
AND NVL(end_date_active,
p_trx_date);
SELECT 'invoice date is ok'
INTo l_temp
FROM fnd_currencies
WHERE currency_code = p_invoice_currency_code
AND p_trx_date BETWEEN NVL(START_DATE_ACTIVE, p_trx_date)
AND NVL(END_DATE_ACTIVE, p_trx_date);
SELECT 'invalid_payment method'
INTO l_temp
FROM ar_receipt_methods arm,
ar_receipt_classes arc
WHERE arm.receipt_class_id = arc.receipt_class_id
AND arm.receipt_method_id = p_receipt_method_id
AND p_trx_date BETWEEN NVL(arm.start_date,
p_trx_date)
AND NVL(arm.end_date,
p_trx_date)
AND rownum = 1;
SELECT arp_trx_defaults_3.get_party_id(paying_customer_id),
arp_trx_defaults_3.get_party_id(bill_to_customer_id)
INTO l_paying_customer_id,l_bill_to_customer_id
FROM RA_CUSTOMER_TRX
WHERE customer_trx_id=p_customer_trx_id;
SELECT 'invalid_payment method'
INTO l_temp
FROM ar_receipt_methods arm,
ar_receipt_method_accounts arma,
ce_bank_accounts cba,
ce_bank_acct_uses aba,
ar_receipt_classes arc,
ce_bank_branches_v bp
WHERE arm.receipt_method_id = arma.receipt_method_id
AND arm.receipt_class_id = arc.receipt_class_id
AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
/* New Condition added Begin*/
AND bp.branch_party_id = cba.bank_branch_id
AND p_trx_date <= NVL(bp.end_date,p_trx_date)
AND (cba.currency_code = p_invoice_currency_code or
cba.receipt_multi_currency_flag ='Y') /* New condition */
/*Removing the join consition based on currency code as part of bug fix 5346710
AND (arc.creation_method_code='MANUAL'
or (arc.creation_method_code='AUTOMATIC'
and ( (nvl(arm.payment_channel_code,'*') = 'CREDIT_CARD' )
or
(nvl(arm.payment_channel_code,'*') <> 'CREDIT_CARD'
AND p_invoice_currency_code in
(select currency_code from iby_fndcpt_payer_assgn_instr_v where
party_id in (l_paying_customer_id,l_bill_to_customer_id))))))*/
/* New Condition added Ends*/
-- AND aba.set_of_books_id = arp_global.set_of_books_id
AND arm.receipt_method_id = p_receipt_method_id
AND p_trx_date < NVL(cba.end_date,
TO_DATE('01/01/2200','DD/MM/YYYY') )
AND p_trx_date BETWEEN NVL(arm.start_date,
p_trx_date)
AND NVL(arm.end_date,
p_trx_date)
AND p_trx_date BETWEEN NVL(arma.start_date,
p_trx_date)
AND NVL(arma.end_date,
p_trx_date)
AND rownum = 1;
SELECT MIN(s.name),
TO_CHAR(MIN(ctl.line_number))
INTO l_temp,
l_temp2
FROM ra_cust_trx_line_salesreps ls,
ra_customer_trx_lines ctl,
ra_salesreps s
WHERE ls.salesrep_id = s.salesrep_id
AND ls.customer_trx_id = p_customer_trx_id
AND ls.customer_trx_line_id = ctl.customer_trx_line_id(+)
AND p_trx_date NOT BETWEEN NVL(s.start_date_active,
p_trx_date)
AND NVL(s.end_date_active,
p_trx_date);
| If no line number has been selected, |
| this is a default salescredit line. |
+----------------------------------------*/
IF (l_temp2 IS NOT NULL)
THEN
/* Bug 2191739 - call to message API for degovtized message */
add_to_error_list(
p_error_mode,
p_error_count,
p_customer_trx_id,
p_trx_number,
l_temp2,
NULL,
gl_public_sector.get_message_name
(p_message_name => 'AR_TW_BAD_DATE_SALESREP',
p_app_short_name => 'AR'),
'TGW_HEADER.TRX_DATE',
'SALESREP_NAME',
l_temp,
'LINE_NUMBER',
l_temp2
);
SELECT TO_CHAR(MIN(lines.line_number))
INTO l_temp
FROM ra_customer_trx_lines lines,
ar_memo_lines aml
WHERE lines.customer_trx_id = p_customer_trx_id
AND lines.memo_line_id = aml.memo_line_id
AND p_trx_date NOT BETWEEN NVL(aml.start_date, p_trx_date)
AND NVL(aml.end_date, p_trx_date);
SELECT NVL(d.gl_date, rec.gl_date),
DECODE(NVL(d.gl_date, rec.gl_date),
rec.gl_date, l.extended_amount,
0),
d.gl_date,
rec.gl_date
FROM ra_cust_trx_line_gl_dist rec,
ra_cust_trx_line_gl_dist d,
ra_customer_trx_lines l,
ra_customer_trx t
WHERE l.customer_trx_line_id = d.customer_trx_line_id(+)
AND l.customer_trx_line_id = p_customer_trx_line_id
AND l.customer_trx_id = t.customer_trx_id
AND rec.customer_trx_id = l.customer_trx_id
AND rec.account_class = 'REC'
AND rec.latest_rec_flag = 'Y'
AND d.account_set_flag(+) = 'N'
GROUP by d.customer_trx_line_id,
d.gl_date,
rec.gl_date,
l.extended_amount
HAVING (
(
SUM(d.amount) <> DECODE( nvl(d.gl_date, rec.gl_date),
rec.gl_date, l.extended_amount,
0)
)
AND -- Changed 'OR' into 'AND'. Bug 1332304.
(
SUM(d.percent) <> DECODE( nvl(d.gl_date, rec.gl_date),
rec.gl_date, 100,
0)
)
) OR
SUM(d.cust_trx_line_gl_dist_id) IS NULL
ORDER BY d.gl_date;