[Home] [Help]
PACKAGE BODY: APPS.RCV_SEEDEVENTS_PVT
Source
1 PACKAGE BODY RCV_SeedEvents_PVT AS
2 /* $Header: RCVVRUTB.pls 120.10.12010000.3 2008/11/10 14:48:11 mpuranik 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 l_source_doc_quantity := l_po_quantity_ordered - l_po_quantity_delivered;
1121 END IF;
1122 ELSIF(l_rcv_txn_type = 'RETURN TO RECEIVING' OR
1123 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'DELIVER'
1124 AND l_source_doc_quantity < 0) OR
1125 (l_rcv_txn_type = 'CORRECT' AND l_par_rcv_txn_type = 'RETURN TO RECEIVING'
1126 AND l_source_doc_quantity > 0)) THEN
1127
1128 l_po_quantity_delivered := l_po_quantity_delivered + l_abs_rt_quantity;
1129 IF (l_po_quantity_delivered <= l_po_quantity_ordered) THEN
1130 NULL; -- l_source_doc_quantity already holds the correct value
1131 ELSIF(l_po_quantity_delivered - l_abs_rt_quantity > l_po_quantity_ordered) THEN
1132 l_source_doc_quantity := 0;
1133 ELSE
1134 --{BUG#FP:6815401-6683404
1135 --l_source_doc_quantity := l_abs_rt_quantity - (l_po_quantity_delivered - l_po_quantity_ordered);
1136 l_source_doc_quantity :=-1*( l_abs_rt_quantity - (l_po_quantity_delivered - l_po_quantity_ordered));
1137 --}
1138 END IF;
1139 END IF;
1140 END IF;
1141
1142 x_source_doc_quantity := l_source_doc_quantity;
1143
1144 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1145 l_api_message := 'x_source_doc_quantity : '||x_source_doc_quantity;
1146 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1147 ,l_api_message);
1148 END IF;
1149
1150
1151 --- Standard check of p_commit
1152 IF FND_API.to_Boolean(p_commit) THEN
1153 COMMIT WORK;
1154 END IF;
1155
1156 -- Standard Call to get message count and if count = 1, get message info
1157 FND_MSG_PUB.Count_And_Get (
1158 p_count => x_msg_count,
1159 p_data => x_msg_data );
1160
1161 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1162 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
1163 ,'Get_Quantity >>');
1164 END IF;
1165 EXCEPTION
1166 WHEN FND_API.g_exc_error THEN
1167 ROLLBACK TO Get_Quantity_PVT;
1168 x_return_status := FND_API.g_ret_sts_error;
1169 FND_MSG_PUB.count_and_get
1170 ( p_count => x_msg_count
1171 , p_data => x_msg_data
1172 );
1173 WHEN FND_API.g_exc_unexpected_error THEN
1174 ROLLBACK TO Get_Quantity_PVT;
1175 x_return_status := FND_API.g_ret_sts_unexp_error ;
1176 FND_MSG_PUB.count_and_get
1177 ( p_count => x_msg_count
1178 , p_data => x_msg_data
1179 );
1180
1181 WHEN OTHERS THEN
1182 ROLLBACK TO Get_Quantity_PVT;
1183 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1184
1185 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1186 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1187 ,'Get_Quantity : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1188 END IF;
1189
1190 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1191 THEN
1192 FND_MSG_PUB.add_exc_msg
1193 ( G_PKG_NAME,
1194 l_api_name || 'Statement -'||to_char(l_stmt_num)
1195 );
1196 END IF;
1197 FND_MSG_PUB.count_and_get
1198 ( p_count => x_msg_count
1199 , p_data => x_msg_data
1200 );
1201
1202 END Get_Quantity;
1203
1204
1205 -- Start of comments
1206 -- API name : Get_UnitPrice
1207 -- Type : Private
1208 -- Function : Returns the Unit Price. Used for non-service line types.
1209 -- Pre-reqs :
1210 -- Parameters :
1211 -- IN : p_api_version IN NUMBER Required
1212 -- p_init_msg_list IN VARCHAR2 Optional
1213 -- Default = FND_API.G_FALSE
1214 -- p_commit IN VARCHAR2 Optional
1215 -- Default = FND_API.G_FALSE
1216 -- p_validation_level IN NUMBER Optional
1217 -- Default = FND_API.G_VALID_LEVEL_FULL
1218 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
1219 -- p_lcm_flag IN VARCHAR2 Optional
1220 --
1221 -- OUT : x_return_status OUT VARCHAR2(1)
1222 -- x_msg_count OUT NUMBER
1223 -- x_msg_data OUT VARCHAR2(2000)
1224 -- x_intercompany_pricing_option OUT NUMBER
1225 -- x_unit_price OUT NUMBER
1226 -- x_currency_code OUT VARCHAR2(15)
1227 -- x_incr_transfer_price OUT NUMBER
1228 -- x_incr_currency_code OUT VARCHAR2(15)
1229 -- Version :
1230 -- Initial version 1.0
1231 --
1232 --
1233 -- Notes : This API returns the unit price. It should only be called for non service line types.
1234 --
1235 -- End of comments
1236 PROCEDURE Get_UnitPrice(
1237 p_api_version IN NUMBER,
1238 p_init_msg_list IN VARCHAR2,
1239 p_commit IN VARCHAR2,
1240 p_validation_level IN NUMBER,
1241 x_return_status OUT NOCOPY VARCHAR2,
1242 x_msg_count OUT NOCOPY NUMBER,
1243 x_msg_data OUT NOCOPY VARCHAR2,
1244 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
1245 p_asset_item_pricing_option IN NUMBER,
1246 p_expense_item_pricing_option IN NUMBER,
1247 /* Support for Landed Cost Management */
1248 p_lcm_flag IN VARCHAR2,
1249 x_intercompany_pricing_option OUT NOCOPY NUMBER,
1250 x_unit_price OUT NOCOPY NUMBER,
1251 /* Support for Landed Cost Management */
1252 x_unit_landed_cost OUT NOCOPY NUMBER,
1253 x_currency_code OUT NOCOPY VARCHAR2,
1254 x_incr_transfer_price OUT NOCOPY NUMBER,
1255 x_incr_currency_code OUT NOCOPY VARCHAR2
1256
1257 ) IS
1258 l_api_name CONSTANT VARCHAR2(30) := 'Get_UnitPrice';
1259 l_api_version CONSTANT NUMBER := 1.0;
1260
1261 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1262 l_msg_count NUMBER := 0;
1263 l_msg_data VARCHAR2(8000) := '';
1264 l_stmt_num NUMBER := 0;
1265 l_api_message VARCHAR2(1000);
1266
1267 l_asset_flag VARCHAR2(1);
1268 l_ic_pricing_option NUMBER := 1;
1269 l_transfer_price NUMBER;
1270 l_unit_price NUMBER;
1271 l_transaction_uom VARCHAR2(3);
1272 l_currency_code RCV_ACCOUNTING_EVENTS.CURRENCY_CODE%TYPE;
1273 l_item_exists NUMBER;
1274 l_from_organization_id NUMBER;
1275 l_from_org_id NUMBER;
1276 l_to_org_id NUMBER;
1277
1278 l_incr_currency_code RCV_ACCOUNTING_EVENTS.CURRENCY_CODE%TYPE;
1279 l_incr_transfer_price NUMBER;
1280
1281 BEGIN
1282 -- Standard start of API savepoint
1283 SAVEPOINT Get_UnitPrice_PVT;
1284
1285 l_stmt_num := 0;
1286
1287 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1288 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
1289 ,'Get_UnitPrice <<');
1290 END IF;
1291
1292 -- Standard call to check for call compatibility
1293 IF NOT FND_API.Compatible_API_Call (
1294 l_api_version,
1295 p_api_version,
1296 l_api_name,
1297 G_PKG_NAME ) THEN
1298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1299 END IF;
1300
1301 -- Initialize message list if p_init_msg_list is set to TRUE
1302 IF FND_API.to_Boolean(p_init_msg_list) THEN
1303 FND_MSG_PUB.initialize;
1304 END IF;
1305
1306 -- Initialize API return status to success
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308 x_incr_transfer_price := 0;
1309 x_incr_currency_code := NULL;
1310 /* Support for Landed Cost Management */
1311 x_unit_landed_cost := NULL;
1312 l_currency_code := p_rcv_event.currency_code;
1313
1314 l_stmt_num := 10;
1315 -- Always use PO price if :
1316 -- 1. No transaction flow exists or
1317 -- 2. Destination type is Shopfloor.
1318 -- 3. If it is the procurement org
1319 -- 4. The PO is for a one-time item.
1320
1321 IF(p_rcv_event.trx_flow_header_id IS NULL OR
1322 p_rcv_event.item_id IS NULL OR
1323 p_rcv_event.destination_type_code = 'SHOP FLOOR' OR
1324 (p_rcv_event.procurement_org_flag = 'Y' AND
1325 p_rcv_event.event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
1326 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL))
1327 ) THEN
1328 l_ic_pricing_option := 1;
1329
1330 ELSE
1331
1332 -- Pricing Option on the Transaction Flow form will determine whether to use
1333 -- PO price or Transfer price.
1334 BEGIN
1335 -- Verify that item exists in organization where event is being created.
1336 l_stmt_num := 30;
1337 SELECT count(*)
1338 INTO l_item_exists
1339 FROM mtl_system_items MSI
1340 WHERE MSI.inventory_item_id = p_rcv_event.item_id
1341 AND MSI.organization_id = p_rcv_event.organization_id;
1342
1343 IF(l_item_exists = 0) THEN
1344 FND_MESSAGE.set_name('PO','PO_INVALID_ITEM');
1345 FND_MSG_pub.add;
1346 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1347 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
1348 END IF;
1349 RAISE FND_API.g_exc_error;
1350 END IF;
1351
1352 -- Use Inventory Asset Flag in the organization where the physical event occurred. This
1353 -- would be the ship to organization id. Using POLL.ship_to_organization_id so it will be
1354 -- available for both Invoice Match and Receiving events.
1355 l_stmt_num := 40;
1356 SELECT MSI.inventory_asset_flag
1357 INTO l_asset_flag
1358 FROM mtl_system_items MSI,
1359 po_line_locations POLL
1360 WHERE MSI.inventory_item_id = p_rcv_event.item_id
1361 AND MSI.organization_id = POLL.ship_to_organization_id
1362 AND POLL.line_location_id = p_rcv_event.po_line_location_id;
1363 EXCEPTION
1364 WHEN NO_DATA_FOUND THEN
1365 FND_MESSAGE.set_name('PO','PO_INVALID_ITEM');
1366 FND_MSG_pub.add;
1367 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1368 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
1369 END IF;
1370 RAISE FND_API.g_exc_error;
1371 END;
1372
1373 IF(l_asset_flag = 'Y') THEN
1374 l_ic_pricing_option := p_asset_item_pricing_option;
1375 ELSE
1376 l_ic_pricing_option := p_expense_item_pricing_option;
1377 END IF;
1378
1379 END IF;
1380
1381 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1382 l_api_message := 'l_ic_pricing_option : '||l_ic_pricing_option;
1383 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1384 ,l_api_message);
1385 END IF;
1386
1387 -- l_ic_pricing_option of 1 => PO Price.
1388 -- l_ic_pricing_option of 2 => Transfer Price.
1389 IF(l_ic_pricing_option = 2) THEN
1390 l_stmt_num := 50;
1391 -- The l_ic_pricing_option can only be 2 for a source type of 'RECEIVING'.
1392 -- Get the UOM of the source_doc since unit price is desired in Document's UOM
1393 SELECT MUOM.uom_code
1394 INTO l_transaction_uom
1395 FROM rcv_transactions RT, mtl_units_of_measure MUOM
1396 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
1397 AND MUOM.unit_of_measure = RT.source_doc_unit_of_measure;
1398
1399 -- While calling the transfer pricing API, the from organization id should be
1400 -- passed. For Intercompany events, the from organization id is the same as
1401 -- organization_id on the event. For the remaining events, the from organization
1402 -- is the transfer_organization_id on the event.
1403
1404 IF(p_rcv_event.event_type_id IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
1405 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
1406 l_from_organization_id := p_rcv_event.organization_id;
1407 l_from_org_id := p_rcv_event.org_id;
1408 l_to_org_id := p_rcv_event.transfer_org_id;
1409 ELSE
1410 l_from_organization_id := p_rcv_event.transfer_organization_id;
1411 l_from_org_id := p_rcv_event.transfer_org_id;
1412 l_to_org_id := p_rcv_event.org_id;
1413 END IF;
1414
1415
1416 -- Alcoa enhancement. Users will be given the option to determine in which
1417 -- currency intercompany invoices should be created. The get_transfer_price
1418 -- API will return the transfer price in the selling OU currency as well in the
1419 -- currency chosen by the user. The returned values will have to be stored
1420 -- in MMT and will be used by Intercompany to determine the Currency in which
1421 -- to create the intercompany invoices.
1422 l_stmt_num := 60;
1423 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1424 l_api_message := 'Calling get_transfer_price API : '||
1425 ' l_from_org_id : '||l_from_org_id||
1426 ' l_to_org_id : '||l_to_org_id||
1427 ' l_transaction_uom : '||l_transaction_uom||
1428 ' item_id : '||p_rcv_event.item_id||
1429 ' p_transaction_id : '|| p_rcv_event.rcv_transaction_id;
1430
1431 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1432 ,l_api_message);
1433 END IF;
1434 INV_TRANSACTION_FLOW_PUB.get_transfer_price(
1435 p_api_version => 1.0,
1436 x_return_status => l_return_status,
1437 x_msg_data => l_msg_data,
1438 x_msg_count => l_msg_count,
1439 x_transfer_price => l_transfer_price,
1440 x_currency_code => l_currency_code,
1441 x_incr_transfer_price => l_incr_transfer_price,
1442 x_incr_currency_code => l_incr_currency_code,
1443 p_from_org_id => l_from_org_id,
1444 p_to_org_id => l_to_org_id,
1445 p_transaction_uom => l_transaction_uom,
1446 p_inventory_item_id => p_rcv_event.item_id,
1447 p_transaction_id => p_rcv_event.rcv_transaction_id,
1448 p_from_organization_id => l_from_organization_id,
1449 p_global_procurement_flag => 'Y',
1450 p_drop_ship_flag => 'N');
1451 IF l_return_status <> FND_API.g_ret_sts_success THEN
1452 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1453 l_api_message := 'Error getting transfer price';
1454 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1455 ,l_api_message);
1456 END IF;
1457 RAISE FND_API.g_exc_unexpected_error;
1458 END IF;
1459
1460 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1461 l_api_message := SUBSTR('l_transfer_price : ' || l_transfer_price||
1462 ' l_currency_code : '||l_currency_code||
1463 ' l_incr_transfer_price : '||l_incr_transfer_price||
1464 ' l_incr_currency_code : '||l_incr_currency_code,1000);
1465 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1466 ,'Get_TransferPrice : '||l_stmt_num||' : '||l_api_message);
1467 END IF;
1468
1469 l_unit_price := l_transfer_price;
1470 x_incr_transfer_price := l_incr_transfer_price;
1471 x_incr_currency_code := l_incr_currency_code;
1472
1473 ELSIF (p_rcv_event.event_source = 'RECEIVING' OR p_rcv_event.event_source = 'RETROPRICE') THEN
1474 l_stmt_num := 70;
1475 SELECT POLL.price_override
1476 INTO l_unit_price
1477 FROM po_line_locations POLL
1478 WHERE POLL.line_location_id = p_rcv_event.po_line_location_id;
1479
1480 /* Support for Landed Cost Management */
1481 IF (p_rcv_event.event_source = 'RECEIVING' AND p_lcm_flag = 'Y') THEN
1482 SELECT unit_landed_cost
1483 INTO x_unit_landed_cost
1484 FROM rcv_transactions
1485 WHERE transaction_id = p_rcv_event.rcv_transaction_id;
1486
1487 END IF;
1488
1489 ELSIF (p_rcv_event.event_source = 'INVOICEMATCH') THEN
1490 l_stmt_num := 80;
1491 SELECT APID.unit_price
1492 INTO l_unit_price
1493 FROM ap_invoice_distributions APID
1494 WHERE APID.invoice_distribution_id = p_rcv_event.inv_distribution_id;
1495 END IF;
1496
1497 x_intercompany_pricing_option := l_ic_pricing_option;
1498 x_unit_price := l_unit_price;
1499 x_currency_code := l_currency_code;
1500
1501 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1502 l_api_message := SUBSTR('x_ic_pricing_option : '||x_intercompany_pricing_option||
1503 ' x_unit_price : '||x_unit_price ||
1504 ' x_currency_code : '||x_currency_code||
1505 ' x_incr_currency_code : '||x_incr_currency_code||
1506 ' x_incr_transfer_price : '||x_incr_transfer_price,1,1000);
1507 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1508 ,l_api_message);
1509 END IF;
1510
1511
1512
1513 -- Standard check of p_commit
1514 IF FND_API.to_Boolean(p_commit) THEN
1515 COMMIT WORK;
1516 END IF;
1517
1518 -- Standard Call to get message count and if count = 1, get message info
1519 FND_MSG_PUB.Count_And_Get (
1520 p_count => x_msg_count,
1521 p_data => x_msg_data );
1522
1523 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1524 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
1525 ,'Get_UnitPrice >>');
1526 END IF;
1527 EXCEPTION
1528 WHEN FND_API.g_exc_error THEN
1529 ROLLBACK TO Get_UnitPrice_PVT;
1530 x_return_status := FND_API.g_ret_sts_error;
1531 FND_MSG_PUB.count_and_get
1532 ( p_count => x_msg_count
1533 , p_data => x_msg_data
1534 );
1535 WHEN FND_API.g_exc_unexpected_error THEN
1536 ROLLBACK TO Get_UnitPrice_PVT;
1537 x_return_status := FND_API.g_ret_sts_unexp_error ;
1538 FND_MSG_PUB.count_and_get
1539 ( p_count => x_msg_count
1540 , p_data => x_msg_data
1541 );
1542
1543 WHEN OTHERS THEN
1544 ROLLBACK TO Get_UnitPrice_PVT;
1545 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1546
1547 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1548 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1549 ,'Get_UnitPrice : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1550 END IF;
1551
1552 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1553 THEN
1554 FND_MSG_PUB.add_exc_msg
1555 ( G_PKG_NAME,
1556 l_api_name || 'Statement -'||to_char(l_stmt_num)
1557 );
1558 END IF;
1559 FND_MSG_PUB.count_and_get
1560 ( p_count => x_msg_count
1561 , p_data => x_msg_data
1562 );
1563 END Get_UnitPrice;
1564
1565 -- Start of comments
1566 -- API name : Get_UnitTax
1567 -- Type : Private
1568 -- Function : Returns the recoverable and non-recoverable tax.
1569 -- Pre-reqs :
1570 -- Parameters :
1571 -- IN : p_api_version IN NUMBER Required
1572 -- p_init_msg_list IN VARCHAR2 Optional
1573 -- Default = FND_API.G_FALSE
1574 -- p_commit IN VARCHAR2 Optional
1575 -- Default = FND_API.G_FALSE
1576 -- p_validation_level IN NUMBER Optional
1577 -- Default = FND_API.G_VALID_LEVEL_FULL
1578 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
1579 --
1580 -- OUT : x_return_status OUT VARCHAR2(1)
1581 -- x_msg_count OUT NUMBER
1582 -- x_msg_data OUT VARCHAR2(2000)
1583 -- x_unit_nr_tax OUT NUMBER
1584 -- x_unit_rec_tax OUT NUMBER
1585 -- x_prior_nr_tax OUT NUMBER
1586 -- x_prior_rec_tax OUT NUMBER
1587 -- Version :
1588 -- Initial version 1.0
1589 --
1590 --
1591 -- Notes : This API returns the tax information.
1592 --
1593 -- End of comments
1594 PROCEDURE Get_UnitTax(
1595 p_api_version IN NUMBER,
1596 p_init_msg_list IN VARCHAR2,
1597 p_commit IN VARCHAR2,
1598 p_validation_level IN NUMBER,
1599 x_return_status OUT NOCOPY VARCHAR2,
1600 x_msg_count OUT NOCOPY NUMBER,
1601 x_msg_data OUT NOCOPY VARCHAR2,
1602
1603 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
1604 x_unit_nr_tax OUT NOCOPY NUMBER,
1605 x_unit_rec_tax OUT NOCOPY NUMBER,
1606 x_prior_nr_tax OUT NOCOPY NUMBER,
1607 x_prior_rec_tax OUT NOCOPY NUMBER
1608
1609
1610 ) IS
1611 l_api_name CONSTANT VARCHAR2(30) := 'Get_UnitTax';
1612 l_api_version CONSTANT NUMBER := 1.0;
1613
1614 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1615 l_msg_count NUMBER := 0;
1616 l_msg_data VARCHAR2(8000) := '';
1617 l_stmt_num NUMBER := 0;
1618 l_api_message VARCHAR2(1000);
1619
1620 l_unit_nr_tax NUMBER := 0;
1621 l_unit_rec_tax NUMBER := 0;
1622 l_prior_nr_tax NUMBER := 0;
1623 l_prior_rec_tax NUMBER := 0;
1624
1625 l_recoverable_tax NUMBER := 0;
1626 l_non_recoverable_tax NUMBER := 0;
1627 l_old_recoverable_tax NUMBER := 0;
1628 l_old_non_recoverable_tax NUMBER := 0;
1629
1630 l_hook_used NUMBER;
1631 l_loc_non_recoverable_tax NUMBER;
1632 l_loc_recoverable_tax NUMBER;
1633 l_err_num NUMBER;
1634 l_err_code VARCHAR2(240);
1635 l_err_msg VARCHAR2(240);
1636
1637 BEGIN
1638 -- Standard start of API savepoint
1639 SAVEPOINT Get_UnitTax_PVT;
1640
1641 l_stmt_num := 0;
1642 l_hook_used:= 0;
1643
1644 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1645 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
1646 ,'Get_UnitTax <<');
1647 END IF;
1648
1649 -- Standard call to check for call compatibility
1650 IF NOT FND_API.Compatible_API_Call (
1651 l_api_version,
1652 p_api_version,
1653 l_api_name,
1654 G_PKG_NAME ) THEN
1655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1656 END IF;
1657
1658 -- Initialize message list if p_init_msg_list is set to TRUE
1659 IF FND_API.to_Boolean(p_init_msg_list) THEN
1660 FND_MSG_PUB.initialize;
1661 END IF;
1662
1663 -- Initialize API return status to success
1664 x_return_status := FND_API.G_RET_STS_SUCCESS;
1665 x_unit_nr_tax := 0;
1666 x_unit_rec_tax := 0;
1667 x_prior_nr_tax := 0;
1668 x_prior_rec_tax := 0;
1669
1670
1671 l_stmt_num := 10;
1672
1673 -- No tax is applicable if pricing option is transfer price.
1674 IF(p_rcv_event.intercompany_pricing_option = 2) THEN
1675 return;
1676 END IF;
1677
1678 IF (p_rcv_event.event_source = 'RECEIVING' OR p_rcv_event.event_source = 'RETROPRICE') THEN
1679 l_stmt_num := 20;
1680
1681 -- Call PO API to get current an prior receoverable and non-recoverable tax
1682 PO_TAX_SV.Get_All_PO_Tax(
1683 p_api_version => l_api_version,
1684 x_return_status => l_return_status,
1685 x_msg_data => l_msg_data,
1686 p_distribution_id => p_rcv_event.po_distribution_id,
1687 x_recoverable_tax => l_recoverable_tax,
1688 x_non_recoverable_tax => l_non_recoverable_tax,
1689 x_old_recoverable_tax => l_old_recoverable_tax,
1690 x_old_non_recoverable_tax => l_old_non_recoverable_tax);
1691
1692 IF l_return_status <> FND_API.g_ret_sts_success THEN
1693 l_api_message := 'Error getting Tax';
1694 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1695 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1696 ,'Get_UnitPrice : '||l_stmt_num||' : '||l_api_message);
1697 END IF;
1698 RAISE FND_API.g_exc_unexpected_error;
1699 END IF;
1700
1701 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1702 l_api_message := SUBSTR('l_recoverable_tax : '||l_recoverable_tax||
1703 ' l_non_recoverable_tax : '||l_non_recoverable_tax||
1704 ' l_old_recoverable_tax : '||l_old_recoverable_tax||
1705 ' l_old_non_recoverable_tax : '||l_old_non_recoverable_tax,1,1000);
1706 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1707 ,l_api_message);
1708 END IF;
1709
1710 /* Bug 6405593 :Added hook call to override the recoverable and Non-Recoverable
1711 taxes for ENCUMBRANCE_REVERSAL event */
1712
1713 IF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL) THEN
1714 l_stmt_num := 25;
1715 l_hook_used := CST_Common_hooks.Get_NRtax_amount(
1716 I_ACCT_TXN_ID =>p_rcv_event.rcv_transaction_id,
1717 I_SOURCE_DOC_TYPE =>'PO',
1718 I_SOURCE_DOC_ID =>p_rcv_event.po_distribution_id,
1719 I_ACCT_SOURCE =>'RCV',
1720 I_USER_ID =>fnd_global.user_id,
1721 I_LOGIN_ID =>fnd_global.login_id,
1722 I_REQ_ID =>fnd_global.conc_request_id,
1723 I_PRG_APPL_ID =>fnd_global.prog_appl_id,
1724 I_PRG_ID =>fnd_global.conc_program_id,
1725 O_DOC_NR_TAX =>l_loc_non_recoverable_tax,
1726 O_DOC_REC_TAX =>l_loc_recoverable_tax,
1727 O_Err_Num =>l_Err_Num,
1728 O_Err_Code =>l_Err_Code,
1729 O_Err_Msg =>l_Err_Msg
1730 );
1731 IF l_hook_used <>0 THEN
1732
1733 IF (l_err_num <> 0) THEN
1734 -- Error occured
1735 l_api_message := 'Error getting Enc Tax error_code : '||l_Err_Code||' Error Message : '||l_Err_Msg;
1736 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1737 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1738 ,'CST_Common_hooks.Get_NRtax_amount : '||l_stmt_num||' : '||l_api_message);
1739 END IF;
1740 RAISE FND_API.g_exc_unexpected_error;
1741 END IF;
1742
1743
1744 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1745 l_api_message := SUBSTR('Hook Used CST_Commonlocalization_hooks.Get_NRtax_amount :'|| l_hook_used ||
1746 ' l_loc_recoverable_tax : '||l_loc_recoverable_tax||
1747 ' l_loc_non_recoverable_tax : '||l_loc_non_recoverable_tax,1,1000);
1748 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1749 ,l_api_message);
1750 END IF;
1751
1752 l_non_recoverable_tax:=nvl(l_non_recoverable_tax,0)+nvl(l_loc_non_recoverable_tax,0);
1753 l_recoverable_tax :=nvl(l_recoverable_tax,0)+nvl(l_loc_recoverable_tax,0);
1754
1755 END IF;
1756 END IF;
1757 /* Bug 6405593 :Added hook call to override the recoverable and Non-Recoverable
1758 taxes for ENCUMBRANCE_REVERSAL event */
1759
1760 IF(p_rcv_event.service_flag = 'Y') THEN
1761 l_stmt_num := 30;
1762 SELECT l_non_recoverable_tax/POD.amount_ordered,
1763 l_recoverable_tax/POD.amount_ordered
1764 INTO l_unit_nr_tax,
1765 l_unit_rec_tax
1766 FROM po_distributions POD
1767 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
1768 ELSE
1769 l_stmt_num := 40;
1770 SELECT l_non_recoverable_tax/POD.quantity_ordered,
1771 l_recoverable_tax/POD.quantity_ordered
1772 INTO l_unit_nr_tax,
1773 l_unit_rec_tax
1774 FROM po_distributions POD
1775 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
1776 END IF;
1777 END IF;
1778
1779 IF (p_rcv_event.event_source = 'RETROPRICE') THEN
1780 l_stmt_num := 50;
1781 SELECT l_old_non_recoverable_tax/POD.quantity_ordered,
1782 l_old_recoverable_tax/POD.quantity_ordered
1783 INTO l_prior_nr_tax,
1784 l_prior_rec_tax
1785 FROM po_distributions POD
1786 WHERE po_distribution_id = p_rcv_event.po_distribution_id;
1787 END IF;
1788
1789 x_unit_nr_tax := NVL(l_unit_nr_tax,0);
1790 x_unit_rec_tax := NVL(l_unit_rec_tax,0);
1791 x_prior_nr_tax := NVL(l_prior_nr_tax,0);
1792 x_prior_rec_tax := NVL(l_prior_rec_tax,0);
1793
1794 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1795 l_api_message := SUBSTR('x_unit_nr_tax : '||x_unit_nr_tax||
1796 ' x_unit_rec_tax : '||x_unit_rec_tax||
1797 ' x_prior_nr_tax : '||x_prior_nr_tax||
1798 ' x_prior_rec_tax : '||x_prior_rec_tax,1,1000);
1799 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
1800 ,l_api_message);
1801 END IF;
1802
1803
1804 --- Standard check of p_commit
1805 IF FND_API.to_Boolean(p_commit) THEN
1806 COMMIT WORK;
1807 END IF;
1808
1809 -- Standard Call to get message count and if count = 1, get message info
1810 FND_MSG_PUB.Count_And_Get (
1811 p_count => x_msg_count,
1812 p_data => x_msg_data );
1813
1814 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1815 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
1816 ,'Get_UnitTax >>');
1817 END IF;
1818
1819
1820 EXCEPTION
1821 WHEN FND_API.g_exc_error THEN
1822 ROLLBACK TO Get_UnitTax_PVT;
1823 x_return_status := FND_API.g_ret_sts_error;
1824 FND_MSG_PUB.count_and_get
1825 ( p_count => x_msg_count
1826 , p_data => x_msg_data
1827 );
1828 WHEN FND_API.g_exc_unexpected_error THEN
1829 ROLLBACK TO Get_UnitTax_PVT;
1830 x_return_status := FND_API.g_ret_sts_unexp_error ;
1831 FND_MSG_PUB.count_and_get
1832 ( p_count => x_msg_count
1833 , p_data => x_msg_data
1834 );
1835
1836 WHEN OTHERS THEN
1837 ROLLBACK TO Get_UnitTax_PVT;
1838 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1839
1840 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1841 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
1842 ,'Get_UnitTax : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
1843 END IF;
1844
1845 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1846 THEN
1847 FND_MSG_PUB.add_exc_msg
1848 ( G_PKG_NAME,
1849 l_api_name || 'Statement -'||to_char(l_stmt_num)
1850 );
1851 END IF;
1852 FND_MSG_PUB.count_and_get
1853 ( p_count => x_msg_count
1854 , p_data => x_msg_data
1855 );
1856 END Get_UnitTax;
1857
1858 -----------------------------------------------------------------------------
1859 -- Start of comments --
1860 -- --
1861 -- PROCEDURE --
1862 -- Convert_UOM This function updates the record type variable --
1863 -- that is passed to it. It inserts the UOM into the --
1864 -- primary_uom field, then it updates the primary_ --
1865 -- quantity with the transaction_quantity converted to --
1866 -- the new UOM and it updates the unit_price by --
1867 -- converting it with the new UOM. --
1868 -- --
1869 -- Because there are already other modules under PO_TOP --
1870 -- that use the inv_convert package, we can safely use --
1871 -- it here without introducing new dependencies on that --
1872 -- product. --
1873 -- --
1874 -- VERSION 1.0 --
1875 -- --
1876 -- PARAMETERS --
1877 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
1878 -- P_INIT_MSG_LIST Initialize message list? True/False --
1879 -- P_COMMIT Should the API commit before returning? True/False --
1880 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
1881 -- X_MSG_COUNT Message Count - # of messages placed in message list--
1882 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
1883 -- P_EVENT_REC Record storing an RCV Accounting Event (RAE) --
1884 -- X_TRANSACTION_QTY Transaction quantity converted from source doc qty --
1885 -- X_PRIMARY_UOM Converted UOM --
1886 -- X_PRIMARY_QTY Primary quantity converted from source doc qty --
1887 -- X_TRX_UOM_CODE Transaction UOM --
1888 -- --
1889 -- HISTORY: --
1890 -- 06/26/03 Bryan Kuntz Created --
1891 -- End of comments --
1892 -----------------------------------------------------------------------------
1893
1894 PROCEDURE Convert_UOM (
1895 P_API_VERSION IN NUMBER,
1896 P_INIT_MSG_LIST IN VARCHAR2,
1897 P_COMMIT IN VARCHAR2,
1898 P_VALIDATION_LEVEL IN NUMBER, -- := FND_API.G_VALID_LEVEL_FULL,
1899 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1900 X_MSG_COUNT OUT NOCOPY NUMBER,
1901 X_MSG_DATA OUT NOCOPY VARCHAR2,
1902 P_EVENT_REC IN RCV_SeedEvents_PVT.rcv_event_rec_type,
1903 X_TRANSACTION_QTY OUT NOCOPY NUMBER,
1904 X_PRIMARY_UOM OUT NOCOPY MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE,
1905 X_PRIMARY_QTY OUT NOCOPY NUMBER,
1906 X_TRX_UOM_CODE OUT NOCOPY VARCHAR2
1907 ) IS
1908
1909 -- local control variables
1910 l_api_name CONSTANT VARCHAR2(30) := 'Convert_UOM';
1911 l_api_version CONSTANT NUMBER := 1.0;
1912 l_stmt_num number := 0;
1913 l_api_message VARCHAR2(1000);
1914
1915 -- local data variables
1916 l_item_id NUMBER;
1917 l_primary_uom_rate NUMBER;
1918 l_trx_uom_rate NUMBER;
1919 l_primary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1920 l_source_doc_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1921 l_trx_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1922 l_primary_uom MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
1923
1924 BEGIN
1925
1926 SAVEPOINT Convert_UOM_PVT;
1927 -- Initialize message list if p_init_msg_list is set to TRUE
1928 if FND_API.to_Boolean(P_INIT_MSG_LIST) then
1929 FND_MSG_PUB.initialize;
1930 end if;
1931
1932 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1933 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.begin'
1934 ,'Convert_UOM <<');
1935 END IF;
1936
1937 -- Standard check for compatibility
1938 IF NOT FND_API.Compatible_API_Call (
1939 l_api_version,
1940 P_API_VERSION,
1941 l_api_name,
1942 G_PKG_NAME ) -- line 90
1943 THEN
1944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1945 END IF;
1946
1947 -- Initialize API return status to success
1948 x_return_status := FND_API.G_RET_STS_SUCCESS;
1949 x_msg_count := 0;
1950 x_msg_data := '';
1951
1952 -- API body
1953 l_stmt_num := 10;
1954 l_item_id := p_event_rec.item_id;
1955
1956 -- Get UOM code for the source document's UOM
1957 SELECT uom_code
1958 INTO l_source_doc_uom_code
1959 FROM mtl_units_of_measure
1960 WHERE unit_of_measure = p_event_rec.source_doc_uom;
1961
1962 -- Get UOM code for the transaction UOM
1963 SELECT uom_code
1964 INTO l_trx_uom_code
1965 FROM mtl_units_of_measure
1966 WHERE unit_of_measure = p_event_rec.transaction_uom;
1967
1968
1969 -- Get UOM for this item/org from MSI and populate primary_uom with it
1970 IF (l_item_id IS NULL) THEN
1971
1972 -- for a one-time item, the primary uom is the
1973 -- base uom for the item's current uom class
1974 l_stmt_num := 20;
1975 SELECT PUOM.uom_code, PUOM.unit_of_measure
1976 INTO l_primary_uom_code, l_primary_uom
1977 FROM mtl_units_of_measure TUOM,
1978 mtl_units_of_measure PUOM
1979 WHERE TUOM.unit_of_measure = p_event_rec.source_doc_uom
1980 AND TUOM.uom_class = PUOM.uom_class
1981 AND PUOM.base_uom_flag = 'Y';
1982
1983 l_item_id := 0;
1984 ELSE
1985 l_stmt_num := 30;
1986 SELECT primary_uom_code
1987 INTO l_primary_uom_code
1988 FROM mtl_system_items
1989 WHERE organization_id = p_event_rec.organization_id
1990 AND inventory_item_id = l_item_id;
1991
1992 l_stmt_num := 40;
1993 SELECT unit_of_measure
1994 INTO l_primary_uom
1995 FROM mtl_units_of_measure
1996 WHERE uom_code = l_primary_uom_code;
1997 END IF;
1998
1999 -- Get the UOM rate from source_doc_uom to primary_uom
2000 l_stmt_num := 50;
2001 INV_Convert.INV_UM_Conversion(
2002 from_unit => l_source_doc_uom_code,
2003 to_unit => l_primary_uom_code,
2004 item_id => l_item_id,
2005 uom_rate => l_primary_uom_rate);
2006
2007 IF (l_primary_uom_rate = -99999) THEN
2008 RAISE FND_API.G_EXC_ERROR;
2009 l_api_message := 'inv_convert.inv_um_conversion() failed to get the UOM rate';
2010 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2011 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2012 l_api_message);
2013 END IF;
2014 END IF;
2015
2016 -- Get the UOM rate from source_doc_uom to transaction_uom
2017 l_stmt_num := 60;
2018 INV_Convert.INV_UM_Conversion(
2019 from_unit => l_source_doc_uom_code,
2020 to_unit => l_trx_uom_code,
2021 item_id => l_item_id,
2022 uom_rate => l_trx_uom_rate);
2023
2024 IF (l_trx_uom_rate = -99999) THEN
2025 RAISE FND_API.G_EXC_ERROR;
2026 l_api_message := 'inv_convert.inv_um_conversion() failed to get the UOM rate';
2027 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2028 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2029 l_api_message);
2030 END IF;
2031 END IF;
2032
2033
2034 -- Populate output variables
2035 x_primary_uom := l_primary_uom;
2036 x_primary_qty := l_primary_uom_rate * p_event_rec.source_doc_quantity; /*BUG 6838756 Removed rounding*/
2037 x_transaction_qty := l_trx_uom_rate * p_event_rec.source_doc_quantity; /*BUG 6838756 Removed rounding*/
2038 x_trx_uom_code := l_trx_uom_code;
2039
2040 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2041 l_api_message := 'x_primary_uom : '||x_primary_uom||
2042 ' x_primary_qty : '||x_primary_qty||
2043 ' x_transaction_qty : '||x_transaction_qty||
2044 ' x_trx_uom_code : '||x_trx_uom_code;
2045 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2046 ,l_api_message);
2047 END IF;
2048
2049 -- End of API body
2050
2051 -- Standard check of P_COMMIT
2052 IF FND_API.to_Boolean(P_COMMIT) THEN
2053 COMMIT WORK;
2054 END IF;
2055
2056 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2057 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.end'
2058 ,'Convert_UOM >>');
2059 END IF;
2060
2061 EXCEPTION
2062 WHEN FND_API.g_exc_error THEN
2063 ROLLBACK TO Convert_UOM_PVT;
2064 x_return_status := FND_API.g_ret_sts_error;
2065 FND_MSG_PUB.count_and_get
2066 ( p_count => x_msg_count
2067 , p_data => x_msg_data
2068 );
2069
2070 WHEN FND_API.g_exc_unexpected_error THEN
2071 ROLLBACK TO Convert_UOM_PVT;
2072 x_return_status := FND_API.g_ret_sts_unexp_error;
2073 FND_MSG_PUB.count_and_get
2074 ( p_count => x_msg_count
2075 , p_data => x_msg_data
2076 );
2077
2078 WHEN NO_DATA_FOUND then
2079 ROLLBACK TO Convert_UOM_PVT;
2080 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
2081 l_api_message := ': Statement # '||to_char(l_stmt_num)||' - No UOM found.';
2082 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2083 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2084 l_api_message);
2085 END IF;
2086 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
2087 THEN
2088 FND_MSG_PUB.add_exc_msg
2089 ( G_PKG_NAME,
2090 l_api_name || l_api_message
2091 );
2092 END IF;
2093 FND_MSG_PUB.count_and_get
2094 ( p_count => x_msg_count
2095 , p_data => x_msg_data
2096 );
2097
2098 WHEN OTHERS then
2099 ROLLBACK TO Convert_UOM_PVT;
2100 x_return_status := FND_API.g_ret_sts_unexp_error ;
2101 l_api_message := 'Unexpected Error at statement('||to_char(l_stmt_num)||'): '||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,100);
2102 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2103 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2104 l_api_message);
2105 END IF;
2106 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2107 FND_MSG_PUB.add_exc_msg
2108 ( G_PKG_NAME,
2109 l_api_name || l_api_message );
2110 END IF;
2111 FND_MSG_PUB.count_and_get
2112 ( p_count => x_msg_count
2113 , p_data => x_msg_data
2114 );
2115
2116 END Convert_UOM;
2117
2118 -----------------------------------------------------------------------------
2119 -- Start of comments --
2120 -- --
2121 -- PROCEDURE --
2122 -- Get_Currency This procedure returns the currency_conversion --
2123 -- parameters, conversion rate, date and type --
2124 --
2125 -- It is being coded for the purpose of providing the --
2126 -- currency conversion parameters for Global Procurement --
2127 -- and true drop shipment scenario, but may be used as a --
2128 -- generic API to return currency conversion rates for --
2129 -- Receiving transactions. --
2130 -- --
2131 -- Logic: --
2132 -- If supplier facing org, if match to po use POD.rate --
2133 -- else --
2134 -- rcv_transactions.curr_conv_rate--
2135 -- Else --
2136 -- Get the conversion type --
2137 -- Determine currency conversion rate --
2138 -- --
2139 -- --
2140 -- --
2141
2142 -- --
2143 -- VERSION 1.0 --
2144 -- --
2145 -- PARAMETERS --
2146 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
2147 -- P_INIT_MSG_LIST Initialize message list? True/False --
2148 -- P_COMMIT Should the API commit before returning? True/False --
2149 -- P_RCV_EVENT Record storing an RCV Accounting Event (RAE) --
2150 -- X_CURRENCY_CODE --
2151 -- X_CURRENCY_CONVERSION_RATE --
2152 -- X_CURRENCY_CONVERSION_TYPE --
2153 -- X_CURRENCY_CONVERSION_TYPE --
2154 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
2155 -- X_MSG_COUNT Message Count - # of messages placed in message list--
2156 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
2157 -- --
2158 -- HISTORY: --
2159 -- 08/02/03 Anju Gupta Created --
2160 -- End of comments --
2161 -----------------------------------------------------------------------------
2162 PROCEDURE Get_Currency(
2163 P_API_VERSION IN NUMBER,
2164 P_INIT_MSG_LIST IN VARCHAR2,
2165 P_COMMIT IN VARCHAR2,
2166 P_VALIDATION_LEVEL IN NUMBER,
2167 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2168 X_MSG_COUNT OUT NOCOPY NUMBER,
2169 X_MSG_DATA OUT NOCOPY VARCHAR2,
2170
2171 P_RCV_EVENT IN RCV_SeedEvents_PVT.rcv_event_rec_type,
2172 X_CURRENCY_CODE OUT NOCOPY VARCHAR2,
2173 X_CURRENCY_CONVERSION_RATE OUT NOCOPY NUMBER,
2174 X_CURRENCY_CONVERSION_DATE OUT NOCOPY DATE,
2175 X_CURRENCY_CONVERSION_TYPE OUT NOCOPY VARCHAR2
2176 ) IS
2177
2178 -- local control variables
2179 l_api_name CONSTANT VARCHAR2(30) := 'GET_Currency';
2180 l_api_version CONSTANT NUMBER := 1.0;
2181 l_stmt_num number := 0;
2182 l_api_message VARCHAR2(1000);
2183
2184 -- local data variables
2185 l_match_option VARCHAR2(1);
2186 l_currency_code RCV_TRANSACTIONS.currency_code%TYPE;
2187 l_currency_conversion_rate NUMBER;
2188 l_currency_conversion_date DATE;
2189 l_currency_conversion_type RCV_TRANSACTIONS.currency_conversion_type%TYPE := '';
2190 l_sob_id NUMBER;
2191 l_po_line_location_id NUMBER;
2192 l_rcv_transaction_id NUMBER;
2193
2194
2195 BEGIN
2196
2197 SAVEPOINT Get_Currency_PVT;
2198
2199 -- Standard call to check for call compatibility
2200 IF NOT FND_API.Compatible_API_Call (
2201 l_api_version,
2202 p_api_version,
2203 l_api_name,
2204 G_PKG_NAME ) THEN
2205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2206 END IF;
2207
2208 -- Initialize message list if p_init_msg_list is set to TRUE
2209 IF FND_API.to_Boolean(P_INIT_MSG_LIST) then
2210 FND_MSG_PUB.initialize;
2211 END IF;
2212
2213
2214 -- Initialize API return status to success
2215 x_return_status := FND_API.G_RET_STS_SUCCESS;
2216
2217 -- API body
2218 l_stmt_num := 10;
2219
2220 IF ((p_rcv_event.procurement_org_flag = 'Y') AND
2221 (p_rcv_event.event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
2222 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL))) THEN
2223
2224 l_currency_code := p_rcv_event.currency_code;
2225
2226 l_stmt_num := 20;
2227 SELECT line_location_id
2228 INTO l_po_line_location_id
2229 FROM po_distributions
2230 WHERE po_distribution_id = p_rcv_event.po_distribution_id;
2231
2232 l_stmt_num := 30;
2233 SELECT match_option
2234 INTO l_match_option
2235 FROM po_line_locations
2236 WHERE line_location_id = l_po_line_location_id;
2237
2238 -- Always use rate on the PO distribution for encumbrance reversals.
2239 IF (l_match_option = 'P' OR (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL)) THEN
2240
2241 l_stmt_num := 40;
2242 SELECT nvl(POD.rate,1),
2243 POH.rate_type,
2244 POD.rate_date
2245 INTO l_currency_conversion_rate,
2246 l_currency_conversion_type,
2247 l_currency_conversion_date
2248 FROM po_distributions POD,
2249 po_headers POH
2250 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id
2251 AND POH.po_header_id = POD.po_header_id;
2252
2253 ELSE
2254 -- This is also correct for ADJUST events where we only create one event
2255 -- for every parent transaction. In the case of a Match to receipt PO, the
2256 -- currency conversion rate of the child transactions (DELIVER CORRECT, RTR,
2257 -- RTV) will be the same as the currency conversion rate on the parent
2258 -- RECEIVE/MATCH transaction. This will be the case even if the daily rate
2259 -- has changed between the time that the parent transaction was done and the
2260 -- time that the child transactions were done.
2261
2262 l_stmt_num := 50;
2263 SELECT RT.currency_conversion_rate,
2264 RT.currency_conversion_type,
2265 RT.currency_conversion_date
2266 INTO l_currency_conversion_rate,
2267 l_currency_conversion_type,
2268 l_currency_conversion_date
2269 FROM rcv_transactions RT
2270 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id;
2271
2272 END IF;
2273 ELSE
2274
2275 l_currency_code := p_rcv_event.currency_code;
2276 l_sob_id := p_rcv_event.set_of_books_id;
2277
2278 -- Use profile INV: Intercompany Currency conversion Type, to determine Conversion Type
2279 -- Ensure that INV uses the same type for conversion for GP/ DS scenarios
2280
2281 l_stmt_num := 70;
2282 FND_PROFILE.get('IC_CURRENCY_CONVERSION_TYPE', l_currency_conversion_type);
2283
2284 l_stmt_num := 80;
2285 l_currency_conversion_rate := GL_Currency_API.get_rate(
2286 x_set_of_books_id => l_sob_id,
2287 x_from_currency => l_currency_code,
2288 x_conversion_date => p_rcv_event.transaction_date,
2289 x_conversion_type => l_currency_conversion_type);
2290 END IF;
2291
2292 x_currency_code := l_currency_code;
2293 x_currency_conversion_rate := l_currency_conversion_rate;
2294 x_currency_conversion_date := NVL(l_currency_conversion_date,sysdate);
2295 x_currency_conversion_type := l_currency_conversion_type;
2296
2297 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2298 l_api_message := SUBSTR('x_currency_code : '||x_currency_code||
2299 ' x_currency_conversion_rate : '||TO_CHAR(x_currency_conversion_rate)||
2300 ' x_currency_conversion_date : '||TO_CHAR(x_currency_conversion_date,'DD-MON-YY')||
2301 ' x_currency_conversion_type : '||x_currency_conversion_type,1,1000);
2302 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2303 ,l_api_message);
2304 END IF;
2305
2306
2307 -- End of API body
2308
2309 FND_MSG_PUB.Count_And_Get (
2310 p_encoded => FND_API.G_FALSE,
2311 p_count => X_MSG_COUNT,
2312 p_data => X_MSG_DATA );
2313
2314
2315 -- Standard check of P_COMMIT
2316 IF FND_API.to_Boolean(P_COMMIT) THEN
2317 COMMIT WORK;
2318 END IF;
2319
2320
2321 EXCEPTION
2322 WHEN FND_API.g_exc_error THEN
2323 ROLLBACK TO Get_Currency_PVT;
2324 x_return_status := FND_API.g_ret_sts_error;
2325 FND_MSG_PUB.count_and_get
2326 ( p_count => x_msg_count
2327 , p_data => x_msg_data
2328 );
2329
2330 WHEN FND_API.g_exc_unexpected_error THEN
2331 ROLLBACK TO Get_Currency_PVT;
2332 x_return_status := FND_API.g_ret_sts_unexp_error ;
2333 FND_MSG_PUB.count_and_get
2334 ( p_count => x_msg_count
2335 , p_data => x_msg_data
2336 );
2337
2338 WHEN NO_DATA_FOUND then
2339 ROLLBACK TO GET_CURRENCY_PVT;
2340 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
2341 l_api_message := 'Unexpected Error: '||l_stmt_num||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,200);
2342 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2343 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2344 l_api_message);
2345 END IF;
2346
2347 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2348 THEN
2349 FND_MSG_PUB.add_exc_msg
2350 ( G_PKG_NAME,
2351 l_api_name || 'Statement -'||to_char(l_stmt_num)
2352 );
2353 END IF;
2354
2355 FND_MSG_PUB.Count_And_Get (
2356 p_count => X_MSG_COUNT,
2357 p_data => X_MSG_DATA );
2358
2359 WHEN OTHERS then
2360 ROLLBACK TO GET_CURRENCY_PVT;
2361 x_return_status := FND_API.g_ret_sts_unexp_error ;
2362 l_api_message := 'Unexpected Error: '||l_stmt_num||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,200);
2363 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2364 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
2365 l_api_message);
2366 END IF;
2367
2368 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2369 THEN
2370 FND_MSG_PUB.add_exc_msg
2371 ( G_PKG_NAME,
2372 l_api_name || 'Statement -'||to_char(l_stmt_num)
2373 );
2374 END IF;
2375
2376 FND_MSG_PUB.Count_And_Get (
2377 p_count => X_MSG_COUNT,
2378 p_data => X_MSG_DATA );
2379
2380 END Get_Currency;
2381
2382
2383
2384 -- Start of comments
2385 -- API name : Get_Accounts
2386 -- Type : Private
2387 -- Function : To get the credit and debit accounts for each event.
2388 -- Pre-reqs :
2389 -- Parameters :
2390 -- IN : p_api_version IN NUMBER Required
2391 -- p_init_msg_list IN VARCHAR2 Optional
2392 -- Default = FND_API.G_FALSE
2393 -- p_commit IN VARCHAR2 Optional
2394 -- Default = FND_API.G_FALSE
2395 -- p_validation_level IN NUMBER Optional
2396 -- Default = FND_API.G_VALID_LEVEL_FULL
2397 -- p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type Required
2398 -- p_transaction_forward_flow_rec mtl_transaction_flow_rec_type,
2399 -- p_transaction_reverse_flow_rec mtl_transaction_flow_rec_type,
2400 --
2401 -- OUT : x_return_status OUT VARCHAR2(1)
2402 -- x_msg_count OUT NUMBER
2403 -- x_msg_data OUT VARCHAR2(2000)
2404 -- x_credit_acct_id OUT NUMBER
2405 -- x_debit_acct_id OUT NUMBER
2406 -- x_ic_cogs_acct_id OUT NUMBER
2407 -- Version :
2408 -- Initial version 1.0
2409 --
2410 --
2411 -- Notes : This API creates all accounting events for RETURN TO VENDOR transactions
2412 -- in RCV_ACCOUNTING_EVENTS.
2413 --
2414 -- End of comments
2415 PROCEDURE Get_Accounts(
2416 p_api_version IN NUMBER,
2417 p_init_msg_list IN VARCHAR2,
2418 p_commit IN VARCHAR2,
2419 p_validation_level IN NUMBER,
2420 x_return_status OUT NOCOPY VARCHAR2,
2421 x_msg_count OUT NOCOPY NUMBER,
2422 x_msg_data OUT NOCOPY VARCHAR2,
2423
2424 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
2425 p_transaction_forward_flow_rec INV_TRANSACTION_FLOW_PUB.mtl_transaction_flow_rec_type,
2426 p_transaction_reverse_flow_rec INV_TRANSACTION_FLOW_PUB.mtl_transaction_flow_rec_type,
2427 /* Support for Landed Cost Management */
2428 p_lcm_flag IN VARCHAR2,
2429 x_credit_acct_id OUT NOCOPY NUMBER,
2430 x_debit_acct_id OUT NOCOPY NUMBER,
2431 x_ic_cogs_acct_id OUT NOCOPY NUMBER,
2432 /* Support for Landed Cost Management */
2433 x_lcm_acct_id OUT NOCOPY NUMBER
2434 ) IS
2435 l_api_name CONSTANT VARCHAR2(30) := 'Get_Accounts';
2436 l_api_version CONSTANT NUMBER := 1.0;
2437
2438 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2439 l_msg_count NUMBER := 0;
2440 l_msg_data VARCHAR2(8000) := '';
2441 l_stmt_num NUMBER := 0;
2442 l_api_message VARCHAR2(1000);
2443
2444 l_credit_acct_id NUMBER;
2445 l_debit_acct_id NUMBER;
2446 l_dist_acct_id NUMBER;
2447 l_ic_cogs_acct_id NUMBER;
2448 l_ic_coss_acct_id NUMBER;
2449
2450 l_pod_accrual_acct_id NUMBER;
2451 l_pod_ccid NUMBER;
2452 l_dest_pod_ccid NUMBER;
2453 l_pod_budget_acct_id NUMBER;
2454
2455 l_receiving_insp_acct_id NUMBER;
2456 l_clearing_acct_id NUMBER;
2457 l_retroprice_adj_acct_id NUMBER;
2458 l_overlaid_acct NUMBER;
2459 /* Support for Landed Cost Management */
2460 l_lcm_acct_id NUMBER;
2461
2462 l_trx_type rcv_transactions.transaction_type%TYPE;
2463 l_parent_trx_type rcv_transactions.transaction_type%TYPE;
2464 l_parent_trx_id NUMBER;
2465 l_account_flag NUMBER := 0;
2466 BEGIN
2467 -- Standard start of API savepoint
2468 SAVEPOINT Get_Accounts_PVT;
2469
2470 l_stmt_num := 0;
2471
2472 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2473 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
2474 ,'Get_Accounts <<');
2475
2476 END IF;
2477
2478 -- Standard call to check for call compatibility
2479 IF NOT FND_API.Compatible_API_Call (
2480 l_api_version,
2481 p_api_version,
2482 l_api_name,
2483 G_PKG_NAME ) THEN
2484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2485 END IF;
2486
2487 -- Initialize message list if p_init_msg_list is set to TRUE
2488 IF FND_API.to_Boolean(p_init_msg_list) THEN
2489 FND_MSG_PUB.initialize;
2490 END IF;
2491
2492 -- Initialize API return status to success
2493 x_return_status := FND_API.G_RET_STS_SUCCESS;
2494 x_credit_acct_id := NULL;
2495 x_debit_acct_id := NULL;
2496 x_ic_cogs_acct_id := NULL;
2497 /* Support for Landed Cost Management */
2498 x_lcm_acct_id := NULL;
2499
2500 -- No accounts are stored for IC events.
2501 l_stmt_num := 5;
2502 IF(p_rcv_event.event_type_id IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
2503 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
2504 return;
2505 END IF;
2506
2507 l_stmt_num := 10;
2508 SELECT POD.accrual_account_id,
2509 POD.code_combination_id,
2510 NVL(POD.dest_charge_account_id,POD.code_combination_id),
2511 POD.budget_account_id
2512 INTO l_pod_accrual_acct_id,
2513 l_pod_ccid,
2514 l_dest_pod_ccid,
2515 l_pod_budget_acct_id
2516 FROM po_distributions POD
2517 WHERE POD.po_distribution_id = p_rcv_event.po_distribution_id;
2518
2519 l_stmt_num := 20;
2520 SELECT receiving_account_id,
2521 clearing_account_id,
2522 retroprice_adj_account_id,
2523 /* Support for Landed Cost Management */
2524 DECODE(p_lcm_flag, 'Y', lcm_account_id, NULL)
2525 INTO l_receiving_insp_acct_id,
2526 l_clearing_acct_id,
2527 l_retroprice_adj_acct_id,
2528 /* Support for Landed Cost Management */
2529 l_lcm_acct_id
2530 FROM RCV_PARAMETERS
2531 WHERE organization_id = p_rcv_event.organization_id;
2532 -- Changes for JFMIP. Bug # 3076229. Call API to override the balancing segment
2533 -- of the Receiving Inspection account for expense destination types. The option
2534 -- (Auto Offset Override on PO_SYSTEM_PARAMETERS) will only be available in orgs
2535 -- where encumbrance is enabled. Hence this is not applicable to Global Procurement,
2536 -- Drop Ship or retroactive pricing.
2537 -- Modified for bug #4893292: Call API to override balance segment for inventory destinations as well.
2538 IF(p_rcv_event.trx_flow_header_id IS NULL AND
2539 p_rcv_event.event_type_id IN (RCV_SeedEvents_PVT.RECEIVE, RCV_SeedEvents_PVT.MATCH,
2540 RCV_SeedEvents_PVT.DELIVER, RCV_SeedEvents_PVT.CORRECT,
2541 RCV_SeedEvents_PVT.RETURN_TO_RECEIVING,
2542 RCV_SeedEvents_PVT.RETURN_TO_VENDOR) AND
2543 p_rcv_event.destination_type_code IN ('EXPENSE', 'INVENTORY')) THEN
2544
2545 l_stmt_num := 30;
2546 PO_Accounting_GRP.build_offset_account
2547 (p_api_version => 1.0,
2548 p_init_msg_list => FND_API.G_FALSE,
2549 x_return_status => l_return_status,
2550 p_base_ccid => l_receiving_insp_acct_id,
2551 p_overlay_ccid => l_dest_pod_ccid,
2552 p_accounting_date =>sysdate,
2553 p_org_id => p_rcv_event.org_id,
2554 x_result_ccid => l_overlaid_acct
2555 );
2556
2557 l_receiving_insp_acct_id := l_overlaid_acct;
2558
2559 END IF;
2560
2561 IF( p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT) THEN
2562
2563 l_stmt_num := 40;
2564 SELECT PARENT_TRX.transaction_type
2565 INTO l_parent_trx_type
2566 FROM rcv_transactions TRX,
2567 rcv_transactions PARENT_TRX
2568 WHERE TRX.transaction_id = p_rcv_event.rcv_transaction_id
2569 AND TRX.parent_transaction_id = PARENT_TRX.transaction_id;
2570 END IF;
2571
2572 l_stmt_num := 50;
2573 IF((p_rcv_event.event_type_id = RCV_SeedEvents_PVT.RECEIVE) OR
2574 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.MATCH) OR
2575 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2576 l_parent_trx_type = 'RECEIVE') OR
2577 p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2578 l_parent_trx_type = 'MATCH') THEN
2579
2580 l_debit_acct_id := l_receiving_insp_acct_id;
2581
2582 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2583 l_credit_acct_id := l_pod_accrual_acct_id;
2584 ELSIF(p_rcv_event.item_id IS NULL) THEN
2585 l_credit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2586 ELSE
2587 l_credit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2588 END IF;
2589
2590 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.LOGICAL_RECEIVE) THEN
2591
2592 -- Use clearing account for :
2593 -- a. destination type of Inventory
2594 -- b. destination type of Expense for inventory items.
2595 -- Use Cost of Sales account for
2596 -- a. destination type of Shop Floor.
2597 -- b. destination type of Expense for one time items
2598 IF(p_rcv_event.destination_type_code = 'INVENTORY' OR
2599 (p_rcv_event.destination_type_code = 'EXPENSE' AND
2600 p_rcv_event.item_id is not null)) THEN
2601 l_debit_acct_id := l_clearing_acct_id;
2602 ELSIF(p_rcv_event.procurement_org_flag = 'Y') THEN
2603 l_debit_acct_id := l_pod_ccid;
2604 ELSE
2605 l_stmt_num := 60;
2606 SELECT cost_of_sales_account
2607 INTO l_ic_coss_acct_id
2608 FROM mtl_parameters MP
2609 WHERE MP.organization_id = p_rcv_event.organization_id;
2610
2611 l_stmt_num := 70;
2612 RCV_SeedEvents_PVT.Get_HookAccount(
2613 p_api_version => l_api_version,
2614 x_return_status => l_return_status,
2615 x_msg_count => l_msg_count,
2616 x_msg_data => l_msg_data,
2617 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2618 p_accounting_line_type => 'IC Cost Of Sales',
2619 p_org_id => p_rcv_event.org_id,
2620 x_distribution_acct_id => l_dist_acct_id);
2621
2622 IF l_return_status <> FND_API.g_ret_sts_success THEN
2623 l_api_message := 'Error in Get_HookAccount';
2624 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2625 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2626 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2627 END IF;
2628 RAISE FND_API.g_exc_unexpected_error;
2629 END IF;
2630
2631 IF(l_dist_acct_id = -1) THEN
2632 l_debit_acct_id := l_ic_coss_acct_id;
2633 ELSE
2634 l_debit_acct_id := l_dist_acct_id;
2635 END IF;
2636
2637 END IF;
2638
2639 l_stmt_num := 80;
2640 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2641 l_credit_acct_id := l_pod_accrual_acct_id;
2642 ELSIF(p_rcv_event.item_id IS NULL) THEN
2643 l_credit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2644 ELSE
2645 l_credit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2646 END IF;
2647
2648 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.DELIVER OR
2649 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2650 l_parent_trx_type = 'DELIVER')) THEN
2651 l_debit_acct_id := l_dest_pod_ccid;
2652 l_credit_acct_id := l_receiving_insp_acct_id;
2653
2654 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.RETURN_TO_VENDOR OR
2655 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2656 l_parent_trx_type = 'RETURN TO VENDOR')) THEN
2657 l_credit_acct_id := l_receiving_insp_acct_id;
2658
2659 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2660 l_debit_acct_id := l_pod_accrual_acct_id;
2661 ELSIF(p_rcv_event.item_id IS NULL) THEN
2662 l_debit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2663 ELSE
2664 l_debit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2665 END IF;
2666
2667 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.LOGICAL_RETURN_TO_VENDOR) THEN
2668 IF(p_rcv_event.destination_type_code = 'INVENTORY' OR
2669 (p_rcv_event.destination_type_code = 'EXPENSE' AND
2670 p_rcv_event.item_id is not null)) THEN
2671 l_credit_acct_id := l_clearing_acct_id;
2672 ELSIF(p_rcv_event.procurement_org_flag = 'Y') THEN
2673 l_credit_acct_id := l_pod_ccid;
2674 ELSE
2675 l_stmt_num := 90;
2676 SELECT cost_of_sales_account
2677 INTO l_ic_coss_acct_id
2678 FROM mtl_parameters MP
2679 WHERE MP.organization_id = p_rcv_event.organization_id;
2680
2681 l_stmt_num := 100;
2682 RCV_SeedEvents_PVT.Get_HookAccount(
2683 p_api_version => l_api_version,
2684 x_return_status => l_return_status,
2685 x_msg_count => l_msg_count,
2686 x_msg_data => l_msg_data,
2687 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2688 p_accounting_line_type => 'IC Cost Of Sales',
2689 p_org_id => p_rcv_event.org_id,
2690 x_distribution_acct_id => l_dist_acct_id);
2691
2692 IF l_return_status <> FND_API.g_ret_sts_success THEN
2693 l_api_message := 'Error in Get_HookAccount';
2694 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2695 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2696 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2697 END IF;
2698 RAISE FND_API.g_exc_unexpected_error;
2699 END IF;
2700
2701 IF(l_dist_acct_id = -1) THEN
2702 l_credit_acct_id := l_ic_coss_acct_id;
2703 ELSE
2704 l_credit_acct_id := l_dist_acct_id;
2705 END IF;
2706
2707 END IF;
2708
2709 IF(p_rcv_event.procurement_org_flag = 'Y') THEN
2710 l_debit_acct_id := l_pod_accrual_acct_id;
2711 ELSIF(p_rcv_event.item_id IS NULL) THEN
2712 l_debit_acct_id := p_transaction_reverse_flow_rec.expense_accrual_account_id;
2713 ELSE
2714 l_debit_acct_id := p_transaction_reverse_flow_rec.inventory_accrual_account_id;
2715 END IF;
2716
2717 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.RETURN_TO_RECEIVING OR
2718 (p_rcv_event.event_type_id = RCV_SeedEvents_PVT.CORRECT AND
2719 l_parent_trx_type = 'RETURN TO RECEIVING')) THEN
2720 l_credit_acct_id := l_dest_pod_ccid;
2721 l_debit_acct_id := l_receiving_insp_acct_id;
2722
2723 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ADJUST_RECEIVE) THEN
2724 -- In the case of drop shipments, we always use the clearing account instead of the Receiving
2725 -- Inspection account. In these scenarios, we should be posting the adjustment for the entire
2726 -- Receipt to the retroactive price adjustment account.
2727 IF(p_rcv_event.trx_flow_header_id IS NOT NULL OR p_rcv_event.drop_ship_flag IN (1,2)) THEN
2728
2729 -- For global procurement scenarios, the debit account is :
2730 -- Retroprice adjustment account for inv items and direct items.
2731 -- IC Cost Of Sales(Charge acct on POD) for one-time items and Expense destinations.
2732
2733 IF ( p_rcv_event.item_id IS NOT NULL OR
2734 p_rcv_event.destination_type_code = 'SHOP FLOOR') THEN
2735
2736 l_stmt_num := 110;
2737 RCV_SeedEvents_PVT.Get_HookAccount(
2738 p_api_version => l_api_version,
2739 x_return_status => l_return_status,
2740 x_msg_count => l_msg_count,
2741 x_msg_data => l_msg_data,
2742 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2743 p_accounting_line_type => 'Retroprice Adjustment',
2744 p_org_id => p_rcv_event.org_id,
2745 x_distribution_acct_id => l_dist_acct_id);
2746
2747 IF l_return_status <> FND_API.g_ret_sts_success THEN
2748 l_api_message := 'Error in Get_HookAccount';
2749 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2750 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2751 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2752 END IF;
2753 RAISE FND_API.g_exc_unexpected_error;
2754 END IF;
2755
2756 IF(l_dist_acct_id = -1) THEN
2757 l_debit_acct_id := l_retroprice_adj_acct_id;
2758 ELSE
2759 l_debit_acct_id := l_dist_acct_id;
2760 END IF;
2761
2762 ELSE
2763 l_debit_acct_id := l_pod_ccid;
2764 END IF;
2765 ELSE
2766 l_debit_acct_id := l_receiving_insp_acct_id;
2767 END IF;
2768
2769 l_credit_acct_id := l_pod_accrual_acct_id;
2770
2771 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ADJUST_DELIVER) THEN
2772
2773 -- Redundant check. Transaction flow header id is always NULL. We only
2774 -- get ADJUST_RECEIVE events for global procurement.
2775 IF(p_rcv_event.trx_flow_header_id IS NULL AND p_rcv_event.drop_ship_flag NOT IN (1,2)) THEN
2776 IF(p_rcv_event.destination_type_code = 'EXPENSE')THEN
2777 l_debit_acct_id := l_dest_pod_ccid;
2778 ELSE
2779
2780 l_stmt_num := 120;
2781 RCV_SeedEvents_PVT.Get_HookAccount(
2782 p_api_version => l_api_version,
2783 x_return_status => l_return_status,
2784 x_msg_count => l_msg_count,
2785 x_msg_data => l_msg_data,
2786 p_rcv_transaction_id => p_rcv_event.rcv_transaction_id,
2787 p_accounting_line_type => 'Retroprice Adjustment',
2788 p_org_id => p_rcv_event.org_id,
2789 x_distribution_acct_id => l_dist_acct_id);
2790
2791 IF l_return_status <> FND_API.g_ret_sts_success THEN
2792 l_api_message := 'Error in Get_HookAccount';
2793 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2794 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2795 ,'Get_Accounts : '||l_stmt_num||' : '||l_api_message);
2796 END IF;
2797 RAISE FND_API.g_exc_unexpected_error;
2798 END IF;
2799
2800 IF(l_dist_acct_id = -1) THEN
2801 l_debit_acct_id := l_retroprice_adj_acct_id;
2802 ELSE
2803 l_debit_acct_id := l_dist_acct_id;
2804 END IF;
2805 END IF;
2806 END IF;
2807
2808 l_stmt_num := 130;
2809 l_credit_acct_id := l_receiving_insp_acct_id;
2810
2811 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE OR
2812 p_rcv_event.event_type_id = RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)THEN
2813 l_credit_acct_id := NULL;
2814 l_debit_acct_id := NULL;
2815
2816 ELSIF(p_rcv_event.event_type_id = RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL) THEN
2817
2818
2819 l_stmt_num := 140;
2820 SELECT RT.transaction_type, RT.parent_transaction_id
2821 INTO l_trx_type, l_parent_trx_id
2822 FROM rcv_transactions RT
2823 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id;
2824
2825 IF(l_trx_type = 'DELIVER')THEN
2826 l_credit_acct_id := l_pod_budget_acct_id;
2827 l_debit_acct_id := NULL;
2828 ELSIF (l_trx_type = 'RETURN TO RECEIVING') THEN
2829 l_debit_acct_id := l_pod_budget_acct_id;
2830 l_credit_acct_id := NULL;
2831 ELSIF (l_trx_type = 'CORRECT') THEN
2832
2833 l_stmt_num := 150;
2834 SELECT PARENT_TRX.transaction_type
2835 INTO l_parent_trx_type
2836 FROM rcv_transactions PARENT_TRX
2837 WHERE PARENT_TRX.transaction_id = l_parent_trx_id;
2838
2839 IF(l_parent_trx_type = 'DELIVER')THEN
2840 l_credit_acct_id := l_pod_budget_acct_id;
2841 l_debit_acct_id := NULL;
2842 ELSIF (l_parent_trx_type = 'RETURN_TO_RECEIVING') THEN
2843 l_debit_acct_id := l_pod_budget_acct_id;
2844 l_credit_acct_id := NULL;
2845 END IF;
2846 END IF;
2847
2848 END IF;
2849
2850 x_debit_acct_id := l_debit_acct_id;
2851 x_credit_acct_id := l_credit_acct_id;
2852 x_ic_cogs_acct_id := p_transaction_forward_flow_rec.intercompany_cogs_account_id;
2853 /* Support for Landed Cost Management */
2854 x_lcm_acct_id := l_lcm_acct_id;
2855
2856 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2857 l_api_message := 'x_debit_acct_id : '||x_debit_acct_id||
2858 ' x_credit_acct_id : '||x_credit_acct_id||
2859 ' x_ic_cogs_acct_id : ' || x_ic_cogs_acct_id;
2860 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2861 ,l_api_message);
2862 END IF;
2863
2864 IF ((l_debit_acct_id IS NULL OR l_credit_acct_id IS NULL) AND
2865 (p_rcv_event.event_type_id NOT IN (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,
2866 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL,
2867 RCV_SeedEvents_PVT.ENCUMBRANCE_REVERSAL))) THEN
2868 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2869 l_api_message := 'Unable to find credit and/or debit account. Setup is incomplete. ';
2870 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
2871 ,l_api_message);
2872 END IF;
2873
2874 FND_MESSAGE.set_name('PO','PO_INVALID_ACCOUNT');
2875 FND_MSG_pub.add;
2876 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2877 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
2878 END IF;
2879 RAISE FND_API.g_exc_error;
2880 END IF;
2881
2882
2883 -- Standard check of p_commit
2884 IF FND_API.to_Boolean(p_commit) THEN
2885 COMMIT WORK;
2886 END IF;
2887
2888 -- Standard Call to get message count and if count = 1, get message info
2889 FND_MSG_PUB.Count_And_Get (
2890 p_count => x_msg_count,
2891 p_data => x_msg_data );
2892
2893 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2894 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
2895 ,'Get_Accounts >>');
2896 END IF;
2897
2898
2899 EXCEPTION
2900 WHEN FND_API.g_exc_error THEN
2901 ROLLBACK TO Get_Accounts_PVT;
2902 x_return_status := FND_API.g_ret_sts_error;
2903 FND_MSG_PUB.count_and_get
2904 ( p_count => x_msg_count
2905 , p_data => x_msg_data
2906 );
2907 WHEN FND_API.g_exc_unexpected_error THEN
2908 ROLLBACK TO Get_Accounts_PVT;
2909 x_return_status := FND_API.g_ret_sts_unexp_error ;
2910 FND_MSG_PUB.count_and_get
2911 ( p_count => x_msg_count
2912 , p_data => x_msg_data
2913 );
2914
2915 WHEN OTHERS THEN
2916 ROLLBACK TO Get_Accounts_PVT;
2917 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2918
2919 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2920 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
2921 ,'Get_Accounts : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
2922 END IF;
2923
2924 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2925 THEN
2926 FND_MSG_PUB.add_exc_msg
2927 ( G_PKG_NAME,
2928 l_api_name || 'Statement -'||to_char(l_stmt_num)
2929 );
2930 END IF;
2931 FND_MSG_PUB.count_and_get
2932 ( p_count => x_msg_count
2933 , p_data => x_msg_data
2934 );
2935
2936 END Get_Accounts;
2937
2938 -- Start of comments
2939 -- API name : Get_HookAccount
2940 -- Type : Private
2941 -- Function : Call account hook to allow customer to override default account.
2942 -- Pre-reqs :
2943 -- Parameters :
2944 -- IN : p_api_version IN NUMBER Required
2945 -- p_init_msg_list IN VARCHAR2 Optional
2946 -- Default = FND_API.G_FALSE
2947 -- p_commit IN VARCHAR2 Optional
2948 -- Default = FND_API.G_FALSE
2949 -- p_validation_level IN NUMBER Optional
2950 -- Default = FND_API.G_VALID_LEVEL_FULL
2951 -- p_rcv_transaction_id IN NUMBER Required
2952 -- p_accounting_line_type IN VARCHAR2 Required
2953 -- p_org_id IN NUMBER Required
2954 --
2955 -- OUT : x_return_status OUT VARCHAR2(1)
2956 -- x_msg_count OUT NUMBER
2957 -- x_msg_data OUT VARCHAR2(2000)
2958 -- x_distribution_acct_id OUT NUMBER
2959 --
2960 -- Version :
2961 -- Initial version 1.0
2962 --
2963 --
2964 -- Notes : This API creates all accounting events for RETURN TO VENDOR transactions
2965 -- in RCV_ACCOUNTING_EVENTS.
2966 --
2967 -- End of comments
2968 PROCEDURE Get_HookAccount(
2969 p_api_version IN NUMBER,
2970 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2971 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2972 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2973 x_return_status OUT NOCOPY VARCHAR2,
2974 x_msg_count OUT NOCOPY NUMBER,
2975 x_msg_data OUT NOCOPY VARCHAR2,
2976
2977 p_rcv_transaction_id IN NUMBER,
2978 p_accounting_line_type IN VARCHAR2,
2979 p_org_id IN NUMBER,
2980 x_distribution_acct_id OUT NOCOPY NUMBER
2981 ) IS
2982 l_api_name CONSTANT VARCHAR2(30) := 'Get_HookAccount';
2983 l_api_version CONSTANT NUMBER := 1.0;
2984
2985 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2986 l_msg_count NUMBER := 0;
2987 l_msg_data VARCHAR2(8000) := '';
2988 l_stmt_num NUMBER := 0;
2989 l_api_message VARCHAR2(1000);
2990
2991 l_dist_acct_id NUMBER;
2992 l_account_flag NUMBER;
2993
2994 BEGIN
2995 -- Standard start of API savepoint
2996 SAVEPOINT Get_HookAccount_PVT;
2997
2998 l_stmt_num := 0;
2999
3000 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3001 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
3002 ,'Get_HookAccount <<');
3003
3004 END IF;
3005
3006 -- Standard call to check for call compatibility
3007 IF NOT FND_API.Compatible_API_Call (
3008 l_api_version,
3009 p_api_version,
3010 l_api_name,
3011 G_PKG_NAME ) THEN
3012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3013 END IF;
3014
3015 -- Initialize message list if p_init_msg_list is set to TRUE
3016 IF FND_API.to_Boolean(p_init_msg_list) THEN
3017 FND_MSG_PUB.initialize;
3018 END IF;
3019
3020 -- Initialize API return status to success
3021 x_return_status := FND_API.G_RET_STS_SUCCESS;
3022 x_distribution_acct_id := -1;
3023
3024 l_stmt_num := 10;
3025 RCV_AccountHook_PUB.Get_Account(
3026 p_api_version => l_api_version,
3027 x_return_status => l_return_status,
3028 x_msg_count => l_msg_count,
3029 x_msg_data => l_msg_data,
3030 p_rcv_transaction_id => p_rcv_transaction_id,
3031 p_accounting_line_type => p_accounting_line_type,
3032 x_distribution_acct_id => l_dist_acct_id);
3033
3034 IF l_return_status <> FND_API.g_ret_sts_success THEN
3035 l_api_message := 'Error in Account Hook';
3036 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3037 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3038 ,'Get_HookAccount : '||l_stmt_num||' : '||l_api_message);
3039 END IF;
3040 RAISE FND_API.g_exc_unexpected_error;
3041 END IF;
3042
3043 IF(l_dist_acct_id <> -1) THEN
3044
3045 l_stmt_num := 20;
3046 SELECT count(*)
3047 INTO l_account_flag
3048 FROM gl_code_combinations GCC,
3049 cst_organization_definitions COD
3050 WHERE COD.operating_unit = p_org_id
3051 AND COD.chart_of_accounts_id = GCC.chart_of_accounts_id
3052 AND GCC.code_combination_id = l_dist_acct_id;
3053
3054 IF(l_account_flag = 0)THEN
3055 FND_MESSAGE.set_name('PO','PO_INVALID_ACCOUNT');
3056 FND_MSG_pub.add;
3057 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3058 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
3059 END IF;
3060 RAISE FND_API.g_exc_error;
3061 END IF;
3062 END IF;
3063
3064 x_distribution_acct_id := l_dist_acct_id;
3065
3066 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3067 l_api_message := 'x_distribution_acct_id : '||x_distribution_acct_id;
3068 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3069 ,l_api_message);
3070 END IF;
3071
3072 -- Standard check of p_commit
3073 IF FND_API.to_Boolean(p_commit) THEN
3074 COMMIT WORK;
3075 END IF;
3076
3077 -- Standard Call to get message count and if count = 1, get message info
3078 FND_MSG_PUB.Count_And_Get (
3079 p_count => x_msg_count,
3080 p_data => x_msg_data );
3081
3082 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3083 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
3084 ,'Get_HookAccount >>');
3085 END IF;
3086
3087
3088 EXCEPTION
3089 WHEN FND_API.g_exc_error THEN
3090 ROLLBACK TO Get_HookAccount_PVT;
3091 x_return_status := FND_API.g_ret_sts_error;
3092 FND_MSG_PUB.count_and_get
3093 ( p_count => x_msg_count
3094 , p_data => x_msg_data
3095 );
3096 WHEN FND_API.g_exc_unexpected_error THEN
3097 ROLLBACK TO Get_HookAccount_PVT;
3098 x_return_status := FND_API.g_ret_sts_unexp_error ;
3099 FND_MSG_PUB.count_and_get
3100 ( p_count => x_msg_count
3101 , p_data => x_msg_data
3102 );
3103
3104 WHEN OTHERS THEN
3105 ROLLBACK TO Get_HookAccount_PVT;
3106 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3107
3108 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3109 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3110 ,'Get_HookAccount : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
3111 END IF;
3112
3113 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3114 THEN
3115 FND_MSG_PUB.add_exc_msg
3116 ( G_PKG_NAME,
3117 l_api_name || 'Statement -'||to_char(l_stmt_num)
3118 );
3119 END IF;
3120 FND_MSG_PUB.count_and_get
3121 ( p_count => x_msg_count
3122 , p_data => x_msg_data
3123 );
3124
3125 END Get_HookAccount;
3126
3127
3128
3129
3130 -- Start of comments
3131 -- API name : Insert_RAEEvents
3132 -- Type : Private
3133 -- Function : To insert events into the Receiving Accounting Events table.
3134 -- Pre-reqs :
3135 -- Parameters :
3136 -- IN : p_api_version IN NUMBER Required
3137 -- p_init_msg_list IN VARCHAR2 Optional
3138 -- Default = FND_API.G_FALSE
3139 -- p_commit IN VARCHAR2 Optional
3140 -- Default = FND_API.G_FALSE
3141 -- p_validation_level IN NUMBER Optional
3142 -- Default = FND_API.G_VALID_LEVEL_FULL
3143 -- p_rcv_events_tbl IN RCV_SeedEvents_PVT.rcv_event_tbl_type Required
3144 --
3145 -- OUT : x_return_status OUT VARCHAR2(1)
3146 -- x_msg_count OUT NUMBER
3147 -- x_msg_data OUT VARCHAR2(2000)
3148 -- Version :
3149 -- Initial version 1.0
3150 --
3151 --
3152 -- Notes : This API inserts all events for a given receiving transaction
3153 -- into RCV_ACCOUNTING_EVENTS.
3154 --
3155 -- End of comments
3156 PROCEDURE Insert_RAEEvents(
3157 p_api_version IN NUMBER,
3158 p_init_msg_list IN VARCHAR2,
3159 p_commit IN VARCHAR2,
3160 p_validation_level IN NUMBER,
3161 x_return_status OUT NOCOPY VARCHAR2,
3162 x_msg_count OUT NOCOPY NUMBER,
3163 x_msg_data OUT NOCOPY VARCHAR2,
3164
3165 p_rcv_events_tbl IN RCV_SeedEvents_PVT.rcv_event_tbl_type,
3166 /* Support for Landed Cost Management */
3167 p_lcm_flag IN VARCHAR2
3168 ) IS
3169 l_api_name CONSTANT VARCHAR2(30) := 'Insert_RAEEvents';
3170 l_api_version CONSTANT NUMBER := 1.0;
3171
3172 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3173 l_msg_count NUMBER := 0;
3174 l_msg_data VARCHAR2(8000) := '';
3175 l_stmt_num NUMBER := 0;
3176 l_api_message VARCHAR2(1000);
3177
3178 l_summarize_acc_flag VARCHAR2(1) := 'N';
3179
3180 l_err_num NUMBER;
3181 l_err_code VARCHAR2(240);
3182 l_err_msg VARCHAR2(240);
3183 l_return_code NUMBER;
3184
3185 l_rcv_transaction_id NUMBER;
3186 l_del_transaction_id NUMBER;
3187 l_detail_accounting_flag VARCHAR2(1) := 'Y';
3188 l_accrue_on_receipt_flag VARCHAR2(1) := 'N';
3189 l_accounting_event_id NUMBER;
3190
3191 l_ctr_first NUMBER;
3192
3193 BEGIN
3194 -- Standard start of API savepoint
3195 SAVEPOINT Insert_RAEEvents_PVT;
3196
3197 l_stmt_num := 0;
3198
3199 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3200 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
3201 ,'Insert_RAEEvents <<');
3202
3203 END IF;
3204
3205 -- Standard call to check for call compatibility
3206 IF NOT FND_API.Compatible_API_Call (
3207 l_api_version,
3208 p_api_version,
3209 l_api_name,
3210 G_PKG_NAME ) THEN
3211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3212 END IF;
3213
3214 -- Initialize message list if p_init_msg_list is set to TRUE
3215 IF FND_API.to_Boolean(p_init_msg_list) THEN
3216 FND_MSG_PUB.initialize;
3217 END IF;
3218
3219 -- Initialize API return status to success
3220 x_return_status := FND_API.G_RET_STS_SUCCESS;
3221
3222 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3223 l_api_message := 'Inserting '||p_rcv_events_tbl.count||' events into RAE';
3224 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3225 ,l_api_message);
3226 END IF;
3227
3228 l_ctr_first := p_rcv_events_tbl.FIRST;
3229
3230 -- Check for accrual option. If accrual option is set to accrue at period-end, don't call the
3231 -- accounting API.
3232 l_stmt_num := 20;
3233 SELECT nvl(poll.accrue_on_receipt_flag, 'N')
3234 INTO l_accrue_on_receipt_flag
3235 FROM po_line_locations POLL
3236 WHERE POLL.line_location_id = p_rcv_events_tbl(l_ctr_first).po_line_location_id;
3237
3238 FOR i IN p_rcv_events_tbl.FIRST..p_rcv_events_tbl.LAST LOOP
3239 l_stmt_num := 30;
3240 SELECT rcv_accounting_event_s.nextval
3241 INTO l_accounting_event_id
3242 FROM dual;
3243
3244 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3245 l_api_message := SUBSTR('i : '||i||
3246 'accounting_event_id : '||l_accounting_event_id||
3247 'rcv_transaction_id : '||p_rcv_events_tbl(i).rcv_transaction_id||
3248 'po_line_id : '||p_rcv_events_tbl(i).po_line_id||
3249 'po_dist_id : '||p_rcv_events_tbl(i).po_distribution_id||
3250 'unit_price : '||p_rcv_events_tbl(i).unit_price||
3251 'currency : '||p_rcv_events_tbl(i).currency_code||
3252 'nr tax : '||p_rcv_events_tbl(i).unit_nr_tax||
3253 'rec tax : '||p_rcv_events_tbl(i).unit_rec_tax,1,1000);
3254 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3255 ,l_api_message);
3256 END IF;
3257
3258 -- We are not doing a bulk insert due to a database limitation. On databases
3259 -- prior to 9i, you cannot do a bulk insert using a table of records. You have to
3260 -- multiple tables of scalar types. The expense of converting the table of records
3261 -- to multiple tables is not worthwhile in this case since we do not expect the
3262 -- number of rows to exceed 10.
3263 l_stmt_num := 40;
3264 INSERT into RCV_ACCOUNTING_EVENTS(
3265 accounting_event_id,
3266 last_update_date,
3267 last_updated_by,
3268 last_update_login,
3269 creation_date,
3270 created_by,
3271 request_id,
3272 program_application_id,
3273 program_id,
3274 program_udpate_date,
3275 rcv_transaction_id,
3276 event_type_id,
3277 event_source,
3278 event_source_id,
3279 set_of_books_id,
3280 org_id,
3281 transfer_org_id,
3282 organization_id,
3283 transfer_organization_id,
3284 debit_account_id,
3285 credit_account_id,
3286 /* Support for Landed Cost Management */
3287 lcm_account_id,
3288 transaction_date,
3289 source_doc_quantity,
3290 transaction_quantity,
3291 primary_quantity,
3292 source_doc_unit_of_measure,
3293 transaction_unit_of_measure,
3294 primary_unit_of_measure,
3295 po_header_id,
3296 po_release_id,
3297 po_line_id,
3298 po_line_location_id,
3299 po_distribution_id,
3300 inventory_item_id,
3301 unit_price,
3302 prior_unit_price,
3303 intercompany_pricing_option,
3304 transaction_amount,
3305 nr_tax,
3306 rec_tax,
3307 nr_tax_amount,
3308 rec_tax_amount,
3309 prior_nr_tax,
3310 prior_rec_tax,
3311 currency_code,
3312 currency_conversion_type,
3313 currency_conversion_rate,
3314 currency_conversion_date,
3315 accounted_flag,
3316 procurement_org_flag,
3317 cross_ou_flag,
3318 trx_flow_header_id,
3319 invoiced_flag,
3320 pa_addition_flag,
3321 /* Support for Landed Cost Management */
3322 unit_landed_cost)
3323 (SELECT
3324 l_accounting_event_id,
3325 sysdate,
3326 fnd_global.user_id,
3327 fnd_global.login_id,
3328 sysdate,
3329 fnd_global.user_id,
3330 fnd_global.conc_request_id,
3331 fnd_global.prog_appl_id,
3332 fnd_global.conc_program_id,
3333 sysdate,
3334 p_rcv_events_tbl(i).rcv_transaction_id,
3335 p_rcv_events_tbl(i).event_type_id,
3336 p_rcv_events_tbl(i).event_source,
3337 decode(p_rcv_events_tbl(i).event_source,
3338 'INVOICEMATCH', p_rcv_events_tbl(i).inv_distribution_id,
3339 p_rcv_events_tbl(i).rcv_transaction_id),
3340 p_rcv_events_tbl(i).set_of_books_id,
3341 p_rcv_events_tbl(i).org_id,
3342 p_rcv_events_tbl(i).transfer_org_id,
3343 p_rcv_events_tbl(i).organization_id,
3344 p_rcv_events_tbl(i).transfer_organization_id,
3345 p_rcv_events_tbl(i).debit_account_id,
3346 p_rcv_events_tbl(i).credit_account_id,
3347 /* Support for Landed Cost Management */
3348 p_rcv_events_tbl(i).lcm_account_id,
3349 p_rcv_events_tbl(i).transaction_date,
3350 decode(p_rcv_events_tbl(i).service_flag, 'N',
3351 p_rcv_events_tbl(i).source_doc_quantity , NULL) source_doc_quantity,
3352 decode(p_rcv_events_tbl(i).service_flag, 'N',
3353 p_rcv_events_tbl(i).transaction_quantity , NULL) transaction_quantity,
3354 decode(p_rcv_events_tbl(i).service_flag, 'N',
3355 p_rcv_events_tbl(i).primary_quantity , NULL) primary_quantity,
3356 p_rcv_events_tbl(i).source_doc_uom,
3357 p_rcv_events_tbl(i).transaction_uom,
3358 p_rcv_events_tbl(i).primary_uom,
3359 p_rcv_events_tbl(i).po_header_id,
3360 p_rcv_events_tbl(i).po_release_id,
3361 p_rcv_events_tbl(i).po_line_id,
3362 p_rcv_events_tbl(i).po_line_location_id,
3363 p_rcv_events_tbl(i).po_distribution_id,
3364 p_rcv_events_tbl(i).item_id,
3365 decode(p_rcv_events_tbl(i).service_flag, 'N',
3366 p_rcv_events_tbl(i).unit_price + p_rcv_events_tbl(i).unit_nr_tax, NULL) unit_price,
3367 decode(p_rcv_events_tbl(i).event_source,'RETROPRICE',
3368 p_rcv_events_tbl(i).prior_unit_price + p_rcv_events_tbl(i).prior_nr_tax,NULL),
3369 p_rcv_events_tbl(i).intercompany_pricing_option,
3370 decode(p_rcv_events_tbl(i).service_flag,'Y',
3371 p_rcv_events_tbl(i).transaction_amount+
3372 p_rcv_events_tbl(i).transaction_amount * p_rcv_events_tbl(i).unit_nr_tax, NULL),
3373 decode(p_rcv_events_tbl(i).service_flag, 'N',
3374 p_rcv_events_tbl(i).unit_nr_tax,NULL),
3375 decode(p_rcv_events_tbl(i).service_flag, 'N',
3376 p_rcv_events_tbl(i).unit_rec_tax,NULL),
3377 decode(p_rcv_events_tbl(i).service_flag, 'Y',
3378 p_rcv_events_tbl(i).transaction_amount*p_rcv_events_tbl(i).unit_nr_tax,NULL),
3379 decode(p_rcv_events_tbl(i).service_flag, 'Y',
3380 p_rcv_events_tbl(i).transaction_amount*p_rcv_events_tbl(i).unit_rec_tax,NULL),
3381 decode(p_rcv_events_tbl(i).event_source,'RETROPRICE',
3382 p_rcv_events_tbl(i).prior_nr_tax,NULL),
3383 decode(p_rcv_events_tbl(i).event_source,'RETROPRICE',
3384 p_rcv_events_tbl(i).prior_rec_tax,NULL),
3385 p_rcv_events_tbl(i).Currency_code,
3386 p_rcv_events_tbl(i).Currency_conversion_type,
3387 p_rcv_events_tbl(i).Currency_conversion_rate,
3388 p_rcv_events_tbl(i).Currency_conversion_date,
3389 'N',
3390 p_rcv_events_tbl(i).procurement_org_flag,
3391 p_rcv_events_tbl(i).Cross_ou_flag,
3392 p_rcv_events_tbl(i).trx_flow_header_id,
3393 decode(p_rcv_events_tbl(i).event_type_id,
3394 RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,'N',
3395 RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL,'N',NULL),
3396 'N', -- Will be changed by PA process
3397 /* Support for Landed Cost Management */
3398 p_rcv_events_tbl(i).unit_landed_cost
3399 FROM DUAL);
3400
3401 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3402 l_api_message := 'Inserted '||SQL%ROWCOUNT||
3403 'rows in RAE for org '||p_rcv_events_tbl(i).org_id;
3404 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3405 ,l_api_message);
3406 END IF;
3407
3408 /* For accrue on receipt POs, call the Create_Accounting API to generate accounting entries
3409 online. For period-end POs that are not global procurement scenarios, the accounting will
3410 be done by the period end accruals process. For global procurement scenarios, the accounting
3411 for the procurement org will be done at period end. For all other orgs, the accounting will
3412 be done online. */
3413 IF ((l_accrue_on_receipt_flag = 'Y' OR
3414 p_rcv_events_tbl(i).procurement_org_flag = 'N') AND
3415 p_rcv_events_tbl(i).event_type_id NOT IN
3416 (RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)) THEN
3417 l_stmt_num := 50;
3418 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3419 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3420 ,'Creating accounting entries in RRS');
3421 END IF;
3422
3423 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3424 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3425 ,'Creating accounting entries for accounting_event_id : '||l_accounting_event_id);
3426 END IF;
3427
3428 -- Call Account generation API to create accounting entries
3429 RCV_CreateAccounting_PVT.Create_AccountingEntry(
3430 p_api_version => 1.0,
3431 x_return_status => l_return_status,
3432 x_msg_count => l_msg_count,
3433 x_msg_data => l_msg_data,
3434 p_accounting_event_id => l_accounting_event_id,
3435 /* Support for Landed Cost Management */
3436 p_lcm_flag => p_lcm_flag);
3437 IF l_return_status <> FND_API.g_ret_sts_success THEN
3438 l_api_message := 'Error in Create_AccountingEntry API';
3439 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3440 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3441 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3442 END IF;
3443 RAISE FND_API.g_exc_unexpected_error;
3444 END IF;
3445
3446 -- Call PA API to update pa_addition_flag - bug 5074573 (fp of 4409125)
3447 l_stmt_num := 55;
3448
3449 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3450 l_api_message := 'Calling PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg with '
3451 || 'p_api_version = '|| '1.0'
3452 || 'p_rcv_transaction_id = ' || p_rcv_events_tbl(i).rcv_transaction_id
3453 || 'p_po_distribution_id = ' || p_rcv_events_tbl(i).po_distribution_id
3454 || 'p_accounting_event_id = ' || l_accounting_event_id;
3455 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3456 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3457 END IF;
3458
3459 PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg (
3460 p_api_version => 1.0,
3461 x_return_status => l_return_status,
3462 x_msg_count => l_msg_count,
3463 x_msg_data => l_msg_data,
3464 p_rcv_transaction_id => p_rcv_events_tbl(i).rcv_transaction_id,
3465 p_po_distribution_id => p_rcv_events_tbl(i).po_distribution_id,
3466 p_accounting_event_id => l_accounting_event_id);
3467
3468 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3469 l_api_message := 'PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg returned with '
3470 || 'x_return_status = '|| l_return_status
3471 || 'x_msg_count = ' || l_msg_count
3472 || 'x_msg_data = ' || l_msg_data;
3473 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3474 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3475 END IF;
3476
3477 IF l_return_status <> FND_API.g_ret_sts_success THEN
3478 l_api_message := 'Error in PA_PO_INTEGRATION_UTILS.Update_PA_Addition_Flg API';
3479 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3480 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3481 ,'Insert_RAEEvents : '||l_stmt_num||' : '||l_api_message);
3482 END IF;
3483 RAISE FND_API.g_exc_unexpected_error;
3484 END IF;
3485
3486 END IF;
3487 END LOOP;
3488
3489
3490
3491 --- Standard check of p_commit
3492 IF FND_API.to_Boolean(p_commit) THEN
3493 COMMIT WORK;
3494 END IF;
3495
3496 -- Standard Call to get message count and if count = 1, get message info
3497 FND_MSG_PUB.Count_And_Get (
3498 p_count => x_msg_count,
3499 p_data => x_msg_data );
3500
3501 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3502 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
3503 ,'Insert_RAEEvents >>');
3504 END IF;
3505
3506
3507 EXCEPTION
3508 WHEN FND_API.g_exc_error THEN
3509 ROLLBACK TO Insert_RAEEvents_PVT;
3510 x_return_status := FND_API.g_ret_sts_error;
3511 FND_MSG_PUB.count_and_get
3512 ( p_count => x_msg_count
3513 , p_data => x_msg_data
3514 );
3515
3516 WHEN FND_API.g_exc_unexpected_error THEN
3517 ROLLBACK TO Insert_RAEEvents_PVT;
3518 x_return_status := FND_API.g_ret_sts_unexp_error ;
3519 FND_MSG_PUB.count_and_get
3520 ( p_count => x_msg_count
3521 , p_data => x_msg_data
3522 );
3523
3524 WHEN OTHERS THEN
3525 ROLLBACK TO Insert_RAEEvents_PVT;
3526 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3527
3528 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3529 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3530 ,'Insert_RAEEvents : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
3531 END IF;
3532
3533 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3534 THEN
3535 FND_MSG_PUB.add_exc_msg
3536 ( G_PKG_NAME,
3537 l_api_name || 'Statement -'||to_char(l_stmt_num)
3538 );
3539 END IF;
3540 FND_MSG_PUB.count_and_get
3541 ( p_count => x_msg_count
3542 , p_data => x_msg_data
3543 );
3544
3545 END Insert_RAEEvents;
3546
3547 -- Start of comments
3548 -- API name : Check_EncumbranceFlag
3549 -- Type : Private
3550 -- Function : Checks to see if encumbrance entries need to be created.
3551 -- Pre-reqs :
3552 -- Parameters :
3553 -- IN : p_api_version IN NUMBER Required
3554 -- p_init_msg_list IN VARCHAR2 Optional
3555 -- Default = FND_API.G_FALSE
3556 -- p_commit IN VARCHAR2 Optional
3557 -- Default = FND_API.G_FALSE
3558 -- p_validation_level IN NUMBER Optional
3559 -- Default = FND_API.G_VALID_LEVEL_FULL
3560 -- p_rcv_sob_id IN NUMBER Required
3561 -- p_po_header_id IN NUMBER Required
3562 --
3563 -- x_encumbrance_flag OUT VARCHAR2(1)
3564 -- x_ussgl_option OUT VARCHAR2(1)
3565 --
3566 --
3567 -- OUT : x_return_status OUT VARCHAR2(1)
3568 -- x_msg_count OUT NUMBER
3569 -- x_msg_data OUT VARCHAR2(2000)
3570 -- Version :
3571 -- Initial version 1.0
3572 --
3573 --
3574 -- Notes : This API checks to see if encumbrance entries need to
3575 -- be created.
3576 --
3577 -- End of comments
3578 PROCEDURE Check_EncumbranceFlag(
3579 p_api_version IN NUMBER,
3580 p_init_msg_list IN VARCHAR2,
3581 p_commit IN VARCHAR2,
3582 p_validation_level IN NUMBER,
3583 x_return_status OUT NOCOPY VARCHAR2,
3584 x_msg_count OUT NOCOPY NUMBER,
3585 x_msg_data OUT NOCOPY VARCHAR2,
3586
3587 p_rcv_sob_id IN NUMBER,
3588
3589 x_encumbrance_flag OUT NOCOPY VARCHAR2,
3590 x_ussgl_option OUT NOCOPY VARCHAR2
3591
3592 ) IS
3593 l_api_name CONSTANT VARCHAR2(30) := 'Check_EncumbranceFlag';
3594 l_api_version CONSTANT NUMBER := 1.0;
3595
3596 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3597 l_msg_count NUMBER := 0;
3598 l_msg_data VARCHAR2(8000) := '';
3599 l_stmt_num NUMBER := 0;
3600 l_api_message VARCHAR2(1000);
3601
3602 l_encumbrance_flag VARCHAR2(1);
3603
3604 BEGIN
3605 -- Standard start of API savepoint
3606 SAVEPOINT Check_EncumbranceFlag_PVT;
3607
3608 l_stmt_num := 0;
3609
3610 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3611 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
3612 ,'Check_EncumbranceFlag <<');
3613 END IF;
3614
3615 -- Standard call to check for call compatibility
3616 IF NOT FND_API.Compatible_API_Call (
3617 l_api_version,
3618 p_api_version,
3619 l_api_name,
3620 G_PKG_NAME ) THEN
3621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3622 END IF;
3623
3624 -- Initialize message list if p_init_msg_list is set to TRUE
3625 IF FND_API.to_Boolean(p_init_msg_list) THEN
3626 FND_MSG_PUB.initialize;
3627 END IF;
3628
3629 -- Initialize API return status to success
3630 x_return_status := FND_API.G_RET_STS_SUCCESS;
3631
3632 l_stmt_num := 10;
3633 SELECT nvl(FSP.purch_encumbrance_flag, 'N')
3634 INTO l_encumbrance_flag
3635 FROM financials_system_parameters FSP
3636 WHERE FSP.set_of_books_id = p_rcv_sob_id;
3637
3638 x_encumbrance_flag := l_encumbrance_flag;
3639 x_ussgl_option := NVL(FND_PROFILE.VALUE('USSGL_OPTION'),'N');
3640
3641
3642 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3643 l_api_message := 'Encumbrance Flag : '||x_encumbrance_flag||
3644 ' Ussgl Option : '||x_ussgl_option;
3645 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
3646 ,l_api_message);
3647 END IF;
3648
3649
3650 -- Standard check of p_commit
3651 IF FND_API.to_Boolean(p_commit) THEN
3652 COMMIT WORK;
3653 END IF;
3654
3655 -- Standard Call to get message count and if count = 1, get message info
3656 FND_MSG_PUB.Count_And_Get (
3657 p_count => x_msg_count,
3658 p_data => x_msg_data );
3659
3660 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3661 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
3662 ,'Check_EncumbranceFlag >>');
3663 END IF;
3664
3665
3666 EXCEPTION
3667 WHEN FND_API.g_exc_error THEN
3668 ROLLBACK TO Check_EncumbranceFlag_PVT;
3669 x_return_status := FND_API.g_ret_sts_error;
3670 FND_MSG_PUB.count_and_get
3671 ( p_count => x_msg_count
3672 , p_data => x_msg_data
3673 );
3674 WHEN FND_API.g_exc_unexpected_error THEN
3675 ROLLBACK TO Check_EncumbranceFlag_PVT;
3676 x_return_status := FND_API.g_ret_sts_unexp_error ;
3677 FND_MSG_PUB.count_and_get
3678 ( p_count => x_msg_count
3679 , p_data => x_msg_data
3680 );
3681
3682 WHEN OTHERS THEN
3683 ROLLBACK TO Check_EncumbranceFlag_PVT;
3684 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3685
3686 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3687 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
3688 ,'Check_EncumbranceFlag : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
3689 END IF;
3690
3691 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3692 THEN
3693 FND_MSG_PUB.add_exc_msg
3694 ( G_PKG_NAME,
3695 l_api_name || 'Statement -'||to_char(l_stmt_num)
3696 );
3697 END IF;
3698 FND_MSG_PUB.count_and_get
3699 ( p_count => x_msg_count
3700 , p_data => x_msg_data
3701 );
3702
3703 END Check_EncumbranceFlag;
3704
3705 -----------------------------------------------------------------------------
3706 -- Start of comments --
3707 -- --
3708 -- PROCEDURE --
3709 -- Insert_MMTEvents This API takes a PL/SQL table as input that has one --
3710 -- entry for each RAE event. It loops through the table --
3711 -- and calls Create_MMTRecord to create logical MMT --
3712 -- transactions as appropriate for each event. --
3713 -- --
3714 -- VERSION 1.0 --
3715 -- --
3716 -- PARAMETERS --
3717 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
3718 -- P_INIT_MSG_LIST Initialize message list? True/False --
3719 -- P_COMMIT Should the API commit before returning? True/False --
3720 -- P_VALIDATION_LEVEL Specify the level of validation on the inputs --
3721 -- P_RCV_EVENTS_TBL Collection of events of type rcv_event_rec_type --
3722 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
3723 -- X_MSG_COUNT Message Count - # of messages placed in message list--
3724 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
3725 -- --
3726 -- HISTORY: --
3727 -- 06/26/03 Bryan Kuntz Created --
3728 -- End of comments --
3729 -----------------------------------------------------------------------------
3730
3731 PROCEDURE Insert_MMTEvents (
3732 P_API_VERSION IN NUMBER,
3733 P_INIT_MSG_LIST IN VARCHAR2,
3734 P_COMMIT IN VARCHAR2,
3735 P_VALIDATION_LEVEL IN NUMBER,
3736 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3737 X_MSG_COUNT OUT NOCOPY NUMBER,
3738 X_MSG_DATA OUT NOCOPY VARCHAR2,
3739 P_RCV_EVENTS_TBL IN RCV_SeedEvents_PVT.rcv_event_tbl_type
3740 ) IS
3741 l_api_name CONSTANT VARCHAR2(30) := 'Insert_MMTEvents';
3742 l_api_version CONSTANT NUMBER := 1.0;
3743 l_api_message VARCHAR2(1000);
3744
3745 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3746 l_msg_count NUMBER := 0;
3747 l_msg_data VARCHAR2(8000) := '';
3748 l_stmt_num NUMBER := 0;
3749
3750 l_ctr BINARY_INTEGER;
3751 l_inv_trx_tbl INV_Logical_Transaction_Global.mtl_trx_tbl_type;
3752 l_inv_trx_tbl_ctr BINARY_INTEGER;
3753 l_correct_ind BOOLEAN := FALSE; -- indicator variable for whether these
3754 -- events are for a correction or not
3755 l_rcv_txn_type RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
3756 l_parent_txn_flag NUMBER := 1;
3757 l_intercompany_price NUMBER; -- may include nr tax depending on the pricing option
3758 l_intercompany_curr_code RCV_ACCOUNTING_EVENTS.CURRENCY_CODE%TYPE;
3759 l_transfer_organization_id NUMBER := NULL;
3760
3761 invalid_event EXCEPTION;
3762 BEGIN
3763
3764 -- Standard start of API savepoint
3765 SAVEPOINT Insert_MMTEvents_PVT;
3766
3767 l_stmt_num := 0;
3768
3769 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3770 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.begin'
3771 ,'Insert_MMTEvents <<');
3772 END IF;
3773
3774 -- Standard call to check for call compatibility
3775 IF NOT FND_API.Compatible_API_Call (
3776 l_api_version,
3777 p_api_version,
3778 l_api_name,
3779 G_PKG_NAME ) THEN
3780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3781 END IF;
3782
3783 -- Initialize message list if p_init_msg_list is set to TRUE
3784 IF FND_API.to_Boolean(p_init_msg_list) THEN
3785 FND_MSG_PUB.initialize;
3786 END IF;
3787
3788 -- Initialize API return status to success
3789 x_return_status := FND_API.G_RET_STS_SUCCESS;
3790 x_msg_count := 0;
3791 x_msg_data := '';
3792
3793 -- API Body
3794 -- Initialize counters
3795 l_inv_trx_tbl_ctr := 0;
3796 l_ctr := p_rcv_events_tbl.FIRST;
3797
3798 l_stmt_num := 10;
3799 -- Determine if this group of events are for a CORRECT txn type
3800 SELECT transaction_type
3801 INTO l_rcv_txn_type
3802 FROM rcv_transactions
3803 WHERE transaction_id = p_rcv_events_tbl(l_ctr).rcv_transaction_id;
3804
3805 if (l_rcv_txn_type = 'CORRECT') then
3806 l_correct_ind := TRUE;
3807 end if;
3808
3809 -- Loop for every event in the table
3810 WHILE l_ctr <= p_rcv_events_tbl.LAST LOOP
3811
3812 -- Logical Events are only seeded in Receiving but not in Inventory for :
3813 -- 1. Expense destination types for one-time items
3814 -- 2. Shop Floor destination types (for both OSP and direct items).
3815 IF(p_rcv_events_tbl(l_ctr).destination_type_code <> 'SHOP FLOOR' AND
3816 (p_rcv_events_tbl(l_ctr).destination_type_code <> 'EXPENSE' OR
3817 p_rcv_events_tbl(l_ctr).item_id IS NOT NULL)) THEN
3818
3819 IF (p_rcv_events_tbl(l_ctr).ship_to_org_flag = 'N') then
3820
3821 -- For RAE events, the transfer_organization_id represents the organization from
3822 -- where the transfer price is derived. Hence in the flow :
3823 -- OU2 <-------- OU1 <--------- Supplier
3824 -- The Logical Receive in OU1 will be at PO price and the transfer_org will be NULL.
3825 -- The Recieve in OU2 could be at transfer price between OU1 and OU2. Hence trasnfer
3826 -- org will be OU1.
3827 -- However, in Inventory the Logical Receive in RAE translates to a Logical PO Receipt
3828 -- and a Logical I/C Sales Issue. The Logical I/C Sales event could be at transfer price.
3829 -- The transfer organization should therefore be picked up from the next event. To keep
3830 -- the values for the Logical PO Receipt and the Logical I/C Sales issue consistent, we
3831 -- will follow this logic for both transactions.
3832
3833 l_transfer_organization_id := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).organization_id;
3834
3835 IF (p_rcv_events_tbl(l_ctr).event_type_id = RCV_SeedEvents_PVT.LOGICAL_RECEIVE) THEN
3836
3837 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
3838 IF (p_rcv_events_tbl(l_ctr).intercompany_pricing_option = 2) then
3839 l_intercompany_price := p_rcv_events_tbl(l_ctr).intercompany_price;
3840 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).intercompany_curr_code;
3841 ELSE
3842 l_intercompany_price := p_rcv_events_tbl(l_ctr).unit_price + p_rcv_events_tbl(l_ctr).unit_nr_tax;
3843 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).currency_code;
3844 END IF;
3845
3846 IF (l_correct_ind) THEN
3847 l_stmt_num := 20;
3848 Create_MMTRecord(p_api_version => 1.0,
3849 p_rcv_event => p_rcv_events_tbl(l_ctr),
3850 p_txn_type_id => 69,
3851 p_intercompany_price => l_intercompany_price,
3852 p_intercompany_curr_code => l_intercompany_curr_code,
3853 p_acct_id => p_rcv_events_tbl(l_ctr).debit_account_id,
3854 p_sign => sign(p_rcv_events_tbl(l_ctr).transaction_quantity),
3855 p_parent_txn_flag => l_parent_txn_flag,
3856 p_transfer_organization_id => l_transfer_organization_id,
3857 x_return_status => l_return_status,
3858 x_msg_count => l_msg_count,
3859 x_msg_data => l_msg_data,
3860 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3861 ELSIF (p_rcv_events_tbl(l_ctr).procurement_org_flag = 'Y') THEN
3862 l_stmt_num := 30;
3863 Create_MMTRecord(p_api_version => 1.0,
3864 p_rcv_event => p_rcv_events_tbl(l_ctr),
3865 p_txn_type_id => 19, -- Logical PO Receipt
3866 p_intercompany_price => l_intercompany_price,
3867 p_intercompany_curr_code => l_intercompany_curr_code,
3868 p_acct_id => p_rcv_events_tbl(l_ctr).debit_account_id,
3869 p_sign => 1,
3870 p_parent_txn_flag => l_parent_txn_flag,
3871 p_transfer_organization_id => l_transfer_organization_id,
3872 x_return_status => l_return_status,
3873 x_msg_count => l_msg_count,
3874 x_msg_data => l_msg_data,
3875 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3876 ELSE
3877 l_stmt_num := 40;
3878 Create_MMTRecord(p_api_version => 1.0,
3879 p_rcv_event => p_rcv_events_tbl(l_ctr),
3880 p_txn_type_id => 22, -- Logical I/C Procurement Receipt
3881 p_intercompany_price => l_intercompany_price,
3882 p_intercompany_curr_code => l_intercompany_curr_code,
3883 p_acct_id => p_rcv_events_tbl(l_ctr).debit_account_id,
3884 p_sign => 1,
3885 p_parent_txn_flag => l_parent_txn_flag,
3886 p_transfer_organization_id => l_transfer_organization_id,
3887 x_return_status => l_return_status,
3888 x_msg_count => l_msg_count,
3889 x_msg_data => l_msg_data,
3890 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3891 END IF;
3892
3893 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3894 RAISE FND_API.g_exc_error;
3895 END IF;
3896
3897 l_stmt_num := 50;
3898 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
3899 IF (p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_pricing_option = 2) THEN
3900 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_price;
3901 l_intercompany_curr_code := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_curr_code;
3902 ELSE
3903 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_price +
3904 p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_nr_tax;
3905 l_intercompany_curr_code :=p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).currency_code;
3906 END IF;
3907
3908 IF (p_rcv_events_tbl(l_ctr).transaction_quantity > 0) THEN
3909 l_stmt_num := 60;
3910 Create_MMTRecord(p_api_version => 1.0,
3911 p_rcv_event => p_rcv_events_tbl(l_ctr),
3912 p_txn_type_id => 11, -- Logical I/C Sales Issue
3913 p_intercompany_price => l_intercompany_price,
3914 p_intercompany_curr_code => l_intercompany_curr_code,
3915 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
3916 p_sign => -1,
3917 p_parent_txn_flag => 0,
3918 p_transfer_organization_id => l_transfer_organization_id,
3919 x_return_status => l_return_status,
3920 x_msg_count => l_msg_count,
3921 x_msg_data => l_msg_data,
3922 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3923 ELSE
3924 l_stmt_num := 70;
3925 Create_MMTRecord(p_api_version => 1.0,
3926 p_rcv_event => p_rcv_events_tbl(l_ctr),
3927 p_txn_type_id => 14, -- Logical I/C Sales Return
3928 p_intercompany_price => l_intercompany_price,
3929 p_intercompany_curr_code => l_intercompany_curr_code,
3930 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
3931 p_sign => 1,
3932 p_parent_txn_flag => 0,
3933 p_transfer_organization_id => l_transfer_organization_id,
3934 x_return_status => l_return_status,
3935 x_msg_count => l_msg_count,
3936 x_msg_data => l_msg_data,
3937 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3938 END IF;
3939 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3940 RAISE FND_API.g_exc_error;
3941 END IF;
3942
3943 ELSIF (p_rcv_events_tbl(l_ctr).event_type_id = RCV_SeedEvents_PVT.LOGICAL_RETURN_TO_VENDOR) THEN
3944 l_stmt_num := 80;
3945 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
3946 IF (p_rcv_events_tbl(l_ctr).intercompany_pricing_option = 2) then
3947 l_intercompany_price := p_rcv_events_tbl(l_ctr).intercompany_price;
3948 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).intercompany_curr_code;
3949 ELSE
3950 l_intercompany_price := p_rcv_events_tbl(l_ctr).unit_price + p_rcv_events_tbl(l_ctr).unit_nr_tax;
3951 l_intercompany_curr_code := p_rcv_events_tbl(l_ctr).currency_code;
3952 END IF;
3953
3954 IF (l_correct_ind) THEN
3955 l_stmt_num := 90;
3956 Create_MMTRecord(p_api_version => 1.0,
3957 p_rcv_event => p_rcv_events_tbl(l_ctr),
3958 p_txn_type_id => 69,
3959 p_intercompany_price => l_intercompany_price,
3960 p_intercompany_curr_code => l_intercompany_curr_code,
3961 p_acct_id => p_rcv_events_tbl(l_ctr).credit_account_id,
3962 p_sign => -1*sign(p_rcv_events_tbl(l_ctr).transaction_quantity),
3963 p_parent_txn_flag => l_parent_txn_flag,
3964 p_transfer_organization_id => l_transfer_organization_id,
3965 x_return_status => l_return_status,
3966 x_msg_count => l_msg_count,
3967 x_msg_data => l_msg_data,
3968 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3969
3970 ELSIF (p_rcv_events_tbl(l_ctr).procurement_org_flag = 'Y') THEN
3971 l_stmt_num := 100;
3972 Create_MMTRecord(p_api_version => 1.0,
3973 p_rcv_event => p_rcv_events_tbl(l_ctr),
3974 p_txn_type_id => 39, -- Logical RTV
3975 p_intercompany_price => l_intercompany_price,
3976 p_intercompany_curr_code => l_intercompany_curr_code,
3977 p_acct_id => p_rcv_events_tbl(l_ctr).credit_account_id,
3978 p_sign => -1,
3979 p_parent_txn_flag => l_parent_txn_flag,
3980 p_transfer_organization_id => l_transfer_organization_id,
3981 x_return_status => l_return_status,
3982 x_msg_count => l_msg_count,
3983 x_msg_data => l_msg_data,
3984 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
3985 ELSE
3986 l_stmt_num := 110;
3987 Create_MMTRecord(p_api_version => 1.0,
3988 p_rcv_event => p_rcv_events_tbl(l_ctr),
3989 p_txn_type_id => 23, -- Logical I/C Procurement Return
3990 p_intercompany_price => l_intercompany_price,
3991 p_intercompany_curr_code => l_intercompany_curr_code,
3992 p_acct_id => p_rcv_events_tbl(l_ctr).credit_account_id,
3993 p_sign => -1,
3994 p_parent_txn_flag => l_parent_txn_flag,
3995 p_transfer_organization_id => l_transfer_organization_id,
3996 x_return_status => l_return_status,
3997 x_msg_count => l_msg_count,
3998 x_msg_data => l_msg_data,
3999 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
4000 END IF;
4001
4002 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4003 RAISE FND_API.g_exc_error;
4004 END IF;
4005
4006 l_stmt_num := 120;
4007 l_inv_trx_tbl_ctr := l_inv_trx_tbl_ctr + 1;
4008
4009 IF (p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_pricing_option = 2) then
4010 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_price;
4011 l_intercompany_curr_code := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).intercompany_curr_code;
4012 ELSE
4013 l_intercompany_price := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_price +
4014 p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).unit_nr_tax;
4015 l_intercompany_curr_code := p_rcv_events_tbl(p_rcv_events_tbl.NEXT(l_ctr)).currency_code;
4016 END IF;
4017
4018 IF (p_rcv_events_tbl(l_ctr).transaction_quantity > 0) THEN
4019 l_stmt_num := 130;
4020 Create_MMTRecord(p_api_version => 1.0,
4021 p_rcv_event => p_rcv_events_tbl(l_ctr),
4022 p_txn_type_id => 14, -- Logical I/C Sales Return
4023 p_intercompany_price => l_intercompany_price,
4024 p_intercompany_curr_code => l_intercompany_curr_code,
4025 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
4026 p_sign => 1,
4027 p_parent_txn_flag => 0,
4028 p_transfer_organization_id => l_transfer_organization_id,
4029 x_return_status => l_return_status,
4030 x_msg_count => l_msg_count,
4031 x_msg_data => l_msg_data,
4032 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
4033 else
4034 l_stmt_num := 140;
4035 Create_MMTRecord(p_api_version => 1.0,
4036 p_rcv_event => p_rcv_events_tbl(l_ctr),
4037 p_txn_type_id => 11, -- Logical I/C Sales Issue
4038 p_intercompany_price => l_intercompany_price,
4039 p_intercompany_curr_code => l_intercompany_curr_code,
4040 p_acct_id => p_rcv_events_tbl(l_ctr).intercompany_cogs_account_id,
4041 p_sign => -1,
4042 p_parent_txn_flag => 0,
4043 p_transfer_organization_id => l_transfer_organization_id,
4044 x_return_status => l_return_status,
4045 x_msg_count => l_msg_count,
4046 x_msg_data => l_msg_data,
4047 x_inv_trx => l_inv_trx_tbl(l_inv_trx_tbl_ctr));
4048 END IF;
4049 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4050 RAISE FND_API.g_exc_error;
4051 END IF;
4052
4053 ELSE
4054 RAISE invalid_event;
4055 -- catch error: should never get anything but Log rcpt or Log RTV
4056 END IF;
4057 l_parent_txn_flag := 0; -- the first transaction inserted will be the parent, all others
4058 -- will be children so their flags are 0
4059 END IF;
4060 END IF;
4061
4062 l_ctr := p_rcv_events_tbl.NEXT(l_ctr);
4063 END LOOP;
4064
4065 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4066 FND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD||'.'||l_api_name||'.'||l_stmt_num
4067 ,'Creating Logical Transactions in MMT');
4068 END IF;
4069
4070 l_stmt_num := 150;
4071 INV_Logical_Transactions_PUB.Create_Logical_Transactions(
4072 x_return_status => l_return_status,
4073 x_msg_count => l_msg_count,
4074 x_msg_data => l_msg_data,
4075 p_api_version_number => 1.0,
4076 p_mtl_trx_tbl => l_inv_trx_tbl,
4077 p_trx_flow_header_id => p_rcv_events_tbl(p_rcv_events_tbl.FIRST).trx_flow_header_id,
4078 p_defer_logical_transactions => 2,
4079 p_logical_trx_type_code => 3,
4080 p_exploded_flag => 1);
4081 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4082 RAISE FND_API.g_exc_error;
4083 END IF;
4084
4085 -- End API Body
4086
4087 -- Standard check of p_commit
4088 IF FND_API.to_Boolean(p_commit) THEN
4089 COMMIT WORK;
4090 END IF;
4091
4092 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4093 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||l_api_name||'.end'
4094 ,'Insert_MMTEvents >>');
4095 END IF;
4096
4097 EXCEPTION
4098
4099 WHEN invalid_event THEN
4100 ROLLBACK TO Insert_MMTEvents_PVT;
4101 x_return_status := FND_API.g_ret_sts_unexp_error ;
4102 l_api_message := 'Unexpected event in element '||to_char(l_ctr)||
4103 ' of input parameter p_rcv_events_tbl';
4104 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4105 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4106 l_api_message);
4107 END IF;
4108 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4109 FND_MSG_PUB.add_exc_msg
4110 ( G_PKG_NAME,
4111 l_api_name ||': '|| l_api_message );
4112 END IF;
4113 FND_MSG_PUB.count_and_get
4114 ( p_count => x_msg_count
4115 , p_data => x_msg_data
4116 );
4117
4118 WHEN FND_API.g_exc_error THEN
4119 ROLLBACK TO Insert_MMTEvents_PVT;
4120 x_return_status := FND_API.g_ret_sts_error;
4121 l_api_message := 'Call to procedure failed';
4122 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4123 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4124 l_api_message);
4125 END IF;
4126 FND_MSG_PUB.count_and_get
4127 ( p_count => x_msg_count
4128 , p_data => x_msg_data
4129 );
4130
4131 WHEN FND_API.g_exc_unexpected_error THEN
4132 ROLLBACK TO Insert_MMTEvents_PVT;
4133 x_return_status := FND_API.g_ret_sts_unexp_error;
4134 l_api_message := 'Wrong version #, expecting version '||to_char(l_api_version);
4135 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4136 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4137 l_api_message);
4138 END IF;
4139 FND_MSG_PUB.count_and_get
4140 ( p_count => x_msg_count
4141 , p_data => x_msg_data
4142 );
4143
4144 WHEN OTHERS THEN
4145 ROLLBACK TO Insert_MMTEvents_PVT;
4146 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4147 l_api_message := 'Unexpected Error: '||l_stmt_num||': '||to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,100);
4148 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4149 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4150 l_api_message);
4151 END IF;
4152 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4153 FND_MSG_PUB.add_exc_msg
4154 ( G_PKG_NAME,
4155 l_api_name ||': '|| l_api_message );
4156 END IF;
4157 FND_MSG_PUB.count_and_get
4158 ( p_count => x_msg_count
4159 , p_data => x_msg_data
4160 );
4161
4162 END Insert_MMTEvents;
4163
4164
4165 -----------------------------------------------------------------------------
4166 -- Start of comments --
4167 -- --
4168 -- PROCEDURE --
4169 -- Create_MMTRecord This API takes an RAE record along with the --
4170 -- parameters listed above and converts them into a --
4171 -- single MMT record which will be used in a subsequent --
4172 -- function to make the physical insert into MMT --
4173 -- --
4174 -- --
4175 -- VERSION 1.0 --
4176 -- --
4177 -- PARAMETERS --
4178 -- P_API_VERSION API Version # - REQUIRED: enter 1.0 --
4179 -- P_INIT_MSG_LIST Initialize message list? True/False --
4180 -- P_COMMIT Should the API commit before returning? True/False --
4181 -- P_VALIDATION_LEVEL Specify the level of validation on the inputs --
4182 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
4183 -- X_MSG_COUNT Message Count - # of messages placed in message list--
4184 -- X_MSG_DATA Message Text - returns msg contents if msg_count = 1--
4185 -- P_RCV_EVENT Represents a single RAE, used to build the MMT entry--
4186 -- P_TXN_TYPE_ID Txn Type ID of the new MMT row being created --
4187 -- P_INTERCOMPANY_PRICE The calling fcn must determine how to populate --
4188 -- this based on the txn type and on the OU's position --
4189 -- in the txn flow. It will represent the transfer --
4190 -- price between this OU and an adjacent one. --
4191 -- P_INTERCOMPANY_CURR_CODE This parameter represents the currency code --
4192 -- of the intercompany price. --
4193 -- P_ACCT_ID Used to populate MMT.distribution_account_id --
4194 -- P_SIGN Used to set the signs (+/-) of the primary quantity --
4195 -- and the transaction quantity --
4196 -- P_PARENT_TXN_FLAG 1 - Indicates that this is the parent transaction --
4197 -- P_TRANSFER_ORGANIZATION_ID The calling function should pass the --
4198 -- organization from the next event. --
4199 -- X_INV_TRX Returns the record that will be inserted into MMT --
4200 -- --
4201 -- HISTORY: --
4202 -- 7/21/03 Bryan Kuntz Created --
4203 -- End of comments --
4204 -----------------------------------------------------------------------------
4205 PROCEDURE Create_MMTRecord(
4206 p_api_version IN NUMBER,
4207 p_init_msg_list IN VARCHAR2,
4208 p_commit IN VARCHAR2,
4209 p_validation_level IN NUMBER,
4210 x_return_status OUT NOCOPY VARCHAR2,
4211 x_msg_count OUT NOCOPY NUMBER,
4212 x_msg_data OUT NOCOPY VARCHAR2,
4213 p_rcv_event IN RCV_SeedEvents_PVT.rcv_event_rec_type,
4214 p_txn_type_id IN NUMBER,
4215 p_intercompany_price IN NUMBER,
4216 p_intercompany_curr_code IN VARCHAR2,
4217 p_acct_id IN NUMBER,
4218 p_sign IN NUMBER,
4219 p_parent_txn_flag IN NUMBER,
4220 p_transfer_organization_id IN NUMBER,
4221 x_inv_trx OUT NOCOPY INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_rec_type
4222 ) IS
4223 l_api_name CONSTANT VARCHAR2(30) := 'Create_MMTRecord';
4224 l_api_version CONSTANT NUMBER := 1.0;
4225 l_api_message VARCHAR2(1000);
4226
4227 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4228 l_msg_count NUMBER := 0;
4229 l_msg_data VARCHAR2(8000) := '';
4230 l_stmt_num NUMBER := 0;
4231
4232 l_ctr BINARY_INTEGER;
4233 l_unit_price NUMBER;
4234 l_inv_trx INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_rec_type;
4235
4236 l_le_id NUMBER; -- holds legal entity ID for timezone conversion
4237 l_le_txn_date DATE; -- transaction date truncated and converted to legal entity timezone
4238
4239 invalid_txn_type EXCEPTION;
4240 BEGIN
4241
4242 -- Standard start of API savepoint
4243 SAVEPOINT Create_MMTRecord_PVT;
4244
4245 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4246 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||'.'||l_api_name||'.begin'
4247 ,'Create_MMTRecord <<');
4248 END IF;
4249
4250 -- Standard call to check for call compatibility
4251 IF NOT FND_API.Compatible_API_Call (
4252 l_api_version,
4253 p_api_version,
4254 l_api_name,
4255 G_PKG_NAME ) THEN
4256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4257 END IF;
4258
4259 -- Initialize message list if p_init_msg_list is set to TRUE
4260 IF FND_API.to_Boolean(p_init_msg_list) THEN
4261 FND_MSG_PUB.initialize;
4262 END IF;
4263
4264 -- Initialize API return status to success
4265 x_return_status := FND_API.G_RET_STS_SUCCESS;
4266 x_msg_count := 0;
4267 x_msg_data := '';
4268
4269 -- API Body
4270 l_inv_trx.intercompany_pricing_option := p_rcv_event.intercompany_pricing_option;
4271
4272 l_stmt_num := 5;
4273
4274 -- Assign transaction action, source type, and invoiced flag
4275 IF (p_txn_type_id = 11) THEN
4276 l_inv_trx.transaction_action_id := 9;
4277 l_inv_trx.transaction_source_type_id := 13;
4278 l_inv_trx.invoiced_flag := 'N';
4279 ELSIF (p_txn_type_id = 14) THEN
4280 l_inv_trx.transaction_action_id := 14;
4281 l_inv_trx.transaction_source_type_id := 13;
4282 l_inv_trx.invoiced_flag := 'N';
4283 ELSIF (p_txn_type_id = 69) THEN
4284 l_inv_trx.transaction_action_id := 11;
4285 l_inv_trx.transaction_source_type_id := 1;
4286 IF (p_rcv_event.procurement_org_flag = 'Y') THEN
4287 l_inv_trx.invoiced_flag := NULL;
4288 l_inv_trx.intercompany_pricing_option := 1;
4289 ELSE
4290 l_inv_trx.invoiced_flag := 'N';
4291 END IF;
4292 ELSIF (p_txn_type_id = 19) THEN
4293 l_inv_trx.transaction_action_id := 26;
4294 l_inv_trx.transaction_source_type_id := 1;
4295 l_inv_trx.invoiced_flag := NULL;
4296 l_inv_trx.intercompany_pricing_option := 1;
4297 ELSIF (p_txn_type_id = 22) THEN
4298 l_inv_trx.transaction_action_id := 10;
4299 l_inv_trx.transaction_source_type_id := 13;
4300 l_inv_trx.invoiced_flag := 'N';
4301 ELSIF (p_txn_type_id = 23) THEN
4302 l_inv_trx.transaction_action_id := 13;
4303 l_inv_trx.transaction_source_type_id := 13;
4304 l_inv_trx.invoiced_flag := 'N';
4305 ELSIF (p_txn_type_id = 39) THEN
4306 l_inv_trx.transaction_action_id := 7;
4307 l_inv_trx.transaction_source_type_id := 1;
4308 l_inv_trx.invoiced_flag := NULL;
4309 l_inv_trx.intercompany_pricing_option := 1;
4310 ELSE
4311 l_api_message := 'Invalid transaction type';
4312 RAISE invalid_txn_type;
4313 END IF;
4314
4315 -- Set currency columns
4316 l_stmt_num := 20;
4317 IF (p_txn_type_id in (19,39)) THEN
4318 l_inv_trx.currency_code := p_rcv_event.currency_code;
4319 l_inv_trx.currency_conversion_rate := p_rcv_event.currency_conversion_rate;
4320 l_inv_trx.currency_conversion_type := p_rcv_event.currency_conversion_type;
4321 l_inv_trx.currency_conversion_date := sysdate;
4322 ELSE
4323 l_inv_trx.currency_code := NULL;
4324 l_inv_trx.currency_conversion_rate := NULL;
4325 l_inv_trx.currency_conversion_type := NULL;
4326 l_inv_trx.currency_conversion_date := NULL;
4327 END IF;
4328
4329 l_stmt_num := 30;
4330 -- Compute unit price and intercompany price
4331 IF (p_rcv_event.intercompany_pricing_option = 2) THEN
4332 l_unit_price := p_rcv_event.unit_price * p_rcv_event.source_doc_quantity/p_rcv_event.primary_quantity;
4333 ELSE
4334 l_unit_price := (p_rcv_event.unit_price + p_rcv_event.unit_nr_tax) *
4335 p_rcv_event.source_doc_quantity/p_rcv_event.primary_quantity;
4336 END IF;
4337
4338 l_stmt_num := 40;
4339 l_api_message := 'No data';
4340 -- Main select statement to populate the l_inv_trx record
4341 SELECT
4342 p_rcv_event.organization_id,
4343 p_rcv_event.item_id,
4344 p_txn_type_id,
4345 rt.po_header_id,
4346 P_SIGN * ABS(p_rcv_event.transaction_quantity),
4347 p_rcv_event.trx_uom_code,
4348 P_SIGN * ABS(p_rcv_event.primary_quantity),
4349 rt.transaction_date,
4350 decode(nvl(fc.minimum_accountable_unit,0), 0,
4351 round(l_unit_price*p_rcv_event.primary_quantity,fc.precision)*
4352 p_rcv_event.currency_conversion_rate/p_rcv_event.primary_quantity,
4353 round(l_unit_price*
4354 p_rcv_event.primary_quantity/fc.minimum_accountable_unit) *
4355 fc.minimum_accountable_unit*p_rcv_event.currency_conversion_rate/p_rcv_event.primary_quantity),
4356 'RCV',
4357 rt.transaction_id,
4358 rt.transaction_id,
4359 p_transfer_organization_id,
4360 NULL, --pod.project_id, remove these 2 because projects will cause failure in inv's create_logical_txns
4361 NULL, --pod.task_id, since they are only expected values in the org that does the deliver
4362 poll.ship_to_location_id,
4363 1,
4364 p_rcv_event.trx_flow_header_id,
4365 decode(nvl(fc.minimum_accountable_unit,0), 0,
4366 round(p_intercompany_price*p_rcv_event.primary_quantity,fc.precision)
4367 /p_rcv_event.primary_quantity,
4368 round(p_intercompany_price*
4369 p_rcv_event.primary_quantity/fc.minimum_accountable_unit) *
4370 fc.minimum_accountable_unit
4371 /p_rcv_event.primary_quantity),
4372 p_intercompany_curr_code,
4373 p_acct_id,
4374 'N',
4375 NULL,
4376 NULL,
4377 p_parent_txn_flag,
4378 NULL
4379 INTO
4380 l_inv_trx.organization_id,
4381 l_inv_trx.inventory_item_id,
4382 l_inv_trx.transaction_type_id,
4383 l_inv_trx.transaction_source_id,
4384 l_inv_trx.transaction_quantity,
4385 l_inv_trx.transaction_uom,
4386 l_inv_trx.primary_quantity,
4387 l_inv_trx.transaction_date,
4388 l_inv_trx.transaction_cost,
4389 l_inv_trx.source_code,
4390 l_inv_trx.source_line_id,
4391 l_inv_trx.rcv_transaction_id,
4392 l_inv_trx.transfer_organization_id,
4393 l_inv_trx.project_id,
4394 l_inv_trx.task_id,
4395 l_inv_trx.ship_to_location_id,
4396 l_inv_trx.transaction_mode,
4397 l_inv_trx.trx_flow_header_id,
4398 l_inv_trx.intercompany_cost,
4399 l_inv_trx.intercompany_currency_code,
4400 l_inv_trx.distribution_account_id,
4401 l_inv_trx.costed_flag,
4402 l_inv_trx.subinventory_code,
4403 l_inv_trx.locator_id,
4404 l_inv_trx.parent_transaction_flag,
4405 l_inv_trx.trx_source_line_id
4406 FROM rcv_transactions RT,
4407 po_lines POL,
4408 po_line_locations POLL,
4409 po_distributions POD,
4410 fnd_currencies FC
4411 WHERE RT.transaction_id = p_rcv_event.rcv_transaction_id
4412 AND POL.po_line_id = p_rcv_event.po_line_id
4413 AND POLL.line_location_id = p_rcv_event.po_line_location_id
4414 AND POD.po_distribution_id = p_rcv_event.po_distribution_id
4415 AND FC.currency_code = p_rcv_event.currency_code;
4416
4417 l_stmt_num := 50;
4418 l_api_message := 'Inventory accounting period not open.';
4419 /* get the legal entity for timezone conversion */
4420 SELECT to_number(org_information2)
4421 INTO l_le_id
4422 FROM hr_organization_information
4423 WHERE organization_id = p_rcv_event.organization_id
4424 AND org_information_context = 'Accounting Information';
4425
4426 l_stmt_num := 55;
4427 /* convert the transaction date into legal entity timezone (truncated) */
4428 l_le_txn_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_SERVER(l_inv_trx.transaction_date, l_le_id);
4429
4430 l_stmt_num := 60;
4431 /* retrieve the accounting period ID */
4432 SELECT acct_period_id
4433 INTO l_inv_trx.acct_period_id
4434 FROM org_acct_periods
4435 WHERE organization_id = p_rcv_event.organization_id
4436 AND l_le_txn_date BETWEEN period_start_date AND schedule_close_date
4437 AND open_flag = 'Y';
4438
4439 /* -- comment out this call for ST bug 3261222
4440 OE_DROP_SHIP_GRP.Get_Drop_Ship_Line_Ids(
4441 p_po_header_id => p_rcv_event.po_header_id,
4442 p_po_line_id => p_rcv_event.po_line_id,
4443 p_po_line_location_id => p_rcv_event.po_line_location_id,
4444 p_po_release_id => l_po_release_id,
4445 x_line_id => l_inv_trx.trx_source_line_id,
4446 x_num_lines => l_so_num_lines,
4447 x_header_id => l_so_header_id,
4448 x_org_id => l_so_org_id);
4449 */
4450
4451
4452 X_INV_TRX := l_inv_trx;
4453 -- ***************
4454
4455 -- Standard check of p_commit
4456 IF FND_API.to_Boolean(p_commit) THEN
4457 COMMIT WORK;
4458 END IF;
4459
4460 -- Standard Call to get message count and if count = 1, get message info
4461 FND_MSG_PUB.Count_And_Get (
4462 p_count => x_msg_count,
4463 p_data => x_msg_data );
4464
4465 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4466 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD ||'.'||l_api_name||'.end'
4467 ,'Create_MMTRecord >>');
4468 END IF;
4469
4470 EXCEPTION
4471
4472 WHEN FND_API.g_exc_error THEN
4473 ROLLBACK TO Create_MMTRecord_PVT;
4474 x_return_status := FND_API.g_ret_sts_error;
4475 FND_MSG_PUB.count_and_get
4476 ( p_count => x_msg_count
4477 , p_data => x_msg_data
4478 );
4479
4480 WHEN FND_API.g_exc_unexpected_error THEN
4481 ROLLBACK TO Create_MMTRecord_PVT;
4482 x_return_status := FND_API.g_ret_sts_unexp_error ;
4483 l_api_message := 'Unexpected error at statement '||to_char(l_stmt_num);
4484 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4485 FND_MSG_PUB.add_exc_msg
4486 ( G_PKG_NAME,
4487 l_api_name ||': '|| l_api_message );
4488 END IF;
4489 FND_MSG_PUB.count_and_get
4490 ( p_count => x_msg_count
4491 , p_data => x_msg_data
4492 );
4493
4494 WHEN invalid_txn_type THEN
4495 ROLLBACK TO Create_MMTRecord_PVT;
4496 x_return_status := FND_API.g_ret_sts_error ;
4497 l_api_message := 'Unexpected transaction type passed in: '||to_char(p_txn_type_id);
4498 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4499 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||l_api_name||'.'||l_stmt_num,
4500 l_api_message);
4501 END IF;
4502 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
4503 FND_MSG_PUB.add_exc_msg
4504 ( G_PKG_NAME,
4505 l_api_name ||': '|| l_api_message );
4506 END IF;
4507 FND_MSG_PUB.count_and_get
4508 ( p_count => x_msg_count
4509 , p_data => x_msg_data
4510 );
4511
4512 WHEN NO_DATA_FOUND THEN
4513 ROLLBACK TO Create_MMTRecord_PVT;
4514 X_RETURN_STATUS := fnd_api.g_ret_sts_error;
4515 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4516 FND_LOG.string(FND_LOG.LEVEL_ERROR,G_LOG_HEAD ||'.'||l_api_name||'.'||l_stmt_num,
4517 l_api_message);
4518 END IF;
4519 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
4520 THEN
4521 FND_MSG_PUB.add_exc_msg
4522 ( G_PKG_NAME,
4523 l_api_name || l_api_message
4524 );
4525 END IF;
4526 FND_MSG_PUB.count_and_get
4527 ( p_count => x_msg_count
4528 , p_data => x_msg_data
4529 );
4530
4531 WHEN OTHERS THEN
4532 ROLLBACK TO Create_MMTRecord_PVT;
4533 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4534 l_api_message := to_char(SQLCODE)||'- '|| substrb(SQLERRM,1,100);
4535 IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4536 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD ||'.'||l_api_name||'.'||l_stmt_num
4537 ,'Create_MMTRecord : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
4538 END IF;
4539 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
4540 THEN
4541 FND_MSG_PUB.add_exc_msg
4542 ( G_PKG_NAME,
4543 l_api_name ||'('||to_char(l_stmt_num)||') - ' || l_api_message
4544 );
4545 END IF;
4546 FND_MSG_PUB.count_and_get
4547 ( p_count => x_msg_count
4548 , p_data => x_msg_data
4549 );
4550
4551 END Create_MMTRecord;
4552
4553 END RCV_SeedEvents_PVT;