1 PACKAGE BODY JTF_EXCEPTIONS_PKG AS
2 /* $Header: jtfvtveb.pls 115.3 2000/02/29 18:26:26 pkm ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_Rowid IN OUT VARCHAR2,
7 x_EXCEPTIONS_ID IN OUT NUMBER,
8 x_LAST_UPDATE_DATE IN DATE,
9 x_LAST_UPDATED_BY IN NUMBER,
10 x_CREATION_DATE IN DATE,
11 x_CREATED_BY IN NUMBER,
12 x_LAST_UPDATE_LOGIN IN NUMBER,
13 x_TERR_ID IN NUMBER,
14 x_RESOURCE_ID IN NUMBER,
15 x_CUSTOMER_ID IN NUMBER,
16 x_ADDRESS_ID IN NUMBER,
17 x_LEAD_ID IN NUMBER,
18 x_OPPORTUNITY_ID IN NUMBER,
19 x_ORG_ID IN NUMBER
20 ) IS
21 CURSOR C IS SELECT rowid FROM JTF_EXCEPTIONS
22 WHERE EXCEPTIONS_ID = x_EXCEPTIONS_ID;
23 CURSOR C2 IS SELECT JTF_EXCEPTIONS_s.nextval FROM sys.dual;
24 BEGIN
25 If (x_EXCEPTIONS_ID IS NULL) then
26 OPEN C2;
27 FETCH C2 INTO x_EXCEPTIONS_ID;
28 CLOSE C2;
29 End If;
30 INSERT INTO JTF_EXCEPTIONS(
31 EXCEPTIONS_ID,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_LOGIN,
37 TERR_ID,
38 RESOURCE_ID,
39 CUSTOMER_ID,
40 ADDRESS_ID,
41 LEAD_ID,
42 OPPORTUNITY_ID,
43 ORG_ID
44 ) VALUES (
45 x_EXCEPTIONS_ID,
46 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
47 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
48 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
49 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
50 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
51 decode( x_TERR_ID, FND_API.G_MISS_NUM, NULL,x_TERR_ID),
52 decode( x_RESOURCE_ID, FND_API.G_MISS_NUM, NULL,x_RESOURCE_ID),
53 decode( x_CUSTOMER_ID, FND_API.G_MISS_NUM, NULL,x_CUSTOMER_ID),
54 decode( x_ADDRESS_ID, FND_API.G_MISS_NUM, NULL,x_ADDRESS_ID),
55 decode( x_LEAD_ID, FND_API.G_MISS_NUM, NULL,x_LEAD_ID),
56 decode( x_OPPORTUNITY_ID, FND_API.G_MISS_NUM, NULL,x_OPPORTUNITY_ID),
57 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID));
58 OPEN C;
59 FETCH C INTO x_Rowid;
60 If (C%NOTFOUND) then
61 CLOSE C;
62 RAISE NO_DATA_FOUND;
63 End If;
64 End Insert_Row;
65
66
67
68 PROCEDURE Delete_Row( x_EXCEPTIONS_ID IN NUMBER
69 ) IS
70 BEGIN
71 DELETE FROM JTF_EXCEPTIONS
72 WHERE EXCEPTIONS_ID = x_EXCEPTIONS_ID;
73 If (SQL%NOTFOUND) then
74 RAISE NO_DATA_FOUND;
75 End If;
76 END Delete_Row;
77
78
79
80 PROCEDURE Update_Row(
81 x_Rowid IN VARCHAR2,
82 x_EXCEPTIONS_ID IN NUMBER,
83 x_LAST_UPDATE_DATE IN DATE,
84 x_LAST_UPDATED_BY IN NUMBER,
85 x_CREATION_DATE IN DATE,
86 x_CREATED_BY IN NUMBER,
87 x_LAST_UPDATE_LOGIN IN NUMBER,
88 x_TERR_ID IN NUMBER,
89 x_RESOURCE_ID IN NUMBER,
90 x_CUSTOMER_ID IN NUMBER,
91 x_ADDRESS_ID IN NUMBER,
92 x_LEAD_ID IN NUMBER,
93 x_OPPORTUNITY_ID IN NUMBER,
94 x_ORG_ID IN NUMBER
95 ) IS
96 BEGIN
97 Update JTF_EXCEPTIONS
98 SET
99 EXCEPTIONS_ID = decode( x_EXCEPTIONS_ID, FND_API.G_MISS_NUM,EXCEPTIONS_ID,x_EXCEPTIONS_ID),
100 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
101 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
102 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
103 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
104 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
105 TERR_ID = decode( x_TERR_ID, FND_API.G_MISS_NUM,TERR_ID,x_TERR_ID),
106 RESOURCE_ID = decode( x_RESOURCE_ID, FND_API.G_MISS_NUM,RESOURCE_ID,x_RESOURCE_ID),
107 CUSTOMER_ID = decode( x_CUSTOMER_ID, FND_API.G_MISS_NUM,CUSTOMER_ID,x_CUSTOMER_ID),
108 ADDRESS_ID = decode( x_ADDRESS_ID, FND_API.G_MISS_NUM,ADDRESS_ID,x_ADDRESS_ID),
109 LEAD_ID = decode( x_LEAD_ID, FND_API.G_MISS_NUM,LEAD_ID,x_LEAD_ID),
110 OPPORTUNITY_ID = decode( x_OPPORTUNITY_ID, FND_API.G_MISS_NUM,OPPORTUNITY_ID,x_OPPORTUNITY_ID),
111 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM,ORG_ID,x_ORG_ID)
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
120
121 PROCEDURE Lock_Row(
122 x_Rowid IN VARCHAR2,
123 x_EXCEPTIONS_ID IN NUMBER,
124 x_LAST_UPDATE_DATE IN DATE,
125 x_LAST_UPDATED_BY IN NUMBER,
126 x_CREATION_DATE IN DATE,
127 x_CREATED_BY IN NUMBER,
128 x_LAST_UPDATE_LOGIN IN NUMBER,
129 x_TERR_ID IN NUMBER,
130 x_RESOURCE_ID IN NUMBER,
131 x_CUSTOMER_ID IN NUMBER,
132 x_ADDRESS_ID IN NUMBER,
133 x_LEAD_ID IN NUMBER,
134 x_OPPORTUNITY_ID IN NUMBER,
135 x_ORG_ID IN NUMBER
136 ) IS
137 CURSOR C IS
138 SELECT *
139 FROM JTF_EXCEPTIONS
140 WHERE rowid = x_Rowid
141 FOR UPDATE of EXCEPTIONS_ID NOWAIT;
142 Recinfo C%ROWTYPE;
143 BEGIN
144 OPEN C;
145 FETCH C INTO Recinfo;
146 If (C%NOTFOUND) then
147 CLOSE C;
148 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
149 APP_EXCEPTION.RAISE_EXCEPTION;
150 End If;
151 CLOSE C;
152 if (
153 ( ( Recinfo.EXCEPTIONS_ID = x_EXCEPTIONS_ID)
154 OR ( ( Recinfo.EXCEPTIONS_ID is NULL )
155 AND ( x_EXCEPTIONS_ID is NULL )))
156 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
157 OR ( ( Recinfo.LAST_UPDATE_DATE is NULL )
158 AND ( x_LAST_UPDATE_DATE is NULL )))
159 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
160 OR ( ( Recinfo.LAST_UPDATED_BY is NULL )
161 AND ( x_LAST_UPDATED_BY is NULL )))
162 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
163 OR ( ( Recinfo.CREATION_DATE is NULL )
164 AND ( x_CREATION_DATE is NULL )))
165 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
166 OR ( ( Recinfo.CREATED_BY is NULL )
167 AND ( x_CREATED_BY is NULL )))
168 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
169 OR ( ( Recinfo.LAST_UPDATE_LOGIN is NULL )
170 AND ( x_LAST_UPDATE_LOGIN is NULL )))
171 AND ( ( Recinfo.TERR_ID = x_TERR_ID)
172 OR ( ( Recinfo.TERR_ID is NULL )
173 AND ( x_TERR_ID is NULL )))
174 AND ( ( Recinfo.RESOURCE_ID = x_RESOURCE_ID)
175 OR ( ( Recinfo.RESOURCE_ID is NULL )
176 AND ( x_RESOURCE_ID is NULL )))
177 AND ( ( Recinfo.CUSTOMER_ID = x_CUSTOMER_ID)
178 OR ( ( Recinfo.CUSTOMER_ID is NULL )
179 AND ( x_CUSTOMER_ID is NULL )))
180 AND ( ( Recinfo.ADDRESS_ID = x_ADDRESS_ID)
181 OR ( ( Recinfo.ADDRESS_ID is NULL )
182 AND ( x_ADDRESS_ID is NULL )))
183 AND ( ( Recinfo.LEAD_ID = x_LEAD_ID)
184 OR ( ( Recinfo.LEAD_ID is NULL )
185 AND ( x_LEAD_ID is NULL )))
186 AND ( ( Recinfo.OPPORTUNITY_ID = x_OPPORTUNITY_ID)
187 OR ( ( Recinfo.OPPORTUNITY_ID is NULL )
188 AND ( x_OPPORTUNITY_ID is NULL )))
189 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
190 OR ( ( Recinfo.ORG_ID is NULL )
191 AND ( x_ORG_ID is NULL )))
192 ) then
193 return;
194 else
195 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
196 APP_EXCEPTION.RAISE_EXCEPTION;
197 End If;
198 END Lock_Row;
199
200 END JTF_EXCEPTIONS_PKG;
[Home] [Help]