[Home] [Help]
PACKAGE BODY: APPS.RCV_TRX_INTERFACE_INSERT_PKG
Source
1 PACKAGE BODY RCV_TRX_INTERFACE_INSERT_PKG as
2 /* $Header: RCVTIR1B.pls 120.3.12010000.2 2008/10/09 17:19:00 vthevark ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Interface_Transaction_Id IN OUT NOCOPY NUMBER,
6 X_Group_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Transaction_Type VARCHAR2,
13 X_Transaction_Date DATE,
14 X_Processing_Status_Code VARCHAR2,
15 X_Processing_Mode_Code VARCHAR2,
16 X_Processing_Request_Id NUMBER,
17 X_Transaction_Status_Code VARCHAR2,
18 X_Category_Id NUMBER,
19 X_Quantity NUMBER,
20 X_Unit_Of_Measure VARCHAR2,
21 X_Interface_Source_Code VARCHAR2,
22 X_Interface_Source_Line_Id NUMBER,
23 X_Inv_Transaction_Id NUMBER,
24 X_Item_Id NUMBER,
25 X_Item_Description VARCHAR2,
26 X_Item_Revision VARCHAR2,
27 X_Uom_Code VARCHAR2,
28 X_Employee_Id NUMBER,
29 X_Auto_Transact_Code VARCHAR2,
30 X_Shipment_Header_Id NUMBER,
31 X_Shipment_Line_Id NUMBER,
32 X_Ship_To_Location_Id NUMBER,
33 X_Primary_Quantity NUMBER,
34 X_Primary_Unit_Of_Measure VARCHAR2,
35 X_Receipt_Source_Code VARCHAR2,
36 X_Vendor_Id NUMBER,
37 X_Vendor_Site_Id NUMBER,
38 X_From_Organization_Id NUMBER,
39 X_To_Organization_Id NUMBER,
40 X_Routing_Header_Id NUMBER,
41 X_Routing_Step_Id NUMBER,
42 X_Source_Document_Code VARCHAR2,
43 X_Parent_Transaction_Id NUMBER,
44 X_Po_Header_Id NUMBER,
45 X_Po_Revision_Num NUMBER,
46 X_Po_Release_Id NUMBER,
47 X_Po_Line_Id NUMBER,
48 X_Po_Line_Location_Id NUMBER,
49 X_Po_Unit_Price NUMBER,
50 X_Currency_Code VARCHAR2,
51 X_Currency_Conversion_Type VARCHAR2,
52 X_Currency_Conversion_Rate NUMBER,
53 X_Currency_Conversion_Date DATE,
54 X_Po_Distribution_Id NUMBER,
55 X_Requisition_Line_Id NUMBER,
56 X_Req_Distribution_Id NUMBER,
57 X_Charge_Account_Id NUMBER,
58 X_Substitute_Unordered_Code VARCHAR2,
59 X_Receipt_Exception_Flag VARCHAR2,
60 X_Accrual_Status_Code VARCHAR2,
61 X_Inspection_Status_Code VARCHAR2,
62 X_Inspection_Quality_Code VARCHAR2,
63 X_Destination_Type_Code VARCHAR2,
64 X_Deliver_To_Person_Id NUMBER,
65 X_Location_Id NUMBER,
66 X_Deliver_To_Location_Id NUMBER,
67 X_Subinventory VARCHAR2,
68 X_Locator_Id NUMBER,
69 X_Wip_Entity_Id NUMBER,
70 X_Wip_Line_Id NUMBER,
71 X_Department_Code VARCHAR2,
72 X_Wip_Repetitive_Schedule_Id NUMBER,
73 X_Wip_Operation_Seq_Num NUMBER,
74 X_Wip_Resource_Seq_Num NUMBER,
75 X_Bom_Resource_Id NUMBER,
76 X_Shipment_Num VARCHAR2,
77 X_Freight_Carrier_Code VARCHAR2,
78 X_Bill_Of_Lading VARCHAR2,
79 X_Packing_Slip VARCHAR2,
80 X_Shipped_Date DATE,
81 X_Expected_Receipt_Date DATE,
82 X_Actual_Cost NUMBER,
83 X_Transfer_Cost NUMBER,
84 X_Transportation_Cost NUMBER,
85 X_Transportation_Account_Id NUMBER,
86 X_Num_Of_Containers NUMBER,
87 X_Waybill_Airbill_Num VARCHAR2,
88 X_Vendor_Item_Num VARCHAR2,
89 X_Vendor_Lot_Num VARCHAR2,
90 X_Rma_Reference VARCHAR2,
91 X_Comments VARCHAR2,
92 X_Attribute_Category VARCHAR2,
93 X_Attribute1 VARCHAR2,
94 X_Attribute2 VARCHAR2,
95 X_Attribute3 VARCHAR2,
96 X_Attribute4 VARCHAR2,
97 X_Attribute5 VARCHAR2,
98 X_Attribute6 VARCHAR2,
99 X_Attribute7 VARCHAR2,
100 X_Attribute8 VARCHAR2,
101 X_Attribute9 VARCHAR2,
102 X_Attribute10 VARCHAR2,
103 X_Attribute11 VARCHAR2,
104 X_Attribute12 VARCHAR2,
105 X_Attribute13 VARCHAR2,
106 X_Attribute14 VARCHAR2,
107 X_Attribute15 VARCHAR2,
108 X_Ship_Head_Attribute_Category VARCHAR2,
109 X_Ship_Head_Attribute1 VARCHAR2,
110 X_Ship_Head_Attribute2 VARCHAR2,
111 X_Ship_Head_Attribute3 VARCHAR2,
112 X_Ship_Head_Attribute4 VARCHAR2,
113 X_Ship_Head_Attribute5 VARCHAR2,
114 X_Ship_Head_Attribute6 VARCHAR2,
115 X_Ship_Head_Attribute7 VARCHAR2,
116 X_Ship_Head_Attribute8 VARCHAR2,
117 X_Ship_Head_Attribute9 VARCHAR2,
118 X_Ship_Head_Attribute10 VARCHAR2,
119 X_Ship_Head_Attribute11 VARCHAR2,
120 X_Ship_Head_Attribute12 VARCHAR2,
121 X_Ship_Head_Attribute13 VARCHAR2,
122 X_Ship_Head_Attribute14 VARCHAR2,
123 X_Ship_Head_Attribute15 VARCHAR2,
124 X_Ship_Line_Attribute_Category VARCHAR2,
125 X_Ship_Line_Attribute1 VARCHAR2,
126 X_Ship_Line_Attribute2 VARCHAR2,
127 X_Ship_Line_Attribute3 VARCHAR2,
128 X_Ship_Line_Attribute4 VARCHAR2,
129 X_Ship_Line_Attribute5 VARCHAR2,
130 X_Ship_Line_Attribute6 VARCHAR2,
131 X_Ship_Line_Attribute7 VARCHAR2,
132 X_Ship_Line_Attribute8 VARCHAR2,
133 X_Ship_Line_Attribute9 VARCHAR2,
134 X_Ship_Line_Attribute10 VARCHAR2,
135 X_Ship_Line_Attribute11 VARCHAR2,
136 X_Ship_Line_Attribute12 VARCHAR2,
137 X_Ship_Line_Attribute13 VARCHAR2,
138 X_Ship_Line_Attribute14 VARCHAR2,
139 X_Ship_Line_Attribute15 VARCHAR2,
140 X_Ussgl_Transaction_Code VARCHAR2,
141 X_Government_Context VARCHAR2,
142 X_Reason_Id NUMBER,
143 X_Destination_Context VARCHAR2,
144 X_Source_Doc_Quantity NUMBER,
145 X_Source_Doc_Unit_Of_Measure VARCHAR2,
146 X_Lot_Number_CC NUMBER,
147 X_Serial_Number_CC NUMBER,
148 X_QA_Collection_Id NUMBER,
149 X_Country_of_Origin_Code VARCHAR2,
150 X_oe_order_header_id number,
151 X_oe_order_line_id number,
152 X_customer_item_num varchar2,
153 X_customer_id number,
154 X_customer_site_id number,
155 X_put_away_rule_id number,
156 X_put_away_strategy_id number,
157 X_lpn_id number,
158 X_transfer_lpn_id number,
159 X_cost_group_id NUMBER DEFAULT NULL,
160 X_mmtt_temp_id NUMBER DEFAULT NULL,
161 X_mobile_txn VARCHAR2 DEFAULT NULL,
162 X_transfer_cost_group_id NUMBER DEFAULT NULL,
163 /*Bug# 1548597 Preetam B */
164 X_secondary_quantity number DEFAULT NULL,
165 X_secondary_unit_of_measure VARCHAR2 DEFAULT NULL,
166 X_lpn_group_id number DEFAULT NULL,
167 p_org_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE DEFAULT NULL,--<R12 MOAC>
168 X_from_subinventory VARCHAR2 DEFAULT NULL, --Added bug # 6529950
169 X_from_locator_id NUMBER DEFAULT NULL, --Added bug # 6529950
170 X_lcm_shipment_line_id NUMBER DEFAULT NULL, -- lcm changes
171 X_unit_landed_cost NUMBER DEFAULT NULL -- lcm changes
172 ) IS
173 CURSOR C IS SELECT rowid FROM RCV_TRANSACTIONS_INTERFACE
174 WHERE interface_transaction_id = X_Interface_Transaction_Id;
175
176
177
178
179
180 CURSOR C2 IS SELECT rcv_transactions_interface_s.nextval FROM sys.dual;
181
182 BEGIN
183 if (X_Interface_Transaction_Id is NULL) then
184 OPEN C2;
185 FETCH C2 INTO X_Interface_Transaction_Id;
186 CLOSE C2;
187 end if;
188
189 INSERT INTO RCV_TRANSACTIONS_INTERFACE(
190 interface_transaction_id,
191 group_id,
192 last_update_date,
193 last_updated_by,
194 creation_date,
195 created_by,
196 last_update_login,
197 transaction_type,
198 transaction_date,
199 processing_status_code,
200 processing_mode_code,
201 processing_request_id,
202 transaction_status_code,
203 category_id,
204 quantity,
205 unit_of_measure,
206 interface_source_code,
207 interface_source_line_id,
208 inv_transaction_id,
209 item_id,
210 item_description,
211 item_revision,
212 uom_code,
213 employee_id,
214 auto_transact_code,
215 shipment_header_id,
216 shipment_line_id,
217 ship_to_location_id,
218 primary_quantity,
219 primary_unit_of_measure,
220 receipt_source_code,
221 vendor_id,
222 vendor_site_id,
223 from_organization_id,
224 to_organization_id,
225 routing_header_id,
226 routing_step_id,
227 source_document_code,
228 parent_transaction_id,
229 po_header_id,
230 po_revision_num,
231 po_release_id,
232 po_line_id,
233 po_line_location_id,
234 po_unit_price,
235 currency_code,
236 currency_conversion_type,
237 currency_conversion_rate,
238 currency_conversion_date,
239 po_distribution_id,
240 requisition_line_id,
241 req_distribution_id,
242 charge_account_id,
243 substitute_unordered_code,
244 receipt_exception_flag,
245 accrual_status_code,
246 inspection_status_code,
247 inspection_quality_code,
248 destination_type_code,
249 deliver_to_person_id,
250 location_id,
251 deliver_to_location_id,
252 subinventory,
253 locator_id,
254 wip_entity_id,
255 wip_line_id,
256 department_code,
257 wip_repetitive_schedule_id,
258 wip_operation_seq_num,
259 wip_resource_seq_num,
260 bom_resource_id,
261 shipment_num,
262 freight_carrier_code,
263 bill_of_lading,
264 packing_slip,
265 shipped_date,
266 expected_receipt_date,
267 actual_cost,
268 transfer_cost,
269 transportation_cost,
270 transportation_account_id,
271 num_of_containers,
272 waybill_airbill_num,
273 vendor_item_num,
274 vendor_lot_num,
275 rma_reference,
276 comments,
277 attribute_category,
278 attribute1,
279 attribute2,
280 attribute3,
281 attribute4,
282 attribute5,
283 attribute6,
284 attribute7,
285 attribute8,
286 attribute9,
287 attribute10,
288 attribute11,
289 attribute12,
290 attribute13,
291 attribute14,
292 attribute15,
293 ship_head_attribute_category,
294 ship_head_attribute1,
295 ship_head_attribute2,
296 ship_head_attribute3,
297 ship_head_attribute4,
298 ship_head_attribute5,
299 ship_head_attribute6,
300 ship_head_attribute7,
301 ship_head_attribute8,
302 ship_head_attribute9,
303 ship_head_attribute10,
304 ship_head_attribute11,
305 ship_head_attribute12,
306 ship_head_attribute13,
307 ship_head_attribute14,
308 ship_head_attribute15,
312 ship_line_attribute3,
309 ship_line_attribute_category,
310 ship_line_attribute1,
311 ship_line_attribute2,
313 ship_line_attribute4,
314 ship_line_attribute5,
315 ship_line_attribute6,
316 ship_line_attribute7,
317 ship_line_attribute8,
318 ship_line_attribute9,
319 ship_line_attribute10,
320 ship_line_attribute11,
321 ship_line_attribute12,
322 ship_line_attribute13,
323 ship_line_attribute14,
324 ship_line_attribute15,
325 ussgl_transaction_code,
326 government_context,
327 reason_id,
328 destination_context,
329 source_doc_quantity,
330 source_doc_unit_of_measure,
331 use_mtl_lot,
332 use_mtl_serial,
333 qa_collection_id,
334 country_of_origin_code,
335 oe_order_header_id,
336 oe_order_line_id,
337 customer_item_num,
338 customer_id,
339 customer_site_id,
340 put_away_rule_id,
341 put_away_strategy_id,
342 lpn_id,
343 transfer_lpn_id,
344 cost_group_id,
345 mmtt_temp_id,
346 mobile_txn,
347 transfer_cost_group_id,
348 /*Bug# 1548597 Preetam B */
349 secondary_quantity,
350 secondary_unit_of_measure,
351 lpn_group_id,
352 org_id, --<R12 MOAC>
353 from_subinventory, --Added bug # 6529950
354 from_locator_id, --Added bug # 6529950
355 lcm_shipment_line_id,
356 unit_landed_cost
357 ) VALUES (
358 X_Interface_Transaction_Id,
359 X_Group_Id,
360 X_Last_Update_Date,
361 X_Last_Updated_By,
362 X_Creation_Date,
363 X_Created_By,
364 X_Last_Update_Login,
365 X_Transaction_Type,
366 X_Transaction_Date,
367 X_Processing_Status_Code,
368 X_Processing_Mode_Code,
369 X_Processing_Request_Id,
370 X_Transaction_Status_Code,
371 X_Category_Id,
372 X_Quantity,
373 X_Unit_Of_Measure,
374 X_Interface_Source_Code,
375 X_Interface_Source_Line_Id,
376 X_Inv_Transaction_Id,
377 X_Item_Id,
378 X_Item_Description,
379 X_Item_Revision,
380 X_Uom_Code,
381 X_Employee_Id,
382 X_Auto_Transact_Code,
383 X_Shipment_Header_Id,
384 X_Shipment_Line_Id,
385 X_Ship_To_Location_Id,
386 X_Primary_Quantity,
387 X_Primary_Unit_Of_Measure,
388 X_Receipt_Source_Code,
389 X_Vendor_Id,
390 X_Vendor_Site_Id,
391 X_From_Organization_Id,
392 X_To_Organization_Id,
393 X_Routing_Header_Id,
394 X_Routing_Step_Id,
395 X_Source_Document_Code,
396 X_Parent_Transaction_Id,
397 X_Po_Header_Id,
398 X_Po_Revision_Num,
399 X_Po_Release_Id,
400 X_Po_Line_Id,
401 X_Po_Line_Location_Id,
402 X_Po_Unit_Price,
403 X_Currency_Code,
404 X_Currency_Conversion_Type,
405 X_Currency_Conversion_Rate,
406 X_Currency_Conversion_Date,
407 X_Po_Distribution_Id,
408 X_Requisition_Line_Id,
409 X_Req_Distribution_Id,
410 X_Charge_Account_Id,
411 X_Substitute_Unordered_Code,
412 X_Receipt_Exception_Flag,
413 X_Accrual_Status_Code,
414 X_Inspection_Status_Code,
415 X_Inspection_Quality_Code,
416 X_Destination_Type_Code,
417 X_Deliver_To_Person_Id,
418 X_Location_Id,
419 X_Deliver_To_Location_Id,
420 X_Subinventory,
421 X_Locator_Id,
422 X_Wip_Entity_Id,
423 X_Wip_Line_Id,
424 X_Department_Code,
425 X_Wip_Repetitive_Schedule_Id,
426 X_Wip_Operation_Seq_Num,
427 X_Wip_Resource_Seq_Num,
428 X_Bom_Resource_Id,
429 X_Shipment_Num,
430 X_Freight_Carrier_Code,
431 X_Bill_Of_Lading,
432 X_Packing_Slip,
433 X_Shipped_Date,
434 X_Expected_Receipt_Date,
435 X_Actual_Cost,
436 X_Transfer_Cost,
437 X_Transportation_Cost,
438 X_Transportation_Account_Id,
439 X_Num_Of_Containers,
440 X_Waybill_Airbill_Num,
441 X_Vendor_Item_Num,
442 X_Vendor_Lot_Num,
443 X_Rma_Reference,
444 X_Comments,
445 X_Attribute_Category,
446 X_Attribute1,
447 X_Attribute2,
448 X_Attribute3,
452 X_Attribute7,
449 X_Attribute4,
450 X_Attribute5,
451 X_Attribute6,
453 X_Attribute8,
454 X_Attribute9,
455 X_Attribute10,
456 X_Attribute11,
457 X_Attribute12,
458 X_Attribute13,
459 X_Attribute14,
460 X_Attribute15,
461 X_Ship_Head_Attribute_Category,
462 X_Ship_Head_Attribute1,
463 X_Ship_Head_Attribute2,
464 X_Ship_Head_Attribute3,
465 X_Ship_Head_Attribute4,
466 X_Ship_Head_Attribute5,
467 X_Ship_Head_Attribute6,
468 X_Ship_Head_Attribute7,
469 X_Ship_Head_Attribute8,
470 X_Ship_Head_Attribute9,
471 X_Ship_Head_Attribute10,
472 X_Ship_Head_Attribute11,
473 X_Ship_Head_Attribute12,
474 X_Ship_Head_Attribute13,
475 X_Ship_Head_Attribute14,
476 X_Ship_Head_Attribute15,
477 X_Ship_Line_Attribute_Category,
478 X_Ship_Line_Attribute1,
479 X_Ship_Line_Attribute2,
480 X_Ship_Line_Attribute3,
481 X_Ship_Line_Attribute4,
482 X_Ship_Line_Attribute5,
483 X_Ship_Line_Attribute6,
484 X_Ship_Line_Attribute7,
485 X_Ship_Line_Attribute8,
486 X_Ship_Line_Attribute9,
487 X_Ship_Line_Attribute10,
488 X_Ship_Line_Attribute11,
489 X_Ship_Line_Attribute12,
490 X_Ship_Line_Attribute13,
491 X_Ship_Line_Attribute14,
492 X_Ship_Line_Attribute15,
493 X_Ussgl_Transaction_Code,
494 X_Government_Context,
495 X_Reason_Id,
496 X_Destination_Context,
497 X_Source_Doc_Quantity,
498 X_Source_Doc_Unit_Of_Measure,
499 X_Lot_Number_CC,
500 X_Serial_Number_CC,
501 X_QA_Collection_Id,
502 X_Country_of_Origin_Code,
503 X_oe_order_header_id,
504 X_oe_order_line_id,
505 X_customer_item_num,
506 X_customer_id,
507 X_customer_site_id,
508 X_put_away_rule_id,
509 X_put_away_strategy_id,
510 X_lpn_id,
511 X_transfer_lpn_id,
512 X_cost_group_id,
513 X_mmtt_temp_id,
514 X_mobile_txn,
515 X_transfer_cost_group_id,
516 /*Bug# 1548597 Preetam B */
517 X_secondary_quantity,
518 X_secondary_unit_of_measure,
519 X_lpn_group_id,
520 p_org_id, --<R12 MOAC>
521 X_from_subinventory, --Added bug # 6529950
522 X_from_locator_id, --Added bug # 6529950
523 X_lcm_shipment_line_id,
524 X_unit_landed_cost
525 );
526
527 OPEN C;
528 FETCH C INTO X_Rowid;
529 if (C%NOTFOUND) then
530 CLOSE C;
531 Raise NO_DATA_FOUND;
532 end if;
533 CLOSE C;
534 END Insert_Row;
535
536
537 -- Procedure for updating vendor site id in rcv_shipment_headers.
538 PROCEDURE UPDATE_SITE_ID(X_GROUP_ID IN NUMBER,
539 X_SHIPMENT_HEADER_ID IN NUMBER,
540 X_ADD_RECEIPT IN VARCHAR2) IS
541
542 x_vendor_site_id NUMBER;
543 x_site_id_count NUMBER;
544 x_rcv_lines_count NUMBER;
545 hdr_vendor_site_id NUMBER;
546 x_count NUMBER;
547
548 BEGIN
549
550 /*
551 ** Determining the value of vendor_site_id to be populated in rcv_shipment_headers
552 */
553
554 /* The following select statement sequences thru the RTI
555 ** for our group_id and determines whether the records have
556 ** the same vendor_site_id or not. If the vendor_site_id
557 ** is the same, we populate this value in rcv_shipment_headers
558 ** else we null it out.
559 */
560
561 select count(count(vendor_site_id))
562 into x_site_id_count
563 from rcv_transactions_interface RTI
564 where RTI.group_id = x_group_id
565 group by vendor_site_id;
566
567
568 select vendor_site_id
569 into hdr_vendor_site_id
570 from rcv_shipment_headers
571 where shipment_header_id = x_shipment_header_id;
572
573
574 if (x_site_id_count = 1) then
575
576 select distinct vendor_site_id
577 into x_vendor_site_id
578 from rcv_transactions_interface RTI
579 where RTI.group_id = x_group_id;
580
581 IF (X_ADD_RECEIPT = 'NEW') THEN
582 hdr_vendor_site_id := x_vendor_site_id;
583 elsif (hdr_vendor_site_id <> x_vendor_site_id) then
584 hdr_vendor_site_id := '';
585 end if;
586
587 else
588
589 hdr_vendor_site_id := '';
590
591 end if;
592
593 update rcv_shipment_headers
594 set vendor_site_id = hdr_vendor_site_id
595 where shipment_header_id = x_shipment_header_id;
596
597 EXCEPTION
598
599 WHEN OTHERS THEN NULL;
600
601 END UPDATE_SITE_ID;
602
603 END RCV_TRX_INTERFACE_INSERT_PKG;