[Home] [Help]
PACKAGE BODY: APPS.RCV_ASN_TRX_INSERT
Source
1 PACKAGE BODY RCV_ASN_TRX_INSERT as
2 /* $Header: RCVAHTXB.pls 115.7 2002/11/25 21:46:22 sbull ship $ */
3
4 PROCEDURE HANDLE_RCV_ASN_TRANSACTIONS (V_TRANS_TAB IN OUT NOCOPY RCV_SHIPMENT_OBJECT_SV.CASCADED_TRANS_TAB_TYPE,
5 V_HEADER_RECORD IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.HEADERRECTYPE) IS
6
7 V_CURRENT_INTERFACE_ID NUMBER;
8 V_PRIOR_INTERFACE_ID NUMBER;
9 I BINARY_INTEGER := 0;
10 E_O_T BINARY_INTEGER := 0;
11
12 X_ROWID VARCHAR2(255);
13 l_wms_return_status VARCHAR2(1);
14 l_wms_msg_count NUMBER;
15 l_wms_msg_data VARCHAR2(400);
16 lorgid NUMBER; -- For Bug 2110031
17 BEGIN
18
19 IF V_TRANS_TAB.COUNT > 0 then
20
21 i := V_TRANS_TAB.FIRST;
22 E_O_T := V_TRANS_TAB.LAST;
23
24 V_CURRENT_INTERFACE_ID := -999;
25 V_PRIOR_INTERFACE_ID := -999;
26
27 /* Delete only once from the rcv_transactions_interface table for every
28 group of pl/sql table rows that have the same interface id */
29
30
31 FOR j in i..E_O_T loop
32
33 ASN_DEBUG.PUT_LINE('Transaction Type ' || v_trans_tab(j).transaction_type);
34
35 V_CURRENT_INTERFACE_ID := V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID;
36
37 /* SELECT GROUP_SEQUENCE_ID_S.nextval into V_TRANS_TAB(j).GROUP_ID
38 from dual; -- Check whether this has to be unique */
39
40 -- Will use the group_id from the header_record as the pre-processor
41 -- needs the header and transactions group id to be the same
42
43 V_TRANS_TAB(j).GROUP_ID := V_HEADER_RECORD.HEADER_RECORD.GROUP_ID;
44 ASN_DEBUG.PUT_LINE('Group id ' || to_char(V_TRANS_TAB(j).GROUP_ID));
45
46 /* Assigning to_organization_id to lorgid which is passed
47 as a parameter to WMS_INSTALL function .Also added
48 debug messages .*/
49
50 lorgid := V_TRANS_TAB(j).TO_ORGANIZATION_ID;
51
52 ASN_DEBUG.PUT_LINE('organization id ' || to_char(V_TRANS_TAB(j).TO_ORGANIZATION_ID));
53 ASN_DEBUG.PUT_LINE('lorgid ' || to_char(lorgid));
54
55
56 IF V_CURRENT_INTERFACE_ID <> V_PRIOR_INTERFACE_ID then
57
58 ASN_DEBUG.PUT_LINE('Handle the original interface id ' || to_char(V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID));
59 ASN_DEBUG.PUT_LINE('delete from rcv_transactions_interface rowid ' || V_TRANS_TAB(j).ROW_ID);
60
61 RCV_TRX_INTERFACE_DELETE_PKG.Delete_Row(V_TRANS_TAB(j).ROW_ID);
62
63 /* V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID := NULL; */ -- need to maintain the interface transaction id
64 -- for error reporting
65 X_ROWID := NULL;
66
67 RCV_ASN_INTERFACE_TRX_INS_PKG.INSERT_ROW(
68 V_TRANS_TAB(j).ROW_ID,
69 V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID,
70 V_TRANS_TAB(j).GROUP_ID,
71 V_TRANS_TAB(j).LAST_UPDATE_DATE,
72 V_TRANS_TAB(j).LAST_UPDATED_BY,
73 V_TRANS_TAB(j).CREATION_DATE,
74 V_TRANS_TAB(j).CREATED_BY,
75 V_TRANS_TAB(j).LAST_UPDATE_LOGIN,
76 V_TRANS_TAB(j).REQUEST_ID,
77 V_TRANS_TAB(j).PROGRAM_APPLICATION_ID,
78 V_TRANS_TAB(j).PROGRAM_ID,
79 V_TRANS_TAB(j).PROGRAM_UPDATE_DATE,
80 V_TRANS_TAB(j).TRANSACTION_TYPE,
81 V_TRANS_TAB(j).TRANSACTION_DATE,
82 V_TRANS_TAB(j).PROCESSING_STATUS_CODE,
83 V_TRANS_TAB(j).PROCESSING_MODE_CODE,
84 V_TRANS_TAB(j).PROCESSING_REQUEST_ID,
85 V_TRANS_TAB(j).TRANSACTION_STATUS_CODE,
86 V_TRANS_TAB(j).CATEGORY_ID,
87 V_TRANS_TAB(j).QUANTITY,
88 V_TRANS_TAB(j).UNIT_OF_MEASURE,
89 V_TRANS_TAB(j).INTERFACE_SOURCE_CODE,
90 V_TRANS_TAB(j).INTERFACE_SOURCE_LINE_ID,
91 V_TRANS_TAB(j).INV_TRANSACTION_ID,
92 V_TRANS_TAB(j).ITEM_ID,
93 V_TRANS_TAB(j).ITEM_DESCRIPTION,
94 V_TRANS_TAB(j).ITEM_REVISION,
95 V_TRANS_TAB(j).UOM_CODE,
96 V_TRANS_TAB(j).EMPLOYEE_ID,
97 V_TRANS_TAB(j).AUTO_TRANSACT_CODE,
98 NVL(V_TRANS_TAB(j).SHIPMENT_HEADER_ID,V_HEADER_RECORD.HEADER_RECORD.RECEIPT_HEADER_ID),
99 V_TRANS_TAB(j).SHIPMENT_LINE_ID,
100 V_TRANS_TAB(j).SHIP_TO_LOCATION_ID,
101 V_TRANS_TAB(j).PRIMARY_QUANTITY,
102 V_TRANS_TAB(j).PRIMARY_UNIT_OF_MEASURE,
103 V_TRANS_TAB(j).RECEIPT_SOURCE_CODE,
104 V_TRANS_TAB(j).VENDOR_ID,
105 V_TRANS_TAB(j).VENDOR_SITE_ID,
106 V_TRANS_TAB(j).FROM_ORGANIZATION_ID,
107 V_TRANS_TAB(j).FROM_SUBINVENTORY,
108 V_TRANS_TAB(j).TO_ORGANIZATION_ID,
109 V_TRANS_TAB(j).INTRANSIT_OWNING_ORG_ID,
110 V_TRANS_TAB(j).ROUTING_HEADER_ID,
111 V_TRANS_TAB(j).ROUTING_STEP_ID,
112 V_TRANS_TAB(j).SOURCE_DOCUMENT_CODE,
113 V_TRANS_TAB(j).PARENT_TRANSACTION_ID,
114 V_TRANS_TAB(j).PO_HEADER_ID,
115 V_TRANS_TAB(j).PO_REVISION_NUM,
116 V_TRANS_TAB(j).PO_RELEASE_ID,
117 V_TRANS_TAB(j).PO_LINE_ID,
118 V_TRANS_TAB(j).PO_LINE_LOCATION_ID,
119 V_TRANS_TAB(j).PO_UNIT_PRICE,
120 V_TRANS_TAB(j).CURRENCY_CODE,
121 V_TRANS_TAB(j).CURRENCY_CONVERSION_TYPE,
122 V_TRANS_TAB(j).CURRENCY_CONVERSION_RATE,
123 V_TRANS_TAB(j).CURRENCY_CONVERSION_DATE,
124 V_TRANS_TAB(j).PO_DISTRIBUTION_ID,
125 V_TRANS_TAB(j).REQUISITION_LINE_ID,
126 V_TRANS_TAB(j).REQ_DISTRIBUTION_ID,
127 V_TRANS_TAB(j).CHARGE_ACCOUNT_ID,
128 V_TRANS_TAB(j).SUBSTITUTE_UNORDERED_CODE,
129 V_TRANS_TAB(j).RECEIPT_EXCEPTION_FLAG,
130 V_TRANS_TAB(j).ACCRUAL_STATUS_CODE,
131 V_TRANS_TAB(j).INSPECTION_STATUS_CODE,
132 V_TRANS_TAB(j).INSPECTION_QUALITY_CODE,
133 V_TRANS_TAB(j).DESTINATION_TYPE_CODE,
134 V_TRANS_TAB(j).DELIVER_TO_PERSON_ID,
135 V_TRANS_TAB(j).LOCATION_ID,
136 V_TRANS_TAB(j).DELIVER_TO_LOCATION_ID,
137 V_TRANS_TAB(j).SUBINVENTORY,
138 V_TRANS_TAB(j).LOCATOR_ID,
139 V_TRANS_TAB(j).WIP_ENTITY_ID,
140 V_TRANS_TAB(j).WIP_LINE_ID,
141 V_TRANS_TAB(j).DEPARTMENT_CODE,
142 V_TRANS_TAB(j).WIP_REPETITIVE_SCHEDULE_ID,
143 V_TRANS_TAB(j).WIP_OPERATION_SEQ_NUM,
144 V_TRANS_TAB(j).WIP_RESOURCE_SEQ_NUM,
145 V_TRANS_TAB(j).BOM_RESOURCE_ID,
146 V_TRANS_TAB(j).SHIPMENT_NUM,
147 V_TRANS_TAB(j).FREIGHT_CARRIER_CODE,
148 V_TRANS_TAB(j).BILL_OF_LADING,
149 V_TRANS_TAB(j).PACKING_SLIP,
150 V_TRANS_TAB(j).SHIPPED_DATE,
151 V_TRANS_TAB(j).EXPECTED_RECEIPT_DATE,
152 V_TRANS_TAB(j).ACTUAL_COST,
153 V_TRANS_TAB(j).TRANSFER_COST,
154 V_TRANS_TAB(j).TRANSPORTATION_COST,
155 V_TRANS_TAB(j).TRANSPORTATION_ACCOUNT_ID,
156 V_TRANS_TAB(j).NUM_OF_CONTAINERS,
157 V_TRANS_TAB(j).WAYBILL_AIRBILL_NUM,
158 V_TRANS_TAB(j).VENDOR_ITEM_NUM,
159 V_TRANS_TAB(j).VENDOR_LOT_NUM,
160 V_TRANS_TAB(j).RMA_REFERENCE,
161 V_TRANS_TAB(j).COMMENTS,
162 V_TRANS_TAB(j).ATTRIBUTE_CATEGORY,
163 V_TRANS_TAB(j).ATTRIBUTE1,
164 V_TRANS_TAB(j).ATTRIBUTE2,
165 V_TRANS_TAB(j).ATTRIBUTE3,
166 V_TRANS_TAB(j).ATTRIBUTE4,
167 V_TRANS_TAB(j).ATTRIBUTE5,
168 V_TRANS_TAB(j).ATTRIBUTE6,
169 V_TRANS_TAB(j).ATTRIBUTE7,
170 V_TRANS_TAB(j).ATTRIBUTE8,
171 V_TRANS_TAB(j).ATTRIBUTE9,
172 V_TRANS_TAB(j).ATTRIBUTE10,
173 V_TRANS_TAB(j).ATTRIBUTE11,
174 V_TRANS_TAB(j).ATTRIBUTE12,
175 V_TRANS_TAB(j).ATTRIBUTE13,
176 V_TRANS_TAB(j).ATTRIBUTE14,
177 V_TRANS_TAB(j).ATTRIBUTE15,
178 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE_CATEGORY,
179 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE1,
180 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE2,
181 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE3,
182 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE4,
183 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE5,
184 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE6,
185 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE7,
186 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE8,
187 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE9,
188 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE10,
189 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE11,
190 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE12,
191 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE13,
192 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE14,
193 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE15,
194 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE_CATEGORY,
195 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE1,
196 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE2,
197 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE3,
198 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE4,
199 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE5,
200 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE6,
201 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE7,
202 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE8,
203 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE9,
204 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE10,
205 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE11,
206 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE12,
207 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE13,
208 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE14,
209 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE15,
210 V_TRANS_TAB(j).USSGL_TRANSACTION_CODE,
211 V_TRANS_TAB(j).GOVERNMENT_CONTEXT,
212 V_TRANS_TAB(j).REASON_ID,
213 V_TRANS_TAB(j).DESTINATION_CONTEXT,
214 V_TRANS_TAB(j).SOURCE_DOC_QUANTITY,
215 V_TRANS_TAB(j).SOURCE_DOC_UNIT_OF_MEASURE,
216 V_TRANS_TAB(j).MOVEMENT_ID,
217 V_TRANS_TAB(j).HEADER_INTERFACE_ID,
218 V_TRANS_TAB(j).VENDOR_CUM_SHIPPED_QTY,
219 V_TRANS_TAB(j).ITEM_NUM,
220 V_TRANS_TAB(j).DOCUMENT_NUM,
221 V_TRANS_TAB(j).DOCUMENT_LINE_NUM,
222 V_TRANS_TAB(j).TRUCK_NUM,
223 V_TRANS_TAB(j).SHIP_TO_LOCATION_CODE,
224 V_TRANS_TAB(j).CONTAINER_NUM,
225 V_TRANS_TAB(j).SUBSTITUTE_ITEM_NUM,
226 V_TRANS_TAB(j).NOTICE_UNIT_PRICE,
227 V_TRANS_TAB(j).ITEM_CATEGORY,
228 V_TRANS_TAB(j).LOCATION_CODE,
229 V_TRANS_TAB(j).VENDOR_NAME,
230 V_TRANS_TAB(j).VENDOR_NUM,
231 V_TRANS_TAB(j).VENDOR_SITE_CODE,
232 V_TRANS_TAB(j).FROM_ORGANIZATION_CODE,
233 V_TRANS_TAB(j).TO_ORGANIZATION_CODE,
234 V_TRANS_TAB(j).INTRANSIT_OWNING_ORG_CODE,
235 V_TRANS_TAB(j).ROUTING_CODE,
236 V_TRANS_TAB(j).ROUTING_STEP,
237 V_TRANS_TAB(j).RELEASE_NUM,
238 V_TRANS_TAB(j).DOCUMENT_SHIPMENT_LINE_NUM,
239 V_TRANS_TAB(j).DOCUMENT_DISTRIBUTION_NUM,
240 V_TRANS_TAB(j).DELIVER_TO_PERSON_NAME,
241 V_TRANS_TAB(j).DELIVER_TO_LOCATION_CODE,
242 V_TRANS_TAB(j).USE_MTL_LOT,
243 V_TRANS_TAB(j).USE_MTL_SERIAL,
244 V_TRANS_TAB(j).LOCATOR,
245 V_TRANS_TAB(j).REASON_NAME,
246 V_TRANS_TAB(j).VALIDATION_FLAG,
247 V_TRANS_TAB(j).SUBSTITUTE_ITEM_ID,
248 V_TRANS_TAB(j).QUANTITY_SHIPPED,
249 V_TRANS_TAB(j).QUANTITY_INVOICED,
250 V_TRANS_TAB(j).TAX_NAME,
251 V_TRANS_TAB(j).TAX_AMOUNT,
252 V_TRANS_TAB(j).REQ_NUM,
253 V_TRANS_TAB(j).REQ_LINE_NUM,
254 V_TRANS_TAB(j).REQ_DISTRIBUTION_NUM,
255 V_TRANS_TAB(j).WIP_ENTITY_NAME,
256 V_TRANS_TAB(j).WIP_LINE_CODE,
257 V_TRANS_TAB(j).RESOURCE_CODE,
258 V_TRANS_TAB(j).SHIPMENT_LINE_STATUS_CODE,
259 V_TRANS_TAB(j).BARCODE_LABEL,
260 V_TRANS_TAB(j).COUNTRY_OF_ORIGIN_CODE);
261
262 ASN_DEBUG.PUT_LINE('RowId ' || V_TRANS_TAB(j).ROW_ID);
263 ASN_DEBUG.PUT_LINE('Interface Id ' || to_char(V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID));
264
265 V_PRIOR_INTERFACE_ID := V_CURRENT_INTERFACE_ID;
266 V_TRANS_TAB.delete(j);
267
268 ELSE
269
270 ASN_DEBUG.PUT_LINE('insert into rcv_transactions_interface with new id ');
271
272 /* Since we are inserting 1-> many rows need to generate a new interface id */
273
274 V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID := NULL;
275 X_ROWID := NULL;
276
277 RCV_ASN_INTERFACE_TRX_INS_PKG.INSERT_ROW(
278 V_TRANS_TAB(j).ROW_ID,
279 V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID,
280 V_TRANS_TAB(j).GROUP_ID,
281 V_TRANS_TAB(j).LAST_UPDATE_DATE,
282 V_TRANS_TAB(j).LAST_UPDATED_BY,
283 V_TRANS_TAB(j).CREATION_DATE,
284 V_TRANS_TAB(j).CREATED_BY,
285 V_TRANS_TAB(j).LAST_UPDATE_LOGIN,
286 V_TRANS_TAB(j).REQUEST_ID,
287 V_TRANS_TAB(j).PROGRAM_APPLICATION_ID,
288 V_TRANS_TAB(j).PROGRAM_ID,
289 V_TRANS_TAB(j).PROGRAM_UPDATE_DATE,
290 V_TRANS_TAB(j).TRANSACTION_TYPE,
291 V_TRANS_TAB(j).TRANSACTION_DATE,
292 V_TRANS_TAB(j).PROCESSING_STATUS_CODE,
293 V_TRANS_TAB(j).PROCESSING_MODE_CODE,
294 V_TRANS_TAB(j).PROCESSING_REQUEST_ID,
295 V_TRANS_TAB(j).TRANSACTION_STATUS_CODE,
296 V_TRANS_TAB(j).CATEGORY_ID,
297 V_TRANS_TAB(j).QUANTITY,
298 V_TRANS_TAB(j).UNIT_OF_MEASURE,
299 V_TRANS_TAB(j).INTERFACE_SOURCE_CODE,
300 V_TRANS_TAB(j).INTERFACE_SOURCE_LINE_ID,
301 V_TRANS_TAB(j).INV_TRANSACTION_ID,
302 V_TRANS_TAB(j).ITEM_ID,
303 V_TRANS_TAB(j).ITEM_DESCRIPTION,
304 V_TRANS_TAB(j).ITEM_REVISION,
305 V_TRANS_TAB(j).UOM_CODE,
306 V_TRANS_TAB(j).EMPLOYEE_ID,
307 V_TRANS_TAB(j).AUTO_TRANSACT_CODE,
308 NVL(V_TRANS_TAB(j).SHIPMENT_HEADER_ID, V_HEADER_RECORD.HEADER_RECORD.RECEIPT_HEADER_ID),
309 V_TRANS_TAB(j).SHIPMENT_LINE_ID,
310 V_TRANS_TAB(j).SHIP_TO_LOCATION_ID,
311 V_TRANS_TAB(j).PRIMARY_QUANTITY,
312 V_TRANS_TAB(j).PRIMARY_UNIT_OF_MEASURE,
313 V_TRANS_TAB(j).RECEIPT_SOURCE_CODE,
314 V_TRANS_TAB(j).VENDOR_ID,
315 V_TRANS_TAB(j).VENDOR_SITE_ID,
316 V_TRANS_TAB(j).FROM_ORGANIZATION_ID,
317 V_TRANS_TAB(j).FROM_SUBINVENTORY,
318 V_TRANS_TAB(j).TO_ORGANIZATION_ID,
319 V_TRANS_TAB(j).INTRANSIT_OWNING_ORG_ID,
320 V_TRANS_TAB(j).ROUTING_HEADER_ID,
321 V_TRANS_TAB(j).ROUTING_STEP_ID,
322 V_TRANS_TAB(j).SOURCE_DOCUMENT_CODE,
323 V_TRANS_TAB(j).PARENT_TRANSACTION_ID,
324 V_TRANS_TAB(j).PO_HEADER_ID,
325 V_TRANS_TAB(j).PO_REVISION_NUM,
326 V_TRANS_TAB(j).PO_RELEASE_ID,
327 V_TRANS_TAB(j).PO_LINE_ID,
328 V_TRANS_TAB(j).PO_LINE_LOCATION_ID,
329 V_TRANS_TAB(j).PO_UNIT_PRICE,
330 V_TRANS_TAB(j).CURRENCY_CODE,
331 V_TRANS_TAB(j).CURRENCY_CONVERSION_TYPE,
332 V_TRANS_TAB(j).CURRENCY_CONVERSION_RATE,
333 V_TRANS_TAB(j).CURRENCY_CONVERSION_DATE,
334 V_TRANS_TAB(j).PO_DISTRIBUTION_ID,
335 V_TRANS_TAB(j).REQUISITION_LINE_ID,
336 V_TRANS_TAB(j).REQ_DISTRIBUTION_ID,
337 V_TRANS_TAB(j).CHARGE_ACCOUNT_ID,
338 V_TRANS_TAB(j).SUBSTITUTE_UNORDERED_CODE,
339 V_TRANS_TAB(j).RECEIPT_EXCEPTION_FLAG,
340 V_TRANS_TAB(j).ACCRUAL_STATUS_CODE,
341 V_TRANS_TAB(j).INSPECTION_STATUS_CODE,
342 V_TRANS_TAB(j).INSPECTION_QUALITY_CODE,
343 V_TRANS_TAB(j).DESTINATION_TYPE_CODE,
344 V_TRANS_TAB(j).DELIVER_TO_PERSON_ID,
345 V_TRANS_TAB(j).LOCATION_ID,
346 V_TRANS_TAB(j).DELIVER_TO_LOCATION_ID,
347 V_TRANS_TAB(j).SUBINVENTORY,
348 V_TRANS_TAB(j).LOCATOR_ID,
349 V_TRANS_TAB(j).WIP_ENTITY_ID,
350 V_TRANS_TAB(j).WIP_LINE_ID,
351 V_TRANS_TAB(j).DEPARTMENT_CODE,
352 V_TRANS_TAB(j).WIP_REPETITIVE_SCHEDULE_ID,
353 V_TRANS_TAB(j).WIP_OPERATION_SEQ_NUM,
354 V_TRANS_TAB(j).WIP_RESOURCE_SEQ_NUM,
355 V_TRANS_TAB(j).BOM_RESOURCE_ID,
356 V_TRANS_TAB(j).SHIPMENT_NUM,
357 V_TRANS_TAB(j).FREIGHT_CARRIER_CODE,
358 V_TRANS_TAB(j).BILL_OF_LADING,
359 V_TRANS_TAB(j).PACKING_SLIP,
360 V_TRANS_TAB(j).SHIPPED_DATE,
361 V_TRANS_TAB(j).EXPECTED_RECEIPT_DATE,
362 V_TRANS_TAB(j).ACTUAL_COST,
363 V_TRANS_TAB(j).TRANSFER_COST,
364 V_TRANS_TAB(j).TRANSPORTATION_COST,
365 V_TRANS_TAB(j).TRANSPORTATION_ACCOUNT_ID,
366 V_TRANS_TAB(j).NUM_OF_CONTAINERS,
367 V_TRANS_TAB(j).WAYBILL_AIRBILL_NUM,
368 V_TRANS_TAB(j).VENDOR_ITEM_NUM,
369 V_TRANS_TAB(j).VENDOR_LOT_NUM,
370 V_TRANS_TAB(j).RMA_REFERENCE,
371 V_TRANS_TAB(j).COMMENTS,
372 V_TRANS_TAB(j).ATTRIBUTE_CATEGORY,
373 V_TRANS_TAB(j).ATTRIBUTE1,
374 V_TRANS_TAB(j).ATTRIBUTE2,
375 V_TRANS_TAB(j).ATTRIBUTE3,
376 V_TRANS_TAB(j).ATTRIBUTE4,
377 V_TRANS_TAB(j).ATTRIBUTE5,
378 V_TRANS_TAB(j).ATTRIBUTE6,
379 V_TRANS_TAB(j).ATTRIBUTE7,
380 V_TRANS_TAB(j).ATTRIBUTE8,
381 V_TRANS_TAB(j).ATTRIBUTE9,
382 V_TRANS_TAB(j).ATTRIBUTE10,
383 V_TRANS_TAB(j).ATTRIBUTE11,
384 V_TRANS_TAB(j).ATTRIBUTE12,
385 V_TRANS_TAB(j).ATTRIBUTE13,
386 V_TRANS_TAB(j).ATTRIBUTE14,
387 V_TRANS_TAB(j).ATTRIBUTE15,
388 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE_CATEGORY,
389 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE1,
390 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE2,
391 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE3,
392 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE4,
393 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE5,
394 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE6,
395 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE7,
396 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE8,
397 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE9,
398 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE10,
399 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE11,
400 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE12,
401 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE13,
402 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE14,
403 V_TRANS_TAB(j).SHIP_HEAD_ATTRIBUTE15,
404 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE_CATEGORY,
405 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE1,
406 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE2,
407 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE3,
408 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE4,
409 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE5,
410 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE6,
411 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE7,
412 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE8,
413 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE9,
414 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE10,
415 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE11,
416 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE12,
417 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE13,
418 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE14,
419 V_TRANS_TAB(j).SHIP_LINE_ATTRIBUTE15,
420 V_TRANS_TAB(j).USSGL_TRANSACTION_CODE,
421 V_TRANS_TAB(j).GOVERNMENT_CONTEXT,
422 V_TRANS_TAB(j).REASON_ID,
423 V_TRANS_TAB(j).DESTINATION_CONTEXT,
424 V_TRANS_TAB(j).SOURCE_DOC_QUANTITY,
425 V_TRANS_TAB(j).SOURCE_DOC_UNIT_OF_MEASURE,
426 V_TRANS_TAB(j).MOVEMENT_ID,
427 V_TRANS_TAB(j).HEADER_INTERFACE_ID,
428 V_TRANS_TAB(j).VENDOR_CUM_SHIPPED_QTY,
429 V_TRANS_TAB(j).ITEM_NUM,
430 V_TRANS_TAB(j).DOCUMENT_NUM,
431 V_TRANS_TAB(j).DOCUMENT_LINE_NUM,
432 V_TRANS_TAB(j).TRUCK_NUM,
433 V_TRANS_TAB(j).SHIP_TO_LOCATION_CODE,
434 V_TRANS_TAB(j).CONTAINER_NUM,
435 V_TRANS_TAB(j).SUBSTITUTE_ITEM_NUM,
436 V_TRANS_TAB(j).NOTICE_UNIT_PRICE,
437 V_TRANS_TAB(j).ITEM_CATEGORY,
438 V_TRANS_TAB(j).LOCATION_CODE,
439 V_TRANS_TAB(j).VENDOR_NAME,
440 V_TRANS_TAB(j).VENDOR_NUM,
441 V_TRANS_TAB(j).VENDOR_SITE_CODE,
442 V_TRANS_TAB(j).FROM_ORGANIZATION_CODE,
443 V_TRANS_TAB(j).TO_ORGANIZATION_CODE,
444 V_TRANS_TAB(j).INTRANSIT_OWNING_ORG_CODE,
445 V_TRANS_TAB(j).ROUTING_CODE,
446 V_TRANS_TAB(j).ROUTING_STEP,
447 V_TRANS_TAB(j).RELEASE_NUM,
448 V_TRANS_TAB(j).DOCUMENT_SHIPMENT_LINE_NUM,
449 V_TRANS_TAB(j).DOCUMENT_DISTRIBUTION_NUM,
450 V_TRANS_TAB(j).DELIVER_TO_PERSON_NAME,
451 V_TRANS_TAB(j).DELIVER_TO_LOCATION_CODE,
452 V_TRANS_TAB(j).USE_MTL_LOT,
453 V_TRANS_TAB(j).USE_MTL_SERIAL,
454 V_TRANS_TAB(j).LOCATOR,
455 V_TRANS_TAB(j).REASON_NAME,
456 V_TRANS_TAB(j).VALIDATION_FLAG,
457 V_TRANS_TAB(j).SUBSTITUTE_ITEM_ID,
458 V_TRANS_TAB(j).QUANTITY_SHIPPED,
459 V_TRANS_TAB(j).QUANTITY_INVOICED,
460 V_TRANS_TAB(j).TAX_NAME,
461 V_TRANS_TAB(j).TAX_AMOUNT,
462 V_TRANS_TAB(j).REQ_NUM,
463 V_TRANS_TAB(j).REQ_LINE_NUM,
464 V_TRANS_TAB(j).REQ_DISTRIBUTION_NUM,
465 V_TRANS_TAB(j).WIP_ENTITY_NAME,
466 V_TRANS_TAB(j).WIP_LINE_CODE,
467 V_TRANS_TAB(j).RESOURCE_CODE,
468 V_TRANS_TAB(j).SHIPMENT_LINE_STATUS_CODE,
469 V_TRANS_TAB(j).BARCODE_LABEL,
470 V_TRANS_TAB(j).COUNTRY_OF_ORIGIN_CODE);
471
472 ASN_DEBUG.PUT_LINE('RowId ' || V_TRANS_TAB(j).ROW_ID);
473 ASN_DEBUG.PUT_LINE('Interface Id ' || to_char(V_TRANS_TAB(j).INTERFACE_TRANSACTION_ID));
474
475 V_TRANS_TAB.delete(j);
476 END IF;
477
478 /* Checking if WMS is intalled before calling WMS_ASN_INTERFACE_PROCESS
479 for Bug 2110031 */
480
481 if( WMS_INSTALL.check_install(l_wms_return_status,l_wms_msg_count,l_wms_msg_data,lorgid) ) then
482
483 begin
484
485 WMS_ASN_INTERFACE.PROCESS(l_wms_return_status, l_wms_msg_count, l_wms_msg_data, v_current_interface_id);
486
487 exception
488 when others then
489 null;
490 end;
491
492 end if;
493
494
495 END LOOP;
496
497 END IF;
498
499 END HANDLE_RCV_ASN_TRANSACTIONS;
500
501 PROCEDURE INSERT_CANCELLED_ASN_LINES (V_HEADER_RECORD IN OUT NOCOPY RCV_SHIPMENT_HEADER_SV.HEADERRECTYPE) IS
502
503 BEGIN
504
505 -- delete any asn lines that have been sent
506
507 asn_debug.put_line('Delete any asn lines that have been sent');
508
509 delete from rcv_transactions_interface
510 where header_interface_id = v_header_record.header_record.header_interface_id;
511
512 -- Insert lines from rcv_shipment_lines into rcv_transactions_interface
513
514 -- Make sure we don't inset cancelled lines and lines that are waiting to
515 -- be cancelled in rti
516 -- The transaction processor will then cancel the lines
517
518 -- Bug 587603 Inserting processing request id for CANCEL otherwise
519 -- transaction processor will not look at it.
520
521 INSERT INTO RCV_TRANSACTIONS_INTERFACE
522 (Interface_Transaction_Id ,
523 Header_interface_id ,
524 Group_Id ,
525 Last_Update_Date ,
526 Last_Updated_By ,
527 Last_Update_Login ,
528 creation_date ,
529 created_by ,
530 Transaction_Type ,
531 Transaction_Date ,
532 Processing_Status_Code ,
533 Processing_Mode_Code ,
534 Transaction_Status_Code ,
535 Category_Id ,
536 Quantity ,
537 Unit_Of_Measure ,
538 Interface_Source_Code ,
539 Item_Id ,
540 Item_Description ,
541 Employee_Id ,
542 Auto_Transact_Code ,
543 Receipt_Source_Code ,
544 Vendor_Id ,
545 To_Organization_Id ,
546 Source_Document_Code ,
547 Po_Header_Id ,
548 Po_Line_Id ,
549 Po_Line_Location_Id ,
550 Shipment_Header_Id ,
551 SHIPMENT_LINE_ID,
552 DESTINATION_TYPE_CODE,
553 processing_request_id)
554 SELECT
555 RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
556 V_header_record.header_record.header_interface_id,
557 V_header_record.header_record.group_id,
558 V_header_record.header_record.last_update_date,
559 V_header_record.header_record.last_updated_by,
560 V_header_record.header_record.last_update_login,
561 V_header_record.header_record.creation_date,
562 V_header_record.header_record.created_by,
563 'CANCEL',
564 nvl(V_header_record.header_record.notice_creation_date,sysdate),
565 'RUNNING', -- This has to be set to running otherwise C code in rvtbm
566 -- will not pick it up
567 'BATCH',
568 'PENDING',
569 rsl.category_id,
570 rsl.quantity_shipped,
571 rsl.unit_of_measure,
572 'RCV',
573 rsl.item_id,
574 rsl.item_description,
575 rsl.employee_id,
576 'CANCEL',
577 'VENDOR',
578 v_header_record.header_record.vendor_id,
579 rsl.to_organization_id,
580 'PO',
581 rsl.po_header_id,
582 rsl.po_line_id,
583 rsl.po_line_location_id,
584 rsl.shipment_header_id,
585 rsl.shipment_line_id,
586 rsl.destination_type_code, V_header_record.header_record.processing_request_id
587 FROM rcv_shipment_lines rsl
588 WHERE rsl.shipment_header_id = V_header_record.header_record.receipt_header_id and
589 rsl.shipment_line_status_code <> 'CANCELLED' and
590 not exists (select 'x' from rcv_transactions_interface rti
591 where
592 rti.shipment_line_id = rsl.shipment_line_id and
593 rti.shipment_header_id = rsl.shipment_header_id and
594 rti.transaction_type = 'CANCEL' and
595 rti.shipment_header_id = V_header_record.header_record.receipt_header_id) ;
596
597 END INSERT_CANCELLED_ASN_LINES;
598
599 END RCV_ASN_TRX_INSERT;