1 PACKAGE BODY IEX_STATUS_RULE_PKG AS
2 /* $Header: iextcstb.pls 120.0 2004/01/24 03:21:45 appldev noship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
6
7 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
8 ,p_STATUS_RULE_ID NUMBER
9 ,p_STATUS_RULE_NAME VARCHAR2
10 ,p_STATUS_RULE_DESCRIPTION VARCHAR2
11 ,p_START_DATE DATE
12 ,p_END_DATE DATE
13 -- ,p_JTF_OBJECT_CODE VARCHAR2
14 ,p_LAST_UPDATE_DATE DATE
15 ,p_LAST_UPDATED_BY NUMBER
16 ,p_CREATION_DATE DATE
17 ,p_CREATED_BY NUMBER
18 ,p_LAST_UPDATE_LOGIN NUMBER
19 ,p_SECURITY_GROUP_ID NUMBER
20 ,p_PROGRAM_ID NUMBER
21 ,p_OBJECT_VERSION_NUMBER NUMBER
22 ) IS
23 CURSOR C IS SELECT ROWID FROM IEX_CUST_STATUS_RULES
24 WHERE STATUS_RULE_ID = p_STATUS_RULE_ID;
25
26 BEGIN
27 INSERT INTO IEX_CUST_STATUS_RULES
28 (
29 STATUS_RULE_ID
30 ,STATUS_RULE_NAME
31 ,STATUS_RULE_DESCRIPTION
32 ,START_DATE
33 ,END_DATE
34 -- ,JTF_OBJECT_CODE
35 ,LAST_UPDATE_DATE
36 ,LAST_UPDATED_BY
37 ,CREATION_DATE
38 ,CREATED_BY
39 ,LAST_UPDATE_LOGIN
40 ,SECURITY_GROUP_ID
41 ,PROGRAM_ID
42 ,OBJECT_VERSION_NUMBER
43 ) VALUES (
44 p_STATUS_RULE_ID
45 ,p_STATUS_RULE_NAME
46 ,p_STATUS_RULE_DESCRIPTION
47 ,p_START_DATE
48 ,p_END_DATE
49 -- ,p_JTF_OBJECT_CODE
50 ,p_LAST_UPDATE_DATE
51 ,p_LAST_UPDATED_BY
52 ,p_CREATION_DATE
53 ,p_CREATED_BY
54 ,p_LAST_UPDATE_LOGIN
55 ,p_SECURITY_GROUP_ID
56 ,p_PROGRAM_ID
57 ,p_OBJECT_VERSION_NUMBER
58 );
59
60 OPEN C;
61 FETCH C INTO x_rowid;
62 IF (C%NOTFOUND) THEN
63 CLOSE C;
64 RAISE NO_DATA_FOUND;
65 END IF;
66 CLOSE C;
67 END Insert_Row;
68
69 /* Update_Row procedure */
70 PROCEDURE Update_Row(x_rowid VARCHAR2
71 ,p_STATUS_RULE_ID NUMBER
72 ,p_STATUS_RULE_NAME VARCHAR2
73 ,p_STATUS_RULE_DESCRIPTION VARCHAR2
74 ,p_START_DATE DATE
75 ,p_END_DATE DATE
76 -- ,p_JTF_OBJECT_CODE VARCHAR2
77 ,p_LAST_UPDATE_DATE DATE
78 ,p_LAST_UPDATED_BY NUMBER
79 ,p_CREATION_DATE DATE
80 ,p_CREATED_BY NUMBER
81 ,p_LAST_UPDATE_LOGIN NUMBER
82 ,p_SECURITY_GROUP_ID NUMBER
83 ,p_PROGRAM_ID NUMBER
84 ,p_OBJECT_VERSION_NUMBER NUMBER
85 ) IS
86 BEGIN
87 UPDATE IEX_CUST_STATUS_RULES SET
88 STATUS_RULE_ID = p_STATUS_RULE_ID
89 ,STATUS_RULE_NAME = p_STATUS_RULE_NAME
90 ,STATUS_RULE_DESCRIPTION = p_STATUS_RULE_DESCRIPTION
91 ,START_DATE = p_START_DATE
92 ,END_DATE = p_END_DATE
93 -- ,JTF_OBJECT_CODE = p_JTF_OBJECT_CODE
94 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
95 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
96 ,CREATION_DATE = p_CREATION_DATE
97 ,CREATED_BY = p_CREATED_BY
98 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
99 ,SECURITY_GROUP_ID = p_SECURITY_GROUP_ID
100 ,PROGRAM_ID = p_PROGRAM_ID
101 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
102 WHERE rowid = x_rowid;
103
104 if (sql%notfound) then
105 raise no_data_found;
106 end if;
107 END Update_Row;
108
109 /* Delete_Row procedure */
110 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
111 BEGIN
112 DELETE FROM IEX_CUST_STATUS_RULES
113 WHERE rowid = x_rowid;
114
115 if (sql%notfound) then
116 raise no_data_found;
117 end if;
118 END Delete_Row;
119
120 /* Lock_Row procedure */
121 PROCEDURE Lock_Row(x_rowid VARCHAR2
122 ,p_STATUS_RULE_ID NUMBER
123 ,p_STATUS_RULE_NAME VARCHAR2
124 ,p_STATUS_RULE_DESCRIPTION VARCHAR2
125 ,p_START_DATE DATE
126 ,p_END_DATE DATE
127 -- ,p_JTF_OBJECT_CODE VARCHAR2
128 ,p_LAST_UPDATE_DATE DATE
129 ,p_LAST_UPDATED_BY NUMBER
130 ,p_CREATION_DATE DATE
131 ,p_CREATED_BY NUMBER
132 ,p_LAST_UPDATE_LOGIN NUMBER
133 ,p_PROGRAM_ID NUMBER
134 ,p_SECURITY_GROUP_ID NUMBER
135 ,p_OBJECT_VERSION_NUMBER NUMBER
136 ) IS
137 CURSOR C IS SELECT * FROM IEX_CUST_STATUS_RULES
138 WHERE rowid = x_rowid
139 FOR UPDATE of STATUS_RULE_ID NOWAIT;
140 Recinfo C%ROWTYPE;
141 BEGIN
142 OPEN C;
143 FETCH C INTO Recinfo;
144 IF (C%NOTFOUND) THEN
145 CLOSE C;
146 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
147 APP_EXCEPTION.Raise_Exception;
148 END IF;
149 CLOSE C;
150
151 IF (
152 (Recinfo.STATUS_RULE_ID = p_STATUS_RULE_ID)
153 AND ( (Recinfo.STATUS_RULE_NAME = p_STATUS_RULE_NAME)
154 OR ( (Recinfo.STATUS_RULE_NAME IS NULL)
155 AND (p_STATUS_RULE_NAME IS NULL)))
156 AND ( (Recinfo.STATUS_RULE_DESCRIPTION = p_STATUS_RULE_DESCRIPTION)
157 OR ( (Recinfo.STATUS_RULE_DESCRIPTION IS NULL)
158 AND (p_STATUS_RULE_DESCRIPTION IS NULL)))
159 AND ( (Recinfo.START_DATE = p_START_DATE)
160 OR ( (Recinfo.START_DATE IS NULL)
161 AND (p_START_DATE IS NULL)))
162 AND ( (Recinfo.END_DATE = p_END_DATE)
163 OR ( (Recinfo.END_DATE IS NULL)
164 AND (p_END_DATE IS NULL)))
165 -- AND ( (Recinfo.JTF_OBJECT_CODE = p_JTF_OBJECT_CODE)
166 -- OR ( (Recinfo.JTF_OBJECT_CODE IS NULL)
167 -- AND (p_JTF_OBJECT_CODE IS NULL)))
168 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
169 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
170 AND (p_LAST_UPDATE_DATE IS NULL)))
171 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
172 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
173 AND (p_LAST_UPDATED_BY IS NULL)))
174 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
175 OR ( (Recinfo.CREATION_DATE IS NULL)
176 AND (p_CREATION_DATE IS NULL)))
177 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
178 OR ( (Recinfo.CREATED_BY IS NULL)
179 AND (p_CREATED_BY IS NULL)))
180 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
181 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
182 AND (p_LAST_UPDATE_LOGIN IS NULL)))
183 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
184 OR ( (Recinfo.PROGRAM_ID IS NULL)
185 AND (p_PROGRAM_ID IS NULL)))
186 AND ( (Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
187 OR ( (Recinfo.SECURITY_GROUP_ID IS NULL)
188 AND (p_SECURITY_GROUP_ID IS NULL)))
189 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
190 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
191 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
192 ) THEN
193 return;
194 ELSE
195 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
196 APP_EXCEPTION.Raise_Exception;
197 END IF;
198 END Lock_Row;
199 END;
200
[Home] [Help]