[Home] [Help]
PACKAGE BODY: APPS.HZ_MATCH_RULE_SECONDARY_PKG
Source
1 PACKAGE BODY HZ_MATCH_RULE_SECONDARY_PKG AS
2 /*$Header: ARHDQMSB.pls 120.6 2005/10/30 04:19:12 appldev noship $ */
3 PROCEDURE Insert_Row(
4 px_SECONDARY_ATTRIBUTE_ID IN OUT NOCOPY NUMBER,
5 p_MATCH_RULE_ID NUMBER,
6 p_ATTRIBUTE_ID NUMBER,
7 p_SCORE NUMBER,
8 p_ACTIVE_FLAG VARCHAR2,
9 p_CREATED_BY NUMBER,
10 p_CREATION_DATE DATE,
11 p_LAST_UPDATE_LOGIN NUMBER,
12 p_LAST_UPDATE_DATE DATE,
13 p_LAST_UPDATED_BY NUMBER,
14 p_OBJECT_VERSION_NUMBER NUMBER,
15 p_DISPLAY_ORDER NUMBER DEFAULT NULL)
16 IS
17 CURSOR C2 IS SELECT HZ_MATCH_RULE_SECONDARY_s.nextval FROM sys.dual;
18 l_success VARCHAR2(1) ;
19
20 BEGIN
21 l_success := 'N';
22 WHILE l_success = 'N' LOOP
23 BEGIN
24 IF ( px_SECONDARY_ATTRIBUTE_ID IS NULL) OR (px_SECONDARY_ATTRIBUTE_ID = FND_API.G_MISS_NUM) THEN
25 OPEN C2;
26 FETCH C2 INTO px_SECONDARY_ATTRIBUTE_ID;
27 CLOSE C2;
28 END IF;
29
30 INSERT INTO HZ_MATCH_RULE_SECONDARY(
31 SECONDARY_ATTRIBUTE_ID,
32 MATCH_RULE_ID,
33 ATTRIBUTE_ID,
34 SCORE,
35 ACTIVE_FLAG,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATE_LOGIN,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 OBJECT_VERSION_NUMBER,
42 DISPLAY_ORDER
43 )
44 VALUES (
45 px_SECONDARY_ATTRIBUTE_ID,
46 decode(p_MATCH_RULE_ID, FND_API.G_MISS_NUM, NULL, p_MATCH_RULE_ID),
47 decode(p_ATTRIBUTE_ID, FND_API.G_MISS_NUM, NULL, p_ATTRIBUTE_ID),
48 decode(p_SCORE, FND_API.G_MISS_NUM, NULL, p_SCORE),
49 decode(p_ACTIVE_FLAG, FND_API.G_MISS_CHAR, NULL, p_ACTIVE_FLAG),
50 decode(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by),
51 decode(p_creation_date, FND_API.G_MISS_DATE, NULL, p_creation_date),
52 decode(p_last_update_login,FND_API.G_MISS_NUM, NULL, p_last_update_login),
53 decode(p_last_update_date, FND_API.G_MISS_DATE, NULL, p_last_update_date),
54 decode(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by),
55 1,
56 decode(p_DISPLAY_ORDER, FND_API.G_MISS_NUM, NULL, p_DISPLAY_ORDER)
57 );
58
59 l_success := 'Y';
60 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
61 IF INSTRB( SQLERRM, 'HZ_MATCH_RULE_SECONDARY_U1' ) <> 0 THEN
62 DECLARE
63 l_count NUMBER;
64 l_dummy VARCHAR2(1);
65 BEGIN
66 l_count := 1;
67 WHILE l_count > 0 LOOP
68 SELECT HZ_MATCH_RULE_SECONDARY_s.nextval
69 into px_SECONDARY_ATTRIBUTE_ID FROM sys.dual;
70 BEGIN
71 SELECT 'Y' INTO l_dummy
72 FROM HZ_MATCH_RULE_SECONDARY
73 WHERE SECONDARY_ATTRIBUTE_ID = px_SECONDARY_ATTRIBUTE_ID;
74 l_count := 1;
75 EXCEPTION WHEN NO_DATA_FOUND THEN
76 l_count := 0;
77 END;
78 END LOOP;
79 END;
80 END IF;
81 END;
82 END LOOP;
83
84 End Insert_Row;
85
86 PROCEDURE Update_Row(
87 p_SECONDARY_ATTRIBUTE_ID NUMBER,
88 p_MATCH_RULE_ID NUMBER,
89 p_ATTRIBUTE_ID NUMBER,
90 p_SCORE NUMBER,
91 p_ACTIVE_FLAG VARCHAR2,
92 p_CREATED_BY NUMBER,
93 p_CREATION_DATE DATE,
94 p_LAST_UPDATE_LOGIN NUMBER,
95 p_LAST_UPDATE_DATE DATE,
96 p_LAST_UPDATED_BY NUMBER,
97 p_OBJECT_VERSION_NUMBER in out NOCOPY NUMBER,
98 p_DISPLAY_ORDER NUMBER DEFAULT NULL
99 ) IS
100
101 l_object_version_number number;
102
103 BEGIN
104
105 l_object_version_number := NVL(p_object_version_number, 1) + 1;
106
107 UPDATE hz_match_rule_SECONDARY
108 SET
109 MATCH_RULE_ID = decode(p_MATCH_RULE_ID, FND_API.G_MISS_NUM, NULL, p_MATCH_RULE_ID),
110 ATTRIBUTE_ID = decode(p_ATTRIBUTE_ID, FND_API.G_MISS_NUM, NULL, p_ATTRIBUTE_ID),
111 SCORE = decode(p_SCORE, FND_API.G_MISS_CHAR, NULL, p_SCORE),
112 ACTIVE_FLAG = decode(p_ACTIVE_FLAG, FND_API.G_MISS_CHAR, NULL, p_ACTIVE_FLAG),
113 -- Bug 3032780
114 /*
115 CREATED_BY = decode(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by),
116 CREATION_DATE = decode(p_CREATION_DATE, FND_API.G_MISS_DATE, NULL,p_CREATION_DATE),
117 */
118 LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM, NULL,p_LAST_UPDATE_LOGIN),
119 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,p_LAST_UPDATE_DATE),
120 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,p_LAST_UPDATED_BY),
121 OBJECT_VERSION_NUMBER = decode(l_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER,l_object_version_number),
122 DISPLAY_ORDER = decode(p_DISPLAY_ORDER, FND_API.G_MISS_NUM, DISPLAY_ORDER,p_DISPLAY_ORDER)
123 WHERE SECONDARY_attribute_id = p_SECONDARY_attribute_id;
124
125 p_OBJECT_VERSION_NUMBER := l_OBJECT_VERSION_NUMBER;
126
127 If (SQL%NOTFOUND) then
128 RAISE NO_DATA_FOUND;
129 End If;
130 END Update_Row;
131
132 PROCEDURE Delete_Row(p_SECONDARY_attribute_id NUMBER) IS
133 BEGIN
134 DELETE FROM HZ_MATCH_RULE_SECONDARY
135 WHERE SECONDARY_attribute_id = p_SECONDARY_attribute_id;
136
137 If (SQL%NOTFOUND) then
138 RAISE NO_DATA_FOUND;
139 End If;
140 END Delete_Row;
141
142 PROCEDURE Lock_Row(
143 p_SECONDARY_ATTRIBUTE_ID IN OUT NOCOPY NUMBER,
144 p_OBJECT_VERSION_NUMBER IN NUMBER
145 ) IS
146 CURSOR C IS
147
148 SELECT OBJECT_VERSION_NUMBER
149 FROM hz_match_rule_SECONDARY
150 WHERE SECONDARY_attribute_id = p_SECONDARY_attribute_id
151 FOR UPDATE OF SECONDARY_attribute_id NOWAIT;
152 Recinfo C%ROWTYPE;
153 BEGIN
154 OPEN C;
155 FETCH C INTO Recinfo;
156 If (C%NOTFOUND) then
157 CLOSE C;
158 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
159 APP_EXCEPTION.RAISE_EXCEPTION;
160 End If;
161 CLOSE C;
162
163 if (
164 ( Recinfo.OBJECT_VERSION_NUMBER IS NOT NULL AND p_OBJECT_VERSION_NUMBER IS NOT NULL
165 AND Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER )
166 OR ((Recinfo.OBJECT_VERSION_NUMBER IS NULL)AND (p_OBJECT_VERSION_NUMBER IS NULL ))
167
168 ) then
169 return;
170 else
171 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
172 APP_EXCEPTION.RAISE_EXCEPTION;
173 End If;
174
175 END Lock_Row;
176
177 END HZ_MATCH_RULE_SECONDARY_PKG;