[Home] [Help]
PACKAGE BODY: APPS.HR_WPM_MASS_SCORE_CARD_TRNSF
Source
1 PACKAGE BODY HR_WPM_MASS_SCORE_CARD_TRNSF AS
2 /* $Header: hrwpmtrnsf.pkb 120.8.12020000.1 2012/06/29 00:35:24 appldev ship $*/
3
4 ---- BEGIN Changes for attachments issue
5 TYPE l_docrow IS RECORD (post_state NUMBER,
6 document_id number,
7 creation_date date,
8 created_by NUMBER,
9 last_update_date DATE,
10 last_updated_by NUMBER,
11 last_update_login NUMBER,
12 datatype_id NUMBER,
13 description fnd_documents_tl.description%TYPE,
14 file_name fnd_documents.file_name%TYPE,
15 media_id fnd_documents.media_id%TYPE,
16 category_id fnd_documents.category_id%TYPE,
17 security_type fnd_documents.security_type%TYPE,
18 publish_flag fnd_documents.publish_flag%TYPE,
19 usage_type fnd_documents.usage_type%TYPE,
20 dm_node fnd_documents.dm_node%TYPE,
21 title fnd_documents_tl.title%TYPE);
22 TYPE t_doc IS TABLE OF l_docrow INDEX BY BINARY_INTEGER;
23 TYPE l_attachdoc IS RECORD (post_state NUMBER,
24 attached_document_id NUMBER,
25 document_id NUMBER,
26 creation_date DATE,
27 created_by NUMBER,
28 last_update_date DATE,
29 last_updated_by NUMBER,
30 last_update_login NUMBER,
31 seq_num NUMBER,
32 entity_name fnd_attached_documents.entity_name%TYPE,
33 pk1_value fnd_attached_documents.pk1_value%TYPE,
34 automatically_added_flag fnd_attached_documents.automatically_added_Flag%TYPE,
35 attachment_category_id NUMBER);
36 TYPE t_attachdoc IS TABLE OF l_attachdoc INDEX BY BINARY_INTEGER;
37 TYPE l_lob IS RECORD (post_state NUMBER,
38 file_id NUMBER,
39 file_name fnd_lobs.file_name%TYPE,
40 file_content_type fnd_lobs.file_content_type%TYPE,
41 file_data BLOB,
42 oracle_charset fnd_lobs.oracle_charset%TYPE,
43 file_format fnd_lobs.file_format%TYPE);
44 TYPE t_lob IS TABLE of l_lob INDEX BY BINARY_INTEGER;
45 TYPE l_shorttext IS RECORD (post_state NUMBER,
46 media_id NUMBER,
47 short_text Fnd_Documents_Short_Text.short_text%TYPE,
48 app_source_version Fnd_Documents_Short_Text.app_source_version%TYPE);
49 TYPE t_shorttext IS TABLE of l_shorttext INDEX BY BINARY_INTEGER;
50 gt_lob t_lob;
51 gt_doc t_doc;
52 gt_attachdoc t_attachdoc;
53 gt_shorttext t_shorttext;
54
55 ---
56 PROCEDURE clob_to_blob (inLob in CLOB,
57 outLob in out nocopy BLOB)
58 IS
59 nPos integer;
60 vcBuff varchar2(32000);
61 rBuff raw(32000);
62 nBuffSize binary_integer;
63 BEGIN
64 IF inlob IS NULL OR outlob IS NULL THEN
65 RAISE_APPLICATION_ERROR(-20000,'Invalid lob locator (null value)');
66 END IF;
67 BEGIN
68 dbms_lob.trim(outLob,0);
69 npos := 0;
70 LOOP
71 nBuffSize := 32000;
72 dbms_lob.read(inlob,nbuffSize,nPos +1, vcBuff);
73 nPos := nPos + nBuffSize;
74 rBuff := vcBuff; -- utl_raw.CAST_to_raw(vcBuff);
75 nBuffSize := utl_raw.length(rBuff);
76 dbms_lob.writeappend(outLob, nBuffSize, rBuff);
77 END LOOP;
78 EXCEPTION WHEN NO_DATA_FOUND /* end of lob */ THEN
79 null;
80 END;
81 EXCEPTION WHEN OTHERS THEN
82 RAISE;
83 END clob_to_blob;
84 --
85 PROCEDURE commit_attachments IS
86 l_doccnt NUMBER;
87 l_attchdcnt NUMBER;
88 l_lobcnt NUMBER;
89 l_document_id NUMBER;
90 l_rowid VARCHAR2(50);
91 l_media_id NUMBER(15);
92 BEGIN
93 --- First start with the FndDocuments data.
94 l_doccnt := gt_doc.COUNT;
95 IF l_doccnt > 0 THEN
96 FOR i IN gt_doc.FIRST..gt_doc.LAST
97 LOOP
98 IF gt_doc(i).post_state = 0 THEN --- Post state is Insert
99 l_document_id := NULL;
100 l_rowid := NULL;
101 BEGIN
102 hr_utility.trace('media_id:'||gt_doc(i).media_id);
103 hr_utility.trace('file name:'||gt_doc(i).file_name);
104 l_media_id := gt_doc(i).media_id;
105 fnd_documents_pkg.Insert_Row(X_Rowid => l_rowid,
106 X_document_id => l_document_id,
107 X_creation_date => gt_doc(i).creation_date,
108 X_created_by => gt_doc(i).created_by,
109 X_last_update_date => gt_doc(i).last_update_date,
110 X_last_updated_by => gt_doc(i).last_updated_by,
111 X_last_update_login => gt_doc(i).last_update_login,
112 X_datatype_id => gt_doc(i).datatype_id,
113 X_category_id => gt_doc(i).category_id,
114 X_security_type => gt_doc(i).security_type,
115 X_publish_flag => gt_doc(i).publish_flag,
116 X_usage_type => gt_doc(i).usage_type,
117 X_language => USERENV('lang'),
118 X_description => gt_doc(i).description,
119 X_file_name => gt_doc(i).file_name,
120 X_media_id => l_media_id,
121 X_create_doc => 'N',
122 X_title => gt_doc(i).title);
123 hr_utility.trace('doc_id created :'||l_document_id);
124 IF gt_doc(i).datatype_id = 1 THEN --- short description
125 IF gt_shorttext.COUNT > 0 THEN --- count check
126 FOR j IN gt_shorttext.FIRST..gt_shorttext.LAST
127 LOOP
128 HR_UTILITY.TRACE('mEDIA ID FROM SHORT TEXT: '||gt_shorttext(j).media_id ||'-'||gt_doc(i).media_id);
129 IF gt_doc(i).media_id = gt_shorttext(j).media_id THEN -- insert only if the media id matches
130 HR_UTILITY.TRACE('INSERTING MEDIA ID: '||GT_SHORTTEXT(J).MEDIA_ID);
131 INSERT INTO FND_DOCUMENTS_SHORT_TEXT
132 (
133 media_id
134 ,short_text
135 ,app_source_version
136 ) VALUES
137 (
138 l_media_id
139 ,gt_shorttext(j).short_text
140 ,gt_shorttext(j).app_source_version
141 );
142 END IF;
143 END LOOP; --- Loop for LOB's
144 END IF; --- count check
145 ELSIF gt_doc(i).datatype_id = 6 THEN --- LOB/document
146 IF gt_lob.COUNT > 0 THEN -- count check
147 FOR j IN gt_lob.FIRST..gt_lob.LAST
148 LOOP
149 IF gt_doc(i).media_id = gt_lob(j).file_id THEN -- insert only if the media id matches
150 INSERT INTO FND_LOBS
151 (
152 FILE_ID
153 ,FILE_NAME
154 ,FILE_CONTENT_TYPE
155 ,FILE_DATA
156 ,UPLOAD_DATE
157 ,ORACLE_CHARSET
158 ,FILE_FORMAT
159 ) VALUES
160 (
161 gt_lob(j).file_id
162 ,gt_lob(j).file_Name
163 ,gt_lob(j).file_content_type
164 ,gt_lob(j).file_data
165 ,SYSDATE
166 ,gt_lob(j).oracle_charset
167 ,gt_lob(j).file_format
168 );
169 END IF;
170 END LOOP; --- Loop for LOB's
171 END IF; -- count check
172 END IF; -- datatype_id check
173 -- Now create attached document row
174 FOR k IN gt_attachdoc.FIRST..gt_attachdoc.LAST
175 LOOP
176 IF gt_doc(i).document_id = gt_attachdoc(k).document_id THEN --- found a matching document, create now.
177 INSERT INTO fnd_attached_documents (
178 attached_document_id,
179 document_id,
180 creation_date,
181 created_by,
182 last_update_date,
183 last_updated_by,
184 last_update_login,
185 seq_num,
186 entity_name,
187 column1,
188 pk1_value,
189 pk2_value,
190 pk3_value,
191 pk4_value,
192 pk5_value,
193 automatically_added_flag,
194 category_id) VALUES (
195 gt_attachdoc(k).attached_document_id,
196 l_document_id,
197 gt_attachdoc(k).creation_date,
198 gt_attachdoc(k).created_by,
199 gt_attachdoc(k).last_update_date,
200 gt_attachdoc(k).last_updated_by,
201 gt_attachdoc(k).last_update_login,
202 gt_attachdoc(k).seq_num,
203 gt_attachdoc(k).entity_name,
204 NULL,
205 gt_attachdoc(k).pk1_value,
206 NULL,
207 NULL,
208 NULL,
209 NULL,
210 gt_attachdoc(k).automatically_added_flag,
211 gt_attachdoc(k).attachment_category_id);
212 END IF;
213 END LOOP;
214 END;
215 END IF; ---- POst state check
216 END LOOP;
217 --
218 END IF;
219 ---
220 EXCEPTION WHEN OTHERS THEN
221 hr_utility.trace('Error in Commit_attachments');
222 hr_utility.trace(sqlerrm);
223 END commit_attachments;
224 --
225 PROCEDURE process_attachments(p_transaction_id IN NUMBER) IS
226 CURSOR csr_clob(p_transaction_id IN NUMBER) IS
227 SELECT transaction_document
228 FROM hr_api_transactions
229 WHERE transaction_id = p_transaction_id;
230 txnClob CLOB;
231 txnXml xmldom.DOMElement;
232 nl xmldom.DOMNodeList;
233 nl2 xmldom.DOMNodeList;
234 nl3 xmldom.DOMNodeList;
235 nl4 xmldom.DOMNodeList;
236 len1 number;
237 len2 number;
238 n xmldom.DOMNode;
239 n1 xmldom.DOMNode;
240 e xmldom.DOMElement;
241 nnm xmldom.DOMNamedNodeMap;
242 attrname varchar2(100);
243 attrval varchar2(100);
244 l_clob CLOB;
245 l_filedata CLOB;
246 lb_filedata blob;
247 l_filestart NUMBER;
248 l_fileend NUMBER;
249 BEGIN
250 -- hr_utility.trace_on(NULL,'ATTACH_TEST');
251 hr_utility.set_location('Entering process_attachments',1);
252 -- delete the pl/sql tables
253 gt_doc.DELETE;
254 gt_attachdoc.DELETE;
255 gt_lob.DELETE;
256 gt_shorttext.DELETE;
257 --
258 OPEN csr_clob(p_transaction_id);
259 FETCH csr_clob INTO txnClob;
260 CLOSE csr_clob;
261 IF txnClob IS NOT NULL THEN -- process only when there is a non null transaction document.
262 txnXml := hr_transaction_swi.convertCLOBtoXMLElement(txnClob);
263 -- Process the FndDocuments rows and populate the pl/sql table
264 nl := xmldom.getElementsByTagName(txnXml, 'FndDocumentsVlEORow');
265 len1 := xmldom.getLength(nl);
266 -- loop through elements
267 FOR j in 0..len1-1 LOOP
268 n := xmldom.item(nl, j);
269 e := xmldom.makeElement(n);
270 gt_doc(j+1).post_State := xmldom.getAttribute(e, 'PS');
271 gt_doc(j+1).Document_id := hr_transaction_swi.getNumberValue(n,'DocumentId',null);
272 gt_doc(j+1).Created_By := hr_transaction_swi.getNumberValue(n,'CreatedBy',null);
273 gt_doc(j+1).creation_date := hr_transaction_swi.getDateValue(n,'CreationDate',null);
274 gt_doc(j+1).last_update_date := hr_transaction_swi.getDateValue(n,'LastUpdateDate',null);
275 gt_doc(j+1).Last_Updated_By := hr_transaction_swi.getNumberValue(n,'LastUpdatedBy',null);
276 gt_doc(j+1).Last_Update_Login := hr_transaction_swi.getNumberValue(n,'LastUpdateLogin',null);
277 gt_doc(j+1).Datatype_Id := hr_transaction_swi.getNumberValue(n,'DatatypeId',null);
278 gt_doc(j+1).Description := hr_transaction_swi.getVarchar2Value(n,'Description',null);
279 gt_doc(j+1).Media_Id := hr_transaction_swi.getNumberValue(n,'MediaId',null);
280 gt_doc(j+1).Category_Id := hr_transaction_swi.getNumberValue(n,'CategoryId',null);
281 gt_doc(j+1).Security_Type := hr_transaction_swi.getNumberValue(n,'SecurityType',null);
282 gt_doc(j+1).Publish_Flag := hr_transaction_swi.getVarchar2Value(n,'PublishFlag',null);
283 gt_doc(j+1).Usage_Type := hr_transaction_swi.getVarchar2Value(n,'UsageType',null);
284 gt_doc(j+1).Dm_Node := hr_transaction_swi.getNumberValue(n,'DmNode',null);
285 gt_doc(j+1).File_Name := hr_transaction_swi.getVarchar2Value(n,'FileName',null);
286 gt_doc(j+1).Title := hr_transaction_swi.getVarchar2Value(n,'Title',null);
287
288 --dbms_output.put_line('DocumentId'||gt_doc(j+1).Document_id);
289 --dbms_output.put_line('PostState: '||gt_doc(j+1).post_State);
290 END LOOP;
291 hr_utility.trace('Number of documents:'||gt_doc.count);
292 -- Process the FndAttachedDocuments rows and populate the pl/sql table
293 nl := xmldom.getElementsByTagName(txnXml, 'FndAttachedDocumentsEORow');
294 len1 := xmldom.getLength(nl);
295 -- loop through elements
296 FOR j in 0..len1-1 LOOP
297 n := xmldom.item(nl, j);
298 e := xmldom.makeElement(n);
299 gt_attachdoc(j+1).post_State := xmldom.getAttribute(e, 'PS');
300 --dbms_output.put_line('PostState: '||gt_attachdoc(j+1).post_State);
301 gt_attachdoc(j+1).attached_document_id := hr_transaction_swi.getNumberValue(n,'AttachedDocumentId',null);
302 gt_attachdoc(j+1).document_id := hr_transaction_swi.getNumberValue(n,'DocumentId',null);
303 gt_attachdoc(j+1).Creation_Date := hr_transaction_swi.getdateValue(n,'CreationDate',null);
304 gt_attachdoc(j+1).Created_by := hr_transaction_swi.getNumberValue(n,'CreatedBy',null);
305 gt_attachdoc(j+1).Last_Update_Date := hr_transaction_swi.getDateValue(n,'LastUpdateDate',null);
306 gt_attachdoc(j+1).Last_Updated_By := hr_transaction_swi.getNumberValue(n,'LastUpdatedBy',null);
307 gt_attachdoc(j+1).Last_Update_Login := hr_transaction_swi.getNumberValue(n,'LastUpdateLogin',null);
308 gt_attachdoc(j+1).Seq_Num := hr_transaction_swi.getNumberValue(n,'SeqNum',null);
309 gt_attachdoc(j+1).Pk1_Value := hr_transaction_swi.getVarchar2Value(n,'Pk1Value',null);
310 gt_attachdoc(j+1).entity_name := hr_transaction_swi.getVarchar2Value(n,'EntityName',null); gt_attachdoc(j+1).Automatically_Added_Flag := hr_transaction_swi.getVarchar2Value(n,'AutomaticallyAddedFlag',null);
311 gt_attachdoc(j+1).Attachment_Category_Id := hr_transaction_swi.getNumberValue(n,'AttachmentCategoryId',null);
312 --dbms_output.put_line('DocumentId:'||gt_attachdoc(j+1).attached_document_id);
313 END LOOP;
314 --dbms_output.put_line('Number of attached doc entities:'||gt_attachdoc.count);
315 -- Now process all FndDocumentsShortText rows and populate PL/SQL TABLE
316 nl := xmldom.getElementsByTagName(txnXml, 'FndDocumentsShortTextEORow');
317 len1 := xmldom.getLength(nl);
318 -- loop through elements
319 FOR j in 0..len1-1 LOOP
320 n := xmldom.item(nl, j);
321 e := xmldom.makeElement(n);
322 gt_shorttext(j+1).post_State := xmldom.getAttribute(e, 'PS');
323 --dbms_output.put_line('PostState: '||gt_shorttext(j+1).post_State);
324 gt_shorttext(j+1).media_id := hr_transaction_swi.getNumberValue(n,'MediaId',null);
325 gt_shorttext(j+1).short_text := hr_transaction_swi.getVarchar2Value(n,'ShortText',null);
326 gt_shorttext(j+1).app_source_version := hr_transaction_swi.getVarchar2Value(n,'AppSourceVersion',null);
327 --dbms_output.put_line('media_id:'||gt_shorttext(j+1).media_id);
328 END LOOP;
329 --dbms_output.put_line('Number of attached doc entities:'||gt_shorttext.count);
330 -- Now process all the FndLOBs rows and populate the PL/SQL Table
331 nl := xmldom.getElementsByTagName(txnXml, 'FndLobsEORow');
332 len1 := xmldom.getLength(nl);
333 -- loop through elements
334 FOR j in 0..len1-1 LOOP
335 n := xmldom.item(nl, j);
336 e := xmldom.makeElement(n);
337 gt_lob(j+1).post_State := xmldom.getAttribute(e, 'PS');
338 --dbms_output.put_line('PostState: '||gt_lob(j+1).post_State);
339 gt_lob(j+1).file_ID := hr_transaction_swi.getNumberValue(n,'FileId',null);
340 gt_lob(j+1).file_Name := hr_transaction_swi.getVarchar2Value(n,'FileName',null);
341 gt_lob(j+1).file_content_type := hr_transaction_swi.getVarchar2Value(n,'FileContentType',null);
342 gt_lob(j+1).oracle_charset := hr_transaction_swi.getVarchar2Value(n,'OracleCharset',null);
343 --dbms_output.put_line('file name:'||gt_lob(j+1).file_Name );
344 gt_lob(j+1).file_format := hr_transaction_swi.getVarchar2Value(n,'FileFormat',null);
345 dbms_lob.createtemporary(l_clob,true);
346 IF NOT xmldom.isnull(n) THEN
347 xmldom.writeToClob(n,l_clob);
348 END IF;
349 l_filestart := INSTR(l_clob,'<FileData>')+10;
350 l_fileend := INSTR(l_clob,'</FileData>')-1;
351 l_filedata:= SUBSTR(l_clob,l_filestart,(l_fileend-l_filestart)+1);
352 --
353 dbms_lob.createtemporary(lb_filedata,true);
354 --
355 clob_to_blob(l_filedata,lb_filedata);
356 gt_lob(j+1).file_data := lb_filedata;
357 --
358 /* IF gt_lob(j+1).post_State = 0 THEN
359 BEGIN
360 INSERT INTO FND_LOBS
361 (
362 FILE_ID
363 ,FILE_NAME
364 ,FILE_CONTENT_TYPE
365 ,FILE_DATA
366 ,UPLOAD_DATE
367 ,ORACLE_CHARSET
368 ,FILE_FORMAT
369 ) VALUES
370 (
371 gt_lob(j+1).file_ID
372 ,gt_lob(j+1).file_Name
373 ,gt_lob(j+1).file_content_type
374 ,gt_lob(j+1).file_data
375 ,SYSDATE
376 ,gt_lob(j+1).oracle_charset
377 ,gt_lob(j+1).file_format
378 );
379 EXCEPTION WHEN OTHERS THEN
380 --dbms_output.put_line('error inserting:'|| gt_lob(j+1).file_ID);
381 END;
382 --dbms_output.put_line('Insert file with id: '||gt_lob(j+1).file_ID);
383 END IF;
384 */
385 END LOOP;
386 hr_utility.trace('before commit_attachments');
387 commit_attachments;
388 hr_utility.trace('after commit_attachments');
389 END IF;
390 EXCEPTION WHEN OTHERS THEN
391 hr_utility.trace('Error in process_attachments');
392 hr_utility.trace(sqlerrm);
393 END process_attachments;
394
395 --------- END changes for attachments
396
397
398 FUNCTION Split
399 (
400 PC$Chaine IN VARCHAR2, -- input string
401 PN$Pos IN PLS_INTEGER, -- token number
402 PC$Sep IN VARCHAR2 DEFAULT ',' -- separator character
403 )
404 RETURN VARCHAR2
405 IS
406 LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;
407 LI$I PLS_INTEGER ;
408 LI$I2 PLS_INTEGER ;
409 BEGIN
410 LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;
411 IF LI$I > 0 THEN
412 LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;
413 IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF ;
414 RETURN( SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ) ) ;
415 ELSE
416 RETURN NULL ;
417 END IF ;
418 END;
419
420
421
422 procedure update_score_cards(p_score_card_list varchar2 default null,
423 p_sc_ovn_list varchar2 default null,
424 p_sc_latest_ovn_list in out nocopy varchar2)
425 is
426 l_sc_ovn_list varchar2(1000);
427
428 cursor get_score_cards ( p_sc_id varchar2 )
429 is
430 select scorecard_id, object_version_number from per_personal_scorecards
431 where scorecard_id = p_sc_id;
432
433
434 api_return_status varchar2(10);
435 l_temp_ovn number default null;
436 formatted_sc_list varchar2(1000) default null;
437
438 i PLS_INTEGER := 1 ;
439 temp_str varchar2(20);
440 l_sc_id per_personal_scorecards.scorecard_id%TYPE;
441 l_sc_ovn per_personal_scorecards.object_version_number%TYPE;
442 l_sc_latest_ovn per_personal_scorecards.object_version_number%TYPE;
443 l_sc_latest_id per_personal_scorecards.scorecard_id%TYPE;
444
445 begin
446 --l_sc_list := p_score_card_list;
447 --OPEN :sc_cursor FOR select scorecard_id, object_version_number from per_personal_scorecards
448 --where scorecard_id in (p_score_card_list);
449 --dbms_output.put_line( ' p_score_card_list ' || p_score_card_list);
450
451
452 LOOP
453 temp_str := Split(p_score_card_list, i , ',') ;
454 EXIT WHEN temp_str IS NULL ;
455 --dbms_output.put_line(' temp_str ' || temp_str);
456 open get_score_cards(to_number(temp_str));
457 fetch get_score_cards into l_sc_id, l_sc_ovn;
458 close get_score_cards;
459 if( l_sc_id is not null ) then
460 hr_personal_scorecard_swi.update_scorecard_status
461 (p_validate => hr_Api.g_false_num
462 ,p_effective_date => trunc(sysdate) -- to be
463 ,p_scorecard_id => l_sc_id
464 ,p_object_version_number => l_sc_ovn
465 ,p_status_code => 'TRANSFER'
466 ,p_return_status => api_return_status
467 );
468
469 open get_score_cards(to_number(temp_str));
470 fetch get_score_cards into l_sc_latest_id, l_sc_latest_ovn;
471 close get_score_cards;
472
473 if(p_sc_latest_ovn_list is null or length(p_sc_latest_ovn_list) <= 0) then
474 p_sc_latest_ovn_list := p_sc_latest_ovn_list || l_sc_latest_ovn;
475 else
476 p_sc_latest_ovn_list := p_sc_latest_ovn_list || ',' || l_sc_latest_ovn;
477 end if;
478 end if;
479 i := i + 1 ;
480 END LOOP ;
481 /*
482 open format_sc_list(p_score_card_list);
483 fetch format_sc_list into formatted_sc_list;
484 close format_sc_list;
485
486 formatted_sc_list := '''' || formatted_sc_list || '''';
487
488 --dbms_output.put_line(' formatted_sc_list ' || formatted_sc_list);
489
490
491 for score_card_list in get_score_cards(p_score_card_list)
492 loop
493 begin
494 l_temp_ovn := score_card_list.object_version_number;
495 api_return_status := null;
496
497 --dbms_output.put_line( ' processing score_card_list.scorecard_id ');
498 hr_personal_scorecard_swi.update_scorecard_status
499 (p_validate => hr_Api.g_false_num
500 ,p_effective_date => trunc(sysdate) -- to be
501 ,p_scorecard_id => score_card_list.scorecard_id
502 ,p_object_version_number => l_temp_ovn
503 ,p_status_code => 'TRANSFER'
504 ,p_return_status => api_return_status
505 );
506
507 --dbms_output.put_line( ' api_return_status ' || api_return_status);
508
509 if(p_sc_latest_ovn_list is null or length(p_sc_latest_ovn_list) <= 0) then
510 p_sc_latest_ovn_list := p_sc_latest_ovn_list || l_temp_ovn;
511 else
512 p_sc_latest_ovn_list := p_sc_latest_ovn_list || ',' || l_temp_ovn;
513 end if;
514
515 exception when others then
516 raise;
517 -- to be
518
519 end;
520 end loop;
521
522 */
523 exception when others then
524 --dbms_output.put_line(' ERROR ' || sqlerrm || sqlcode);
525 raise;
526
527 end update_score_cards;
528
529 /* txn_owner_person_id is the actual owner of the txn, this is needed as
530 there is switch functionality where the HR rep can perform the txn, in this
531 case it is decided that the whole process as to use the actual manager rather
532 than who is acting */
533
534 PROCEDURE MassScoreCardTransfer
535 ( score_card_list IN VARCHAR2 DEFAULT null,
536 sc_ovn_list IN VARCHAR2 DEFAULT null,
537 txn_owner_person_id in per_all_people_f.person_id%TYPE,
538 comments in varchar2,
539 result_code out nocopy VARCHAR2 )
540 IS
541 cursor get_person_name(p_person_id per_all_people_f.person_id%TYPE)
542 is
543 select global_name from per_people_f
544 where person_id = p_person_id
545 and trunc(sysdate) between effective_start_date and effective_end_date;
546
547 cursor get_wf_role(p_person_id per_all_people_f.person_id%TYPE)
548 is
549 select name from wf_roles
550 where orig_system_id = p_person_id
551 and orig_system = 'PER';
552
553 item_key_number number;
554 item_key hr_api_transactions.item_key%type default '';
555 item_type hr_api_transactions.item_type%type default 'HRWPM';
556 mgr_name per_all_people_f.global_name%TYPE;
557 mgr_role wf_local_roles.name%TYPE;
558
559 l_sc_latest_ovn_list varchar2(1000) default null;
560 BEGIN
561
562 select hr_workflow_item_key_s.NEXTVAL into item_key_number from dual ;
563 item_key := item_key || item_key_number;
564 --dbms_output.put_line(' item_key = ' || item_key);
565
566 open get_wf_role(txn_owner_person_id);
567 fetch get_wf_role into mgr_role;
568 close get_wf_role;
569
570 open get_person_name(txn_owner_person_id);
571 fetch get_person_name into mgr_name;
572 close get_person_name;
573
574 savepoint start_process;
575
576 update_score_cards(score_card_list, sc_ovn_list, l_sc_latest_ovn_list);
577
578 --dbms_output.put_line(' l_sc_latest_ovn_list ' || l_sc_latest_ovn_list);
579
580 --dbms_output.put_line(' mgr_role ' || mgr_role || ' mgr_name ' || mgr_name);
581 wf_engine.CreateProcess (itemtype => item_type,
582 itemkey => item_key,
583 process => 'MASS_SCORE_CARD_TRANSFER',
584 user_key => 'Mass Score Card Transfer',
585 owner_role => mgr_role);
586 -- owner_role => fnd_global.user_name);
587
588 --dbms_output.put_line(' Created Process ' );
589
590 --- to be substituted
591 wf_engine.setitemattrtext(item_type,item_key,SC_LIST_WF_ATTR_NAME,score_card_list);
592 wf_engine.setitemattrtext(item_type,item_key,SC_OVN_LIST_WF_ATTR_NAME,l_sc_latest_ovn_list);
593 -- wf_engine.setitemattrtext(item_type,item_key,'HR_WPM_MASS_SC_TRNSF_PERFORMER',fnd_global.user_name);
594 wf_engine.SetItemAttrNumber(item_type,item_key,'HR_WPM_TXN_OWNER_PERSON_ID',txn_owner_person_id);
595 wf_engine.setitemattrtext(item_type,item_key,'HR_WPM_MASS_SC_TRNSF_PERFORMER',mgr_role);
596 wf_engine.setitemattrtext(item_type,item_key,'HR_WPM_MGR_NAME',mgr_name);
597 wf_engine.setitemattrtext(item_type,item_key,'HR_WPM_MASS_TRNSF_COMMENTS',comments);
598 -- As we could not use ScoreCardHeaderCO because of encryption problem
599 -- we have to differentiate the caller whether it is from Mass Score Card Process or
600 -- normal.
601 wf_engine.setitemattrtext(item_type,item_key,'HR_WPM_SC_SOURCE_TYPE','WF');
602
603
604 wf_engine.StartProcess (itemtype => item_type,
605 itemkey => item_key );
606
607 --dbms_output.put_line(' Start Process ' );
608
609 result_code := 'S';
610 --dbms_output.put_line(' Launched the Process ' || result_code);
611
612 -- to be removed when integrated
613 --commit;
614
615 EXCEPTION
616 WHEN others THEN
617 --dbms_output.put_line(' Failed to start the process ');
618 rollback to start_process;
619 --dbms_output.put_line( 'Exception in this procedure' || sqlcode || sqlerrm );
620 result_code := 'F';
621 raise ;
622 END;
623
624
625
626
627 PROCEDURE Defer(itemtype in varchar2,
628 itemkey in varchar2,
629 actid in number,
630 funcmode in varchar2,
631 resultout out nocopy varchar2)
632 IS
633 BEGIN
634 -- to be added for profile check
635 if (funcmode = 'RUN') then
636 resultout :='COMPLETE:N';
637 end if;
638
639 EXCEPTION
640 WHEN others THEN
641 raise;
642 END;
643
644 PROCEDURE IS_FINAL_SCORE_CARD (itemtype in varchar2,
645 itemkey in varchar2,
646 actid in number,
647 funcmode in varchar2,
648 resultout out nocopy varchar2)
649 IS
650 score_card_list varchar2(1000);
651 processed_score_card_list varchar2(1000);
652 log_message varchar2(1000) default null;
653 BEGIN
654 if (funcmode = 'RUN') then
655
656 score_card_list := wf_engine.getitemattrtext(itemtype,itemkey,SC_LIST_WF_ATTR_NAME,false);
657 processed_score_card_list := wf_engine.getitemattrtext(itemtype,itemkey,SC_PROCESSED_LIST_WF_ATTR_NAME,true);
658
659 if(score_card_list = processed_score_card_list) then
660 resultout :='COMPLETE:Y';
661 else
662 resultout :='COMPLETE:N';
663 end if;
664
665
666
667 end if;
668 EXCEPTION
669 WHEN others THEN
670 raise;
671 END;
672
673
674 PROCEDURE FAILED_SCORE_CARDS (itemtype in varchar2,
675 itemkey in varchar2,
676 actid in number,
677 funcmode in varchar2,
678 resultout out nocopy varchar2)
679 IS
680 sc_error_list varchar2(1000);
681 BEGIN
682 if (funcmode = 'RUN') then
683 sc_error_list := wf_engine.getitemattrtext(itemtype,itemkey,'HR_WPM_SC_ERROR_LIST',true);
684 if( sc_error_list is not null and length(sc_error_list) > 0)
685 then
686 resultout :='COMPLETE:Y';
687 else
688 resultout :='COMPLETE:N';
689 end if;
690 end if;
691 EXCEPTION
692 WHEN others THEN
693 raise;
694 END;
695
696 PROCEDURE TEST_ACTIVITY (itemtype in varchar2,
697 itemkey in varchar2,
698 actid in number,
699 funcmode in varchar2,
700 resultout out nocopy varchar2)
701 IS
702 BEGIN
703 if (funcmode = 'RUN') then
704 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_LOG_MESSAGES','TEST_ACTIVITY');
705 -- resultout :='COMPLETE:Y';
706 end if;
707 EXCEPTION
708 WHEN others THEN
709 raise;
710 END;
711
712
713 PROCEDURE PROCESS_SCORE_CARD (itemtype in varchar2,
714 itemkey in varchar2,
715 actid in number,
716 funcmode in varchar2,
717 resultout out nocopy varchar2)
718 IS
719
720 cursor get_score_card_role(p_score_card_id in number)
721 IS
722 select wf.name wf_role, people.global_name emp_name, sc.scorecard_name, sc.plan_id
723 from per_personal_scorecards sc, per_all_assignments_f asgn,
724 per_all_people_f people, wf_roles wf
725 where sc.scorecard_id = p_score_card_id
726 and asgn.assignment_id = sc.assignment_id
727 and trunc(sysdate) between asgn.effective_start_date and asgn.effective_end_date
728 and asgn.person_id = people.person_id
729 and wf.orig_system_id = people.person_id
730 and wf.orig_system = 'PER'
731 and trunc(sysdate) between people.effective_start_date and people.effective_end_date;
732 -- to be see that the cursor retreives only one id
733 cursor get_score_card_txn(p_score_card_id number, txn_owner per_all_people_f.person_id%TYPE)
734 IS
735 select transaction_id from hr_api_transactions
736 where transaction_ref_id = p_score_card_id
737 and transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
738 and creator_person_id = txn_owner;
739
740 -- cursor to get the ovn for a scorecardId.
741 cursor get_score_cards ( p_sc_id varchar2 )
742 is
743 select object_version_number from per_personal_scorecards
744 where scorecard_id = p_sc_id;
745 -- to be sorted with the lenght
746 score_card_list varchar2(1000);
747 SC_OVN_LIST varchar2(1000);
748 processed_score_card_list varchar2(1000);
749 processed_sc_ovn_list varchar2(1000);
750 processed_error_sc_list varchar2(1000);
751 processed_succ_sc_list varchar2(1000);
752 next_score_card varchar2(20) default null;
753 next_sc_ovn varchar2(20) default null;
754 api_return_status varchar2(20) default null;
755 api_return_ovn number default null;
756 score_card_wf_role wf_local_roles.name%TYPE;
757 log_message varchar2(5000) default null;
758 score_card_performer wf_local_roles.name%TYPE;
759 score_card_emp_name per_all_people_f.global_name%TYPE;
760 score_card_txn_id number default null;
761 l_error_log varchar2(20000);
762 score_card_name per_personal_scorecards.scorecard_name%TYPE;
763 txn_owner_person_id per_all_people_f.person_id%TYPE;
764 temp varchar2(2000);
765 l_duplsicate_name_warning boolean;
766 l_proc varchar2(72) := g_package || 'PROCESS_SCORE_CARD';
767 l_score_card_plan_id per_personal_scorecards.plan_id%TYPE;
768
769
770 BEGIN
771
772
773 if (funcmode = 'RUN') then
774 score_card_list := wf_engine.getitemattrtext(itemtype,itemkey,SC_LIST_WF_ATTR_NAME,false);
775 SC_OVN_LIST := wf_engine.getitemattrtext(itemtype,itemkey,SC_OVN_LIST_WF_ATTR_NAME,false);
776
777 processed_score_card_list := wf_engine.getitemattrtext(itemtype,itemkey,SC_PROCESSED_LIST_WF_ATTR_NAME,true);
778 processed_sc_ovn_list := wf_engine.getitemattrtext(itemtype,itemkey,SC_OVNS_PROCESSED_WF_ATTR_NAME,true);
779 processed_error_sc_list := wf_engine.getitemattrtext(itemtype,itemkey,'HR_WPM_SC_ERROR_LIST',true);
780 txn_owner_person_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'HR_WPM_TXN_OWNER_PERSON_ID',true);
781
782 processed_succ_sc_list := wf_engine.getitemattrtext(itemtype,itemkey,'HR_WPM_MASS_SC_SUCC_LIST',true);
783
784 if( length(score_card_list) > nvl(length(processed_score_card_list),0) ) then
785 if( processed_score_card_list is null or processed_score_card_list = '') then
786 next_score_card := Split( score_card_list, 1 , ',') ;
787 --next_sc_ovn := Split( SC_OVN_LIST, 1 , ',') ;
788 -- get the latest ovn from db rather than reading from list as the list not used everywhere consistently
789 open get_score_cards (next_score_card);
790 fetch get_score_cards into next_sc_ovn;
791 close get_score_cards;
792
793 else
794 next_score_card := Split( substr(score_card_list, length(processed_score_card_list) ) ,2, ',');
795 -- next_sc_ovn := Split( substr(SC_OVN_LIST, length(processed_sc_ovn_list) ) ,2, ',');
796 -- get the latest ovn from db rather than reading from list as the list not used everywhere consistently
797 open get_score_cards (next_score_card);
798 fetch get_score_cards into next_sc_ovn;
799 close get_score_cards;
800 end if;
801
802 if( length(next_score_card)>0 and instr(next_score_card,',',1) = 0) then
803 begin
804 -- 8233647 Bug Fix
805 open get_score_card_txn(to_number(next_score_card),txn_owner_person_id);
806 fetch get_score_card_txn into score_card_txn_id;
807 close get_score_card_txn;
808
809 open get_score_card_role(to_number(next_score_card));
810 fetch get_score_card_role into score_card_performer, score_card_emp_name, score_card_name, l_score_card_plan_id;
811 close get_score_card_role;
812
813 log_message := wf_engine.getitemattrtext(itemtype,itemkey,'SC_ERROR',true);
814
815 savepoint start_process_scorecard;
816
817 if( score_card_txn_id is null) then
818 hr_personal_scorecard_swi.update_scorecard_status
819 (p_validate => hr_Api.g_false_num
820 ,p_effective_date => trunc(sysdate) -- to be
821 ,p_scorecard_id => to_number(next_score_card)
822 ,p_object_version_number => next_sc_ovn
823 ,p_status_code => 'WKR'
824 ,p_return_status => api_return_status
825 );
826 else
827
828 /*
829 api_return_status := hr_transaction_swi.commit_transaction_tree(
830 p_transaction_id => score_card_txn_id,
831 p_validate => 0,
832 p_error_log => l_error_log);
833 */
834
835 api_return_status := hr_transaction_swi.commit_transaction(
836 p_transaction_id => score_card_txn_id,
837 p_validate => hr_Api.g_false_num);
838 end if;
839
840
841 --dbms_output.put_line(' *** api_return_status *** ' || api_return_status);
842 --dbms_output.put_line(' *** TXN LOG *** ' || l_error_log);
843
844 -- as error_log column has 4000 length we need to truncate
845 if(length(l_error_log) > 3800) then
846 hr_utility.set_location(' Error Log Truncated ' || l_proc,50);
847 l_error_log := substr(l_error_log,1,3800);
848 end if;
849
850 if(api_return_status = 'E') then
851 for i in 1 .. fnd_msg_pub.count_msg Loop
852 l_error_log := l_error_log || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
853 end loop;
854
855 log_message := log_message || l_error_log;
856
857 if ( api_return_status = 'E' ) then
858 rollback to start_process_scorecard;
859
860 hr_personal_scorecard_swi.update_scorecard_status
861 (p_validate => hr_Api.g_false_num
862 ,p_effective_date => trunc(sysdate) -- to be
863 ,p_scorecard_id => to_number(next_score_card)
864 ,p_object_version_number => next_sc_ovn
865 ,p_status_code => 'ERROR'
866 ,p_return_status => api_return_status
867 );
868 end if;
869
870 fnd_msg_pub.Delete_Msg;
871
872 -- to be changed once the error_log column is added
873 --update per_personal_scorecards
874 --set error_log = l_error_log
875 --where scorecard_id = next_score_card;
876 per_pms_upd.upd(p_effective_date => trunc(sysdate),
877 p_scorecard_id => to_number(next_score_card),
878 p_object_version_number => next_sc_ovn,
879 p_error_log => l_error_log,
880 p_duplicate_name_warning => l_duplsicate_name_warning);
881
882 wf_engine.setitemattrtext(itemtype,itemkey,'SC_ERROR' , log_message || 'SCID='||next_score_card || 'api_return_status=' || api_return_status);
883
884 --dbms_output.put_line(' Putting the attributes ');
885 wf_engine.setitemattrtext(itemtype,itemkey,'SC_EMP_NAME',score_card_emp_name);
886 wf_engine.setitemattrtext(itemtype,itemkey,'HR_MASS_TRNSF_PLAN_ID',''||l_score_card_plan_id);
887 wf_engine.setitemattrtext(itemtype,itemkey,'HR_REL_APPS_ACTION_TYPE','EMP_TO_MGR');
888
889 if( processed_error_sc_list is not null and length(processed_error_sc_list) > 0)
890 then
891 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_SC_ERROR_LIST',processed_error_sc_list || ',' || next_score_card);
892 else
893 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_SC_ERROR_LIST',next_score_card);
894 end if;
895
896 if(length(processed_score_card_list) is null or length(processed_score_card_list) = 0) then
897 wf_engine.setitemattrtext(itemtype,itemkey,SC_PROCESSED_LIST_WF_ATTR_NAME, next_score_card);
898 wf_engine.setitemattrtext(itemtype,itemkey,SC_OVNS_PROCESSED_WF_ATTR_NAME, next_sc_ovn);
899 else
900 wf_engine.setitemattrtext(itemtype,itemkey,SC_PROCESSED_LIST_WF_ATTR_NAME,processed_score_card_list || ',' ||next_score_card);
901 wf_engine.setitemattrtext(itemtype,itemkey,SC_OVNS_PROCESSED_WF_ATTR_NAME,processed_sc_ovn_list || ',' ||next_sc_ovn);
902 end if;
903 --dbms_output.put_line(' done attributes ');
904
905 resultout :='COMPLETE:FAIL';
906 elsif ( api_return_status = 'S' or api_return_status = 'W') then
907
908 if( api_return_status = 'W' ) then
909 for i in 1 .. fnd_msg_pub.count_msg Loop
910 log_message := log_message || l_error_log || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
911 end loop;
912
913 fnd_msg_pub.Delete_Msg;
914
915 --update per_personal_scorecards
916 --set error_log = l_error_log
917 --where scorecard_id = next_score_card;
918 end if;
919
920 begin
921
922 hr_personal_scorecard_swi.update_scorecard_status
923 (p_validate => hr_Api.g_false_num
924 ,p_effective_date => trunc(sysdate) -- to be
925 ,p_scorecard_id => to_number(next_score_card)
926 ,p_object_version_number => next_sc_ovn
927 ,p_status_code => 'WKR'
928 ,p_return_status => api_return_status
929 );
930 begin
931 hr_utility.trace('Commiting attachments for scorecard:'||to_number(next_score_card));
932 process_attachments(p_transaction_id => score_card_txn_id);
933 hr_utility.trace('Completed Commiting attachments for scorecard:'||to_number(next_score_card));
934 exception when others then
935 hr_utility.trace('ERROR Commiting attachments for scorecard:'||to_number(next_score_card));
936 RAISE;
937 end;
938 hr_utility.trace('success with commit_attachments');
939
940 per_pms_upd.upd(p_effective_date => trunc(sysdate),
941 p_scorecard_id => to_number(next_score_card),
942 p_object_version_number => next_sc_ovn,
943 p_error_log => l_error_log,
944 p_duplicate_name_warning => l_duplsicate_name_warning);
945
946 hr_transaction_swi.delete_transaction(score_card_txn_id,hr_Api.g_false_num);
947
948 exception when others then
949
950 for i in 1 .. fnd_msg_pub.count_msg Loop
951 l_error_log := l_error_log || fnd_msg_pub.get(p_msg_index => I, p_encoded => 'F');
952 end loop;
953
954 temp := wf_engine.getitemattrtext(itemtype,itemkey,'SC_ERROR', true);
955 wf_engine.setitemattrtext(itemtype,itemkey,'SC_ERROR', temp || l_error_log);
956 end;
957
958 --dbms_output.put_line(' putting attributes ');
959
960 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_SC_NAME',score_card_name);
961 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_EMP_SC_ID',next_score_card);
962 wf_engine.setitemattrtext(itemtype,itemkey,'HR_MASS_TRNSF_PLAN_ID',''||l_score_card_plan_id);
963 wf_engine.setitemattrtext(itemtype,itemkey,'HR_REL_APPS_ACTION_TYPE','MGR_TO_EMP');
964
965 if( processed_succ_sc_list is not null and length(processed_succ_sc_list) > 0)
966 then
967 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_MASS_SC_SUCC_LIST',processed_succ_sc_list || ',' || next_score_card);
968 else
969 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_MASS_SC_SUCC_LIST',next_score_card);
970 end if;
971
972 resultout :='COMPLETE:SUCCESS';
973 end if;
974
975 if(length(processed_score_card_list) is null or length(processed_score_card_list) = 0) then
976 wf_engine.setitemattrtext(itemtype,itemkey,SC_PROCESSED_LIST_WF_ATTR_NAME, next_score_card);
977 wf_engine.setitemattrtext(itemtype,itemkey,SC_OVNS_PROCESSED_WF_ATTR_NAME, next_sc_ovn);
978 else
979 wf_engine.setitemattrtext(itemtype,itemkey,SC_PROCESSED_LIST_WF_ATTR_NAME,processed_score_card_list || ',' ||next_score_card);
980 wf_engine.setitemattrtext(itemtype,itemkey,SC_OVNS_PROCESSED_WF_ATTR_NAME,processed_sc_ovn_list || ',' ||next_sc_ovn);
981 end if;
982
983 if( processed_succ_sc_list is not null and length(processed_succ_sc_list) > 0)
984 then
985 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_MASS_SC_SUCC_LIST',processed_succ_sc_list || ',' || next_score_card);
986 else
987 wf_engine.setitemattrtext(itemtype,itemkey,'HR_WPM_MASS_SC_SUCC_LIST',next_score_card);
988 end if;
989
990 wf_engine.setitemattrtext(itemtype,itemkey,SC_PERFORMER_WF_ATTR_NAME,score_card_performer);
991
992 exception
993 when others then
994 log_message := log_message || sqlerrm || sqlcode;
995 --dbms_output.put_line(' log_message ' || log_message);
996 wf_engine.setitemattrtext(itemtype,itemkey,'SC_ERROR',log_message);
997 wf_engine.setitemattrtext(itemtype,itemkey,'SC_EMP_NAME',score_card_emp_name);
998 resultout :='COMPLETE:FAIL';
999 end;
1000
1001 end if;
1002
1003 end if;
1004
1005 end if;
1006
1007 END;
1008
1009
1010 procedure SEND_NTF(itemtype in varchar2,
1011 itemkey in varchar2,
1012 actid in number,
1013 funcmode in varchar2,
1014 resultout in out nocopy varchar2)
1015 is
1016 prole wf_users.name%type; -- Fix 3210283.
1017 expand_role varchar2(1);
1018
1019 begin
1020 if (funcmode <> wf_engine.eng_run) then
1021 resultout := wf_engine.eng_null;
1022 return;
1023 end if;
1024
1025 prole := wf_engine.GetActivityAttrText(
1026 itemtype => itemtype,
1027 itemkey => itemkey,
1028 actid => actid,
1029 aname => 'PERFORMER');
1030
1031 expand_role := nvl(Wf_Engine.GetActivityAttrText(itemtype, itemkey,
1032 actid, 'EXPANDROLES'),'N');
1033
1034
1035
1036 if prole is null then
1037 Wf_Core.Token('TYPE', itemtype);
1038 Wf_Core.Token('ACTID', to_char(actid));
1039 Wf_Core.Raise('WFENG_NOTIFICATION_PERFORMER');
1040 end if;
1041
1042 Wf_Engine_Util.Notification_Send(itemtype, itemkey, actid,
1043 'HR_WPM_SC_TRNSF_SUCC', 'HRWPM', prole, expand_role,
1044 resultout);
1045
1046
1047 exception when others then
1048 -- 8774941 bug fix
1049 -- raise;
1050 resultout := '#NULL';
1051 end;
1052
1053
1054 END HR_WPM_MASS_SCORE_CARD_TRNSF; -- Package spec
1055