[Home] [Help]
PACKAGE BODY: APPS.OZF_ACCT_GENERATOR
Source
1 PACKAGE BODY Ozf_Acct_Generator AS
2 /*$Header: ozfactgb.pls 120.0.12000000.2 2007/08/16 13:10:27 kdass ship $*/
3
4
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ozf_Acct_Generator';
7 G_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8
9 -- Start of Comments
10 -- API name ams_acct_generator
11 -- Type Private
12 -- Version Current version = 1.0
13 -- Initial version = 1.0
14 --Name of the function gl_post_account
15
16 FUNCTION Get_User_Role
17 RETURN VARCHAR2
18 IS
19
20 CURSOR c_user (p_user_id in number) IS
21 SELECT employee_id
22 FROM fnd_user
23 WHERE user_id = p_user_id ;
24
25 l_user_id number;
26 l_person_id number;
27 l_role_display_name varchar2(240);
28 l_role_name varchar2(240) := null;
29
30 BEGIN
31
32 l_user_id := FND_GLOBAL.user_id;
33
34 OPEN c_user(l_user_id);
35 FETCH c_user INTO l_person_id ;
36 IF c_user%NOTFOUND THEN
37 l_role_name := null;
38 END IF;
39 CLOSE c_user ;
40
41 IF l_person_id is not null THEN
42 -- Pass the Employee ID to get the Role
43 WF_DIRECTORY.getrolename(
44 p_orig_system => 'PER',
45 p_orig_system_id => l_person_id ,
46 p_name => l_role_name,
47 p_display_name => l_role_display_name );
48 END IF;
49
50 IF G_DEBUG_HIGH_ON THEN
51 ozf_utility_pvt.debug_message('Role: ' || l_role_name );
52 ozf_utility_pvt.debug_message('Role Name: ' || l_role_display_name );
53 END IF;
54
55 return l_role_name;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 l_role_name := null;
60 return l_role_name;
61 END Get_User_Role;
62
63 /* This function is used to determine the ccid of the account type.
64 There are three possible types,
65 A.'Accrued Promotion Liability'
66 B.'Deduction Adjustment Clearing'
67 C.'Deduction Adjustment Account'.
68 In case A and C - ccid is derive from ozf_funds_all_b
69 case of B the value is derived from ozf_sys_parameters_all.
70 This function can be customized based on customer needs */
71
72 FUNCTION gl_post_account(
73 p_api_version_number IN NUMBER,
74 p_account_type IN VARCHAR2,
75 p_budget_id IN NUMBER,
76 x_msg_count OUT NOCOPY NUMBER,
77 x_msg_data OUT NOCOPY VARCHAR2,
78 x_return_status OUT NOCOPY VARCHAR2)
79 RETURN NUMBER
80 IS
81 l_post_account NUMBER := 0;
82 l_api_version_number CONSTANT NUMBER := 1.0;
83 l_api_name CONSTANT VARCHAR2(30) := 'gl_post_account';
84
85 l_fund_expense_account number;
86 l_fund_accrual_liability number;
87 l_cat_expense_account number;
88 l_cat_accrual_liability number;
89
90 l_expense_account number;
91 l_accrual_liability number;
92 l_ven_clearing number;
93 l_rec_clearing number;
94
95 CURSOR get_fund_accounts_csr (p_budget_id in number) IS
96 select f.ded_adjustment_account
97 , f.accrued_liable_account
98 , c.ded_adjustment_account
99 , c.accrued_liability_account
100 from ozf_funds_all_b f
101 , ams_categories_b c
102 where f.category_id = c.category_id
103 and f.fund_id = p_budget_id;
104
105 -- default accounts
106 CURSOR get_sys_param_csr(p_id in number) IS
107 select osp.gl_id_ded_adj -- expense account
108 , osp.gl_id_accr_promo_liab -- accrual liability account
109 , osp.gl_id_ded_clearing -- vendor clearing account
110 , osp.gl_rec_clearing_account -- receivables clearing account
111 FROM ozf_sys_parameters_all osp
112 --, ozf_funds_utilized_all_b ofa
113 , ozf_funds_all_b f
114 WHERE osp.org_id = NVL(f.org_id, -99)
115 --AND ofa.fund_id = f.fund_id
116 AND f.fund_id = p_id;
117
118 BEGIN
119
120 IF G_DEBUG_HIGH_ON THEN
121 OZF_UTILITY_PVT.debug_message('Private Api: ' || l_api_name || ' START');
122 END IF;
123 IF NOT FND_API.Compatible_API_Call
124 (l_api_version_number
125 ,p_api_version_number
126 ,l_api_name
127 ,G_PKG_NAME
128 )
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132
133 -- get account from the fund
134 IF p_budget_id is not null OR
135 p_budget_id <> FND_API.G_MISS_NUM
136 THEN
137 -- get default accounts from system parameters
138 OPEN get_sys_param_csr(p_budget_id);
139 FETCH get_sys_param_csr INTO l_expense_account,
140 l_accrual_liability,
141 l_ven_clearing,
142 l_rec_clearing;
143 CLOSE get_sys_param_csr;
144
145 OPEN get_fund_accounts_csr(p_budget_id);
146 FETCH get_fund_accounts_csr INTO l_fund_expense_account,
147 l_fund_accrual_liability,
148 l_cat_expense_account,
149 l_cat_accrual_liability;
150 CLOSE get_fund_accounts_csr;
151 END IF;
152
153
154 -- default budgets expense account
155 IF l_fund_expense_account is null THEN
156 IF l_cat_expense_account is null THEN
157 l_fund_expense_account := l_expense_account;
158 ELSE
159 l_fund_expense_account := l_cat_expense_account;
160 END IF;
161 END IF;
162
163 -- default funds accrual liability account
164 IF l_fund_accrual_liability is null THEN
165 IF l_cat_accrual_liability is null THEN
166 l_fund_accrual_liability := l_accrual_liability;
167 ELSE
168 l_fund_accrual_liability := l_cat_accrual_liability;
169 END IF;
170 END IF;
171
172 --DBMS_OUTPUT.PUT_LINE('Entering the api gl_post_account');
173 IF p_account_type = 'ACCRUAL_LIABILITY' THEN
174 IF l_fund_accrual_liability is null THEN
175 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
176 FND_MESSAGE.set_name('OZF', 'OZF_GL_NO_ACC_LIAB_ACCT');
177 FND_MSG_PUB.add;
178 END IF;
179 x_return_status := FND_API.g_ret_sts_error;
180 ELSE
181 l_post_account := l_fund_accrual_liability;
182 END IF;
183 ELSIF p_account_type = 'EXPENSE ACCOUNT' THEN
184 IF l_fund_expense_account is null THEN
185 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
186 FND_MESSAGE.set_name('OZF', 'OZF_GL_NO_EXP_ACCT');
187 FND_MSG_PUB.add;
188 END IF;
189 x_return_status := FND_API.g_ret_sts_error;
190 ELSE
191 l_post_account := l_fund_expense_account;
192 END IF;
193 ELSIF p_account_type = 'VEN_CLEARING' THEN
194 IF l_ven_clearing is null THEN
195 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
196 FND_MESSAGE.set_name('OZF', 'OZF_GL_NO_VEN_CLEAR_ACCT');
197 FND_MSG_PUB.add;
198 END IF;
199 x_return_status := FND_API.g_ret_sts_error;
200 ELSE
201 l_post_account := l_ven_clearing;
202 END IF;
203 ELSIF p_account_type = 'REC_CLEARING' THEN
204 IF l_rec_clearing is null THEN
205 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
206 FND_MESSAGE.set_name('OZF', 'OZF_GL_NO_REC_CLEAR_ACCT');
207 FND_MSG_PUB.add;
208 END IF;
209 x_return_status := FND_API.g_ret_sts_error;
210 ELSE
211 l_post_account := l_rec_clearing;
212 END IF;
213 ELSE
214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
215 FND_MESSAGE.set_name('OZF', 'OZF_GL_INVALID_ACCT_EVENT');
216 FND_MSG_PUB.add;
217 END IF;
218 l_post_account := null;
219 END IF;
220
221 FND_MSG_PUB.Count_And_Get
222 (p_count => x_msg_count,
223 p_data => x_msg_data);
224
225 IF G_DEBUG_HIGH_ON THEN
226 OZF_UTILITY_PVT.debug_message('Private Api: ' || l_api_name || ' END');
227 END IF;
228 RETURN l_post_account;
229
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 l_post_account := 0;
233 -- Standard call to get message count and if count=1, get the message
234 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
235 FND_MESSAGE.SET_NAME('OZF','OZF_INVALID_ACCOUNT');
236 FND_MESSAGE.SET_TOKEN('CCID',l_post_account);
237 FND_MSG_PUB.ADD;
238 END IF;
239 FND_MSG_PUB.Count_And_Get
240 (p_count => x_msg_count,
241 p_data => x_msg_data);
242 x_return_status := FND_API.g_ret_sts_error;
243 RETURN l_post_account;
244 WHEN FND_API.G_EXC_ERROR THEN
245 l_post_account := 0;
246 -- Standard call to get message count and if count=1, get the message
247 FND_MSG_PUB.Count_And_Get (
248 p_encoded => FND_API.G_FALSE,
249 p_count => x_msg_count,
250 p_data => x_msg_data);
251 x_return_status := FND_API.g_ret_sts_error;
252 RETURN l_post_account;
253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
254 l_post_account := 0;
255 -- Standard call to get message count and if count=1, get the message
256 FND_MSG_PUB.Count_And_Get (
257 p_encoded => FND_API.G_FALSE,
258 p_count => x_msg_count,
259 p_data => x_msg_data);
260 x_return_status := FND_API.g_ret_sts_unexp_error;
261 RETURN l_post_account;
262 WHEN OTHERS THEN
263 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
264 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
265 END IF;
266 l_post_account := 0;
267 -- Standard call to get message count and if count=1, get the message
268 FND_MSG_PUB.Count_And_Get (
269 p_encoded => FND_API.G_FALSE,
270 p_count => x_msg_count,
271 p_data => x_msg_data);
272 RETURN l_post_account;
273 END gl_post_account;
274 -------------------------------------------------------------------------------
275 /*===========================================================================+
276 | Name: START_PROCESS |
277 | Purpose: Runs the Workflow process to generate accounts below: |
278 | 'Accrued Promotion Liability' |
279 | 'Sales Expense Account' |
280 +===========================================================================*/
281 PROCEDURE Start_Process (
282 p_api_version_number IN NUMBER,
283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
284 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
285
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289
290 p_account_type IN VARCHAR2,
291 p_claim_id IN NUMBER,
292 p_budget_id IN NUMBER,
293 p_utilization_id IN NUMBER,
294 p_offer_id IN NUMBER,
295 p_order_id IN NUMBER,
296 p_line_id IN NUMBER,
297 p_item_type IN VARCHAR2,
298 p_item_id IN NUMBER,
299 p_price_adj_id IN NUMBER,
300 p_cust_account_id IN NUMBER,
301
302 x_return_ccid IN OUT NOCOPY NUMBER,
303 x_concat_segs IN OUT NOCOPY VARCHAR2,
304 x_concat_ids IN OUT NOCOPY VARCHAR2,
305 x_concat_descrs IN OUT NOCOPY VARCHAR2 )
306 IS
307 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process';
308 l_api_version_number CONSTANT NUMBER := 1.0;
309 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
310 --
311
312 l_org_id NUMBER;
313 l_chart_of_accounts_id NUMBER;
314 l_account_id NUMBER;
315
316 l_line_id NUMBER;
317 l_reference_line_id NUMBER;
318 l_line_category_code VARCHAR2(30);
319
320 l_header_id NUMBER := p_order_id;
321 l_ship_from_org_id NUMBER;
322 l_sold_to_org_id NUMBER;
323 l_commitment_id NUMBER;
324 l_salesrep_id NUMBER;
325 l_inventory_item_id NUMBER;
326 l_item_category_id NUMBER;
327 l_item_type_code VARCHAR2(30);
328
329 l_order_category_code VARCHAR2(30);
330 l_order_type_id NUMBER;
331
332 l_option_flag VARCHAR2(1);
333 l_errmsg VARCHAR2(2000);
334 l_new_comb BOOLEAN := TRUE;
335 l_itemtype VARCHAR2(30) := 'OZFACCTG';
336 l_itemkey VARCHAR2(38);
337 l_role_name varchar2(240) := null;
338 l_debug_flag varchar2(30);
339 l_result BOOLEAN;
340 l_tp_util NUMBER := 0;
341
342 CURSOR price_adj_csr(p_price_adj_id in number) IS
343 SELECT HEADER_ID,
344 LINE_ID
345 FROM OE_PRICE_ADJUSTMENTS
346 WHERE PRICE_ADJUSTMENT_ID = p_price_adj_id;
347
348 CURSOR line_detail_csr(p_line_id in number) IS
349 SELECT HEADER_ID,
350 ORG_ID,
351 SHIP_FROM_ORG_ID,
352 SOLD_TO_ORG_ID,
353 COMMITMENT_ID,
354 SALESREP_ID,
355 INVENTORY_ITEM_ID,
356 ITEM_TYPE_CODE
357 FROM oe_order_lines_all
358 WHERE LINE_ID = p_line_id;
359
360 CURSOR header_detail_csr(p_header_id in number) IS
361 SELECT HEADER_ID,
362 ORG_ID,
363 SHIP_FROM_ORG_ID,
364 SOLD_TO_ORG_ID,
365 SALESREP_ID
366 FROM oe_order_headers_all
367 WHERE HEADER_ID = p_header_id;
368
369 CURSOR order_line_csr(p_line_id in number) IS
370 SELECT REFERENCE_LINE_ID,
371 LINE_CATEGORY_CODE
372 FROM OE_ORDER_LINES_ALL
373 WHERE LINE_ID = p_line_id;
374
375 CURSOR order_header_csr(p_header_id in number) IS
376 SELECT ORDER_CATEGORY_CODE,
377 ORDER_TYPE_ID
378 FROM OE_ORDER_HEADERS_ALL
379 WHERE HEADER_ID = p_header_id;
380
381 CURSOR budget_org_csr(p_budget_id in number) IS
382 SELECT org_id
383 FROM ozf_funds_all_b
384 WHERE fund_id = p_budget_id;
385
386 CURSOR claim_org_csr(p_claim_id in number) IS
387 SELECT org_id
388 FROM ozf_claims_all
389 WHERE claim_id = p_claim_id;
390
391 CURSOR chart_of_acct_csr(p_org_id in number)IS
392 select chart_of_accounts_id
393 from gl_sets_of_books sob
394 , ozf_sys_parameters_all osp
395 where osp.set_of_books_id = sob.set_of_books_id
396 and osp.org_id = NVL(p_org_id, -99);
397
398 CURSOR c_third_party_util IS
399 SELECT 1
400 FROM ozf_funds_utilized_all_b
401 WHERE utilization_id = p_utilization_id
402 AND object_type = 'TP_ORDER';
403
404 BEGIN
405 IF G_DEBUG_HIGH_ON THEN
406 OZF_UTILITY_PVT.debug_message('Private Api: ' || l_full_name || ' START');
407 END IF;
408 IF NOT FND_API.Compatible_API_Call
409 (l_api_version_number
410 ,p_api_version_number
411 ,l_api_name
412 ,G_PKG_NAME
413 )
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417 --Initialize message list if p_init_msg_list is TRUE.
418 IF FND_API.To_Boolean (p_init_msg_list) THEN
419 FND_MSG_PUB.initialize;
420 END IF;
421 -- Initialize API return status to sucess
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423
424 IF G_DEBUG_HIGH_ON THEN
425 OZF_UTILITY_PVT.debug_message('Retreiving Chart Of Accounts Id');
426 END IF;
427 /* get org id */
428 IF p_budget_id <> FND_API.G_MISS_NUM OR
429 p_budget_id IS NOT NULL
430 THEN
431 OPEN budget_org_csr(p_budget_id);
432 FETCH budget_org_csr INTO l_org_id;
433 CLOSE budget_org_csr;
434 END IF;
435
436 /* Retreive the Chart of accounts Id */
437 OPEN chart_of_acct_csr(l_org_id);
438 FETCH chart_of_acct_csr INTO l_chart_of_accounts_id;
439 CLOSE chart_of_acct_csr;
440
441 IF l_chart_of_accounts_id is null THEN
442 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
443 FND_MESSAGE.SET_NAME('OZF','OZF_GL_CHART_OF_ACCT_MISSING');
444 FND_MESSAGE.SET_TOKEN('BUDGET',p_budget_id);
445 END IF;
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 /* This will retrieve the account_id for gl_posting purposes*/
450 l_account_id := gl_post_account(p_api_version_number => 1.0,
451 p_account_type => p_account_type,
452 p_budget_id => p_budget_id,
453 x_msg_count => x_msg_count,
454 x_msg_data => x_msg_data,
455 x_return_status => x_return_status);
456 IF x_return_status = FND_API.g_ret_sts_error THEN
457 RAISE FND_API.G_EXC_ERROR;
458 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460 END IF;
461
462 -- call account generator workflow to get the cost of sale account
463 -- get order header/line details from price adjustment
464 IF p_price_adj_id <> FND_API.G_MISS_NUM OR
465 p_price_adj_id IS NOT NULL
466 THEN
467
468 --kdass 16-Aug-07 bug 6345327 - for third party utilizations, do not pass line_id and header_id
469 l_header_id := NULL;
470 l_line_id := NULL;
471
472 OPEN c_third_party_util;
473 FETCH c_third_party_util INTO l_tp_util;
474 CLOSE c_third_party_util;
475
476 -- if not third party utilization
477 IF l_tp_util <> 1 THEN
478 OPEN price_adj_csr(p_price_adj_id);
479 FETCH price_adj_csr INTO l_header_id, l_line_id;
480 CLOSE price_adj_csr;
481
482 -- if line not found then set line_id to IN parameter
483 IF l_line_id is null THEN
484 l_line_id := p_line_id;
485 END IF;
486 -- if header not found then set order_id to IN parameter
487 IF l_header_id is null THEN
488 l_header_id := p_order_id;
489 END IF;
490
491 END IF;
492 END IF;
493
494 -- line id is found -- Accruals at Order Line level
495 IF l_line_id <> FND_API.G_MISS_NUM OR
496 l_line_id IS NOT NULL
497 THEN
498 IF G_DEBUG_HIGH_ON THEN
499 OZF_UTILITY_PVT.debug_message('Checking Order Category');
500 END IF;
501 OPEN order_line_csr(l_line_id);
502 FETCH order_line_csr INTO l_reference_line_id, l_line_category_code;
503 CLOSE order_line_csr;
504
505 -- for returns set the reference line id
506 IF l_line_category_code = 'RETURN' AND
507 l_reference_line_id IS NOT NULL
508 THEN
509 l_line_id := l_reference_line_id;
510 END IF;
511
512 IF G_DEBUG_HIGH_ON THEN
513 OZF_UTILITY_PVT.debug_message('Retreiving Line Header');
514 END IF;
515 /* Retreive the header and line details */
516 OPEN line_detail_csr(l_line_id);
517 FETCH line_detail_csr
518 INTO l_header_id,
519 l_org_id,
520 l_ship_from_org_id,
521 l_sold_to_org_id,
522 l_commitment_id,
523 l_salesrep_id,
524 l_inventory_item_id,
525 l_item_type_code;
526 CLOSE line_detail_csr;
527 IF l_header_id is null THEN
528 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
529 FND_MESSAGE.SET_NAME('OZF','OZF_GL_HEADER_DETAIL_MISSING');
530 FND_MESSAGE.SET_TOKEN('LINE_ID',l_line_id);
531 FND_MSG_PUB.ADD;
532 END IF;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535
536 IF l_ship_from_org_id IS NULL THEN
537 -- 19-JUL-2004 yizhang: bill only orders do not have ship from org
538 /*
539 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
540 FND_MESSAGE.SET_NAME('OZF','OZF_WAREHOUSE_MISSING');
541 FND_MESSAGE.SET_TOKEN('LINE_ID',l_line_id);
542 FND_MSG_PUB.ADD;
543 END IF;
544 RAISE FND_API.G_EXC_ERROR;
545 */
546 l_ship_from_org_id := l_org_id;
547 END IF;
548
549 IF G_DEBUG_HIGH_ON THEN
550 OZF_UTILITY_PVT.debug_message('Retreiving Order Category');
551 END IF;
552 /* Retreive Order category and order type */
553 OPEN order_header_csr(l_header_id);
554 FETCH order_header_csr INTO l_order_category_code, l_order_type_id;
555 CLOSE order_header_csr;
556
557 IF G_DEBUG_HIGH_ON THEN
558 OZF_UTILITY_PVT.debug_message('Retreiving Chart Of Accounts Id');
559 END IF;
560 /* Retreive the Chart of accounts Id */
561 OPEN chart_of_acct_csr(l_ship_from_org_id);
562 FETCH chart_of_acct_csr INTO l_chart_of_accounts_id;
563 CLOSE chart_of_acct_csr;
564
565 -- line is null and header is found -- Accruals at Order header level
566 ELSIF l_header_id <> FND_API.G_MISS_NUM OR
567 l_header_id IS NOT NULL
568 THEN
569 IF G_DEBUG_HIGH_ON THEN
570 OZF_UTILITY_PVT.debug_message('Retreiving Header');
571 END IF;
572 /* Retreive the header and line details */
573 OPEN header_detail_csr(l_header_id);
574 FETCH header_detail_csr
575 INTO l_header_id,
576 l_org_id,
577 l_ship_from_org_id,
578 l_sold_to_org_id,
579 l_salesrep_id;
580 CLOSE header_detail_csr;
581
582 IF G_DEBUG_HIGH_ON THEN
583 OZF_UTILITY_PVT.debug_message('Retreiving Order Category');
584 END IF;
585 /* Retreive Order category and order type */
586 OPEN order_header_csr(l_header_id);
587 FETCH order_header_csr INTO l_order_category_code, l_order_type_id;
588 CLOSE order_header_csr;
589
590 IF p_item_type = 'PRODUCT' THEN
591 -- set the inventory item id
592 l_inventory_item_id := p_item_id;
593 ELSIF p_item_type = 'FAMILY' THEN
594 -- set the item category
595 l_item_category_id := p_item_id;
596 END IF;
597
598 IF l_ship_from_org_id IS NULL THEN
599 l_ship_from_org_id := l_org_id;
600 END IF;
601
602 IF G_DEBUG_HIGH_ON THEN
603 OZF_UTILITY_PVT.debug_message('Retreiving Chart Of Accounts Id');
604 END IF;
605 /* Retreive the Chart of accounts Id */
606 OPEN chart_of_acct_csr(l_ship_from_org_id);
607 FETCH chart_of_acct_csr INTO l_chart_of_accounts_id;
608 CLOSE chart_of_acct_csr;
609
610 -- line id and header is null and item id passed -- Lumpsums
611 ELSIF p_item_id <> FND_API.G_MISS_NUM OR
612 p_item_id is not null
613 THEN
614 IF p_item_type = 'PRODUCT' THEN
615 -- set the inventory item id
616 l_inventory_item_id := p_item_id;
617 l_ship_from_org_id := l_org_id;
618 ELSIF p_item_type = 'FAMILY' THEN
619 -- set the item category
620 l_item_category_id := p_item_id;
621 l_ship_from_org_id := l_org_id;
622 END IF;
623 l_sold_to_org_id := p_cust_account_id;
624
625 -- Manual adjustments without product/order
626 ELSE
627 l_ship_from_org_id := l_org_id;
628 l_sold_to_org_id := p_cust_account_id;
629 END IF; -- order line is not passed
630
631 /* Initialize the workflow item attributes */
632 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
633 FND_MESSAGE.SET_NAME('OZF','INITIALIZE_WORK_FLOW');
634 END IF;
635
636 l_itemkey := Fnd_Flex_Workflow.INITIALIZE
637 ('SQLGL',
638 'GL#',
639 l_chart_of_accounts_id,
640 'OZFACCTG');
641
642 IF G_DEBUG_HIGH_ON THEN
643 OZF_UTILITY_PVT.debug_message('Item Key: ' || l_itemkey);
644 END IF;
645
646 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
647 itemkey => l_itemkey,
648 aname =>'COMMITMENT_ID',
649 avalue =>l_commitment_id);
650
651 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
652 itemkey => l_itemkey,
653 aname =>'CUSTOMER_ID',
654 avalue =>l_sold_to_org_id);
655
656 wf_engine.SetItemAttrText(itemtype => l_itemtype,
657 itemkey => l_itemkey,
658 aname => 'ORDER_CATEGORY',
659 avalue => l_order_category_code);
660
661 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
662 itemkey => l_itemkey,
663 aname => 'HEADER_ID',
664 avalue => l_header_id);
665
666 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
667 itemkey => l_itemkey,
668 aname => 'LINE_ID',
669 avalue => l_line_id);
670
671 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
672 itemkey => l_itemkey,
673 aname => 'ORDER_TYPE_ID',
674 avalue => l_order_type_id);
675
676 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
677 itemkey => l_itemkey,
678 aname => 'ORGANIZATION_ID',
679 avalue => l_ship_from_org_id);
680
681 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
682 itemkey => l_itemkey,
683 aname => 'ORG_ID',
684 avalue => l_org_id);
685
686 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
687 itemkey => l_itemkey,
688 aname =>'CHART_OF_ACCOUNTS_ID',
689 avalue =>l_chart_of_accounts_id);
690
691 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
692 itemkey => l_itemkey,
693 aname =>'SALESREP_ID',
694 avalue =>l_salesrep_id);
695
696 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
697 itemkey => l_itemkey,
698 aname =>'INVENTORY_ITEM_ID',
699 avalue =>l_inventory_item_id);
700
701 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
702 itemkey => l_itemkey,
703 aname =>'ITEM_CATEGORY_ID',
704 avalue =>l_item_category_id);
705
706 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
707 itemkey => l_itemkey,
708 aname =>'BUDGET_ID',
709 avalue =>p_budget_id);
710
711 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
712 itemkey => l_itemkey,
713 aname =>'OFFER_ID',
714 avalue =>p_offer_id);
715
716 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
717 itemkey => l_itemkey,
718 aname =>'PRICE_ADJ_ID',
719 avalue =>p_price_adj_id);
720
721 wf_engine.SetItemAttrText(itemtype => l_itemtype,
722 itemkey => l_itemkey,
723 aname =>'ACCOUNT_ID',
724 avalue =>l_account_id);
725
726 wf_engine.SetItemAttrText(itemtype => l_itemtype,
727 itemkey => l_itemkey,
728 aname =>'ACCOUNT_TYPE',
729 avalue =>p_account_type);
730
731 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
732 itemkey => l_itemkey,
733 aname =>'CLAIM_ID',
734 avalue =>p_claim_id);
735
736 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
737 itemkey => l_itemkey,
738 aname =>'UTILIZATION_ID',
739 avalue =>p_utilization_id);
740
741 IF l_item_type_code = Oe_Globals.G_ITEM_OPTION THEN --for future use
742 l_option_flag := 'Y';
743 ELSE
744 l_option_flag := 'N';
745 END IF;
746
747 wf_engine.SetItemAttrText(itemtype => l_itemtype,
748 itemkey => l_itemkey,
749 aname =>'OPTION_FLAG',
750 avalue =>l_option_flag);
751
752 l_debug_flag := fnd_profile.value('ACCOUNT_GENERATOR:DEBUG_MODE');
753
754 IF G_DEBUG_HIGH_ON THEN
755 OZF_UTILITY_PVT.debug_message('Debug Flag: ' || l_debug_flag );
756 END IF;
757
758 IF l_debug_flag = 'Y' THEN
759
760 l_role_name := Get_User_Role;
761
762 wf_engine.SetItemOwner(itemtype => l_itemtype,
763 itemkey => l_itemkey,
764 owner => l_role_name);
765 END IF;
766
767 IF G_DEBUG_HIGH_ON THEN
768 OZF_UTILITY_PVT.debug_message('submitting the fnd_flex_workflow_generate process');
769 END IF;
770 l_result := Fnd_Flex_Workflow.GENERATE('OZFACCTG',
771 l_itemkey,
772 TRUE,
773 x_return_ccid,
774 x_concat_segs,
775 x_concat_ids,
776 x_concat_descrs,
777 l_errmsg,
778 l_new_comb);
779
780 IF l_result THEN
781 IF G_DEBUG_HIGH_ON THEN
782 OZF_UTILITY_PVT.debug_message('Success OZFACCTG WF'||'-'||x_return_ccid);
783 END IF;
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785 ELSE
786 IF l_debug_flag = 'Y' THEN
787 IF G_DEBUG_HIGH_ON THEN
788 OZF_UTILITY_PVT.debug_message('Returning Default on Error'||'-'||l_account_id);
789 END IF;
790 x_return_ccid := l_account_id;
791 x_return_status := FND_API.G_RET_STS_SUCCESS;
792 ELSE
793 IF G_DEBUG_HIGH_ON THEN
794 OZF_UTILITY_PVT.debug_message('Error in OZFACCTG WF'||'-'||x_return_ccid);
795 END IF;
796 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
797 FND_MESSAGE.SET_NAME('OZF','OZF_GL_ACC_GEN_FAILED');
798 FND_MSG_PUB.ADD;
799 END IF;
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 END IF;
802 END IF;
803
804 IF G_DEBUG_HIGH_ON THEN
805 OZF_UTILITY_PVT.debug_message('Private Api: ' || l_api_name || ' END');
806 END IF;
807 --Standard call to get message count and if count=1, get the message
808 FND_MSG_PUB.Count_And_Get (
809 p_encoded => FND_API.G_FALSE,
810 p_count => x_msg_count,
811 p_data => x_msg_data
812 );
813 EXCEPTION
814 WHEN NO_DATA_FOUND THEN
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
817 FND_MESSAGE.SET_NAME('OZF','OZF_COGS_NOT_FOUND');
818 FND_MESSAGE.SET_TOKEN('LINE_ID',l_line_id);
819 FND_MSG_PUB.ADD;
820 END IF;
821 FND_MSG_PUB.Count_And_Get (
822 p_encoded => FND_API.G_FALSE,
823 p_count => x_msg_count,
824 p_data => x_msg_data
825 );
826 WHEN FND_API.G_EXC_ERROR THEN
827 x_return_status := FND_API.G_RET_STS_ERROR;
828 -- Standard call to get message count and if count=1, get the message
829 FND_MSG_PUB.Count_And_Get (
830 p_encoded => FND_API.G_FALSE,
831 p_count => x_msg_count,
832 p_data => x_msg_data
833 );
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
836 -- Standard call to get message count and if count=1, get the message
837 FND_MSG_PUB.Count_And_Get (
838 p_encoded => FND_API.G_FALSE,
839 p_count => x_msg_count,
840 p_data => x_msg_data
841 );
842 WHEN OTHERS THEN
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
845 THEN
846 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
847 END IF;
848 -- Standard call to get message count and if count=1, get the message
849 FND_MSG_PUB.Count_And_Get (
850 p_encoded => FND_API.G_FALSE,
851 p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 END Start_Process; /* START_PROCESS */
855
856 /*===========================================================================+
857 | Name: GET_COST_SALE_ITEM_DERIVED |
858 | Purpose: Derives the COGS account for a line regardless of the option flag|
859 +===========================================================================*/
860 PROCEDURE Get_Cost_Sale_Item_Derived
861 (
862 itemtype IN VARCHAR2,
863 itemkey IN VARCHAR2,
864 actid IN NUMBER,
865 funcmode IN VARCHAR2,
866 result OUT NOCOPY VARCHAR2)
867 IS
868 l_cost_sale_item_derived VARCHAR2(240) DEFAULT NULL;
869 l_line_id NUMBER;
870 l_organization_id NUMBER;
871 l_inventory_item_id NUMBER;
872 fb_error_msg VARCHAR2(240) DEFAULT NULL;
873 l_error_msg VARCHAR2(240) DEFAULT NULL;
874 l_item_type_code VARCHAR2(30);
875 l_link_to_line_id NUMBER;
876 l_api_name VARCHAR2(240):= 'Get_Cost_Sale_Item_Derived';
877
878 CURSOR item_type_csr(p_line_id in number) IS
879 SELECT LINK_TO_LINE_ID,
880 ITEM_TYPE_CODE
881 FROM OE_ORDER_LINES_ALL
882 WHERE LINE_ID = p_line_id;
883
884 CURSOR cost_of_sales_acct_csr(p_line_id in number) IS
885 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
886 FROM OE_ORDER_LINES_ALL OL,
887 MTL_SYSTEM_ITEMS M
888 WHERE OL.LINE_ID = p_line_id
889 AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
890 AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
891
892 CURSOR cogs_acct_csr( p_inventory_item_id in number
893 ,p_organization_id in number) IS
894 SELECT NVL(COST_OF_SALES_ACCOUNT,0)
895 FROM MTL_SYSTEM_ITEMS
896 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
897 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
898
899 BEGIN
900 IF G_DEBUG_HIGH_ON THEN
901 OZF_UTILITY_PVT.debug_message('Private Api: ' || l_api_name || ' START');
902 END IF;
903
904 IF (FUNCMODE = 'RUN') THEN
905 l_line_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_ID');
906 l_organization_id := wf_engine.GetItemAttrNumber(itemtype,
907 itemkey,
908 'ORGANIZATION_ID');
909 l_inventory_item_id := wf_engine.GetItemAttrNumber(itemtype,
910 itemkey,
911 'INVENTORY_ITEM_ID');
912 l_cost_sale_item_derived := NULL;
913
914 IF G_DEBUG_HIGH_ON THEN
915 OZF_UTILITY_PVT.debug_message('line_id = ' || l_line_id);
916 OZF_UTILITY_PVT.debug_message('organization_id = ' || l_organization_id);
917 OZF_UTILITY_PVT.debug_message('inventory_item_id = ' || l_inventory_item_id);
918 END IF;
919
920 IF l_line_id IS NOT NULL THEN
921 OPEN cost_of_sales_acct_csr(l_line_id);
922 FETCH cost_of_sales_acct_csr INTO l_cost_sale_item_derived;
923 CLOSE cost_of_sales_acct_csr;
924
925 IF l_cost_sale_item_derived is null THEN
926 OPEN item_type_csr(l_line_id);
927 FETCH item_type_csr INTO l_link_to_line_id, l_item_type_code;
928 CLOSE item_type_csr;
929
930 IF l_item_type_code <> Oe_Globals.G_ITEM_CONFIG
931 THEN
932 FND_MESSAGE.SET_NAME('OZF','OZF_GENERATE_CCID_FAILED');
933 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
934 FND_MESSAGE.SET_ENCODED(fb_error_msg);
935 l_error_msg := FND_MESSAGE.GET;
936 wf_engine.setItemAttrText(itemtype,
937 itemkey,
938 'ERROR_MESSAGE',
939 l_error_msg);
940 result := 'COMPLETE:FAILURE';
941 RETURN;
942 ELSE -- if l_item_type_code = Oe_Globals.G_ITEM_CONFIG
943 OPEN cost_of_sales_acct_csr(l_link_to_line_id);
944 FETCH cost_of_sales_acct_csr INTO l_cost_sale_item_derived;
945 CLOSE cost_of_sales_acct_csr;
946 IF l_cost_sale_item_derived IS NULL THEN
947 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
948 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
949 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
950 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
951 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
952 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
953 FND_MESSAGE.SET_ENCODED(fb_error_msg);
954 l_error_msg := FND_MESSAGE.GET;
955 wf_engine.setItemAttrText(itemtype,
956 itemkey,
957 'ERROR_MESSAGE',
958 l_error_msg);
959 result := 'COMPLETE:FAILURE';
960 RETURN;
961 END IF;
962 END IF;
963 END IF;
964 ELSE
965 IF l_inventory_item_id IS NOT NULL THEN
966 OPEN cogs_acct_csr(l_inventory_item_id, l_organization_id);
967 FETCH cogs_acct_csr INTO l_cost_sale_item_derived;
968 CLOSE cogs_acct_csr;
969 END IF;
970 END IF; -- line id not null
971
972 IF l_cost_sale_item_derived = 0 THEN
973 FND_MESSAGE.SET_NAME('OZF','OE_COGS_CCID_GEN_FAILED');
974 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
975 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
976 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
977 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
978 fb_error_msg := FND_MESSAGE.GET_ENCODED;
979 FND_MESSAGE.SET_ENCODED(fb_error_msg);
980 l_error_msg := FND_MESSAGE.GET;
981 wf_engine.setItemAttrText(itemtype,
982 itemkey,
983 'ERROR_MESSAGE',
984 l_error_msg);
985 result := 'COMPLETE:FAILURE';
986 RETURN;
987 END IF;
988
989 IF l_cost_sale_item_derived is not null THEN
990 wf_engine.setItemAttrNumber(itemtype,
991 itemkey,
992 'GENERATED_CCID',
993 TO_NUMBER(l_cost_sale_item_derived));
994 result := 'COMPLETE:SUCCESS';
995 RETURN;
996 ELSE
997 result := 'COMPLETE:FAILURE';
998 RETURN;
999 END IF;
1000
1001 ELSIF (funcmode = 'CANCEL') THEN
1002 result := wf_engine.eng_completed;
1003 RETURN;
1004 ELSE
1005 result := '';
1006 RETURN;
1007 END IF;
1008 IF G_DEBUG_HIGH_ON THEN
1009 OZF_UTILITY_PVT.debug_message('Private Api: ' || l_api_name || 'END');
1010 END IF;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 wf_core.context('AMS_ACCT_GENERATOR','GET_COST_SALE_ITEM_DERIVED',
1015 itemtype,itemkey,TO_CHAR(actid),funcmode);
1016 result := 'COMPLETE:FAILURE';
1017 RAISE;
1018 END GET_COST_SALE_ITEM_DERIVED;
1019
1020 END Ozf_Acct_Generator;