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