1 PACKAGE BODY RCV_LOT_SERIAL_SV AS
2 /* $Header: RCVTXLSB.pls 120.2 2005/06/21 18:57:50 wkunz noship $*/
3
4 /*=============================================================================
5 Name: create_rcv_lotserial
6 Desc: Create the necessary rcv_lots_interface and rcv_serials_interface
7 rows based on the rows created in the mtl_transactions_lots_temp
8 and the mtl_serial_numbers_temp table.
9 There is an issue here between v10 and 10sc.
10 In 10 we inserted rows into the rcv_lots_interface
11 and rcv_serials_interface tables through the
12 lot and serial forms. In 10sc we are using the Inventory lot and
13 serial forms which insert into the mtl_transaction_lots_temp and
14 the mtl_serial_numbers_temp table. The issue here is that if the
15 transaction_interface row was created by a 10 client then we want
16 to continue to insert into the mtl_ tables. If this trx was
17 generated through a 10sc client then we need to insert into the
18 10sc tables. We are adding a flag use_mtl_lot_serial that is null
19 allowable to tell us whether to use the rcv_ tables or the mtl_
20 tables)
21
22 Args: IN: interface_trx_id - ID of the transaction to be rejected.
23 IN: status - New status of the transaction
24 Reqs:
25 Mods:
26 Err : return(FALSE) on error. Error messages returned on AOL message stack
27 Algr: update rcv_transactions_interface to set new status
28 Note:
29 =============================================================================*/
30 PROCEDURE create_rcv_lotserial(interface_trx_id IN NUMBER,
31 use_mtl_lot IN NUMBER,
32 use_mtl_serial IN NUMBER) IS
33
34 X_progress VARCHAR2(4) := '000';
35
36 BEGIN
37
38 /*
39 ** Insert into the lots interface. The item is either under lot control
40 ** only or is under both lot and serial control
41 */
42 IF (use_mtl_lot = 2 OR use_mtl_serial = 2) THEN
43
44 X_progress := '010';
45
46 INSERT INTO rcv_lots_interface (
47 INTERFACE_TRANSACTION_ID,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_LOGIN,
53 REQUEST_ID,
54 PROGRAM_APPLICATION_ID,
55 PROGRAM_ID,
56 PROGRAM_UPDATE_DATE,
57 LOT_NUM,
58 QUANTITY,
59 TRANSACTION_DATE,
60 EXPIRATION_DATE,
61 PRIMARY_QUANTITY,
62 ITEM_ID,
63 SHIPMENT_LINE_ID,
64 --Bug Fix # 1548597.
65 --Added the following Columns to Insert them into RCV_LOTS_INTERFACE.
66 SECONDARY_QUANTITY,
67 SUBLOT_NUM
68 --End of Modification for Bug # 1548597.
69 )
70 SELECT rti.interface_transaction_id,
71 rti.last_update_date,
72 rti.last_updated_by,
73 rti.creation_date,
74 rti.created_by,
75 rti.last_update_login,
76 rti.request_id,
77 rti.program_application_id,
78 rti.program_id,
79 rti.program_update_date,
80 mtlt.lot_number,
81 mtlt.transaction_quantity,
82 rti.transaction_date,
83 mtlt.lot_expiration_date,
84 mtlt.primary_quantity,
85 rti.item_id,
86 rti.shipment_line_id,
87 --Bug Fix # 1548597.
88 --Added the following Columns to Insert into RCV_LOTS_INTERFACE table.
89 mtlt.secondary_quantity,
90 mtlt.sublot_num
91 --End of Modification for Bug # 1548597.
92 FROM rcv_transactions_interface rti,
93 mtl_transaction_lots_temp mtlt
94 WHERE rti.interface_transaction_id = interface_trx_id
95 AND mtlt.transaction_temp_id = rti.interface_transaction_id;
96
97 END IF;
98
99 /*
100 ** The item is only under serial control if the use_mtl_serial field
101 ** is equal to 2.
102 ** Insert into the serial interface. The item is under both lot and
103 ** serial control if use_mtl_serial is equal to 5. The serial form
104 ** creates the serial rows with the transaction_temp_id equal to the
105 ** interface_transaction_id
106 */
107 IF (use_mtl_serial = 2) THEN
108
109 X_progress := '020';
110
111 INSERT INTO rcv_serials_interface (
112 INTERFACE_TRANSACTION_ID,
113 LAST_UPDATE_DATE,
114 LAST_UPDATED_BY,
115 CREATION_DATE,
116 CREATED_BY,
117 LAST_UPDATE_LOGIN,
118 REQUEST_ID,
119 PROGRAM_APPLICATION_ID,
120 PROGRAM_ID,
121 PROGRAM_UPDATE_DATE,
122 TRANSACTION_DATE,
123 FM_SERIAL_NUM,
124 TO_SERIAL_NUM,
125 SERIAL_PREFIX,
126 LOT_NUM,
127 VENDOR_SERIAL_NUM,
128 VENDOR_LOT_NUM,
129 ITEM_ID,
130 ORGANIZATION_ID)
131 SELECT rti.interface_transaction_id,
132 rti.last_update_date,
133 rti.last_updated_by,
134 rti.creation_date,
135 rti.created_by,
136 rti.last_update_login,
137 rti.request_id,
138 rti.program_application_id,
139 rti.program_id,
140 rti.program_update_date,
141 rti.transaction_date,
142 mtst.fm_serial_number,
143 mtst.to_serial_number,
144 mtst.serial_prefix,
145 null,
146 null,
147 rti.vendor_lot_num,
148 rti.item_id,
149 rti.to_organization_id
150 FROM rcv_transactions_interface rti,
151 mtl_serial_numbers_temp mtst
152 WHERE rti.interface_transaction_id = interface_trx_id
153 AND mtst.transaction_temp_id = rti.interface_transaction_id;
154
155 /*
156 ** The item is only under serial control if the use_mtl_serial field
157 ** is equal to 2.
158 ** Insert into the serial interface. The item is under both lot and
159 ** serial control if use_mtl_serial is equal to 5. The serial form
160 ** creates the serial rows with the transaction_temp_id equal to the
161 ** interface_transaction_id
162 */
163 ELSIF (use_mtl_serial = 5) THEN
164
165 X_progress := '030';
166
167 INSERT INTO rcv_serials_interface (
168 INTERFACE_TRANSACTION_ID,
169 LAST_UPDATE_DATE,
170 LAST_UPDATED_BY,
171 CREATION_DATE,
172 CREATED_BY,
173 LAST_UPDATE_LOGIN,
174 REQUEST_ID,
175 PROGRAM_APPLICATION_ID,
176 PROGRAM_ID,
177 PROGRAM_UPDATE_DATE,
178 TRANSACTION_DATE,
179 FM_SERIAL_NUM,
180 TO_SERIAL_NUM,
181 SERIAL_PREFIX,
182 LOT_NUM,
183 VENDOR_SERIAL_NUM,
184 VENDOR_LOT_NUM,
185 ITEM_ID,
186 ORGANIZATION_ID)
187 SELECT rti.interface_transaction_id,
188 rti.last_update_date,
189 rti.last_updated_by,
190 rti.creation_date,
191 rti.created_by,
192 rti.last_update_login,
193 rti.request_id,
194 rti.program_application_id,
195 rti.program_id,
196 rti.program_update_date,
197 rti.transaction_date,
198 mtst.fm_serial_number,
199 mtst.to_serial_number,
200 mtst.serial_prefix,
201 mtlt.lot_number,
202 null,
203 rti.vendor_lot_num,
204 rti.item_id,
205 rti.to_organization_id
206 FROM rcv_transactions_interface rti,
207 mtl_transaction_lots_temp mtlt,
208 mtl_serial_numbers_temp mtst
209 WHERE rti.interface_transaction_id = interface_trx_id
210 AND mtlt.transaction_temp_id = rti.interface_transaction_id
211 AND mtlt.SERIAL_TRANSACTION_TEMP_ID =
212 mtst.transaction_temp_id;
213
214 END IF;
215
216 RETURN;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 po_message_s.sql_error('create_rcv_lotserial', X_progress, sqlcode);
221 RAISE;
222
223 END create_rcv_lotserial;
224
225 END RCV_LOT_SERIAL_SV;
226
[Home] [Help]