1 PACKAGE BODY per_fr_org_ddf_validation AS
2 /* $Header: pefroriv.pkb 120.0.12000000.2 2007/02/28 10:40:22 spendhar ship $ */
3 --
4 g_package constant varchar2(30) := 'per_fr_org_ddf_validation';
5 --
6 PROCEDURE validate_fr_opm_mapping
7 (p_org_information_id IN NUMBER
8 ,p_org_information_context IN VARCHAR2
9 ,p_organization_id IN NUMBER
10 ,p_org_information1 IN VARCHAR2
11 ,p_org_information2 IN VARCHAR2)
12 IS
13 l_proc varchar2(72) := g_package||'.validate_fr_opm_mapping';
14 L_dummy number;
15 Cursor csr_chk_specific_opm is
16 select 1
17 from hr_organization_information
18 where ORG_INFORMATION_CONTEXT = 'FR_DYN_PAYMETH_MAPPING_INFO'
19 and ORG_INFORMATION1 = p_org_information1
20 and ORGANIZATION_ID <> p_organization_id;
21 --
22 cursor csr_chk_generic_opm is
23 select 1
24 from hr_organization_information
25 where ORG_INFORMATION_CONTEXT = 'FR_DYN_PAYMETH_MAPPING_INFO'
26 and ORG_INFORMATION2 = p_org_information2
27 and ORGANIZATION_ID = p_organization_id
28 and org_information_id <> p_org_information_id;
29 --
30 BEGIN
31 --
32 /* Added for GSI Bug 5472781 */
33 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
34 hr_utility.set_location('Leaving : '|| l_proc , 5);
35 return;
36 END IF;
37 --
38 hr_utility.set_location('Entering: '||l_proc,5);
39 if p_org_information_context = 'FR_DYN_PAYMETH_MAPPING_INFO' then
40 hr_utility.set_location(l_proc,10);
41 if hr_multi_message.no_exclusive_error
42 (p_check_column1 => 'HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1')
43 then
44 -- No error for ORG_INFORMATION1 already exists so,
45 -- prevent account-specific methods from being attached to more than
46 -- one organization, but allow them to be registered more than once to
47 -- the same organization.
48 --
49 Open csr_chk_specific_opm;
50 Fetch csr_chk_specific_opm into L_dummy;
51 If csr_chk_specific_opm%FOUND then
52 Close csr_chk_specific_opm;
53 fnd_message.set_name('PER', 'HR_75032_ORI_SPEC_OPM_EXISTS');
54 hr_multi_message.add(p_associated_column1 =>
55 'HR_ORGANIZATION_INFORMATION.ORG_INFORMATION1');
56 else
57 close csr_chk_specific_opm;
58 end if;
59 end if; -- check existing errors
60 if hr_multi_message.no_exclusive_error
61 (p_check_column1 => 'HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2')
62 then
63 -- No error for ORG_INFORMATION2 already exists so,
64 -- prevent account-independent methods from being attached to an
65 -- organization more than once.
66 --
67 Open csr_chk_generic_opm;
68 fetch csr_chk_generic_opm into l_dummy;
69 if csr_chk_generic_opm%FOUND then
70 close csr_chk_generic_opm;
71 fnd_message.set_name('PER', 'HR_75033_ORI_GEN_OPM_EXISTS');
72 hr_multi_message.add(p_associated_column1 =>
73 'HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2');
74 else
75 close csr_chk_generic_opm;
76 end if;
77 end if; -- check existing errors
78 end if; -- org_information_context = 'FR_DYN_PAYMETH_MAPPING_INFO'
79 hr_utility.set_location(' Leaving: '||l_proc,99);
80 end validate_fr_opm_mapping;
81
82 PROCEDURE validate_fr_contrib_codes
83 (p_org_information_id IN NUMBER
84 ,p_org_information_context IN VARCHAR2
85 ,p_organization_id IN NUMBER
86 ,p_org_information3 IN VARCHAR2
87 ,p_org_information5 IN VARCHAR2)
88 IS
89 l_proc varchar2(72) := g_package||'.validate_fr_contrib_codes';
90 l_inv_code varchar2(1) := 'Y';
91 l_type varchar2(10);
92 Cursor c_ins_prov_type IS
93 select nvl(ip.type, null)
94 from hr_fr_insurance_providers_v ip
95 where ip.organization_id = p_org_information3;
96 BEGIN
97 --
98 /* Added for GSI Bug 5472781 */
99 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
100 hr_utility.set_location('Leaving : '|| l_proc , 01);
101 return;
102 END IF;
103 --
104 hr_utility.set_location(' Entering '||l_proc,01);
105 if p_org_information_context = 'FR_COMP_CADRE_LIFE_INSURE' then
106 hr_utility.set_location(' Validating contrib_codes '||p_org_information5,01);
107 open c_ins_prov_type;
108 fetch c_ins_prov_type into l_type;
109 close c_ins_prov_type;
110
111 if l_type = 'AGIRC' then
112 if (substr(p_org_information5,1,1) = '3'
113 and substr(p_org_information5,2,4) = 'XXXX'
114 and substr(p_org_information5,6,1) between '0' and '9'
115 and substr(p_org_information5,7,1) between '0' and '9' ) then
116 l_inv_code := 'N';
117 end if;
118 elsif l_type = 'ARRCO' then
119 if (substr(p_org_information5,1,1) = '4'
120 and substr(p_org_information5,2,4) = 'XXXX'
121 and substr(p_org_information5,6,1) between '0' and '9'
122 and substr(p_org_information5,7,1) between '0' and '9' ) then
123 l_inv_code := 'N';
124 end if;
125 else
126 if not( substr(p_org_information5,1,1) between '1' and '4') then
127 l_inv_code := 'N';
128 end if;
129 end if; -- org_information3 check
130 if l_inv_code = 'Y' then
131 fnd_message.set_name('PAY','PAY_74962_INVALID_CONTRIB_CODE');
132 fnd_message.set_token('CONTRIB_CODE',p_org_information5);
133 fnd_message.raise_error;
134 end if; -- invalid code check
135 end if; -- org_information_context check
136 END validate_fr_contrib_codes;
137
138 end per_fr_org_ddf_validation;