[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| PUBLIC PROCEDURE SELECT_WF_PROCESS
|
| DESCRIPTION
| This process selects the process to run.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| P_PARAM1 IN Standard in parameter
| X_PARAM2 OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 17-Jan-2005 GBELLARY Created
|
*=======================================================================*/
/*=======================================================================+
| Package Global Constants
+=======================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_WORKFLOW_PUB';
select NOTIFICATION_ID, responder
from wf_notifications
where MESSAGE_TYPE = p_itemtype and
ITEM_KEY = p_itemkey;
select USER_ID
from fnd_user
where USER_NAME = p_user_name;
select usr.user_name,
resp.responsibility_id,
resp.responsibility_key,
resp.request_group_id,
rgr.request_group_name
from fnd_user usr,
FND_USER_RESP_GROUPS urg,
fnd_responsibility resp,
FND_REQUEST_GROUPS rgr
where
usr.user_id = p_user_id and
usr.user_id = urg.user_id and
urg.responsibility_application_id = 206 and
urg.responsibility_id = nvl(p_resp_id, urg.responsibility_id) and
urg.responsibility_id = resp.responsibility_id and
resp.application_id = urg.responsibility_application_id and
rgr.request_group_id = resp.request_group_id and
rgr.application_id = urg.responsibility_application_id and
rgr.request_group_id in
(select rgu.request_group_id
from FND_CONCURRENT_PROGRAMS cp,
FND_REQUEST_GROUP_UNITS rgu
where
rgu.request_unit_id = cp.concurrent_program_id and
rgu.application_id = urg.responsibility_application_id and
cp.application_id = urg.responsibility_application_id and
cp.concurrent_program_name in ('LNS_BILLING','LNS_GL_TRANSFER'));
SELECT open_to_term_flag
,open_to_term_event
FROM lns_loan_headers_all
WHERE loan_id = p_loan_id;
SELECT wf_business_event
,lh.loan_number
,lh.requested_amount
,lh.loan_description
,lh.loan_class_code
,lh.loan_type_id
,lt.loan_type_name
,to_char(lh.requested_amount,
FND_CURRENCY.SAFE_GET_FORMAT_MASK(lh.LOAN_CURRENCY,50))
|| ' ' || lh.loan_currency loan_formatted_amount
,to_char(nvl(lh.requested_amount,0)-nvl(lh.funded_amount,0),
FND_CURRENCY.SAFE_GET_FORMAT_MASK(lh.LOAN_CURRENCY,50))
|| ' ' || lh.loan_currency loan_undisbursed_amount
,decode(lh.loan_class_code,'ERS','LNS_ERS_CONTEXT_HOMEPAGE_MENU',
'LNS_LOAN_CONTEXT_HOMEPAGE_MENU') function_name
,lh.last_updated_by current_user_id
FROM lns_events le, lns_loan_headers_all_vl lh, lns_loan_types_vl lt
WHERE lh.loan_class_code = le.loan_class_code
AND le.enabled_flag = 'Y'
AND le.event_name = p_event_name
AND lt.loan_type_id = lh.loan_type_id
AND lh.loan_id = p_loan_id;
SELECT notes.notes
FROM jtf_notes_tl notes
WHERE notes.jtf_note_id = (SELECT max(note.jtf_note_id)
FROM jtf_notes_b note
WHERE note.source_object_code ='LNS_LOAN'
AND note.source_object_id = p_loan_id
)
AND notes.language = USERENV('LANG');
SELECT lns_workflow_itemkey_s.nextval
INTO ItemKey
FROM dual;
LNS_EVT_ACTION_HISTORY_H_PKG.Insert_Row (
X_EVENT_ACTION_HISTORY_ID => l_ev_action_hist_id
,P_EVENT_ACTION_ID => l_event_action_id
,P_LOAN_ID => l_loan_id
,P_WF_ITEMKEY => itemkey
,P_WF_ITEMTYPE => itemtype
,P_ACTIVITY_DATE => sysdate);
SELECT usr_resp.responsibility_id
FROM fnd_user_resp_groups usr_resp
WHERE usr_resp.responsibility_application_id = p_appl_id
AND trunc(sysdate) between trunc(nvl(usr_resp.start_date, sysdate)) and trunc(nvl(usr_resp.end_date, sysdate))
-- AND usr_resp.start_date < sysdate AND nvl(usr_resp.end_date,sysdate-1) > sysdate -- Bug#8247186
AND usr_resp.user_id = p_user_id;
SELECT primary_recipient_type
, primary_recipient_name
, priority_num
, nvl(active_for_num,0)*24*60 -- This has to be converted into minutes
, delivery_method
, event_action_id
FROM lns_events le, lns_event_actions lea
WHERE le.event_name = p_event_name
AND le.enabled_flag = 'Y'
AND le.loan_class_code = p_loan_class_code
AND lea.event_id = le.event_id
AND lea.EVENT_ACTION_NAME = 'NOTIFICATION'
AND lea.enabled_flag = 'Y'
AND lea.loan_type_id = p_loan_type_id;
SELECT fndu.user_name
FROM fnd_user fndu
WHERE fndu.user_id = p_current_user_id;
SELECT fndu.user_name
,fndu.user_id
FROM jtf_rs_role_relations rel
,jtf_rs_roles_b rol
,jtf_rs_resource_extns res
,fnd_user fndu
WHERE rel.role_id = rol.role_id
AND rel.delete_flag <> 'Y'
AND SYSDATE BETWEEN NVL(rel.start_date_active,sysdate)
AND NVL(rel.end_date_active,sysdate)
AND rol.role_type_code = 'LOANS'
AND rol.role_code = l_primary_recipient_name
AND rol.active_flag = 'Y'
AND rel.role_resource_id = res.resource_id
AND res.category = 'EMPLOYEE'
AND res.start_date_active <= SYSDATE
AND (res.end_date_active is null or res.end_date_active >= SYSDATE)
AND fndu.user_id = res.user_id;
SELECT hp.party_name borrower_name
,fnd.user_name
,res.source_name
,llklc.meaning loan_class
,llklt.loan_type_name loan_type
,llkst.meaning loan_subtype
,to_char(nvl(llh.collateral_percent,0)) || '%' collateral_percent
,llh.loan_start_date
,llh.loan_term || ' ' || llktt.meaning term
,llh.loan_maturity_date
,LNS_FINANCIALS.getActiveRate(llh.LOAN_ID) interest_rate
,llkp.meaning loan_purpose
,llh.org_id
,lprod.loan_product_name
FROM lns_loan_headers_all_vl llh, hz_parties hp,
jtf_rs_resource_extns res, fnd_user fnd,
lns_payments_summary_v ps,
lns_lookups llktt,
lns_lookups llklc,
lns_loan_types_vl llklt,
lns_lookups llkp,
lns_lookups llkst,
lns_loan_products_all_vl lprod
WHERE llh.primary_borrower_id = hp.party_id
AND llh.loan_assigned_to = res.resource_id
AND res.category = 'EMPLOYEE'
AND fnd.user_id = res.user_id
AND llktt.lookup_code = llh.loan_term_period
AND llktt.lookup_type = 'PERIOD'
AND llklc.lookup_code = llh.loan_class_code
AND llklc.lookup_type = 'LOAN_CLASS'
AND llklt.loan_type_id = llh.loan_type_id
AND llkp.lookup_code (+) = llh.loan_purpose_code
AND llkp.lookup_type (+) = 'LOAN_PURPOSE'
AND llkst.lookup_code (+) = llh.loan_subtype
AND llkst.lookup_type (+) = 'LOAN_SUBTYPE'
AND llh.loan_id = p_loan_id
AND llh.product_id=lprod.loan_product_id;
SELECT
(CASE WHEN llh.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR llh.FUNDED_AMOUNT = 0 THEN llh.requested_amount
ELSE ps.total_principal_balance END) loan_amount
,to_char((CASE WHEN llh.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR llh.FUNDED_AMOUNT = 0 THEN llh.requested_amount
ELSE ps.total_principal_balance END),
FND_CURRENCY.SAFE_GET_FORMAT_MASK(llh.LOAN_CURRENCY,50))
|| ' ' || llh.loan_currency loan_formatted_amount
FROM lns_loan_headers_all llh
,lns_pay_sum_v ps
WHERE llh.loan_id = p_loan_id
AND ps.loan_id = llh.loan_id;
SELECT to_char(ps.total_overdue,
FND_CURRENCY.SAFE_GET_FORMAT_MASK(llh.LOAN_CURRENCY,50))
|| ' ' || llh.loan_currency overdue_amount
,ps.number_overdue_bills overdue_num
FROM lns_loan_headers_all llh
,lns_pay_sum_overdue_v ps
WHERE llh.loan_id = p_loan_id
AND ps.loan_id = llh.loan_id;
SELECT notes.notes
FROM jtf_notes_tl notes
WHERE notes.jtf_note_id = (SELECT max(note.jtf_note_id)
FROM jtf_notes_b note
WHERE note.source_object_code ='LNS_LOAN'
AND note.source_object_id = p_loan_id
)
AND notes.language = USERENV('LANG');
SELECT notes.notes
FROM jtf_notes_tl notes
WHERE notes.jtf_note_id = (select jtf_note_id
from jtf_note_contexts
where note_context_type_id = (select max(action_id)
from LNS_APPROVAL_ACTIONS
where loan_id = p_loan_id)
)
AND notes.language = USERENV('LANG');
SELECT fndu.user_name
FROM fnd_user fndu
WHERE fndu.user_id = current_user_id;
SELECT notification_id
FROM WF_NOTIFICATIONS
WHERE item_key like search_criteria
AND message_name = 'MSG_LOAN_TO_BE_APPROVED'
AND status = 'OPEN';
| This procedure insters/updates the Loan Approval Status in LNS_APPROVAL_ACTIONS table.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| PARAMETERS
| itemtype in Item Type
| itemkey in Item Key
| actid in Action Id
| funcmode in Function Mode
| resultout out Result Out
|
| KNOWN ISSUES
| None
|
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 23-Aug-2009 avepati Created
| 23-Aug-2009 avepati Bug 8764310 - Loan Notification Missing Approve and Reject Buttons
| 10-Dec-2009 avepati Bug 9092928 - Loan Approval/Rejection Notes is not saved
*=======================================================================*/
PROCEDURE PROCESS_LOAN_APPROVAL(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOAN_APPROVAL';
select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
from lns_loan_headers_all loan
where loan.loan_id = l_loan_id;
select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_loan_approval_action_rec.action_id from dual;
l_loan_approval_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
l_loan_approval_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
l_loan_approval_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
P_CREATED_BY => LNS_UTILITY_PUB.created_by,
P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
X_JTF_NOTE_ID => l_note_id ,
P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
P_NOTE_STATUS => 'I', -- Visibility : public
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
);
| This procedure insters/updates the Loan Rejection Status in LNS_APPROVAL_ACTIONS table.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| PARAMETERS
| itemtype in Item Type
| itemkey in Item Key
| actid in Action Id
| funcmode in Function Mode
| resultout out Result Out
|
| KNOWN ISSUES
| None
|
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 23-Aug-2009 avepati Created
| 23-Aug-2009 avepati bug 8764310 - Loan Notification Missing Approve and Reject Buttons
| 10-Dec-2009 avepati Bug 9092928 - Loan Approval/Rejection Notes is not saved
*=======================================================================*/
PROCEDURE PROCESS_LOAN_REJECTION(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOAN_REJECTION';
select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
from lns_loan_headers_all loan
where loan.loan_id = l_loan_id;
select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_loan_reject_action_rec.action_id from dual;
l_loan_reject_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
l_loan_reject_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
l_loan_reject_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
P_CREATED_BY => LNS_UTILITY_PUB.created_by,
P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
X_JTF_NOTE_ID => l_note_id ,
P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
P_NOTE_STATUS => 'I', -- Visibility : public
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
);
| This procedure updates the Loan Status back to Incomplete in LNS_APPROVAL_ACTIONS table.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| PARAMETERS
| itemtype in Item Type
| itemkey in Item Key
| actid in Action Id
| funcmode in Function Mode
| resultout out Result Out
|
| KNOWN ISSUES
| None
|
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 23-Aug-2009 avepati Created
| 23-Aug-2009 avepati bug 8764310 - Loan Notification Missing Approve and Reject Buttons
| 10-Dec-2009 avepati Bug 9092928 - Loan Approval/Rejection Notes is not saved
*=======================================================================*/
PROCEDURE REQUEST_LOAN_INFORMATION(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'REQUEST_LOAN_INFORMATION';
select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
from lns_loan_headers_all loan
where loan.loan_id = l_loan_id;
select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_req_loan_info_action_rec.action_id from dual;
l_req_loan_info_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
l_req_loan_info_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
l_req_loan_info_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
AME_API2.updateApprovalStatus(applicationIdIn => 206,
transactionTypeIn => l_transaction_type,
transactionIdIn => l_loan_id,
approverIn => approverRecord,
updateItemIn => TRUE);
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
P_CREATED_BY => LNS_UTILITY_PUB.created_by,
P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
X_JTF_NOTE_ID => l_note_id ,
P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
P_NOTE_STATUS => 'I', -- Visibility : public
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
);
insert into lns_event_actions (
EVENT_ACTION_ID
, EVENT_ID
, EVENT_ACTION_NAME
, DESCRIPTION
, LOAN_TYPE_ID
, ACTION_TYPE
, ENABLED_FLAG
, API_NAME
, NOTIFICATION_TYPE
, SETUP_TYPE
, PRIMARY_RECIPIENT_TYPE
, PRIMARY_RECIPIENT_NAME
, SECONDARY_RECIPIENT_TYPE
, SECONDARY_RECIPIENT_NAME
, PRIORITY_NUM
, DAYS_PRIOR_NUM
, ACTIVE_FOR_NUM
, DELIVERY_METHOD
, OBJECT_VERSION_NUMBER
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN )
select LNS_EVENT_ACTIONS_S.nextval --event_action_id
, ea.EVENT_ID
, ea.EVENT_ACTION_NAME
, ea.DESCRIPTION
, missingvalues.LOAN_TYPE_ID
, ea.ACTION_TYPE
, 'Y' --enabled_flag
, ea.API_NAME
, ea.NOTIFICATION_TYPE
, ea.SETUP_TYPE
, ea.PRIMARY_RECIPIENT_TYPE
, ea.PRIMARY_RECIPIENT_NAME
, ea.SECONDARY_RECIPIENT_TYPE
, ea.SECONDARY_RECIPIENT_NAME
, ea.PRIORITY_NUM
, ea.DAYS_PRIOR_NUM
, ea.ACTIVE_FOR_NUM
, ea.DELIVERY_METHOD
, ea.OBJECT_VERSION_NUMBER
, sysdate
, LNS_UTILITY_PUB.CREATED_BY
, sysdate
, LNS_UTILITY_PUB.LAST_UPDATED_BY
, LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
from lns_event_actions ea, lns_events ev,
(select loan_class_code, loan_type_id
from lns_loan_types_vl
minus
select ev.loan_class_code loan_class_code, ea.loan_type_id loan_type_id
from lns_events ev, lns_event_actions ea
where ea.event_id = ev.event_id
and ea.event_action_name = 'NOTIFICATION') missingvalues
where ev.loan_class_code = missingvalues.loan_class_code
and ea.event_id = ev.event_id
and ea.loan_type = decode(ev.loan_class_code,'ERS','ERS','BUSINESS')
and ea.event_action_name = 'NOTIFICATION';
| PUBLIC PROCEDURE DELETE_LNS_EVENT_ACTIONS
|
| DESCRIPTION
| This procedure deletes the event action records from the table
| lns_event_actions table for the provided loanType.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_loan_type_id IN Standard in parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 16-Mar-2009 MBOLLI Created
|
*=======================================================================*/
PROCEDURE DELETE_LNS_EVENT_ACTIONS ( p_loan_type_id IN NUMBER) IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_LNS_EVENT_ACTIONS';
DELETE FROM lns_event_actions
WHERE loan_type_id = p_loan_type_id;
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Error in delete_lns_event_actions: ' || sqlerrm);
| PUBLIC PROCEDURE UPDATE_APPROVAL_STATUS_TO_AME
|
| DESCRIPTION
| This procedure is used to notify the AME_ENGINE when an approver Approves a loan.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_loan_type_id IN Standard in parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
|
*=======================================================================*/
/*************************************************************************/
PROCEDURE UPDATE_APPROVAL_STATUS_TO_AME(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_approver_user_name VARCHAR2(320);
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_APPROVAL_STATUS_TO_AME';
AME_API2.updateApprovalStatus(applicationIdIn => 206,
transactionTypeIn => l_transaction_type,
transactionIdIn => l_loan_id,
approverIn => approverRecord,
updateItemIn => TRUE);
proc_name => 'UPDATE_APPROVAL_STATUS_TO_AME',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => null,
arg4 => null,
arg5 => null);
END UPDATE_APPROVAL_STATUS_TO_AME;
| PUBLIC PROCEDURE UPDATE_REJECTED_STATUS_TO_AME
|
| DESCRIPTION
| This procedure is used to notify the AME_ENGINE when an Approver Rejects a loan.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_loan_type_id IN Standard in parameter
|
| KNOWN ISSUES
| None
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
|
*=======================================================================*/
/*************************************************************************/
PROCEDURE UPDATE_REJECTED_STATUS_TO_AME(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_approver_user_name VARCHAR2(320);
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REJECTED_STATUS_TO_AME';
AME_API2.updateApprovalStatus(applicationIdIn => 206,
transactionTypeIn => l_transaction_type,
transactionIdIn => l_loan_id,
approverIn => approverRecord,
updateItemIn => TRUE);
proc_name => 'UPDATE_REJECTED_STATUS_TO_AME',
arg1 => p_item_type,
arg2 => p_item_key,
arg3 => null,
arg4 => null,
arg5 => null);
END UPDATE_REJECTED_STATUS_TO_AME;
select
nvl(manager.manager_flag,'N') manager_flag
from
(select
rol.manager_flag,rel.role_resource_id
FROM
jtf_rs_role_relations rel ,
jtf_rs_roles_b rol
where rel.role_id = rol.role_id
and rel.delete_flag <> 'Y'
and sysdate between nvl(rel.start_date_active,sysdate) and nvl(rel.end_date_active,sysdate)
and rol.role_type_code = 'LOANS'
and rol.role_code = 'LOAN_MGR'
and rol.active_flag = 'Y'
) manager,
jtf_rs_resource_extns res
where
manager.role_resource_id(+) = res.resource_id
and category = 'EMPLOYEE'
and res.start_date_active <= sysdate
and (res.end_date_active is null or res.end_date_active >= sysdate)
and res.user_id = p_user_id;
select user_id from fnd_user where user_name = p_approver_user_name;
| This procedure inserts the loan Approval Actions in LNS_APPROVAL_ACTIONS table
| whenever an approver takes any action from notification.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| PARAMETERS
| itemtype in Item Type
| itemkey in Item Key
| actid in Action Id
| funcmode in Function Mode
| resultout out Result Out
|
| KNOWN ISSUES
| None
|
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 12-Nov-2010 gparuchu Created
*=======================================================================*/
PROCEDURE PROCESS_AME_APPROVAL_ACTION(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_AME_APPROVAL_ACTION';
select loan.object_version_number, loan.org_id into l_object_version_number,l_org_id
from lns_loan_headers_all loan
where loan.loan_id = l_loan_id;
select LNS_APPROVAL_ACTIONS_S.NEXTVAL into l_loan_approval_action_rec.action_id from dual;
l_loan_approval_action_rec.last_updated_by := LNS_UTILITY_PUB.LAST_UPDATED_BY;
l_loan_approval_action_rec.last_update_date := LNS_UTILITY_PUB.LAST_UPDATE_DATE;
l_loan_approval_action_rec.last_update_login := LNS_UTILITY_PUB.LAST_UPDATE_LOGIN;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_date := LNS_UTILITY_PUB.last_update_date;
G_JTF_NOTE_CONTEXTS_TAB(1).last_updated_by := LNS_UTILITY_PUB.last_updated_by;
G_JTF_NOTE_CONTEXTS_TAB(1).last_update_login := LNS_UTILITY_PUB.last_update_login;
P_LAST_UPDATE_DATE => LNS_UTILITY_PUB.last_update_date,
P_LAST_UPDATED_BY => LNS_UTILITY_PUB.last_updated_by,
P_CREATION_DATE => LNS_UTILITY_PUB.creation_date,
P_CREATED_BY => LNS_UTILITY_PUB.created_by,
P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.last_update_login,
X_JTF_NOTE_ID => l_note_id ,
P_NOTE_TYPE => 'LNS_ORIG', -- Note Type : origination
P_NOTE_STATUS => 'I', -- Visibility : public
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_JTF_NOTE_CONTEXTS_TAB => G_JTF_NOTE_CONTEXTS_TAB
);