[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
FROM ra_cm_requests_all
WHERE request_id = p_item_key;
/*7367350 storing internal comment and inserting notes*/
l_internal_comment VARCHAR2(1760) DEFAULT NULL;
SELECT name, display_name
FROM wf_users
WHERE orig_system = 'PER'
AND orig_system_id = emp_id;
SELECT name, display_name
FROM wf_users
WHERE orig_system = 'FND_USR'
AND orig_system_id = user_id;
SELECT employee_id
FROM fnd_user
WHERE user_id = user_id1;
select r.request_id,
r.customer_trx_id,
r.total_amount,
r.cm_reason_code,
l.meaning,
r.created_by,
r.comments,
r.line_credits_flag,
r.line_amount,
r.tax_amount,
r.freight_amount,
r.ORIG_TRX_NUMBER,
r.TAX_EX_CERT_NUM,
r.internal_comment
into l_workflow_document_id,
l_customer_trx_id,
l_amount,
p_reason,
p_reason_meaning,
l_created_by,
p_comments,
l_line_credit_flag,
l_line_amount,
l_tax_amount,
l_freight_amount,
l_orig_trx_number,
l_tax_ex_cert_num,
p_internal_comment
from ar_lookups l,
ra_cm_requests r
where r.request_id = p_item_key
and r.cm_reason_code = l.lookup_code
and l.lookup_type = 'CREDIT_MEMO_REASON';
/* select employee_id
into p_requestor_id
from fnd_user
where user_id = l_created_by;
select sum(ps.amount_line_items_original), sum(ps.tax_original),
sum(ps.freight_original), sum(ps.amount_due_original),
ps.invoice_currency_code
into p_original_line_amount , p_original_tax_amount,
p_original_freight_amount, p_original_total, p_currency_code
from ar_payment_schedules ps
where ps.customer_trx_id = p_customer_trx_id
group by ps.invoice_currency_code ;
select url
into l_url
from ra_cm_requests
where request_id = p_item_key;
select transaction_url
into l_trans_url
from ra_cm_requests
where request_id = p_item_key;
select activities_url
into l_act_url
from ra_cm_requests
where request_id =p_item_key;
select rct.bill_to_site_use_id,
rct.bill_to_customer_id,
substrb(party.party_name,1,50),
bill_to_cust.account_number,
rct.trx_number
into p_bill_to_site_use_id, p_customer_id,
p_bill_to_customer_name, p_bill_to_customer_number,
p_trx_number
from hz_cust_accounts bill_to_cust,
hz_parties party,
ra_customer_trx rct
where rct.customer_trx_id = p_customer_trx_id
and rct.bill_to_customer_id = bill_to_cust.cust_account_id
and bill_to_cust.party_id = party.party_id ;
select substrb(party.party_name,1,50),
ship_to_cust.account_number
into p_ship_to_customer_name,
p_ship_to_customer_number
from hz_cust_accounts ship_to_cust,
hz_parties party,
ra_customer_trx rct
where rct.customer_trx_id = p_customer_trx_id
and rct.ship_to_customer_id = ship_to_cust.cust_account_id
and ship_to_cust.party_id = party.party_id;
select col.employee_id, cp.collector_id,
col.name
into p_collector_employee_id, p_collector_id,
p_collector_name
from ar_collectors col, hz_customer_profiles cp
where cp.cust_account_id = p_customer_id
and cp.site_use_id = p_bill_to_site_use_id
and cp.collector_id = col.collector_id ;
select col.employee_id,
cp_cust.collector_id,
col.name
into p_collector_employee_id,
p_collector_id,
p_collector_name
from ar_collectors col,
hz_customer_profiles cp_cust
where cp_cust.cust_account_id = p_customer_id
and cp_cust.site_use_id IS NULL
and cp_cust.collector_id = col.collector_id ;
Select user_id
From fnd_user
Where employee_id = l_collector_employee_id;
SelectFirstPrimaryApproverId(l_reason_code,
l_currency_code,
l_approver_id);
SELECT invoicing_rule_id
INTO l_invoicing_rule_id
FROM ra_customer_trx
WHERE customer_trx_id = l_customer_trx_id;
l_debug_mesg := 'Get the user name of selected role';
SELECT user_id INTO l_employee_id
FROM fnd_user
WHERE user_name = l_approver_name;
SELECT primary_flag INTO l_primary_flag
FROM ar_approval_user_limits aul
WHERE reason_code = l_reason_code
AND currency_code = l_currency_code
AND user_id = l_employee_id;
SelectFirstPrimaryApproverId(l_reason_code,
l_currency_code,
l_first_approver_id);
SelectPrimaryApproverId(l_reason_code,
l_currency_code,
l_approver_count,
l_approver_id);
select employee_id, user_id into l_employee_id, l_approver_id
from fnd_user
where user_name = l_approver_user_name;
select employee_id into l_employee_id
from fnd_user
where user_id = l_approver_id;
SELECT hremp.supervisor_id
INTO l_supervisor_emp_id
FROM per_all_assignments_f hremp
WHERE hremp.person_id = l_employee_id
AND primary_flag = 'Y' -- get primary assgt
AND assignment_type = 'E' -- ensure emp assgt, not applicant assgt
AND trunc(sysdate) BETWEEN hremp.effective_start_date AND
hremp.effective_end_date ;
select user_id into l_approver_id
from fnd_user
where employee_id = l_supervisor_emp_id;
select count(*)
from per_all_people_f
where person_id=p_employee_id;
select count(*)
from ar_approval_user_limits aul
where aul.reason_code = l_reason_code
and aul.currency_code = l_currency_code
and aul.primary_flag = p_primary_flag
and user_id = p_employee_id
order by - aul.amount_from;
PROCEDURE SelectFirstPrimaryApproverId(p_reason_code IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_approver_employee_id OUT NOCOPY NUMBER) IS
cursor c1 is
select aul.user_id
from ar_approval_user_limits aul
where aul.reason_code = p_reason_code
and aul.currency_code = p_currency_code
and aul.primary_flag = 'Y'
order by - aul.amount_from;
l_debug_mesg := 'Select first employee_id with lowest dollar value';
Wf_Core.Context('AR_CMREQ_WF', 'SelectFirstPrimaryApproverId',
null, null, null, l_debug_mesg);
END SelectFirstPrimaryApproverId;
PROCEDURE SelectPrimaryApproverId(p_reason_code IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_approver_count IN NUMBER,
p_approver_employee_id OUT NOCOPY NUMBER) IS
cursor c1 is
select aul.user_id
from ar_approval_user_limits aul
where aul.reason_code = p_reason_code
and aul.currency_code = p_currency_code
and aul.primary_flag = 'Y'
order by - aul.amount_from;
l_debug_mesg := 'Select employee_id with dollar value larger than previous one';
Wf_Core.Context('AR_CMREQ_WF', 'SelectPrimaryApproverId',
null, null, null, l_debug_mesg);
END SelectPrimaryApproverId;
SELECT employee_id
INTO l_employee_id
FROM fnd_user
WHERE user_id = p_user_id;
select wu.name, wu.display_name
into p_user_name, p_display_name
from wf_users wu, fnd_user fu
where wu.orig_system = 'PER'
and wu.orig_system_id = l_employee_id
and wu.orig_system_id = fu.employee_id
and fu.user_id = p_user_id
and fu.user_name = wu.name;
SELECT employee_id
FROM fnd_user
WHERE user_id = l_approver_id;
Select user_id
From fnd_user
Where employee_id = l_manager_id ;
SELECT hremp.supervisor_id
INTO l_manager_id
FROM per_all_assignments_f hremp
WHERE hremp.person_id = l_employee_id
AND primary_flag = 'Y' -- get primary assgt
AND assignment_type = 'E' -- ensure emp assgt, not applicant assgt
AND trunc(sysdate) BETWEEN hremp.effective_start_date AND
hremp.effective_end_date;
Select user_id
From fnd_user
Where employee_id = l_collector_employee_id;
l_debug_mesg := 'Check if the selected approver is a final one';
select aul.amount_to, aul.amount_from
into l_amount_to, l_amount_from
from ar_approval_user_limits aul
where aul.user_id = p_approver_id
and aul.reason_code = p_reason_code
and aul.currency_code = p_currency_code ;
SELECT payment_schedule_id, due_date, amount_in_dispute, dispute_date
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id;
SELECT terms_sequence_number,payment_schedule_id , due_date , dispute_date , amount_in_dispute , amount_due_remaining
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id
AND NVL(ps.amount_in_dispute,0) <> 0 /*12998583-M1*/
ORDER BY terms_sequence_number desc;
SELECT total_amount * -1
into remove_from_dispute_amt
from ra_cm_requests
WHERE request_id = l_request_id;
arp_process_cutil.update_ps
(p_ps_id=> ps_rec.payment_schedule_id,
p_due_date=> ps_rec.due_date,
--p_amount_in_dispute=> new_dispute_amt,
p_amount_in_dispute=> ps_rec.amount_in_dispute, /*12998583*/
p_dispute_date=> new_dispute_date,
p_update_dff => 'N',
p_attribute_category=>NULL,
p_attribute1=>NULL,
p_attribute2=>NULL,
p_attribute3=>NULL,
p_attribute4=>NULL,
p_attribute5=>NULL,
p_attribute6=>NULL,
p_attribute7=>NULL,
p_attribute8=>NULL,
p_attribute9=>NULL,
p_attribute10=>NULL,
p_attribute11=>NULL,
p_attribute12=>NULL,
p_attribute13=>NULL,
p_attribute14=>NULL,
p_attribute15=>NULL );
arp_process_cutil.update_ps
(p_ps_id=> ps_rec.payment_schedule_id,
p_due_date=> ps_rec.due_date,
p_amount_in_dispute=> NULL ,
p_dispute_date=> NULL,
p_update_dff => 'N',
p_attribute_category=>NULL,
p_attribute1=>NULL,
p_attribute2=>NULL,
p_attribute3=>NULL,
p_attribute4=>NULL,
p_attribute5=>NULL,
p_attribute6=>NULL,
p_attribute7=>NULL,
p_attribute8=>NULL,
p_attribute9=>NULL,
p_attribute10=>NULL,
p_attribute11=>NULL,
p_attribute12=>NULL,
p_attribute13=>NULL,
p_attribute14=>NULL,
p_attribute15=>NULL );
SELECT display_name,orig_system_id INTO l_role_display_name, l_role_id
FROM wf_roles
WHERE name = l_receivable_role;
SELECT orig_system_id, display_name
INTO l_approver_id, l_approver_display_name
FROM wf_users
WHERE orig_system = 'PER'
AND name = l_approver_user_name;
PROCEDURE InsertSubmissionNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
/* Bug 7367350 inserting internal notes */
l_internal_comment VARCHAR2(1760) DEFAULT NULL;
l_debug_mesg := 'Insert WF submission notes';
select meaning into l_reason_meaning
from ar_lookups
where lookup_type = 'CREDIT_MEMO_REASON'
and lookup_code = l_reason_code;
select meaning into l_comment_type
from ar_lookups
where LOOKUP_TYPE='AR_COMMENT_CLASSIFICATION'
AND LOOKUP_CODE='C';
select meaning into l_comment_type
from ar_lookups
where LOOKUP_TYPE='AR_COMMENT_CLASSIFICATION'
AND LOOKUP_CODE='I';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text1,
l_note_id);
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertSubmissionNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertSubmissionNotes;
PROCEDURE InsertApprovalReminderNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Insert Request Approval Reminder notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertApprovalReminderNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertApprovalReminderNotes;
PROCEDURE InsertEscalationNotes (p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Insert Escalation notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertEscalationNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertEscalationNotes;
PROCEDURE InsertRequestManualNotes (p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Insert Request Manual Entry notes';
SELECT display_name INTO l_role_display_name
FROM wf_roles
WHERE name = l_receivable_role;
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertRequestManualNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertRequestManualNotes;
PROCEDURE InsertCompletedManualNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_last_updated_by number;
l_last_update_login number;
l_last_updated_by := ARP_GLOBAL.user_id;
l_last_update_login := ARP_GLOBAL.last_update_login ;
l_debug_mesg := 'Insert Completed Manual Entry notes';
SELECT display_name INTO l_role_display_name
FROM wf_roles
WHERE name = l_receivable_role;
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
/* Bug 1908252 : update last_update* fields */
update ra_cm_requests
set status = 'COMPLETE',
approval_date = SYSDATE,
last_updated_by = l_last_updated_by,
last_update_date = SYSDATE,
last_update_login = l_last_update_login
where request_id = p_item_key;
wf_core.context('ARP_CMREQ_WF', 'InsertCompletedManualNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertCompletedManualNotes;
PROCEDURE InsertRequestApprovalNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Insert Request Approval notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertRequestApprovalNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertRequestApprovalNotes;
PROCEDURE InsertApprovedResponseNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Insert Approved Response notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertApprovedResponseNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertApprovedResponseNotes;
PROCEDURE InsertRejectedResponseNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_last_updated_by number;
l_last_update_login number;
l_last_updated_by := ARP_GLOBAL.user_id;
l_last_update_login := ARP_GLOBAL.last_update_login ;
l_debug_mesg := 'Insert Rejected Response notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
/* Bug 1908252 : update last_update* fields */
UPDATE ra_cm_requests
SET status = 'NOT_APPROVED',
last_updated_by = l_last_updated_by,
last_update_date = SYSDATE,
last_update_login = l_last_update_login
WHERE request_id = p_item_key;
wf_core.context('ARP_CMREQ_WF', 'InsertRejectedResponseNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertRejectedResponseNotes;
PROCEDURE InsertSuccessfulAPINotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Insert Completed Successful API notes';
/* Get trx number for CM and the insert into note text */
fnd_message.set_name('AR', 'AR_WF_COMPLETED_SUCCESSFUL');
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertSuccessfulAPINotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertSuccessfulAPINotes;
PROCEDURE InsertNotes(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result OUT NOCOPY VARCHAR2) IS
l_debug_mesg varchar2(240);
l_debug_mesg := 'Create a call record and insert a note';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
'Credit Memo request was approved by receivable role.',
l_note_id);
wf_core.context('ARP_CMREQ_WF', 'InsertNotes',
p_item_type, p_item_key, to_char(p_actid), p_funcmode);
end InsertNotes;
PROCEDURE InsertTrxNotes(x_customer_call_id IN NUMBER,
x_customer_call_topic_id IN NUMBER,
x_action_id IN NUMBER,
x_customer_trx_id IN NUMBER,
x_note_type IN VARCHAR2,
x_text IN VARCHAR2,
x_note_id OUT NOCOPY NUMBER) IS
l_debug_mesg varchar2(240);
l_last_updated_by number;
l_last_update_date date;
l_last_update_login number;
l_debug_mesg := 'Insert call topic notes';
l_last_update_login := ARP_GLOBAL.last_update_login ;
l_last_update_date := sysdate;
l_last_updated_by := ARP_GLOBAL.USER_ID;
arp_notes_pkg.insert_cover(
p_note_type => x_note_type,
p_text => x_text,
p_customer_call_id => null,
p_customer_call_topic_id => null,
p_call_action_id => NULL,
p_customer_trx_id => x_customer_trx_id,
p_note_id => x_note_id,
p_last_updated_by => l_last_updated_by,
p_last_update_date => l_last_update_date,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date);
wf_core.Context('ARP_CMREQ_WF', 'InsertTrxNotes',
null, null, null, l_debug_mesg);
END InsertTrxNotes;
l_last_updated_by number;
l_last_update_login number;
SELECT ra.receivable_application_id,
ra.customer_trx_id,
ct.trx_number
from ar_receivable_applications ra, ra_customer_trx ct
where ra.posting_control_id=-3
and ra.application_type = 'CM'
and ra.customer_trx_id=p_customer_trx_id
and ra.event_id is null
and ra.customer_trx_id = ct.customer_trx_id;
l_last_updated_by := ARP_GLOBAL.user_id;
l_last_update_login := ARP_GLOBAL.last_update_login ;
select cm_customer_trx_id
into l_credit_memo_id
from ra_cm_requests
where request_id = l_request_id;
/* Bug 1908252 : update last_update* fields */
update ra_cm_requests
set status='COMPLETE',
approval_date = SYSDATE,
last_updated_by = l_last_updated_by,
last_update_date = SYSDATE,
last_update_login = l_last_update_login
where request_id = p_item_key;
select trx_number
into l_credit_memo_number
from ra_customer_trx
where customer_trx_id = l_credit_memo_id;
/* Bug 1908252 : update last_update* fields */
update ra_cm_requests
set status='APPROVED_PEND_COMP',
approval_date = SYSDATE,
last_updated_by = l_last_updated_by,
last_update_date = SYSDATE,
last_update_login = l_last_update_login
where request_id = p_item_key;
SELECT COUNT(*) INTO l_count
FROM ra_terms_lines
WHERE term_id = (SELECT term_id FROM ra_customer_trx
WHERE customer_trx_id = l_customer_trx_id);
SELECT invoicing_rule_id INTO l_invoicing_rule_id
FROM ra_customer_trx
WHERE customer_trx_id = l_customer_trx_id;
select org_id into l_org_id
from ra_cm_requests_all
where request_id = p_item_key;