[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEADS_LOG_PKG
Source
1 PACKAGE BODY AS_SALES_LEADS_LOG_PKG as
2 /* $Header: asxtslab.pls 115.9 2002/12/18 22:29:40 solin ship $ */
3
4 -- Start of Comments
5 -- Package name : AS_SALES_LEADS_LOG_PVT
6 -- Purpose : Sales activity log management
7 -- NOTE :
8 -- History : 07/07/2000 CDESANTI Created.
9 -- 12/17/2002 SOLIN Add manual_rank_flag
10 --
11
12 -- NAME
13 -- Insert_Row
14 --
15 -- HISTORY
16 --
17 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
18 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
19 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
20 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
21
22 PROCEDURE Insert_Row( px_log_id IN OUT NOCOPY NUMBER,
23 p_sales_lead_id NUMBER,
24 p_created_by NUMBER,
25 p_creation_date DATE,
26 p_last_updated_by NUMBER,
27 p_last_update_date DATE,
28 p_last_update_login NUMBER,
29 p_request_id NUMBER,
30 p_program_application_id NUMBER,
31 p_program_id NUMBER,
32 p_program_update_date DATE,
33 p_status_code VARCHAR2,
34 p_assign_to_person_id NUMBER,
35 p_assign_to_salesforce_id NUMBER,
36 p_reject_reason_code VARCHAR2,
37 p_assign_sales_group_id NUMBER,
38 p_lead_rank_id NUMBER,
39 p_qualified_flag VARCHAR2,
40 -- new column for CAPRI lead referral
41 p_category VARCHAR2,
42 -- SOLIN added
43 p_manual_rank_flag VARCHAR2
44 ) IS
45 CURSOR C2 IS SELECT as_sales_leads_log_s.nextval FROM sys.dual;
46
47 p_User_Id NUMBER;
48 p_Login_Id NUMBER;
49 p_Date DATE;
50 p_Conc_Request_Id NUMBER;
51 p_Prg_Id NUMBER;
52 p_Prg_Update_Date DATE;
53 p_Prg_Appl_Id NUMBER;
54
55 BEGIN
56 if (px_log_id IS NULL) OR (px_log_id = FND_API.G_MISS_NUM) then
57 OPEN C2;
58 FETCH C2 INTO px_log_id;
59 CLOSE C2;
60 end if;
61
62 INSERT INTO AS_SALES_LEADS_LOG (
63 LOG_ID,
64 SALES_LEAD_ID,
65 CREATED_BY,
66 CREATION_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATE_LOGIN,
70 REQUEST_ID,
71 PROGRAM_APPLICATION_ID,
72 PROGRAM_ID,
73 PROGRAM_UPDATE_DATE,
74 STATUS_CODE,
75 ASSIGN_TO_PERSON_ID,
76 ASSIGN_TO_SALESFORCE_ID,
77 REJECT_REASON_CODE,
78 ASSIGN_SALES_GROUP_ID,
79 LEAD_RANK_ID,
80 QUALIFIED_FLAG,
81 -- new column for CAPRI lead referral
82 CATEGORY,
83 MANUAL_RANK_FLAG
84 )
85 VALUES ( px_log_id,
86 decode (p_sales_lead_id, fnd_api.g_miss_num, null, p_sales_lead_id),
87 decode (p_created_by , fnd_api.g_miss_num, null, p_created_by),
88 decode (p_creation_date,fnd_api.g_miss_date, to_date(null), p_creation_date),
89 decode (p_last_updated_by, fnd_api.g_miss_num, null, p_last_updated_by),
90 decode (p_last_update_date , fnd_api.g_miss_date, to_date(null), p_last_update_date),
91 decode (p_last_update_login , fnd_api.g_miss_num, null, p_last_update_login),
92 decode (p_request_id , fnd_api.g_miss_num, null, p_request_id),
93 decode (p_program_application_id , fnd_api.g_miss_num, null,p_program_application_id),
94 decode (p_program_id , fnd_api.g_miss_num, null,p_program_id),
95 decode (p_program_update_date , fnd_api.g_miss_date, to_date(null),p_program_update_date),
96 decode (p_status_code , fnd_api.g_miss_char, null, p_status_code),
97 decode (p_assign_to_person_id , fnd_api.g_miss_num, null,p_assign_to_person_id),
98 decode (p_assign_to_salesforce_id , fnd_api.g_miss_num, null,p_assign_to_salesforce_id),
99 decode (p_reject_reason_code, fnd_api.g_miss_char, null,p_reject_reason_code),
100 decode (p_assign_sales_group_id, fnd_api.g_miss_num, null,p_assign_sales_group_id),
101 decode (p_lead_rank_id , fnd_api.g_miss_num, null,p_lead_rank_id),
102 decode (p_qualified_flag, fnd_api.g_miss_char, null,p_qualified_flag),
103 decode (p_category, fnd_api.g_miss_char, null,p_category),
104 decode (p_manual_rank_flag, fnd_api.g_miss_char, null,p_manual_rank_flag)
105
106 );
107
108
109 END Insert_Row;
110
111 PROCEDURE Lock_Row( p_log_id NUMBER,
112 p_sales_lead_id NUMBER,
113 p_created_by NUMBER,
114 p_creation_date DATE,
115 p_last_updated_by NUMBER,
116 p_last_update_date DATE,
117 p_last_update_login NUMBER,
118 p_request_id NUMBER,
119 p_program_application_id NUMBER,
120 p_program_id NUMBER,
121 p_program_update_date DATE,
122 p_status_code VARCHAR2,
123 p_assign_to_person_id NUMBER,
124 p_assign_to_salesforce_id NUMBER,
125 p_reject_reason_code VARCHAR2,
126 p_assign_sales_group_id NUMBER,
127 p_lead_rank_id NUMBER,
128 p_qualified_flag VARCHAR2,
129 -- new column for CAPRI lead referral
130
131 p_category VARCHAR2,
132 p_manual_rank_flag VARCHAR2
133 ) IS
134
135 CURSOR C IS
136 SELECT *
137 FROM AS_SALES_LEADS_LOG
138 WHERE log_id = p_log_id
139 FOR UPDATE of log_Id NOWAIT;
140 Recinfo C%ROWTYPE;
141 BEGIN
142 OPEN C;
143 FETCH C INTO Recinfo;
144 if (C%NOTFOUND) then
145 CLOSE C;
146 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
147 APP_EXCEPTION.Raise_Exception;
148 end if;
149 CLOSE C;
150 if ( ( (Recinfo.log_id = p_log_Id)
151 OR ( (Recinfo.log_id IS NULL)
152 AND (p_Log_Id IS NULL)))
153 AND ( (Recinfo.sales_lead_id = p_sales_lead_id)
154 OR ( (Recinfo.sales_lead_id IS NULL)
155 AND (p_sales_lead_id IS NULL)))
156
157 AND ( (Recinfo.request_id = p_request_id)
158 OR ( (Recinfo.request_id IS NULL)
159 AND (p_request_id IS NULL)))
160 AND ( (Recinfo.program_application_id = p_program_application_id)
161 OR ( (Recinfo.program_application_id IS NULL)
162 AND (p_program_application_id IS NULL)))
163 AND ( (Recinfo.program_id = p_program_id)
164 OR ( (Recinfo.program_id IS NULL)
165 AND (p_program_id IS NULL)))
166 AND ( (Recinfo.program_update_date = p_program_update_date)
167 OR ( (Recinfo.program_update_date IS NULL)
168 AND (p_program_update_date IS NULL)))
169 AND ( (Recinfo.status_code = p_status_code)
170 OR ( (Recinfo.status_code IS NULL)
171 AND (p_status_code IS NULL)))
172 AND ( (Recinfo.assign_to_person_id = p_assign_to_person_id)
173 OR ( (Recinfo.assign_to_person_id IS NULL)
174 AND (p_assign_to_person_id IS NULL)))
175 AND ( (Recinfo.assign_to_salesforce_id = p_assign_to_salesforce_id)
176 OR ( (Recinfo.assign_to_salesforce_id IS NULL)
177 AND (p_assign_to_salesforce_id IS NULL)))
178 AND ( (Recinfo.reject_reason_code = p_reject_reason_code)
179 OR ( (Recinfo.reject_reason_code IS NULL)
180 AND (p_reject_reason_code IS NULL)))
181 AND ( (Recinfo.assign_sales_group_id = p_assign_sales_group_id)
182 OR ( (Recinfo.assign_sales_group_id IS NULL)
183 AND (p_assign_sales_group_id IS NULL)))
184 AND ( (Recinfo.lead_rank_id = p_lead_rank_id)
185 OR ( (Recinfo.lead_rank_id IS NULL)
186 AND (p_lead_rank_id IS NULL)))
187 AND ( (Recinfo.qualified_flag = p_qualified_flag)
188 OR ( (Recinfo.qualified_flag IS NULL)
189 AND (p_qualified_flag IS NULL)))
190 AND ( (Recinfo.category = p_category)
191 OR ( (Recinfo.category IS NULL)
192 AND (p_category IS NULL)))
193 AND ( (Recinfo.manual_rank_flag = p_manual_rank_flag)
194 OR ( (Recinfo.manual_rank_flag IS NULL)
195 AND (p_manual_rank_flag IS NULL)))
196
197 ) then
198 return;
199 else
200 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
201 APP_EXCEPTION.Raise_Exception;
202 end if;
203 END Lock_Row;
204
205 PROCEDURE Update_Row(p_log_id NUMBER,
206 p_sales_lead_id NUMBER,
207 p_created_by NUMBER,
208 p_creation_date DATE,
209 p_last_updated_by NUMBER,
210 p_last_update_date DATE,
211 p_last_update_login NUMBER,
212 p_request_id NUMBER,
213 p_program_application_id NUMBER,
214 p_program_id NUMBER,
215 p_program_update_date DATE,
216 p_status_code VARCHAR2,
217 p_assign_to_person_id NUMBER,
218 p_assign_to_salesforce_id NUMBER,
219 p_reject_reason_code VARCHAR2,
220 p_assign_sales_group_id NUMBER,
221 p_lead_rank_id NUMBER,
222 p_qualified_flag VARCHAR2,
223 -- new column for CAPRI lead referral
224 p_category VARCHAR2,
225 p_manual_rank_flag VARCHAR2
226 ) IS
227 BEGIN
228 UPDATE AS_SALES_LEADS_LOG
229 SET
230 sales_lead_id = decode(p_sales_lead_id, FND_API.G_MISS_NUM, sales_lead_id, p_sales_lead_id),
231 last_update_date = decode(p_last_Update_Date, FND_API.G_MISS_DATE, last_update_date , p_last_update_date),
232 last_updated_by = decode(last_Updated_By, FND_API.G_MISS_NUM, last_updated_by, p_last_updated_by),
233 last_update_login = decode(last_Update_Login, FND_API.G_MISS_NUM, last_update_login, p_last_update_login),
234 request_id = decode(request_id , FND_API.G_MISS_NUM, request_id, p_request_id),
235 program_application_id = decode(program_application_id , FND_API.G_MISS_NUM, program_application_id, p_program_application_id),
236 program_id = decode(program_id , FND_API.G_MISS_NUM, program_id, p_program_id),
237 program_update_date = decode(program_update_date , FND_API.G_MISS_DATE, program_update_date, p_program_update_date),
238 status_code = decode(status_code , FND_API.G_MISS_CHAR, status_code, p_status_code),
239 assign_to_person_id = decode(assign_to_person_id , FND_API.G_MISS_NUM, assign_to_person_id, p_assign_to_person_id),
240 assign_to_salesforce_id = decode(assign_to_salesforce_id , FND_API.G_MISS_NUM, assign_to_salesforce_id, p_assign_to_salesforce_id),
241 reject_reason_code = decode(reject_reason_code, FND_API.G_MISS_CHAR, reject_reason_code, p_reject_reason_code),
242 assign_sales_group_id = decode(assign_sales_group_id, FND_API.G_MISS_NUM, assign_sales_group_id, p_assign_sales_group_id),
243 lead_rank_id = decode(lead_rank_id , FND_API.G_MISS_NUM, lead_rank_id, p_lead_rank_id),
244 qualified_flag = decode(qualified_flag, FND_API.G_MISS_CHAR, qualified_flag, p_qualified_flag),
245 category = decode(category, FND_API.G_MISS_CHAR, category, p_category),
246 manual_rank_flag = decode(manual_rank_flag, FND_API.G_MISS_CHAR, manual_rank_flag, p_manual_rank_flag)
247
248 WHERE log_id = p_log_id;
249
250 if (SQL%NOTFOUND) then
251 RAISE NO_DATA_FOUND;
252 end if;
253
254 END Update_Row;
255
256 PROCEDURE Delete_Row(p_log_id NUMBER) is
257 BEGIN
258
259 DELETE FROM AS_SALES_LEADS_LOG
260 WHERE log_id = p_log_id;
261
262 if (SQL%NOTFOUND) then
263 RAISE NO_DATA_FOUND;
264 end if;
265
266 END Delete_Row;
267
268
269 END AS_SALES_LEADS_LOG_PKG;
270