[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lookup_code
FROM ar_lookups
WHERE lookup_type = l_lookup_type
AND enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
trunc(sysdate))
AND nvl(trunc(end_date_active),trunc(sysdate)) ;
SELECT trunc(g.start_date) start_date,
trunc(g.end_date) end_date
FROM gl_period_statuses g,
gl_sets_of_books b
WHERE g.application_id = 222
AND g.set_of_books_id = l_set_of_books_id
AND g.set_of_books_id = b.set_of_books_id
AND g.period_type = b.accounted_period_type
AND g.adjustment_period_flag = 'N'
AND g.closing_status IN ('O','F') ;
SELECT set_of_books_id
INTO l_set_of_books_id
FROM ar_system_parameters ;
| the select clause. |
| SNAMBIAR 31-May-00 Bug 1290698 . Included type ENDORSEMENT also |
| BOE/BR
| SNAMBIAR 31-Jan-01 Bug 1620930 . |
| SNAMBIAR 02-Apr-01 Modified the cursor to pickup receivables trx|
| id -12 which is used for deduction chargeback|
| reversal
| M Raymond 30-JUL-02 Bug 2441496 - Need to add FINCHRG to
| list of cached receivables trx.
+===========================================================================*/
PROCEDURE Cache_Receivables_Trx (p_return_status IN OUT NOCOPY VARCHAR2 )
IS
CURSOR l_receivables_csr IS
SELECT receivables_trx_id,name,type,code_combination_id ,accounting_affect_flag,
gl_account_source /*Bug 2925924*/
FROM ar_receivables_trx
WHERE nvl(status,'A') = 'A'
AND type in ('ADJUST','ENDORSEMENT','FINCHRG')
AND receivables_trx_id not in (-11,-13 )
AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
trunc(sysdate))
AND nvl(trunc(end_date_active),trunc(sysdate)) ;
SELECT ussgl_transaction_code,context
FROM gl_ussgl_transaction_codes
WHERE chart_of_accounts_id = arp_global.chart_of_accounts_id
AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
trunc(sysdate))
AND nvl(trunc(end_date_active),trunc(sysdate)) ;
SELECT code_combination_id
FROM gl_code_combinations
WHERE chart_of_accounts_id = arp_global.chart_of_accounts_id
AND enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
trunc(sysdate))
AND nvl(trunc(end_date_active),trunc(sysdate)) ;
G_APPROVAL_TBL.DELETE;
G_REASON_TBL.DELETE;
G_ADJTYPE_TBL.DELETE;
G_RCVTRX_TBL.DELETE;
G_USSGL_TBL.DELETE;
G_GLPERIOD_TBL.DELETE;
G_CCID_TBL.DELETE;
SELECT amount_to,
amount_from
INTO l_approval_amount_to,
l_approval_amount_from
FROM ar_approval_user_limits
WHERE user_id = l_user_id
AND currency_code = p_inv_curr_code
AND document_type = 'ADJ';
| Ensure that approval data has been selected |
+-----------------------------------------------*/
IF ( p_approved_flag = FND_API.G_TRUE )
THEN
/*--------------------------------------------+
| Perform actual check of approval limits. |
+--------------------------------------------*/
IF (( p_adj_amount > l_approval_amount_to ) OR
( p_adj_amount < l_approval_amount_from ))
THEN
IF PG_DEBUG in ('Y', 'C') THEN
arp_util.debug('Within_approval_limits: ' || 'User ID: ' || l_user_id ||
' Amount: ' || p_adj_amount ||
' From: ' || l_approval_amount_from ||
' To: ' || l_approval_amount_to ||
' exceeds approval limit', G_MSG_HIGH );
SELECT *
INTO p_ps_rec
FROM ar_payment_schedules
WHERE payment_schedule_id = p_adj_rec.payment_schedule_id;
SELECT count(*)
INTO l_count
FROM RA_CUSTOMER_TRX_LINES
WHERE customer_trx_id = p_adj_rec.customer_trx_id AND
customer_trx_line_id = p_adj_rec.customer_trx_line_id ;
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'Validate_Payschd',
'payment_sch_id'
);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'AR_AAPI_NO_CUSTOMER_TRX_LINEID',
'payment_sch_id'
);
select invoice_currency_code into l_currency_code
from ra_customer_trx
where customer_trx_id=p_adj_rec.customer_trx_id;
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'AR_AAPI_INVALID_RCVABLE_TRX_ID',
'receivables_trx_id'
);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'AR_AAPI_INVALID_RCVABLE_TRX_ID',
'receivables_trx_id'
);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'AR_AAPI_NO_CCID_FOR_ACTIVITY',
'receivables_trx_id'
);
SELECT count(*)
INTO l_count
FROM gl_code_combinations
WHERE code_combination_id = p_adj_rec.code_combination_id
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(start_date_active, sysdate)
AND NVL(end_date_active, sysdate);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'AR_AAPI_INVALID_CCID',
'receivables_trx_id'
);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
'AR_AAPI_OVERRIDE_CCID_DISALLOW',
'receivables_trx_id'
);
select set_of_books_id
into l_set_of_books_id
from ar_system_parameters;
SELECT count(*)
INTO l_count
FROM gl_period_statuses g,
gl_sets_of_books b
WHERE g.application_id = 222
AND g.set_of_books_id = l_set_of_books_id
AND g.set_of_books_id = b.set_of_books_id
AND g.period_type = b.accounted_period_type
AND g.adjustment_period_flag = 'N'
AND g.closing_status IN ('O','F')
AND trunc(p_gl_date) BETWEEN nvl(trunc(g.start_date),
trunc(p_gl_date))
AND nvl(trunc(g.end_date),trunc(p_gl_date)) ;
SELECT descriptive_flexfield_name
INTO l_flex_name
FROM fnd_descriptive_flexs
WHERE application_id = arp_global.G_AR_APP_ID AND
application_table_name like 'AR_ADJUSTMENTS' ;
SELECT count(*)
INTO l_count
FROM ar_cash_receipts
WHERE cash_receipt_id = p_adj_rec.associated_cash_receipt_id ;
SELECT b.sum_orig sum_orig
,b.sum_acctd_orig sum_acctd_orig
,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',amt
,'APP_CHRG',DECODE(a.line_type,'LINE',amt,0) * -1,0))) CHRG_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',acctd_amt
,'APP_CHRG',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_CHRG_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
,'APP_FRT',DECODE(a.line_type,'LINE',amt,0) * -1,0))) FRT_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
,'APP_FRT',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_FRT_ON_REV_LINE
,SUM((DECODE(a.activity_bucket,'ED_FRT',amt,0))) ED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'ED_FRT',acctd_amt,0))) ACCTD_ED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'UNED_FRT',amt,0))) UNED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'UNED_FRT',acctd_amt,0))) ACCTD_UNED_FRT_REV_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_LINE',amt
,'APP_LINE',(amt * -1)
,'ED_LINE' ,amt
,'UNED_LINE',amt -- line
,'ADJ_TAX' ,amt
,'APP_TAX' ,(amt * -1)
,'ED_TAX' ,amt
,'UNED_TAX',amt --tax
,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',amt,0) * -1)
,'APP_CHRG',(DECODE(a.line_type,'CHARGES',amt,0) * -1)
,0))) REM_TYPE_LINE
,SUM((DECODE(a.activity_bucket,'ADJ_LINE',acctd_amt
,'APP_LINE',(acctd_amt * -1)
,'ED_LINE' ,acctd_amt
,'UNED_LINE',acctd_amt -- line
,'ADJ_TAX' ,acctd_amt
,'APP_TAX' ,(acctd_amt * -1)
,'ED_TAX' ,acctd_amt
,'UNED_TAX',acctd_amt --tax
,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',acctd_amt,0) * -1)
,'APP_CHRG',(DECODE(a.line_type,'CHARGES',acctd_amt,0) * -1)
,0))) ACCTD_REM_TYPE_LINE
,b.customer_trx_line_id CUSTOMER_TRX_LINE_ID,
ctl1.line_type line_type
FROM
(SELECT SUM( NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0) ) amt,
SUM( NVL(ard.acctd_amount_cr,0) - NVL(ard.acctd_amount_dr,0)) acctd_amt,
ard.ref_customer_trx_line_id,
ard.ref_account_class,
ard.activity_bucket,
ctl.line_type
FROM ar_distributions ard,
ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
AND (ctl.customer_trx_line_id = p_customer_trx_line_id
OR
(ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
AND ctl.line_type = 'TAX'
)
)
GROUP BY
ard.ref_customer_trx_line_id,
ard.ref_account_class,
ard.activity_bucket,
ctl.line_type) a,
(SELECT SUM(ctlgd.AMOUNT) sum_orig,
SUM(ctlgd.ACCTD_AMOUNT) sum_acctd_orig,
ctlgd.customer_trx_line_id
FROM ra_cust_trx_line_gl_dist ctlgd,
ra_customer_trx_lines ctll
WHERE ctlgd.customer_trx_id = p_customer_trx_id
AND ctlgd.customer_trx_line_id = ctll.customer_trx_line_id
AND (ctll.customer_trx_line_id = p_customer_trx_line_id
OR
(ctll.link_to_cust_trx_line_id = p_customer_trx_line_id
AND ctll.line_type = 'TAX'
)
)
GROUP BY ctlgd.customer_trx_line_id) b,
ra_customer_trx_lines ctl1
WHERE a.ref_customer_trx_line_id (+) = b.customer_trx_line_id
and b.customer_trx_line_id=ctl1.customer_trx_line_id
GROUP BY b.customer_trx_line_id,
b.sum_orig,
b.sum_acctd_orig, ctl1.line_type;
SELECT creation_sign,
allow_overapplication_flag
INTO l_creation_sign,
l_allow_overapp_flag
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_ps_rec.cust_trx_type_id;
SELECT creation_sign,
allow_overapplication_flag
INTO l_creation_sign,
l_allow_overapp_flag
FROM ra_cust_trx_types
WHERE cust_trx_type_id = p_ps_rec.cust_trx_type_id;
SELECT sum(DECODE (lines.line_type,
'TAX',0,
'FREIGHT',0 , 1) *
DECODE(ct.complete_flag, 'N',
0, lines.amount_due_remaining)), -- line adr
sum(DECODE (lines.line_type,
'TAX',1,0) *
DECODE(ct.complete_flag,
'N', 0,
lines.amount_due_remaining )), -- tax adr
sum(DECODE (lines.line_type,
'TAX',0,
'FREIGHT',0 , 1) *
DECODE(ct.complete_flag, 'N',
0, lines.amount_due_original)), -- line adr org
sum(DECODE (lines.line_type,
'TAX',1,0) *
DECODE(ct.complete_flag,
'N', 0,
lines.amount_due_original)), -- tax adr org
max(ct.invoice_currency_code) -- curr code
INTO l_line_remaining,
l_tax_remaining,
l_line_org,
l_tax_org,
l_invoice_currency_code
FROM ra_customer_trx ct,
ra_customer_trx_lines lines
WHERE (lines.customer_Trx_line_id = p_adj_rec.customer_trx_line_id or
lines.link_to_cust_trx_line_id = p_adj_rec.customer_trx_line_id)
AND ct.customer_Trx_id = lines.customer_trx_id;
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
l_message_name,
'Overapplication'
);
insert into ar_llca_adj_trx_errors_gt
(
customer_trx_id,
customer_trx_line_id,
receivables_trx_id,
error_message,
invalid_value
)
values
(
l_customer_trx_id,
l_customer_trx_line_id,
l_receivables_trx_id,
l_message_name,
'Overapplication'
);