1 PACKAGE BODY WSH_FLEX_PKG_COGS AS
2 /* $Header: WSHWFDFB.pls 115.6 99/07/16 08:24:40 porting ship $ */
3
4 /*
5 * Global Variables
6 */
7
8 DEBUG_COGS BOOLEAN := FALSE;
9
10 --
11 -- PRIVATE PROCEDURES
12 --
13
14 PROCEDURE PRINTLN(p_string IN VARCHAR2) IS
15 BEGIN
16 --dbms_output.enable(1000000);
17 --dbms_output.put_line(p_string);
18 null;
19 END PRINTLN;
20
21 --
22 -- PUBLIC FUNCTIONS
23 --
24
25
26 /*===========================================================================+
27 | Name: START_PROCESS |
28 | Purpose: Runs the Workflow process to create the COGS account |
29 +===========================================================================*/
30
31 FUNCTION START_PROCESS(X_COMMITMENT_ID IN NUMBER,
32 X_CUSTOMER_ID IN NUMBER,
33 X_OPTION_FLAG IN VARCHAR2,
34 X_ORDER_CATEGORY IN VARCHAR2,
35 X_LINE_DETAIL_ID IN NUMBER,
36 X_ORDER_LINE_HEADER_ID IN NUMBER,
37 X_ORDER_LINE_ID IN NUMBER,
38 X_PICKING_LINE_DTL_ID IN NUMBER,
39 X_ORDER_TYPE_ID IN NUMBER,
40 X_ORG_ID IN NUMBER,
41 X_FLEX_NUMBER IN NUMBER,
42 X_RETURN_CCID IN OUT NUMBER,
43 X_CONCAT_SEGS IN OUT VARCHAR2,
44 X_CONCAT_IDS IN OUT VARCHAR2,
45 X_CONCAT_DESCRS IN OUT VARCHAR2,
46 X_ERRMSG IN OUT VARCHAR2)
47 RETURN BOOLEAN IS
48
49
50 L_ITEMTYPE VARCHAR2(30) := 'SHPFLXWF';
51 L_ITEMKEY VARCHAR2(30);
52 L_RESULT BOOLEAN;
53 L_commitment_id NUMBER;
54 L_line_detail_id NUMBER;
55 L_pick_line_detail_id NUMBER;
56 L_organization_id NUMBER;
57 current_org_id NUMBER;
58 new_ccid boolean := TRUE; -- flag that indicates if CCID is new
59 BEGIN <<GEN_CCID>>
60
61 --
62 -- Bug 848715
63 -- Set the Org
64 --
65
66
67 SELECT nvl(org_id, -99)
68 INTO current_org_id
69 FROM so_headers_all
70 WHERE header_id = X_ORDER_LINE_HEADER_ID;
71
72 IF (DEBUG_COGS) THEN
73 PRINTLN('Current Org Id : '||to_char(current_org_id));
74 END IF;
75
76 IF ( current_org_id <> -99 ) THEN
77 FND_CLIENT_INFO.SET_ORG_CONTEXT(current_org_id);
78 END IF;
79
80 IF (DEBUG_COGS) THEN
81 PRINTLN('Calling Initialize from START_PROCESS');
82 END IF;
83 L_ITEMKEY := FND_FLEX_WORKFLOW.INITIALIZE
84 ('SQLGL',
85 'GL#',
86 X_FLEX_NUMBER,
87 'SHPFLXWF'
88 );
89
90 IF (DEBUG_COGS) THEN
91 PRINTLN('End of Initialize.');
92 PRINTLN('L_ITEMTYPE = '||L_ITEMTYPE);
93 PRINTLN('L_ITEMKEY = '||L_ITEMKEY);
94 END IF;
95
96 /* Bug 740007: map four variables to NULL as needed */
97
98 L_commitment_id := X_COMMITMENT_ID;
99 L_line_detail_id := X_LINE_DETAIL_ID;
100 L_pick_line_detail_id := X_PICKING_LINE_DTL_ID;
101 L_organization_id := X_ORG_ID;
102
103 IF L_commitment_id = 0 THEN
104 L_commitment_id := NULL;
105 END IF;
106
107 IF L_line_detail_id = 0 THEN
108 L_line_detail_id := NULL;
109 END IF;
110
111 IF L_pick_line_detail_id = 0 THEN
112 L_pick_line_detail_id := NULL;
113 END IF;
114
115 IF L_organization_id = -1 THEN
116 L_organization_id := NULL;
117 END IF;
118
119 /* Initialize the workflow item attributes */
120 IF (DEBUG_COGS) THEN
121 PRINTLN('Initilizing Workflow Item Attributes');
122 END IF;
123 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
124 itemkey => L_ITEMKEY,
125 aname =>'COMMITMENT_ID',
126 avalue => L_commitment_id);
127
128 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
129 itemkey => L_ITEMKEY,
130 aname =>'CUSTOMER_ID',
131 avalue =>X_CUSTOMER_ID);
132
133 wf_engine.SetItemAttrText(itemtype => L_ITEMTYPE,
134 itemkey => L_ITEMKEY,
135 aname => 'OPTION_FLAG',
136 avalue => X_OPTION_FLAG);
137
138 wf_engine.SetItemAttrText(itemtype => L_ITEMTYPE,
139 itemkey => L_ITEMKEY,
140 aname => 'ORDER_CATEGORY',
141 avalue => X_ORDER_CATEGORY);
142
143 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
144 itemkey => L_ITEMKEY,
145 aname => 'LINE_DETAIL_ID',
146 avalue => L_line_detail_id);
147
148 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
149 itemkey => L_ITEMKEY,
150 aname => 'ORDER_LINE_HEADER_ID',
151 avalue => X_ORDER_LINE_HEADER_ID);
152
153 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
154 itemkey => L_ITEMKEY,
155 aname => 'ORDER_LINE_ID',
156 avalue => X_ORDER_LINE_ID);
157
158 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
159 itemkey => L_ITEMKEY,
160 aname => 'PICKING_LINE_DETAIL_ID',
161 avalue => L_pick_line_detail_id);
162
163 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
164 itemkey => L_ITEMKEY,
165 aname => 'ORDER_TYPE_ID',
166 avalue => X_ORDER_TYPE_ID);
167
168 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
169 itemkey => L_ITEMKEY,
170 aname => 'ORGANIZATION_ID',
171 avalue => L_organization_id);
172
173 wf_engine.SetItemAttrNumber(itemtype => L_ITEMTYPE,
174 itemkey => L_ITEMKEY,
175 aname =>'CHART_OF_ACCOUNTS_ID',
176 avalue =>X_FLEX_NUMBER);
177
178 IF (DEBUG_COGS) THEN
179 PRINTLN('Calling FND_ELEX_WORKFLOW.GENERATE from START_PROCESS');
180 END IF;
181 l_result := FND_FLEX_WORKFLOW.GENERATE( 'SHPFLXWF',
182 L_ITEMKEY,
183 TRUE, -- insert if new
184 X_RETURN_CCID,
185 X_CONCAT_SEGS,
186 X_CONCAT_IDS,
187 X_CONCAT_DESCRS,
188 X_ERRMSG,
189 new_ccid);
190 IF (DEBUG_COGS) THEN
191 PRINTLN('End of generate');
192 END IF;
193 RETURN l_result;
194 EXCEPTION
195 WHEN OTHERS THEN
196 IF (DEBUG_COGS) THEN
197 PRINTLN('ERROR: other excpn of START_PROCESS :'||sqlerrm);
198 END IF;
199 wf_core.context('WSH_FLEX_PKG_COGS','START_PROCESS',X_COMMITMENT_ID,X_CUSTOMER_ID,X_ORDER_TYPE_ID ,X_ORG_ID);
200 raise;
201 END; /* START_PROCESS */
202
203 /*===========================================================================+
204 | Name: GET_COST_SALE_ITEM_DERIVED |
205 | Purpose: Derives the COGS account for a line regardless of the option flag|
206 +===========================================================================*/
207
208 PROCEDURE GET_COST_SALE_ITEM_DERIVED(ITEMTYPE IN VARCHAR2,
209 ITEMKEY IN VARCHAR2,
210 ACTID IN NUMBER,
211 FUNCMODE IN VARCHAR2,
212 RESULT OUT VARCHAR2)
213 IS
214 L_COST_SALE_ITEM_DERIVED VARCHAR2(240) DEFAULT NULL;
215 L_LINE_DETAIL_ID NUMBER;
216 L_ORDER_LINE_ID NUMBER;
217 L_PICK_LINE_DETAIL_ID NUMBER;
218 L_ORGANIZATION_ID NUMBER;
219 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
220 BEGIN <<GET_COST_SALE_ITEM_DERIVED>>
221 IF (DEBUG_COGS) THEN
222 DBMS_OUTPUT.ENABLE(1000000);
223 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_COST_SALE_ITEM_DERIVED');
224 PRINTLN('FUNCMODE = '||FUNCMODE);
225 END IF;
226 IF (FUNCMODE = 'RUN') THEN
227 L_LINE_DETAIL_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_DETAIL_ID');
228 L_ORDER_LINE_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_LINE_ID');
229 L_PICK_LINE_DETAIL_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'PICKING_LINE_DETAIL_ID');
230 L_ORGANIZATION_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORGANIZATION_ID');
231 L_COST_SALE_ITEM_DERIVED := NULL;
232
233 IF (L_LINE_DETAIL_ID IS NOT NULL) THEN
234 IF (DEBUG_COGS) THEN
235 PRINTLN('L_LINE_DETAIL_ID is not null.');
236 END IF;
237 BEGIN
238 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
239 INTO L_COST_SALE_ITEM_DERIVED
240 FROM SO_LINE_DETAILS LD,
241 MTL_SYSTEM_ITEMS M
242 WHERE LD.LINE_DETAIL_ID = L_LINE_DETAIL_ID
243 AND M.ORGANIZATION_ID = LD.WAREHOUSE_ID
244 AND M.INVENTORY_ITEM_ID = LD.INVENTORY_ITEM_ID;
245 IF (DEBUG_COGS) THEN
246 PRINTLN('L_COST_SALE_ITEM_DERIVED = '||L_COST_SALE_ITEM_DERIVED);
247 END IF;
248 EXCEPTION
249 WHEN NO_DATA_FOUND THEN
250 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
251 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_LINE_DETAIL_ID));
252 FND_MESSAGE.SET_TOKEN('VSET_ID', '102319');
253 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
254 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
255 result := 'COMPLETE:FAILURE';
256 RETURN;
257 WHEN OTHERS THEN
258 IF (DEBUG_COGS) THEN
259 PRINTLN('ERROR: other excpn of GET_COST_SALE_ITEM_DERIVED 1 :'||sqlerrm);
260 END IF;
261 result := 'COMPLETE:FAILURE';
262 RAISE;
263
264 END;
265 END IF;
266 IF (L_COST_SALE_ITEM_DERIVED IS NULL) THEN
267 IF (L_PICK_LINE_DETAIL_ID IS NOT NULL) THEN
268 IF (DEBUG_COGS) THEN
269 PRINTLN('L_COST_SALE_ITEM_DERIVED is null and L_PICK_LINE_DETAIL_ID is not null');
270 END IF;
271 BEGIN
272 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
273 INTO L_COST_SALE_ITEM_DERIVED
274 FROM SO_PICKING_LINE_DETAILS PLD,
275 SO_PICKING_LINES PL,
276 MTL_SYSTEM_ITEMS M
277 WHERE PLD.PICKING_LINE_DETAIL_ID = L_PICK_LINE_DETAIL_ID
278 AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID
279 AND M.ORGANIZATION_ID = PLD.WAREHOUSE_ID
280 AND M.INVENTORY_ITEM_ID = PL.INVENTORY_ITEM_ID;
281 IF (DEBUG_COGS) THEN
282 PRINTLN('L_COST_SALE_ITEM_DERIVED = '||L_COST_SALE_ITEM_DERIVED);
283 END IF;
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
287 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_PICK_LINE_DETAIL_ID));
288 FND_MESSAGE.SET_TOKEN('VSET_ID', '102320');
289 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
290 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
291 result := 'COMPLETE:FAILURE';
292 RETURN;
293 WHEN OTHERS THEN
294 IF (DEBUG_COGS) THEN
295 PRINTLN('ERROR: other excpn of GET_COST_SALE_ITEM_DERIVED 2 :'||sqlerrm);
296 END IF;
297 result := 'COMPLETE:FAILURE';
298 RAISE;
299 END;
300 END IF;
301 END IF;
302 IF (L_COST_SALE_ITEM_DERIVED IS NULL) THEN
303 IF (L_ORGANIZATION_ID IS NOT NULL) THEN
304 IF (DEBUG_COGS) THEN
305 PRINTLN('L_COST_SALE_ITEM_DERIVED is null and L_ORGANIZATION_ID is not null');
306 END IF;
307 BEGIN
308 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
309 INTO L_COST_SALE_ITEM_DERIVED
310 FROM SO_LINES L,
311 MTL_SYSTEM_ITEMS M
312 WHERE M.ORGANIZATION_ID = L_ORGANIZATION_ID
313 AND L.LINE_ID = L_ORDER_LINE_ID
314 AND M.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID;
315 IF (DEBUG_COGS) THEN
316 PRINTLN('L_COST_SALE_ITEM_DERIVED = '||L_COST_SALE_ITEM_DERIVED);
317 END IF;
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
321 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_ORDER_LINE_ID));
322 FND_MESSAGE.SET_TOKEN('VSET_ID', '102318');
323 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
324 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
325 result := 'COMPLETE:FAILURE';
326 RETURN;
327 WHEN OTHERS THEN
328 IF (DEBUG_COGS) THEN
329 PRINTLN('ERROR: other excpn of GET_COST_SALE_ITEM_DERIVED 3 :'||sqlerrm);
330 END IF;
331 result := 'COMPLETE:FAILURE';
332 RAISE;
333 END;
334 END IF;
335 END IF;
336 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(L_COST_SALE_ITEM_DERIVED));
337 result := 'COMPLETE:SUCCESS';
338 IF (DEBUG_COGS) THEN
339 PRINTLN('Input Paramerers : ');
340 PRINTLN('Line detail id :'||to_char(l_line_detail_id));
341 PRINTLN('Line id :'||to_char(l_order_line_id));
342 PRINTLN('Picking line detail id :'||to_char(l_pick_line_detail_id));
343 PRINTLN('Organization id :'||to_char(l_organization_id));
344 PRINTLN('Output : ');
345 PRINTLN('Generated CCID :'||l_cost_sale_item_derived);
346 END IF;
347 RETURN;
348 ELSIF (funcmode = 'CANCEL') THEN
349 result := wf_engine.eng_completed;
350 RETURN;
351 ELSE
352 result := '';
353 RETURN;
354 END IF;
355 EXCEPTION
356 WHEN OTHERS THEN
357 IF (DEBUG_COGS) THEN
358 PRINTLN('ERROR: other excpn of GET_COST_SALE_ITEM_DERIVED : '||sqlerrm);
359 END IF;
360 wf_core.context('WSH_FLEX_PKG_COGS','GET_COST_SALE_ITEM_DERIVED',
361 itemtype,itemkey,TO_CHAR(actid),funcmode);
362 result := 'COMPLETE:FAILURE';
363 RAISE;
364 END; /* GET_COST_SALE_ITEM_DERIVED */
365
366 /*===========================================================================+
367 | Name: GET_COST_SALE_MODEL_DERIVED |
368 | Purpose: Derives the COGS account for a model |
369 +===========================================================================*/
370
371 PROCEDURE GET_COST_SALE_MODEL_DERIVED(ITEMTYPE IN VARCHAR2,
372 ITEMKEY IN VARCHAR2,
373 ACTID IN NUMBER,
374 FUNCMODE IN VARCHAR2,
375 RESULT OUT VARCHAR2)
376 IS
377 L_COST_SALE_MODEL_DERIVED VARCHAR2(240) DEFAULT NULL;
378 L_LINE_DETAIL_ID NUMBER;
379 L_ORDER_LINE_ID NUMBER;
380 L_PICK_LINE_DETAIL_ID NUMBER;
381 L_ORGANIZATION_ID NUMBER;
382 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
383 BEGIN <<GET_COST_SALE_MODEL_DERIVED>>
384 IF (DEBUG_COGS) THEN
385 DBMS_OUTPUT.ENABLE(1000000);
386 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_COST_SALE_MODEL_DERIVED');
387 PRINTLN('FUNCMODE = '||FUNCMODE);
388 END IF;
389 IF (FUNCMODE = 'RUN') THEN
390 L_LINE_DETAIL_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'LINE_DETAIL_ID');
391 L_ORDER_LINE_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_LINE_ID');
392 L_PICK_LINE_DETAIL_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'PICKING_LINE_DETAIL_ID');
393 L_ORGANIZATION_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORGANIZATION_ID');
394 L_COST_SALE_MODEL_DERIVED := NULL;
395 IF (L_LINE_DETAIL_ID IS NOT NULL) THEN
396 BEGIN
397 IF (DEBUG_COGS) THEN
398 PRINTLN('L_LINE_DETAIL_ID is not null');
399 END IF;
400 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
401 INTO L_COST_SALE_MODEL_DERIVED
402 FROM SO_LINE_DETAILS LD,
403 MTL_SYSTEM_ITEMS M
404 WHERE LD.LINE_DETAIL_ID = L_LINE_DETAIL_ID
405 AND M.ORGANIZATION_ID = LD.WAREHOUSE_ID
406 AND M.INVENTORY_ITEM_ID = LD.INVENTORY_ITEM_ID;
407 IF (DEBUG_COGS) THEN
408 PRINTLN('L_COST_SALE_MODEL_DERIVED = '||L_COST_SALE_MODEL_DERIVED);
409 END IF;
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
413 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_LINE_DETAIL_ID));
414 FND_MESSAGE.SET_TOKEN('VSET_ID', '102319');
415 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
416 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
417 result := 'COMPLETE:FAILURE';
418 RETURN;
419 WHEN OTHERS THEN
420 IF (DEBUG_COGS) THEN
421 PRINTLN('ERROR: other excpn of GET_COST_SALE_MODEL_DERIVED 1 :'||sqlerrm);
422 END IF;
423 result := 'COMPLETE:FAILURE';
424 RAISE;
425 END;
426 END IF;
427 IF (L_COST_SALE_MODEL_DERIVED IS NULL) THEN
428 IF (L_PICK_LINE_DETAIL_ID IS NOT NULL) THEN
429 IF (DEBUG_COGS) THEN
430 PRINTLN('L_COST_SALE_MODEL_DERIVED is null and L_PICK_LINE_DETAIL_ID is not null');
431 END IF;
432 BEGIN
433 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
434 INTO L_COST_SALE_MODEL_DERIVED
435 FROM SO_PICKING_LINE_DETAILS PLD,
436 SO_PICKING_LINES PL,
437 MTL_SYSTEM_ITEMS M
438 WHERE PLD.PICKING_LINE_DETAIL_ID = L_PICK_LINE_DETAIL_ID
439 AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID
440 AND M.ORGANIZATION_ID = PLD.WAREHOUSE_ID
441 AND M.INVENTORY_ITEM_ID = PL.INVENTORY_ITEM_ID;
442 IF (DEBUG_COGS) THEN
443 PRINTLN('L_COST_SALE_MODEL_DERIVED = '||L_COST_SALE_MODEL_DERIVED);
444 END IF;
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
448 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_PICK_LINE_DETAIL_ID));
449 FND_MESSAGE.SET_TOKEN('VSET_ID', '102320');
450 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
451 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
452 result := 'COMPLETE:FAILURE';
453 RETURN;
454 WHEN OTHERS THEN
455 IF (DEBUG_COGS) THEN
456 PRINTLN('ERROR: other excpn of GET_COST_SALE_MODEL_DERIVED 2 :'||sqlerrm);
457 END IF;
458 result := 'COMPLETE:FAILURE';
459 RAISE;
460 END;
461 END IF;
462 END IF;
463 IF (L_COST_SALE_MODEL_DERIVED IS NULL) THEN
464 IF (L_ORGANIZATION_ID IS NOT NULL) THEN
465 IF (DEBUG_COGS) THEN
466 PRINTLN('L_COST_SALE_ITEM_DERIVED is null and L_ORGANIZATION_ID is not null');
467 END IF;
468 BEGIN
469 SELECT NVL(M.COST_OF_SALES_ACCOUNT,0)
470 INTO L_COST_SALE_MODEL_DERIVED
471 FROM SO_LINES L,
472 MTL_SYSTEM_ITEMS M
473 WHERE M.ORGANIZATION_ID = L_ORGANIZATION_ID
474 AND L.LINE_ID = L_ORDER_LINE_ID
475 AND M.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID;
476 IF (DEBUG_COGS) THEN
477 PRINTLN('L_COST_SALE_MODEL_DERIVED = '||L_COST_SALE_MODEL_DERIVED);
478 END IF;
479 EXCEPTION
480 WHEN NO_DATA_FOUND THEN
481 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
482 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_ORDER_LINE_ID));
483 FND_MESSAGE.SET_TOKEN('VSET_ID', '102318');
484 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
485 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
486 result := 'COMPLETE:FAILURE';
487 RETURN;
488 WHEN OTHERS THEN
489 IF (DEBUG_COGS) THEN
490 PRINTLN('ERROR: other excpn of GET_COST_SALE_MODEL_DERIVED 3 :'||sqlerrm);
491 END IF;
492 result := 'COMPLETE:FAILURE';
493 RAISE;
494 END;
495 END IF;
496 END IF;
497 IF (L_COST_SALE_MODEL_DERIVED IS NULL) THEN
498 IF (L_ORDER_LINE_ID IS NOT NULL) THEN
499 IF (DEBUG_COGS) THEN
500 PRINTLN('L_COST_SALE_ITEM_DERIVED is null and L_ORDER_LINE_ID is not null');
501 END IF;
502 BEGIN
503 SELECT NVL(COST_OF_SALES_ACCOUNT,0)
504 INTO L_COST_SALE_MODEL_DERIVED
505 FROM SO_MODEL_LINE_COGS_ACCOUNT
506 WHERE LINE_ID = L_ORDER_LINE_ID;
507 IF (DEBUG_COGS) THEN
508 PRINTLN('L_COST_SALE_MODEL_DERIVED = '||L_COST_SALE_MODEL_DERIVED);
509 END IF;
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
513 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(L_ORDER_LINE_ID));
514 FND_MESSAGE.SET_TOKEN('VSET_ID', '102321');
515 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
516 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
517 result := 'COMPLETE:FAILURE';
518 RETURN;
519 WHEN OTHERS THEN
520 IF (DEBUG_COGS) THEN
521 PRINTLN('ERROR: other excpn of GET_COST_SALE_MODEL_DERIVED 4 :'||sqlerrm);
522 END IF;
523 result := 'COMPLETE:FAILURE';
524 RAISE;
525 END;
526 END IF;
527 END IF;
528 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(L_COST_SALE_MODEL_DERIVED));
529 result := 'COMPLETE:SUCCESS';
530 IF (DEBUG_COGS) THEN
531 PRINTLN('Input Paramerers : ');
532 PRINTLN('Line detail id :'||to_char(l_line_detail_id));
533 PRINTLN('Line id :'||to_char(l_order_line_id));
534 PRINTLN('Picking line detail id :'||to_char(l_pick_line_detail_id));
535 PRINTLN('Organization id :'||to_char(l_organization_id));
536 PRINTLN('Output : ');
537 PRINTLN('Generated CCID :'||l_cost_sale_model_derived);
538 END IF;
539 RETURN;
540 ELSIF (funcmode = 'CANCEL') THEN
541 result := wf_engine.eng_completed;
542 RETURN;
543 ELSE
544 result := '';
545 RETURN;
546 END IF;
547 EXCEPTION
548 WHEN OTHERS THEN
549 IF (DEBUG_COGS) THEN
550 PRINTLN('ERROR: other excpn of GET_COST_SALE_MODEL_DERIVED : '||sqlerrm);
551 END IF;
552 wf_core.context('WSH_FLEX_PKG_COGS','GET_COST_SALE_MODEL_DERIVED',
553 itemtype,itemkey,TO_CHAR(actid),funcmode);
554 result := 'COMPLETE:FAILURE';
555 RAISE;
556 END; /* GET_COST_SALE_MODEL_DERIVED */
557
558
559 /*===========================================================================+
560 | Name: GET_ORDER_TYPE_DERIVED |
561 | Purpose: Derives the CCID from the Order type |
562 +===========================================================================*/
563
564 PROCEDURE GET_ORDER_TYPE_DERIVED(ITEMTYPE IN VARCHAR2,
565 ITEMKEY IN VARCHAR2,
566 ACTID IN NUMBER,
567 FUNCMODE IN VARCHAR2,
568 RESULT OUT VARCHAR2)
569 IS
570 L_ORDER_TYPE_CCID VARCHAR2(240) DEFAULT NULL;
571 L_ORDER_TYPE_ID NUMBER;
572 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
573 BEGIN <<GET_ORDER_TYPE_DERIVED>>
574 IF (DEBUG_COGS) THEN
575 DBMS_OUTPUT.ENABLE(1000000);
576 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_ORDER_TYPE_DERIVED');
577 PRINTLN('FUNCMODE = '||FUNCMODE);
578 END IF;
579 IF (FUNCMODE = 'RUN') THEN
580 L_ORDER_TYPE_ID := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_TYPE_ID');
581 L_ORDER_TYPE_CCID := NULL;
582 IF (L_ORDER_TYPE_ID IS NOT NULL) THEN
583 IF (DEBUG_COGS) THEN
584 PRINTLN('L_ORDER_TYPE_ID is not null');
585 END IF;
586 BEGIN
587 SELECT NVL(COST_OF_GOODS_SOLD_ACCOUNT, 0)
588 INTO L_ORDER_TYPE_CCID
589 FROM SO_ORDER_TYPES
590 WHERE ORDER_TYPE_ID = L_ORDER_TYPE_ID;
591 IF (DEBUG_COGS) THEN
592 PRINTLN('L_ORDER_TYPE_CCID = '||L_ORDER_TYPE_CCID);
593 END IF;
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
597 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_ORDER_TYPE_ID));
598 FND_MESSAGE.SET_TOKEN('VSET_ID', '101643');
599 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
600 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
601 result := 'COMPLETE:FAILURE';
602 RETURN;
603 WHEN OTHERS THEN
604 IF (DEBUG_COGS) THEN
605 PRINTLN('ERROR: other excpn of GET_ORDER_TYPE_DERIVED :'||sqlerrm);
606 END IF;
607 result := 'COMPLETE:FAILURE';
608 RAISE;
609 END;
610 END IF;
611 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(L_ORDER_TYPE_CCID));
612 result := 'COMPLETE:SUCCESS';
613 IF (DEBUG_COGS) THEN
614 PRINTLN('Input Paramerers : ');
615 PRINTLN('Order Type ID :'||to_char(l_order_type_id));
616 PRINTLN('Output : ');
617 PRINTLN('Generated CCID :'||l_order_type_ccid);
618 END IF;
619 RETURN;
620 ELSIF (funcmode = 'CANCEL') THEN
621 result := wf_engine.eng_completed;
622 RETURN;
623 ELSE
624 result := '';
625 RETURN;
626 END IF;
627 EXCEPTION
628 WHEN OTHERS THEN
629 IF (DEBUG_COGS) THEN
630 PRINTLN('ERROR: other excpn of GET_ORDER_TYPE_DERIVED : '||sqlerrm);
631 END IF;
632 wf_core.context('WSH_FLEX_PKG_COGS','GET_ORDER_TYPE_DERIVED',
633 itemtype,itemkey,TO_CHAR(actid),funcmode);
634 result := 'COMPLETE:FAILURE';
635 RAISE;
636 END; /* GET_ORDER_TYPE_DERIVED */
637
638 /*===========================================================================+
639 | Name: GET_SALESREP_REV_DERIVED |
640 | Purpose: Derives the CCID from salesrep's revenue segment |
641 +===========================================================================*/
642
643 PROCEDURE GET_SALESREP_REV_DERIVED(ITEMTYPE IN VARCHAR2,
644 ITEMKEY IN VARCHAR2,
645 ACTID IN NUMBER,
646 FUNCMODE IN VARCHAR2,
647 RESULT OUT VARCHAR2)
648 IS
649 L_SALESREP_REV_DERIVED VARCHAR2(240) DEFAULT NULL;
650 L_SALESREP_ID NUMBER;
651 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
652 BEGIN <<GET_SALESREP_REV_DERIVED>>
653 IF (DEBUG_COGS) THEN
654 DBMS_OUTPUT.ENABLE(1000000);
655 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_SALESREP_REV_DERIVED');
656 PRINTLN('FUNCMODE = '||FUNCMODE);
657 END IF;
658 IF (FUNCMODE = 'RUN') THEN
659 L_SALESREP_ID := wf_engine.GetActivityAttrNumber(itemtype,itemkey,actid,'SALESREPID');
660 L_SALESREP_REV_DERIVED := NULL;
661 IF (L_SALESREP_ID IS NOT NULL) THEN
662 IF (DEBUG_COGS) THEN
663 PRINTLN('L_SALESREP_ID is not null');
664 END IF;
665 BEGIN
666 SELECT NVL(GL_ID_REV, 0)
667 INTO L_SALESREP_REV_DERIVED
668 FROM RA_SALESREPS
669 WHERE SALESREP_ID = L_SALESREP_ID;
670 IF (DEBUG_COGS) THEN
671 PRINTLN('L_SALESREP_REV_DERIVED = '||L_SALESREP_REV_DERIVED);
672 END IF;
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
676 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_SALESREP_ID));
677 FND_MESSAGE.SET_TOKEN('VSET_ID', '101645');
678 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
679 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
680 result := 'COMPLETE:FAILURE';
681 RETURN;
682 WHEN OTHERS THEN
683 IF (DEBUG_COGS) THEN
684 PRINTLN('ERROR: other excpn of GET_SALESREP_REV_DERIVED :'||sqlerrm);
685 END IF;
686 result := 'COMPLETE:FAILURE';
687 RAISE;
688 END;
689 END IF;
690 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(L_SALESREP_REV_DERIVED));
691 result := 'COMPLETE:SUCCESS';
692 IF (DEBUG_COGS) THEN
693 PRINTLN('Input Paramerers : ');
694 PRINTLN('Salesrep ID :' || to_char(l_salesrep_id));
695 PRINTLN('Output : ');
696 PRINTLN('Generated CCID :'||l_salesrep_rev_derived);
697 END IF;
698 RETURN;
699 ELSIF (funcmode = 'CANCEL') THEN
700 result := wf_engine.eng_completed;
701 RETURN;
702 ELSE
703 result := '';
704 RETURN;
705 END IF;
706 EXCEPTION
707 WHEN OTHERS THEN
708 IF (DEBUG_COGS) THEN
709 PRINTLN('ERROR: other excpn of GET_SALESREP_REV_DERIVED : '||sqlerrm);
710 END IF;
711 wf_core.context('WSH_FLEX_PKG_COGS','GET_SALESREP_REV_DERIVED',
712 itemtype,itemkey,TO_CHAR(actid),funcmode);
713 result := 'COMPLETE:FAILURE';
714 RAISE;
715 END; /* GET_SALESREP_REV_DERIVED */
716
717 /*===========================================================================+
718 | Name: GET_SALESREP_ID |
719 | Purpose: Derives the salesrep's ID |
720 +===========================================================================*/
721
722 PROCEDURE GET_SALESREP_ID(ITEMTYPE IN VARCHAR2,
723 ITEMKEY IN VARCHAR2,
724 ACTID IN NUMBER,
725 FUNCMODE IN VARCHAR2,
726 RESULT OUT VARCHAR2)
727 IS
728 L_SALESREP_ID VARCHAR2(240) DEFAULT NULL;
729 L_ORDER_LINE_ID NUMBER;
730 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
731 BEGIN <<GET_SALESREP_ID>>
732 IF (DEBUG_COGS) THEN
733 DBMS_OUTPUT.ENABLE(1000000);
734 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_SALESREP_ID');
735 PRINTLN('FUNCMODE = '||FUNCMODE);
736 END IF;
737 IF (FUNCMODE = 'RUN') THEN
738 L_ORDER_LINE_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_LINE_ID');
739 IF (DEBUG_COGS) THEN
740 PRINTLN('Input Paramerers : ');
741 PRINTLN('Order Limne ID :'|| to_char(l_order_line_id));
742 END IF;
743 L_SALESREP_ID := NULL;
744 IF (L_ORDER_LINE_ID IS NOT NULL) THEN
745 IF (DEBUG_COGS) THEN
746 PRINTLN('L_ORDER_LINE_ID is not null');
747 END IF;
748 BEGIN
749 SELECT SALESREP_ID
750 INTO L_SALESREP_ID
751 FROM SO_LINE_SALES_CREDITS
752 WHERE LINE_ID = L_ORDER_LINE_ID
753 AND QUOTA_FLAG = 'Y'
754 AND SALESREP_ID = (
755 SELECT MIN(SALESREP_ID)
756 FROM SO_LINE_SALES_CREDITS C1
757 WHERE C1.LINE_ID = L_ORDER_LINE_ID
758 AND C1.QUOTA_FLAG = 'Y'
759 AND C1.PERCENT = (
760 SELECT MAX(PERCENT)
761 FROM SO_LINE_SALES_CREDITS C2
762 WHERE C2.LINE_ID = L_ORDER_LINE_ID
763 AND C2.QUOTA_FLAG = 'Y'
764 AND C2.LEVEL_ID = (
765 SELECT MAX(LEVEL_ID)
766 FROM SO_LINE_SALES_CREDITS C3
767 WHERE C3.LINE_ID = L_ORDER_LINE_ID
768 AND C3.QUOTA_FLAG = 'Y'
769 )
770 )
771 )
772 AND ROWNUM = 1;
773 IF (DEBUG_COGS) THEN
774 PRINTLN('L_SALESREP_ID = '||L_SALESREP_ID);
775 END IF;
776 EXCEPTION
777 WHEN NO_DATA_FOUND THEN
778 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
779 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_ORDER_LINE_ID));
780 FND_MESSAGE.SET_TOKEN('VSET_ID', '101646');
781 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
782 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
783 result := 'COMPLETE:FAILURE';
784 RETURN;
785 WHEN OTHERS THEN
786 IF (DEBUG_COGS) THEN
787 PRINTLN('ERROR: other excpn of GET_SALESREP_ID :'||sqlerrm);
788 END IF;
789 result := 'COMPLETE:FAILURE';
790 RAISE;
791 END;
792 wf_engine.setItemAttrNumber(itemtype,itemkey,'SALESREP_ID',TO_NUMBER(L_SALESREP_ID));
793 result := 'COMPLETE:SUCCESS';
794 ELSE
795 result := 'COMPLETE:FAILURE';
796 RETURN;
797 END IF;
798 IF (DEBUG_COGS) THEN
799 PRINTLN('Output : ');
800 PRINTLN('Salesrep ID :'|| l_salesrep_id);
801 END IF;
802 RETURN;
803 ELSIF (funcmode = 'CANCEL') THEN
804 result := wf_engine.eng_completed;
805 RETURN;
806 ELSE
807 result := '';
808 RETURN;
809 END IF;
810 EXCEPTION
811 WHEN OTHERS THEN
812 IF (DEBUG_COGS) THEN
813 PRINTLN('ERROR: other excpn of GET_SALESREP_ID : '||sqlerrm);
814 END IF;
815 wf_core.context('WSH_FLEX_PKG_COGS','GET_SALESREP_ID',
816 itemtype,itemkey,TO_CHAR(actid),funcmode);
817 result := 'COMPLETE:FAILURE';
818 RAISE;
819 END; /* GET_SALESREP_ID */
820
821 /*===========================================================================+
822 | Name: GET_COST_SALE |
823 | Purpose: Derives a cost of sales account for an inventory Item ID |
824 | and Organization ID |
825 +===========================================================================*/
826
827 PROCEDURE GET_COST_SALE(ITEMTYPE IN VARCHAR2,
828 ITEMKEY IN VARCHAR2,
829 ACTID IN NUMBER,
830 FUNCMODE IN VARCHAR2,
831 RESULT OUT VARCHAR2)
832 IS
833 L_ACCOUNT_DERIVED VARCHAR2(240) DEFAULT NULL;
834 L_INV_ITEM_ID NUMBER;
835 L_ORGANIZATION_ID NUMBER;
836 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
837 BEGIN <<GET_COST_SALE>>
838 IF (DEBUG_COGS) THEN
839 DBMS_OUTPUT.ENABLE(1000000);
840 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_COST_SALE');
841 PRINTLN('FUNCMODE = '||FUNCMODE);
842 END IF;
843 IF (FUNCMODE = 'RUN') THEN
844 L_INV_ITEM_ID := wf_engine.GetActivityAttrNumber(itemtype,itemkey,actid,'INVITEMID');
845 L_ORGANIZATION_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORGANIZATION_ID');
846 IF (DEBUG_COGS) THEN
847 PRINTLN('Input Paramerers : ');
848 PRINTLN('Inventory Item ID :'|| to_char(l_inv_item_id));
849 PRINTLN('Organization ID :'|| to_char(l_organization_id));
850 END IF;
851 L_ACCOUNT_DERIVED := NULL;
852 IF (L_INV_ITEM_ID IS NOT NULL) THEN
853 IF (DEBUG_COGS) THEN
854 PRINTLN('L_INV_ITEM_ID is not null');
855 END IF;
856 BEGIN
857 SELECT NVL(COST_OF_SALES_ACCOUNT, 0)
858 INTO L_ACCOUNT_DERIVED
859 FROM MTL_SYSTEM_ITEMS
860 WHERE INVENTORY_ITEM_ID = L_INV_ITEM_ID
861 AND ORGANIZATION_ID = L_ORGANIZATION_ID;
862 IF (DEBUG_COGS) THEN
863 PRINTLN('L_ACCOUNT_DERIVED = '||L_ACCOUNT_DERIVED);
864 END IF;
865 EXCEPTION
866 WHEN NO_DATA_FOUND THEN
867 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
868 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_INV_ITEM_ID));
869 FND_MESSAGE.SET_TOKEN('VSET_ID', '101640');
870 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
871 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
872 result := 'COMPLETE:FAILURE';
873 RETURN;
874 WHEN OTHERS THEN
875 IF (DEBUG_COGS) THEN
876 PRINTLN('ERROR: other excpn of GET_COST_SALE :'||sqlerrm);
877 END IF;
878 result := 'COMPLETE:FAILURE';
879 RAISE;
880 END;
881 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',TO_NUMBER(L_ACCOUNT_DERIVED));
882 result := 'COMPLETE:SUCCESS';
883 ELSE
884 result := 'COMPLETE:FAILURE';
885 RETURN;
886 END IF;
887 IF (DEBUG_COGS) THEN
888 PRINTLN('Output : ');
889 PRINTLN('Generated CCID :'|| l_account_derived);
890 END IF;
891 RETURN;
892 ELSIF (funcmode = 'CANCEL') THEN
893 result := wf_engine.eng_completed;
894 RETURN;
895 ELSE
896 result := '';
897 RETURN;
898 END IF;
899 EXCEPTION
900 WHEN OTHERS THEN
901 IF (DEBUG_COGS) THEN
902 PRINTLN('ERROR: other excpn of GET_COST_SALE : '||sqlerrm);
903 END IF;
904 wf_core.context('WSH_FLEX_PKG_COGS','GET_SALESREP_REV_DERIVED',
905 itemtype,itemkey,TO_CHAR(actid),funcmode);
906 result := 'COMPLETE:FAILURE';
907 RAISE;
908 END; /* GET_COST_SALE */
909
910 /*===========================================================================+
911 | Name: GET_INV_ITEM_ID |
912 | Purpose: Derives Inventory Item ID from Order Line ID |
913 +===========================================================================*/
914
915 PROCEDURE GET_INV_ITEM_ID(ITEMTYPE IN VARCHAR2,
916 ITEMKEY IN VARCHAR2,
917 ACTID IN NUMBER,
918 FUNCMODE IN VARCHAR2,
919 RESULT OUT VARCHAR2)
920 IS
921 L_INV_ITEM_ID VARCHAR2(240) DEFAULT NULL;
922 L_ORDER_LINE_ID NUMBER;
923 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
924 BEGIN <<GET_INV_ITEM_ID>>
925 IF (DEBUG_COGS) THEN
926 DBMS_OUTPUT.ENABLE(1000000);
927 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_INV_ITEM_ID');
928 PRINTLN('FUNCMODE = '||FUNCMODE);
929 END IF;
930 IF (FUNCMODE = 'RUN') THEN
931 L_ORDER_LINE_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_LINE_ID');
932 L_INV_ITEM_ID := NULL;
933 IF (L_ORDER_LINE_ID IS NOT NULL) THEN
934 IF (DEBUG_COGS) THEN
935 PRINTLN('L_ACCOUNT_DERIVED is not null');
936 END IF;
937 BEGIN
938 SELECT INVENTORY_ITEM_ID
939 INTO L_INV_ITEM_ID
940 FROM SO_LINES
941 WHERE LINE_ID = L_ORDER_LINE_ID;
942 IF (DEBUG_COGS) THEN
943 PRINTLN('L_INV_ITEM_ID = '||L_INV_ITEM_ID);
944 END IF;
945 EXCEPTION
946 WHEN NO_DATA_FOUND THEN
947 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
948 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_ORDER_LINE_ID));
949 FND_MESSAGE.SET_TOKEN('VSET_ID', '101641');
950 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
951 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
952 result := 'COMPLETE:FAILURE';
953 RETURN;
954 WHEN OTHERS THEN
955 IF (DEBUG_COGS) THEN
956 PRINTLN('ERROR: other excpn of GET_INV_ITEM_ID :'||sqlerrm);
957 END IF;
958 result := 'COMPLETE:FAILURE';
959 RAISE;
960 END;
961 wf_engine.setItemAttrNumber(itemtype,itemkey,'INV_ITEM_ID',TO_NUMBER(L_INV_ITEM_ID));
962 result := 'COMPLETE:SUCCESS';
963 ELSE
964 result := 'COMPLETE:FAILURE';
965 RETURN;
966 END IF;
967 IF (DEBUG_COGS) THEN
968 PRINTLN('Input Paramerers : ');
969 PRINTLN('Line ID :'|| to_char(l_order_line_id));
970 PRINTLN('Output : ');
974 ELSIF (funcmode = 'CANCEL') THEN
971 PRINTLN('Inventory Item ID :'||l_inv_item_id);
972 END IF;
973 RETURN;
975 result := wf_engine.eng_completed;
976 RETURN;
977 ELSE
978 result := '';
979 RETURN;
980 END IF;
981 EXCEPTION
982 WHEN OTHERS THEN
983 IF (DEBUG_COGS) THEN
984 PRINTLN('ERROR: other excpn of GET_INV_ITEM_ID : '||sqlerrm);
985 END IF;
986 wf_core.context('WSH_FLEX_PKG_COGS','GET_INV_ITEM_ID',
987 itemtype,itemkey,TO_CHAR(actid),funcmode);
988 result := 'COMPLETE:FAILURE';
989 RAISE;
990 END; /* GET_INV_ITEM_ID */
991
992 /*===========================================================================+
993 | Name: GET_TRX_TYPE |
994 | Purpose: Derives the transaction type for a commitment ID |
995 +===========================================================================*/
996
997 PROCEDURE GET_TRX_TYPE(ITEMTYPE IN VARCHAR2,
998 ITEMKEY IN VARCHAR2,
999 ACTID IN NUMBER,
1000 FUNCMODE IN VARCHAR2,
1001 RESULT OUT VARCHAR2)
1002 IS
1003 L_TRX_TYPE VARCHAR2(240) DEFAULT NULL;
1004 L_COMMITMENT_ID NUMBER;
1005 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
1006 BEGIN <<GET_TRX_TYPE>>
1007 IF (DEBUG_COGS) THEN
1008 DBMS_OUTPUT.ENABLE(1000000);
1009 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_TRX_TYPE');
1010 END IF;
1011 IF (FUNCMODE = 'RUN') THEN
1012 L_COMMITMENT_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'COMMITMENT_ID');
1013 IF (DEBUG_COGS) THEN
1014 PRINTLN('Input Paramerers : ');
1015 PRINTLN('Commitment ID :'|| to_char(l_commitment_id));
1016 PRINTLN('FUNCMODE = '||FUNCMODE);
1017 END IF;
1018 L_TRX_TYPE:= NULL;
1019 IF (L_COMMITMENT_ID IS NOT NULL) THEN
1020 IF (DEBUG_COGS) THEN
1021 PRINTLN('L_COMMITMENT_ID is not null.');
1022 END IF;
1023 BEGIN
1024 SELECT TYPE.TYPE
1025 INTO L_TRX_TYPE
1026 FROM RA_CUSTOMER_TRX TRX, RA_CUST_TRX_TYPES TYPE
1027 WHERE TRX.CUSTOMER_TRX_ID = L_COMMITMENT_ID
1028 AND TRX.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID;
1029 IF (DEBUG_COGS) THEN
1030 PRINTLN('L_TRX_TYPE = '||L_TRX_TYPE);
1031 END IF;
1032 EXCEPTION
1033 WHEN NO_DATA_FOUND THEN
1034 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
1035 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_COMMITMENT_ID));
1036 FND_MESSAGE.SET_TOKEN('VSET_ID', '101647');
1037 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
1038 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
1039 result := 'COMPLETE:FAILURE';
1040 RETURN;
1041 WHEN OTHERS THEN
1042 IF (DEBUG_COGS) THEN
1043 PRINTLN('ERROR: other excpn of GET_TRX_TYPE :'||sqlerrm);
1044 END IF;
1045 result := 'COMPLETE:FAILURE';
1046 RAISE;
1047 END;
1048 wf_engine.setItemAttrText(itemtype,itemkey,'TRX_TYPE_DERIVED',L_TRX_TYPE);
1049 result := 'COMPLETE:SUCCESS';
1050 ELSE
1051 result := 'COMPLETE:FAILURE';
1052 RETURN;
1053 END IF;
1054 IF (DEBUG_COGS) THEN
1055 PRINTLN('Output : ');
1056 PRINTLN('Transaction Type'||l_trx_type);
1057 END IF;
1058 RETURN;
1059 ELSIF (funcmode = 'CANCEL') THEN
1060 result := wf_engine.eng_completed;
1061 RETURN;
1062 ELSE
1063 result := '';
1064 RETURN;
1065 END IF;
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 IF (DEBUG_COGS) THEN
1069 PRINTLN('ERROR: other excpn of GET_TRX_TYPE : '||sqlerrm);
1070 END IF;
1071 wf_core.context('WSH_FLEX_PKG_COGS','GET_TRX_TYPE',
1072 itemtype,itemkey,TO_CHAR(actid),funcmode);
1073 result := 'COMPLETE:FAILURE';
1074 RAISE;
1075 END; /* GET_TRX_TYPE */
1076
1077 /*===========================================================================+
1078 | Name: GET_OPERATING_UNIT |
1079 | Purpose: Derives the selling operating unit |
1080 +===========================================================================*/
1081
1082 PROCEDURE GET_OPERATING_UNIT(ITEMTYPE IN VARCHAR2,
1083 ITEMKEY IN VARCHAR2,
1084 ACTID IN NUMBER,
1085 FUNCMODE IN VARCHAR2,
1086 RESULT OUT VARCHAR2)
1087 IS
1088 L_OPERATING_UNIT VARCHAR2(240) DEFAULT NULL;
1089 L_ORDER_LINE_ID NUMBER;
1090 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
1091 BEGIN <<GET_OPERATING_UNIT>>
1092 IF (DEBUG_COGS) THEN
1093 DBMS_OUTPUT.ENABLE(1000000);
1094 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_OPERATING_UNIT');
1095 PRINTLN('FUNCMODE = '||FUNCMODE);
1096 END IF;
1097 IF (FUNCMODE = 'RUN') THEN
1098 L_ORDER_LINE_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_LINE_ID');
1099 IF (DEBUG_COGS) THEN
1100 PRINTLN('Input Paramerers : ');
1101 PRINTLN('Line ID :'||to_char(l_order_line_id));
1102 END IF;
1103 L_OPERATING_UNIT := NULL;
1104 IF (L_ORDER_LINE_ID IS NOT NULL) THEN
1105 IF (DEBUG_COGS) THEN
1106 PRINTLN('L_ORDER_LINE_ID is not null.');
1107 END IF;
1108 BEGIN
1109 SELECT ORG_ID
1110 INTO L_OPERATING_UNIT
1114 PRINTLN('L_OPERATING_UNIT = '||L_OPERATING_UNIT);
1111 FROM SO_LINES
1112 WHERE LINE_ID = L_ORDER_LINE_ID;
1113 IF (DEBUG_COGS) THEN
1115 END IF;
1116 EXCEPTION
1117 WHEN NO_DATA_FOUND THEN
1118 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
1119 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_ORDER_LINE_ID));
1120 FND_MESSAGE.SET_TOKEN('VSET_ID','103098');
1121 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
1122 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
1123 result := 'COMPLETE:FAILURE';
1124 RETURN;
1125 WHEN OTHERS THEN
1126 IF (DEBUG_COGS) THEN
1127 PRINTLN('ERROR: other excpn of GET_OPERATING_UNIT :'||sqlerrm);
1128 END IF;
1129 result := 'COMPLETE:FAILURE';
1130 RAISE;
1131 END;
1132 wf_engine.setItemAttrNumber(itemtype,itemkey,'ORG_ID',TO_NUMBER(L_OPERATING_UNIT));
1133 result := 'COMPLETE:SUCCESS';
1134 ELSE
1135 result := 'COMPLETE:FAILURE';
1136 RETURN;
1137 END IF;
1138 IF (DEBUG_COGS) THEN
1139 PRINTLN('Output : ');
1140 PRINTLN('Operating Unit :'||l_operating_unit);
1141 END IF;
1142 RETURN;
1143 ELSIF (funcmode = 'CANCEL') THEN
1144 result := wf_engine.eng_completed;
1145 RETURN;
1146 ELSE
1147 result := '';
1148 RETURN;
1149 END IF;
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 IF (DEBUG_COGS) THEN
1153 PRINTLN('ERROR: other excpn of GET_OPERATING_UNIT : '||sqlerrm);
1154 END IF;
1155 wf_core.context('WSH_FLEX_PKG_COGS','GET_OPERATING_UNIT',
1156 itemtype,itemkey,TO_CHAR(actid),funcmode);
1157 result := 'COMPLETE:FAILURE';
1158 RAISE;
1159 END; /* GET_OPERATING_UNIT */
1160
1161 /*===========================================================================+
1162 | Name: GET_PARENT_LINE |
1163 | Purpose: Derives a parent line id for a order line id |
1164 +===========================================================================*/
1165
1166 PROCEDURE GET_PARENT_LINE(ITEMTYPE IN VARCHAR2,
1167 ITEMKEY IN VARCHAR2,
1168 ACTID IN NUMBER,
1169 FUNCMODE IN VARCHAR2,
1170 RESULT OUT VARCHAR2)
1171 IS
1172 L_PARENT_LINE_ID VARCHAR2(240) DEFAULT NULL;
1173 L_ORDER_LINE_ID NUMBER;
1174 FB_ERROR_MSG VARCHAR2(240) DEFAULT NULL;
1175 BEGIN <<GET_PARENT_LINE>>
1176 IF (DEBUG_COGS) THEN
1177 DBMS_OUTPUT.ENABLE(1000000);
1178 PRINTLN('Calling WSH_FLEX_PKG_COGS.GET_PARENT_LINE');
1179 PRINTLN('FUNCMODE = '||FUNCMODE);
1180 END IF;
1181 IF (FUNCMODE = 'RUN') THEN
1182 L_ORDER_LINE_ID:= wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORDER_LINE_ID');
1183 L_PARENT_LINE_ID := NULL;
1184 IF (L_ORDER_LINE_ID IS NOT NULL) THEN
1185 IF (DEBUG_COGS) THEN
1186 PRINTLN('L_ORDER_LINE_ID is not null.');
1187 END IF;
1188 BEGIN
1189 SELECT PARENT_LINE_ID
1190 INTO L_PARENT_LINE_ID
1191 FROM SO_LINES
1192 WHERE LINE_ID = L_ORDER_LINE_ID;
1193 IF (DEBUG_COGS) THEN
1194 PRINTLN('L_PARENT_LINE_ID = '||L_PARENT_LINE_ID);
1195 END IF;
1196 EXCEPTION
1197 WHEN NO_DATA_FOUND THEN
1198 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
1199 FND_MESSAGE.SET_TOKEN('VALUE',TO_CHAR(L_ORDER_LINE_ID));
1200 FND_MESSAGE.SET_TOKEN('VSET_ID','101644');
1201 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
1202 wf_engine.setItemAttrText(itemtype,itemkey,'ERROR_MESSAGE',FB_ERROR_MSG);
1203 result := 'COMPLETE:FAILURE';
1204 RETURN;
1205 WHEN OTHERS THEN
1206 IF (DEBUG_COGS) THEN
1207 PRINTLN('ERROR: other excpn of GET_PARENT_LINE :'||sqlerrm);
1208 END IF;
1209 result := 'COMPLETE:FAILURE';
1210 RAISE;
1211 END;
1212 END IF;
1213 wf_engine.setItemAttrNumber(itemtype,itemkey,'PARENT_LINE_ID_DERIVED',L_PARENT_LINE_ID);
1214 result := 'COMPLETE:SUCCESS';
1215 IF (DEBUG_COGS) THEN
1216 PRINTLN('Input Paramerers : ');
1217 PRINTLN('Line ID :'||to_char(l_order_line_id));
1218 PRINTLN('Output : ');
1219 PRINTLN('Parent Line ID :'|| l_parent_line_id);
1220 END IF;
1221 RETURN;
1222 ELSIF (funcmode = 'CANCEL') THEN
1223 result := wf_engine.eng_completed;
1224 RETURN;
1225 ELSE
1226 result := '';
1227 RETURN;
1228 END IF;
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 IF (DEBUG_COGS) THEN
1232 PRINTLN('ERROR: other excpn of GET_PARENT_LINE : '||sqlerrm);
1233 END IF;
1234 wf_core.context('WSH_FLEX_PKG_COGS','GET_PARENT_LINE',
1235 itemtype,itemkey,TO_CHAR(actid),funcmode);
1236 result := 'COMPLETE:FAILURE';
1237 RAISE;
1238 END; /* GET_PARENT_LINE */
1239
1240 END WSH_FLEX_PKG_COGS;
[Home] [Help]