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