[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;
SELECT org_id
FROM ra_cm_requests_all
WHERE request_id = p_item_key;
SELECT 1
FROM wf_roles
WHERE orig_system = 'PER'
AND orig_system_id = p_employee_id
AND status = 'ACTIVE'
AND (expiration_date IS NULL OR
sysdate < expiration_date)
AND rownum < 2;
SELECT supervisor_id
FROM per_all_assignments_f
WHERE person_id = l_employee_id
AND per_all_assignments_f.primary_flag = 'Y'
AND per_all_assignments_f.assignment_type in ('E','C')
AND per_all_assignments_f.assignment_status_type_id
NOT IN
(SELECT assignment_status_type_id
FROM per_assignment_status_types
WHERE per_system_status = 'TERM_ASSIGN');
SELECT employee_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT user_id
FROM fnd_user
WHERE employee_id = p_employee_id
AND NVL(end_date,sysdate) >= sysdate ;
SELECT user_id
FROM fnd_user
WHERE user_name = p_user_name;
InsertTrxNotes (
x_customer_call_id => NULL,
x_customer_call_topic_id => NULL,
x_action_id => NULL,
x_customer_trx_id => l_customer_trx_id,
x_note_type => 'MAINTAIN',
x_text => l_note_text,
x_note_id => l_note_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;
g_debug_mesg := 'call AME updateApprovalStatus - ' ||
'l_approver_user_id: ' || l_approver_user_id ||
' l_approver_employee_id: ' || l_approver_employee_id;
ame_api.updateApprovalStatus2(
applicationIdIn => c_application_id,
transactionIdIn => p_item_key,
approvalStatusIn => p_response,
approverPersonIdIn => l_approver_employee_id,
approverUserIdIn => l_approver_user_id,
transactionTypeIn => l_transaction_type);
g_debug_mesg := 'Returned successfully from updateApprovalStatus!';
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
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 sum(ps.amount_line_items_original),
sum(ps.tax_original),
sum(ps.freight_original),
sum(ps.amount_due_original),
ps.invoice_currency_code
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id
GROUP BY ps.invoice_currency_code ;
SELECT url, transaction_url, activities_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
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
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 collector_id, name
FROM ar_collectors
WHERE employee_id = p_employee_id;
SELECT invoicing_rule_id
FROM ra_customer_trx
WHERE customer_trx_id = p_cust_trx_id;
SELECT employee_id, user_id
FROM fnd_user
WHERE user_name = p_user_name;
SELECT supervisor_id
FROM per_all_assignments_f
WHERE person_id = p_employee_id
AND primary_flag = 'Y' -- get primary assgt
AND assignment_type = 'E' -- ensure emp assgt, not applicant assgt
AND trunc(sysdate) BETWEEN effective_start_date
AND effective_end_date;
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(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 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;
/*7367350 Changed procedure to insert note in Invoice maintenance notes.
A new note is inserted in for internal comment */
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTSUBMISSIONNOTES';
arp_standard.debug('InsertSubmissionNotes: ' || g_debug_mesg);
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';
/*bug 7367350 Changes to insert internla commen notes in invoice maintenance */
IF l_internal_comment is NOT NULL then
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);
proc_name => 'INSERTSUBMISSIONNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTAPPROVALREMINDERNOTES';
arp_standard.debug('InsertApprovalReminderNotes: ' || g_debug_mesg);
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
proc_name => 'INSERTAPPROVALREMINDERNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTESCAlATIONNOTES';
arp_standard.debug('InsertEscalationNotes: ' || g_debug_mesg);
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
proc_name => 'INSERTESCALATIONNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTREQUESTMANUALNOTES';
arp_standard.debug('InsertRequestManualNotes: ' || g_debug_mesg);
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);
proc_name => 'INSERTREQUESTMANUALNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
g_debug_mesg := 'Entered INSERTCOMPLETEDMANUALNOTES';
arp_standard.debug('InsertCompletedManualNotes: ' || g_debug_mesg);
l_last_updated_by := ARP_GLOBAL.user_id;
l_last_update_login := ARP_GLOBAL.last_update_login ;
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);
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;
proc_name => 'INSERTCOMPLETEDMANUALNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTREQUESTAPPROVALNOTES';
arp_standard.debug('InsertRequestApprovalNotes: ' || g_debug_mesg);
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
proc_name => 'INSERTREQUESTAPPROVALNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTAPPROVEDRESPONSENOTES';
arp_standard.debug('InsertApprovedResponseNotes: ' || g_debug_mesg);
g_debug_mesg := 'Insert Approved Response notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
g_debug_mesg := 'InsertApprovedResponseNotes - return from RecordResponse';
arp_standard.debug('InsertApprovedResponseNotes: ' || g_debug_mesg);
proc_name => 'INSERTAPPROVEDRESPONSENOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
g_debug_mesg := 'Entered INSERTREJECTEDRESPONSENOTES';
arp_standard.debug('InsertRejectedResponseNotes: ' || g_debug_mesg);
l_last_updated_by := ARP_GLOBAL.user_id;
l_last_update_login := ARP_GLOBAL.last_update_login ;
g_debug_mesg := 'Insert Rejected Response notes';
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
l_note_text,
l_note_id);
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;
g_debug_mesg := 'InsertRejectedResponseNotes -return from updt Approval';
arp_standard.debug('InsertRejectedResponseNotes: ' || g_debug_mesg);
proc_name => 'INSERTREJECTEDRESPONSENOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_document_id NUMBER;
g_debug_mesg := 'Entered INSERTSUCCESFULAPINOTES';
arp_standard.debug('InsertSuccessfulAPINotes: ' || g_debug_mesg);
/* 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);
proc_name => 'INSERTSUCCESSFULAPINOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_customer_id NUMBER;
g_debug_mesg := 'Entered INSERTNOTES';
arp_standard.debug('InsertNotes: ' || g_debug_mesg);
InsertTrxNotes(NULL,
NULL,
NULL,
l_customer_trx_id,
'MAINTAIN',
'Credit Memo request was approved by receivable role.',
l_note_id);
proc_name => 'INSERTNOTES',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => p_funcmode,
arg4 => to_char(p_actid),
arg5 => g_debug_mesg);
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_last_updated_by NUMBER;
l_last_update_date date;
l_last_update_login NUMBER;
g_debug_mesg := 'Entered INSERTTRXNOTES';
arp_standard.debug('InsertTrxNotes: ' || g_debug_mesg);
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);
g_debug_mesg := 'INSERTTRXNOTES - notes inserted';
arp_standard.debug('InsertTrxNotes: ' || g_debug_mesg);
proc_name => 'INSERTTRXNOTES',
arg1 => c_item_type,
arg2 => NULL,
arg3 => NULL,
arg4 => NULL,
arg5 => g_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;
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;
g_debug_mesg := 'last Updated By: '
|| l_last_updated_by || ' '
|| l_last_update_login;
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;
g_debug_mesg := 'After Update';
SELECT COUNT(*)
FROM ra_terms_lines
WHERE term_id =
(SELECT term_id
FROM ra_customer_trx
WHERE customer_trx_id = p_cust_trx_id);
SELECT invoicing_rule_id
FROM ra_customer_trx
WHERE customer_trx_id = p_cust_trx_id;
SELECT rsa.person_id
FROM ra_customer_trx_all rcta, ra_salesreps_all rsa
WHERE rcta.primary_salesrep_id = rsa.salesrep_id
AND rcta.customer_trx_id = p_id;
SELECT customer_trx_id
FROM ra_customer_trx
WHERE customer_trx_id = p_credit_memo_id
AND previous_customer_trx_id IS NULL;