[Home] [Help]
PACKAGE BODY: APPS.DPP_ITEMCOST_PVT
Source
1 PACKAGE BODY DPP_ITEMCOST_PVT AS
2 /* $Header: dppvcstb.pls 120.34 2011/05/21 00:17:45 hekkiral noship $ */
3 -- Package name : DPP_ITEMCOST_PVT
4 -- Purpose :
5 -- History :
6 -- NOTE :
7 -- End of Comments
8
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_ITEMCOST_PVT';
10 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
11 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvcstb.pls';
12 ---------------------------------------------------------------------
13 -- PROCEDURE
14 -- Update_ItemCost
15 --
16 -- PURPOSE
17 -- Update item cost.
18 --
19 -- PARAMETERS
20 --
21 -- NOTES
22 -- 1.
23 -- 2.
24 ----------------------------------------------------------------------
25 function wait_for_rec_processing(in_execution_detail_id IN number default NULL,
26 in_transaction_header_id IN number default NULL,
27 interval IN number default 60,
28 max_wait IN number default 0,
29 message OUT NOCOPY varchar2)
30 return boolean is
31
32 Call_Status boolean;
33 Time_Out boolean := FALSE;
34 pipename varchar2(60);
35 req_phase varchar2(15);
36 STime number(30);
37 ETime number(30);
38 i number;
39 l_interface_pending_count number;
40 begin
41 if ( max_wait > 0 ) then
42 Time_Out := TRUE;
43 Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) + To_Char(Sysdate, 'SSSss'))
44 Into STime From Sys.Dual;
45 end if;
46
47 LOOP
48
49 SELECT
50 count(*)
51 INTO
52 l_interface_pending_count
53 FROM
54 mtl_transactions_interface
55 WHERE
56 source_code = 'Price Protection' AND
57 source_header_id = in_execution_detail_id AND
58 transaction_header_id = in_transaction_header_id AND
59 process_flag = 1;
60
61 IF l_interface_pending_count = 0 THEN
62 call_status := TRUE;
63 return (call_status);
64 end if;
65
66 if ( Time_Out ) then
67 Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) + To_Char(Sysdate, 'SSSss'))
68 Into ETime From Sys.Dual;
69
70 if ( (ETime - STime) >= max_wait ) then
71 call_status := FALSE;
72 return (call_status);
73 end if;
74 end if;
75 dbms_lock.sleep(interval);
76 END LOOP;
77
78 exception
79 when others then
80 Fnd_Message.Set_Name('FND', 'CP-Generic oracle error');
81 Fnd_Message.Set_Token('ERROR', substr(SQLERRM, 1, 80), FALSE);
82 Fnd_Message.Set_Token('ROUTINE','DPP_ITEMCOST_PVT.wait_for_rec_processing', FALSE);
83 FND_MSG_PUB.add;
84 return FALSE;
85 end wait_for_rec_processing;
86
87 PROCEDURE Update_ItemCost
88 (p_api_Version IN NUMBER,
89 p_Init_msg_List IN VARCHAR2 := fnd_api.g_False,
90 p_Commit IN VARCHAR2 := fnd_api.g_False,
91 p_Validation_Level IN NUMBER := fnd_api.g_Valid_Level_Full,
92 x_Return_Status OUT NOCOPY VARCHAR2,
93 x_msg_Count OUT NOCOPY NUMBER,
94 x_msg_Data OUT NOCOPY VARCHAR2,
95 p_txn_hdr_rec IN DPP_CST_HDR_REC_TYPE,
96 p_Item_Cost_Tbl IN DPP_TXN_LINE_TBL_TYPE)
97 IS
98 l_api_name CONSTANT VARCHAR2(30) := 'Update_ItemCost';
99 l_api_version CONSTANT NUMBER := 1.0;
100 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
101
102 l_return_status VARCHAR2(30);
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(4000);
105
106 l_cost_type_id NUMBER := 8; -- seeded cost type for Price Protection
107 l_transaction_type_id NUMBER := 80; -- seeded type for average cost update
108 l_transaction_action_id NUMBER := 24; -- seeded for cost update
109 l_dpp_application_id NUMBER := 9000; -- seeded for dpp
110
111 l_cost_type VARCHAR2(30);
112 l_src_hdr_id NUMBER;
113 l_cost_import_req_id NUMBER;
114 l_cost_upd_req_id NUMBER;
115 l_wait_req BOOLEAN;
116 l_phase VARCHAR2(30);
117 l_status VARCHAR2(30);
118 l_dev_phase VARCHAR2(30);
119 l_dev_status VARCHAR2(30);
120 l_message VARCHAR2(30);
121 l_to_amount NUMBER := 0;
122 l_exchange_rate NUMBER;
123 l_interface_pending_count NUMBER := 0;
124 l_txn_subtype VARCHAR2(240);
125 l_wait_status BOOLEAN;
126 l_import_cost_group_id NUMBER;
127 l_prior_cost NUMBER;
128 l_processed_flag VARCHAR2(1) := 'N';
129 l_transaction_line_id NUMBER;
130 l_transaction_subtype VARCHAR2(20);
131 l_bom_installed NUMBER;
132 l_incorrect_price_exists NUMBER := 0;
133 l_responsibility_id NUMBER;
134 l_execution_status VARCHAR2(20);
135 l_transaction_number VARCHAR2(50);
136 l_sysdate DATE := sysdate;
137 l_trunc_sysdate DATE := trunc(sysdate);
138 l_output_xml CLOB;
139 l_queryCtx dbms_xmlquery.ctxType;
140
141 --- Begin Added for A/c Generator W/f ---
142 l_itemtype VARCHAR2(30) := 'OZFACCTG';
143 l_itemkey VARCHAR2(38);
144 x_return_ccid NUMBER;
145 x_concat_segs VARCHAR2(500);
146 x_concat_ids VARCHAR2(500);
147 x_concat_descrs VARCHAR2(500);
148 l_bg_process_mode VARCHAR2(1);
149 l_cost_adj_ccid NUMBER;
150 l_chart_of_accounts_id NUMBER;
151 l_role_name VARCHAR2(240) := null;
152 l_debug_flag VARCHAR2(30);
153 l_result BOOLEAN;
154 l_errmsg VARCHAR2(2000);
155 l_new_comb BOOLEAN := TRUE;
156 l_user_name VARCHAR2(100);
157 l_correct_item VARCHAR2(1) := 'Y';
158 l_insert_xla_header VARCHAR2(1) := 'N';
159 --- End Added for A/c Generator W/f ---
160
161 l_txn_hdr_rec DPP_ITEMCOST_PVT.dpp_cst_hdr_rec_type := p_txn_hdr_rec;
162 l_item_cost_tbl DPP_ITEMCOST_PVT.dpp_txn_line_tbl_type := p_item_cost_tbl;
163 l_inv_org_details_tbl DPP_ITEMCOST_PVT.inv_org_details_tbl_type;
164
165 l_exe_update_rec DPP_ExecutionDetails_PVT.dpp_exe_update_rec_type;
166 l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
167 l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_ITEMCOST_PVT.UPDATE_ITEMCOST';
168
169 TYPE inventory_item_id_tbl IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE
170 INDEX BY PLS_INTEGER;
171 TYPE error_explanation_tbl IS TABLE OF mtl_transactions_interface.error_explanation%TYPE
172 INDEX BY PLS_INTEGER;
173 TYPE source_line_id_tbl IS TABLE OF mtl_transactions_interface.source_line_id%TYPE
174 INDEX BY PLS_INTEGER;
175 TYPE transaction_id_tbl IS TABLE OF mtl_material_transactions.transaction_id%TYPE
176 INDEX BY PLS_INTEGER;
177 TYPE transaction_line_id_tbl IS TABLE OF dpp_transaction_lines_all.transaction_line_id%TYPE
178 INDEX BY PLS_INTEGER;
179 TYPE transaction_subtype_tbl IS TABLE OF dpp_xla_lines.transaction_sub_type%TYPE
180 INDEX BY PLS_INTEGER;
181
182 inventory_item_ids inventory_item_id_tbl;
183 error_explanations error_explanation_tbl;
184 source_line_ids source_line_id_tbl;
185 transaction_ids transaction_id_tbl;
186 transaction_line_ids transaction_line_id_tbl;
187 transaction_subtypes transaction_subtype_tbl;
188
189 CURSOR Item_cur(p_inventory_item_id IN NUMBER,p_org_id IN NUMBER, p_trunc_sysdate IN DATE)
190 IS
191 SELECT mp.organization_id,msi.primary_uom_code transaction_uom,
192 msi.concatenated_segments item_number, mp.primary_cost_method, cod.organization_name,
193 mp.default_cost_group_id cost_group_id,
194 cod.currency_code
195 FROM mtl_parameters mp,
196 mtl_system_items_kfv msi,
197 cst_organization_definitions cod
198 WHERE mp.organization_id = msi.organization_id
199 AND mp.primary_cost_method IN (1,2)
200 AND msi.inventory_item_id = p_inventory_item_id
201 AND msi.inventory_asset_flag = 'Y'
202 AND cod.organization_id = mp.organization_id
203 AND cod.operating_unit = p_org_id
204 -- AND NVL(mp.consigned_flag,'N') = 'N'
205 AND mp.process_enabled_flag = 'N'
206 AND NVL(cod.disable_date,p_trunc_sysdate + 1) > p_trunc_sysdate;
207
208 CURSOR Organization_cur(p_cost_type_id IN NUMBER,p_request_id IN NUMBER)
209 IS
210 SELECT DISTINCT organization_id from cst_item_costs
211 WHERE cost_type_id = p_cost_type_id
212 AND request_id = p_request_id;
213
214 BEGIN
215 ------------------------------------------
216 -- Initialization
217 ------------------------------------------
218
219 -- Standard call to check for call compatibility.
220 IF NOT FND_API.Compatible_API_Call (l_api_version,
221 p_api_version,
222 l_api_name,
223 g_pkg_name) THEN
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226
227 -- Initialize message list if p_init_msg_list is set to TRUE.
228 IF FND_API.to_Boolean( p_init_msg_list ) THEN
229 FND_MSG_PUB.initialize;
230 END IF;
231
232 -- Debug Message
233
234 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'start at: '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
235
236 -- Initialize API return status to sucess
237 l_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 --
240 -- API body
241 --
242 -- check for mandatory input parameters --
243 IF l_txn_hdr_rec.org_id IS NULL THEN
244 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
245 FND_MESSAGE.set_token('ID', 'Org ID');
246 FND_MSG_PUB.add;
247 RAISE FND_API.G_EXC_ERROR;
248 ELSIF l_txn_hdr_rec.last_updated_by IS NULL THEN
249 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
250 FND_MESSAGE.set_token('ID', 'User ID - Last_Updated_By');
251 FND_MSG_PUB.add;
252 RAISE FND_API.G_EXC_ERROR;
253 ELSIF l_txn_hdr_rec.cost_adjustment_account IS NULL THEN
254 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
255 FND_MESSAGE.set_token('ID', 'Cost Adjustment Account');
256 FND_MSG_PUB.add;
257 RAISE FND_API.G_EXC_ERROR;
258 ELSIF l_txn_hdr_rec.transaction_header_id IS NULL THEN
259 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
260 FND_MESSAGE.set_token('ID', 'Transaction Header ID');
261 FND_MSG_PUB.add;
262 RAISE FND_API.G_EXC_ERROR;
263 ELSIF l_txn_hdr_rec.execution_detail_id IS NULL THEN
264 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
265 FND_MESSAGE.set_token('ID', 'Execution Detail ID');
266 FND_MSG_PUB.add;
267 RAISE FND_API.G_EXC_ERROR;
268 ELSIF l_txn_hdr_rec.transaction_number IS NULL THEN
269 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
270 FND_MESSAGE.set_token('ID', 'Transaction Number');
271 FND_MSG_PUB.add;
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274
275 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After mandatory checks:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
276
277 l_bg_process_mode := nvl(fnd_profile.value('DPP_ACCT_GEN_USE_WORKFLOW'),'N');
278
279 --Get the user name for the last updated by user
280 BEGIN
281 SELECT user_name
282 INTO l_user_name
283 FROM fnd_user
284 WHERE user_id = l_txn_hdr_rec.last_updated_by;
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Invalid User');
288 RAISE FND_API.G_EXC_ERROR;
289 WHEN OTHERS THEN
290 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
291 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
292 fnd_message.set_token('ERRNO', sqlcode);
293 fnd_message.set_token('REASON', sqlerrm);
294 FND_MSG_PUB.add;
295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
296 END;
297
298 --Getting a valid Price Protection Responsibility at the User Level Profile Options
299 BEGIN
300 SELECT frv.responsibility_id
301 INTO l_responsibility_id
302 FROM fnd_profile_options fpo,
303 fnd_profile_option_values fpov,
304 fnd_responsibility_vl frv,
305 fnd_user_resp_groups_direct furgd
306 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
307 AND fpo.profile_option_id = fpov.profile_option_id
308 AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
309 AND fpov.level_id = 10004
310 AND furgd.user_id = fpov.level_value
311 AND frv.application_id = 9000
312 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
313 AND NVL (frv.end_date, TRUNC (SYSDATE))
314 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
315 AND NVL (furgd.end_date, TRUNC (SYSDATE))
316 AND furgd.responsibility_id = frv.responsibility_id
317 AND furgd.responsibility_application_id = frv.application_id
318 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
319 AND ROWNUM = 1;
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 l_responsibility_id := -1;
323 BEGIN
324 SELECT frv.responsibility_id
325 INTO l_responsibility_id
326 FROM fnd_profile_options fpo,
327 fnd_profile_option_values fpov,
328 fnd_responsibility_vl frv,
329 fnd_user_resp_groups_direct furgd,
330 per_security_profiles psp
331 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
332 AND fpo.profile_option_id = fpov.profile_option_id
333 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
334 AND ((psp.view_all_organizations_flag = 'Y'
335 AND psp.business_group_id IS NOT NULL
336 AND EXISTS (SELECT 1
337 FROM hr_operating_units hr
338 WHERE hr.business_group_id = psp.business_group_id
339 AND hr.usable_flag IS NULL
340 AND hr.organization_id =
341 l_txn_hdr_rec.org_id))
342 OR (psp.view_all_organizations_flag = 'Y'
343 AND psp.business_group_id IS NULL)
344 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
345 AND EXISTS (SELECT 1
346 FROM per_organization_list per,
347 hr_operating_units hr
348 WHERE per.security_profile_id = psp.security_profile_id
349 AND hr.organization_id = per.organization_id
350 AND hr.usable_flag IS NULL
351 AND per.organization_id = l_txn_hdr_rec.org_id)))
352 AND fpov.level_id = 10004
353 AND furgd.user_id = fpov.level_value
354 AND frv.application_id = 9000
355 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
356 AND NVL (frv.end_date, TRUNC (SYSDATE))
357 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
358 AND NVL (furgd.end_date, TRUNC (SYSDATE))
359 AND furgd.responsibility_id = frv.responsibility_id
360 AND furgd.responsibility_application_id = frv.application_id
361 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
362 AND ROWNUM = 1;
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN
365 l_responsibility_id := -1;
366 WHEN OTHERS THEN
367 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
368 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
369 fnd_message.set_token('ERRNO', sqlcode);
370 fnd_message.set_token('REASON', sqlerrm);
371 FND_MSG_PUB.add;
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END;
374 WHEN OTHERS THEN
375 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
376 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
377 fnd_message.set_token('ERRNO', sqlcode);
378 fnd_message.set_token('REASON', sqlerrm);
379 FND_MSG_PUB.add;
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END;
382
383 --Getting a valid Price Protection Responsibility at the Responsibility Level Profile Options
384 IF l_responsibility_id = -1 THEN
385 BEGIN
386 SELECT frv.responsibility_id
387 INTO l_responsibility_id
388 FROM fnd_profile_options fpo,
389 fnd_profile_option_values fpov,
390 fnd_responsibility_vl frv,
391 fnd_user_resp_groups_direct furgd
392 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
393 AND fpo.profile_option_id = fpov.profile_option_id
394 AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
395 AND fpov.level_id = 10003
396 AND frv.responsibility_id = fpov.level_value
397 AND frv.application_id = 9000
398 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
399 AND NVL (frv.end_date, TRUNC (SYSDATE))
400 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
401 AND NVL (furgd.end_date, TRUNC (SYSDATE))
402 AND furgd.responsibility_id = frv.responsibility_id
403 AND furgd.responsibility_application_id = frv.application_id
404 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
405 AND ROWNUM = 1;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 l_responsibility_id := -1;
409 BEGIN
410 SELECT frv.responsibility_id
411 INTO l_responsibility_id
412 FROM fnd_profile_options fpo,
413 fnd_profile_option_values fpov,
414 fnd_responsibility_vl frv,
415 fnd_user_resp_groups_direct furgd,
416 per_security_profiles psp
417 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
418 AND fpo.profile_option_id = fpov.profile_option_id
419 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
420 AND ((psp.view_all_organizations_flag = 'Y'
421 AND psp.business_group_id IS NOT NULL
422 AND EXISTS (SELECT 1
423 FROM hr_operating_units hr
424 WHERE hr.business_group_id = psp.business_group_id
425 AND hr.usable_flag IS NULL
426 AND hr.organization_id = l_txn_hdr_rec.org_id))
427 OR (psp.view_all_organizations_flag = 'Y'
428 AND psp.business_group_id IS NULL)
429 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
430 AND EXISTS (SELECT 1
431 FROM per_organization_list per,
432 hr_operating_units hr
433 WHERE per.security_profile_id = psp.security_profile_id
434 AND hr.organization_id = per.organization_id
435 AND hr.usable_flag IS NULL
436 AND per.organization_id = l_txn_hdr_rec.org_id)))
437 AND fpov.level_id = 10003
438 AND frv.responsibility_id = fpov.level_value
439 AND frv.application_id = 9000
440 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
441 AND NVL (frv.end_date, TRUNC (SYSDATE))
442 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
443 AND NVL (furgd.end_date, TRUNC (SYSDATE))
444 AND furgd.responsibility_id = frv.responsibility_id
445 AND furgd.responsibility_application_id = frv.application_id
446 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
447 AND ROWNUM = 1;
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 l_responsibility_id := -1;
451 WHEN OTHERS THEN
452 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
453 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
454 fnd_message.set_token('ERRNO', sqlcode);
455 fnd_message.set_token('REASON', sqlerrm);
456 FND_MSG_PUB.add;
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END;
459 WHEN OTHERS THEN
460 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
461 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
462 fnd_message.set_token('ERRNO', sqlcode);
463 fnd_message.set_token('REASON', sqlerrm);
464 FND_MSG_PUB.add;
465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
466 END;
467 END IF;
468
469 --Getting a valid Price Protection Responsibility at the Application Level Profile Options
470 IF l_responsibility_id = -1 THEN
471 BEGIN
472 SELECT frv.responsibility_id
473 INTO l_responsibility_id
474 FROM fnd_profile_options fpo,
475 fnd_profile_option_values fpov,
476 fnd_responsibility_vl frv,
477 fnd_user_resp_groups_direct furgd
478 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
479 AND fpo.profile_option_id = fpov.profile_option_id
480 AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
481 AND fpov.level_id = 10002
482 AND frv.application_id = fpov.level_value
483 AND frv.application_id = 9000
484 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
485 AND NVL (frv.end_date, TRUNC (SYSDATE))
486 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
487 AND NVL (furgd.end_date, TRUNC (SYSDATE))
488 AND furgd.responsibility_id = frv.responsibility_id
489 AND furgd.responsibility_application_id = frv.application_id
490 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
491 AND ROWNUM = 1;
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 l_responsibility_id := -1;
495 BEGIN
496 SELECT frv.responsibility_id
497 INTO l_responsibility_id
498 FROM fnd_profile_options fpo,
499 fnd_profile_option_values fpov,
500 fnd_responsibility_vl frv,
501 fnd_user_resp_groups_direct furgd,
502 per_security_profiles psp
503 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
504 AND fpo.profile_option_id = fpov.profile_option_id
505 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
506 AND ((psp.view_all_organizations_flag = 'Y'
507 AND psp.business_group_id IS NOT NULL
508 AND EXISTS (SELECT 1
509 FROM hr_operating_units hr
510 WHERE hr.business_group_id = psp.business_group_id
511 AND hr.usable_flag IS NULL
512 AND hr.organization_id = l_txn_hdr_rec.org_id))
513 OR (psp.view_all_organizations_flag = 'Y'
514 AND psp.business_group_id IS NULL)
515 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
516 AND EXISTS (SELECT 1
517 FROM per_organization_list per,
518 hr_operating_units hr
519 WHERE per.security_profile_id = psp.security_profile_id
520 AND hr.organization_id = per.organization_id
521 AND hr.usable_flag IS NULL
522 AND per.organization_id = l_txn_hdr_rec.org_id)))
523 AND fpov.level_id = 10002
524 AND frv.application_id = fpov.level_value
525 AND frv.application_id = 9000
526 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
527 AND NVL (frv.end_date, TRUNC (SYSDATE))
528 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
529 AND NVL (furgd.end_date, TRUNC (SYSDATE))
530 AND furgd.responsibility_id = frv.responsibility_id
531 AND furgd.responsibility_application_id = frv.application_id
532 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
533 AND ROWNUM = 1;
534 EXCEPTION
535 WHEN NO_DATA_FOUND THEN
536 l_responsibility_id := -1;
537 WHEN OTHERS THEN
538 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
539 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
540 fnd_message.set_token('ERRNO', sqlcode);
541 fnd_message.set_token('REASON', sqlerrm);
542 FND_MSG_PUB.add;
543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
544 END;
545 WHEN OTHERS THEN
546 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
547 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
548 fnd_message.set_token('ERRNO', sqlcode);
549 fnd_message.set_token('REASON', sqlerrm);
550 FND_MSG_PUB.add;
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 END;
553 END IF;
554
555 --Getting a valid Price Protection Responsibility at the Site Level Profile Options
556 IF l_responsibility_id = -1 THEN
557 BEGIN
558 SELECT frv.responsibility_id
559 INTO l_responsibility_id
560 FROM fnd_profile_options fpo,
561 fnd_profile_option_values fpov,
562 fnd_responsibility_vl frv,
563 fnd_user_resp_groups_direct furgd
564 WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
565 AND fpo.profile_option_id = fpov.profile_option_id
566 AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
567 AND fpov.level_id = 10001
568 AND fpov.level_value = 0
569 AND frv.application_id = 9000
570 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
571 AND NVL (frv.end_date, TRUNC (SYSDATE))
572 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
573 AND NVL (furgd.end_date, TRUNC (SYSDATE))
574 AND furgd.responsibility_id = frv.responsibility_id
575 AND furgd.responsibility_application_id = frv.application_id
576 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
577 AND ROWNUM = 1;
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 l_responsibility_id := -1;
581 BEGIN
582
583 SELECT frv.responsibility_id
584 INTO l_responsibility_id
585 FROM fnd_profile_options fpo,
586 fnd_profile_option_values fpov,
587 fnd_responsibility_vl frv,
588 fnd_user_resp_groups_direct furgd,
589 per_security_profiles psp
590 WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
591 AND fpo.profile_option_id = fpov.profile_option_id
592 AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
593 AND ((psp.view_all_organizations_flag = 'Y'
594 AND psp.business_group_id IS NOT NULL
595 AND EXISTS (SELECT 1
596 FROM hr_operating_units hr
597 WHERE hr.business_group_id = psp.business_group_id
598 AND hr.usable_flag IS NULL
599 AND hr.organization_id = l_txn_hdr_rec.org_id))
600 OR (psp.view_all_organizations_flag = 'Y'
601 AND psp.business_group_id IS NULL)
602 OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
603 AND EXISTS (SELECT 1
604 FROM per_organization_list per,
605 hr_operating_units hr
606 WHERE per.security_profile_id = psp.security_profile_id
607 AND hr.organization_id = per.organization_id
608 AND hr.usable_flag IS NULL
609 AND per.organization_id = l_txn_hdr_rec.org_id)))
610 AND fpov.level_id = 10001
611 AND fpov.level_value = 0
612 AND frv.application_id = 9000
613 AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
614 AND NVL (frv.end_date, TRUNC (SYSDATE))
615 AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
616 AND NVL (furgd.end_date, TRUNC (SYSDATE))
617 AND furgd.responsibility_id = frv.responsibility_id
618 AND furgd.responsibility_application_id = frv.application_id
619 AND furgd.user_id = l_txn_hdr_rec.last_updated_by
620 AND ROWNUM = 1;
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 l_responsibility_id := -1;
624 WHEN OTHERS THEN
625 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
626 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
627 fnd_message.set_token('ERRNO', sqlcode);
628 fnd_message.set_token('REASON', sqlerrm);
629 FND_MSG_PUB.add;
630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
631 END;
632 WHEN OTHERS THEN
633 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
634 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT');
635 fnd_message.set_token('ERRNO', sqlcode);
636 fnd_message.set_token('REASON', sqlerrm);
637 FND_MSG_PUB.add;
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END;
640 END IF;
641
642 --Check if the responsibility id is -1
643 IF l_responsibility_id = -1 THEN
644 FND_MESSAGE.set_name('DPP', 'DPP_INVALID_RESP');
645 FND_MESSAGE.set_token('USER', l_user_name);
646 FND_MSG_PUB.add;
647
648 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Price Protection responsibility not available for Last updated user'||l_user_name);
649
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652
653 FND_GLOBAL.APPS_INITIALIZE(l_txn_hdr_rec.last_updated_by,l_responsibility_id,l_dpp_application_id);
654 MO_GLOBAL.set_policy_context('S',l_txn_hdr_rec.org_id);
655
656 BEGIN
657 SELECT cost_type
658 INTO l_cost_type
659 FROM cst_cost_types
660 WHERE cost_type_id = 8;
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'The cost type DPPCost is not setup.');
664 RAISE FND_API.G_EXC_ERROR;
665 END;
666
667 SELECT dpp_cst_group_id_seq.nextval
668 INTO l_import_cost_group_id
669 FROM dual;
670
671 SELECT count(*)
672 INTO l_bom_installed
673 FROM bom_parameters
674 WHERE organization_id = l_txn_hdr_rec.org_id;
675
676 -- Begin A/c Generator Code ---
677
678 IF l_bg_process_mode = 'Y' THEN
679 --Changed from ozf_sys_parameters_all to hr_operating_units
680 SELECT chart_of_accounts_id
681 INTO l_chart_of_accounts_id
682 FROM gl_sets_of_books sob,
683 hr_operating_units hr
684 WHERE hr.set_of_books_id = sob.set_of_books_id
685 AND hr.organization_id = l_txn_hdr_rec.org_id;
686
687 l_debug_flag := fnd_profile.value('ACCOUNT_GENERATOR:DEBUG_MODE');
688
689 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Debug Flag: ' || l_debug_flag );
690
691 END IF;
692
693 -- Standard begin of API savepoint
694 SAVEPOINT Update_ItemCost_PVT;
695
696 IF l_item_cost_tbl.COUNT > 0 THEN
697
698 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before For Loop:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
699
700 FOR i IN l_item_cost_tbl.FIRST..l_item_cost_tbl.LAST LOOP
701 l_status_Update_tbl(i).transaction_line_id := l_item_cost_tbl(i).transaction_line_id;
702 l_status_Update_tbl(i).update_status := 'Y';
703
704 FOR Item_rec IN Item_cur(l_item_cost_tbl(i).inventory_item_id,l_txn_hdr_rec.org_id, l_trunc_sysdate) LOOP
705 l_item_cost_tbl(i).item_number := item_rec.item_number;
706 l_inv_org_details_tbl(i).inventory_org_name := Item_rec.organization_name;
707
708 IF l_item_cost_tbl(i).currency = Item_rec.currency_code THEN
709 l_to_amount := l_item_cost_tbl(i).new_price;
710 ELSE
711 l_to_amount := 0;
712 DPP_UTILITY_PVT.convert_currency(p_from_currency => l_item_cost_tbl(i).currency
713 ,p_to_currency => Item_rec.currency_code
714 ,p_conv_type => FND_API.G_MISS_CHAR
715 ,p_conv_rate => FND_API.G_MISS_NUM
716 ,p_conv_date => l_trunc_sysdate
717 ,p_from_amount => l_item_cost_tbl(i).new_price
718 ,x_return_status => l_return_status
719 ,x_to_amount => l_to_amount
720 ,x_rate => l_exchange_rate);
721 END IF;
722
723 IF Item_rec.primary_cost_method = 1 THEN
724 BEGIN
725 SELECT NVL(ctc.item_cost,0) prior_cost
726 INTO l_prior_cost
727 FROM cst_item_costs ctc
728 WHERE ctc.organization_id = Item_rec.organization_id
729 AND ctc.inventory_item_id = l_item_cost_tbl(i).inventory_item_id
730 AND ctc.cost_type_id = 1;
731 EXCEPTION
732 WHEN NO_DATA_FOUND THEN
733 l_prior_cost := 0;
734 END;
735 ELSE
736 BEGIN
737 SELECT NVL(item_cost,0)
738 INTO l_prior_cost
739 FROM cst_quantity_layers
740 WHERE organization_id = Item_rec.organization_id
741 AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id
742 AND cost_group_id = item_rec.cost_group_id;
743 EXCEPTION
744 WHEN NO_DATA_FOUND THEN
745 l_prior_cost := 0;
746 END;
747 END IF;
748
749 --Insert the available details into the GT table.
750 IF l_prior_cost = 0 THEN
751 l_txn_subtype := null;
752 ELSIF l_to_amount < l_prior_cost THEN
753 l_txn_subtype := 'PRICE_DECREASE';
754 ELSE
755 l_txn_subtype := 'PRICE_INCREASE';
756 END IF;
757
758 INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
759 NewPrice,
760 Currency,
761 Inventory_Org_Name,
762 inventory_item_id,
763 transaction_subtype,
764 transaction_line_id,
765 organization_id)
766 VALUES (l_item_cost_tbl(i).item_number,
767 l_item_cost_tbl(i).new_price,
768 l_item_cost_tbl(i).currency,
769 Item_rec.organization_name,
770 l_item_cost_tbl(i).inventory_item_id,
771 l_txn_subtype,
772 l_item_cost_tbl(i).transaction_line_id,
773 Item_rec.organization_id);
774
775 --If Prior cost is Zero or not defined then raise an Exception
776 IF l_prior_cost = 0 THEN
777 --ADD Messages
778 l_item_cost_tbl(i).Reason_for_failure := 'Costing setup is not done or the cost is Zero for the item:'||l_item_cost_tbl(i).item_number ;
779 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Costing setup is not done or the cost is Zero for the item:'||l_item_cost_tbl(i).item_number);
780 l_return_status := FND_API.G_RET_STS_ERROR;
781
782 UPDATE DPP_OUTPUT_XML_GT
783 SET reason_for_failure = l_item_cost_tbl(i).Reason_for_failure
784 WHERE organization_id = Item_rec.organization_id
785 AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
786
787 l_status_Update_tbl(i).update_status := 'N';
788 l_correct_item := 'N';
789 --CONTINUE; Fix for the bug 7621428
790 GOTO END_LOOP; --Fix for the bug 7621428
791 END IF;
792
793 IF l_to_amount <> l_prior_cost THEN
794 --Check if any organization has incorrect price existing for that item
795 l_incorrect_price_exists := 0;
796
797 BEGIN
798 SELECT 1
799 INTO l_incorrect_price_exists
800 FROM dual
801 WHERE EXISTS (SELECT cis.organization_id
802 FROM cst_item_costs cis,
803 org_organization_definitions ood
804 WHERE cis.organization_id = ood.organization_id
805 AND cis.organization_id = Item_rec.organization_id
806 AND ood.operating_unit = l_txn_hdr_rec.org_id
807 AND cis.cost_type_id = 1
808 AND cis.inventory_item_id = l_item_cost_tbl(i).inventory_item_id
809 AND cis.item_cost > 0
810 AND (((cis.item_cost-l_to_amount) >0
811 AND l_item_cost_tbl(i).price_change <0)
812 OR ((cis.item_cost-l_to_amount) <0 AND l_item_cost_tbl(i).price_change >0)));
813 EXCEPTION
814 WHEN NO_DATA_FOUND THEN
815 l_incorrect_price_exists := 0;
816 END;
817
818 IF NVL(l_incorrect_price_exists,0) = 0 THEN
819 BEGIN
820 SELECT 1
821 INTO l_incorrect_price_exists
822 FROM dual
823 WHERE EXISTS (SELECT cql.organization_id
824 FROM cst_quantity_layers cql,
825 org_organization_definitions ood
826 WHERE cql.organization_id = ood.organization_id
827
828 AND cql.organization_id = Item_rec.organization_id
829 AND ood.operating_unit = l_txn_hdr_rec.org_id
830 AND cql.inventory_item_id = l_item_cost_tbl(i).inventory_item_id
831 AND cql.cost_group_id = item_rec.cost_group_id
832 AND cql.item_cost > 0
833 AND(((cql.item_cost -l_to_amount) > 0
834 AND l_item_cost_tbl(i).price_change < 0)
835 OR((item_cost -l_to_amount) < 0 AND l_item_cost_tbl(i).price_change > 0)));
836
837 EXCEPTION
838 WHEN NO_DATA_FOUND THEN
839 l_incorrect_price_exists := 0;
840 END;
841 END IF; --NVL(l_incorrect_price_exists,0) = 0
842
843 IF NVL(l_incorrect_price_exists,0) = 1 THEN
844 l_item_cost_tbl(i).Reason_for_failure := 'This inventory organizations has an incorrect cost for item:'||l_item_cost_tbl(i).item_number;
845 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'This inventory organizations has an incorrect cost for item:'||l_item_cost_tbl(i).item_number);
846 l_return_status := FND_API.G_RET_STS_ERROR;
847
848 UPDATE DPP_OUTPUT_XML_GT
849 SET reason_for_failure = l_item_cost_tbl(i).Reason_for_failure
850 WHERE organization_id = Item_rec.organization_id
851 AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
852
853 l_status_Update_tbl(i).update_status := 'N';
854 l_correct_item := 'N';
855
856 GOTO INCORRECT_PRICE;
857 END IF;
858 END IF; -- end if for l_to_amount <> l_prior_cost if
859 << END_LOOP >> --Fix for the bug 7621428
860 null; --Fix for the bug 7621428
861 END LOOP; --ITEM REC Cursor end.
862
863 IF l_correct_item = 'N' THEN
864 null;
865 ELSE
866 l_insert_xla_header := 'Y';
867 FOR Item_rec IN Item_cur(l_item_cost_tbl(i).inventory_item_id,l_txn_hdr_rec.org_id, l_trunc_sysdate) LOOP
868 -- Begin A/c Generator Code ---
869 IF l_bg_process_mode = 'Y' THEN
870 l_itemkey := Fnd_Flex_Workflow.INITIALIZE('SQLGL',
871 'GL#',
872 l_chart_of_accounts_id,
873 'OZFACCTG');
874
875 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
876 itemkey => l_itemkey,
877 aname => 'ORG_ID',
878 avalue => l_txn_hdr_rec.org_id);
879
880 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
881 itemkey => l_itemkey,
882 aname => 'CHART_OF_ACCOUNTS_ID',
883 avalue => l_chart_of_accounts_id);
884
885 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
886 itemkey => l_itemkey,
887 aname => 'INVENTORY_ITEM_ID',
888 avalue => l_item_cost_tbl(i).inventory_item_id);
889
890 wf_engine.SetItemAttrText(itemtype => l_itemtype,
891 itemkey => l_itemkey,
892 aname => 'ACCOUNT_ID',
893 avalue => l_txn_hdr_rec.cost_adjustment_account);
894
895 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
896 itemkey => l_itemkey,
897 aname => 'ORGANIZATION_ID',
898 avalue => Item_rec.organization_id);
899
900 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'submitting the fnd_flex_workflow_generate process');
901
902 l_result := Fnd_Flex_Workflow.GENERATE('OZFACCTG',
903 l_itemkey,
904 TRUE,
905 x_return_ccid,
906 x_concat_segs,
907 x_concat_ids,
908 x_concat_descrs,
909 l_errmsg,
910 l_new_comb);
911
912 IF l_result THEN
913 IF Item_rec.primary_cost_method = 1 THEN
914 INSERT INTO cst_Item_cst_dtls_InterFace(Inventory_Item_Id,
915 Organization_Id,
916 Item_Cost,
917 Basis_Type,
918 Usage_Rate_Or_Amount,
919 Cost_Type_Id,
920 Cost_Type,
921 Last_Update_Date,
922 Last_Updated_By,
923 Creation_Date,
924 Created_By,
925 Group_Id,
926 Process_Flag,
927 Cost_Element,
928 Cost_Element_Id,
929 Net_Yield_Or_Shrinkage_Factor,
930 Level_Type)
931 VALUES(L_item_cost_tbl(i).inventory_item_id,
932 Item_rec.Organization_Id,
933 NULL,
934 1,
935 l_To_Amount,
936 l_Cost_Type_Id,
937 l_Cost_Type,
938 l_sysDate,
939 l_txn_hdr_rec.Last_Updated_By,
940 l_sysDate,
941 l_txn_hdr_rec.Last_Updated_By,
942 l_Import_Cost_Group_Id,
943 1,
944 NULL,
945 1,
946 1,
947 1);
948
949 l_status_Update_tbl(i).update_status := 'Y';
950 ELSIF Item_rec.primary_cost_method = 2 THEN
951 INSERT INTO mtl_Transactions_InterFace(Transaction_InterFace_Id,
952 Transaction_Header_Id,
953 Source_Code,
954 Source_Line_Id,
955 Source_Header_Id,
956 Process_Flag,
957 Transaction_Mode,
958 Last_Update_Date,
959 Last_Updated_By,
960 Creation_Date,
961 Created_By,
962 Organization_Id,
963 Transaction_Quantity,
964 Transaction_uom,
965 Transaction_Date,
966 Transaction_Type_Id,
967 Inventory_Item_Id,
968 New_Average_Cost,
969 Currency_Code,
970 Cost_Group_Id,
971 Material_Account,
972 Transaction_Reference)
973 VALUES (dpp_mtl_txn_IfAce_Id_seq.Nextval,
974 l_txn_hdr_rec.Transaction_Header_Id,
975 'Price Protection',
976 l_txn_hdr_rec.Execution_Detail_Id,
977 l_txn_hdr_rec.Execution_Detail_Id,
978 1, -- Process is 1
979 3, -- Background is 3
980 l_sysDate,
981 l_txn_hdr_rec.Last_Updated_By,
982 SYSDATE,
983 l_txn_hdr_rec.Last_Updated_By,
984 Item_rec.Organization_Id,
985 1,
986 Item_rec.Transaction_uom,
987 l_sysDate,
988 l_Transaction_Type_Id,
989 L_item_cost_tbl(i).inventory_item_id,
990 l_To_Amount,
991 L_item_cost_tbl(i).currency,
992 Item_rec.Cost_Group_Id,
993 Nvl(x_Return_ccId,l_txn_hdr_rec.Cost_Adjustment_Account),
994 l_txn_SubType);
995
996 INSERT INTO mtl_txn_Cost_det_InterFace(Transaction_InterFace_Id,
997 Last_Update_Date,
998 Last_Updated_By,
999 Creation_Date,
1000 Created_By,
1001 Organization_Id,
1002 Cost_Element_Id,
1003 Level_Type,
1004 New_Average_Cost)
1005 VALUES (dpp_mtl_txn_IfAce_Id_seq.Currval,
1006 l_sysDate,
1007 l_txn_hdr_rec.Last_Updated_By,
1008 l_sysDate,
1009 l_txn_hdr_rec.Last_Updated_By,
1010 Item_rec.Organization_Id,
1011 1,
1012 1,
1013 l_To_Amount);
1014
1015 l_status_Update_tbl(i).update_status := 'Y';
1016
1017 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Success OZFACCTG WF'||'-'||x_return_ccid);
1018
1019 END IF; -- end if for primary_cost_method
1020 ELSE
1021
1022 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'OZFACCTG WF - Failure');
1023
1024 l_return_status := FND_API.G_RET_STS_ERROR;
1025 l_status_Update_tbl(i).update_status := 'N';
1026 l_item_cost_tbl(i).Reason_for_failure := 'OZFACCTG Workflow - Failure for item:'||l_item_cost_tbl(i).item_number||' Error Message: '||SUBSTR(l_errmsg,1,254);
1027
1028 UPDATE DPP_OUTPUT_XML_GT
1029 SET reason_for_failure = l_item_cost_tbl(i).Reason_for_failure
1030 WHERE organization_id = Item_rec.organization_id
1031 AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
1032
1033 GOTO WORKFLOW_ERROR;
1034
1035 END IF; -- end if for l_result
1036
1037 ELSE
1038 -- End A/c Generator Code---
1039 IF Item_rec.primary_cost_method = 1 THEN
1040 INSERT INTO cst_Item_cst_dtls_InterFace(Inventory_Item_Id,
1041 Organization_Id,
1042 Item_Cost,
1043 Basis_Type,
1044 Usage_Rate_Or_Amount,
1045 Cost_Type_Id,
1046 Cost_Type,
1047 Last_Update_Date,
1048 Last_Updated_By,
1049 Creation_Date,
1050 Created_By,
1051 Group_Id,
1052 Process_Flag,
1053 Cost_Element,
1054 Cost_Element_Id,
1055 Net_Yield_Or_Shrinkage_Factor,
1056 Level_Type)
1057 VALUES(L_item_cost_tbl(i).inventory_item_id,
1058 Item_rec.Organization_Id,
1059 NULL,
1060 1,
1061 l_To_Amount,
1062 l_Cost_Type_Id,
1063 l_Cost_Type,
1064 l_sysDate,
1065 l_txn_hdr_rec.Last_Updated_By,
1066 l_sysDate,
1067 l_txn_hdr_rec.Last_Updated_By,
1068 l_Import_Cost_Group_Id,
1069 1,
1070 NULL,
1071 1,
1072 1,
1073 1);
1074
1075 l_status_Update_tbl(i).update_status := 'Y';
1076 ELSIF Item_rec.primary_cost_method = 2 THEN
1077 INSERT INTO mtl_Transactions_InterFace(Transaction_InterFace_Id,
1078 Transaction_Header_Id,
1079 Source_Code,
1080 Source_Line_Id,
1081 Source_Header_Id,
1082 Process_Flag,
1083 Transaction_Mode,
1084 Last_Update_Date,
1085 Last_Updated_By,
1086 Creation_Date,
1087 Created_By,
1088 Organization_Id,
1089 Transaction_Quantity,
1090 Transaction_uom,
1091 Transaction_Date,
1092 Transaction_Type_Id,
1093 Inventory_Item_Id,
1094 New_Average_Cost,
1095 Currency_Code,
1096 Cost_Group_Id,
1097 Material_Account,
1098 Transaction_Reference)
1099 VALUES (dpp_mtl_txn_IfAce_Id_seq.Nextval,
1100 l_txn_hdr_rec.Transaction_Header_Id,
1101 'Price Protection',
1102 l_txn_hdr_rec.Execution_Detail_Id,
1103 l_txn_hdr_rec.Execution_Detail_Id,
1104 1, -- Process is 1
1105 3, -- Background is 3
1106 l_sysDate,
1107 l_txn_hdr_rec.Last_Updated_By,
1108 l_sysDate,
1109 l_txn_hdr_rec.Last_Updated_By,
1110 Item_rec.Organization_Id,
1111 1,
1112 Item_rec.Transaction_uom,
1113 l_sysDate,
1114 l_Transaction_Type_Id,
1115 L_item_cost_tbl(i).inventory_item_id,
1116 l_To_Amount,
1117 L_item_cost_tbl(i).currency,
1118 Item_rec.Cost_Group_Id,
1119 Nvl(x_Return_ccId,l_txn_hdr_rec.Cost_Adjustment_Account),
1120 l_txn_SubType);
1121
1122 INSERT INTO mtl_txn_Cost_det_InterFace(Transaction_InterFace_Id,
1123 Last_Update_Date,
1124 Last_Updated_By,
1125 Creation_Date,
1126 Created_By,
1127 Organization_Id,
1128 Cost_Element_Id,
1129 Level_Type,
1130 New_Average_Cost)
1131 VALUES (dpp_mtl_txn_IfAce_Id_seq.Currval,
1132 l_sysDate,
1133 l_txn_hdr_rec.Last_Updated_By,
1134 l_sysDate,
1135 l_txn_hdr_rec.Last_Updated_By,
1136 Item_rec.Organization_Id,
1137 1,
1138 1,
1139 l_To_Amount);
1140 l_status_Update_tbl(i).update_status := 'Y';
1141 END IF;
1142 END IF; -- a/c generator end if
1143 END LOOP; --ITEM REC Cursor end.
1144 END IF; --Correct Item
1145 l_item_cost_tbl(i).inv_org_details_tbl := l_inv_org_details_tbl;
1146 END LOOP;
1147 END IF;
1148
1149 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After For Loop:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1150
1151 --Calling the "Cost Import Process" Concurrent Program
1152 BEGIN
1153 l_cost_import_req_id := FND_REQUEST.submit_request(
1154 application => 'BOM',
1155 program => 'CSTPCIMP',
1156 description => NULL,
1157 start_time => NULL,
1158 sub_request => FALSE,
1159 argument1 => 4, --Import cost option
1160 argument2 => 2 , --Mode to run this request
1161 argument3 => 1, --Group ID option
1162 argument4 => 1, --Group ID Dummy
1163 argument5 => l_import_cost_group_id, --Group ID
1164 argument6 => l_cost_type, --Cost type to import to
1165 argument7 => 1); --Delete successful rows
1166
1167 COMMIT;
1168
1169 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Cost Import Request ID: '||l_cost_import_req_id);
1170
1171 l_wait_req := fnd_concurrent.wait_for_request(
1172 request_id => l_cost_import_req_id,
1173 interval => 60,
1174 max_wait => 0,
1175 phase => l_phase,
1176 status => l_status,
1177 dev_phase => l_dev_phase,
1178 dev_status => l_dev_status,
1179 message => l_message);
1180 END;
1181
1182 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Import Request:' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1183 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_phase: ' || l_dev_phase || '; l_status: ' || l_dev_status);
1184
1185 IF l_dev_status NOT IN ('WARNING','ERROR') THEN
1186
1187 FOR Organization_Rec IN Organization_Cur(l_cost_type_id,l_cost_import_req_id)
1188 LOOP
1189 --Calling the "Update Standard Costs" Concurrent Program
1190 BEGIN
1191 l_cost_upd_req_id := FND_REQUEST.submit_request(
1192 application => 'BOM',
1193 program => 'CMCICU',
1194 description => NULL,
1195 start_time => NULL,
1196 sub_request => FALSE,
1197 argument1 => Organization_Rec.organization_id,
1198 argument2 => l_bom_installed ,
1199 argument3 => l_cost_type_id,
1200 argument4 => 1,
1201 argument5 => Nvl(x_Return_ccId,l_txn_hdr_rec.Cost_Adjustment_Account),
1202 argument6 => 'DPP Std Cost Update - Execution Detail ID: '||l_txn_hdr_rec.execution_detail_id,
1203 argument7 => 1,
1204 argument8 => 1,
1205 argument9 => 3,
1206 argument10 => null,
1207 argument11 => null,
1208 argument12 => null,
1209 argument13 => null,
1210 argument14 => null,
1211 argument15 => null,
1212 argument16 => null,
1213 argument17 => null,
1214 argument18 => null,
1215 argument19 => null,
1216 argument20 => null,
1217 argument21 => null,
1218 argument22 => null,
1219 argument23 => 1,
1220 argument24 => 2);
1221 COMMIT;
1222
1223 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Std Cost Update Request ID: '||l_cost_upd_req_id);
1224
1225 l_wait_req := fnd_concurrent.wait_for_request(
1226 request_id => l_cost_upd_req_id,
1227 interval => 60,
1228 max_wait => 0,
1229 phase => l_phase,
1230 status => l_status,
1231 dev_phase => l_dev_phase,
1232 dev_status => l_dev_status,
1233 message => l_message);
1234
1235 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'l_phase: '||l_dev_phase|| '; l_status: '||l_dev_status);
1236
1237 END;
1238
1239 IF l_dev_status IN ('WARNING','ERROR') THEN
1240 l_return_status := FND_API.g_ret_sts_error;
1241 UPDATE DPP_OUTPUT_XML_GT
1242 SET reason_for_failure = 'Std Cost Update Request ID: '||l_cost_upd_req_id||' '|| l_dev_phase||' with '||l_dev_status
1243 WHERE organization_id = Organization_Rec.organization_id;
1244
1245 -- Flip the success flag to N for all items to enable resubmission
1246 FOR i IN l_status_Update_tbl.FIRST..l_status_Update_tbl.LAST
1247 LOOP
1248 l_status_Update_tbl(i).update_status := 'N';
1249 END LOOP;
1250 END IF;
1251
1252 END LOOP;
1253
1254 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Std Cost Update request:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1255
1256 ELSE
1257 l_return_status := FND_API.g_ret_sts_error;
1258 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Item Cost Import Request ID: '||l_cost_import_req_id||' '|| l_dev_phase||' with '||l_dev_status);
1259
1260 UPDATE DPP_OUTPUT_XML_GT
1261 SET reason_for_failure = 'Item Cost Import Request ID: '||l_cost_import_req_id||' '|| l_dev_phase||' with '||l_dev_status;
1262
1263 -- Flip the success flag to N for all items to enable resubmission
1264 FOR i IN l_status_Update_tbl.FIRST..l_status_Update_tbl.LAST
1265 LOOP
1266 l_status_Update_tbl(i).update_status := 'N';
1267 END LOOP;
1268
1269 END IF;
1270
1271 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Starting Error Processing: ' || l_return_status);
1272
1273 -- Standard begin of API savepoint
1274 SAVEPOINT Update_ItemCost_PVT;
1275
1276 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before Std Cost Error Processing:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1277
1278 -- Begin Error Processing for Std Cost Update
1279 SELECT Inventory_Item_Id,
1280 Error_Explanation
1281 BULK COLLECT INTO Inventory_Item_Ids,
1282 Error_Explanations
1283 FROM cst_Item_cst_dtls_InterFace
1284 WHERE Cost_Type_Id = l_Cost_Type_Id
1285 AND Group_Id = l_Import_Cost_Group_Id
1286 AND Process_Flag = 3;
1287
1288 FORALL indx IN inventory_item_ids.FIRST .. inventory_item_ids.LAST
1289 UPDATE DPP_OUTPUT_XML_GT
1290 SET reason_for_failure = error_explanations(indx)
1291 WHERE inventory_item_id = inventory_item_ids(indx);
1292
1293
1294 IF inventory_item_ids.COUNT > 0 THEN
1295 l_return_status := FND_API.g_ret_sts_error;
1296 END IF;
1297
1298 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Std Cost Error Processing:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1299
1300 -- End Error Processing for Std Cost Update
1301
1302 -- Begin Error Processing for Avg Cost Update
1303 SELECT COUNT(* )
1304 INTO l_InterFace_Pending_Count
1305 FROM mtl_Transactions_InterFace
1306 WHERE Source_Code = 'Price Protection'
1307 AND Source_Header_Id = l_txn_hdr_rec.Execution_Detail_Id
1308 AND Transaction_Header_Id = l_txn_hdr_rec.Transaction_Header_Id
1309 AND Process_Flag = 1;
1310
1311 IF l_interface_pending_count > 0 THEN
1312
1313 l_wait_status := wait_for_rec_processing(in_execution_detail_id => l_txn_hdr_rec.Execution_detail_ID,
1314 in_transaction_header_id => l_txn_hdr_rec.Transaction_Header_ID,
1315 interval => 60,
1316 max_wait => 0,
1317 message => l_msg_data);
1318
1319 ELSE
1320 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before Avg Cost Error Processing:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1321
1322 SELECT Source_Line_Id,
1323 Nvl(Error_Explanation,Error_Code) Error_Explanation
1324 BULK COLLECT INTO Source_Line_Ids,
1325 Error_Explanations
1326 FROM mtl_Transactions_InterFace
1327 WHERE Source_Code = 'Price Protection'
1328 AND Source_Header_Id = l_txn_hdr_rec.Execution_Detail_Id
1329 AND Transaction_Header_Id = l_txn_hdr_rec.Transaction_Header_Id
1330 AND Process_Flag = 3;
1331
1332 FORALL indx IN source_line_ids.FIRST .. source_line_ids.LAST
1333 UPDATE DPP_OUTPUT_XML_GT
1334 SET reason_for_failure = error_explanations(indx)
1335 WHERE transaction_line_id = source_line_ids(indx);
1336
1337 IF source_line_ids.COUNT > 0 THEN
1338 l_return_status := fnd_api.g_ret_sts_error;
1339 END IF;
1340
1341 END IF;
1342 -- End Error Processing for Avg Cost Update
1343
1344 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Avg Cost Error Processing:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1345 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inserting into SLA tables: ' || l_return_status);
1346
1347 l_sysdate := sysdate;
1348
1349
1350 IF l_insert_xla_header = 'Y' THEN
1351 BEGIN
1352 INSERT INTO DPP_XLA_HEADERS(
1353 transaction_header_id
1354 ,pp_transaction_type
1355 ,base_transaction_header_id
1356 ,processed_flag
1357 ,creation_date
1358 ,created_by
1359 ,last_update_date
1360 ,last_updated_by
1361 ,last_update_login)
1362 VALUES(
1363 l_txn_hdr_rec.Transaction_Header_ID
1364 ,'COST_UPDATE'
1365 ,l_txn_hdr_rec.Execution_Detail_ID
1366 ,l_processed_flag
1367 ,l_sysdate
1368 ,l_txn_hdr_rec.last_updated_by
1369 ,l_sysdate
1370 ,l_txn_hdr_rec.last_updated_by
1371 ,FND_GLOBAL.login_id);
1372
1373 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After SLA Hdr Insert:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1374 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inserting into SLA Lines table for Avg Cost Update ');
1375
1376 -- Begin SLA Line Processing for Avg Cost Update
1377 BEGIN
1378 SELECT
1379 mmt.transaction_id,
1380 dpp_gt.transaction_line_id,
1381 dpp_gt.transaction_subtype
1382 BULK COLLECT INTO
1383 transaction_ids,
1384 transaction_line_ids,
1385 transaction_subtypes
1386 FROM
1387 mtl_material_transactions mmt,
1388 DPP_OUTPUT_XML_GT dpp_gt
1389 WHERE
1390 mmt.transaction_source_type_id = 13 -- Inventory
1391 AND mmt.source_line_id = l_txn_hdr_rec.Execution_Detail_ID
1392 AND mmt.transaction_type_id = l_transaction_type_id
1393 AND mmt.transaction_action_id = l_transaction_action_id
1394 AND dpp_gt.organization_id = mmt.organization_id
1395 AND dpp_gt.inventory_item_id = mmt.inventory_item_id;
1396
1397 FORALL indx IN transaction_ids.FIRST .. transaction_ids.LAST
1398 INSERT INTO DPP_XLA_LINES(
1399 transaction_header_id
1400 ,transaction_line_id
1401 ,base_transaction_header_id
1402 ,base_transaction_line_id
1403 ,transaction_sub_type
1404 ,creation_date
1405 ,created_by
1406 ,last_update_date
1407 ,last_updated_by)
1408 VALUES(
1409 l_txn_hdr_rec.Transaction_Header_ID
1410 ,transaction_line_ids(indx)
1411 ,l_txn_hdr_rec.Execution_Detail_ID
1412 ,transaction_ids(indx)
1413 ,transaction_subtypes(indx)
1414 ,l_sysdate
1415 ,l_txn_hdr_rec.last_updated_by
1416 ,l_sysdate
1417 ,l_txn_hdr_rec.last_updated_by);
1418 EXCEPTION
1419 WHEN NO_DATA_FOUND THEN
1420 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'No Data Found for SLA lines table insertion - Avg Costing...');
1421 END;
1422 -- End SLA Line Processing for Avg Cost Update
1423
1424 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inserting into SLA Lines table for Std Cost Update: ');
1425 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before SLA Line Insert - Std:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1426
1427 -- Begin SLA Line Processing for Std Cost Update
1428 BEGIN
1429 SELECT
1430 mmt.transaction_id,
1431 dpp_gt.transaction_line_id,
1432 dpp_gt.transaction_subtype
1433 BULK COLLECT INTO
1434 transaction_ids,
1435 transaction_line_ids,
1436 transaction_subtypes
1437 FROM
1438 mtl_material_transactions mmt,
1439 cst_cost_updates ccu,
1440 DPP_OUTPUT_XML_GT dpp_gt
1441 WHERE
1442 mmt.transaction_source_id = ccu.cost_update_id
1443 AND mmt.transaction_source_type_id = 11
1444 AND mmt.transaction_action_id = l_transaction_action_id
1445 AND ccu.description = 'DPP Std Cost Update - Execution Detail ID: '||l_txn_hdr_rec.execution_detail_id
1446 AND ccu.cost_type_id = l_cost_type_id
1447 AND dpp_gt.organization_id = mmt.organization_id
1448 AND dpp_gt.inventory_item_id = mmt.inventory_item_id;
1449
1450 FORALL indx IN transaction_ids.FIRST .. transaction_ids.LAST
1451 INSERT INTO DPP_XLA_LINES(
1452 transaction_header_id
1453 ,transaction_line_id
1454 ,base_transaction_header_id
1455 ,base_transaction_line_id
1456 ,transaction_sub_type
1457 ,creation_date
1458 ,created_by
1459 ,last_update_date
1460 ,last_updated_by)
1461 VALUES(
1462 l_txn_hdr_rec.Transaction_Header_ID
1463 ,transaction_line_ids(indx)
1464 ,l_txn_hdr_rec.Execution_Detail_ID
1465 ,transaction_ids(indx)
1466 ,transaction_subtypes(indx)
1467 ,l_sysdate
1468 ,l_txn_hdr_rec.last_updated_by
1469 ,l_sysdate
1470 ,l_txn_hdr_rec.last_updated_by);
1471
1472 EXCEPTION
1473 WHEN NO_DATA_FOUND THEN
1474 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'No Data Found for SLA table insertion...');
1475 END;
1476 -- End SLA Line Processing for Std Cost Update
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1481 -- IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1482 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1483 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT.Update_ItemCost-SLA Tables Insertion');
1484 fnd_message.set_token('ERRNO', sqlcode);
1485 fnd_message.set_token('REASON', sqlerrm);
1486 FND_MSG_PUB.add;
1487 -- END IF;
1488 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in SLA Tables Insertion:' || sqlerrm);
1489 END;
1490 END IF;
1491
1492 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before Exe Dtls Update:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1493
1494 << INCORRECT_PRICE >>
1495
1496 << WORKFLOW_ERROR >>
1497
1498 x_return_status := l_return_status;
1499
1500 SELECT DECODE(l_return_status,FND_API.G_RET_STS_SUCCESS,'SUCCESS','WARNING')
1501 INTO l_execution_status
1502 FROM DUAL;
1503
1504 BEGIN
1505 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Status before generating output xml: ' || l_return_status);
1506
1507 --Get the output XML from DPP_OUTPUT_XML_GT table
1508 l_Transaction_Number := '''' || l_txn_hdr_rec.Transaction_Number || '''';
1509
1510 IF x_return_status IN (FND_API.G_RET_STS_ERROR, FND_API.G_RET_STS_UNEXP_ERROR) THEN
1511 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
1512 CURSOR (Select Item_Number ITEMNUMBER,
1513 inventory_org_name ORGNAME,
1514 NewPrice NEWPRICE,
1515 Currency CURRENCY,
1516 Reason_For_Failure REASON
1517 from DPP_OUTPUT_XML_GT
1518 where Reason_For_Failure IS NOT NULL) TRANSACTION from dual');
1519 ELSE
1520 l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER from dual');
1521 END IF;
1522
1523 dbms_xmlquery.setRowTag(l_queryCtx, 'ROOT');
1524 l_output_xml := dbms_xmlquery.getXml(l_queryCtx);
1525 dbms_xmlquery.closeContext(l_queryCtx);
1526
1527 EXCEPTION
1528 WHEN OTHERS THEN
1529 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1531 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT.Update_ItemCost-XML Generation');
1532 fnd_message.set_token('ERRNO', sqlcode);
1533 fnd_message.set_token('REASON', sqlerrm);
1534 FND_MSG_PUB.add;
1535 END;
1536
1537 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Status before calling update API: ' || l_return_status);
1538
1539 l_exe_update_rec.Transaction_Header_ID := l_txn_hdr_rec.Transaction_Header_ID;
1540 l_exe_update_rec.Org_ID := l_txn_hdr_rec.Org_ID;
1541 l_exe_update_rec.Execution_Detail_ID := l_txn_hdr_rec.Execution_Detail_ID;
1542 l_exe_update_rec.Output_XML := l_output_xml;
1543 l_exe_update_rec.execution_status := l_execution_status;
1544 l_exe_update_rec.Execution_End_Date := SYSDATE;
1545 l_exe_update_rec.Provider_Process_Id := l_txn_hdr_rec.Provider_Process_Id;
1546 l_exe_update_rec.Provider_Process_Instance_id := l_txn_hdr_rec.Provider_Process_Instance_id;
1547 l_exe_update_rec.Last_Updated_By := l_txn_hdr_rec.Last_Updated_By;
1548
1549 DPP_ExecutionDetails_PVT.Update_ExecutionDetails(
1550 p_api_version => l_api_version
1551 ,p_init_msg_list => FND_API.G_FALSE
1552 ,p_commit => FND_API.G_FALSE
1553 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1554 ,x_return_status => l_return_status
1555 ,x_msg_count => l_msg_count
1556 ,x_msg_data => l_msg_data
1557 ,p_EXE_UPDATE_rec => l_exe_update_rec
1558 ,p_status_Update_tbl=> l_status_Update_tbl
1559 );
1560
1561 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Exe Dtls Update:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
1562
1563 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1564 x_return_status := l_return_status;
1565 END IF;
1566
1567 -- Standard check for p_commit
1568 IF FND_API.to_Boolean( p_commit )
1569 THEN
1570 COMMIT WORK;
1571 END IF;
1572
1573 -- Debug Message
1574
1575 dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Private API: ' || l_api_name || 'end');
1576
1577 -- Standard call to get message count and if count is 1, get message info.
1578 FND_MSG_PUB.Count_And_Get
1579 (p_count => x_msg_count,
1580 p_data => x_msg_data
1581 );
1582 IF x_msg_count > 1 THEN
1583 FOR I IN 1..x_msg_count LOOP
1584 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1585 END LOOP;
1586 END IF;
1587 --Exception Handling
1588 EXCEPTION
1589
1590 WHEN FND_API.G_EXC_ERROR THEN
1591 x_return_status := FND_API.G_RET_STS_ERROR;
1592 -- Standard call to get message count and if count=1, get the message
1593 FND_MSG_PUB.Count_And_Get (
1594 p_encoded => FND_API.G_FALSE,
1595 p_count => x_msg_count,
1596 p_data => x_msg_data
1597 );
1598 IF x_msg_count > 1 THEN
1599 FOR I IN 1..x_msg_count LOOP
1600 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1601 END LOOP;
1602 END IF;
1603
1604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1605 ROLLBACK TO UPDATE_ITEMCOST_PVT;
1606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1607 -- Standard call to get message count and if count=1, get the message
1608 FND_MSG_PUB.Count_And_Get (
1609 p_encoded => FND_API.G_FALSE,
1610 p_count => x_msg_count,
1611 p_data => x_msg_data
1612 );
1613 IF x_msg_count > 1 THEN
1614 FOR I IN 1..x_msg_count LOOP
1615 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1616 END LOOP;
1617 END IF;
1618
1619 WHEN OTHERS THEN
1620 ROLLBACK TO UPDATE_ITEMCOST_PVT;
1621 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1622 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
1623 fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT.Update_ItemCost');
1624 fnd_message.set_token('ERRNO', sqlcode);
1625 fnd_message.set_token('REASON', sqlerrm);
1626 FND_MSG_PUB.add;
1627
1628 -- Standard call to get message count and if count=1, get the message
1629 FND_MSG_PUB.Count_And_Get (
1630 p_encoded => FND_API.G_FALSE,
1631 p_count => x_msg_count,
1632 p_data => x_msg_data
1633 );
1634 IF x_msg_count > 1 THEN
1635 FOR I IN 1..x_msg_count LOOP
1636 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
1637 END LOOP;
1638 END IF;
1639 END Update_ItemCost;
1640
1641 END DPP_ITEMCOST_PVT;