[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
,ica.case_number
FROM iex_delinquencies_all ida
,iex_case_objects ico
,iex_cases_all_b ica
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id
AND ico.cas_id = ica.cas_id;
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
, okh.contract_number
FROM iex_delinquencies_all ida
,iex_case_objects ico
,okc_k_headers_b okh
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id
AND ico.object_id = okh.id;
SELECT prog.id program_id
,prog.contract_number program_number
,lease.id contract_id
,rgp.dnz_chr_id
,lease.contract_number contract_number
,rgp.rgd_code
FROM okc_k_headers_b prog,
okc_k_headers_b lease,
okl_k_headers khr,
okc_rule_groups_b rgp
WHERE khr.id = lease.id
AND khr.khr_id = prog.id
AND prog.scs_code = 'PROGRAM'
AND lease.scs_code in ('LEASE','LOAN')
AND rgp.rgd_code = 'COAGRM'
AND rgp.dnz_chr_id = prog.id
AND lease.contract_number = cp_contract_number;
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
, okh.contract_number
FROM iex_delinquencies_all ida
,iex_case_objects ico
,okc_k_headers_b okh
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id
AND ico.object_id = okh.id;
SELECT prog.id program_id
,prog.contract_number program_number
,lease.id contract_id
,rgp.dnz_chr_id
,lease.contract_number contract_number
,rgp.rgd_code
FROM okc_k_headers_b prog,
okc_k_headers_b lease,
okl_k_headers khr,
okc_rule_groups_b rgp
WHERE khr.id = lease.id
AND khr.khr_id = prog.id
AND prog.scs_code = 'PROGRAM'
AND lease.scs_code in ('LEASE','LOAN')
AND rgp.rgd_code = 'COAGRM'
AND rgp.dnz_chr_id = prog.id
AND lease.contract_number = cp_contract_number;
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
, okh.contract_number
FROM iex_delinquencies_all ida
,iex_case_objects ico
,okc_k_headers_b okh
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id
AND ico.object_id = okh.id;
SELECT prog.id program_id
,prog.contract_number program_number
,lease.id contract_id
,rgp.dnz_chr_id
,lease.contract_number contract_number
,rgp.rgd_code
FROM okc_k_headers_b prog,
okc_k_headers_b lease,
okl_k_headers khr,
okc_rule_groups_b rgp
WHERE khr.id = lease.id
AND khr.khr_id = prog.id
AND prog.scs_code = 'PROGRAM'
AND lease.scs_code in ('LEASE','LOAN')
AND rgp.rgd_code = 'COAGRM'
AND rgp.dnz_chr_id = prog.id
AND lease.contract_number = cp_contract_number;
CURSOR l_vendor_csr(cp_case_number IN VARCHAR2) IS SELECT pv.vendor_id
,pv.vendor_name
--,pvs.email_address
FROM iex_cases_all_b ica
,iex_case_objects ico
,okc_k_party_roles_v opr
,po_vendors pv
--,po_vendor_sites_all pvs
WHERE ica.case_number = cp_case_number
AND ica.cas_id = ico.cas_id
AND ico.object_id =opr.dnz_chr_id
AND opr.rle_code = 'OKL_VENDOR'
AND opr.object1_id1 = pv.vendor_id;
CURSOR l_khr_csr(cp_case_number IN VARCHAR2) IS SELECT ico.object_id
, okh.contract_number
FROM iex_cases_all_b ica
,iex_case_objects ico
,okc_k_headers_b okh
WHERE ica.case_number = cp_case_number
AND ica.cas_id = ico.cas_id
AND ico.object_id = okh.id;
SELECT prog.id program_id
,prog.contract_number program_number
,lease.id contract_id
,rgp.dnz_chr_id
,lease.contract_number contract_number
,rgp.rgd_code
FROM okc_k_headers_b prog,
okc_k_headers_b lease,
okl_k_headers khr,
okc_rule_groups_b rgp
WHERE khr.id = lease.id
AND khr.khr_id = prog.id
AND prog.scs_code = 'PROGRAM'
AND lease.scs_code in ('LEASE','LOAN')
AND rgp.rgd_code = 'COAGRM'
AND rgp.dnz_chr_id = prog.id
AND lease.contract_number = cp_contract_number;
SELECT pvs.email_address
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_code = cp_vendor_site_code;
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
FROM iex_delinquencies_all ida
,iex_case_objects ico
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id;
iex_open_interface_pub.insert_pending(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_object1_id1 => cur.object_id,
p_object1_id2 => '#',
p_jtot_object1_code => 'OKX_LEASE',
p_action => IEX_OPI_PVT.ACTION_NOTIFY_CUST,
p_status => IEX_OPI_PVT.STATUS_PENDING_AUTO,
p_comments => OKC_API.G_MISS_CHAR,
p_ext_agncy_id => NULL,
p_review_date => NULL,
p_recall_date => NULL,
p_automatic_recall_flag => NULL,
p_review_before_recall_flag => NULL,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data);
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
FROM iex_delinquencies_all ida
,iex_case_objects ico
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id;
iex_open_interface_pub.insert_pending(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_object1_id1 => cur.object_id,
p_object1_id2 => '#',
p_jtot_object1_code => 'OKX_LEASE',
p_action => IEX_OPI_PVT.ACTION_REPORT_CB,
p_status => IEX_OPI_PVT.STATUS_PENDING_AUTO,
p_comments => OKC_API.G_MISS_CHAR,
p_ext_agncy_id => NULL,
p_review_date => NULL,
p_recall_date => NULL,
p_automatic_recall_flag => NULL,
p_review_before_recall_flag => NULL,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data);
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
FROM iex_delinquencies_all ida
,iex_case_objects ico
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id;
iex_open_interface_pub.insert_pending(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_object1_id1 => cur.object_id,
p_object1_id2 => '#',
p_jtot_object1_code => 'OKX_LEASE',
p_action => IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY,
p_status => IEX_OPI_PVT.STATUS_PENDING_AUTO,
p_comments => OKC_API.G_MISS_CHAR,
p_ext_agncy_id => NULL,
p_review_date => NULL,
p_recall_date => NULL,
p_automatic_recall_flag => NULL,
p_review_before_recall_flag => NULL,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data);
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT OIN.ID,
OIN.KHR_ID,
OIN.CAS_ID,
IOH.ID HST_ID,
IOH.OBJECT1_ID1,
IOH.OBJECT1_ID2,
IOH.JTOT_OBJECT1_CODE,
IOH.ACTION,
IOH.STATUS,
IOH.REQUEST_DATE,
IOH.PROCESS_DATE,
IOH.EXT_AGNCY_ID
FROM iex_delinquencies_all ida
,iex_case_objects ico
,Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id
AND ico.object_id = oin.khr_id
AND OIN.khr_id = TO_NUMBER(IOH.object1_id1)
AND IOH.jtot_object1_code = 'OKX_LEASE'
AND (IOH.ACTION = IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY)
AND (IOH.STATUS = IEX_OPI_PVT.STATUS_PROCESSED);
CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT OIN.ID,
OIN.KHR_ID,
IOH.ID hst_id,
IOH.OBJECT1_ID1,
IOH.OBJECT1_ID2,
IOH.JTOT_OBJECT1_CODE,
IOH.EXT_AGNCY_ID
FROM iex_delinquencies_all ida
,iex_case_objects ico
,Okl_Open_Int OIN
,Iex_Open_Int_Hst IOH
WHERE ida.delinquency_id = cp_delinquency_id
AND ida.case_id = ico.cas_id
AND ico.object_id = oin.khr_id
AND OIN.khr_id = TO_NUMBER(IOH.object1_id1)
AND IOH.jtot_object1_code = 'OKX_LEASE'
AND (IOH.ACTION = IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY)
AND (IOH.STATUS = IEX_OPI_PVT.STATUS_NOTIFIED OR IOH.STATUS = IEX_OPI_PVT.STATUS_PROCESSED);
* flow is over and also updates the work item
* the send signal is sent when the agent REJECTS the
* notification since the vendor didn't approve it ,
* so set the status to 'CANCELLED'.
**/
PROCEDURE wf_send_signal_cancelled(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2) AS
l_work_item_id number;
iex_stry_utl_pub.update_work_item(
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_work_item_id => l_work_item_id,
p_status => 'CANCELLED',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
end if; -- if update is succcessful;
* flow is over and also updates the work item
* the send signal is sent when the agent REJECTS the
* notification since the vendor didn't approve it ,
* so set the status to 'COMPLETE'.
**/
PROCEDURE wf_send_signal_complete(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2) AS
l_work_item_id number;
iex_stry_utl_pub.update_work_item(
p_api_version => 1.0,
p_commit => FND_API.G_TRUE,
p_init_msg_list => FND_API.G_TRUE,
p_work_item_id => l_work_item_id,
p_status => 'COMPLETE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
end if; -- if update is succcessful;
CURSOR l_party_csr(cp_case_number in varchar2) IS SELECT hp.party_name
FROM IEX_CASES_ALL_B ica
,HZ_PARTIES hp
WHERE ica.case_number = cp_case_number
AND ica.party_id = hp.party_id;
CURSOR l_contract_csr(cp_case_number in varchar2) IS SELECT okh.contract_number
FROM IEX_CASES_ALL_B ica
,IEX_CASE_OBJECTS ico
,OKC_K_HEADERS_V okh
WHERE ica.case_number = cp_case_number
AND ica.cas_id = ico.cas_id
AND ico.object_id = okh.id;
CURSOR l_contract_csr(cp_case_number in varchar2) IS SELECT okh.id
FROM IEX_CASES_ALL_B ica
,IEX_CASE_OBJECTS ico
,OKC_K_HEADERS_V okh
WHERE ica.case_number = cp_case_number
AND ica.cas_id = ico.cas_id
AND ico.object_id = okh.id;
SELECT sum(nvl(aps.amount_due_remaining, 0)) past_due_amount
FROM iex_cases_all_b ica
,iex_case_objects ico
,okl_cnsld_ar_strms_b ocas
,ar_payment_schedules_all aps
WHERE ica.case_number = cp_case_number
AND ica.cas_id = ico.cas_id
AND ico.object_id = ocas.khr_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class = 'INV'
AND aps.due_date < sysdate
AND nvl(aps.amount_due_remaining, 0) > 0;
CURSOR l_vendor_csr(cp_case_number in varchar2) IS SELECT pv.vendor_name
FROM iex_cases_all_b ica
,iex_case_objects ico
,okc_k_party_roles_v opr
,po_vendors pv
,po_vendor_sites_all pvs
WHERE ica.case_number = cp_case_number
AND ica.cas_id = ico.cas_id
AND ico.object_id =opr.dnz_chr_id
AND opr.rle_code = 'OKL_VENDOR'
AND opr.object1_id1 = pv.vendor_id
AND pv.vendor_id = pvs.vendor_id;
SELECT okl_wf_item_s.nextval
FROM dual;