[Home] [Help]
PACKAGE BODY: APPS.PO_EDA_PKG
Source
1 PACKAGE BODY PO_EDA_PKG AS
2 /* $Header: PO_EDA_PKG.plb 120.2.12020000.2 2013/02/21 15:36:07 harchand noship $ */
3
4 FUNCTION get_lookup_value (p_lookup_code VARCHAR2) RETURN VARCHAR2 IS
5
6 CURSOR c_get_lookup_value IS
7 SELECT meaning
8 FROM fnd_lookups
9 WHERE lookup_type = 'PO_EDA_COMPLETION_STATUS'
10 AND (SYSDATE BETWEEN start_date_active AND Nvl(end_date_active,SYSDATE+1))
11 AND lookup_code = p_lookup_code ;
12
13 l_lookup_value VARCHAR2(30):= NULL ;
14
15 BEGIN
16
17 OPEN c_get_lookup_value;
18 FETCH c_get_lookup_value INTO l_lookup_value;
19 CLOSE c_get_lookup_value;
20
21 RETURN l_lookup_value;
22
23 END get_lookup_value;
24
25
26 PROCEDURE VALIDATE_PDS (
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2,
30 p_document_type IN VARCHAR2,
31 p_document_id IN NUMBER,
32 p_draft_id IN NUMBER,
33 p_doc_type_class IN VARCHAR2
34 )
35
36 IS
37
38
39
40 l_event_key number;
41 l_event_data clob;
42 l_event_name varchar2(250);
43
44 ParamList wf_parameter_list_t := wf_parameter_list_t();
45 l_param wf_parameter_t;
46 l_parameter_index NUMBER := 0;
47 l_message VARCHAR2(500);
48
49 l_base_or_mod VARCHAR2(30);
50 l_clm_document_number VARCHAR2(50);
51
52
53 CURSOR c_get_clm_number IS
54 SELECT clm_document_number
55 FROM po_headers_all
56 WHERE po_header_id = p_document_id;
57
58 CURSOR c_get_modification_number IS
59 SELECT d.modification_number
60 FROM po_headers_draft_all h, po_drafts d
61 WHERE d.document_id = p_document_id
62 AND d.draft_id = p_draft_id
63 AND d.document_id = h.po_header_id
64 AND d.draft_id = h.draft_id;
65
66 l_lookup_code VARCHAR2(30);
67
68 l_schemaversionused VARCHAR2(30);
69 l_systemadministratordodaac VARCHAR2(30);
70 l_datatemplatecode VARCHAR2(30);
71 l_ditprnumber NUMBER ;
72 l_doc_purpose VARCHAR2(30);
73 l_system_name VARCHAR2(30);
74
75 CURSOR conc_pgm_params IS
76 SELECT END_USER_COLUMN_NAME,DEFAULT_VALUE
77 FROM FND_DESCR_FLEX_COL_USAGE_VL
78 WHERE (APPLICATION_ID=201)
79 and (DESCRIPTIVE_FLEXFIELD_NAME='$SRS$.POEDAFILGEN')
80 order by column_seq_num;
81
82 l_req_id NUMBER;
83
84 CURSOR c_req_completion_text IS
85 SELECT completion_text
86 FROM FND_CONC_REQ_SUMMARY_V
87 WHERE request_id = l_req_id;
88
89 l_completion_text VARCHAR2(500);
90
91 BEGIN
92 /*
93 SELECT po_eda_evnt_s.NEXTVAL INTO l_event_key FROM dual;
94 l_event_key := 'EDA'||l_event_key;
95 l_event_name := 'oracle.apps.po.integration.eda';
96 l_message := wf_event.test(l_event_name);
97
98 l_event_data := gen_xml_payload(p_document_type => p_document_type,
99 p_document_id => p_document_id,
100 p_draft_id => p_draft_id,
101 p_doc_type_class=> p_doc_type_class,
102 p_supplier_name => c.supplier_name ,
103 p_vendor_site_code => c.vendor_site_code,
104 p_duns_number => c.duns_number,
105 p_tin_number => c.tin_number
106 );
107 wf_event.AddParameterToList( p_name => 'PO_EDA_DOC_ID'
108 , p_value => p_document_id
109 , p_parameterList => ParamList );
110
111 wf_event.AddParameterToList( p_name => 'PO_EDA_DOC_TYPE'
112 , p_value => p_document_type
113 , p_parameterList => ParamList );
114
115 wf_event.AddParameterToList( p_name => 'PO_EDA_DRAFT_ID'
116 , p_value => p_draft_id
117 , p_parameterList => ParamList );
118
119 wf_event.Raise( p_event_name => l_event_name
120 , p_event_key => l_event_key
121 , p_parameters => ParamList );
122
123 ParamList.DELETE;
124
125
126 COMMIT;
127 */
128
129 IF p_draft_id = -1 THEN
130 l_base_or_mod := 'Base Documents';
131 OPEN c_get_clm_number;
132 FETCH c_get_clm_number INTO l_clm_document_number;
133 CLOSE c_get_clm_number;
134 ELSE
135 l_base_or_mod := 'Modifications';
136 OPEN c_get_modification_number;
137 FETCH c_get_modification_number INTO l_clm_document_number;
138 CLOSE c_get_modification_number;
139
140 END IF;
141
142 FOR cparam IN conc_pgm_params LOOP
143 IF cparam.END_USER_COLUMN_NAME = 'p_schemaversionused' THEN
144 l_schemaversionused := cparam.DEFAULT_VALUE;
145 END IF;
146 IF cparam.END_USER_COLUMN_NAME = 'p_systemadministratordodaac' THEN
147 l_systemadministratordodaac := cparam.DEFAULT_VALUE;
148 END IF;
149 IF cparam.END_USER_COLUMN_NAME = 'p_datatemplatecode' THEN
150 l_datatemplatecode := cparam.DEFAULT_VALUE;
151 END IF;
152 IF cparam.END_USER_COLUMN_NAME = 'p_ditprnumber' THEN
153 l_ditprnumber := cparam.DEFAULT_VALUE;
154 END IF;
155 IF cparam.END_USER_COLUMN_NAME = 'p_doc_purpose' THEN
156 l_doc_purpose := cparam.DEFAULT_VALUE;
157 END IF;
158 IF cparam.END_USER_COLUMN_NAME = 'p_system_name' THEN
159 l_system_name := cparam.DEFAULT_VALUE;
160 END IF;
161
162 END LOOP;
163
164 l_req_id := fnd_request.submit_request
165 (application => 'PO',
166 program => 'POEDAFILGEN',
167 description => NULL,
168 start_time => NULL,
169 sub_request => TRUE,
170 argument1 => l_base_or_mod,
171 argument2 => l_clm_document_number,
172 argument3 => NULL,
173 argument4 => NULL,
174 argument5 => NULL,
175 argument6 => NULL,
176 argument7 => NULL,
177 argument8 => NULL,
178 argument9 => NULL,
179 argument10 => l_schemaversionused,
180 argument11 => l_systemadministratordodaac,
181 argument12 => l_datatemplatecode,
182 argument13 => l_ditprnumber,
183 argument14 => l_doc_purpose,
184 argument15 => l_system_name
185 );
186 COMMIT;
187
188 l_lookup_code:=get_lookup_value('SUBMITTED');
189
190 OPEN c_req_completion_text;
191 FETCH c_req_completion_text INTO l_completion_text;
192 CLOSE c_req_completion_text;
193
194 l_completion_text:= 'Request Id:'||l_req_id||' '||l_completion_text;
195 INSERT INTO po_electronic_data_access
196 (
197 id ,
198 doc_id ,
199 doc_type ,
200 draft_id ,
201 performed_by ,
202 request_date ,
203 response_date ,
204 status ,
205 response ,
206 approved_without_check ,
207 reason ,
208 history_record ,
209 request_id ,
210 last_update_date ,
211 last_updated_by ,
212 creation_date ,
213 created_by ,
214 last_update_login
215 )
216 VALUES (po_eda_s.NEXTVAL,
217 p_document_id,
218 p_document_type,
219 p_draft_id,
220 Fnd_Global.User_Id,
221 SYSDATE,
222 SYSDATE,
223 l_lookup_code,
224 l_completion_text,
225 NULL,
226 NULL,
227 'Y',
228 l_req_id,
229 SYSDATE,
230 Fnd_Global.User_Id,
231 SYSDATE,
232 Fnd_Global.User_Id,
233 Fnd_Global.Login_Id);
234
235 COMMIT;
236
237
238 EXCEPTION WHEN OTHERS THEN
239 NULL;
240 END VALIDATE_PDS;
241
242
243 PROCEDURE INIT_CURRENT_ROW (
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_document_type IN VARCHAR2,
248 p_document_id IN NUMBER,
249 p_draft_id IN NUMBER,
250 p_doc_type_class IN VARCHAR2
251 )
252
253 IS
254
255 l_curr_row_exists VARCHAR2(1):='N';
256 CURSOR c_get_current_row IS
257 SELECT 'Y'
258 FROM po_electronic_data_access
259 WHERE doc_id = p_document_id
260 AND doc_type = p_document_type
261 AND Nvl(draft_id,-1) = Nvl(p_draft_id,-1)
262 AND history_record = 'N';
263
264 BEGIN
265
266 OPEN c_get_current_row;
267 FETCH c_get_current_row INTO l_curr_row_exists;
268 CLOSE c_get_current_row;
269
270 IF l_curr_row_exists = 'N' THEN
271
272 INSERT INTO po_electronic_data_access
273 (
274 id ,
275 doc_id ,
276 doc_type ,
277 draft_id ,
278 performed_by ,
279 request_date ,
280 response_date ,
281 status ,
282 response ,
283 approved_without_check ,
284 reason ,
285 history_record ,
286 request_id ,
287 last_update_date ,
288 last_updated_by ,
289 creation_date ,
290 created_by ,
291 last_update_login
292 )
293 VALUES (po_eda_s.NEXTVAL,
294 p_document_id,
295 p_document_type,
296 p_draft_id,
297 1020000,
298 SYSDATE,
299 SYSDATE,
300 NULL,
301 NULL,
302 NULL,
303 NULL,
304 'N',
305 NULL,
306 SYSDATE,
307 1020000,
308 SYSDATE,
309 1020000,
310 1);
311
312
313 END IF;
314
315
316 COMMIT;
317
318 EXCEPTION WHEN OTHERS THEN
319 NULL;
320 END INIT_CURRENT_ROW;
321
322
323 PROCEDURE REFRESH_DETAILS (
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 p_document_type IN VARCHAR2,
328 p_document_id IN NUMBER,
329 p_draft_id IN NUMBER,
330 p_doc_type_class IN VARCHAR2
331 )
332
333 IS
334
335
336 CURSOR c_get_history_rows IS
337 SELECT id,request_id
338 FROM po_electronic_data_access
339 WHERE doc_id = p_document_id
340 AND doc_type = p_document_type
341 AND Nvl(draft_id,-1) = Nvl(p_draft_id,-1)
342 AND history_record = 'Y';
343
344
345 CURSOR c_request_status( cp_request_id NUMBER) IS
346 SELECT phase_code, status_code, completion_text
347 FROM fnd_concurrent_requests
348 WHERE request_id = cp_request_id;
349
350 l_phase_code VARCHAR2(10);
351 l_status_code VARCHAR2(10);
352 l_completion_text VARCHAR2(240);
353 l_lookup_code_complete VARCHAR2(30);
354 l_lookup_code_fail VARCHAR2(30);
355
356 l_validate_pds_status VARCHAR2(1);
357 BEGIN
358
359 FOR c IN c_get_history_rows LOOP
360
361 OPEN c_request_status(c.request_id);
362 FETCH c_request_status INTO l_phase_code, l_status_code, l_completion_text;
363 CLOSE c_request_status;
364
365 l_lookup_code_complete:=get_lookup_value('COMPLETE');
366 l_lookup_code_fail:=get_lookup_value('FAIL');
367
368 IF l_phase_code = 'C' THEN
369 UPDATE po_electronic_data_access
370 SET status = Decode(l_status_code,'C',l_lookup_code_complete,'I',l_lookup_code_complete,'R',l_lookup_code_complete,l_lookup_code_fail),
371 response = l_completion_text
372 WHERE id = c.id;
373 END IF;
374
375 END LOOP;
376
377 COMMIT;
378
379 EXCEPTION WHEN OTHERS THEN
380 NULL;
381 END REFRESH_DETAILS;
382
383 FUNCTION po_eda_receive(
384 p_subscription_guid IN RAW
385 ,p_event IN OUT NOCOPY wf_event_t)
386 RETURN VARCHAR2 IS
387 l_doc_id NUMBER;
388 l_doc_type VARCHAR2(30);
389 l_draft_id NUMBER;
390 l_doc_type_class VARCHAR2(30);
391 l_input_url VARCHAR2(500);
392 l_base_or_mod VARCHAR2(30);
393 l_clm_document_number VARCHAR2(50);
394
395
396 CURSOR c_get_clm_number IS
397 SELECT clm_document_number
398 FROM po_headers_all
399 WHERE po_header_id = l_doc_id;
400
401 CURSOR c_get_modification_number IS
402 SELECT d.modification_number
403 FROM po_headers_draft_all h, po_drafts d
404 WHERE d.document_id = l_doc_id
405 AND d.draft_id = l_draft_id
406 AND d.document_id = h.po_header_id
407 AND d.draft_id = h.draft_id;
408
409 l_lookup_code VARCHAR2(30);
410
411 l_schemaversionused VARCHAR2(30);
412 l_systemadministratordodaac VARCHAR2(30);
413 l_datatemplatecode VARCHAR2(30);
414 l_ditprnumber NUMBER ;
415 l_doc_purpose VARCHAR2(30);
416 l_system_name VARCHAR2(30);
417
418 CURSOR conc_pgm_params IS
419 SELECT END_USER_COLUMN_NAME,DEFAULT_VALUE
420 FROM FND_DESCR_FLEX_COL_USAGE_VL
421 WHERE (APPLICATION_ID=201)
422 and (DESCRIPTIVE_FLEXFIELD_NAME='$SRS$.POEDAFILGEN')
423 order by column_seq_num;
424
425 l_req_id NUMBER;
426
427 CURSOR c_req_completion_text IS
428 SELECT completion_text
429 FROM FND_CONC_REQ_SUMMARY_V
430 WHERE request_id = l_req_id;
431
432 l_completion_text VARCHAR2(500);
433
434 BEGIN
435 --read the parameters values passed to this event
436 l_doc_id := p_event.getvalueforparameter('PO_EDA_DOC_ID');
437 l_doc_type := p_event.getvalueforparameter('PO_EDA_DOC_TYPE');
438 l_draft_id := p_event.getvalueforparameter('PO_EDA_DRAFT_ID');
439
440 IF l_draft_id = -1 THEN
441 l_base_or_mod := 'Base Documents';
442 OPEN c_get_clm_number;
443 FETCH c_get_clm_number INTO l_clm_document_number;
444 CLOSE c_get_clm_number;
445 ELSE
446 l_base_or_mod := 'Modifications';
447 OPEN c_get_modification_number;
448 FETCH c_get_modification_number INTO l_clm_document_number;
449 CLOSE c_get_modification_number;
450
451 END IF;
452
453 FOR cparam IN conc_pgm_params LOOP
454 IF cparam.END_USER_COLUMN_NAME = 'p_schemaversionused' THEN
455 l_schemaversionused := cparam.DEFAULT_VALUE;
456 END IF;
457 IF cparam.END_USER_COLUMN_NAME = 'p_systemadministratordodaac' THEN
458 l_systemadministratordodaac := cparam.DEFAULT_VALUE;
459 END IF;
460 IF cparam.END_USER_COLUMN_NAME = 'p_datatemplatecode' THEN
461 l_datatemplatecode := cparam.DEFAULT_VALUE;
462 END IF;
463 IF cparam.END_USER_COLUMN_NAME = 'p_ditprnumber' THEN
464 l_ditprnumber := cparam.DEFAULT_VALUE;
465 END IF;
466 IF cparam.END_USER_COLUMN_NAME = 'p_doc_purpose' THEN
467 l_doc_purpose := cparam.DEFAULT_VALUE;
468 END IF;
469 IF cparam.END_USER_COLUMN_NAME = 'p_system_name' THEN
470 l_system_name := cparam.DEFAULT_VALUE;
471 END IF;
472
473 END LOOP;
474
475 l_req_id := fnd_request.submit_request
476 (application => 'PO',
477 program => 'POEDAFILGEN',
478 description => NULL,
479 start_time => NULL,
480 sub_request => TRUE,
481 argument1 => l_base_or_mod,
482 argument2 => l_clm_document_number,
483 argument3 => NULL,
484 argument4 => NULL,
485 argument5 => NULL,
486 argument6 => NULL,
487 argument7 => NULL,
488 argument8 => NULL,
489 argument9 => NULL,
490 argument10 => l_schemaversionused,
491 argument11 => l_systemadministratordodaac,
492 argument12 => l_datatemplatecode,
493 argument13 => l_ditprnumber,
494 argument14 => l_doc_purpose,
495 argument15 => l_system_name
496 );
497 COMMIT;
498
499 l_lookup_code:=get_lookup_value('SUBMITTED');
500
501 OPEN c_req_completion_text;
502 FETCH c_req_completion_text INTO l_completion_text;
503 CLOSE c_req_completion_text;
504
505 l_completion_text:= 'Request Id:'||l_req_id||' '||l_completion_text;
506 INSERT INTO po_electronic_data_access
507 (
508 id ,
509 doc_id ,
510 doc_type ,
511 draft_id ,
512 performed_by ,
513 request_date ,
514 response_date ,
515 status ,
516 response ,
517 approved_without_check ,
518 reason ,
519 history_record ,
520 request_id ,
521 last_update_date ,
522 last_updated_by ,
523 creation_date ,
524 created_by ,
525 last_update_login
526 )
527 VALUES (po_eda_s.NEXTVAL,
528 l_doc_id,
529 l_doc_type,
530 l_draft_id,
531 Fnd_Global.User_Id,
532 SYSDATE,
533 SYSDATE,
534 l_lookup_code,
535 l_completion_text,
536 NULL,
537 NULL,
538 'Y',
539 l_req_id,
540 SYSDATE,
541 Fnd_Global.User_Id,
542 SYSDATE,
543 Fnd_Global.User_Id,
544 Fnd_Global.Login_Id);
545
546 COMMIT;
547
548 RETURN 'SUCCESS';
549 END po_eda_receive;
550
551
552 END PO_EDA_PKG;