[Home] [Help]
PACKAGE BODY: APPS.RCV_TRX_INTERFACE_TRX_UPD_PKG
Source
1 PACKAGE BODY RCV_TRX_INTERFACE_TRX_UPD_PKG as
2 /* $Header: RCVTIR6B.pls 120.0.12020000.4 2013/01/31 03:45:12 wayin ship $ */
3
4 PROCEDURE update_rcv_transaction (
5 rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE) IS
6
7 X_rowid ROWID;
8 X_progress VARCHAR2(4) := '000';
9
10 BEGIN
11
12 /*
13 ** get the rowid for this transaction
14 */
15 X_progress := '010';
16
17 SELECT rowid
18 INTO X_Rowid
19 FROM rcv_transactions_interface
20 WHERE interface_transaction_id = rcv_trx.interface_transaction_id;
21
22 /*
23 ** Update the columns of the transaction
24 */
25 RCV_TRX_INTERFACE_UPDATE_PKG.update_row(
26 X_Rowid,
27 rcv_trx.Interface_Transaction_Id,
28 rcv_trx.Group_Id,
29 rcv_trx.Last_Update_Date,
30 rcv_trx.Last_Updated_By,
31 rcv_trx.Last_Update_Login,
32 rcv_trx.Transaction_Type,
33 rcv_trx.Transaction_Date,
34 rcv_trx.Processing_Status_Code,
35 rcv_trx.Processing_Mode_Code,
36 rcv_trx.Processing_Request_Id,
37 rcv_trx.Transaction_Status_Code,
38 rcv_trx.Category_Id,
39 rcv_trx.Quantity,
40 rcv_trx.Unit_Of_Measure,
41 rcv_trx.Interface_Source_Code,
42 rcv_trx.Interface_Source_Line_Id,
43 rcv_trx.Inv_Transaction_Id,
44 rcv_trx.Item_Id,
45 rcv_trx.Item_Description,
46 rcv_trx.Item_Revision,
47 rcv_trx.Uom_Code,
48 rcv_trx.Employee_Id,
49 rcv_trx.Auto_Transact_Code,
50 rcv_trx.Shipment_Header_Id,
51 rcv_trx.Shipment_Line_Id,
52 rcv_trx.Ship_To_Location_Id,
53 rcv_trx.Primary_Quantity,
54 rcv_trx.Primary_Unit_Of_Measure,
55 rcv_trx.Receipt_Source_Code,
56 rcv_trx.Vendor_Id,
57 rcv_trx.Vendor_Site_Id,
58 rcv_trx.From_Organization_Id,
59 rcv_trx.To_Organization_Id,
60 rcv_trx.Routing_Header_Id,
61 rcv_trx.Routing_Step_Id,
62 rcv_trx.Source_Document_Code,
63 rcv_trx.Parent_Transaction_Id,
64 rcv_trx.Po_Header_Id,
65 rcv_trx.Po_Revision_Num,
66 rcv_trx.Po_Release_Id,
67 rcv_trx.Po_Line_Id,
68 rcv_trx.Po_Line_Location_Id,
69 rcv_trx.Po_Unit_Price,
70 rcv_trx.Currency_Code,
71 rcv_trx.Currency_Conversion_Type,
72 rcv_trx.Currency_Conversion_Rate,
73 rcv_trx.Currency_Conversion_Date,
74 rcv_trx.Po_Distribution_Id,
75 rcv_trx.Requisition_Line_Id,
76 rcv_trx.Req_Distribution_Id,
77 rcv_trx.Charge_Account_Id,
78 rcv_trx.Substitute_Unordered_Code,
79 rcv_trx.Receipt_Exception_Flag,
80 rcv_trx.Accrual_Status_Code,
81 rcv_trx.Inspection_Status_Code,
82 rcv_trx.Inspection_Quality_Code,
83 rcv_trx.Destination_Type_Code,
84 rcv_trx.Deliver_To_Person_Id,
85 rcv_trx.Location_Id,
86 rcv_trx.Deliver_To_Location_Id,
87 rcv_trx.Subinventory,
88 rcv_trx.Locator_Id,
89 rcv_trx.Wip_Entity_Id,
90 rcv_trx.Wip_Line_Id,
91 rcv_trx.Department_Code,
92 rcv_trx.Wip_Repetitive_Schedule_Id,
93 rcv_trx.Wip_Operation_Seq_Num,
94 rcv_trx.Wip_Resource_Seq_Num,
95 rcv_trx.Bom_Resource_Id,
96 rcv_trx.Shipment_Num,
97 rcv_trx.Freight_Carrier_Code,
98 rcv_trx.Bill_Of_Lading,
99 rcv_trx.Packing_Slip,
100 rcv_trx.Shipped_Date,
101 rcv_trx.Expected_Receipt_Date,
102 rcv_trx.Actual_Cost,
103 rcv_trx.Transfer_Cost,
104 rcv_trx.Transportation_Cost,
105 rcv_trx.Transportation_Account_Id,
106 rcv_trx.Num_Of_Containers,
107 rcv_trx.Waybill_Airbill_Num,
108 rcv_trx.Vendor_Item_Num,
109 rcv_trx.Vendor_Lot_Num,
110 rcv_trx.Rma_Reference,
111 rcv_trx.Comments,
112 rcv_trx.Attribute_Category,
113 rcv_trx.Attribute1,
114 rcv_trx.Attribute2,
115 rcv_trx.Attribute3,
116 rcv_trx.Attribute4,
117 rcv_trx.Attribute5,
118 rcv_trx.Attribute6,
119 rcv_trx.Attribute7,
120 rcv_trx.Attribute8,
121 rcv_trx.Attribute9,
122 rcv_trx.Attribute10,
123 rcv_trx.Attribute11,
124 rcv_trx.Attribute12,
125 rcv_trx.Attribute13,
126 rcv_trx.Attribute14,
127 rcv_trx.Attribute15,
128 rcv_trx.Ship_Head_Attribute_Category,
129 rcv_trx.Ship_Head_Attribute1,
130 rcv_trx.Ship_Head_Attribute2,
131 rcv_trx.Ship_Head_Attribute3,
132 rcv_trx.Ship_Head_Attribute4,
133 rcv_trx.Ship_Head_Attribute5,
134 rcv_trx.Ship_Head_Attribute6,
135 rcv_trx.Ship_Head_Attribute7,
136 rcv_trx.Ship_Head_Attribute8,
137 rcv_trx.Ship_Head_Attribute9,
138 rcv_trx.Ship_Head_Attribute10,
139 rcv_trx.Ship_Head_Attribute11,
140 rcv_trx.Ship_Head_Attribute12,
141 rcv_trx.Ship_Head_Attribute13,
142 rcv_trx.Ship_Head_Attribute14,
143 rcv_trx.Ship_Head_Attribute15,
144 rcv_trx.Ship_Line_Attribute_Category,
145 rcv_trx.Ship_Line_Attribute1,
146 rcv_trx.Ship_Line_Attribute2,
147 rcv_trx.Ship_Line_Attribute3,
148 rcv_trx.Ship_Line_Attribute4,
149 rcv_trx.Ship_Line_Attribute5,
150 rcv_trx.Ship_Line_Attribute6,
151 rcv_trx.Ship_Line_Attribute7,
152 rcv_trx.Ship_Line_Attribute8,
153 rcv_trx.Ship_Line_Attribute9,
154 rcv_trx.Ship_Line_Attribute10,
155 rcv_trx.Ship_Line_Attribute11,
156 rcv_trx.Ship_Line_Attribute12,
157 rcv_trx.Ship_Line_Attribute13,
158 rcv_trx.Ship_Line_Attribute14,
159 rcv_trx.Ship_Line_Attribute15,
160 rcv_trx.Ussgl_Transaction_Code,
161 rcv_trx.Government_Context,
162 rcv_trx.Reason_Id,
163 rcv_trx.Destination_Context,
164 rcv_trx.Source_Doc_Quantity,
165 rcv_trx.Source_Doc_Unit_Of_Measure,
166 rcv_trx.Use_Mtl_Lot,
167 rcv_trx.Use_Mtl_Serial);
168
169 END update_rcv_transaction;
170 --ROI project start
171 PROCEDURE roi_update_rti_rhi(p_rti_id IN NUMBER,
172 p_success_flag IN OUT NOCOPY NUMBER) IS
173 BEGIN
174 -- update the current RTI and its parent RTIs
175 UPDATE rcv_transactions_interface
176 SET request_id = NULL,
177 processing_request_id = NULL,
178 order_transaction_id = NULL,
179 primary_quantity = NULL,
180 primary_unit_of_measure = NULL,
181 interface_transaction_qty = NULL,
182 processing_status_code = 'PENDING',
183 transaction_status_code = 'PENDING',
184 processing_mode_code = 'BATCH'
185 WHERE interface_transaction_id IN
186 (SELECT interface_transaction_id
187 FROM rcv_transactions_interface rti
188 START WITH rti.interface_transaction_id = p_rti_id
189 CONNECT BY PRIOR rti.parent_interface_txn_id =
190 rti.interface_transaction_id);
191
192 UPDATE rcv_headers_interface
193 SET processing_request_id = NULL,
194 receipt_header_id = NULL,
195 processing_status_code = 'PENDING'
196 WHERE header_interface_id IN
197 (SELECT header_interface_id
198 FROM rcv_transactions_interface
199 START WITH interface_transaction_id = p_rti_id
200 CONNECT BY PRIOR
201 parent_interface_txn_id = interface_transaction_id);
202 p_success_flag := 0;
203 EXCEPTION
204 WHEN OTHERS THEN
205 p_success_flag := 1;
206 END roi_update_rti_rhi;
207
208 PROCEDURE roi_update_rti_rhi_grp(p_rti_index IN NUMBER,
209 p_count IN NUMBER,
210 p_rti_id_tbl IN rti_table,
211 p_group_id_tbl IN group_table,
212 p_process_flag_tbl IN OUT NOCOPY process_flag,
213 p_success_flag IN OUT NOCOPY NUMBER) IS
214 PRAGMA AUTONOMOUS_TRANSACTION;
215 l_rti_id NUMBER;
216 l_group_id NUMBER;
217 l_inner_rti_id NUMBER;
218 l_inner_group_id NUMBER;
219 l_inner_index NUMBER := 1;
220 BEGIN
221
222 l_rti_id := p_rti_id_tbl(p_rti_index);
223 l_group_id := p_group_id_tbl(p_rti_index);
224
225 --update the current record(rhi,rti) and its parent
226 roi_update_rti_rhi(l_rti_id, p_success_flag);
227 p_process_flag_tbl(p_rti_index) := 1;
228 IF p_success_flag = 0 THEN
229 -- find records in the same group
230 l_inner_index := p_rti_index + 1;
231 WHILE (l_inner_index <= p_count) LOOP
232 l_inner_rti_id := p_rti_id_tbl(l_inner_index);
233 l_inner_group_id := p_group_id_tbl(l_inner_index);
234 --update rhi in same group
235 IF p_success_flag = 0 AND p_process_flag_tbl(l_inner_index) = 0 AND
236 l_group_id = l_inner_group_id THEN
237 roi_update_rti_rhi(l_inner_rti_id, p_success_flag);
238 p_process_flag_tbl(l_inner_index) := 1;
239 END IF;
240 l_inner_index := l_inner_index + 1;
241 END LOOP;
242 END IF;
243 IF p_success_flag = 0 THEN
244 COMMIT;
245 ELSE
246 ROLLBACK;
247 END IF;
248 EXCEPTION
249 WHEN OTHERS THEN
250 p_success_flag := 1;
251 ROLLBACK;
252 END roi_update_rti_rhi_grp;
253
254 /*===========================================================================
255
256 PROCEDURE NAME: RESUBMIT ()
257
258 ===========================================================================*/
259 PROCEDURE resubmit(p_rti_id_tbl IN rti_table,
260 p_group_id_tbl IN group_table,
261 p_process_flag_tbl IN OUT NOCOPY process_flag,
262 p_count IN NUMBER) IS
263 l_sucess_flag NUMBER := 0;
264 l_index NUMBER := 1;
265 v_req_id NUMBER;
266
267 BEGIN
268
269 IF (g_asn_debug = 'Y') THEN
270 asn_debug.put_line('Enter RCV_ROI_INTERFACE_PKG.RESUBMIT');
271 END IF;
272
273 WHILE (l_index <= p_count) LOOP
274 ---update data record in same group and its parent if not processed
275 IF (p_process_flag_tbl(l_index) = 0) THEN
276 roi_update_rti_rhi_grp(l_index,
277 p_count,
278 p_rti_id_tbl,
279 p_group_id_tbl,
280 p_process_flag_tbl,
281 l_sucess_flag);
282 --if update sucessfully then sibmit RTP with the group
283 IF (l_sucess_flag = 0) THEN
284 v_req_id := fnd_request.submit_request('PO',
285 'RVCTP',
286 null,
287 null,
288 false,
289 'BATCH',
290 p_group_id_tbl(l_index),
291 NULL,
292 NULL,
293 NULL,
294 NULL,
295 NULL,
296 NULL, NULL,
297 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
298 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
299 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
300 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
301 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
302 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
303 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
304 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
305 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
306 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
307 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
308 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
309 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
310 ELSE
311 IF (g_asn_debug = 'Y') THEN
312 asn_debug.put_line('Unexpected exception in update rhi/rti : ' ||
313 SQLERRM);
314 END IF;
315 END IF;
316 END IF;
317 l_index := l_index + 1;
318 l_sucess_flag := 0;
319 END LOOP;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 IF (g_asn_debug = 'Y') THEN
324 asn_debug.put_line('Unexpected exception in RESUBMIT : ' ||
325 SQLERRM);
326 END IF;
327 END resubmit;
328 --ROI project end
329 END RCV_TRX_INTERFACE_TRX_UPD_PKG;