[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lines.days_start,
lines.days_to,
lines.type
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and buckets.aging_bucket_id = to_number(p_bucket_name)
and nvl(buckets.status,'A') = 'A'
order by lines.bucket_sequence_num
;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code0
from dual ;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code1
from dual ;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code2
from dual ;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code3
from dual ;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code4
from dual ;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code5
from dual ;
select decode(l_bucket_line_type ,
'DISPUTE_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'PENDADJ_ONLY' , 'OIR_AGING_' || l_bucket_line_type ,
'DISPUTE_PENDADJ', 'OIR_AGING_' || l_bucket_line_type ,
'OIR_AGING_' || to_char(l_bucket_days_from) || '_' || to_char(l_bucket_days_to)
) into p_bucket_status_code6
from dual ;
select lines.days_start,
lines.days_to,
lines.report_heading1,
lines.report_heading2,
lines.type
from ar_aging_bucket_lines lines,
ar_aging_buckets buckets
where lines.aging_bucket_id = buckets.aging_bucket_id
and buckets.aging_bucket_id = to_number(p_bucket_name)
and nvl(buckets.status,'A') = 'A'
order by lines.bucket_sequence_num
;
SELECT sum(amt), sum(b0*amt), sum(b1*amt), sum(b2*amt), sum(b3*amt), sum(b4*amt), sum(b5*amt), sum(b6*amt)
FROM (select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining) amt,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_irec_user_acct_sites_all AcctSites
where ps.status = 'OP'
and AcctSites.user_id=FND_GLOBAL.USER_ID()
and AcctSites.customer_id=ps.customer_id
and AcctSites.customer_site_use_id=ps.customer_site_use_id
and AcctSites.session_id=p_session_id
and AcctSites.customer_id= p_customer_id
and AcctSites.org_id = ps.org_id
and upper(p_currency_code) = ps.invoice_currency_code
and 'CM' <> ps.class
and 'PMT' <> ps.class
and 'GUAR' <> ps.class
) ;
SELECT SUM(amt), SUM(amt*b0),SUM(amt*b1),SUM(amt*b2),SUM(amt*b3),SUM(amt*b4),SUM(amt*b5),SUM(amt*b6)
FROM (
select decode(p_currency_code, NULL, ps.acctd_amount_due_remaining,
ps.amount_due_remaining) amt,
decode(v_bucket_line_type_0,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_0,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_0,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b0,
decode(v_bucket_line_type_1,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_1,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_1,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b1,
decode(v_bucket_line_type_2,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_2,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_2,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b2,
decode(v_bucket_line_type_3,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_3,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_3,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b3,
decode(v_bucket_line_type_4,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_4,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_4,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b4,
decode(v_bucket_line_type_5,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_5,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_5,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b5,
decode(v_bucket_line_type_6,
'DISPUTE_ONLY',decode(nvl(ps.amount_in_dispute,0),0,0,1),
'PENDADJ_ONLY',decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
'DISPUTE_PENDADJ',decode(nvl(ps.amount_in_dispute,0),
0,decode(nvl(ps.amount_adjusted_pending,0),0,0,1),
1),
decode( greatest(v_bucket_days_from_6,
ceil(p_as_of_date-ps.due_date)),
least(v_bucket_days_to_6,
ceil(p_as_of_date-ps.due_date)),1,
0)
* decode(nvl(ps.amount_in_dispute,0), 0, 1,
decode(v_bucket_category,
'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))
* decode(nvl(ps.amount_adjusted_pending,0), 0, 1,
decode(v_bucket_category,
'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
1))) b6
from ar_payment_schedules ps,
ar_irec_user_acct_sites_all AcctSites
where ps.status = 'OP'
and AcctSites.user_id=FND_GLOBAL.USER_ID()
and AcctSites.customer_id=ps.customer_id
and AcctSites.customer_site_use_id=ps.customer_site_use_id
and AcctSites.session_id=p_session_id
and AcctSites.org_id = ps.org_id
and p_currency_code = ps.invoice_currency_code
and 'CM' <> ps.class
and 'PMT' <> ps.class
and 'GUAR' <> ps.class
);
select 'Y' into l_valid_user
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where fcr.request_id = p_request_id
and fcr.requested_by = p_user_id
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcp.concurrent_program_name = 'RAXINV_SEL';
select status_code into l_status
from fnd_concurrent_requests
where request_id = p_request_id;
| inserts the record in ar_irec_print_requests table.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_id_list The ids to be submitted
| p_list_type List type
| p_description Description
| p_template_id Template id
| p_customer_id Customer id
| p_site_id Customer Site Use Id
|
| RETURNS
| x_req_id_list Request Id
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 01-Aug-2005 rsinthre Created
|
*=======================================================================*/
PROCEDURE oir_bpa_print_invoices(
p_id_list IN VARCHAR2,
x_req_id_list OUT NOCOPY VARCHAR2,
p_list_type IN VARCHAR2,
p_description IN VARCHAR2 ,
p_customer_id IN NUMBER,
p_customer_site_id IN NUMBER DEFAULT NULL,
p_user_name IN VARCHAR2
) IS
l_start_location NUMBER default 0;
l_last_update_login NUMBER(15);
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
INSERT INTO AR_IREC_PRINT_REQUESTS(REQUEST_ID, CUSTOMER_ID, CUSTOMER_SITE_USE_ID, REQUESTED_BY, PROGRAM_NAME, UPLOAD_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (to_number(l_req_id_list), p_customer_id, p_customer_site_id, l_created_by, p_description, sysdate, l_created_by, l_creation_date, l_last_update_login, l_last_update_date, l_last_updated_by);
INSERT INTO AR_IREC_PRINT_REQUESTS(REQUEST_ID, CUSTOMER_ID, CUSTOMER_SITE_USE_ID, REQUESTED_BY, PROGRAM_NAME, UPLOAD_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (to_number(l_request_id), p_customer_id, p_customer_site_id, l_created_by, p_description, sysdate, l_created_by, l_creation_date, l_last_update_login, l_last_update_date, l_last_updated_by);