[Home] [Help]
PACKAGE BODY: APPS.OE_FLEX_COGS_PUB
Source
1 PACKAGE BODY OE_Flex_Cogs_Pub AS
2 /* $Header: OEXWCGSB.pls 120.2.12020000.3 2012/09/10 08:17:44 sujithku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Flex_Cogs_PUB';
5
6 TYPE Chart_Of_Accounts_Rec_Type IS RECORD
7 ( ship_from_org_id NUMBER := NULL
8 , chart_of_accounts_id NUMBER := NULL
9 );
10
11 TYPE Chart_Of_Accounts_Tbl_Type IS TABLE OF Chart_Of_Accounts_Rec_Type
12 INDEX BY BINARY_INTEGER;
13
14 G_Chart_Of_Accounts_Rec Chart_Of_Accounts_Rec_Type;
15 G_Chart_Of_Accounts_Tbl Chart_Of_Accounts_Tbl_Type;
16 -- Start of Comments
17 -- API name OE_Flex_Cogs_PUB
18 -- Type Public
19 -- Version Current version = 1.0
20 -- Initial version = 1.0
21
22 /*
23 Procedure log_error(p_error IN Varchar2, p_dummy IN NUMBER Default 0)
24 Begin
25 qp_util.log_error(1,NULL, NULL, NULL, NULL,NULL,NULL, NULL,'ONT_COGS',
26 p_error, 'ONT_COGS');
27 End;
28 */
29
30 /*===========================================================================+
31 | Name: START_PROCESS |
32 | Purpose: Runs the Workflow process to create the COGS account |
33 +===========================================================================*/
34
35 FUNCTION Start_Process
36 (
37 p_api_version_number IN NUMBER,
38 p_line_id IN NUMBER,
39 x_return_ccid OUT NOCOPY NUMBER,
40 x_concat_segs OUT NOCOPY VARCHAR2,
41 x_concat_ids OUT NOCOPY VARCHAR2,
42 x_concat_descrs OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2)
45 RETURN VARCHAR2
46 IS
47
48 l_itemtype VARCHAR2(30) := 'OECOGS';
49 l_itemkey VARCHAR2(38);
50 l_result BOOLEAN;
51 l_return_status VARCHAR2(1);
52 l_chart_of_accounts_id NUMBER;
53 l_option_flag VARCHAR2(1);
54 l_errmsg VARCHAR2(2000);
55 l_api_version_number CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process';
57 l_header_id NUMBER;
58 l_line_id NUMBER;
59 l_ship_from_org_id NUMBER;
60 l_commitment_id NUMBER;
61 l_sold_to_org_id NUMBER;
62 l_org_id NUMBER;
63 l_salesrep_id NUMBER;
64 l_inventory_item_id NUMBER;
65 l_item_type_code VARCHAR2(30);
66 l_line_category_code VARCHAR2(30);
67 l_reference_line_id NUMBER;
68 l_order_category_code VARCHAR2(30);
69 l_order_type_id NUMBER;
70 l_new_combination BOOLEAN; -- 1775305
71 lx_return_ccid NUMBER;
72 lx_concat_segs VARCHAR2(1000);
73 lx_concat_ids VARCHAR2(1000);
74 lx_concat_descrs VARCHAR2(1000);
75 lx_msg_count NUMBER;
76 lx_msg_data VARCHAR2(1000);
77 g_stmt VARCHAR2(500);
78
79 --3406720
80 l_aname wf_engine.nametabtyp;
81 l_avalue wf_engine.numtabtyp;
82 l_aname2 wf_engine.nametabtyp;
83 l_avaluetext wf_engine.texttabtyp;
84 l_chart_of_accounts_rec Chart_Of_Accounts_Rec_Type;
85 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
86 --Exception Management begin
87 l_order_source_id NUMBER;
88 l_orig_sys_document_ref VARCHAR2(50);
89 l_orig_sys_line_ref VARCHAR2(50);
90 l_orig_sys_shipment_ref VARCHAR2(50);
91 l_change_sequence VARCHAR2(50);
92 l_source_document_type_id NUMBER;
93 l_source_document_id NUMBER;
94 l_source_document_line_id NUMBER;
95 --Exception Management end
96 l_retsourcelin_pref VARCHAR2(10) := ''; --14374856
97 l_lin_org NUMBER:= 0; --14374856
98 BEGIN
99 /*oe_Debug_pub.setdebuglevel(5);*/
100 --Commented out the call to debug on for bug 3406720
101 --oe_debug_pub.debug_on;
102 --oe_debug_pub.initialize;
103 g_stmt:='1';
104
105 IF l_debug_level > 0 THEN
106 oe_debug_pub.add('Entering OE_Flex_Cogs_Pub.Start_process : '|| to_char(p_line_id),1);
107 END IF;
108
109 IF NOT FND_API.Compatible_API_Call
110 ( l_api_version_number
111 , p_api_version_number
112 , l_api_name
113 , G_PKG_NAME
114 )
115 THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END IF;
118
119 IF p_line_id IS NULL OR
120 p_line_id = FND_API.G_MISS_NUM THEN
121
122 FND_MESSAGE.SET_NAME('ONT','OE_COGS_LINE_ID_MISSING');
123 OE_MSG_PUB.ADD;
124 RAISE FND_API.G_EXC_ERROR;
125
126 END IF;
127
128 g_stmt:='5';
129
130 BEGIN
131 SELECT REFERENCE_LINE_ID,
132 LINE_CATEGORY_CODE,
133 ORG_ID --14374856
134 INTO l_reference_line_id,
135 l_line_category_code,
136 l_lin_org --14374856
137 FROM OE_ORDER_LINES
138 WHERE LINE_ID = p_line_id;
139
140 EXCEPTION
141
142 WHEN NO_DATA_FOUND THEN
143 FND_MESSAGE.SET_NAME('ONT','OE_COGS_INVALID_LINE_ID');
144 FND_MESSAGE.SET_TOKEN('LINE_ID',l_line_id);
145 OE_MSG_PUB.ADD;
146 RAISE FND_API.G_EXC_ERROR;
147
148 END;
149
150 --14374856 START
151 l_retsourcelin_pref := NVL(oe_sys_parameters.Value('ONT_COGS_SOURCE_LINE_FOR_RMA',l_lin_org),'RSO');
152 --14374856 END
153
154 IF l_line_category_code = 'RETURN' and
155 l_reference_line_id is not null THEN
156 --14374856 START
157 IF l_retsourcelin_pref = 'RMA' THEN
158 l_line_id := p_line_id;
159 ELSE
160 --14374856 END
161 l_line_id := l_reference_line_id;
162 END IF; --14374856
163 ELSE
164 l_line_id := p_line_id;
165 END IF;
166
167 g_stmt:='10';
168 /* Retreive the header and line details */
169
170 BEGIN
171
172 SELECT HEADER_ID,
173 ORG_ID,
174 SHIP_FROM_ORG_ID,
175 SOLD_TO_ORG_ID,
176 COMMITMENT_ID,
177 SALESREP_ID,
178 INVENTORY_ITEM_ID,
179 ITEM_TYPE_CODE,
180 ORDER_SOURCE_ID,
181 ORIG_SYS_DOCUMENT_REF,
182 ORIG_SYS_LINE_REF,
183 ORIG_SYS_SHIPMENT_REF,
184 CHANGE_SEQUENCE,
185 SOURCE_DOCUMENT_TYPE_ID,
186 SOURCE_DOCUMENT_ID,
187 SOURCE_DOCUMENT_LINE_ID
188 INTO l_header_id,
189 l_org_id,
190 l_ship_from_org_id,
191 l_sold_to_org_id,
192 l_commitment_id,
193 l_salesrep_id,
194 l_inventory_item_id,
195 l_item_type_code,
196 l_order_source_id,
197 l_orig_sys_document_ref,
198 l_orig_sys_line_ref,
199 l_orig_sys_shipment_ref,
200 l_change_sequence,
201 l_source_document_type_id,
202 l_source_document_id,
203 l_source_document_line_id
204 FROM OE_ORDER_LINES
205 WHERE LINE_ID = l_line_id;
206
207 -- Exception Management begin Set message context
208 OE_MSG_PUB.set_msg_context(
209 p_entity_code => 'LINE'
210 ,p_entity_id => l_line_id
211 ,p_header_id => l_header_id
212 ,p_line_id => l_line_id
213 ,p_order_source_id => l_order_source_id
214 ,p_orig_sys_document_ref => l_orig_sys_document_ref
215 ,p_orig_sys_document_line_ref => l_orig_sys_line_ref
216 ,p_orig_sys_shipment_ref => l_orig_sys_shipment_ref
217 ,p_change_sequence => l_change_sequence
218 ,p_source_document_type_id => l_source_document_type_id
219 ,p_source_document_id => l_source_document_id
220 ,p_source_document_line_id => l_source_document_line_id );
221 --Exception Management end
222
223 EXCEPTION
224
225 WHEN NO_DATA_FOUND THEN
226 -- Set message context
227 OE_MSG_PUB.set_msg_context(
228 p_entity_code => 'LINE'
229 ,p_entity_id => l_line_id
230 ,p_line_id => l_line_id
231 );
232
233 FND_MESSAGE.SET_NAME('ONT','OE_COGS_INVALID_LINE_ID');
234 FND_MESSAGE.SET_TOKEN('LINE_ID',l_line_id);
235 OE_MSG_PUB.ADD;
236 RAISE FND_API.G_EXC_ERROR;
237 END;
238
239 g_stmt:='15';
240
241 SELECT ORDER_CATEGORY_CODE,
242 ORDER_TYPE_ID
243 INTO l_order_category_code,
244 l_order_type_id
245 FROM OE_ORDER_HEADERS
246 WHERE HEADER_ID = l_header_id;
247
248 IF l_ship_from_org_id IS NULL THEN
249
250 FND_MESSAGE.SET_NAME('ONT','OE_COGS_WAREHOUSE_MISSING');
251 FND_MESSAGE.SET_TOKEN('LINE_ID',l_line_id);
252 OE_MSG_PUB.ADD;
253 RAISE FND_API.G_EXC_ERROR;
254
255 END IF;
256
257 g_stmt:='20';
258
259 /* Retreive the Chart of accounts Id */
260 -- 3406720 Cached the value for Chart of accounts id
261 IF G_Chart_Of_Accounts_Tbl.exists(l_ship_from_org_id) THEN
262 l_chart_of_accounts_id := G_Chart_Of_Accounts_Tbl(l_ship_from_org_id).Chart_Of_Accounts_Id;
263 ELSE
264 /*SELECT CHART_OF_ACCOUNTS_ID
265 INTO l_chart_of_accounts_id
266 FROM ORG_ORGANIZATION_DEFINITIONS
267 WHERE ORGANIZATION_ID = l_ship_from_org_id; */
268
269 SELECT /*+ ordered use_nl(HOI1,HOI2,GSOB) */
270 GSOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
271 INTO l_chart_of_accounts_id
272 FROM HR_ORGANIZATION_UNITS HOU ,
273 HR_ORGANIZATION_INFORMATION HOI1 ,
274 HR_ORGANIZATION_INFORMATION HOI2 ,
275 GL_SETS_OF_BOOKS GSOB
276 WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
277 AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
278 AND HOI1.ORG_INFORMATION1 = 'INV'
279 AND HOI1.ORG_INFORMATION2 = 'Y'
280 AND ( HOI1.ORG_INFORMATION_CONTEXT || '' ) = 'CLASS'
281 AND ( HOI2.ORG_INFORMATION_CONTEXT || '' ) ='Accounting Information'
282 AND HOI2.ORG_INFORMATION1
283 = GSOB.SET_OF_BOOKS_ID
284 and hou.organization_id = l_ship_from_org_id;
285
286 l_chart_of_accounts_rec.ship_from_org_id := l_ship_from_org_id;
287 l_chart_of_accounts_rec.chart_of_accounts_id := l_chart_of_accounts_id;
288 G_Chart_Of_Accounts_Tbl(l_ship_from_org_id) := l_chart_of_accounts_rec;
289 END IF;
290 IF l_debug_level > 0 THEN
291 oe_debug_pub.add('Chart Of accounts Id : '|| to_char(l_chart_of_accounts_id),2);
292 END IF;
293 /* Initialize the workflow item attributes */
294 l_itemkey := FND_FLEX_WORKFLOW.INITIALIZE
295 ('SQLGL',
296 'GL#',
297 l_chart_of_accounts_id,
298 'OECOGS'
299 );
300 IF l_debug_level > 0 THEN
301 oe_debug_pub.add('Item Key : '||l_itemkey,2);
302 oe_debug_pub.add('Initilizing Workflow Item Attributes');
303 END If;
304 g_stmt:='35';
305
306 l_aname(1) := 'COMMITMENT_ID';
307 l_avalue(1):= l_commitment_id;
308
309 g_stmt:='40';
310
311 l_aname(2) := 'CUSTOMER_ID';
312 l_avalue(2):= l_sold_to_org_id;
313
314 g_stmt:='45';
315
316 l_aname2(1) := 'ORDER_CATEGORY';
317 l_avaluetext(1):= l_order_category_code;
318
319 g_stmt:='50';
320
321 l_aname(3) := 'HEADER_ID';
322 l_avalue(3):= l_header_id;
323
324 g_stmt:='55';
325
326 l_aname(4) := 'LINE_ID';
327 l_avalue(4):= l_line_id;
328
329 g_stmt:='60';
330
331 l_aname(5) := 'ORDER_TYPE_ID';
332 l_avalue(5):= l_order_type_id;
333
334 l_aname(6) := 'ORGANIZATION_ID';
335 l_avalue(6):= l_ship_from_org_id;
336
337 l_aname(7) := 'ORG_ID';
338 l_avalue(7):= l_org_id;
339
340 l_aname(8) := 'CHART_OF_ACCOUNTS_ID';
341 l_avalue(8):= l_chart_of_accounts_id;
342
343 l_aname(9) := 'SALESREP_ID';
344 l_avalue(9):= l_salesrep_id;
345
346 l_aname(10) := 'INVENTORY_ITEM_ID';
347 l_avalue(10):=l_inventory_item_id;
348
349 wf_engine.SetItemAttrNumberArray(l_itemtype
350 , l_itemkey
351 , l_aname
352 , l_avalue
353 );
354
355 g_stmt:='65';
356
357 IF l_item_type_code = OE_GLOBALS.G_ITEM_OPTION THEN
358
359 l_option_flag := 'Y';
360 ELSE
361 l_option_flag := 'N';
362
363 END IF;
364
365 l_aname2(2) := 'OPTION_FLAG';
366 l_avaluetext(2):= l_option_flag;
367 g_stmt:='70';
368
369 wf_engine.SetItemAttrTextArray(itemtype => l_itemtype,
370 itemkey => l_itemkey,
371 aname => l_aname2,
372 avalue => l_avaluetext);
373 IF l_debug_level > 0 THEN
374 oe_debug_pub.add('Calling FND_ELEX_WORKFLOW.GENERATE from START_PROCESS');
375 END IF;
376 g_stmt:='75';
377 l_result := FND_FLEX_WORKFLOW.GENERATE( itemtype => 'OECOGS', -- 1775305
378 itemkey => l_itemkey,
379 insert_if_new => TRUE,
380 ccid => lx_return_ccid,
381 concat_segs => lx_concat_segs,
382 concat_ids => lx_concat_ids,
383 concat_descrs => lx_concat_descrs,
384 error_message => l_errmsg,
385 new_combination => l_new_combination);
386 Begin
387 g_stmt:='assigning back ccid';
388 x_return_ccid:=lx_return_ccid;
389
390 g_stmt:='assiging back concat segs';
391 x_concat_segs:=lx_concat_segs;
392
393 g_stmt:='assigning back concat_ids';
394 x_concat_ids :=lx_concat_ids;
395
396 g_stmt:='assigning back concat descrs';
397 x_concat_descrs:=lx_concat_descrs;
398
399 Exception
400 When Others Then
401 oe_debug_pub.add(SQLERRM||':'||g_stmt);
402 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
403 End;
404
405 IF INSTR(l_errmsg, CHR(0)) > 0 THEN -- 2352606
406 fnd_message.set_encoded(l_errmsg);
407 l_errmsg := fnd_message.get;
408 END IF;
409
410 g_stmt:='80';
411
412 IF l_debug_level > 0 THEN
413 oe_debug_pub.add('Return CCID : '|| x_return_ccid,2);
414 oe_debug_pub.add('Concat Segments : '|| x_concat_segs,2);
415 oe_debug_pub.add('Concat Id : '|| x_concat_ids,2);
416 oe_debug_pub.add('Concat Descriptions : '|| x_concat_descrs,2);
417 END IF;
418
419 IF l_result THEN
420 l_return_status := FND_API.G_RET_STS_SUCCESS;
421 ELSE
422 IF l_debug_level > 0 THEN
423 oe_debug_pub.add('Error Message : '|| l_errmsg,2);
424 END IF;
425 FND_MESSAGE.SET_NAME('ONT','OE_COGS_ACC_GEN_FAILED');
426 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE',l_errmsg);
427 OE_MSG_PUB.ADD;
428 RAISE FND_API.G_EXC_ERROR;
429 END IF;
430 oe_debug_pub.add('Exiting OE_Flex_Cogs_Pub.Start_process : '|| l_return_status, 1); -- 2352606
431 RETURN l_return_status;
432 EXCEPTION
433
434 WHEN NO_DATA_FOUND THEN
435 FND_MESSAGE.SET_NAME('ONT','OE_COGS_COA_ID_NOT_FOUND');
436 FND_MESSAGE.SET_TOKEN('ORGANIZATION_ID',l_ship_from_org_id);
437 OE_MSG_PUB.ADD;
438
439 x_msg_count := OE_MSG_PUB.Count_Msg; -- 2352606 start
440 IF x_msg_count > 0 THEN
441 x_msg_data := OE_MSG_PUB.Get
442 ( p_encoded => FND_API.G_FALSE
443 , p_msg_index => OE_MSG_PUB.G_LAST
444 );
445 OE_MSG_PUB.Reset;
446 END IF;
447 oe_debug_pub.add('Exiting OE_Flex_Cogs_Pub.Start_process : NDF', 1); -- 2352606 end
448
449 oe_debug_pub.add(SQLERRM||':'||g_stmt);
450 return FND_API.G_RET_STS_ERROR;
451
452 WHEN FND_API.G_EXC_ERROR THEN
453 x_msg_count := OE_MSG_PUB.Count_Msg; -- 2352606 start
454 IF x_msg_count > 0 THEN
455 x_msg_data := OE_MSG_PUB.Get
456 ( p_encoded => FND_API.G_FALSE
457 , p_msg_index => OE_MSG_PUB.G_LAST
458 );
459 OE_MSG_PUB.Reset;
460 END IF;
461 oe_debug_pub.add('Exiting OE_Flex_Cogs_Pub.Start_process : E', 1); -- 2352606 end
462
463 oe_debug_pub.add(SQLERRM||':'||g_stmt);
464 return FND_API.G_RET_STS_ERROR;
465
466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
467
468 -- Get message count and data
469
470 x_msg_count := OE_MSG_PUB.Count_Msg; -- 2352606 start
471 IF x_msg_count > 0 THEN
472 x_msg_data := OE_MSG_PUB.Get
473 ( p_encoded => FND_API.G_FALSE
474 , p_msg_index => OE_MSG_PUB.G_LAST
475 );
476 OE_MSG_PUB.Reset;
477 END IF;
478 oe_debug_pub.add('Exiting OE_Flex_Cogs_Pub.Start_process : U', 1); -- 2352606 end
479
480 oe_debug_pub.add(SQLERRM||':'||g_stmt);
481 return FND_API.G_RET_STS_UNEXP_ERROR ;
482
483 WHEN OTHERS THEN
484
485 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
486 THEN
487 OE_MSG_PUB.Add_Exc_Msg
488 ( G_PKG_NAME
489 , 'Start_Process'
490 );
491
492 END IF;
493
494 -- Get message count and data
495
496 x_msg_count := OE_MSG_PUB.Count_Msg; -- 2352606 start
497 IF x_msg_count > 0 THEN
498 x_msg_data := OE_MSG_PUB.Get
499 ( p_encoded => FND_API.G_FALSE
500 , p_msg_index => OE_MSG_PUB.G_LAST
501 );
502 OE_MSG_PUB.Reset;
503 END IF;
504
505 oe_debug_pub.add('Exiting OE_Flex_Cogs_Pub.Start_process : O', 1); -- 2352606 end
506 oe_debug_pub.add(SQLERRM||':'||g_stmt);
507 return FND_API.G_RET_STS_UNEXP_ERROR;
508
509 END Start_Process; /* START_PROCESS */
510
511 /*===========================================================================+
512 | Name: GET_COST_SALE_ITEM_DERIVED |
513 | Purpose: Derives the COGS account for a line regardless of the option flag|
514 +===========================================================================*/
515
516 PROCEDURE Get_Cost_Sale_Item_Derived
517 (
518 itemtype IN VARCHAR2,
519 itemkey IN VARCHAR2,
520 actid IN NUMBER,
521 funcmode IN VARCHAR2,
522 result OUT NOCOPY VARCHAR2)
523 IS
524 l_cost_sale_item_derived VARCHAR2(240) DEFAULT NULL;
525 l_line_id NUMBER;
526 l_organization_id NUMBER;
527 l_inventory_item_id NUMBER;
528 fb_error_msg VARCHAR2(240) DEFAULT NULL;
529 l_error_msg VARCHAR2(240) DEFAULT NULL;
530 l_item_type_code VARCHAR2(30);
531 l_link_to_line_id NUMBER;
532
533 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
534 BEGIN
535 -- start data fix project
536 OE_STANDARD_WF.Set_Msg_Context(actid);
537 -- end data fix project
538 IF l_debug_level > 0 THEN
539 oe_debug_pub.add('Entering OE_Flex_Cogs_Pub.GET_COST_SALE_ITEM_DERIVED');
540 oe_debug_pub.add(' Item Type : '||itemtype,2);
541 oe_debug_pub.add(' Item Key : '||itemkey,2);
542 oe_debug_pub.add(' Activity Id : '||to_char(actid),2);
543 oe_debug_pub.add(' funcmode : '||funcmode,2);
544 END IF;
545 IF (FUNCMODE = 'RUN') THEN
546 l_line_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_ID');
547 l_organization_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORGANIZATION_ID');
548 l_inventory_item_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'INVENTORY_ITEM_ID');
549
550 SELECT LINK_TO_LINE_ID,
551 ITEM_TYPE_CODE
552 INTO l_link_to_line_id,
553 l_item_type_code
554 FROM OE_ORDER_LINES
555 WHERE LINE_ID = l_line_id;
556
557 l_cost_sale_item_derived := NULL;
558
559 IF l_line_id IS NOT NULL THEN
560 BEGIN
561 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
562 INTO l_cost_sale_item_derived
563 FROM OE_ORDER_LINES OL,
564 MTL_SYSTEM_ITEMS M
565 WHERE OL.LINE_ID = l_line_id
566 AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
567 AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
568 EXCEPTION
569 WHEN NO_DATA_FOUND THEN
570
571 IF l_item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
572
573 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
574 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
575 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
576 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
577 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
578 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
579 FND_MESSAGE.SET_ENCODED(fb_error_msg);
580 l_error_msg := FND_MESSAGE.GET;
581 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
582 result := 'COMPLETE:FAILURE';
583 RETURN;
584 END IF;
585 END;
586 END IF;
587
588 IF l_cost_sale_item_derived IS NULL THEN
589
590 IF l_item_type_code = OE_GLOBALS.G_ITEM_CONFIG THEN
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add('Going for Model line for CONFIG',2);
593 END IF;
594 BEGIN
595
596 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
597 INTO l_cost_sale_item_derived
598 FROM OE_ORDER_LINES OL,
599 MTL_SYSTEM_ITEMS M
600 WHERE OL.LINE_ID = l_link_to_line_id
601 AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
602 AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
603 EXCEPTION
604 WHEN NO_DATA_FOUND THEN
605 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
606 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
607 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
608 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
609 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
610 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
611 FND_MESSAGE.SET_ENCODED(fb_error_msg);
612 l_error_msg := FND_MESSAGE.GET;
613 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
614 result := 'COMPLETE:FAILURE';
615 RETURN;
616 END;
617
618 END IF;
619 END IF;
620
621 IF l_cost_sale_item_derived = 0 THEN
622
623 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
624 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
625 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
626 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
627 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
628
629 fb_error_msg := FND_MESSAGE.GET_ENCODED;
630 FND_MESSAGE.SET_ENCODED(fb_error_msg);
631 l_error_msg := FND_MESSAGE.GET;
632
633 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
634 result := 'COMPLETE:FAILURE';
635 RETURN;
636
637 END IF;
638
639 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(l_cost_sale_item_derived));
640 result := 'COMPLETE:SUCCESS';
641 IF l_debug_level > 0 THEN
642 oe_debug_pub.add('Input Paramerers : ');
643 oe_debug_pub.add('Line id :'||to_char(l_line_id));
644 oe_debug_pub.add('Organization id :'||to_char(l_organization_id));
645 oe_debug_pub.add('Output : ');
646 oe_debug_pub.add('Generated CCID :'||l_cost_sale_item_derived);
647
648 oe_debug_pub.add('Exiting from OE_Flex_COGS_Pub.Get_Cost_Sale_Item_Derived',1);
649 END IF;
650 RETURN;
651 ELSIF (funcmode = 'CANCEL') THEN
652 result := wf_engine.eng_completed;
653 RETURN;
654 ELSE
655 result := '';
656 RETURN;
657 END IF;
658 EXCEPTION
659
660 WHEN OTHERS THEN
661 wf_core.context('OE_FLEX_COGS_PUB','GET_COST_SALE_ITEM_DERIVED',
662 itemtype,itemkey,TO_CHAR(actid),funcmode);
663 result := 'COMPLETE:FAILURE';
664 -- start data fix project
665 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
666 p_itemtype => itemtype,
667 p_itemkey => itemkey);
668 OE_STANDARD_WF.Save_Messages;
669 OE_STANDARD_WF.Clear_Msg_Context;
670 -- end data fix project
671 RAISE;
672 END GET_COST_SALE_ITEM_DERIVED;
673
674 /*===========================================================================+
675 | Name: GET_MODEL_DERIVED |
676 | Purpose: Derives the COGS account for an option line from it's option |
677 +===========================================================================*/
678
679 PROCEDURE Get_Model_Derived
680 (
681 itemtype IN VARCHAR2,
682 itemkey IN VARCHAR2,
683 actid IN NUMBER,
684 funcmode IN VARCHAR2,
685 result OUT NOCOPY VARCHAR2)
686
687 IS
688 l_cost_sale_model_derived VARCHAR2(240) DEFAULT NULL;
689 l_line_id NUMBER;
690 l_organization_id NUMBER;
691 l_model_line_id NUMBER;
692 fb_error_msg VARCHAR2(240) DEFAULT NULL;
693 l_error_msg VARCHAR2(240) DEFAULT NULL;
694 l_ship_from_org_id NUMBER;
695 l_inventory_item_id NUMBER;
696 l_top_model_line_id NUMBER;
697
698 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
699 BEGIN
700 -- start data fix project
701 OE_STANDARD_WF.Set_Msg_Context(actid);
702 -- end data fix project
703 IF l_debug_level > 0 THEN
704 oe_debug_pub.add('Entering OE_Flex_Cogs_Pub.GET_COST_SALE_MODEL_DERIVED');
705
706 oe_debug_pub.add(' Item Type : '||itemtype,2);
707 oe_debug_pub.add(' Item Key : '||itemkey,2);
708 oe_debug_pub.add(' Activity Id : '||to_char(actid),2);
709 oe_debug_pub.add(' funcmode : '||funcmode,2);
710 END IF;
711 IF (FUNCMODE = 'RUN') THEN
712 l_line_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_ID');
713
714 SELECT TOP_MODEL_LINE_ID,
715 INVENTORY_ITEM_ID,
716 SHIP_FROM_ORG_ID
717 INTO l_top_model_line_id,
718 l_inventory_item_id,
719 l_ship_from_org_id
720 FROM OE_ORDER_LINES
721 WHERE LINE_ID = l_line_id;
722
723 l_model_line_id := l_top_model_line_id;
724 l_organization_id := l_ship_from_org_id;
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add('Model Line Id : '||to_char(l_model_line_id),2);
727 END IF;
728 l_cost_sale_model_derived := NULL;
729
730 IF (l_model_line_id IS NOT NULL) THEN
731 BEGIN
732 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
733 INTO l_cost_sale_model_derived
734 FROM OE_ORDER_LINES OL,
735 MTL_SYSTEM_ITEMS M
736 WHERE OL.LINE_ID = l_model_line_id
737 AND M.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
738 AND M.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID;
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
742 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
743 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
744 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
745 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
746 fb_error_msg := FND_MESSAGE.GET_ENCODED;
747 FND_MESSAGE.SET_ENCODED(fb_error_msg);
748 l_error_msg := FND_MESSAGE.GET;
749 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
750 result := 'COMPLETE:FAILURE';
751 RETURN;
752 END;
753 END IF;
754
755 IF l_cost_sale_model_derived = 0 THEN
756
757 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
758 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
759 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
760 FND_MESSAGE.SET_TOKEN('VALUE1',l_inventory_item_id);
761 FND_MESSAGE.SET_TOKEN('VALUE2',l_organization_id);
762
763 fb_error_msg := FND_MESSAGE.GET_ENCODED;
764 FND_MESSAGE.SET_ENCODED(fb_error_msg);
765 l_error_msg := FND_MESSAGE.GET;
766
767 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
768 result := 'COMPLETE:FAILURE';
769 RETURN;
770
771 END IF;
772
773 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(l_cost_sale_model_derived));
774 result := 'COMPLETE:SUCCESS';
775 IF l_debug_level > 0 THEN
776 oe_debug_pub.add('Input Paramerers : ');
777 oe_debug_pub.add('Line id :'||to_char(l_line_id));
778 oe_debug_pub.add('Output : ');
779 oe_debug_pub.add('Generated CCID :'||l_cost_sale_model_derived);
780
781 oe_debug_pub.add('Exiting from OE_Flex_COGS_Pub.Get_Model_Derived',1);
782 END IF;
783 RETURN;
784 ELSIF (funcmode = 'CANCEL') THEN
785 result := wf_engine.eng_completed;
786 RETURN;
787 ELSE
788 result := '';
789 RETURN;
790 END IF;
791 EXCEPTION
792 WHEN OTHERS THEN
793 wf_core.context('OE_FLEX_COGS_PUB','GET_MODEL_DERIVED',
794 itemtype,itemkey,TO_CHAR(actid),funcmode);
795 result := 'COMPLETE:FAILURE';
796 -- start data fix project
797 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
798 p_itemtype => itemtype,
799 p_itemkey => itemkey);
800 OE_STANDARD_WF.Save_Messages;
801 OE_STANDARD_WF.Clear_Msg_Context;
802 -- end data fix project
803 RAISE;
804 END Get_Model_Derived;
805
806
807 /*===========================================================================+
808 | Name: GET_ORDER_TYPE_DERIVED |
809 | Purpose: Derives the CCID from the Order type |
810 +===========================================================================*/
811
812 PROCEDURE Get_Order_Type_Derived
813 (
814 itemtype IN VARCHAR2,
815 itemkey IN VARCHAR2,
816 actid IN NUMBER,
817 funcmode IN VARCHAR2,
818 result OUT NOCOPY VARCHAR2)
819 IS
820 l_order_type_ccid VARCHAR2(240) DEFAULT NULL;
821 l_order_type_id NUMBER;
822 fb_error_msg VARCHAR2(240) DEFAULT NULL;
823 l_error_msg VARCHAR2(240) DEFAULT NULL;
824
825 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
826 BEGIN
827 -- start data fix project
828 OE_STANDARD_WF.Set_Msg_Context(actid);
829 -- end data fix project
830 IF l_debug_level > 0 THEN
831 oe_debug_pub.add('Entering OE_Flex_Cogs_Pub.GET_ORDER_TYPE_DERIVED');
832 oe_debug_pub.add(' Item Type : '||itemtype,2);
833 oe_debug_pub.add(' Item Key : '||itemkey,2);
834 oe_debug_pub.add(' Activity Id : '||to_char(actid),2);
835 oe_debug_pub.add(' funcmode : '||funcmode,2);
836 END IF;
837
838 IF (funcmode = 'RUN') THEN
839 l_order_type_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_TYPE_ID');
840 l_order_type_ccid := NULL;
841 IF (l_order_type_id IS NOT NULL) THEN
842 BEGIN
843 SELECT NVL(COST_OF_GOODS_SOLD_ACCOUNT, 0)
844 INTO l_order_type_ccid
845 FROM OE_TRANSACTION_TYPES_ALL
846 WHERE TRANSACTION_TYPE_ID = l_order_type_id;
847 EXCEPTION
848 WHEN NO_DATA_FOUND THEN
849 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
850 FND_MESSAGE.SET_TOKEN('PARAM1','Order Type Id');
851 FND_MESSAGE.SET_TOKEN('PARAM2','');
852 FND_MESSAGE.SET_TOKEN('VALUE1',l_order_type_id);
853 FND_MESSAGE.SET_TOKEN('VALUE2','');
854 fb_error_msg := FND_MESSAGE.GET_ENCODED;
855 FND_MESSAGE.SET_ENCODED(fb_error_msg);
856 l_error_msg := FND_MESSAGE.GET;
857 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
858 result := 'COMPLETE:FAILURE';
859 RETURN;
860 END;
861 END IF;
862
863 IF l_order_type_ccid = 0 THEN
864
865 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
866 FND_MESSAGE.SET_TOKEN('PARAM1','Order Type Id');
867 FND_MESSAGE.SET_TOKEN('PARAM2','');
868 FND_MESSAGE.SET_TOKEN('VALUE1',l_order_type_id);
869 FND_MESSAGE.SET_TOKEN('VALUE2','');
870
871 fb_error_msg := FND_MESSAGE.GET_ENCODED;
872 FND_MESSAGE.SET_ENCODED(fb_error_msg);
873 l_error_msg := FND_MESSAGE.GET;
874
875 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
876 result := 'COMPLETE:FAILURE';
877 RETURN;
878
879 END IF;
880
881 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(L_ORDER_TYPE_CCID));
882 result := 'COMPLETE:SUCCESS';
883 IF l_debug_level > 0 THEN
884 oe_debug_pub.add('Input Paramerers : ',2);
885 oe_debug_pub.add('Order Type ID :'||to_char(l_order_type_id),2);
886 oe_debug_pub.add('Output : ',2);
887 oe_debug_pub.add('Generated CCID :'||l_order_type_ccid,2);
888 END IF;
889 RETURN;
890 ELSIF (funcmode = 'CANCEL') THEN
891 result := wf_engine.eng_completed;
892 RETURN;
893 ELSE
894 result := '';
895 RETURN;
896 END IF;
897 EXCEPTION
898 WHEN OTHERS THEN
899 wf_core.context('OE_FLEX_COGS_PUB','GET_ORDER_TYPE_DERIVED',
900 itemtype,itemkey,TO_CHAR(actid),funcmode);
901 result := 'COMPLETE:FAILURE';
902 -- start data fix project
903 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
904 p_itemtype => itemtype,
905 p_itemkey => itemkey);
906 OE_STANDARD_WF.Save_Messages;
907 OE_STANDARD_WF.Clear_Msg_Context;
908 -- end data fix project
909 RAISE;
910 END Get_Order_Type_Derived;
911
912 /*===========================================================================+
913 | Name: GET_SALESREP_REV_DERIVED |
914 | Purpose: Derives the CCID from salesrep's revenue segment |
915 +===========================================================================*/
916
917 PROCEDURE Get_Salesrep_Rev_Derived
918 (
919 itemtype IN VARCHAR2,
920 itemkey IN VARCHAR2,
921 actid IN NUMBER,
922 funcmode IN VARCHAR2,
923 result OUT NOCOPY VARCHAR2)
924
925 IS
926
927 l_salesrep_rev_derived VARCHAR2(240) DEFAULT NULL;
928 l_salesrep_id NUMBER;
929 fb_error_msg VARCHAR2(240) DEFAULT NULL;
930 l_error_msg VARCHAR2(240) DEFAULT NULL;
931 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
932 BEGIN
933 -- start data fix project
934 OE_STANDARD_WF.Set_Msg_Context(actid);
935 -- end data fix project
936 IF l_debug_level > 0 THEN
937 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.GET_SALESREP_REV_DERIVED',1);
938 END IF;
939 IF (FUNCMODE = 'RUN') THEN
940 l_salesrep_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'SALESREP_ID');
941 IF l_debug_level > 0 THEN
942 oe_debug_pub.add('Sales rep id : '||to_char(l_salesrep_id),2);
943 END If;
944 l_salesrep_rev_derived := NULL;
945
946 IF (l_salesrep_id IS NOT NULL) THEN
947
948 BEGIN
949 SELECT NVL(GL_ID_REV, 0)
950 INTO l_salesrep_rev_derived
951 FROM RA_SALESREPS
952 WHERE SALESREP_ID = L_SALESREP_ID;
953
954
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957
958 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
959 FND_MESSAGE.SET_TOKEN('PARAM1','Sales rep id');
960 FND_MESSAGE.SET_TOKEN('PARAM2','');
961 FND_MESSAGE.SET_TOKEN('VALUE1',l_salesrep_id);
962 FND_MESSAGE.SET_TOKEN('VALUE2','');
963 fb_error_msg := FND_MESSAGE.GET_ENCODED;
964 FND_MESSAGE.SET_ENCODED(fb_error_msg);
965 l_error_msg := FND_MESSAGE.GET;
966 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
967 result := 'COMPLETE:FAILURE';
968 RETURN;
969 END;
970 END IF;
971
972 IF l_salesrep_rev_derived = 0 THEN
973
974 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
975 FND_MESSAGE.SET_TOKEN('PARAM1','Sales rep id');
976 FND_MESSAGE.SET_TOKEN('PARAM2','');
977 FND_MESSAGE.SET_TOKEN('VALUE1',l_salesrep_id);
978 FND_MESSAGE.SET_TOKEN('VALUE2','');
979
980 fb_error_msg := FND_MESSAGE.GET_ENCODED;
981 FND_MESSAGE.SET_ENCODED(fb_error_msg);
982 l_error_msg := FND_MESSAGE.GET;
983
984 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
985 result := 'COMPLETE:FAILURE';
986 RETURN;
987
988 END IF;
989
990 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(l_salesrep_rev_derived));
991 result := 'COMPLETE:SUCCESS';
992 IF l_debug_level > 0 THEN
993 oe_debug_pub.add('Input Paramerers : ',2);
994 oe_debug_pub.add('Salesrep ID :' || to_char(l_salesrep_id),2);
995 oe_debug_pub.add('Output : ',2);
996 oe_debug_pub.add('Generated CCID :'||l_salesrep_rev_derived,2);
997 END IF;
998 RETURN;
999 ELSIF (funcmode = 'CANCEL') THEN
1000 result := wf_engine.eng_completed;
1001 RETURN;
1002 ELSE
1003 result := '';
1004 RETURN;
1005 END IF;
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 wf_core.context('OE_FLEX_COGS_PUB','GET_SALESREP_REV_DERIVED',
1009 itemtype,itemkey,TO_CHAR(actid),funcmode);
1010 result := 'COMPLETE:FAILURE';
1011 -- start data fix project
1012 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1013 p_itemtype => itemtype,
1014 p_itemkey => itemkey);
1015 OE_STANDARD_WF.Save_Messages;
1016 OE_STANDARD_WF.Clear_Msg_Context;
1017 -- end data fix project
1018 RAISE;
1019 END Get_Salesrep_Rev_Derived;
1020
1021
1022 /*===========================================================================+
1023 | Name: Get_Type_From_Line |
1024 | Purpose: Get transaction type id from a line |
1025 +===========================================================================*/
1026 PROCEDURE Get_Type_From_Line
1027 ( itemtype IN VARCHAR2,
1028 itemkey IN VARCHAR2,
1029 actid IN NUMBER,
1030 funcmode IN VARCHAR2,
1031 result OUT NOCOPY VARCHAR2)
1032
1033 IS
1034 l_order_line_id NUMBER;
1035 l_header_id NUMBER;
1036 fb_error_msg VARCHAR2(240) DEFAULT NULL;
1037 l_error_msg VARCHAR2(240) DEFAULT NULL;
1038 l_order_type_id NUMBER; --a.k.a transaction_type_id, line_type_id
1039 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1040 BEGIN
1041 -- start data fix project
1042 OE_STANDARD_WF.Set_Msg_Context(actid);
1043 -- end data fix project
1044 IF l_debug_level > 0 THEN
1045 oe_debug_pub.add('Entering Get_type_from_line',2);
1046 END IF;
1047 --DBMS_OUTPUT.PUT_LINE('Entering get_line_from_line');
1048
1049 IF (FUNCMODE = 'RUN') THEN
1050 l_order_line_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_ID');
1051 IF l_debug_level > 0 THEN
1052 oe_debug_pub.add('Input Paramerers : ',2);
1053 oe_debug_pub.add('Order Line ID :'|| to_char(l_order_line_id),2);
1054 END IF;
1055
1056 IF (l_order_line_id IS NOT NULL) THEN
1057 BEGIN
1058
1059 Select line_type_id
1060 Into l_order_type_id
1061 From oe_order_lines_all
1062 Where line_id = l_order_line_id;
1063
1064 EXCEPTION
1065 WHEN NO_DATA_FOUND THEN
1066 --FND_MESSAGE.SET_NAME('ONT','OE_COGS_SALESREP_NOT_FOUND');
1067 --FND_MESSAGE.SET_TOKEN('LINEID',l_order_line_id);
1068 --fb_error_msg := FND_MESSAGE.GET_ENCODED;
1069 --FND_MESSAGE.SET_ENCODED(fb_error_msg);
1070 --l_error_msg := FND_MESSAGE.GET;
1071 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE','No line type id found in oe_order_lines_all');
1072 result := 'COMPLETE:FAILURE';
1073 RETURN;
1074
1075 END;
1076 wf_engine.setItemAttrNumber(itemtype,itemkey,'ORDER_TYPE_ID',l_order_type_id);
1077 result := 'COMPLETE:SUCCESS';
1078 ELSE
1079 result := 'COMPLETE:FAILURE';
1080 RETURN;
1081 END IF;
1082 IF l_debug_level > 0 THEN
1083 oe_debug_pub.add('Output : ',2);
1084 oe_debug_pub.add('Salesrep ID :'|| l_order_type_id,2);
1085 END IF;
1086 RETURN;
1087 ELSIF (funcmode = 'CANCEL') THEN
1088 result := wf_engine.eng_completed;
1089 RETURN;
1090 ELSE
1091 result := '';
1092 RETURN;
1093 END IF;
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 wf_core.context('OE_FLEX_COGS_PUB','GET_TYPE_FROM_LINE',
1097 itemtype,itemkey,TO_CHAR(actid),funcmode);
1098 result := 'COMPLETE:FAILURE';
1099 -- start data fix project
1100 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1101 p_itemtype => itemtype,
1102 p_itemkey => itemkey);
1103 OE_STANDARD_WF.Save_Messages;
1104 OE_STANDARD_WF.Clear_Msg_Context;
1105 -- end data fix project
1106 RAISE;
1107 END Get_Type_From_Line;
1108
1109
1110 /*===========================================================================+
1111 | Name: GET_SALESREP_ID |
1112 | Purpose: Derives the salesrep's ID |
1113 +===========================================================================*/
1114
1115 PROCEDURE Get_Salesrep_Id
1116 (
1117 itemtype IN VARCHAR2,
1118 itemkey IN VARCHAR2,
1119 actid IN NUMBER,
1120 funcmode IN VARCHAR2,
1121 result OUT NOCOPY VARCHAR2)
1122
1123 IS
1124 l_salesrep_id VARCHAR2(240) DEFAULT NULL;
1125 l_order_line_id NUMBER;
1126 l_header_id NUMBER;
1127 fb_error_msg VARCHAR2(240) DEFAULT NULL;
1128 l_error_msg VARCHAR2(240) DEFAULT NULL;
1129 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1130 BEGIN
1131 -- start data fix project
1132 OE_STANDARD_WF.Set_Msg_Context(actid);
1133 -- end data fix project
1134 IF l_debug_level > 0 THEN
1135 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.GET_SALESREP_ID',2);
1136 END IF;
1137 IF (FUNCMODE = 'RUN') THEN
1138 l_order_line_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_ID');
1139 l_header_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1140
1141 IF l_debug_level > 0 THEN
1142 oe_debug_pub.add('Input Paramerers : ',2);
1143 oe_debug_pub.add('Order Line ID :'|| to_char(l_order_line_id),2);
1144 oe_debug_pub.add('Order Header ID :'|| to_char(l_header_id),2);
1145 END IF;
1146 l_salesrep_id := NULL;
1147
1148 IF (l_order_line_id IS NOT NULL) THEN
1149 BEGIN
1150 SELECT SALESREP_ID
1151 INTO l_salesrep_id
1152 FROM OE_SALES_CREDITS
1153 WHERE LINE_ID = L_ORDER_LINE_ID
1154 AND SALESREP_ID = (
1155 SELECT MIN(SALESREP_ID)
1156 FROM OE_SALES_CREDITS SC ,
1157 OE_SALES_CREDIT_TYPES SCT
1158 WHERE SC.LINE_ID = L_ORDER_LINE_ID
1159 AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
1160 AND SCT.QUOTA_FLAG = 'Y'
1161 AND SC.PERCENT = (
1162 SELECT MAX(PERCENT)
1163 FROM OE_SALES_CREDITS SC, --Bug4096083 start
1164 OE_SALES_CREDIT_TYPES SCT
1165 WHERE SC.LINE_ID = L_ORDER_LINE_ID
1166 AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
1167 AND SCT.QUOTA_FLAG = 'Y' --Bug4096083 end
1168 )
1169 )
1170 AND ROWNUM = 1;
1171
1172 EXCEPTION
1173 WHEN NO_DATA_FOUND THEN
1174 IF l_debug_level > 0 THEN
1175 oe_debug_pub.add('Sales rep not found at line level',2);
1176 END IF;
1177 BEGIN
1178 SELECT SALESREP_ID
1179 INTO l_salesrep_id
1180 FROM OE_SALES_CREDITS
1181 WHERE HEADER_ID = l_header_id
1182 AND LINE_ID IS NULL
1183 AND SALESREP_ID = (
1184 SELECT MIN(SALESREP_ID)
1185 FROM OE_SALES_CREDITS SC ,
1186 OE_SALES_CREDIT_TYPES SCT
1187 WHERE SC.HEADER_ID = L_HEADER_ID
1188 AND SC.LINE_ID IS NULL
1189 AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
1190 AND SCT.QUOTA_FLAG = 'Y'
1191 AND SC.PERCENT = (
1192 SELECT MAX(PERCENT)
1193 FROM OE_SALES_CREDITS SC, --Bug4096083 start
1194 OE_SALES_CREDIT_TYPES SCT
1195 WHERE SC.HEADER_ID = L_HEADER_ID
1196 AND SC.LINE_ID IS NULL
1197 AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
1198 AND SCT.QUOTA_FLAG = 'Y' --Bug4096083 end
1199 )
1200 )
1201 AND ROWNUM = 1;
1202
1203 EXCEPTION
1204
1205 WHEN NO_DATA_FOUND THEN
1206 FND_MESSAGE.SET_NAME('ONT','OE_COGS_SALESREP_NOT_FOUND');
1207 FND_MESSAGE.SET_TOKEN('LINEID',l_order_line_id);
1208 fb_error_msg := FND_MESSAGE.GET_ENCODED;
1209 FND_MESSAGE.SET_ENCODED(fb_error_msg);
1210 l_error_msg := FND_MESSAGE.GET;
1211 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
1212 result := 'COMPLETE:FAILURE';
1213 RETURN;
1214 END;
1215 END;
1216 wf_engine.setItemAttrNumber(itemtype,itemkey,'SALESREP_ID',TO_NUMBER(l_salesrep_id));
1217 result := 'COMPLETE:SUCCESS';
1218 ELSE
1219 result := 'COMPLETE:FAILURE';
1220 RETURN;
1221 END IF;
1222 IF l_debug_level > 0 THEN
1223 oe_debug_pub.add('Output : ',2);
1224 oe_debug_pub.add('Salesrep ID :'|| l_salesrep_id,2);
1225 END IF;
1226 RETURN;
1227 ELSIF (funcmode = 'CANCEL') THEN
1228 result := wf_engine.eng_completed;
1229 RETURN;
1230 ELSE
1231 result := '';
1232 RETURN;
1233 END IF;
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 wf_core.context('OE_FLEX_COGS_PUB','GET_SALESREP_ID',
1237 itemtype,itemkey,TO_CHAR(actid),funcmode);
1238 result := 'COMPLETE:FAILURE';
1239 -- start data fix project
1240 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1241 p_itemtype => itemtype,
1242 p_itemkey => itemkey);
1243 OE_STANDARD_WF.Save_Messages;
1244 OE_STANDARD_WF.Clear_Msg_Context;
1245 -- end data fix project
1246 RAISE;
1247 END Get_Salesrep_Id;
1248
1249 /*===========================================================================+
1250 | Name: Get_invitm_Org_derived
1251 | Purpose: Derives a cost of sales account for an inventory Item ID |
1252 | and Selling Operating Unit
1253 +===========================================================================*/
1254
1255 PROCEDURE Get_Invitm_Org_Derived
1256 (
1257 itemtype IN VARCHAR2,
1258 itemkey IN VARCHAR2,
1259 actid IN NUMBER,
1260 funcmode IN VARCHAR2,
1261 result OUT NOCOPY VARCHAR2)
1262 IS
1263 l_account_derived VARCHAR2(240) DEFAULT NULL;
1264 l_inv_item_id NUMBER;
1265 l_ship_from_org_id NUMBER;
1266 fb_error_msg VARCHAR2(240) DEFAULT NULL;
1267 l_error_msg VARCHAR2(240) DEFAULT NULL;
1268
1269 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1270 BEGIN
1271 -- start data fix project
1272 OE_STANDARD_WF.Set_Msg_Context(actid);
1273 -- end data fix project
1274 IF l_debug_level > 0 THEN
1275 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.Get_Invitm_Org_Derived',1);
1276 END IF;
1277 IF (FUNCMODE = 'RUN') THEN
1278 l_inv_item_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'INVENTORY_ITEM_ID');
1279 l_ship_from_org_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORGANIZATION_ID');
1280 IF l_debug_level > 0 THEN
1281 oe_debug_pub.add('Input Paramerers : ',2);
1282 oe_debug_pub.add('Inventory Item ID :'|| to_char(l_inv_item_id),2);
1283 oe_debug_pub.add('Organization ID :'|| to_char(l_ship_from_org_id),2);
1284 END IF;
1285 L_ACCOUNT_DERIVED := NULL;
1286 IF (L_INV_ITEM_ID IS NOT NULL) THEN
1287 BEGIN
1288 SELECT NVL(COST_OF_SALES_ACCOUNT, 0)
1289 INTO l_account_derived
1290 FROM MTL_SYSTEM_ITEMS
1291 WHERE INVENTORY_ITEM_ID = l_inv_item_id
1292 AND ORGANIZATION_ID = l_ship_from_org_id;
1293 EXCEPTION
1294 WHEN NO_DATA_FOUND THEN
1295 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
1296 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
1297 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
1298 FND_MESSAGE.SET_TOKEN('VALUE1',l_inv_item_id);
1299 FND_MESSAGE.SET_TOKEN('VALUE2',l_ship_from_org_id);
1300 fb_error_msg := FND_MESSAGE.GET_ENCODED;
1301 FND_MESSAGE.SET_ENCODED(fb_error_msg);
1302 l_error_msg := FND_MESSAGE.GET;
1303 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
1304 result := 'COMPLETE:FAILURE';
1305 RETURN;
1306 END;
1307
1308 IF l_account_derived = 0 THEN
1309
1310 FND_MESSAGE.SET_NAME('ONT','OE_COGS_CCID_GEN_FAILED');
1311 FND_MESSAGE.SET_TOKEN('PARAM1','Inventory Item id');
1312 FND_MESSAGE.SET_TOKEN('PARAM2','/Warehouse ');
1313 FND_MESSAGE.SET_TOKEN('VALUE1',l_inv_item_id);
1314 FND_MESSAGE.SET_TOKEN('VALUE2',l_ship_from_org_id);
1315
1316 fb_error_msg := FND_MESSAGE.GET_ENCODED;
1317 FND_MESSAGE.SET_ENCODED(fb_error_msg);
1318 l_error_msg := FND_MESSAGE.GET;
1319
1320 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
1321 result := 'COMPLETE:FAILURE';
1322 RETURN;
1323
1324 END IF;
1325 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(l_account_derived));
1326 result := 'COMPLETE:SUCCESS';
1327 ELSE
1328 result := 'COMPLETE:FAILURE';
1329 RETURN;
1330 END IF;
1331 IF l_debug_level > 0 THEN
1332 oe_debug_pub.add('Output : ',2);
1333 oe_debug_pub.add('Generated CCID :'|| l_account_derived,2);
1334 END IF;
1335 RETURN;
1336 ELSIF (funcmode = 'CANCEL') THEN
1337 result := wf_engine.eng_completed;
1338 RETURN;
1339 ELSE
1340 result := '';
1341 RETURN;
1342 END IF;
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 wf_core.context('OE_FLEX_COGS_PUB','Get_Invitm_Org_Derived',
1346 itemtype,itemkey,TO_CHAR(actid),funcmode);
1347 result := 'COMPLETE:FAILURE';
1348 -- start data fix project
1349 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1350 p_itemtype => itemtype,
1351 p_itemkey => itemkey);
1352 OE_STANDARD_WF.Save_Messages;
1353 OE_STANDARD_WF.Clear_Msg_Context;
1354 -- end data fix project
1355 RAISE;
1356
1357 END Get_Invitm_Org_Derived;
1358
1359 PROCEDURE Check_Option
1360 (
1361 itemtype IN VARCHAR2,
1362 itemkey IN VARCHAR2,
1363 actid IN NUMBER,
1364 funcmode IN VARCHAR2,
1365 result OUT NOCOPY VARCHAR2)
1366
1367 IS
1368
1369 l_option_flag VARCHAR2(1);
1370 fb_error_msg VARCHAR2(240) DEFAULT NULL;
1371 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1372 BEGIN
1373 -- start data fix project
1374 OE_STANDARD_WF.Set_Msg_Context(actid);
1375 -- end data fix project
1376 IF l_debug_level > 0 THEN
1377 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.Check_Option',1);
1378 END IF;
1379 IF (FUNCMODE = 'RUN') THEN
1380 l_option_flag := wf_engine.GetItemAttrText(itemtype,itemkey,'OPTION_FLAG');
1381 IF l_debug_level > 0 THEN
1382 oe_debug_pub.add('Option Flag :'|| l_option_flag,2);
1383 END IF;
1384 IF l_option_flag = 'Y' THEN
1385
1386 result := 'COMPLETE:TRUE';
1387 ELSE
1388 result := 'COMPLETE:FALSE';
1389
1390 END IF;
1391
1392 RETURN;
1393
1394 ELSIF (funcmode = 'CANCEL') THEN
1395 result := wf_engine.eng_completed;
1396 RETURN;
1397 ELSE
1398 result := '';
1399 RETURN;
1400 END IF;
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 wf_core.context('OE_FLEX_COGS_PUB','Check_Option',
1404 itemtype,itemkey,TO_CHAR(actid),funcmode);
1405 result := 'COMPLETE:FAILURE';
1406 -- start data fix project
1407 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1408 p_itemtype => itemtype,
1409 p_itemkey => itemkey);
1410 OE_STANDARD_WF.Save_Messages;
1411 OE_STANDARD_WF.Clear_Msg_Context;
1412 -- end data fix project
1413 RAISE;
1414 END Check_Option;
1415
1416 /*===========================================================================+
1417 | Name: GET_TRX_TYPE |
1418 | Purpose: Derives the transaction type for a commitment ID |
1419 +===========================================================================*/
1420
1421 PROCEDURE Get_Trx_Type
1422 (
1423 itemtype IN VARCHAR2,
1424 itemkey IN VARCHAR2,
1425 actid IN NUMBER,
1426 funcmode IN VARCHAR2,
1427 result OUT NOCOPY VARCHAR2)
1428 IS
1429 l_trx_type VARCHAR2(240) DEFAULT NULL;
1430 l_commitment_id NUMBER;
1431 fb_error_msg VARCHAR2(240) DEFAULT NULL;
1432 l_error_msg VARCHAR2(240) DEFAULT NULL;
1433 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1434 BEGIN
1435 -- start data fix project
1436 OE_STANDARD_WF.Set_Msg_Context(actid);
1437 -- end data fix project
1438 IF l_debug_level > 0 THEN
1439 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.GET_TRX_TYPE',2);
1440 END IF;
1441 IF (FUNCMODE = 'RUN') THEN
1442 l_commitment_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'COMMITMENT_ID');
1443 IF l_debug_level > 0 THEN
1444 oe_debug_pub.add('Input Paramerers : ',2);
1445 oe_debug_pub.add('Commitment ID :'|| to_char(l_commitment_id),2);
1446 END IF;
1447 l_trx_type := NULL;
1448
1449 IF (l_commitment_id IS NOT NULL) THEN
1450 BEGIN
1451 SELECT /* MOAC_SQL_CHANGE */ TYPE.TYPE
1452 INTO l_trx_type
1453 FROM RA_CUSTOMER_TRX TRX, RA_CUST_TRX_TYPES_ALL TYPE
1454 WHERE TRX.CUSTOMER_TRX_ID = L_COMMITMENT_ID
1455 AND TRX.ORG_ID = TYPE.ORG_ID
1456 AND TRX.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID;
1457 EXCEPTION
1458 WHEN NO_DATA_FOUND THEN
1459 FND_MESSAGE.SET_NAME('ONT','OE_COGS_TRX_TYPE_NOT_FOUND');
1460 FND_MESSAGE.SET_TOKEN('COMMITMENTID',l_commitment_id);
1461 fb_error_msg := FND_MESSAGE.GET_ENCODED;
1462 FND_MESSAGE.SET_ENCODED(fb_error_msg);
1463 l_error_msg := FND_MESSAGE.GET;
1464 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',l_error_msg);
1465 result := 'COMPLETE:FAILURE';
1466 RETURN;
1467 END;
1468 wf_engine.setItemAttrText(itemtype,itemkey,'TRX_TYPE_DERIVED',l_trx_type);
1469 result := 'COMPLETE:SUCCESS';
1470 ELSE
1471 result := 'COMPLETE:FAILURE';
1472 RETURN;
1473 END IF;
1474 IF l_debug_level > 0 THEN
1475 oe_debug_pub.add('Output : ',2);
1476 oe_debug_pub.add('Transaction Type'||l_trx_type,2);
1477 END IF;
1478 RETURN;
1479 ELSIF (funcmode = 'CANCEL') THEN
1480 result := wf_engine.eng_completed;
1481 RETURN;
1482 ELSE
1483 result := '';
1484 RETURN;
1485 END IF;
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 wf_core.context('OE_FLEX_COGS_PUB','GET_TRX_TYPE',
1489 itemtype,itemkey,TO_CHAR(actid),funcmode);
1490 result := 'COMPLETE:FAILURE';
1491 -- start data fix project
1492 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1493 p_itemtype => itemtype,
1494 p_itemkey => itemkey);
1495 OE_STANDARD_WF.Save_Messages;
1496 OE_STANDARD_WF.Clear_Msg_Context;
1497 -- end data fix project
1498 RAISE;
1499 END Get_Trx_Type;
1500
1501 /*===========================================================================+
1502 | Name: BUILD |
1503 | Purpose: This is a stub build function that returns a value FALSE and |
1504 | sets the value of the output varriable FB_FLEX_SEGto NULL and |
1505 | output error message variable FB_ERROR_MSG to the AOL error |
1506 | message FLEXWK-UPGRADE FUNC MISSING. This will ensure that the |
1507 | user will get an appropriate error message if they try to use |
1508 | the FLEXBUILDER_UPGRADE process without creating the conversion |
1509 | package successfully. |
1510 +===========================================================================*/
1511
1512 FUNCTION Build (
1513 fb_flex_num IN NUMBER DEFAULT 101,
1514 oe_ii_commitment_id_RAW IN VARCHAR2 DEFAULT NULL,
1515 oe_ii_customer_id_raw IN VARCHAR2 DEFAULT NULL,
1516 oe_ii_header_id_raw IN VARCHAR2 DEFAULT NULL,
1517 oe_ii_option_flag_raw IN VARCHAR2 DEFAULT NULL,
1518 oe_ii_order_category_raw IN VARCHAR2 DEFAULT NULL,
1519 oe_ii_order_line_id_raw IN VARCHAR2 DEFAULT NULL,
1520 oe_ii_order_type_id_raw IN VARCHAR2 DEFAULT NULL,
1521 oe_ii_organization_id_raw IN VARCHAR2 DEFAULT NULL,
1522 fb_flex_seg IN OUT NOCOPY VARCHAR2,
1523 fb_error_msg IN OUT NOCOPY VARCHAR2)
1524 RETURN BOOLEAN
1525 IS
1526 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1527 BEGIN
1528 IF l_debug_level > 0 THEN
1529 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.BUILD',2);
1530 END IF;
1531 fb_flex_seg := NULL;
1532
1533 FND_MESSAGE.SET_NAME('FND', 'FLEXWK-UPGRADE FUNC MISSING');
1534 FND_MESSAGE.SET_TOKEN('FUNC','OE_INVENTORY_INTERFACE');
1535 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
1536 RETURN FALSE;
1537 END Build;
1538
1539
1540 /*===========================================================================+
1541 | Name: UPGRADE_COGS_FLEX |
1542 | Purpose: Determines whether an item is an option item or not |
1543 +===========================================================================*/
1544
1545 PROCEDURE Upgrade_Cogs_Flex
1546 (
1547 itemtype IN VARCHAR2,
1548 itemkey IN VARCHAR2,
1549 actid IN NUMBER,
1550 funcmode IN VARCHAR2,
1551 result OUT NOCOPY VARCHAR2)
1552
1553 IS
1554
1555 l_order_line_id NUMBER;
1556 l_organization_id NUMBER;
1557 l_commitment_id NUMBER;
1558 l_customer_id NUMBER;
1559 l_header_id NUMBER;
1560 l_order_category VARCHAR2(30);
1561 l_order_type_id NUMBER;
1562 l_option_flag VARCHAR2(2);
1563 l_flex_num NUMBER;
1564 l_fb_flex_segs VARCHAR2(240) DEFAULT NULL;
1565 l_fb_error_msg VARCHAR2(240) DEFAULT NULL;
1566 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1567 BEGIN
1568 -- start data fix project
1569 OE_STANDARD_WF.Set_Msg_Context(actid);
1570 -- end data fix project
1571 IF l_debug_level > 0 THEN
1572 oe_debug_pub.add('Entering OE_FLEX_COGS_PUB.BUILD',1);
1573 END IF;
1574 IF (FUNCMODE = 'RUN') THEN
1575
1576 l_order_line_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_ID');
1577 l_organization_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORGANIZATION_ID');
1578 l_commitment_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'COMMITMENT_ID');
1579 l_customer_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'CUSTOMER_ID');
1580 l_header_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'HEADER_ID');
1581 l_order_category:= wf_engine.GetItemAttrText(itemtype,itemkey,'ORDER_CATEGORY');
1582 l_order_type_id:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_TYPE_ID');
1583 l_option_flag:= wf_engine.GetItemAttrText(itemtype,itemkey,'OPTION_FLAG');
1584 l_flex_num:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'CHART_OF_ACCOUNTS_ID');
1585
1586 IF l_debug_level > 0 THEN
1587 oe_debug_pub.add('Input Paramerers : ',2);
1588 oe_debug_pub.add('Line id :'||to_char(l_order_line_id),2);
1589 oe_debug_pub.add('Organization id :'||to_char(l_organization_id),2);
1590 oe_debug_pub.add('Commitment ID :'||to_char(l_commitment_id),2);
1591 oe_debug_pub.add('Customer ID :'||to_char(l_customer_id),2);
1592 oe_debug_pub.add('Order Category :'||l_order_category,2);
1593 oe_debug_pub.add('Order Type :'||to_char(l_order_type_id),2);
1594 oe_debug_pub.add('Option Flag :'||l_option_flag,2);
1595 oe_debug_pub.add('Structure Number :'|| to_char(l_flex_num),2);
1596 END IF;
1597 IF (OE_FLEX_COGS_PUB.Build(
1598 l_flex_num,
1599 l_commitment_id,
1600 l_customer_id,
1601 l_header_id,
1602 l_option_flag,
1603 l_order_category,
1604 l_order_line_id,
1605 l_order_type_id,
1606 l_organization_id,
1607 l_fb_flex_segs,
1608 l_fb_error_msg)=TRUE) THEN
1609
1610 result := 'COMPLETE:SUCCESS';
1611 ELSE
1612 result := 'COMPLETE:FAILURE';
1613 END IF;
1614
1615 IF L_FB_ERROR_MSG IS NOT NULL THEN
1616 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',L_FB_ERROR_MSG);
1617 END IF;
1618
1619 FND_FLEX_WORKFLOW.LOAD_CONCATENATED_SEGMENTS(itemtype,
1620 itemkey,
1621 l_fb_flex_segs);
1622 RETURN;
1623
1624 ELSIF (funcmode = 'CANCEL') THEN
1625
1626 result := wf_engine.eng_completed;
1627 RETURN;
1628 ELSE
1629 result := '';
1630 RETURN;
1631 END IF;
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 wf_core.context('OE_FLEX_COGS_PUB','UPGRADE_COGS_FLEX',
1635 itemtype,itemkey,TO_CHAR(actid),funcmode);
1636 -- start data fix project
1637 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1638 p_itemtype => itemtype,
1639 p_itemkey => itemkey);
1640 OE_STANDARD_WF.Save_Messages;
1641 OE_STANDARD_WF.Clear_Msg_Context;
1642 -- end data fix project
1643 RAISE;
1644 END Upgrade_Cogs_Flex;
1645
1646 END OE_Flex_Cogs_Pub;