42: Begin
43: --
44: hr_utility.set_location('Entering:'||l_proc, 5);
45: --
46: l_api_updating := per_bil_shd.api_updating
47: (p_id_value => p_id_value,
48: p_object_version_number => p_object_version_number);
49: --
50: if (l_api_updating
48: p_object_version_number => p_object_version_number);
49: --
50: if (l_api_updating
51: and nvl(p_id_value,hr_api.g_number)
52: <> per_bil_shd.g_old_rec.id_value) then
53: --
54: -- raise error as PK has changed
55: --
56: per_bil_shd.constraint_error('hr_summary_PK');
52: <> per_bil_shd.g_old_rec.id_value) then
53: --
54: -- raise error as PK has changed
55: --
56: per_bil_shd.constraint_error('hr_summary_PK');
57: --
58: elsif not l_api_updating then
59: --
60: -- check if PK is null
62: if p_id_value is not null then
63: --
64: -- raise error as PK is not null
65: --
66: per_bil_shd.constraint_error('hr_summary_PK');
67: --
68: end if;
69: --
70: end if;
75: --
76: -- ----------------------------------------------------------------------------
77: -- |---------------------------< insert_validate >----------------------------|
78: -- ----------------------------------------------------------------------------
79: Procedure insert_validate(p_rec in per_bil_shd.g_rec_type) is
80: --
81: l_proc varchar2(72) := g_package||'insert_validate';
82: l_stmt hr_summary_restriction_type.restriction_sql%type;
83: --
95: if p_rec.fk_value1 is not null
96: or p_rec.fk_value2 is not null
97: or p_rec.fk_value3 is not null then
98: --
99: if not per_bil_shd.parent_found(p_rec => p_rec) then
100: per_bil_shd.constraint_error('PARENT_RECORD');
101: end if;
102: --
103: end if;
96: or p_rec.fk_value2 is not null
97: or p_rec.fk_value3 is not null then
98: --
99: if not per_bil_shd.parent_found(p_rec => p_rec) then
100: per_bil_shd.constraint_error('PARENT_RECORD');
101: end if;
102: --
103: end if;
104: --
101: end if;
102: --
103: end if;
104: --
105: if per_bil_shd.row_exist (p_rec => p_rec) then
106: per_bil_shd.constraint_error('UNIQUE_ROW');
107: end if;
108: --
109: if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
102: --
103: end if;
104: --
105: if per_bil_shd.row_exist (p_rec => p_rec) then
106: per_bil_shd.constraint_error('UNIQUE_ROW');
107: end if;
108: --
109: if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
110: --
107: end if;
108: --
109: if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
110: --
111: per_bil_shd.lookup_exists(p_code => p_rec.text_value1
112: ,p_type => 'GSP_'||p_rec.type);
113: --
114: if p_rec.type = 'RESTRICTION_TYPE' then
115: if p_rec.text_value4 is not null then
113: --
114: if p_rec.type = 'RESTRICTION_TYPE' then
115: if p_rec.text_value4 is not null then
116: l_stmt := p_rec.text_value4;
117: per_bil_shd.check_restriction_sql (p_stmt => l_stmt
118: ,p_business_group_id => p_rec.business_group_id);
119: end if;
120: end if;
121: --
120: end if;
121: --
122: end if;
123: --
124: if p_rec.type = 'ITEM_TYPE_USAGE' and per_bil_shd.sequence_exist (p_rec => p_rec) then
125: per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
126: end if;
127: --
128: if p_rec.type = 'RESTRICTION_VALUE' then
121: --
122: end if;
123: --
124: if p_rec.type = 'ITEM_TYPE_USAGE' and per_bil_shd.sequence_exist (p_rec => p_rec) then
125: per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
126: end if;
127: --
128: if p_rec.type = 'RESTRICTION_VALUE' then
129: --
126: end if;
127: --
128: if p_rec.type = 'RESTRICTION_VALUE' then
129: --
130: if not per_bil_shd.chk_date_valid(p_rec => p_rec) then
131: fnd_message.set_name('PER','HR_51155_INVAL_DATE_FORMAT');
132: fnd_message.raise_error;
133: end if;
134: --
131: fnd_message.set_name('PER','HR_51155_INVAL_DATE_FORMAT');
132: fnd_message.raise_error;
133: end if;
134: --
135: per_bil_shd.valid_value(p_rec => p_rec);
136: --
137: end if;
138: --
139: hr_utility.set_location(' Leaving:'||l_proc, 10);
141: --
142: -- ----------------------------------------------------------------------------
143: -- |---------------------------< update_validate >----------------------------|
144: -- ----------------------------------------------------------------------------
145: Procedure update_validate(p_rec in per_bil_shd.g_rec_type) is
146: --
147: l_proc varchar2(72) := g_package||'update_validate';
148: l_stmt hr_summary_restriction_type.restriction_sql%type;
149: --
160: --
161: -- Check to see if any of the (user-owned) fk values or name have changed
162: -- If they have then check too see if it is unique/valid
163: --
164: if (per_bil_shd.g_old_rec.fk_value1 <> p_rec.fk_value1 or
165: per_bil_shd.g_old_rec.fk_value2 <> p_rec.fk_value2 or
166: per_bil_shd.g_old_rec.text_value1 <> p_rec.text_value1) then
167: if per_bil_shd.row_exist (p_rec => p_rec) then
168: per_bil_shd.constraint_error('UNIQUE_ROW');
161: -- Check to see if any of the (user-owned) fk values or name have changed
162: -- If they have then check too see if it is unique/valid
163: --
164: if (per_bil_shd.g_old_rec.fk_value1 <> p_rec.fk_value1 or
165: per_bil_shd.g_old_rec.fk_value2 <> p_rec.fk_value2 or
166: per_bil_shd.g_old_rec.text_value1 <> p_rec.text_value1) then
167: if per_bil_shd.row_exist (p_rec => p_rec) then
168: per_bil_shd.constraint_error('UNIQUE_ROW');
169: end if;
162: -- If they have then check too see if it is unique/valid
163: --
164: if (per_bil_shd.g_old_rec.fk_value1 <> p_rec.fk_value1 or
165: per_bil_shd.g_old_rec.fk_value2 <> p_rec.fk_value2 or
166: per_bil_shd.g_old_rec.text_value1 <> p_rec.text_value1) then
167: if per_bil_shd.row_exist (p_rec => p_rec) then
168: per_bil_shd.constraint_error('UNIQUE_ROW');
169: end if;
170: end if;
163: --
164: if (per_bil_shd.g_old_rec.fk_value1 <> p_rec.fk_value1 or
165: per_bil_shd.g_old_rec.fk_value2 <> p_rec.fk_value2 or
166: per_bil_shd.g_old_rec.text_value1 <> p_rec.text_value1) then
167: if per_bil_shd.row_exist (p_rec => p_rec) then
168: per_bil_shd.constraint_error('UNIQUE_ROW');
169: end if;
170: end if;
171: --
164: if (per_bil_shd.g_old_rec.fk_value1 <> p_rec.fk_value1 or
165: per_bil_shd.g_old_rec.fk_value2 <> p_rec.fk_value2 or
166: per_bil_shd.g_old_rec.text_value1 <> p_rec.text_value1) then
167: if per_bil_shd.row_exist (p_rec => p_rec) then
168: per_bil_shd.constraint_error('UNIQUE_ROW');
169: end if;
170: end if;
171: --
172: -- only check for unique sequence number if row being updated is not system-owned
171: --
172: -- only check for unique sequence number if row being updated is not system-owned
173: --
174: if p_rec.type = 'ITEM_TYPE_USAGE'
175: and per_bil_shd.g_old_rec.num_value1 <> p_rec.num_value1 then
176: if per_bil_shd.sequence_exist (p_rec => p_rec) then
177: per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
178: end if;
179: end if;
172: -- only check for unique sequence number if row being updated is not system-owned
173: --
174: if p_rec.type = 'ITEM_TYPE_USAGE'
175: and per_bil_shd.g_old_rec.num_value1 <> p_rec.num_value1 then
176: if per_bil_shd.sequence_exist (p_rec => p_rec) then
177: per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
178: end if;
179: end if;
180: --
173: --
174: if p_rec.type = 'ITEM_TYPE_USAGE'
175: and per_bil_shd.g_old_rec.num_value1 <> p_rec.num_value1 then
176: if per_bil_shd.sequence_exist (p_rec => p_rec) then
177: per_bil_shd.constraint_error('UNIQUE_SEQUENCE');
178: end if;
179: end if;
180: --
181: if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
179: end if;
180: --
181: if p_rec.type in ('ITEM_TYPE','RESTRICTION_TYPE','KEY_TYPE') then
182: --
183: per_bil_shd.lookup_exists(p_code => p_rec.text_value1
184: ,p_type => 'GSP_'||p_rec.type);
185: --
186: if p_rec.type = 'RESTRICTION_TYPE' then
187: if p_rec.text_value4 is not null then
185: --
186: if p_rec.type = 'RESTRICTION_TYPE' then
187: if p_rec.text_value4 is not null then
188: l_stmt := p_rec.text_value4;
189: per_bil_shd.check_restriction_sql (p_stmt => l_stmt
190: ,p_business_group_id => p_rec.business_group_id);
191: end if;
192: end if;
193: --
193: --
194: end if;
195: --
196: if p_rec.type = 'RESTRICTION_VALUE' then
197: if not per_bil_shd.chk_date_valid(p_rec => p_rec) then
198: fnd_message.set_name('PER','HR_51155_INVAL_DATE_FORMAT');
199: fnd_message.raise_error;
200: end if;
201: end if;
200: end if;
201: end if;
202: --
203: if p_rec.type = 'RESTRICTION_VALUE' then
204: per_bil_shd.valid_value(p_rec => p_rec);
205: end if;
206: --
207: hr_utility.set_location(' Leaving:'||l_proc, 10);
208: --
210: --
211: -- ----------------------------------------------------------------------------
212: -- |---------------------------< delete_validate >----------------------------|
213: -- ----------------------------------------------------------------------------
214: Procedure delete_validate(p_rec in per_bil_shd.g_rec_type) is
215: --
216: l_proc varchar2(72) := g_package||'delete_validate';
217: l_type hr_summary.type%type;
218: --
230: fetch csr_check_children into l_type;
231: if csr_check_children%found then
232: close csr_check_children;
233: if l_type = 'ITEM_TYPE_USAGE' then
234: per_bil_shd.constraint_error('CHILD_RECORD_ITU');
235: elsif l_type = 'VALID_RESTRICTION' then
236: per_bil_shd.constraint_error('CHILD_RECORD_VR');
237: elsif l_type = 'RESTRICTION_USAGE' then
238: per_bil_shd.constraint_error('CHILD_RECORD_RTU');
232: close csr_check_children;
233: if l_type = 'ITEM_TYPE_USAGE' then
234: per_bil_shd.constraint_error('CHILD_RECORD_ITU');
235: elsif l_type = 'VALID_RESTRICTION' then
236: per_bil_shd.constraint_error('CHILD_RECORD_VR');
237: elsif l_type = 'RESTRICTION_USAGE' then
238: per_bil_shd.constraint_error('CHILD_RECORD_RTU');
239: elsif l_type = 'KEY_TYPE_USAGE' then
240: per_bil_shd.constraint_error('CHILD_RECORD_KTU');
234: per_bil_shd.constraint_error('CHILD_RECORD_ITU');
235: elsif l_type = 'VALID_RESTRICTION' then
236: per_bil_shd.constraint_error('CHILD_RECORD_VR');
237: elsif l_type = 'RESTRICTION_USAGE' then
238: per_bil_shd.constraint_error('CHILD_RECORD_RTU');
239: elsif l_type = 'KEY_TYPE_USAGE' then
240: per_bil_shd.constraint_error('CHILD_RECORD_KTU');
241: elsif l_type = 'RESTRICTION_VALUE' then
242: per_bil_shd.constraint_error('CHILD_RECORD_RV');
236: per_bil_shd.constraint_error('CHILD_RECORD_VR');
237: elsif l_type = 'RESTRICTION_USAGE' then
238: per_bil_shd.constraint_error('CHILD_RECORD_RTU');
239: elsif l_type = 'KEY_TYPE_USAGE' then
240: per_bil_shd.constraint_error('CHILD_RECORD_KTU');
241: elsif l_type = 'RESTRICTION_VALUE' then
242: per_bil_shd.constraint_error('CHILD_RECORD_RV');
243: elsif l_type = 'ITEM_VALUE' then
244: per_bil_shd.constraint_error('CHILD_RECORD_IV');
238: per_bil_shd.constraint_error('CHILD_RECORD_RTU');
239: elsif l_type = 'KEY_TYPE_USAGE' then
240: per_bil_shd.constraint_error('CHILD_RECORD_KTU');
241: elsif l_type = 'RESTRICTION_VALUE' then
242: per_bil_shd.constraint_error('CHILD_RECORD_RV');
243: elsif l_type = 'ITEM_VALUE' then
244: per_bil_shd.constraint_error('CHILD_RECORD_IV');
245: elsif l_type = 'KEY_VALUE' then
246: per_bil_shd.constraint_error('CHILD_RECORD_KV');
240: per_bil_shd.constraint_error('CHILD_RECORD_KTU');
241: elsif l_type = 'RESTRICTION_VALUE' then
242: per_bil_shd.constraint_error('CHILD_RECORD_RV');
243: elsif l_type = 'ITEM_VALUE' then
244: per_bil_shd.constraint_error('CHILD_RECORD_IV');
245: elsif l_type = 'KEY_VALUE' then
246: per_bil_shd.constraint_error('CHILD_RECORD_KV');
247: elsif l_type = 'VALID_KEY_TYPE' then
248: per_bil_shd.constraint_error('CHILD_RECORD_VKT');
242: per_bil_shd.constraint_error('CHILD_RECORD_RV');
243: elsif l_type = 'ITEM_VALUE' then
244: per_bil_shd.constraint_error('CHILD_RECORD_IV');
245: elsif l_type = 'KEY_VALUE' then
246: per_bil_shd.constraint_error('CHILD_RECORD_KV');
247: elsif l_type = 'VALID_KEY_TYPE' then
248: per_bil_shd.constraint_error('CHILD_RECORD_VKT');
249: elsif l_type = 'PROCESS_RUN' then
250: per_bil_shd.constraint_error('CHILD_RECORD_PR');
244: per_bil_shd.constraint_error('CHILD_RECORD_IV');
245: elsif l_type = 'KEY_VALUE' then
246: per_bil_shd.constraint_error('CHILD_RECORD_KV');
247: elsif l_type = 'VALID_KEY_TYPE' then
248: per_bil_shd.constraint_error('CHILD_RECORD_VKT');
249: elsif l_type = 'PROCESS_RUN' then
250: per_bil_shd.constraint_error('CHILD_RECORD_PR');
251: end if;
252: else
246: per_bil_shd.constraint_error('CHILD_RECORD_KV');
247: elsif l_type = 'VALID_KEY_TYPE' then
248: per_bil_shd.constraint_error('CHILD_RECORD_VKT');
249: elsif l_type = 'PROCESS_RUN' then
250: per_bil_shd.constraint_error('CHILD_RECORD_PR');
251: end if;
252: else
253: close csr_check_children;
254: end if;