1 PACKAGE BODY JTF_TERR_TYPE_USGS_PKG as
2 /* $Header: jtfvttub.pls 120.1 2005/06/24 00:26:29 jradhakr ship $ */
3
4 -- 01/20/99 VNEDUNGA Changing Update/Lock_row to use terr_type_usg_id
5 -- insted of row_id
6 -- 01/20/00 VNEDUNGA Changing = NULL to IS NULL
7 -- 02/17/00 VNEDUNGA Adding ORG_ID to the table handler procedures
8
9 PROCEDURE Insert_Row(
10 x_Rowid IN OUT NOCOPY VARCHAR2,
11 x_TERR_TYPE_USG_ID IN OUT NOCOPY NUMBER,
12 x_LAST_UPDATED_BY IN NUMBER,
13 x_LAST_UPDATE_DATE IN DATE,
14 x_CREATED_BY IN NUMBER,
15 x_CREATION_DATE IN DATE,
16 x_LAST_UPDATE_LOGIN IN NUMBER,
17 x_SOURCE_ID IN NUMBER,
18 x_TERR_TYPE_ID IN NUMBER,
19 x_ORG_ID IN NUMBER
20 ) IS
21 CURSOR C IS SELECT rowid FROM JTF_TERR_TYPE_USGS
22 WHERE TERR_TYPE_USG_ID = x_TERR_TYPE_USG_ID;
23
24 CURSOR C2 IS SELECT JTF_TERR_TYPE_USGS_s.nextval FROM sys.dual;
25 BEGIN
26 If (x_TERR_TYPE_USG_ID IS NULL) then
27 OPEN C2;
28 FETCH C2 INTO x_TERR_TYPE_USG_ID;
29 CLOSE C2;
30 End If;
31 INSERT INTO JTF_TERR_TYPE_USGS(
32 TERR_TYPE_USG_ID,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_DATE,
35 CREATED_BY,
36 CREATION_DATE,
37 LAST_UPDATE_LOGIN,
38 TERR_TYPE_ID,
39 SOURCE_ID,
40 ORG_ID
41 ) VALUES (
42 x_TERR_TYPE_USG_ID,
43 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
44 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL,x_LAST_UPDATE_DATE),
45 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
46 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, NULL,x_CREATION_DATE),
47 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
48 decode( x_TERR_TYPE_ID, FND_API.G_MISS_NUM, NULL,x_TERR_TYPE_ID),
49 decode( x_SOURCE_ID, FND_API.G_MISS_NUM, NULL,x_SOURCE_ID),
50 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL, x_ORG_ID) );
51 OPEN C;
52 FETCH C INTO x_Rowid;
53 If (C%NOTFOUND) then
54 CLOSE C;
55 RAISE NO_DATA_FOUND;
56 End If;
57 End Insert_Row;
58
59
60
61 PROCEDURE Delete_Row( x_TERR_TYPE_USG_ID IN NUMBER
62 ) IS
63 BEGIN
64 DELETE FROM JTF_TERR_TYPE_USGS
65 WHERE TERR_TYPE_USG_ID = x_TERR_TYPE_USG_ID;
66 If (SQL%NOTFOUND) then
67 RAISE NO_DATA_FOUND;
68 End If;
69 END Delete_Row;
70
71
72
73 PROCEDURE Update_Row(
74 x_Rowid IN VARCHAR2,
75 x_TERR_TYPE_USG_ID IN NUMBER,
76 x_LAST_UPDATED_BY IN NUMBER,
77 x_LAST_UPDATE_DATE IN DATE,
78 x_CREATED_BY IN NUMBER,
79 x_CREATION_DATE IN DATE,
80 x_LAST_UPDATE_LOGIN IN NUMBER,
81 x_TERR_TYPE_ID IN NUMBER,
82 x_SOURCE_ID IN NUMBER,
83 x_ORG_ID IN NUMBER
84 ) IS
85 BEGIN
86 Update JTF_TERR_TYPE_USGS
87 SET
88 TERR_TYPE_USG_ID = decode( x_TERR_TYPE_USG_ID, FND_API.G_MISS_NUM,TERR_TYPE_USG_ID,x_TERR_TYPE_USG_ID),
89 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
90 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
91 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
92 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
93 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
94 TERR_TYPE_ID = decode( x_TERR_TYPE_ID, FND_API.G_MISS_NUM,TERR_TYPE_ID,x_TERR_TYPE_ID),
95 SOURCE_ID = decode( x_SOURCE_ID, FND_API.G_MISS_NUM,SOURCE_ID,x_SOURCE_ID),
96 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM, ORG_ID, x_ORG_ID)
97 where TERR_TYPE_USG_ID = X_TERR_TYPE_USG_ID;
98
99 If (SQL%NOTFOUND) then
100 RAISE NO_DATA_FOUND;
101 End If;
102 END Update_Row;
103
104
105
106 PROCEDURE Lock_Row(
107 x_Rowid IN VARCHAR2,
108 x_TERR_TYPE_USG_ID IN NUMBER,
109 x_LAST_UPDATED_BY IN NUMBER,
110 x_LAST_UPDATE_DATE IN DATE,
111 x_CREATED_BY IN NUMBER,
112 x_CREATION_DATE IN DATE,
113 x_LAST_UPDATE_LOGIN IN NUMBER,
114 x_TERR_TYPE_ID IN NUMBER,
115 x_SOURCE_ID IN NUMBER,
116 x_ORG_ID IN NUMBER
117 ) IS
118 CURSOR C IS
119 SELECT *
120 FROM JTF_TERR_TYPE_USGS
121 WHERE TERR_TYPE_USG_ID = x_TERR_TYPE_USG_ID
122 FOR UPDATE of TERR_TYPE_USG_ID NOWAIT;
123 Recinfo C%ROWTYPE;
124 BEGIN
125 OPEN C;
126 FETCH C INTO Recinfo;
127 If (C%NOTFOUND) then
128 CLOSE C;
129 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
130 APP_EXCEPTION.RAISE_EXCEPTION;
131 End If;
132 CLOSE C;
133 if (
134 ( ( Recinfo.TERR_TYPE_USG_ID = x_TERR_TYPE_USG_ID)
135 OR ( ( Recinfo.TERR_TYPE_USG_ID IS NULL )
136 AND ( x_TERR_TYPE_USG_ID IS NULL )))
137 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
138 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
139 AND ( x_LAST_UPDATED_BY IS NULL )))
140 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
141 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
142 AND ( x_LAST_UPDATE_DATE IS NULL )))
143 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
144 OR ( ( Recinfo.CREATED_BY IS NULL )
145 AND ( x_CREATED_BY IS NULL )))
146 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
147 OR ( ( Recinfo.CREATION_DATE IS NULL )
148 AND ( x_CREATION_DATE IS NULL )))
149 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
150 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
151 AND ( x_LAST_UPDATE_LOGIN IS NULL )))
152 AND ( ( Recinfo.TERR_TYPE_ID = x_TERR_TYPE_ID)
153 OR ( ( Recinfo.TERR_TYPE_ID IS NULL )
154 AND ( x_TERR_TYPE_ID IS NULL )))
155 AND ( ( Recinfo.SOURCE_ID = x_SOURCE_ID)
156 OR ( ( Recinfo.SOURCE_ID IS NULL )
157 AND ( x_SOURCE_ID IS NULL )))
158 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
159 OR ( ( Recinfo.ORG_ID IS NULL )
160 AND ( x_ORG_ID IS NULL )))
161 ) then
162 return;
163 else
164 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
165 APP_EXCEPTION.RAISE_EXCEPTION;
166 End If;
167 END Lock_Row;
168
169 END JTF_TERR_TYPE_USGS_PKG;
[Home] [Help]