1 PACKAGE BODY GML_REPROCESS_RCPTS AS
2 /* $Header: GMLRRCTB.pls 120.1 2005/08/15 09:24:32 rakulkar noship $ */
3
4
5 /*========================================================================
6 | |
7 | PROCEDURE NAME update_records |
8 | |
9 | DESCRIPTION Procedure to Update the PROCESSING_STATUS_CODE and |
10 | TRANSACTION_STATUS_CODE in RCV_HEADERS_INTERFACE and |
11 | RCV_TRANSACTIONS_INTERFACE to PENDING and the |
12 | VALIDATION_FLAG in both tables to 'Y' so that the |
13 | receiving transaction processor will pick these |
14 | records up next time its run |
15 | |
16 | MODIFICATION HISTORY |
17 | |
18 | 12-OCT-00 Preetam B - Created. |
19 | |
20 | 09-MAY-01 Uday Phadtare Bug1774591. Upadate po_revision_num
21 | 11-JUL-01 Uday Phadtare Procedure Update_Records modified as part of
22 | Bug# 1878034 fix.
23 | 02-MAY-02 Uday Phadtare B2350663. Cursor Cr_get_rcpts modified so that records with
24 | NULL header_interface_id are also processed.
25 | 24-JUL-02 Lakshmi Swamy B2462033. Included delete statements to po_interface_errors
26 | so that multiple error messages for the same interface id are avoided.
27 | 12-AUG-02 Uday Phadtare B2470051. Update the status to 'PENDING' even if the
28 | status is 'PRINT' in rcv_headers_interface and rcv_transactions_interface.
29 | 29-OCT-02 Uday Phadtare B2647879. Do not update the status to 'PENDING'
30 | in rcv_headers_interface if the status is 'SUCCESS'.
31 =========================================================================*/
32
33 PROCEDURE Update_Records(errbufx OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2)
34 IS
35
36 CURSOR Cr_get_rcpts IS
37 SELECT h.header_interface_id hid,
38 h.processing_status_code psc,
39 d.interface_transaction_id tid
40 FROM rcv_headers_interface h, rcv_transactions_interface d
41 WHERE h.header_interface_id(+) = d.header_interface_id
42 AND ( h.processing_Status_code IN('ERROR','PRINT') OR d.processing_Status_code IN('ERROR','PRINT')
43 OR d.transaction_status_code IN('ERROR','PRINT') )
44 AND d.comments LIKE 'OPM%';
45
46
47 BEGIN
48
49 For Cr_get_rcpts_rec in Cr_get_rcpts
50 Loop
51 IF Cr_get_rcpts_rec.hid IS NOT NULL THEN
52 IF Cr_get_rcpts_rec.psc <> 'SUCCESS' THEN
53 Update RCV_HEADERS_INTERFACE
54 Set PROCESSING_STATUS_CODE = 'PENDING',
55 VALIDATION_FLAG = 'Y'
56 Where HEADER_INTERFACE_ID = Cr_get_rcpts_rec.hid;
57 END IF;
58
59 /* 2462033 - Included delete */
60
61 DELETE from po_interface_errors
62 WHERE interface_header_id = Cr_get_rcpts_rec.hid;
63
64 END IF;
65
66
67
68 Update RCV_TRANSACTIONS_INTERFACE RT
69 Set PROCESSING_STATUS_CODE = 'PENDING',
70 TRANSACTION_STATUS_CODE = 'PENDING',
71 VALIDATION_FLAG = 'Y',
72 PO_REVISION_NUM = (select revision_num from po_headers_all where
73 po_header_id = rt.po_header_id)
74 Where INTERFACE_TRANSACTION_ID= Cr_get_rcpts_rec.tid;
75
76 /* 2462033 - Included delete */
77 DELETE from po_interface_errors
78 WHERE INTERFACE_TRANSACTION_ID= Cr_get_rcpts_rec.tid
79 OR INTERFACE_LINE_ID = Cr_get_rcpts_rec.tid ;
80
81 End Loop;
82
83 commit;
84
85 END;
86
87
88 /*========================================================================
89 | |
90 | PROCEDURE NAME reprocess_adjust_errors |
91 | |
92 | DESCRIPTION Procedure to Update the PROCESSING_STATUS_CODE and |
93 | TRANSACTION_STATUS_CODE in RCV_HEADERS_INTERFACE and |
94 | RCV_TRANSACTIONS_INTERFACE to PENDING and the |
95 | VALIDATION_FLAG in both tables to 'Y' so that the |
96 | receiving transaction processor will pick these |
97 | records up next time its run |
98 | |
99 | MODIFICATION HISTORY |
100 | |
101 | 12-OCT-00 Preetam B - Created. |
102 | |
103 | |
104 =========================================================================*/
105
106 PROCEDURE reprocess_adjust_errors(errbufx OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2)
107 IS
108
109 err_num NUMBER;
110 err_msg VARCHAR2(100);
111
112 BEGIN
113
114 gml_recv_trans_pkg.gml_process_adjust_errors(retcode);
115
116
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 err_num := SQLCODE;
121 errbufx := SUBSTRB(SQLERRM, 1, 100);
122
123 END;
124
125 END GML_REPROCESS_RCPTS;
[Home] [Help]