1 PACKAGE BODY AML_SALES_LEAD_TIMEFRAMES_PKG as
2 /* $Header: amlttfrb.pls 115.6 2003/01/03 23:45:26 ckapoor noship $ */
3 -- Start of Comments
4 -- Package name : AML_SALES_LEAD_TIMEFRAMES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AML_SALES_LEAD_TIMEFRAMES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amlttfrb.pls';
13
14 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
15 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
16 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
17 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
18
19 PROCEDURE Insert_Row(
20 px_TIMEFRAME_ID IN OUT NOCOPY NUMBER
21 ,p_DECISION_TIMEFRAME_CODE VARCHAR2
22 ,p_TIMEFRAME_DAYS NUMBER
23 ,p_CREATION_DATE in DATE
24 ,p_CREATED_BY in NUMBER
25 ,p_LAST_UPDATE_DATE in DATE
26 ,p_LAST_UPDATED_BY in NUMBER
27 ,p_LAST_UPDATE_LOGIN in NUMBER
28 ,p_ENABLED_FLAG in VARCHAR2)
29
30 IS
31 CURSOR C2 IS SELECT AML_SALES_LEAD_TIMEFRAMES_S.nextval FROM sys.dual;
32 BEGIN
33 If (px_TIMEFRAME_ID IS NULL) OR (px_TIMEFRAME_ID = FND_API.G_MISS_NUM) then
34 OPEN C2;
35 FETCH C2 INTO px_TIMEFRAME_ID;
36 CLOSE C2;
37 End If;
38 INSERT INTO AML_SALES_LEAD_TIMEFRAMES(
39 TIMEFRAME_ID
40 ,DECISION_TIMEFRAME_CODE
41 ,TIMEFRAME_DAYS
42 ,CREATION_DATE
43 ,CREATED_BY
44 ,LAST_UPDATE_DATE
45 ,LAST_UPDATED_BY
46 ,LAST_UPDATE_LOGIN
47 , ENABLED_FLAG
48 ) VALUES (
49 px_TIMEFRAME_ID
50 ,decode( p_DECISION_TIMEFRAME_CODE, FND_API.G_MISS_CHAR, NULL, p_DECISION_TIMEFRAME_CODE)
51 ,decode( p_TIMEFRAME_DAYS, FND_API.G_MISS_NUM, NULL, p_TIMEFRAME_DAYS)
52 , DECODE(p_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_creation_date)
53 , DECODE(p_created_by,FND_API.G_MISS_NUM,NULL,p_created_by)
54 , DECODE(p_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),p_last_update_date)
55 , DECODE(p_last_updated_by,FND_API.G_MISS_NUM,NULL,p_last_updated_by)
56 , DECODE(p_last_update_login,FND_API.G_MISS_NUM,NULL,p_last_update_login)
57 , DECODE(p_enabled_flag, FND_API.G_MISS_CHAR, 'N', p_enabled_flag));
58
59 End Insert_Row;
60
61 PROCEDURE Update_Row(
62 p_TIMEFRAME_ID NUMBER
63 ,p_DECISION_TIMEFRAME_CODE VARCHAR2
64 ,p_TIMEFRAME_DAYS NUMBER
65 ,p_CREATION_DATE in DATE
66 ,p_CREATED_BY in NUMBER
67 ,p_LAST_UPDATE_DATE in DATE
68 ,p_LAST_UPDATED_BY in NUMBER
69 ,p_LAST_UPDATE_LOGIN in NUMBER
70 ,p_ENABLED_FLAG in VARCHAR2)
71
72 IS
73 BEGIN
74 Update AML_SALES_LEAD_TIMEFRAMES
75 SET
76 DECISION_TIMEFRAME_CODE = decode( p_DECISION_TIMEFRAME_CODE, FND_API.G_MISS_CHAR, DECISION_TIMEFRAME_CODE, p_DECISION_TIMEFRAME_CODE)
77 ,TIMEFRAME_DAYS = decode( p_TIMEFRAME_DAYS, FND_API.G_MISS_NUM, TIMEFRAME_DAYS, p_TIMEFRAME_DAYS)
78 , creation_date = DECODE(p_creation_date,FND_API.G_MISS_DATE,CREATION_DATE,p_creation_date)
79 , created_by = DECODE(p_created_by,FND_API.G_MISS_NUM,CREATED_BY,p_created_by)
80 , last_update_date = DECODE(p_last_update_date,FND_API.G_MISS_DATE,LAST_UPDATE_DATE,p_last_update_date)
81 , last_updated_by = DECODE(p_last_updated_by,FND_API.G_MISS_NUM,LAST_UPDATED_BY,p_last_updated_by)
82 , last_update_login = DECODE(p_last_update_login,FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,p_last_update_login)
83 , enabled_flag = DECODE(p_enabled_flag,FND_API.G_MISS_CHAR,ENABLED_FLAG, p_enabled_flag)
84
85
86 where TIMEFRAME_ID = p_TIMEFRAME_ID;
87
88 If (SQL%NOTFOUND) then
89 RAISE NO_DATA_FOUND;
90 End If;
91 END Update_Row;
92
93 PROCEDURE Delete_Row(
94 p_TIMEFRAME_ID NUMBER)
95 IS
96 BEGIN
97 DELETE FROM AML_SALES_LEAD_TIMEFRAMES
98 WHERE TIMEFRAME_ID = p_TIMEFRAME_ID;
99 If (SQL%NOTFOUND) then
100 RAISE NO_DATA_FOUND;
101 End If;
102 END Delete_Row;
103
104 PROCEDURE Lock_Row(
105 p_TIMEFRAME_ID NUMBER
106 ,p_DECISION_TIMEFRAME_CODE VARCHAR2
107 ,p_TIMEFRAME_DAYS NUMBER
108 ,p_CREATION_DATE in DATE
109 ,p_CREATED_BY in NUMBER
110 ,p_LAST_UPDATE_DATE in DATE
111 ,p_LAST_UPDATED_BY in NUMBER
112 ,p_LAST_UPDATE_LOGIN in NUMBER
113 ,p_ENABLED_FLAG in VARCHAR2)
114
115 IS
116 CURSOR C IS
117 SELECT *
118 FROM AML_SALES_LEAD_TIMEFRAMES
119 WHERE TIMEFRAME_ID = p_TIMEFRAME_ID
120 FOR UPDATE of TIMEFRAME_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.TIMEFRAME_ID = p_TIMEFRAME_ID)
133 AND ( ( Recinfo.DECISION_TIMEFRAME_CODE = p_DECISION_TIMEFRAME_CODE)
134 OR ( ( Recinfo.DECISION_TIMEFRAME_CODE IS NULL )
135 AND ( p_DECISION_TIMEFRAME_CODE IS NULL )))
136 AND ( ( Recinfo.TIMEFRAME_DAYS = p_TIMEFRAME_DAYS)
137 OR ( ( Recinfo.TIMEFRAME_DAYS IS NULL )
138 AND ( p_TIMEFRAME_DAYS IS NULL )))
139 AND ((Recinfo.creation_date = p_creation_date)
140 OR ((Recinfo.creation_date IS NULL)
141 AND ( p_creation_date IS NULL)))
142 AND ((Recinfo.created_by = p_created_by)
143 OR ((Recinfo.created_by IS NULL)
144 AND ( p_created_by IS NULL)))
145 AND ((Recinfo.last_update_date = p_last_update_date)
146 OR ((Recinfo.last_update_date IS NULL)
147 AND ( p_last_update_date IS NULL)))
148 AND ((Recinfo.last_updated_by = p_last_updated_by)
149 OR ((Recinfo.last_updated_by IS NULL)
150 AND ( p_last_updated_by IS NULL)))
151 AND ((Recinfo.last_update_login = p_last_update_login)
152 OR ((Recinfo.last_update_login IS NULL)
153 AND ( p_last_update_login IS NULL)))
154 AND ((Recinfo.enabled_flag = p_enabled_flag)
155 OR ((Recinfo.enabled_flag IS NULL)
156 AND ( p_enabled_flag IS NULL)))
157
158 ) then
159 return;
160 else
161 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
162 APP_EXCEPTION.RAISE_EXCEPTION;
163 End If;
164 END Lock_Row;
165
166
167 PROCEDURE Load_Row (
168 X_timeframe_ID in OUT NOCOPY NUMBER,
169 X_DECISION_TIMEFRAME_CODE in VARCHAR2,
170 X_TIMEFRAME_DAYS in NUMBER,
171 X_OWNER in VARCHAR2,
172 X_ENABLED_FLAG in VARCHAR2)
173 IS
174 user_id number := 0;
175 row_id varchar2(64);
176
177
178 CURSOR c_get_last_updated (c_rank_id NUMBER) IS
179 SELECT last_updated_by
180 FROM AML_SALES_LEAD_TIMEFRAMES
181 WHERE timeframe_id = x_timeframe_id;
182 l_last_updated_by NUMBER;
183
184
185 BEGIN
186
187 -- If last_updated_by is not 1, means this record has been updated by
188 -- customer, we should not overwrite it.
189 OPEN c_get_last_updated (x_TIMEFRAME_ID);
190 FETCH c_get_last_updated INTO l_last_updated_by;
191 CLOSE c_get_last_updated;
192
193 IF nvl(l_last_updated_by, 1) = 1
194 THEN
195 if (X_OWNER = 'SEED') then
196 user_id := 1;
197 end if;
198
199 Update_Row(p_TIMEFRAME_ID => x_TIMEFRAME_ID,
200 p_DECISION_TIMEFRAME_CODE => x_DECISION_TIMEFRAME_CODE,
201 p_TIMEFRAME_DAYS => x_TIMEFRAME_DAYS,
202 p_CREATION_DATE => FND_API.G_MISS_DATE,
203 p_CREATED_BY => FND_API.G_MISS_NUM,
204 p_LAST_UPDATE_DATE => sysdate,
205 p_LAST_UPDATED_BY => user_id,
206 p_LAST_UPDATE_LOGIN => 0,
207 p_ENABLED_FLAG => x_ENABLED_FLAG
208 );
209
210
211
212 END IF;
213
214 EXCEPTION
215 when no_data_found then
216 Insert_Row(px_TIMEFRAME_ID => x_TIMEFRAME_ID,
217 p_DECISION_TIMEFRAME_CODE => x_DECISION_TIMEFRAME_CODE,
218 p_TIMEFRAME_DAYS => x_TIMEFRAME_DAYS,
219 p_creation_date => sysdate,
220 p_created_by => 0,
221 p_LAST_UPDATE_DATE => sysdate,
222 p_LAST_UPDATED_BY => user_id,
223 p_LAST_UPDATE_LOGIN => 0,
224 p_ENABLED_FLAG => x_ENABLED_FLAG
225 );
226
227
228
229 END load_row;
230
231
232
233 End AML_SALES_LEAD_TIMEFRAMES_PKG;
[Home] [Help]