[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEADS_PKG
Source
1 PACKAGE BODY AS_SALES_LEADS_PKG as
2 /* $Header: asxtslmb.pls 115.34 2004/04/14 20:39:28 chchandr ship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEADS_PKG
5 -- Purpose : Sales leads table handlers
6 -- NOTE :
7 -- History : 06/05/2000 FFANG Generated by Table Handler Generater.
8 -- 06/06/2000 FFANG Modified according to data schema
9 -- changes.
10 -- 06/20/2000 FFANG Correct sales__lead_line_id to
11 -- sales_lead_line_id
12 -- 06/21/2000 FFANG Modified according schema changes
13 --
14 -- End of Comments
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEADS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxtslmb.pls';
19
20 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
21 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
22 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
23 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
24
25 PROCEDURE Sales_Lead_Insert_Row(
26 px_SALES_LEAD_ID IN OUT NOCOPY NUMBER,
27 p_LAST_UPDATE_DATE DATE,
28 p_LAST_UPDATED_BY NUMBER,
29 p_CREATION_DATE DATE,
30 p_CREATED_BY NUMBER,
31 p_LAST_UPDATE_LOGIN NUMBER,
32 p_REQUEST_ID NUMBER,
33 p_PROGRAM_APPLICATION_ID NUMBER,
34 p_PROGRAM_ID NUMBER,
35 p_PROGRAM_UPDATE_DATE DATE,
36 p_LEAD_NUMBER VARCHAR2,
37 p_STATUS_CODE VARCHAR2,
38 p_CUSTOMER_ID NUMBER,
39 p_ADDRESS_ID NUMBER,
40 p_SOURCE_PROMOTION_ID NUMBER,
41 p_INITIATING_CONTACT_ID NUMBER,
42 p_ORIG_SYSTEM_REFERENCE VARCHAR2,
43 p_CONTACT_ROLE_CODE VARCHAR2,
44 p_CHANNEL_CODE VARCHAR2,
45 p_BUDGET_AMOUNT NUMBER,
46 p_CURRENCY_CODE VARCHAR2,
47 p_DECISION_TIMEFRAME_CODE VARCHAR2,
48 p_CLOSE_REASON VARCHAR2,
49 p_LEAD_RANK_ID NUMBER,
50 p_LEAD_RANK_CODE VARCHAR2,
51 p_PARENT_PROJECT VARCHAR2,
52 p_DESCRIPTION VARCHAR2,
53 p_ATTRIBUTE_CATEGORY VARCHAR2,
54 p_ATTRIBUTE1 VARCHAR2,
55 p_ATTRIBUTE2 VARCHAR2,
56 p_ATTRIBUTE3 VARCHAR2,
57 p_ATTRIBUTE4 VARCHAR2,
58 p_ATTRIBUTE5 VARCHAR2,
59 p_ATTRIBUTE6 VARCHAR2,
60 p_ATTRIBUTE7 VARCHAR2,
61 p_ATTRIBUTE8 VARCHAR2,
62 p_ATTRIBUTE9 VARCHAR2,
63 p_ATTRIBUTE10 VARCHAR2,
64 p_ATTRIBUTE11 VARCHAR2,
65 p_ATTRIBUTE12 VARCHAR2,
66 p_ATTRIBUTE13 VARCHAR2,
67 p_ATTRIBUTE14 VARCHAR2,
68 p_ATTRIBUTE15 VARCHAR2,
69 p_ASSIGN_TO_PERSON_ID NUMBER,
70 p_ASSIGN_TO_SALESFORCE_ID NUMBER,
71 p_ASSIGN_SALES_GROUP_ID NUMBER,
72 p_ASSIGN_DATE DATE,
73 p_BUDGET_STATUS_CODE VARCHAR2,
74 p_ACCEPT_FLAG VARCHAR2,
75 p_VEHICLE_RESPONSE_CODE VARCHAR2,
76 p_TOTAL_SCORE NUMBER,
77 p_SCORECARD_ID NUMBER,
78 p_KEEP_FLAG VARCHAR2,
79 p_URGENT_FLAG VARCHAR2,
80 p_IMPORT_FLAG VARCHAR2,
81 p_REJECT_REASON_CODE VARCHAR2,
82 p_DELETED_FLAG VARCHAR2,
83 p_OFFER_ID NUMBER,
84 p_QUALIFIED_FLAG VARCHAR2,
85 p_ORIG_SYSTEM_CODE VARCHAR2,
86 -- p_SECURITY_GROUP_ID NUMBER,
87 p_INC_PARTNER_PARTY_ID NUMBER,
88 p_INC_PARTNER_RESOURCE_ID NUMBER,
89 p_PRM_EXEC_SPONSOR_FLAG VARCHAR2,
90 p_PRM_PRJ_LEAD_IN_PLACE_FLAG VARCHAR2,
91 p_PRM_SALES_LEAD_TYPE VARCHAR2,
92 p_PRM_IND_CLASSIFICATION_CODE VARCHAR2,
93 p_PRM_ASSIGNMENT_TYPE VARCHAR2,
94 p_AUTO_ASSIGNMENT_TYPE VARCHAR2,
95 p_PRIMARY_CONTACT_PARTY_ID NUMBER,
96 -- bug 2098158
97 p_PRIMARY_CNT_PERSON_PARTY_ID NUMBER,
98 p_PRIMARY_CONTACT_PHONE_ID NUMBER,
99 -- new columns for CAPRI lead referral
100 p_REFERRED_BY NUMBER,
101 p_REFERRAL_TYPE VARCHAR2,
102 p_REFERRAL_STATUS VARCHAR2,
103 p_REF_DECLINE_REASON VARCHAR2,
104 p_REF_COMM_LTR_STATUS VARCHAR2,
105 p_REF_ORDER_NUMBER NUMBER,
106 p_REF_ORDER_AMT NUMBER,
107 p_REF_COMM_AMT NUMBER,
108 -- bug No.2341515, 2368075
109 p_LEAD_DATE DATE ,
110 p_SOURCE_SYSTEM VARCHAR2,
111 p_COUNTRY VARCHAR2,
112 -- 11.5.9
113 p_TOTAL_AMOUNT NUMBER,
114 p_EXPIRATION_DATE DATE,
115 p_LEAD_RANK_IND VARCHAR2,
116 p_LEAD_ENGINE_RUN_DATE DATE,
117 p_CURRENT_REROUTES NUMBER
118
119 -- new columns for appsperf CRMAP denorm project bug 2928041
120
121 , p_STATUS_OPEN_FLAG VARCHAR2,
122 p_LEAD_RANK_SCORE NUMBER
123
124 -- 11.5.10 - ckapoor : new columns
125
126 , p_MARKETING_SCORE NUMBER
127 , p_INTERACTION_SCORE NUMBER
128 , p_SOURCE_PRIMARY_REFERENCE VARCHAR2
129 , p_SOURCE_SECONDARY_REFERENCE VARCHAR2
130 , p_SALES_METHODOLOGY_ID NUMBER
131 , p_SALES_STAGE_ID NUMBER
132
133
134
135
136
137 )
138 IS
139 CURSOR C2 IS SELECT AS_SALES_LEADS_S.nextval FROM sys.dual;
140 BEGIN
141 If (px_SALES_LEAD_ID IS NULL) OR (px_SALES_LEAD_ID = FND_API.G_MISS_NUM) then
142 OPEN C2;
143 FETCH C2 INTO px_SALES_LEAD_ID;
144 CLOSE C2;
145 End If;
146
147
148 INSERT INTO AS_SALES_LEADS(
149 SALES_LEAD_ID,
150 LAST_UPDATE_DATE,
151 LAST_UPDATED_BY,
152 CREATION_DATE,
153 CREATED_BY,
154 LAST_UPDATE_LOGIN,
155 REQUEST_ID,
156 PROGRAM_APPLICATION_ID,
157 PROGRAM_ID,
158 PROGRAM_UPDATE_DATE,
159 LEAD_NUMBER,
160 STATUS_CODE,
161 CUSTOMER_ID,
162 ADDRESS_ID,
163 SOURCE_PROMOTION_ID,
164 INITIATING_CONTACT_ID,
165 ORIG_SYSTEM_REFERENCE,
166 CONTACT_ROLE_CODE,
167 CHANNEL_CODE,
168 BUDGET_AMOUNT,
169 CURRENCY_CODE,
170 DECISION_TIMEFRAME_CODE,
171 CLOSE_REASON,
172 LEAD_RANK_ID,
173 LEAD_RANK_CODE,
174 PARENT_PROJECT,
175 DESCRIPTION,
176 ATTRIBUTE_CATEGORY,
177 ATTRIBUTE1,
178 ATTRIBUTE2,
179 ATTRIBUTE3,
180 ATTRIBUTE4,
181 ATTRIBUTE5,
182 ATTRIBUTE6,
183 ATTRIBUTE7,
184 ATTRIBUTE8,
185 ATTRIBUTE9,
186 ATTRIBUTE10,
187 ATTRIBUTE11,
188 ATTRIBUTE12,
189 ATTRIBUTE13,
190 ATTRIBUTE14,
191 ATTRIBUTE15,
192 ASSIGN_TO_PERSON_ID,
193 ASSIGN_TO_SALESFORCE_ID,
194 ASSIGN_SALES_GROUP_ID,
195 ASSIGN_DATE,
196 BUDGET_STATUS_CODE,
197 ACCEPT_FLAG,
198 VEHICLE_RESPONSE_CODE,
199 TOTAL_SCORE,
200 SCORECARD_ID,
201 KEEP_FLAG,
202 URGENT_FLAG,
203 IMPORT_FLAG,
204 REJECT_REASON_CODE,
205 DELETED_FLAG,
206 OFFER_ID,
207 QUALIFIED_FLAG,
208 ORIG_SYSTEM_CODE,
209 -- SECURITY_GROUP_ID,
210 INCUMBENT_PARTNER_PARTY_ID,
211 INCUMBENT_PARTNER_RESOURCE_ID,
212 PRM_EXEC_SPONSOR_FLAG,
213 PRM_PRJ_LEAD_IN_PLACE_FLAG,
214 PRM_SALES_LEAD_TYPE,
215 PRM_IND_CLASSIFICATION_CODE,
216 PRM_ASSIGNMENT_TYPE,
217 AUTO_ASSIGNMENT_TYPE,
218 PRIMARY_CONTACT_PARTY_ID,
219 -- bug 2098158
220 PRIMARY_CNT_PERSON_PARTY_ID,
221 PRIMARY_CONTACT_PHONE_ID,
222
223 -- new columns for CAPRI lead referral
224 REFERRED_BY,
225 REFERRAL_TYPE,
226 REFERRAL_STATUS,
227 REF_DECLINE_REASON,
228 REF_COMM_LTR_STATUS,
229 REF_ORDER_NUMBER,
230 REF_ORDER_AMT,
231 REF_COMM_AMT,
232 TRUNC_CREATION_DATE,
233 -- bug No.2341515, 2368075
234 LEAD_DATE ,
235 SOURCE_SYSTEM ,
236 COUNTRY ,
237 -- 11.5.9
238 TOTAL_AMOUNT ,
239 EXPIRATION_DATE ,
240 LEAD_RANK_IND ,
241 LEAD_ENGINE_RUN_DATE,
242 CURRENT_REROUTES
243
244 -- new columns for appsperf CRMAP denorm project bug 2928041
245 ,STATUS_OPEN_FLAG
246 , LEAD_RANK_SCORE
247
248 -- 11.5.10 - ckapoor : new columns
249
250 , MARKETING_SCORE
251 , INTERACTION_SCORE
252 , SOURCE_PRIMARY_REFERENCE
253 , SOURCE_SECONDARY_REFERENCE
254 , SALES_METHODOLOGY_ID
255 , SALES_STAGE_ID
256
257
258
259
260 )
261 VALUES (
262 px_SALES_LEAD_ID,
263 decode(p_LAST_UPDATE_DATE,FND_API.G_MISS_DATE, TO_DATE(NULL),p_LAST_UPDATE_DATE),
264 decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
265 decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
266 decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
267 decode(p_LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM, NULL,p_LAST_UPDATE_LOGIN),
268 decode(p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
269 decode(p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL,
270 p_PROGRAM_APPLICATION_ID),
271 decode(p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
275 decode(p_STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_STATUS_CODE),
272 decode(p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),
273 p_PROGRAM_UPDATE_DATE),
274 px_SALES_LEAD_ID,
276 decode(p_CUSTOMER_ID, FND_API.G_MISS_NUM, NULL, p_CUSTOMER_ID),
277 decode(p_ADDRESS_ID, FND_API.G_MISS_NUM, NULL, p_ADDRESS_ID),
278 decode(p_SOURCE_PROMOTION_ID, FND_API.G_MISS_NUM, NULL,
279 p_SOURCE_PROMOTION_ID),
280 decode(p_INITIATING_CONTACT_ID, FND_API.G_MISS_NUM, NULL,
281 p_INITIATING_CONTACT_ID),
282 decode(p_ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, NULL,
283 p_ORIG_SYSTEM_REFERENCE),
284 decode(p_CONTACT_ROLE_CODE,FND_API.G_MISS_CHAR,NULL,p_CONTACT_ROLE_CODE),
285 decode(p_CHANNEL_CODE, FND_API.G_MISS_CHAR, NULL, p_CHANNEL_CODE),
286 decode(p_BUDGET_AMOUNT, FND_API.G_MISS_NUM, NULL, p_BUDGET_AMOUNT),
287 decode(p_CURRENCY_CODE, FND_API.G_MISS_CHAR, NULL, p_CURRENCY_CODE),
288 decode(p_DECISION_TIMEFRAME_CODE, FND_API.G_MISS_CHAR, NULL,
289 p_DECISION_TIMEFRAME_CODE),
290 decode(p_CLOSE_REASON, FND_API.G_MISS_CHAR, NULL, p_CLOSE_REASON),
291 decode(p_LEAD_RANK_ID, FND_API.G_MISS_NUM, NULL, p_LEAD_RANK_ID),
292 decode(p_LEAD_RANK_CODE, FND_API.G_MISS_CHAR, NULL, p_LEAD_RANK_CODE),
293 decode(p_PARENT_PROJECT, FND_API.G_MISS_CHAR, NULL, p_PARENT_PROJECT),
294 decode(p_DESCRIPTION, NULL, to_char(px_SALES_LEAD_ID), FND_API.G_MISS_CHAR, to_char(px_SALES_LEAD_ID), p_DESCRIPTION),
295 decode(p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL,
296 p_ATTRIBUTE_CATEGORY),
297 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
298 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
299 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
300 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
301 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
302 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
303 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
304 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
305 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
306 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
307 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
308 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
309 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
310 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
311 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
312 decode(p_ASSIGN_TO_PERSON_ID, FND_API.G_MISS_NUM, NULL,
313 p_ASSIGN_TO_PERSON_ID),
314 decode(p_ASSIGN_TO_SALESFORCE_ID, FND_API.G_MISS_NUM, NULL,
315 p_ASSIGN_TO_SALESFORCE_ID),
316 decode(p_ASSIGN_SALES_GROUP_ID, FND_API.G_MISS_NUM, NULL,
317 p_ASSIGN_SALES_GROUP_ID),
318 decode(p_ASSIGN_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_ASSIGN_DATE),
319 decode(p_BUDGET_STATUS_CODE, FND_API.G_MISS_CHAR, NULL,
320 p_BUDGET_STATUS_CODE),
321 decode(p_ACCEPT_FLAG, FND_API.G_MISS_CHAR, NULL, p_ACCEPT_FLAG),
322 decode(p_VEHICLE_RESPONSE_CODE, FND_API.G_MISS_CHAR, NULL,
323 p_VEHICLE_RESPONSE_CODE),
324 decode(p_TOTAL_SCORE, FND_API.G_MISS_NUM, NULL, p_TOTAL_SCORE),
325 decode(p_SCORECARD_ID, FND_API.G_MISS_NUM, NULL, p_SCORECARD_ID),
326 decode(p_KEEP_FLAG, FND_API.G_MISS_CHAR, NULL, p_KEEP_FLAG),
327 decode(p_URGENT_FLAG, FND_API.G_MISS_CHAR, NULL, p_URGENT_FLAG),
328 decode(p_IMPORT_FLAG, FND_API.G_MISS_CHAR, NULL, p_IMPORT_FLAG),
329 decode(p_REJECT_REASON_CODE, FND_API.G_MISS_CHAR, NULL,
330 p_REJECT_REASON_CODE),
331 decode(p_DELETED_FLAG, FND_API.G_MISS_CHAR, NULL, p_DELETED_FLAG),
332 decode(p_OFFER_ID, FND_API.G_MISS_NUM, NULL, p_OFFER_ID),
336 decode(p_INC_PARTNER_PARTY_ID, FND_API.G_MISS_NUM, NULL,
333 decode(p_QUALIFIED_FLAG, FND_API.G_MISS_CHAR, NULL, p_QUALIFIED_FLAG),
334 decode(p_ORIG_SYSTEM_CODE, FND_API.G_MISS_CHAR, NULL, p_ORIG_SYSTEM_CODE),
335 -- decode(p_SECURITY_GROUP_ID,FND_API.G_MISS_NUM,NULL, p_SECURITY_GROUP_ID),
337 p_INC_PARTNER_PARTY_ID),
338 decode(p_INC_PARTNER_RESOURCE_ID, FND_API.G_MISS_NUM, NULL,
339 p_INC_PARTNER_RESOURCE_ID),
340 decode(p_PRM_EXEC_SPONSOR_FLAG, FND_API.G_MISS_CHAR, NULL,
341 p_PRM_EXEC_SPONSOR_FLAG),
342 decode(p_PRM_PRJ_LEAD_IN_PLACE_FLAG, FND_API.G_MISS_CHAR, NULL,
343 p_PRM_PRJ_LEAD_IN_PLACE_FLAG),
344 decode(p_PRM_SALES_LEAD_TYPE, FND_API.G_MISS_CHAR, NULL,
345 p_PRM_SALES_LEAD_TYPE),
346 decode(p_PRM_IND_CLASSIFICATION_CODE, FND_API.G_MISS_CHAR, NULL,
347 p_PRM_IND_CLASSIFICATION_CODE),
348 decode(p_PRM_ASSIGNMENT_TYPE, FND_API.G_MISS_CHAR, NULL,
349 p_PRM_ASSIGNMENT_TYPE),
353 p_PRIMARY_CONTACT_PARTY_ID),
350 decode(p_AUTO_ASSIGNMENT_TYPE, FND_API.G_MISS_CHAR, NULL,
351 p_AUTO_ASSIGNMENT_TYPE),
352 decode(p_PRIMARY_CONTACT_PARTY_ID, FND_API.G_MISS_NUM, NULL,
354 decode(p_PRIMARY_CNT_PERSON_PARTY_ID, FND_API.G_MISS_NUM, NULL,
355 p_PRIMARY_CNT_PERSON_PARTY_ID),
356 decode(p_PRIMARY_CONTACT_PHONE_ID, FND_API.G_MISS_NUM, NULL,
357 p_PRIMARY_CONTACT_PHONE_ID),
358 decode(p_REFERRED_BY, FND_API.G_MISS_NUM, NULL,
359 p_REFERRED_BY),
360 decode(p_REFERRAL_TYPE, FND_API.G_MISS_CHAR, NULL,
361 p_REFERRAL_TYPE),
362 decode(p_REFERRAL_STATUS, FND_API.G_MISS_CHAR, NULL,
363 p_REFERRAL_STATUS),
364 decode(p_REF_DECLINE_REASON, FND_API.G_MISS_CHAR, NULL,
365 p_REF_DECLINE_REASON),
366 decode(p_REF_COMM_LTR_STATUS, FND_API.G_MISS_CHAR, NULL,
367 p_REF_COMM_LTR_STATUS),
368 decode(p_REF_ORDER_NUMBER, FND_API.G_MISS_NUM, NULL,
369 p_REF_ORDER_NUMBER),
370 decode(p_REF_ORDER_AMT, FND_API.G_MISS_NUM, NULL,
371 p_REF_ORDER_AMT),
372 decode(p_REF_COMM_AMT, FND_API.G_MISS_NUM, NULL,
373 p_REF_COMM_AMT),
374 decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TRUNC(TO_DATE(NULL)), TRUNC(p_CREATION_DATE)),
375 decode(p_LEAD_DATE, FND_API.G_MISS_DATE, SYSDATE, p_LEAD_DATE),
376 decode(p_SOURCE_SYSTEM, FND_API.G_MISS_CHAR, 'USER', p_SOURCE_SYSTEM),
377 decode(p_COUNTRY, FND_API.G_MISS_CHAR, NULL, p_COUNTRY),
378
379 decode(p_TOTAL_AMOUNT, FND_API.G_MISS_NUM, NULL,p_TOTAL_AMOUNT),
380 decode(p_EXPIRATION_DATE, FND_API.G_MISS_DATE, SYSDATE, p_EXPIRATION_DATE),
381 decode(p_LEAD_RANK_IND, FND_API.G_MISS_CHAR, NULL,p_LEAD_RANK_IND),
382 decode(p_LEAD_ENGINE_RUN_DATE, FND_API.G_MISS_DATE, NULL, p_LEAD_ENGINE_RUN_DATE),
383 decode(p_CURRENT_REROUTES, FND_API.G_MISS_NUM, NULL,p_CURRENT_REROUTES)
384
385 -- new columns for appsperf CRMAP denorm project bug 2928041
386
387 , decode(p_STATUS_OPEN_FLAG, FND_API.G_MISS_CHAR, NULL, p_STATUS_OPEN_FLAG)
388
389 , decode(p_LEAD_RANK_SCORE, FND_API.G_MISS_NUM, NULL,p_LEAD_RANK_SCORE)
390
391
392 -- 11.5.10 - ckapoor : new columns
393
394
395 , decode(p_MARKETING_SCORE, FND_API.G_MISS_NUM, NULL, p_MARKETING_SCORE)
396
397 , decode(p_INTERACTION_SCORE, FND_API.G_MISS_NUM, NULL,p_INTERACTION_SCORE)
398
399 , decode(p_SOURCE_PRIMARY_REFERENCE, FND_API.G_MISS_CHAR, NULL, p_SOURCE_PRIMARY_REFERENCE)
400
401 , decode(p_SOURCE_SECONDARY_REFERENCE, FND_API.G_MISS_CHAR, NULL,p_SOURCE_SECONDARY_REFERENCE)
402 , decode(p_SALES_METHODOLOGY_ID, FND_API.G_MISS_NUM, NULL, p_SALES_METHODOLOGY_ID)
403
404 , decode(p_SALES_STAGE_ID, FND_API.G_MISS_NUM, NULL,p_SALES_STAGE_ID)
405
406
407 );
408 End Sales_Lead_Insert_Row;
409
410
411 PROCEDURE Sales_Lead_Update_Row(
412 p_SALES_LEAD_ID NUMBER,
413 p_LAST_UPDATE_DATE DATE,
414 p_LAST_UPDATED_BY NUMBER,
415 p_CREATION_DATE DATE,
416 p_CREATED_BY NUMBER,
417 p_LAST_UPDATE_LOGIN NUMBER,
418 p_REQUEST_ID NUMBER,
419 p_PROGRAM_APPLICATION_ID NUMBER,
420 p_PROGRAM_ID NUMBER,
421 p_PROGRAM_UPDATE_DATE DATE,
422 p_LEAD_NUMBER VARCHAR2,
423 p_STATUS_CODE VARCHAR2,
424 p_CUSTOMER_ID NUMBER,
425 p_ADDRESS_ID NUMBER,
426 p_SOURCE_PROMOTION_ID NUMBER,
427 p_INITIATING_CONTACT_ID NUMBER,
428 p_ORIG_SYSTEM_REFERENCE VARCHAR2,
429 p_CONTACT_ROLE_CODE VARCHAR2,
430 p_CHANNEL_CODE VARCHAR2,
431 p_BUDGET_AMOUNT NUMBER,
432 p_CURRENCY_CODE VARCHAR2,
433 p_DECISION_TIMEFRAME_CODE VARCHAR2,
434 p_CLOSE_REASON VARCHAR2,
435 p_LEAD_RANK_ID NUMBER,
436 p_LEAD_RANK_CODE VARCHAR2,
437 p_PARENT_PROJECT VARCHAR2,
438 p_DESCRIPTION VARCHAR2,
439 p_ATTRIBUTE_CATEGORY VARCHAR2,
440 p_ATTRIBUTE1 VARCHAR2,
441 p_ATTRIBUTE2 VARCHAR2,
442 p_ATTRIBUTE3 VARCHAR2,
443 p_ATTRIBUTE4 VARCHAR2,
444 p_ATTRIBUTE5 VARCHAR2,
445 p_ATTRIBUTE6 VARCHAR2,
446 p_ATTRIBUTE7 VARCHAR2,
447 p_ATTRIBUTE8 VARCHAR2,
448 p_ATTRIBUTE9 VARCHAR2,
449 p_ATTRIBUTE10 VARCHAR2,
450 p_ATTRIBUTE11 VARCHAR2,
451 p_ATTRIBUTE12 VARCHAR2,
452 p_ATTRIBUTE13 VARCHAR2,
453 p_ATTRIBUTE14 VARCHAR2,
454 p_ATTRIBUTE15 VARCHAR2,
455 p_ASSIGN_TO_PERSON_ID NUMBER,
456 p_ASSIGN_TO_SALESFORCE_ID NUMBER,
457 p_ASSIGN_SALES_GROUP_ID NUMBER,
458 p_ASSIGN_DATE DATE,
459 p_BUDGET_STATUS_CODE VARCHAR2,
460 p_ACCEPT_FLAG VARCHAR2,
461 p_VEHICLE_RESPONSE_CODE VARCHAR2,
462 p_TOTAL_SCORE NUMBER,
463 p_SCORECARD_ID NUMBER,
464 p_KEEP_FLAG VARCHAR2,
465 p_URGENT_FLAG VARCHAR2,
466 p_IMPORT_FLAG VARCHAR2,
467 p_REJECT_REASON_CODE VARCHAR2,
468 p_DELETED_FLAG VARCHAR2,
469 p_OFFER_ID NUMBER,
470 p_QUALIFIED_FLAG VARCHAR2,
471 p_ORIG_SYSTEM_CODE VARCHAR2,
472 -- p_SECURITY_GROUP_ID NUMBER,
473 p_INC_PARTNER_PARTY_ID NUMBER,
474 p_INC_PARTNER_RESOURCE_ID NUMBER,
475 p_PRM_EXEC_SPONSOR_FLAG VARCHAR2,
476 p_PRM_PRJ_LEAD_IN_PLACE_FLAG VARCHAR2,
477 p_PRM_SALES_LEAD_TYPE VARCHAR2,
478 p_PRM_IND_CLASSIFICATION_CODE VARCHAR2,
479 p_PRM_ASSIGNMENT_TYPE VARCHAR2,
480 p_AUTO_ASSIGNMENT_TYPE VARCHAR2,
481 p_PRIMARY_CONTACT_PARTY_ID NUMBER,
482 -- bug 2098158
483 p_PRIMARY_CNT_PERSON_PARTY_ID NUMBER,
484 p_PRIMARY_CONTACT_PHONE_ID NUMBER,
485 -- new columns for CAPRI lead referral
486 p_REFERRED_BY NUMBER,
490 p_REF_COMM_LTR_STATUS VARCHAR2,
487 p_REFERRAL_TYPE VARCHAR2,
488 p_REFERRAL_STATUS VARCHAR2,
489 p_REF_DECLINE_REASON VARCHAR2,
491 p_REF_ORDER_NUMBER NUMBER,
492 p_REF_ORDER_AMT NUMBER,
493 p_REF_COMM_AMT NUMBER,
494 -- bug No.2341515, 2368075
495 p_LEAD_DATE DATE ,
496 p_SOURCE_SYSTEM VARCHAR2,
497 p_COUNTRY VARCHAR2,
498 -- 11.5.9
499 p_TOTAL_AMOUNT NUMBER ,
500 p_EXPIRATION_DATE DATE ,
501 p_LEAD_RANK_IND VARCHAR2,
502 p_LEAD_ENGINE_RUN_DATE DATE,
503 p_CURRENT_REROUTES NUMBER
504
505 -- new columns for appsperf CRMAP denorm project bug 2928041
506
507 , p_STATUS_OPEN_FLAG VARCHAR2,
508 p_LEAD_RANK_SCORE NUMBER
509
510
511 -- 11.5.10 - ckapoor : new columns
512
513 , p_MARKETING_SCORE NUMBER
514 , p_INTERACTION_SCORE NUMBER
515 , p_SOURCE_PRIMARY_REFERENCE VARCHAR2
516 , p_SOURCE_SECONDARY_REFERENCE VARCHAR2
517 , p_SALES_METHODOLOGY_ID NUMBER
518 , p_SALES_STAGE_ID NUMBER
519
520
521
522 )
523 IS
524 /*
525 l_obj_verno number;
526
527 cursor c_obj_verno is
528 select object_version_number
529 from as_sales_leads
530 where sales_lead_id = p_SALES_LEAD_ID;
531 */
532 BEGIN
533 Update AS_SALES_LEADS
534 SET
535 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,
536 LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
537 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM,
538 LAST_UPDATED_BY, p_LAST_UPDATED_BY),
539 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE,
540 CREATION_DATE, p_CREATION_DATE),
541 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY,
542 p_CREATED_BY),
543 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,
544 LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
545 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID,
546 p_REQUEST_ID),
547 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID,
548 FND_API.G_MISS_NUM,
549 PROGRAM_APPLICATION_ID,
550 p_PROGRAM_APPLICATION_ID),
551 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID,
552 p_PROGRAM_ID),
553 PROGRAM_UPDATE_DATE = decode( p_PROGRAM_UPDATE_DATE,
554 FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE,
555 p_PROGRAM_UPDATE_DATE),
556 LEAD_NUMBER = decode( p_LEAD_NUMBER, FND_API.G_MISS_CHAR, LEAD_NUMBER,
557 p_LEAD_NUMBER),
558 STATUS_CODE = decode( p_STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE,
559 p_STATUS_CODE),
560 CUSTOMER_ID = decode( p_CUSTOMER_ID, FND_API.G_MISS_NUM, CUSTOMER_ID,
561 p_CUSTOMER_ID),
562 ADDRESS_ID = decode( p_ADDRESS_ID, FND_API.G_MISS_NUM, ADDRESS_ID,
563 p_ADDRESS_ID),
564 SOURCE_PROMOTION_ID = decode( p_SOURCE_PROMOTION_ID,FND_API.G_MISS_NUM, SOURCE_PROMOTION_ID,
565 p_SOURCE_PROMOTION_ID),
566 INITIATING_CONTACT_ID = decode( p_INITIATING_CONTACT_ID,
567 FND_API.G_MISS_NUM,
568 INITIATING_CONTACT_ID,
569 p_INITIATING_CONTACT_ID),
570 ORIG_SYSTEM_REFERENCE = decode( p_ORIG_SYSTEM_REFERENCE,
571 FND_API.G_MISS_CHAR,
572 ORIG_SYSTEM_REFERENCE,
576 CHANNEL_CODE = decode( p_CHANNEL_CODE, FND_API.G_MISS_CHAR,
573 p_ORIG_SYSTEM_REFERENCE),
574 CONTACT_ROLE_CODE = decode( p_CONTACT_ROLE_CODE, FND_API.G_MISS_CHAR,
575 CONTACT_ROLE_CODE, p_CONTACT_ROLE_CODE),
577 CHANNEL_CODE, p_CHANNEL_CODE),
578 BUDGET_AMOUNT = decode( p_BUDGET_AMOUNT, FND_API.G_MISS_NUM,
579 BUDGET_AMOUNT, p_BUDGET_AMOUNT),
580 CURRENCY_CODE = decode( p_CURRENCY_CODE, FND_API.G_MISS_CHAR,
581 CURRENCY_CODE, p_CURRENCY_CODE),
582 DECISION_TIMEFRAME_CODE = decode( p_DECISION_TIMEFRAME_CODE,
583 FND_API.G_MISS_CHAR,
584 DECISION_TIMEFRAME_CODE,
585 p_DECISION_TIMEFRAME_CODE),
586 CLOSE_REASON = decode( p_CLOSE_REASON, FND_API.G_MISS_CHAR,
587 CLOSE_REASON, p_CLOSE_REASON),
588 LEAD_RANK_ID = decode( p_LEAD_RANK_ID, FND_API.G_MISS_NUM,
589 LEAD_RANK_ID, p_LEAD_RANK_ID),
590 LEAD_RANK_CODE = decode( p_LEAD_RANK_CODE, FND_API.G_MISS_CHAR,
591 LEAD_RANK_CODE, p_LEAD_RANK_CODE),
592 PARENT_PROJECT = decode( p_PARENT_PROJECT, FND_API.G_MISS_CHAR,
593 PARENT_PROJECT, p_PARENT_PROJECT),
594 DESCRIPTION = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR,
595 DESCRIPTION, p_DESCRIPTION),
596 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR,
597 ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
598 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1,
599 p_ATTRIBUTE1),
600 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2,
601 p_ATTRIBUTE2),
602 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3,
603 p_ATTRIBUTE3),
604 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4,
605 p_ATTRIBUTE4),
606 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5,
607 p_ATTRIBUTE5),
608 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6,
609 p_ATTRIBUTE6),
610 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7,
611 p_ATTRIBUTE7),
612 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8,
613 p_ATTRIBUTE8),
614 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9,
618 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11,
615 p_ATTRIBUTE9),
616 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10,
617 p_ATTRIBUTE10),
619 p_ATTRIBUTE11),
620 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12,
621 p_ATTRIBUTE12),
622 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13,
623 p_ATTRIBUTE13),
624 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14,
625 p_ATTRIBUTE14),
626 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15,
627 p_ATTRIBUTE15),
628 ASSIGN_TO_PERSON_ID = decode( p_ASSIGN_TO_PERSON_ID,FND_API.G_MISS_NUM,
629 ASSIGN_TO_PERSON_ID,
630 p_ASSIGN_TO_PERSON_ID),
631 ASSIGN_TO_SALESFORCE_ID = decode( p_ASSIGN_TO_SALESFORCE_ID,
632 FND_API.G_MISS_NUM,
633 ASSIGN_TO_SALESFORCE_ID,
634 p_ASSIGN_TO_SALESFORCE_ID),
635 ASSIGN_SALES_GROUP_ID = decode( p_ASSIGN_SALES_GROUP_ID,
636 FND_API.G_MISS_NUM,
637 ASSIGN_SALES_GROUP_ID,
638 p_ASSIGN_SALES_GROUP_ID),
639 ASSIGN_DATE = decode( p_ASSIGN_DATE, FND_API.G_MISS_DATE, ASSIGN_DATE,
640 p_ASSIGN_DATE),
641 BUDGET_STATUS_CODE = decode( p_BUDGET_STATUS_CODE, FND_API.G_MISS_CHAR,
642 BUDGET_STATUS_CODE, p_BUDGET_STATUS_CODE),
643 ACCEPT_FLAG = decode( p_ACCEPT_FLAG, FND_API.G_MISS_CHAR, ACCEPT_FLAG,
644 p_ACCEPT_FLAG),
645 VEHICLE_RESPONSE_CODE = decode( p_VEHICLE_RESPONSE_CODE,
646 FND_API.G_MISS_CHAR,
647 VEHICLE_RESPONSE_CODE,
648 p_VEHICLE_RESPONSE_CODE),
649 TOTAL_SCORE = decode( p_TOTAL_SCORE, FND_API.G_MISS_NUM, TOTAL_SCORE,
650 p_TOTAL_SCORE),
651 SCORECARD_ID = decode( p_SCORECARD_ID, FND_API.G_MISS_NUM,SCORECARD_ID,
652 p_SCORECARD_ID),
653 KEEP_FLAG = decode( p_KEEP_FLAG, FND_API.G_MISS_CHAR, KEEP_FLAG,
654 p_KEEP_FLAG),
655 URGENT_FLAG = decode( p_URGENT_FLAG, FND_API.G_MISS_CHAR, URGENT_FLAG,
656 p_URGENT_FLAG),
657 IMPORT_FLAG = decode( p_IMPORT_FLAG, FND_API.G_MISS_CHAR, IMPORT_FLAG,
658 p_IMPORT_FLAG),
662 DELETED_FLAG, p_DELETED_FLAG),
659 REJECT_REASON_CODE = decode( p_REJECT_REASON_CODE, FND_API.G_MISS_CHAR,
660 REJECT_REASON_CODE, p_REJECT_REASON_CODE),
661 DELETED_FLAG = decode( p_DELETED_FLAG, FND_API.G_MISS_CHAR,
663 OFFER_ID = decode(p_OFFER_ID,FND_API.G_MISS_NUM, OFFER_ID, p_OFFER_ID),
664 QUALIFIED_FLAG = decode( p_QUALIFIED_FLAG, FND_API.G_MISS_CHAR,
665 QUALIFIED_FLAG, p_QUALIFIED_FLAG),
666 ORIG_SYSTEM_CODE = decode( p_ORIG_SYSTEM_CODE, FND_API.G_MISS_CHAR,
667 ORIG_SYSTEM_CODE, p_ORIG_SYSTEM_CODE),
668 -- SECURITY_GROUP_ID = decode(p_SECURITY_GROUP_ID, FND_API.G_MISS_NUM,
669 -- SECURITY_GROUP_ID, p_SECURITY_GROUP_ID),
670 INCUMBENT_PARTNER_PARTY_ID = decode(p_INC_PARTNER_PARTY_ID,
671 FND_API.G_MISS_NUM,
672 INCUMBENT_PARTNER_PARTY_ID,
673 p_INC_PARTNER_PARTY_ID),
674 INCUMBENT_PARTNER_RESOURCE_ID = decode(p_INC_PARTNER_RESOURCE_ID,
675 FND_API.G_MISS_NUM,
676 INCUMBENT_PARTNER_RESOURCE_ID,
677 p_INC_PARTNER_RESOURCE_ID),
678 PRM_EXEC_SPONSOR_FLAG = decode(p_PRM_EXEC_SPONSOR_FLAG,
679 FND_API.G_MISS_CHAR,
680 PRM_EXEC_SPONSOR_FLAG,
681 p_PRM_EXEC_SPONSOR_FLAG),
682 PRM_PRJ_LEAD_IN_PLACE_FLAG = decode(p_PRM_PRJ_LEAD_IN_PLACE_FLAG,
683 FND_API.G_MISS_CHAR,
684 PRM_PRJ_LEAD_IN_PLACE_FLAG,
685 p_PRM_PRJ_LEAD_IN_PLACE_FLAG),
686 PRM_SALES_LEAD_TYPE = decode(p_PRM_SALES_LEAD_TYPE,
687 FND_API.G_MISS_CHAR,
688 PRM_SALES_LEAD_TYPE,
689 p_PRM_SALES_LEAD_TYPE),
690 PRM_IND_CLASSIFICATION_CODE = decode(p_PRM_IND_CLASSIFICATION_CODE,
691 FND_API.G_MISS_CHAR,
692 PRM_IND_CLASSIFICATION_CODE,
693 p_PRM_IND_CLASSIFICATION_CODE),
694 PRM_ASSIGNMENT_TYPE = decode(p_PRM_ASSIGNMENT_TYPE,
695 FND_API.G_MISS_CHAR,
696 PRM_ASSIGNMENT_TYPE,
697 p_PRM_ASSIGNMENT_TYPE),
698 AUTO_ASSIGNMENT_TYPE = decode(p_AUTO_ASSIGNMENT_TYPE,
699 FND_API.G_MISS_CHAR,
700 AUTO_ASSIGNMENT_TYPE,
701 p_AUTO_ASSIGNMENT_TYPE),
702
703 PRIMARY_CONTACT_PARTY_ID = decode(p_PRIMARY_CONTACT_PARTY_ID,
704 FND_API.G_MISS_NUM,
705 PRIMARY_CONTACT_PARTY_ID,
706 p_PRIMARY_CONTACT_PARTY_ID),
707
708 PRIMARY_CNT_PERSON_PARTY_ID = decode(p_PRIMARY_CNT_PERSON_PARTY_ID,
709 FND_API.G_MISS_NUM,
710 PRIMARY_CNT_PERSON_PARTY_ID,
711 p_PRIMARY_CNT_PERSON_PARTY_ID),
712
713 PRIMARY_CONTACT_PHONE_ID = decode(p_PRIMARY_CONTACT_PHONE_ID,
714 FND_API.G_MISS_NUM,
715 PRIMARY_CONTACT_PHONE_ID,
716 p_PRIMARY_CONTACT_PHONE_ID),
717
718 REFERRED_BY = decode(p_REFERRED_BY,
719 FND_API.G_MISS_NUM,
723 REFERRAL_TYPE = decode(p_REFERRAL_TYPE,
720 REFERRED_BY,
721 p_REFERRED_BY),
722
724 FND_API.G_MISS_CHAR,
725 REFERRAL_TYPE,
726 p_REFERRAL_TYPE),
727
728
729 REFERRAL_STATUS = decode(p_REFERRAL_STATUS,
730 FND_API.G_MISS_CHAR,
731 REFERRAL_STATUS,
732 p_REFERRAL_STATUS),
733
734
735 REF_DECLINE_REASON = decode(p_REF_DECLINE_REASON,
736 FND_API.G_MISS_CHAR,
737 REF_DECLINE_REASON,
738 p_REF_DECLINE_REASON),
739
740
741 REF_COMM_LTR_STATUS = decode(p_REF_COMM_LTR_STATUS,
742 FND_API.G_MISS_CHAR,
743 REF_COMM_LTR_STATUS,
744 p_REF_COMM_LTR_STATUS),
748 FND_API.G_MISS_NUM,
745
746
747 REF_ORDER_NUMBER = decode(p_REF_ORDER_NUMBER,
749 REF_ORDER_NUMBER,
750 p_REF_ORDER_NUMBER),
751
752 REF_ORDER_AMT = decode(p_REF_ORDER_AMT,
753 FND_API.G_MISS_NUM,
754 REF_ORDER_AMT,
755 p_REF_ORDER_AMT),
756
757
758 REF_COMM_AMT = decode(p_REF_COMM_AMT,
759 FND_API.G_MISS_NUM,
760 REF_COMM_AMT,
761 p_REF_COMM_AMT),
762
763 TRUNC_CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE,
764 TRUNC_CREATION_DATE, TRUNC(p_CREATION_DATE)),
765
766 LEAD_DATE = decode( p_LEAD_DATE , FND_API.G_MISS_DATE,
767 LEAD_DATE , p_LEAD_DATE ),
768
769 SOURCE_SYSTEM = decode( p_SOURCE_SYSTEM , FND_API.G_MISS_CHAR,
770 SOURCE_SYSTEM , p_SOURCE_SYSTEM ),
771
772 COUNTRY = decode( p_COUNTRY , FND_API.G_MISS_CHAR,
773 COUNTRY , p_COUNTRY ),
774
775 TOTAL_AMOUNT = decode(p_TOTAL_AMOUNT, FND_API.G_MISS_NUM,TOTAL_AMOUNT,p_TOTAL_AMOUNT),
776
777 EXPIRATION_DATE = decode( p_EXPIRATION_DATE , FND_API.G_MISS_DATE,
778 EXPIRATION_DATE , p_EXPIRATION_DATE ),
779
780 LEAD_RANK_IND = decode(p_LEAD_RANK_IND, FND_API.G_MISS_CHAR,LEAD_RANK_IND,p_LEAD_RANK_IND),
781
782 LEAD_ENGINE_RUN_DATE = decode( p_LEAD_ENGINE_RUN_DATE, FND_API.G_MISS_DATE,
783 LEAD_ENGINE_RUN_DATE , p_LEAD_ENGINE_RUN_DATE ),
784
785 CURRENT_REROUTES = decode(p_CURRENT_REROUTES, FND_API.G_MISS_NUM,CURRENT_REROUTES,p_CURRENT_REROUTES)
786
787 -- new columns for appsperf CRMAP denorm project bug 2928041
788
789 , STATUS_OPEN_FLAG = decode( p_STATUS_OPEN_FLAG, FND_API.G_MISS_CHAR,
790 STATUS_OPEN_FLAG, p_STATUS_OPEN_FLAG)
791
792 , LEAD_RANK_SCORE = decode(p_LEAD_RANK_SCORE,
793 FND_API.G_MISS_NUM,
794 LEAD_RANK_SCORE,
795 p_LEAD_RANK_SCORE)
796
797 -- 11.5.10 - ckapoor : new columns
798
799 , MARKETING_SCORE = decode( p_MARKETING_SCORE, FND_API.G_MISS_NUM,
800 MARKETING_SCORE, p_MARKETING_SCORE)
801
802 , INTERACTION_SCORE = decode(p_INTERACTION_SCORE,
803 FND_API.G_MISS_NUM,
804 INTERACTION_SCORE,
805 p_INTERACTION_SCORE)
806
807 , SOURCE_PRIMARY_REFERENCE = decode( p_SOURCE_PRIMARY_REFERENCE, FND_API.G_MISS_CHAR,
808 SOURCE_PRIMARY_REFERENCE, p_SOURCE_PRIMARY_REFERENCE)
809
810 , SOURCE_SECONDARY_REFERENCE = decode(p_SOURCE_SECONDARY_REFERENCE,
811 FND_API.G_MISS_CHAR,
812 SOURCE_SECONDARY_REFERENCE,
813 p_SOURCE_SECONDARY_REFERENCE)
814
815 , SALES_METHODOLOGY_ID = decode( p_SALES_METHODOLOGY_ID, FND_API.G_MISS_NUM,
816 SALES_METHODOLOGY_ID, p_SALES_METHODOLOGY_ID)
817
818 , SALES_STAGE_ID = decode(p_SALES_STAGE_ID,
819 FND_API.G_MISS_NUM,
820 SALES_STAGE_ID,
821 p_SALES_STAGE_ID)
822 , object_version_number = decode(object_version_number, null, 1, object_version_number+1)
823
824
825 WHERE SALES_LEAD_ID = p_SALES_LEAD_ID;
826
827 /* open c_obj_verno;
828 fetch c_obj_verno into l_obj_verno;
829 close c_obj_verno;
830
831 update as_sales_leads
832 set object_version_number = decode(l_obj_verno, null, 1, l_obj_verno+1)
833 where sales_lead_id = p_SALES_LEAD_ID;
834 */
835 If (SQL%NOTFOUND) then
836 RAISE NO_DATA_FOUND;
837 End If;
838 END Sales_Lead_Update_Row;
839
840 PROCEDURE Sales_Lead_Lock_Row(
841 p_SALES_LEAD_ID NUMBER,
842 p_LAST_UPDATE_DATE DATE,
843 p_LAST_UPDATED_BY NUMBER,
844 p_CREATION_DATE DATE,
845 p_CREATED_BY NUMBER,
846 p_LAST_UPDATE_LOGIN NUMBER,
847 p_REQUEST_ID NUMBER,
848 p_PROGRAM_APPLICATION_ID NUMBER,
849 p_PROGRAM_ID NUMBER,
850 p_PROGRAM_UPDATE_DATE DATE,
851 p_LEAD_NUMBER VARCHAR2,
852 p_STATUS_CODE VARCHAR2,
853 p_CUSTOMER_ID NUMBER,
854 p_ADDRESS_ID NUMBER,
855 p_SOURCE_PROMOTION_ID NUMBER,
856 p_INITIATING_CONTACT_ID NUMBER,
857 p_ORIG_SYSTEM_REFERENCE VARCHAR2,
858 p_CONTACT_ROLE_CODE VARCHAR2,
859 p_CHANNEL_CODE VARCHAR2,
860 p_BUDGET_AMOUNT NUMBER,
861 p_CURRENCY_CODE VARCHAR2,
862 p_DECISION_TIMEFRAME_CODE VARCHAR2,
863 p_CLOSE_REASON VARCHAR2,
864 p_LEAD_RANK_ID NUMBER,
865 p_LEAD_RANK_CODE VARCHAR2,
866 p_PARENT_PROJECT VARCHAR2,
867 p_DESCRIPTION VARCHAR2,
868 p_ATTRIBUTE_CATEGORY VARCHAR2,
869 p_ATTRIBUTE1 VARCHAR2,
870 p_ATTRIBUTE2 VARCHAR2,
871 p_ATTRIBUTE3 VARCHAR2,
872 p_ATTRIBUTE4 VARCHAR2,
873 p_ATTRIBUTE5 VARCHAR2,
874 p_ATTRIBUTE6 VARCHAR2,
875 p_ATTRIBUTE7 VARCHAR2,
876 p_ATTRIBUTE8 VARCHAR2,
877 p_ATTRIBUTE9 VARCHAR2,
878 p_ATTRIBUTE10 VARCHAR2,
884 p_ASSIGN_TO_PERSON_ID NUMBER,
879 p_ATTRIBUTE11 VARCHAR2,
880 p_ATTRIBUTE12 VARCHAR2,
881 p_ATTRIBUTE13 VARCHAR2,
882 p_ATTRIBUTE14 VARCHAR2,
883 p_ATTRIBUTE15 VARCHAR2,
885 p_ASSIGN_TO_SALESFORCE_ID NUMBER,
886 p_ASSIGN_SALES_GROUP_ID NUMBER,
887 p_ASSIGN_DATE DATE,
888 p_BUDGET_STATUS_CODE VARCHAR2,
889 p_ACCEPT_FLAG VARCHAR2,
890 p_VEHICLE_RESPONSE_CODE VARCHAR2,
891 p_TOTAL_SCORE NUMBER,
892 p_SCORECARD_ID NUMBER,
893 p_KEEP_FLAG VARCHAR2,
894 p_URGENT_FLAG VARCHAR2,
895 p_IMPORT_FLAG VARCHAR2,
896 p_REJECT_REASON_CODE VARCHAR2,
897 p_DELETED_FLAG VARCHAR2,
898 p_OFFER_ID NUMBER,
899 p_QUALIFIED_FLAG VARCHAR2,
900 p_ORIG_SYSTEM_CODE VARCHAR2,
901 -- p_SECURITY_GROUP_ID NUMBER,
902 p_INC_PARTNER_PARTY_ID NUMBER,
903 p_INC_PARTNER_RESOURCE_ID NUMBER,
904 p_PRM_EXEC_SPONSOR_FLAG VARCHAR2,
905 p_PRM_PRJ_LEAD_IN_PLACE_FLAG VARCHAR2,
906 p_PRM_SALES_LEAD_TYPE VARCHAR2,
907 p_PRM_IND_CLASSIFICATION_CODE VARCHAR2,
908 p_PRM_ASSIGNMENT_TYPE VARCHAR2,
909 p_AUTO_ASSIGNMENT_TYPE VARCHAR2,
910 p_PRIMARY_CONTACT_PARTY_ID NUMBER,
911 -- bug 2098158
912 p_PRIMARY_CNT_PERSON_PARTY_ID NUMBER,
913 p_PRIMARY_CONTACT_PHONE_ID NUMBER,
914 -- new columns for CAPRI lead referral
915
916 p_REFERRED_BY NUMBER,
917 p_REFERRAL_TYPE VARCHAR2,
918 p_REFERRAL_STATUS VARCHAR2,
919 p_REF_DECLINE_REASON VARCHAR2,
920 p_REF_COMM_LTR_STATUS VARCHAR2,
921 p_REF_ORDER_NUMBER NUMBER,
922 p_REF_ORDER_AMT NUMBER,
923 p_REF_COMM_AMT NUMBER,
924 -- bug No.2341515, 2368075
925 p_LEAD_DATE DATE ,
926 p_SOURCE_SYSTEM VARCHAR2,
927 p_COUNTRY VARCHAR2,
928 -- 11.5.9
929 p_TOTAL_AMOUNT NUMBER,
930 p_EXPIRATION_DATE DATE,
931 p_LEAD_RANK_IND VARCHAR2,
932 p_LEAD_ENGINE_RUN_DATE DATE,
933 p_CURRENT_REROUTES NUMBER
934
935 -- new columns for appsperf CRMAP denorm project bug 2928041
936
937 , p_STATUS_OPEN_FLAG VARCHAR2,
938 p_LEAD_RANK_SCORE NUMBER
939
940
941 -- 11.5.10 - ckapoor : new columns
942
943 , p_MARKETING_SCORE NUMBER
944 , p_INTERACTION_SCORE NUMBER
945 , p_SOURCE_PRIMARY_REFERENCE VARCHAR2
946 , p_SOURCE_SECONDARY_REFERENCE VARCHAR2
947 , p_SALES_METHODOLOGY_ID NUMBER
948 , p_SALES_STAGE_ID NUMBER
949
950
951
952
953
954
955
956
957
958
959 )
960
961 IS
962 CURSOR C IS
963 SELECT *
964 FROM AS_SALES_LEADS
965 WHERE SALES_LEAD_ID = p_SALES_LEAD_ID
966 FOR UPDATE of SALES_LEAD_ID NOWAIT;
967 Recinfo C%ROWTYPE;
968 BEGIN
969 OPEN C;
970 FETCH C INTO Recinfo;
971 If (C%NOTFOUND) then
972 CLOSE C;
973 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
974 APP_EXCEPTION.RAISE_EXCEPTION;
975 End If;
976 CLOSE C;
977 if (
978 ( Recinfo.SALES_LEAD_ID = p_SALES_LEAD_ID)
979 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
980 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
981 AND ( p_LAST_UPDATE_DATE IS NULL )))
982 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
983 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
984 AND ( p_LAST_UPDATED_BY IS NULL )))
985 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
986 OR ( ( Recinfo.CREATION_DATE IS NULL )
987 AND ( p_CREATION_DATE IS NULL )))
988 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
989 OR ( ( Recinfo.CREATED_BY IS NULL )
990 AND ( p_CREATED_BY IS NULL )))
991 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
992 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
993 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
994 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
995 OR ( ( Recinfo.REQUEST_ID IS NULL )
996 AND ( p_REQUEST_ID IS NULL )))
997 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
998 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
999 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
1000 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
1001 OR ( ( Recinfo.PROGRAM_ID IS NULL )
1002 AND ( p_PROGRAM_ID IS NULL )))
1003 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
1007 OR ( ( Recinfo.LEAD_NUMBER IS NULL )
1004 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
1005 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
1006 AND ( ( Recinfo.LEAD_NUMBER = p_LEAD_NUMBER)
1008 AND ( p_LEAD_NUMBER IS NULL )))
1009 AND ( ( Recinfo.STATUS_CODE = p_STATUS_CODE)
1010 OR ( ( Recinfo.STATUS_CODE IS NULL )
1011 AND ( p_STATUS_CODE IS NULL )))
1012 AND ( ( Recinfo.CUSTOMER_ID = p_CUSTOMER_ID)
1013 OR ( ( Recinfo.CUSTOMER_ID IS NULL )
1014 AND ( p_CUSTOMER_ID IS NULL )))
1015 AND ( ( Recinfo.ADDRESS_ID = p_ADDRESS_ID)
1016 OR ( ( Recinfo.ADDRESS_ID IS NULL )
1017 AND ( p_ADDRESS_ID IS NULL )))
1018 AND ( ( Recinfo.SOURCE_PROMOTION_ID = p_SOURCE_PROMOTION_ID)
1019 OR ( ( Recinfo.SOURCE_PROMOTION_ID IS NULL )
1020 AND ( p_SOURCE_PROMOTION_ID IS NULL )))
1021 AND ( ( Recinfo.INITIATING_CONTACT_ID = p_INITIATING_CONTACT_ID)
1022 OR ( ( Recinfo.INITIATING_CONTACT_ID IS NULL )
1023 AND ( p_INITIATING_CONTACT_ID IS NULL )))
1024 AND ( ( Recinfo.ORIG_SYSTEM_REFERENCE = p_ORIG_SYSTEM_REFERENCE)
1025 OR ( ( Recinfo.ORIG_SYSTEM_REFERENCE IS NULL )
1026 AND ( p_ORIG_SYSTEM_REFERENCE IS NULL )))
1027 AND ( ( Recinfo.CONTACT_ROLE_CODE = p_CONTACT_ROLE_CODE)
1028 OR ( ( Recinfo.CONTACT_ROLE_CODE IS NULL )
1029 AND ( p_CONTACT_ROLE_CODE IS NULL )))
1030 AND ( ( Recinfo.CHANNEL_CODE = p_CHANNEL_CODE)
1031 OR ( ( Recinfo.CHANNEL_CODE IS NULL )
1032 AND ( p_CHANNEL_CODE IS NULL )))
1033 AND ( ( Recinfo.BUDGET_AMOUNT = p_BUDGET_AMOUNT)
1034 OR ( ( Recinfo.BUDGET_AMOUNT IS NULL )
1035 AND ( p_BUDGET_AMOUNT IS NULL )))
1036 AND ( ( Recinfo.CURRENCY_CODE = p_CURRENCY_CODE)
1040 OR ( ( Recinfo.DECISION_TIMEFRAME_CODE IS NULL )
1037 OR ( ( Recinfo.CURRENCY_CODE IS NULL )
1038 AND ( p_CURRENCY_CODE IS NULL )))
1039 AND ( ( Recinfo.DECISION_TIMEFRAME_CODE = p_DECISION_TIMEFRAME_CODE)
1041 AND ( p_DECISION_TIMEFRAME_CODE IS NULL )))
1042 AND ( ( Recinfo.CLOSE_REASON = p_CLOSE_REASON)
1043 OR ( ( Recinfo.CLOSE_REASON IS NULL )
1044 AND ( p_CLOSE_REASON IS NULL )))
1045 AND ( ( Recinfo.LEAD_RANK_ID = p_LEAD_RANK_ID)
1046 OR ( ( Recinfo.LEAD_RANK_ID IS NULL )
1047 AND ( p_LEAD_RANK_ID IS NULL )))
1048 AND ( ( Recinfo.LEAD_RANK_CODE = p_LEAD_RANK_CODE)
1049 OR ( ( Recinfo.LEAD_RANK_CODE IS NULL )
1050 AND ( p_LEAD_RANK_CODE IS NULL )))
1051 AND ( ( Recinfo.PARENT_PROJECT = p_PARENT_PROJECT)
1052 OR ( ( Recinfo.PARENT_PROJECT IS NULL )
1053 AND ( p_PARENT_PROJECT IS NULL )))
1054 AND ( ( Recinfo.DESCRIPTION = p_DESCRIPTION)
1055 OR ( ( Recinfo.DESCRIPTION IS NULL )
1056 AND ( p_DESCRIPTION IS NULL )))
1057 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
1058 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
1059 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
1060 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
1061 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
1062 AND ( p_ATTRIBUTE1 IS NULL )))
1063 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
1064 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
1065 AND ( p_ATTRIBUTE2 IS NULL )))
1066 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
1067 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
1068 AND ( p_ATTRIBUTE3 IS NULL )))
1069 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
1070 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
1071 AND ( p_ATTRIBUTE4 IS NULL )))
1072 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
1073 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
1077 AND ( p_ATTRIBUTE6 IS NULL )))
1074 AND ( p_ATTRIBUTE5 IS NULL )))
1075 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
1076 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
1078 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
1079 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
1080 AND ( p_ATTRIBUTE7 IS NULL )))
1081 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
1082 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
1083 AND ( p_ATTRIBUTE8 IS NULL )))
1084 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
1085 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
1086 AND ( p_ATTRIBUTE9 IS NULL )))
1087 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
1088 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
1089 AND ( p_ATTRIBUTE10 IS NULL )))
1090 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
1091 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
1092 AND ( p_ATTRIBUTE11 IS NULL )))
1093 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
1094 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
1095 AND ( p_ATTRIBUTE12 IS NULL )))
1096 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
1097 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
1098 AND ( p_ATTRIBUTE13 IS NULL )))
1099 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
1100 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
1101 AND ( p_ATTRIBUTE14 IS NULL )))
1102 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
1103 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
1104 AND ( p_ATTRIBUTE15 IS NULL )))
1105 AND ( ( Recinfo.ASSIGN_TO_PERSON_ID = p_ASSIGN_TO_PERSON_ID)
1106 OR ( ( Recinfo.ASSIGN_TO_PERSON_ID IS NULL )
1107 AND ( p_ASSIGN_TO_PERSON_ID IS NULL )))
1108 AND ( ( Recinfo.ASSIGN_TO_SALESFORCE_ID = p_ASSIGN_TO_SALESFORCE_ID)
1109 OR ( ( Recinfo.ASSIGN_TO_SALESFORCE_ID IS NULL )
1110 AND ( p_ASSIGN_TO_SALESFORCE_ID IS NULL )))
1111 AND ( ( Recinfo.ASSIGN_SALES_GROUP_ID = p_ASSIGN_SALES_GROUP_ID)
1112 OR ( ( Recinfo.ASSIGN_SALES_GROUP_ID IS NULL )
1113 AND ( p_ASSIGN_SALES_GROUP_ID IS NULL )))
1114 AND ( ( Recinfo.ASSIGN_DATE = p_ASSIGN_DATE)
1115 OR ( ( Recinfo.ASSIGN_DATE IS NULL )
1116 AND ( p_ASSIGN_DATE IS NULL )))
1117 AND ( ( Recinfo.BUDGET_STATUS_CODE = p_BUDGET_STATUS_CODE)
1118 OR ( ( Recinfo.BUDGET_STATUS_CODE IS NULL )
1119 AND ( p_BUDGET_STATUS_CODE IS NULL )))
1120 AND ( ( Recinfo.ACCEPT_FLAG = p_ACCEPT_FLAG)
1121 OR ( ( Recinfo.ACCEPT_FLAG IS NULL )
1122 AND ( p_ACCEPT_FLAG IS NULL )))
1123 AND ( ( Recinfo.VEHICLE_RESPONSE_CODE = p_VEHICLE_RESPONSE_CODE)
1124 OR ( ( Recinfo.VEHICLE_RESPONSE_CODE IS NULL )
1125 AND ( p_VEHICLE_RESPONSE_CODE IS NULL )))
1126 AND ( ( Recinfo.TOTAL_SCORE = p_TOTAL_SCORE)
1127 OR ( ( Recinfo.TOTAL_SCORE IS NULL )
1128 AND ( p_TOTAL_SCORE IS NULL )))
1129 AND ( ( Recinfo.SCORECARD_ID = p_SCORECARD_ID)
1130 OR ( ( Recinfo.SCORECARD_ID IS NULL )
1131 AND ( p_SCORECARD_ID IS NULL )))
1132 AND ( ( Recinfo.KEEP_FLAG = p_KEEP_FLAG)
1133 OR ( ( Recinfo.KEEP_FLAG IS NULL )
1134 AND ( p_KEEP_FLAG IS NULL )))
1135 AND ( ( Recinfo.URGENT_FLAG = p_URGENT_FLAG)
1136 OR ( ( Recinfo.URGENT_FLAG IS NULL )
1137 AND ( p_URGENT_FLAG IS NULL )))
1138 AND ( ( Recinfo.IMPORT_FLAG = p_IMPORT_FLAG)
1139 OR ( ( Recinfo.IMPORT_FLAG IS NULL )
1140 AND ( p_IMPORT_FLAG IS NULL )))
1141 AND ( ( Recinfo.REJECT_REASON_CODE = p_REJECT_REASON_CODE)
1142 OR ( ( Recinfo.REJECT_REASON_CODE IS NULL )
1143 AND ( p_REJECT_REASON_CODE IS NULL )))
1144 AND ( ( Recinfo.DELETED_FLAG = p_DELETED_FLAG)
1145 OR ( ( Recinfo.DELETED_FLAG IS NULL )
1146 AND ( p_DELETED_FLAG IS NULL )))
1147 AND ( ( Recinfo.OFFER_ID = p_OFFER_ID)
1148 OR ( ( Recinfo.OFFER_ID IS NULL )
1149 AND ( p_OFFER_ID IS NULL )))
1150 AND ( ( Recinfo.QUALIFIED_FLAG = p_QUALIFIED_FLAG)
1151 OR ( ( Recinfo.QUALIFIED_FLAG IS NULL )
1152 AND ( p_QUALIFIED_FLAG IS NULL )))
1153 AND ( ( Recinfo.ORIG_SYSTEM_CODE = p_ORIG_SYSTEM_CODE)
1154 OR ( ( Recinfo.ORIG_SYSTEM_CODE IS NULL )
1155 AND ( p_ORIG_SYSTEM_CODE IS NULL )))
1156 -- AND ( ( Recinfo.SECURITY_GROUP_ID = p_SECURITY_GROUP_ID)
1160 OR ( ( Recinfo.INCUMBENT_PARTNER_PARTY_ID IS NULL )
1157 -- OR ( ( Recinfo.SECURITY_GROUP_ID IS NULL )
1158 -- AND ( p_SECURITY_GROUP_ID IS NULL )))
1159 AND ( (Recinfo.INCUMBENT_PARTNER_PARTY_ID=p_INC_PARTNER_PARTY_ID)
1164 OR ( ( Recinfo.INCUMBENT_PARTNER_RESOURCE_ID IS NULL )
1161 AND ( p_INC_PARTNER_PARTY_ID IS NULL )))
1162 AND ( ( Recinfo.INCUMBENT_PARTNER_RESOURCE_ID =
1163 p_INC_PARTNER_RESOURCE_ID)
1165 AND ( p_INC_PARTNER_RESOURCE_ID IS NULL )))
1166 AND ( ( Recinfo.PRM_EXEC_SPONSOR_FLAG =
1167 p_PRM_EXEC_SPONSOR_FLAG)
1168 OR ( ( Recinfo.PRM_EXEC_SPONSOR_FLAG IS NULL )
1169 AND ( p_PRM_EXEC_SPONSOR_FLAG IS NULL )))
1170 AND ( ( Recinfo.PRM_PRJ_LEAD_IN_PLACE_FLAG =
1171 p_PRM_PRJ_LEAD_IN_PLACE_FLAG)
1172 OR ( ( Recinfo.PRM_PRJ_LEAD_IN_PLACE_FLAG IS NULL )
1173 AND ( p_PRM_PRJ_LEAD_IN_PLACE_FLAG IS NULL )))
1174 AND ( ( Recinfo.PRM_SALES_LEAD_TYPE =
1175 p_PRM_SALES_LEAD_TYPE)
1176 OR ( ( Recinfo.PRM_SALES_LEAD_TYPE IS NULL )
1177 AND ( p_PRM_SALES_LEAD_TYPE IS NULL )))
1178 AND ( ( Recinfo.PRM_IND_CLASSIFICATION_CODE =
1179 p_PRM_IND_CLASSIFICATION_CODE)
1180 OR ( ( Recinfo.PRM_IND_CLASSIFICATION_CODE IS NULL )
1181 AND ( p_PRM_IND_CLASSIFICATION_CODE IS NULL )))
1182 AND ( ( Recinfo.PRM_ASSIGNMENT_TYPE =
1183 p_PRM_ASSIGNMENT_TYPE)
1184 OR ( ( Recinfo.PRM_ASSIGNMENT_TYPE IS NULL )
1185 AND ( p_PRM_ASSIGNMENT_TYPE IS NULL )))
1186 AND ( ( Recinfo.AUTO_ASSIGNMENT_TYPE =
1187 p_AUTO_ASSIGNMENT_TYPE)
1188 OR ( ( Recinfo.AUTO_ASSIGNMENT_TYPE IS NULL )
1189 AND ( p_AUTO_ASSIGNMENT_TYPE IS NULL )))
1190 AND ( ( Recinfo.PRIMARY_CONTACT_PARTY_ID =
1191 p_PRIMARY_CONTACT_PARTY_ID)
1192 OR ( ( Recinfo.PRIMARY_CONTACT_PARTY_ID IS NULL )
1193 AND ( p_PRIMARY_CONTACT_PARTY_ID IS NULL )))
1194
1195 AND ( ( Recinfo.PRIMARY_CNT_PERSON_PARTY_ID =
1196 p_PRIMARY_CNT_PERSON_PARTY_ID)
1197 OR ( ( Recinfo.PRIMARY_CNT_PERSON_PARTY_ID IS NULL )
1198 AND ( p_PRIMARY_CNT_PERSON_PARTY_ID IS NULL )))
1199
1200
1201 AND ( ( Recinfo.PRIMARY_CONTACT_PHONE_ID =
1202 p_PRIMARY_CONTACT_PHONE_ID)
1203 OR ( ( Recinfo.PRIMARY_CONTACT_PHONE_ID IS NULL )
1204 AND ( p_PRIMARY_CONTACT_PHONE_ID IS NULL )))
1205
1206 AND ( ( Recinfo.REFERRED_BY =
1207 p_REFERRED_BY)
1208 OR ( ( Recinfo.REFERRED_BY IS NULL )
1209 AND ( p_REFERRED_BY IS NULL )))
1210
1211 AND ( ( Recinfo.REFERRAL_TYPE =
1212 p_REFERRAL_TYPE)
1213 OR ( ( Recinfo.REFERRAL_TYPE IS NULL )
1214 AND ( p_REFERRAL_TYPE IS NULL )))
1215
1216 AND ( ( Recinfo.REFERRAL_STATUS =
1217 p_REFERRAL_STATUS)
1218 OR ( ( Recinfo.REFERRAL_STATUS IS NULL )
1219 AND ( p_REFERRAL_STATUS IS NULL )))
1220
1221
1222 AND ( ( Recinfo.REF_DECLINE_REASON =
1223 p_REF_DECLINE_REASON)
1224 OR ( ( Recinfo.REF_DECLINE_REASON IS NULL )
1225 AND ( p_REF_DECLINE_REASON IS NULL )))
1226
1227
1228 AND ( ( Recinfo.REF_COMM_LTR_STATUS =
1229 p_REF_COMM_LTR_STATUS)
1230 OR ( ( Recinfo.REF_COMM_LTR_STATUS IS NULL )
1231 AND ( p_REF_COMM_LTR_STATUS IS NULL )))
1232
1233 AND ( ( Recinfo.REF_ORDER_NUMBER =
1234 p_REF_ORDER_NUMBER)
1235 OR ( ( Recinfo.REF_ORDER_NUMBER IS NULL )
1236 AND ( p_REF_ORDER_NUMBER IS NULL )))
1237
1238
1239 AND ( ( Recinfo.REF_ORDER_AMT =
1240 p_REF_ORDER_AMT)
1241 OR ( ( Recinfo.REF_ORDER_AMT IS NULL )
1242 AND ( p_REF_ORDER_AMT IS NULL )))
1243
1244
1245 AND ( ( Recinfo.REF_COMM_AMT =
1246 p_REF_COMM_AMT)
1247 OR ( ( Recinfo.REF_COMM_AMT IS NULL )
1248 AND ( p_REF_COMM_AMT IS NULL )))
1249
1250 AND ( ( Recinfo.LEAD_DATE =
1251 p_LEAD_DATE)
1252 OR ( ( Recinfo.LEAD_DATE IS NULL )
1253 AND ( p_LEAD_DATE IS NULL )))
1254
1255 AND ( ( Recinfo.SOURCE_SYSTEM =
1256 p_SOURCE_SYSTEM)
1257 OR ( ( Recinfo.SOURCE_SYSTEM IS NULL )
1258 AND ( p_SOURCE_SYSTEM IS NULL )))
1259
1260 AND ( ( Recinfo.COUNTRY =
1261 p_COUNTRY)
1262 OR ( ( Recinfo.COUNTRY IS NULL )
1263 AND ( p_COUNTRY IS NULL )))
1264
1265 AND ( ( Recinfo.TOTAL_AMOUNT =
1266 p_TOTAL_AMOUNT)
1267 OR ( ( Recinfo.TOTAL_AMOUNT IS NULL )
1268 AND ( p_TOTAL_AMOUNT IS NULL )))
1269
1270
1271 AND ( ( Recinfo.EXPIRATION_DATE =
1272 p_EXPIRATION_DATE)
1273 OR ( ( Recinfo.EXPIRATION_DATE IS NULL )
1274 AND ( p_EXPIRATION_DATE IS NULL )))
1275
1276
1277
1278
1279 AND ( ( Recinfo.LEAD_RANK_IND =
1280 p_LEAD_RANK_IND)
1281 OR ( ( Recinfo.LEAD_RANK_IND IS NULL )
1282 AND ( p_LEAD_RANK_IND IS NULL )))
1283
1284 AND ( ( Recinfo.LEAD_ENGINE_RUN_DATE =
1285 p_LEAD_ENGINE_RUN_DATE)
1289 AND ( ( Recinfo.CURRENT_REROUTES =
1286 OR ( ( Recinfo.LEAD_ENGINE_RUN_DATE IS NULL )
1287 AND ( p_LEAD_ENGINE_RUN_DATE IS NULL )))
1288
1290 p_CURRENT_REROUTES )
1291 OR ( ( Recinfo.CURRENT_REROUTES IS NULL )
1292 AND ( p_CURRENT_REROUTES IS NULL )))
1293
1294
1295 -- new columns for appsperf CRMAP denorm project bug 2928041
1296
1297
1298 AND ( ( Recinfo.STATUS_OPEN_FLAG = p_STATUS_OPEN_FLAG )
1299 OR ( ( Recinfo.STATUS_OPEN_FLAG IS NULL )
1300 AND ( p_STATUS_OPEN_FLAG IS NULL )))
1301
1302
1303 AND ( ( Recinfo.LEAD_RANK_SCORE =
1307
1304 p_LEAD_RANK_SCORE )
1305 OR ( ( Recinfo.LEAD_RANK_SCORE IS NULL )
1306 AND ( p_LEAD_RANK_SCORE IS NULL )))
1308
1309
1310
1311
1312 -- 11.5.10 - ckapoor : new columns
1313
1314 AND ( ( Recinfo.MARKETING_SCORE = p_MARKETING_SCORE )
1315 OR ( ( Recinfo.MARKETING_SCORE IS NULL )
1316 AND ( p_MARKETING_SCORE IS NULL )))
1317
1318
1319 AND ( ( Recinfo.INTERACTION_SCORE =
1320 p_INTERACTION_SCORE )
1321 OR ( ( Recinfo.INTERACTION_SCORE IS NULL )
1322 AND ( p_INTERACTION_SCORE IS NULL )))
1323
1324 AND ( ( Recinfo.SOURCE_PRIMARY_REFERENCE = p_SOURCE_PRIMARY_REFERENCE )
1325 OR ( ( Recinfo.SOURCE_PRIMARY_REFERENCE IS NULL )
1326 AND ( p_SOURCE_PRIMARY_REFERENCE IS NULL )))
1327
1328
1329 AND ( ( Recinfo.SOURCE_SECONDARY_REFERENCE =
1330 p_SOURCE_SECONDARY_REFERENCE )
1331 OR ( ( Recinfo.SOURCE_SECONDARY_REFERENCE IS NULL )
1332 AND ( p_SOURCE_SECONDARY_REFERENCE IS NULL )))
1333
1334 AND ( ( Recinfo.SALES_METHODOLOGY_ID = p_SALES_METHODOLOGY_ID )
1335 OR ( ( Recinfo.SALES_METHODOLOGY_ID IS NULL )
1336 AND ( p_SALES_METHODOLOGY_ID IS NULL )))
1337
1338
1339 AND ( ( Recinfo.SALES_STAGE_ID =
1340 p_SALES_STAGE_ID )
1341 OR ( ( Recinfo.SALES_STAGE_ID IS NULL )
1342 AND ( p_SALES_STAGE_ID IS NULL )))
1343
1344
1345
1346
1347 ) then
1348 return;
1349 else
1350 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
1351 APP_EXCEPTION.RAISE_EXCEPTION;
1352 End If;
1353 END Sales_Lead_Lock_Row;
1354
1355
1356 End AS_SALES_LEADS_PKG;