[Home] [Help]
PACKAGE BODY: APPS.OE_LOTS_ACK_UTIL
Source
1 PACKAGE BODY OE_Lots_Ack_Util AS
2 /* $Header: OEXUSAKB.pls 115.6 2003/10/20 07:17:23 appldev ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Lots_Ack_Util';
7
8 PROCEDURE Insert_Row
9 ( p_line_tbl IN OE_Order_Pub.Line_Tbl_Type
10 , p_lot_serial_tbl IN OE_Order_Pub.Lot_Serial_Tbl_Type
11 , p_lot_serial_val_tbl IN OE_Order_Pub.Lot_Serial_Val_Tbl_Type
12 , p_old_line_tbl IN OE_Order_Pub.Line_Tbl_type
13 , p_old_lot_serial_tbl IN OE_Order_Pub.Lot_Serial_Tbl_Type
14 , p_old_lot_serial_val_tbl IN OE_Order_Pub.Lot_Serial_Val_Tbl_Type
15 , p_reject_order IN VARCHAR2
16 , x_return_status OUT NOCOPY VARCHAR2
17
18 )
19 IS
20 l_return_status VARCHAR2(1);
21 l_lot_serial_rec OE_Order_Pub.Lot_Serial_Rec_Type;
22 l_lot_serial_val_rec OE_Order_Pub.Lot_Serial_Val_Rec_Type;
23 --
24 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
25 --
26 BEGIN
27
28 IF l_debug_level > 0 THEN
29 oe_debug_pub.add('Enter OE_Lots_Ack_Util.Insert_Row');
30 End If;
31
32 /*
33 FOR I IN 1..p_old_line_tbl.COUNT LOOP
34 FOR J IN 1..p_old_lot_serial_tbl.COUNT LOOP
35 IF p_old_lot_serial_tbl(J).line_index = I THEN
36 IF p_reject_order = 'N' THEN
37 l_lot_serial_rec := p_lot_serial_tbl(J);
38 l_lot_serial_val_rec := p_lot_serial_val_tbl(J);
39 ELSE
40 l_lot_serial_rec := p_old_lot_serial_tbl(J);
41 -- Value record is not required as record is rejected
42 -- l_lot_serial_val_rec := p_old_lot_serial_val_tbl(J);
43 END IF;
44
45 IF l_debug_level > 0 THEN
46 oe_debug_pub.add( 'BEFORE LOTSERIAL ACKNOWLEDGMENT INSERT STATEMENT' ) ;
47 END IF;
48
49 INSERT INTO OE_LOTSERIAL_ACKS
50 ( ACKNOWLEDGMENT_FLAG
51 , ATTRIBUTE1
52 , ATTRIBUTE10
53 , ATTRIBUTE11
54 , ATTRIBUTE12
55 , ATTRIBUTE13
56 , ATTRIBUTE14
57 , ATTRIBUTE15
58 , ATTRIBUTE2
59 , ATTRIBUTE3
60 , ATTRIBUTE4
61 , ATTRIBUTE5
62 , ATTRIBUTE6
63 , ATTRIBUTE7
64 , ATTRIBUTE8
65 , ATTRIBUTE9
66 , BUYER_SELLER_FLAG
67 , CHANGE_DATE
68 , CHANGE_SEQUENCE
69 , CONTEXT
70 , CREATED_BY
71 , CREATION_DATE
72 -- , ERROR_FLAG
73 , FROM_SERIAL_NUMBER
74 -- , INTERFACE_STATUS
75 , LAST_UPDATED_BY
76 , LAST_UPDATE_DATE
77 , LAST_UPDATE_LOGIN
78 , LOT_NUMBER
79 , OPERATION_CODE
80 -- , ORDER_SOURCE
81 -- , ORDER_SOURCE_ID
82 , ORIG_SYS_DOCUMENT_REF
83 , ORIG_SYS_LINE_REF
84 , ORIG_SYS_LOT_SERIAL_REF
85 -- , ORIG_SYS_SHIPMENT_REF
86 -- , PROGRAM_APPLICATION_ID
87 -- , PROGRAM_ID
88 -- , PROGRAM_UPDATE_DATE
89 , QUANTITY
90 -- , REQUEST_ID
91 -- , TO_SERIAL_NUMBER
92 )
93 VALUES
94 ( 'A'
95 -- , ???.ACKNOWLEDGMENT_FLAG
96 , l_lot_serial_rec.ATTRIBUTE1
97 , l_lot_serial_rec.ATTRIBUTE10
98 , l_lot_serial_rec.ATTRIBUTE11
99 , l_lot_serial_rec.ATTRIBUTE12
100 , l_lot_serial_rec.ATTRIBUTE13
101 , l_lot_serial_rec.ATTRIBUTE14
102 , l_lot_serial_rec.ATTRIBUTE15
103 , l_lot_serial_rec.ATTRIBUTE2
104 , l_lot_serial_rec.ATTRIBUTE3
105 , l_lot_serial_rec.ATTRIBUTE4
106 , l_lot_serial_rec.ATTRIBUTE5
107 , l_lot_serial_rec.ATTRIBUTE6
108 , l_lot_serial_rec.ATTRIBUTE7
109 , l_lot_serial_rec.ATTRIBUTE8
110 , l_lot_serial_rec.ATTRIBUTE9
111 , 'B'
112 -- , ???.BUYER_SELLER_FLAG
113 , SYSDATE
114 -- , ???.CHANGE_DATE
115 , 1
116 -- , ???.CHANGE_SEQUENCE
117 , l_lot_serial_rec.CONTEXT
118 , l_lot_serial_rec.CREATED_BY
119 , l_lot_serial_rec.CREATION_DATE
120 -- , l_lot_serial_rec.ERROR_FLAG
121 , l_lot_serial_rec.FROM_SERIAL_NUMBER
122 -- , l_lot_serial_rec.INTERFACE_STATUS
123 , l_lot_serial_rec.LAST_UPDATED_BY
124 , l_lot_serial_rec.LAST_UPDATE_DATE
125 , l_lot_serial_rec.LAST_UPDATE_LOGIN
126 , l_lot_serial_rec.LOT_NUMBER
127 , l_lot_serial_rec.OPERATION
128 -- , l_lot_serial_rec.ORDER_SOURCE
129 -- , l_lot_serial_rec.ORDER_SOURCE_ID
130 , p_old_line_tbl(I).ORIG_SYS_DOCUMENT_REF
131 , p_old_line_tbl(I).ORIG_SYS_LINE_REF
132 , l_lot_serial_rec.ORIG_SYS_LOTSERIAL_REF
133 -- , l_lot_serial_rec.ORIG_SYS_SHIPMENT_REF
134 -- , l_lot_serial_rec.PROGRAM_APPLICATION_ID
135 -- , l_lot_serial_rec.PROGRAM_ID
136 -- , l_lot_serial_rec.PROGRAM_UPDATE_DATE
137 , l_lot_serial_rec.QUANTITY
138 -- , l_lot_serial_rec.REQUEST_ID
139 -- , l_lot_serial_rec.TO_SERIAL_NUMBER
140 );
141 END IF;
142 END LOOP;
143 END LOOP;
144 */
145
146 EXCEPTION
147
148 WHEN OTHERS THEN
149 IF l_debug_level > 0 THEN
150 oe_debug_pub.add( 'ENCOUNTERED OTHERS ERROR EXCEPTION IN OE_LOTS_ACK_UTIL.INSERT_ROW: '||SQLERRM ) ;
151 END IF;
152
153 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
154 THEN
155 FND_MSG_PUB.Add_Exc_Msg
156 (G_PKG_NAME, 'OE_Lots_Ack_Util.Insert_Row');
157 END IF;
158
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160
161 END Insert_Row;
162
163
164 PROCEDURE Delete_Row
165 ( p_orig_sys_document_ref IN VARCHAR2
166 , p_change_sequence IN VARCHAR2
167 , p_change_date IN DATE
168 , p_orig_sys_line_ref IN VARCHAR2
169 , p_orig_sys_shipment_ref IN VARCHAR2
170 , p_orig_sys_lot_serial_ref IN VARCHAR2
171 )
172 IS
173 --
174 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
175 --
176 BEGIN
177
178 DELETE FROM OE_LOTSERIAL_ACKS
179 WHERE ORIG_SYS_DOCUMENT_REF = p_orig_sys_document_ref
180 AND CHANGE_SEQUENCE = p_change_sequence
181 AND CHANGE_DATE = p_change_date
182 AND ORIG_SYS_LINE_REF = p_orig_sys_line_ref
183 AND ORIG_SYS_SHIPMENT_REF = p_orig_sys_shipment_ref
184 AND ORIG_SYS_LOT_SERIAL_REF = p_orig_sys_lot_serial_ref
185 ;
186
187 EXCEPTION
188
189 WHEN OTHERS THEN
190 IF l_debug_level > 0 THEN
191 oe_debug_pub.add( 'ENCOUNTERED OTHERS ERROR EXCEPTION IN OE_LOTS_ACK_UTIL.DELETE_ROW: '||SQLERRM ) ;
192 END IF;
193
194 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
195 THEN
196 FND_MSG_PUB.Add_Exc_Msg
197 (G_PKG_NAME, 'OE_Lots_Ack_Util.Delete_Row');
198 END IF;
199
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201
202 END Delete_Row;
203
204 END OE_Lots_Ack_Util;