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