1 PACKAGE BODY PER_US_ASG_EXT_INFO_CHK AS
2 /* $Header: peuseitd.pkb 120.0 2005/05/31 22:36:54 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : PER_US_ASG_EXT_INFO_CHK
21
22 Description : This package checks for unique combination of state and involuntary
23 deduction category entered from EIT. This package is called by
24 before process hooks of create_assignment_extra_info and
25 update_assignment_extra_info APIs.
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ------ ------- -------------------------------
31 26-MAY-2004 sdahiya 115.0 Created.
32 *****************************************************************************/
33
34
35 /*******************************************************************************
36 Name : chk_dup_proration_ins
37 Purpose : This procedure checks for unique combination of state and involuntary
38 deduction category while inserting assignment EIT records.
39 *******************************************************************************/
40
41 PROCEDURE CHK_DUP_PRORATION_INS(
42 p_assignment_id per_assignment_extra_info.assignment_id%TYPE,
43 p_aei_information_category per_assignment_extra_info.aei_information_category%TYPE,
44 p_aei_information1 per_assignment_extra_info.aei_information1%TYPE,
45 p_aei_information2 per_assignment_extra_info.aei_information2%TYPE
46 ) IS
47
48 CURSOR c_dup_records IS
49 SELECT COUNT(1)
50 FROM per_assignment_extra_info
51 WHERE assignment_id = p_assignment_id
52 AND aei_information1 = p_aei_information1
53 AND aei_information2 = p_aei_information2
54 AND ((assignment_extra_info_id <> GLB_ASG_EXTRA_INFO_ID AND GLB_MODE IS NOT NULL) OR
55 (GLB_MODE IS NULL))
56 AND aei_information_category = 'US_PRORATION_RULE';
57
58 l_count number;
59 l_proc_name varchar2(100);
60
61 BEGIN
62
63 l_proc_name := 'PER_US_ASG_EXT_INFO_CHK.CHK_DUP_PRORATION_INS';
64 hr_utility.trace('Entering '||l_proc_name);
65 hr_utility.set_location (l_proc_name,10);
66 hr_utility.trace('Input parameters....');
67 hr_utility.trace('P_ASSIGNMENT_ID = '||P_ASSIGNMENT_ID);
68 hr_utility.trace('P_AEI_INFORMATION_CATEGORY = '||P_AEI_INFORMATION_CATEGORY);
69 hr_utility.trace('P_AEI_INFORMATION1 = '||P_AEI_INFORMATION1);
70 hr_utility.trace('P_AEI_INFORMATION2 = '||P_AEI_INFORMATION2);
71
72 IF p_aei_information_category = 'US_PRORATION_RULE' THEN
73 OPEN c_dup_records;
74 FETCH c_dup_records INTO l_count;
75 CLOSE c_dup_records;
76
77 hr_utility.trace('Number of records = '||l_count);
78 IF l_count >= 1 THEN
79 hr_utility.set_location (l_proc_name,20);
80 hr_utility.set_message(800, 'PER_US_DUP_PRORATION');
81 hr_utility.raise_error;
82 END IF;
83 END IF;
84 hr_utility.set_location (l_proc_name,30);
85 hr_utility.trace('Leaving '||l_proc_name);
86
87 END CHK_DUP_PRORATION_INS;
88
89
90 /*******************************************************************************
91 Name : chk_dup_proration_upd
92 Purpose : This procedure checks for unique combination of state and involuntary
93 deduction category while updating assignment EIT records.
94 *******************************************************************************/
95
96 PROCEDURE CHK_DUP_PRORATION_UPD(
97 p_assignment_extra_info_id per_assignment_extra_info.assignment_extra_info_id%TYPE,
98 p_aei_information_category per_assignment_extra_info.aei_information_category%TYPE,
99 p_aei_information1 per_assignment_extra_info.aei_information1%TYPE,
100 p_aei_information2 per_assignment_extra_info.aei_information2%TYPE
101 ) IS
102 CURSOR c_asg_id IS
103 SELECT assignment_id
104 FROM per_assignment_extra_info
105 WHERE assignment_extra_info_id = p_assignment_extra_info_id;
106
107 l_asg_id per_assignment_extra_info.assignment_id%TYPE;
108 l_proc_name varchar2(100);
109 BEGIN
110 l_proc_name := 'PER_US_ASG_EXT_INFO_CHK.CHK_DUP_PRORATION_UPD';
111 hr_utility.trace('Entering '||l_proc_name);
112 hr_utility.set_location(l_proc_name, 10);
113 hr_utility.trace('Input parameters....');
114 hr_utility.trace('P_ASSIGNMENT_EXTRA_INFO_ID = '||P_ASSIGNMENT_EXTRA_INFO_ID);
115 hr_utility.trace('P_AEI_INFORMATION_CATEGORY = '||P_AEI_INFORMATION_CATEGORY);
116 hr_utility.trace('P_AEI_INFORMATION1 = '||P_AEI_INFORMATION1);
117 hr_utility.trace('P_AEI_INFORMATION2 = '||P_AEI_INFORMATION2);
118 OPEN c_asg_id;
119 FETCH c_asg_id INTO l_asg_id;
120 CLOSE c_asg_id;
121
122 GLB_MODE := 'UPDATE';
123 GLB_ASG_EXTRA_INFO_ID := p_assignment_extra_info_id;
124
125 CHK_DUP_PRORATION_INS(
126 l_asg_id,
127 p_aei_information_category,
128 p_aei_information1,
129 p_aei_information2
130 );
131
132 GLB_MODE := NULL;
133 hr_utility.set_location(l_proc_name, 20);
134 hr_utility.trace('Leaving '||l_proc_name);
135 END CHK_DUP_PRORATION_UPD;
136
137 END PER_US_ASG_EXT_INFO_CHK;
[Home] [Help]