[Home] [Help]
PACKAGE BODY: APPS.PER_US_VALIDATE_AEI
Source
1 Package body per_us_validate_aei as
2 /* $Header: peusaeiv.pkb 115.3 2003/05/17 03:59:17 tmehra ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_us_validate_aei.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |-----------------------< chk_for_duplicate_rows >-------------------------|
13 -- ----------------------------------------------------------------------------
14 -- Description:
15 -- Verify that the segment values are not duplicated for an information
16 -- type in a multi record situation
17 -- Added for US Payroll specific situations.
18 --
19 -- Pre Conditions:
20 --
21 --
22 -- In Parameters:
23 -- assignment_id, information_type, aei_information1, aei_information2
24 --
25 -- Post Success:
26 -- Processing continues.
27 --
28 -- Post Failure:
29 -- An application error will be raised and processing is terminated.
30 --
31 -- Access Status:
32 -- Internal Table Handler Use Only.
33 -- ----------------------------------------------------------------------------
34 Procedure chk_for_duplicate_rows ( p_assignment_id number,
35 p_information_type varchar2,
36 p_aei_information1 varchar2,
37 p_aei_information2 varchar2,
38 p_aei_information3 varchar2) is
39 --
40 l_proc varchar2(100) := g_package||'chk_for_duplicate_rows';
41 l_count number := 0;
42 --
43 CURSOR c1 (p_assignment_id number,
44 p_information_type varchar2,
45 p_aei_information1 varchar2,
46 p_aei_information3 varchar2) is
47 SELECT count(*)
48 FROM per_assignment_extra_info
49 WHERE assignment_id = p_assignment_id
50 AND information_type = p_information_type
51 AND aei_information1 = p_aei_information1
52 AND aei_information3 = p_aei_information3
53 HAVING count(*) > 1;
54
55
56 CURSOR c2 (p_assignment_id number,
57 p_aei_information1 varchar2,
58 p_aei_information3 varchar2) is
59 SELECT count(*) ct
60 FROM hr_organization_information orgi,
61 per_assignments_f paf,
62 hr_soft_coding_keyflex sft
63 WHERE orgi.organization_id = to_number(sft.segment1)
64 AND sft.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
65 AND paf.assignment_id = p_assignment_id
66 AND orgi.org_information1 = p_aei_information1
67 AND orgi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
68 AND orgi.org_information3 = p_aei_information3;
69
70
71 --
72 Begin
73 --
74 hr_utility.set_location('Entering:'||l_proc, 10);
75 --
76 if p_information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD' then
77 --
78 -- check for wage plan code and duplicate record for california
79 --
80 if p_aei_information1 = 'CA' then
81 if p_aei_information3 is null then
82 hr_utility.set_message(801, 'HR_6001_ALL_MANDATORY_FIELD');
83 hr_utility.set_message_token('MISSING_FIELD', 'Tax Type for California');
84 hr_utility.raise_error;
85 end if;
86 --
87 hr_utility.set_location(l_proc, 20);
88 --
89 for c1_rec in c1 (p_assignment_id,
90 p_information_type,
91 p_aei_information1,
92 p_aei_information3 ) loop
93 --
94 -- raise error if the record exists for the assignment id
95 --
96 hr_utility.set_message(800, 'PER_AEI_REC_EXIST_FOR_INFO_TYP');
97 hr_utility.raise_error;
98 end loop;
99
100 for c2_rec in c2 (p_assignment_id,
101 p_aei_information1,
102 p_aei_information3 ) loop
103
104 l_count := c2_rec.ct;
105
106 end loop;
107
108 IF l_count = 0 THEN
109
110 --
111 -- raise error if the wage plan is not defined at the GRE level
112 --
113
114 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
115 hr_utility.set_message_token('FORMAT','with Tax Type defined at GRE');
116
117 hr_utility.raise_error;
118
119 END IF;
120
121
122 end if;
123 end if;
124 --
125 hr_utility.set_location(' Leaving:'||l_proc, 50);
126 --
127 End chk_for_duplicate_rows;
128 --
129 --
130 End per_us_validate_aei;