1 PACKAGE BODY JTF_TERR_USGS_PKG AS
2 /* $Header: jtfvtusb.pls 120.0 2005/06/02 18:23:09 appldev ship $ */
3
4 -- 01/20/00 VNEDUNGA Changing Update/Lock_Row procedure to use
5 -- terr_usg-id instead of row_id
6 -- 01/20/00 VNEDUNGA Changing = NULL to IS NULL
7 -- 02/24/00 vnedunga fixing decode for date fields
8
9 PROCEDURE Insert_Row(
10 x_Rowid IN OUT NOCOPY VARCHAR2,
11 x_TERR_USG_ID IN OUT NOCOPY NUMBER,
12 x_LAST_UPDATE_DATE IN DATE,
13 x_LAST_UPDATED_BY IN NUMBER,
14 x_CREATION_DATE IN DATE,
15 x_CREATED_BY IN NUMBER,
16 x_LAST_UPDATE_LOGIN IN NUMBER,
17 x_TERR_ID IN NUMBER,
18 x_SOURCE_ID IN NUMBER,
19 x_ORG_ID IN NUMBER
20 ) IS
21 CURSOR C IS SELECT rowid FROM JTF_TERR_USGS_all
22 WHERE TERR_USG_ID = x_TERR_USG_ID;
23 CURSOR C2 IS SELECT JTF_TERR_USGS_s.nextval FROM sys.dual;
24 BEGIN
25 If (x_TERR_USG_ID IS NULL) then
26 OPEN C2;
27 FETCH C2 INTO x_TERR_USG_ID;
28 CLOSE C2;
29 End If;
30 INSERT INTO JTF_TERR_USGS_ALL(
31 TERR_USG_ID,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 CREATION_DATE,
35 CREATED_BY,
36 LAST_UPDATE_LOGIN,
37 TERR_ID,
38 SOURCE_ID,
39 ORG_ID
40 ) VALUES (
41 x_TERR_USG_ID,
42 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
43 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
44 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
45 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
46 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
47 decode( x_TERR_ID, FND_API.G_MISS_NUM, NULL,x_TERR_ID),
48 decode( x_SOURCE_ID, FND_API.G_MISS_NUM, NULL,x_SOURCE_ID),
49 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID)
50 );
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_USG_ID IN NUMBER
62 ) IS
63 BEGIN
64 DELETE FROM JTF_TERR_USGS_ALL
65 WHERE TERR_USG_ID = x_TERR_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_USG_ID IN NUMBER,
76 x_LAST_UPDATE_DATE IN DATE,
77 x_LAST_UPDATED_BY IN NUMBER,
78 x_CREATION_DATE IN DATE,
79 x_CREATED_BY IN NUMBER,
80 x_LAST_UPDATE_LOGIN IN NUMBER,
81 x_TERR_ID IN NUMBER,
82 x_SOURCE_ID IN NUMBER,
83 x_ORG_ID IN NUMBER
84 ) IS
85 BEGIN
86 Update JTF_TERR_USGS_all
87 SET
88 TERR_USG_ID = decode( x_TERR_USG_ID, FND_API.G_MISS_NUM,TERR_USG_ID,x_TERR_USG_ID),
89 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
90 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
91 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
92 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
93 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
94 TERR_ID = decode( x_TERR_ID, FND_API.G_MISS_NUM,TERR_ID,x_TERR_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_USG_ID = X_TERR_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_USG_ID IN NUMBER,
109 x_LAST_UPDATE_DATE IN DATE,
110 x_LAST_UPDATED_BY IN NUMBER,
111 x_CREATION_DATE IN DATE,
112 x_CREATED_BY IN NUMBER,
113 x_LAST_UPDATE_LOGIN IN NUMBER,
114 x_TERR_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_USGS_ALL
121 WHERE TERR_USG_ID = x_TERR_USG_ID
122 FOR UPDATE of TERR_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_USG_ID = x_TERR_USG_ID)
135 OR ( ( Recinfo.TERR_USG_ID IS NULL )
136 AND ( x_TERR_USG_ID IS NULL )))
137 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
138 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
139 AND ( x_LAST_UPDATE_DATE IS NULL )))
140 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
141 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
142 AND ( x_LAST_UPDATED_BY IS NULL )))
143 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
144 OR ( ( Recinfo.CREATION_DATE IS NULL )
145 AND ( x_CREATION_DATE IS NULL )))
146 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
147 OR ( ( Recinfo.CREATED_BY IS NULL )
148 AND ( x_CREATED_BY 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_ID = x_TERR_ID)
153 OR ( ( Recinfo.TERR_ID IS NULL )
154 AND ( x_TERR_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_USGS_PKG;
[Home] [Help]