1 package body CSM_HTML5_PKG as
2 /*$Header: csmhtm5b.pls 120.1.12020000.2 2013/04/09 06:24:47 saradhak noship $*/
3
4 -- DESCRIPTION
5 -- This package is used by CSM HTML5 MFS Application
6 --
7 -- HISTORY
8 -- 01-jun-2010 saradhak Created
9 -- 17-jan-2012 saradhak Added FND VAULT
10 -- 18-mar-2013 saradhak Added Conflict Resolution
11 g_current_tranid NUMBER;
12 G_SESSION_ID number;
13
14 G_CSM_SCHEMA VARCHAR2(5):='CSM';
15 G_APPS_SCHEMA VARCHAR2(5):='APPS';
16 g_pattern varchar2(2) :='/';
17
18 TYPE numtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 TYPE clobtype IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
20 prime_list numtype;
21 prime_cnt NUMBER:=0;
22
23 g_e NUMBER :=17;
24 g_n NUMBER :=3233;
25 g_d NUMBER :=2753;
26
27 g_nl_chr VARCHAR2(10) :=fnd_global.local_chr(10);
28
29 CURSOR c_get_inq_Table(c_piv_name VARCHAR2)
30 IS SELECT INQ_NAME FROM ASG_PUB_ITEM WHERE ITEM_ID =C_PIV_NAME;
31
32 procedure set_context(context_name in varchar2, attr_name in varchar2,attr_value in varchar2)
33 is begin
34 dbms_session.set_context(context_name, attr_name, attr_value);
35 end set_context;
36
37 function handle_vault(l_mode in varchar2,key in varchar2,val in varchar2 DEFAULT NULL) return varchar2
38 is
39 x varchar2(100):='Done';
40 begin
41 set_context('CSM5_VAULT_NS', 'VAULT', 'Y');
42 IF(l_mode='GET') then x:=fnd_vault.get('CSM',key);
43 else fnd_vault.puts('CSM',key,val,'CSM5_VAULT_NS.VAULT'); end if;
44 set_context('CSM5_VAULT_NS', 'VAULT', 'N');
45 return x;
46 end handle_vault;
47
48 procedure dbms_clearAllContext
49 IS
50 BEGIN
51 DBMS_SESSION.CLEAR_ALL_CONTEXT('CSM5_VAULT_NS');
52 DBMS_SESSION.SET_CONTEXT ('CSM5_VAULT_NS','APPLICATION','STARTED');
53 END dbms_clearAllContext;
54
55 procedure dbms_createContext
56 IS
57 X VARCHAR2(100);
58 BEGIN
59 begin
60 select sys_context('CSM5_VAULT_NS', 'APPLICATION') into x from dual;
61 IF(x='STARTED') THEN
62 return;
63 END IF;
64 exception
65 when others then
66 null;
67 end;
68 EXECUTE IMMEDIATE 'create context CSM5_VAULT_NS using CSM_HTML5_PKG';
69 DBMS_SESSION.SET_CONTEXT ('CSM5_VAULT_NS','APPLICATION','STARTED');
70 END dbms_createContext;
71
72 PROCEDURE PARSE_XML(l_xml_clob_payload IN CLOB,
73 X_COL_NAME_LIST OUT NOCOPY CSM_VARCHAR_LIST,
74 X_COL_VALUE_LIST OUT NOCOPY CSM_VARCHAR_LIST,
75 X_COL_NAME_IDX OUT NOCOPY numtype,
76 X_COL_CLOB_LIST OUT NOCOPY clobtype,
77 p_fix_esc_chars IN BOOLEAN:=false)
78
79 AS
80
81 --l_xml_payload XMLTYPE;
82 --l_xml_clob_payload CLOB;
83
84 l_xml_doc xmldom.DOMDocument;
85 l_xml_parser xmlparser.Parser;
86 l_xml_node_list xmldom.DOMNodeList;
87 l_xml_node xmldom.DOMNode;
88 l_xml_node_len NUMBER;
89 len2 number;
90 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
91 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
92
93 BEGIN
94 --Convert it to CLOB to parse it
95 /*l_xml_clob_payload := l_xml_payload.getCLOBVal();*/
96
97 --PARSE the XML
98 l_xml_parser := xmlparser.newParser;
99 xmlparser.parseClob(l_xml_parser, l_xml_clob_payload);
100 L_XML_DOC := XMLPARSER.GETDOCUMENT(L_XML_PARSER);
101 xmlparser.freeParser(l_xml_parser);
102 l_xml_node_list := xmldom.getElementsByTagName(l_xml_doc, '*');
103 L_XML_NODE_LEN := XMLDOM.GETLENGTH(L_XML_NODE_LIST);
104 L_COL_NAME_LIST.EXTEND(L_XML_NODE_LEN);
105 l_COL_VALUE_LIST.EXTEND(l_xml_node_len);
106 -- loop through elements
107 FOR I IN 2..L_XML_NODE_LEN-1 LOOP
108 l_xml_node := xmldom.item(l_xml_node_list, i);
109
110 l_COL_NAME_LIST(i-1) := xmldom.getNodeName(l_xml_node);
111 L_XML_NODE := XMLDOM.GETFIRSTCHILD(L_XML_NODE);
112 If xmldom.getNodeType(l_xml_node) = xmldom.TEXT_NODE THEN
113 BEGIN
114 l_COL_VALUE_LIST(i-1) := xmldom.getNodeValue(l_xml_node);
115 IF p_fix_esc_chars THEN
116 l_COL_VALUE_LIST(i-1):=replace(replace(replace(l_COL_VALUE_LIST(i-1),'%amp;',fnd_global.local_chr(38)),'%lt;','<'),'%gt;','>');
117 END IF;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 IF INSTR(TO_CHAR(SQLCODE),'6502')>0 THEN
122 X_COL_NAME_IDX(i-1):=99;
123 X_COL_CLOB_LIST(i-1):=xmldom.getNodeValue(l_xml_node);
124 IF p_fix_esc_chars THEN
125 X_COL_CLOB_LIST(i-1):=replace(replace(replace(X_COL_CLOB_LIST(i-1),'%amp;',fnd_global.local_chr(38)),'%lt;','<'),'%gt;','>');
126 END IF;
127 CSM_UTIL_PKG.LOG('Found clob data in column: '||l_COL_NAME_LIST(i-1), 'CSM_HTML5_PKG.PARSE_XML',FND_LOG.LEVEL_PROCEDURE);
128 ELSE
129 CSM_UTIL_PKG.LOG('Exception with error code :'||TO_CHAR(SQLCODE)||' and message:'||substr(sqlerrm,0,2000), 'CSM_HTML5_PKG.PARSE_XML',FND_LOG.LEVEL_PROCEDURE);
130 RAISE;
131 END IF;
132 END;
133 ELSE
134 l_COL_VALUE_LIST(i-1) := NULL;
135 END IF;
136
137 END LOOP;
138 x_COL_NAME_LIST := l_COL_NAME_LIST;
139 X_COL_VALUE_LIST := L_COL_VALUE_LIST;
140
141
142 EXCEPTION WHEN OTHERS THEN
143 CSM_UTIL_PKG.LOG('Exception :'||substr(sqlerrm,0,2000), 'CSM_HTML5_PKG.PARSE_XML', FND_LOG.LEVEL_PROCEDURE);
144 RAISE;
145 END PARSE_XML;
146
147
148 PROCEDURE GET_XML_PAYLOAD
149 ( p_TABLE_NAME IN VARCHAR2,
150 p_pk_name IN VARCHAR2,
151 p_pk_value IN VARCHAR2,
152 x_XML_PAYLOAD OUT NOCOPY CLOB
153 )
154 AS
155 l_QUERY_TEXT1 VARCHAR2(1000);
156 l_xml CLOB;
157 qrycontext DBMS_XMLGEN.ctxHandle;
158 BEGIN
159 /* Notes:
160 ======
161 1. getXml fails when chr(31) or chr(0) is encountered in any of the column data
162 */
163 /* This query is causing too much SQL Parse processing.
164 Switching to bind variable processing */
165 --l_QUERY_TEXT1 := 'SELECT * FROM ' || p_TABLE_NAME ||' WHERE '|| p_pk_name||'in ('||p_pk_value||')';
166
167
168 /* Works with single column PKs. OK for now as we use it with access_id only */
169 l_QUERY_TEXT1 := 'SELECT * FROM ' ||
170 p_TABLE_NAME ||
171 ' WHERE ' || p_pk_name || ' = :PK_VALUE';
172
173 --Execute the SQL query
174 qrycontext := DBMS_XMLGEN.newcontext(l_QUERY_TEXT1) ;
175 DBMS_XMLGEN.setBindValue(qrycontext, 'PK_VALUE', p_pk_value);
176 DBMS_XMLGEN.setnullhandling (qrycontext, DBMS_XMLGEN.empty_tag);
177
178 l_xml := DBMS_XMLGEN.getxml (qrycontext);
179 dbms_xmlgen.closeContext(qrycontext);
180 x_XML_PAYLOAD := l_xml;
181
182 EXCEPTION WHEN OTHERS THEN
183 CSM_UTIL_PKG.LOG('Exception for Query:'||l_QUERY_TEXT1, 'CSM_HTML5_PKG.GET_XML_PAYLOAD', FND_LOG.LEVEL_PROCEDURE);
184 raise;
185 END GET_XML_PAYLOAD;
186
187 -----------------START CACHE APIS---------------------------
188 PROCEDURE INSERT_CACHE(P_U IN VARCHAR2,PI IN VARCHAR2,P_D IN CLOB, P_CNT IN NUMBER)
189 IS
190 PRAGMA AUTONOMOUS_TRANSACTION;
191 BEGIN
192 /* Bug 16105095. Never cache csm_sequences data */
193 IF(PI = 'CSM_SEQUENCES' AND P_U <> 'CSM_CACHE_PVT') THEN
194 ROLLBACK;
195 RETURN ;
196 END IF;
197
198 IF P_D IS NOT NULL THEN
199 INSERT INTO CSM_HTML5_CACHE(USER_NAME,PI_NAME,DATA,REC_CNT,SESSION_ID) VALUES(P_U,PI,P_D,P_CNT,G_SESSION_ID);
200 END IF;
201 COMMIT;
202 EXCEPTION
203 WHEN OTHERS THEN --unique index violation can happen for DDL cache when processed concurrently and it is insignificant
204 CSM_UTIL_PKG.LOG('Unique index violation(Race): for user:'||P_U ||' in session:'||G_SESSION_ID, 'CSM_HTML5_PKG.GET_XML_PAYLOAD', FND_LOG.LEVEL_PROCEDURE);
205 ROLLBACK;
206 IF(P_U<>'CSM_CACHE_PVT') THEN
207 RAISE;
208 END IF;
209 END INSERT_CACHE;
210
211 PROCEDURE DELETE_CACHE(PI IN VARCHAR2,P_U IN VARCHAR2:=NULL)
212 IS
213 PRAGMA AUTONOMOUS_TRANSACTION;
214 BEGIN
215 IF P_U IS NULL THEN --always
216 DELETE FROM CSM_HTML5_CACHE WHERE PI_NAME=PI;
217 ELSE
218 DELETE FROM CSM_HTML5_CACHE WHERE PI_NAME=PI AND USER_NAME=P_U;
219 END IF;
220
221 COMMIT;
222 END DELETE_CACHE;
223
224 FUNCTION GET_CACHED(P_U IN VARCHAR2,PI IN VARCHAR2,X_DATA OUT NOCOPY CLOB,X_CNT OUT NOCOPY NUMBER) RETURN BOOLEAN
225 IS
226 BEGIN
227 /* Bug 16105095. Never cache csm_sequences data*/
228 IF(PI = 'CSM_SEQUENCES' AND P_U <> 'CSM_CACHE_PVT') THEN
229 RETURN FALSE;
230 END IF;
231
232 SELECT DATA,REC_CNT INTO X_DATA,X_CNT FROM CSM_HTML5_CACHE WHERE USER_NAME=P_U AND PI_NAME=PI;
233 CSM_UTIL_PKG.LOG(pi||' hit in '||p_u||' cache', 'CSM_HTML5_PKG.GET_CACHED', FND_LOG.LEVEL_PROCEDURE);
234 RETURN TRUE;
235 EXCEPTION
236 WHEN OTHERS THEN
237 RETURN FALSE;
238 END GET_CACHED;
239
240 PROCEDURE REMOVE_DIRTY_CACHE
241 IS
242 PRAGMA AUTONOMOUS_TRANSACTION;
243 BEGIN
244 DELETE FROM CSM_HTML5_CACHE B WHERE EXISTS(SELECT 1 FROM ASG_SYSTEM_DIRTY_QUEUE
245 WHERE CLIENT_ID=B.USER_NAME
246 AND PUB_ITEM=B.PI_NAME);
247 COMMIT;
248 END REMOVE_DIRTY_CACHE;
249
250 -----------------END CACHE APIs---------------------------
251
252 Procedure getPrimes
253 IS
254 prime boolean;
255 begin
256 if(prime_list.count > 0) then
257 return;
258 end if;
259
260 for i in reverse 3..200
261 loop
262 prime:=true;
263 for j in 2..sqrt(i)
264 loop
265 if (mod(i,j)=0) then prime:=false; exit;
266 end if;
267 end loop;
268
269 if(prime) then
270 prime_cnt:=prime_cnt+1;
271 prime_list(prime_cnt) := i;
272 end if;
273 end loop;
274 end getPrimes;
275
276 FUNCTION GCD(small number, big number) return NUMBER
277 IS
278 modl number;
279 begin
280 if(small > big) then return GCD(big,small); end if;
281
282 modl:= mod(big,small);
283 if( modl = 0) then return small;
284 else return GCD(modl,small);
285 end if;
286 end GCD;
287
288 FUNCTION isCoPrime(a in NUMBER, b in number) return boolean
289 IS
290 BEGIN
291 if(GCD(a,b)=1) then return true;
292 else return false; end if;
293 END isCoPrime;
294
295 FUNCTION nextInt (minN IN NUMBER, maxN IN number) RETURN NUMBER
296 IS
297 x NUMBER;
298 modl number;
299 BEGIN
300 x:= round(dbms_random.value*100000);
301
302 modl := mod(x,maxN);
303
304 IF (modl<minN) THEN RETURN nextInt(minN,maxN);
305 ELSE RETURN modl; END IF;
306
307 END nextInt;
308
309
310 FUNCTION generate_rsa_keys return VARCHAR2
311 IS
312 p number; i number; e number;
313 q number; k number; phi number;
314 r number; n number; d number;
315 BEGIN
316
317 if(prime_list.count = 0) then
318 getPrimes;
319 end if;
320
321 k:=ceil(prime_cnt/2);
322 i:=nextInt(1,k+1);
323 p:=prime_list(i);
324
325 i:=nextInt(k+1,prime_cnt-1);
326 q:=prime_list(i);
327
328 -- insert into csm_log values('Chosen Primes in '||prime_cnt||': p=' || p||' and q='||q ,sysdate);
329
330 phi :=(p-1)*(q-1);
331 n := p*q;
332
333 loop
334 e:=nextInt(2,phi+1);
335 exit when isCoPrime(e,phi);
336 end loop;
337
338 d:=phi;
339
340 loop
341 d:=d-1;
342 exit when ( d>1 and (mod((d*e),phi) = 1) );
343 if(d<0) then
344 return generate_rsa_keys;
345 end if;
346 end loop;
347
348 return e||':'||d||':'||n;
349 END generate_rsa_keys;
350
351 PROCEDURE set_rsa_keys(p_user_name IN VARCHAR2,x_e OUT NOCOPY NUMBER,x_d OUT NOCOPY NUMBER, x_n OUT NOCOPY NUMBER
352 ,p_in_prof IN VARCHAR2 :='RSA_KEY')
353 IS
354 l_str VARCHAR2(200);
355 l_t1 NUMBER;
356 l_t2 NUMBER;
357 BEGIN
358
359 IF p_in_prof='RSA_KEY' THEN
360 l_str:=handle_vault('GET','RSA_KEY_'||asg_base.get_user_id(p_user_name));
361 --CSM_UTIL_PKG.LOG('vault get:'||l_str, 'CSM_HTML5_PKG.set_rsa_keys', FND_LOG.LEVEL_PROCEDURE);
362 ELSE
363 select prf_value into l_str from csm_html5_clients_info where user_name=p_user_name and prf_name=p_in_prof;
364 END IF;
365
366 l_t1:=instr(l_str,':',1,1);
367 l_t2:=instr(l_str,':',1,2);
368 x_e:=to_number(substr(l_str,1,l_t1-1));
369 x_d:=to_number(substr(l_str,l_t1+1,l_t2-l_t1-1));
370 x_n:=to_number(substr(l_str,l_t2+1));
371 Exception
372 when others then
373 x_e:=g_e;
374 x_d:=g_d;
375 x_n:=g_n;
376 END set_rsa_keys;
377
378 function modPow(num in number,e in number,m in number) return NUMBER
379 IS
380 res NUMBER:=num;
381 BEGIN
382 for i in 2..e
383 loop
384 res:=mod((res *num),m);
385 end loop;
386 return res;
387 end modPow;
388
389 function decrypt(p_user_name IN VARCHAR2, input in varchar2) return varchar2
390 IS
391 crypted VARCHAR2(1000) :=input;
392
393 y number;
394 decrypted VARCHAR2(100) :='';
395 l_e NUMBER := g_e;
396 l_d NUMBER := g_d;
397 l_n NUMBER := g_n;
398
399 BEGIN
400
401 set_rsa_keys(p_user_name,l_e,l_d,l_n);
402
403 loop
404 exit when crypted is null or instr(crypted,g_pattern)=0 ;
405 y:=instr(crypted,g_pattern);
406 decrypted := decrypted || fnd_global.local_chr(modPow(substr(crypted,1,y-1), l_d,l_n));
407 crypted:=substr(crypted,y+length(g_pattern));
408 end loop;
409 return decrypted;
410 END decrypt;
411
412 function removeHex(input in varchar2) return varchar2
413 is
414 inp varchar2(1000):=input;
415 begin
416 for i in 97..102
417 loop
418 inp:=replace(inp,fnd_global.local_chr(i),i-87);
419 end loop;
420 return inp;
421 end removeHex;
422
423 function decode(p_input in varchar2) return varchar2
424 IS
425 input varchar2(1000) :=removeHex(p_input);
426 CNT NUMBER :=to_number(substr(input,-1));
427 INP VARCHAR2(1000) :=substr(input,1,length(input)-1);
428 places VARCHAR2(100);
429 a VARCHAR2(1000);
430 b VARCHAR2(1000);
431 BEGIN
432
433 cnt:= to_number(substr(inp, (-1 * cnt)));
434 inp:=substr(inp,1,length(inp)-length(to_char(cnt)));
435 places:= substr(inp, (-1 * cnt));
436 inp:=substr(inp,1,length(inp)-cnt-1); --last place append "/"
437
438 cnt:=0;
439 for i in 1..length(places)
440 LOOP
441 cnt:=cnt + to_number(substr(places,i,1));
442 a:=substr(inp,1,cnt-1);
443 b:=substr(inp,cnt+1);
444 inp:=a||g_pattern||b;
445 END LOOP;
446
447 return inp ||'/';
448
449 END decode;
450
451 Function encode(p_input IN VARCHAR2) return VARCHAR2
452 IS
453 k number; y number; a varchar2(2000); b varchar2(2000);
454 re VARCHAR2(4000):=p_input;
455 codedAt VARCHAR2(1000) :='';
456 str varchar2(100);
457 begin
458 k:=length(p_input);
459
460 while(instr(re,g_pattern) <> 0)
461 loop
462 y:=instr(re,g_pattern);
463 a:=substr(re,1,y-1);
464 b:=substr(re, (y+length(g_pattern)));
465 codedAt:=codedAt||to_char((k-nvl(length(b),0)));
466 k:=nvl(length(b),0);
467 re:=a||to_char( mod(to_number(substr(dbms_random.value,2,10)),10))||b;
468 end loop;
469
470 str:=to_char(length(codedAt)-1); --last / not reqd, omit
471 codedAt:=substr(codedAt,1,length(codedAt)-1)||str||length(str);
472 re := re||codedAt;
473 return re;
474 end encode;
475
476 function encrypt(p_input IN VARCHAR2,p_e IN NUMBER,p_n IN NUMBER) return varchar2
477 IS
478 crypted varchar2(4000):= '';
479 ch char(1);
480 begin
481 for i in 1..length(p_input)
482 loop
483 ch:=substr(p_input,i,1);
484 crypted := crypted || to_char(modPow(ascii(ch), p_e,p_n)) ||g_pattern;
485 end loop;
486
487 return crypted;
488 end;
489
490 procedure set_user_profile(p_uname IN VARCHAR2,pname IN VARCHAR2, pvalue IN VARCHAR2)
491 is PRAGMA AUTONOMOUS_TRANSACTION;
492 x varchar2(100);
493 begin
494
495 IF pname='RSA_KEY' THEN
496 x:=handle_vault('PUT','RSA_KEY_'||asg_base.get_user_id(p_uname),pvalue); --key size max is 30
497 --CSM_UTIL_PKG.LOG('vault put:'||x, 'CSM_HTML5_PKG.set_user_profile', FND_LOG.LEVEL_PROCEDURE);
498 ELSE
499 UPDATE csm_html5_clients_info SET PRF_VALUE=pvalue
500 WHERE PRF_NAME=pname AND USER_NAME=p_uname;
501
502 IF SQL%ROWCOUNT= 0 THEN
503 INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(p_uname,pname, pvalue);
504 END IF;
505
506 END IF;
507 COMMIT;
508 end set_user_profile;
509
510 function get_rsa_keys(x_e OUT NOCOPY NUMBER,x_d OUT NOCOPY NUMBER, x_n OUT NOCOPY NUMBER) return VARCHAR2
511 Is
512 l_str VARCHAR2(200);
513 l_t1 NUMBER;
514 l_t2 NUMBER;
515 BEGIN
516
517 l_str:=generate_rsa_keys;
518
519 l_t1:=instr(l_str,':',1,1);
520 l_t2:=instr(l_str,':',1,2);
521 x_e:=to_number(substr(l_str,1,l_t1-1));
522 x_d:=to_number(substr(l_str,l_t1+1,l_t2-l_t1-1));
523 x_n:=to_number(substr(l_str,l_t2+1));
524
525 return l_str;
526 end get_rsa_keys;
527
528 procedure MARK_ERROR(p_user_name IN VARCHAR2, p_PI_NAME IN VARCHAR2, p_pk_value IN VARCHAR2)
529 IS
530 l_temp VARCHAR2(4000);
531 l_t1 NUMBER;
532 l_t2 NUMBER;
533 found boolean:=false;
534 l_sub varchar2(4000);
535 l_sub2 varchar2(4000);
536 BEGIN
537 BEGIN
538 SELECT PRF_VALUE INTO l_temp FROM csm_html5_clients_info
539 WHERE USER_NAME=p_user_name
540 AND PRF_NAME='XML_ERROR';
541
542 IF length(l_temp)>3000 THEN
543 UPDATE csm_html5_clients_info
544 SET PRF_VALUE=l_temp||'.'
545 WHERE USER_NAME=p_user_name
546 AND PRF_NAME='XML_ERROR';
547 CSM_UTIL_PKG.LOG('No more space to MARK_ERROR' , 'CSM_HTML5_PKG.MARK_ERROR', FND_LOG.LEVEL_PROCEDURE);
548 RETURN;
549 END IF;
550
551 l_t1 := INSTR(l_temp,p_PI_NAME);
552
553 IF l_t1>0 THEN
554 l_t2:=INSTR(l_temp,';',l_t1);
555 l_t1:=length(p_PI_NAME)+1;
556 l_sub:=substr(l_temp,l_t1,l_t2-l_t1);
557 found := false;
558 LOOP
559 l_t1:=INSTR(l_sub,'-');
560 If l_t1>0 THEN
561 l_sub2:= substr(l_sub,1,l_t1-1);
562 l_sub:=substr(l_sub,l_t1+1);
563 ELSE
564 l_sub2 := l_sub;
565 l_sub:='';
566 END IF;
567 IF (l_sub2 = p_pk_value) THEN
568 found:=true;
569 END IF;
570 EXIT WHEN found OR l_t1<=0;
571 END LOOP;
572
573 IF found THEN
574 return;
575 END IF;
576
577 l_temp:=replace(l_temp,p_PI_NAME||':',p_PI_NAME||':'||p_pk_value||'-');
578 ELSE
579 l_temp:=l_temp||p_PI_NAME||':'||p_pk_value||';';
580 END IF;
581
582 UPDATE csm_html5_clients_info
583 SET PRF_VALUE=l_temp
584 WHERE USER_NAME=p_user_name
585 AND PRF_NAME='XML_ERROR';
586
587 EXCEPTION
588 WHEN no_data_found THEN
589 INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(p_user_name,'XML_ERROR',p_PI_NAME||':'||p_pk_value||';');
590 END;
591 EXCEPTION
592 WHEN OTHERS THEN
593 CSM_UTIL_PKG.LOG('Exception occurred in MARK_ERROR: ' || substr(sqlerrm,0,2000), 'CSM_HTML5_PKG.MARK_ERROR', FND_LOG.LEVEL_PROCEDURE);
594 END MARK_ERROR;
595
596
597 PROCEDURE set_indx_ref(p_idx IN VARCHAR2)
598 IS
599 BEGIN
600 FOR rec IN (SELECT USER_NAME FROM ASG_USER au WHERE ENABLED='Y' AND MULTI_PLATFORM='Y'
601 AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO
602 WHERE USER_NAME=au.USER_NAME AND PRF_NAME='INDXREF' AND PRF_VALUE=p_idx)
603 AND EXISTS(SELECT 1 FROM CSM_HTML5_SYNC_INFO WHERE USER_NAME =au.USER_NAME)
604 )
605 LOOP
606 INSERT INTO CSM_HTML5_CLIENTS_INFO(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(rec.USER_NAME,'INDXREF',p_idx);
607 END LOOP;
608
609 DELETE FROM CSM_HTML5_CACHE WHERE USER_NAME='APPLICATION' AND PI_NAME=(SELECT ITEM_ID FROM ASG_PUB_ITEM_INDEX WHERE INDEX_ID=p_idx) ;
610 END set_indx_ref;
611
612 PROCEDURE set_indx_ref(p_user_name IN VARCHAR2,p_idx IN VARCHAR2)
613 IS
614 BEGIN
615 FOR rec IN (SELECT USER_NAME FROM ASG_USER au WHERE ENABLED='Y' AND MULTI_PLATFORM='Y' AND USER_NAME=p_USER_NAME
616 AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO
617 WHERE USER_NAME=au.USER_NAME AND PRF_NAME='INDXREF' AND PRF_VALUE=p_idx)
618 AND EXISTS(SELECT 1 FROM CSM_HTML5_SYNC_INFO WHERE USER_NAME =au.USER_NAME)
619 )
620 LOOP
621 INSERT INTO CSM_HTML5_CLIENTS_INFO(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(rec.USER_NAME,'INDXREF',p_idx);
622 END LOOP;
623
624 DELETE FROM CSM_HTML5_CACHE WHERE USER_NAME='APPLICATION' AND PI_NAME=(SELECT ITEM_ID FROM ASG_PUB_ITEM_INDEX WHERE INDEX_ID=p_idx) ;
625 END set_indx_ref;
626
627 PROCEDURE remove_indx_ref(p_USER_NAME IN VARCHAR2)
628 IS
629 BEGIN
630 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_USER_NAME AND PRF_NAME like 'INDXREF%';
631 END remove_indx_ref;
632
633 PROCEDURE remove_indx_ref(p_user_name IN VARCHAR2,p_pi IN VARCHAR2)
634 IS
635 BEGIN
636 IF p_user_name is NULL THEN
637 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE PRF_NAME='INDXREF' AND PRF_VALUE IN (SELECT INDEX_ID FROM ASG_PUB_ITEM_INDEX WHERE ITEM_ID=p_pi);
638 ELSE
639 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_USER_NAME
640 AND PRF_NAME='INDXREF' AND PRF_VALUE IN (SELECT INDEX_ID FROM ASG_PUB_ITEM_INDEX WHERE ITEM_ID=p_pi);
641 END IF;
642 END remove_indx_ref;
643
644 PROCEDURE mark_pi_altered(p_pi IN VARCHAR2)
645 IS
646 BEGIN
647 DELETE FROM csm_html5_clients_info WHERE USER_NAME='APPLICATION' AND PRF_NAME='SCHEMA_REUSE';
648 csm_html5_pkg.set_comp_ref(p_pi);
649 DELETE_CACHE(p_pi);
650 END mark_pi_altered;
651
652 PROCEDURE set_comp_ref(p_pi IN VARCHAR2)
653 IS
654 BEGIN
655 FOR rec IN (SELECT USER_NAME FROM ASG_USER au WHERE ENABLED='Y' AND MULTI_PLATFORM='Y'
656 AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO
657 WHERE USER_NAME=au.USER_NAME AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pi)
658 AND NOT EXISTS(SELECT 1 FROM ASG_PURGE_SDQ WHERE USER_NAME =au.USER_NAME AND pub_name = 'SERVICEP' AND TRANSACTION_ID IS NULL)
659 )
660 LOOP
661 INSERT INTO CSM_HTML5_CLIENTS_INFO(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(rec.USER_NAME,'COMPREF',p_pi);
662 END LOOP;
663 remove_indx_ref(null,p_pi);
664 END set_comp_ref;
665
666 PROCEDURE set_comp_ref(p_user_name IN VARCHAR2,p_pi IN VARCHAR2) --any arbitrary pi_name as well
667 IS
668 BEGIN
669 FOR rec IN (SELECT USER_NAME FROM ASG_USER au WHERE ENABLED='Y' AND MULTI_PLATFORM='Y' AND USER_NAME=p_user_name
670 AND MULTI_PLATFORM='Y' AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO
671 WHERE USER_NAME=au.USER_NAME AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pi))
672 LOOP
673 INSERT INTO CSM_HTML5_CLIENTS_INFO(USER_NAME,PRF_NAME,PRF_VALUE) VALUES(p_USER_NAME,'COMPREF',p_pi);
674 END LOOP;
675 remove_indx_ref(p_user_name,p_pi);
676 END set_comp_ref;
677
678 PROCEDURE remove_comp_ref(p_user_name IN VARCHAR2,p_pi IN VARCHAR2)
679 IS
680 BEGIN
681 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_USER_NAME
682 AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pi;
683 END remove_comp_ref;
684
685 PROCEDURE remove_comp_ref(p_user_name IN VARCHAR2)
686 IS
687 BEGIN
688 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_USER_NAME AND PRF_NAME like 'COMPREF%';
689 remove_indx_ref(p_user_name);
690 END remove_comp_ref;
691
692 FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
693 AS
694 v_clob CLOB;
695 v_varchar VARCHAR2(32767);
696 v_start PLS_INTEGER := 1;
697 v_buffer PLS_INTEGER := 32767;
698 BEGIN
699 DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
700
701 FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
702 LOOP
703 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
704
705 DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
706
707 v_start := v_start + v_buffer;
708 END LOOP;
709
710 RETURN v_clob;
711
712 END blob_to_clob;
713
714 FUNCTION clob_to_blob (p_clob CLOB) return BLOB
715 as
716 l_blob blob;
717 l_dest_offset integer := 1;
718 l_source_offset integer := 1;
719 l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
720 l_warning integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
721 BEGIN
722 DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
723 DBMS_LOB.CONVERTTOBLOB
724 (
725 dest_lob =>l_blob,
726 src_clob =>p_clob,
727 amount =>DBMS_LOB.LOBMAXSIZE,
728 dest_offset =>l_dest_offset,
729 src_offset =>l_source_offset,
730 blob_csid =>DBMS_LOB.DEFAULT_CSID,
731 lang_context=>l_lang_context,
732 warning =>l_warning
733 );
734 return l_blob;
735 END clob_to_blob;
736
737 FUNCTION get_index_refresh(p_user_name IN VARCHAR2) RETURN CLOB
738 IS
739 l_stmt CLOB:='';
740 BEGIN
741 FOR rec IN (SELECT item_id ,index_name,index_columns,enabled
742 FROM CSM_HTML5_CLIENTS_INFO a, ASG_PUB_ITEM_INDEX b
743 WHERE a.USER_NAME=p_user_name AND a.PRF_NAME='INDXREF'
744 AND a.PRF_VALUE=b.index_id)
745 LOOP
746 l_stmt:=l_stmt||'appsdb.doDDL("DROP INDEX '||rec.index_name||'",[],";",";");'||g_nl_chr;
747 IF rec.ENABLED='Y' THEN
748 IF(SUBSTR(rec.index_name,-2,1)='U') THEN --assumes only 9 indexes per table
749 l_stmt:=l_stmt||'appsdb.doDDL("CREATE UNIQUE INDEX IF NOT EXISTS '||rec.index_name||' ON '||rec.ITEM_ID||'('||rec.index_columns||')");'||g_nl_chr;
750 ELSE
751 l_stmt:=l_stmt||'appsdb.doDDL("CREATE INDEX IF NOT EXISTS '||rec.index_name||' ON '||rec.ITEM_ID||'('||rec.index_columns||')");'||g_nl_chr;
752 END IF;
753 END IF;
754 END LOOP;
755 return l_stmt;
756 END get_index_refresh ;
757
758 FUNCTION create_index_js( p_pi IN VARCHAR2) RETURN CLOB
759 IS
760 l_stmt CLOB:='';
761 BEGIN
762 FOR rec IN (select index_name,index_columns,enabled from ASG_PUB_ITEM_INDEX where item_id =p_pi)
763 LOOP
764 l_stmt:=l_stmt||'appsdb.doDDL("DROP INDEX '||rec.index_name||'",[],";",";");'||g_nl_chr;
765 IF rec.ENABLED='Y' THEN
766 IF(SUBSTR(rec.index_name,-2,1)='U') THEN --assumes only 9 indexes per table
767 l_stmt:=l_stmt||'appsdb.doDDL("CREATE UNIQUE INDEX IF NOT EXISTS '||rec.index_name||' ON '||p_pi||'('||rec.index_columns||')");'||g_nl_chr;
768 ELSE
769 l_stmt:=l_stmt||'appsdb.doDDL("CREATE INDEX IF NOT EXISTS '||rec.index_name||' ON '||p_pi||'('||rec.index_columns||')");'||g_nl_chr;
770 END IF;
771 END IF;
772 END LOOP;
773 return l_stmt;
774 END create_index_js;
775
776 FUNCTION create_table_js( p_pi IN VARCHAR2) RETURN CLOB
777 IS
778 TYPE l_type IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
779 l_name_tab l_type; l_dtype_tab l_type;
780 l_stmt CLOB:=empty_clob();
781 l_pk_cols VARCHAR2(1000);
782 l_error_message varchar2(4000);
783 TYPE c_curtype is ref cursor;
784 c_cur c_curtype;
785 l_cached BOOLEAN:=false;
786 l_temp NUMBER;
787 BEGIN
788
789 l_cached:=GET_CACHED('CSM_CACHE_PVT',p_pi,l_stmt,l_temp);
790
791 IF NOT l_cached THEN
792 l_stmt:=g_nl_chr;
793 for rec in (select item_id,upper(base_object_name) piv,primary_key_column from asg_pub_item where item_id=p_pi and rownum=1) --only one record selected any way
794 loop
795 l_name_tab.DELETE;
796 l_dtype_tab.DELETE;
797 l_pk_cols:=rec.primary_key_column;
798
799 OPEN c_cur FOR 'select column_name,decode(data_type,''CLOB'',''TEXT'',''CHAR'',''TEXT'',''VARCHAR2'',''TEXT'',''DATE'',''TIME'',''NUMBER'',DECODE(NVL(DATA_SCALE,0),0,''INTEGER'',''REAL''),data_type)
800 from all_tab_columns where owner='''||G_APPS_SCHEMA||''' and table_name='''||rec.piv||''' order by column_id';
801 FETCH c_cur BULK COLLECT INTO l_name_tab,l_dtype_tab;
802 l_stmt:=l_stmt ||'appsdb.doDDL(';
803 l_stmt:=l_stmt||'''CREATE TABLE IF NOT EXISTS '||rec.item_id ||'(';
804 FOR I IN 1..l_name_tab.COUNT
805 LOOP
806 IF(I>1) THEN
807 l_stmt:=l_stmt||',';
808 END IF;
809 l_stmt:=l_stmt||l_name_tab(I)||' '||l_dtype_tab(I);
810 END LOOP;
811
812 l_stmt:=l_stmt||')'');'||g_nl_chr;
813
814 l_stmt:=l_stmt||'appsdb.doDDL("CREATE UNIQUE INDEX IF NOT EXISTS '||p_pi||'_U1_SYS ON '||p_pi||'(ACCESS_ID)");'||g_nl_chr
815 ||'appsdb.doDDL("CREATE UNIQUE INDEX IF NOT EXISTS '||p_pi||'_U2_SYS ON '||p_pi||'('||l_pk_cols||')");'||g_nl_chr;
816
817 CLOSE c_cur;
818 end loop;
819
820 l_stmt:='appsdb.doDDL("DROP INDEX '||p_pi||'_U1_SYS",[],";",";");'||g_nl_chr
821 ||'appsdb.doDDL("DROP INDEX '||p_pi||'_U2_SYS",[],";",";");'||g_nl_chr
822 ||'appsdb.doDDL("DROP TABLE '||p_pi||'",[],";",";");'||l_stmt;
823
824 l_stmt:=l_stmt||create_index_js(p_pi);
825
826 INSERT_CACHE('CSM_CACHE_PVT',p_pi,l_stmt,null);
827 END IF;
828
829 return l_stmt;
830
831 EXCEPTION
832 WHEN others THEN
833 CSM_UTIL_PKG.LOG('Exception occurred in create_table_js: ' || substr(sqlerrm,0,2000), 'CSM_HTML5_PKG.create_table_js', FND_LOG.LEVEL_PROCEDURE);
834 raise;
835 END create_table_js;
836
837 procedure query_get_schema_js(p_result OUT nocopy CLOB,
838 x_return_status OUT nocopy VARCHAR2,
839 x_error_message OUT nocopy VARCHAR2)
840 IS
841 l_stmt CLOB:=g_nl_chr;
842
843 BEGIN
844
845 x_return_status:=fnd_api.g_ret_sts_success;
846 x_error_message:='Success';
847
848 l_stmt := l_stmt ||'function create_mfs_schema(){ '||g_nl_chr;
849
850 for rec in (select item_id,query1 from asg_pub_item where pub_name='SERVICEP' and enabled='Y'
851 and nvl(html5_offline,'N')='Y')
852 loop
853 CSM_UTIL_PKG.LOG('Processing schema of '||rec.item_id,'CSM_HTML5_PKG.query_get_schema_js',FND_LOG.LEVEL_ERROR);
854 l_stmt := l_stmt||create_table_js(rec.item_id);
855 end loop;
856
857 l_stmt := l_stmt||'}'||g_nl_chr;
858 p_result:=l_stmt;
859 EXCEPTION
860 WHEN others THEN
861 x_return_status := fnd_api.g_ret_sts_error;
862 x_error_message := 'Exception occurred in query_get_schema_js: ' || substr(sqlerrm,0,2000);
863 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_HTML5_PKG.query_get_schema_js', FND_LOG.LEVEL_PROCEDURE);
864 END query_get_schema_js;
865
866 procedure query_schema_js( p_user_name IN VARCHAR2,
867 p_result OUT nocopy CLOB,
868 x_return_status OUT nocopy VARCHAR2,
869 x_error_message OUT nocopy VARCHAR2)
870 IS
871
872 instId number:=-1; ret VARCHAR2(1); m varchar2(4000);
873 l_id CSM_INTEGER_LIST :=CSM_INTEGER_LIST();
874 l_ch CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
875 l_dt CSM_DATE_LIST:=CSM_DATE_LIST();
876 l_blob BLOB:=empty_blob();
877
878 BEGIN
879
880 x_return_status:=fnd_api.g_ret_sts_success;
881 x_error_message:='Success';
882
883 BEGIN
884 SELECT to_number(PRF_VALUE) into instId FROM csm_html5_clients_info WHERE USER_NAME='APPLICATION' AND PRF_NAME='SCHEMA_REUSE';
885
886 select result into l_blob from csm_query_results_acc where instance_id = instId and query_id=5;
887 p_result:=blob_to_clob(l_blob);
888 CSM_UTIL_PKG.LOG('Schema Reused from instance:'||instId, 'CSM_HTML5_PKG.query_schema_js', FND_LOG.LEVEL_PROCEDURE);
889 RETURN;
890 EXCEPTION
891 WHEN Others THEN
892 instId:=-1;
893 END;
894
895 CSM_QUERY_PKG.INSERT_INSTANCE(asg_base.get_user_id(p_user_name), 5, NULL, 'REUSABLE_SCHEMA', l_id, l_ch, l_dt,NULL, instId, ret, m);
896
897 CSM_UTIL_PKG.LOG('Instance Insert Status:'||instId||':'||ret||':'||m, 'CSM_HTML5_PKG.query_schema_js', FND_LOG.LEVEL_PROCEDURE);
898
899 IF (ret=fnd_api.g_ret_sts_success) THEN
900 query_get_schema_js(p_result,ret,m);
901
902 IF (ret=fnd_api.g_ret_sts_success) THEN
903 l_blob:=clob_to_blob(p_result);
904 INSERT INTO CSM_QUERY_RESULTS_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , LINE_ID,
905 RESULT ,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
906 VALUES (CSM_QUERY_RESULTS_ACC_S.NEXTVAL, asg_base.get_user_id(p_user_name), 5, instId, 1,
907 l_blob, fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id);
908
909 set_user_profile('APPLICATION','SCHEMA_REUSE',to_char(instId));
910 RETURN;
911 ELSE
912 CSM_UTIL_PKG.LOG(m, 'CSM_HTML5_PKG.query_schema_js', FND_LOG.LEVEL_PROCEDURE);
913 END IF;
914 END IF;
915
916 x_return_status:=ret;
917 x_error_message:=m;
918
919 END query_schema_js;
920
921 PROCEDURE create_ajax_session(p_user_name IN VARCHAR2,p_response IN VARCHAR2,p_sync_id IN NUMBER:=NULL)
922 IS
923 PRAGMA AUTONOMOUS_TRANSACTION;
924 BEGIN
925
926 INSERT INTO csm_html5_ajax_info(AJAX_SESSION_ID,USER_NAME,CREATION_DATE,SYNC_RESPONSE,SYNC_ID)
927 VALUES(csm_html5_ajax_s.NEXTVAL,p_user_name,SYSDATE,p_response,p_sync_id);
928
929 COMMIT;
930 END create_ajax_session;
931
932 FUNCTION create_session(p_user_name IN VARCHAR2,p_mode IN VARCHAR2,p_full_sync IN VARCHAR2 :='N') return NUMBER
933 IS
934 PRAGMA AUTONOMOUS_TRANSACTION;
935 l_session_id NUMBER;
936 l_instance NUMBER:=CSM_QUERY_PKG.G_INST_IN_PROCESS;
937 l_cnt NUMBER:=0;
938 BEGIN
939
940 SELECT csm_html5_sync_s.NEXTVAL INTO l_session_id FROM DUAL;
941
942 INSERT INTO csm_html5_sync_info(SESSION_ID,USER_NAME,START_DATE,FULL_SYNC,STATUS,STATUS_DESC,SYNC_TYPE,Q_INSTANCE_ID)
943 VALUES(l_session_id,p_user_name,SYSDATE,p_full_sync,'IN_PROGRESS','Sync has started successfuly',p_mode,l_instance);
944
945 create_ajax_session(p_user_name,'Sync thru Ajax succeeded',l_session_id);
946
947 COMMIT;
948 RETURN l_session_id;
949 END create_session;
950
951
952 procedure update_session_details(p_session_id IN NUMBER,p_pi IN VARCHAR2, p_count IN NUMBER,p_dummy IN OUT NOCOPY BOOLEAN,p_comp_ref IN VARCHAR2 :='N')
953 IS
954 PRAGMA AUTONOMOUS_TRANSACTION;
955 BEGIN
956
957 INSERT INTO csm_html5_sync_details(SESSION_ID, pi_name,record_count,comp_ref)
958 values(p_session_id,p_pi,p_count,p_comp_ref);
959
960 IF(p_dummy) THEN
961 p_dummy := false;
962 UPDATE csm_html5_sync_info SET STATUS_DESC=STATUS_DESC||' :Processed pi-s :'||p_pi
963 WHERE SESSION_ID=p_session_id;
964 ELSE
965 UPDATE csm_html5_sync_info SET STATUS_DESC=STATUS_DESC||' ,'||p_pi
966 WHERE SESSION_ID=p_session_id;
967 END IF;
968
969 COMMIT;
970 END update_session_details;
971
972 procedure end_session(p_session_id IN NUMBER,p_status IN VARCHAR2, p_status_desc IN VARCHAR2:=NULL)
973 IS
974 PRAGMA AUTONOMOUS_TRANSACTION;
975 BEGIN
976
977 UPDATE csm_html5_sync_info SET END_DATE=SYSDATE ,STATUS =p_status,STATUS_DESC=NVL(p_status_desc,STATUS_DESC||' : Sync Ended Successfully.')
978 WHERE SESSION_ID=p_session_id;
979
980 COMMIT;
981 END end_session;
982
983 FUNCTION hasHTML5Enabled(p_user_name IN VARCHAR2) return BOOLEAN
984 IS
985 x NUMBER;
986 BEGIN
987 SELECT USER_ID INTO x FROM ASG_USER
988 WHERE USER_NAME=p_user_name
989 AND ENABLED='Y'
990 AND MULTI_PLATFORM='Y';
991
992 RETURN TRUE;
993 EXCEPTION
994 WHEN OTHERS THEN
995 RETURN FALSE;
996 END hasHTML5Enabled;
997
998 FUNCTION authenticate(p_user_name IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN
999 IS
1000 l_password VARCHAR2(200);
1001 l_b NUMBER;
1002 BEGIN
1003
1004 IF NOT hasHTML5Enabled(p_user_name) THEN
1005 RETURN FALSE;
1006 END IF;
1007
1008 l_password := decrypt(p_user_name,decode(p_password));
1009
1010 IF isOIDAuth(p_user_name) THEN
1011 --Bug 14057306
1012 /* RETURN FND_USER_PKG.ValidateSSOLogin(p_user_name,l_password); -- Some may not have applied FND pre-req, they shd use "/local" */
1013 execute immediate 'DECLARE x number:=0; BEGIN IF FND_USER_PKG.ValidateSSOLogin(:1,:2) THEN x:=1; END IF; :3:=x; END;' using p_user_name,l_password, out l_b;
1014
1015 if l_b<>0 then
1016 RETURN TRUE;
1017 else
1018 RETURN FALSE;
1019 end if;
1020 ELSE
1021 RETURN FND_USER_PKG.ValidateLogin(p_user_name,l_password);
1022 END IF;
1023 END authenticate;
1024
1025 procedure query_validate_user( p_user_name IN VARCHAR2,
1026 p_password IN VARCHAR2,
1027 p_result OUT nocopy CLOB,
1028 x_return_status OUT nocopy VARCHAR2,
1029 x_error_message OUT nocopy VARCHAR2)
1030 Is
1031 l_final CLOB:='';
1032 l_response VARCHAR2(200);
1033 l_str VARCHAR2(100);
1034 BEGIN
1035
1036 x_return_status:=fnd_api.g_ret_sts_success;
1037 x_error_message:='Success';
1038
1039 IF NOT isHTML5User(p_user_name) THEN
1040 CSM_UTIL_PKG.LOG('Reporting that '||p_user_name||' is not a HTML5 user', 'CSM_HTML5_PKG.query_validate_user', FND_LOG.LEVEL_PROCEDURE);
1041 l_final:=l_final||'reEnableSyncFunction("Sorry, '''||p_user_name||''' is not authorized to use this app."); ';
1042 l_response := 'query_validate_user: Not a mobile user';
1043 ELSE
1044 IF (authenticate(p_user_name,p_password)) THEN
1045 CSM_UTIL_PKG.LOG('Reporting that '||p_user_name||'-s password is valid', 'CSM_HTML5_PKG.query_validate_user', FND_LOG.LEVEL_PROCEDURE);
1046 l_final:=l_final||'setupMetaData();';
1047 l_response:='query_validate_user: Valid password';
1048 ELSE
1049 CSM_UTIL_PKG.LOG('Reporting that '||p_user_name||'-s password is incorrect', 'CSM_HTML5_PKG.query_validate_user', FND_LOG.LEVEL_PROCEDURE);
1050 l_final:=l_final||'reEnableSyncFunction("Sorry, the password entered is incorrect."); ';
1051 l_response:='query_validate_user: Invalid password';
1052 END IF;
1053 END IF;
1054
1055 create_ajax_session(p_user_name,l_response);
1056
1057 p_result:=l_final;
1058
1059 END query_validate_user;
1060
1061 FUNCTION translateString(p_input IN VARCHAR2) RETURN VARCHAR2
1062 IS
1063 BEGIN
1064 return replace(replace(replace(p_input,'\','\\'),'"','\"'), g_nl_chr, '\n');
1065 END translateString;
1066
1067 FUNCTION translateClobString(p_input IN CLOB) RETURN CLOB
1068 IS
1069 BEGIN
1070 return replace(replace(replace(p_input,'\','\\'),'"','\"'), g_nl_chr, '\n');
1071 END translateClobString;
1072
1073 FUNCTION get_base_object(pi IN VARCHAR2,piv IN VARCHAR2) RETURN VARCHAR2
1074 IS
1075 PRAGMA AUTONOMOUS_TRANSACTION;
1076 l_exists NUMBER;
1077 l_temp_tab VARCHAR2(100):= G_CSM_SCHEMA||'.CSM_5G_'||substr(pi,5);
1078 BEGIN
1079 BEGIN
1080 EXECUTE IMMEDIATE 'SELECT count(1) FROM '||l_temp_tab||' WHERE ROWNUM=1' INTO l_exists;
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 CSM_UTIL_PKG.LOG('Creating base object '||l_temp_tab, 'CSM_HTML5_PKG.get_base_object', FND_LOG.LEVEL_PROCEDURE);
1084 execute immediate 'CREATE GLOBAL TEMPORARY TABLE '||l_temp_tab||' ON COMMIT DELETE ROWS AS SELECT * FROM '||piv;
1085 END;
1086 COMMIT;
1087 RETURN l_temp_tab;
1088 END get_base_object;
1089
1090 FUNCTION set_base_object(pi IN VARCHAR2,piv IN VARCHAR2) RETURN VARCHAR2
1091 IS
1092 l_temp_object VARCHAR2(500);
1093 BEGIN
1094 IF(pi<>'CSM_MTL_SEC_INVENTORIES') THEN
1095 RETURN piv;
1096 END IF;
1097 l_temp_object:=get_base_object(pi,piv);
1098 CSM_UTIL_PKG.LOG('Processing from Temporary table:'||l_temp_object, 'CSM_HTML5_PKG.set_base_object', FND_LOG.LEVEL_PROCEDURE);
1099 execute immediate 'INSERT INTO '||l_temp_object||' SELECT * FROM '||piv;
1100 return l_temp_object;
1101 END set_base_object;
1102
1103
1104 procedure query_sync_data_js(
1105 p_user_name IN VARCHAR2,
1106 p_password IN VARCHAR2,
1107 p_result OUT nocopy CLOB,
1108 x_return_status OUT nocopy VARCHAR2,
1109 x_error_message OUT nocopy VARCHAR2,
1110 p_bypass_auth IN BOOLEAN DEFAULT FALSE
1111 )
1112 IS
1113 TYPE c_curtype is ref cursor;
1114 c_cur c_curtype;
1115 l_stmt CLOB;
1116 l_final CLOB:=g_nl_chr;
1117 l_pk_value VARCHAR2(200);
1118
1119 M Boolean :=false;
1120 l_AUTH Boolean :=false;
1121 l_cached boolean:=false;
1122 l_cache CLOB;
1123
1124 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1125 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1126 l_COL_NAME_IDX numtype;
1127 l_COL_CLOB_LIST clobtype;
1128
1129 l_uname varchar2(200); l_lang varchar2(10);
1130 l_uid number; lrid number; l_app_id number; l_synch_date date;
1131 l_respid number;
1132 l_rec_count NUMBER;
1133 l_total_r_count NUMBER:=0;
1134
1135 l_warning boolean :=false;
1136 l_session_id NUMBER;
1137 l_dummy boolean:=true;
1138 l_e NUMBER;l_d NUMBER;l_n NUMBER;
1139 l_base_object VARCHAR2(500);
1140 BEGIN
1141 x_return_status:=fnd_api.g_ret_sts_success;
1142 x_error_message:='Success';
1143
1144 l_final := l_final ||'function mfs_synch_data(){ '||g_nl_chr;
1145
1146 IF (NOT p_bypass_auth) AND (NOT isHTML5User(p_user_name)) THEN
1147 CSM_UTIL_PKG.LOG('Quitting...since '||p_user_name||' is not an HTML5 user', 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1148 l_final:=l_final||' display_alert(''csm_mfs_error'',10); '||g_nl_chr||'}'||g_nl_chr;
1149 p_result:=l_final||g_nl_chr||' $("#mfs_sync").click(function(){ '||g_nl_chr
1150 || ' mfs_synch_data();'||g_nl_chr
1151 || ' var d=document.getElementById("mfs_sync_script"); var head= document.getElementsByTagName("head")[0]; '||g_nl_chr
1152 || ' if(d) { head.removeChild(d); delete(d);} delete_sync_element(true); }); '||g_nl_chr||g_nl_chr
1153 || ' $("#mfs_sync").trigger("click"); '||g_nl_chr;
1154
1155 CREATE_AJAX_SESSION(p_user_name,'csm_mfs_error: Not authorized HTML5 User');
1156 RETURN;
1157 END IF;
1158
1159 IF p_bypass_auth THEN
1160 l_AUTH := true;
1161 ELSE
1162 l_AUTH := authenticate(p_user_name,p_password) ;
1163 END IF;
1164
1165 IF l_AUTH THEN
1166
1167 set_user_profile(p_user_name,'REUSE_AUTH_6','SUCCESS');
1168
1169 l_session_id:=CREATE_SESSION(p_user_name,'DOWNLOAD','Y');
1170 G_SESSION_ID := l_session_id;
1171 REMOVE_DIRTY_CACHE;
1172
1173 query_schema_js(p_user_name,l_stmt,x_return_status,x_error_message);
1174
1175 IF(x_return_status = fnd_api.g_ret_sts_error) THEN
1176 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1177 delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_6';
1178 END_SESSION(l_session_id,'ERROR',x_error_message);
1179 RETURN;
1180 END IF;
1181
1182 l_final:= l_stmt||l_final||g_nl_chr;
1183
1184 l_final:=l_final||'createLocalSyncTables();create_mfs_schema();sync.session('||to_char(l_session_id)||');'||g_nl_chr||g_nl_chr;
1185
1186 --Config data setup -- start
1187 l_final:=l_final ||'var sqlS="";appsdb.delete_record("DELETE FROM CSM_CONFIG_DATA");'||g_nl_chr;
1188 l_final := l_final ||'sync.addConfig("V","'||get_app_revision||'");'||g_nl_chr;
1189 l_final := l_final ||'appsdb.v="'||get_app_revision||'";'||g_nl_chr;
1190 --Config data setup -- end
1191
1192 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''RRRR-MM-DD HH24:MI:SS''';
1193
1194 CSM_UTIL_PKG.LOG('Beginning PI download', 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1195
1196 select useR_name,language,resource_id,useR_id,responsibility_id,app_id,lasT_synch_date_end
1197 into l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date from asg_user where user_name=upper(p_user_name);
1198 asg_base.init_debug(l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date);
1199
1200
1201 for rec in (select item_id,base_objecT_name from asg_pub_item where pub_name='SERVICEP' and enabled='Y'
1202 and nvl(html5_offline,'N')='Y' order by item_id)
1203 LOOP
1204
1205 CSM_UTIL_PKG.LOG('Processing PI :'||rec.item_id, 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1206
1207 l_final:=l_final ||'appsdb.delete_record("DELETE FROM '||rec.item_id||'");'||g_nl_chr;
1208
1209 DELETE FROM ASG_SYSTEM_DIRTY_QUEUE WHERE CLIENT_ID=p_user_name AND PUB_ITEM=rec.item_id; --clear SDQ records here, so that new records inserted into sdq during this process
1210 --won't be missed. Confirmed that cursor resultSet is the one picked at the time of OPEN
1211 l_cached:=GET_CACHED (p_user_name,rec.item_id,l_stmt,l_rec_count);
1212
1213 IF NOT l_cached THEN
1214 l_rec_count :=0;
1215 l_cache:='';
1216
1217 l_base_object:=rec.base_objecT_name;
1218
1219 IF(rec.item_id='CSM_MTL_SEC_INVENTORIES') THEN
1220 l_base_object := set_base_object(rec.item_id,rec.base_objecT_name);
1221 END IF;
1222
1223 OPEN c_cur FOR 'SELECT ACCESS_ID from '||l_base_object|| ' where access_id is not null';
1224 LOOP
1225 FETCH c_cur into l_pk_value;
1226 EXIT WHEN c_cur%NOTFOUND;
1227
1228 l_rec_count:=l_rec_count+1;
1229 l_total_r_count := l_total_r_count+1;
1230
1231
1232 BEGIN
1233
1234 GET_XML_PAYLOAD(l_base_object,'ACCESS_ID',l_pk_value,l_stmt);
1235 l_COL_NAME_IDX.DELETE;l_COL_CLOB_LIST.DELETE;
1236 PARSE_XML(l_stmt,l_COL_NAME_LIST,L_COL_VALUE_LIST,l_COL_NAME_IDX,l_COL_CLOB_LIST);
1237
1238 IF(l_rec_count=1) THEN
1239 l_stmt :='sqlS="INSERT INTO '||rec.item_id||'('||l_COL_NAME_LIST(1);
1240 FOR j IN 2..l_COL_NAME_LIST.COUNT
1241 LOOP
1242 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1243 l_stmt := l_stmt||','||l_COL_NAME_LIST(j);
1244 END IF;
1245 END LOOP;
1246
1247 l_stmt:= l_stmt||') VALUES(?';
1248
1249 FOR j IN 2..l_COL_VALUE_LIST.COUNT
1250 LOOP
1251 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1252 l_stmt := l_stmt||',?';
1253 END IF;
1254 END LOOP;
1255 l_stmt:= l_stmt||');";'||g_nl_chr||'appsdb.add_record(sqlS,[';
1256 ELSE
1257 l_stmt:='appsdb.add_record(sqlS,[';
1258 END IF;
1259
1260 l_stmt:=l_stmt ||'"'||translateString(l_COL_VALUE_LIST(1))||'"';
1261
1262 FOR j IN 2..l_COL_VALUE_LIST.COUNT
1263 LOOP
1264 IF(l_COL_NAME_LIST(j) IS NOT NULL ) THEN
1265 IF (l_COL_NAME_IDX.EXISTS(j)) THEN
1266 l_stmt:=l_stmt ||',"'||translateClobString(l_COL_CLOB_LIST(J))||'"';
1267 ELSE
1268 IF l_COL_VALUE_LIST(j) IS NOT NULL THEN
1269 l_stmt:=l_stmt ||',"'||translateString(l_COL_VALUE_LIST(J))||'"';
1270 ELSE
1271 l_stmt:=l_stmt ||',null';
1272 END IF;
1273 END IF;
1274 END IF;
1275 END LOOP;
1276
1277
1278 IF(l_rec_count=1) THEN
1279 l_stmt:=l_stmt||'],"x3='''||rec.item_id||''';doProgress(++sqlR,x3);","SSEFN(++sqlR);");';
1280 ELSE
1281 l_stmt:=l_stmt||'],"doProgress(++sqlR,x3);","SSEFN(++sqlR);");';
1282 END IF;
1283
1284 l_final:=l_final||l_stmt||g_nl_chr;
1285 l_cache:=l_cache||l_stmt||g_nl_chr;
1286 EXCEPTION
1287 WHEN Others THEN
1288 CSM_UTIL_PKG.LOG('GET_XML_PAYLOAD failed.', 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1289 MARK_ERROR(p_user_name,rec.item_id,l_pk_value);
1290 l_warning:=true;
1291 l_rec_count:=l_rec_count-1;
1292 l_total_r_count:=l_total_r_count-1;
1293 END;
1294 END LOOP; --PI LEVEL LOOP
1295 CLOSE c_cur;
1296
1297 INSERT_CACHE(p_user_name,rec.item_id,l_cache,l_rec_count);
1298 ELSE
1299 l_final:=l_final||l_stmt||g_nl_chr;
1300 l_total_r_count := l_total_r_count+l_rec_count;
1301 END IF; --cache check
1302
1303 UPDATE_SESSION_DETAILS(l_session_id,rec.item_id,l_rec_count,l_dummy,'Y');
1304 l_final := l_final||'sync.logSyncedPIDetails('||to_char(l_session_id)||',"'||rec.item_id||'",'||to_char(l_rec_count)||',"Y");'||g_nl_chr;
1305
1306 END LOOP; --outermost loop
1307
1308 l_final := 'var tc='||to_char(l_total_r_count)||';var sqlR=0;var x3="";'||l_final||g_nl_chr||g_nl_chr;
1309
1310 /*Gone offline message + book keeping*/
1311
1312
1313 l_final:=l_final||' display_alert(''csm_fdata'',10);sync.goOffline();'||g_nl_chr;
1314 l_final:=l_final||' total_sync_rec_count= tc; sync.logSyncSession('||to_char(l_session_id)||',false,"Y");'||g_nl_chr;
1315
1316 l_final:=l_final||'}'||g_nl_chr;
1317 M:=true;
1318 ELSE
1319 M:=false;
1320 l_final:=l_final||' display_alert(''csm_auth_error'',10); '||g_nl_chr||'}'||g_nl_chr;
1321 CREATE_AJAX_SESSION(p_user_name,'csm_auth_error: Invalid password');
1322 delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_6';
1323 END IF;
1324
1325 l_final:=l_final||g_nl_chr||' $("#mfs_sync").click(function(){ '||g_nl_chr
1326 || ' mfs_synch_data();'||g_nl_chr
1327 || ' var d=document.getElementById("mfs_sync_script"); var head= document.getElementsByTagName("head")[0]; '||g_nl_chr
1328 || ' if(d) { head.removeChild(d); delete(d);} '
1329 ||' delete_sync_element(); }); ';
1330
1331 l_final:=l_final||g_nl_chr||g_nl_chr|| ' $("#mfs_sync").trigger("click"); '||g_nl_chr;
1332
1333 p_result:=l_final;
1334
1335 IF M THEN
1336 /* M:=asg_download.purgeSdq(p_user_name); -- Replaced with delete at PI processing*/
1337 UPDATE asg_purge_sdq set TRANSACTION_ID=NVL(l_session_id,1) WHERE user_name = p_user_name; --first sync over
1338 UPDATE asg_complete_refresh set synch_completed='Y' WHERE user_name = p_user_name;
1339 remove_comp_ref(p_user_name);
1340 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name AND PRF_NAME='APP_REVISION';
1341 IF l_warning THEN
1342 END_SESSION(l_session_id,'SUCCESS_W');
1343 ELSE
1344 END_SESSION(l_session_id,'SUCCESS');
1345 END IF;
1346 END IF;
1347
1348 EXCEPTION
1349 WHEN others THEN
1350 x_return_status := fnd_api.g_ret_sts_error;
1351 x_error_message := 'Exception occurred in query_sync_data_js: ' || substr(sqlerrm,1,3900);
1352 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_HTML5_PKG.query_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1353 delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_6';
1354 END_SESSION(l_session_id,'ERROR',x_error_message);
1355 END query_sync_data_js;
1356
1357 FUNCTION is_complete_ref(p_user_name IN VARCHAR2,p_pi IN VARCHAR2) return BOOLEAN
1358 IS
1359 l_t VARCHAR2(100);
1360 BEGIN
1361 BEGIN
1362 SELECT PRF_VALUE INTO l_t FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name
1363 AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pi;
1364 RETURN TRUE;
1365 EXCEPTION
1366 WHEN Others THEN
1367 NULL;
1368 END;
1369 RETURN FALSE;
1370 END is_complete_ref;
1371
1372 FUNCTION complete_refresh(p_session_id IN NUMBER,p_user_name IN VARCHAR2,p_pi IN VARCHAR2,
1373 p_count IN OUT NOCOPY NUMBER,p_warning OUT NOCOPY BOOLEAN,p_dummy IN OUT NOCOPY BOOLEAN) return CLOB
1374 IS
1375 TYPE c_curtype is ref cursor;
1376 c_cur c_curtype;
1377 l_final CLOB := EMPTY_CLOB();
1378 l_pk_value VARCHAR2(200);
1379 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1380 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1381 l_COL_NAME_IDX numtype;
1382 l_COL_CLOB_LIST clobtype;
1383 l_stmt CLOB:=NULL;
1384 l_rec_count NUMBER:=p_count;
1385 l_cached boolean:=false;
1386 l_cache CLOB;
1387 l_base_object VARCHAR2(500);
1388 BEGIN
1389
1390 CSM_UTIL_PKG.LOG('Processing Complete refresh of PI :'||p_pi, 'CSM_HTML5_PKG.complete_refresh', FND_LOG.LEVEL_PROCEDURE);
1391
1392
1393 --This api processes only one pi at a time though in loop: name=p_pi picks up one record
1394 for rec in (select item_id,base_objecT_name from asg_pub_item where pub_name='SERVICEP' and enabled='Y'
1395 and name=p_pi)
1396 LOOP
1397 l_final:=l_final || create_table_js(p_pi);
1398
1399 DELETE FROM ASG_SYSTEM_DIRTY_QUEUE WHERE CLIENT_ID=p_user_name AND PUB_ITEM=p_pi; --clear records here, so that new records inserted into sdq during this process
1400 --won't be missed. Confirmed that cursor resultSet is the one at the time of OPEN
1401
1402 l_cached:=GET_CACHED(p_user_name,p_pi,l_stmt,l_rec_count);
1403
1404 IF NOT l_cached THEN
1405 l_rec_count:=p_count;
1406 l_cache :='';
1407
1408 l_base_object:=rec.base_objecT_name;
1409
1410 IF(rec.item_id='CSM_MTL_SEC_INVENTORIES') THEN
1411 l_base_object := set_base_object(rec.item_id,rec.base_objecT_name);
1412 END IF;
1413
1414 OPEN c_cur FOR 'SELECT ACCESS_ID from '||l_base_object|| ' where access_id is not null';
1415 LOOP
1416
1417 FETCH c_cur into l_pk_value;
1418 EXIT WHEN c_cur%NOTFOUND;
1419
1420 l_rec_count:=l_rec_count+1;
1421
1422 BEGIN
1423 GET_XML_PAYLOAD(l_base_object,'ACCESS_ID',l_pk_value,l_stmt);
1424 l_COL_NAME_IDX.DELETE;l_COL_CLOB_LIST.DELETE;
1425 PARSE_XML(l_stmt,l_COL_NAME_LIST,L_COL_VALUE_LIST,l_COL_NAME_IDX,l_COL_CLOB_LIST);
1426
1427 IF (l_rec_count=p_count+1) THEN
1428 l_stmt := 'sqlS="INSERT INTO '||rec.item_id||'('||l_COL_NAME_LIST(1);
1429 FOR j IN 2..l_COL_NAME_LIST.COUNT
1430 LOOP
1431 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1432 l_stmt := l_stmt||','||l_COL_NAME_LIST(j);
1433 END IF;
1434 END LOOP;
1435
1436 l_stmt:= l_stmt||') VALUES(?';
1437
1438 FOR j IN 2..l_COL_VALUE_LIST.COUNT
1439 LOOP
1440 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1441 l_stmt := l_stmt||',?';
1442 END IF;
1443 END LOOP;
1444 l_stmt:= l_stmt||');";'||g_nl_chr||'appsdb.add_record(sqlS,[';
1445 ELSE
1446 l_stmt:='appsdb.add_record(sqlS,[';
1447 END IF;
1448
1449 l_stmt:=l_stmt ||'"'||translateString(l_COL_VALUE_LIST(1))||'"';
1450
1451
1452 FOR j IN 2..l_COL_VALUE_LIST.COUNT
1453 LOOP
1454 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1455 IF (l_COL_NAME_IDX.EXISTS(j)) THEN
1456 l_stmt:=l_stmt ||',"'||translateClobString(l_COL_CLOB_LIST(J))||'"';
1457 ELSE
1458 IF l_COL_VALUE_LIST(j) IS NOT NULL THEN
1459 l_stmt:=l_stmt ||',"'||translateString(l_COL_VALUE_LIST(J))||'"';
1460 ELSE
1461 l_stmt:=l_stmt ||',null';
1462 END IF;
1463 END IF;
1464 END IF;
1465 END LOOP;
1466
1467 IF (l_rec_count=p_count+1) THEN
1468 l_stmt:=l_stmt||'],"x3='''||rec.item_id||''';doProgress(++sqlR,x3);","SSEFN(++sqlR);");';
1469 ELSE
1470 l_stmt:=l_stmt||'],"doProgress(++sqlR,x3);","SSEFN(++sqlR);");';
1471 END IF;
1472
1473 l_final:=l_final||l_stmt||g_nl_chr;
1474 l_cache:=l_cache||l_stmt||g_nl_chr;
1475 EXCEPTION
1476 WHEN Others THEN
1477 CSM_UTIL_PKG.LOG('GET_XML_PAYLOAD failed.', 'CSM_HTML5_PKG.complete_refresh', FND_LOG.LEVEL_PROCEDURE);
1478 MARK_ERROR(p_user_name,rec.item_id,l_pk_value);
1479 l_rec_count:=l_rec_count-1;
1480 p_warning:=true;
1481 END;
1482 END LOOP;
1483 CLOSE c_cur;
1484 INSERT_CACHE(p_user_name,p_pi,l_cache,l_rec_count-p_count);
1485 ELSE
1486 l_rec_count:=l_rec_count+p_count;
1487 l_final:=l_final||l_stmt||g_nl_chr;
1488 END IF;
1489
1490 UPDATE_SESSION_DETAILS(p_session_id,rec.item_id,l_rec_count-p_count,p_dummy,'Y');
1491 l_final := l_final||'sync.logSyncedPIDetails('||to_char(p_session_id)||',"'||rec.item_id||'",'||to_char(l_rec_count-p_count)||',"Y");'||g_nl_chr;
1492 end loop;
1493
1494 --remove_comp_ref(p_user_name,p_pi); shd be done in next sync
1495
1496 p_count := l_rec_count;
1497 return l_final;
1498 END complete_refresh;
1499
1500 procedure q_inc_sync_cref_js(
1501 p_user_name IN VARCHAR2,
1502 p_password IN VARCHAR2,
1503 p_result OUT nocopy CLOB,
1504 x_return_status OUT nocopy VARCHAR2,
1505 x_error_message OUT nocopy VARCHAR2
1506 )
1507 IS
1508 BEGIN
1509 q_inc_sync_data_js(p_user_name,p_password,-1,p_result,x_return_status,x_error_message,true);
1510 END q_inc_sync_cref_js;
1511
1512 procedure q_inc_sync_data_js(
1513 p_user_name IN VARCHAR2,
1514 p_password IN VARCHAR2,
1515 p_tranid IN NUMBER,
1516 p_result OUT nocopy CLOB,
1517 x_return_status OUT nocopy VARCHAR2,
1518 x_error_message OUT nocopy VARCHAR2,
1519 p_refresh_all boolean :=false
1520 )
1521 IS
1522
1523 TYPE l_c_idx_list_t IS TABLE OF VARCHAR2(1000) INDEX BY VARCHAR2(200);
1524 TYPE l_n_idx_list_t IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
1525 l_comp_ref_list l_c_idx_list_t;
1526 l_insertDML_pk_list l_c_idx_list_t;
1527 l_insertDML_pi_list l_n_idx_list_t;
1528 l_pp_cnt NUMBER:=0;
1529
1530 l_idx NUMBER; M BOOLEAN:=false;
1531 AUTH BOOLEAN :=FALSE;
1532 K NUMBER;
1533 l_stmt CLOB;
1534 l_final CLOB:=NULL;
1535 l_pk_value VARCHAR2(200);
1536
1537 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1538 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1539 l_COL_NAME_IDX numtype;
1540 l_COL_CLOB_LIST clobtype;
1541
1542 l_uname varchar2(200); l_lang varchar2(10);
1543 l_uid number; lrid number; l_app_id number; l_synch_date date;
1544 l_respid number;
1545
1546 l_rec_count NUMBER :=0;
1547 l_last_pi VARCHAR2(200):=NULL;
1548 l_insertDML_pi VARCHAR2(200):=NULL;
1549 l_insertpk_cols VARCHAR2(1000):=NULL;
1550 l_total_r_count NUMBER :=0;
1551 l_warning boolean:=false;
1552 l_session_id NUMBER;
1553 l_dummy boolean:= true;
1554 l_e NUMBER;l_d NUMBER;l_n NUMBER;
1555 l_str VARCHAR2(100);
1556 l_more_avl BOOLEAN:=false;
1557 l_refresh_all BOOLEAN :=p_refresh_all;
1558 BEGIN
1559 x_return_status:=fnd_api.g_ret_sts_success;
1560 x_error_message:='Success';
1561
1562 IF NOT isHTML5User(p_user_name) THEN
1563 CSM_UTIL_PKG.LOG('Quitting...since '||p_user_name||' is not an HTML5 user', 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1564 l_final :=g_nl_chr||'function mfs_synch_data(){ '||g_nl_chr;
1565 l_final:=l_final||' display_alert(''csm_mfs_error'',10); '||g_nl_chr||'}'||g_nl_chr;
1566 p_result:=l_final||g_nl_chr||' $("#mfs_sync").click(function(){ '||g_nl_chr
1567 || ' mfs_synch_data();'||g_nl_chr
1568 || ' var d=document.getElementById("mfs_sync_script"); var head= document.getElementsByTagName("head")[0]; '||g_nl_chr
1569 || ' if(d) { head.removeChild(d); delete(d);} delete_sync_element(); }); '||g_nl_chr||g_nl_chr
1570 || ' $("#mfs_sync").trigger("click"); '||g_nl_chr;
1571 CREATE_AJAX_SESSION(p_user_name,'csm_mfs_error: Not authorized HTML5 user');
1572 RETURN;
1573 END IF;
1574
1575 AUTH := authenticate(p_user_name,p_password);
1576 IF AUTH THEN
1577
1578 l_session_id:=CREATE_SESSION(p_user_name,'DOWNLOAD');
1579
1580 IF IS_FIRST_SYNC(p_user_name) THEN
1581 CSM_UTIL_PKG.LOG(p_user_name||' has been dropped and recreated. Kicking off Full Refresh', 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1582 UPDATE asg_purge_sdq set TRANSACTION_ID=NVL(l_session_id,1) WHERE user_name = p_user_name; --first sync over
1583 UPDATE asg_complete_refresh set synch_completed='Y' WHERE user_name = p_user_name;
1584 l_refresh_all:=TRUE;
1585 END IF;
1586
1587 IF l_refresh_all THEN
1588 set_user_profile(p_user_name,'REUSE_AUTH_9','SUCCESS');
1589 ELSE --purge old data if last sync is applied
1590
1591 /*****COMPLETE REFRESH***********/
1592 --if comp ref is also set in current TXN
1593 DELETE FROM CSM_HTML5_CLIENTS_INFO a WHERE USER_NAME=p_user_name
1594 AND PRF_NAME LIKE 'COMPREF-TID-%' AND EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO b WHERE a.USER_NAME=b.USER_NAME AND b.PRF_NAME='COMPREF' AND a.PRF_VALUE=b.PRF_VALUE) ;
1595
1596 --if last comp ref is successful
1597 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name
1598 AND PRF_NAME LIKE 'COMPREF-TID-%' AND TO_NUMBER(SUBSTR(PRF_NAME,13))<p_tranid;
1599
1600 --if last comp ref not successful
1601 UPDATE CSM_HTML5_CLIENTS_INFO SET PRF_NAME='COMPREF' WHERE USER_NAME=p_user_name
1602 AND PRF_NAME LIKE 'COMPREF-TID-%';
1603
1604
1605 /*****INDEX REFRESH***********/
1606 --if indx ref is also set in current TXN
1607 DELETE FROM CSM_HTML5_CLIENTS_INFO a WHERE USER_NAME=p_user_name
1608 AND PRF_NAME LIKE 'INDXREF-TID-%' AND EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO b WHERE a.USER_NAME=b.USER_NAME AND b.PRF_NAME='INDXREF' AND a.PRF_VALUE=b.PRF_VALUE) ;
1609
1610 --if last indx ref is successful
1611 DELETE FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name
1612 AND PRF_NAME LIKE 'INDXREF-TID-%' AND TO_NUMBER(SUBSTR(PRF_NAME,13))<p_tranid;
1613
1614 --if last indx ref not successful
1615 UPDATE CSM_HTML5_CLIENTS_INFO SET PRF_NAME='INDXREF' WHERE USER_NAME=p_user_name
1616 AND PRF_NAME LIKE 'INDXREF-TID-%';
1617
1618
1619 /*****ASG SDQ***********/
1620 --remove successfully deleted DMLs from SDQ
1621 DELETE FROM asg_delete_queue
1622 WHERE qid IN (SELECT qid
1623 FROM asg_system_dirty_queue
1624 WHERE client_id = p_user_name AND
1625 (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP')));
1626
1627 --remove successful DMLs from SDQ and unwanted PI-s
1628 DELETE FROM asg_system_dirty_queue
1629 WHERE client_id = p_user_name AND
1630 (transaction_id < p_tranid OR pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='N' AND PUB_NAME='SERVICEP'));
1631
1632 --restore unsuccessful ones for current sync
1633 UPDATE asg_system_dirty_queue SET transaction_id=p_tranid
1634 WHERE client_id = p_user_name and rownum<=2000; --inc sync processes only max 2000 records(exclusive of comp ref) per sync
1635
1636 IF SQL%ROWCOUNT=2000 THEN
1637 l_more_avl := true;
1638 END IF;
1639 END IF;
1640
1641
1642 G_SESSION_ID := l_session_id;
1643 REMOVE_DIRTY_CACHE; --here bcoz it shd be after old SDQ records are cleared
1644
1645 IF l_refresh_all THEN
1646 CSM_UTIL_PKG.LOG('Refresh All is set', 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1647 for pi in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='Y')
1648 loop
1649 set_comp_ref(p_user_name,pi.name);
1650 end loop;
1651 END IF;
1652
1653 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''RRRR-MM-DD HH24:MI:SS''';
1654
1655 select useR_name,language,resource_id,useR_id,responsibility_id,app_id,lasT_synch_date_end
1656 into l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date from asg_user where user_name=upper(p_user_name);
1657 asg_base.init_debug(l_uname,l_lang,lrid,l_uid,l_respid,l_app_id,l_synch_date);
1658
1659
1660 /*****COMPLETE REFRESH***********/
1661 for pi in (SELECT a.NAME FROM ASG_PUB_ITEM a,CSM_HTML5_CLIENTS_INFO b
1662 WHERE nvl(html5_offline,'N')='Y'
1663 AND PRF_NAME='COMPREF'
1664 AND PRF_VALUE=a.NAME
1665 AND b.USER_NAME=p_user_name ORDER BY a.NAME)
1666 loop
1667 l_final:=l_final||g_nl_chr||complete_refresh(l_session_id,p_user_name,pi.name,l_total_r_count,l_warning,l_dummy)||g_nl_chr;
1668
1669
1670 remove_indx_ref(p_user_name,pi.name);
1671
1672 UPDATE CSM_HTML5_CLIENTS_INFO SET PRF_NAME='COMPREF-TID-'||p_tranid
1673 WHERE USER_NAME=p_user_name AND PRF_NAME='COMPREF' AND PRF_VALUE= pi.name; --check and clear in next sync
1674
1675 l_comp_ref_list(pi.name):='1';
1676 end loop;
1677
1678
1679 /*****INDEX REFRESH***********/
1680 l_final:=l_final||get_index_refresh(p_user_name);
1681 UPDATE CSM_HTML5_CLIENTS_INFO
1682 SET PRF_NAME='INDXREF-TID-'||p_tranid
1683 WHERE USER_NAME=p_user_name AND PRF_NAME='INDXREF'; --check and clear in next sync
1684
1685
1686 /*****ASG SDQ PROCESSING***********/
1687 for rec in (select b.pub_item,b.access_id,b.dml_type,base_object_name,a.primary_key_column
1688 from asg_pub_item a,
1689 (select pub_item,access_id,client_id,min(dml_type) as dml_type
1690 from asG_system_dirty_queue
1691 where client_id=p_useR_name
1692 and transaction_id = p_tranid
1693 and nvl(download_flag,'Y')='Y' --Bug 16456574: process conflicts
1694 and pub_item in (SELECT NAME FROM ASG_PUB_ITEM WHERE nvl(html5_offline,'N')='Y')
1695 group by pub_item,access_id,client_id) b
1696 where b.pub_item=a.item_id
1697 order by b.pub_item)
1698 LOOP
1699
1700 CSM_UTIL_PKG.LOG('Found/Processing PI -'||rec.pub_item||' with access_id:'||rec.access_id, 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1701
1702 IF(l_last_pi IS NULL OR l_last_pi <> rec.pub_item) THEN
1703 IF(l_last_pi IS NOT NULL) AND NOT l_comp_ref_list.exists(rec.pub_item) THEN
1704 UPDATE_SESSION_DETAILS(l_session_id,l_last_pi,l_rec_count,l_dummy);
1705 l_final := l_final||'sync.logSyncedPIDetails('||to_char(l_session_id)||',"'||l_last_pi||'",'||to_char(l_rec_count)||',"N");'||g_nl_chr;
1706 END IF;
1707 l_last_pi:=rec.pub_item;
1708 l_rec_count := 0;
1709 END IF;
1710
1711
1712 IF l_comp_ref_list.exists(rec.pub_item) THEN
1713 CSM_UTIL_PKG.LOG('Complete refresh is set for '||p_user_name||'-'||rec.pub_item, 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1714 ELSE
1715 l_rec_count:= l_rec_count+1;
1716 l_total_r_count:=l_total_r_count+1;
1717
1718 If(rec.dml_type = 0) THEN
1719 l_final:=l_final ||'appsdb.delete_record("DELETE FROM '||rec.pub_item||' WHERE ACCESS_ID='||to_char(rec.access_id)||'",[],"doProgress(++sqlR,'''||rec.pub_item||''');");'||g_nl_chr;
1720 ELSE
1721
1722 BEGIN
1723 GET_XML_PAYLOAD(rec.base_objecT_name,'ACCESS_ID',rec.ACCESS_ID,l_stmt);
1724 EXCEPTION
1725 WHEN Others THEN
1726 CSM_UTIL_PKG.LOG('GET_XML_PAYLOAD failed.', 'CSM_HTML5_PKG.q_inc_sync_data_js', FND_LOG.LEVEL_PROCEDURE);
1727 MARK_ERROR(p_user_name,rec.pub_item,rec.ACCESS_ID);
1728 l_rec_count:= l_rec_count-1;
1729 l_total_r_count:=l_total_r_count-1;
1730 l_stmt := NULL;
1731 l_warning:=true;
1732 END;
1733
1734 IF l_stmt IS NOT NULL THEN
1735 l_COL_NAME_IDX.DELETE;l_COL_CLOB_LIST.DELETE;
1736 PARSE_XML(l_stmt,l_COL_NAME_LIST,L_COL_VALUE_LIST,l_COL_NAME_IDX,l_COL_CLOB_LIST);
1737
1738 IF (rec.dml_type = 1) THEN --INSERT dml
1739
1740 l_stmt:='';
1741
1742 IF NOT l_insertDML_pk_list.exists(rec.pub_item) THEN
1743 l_insertDML_pk_list(rec.pub_item):=rec.primary_key_column;
1744 l_pp_cnt:=l_pp_cnt+1;
1745 l_insertDML_pi_list(l_pp_cnt):=rec.pub_item;
1746 l_stmt:='appsdb.doDDL("DROP INDEX '||rec.pub_item||'_U2_SYS",[],";",";");'||g_nl_chr;
1747 END IF;
1748
1749 l_stmt :=l_stmt|| 'appsdb.add_record('||g_nl_chr
1750 ||'"INSERT INTO '||rec.pub_item||'('||l_COL_NAME_LIST(1);
1751 FOR j IN 2..l_COL_NAME_LIST.COUNT
1752 LOOP
1753 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1754 l_stmt := l_stmt||','||l_COL_NAME_LIST(j);
1755 END IF;
1756 END LOOP;
1757
1758 l_stmt:= l_stmt||') VALUES(?';
1759
1760 FOR j IN 2..l_COL_VALUE_LIST.COUNT
1761 LOOP
1762 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1763 l_stmt := l_stmt||',?';
1764 END IF;
1765 END LOOP;
1766
1767 l_stmt:= l_stmt||');",["'||translateString(l_COL_VALUE_LIST(1))||'"';
1768
1769 FOR j IN 2..l_COL_VALUE_LIST.COUNT
1770 LOOP
1771 IF(l_COL_NAME_LIST(j) IS NOT NULL) THEN
1772 IF (l_COL_NAME_IDX.EXISTS(j)) THEN
1773 l_stmt:=l_stmt ||',"'||translateClobString(l_COL_CLOB_LIST(J))||'"';
1774 ELSE
1775 IF l_COL_VALUE_LIST(j) IS NOT NULL THEN
1776 l_stmt:=l_stmt ||',"'||translateString(l_COL_VALUE_LIST(J))||'"';
1777 ELSE
1778 l_stmt:=l_stmt ||',null';
1779 END IF;
1780 END IF;
1781 END IF;
1782 END LOOP;
1783 l_stmt:=l_stmt||'],"doProgress(++sqlR,'''||rec.pub_item||''');");';
1784 l_final:=l_final||l_stmt||g_nl_chr;
1785
1786 ELSE --dml type=2
1787
1788
1789 l_stmt := 'appsdb.update_record("UPDATE '||rec.pub_item||' SET ';
1790 K:=0;
1791 FOR j IN 1..l_COL_NAME_LIST.COUNT
1792 LOOP
1793 IF l_COL_NAME_LIST(j)='ACCESS_ID' THEN
1794 l_idx:=j;
1795 END IF;
1796 IF(l_COL_NAME_LIST(j) IS NOT NULL AND l_COL_NAME_LIST(j)<>'ACCESS_ID' ) THEN
1797 K:=K+1;
1798 IF(K<>1) THEN
1799 l_stmt := l_stmt||',';
1800 END IF;
1801 l_stmt := l_stmt||l_COL_NAME_LIST(j)||'=?';
1802 END IF;
1803 END LOOP;
1804
1805 l_stmt:= l_stmt||' WHERE ACCESS_ID='||l_COL_VALUE_LIST(l_idx);
1806
1807
1808 l_stmt:= l_stmt||';",[';
1809 K:=0;
1810 FOR j IN 1..l_COL_VALUE_LIST.COUNT
1811 LOOP
1812 IF(l_COL_NAME_LIST(j) IS NOT NULL AND j<>l_idx) THEN
1813 K:=K+1;
1814 IF(K<>1) THEN
1815 l_stmt := l_stmt||',';
1816 END IF;
1817 IF (l_COL_NAME_IDX.EXISTS(j)) THEN
1818 l_stmt:=l_stmt ||'"'||translateClobString(l_COL_CLOB_LIST(J))||'"';
1819 ELSE
1820 IF l_COL_VALUE_LIST(j) IS NOT NULL THEN
1821 l_stmt:=l_stmt ||'"'||translateString(l_COL_VALUE_LIST(J))||'"';
1822 ELSE
1823 l_stmt:=l_stmt ||'null';
1824 END IF;
1825 END IF;
1826 END IF;
1827 END LOOP;
1828 l_stmt:=l_stmt||'],"doProgress(++sqlR,'''||rec.pub_item||''');");';
1829
1830 l_final:=l_final||l_stmt||g_nl_chr;
1831 END IF; --rec.dml_type = 1 check
1832 ELSE --l_stmt IS NULL - Else block
1833 l_rec_count:= l_rec_count-1;
1834 l_total_r_count:=l_total_r_count-1;
1835 END IF; --l_stmt IS NOT NULL
1836 END IF;--rec.dml_type = 0 check
1837 END IF;--is_complete_ref check
1838 END LOOP;
1839
1840
1841 IF l_final IS NULL THEN
1842 M:=true;
1843 ELSE
1844 FOR I IN 1..l_insertDML_pi_list.COUNT
1845 LOOP
1846 l_final:=l_final||'sync.clearOldInserts("'||l_insertDML_pi_list(I)||'","'||l_insertDML_pk_list(l_insertDML_pi_list(I))||'");'||g_nl_chr;
1847 END LOOP;
1848
1849 IF(l_last_pi IS NOT NULL) AND NOT l_comp_ref_list.exists(l_last_pi) THEN
1850 UPDATE_SESSION_DETAILS(l_session_id,l_last_pi,l_rec_count,l_dummy);
1851 l_final := l_final||'sync.logSyncedPIDetails('||to_char(l_session_id)||',"'||l_last_pi||'",'||to_char(l_rec_count)||',"N");'||g_nl_chr;
1852 END IF;
1853
1854 IF l_more_avl THEN
1855 l_final:=l_final||'sync.moreDataFound();'||g_nl_chr;
1856 END IF ;
1857 END IF;
1858
1859
1860 l_final :=g_nl_chr||'function mfs_synch_data(){ sync.session('||to_char(l_session_id)||');'||g_nl_chr || l_final ;
1861
1862 IF(M) THEN l_final :=l_final||' total_sync_rec_count= 0; ';
1863 ELSE
1864 l_final :=l_final||' total_sync_rec_count= tc; ';
1865 DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_user_name AND PRF_NAME like 'REUSABLE%';
1866 END IF;
1867
1868 IF (l_refresh_all) THEN l_final :=l_final||' sync.logSyncSession('||to_char(l_session_id)||',false,"Y"); ' ||g_nl_chr;
1869 ELSE l_final :=l_final||' sync.logSyncSession('||to_char(l_session_id)||',false,"N"); ' ||g_nl_chr;
1870 END IF;
1871
1872 IF M THEN
1873 l_final:=l_final||' display_alert("csm_no_sdata",10);'||g_nl_chr||'}'||g_nl_chr;
1874 ELSE
1875 l_final := 'var tc='||to_char(l_total_r_count)||';var sqlR=0;'||g_nl_chr||l_final||g_nl_chr;
1876 l_final:=l_final||' display_alert("csm_sdata",10); '||g_nl_chr;
1877 l_final:=l_final||'}'||g_nl_chr;
1878 END IF;
1879
1880 ELSE
1881 l_final:=l_final||' display_alert("csm_pass_chg");'||g_nl_chr;
1882 l_final :=g_nl_chr||'function mfs_synch_data(){ '||g_nl_chr || l_final||' }' ||g_nl_chr;
1883 M:=true; --to avoid get Tasks
1884 CREATE_AJAX_SESSION(p_user_name,'csm_auth_error: Invalid password');
1885 IF l_refresh_all THEN --case of plsql exception followed by call to query Id 9 with wrong password
1886 delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_9';
1887 END IF;
1888 END IF;
1889
1890 l_final:=l_final||g_nl_chr||' $("#mfs_sync").click(function(){ '||g_nl_chr
1891 || ' mfs_synch_data();'||g_nl_chr;
1892 l_final:=l_final||'var d=document.getElementById("mfs_sync_script"); var head= document.getElementsByTagName("head")[0]; '||g_nl_chr
1893 || ' if(d) { head.removeChild(d); delete(d);} delete_sync_element(); }); '||g_nl_chr||g_nl_chr
1894 || ' $("#mfs_sync").trigger("click"); '||g_nl_chr;
1895
1896
1897 p_result:=l_final;
1898
1899 IF AUTH THEN
1900
1901 IF(l_refresh_all) THEN
1902 M:=asg_download.purgeSdq(p_user_name);
1903 END IF;
1904 IF(l_warning) THEN
1905 END_SESSION(l_session_id,'SUCCESS_W');
1906 ELSE
1907 END_SESSION(l_session_id,'SUCCESS');
1908 END IF;
1909 END IF;
1910
1911 EXCEPTION
1912 WHEN others THEN
1913 x_return_status := fnd_api.g_ret_sts_error;
1914 x_error_message := 'Exception occurred in q_inc_sync_data_js: '||length(l_final)||'::'|| substr(sqlerrm,0,2000);
1915 CSM_UTIL_PKG.LOG( x_error_message,'CSM_HTML5_PKG.q_inc_sync_data_js',FND_LOG.LEVEL_PROCEDURE);
1916 IF l_refresh_all THEN
1917 delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_9';
1918 END IF;
1919 END_SESSION(l_session_id,'ERROR',x_error_message);
1920 END q_inc_sync_data_js;
1921
1922 FUNCTION get_MD5_hash (p_input IN VARCHAR2)
1923 RETURN VARCHAR2
1924 IS
1925 BEGIN
1926 --RETURN lower(RAWTOHEX(UTL_RAW.cast_to_raw(dbms_obfuscation_toolkit.MD5(input_string=>p_input))));
1927 RETURN 'Deprecated';
1928 END get_MD5_hash;
1929
1930 PROCEDURE enable_pi(p_pi IN VARCHAR2)
1931 IS
1932 BEGIN
1933 UPDATE ASG_PUB_ITEM SET HTML5_OFFLINE='Y' WHERE ITEM_ID=p_pi;
1934 set_comp_ref(p_pi);
1935 DELETE FROM csm_html5_clients_info WHERE PRF_NAME like 'REUSABLE%' OR PRF_NAME like 'SCHEMA%REUSE%';
1936 END enable_pi;
1937
1938 PROCEDURE disable_pi(p_pi IN VARCHAR2)
1939 IS
1940 BEGIN
1941 UPDATE ASG_PUB_ITEM SET HTML5_OFFLINE='N' WHERE ITEM_ID=p_pi;
1942 DELETE FROM csm_html5_clients_info WHERE PRF_NAME like 'REUSABLE%';
1943 remove_comp_ref(p_pi);
1944 END disable_pi;
1945
1946 PROCEDURE PURGE_HTML5_DATA(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
1947 IS
1948 PRAGMA AUTONOMOUS_TRANSACTION; --required for asg_defer to work as it's a pragma-a-t and not finding inq info data
1949 l_sqlerrno VARCHAR2(20);
1950 l_sqlerrmsg VARCHAR2(4000);
1951 l_error_msg VARCHAR2(4000);
1952 l_return_status VARCHAR2(2000);
1953 l_last_run_date DATE;
1954 l_dummy NUMBER;
1955
1956
1957 CURSOR l_upd_last_run_date_csr
1958 IS
1959 SELECT 1
1960 FROM jtm_con_request_data
1961 WHERE product_code = 'CSM'
1962 AND package_name = 'CSM_HTML5_PKG'
1963 AND procedure_name = 'PURGE_HTML5_DATA'
1964 FOR UPDATE OF last_run_date NOWAIT;
1965
1966 CURSOR c_purge_days IS
1967 select profile_option_value from fnd_profile_option_values where profile_option_id in
1968 (select profile_option_id from fnd_profile_options where profile_option_name='CSM_PURGE_INTERVAL')
1969 and level_id=10001;
1970
1971 l_days NUMBER;
1972
1973 BEGIN
1974
1975 l_last_run_date := SYSDATE;
1976
1977 DELETE FROM CSM_HTML5_CLIENTS_INFO a WHERE USER_NAME<>'APPLICATION' AND NOT EXISTS (SELECT 1 FROM ASG_USER au WHERE au.USER_NAME=a.USER_NAME);
1978
1979 OPEN c_purge_days;
1980 FETCH c_purge_days INTO l_days;
1981 IF c_purge_days%FOUND AND l_days IS NOT NULL AND l_days>0 THEN
1982 delete from csm_html5_ajax_info where creation_date < (sysdate-l_days);
1983 delete from csm_html5_sync_info where start_date < (sysdate-l_days);
1984 delete from csm_html5_sync_details a where not exists(select 1 from csm_html5_sync_info b where a.session_id=b.session_id);
1985 delete from csm_query_instances_acc
1986 where creation_date < (sysdate-l_days)
1987 and query_id between 1 and 99
1988 and instance_id not in (select to_number(prf_value) from csm_html5_clients_info WHERE PRF_NAME='SCHEMA_REUSE' OR PRF_NAME like 'REUSABLE%' OR PRF_NAME LIKE 'USER_CREAT%')
1989 and not exists (select 1 from csm_html5_sync_info where instance_id=q_instance_id);
1990 delete from csm_query_results_acc where instance_id not in ( select instance_id from csm_query_instances_acc where query_id between 1 and 99 );
1991 END IF;
1992 CLOSE c_purge_days;
1993
1994
1995 -- update last_run_date
1996 OPEN l_upd_last_run_date_csr;
1997 FETCH l_upd_last_run_date_csr INTO l_dummy;
1998 IF l_upd_last_run_date_csr%FOUND THEN
1999 UPDATE jtm_con_request_data
2000 SET last_run_date = l_last_run_date
2001 WHERE CURRENT OF l_upd_last_run_date_csr;
2002 END IF;
2003 CLOSE l_upd_last_run_date_csr;
2004 COMMIT;
2005
2006 p_status := 'SUCCESS';
2007 p_message := 'CSM_HTML5_PKG.PURGE_HTML5_DATA Executed successfully';
2008
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011 l_sqlerrno := TO_CHAR(SQLCODE);
2012 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
2013 ROLLBACK;
2014 l_error_msg := ' Exception in PURGE_HTML5_DATA:' || l_sqlerrno || ':' || l_sqlerrmsg;
2015 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_HTML5_PKG.PURGE_HTML5_DATA',FND_LOG.LEVEL_EVENT);
2016 p_status := 'ERROR';
2017 p_message := 'Error in CSM_HTML5_PKG.PURGE_HTML5_DATA: ' || l_error_msg;
2018 END PURGE_HTML5_DATA;
2019
2020 FUNCTION isHTML5User(p_user_name IN VARCHAR2) return BOOLEAN
2021 Is
2022 x NUMBER;
2023 BEGIN
2024 SELECT USER_ID INTO x FROM ASG_USER
2025 WHERE USER_NAME=p_user_name
2026 AND ENABLED='Y'
2027 AND MULTI_PLATFORM='Y';
2028
2029 RETURN CSM_UTIL_PKG.is_palm_user(x);
2030 EXCEPTION
2031 WHEN Others THEN
2032 return FALSE;
2033 END isHTML5User;
2034
2035 PROCEDURE process_sequences(p_user_name IN VARCHAR2,p_inp IN CLOB)
2036 IS
2037 l_seq_name VARCHAR2(30);
2038 l_curr_val NUMBER(38);
2039 l_client_num NUMBER;
2040 TYPE l_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
2041 seq l_type; cval l_type; inp clob:=p_inp;
2042 l number; sub varchar2(500);
2043 cnt number:=1;n number;
2044 BEGIN
2045
2046 if(inp is null) then return; end if;
2047
2048 LOOP
2049 l:=instr(inp,',');
2050 exit when l is null or l=0;
2051 sub:=substr(inp,1,l-1);
2052 inp:=substr(inp,l+1);
2053 n:=instr(sub,':'); seq(cnt):=substr(sub,1,n-1); cval(cnt):=substr(sub,n+1);cnt:=cnt+1;
2054 END LOOP;
2055
2056 if(l is null or l=0) then
2057 sub:=inp; n:=instr(sub,':'); seq(cnt):=substr(sub,1,n-1); cval(cnt):=substr(sub,n+1);
2058 end if;
2059
2060 SELECT client_number INTO l_client_num
2061 FROM asg_user
2062 WHERE user_name = p_user_name;
2063
2064 FOR i in 1..cnt
2065 loop
2066 l_seq_name:=seq(i);
2067 l_curr_val:=cval(i);
2068
2069 IF(MOD(l_curr_val, 1000000) = l_client_num) THEN
2070 UPDATE asg_sequence_partitions SET curr_val = l_curr_val
2071 WHERE CLIENTID = p_user_name
2072 AND name = l_seq_name
2073 AND curr_val < l_curr_val;
2074
2075 CSM_UTIL_PKG.log ('Updated sequence for user: ' || p_user_name ||'\n sequence: ' || l_seq_name || '\n Seq value: ' || l_curr_val, 'CSM_HTML5_PKG.process_sequences',FND_LOG.LEVEL_PROCEDURE);
2076 ELSE
2077 CSM_UTIL_PKG.log('Users sequence mismatch! Sequence Name: ' || l_seq_name
2078 ||' \nSequence Value: ' ||l_curr_val || '\n Client_Number: ' || l_client_num, 'CSM_HTML5_PKG.process_sequences',FND_LOG.LEVEL_PROCEDURE);
2079 END IF;
2080 end loop;
2081 END process_sequences;
2082
2083 PROCEDURE process_pubitem_conflicts(p_user_name IN VARCHAR2,
2084 p_upload_tranid IN NUMBER,
2085 p_pubitem IN VARCHAR2)
2086 IS
2087 CURSOR c_conf_rows (p_user_name VARCHAR2,
2088 p_upload_tranid NUMBER,
2089 p_pubitem VARCHAR2) IS
2090 SELECT sequence
2091 FROM asg_conf_info
2092 WHERE user_name = p_user_name AND
2093 transaction_id = p_upload_tranid AND
2094 pub_item = p_pubitem AND
2095 sequence IS NOT NULL;
2096 l_client_wins VARCHAR2(1);
2097 l_server_wins VARCHAR2(1);
2098 l_conf_resolution VARCHAR2(1);
2099 l_conflict_callout VARCHAR2(100);
2100 l_sequence NUMBER;
2101 l_conf_count NUMBER;
2102 l_query_string VARCHAR2(2000);
2103 BEGIN
2104
2105
2106 SELECT conflict_callout INTO l_conflict_callout
2107 FROM asg_pub_item WHERE name = p_pubitem;
2108
2109 l_client_wins := asg_base.G_CLIENT_WINS;
2110 l_server_wins := asg_base.G_SERVER_WINS;
2111
2112 -- Get the access_id of updated DMLs
2113 insert into asg_conf_info (user_name,
2114 pub_item,
2115 transaction_id,
2116 access_id,
2117 sequence,
2118 resolution,
2119 creation_date,
2120 created_by,
2121 last_update_date,
2122 last_updated_by)
2123 SELECT p_user_name, p_pubitem, p_upload_tranid, access_id,b.sequence, l_client_wins,
2124 sysdate, 1, sysdate, 1
2125 FROM asg_system_dirty_queue a, CSM_HTML5_PARSED_DATA b
2126 WHERE client_id = p_user_name AND pub_item = p_pubitem AND
2127 transaction_id is NULL AND a.dml_type = 2
2128 AND b.session_id=p_upload_tranid AND b.piv_name=a.pub_item AND a.access_id=b.pk_value AND b.dml_type='U'
2129 AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME=p_user_name AND PRF_NAME='COMPREF' AND PRF_VALUE=p_pubitem);
2130
2131 l_conf_count := SQL%ROWCOUNT;
2132
2133 CSM_UTIL_PKG.LOG('Number of possible conflicts: ' || l_conf_count
2134 ,'CSM_HTML5_PKG.process_pubitem_conflicts', FND_LOG.LEVEL_PROCEDURE);
2135
2136 IF (l_conf_count = 0) THEN
2137 CSM_UTIL_PKG.LOG('Leaving as No conflicts exist.' ,'CSM_HTML5_PKG.process_pubitem_conflicts', FND_LOG.LEVEL_PROCEDURE);
2138 RETURN;
2139 END IF;
2140
2141 -- Ready to call the pubitem callback
2142 -- If conflict callout is not specified client wins
2143 IF (l_conflict_callout IS NOT NULL) THEN
2144 FOR ccr in c_conf_rows(p_user_name, p_upload_tranid, p_pubitem) LOOP
2145 l_sequence := ccr.sequence;
2146
2147 l_query_string := 'SELECT ' || l_conflict_callout ||'(:1,:2,:3) from dual';
2148
2149 CSM_UTIL_PKG.LOG('SQL Command: ' || replace(l_query_string,'''','''''') ,'CSM_HTML5_PKG.process_pubitem_conflicts', FND_LOG.LEVEL_PROCEDURE);
2150
2151 BEGIN
2152 l_conf_resolution := l_client_wins;
2153 EXECUTE IMMEDIATE l_query_string INTO l_conf_resolution USING p_user_name,p_upload_tranid,l_sequence ;
2154 EXCEPTION
2155 WHEN OTHERS THEN
2156 CSM_UTIL_PKG.LOG('Exception in pub item level callback. ' || sqlerrm ,'CSM_HTML5_PKG.process_pubitem_conflicts', FND_LOG.LEVEL_PROCEDURE);
2157 END;
2158 IF (l_conf_resolution = l_server_wins) THEN
2159 UPDATE asg_conf_info
2160 SET resolution = l_conf_resolution
2161 WHERE user_name = p_user_name AND
2162 transaction_id = p_upload_tranid AND
2163 pub_item = p_pubitem AND
2164 sequence = l_sequence;
2165 END IF;
2166 END LOOP;
2167 END IF;
2168
2169 -- Ok, all the conflict rows are processed. We need to
2170 -- reset download flag for those rows where client wins applies
2171 UPDATE asg_system_dirty_queue
2172 SET download_flag = 'N'
2173 WHERE client_id = p_user_name AND
2174 pub_item = p_pubitem AND
2175 transaction_id is NULL AND
2176 dml_type = 2 AND
2177 access_id in (select access_id
2178 FROM asg_conf_info
2179 WHERE user_name = p_user_name AND
2180 transaction_id = p_upload_tranid AND
2181 pub_item = p_pubitem AND
2182 sequence IS NOT NULL AND
2183 resolution = l_client_wins);
2184 END process_pubitem_conflicts;
2185
2186 PROCEDURE process_conflicts(p_user_name IN VARCHAR2,p_upload_tran_id IN NUMBER)
2187 IS
2188 BEGIN
2189
2190 CSM_UTIL_PKG.LOG('Entered Process Conflicts for tranid: '||p_upload_tran_id, 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);
2191
2192 FOR rec IN (SELECT api.item_id
2193 FROM csm_html5_inq ci, asg_pub_item api
2194 WHERE ci.user_name = p_user_name
2195 AND ci.sync_id = p_upload_tran_id
2196 AND ci.pi_name = api.name
2197 AND api.detect_conflict = 'Y')
2198 LOOP
2199 CSM_UTIL_PKG.LOG('Processing ' ||rec.item_id || ' for conflicts.', 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);
2200
2201 process_pubitem_conflicts(p_user_name, p_upload_tran_id, rec.item_id);
2202
2203 CSM_UTIL_PKG.LOG('Done Processing ' ||rec.item_id || ' for conflicts.', 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);
2204 END LOOP;
2205
2206 CSM_UTIL_PKG.LOG('Leaving Process Conflicts ', 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 CSM_UTIL_PKG.LOG('Exception when processing for conflicts. '|| substr(sqlerrm,1,2000), 'CSM_HTML5_PKG.process_conflicts', FND_LOG.LEVEL_PROCEDURE);
2210
2211 END process_conflicts;
2212
2213 --queryId=16
2214 PROCEDURE query_parse_upload(p_user_name IN VARCHAR2,p_session_id IN NUMBER,
2215 p_result OUT nocopy CLOB,
2216 x_return_status OUT nocopy VARCHAR2,
2217 x_error_message OUT nocopy VARCHAR2)
2218 IS
2219 cnt number:=0;
2220 trnid VARCHAR2(4000):='';
2221 BEGIN
2222 For rec IN (select a.session_id from csm_html5_upload_data a, csm_html5_sync_info c
2223 where a.status<>'PARSED' and a.session_id=c.session_id
2224 and c.useR_name=p_user_name order by session_id asc)
2225 loop
2226 CSM_HTML5_PKG.HTML5_PARSE_XML(rec.SESSION_ID,x_return_status,x_error_message);
2227 IF x_return_status=FND_API.G_RET_STS_ERROR THEN
2228 cnt:=cnt+1;
2229 trnid:=rec.session_id;
2230 CSM_UTIL_PKG.LOG( 'Exception while parsing upload data in tran_id:'||rec.session_id||' for user:'||p_user_name||' with error:'||substr(x_error_message,1,1000),'CSM_HTML5_PKG.query_parse_upload', FND_LOG.LEVEL_PROCEDURE);
2231 UPDATE CSM_HTML5_UPLOAD_DATA
2232 SET LAST_UPDATE_DATE = SYSDATE,
2233 LAST_UPDATED_BY = 1,
2234 STATUS = 'PARSE_ERROR',
2235 COMMENTS = x_error_message
2236 WHERE SESSION_ID = rec.SESSION_ID;
2237 EXIT;
2238 END IF;
2239 end loop;
2240
2241 CSM_HTML5_PKG.process_upload(p_user_name);
2242
2243 IF cnt>0 THEN
2244 p_result := 'sync.upload_complete(true,"w:'||trnid||'");'; --synchronous uploaded, warnings txn
2245 END_SESSION(p_session_id,'SUCCESS_W');
2246 ELSE
2247 p_result := 'sync.upload_complete(true,"s");';
2248 END_SESSION(p_session_id,'SUCCESS');
2249 END IF ;
2250 x_return_status := fnd_api.g_ret_sts_success;
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 x_return_status := fnd_api.g_ret_sts_error;
2254 x_error_message := 'Exception occurred in query_parse_upload: ' || substr(sqlerrm,1,3900);
2255 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_HTML5_PKG.query_parse_upload', FND_LOG.LEVEL_PROCEDURE);
2256 p_result := 'handle_sync_exception("'||translateString(x_error_message)||'");';
2257 END_SESSION(p_session_id,'ERROR',x_error_message);
2258 END query_parse_upload;
2259
2260
2261 FUNCTION uploadData(p_user_name IN VARCHAR2,
2262 p_password IN VARCHAR2,
2263 p_sync_data IN CLOB,
2264 p_seq_data IN CLOB,
2265 p_result OUT nocopy VARCHAR2) RETURN NUMBER
2266 IS
2267
2268 l_session_id NUMBER;
2269 l_final VARCHAR2(4000);
2270 is_synchronous boolean :=false;
2271
2272 l_app_id NUMBER:=883;
2273 l_resp_id NUMBER:=23675;
2274 l_user_id NUMBER;
2275 instId number:=-1; ret VARCHAR2(1); m varchar2(4000);
2276 l_id CSM_INTEGER_LIST :=CSM_INTEGER_LIST();
2277 l_ch CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
2278 l_dt CSM_DATE_LIST:=CSM_DATE_LIST();
2279 req_id number:=0;
2280 l_ret VARCHAR2(100);
2281 l_err varchar2(4000);
2282 l_dummy boolean :=true;
2283 BEGIN
2284
2285 l_final := empty_clob();
2286
2287 IF NOT isHTML5User(p_user_name) THEN
2288 CSM_UTIL_PKG.LOG('Quitting...since '||p_user_name||' is not an HTML5 user', 'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2289 CREATE_AJAX_SESSION(p_user_name,'csm_mfs_error: Not authorized HTML5 user');
2290 l_final := 'display_alert("csm_mfs_error")';
2291 p_result :=l_final;
2292 RETURN -1;
2293 END IF;
2294
2295 IF NOT authenticate(p_user_name,p_password) THEN
2296 CSM_UTIL_PKG.LOG('Auth failed for USER: ' || p_user_name,'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2297 CREATE_AJAX_SESSION(p_user_name,'csm_auth_error: Invalid password');
2298 l_final := 'display_alert("csm_pass_chg");';
2299 p_result :=l_final;
2300 RETURN -1;
2301 END IF;
2302
2303 CSM_UTIL_PKG.LOG('Dumping Uploaded data for USER: ' || p_user_name,'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2304
2305
2306 SELECT USER_ID,RESPONSIBILITY_ID,APP_ID INTO l_user_id,l_resp_id,l_app_id FROM ASG_USER WHERE USER_NAME=p_user_name;
2307
2308 IF fnd_profile.value_specific('ASG_SYNCHRONOUS_UPLOAD',l_user_id,l_resp_id,l_app_id)='Y' THEN
2309 is_synchronous:=true;
2310 END IF;
2311
2312 l_session_id:=CREATE_SESSION(p_user_name,'UPLOAD');
2313
2314 IF p_seq_data IS NOT NULL THEN
2315 process_sequences(p_user_name,p_seq_data);
2316 END IF;
2317
2318 INSERT INTO CSM_HTML5_UPLOAD_DATA(SESSION_ID,STATUS,COMMENTS,UPLOADED_PAYLOAD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
2319 VALUES(l_session_id,'RECEIVED','Sync successfully dumped the data',p_sync_data,l_user_id,SYSDATE,l_user_id,SYSDATE);
2320
2321 --Bug 16456574
2322 CSM_HTML5_PKG.HTML5_PARSE_XML(l_session_id,l_ret,l_err);
2323 IF l_ret=FND_API.G_RET_STS_ERROR THEN
2324 CSM_UTIL_PKG.LOG( 'Exception while parsing upload data in tran_id:'||l_session_id||' for user:'||p_user_name
2325 ||' with error:'||substr(l_err,1,1000),'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2326 UPDATE CSM_HTML5_UPLOAD_DATA
2327 SET LAST_UPDATE_DATE = SYSDATE,
2328 LAST_UPDATED_BY = 1,
2329 STATUS = 'PARSE_ERROR',
2330 COMMENTS = l_err
2331 WHERE SESSION_ID = l_session_id;
2332 l_err:= 'w:'||l_session_id;
2333 ELSE
2334 l_dummy:=true;
2335 FOR rec IN (SELECT PIV_NAME,COUNT(1) cnt FROM CSM_HTML5_PARSED_DATA
2336 WHERE SESSION_ID=l_session_id GROUP BY PIV_NAME ORDER BY PIV_NAME)
2337 LOOP
2338 UPDATE_SESSION_DETAILS(l_session_id,rec.PIV_NAME,rec.cnt,l_dummy);
2339 END LOOP;
2340
2341 SELECT COUNT(*) INTO req_id FROM CSM_HTML5_PARSED_DATA
2342 WHERE SESSION_ID=l_session_id AND STATUS='PARSE_ERROR';
2343
2344 IF req_id > 0 THEN
2345 l_err:= 'w:'||l_session_id; req_id:=0;
2346 ELSE
2347 l_err:= 's';
2348 END IF;
2349 END IF;
2350
2351 process_conflicts(p_user_name,l_session_id);
2352
2353 IF NOT is_synchronous THEN
2354 p_result := 'sync.logSyncSession('||to_char(l_session_id)||',true);sync.upload_complete(false,"'||l_err||'");';
2355 END_SESSION(l_session_id,'SUCCESS');
2356 RETURN -1;
2357 END IF;
2358
2359 CSM_UTIL_PKG.LOG('Synchronous Upload set for USER: ' || p_user_name,'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2360
2361 l_id.extend(2);l_ch.extend(2);l_dt.extend(2);
2362 l_id(1):=1; l_ch(1):=p_user_name;
2363
2364 l_id(2):=2; l_ch(2):=l_session_id;
2365
2366 CSM_QUERY_PKG.INSERT_INSTANCE(asg_base.get_user_id(p_user_name), 16, NULL, 'SYNCHRONOUS_UPLOAD', l_id, l_ch, l_dt,NULL, instId, ret, m);
2367
2368 IF (ret=fnd_api.g_ret_sts_success) THEN
2369 CSM_UTIL_PKG.LOG('Created Instance -:'||instId, 'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2370
2371 UPDATE csm_html5_sync_info SET Q_INSTANCE_ID=instId WHERE SESSION_ID=l_session_id;
2372
2373 req_id:=SUBMIT_CON_QUERY(asg_base.get_user_id(p_user_name), 16, instId);
2374 IF req_id=0 THEN
2375 CSM_UTIL_PKG.LOG('Sorry, this user -'||p_user_name||' does not have the responsibility/request group to submit request', 'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2376 p_result:= 'sync.logSyncSession('||to_char(l_session_id)||',true);sync.upload_complete(true,"e:no_resp_to_submit_req");';
2377 END_SESSION(l_session_id,'SUCCESS_W');
2378 RETURN -1;
2379 END IF;
2380 ELSE
2381 CSM_UTIL_PKG.LOG('Create Instance failed - Status:'||instId||':'||ret||':'||m, 'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2382 p_result:= 'sync.logSyncSession('||to_char(l_session_id)||',true);sync.upload_complete(true,"e:instance_create_failed");';
2383 END_SESSION(l_session_id,'SUCCESS_W');
2384 RETURN -1;
2385 END IF;
2386
2387 /*p_result:= 'sync.logSyncSession('||to_char(l_session_id)||',true);setTimeout("connectTo(''queryId=13 amp INSTANCE_ID='||instId||' amp USER_NAME='||p_user_name||');secureSessionId='||instId||';",5000);'*/
2388 p_result:=to_char(req_id)||':'||to_char(l_session_id); --ampersand is causing issue with plsql, so moved this string to java
2389 RETURN instId;
2390
2391 EXCEPTION
2392 WHEN others THEN
2393 ROLLBACK;
2394 l_final := 'Exception occurred in uploadData: ' || substr(sqlerrm,1,3900);
2395 CSM_UTIL_PKG.LOG(l_final, 'CSM_HTML5_PKG.uploadData', FND_LOG.LEVEL_PROCEDURE);
2396 END_SESSION(l_session_id,'ERROR',l_final);
2397 p_result := 'handle_sync_exception("'||translateString(l_final)||'");';
2398 RETURN -1;
2399 END uploadData;
2400
2401 PROCEDURE CONC_EXECUTE_QUERY(errbuf OUT NOCOPY VARCHAR2,
2402 RETCODE OUT NOCOPY VARCHAR2,
2403 p_USER_ID IN NUMBER,
2404 p_QUERY_ID IN NUMBER,
2405 p_INSTANCE_ID IN NUMBER
2406 )
2407 IS
2408 BEGIN
2409 CSM_QUERY_PKG.EXECUTE_QUERY(p_USER_ID, p_QUERY_ID, p_INSTANCE_ID, RETCODE, errbuf);
2410 END CONC_EXECUTE_QUERY;
2411
2412 FUNCTION SUBMIT_CON_QUERY(p_USER_ID IN NUMBER,
2413 p_QUERY_ID IN NUMBER,
2414 p_INSTANCE_ID IN NUMBER) RETURN NUMBER
2415 IS
2416 CON_REQUEST_ID NUMBER;
2417 l_app_id NUMBER:=883;
2418 l_resp_id NUMBER:=23675;
2419 l_app_name VARCHAR2(10):='CSM';
2420 BEGIN
2421 begin
2422 select responsibility_id ,application_id into l_resp_id ,l_app_id
2423 from fnd_responsibility a, fnd_oracle_userid b
2424 where responsibility_key='OMFS_PALM' and a.application_id=b.oracle_id
2425 and b.oracle_username=l_app_name ;
2426 exception
2427 when others then
2428 null;
2429 end;
2430
2431 FND_GLOBAL.APPS_INITIALIZE (p_USER_ID,l_resp_id,l_app_id);
2432
2433 CON_REQUEST_ID :=FND_REQUEST.SUBMIT_REQUEST(application => l_app_name
2434 ,program => 'CSM_MULTIPLAT_CONC'
2435 ,start_time => SYSDATE
2436 ,sub_request => FALSE
2437 ,argument1 => to_char(p_USER_ID)
2438 ,argument2 => to_char(p_QUERY_ID)
2439 ,argument3 => to_char(p_INSTANCE_ID));
2440 COMMIT;
2441
2442 RETURN CON_REQUEST_ID;
2443 END SUBMIT_CON_QUERY;
2444
2445 PROCEDURE set_app_revision(p_version IN VARCHAR2,p_force_update IN VARCHAR2 :='N')
2446 IS
2447 l_cur_version VARCHAR2(100):=get_app_revision;
2448 BEGIN
2449
2450 IF l_cur_version IS NULL THEN
2451 INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE)
2452 VALUES('APPLICATION','REVISION',p_version);
2453 ELSIF to_number(p_version)> to_number(l_cur_version) THEN
2454 UPDATE csm_html5_clients_info SET PRF_VALUE=p_version
2455 WHERE USER_NAME='APPLICATION' AND PRF_NAME='REVISION';
2456
2457 UPDATE csm_html5_clients_info SET PRF_VALUE=p_force_update
2458 WHERE USER_NAME='APPLICATION' AND PRF_NAME='FORCE_UPDATE';
2459 IF SQL%ROWCOUNT=0 THEN
2460 INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE)
2461 VALUES('APPLICATION','FORCE_UPDATE',p_force_update);
2462 END IF;
2463 END IF;
2464
2465 END set_app_revision;
2466
2467 FUNCTION get_app_revision(p_u IN VARCHAR2:=NULL,p_v IN VARCHAR2:=NULL) RETURN VARCHAR2
2468 IS
2469 x varchar2(1000);
2470 BEGIN
2471 SELECT MAX(PRF_VALUE) into x FROM csm_html5_clients_info
2472 WHERE USER_NAME='APPLICATION' AND PRF_NAME='REVISION';
2473
2474 IF p_u IS NOT NULL AND p_v IS NOT NULL THEN
2475 UPDATE csm_html5_clients_info SET PRF_VALUE=p_v
2476 WHERE USER_NAME=P_U AND PRF_NAME='LAST_SYNC_APP_VERSION';
2477 IF SQL%ROWCOUNT=0 THEN
2478 INSERT INTO csm_html5_clients_info(USER_NAME,PRF_NAME,PRF_VALUE)
2479 VALUES(P_U,'LAST_SYNC_APP_VERSION',p_v);
2480 END IF;
2481 END IF;
2482
2483 RETURN x;
2484 END get_app_revision;
2485
2486 FUNCTION has_upgraded_version(p_user_name IN VARCHAR2,p_version IN VARCHAR2 :=NULL) RETURN BOOLEAN
2487 IS
2488 x VARCHAR2(1000);
2489 BEGIN
2490 IF (p_version IS NULL) THEN
2491 SELECT PRF_VALUE into x FROM csm_html5_clients_info
2492 WHERE USER_NAME=p_user_name AND PRF_NAME='APP_REVISION';
2493
2494 DELETE FROM csm_html5_clients_info
2495 WHERE USER_NAME=p_user_name AND PRF_NAME='APP_REVISION';
2496
2497 RETURN TRUE;
2498 ELSE
2499 SELECT PRF_VALUE into x FROM csm_html5_clients_info
2500 WHERE USER_NAME='APPLICATION' AND PRF_NAME='REVISION';
2501
2502 if (trim(X) <> trim(p_version)) THEN return TRUE; END IF;
2503 END IF;
2504 RETURN FALSE;
2505 EXCEPTION
2506 WHEN Others THEN
2507 return FALSE;
2508 END has_upgraded_version;
2509
2510 FUNCTION GET_SECURE_SESSION(p_uname IN VARCHAR2) RETURN VARCHAR2
2511 IS
2512 l_e NUMBER;l_d NUMBER;l_n NUMBER;
2513 l_s VARCHAR2(100);
2514 l_seq NUMBER;
2515 BEGIN
2516
2517 l_seq := round(dbms_random.value*100000);
2518 set_user_profile(p_uname,'RSA_KEY',get_rsa_keys(l_e,l_d,l_n));
2519
2520 l_s :=to_char(l_e)||g_pattern||to_char(l_seq)||g_pattern||to_char(l_n)||g_pattern;
2521 RETURN 'sessionCreated("'||encode(l_s)||'");';
2522
2523 END GET_SECURE_SESSION;
2524
2525 /* An OPTIMIZATION FRAMEWORK for queryid 6 and 9 */
2526 FUNCTION GET_REUSABLE(p_USER_NAME IN VARCHAR2,p_password IN VARCHAR2,p_QUERY_ID IN NUMBER) RETURN VARCHAR2
2527 IS
2528 x VARCHAR2(1000);
2529 BEGIN
2530 IF NOT authenticate(p_user_name,p_password) THEN
2531 RETURN '-1';
2532 END IF ;
2533
2534 BEGIN
2535 SELECT '1' INTO x FROM ASG_SYSTEM_DIRTY_QUEUE sdq WHERE CLIENT_ID=p_USER_NAME AND ROWNUM<2
2536 AND EXISTS(SELECT 1 FROM ASG_PUB_ITEM pi WHERE pi.item_id=sdq.pub_item and nvl(html5_offline,'N')='Y');
2537
2538 DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_USER_NAME AND PRF_NAME like 'REUSABLE%';
2539 RETURN '-1';
2540 EXCEPTION
2541 WHEN OTHERS THEN
2542 NULL;
2543 END;
2544
2545 BEGIN
2546 SELECT PRF_VALUE into x FROM csm_html5_clients_info
2547 WHERE USER_NAME=p_USER_NAME AND PRF_NAME='USER_CREATION_DATA';
2548 DELETE FROM csm_html5_clients_info WHERE USER_NAME=p_USER_NAME AND PRF_NAME='USER_CREATION_DATA';
2549 RETURN (-1*x);
2550 EXCEPTION
2551 WHEN OTHERS THEN
2552 NULL;
2553 END;
2554
2555 BEGIN
2556 SELECT PRF_VALUE into x FROM csm_html5_clients_info
2557 WHERE USER_NAME=p_USER_NAME AND PRF_NAME='REUSABLE_'||p_QUERY_ID;
2558 EXCEPTION
2559 WHEN OTHERS THEN
2560 select '-2' into x from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_'||p_QUERY_ID;
2561 END ;
2562
2563 RETURN x;
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567 RETURN '-1';
2568 END GET_REUSABLE;
2569
2570 PROCEDURE SET_REUSABLE(p_user_name IN VARCHAR2,p_QUERY_ID IN NUMBER,p_INSTANCE_ID IN NUMBER)
2571 IS
2572 x NUMBER;
2573 BEGIN
2574
2575 BEGIN
2576 select 1 into x from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_'||p_QUERY_ID;
2577 delete from csm_html5_clients_info where user_name=p_user_name and prf_name='REUSE_AUTH_'||p_QUERY_ID;
2578 set_user_profile(p_USER_NAME,'REUSABLE_'||p_QUERY_ID,to_char(p_INSTANCE_ID));
2579 EXCEPTION
2580 WHEN OTHERS THEN
2581 CSM_UTIL_PKG.LOG('Unable to set reusable since the authentication failed', 'CSM_HTML5_PKG.SET_REUSABLE', FND_LOG.LEVEL_PROCEDURE);
2582 RETURN;
2583 END;
2584 END SET_REUSABLE;
2585
2586 procedure generate_html5_data(p_user_name IN VARCHAR2)
2587 is
2588 instId number:=-1; ret VARCHAR2(1); m varchar2(4000);
2589 l_id CSM_INTEGER_LIST :=CSM_INTEGER_LIST();
2590 l_ch CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
2591 l_dt CSM_DATE_LIST:=CSM_DATE_LIST();
2592 req_id number:=0;
2593 begin
2594 l_id.extend(1);l_ch.extend(1);l_dt.extend(1);
2595 l_id(1):=1;
2596 l_ch(1):=p_user_name;
2597 CSM_QUERY_PKG.INSERT_INSTANCE(asg_base.get_user_id(p_user_name), 12, NULL, 'GENERATE_USER_DATA', l_id, l_ch, l_dt,NULL, instId, ret, m);
2598
2599 IF (ret=fnd_api.g_ret_sts_success) THEN
2600 CSM_UTIL_PKG.LOG('Created Instance -:'||instId, 'CSM_HTML5_PKG.generate_html5_data', FND_LOG.LEVEL_PROCEDURE);
2601 req_id:=SUBMIT_CON_QUERY(asg_base.get_user_id(p_user_name), 12, instId);
2602 IF req_id=0 THEN
2603 CSM_UTIL_PKG.LOG('Sorry, this user -'||p_user_name||' does not have the responsibility/request group to submit request', 'CSM_HTML5_PKG.generate_html5_data', FND_LOG.LEVEL_PROCEDURE);
2604 ELSE
2605 set_user_profile(p_user_name,'USER_CREATION_DATA',to_char(instId));
2606 END IF;
2607 ELSE
2608 CSM_UTIL_PKG.LOG('Create Instance failed - Status:'||instId||':'||ret||':'||m, 'CSM_HTML5_PKG.generate_html5_data', FND_LOG.LEVEL_PROCEDURE);
2609 END IF;
2610 exception when others then
2611 CSM_UTIL_PKG.LOG('Exception occurred in generate_html5_data: ' || substr(sqlerrm,1,3900), 'CSM_HTML5_PKG.generate_html5_data', FND_LOG.LEVEL_PROCEDURE);
2612 end generate_html5_data;
2613
2614 procedure query_generate_html5_data( p_user_name IN VARCHAR2,p_result OUT nocopy CLOB, x_return_status OUT nocopy VARCHAR2,
2615 x_error_message OUT nocopy VARCHAR2)
2616 is
2617 begin
2618 query_sync_data_js(p_user_name, null,p_result,x_return_status,x_error_message,true);
2619 end query_generate_html5_data;
2620
2621 PROCEDURE GET_INQ_INS_ST(P_TABLE_NAME IN VARCHAR2,P_COL_NAME_LIST IN CSM_VARCHAR_LIST,P_COL_VALUE_LIST IN CSM_VARCHAR_LIST,P_COL_IDX_LIST numtype,p_result OUT NOCOPY CLOB)
2622 IS
2623 L_STMT CLOB :=empty_clob();
2624 l_part CLOB :=empty_clob();
2625 l_cnt NUMBER:=1;
2626 BEGIN
2627
2628 L_STMT := 'INSERT INTO '||P_TABLE_NAME||'(';
2629
2630 l_part:='';
2631 FOR j IN 1..P_COL_NAME_LIST.COUNT
2632 LOOP
2633 IF(P_COL_NAME_LIST(j)<>'ACCESS_ID' AND P_COL_NAME_LIST(j) IS NOT NULL AND (P_COL_VALUE_LIST(j) IS NOT NULL OR P_COL_IDX_LIST.EXISTS(j))) THEN
2634 l_part := l_part||','||P_COL_NAME_LIST(j);
2635 END IF;
2636 END LOOP;
2637
2638 l_part:=l_part||',CLID$$CS,TRANID$$,SEQNO$$,DMLTYPE$$,VERSION$$';
2639 l_stmt:=l_stmt|| SUBSTR(l_part,2)||') VALUES(';
2640
2641 l_part:='';
2642 FOR j IN 1..P_COL_VALUE_LIST.COUNT
2643 LOOP
2644 IF(P_COL_NAME_LIST(j)<>'ACCESS_ID' AND P_COL_NAME_LIST(j) IS NOT NULL) THEN
2645 IF (P_COL_VALUE_LIST(j) IS NOT NULL) THEN
2646 l_part := l_part||','''||replace(P_COL_VALUE_LIST(j),'''','''''')||'''';
2647 ELSIF P_COL_IDX_LIST.EXISTS(j) THEN
2648 l_part := l_part||',:'||to_char(l_cnt);
2649 l_cnt:=l_cnt+1;
2650 END IF;
2651 END IF;
2652 END LOOP;
2653
2654
2655 for i in 1..5
2656 loop
2657 l_part:=l_part||',:'||to_char(l_cnt);
2658 l_cnt:=l_cnt+1;
2659 end loop;
2660
2661 l_stmt:=l_stmt|| SUBSTR(l_part,2)||')';
2662
2663 p_result:=l_stmt;
2664
2665 --CSM_UTIL_PKG.LOG( l_stmt ,'CSM_HTML5_PKG.GET_INQ_INS_ST',FND_LOG.LEVEL_PROCEDURE);
2666 END GET_INQ_INS_ST;
2667
2668 PROCEDURE HTML5_PARSE_XML(p_SYNC_ID IN NUMBER,X_RETURN_STATUS OUT NOCOPY VARCHAR2,x_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
2669 IS
2670
2671 CURSOR c_get_sync_data(sync_id NUMBER)
2672 IS
2673 SELECT UPLOADED_PAYLOAD
2674 FROM CSM_HTML5_UPLOAD_DATA
2675 WHERE SESSION_ID = sync_id
2676 AND STATUS<>'PARSED';
2677
2678 CURSOR c_get_sync_uname(sync_id NUMBER)
2679 IS
2680 SELECT au.USER_NAME, USER_ID, RESPONSIBILITY_ID, APP_ID
2681 FROM CSM_HTML5_SYNC_INFO si, ASG_USER au
2682 WHERE SESSION_ID =SYNC_ID
2683 AND au.USER_NAME =si.USER_NAME;
2684
2685
2686 l_xml_clob_payload CLOB;
2687 l_result CLOB;
2688 part_xml CLOB;
2689 l_xml_doc xmldom.DOMDocument;
2690 l_xml_parser xmlparser.Parser;
2691 l_xml_node_list xmldom.DOMNodeList;
2692 l_xml_DATA_node_list xmldom.DOMNodeList;
2693 l_xml_node xmldom.DOMNode;
2694 l_xml_node_len NUMBER;
2695 dummy number;
2696 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
2697 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
2698 l_COL_NAME_IDX numtype;
2699 l_COL_CLOB_LIST clobtype;
2700
2701
2702 L_ACCESS_ID NUMBER;
2703 L_NODE_NAME VARCHAR2(300);
2704 L_NODE_VALUE VARCHAR2(300);
2705 L_PIV_NAME VARCHAR2(300);
2706 L_DML_TYPE VARCHAR2(3);
2707 l_sec_node dbms_xmldom.domnode;
2708 L_SEC_LST DBMS_XMLDOM.DOMNODELIST;
2709 l_first_node dbms_xmldom.domnode;
2710 L_FIRST_LST DBMS_XMLDOM.DOMNODELIST;
2711 L_FIRST_LEN NUMBER;
2712 L_INQ_TABLE_NAME VARCHAR2(256);
2713 L_SYNC_USER_NAME VARCHAR2(500);
2714 L_RESP_ID NUMBER;
2715 L_APP_ID NUMBER;
2716 L_USER_ID NUMBER;
2717 L_PAYLOAD_ID NUMBER;
2718 l_seq NUMBER:=1;
2719
2720 CURSOR c_date_f
2721 IS
2722 SELECT upper(VALUE)
2723 FROM NLS_SESSION_PARAMETERS
2724 WHERE PARAMETER='NLS_DATE_FORMAT';
2725 l_df VARCHAR2(100);
2726 l_htm5_df VARCHAR2(100):='RRRR-MM-DD HH24:MI:SS';
2727 l_changed_df boolean:=false;
2728 BEGIN
2729 CSM_UTIL_PKG.LOG( 'Entering PARSE_XML for SYNC ID : ' || p_SYNC_ID ,'CSM_HTML5_PKG.HTML5_PARSE_XML',FND_LOG.LEVEL_PROCEDURE);
2730
2731 OPEN c_date_f;
2732 FETCH c_date_f INTO l_df;
2733 CLOSE c_date_f;
2734
2735 --get synch user name
2736 OPEN C_GET_SYNC_UNAME(P_SYNC_ID);
2737 FETCH C_GET_SYNC_UNAME INTO L_SYNC_USER_NAME, L_USER_ID, L_RESP_ID, L_APP_ID;
2738 CLOSE C_GET_SYNC_UNAME;
2739
2740 --Parse Payload
2741 OPEN c_get_sync_data(p_SYNC_ID);
2742 FETCH c_get_sync_data INTO l_xml_clob_payload;
2743 CLOSE c_get_sync_data;
2744
2745 IF l_xml_clob_payload IS NULL THEN
2746 CSM_UTIL_PKG.LOG ( 'No data found in SYNC ID : ' || p_SYNC_ID, 'CSM_HTML5_PKG.HTML5_PARSE_XML',FND_LOG.LEVEL_EXCEPTION);
2747 x_return_status := FND_API.G_RET_STS_ERROR;
2748 x_error_message := 'Either Synch has uploaded no Data Or Is already parsed' ;
2749 RETURN;
2750 END IF;
2751
2752 --PARSE the XML
2753 l_xml_parser := xmlparser.newParser;
2754 xmlparser.parseClob(l_xml_parser, l_xml_clob_payload);
2755 L_XML_DOC := XMLPARSER.GETDOCUMENT(L_XML_PARSER);
2756 xmlparser.freeParser(l_xml_parser);
2757
2758 l_first_lst := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(L_XML_DOC),'/SYNC_DATA/PIV');
2759 l_first_len := dbms_xmldom.getLength(l_first_lst);
2760
2761 l_sec_lst := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(L_XML_DOC),'/SYNC_DATA/PIV/DATA');
2762 l_sec_node := xmldom.makenode(xmldom.makeelement(xmldom.item(l_sec_lst, 0)));
2763
2764 IF l_df <> l_htm5_df THEN
2765 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='''||l_htm5_df||'''';
2766 l_changed_df:=true;
2767 END If;
2768
2769 -- loop through elements
2770 FOR I IN 0..l_first_len-1 LOOP
2771 l_first_node := xmldom.item(l_first_lst, i);
2772 l_sec_node := xmldom.makenode(xmldom.makeelement(xmldom.item(l_sec_lst, i)));
2773 L_NODE_NAME := XMLDOM.GETNODENAME(L_FIRST_NODE);
2774
2775 IF L_NODE_NAME = 'PIV' THEN
2776
2777 dbms_xslprocessor.valueOf(l_first_node,'PIV_NAME/text()',L_PIV_NAME);
2778 dbms_xslprocessor.valueOf(l_first_node,'ACCESS_ID/text()',L_ACCESS_ID);
2779 dbms_xslprocessor.valueOf(l_first_node,'DML_TYPE/text()',L_DML_TYPE);
2780
2781 END IF;
2782
2783 part_xml := ' ';
2784 dbms_xmldom.writetoclob(l_sec_node, part_xml);
2785
2786 part_xml := REPLACE (part_xml,'<DATA>','<?xml version="' || '1.0' || '"?>' || '<ROWSET><ROW>');
2787 part_xml := REPLACE (part_xml,'</DATA>','</ROW></ROWSET>');
2788
2789 OPEN C_GET_INQ_TABLE(L_PIV_NAME);
2790 FETCH C_GET_INQ_TABLE INTO L_INQ_TABLE_NAME;
2791 CLOSE C_GET_INQ_TABLE;
2792
2793
2794 BEGIN
2795
2796 BEGIN
2797 l_COL_NAME_IDX.DELETE;l_COL_CLOB_LIST.DELETE;
2798 PARSE_XML(part_xml,l_COL_NAME_LIST,L_COL_VALUE_LIST,l_COL_NAME_IDX,l_COL_CLOB_LIST,true);
2799 EXCEPTION
2800 WHEN OTHERS THEN
2801 CSM_UTIL_PKG.LOG( 'Parse xml failed for ' || p_SYNC_ID ||': Payload saved in :'||L_PAYLOAD_ID ,'CSM_HTML5_PKG.HTML5_PARSE_XML',FND_LOG.LEVEL_PROCEDURE);
2802 RAISE_APPLICATION_ERROR(-20994, 'PARSE_XML Failed:'||SUBSTR(SQLERRM,1,3000));
2803 END;
2804
2805 IF L_INQ_TABLE_NAME is null THEN
2806 CSM_UTIL_PKG.LOG( 'INQ TABLE NAME is null for ' || p_SYNC_ID ||': Payload saved in :'||L_PAYLOAD_ID ,'CSM_HTML5_PKG.HTML5_PARSE_XML',FND_LOG.LEVEL_PROCEDURE);
2807 RAISE_APPLICATION_ERROR(-20994, 'INQ TABLE NAME is null for ' || p_SYNC_ID||': Payload saved in :'||L_PAYLOAD_ID);
2808 END IF;
2809
2810 GET_INQ_INS_ST(L_INQ_TABLE_NAME,l_COL_NAME_LIST,L_COL_VALUE_LIST,l_COL_NAME_IDX,l_result);
2811 IF(l_COL_NAME_IDX.COUNT=0) THEN
2812 EXECUTE IMMEDIATE l_result USING L_SYNC_USER_NAME,P_SYNC_ID,l_seq,L_DML_TYPE,1;
2813 ELSE
2814 IF (l_COL_NAME_IDX.COUNT=1) THEN
2815 EXECUTE IMMEDIATE l_result USING l_COL_CLOB_LIST(l_COL_NAME_IDX.FIRST),L_SYNC_USER_NAME,P_SYNC_ID,l_seq,L_DML_TYPE,1;
2816 ELSIF (l_COL_NAME_IDX.COUNT=2) THEN
2817 EXECUTE IMMEDIATE l_result USING l_COL_CLOB_LIST(l_COL_NAME_IDX.FIRST),l_COL_CLOB_LIST(l_COL_NAME_IDX.LAST),L_SYNC_USER_NAME,P_SYNC_ID,l_seq,L_DML_TYPE,1;
2818 ELSE
2819 RAISE_APPLICATION_ERROR(-20994, 'ONLY TWO CLOB COLUMNS PER TABLE SUPPORTED IN UPLOAD FOR NOW');
2820 END IF;
2821 END IF;
2822
2823 BEGIN
2824 SELECT 1 INTO dummy FROM csm_html5_inq
2825 WHERE USER_NAME=L_SYNC_USER_NAME AND pi_name=L_PIV_NAME AND SYNC_ID=p_SYNC_ID;
2826 EXCEPTION
2827 WHEN OTHERS THEN
2828 INSERT INTO csm_html5_inq (USER_NAME,PI_NAME,SYNC_ID) VALUES(L_SYNC_USER_NAME,L_PIV_NAME,p_SYNC_ID);
2829 END;
2830
2831 INSERT INTO CSM_HTML5_PARSED_DATA(SESSION_ID,SEQUENCE, PIV_NAME, PK_VALUE,
2832 DML_TYPE, STATUS, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
2833 VALUES(P_SYNC_ID,l_seq,L_PIV_NAME,L_ACCESS_ID,L_DML_TYPE,'MOVED_TO_INQ',SYSDATE,L_USER_ID,SYSDATE,L_USER_ID);
2834
2835 EXCEPTION
2836 WHEN OTHERS THEN
2837
2838 X_ERROR_MESSAGE:=SUBSTR(SQLERRM,1,3000);
2839
2840 INSERT INTO CSM_HTML5_PARSED_DATA(SESSION_ID,SEQUENCE, PIV_NAME, PK_VALUE,
2841 DML_TYPE,PAYLOAD, STATUS,COMMENTS, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
2842 VALUES(P_SYNC_ID,l_seq,L_PIV_NAME,L_ACCESS_ID,L_DML_TYPE,PART_XML,'PARSE_ERROR',X_ERROR_MESSAGE,SYSDATE,L_USER_ID,SYSDATE,L_USER_ID);
2843
2844 END;
2845 l_seq:=l_seq+1;
2846 END LOOP;
2847
2848 UPDATE CSM_HTML5_UPLOAD_DATA
2849 SET LAST_UPDATE_DATE = SYSDATE,
2850 LAST_UPDATED_BY = 1,
2851 STATUS = 'PARSED',
2852 COMMENTS = 'Uploaded Data Parsed Successfully'
2853 WHERE SESSION_ID = P_SYNC_ID;
2854
2855 setup_inq_info(L_SYNC_USER_NAME,P_SYNC_ID,X_RETURN_STATUS);
2856 UPDATE asg_user SET hwm_tranid = P_SYNC_ID WHERE user_name = L_SYNC_USER_NAME;
2857
2858 x_return_status := FND_API.G_RET_STS_SUCCESS;
2859 x_error_message := 'XML Parsing Successfully completed ';
2860 CSM_UTIL_PKG.LOG
2861 ( 'Leaving PARSE_XML for SYNC ID after successfully Executing : ' || p_SYNC_ID , 'CSM_HTML5_PKG.HTML5_PARSE_XML',
2862 FND_LOG.LEVEL_PROCEDURE);
2863
2864 IF l_changed_df THEN
2865 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='''||l_df||'''';
2866 END If;
2867
2868
2869 EXCEPTION WHEN OTHERS THEN
2870 /*** catch and log exceptions ***/
2871 CSM_UTIL_PKG.LOG
2872 ( 'Exception occurred in PARSE_XML for SYNC ID : ' || p_SYNC_ID || SUBSTR(SQLERRM,1,3000), 'CSM_HTML5_PKG.HTML5_PARSE_XML',
2873 FND_LOG.LEVEL_EXCEPTION);
2874 x_return_status := FND_API.G_RET_STS_ERROR;
2875 X_ERROR_MESSAGE := 'XML Retrieve Failed With Message : ' || SUBSTR(SQLERRM,1,3000) ;
2876 IF l_changed_df THEN
2877 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='''||l_df||'''';
2878 END If;
2879 END HTML5_PARSE_XML;
2880
2881 PROCEDURE setup_inq_info(p_user_name IN VARCHAR2,
2882 p_tranid IN NUMBER,
2883 x_return_status OUT NOCOPY VARCHAR2)
2884 IS
2885 counter PLS_INTEGER;
2886 counter2 PLS_INTEGER;
2887 l_cursor_id1 NUMBER;
2888 l_cursor_ret1 NUMBER;
2889 l_store VARCHAR2(30);
2890 l_inq_count NUMBER;
2891 l_resource_id NUMBER;
2892 l_select_store_sqlstring VARCHAR2(512);
2893 l_select_inqcnt_sqlstring VARCHAR2(512);
2894 l_delete_inq_sqlstring VARCHAR2(512);
2895 l_pubitems_tbl asg_apply.vc2_tbl_type;
2896 curr_tranid NUMBER;
2897 curr_pubitem VARCHAR2(30);
2898 curr_pubitem_length PLS_INTEGER;
2899 l_pubitems_max_length PLS_INTEGER := 4000;
2900 l_pubitems_1 VARCHAR2(4000);
2901 l_pubitems_2 VARCHAR2(4000);
2902 CURSOR c_resource_id (p_user_name VARCHAR2) IS
2903 SELECT resource_id
2904 FROM asg_user
2905 WHERE user_name = p_user_name;
2906 BEGIN
2907 x_return_status := FND_API.G_RET_STS_SUCCESS;
2908 curr_tranid := p_tranid;
2909
2910 l_select_inqcnt_sqlstring :=
2911 'SELECT count(*) FROM CSM_HTML5_INQ ci ' ||
2912 'WHERE user_name = :1 AND ' ||
2913 ' sync_id = :2 AND ' ||
2914 ' pi_name in (select item_id from asg_pub_item) ' ||
2915 'AND NOT EXISTS (SELECT 1 FROM asg_users_inqinfo ' ||
2916 ' WHERE device_user_name = ci.user_name AND TRANID =ci.sync_id) ' ;
2917
2918 EXECUTE IMMEDIATE l_select_inqcnt_sqlstring INTO l_inq_count USING p_user_name, p_tranid;
2919
2920 IF (l_inq_count > 0) THEN
2921
2922 OPEN c_resource_id(p_user_name);
2923 FETCH c_resource_id into l_resource_id;
2924 CLOSE c_resource_id;
2925 IF l_resource_id IS NULL THEN
2926 csm_util_pkg.log('Did not find the user: '|| p_user_name || ' in asg_user table', 'CSM_HTML5_PKG.setup_inq_info',FND_LOG.LEVEL_PROCEDURE);
2927 x_return_status := FND_API.G_RET_STS_ERROR;
2928 return;
2929 END IF;
2930
2931 INSERT INTO asg_users_inqinfo (device_user_name,
2932 resource_id,
2933 tranid,
2934 sync_date,
2935 processed,
2936 deferred,
2937 archive,
2938 last_update_date,
2939 last_updated_by,
2940 creation_date,
2941 created_by)
2942 VALUES (p_user_name,
2943 l_resource_id,
2944 p_tranid,
2945 sysdate,
2946 'N',
2947 'N',
2948 'Y',
2949 SYSDATE,
2950 1,
2951 SYSDATE,
2952 1);
2953
2954 -- Get the list of pub-items for this tranid
2955 -- This is the list of all publication items uploaded for that tranid
2956 l_pubitems_1 := null;
2957 l_pubitems_2 := null;
2958 counter := 1;
2959 counter2:= 1;
2960 curr_pubitem_length := 0;
2961 l_select_store_sqlstring :=
2962 'SELECT pi_name FROM CSM_HTML5_INQ ci '||
2963 'WHERE user_name = :1 AND ' ||
2964 ' sync_id = :2 AND ' ||
2965 ' pi_name in (select item_id from asg_pub_item) '||
2966 ' AND NOT EXISTS (SELECT 1 FROM asg_users_inqarchive ' ||
2967 ' WHERE device_user_name = ci.user_name AND TRANID =ci.sync_id) '||
2968 ' ORDER BY pi_name';
2969
2970 l_cursor_id1 := DBMS_SQL.OPEN_CURSOR;
2971 DBMS_SQL.PARSE (l_cursor_id1, l_select_store_sqlstring, DBMS_SQL.v7);
2972 DBMS_SQL.DEFINE_COLUMN (l_cursor_id1, 1, l_store, 30);
2973 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':1', p_user_name);
2974 DBMS_SQL.BIND_VARIABLE(l_cursor_id1, ':2', p_tranid);
2975 l_cursor_ret1 := DBMS_SQL.EXECUTE (l_cursor_id1);
2976
2977 counter := 1;
2978 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id1) > 0 ) LOOP
2979 DBMS_SQL.COLUMN_VALUE (l_cursor_id1, 1, l_store);
2980 l_pubitems_tbl(counter) := l_store;
2981 counter := counter +1;
2982 END LOOP;
2983
2984 DBMS_SQL.CLOSE_CURSOR(l_cursor_id1);
2985 --12.1
2986 counter := 1;
2987 counter2:= 1;
2988 FOR curr_index2 IN 1..l_pubitems_tbl.count LOOP
2989 curr_pubitem := l_pubitems_tbl(curr_index2);
2990 curr_pubitem_length := curr_pubitem_length + length(curr_pubitem);
2991 IF curr_pubitem_length >= 8000 THEN
2992 EXIT;
2993 END IF;
2994 IF curr_pubitem_length < 4000 THEN
2995 IF counter >1 THEN
2996 l_pubitems_1 := l_pubitems_1 || ',';
2997 curr_pubitem_length := curr_pubitem_length + 1; -- length of ','
2998 END IF;
2999 l_pubitems_1 := l_pubitems_1 || curr_pubitem;
3000 counter := counter +1;
3001 ELSE
3002 IF counter2 >1 THEN
3003 l_pubitems_2 := l_pubitems_2 || ',';
3004 curr_pubitem_length := curr_pubitem_length + 1; -- length of ','
3005 END IF;
3006 l_pubitems_2 := l_pubitems_2 || curr_pubitem;
3007 counter2 := counter2 +1;
3008 END IF;
3009 END LOOP;
3010
3011 -- Replace with call to table-handler for asg_users_inqarchive
3012 INSERT INTO asg_users_inqarchive (device_user_name,
3013 resource_id,
3014 tranid,
3015 sync_date,
3016 processed,
3017 deferred,
3018 pub_items1,
3019 pub_items2,
3020 last_update_date,
3021 last_updated_by,
3022 creation_date,
3023 created_by)
3024 VALUES (p_user_name,
3025 l_resource_id,
3026 p_tranid,
3027 sysdate,
3028 'N',
3029 'N',
3030 l_pubitems_1,
3031 l_pubitems_2,
3032 SYSDATE,
3033 1,
3034 SYSDATE,
3035 1);
3036 END IF;
3037
3038 END setup_inq_info;
3039
3040 -- Procedure to purge all the dirty INQ records for
3041 -- the specified user/transid/publication-item(s)
3042 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
3043 p_tranid IN NUMBER,
3044 p_pubitems_tbl IN asg_apply.vc2_tbl_type,
3045 x_return_status OUT NOCOPY VARCHAR2)
3046 IS
3047 num_pubitems PLS_INTEGER;
3048 counter PLS_INTEGER;
3049 curr_pubitem VARCHAR2(30);
3050 inq_tbl_name VARCHAR2(30);
3051 sql_string VARCHAR2(1024);
3052 l_deferred_trans BOOLEAN := FALSE;
3053 l_resource_id NUMBER;
3054
3055 TYPE c_curtype is ref cursor;
3056 c_cur c_curtype;
3057
3058 l_dml VARCHAR2(1);
3059 l_pk VARCHAR2(1000);
3060 l_seq NUMBER;
3061
3062 CURSOR c_resource_id (p_user_name VARCHAR2) IS
3063 SELECT resource_id
3064 FROM asg_user
3065 WHERE user_name = p_user_name;
3066
3067 CURSOR c_pk(b_pi VARCHAR2)
3068 IS --currently only one pk col supported
3069 select primary_key_column from asg_pub_item where item_id=b_pi and instr(primary_key_column,',')=0;
3070
3071 BEGIN
3072 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
3073 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) OR
3074 (p_pubitems_tbl IS NULL) THEN
3075 x_return_status := FND_API.G_RET_STS_ERROR;
3076 return;
3077 END IF;
3078
3079 x_return_status := FND_API.G_RET_STS_SUCCESS;
3080 num_pubitems := p_pubitems_tbl.count;
3081 FOR curr_index in 1..num_pubitems LOOP
3082 curr_pubitem := p_pubitems_tbl(curr_index);
3083 -- Change to <pubitem_name>_inq once that synonym exists
3084
3085 OPEN C_GET_INQ_TABLE(curr_pubitem);
3086 FETCH C_GET_INQ_TABLE INTO inq_tbl_name;
3087 CLOSE C_GET_INQ_TABLE;
3088
3089 CSM_UTIL_PKG.log('Deferring unprocessed records in publication item: ' || curr_pubitem, 'CSM_HTML5_PKG.purge_pubitems',FND_LOG.LEVEL_PROCEDURE);
3090
3091 OPEN c_resource_id(p_user_name);
3092 FETCH c_resource_id INTO l_resource_id;
3093 IF c_resource_id%NOTFOUND THEN
3094 CLOSE c_resource_id;
3095 CSM_UTIL_PKG.log('purge_pubitems: User: ' ||p_user_name||' not found in asg_user table', 'CSM_HTML5_PKG.purge_pubitems',FND_LOG.LEVEL_PROCEDURE);
3096 x_return_status := FND_API.G_RET_STS_ERROR;
3097 return;
3098 END IF;
3099 CLOSE c_resource_id;
3100
3101
3102 l_pk :=NULL;
3103 OPEN c_pk(curr_pubitem);
3104 FETCH c_pk INTO l_pk;
3105 CLOSE c_pk;
3106
3107 IF l_pk IS NOT NULL THEN --this functionality is not there in usual ppc/laptop
3108 OPEN c_cur FOR 'SELECT DMLTYPE$$,SEQNO$$ ,to_char('||l_pk||') FROM '||inq_tbl_name||' a WHERE clid$$cs='''||p_user_name||''' and TRANID$$='||p_tranid
3109 ||'and not exists (SELECT 1 FROM asg_deferred_traninfo WHERE device_user_name = a.clid$$cs AND deferred_tran_id = a.tranid$$ and sequence=a.seqno$$ AND object_name ='''
3110 ||curr_pubitem||''')';
3111 LOOP
3112 FETCH c_cur INTO l_dml,l_seq,l_pk;
3113 EXIT WHEN c_cur%NOTFOUND;
3114 csm_notification_event_pkg.notify_deferred(p_user_name,p_tranid, curr_pubitem,l_seq, l_dml,l_pk,'Row deferred because it was left unprocessed');
3115 END LOOP;
3116 END IF;
3117
3118 -- Defer those records that were not already deferred or deleted during
3119 -- processing by wrapper
3120 sql_string := 'INSERT INTO asg_deferred_traninfo ('||
3121 'DEVICE_USER_NAME, ' ||
3122 'RESOURCE_ID, ' ||
3123 'DEFERRED_TRAN_ID, ' ||
3124 'MOBILE_ERROR_ID, ' ||
3125 'ERROR_DESCRIPTION, ' ||
3126 'OBJECT_NAME, ' ||
3127 'SEQUENCE, ' ||
3128 'STATUS, ' ||
3129 'SYNC_TIME, ' ||
3130 'FAILURES, ' ||
3131 'LAST_UPDATE_DATE, ' ||
3132 'LAST_UPDATED_BY, ' ||
3133 'CREATION_DATE, ' ||
3134 'CREATED_BY) ' ||
3135 'SELECT :1, :2, :3, NULL,''Row deferred because it was left unprocessed'', :4,seqno$$, 1,NULL,1,SYSDATE,1,SYSDATE,1 ' ||
3136 ' FROM '|| inq_tbl_name ||
3137 ' b WHERE b.clid$$cs = :5 AND ' ||
3138 ' tranid$$ = :6 AND ' ||
3139 ' b.seqno$$ not in (SELECT sequence ' ||
3140 'FROM asg_deferred_traninfo ' ||
3141 'WHERE device_user_name = :7 AND ' ||
3142 ' deferred_tran_id = :8 '||
3143 ' AND object_name = :9)';
3144 BEGIN
3145 EXECUTE IMMEDIATE sql_string
3146 USING p_user_name, l_resource_id, p_tranid,
3147 curr_pubitem, p_user_name,
3148 p_tranid, p_user_name, p_tranid, curr_pubitem;
3149
3150 IF SQL%ROWCOUNT >0 THEN
3151 CSM_UTIL_PKG.log('Number of rows deferred as unprocessed: ' || SQL%ROWCOUNT, 'CSM_HTML5_PKG.purge_pubitems',FND_LOG.LEVEL_PROCEDURE);
3152 l_deferred_trans := TRUE;
3153 END IF;
3154 EXCEPTION
3155 WHEN OTHERS THEN
3156 CSM_UTIL_PKG.log('Exception executing the SQL Command ' ||SUBSTR(SQLERRM,1,3000), 'CSM_HTML5_PKG.purge_pubitems',FND_LOG.LEVEL_PROCEDURE);
3157 END;
3158
3159 DELETE FROM CSM_HTML5_INQ WHERE USER_NAME=p_user_name AND SYNC_ID=p_tranid AND PI_NAME=curr_pubitem;
3160
3161 END LOOP;
3162
3163 IF (l_deferred_trans = TRUE) THEN
3164 UPDATE asg_users_inqinfo
3165 SET deferred = 'Y', processed = 'I',
3166 last_update_date = SYSDATE, last_updated_by = 1
3167 WHERE device_user_name = p_user_name AND
3168 tranid = p_tranid;
3169 END IF;
3170
3171 END purge_pubitems;
3172
3173 -- Procedure to purge all the dirty INQ records for
3174 -- the specified user/transid
3175 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
3176 p_tranid IN NUMBER,
3177 x_return_status OUT NOCOPY VARCHAR2)
3178 IS
3179 l_pubitems_tbl asg_apply.vc2_tbl_type;
3180 CURSOR l_c IS SELECT PI_NAME FROM CSM_HTML5_INQ WHERE USER_NAME=p_user_name AND SYNC_ID=p_tranid;
3181 BEGIN
3182
3183 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) OR
3184 ((p_tranid = FND_API.G_MISS_NUM) OR (p_tranid IS NULL)) THEN
3185 x_return_status := FND_API.G_RET_STS_ERROR;
3186 return;
3187 END IF;
3188
3189 x_return_status := FND_API.G_RET_STS_SUCCESS;
3190 OPEN l_c;
3191 FETCH l_c BULK COLLECT INTO l_pubitems_tbl;
3192 CLOSE l_c;
3193
3194 -- Found some dirty pub-items for this <p_user_name, p_tranid>
3195 IF (l_pubitems_tbl IS NOT NULL) AND
3196 (l_pubitems_tbl.count > 0) THEN
3197 purge_pubitems(p_user_name, p_tranid, l_pubitems_tbl, x_return_status);
3198 END IF;
3199
3200 -- If no deferred rows, set processed = 'Y'
3201 UPDATE asg_users_inqinfo
3202 SET processed = 'Y', last_update_date=SYSDATE, last_updated_by=1
3203 WHERE device_user_name = p_user_name AND
3204 tranid = p_tranid AND
3205 tranid not IN
3206 (SELECT distinct deferred_tran_id
3207 FROM asg_deferred_traninfo
3208 WHERE device_user_name = p_user_name AND
3209 deferred_tran_id = p_tranid);
3210
3211 END purge_pubitems;
3212
3213 -- Procedure to purge all the dirty INQ records for
3214 -- the specified user
3215 PROCEDURE purge_pubitems(p_user_name IN VARCHAR2,
3216 x_return_status OUT NOCOPY VARCHAR2)
3217 IS
3218
3219 counter PLS_INTEGER;
3220 curr_tranid NUMBER;
3221 l_tranid_tbl asg_apply.num_tbl_type;
3222 cursor c_tranids (p_user_name VARCHAR2, p_max_tranid NUMBER) IS
3223 SELECT tranid FROM asg_users_inqinfo
3224 WHERE device_user_name = p_user_name AND
3225 tranid <= p_max_tranid;
3226 BEGIN
3227 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) THEN
3228 x_return_status := FND_API.G_RET_STS_ERROR;
3229 return;
3230 END IF;
3231
3232 counter :=1;
3233 x_return_status := FND_API.G_RET_STS_SUCCESS;
3234 -- Get all the tranids for this user
3235 FOR cti in c_tranids(p_user_name, g_current_tranid) LOOP
3236 l_tranid_tbl(counter) := cti.tranid;
3237 counter := counter+1;
3238 END LOOP;
3239
3240 -- Process one tranid at a time.
3241 IF counter >1 THEN
3242 FOR curr_index in 1..l_tranid_tbl.count LOOP
3243 curr_tranid := l_tranid_tbl(curr_index);
3244 CSM_UTIL_PKG.log('Purging tranid: ' || curr_tranid, 'CSM_HTML5_PKG.purge_pubitems',FND_LOG.LEVEL_PROCEDURE);
3245 purge_pubitems(p_user_name, curr_tranid, x_return_status);
3246
3247 --Purge xml upload dump tables
3248 DELETE FROM CSM_HTML5_PARSED_DATA a
3249 WHERE A.SESSION_ID=curr_tranid
3250 AND a.status='MOVED_TO_INQ'
3251 AND NOT EXISTS (SELECT 1 FROM asg_deferred_traninfo b
3252 WHERE a.session_id=b.DEFERRED_TRAN_ID
3253 AND a.sequence=b.sequence); --reject record requires the data if deferred
3254 END LOOP;
3255 END IF;
3256
3257 DELETE FROM CSM_HTML5_UPLOAD_DATA b
3258 WHERE SESSION_ID <= g_current_tranid
3259 AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_PARSED_DATA a WHERE a.SESSION_ID=b.SESSION_ID);
3260 END purge_pubitems;
3261
3262 PROCEDURE end_client_apply(p_user_name IN VARCHAR2,
3263 x_return_status OUT NOCOPY VARCHAR2)
3264 IS
3265 curr_tranid NUMBER;
3266 curr_tran_processed VARCHAR2(1);
3267 curr_tran_deferred VARCHAR2(1);
3268 curr_tran_archive VARCHAR2(1);
3269 CURSOR c_archive_asg_users(p_user_name VARCHAR2) IS
3270 SELECT tranid, processed, deferred, archive
3271 FROM asg_users_inqinfo
3272 WHERE device_user_name = p_user_name;
3273 BEGIN
3274
3275 IF ((p_user_name = FND_API.G_MISS_CHAR) OR (p_user_name IS NULL)) THEN
3276 x_return_status := FND_API.G_RET_STS_ERROR;
3277 return;
3278 END IF;
3279
3280 x_return_status := FND_API.G_RET_STS_SUCCESS;
3281
3282 purge_pubitems(p_user_name, x_return_status);
3283
3284 FOR caau in c_archive_asg_users(p_user_name) LOOP
3285 curr_tranid := caau.tranid;
3286 curr_tran_processed := caau.processed;
3287 curr_tran_deferred := caau.deferred;
3288 curr_tran_archive := caau.archive;
3289 IF (curr_tran_processed = 'Y') AND (curr_tran_archive = 'Y') THEN
3290 UPDATE asg_users_inqarchive
3291 SET processed = 'Y', deferred = curr_tran_deferred,
3292 last_update_date = SYSDATE, last_updated_by = 1
3293 WHERE device_user_name = p_user_name AND
3294 tranid = curr_tranid;
3295 END IF;
3296 END LOOP;
3297
3298 DELETE FROM asg_users_inqinfo
3299 WHERE device_user_name = p_user_name AND processed = 'Y';
3300
3301 --COMMIT;
3302
3303 END end_client_apply;
3304
3305 PROCEDURE get_first_tranid(p_user_name IN VARCHAR2,
3306 x_tranid OUT NOCOPY NUMBER,
3307 x_return_status OUT NOCOPY VARCHAR2)
3308 IS
3309 CURSOR c_first_tran (p_user_name VARCHAR2) IS
3310 SELECT min(tranid) tran_id
3311 FROM asg_users_inqinfo a
3312 WHERE a.device_user_name = p_user_name AND
3313 a.deferred='N'
3314 AND a.tranid <=
3315 (SELECT nvl(hwm_tranid,1000000000000)
3316 FROM asg_user
3317 WHERE user_name=a.device_user_name);
3318
3319 BEGIN
3320
3321 x_return_status := FND_API.G_RET_STS_SUCCESS;
3322
3323 OPEN c_first_tran(p_user_name);
3324 FETCH c_first_tran into x_tranid;
3325 IF c_first_tran%NOTFOUND OR x_tranid is NULL THEN
3326 x_return_status := FND_API.G_RET_STS_ERROR;
3327 csm_util_pkg.log('No new tranids', 'CSM_HTML5_PKG.get_first_tranid',FND_LOG.LEVEL_PROCEDURE);
3328 CLOSE c_first_tran;
3329 RETURN;
3330 END IF;
3331 CLOSE c_first_tran;
3332 csm_util_pkg.log('Returning UserName: ' || p_user_name ||' TranID: ' || x_tranid, 'CSM_HTML5_PKG.get_first_tranid',FND_LOG.LEVEL_PROCEDURE);
3333 END get_first_tranid;
3334
3335 -- Will set x_return_status to FND_API.G_RET_STS_ERROR if no tranid exists
3336 -- Returns both dirty and deferred tranids
3337 PROCEDURE get_next_tranid(p_user_name IN VARCHAR2,
3338 p_curr_tranid IN NUMBER,
3339 x_tranid OUT NOCOPY NUMBER,
3340 x_return_status OUT NOCOPY VARCHAR2)
3341 IS
3342 CURSOR c_next_tran (p_user_name VARCHAR2, p_tranid VARCHAR2) IS
3343 SELECT min(tranid) tran_id
3344 FROM asg_users_inqinfo a
3345 WHERE tranid > p_tranid
3346 AND a.device_user_name = p_user_name AND a.deferred='N'
3347 AND a.tranid <= (SELECT nvl(hwm_tranid,1000000000000)
3348 FROM asg_user WHERE user_name=a.device_user_name);
3349
3350 BEGIN
3351
3352 x_return_status := FND_API.G_RET_STS_SUCCESS;
3353
3354 OPEN c_next_tran(p_user_name, p_curr_tranid);
3355 FETCH c_next_tran into x_tranid;
3356 IF c_next_tran%NOTFOUND OR x_tranid IS NULL THEN
3357 -- When the current tranid is the last one, set the next tranid
3358 -- also to the last one.
3359 x_tranid := p_curr_tranid;
3360 x_return_status := FND_API.G_RET_STS_ERROR;
3361 csm_util_pkg.log('No new tranids', 'CSM_HTML5_PKG.get_next_tranid',FND_LOG.LEVEL_PROCEDURE);
3362 CLOSE c_next_tran;
3363 RETURN;
3364 END IF;
3365 CLOSE c_next_tran;
3366
3367 csm_util_pkg.log('Returning UserName: ' || p_user_name ||'\n Current TranID: ' || p_curr_tranid
3368 ||'\n Next TranID: ' || x_tranid, 'CSM_HTML5_PKG.get_next_tranid',FND_LOG.LEVEL_PROCEDURE);
3369
3370 END get_next_tranid;
3371
3372
3373 PROCEDURE process_user(p_user_name IN VARCHAR2,
3374 p_tranid IN NUMBER,
3375 x_return_status OUT NOCOPY VARCHAR2)
3376 IS
3377 l_respid NUMBER;
3378 l_appid NUMBER;
3379 l_userid NUMBER;
3380 l_conc_userid NUMBER;
3381 l_conc_respid NUMBER;
3382 l_conc_appid NUMBER;
3383 BEGIN
3384 x_return_status := FND_API.G_RET_STS_SUCCESS;
3385
3386 -- Get the conc program's user id, respid and appid
3387 l_conc_userid := fnd_global.user_id();
3388 IF l_conc_userid IS NULL or l_conc_userid = -1 THEN
3389 l_conc_userid := 5;
3390 END IF;
3391 l_conc_respid := fnd_global.resp_id();
3392 IF l_conc_respid IS NULL or l_conc_respid = -1 THEN
3393 l_conc_respid := 20420;
3394 END IF;
3395 l_conc_appid := fnd_global.resp_appl_id();
3396 IF l_conc_appid IS NULL or l_conc_appid = -1 THEN
3397 l_conc_appid := 1;
3398 END IF;
3399
3400 SELECT au.user_id,pr.responsibility_id, pr.app_id
3401 INTO l_userid,l_respid,l_appid
3402 FROM asg_user_pub_resps pr ,asg_user au
3403 WHERE pr.user_name = upper(p_user_name)
3404 AND au.user_name=pr.user_name
3405 AND pr.pub_name = upper('SERVICEP') AND ROWNUM=1;
3406
3407
3408 fnd_global.apps_initialize(l_userid, l_respid, l_appid);
3409 BEGIN
3410 CSM_SERVICEP_WRAPPER_PKG.APPLY_HTML5_CHANGES(p_user_name,p_tranid);
3411 EXCEPTION
3412 WHEN OTHERS THEN
3413 CSM_UTIL_PKG.LOG( 'Exception occurred in process_user:'||SUBSTR(SQLERRM,1,3000), 'CSM_HTML5_PKG.process_user',FND_LOG.LEVEL_EXCEPTION);
3414 fnd_global.apps_initialize(l_conc_userid, l_conc_respid, l_conc_appid);
3415 --x_return_status := FND_API.G_RET_STS_ERROR; --in asg_apply returns as SUCCESS always
3416 END;
3417 END process_user;
3418
3419 PROCEDURE process_upload(curr_user IN VARCHAR2)
3420 IS
3421 curr_tranid NUMBER;
3422 next_tranid NUMBER;
3423 l_return_status VARCHAR2(1);
3424 BEGIN
3425 csm_util_pkg.log('Processing upload for user: '|| curr_user, 'CSM_HTML5_PKG.process_upload',FND_LOG.LEVEL_PROCEDURE);
3426 get_first_tranid(p_user_name => curr_user,x_tranid => curr_tranid,x_return_status => l_return_status);
3427 g_current_tranid:=curr_tranid;
3428 WHILE l_return_status = FND_API.G_RET_STS_SUCCESS LOOP
3429 csm_util_pkg.log('Processing tranid: ' || curr_tranid, 'CSM_HTML5_PKG.process_upload',FND_LOG.LEVEL_PROCEDURE);
3430 process_user(p_user_name => curr_user,p_tranid => curr_tranid, x_return_status => l_return_status);
3431 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3432 -- Make the call to get the next tranid
3433 -- This means if upload processing for one transaction failed
3434 -- upload processing for the rest of the transactions is stopped.
3435 get_next_tranid(p_user_name => curr_user,p_curr_tranid => curr_tranid,x_tranid => next_tranid,x_return_status => l_return_status);
3436
3437 curr_tranid := next_tranid;
3438 g_current_tranid:=curr_tranid; --used by purge pub items in end client apply
3439 END IF;
3440 END LOOP;
3441
3442 end_client_apply(p_user_name => curr_user, x_return_status => l_return_status);
3443
3444 csm_util_pkg.log('Return status from end_client_apply: ' ||l_return_status, 'CSM_HTML5_PKG.process_upload',FND_LOG.LEVEL_PROCEDURE);
3445
3446 END process_upload;
3447
3448
3449 PROCEDURE get_all_pub_items(p_user_name IN VARCHAR2,
3450 p_tranid IN NUMBER,
3451 x_pubitems_tbl OUT NOCOPY asg_apply.vc2_tbl_type,
3452 x_return_status OUT NOCOPY VARCHAR2)
3453 IS
3454 counter PLS_INTEGER;
3455 CURSOR l_cursor1 IS
3456 SELECT PI_NAME
3457 FROM CSM_HTML5_INQ a,ASG_PUB_ITEM b
3458 WHERE a.PI_NAME=b.ITEM_ID
3459 AND a.user_name=p_user_name
3460 AND a.SYNC_ID=p_tranid
3461 ORDER BY nvl(b.table_weight,0);
3462
3463 l_dummy boolean:=false;
3464 l_n NUMBER;
3465 BEGIN
3466
3467 x_return_status := FND_API.G_RET_STS_SUCCESS;
3468
3469 OPEN l_cursor1;
3470 FETCH l_cursor1 BULK COLLECT INTO x_pubitems_tbl;
3471 CLOSE l_cursor1;
3472
3473 l_n:=x_pubitems_tbl.count;
3474 IF l_n >0 THEN l_dummy:=true; END IF;
3475
3476 FOR REC IN (SELECT object_name
3477 FROM asg_deferred_traninfo a,asg_pub_item b
3478 WHERE device_user_name = p_user_name
3479 AND deferred_tran_id = p_tranid
3480 AND b.item_id=a.object_name
3481 AND NOT EXISTS(SELECT 1 FROM CSM_HTML5_INQ
3482 WHERE PI_NAME= object_name
3483 AND USER_NAME=device_user_name
3484 AND SYNC_ID=deferred_tran_id)
3485 ORDER BY nvl(b.table_weight,0))
3486 LOOP
3487 l_n:=l_n+1;
3488 x_pubitems_tbl(l_n):=rec.OBJECT_NAME;
3489 END LOOP;
3490
3491 IF(l_n=x_pubitems_tbl.COUNT) THEN l_dummy:=false; END IF;
3492
3493 IF l_dummy THEN
3494 asg_apply.sort_by_weight('SERVICEP', x_pubitems_tbl);
3495 END IF;
3496
3497 END get_all_pub_items;
3498
3499
3500 PROCEDURE reapply_transaction(p_user_name IN VARCHAR2,
3501 p_tranid IN NUMBER,
3502 x_return_status OUT NOCOPY VARCHAR2,
3503 p_commit_flag IN BOOLEAN)
3504 IS
3505
3506 l_def_trans VARCHAR2(1);
3507 l_def_count NUMBER;
3508 l_user_id NUMBER;
3509 l_resp_id NUMBER;
3510 l_app_id NUMBER;
3511 l_orig_user_id NUMBER;
3512 l_orig_resp_id NUMBER;
3513 l_orig_app_id NUMBER;
3514
3515 CURSOR c_deferred_processed (p_user_name VARCHAR2, p_tranid NUMBER) IS
3516 SELECT count(*) count
3517 FROM asg_deferred_traninfo
3518 WHERE device_user_name = p_user_name AND
3519 deferred_tran_id = p_tranid AND
3520 status <> 0;
3521 BEGIN
3522
3523 CSM_UTIL_PKG.LOG( 'Reapply started for user:'||p_user_name ||' tranid:'||p_tranid, 'CSM_HTML5_PKG.reapply_transaction',FND_LOG.LEVEL_PROCEDURE);
3524 -- Check if this transaction is deferred
3525 l_def_trans := asg_defer.is_deferred(p_user_name, p_tranid);
3526 IF l_def_trans = FND_API.G_FALSE THEN
3527 CSM_UTIL_PKG.LOG( 'Leaving since tranid is not deferred', 'CSM_HTML5_PKG.reapply_transaction',FND_LOG.LEVEL_PROCEDURE);
3528 x_return_status := FND_API.G_RET_STS_ERROR;
3529 return;
3530 END IF;
3531
3532 x_return_status := FND_API.G_RET_STS_SUCCESS;
3533
3534 BEGIN
3535 l_orig_user_id := fnd_global.user_id();
3536 l_orig_resp_id := fnd_global.resp_id();
3537 l_orig_app_id := fnd_global.resp_appl_id();
3538
3539 SELECT au.user_id,pr.responsibility_id, pr.app_id
3540 INTO l_user_id,l_resp_id,l_app_id
3541 FROM asg_user_pub_resps pr ,asg_user au
3542 WHERE pr.user_name = upper(p_user_name)
3543 AND au.user_name=pr.user_name
3544 AND pr.pub_name = upper('SERVICEP') AND ROWNUM=1;
3545
3546 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
3547 CSM_SERVICEP_WRAPPER_PKG.APPLY_HTML5_CHANGES(p_user_name,p_tranid);
3548 fnd_global.apps_initialize(l_orig_user_id, l_orig_resp_id,l_orig_app_id);
3549 EXCEPTION
3550 WHEN OTHERS THEN
3551 CSM_UTIL_PKG.LOG( 'Exception occurred in reapply_transaction:'||SUBSTR(SQLERRM,1,3000), 'CSM_HTML5_PKG.reapply_transaction',FND_LOG.LEVEL_EXCEPTION);
3552 x_return_status := FND_API.G_RET_STS_SUCCESS;
3553 fnd_global.apps_initialize(l_orig_user_id, l_orig_resp_id,l_orig_app_id);
3554 return;
3555 END;
3556
3557 OPEN c_deferred_processed(p_user_name, p_tranid);
3558 FETCH c_deferred_processed INTO l_def_count;
3559 CLOSE c_deferred_processed;
3560 -- All the deferred records are processed
3561 IF l_def_count = 0 THEN
3562 UPDATE asg_users_inqinfo
3563 SET deferred = 'S'
3564 WHERE device_user_name = p_user_name AND
3565 tranid = p_tranid;
3566 END IF;
3567
3568 IF p_commit_flag THEN
3569 COMMIT;
3570 END IF;
3571
3572 CSM_UTIL_PKG.LOG( 'Reapply Completed', 'CSM_HTML5_PKG.reapply_transaction',FND_LOG.LEVEL_PROCEDURE);
3573 END reapply_transaction;
3574
3575 PROCEDURE CONC_PROCESS_UPLOAD(errbuf OUT NOCOPY VARCHAR2,RETCODE OUT NOCOPY VARCHAR2)
3576 IS
3577 l_uname VARCHAR2(500):=NULL;
3578 l_list VARCHAR2(4000):=NULL;
3579 BEGIN
3580 FOR rec IN (select a.session_id,c.user_name from csm_html5_upload_data a,asg_user b, csm_html5_sync_info c
3581 where a.status<>'PARSED' and a.session_id=c.session_id
3582 and nvl(fnd_profile.value_specific('ASG_SYNCHRONOUS_UPLOAD',b.user_id,b.responsibility_id,b.app_id),'N')='N'
3583 and b.user_name=c.user_name and b.multi_platform='Y' and b.enabled='Y' ORDER BY c.USER_NAME,a.SESSION_ID ASC)
3584 LOOP
3585 IF(l_uname IS NULL OR l_uname<>rec.USER_NAME) THEN
3586 CSM_HTML5_PKG.HTML5_PARSE_XML(rec.SESSION_ID,RETCODE,errbuf);
3587 IF RETCODE=FND_API.G_RET_STS_ERROR THEN
3588 CSM_UTIL_PKG.LOG( 'Exception while parsing upload data in tran_id:'||rec.session_id||' for user:'||rec.USER_NAME||' with error:'||substr(errbuf,1,1000), 'CSM_HTML5_PKG.CONC_PROCESS_UPLOAD',FND_LOG.LEVEL_EXCEPTION);
3589
3590 UPDATE CSM_HTML5_UPLOAD_DATA
3591 SET LAST_UPDATE_DATE = SYSDATE,
3592 LAST_UPDATED_BY = 1,
3593 STATUS = 'PARSE_ERROR',
3594 COMMENTS = errbuf
3595 WHERE SESSION_ID = rec.SESSION_ID;
3596
3597 l_uname:= rec.USER_NAME;
3598 IF l_list IS NULL THEN l_list:=l_uname;
3599 else l_list:=substr(l_list||','||l_uname,0,3500);
3600 END IF;
3601 END IF;
3602 END IF;
3603 END LOOP;
3604
3605 FOR rec IN (SELECT DISTINCT USER_NAME FROM CSM_HTML5_INQ)
3606 LOOP
3607 CSM_HTML5_PKG.process_upload(rec.USER_NAME);
3608 END LOOP;
3609
3610 IF l_uname is null THEN
3611 retcode := FND_API.G_RET_STS_SUCCESS;
3612 errbuf:='Success';
3613 ELSE
3614 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
3615 errbuf:='Issue with parsing xml data of users:'||l_list;
3616 END IF;
3617 END CONC_PROCESS_UPLOAD;
3618
3619 FUNCTION isSSOEnabled RETURN VARCHAR2
3620 IS
3621 BEGIN
3622 IF instr(NVL(fnd_profile.value('APPS_SSO'),'NULL'),'SSO')>0 THEN RETURN 'Y'; END IF;
3623 RETURN 'N';
3624 END isSSOEnabled;
3625
3626 PROCEDURE setForceLocalLogin(p_user_name IN VARCHAR2,p_v IN VARCHAR2)
3627 IS
3628 BEGIN
3629 CSM_UTIL_PKG.LOG('Local Login set to :'||p_v||' for user:'||p_user_name, 'CSM_HTML5_PKG.setForceLocalLogin', FND_LOG.LEVEL_PROCEDURE);
3630 IF p_v<>'Y' THEN
3631 DELETE FROM csm_html5_clients_info WHERE PRF_NAME='FORCE_LOCAL_LOGIN' AND USER_NAME=p_user_name;
3632 ELSE
3633 set_user_profile(p_user_name,'FORCE_LOCAL_LOGIN','Y');
3634 END IF;
3635
3636 END setForceLocalLogin;
3637
3638 FUNCTION isOIDAuth(p_user_name IN VARCHAR2) RETURN BOOLEAN
3639 IS
3640 l_user_id NUMBER;
3641 l_resp_id NUMBER;
3642 l_app_id NUMBER;
3643 l_local VARCHAR2(1):='N';
3644 BEGIN
3645
3646 --note: other ajax queries might also set force login to Y. stopForceLogin from client only can set it back to N
3647 --add if block with profile later to turn this feature off completely
3648 BEGIN
3649 SELECT PRF_VALUE INTO l_local
3650 FROM CSM_HTML5_CLIENTS_INFO
3651 WHERE USER_NAME=p_user_name
3652 AND PRF_NAME='FORCE_LOCAL_LOGIN'
3653 AND PRF_VALUE='Y';
3654
3655 RETURN FALSE;
3656 EXCEPTION
3657 WHEN OTHERS THEN
3658 NULL;
3659 END;
3660
3661
3662
3663 SELECT user_id,responsibility_id, app_id
3664 INTO l_user_id,l_resp_id,l_app_id
3665 FROM asg_user WHERE user_name=p_user_name;
3666
3667 IF (instr(NVL(fnd_profile.value('APPS_SSO'),'NULL'),'SSO')>0)
3668 AND (NVL(fnd_profile.value_specific('APPS_SSO_LOCAL_LOGIN',l_user_id,l_resp_id,l_app_id),'BOTH')<>'LOCAL') THEN
3669 RETURN TRUE;
3670 END IF;
3671
3672 RETURN FALSE;
3673 EXCEPTION
3674 WHEN OTHERS THEN
3675 RETURN FALSE;
3676 END isOIDAuth;
3677
3678 procedure delete_user_vault(p_user_id IN NUMBER)
3679 is
3680 begin
3681 set_context('CSM5_VAULT_NS', 'VAULT', 'Y');
3682 fnd_vault.del('CSM','RSA_KEY_'||p_user_id);
3683 set_context('CSM5_VAULT_NS', 'VAULT', 'N');
3684 exception
3685 when others then
3686 CSM_UTIL_PKG.LOG('Exception:'||' for user_id:'||p_user_id||' with msg:'||substr(sqlerrm,1,2000), 'CSM_HTML5_PKG.delete_user_vault', FND_LOG.LEVEL_PROCEDURE);
3687 end delete_user_vault;
3688
3689 PROCEDURE DROP_USER(p_user_id IN NUMBER)
3690 IS BEGIN
3691 DELETE FROM csm_html5_clients_info WHERE USER_NAME=(SELECT USER_NAME
3692 FROM ASG_USER WHERE USER_ID=p_user_id) AND PRF_NAME NOT IN ('REVISION','FORCE_UPDATE'); --if any user has name as APPLICATION
3693
3694 DELETE FROM CSM_HTML5_CACHE WHERE USER_NAME=(SELECT USER_NAME
3695 FROM ASG_USER WHERE USER_ID=p_user_id);
3696
3697 CSM_HTML5_PKG.DELETE_USER_VAULT(p_user_id);
3698 END DROP_USER;
3699
3700 FUNCTION IS_FIRST_SYNC(p_user_name IN VARCHAR2) RETURN BOOLEAN
3701 IS
3702 l_t NUMBER;
3703 BEGIN
3704 SELECT 1 INTO l_t
3705 FROM asg_purge_sdq
3706 WHERE user_name = p_user_name
3707 AND pub_name = 'SERVICEP'
3708 AND TRANSACTION_ID IS NULL;
3709 RETURN TRUE;
3710 EXCEPTION
3711 WHEN OTHERS THEN
3712 RETURN FALSE;
3713 END IS_FIRST_SYNC;
3714
3715 FUNCTION get_app_upg_info(p_user_name IN VARCHAR2) RETURN VARCHAR2
3716 IS
3717 l_store VARCHAR2(4000);
3718 BEGIN
3719 SELECT (SELECT NVL(MAX(PRF_VALUE),'N') FROM CSM_HTML5_CLIENTS_INFO WHERE USER_NAME='APPLICATION' AND PRF_NAME='FORCE_UPDATE')
3720 ||';'|| fnd_profile.value_specific('CSM_MULTI_APP_STORE_URL',USER_ID,RESPONSIBILITY_ID)
3721 INTO l_store
3722 FROM ASG_USER WHERE USER_NAME=p_user_name;
3723
3724 RETURN l_store;
3725 END get_app_upg_info;
3726
3727 end CSM_HTML5_PKG;
[Home] [Help]