1 PACKAGE BODY ASL_EXCEL_UTIL_PVT AS
2 /* $Header: aslvxlub.pls 120.1 2005/11/11 02:11:33 vjayamoh noship $ */
3
4 -- Session Based Apps Contexts
5 t_person_id NUMBER := -1 ;
6 t_user_id NUMBER := -1 ;
7 t_salesforce_id NUMBER := -1 ;
8 t_salesgroup_id NUMBER := -1 ;
9 t_org_id NUMBER := 204;
10
11 -- module_enabled_flags
12 t_org_enabled_flag BOOLEAN := FALSE;
13 /* BLAM */
14 t_per_enabled_flag BOOLEAN := FALSE;
15 /* BLAM */
16 t_cnt_enabled_flag BOOLEAN := FALSE;
17 t_opp_enabled_flag BOOLEAN := FALSE;
18 t_lead_enabled_flag BOOLEAN := FALSE;
19 t_frcst_enabled_flag BOOLEAN := FALSE;
20 t_qot_enabled_flag BOOLEAN := FALSE;
21
22 t_manager_flag VARCHAR2(1) := 'N';
23 t_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
24 -- Cache the last sync time
25 t_last_sync_time DATE := NULL;
26 t_current_sync_time DATE := NULL;
27
28 /*
29 ** Get organizations that this sales rep firstly get
30 ** access to since last sync.
31 ** This table actually is a superset of all the new organizations because
32 ** it doesn't know any advanced preference of Organizations.
33 ** TBD: bulk operation.
34 ** Open Issue: The AND / OR of download preference combination.
35 ** Also, wonder if ASL_OPPORTUNITY_ACC, ASL_ORGANIZATION_ACC
36 ** is a better choice. It only requires us to implement Sales Team user hook
37 ** Because join sales team and opportunity is always expensive and not unique at all.
38 */
39 /* BLAM -- Generic routine for org and person, replaced GET_NEW_ORG_REC */
40 PROCEDURE GET_NEW_CUST_REC
41 (p_salesforce_id IN NUMBER,
42 p_last_sync_time IN DATE) IS
43
44 CURSOR C_NEW_CUST_TEAM(p_salesforce_id NUMBER, p_last_sync_time DATE) IS
45 SELECT DISTINCT ACC1.customer_id
46 FROM AS_ACCESSES_ALL ACC1, AS_ACCESSES_ALL ACC2
47 WHERE ACC1.salesforce_id = p_salesforce_id
48 AND ACC1.creation_date > p_last_sync_time
49 AND ACC1.salesforce_id = ACC2.salesforce_id(+)
50 AND ACC1.customer_id = ACC2.customer_id (+)
51 AND ACC2.creation_date(+) <= p_last_sync_time
52 AND ACC2.salesforce_id IS NULL;
53
54 l_new_cust_rec ASL_NEW_CUST_REC_TYPE;
55 BEGIN
56
57 -- Cleanup M_NEW_CUST_TBL
58 FOR r_new_cust_team IN C_NEW_CUST_TEAM (p_salesforce_id, p_last_sync_time) LOOP
59 IF (r_new_cust_team.customer_id < M_SERVER_PK_ID_MAX) THEN
60 l_new_cust_rec.CUSTOMER_ID := r_new_cust_team.customer_id;
61 l_new_cust_rec.DOWNLOAD_FLAG := 'N';
62 BEGIN
63 M_NEW_CUST_TBL(r_new_cust_team.customer_id) := l_new_cust_rec;
64 EXCEPTION
65 WHEN OTHERS THEN
66 NULL;
67 END;
68 END IF;
69 END LOOP;
70 END GET_NEW_CUST_REC;
71 /* BLAM */
72
73 PROCEDURE GET_NEW_OPP_REC
74 (p_salesforce_id IN NUMBER,
75 p_last_sync_time IN DATE) IS
76
77 CURSOR C_NEW_OPP_TEAM(p_salesforce_id NUMBER, p_last_sync_time DATE) IS
78 SELECT DISTINCT ACC1.customer_id, ACC1.lead_id
79 FROM AS_ACCESSES_ALL ACC1, AS_ACCESSES_ALL ACC2
80 WHERE ACC1.salesforce_id = p_salesforce_id
81 AND ACC1.creation_date > p_last_sync_time
82 AND ACC1.salesforce_id = ACC2.salesforce_id(+)
83 AND ACC1.customer_id = ACC2.customer_id (+)
84 AND ACC1.lead_id = ACC2.lead_id (+)
85 AND ACC2.creation_date(+) <= p_last_sync_time
86 AND ACC2.salesforce_id IS NULL
87 AND ACC1.LEAD_ID IS NOT NULL;
88
89 l_new_opp_rec ASL_NEW_OPPORTUNITY_REC_TYPE;
90 BEGIN
91
92 FOR r_new_opp_team IN C_NEW_OPP_TEAM(p_salesforce_id, p_last_sync_time) LOOP
93 IF (r_new_opp_team.lead_id < M_SERVER_PK_ID_MAX) THEN
94 l_new_opp_rec.OPPORTUNITY_ID := r_new_opp_team.lead_id;
95 l_new_opp_rec.CUSTOMER_ID := r_new_opp_team.customer_id;
96 l_new_opp_rec.DOWNLOAD_FLAG := 'N';
97 BEGIN
98 M_NEW_OPP_TBL(r_new_opp_team.lead_id) := l_new_opp_rec;
99 EXCEPTION
100 WHEN OTHERS THEN
101 NULL;
102 END;
103 END IF;
104 END LOOP;
105 END GET_NEW_OPP_REC;
106
107 PROCEDURE GET_NEW_LEAD_REC
108 (p_salesforce_id IN NUMBER,
109 p_last_sync_time IN DATE) IS
110
111 CURSOR C_NEW_LEAD_TEAM(p_salesforce_id NUMBER, p_last_sync_time DATE) IS
112 SELECT DISTINCT ACC1.customer_id, ACC1.sales_lead_id
113 FROM AS_ACCESSES_ALL ACC1, AS_ACCESSES_ALL ACC2
114 WHERE ACC1.salesforce_id = p_salesforce_id
115 AND ACC1.creation_date > p_last_sync_time
116 AND ACC1.salesforce_id = ACC2.salesforce_id(+)
117 AND ACC1.customer_id = ACC2.customer_id (+)
118 AND ACC1.sales_lead_id = ACC2.sales_lead_id (+)
119 AND ACC2.creation_date(+) <= p_last_sync_time
120 AND ACC2.salesforce_id IS NULL
121 AND ACC1.sales_lead_id IS NOT NULL;
122
123 l_new_lead_rec ASL_NEW_LEAD_REC_TYPE;
124 BEGIN
125
126 FOR r_new_lead_team IN C_NEW_LEAD_TEAM(p_salesforce_id, p_last_sync_time) LOOP
127 IF (r_new_lead_team.sales_lead_id < M_SERVER_PK_ID_MAX) THEN
128 l_new_lead_rec.SALES_LEAD_ID := r_new_lead_team.sales_lead_id;
129 l_new_lead_rec.CUSTOMER_ID := r_new_lead_team.customer_id;
130 l_new_lead_rec.DOWNLOAD_FLAG := 'N';
131 BEGIN
132 M_NEW_LEAD_TBL(r_new_lead_team.sales_lead_id) := l_new_lead_rec;
133 EXCEPTION
134 WHEN OTHERS THEN
135 NULL;
136 END;
137 END IF;
138 END LOOP;
139 END GET_NEW_LEAD_REC;
140
141
142 PROCEDURE GET_NEW_QOT_REC
143 (p_salesforce_id IN NUMBER,
144 p_last_sync_time IN DATE) IS
145
146 CURSOR C_NEW_QOT_TEAM(p_salesforce_id NUMBER, p_last_sync_time DATE) IS
147 SELECT QUOTE_HEADER_ID
148 FROM ASO_QUOTE_HEADERS_ALL
149 WHERE QUOTE_NUMBER IN (
150 SELECT DISTINCT ACC1.quote_number
151 FROM ASO_QUOTE_ACCESSES ACC1, ASO_QUOTE_ACCESSES ACC2
152 WHERE ACC1.resource_id = p_salesforce_id
153 AND ACC1.creation_date > p_last_sync_time
154 AND ACC1.resource_id = ACC2.resource_id(+)
155 AND ACC1.QUOTE_NUMBER = ACC2.QUOTE_NUMBER (+)
156 AND ACC2.creation_date(+) <= p_last_sync_time
157 AND ACC1.resource_id IS NOT NULL
158 AND ACC2.resource_id IS NULL
159 )
160 AND MAX_VERSION_FLAG = 'Y';
161
162 l_new_qot_rec ASL_NEW_QUOTE_REC_TYPE;
163 BEGIN
164
165 -- Cleanup M_NEW_QOT_TBL
166 FOR r_new_qot_team IN C_NEW_QOT_TEAM (p_salesforce_id, p_last_sync_time) LOOP
167 IF (r_new_qot_team.QUOTE_HEADER_ID < M_SERVER_PK_ID_MAX) THEN
168 l_new_qot_rec.QUOTE_HEADER_ID := r_new_qot_team.QUOTE_HEADER_ID;
169 l_new_qot_rec.DOWNLOAD_FLAG := 'N';
170 BEGIN
171 M_NEW_QOT_TBL(r_new_qot_team.QUOTE_HEADER_ID) := l_new_qot_rec;
172 EXCEPTION
173 WHEN OTHERS THEN
174 NULL;
175 END;
176 END IF;
177 END LOOP;
178 END GET_NEW_QOT_REC;
179
180 /*
181 ** save last downloaded Inventory categories
182 */
183 PROCEDURE GET_DOWNLOADED_INV_CATGRY_REC
184 (p_org_id NUMBER
185 ,p_user_id NUMBER
186 ,p_app_id NUMBER
187 ,p_resp_id NUMBER) IS
188
189 /*
190 CURSOR C_DOWNLOADED_INV_CATEGORY(p_org_id NUMBER, p_user_id NUMBER, p_app_id NUMBER, p_resp_id NUMBER) IS
191 SELECT DISTINCT CATEGORY_ID
192 FROM MTL_ITEM_CATEGORIES
193 WHERE ORGANIZATION_ID = p_org_id
194 AND CATEGORY_ID in
195 (SELECT FND_PROFILE.VALUE_SPECIFIC('ASL_EXCEL_INV_CATEGORY',p_user_id,p_resp_id,p_app_id)
196 FROM DUAL
197 );
198
199 */
200 l_inv_categories varchar(2000) := '';
201
202 CURSOR C_DOWNLOADED_INV_CATEGORY(p_org_id NUMBER, p_user_id NUMBER, p_app_id NUMBER, p_resp_id NUMBER) IS
203 SELECT DISTINCT CATEGORY_ID
204 FROM MTL_ITEM_CATEGORIES
205 WHERE ORGANIZATION_ID = p_org_id
206 AND INSTR(l_inv_categories, fnd_global.local_chr(39)||to_char(CATEGORY_ID)|| fnd_global.local_chr(39))>0;
207
208 l_old_inv_catgry_rec ASL_OLD_INV_CATEGORY_REC_TYPE;
209 BEGIN
210 SELECT FND_PROFILE.VALUE_SPECIFIC('ASL_EXCEL_INV_CATEGORY',p_user_id, p_resp_id, p_app_id)
211 INTO l_inv_categories
212 FROM DUAL;
213
214 FOR r_old_inv_catgry_rec IN C_DOWNLOADED_INV_CATEGORY (p_org_id,p_user_id,p_app_id,p_resp_id) LOOP
215 IF (r_old_inv_catgry_rec.category_id < M_SERVER_PK_ID_MAX) THEN
216 l_old_inv_catgry_rec.category_id := r_old_inv_catgry_rec.category_id;
217 BEGIN
218 M_OLD_INV_TBL(r_old_inv_catgry_rec.category_id) := l_old_inv_catgry_rec;
219 EXCEPTION
220 WHEN OTHERS THEN
221 NULL;
222 END;
223 END IF;
224 END LOOP;
225 END GET_DOWNLOADED_INV_CATGRY_REC;
226
227
228
229 /*
230 Save last downloaded price list headers
231 */
232 --VJAYAMOH modified as part of sql rep bugs
233 --Bug 4266530
234 PROCEDURE GET_DOWNLOADED_PRICE_LIST_REC
235 (p_org_id NUMBER
236 ,p_user_id NUMBER
237 ,p_app_id NUMBER
238 ,p_resp_id NUMBER) IS
239
240 l_old_price_list_rec ASL_OLD_PRICE_LIST_REC_TYPE;
241 l_prof_price_list_headers varchar(1000) := '';
242 l_end_index number :=0;
243 l_header_id_string varchar(1000) := '';
244 l_count NUMBER := 0;
245 CURSOR l_price_list_headers_c(p_header_id IN NUMBER) IS
246 SELECT 1
247 FROM QP_LIST_HEADERS_B
248 WHERE LIST_HEADER_ID = p_header_id;
249 BEGIN
250
251 SELECT FND_PROFILE.VALUE_SPECIFIC('ASL_EXCEL_PRICE_LIST',p_user_id,p_resp_id,p_app_id) into l_prof_price_list_headers FROM DUAL;
252 l_prof_price_list_headers := replace( l_prof_price_list_headers ,'(','');
253 l_prof_price_list_headers := replace( l_prof_price_list_headers ,')','');
254 l_prof_price_list_headers := replace( l_prof_price_list_headers ,fnd_global.local_chr(39),'');
255
256
257 while (INSTR(l_prof_price_list_headers, ',') > 0)
258 LOOP
259 l_end_index := INSTR(l_prof_price_list_headers, ',');
260 l_header_id_string := Substr(l_prof_price_list_headers, 0, l_end_index-1);
261 l_prof_price_list_headers := Substr(l_prof_price_list_headers, l_end_index+1, length(l_prof_price_list_headers));
262
263 IF (l_header_id_string < M_SERVER_PK_ID_MAX) THEN
264 OPEN l_price_list_headers_c(l_header_id_string);
265 FETCH l_price_list_headers_c INTO l_count;
266 IF (l_price_list_headers_c%FOUND) THEN
267 BEGIN
268 l_old_price_list_rec.list_header_id := l_header_id_string;
269 M_OLD_PRICE_LIST_TBL(l_header_id_string) := l_old_price_list_rec;
270 EXCEPTION
271 WHEN OTHERS THEN
272 NULL;
273 END;
274 CLOSE l_price_list_headers_c;
275 END IF;
276 END IF;
277 END LOOP;
278
279
280 IF (l_prof_price_list_headers <> 'ALL' AND (length(l_prof_price_list_headers) > 0) ) THEN
281 if true then
282 OPEN l_price_list_headers_c(l_prof_price_list_headers);
283 FETCH l_price_list_headers_c INTO l_count;
284 IF (l_price_list_headers_c%FOUND) THEN
285 BEGIN
286 l_old_price_list_rec.list_header_id := l_prof_price_list_headers;
287 M_OLD_PRICE_LIST_TBL(l_prof_price_list_headers) := l_old_price_list_rec;
288 EXCEPTION
289 WHEN OTHERS THEN
290 NULL;
291 END;
292 END IF;
293 END IF;
294 END IF;
295
296
297 END GET_DOWNLOADED_PRICE_LIST_REC;
298
299
300
301 /*
302 ** Save Sync Context for a particular sales rep.
303 ** It doesn't do any validation on the passed in values. Assumeably ASF_PAGE
304 ** Already done that.
305 ** If incremental sync is implmented, save the new-added Organization for
306 ** Check_Org_Download Method
307 ** last_sync_time comes from FND_PROFILE 'ASL_EXCEL_LAST_SYNC_TIME'. If the value
308 ** is null, it means this user never synced, set the M_FULL_SYNC to true no matter
309 ** what value client sends over.
310 */
311 PROCEDURE SAVE_SYNC_CONTEXT
312 ( p_salesforce_id IN NUMBER,
313 p_salesgroup_id IN NUMBER,
314 p_person_id IN NUMBER,
315 p_user_id IN NUMBER,
316 p_full_sync IN NUMBER,
317 x_curr_sync_time_str OUT NOCOPY VARCHAR2
318 ) IS
319 l_last_sync_time_str VARCHAR2(60) := NULL;
320 l_default_sync_time_str VARCHAR2(60) := NULL;
321 l_defined boolean;
322 l_full_sync_str VARCHAR2(10) := NULL;
323 BEGIN
324
325 t_person_id := p_person_id;
326 t_user_id := p_user_id;
327 t_salesforce_id := p_salesforce_id;
328 t_salesgroup_id := p_salesgroup_id;
329 t_manager_flag := CHECK_MANAGER_FLAG(t_salesgroup_id);
330 -- t_last_sync_time := p_last_sync_time;
331
332 -- l_last_sync_time_str := FND_PROFILE.VALUE('ASL_EXCEL_LAST_SYNC_TIME');
333 BEGIN
334 FND_PROFILE.GET_SPECIFIC(name_z => 'ASL_EXCEL_LAST_SYNC_TIME',
335 user_id_z => p_user_id ,
336 val_z => l_last_sync_time_str,
337 defined_z => l_defined );
338 EXCEPTION
339 WHEN OTHERS THEN
340 l_last_sync_time_str := NULL;
341 END;
342
343 -- Temp solution for retrieving site level profile option
344 l_default_sync_time_str := '1970-02-29:02:59:31';
345
346 -- l_last_sync_time_str := '2002-07-29:02:57:31';
347 M_FULL_SYNC := TRUE;
348
349 IF (p_full_sync = 0 AND l_last_sync_time_str IS NOT NULL)
350 THEN
351 IF ((l_default_sync_time_str IS NOT NULL) AND (l_last_sync_time_str <> l_default_sync_time_str)) THEN
352
353 BEGIN
354 t_last_sync_time := TO_DATE(l_last_sync_time_str, M_CONVERSION_DATE_FORMAT);
355 -- Get the organizations that newly assigned to this sales rep
356 -- it can come from Organization sales team, Opportunity Sales Team
357 -- LEAD sales team.
358 -- If we have user hooks, this part is no longer needed.
359 /* BLAM */
360 M_NEW_CUST_TBL.DELETE;
361 /* BLAM */
362 M_NEW_CNT_TBL.DELETE;
363 M_NEW_OPP_TBL.DELETE;
364 M_NEW_OPP_LINE_TBL.DELETE;
365 /* agmoore - changes for opportunity classifications 2744023 */
366 M_NEW_OPP_CLASS_TBL.DELETE;
367 /* lcooper - changes for opportunity issues 2675493 */
368 M_NEW_LEAD_TBL.DELETE;
369 M_OLD_INV_TBL.DELETE;
370 M_OLD_PRICE_LIST_TBL.DELETE;
371 M_NEW_CUST_ACCOUNT_TBL.DELETE;
372
373 /* BLAM */
374 IF t_org_enabled_flag = true OR t_per_enabled_flag = true THEN
375 GET_NEW_CUST_REC(t_salesforce_id, t_last_sync_time);
376 END IF;
377 /* BLAM */
378
379 IF t_opp_enabled_flag = true THEN
380 GET_NEW_OPP_REC(t_salesforce_id, t_last_sync_time);
381 END IF;
382
383 IF t_lead_enabled_flag = true THEN
384 GET_NEW_LEAD_REC(t_salesforce_id, t_last_sync_time);
385 END IF;
386
387 IF t_qot_enabled_flag = true THEN
388 GET_NEW_QOT_REC(t_salesforce_id, t_last_sync_time);
389 GET_DOWNLOADED_INV_CATGRY_REC(t_org_id, t_user_id, null, null);
390 GET_DOWNLOADED_PRICE_LIST_REC(t_org_id, t_user_id, null, null);
391 END IF;
392
393 M_FULL_SYNC := FALSE;
394 EXCEPTION
395 WHEN OTHERS THEN
396 /*
397 ASL_UTIL_LOG_PKG.Create_Log_Entry(
398 p_resource_id => t_salesforce_id,
399 p_log_type => 'Excel',
400 p_log_location => 'Save Sync Context',
401 p_log_desc => ' Converting last_sync_time_str has exception ' || SQLERRM );
402 */
403 M_FULL_SYNC := TRUE;
404 END;
405 END IF;
406 END IF;
407
408 /*
409 IF (M_FULL_SYNC = TRUE) THEN
410 DBMS_OUTPUT.PUT_LINE(' Doing Full Sync');
411 END IF;
412 */
413 /*
414 ** Get the current sysdate for SYNC_TIME of this sync transaction
415 */
416 SELECT SYSDATE INTO t_current_sync_time FROM DUAL;
417
418 x_curr_sync_time_str := TO_CHAR(t_current_sync_time, M_CONVERSION_DATE_FORMAT);
419
420 IF (M_FULL_SYNC) THEN
421 l_full_sync_str := 'TRUE';
422 ELSE
423 l_full_sync_str := 'FALSE';
424 END IF;
425 /*
426 ASL_UTIL_LOG_PKG.Create_Log_Entry(
427 p_resource_id => t_salesforce_id,
428 p_log_type => 'Excel',
429 p_log_location => 'Save Sync Context',
430 p_log_desc => ' It is doing ' || l_full_sync_str || ' last sync time IS ' ||
431 l_last_sync_time_str || ' sync flag passed in is ' || p_full_sync );
432 */
433 END SAVE_SYNC_CONTEXT;
434
435 PROCEDURE SET_ACCESS_PROFILE_VALUES
436 (p_cust_access IN VARCHAR2
437 ,p_lead_access IN VARCHAR2
438 ,p_opp_access IN VARCHAR2
439 ,p_mgr_update IN VARCHAR2
440 ,p_admin_update IN VARCHAR2
441 ) IS
442
443 BEGIN
444
445 t_access_profile_rec.cust_access_profile_value := p_cust_access;
446 t_access_profile_rec.lead_access_profile_value := p_lead_access;
447 t_access_profile_rec.opp_access_profile_value := p_opp_access;
448 t_access_profile_rec.mgr_update_profile_value := p_mgr_update;
449 t_access_profile_rec.admin_update_profile_value := p_admin_update;
450
451 END SET_ACCESS_PROFILE_VALUES;
452 /*
453 ** TBD:
454 ** To save the PL/SQL <-> JDBC communication, combine Check_Organization_New_Download
455 ** and Check_Customer_Updateble together. Right now the problem is the DownloadProcessor.java
456 ** is too generic to split one column 'IY' to 'I' and 'Y'.
457 ** TBD:
458 ** Would it be better to delete all the entries where download_status = 'N'?
459 */
460 /* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
461 /* and ASL_NEW_ORG_REC_TYPE to ASL_NEW_CUST_REC_TYPE */
462
463 FUNCTION Check_Organization_Download
464 (p_customer_id IN NUMBER
465 ,p_org_creation_date IN DATE
466 ,p_org_update_date IN DATE
467 ,p_profile_creation_date IN DATE
468 ,p_ploc_update_date IN DATE
469 ,p_sloc_update_date IN DATE
470 ,p_bloc_update_date IN DATE
471 ,p_phone_update_date IN DATE
472 ,p_email_update_date IN DATE
473 ) RETURN VARCHAR2 IS
474 l_new_org_rec ASL_NEW_CUST_REC_TYPE;
475
476 l_download_status VARCHAR2(1) := 'I';
477 BEGIN
478 --DBMS_OUTPUT.PUT_LINE('Checking Customer ' || p_customer_id);
479 IF (M_FULL_SYNC = FALSE) THEN
480 IF (p_customer_id >= M_SERVER_PK_ID_MAX) THEN
481 RETURN l_download_status;
482 END IF;
483 IF ((p_org_creation_date > t_last_sync_time) OR
484 (M_NEW_CUST_TBL.EXISTS(p_customer_id))
485 )
486 THEN
487 l_download_status := 'I';
488 IF (M_NEW_CUST_TBL.EXISTS(p_customer_id)) THEN
489 M_NEW_CUST_TBL(p_customer_id).DOWNLOAD_FLAG := 'Y';
490 ELSE
491 l_new_org_rec.CUSTOMER_ID := p_customer_id;
492 l_new_org_rec.DOWNLOAD_FLAG := 'Y';
493 M_NEW_CUST_TBL(p_customer_id) := l_new_org_rec;
494 END IF;
495 ELSIF ((p_org_update_date > t_last_sync_time) OR
496 (p_profile_creation_date > t_last_sync_time) OR
497 (p_ploc_update_date > t_last_sync_time) OR
498 (p_sloc_update_date > t_last_sync_time) OR
499 (p_bloc_update_date > t_last_sync_time) OR
500 (p_phone_update_date > t_last_sync_time) OR
501 (p_email_update_date > t_last_sync_time))
502 THEN
503 l_download_status := 'U';
504 ELSE
505 l_download_status := 'O';
506 END IF;
507 END IF;
508 -- DBMS_OUTPUT.PUT_LINE(' STATUS IS ' || l_download_status);
509 RETURN l_download_status;
510 END Check_Organization_Download;
511
512 /* START BLAM */
513 FUNCTION Check_Person_Download
514 (p_customer_id IN NUMBER
515 ,p_per_creation_date IN DATE
516 ,p_per_update_date IN DATE
517 ,p_profile_creation_date IN DATE
518 ,p_phone_update_date IN DATE
519 ,p_email_update_date IN DATE
520 ) RETURN VARCHAR2 IS
521 l_new_per_rec ASL_NEW_CUST_REC_TYPE;
522 l_download_status VARCHAR2(1) := 'I';
523 BEGIN
524 --DBMS_OUTPUT.PUT_LINE('Checking Customer ' || p_customer_id);
525 IF (M_FULL_SYNC = FALSE) THEN
526 IF (p_customer_id >= M_SERVER_PK_ID_MAX) THEN
527 RETURN l_download_status;
528 END IF;
529 IF ((p_per_creation_date > t_last_sync_time) OR
530 (M_NEW_CUST_TBL.EXISTS(p_customer_id))
531 )
532 THEN
533 l_download_status := 'I';
534 IF (M_NEW_CUST_TBL.EXISTS(p_customer_id)) THEN
535 M_NEW_CUST_TBL(p_customer_id).DOWNLOAD_FLAG := 'Y';
536 ELSE
537 l_new_per_rec.CUSTOMER_ID := p_customer_id;
538 l_new_per_rec.DOWNLOAD_FLAG := 'Y';
539 M_NEW_CUST_TBL(p_customer_id) := l_new_per_rec;
540 END IF;
541 ELSIF ((p_per_update_date > t_last_sync_time) OR
542 (p_profile_creation_date > t_last_sync_time) OR
543 (p_phone_update_date > t_last_sync_time) OR
544 (p_email_update_date > t_last_sync_time))
545 THEN
546 l_download_status := 'U';
547 ELSE
548 l_download_status := 'O';
549 END IF;
550 END IF;
551 -- DBMS_OUTPUT.PUT_LINE(' STATUS IS ' || l_download_status);
552 RETURN l_download_status;
553 END Check_Person_Download;
554 /* BLAM */
555
556 FUNCTION Check_Opp_Download
557 (p_opp_creation_date IN DATE
558 ,p_opp_update_date IN DATE
559 ,p_opportunity_id IN NUMBER
560 ,p_customer_id IN NUMBER
561 ,p_customer_update_date IN DATE
562 ,p_contact_party_update_date IN DATE
563 ,p_contact_person_update_date IN DATE
564 ,p_rel_update_date IN DATE
565 ) RETURN VARCHAR2 IS
566
567 l_new_opp_rec ASL_NEW_OPPORTUNITY_REC_TYPE;
568 l_download_status VARCHAR2(1) := 'I';
569 BEGIN
570 IF (M_FULL_SYNC = FALSE) THEN
571 IF (p_opportunity_id >= M_SERVER_PK_ID_MAX) THEN
572 RETURN l_download_status;
573 END IF;
574 IF ((p_opp_creation_date > t_last_sync_time) OR
575 (M_NEW_OPP_TBL.EXISTS(p_opportunity_id))
576 )
577 THEN
578 IF (M_NEW_OPP_TBL.EXISTS(p_opportunity_id)
579 ) THEN
580 M_NEW_OPP_TBL(p_opportunity_id).DOWNLOAD_FLAG := 'Y';
581 ELSE
582 l_new_opp_rec.OPPORTUNITY_ID := p_opportunity_id;
583 l_new_opp_rec.CUSTOMER_ID := p_customer_id;
584 l_new_opp_rec.DOWNLOAD_FLAG := 'Y';
585 M_NEW_OPP_TBL(p_opportunity_id) := l_new_opp_rec;
586 END IF;
587 ELSIF ((p_opp_update_date > t_last_sync_time) OR
588 (p_customer_update_date > t_last_sync_time) OR
589 (p_contact_party_update_date > t_last_sync_time) OR
590 (p_contact_person_update_date > t_last_sync_time) OR
591 (p_rel_update_date > t_last_sync_time)
592 )
593 THEN
594 l_download_status := 'U';
595 ELSE
596 l_download_status := 'O';
597 END IF;
598
599 END IF;
600
601 RETURN l_download_status;
602 END Check_Opp_Download;
603
604 FUNCTION Check_Opp_Det_Download
605 (p_line_creation_date IN DATE
606 ,p_line_update_date IN DATE
607 ,p_opportunity_id IN NUMBER
608 ,p_opp_line_id IN NUMBER
609 ) RETURN VARCHAR2 IS
610 l_new_opp_line_rec ASL_NEW_OPP_LINE_REC_TYPE;
611 l_download_status VARCHAR2(1) := 'I';
612
613 BEGIN
614
615 IF (M_FULL_SYNC = FALSE) THEN
616 IF ((p_opportunity_id >= M_SERVER_PK_ID_MAX) OR (p_opp_line_id >= M_SERVER_PK_ID_MAX)) THEN
617 RETURN l_download_status;
618 END IF;
619 IF ((p_line_creation_date > t_last_sync_time) OR
620 (M_NEW_OPP_TBL.EXISTS(p_opportunity_id) AND (M_NEW_OPP_TBL(p_opportunity_id).DOWNLOAD_FLAG = 'Y'))
621 )
622 THEN
623 l_new_opp_line_rec.OPPORTUNITY_ID := p_opportunity_id;
624 l_new_opp_line_rec.OPPORTUNITY_LINE_ID := p_opp_line_id;
625 M_NEW_OPP_LINE_TBL(p_opp_line_id) := l_new_opp_line_rec;
626 ELSIF ((p_line_update_date > t_last_sync_time)
627 )
628 THEN
629 l_download_status := 'U';
630 ELSE
631 l_download_status := 'O';
632 END IF;
633 END IF;
634 return l_download_status;
635 END Check_Opp_Det_Download;
636
637 /* agmoore - changes for opportunity classifications 2744023 */
638 FUNCTION Check_Opp_Class_Download
639 (p_class_creation_date IN DATE
640 ,p_class_update_date IN DATE
641 ,p_opportunity_id IN NUMBER
642 ,p_opp_class_id IN NUMBER
643 ) RETURN VARCHAR2 IS
644 l_new_opp_class_rec ASL_NEW_OPP_CLASS_REC_TYPE;
645 l_download_status VARCHAR2(1) := 'I';
646
647 BEGIN
648
649 IF (M_FULL_SYNC = FALSE) THEN
650 IF ((p_opportunity_id >= M_SERVER_PK_ID_MAX) OR (p_opp_class_id >= M_SERVER_PK_ID_MAX)) THEN
651 RETURN l_download_status;
652 END IF;
653 IF ((p_class_creation_date > t_last_sync_time) OR
654 (M_NEW_OPP_TBL.EXISTS(p_opportunity_id) AND (M_NEW_OPP_TBL(p_opportunity_id).DOWNLOAD_FLAG = 'Y'))
655 )
656 THEN
657 --l_download_status := 'I';
658 l_new_opp_class_rec.OPPORTUNITY_ID := p_opportunity_id;
659 l_new_opp_class_rec.OPPORTUNITY_CLASS_ID := p_opp_class_id;
660 M_NEW_OPP_CLASS_TBL(p_opp_class_id) := l_new_opp_class_rec;
661 ELSIF ((p_class_update_date > t_last_sync_time)
662 )
663 THEN
664 l_download_status := 'U';
665 ELSE
666 l_download_status := 'O';
667 END IF;
668 END IF;
669 return l_download_status;
670 END Check_Opp_Class_Download;
671
672 /* lcooper - changes for opportunity issues 2675493 */
673 FUNCTION Check_Opp_Issues_Download
674 (p_issue_creation_date IN DATE,
675 p_issue_update_date IN DATE,
676 p_opportunity_id IN NUMBER,
677 p_opp_issue_id IN NUMBER
678 ) RETURN VARCHAR2 IS
679 l_new_opp_issues_rec ASL_NEW_OPP_ISSUES_REC_TYPE;
680 l_download_status VARCHAR2(1) := 'I';
681
682 BEGIN
683
684 IF (M_FULL_SYNC = FALSE) THEN
685 IF ((p_opportunity_id >= M_SERVER_PK_ID_MAX) OR (p_opp_issue_id >= M_SERVER_PK_ID_MAX)) THEN
686 RETURN l_download_status;
687 END IF;
688 IF ((p_issue_creation_date > t_last_sync_time) OR
689 (M_NEW_OPP_TBL.EXISTS(p_opportunity_id) AND (M_NEW_OPP_TBL(p_opportunity_id).DOWNLOAD_FLAG = 'Y'))
690 )
691 THEN
692 l_new_opp_issues_rec.OPPORTUNITY_ID := p_opportunity_id;
693 l_new_opp_issues_rec.OPPORTUNITY_ISSUE_ID := p_opp_issue_id;
694 M_NEW_OPP_ISSUES_TBL(p_opp_issue_id) := l_new_opp_issues_rec;
695 ELSIF ((p_issue_update_date > t_last_sync_time)
696 )
697 THEN
698 l_download_status := 'U';
699 ELSE
700 l_download_status := 'O';
701 END IF;
702 END IF;
703 return l_download_status;
704 END Check_Opp_Issues_Download;
705
706 FUNCTION Check_Opp_Credit_Download
707 (p_credit_creation_date IN DATE
708 ,p_credit_last_update_date IN DATE
709 ,p_lead_line_id IN NUMBER
710 ,p_group_last_update_date IN DATE
711 ,p_resource_last_update_date IN DATE
712 ) RETURN VARCHAR2 IS
713 l_download_status VARCHAR2(1) := 'I';
714 BEGIN
715 IF (M_FULL_SYNC = TRUE) THEN
716 RETURN l_download_status;
717 END IF;
718
719 IF (p_lead_line_id >= M_SERVER_PK_ID_MAX) THEN
720 RETURN l_download_status;
721 END IF;
722
723 IF ((p_credit_creation_date > t_last_sync_time) OR
724 (M_NEW_OPP_LINE_TBL.EXISTS(p_lead_line_id))
725 )
726 THEN
727 l_download_status := 'I';
728 ELSIF ((p_credit_last_update_date > t_last_sync_time) OR
729 (p_group_last_update_date > t_last_sync_time) OR
730 (p_resource_last_update_date > t_last_sync_time)
731 )
732 THEN
733 l_download_status := 'U';
734 ELSE
735 l_download_status := 'O';
736 END IF;
737 return l_download_status;
738 END Check_Opp_Credit_Download;
739
740 /*
741 ** Assume no body will change the contact party itself
742 */
743 /* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
744 FUNCTION Check_Contact_Download
745 (p_contact_creation_date IN DATE
746 ,p_contact_party_id IN NUMBER
747 ,p_contact_person_id IN NUMBER
748 ,p_customer_id IN NUMBER
749 ,p_person_update_date IN DATE
750 ,p_contact_update_date IN DATE
751 ,p_loc_update_date IN DATE
752 ,p_phone_update_date IN DATE
753 ,p_email_update_date IN DATE
754 ) RETURN VARCHAR2 IS
755
756 l_download_status VARCHAR2(1) := 'I';
757 l_new_contact_rec ASL_NEW_CONTACT_REC_TYPE;
758 BEGIN
759 IF (M_FULL_SYNC = TRUE) THEN
760 RETURN l_download_status;
761 END IF;
762
763 IF (p_contact_party_id >= M_SERVER_PK_ID_MAX) THEN
764 RETURN l_download_status;
765 END IF;
766
767 IF ((p_contact_creation_date > t_last_sync_time) OR
768 (M_NEW_CUST_TBL.EXISTS(p_customer_id) AND (M_NEW_CUST_TBL(p_customer_id).DOWNLOAD_FLAG = 'Y'))
769 )
770 THEN
771 l_download_status := 'I';
772 l_new_contact_rec.CONTACT_PARTY_ID := p_contact_party_id;
773 l_new_contact_rec.CONTACT_PERSON_ID := p_contact_person_id;
774 l_new_contact_rec.CUSTOMER_ID := p_customer_id;
775 M_NEW_CNT_TBL(p_contact_party_id) := l_new_contact_rec;
776 ELSIF ((p_person_update_date > t_last_sync_time) OR
777 (p_contact_update_date > t_last_sync_time) OR
778 (p_loc_update_date > t_last_sync_time) OR
779 (p_phone_update_date > t_last_sync_time) OR
780 (p_email_update_date > t_last_sync_time)
781 )
782 THEN
783 l_download_status := 'U';
784 ELSE
785 l_download_status := 'O';
786 END IF;
787
788 RETURN l_download_status;
789 END Check_Contact_Download;
790
791 /* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
792 FUNCTION Check_Notes_Download
793 (p_note_creation_date IN DATE
794 ,p_note_source_object_code IN VARCHAR2
795 ,p_note_source_object_id IN NUMBER
796 ,p_tl_update_date IN DATE
797 ) RETURN VARCHAR2 IS
798
799 l_download_status VARCHAR2(1) := 'I';
800
801 BEGIN
802 IF (M_FULL_SYNC = TRUE) THEN
803 RETURN l_download_status;
804 END IF;
805
806 -- Bug fix for 2656639. If Note Source Object Id is identified to be Mobile
807 -- download them anyway.
808 IF (p_note_source_object_id >= M_SERVER_PK_ID_MAX) THEN
809 RETURN l_download_status;
810 END IF;
811
812 IF ((p_note_creation_date > t_last_sync_time) OR
813 ((p_note_source_object_code = 'PARTY')
814 AND
815 (
816 (M_NEW_CUST_TBL.EXISTS(p_note_source_object_id) AND (M_NEW_CUST_TBL(p_note_source_object_id).DOWNLOAD_FLAG = 'Y')) OR
817 (M_NEW_CNT_TBL.EXISTS(p_note_source_object_id))
818 )
819 )
820 OR
821 ((p_note_source_object_code = 'OPPORTUNITY') AND
822 (M_NEW_OPP_TBL.EXISTS(p_note_source_object_id) AND (M_NEW_OPP_TBL(p_note_source_object_id).DOWNLOAD_FLAG = 'Y'))
823 )
824 OR
825 ((p_note_source_object_code = 'LEAD' ) AND
826 (M_NEW_LEAD_TBL.EXISTS(p_note_source_object_id) AND (M_NEW_LEAD_TBL(p_note_source_object_id).DOWNLOAD_FLAG = 'Y'))
827 )
828 )
829 THEN
830 l_download_status := 'I';
831 ELSIF ((p_tl_update_date > t_last_sync_time)
832 )
833 THEN
834 l_download_status := 'U';
835 ELSE
836 l_download_status := 'O';
837 END IF;
838
839 RETURN l_download_status;
840 END Check_Notes_Download;
841
842 FUNCTION Check_Lead_Download
843 (p_lead_creation_date IN DATE
844 ,p_lead_last_update_date IN DATE
845 ,p_sales_lead_id IN NUMBER
846 ,p_customer_id IN NUMBER
847 ,p_customer_update_date IN DATE
848 ,p_cnt_party_update_date IN DATE
849 ,p_rel_last_update_date IN DATE
850 ,p_cnt_person_update_date IN DATE
851 ) RETURN VARCHAR2 IS
852 l_download_status VARCHAR2(1) := 'I';
853 l_new_lead_rec ASL_NEW_LEAD_REC_TYPE;
854 BEGIN
855 IF (M_FULL_SYNC = TRUE) THEN
856 RETURN l_download_status;
857 END IF;
858
859 IF (p_sales_lead_id >= M_SERVER_PK_ID_MAX) THEN
860 RETURN l_download_status;
861 END IF;
862
863 IF ((p_lead_creation_date > t_last_sync_time) OR
864 (M_NEW_LEAD_TBL.EXISTS(p_sales_lead_id))
865 )
866 THEN
867 l_download_status := 'I';
868 IF (M_NEW_LEAD_TBL.EXISTS(p_sales_lead_id)
869 ) THEN
870 M_NEW_LEAD_TBL(p_sales_lead_id).DOWNLOAD_FLAG := 'Y';
871 ELSE
872 l_new_lead_rec.SALES_LEAD_ID := p_sales_lead_id;
873 l_new_lead_rec.CUSTOMER_ID := p_customer_id;
874 l_new_lead_rec.DOWNLOAD_FLAG := 'Y';
875 M_NEW_LEAD_TBL(p_sales_lead_id) := l_new_lead_rec;
876 END IF;
877 ELSIF ((p_lead_last_update_date > t_last_sync_time) OR
878 (p_customer_update_date > t_last_sync_time) OR
879 (p_cnt_party_update_date > t_last_sync_time) OR
880 (p_cnt_person_update_date > t_last_sync_time) OR
881 (p_rel_last_update_date > t_last_sync_time)
882 )
883 THEN
884 l_download_status := 'U';
885 ELSE
886 l_download_status := 'O';
887 END IF;
888 return l_download_status;
889 END Check_Lead_Download;
890
891 FUNCTION Check_Lead_Det_Download
892 (p_line_creation_date IN DATE
893 ,p_line_last_update_date IN DATE
894 ,p_sales_lead_id IN NUMBER
895 ) RETURN VARCHAR2 IS
896 l_download_status VARCHAR2(1) := 'I';
897 BEGIN
898 IF ((M_FULL_SYNC = TRUE) OR (p_sales_lead_id >= M_SERVER_PK_ID_MAX)) THEN
899 RETURN l_download_status;
900 END IF;
901
902 IF ((p_line_creation_date > t_last_sync_time) OR
903 (M_NEW_LEAD_TBL.EXISTS(p_sales_lead_id) AND (M_NEW_LEAD_TBL(p_sales_lead_id).DOWNLOAD_FLAG = 'Y'))
904 )
905 THEN
906 l_download_status := 'I';
907 ELSIF (p_line_last_update_date > t_last_sync_time)
908 THEN
909 l_download_status := 'U';
910 ELSE
911 l_download_status := 'O';
912 END IF;
913 RETURN l_download_status;
914
915 END Check_Lead_Det_Download;
916
917 /* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
918 FUNCTION Check_CST_SalesTeam_Download
919 (p_team_creation_date IN DATE
920 ,p_team_last_update_date IN DATE
921 ,p_customer_id IN NUMBER
922 ,p_group_last_update_date IN DATE
923 ,p_resource_last_update_date IN DATE
924 ) RETURN VARCHAR2 IS
925 l_download_status VARCHAR2(1) := 'I';
926 BEGIN
927
928 IF ((M_FULL_SYNC = TRUE) OR (p_customer_id >= M_SERVER_PK_ID_MAX)) THEN
929 RETURN l_download_status;
930 END IF;
931
932 IF ((p_team_creation_date > t_last_sync_time) OR
933 (M_NEW_CUST_TBL.EXISTS(p_customer_id) AND (M_NEW_CUST_TBL(p_customer_id).DOWNLOAD_FLAG = 'Y'))
934 )
935 THEN
936 l_download_status := 'I';
937 ELSIF ((p_team_last_update_date > t_last_sync_time) OR
938 (p_group_last_update_date > t_last_sync_time) OR
939 (p_resource_last_update_date > t_last_sync_time)
940 )
941 THEN
942 l_download_status := 'U';
943 ELSE
944 l_download_status := 'O';
945 END IF;
946
947 RETURN l_download_status;
948
949 END Check_CST_SalesTeam_Download;
950
951 FUNCTION Check_Opp_SalesTeam_Download
952 (p_team_creation_date IN DATE
953 ,p_team_last_update_date IN DATE
954 ,p_opportunity_id IN NUMBER
955 ,p_group_last_update_date IN DATE
956 ,p_resource_last_update_date IN DATE
957 ) RETURN VARCHAR2 IS
958 l_download_status VARCHAR2(1) := 'I';
959 BEGIN
960
961 IF ((M_FULL_SYNC = TRUE) OR (p_opportunity_id >= M_SERVER_PK_ID_MAX)) THEN
962 RETURN l_download_status;
963 END IF;
964
965 IF ((p_team_creation_date > t_last_sync_time) OR
966 (M_NEW_OPP_TBL.EXISTS(p_opportunity_id) AND (M_NEW_OPP_TBL(p_opportunity_id).DOWNLOAD_FLAG = 'Y'))
967 )
968 THEN
969 l_download_status := 'I';
970 ELSIF ((p_team_last_update_date > t_last_sync_time) OR
971 (p_group_last_update_date > t_last_sync_time) OR
972 (p_resource_last_update_date > t_last_sync_time)
973 )
974 THEN
975 l_download_status := 'U';
976 ELSE
977 l_download_status := 'O';
978 END IF;
979
980 RETURN l_download_status;
981
982 END Check_Opp_SalesTeam_Download;
983
984
985 FUNCTION Check_Lead_SalesTeam_Download
986 (p_team_creation_date IN DATE
987 ,p_team_last_update_date IN DATE
988 ,p_lead_id IN NUMBER
989 ,p_group_last_update_date IN DATE
990 ,p_resource_last_update_date IN DATE
991 ) RETURN VARCHAR2 IS
992 l_download_status VARCHAR2(1) := 'I';
993 BEGIN
994 IF ((M_FULL_SYNC = TRUE) OR (p_lead_id >= M_SERVER_PK_ID_MAX)) THEN
995 RETURN l_download_status;
996 END IF;
997
998 IF ((p_team_creation_date > t_last_sync_time) OR
999 (M_NEW_LEAD_TBL.EXISTS(p_lead_id) AND (M_NEW_LEAD_TBL(p_lead_id).DOWNLOAD_FLAG = 'Y'))
1000 )
1001 THEN
1002 l_download_status := 'I';
1003 ELSIF ((p_team_last_update_date > t_last_sync_time) OR
1004 (p_group_last_update_date > t_last_sync_time) OR
1005 (p_resource_last_update_date > t_last_sync_time)
1006 )
1007 THEN
1008 l_download_status := 'U';
1009 ELSE
1010 l_download_status := 'O';
1011 END IF;
1012
1013 RETURN l_download_status;
1014 END Check_Lead_SalesTeam_Download;
1015
1016
1017 /* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
1018 FUNCTION Check_Address_Download
1019 (p_customer_id IN NUMBER
1020 ,p_add_creation_date IN DATE
1021 ,p_add_update_date IN DATE
1022 ) RETURN VARCHAR2 IS
1023 l_download_status VARCHAR2(1) := 'I';
1024 BEGIN
1025 IF ((M_FULL_SYNC = TRUE) OR (p_customer_id >= M_SERVER_PK_ID_MAX)) THEN
1026 RETURN l_download_status;
1027 END IF;
1028 IF ((p_add_creation_date > t_last_sync_time) OR
1029 (M_NEW_CUST_TBL.EXISTS(p_customer_id) AND (M_NEW_CUST_TBL(p_customer_id).DOWNLOAD_FLAG = 'Y'))
1030 )
1031 THEN
1032 l_download_status := 'I';
1033 ELSIF (p_add_update_date > t_last_sync_time)
1034 THEN
1035 l_download_status := 'U';
1036 ELSE
1037 l_download_status := 'O';
1038 END IF;
1039
1040 RETURN l_download_status;
1041 END Check_Address_Download;
1042
1043
1044 /*
1045 ** Passing in a customer, check if it is updateable by this particular
1046 ** resource
1047 ** For contact access priv, the sql will pass in the object_id to check.
1048 ** because contact's updateable belongs to its object that it relates to.
1049 */
1050 FUNCTION CHECK_CUSTOMER_UPDATEBLE
1051 (p_api_version_number IN NUMBER
1052 ,p_init_msg_list IN VARCHAR2
1053 ,p_validation_level IN NUMBER
1054 ,p_customer_id IN NUMBER
1055 ,p_party_type IN VARCHAR2 DEFAULT NULL
1056 ) RETURN VARCHAR2 IS
1057 l_access_privilege VARCHAR2(1);
1058 l_return_status VARCHAR2(1);
1059 l_msg_count NUMBER;
1060 l_msg_data VARCHAR2(500);
1061 /* START BLAM */
1062 l_party_Type VARCHAR2(30);
1063
1064 CURSOR C_Get_Party_Type (p_party_id NUMBER) IS
1065 SELECT party_type
1066 FROM HZ_PARTIES
1067 WHERE party_id = p_party_id;
1068 BEGIN
1069
1070 if p_party_type IS NULL then
1071 OPEN C_Get_Party_Type (p_customer_id);
1072 FETCH C_Get_Party_Type into l_party_type;
1073 IF C_Get_Party_Type%NOTFOUND THEN
1074 RAISE NO_DATA_FOUND;
1075 END IF;
1076 CLOSE C_Get_Party_Type;
1077 else
1078 l_party_type := p_party_type;
1079 end if;
1080
1081 IF l_party_type = 'ORGANIZATION' THEN
1082 /* END BLAM */
1083 AS_ACCESS_PUB.has_organizationAccess
1084 (p_api_version_number => p_api_version_number
1085 ,p_init_msg_list => p_init_msg_list
1086 ,p_validation_level => p_validation_level
1087 ,p_access_profile_rec => t_access_profile_rec
1088 ,p_admin_flag => t_manager_flag
1089 ,p_admin_group_id => t_salesgroup_id
1090 ,p_person_id => t_person_id
1091 ,p_customer_id => p_customer_id
1092 ,p_check_access_flag => 'Y'
1093 ,p_identity_salesforce_id => t_salesforce_id
1094 ,p_partner_cont_party_id => null
1095 ,x_return_status => l_return_status
1096 ,x_msg_count => l_msg_count
1097 ,x_msg_data => l_msg_data
1098 ,x_access_privilege => l_access_privilege
1099 );
1100 /* START BLAM */
1101
1102 ELSIF l_party_type = 'PERSON' THEN
1103 AS_ACCESS_PUB.has_personAccess
1104 (p_api_version_number => p_api_version_number
1105 ,p_init_msg_list => p_init_msg_list
1106 ,p_validation_level => p_validation_level
1107 ,p_access_profile_rec => t_access_profile_rec
1108 ,p_admin_flag => t_manager_flag
1109 ,p_admin_group_id => t_salesgroup_id
1110 ,p_person_id => t_person_id
1111 ,p_security_id => null
1112 ,p_security_type => null
1113 ,p_person_party_id => p_customer_id
1114 ,p_check_access_flag => 'Y'
1115 ,p_identity_salesforce_id => t_salesforce_id
1116 ,p_partner_cont_party_id => null
1117 ,x_return_status => l_return_status
1118 ,x_msg_count => l_msg_count
1119 ,x_msg_data => l_msg_data
1120 ,x_access_privilege => l_access_privilege
1121 );
1122 ELSE
1123 l_access_privilege := 'N';
1124 END IF;
1125 /* END BLAM */
1126
1127 IF (l_access_privilege = 'F')
1128 THEN
1129 RETURN 'Y';
1130 ELSE
1131 RETURN 'N';
1132 END IF;
1133
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 RETURN 'N';
1137 END CHECK_CUSTOMER_UPDATEBLE;
1138
1139 FUNCTION CHECK_OPPORTUNITY_UPDATEBLE
1140 (p_api_version_number IN NUMBER
1141 ,p_init_msg_list IN VARCHAR2
1142 ,p_validation_level IN NUMBER
1143 ,p_opportunity_id IN NUMBER
1144 ) RETURN VARCHAR2 IS
1145 l_access_privilege VARCHAR2(1);
1146 l_return_status VARCHAR2(1);
1147 l_msg_count NUMBER;
1148 l_msg_data VARCHAR2(500);
1149 BEGIN
1150
1151 AS_ACCESS_PUB.has_opportunityAccess
1152 (p_api_version_number => p_api_version_number
1153 ,p_init_msg_list => p_init_msg_list
1154 ,p_validation_level => p_validation_level
1155 ,p_access_profile_rec => t_access_profile_rec
1156 ,p_admin_flag => t_manager_flag
1157 ,p_admin_group_id => t_salesgroup_id
1158 ,p_person_id => t_person_id
1159 ,p_opportunity_id => p_opportunity_id
1160 ,p_check_access_flag => 'Y'
1161 ,p_identity_salesforce_id => t_salesforce_id
1162 ,p_partner_cont_party_id => null
1163 ,x_return_status => l_return_status
1164 ,x_msg_count => l_msg_count
1165 ,x_msg_data => l_msg_data
1166 ,x_access_privilege => l_access_privilege
1167 );
1168
1169 IF (l_access_privilege = 'F')
1170 THEN
1171 RETURN 'Y';
1172 ELSE
1173 RETURN 'N';
1174 END IF;
1175
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 RETURN 'N';
1179 END CHECK_OPPORTUNITY_UPDATEBLE;
1180
1181 FUNCTION CHECK_LEAD_UPDATEBLE
1182 (p_api_version_number IN NUMBER
1183 ,p_init_msg_list IN VARCHAR2
1184 ,p_validation_level IN NUMBER
1185 ,p_sales_lead_id IN NUMBER
1186 ) RETURN VARCHAR2 IS
1187 l_access_privilege VARCHAR2(1);
1188 l_return_status VARCHAR2(1);
1189 l_msg_count NUMBER;
1190 l_msg_data VARCHAR2(500);
1191 BEGIN
1192
1193 AS_ACCESS_PUB.has_leadAccess
1194 (p_api_version_number => p_api_version_number
1195 ,p_init_msg_list => p_init_msg_list
1196 ,p_validation_level => p_validation_level
1197 ,p_access_profile_rec => t_access_profile_rec
1198 ,p_admin_flag => t_manager_flag
1199 ,p_admin_group_id => t_salesgroup_id
1200 ,p_person_id => t_person_id
1201 ,p_sales_lead_id => p_sales_lead_id
1202 ,p_check_access_flag => 'Y'
1203 ,p_identity_salesforce_id => t_salesforce_id
1204 ,p_partner_cont_party_id => null
1205 ,x_return_status => l_return_status
1206 ,x_msg_count => l_msg_count
1207 ,x_msg_data => l_msg_data
1208 ,x_access_privilege => l_access_privilege
1209 );
1210
1211 IF (l_access_privilege = 'F')
1212 THEN
1213 RETURN 'Y';
1214 ELSE
1215 RETURN 'N';
1216 END IF;
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 RETURN 'N';
1221 END CHECK_LEAD_UPDATEBLE;
1222
1223 /*
1224 FUNCTION CHECK_CUSTOMER_UPDATEBLE(p_customer_id IN NUMBER) RETURN VARCHAR2 IS
1225 v_dummy VARCHAR2(1);
1226 CURSOR C_CUSTOMER_UPDATEABLE (p_customer_id NUMBER,
1227 p_resource_id NUMBER) IS
1228 SELECT 'X' FROM AS_ACCESSES_ALL
1229 WHERE salesforce_id = p_resource_id
1230 AND customer_id = p_customer_id
1231 AND lead_id is NULL
1232 AND sales_lead_id is NULL;
1233 BEGIN
1234 OPEN C_CUSTOMER_UPDATEABLE(p_customer_id, t_salesforce_id);
1235 FETCH C_CUSTOMER_UPDATEABLE INTO v_dummy;
1236 IF C_CUSTOMER_UPDATEABLE%NOTFOUND
1237 THEN
1238 CLOSE C_CUSTOMER_UPDATEABLE;
1239 RETURN 'N';
1240 END IF;
1241 CLOSE C_CUSTOMER_UPDATEABLE;
1242 RETURN 'Y';
1243 END CHECK_CUSTOMER_UPDATEBLE;
1244 */
1245
1246 /*
1247 ** Given a sales group id, check if this sales rep is manager of this sales group
1248 */
1249 FUNCTION CHECK_MANAGER_FLAG(p_group_id IN NUMBER) RETURN VARCHAR2 IS
1250 v_dummy VARCHAR2(1);
1251 CURSOR C_MANAGER_FLAG(p_group_id NUMBER, p_salesforce_id NUMBER) IS
1252 SELECT 'X' FROM AS_FC_SALESFORCE_V
1253 WHERE salesforce_id = p_salesforce_id
1254 AND sales_group_id = p_group_id
1255 AND member_delete_flag <> 'Y'
1256 AND rrel_delete_flag <> 'Y'
1257 AND manager_flag = 'Y';
1258 BEGIN
1259 OPEN C_MANAGER_FLAG(p_group_id, t_salesforce_id);
1260 FETCH C_MANAGER_FLAG INTO v_dummy;
1261 IF C_MANAGER_FLAG%NOTFOUND
1262 THEN
1263 CLOSE C_MANAGER_FLAG;
1264 RETURN 'N';
1265 END IF;
1266 CLOSE C_MANAGER_FLAG;
1267 RETURN 'Y';
1268 END CHECK_MANAGER_FLAG;
1269
1270
1271 FUNCTION GET_SOURCE_NAME(p_source_code_id IN NUMBER) RETURN VARCHAR2 IS
1272 l_source_name VARCHAR2(2000) := NULL;
1273 CURSOR C_SOURCE_NAME(p_source_code_id NUMBER) IS
1274 SELECT name FROM AMS_P_SOURCE_CODES_V
1275 WHERE SOURCE_CODE_ID = p_source_code_id;
1276 BEGIN
1277 OPEN C_SOURCE_NAME(p_source_code_id);
1278 FETCH C_SOURCE_NAME INTO l_source_name;
1279 IF C_SOURCE_NAME%NOTFOUND
1280 THEN
1281 l_source_name := NULL;
1282 END IF;
1283 CLOSE C_SOURCE_NAME;
1284 RETURN l_source_name;
1285 END GET_SOURCE_NAME;
1286
1287 /*
1288 ** quote header incremental sync
1289 */
1290 FUNCTION Check_Quote_Download
1291 (p_qot_creation_date IN DATE
1292 ,p_qot_update_date IN DATE
1293 ,p_qot_header_id IN NUMBER
1294 ,p_cust_accnt_update_date IN DATE
1295 ,p_customer_update_date IN DATE
1296 ,p_org_contact_update_date IN DATE
1297 ,p_rel_update_date IN DATE
1298 ,p_contact_party_update_date IN DATE
1299 ,p_sold_to_party_update_date IN DATE
1300 ,p_related_obj_update_date IN DATE
1301 ,p_related_opp_update_date IN DATE
1302
1303 ) RETURN VARCHAR2 IS
1304
1305 l_new_qot_rec ASL_NEW_QUOTE_REC_TYPE;
1306 l_download_status VARCHAR2(1) := 'I';
1307 BEGIN
1308 IF (M_FULL_SYNC = FALSE) THEN
1309 IF (p_qot_header_id >= M_SERVER_PK_ID_MAX) THEN
1310 RETURN l_download_status;
1311 END IF;
1312 IF ((p_qot_creation_date > t_last_sync_time) OR
1313 (M_NEW_QOT_TBL.EXISTS(p_qot_header_id))
1314 )
1315 THEN
1316 IF (M_NEW_QOT_TBL.EXISTS(p_qot_header_id)
1317 ) THEN
1318 M_NEW_QOT_TBL(p_qot_header_id).DOWNLOAD_FLAG := 'Y';
1319 ELSE
1320 l_new_qot_rec.QUOTE_HEADER_ID := p_qot_header_id;
1321 l_new_qot_rec.DOWNLOAD_FLAG := 'Y';
1322 M_NEW_QOT_TBL(p_qot_header_id) := l_new_qot_rec;
1323 END IF;
1324 ELSIF ((p_qot_update_date > t_last_sync_time) OR
1325 (p_cust_accnt_update_date > t_last_sync_time) OR
1326 (p_customer_update_date > t_last_sync_time) OR
1327 (p_org_contact_update_date > t_last_sync_time) OR
1328 (p_rel_update_date > t_last_sync_time) OR
1329 (p_contact_party_update_date > t_last_sync_time) OR
1330 (p_sold_to_party_update_date > t_last_sync_time) OR
1331 (p_related_obj_update_date > t_last_sync_time) OR
1332 (p_related_opp_update_date > t_last_sync_time)
1333 )
1334 THEN
1335 l_download_status := 'U';
1336 ELSE
1337 l_download_status := 'O';
1338 END IF;
1339
1340 END IF;
1341
1342 RETURN l_download_status;
1343 END Check_Quote_Download;
1344
1345
1346 /*
1347 ** quote detail incremental sync
1348 */
1349 FUNCTION Check_Quote_Det_Download
1350 (p_quote_line_creation_date IN DATE
1351 ,p_quote_line_update_date IN DATE
1352 ,p_quote_line_det_update_date IN DATE
1353 ,p_quote_header_id IN NUMBER
1354 ,p_quote_line_id IN NUMBER
1355 ,p_quote_line_detail_id IN NUMBER
1356 ) RETURN VARCHAR2 IS
1357 l_new_qot_det_rec ASL_NEW_QUOTE_DET_REC_TYPE;
1358 l_download_status VARCHAR2(1) := 'I';
1359
1360 BEGIN
1361
1362 IF (M_FULL_SYNC = FALSE) THEN
1363 IF ((p_quote_line_id >= M_SERVER_PK_ID_MAX) OR
1364 (p_quote_header_id >= M_SERVER_PK_ID_MAX)) THEN
1365 RETURN l_download_status;
1366 END IF;
1367 IF ((p_quote_line_creation_date > t_last_sync_time) OR
1368 (M_NEW_QOT_TBL.EXISTS(p_quote_header_id) AND (M_NEW_QOT_TBL(p_quote_header_id).DOWNLOAD_FLAG = 'Y'))
1369 )
1370 THEN
1371 l_new_qot_det_rec.QUOTE_HEADER_ID := p_quote_header_id;
1372 l_new_qot_det_rec.QUOTE_LINE_ID := p_quote_line_id;
1373 l_new_qot_det_rec.QUOTE_LINE_DETAIL_ID := p_quote_line_detail_id;
1374 M_NEW_QOT_DET_TBL(p_quote_line_id) := l_new_qot_det_rec;
1375 ELSIF ((p_quote_line_update_date > t_last_sync_time) OR
1376 (p_quote_line_det_update_date > t_last_sync_time)
1377 )
1378 THEN
1379 l_download_status := 'U';
1380 ELSE
1381 l_download_status := 'O';
1382 END IF;
1383 END IF;
1384 return l_download_status;
1385 END Check_Quote_Det_Download;
1386
1387 /*
1388 ** quote shipment incremental sync
1389 */
1390 FUNCTION Check_Quote_Shipment_Download
1391 (p_quote_shipment_creation_date IN DATE
1392 ,p_quote_shipment_update_date IN DATE
1393 ,p_quote_header_id IN NUMBER
1394 ,p_quote_shipment_id IN NUMBER
1395 ,p_ship_to_site_update_date IN DATE
1396 ,p_ship_to_relation_update_date IN DATE
1397 ,p_ship_to_contact_update_date IN DATE
1398
1399 ) RETURN VARCHAR2 IS
1400 l_download_status VARCHAR2(1) := 'I';
1401
1402 BEGIN
1403
1404 IF (M_FULL_SYNC = FALSE) THEN
1405 IF ((p_quote_header_id >= M_SERVER_PK_ID_MAX))
1406 THEN
1407 RETURN l_download_status;
1408 END IF;
1409 IF ((p_quote_shipment_creation_date > t_last_sync_time) OR
1410 (M_NEW_QOT_TBL.EXISTS(p_quote_header_id) AND (M_NEW_QOT_TBL(p_quote_header_id).DOWNLOAD_FLAG = 'Y'))
1411 )
1412 THEN
1413 l_download_status := 'I';
1414 ELSIF ((p_quote_shipment_update_date > t_last_sync_time) OR
1415 (p_ship_to_site_update_date > t_last_sync_time) OR
1416 (p_ship_to_relation_update_date > t_last_sync_time) OR
1417 (p_ship_to_contact_update_date > t_last_sync_time)
1418 )
1419 THEN
1420 l_download_status := 'U';
1421 ELSE
1422 l_download_status := 'O';
1423 END IF;
1424 END IF;
1425 return l_download_status;
1426 END Check_Quote_Shipment_Download;
1427
1428 /*
1429 ** quote payment incremental sync
1430 */
1431 FUNCTION Check_Quote_Payment_Download
1432 (p_quote_payment_creation_date IN DATE
1433 ,p_quote_payment_update_date IN DATE
1434 ,p_quote_header_id IN NUMBER
1435 ,p_quote_payment_id IN NUMBER
1436
1437 ) RETURN VARCHAR2 IS
1438 l_download_status VARCHAR2(1) := 'I';
1439
1440 BEGIN
1441
1442 IF (M_FULL_SYNC = FALSE) THEN
1443 IF ((p_quote_header_id >= M_SERVER_PK_ID_MAX))
1444 THEN
1445 RETURN l_download_status;
1446 END IF;
1447 IF ((p_quote_payment_creation_date > t_last_sync_time) OR
1448 (M_NEW_QOT_TBL.EXISTS(p_quote_header_id) AND (M_NEW_QOT_TBL(p_quote_header_id).DOWNLOAD_FLAG = 'Y'))
1449 )
1450 THEN
1451 l_download_status := 'I';
1452 ELSIF (p_quote_payment_update_date > t_last_sync_time)
1453 THEN
1454 l_download_status := 'U';
1455 ELSE
1456 l_download_status := 'O';
1457 END IF;
1458 END IF;
1459 return l_download_status;
1460 END Check_Quote_Payment_Download;
1461
1462
1463 /*
1464 ** quote price adjustment incremental sync
1465 */
1466 FUNCTION Check_Quote_Price_Adj_Download
1467 (p_price_Adj_creation_date IN DATE
1468 ,p_price_Adj_update_date IN DATE
1469 ,p_quote_header_id IN NUMBER
1470 ,p_quote_line_id IN NUMBER
1471 ,p_price_adjustment_id IN NUMBER
1472
1473 ) RETURN VARCHAR2 IS
1474 l_download_status VARCHAR2(1) := 'I';
1475
1476 BEGIN
1477
1478 IF (M_FULL_SYNC = FALSE) THEN
1479 IF (p_quote_line_id >= M_SERVER_PK_ID_MAX)
1480 THEN
1481 RETURN l_download_status;
1482 END IF;
1483 IF ((p_price_Adj_creation_date > t_last_sync_time) OR
1484 (M_NEW_QOT_DET_TBL.EXISTS(p_quote_line_id) )
1485 )
1486 THEN
1487 l_download_status := 'I';
1488 ELSIF (p_price_Adj_update_date > t_last_sync_time)
1489 THEN
1490 l_download_status := 'U';
1491 ELSE
1492 l_download_status := 'O';
1493 END IF;
1494 END IF;
1495 return l_download_status;
1496 END Check_Quote_Price_Adj_Download;
1497
1498 /*
1499 ** quote sales team incremental sync
1500 */
1501 FUNCTION Check_Quote_Salesteam_Download
1502 (p_qot_Salesteam_creation_date IN DATE
1503 ,p_qot_Salesteam_update_date IN DATE
1504 ,p_quote_header_id IN NUMBER
1505 ,p_quote_access_id IN NUMBER
1506 ) RETURN VARCHAR2 IS
1507 l_download_status VARCHAR2(1) := 'I';
1508
1509 BEGIN
1510
1511 IF (M_FULL_SYNC = FALSE) THEN
1512 IF ((p_quote_header_id >= M_SERVER_PK_ID_MAX) )
1513 THEN
1514 RETURN l_download_status;
1515 END IF;
1516 IF ((p_qot_Salesteam_creation_date > t_last_sync_time) OR
1517 (M_NEW_QOT_DET_TBL.EXISTS(p_quote_header_id) AND (M_NEW_QOT_TBL(p_quote_header_id).DOWNLOAD_FLAG = 'Y') )
1518 )
1519 THEN
1520 l_download_status := 'I';
1521 ELSIF (p_qot_Salesteam_update_date > t_last_sync_time)
1522 THEN
1523 l_download_status := 'U';
1524 ELSE
1525 l_download_status := 'O';
1526 END IF;
1527 END IF;
1528 return l_download_status;
1529 END Check_Quote_Salesteam_Download;
1530
1531 /*
1532 ** quote sales credit incremental sync
1533 */
1534 FUNCTION Check_Qot_Salescredit_Download
1535 (p_qot_scredit_creation_date IN DATE
1536 ,p_qot_scredit_update_date IN DATE
1537 ,p_quote_header_id IN NUMBER
1538 ,p_quote_sales_credit_id IN NUMBER
1539 ) RETURN VARCHAR2 IS
1540 l_download_status VARCHAR2(1) := 'I';
1541
1542 BEGIN
1543
1544 IF (M_FULL_SYNC = FALSE) THEN
1545 IF ((p_quote_header_id >= M_SERVER_PK_ID_MAX) )
1546 THEN
1547 RETURN l_download_status;
1548 END IF;
1549 IF ((p_qot_scredit_creation_date > t_last_sync_time) OR
1550 (M_NEW_QOT_TBL.EXISTS(p_quote_header_id) AND (M_NEW_QOT_TBL(p_quote_header_id).DOWNLOAD_FLAG = 'Y'))
1551 )
1552 THEN
1553 l_download_status := 'I';
1554 ELSIF (p_qot_scredit_update_date > t_last_sync_time)
1555 THEN
1556 l_download_status := 'U';
1557 ELSE
1558 l_download_status := 'O';
1559 END IF;
1560 END IF;
1561
1562 return l_download_status;
1563 END Check_Qot_Salescredit_Download;
1564
1565
1566 /*
1567 ** Inventory Item incremental sync
1568 */
1569 FUNCTION Check_Inv_Item_Download
1570 (p_inv_item_creation_date IN DATE
1571 ,p_inv_item_b_update_date IN DATE
1572 ,p_inv_item_tl_update_date IN DATE
1573 ,p_inv_catgry_update_date IN DATE
1574 ,p_inv_uom_update_date IN DATE
1575 ,p_inv_category_id IN NUMBER
1576 ,p_inv_item_id IN NUMBER
1577 ) RETURN VARCHAR2 IS
1578 l_download_status VARCHAR2(1) := 'I';
1579
1580 BEGIN
1581
1582 IF (M_FULL_SYNC = FALSE AND M_OLD_INV_TBL.EXISTS(p_inv_category_id) )
1583 THEN
1584 IF ((p_inv_item_id >= M_SERVER_PK_ID_MAX) )
1585 THEN
1586 RETURN l_download_status;
1587 END IF;
1588 IF (p_inv_item_creation_date > t_last_sync_time)
1589 THEN
1590 l_download_status := 'I';
1591 ELSIF ( (p_inv_item_b_update_date > t_last_sync_time) OR
1592 (p_inv_item_tl_update_date > t_last_sync_time) OR
1593 (p_inv_catgry_update_date > t_last_sync_time) OR
1594 (p_inv_uom_update_date > t_last_sync_time)
1595 )
1596 THEN
1597 l_download_status := 'U';
1598 ELSE
1599 l_download_status := 'O';
1600 END IF;
1601 END IF;
1602
1603 return l_download_status;
1604 END Check_Inv_Item_Download;
1605
1606
1607 /*
1608 ** price list line incremental sync
1609 */
1610 FUNCTION Check_Price_List_Download
1611 (p_list_line_creation_date IN DATE
1612 ,p_list_line_update_date IN DATE
1613 ,p_line_attr_update_date IN DATE
1614 ,p_list_header_id IN NUMBER
1615 ,p_inv_category_id IN NUMBER
1616 ) RETURN VARCHAR2 IS
1617 l_download_status VARCHAR2(1) := 'I';
1618
1619 BEGIN
1620
1621 IF (M_FULL_SYNC = FALSE AND M_OLD_PRICE_LIST_TBL.EXISTS(p_list_header_id) AND
1622 M_OLD_INV_TBL.EXISTS(p_inv_category_id) )
1623 THEN
1624 IF (p_list_line_creation_date > t_last_sync_time)
1625 THEN
1626 l_download_status := 'I';
1627 ELSIF ((p_list_line_update_date > t_last_sync_time) OR
1628 (p_line_attr_update_date > t_last_sync_time)
1629 )
1630 THEN
1631 l_download_status := 'U';
1632 ELSE
1633 l_download_status := 'O';
1634 END IF;
1635 END IF;
1636
1637 return l_download_status;
1638 END Check_Price_List_Download;
1639
1640
1641 /*
1642 ** customer account incremental sync
1643 */
1644 /* BLAM Changed references to M_NEW_ORG_TBL to M_NEW_CUST_TBL */
1645 FUNCTION Check_Cust_Account_Download
1646 (p_customer_id IN NUMBER
1647 ,p_cust_accnt_id IN NUMBER
1648 ,p_cust_accnt_creation_date IN DATE
1649 ,p_cust_update_date IN DATE
1650 ,p_cust_accnt_update_date IN DATE
1651 ,p_loc_update_date IN DATE
1652 ,p_site_update_date IN DATE
1653 ,p_site_use_update_date IN DATE
1654 ) RETURN VARCHAR2 IS
1655 l_new_account_rec ASL_CUSTOMER_ACCOUNT_REC_TYPE;
1656 l_download_status VARCHAR2(1) := 'I';
1657 BEGIN
1658 IF (M_FULL_SYNC = FALSE) THEN
1659 IF (p_customer_id >= M_SERVER_PK_ID_MAX OR p_cust_accnt_id >= M_SERVER_PK_ID_MAX) THEN
1660 RETURN l_download_status;
1661 END IF;
1662 IF ((M_NEW_CUST_TBL.EXISTS(p_customer_id) AND (M_NEW_CUST_TBL(p_customer_id).DOWNLOAD_FLAG = 'Y')) OR
1663 (p_cust_accnt_creation_date > t_last_sync_time)
1664 )
1665 THEN
1666 l_download_status := 'I';
1667 l_new_account_rec.CUST_ACCOUNT_ID := p_cust_accnt_id;
1668 M_NEW_CUST_ACCOUNT_TBL(p_cust_accnt_id) := l_new_account_rec;
1669 ELSIF ((p_cust_update_date > t_last_sync_time) OR
1670 (p_cust_accnt_update_date > t_last_sync_time) OR
1671 (p_loc_update_date > t_last_sync_time) OR
1672 (p_site_update_date > t_last_sync_time) OR
1673 (p_site_use_update_date > t_last_sync_time)
1674 )
1675 THEN
1676 l_download_status := 'U';
1677 ELSE
1678 l_download_status := 'O';
1679 END IF;
1680 END IF;
1681 RETURN l_download_status;
1682 END Check_Cust_Account_Download;
1683
1684 /*
1685 ** related customer address incremental sync
1686 */
1687 FUNCTION Check_Rel_Cust_Addr_Download
1688 (p_cust_accnt_id IN NUMBER
1689 ,p_cust_rel_creation_date IN DATE
1690 ,p_cust_update_date IN DATE
1691 ,p_loc_update_date IN DATE
1692 ,p_site_update_date IN DATE
1693 ) RETURN VARCHAR2 IS
1694 l_download_status VARCHAR2(1) := 'I';
1695 BEGIN
1696 IF (M_FULL_SYNC = FALSE) THEN
1697 IF (p_cust_accnt_id >= M_SERVER_PK_ID_MAX) THEN
1698 RETURN l_download_status;
1699 END IF;
1700 IF ((p_cust_rel_creation_date > t_last_sync_time) OR
1701 (M_NEW_CUST_ACCOUNT_TBL.EXISTS(p_cust_accnt_id))
1702 )
1703 THEN
1704 l_download_status := 'I';
1705 ELSIF ((p_cust_update_date > t_last_sync_time) OR
1706 (p_loc_update_date > t_last_sync_time) OR
1707 (p_site_update_date > t_last_sync_time)
1708 )
1709 THEN
1710 l_download_status := 'U';
1711 ELSE
1712 l_download_status := 'O';
1713 END IF;
1714 END IF;
1715 RETURN l_download_status;
1716 END Check_Rel_Cust_Addr_Download;
1717
1718 /*
1719 ** related customer contact incremental sync
1720 */
1721 FUNCTION Check_Rel_Cust_Cont_Download
1722 (p_cust_accnt_id IN NUMBER
1723 ,p_cust_rel_creation_date IN DATE
1724 ,p_cust_update_date IN DATE
1725 ,p_contact_update_date IN DATE
1726 ) RETURN VARCHAR2 IS
1727 l_download_status VARCHAR2(1) := 'I';
1728 BEGIN
1729 IF (M_FULL_SYNC = FALSE) THEN
1730 IF (p_cust_accnt_id >= M_SERVER_PK_ID_MAX) THEN
1731 RETURN l_download_status;
1732 END IF;
1733 IF ((p_cust_rel_creation_date > t_last_sync_time) OR
1734 (M_NEW_CUST_ACCOUNT_TBL.EXISTS(p_cust_accnt_id))
1735 )
1736 THEN
1737 l_download_status := 'I';
1738 ELSIF ((p_cust_update_date > t_last_sync_time) OR
1739 (p_contact_update_date > t_last_sync_time)
1740 )
1741 THEN
1742 l_download_status := 'U';
1743 ELSE
1744 l_download_status := 'O';
1745 END IF;
1746 END IF;
1747 RETURN l_download_status;
1748 END Check_Rel_Cust_Cont_Download;
1749
1750 PROCEDURE SET_DEFAUL_ORG_ID
1751 (p_default_org_id IN NUMBER
1752 ) IS
1753 BEGIN
1754 t_org_id := p_default_org_id;
1755 END SET_DEFAUL_ORG_ID;
1756
1757 PROCEDURE SET_ENABLED_MODULES
1758 (p_module_name IN VARCHAR2
1759 ) IS
1760 BEGIN
1761 IF p_module_name = 'ORGANIZATION' THEN
1762 t_org_enabled_flag := TRUE;
1763 END IF;
1764
1765 /* BLAM */
1766 IF p_module_name = 'PERSON' THEN
1767 t_per_enabled_flag := TRUE;
1768 END IF;
1769 /* BLAM */
1770
1771 IF p_module_name = 'CONTACT' THEN
1772 t_cnt_enabled_flag := TRUE;
1773 END IF;
1774
1775 IF p_module_name = 'OPPORTUNITY' THEN
1776 t_opp_enabled_flag := TRUE;
1777 END IF;
1778
1779 IF p_module_name = 'LEAD' THEN
1780 t_lead_enabled_flag := TRUE;
1781 END IF;
1782
1783 IF p_module_name = 'FORECAST' THEN
1784 t_frcst_enabled_flag := TRUE;
1785 END IF;
1786
1787 IF p_module_name = 'QUOTE' THEN
1788 t_qot_enabled_flag := TRUE;
1789 END IF;
1790
1791 END SET_ENABLED_MODULES;
1792
1793 /*
1794 ** End of quote incremental sync
1795 */
1796
1797 /*
1798 *Overload lead/opp dirty flag for incremental sync home page
1799 *display problem caused by a change in the related org.
1800 */
1801 FUNCTION GET_LEAD_DIRTY
1802 (p_lead_creation_date IN DATE
1803 ,p_lead_last_update_date IN DATE
1804 ,p_customer_update_date IN DATE
1805 ) RETURN VARCHAR2 IS
1806 BEGIN
1807 IF (M_FULL_SYNC = FALSE) THEN
1808 IF (p_customer_update_date > t_last_sync_time)
1809 THEN
1810 IF ((p_lead_creation_date < t_last_sync_time) AND
1811 (p_lead_last_update_date < t_last_sync_time)
1812 )
1813 THEN
1814 return 'NI';
1815 END IF;
1816 END IF;
1817 END IF;
1818 RETURN '';
1819 END GET_LEAD_DIRTY;
1820
1821 FUNCTION GET_OPPORTUNITY_DIRTY
1822 (p_opp_creation_date IN DATE
1823 ,p_opp_update_date IN DATE
1824 ,p_customer_update_date IN DATE
1825 ) RETURN VARCHAR2 IS
1826 BEGIN
1827 IF (M_FULL_SYNC = FALSE) THEN
1828 IF (p_customer_update_date > t_last_sync_time)
1829 THEN
1830 IF ((p_opp_creation_date < t_last_sync_time) AND
1831 (p_opp_update_date < t_last_sync_time)
1832 )
1833 THEN
1834 return 'NI';
1835 END IF;
1836 END IF;
1837 END IF;
1838 RETURN '';
1839 END GET_OPPORTUNITY_DIRTY;
1840
1841 END ASL_EXCEL_UTIL_PVT;
1842
[Home] [Help]