[Home] [Help]
PACKAGE BODY: APPS.INV_WORKFLOW
Source
1 PACKAGE BODY inv_workflow AS
2 /* $Header: INVFBWFB.pls 120.3.12000000.1 2007/01/17 16:14:15 appldev ship $ */
3
4 --
5 -- Global Variables
6 --
7 g_version_printed BOOLEAN := FALSE;
8 g_pkg_name CONSTANT VARCHAR2(50) := 'INV_WORKFLOW';
9
10 PROCEDURE print_debug(p_message IN VARCHAR2, p_module IN VARCHAR2) IS
11 BEGIN
12 IF NOT g_version_printed THEN
13 inv_log_util.TRACE('$Header: INVFBWFB.pls 120.3.12000000.1 2007/01/17 16:14:15 appldev ship $', g_pkg_name, 9);
14 g_version_printed := TRUE;
15 END IF;
16
17 inv_log_util.TRACE(p_message, g_pkg_name || '.' || p_module);
18 END;
19
20 FUNCTION call_generate_cogs(
21 c_fb_flex_num IN NUMBER DEFAULT 101
22 , c_ic_customer_id IN NUMBER DEFAULT NULL
23 , c_ic_item_id IN NUMBER DEFAULT NULL
24 , c_ic_order_header_id IN NUMBER DEFAULT NULL
25 , c_ic_order_line_id IN NUMBER DEFAULT NULL
26 , c_ic_order_type_id IN NUMBER DEFAULT NULL
27 , c_ic_sell_oper_unit IN NUMBER DEFAULT NULL
28 , c_v_ccid IN OUT NOCOPY NUMBER
29 , c_fb_flex_seg IN OUT NOCOPY VARCHAR2
30 , c_fb_error_msg IN OUT NOCOPY VARCHAR2
31 , c_ic_to_inv_organization_id IN NUMBER DEFAULT NULL -- Bug: 4474976.
32 )
33 RETURN BOOLEAN IS
34 l_success BOOLEAN := TRUE;
35 BEGIN
36 l_success :=
37 inv_workflow.generate_cogs(
38 fb_flex_num => c_fb_flex_num
39 , ic_customer_id => TO_CHAR(c_ic_customer_id)
40 , ic_item_id => TO_CHAR(c_ic_item_id)
41 , ic_order_header_id => TO_CHAR(c_ic_order_header_id)
42 , ic_order_line_id => TO_CHAR(c_ic_order_line_id)
43 , ic_order_type_id => TO_CHAR(c_ic_order_type_id)
44 , ic_sell_oper_unit => TO_CHAR(c_ic_sell_oper_unit)
45 , v_ccid => c_v_ccid
46 , fb_flex_seg => c_fb_flex_seg
47 , fb_error_msg => c_fb_error_msg
48 , ic_to_inv_organization_id => TO_CHAR(c_ic_to_inv_organization_id) -- Bug: 4474976.
49 );
50 RETURN l_success;
51 END call_generate_cogs;
52
53 FUNCTION generate_cogs(
54 fb_flex_num IN NUMBER DEFAULT 101
55 , ic_customer_id IN VARCHAR2 DEFAULT NULL
56 , ic_item_id IN VARCHAR2 DEFAULT NULL
57 , ic_order_header_id IN VARCHAR2 DEFAULT NULL
58 , ic_order_line_id IN VARCHAR2 DEFAULT NULL
59 , ic_order_type_id IN VARCHAR2 DEFAULT NULL
60 , ic_sell_oper_unit IN VARCHAR2 DEFAULT NULL
61 , v_ccid IN OUT NOCOPY NUMBER
62 , fb_flex_seg IN OUT NOCOPY VARCHAR2
63 , fb_error_msg IN OUT NOCOPY VARCHAR2
64 , ic_to_inv_organization_id IN NUMBER DEFAULT NULL -- Bug: 4474976.
65 )
66 RETURN BOOLEAN IS
67 v_item_cogs NUMBER := NULL;
68 v_organization_cogs NUMBER := NULL;
69 v_order_type_cogs NUMBER;
70 v_concat_ids VARCHAR2(2000);
71 v_concat_segs VARCHAR2(2000);
72 v_concat_descrs VARCHAR2(2000);
73 error_message VARCHAR2(500);
74 v_generate_success BOOLEAN := TRUE;
75 v_itemkey VARCHAR2(100);
76 ic_items_cogs NUMBER := NULL;
77 ic_organization_cogs NUMBER := NULL;
78 ic_order_type_cogs NUMBER := NULL;
79 translated_mesg VARCHAR2(1500) := NULL;
80 v_buffer VARCHAR2(1000) := NULL;
81 l_err_pt VARCHAR2(10) := NULL;
82 v_doc_type_id NUMBER := NULL;
83 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
84 l_function_name VARCHAR2(20) := 'GENERATE_COGS';
85 BEGIN
86 l_err_pt := '10';
87
88 -- fnd_flex_workflow.debug_on; -- To print the FND Account Generator debug messages.
89
90 IF (l_debug = 1) THEN
91 print_debug('FB_FLEX_NUM: ' || TO_CHAR(fb_flex_num), l_function_name);
92 print_debug('IC_CUSTOMER_ID: ' || ic_customer_id, l_function_name);
93 print_debug('IC_ITEM_ID: ' || ic_item_id, l_function_name);
94 print_debug('IC_ORDER_HEADER_ID: ' || ic_order_header_id, l_function_name);
95 print_debug('IC_ORDER_LINE_ID: ' || ic_order_line_id, l_function_name);
96 print_debug('IC_ORDER_TYPE_ID: ' || ic_order_type_id, l_function_name);
97 print_debug('IC_SELL_OPER_UNIT: ' || ic_sell_oper_unit, l_function_name);
98 print_debug('IC_TO_INV_ORGANIZATION_ID: ' || ic_to_inv_organization_id, l_function_name);
99 print_debug('V_CCID: ' || TO_CHAR(v_ccid), l_function_name);
100 print_debug('FB_FLEX_SEG: ' || fb_flex_seg, l_function_name);
101 print_debug('FB_ERROR_MSG: ' || fb_error_msg, l_function_name);
102 print_debug('Calling Workflow Initialize', l_function_name);
103 END IF;
104
105 wf_item.clearcache;
106 v_itemkey :=
107 fnd_flex_workflow.initialize(appl_short_name => 'SQLGL', code => 'GL#', num => fb_flex_num, itemtype => 'INVFLXWF');
108 print_debug('Created itemkey :' || v_itemkey, l_function_name);
109
110 IF (ic_items_cogs IS NULL AND (ic_item_id IS NOT NULL AND ic_to_inv_organization_id IS NOT NULL)) THEN
111 BEGIN
112 -- Bug: 4474976
113 -- Replaced the local variable ic_sell_oper_unit with ic_to_inv_organization_id
114 -- in the WHERE clause of the following SELECT Statment.
115 -- Removed NVL function from the SELECT statement.
116 SELECT cost_of_sales_account
117 INTO v_item_cogs
118 FROM mtl_system_items
119 WHERE inventory_item_id = ic_item_id
120 AND organization_id = ic_to_inv_organization_id;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 NULL;
124 /*
125 FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
126 FND_MESSAGE.SET_TOKEN('VALUE', IC_ITEM_ID);
127 FND_MESSAGE.SET_TOKEN('VSET_ID', '103099');
128 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
129 return FALSE; */
130 END;
131 END IF;
132
133 IF (l_debug = 1) THEN
134 print_debug('IC_ITEMS_COGS: ' || v_item_cogs, l_function_name);
135 END IF;
136
137 l_err_pt := '10.5';
138
139 /* Bug3393516 : When the profile Tax: Invoice Freight as Revenue = 'Yes', then there will be atleast 2 lines in
140 ra_customer_trx_lines_all (After Autoinoice Import) and the order line_id corresponding to the freight line will be 0.
141 As a result the following code (without the if statement) fails and INCIAP ends up giving a warning */
142 IF (ic_order_line_id <> 0) THEN
143 BEGIN
144 SELECT source_document_type_id
145 INTO v_doc_type_id
146 FROM oe_order_lines_all
147 WHERE line_id = TO_NUMBER(ic_order_line_id);
148 EXCEPTION
149 WHEN NO_DATA_FOUND THEN
150 fnd_message.set_name('FND', 'FLEX-BUILD INVALID VALUE');
151 fnd_message.set_token('VALUE', ic_order_line_id);
152 fnd_message.set_token('VSET_ID', '000000');
153 fb_error_msg := fnd_message.get_encoded;
154 RETURN FALSE;
155 END;
156 END IF;
157
158 IF (l_debug = 1) THEN
159 print_debug('Found v_doc_type_id: ' || v_doc_type_id, l_function_name);
160 END IF;
161
162 l_err_pt := '20';
163
164 /* Derive Source parameter IC_ORDER_TYPE_COGS */
165 /* If source document type ID = 10, the order is from an internal requisition.
166 Use receiving organization's purchasing accrual account instead of order type
167 COGS. */
168 IF (v_doc_type_id = 10) THEN
169 BEGIN
170 SELECT mp.ap_accrual_account
171 INTO v_order_type_cogs
172 FROM mtl_parameters mp, po_requisition_lines_all prl, oe_order_lines_all ool
173 WHERE ool.line_id = TO_NUMBER(ic_order_line_id)
174 AND prl.requisition_line_id = ool.source_document_line_id
175 AND mp.organization_id = prl.destination_organization_id;
176
177 print_debug('Found v_acrual_acct: ' || v_order_type_cogs, l_function_name);
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 fnd_message.set_name('FND', 'FLEX-BUILD INVALID VALUE');
181 fnd_message.set_token('VALUE', ic_order_type_id);
182 fnd_message.set_token('VSET_ID', '103100');
183 fb_error_msg := fnd_message.get_encoded;
184 RETURN FALSE;
185 END;
186 ELSE
187 l_err_pt := '20.5';
188
189 IF (ic_order_type_cogs IS NULL) THEN
190 BEGIN
191 SELECT NVL(cost_of_goods_sold_account, 0)
192 INTO v_order_type_cogs
193 FROM oe_transaction_types_all
194 WHERE transaction_type_id = TO_NUMBER(ic_order_type_id)
195 AND 1 = 1;
196
197 print_debug('Found v_order_type_cogs: ' || v_order_type_cogs, l_function_name);
198 EXCEPTION
199 WHEN NO_DATA_FOUND THEN
200 fnd_message.set_name('FND', 'FLEX-BUILD INVALID VALUE');
201 fnd_message.set_token('VALUE', ic_order_type_id);
202 fnd_message.set_token('VSET_ID', '103100');
203 fb_error_msg := fnd_message.get_encoded;
204 RETURN FALSE;
205 END;
206 END IF;
207 END IF;
208
209 l_err_pt := '30';
210
211 IF (ic_organization_cogs IS NULL AND ic_to_inv_organization_id IS NOT NULL) THEN
212 BEGIN
213 -- Bug: 4474976
214 -- Replaced the local variable ic_sell_oper_unit with ic_to_inv_organization_id
215 -- in the WHERE clause of the following SQL Statment.
216 -- Removed NVL function from the SELECT statement.
217 SELECT cost_of_sales_account
218 INTO v_organization_cogs
219 FROM mtl_parameters
220 WHERE organization_id = ic_to_inv_organization_id;
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 NULL;
224 /* FND_MESSAGE.SET_NAME('FND', 'FLEX-BUILD INVALID VALUE');
225 FND_MESSAGE.SET_TOKEN('VALUE', IC_SELL_OPER_UNIT);
226 FND_MESSAGE.SET_TOKEN('VSET_ID', '103101');
227 FB_ERROR_MSG := FND_MESSAGE.GET_ENCODED;
228 return FALSE;*/
229 END;
230 END IF;
231
232 IF (l_debug = 1) THEN
233 print_debug('V_ORGANIZATION_COGS: ' || TO_CHAR(v_organization_cogs), l_function_name);
234 END IF;
235
236 l_err_pt := '40';
237
238 /*
239 +-------------------------------------------------------
240 | Now set atributes for all raw and derived parameters |
241 +-------------------------------------------------------+*/
242 IF (l_debug = 1) THEN
243 print_debug('Initilizing Workflow Item Attributes', l_function_name);
244 END IF;
245
246 IF (ic_customer_id IS NOT NULL) THEN
247 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_CUSTOMER_ID', avalue => ic_customer_id);
248 END IF;
249
250 l_err_pt := '41';
251
252 IF (ic_item_id IS NOT NULL) THEN
253 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ITEM_ID', avalue => ic_item_id);
254 END IF;
255
256 l_err_pt := '42';
257
258 IF (ic_order_header_id IS NOT NULL) THEN
259 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ORDER_HEADER_ID', avalue => ic_order_header_id);
260 END IF;
261
262 l_err_pt := '43';
263
264 IF (ic_order_line_id IS NOT NULL) THEN
265 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ORDER_LINE_ID', avalue => ic_order_line_id);
266 END IF;
267
268 l_err_pt := '44';
269
270 IF (ic_order_type_id IS NOT NULL) THEN
271 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ORDER_TYPE_ID', avalue => ic_order_type_id);
272 END IF;
273
274 l_err_pt := '45';
275
276 IF (ic_sell_oper_unit IS NOT NULL) THEN
277 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_SELL_OPER_UNIT', avalue => ic_sell_oper_unit);
278 END IF;
279
280 l_err_pt := '46';
281
282 IF (v_item_cogs IS NOT NULL) THEN
283 wf_engine.setitemattrnumber(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ITEMS_COGS', avalue => v_item_cogs);
284 END IF;
285
286 l_err_pt := '47';
287
288 IF (v_organization_cogs IS NOT NULL) THEN
289 wf_engine.setitemattrnumber(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ORGANIZATION_COGS', avalue => v_organization_cogs);
290 END IF;
291
292 l_err_pt := '48';
293
294 IF (v_order_type_cogs IS NOT NULL) THEN
295 wf_engine.setitemattrnumber(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'IC_ORDER_TYPE_COGS', avalue => v_order_type_cogs);
296 END IF;
297
298 l_err_pt := '49';
299
300 IF (fb_flex_num IS NOT NULL) THEN
301 wf_engine.setitemattrnumber(itemtype => 'INVFLXWF', itemkey => v_itemkey, aname => 'CHART_OF_ACCOUNTS_ID', avalue => fb_flex_num);
302 END IF;
303
304 l_err_pt := '50';
305 /*+--------------------------------------------------+
306 | Now call the generate function which will kickoff |
307 | the workflow. |
308 +----------------------------------------------------*/
309 v_ccid := v_order_type_cogs;
310
311 IF (l_debug = 1) THEN
312 print_debug('Calling FND_ELEX_WORKFLOW.GENERATE with Parameters:', l_function_name);
313 print_debug('Itemtype:' || ' INVFLXWF', l_function_name);
314 print_debug('itemkey: ' || v_itemkey, l_function_name);
315 print_debug('ccid: ' || TO_CHAR(v_ccid), l_function_name);
316 END IF;
317
318 v_generate_success :=
319 fnd_flex_workflow.generate(
320 itemtype => 'INVFLXWF'
321 , itemkey => v_itemkey
322 , ccid => v_ccid
323 , concat_segs => v_concat_segs
324 , concat_ids => v_concat_ids
325 , concat_descrs => v_concat_descrs
326 , error_message => fb_error_msg
327 );
328 print_debug('FND_ELEX_WORKFLOW.GENERATE returned ccid' || TO_CHAR(v_ccid), l_function_name);
329 print_debug('FND_ELEX_WORKFLOW.GENERATE returned v_concat_segs' || v_concat_segs, l_function_name);
330
331 IF (v_generate_success) THEN
332 fb_flex_seg := v_concat_segs;
333
334 IF (v_ccid = -1) THEN
335 l_err_pt := '51';
336 v_ccid :=
337 fnd_flex_ext.get_ccid(
338 application_short_name => 'SQLGL'
339 , key_flex_code => 'GL#'
340 , structure_number => fb_flex_num
341 , validation_date => TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')
342 , concatenated_segments => v_concat_segs
343 );
344 print_debug('fnd_flex_ext.get_ccid returned' || TO_CHAR(v_ccid), l_function_name);
345
346 IF (v_ccid = 0) THEN
347 fb_error_msg := SUBSTR(fnd_message.get_encoded, 1, 240);
348 print_debug('fnd_flex_ext.get_ccid returned no ccid.', l_function_name);
349 RETURN FALSE;
350 END IF;
351 END IF;
352
353 RETURN TRUE;
354 ELSE
355 l_err_pt := '52';
356 fb_flex_seg := v_concat_segs;
357 fb_error_msg := SUBSTR(fb_error_msg, 1, 240);
358 print_debug('FND_FLEX_WORKFLOW.GENERATE returned FALSE with error: ' || fb_error_msg, l_function_name);
359 RETURN FALSE;
360 END IF;
361 EXCEPTION
362 WHEN OTHERS THEN
363 l_err_pt := '53';
364 print_debug('Unexpected error at l_err_pt: ' || l_err_pt, l_function_name);
365 fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
366 fnd_message.set_token('ENTITY1', l_function_name);
367 v_buffer := TO_CHAR(SQLCODE) || ' ' || SUBSTR(SQLERRM, 1, 150);
368 fnd_message.set_token('ENTITY2', v_buffer);
369 fb_error_msg := SUBSTR(fnd_message.get_encoded, 1, 240);
370 print_debug('Error message: ' || fb_error_msg, l_function_name);
371 RETURN FALSE;
372 END generate_cogs;
373
374 PROCEDURE invoke_build(itemtype IN VARCHAR2, itemkey IN VARCHAR2, actid IN NUMBER, funcmode IN VARCHAR2, RESULT OUT NOCOPY VARCHAR2) IS
375 build_success BOOLEAN := TRUE;
376 p_fb_flex_num NUMBER DEFAULT 101;
377 p_ic_customer_id VARCHAR2(100) DEFAULT NULL;
378 p_ic_item_id VARCHAR2(100) DEFAULT NULL;
379 p_ic_order_header_id VARCHAR2(100) DEFAULT NULL;
380 p_ic_order_line_id VARCHAR2(100) DEFAULT NULL;
381 p_ic_order_type_id VARCHAR2(100) DEFAULT NULL;
382 p_ic_sell_oper_unit VARCHAR2(100) DEFAULT NULL;
383 p_fb_flex_seg VARCHAR2(2000);
384 p_fb_error_msg VARCHAR2(2000);
385 p_ic_organization_cogs NUMBER;
386 p_ic_items_cogs NUMBER;
387 p_ic_order_type_cogs NUMBER;
388 p_ic_structure_id NUMBER;
389 BEGIN
390 IF (funcmode = 'RUN') THEN
391 -- call flexbuilder build here
392 -- then load segments and etc
393
394 -- now copy attributes to local variables to pass to build function
395 p_ic_customer_id := wf_engine.getitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_CUSTOMER_ID');
396 p_ic_item_id := wf_engine.getitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ITEM_ID');
397 p_ic_order_header_id := wf_engine.getitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ORDER_HEADER_ID');
398 p_ic_order_line_id := wf_engine.getitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ORDER_LINE_ID');
399 p_ic_order_type_id := wf_engine.getitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ORDER_TYPE_ID');
400 p_ic_sell_oper_unit := wf_engine.getitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_SELL_OPER_UNIT');
401 p_ic_items_cogs := wf_engine.getitemattrnumber(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ITEMS_COGS');
402 p_ic_organization_cogs := wf_engine.getitemattrnumber(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ORGANIZATION_COGS');
403 p_ic_order_type_cogs := wf_engine.getitemattrnumber(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'IC_ORDER_TYPE_COGS');
404 p_ic_structure_id := wf_engine.getitemattrnumber(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'CHART_OF_ACCOUNTS_ID');
405 build_success :=
406 inv_intercompany_cogs.BUILD(
407 fb_flex_num => p_ic_structure_id
408 , ic_customer_id => p_ic_customer_id
409 , ic_item_id => p_ic_item_id
410 , ic_order_header_id => p_ic_order_header_id
411 , ic_order_line_id => p_ic_order_line_id
412 , ic_order_type_id => p_ic_order_type_id
413 , ic_sell_oper_unit => p_ic_sell_oper_unit
414 , fb_flex_seg => p_fb_flex_seg
415 , fb_error_msg => p_fb_error_msg
416 );
417 -- Now load segment s into workflow attrubutes
418 fnd_flex_workflow.load_concatenated_segments(itemtype => itemtype, itemkey => itemkey, concat_segs => p_fb_flex_seg);
419
420 IF (NOT build_success) THEN
421 RESULT := 'COMPLETE:FAILURE';
422 wf_engine.setitemattrtext(itemtype => 'INVFLXWF', itemkey => itemkey, aname => 'ERROR_MESSAGE', avalue => p_fb_error_msg);
423 RETURN;
424 ELSE
425 RESULT := 'COMPLETE:SUCCESS';
426 RETURN;
427 END IF;
428 END IF;
429
430 IF (funcmode = 'CANCEL') THEN
431 RESULT := 'COMPLETE';
432 END IF;
433 EXCEPTION
434 WHEN OTHERS THEN
435 wf_core.CONTEXT('INV_WORKFLOW', 'INVOKE_BUILD', itemtype, itemkey, TO_CHAR(actid), funcmode);
436 RAISE;
437 END invoke_build;
438 END inv_workflow;