[Home] [Help]
PACKAGE BODY: APPS.RCV_SEEDEVENTS_PVT
Source
1 PACKAGE BODY RCV_SeedEvents_PVT AS
2 /* $Header: RCVVRUTB.pls 120.14 2010/12/30 18:46:21 hyu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_SeedEvents_PVT';
5 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 G_LOG_HEAD CONSTANT VARCHAR2(40) := 'po.plsql.'||G_PKG_NAME;
7
8 -- API name : Seed_RAEEvent
9 -- Type : Private
10 -- Function : To seed accounting event in RCV_ACCOUNTING_EVENTS.
11 -- Pre-reqs :
12 -- Parameters :
13 -- IN : p_api_version IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2 Optional
15 -- Default = FND_API.G_FALSE
16 -- p_commit IN VARCHAR2 Optional
17 -- Default = FND_API.G_FALSE
18 -- p_validation_level IN NUMBER Optional
19 -- Default = FND_API.G_VALID_LEVEL_FULL
20 -- p_event_source IN VARCHAR2 Required
21 -- p_event_type_id IN NUMBER Required
22 -- p_rcv_transaction_id IN NUMBER Optional
23 -- p_inv_distribution_id IN NUMBER Optional
24 -- p_po_distribution_id IN NUMBER Required
25 -- p_direct_delivery_flag IN VARCHAR2 Optional
26 -- p_cross_ou_flag IN VARCHAR2 Optional
27 -- p_procurement_org_flag IN VARCHAR2 Optional
28 -- p_org_id IN NUMBER Required
29 -- p_organization_id IN NUMBER Optional
30 -- p_transfer_org_id IN NUMBER Optional
31 -- p_transfer_organization_id IN NUMBER Optional
32 -- p_transaction_forward_flow_rec mtl_transaction_flow_rec_type,
33 -- p_transaction_reverse_flow_rec mtl_transaction_flow_rec_type,
34 -- p_transaction_flow_rec IN mtl_transaction_flow_rec_type
35 -- p_unit_price IN NUMBER Required
36 -- p_prior_unit_price IN NUMBER Optional
37 -- p_lcm_flag IN VARCHAR2
38 --
39 -- OUT : x_return_status OUT VARCHAR2(1)
40 -- x_msg_count OUT NUMBER
41 -- x_msg_data OUT VARCHAR2(2000)
42 -- x_rcv_event OUT RCV_SeedEvents_PVT.rcv_event_tbl_type;
43 -- Version :
44 -- Initial version 1.0
45 --
46 --
47 -- Notes : This API is used to seed events in RCV_ACCOUNTING_EVENTS table.
48 --
49 -- End of comments
50 -------------------------------------------------------------------------------
51 PROCEDURE Seed_RAEEvent(
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2,
54 p_commit IN VARCHAR2,
55 p_validation_level IN NUMBER,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59
60 p_event_source IN VARCHAR2,
61 p_event_type_id IN NUMBER,
62 p_rcv_transaction_id IN NUMBER,
63 p_inv_distribution_id IN NUMBER,
64 p_po_distribution_id IN NUMBER,
65 p_direct_delivery_flag IN VARCHAR2,
66 p_cross_ou_flag IN VARCHAR2,
67 p_procurement_org_flag IN VARCHAR2,
68 p_ship_to_org_flag IN VARCHAR2,
69 p_drop_ship_flag IN NUMBER,
70 p_org_id IN NUMBER,
71 p_organization_id IN NUMBER,
72 p_transfer_org_id IN NUMBER,
73 p_transfer_organization_id IN NUMBER,
74 p_trx_flow_header_id IN NUMBER,
75 p_transaction_forward_flow_rec INV_TRANSACTION_FLOW_PUB.mtl_transaction_flow_rec_type,
76 p_transaction_reverse_flow_rec INV_TRANSACTION_FLOW_PUB.mtl_transaction_flow_rec_type,
77 p_unit_price IN NUMBER,
78 p_prior_unit_price IN NUMBER,
79 /* Support for Landed Cost Management */
80 p_lcm_flag IN VARCHAR2,
81 x_rcv_event OUT NOCOPY RCV_SeedEvents_PVT.rcv_event_rec_type
82
83
84 ) IS
85 l_api_name CONSTANT VARCHAR2(30) := 'Seed_RAEEvent';
86 l_api_version CONSTANT NUMBER := 1.0;
87
88 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
89 l_msg_count NUMBER := 0;
90 l_msg_data VARCHAR2(8000) := '';
91 l_stmt_num NUMBER := 0;
92 l_api_message VARCHAR2(1000);
93
94 l_rcv_event RCV_SeedEvents_PVT.rcv_event_rec_type;
95 l_transaction_amount NUMBER := 0;
96 l_source_doc_quantity NUMBER := 0;
97 l_transaction_quantity NUMBER := 0;
98 l_ic_pricing_option NUMBER := 1;
99 l_unit_price NUMBER := 0;
100 l_unit_nr_tax NUMBER := 0;
101 l_unit_rec_tax NUMBER := 0;
102 l_prior_nr_tax NUMBER := 0;
103 l_prior_rec_tax NUMBER := 0;
104
105 l_currency_code VARCHAR2(15);
106 l_currency_conversion_rate NUMBER;
107 l_currency_conversion_date DATE;
108 l_currency_conversion_type VARCHAR2(30);
109
110 l_incr_transfer_price NUMBER := 0;
111 l_incr_currency_code VARCHAR2(15) := NULL;
112
113 l_dest_org_id NUMBER;
114 l_trx_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
115 l_primary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
116 l_primary_qty NUMBER;
117 l_credit_acct_id NUMBER;
118 l_debit_acct_id NUMBER;
119 l_ic_cogs_acct_id NUMBER;
120 /* Support for Landed Cost Management */
121 l_lcm_acct_id NUMBER;
122 l_unit_landed_cost NUMBER;
123
124 l_asset_option NUMBER;
125 l_expense_option NUMBER;
126 l_detail_accounting_flag VARCHAR2(1);
127
128 l_gl_installed BOOLEAN := FALSE;
129 l_status VARCHAR2(1);
130 l_industry VARCHAR2(1);
131 l_oracle_schema VARCHAR2(30);
132 l_encumbrance_flag VARCHAR2(1);
133 l_ussgl_option VARCHAR2(1);
134
135 BEGIN
136 -- Standard start of API savepoint
137 SAVEPOINT Seed_RAEEvent_PVT;
138
139 l_stmt_num := 0;
140
141 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
142 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
143 ,'Seed_RAEEvent <<');
144 END IF;
145
146 -- Standard call to check for call compatibility
147 IF NOT FND_API.Compatible_API_Call (
148 l_api_version,
149 p_api_version,
150 l_api_name,
151 G_PKG_NAME ) THEN
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END IF;
154
155 -- Initialize message list if p_init_msg_list is set to TRUE
156 IF FND_API.to_Boolean(p_init_msg_list) THEN
157 FND_MSG_PUB.initialize;
158 END IF;
159
160 -- Initialize API return status to success
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
164 l_api_message := 'Seed_RAEEvent : PARAMETERS 1:'||
165 ' p_event_source : '||p_event_source||
166 ' p_event_type_id : '||p_event_type_id||
167 ' p_rcv_transaction_id : '||p_rcv_transaction_id||
168 ' p_inv_distribution_id : '||p_inv_distribution_id||
169 ' p_po_distribution_id : '||p_po_distribution_id||
170 ' p_direct_delivery_flag : '||p_direct_delivery_flag||
171 ' p_cross_ou_flag : '||p_cross_ou_flag;
172 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
173 ,l_api_message);
174
175 l_api_message := 'Seed_RAEEvent : PARAMETERS 2:'||
176 ' p_procurement_org_flag : '||p_procurement_org_flag||
177 ' p_ship_to_org_flag : '||p_ship_to_org_flag||
178 ' p_drop_ship_flag : '||p_drop_ship_flag||
179 ' p_org_id : '||p_org_id||
180 ' p_organization_id : '||p_organization_id||
181 ' p_transfer_org_id : '||p_transfer_org_id||
182 ' p_transfer_organization_id : '||p_transfer_organization_id||
183 ' p_trx_flow_header_id : '||p_trx_flow_header_id||
184 ' p_unit_price : '||p_unit_price||
185 ' p_prior_unit_price : '||p_prior_unit_price;
186
187 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
188 ,l_api_message);
189
190 END IF;
191
192
193 l_stmt_num := 15;
194 l_rcv_event.event_source := p_event_source;
195 l_rcv_event.event_type_id := p_event_type_id;
196 l_rcv_event.rcv_transaction_id := p_rcv_transaction_id;
197 l_rcv_event.cross_ou_flag := p_cross_ou_flag;
198 l_rcv_event.procurement_org_flag := p_procurement_org_flag;
199 l_rcv_event.ship_to_org_flag := p_ship_to_org_flag;
200 l_rcv_event.drop_ship_flag := p_drop_ship_flag;
201 l_rcv_event.po_distribution_id := p_po_distribution_id;
202 l_rcv_event.direct_delivery_flag := p_direct_delivery_flag;
203
204 -- Initialize PO Information
205 IF (p_event_source = 'INVOICEMATCH') THEN
206 -- This source is only for period end accruals, one-time items
207 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
208 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
209 ,'Getting PO information from Invoice');
210 END IF;
211
212 l_stmt_num := 20;
213
214
215 SELECT POD.po_header_id,
216 POL.po_line_id,
217 POD.po_distribution_id,
218 POD.destination_type_code,
219 POLL.line_location_id,
220 sysdate,
221 POL.item_id,
222 APID.quantity_invoiced,
223 POLL.unit_meas_lookup_code,
224 POH.currency_code
225 INTO l_rcv_event.po_header_id,
226 l_rcv_event.po_line_id,
227 l_rcv_event.po_distribution_id,
228 l_rcv_event.destination_type_code,
229 l_rcv_event.po_line_location_id,
230 l_rcv_event.transaction_date,
231 l_rcv_event.item_id,
232 l_rcv_event.source_doc_quantity,
233 l_rcv_event.source_doc_uom,
234 l_rcv_event.currency_code
235 FROM ap_invoice_distributions APID,
236 po_distributions POD,
237 po_line_locations POLL,
238 po_lines POL,
239 po_headers POH
240 WHERE APID.invoice_distribution_id = p_inv_distribution_id
241 AND POD.po_distribution_id = APID.po_distribution_id
242 AND POD.line_location_id = POLL.line_location_id
243 AND POL.po_line_id = POLL.po_line_id
244 AND POH.po_header_id = POD.po_header_id;
245
246 l_rcv_event.inv_distribution_id := p_inv_distribution_id;
247 l_rcv_event.transaction_quantity := l_rcv_event.source_doc_quantity;
248 l_rcv_event.transaction_uom := l_rcv_event.source_doc_uom;
249 ELSE
250 l_stmt_num := 30;
251
252 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
253 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
254 ,'Getting PO information from Receiving Transaction');
255 END IF;
256
257
258 SELECT RT.po_header_id,
259 RT.po_release_id,
260 RT.po_line_id,
261 RT.po_line_location_id,
262 RT.transaction_date,
263 POL.item_id,
264 POLL.ship_to_organization_id,
265 RT.unit_of_measure,
266 RT.source_doc_unit_of_measure,
267 POH.currency_code,
268 POD.destination_type_code,
269 /* Support for Landed Cost Management */
270 rt.unit_landed_cost
271 INTO l_rcv_event.po_header_id,
272 l_rcv_event.po_release_id,
273 l_rcv_event.po_line_id,
274 l_rcv_event.po_line_location_id,
275 l_rcv_event.transaction_date,
276 l_rcv_event.item_id,
277 l_dest_org_id,
278 l_rcv_event.transaction_uom,
279 l_rcv_event.source_doc_uom,
280 l_rcv_event.currency_code,
281 l_rcv_event.destination_type_code,
282 /* Support for Landed Cost Management */
283 l_rcv_event.unit_landed_cost
284 FROM rcv_transactions RT,
285 po_lines POL,
286 po_line_locations POLL,
287 po_headers POH,
288 po_distributions POD
289 WHERE RT.transaction_id = p_rcv_transaction_id
290 AND POH.po_header_id = RT.po_header_id
291 AND POL.po_line_id = RT.po_line_id
292 AND POLL.line_location_id = RT.po_line_location_id
293 AND POD.po_distribution_id = p_po_distribution_id;
294
295 END IF;
296
297 -- p_transaction_forward_flow_rec represents the transaction flow record where the
298 -- start_org_id is the org_id where the event is being seeded.
299 -- p_transaction_reverse_flow_rec represents the transaction flow record where the
300 -- end_org_id is the org_id where the event is being seeded.
301 -- We need both because some information is derived based on the forward flow and
302 -- some based on the reverse flow :
303 -- transfer_price : based on reverse flow
304 -- I/C accrual : based on reverse flow
305 -- I/C cogs : based on forward flow. (used in creating transactions in inventory.
306 -- The events will be seeded such that the transfer_org_id will represent the reverse
307 -- flow.
308 l_stmt_num := 40;
309
310 l_rcv_event.org_id := p_org_id;
311 l_rcv_event.transfer_org_id := p_transfer_org_id;
312 l_rcv_event.organization_id := p_organization_id;
313 l_rcv_event.transfer_organization_id := p_transfer_organization_id;
314 l_rcv_event.trx_flow_header_id := p_trx_flow_header_id;
315
316 -- Get the Set Of Books Identifier
317 l_stmt_num := 50;
318 SELECT ledger_id
319 INTO l_rcv_event.set_of_books_id
320 FROM cst_acct_info_v
321 WHERE organization_id = p_organization_id;
322
323
324 -- Initialize transaction date
325 IF (p_event_type_id IN (RCV_SeedEvents_PVT.ADJUST_RECEIVE, RCV_SeedEvents_PVT.ADJUST_DELIVER,
326 RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
327 l_rcv_event.transaction_date := SYSDATE;
328 END IF;
329
330 -- Encumbrance cannot be enabled for global procurement scenarios.
331 IF(l_rcv_event.trx_flow_header_id IS NULL) THEN
332 -- If GL is installed, and either encumbrance is enabled or USSGL profile is enabled,
333 -- journal import is called by the receiving TM. The group_id passed by receiving should
334 -- be stamped on the event in this scenario.
335 l_stmt_num := 60;
336 l_gl_installed := FND_INSTALLATION.GET_APP_INFO ('SQLGL',
337 l_status,
338 l_industry,
339 l_oracle_schema);
340
341 IF(l_status = 'I') THEN
342 l_stmt_num := 70;
343 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
344 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
345 ,'Checking if encumbrance is enabled.');
346 END IF;
347 RCV_SeedEvents_PVT.Check_EncumbranceFlag(
348 p_api_version => 1.0,
349 x_return_status => l_return_status,
350 x_msg_count => l_msg_count,
351 x_msg_data => l_msg_data,
352 p_rcv_sob_id => l_rcv_event.set_of_books_id,
353 x_encumbrance_flag => l_encumbrance_flag,
354 x_ussgl_option => l_ussgl_option);
355 IF l_return_status <> FND_API.g_ret_sts_success THEN
356 l_api_message := 'Error in checking for encumbrance flag ';
357 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
358 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
359 ,'SeedRAEEvents : '||l_stmt_num||' : '||l_api_message);
360 END IF;
361 RAISE FND_API.g_exc_unexpected_error;
362 END IF;
363
364 END IF;
365 END IF;
366
367 l_stmt_num := 80;
368
369 -- Check if event is for a service line type
370 SELECT decode(POLL.matching_basis, 'AMOUNT', 'Y', 'N')
371 INTO l_rcv_event.service_flag
372 FROM po_line_locations POLL
373 WHERE POLL.line_location_id = l_rcv_event.po_line_location_id;
374
375 l_stmt_num := 90;
376
377
378 -- Initialize Unit Price
379 IF(p_event_type_id IN (RCV_SeedEvents_PVT.ADJUST_RECEIVE,RCV_SeedEvents_PVT.ADJUST_DELIVER)) THEN
380 l_rcv_event.unit_price := p_unit_price;
381 l_rcv_event.prior_unit_price := p_prior_unit_price;
382 ELSIF l_rcv_event.service_flag = 'Y' THEN
383 l_stmt_num := 100;
384
385 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
386 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
387 ,'Service line type : Getting Transaction Amount');
388 END IF;
389
390 Get_TransactionAmount( p_api_version => l_api_version,
391 x_return_status => l_return_status,
392 x_msg_count => l_msg_count,
393 x_msg_data => l_msg_data,
394 p_rcv_event => l_rcv_event,
395 x_transaction_amount => l_transaction_amount);
396 IF l_return_status <> FND_API.g_ret_sts_success THEN
397 l_api_message := 'Error getting transaction amount';
398 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
399 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
400 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
401 END IF;
402 RAISE FND_API.g_exc_unexpected_error;
403 END IF;
404
405 l_rcv_event.transaction_amount := l_transaction_amount;
406 ELSE
407 l_stmt_num := 110;
408
409 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
410 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
411 ,'Non Service Line Type : Getting Unit Price');
412 END IF;
413
414 IF (p_event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
415 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
416 l_asset_option := p_transaction_reverse_flow_rec.asset_item_pricing_option;
417 l_expense_option := p_transaction_reverse_flow_rec.expense_item_pricing_option;
418 ELSE
419 l_asset_option := p_transaction_forward_flow_rec.asset_item_pricing_option;
420 l_expense_option := p_transaction_forward_flow_rec.expense_item_pricing_option;
421 END IF;
422 Get_UnitPrice( p_api_version => l_api_version,
423 x_return_status => l_return_status,
424 x_msg_count => l_msg_count,
425 x_msg_data => l_msg_data,
426 p_rcv_event => l_rcv_event,
427 p_asset_item_pricing_option => l_asset_option,
428 p_expense_item_pricing_option => l_expense_option,
429 /* Support for Landed Cost Management */
430 p_lcm_flag => p_lcm_flag,
431 x_intercompany_pricing_option => l_ic_pricing_option,
432 x_unit_price => l_unit_price,
433 /* Support for Landed Cost Management */
434 x_unit_landed_cost => l_unit_landed_cost,
435 x_currency_code => l_currency_code,
436 x_incr_transfer_price => l_incr_transfer_price,
437 x_incr_currency_code => l_incr_currency_code);
438 IF l_return_status <> FND_API.g_ret_sts_success THEN
439 l_api_message := 'Error getting unit price';
440 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
441 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
442 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
443 END IF;
444 RAISE FND_API.g_exc_unexpected_error;
445 END IF;
446
447 l_rcv_event.intercompany_pricing_option := l_ic_pricing_option;
448 l_rcv_event.currency_code := l_currency_code;
449 l_rcv_event.unit_price := l_unit_price;
450 l_rcv_event.intercompany_price := l_incr_transfer_price;
451 l_rcv_event.intercompany_curr_code := l_incr_currency_code;
452 /* Support for Landed Cost Management */
453 l_rcv_event.unit_landed_cost := l_unit_landed_cost;
454 END IF;
455
456 -- Initialize Transaction Quantity
457 IF l_rcv_event.service_flag = 'N' THEN
458 l_stmt_num := 120;
459
460 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
461 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
462 ,'Non Service line type : Getting Quantity');
463 END IF;
464
465 Get_Quantity( p_api_version => l_api_version,
466 x_return_status => l_return_status,
467 x_msg_count => l_msg_count,
468 x_msg_data => l_msg_data,
469 p_rcv_event => l_rcv_event,
470 x_source_doc_quantity => l_source_doc_quantity);
471
472 IF l_return_status <> FND_API.g_ret_sts_success THEN
473 l_api_message := 'Error getting quantity';
474 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
475 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
476 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
477 END IF;
478 RAISE FND_API.g_exc_unexpected_error;
479 END IF;
480
481 l_rcv_event.source_doc_quantity := l_source_doc_quantity;
482
483 -- If transaction quantity is 0, then no event should be seeded.
484 IF (l_source_doc_quantity = 0) THEN
485 x_return_status := 'W';
486
487 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
488 l_api_message := 'Transaction Quantity is 0. Returning without seeding event.';
489 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
490 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
491 END IF;
492
493 return;
494 END IF;
495
496 END IF;
497
498
499 l_stmt_num := 130;
500
501 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
502 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
503 ,'Getting Tax');
504 END IF;
505 Get_UnitTax( p_api_version => l_api_version,
506 x_return_status => l_return_status,
507 x_msg_count => l_msg_count,
508 x_msg_data => l_msg_data,
509 p_rcv_event => l_rcv_event,
510 x_unit_nr_tax => l_unit_nr_tax,
511 x_unit_rec_tax => l_unit_rec_tax,
512 x_prior_nr_tax => l_prior_nr_tax,
513 x_prior_rec_tax => l_prior_rec_tax);
514 IF l_return_status <> FND_API.g_ret_sts_success THEN
515 l_api_message := 'Error getting tax';
516 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
517 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
518 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
519 END IF;
520 RAISE FND_API.g_exc_unexpected_error;
521 END IF;
522
523 l_rcv_event.unit_nr_tax := l_unit_nr_tax;
524 l_rcv_event.unit_rec_tax := l_unit_rec_tax;
525 l_rcv_event.prior_nr_tax := l_prior_nr_tax;
526 l_rcv_event.prior_rec_tax := l_prior_rec_tax;
527
528
529 l_stmt_num := 140;
530
531 IF l_rcv_event.service_flag = 'N' THEN
532 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
533 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
534 ,'Getting UOM');
535 END IF;
536 Convert_UOM( p_api_version => l_api_version,
537 x_return_status => l_return_status,
538 x_msg_count => l_msg_count,
539 x_msg_data => l_msg_data,
540 p_event_rec => l_rcv_event,
541 x_transaction_qty => l_transaction_quantity,
542 x_primary_uom => l_primary_uom,
543 x_primary_qty => l_primary_qty,
544 x_trx_uom_code => l_trx_uom_code);
545 IF l_return_status <> FND_API.g_ret_sts_success THEN
546 l_api_message := 'Error Converting UOM';
547 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
548 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
549 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
550 END IF;
551 RAISE FND_API.g_exc_unexpected_error;
552 END IF;
553
554 l_rcv_event.transaction_quantity := l_transaction_quantity;
555 l_rcv_event.primary_uom := l_primary_uom;
556 l_rcv_event.primary_quantity := l_primary_qty;
557 l_rcv_event.trx_uom_code := l_trx_uom_code;
558
559 END IF;
560
561 -- Initialize Currency Information
562 l_stmt_num := 150;
563
564
565 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
566 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
567 ,'Getting Currency Information');
568 END IF;
569 Get_Currency( p_api_version => l_api_version,
570 x_return_status => l_return_status,
571 x_msg_count => l_msg_count,
572 x_msg_data => l_msg_data,
573 p_rcv_event => l_rcv_event,
574 x_currency_code => l_currency_code,
575 x_currency_conversion_rate => l_currency_conversion_rate,
576 x_currency_conversion_date => l_currency_conversion_date,
577 x_currency_conversion_type => l_currency_conversion_type);
578
579 IF l_return_status <> FND_API.g_ret_sts_success THEN
580 l_api_message := 'Error Getting Currency';
581 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
582 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
583 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
584 END IF;
585 RAISE FND_API.g_exc_unexpected_error;
586 END IF;
587
588 l_rcv_event.currency_code := l_currency_code;
589 l_rcv_event.currency_conversion_rate := l_currency_conversion_rate;
590 l_rcv_event.currency_conversion_date := l_currency_conversion_date;
591 l_rcv_event.currency_conversion_type := l_currency_conversion_type;
592
593 -- Get Debit and Credit Accounts
594 l_stmt_num := 160;
595
596 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
597 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
598 ,'Getting Debit and Credit Accounts');
599 END IF;
600
601 Get_Accounts(p_api_version => l_api_version,
602 x_return_status => l_return_status,
603 x_msg_count => l_msg_count,
604 x_msg_data => l_msg_data,
605 p_rcv_event => l_rcv_event,
606 p_transaction_forward_flow_rec => p_transaction_forward_flow_rec,
607 p_transaction_reverse_flow_rec => p_transaction_reverse_flow_rec,
608 /* Support for Landed Cost Management */
609 p_lcm_flag => p_lcm_flag,
610 x_credit_acct_id => l_credit_acct_id,
611 x_debit_acct_id => l_debit_acct_id,
612 x_ic_cogs_acct_id => l_ic_cogs_acct_id,
613 /* Support for Landed Cost Management */
614 x_lcm_acct_id => l_lcm_acct_id);
615 IF l_return_status <> FND_API.g_ret_sts_success THEN
616 l_api_message := 'Error getting account information';
617 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
618 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
619 ,'Seed_RAEEvent : '||l_stmt_num||' : '||l_api_message);
620 END IF;
621 RAISE FND_API.g_exc_unexpected_error;
622 END IF;
623
624 l_rcv_event.credit_account_id := l_credit_acct_id;
625 l_rcv_event.debit_account_id := l_debit_acct_id;
626 l_rcv_event.intercompany_cogs_account_id := l_ic_cogs_acct_id;
627 /* Support for Landed Cost Management */
628 l_rcv_event.lcm_account_id := l_lcm_acct_id;
629
630
631 x_rcv_event := l_rcv_event;
632
633 --- Standard check of p_commit
634 IF FND_API.to_Boolean(p_commit) THEN
635 COMMIT WORK;
636 END IF;
637
638 -- Standard Call to get message count and if count = 1, get message info
639 FND_MSG_PUB.Count_And_Get (
640 p_count => x_msg_count,
641 p_data => x_msg_data );
642
643 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
644 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
645 ,'Seed_RAEEvent >>');
646 END IF;
647
648 EXCEPTION
649 WHEN FND_API.g_exc_error THEN
650 ROLLBACK TO Seed_RAEEvent_PVT;
651 x_return_status := FND_API.g_ret_sts_error;
652 FND_MSG_PUB.count_and_get
653 ( p_count => x_msg_count
654 , p_data => x_msg_data
655 );
656 WHEN FND_API.g_exc_unexpected_error THEN
657
658 ROLLBACK TO Seed_RAEEvent_PVT;
659 x_return_status := FND_API.g_ret_sts_unexp_error ;
660 FND_MSG_PUB.count_and_get
661 ( p_count => x_msg_count
662 , p_data => x_msg_data
663 );
664
665 WHEN OTHERS THEN
666 ROLLBACK TO Seed_RAEEvent_PVT;
667 x_return_status := fnd_api.g_ret_sts_unexp_error ;
668
669 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
670 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
671 ,'Seed_RAEEvent '||l_stmt_num||' : '||substr(SQLERRM,1,200));
672 END IF;
673
674 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
675 THEN
676 FND_MSG_PUB.add_exc_msg
677 ( G_PKG_NAME,
678 l_api_name || 'Statement -'||to_char(l_stmt_num)
679 );
680 END IF;
681 FND_MSG_PUB.count_and_get
682 ( p_count => x_msg_count
683 , p_data => x_msg_data
684 );
685
686 END Seed_RAEEvent;
687
688 -- Start of comments
689 -- API name : Get_TransactionAmount
690 -- Type : Private
691 -- Function : Returns the transaction amount. Used for service line types.
692 -- Pre-reqs :
693 -- Parameters :
694 -- IN : p_api_version IN NUMBER Required
695 -- p_init_msg_list IN VARCHAR2 Optional
696 -- Default = FND_API.G_FALSE
697 -- p_commit IN VARCHAR2 Optional
698 -- Default = FND_API.G_FALSE
699 -- p_validation_level IN NUMBER Optional
700 -- Default = FND_API.G_VALID_LEVEL_FULL
701 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
702 --
703 -- OUT : x_return_status OUT VARCHAR2(1)
704 -- x_msg_count OUT NUMBER
705 -- x_msg_data OUT VARCHAR2(2000)
706 -- x_transaction_amount OUT NUMBER
707 -- Version :
708 -- Initial version 1.0
709 --
710 --
711 -- Notes : This API returns the transaction amount. It should only be called for service line types.
712 --
713 -- End of comments
714 PROCEDURE Get_TransactionAmount(
715 p_api_version IN NUMBER,
716 p_init_msg_list IN VARCHAR2,
717 p_commit IN VARCHAR2,
718 p_validation_level IN NUMBER,
719 x_return_status OUT NOCOPY VARCHAR2,
720 x_msg_count OUT NOCOPY NUMBER,
721 x_msg_data OUT NOCOPY VARCHAR2,
722
723 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
724 x_transaction_amount OUT NOCOPY NUMBER
725 ) IS
726 l_api_name CONSTANT VARCHAR2(30) := 'Get_TransactionAmount';
727 l_api_version CONSTANT NUMBER := 1.0;
728
729 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
730 l_msg_count NUMBER := 0;
731 l_msg_data VARCHAR2(8000) := '';
732 l_stmt_num NUMBER := 0;
733 l_api_message VARCHAR2(1000);
734
735 l_transaction_amount NUMBER;
736 l_po_amount_ordered NUMBER;
737 l_po_amount_delivered NUMBER;
738 l_abs_rt_amount NUMBER;
739
740 l_rcv_txn_type RCV_Transactions.transaction_type%TYPE;
741 l_parent_txn_id NUMBER;
742 l_par_rcv_txn_type RCV_Transactions.transaction_type%TYPE;
743
744 BEGIN
745 -- Standard start of API savepoint
746 SAVEPOINT Get_TransactionAmount_PVT;
747
748 l_stmt_num := 0;
749
750 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
751 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
752 ,'Get_TransactionAmount <<');
753 END IF;
754
755 -- Standard call to check for call compatibility
756 IF NOT FND_API.Compatible_API_Call (
757 l_api_version,
758 p_api_version,
759 l_api_name,
760 G_PKG_NAME ) THEN
761 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
762 END IF;
763
764 -- Initialize message list if p_init_msg_list is set to TRUE
765 IF FND_API.to_Boolean(p_init_msg_list) THEN
766 FND_MSG_PUB.initialize;
767 END IF;
768
769 -- Initialize API return status to success
770 x_return_status := FND_API.G_RET_STS_SUCCESS;
771
772 -- For service line types, only the source types of RECEIVING and INVOICEMATCH
773 -- are valid. Retroactive price changes on service line types have no accounting
774 -- impact.
775 IF(p_rcv_event.event_source = 'RECEIVING') THEN
776
777 -- If receiving transaction has a distribution, entire amount is allocated to
778 -- the distribution. Otherwise, the amount has to be prorated based on amount ordered.
779 l_stmt_num := 10;
780 SELECT decode(RT.po_distribution_id, NULL,
781 RT.amount * (POD.amount_ordered/POLL.amount),
782 RT.amount)
783 INTO l_transaction_amount
784 FROM rcv_transactions RT,
785 po_distributions POD,
786 po_line_locations POLL
787 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
788 AND POD.po_distribution_id = p_rcv_event.po_distribution_id
789 AND POLL.line_location_id = p_rcv_event.po_line_location_id;
790
791 ELSIF(p_rcv_event.event_source = 'INVOICEMATCH') THEN
792
793 -- For source of invoice match, there will always be a po_distribution_id
794 l_stmt_num := 20;
795 SELECT APID.amount
796 INTO l_transaction_amount
797 FROM ap_invoice_distributions APID
798 WHERE APID.invoice_distribution_id = p_rcv_event.inv_distribution_id;
799
800 END IF;
801
802 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
803 l_api_message := 'Transaction Amount : '||l_transaction_amount;
804 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
805 ,l_api_message);
806 END IF;
807
808
809 -- For encumbrance reversal events , only reverse encumbrance
810 -- upto amount_ordered. If amount received exceeds the
811 -- ordered amount, transaction amount should be reduced such that
812 -- it does not exceed amount ordered.
813 IF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL) THEN
814 l_abs_rt_amount := ABS(l_transaction_amount);
815
816 l_stmt_num := 40;
817 SELECT RT.transaction_type, RT.parent_transaction_id
818 INTO l_rcv_txn_type, l_parent_txn_id
819 FROM rcv_transactions RT
820 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id;
821
822 l_stmt_num := 50;
823 SELECT PARENT.transaction_type
824 INTO l_par_rcv_txn_type
825 FROM rcv_transactions PARENT
826 WHERE PARENT.transaction_id =l_parent_txn_id;
827
828 l_stmt_num := 60;
829 SELECT POD.amount_ordered, POD.amount_delivered
830 INTO l_po_amount_ordered, l_po_amount_delivered
831 FROM po_distributions POD
832 WHERE po_distribution_id = p_rcv_event.po_distribution_id;
833
834 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
835 l_api_message := substr('l_rcv_txn_type : '||l_rcv_txn_type||
836 ' l_par_rcv_txn_type : '||l_par_rcv_txn_type||
837 ' l_po_amount_ordered : '||l_po_amount_ordered||
838 ' l_po_amount_delivered : '||l_po_amount_delivered, 1, 1000);
839 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
840 ,l_api_message);
841 END IF;
842
843
844 l_stmt_num := 70;
845
846 IF(l_rcv_txn_type = 'DELIVER' OR
847 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'DELIVER'
848 AND l_transaction_amount > 0) OR
849 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'RETURN TO RECEIVING'
850 AND l_transaction_amount < 0)) THEN
851 l_po_amount_delivered := l_po_amount_delivered - l_abs_rt_amount;
852 IF (l_po_amount_delivered >= l_po_amount_ordered) THEN
853 l_transaction_amount := 0;
854 ELSIF(l_abs_rt_amount + l_po_amount_delivered <= l_po_amount_ordered) THEN
855 l_transaction_amount := l_abs_rt_amount;
856 ELSE
857 l_transaction_amount := l_po_amount_ordered - l_po_amount_delivered;
858 END IF;
859 ELSIF(l_rcv_txn_type = 'RETURN TO VENDOR' OR
860 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'DELIVER'
861 AND p_rcv_event.transaction_amount < 0) OR
862 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'RETURN TO RECEIVING'
863 AND p_rcv_event.transaction_amount > 0)) THEN
864 l_po_amount_delivered := l_po_amount_delivered + l_abs_rt_amount;
865 IF (l_po_amount_delivered < l_po_amount_ordered) THEN
866 l_transaction_amount := l_abs_rt_amount;
867 ELSIF(l_po_amount_delivered - l_abs_rt_amount > l_po_amount_ordered) THEN
868 l_transaction_amount := 0;
869 ELSE
870 l_transaction_amount := l_abs_rt_amount - (l_po_amount_delivered - l_po_amount_ordered);
871 END IF;
872 END IF;
873 END IF;
874
875 x_transaction_amount := l_transaction_amount;
876
877 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
878 l_api_message := 'x_transaction_amount : '||x_transaction_amount;
879 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
880 ,l_api_message);
881 END IF;
882
883
884
885 --- Standard check of p_commit
886 IF FND_API.to_Boolean(p_commit) THEN
887 COMMIT WORK;
888 END IF;
889
890 -- Standard Call to get message count and if count = 1, get message info
891 FND_MSG_PUB.Count_And_Get (
892 p_count => x_msg_count,
893 p_data => x_msg_data );
894
895 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
896 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
897 ,'Get_TransactionAmount >>');
898 END IF;
899 EXCEPTION
900 WHEN FND_API.g_exc_error THEN
901 ROLLBACK TO Get_TransactionAmount_PVT;
902 x_return_status := FND_API.g_ret_sts_error;
903 FND_MSG_PUB.count_and_get
904 ( p_count => x_msg_count
905 , p_data => x_msg_data
906 );
907 WHEN FND_API.g_exc_unexpected_error THEN
908 ROLLBACK TO Get_TransactionAmount_PVT;
909 x_return_status := FND_API.g_ret_sts_unexp_error ;
910 FND_MSG_PUB.count_and_get
911 ( p_count => x_msg_count
912 , p_data => x_msg_data
913 );
914
915 WHEN OTHERS THEN
916 ROLLBACK TO Get_TransactionAmount_PVT;
917 x_return_status := fnd_api.g_ret_sts_unexp_error ;
918
919 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
920 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
921 ,'Get_TransactionAmount : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
922 END IF;
923
924 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
925 THEN
926 FND_MSG_PUB.add_exc_msg
927 ( G_PKG_NAME,
928 l_api_name || 'Statement -'||to_char(l_stmt_num)
929 );
930 END IF;
931 FND_MSG_PUB.count_and_get
932 ( p_count => x_msg_count
933 , p_data => x_msg_data
934 );
935
936 END Get_TransactionAmount;
937
938 -- Start of comments
939 -- API name : Get_Quantity
940 -- Type : Private
941 -- Function : Returns the quantity in source doc UOM. It includes additional
942 -- checks for encumbrance reversal events. We should only
943 -- encumber upto quantity ordered. If quantity received is
944 -- greater than quantity ordered, we should not encumber for
945 -- the excess.
946 -- Pre-reqs :
947 -- Parameters :
948 -- IN : p_api_version IN NUMBER Required
949 -- p_init_msg_list IN VARCHAR2 Optional
950 -- Default = FND_API.G_FALSE
951 -- p_commit IN VARCHAR2 Optional
952 -- Default = FND_API.G_FALSE
953 -- p_validation_level IN NUMBER Optional
954 -- Default = FND_API.G_VALID_LEVEL_FULL
955 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
956 --
957 -- OUT : x_return_status OUT VARCHAR2(1)
958 -- x_msg_count OUT NUMBER
959 -- x_msg_data OUT VARCHAR2(2000)
960 -- x_source_doc_quantity OUT NUMBER
961 -- Version :
962 -- Initial version 1.0
963 --
964 --
965 -- Notes : This API returns the transaction quantity. It should
966 -- only be called for non-service line types.
967 --
968 -- End of comments
969 PROCEDURE Get_Quantity(
970 p_api_version IN NUMBER,
971 p_init_msg_list IN VARCHAR2,
972 p_commit IN VARCHAR2,
973 p_validation_level IN NUMBER,
974 x_return_status OUT NOCOPY VARCHAR2,
975 x_msg_count OUT NOCOPY NUMBER,
976 x_msg_data OUT NOCOPY VARCHAR2,
977
978 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
979 x_source_doc_quantity OUT NOCOPY NUMBER
980 ) IS
981 l_api_name CONSTANT VARCHAR2(30) := 'Get_Quantity';
982 l_api_version CONSTANT NUMBER := 1.0;
983
984 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
985 l_msg_count NUMBER := 0;
986 l_msg_data VARCHAR2(8000) := '';
987 l_stmt_num NUMBER := 0;
988 l_api_message VARCHAR2(1000);
989
990 l_source_doc_quantity NUMBER;
991 l_po_quantity_ordered NUMBER;
992 l_po_quantity_delivered NUMBER;
993 l_abs_rt_quantity NUMBER;
994
995 l_rcv_txn_type RCV_Transactions.transaction_type%TYPE;
996 l_parent_txn_id NUMBER;
997 l_par_rcv_txn_type RCV_Transactions.transaction_type%TYPE;
998
999
1000 BEGIN
1001 -- Standard start of API savepoint
1002 SAVEPOINT Get_Quantity_PVT;
1003
1004 l_stmt_num := 0;
1005
1006 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1007 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
1008 ,'Get_Quantity <<');
1009 END IF;
1010
1011
1012 -- Standard call to check for call compatibility
1013 IF NOT FND_API.Compatible_API_Call (
1014 l_api_version,
1015 p_api_version,
1016 l_api_name,
1017 G_PKG_NAME ) THEN
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF;
1020
1021 -- Initialize message list if p_init_msg_list is set to TRUE
1022 IF FND_API.to_Boolean(p_init_msg_list) THEN
1023 FND_MSG_PUB.initialize;
1024 END IF;
1025
1026 -- Initialize API return status to success
1027 x_return_status := FND_API.G_RET_STS_SUCCESS;
1028
1029 l_stmt_num := 10;
1030
1031 IF (p_rcv_event.event_source = 'RECEIVING') THEN
1032 l_stmt_num := 20;
1033 SELECT decode(RT.po_distribution_id, NULL,
1034 RT.source_doc_quantity * POD.quantity_ordered/POLL.quantity,
1035 RT.source_doc_quantity)
1036 INTO l_source_doc_quantity
1037 FROM rcv_transactions RT,
1038 po_line_locations POLL,
1039 po_distributions POD
1040 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
1041 AND POLL.line_location_id = p_rcv_event.po_line_location_id
1042 AND POD.po_distribution_id = p_rcv_event.po_distribution_id;
1043
1044 ELSIF (p_rcv_event.event_source = 'RETROPRICE') THEN
1045 IF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ADJUST_RECEIVE) THEN
1046 l_stmt_num := 30;
1047 l_source_doc_quantity := RCV_ACCRUAL_SV.get_received_quantity(p_rcv_event.rcv_transaction_id, sysdate);
1048 ELSE
1049 l_stmt_num := 40;
1050 l_source_doc_quantity := RCV_ACCRUAL_SV.get_delivered_quantity(p_rcv_event.rcv_transaction_id, sysdate);
1051 END IF;
1052
1053 l_stmt_num := 50;
1054 SELECT decode(RT.po_distribution_id, NULL,
1055 l_source_doc_quantity * POD.quantity_ordered/POLL.quantity,
1056 l_source_doc_quantity)
1057 INTO l_source_doc_quantity
1058 FROM rcv_transactions RT,
1059 po_line_locations POLL,
1060 po_distributions POD
1061 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
1062 AND POLL.line_location_id = p_rcv_event.po_line_location_id
1063 AND POD.po_distribution_id = p_rcv_event.po_distribution_id;
1064 END IF;
1065
1066
1067 -- For encumbrance reversal events only match
1068 -- upto quantity_ordered. If quantity received/invoiced exceeds the
1069 -- ordered quantity, transaction quantity should be reduced such that
1070 -- it does not exceed quantity ordered.
1071 IF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL) THEN
1072 l_abs_rt_quantity := ABS(l_source_doc_quantity);
1073
1074 l_stmt_num := 60;
1075 SELECT RT.transaction_type, RT.parent_transaction_id
1076 INTO l_rcv_txn_type, l_parent_txn_id
1077 FROM rcv_transactions RT
1078 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id;
1079
1080 l_stmt_num := 70;
1081 SELECT PARENT.transaction_type
1082 INTO l_par_rcv_txn_type
1083 FROM rcv_transactions PARENT
1084 WHERE PARENT.transaction_id =l_parent_txn_id;
1085
1086 l_stmt_num := 80;
1087 SELECT POD.quantity_ordered, POD.quantity_delivered
1088 INTO l_po_quantity_ordered, l_po_quantity_delivered
1089 FROM po_distributions POD
1090 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
1091
1092 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1093 l_api_message := SUBSTR('l_rcv_txn_type : '||l_rcv_txn_type||
1094 ' l_parent_txn_id : '||l_parent_txn_id||
1095 ' l_par_rcv_txn_type : '||l_par_rcv_txn_type||
1096 ' l_abs_rt_quantity : '||l_abs_rt_quantity||
1097 ' l_po_quantity_ordered : '||l_po_quantity_ordered||
1098 ' l_po_quantity_delivered : '||l_po_quantity_delivered,1,1000);
1099 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1100 ,l_api_message);
1101 END IF;
1102
1103 /* Bug #3333610. Receiving updates quantity delivered prior to calling the events API.
1104 Consequently, we should subtract the current quantity from the quantity delivered to
1105 get the quantity that has been delivered previously. */
1106
1107 l_stmt_num := 90;
1108 IF(l_rcv_txn_type = 'DELIVER' OR
1109 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'DELIVER'
1110 AND l_source_doc_quantity > 0) OR
1111 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'RETURN TO RECEIVING'
1112 AND l_source_doc_quantity < 0)) THEN
1113
1114 l_po_quantity_delivered := l_po_quantity_delivered - l_abs_rt_quantity;
1115 IF (l_po_quantity_delivered >= l_po_quantity_ordered) THEN
1116 l_source_doc_quantity := 0;
1117 ELSIF(l_abs_rt_quantity + l_po_quantity_delivered <= l_po_quantity_ordered) THEN
1118 NULL; -- l_source_doc_quantity already holds the correct value
1119 ELSE
1120 --BUG#10209325
1121 if (l_source_doc_quantity > 0) then
1122 l_source_doc_quantity := l_po_quantity_ordered - l_po_quantity_delivered;
1123 else
1124 l_source_doc_quantity := -1*(l_po_quantity_ordered - l_po_quantity_delivered);
1125 end if;
1126 END IF;
1127 ELSIF(l_rcv_txn_type = 'RETURN TO RECEIVING' OR
1128 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'DELIVER'
1129 AND l_source_doc_quantity < 0) OR
1130 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'RETURN TO RECEIVING'
1131 AND l_source_doc_quantity > 0)) THEN
1132
1133 l_po_quantity_delivered := l_po_quantity_delivered + l_abs_rt_quantity;
1134 IF (l_po_quantity_delivered <= l_po_quantity_ordered) THEN
1135 NULL; -- l_source_doc_quantity already holds the correct value
1136 ELSIF(l_po_quantity_delivered - l_abs_rt_quantity > l_po_quantity_ordered) THEN
1137 l_source_doc_quantity := 0;
1138 ELSE
1139 --BUG#10209325
1140 if (l_source_doc_quantity > 0) then
1141 l_source_doc_quantity := l_abs_rt_quantity - (l_po_quantity_delivered - l_po_quantity_ordered);
1142 else
1143 l_source_doc_quantity :=-1*( l_abs_rt_quantity - (l_po_quantity_delivered - l_po_quantity_ordered));
1144 end if;
1145 END IF;
1146 END IF;
1147 END IF;
1148
1149 x_source_doc_quantity := l_source_doc_quantity;
1150
1151 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1152 l_api_message := 'x_source_doc_quantity : '||x_source_doc_quantity;
1153 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1154 ,l_api_message);
1155 END IF;
1156
1157
1158 --- Standard check of p_commit
1159 IF FND_API.to_Boolean(p_commit) THEN
1160 COMMIT WORK;
1161 END IF;
1162
1163 -- Standard Call to get message count and if count = 1, get message info
1164 FND_MSG_PUB.Count_And_Get (
1165 p_count => x_msg_count,
1166 p_data => x_msg_data );
1167
1168 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1169 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
1170 ,'Get_Quantity >>');
1171 END IF;
1172 EXCEPTION
1173 WHEN FND_API.g_exc_error THEN
1174 ROLLBACK TO Get_Quantity_PVT;
1175 x_return_status := FND_API.g_ret_sts_error;
1176 FND_MSG_PUB.count_and_get
1177 ( p_count => x_msg_count
1178 , p_data => x_msg_data
1179 );
1180 WHEN FND_API.g_exc_unexpected_error THEN
1181 ROLLBACK TO Get_Quantity_PVT;
1182 x_return_status := FND_API.g_ret_sts_unexp_error ;
1183 FND_MSG_PUB.count_and_get
1184 ( p_count => x_msg_count
1185 , p_data => x_msg_data
1186 );
1187
1188 WHEN OTHERS THEN
1189 ROLLBACK TO Get_Quantity_PVT;
1190 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1191
1192 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1193 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1194 ,'Get_Quantity : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1195 END IF;
1196
1197 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1198 THEN
1199 FND_MSG_PUB.add_exc_msg
1200 ( G_PKG_NAME,
1201 l_api_name || 'Statement -'||to_char(l_stmt_num)
1202 );
1203 END IF;
1204 FND_MSG_PUB.count_and_get
1205 ( p_count => x_msg_count
1206 , p_data => x_msg_data
1207 );
1208
1209 END Get_Quantity;
1210
1211
1212 -- Start of comments
1213 -- API name : Get_UnitPrice
1214 -- Type : Private
1215 -- Function : Returns the Unit Price. Used for non-service line types.
1216 -- Pre-reqs :
1217 -- Parameters :
1218 -- IN : p_api_version IN NUMBER Required
1219 -- p_init_msg_list IN VARCHAR2 Optional
1220 -- Default = FND_API.G_FALSE
1221 -- p_commit IN VARCHAR2 Optional
1222 -- Default = FND_API.G_FALSE
1223 -- p_validation_level IN NUMBER Optional
1224 -- Default = FND_API.G_VALID_LEVEL_FULL
1225 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
1226 -- p_lcm_flag IN VARCHAR2 Optional
1227 --
1228 -- OUT : x_return_status OUT VARCHAR2(1)
1229 -- x_msg_count OUT NUMBER
1230 -- x_msg_data OUT VARCHAR2(2000)
1231 -- x_intercompany_pricing_option OUT NUMBER
1232 -- x_unit_price OUT NUMBER
1233 -- x_currency_code OUT VARCHAR2(15)
1234 -- x_incr_transfer_price OUT NUMBER
1235 -- x_incr_currency_code OUT VARCHAR2(15)
1236 -- Version :
1237 -- Initial version 1.0
1238 --
1239 --
1240 -- Notes : This API returns the unit price. It should only be called for non service line types.
1241 --
1242 -- End of comments
1243 PROCEDURE Get_UnitPrice(
1244 p_api_version IN NUMBER,
1245 p_init_msg_list IN VARCHAR2,
1246 p_commit IN VARCHAR2,
1247 p_validation_level IN NUMBER,
1248 x_return_status OUT NOCOPY VARCHAR2,
1249 x_msg_count OUT NOCOPY NUMBER,
1250 x_msg_data OUT NOCOPY VARCHAR2,
1251 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
1252 p_asset_item_pricing_option IN NUMBER,
1253 p_expense_item_pricing_option IN NUMBER,
1254 /* Support for Landed Cost Management */
1255 p_lcm_flag IN VARCHAR2,
1256 x_intercompany_pricing_option OUT NOCOPY NUMBER,
1257 x_unit_price OUT NOCOPY NUMBER,
1258 /* Support for Landed Cost Management */
1259 x_unit_landed_cost OUT NOCOPY NUMBER,
1260 x_currency_code OUT NOCOPY VARCHAR2,
1261 x_incr_transfer_price OUT NOCOPY NUMBER,
1262 x_incr_currency_code OUT NOCOPY VARCHAR2
1263
1264 ) IS
1265 l_api_name CONSTANT VARCHAR2(30) := 'Get_UnitPrice';
1266 l_api_version CONSTANT NUMBER := 1.0;
1267
1268 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1269 l_msg_count NUMBER := 0;
1270 l_msg_data VARCHAR2(8000) := '';
1271 l_stmt_num NUMBER := 0;
1272 l_api_message VARCHAR2(1000);
1273
1274 l_asset_flag VARCHAR2(1);
1275 l_ic_pricing_option NUMBER := 1;
1276 l_transfer_price NUMBER;
1277 l_unit_price NUMBER;
1278 l_transaction_uom VARCHAR2(3);
1279 l_currency_code RCV_ACCOUNTING_EVENTS.CURRENCY_CODE%TYPE;
1280 l_item_exists NUMBER;
1281 l_from_organization_id NUMBER;
1282 l_from_org_id NUMBER;
1283 l_to_org_id NUMBER;
1284
1285 l_incr_currency_code RCV_ACCOUNTING_EVENTS.CURRENCY_CODE%TYPE;
1286 l_incr_transfer_price NUMBER;
1287
1288 BEGIN
1289 -- Standard start of API savepoint
1290 SAVEPOINT Get_UnitPrice_PVT;
1291
1292 l_stmt_num := 0;
1293
1294 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1295 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
1296 ,'Get_UnitPrice <<');
1297 END IF;
1298
1299 -- Standard call to check for call compatibility
1300 IF NOT FND_API.Compatible_API_Call (
1301 l_api_version,
1302 p_api_version,
1303 l_api_name,
1304 G_PKG_NAME ) THEN
1305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1306 END IF;
1307
1308 -- Initialize message list if p_init_msg_list is set to TRUE
1309 IF FND_API.to_Boolean(p_init_msg_list) THEN
1310 FND_MSG_PUB.initialize;
1311 END IF;
1312
1313 -- Initialize API return status to success
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315 x_incr_transfer_price := 0;
1316 x_incr_currency_code := NULL;
1317 /* Support for Landed Cost Management */
1318 x_unit_landed_cost := NULL;
1319 l_currency_code := p_rcv_event.currency_code;
1320
1321 l_stmt_num := 10;
1322 -- Always use PO price if :
1323 -- 1. No transaction flow exists or
1324 -- 2. Destination type is Shopfloor.
1325 -- 3. If it is the procurement org
1326 -- 4. The PO is for a one-time item.
1327
1328 IF(p_rcv_event.trx_flow_header_id IS NULL OR
1329 p_rcv_event.item_id IS NULL OR
1330 p_rcv_event.destination_type_code = 'SHOP FLOOR' OR
1331 (p_rcv_event.procurement_org_flag = 'Y' AND
1332 p_rcv_event.event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
1333 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL))
1334 ) THEN
1335 l_ic_pricing_option := 1;
1336
1337 ELSE
1338
1339 -- Pricing Option on the Transaction Flow form will determine whether to use
1340 -- PO price or Transfer price.
1341 BEGIN
1342 -- Verify that item exists in organization where event is being created.
1343 l_stmt_num := 30;
1344 SELECT count(*)
1345 INTO l_item_exists
1346 FROM mtl_system_items MSI
1347 WHERE MSI.inventory_item_id = p_rcv_event.item_id
1348 AND MSI.organization_id = p_rcv_event.organization_id;
1349
1350 IF(l_item_exists = 0) THEN
1351 FND_MESSAGE.set_name('PO','PO_INVALID_ITEM');
1352 FND_MSG_pub.add;
1353 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1354 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
1355 END IF;
1356 RAISE FND_API.g_exc_error;
1357 END IF;
1358
1359 -- Use Inventory Asset Flag in the organization where the physical event occurred. This
1360 -- would be the ship to organization id. Using POLL.ship_to_organization_id so it will be
1361 -- available for both Invoice Match and Receiving events.
1362 l_stmt_num := 40;
1363 SELECT MSI.inventory_asset_flag
1364 INTO l_asset_flag
1365 FROM mtl_system_items MSI,
1366 po_line_locations POLL
1367 WHERE MSI.inventory_item_id = p_rcv_event.item_id
1368 AND MSI.organization_id = POLL.ship_to_organization_id
1369 AND POLL.line_location_id = p_rcv_event.po_line_location_id;
1370 EXCEPTION
1371 WHEN NO_DATA_FOUND THEN
1372 FND_MESSAGE.set_name('PO','PO_INVALID_ITEM');
1373 FND_MSG_pub.add;
1374 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1375 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
1376 END IF;
1377 RAISE FND_API.g_exc_error;
1378 END;
1379
1380 IF(l_asset_flag = 'Y') THEN
1381 l_ic_pricing_option := p_asset_item_pricing_option;
1382 ELSE
1383 l_ic_pricing_option := p_expense_item_pricing_option;
1384 END IF;
1385
1386 END IF;
1387
1388 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1389 l_api_message := 'l_ic_pricing_option : '||l_ic_pricing_option;
1390 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1391 ,l_api_message);
1392 END IF;
1393
1394 -- l_ic_pricing_option of 1 => PO Price.
1395 -- l_ic_pricing_option of 2 => Transfer Price.
1396 IF(l_ic_pricing_option = 2) THEN
1397 l_stmt_num := 50;
1398 -- The l_ic_pricing_option can only be 2 for a source type of 'RECEIVING'.
1399 -- Get the UOM of the source_doc since unit price is desired in Document's UOM
1400 SELECT MUOM.uom_code
1401 INTO l_transaction_uom
1402 FROM rcv_transactions RT, mtl_units_of_measure MUOM
1403 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
1404 AND MUOM.unit_of_measure = RT.source_doc_unit_of_measure;
1405
1406 -- While calling the transfer pricing API, the from organization id should be
1407 -- passed. For Intercompany events, the from organization id is the same as
1408 -- organization_id on the event. For the remaining events, the from organization
1409 -- is the transfer_organization_id on the event.
1410
1411 IF(p_rcv_event.event_type_id IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
1412 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
1413 l_from_organization_id := p_rcv_event.organization_id;
1414 l_from_org_id := p_rcv_event.org_id;
1415 l_to_org_id := p_rcv_event.transfer_org_id;
1416 ELSE
1417 l_from_organization_id := p_rcv_event.transfer_organization_id;
1418 l_from_org_id := p_rcv_event.transfer_org_id;
1419 l_to_org_id := p_rcv_event.org_id;
1420 END IF;
1421
1422
1423 -- Alcoa enhancement. Users will be given the option to determine in which
1424 -- currency intercompany invoices should be created. The get_transfer_price
1425 -- API will return the transfer price in the selling OU currency as well in the
1426 -- currency chosen by the user. The returned values will have to be stored
1427 -- in MMT and will be used by Intercompany to determine the Currency in which
1428 -- to create the intercompany invoices.
1429 l_stmt_num := 60;
1430 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1431 l_api_message := 'Calling get_transfer_price API : '||
1432 ' l_from_org_id : '||l_from_org_id||
1433 ' l_to_org_id : '||l_to_org_id||
1434 ' l_transaction_uom : '||l_transaction_uom||
1435 ' item_id : '||p_rcv_event.item_id||
1436 ' p_transaction_id : '|| p_rcv_event.rcv_transaction_id;
1437
1438 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1439 ,l_api_message);
1440 END IF;
1441 INV_TRANSACTION_FLOW_PUB.get_transfer_price(
1442 p_api_version => 1.0,
1443 x_return_status => l_return_status,
1444 x_msg_data => l_msg_data,
1445 x_msg_count => l_msg_count,
1446 x_transfer_price => l_transfer_price,
1447 x_currency_code => l_currency_code,
1448 x_incr_transfer_price => l_incr_transfer_price,
1449 x_incr_currency_code => l_incr_currency_code,
1450 p_from_org_id => l_from_org_id,
1451 p_to_org_id => l_to_org_id,
1452 p_transaction_uom => l_transaction_uom,
1453 p_inventory_item_id => p_rcv_event.item_id,
1454 p_transaction_id => p_rcv_event.rcv_transaction_id,
1455 p_from_organization_id => l_from_organization_id,
1456 p_global_procurement_flag => 'Y',
1457 p_drop_ship_flag => 'N');
1458 IF l_return_status <> FND_API.g_ret_sts_success THEN
1459 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1460 l_api_message := 'Error getting transfer price';
1461 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1462 ,l_api_message);
1463 END IF;
1464 RAISE FND_API.g_exc_unexpected_error;
1465 END IF;
1466
1467 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1468 l_api_message := SUBSTR('l_transfer_price : ' || l_transfer_price||
1469 ' l_currency_code : '||l_currency_code||
1470 ' l_incr_transfer_price : '||l_incr_transfer_price||
1471 ' l_incr_currency_code : '||l_incr_currency_code,1000);
1472 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1473 ,'Get_TransferPrice : '||l_stmt_num||' : '||l_api_message);
1474 END IF;
1475
1476 l_unit_price := l_transfer_price;
1477 x_incr_transfer_price := l_incr_transfer_price;
1478 x_incr_currency_code := l_incr_currency_code;
1479
1480 ELSIF (p_rcv_event.event_source = 'RECEIVING' OR p_rcv_event.event_source = 'RETROPRICE') THEN
1481 l_stmt_num := 70;
1482 SELECT POLL.price_override
1483 INTO l_unit_price
1484 FROM po_line_locations POLL
1485 WHERE POLL.line_location_id = p_rcv_event.po_line_location_id;
1486
1487 /* Support for Landed Cost Management */
1488 IF (p_rcv_event.event_source = 'RECEIVING' AND p_lcm_flag = 'Y') THEN
1489 SELECT unit_landed_cost
1490 INTO x_unit_landed_cost
1491 FROM rcv_transactions
1492 WHERE transaction_id = p_rcv_event.rcv_transaction_id;
1493
1494 END IF;
1495
1496 ELSIF (p_rcv_event.event_source = 'INVOICEMATCH') THEN
1497 l_stmt_num := 80;
1498 SELECT APID.unit_price
1499 INTO l_unit_price
1500 FROM ap_invoice_distributions APID
1501 WHERE APID.invoice_distribution_id = p_rcv_event.inv_distribution_id;
1502 END IF;
1503
1504 x_intercompany_pricing_option := l_ic_pricing_option;
1505 x_unit_price := l_unit_price;
1506 x_currency_code := l_currency_code;
1507
1508 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1509 l_api_message := SUBSTR('x_ic_pricing_option : '||x_intercompany_pricing_option||
1510 ' x_unit_price : '||x_unit_price ||
1511 ' x_currency_code : '||x_currency_code||
1512 ' x_incr_currency_code : '||x_incr_currency_code||
1513 ' x_incr_transfer_price : '||x_incr_transfer_price,1,1000);
1514 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1515 ,l_api_message);
1516 END IF;
1517
1518
1519
1520 -- Standard check of p_commit
1521 IF FND_API.to_Boolean(p_commit) THEN
1522 COMMIT WORK;
1523 END IF;
1524
1525 -- Standard Call to get message count and if count = 1, get message info
1526 FND_MSG_PUB.Count_And_Get (
1527 p_count => x_msg_count,
1528 p_data => x_msg_data );
1529
1530 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1531 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
1532 ,'Get_UnitPrice >>');
1533 END IF;
1534 EXCEPTION
1535 WHEN FND_API.g_exc_error THEN
1536 ROLLBACK TO Get_UnitPrice_PVT;
1537 x_return_status := FND_API.g_ret_sts_error;
1538 FND_MSG_PUB.count_and_get
1539 ( p_count => x_msg_count
1540 , p_data => x_msg_data
1541 );
1542 WHEN FND_API.g_exc_unexpected_error THEN
1543 ROLLBACK TO Get_UnitPrice_PVT;
1544 x_return_status := FND_API.g_ret_sts_unexp_error ;
1545 FND_MSG_PUB.count_and_get
1546 ( p_count => x_msg_count
1547 , p_data => x_msg_data
1548 );
1549
1550 WHEN OTHERS THEN
1551 ROLLBACK TO Get_UnitPrice_PVT;
1552 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1553
1554 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1555 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1556 ,'Get_UnitPrice : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1557 END IF;
1558
1559 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1560 THEN
1561 FND_MSG_PUB.add_exc_msg
1562 ( G_PKG_NAME,
1563 l_api_name || 'Statement -'||to_char(l_stmt_num)
1564 );
1565 END IF;
1566 FND_MSG_PUB.count_and_get
1567 ( p_count => x_msg_count
1568 , p_data => x_msg_data
1569 );
1570 END Get_UnitPrice;
1571
1572 -- Start of comments
1573 -- API name : Get_UnitTax
1574 -- Type : Private
1575 -- Function : Returns the recoverable and non-recoverable tax.
1576 -- Pre-reqs :
1577 -- Parameters :
1578 -- IN : p_api_version IN NUMBER Required
1579 -- p_init_msg_list IN VARCHAR2 Optional
1580 -- Default = FND_API.G_FALSE
1581 -- p_commit IN VARCHAR2 Optional
1582 -- Default = FND_API.G_FALSE
1583 -- p_validation_level IN NUMBER Optional
1584 -- Default = FND_API.G_VALID_LEVEL_FULL
1585 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
1586 --
1587 -- OUT : x_return_status OUT VARCHAR2(1)
1588 -- x_msg_count OUT NUMBER
1589 -- x_msg_data OUT VARCHAR2(2000)
1590 -- x_unit_nr_tax OUT NUMBER
1591 -- x_unit_rec_tax OUT NUMBER
1592 -- x_prior_nr_tax OUT NUMBER
1593 -- x_prior_rec_tax OUT NUMBER
1594 -- Version :
1595 -- Initial version 1.0
1596 --
1597 --
1598 -- Notes : This API returns the tax information.
1599 --
1600 -- End of comments
1601 PROCEDURE Get_UnitTax(
1602 p_api_version IN NUMBER,
1603 p_init_msg_list IN VARCHAR2,
1604 p_commit IN VARCHAR2,
1605 p_validation_level IN NUMBER,
1606 x_return_status OUT NOCOPY VARCHAR2,
1607 x_msg_count OUT NOCOPY NUMBER,
1608 x_msg_data OUT NOCOPY VARCHAR2,
1609
1610 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
1611 x_unit_nr_tax OUT NOCOPY NUMBER,
1612 x_unit_rec_tax OUT NOCOPY NUMBER,
1613 x_prior_nr_tax OUT NOCOPY NUMBER,
1614 x_prior_rec_tax OUT NOCOPY NUMBER
1615
1616
1617 ) IS
1618 l_api_name CONSTANT VARCHAR2(30) := 'Get_UnitTax';
1619 l_api_version CONSTANT NUMBER := 1.0;
1620
1621 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1622 l_msg_count NUMBER := 0;
1623 l_msg_data VARCHAR2(8000) := '';
1624 l_stmt_num NUMBER := 0;
1625 l_api_message VARCHAR2(1000);
1626
1627 l_unit_nr_tax NUMBER := 0;
1628 l_unit_rec_tax NUMBER := 0;
1629 l_prior_nr_tax NUMBER := 0;
1630 l_prior_rec_tax NUMBER := 0;
1631
1632 l_recoverable_tax NUMBER := 0;
1633 l_non_recoverable_tax NUMBER := 0;
1634 l_old_recoverable_tax NUMBER := 0;
1635 l_old_non_recoverable_tax NUMBER := 0;
1636
1637 l_hook_used NUMBER;
1638 l_loc_non_recoverable_tax NUMBER;
1639 l_loc_recoverable_tax NUMBER;
1640 l_err_num NUMBER;
1641 l_err_code VARCHAR2(240);
1642 l_err_msg VARCHAR2(240);
1643
1644 BEGIN
1645 -- Standard start of API savepoint
1646 SAVEPOINT Get_UnitTax_PVT;
1647
1648 l_stmt_num := 0;
1649 l_hook_used:= 0;
1650
1651 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1652 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
1653 ,'Get_UnitTax <<');
1654 END IF;
1655
1656 -- Standard call to check for call compatibility
1657 IF NOT FND_API.Compatible_API_Call (
1658 l_api_version,
1659 p_api_version,
1660 l_api_name,
1661 G_PKG_NAME ) THEN
1662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1663 END IF;
1664
1665 -- Initialize message list if p_init_msg_list is set to TRUE
1666 IF FND_API.to_Boolean(p_init_msg_list) THEN
1667 FND_MSG_PUB.initialize;
1668 END IF;
1669
1670 -- Initialize API return status to success
1671 x_return_status := FND_API.G_RET_STS_SUCCESS;
1672 x_unit_nr_tax := 0;
1673 x_unit_rec_tax := 0;
1674 x_prior_nr_tax := 0;
1675 x_prior_rec_tax := 0;
1676
1677
1678 l_stmt_num := 10;
1679
1680 -- No tax is applicable if pricing option is transfer price.
1681 IF(p_rcv_event.intercompany_pricing_option = 2) THEN
1682 return;
1683 END IF;
1684
1685 IF (p_rcv_event.event_source = 'RECEIVING' OR p_rcv_event.event_source = 'RETROPRICE') THEN
1686 l_stmt_num := 20;
1687
1688 -- Call PO API to get current an prior receoverable and non-recoverable tax
1689 PO_TAX_SV.Get_All_PO_Tax(
1690 p_api_version => l_api_version,
1691 x_return_status => l_return_status,
1692 x_msg_data => l_msg_data,
1693 p_distribution_id => p_rcv_event.po_distribution_id,
1694 x_recoverable_tax => l_recoverable_tax,
1695 x_non_recoverable_tax => l_non_recoverable_tax,
1696 x_old_recoverable_tax => l_old_recoverable_tax,
1697 x_old_non_recoverable_tax => l_old_non_recoverable_tax);
1698
1699 IF l_return_status <> FND_API.g_ret_sts_success THEN
1700 l_api_message := 'Error getting Tax';
1701 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1702 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1703 ,'Get_UnitPrice : '||l_stmt_num||' : '||l_api_message);
1704 END IF;
1705 RAISE FND_API.g_exc_unexpected_error;
1706 END IF;
1707
1708 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1709 l_api_message := SUBSTR('l_recoverable_tax : '||l_recoverable_tax||
1710 ' l_non_recoverable_tax : '||l_non_recoverable_tax||
1711 ' l_old_recoverable_tax : '||l_old_recoverable_tax||
1712 ' l_old_non_recoverable_tax : '||l_old_non_recoverable_tax,1,1000);
1713 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1714 ,l_api_message);
1715 END IF;
1716
1717 /* Bug 6405593 :Added hook call to override the recoverable and Non-Recoverable
1718 taxes for ENCUMBRANCE_REVERSAL event */
1719
1720 IF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL) THEN
1721 l_stmt_num := 25;
1722 l_hook_used := CST_Common_hooks.Get_NRtax_amount(
1723 I_ACCT_TXN_ID =>p_rcv_event.rcv_transaction_id,
1724 I_SOURCE_DOC_TYPE =>'PO',
1725 I_SOURCE_DOC_ID =>p_rcv_event.po_distribution_id,
1726 I_ACCT_SOURCE =>'RCV',
1727 I_USER_ID =>fnd_global.user_id,
1728 I_LOGIN_ID =>fnd_global.login_id,
1729 I_REQ_ID =>fnd_global.conc_request_id,
1730 I_PRG_APPL_ID =>fnd_global.prog_appl_id,
1731 I_PRG_ID =>fnd_global.conc_program_id,
1732 O_DOC_NR_TAX =>l_loc_non_recoverable_tax,
1733 O_DOC_REC_TAX =>l_loc_recoverable_tax,
1734 O_Err_Num =>l_Err_Num,
1735 O_Err_Code =>l_Err_Code,
1736 O_Err_Msg =>l_Err_Msg
1737 );
1738 IF l_hook_used <>0 THEN
1739
1740 IF (l_err_num <> 0) THEN
1741 -- Error occured
1742 l_api_message := 'Error getting Enc Tax error_code : '||l_Err_Code||' Error Message : '||l_Err_Msg;
1743 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1744 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1745 ,'CST_Common_hooks.Get_NRtax_amount : '||l_stmt_num||' : '||l_api_message);
1746 END IF;
1747 RAISE FND_API.g_exc_unexpected_error;
1748 END IF;
1749
1750
1751 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1752 l_api_message := SUBSTR('Hook Used CST_Commonlocalization_hooks.Get_NRtax_amount :'|| l_hook_used ||
1753 ' l_loc_recoverable_tax : '||l_loc_recoverable_tax||
1754 ' l_loc_non_recoverable_tax : '||l_loc_non_recoverable_tax,1,1000);
1755 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1756 ,l_api_message);
1757 END IF;
1758
1759 l_non_recoverable_tax:=nvl(l_non_recoverable_tax,0)+nvl(l_loc_non_recoverable_tax,0);
1760 l_recoverable_tax :=nvl(l_recoverable_tax,0)+nvl(l_loc_recoverable_tax,0);
1761
1762 END IF;
1763 END IF;
1764 /* Bug 6405593 :Added hook call to override the recoverable and Non-Recoverable
1765 taxes for ENCUMBRANCE_REVERSAL event */
1766
1767 IF(p_rcv_event.service_flag = 'Y') THEN
1768 l_stmt_num := 30;
1769 SELECT l_non_recoverable_tax/POD.amount_ordered,
1770 l_recoverable_tax/POD.amount_ordered
1771 INTO l_unit_nr_tax,
1772 l_unit_rec_tax
1773 FROM po_distributions POD
1774 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
1775 ELSE
1776 l_stmt_num := 40;
1777 SELECT l_non_recoverable_tax/POD.quantity_ordered,
1778 l_recoverable_tax/POD.quantity_ordered
1779 INTO l_unit_nr_tax,
1780 l_unit_rec_tax
1781 FROM po_distributions POD
1782 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
1783 END IF;
1784 END IF;
1785
1786 IF (p_rcv_event.event_source = 'RETROPRICE') THEN
1787 l_stmt_num := 50;
1788 SELECT l_old_non_recoverable_tax/POD.quantity_ordered,
1789 l_old_recoverable_tax/POD.quantity_ordered
1790 INTO l_prior_nr_tax,
1791 l_prior_rec_tax
1792 FROM po_distributions POD
1793 WHERE po_distribution_id = p_rcv_event.po_distribution_id;
1794 END IF;
1795
1796 x_unit_nr_tax := NVL(l_unit_nr_tax,0);
1797 x_unit_rec_tax := NVL(l_unit_rec_tax,0);
1798 x_prior_nr_tax := NVL(l_prior_nr_tax,0);
1799 x_prior_rec_tax := NVL(l_prior_rec_tax,0);
1800
1801 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1802 l_api_message := SUBSTR('x_unit_nr_tax : '||x_unit_nr_tax||
1803 ' x_unit_rec_tax : '||x_unit_rec_tax||
1804 ' x_prior_nr_tax : '||x_prior_nr_tax||
1805 ' x_prior_rec_tax : '||x_prior_rec_tax,1,1000);
1806 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1807 ,l_api_message);
1808 END IF;
1809
1810
1811 --- Standard check of p_commit
1812 IF FND_API.to_Boolean(p_commit) THEN
1813 COMMIT WORK;
1814 END IF;
1815
1816 -- Standard Call to get message count and if count = 1, get message info
1817 FND_MSG_PUB.Count_And_Get (
1818 p_count => x_msg_count,
1819 p_data => x_msg_data );
1820
1821 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1822 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
1823 ,'Get_UnitTax >>');
1824 END IF;
1825
1826
1827 EXCEPTION
1828 WHEN FND_API.g_exc_error THEN
1829 ROLLBACK TO Get_UnitTax_PVT;
1830 x_return_status := FND_API.g_ret_sts_error;
1831 FND_MSG_PUB.count_and_get
1832 ( p_count => x_msg_count
1833 , p_data => x_msg_data
1834 );
1835 WHEN FND_API.g_exc_unexpected_error THEN
1836 ROLLBACK TO Get_UnitTax_PVT;
1837 x_return_status := FND_API.g_ret_sts_unexp_error ;
1838 FND_MSG_PUB.count_and_get
1839 ( p_count => x_msg_count
1840 , p_data => x_msg_data
1841 );
1842
1843 WHEN OTHERS THEN
1844 ROLLBACK TO Get_UnitTax_PVT;
1845 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1846
1847 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1848 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1849 ,'Get_UnitTax : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1850 END IF;
1851
1852 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1853 THEN
1854 FND_MSG_PUB.add_exc_msg
1855 ( G_PKG_NAME,
1856 l_api_name || 'Statement -'||to_char(l_stmt_num)
1857 );
1858 END IF;
1859 FND_MSG_PUB.count_and_get
1860 ( p_count => x_msg_count
1861 , p_data => x_msg_data
1862 );
1863 END Get_UnitTax;
1864
1865 -----------------------------------------------------------------------------
1866 -- Start of comments --
1867 -- --
1868 -- PROCEDURE --
1869 -- Convert_UOM This function updates the record type variable --
1870 -- that is passed to it. It inserts the UOM into the --
1871 -- primary_uom field, then it updates the primary_ --
1872 -- quantity with the transaction_quantity converted to --
1873 -- the new UOM and it updates the unit_price by --
1874 -- converting it with the new UOM. --
1875 -- --
1876 -- Because there are already other modules under PO_TOP --
1877 -- that use the inv_convert package, we can safely use --
1878 -- it here without introducing new dependencies on that --
1879 -- product. --
1880 -- --
1881 -- VERSION 1.0 --
1882 -- --
1883 -- PARAMETERS --
1884 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
1885 -- P_INIT_MSG_LIST Initialize message list? True/False --
1886 -- P_COMMIT Should the API commit before returning? True/False --
1887 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
1888 -- X_MSG_COUNT Message Count - # of messages placed in message list--
1889 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
1890 -- P_EVENT_REC Record storing an RCV Accounting Event (RAE) --
1891 -- X_TRANSACTION_QTY Transaction quantity converted from source doc qty --
1892 -- X_PRIMARY_UOM Converted UOM --
1893 -- X_PRIMARY_QTY Primary quantity converted from source doc qty --
1894 -- X_TRX_UOM_CODE Transaction UOM --
1895 -- --
1896 -- HISTORY: --
1897 -- 06/26/03 Bryan Kuntz Created --
1898 -- End of comments --
1899 -----------------------------------------------------------------------------
1900
1901 PROCEDURE Convert_UOM (
1902 P_API_VERSION IN NUMBER,
1903 P_INIT_MSG_LIST IN VARCHAR2,
1904 P_COMMIT IN VARCHAR2,
1905 P_VALIDATION_LEVEL IN NUMBER, -- := FND_API.G_VALID_LEVEL_FULL,
1906 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1907 X_MSG_COUNT OUT NOCOPY NUMBER,
1908 X_MSG_DATA OUT NOCOPY VARCHAR2,
1909 P_EVENT_REC IN RCV_SeedEvents_PVT.rcv_event_rec_type,
1910 X_TRANSACTION_QTY OUT NOCOPY NUMBER,
1911 X_PRIMARY_UOM OUT NOCOPY MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE,
1912 X_PRIMARY_QTY OUT NOCOPY NUMBER,
1913 X_TRX_UOM_CODE OUT NOCOPY VARCHAR2
1914 ) IS
1915
1916 -- local control variables
1917 l_api_name CONSTANT VARCHAR2(30) := 'Convert_UOM';
1918 l_api_version CONSTANT NUMBER := 1.0;
1919 l_stmt_num number := 0;
1920 l_api_message VARCHAR2(1000);
1921
1922 -- local data variables
1923 l_item_id NUMBER;
1924 l_primary_uom_rate NUMBER;
1925 l_trx_uom_rate NUMBER;
1926 l_primary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1927 l_source_doc_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1928 l_trx_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1929 l_primary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
1930
1931 BEGIN
1932
1933 SAVEPOINT Convert_UOM_PVT;
1934 -- Initialize message list if p_init_msg_list is set to TRUE
1935 if FND_API.to_Boolean(P_INIT_MSG_LIST) then
1936 FND_MSG_PUB.initialize;
1937 end if;
1938
1939 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1940 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.begin'
1941 ,'Convert_UOM <<');
1942 END IF;
1943
1944 -- Standard check for compatibility
1945 IF NOT FND_API.Compatible_API_Call (
1946 l_api_version,
1947 P_API_VERSION,
1948 l_api_name,
1949 G_PKG_NAME ) -- line 90
1950 THEN
1951 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1952 END IF;
1953
1954 -- Initialize API return status to success
1955 x_return_status := FND_API.G_RET_STS_SUCCESS;
1956 x_msg_count := 0;
1957 x_msg_data := '';
1958
1959 -- API body
1960 l_stmt_num := 10;
1961 l_item_id := p_event_rec.item_id;
1962
1963 -- Get UOM code for the source document's UOM
1964 SELECT uom_code
1965 INTO l_source_doc_uom_code
1966 FROM mtl_units_of_measure
1967 WHERE unit_of_measure = p_event_rec.source_doc_uom;
1968
1969 -- Get UOM code for the transaction UOM
1970 SELECT uom_code
1971 INTO l_trx_uom_code
1972 FROM mtl_units_of_measure
1973 WHERE unit_of_measure = p_event_rec.transaction_uom;
1974
1975
1976 -- Get UOM for this item/org from MSI and populate primary_uom with it
1977 IF (l_item_id IS NULL) THEN
1978
1979 -- for a one-time item, the primary uom is the
1980 -- base uom for the item's current uom class
1981 l_stmt_num := 20;
1982 SELECT PUOM.uom_code, PUOM.unit_of_measure
1983 INTO l_primary_uom_code, l_primary_uom
1984 FROM mtl_units_of_measure TUOM,
1985 mtl_units_of_measure PUOM
1986 WHERE TUOM.unit_of_measure = p_event_rec.source_doc_uom
1987 AND TUOM.uom_class = PUOM.uom_class
1988 AND PUOM.base_uom_flag = 'Y';
1989
1990 l_item_id := 0;
1991 ELSE
1992 l_stmt_num := 30;
1993 SELECT primary_uom_code
1994 INTO l_primary_uom_code
1995 FROM mtl_system_items
1996 WHERE organization_id = p_event_rec.organization_id
1997 AND inventory_item_id = l_item_id;
1998
1999 l_stmt_num := 40;
2000 SELECT unit_of_measure
2001 INTO l_primary_uom
2002 FROM mtl_units_of_measure
2003 WHERE uom_code = l_primary_uom_code;
2004 END IF;
2005
2006 -- Get the UOM rate from source_doc_uom to primary_uom
2007 l_stmt_num := 50;
2008 INV_Convert.INV_UM_Conversion(
2009 from_unit => l_source_doc_uom_code,
2010 to_unit => l_primary_uom_code,
2011 item_id => l_item_id,
2012 uom_rate => l_primary_uom_rate);
2013
2014 IF (l_primary_uom_rate = -99999) THEN
2015 RAISE FND_API.G_EXC_ERROR;
2016 l_api_message := 'inv_convert.inv_um_conversion() failed to get the UOM rate';
2017 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2018 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2019 l_api_message);
2020 END IF;
2021 END IF;
2022
2023 -- Get the UOM rate from source_doc_uom to transaction_uom
2024 l_stmt_num := 60;
2025 INV_Convert.INV_UM_Conversion(
2026 from_unit => l_source_doc_uom_code,
2027 to_unit => l_trx_uom_code,
2028 item_id => l_item_id,
2029 uom_rate => l_trx_uom_rate);
2030
2031 IF (l_trx_uom_rate = -99999) THEN
2032 RAISE FND_API.G_EXC_ERROR;
2033 l_api_message := 'inv_convert.inv_um_conversion() failed to get the UOM rate';
2034 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2035 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2036 l_api_message);
2037 END IF;
2038 END IF;
2039
2040
2041 -- Populate output variables
2042 x_primary_uom := l_primary_uom;
2043 x_primary_qty := l_primary_uom_rate * p_event_rec.source_doc_quantity; /*BUG 6838756 Removed rounding*/
2044 x_transaction_qty := l_trx_uom_rate * p_event_rec.source_doc_quantity; /*BUG 6838756 Removed rounding*/
2045 x_trx_uom_code := l_trx_uom_code;
2046
2047 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2048 l_api_message := 'x_primary_uom : '||x_primary_uom||
2049 ' x_primary_qty : '||x_primary_qty||
2050 ' x_transaction_qty : '||x_transaction_qty||
2051 ' x_trx_uom_code : '||x_trx_uom_code;
2052 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2053 ,l_api_message);
2054 END IF;
2055
2056 -- End of API body
2057
2058 -- Standard check of P_COMMIT
2059 IF FND_API.to_Boolean(P_COMMIT) THEN
2060 COMMIT WORK;
2061 END IF;
2062
2063 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2064 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.end'
2065 ,'Convert_UOM >>');
2066 END IF;
2067
2068 EXCEPTION
2069 WHEN FND_API.g_exc_error THEN
2070 ROLLBACK TO Convert_UOM_PVT;
2071 x_return_status := FND_API.g_ret_sts_error;
2072 FND_MSG_PUB.count_and_get
2073 ( p_count => x_msg_count
2074 , p_data => x_msg_data
2075 );
2076
2077 WHEN FND_API.g_exc_unexpected_error THEN
2078 ROLLBACK TO Convert_UOM_PVT;
2079 x_return_status := FND_API.g_ret_sts_unexp_error;
2080 FND_MSG_PUB.count_and_get
2081 ( p_count => x_msg_count
2082 , p_data => x_msg_data
2083 );
2084
2085 WHEN NO_DATA_FOUND then
2086 ROLLBACK TO Convert_UOM_PVT;
2087 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
2088 l_api_message := ': Statement # '||to_char(l_stmt_num)||' - No UOM found.';
2089 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2090 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2091 l_api_message);
2092 END IF;
2093 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2094 THEN
2095 FND_MSG_PUB.add_exc_msg
2096 ( G_PKG_NAME,
2097 l_api_name || l_api_message
2098 );
2099 END IF;
2100 FND_MSG_PUB.count_and_get
2101 ( p_count => x_msg_count
2102 , p_data => x_msg_data
2103 );
2104
2105 WHEN OTHERS then
2106 ROLLBACK TO Convert_UOM_PVT;
2107 x_return_status := FND_API.g_ret_sts_unexp_error ;
2108 l_api_message := 'Unexpected Error at statement('||to_char(l_stmt_num)||'): '||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,100);
2109 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2110 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2111 l_api_message);
2112 END IF;
2113 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2114 FND_MSG_PUB.add_exc_msg
2115 ( G_PKG_NAME,
2116 l_api_name || l_api_message );
2117 END IF;
2118 FND_MSG_PUB.count_and_get
2119 ( p_count => x_msg_count
2120 , p_data => x_msg_data
2121 );
2122
2123 END Convert_UOM;
2124
2125 -----------------------------------------------------------------------------
2126 -- Start of comments --
2127 -- --
2128 -- PROCEDURE --
2129 -- Get_Currency This procedure returns the currency_conversion --
2130 -- parameters, conversion rate, date and type --
2131 --
2132 -- It is being coded for the purpose of providing the --
2133 -- currency conversion parameters for Global Procurement --
2134 -- and true drop shipment scenario, but may be used as a --
2135 -- generic API to return currency conversion rates for --
2136 -- Receiving transactions. --
2137 -- --
2138 -- Logic: --
2139 -- If supplier facing org, if match to po use POD.rate --
2140 -- else --
2141 -- rcv_transactions.curr_conv_rate--
2142 -- Else --
2143 -- Get the conversion type --
2144 -- Determine currency conversion rate --
2145 -- --
2146 -- --
2147 -- --
2148
2149 -- --
2150 -- VERSION 1.0 --
2151 -- --
2152 -- PARAMETERS --
2153 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
2154 -- P_INIT_MSG_LIST Initialize message list? True/False --
2155 -- P_COMMIT Should the API commit before returning? True/False --
2156 -- P_RCV_EVENT Record storing an RCV Accounting Event (RAE) --
2157 -- X_CURRENCY_CODE --
2158 -- X_CURRENCY_CONVERSION_RATE --
2159 -- X_CURRENCY_CONVERSION_TYPE --
2160 -- X_CURRENCY_CONVERSION_TYPE --
2161 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
2162 -- X_MSG_COUNT Message Count - # of messages placed in message list--
2163 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
2164 -- --
2165 -- HISTORY: --
2166 -- 08/02/03 Anju Gupta Created --
2167 -- End of comments --
2168 -----------------------------------------------------------------------------
2169 PROCEDURE Get_Currency(
2170 P_API_VERSION IN NUMBER,
2171 P_INIT_MSG_LIST IN VARCHAR2,
2172 P_COMMIT IN VARCHAR2,
2173 P_VALIDATION_LEVEL IN NUMBER,
2174 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2175 X_MSG_COUNT OUT NOCOPY NUMBER,
2176 X_MSG_DATA OUT NOCOPY VARCHAR2,
2177
2178 P_RCV_EVENT IN RCV_SeedEvents_PVT.rcv_event_rec_type,
2179 X_CURRENCY_CODE OUT NOCOPY VARCHAR2,
2180 X_CURRENCY_CONVERSION_RATE OUT NOCOPY NUMBER,
2181 X_CURRENCY_CONVERSION_DATE OUT NOCOPY DATE,
2182 X_CURRENCY_CONVERSION_TYPE OUT NOCOPY VARCHAR2
2183 ) IS
2184
2185 -- local control variables
2186 l_api_name CONSTANT VARCHAR2(30) := 'GET_Currency';
2187 l_api_version CONSTANT NUMBER := 1.0;
2188 l_stmt_num number := 0;
2189 l_api_message VARCHAR2(1000);
2190
2191 -- local data variables
2192 l_match_option VARCHAR2(1);
2193 l_currency_code RCV_TRANSACTIONS.currency_code%TYPE;
2194 l_currency_conversion_rate NUMBER;
2195 l_currency_conversion_date DATE;
2196 l_currency_conversion_type RCV_TRANSACTIONS.currency_conversion_type%TYPE := '';
2197 l_sob_id NUMBER;
2198 l_po_line_location_id NUMBER;
2199 l_rcv_transaction_id NUMBER;
2200
2201
2202 BEGIN
2203
2204 SAVEPOINT Get_Currency_PVT;
2205
2206 -- Standard call to check for call compatibility
2207 IF NOT FND_API.Compatible_API_Call (
2208 l_api_version,
2209 p_api_version,
2210 l_api_name,
2211 G_PKG_NAME ) THEN
2212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2213 END IF;
2214
2215 -- Initialize message list if p_init_msg_list is set to TRUE
2216 IF FND_API.to_Boolean(P_INIT_MSG_LIST) then
2217 FND_MSG_PUB.initialize;
2218 END IF;
2219
2220
2221 -- Initialize API return status to success
2222 x_return_status := FND_API.G_RET_STS_SUCCESS;
2223
2224 -- API body
2225 l_stmt_num := 10;
2226
2227 IF ((p_rcv_event.procurement_org_flag = 'Y') AND
2228 (p_rcv_event.event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
2229 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL))) THEN
2230
2231 l_currency_code := p_rcv_event.currency_code;
2232
2233 l_stmt_num := 20;
2234 SELECT line_location_id
2235 INTO l_po_line_location_id
2236 FROM po_distributions
2237 WHERE po_distribution_id = p_rcv_event.po_distribution_id;
2238
2239 l_stmt_num := 30;
2240 SELECT match_option
2241 INTO l_match_option
2242 FROM po_line_locations
2243 WHERE line_location_id = l_po_line_location_id;
2244
2245 -- Always use rate on the PO distribution for encumbrance reversals.
2246 IF (l_match_option = 'P' OR (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL)) THEN
2247
2248 l_stmt_num := 40;
2249 SELECT nvl(POD.rate,1),
2250 POH.rate_type,
2251 nvl(POD.rate_date,POD.creation_date) /*Changes for bug 8623413 to take creation date in case rate_date is null */
2252 INTO l_currency_conversion_rate,
2253 l_currency_conversion_type,
2254 l_currency_conversion_date
2255 FROM po_distributions POD,
2256 po_headers POH
2257 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id
2258 AND POH.po_header_id = POD.po_header_id;
2259
2260 ELSE
2261 -- This is also correct for ADJUST events where we only create one event
2262 -- for every parent transaction. In the case of a Match to receipt PO, the
2263 -- currency conversion rate of the child transactions (DELIVER CORRECT, RTR,
2264 -- RTV) will be the same as the currency conversion rate on the parent
2265 -- RECEIVE/MATCH transaction. This will be the case even if the daily rate
2266 -- has changed between the time that the parent transaction was done and the
2267 -- time that the child transactions were done.
2268
2269 l_stmt_num := 50;
2270 SELECT RT.currency_conversion_rate,
2271 RT.currency_conversion_type,
2272 RT.currency_conversion_date
2273 INTO l_currency_conversion_rate,
2274 l_currency_conversion_type,
2275 l_currency_conversion_date
2276 FROM rcv_transactions RT
2277 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id;
2278
2279 END IF;
2280 ELSE
2281
2282 l_currency_code := p_rcv_event.currency_code;
2283 l_sob_id := p_rcv_event.set_of_books_id;
2284
2285 -- Use profile INV: Intercompany Currency conversion Type, to determine Conversion Type
2286 -- Ensure that INV uses the same type for conversion for GP/ DS scenarios
2287
2288 l_stmt_num := 70;
2289 FND_PROFILE.get('IC_CURRENCY_CONVERSION_TYPE', l_currency_conversion_type);
2290
2291 l_stmt_num := 80;
2292 l_currency_conversion_rate := GL_Currency_API.get_rate(
2293 x_set_of_books_id => l_sob_id,
2294 x_from_currency => l_currency_code,
2295 x_conversion_date => p_rcv_event.transaction_date,
2296 x_conversion_type => l_currency_conversion_type);
2297 END IF;
2298
2299 x_currency_code := l_currency_code;
2300 x_currency_conversion_rate := l_currency_conversion_rate;
2301 x_currency_conversion_date := NVL(l_currency_conversion_date,sysdate);
2302 x_currency_conversion_type := l_currency_conversion_type;
2303
2304 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2305 l_api_message := SUBSTR('x_currency_code : '||x_currency_code||
2306 ' x_currency_conversion_rate : '||TO_CHAR(x_currency_conversion_rate)||
2307 ' x_currency_conversion_date : '||TO_CHAR(x_currency_conversion_date,'DD-MON-YY')||
2308 ' x_currency_conversion_type : '||x_currency_conversion_type,1,1000);
2309 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2310 ,l_api_message);
2311 END IF;
2312
2313
2314 -- End of API body
2315
2316 FND_MSG_PUB.Count_And_Get (
2317 p_encoded => FND_API.G_FALSE,
2318 p_count => X_MSG_COUNT,
2319 p_data => X_MSG_DATA );
2320
2321
2322 -- Standard check of P_COMMIT
2323 IF FND_API.to_Boolean(P_COMMIT) THEN
2324 COMMIT WORK;
2325 END IF;
2326
2327
2328 EXCEPTION
2329 WHEN FND_API.g_exc_error THEN
2330 ROLLBACK TO Get_Currency_PVT;
2331 x_return_status := FND_API.g_ret_sts_error;
2332 FND_MSG_PUB.count_and_get
2333 ( p_count => x_msg_count
2334 , p_data => x_msg_data
2335 );
2336
2337 WHEN FND_API.g_exc_unexpected_error THEN
2338 ROLLBACK TO Get_Currency_PVT;
2339 x_return_status := FND_API.g_ret_sts_unexp_error ;
2340 FND_MSG_PUB.count_and_get
2341 ( p_count => x_msg_count
2342 , p_data => x_msg_data
2343 );
2344
2345 WHEN NO_DATA_FOUND then
2346 ROLLBACK TO GET_CURRENCY_PVT;
2347 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
2348 l_api_message := 'Unexpected Error: '||l_stmt_num||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,200);
2349 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2350 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2351 l_api_message);
2352 END IF;
2353
2354 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2355 THEN
2356 FND_MSG_PUB.add_exc_msg
2357 ( G_PKG_NAME,
2358 l_api_name || 'Statement -'||to_char(l_stmt_num)
2359 );
2360 END IF;
2361
2362 FND_MSG_PUB.Count_And_Get (
2363 p_count => X_MSG_COUNT,
2364 p_data => X_MSG_DATA );
2365
2366 WHEN OTHERS then
2367 ROLLBACK TO GET_CURRENCY_PVT;
2368 x_return_status := FND_API.g_ret_sts_unexp_error ;
2369 l_api_message := 'Unexpected Error: '||l_stmt_num||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,200);
2370 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2371 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2372 l_api_message);
2373 END IF;
2374
2375 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2376 THEN
2377 FND_MSG_PUB.add_exc_msg
2378 ( G_PKG_NAME,
2379 l_api_name || 'Statement -'||to_char(l_stmt_num)
2380 );
2381 END IF;
2382
2383 FND_MSG_PUB.Count_And_Get (
2384 p_count => X_MSG_COUNT,
2385 p_data => X_MSG_DATA );
2386
2387 END Get_Currency;
2388
2389
2390
2391 -- Start of comments
2392 -- API name : Get_Accounts
2393 -- Type : Private
2394 -- Function : To get the credit and debit accounts for each event.
2395 -- Pre-reqs :
2396 -- Parameters :
2397 -- IN : p_api_version IN NUMBER Required
2398 -- p_init_msg_list IN VARCHAR2 Optional
2399 -- Default = FND_API.G_FALSE
2400 -- p_commit IN VARCHAR2 Optional
2401 -- Default = FND_API.G_FALSE
2402 -- p_validation_level IN NUMBER Optional
2403 -- Default = FND_API.G_VALID_LEVEL_FULL
2404 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
2405 -- p_transaction_forward_flow_rec mtl_transaction_flow_rec_type,
2406 -- p_transaction_reverse_flow_rec mtl_transaction_flow_rec_type,
2407 --
2408 -- OUT : x_return_status OUT VARCHAR2(1)
2409 -- x_msg_count OUT NUMBER
2410 -- x_msg_data OUT VARCHAR2(2000)
2411 -- x_credit_acct_id OUT NUMBER
2412 -- x_debit_acct_id OUT NUMBER
2413 -- x_ic_cogs_acct_id OUT NUMBER
2414 -- Version :
2415 -- Initial version 1.0
2416 --
2417 --
2418 -- Notes : This API creates all accounting events for RETURN TO VENDOR transactions
2419 -- in RCV_ACCOUNTING_EVENTS.
2420 --
2421 -- End of comments
2422 PROCEDURE Get_Accounts(
2423 p_api_version IN NUMBER,
2424 p_init_msg_list IN VARCHAR2,
2425 p_commit IN VARCHAR2,
2426 p_validation_level IN NUMBER,
2427 x_return_status OUT NOCOPY VARCHAR2,
2428 x_msg_count OUT NOCOPY NUMBER,
2429 x_msg_data OUT NOCOPY VARCHAR2,
2430
2431 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
2432 p_transaction_forward_flow_rec INV_TRANSACTION_FLOW_PUB.mtl_transaction_flow_rec_type,
2433 p_transaction_reverse_flow_rec INV_TRANSACTION_FLOW_PUB.mtl_transaction_flow_rec_type,
2434 /* Support for Landed Cost Management */
2435 p_lcm_flag IN VARCHAR2,
2436 x_credit_acct_id OUT NOCOPY NUMBER,
2437 x_debit_acct_id OUT NOCOPY NUMBER,
2438 x_ic_cogs_acct_id OUT NOCOPY NUMBER,
2439 /* Support for Landed Cost Management */
2440 x_lcm_acct_id OUT NOCOPY NUMBER
2441 ) IS
2442 l_api_name CONSTANT VARCHAR2(30) := 'Get_Accounts';
2443 l_api_version CONSTANT NUMBER := 1.0;
2444
2445 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2446 l_msg_count NUMBER := 0;
2447 l_msg_data VARCHAR2(8000) := '';
2448 l_stmt_num NUMBER := 0;
2449 l_api_message VARCHAR2(1000);
2450
2451 l_credit_acct_id NUMBER;
2452 l_debit_acct_id NUMBER;
2453 l_dist_acct_id NUMBER;
2454 l_ic_cogs_acct_id NUMBER;
2455 l_ic_coss_acct_id NUMBER;
2456
2457 l_pod_accrual_acct_id NUMBER;
2458 l_pod_ccid NUMBER;
2459 l_dest_pod_ccid NUMBER;
2460 l_pod_budget_acct_id NUMBER;
2461
2462 l_receiving_insp_acct_id NUMBER;
2463 l_clearing_acct_id NUMBER;
2464 l_retroprice_adj_acct_id NUMBER;
2465 l_overlaid_acct NUMBER;
2466 /* Support for Landed Cost Management */
2467 l_lcm_acct_id NUMBER;
2468
2469 l_trx_type rcv_transactions.transaction_type%TYPE;
2470 l_parent_trx_type rcv_transactions.transaction_type%TYPE;
2471 l_parent_trx_id NUMBER;
2472 l_account_flag NUMBER := 0;
2473 BEGIN
2474 -- Standard start of API savepoint
2475 SAVEPOINT Get_Accounts_PVT;
2476
2477 l_stmt_num := 0;
2478
2479 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2480 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
2481 ,'Get_Accounts <<');
2482
2483 END IF;
2484
2485 -- Standard call to check for call compatibility
2486 IF NOT FND_API.Compatible_API_Call (
2487 l_api_version,
2488 p_api_version,
2489 l_api_name,
2490 G_PKG_NAME ) THEN
2491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2492 END IF;
2493
2494 -- Initialize message list if p_init_msg_list is set to TRUE
2495 IF FND_API.to_Boolean(p_init_msg_list) THEN
2496 FND_MSG_PUB.initialize;
2497 END IF;
2498
2499 -- Initialize API return status to success
2500 x_return_status := FND_API.G_RET_STS_SUCCESS;
2501 x_credit_acct_id := NULL;
2502 x_debit_acct_id := NULL;
2503 x_ic_cogs_acct_id := NULL;
2504 /* Support for Landed Cost Management */
2505 x_lcm_acct_id := NULL;
2506
2507 -- No accounts are stored for IC events.
2508 l_stmt_num := 5;
2509 IF(p_rcv_event.event_type_id IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
2510 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
2511 return;
2512 END IF;
2513
2514 l_stmt_num := 10;
2515 SELECT POD.accrual_account_id,
2516 POD.code_combination_id,
2517 NVL(POD.dest_charge_account_id,POD.code_combination_id),
2518 POD.budget_account_id
2519 INTO l_pod_accrual_acct_id,
2520 l_pod_ccid,
2521 l_dest_pod_ccid,
2522 l_pod_budget_acct_id
2523 FROM po_distributions POD
2524 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
2525
2526 l_stmt_num := 20;
2527 SELECT receiving_account_id,
2528 clearing_account_id,
2529 retroprice_adj_account_id,
2530 /* Support for Landed Cost Management */
2531 DECODE(p_lcm_flag, 'Y', lcm_account_id, NULL)
2532 INTO l_receiving_insp_acct_id,
2533 l_clearing_acct_id,
2534 l_retroprice_adj_acct_id,
2535 /* Support for Landed Cost Management */
2536 l_lcm_acct_id
2537 FROM RCV_PARAMETERS
2538 WHERE organization_id = p_rcv_event.organization_id;
2539 -- Changes for JFMIP. Bug # 3076229. Call API to override the balancing segment
2540 -- of the Receiving Inspection account for expense destination types. The option
2541 -- (Auto Offset Override on PO_SYSTEM_PARAMETERS) will only be available in orgs
2542 -- where encumbrance is enabled. Hence this is not applicable to Global Procurement,
2543 -- Drop Ship or retroactive pricing.
2544 -- Modified for bug #4893292: Call API to override balance segment for inventory destinations as well.
2545 IF(p_rcv_event.trx_flow_header_id IS NULL AND
2546 p_rcv_event.event_type_id IN (RCV_SeedEvents_PVT.RECEIVE, RCV_SeedEvents_PVT.MATCH,
2547 RCV_SeedEvents_PVT.DELIVER, RCV_SeedEvents_PVT.CORRECT,
2548 RCV_SeedEvents_PVT.RETURN_TO_RECEIVING,
2549 RCV_SeedEvents_PVT.RETURN_TO_VENDOR) AND
2550 p_rcv_event.destination_type_code IN ('EXPENSE', 'INVENTORY')) THEN
2551
2552 l_stmt_num := 30;
2553 PO_Accounting_GRP.build_offset_account
2554 (p_api_version => 1.0,
2555 p_init_msg_list => FND_API.G_FALSE,
2556 x_return_status => l_return_status,
2557 p_base_ccid => l_receiving_insp_acct_id,
2558 p_overlay_ccid => l_dest_pod_ccid,
2559 p_accounting_date =>sysdate,
2560 p_org_id => p_rcv_event.org_id,
2561 x_result_ccid => l_overlaid_acct
2562 );
2563
2564 l_receiving_insp_acct_id := l_overlaid_acct;
2565
2566 END IF;
2567
2568 IF( p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT) THEN
2569
2570 l_stmt_num := 40;
2571 SELECT PARENT_TRX.transaction_type
2572 INTO l_parent_trx_type
2573 FROM rcv_transactions TRX,
2574 rcv_transactions PARENT_TRX
2575 WHERE TRX.transaction_id = p_rcv_event.rcv_transaction_id
2576 AND TRX.parent_transaction_id = PARENT_TRX.transaction_id;
2577 END IF;
2578
2579 l_stmt_num := 50;
2580 IF((p_rcv_event.event_type_id = RCV_SeedEvents_PVT.RECEIVE) OR
2581 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.MATCH) OR
2582 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2583 l_parent_trx_type = 'RECEIVE') OR
2584 p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2585 l_parent_trx_type = 'MATCH') THEN
2586
2587 l_debit_acct_id := l_receiving_insp_acct_id;
2588
2589 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2590 l_credit_acct_id := l_pod_accrual_acct_id;
2591 ELSIF(p_rcv_event.item_id IS NULL) THEN
2592 l_credit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2593 ELSE
2594 l_credit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2595 END IF;
2596
2597 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.LOGICAL_RECEIVE) THEN
2598
2599 -- Use clearing account for :
2600 -- a. destination type of Inventory
2601 -- b. destination type of Expense for inventory items.
2602 -- Use Cost of Sales account for
2603 -- a. destination type of Shop Floor.
2604 -- b. destination type of Expense for one time items
2605 IF(p_rcv_event.destination_type_code = 'INVENTORY' OR
2606 (p_rcv_event.destination_type_code = 'EXPENSE' AND
2607 p_rcv_event.item_id is not null)) THEN
2608 l_debit_acct_id := l_clearing_acct_id;
2609 ELSIF(p_rcv_event.procurement_org_flag = 'Y') THEN
2610 l_debit_acct_id := l_pod_ccid;
2611 ELSE
2612 l_stmt_num := 60;
2613 SELECT cost_of_sales_account
2614 INTO l_ic_coss_acct_id
2615 FROM mtl_parameters MP
2616 WHERE MP.organization_id = p_rcv_event.organization_id;
2617
2618 l_stmt_num := 70;
2619 RCV_SeedEvents_PVT.Get_HookAccount(
2620 p_api_version => l_api_version,
2621 x_return_status => l_return_status,
2622 x_msg_count => l_msg_count,
2623 x_msg_data => l_msg_data,
2624 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2625 p_accounting_line_type => 'IC Cost Of Sales',
2626 p_org_id => p_rcv_event.org_id,
2627 x_distribution_acct_id => l_dist_acct_id);
2628
2629 IF l_return_status <> FND_API.g_ret_sts_success THEN
2630 l_api_message := 'Error in Get_HookAccount';
2631 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2632 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2633 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2634 END IF;
2635 RAISE FND_API.g_exc_unexpected_error;
2636 END IF;
2637
2638 IF(l_dist_acct_id = -1) THEN
2639 l_debit_acct_id := l_ic_coss_acct_id;
2640 ELSE
2641 l_debit_acct_id := l_dist_acct_id;
2642 END IF;
2643
2644 END IF;
2645
2646 l_stmt_num := 80;
2647 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2648 l_credit_acct_id := l_pod_accrual_acct_id;
2649 ELSIF(p_rcv_event.item_id IS NULL) THEN
2650 l_credit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2651 ELSE
2652 l_credit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2653 END IF;
2654
2655 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.DELIVER OR
2656 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2657 l_parent_trx_type = 'DELIVER')) THEN
2658 l_debit_acct_id := l_dest_pod_ccid;
2659 l_credit_acct_id := l_receiving_insp_acct_id;
2660
2661 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.RETURN_TO_VENDOR OR
2662 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2663 l_parent_trx_type = 'RETURN TO VENDOR')) THEN
2664 l_credit_acct_id := l_receiving_insp_acct_id;
2665
2666 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2667 l_debit_acct_id := l_pod_accrual_acct_id;
2668 ELSIF(p_rcv_event.item_id IS NULL) THEN
2669 l_debit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2670 ELSE
2671 l_debit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2672 END IF;
2673
2674 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.LOGICAL_RETURN_TO_VENDOR) THEN
2675 IF(p_rcv_event.destination_type_code = 'INVENTORY' OR
2676 (p_rcv_event.destination_type_code = 'EXPENSE' AND
2677 p_rcv_event.item_id is not null)) THEN
2678 l_credit_acct_id := l_clearing_acct_id;
2679 ELSIF(p_rcv_event.procurement_org_flag = 'Y') THEN
2680 l_credit_acct_id := l_pod_ccid;
2681 ELSE
2682 l_stmt_num := 90;
2683 SELECT cost_of_sales_account
2684 INTO l_ic_coss_acct_id
2685 FROM mtl_parameters MP
2686 WHERE MP.organization_id = p_rcv_event.organization_id;
2687
2688 l_stmt_num := 100;
2689 RCV_SeedEvents_PVT.Get_HookAccount(
2690 p_api_version => l_api_version,
2691 x_return_status => l_return_status,
2692 x_msg_count => l_msg_count,
2693 x_msg_data => l_msg_data,
2694 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2695 p_accounting_line_type => 'IC Cost Of Sales',
2696 p_org_id => p_rcv_event.org_id,
2697 x_distribution_acct_id => l_dist_acct_id);
2698
2699 IF l_return_status <> FND_API.g_ret_sts_success THEN
2700 l_api_message := 'Error in Get_HookAccount';
2701 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2702 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2703 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2704 END IF;
2705 RAISE FND_API.g_exc_unexpected_error;
2706 END IF;
2707
2708 IF(l_dist_acct_id = -1) THEN
2709 l_credit_acct_id := l_ic_coss_acct_id;
2710 ELSE
2711 l_credit_acct_id := l_dist_acct_id;
2712 END IF;
2713
2714 END IF;
2715
2716 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2717 l_debit_acct_id := l_pod_accrual_acct_id;
2718 ELSIF(p_rcv_event.item_id IS NULL) THEN
2719 l_debit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2720 ELSE
2721 l_debit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2722 END IF;
2723
2724 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.RETURN_TO_RECEIVING OR
2725 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2726 l_parent_trx_type = 'RETURN TO RECEIVING')) THEN
2727 l_credit_acct_id := l_dest_pod_ccid;
2728 l_debit_acct_id := l_receiving_insp_acct_id;
2729
2730 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ADJUST_RECEIVE) THEN
2731 -- In the case of drop shipments, we always use the clearing account instead of the Receiving
2732 -- Inspection account. In these scenarios, we should be posting the adjustment for the entire
2733 -- Receipt to the retroactive price adjustment account.
2734 IF(p_rcv_event.trx_flow_header_id IS NOT NULL OR p_rcv_event.drop_ship_flag IN (1,2)) THEN
2735
2736 -- For global procurement scenarios, the debit account is :
2737 -- Retroprice adjustment account for inv items and direct items.
2738 -- IC Cost Of Sales(Charge acct on POD) for one-time items and Expense destinations.
2739
2740 IF ( p_rcv_event.item_id IS NOT NULL OR
2741 p_rcv_event.destination_type_code = 'SHOP FLOOR') THEN
2742
2743 l_stmt_num := 110;
2744 RCV_SeedEvents_PVT.Get_HookAccount(
2745 p_api_version => l_api_version,
2746 x_return_status => l_return_status,
2747 x_msg_count => l_msg_count,
2748 x_msg_data => l_msg_data,
2749 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2750 p_accounting_line_type => 'Retroprice Adjustment',
2751 p_org_id => p_rcv_event.org_id,
2752 x_distribution_acct_id => l_dist_acct_id);
2753
2754 IF l_return_status <> FND_API.g_ret_sts_success THEN
2755 l_api_message := 'Error in Get_HookAccount';
2756 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2757 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2758 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2759 END IF;
2760 RAISE FND_API.g_exc_unexpected_error;
2761 END IF;
2762
2763 IF(l_dist_acct_id = -1) THEN
2764 l_debit_acct_id := l_retroprice_adj_acct_id;
2765 ELSE
2766 l_debit_acct_id := l_dist_acct_id;
2767 END IF;
2768
2769 ELSE
2770 l_debit_acct_id := l_pod_ccid;
2771 END IF;
2772 ELSE
2773 l_debit_acct_id := l_receiving_insp_acct_id;
2774 END IF;
2775
2776 l_credit_acct_id := l_pod_accrual_acct_id;
2777
2778 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ADJUST_DELIVER) THEN
2779
2780 -- Redundant check. Transaction flow header id is always NULL. We only
2781 -- get ADJUST_RECEIVE events for global procurement.
2782 /* Modified for bug 8832353
2783 IF(p_rcv_event.trx_flow_header_id IS NULL AND p_rcv_event.drop_ship_flag NOT IN (1,2)) THEN*/
2784 IF(p_rcv_event.trx_flow_header_id IS NULL AND (p_rcv_event.drop_ship_flag IS NULL OR p_rcv_event.drop_ship_flag NOT IN (1,2))) THEN
2785 IF(p_rcv_event.destination_type_code = 'EXPENSE')THEN
2786 l_debit_acct_id := l_dest_pod_ccid;
2787 ELSE
2788
2789 l_stmt_num := 120;
2790 RCV_SeedEvents_PVT.Get_HookAccount(
2791 p_api_version => l_api_version,
2792 x_return_status => l_return_status,
2793 x_msg_count => l_msg_count,
2794 x_msg_data => l_msg_data,
2795 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2796 p_accounting_line_type => 'Retroprice Adjustment',
2797 p_org_id => p_rcv_event.org_id,
2798 x_distribution_acct_id => l_dist_acct_id);
2799
2800 IF l_return_status <> FND_API.g_ret_sts_success THEN
2801 l_api_message := 'Error in Get_HookAccount';
2802 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2803 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2804 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2805 END IF;
2806 RAISE FND_API.g_exc_unexpected_error;
2807 END IF;
2808
2809 IF(l_dist_acct_id = -1) THEN
2810 l_debit_acct_id := l_retroprice_adj_acct_id;
2811 ELSE
2812 l_debit_acct_id := l_dist_acct_id;
2813 END IF;
2814 END IF;
2815
2816 l_stmt_num := 130;
2817 l_credit_acct_id := l_receiving_insp_acct_id;
2818 END IF;
2819
2820 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE OR
2821 p_rcv_event.event_type_id = RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)THEN
2822 l_credit_acct_id := NULL;
2823 l_debit_acct_id := NULL;
2824
2825 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL) THEN
2826
2827
2828 l_stmt_num := 140;
2829 SELECT RT.transaction_type, RT.parent_transaction_id
2830 INTO l_trx_type, l_parent_trx_id
2831 FROM rcv_transactions RT
2832 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id;
2833
2834 IF(l_trx_type = 'DELIVER')THEN
2835 l_credit_acct_id := l_pod_budget_acct_id;
2836 l_debit_acct_id := NULL;
2837 ELSIF (l_trx_type = 'RETURN TO RECEIVING') THEN
2838 l_debit_acct_id := l_pod_budget_acct_id;
2839 l_credit_acct_id := NULL;
2840 ELSIF (l_trx_type = 'CORRECT') THEN
2841
2842 l_stmt_num := 150;
2843 SELECT PARENT_TRX.transaction_type
2844 INTO l_parent_trx_type
2845 FROM rcv_transactions PARENT_TRX
2846 WHERE PARENT_TRX.transaction_id = l_parent_trx_id;
2847
2848 IF(l_parent_trx_type = 'DELIVER')THEN
2849 l_credit_acct_id := l_pod_budget_acct_id;
2850 l_debit_acct_id := NULL;
2851 ELSIF (l_parent_trx_type = 'RETURN_TO_RECEIVING') THEN
2852 l_debit_acct_id := l_pod_budget_acct_id;
2853 l_credit_acct_id := NULL;
2854 END IF;
2855 END IF;
2856
2857 END IF;
2858
2859 x_debit_acct_id := l_debit_acct_id;
2860 x_credit_acct_id := l_credit_acct_id;
2861 x_ic_cogs_acct_id := p_transaction_forward_flow_rec.intercompany_cogs_account_id;
2862 /* Support for Landed Cost Management */
2863 x_lcm_acct_id := l_lcm_acct_id;
2864
2865 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2866 l_api_message := 'x_debit_acct_id : '||x_debit_acct_id||
2867 ' x_credit_acct_id : '||x_credit_acct_id||
2868 ' x_ic_cogs_acct_id : ' || x_ic_cogs_acct_id;
2869 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2870 ,l_api_message);
2871 END IF;
2872
2873 IF ((l_debit_acct_id IS NULL OR l_credit_acct_id IS NULL) AND
2874 (p_rcv_event.event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
2875 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL,
2876 RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL))) THEN
2877 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2878 l_api_message := 'Unable to find credit and/or debit account. Setup is incomplete. ';
2879 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2880 ,l_api_message);
2881 END IF;
2882
2883 FND_MESSAGE.set_name('PO','PO_INVALID_ACCOUNT');
2884 FND_MSG_pub.add;
2885 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2886 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
2887 END IF;
2888 RAISE FND_API.g_exc_error;
2889 END IF;
2890
2891
2892 -- Standard check of p_commit
2893 IF FND_API.to_Boolean(p_commit) THEN
2894 COMMIT WORK;
2895 END IF;
2896
2897 -- Standard Call to get message count and if count = 1, get message info
2898 FND_MSG_PUB.Count_And_Get (
2899 p_count => x_msg_count,
2900 p_data => x_msg_data );
2901
2902 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2903 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
2904 ,'Get_Accounts >>');
2905 END IF;
2906
2907
2908 EXCEPTION
2909 WHEN FND_API.g_exc_error THEN
2910 ROLLBACK TO Get_Accounts_PVT;
2911 x_return_status := FND_API.g_ret_sts_error;
2912 FND_MSG_PUB.count_and_get
2913 ( p_count => x_msg_count
2914 , p_data => x_msg_data
2915 );
2916 WHEN FND_API.g_exc_unexpected_error THEN
2917 ROLLBACK TO Get_Accounts_PVT;
2918 x_return_status := FND_API.g_ret_sts_unexp_error ;
2919 FND_MSG_PUB.count_and_get
2920 ( p_count => x_msg_count
2921 , p_data => x_msg_data
2922 );
2923
2924 WHEN OTHERS THEN
2925 ROLLBACK TO Get_Accounts_PVT;
2926 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2927
2928 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2929 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2930 ,'Get_Accounts : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
2931 END IF;
2932
2933 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2934 THEN
2935 FND_MSG_PUB.add_exc_msg
2936 ( G_PKG_NAME,
2937 l_api_name || 'Statement -'||to_char(l_stmt_num)
2938 );
2939 END IF;
2940 FND_MSG_PUB.count_and_get
2941 ( p_count => x_msg_count
2942 , p_data => x_msg_data
2943 );
2944
2945 END Get_Accounts;
2946
2947 -- Start of comments
2948 -- API name : Get_HookAccount
2949 -- Type : Private
2950 -- Function : Call account hook to allow customer to override default account.
2951 -- Pre-reqs :
2952 -- Parameters :
2953 -- IN : p_api_version IN NUMBER Required
2954 -- p_init_msg_list IN VARCHAR2 Optional
2955 -- Default = FND_API.G_FALSE
2956 -- p_commit IN VARCHAR2 Optional
2957 -- Default = FND_API.G_FALSE
2958 -- p_validation_level IN NUMBER Optional
2959 -- Default = FND_API.G_VALID_LEVEL_FULL
2960 -- p_rcv_transaction_id IN NUMBER Required
2961 -- p_accounting_line_type IN VARCHAR2 Required
2962 -- p_org_id IN NUMBER Required
2963 --
2964 -- OUT : x_return_status OUT VARCHAR2(1)
2965 -- x_msg_count OUT NUMBER
2966 -- x_msg_data OUT VARCHAR2(2000)
2967 -- x_distribution_acct_id OUT NUMBER
2968 --
2969 -- Version :
2970 -- Initial version 1.0
2971 --
2972 --
2973 -- Notes : This API creates all accounting events for RETURN TO VENDOR transactions
2974 -- in RCV_ACCOUNTING_EVENTS.
2975 --
2976 -- End of comments
2977 PROCEDURE Get_HookAccount(
2978 p_api_version IN NUMBER,
2979 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2980 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2981 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2982 x_return_status OUT NOCOPY VARCHAR2,
2983 x_msg_count OUT NOCOPY NUMBER,
2984 x_msg_data OUT NOCOPY VARCHAR2,
2985
2986 p_rcv_transaction_id IN NUMBER,
2987 p_accounting_line_type IN VARCHAR2,
2988 p_org_id IN NUMBER,
2989 x_distribution_acct_id OUT NOCOPY NUMBER
2990 ) IS
2991 l_api_name CONSTANT VARCHAR2(30) := 'Get_HookAccount';
2992 l_api_version CONSTANT NUMBER := 1.0;
2993
2994 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2995 l_msg_count NUMBER := 0;
2996 l_msg_data VARCHAR2(8000) := '';
2997 l_stmt_num NUMBER := 0;
2998 l_api_message VARCHAR2(1000);
2999
3000 l_dist_acct_id NUMBER;
3001 l_account_flag NUMBER;
3002
3003 BEGIN
3004 -- Standard start of API savepoint
3005 SAVEPOINT Get_HookAccount_PVT;
3006
3007 l_stmt_num := 0;
3008
3009 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3010 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
3011 ,'Get_HookAccount <<');
3012
3013 END IF;
3014
3015 -- Standard call to check for call compatibility
3016 IF NOT FND_API.Compatible_API_Call (
3017 l_api_version,
3018 p_api_version,
3019 l_api_name,
3020 G_PKG_NAME ) THEN
3021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3022 END IF;
3023
3024 -- Initialize message list if p_init_msg_list is set to TRUE
3025 IF FND_API.to_Boolean(p_init_msg_list) THEN
3026 FND_MSG_PUB.initialize;
3027 END IF;
3028
3029 -- Initialize API return status to success
3030 x_return_status := FND_API.G_RET_STS_SUCCESS;
3031 x_distribution_acct_id := -1;
3032
3033 l_stmt_num := 10;
3034 RCV_AccountHook_PUB.Get_Account(
3035 p_api_version => l_api_version,
3036 x_return_status => l_return_status,
3037 x_msg_count => l_msg_count,
3038 x_msg_data => l_msg_data,
3039 p_rcv_transaction_id => p_rcv_transaction_id,
3040 p_accounting_line_type => p_accounting_line_type,
3041 x_distribution_acct_id => l_dist_acct_id);
3042
3043 IF l_return_status <> FND_API.g_ret_sts_success THEN
3044 l_api_message := 'Error in Account Hook';
3045 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3046 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3047 ,'Get_HookAccount : '||l_stmt_num||' : '||l_api_message);
3048 END IF;
3049 RAISE FND_API.g_exc_unexpected_error;
3050 END IF;
3051
3052 IF(l_dist_acct_id <> -1) THEN
3053
3054 l_stmt_num := 20;
3055 SELECT count(*)
3056 INTO l_account_flag
3057 FROM gl_code_combinations GCC,
3058 cst_organization_definitions COD
3059 WHERE COD.operating_unit = p_org_id
3060 AND COD.chart_of_accounts_id = GCC.chart_of_accounts_id
3061 AND GCC.code_combination_id = l_dist_acct_id;
3062
3063 IF(l_account_flag = 0)THEN
3064 FND_MESSAGE.set_name('PO','PO_INVALID_ACCOUNT');
3065 FND_MSG_pub.add;
3066 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3067 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
3068 END IF;
3069 RAISE FND_API.g_exc_error;
3070 END IF;
3071 END IF;
3072
3073 x_distribution_acct_id := l_dist_acct_id;
3074
3075 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3076 l_api_message := 'x_distribution_acct_id : '||x_distribution_acct_id;
3077 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3078 ,l_api_message);
3079 END IF;
3080
3081 -- Standard check of p_commit
3082 IF FND_API.to_Boolean(p_commit) THEN
3083 COMMIT WORK;
3084 END IF;
3085
3086 -- Standard Call to get message count and if count = 1, get message info
3087 FND_MSG_PUB.Count_And_Get (
3088 p_count => x_msg_count,
3089 p_data => x_msg_data );
3090
3091 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3092 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
3093 ,'Get_HookAccount >>');
3094 END IF;
3095
3096
3097 EXCEPTION
3098 WHEN FND_API.g_exc_error THEN
3099 ROLLBACK TO Get_HookAccount_PVT;
3100 x_return_status := FND_API.g_ret_sts_error;
3101 FND_MSG_PUB.count_and_get
3102 ( p_count => x_msg_count
3103 , p_data => x_msg_data
3104 );
3105 WHEN FND_API.g_exc_unexpected_error THEN
3106 ROLLBACK TO Get_HookAccount_PVT;
3107 x_return_status := FND_API.g_ret_sts_unexp_error ;
3108 FND_MSG_PUB.count_and_get
3109 ( p_count => x_msg_count
3110 , p_data => x_msg_data
3111 );
3112
3113 WHEN OTHERS THEN
3114 ROLLBACK TO Get_HookAccount_PVT;
3115 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3116
3117 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3118 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3119 ,'Get_HookAccount : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
3120 END IF;
3121
3122 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3123 THEN
3124 FND_MSG_PUB.add_exc_msg
3125 ( G_PKG_NAME,
3126 l_api_name || 'Statement -'||to_char(l_stmt_num)
3127 );
3128 END IF;
3129 FND_MSG_PUB.count_and_get
3130 ( p_count => x_msg_count
3131 , p_data => x_msg_data
3132 );
3133
3134 END Get_HookAccount;
3135
3136
3137
3138
3139 -- Start of comments
3140 -- API name : Insert_RAEEvents
3141 -- Type : Private
3142 -- Function : To insert events into the Receiving Accounting Events table.
3143 -- Pre-reqs :
3144 -- Parameters :
3145 -- IN : p_api_version IN NUMBER Required
3146 -- p_init_msg_list IN VARCHAR2 Optional
3147 -- Default = FND_API.G_FALSE
3148 -- p_commit IN VARCHAR2 Optional
3149 -- Default = FND_API.G_FALSE
3150 -- p_validation_level IN NUMBER Optional
3151 -- Default = FND_API.G_VALID_LEVEL_FULL
3152 -- p_rcv_events_tbl IN RCV_SeedEvents_PVT.rcv_event_tbl_type Required
3153 --
3154 -- OUT : x_return_status OUT VARCHAR2(1)
3155 -- x_msg_count OUT NUMBER
3156 -- x_msg_data OUT VARCHAR2(2000)
3157 -- Version :
3158 -- Initial version 1.0
3159 --
3160 --
3161 -- Notes : This API inserts all events for a given receiving transaction
3162 -- into RCV_ACCOUNTING_EVENTS.
3163 --
3164 -- End of comments
3165 PROCEDURE Insert_RAEEvents(
3166 p_api_version IN NUMBER,
3167 p_init_msg_list IN VARCHAR2,
3168 p_commit IN VARCHAR2,
3169 p_validation_level IN NUMBER,
3170 x_return_status OUT NOCOPY VARCHAR2,
3171 x_msg_count OUT NOCOPY NUMBER,
3172 x_msg_data OUT NOCOPY VARCHAR2,
3173
3174 p_rcv_events_tbl IN RCV_SeedEvents_PVT.rcv_event_tbl_type,
3175 /* Support for Landed Cost Management */
3176 p_lcm_flag IN VARCHAR2
3177 ) IS
3178 l_api_name CONSTANT VARCHAR2(30) := 'Insert_RAEEvents';
3179 l_api_version CONSTANT NUMBER := 1.0;
3180
3181 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3182 l_msg_count NUMBER := 0;
3183 l_msg_data VARCHAR2(8000) := '';
3184 l_stmt_num NUMBER := 0;
3185 l_api_message VARCHAR2(1000);
3186
3187 l_summarize_acc_flag VARCHAR2(1) := 'N';
3188
3189 l_err_num NUMBER;
3190 l_err_code VARCHAR2(240);
3191 l_err_msg VARCHAR2(240);
3192 l_return_code NUMBER;
3193
3194 l_rcv_transaction_id NUMBER;
3195 l_del_transaction_id NUMBER;
3196 l_detail_accounting_flag VARCHAR2(1) := 'Y';
3197 l_accrue_on_receipt_flag VARCHAR2(1) := 'N';
3198 l_accounting_event_id NUMBER;
3199
3200 l_ctr_first NUMBER;
3201
3202 BEGIN
3203 -- Standard start of API savepoint
3204 SAVEPOINT Insert_RAEEvents_PVT;
3205
3206 l_stmt_num := 0;
3207
3208 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3209 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
3210 ,'Insert_RAEEvents <<');
3211
3212 END IF;
3213
3214 -- Standard call to check for call compatibility
3215 IF NOT FND_API.Compatible_API_Call (
3216 l_api_version,
3217 p_api_version,
3218 l_api_name,
3219 G_PKG_NAME ) THEN
3220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3221 END IF;
3222
3223 -- Initialize message list if p_init_msg_list is set to TRUE
3224 IF FND_API.to_Boolean(p_init_msg_list) THEN
3225 FND_MSG_PUB.initialize;
3226 END IF;
3227
3228 -- Initialize API return status to success
3229 x_return_status := FND_API.G_RET_STS_SUCCESS;
3230
3231 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3232 l_api_message := 'Inserting '||p_rcv_events_tbl.count||' events into RAE';
3233 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3234 ,l_api_message);
3235 END IF;
3236
3237 l_ctr_first := p_rcv_events_tbl.FIRST;
3238
3239 -- Check for accrual option. If accrual option is set to accrue at period-end, don't call the
3240 -- accounting API.
3241 l_stmt_num := 20;
3242 SELECT nvl(poll.accrue_on_receipt_flag, 'N')
3243 INTO l_accrue_on_receipt_flag
3244 FROM po_line_locations POLL
3245 WHERE POLL.line_location_id = p_rcv_events_tbl(l_ctr_first).po_line_location_id;
3246
3247 FOR i IN p_rcv_events_tbl.FIRST..p_rcv_events_tbl.LAST LOOP
3248 l_stmt_num := 30;
3249 SELECT rcv_accounting_event_s.nextval
3250 INTO l_accounting_event_id
3251 FROM dual;
3252
3253 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3254 l_api_message := SUBSTR('i : '||i||
3255 'accounting_event_id : '||l_accounting_event_id||
3256 'rcv_transaction_id : '||p_rcv_events_tbl(i).rcv_transaction_id||
3257 'po_line_id : '||p_rcv_events_tbl(i).po_line_id||
3258 'po_dist_id : '||p_rcv_events_tbl(i).po_distribution_id||
3259 'unit_price : '||p_rcv_events_tbl(i).unit_price||
3260 'currency : '||p_rcv_events_tbl(i).currency_code||
3261 'nr tax : '||p_rcv_events_tbl(i).unit_nr_tax||
3262 'rec tax : '||p_rcv_events_tbl(i).unit_rec_tax,1,1000);
3263 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3264 ,l_api_message);
3265 END IF;
3266
3267 -- We are not doing a bulk insert due to a database limitation. On databases
3268 -- prior to 9i, you cannot do a bulk insert using a table of records. You have to
3269 -- multiple tables of scalar types. The expense of converting the table of records
3270 -- to multiple tables is not worthwhile in this case since we do not expect the
3271 -- number of rows to exceed 10.
3272 l_stmt_num := 40;
3273 INSERT into RCV_ACCOUNTING_EVENTS(
3274 accounting_event_id,
3275 last_update_date,
3276 last_updated_by,
3277 last_update_login,
3278 creation_date,
3279 created_by,
3280 request_id,
3281 program_application_id,
3282 program_id,
3283 program_udpate_date,
3284 rcv_transaction_id,
3285 event_type_id,
3286 event_source,
3287 event_source_id,
3288 set_of_books_id,
3289 org_id,
3290 transfer_org_id,
3291 organization_id,
3292 transfer_organization_id,
3293 debit_account_id,
3294 credit_account_id,
3295 /* Support for Landed Cost Management */
3296 lcm_account_id,
3297 transaction_date,
3298 source_doc_quantity,
3299 transaction_quantity,
3300 primary_quantity,
3301 source_doc_unit_of_measure,
3302 transaction_unit_of_measure,
3303 primary_unit_of_measure,
3304 po_header_id,
3305 po_release_id,
3306 po_line_id,
3307 po_line_location_id,
3308 po_distribution_id,
3309 inventory_item_id,
3310 unit_price,
3311 prior_unit_price,
3312 intercompany_pricing_option,
3313 transaction_amount,
3314 nr_tax,
3315 rec_tax,
3316 nr_tax_amount,
3317 rec_tax_amount,
3318 prior_nr_tax,
3319 prior_rec_tax,
3320 currency_code,
3321 currency_conversion_type,
3322 currency_conversion_rate,
3323 currency_conversion_date,
3324 accounted_flag,
3325 procurement_org_flag,
3326 cross_ou_flag,
3327 trx_flow_header_id,
3328 invoiced_flag,
3329 pa_addition_flag,
3330 /* Support for Landed Cost Management */
3331 unit_landed_cost)
3332 (SELECT
3333 l_accounting_event_id,
3334 sysdate,
3335 fnd_global.user_id,
3336 fnd_global.login_id,
3337 sysdate,
3338 fnd_global.user_id,
3339 fnd_global.conc_request_id,
3340 fnd_global.prog_appl_id,
3341 fnd_global.conc_program_id,
3342 sysdate,
3343 p_rcv_events_tbl(i).rcv_transaction_id,
3344 p_rcv_events_tbl(i).event_type_id,
3345 p_rcv_events_tbl(i).event_source,
3346 decode(p_rcv_events_tbl(i).event_source,
3347 'INVOICEMATCH', p_rcv_events_tbl(i).inv_distribution_id,
3348 p_rcv_events_tbl(i).rcv_transaction_id),
3349 p_rcv_events_tbl(i).set_of_books_id,
3350 p_rcv_events_tbl(i).org_id,
3351 p_rcv_events_tbl(i).transfer_org_id,
3352 p_rcv_events_tbl(i).organization_id,
3353 p_rcv_events_tbl(i).transfer_organization_id,
3354 p_rcv_events_tbl(i).debit_account_id,
3355 p_rcv_events_tbl(i).credit_account_id,
3356 /* Support for Landed Cost Management */
3357 p_rcv_events_tbl(i).lcm_account_id,
3358 p_rcv_events_tbl(i).transaction_date,
3359 decode(p_rcv_events_tbl(i).service_flag, 'N',
3360 p_rcv_events_tbl(i).source_doc_quantity , NULL) source_doc_quantity,
3361 decode(p_rcv_events_tbl(i).service_flag, 'N',
3362 p_rcv_events_tbl(i).transaction_quantity , NULL) transaction_quantity,
3363 decode(p_rcv_events_tbl(i).service_flag, 'N',
3364 p_rcv_events_tbl(i).primary_quantity , NULL) primary_quantity,
3365 p_rcv_events_tbl(i).source_doc_uom,
3366 p_rcv_events_tbl(i).transaction_uom,
3367 p_rcv_events_tbl(i).primary_uom,
3368 p_rcv_events_tbl(i).po_header_id,
3369 p_rcv_events_tbl(i).po_release_id,
3370 p_rcv_events_tbl(i).po_line_id,
3371 p_rcv_events_tbl(i).po_line_location_id,
3372 p_rcv_events_tbl(i).po_distribution_id,
3373 p_rcv_events_tbl(i).item_id,
3374 decode(p_rcv_events_tbl(i).service_flag, 'N',
3375 p_rcv_events_tbl(i).unit_price + p_rcv_events_tbl(i).unit_nr_tax, NULL) unit_price,
3376 decode(p_rcv_events_tbl(i).event_source,'RETROPRICE',
3377 p_rcv_events_tbl(i).prior_unit_price + p_rcv_events_tbl(i).prior_nr_tax,NULL),
3378 p_rcv_events_tbl(i).intercompany_pricing_option,
3379 decode(p_rcv_events_tbl(i).service_flag,'Y',
3380 p_rcv_events_tbl(i).transaction_amount+
3381 p_rcv_events_tbl(i).transaction_amount * p_rcv_events_tbl(i).unit_nr_tax, NULL),
3382 decode(p_rcv_events_tbl(i).service_flag, 'N',
3383 p_rcv_events_tbl(i).unit_nr_tax,NULL),
3384 decode(p_rcv_events_tbl(i).service_flag, 'N',
3385 p_rcv_events_tbl(i).unit_rec_tax,NULL),
3386 decode(p_rcv_events_tbl(i).service_flag, 'Y',
3387 p_rcv_events_tbl(i).transaction_amount*p_rcv_events_tbl(i).unit_nr_tax,NULL),
3388 decode(p_rcv_events_tbl(i).service_flag, 'Y',
3389 p_rcv_events_tbl(i).transaction_amount*p_rcv_events_tbl(i).unit_rec_tax,NULL),
3390 decode(p_rcv_events_tbl(i).event_source,'RETROPRICE',
3391 p_rcv_events_tbl(i).prior_nr_tax,NULL),
3392 decode(p_rcv_events_tbl(i).event_source,'RETROPRICE',
3393 p_rcv_events_tbl(i).prior_rec_tax,NULL),
3394 p_rcv_events_tbl(i).Currency_code,
3395 p_rcv_events_tbl(i).Currency_conversion_type,
3396 p_rcv_events_tbl(i).Currency_conversion_rate,
3397 p_rcv_events_tbl(i).Currency_conversion_date,
3398 'N',
3399 p_rcv_events_tbl(i).procurement_org_flag,
3400 p_rcv_events_tbl(i).Cross_ou_flag,
3401 p_rcv_events_tbl(i).trx_flow_header_id,
3402 decode(p_rcv_events_tbl(i).event_type_id,
3403 RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,'N',
3404 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL,'N',NULL),
3405 'N', -- Will be changed by PA process
3406 /* Support for Landed Cost Management */
3407 p_rcv_events_tbl(i).unit_landed_cost
3408 FROM DUAL);
3409
3410 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3411 l_api_message := 'Inserted '||SQL%ROWCOUNT||
3412 'rows in RAE for org '||p_rcv_events_tbl(i).org_id;
3413 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3414 ,l_api_message);
3415 END IF;
3416
3417 /* For accrue on receipt POs, call the Create_Accounting API to generate accounting entries
3418 online. For period-end POs that are not global procurement scenarios, the accounting will
3419 be done by the period end accruals process. For global procurement scenarios, the accounting
3420 for the procurement org will be done at period end. For all other orgs, the accounting will
3421 be done online. */
3422 IF ((l_accrue_on_receipt_flag = 'Y' OR
3423 p_rcv_events_tbl(i).procurement_org_flag = 'N') AND
3424 p_rcv_events_tbl(i).event_type_id NOT IN
3425 (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
3426 l_stmt_num := 50;
3427 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3428 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3429 ,'Creating accounting entries in RRS');
3430 END IF;
3431
3432 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3433 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3434 ,'Creating accounting entries for accounting_event_id : '||l_accounting_event_id);
3435 END IF;
3436
3437 -- Call Account generation API to create accounting entries
3438 RCV_CreateAccounting_PVT.Create_AccountingEntry(
3439 p_api_version => 1.0,
3440 x_return_status => l_return_status,
3441 x_msg_count => l_msg_count,
3442 x_msg_data => l_msg_data,
3443 p_accounting_event_id => l_accounting_event_id,
3444 /* Support for Landed Cost Management */
3445 p_lcm_flag => p_lcm_flag);
3446 IF l_return_status <> FND_API.g_ret_sts_success THEN
3447 l_api_message := 'Error in Create_AccountingEntry API';
3448 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3449 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3450 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3451 END IF;
3452 RAISE FND_API.g_exc_unexpected_error;
3453 END IF;
3454
3455 -- Call PA API to update pa_addition_flag - bug 5074573 (fp of 4409125)
3456 l_stmt_num := 55;
3457
3458 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3459 l_api_message := 'Calling PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg with '
3460 || 'p_api_version = '|| '1.0'
3461 || 'p_rcv_transaction_id = ' || p_rcv_events_tbl(i).rcv_transaction_id
3462 || 'p_po_distribution_id = ' || p_rcv_events_tbl(i).po_distribution_id
3463 || 'p_accounting_event_id = ' || l_accounting_event_id;
3464 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3465 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3466 END IF;
3467
3468 PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg (
3469 p_api_version => 1.0,
3470 x_return_status => l_return_status,
3471 x_msg_count => l_msg_count,
3472 x_msg_data => l_msg_data,
3473 p_rcv_transaction_id => p_rcv_events_tbl(i).rcv_transaction_id,
3474 p_po_distribution_id => p_rcv_events_tbl(i).po_distribution_id,
3475 p_accounting_event_id => l_accounting_event_id);
3476
3477 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3478 l_api_message := 'PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg returned with '
3479 || 'x_return_status = '|| l_return_status
3480 || 'x_msg_count = ' || l_msg_count
3481 || 'x_msg_data = ' || l_msg_data;
3482 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3483 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3484 END IF;
3485
3486 IF l_return_status <> FND_API.g_ret_sts_success THEN
3487 l_api_message := 'Error in PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg API';
3488 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3489 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3490 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3491 END IF;
3492 RAISE FND_API.g_exc_unexpected_error;
3493 END IF;
3494
3495 END IF;
3496 END LOOP;
3497
3498
3499
3500 --- Standard check of p_commit
3501 IF FND_API.to_Boolean(p_commit) THEN
3502 COMMIT WORK;
3503 END IF;
3504
3505 -- Standard Call to get message count and if count = 1, get message info
3506 FND_MSG_PUB.Count_And_Get (
3507 p_count => x_msg_count,
3508 p_data => x_msg_data );
3509
3510 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3511 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
3512 ,'Insert_RAEEvents >>');
3513 END IF;
3514
3515
3516 EXCEPTION
3517 WHEN FND_API.g_exc_error THEN
3518 ROLLBACK TO Insert_RAEEvents_PVT;
3519 x_return_status := FND_API.g_ret_sts_error;
3520 FND_MSG_PUB.count_and_get
3521 ( p_count => x_msg_count
3522 , p_data => x_msg_data
3523 );
3524
3525 WHEN FND_API.g_exc_unexpected_error THEN
3526 ROLLBACK TO Insert_RAEEvents_PVT;
3527 x_return_status := FND_API.g_ret_sts_unexp_error ;
3528 FND_MSG_PUB.count_and_get
3529 ( p_count => x_msg_count
3530 , p_data => x_msg_data
3531 );
3532
3533 WHEN OTHERS THEN
3534 ROLLBACK TO Insert_RAEEvents_PVT;
3535 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3536
3537 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3538 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3539 ,'Insert_RAEEvents : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
3540 END IF;
3541
3542 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3543 THEN
3544 FND_MSG_PUB.add_exc_msg
3545 ( G_PKG_NAME,
3546 l_api_name || 'Statement -'||to_char(l_stmt_num)
3547 );
3548 END IF;
3549 FND_MSG_PUB.count_and_get
3550 ( p_count => x_msg_count
3551 , p_data => x_msg_data
3552 );
3553
3554 END Insert_RAEEvents;
3555
3556 -- Start of comments
3557 -- API name : Check_EncumbranceFlag
3558 -- Type : Private
3559 -- Function : Checks to see if encumbrance entries need to be created.
3560 -- Pre-reqs :
3561 -- Parameters :
3562 -- IN : p_api_version IN NUMBER Required
3563 -- p_init_msg_list IN VARCHAR2 Optional
3564 -- Default = FND_API.G_FALSE
3565 -- p_commit IN VARCHAR2 Optional
3566 -- Default = FND_API.G_FALSE
3567 -- p_validation_level IN NUMBER Optional
3568 -- Default = FND_API.G_VALID_LEVEL_FULL
3569 -- p_rcv_sob_id IN NUMBER Required
3570 -- p_po_header_id IN NUMBER Required
3571 --
3572 -- x_encumbrance_flag OUT VARCHAR2(1)
3573 -- x_ussgl_option OUT VARCHAR2(1)
3574 --
3575 --
3576 -- OUT : x_return_status OUT VARCHAR2(1)
3577 -- x_msg_count OUT NUMBER
3578 -- x_msg_data OUT VARCHAR2(2000)
3579 -- Version :
3580 -- Initial version 1.0
3581 --
3582 --
3583 -- Notes : This API checks to see if encumbrance entries need to
3584 -- be created.
3585 --
3586 -- End of comments
3587 PROCEDURE Check_EncumbranceFlag(
3588 p_api_version IN NUMBER,
3589 p_init_msg_list IN VARCHAR2,
3590 p_commit IN VARCHAR2,
3591 p_validation_level IN NUMBER,
3592 x_return_status OUT NOCOPY VARCHAR2,
3593 x_msg_count OUT NOCOPY NUMBER,
3594 x_msg_data OUT NOCOPY VARCHAR2,
3595
3596 p_rcv_sob_id IN NUMBER,
3597
3598 x_encumbrance_flag OUT NOCOPY VARCHAR2,
3599 x_ussgl_option OUT NOCOPY VARCHAR2
3600
3601 ) IS
3602 l_api_name CONSTANT VARCHAR2(30) := 'Check_EncumbranceFlag';
3603 l_api_version CONSTANT NUMBER := 1.0;
3604
3605 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3606 l_msg_count NUMBER := 0;
3607 l_msg_data VARCHAR2(8000) := '';
3608 l_stmt_num NUMBER := 0;
3609 l_api_message VARCHAR2(1000);
3610
3611 l_encumbrance_flag VARCHAR2(1);
3612
3613 BEGIN
3614 -- Standard start of API savepoint
3615 SAVEPOINT Check_EncumbranceFlag_PVT;
3616
3617 l_stmt_num := 0;
3618
3619 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3620 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
3621 ,'Check_EncumbranceFlag <<');
3622 END IF;
3623
3624 -- Standard call to check for call compatibility
3625 IF NOT FND_API.Compatible_API_Call (
3626 l_api_version,
3627 p_api_version,
3628 l_api_name,
3629 G_PKG_NAME ) THEN
3630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3631 END IF;
3632
3633 -- Initialize message list if p_init_msg_list is set to TRUE
3634 IF FND_API.to_Boolean(p_init_msg_list) THEN
3635 FND_MSG_PUB.initialize;
3636 END IF;
3637
3638 -- Initialize API return status to success
3639 x_return_status := FND_API.G_RET_STS_SUCCESS;
3640
3641 l_stmt_num := 10;
3642 SELECT nvl(FSP.purch_encumbrance_flag, 'N')
3643 INTO l_encumbrance_flag
3644 FROM financials_system_parameters FSP
3645 WHERE FSP.set_of_books_id = p_rcv_sob_id;
3646
3647 x_encumbrance_flag := l_encumbrance_flag;
3648 x_ussgl_option := NVL(FND_PROFILE.VALUE('USSGL_OPTION'),'N');
3649
3650
3651 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3652 l_api_message := 'Encumbrance Flag : '||x_encumbrance_flag||
3653 ' Ussgl Option : '||x_ussgl_option;
3654 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3655 ,l_api_message);
3656 END IF;
3657
3658
3659 -- Standard check of p_commit
3660 IF FND_API.to_Boolean(p_commit) THEN
3661 COMMIT WORK;
3662 END IF;
3663
3664 -- Standard Call to get message count and if count = 1, get message info
3665 FND_MSG_PUB.Count_And_Get (
3666 p_count => x_msg_count,
3667 p_data => x_msg_data );
3668
3669 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3670 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
3671 ,'Check_EncumbranceFlag >>');
3672 END IF;
3673
3674
3675 EXCEPTION
3676 WHEN FND_API.g_exc_error THEN
3677 ROLLBACK TO Check_EncumbranceFlag_PVT;
3678 x_return_status := FND_API.g_ret_sts_error;
3679 FND_MSG_PUB.count_and_get
3680 ( p_count => x_msg_count
3681 , p_data => x_msg_data
3682 );
3683 WHEN FND_API.g_exc_unexpected_error THEN
3684 ROLLBACK TO Check_EncumbranceFlag_PVT;
3685 x_return_status := FND_API.g_ret_sts_unexp_error ;
3686 FND_MSG_PUB.count_and_get
3687 ( p_count => x_msg_count
3688 , p_data => x_msg_data
3689 );
3690
3691 WHEN OTHERS THEN
3692 ROLLBACK TO Check_EncumbranceFlag_PVT;
3693 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3694
3695 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3696 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3697 ,'Check_EncumbranceFlag : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
3698 END IF;
3699
3700 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3701 THEN
3702 FND_MSG_PUB.add_exc_msg
3703 ( G_PKG_NAME,
3704 l_api_name || 'Statement -'||to_char(l_stmt_num)
3705 );
3706 END IF;
3707 FND_MSG_PUB.count_and_get
3708 ( p_count => x_msg_count
3709 , p_data => x_msg_data
3710 );
3711
3712 END Check_EncumbranceFlag;
3713
3714 -----------------------------------------------------------------------------
3715 -- Start of comments --
3716 -- --
3717 -- PROCEDURE --
3718 -- Insert_MMTEvents This API takes a PL/SQL table as input that has one --
3719 -- entry for each RAE event. It loops through the table --
3720 -- and calls Create_MMTRecord to create logical MMT --
3721 -- transactions as appropriate for each event. --
3722 -- --
3723 -- VERSION 1.0 --
3724 -- --
3725 -- PARAMETERS --
3726 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
3727 -- P_INIT_MSG_LIST Initialize message list? True/False --
3728 -- P_COMMIT Should the API commit before returning? True/False --
3729 -- P_VALIDATION_LEVEL Specify the level of validation on the inputs --
3730 -- P_RCV_EVENTS_TBL Collection of events of type rcv_event_rec_type --
3731 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
3732 -- X_MSG_COUNT Message Count - # of messages placed in message list--
3733 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
3734 -- --
3735 -- HISTORY: --
3736 -- 06/26/03 Bryan Kuntz Created --
3737 -- End of comments --
3738 -----------------------------------------------------------------------------
3739
3740 PROCEDURE Insert_MMTEvents (
3741 P_API_VERSION IN NUMBER,
3742 P_INIT_MSG_LIST IN VARCHAR2,
3743 P_COMMIT IN VARCHAR2,
3744 P_VALIDATION_LEVEL IN NUMBER,
3745 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3746 X_MSG_COUNT OUT NOCOPY NUMBER,
3747 X_MSG_DATA OUT NOCOPY VARCHAR2,
3748 P_RCV_EVENTS_TBL IN RCV_SeedEvents_PVT.rcv_event_tbl_type
3749 ) IS
3750 l_api_name CONSTANT VARCHAR2(30) := 'Insert_MMTEvents';
3751 l_api_version CONSTANT NUMBER := 1.0;
3752 l_api_message VARCHAR2(1000);
3753
3754 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3755 l_msg_count NUMBER := 0;
3756 l_msg_data VARCHAR2(8000) := '';
3757 l_stmt_num NUMBER := 0;
3758
3759 l_ctr BINARY_INTEGER;
3760 l_inv_trx_tbl INV_Logical_Transaction_Global.mtl_trx_tbl_type;
3761 l_inv_trx_tbl_ctr BINARY_INTEGER;
3762 l_correct_ind BOOLEAN := FALSE; -- indicator variable for whether these
3763 -- events are for a correction or not
3764 l_rcv_txn_type RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
3765 l_parent_txn_flag NUMBER := 1;
3766 l_intercompany_price NUMBER; -- may include nr tax depending on the pricing option
3767 l_intercompany_curr_code RCV_ACCOUNTING_EVENTS.CURRENCY_CODE%TYPE;
3768 l_transfer_organization_id NUMBER := NULL;
3769
3770 invalid_event EXCEPTION;
3771 BEGIN
3772
3773 -- Standard start of API savepoint
3774 SAVEPOINT Insert_MMTEvents_PVT;
3775
3776 l_stmt_num := 0;
3777
3778 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3779 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.begin'
3780 ,'Insert_MMTEvents <<');
3781 END IF;
3782
3783 -- Standard call to check for call compatibility
3784 IF NOT FND_API.Compatible_API_Call (
3785 l_api_version,
3786 p_api_version,
3787 l_api_name,
3788 G_PKG_NAME ) THEN
3789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3790 END IF;
3791
3792 -- Initialize message list if p_init_msg_list is set to TRUE
3793 IF FND_API.to_Boolean(p_init_msg_list) THEN
3794 FND_MSG_PUB.initialize;
3795 END IF;
3796
3797 -- Initialize API return status to success
3798 x_return_status := FND_API.G_RET_STS_SUCCESS;
3799 x_msg_count := 0;
3800 x_msg_data := '';
3801
3802 -- API Body
3803 -- Initialize counters
3804 l_inv_trx_tbl_ctr := 0;
3805 l_ctr := p_rcv_events_tbl.FIRST;
3806
3807 l_stmt_num := 10;
3808 -- Determine if this group of events are for a CORRECT txn type
3809 SELECT transaction_type
3810 INTO l_rcv_txn_type
3811 FROM rcv_transactions
3812 WHERE transaction_id = p_rcv_events_tbl(l_ctr).rcv_transaction_id;
3813
3814 if (l_rcv_txn_type = 'CORRECT') then
3815 l_correct_ind := TRUE;
3816 end if;
3817
3818 -- Loop for every event in the table
3819 WHILE l_ctr <= p_rcv_events_tbl.LAST LOOP
3820
3821 -- Logical Events are only seeded in Receiving but not in Inventory for :
3822 -- 1. Expense destination types for one-time items
3823 -- 2. Shop Floor destination types (for both OSP and direct items).
3824 IF(p_rcv_events_tbl(l_ctr).destination_type_code <> 'SHOP FLOOR' AND
3825 (p_rcv_events_tbl(l_ctr).destination_type_code <> 'EXPENSE' OR
3826 p_rcv_events_tbl(l_ctr).item_id IS NOT NULL)) THEN
3827
3828 IF (p_rcv_events_tbl(l_ctr).ship_to_org_flag = 'N') then
3829
3830 -- For RAE events, the transfer_organization_id represents the organization from
3831 -- where the transfer price is derived. Hence in the flow :
3832 -- OU2 <-------- OU1 <--------- Supplier
3833 -- The Logical Receive in OU1 will be at PO price and the transfer_org will be NULL.
3834 -- The Recieve in OU2 could be at transfer price between OU1 and OU2. Hence trasnfer
3835 -- org will be OU1.
3836 -- However, in Inventory the Logical Receive in RAE translates to a Logical PO Receipt
3837 -- and a Logical I/C Sales Issue. The Logical I/C Sales event could be at transfer price.
3838 -- The transfer organization should therefore be picked up from the next event. To keep
3839 -- the values for the Logical PO Receipt and the Logical I/C Sales issue consistent, we
3840 -- will follow this logic for both transactions.
3841
3842 l_transfer_organization_id := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).organization_id;
3843
3844 IF (p_rcv_events_tbl(l_ctr).event_type_id = RCV_SeedEvents_PVT.LOGICAL_RECEIVE) THEN
3845
3846 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
3847 IF (p_rcv_events_tbl(l_ctr).intercompany_pricing_option = 2) then
3848 l_intercompany_price := p_rcv_events_tbl(l_ctr).intercompany_price;
3849 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).intercompany_curr_code;
3850 ELSE
3851 l_intercompany_price := p_rcv_events_tbl(l_ctr).unit_price + p_rcv_events_tbl(l_ctr).unit_nr_tax;
3852 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).currency_code;
3853 END IF;
3854
3855 IF (l_correct_ind) THEN
3856 l_stmt_num := 20;
3857 Create_MMTRecord(p_api_version => 1.0,
3858 p_rcv_event => p_rcv_events_tbl(l_ctr),
3859 p_txn_type_id => 69,
3860 p_intercompany_price => l_intercompany_price,
3861 p_intercompany_curr_code => l_intercompany_curr_code,
3862 p_acct_id => p_rcv_events_tbl(l_ctr).debit_account_id,
3863 p_sign => sign(p_rcv_events_tbl(l_ctr).transaction_quantity),
3864 p_parent_txn_flag => l_parent_txn_flag,
3865 p_transfer_organization_id => l_transfer_organization_id,
3866 x_return_status => l_return_status,
3867 x_msg_count => l_msg_count,
3868 x_msg_data => l_msg_data,
3869 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3870 ELSIF (p_rcv_events_tbl(l_ctr).procurement_org_flag = 'Y') THEN
3871 l_stmt_num := 30;
3872 Create_MMTRecord(p_api_version => 1.0,
3873 p_rcv_event => p_rcv_events_tbl(l_ctr),
3874 p_txn_type_id => 19, -- Logical PO Receipt
3875 p_intercompany_price => l_intercompany_price,
3876 p_intercompany_curr_code => l_intercompany_curr_code,
3877 p_acct_id => p_rcv_events_tbl(l_ctr).debit_account_id,
3878 p_sign => 1,
3879 p_parent_txn_flag => l_parent_txn_flag,
3880 p_transfer_organization_id => l_transfer_organization_id,
3881 x_return_status => l_return_status,
3882 x_msg_count => l_msg_count,
3883 x_msg_data => l_msg_data,
3884 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3885 ELSE
3886 l_stmt_num := 40;
3887 Create_MMTRecord(p_api_version => 1.0,
3888 p_rcv_event => p_rcv_events_tbl(l_ctr),
3889 p_txn_type_id => 22, -- Logical I/C Procurement Receipt
3890 p_intercompany_price => l_intercompany_price,
3891 p_intercompany_curr_code => l_intercompany_curr_code,
3892 p_acct_id => p_rcv_events_tbl(l_ctr).debit_account_id,
3893 p_sign => 1,
3894 p_parent_txn_flag => l_parent_txn_flag,
3895 p_transfer_organization_id => l_transfer_organization_id,
3896 x_return_status => l_return_status,
3897 x_msg_count => l_msg_count,
3898 x_msg_data => l_msg_data,
3899 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3900 END IF;
3901
3902 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3903 RAISE FND_API.g_exc_error;
3904 END IF;
3905
3906 l_stmt_num := 50;
3907 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
3908 IF (p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_pricing_option = 2) THEN
3909 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_price;
3910 l_intercompany_curr_code := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_curr_code;
3911 ELSE
3912 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_price +
3913 p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_nr_tax;
3914 l_intercompany_curr_code :=p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).currency_code;
3915 END IF;
3916
3917 IF (p_rcv_events_tbl(l_ctr).transaction_quantity > 0) THEN
3918 l_stmt_num := 60;
3919 Create_MMTRecord(p_api_version => 1.0,
3920 p_rcv_event => p_rcv_events_tbl(l_ctr),
3921 p_txn_type_id => 11, -- Logical I/C Sales Issue
3922 p_intercompany_price => l_intercompany_price,
3923 p_intercompany_curr_code => l_intercompany_curr_code,
3924 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
3925 p_sign => -1,
3926 p_parent_txn_flag => 0,
3927 p_transfer_organization_id => l_transfer_organization_id,
3928 x_return_status => l_return_status,
3929 x_msg_count => l_msg_count,
3930 x_msg_data => l_msg_data,
3931 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3932 ELSE
3933 l_stmt_num := 70;
3934 Create_MMTRecord(p_api_version => 1.0,
3935 p_rcv_event => p_rcv_events_tbl(l_ctr),
3936 p_txn_type_id => 14, -- Logical I/C Sales Return
3937 p_intercompany_price => l_intercompany_price,
3938 p_intercompany_curr_code => l_intercompany_curr_code,
3939 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
3940 p_sign => 1,
3941 p_parent_txn_flag => 0,
3942 p_transfer_organization_id => l_transfer_organization_id,
3943 x_return_status => l_return_status,
3944 x_msg_count => l_msg_count,
3945 x_msg_data => l_msg_data,
3946 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3947 END IF;
3948 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3949 RAISE FND_API.g_exc_error;
3950 END IF;
3951
3952 ELSIF (p_rcv_events_tbl(l_ctr).event_type_id = RCV_SeedEvents_PVT.LOGICAL_RETURN_TO_VENDOR) THEN
3953 l_stmt_num := 80;
3954 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
3955 IF (p_rcv_events_tbl(l_ctr).intercompany_pricing_option = 2) then
3956 l_intercompany_price := p_rcv_events_tbl(l_ctr).intercompany_price;
3957 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).intercompany_curr_code;
3958 ELSE
3959 l_intercompany_price := p_rcv_events_tbl(l_ctr).unit_price + p_rcv_events_tbl(l_ctr).unit_nr_tax;
3960 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).currency_code;
3961 END IF;
3962
3963 IF (l_correct_ind) THEN
3964 l_stmt_num := 90;
3965 Create_MMTRecord(p_api_version => 1.0,
3966 p_rcv_event => p_rcv_events_tbl(l_ctr),
3967 p_txn_type_id => 69,
3968 p_intercompany_price => l_intercompany_price,
3969 p_intercompany_curr_code => l_intercompany_curr_code,
3970 p_acct_id => p_rcv_events_tbl(l_ctr).credit_account_id,
3971 p_sign => -1*sign(p_rcv_events_tbl(l_ctr).transaction_quantity),
3972 p_parent_txn_flag => l_parent_txn_flag,
3973 p_transfer_organization_id => l_transfer_organization_id,
3974 x_return_status => l_return_status,
3975 x_msg_count => l_msg_count,
3976 x_msg_data => l_msg_data,
3977 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3978
3979 ELSIF (p_rcv_events_tbl(l_ctr).procurement_org_flag = 'Y') THEN
3980 l_stmt_num := 100;
3981 Create_MMTRecord(p_api_version => 1.0,
3982 p_rcv_event => p_rcv_events_tbl(l_ctr),
3983 p_txn_type_id => 39, -- Logical RTV
3984 p_intercompany_price => l_intercompany_price,
3985 p_intercompany_curr_code => l_intercompany_curr_code,
3986 p_acct_id => p_rcv_events_tbl(l_ctr).credit_account_id,
3987 p_sign => -1,
3988 p_parent_txn_flag => l_parent_txn_flag,
3989 p_transfer_organization_id => l_transfer_organization_id,
3990 x_return_status => l_return_status,
3991 x_msg_count => l_msg_count,
3992 x_msg_data => l_msg_data,
3993 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3994 ELSE
3995 l_stmt_num := 110;
3996 Create_MMTRecord(p_api_version => 1.0,
3997 p_rcv_event => p_rcv_events_tbl(l_ctr),
3998 p_txn_type_id => 23, -- Logical I/C Procurement Return
3999 p_intercompany_price => l_intercompany_price,
4000 p_intercompany_curr_code => l_intercompany_curr_code,
4001 p_acct_id => p_rcv_events_tbl(l_ctr).credit_account_id,
4002 p_sign => -1,
4003 p_parent_txn_flag => l_parent_txn_flag,
4004 p_transfer_organization_id => l_transfer_organization_id,
4005 x_return_status => l_return_status,
4006 x_msg_count => l_msg_count,
4007 x_msg_data => l_msg_data,
4008 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
4009 END IF;
4010
4011 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4012 RAISE FND_API.g_exc_error;
4013 END IF;
4014
4015 l_stmt_num := 120;
4016 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
4017
4018 IF (p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_pricing_option = 2) then
4019 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_price;
4020 l_intercompany_curr_code := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_curr_code;
4021 ELSE
4022 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_price +
4023 p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_nr_tax;
4024 l_intercompany_curr_code := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).currency_code;
4025 END IF;
4026
4027 IF (p_rcv_events_tbl(l_ctr).transaction_quantity > 0) THEN
4028 l_stmt_num := 130;
4029 Create_MMTRecord(p_api_version => 1.0,
4030 p_rcv_event => p_rcv_events_tbl(l_ctr),
4031 p_txn_type_id => 14, -- Logical I/C Sales Return
4032 p_intercompany_price => l_intercompany_price,
4033 p_intercompany_curr_code => l_intercompany_curr_code,
4034 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
4035 p_sign => 1,
4036 p_parent_txn_flag => 0,
4037 p_transfer_organization_id => l_transfer_organization_id,
4038 x_return_status => l_return_status,
4039 x_msg_count => l_msg_count,
4040 x_msg_data => l_msg_data,
4041 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
4042 else
4043 l_stmt_num := 140;
4044 Create_MMTRecord(p_api_version => 1.0,
4045 p_rcv_event => p_rcv_events_tbl(l_ctr),
4046 p_txn_type_id => 11, -- Logical I/C Sales Issue
4047 p_intercompany_price => l_intercompany_price,
4048 p_intercompany_curr_code => l_intercompany_curr_code,
4049 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
4050 p_sign => -1,
4051 p_parent_txn_flag => 0,
4052 p_transfer_organization_id => l_transfer_organization_id,
4053 x_return_status => l_return_status,
4054 x_msg_count => l_msg_count,
4055 x_msg_data => l_msg_data,
4056 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
4057 END IF;
4058 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4059 RAISE FND_API.g_exc_error;
4060 END IF;
4061
4062 ELSE
4063 RAISE invalid_event;
4064 -- catch error: should never get anything but Log rcpt or Log RTV
4065 END IF;
4066 l_parent_txn_flag := 0; -- the first transaction inserted will be the parent, all others
4067 -- will be children so their flags are 0
4068 END IF;
4069 END IF;
4070
4071 l_ctr := p_rcv_events_tbl.NEXT(l_ctr);
4072 END LOOP;
4073
4074 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4075 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
4076 ,'Creating Logical Transactions in MMT');
4077 END IF;
4078
4079 l_stmt_num := 150;
4080 INV_Logical_Transactions_PUB.Create_Logical_Transactions(
4081 x_return_status => l_return_status,
4082 x_msg_count => l_msg_count,
4083 x_msg_data => l_msg_data,
4084 p_api_version_number => 1.0,
4085 p_mtl_trx_tbl => l_inv_trx_tbl,
4086 p_trx_flow_header_id => p_rcv_events_tbl(p_rcv_events_tbl.FIRST).trx_flow_header_id,
4087 p_defer_logical_transactions => 2,
4088 p_logical_trx_type_code => 3,
4089 p_exploded_flag => 1);
4090 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4091 RAISE FND_API.g_exc_error;
4092 END IF;
4093
4094 -- End API Body
4095
4096 -- Standard check of p_commit
4097 IF FND_API.to_Boolean(p_commit) THEN
4098 COMMIT WORK;
4099 END IF;
4100
4101 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4102 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.end'
4103 ,'Insert_MMTEvents >>');
4104 END IF;
4105
4106 EXCEPTION
4107
4108 WHEN invalid_event THEN
4109 ROLLBACK TO Insert_MMTEvents_PVT;
4110 x_return_status := FND_API.g_ret_sts_unexp_error ;
4111 l_api_message := 'Unexpected event in element '||to_char(l_ctr)||
4112 ' of input parameter p_rcv_events_tbl';
4113 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4114 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4115 l_api_message);
4116 END IF;
4117 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4118 FND_MSG_PUB.add_exc_msg
4119 ( G_PKG_NAME,
4120 l_api_name ||': '|| l_api_message );
4121 END IF;
4122 FND_MSG_PUB.count_and_get
4123 ( p_count => x_msg_count
4124 , p_data => x_msg_data
4125 );
4126
4127 WHEN FND_API.g_exc_error THEN
4128 ROLLBACK TO Insert_MMTEvents_PVT;
4129 x_return_status := FND_API.g_ret_sts_error;
4130 l_api_message := 'Call to procedure failed';
4131 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4132 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4133 l_api_message);
4134 END IF;
4135 FND_MSG_PUB.count_and_get
4136 ( p_count => x_msg_count
4137 , p_data => x_msg_data
4138 );
4139
4140 WHEN FND_API.g_exc_unexpected_error THEN
4141 ROLLBACK TO Insert_MMTEvents_PVT;
4142 x_return_status := FND_API.g_ret_sts_unexp_error;
4143 l_api_message := 'Wrong version #, expecting version '||to_char(l_api_version);
4144 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4145 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4146 l_api_message);
4147 END IF;
4148 FND_MSG_PUB.count_and_get
4149 ( p_count => x_msg_count
4150 , p_data => x_msg_data
4151 );
4152
4153 WHEN OTHERS THEN
4154 ROLLBACK TO Insert_MMTEvents_PVT;
4155 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4156 l_api_message := 'Unexpected Error: '||l_stmt_num||': '||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,100);
4157 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4158 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4159 l_api_message);
4160 END IF;
4161 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4162 FND_MSG_PUB.add_exc_msg
4163 ( G_PKG_NAME,
4164 l_api_name ||': '|| l_api_message );
4165 END IF;
4166 FND_MSG_PUB.count_and_get
4167 ( p_count => x_msg_count
4168 , p_data => x_msg_data
4169 );
4170
4171 END Insert_MMTEvents;
4172
4173
4174 -----------------------------------------------------------------------------
4175 -- Start of comments --
4176 -- --
4177 -- PROCEDURE --
4178 -- Create_MMTRecord This API takes an RAE record along with the --
4179 -- parameters listed above and converts them into a --
4180 -- single MMT record which will be used in a subsequent --
4181 -- function to make the physical insert into MMT --
4182 -- --
4183 -- --
4184 -- VERSION 1.0 --
4185 -- --
4186 -- PARAMETERS --
4187 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
4188 -- P_INIT_MSG_LIST Initialize message list? True/False --
4189 -- P_COMMIT Should the API commit before returning? True/False --
4190 -- P_VALIDATION_LEVEL Specify the level of validation on the inputs --
4191 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
4192 -- X_MSG_COUNT Message Count - # of messages placed in message list--
4193 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
4194 -- P_RCV_EVENT Represents a single RAE, used to build the MMT entry--
4195 -- P_TXN_TYPE_ID Txn Type ID of the new MMT row being created --
4196 -- P_INTERCOMPANY_PRICE The calling fcn must determine how to populate --
4197 -- this based on the txn type and on the OU's position --
4198 -- in the txn flow. It will represent the transfer --
4199 -- price between this OU and an adjacent one. --
4200 -- P_INTERCOMPANY_CURR_CODE This parameter represents the currency code --
4201 -- of the intercompany price. --
4202 -- P_ACCT_ID Used to populate MMT.distribution_account_id --
4203 -- P_SIGN Used to set the signs (+/-) of the primary quantity --
4204 -- and the transaction quantity --
4205 -- P_PARENT_TXN_FLAG 1 - Indicates that this is the parent transaction --
4206 -- P_TRANSFER_ORGANIZATION_ID The calling function should pass the --
4207 -- organization from the next event. --
4208 -- X_INV_TRX Returns the record that will be inserted into MMT --
4209 -- --
4210 -- HISTORY: --
4211 -- 7/21/03 Bryan Kuntz Created --
4212 -- End of comments --
4213 -----------------------------------------------------------------------------
4214 PROCEDURE Create_MMTRecord(
4215 p_api_version IN NUMBER,
4216 p_init_msg_list IN VARCHAR2,
4217 p_commit IN VARCHAR2,
4218 p_validation_level IN NUMBER,
4219 x_return_status OUT NOCOPY VARCHAR2,
4220 x_msg_count OUT NOCOPY NUMBER,
4221 x_msg_data OUT NOCOPY VARCHAR2,
4222 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
4223 p_txn_type_id IN NUMBER,
4224 p_intercompany_price IN NUMBER,
4225 p_intercompany_curr_code IN VARCHAR2,
4226 p_acct_id IN NUMBER,
4227 p_sign IN NUMBER,
4228 p_parent_txn_flag IN NUMBER,
4229 p_transfer_organization_id IN NUMBER,
4230 x_inv_trx OUT NOCOPY INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_rec_type
4231 ) IS
4232 l_api_name CONSTANT VARCHAR2(30) := 'Create_MMTRecord';
4233 l_api_version CONSTANT NUMBER := 1.0;
4234 l_api_message VARCHAR2(1000);
4235
4236 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4237 l_msg_count NUMBER := 0;
4238 l_msg_data VARCHAR2(8000) := '';
4239 l_stmt_num NUMBER := 0;
4240
4241 l_ctr BINARY_INTEGER;
4242 l_unit_price NUMBER;
4243 l_inv_trx INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_rec_type;
4244
4245 l_le_id NUMBER; -- holds legal entity ID for timezone conversion
4246 l_le_txn_date DATE; -- transaction date truncated and converted to legal entity timezone
4247
4248 invalid_txn_type EXCEPTION;
4249 BEGIN
4250
4251 -- Standard start of API savepoint
4252 SAVEPOINT Create_MMTRecord_PVT;
4253
4254 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4255 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||'.'||l_api_name||'.begin'
4256 ,'Create_MMTRecord <<');
4257 END IF;
4258
4259 -- Standard call to check for call compatibility
4260 IF NOT FND_API.Compatible_API_Call (
4261 l_api_version,
4262 p_api_version,
4263 l_api_name,
4264 G_PKG_NAME ) THEN
4265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4266 END IF;
4267
4268 -- Initialize message list if p_init_msg_list is set to TRUE
4269 IF FND_API.to_Boolean(p_init_msg_list) THEN
4270 FND_MSG_PUB.initialize;
4271 END IF;
4272
4273 -- Initialize API return status to success
4274 x_return_status := FND_API.G_RET_STS_SUCCESS;
4275 x_msg_count := 0;
4276 x_msg_data := '';
4277
4278 -- API Body
4279 l_inv_trx.intercompany_pricing_option := p_rcv_event.intercompany_pricing_option;
4280
4281 l_stmt_num := 5;
4282
4283 -- Assign transaction action, source type, and invoiced flag
4284 IF (p_txn_type_id = 11) THEN
4285 l_inv_trx.transaction_action_id := 9;
4286 l_inv_trx.transaction_source_type_id := 13;
4287 l_inv_trx.invoiced_flag := 'N';
4288 ELSIF (p_txn_type_id = 14) THEN
4289 l_inv_trx.transaction_action_id := 14;
4290 l_inv_trx.transaction_source_type_id := 13;
4291 l_inv_trx.invoiced_flag := 'N';
4292 ELSIF (p_txn_type_id = 69) THEN
4293 l_inv_trx.transaction_action_id := 11;
4294 l_inv_trx.transaction_source_type_id := 1;
4295 IF (p_rcv_event.procurement_org_flag = 'Y') THEN
4296 l_inv_trx.invoiced_flag := NULL;
4297 l_inv_trx.intercompany_pricing_option := 1;
4298 ELSE
4299 l_inv_trx.invoiced_flag := 'N';
4300 END IF;
4301 ELSIF (p_txn_type_id = 19) THEN
4302 l_inv_trx.transaction_action_id := 26;
4303 l_inv_trx.transaction_source_type_id := 1;
4304 l_inv_trx.invoiced_flag := NULL;
4305 l_inv_trx.intercompany_pricing_option := 1;
4306 ELSIF (p_txn_type_id = 22) THEN
4307 l_inv_trx.transaction_action_id := 10;
4308 l_inv_trx.transaction_source_type_id := 13;
4309 l_inv_trx.invoiced_flag := 'N';
4310 ELSIF (p_txn_type_id = 23) THEN
4311 l_inv_trx.transaction_action_id := 13;
4312 l_inv_trx.transaction_source_type_id := 13;
4313 l_inv_trx.invoiced_flag := 'N';
4314 ELSIF (p_txn_type_id = 39) THEN
4315 l_inv_trx.transaction_action_id := 7;
4316 l_inv_trx.transaction_source_type_id := 1;
4317 l_inv_trx.invoiced_flag := NULL;
4318 l_inv_trx.intercompany_pricing_option := 1;
4319 ELSE
4320 l_api_message := 'Invalid transaction type';
4321 RAISE invalid_txn_type;
4322 END IF;
4323
4324 -- Set currency columns
4325 l_stmt_num := 20;
4326 IF (p_txn_type_id in (19,39)) THEN
4327 l_inv_trx.currency_code := p_rcv_event.currency_code;
4328 l_inv_trx.currency_conversion_rate := p_rcv_event.currency_conversion_rate;
4329 l_inv_trx.currency_conversion_type := p_rcv_event.currency_conversion_type;
4330 l_inv_trx.currency_conversion_date := sysdate;
4331 ELSE
4332 l_inv_trx.currency_code := NULL;
4333 l_inv_trx.currency_conversion_rate := NULL;
4334 l_inv_trx.currency_conversion_type := NULL;
4335 l_inv_trx.currency_conversion_date := NULL;
4336 END IF;
4337
4338 l_stmt_num := 30;
4339 -- Compute unit price and intercompany price
4340 IF (p_rcv_event.intercompany_pricing_option = 2) THEN
4341 l_unit_price := p_rcv_event.unit_price * p_rcv_event.source_doc_quantity/p_rcv_event.primary_quantity;
4342 ELSE
4343 l_unit_price := (p_rcv_event.unit_price + p_rcv_event.unit_nr_tax) *
4344 p_rcv_event.source_doc_quantity/p_rcv_event.primary_quantity;
4345 END IF;
4346
4347 l_stmt_num := 40;
4348 l_api_message := 'No data';
4349 -- Main select statement to populate the l_inv_trx record
4350 SELECT
4351 p_rcv_event.organization_id,
4352 p_rcv_event.item_id,
4353 p_txn_type_id,
4354 rt.po_header_id,
4355 P_SIGN * ABS(p_rcv_event.transaction_quantity),
4356 p_rcv_event.trx_uom_code,
4357 P_SIGN * ABS(p_rcv_event.primary_quantity),
4358 rt.transaction_date,
4359 decode(nvl(fc.minimum_accountable_unit,0), 0,
4360 round(l_unit_price*p_rcv_event.primary_quantity,fc.precision)*
4361 p_rcv_event.currency_conversion_rate/p_rcv_event.primary_quantity,
4362 round(l_unit_price*
4363 p_rcv_event.primary_quantity/fc.minimum_accountable_unit) *
4364 fc.minimum_accountable_unit*p_rcv_event.currency_conversion_rate/p_rcv_event.primary_quantity),
4365 'RCV',
4366 rt.transaction_id,
4367 rt.transaction_id,
4368 p_transfer_organization_id,
4369 NULL, --pod.project_id, remove these 2 because projects will cause failure in inv's create_logical_txns
4370 NULL, --pod.task_id, since they are only expected values in the org that does the deliver
4371 poll.ship_to_location_id,
4372 1,
4373 p_rcv_event.trx_flow_header_id,
4374 decode(nvl(fc.minimum_accountable_unit,0), 0,
4375 round(p_intercompany_price*p_rcv_event.primary_quantity,fc.precision)
4376 /p_rcv_event.primary_quantity,
4377 round(p_intercompany_price*
4378 p_rcv_event.primary_quantity/fc.minimum_accountable_unit) *
4379 fc.minimum_accountable_unit
4380 /p_rcv_event.primary_quantity),
4381 p_intercompany_curr_code,
4382 p_acct_id,
4383 'N',
4384 NULL,
4385 NULL,
4386 p_parent_txn_flag,
4387 NULL
4388 INTO
4389 l_inv_trx.organization_id,
4390 l_inv_trx.inventory_item_id,
4391 l_inv_trx.transaction_type_id,
4392 l_inv_trx.transaction_source_id,
4393 l_inv_trx.transaction_quantity,
4394 l_inv_trx.transaction_uom,
4395 l_inv_trx.primary_quantity,
4396 l_inv_trx.transaction_date,
4397 l_inv_trx.transaction_cost,
4398 l_inv_trx.source_code,
4399 l_inv_trx.source_line_id,
4400 l_inv_trx.rcv_transaction_id,
4401 l_inv_trx.transfer_organization_id,
4402 l_inv_trx.project_id,
4403 l_inv_trx.task_id,
4404 l_inv_trx.ship_to_location_id,
4405 l_inv_trx.transaction_mode,
4406 l_inv_trx.trx_flow_header_id,
4407 l_inv_trx.intercompany_cost,
4408 l_inv_trx.intercompany_currency_code,
4409 l_inv_trx.distribution_account_id,
4410 l_inv_trx.costed_flag,
4411 l_inv_trx.subinventory_code,
4412 l_inv_trx.locator_id,
4413 l_inv_trx.parent_transaction_flag,
4414 l_inv_trx.trx_source_line_id
4415 FROM rcv_transactions RT,
4416 po_lines POL,
4417 po_line_locations POLL,
4418 po_distributions POD,
4419 fnd_currencies FC
4420 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
4421 AND POL.po_line_id = p_rcv_event.po_line_id
4422 AND POLL.line_location_id = p_rcv_event.po_line_location_id
4423 AND POD.po_distribution_id = p_rcv_event.po_distribution_id
4424 AND FC.currency_code = p_rcv_event.currency_code;
4425
4426 l_stmt_num := 50;
4427 l_api_message := 'Inventory accounting period not open.';
4428 /* get the legal entity for timezone conversion */
4429 SELECT to_number(org_information2)
4430 INTO l_le_id
4431 FROM hr_organization_information
4432 WHERE organization_id = p_rcv_event.organization_id
4433 AND org_information_context = 'Accounting Information';
4434
4435 l_stmt_num := 55;
4436 /* convert the transaction date into legal entity timezone (truncated) */
4437 l_le_txn_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(l_inv_trx.transaction_date, l_le_id);
4438
4439 l_stmt_num := 60;
4440 /* retrieve the accounting period ID */
4441 SELECT acct_period_id
4442 INTO l_inv_trx.acct_period_id
4443 FROM org_acct_periods
4444 WHERE organization_id = p_rcv_event.organization_id
4445 AND l_le_txn_date BETWEEN period_start_date AND schedule_close_date
4446 AND open_flag = 'Y';
4447
4448 /* -- comment out this call for ST bug 3261222
4449 OE_DROP_SHIP_GRP.Get_Drop_Ship_Line_Ids(
4450 p_po_header_id => p_rcv_event.po_header_id,
4451 p_po_line_id => p_rcv_event.po_line_id,
4452 p_po_line_location_id => p_rcv_event.po_line_location_id,
4453 p_po_release_id => l_po_release_id,
4454 x_line_id => l_inv_trx.trx_source_line_id,
4455 x_num_lines => l_so_num_lines,
4456 x_header_id => l_so_header_id,
4457 x_org_id => l_so_org_id);
4458 */
4459
4460
4461 X_INV_TRX := l_inv_trx;
4462 -- ***************
4463
4464 -- Standard check of p_commit
4465 IF FND_API.to_Boolean(p_commit) THEN
4466 COMMIT WORK;
4467 END IF;
4468
4469 -- Standard Call to get message count and if count = 1, get message info
4470 FND_MSG_PUB.Count_And_Get (
4471 p_count => x_msg_count,
4472 p_data => x_msg_data );
4473
4474 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4475 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||'.'||l_api_name||'.end'
4476 ,'Create_MMTRecord >>');
4477 END IF;
4478
4479 EXCEPTION
4480
4481 WHEN FND_API.g_exc_error THEN
4482 ROLLBACK TO Create_MMTRecord_PVT;
4483 x_return_status := FND_API.g_ret_sts_error;
4484 FND_MSG_PUB.count_and_get
4485 ( p_count => x_msg_count
4486 , p_data => x_msg_data
4487 );
4488
4489 WHEN FND_API.g_exc_unexpected_error THEN
4490 ROLLBACK TO Create_MMTRecord_PVT;
4491 x_return_status := FND_API.g_ret_sts_unexp_error ;
4492 l_api_message := 'Unexpected error at statement '||to_char(l_stmt_num);
4493 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4494 FND_MSG_PUB.add_exc_msg
4495 ( G_PKG_NAME,
4496 l_api_name ||': '|| l_api_message );
4497 END IF;
4498 FND_MSG_PUB.count_and_get
4499 ( p_count => x_msg_count
4500 , p_data => x_msg_data
4501 );
4502
4503 WHEN invalid_txn_type THEN
4504 ROLLBACK TO Create_MMTRecord_PVT;
4505 x_return_status := FND_API.g_ret_sts_error ;
4506 l_api_message := 'Unexpected transaction type passed in: '||to_char(p_txn_type_id);
4507 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4508 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4509 l_api_message);
4510 END IF;
4511 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
4512 FND_MSG_PUB.add_exc_msg
4513 ( G_PKG_NAME,
4514 l_api_name ||': '|| l_api_message );
4515 END IF;
4516 FND_MSG_PUB.count_and_get
4517 ( p_count => x_msg_count
4518 , p_data => x_msg_data
4519 );
4520
4521 WHEN NO_DATA_FOUND THEN
4522 ROLLBACK TO Create_MMTRecord_PVT;
4523 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
4524 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4525 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||'.'||l_api_name||'.'||l_stmt_num,
4526 l_api_message);
4527 END IF;
4528 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
4529 THEN
4530 FND_MSG_PUB.add_exc_msg
4531 ( G_PKG_NAME,
4532 l_api_name || l_api_message
4533 );
4534 END IF;
4535 FND_MSG_PUB.count_and_get
4536 ( p_count => x_msg_count
4537 , p_data => x_msg_data
4538 );
4539
4540 WHEN OTHERS THEN
4541 ROLLBACK TO Create_MMTRecord_PVT;
4542 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4543 l_api_message := to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,100);
4544 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4545 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||'.'||l_api_name||'.'||l_stmt_num
4546 ,'Create_MMTRecord : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
4547 END IF;
4548 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
4549 THEN
4550 FND_MSG_PUB.add_exc_msg
4551 ( G_PKG_NAME,
4552 l_api_name ||'('||to_char(l_stmt_num)||') - ' || l_api_message
4553 );
4554 END IF;
4555 FND_MSG_PUB.count_and_get
4556 ( p_count => x_msg_count
4557 , p_data => x_msg_data
4558 );
4559
4560 END Create_MMTRecord;
4561
4562 END RCV_SeedEvents_PVT;