[Home] [Help]
PACKAGE BODY: APPS.RCV_TRANSACTION_PROCESSOR
Source
1 PACKAGE BODY RCV_TRANSACTION_PROCESSOR AS
2 /* $Header: RCVGTPB.pls 120.10.12010000.3 2008/10/24 09:39:20 sdpaul ship $ */
3
4 TYPE FlagTabByVC IS TABLE OF VARCHAR2(1) INDEX BY VARCHAR2(256);
5
6 g_accounting_info DBMS_SQL.number_table;
7 g_installed_products FlagTabByVC;
8 g_conc_request_id NUMBER := -1;
9 g_conc_login_id NUMBER := -1;
10 g_conc_program_id NUMBER := -1;
11 g_prog_appl_id NUMBER := -1;
12 g_user_id NUMBER := -1;
13
14 FUNCTION conc_request_id
15 RETURN NUMBER IS
16 BEGIN
17 IF g_conc_request_id = -1 THEN
18 g_conc_request_id := FND_GLOBAL.conc_request_id;
19 END IF;
20
21 RETURN g_conc_request_id;
22 END conc_request_id;
23
24 FUNCTION conc_login_id
25 RETURN NUMBER IS
26 BEGIN
27 IF g_conc_login_id = -1 THEN
28 g_conc_login_id := FND_GLOBAL.conc_login_id;
29 END IF;
30
31 RETURN g_conc_login_id;
32 END conc_login_id;
33
34 FUNCTION conc_program_id
35 RETURN NUMBER IS
36 BEGIN
37 IF g_conc_program_id = -1 THEN
38 g_conc_program_id := FND_GLOBAL.conc_program_id;
39 END IF;
40
41 RETURN g_conc_program_id;
42 END conc_program_id;
43
44 FUNCTION prog_appl_id
45 RETURN NUMBER IS
46 BEGIN
47 IF g_prog_appl_id = -1 THEN
48 g_prog_appl_id := FND_GLOBAL.prog_appl_id;
49 END IF;
50
51 RETURN g_prog_appl_id;
52 END prog_appl_id;
53
54 FUNCTION user_id
55 RETURN NUMBER IS
56 BEGIN
57 IF g_user_id = -1 THEN
58 g_user_id := FND_GLOBAL.user_id;
59 END IF;
60
61 RETURN g_user_id;
62 END user_id;
63
64 FUNCTION get_product_install_status( p_product_name IN VARCHAR2 )
65 RETURN VARCHAR2 IS
66 BEGIN
67 IF NOT g_installed_products.EXISTS(p_product_name) THEN
68 g_installed_products(p_product_name) := PO_CORE_S.get_product_install_status( p_product_name );
69 END IF;
70
71 RETURN g_installed_products(p_product_name);
72 END get_product_install_status;
73
74 FUNCTION Valid_Accounting_Info( p_org_id NUMBER )
75 RETURN BOOLEAN IS
76 BEGIN
77 IF NOT g_accounting_info.EXISTS(p_org_id) THEN
78 SELECT COUNT(*)
79 INTO g_accounting_info(p_org_id)
80 FROM hr_organization_information hoi
81 , gl_sets_of_books gsob
82 , financials_system_params_all fsp
83 WHERE hoi.organization_id = p_org_id
84 AND hoi.org_information_context||'' = 'Accounting Information'
85 AND (fsp.org_id IS NULL OR hoi.org_information3 = TO_CHAR(fsp.org_id))
86 AND fsp.set_of_books_id = gsob.set_of_books_id;
87 END IF;
88
89 RETURN g_accounting_info(p_org_id) > 0;
90 END Valid_Accounting_Info;
91
92 /*Bug 5517298: Added the function get_acct_period_status*/
93 FUNCTION get_acct_period_status(p_trx_date IN DATE,
94 p_org_id IN NUMBER) RETURN VARCHAR2 IS
95
96 l_closing_status VARCHAR2(1) := NULL;
97 l_open_flag VARCHAR2(1) := NULL;
98 l_progress VARCHAR2(3) := NULL;
99
100
101 BEGIN
102
103 l_progress := '010';
104
105 SELECT oap.open_flag
106 INTO l_open_flag
107 FROM org_acct_periods oap
108 WHERE oap.organization_id = p_org_id
109 AND (trunc(p_trx_date)
110 BETWEEN trunc(oap.period_start_date) AND
111 trunc (oap.schedule_close_date));
112
113 if (l_open_flag = 'Y') then
114 l_closing_status := 'O';
115 elsif (l_open_flag = 'N') then
116 l_closing_status := 'N';
117 else
118 l_closing_status := 'F';
119 end if;
120
121 RETURN l_closing_status;
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND then
125 po_message_s.app_error('PO_INV_NO_OPEN_PERIOD');
126 RAISE;
127 WHEN TOO_MANY_ROWS then
128 po_message_s.app_error('PO_INV_MUL_PERIODS');
129 RAISE;
130 WHEN OTHERS THEN
131 po_message_s.sql_error('get_acct_period_status', l_progress, sqlcode);
132 RAISE;
133 END get_acct_period_status;
134
135 PROCEDURE RVTTHIns
136 ( p_rti_id IN RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
137 , p_transaction_type IN RCV_TRANSACTIONS.transaction_type%TYPE
138 , p_shipment_header_id IN RCV_SHIPMENT_HEADERS.shipment_header_id%TYPE
139 , p_shipment_line_id IN RCV_SHIPMENT_LINES.shipment_line_id%TYPE
140 , p_primary_unit_of_measure IN RCV_TRANSACTIONS.primary_unit_of_measure%TYPE
141 , p_primary_quantity IN RCV_TRANSACTIONS.primary_quantity%TYPE
142 , p_source_doc_unit_of_measure IN RCV_TRANSACTIONS.source_doc_unit_of_measure%TYPE
143 , p_source_doc_quantity IN RCV_TRANSACTIONS.source_doc_quantity%TYPE
144 , p_parent_id IN RCV_TRANSACTIONS.transaction_id%TYPE
145 , p_receive_id IN OUT NOCOPY RCV_TRANSACTIONS.transaction_id%TYPE
146 , p_deliver_id IN OUT NOCOPY RCV_TRANSACTIONS.transaction_id%TYPE
147 , p_correct_id IN OUT NOCOPY RCV_TRANSACTIONS.transaction_id%TYPE
148 , p_return_id IN OUT NOCOPY RCV_TRANSACTIONS.transaction_id%TYPE
149 , x_rt_id OUT NOCOPY RCV_TRANSACTIONS.transaction_id%TYPE
150 , x_error_message OUT NOCOPY VARCHAR2
151 ) IS
152 l_rt_row RCV_TRANSACTIONS%ROWTYPE;
153 l_parent_row RCV_TRANSACTIONS%ROWTYPE;
154 l_rti_row RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
155
156 l_use_ship_to_flag VARCHAR2(1);
157 l_common_receiving BOOLEAN;
158
159 /*Bug 5517289-Start*/
160 CURSOR c_fob_point (l_from_org_id NUMBER,l_to_org_id NUMBER)is
161
162 SELECT fob_point
163 FROM mtl_interorg_parameters
164 WHERE from_organization_id = l_from_org_id
165 AND to_organization_id = l_to_org_id;
166
167 l_fob_point number;
168 /*Bug 5517289-End*/
169
170 BEGIN
171 asn_debug.put_line('RVTTHIns(p_rti_id:' || p_rti_id
172 || ', p_transaction_type: ' || p_transaction_type
173 || ', p_shipment_header_id: ' || p_shipment_header_id
174 || ', p_shipment_line_id: ' || p_shipment_line_id
175 || ', p_primary_unit_of_measure: ' || p_primary_unit_of_measure
176 || ', p_primary_quantity: ' || p_primary_quantity
177 || ', p_source_doc_unit_of_measure: ' || p_source_doc_unit_of_measure
178 || ', p_source_doc_quantity: ' || p_source_doc_quantity
179 || ', p_parent_id: ' || p_parent_id
180 || ', p_receive_id: ' || p_receive_id
181 || ', p_deliver_id: ' || p_deliver_id
182 || ', p_correct_id: ' || p_correct_id
183 || ', p_return_id: ' || p_return_id
184 || ')');
185
186 SELECT rcv_transactions_s.NEXTVAL
187 INTO l_rt_row.transaction_id
188 FROM DUAL;
189
190 -- Bug 4401341
191 /*Commenting the rcv_table_functions.get_rti_frow_from_id call and fetching
192 the value from rti table as lpn_id fetched from the cache table does not
193 have the value that is updated by Inventory.There may be other fields that
194 would have got updated.So fetching all the values directly from rti and then
195 use them to populate rcv_transactions table.
196 */
197 -- l_rti_row := RCV_TABLE_FUNCTIONS.get_rti_row_from_id( p_rti_id );
198
199 select *
200 into l_rti_row
201 from rcv_transactions_interface
202 where interface_transaction_id = p_rti_id;
203
204 -- default values
205 l_rt_row.user_entered_flag := 'Y';
206 l_use_ship_to_flag := 'N';
207
208 IF p_transaction_type = 'RECEIVE' THEN
209 l_rt_row.parent_transaction_id := -1;
210 p_receive_id := l_rt_row.transaction_id;
211 IF p_transaction_type = 'SHIP' THEN
212 l_rt_row.user_entered_flag := 'N';
213 END IF;
214 IF l_rti_row.auto_transact_code = 'DELIVER' THEN
215 l_use_ship_to_flag := 'Y';
216 END IF;
217 ELSIF p_transaction_type = 'DELIVER' THEN
218 p_deliver_id := l_rt_row.transaction_id;
219 IF l_rti_row.auto_transact_code = 'DELIVER' THEN
220 l_rt_row.user_entered_flag := 'N';
221 l_rt_row.parent_transaction_id := p_receive_id;
222 END IF;
223 ELSIF p_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
224 l_parent_row := RCV_TABLE_FUNCTIONS.get_rt_row_from_id( l_rti_row.parent_transaction_id );
225 IF l_parent_row.transaction_type = 'DELIVER' THEN
226 l_rt_row.parent_transaction_id := l_parent_row.parent_transaction_id;
227 l_use_ship_to_flag := 'Y';
228 END IF;
229 p_return_id := l_rt_row.transaction_id;
230 ELSIF p_transaction_type = 'RETURN TO RECEIVING' THEN
231 IF l_rti_row.transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
232 l_rt_row.user_entered_flag := 'N';
233 END IF;
234 p_correct_id := l_rt_row.transaction_id;
235 ELSIF p_transaction_type = 'CORRECT' THEN
236 p_correct_id := l_rt_row.transaction_id;
237 END IF;
238
239 IF l_rt_row.parent_transaction_id IS NULL THEN
240 l_rt_row.parent_transaction_id := p_parent_id;
241 END IF;
242
243 IF NOT Valid_Accounting_Info( l_rti_row.to_organization_id ) THEN
244 x_error_message := 'RCV_INV_ACCT_INVALID';
245 RETURN;
246 END IF;
247
248 /*
249 Bug 6359747
250 Changing the Created_by and Last_Updated_by from user_id to whats in RTI record.
251 Commented old lines below and add new ones.
252 */
253 l_rt_row.creation_date := SYSDATE;
254 --l_rt_row.created_by := user_id;
255 l_rt_row.created_by := l_rti_row.created_by;
256
257 l_rt_row.last_update_date := SYSDATE;
258 --l_rt_row.last_updated_by := user_id;
259 l_rt_row.last_updated_by := l_rti_row.last_updated_by;
260
261 /* Bug# 7343638
262 * If the processing mode is ONLINE, all the below fields were
263 * being set as -1.
264 * Explicitely setting the below fields to 0 in case of ONLINE
265 * as this was the value set earlier.
266 */
267
268 l_rt_row.last_update_login := l_rti_row.last_update_login;
269
270 IF (l_rti_row.processing_mode_code = 'ONLINE') THEN
271 l_rt_row.request_id := 0;
272 l_rt_row.program_application_id := 0;
273 l_rt_row.program_id := 0;
274 ELSE
275 l_rt_row.request_id := conc_request_id;
276 l_rt_row.program_application_id := prog_appl_id;
277 l_rt_row.program_id := conc_program_id;
278 END IF;
279 -- End bug# 7343638
280
281 l_rt_row.program_update_date := SYSDATE;
282 l_rt_row.interface_source_code := l_rti_row.interface_source_code;
283 l_rt_row.interface_source_line_id := l_rti_row.interface_source_line_id;
284 l_rt_row.transaction_type := p_transaction_type;
285 l_rt_row.transaction_date := l_rti_row.transaction_date;
286 l_rt_row.source_document_code := l_rti_row.source_document_code;
287 IF l_use_ship_to_flag = 'Y' THEN
288 l_rt_row.destination_type_code := 'RECEIVING';
289 ELSE
290 l_rt_row.destination_type_code := l_rti_row.destination_type_code;
291 END IF;
292 l_rt_row.location_id := l_rti_row.location_id;
293 l_rt_row.quantity := l_rti_row.quantity;
294 l_rt_row.unit_of_measure := l_rti_row.unit_of_measure;
295 l_rt_row.uom_code := l_rti_row.uom_code;
296 l_rt_row.primary_quantity := p_primary_quantity;
297 l_rt_row.primary_unit_of_measure := p_primary_unit_of_measure;
298 l_rt_row.source_doc_quantity := p_source_doc_quantity;
299 l_rt_row.source_doc_unit_of_measure := p_source_doc_unit_of_measure;
300 l_rt_row.shipment_header_id := p_shipment_header_id;
301 l_rt_row.shipment_line_id := p_shipment_line_id;
302 l_rt_row.employee_id := l_rti_row.employee_id;
303 l_rt_row.po_header_id := l_rti_row.po_header_id;
304 l_rt_row.po_release_id := l_rti_row.po_release_id;
305 l_rt_row.po_line_id := l_rti_row.po_line_id;
306 l_rt_row.po_line_location_id := l_rti_row.po_line_location_id;
307 l_rt_row.po_distribution_id := l_rti_row.po_distribution_id;
308 l_rt_row.po_revision_num := l_rti_row.po_revision_num;
309 l_rt_row.requisition_line_id := l_rti_row.requisition_line_id;
310 l_rt_row.req_distribution_id := l_rti_row.req_distribution_id;
311
312 -- Bug 6265149 : Start
313 -- It is mandatory to set the following:
314 -- Purchasing > Setups > Purchasing > Document types (PO/Release) > Archive on => 'Approve'
315 begin
316 if (l_rti_row.source_document_code = 'PO' and
317 l_rt_row.transaction_type in ('DELIVER', 'ACCEPT', 'REJECT')) then
318 --
319 select price_override
320 into l_rt_row.po_unit_price
321 from po_line_locations_archive
322 where line_location_id = l_rti_row.po_line_location_id
323 and nvl(latest_external_flag,'N') = 'Y';
324 --
325 asn_debug.put_line('PO Unit Price from pll_archive :' || l_rt_row.po_unit_price);
326 --
327 end if;
328 exception
329 when others then
330 asn_debug.put_line('Fetching from pll_archive failed : ' || SQLERRM);
331 l_rt_row.po_unit_price := NULL;
332 end;
333 --
334 if (l_rt_row.po_unit_price is null) then
335 l_rt_row.po_unit_price := l_rti_row.po_unit_price;
336 end if;
337 asn_debug.put_line('PO Unit Price is :' || l_rt_row.po_unit_price);
338 --
339 -- Bug 6265149 : End
340
341 IF l_rti_row.currency_code IS NULL THEN
342
343 /* Bug 5246147: Removed the function calls rcv_table_functions.get_fspa_row_from_org() and
344 rcv_table_functions.get_sob_row_from_id() to get the currency_code value and
345 added the following sql to get the currency_code */
346 select gsob.currency_code into
347 l_rt_row.currency_code
348 from hr_organization_information hoi,
349 financials_system_params_all fsp,
350 gl_sets_of_books gsob
351 where hoi.organization_id = l_rti_row.to_organization_id
352 and hoi.org_information_context||'' = 'Accounting Information'
353 and (fsp.org_id is null OR hoi.org_information3 = to_char(fsp.org_id))
354 and hoi.org_information1 = to_char(fsp.set_of_books_id)
355 and fsp.set_of_books_id = gsob.set_of_books_id;
356 ELSE
357 l_rt_row.currency_code := l_rti_row.currency_code;
358 END IF;
359 IF l_rti_row.currency_conversion_rate IS NULL AND l_rti_row.source_document_code <> 'RMA' THEN
360 l_rt_row.currency_conversion_rate := 1;
361 ELSE
362 l_rt_row.currency_conversion_rate := l_rti_row.currency_conversion_rate;
363 END IF;
364 l_rt_row.currency_conversion_date := l_rti_row.currency_conversion_date;
365 l_rt_row.currency_conversion_type := l_rti_row.currency_conversion_type;
366 l_rt_row.routing_header_id := l_rti_row.routing_header_id;
367 l_rt_row.routing_step_id := l_rti_row.routing_step_id;
368 l_rt_row.substitute_unordered_code := l_rti_row.substitute_unordered_code;
369 l_rt_row.receipt_exception_flag := l_rti_row.receipt_exception_flag;
370 CASE p_transaction_type
371 WHEN 'RECEIVE' THEN l_rt_row.inspection_status_code := 'NOT INSPECTED';
372 WHEN 'ACCEPT' THEN l_rt_row.inspection_status_code := 'ACCEPTED';
373 WHEN 'REJECT' THEN l_rt_row.inspection_status_code := 'REJECTED';
374 ELSE l_rt_row.inspection_status_code := l_rti_row.inspection_status_code;
375 END CASE;
376 IF p_transaction_type = 'RECEIVE' THEN
377 l_rt_row.inspection_quality_code := '';
378 ELSE
379 l_rt_row.inspection_quality_code := l_rti_row.inspection_quality_code;
380 END IF;
381 l_rt_row.vendor_id := l_rti_row.vendor_id;
382 l_rt_row.vendor_site_id := l_rti_row.vendor_site_id;
383 l_rt_row.vendor_lot_num := l_rti_row.vendor_lot_num;
384 l_rt_row.organization_id := l_rti_row.to_organization_id;
385 l_rt_row.from_subinventory := l_rti_row.from_subinventory;
386 l_rt_row.from_locator_id := l_rti_row.from_locator_id;
387 l_rt_row.subinventory := l_rti_row.subinventory;
388 l_rt_row.locator_id := l_rti_row.locator_id;
389 IF ( p_transaction_type = 'RECEIVE' AND
390 l_rti_row.auto_transact_code = 'DELIVER' ) OR
391 p_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
392 THEN
393 l_rt_row.subinventory := NULL;
394 l_rt_row.locator_id := NULL;
395 END IF;
396 IF p_transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND
397 l_parent_row.transaction_type = 'DELIVER'
398 THEN
399 l_rt_row.from_subinventory := l_rti_row.subinventory;
400 l_rt_row.from_locator_id := l_rti_row.locator_id;
401 END IF;
402 l_rt_row.rma_reference := l_rti_row.rma_reference;
403 l_rt_row.deliver_to_person_id := l_rti_row.deliver_to_person_id;
404 l_rt_row.deliver_to_location_id := l_rti_row.deliver_to_location_id;
405 l_rt_row.department_code := l_rti_row.department_code;
406 l_rt_row.wip_entity_id := l_rti_row.wip_entity_id;
407 l_rt_row.wip_line_id := l_rti_row.wip_line_id;
408 l_rt_row.wip_repetitive_schedule_id := l_rti_row.wip_repetitive_schedule_id;
409 l_rt_row.wip_operation_seq_num := l_rti_row.wip_operation_seq_num;
410 l_rt_row.wip_resource_seq_num := l_rti_row.wip_resource_seq_num;
411 l_rt_row.bom_resource_id := l_rti_row.bom_resource_id;
412 IF p_transaction_type IN ('RECEIVE','DELIVER') THEN
413 l_rt_row.inv_transaction_id := l_rti_row.inv_transaction_id;
414 ELSE
415 l_rt_row.inv_transaction_id := '';
416 END IF;
417 l_rt_row.reason_id := l_rti_row.reason_id;
418 IF l_use_ship_to_flag = 'Y' THEN
419 l_rt_row.destination_context := 'RECEIVING';
420 ELSE
421 l_rt_row.destination_context := l_rti_row.destination_context;
422 END IF;
423 l_rt_row.comments := l_rti_row.comments;
424 l_rt_row.interface_transaction_id := l_rti_row.interface_transaction_id;
425 l_rt_row.group_id := l_rti_row.group_id;
426 l_rt_row.attribute_category := l_rti_row.attribute_category;
427 l_rt_row.attribute1 := l_rti_row.attribute1;
428 l_rt_row.attribute2 := l_rti_row.attribute2;
429 l_rt_row.attribute3 := l_rti_row.attribute3;
430 l_rt_row.attribute4 := l_rti_row.attribute4;
431 l_rt_row.attribute5 := l_rti_row.attribute5;
432 l_rt_row.attribute6 := l_rti_row.attribute6;
433 l_rt_row.attribute7 := l_rti_row.attribute7;
434 l_rt_row.attribute8 := l_rti_row.attribute8;
435 l_rt_row.attribute9 := l_rti_row.attribute9;
436 l_rt_row.attribute10 := l_rti_row.attribute10;
437 l_rt_row.attribute11 := l_rti_row.attribute11;
438 l_rt_row.attribute12 := l_rti_row.attribute12;
439 l_rt_row.attribute13 := l_rti_row.attribute13;
440 l_rt_row.attribute14 := l_rti_row.attribute14;
441 l_rt_row.attribute15 := l_rti_row.attribute15;
442 l_rt_row.movement_id := l_rti_row.movement_id;
443 IF l_rti_row.vendor_site_id IS NOT NULL AND RCV_TABLE_FUNCTIONS.get_pvs_row_from_id(l_rti_row.vendor_site_id).pay_on_code IN ('RECEIPT','RECEIPT_AND_USE') THEN
444 l_rt_row.invoice_status_code := 'PENDING';
445 ELSE
446 l_rt_row.invoice_status_code := '';
447 END IF;
448 l_rt_row.qa_collection_id := l_rti_row.qa_collection_id;
449 l_rt_row.mvt_stat_status := 'NEW';
450 l_rt_row.country_of_origin_code := l_rti_row.country_of_origin_code;
451 l_rt_row.oe_order_header_id := l_rti_row.oe_order_header_id;
452 l_rt_row.oe_order_line_id := l_rti_row.oe_order_line_id;
453 l_rt_row.customer_id := l_rti_row.customer_id;
454 l_rt_row.customer_site_id := l_rti_row.customer_site_id;
455
456 IF l_rti_row.validation_flag = 'N' THEN
457 IF p_transaction_type = 'RECEIVE' THEN
458 l_rt_row.transfer_lpn_id := null; --bugfix 4473005
459 ELSE
460 l_rt_row.lpn_id := l_rti_row.lpn_id;
461 l_rt_row.transfer_lpn_id := l_rti_row.transfer_lpn_id;
462 END IF;
463 ELSE
464 --Bug 4401341 : If transaction_type is 'SHIP' and auto_transact_code is
465 --'RECEIVE', then we stamp the lpn_id and transfer_lpn_id columns in
466 --rcv_transactions table.
467 IF p_transaction_type = 'DELIVER' AND
468 l_rti_row.transaction_type = 'RECEIVE'
469 THEN
470 l_rt_row.lpn_id := l_rti_row.transfer_lpn_id;
471 l_rt_row.transfer_lpn_id := l_rti_row.transfer_lpn_id;
472 ELSIF p_transaction_type = 'RECEIVE' AND
473 l_rti_row.transaction_type = 'SHIP'
474 THEN
475 l_rt_row.lpn_id := l_rti_row.transfer_lpn_id;
476 l_rt_row.transfer_lpn_id := l_rti_row.lpn_id;
477 ELSE
478 l_rt_row.lpn_id := l_rti_row.lpn_id;
479 l_rt_row.transfer_lpn_id := l_rti_row.transfer_lpn_id;
480 END IF;
481 END IF;
482
483 l_rt_row.mobile_txn := l_rti_row.mobile_txn;
484 l_rt_row.secondary_quantity := l_rti_row.secondary_quantity;
485 l_rt_row.secondary_unit_of_measure := l_rti_row.secondary_unit_of_measure;
486 IF l_rti_row.parent_transaction_id IS NULL THEN
487 IF l_rti_row.po_line_location_id IS NOT NULL THEN
488 l_rt_row.consigned_flag := RCV_TABLE_FUNCTIONS.get_pll_row_from_id(l_rti_row.po_line_location_id).consigned_flag;
489 END IF;
490 ELSE
491 l_rt_row.consigned_flag := RCV_TABLE_FUNCTIONS.get_rt_row_from_id(l_rti_row.parent_transaction_id).consigned_flag;
492 END IF;
493
494 l_rt_row.lpn_group_id := l_rti_row.lpn_group_id;
495 l_rt_row.amount := l_rti_row.amount;
496 l_rt_row.job_id := l_rti_row.job_id;
497 l_rt_row.timecard_id := l_rti_row.timecard_id;
498 l_rt_row.timecard_ovn := l_rti_row.timecard_ovn;
499 l_rt_row.project_id := l_rti_row.project_id;
500 l_rt_row.task_id := l_rti_row.task_id;
501 l_rt_row.requested_amount := l_rti_row.requested_amount;
502 l_rt_row.material_stored_amount := l_rti_row.material_stored_amount;
503 l_rt_row.replenish_order_line_id := l_rti_row.replenish_order_line_id;
504
505 /*Bug 5517289-Start*/
506 IF ((l_rti_row.source_document_code = 'INVENTORY') OR (l_rti_row.source_document_code = 'REQ')) THEN
507
508 OPEN c_fob_point(l_rti_row.from_organization_id,l_rti_row.to_organization_id);
509 FETCH c_fob_point INTO l_fob_point;
510 CLOSE c_fob_point;
511
512 asn_debug.put_line('FOB Point is :' || l_fob_point);
513
514 BEGIN
515
516 IF (l_fob_point = 2) THEN
517 asn_debug.put_line('Validating the INV accouting period for Organization :'||l_rti_row.from_organization_id);
518 IF (RCV_TRANSACTION_PROCESSOR.get_acct_period_status(p_trx_date => l_rti_row.transaction_date,
519 p_org_id => l_rti_row.from_organization_id)
520 NOT IN ('O', 'F')) THEN
521 asn_debug.put_line('INV accounting period is not opened for the source organization :'|| l_rti_row.from_organization_id);
522 x_error_message := 'PO_INV_NO_OPEN_PERIOD';
523 RETURN;
524 END IF;
525 END IF;
526 EXCEPTION
527 WHEN OTHERS THEN
528 asn_debug.put_line('unexpected error in get_acct_period_status');
529 x_error_message := 'PO_INV_NO_OPEN_PERIOD';
530 RETURN;
531 END;
532 END IF;
533 /*Bug 5517289-End*/
534
535 /* Bug 5842219:
536 source_transaction_num provided in the rti table has to be
537 maintained in the rt table. */
538 l_rt_row.source_transaction_num := l_rti_row.source_transaction_num;
539 asn_debug.put_line('source_transaction_num:' || l_rt_row.source_transaction_num);
540
541 /* lcm changes */
542 l_rt_row.lcm_shipment_line_id := l_rti_row.lcm_shipment_line_id;
543 l_rt_row.unit_landed_cost := l_rti_row.unit_landed_cost;
544 asn_debug.put_line('lcm_shipment_line_id is ' || l_rt_row.lcm_shipment_line_id);
545 asn_debug.put_line('unit_landed_cost is ' || l_rt_row.unit_landed_cost);
546
547 asn_debug.put_line('Inserting RT row (' || l_rt_row.transaction_type || ')...');
548 asn_debug.put_line('transaction_id: ' || l_rt_row.transaction_id
549 || ' parent_transaction_id: ' || l_rt_row.parent_transaction_id
550 || ' interface_transaction_id: ' || l_rt_row.interface_transaction_id
551 || ' group_id: ' || l_rt_row.group_id
552 || ' request_id: ' || l_rt_row.request_id
553 );
554
555 /* GSCC errors come up when we use the foll. insert.
556 * Changing to use the full insert stmts.
557 INSERT INTO RCV_TRANSACTIONS
558 VALUES l_rt_row;
559 */
560 /* Bug: 6487371
561 * Added exception handler to catch the exception when insertion into rcv_transactions
562 * fails due to exception raised in the triggers(for eg; India Localisation triggers) on
563 * rcv_transactions table. Similarly added exception handler for insertion into
564 * po_note_references table. To this rvthinns() function as whole, added one exception handler.
565 * While storing sqlerrm in the x_message_data getting only the first 200 bytes.
566 * without that unhandled exception is raised while copying the sqlerrm. And moreover
567 * in rvtth.lpc rvthinns(), x_msg_data is defined to store only 200 bytes.
568 */
569 BEGIN --Bug: 6487371
570 INSERT INTO rcv_transactions
571 (transaction_id,
572 last_update_date,
573 last_updated_by,
574 created_by,
575 creation_date,
576 last_update_login,
577 request_id,
578 program_application_id,
579 program_id,
580 program_update_date,
581 interface_source_code,
582 interface_source_line_id,
583 user_entered_flag,
584 transaction_type,
585 transaction_date,
586 source_document_code,
587 destination_type_code,
588 location_id,
589 quantity,
590 unit_of_measure,
591 uom_code,
592 primary_quantity,
593 primary_unit_of_measure,
594 source_doc_quantity,
595 source_doc_unit_of_measure,
596 shipment_header_id,
597 shipment_line_id,
598 parent_transaction_id,
599 employee_id,
600 po_header_id,
601 po_release_id,
602 po_line_id,
603 po_line_location_id,
604 po_distribution_id,
605 po_revision_num,
606 requisition_line_id,
607 req_distribution_id,
608 po_unit_price,
609 currency_code,
610 currency_conversion_rate,
611 currency_conversion_date,
612 currency_conversion_type,
613 routing_header_id,
614 routing_step_id,
615 substitute_unordered_code,
616 receipt_exception_flag,
617 inspection_status_code,
618 inspection_quality_code,
619 vendor_id,
620 vendor_site_id,
621 vendor_lot_num,
622 organization_id,
623 from_subinventory, /*FPJ WMS change */
624 from_locator_id,
625 subinventory,
626 locator_id,
627 rma_reference,
628 deliver_to_person_id,
629 deliver_to_location_id,
630 department_code,
631 wip_entity_id,
632 wip_line_id,
633 wip_repetitive_schedule_id,
634 wip_operation_seq_num,
635 wip_resource_seq_num,
636 bom_resource_id,
637 inv_transaction_id,
638 reason_id,
639 destination_context,
640 comments,
641 interface_transaction_id,
642 group_id,
643 attribute_category,
644 attribute1,
645 attribute2,
646 attribute3,
647 attribute4,
648 attribute5,
649 attribute6,
650 attribute7,
651 attribute8,
652 attribute9,
653 attribute10,
654 attribute11,
655 attribute12,
656 attribute13,
657 attribute14,
658 attribute15,
659 movement_id,
660 invoice_status_code, /* BUG 551612 */
661 qa_collection_id,
662 mvt_stat_status,
663 country_of_origin_code,
664 oe_order_header_id,
665 oe_order_line_id,
666 customer_id,
667 customer_site_id,
668 lpn_id,
669 transfer_lpn_id,
670 mobile_txn,
671 secondary_quantity,
672 secondary_unit_of_measure,
673 consigned_flag, /*<CONSIGNED INV RTP FPI>*/
674 lpn_group_id, /*FPJ WMS */
675 amount,
676 job_id,
677 timecard_id,
678 timecard_ovn,
679 project_id,
680 task_id,
681 requested_amount, --Complex work
682 material_stored_amount, -- Complex Work
683 replenish_order_line_id, -- Bug 5367699
684 source_transaction_num, -- Bug 5842219
685 lcm_shipment_line_id, -- lcm changes
686 unit_landed_cost) -- lcm changes
687 VALUES
688 (l_rt_row.transaction_id,
689 l_rt_row.last_update_date,
690 l_rt_row.last_updated_by,
691 l_rt_row.created_by,
692 l_rt_row.creation_date,
693 l_rt_row.last_update_login,
694 l_rt_row.request_id,
695 l_rt_row.program_application_id,
696 l_rt_row.program_id,
697 l_rt_row.program_update_date,
698 l_rt_row.interface_source_code,
699 l_rt_row.interface_source_line_id,
700 l_rt_row.user_entered_flag,
701 l_rt_row.transaction_type,
702 l_rt_row.transaction_date,
703 l_rt_row.source_document_code,
704 l_rt_row.destination_type_code,
705 l_rt_row.location_id,
706 l_rt_row.quantity,
707 l_rt_row.unit_of_measure,
708 l_rt_row.uom_code,
709 l_rt_row.primary_quantity,
710 l_rt_row.primary_unit_of_measure,
711 l_rt_row.source_doc_quantity,
712 l_rt_row.source_doc_unit_of_measure,
713 l_rt_row.shipment_header_id,
714 l_rt_row.shipment_line_id,
715 l_rt_row.parent_transaction_id,
716 l_rt_row.employee_id,
717 l_rt_row.po_header_id,
718 l_rt_row.po_release_id,
719 l_rt_row.po_line_id,
720 l_rt_row.po_line_location_id,
721 l_rt_row.po_distribution_id,
722 l_rt_row.po_revision_num,
723 l_rt_row.requisition_line_id,
724 l_rt_row.req_distribution_id,
725 l_rt_row.po_unit_price,
726 l_rt_row.currency_code,
727 l_rt_row.currency_conversion_rate,
728 l_rt_row.currency_conversion_date,
729 l_rt_row.currency_conversion_type,
730 l_rt_row.routing_header_id,
731 l_rt_row.routing_step_id,
732 l_rt_row.substitute_unordered_code,
733 l_rt_row.receipt_exception_flag,
734 l_rt_row.inspection_status_code,
735 l_rt_row.inspection_quality_code,
736 l_rt_row.vendor_id,
737 l_rt_row.vendor_site_id,
738 l_rt_row.vendor_lot_num,
739 l_rt_row.organization_id,
740 l_rt_row.from_subinventory, /*FPJ WMS change */
741 l_rt_row.from_locator_id,
742 l_rt_row.subinventory,
743 l_rt_row.locator_id,
744 l_rt_row.rma_reference,
745 l_rt_row.deliver_to_person_id,
746 l_rt_row.deliver_to_location_id,
747 l_rt_row.department_code,
748 l_rt_row.wip_entity_id,
749 l_rt_row.wip_line_id,
750 l_rt_row.wip_repetitive_schedule_id,
751 l_rt_row.wip_operation_seq_num,
752 l_rt_row.wip_resource_seq_num,
753 l_rt_row.bom_resource_id,
754 l_rt_row.inv_transaction_id,
755 l_rt_row.reason_id,
756 l_rt_row.destination_context,
757 l_rt_row.comments,
758 l_rt_row.interface_transaction_id,
759 l_rt_row.group_id,
760 l_rt_row.attribute_category,
761 l_rt_row.attribute1,
762 l_rt_row.attribute2,
763 l_rt_row.attribute3,
764 l_rt_row.attribute4,
765 l_rt_row.attribute5,
766 l_rt_row.attribute6,
767 l_rt_row.attribute7,
768 l_rt_row.attribute8,
769 l_rt_row.attribute9,
770 l_rt_row.attribute10,
771 l_rt_row.attribute11,
772 l_rt_row.attribute12,
773 l_rt_row.attribute13,
774 l_rt_row.attribute14,
775 l_rt_row.attribute15,
776 l_rt_row.movement_id,
777 l_rt_row.invoice_status_code, /* BUG 551612 */
778 l_rt_row.qa_collection_id,
779 l_rt_row.mvt_stat_status,
780 l_rt_row.country_of_origin_code,
781 l_rt_row.oe_order_header_id,
782 l_rt_row.oe_order_line_id,
783 l_rt_row.customer_id,
784 l_rt_row.customer_site_id,
785 l_rt_row.lpn_id,
786 l_rt_row.transfer_lpn_id,
787 l_rt_row.mobile_txn,
788 l_rt_row.secondary_quantity,
789 l_rt_row.secondary_unit_of_measure,
790 l_rt_row.consigned_flag, /*<CONSIGNED INV RTP FPI>*/
791 l_rt_row.lpn_group_id, /*FPJ WMS */
792 l_rt_row.amount,
793 l_rt_row.job_id,
794 l_rt_row.timecard_id,
795 l_rt_row.timecard_ovn,
796 l_rt_row.project_id,
797 l_rt_row.task_id,
798 l_rt_row.requested_amount, --Complex work
799 l_rt_row.material_stored_amount, -- Complex Work
800 l_rt_row.replenish_order_line_id, -- Bug 5367699
801 l_rt_row.source_transaction_num, -- Bug 5842219
802 l_rt_row.lcm_shipment_line_id, -- lcm changes
803 l_rt_row.unit_landed_cost); -- lcm changes
804 EXCEPTION --Bug: 6487371
805 when others then
806 asn_debug.put_line('Error occured while inserting into rcv_transactions...'||sqlerrm);
807 x_error_message := substr(sqlerrm,1,200);
808 RETURN;
809 END;--Bug: 6487371
810
811 x_rt_id := l_rt_row.transaction_id;
812
813 BEGIN
814 asn_debug.put_line('Updating child RTI rows');
815
816 /* FPJ FASTFORWARD START.
817 * If this rti row is a parent of any rti rows, then
818 * we need to update the parent_transaction_id of
819 * children with this new transaction id since it will
820 * not be populated at the pre-processor stage.
821 * Update only those rows which has parent_transaction_id
822 * as null since if the user has populated parent_transaction_id
823 * and parent_interface_txn_id, then we dont want to override
824 * it.
825 */
826 UPDATE rcv_transactions_interface
827 SET parent_transaction_id = l_rt_row.transaction_id
828 , shipment_line_id = l_rt_row.shipment_line_id
829 WHERE parent_interface_txn_id = l_rti_row.interface_transaction_id
830 AND parent_transaction_id IS NULL;
831 EXCEPTION
832 WHEN NO_DATA_FOUND THEN
833 NULL;
834 END;
835
836 IF NVL(l_rti_row.qa_collection_id,0) <> 0 THEN
837 asn_debug.put_line('Enabling Quality Inspection Results');
838
839 DECLARE
840 l_return_status VARCHAR2(1);
841 l_msg_count NUMBER := 0;
842 l_msg_data VARCHAR2(2000);
843 BEGIN
844 QA_RESULT_GRP.enable
845 ( 1
846 , 'F'
847 , 'F'
848 , 0
849 , l_rti_row.qa_collection_id
850 , l_return_status
851 , l_msg_count
852 , l_msg_data
853 );
854
855 IF l_msg_count IS NULL OR l_msg_count = -1 THEN
856 asn_debug.put_line('QA Actions Failed: ' || l_msg_data);
857 x_error_message := 'QA_ACTIONS_FAILED';
858 RETURN;
859 END IF;
860 EXCEPTION
861 WHEN OTHERS THEN
862 asn_debug.put_line('QA Actions Failed: ' || SQLERRM);
863 x_error_message := 'QA_ACTIONS_FAILED';
864 RETURN;
865 END;
866 END IF;
867
868 IF l_rti_row.erecord_id IS NOT NULL AND l_rti_row.erecord_id > 0 THEN
869 DECLARE
870 l_event_name VARCHAR2(240);
871 l_event_key VARCHAR2(240);
872 l_erecord_id NUMBER;
873 l_return_status VARCHAR2(1);
874 l_msg_count NUMBER := 0;
875 l_msg_data VARCHAR2(2000);
876 BEGIN
877 -- FPJ EDR integration
878 -- get the event name and erecord id
879 CASE l_rti_row.transaction_type
880 WHEN 'ACCEPT' THEN l_event_name := 'oracle.apps.po.rcv.inspect';
881 WHEN 'REJECT' THEN l_event_name := 'oracle.apps.po.rcv.inspect';
882 WHEN 'DELIVER' THEN l_event_name := 'oracle.apps.po.rcv.deliver';
883 WHEN 'TRANSFER' THEN l_event_name := 'oracle.apps.po.rcv.transfer';
884 END CASE;
885
886 l_event_key := l_rti_row.parent_transaction_id || '-' || l_rti_row.qa_collection_id;
887 l_erecord_id := l_rti_row.erecord_id;
888
889 -- FPJ EDR integration
890 -- acknowledge erecord has been enabled
891 QA_EDR_STANDARD.SEND_ACKN
892 ( 1.0
893 , FND_API.G_FALSE
894 , l_return_status
895 , l_msg_count
896 , l_msg_data
897 , l_event_name
898 , l_event_key
899 , l_rti_row.erecord_id
900 , 'SUCCESS'
901 , NULL
902 , 'Receiving Transaction Processor'
903 , FND_API.G_TRUE
904 );
905
906 IF l_return_status <> 'S' THEN
907 asn_debug.put_line('QA_EDR_STANDARD.SEND_ACKN failed with return status: ' || l_return_status);
908 IF l_msg_count > 0 THEN
909 asn_debug.put_line(l_msg_data);
910 END IF;
911
912 x_error_message := 'EDR_SEND_ACKN_FAILED';
913 RETURN;
914 END IF;
915 EXCEPTION
916 WHEN OTHERS THEN
917 asn_debug.put_line('QA_EDR_STANDARD.SEND_ACKN failed');
918 IF l_msg_count > 0 THEN
919 asn_debug.put_line(l_msg_data);
920 END IF;
921
922 asn_debug.put_line(SQLERRM);
923
924 x_error_message := 'EDR_SEND_ACKN_FAILED';
925 RETURN;
926 END;
927 END IF;
928
929 IF get_product_install_status('CSE') = 'I' THEN
930 DECLARE
931 l_return_status VARCHAR2(1);
932 BEGIN
933 asn_debug.put_line('Calling CSE post transaction exit');
934 CSE_RCVTXN_PKG.PostTransaction_Exit
935 ( l_rt_row.transaction_id
936 , l_rti_row.interface_transaction_id
937 , l_return_status
938 );
939 EXCEPTION
940 WHEN OTHERS THEN
941 NULL;
942 END;
943 END IF;
944
945 l_common_receiving := p_transaction_type = 'RECEIVE' AND
946 get_product_install_status('GMI') = 'I' AND
947 gml_po_for_process.check_po_for_proc;
948
949 IF l_common_receiving THEN
950 BEGIN
951 asn_debug.put_line('Performing Common Receiving quality event');
952 gml_rcv_db_common.raise_quality_event
953 ( l_rt_row.transaction_id
954 , l_rti_row.item_id
955 , l_rti_row.to_organization_id
956 );
957 EXCEPTION
958 WHEN OTHERS THEN
959 NULL;
960 END;
961 END IF;
962
963 asn_debug.put_line('Updating PO Note References');
964
965 DECLARE
966 l_row_id NUMBER;
967 BEGIN
968 UPDATE po_note_references
969 SET table_name = 'RCV_TRANSACTIONS',
970 column_name = 'TRANSACTION_ID',
971 foreign_id = l_rt_row.transaction_id
972 WHERE table_name = 'RCV_TRANSACTIONS_INTERFACE'
973 AND column_name = 'INTERFACE_TRANSACTION_ID'
974 AND foreign_id = l_rti_row.interface_transaction_id;
975
976 IF SQL%ROWCOUNT > 0 AND
977 p_transaction_type = 'DELIVER' AND
978 l_rti_row.auto_transact_code = 'DELIVER' AND
979 p_receive_id IS NOT NULL
980 THEN
981 INSERT INTO po_note_references
982 (po_note_reference_id,
983 last_update_date,
984 last_updated_by,
985 last_update_login,
986 creation_date,
987 created_by,
988 po_note_id,
989 table_name,
990 column_name,
991 foreign_id,
992 sequence_num,
993 storage_type,
994 request_id,
995 program_application_id,
996 program_id,
997 program_update_date,
998 attribute_category,
999 attribute1,
1000 attribute2,
1001 attribute3,
1002 attribute4,
1003 attribute5,
1004 attribute6,
1005 attribute7,
1006 attribute8,
1007 attribute9,
1008 attribute10,
1009 attribute11,
1010 attribute12,
1011 attribute13,
1012 attribute14,
1013 attribute15)
1014 SELECT po_note_references_s.nextval,
1015 last_update_date,
1016 last_updated_by,
1017 last_update_login,
1018 creation_date,
1019 created_by,
1020 po_note_id,
1021 table_name,
1022 column_name,
1023 l_rt_row.transaction_id,
1024 sequence_num,
1025 storage_type,
1026 request_id,
1027 program_application_id,
1028 program_id,
1029 program_update_date,
1030 attribute_category,
1031 attribute1,
1032 attribute2,
1033 attribute3,
1034 attribute4,
1035 attribute5,
1036 attribute6,
1037 attribute7,
1038 attribute8,
1039 attribute9,
1040 attribute10,
1041 attribute11,
1042 attribute12,
1043 attribute13,
1044 attribute14,
1045 attribute15
1046 FROM po_note_references
1047 WHERE table_name = 'RCV_TRANSACTIONS'
1048 AND column_name = 'TRANSACTION_ID'
1049 AND foreign_id = p_receive_id;
1050 END IF;
1051 EXCEPTION--Bug: 6487371
1052 when others then
1053 asn_debug.put_line('Error while inserting into po_note_references...'||sqlerrm);
1054 x_error_message := substr(sqlerrm,1,200);
1055 RETURN;--Bug: 6487371
1056 END;
1057 EXCEPTION--Bug: 6487371
1058 when others then
1059 asn_debug.put_line('Unexpected error occured...'||sqlerrm);
1060 x_error_message := substr(sqlerrm,1,200);
1061 RETURN;--Bug: 6487371
1062
1063 asn_debug.put_line('Done with RVTTHIns');
1064 END RVTTHIns;
1065
1066 END RCV_TRANSACTION_PROCESSOR;