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