1 PACKAGE BODY pay_functional_usages_pkg AS
2 -- $Header: pypfuapi.pkb 115.2 2002/12/11 15:13:43 exjones noship $
3 --
4 PROCEDURE lock_row(
5 p_row_id IN VARCHAR2,
6 p_usage_id IN NUMBER,
7 p_area_id IN NUMBER,
8 p_legislation_code IN VARCHAR2,
9 p_business_group_id IN NUMBER,
10 p_payroll_id IN NUMBER
11 ) IS
12 --
13 CURSOR csr_functional_usage IS
14 SELECT *
15 FROM pay_functional_usages
16 WHERE rowid = p_row_id
17 FOR UPDATE OF
18 usage_id NOWAIT;
19 --
20 usage_record csr_functional_usage%ROWTYPE;
21 --
22 BEGIN
23 OPEN csr_functional_usage;
24 FETCH csr_functional_usage INTO usage_record;
25 IF csr_functional_usage%NOTFOUND THEN
26 CLOSE csr_functional_usage;
27 Hr_Utility.Set_Message(801,'HR_6153_ALL_PROCEDURE_FAIL');
28 Hr_Utility.Set_Message_Token(
29 'PROCEDURE',
30 'PAY_FUNCTIONAL_USAGES_PKG.LOCK_ROW'
31 );
32 END IF;
33 CLOSE csr_functional_usage;
34 --
35 IF ((usage_record.usage_id = p_usage_id) OR
36 (usage_record.usage_id IS NULL AND p_usage_id IS NULL))
37 AND ((usage_record.area_id = p_area_id) OR
38 (usage_record.area_id IS NULL AND p_area_id IS NULL))
39 AND ((usage_record.legislation_code = p_legislation_code) OR
40 (usage_record.legislation_code IS NULL AND p_legislation_code IS NULL))
41 AND ((usage_record.business_group_id = p_business_group_id) OR
42 (usage_record.business_group_id IS NULL AND p_business_group_id IS NULL))
43 AND ((usage_record.payroll_id = p_payroll_id) OR
44 (usage_record.payroll_id IS NULL AND p_payroll_id IS NULL))
45 THEN
46 RETURN;
47 ELSE
48 Hr_Utility.Set_Message(0,'FORM_RECORD_CHANGED');
49 Hr_Utility.Raise_Error;
50 END IF;
51 END lock_row;
52 --
53 PROCEDURE insert_row(
54 p_row_id IN out nocopy VARCHAR2,
55 p_usage_id IN out nocopy NUMBER,
56 p_area_id IN NUMBER,
57 p_legislation_code IN VARCHAR2,
58 p_business_group_id IN NUMBER,
59 p_payroll_id IN NUMBER
60 ) IS
61 --
62 CURSOR csr_new_id IS
63 SELECT pay_functional_usages_s.NEXTVAL
64 FROM dual;
65 --
66 CURSOR csr_usage_rowid IS
67 SELECT rowid
68 FROM pay_functional_usages
69 WHERE usage_id = p_usage_id;
70 --
71 BEGIN
72 --
73 OPEN csr_new_id;
74 FETCH csr_new_id INTO p_usage_id;
75 CLOSE csr_new_id;
76 --
77 INSERT INTO pay_functional_usages(
78 usage_id,
79 area_id,
80 legislation_code,
81 business_group_id,
82 payroll_id
83 ) VALUES (
84 p_usage_id,
85 p_area_id,
86 p_legislation_code,
87 p_business_group_id,
88 p_payroll_id
89 );
90 --
91 OPEN csr_usage_rowid;
92 FETCH csr_usage_rowid INTO p_row_id;
93 IF csr_usage_rowid%NOTFOUND THEN
94 CLOSE csr_usage_rowid;
95 Hr_Utility.Set_Message(801,'HR_6153_ALL_PROCEDURE_FAIL');
96 Hr_Utility.Set_Message_Token(
97 'PROCEDURE',
98 'PAY_FUNCTIONAL_USAGES_PKG.INSERT_ROW'
99 );
100 END IF;
101 CLOSE csr_usage_rowid;
102 --
103 END insert_row;
104 --
105 PROCEDURE update_row(
106 p_row_id IN VARCHAR2,
107 p_usage_id IN NUMBER,
108 p_area_id IN NUMBER,
109 p_legislation_code IN VARCHAR2,
110 p_business_group_id IN NUMBER,
111 p_payroll_id IN NUMBER
112 ) IS
113 BEGIN
114 UPDATE pay_functional_usages
115 SET usage_id = p_usage_id,
116 area_id = p_area_id,
117 legislation_code = p_legislation_code,
118 business_group_id = p_business_group_id,
119 payroll_id = p_payroll_id
120 WHERE rowid = p_row_id;
121 --
122 IF SQL%NOTFOUND THEN
123 Hr_Utility.Set_Message(801,'HR_6153_ALL_PROCEDURE_FAIL');
124 Hr_Utility.Set_Message_Token(
125 'PROCEDURE',
126 'PAY_FUNCTIONAL_USAGES_PKG.UPDATE_ROW'
127 );
128 END IF;
129 END update_row;
130 --
131 PROCEDURE delete_row(
132 p_row_id IN VARCHAR2,
133 p_usage_id IN NUMBER
134 ) IS
135 BEGIN
136 DELETE
137 FROM pay_functional_usages
138 WHERE rowid = p_row_id;
139 --
140 IF SQL%NOTFOUND THEN
141 Hr_Utility.Set_Message(801,'HR_6153_ALL_PROCEDURE_FAIL');
142 Hr_Utility.Set_Message_Token(
143 'PROCEDURE',
144 'PAY_FUNCTIONAL_USAGES_PKG.DELETE_ROW'
145 );
146 END IF;
147 END delete_row;
148 --
149 END pay_functional_usages_pkg;