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