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