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