[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_FUNDING_PUB';
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 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 usr_resp.start_date < sysdate
AND usr_resp.end_date > sysdate
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
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
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;
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;
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';