[Home] [Help]
PACKAGE BODY: APPS.PO_INBOUND_XML
Source
1 PACKAGE BODY po_inbound_xml AS
2 /* $Header: POSYNCIB.pls 120.4 2010/07/20 21:14:05 bvanjaku noship $ */
3 l_debug NUMBER :=NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4 l_module VARCHAR2(15):='PO_INBOUND_XML';
5 PROCEDURE trace
6 (
7 p_message VARCHAR2,
8 p_module VARCHAR2,
9 p_level NUMBER := 9)
10 IS
11 pragma autonomous_transaction;
12 BEGIN
13 inv_log_util.trace( p_message,p_module,p_level);
14 COMMIT;
15 END;
16 FUNCTION update_po
17 (
18 P_PO_NUMBER VARCHAR2,
19 P_RELEASE_NUMBER NUMBER,
20 P_REVISION_NUMBER NUMBER,
21 P_LINE_NUMBER NUMBER,
22 P_SHIPMENT_NUMBER NUMBER,
23 P_NEW_QUANTITY NUMBER,
24 P_NEW_PRICE NUMBER,
25 P_NEW_PROMISED_DATE DATE,
26 P_NEW_NEED_BY_DATE DATE := NULL,
27 P_LAUNCH_APPROVALS_FLAG VARCHAR2,
28 P_UPDATE_SOURCE VARCHAR2,
29 P_VERSION VARCHAR2,
30 P_OVERRIDE_DATE DATE := NULL,
31 P_API_ERRORS OUT NOCOPY PO_API_ERRORS_REC_TYPE,
32 P_BUYER_NAME VARCHAR2 DEFAULT NULL,
33 P_secondary_quantity NUMBER ,
34 P_preferred_grade VARCHAR2,
35 P_org_id IN NUMBER DEFAULT NULL )
36 RETURN NUMBER
37 IS
38 pragma autonomous_transaction;
39 l_result NUMBER := 1;
40 BEGIN
41 BEGIN
42 IF ( l_debug=1 ) THEN
43 trace ( 'In update_po for PO Number: '||P_PO_NUMBER,l_module ) ;
44 END IF;
45 l_result:=po_change_api1_s.update_po ( X_PO_NUMBER =>P_PO_NUMBER, --Mandatory
46 X_RELEASE_NUMBER =>P_RELEASE_NUMBER, X_REVISION_NUMBER =>P_REVISION_NUMBER, --Mandatory
47 X_LINE_NUMBER =>P_LINE_NUMBER, --Mandatory
48 X_SHIPMENT_NUMBER =>P_SHIPMENT_NUMBER,
49 NEW_QUANTITY =>P_NEW_QUANTITY,
50 NEW_PRICE =>P_NEW_PRICE,
51 NEW_PROMISED_DATE =>P_NEW_PROMISED_DATE,
52 NEW_NEED_BY_DATE =>P_NEW_NEED_BY_DATE,
53 LAUNCH_APPROVALS_FLAG=>P_LAUNCH_APPROVALS_FLAG,
54 UPDATE_SOURCE =>P_UPDATE_SOURCE,
55 VERSION =>P_VERSION,
56 X_OVERRIDE_DATE =>P_OVERRIDE_DATE,
57 X_API_ERRORS =>P_API_ERRORS,
58 p_BUYER_NAME =>P_BUYER_NAME,
59 p_secondary_quantity =>P_secondary_quantity,
60 p_preferred_grade =>P_preferred_grade,
61 p_org_id =>P_org_id ) ;
62 COMMIT;
63 EXCEPTION
64 WHEN OTHERS THEN
65 l_result :=0;
66 IF ( l_debug=1 ) THEN
67 trace ( 'Unable to update PO: '||P_PO_NUMBER||'. '||SQLERRM,l_module ) ;
68 END IF;
69 END;
70 RETURN l_result;
71 END update_po;
72 PROCEDURE cancel_po
73 (
74 l_api_version IN NUMBER,
75 l_init_msg_list IN VARCHAR2,
76 l_commit IN VARCHAR2,
77 l_return_status OUT NOCOPY VARCHAR2,
78 l_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
79 l_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
80 l_doc_id IN NUMBER,
81 l_doc_num IN PO_HEADERS.segment1%TYPE,
82 l_release_id IN NUMBER,
83 l_release_num IN NUMBER,
84 l_doc_line_id IN NUMBER,
85 l_doc_line_num IN NUMBER,
86 l_doc_line_loc_id IN NUMBER,
87 l_doc_shipment_num IN NUMBER,
88 l_action IN VARCHAR2,
89 l_action_date IN DATE,
90 l_cancel_reason IN PO_LINES.cancel_reason%TYPE,
91 l_cancel_reqs_flag IN VARCHAR2,
92 l_print_flag IN VARCHAR2,
93 l_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE,
94 l_use_gldate IN VARCHAR2 DEFAULT NULL,
95 l_org_id IN NUMBER DEFAULT NULL )
96 IS
97 pragma autonomous_transaction;
98 BEGIN
99 BEGIN
100 IF ( l_debug=1 ) THEN
101 trace ( 'In cancel_po for PO Number: '||l_doc_num,l_module ) ;
102 END IF;
103 PO_Document_Control_PUB.control_document ( p_api_version =>l_api_version,
104 p_init_msg_list =>l_init_msg_list,
105 p_commit =>l_commit,
106 x_return_status =>l_return_status,
107 p_doc_type =>l_doc_type,
108 p_doc_subtype =>l_doc_subtype,
109 p_doc_id =>l_doc_id ,
110 p_doc_num =>l_doc_num,
111 p_release_id =>l_release_id,
112 p_release_num =>l_release_num,
113 p_doc_line_id =>l_doc_line_id,
114 p_doc_line_num =>l_doc_line_num,
115 p_doc_line_loc_id =>l_doc_line_loc_id,
116 p_doc_shipment_num =>l_doc_shipment_num,
117 p_action =>l_action,
118 p_action_date =>l_action_date,
119 p_cancel_reason =>l_cancel_reason,
120 p_cancel_reqs_flag =>l_cancel_reqs_flag,
121 p_print_flag =>l_print_flag,
122 p_note_to_vendor =>l_note_to_vendor,
123 p_use_gldate =>l_use_gldate,
124 p_org_id =>l_org_id ) ;
125 COMMIT;
126 EXCEPTION
127 WHEN OTHERS THEN
128 IF ( l_debug=1 ) THEN
129 trace ( 'Unable to cancel PO: '||l_doc_num||'. '||SQLERRM,l_module ) ;
130 END IF;
131 END;
132 END cancel_po;
133 PROCEDURE log_err_msg
134 (
135 x_msg PO_API_ERRORS_REC_TYPE,
136 p_userid NUMBER,
137 p_applid NUMBER,
138 p_respid NUMBER,
139 p_batch_id NUMBER,
140 p_interface_header_id NUMBER,
141 p_interface_line_id NUMBER )
142 IS
143 pragma autonomous_transaction;
144 c NUMBER;
145 BEGIN
146 IF ( l_debug=1 ) THEN
147 trace ( 'Logging error messages',l_module ) ;
148 END IF;
149 FOR c IN 1..x_msg.message_name.count
150 LOOP
151 BEGIN
152 INSERT
153 INTO po_interface_errors
154 (
155 INTERFACE_TYPE,
156 INTERFACE_TRANSACTION_ID,
157 COLUMN_NAME,
158 ERROR_MESSAGE,
159 PROCESSING_DATE,
160 CREATION_DATE,
161 CREATED_BY,
162 LAST_UPDATE_DATE,
163 LAST_UPDATED_BY,
164 LAST_UPDATE_LOGIN,
165 REQUEST_ID,
166 PROGRAM_APPLICATION_ID,
167 PROGRAM_ID,
168 PROGRAM_UPDATE_DATE,
169 ERROR_MESSAGE_NAME,
170 TABLE_NAME,
171 BATCH_ID,
172 INTERFACE_HEADER_ID,
173 INTERFACE_LINE_ID,
174 INTERFACE_DISTRIBUTION_ID,
175 COLUMN_VALUE,
176 INTERFACE_LINE_LOCATION_ID,
177 INTERFACE_ATTR_VALUES_ID,
178 INTERFACE_ATTR_VALUES_TLP_ID,
179 PRICE_DIFF_INTERFACE_ID,
180 TOKEN1_NAME,
181 TOKEN1_VALUE,
182 TOKEN2_NAME,
183 TOKEN2_VALUE,
184 TOKEN3_NAME,
185 TOKEN3_VALUE,
186 TOKEN4_NAME,
187 TOKEN4_VALUE,
188 TOKEN5_NAME,
189 TOKEN5_VALUE,
190 TOKEN6_NAME,
191 TOKEN6_VALUE,
192 APP_NAME
193 )
194 VALUES
195 (
196 'PO_DOCS_OPEN_INTERFACE',
197 PO_INTERFACE_ERRORS_S.nextval,
198 x_msg.column_name(c),
199 x_msg.message_text(c),
200 sysdate,
201 sysdate,
202 p_userid,
203 sysdate,
204 p_userid,
205 p_userid,
206 NULL,
207 p_applid,
208 p_respid,
209 sysdate,
210 x_msg.message_name(c),
211 x_msg.table_name(c),
212 p_batch_id,
213 p_interface_header_id,
214 p_interface_line_id,
215 NULL,
216 NULL,
217 NULL,
218 NULL,
219 NULL,
220 NULL,
221 NULL,
222 NULL,
223 NULL,
224 NULL,
225 NULL,
226 NULL,
227 NULL,
228 NULL,
229 NULL,
230 NULL,
231 NULL,
232 NULL,
233 NULL
234 );
235 COMMIT;
236 EXCEPTION
237 WHEN OTHERS THEN
238 IF
239 (
240 l_debug=1
241 )
242 THEN
243 trace
244 (
245 'Unable to log error message. '||SQLERRM,l_module
246 )
247 ;
248 END IF;
249 END;
250 END LOOP;
251 EXCEPTION
252 WHEN OTHERS THEN
253 IF
254 (
255 l_debug=1
256 )
257 THEN
258 trace
259 (
260 'Unable to log error message. '||SQLERRM,l_module
261 )
262 ;
263 END IF;
264 END log_err_msg;
265 PROCEDURE process_po_inbound_xml
266 (
267 itemtype IN VARCHAR2,
268 itemkey IN VARCHAR2,
269 actid IN NUMBER,
270 funcmode IN VARCHAR2,
271 resultout OUT NOCOPY VARCHAR2
272 )
273 IS
274 l_interface_header_id NUMBER :=NULL;
275 l_argument1 NUMBER :=NULL ; --Buyer Id
276 l_argument2 VARCHAR2
277 (
278 20
279 )
280 :='STANDARD' ; --Document Type STANDARD
281 l_argument3 NUMBER :=NULL ; --Document Sub Type
282 l_argument4 VARCHAR2
283 (
284 20
285 )
286 :='N' ; --Create Items 'Y' or 'N'
287 l_argument5 NUMBER :=NULL ; --Create Sourcing Rules
288 l_argument6 VARCHAR2
289 (
290 20
291 )
292 :='APPROVED'; --Approval Status APPROVED or INCOMPLETE or INITIATE APPROVAL
293 l_argument7 NUMBER :=NULL ; --Release Generation Method
294 l_argument8 NUMBER :=NULL; --Batch Id
295 l_argument9 NUMBER :=NULL; --Operating Unit
296 l_argument10 NUMBER :=NULL ; --Global Agreement
297 l_req_id NUMBER;
298 p_userid NUMBER;
299 p_respid NUMBER;
300 p_applid NUMBER;
301 p_session_id NUMBER;
302 x_msg PO_API_ERRORS_REC_TYPE;
303 x_return_status VARCHAR2
304 (
305 10
306 )
307 ;
308 x_cancel_msg VARCHAR2
309 (
310 2000
311 )
312 ;
313 l_result NUMBER;
314 l_result_lines NUMBER;
315 l_rec_count NUMBER;
316 l_rec_upd_count NUMBER;
317 l_rec_can_count NUMBER;
318 l_lns_count NUMBER;
319 l_rejected_flag NUMBER:=0;
320 l_po_number po_headers_interface.document_num%type;
321 l_release_num po_headers_interface.release_num%type:=NULL;
322 l_revision_num po_headers_interface.revision_num%type;
323 l_line_num po_lines_interface.line_num%type:=NULL;
324 CURSOR l_csr_lines
325 (
326 p_interface_header_id NUMBER,p_process_code VARCHAR2
327 )
328 IS
329 SELECT interface_line_id,
330 line_num,
331 shipment_num,
332 quantity,
333 unit_price,
334 promised_date,
335 need_by_date,
336 organization_id
337 FROM po_lines_interface
338 WHERE interface_header_id=p_interface_header_id
339 AND process_code =p_process_code
340 ORDER BY line_num FOR UPDATE OF po_lines_interface.process_code;
341
342 type po_lines_rec
343 IS
344 record
345 (
346 interface_line_id po_lines_interface.interface_line_id%type,
347 line_num po_lines_interface.line_num%type,
348 shipment_num po_lines_interface.shipment_num%type,
349 quantity po_lines_interface.quantity%type,
350 unit_price po_lines_interface.unit_price%type,
351 promised_date po_lines_interface.promised_date%type,
352 need_by_date po_lines_interface.need_by_date%type,
353 organization_id po_lines_interface.organization_id%type );
354 l_po_lines_rec po_lines_rec;
355 L_LAUNCH_APPROVALS_FLAG VARCHAR2(1):='N';
356 po_process_inbound EXCEPTION;
357 BEGIN
358 l_interface_header_id := itemkey;
359 IF(l_debug =1) THEN
360 trace('In process_po_inbound_xml',l_module);
361 END IF;
362 IF l_interface_header_id IS NULL THEN
363 trace('EXCEPTION: PO interface_header_id is NULL',l_module);
364 RAISE po_process_inbound;
365 ELSE
366 p_respid :=FND_GLOBAL.RESP_ID;
367 p_applid :=FND_GLOBAL.RESP_APPL_ID;
368 p_userid :=FND_GLOBAL.USER_ID;
369 BEGIN
370 MO_GLOBAL.init ('PO');
371 fnd_global.apps_initialize(p_userid, p_respid, p_applid);
372 l_debug :=NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
373 IF(l_debug=1) THEN
374 trace('In process_po_inbound_xml',l_module);
375 trace('l_interface_header_id: '||l_interface_header_id,l_module);
376 trace('User ID, Resp ID, Appl ID: '||p_userid||', '||p_respid||', '||p_applid,l_module);
377 END IF;
378 EXCEPTION
379 WHEN OTHERS THEN
380 IF(l_debug=1) THEN
381 trace('EXCEPTION: Unable to initialize.',l_module);
382 END IF;
383 RAISE po_process_inbound;
384 END;
385 BEGIN
386 SELECT document_num,
387 batch_id,
388 org_id
389 INTO l_po_number,
390 l_argument8,
391 l_argument9
392 FROM po_headers_interface
393 WHERE interface_header_id= l_interface_header_id;
394 EXCEPTION
395 WHEN OTHERS THEN
396 l_po_number:=NULL;
397 IF(l_debug =1) THEN
398 trace('EXCEPTION: Unable retrieve PO number, batch_id, org_id. '||SQLERRM,l_module);
399 END IF;
400 RAISE po_process_inbound;
401 END;
402 IF(l_debug=1) THEN
403 trace('document_num, batch_id, org_id: '||l_po_number||', '||l_argument8||', '||l_argument9,l_module);
404 END IF;
405 BEGIN
406 SELECT COUNT(1)
407 INTO l_rec_count
408 FROM po_lines_interface
409 WHERE interface_header_id= l_interface_header_id
410 AND process_code IS NULL;
411 EXCEPTION
412 WHEN OTHERS THEN
413 l_rec_count:=0;
414 IF(l_debug =1) THEN
415 trace('Unable retrieve record count of po lines creation. '||SQLERRM,l_module);
416 END IF;
417 END;
418 IF(l_debug=1) THEN
419 trace('Record count of po lines creation: '||l_rec_count,l_module);
420 END IF;
421 IF l_po_number IS NOT NULL THEN
422 BEGIN
423 SELECT COUNT(1)
424 INTO l_rec_upd_count
425 FROM po_lines_interface
426 WHERE interface_header_id= l_interface_header_id
427 AND process_code ='UPDATE';
428 EXCEPTION
429 WHEN OTHERS THEN
430 l_rec_upd_count:=0;
431 IF(l_debug =1) THEN
432 trace('Unable retrieve record count of po lines updation. '||SQLERRM,l_module);
433 END IF;
434 END;
435 IF(l_debug=1) THEN
436 trace('Record count of po lines updation: '||l_rec_upd_count,l_module);
437 END IF;
438 BEGIN
439 SELECT COUNT(1)
440 INTO l_rec_can_count
441 FROM po_lines_interface
442 WHERE interface_header_id= l_interface_header_id
443 AND process_code ='CANCEL';
444 EXCEPTION
445 WHEN OTHERS THEN
446 l_rec_can_count:=0;
447 IF(l_debug =1) THEN
448 trace('Unable retrieve record count of po lines cancellation. '||SQLERRM,l_module);
449 END IF;
450 END;
451 IF(l_debug=1) THEN
452 trace('Record count of po lines updation: '||l_rec_can_count,l_module);
453 END IF;
454 IF l_rec_upd_count>0 THEN
455 IF(l_debug =1) THEN
456 trace('Started processing Update - PO Line',l_module);
457 END IF;
458 BEGIN
459 OPEN l_csr_lines(l_interface_header_id,'UPDATE');
460 LOOP
461 FETCH l_csr_lines INTO l_po_lines_rec;
462 EXIT
463 WHEN l_csr_lines%notfound;
464 IF l_line_num IS NULL OR l_line_num<>l_po_lines_rec.line_num THEN
465 l_line_num :=l_po_lines_rec.line_num;
466 IF(l_debug =1) THEN
467 trace('Processing Update - PO Line at line level. Line Num: '||l_line_num,l_module);
468 END IF;
469 BEGIN
470 SELECT revision_num
471 INTO l_revision_num
472 FROM po_headers_all
473 WHERE segment1= l_po_number
474 AND org_id = l_argument9;
475 EXCEPTION
476 WHEN OTHERS THEN
477 l_revision_num:=NULL;
478 IF(l_debug =1) THEN
479 trace('EXCEPTION: Unable retrieve revision number',l_module);
480 END IF;
481 RAISE po_process_inbound;
482 END;
483 l_result_lines :=update_po (P_PO_NUMBER =>l_po_number, --Mandatory
484 P_RELEASE_NUMBER =>l_release_num, P_REVISION_NUMBER =>l_revision_num, --Mandatory
485 P_LINE_NUMBER =>l_po_lines_rec.line_num, --Mandatory
486 P_SHIPMENT_NUMBER =>NULL,
487 P_NEW_QUANTITY =>NULL,
488 P_NEW_PRICE =>l_po_lines_rec.unit_price,
489 P_NEW_PROMISED_DATE =>NULL,
490 P_NEW_NEED_BY_DATE =>NULL,
491 P_LAUNCH_APPROVALS_FLAG=>L_LAUNCH_APPROVALS_FLAG,
492 P_UPDATE_SOURCE =>NULL, P_VERSION =>1.0,
493 P_OVERRIDE_DATE =>NULL, P_API_ERRORS =>x_msg,
494 P_BUYER_NAME =>NULL,
495 P_secondary_quantity =>NULL,
496 P_preferred_grade =>NULL,
497 P_org_id =>l_po_lines_rec.organization_id );
498 IF l_result_lines<>1 THEN
499 UPDATE po_lines_interface
500 SET process_code = 'REJECTED'
501 WHERE CURRENT OF l_csr_lines;
502
503 log_err_msg (x_msg, p_userid, p_applid , p_respid,l_argument8, l_interface_header_id, l_po_lines_rec.interface_line_id);
504 l_rejected_flag:=1;
505 END IF;
506 END IF;
507 IF l_result_lines=1 THEN
508 BEGIN
509 SELECT revision_num
510 INTO l_revision_num
511 FROM po_headers_all
512 WHERE segment1= l_po_number
513 AND org_id = l_argument9;
514 EXCEPTION
515 WHEN OTHERS THEN
516 l_revision_num:=NULL;
517 IF(l_debug =1) THEN
518 trace('Unable retrieve revision number',l_module);
519 END IF;
520 RAISE po_process_inbound;
521 END;
522 IF(l_debug=1) THEN
523 trace('Processing Update - PO Line at shipment level. Line Num, Shipment Num: '||l_line_num||', '||l_po_lines_rec.shipment_num,l_module);
524 END IF;
525 IF l_csr_lines%ROWCOUNT = l_rec_upd_count THEN
526 L_LAUNCH_APPROVALS_FLAG:='Y';
527 END IF;
528 l_result :=update_po ( P_PO_NUMBER =>l_po_number, --Mandatory
529 P_RELEASE_NUMBER =>l_release_num, P_REVISION_NUMBER =>l_revision_num, --Mandatory
530 P_LINE_NUMBER =>l_po_lines_rec.line_num, --Mandatory
531 P_SHIPMENT_NUMBER =>l_po_lines_rec.shipment_num,
532 P_NEW_QUANTITY =>l_po_lines_rec.quantity,
533 P_NEW_PRICE =>NULL,
534 P_NEW_PROMISED_DATE =>l_po_lines_rec.promised_date,
535 P_NEW_NEED_BY_DATE =>l_po_lines_rec.need_by_date,
536 P_LAUNCH_APPROVALS_FLAG=>L_LAUNCH_APPROVALS_FLAG,
537 P_UPDATE_SOURCE =>NULL, P_VERSION =>1.0,
538 P_OVERRIDE_DATE =>NULL, P_API_ERRORS =>x_msg,
539 p_BUYER_NAME =>NULL,
540 p_secondary_quantity =>NULL,
541 p_preferred_grade =>NULL,
542 p_org_id =>l_po_lines_rec.organization_id );
543 IF l_result=1 THEN
544 UPDATE po_lines_interface
545 SET process_code = 'ACCEPTED'
546 WHERE CURRENT OF l_csr_lines;
547 ELSE
548 UPDATE po_lines_interface
549 SET process_code = 'REJECTED'
550 WHERE CURRENT OF l_csr_lines;
551
552 log_err_msg ( x_msg, p_userid, p_applid , p_respid,l_argument8, l_interface_header_id, l_po_lines_rec.interface_line_id);
553 l_rejected_flag:=1;
554 END IF;
555 END IF;
556 END LOOP;
557 EXCEPTION
558 WHEN OTHERS THEN
559 IF(l_debug=1) THEN
560 trace('EXCEPTION: In processing Update - PO line. ',l_module);
561 END IF;
562 RAISE po_process_inbound;
563 END;
564 CLOSE l_csr_lines;
565 IF(l_debug=1) THEN
566 trace('Completed processing Update - PO Line',l_module);
567 END IF;
568 END IF;
569 IF l_rec_can_count>0 THEN
570 IF(l_debug =1) THEN
571 trace('Started processing Cancel - PO Line',l_module);
572 END IF;
573 BEGIN
574 OPEN l_csr_lines ( l_interface_header_id, 'CANCEL' ) ;
575 LOOP
576 FETCH l_csr_lines INTO l_po_lines_rec;
577 EXIT
578 WHEN l_csr_lines%notfound;
579 IF(l_debug=1) THEN
580 trace('Processing Cancel - PO Line. Line Num: '||l_po_lines_rec.line_num,l_module);
581 END IF;
582 cancel_po (l_api_version =>1.0,
583 l_init_msg_list =>x_cancel_msg,
584 l_commit =>FND_API.G_TRUE,
585 l_return_status =>x_return_status,
586 l_doc_type =>'PO',
587 l_doc_subtype =>'STANDARD',
588 l_doc_id =>NULL ,
589 l_doc_num =>l_po_number,
590 l_release_id =>NULL,
591 l_release_num =>NULL,
592 l_doc_line_id =>NULL,
593 l_doc_line_num =>l_po_lines_rec.line_num,
594 l_doc_line_loc_id =>NULL,
595 l_doc_shipment_num =>NULL,
596 l_action =>'CANCEL',
597 l_action_date =>sysdate,
598 l_cancel_reason =>NULL,
599 l_cancel_reqs_flag =>'N',
600 l_print_flag =>'N',
601 l_note_to_vendor =>NULL,
602 l_use_gldate =>NULL,
603 l_org_id =>l_po_lines_rec.organization_id );
604 IF x_return_status=FND_API.g_ret_sts_success THEN
605 UPDATE po_lines_interface
606 SET process_code = 'ACCEPTED'
607 WHERE CURRENT OF l_csr_lines;
608 ELSE
609 UPDATE po_lines_interface
610 SET process_code = 'REJECTED'
611 WHERE CURRENT OF l_csr_lines;
612
613 l_rejected_flag:=1;
614 END IF;
615 END LOOP;
616 EXCEPTION
617 WHEN OTHERS THEN
618 IF(l_debug=1) THEN
619 trace('EXCEPTION: In processing Cancel - PO line. ',l_module);
620 END IF;
621 RAISE po_process_inbound;
622 END;
623 IF(l_debug=1) THEN
624 trace('Completed processing Cancel - PO Line',l_module);
625 END IF;
626 END IF;
627 IF is_po_number_exists(l_po_number,l_argument9) THEN
628 BEGIN
629 SELECT revision_num
630 INTO l_revision_num
631 FROM po_headers_interface
632 WHERE interface_header_id= l_interface_header_id;
633 EXCEPTION
634 WHEN OTHERS THEN
635 l_revision_num:=NULL;
636 IF(l_debug =1) THEN
637 trace('EXCEPTION: Unable retrieve revision number',l_module);
638 END IF;
639 RAISE po_process_inbound;
640 END;
641 BEGIN
642 IF l_revision_num IS NOT NULL THEN
643 UPDATE po_headers_all
644 SET revision_num=l_revision_num
645 WHERE segment1 = l_po_number
646 AND org_id = l_argument9;
647 END IF;
648 EXCEPTION
649 WHEN OTHERS THEN
650 IF(l_debug=1) THEN
651 trace('EXCEPTION: Unable update revision number',l_module);
652 END IF;
653 RAISE po_process_inbound;
654 END;
655 END IF;
656 IF(l_debug=1) THEN
657 trace('Started deleting processed records. ',l_module);
658 END IF;
659 BEGIN
660 DELETE
661 FROM po_distributions_interface
662 WHERE interface_line_id IN
663 (SELECT interface_line_id
664 FROM po_lines_interface
665 WHERE interface_header_id =l_interface_header_id
666 AND process_code ='ACCEPTED'
667 );
668 DELETE
669 FROM po_lines_interface
670 WHERE interface_header_id =l_interface_header_id
671 AND process_code ='ACCEPTED';
672 IF l_rec_count =0 THEN
673 SELECT COUNT(*)
674 INTO l_lns_count
675 FROM po_lines_interface
676 WHERE interface_header_id =l_interface_header_id;
677 IF l_lns_count =0 THEN
678 DELETE
679 FROM po_headers_interface
680 WHERE interface_header_id =l_interface_header_id;
681 END IF;
682 END IF;
683 COMMIT;
684 EXCEPTION
685 WHEN OTHERS THEN
686 ROLLBACK;
687 IF(l_debug=1) THEN
688 trace('EXCEPTION: In deleting processed records. '||SQLERRM,l_module);
689 END IF;
690 RAISE po_process_inbound;
691 END;
692 IF(l_debug=1) THEN
693 trace('Completed deleting processed records. ',l_module);
694 END IF;
695 END IF;
696 IF l_rec_count>0 THEN
697 IF(l_debug =1) THEN
698 trace('Submitting Concurrent Request for Program POXPOPDOI',l_module);
699 END IF;
700 l_req_id := fnd_request.submit_request( application => 'PO',
701 program => 'POXPOPDOI',
702 Argument1=>l_argument1,
703 Argument2=>l_argument2,
704 Argument3=>l_argument3,
705 Argument4=>l_argument4,
706 Argument5=>l_argument5,
707 Argument6=>l_argument6,
708 Argument7=>l_argument7,
709 Argument8=>l_argument8,
710 Argument9=>l_argument9,
711 Argument10=>l_argument10);
712 COMMIT; -- Need to commit for the request to be submitted
713 IF (l_req_id = 0) THEN
714 fnd_message.set_name(application => 'PO', NAME => 'PO_SUBMIT_FAIL');
715 fnd_message.set_token(token => 'TOKEN', VALUE =>l_argument8,TRANSLATE => FALSE);
716 fnd_msg_pub.ADD;
717 ROLLBACK;
718 RAISE po_process_inbound;
719 ELSE
720 fnd_message.set_name(application => 'PO', NAME => 'PO_CONC_TRANSACT');
721 fnd_message.set_token(token => 'REQUEST_ID', VALUE => TO_CHAR(l_req_id), TRANSLATE => FALSE);
722 fnd_msg_pub.ADD;
723 END IF;
724 END IF;
725 END IF;
726 IF l_rejected_flag=0 THEN
727 resultout := 'COMPLETE:SUCCESS';
728 ELSE
729 resultout := 'COMPLETE:ERROR';
730 END IF;
731 EXCEPTION
732 WHEN po_process_inbound THEN
733 resultout := 'COMPLETE:ERROR';
734 IF(l_debug =1) THEN
735 trace('Unexpected error has occured. Error message: '||SQLERRM,l_module);
736 END IF;
737 WHEN OTHERS THEN
738 resultout := 'COMPLETE:ERROR';
739 IF(l_debug =1) THEN
740 trace('Unexpected error has occured. Error message: '||SQLERRM,l_module);
741 END IF;
742 END process_po_inbound_xml;
743 PROCEDURE is_po_number_exists
744 (
745 p_po_number IN VARCHAR,
746 p_operating_unit IN NUMBER,
747 p_po_header_id OUT NOCOPY NUMBER)
748 IS
749 BEGIN
750 SELECT po_header_id
751 INTO p_po_header_id
752 FROM po_headers_all
753 WHERE segment1=p_po_number
754 AND org_id = p_operating_unit;
755 EXCEPTION
756 WHEN No_Data_Found THEN
757 p_po_header_id:=NULL;
758 IF(l_debug =1) THEN
759 trace('PO number does not exists',l_module);
760 END IF;
761 END is_po_number_exists;
762 FUNCTION is_po_number_exists
763 (
764 p_po_number IN VARCHAR,
765 p_operating_unit IN NUMBER)
766 RETURN BOOLEAN
767 IS
768 l_po_number NUMBER;
769 BEGIN
770 SELECT segment1
771 INTO l_po_number
772 FROM po_headers_all
773 WHERE segment1=p_po_number
774 AND org_id = p_operating_unit;
775 RETURN TRUE;
776 EXCEPTION
777 WHEN No_Data_Found THEN
778 IF(l_debug=1) THEN
779 trace('PO number does not exists',l_module);
780 END IF;
781 RETURN FALSE;
782 END is_po_number_exists;
783 PROCEDURE GET_OPERATING_UNIT
784 (
785 p_location_code IN VARCHAR,
786 p_operating_unit OUT NOCOPY NUMBER)
787 IS
788 BEGIN
789 SELECT OPERATING_UNIT
790 INTO p_operating_unit
791 FROM ORG_ORGANIZATION_DEFINITIONS
792 WHERE ORGANIZATION_ID IN
793 (SELECT INVENTORY_ORGANIZATION_ID
794 FROM hr_locations
795 WHERE location_code = p_location_code
796 );
797 EXCEPTION
798 WHEN No_Data_Found THEN
799 p_operating_unit:=NULL;
800 IF(l_debug =1) THEN
801 trace('Operating unit not found',l_module);
802 END IF;
803 END GET_OPERATING_UNIT;
804 PROCEDURE GET_CLIENT_CODE
805 (
806 p_trading_location_code IN VARCHAR2,
807 p_client_code OUT NOCOPY VARCHAR2)
808 IS
809 p_trading_partner_type VARCHAR2(30);
810 p_trading_partner_site_id NUMBER;
811 BEGIN
812 BEGIN
813 SELECT eth.party_type ,
814 eth.party_site_id
815 INTO p_trading_partner_type,
816 p_trading_partner_site_id
817 FROM ecx_tp_headers eth,
818 ecx_tp_details etd
819 WHERE eth.tp_header_id = etd.tp_header_id
820 AND etd.source_tp_location_code=p_trading_location_code;
821 EXCEPTION
822 WHEN No_Data_Found THEN
823 p_trading_partner_type :=NULL;
824 p_trading_partner_site_id:=NULL;
825 IF(l_debug =1) THEN
826 trace('Unable to retrieve trading partner details',l_module);
827 END IF;
828 END;
829 IF p_trading_partner_site_id IS NOT NULL AND p_trading_partner_type IS NOT NULL AND p_trading_partner_type = 'C' AND WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' THEN
830 BEGIN
831 SELECT client_code
832 INTO p_client_code
833 FROM mtl_client_parameters
834 WHERE trading_partner_site_id = p_trading_partner_site_id;
835 EXCEPTION
836 WHEN No_Data_Found THEN
837 p_client_code:=NULL;
838 IF(l_debug =1) THEN
839 trace('Client code not found',l_module);
840 END IF;
841 END;
842 END IF;
843 END GET_CLIENT_CODE;
844 PROCEDURE GET_LINE_TYPE
845 (
846 p_operating_unit IN NUMBER,
847 p_line_type OUT NOCOPY VARCHAR2)
848 IS
849 BEGIN
850 SELECT pol.line_type
851 INTO p_line_type
852 FROM po_system_parameters_all posp,
853 po_line_types pol
854 WHERE posp.org_id =p_operating_unit
855 AND posp.line_type_id=pol.line_type_id;
856 EXCEPTION
857 WHEN No_Data_Found THEN
858 p_line_type:=NULL;
859 IF(l_debug =1) THEN
860 trace('Line type not found',l_module);
861 END IF;
862 END GET_LINE_TYPE;
863 PROCEDURE INITIALISE
864 (
865 p_user_name IN VARCHAR,
866 p_user_id OUT NOCOPY NUMBER )
867 IS
868 p_resp_id NUMBER;
869 p_appl_id NUMBER;
870 BEGIN
871 BEGIN
872 SELECT user_id
873 INTO p_user_id
874 FROM fnd_user
875 WHERE user_name = upper(p_user_name);
876 EXCEPTION
877 WHEN OTHERS THEN
878 p_user_id:=0;
879 IF(l_debug=1) THEN
880 trace('Unable to retrieve user id. '||SQLERRM,l_module);
881 END IF;
882 END;
883 BEGIN
884 SELECT RESPONSIBILITY_ID,
885 RESPONSIBILITY_APPLICATION_ID
886 INTO p_resp_id,
887 p_appl_id
888 FROM FND_USER_RESP_GROUPS_ALL
889 WHERE user_id = p_user_id
890 AND RESPONSIBILITY_APPLICATION_ID = 201
891 AND ROWNUM =1;
892 EXCEPTION
893 WHEN OTHERS THEN
894 p_resp_id:=0;
895 p_appl_id:=0;
896 IF(l_debug=1) THEN
897 trace('Unable to retrieve responsiblity id, responsiblity application is. '||SQLERRM,l_module);
898 END IF;
899 END;
900 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
901 END INITIALISE;
902 PROCEDURE GET_TERM_ID
903 (
904 p_term_name VARCHAR2,
905 p_term_id OUT NOCOPY NUMBER)
906 IS
907 BEGIN
908 BEGIN
909 SELECT TERM_ID INTO p_term_id FROM ap_terms WHERE name=p_term_name;
910 EXCEPTION
911 WHEN No_Data_Found THEN
912 p_term_id:=NULL;
913 IF(l_debug=1) THEN
914 trace('Term id not found',l_module);
915 END IF;
916 END;
917 END GET_TERM_ID;
918 END po_inbound_xml;