1 PACKAGE BODY IEX_PAY_OKL_XREF_PKG AS
2 /* $Header: iextpoxb.pls 120.0 2004/01/24 03:22:27 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_PAY_OKL_XREF_ID NUMBER DEFAULT NULL
9 ,p_OKL_PAYMENT_ID NUMBER DEFAULT NULL
10 ,p_CNSLD_INVOICE_ID NUMBER DEFAULT NULL
11 ,p_CONTRACT_ID NUMBER DEFAULT NULL
12 ,p_LAST_UPDATE_DATE DATE
13 ,p_SECURITY_GROUP_ID NUMBER DEFAULT NULL
14 ,p_PAYMENT_ID NUMBER DEFAULT NULL
15 ,p_LAST_UPDATED_BY NUMBER
16 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
17 ,p_CREATION_DATE DATE
18 ,p_CREATED_BY NUMBER
19 ,p_PROGRAM_ID NUMBER DEFAULT NULL
20 ,p_OBJECT_VERSION_NUMBER NUMBER
21 ,p_REQUEST_ID NUMBER DEFAULT NULL
22 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
23 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
24 ) IS
25 CURSOR C IS SELECT ROWID FROM IEX_PAY_OKL_XREF
26 WHERE PAY_OKL_XREF_ID = p_PAY_OKL_XREF_ID;
27
28 BEGIN
29 INSERT INTO IEX_PAY_OKL_XREF
30 (
31 PAY_OKL_XREF_ID
32 ,OKL_PAYMENT_ID
33 ,CNSLD_INVOICE_ID
34 ,CONTRACT_ID
35 ,LAST_UPDATE_DATE
36 ,SECURITY_GROUP_ID
37 ,PAYMENT_ID
38 ,LAST_UPDATED_BY
39 ,LAST_UPDATE_LOGIN
40 ,CREATION_DATE
41 ,CREATED_BY
42 ,PROGRAM_ID
43 ,OBJECT_VERSION_NUMBER
44 ,REQUEST_ID
45 ,PROGRAM_APPLICATION_ID
46 ,PROGRAM_UPDATE_DATE
47 ) VALUES (
48 p_PAY_OKL_XREF_ID
49 ,p_OKL_PAYMENT_ID
50 ,p_CNSLD_INVOICE_ID
51 ,p_CONTRACT_ID
52 ,p_LAST_UPDATE_DATE
53 ,p_SECURITY_GROUP_ID
54 ,p_PAYMENT_ID
55 ,p_LAST_UPDATED_BY
56 ,p_LAST_UPDATE_LOGIN
57 ,p_CREATION_DATE
58 ,p_CREATED_BY
59 ,p_PROGRAM_ID
60 ,p_OBJECT_VERSION_NUMBER
61 ,p_REQUEST_ID
62 ,p_PROGRAM_APPLICATION_ID
63 ,p_PROGRAM_UPDATE_DATE
64 );
65
66 OPEN C;
67 FETCH C INTO x_rowid;
68 IF (C%NOTFOUND) THEN
69 CLOSE C;
70 RAISE NO_DATA_FOUND;
71 END IF;
72 CLOSE C;
73 END Insert_Row;
74
75 /* Update_Row procedure */
76 PROCEDURE Update_Row(x_rowid VARCHAR2
77 ,p_PAY_OKL_XREF_ID NUMBER DEFAULT NULL
78 ,p_OKL_PAYMENT_ID NUMBER DEFAULT NULL
79 ,p_CNSLD_INVOICE_ID NUMBER DEFAULT NULL
80 ,p_CONTRACT_ID NUMBER DEFAULT NULL
81 ,p_LAST_UPDATE_DATE DATE
82 ,p_SECURITY_GROUP_ID NUMBER DEFAULT NULL
83 ,p_PAYMENT_ID NUMBER DEFAULT NULL
84 ,p_LAST_UPDATED_BY NUMBER
85 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
86 ,p_CREATION_DATE DATE
87 ,p_CREATED_BY NUMBER
88 ,p_PROGRAM_ID NUMBER DEFAULT NULL
89 ,p_OBJECT_VERSION_NUMBER NUMBER
90 ,p_REQUEST_ID NUMBER DEFAULT NULL
91 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
92 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
93 ) IS
94 BEGIN
95 UPDATE IEX_PAY_OKL_XREF SET
96 PAY_OKL_XREF_ID = p_PAY_OKL_XREF_ID
97 ,OKL_PAYMENT_ID = p_OKL_PAYMENT_ID
98 ,CNSLD_INVOICE_ID = p_CNSLD_INVOICE_ID
99 ,CONTRACT_ID = p_CONTRACT_ID
100 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
101 ,SECURITY_GROUP_ID = p_SECURITY_GROUP_ID
102 ,PAYMENT_ID = p_PAYMENT_ID
103 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
104 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
105 ,CREATION_DATE = p_CREATION_DATE
106 ,CREATED_BY = p_CREATED_BY
107 ,PROGRAM_ID = p_PROGRAM_ID
108 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
109 ,REQUEST_ID = p_REQUEST_ID
110 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
111 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
112 WHERE rowid = x_rowid;
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117 END Update_Row;
118
119 /* Delete_Row procedure */
120 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
121 BEGIN
122 DELETE FROM IEX_PAY_OKL_XREF
123 WHERE rowid = x_rowid;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128 END Delete_Row;
129
130 /* Lock_Row procedure */
131 PROCEDURE Lock_Row(x_rowid VARCHAR2
132 ,p_PAY_OKL_XREF_ID NUMBER DEFAULT NULL
133 ,p_OKL_PAYMENT_ID NUMBER DEFAULT NULL
134 ,p_CNSLD_INVOICE_ID NUMBER DEFAULT NULL
135 ,p_CONTRACT_ID NUMBER DEFAULT NULL
136 ,p_LAST_UPDATE_DATE DATE
137 ,p_SECURITY_GROUP_ID NUMBER DEFAULT NULL
138 ,p_PAYMENT_ID NUMBER DEFAULT NULL
139 ,p_LAST_UPDATED_BY NUMBER
140 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
141 ,p_CREATION_DATE DATE
142 ,p_CREATED_BY NUMBER
143 ,p_PROGRAM_ID NUMBER DEFAULT NULL
144 ,p_OBJECT_VERSION_NUMBER NUMBER
145 ,p_REQUEST_ID NUMBER DEFAULT NULL
146 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
147 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
148 ) IS
149 CURSOR C IS SELECT * FROM IEX_PAY_OKL_XREF
150 WHERE rowid = x_rowid
151 FOR UPDATE of PAY_OKL_XREF_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.PAY_OKL_XREF_ID = p_PAY_OKL_XREF_ID)
165 AND ( (Recinfo.OKL_PAYMENT_ID = p_OKL_PAYMENT_ID)
166 OR ( (Recinfo.OKL_PAYMENT_ID IS NULL)
167 AND (p_OKL_PAYMENT_ID IS NULL)))
168 AND ( (Recinfo.CNSLD_INVOICE_ID = p_CNSLD_INVOICE_ID)
169 OR ( (Recinfo.CNSLD_INVOICE_ID IS NULL)
170 AND (p_CNSLD_INVOICE_ID IS NULL)))
171 AND ( (Recinfo.CONTRACT_ID = p_CONTRACT_ID)
172 OR ( (Recinfo.CONTRACT_ID IS NULL)
173 AND (p_CONTRACT_ID IS NULL)))
174 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
175 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
176 AND (p_LAST_UPDATE_DATE 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.PAYMENT_ID = p_PAYMENT_ID)
181 OR ( (Recinfo.PAYMENT_ID IS NULL)
182 AND (p_PAYMENT_ID IS NULL)))
183 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
184 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
185 AND (p_LAST_UPDATED_BY IS NULL)))
186 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
187 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
188 AND (p_LAST_UPDATE_LOGIN IS NULL)))
189 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
190 OR ( (Recinfo.CREATION_DATE IS NULL)
191 AND (p_CREATION_DATE IS NULL)))
192 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
193 OR ( (Recinfo.CREATED_BY IS NULL)
194 AND (p_CREATED_BY IS NULL)))
195 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
196 OR ( (Recinfo.PROGRAM_ID IS NULL)
197 AND (p_PROGRAM_ID IS NULL)))
198 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
199 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
200 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
201 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
202 OR ( (Recinfo.REQUEST_ID IS NULL)
203 AND (p_REQUEST_ID IS NULL)))
204 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
205 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
206 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
207 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
208 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
209 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
210 ) THEN
211 return;
212 ELSE
213 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
214 APP_EXCEPTION.Raise_Exception;
215 END IF;
216 END Lock_Row;
217 END;
218