1 PACKAGE BODY PER_PL_DEI_INFO AS
2 /* $Header: pepldeip.pkb 120.1 2006/09/13 11:14:04 mseshadr noship $ */
3
4 PROCEDURE VALIDATE_DATES(P_ISSUED_DATE DATE,
5 P_DATE_FROM DATE,
6 P_DOCUMENT_NUMBER VARCHAR2) IS
7
8
9 BEGIN
10
11
12 /* Issue Date is mandatory */
13 if p_issued_date is null then
14 hr_utility.set_message(800,'HR_375884_DEI_DATE_REQD');
15 hr_utility.raise_error;
16 end if;
17
18 /* Document Number si mandatory */
19 if p_document_number is null then
20 hr_utility.set_message(800,'HR_375885_DEI_NO_REQD');
21 hr_utility.raise_error;
22 end if;
23
24 -- Issued Date should be same as that of Valid From date.
25 if P_ISSUED_DATE <> P_DATE_FROM then
26 hr_utility.set_message(800,'HR_375876_INVALID_DEI_DATE');
27 hr_utility.raise_error;
28 end if;
29
30 END VALIDATE_DATES;
31
32 PROCEDURE CREATE_PL_DEI_INFO(P_PERSON_ID NUMBER,
33 P_DOCUMENT_TYPE_ID NUMBER,
34 P_DOCUMENT_NUMBER VARCHAR2,
35 P_ISSUED_DATE DATE,
36 P_DATE_FROM DATE,
37 P_DATE_TO DATE) IS
38
39 cursor csr_document_type is
40 select system_document_type from hr_document_types
41 where document_type_id = p_document_type_id;
42
43 cursor csr_doc_exists is
44 select null from hr_document_extra_info
45 where person_id = p_person_id
46 and document_type_id = p_document_type_id
47 and (date_from between p_date_from and p_date_to or
48 date_to between p_date_from and p_date_to or
49 p_date_from between date_from and date_to);
50
51 l_exists varchar2(1);
52 l_sys_doc_type hr_document_types.system_document_type%TYPE;
53 l_proc varchar2(18);
54 BEGIN
55 l_proc:='CREATE_PL_DEI_INFO';
56 /* Added for GSI Bug 5472781 */
57 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
58 hr_utility.set_location('Leaving : '||l_proc,10);
59 return;
60 END IF;
61 open csr_document_type;
62 fetch csr_document_type into l_sys_doc_type;
63 close csr_document_type;
64
65 if l_sys_doc_type in ('PL_PASSPORT','PL_IDENTITY_CARD','PL_MILITARY_BOOK') then
66
67 VALIDATE_DATES(P_ISSUED_DATE => p_issued_date,
68 P_DATE_FROM => p_date_from,
69 P_DOCUMENT_NUMBER => p_document_number);
70
71 end if;
72
73
74 -- Another document of the same type should not exists in the same period when the current record
75 -- is created
76 /* Do the validations only for Passport and Identity Card */
77
78 if l_sys_doc_type in ('PL_PASSPORT','PL_IDENTITY_CARD') then
79
80 open csr_doc_exists;
81 fetch csr_doc_exists into l_exists;
82 if csr_doc_exists%FOUND then
83 hr_utility.set_message(800,'HR_375877_OVERLAP_DEI_INFO');
84 hr_utility.raise_error;
85 end if;
86 close csr_doc_exists;
87
88 end if;
89
90 END CREATE_PL_DEI_INFO;
91
92
93
94 PROCEDURE UPDATE_PL_DEI_INFO(P_DOCUMENT_EXTRA_INFO_ID NUMBER,
95 P_DOCUMENT_TYPE_ID NUMBER,
96 P_DOCUMENT_NUMBER VARCHAR2,
97 P_PERSON_ID NUMBER,
98 P_DATE_FROM DATE,
99 P_DATE_TO DATE,
100 P_ISSUED_DATE DATE) IS
101
102 cursor csr_document_type is
103 select system_document_type from hr_document_types
104 where document_type_id = p_document_type_id;
105
106 cursor csr_doc_exists is
107 select null from hr_document_extra_info
108 where person_id = p_person_id
109 and document_type_id = p_document_type_id
110 and (date_from between p_date_from and p_date_to or
111 date_to between p_date_from and p_date_to or
112 p_date_from between date_from and date_to)
113 and document_extra_info_id <> p_document_extra_info_id;
114
115 l_exists varchar2(1);
116 l_sys_doc_type hr_document_types.system_document_type%TYPE;
117 l_proc varchar2(18);
118 BEGIN
119 l_proc:='UPDATE_PL_DEI_INFO';
120 /* Added for GSI Bug 5472781 */
121 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
122 hr_utility.set_location('Leaving : '||l_proc,10);
123 return;
124 END IF;
125
126 open csr_document_type;
127 fetch csr_document_type into l_sys_doc_type;
128 close csr_document_type;
129
130 if l_sys_doc_type in ('PL_PASSPORT','PL_IDENTITY_CARD','PL_MILITARY_BOOK') then
131
132 VALIDATE_DATES(P_ISSUED_DATE => p_issued_date,
133 P_DATE_FROM => p_date_from,
134 P_DOCUMENT_NUMBER => p_document_number);
135
136 end if;
137
138 -- Another document of the same type should not exists in the same period when the current record
139 -- is created
140
141 /* Do the validations only for Passport and Identity Card */
142
143 if l_sys_doc_type in ('PL_PASSPORT','PL_IDENTITY_CARD') then
144
145 open csr_doc_exists;
146 fetch csr_doc_exists into l_exists;
147 if csr_doc_exists%FOUND then
148 hr_utility.set_message(800,'HR_375877_OVERLAP_DEI_INFO');
149 hr_utility.raise_error;
150 end if;
151 close csr_doc_exists;
152
153 end if;
154
155 END UPDATE_PL_DEI_INFO;
156
157 END PER_PL_DEI_INFO;
[Home] [Help]