1 PACKAGE BODY igr_person_type_pkg AS
2 /* $Header: IGSRT06B.pls 120.0 2005/06/01 15:24:58 appldev noship $ */
3
4 PROCEDURE update_persontype_funnel(
5 p_person_id IN NUMBER,
6 p_person_type_code IN VARCHAR2,
7 p_funnel_status IN VARCHAR2,
8 p_return_status OUT NOCOPY VARCHAR2,
9 p_message_text OUT NOCOPY VARCHAR2) AS
10
11 cst_prospect CONSTANT varchar2(50) := 'PROSPECT';
12 cst_applicant CONSTANT varchar2(50) := 'APPLICANT';
13 cst_evaluator CONSTANT varchar2(50) := 'EVALUATOR';
14 cst_funnel_status CONSTANT varchar2(25):= '100-IDENTIFIED';
15
16 lv_type_instance_id NUMBER(15);
17 l_person_type_code igs_pe_person_types.person_type_code%TYPE;
18 l_funnel_status igs_pe_typ_instances_all.funnel_status%TYPE;
19
20 lv_sysdate DATE;
21 l_org_id NUMBER(15);
22 lv_rowid2 VARCHAR2(30);
23
24 CURSOR c_person_type_code(l_system_type IGS_PE_PERSON_TYPES.system_type%TYPE) IS
25 SELECT person_type_code
26 FROM igs_pe_person_types
27 WHERE system_type=l_system_type;
28
29 BEGIN
30 l_org_id := igs_ge_gen_003.get_org_id;
31 lv_sysdate := SYSDATE;
32
33 IF p_person_type_code IS NULL THEN
34 OPEN c_person_type_code('PROSPECT');
35 FETCH c_person_type_code INTO l_person_type_code;
36 CLOSE c_person_type_code;
37 ELSE
38 l_person_type_code := p_person_type_code;
39 END IF;
40 IF p_funnel_status IS NULL THEN
41 l_funnel_status := cst_funnel_status ;
42 ELSE
43 l_funnel_status := p_funnel_status ;
44 END IF;
45
46 -- If the person is an evaluator, you should not create the Inquiry
47
48 IF checkactiveXPersontype(p_person_id, cst_evaluator) THEN
49 p_return_status := 'E';
50 FND_MESSAGE.SET_NAME('IGS','IGS_AD_EVAL_NOT_CRT_INQ');
51 p_message_text := FND_MESSAGE.GET;
52 RETURN;
53 END IF;
54
55
56 IF NOT checkactiveXPersontype(p_person_id, cst_applicant) THEN -- Inactive Applicant exists
57 IF NOT checkactiveXPersontype(p_person_id, cst_prospect) THEN -- Inactive Prospect exists
58 -- Call igs_pe_type_instance.insert_row
59 -- pass the person type as system defined value of 'PROSPECT'
60 -- and funnel status as '100-IDENTIFIED'
61
62 -- Other person type will be automatically deleted inside the
63 -- igs_pe_typ_instances_pkg if we are creating other than 'OTHER'
64 -- person type which is active
65 igs_pe_typ_instances_pkg.insert_row
66 (
67 X_MODE => 'R',
68 X_RowId => lv_rowid2,
69 X_TYPE_INSTANCE_ID => lv_type_instance_id,
70 X_PERSON_TYPE_CODE => l_person_type_code,
71 X_PERSON_ID => p_person_id,
72 X_COURSE_CD => NULL,
73 X_FUNNEL_STATUS => l_funnel_status,
74 X_ADMISSION_APPL_NUMBER => NULL,
75 X_NOMINATED_COURSE_CD => NULL,
76 X_SEQUENCE_NUMBER => NULL,
77 X_START_DATE => lv_sysdate,
78 X_END_DATE => NULL,
79 X_CREATE_METHOD => NULL,
80 X_ENDED_BY => NULL,
81 X_END_METHOD => NULL,
82 X_CC_VERSION_NUMBER => NULL,
83 X_NCC_VERSION_NUMBER => NULL,
84 X_Org_Id => l_org_id,
85 X_EMPLMNT_CATEGORY_CODE => NULL
86 );
87 END IF;
88 END IF;
89 RETURN;
90 END ;
91
92 FUNCTION checkactiveXPersontype(p_person_id IN NUMBER, p_person_type IN VARCHAR2)
93 RETURN BOOLEAN AS
94 l_exists VARCHAR2(1);
95 CURSOR c_persontype_exist (cp_person_id igs_pe_typ_instances.person_id%TYPE)
96 IS
97 SELECT 'X'
98 FROM
99 igs_pe_typ_instances_all pti, igs_pe_person_types pt
100 WHERE
101 pti.person_id = cp_person_id
102 AND pti.person_type_code = pt.person_type_code
103 AND pt.system_type = p_person_type
104 AND (end_date IS NULL OR (TRUNC(end_date) IS NOT NULL AND TRUNC(end_date) > SYSDATE));
105 BEGIN
106 OPEN c_persontype_exist (p_person_id );
107 FETCH c_persontype_exist INTO l_exists;
108 IF c_persontype_exist%FOUND THEN
109 CLOSE c_persontype_exist;
110 RETURN TRUE;
111 ELSE
112 CLOSE c_persontype_exist;
113 RETURN FALSE;
114 END IF;
115 END;
116 END igr_person_type_pkg;
[Home] [Help]