[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_TYPE_QUAL_PKG
Source
1 PACKAGE BODY JTF_TERR_TYPE_QUAL_PKG as
2 /* $Header: jtfvttqb.pls 120.1 2005/06/24 00:26:24 jradhakr ship $ */
3
4 -----------------------------------------------------------------------
5 -- HISTORY
6 -- 11/20/99 VNEDUNGA Changing qualifer Mode form
7 -- number to varchar2
8 -- 01/20/00 VNEDUNGA Changing Update/Lock row procedures
9 -- to use terr_type_qual_id instead of
10 -- row_id
11 -- 01/20/00 VNEDUNGA Changing = NULL to IS NULL
12 -- 02/17/00 VNEDUNGA Adding ORG_ID to the table handler
13 -- procedures
14 -- 02/24/00 vnedunga fixing decode for date fields
15 --
16 -- End of Comments
17 ------------------------------------------------------------------------
18
19 PROCEDURE Insert_Row(
20 x_Rowid IN OUT NOCOPY VARCHAR2,
21 x_TERR_TYPE_QUAL_ID IN OUT NOCOPY NUMBER,
22 x_LAST_UPDATED_BY IN NUMBER,
23 x_LAST_UPDATE_DATE IN DATE,
24 x_CREATED_BY IN NUMBER,
25 x_CREATION_DATE IN DATE,
26 x_LAST_UPDATE_LOGIN IN NUMBER,
27 x_QUAL_USG_ID IN NUMBER,
28 x_TERR_TYPE_ID IN NUMBER,
29 x_EXCLUSIVE_USE_FLAG IN VARCHAR2,
30 x_OVERLAP_ALLOWED_FLAG IN VARCHAR2,
31 x_IN_USE_FLAG IN VARCHAR2,
32 x_QUALIFIER_MODE IN VARCHAR2,
33 x_ORG_ID IN NUMBER
34 ) IS
35 CURSOR C IS SELECT rowid FROM JTF_TERR_TYPE_QUAL
36 WHERE TERR_TYPE_QUAL_ID = x_TERR_TYPE_QUAL_ID;
37 CURSOR C2 IS SELECT JTF_TERR_TYPE_QUAL_s.nextval FROM sys.dual;
38 BEGIN
39 If (x_TERR_TYPE_QUAL_ID IS NULL) then
40 OPEN C2;
41 FETCH C2 INTO x_TERR_TYPE_QUAL_ID;
42 CLOSE C2;
43 End If;
44 INSERT INTO JTF_TERR_TYPE_QUAL(
45 TERR_TYPE_QUAL_ID,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_DATE,
48 CREATED_BY,
49 CREATION_DATE,
50 LAST_UPDATE_LOGIN,
51 QUAL_USG_ID,
52 TERR_TYPE_ID,
53 EXCLUSIVE_USE_FLAG,
54 OVERLAP_ALLOWED_FLAG,
55 IN_USE_FLAG,
56 QUALIFIER_MODE,
57 ORG_ID
58 ) VALUES (
59 x_TERR_TYPE_QUAL_ID,
60 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
61 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
62 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
63 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
64 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
65 decode( x_QUAL_USG_ID, FND_API.G_MISS_NUM, NULL,x_QUAL_USG_ID),
66 decode( x_TERR_TYPE_ID, FND_API.G_MISS_NUM, NULL,x_TERR_TYPE_ID),
67 decode( x_EXCLUSIVE_USE_FLAG, FND_API.G_MISS_CHAR, NULL,x_EXCLUSIVE_USE_FLAG),
68 decode( x_OVERLAP_ALLOWED_FLAG, FND_API.G_MISS_CHAR, NULL,x_OVERLAP_ALLOWED_FLAG),
69 decode( x_IN_USE_FLAG, FND_API.G_MISS_CHAR, NULL,x_IN_USE_FLAG),
70 decode( x_QUALIFIER_MODE, FND_API.G_MISS_CHAR, NULL,x_QUALIFIER_MODE),
71 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL, x_ORG_ID) );
72 OPEN C;
73 FETCH C INTO x_Rowid;
74 If (C%NOTFOUND) then
75 CLOSE C;
76 RAISE NO_DATA_FOUND;
77 End If;
78 End Insert_Row;
79
80
81
82 PROCEDURE Delete_Row( x_TERR_TYPE_QUAL_ID IN NUMBER
83 ) IS
84 BEGIN
85 DELETE FROM JTF_TERR_TYPE_QUAL
86 WHERE TERR_TYPE_QUAL_ID = x_TERR_TYPE_QUAL_ID;
87 If (SQL%NOTFOUND) then
88 RAISE NO_DATA_FOUND;
89 End If;
90 END Delete_Row;
91
92
93
94 PROCEDURE Update_Row(
95 x_Rowid IN VARCHAR2,
96 x_TERR_TYPE_QUAL_ID IN NUMBER,
97 x_LAST_UPDATED_BY IN NUMBER,
98 x_LAST_UPDATE_DATE IN DATE,
99 x_CREATED_BY IN NUMBER,
100 x_CREATION_DATE IN DATE,
101 x_LAST_UPDATE_LOGIN IN NUMBER,
102 x_QUAL_USG_ID IN NUMBER,
103 x_TERR_TYPE_ID IN NUMBER,
104 x_EXCLUSIVE_USE_FLAG IN VARCHAR2,
105 x_OVERLAP_ALLOWED_FLAG IN VARCHAR2,
106 x_IN_USE_FLAG IN VARCHAR2,
107 x_QUALIFIER_MODE IN VARCHAR2,
108 x_ORG_ID IN NUMBER
109 ) IS
110 BEGIN
111 Update JTF_TERR_TYPE_QUAL
112 SET
113 TERR_TYPE_QUAL_ID = decode( x_TERR_TYPE_QUAL_ID, FND_API.G_MISS_NUM,TERR_TYPE_QUAL_ID,x_TERR_TYPE_QUAL_ID),
114 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
115 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
116 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
117 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
118 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
119 QUAL_USG_ID = decode( x_QUAL_USG_ID, FND_API.G_MISS_NUM,QUAL_USG_ID,x_QUAL_USG_ID),
120 TERR_TYPE_ID = decode( x_TERR_TYPE_ID, FND_API.G_MISS_NUM,TERR_TYPE_ID,x_TERR_TYPE_ID),
121 EXCLUSIVE_USE_FLAG = decode( x_EXCLUSIVE_USE_FLAG, FND_API.G_MISS_CHAR,EXCLUSIVE_USE_FLAG,x_EXCLUSIVE_USE_FLAG),
122 OVERLAP_ALLOWED_FLAG = decode( x_OVERLAP_ALLOWED_FLAG, FND_API.G_MISS_CHAR,OVERLAP_ALLOWED_FLAG,x_OVERLAP_ALLOWED_FLAG),
123 IN_USE_FLAG = decode( x_IN_USE_FLAG, FND_API.G_MISS_CHAR,IN_USE_FLAG,x_IN_USE_FLAG),
124 QUALIFIER_MODE = decode( x_QUALIFIER_MODE, FND_API.G_MISS_CHAR,QUALIFIER_MODE,x_QUALIFIER_MODE),
125 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM, ORG_ID, x_ORG_ID)
126 where TERR_TYPE_QUAL_ID = X_TERR_TYPE_QUAL_ID;
127
128 If (SQL%NOTFOUND) then
129 RAISE NO_DATA_FOUND;
130 End If;
131 END Update_Row;
132
133
134
135 PROCEDURE Lock_Row(
136 x_Rowid IN VARCHAR2,
137 x_TERR_TYPE_QUAL_ID IN NUMBER,
138 x_LAST_UPDATED_BY IN NUMBER,
139 x_LAST_UPDATE_DATE IN DATE,
140 x_CREATED_BY IN NUMBER,
141 x_CREATION_DATE IN DATE,
142 x_LAST_UPDATE_LOGIN IN NUMBER,
143 x_QUAL_USG_ID IN NUMBER,
144 x_TERR_TYPE_ID IN NUMBER,
145 x_EXCLUSIVE_USE_FLAG IN VARCHAR2,
146 x_OVERLAP_ALLOWED_FLAG IN VARCHAR2,
147 x_IN_USE_FLAG IN VARCHAR2,
148 x_QUALIFIER_MODE IN VARCHAR2,
149 x_ORG_ID IN NUMBER
150 ) IS
151 CURSOR C IS
152 SELECT *
153 FROM JTF_TERR_TYPE_QUAL
154 WHERE TERR_TYPE_QUAL_ID = x_TERR_TYPE_QUAL_ID
155 FOR UPDATE of TERR_TYPE_QUAL_ID NOWAIT;
156 Recinfo C%ROWTYPE;
157 BEGIN
158 OPEN C;
159 FETCH C INTO Recinfo;
160 If (C%NOTFOUND) then
161 CLOSE C;
162 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
163 APP_EXCEPTION.RAISE_EXCEPTION;
164 End If;
165 CLOSE C;
166 if (
167 ( ( Recinfo.TERR_TYPE_QUAL_ID = x_TERR_TYPE_QUAL_ID)
168 OR ( ( Recinfo.TERR_TYPE_QUAL_ID IS NULL )
169 AND ( x_TERR_TYPE_QUAL_ID IS NULL )))
170 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
171 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
172 AND ( x_LAST_UPDATED_BY IS NULL )))
173 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
174 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
175 AND ( x_LAST_UPDATE_DATE IS NULL )))
176 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
177 OR ( ( Recinfo.CREATED_BY IS NULL )
178 AND ( x_CREATED_BY IS NULL )))
179 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
180 OR ( ( Recinfo.CREATION_DATE IS NULL )
181 AND ( x_CREATION_DATE IS NULL )))
182 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
183 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
184 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
185 AND ( ( Recinfo.QUAL_USG_ID = x_QUAL_USG_ID)
186 OR ( ( Recinfo.QUAL_USG_ID IS NULL )
187 AND ( x_QUAL_USG_ID IS NULL )))
188 AND ( ( Recinfo.TERR_TYPE_ID = x_TERR_TYPE_ID)
189 OR ( ( Recinfo.TERR_TYPE_ID IS NULL )
190 AND ( x_TERR_TYPE_ID IS NULL )))
191 AND ( ( Recinfo.EXCLUSIVE_USE_FLAG = x_EXCLUSIVE_USE_FLAG)
192 OR ( ( Recinfo.EXCLUSIVE_USE_FLAG IS NULL )
193 AND ( x_EXCLUSIVE_USE_FLAG IS NULL )))
194 AND ( ( Recinfo.OVERLAP_ALLOWED_FLAG = x_OVERLAP_ALLOWED_FLAG)
195 OR ( ( Recinfo.OVERLAP_ALLOWED_FLAG IS NULL )
196 AND ( x_OVERLAP_ALLOWED_FLAG IS NULL )))
197 AND ( ( Recinfo.IN_USE_FLAG = x_IN_USE_FLAG)
198 OR ( ( Recinfo.IN_USE_FLAG IS NULL )
199 AND ( x_IN_USE_FLAG IS NULL )))
200 AND ( ( Recinfo.QUALIFIER_MODE = x_QUALIFIER_MODE)
201 OR ( ( Recinfo.QUALIFIER_MODE IS NULL )
202 AND ( x_QUALIFIER_MODE IS NULL )))
203 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
204 OR ( ( Recinfo.ORG_ID IS NULL )
205 AND ( x_ORG_ID IS NULL )))
206 ) then
207 return;
208 else
209 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
210 APP_EXCEPTION.RAISE_EXCEPTION;
211 End If;
212 END Lock_Row;
213
214 END JTF_TERR_TYPE_QUAL_PKG;