[Home] [Help]
564: l_appl_id NUMBER;
565: --Get all valid lns responsibilities for the user_id
566: CURSOR C_GET_USER_RESPS(p_user_id NUMBER,p_appl_id NUMBER) IS
567: SELECT usr_resp.responsibility_id
568: FROM fnd_user_resp_groups usr_resp
569: WHERE usr_resp.responsibility_application_id = p_appl_id
570: AND usr_resp.start_date < sysdate
571: AND usr_resp.end_date > sysdate
572: AND usr_resp.user_id = p_user_id;
653: l_api_name CONSTANT VARCHAR2(30)
654: := 'CREATE_NOTIFICATION_DETAILS';
655: l_api_version CONSTANT NUMBER := 1.0;
656: l_user_roles VARCHAR2(32000);
657: l_user_name FND_USER.user_name%TYPE;
658: l_role_name VARCHAR2(100);
659: l_loan_number LNS_LOAN_HEADERS.LOAN_NUMBER%TYPE;
660: l_primary_recipient_type lns_event_actions.primary_recipient_type%TYPE;
661: l_primary_recipient_name lns_event_actions.primary_recipient_name%TYPE;
662: l_priority_num lns_event_actions.priority_num%TYPE;
663: l_active_for_num lns_event_actions.active_for_num%TYPE;
664: l_delivery_method lns_event_actions.delivery_method%TYPE;
665: l_loan_assigned_name jtf_rs_resource_extns.source_name%TYPE;
666: l_loan_assigned_user fnd_user.user_name%TYPE;
667: l_current_user fnd_user.user_name%TYPE;
668: l_borrower_name hz_parties.party_name%TYPE;
669: l_loan_class lns_lookups.meaning%TYPE;
670: l_loan_type lns_loan_types.loan_type_name%TYPE;
663: l_active_for_num lns_event_actions.active_for_num%TYPE;
664: l_delivery_method lns_event_actions.delivery_method%TYPE;
665: l_loan_assigned_name jtf_rs_resource_extns.source_name%TYPE;
666: l_loan_assigned_user fnd_user.user_name%TYPE;
667: l_current_user fnd_user.user_name%TYPE;
668: l_borrower_name hz_parties.party_name%TYPE;
669: l_loan_class lns_lookups.meaning%TYPE;
670: l_loan_type lns_loan_types.loan_type_name%TYPE;
671: l_loan_purpose lns_lookups.meaning%TYPE;
703: AND lea.enabled_flag = 'Y'
704: AND lea.loan_type_id = p_loan_type_id;
705: CURSOR csr_current_user IS
706: SELECT fndu.user_name
707: FROM fnd_user fndu
708: WHERE fndu.user_id = p_current_user_id;
709: CURSOR csr_loan_role_users IS
710: SELECT fndu.user_name
711: ,fndu.user_id
711: ,fndu.user_id
712: FROM jtf_rs_role_relations rel
713: ,jtf_rs_roles_b rol
714: ,jtf_rs_resource_extns res
715: ,fnd_user fndu
716: WHERE rel.role_id = rol.role_id
717: AND rel.delete_flag <> 'Y'
718: AND SYSDATE BETWEEN NVL(rel.start_date_active,sysdate)
719: AND NVL(rel.end_date_active,sysdate)
726: AND (res.end_date_active is null or res.end_date_active >= SYSDATE)
727: AND fndu.user_id = res.user_id;
728: CURSOR csr_loan_details IS
729: SELECT hp.party_name borrower_name
730: ,fnd.user_name
731: ,res.source_name
732: ,llklc.meaning loan_class
733: ,llklt.loan_type_name loan_type
734: ,llkst.meaning loan_subtype
739: ,LNS_FINANCIALS.getActiveRate(llh.LOAN_ID) interest_rate
740: ,llkp.meaning loan_purpose
741: ,llh.org_id
742: FROM lns_loan_headers_all_vl llh, hz_parties hp,
743: jtf_rs_resource_extns res, fnd_user fnd,
744: lns_payments_summary_v ps,
745: lns_lookups llktt,
746: lns_lookups llklc,
747: lns_loan_types_vl llklt,
749: lns_lookups llkst
750: WHERE llh.primary_borrower_id = hp.party_id
751: AND llh.loan_assigned_to = res.resource_id
752: AND res.category = 'EMPLOYEE'
753: AND fnd.user_id = res.user_id
754: AND llktt.lookup_code = llh.loan_term_period
755: AND llktt.lookup_type = 'PERIOD'
756: AND llklc.lookup_code = llh.loan_class_code
757: AND llklc.lookup_type = 'LOAN_CLASS'