1: PACKAGE BODY HZ_DSS_ASSIGNMENTS_PKG AS
2: /* $Header: ARHPDSAB.pls 115.2 2002/10/10 01:41:46 chsaulit noship $ */
3:
4: PROCEDURE Insert_Row (
5: x_rowid IN OUT NOCOPY VARCHAR2,
20: BEGIN
21:
22: WHILE l_success = 'N' LOOP
23: BEGIN
24: INSERT INTO HZ_DSS_ASSIGNMENTS (
25: assignment_id,
26: status,
27: owner_table_name,
28: owner_table_id1,
39: object_version_number
40: )
41: VALUES (
42: DECODE(x_assignment_id,
43: FND_API.G_MISS_NUM, HZ_DSS_ASSIGNMENTS_S.NEXTVAL,
44: NULL, HZ_DSS_ASSIGNMENTS_S.NEXTVAL,
45: x_assignment_id),
46: DECODE(x_status,
47: FND_API.G_MISS_CHAR, 'A',
40: )
41: VALUES (
42: DECODE(x_assignment_id,
43: FND_API.G_MISS_NUM, HZ_DSS_ASSIGNMENTS_S.NEXTVAL,
44: NULL, HZ_DSS_ASSIGNMENTS_S.NEXTVAL,
45: x_assignment_id),
46: DECODE(x_status,
47: FND_API.G_MISS_CHAR, 'A',
48: NULL, 'A',
86: l_success := 'Y';
87:
88: EXCEPTION
89: WHEN DUP_VAL_ON_INDEX THEN
90: IF INSTR(SQLERRM, 'HZ_DSS_ASSIGNMENTS_U1') <> 0 THEN
91: DECLARE
92: l_count NUMBER;
93: l_dummy VARCHAR2(1);
94: BEGIN
93: l_dummy VARCHAR2(1);
94: BEGIN
95: l_count := 1;
96: WHILE l_count > 0 LOOP
97: SELECT HZ_DSS_ASSIGNMENTS_S.NEXTVAL
98: INTO x_assignment_id FROM dual;
99: BEGIN
100: SELECT 'Y' INTO l_dummy
101: FROM HZ_DSS_ASSIGNMENTS
97: SELECT HZ_DSS_ASSIGNMENTS_S.NEXTVAL
98: INTO x_assignment_id FROM dual;
99: BEGIN
100: SELECT 'Y' INTO l_dummy
101: FROM HZ_DSS_ASSIGNMENTS
102: WHERE assignment_id = x_assignment_id;
103: l_count := 1;
104: EXCEPTION
105: WHEN NO_DATA_FOUND THEN
127: x_object_version_number IN NUMBER
128: ) IS
129: BEGIN
130:
131: UPDATE HZ_DSS_ASSIGNMENTS
132: SET
133: status =
134: DECODE(x_status,
135: NULL, status,
207: x_object_version_number IN NUMBER
208: ) IS
209:
210: CURSOR c IS
211: SELECT * FROM hz_dss_assignments
212: WHERE rowid = x_rowid
213: FOR UPDATE NOWAIT;
214: Recinfo c%ROWTYPE;
215:
315: x_owner_table_id4,
316: x_owner_table_id5,
317: x_dss_group_code,
318: x_object_version_number
319: FROM HZ_DSS_ASSIGNMENTS
320: WHERE assignment_id = x_assignment_id;
321:
322: EXCEPTION
323: WHEN NO_DATA_FOUND THEN
333: x_assignment_id IN NUMBER
334: ) IS
335: BEGIN
336:
337: DELETE FROM HZ_DSS_ASSIGNMENTS
338: WHERE assignment_id = x_assignment_id;
339:
340: IF ( SQL%NOTFOUND ) THEN
341: RAISE NO_DATA_FOUND;
342: END IF;
343:
344: END Delete_Row;
345:
346: END HZ_DSS_ASSIGNMENTS_PKG;