[Home] [Help]
PACKAGE BODY: APPS.CN_COMP_PLAN_XMLCOPY_PVT
Source
1 PACKAGE BODY CN_COMP_PLAN_XMLCOPY_PVT AS
2 /*$Header: cnvcpxmlb.pls 120.46.12020000.7 2013/02/21 09:20:24 avnalam ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_COMP_PLAN_XMLCOPY_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(15) := 'cnvcpxmlb.pls';
6
7 /****************************************************************************/
8 -- Import_PlanCopy Procedure - This Procedure is just a wrapper around
9 -- the Parse_XML Procedure. This Procedure does following things
10 -- 1. It validates the XML.
11 -- 2. Gets prefix, start_date and end_date information
12 -- 3. Pass this information to Parse_XML Procedure.
13 -- 4. Gets the status of Import from Parse_XML and updates cn_copy_requests.
14 /***************************************************************************/
15 PROCEDURE Import_PlanCopy
16 (errbuf OUT NOCOPY VARCHAR2,
17 retcode OUT NOCOPY NUMBER,
18 p_exp_imp_request_id IN cn_copy_requests_all.exp_imp_request_id%TYPE) IS
19
20 CURSOR oic_plan_copy IS
21 SELECT extract(value(v),'/OIC_PLAN_COPY') "CP"
22 FROM cn_copy_requests_all cr,
23 TABLE(XMLSequence(extract(cr.file_content_xmltype,'/OIC_PLAN_COPY'))) v
24 WHERE cr.exp_imp_request_id = p_exp_imp_request_id;
25
26 CURSOR oic_object_count IS
27 SELECT COUNT(extract(value(v),'/CnCompPlansVO'))
28 FROM cn_copy_requests_all cr,
29 TABLE(XMLSequence(extract(cr.file_content_xmltype,'/OIC_PLAN_COPY/CnCompPlansVO'))) v
30 WHERE cr.exp_imp_request_id = p_exp_imp_request_id;
31
32 v_prefix cn_copy_requests_all.prefix_info%TYPE;
33 v_xml cn_copy_requests_all.file_content_xmltype%TYPE;
34 v_org_id cn_copy_requests_all.org_id%TYPE;
35 v_object_count NUMBER;
36 v_start_date DATE;
37 v_end_date DATE;
38 l_msgs VARCHAR2(2000);
39 l_err_message VARCHAR2(2000);
40 x_import_status VARCHAR2(30);
41 l_api_name CONSTANT VARCHAR2(30) := 'Import_PlanCopy';
42 x_return_status VARCHAR2(1);
43 x_msg_count NUMBER;
44 x_msg_data VARCHAR2(2000);
45
46 BEGIN
47 fnd_file.put_line(fnd_file.log, '**************************************************************');
48 fnd_file.put_line(fnd_file.log, '******************* START - PLAN COPY IMPORT *****************');
49 fnd_file.put_line(fnd_file.log, '**************************************************************');
50
51 -- Standard Start of API savepoint
52 SAVEPOINT Import_PlanCopy;
53 retcode := 0;
54
55 -- Get Prefix and Date Information
56 SELECT prefix_info, change_start_date, change_end_date, org_id INTO v_prefix, v_start_date, v_end_date, v_org_id
57 FROM cn_copy_requests_all
58 WHERE exp_imp_request_id = p_exp_imp_request_id;
59
60 -- Check if OrgId is not set
61 IF v_org_id IS NULL THEN
62 RAISE fnd_api.g_exc_unexpected_error;
63 END IF;
64
65 -- Populating CLOB and XMLType Columns of cn_copy_requests_all
66 -- Plan Copy Import Request only populates the BLOB column.
67 -- Calling Common Utility Package for conversion to CLOB and XMLType
68 cn_plancopy_util_pvt.convert_blob_to_xmltype (
69 p_api_version => 1.0,
70 p_init_msg_list => FND_API.G_FALSE,
71 p_commit => FND_API.G_FALSE,
72 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
73 p_exp_imp_id => p_exp_imp_request_id,
74 x_return_status => x_return_status,
75 x_msg_count => x_msg_count,
76 x_msg_data => x_msg_data);
77 IF x_return_status = fnd_api.g_ret_sts_success THEN
78 COMMIT;
79 ELSE
80 RAISE fnd_api.g_exc_unexpected_error;
81 END IF;
82
83 -- Fetching XML from XMLType.
84 OPEN oic_plan_copy;
85 FETCH oic_plan_copy INTO v_xml;
86 CLOSE oic_plan_copy;
87
88 -- Count total number of objects to copy
89 OPEN oic_object_count;
90 FETCH oic_object_count INTO v_object_count;
91 CLOSE oic_object_count;
92
93 -- Parse XML
94 Parse_XML(p_api_version => 1.0,
95 p_init_msg_list => FND_API.G_FALSE,
96 p_commit => FND_API.G_FALSE,
97 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
98 p_xml => v_xml,
99 p_prefix => v_prefix,
100 p_start_date => v_start_date,
101 p_end_date => v_end_date,
102 p_org_id => v_org_id,
103 p_object_count => v_object_count,
104 x_import_status => x_import_status);
105
106 -- Update the status of Import Process
107 IF x_import_status = 'COMPLETED' THEN
108 UPDATE cn_copy_requests_all
109 SET status_code = 'COMPLETED',
110 completion_date = SYSDATE
111 WHERE exp_imp_request_id = p_exp_imp_request_id;
112 COMMIT;
113 ELSE
114 UPDATE cn_copy_requests_all
115 SET status_code = 'FAILED',
116 completion_date = SYSDATE
117 WHERE exp_imp_request_id = p_exp_imp_request_id;
118 COMMIT;
119 END IF;
120
121 EXCEPTION
122 WHEN fnd_api.g_exc_unexpected_error THEN
123 ROLLBACK TO Import_PlanCopy;
124 retcode := 2;
125 errbuf := SQLCODE||' '||Sqlerrm;
126 UPDATE cn_copy_requests_all
127 SET status_code = 'FAILED',
128 completion_date = SYSDATE
129 WHERE exp_imp_request_id = p_exp_imp_request_id;
130 COMMIT;
131 WHEN OTHERS THEN
132 retcode := 2;
133 errbuf := SQLCODE||' '||Sqlerrm;
134 UPDATE cn_copy_requests_all
135 SET status_code = 'FAILED',
136 completion_date = SYSDATE
137 WHERE exp_imp_request_id = p_exp_imp_request_id;
138 COMMIT;
139 END Import_PlanCopy;
140
141 /***************************************************************************/
142 -- Parse_XML - This Procedure parses the XML file.This procedure does following
143 -- 1. Parse each component of the compensation plan in the XML and
144 -- creates the component in the target system, if successful.
145 -- 2. Enters the failure and success information / messages in the
146 -- Log file of the request.
147 -- 3. Return the import process status back to Import_PlanCopy, which
148 -- updates cn_copy_requests table.
149 /***************************************************************************/
150 PROCEDURE Parse_XML
151 (p_api_version IN NUMBER := 1.0,
152 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
153 p_commit IN VARCHAR2 := FND_API.G_FALSE,
154 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
155 p_xml IN cn_copy_requests_all.file_content_xmltype%TYPE,
156 p_prefix IN cn_copy_requests_all.prefix_info%TYPE,
157 p_start_date IN DATE,
158 p_end_date IN DATE,
159 p_org_id IN cn_copy_requests_all.org_id%TYPE,
160 p_object_count IN NUMBER,
161 x_import_status OUT NOCOPY VARCHAR2)IS
162
163 -- Table Record Declaration
164 v_expression_rec cn_calc_sql_exps%ROWTYPE;
165 v_rate_dimension_rec cn_rate_dimensions%ROWTYPE;
166 v_rate_dim_tiers_tbl cn_rate_dimensions_pvt.tiers_tbl_type := cn_rate_dimensions_pvt.g_miss_tiers_tbl;
167 v_rate_table_rec cn_rate_schedules%ROWTYPE;
168 v_rate_sch_dims_tbl cn_multi_rate_schedules_pvt.dims_tbl_type := cn_multi_rate_schedules_pvt.g_miss_dims_tbl;
169 v_rate_tiers_tbl cn_multi_rate_schedules_pvt.comm_tbl_type;
170 v_formula_rec cn_calc_formulas%ROWTYPE;
171 v_input_exp_tbl cn_calc_formulas_pvt.input_tbl_type := cn_calc_formulas_pvt.g_miss_input_tbl;
172 v_rt_assign_tbl cn_calc_formulas_pvt.rt_assign_tbl_type := cn_calc_formulas_pvt.g_miss_rt_assign_tbl;
173 v_plan_element_rec cn_plan_element_pub.plan_element_rec_type;
174 v_revenue_class_tbl cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl;
175 v_rev_uplift_tbl cn_plan_element_pub.rev_uplift_rec_tbl_type := cn_plan_element_pub.g_miss_rev_uplift_rec_tbl;
176 v_trx_factor_tbl cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl;
177 v_rt_quota_asgns_tbl cn_plan_element_pub.rt_quota_asgns_rec_tbl_type := cn_plan_element_pub.g_miss_rt_quota_asgns_rec_tbl;
178 v_period_quotas_tbl cn_plan_element_pub.period_quotas_rec_tbl_type := cn_plan_element_pub.g_miss_period_quotas_rec_tbl;
179 v_comp_plan_rec cn_comp_plan_pvt.comp_plan_rec_type;
180 v_quota_assign_tbl cn_quota_assign_pvt.quota_assign_tbl_type := cn_quota_assign_pvt.g_miss_quota_assign_rec_tb;
181 TYPE v_rate_dim_exp_rec IS RECORD
182 (min_exp_name cn_calc_sql_exps.name%TYPE,
183 max_exp_name cn_calc_sql_exps.name%TYPE);
184 TYPE v_rate_dim_exp_tbl IS TABLE OF v_rate_dim_exp_rec INDEX BY BINARY_INTEGER;
185 g_miss_rate_dim_exp_tbl v_rate_dim_exp_tbl;
186 TYPE v_calc_edges_rec IS RECORD(
187 calc_edge_id cn_calc_edges.calc_edge_id%TYPE := NULL,
188 edge_type cn_calc_edges.edge_type%TYPE,
189 parent_id cn_calc_edges.parent_id%TYPE,
190 child_id cn_calc_edges.child_id%TYPE,
191 parent_name cn_calc_sql_exps.name%TYPE,
192 child_name cn_calc_formulas.name%TYPE);
193 TYPE v_calc_edges_tbl IS TABLE OF v_calc_edges_rec INDEX BY BINARY_INTEGER;
194 g_miss_calc_edges_tbl v_calc_edges_tbl;
195 -- XML Declaration
196 v_doc dbms_xmldom.DOMDocument;
197 v_node dbms_xmldom.DOMNode;
198 v_parent_node dbms_xmldom.DOMNode;
199 v_parent_node_list dbms_xmldom.DOMNodeList;
200 v_parent_node_length NUMBER;
201 v_child_node dbms_xmldom.DOMNode;
202 v_child_node_name VARCHAR2(30);
203 v_node_first_child dbms_xmldom.DOMNode;
204 v_child_node_element dbms_xmldom.DOMElement;
205 v_element_cast dbms_xmldom.DOMElement;
206 v_name_node dbms_xmldom.DOMNodeList;
207 v_name_node_value VARCHAR2(80);
208 v_name_node_value_new VARCHAR2(80);
209 v_node_sibling_Next dbms_xmldom.DOMNode;
210 v_node_sibling_child_Next dbms_xmldom.DOMNode;
211 v_node_sibling_list_Next dbms_xmldom.DOMNodeList;
212 v_node_sibling_name_Next VARCHAR2(30);
213 v_node_sibling_length_Next NUMBER;
214 v_element_sibling_cast_Next dbms_xmldom.DOMElement;
215 v_node_sibling_Previous dbms_xmldom.DOMNode;
216 --Other Declaration
217 l_api_version NUMBER := 1.0;
218 l_api_name CONSTANT VARCHAR2(30) := 'Parse_XML';
219 l_rate_dimension_id cn_rate_dimensions.rate_dimension_id%TYPE;
220 l_rate_dim_tier_id cn_rate_dim_tiers.rate_dim_tier_id%TYPE;
221 l_rate_schedule_id cn_rate_schedules.rate_schedule_id%TYPE;
222 l_calc_formula_id cn_calc_formulas.calc_formula_id%TYPE;
223 l_calc_sql_exp_id cn_calc_sql_exps.calc_sql_exp_id%TYPE;
224 l_output_exp_name cn_calc_sql_exps.name%TYPE;
225 l_f_output_exp_name cn_calc_sql_exps.name%TYPE;
226 l_perf_measure_name cn_calc_sql_exps.name%TYPE;
227 l_comp_plan_id cn_comp_plans.comp_plan_id%TYPE;
228 l_quota_assign_id cn_quota_assigns.quota_assign_id%TYPE;
229 l_pmt_group_code NUMBER;
230 l_rev_class_name cn_revenue_classes.name%TYPE;
231 l_uplift_start_date DATE;
232 l_uplift_end_date DATE;
233
234 l_reuse_count NUMBER;
235 l_sql_fail_count NUMBER;
236 l_formula_name_count NUMBER;
237 l_exp_name_count NUMBER;
238 l_rev_class_name_count NUMBER;
239 l_crd_type_count NUMBER;
240 l_int_type_count NUMBER;
241 l_rate_schedule_name_count NUMBER;
242 l_rate_dim_name_count NUMBER;
243 l_pe_name_count NUMBER;
244 l_quota_asgn_count NUMBER;
245 l_rev_class_least_count NUMBER;
246 l_rt_fm_notexist_count NUMBER;
247
248 l_child_id cn_calc_formulas.calc_formula_id%TYPE;
249 l_child_name cn_calc_formulas.name%TYPE;
250 l_parent_name cn_calc_sql_exps.name%TYPE;
251 l_formula_pkg_source VARCHAR2(30);
252 l_formula_pkg_target VARCHAR2(30);
253 l_source_org_id NUMBER;
254 l_open NUMBER;
255 l_close NUMBER;
256 l_open_sql NUMBER;
257 l_close_sql NUMBER;
258 l_pe_mtrc_p_sql VARCHAR2(30);
259 l_pe_p_sql VARCHAR2(30);
260 l_open_p_sql NUMBER;
261 l_close_p_sql NUMBER;
262 l_quota_id cn_quotas.quota_id%TYPE;
263 l_failed_plan_name VARCHAR2(2000);
264 l_formula_name_source cn_calc_formulas.name%TYPE;
265 l_pe_source_name cn_quotas.name%TYPE;
266 l_pe_num NUMBER := 0;
267 l_pe_count NUMBER;
268 l_ee_count NUMBER;
269 l_pe_mtrc VARCHAR2(30);
270 l_pe VARCHAR2(30);
271 l_pe_mtrc_sql VARCHAR2(30);
272 l_pe_sql VARCHAR2(30);
273 l_pe_exist BOOLEAN := TRUE;
274 l_ee_tab_name VARCHAR2(100);
275 l_ee_tab_name_new VARCHAr2(100);
276 l_ee_alias NUMBER;
277 l_ee_exist_obj_check NUMBER;
278 x_loading_status VARCHAR2(30);
279 x_object_version_number NUMBER := 0;
280 p_success_obj_count NUMBER := 0;
281 p_reuse_obj_count NUMBER := 0;
282 x_return_status VARCHAR2(1);
283 x_msg_count NUMBER;
284 x_msg_data VARCHAR2(240);
285 l_expense_acc_desc VARCHAR2(100);
286 l_liability_acc_desc VARCHAR2(100);
287 l_liab_account_id NUMBER;
288 l_expense_account_id NUMBER;
289 l_ee_piped_sql_from CLOB;
290
291 l_period_name cn_period_statuses.period_name%TYPE;
292 l_period_exist_count NUMBER;
293 l_period_end_date DATE;
294 l_exp_name_source cn_calc_sql_exps.name%TYPE;
295 l_period_id cn_period_statuses.period_id%TYPE;
296 --defined by Naren to fix bug 13606519
297
298 l_quota_count NUMBER:=0;
299
300 TYPE v_pe_exp_rec IS RECORD
301 (old_pe_name cn_quotas.name%TYPE,
302 new_pe_name cn_quotas.name%TYPE,
303 old_pe_id cn_quotas.quota_id%TYPE,
304 new_pe_id cn_quotas.quota_id%TYPE);
305 TYPE v_pe_exp_tbl IS TABLE OF v_pe_exp_rec INDEX BY BINARY_INTEGER;
306 g_miss_pe_exp_rec v_pe_exp_rec;
307 g_miss_pe_exp_tbl v_pe_exp_tbl;
308 l_new_pe_name cn_quotas.name%TYPE;
309 l_pe_counter NUMBER;
310
311 TYPE v_pe_collection IS TABLE OF NUMBER;
312 v_pe_tbl v_pe_collection;
313
314 CURSOR c_expense_account_id (p_exp_acc_desc VARCHAR2) IS
315 SELECT code_combination_id
316 FROM gl_sets_of_books glb, cn_repositories r, gl_code_combinations glc
317 WHERE account_type = 'E'
318 AND glb.chart_of_accounts_id = glc.chart_of_accounts_id
319 AND r.set_of_books_id = glb.set_of_books_id
320 AND SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = p_exp_acc_desc
321 AND r.org_id = p_org_id;
322
323 CURSOR c_liab_account_id (p_liab_acc_desc VARCHAR2) IS
324 SELECT code_combination_id
325 FROM gl_sets_of_books glb, cn_repositories r, gl_code_combinations glc
326 WHERE account_type = 'L'
327 AND glb.chart_of_accounts_id = glc.chart_of_accounts_id
328 AND r.set_of_books_id = glb.set_of_books_id
329 AND SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = p_liab_acc_desc
330 AND r.org_id = p_org_id;
331
332 BEGIN
333 /**********************************************************************/
334 /* Standard API Checks */
335 /**********************************************************************/
336 -- Standard Start of API savepoint
337 -- SAVEPOINT Parse_XML;
338 -- Standard call to check for call compatibility.
339 IF NOT fnd_api.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME )THEN
340 RAISE fnd_api.g_exc_unexpected_error;
341 END IF;
342
343 -- Initialize message list if p_init_msg_list is set to TRUE.
344 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
345 fnd_msg_pub.initialize;
346 END IF;
347
348 -- Initialize the Import Status to 'FAILED'
349 x_import_status := 'FAILED';
350 /**********************************************************************/
351 /* API Body - Start */
352 /**********************************************************************/
353 -- Create DOMDocument handle:
354 v_doc := dbms_xmldom.newDOMDocument(p_xml);
355 -- Create node from DOMDocument handle:
356 v_node := dbms_xmldom.makeNode(v_doc);
357 -- Get First Child (Parent Node) of the node
358 v_parent_node := dbms_xmldom.getFirstChild(v_node);
359 -- Get the length of parent node
360 v_parent_node_length := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_parent_node));
361 -- Plan element Counter for Interdependent PE check in Expression
362 l_pe_counter := 0;
363
364 IF v_parent_node_length > 0 THEN
365 v_parent_node_list := dbms_xmldom.getChildNodes(v_parent_node);
366 FOR i IN 0..v_parent_node_length-1 LOOP
367 -- All Counters and Checks initialization
368 l_sql_fail_count := 0;
369 l_reuse_count := 0;
370 l_pe_num := 1;
371 v_pe_tbl:= v_pe_collection();
372
373 -- Loop through all the child nodes of OIC_PLAN_COPY Node
374 v_child_node := dbms_xmldom.item(v_parent_node_list,i);
375 v_child_node_name := dbms_xmldom.getNodeName(dbms_xmldom.item(v_parent_node_list,i));
376
377 /* ****************************** Main Loop Start ************************ */
378
379 --*********************************************************************
380 --********************** Parse Expression ***********************
381 --*********************************************************************
382 IF v_child_node_name = 'CnCalcSqlExpsVO' THEN
383 -- Rollback SavePoint
384 SAVEPOINT Create_Expression;
385 -- Intialising Rate Table record
386 v_expression_rec := NULL;
387 -- Get the CnCalcSqlExpsVORow
388 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
389 -- Cast Node to Element
390 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
391 -- Get the Expression Name
392 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
393 -- Get the Expression Name Value
394 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
395 -- Attach prefix to the Name Value
396 -- v_name_node_value_new := p_prefix || v_name_node_value;
397
398 -- Call common utility package for name length check
399 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
400 p_name => v_name_node_value,
401 p_org_id => p_org_id,
402 p_type => 'EXPRESSION',
403 p_prefix => p_prefix);
404
405 -- Check if Expression already exists in the Target Instance
406 SELECT COUNT(name) INTO l_reuse_count
407 FROM cn_calc_sql_exps
408 WHERE name = v_name_node_value_new
409 AND org_id = p_org_id;
410
411 --Additional condition to check the expressioin exist in the target is valid or not
412
413 IF l_reuse_count>0 THEN
414
415 SELECT status INTO v_expression_rec.status
416 FROM cn_calc_sql_exps
417 WHERE name=v_name_node_value_new
418 AND org_id=p_org_id;
419
420 IF v_expression_rec.status <>'VALID' THEN
421
422 fnd_message.set_name ('CN' , 'Expression exist in the target, in INVALID status');
423 fnd_message.set_token('EXPRESSION_NAME',v_name_node_value_new);
424 fnd_file.put_line(fnd_file.log, fnd_message.get);
425 l_sql_fail_count:=1;
426 ROLLBACK TO Create_Expression;
427 EXIT;
428
429 END IF; --end of v_expression_rec.status='VALID'
430
431 END IF; --end of checking whether expression is valid or not
432
433 -- Get the other Expression values
434 l_source_org_id := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'OrgId'),0)));
435 v_expression_rec.org_id := p_org_id;
436 v_expression_rec.name := v_name_node_value_new;
437 v_expression_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
438 v_expression_rec.status := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Status'),0)));
439
440 v_expression_rec.exp_type_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ExpTypeCode'),0)));
441
442 v_expression_rec.expression_disp := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ExpressionDisp'),0)));
443
444 v_expression_rec.sql_select := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SqlSelect'),0)));
445 v_expression_rec.sql_from := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SqlFrom'),0)));
446 v_expression_rec.piped_sql_select := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedSqlSelect'),0)));
447 v_expression_rec.piped_sql_from := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedSqlFrom'),0)));
448 v_expression_rec.piped_expression_disp := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedExpressionDisp'),0)));
449
450 --*********************************************************************
451 -- Parse Formula or/and Expression in Expression - Calc Edges
452 --*********************************************************************
453 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
454 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
455 IF v_node_sibling_name_Next = 'CnCalcEdgesVO' THEN
456 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
457 IF v_node_sibling_length_Next > 0 THEN
458 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
459 -- Clearing the Temporary Table
460 g_miss_calc_edges_tbl.DELETE;
461 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
462 -- Loop through all the child nodes of CnRateDimTiers Node
463 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
464 -- Cast Node to Element
465 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
466 -- Get Calc Edges Information
467 l_formula_pkg_source := NULL;
468 l_formula_pkg_target := NULL;
469 l_formula_name_source := NULL;
470 g_miss_calc_edges_tbl(i).child_name
471 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ChildName'),0)));
472 g_miss_calc_edges_tbl(i).edge_type
473 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EdgeType'),0)));
474 g_miss_calc_edges_tbl(i).parent_name
475 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ParentName'),0)));
476 -- Call common utility package for name length check
477 g_miss_calc_edges_tbl(i).parent_name := cn_plancopy_util_pvt.check_name_length(
478 p_name => g_miss_calc_edges_tbl(i).parent_name,
479 p_org_id => p_org_id,
480 p_type => 'EXPRESSION',
481 p_prefix => p_prefix);
482
483 ----------------------------------------------
484 -- Step1: Check if Expression contains Formula
485 ----------------------------------------------
486 IF g_miss_calc_edges_tbl(i).edge_type = 'FE' THEN
487 -- Storing old name of the formula in source system
488 l_formula_name_source := g_miss_calc_edges_tbl(i).child_name;
489 -- Call common utility package for name length check
490 g_miss_calc_edges_tbl(i).child_name := cn_plancopy_util_pvt.check_name_length(
491 p_name => g_miss_calc_edges_tbl(i).child_name,
492 p_org_id => p_org_id,
493 p_type => 'FORMULA',
494 p_prefix => p_prefix);
495
496 g_miss_calc_edges_tbl(i).child_id
497 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ChildId'),0)));
498
499 -- Formula Package Information
500 l_formula_pkg_source := 'cn_formula_'||g_miss_calc_edges_tbl(i).child_id||'_'||l_source_org_id||'_pkg';
501
502 -- Get Formula Information
503 IF g_miss_calc_edges_tbl(i).parent_name = v_expression_rec.name THEN
504 SELECT COUNT(name) INTO l_formula_name_count
505 FROM cn_calc_formulas
506 WHERE name = g_miss_calc_edges_tbl(i).child_name
507 AND org_id = p_org_id;
508 IF l_formula_name_count = 0 THEN
509 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_MISS');
510 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
511 fnd_message.set_token('FORMULA_NAME',g_miss_calc_edges_tbl(i).child_name);
512 fnd_file.put_line(fnd_file.log, fnd_message.get);
513 l_sql_fail_count := 1;
514 EXIT;
515 ELSE
516 SELECT calc_formula_id INTO g_miss_calc_edges_tbl(i).child_id
517 FROM cn_calc_formulas
518 WHERE name = g_miss_calc_edges_tbl(i).child_name
519 AND org_id = p_org_id;
520 END IF;
521 l_formula_pkg_target := 'cn_formula_'||g_miss_calc_edges_tbl(i).child_id||'_'||p_org_id||'_pkg';
522 v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,l_formula_pkg_source,l_formula_pkg_target);
523 v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select,l_formula_pkg_source,l_formula_pkg_target);
524 v_expression_rec.expression_disp := REPLACE(v_expression_rec.expression_disp,l_formula_name_source,g_miss_calc_edges_tbl(i).child_name);
525 v_expression_rec.piped_expression_disp := REPLACE(v_expression_rec.piped_expression_disp,l_formula_name_source,g_miss_calc_edges_tbl(i).child_name);
526 END IF;
527 END IF;
528 ---------------------------------------------------------
529 -- Step2: Check if Expression contains another Expression
530 ---------------------------------------------------------
531 IF g_miss_calc_edges_tbl(i).edge_type = 'EE' THEN
532 -- Storing old name of the formula in source system
533 l_exp_name_source := g_miss_calc_edges_tbl(i).child_name;
534 -- Call common utility package for name length check
535 g_miss_calc_edges_tbl(i).child_name := cn_plancopy_util_pvt.check_name_length(
536 p_name => g_miss_calc_edges_tbl(i).child_name,
537 p_org_id => p_org_id,
538 p_type => 'EXPRESSION',
539 p_prefix => p_prefix);
540 g_miss_calc_edges_tbl(i).child_id
541 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ChildId'),0)));
542 -- Get Formula Information
543 IF g_miss_calc_edges_tbl(i).parent_name = v_expression_rec.name THEN
544 SELECT COUNT(name) INTO l_exp_name_count
545 FROM cn_calc_sql_exps
546 WHERE name = g_miss_calc_edges_tbl(i).child_name
547 AND org_id = p_org_id;
548 IF l_exp_name_count = 0 THEN
549 fnd_message.set_name ('CN' , 'CN_COPY_EXP_EXP_MISS');
550 fnd_message.set_token('EXPRESSION_NAME_1',v_expression_rec.name);
551 fnd_message.set_token('EXPRESSION_NAME_2',g_miss_calc_edges_tbl(i).child_name);
552 fnd_file.put_line(fnd_file.log, fnd_message.get);
553 l_sql_fail_count := 1;
554 EXIT;
555 ELSE
556 SELECT calc_sql_exp_id INTO g_miss_calc_edges_tbl(i).child_id
557 FROM cn_calc_sql_exps
558 WHERE name = g_miss_calc_edges_tbl(i).child_name
559 AND org_id = p_org_id;
560 END IF;
561 v_expression_rec.expression_disp := REPLACE(v_expression_rec.expression_disp,l_exp_name_source,g_miss_calc_edges_tbl(i).child_name);
562 v_expression_rec.piped_expression_disp := REPLACE(v_expression_rec.piped_expression_disp,l_exp_name_source,g_miss_calc_edges_tbl(i).child_name);
563 END IF;
564 END IF;
565 END LOOP;
566 END IF;
567 END IF;
568
569 IF l_sql_fail_count = 0 THEN
570 --*********************************************************************
571 -- Parse Plan Element in Expression
572 --*********************************************************************
573 l_pe_count := instr(v_expression_rec.piped_sql_select, 'PE.',1);
574
575 IF l_pe_count > 0 THEN
576
577 IF g_miss_pe_exp_tbl.COUNT > 0 THEN
578
579 FOR i IN g_miss_pe_exp_tbl.FIRST..g_miss_pe_exp_tbl.LAST LOOP
580
581 IF INSTR(v_expression_rec.piped_sql_select,g_miss_pe_exp_tbl(i).old_pe_id,1) >0 THEN
582
583 v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.','[A'|| l_pe_num ||']PE.');
584
585 v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.','[A'|| l_pe_num ||']PE.');
586
587 v_expression_rec.expression_disp := REPLACE(v_expression_rec.expression_disp,
588 g_miss_pe_exp_tbl(i).old_pe_name,g_miss_pe_exp_tbl(i).new_pe_name);
589
590 v_expression_rec.piped_expression_disp := REPLACE(v_expression_rec.piped_expression_disp,
591 g_miss_pe_exp_tbl(i).old_pe_name,g_miss_pe_exp_tbl(i).new_pe_name);
592
593 v_pe_tbl.EXTEND;
594
595 v_pe_tbl(l_pe_num) := g_miss_pe_exp_tbl(i).new_pe_id;
596 l_pe_num:=l_pe_num+1;
597
598 END IF; --end of IF
599
600
601 END LOOP; --end of for loop
602
603 IF v_pe_tbl.COUNT >0 THEN
604
605 FOR i IN v_pe_tbl.FIRST..v_pe_tbl.LAST LOOP
606
607 v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,'[A'|| i ||']PE.', v_pe_tbl(i) ||'PE.');
608
609 v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select, '[A'|| i ||']PE.', v_pe_tbl(i) ||'PE.');
610
611 END LOOP;
612
613 END IF; --end of v_pe_tbl.COUNT
614
615 --clear temporary table
616 v_pe_tbl.DELETE;
617
618
619 ELSE
620
621 l_sql_fail_count := 1;
622 EXIT;
623
624 END IF; -- end of g_miss_pe_exp_tbl.COUNT
625
626 END IF;-- end of l_pe_count
627
628
629 --*********************************************************************
630 -- Parse External Element in Expression
631 --*********************************************************************
632 l_ee_count := 0;
633 l_ee_piped_sql_from := v_expression_rec.piped_sql_from;
634 IF l_ee_piped_sql_from IS NOT NULL THEN
635 LOOP
636 l_ee_tab_name := SUBSTR(l_ee_piped_sql_from,1,INSTR(l_ee_piped_sql_from, '|')-1);
637 IF l_ee_tab_name <> 'DUAL' THEN
638 -- Check if Alias exists
639 l_ee_alias := instr(l_ee_tab_name, ' ',1);
640 IF l_ee_alias > 0 THEN
641 l_ee_tab_name_new := SUBSTR(l_ee_piped_sql_from,1,INSTR(l_ee_piped_sql_from, ' ')-1);
642 ELSE
643 l_ee_tab_name_new := l_ee_tab_name;
644 END IF;
645 -- Check object exists in Target System
646 SELECT COUNT(name) INTO l_ee_exist_obj_check
647 FROM cn_objects
648 WHERE org_id = p_org_id
649 AND calc_eligible_flag = 'Y'
650 AND object_type = 'TBL'
651 AND name = l_ee_tab_name_new;
652 -- Error Message - If table does not exist in Target System
653 IF l_ee_exist_obj_check = 0 THEN
654 fnd_message.set_name ('CN' , 'CN_COPY_EXP_EXT_MAP_MISS');
655 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
656 fnd_file.put_line(fnd_file.log, fnd_message.get);
657 l_sql_fail_count := 1;
658 EXIT;
659 END IF;
660 END IF;
661 -- Remove the table name which is checked and pick the next table
662 l_ee_piped_sql_from := REPLACE(l_ee_piped_sql_from,l_ee_tab_name||'|','');
663 l_ee_count := INSTR(l_ee_piped_sql_from, '|');
664 IF l_ee_count = 0 THEN
665 EXIT;
666 END IF;
667 END LOOP;
668 END IF;
669
670 IF l_sql_fail_count = 0 THEN
671
672 IF l_reuse_count >0 THEN
673
674 l_calc_sql_exp_id := NULL;
675 x_object_version_number:=0;
676
677 SELECT calc_sql_exp_id,object_version_number INTO l_calc_sql_exp_id, x_object_version_number
678 FROM cn_calc_sql_exps
679 WHERE name = v_name_node_value_new
680 AND org_id = p_org_id;
681
682 CN_PLANCOPY_UTIL_PVT.update_existing_expression(
683 p_api_version => p_api_version
684 , p_init_msg_list => p_init_msg_list
685 , p_commit => p_commit
686 , p_validation_level => p_validation_level
687 , p_update_parent_also => fnd_api.g_false
688 , p_org_id => p_org_id
689 , p_calc_sql_exp_id => l_calc_sql_exp_id
690 , p_name => v_expression_rec.name
691 , p_description => v_expression_rec.description
692 , p_expression_disp => v_expression_rec.expression_disp
693 , -- CLOBs
694 p_sql_select => v_expression_rec.sql_select
695 , p_sql_from => v_expression_rec.sql_from
696 , p_piped_expression_disp => v_expression_rec.piped_expression_disp
697 , p_piped_sql_select => v_expression_rec.piped_sql_select
698 , p_piped_sql_from => v_expression_rec.piped_sql_from
699 , p_ovn => x_object_version_number
700 , p_exp_type_code => v_expression_rec.exp_type_code
701 , p_status => v_expression_rec.status
702 , x_return_status => x_return_status
703 , x_msg_count => x_msg_count
704 , x_msg_data => x_msg_data
705 );
706
707 IF x_return_status = fnd_api.g_ret_sts_success THEN
708 fnd_message.set_name ('CN' , 'CN_COPY_EXP_REUSE');
709 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
710 fnd_file.put_line(fnd_file.log, fnd_message.get);
711 COMMIT;
712 ELSE
713 ROLLBACK TO Create_Expression;
714 IF x_return_status = fnd_api.g_ret_sts_error THEN
715 fnd_message.set_name ('CN' , 'Update of existing expression Failed with return status error');
716 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
717 fnd_file.put_line(fnd_file.log, fnd_message.get);
718 fnd_file.put_line(fnd_file.log, '***ERROR STATUS: '||x_msg_data);
719 END IF;
720 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
721 fnd_message.set_name ('CN' , 'Update of existing expression failed with unexpected error');
722 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
723 fnd_file.put_line(fnd_file.log, fnd_message.get);
724 fnd_file.put_line(fnd_file.log, '***UNEXPECTED ERROR: '||x_msg_data);
725 END IF;
726 END IF; --end of x_return_status
727
728
729 ELSE
730
731 --*********************************************************************
732 -- Import Expression
733 --*********************************************************************
734 l_calc_sql_exp_id := NULL;
735 cn_calc_sql_exps_pvt.create_expression(
736 p_api_version => p_api_version,
737 p_init_msg_list => p_init_msg_list,
738 p_commit => p_commit,
739 p_validation_level => p_validation_level,
740 p_org_id => p_org_id,
741 p_name => v_expression_rec.name,
742 p_description => v_expression_rec.description,
743 p_expression_disp => v_expression_rec.expression_disp,
744 p_sql_select => v_expression_rec.sql_select,
745 p_sql_from => v_expression_rec.sql_from,
746 p_piped_expression_disp => v_expression_rec.piped_expression_disp,
747 p_piped_sql_select => v_expression_rec.piped_sql_select,
748 p_piped_sql_from => v_expression_rec.piped_sql_from,
749 x_calc_sql_exp_id => l_calc_sql_exp_id,
750 x_exp_type_code => v_expression_rec.exp_type_code,
751 x_status => v_expression_rec.status,
752 x_return_status => x_return_status,
753 x_msg_count => x_msg_count,
754 x_msg_data => x_msg_data,
755 x_object_version_number => x_object_version_number);
756 IF x_return_status = fnd_api.g_ret_sts_success THEN
757 fnd_message.set_name ('CN' , 'CN_COPY_EXP_CREATE');
758 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
759 fnd_file.put_line(fnd_file.log, fnd_message.get);
760 COMMIT;
761 ELSE
762 ROLLBACK TO Create_Expression;
763 IF x_return_status = fnd_api.g_ret_sts_error THEN
764 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL_EXPECTED');
765 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
766 fnd_file.put_line(fnd_file.log, fnd_message.get);
767 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
768 END IF;
769 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
770 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL');
771 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
772 fnd_file.put_line(fnd_file.log, fnd_message.get);
773 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
774 END IF;
775 END IF; --end of x_return_status
776
777 END IF; --end of l_reuse_count
778 ELSE
779 ROLLBACK TO Create_Expression;
780 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL');
781 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
782 fnd_file.put_line(fnd_file.log, fnd_message.get);
783
784
785 END IF; --end of l_sql_fail_count
786 ELSE
787 ROLLBACK TO Create_Expression;
788 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL');
789 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
790 fnd_file.put_line(fnd_file.log, fnd_message.get);
791 END IF;
792 END IF; --end of CnCalcSqlExpsVO
793 -- END IF;
794
795 --*********************************************************************
796 --****************** Parse Rate Dimension ***********************
797 --*********************************************************************
798 IF v_child_node_name = 'CnRateDimensionsVO' THEN
799 -- Rollback SavePoint
800 SAVEPOINT Create_RateDimension;
801 -- Intialising Rate Table record
802 v_rate_dimension_rec := NULL;
803 -- Get the CnRateDimensionsVORow
804 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
805 -- Cast Node to Element
806 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
807 -- Get the Rate Dimension Name
808 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
809 -- Get the Rate Dimension Name Value
810 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
811 -- Attach prefix to the Name Value
812 -- v_name_node_value_new := p_prefix || v_name_node_value;
813
814 -- Call common utility package for name length check
815 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
816 p_name => v_name_node_value,
817 p_org_id => p_org_id,
818 p_type => 'RATEDIMENSION',
819 p_prefix => p_prefix);
820
821 -- Check if Rate Dimension already exists in the Target Instance
822 SELECT COUNT(name) INTO l_reuse_count
823 FROM cn_rate_dimensions
824 WHERE name = v_name_node_value_new
825 AND org_id = p_org_id;
826
827 --If Rate Dimension exists then do not Insert, Else insert a new record.
828 IF l_reuse_count > 0 THEN
829 fnd_message.set_name ('CN' , 'CN_COPY_RD_REUSE');
830 fnd_message.set_token('RATE_DIMENSION_NAME',v_name_node_value_new);
831 fnd_file.put_line(fnd_file.log, fnd_message.get);
832 END IF;
833
834 IF l_reuse_count = 0 THEN
835 -- Get the other Rate Dimension Values
836 v_rate_dimension_rec.org_id := p_org_id;
837 v_rate_dimension_rec.name := v_name_node_value_new;
838 v_rate_dimension_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
839 v_rate_dimension_rec.dim_unit_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'DimUnitCode'),0)));
840 v_rate_dimension_rec.number_tier := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'NumberTier'),0)));
841
842 --*********************************************************************
843 -- Parse Rate Dim Tiers
844 --*********************************************************************
845 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
846 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
847 IF v_node_sibling_name_Next = 'CnRateDimTiersVO' THEN
848 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
849 IF v_node_sibling_length_Next > 0 THEN
850 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
851 -- Clearing the Temporary Table
852 v_rate_dim_tiers_tbl.DELETE;
853 g_miss_rate_dim_exp_tbl.DELETE;
854 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
855 -- Loop through all the child nodes of CnRateDimTiers Node
856 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
857 -- Cast Node to Element
858 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
859 -- Get the Rate Dim Tier Values
860 v_rate_dim_tiers_tbl(i).minimum_amount := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'MinimumAmount'),0)));
861 v_rate_dim_tiers_tbl(i).maximum_amount := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'MaximumAmount'),0)));
862 v_rate_dim_tiers_tbl(i).tier_sequence := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'TierSequence'),0)));
863 v_rate_dim_tiers_tbl(i).string_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StringValue'),0)));
864 g_miss_rate_dim_exp_tbl(i).min_exp_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'MinExpName'),0)));
865 g_miss_rate_dim_exp_tbl(i).max_exp_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'MaxExpName'),0)));
866
867 IF g_miss_rate_dim_exp_tbl(i).min_exp_name IS NOT NULL THEN
868 -- Call common utility package for name length check
869 g_miss_rate_dim_exp_tbl(i).min_exp_name := cn_plancopy_util_pvt.check_name_length(
870 p_name => g_miss_rate_dim_exp_tbl(i).min_exp_name,
871 p_org_id => p_org_id,
872 p_type => 'EXPRESSION',
873 p_prefix => p_prefix);
874 SELECT COUNT(name) INTO l_exp_name_count
875 FROM cn_calc_sql_exps
876 WHERE name = g_miss_rate_dim_exp_tbl(i).min_exp_name
877 AND org_id = p_org_id;
878
879 IF l_exp_name_count = 0 THEN
880 l_sql_fail_count := 1;
881 EXIT;
882 ELSE
883 SELECT calc_sql_exp_id INTO v_rate_dim_tiers_tbl(i).min_exp_id
884 FROM cn_calc_sql_exps
885 WHERE name = g_miss_rate_dim_exp_tbl(i).min_exp_name
886 AND org_id = p_org_id;
887 END IF;
888 END IF;
889 IF g_miss_rate_dim_exp_tbl(i).max_exp_name IS NOT NULL THEN
890 -- Call common utility package for name length check
891 g_miss_rate_dim_exp_tbl(i).max_exp_name := cn_plancopy_util_pvt.check_name_length(
892 p_name => g_miss_rate_dim_exp_tbl(i).max_exp_name,
893 p_org_id => p_org_id,
894 p_type => 'EXPRESSION',
895 p_prefix => p_prefix);
896
897 SELECT COUNT(name) INTO l_exp_name_count
898 FROM cn_calc_sql_exps
899 WHERE name = g_miss_rate_dim_exp_tbl(i).max_exp_name
900 AND org_id = p_org_id;
901 IF l_exp_name_count = 0 THEN
902 l_sql_fail_count := 1;
903 EXIT;
904 ELSE
905 SELECT calc_sql_exp_id INTO v_rate_dim_tiers_tbl(i).max_exp_id
906 FROM cn_calc_sql_exps
907 WHERE name = g_miss_rate_dim_exp_tbl(i).max_exp_name
908 AND org_id = p_org_id;
909 END IF;
910 END IF;
911 END LOOP;
912
913 IF l_sql_fail_count = 0 THEN
914 --*********************************************************************
915 -- Import Rate Dimension and Rate Dim Tiers
916 --*********************************************************************
917 l_rate_dimension_id := NULL;
918 cn_rate_dimensions_pvt.create_dimension(
919 p_api_version => p_api_version,
920 p_init_msg_list => p_init_msg_list,
921 p_commit => p_commit,
922 p_validation_level => p_validation_level,
923 p_name => v_rate_dimension_rec.name,
924 p_description => v_rate_dimension_rec.description,
925 p_dim_unit_code => v_rate_dimension_rec.dim_unit_code,
926 p_number_tier => v_rate_dimension_rec.number_tier,
927 p_tiers_tbl => v_rate_dim_tiers_tbl,
928 p_org_id => p_org_id,
929 x_rate_dimension_id => l_rate_dimension_id,
930 x_return_status => x_return_status,
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data);
933 IF x_return_status = fnd_api.g_ret_sts_success THEN
934 fnd_message.set_name ('CN' , 'CN_COPY_RD_CREATE');
935 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
936 fnd_file.put_line(fnd_file.log, fnd_message.get);
937 IF (g_miss_rate_dim_exp_tbl.COUNT > 0 AND v_rate_dimension_rec.dim_unit_code = 'EXPRESSION') THEN
938 FOR i IN g_miss_rate_dim_exp_tbl.FIRST..g_miss_rate_dim_exp_tbl.LAST LOOP
939 fnd_message.set_name ('CN' , 'CN_COPY_EXP_RD_ASSIGN');
940 fnd_message.set_token('EXPRESION_NAME',g_miss_rate_dim_exp_tbl(i).min_exp_name);
941 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
942 fnd_file.put_line(fnd_file.log, fnd_message.get);
943 fnd_message.set_name ('CN' , 'CN_COPY_EXP_RD_ASSIGN');
944 fnd_message.set_token('EXPRESION_NAME',g_miss_rate_dim_exp_tbl(i).max_exp_name);
945 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
946 fnd_file.put_line(fnd_file.log, fnd_message.get);
947 END LOOP;
948 END IF;
949 COMMIT;
950 ELSE
951 ROLLBACK TO Create_RateDimension;
952 IF x_return_status = fnd_api.g_ret_sts_error THEN
953 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL_EXPECTED');
954 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
955 fnd_file.put_line(fnd_file.log, fnd_message.get);
956 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
957 END IF;
958 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
959 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
960 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
961 fnd_file.put_line(fnd_file.log, fnd_message.get);
962 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
963 END IF;
964 END IF;
965 ELSE
966 ROLLBACK TO Create_RateDimension;
967 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
968 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
969 fnd_file.put_line(fnd_file.log, fnd_message.get);
970 END IF;
971 ELSE
972 ROLLBACK TO Create_RateDimension;
973 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
974 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
975 fnd_file.put_line(fnd_file.log, fnd_message.get);
976 END IF;
977 ELSE
978 ROLLBACK TO Create_RateDimension;
979 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
980 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
981 fnd_file.put_line(fnd_file.log, fnd_message.get);
982 END IF;
983 END IF;
984 END IF;
985
986 --*********************************************************************
987 --*************** Parse Rate Table - Rate Schedule **************
988 --*********************************************************************
989 IF v_child_node_name = 'CnRateSchedulesVO' THEN
990 -- Rollback SavePoint
991 SAVEPOINT Create_RateSchedule;
992 -- Intialising Rate Table record
993 v_rate_table_rec := NULL;
994 -- Get the CnRateSchedulesVORow
995 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
996 -- Cast Node to Element
997 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
998 -- Get the Rate Table Name
999 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
1000 -- Get the Rate Table Name Value
1001 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
1002
1003 -- Attach prefix to the Name Value
1004 -- Call common utility package for name length check
1005 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
1006 p_name => v_name_node_value,
1007 p_org_id => p_org_id,
1008 p_type => 'RATETABLE',
1009 p_prefix => p_prefix);
1010
1011 -- Check if Rate Table already exists in the Target Instance
1012 SELECT COUNT(name) INTO l_reuse_count
1013 FROM cn_rate_schedules
1014 WHERE name = v_name_node_value_new
1015 AND org_id = p_org_id;
1016
1017 --If Rate Table exists then do not Insert otherwise insert a new Record.
1018 IF l_reuse_count > 0 THEN
1019 ROLLBACK TO Create_RateSchedule;
1020 fnd_message.set_name ('CN' , 'CN_COPY_RT_REUSE');
1021 fnd_message.set_token('RATE_TABLE_NAME',v_name_node_value_new);
1022 fnd_file.put_line(fnd_file.log, fnd_message.get);
1023 END IF;
1024
1025 IF l_reuse_count = 0 THEN
1026 -- Get the other Rate Table values
1027 v_rate_table_rec.name := v_name_node_value_new;
1028 v_rate_table_rec.commission_unit_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'CommissionUnitCode'),0)));
1029 v_rate_table_rec.org_id := p_org_id;
1030 v_rate_table_rec.number_dim := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'NumberDim'),0)));
1031 --*********************************************************************
1032 -- Parse Rate Schedule Dims
1033 --*********************************************************************
1034 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
1035 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1036 IF v_node_sibling_name_Next = 'CnRateSchDimsVO' THEN
1037 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1038 IF v_node_sibling_length_Next > 0 THEN
1039 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1040 -- Clearing the Temporary Table
1041 v_rate_sch_dims_tbl.DELETE;
1042 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1043 -- Loop through all the child nodes of CnRateDimTiers Node
1044 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1045 -- Cast Node to Element
1046 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1047 -- Get the Rate Dim Tier Values
1048 v_rate_sch_dims_tbl(i).rate_dim_name
1049 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateDimensionName'),0)));
1050 -- Call common utility package for name length check
1051 v_rate_sch_dims_tbl(i).rate_dim_name := cn_plancopy_util_pvt.check_name_length(
1052 p_name => v_rate_sch_dims_tbl(i).rate_dim_name,
1053 p_org_id => p_org_id,
1054 p_type => 'RATEDIMENSION',
1055 p_prefix => p_prefix);
1056 v_rate_sch_dims_tbl(i).rate_dim_sequence
1057 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateDimSequence'),0)));
1058
1059 SELECT COUNT(name) INTO l_rate_dim_name_count
1060 FROM cn_rate_dimensions
1061 WHERE name = v_rate_sch_dims_tbl(i).rate_dim_name
1062 AND org_id = p_org_id;
1063
1064 IF l_rate_dim_name_count = 0 THEN
1065 l_sql_fail_count := 1;
1066 EXIT;
1067 ELSE
1068 SELECT rate_dimension_id INTO v_rate_sch_dims_tbl(i).rate_dimension_id
1069 FROM cn_rate_dimensions
1070 WHERE name = v_rate_sch_dims_tbl(i).rate_dim_name
1071 AND org_id = p_org_id;
1072 END IF;
1073 END LOOP;
1074
1075 IF l_sql_fail_count = 0 THEN
1076 --*********************************************************************
1077 -- Parse Rate Tiers
1078 --*********************************************************************
1079 -- bug 12736364 fix start - leap frog version 120.36.12010000.3
1080 -- latest version has data model change
1081 -- Clearing the Temporary Table
1082 v_rate_tiers_tbl.DELETE;
1083 -- bug 12736364 fix end
1084
1085 v_node_sibling_Previous := v_node_sibling_Next;
1086 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1087 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1088 IF v_node_sibling_name_Next = 'CnRateTiersVO' THEN
1089 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1090 IF v_node_sibling_length_Next > 0 THEN
1091 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1092
1093 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1094 -- Loop through all the child nodes of CnRateDimTiers Node
1095 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1096 -- Cast Node to Element
1097 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1098 -- Get the Rate Dim Tier Values
1099 v_rate_tiers_tbl(i).p_org_id := p_org_id;
1100 v_rate_tiers_tbl(i).p_commission_amount
1101 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CommissionAmount'),0)));
1102 v_rate_tiers_tbl(i).p_rate_sequence
1103 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateSequence'),0)));
1104 END LOOP;
1105 ELSE
1106 ROLLBACK TO Create_RateSchedule;
1107 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1108 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1109 fnd_file.put_line(fnd_file.log, fnd_message.get);
1110 END IF;
1111 END IF;
1112 --*********************************************************************
1113 -- Import Rate Table - Rate Schedule
1114 --*********************************************************************
1115 l_rate_schedule_id := NULL;
1116 cn_multi_rate_schedules_pvt.create_schedule(
1117 p_api_version => p_api_version,
1118 p_init_msg_list => p_init_msg_list,
1119 p_commit => p_commit,
1120 p_validation_level => p_validation_level,
1121 p_name => v_rate_table_rec.name,
1122 p_commission_unit_code => v_rate_table_rec.commission_unit_code,
1123 p_number_dim => v_rate_table_rec.number_dim,
1124 p_dims_tbl => v_rate_sch_dims_tbl,
1125 p_org_id => p_org_id,
1126 x_rate_schedule_id => l_rate_schedule_id,
1127 x_return_status => x_return_status,
1128 x_msg_count => x_msg_count,
1129 x_msg_data => x_msg_data);
1130 IF x_return_status = fnd_api.g_ret_sts_success THEN
1131 --*********************************************************************
1132 -- Import Rate Tiers
1133 --*********************************************************************
1134 IF (v_rate_tiers_tbl.COUNT > 0) THEN
1135 FOR i IN v_rate_tiers_tbl.FIRST..v_rate_tiers_tbl.LAST LOOP
1136 cn_multi_rate_schedules_pvt.update_rate(
1137 p_rate_schedule_id => l_rate_schedule_id,
1138 p_rate_sequence => v_rate_tiers_tbl(i).p_rate_sequence,
1139 p_commission_amount => v_rate_tiers_tbl(i).p_commission_amount,
1140 p_object_version_number => x_object_version_number,
1141 p_org_id => p_org_id);
1142 END LOOP;
1143 END IF;
1144 fnd_message.set_name ('CN' , 'CN_COPY_RT_CREATE');
1145 fnd_message.set_token('RATE_TABLE_NAME',v_name_node_value_new);
1146 fnd_file.put_line(fnd_file.log, fnd_message.get);
1147 IF (v_rate_sch_dims_tbl.COUNT > 0) THEN
1148 FOR i IN v_rate_sch_dims_tbl.FIRST..v_rate_sch_dims_tbl.LAST LOOP
1149 fnd_message.set_name ('CN' , 'CN_COPY_RD_RT_ASSIGN');
1150 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_sch_dims_tbl(i).rate_dim_name);
1151 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1152 fnd_file.put_line(fnd_file.log, fnd_message.get);
1153 END LOOP;
1154 END IF;
1155 COMMIT;
1156 ELSE
1157 ROLLBACK TO Create_RateDimension;
1158 IF x_return_status = fnd_api.g_ret_sts_error THEN
1159 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL_EXPECTED');
1160 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1161 fnd_file.put_line(fnd_file.log, fnd_message.get);
1162 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1163 END IF;
1164 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1165 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1166 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1167 fnd_file.put_line(fnd_file.log, fnd_message.get);
1168 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1169 END IF;
1170 END IF;
1171 ELSE
1172 ROLLBACK TO Create_RateSchedule;
1173 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1174 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1175 fnd_file.put_line(fnd_file.log, fnd_message.get);
1176 END IF;
1177 ELSE
1178 ROLLBACK TO Create_RateSchedule;
1179 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1180 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1181 fnd_file.put_line(fnd_file.log, fnd_message.get);
1182 END IF;
1183 ELSE
1184 ROLLBACK TO Create_RateSchedule;
1185 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1186 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1187 fnd_file.put_line(fnd_file.log, fnd_message.get);
1188 END IF;
1189 END IF;
1190 END IF;
1191
1192 --*********************************************************************
1193 --************************ Parse Formula ************************
1194 --*********************************************************************
1195 IF v_child_node_name = 'CnCalcFormulasVO' THEN
1196 -- Rollback SavePoint
1197 SAVEPOINT Create_Formula;
1198 -- Intialising formula record
1199 v_formula_rec := NULL;
1200 -- Get the CnCalcFormulasVORow
1201 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
1202 -- Cast Node to Element
1203 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
1204 -- Get the Formula Name
1205 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
1206 -- Get the Formula Name Value
1207 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
1208 -- Attach prefix to the Name Value
1209 -- v_name_node_value_new := p_prefix || v_name_node_value;
1210
1211 -- Call common utility package for name length check
1212 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
1213 p_name => v_name_node_value,
1214 p_org_id => p_org_id,
1215 p_type => 'FORMULA',
1216 p_prefix => p_prefix);
1217
1218 -- Check if Formula already exists in the Target Instance
1219 SELECT COUNT(name) INTO l_reuse_count
1220 FROM cn_calc_formulas
1221 WHERE name = v_name_node_value_new
1222 AND org_id = p_org_id;
1223
1224 --If Formula exists then do not Insert otherwise insert a new Record.
1225 IF l_reuse_count > 0 THEN
1226
1227 SELECT formula_status INTO v_formula_rec.formula_status
1228 FROM cn_calc_formulas
1229 WHERE name=v_name_node_value_new
1230 AND org_id = p_org_id;
1231
1232 IF v_formula_rec.formula_status = 'COMPLETE' THEN
1233 fnd_message.set_name ('CN' , 'CN_COPY_FM_REUSE');
1234 fnd_message.set_token('FORMULA_NAME',v_name_node_value_new);
1235 fnd_file.put_line(fnd_file.log, fnd_message.get);
1236 ELSE
1237 fnd_message.set_name ('CN' , 'Formula Exist in the targe,in INCOMPLETE status');
1238 fnd_message.set_token('FORMULA_NAME',v_name_node_value_new);
1239 fnd_file.put_line(fnd_file.log, fnd_message.get);
1240 ROLLBACK TO Create_Formula;
1241 EXIT;
1242
1243 END IF;
1244
1245 END IF; --end of l_reuse_count>0
1246
1247 IF l_reuse_count = 0 THEN
1248 -- Get the other Formula values
1249 v_formula_rec.org_id := p_org_id;
1250 v_formula_rec.name := v_name_node_value_new;
1251 v_formula_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
1252 v_formula_rec.formula_status := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FormulaStatus'),0)));
1253 v_formula_rec.split_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SplitFlag'),0)));
1254 v_formula_rec.cumulative_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'CumulativeFlag'),0)));
1255 v_formula_rec.itd_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ItdFlag'),0)));
1256 v_formula_rec.trx_group_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'TrxGroupCode'),0)));
1257 v_formula_rec.threshold_all_tier_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ThresholdAllTierFlag'),0)));
1258 v_formula_rec.number_dim := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'NumberDim'),0)));
1259 v_formula_rec.formula_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FormulaType'),0)));
1260 v_formula_rec.modeling_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ModelingFlag'),0)));
1261 l_output_exp_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'OutputExpName'),0)));
1262 l_f_output_exp_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FOutputExpName'),0)));
1263 l_perf_measure_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PerfMeasureName'),0)));
1264
1265 IF l_output_exp_name IS NULL THEN
1266 l_sql_fail_count := 1;
1267 ELSE
1268 -- Call common utility package for name length check
1269 l_output_exp_name := cn_plancopy_util_pvt.check_name_length(
1270 p_name => l_output_exp_name,
1271 p_org_id => p_org_id,
1272 p_type => 'EXPRESSION',
1273 p_prefix => p_prefix);
1274
1275 SELECT COUNT(name) INTO l_exp_name_count
1276 FROM cn_calc_sql_exps
1277 WHERE name = l_output_exp_name
1278 AND org_id = p_org_id;
1279
1280 IF l_exp_name_count = 0 THEN
1281 l_sql_fail_count := 1;
1282 ELSE
1283 SELECT calc_sql_exp_id INTO v_formula_rec.output_exp_id
1284 FROM cn_calc_sql_exps
1285 WHERE name = l_output_exp_name
1286 AND org_id = p_org_id;
1287 END IF;
1288
1289 IF l_f_output_exp_name IS NOT NULL THEN
1290 -- Call common utility package for name length check
1291 l_f_output_exp_name := cn_plancopy_util_pvt.check_name_length(
1292 p_name => l_f_output_exp_name,
1293 p_org_id => p_org_id,
1294 p_type => 'EXPRESSION',
1295 p_prefix => p_prefix);
1296
1297 SELECT COUNT(name) INTO l_exp_name_count
1298 FROM cn_calc_sql_exps
1299 WHERE name = l_f_output_exp_name
1300 AND org_id = p_org_id;
1301 IF l_exp_name_count = 0 THEN
1302 l_sql_fail_count := 1;
1303 ELSE
1304 SELECT calc_sql_exp_id INTO v_formula_rec.f_output_exp_id
1305 FROM cn_calc_sql_exps
1306 WHERE name = l_f_output_exp_name
1307 AND org_id = p_org_id;
1308 END IF;
1309 END IF;
1310
1311 IF l_perf_measure_name IS NOT NULL THEN
1312 -- Call common utility package for name length check
1313 l_perf_measure_name := cn_plancopy_util_pvt.check_name_length(
1314 p_name => l_perf_measure_name,
1315 p_org_id => p_org_id,
1316 p_type => 'EXPRESSION',
1317 p_prefix => p_prefix);
1318
1319 SELECT COUNT(name) INTO l_exp_name_count
1320 FROM cn_calc_sql_exps
1321 WHERE name = l_perf_measure_name
1322 AND org_id = p_org_id;
1323
1324 IF l_exp_name_count = 0 THEN
1325 l_sql_fail_count := 1;
1326 ELSE
1327 SELECT calc_sql_exp_id INTO v_formula_rec.perf_measure_id
1328 FROM cn_calc_sql_exps
1329 WHERE name = l_perf_measure_name
1330 AND org_id = p_org_id;
1331 END IF;
1332 END IF;
1333 END IF;
1334 IF l_sql_fail_count = 0 THEN
1335 --*********************************************************************
1336 -- Parse Formula - Input Expression Assignment
1337 --*********************************************************************
1338 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
1339 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1340 IF v_node_sibling_name_Next = 'CnFormulaInputsVO' THEN
1341 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1342 IF v_node_sibling_length_Next > 0 THEN
1343 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1344 -- Clearing the Temporary Table
1345 v_input_exp_tbl.DELETE;
1346 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1347 -- Loop through all the child nodes of CnRateDimTiers Node
1348 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1349 -- Cast Node to Element
1350 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1351 -- Get the Rate Dim Tier Values
1352 v_input_exp_tbl(i).rate_dim_sequence
1353 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateDimSequence'),0)));
1354 v_input_exp_tbl(i).calc_exp_name
1355 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CalcSqlExpName'),0)));
1356 v_input_exp_tbl(i).calc_exp_status
1357 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CalcSqlExpStatus'),0)));
1358 v_input_exp_tbl(i).f_calc_exp_name
1359 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'FCalcSqlExpName'),0)));
1360 v_input_exp_tbl(i).f_calc_exp_status
1361 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'FCalcSqlExpStatus'),0)));
1362 v_input_exp_tbl(i).cumulative_flag
1363 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CumulativeFlag'),0)));
1364 v_input_exp_tbl(i).split_flag
1365 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'SplitFlag'),0)));
1366
1367 IF v_input_exp_tbl(i).calc_exp_name IS NULL THEN
1368 l_sql_fail_count := 1;
1369 EXIT;
1370 ELSE
1371 -- Call common utility package for name length check
1372 v_input_exp_tbl(i).calc_exp_name := cn_plancopy_util_pvt.check_name_length(
1373 p_name => v_input_exp_tbl(i).calc_exp_name,
1374 p_org_id => p_org_id,
1375 p_type => 'EXPRESSION',
1376 p_prefix => p_prefix);
1377
1378 SELECT COUNT(name) INTO l_exp_name_count
1379 FROM cn_calc_sql_exps
1380 WHERE name = v_input_exp_tbl(i).calc_exp_name
1381 AND org_id = p_org_id;
1382 IF l_exp_name_count = 0 THEN
1383 l_sql_fail_count := 1;
1384 EXIT;
1385 ELSE
1386 SELECT calc_sql_exp_id INTO v_input_exp_tbl(i).calc_sql_exp_id
1387 FROM cn_calc_sql_exps
1388 WHERE name = v_input_exp_tbl(i).calc_exp_name
1389 AND org_id = p_org_id;
1390 END IF;
1391 END IF;
1392
1393 IF v_input_exp_tbl(i).f_calc_exp_name IS NOT NULL THEN
1394 -- Call common utility package for name length check
1395 v_input_exp_tbl(i).f_calc_exp_name := cn_plancopy_util_pvt.check_name_length(
1396 p_name => v_input_exp_tbl(i).f_calc_exp_name,
1397 p_org_id => p_org_id,
1398 p_type => 'EXPRESSION',
1399 p_prefix => p_prefix);
1400
1401 SELECT COUNT(name) INTO l_exp_name_count
1402 FROM cn_calc_sql_exps
1403 WHERE name = v_input_exp_tbl(i).f_calc_exp_name
1404 AND org_id = p_org_id;
1405
1406 IF l_exp_name_count = 0 THEN
1407 l_sql_fail_count := 1;
1408 EXIT;
1409 ELSE
1410 SELECT calc_sql_exp_id INTO v_input_exp_tbl(i).f_calc_sql_exp_id
1411 FROM cn_calc_sql_exps_all
1412 WHERE name = v_input_exp_tbl(i).f_calc_exp_name
1413 AND org_id = p_org_id;
1414 END IF;
1415 END IF;
1416 END LOOP;
1417
1418 IF l_sql_fail_count = 0 THEN
1419 --*********************************************************************
1420 -- Parse Formula - Rate Table Assignment
1421 --*********************************************************************
1422 v_node_sibling_Previous := v_node_sibling_Next;
1423 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1424 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1425 IF v_node_sibling_name_Next = 'CnRtFormulaAsgnsVO' THEN
1426 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1427 IF v_node_sibling_length_Next > 0 THEN
1428 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1429 -- Clearing the Temporary Table
1430 v_rt_assign_tbl.DELETE;
1431 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1432 -- Loop through all the child nodes of CnRateDimTiers Node
1433 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1434 -- Cast Node to Element
1435 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1436 -- Get the Formula Rate Table Values
1437
1438 /*code changes done to fix bug 14364651 */
1439 IF p_start_date IS NULL THEN
1440 v_rt_assign_tbl(i).start_date
1441 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
1442
1443 ELSE
1444 v_rt_assign_tbl(i).start_date:=p_start_date;
1445
1446 END IF;
1447
1448 IF p_start_date IS NULL AND p_end_date IS NULL THEN
1449
1450 v_rt_assign_tbl(i).end_date
1451 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
1452
1453 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
1454
1455 v_rt_assign_tbl(i).end_date:=p_end_date;
1456
1457 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
1458
1459 v_rt_assign_tbl(i).end_date:=NULL;
1460
1461 END IF;
1462 /*code changes done to fix bug 14364651 ends */
1463
1464 v_rt_assign_tbl(i).rate_schedule_name
1465 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleName'),0)));
1466 v_rt_assign_tbl(i).rate_schedule_type
1467 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleType'),0)));
1468
1469 IF v_rt_assign_tbl(i).rate_schedule_name IS NULL THEN
1470 l_sql_fail_count := 1;
1471 EXIT;
1472 ELSE
1473 -- Call common utility package for name length check
1474 v_rt_assign_tbl(i).rate_schedule_name := cn_plancopy_util_pvt.check_name_length(
1475 p_name => v_rt_assign_tbl(i).rate_schedule_name,
1476 p_org_id => p_org_id,
1477 p_type => 'RATETABLE',
1478 p_prefix => p_prefix);
1479
1480 SELECT COUNT(name) INTO l_rate_schedule_name_count
1481 FROM cn_rate_schedules
1482 WHERE name = v_rt_assign_tbl(i).rate_schedule_name
1483 AND org_id = p_org_id;
1484
1485 IF l_rate_schedule_name_count = 0 THEN
1486 l_sql_fail_count := 1;
1487 EXIT;
1488 ELSE
1489 SELECT rate_schedule_id INTO v_rt_assign_tbl(i).rate_schedule_id
1490 FROM cn_rate_schedules
1491 WHERE name = v_rt_assign_tbl(i).rate_schedule_name
1492 AND org_id = p_org_id;
1493 END IF;
1494 END IF;
1495 END LOOP;
1496 ELSE
1497 l_sql_fail_count := 1;
1498 --ROLLBACK TO Create_Formula;
1499 --fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1500 --fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1501 --fnd_file.put_line(fnd_file.log, fnd_message.get);
1502 END IF;
1503 END IF;
1504
1505 IF l_sql_fail_count = 0 THEN
1506 --**********************************************
1507 -- Import Formula
1508 --**********************************************
1509 l_calc_formula_id := Null;
1510 v_formula_rec.calc_formula_id := NULL;
1511 cn_calc_formulas_pvt.create_formula(
1512 p_api_version => p_api_version,
1513 p_init_msg_list => p_init_msg_list,
1514 p_commit => p_commit,
1515 p_validation_level => p_validation_level,
1516 p_generate_packages => FND_API.G_TRUE,
1517 p_name => v_formula_rec.name,
1518 p_description => v_formula_rec.description,
1519 p_formula_type => v_formula_rec.formula_type,
1520 p_trx_group_code => v_formula_rec.trx_group_code,
1521 p_number_dim => v_formula_rec.number_dim,
1522 p_cumulative_flag => v_formula_rec.cumulative_flag,
1523 p_itd_flag => v_formula_rec.itd_flag,
1524 p_split_flag => v_formula_rec.split_flag,
1525 p_threshold_all_tier_flag => v_formula_rec.threshold_all_tier_flag,
1526 p_modeling_flag => v_formula_rec.modeling_flag,
1527 p_perf_measure_id => v_formula_rec.perf_measure_id,
1528 p_output_exp_id => v_formula_rec.output_exp_id,
1529 p_f_output_exp_id => v_formula_rec.f_output_exp_id,
1530 p_input_tbl => v_input_exp_tbl,
1531 p_rt_assign_tbl => v_rt_assign_tbl,
1532 p_org_id => p_org_id,
1533 x_calc_formula_id => l_calc_formula_id,
1534 x_formula_status => v_formula_rec.formula_status,
1535 x_return_status => x_return_status,
1536 x_msg_count => x_msg_count,
1537 x_msg_data => x_msg_data);
1538
1539 IF x_return_status = fnd_api.g_ret_sts_success THEN
1540 fnd_message.set_name ('CN' , 'CN_COPY_FM_CREATE');
1541 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1542 fnd_file.put_line(fnd_file.log, fnd_message.get);
1543 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1544 fnd_message.set_token('EXPRESSION_NAME',l_output_exp_name);
1545 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1546 fnd_file.put_line(fnd_file.log, fnd_message.get);
1547 IF l_f_output_exp_name IS NOT NULL THEN
1548 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1549 fnd_message.set_token('EXPRESSION_NAME',l_f_output_exp_name);
1550 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1551 fnd_file.put_line(fnd_file.log, fnd_message.get);
1552 END IF;
1553 IF l_perf_measure_name IS NOT NULL THEN
1554 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1555 fnd_message.set_token('EXPRESSION_NAME',l_perf_measure_name);
1556 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1557 fnd_file.put_line(fnd_file.log, fnd_message.get);
1558 END IF;
1559 IF (v_input_exp_tbl.COUNT > 0) THEN
1560 FOR i IN v_input_exp_tbl.FIRST..v_input_exp_tbl.LAST LOOP
1561 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1562 fnd_message.set_token('EXPRESSION_NAME',v_input_exp_tbl(i).calc_exp_name);
1563 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1564 fnd_file.put_line(fnd_file.log, fnd_message.get);
1565 IF v_input_exp_tbl(i).f_calc_exp_name IS NOT NULL THEN
1566 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1567 fnd_message.set_token('EXPRESSION_NAME',v_input_exp_tbl(i).f_calc_exp_name);
1568 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1569 fnd_file.put_line(fnd_file.log, fnd_message.get);
1570 END IF;
1571 END LOOP;
1572 END IF;
1573 IF (v_rt_assign_tbl.COUNT > 0) THEN
1574 FOR i IN v_rt_assign_tbl.FIRST..v_rt_assign_tbl.LAST LOOP
1575 fnd_message.set_name ('CN' , 'CN_COPY_RT_FM_ASSIGN');
1576 fnd_message.set_token('RATE_TABLE_NAME',v_rt_assign_tbl(i).rate_schedule_name);
1577 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1578 fnd_message.set_token('ASSIGNMENT_START_DATE', v_rt_assign_tbl(i).start_date);
1579 IF v_rt_assign_tbl(i).end_date IS NOT NULL THEN
1580 fnd_message.set_token('ASSIGNMENT_END_DATE', v_rt_assign_tbl(i).end_date);
1581 ELSE
1582 fnd_message.set_token('ASSIGNMENT_END_DATE', 'NULL');
1583 END IF;
1584 fnd_file.put_line(fnd_file.log, fnd_message.get);
1585 END LOOP;
1586 END IF;
1587 COMMIT;
1588 ELSE
1589 -- No ROLLBACK TO Create_Formula - Generate has a separate COMMIT cycle.
1590 IF x_return_status = fnd_api.g_ret_sts_error THEN
1591 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL_EXPECTED');
1592 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1593 fnd_file.put_line(fnd_file.log, fnd_message.get);
1594 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1595 END IF;
1596 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1597 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1598 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1599 fnd_file.put_line(fnd_file.log, fnd_message.get);
1600 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1601 END IF;
1602 END IF;
1603 ELSE
1604 ROLLBACK TO Create_Formula;
1605 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1606 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1607 fnd_file.put_line(fnd_file.log, fnd_message.get);
1608 END IF;
1609 ELSE
1610 ROLLBACK TO Create_Formula;
1611 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1612 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1613 fnd_file.put_line(fnd_file.log, fnd_message.get);
1614 END IF;
1615 ELSE
1616 ROLLBACK TO Create_Formula;
1617 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1618 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1619 fnd_file.put_line(fnd_file.log, fnd_message.get);
1620 END IF;
1621 ELSE
1622 ROLLBACK TO Create_Formula;
1623 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1624 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1625 fnd_file.put_line(fnd_file.log, fnd_message.get);
1626 END IF;
1627 ELSE
1628 ROLLBACK TO Create_Formula;
1629 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1630 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1631 fnd_file.put_line(fnd_file.log, fnd_message.get);
1632 END IF;
1633 END IF;
1634 END IF;
1635
1636 --*********************************************************************
1637 --********************** Parse Plan Element *********************
1638 --*********************************************************************
1639 IF v_child_node_name = 'CnQuotasVO' THEN
1640 -- Rollback SavePoint
1641 SAVEPOINT Create_PlanElement;
1642 -- Intialising Rate Table record
1643 v_plan_element_rec := NULL;
1644 -- Get the CnQuotasVORow
1645 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
1646 -- Cast Node to Element
1647 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
1648 -- Get the Plan Element Name
1649 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
1650 -- Get the Plan Element Name Value
1651 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
1652 -- Attach prefix to the Name Value
1653 -- Call common utility package for name length check
1654 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
1655 p_name => v_name_node_value,
1656 p_org_id => p_org_id,
1657 p_type => 'PLANELEMENT',
1658 p_prefix => p_prefix);
1659
1660 -- Check if Plan Element already exists in the Target Instance
1661 SELECT COUNT(name) INTO l_reuse_count
1662 FROM cn_quotas_v
1663 WHERE name = v_name_node_value_new
1664 AND org_id = p_org_id;
1665
1666 --If Plan Element exists then do not Insert otherwise insert a new Record.
1667 IF l_reuse_count > 0 THEN
1668
1669
1670 SELECT quota_status INTO v_plan_element_rec.status
1671 FROM cn_quotas_v
1672 WHERE name=v_name_node_value_new
1673 AND org_id=p_org_id;
1674
1675 IF v_plan_element_rec.status = 'COMPLETE' THEN
1676
1677 /*Added by Naren on 29thFeb,2012*/
1678
1679 g_miss_pe_exp_rec.old_pe_name := v_name_node_value; --name will be same as existing name in the target
1680
1681 g_miss_pe_exp_rec.old_pe_id := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaId'),0)));
1682
1683 g_miss_pe_exp_rec.new_pe_name:=v_name_node_value_new; --pe name can change if customer prefix and import
1684
1685 SELECT quota_id INTO g_miss_pe_exp_rec.new_pe_id
1686 FROM cn_quotas_v
1687 WHERE name = v_name_node_value_new;
1688
1689
1690 l_pe_counter := l_pe_counter + 1;
1691 g_miss_pe_exp_tbl(l_pe_counter).old_pe_name := g_miss_pe_exp_rec.old_pe_name;
1692 g_miss_pe_exp_tbl(l_pe_counter).old_pe_id := g_miss_pe_exp_rec.old_pe_id;
1693 g_miss_pe_exp_tbl(l_pe_counter).new_pe_name := g_miss_pe_exp_rec.new_pe_name;
1694 g_miss_pe_exp_tbl(l_pe_counter).new_pe_id := g_miss_pe_exp_rec.new_pe_id;
1695
1696
1697
1698 /*addition ends here by Naren*/
1699
1700 fnd_message.set_name ('CN' , 'CN_COPY_PE_REUSE');
1701 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1702 fnd_file.put_line(fnd_file.log, fnd_message.get);
1703
1704 ELSE
1705
1706 fnd_message.set_name ('CN' , 'Plan element exist in the target, but in INCOMPLETE Status');
1707 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1708 fnd_file.put_line(fnd_file.log, fnd_message.get);
1709 ROLLBACK TO Create_PlanElement;
1710 EXIT;
1711
1712 END IF; --end of v_plan_element_rec.status
1713
1714
1715 END IF; -- end of l_reuse_count
1716
1717 -- If Plan Element does not exist then proceed further.
1718 IF l_reuse_count = 0 THEN
1719 -- Old value of plan element for Interdependent cases
1720 g_miss_pe_exp_rec.old_pe_name := v_name_node_value;
1721 g_miss_pe_exp_rec.old_pe_id := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaId'),0)));
1722 -- Get the other Plan Element values
1723 v_plan_element_rec.quota_id := NULL;
1724 v_plan_element_rec.name := v_name_node_value_new;
1725 v_plan_element_rec.element_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaTypeCode'),0)));
1726 v_plan_element_rec.target := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Target'),0)));
1727 v_plan_element_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
1728 v_plan_element_rec.payment_amount := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PaymentAmount'),0)));
1729 v_plan_element_rec.org_id := p_org_id;
1730 v_plan_element_rec.incentive_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'IncentiveTypeCode'),0)));
1731 v_plan_element_rec.payee_assign_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PayeeAssignFlag'),0)));
1732 v_plan_element_rec.performance_goal := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PerformanceGoal'),0)));
1733 v_plan_element_rec.status := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaStatus'),0)));
1734 v_plan_element_rec.addup_from_rev_class_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'AddupFromRevClassFlag'),0)));
1735 v_plan_element_rec.quota_group_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaGroupCode'),0)));
1736 v_plan_element_rec.payment_group_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PaymentGroupCode'),0)));
1737 v_plan_element_rec.indirect_credit := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'IndirectCredit'),0)));
1738 v_plan_element_rec.calc_formula_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FormulaName'),0)));
1739 v_plan_element_rec.credit_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'CreditTypeName'),0)));
1740 v_plan_element_rec.interval_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'IntervalTypeName'),0)));
1741 -- Other Attributes Start
1742 v_plan_element_rec.package_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PackageName'),0)));
1743 v_plan_element_rec.attribute_category := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'AttributeCategory'),0)));
1744 v_plan_element_rec.attribute1 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute1'),0)));
1745 v_plan_element_rec.attribute2 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute2'),0)));
1746 v_plan_element_rec.attribute3 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute3'),0)));
1747 v_plan_element_rec.attribute4 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute4'),0)));
1748 v_plan_element_rec.attribute5 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute5'),0)));
1749 v_plan_element_rec.attribute6 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute6'),0)));
1750 v_plan_element_rec.attribute7 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute7'),0)));
1751 v_plan_element_rec.attribute8 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute8'),0)));
1752 v_plan_element_rec.attribute9 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute9'),0)));
1753 v_plan_element_rec.attribute10 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute10'),0)));
1754 v_plan_element_rec.attribute11 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute11'),0)));
1755 v_plan_element_rec.attribute12 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute12'),0)));
1756 v_plan_element_rec.attribute13 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute13'),0)));
1757 v_plan_element_rec.attribute14 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute14'),0)));
1758 v_plan_element_rec.attribute15 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute15'),0)));
1759 v_plan_element_rec.rt_sched_custom_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'RtSchedCustomFlag'),0)));
1760 v_plan_element_rec.vesting_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'VestingFlag'),0)));
1761 v_plan_element_rec.period_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PeriodType'),0)));
1762 -- New Column added to cn_quotas table in R12+
1763 v_plan_element_rec.sreps_enddated_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SalesrepsEnddatedFlag'),0)));
1764 -- Liability and Expense Account Information
1765 l_expense_acc_desc := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ExpenseAccountDesc'),0)));
1766 l_liability_acc_desc := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'LiabilityAccountDesc'),0)));
1767 -- Other Attributes End
1768
1769 -- Find Expense Account information in Target System
1770 l_expense_account_id := 0;
1771 IF l_expense_acc_desc IS NOT NULL THEN
1772 OPEN c_expense_account_id (l_expense_acc_desc);
1773 FETCH c_expense_account_id INTO l_expense_account_id;
1774 CLOSE c_expense_account_id;
1775 IF l_expense_account_id IS NULL THEN
1776 v_plan_element_rec.expense_account_id := NULL;
1777 ELSE
1778 v_plan_element_rec.expense_account_id := l_expense_account_id;
1779 END IF;
1780 ELSE
1781 v_plan_element_rec.expense_account_id := NULL;
1782 END IF;
1783
1784 -- Find Liability Account information in Target System
1785 l_liab_account_id := 0;
1786 IF l_liability_acc_desc IS NOT NULL THEN
1787 OPEN c_liab_account_id (l_liability_acc_desc);
1788 FETCH c_liab_account_id INTO l_liab_account_id;
1789 CLOSE c_liab_account_id;
1790 IF l_liab_account_id IS NULL THEN
1791 v_plan_element_rec.liability_account_id := NULL;
1792 ELSE
1793 v_plan_element_rec.liability_account_id := l_liab_account_id;
1794 END IF;
1795 ELSE
1796 v_plan_element_rec.liability_account_id := NULL;
1797 END IF;
1798
1799 -- Check for External Formula
1800 IF v_plan_element_rec.element_type = 'EXTERNAL' AND
1801 v_plan_element_rec.package_name IS NULL THEN
1802 fnd_message.set_name ('CN' , 'CN_COPY_PE_EXT_FM_MISS');
1803 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1804 fnd_file.put_line(fnd_file.log, fnd_message.get);
1805 l_sql_fail_count := 1;
1806 END IF;
1807
1808 -- Check for Formula Name in Target System.
1809 IF v_plan_element_rec.element_type = 'FORMULA' THEN
1810 -- Call common utility package for name length check
1811 v_plan_element_rec.calc_formula_name := cn_plancopy_util_pvt.check_name_length(
1812 p_name => v_plan_element_rec.calc_formula_name,
1813 p_org_id => p_org_id,
1814 p_type => 'FORMULA',
1815 p_prefix => p_prefix);
1816 SELECT COUNT(name) INTO l_formula_name_count
1817 FROM cn_calc_formulas
1818 WHERE name = v_plan_element_rec.calc_formula_name
1819 AND org_id = p_org_id;
1820 IF l_formula_name_count = 0 THEN
1821 l_sql_fail_count := 1;
1822 END IF;
1823 END IF;
1824
1825 -- Check Interval Type Name in Target System
1826 SELECT COUNT(name) INTO l_int_type_count
1827 FROM cn_interval_types
1828 WHERE name = v_plan_element_rec.interval_name
1829 AND org_id = p_org_id;
1830 IF l_int_type_count = 0 THEN
1831 fnd_message.set_name ('CN' , 'CN_COPY_PE_INT_TYPE_MISS');
1832 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1833 fnd_message.set_token('INTERVAL_TYPE_NAME',v_plan_element_rec.interval_name);
1834 fnd_file.put_line(fnd_file.log, fnd_message.get);
1835 l_sql_fail_count := 1;
1836 END IF;
1837
1838 -- Check Credit Type Name in Target System
1839 SELECT COUNT(name) INTO l_crd_type_count
1840 FROM cn_credit_types
1841 WHERE name = v_plan_element_rec.credit_type
1842 AND org_id = p_org_id;
1843 IF l_crd_type_count = 0 THEN
1844 fnd_message.set_name ('CN' , 'CN_COPY_PE_CRD_TYPE_MISS');
1845 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1846 fnd_message.set_token('CREDIT_TYPE_NAME',v_plan_element_rec.credit_type);
1847 fnd_file.put_line(fnd_file.log, fnd_message.get);
1848 l_sql_fail_count := 1;
1849 END IF;
1850
1851 -- If all of the above are NOT NULL then proceed further
1852 IF l_sql_fail_count = 0 THEN
1853 -- Check Payment Group Code in Target System
1854 SELECT COUNT(lookup_code) INTO l_pmt_group_code
1855 FROM cn_lookups
1856 WHERE lookup_type = 'PAYMENT_GROUP_CODE'
1857 AND lookup_code = v_plan_element_rec.payment_group_code;
1858
1859 -- If Payment Group does not exists, Set it to 'STANDARD'
1860 IF l_pmt_group_code = 0 THEN
1861 fnd_message.set_name ('CN' , 'CN_COPY_PE_PMT_GRP_DFLT');
1862 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1863 fnd_message.set_token('PAYMENT_GROUP_CODE_NAME',v_plan_element_rec.payment_group_code);
1864 fnd_file.put_line(fnd_file.log, fnd_message.get);
1865 v_plan_element_rec.payment_group_code := 'STANDARD';
1866 END IF;
1867
1868 -- Check if Start Date and End Date values are passed for change.
1869 IF p_start_date IS NULL THEN
1870 v_plan_element_rec.start_date := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'StartDate'),0))),'YYYY-MM-DD');
1871 ELSE
1872 v_plan_element_rec.start_date := p_start_date;
1873 END IF;
1874
1875 IF p_start_date IS NULL AND p_end_date IS NULL THEN
1876 v_plan_element_rec.end_date := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'EndDate'),0))),'YYYY-MM-DD');
1877 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
1878 v_plan_element_rec.end_date := p_end_date;
1879 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
1880 v_plan_element_rec.end_date := NULL;
1881 END IF;
1882
1883 --*********************************************************************
1884 -- Parse Quota Rules - Revenue Class Assignments
1885 --*********************************************************************
1886 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
1887 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1888
1889 IF v_node_sibling_name_Next = 'CnQuotaRulesVO' THEN
1890
1891 -- Initializing for at least one rev class count
1892 l_rev_class_least_count := 0;
1893 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1894 -- Clearing the Temporary Table
1895 v_revenue_class_tbl.DELETE;
1896 IF v_node_sibling_length_Next > 0 THEN
1897 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1898 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1899 -- Loop through all the child nodes of CnQuotaAssignsVO Node
1900 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1901 -- Cast Node to Element
1902 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1903
1904 -- Find If Revenue Class exists in the Target System
1905 l_rev_class_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClsName'),0)));
1906 SELECT COUNT(name) into l_rev_class_name_count
1907 FROM cn_revenue_classes
1908 WHERE name = l_rev_class_name
1909 AND org_id = p_org_id;
1910
1911 -- Get the Revenue Class Values - Only If Revenue Class exists in the Target System
1912 IF l_rev_class_name_count <> 0 THEN
1913 v_revenue_class_tbl(i).rev_class_name
1914 := l_rev_class_name;
1915 v_revenue_class_tbl(i).rev_class_target
1916 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Target'),0)));
1917 v_revenue_class_tbl(i).rev_class_payment_amount
1918 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PaymentAmount'),0)));
1919 v_revenue_class_tbl(i).rev_class_performance_goal
1920 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PerformanceGoal'),0)));
1921 v_revenue_class_tbl(i).description
1922 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Description'),0)));
1923 v_revenue_class_tbl(i).org_id
1924 := p_org_id;
1925 -- Other Attributes Start
1926 v_revenue_class_tbl(i).attribute_category
1927 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'AttributeCategory'),0)));
1928 v_revenue_class_tbl(i).attribute1
1929 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
1930 v_revenue_class_tbl(i).attribute2
1931 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
1932 v_revenue_class_tbl(i).attribute3
1933 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
1934 v_revenue_class_tbl(i).attribute4
1935 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
1936 v_revenue_class_tbl(i).attribute5
1937 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
1938 v_revenue_class_tbl(i).attribute6
1939 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
1940 v_revenue_class_tbl(i).attribute7
1941 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
1942 v_revenue_class_tbl(i).attribute8
1943 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
1944 v_revenue_class_tbl(i).attribute9
1945 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
1946 v_revenue_class_tbl(i).attribute10
1947 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
1948 v_revenue_class_tbl(i).attribute11
1949 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
1950 v_revenue_class_tbl(i).attribute12
1951 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
1952 v_revenue_class_tbl(i).attribute13
1953 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
1954 v_revenue_class_tbl(i).attribute14
1955 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
1956 v_revenue_class_tbl(i).attribute15
1957 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
1958 v_revenue_class_tbl(i).rev_class_name_old
1959 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClassNameOld'),0)));
1960 -- Other Attributes End
1961
1962 --Check for atleast one revenue class assign to Plan Element
1963 l_rev_class_least_count := 1;
1964 ELSE
1965 fnd_message.set_name ('CN' , 'CN_COPY_PE_REV_CLS_MISS');
1966 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1967 fnd_message.set_token('PRODUCT_NAME',l_rev_class_name);
1968 fnd_file.put_line(fnd_file.log, fnd_message.get);
1969 END IF;
1970 END LOOP;
1971
1972 -- If atleast one revenue class exists then proceed further
1973 IF l_rev_class_least_count = 1 THEN
1974 --*********************************************************************
1975 -- Parse Quota Rule Uplifts
1976 --*********************************************************************
1977 v_node_sibling_Previous := v_node_sibling_Next;
1978 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1979 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1980
1981 -- Clear temporary table v_rev_uplift_tbl because plan element may or may not have product multipliers. Added by Naren to fix --bug 13606519
1982 v_rev_uplift_tbl.DELETE;
1983
1984 --Count the number of multipliers for each revenue class
1985 l_quota_count:=0;
1986
1987 WHILE v_node_sibling_name_Next = 'CnQuotaRuleUpliftsVO' LOOP
1988
1989 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1990 IF v_node_sibling_length_Next > 0 THEN
1991 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1992 /*
1993 commented below code by Naren to fix the bug 13606519
1994 -- Clearing the Temporary Table
1995 v_rev_uplift_tbl.DELETE;
1996 */
1997
1998 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1999 -- Loop through all the child nodes of CnQuotaAssignsVO Node
2000 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2001 -- Cast Node to Element
2002 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2003
2004 -- Find Revenue Class existing in the Target System
2005 /*code changes done to fix bug 14364651 */
2006 IF p_start_date IS NULL THEN
2007
2008 l_uplift_start_date
2009 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
2010
2011
2012 ELSE
2013
2014 l_uplift_start_date:= p_start_date;
2015
2016 END IF;
2017
2018 IF p_start_date IS NULL AND p_end_date IS NULL THEN
2019
2020 l_uplift_end_date
2021 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
2022
2023 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
2024
2025 l_uplift_end_date:=p_end_date;
2026
2027 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
2028
2029 l_uplift_end_date:=NULL;
2030
2031 END IF;
2032
2033 /*code changes done to fix bug 14364651 ends*/
2034
2035 l_rev_class_name
2036 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClsName'),0)));
2037
2038 -- Check for Id
2039 SELECT COUNT(name) INTO l_rev_class_name_count
2040 FROM cn_revenue_classes
2041 WHERE name = l_rev_class_name
2042 AND org_id = p_org_id;
2043
2044
2045 IF (l_uplift_start_date >= v_plan_element_rec.start_date AND
2046 NVL(l_uplift_end_date,to_date('20991231','yyyymmdd')) <= NVL(v_plan_element_rec.end_date,to_date('29991231', 'yyyymmdd')) )OR
2047 (l_uplift_start_date >= v_plan_element_rec.start_date AND l_uplift_end_date IS NULL AND v_plan_element_rec.end_date IS NULL ) --Here its not mandatory to enter end date of plan element and product multipliers.
2048 THEN
2049
2050 -- Get the Revenue Class Values - Only If Revenue Class exists in the Target System
2051 IF l_rev_class_name_count <> 0 THEN
2052
2053 -- Get the Quota Assign Values
2054 v_rev_uplift_tbl(l_quota_count).rev_class_name
2055 := l_rev_class_name;
2056
2057 /* Commented to fix bug 14364651
2058
2059 v_rev_uplift_tbl(l_quota_count).start_date
2060 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
2061 v_rev_uplift_tbl(l_quota_count).end_date
2062 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
2063
2064 */
2065
2066 /*Added to fix bug 14364651*/
2067
2068 v_rev_uplift_tbl(l_quota_count).start_date:=l_uplift_start_date;
2069 v_rev_uplift_tbl(l_quota_count).end_date:=l_uplift_end_date;
2070
2071 --addition ends here
2072
2073 v_rev_uplift_tbl(l_quota_count).rev_class_payment_uplift
2074 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PaymentFactor'),0)));
2075 v_rev_uplift_tbl(l_quota_count).rev_class_quota_uplift
2076 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'QuotaFactor'),0)));
2077 v_rev_uplift_tbl(l_quota_count).org_id
2078 := p_org_id;
2079 -- Other Attributes Start
2080 v_rev_uplift_tbl(l_quota_count).attribute_category
2081 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'AttributeCategory'),0)));
2082 v_rev_uplift_tbl(l_quota_count).attribute1
2083 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
2084 v_rev_uplift_tbl(l_quota_count).attribute2
2085 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
2086 v_rev_uplift_tbl(l_quota_count).attribute3
2087 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
2088 v_rev_uplift_tbl(l_quota_count).attribute4
2089 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
2090 v_rev_uplift_tbl(l_quota_count).attribute5
2091 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
2092 v_rev_uplift_tbl(l_quota_count).attribute6
2093 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
2094 v_rev_uplift_tbl(l_quota_count).attribute7
2095 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
2096 v_rev_uplift_tbl(l_quota_count).attribute8
2097 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
2098 v_rev_uplift_tbl(l_quota_count).attribute9
2099 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
2100 v_rev_uplift_tbl(l_quota_count).attribute10
2101 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
2102 v_rev_uplift_tbl(l_quota_count).attribute11
2103 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
2104 v_rev_uplift_tbl(l_quota_count).attribute12
2105 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
2106 v_rev_uplift_tbl(l_quota_count).attribute13
2107 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
2108 v_rev_uplift_tbl(l_quota_count).attribute14
2109 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
2110 v_rev_uplift_tbl(l_quota_count).attribute15
2111 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
2112 v_rev_uplift_tbl(l_quota_count).rev_class_name_old
2113 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClassNameOld'),0)));
2114 v_rev_uplift_tbl(l_quota_count).start_date_old
2115 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDateOld'),0))),'YYYY-MM-DD');
2116 v_rev_uplift_tbl(l_quota_count).end_date_old
2117 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDateOld'),0))),'YYYY-MM-DD');
2118
2119 l_quota_count:=l_quota_count+1; --increment the value
2120
2121 -- Other Attributes End
2122 END IF;
2123 ELSE
2124 fnd_message.set_name ('CN' , 'CN_COPY_PE_FCTRS_OUT_RANGE');
2125 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
2126 fnd_file.put_line(fnd_file.log, fnd_message.get);
2127 fnd_file.put_line(fnd_file.Log, 'Plan element and Multipliers dates are miss matching');
2128 END IF;
2129 END LOOP;
2130 END IF;
2131
2132 v_node_sibling_Previous := v_node_sibling_Next;
2133 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
2134 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
2135
2136 END LOOP;
2137
2138 IF v_node_sibling_name_Next = 'CnTrxFactorsVO' THEN
2139 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
2140 IF v_node_sibling_length_Next > 0 THEN
2141 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
2142 -- Clearing the Temporary Table
2143 v_trx_factor_tbl.DELETE;
2144 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
2145 -- Loop through all the child nodes of CnTrxFactorsVO Node
2146 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2147 -- Cast Node to Element
2148 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2149
2150 -- Find Revenue Class existing in the Target System
2151 l_rev_class_name :=
2152 dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClsName'),0)));
2153 SELECT COUNT(name) INTO l_rev_class_name_count
2154 FROM cn_revenue_classes
2155 WHERE name = l_rev_class_name
2156 AND org_id = p_org_id;
2157
2158 -- Get the Revenue Class Values - Only If Revenue Class exists in the Target System
2159 IF l_rev_class_name_count <> 0 THEN
2160 -- Get the Quota Assign Values
2161 v_trx_factor_tbl(i).trx_type
2162 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'TrxType'),0)));
2163 v_trx_factor_tbl(i).event_factor
2164 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EventFactor'),0)));
2165 v_trx_factor_tbl(i).rev_class_name
2166 := l_rev_class_name;
2167 v_trx_factor_tbl(i).org_id
2168 := p_org_id;
2169 END IF;
2170 END LOOP;
2171 END IF;
2172 END IF;
2173 --*********************************************************************
2174 -- Parse RT Quota Assigns
2175 --*********************************************************************
2176 v_node_sibling_Previous := v_node_sibling_Next;
2177 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
2178 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
2179 ELSE
2180 ROLLBACK TO Create_PlanElement;
2181 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2182 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2183 fnd_file.put_line(fnd_file.log, fnd_message.get);
2184 fnd_file.put_line(fnd_file.Log,'2$$$$$');
2185 END IF;
2186 ELSE
2187 ROLLBACK TO Create_PlanElement;
2188 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2189 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2190 fnd_file.put_line(fnd_file.log, fnd_message.get);
2191 fnd_file.put_line(fnd_file.Log,'3$$$$$');
2192 END IF;
2193 ELSE
2194 --Bug 8558744: The Product is not Mandatory in Plan Element.
2195 -- Clearing the Temporary Table
2196 v_revenue_class_tbl.DELETE;
2197 v_trx_factor_tbl.DELETE;
2198 v_rev_uplift_tbl.DELETE;
2199 END IF;
2200
2201 IF v_node_sibling_name_Next = 'CnRtQuotaAsgnsVO' THEN
2202 -- Initializing formula and RT count check in PE
2203 l_rt_fm_notexist_count := 0;
2204 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
2205 IF v_node_sibling_length_Next > 0 THEN
2206 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
2207 -- Clearing the Temporary Table
2208 v_rt_quota_asgns_tbl.DELETE;
2209 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
2210 -- Loop through all the child nodes of CnQuotaAssignsVO Node
2211 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2212 -- Cast Node to Element
2213 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2214 -- Get the Quota Assign Values
2215 v_rt_quota_asgns_tbl(i).rate_schedule_name
2216 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleName'),0)));
2217 v_rt_quota_asgns_tbl(i).calc_formula_name
2218 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CalcFormulaName'),0)));
2219
2220 /*Code changes done to fix bug 14364651 */
2221 IF p_start_date IS NULL THEN
2222 v_rt_quota_asgns_tbl(i).start_date
2223 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
2224
2225 ELSE
2226
2227 v_rt_quota_asgns_tbl(i).start_date:= p_start_date;
2228
2229 END IF;
2230
2231 IF p_start_date IS NULL AND p_end_date IS NULL THEN
2232
2233 v_rt_quota_asgns_tbl(i).end_date
2234 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
2235
2236 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
2237
2238 v_rt_quota_asgns_tbl(i).end_date:=p_end_date;
2239
2240 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
2241
2242 v_rt_quota_asgns_tbl(i).end_date:= NULL;
2243
2244 END IF;
2245
2246 /*Code changes done to fix bug 14364651 ends*/
2247
2248
2249 v_rt_quota_asgns_tbl(i).org_id
2250 := p_org_id;
2251 -- Other Attributes Start
2252 v_rt_quota_asgns_tbl(i).attribute_category
2253 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'AttributeCategory'),0)));
2254 v_rt_quota_asgns_tbl(i).attribute1
2255 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
2256 v_rt_quota_asgns_tbl(i).attribute2
2257 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
2258 v_rt_quota_asgns_tbl(i).attribute3
2259 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
2260 v_rt_quota_asgns_tbl(i).attribute4
2261 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
2262 v_rt_quota_asgns_tbl(i).attribute5
2263 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
2264 v_rt_quota_asgns_tbl(i).attribute6
2265 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
2266 v_rt_quota_asgns_tbl(i).attribute7
2267 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
2268 v_rt_quota_asgns_tbl(i).attribute8
2269 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
2270 v_rt_quota_asgns_tbl(i).attribute9
2271 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
2272 v_rt_quota_asgns_tbl(i).attribute10
2273 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
2274 v_rt_quota_asgns_tbl(i).attribute11
2275 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
2276 v_rt_quota_asgns_tbl(i).attribute12
2277 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
2278 v_rt_quota_asgns_tbl(i).attribute13
2279 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
2280 v_rt_quota_asgns_tbl(i).attribute14
2281 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
2282 v_rt_quota_asgns_tbl(i).attribute15
2283 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
2284 v_rt_quota_asgns_tbl(i).rate_schedule_name_old
2285 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleNameOld'),0)));
2286 v_rt_quota_asgns_tbl(i).start_date_old
2287 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDateOld'),0))),'YYYY-MM-DD');
2288 v_rt_quota_asgns_tbl(i).end_date_old
2289 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDateOld'),0))),'YYYY-MM-DD');
2290 -- Other Attributes End
2291
2292 -- Call common utility package for name length check
2293 v_rt_quota_asgns_tbl(i).rate_schedule_name := cn_plancopy_util_pvt.check_name_length(
2294 p_name => v_rt_quota_asgns_tbl(i).rate_schedule_name,
2295 p_org_id => p_org_id,
2296 p_type => 'RATETABLE',
2297 p_prefix => p_prefix);
2298 -- Check for Rate Table Name existence in Target System
2299 SELECT COUNT(name) INTO l_rate_schedule_name_count
2300 FROM cn_rate_schedules
2301 WHERE name = v_rt_quota_asgns_tbl(i).rate_schedule_name
2302 AND org_id = p_org_id;
2303
2304 -- Call common utility package for name length check
2305 -- Only if quota type is 'FORMULA'
2306 IF v_plan_element_rec.element_type = 'FORMULA' THEN
2307 v_rt_quota_asgns_tbl(i).calc_formula_name := cn_plancopy_util_pvt.check_name_length(
2308 p_name => v_rt_quota_asgns_tbl(i).calc_formula_name,
2309 p_org_id => p_org_id,
2310 p_type => 'FORMULA',
2311 p_prefix => p_prefix);
2312 -- Check for Formula Name existence in Target System
2313 SELECT COUNT(name) INTO l_formula_name_count
2314 FROM cn_calc_formulas
2315 WHERE name = v_rt_quota_asgns_tbl(i).calc_formula_name
2316 AND org_id = p_org_id;
2317 END IF;
2318 -- If Rate Table does not exist, do not create Plan Element
2319 IF l_rate_schedule_name_count = 0 THEN
2320 l_rt_fm_notexist_count := 1;
2321 EXIT;
2322 END IF;
2323 -- If Formula does not exist, do not create Plan Element
2324 IF v_plan_element_rec.element_type = 'FORMULA' AND l_formula_name_count = 0 THEN
2325 l_rt_fm_notexist_count := 1;
2326 EXIT;
2327 END IF;
2328 END LOOP;
2329 END IF;
2330
2331 -- If Rate Table and Formula exists, proceed further
2332 IF l_rt_fm_notexist_count = 0 THEN
2333 --*********************************************************************
2334 -- Parse Period Quotas
2335 --*********************************************************************
2336 v_node_sibling_Previous := v_node_sibling_Next;
2337 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
2338 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
2339 IF v_node_sibling_name_Next = 'CnPeriodQuotasVO' THEN
2340 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
2341 IF v_node_sibling_length_Next > 0 THEN
2342 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
2343 -- Clearing the Temporary Table
2344 v_period_quotas_tbl.DELETE;
2345
2346 IF p_start_date IS NOT NULL THEN
2347
2348 select COUNT(period_id) into l_period_exist_count from cn_period_statuses where start_date <= p_start_date and end_date >= p_start_date AND org_id = p_org_id AND period_status='O';
2349
2350 IF l_period_exist_count=0 THEN
2351
2352 ROLLBACK TO Create_PlanElement;
2353 fnd_file.put_line(fnd_file.log, 'Period Does not exist or a closed period. Check for the date: ' || p_start_date);
2354 fnd_message.set_name ('CN', 'CN_PERIOD_NOT_EXIST');
2355 fnd_message.set_token ('PERIOD_NAME', p_start_date);
2356 fnd_file.put_line(fnd_file.log, fnd_message.get);
2357 RAISE FND_API.G_EXC_ERROR;
2358
2359 END IF; --end of l_period_exist_count
2360
2361 select period_id into l_period_id from cn_period_statuses where start_date <= p_start_date and end_date >= p_start_date AND org_id = p_org_id;
2362
2363 END IF;
2364
2365 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
2366 -- Loop through all the child nodes of CnQuotaAssignsVO Node
2367 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2368 -- Cast Node to Element
2369 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2370 -- Checking Period Status and Period Range
2371 l_period_name := NULL;
2372
2373
2374 IF p_start_date IS NOT NULL THEN
2375
2376 l_period_name := cn_api.get_acc_period_name(l_period_id, p_org_id);
2377
2378 IF l_period_name IS NULL THEN
2379
2380 l_period_id:=l_period_id-1;
2381 l_period_exist_count:=0;
2382 SELECT COUNT(PERIOD_ID) INTO l_period_exist_count FROM cn_acc_period_statuses_v WHERE ORG_ID=p_org_id and period_id>l_period_id;
2383
2384 IF l_period_exist_count<>0 THEN
2385
2386 SELECT MIN(PERIOD_ID) INTO l_period_id FROM cn_acc_period_statuses_v WHERE ORG_ID=p_org_id AND period_id>l_period_id;
2387 l_period_name := cn_api.get_acc_period_name(l_period_id, p_org_id);
2388
2389 END IF; --end of l_period_exist_count<>0
2390
2391 END IF; --end of l_period_name is NULL
2392
2393 l_period_id := l_period_id + 1;
2394
2395
2396 ELSE
2397
2398 l_period_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodName'),0)));
2399
2400 END IF;
2401
2402
2403 l_period_exist_count:=0;
2404 SELECT count(period_name) INTO l_period_exist_count
2405 FROM cn_period_statuses
2406 WHERE period_name = l_period_name
2407 AND org_id = p_org_id;
2408
2409 IF l_period_exist_count <> 0 THEN
2410
2411 SELECT end_date INTO l_period_end_date
2412 FROM cn_period_statuses
2413 WHERE period_name = l_period_name
2414 AND org_id = p_org_id;
2415
2416 IF p_end_date IS NULL AND NVL(v_plan_element_rec.end_date,l_period_end_date) >= l_period_end_date THEN
2417 -- Get the Quota Assign Values
2418
2419 v_period_quotas_tbl(i).period_name := l_period_name;
2420
2421 v_period_quotas_tbl(i).period_target := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodTarget'),0)));
2422 v_period_quotas_tbl(i).period_payment := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodPayment'),0)));
2423 v_period_quotas_tbl(i).performance_goal := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PerformanceGoal'),0)));
2424 v_period_quotas_tbl(i).org_id := p_org_id;
2425 -- Other Attributes Start
2426 v_period_quotas_tbl(i).attribute1 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
2427 v_period_quotas_tbl(i).attribute2 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
2428 v_period_quotas_tbl(i).attribute3 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
2429 v_period_quotas_tbl(i).attribute4 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
2430 v_period_quotas_tbl(i).attribute5 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
2431 v_period_quotas_tbl(i).attribute6 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
2432 v_period_quotas_tbl(i).attribute7 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
2433 v_period_quotas_tbl(i).attribute8 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
2434 v_period_quotas_tbl(i).attribute9 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
2435 v_period_quotas_tbl(i).attribute10 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
2436 v_period_quotas_tbl(i).attribute11 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
2437 v_period_quotas_tbl(i).attribute12 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
2438 v_period_quotas_tbl(i).attribute13 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
2439 v_period_quotas_tbl(i).attribute14 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
2440 v_period_quotas_tbl(i).attribute15 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
2441 v_period_quotas_tbl(i).period_name_old := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodNameOld'),0)));
2442 -- Other Attributes End
2443 END IF;
2444 IF p_end_date is NOT NULL AND p_end_date >= l_period_end_date THEN
2445 -- Get the Quota Assign Values
2446 v_period_quotas_tbl(i).period_name := l_period_name;
2447
2448 v_period_quotas_tbl(i).period_target := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodTarget'),0)));
2449 v_period_quotas_tbl(i).period_payment := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodPayment'),0)));
2450 v_period_quotas_tbl(i).performance_goal := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PerformanceGoal'),0)));
2451 v_period_quotas_tbl(i).org_id := p_org_id;
2452 -- Other Attributes Start
2453 v_period_quotas_tbl(i).attribute1 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
2454 v_period_quotas_tbl(i).attribute2 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
2455 v_period_quotas_tbl(i).attribute3 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
2456 v_period_quotas_tbl(i).attribute4 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
2457 v_period_quotas_tbl(i).attribute5 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
2458 v_period_quotas_tbl(i).attribute6 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
2459 v_period_quotas_tbl(i).attribute7 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
2460 v_period_quotas_tbl(i).attribute8 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
2461 v_period_quotas_tbl(i).attribute9 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
2462 v_period_quotas_tbl(i).attribute10 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
2463 v_period_quotas_tbl(i).attribute11 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
2464 v_period_quotas_tbl(i).attribute12 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
2465 v_period_quotas_tbl(i).attribute13 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
2466 v_period_quotas_tbl(i).attribute14 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
2467 v_period_quotas_tbl(i).attribute15 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
2468 v_period_quotas_tbl(i).period_name_old := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodNameOld'),0)));
2469 -- Other Attributes End
2470 END IF;
2471 END IF;
2472 END LOOP;
2473 -- Clearing the Temporary Table to pass it as Null
2474 -- v_period_quotas_tbl.DELETE;
2475 END IF;
2476 END IF;
2477
2478 --Call to Plan Element Public API to create Plan Element
2479 cn_plan_element_pub.create_plan_element (
2480 p_api_version => p_api_version,
2481 p_init_msg_list => p_init_msg_list,
2482 p_commit => p_commit,
2483 p_validation_level => p_validation_level,
2484 x_return_status => x_return_status,
2485 x_msg_count => x_msg_count,
2486 x_msg_data => x_msg_data,
2487 p_plan_element_rec => v_plan_element_rec,
2488 p_revenue_class_rec_tbl => v_revenue_class_tbl,
2489 p_rev_uplift_rec_tbl => v_rev_uplift_tbl,
2490 p_trx_factor_rec_tbl => v_trx_factor_tbl,
2491 p_period_quotas_rec_tbl => v_period_quotas_tbl,
2492 p_rt_quota_asgns_rec_tbl => v_rt_quota_asgns_tbl,
2493 x_loading_status => x_loading_status,
2494 p_is_duplicate => 'N');
2495
2496
2497
2498 IF x_return_status = fnd_api.g_ret_sts_success THEN
2499 -- Log Message for Plan Element creation.
2500 fnd_message.set_name ('CN' , 'CN_COPY_PE_CREATE');
2501 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2502 fnd_message.set_token('PLAN_ELEMENT_START_DATE',v_plan_element_rec.start_date);
2503 IF v_plan_element_rec.end_date IS NOT NULL THEN
2504 fnd_message.set_token('PLAN_ELEMENT_END_DATE', v_plan_element_rec.end_date);
2505 ELSE
2506 fnd_message.set_token('PLAN_ELEMENT_END_DATE', 'NULL');
2507 END IF;
2508 fnd_file.put_line(fnd_file.log, fnd_message.get);
2509 -- Log Message for Formula to Quota Assignment
2510 fnd_message.set_name ('CN' , 'CN_COPY_FM_PE_ASSIGN');
2511 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2512 fnd_message.set_token('FORMULA_NAME',v_plan_element_rec.calc_formula_name);
2513 fnd_file.put_line(fnd_file.log, fnd_message.get);
2514 -- Log Message for Rate Table to Quota Assignment
2515 IF (v_rt_quota_asgns_tbl.COUNT > 0) THEN
2516 FOR i IN v_rt_quota_asgns_tbl.FIRST..v_rt_quota_asgns_tbl.LAST LOOP
2517 fnd_message.set_name ('CN' , 'CN_COPY_RT_PE_ASSIGN');
2518 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2519 fnd_message.set_token('RATE_TABLE_NAME',v_rt_quota_asgns_tbl(i).rate_schedule_name);
2520 fnd_message.set_token('ASSIGNMENT_START_DATE',v_rt_quota_asgns_tbl(i).start_date);
2521 IF v_rt_quota_asgns_tbl(i).end_date IS NOT NULL THEN
2522 fnd_message.set_token('ASSIGNMENT_END_DATE', v_rt_quota_asgns_tbl(i).end_date);
2523 ELSE
2524 fnd_message.set_token('ASSIGNMENT_END_DATE', 'NULL');
2525 END IF;
2526 fnd_file.put_line(fnd_file.log, fnd_message.get);
2527 END LOOP;
2528 END IF;
2529 COMMIT;
2530 -- New value of plan element for Interdependent cases
2531 g_miss_pe_exp_rec.new_pe_name := v_plan_element_rec.name;
2532 SELECT COUNT(name) INTO l_new_pe_name
2533 FROM cn_quotas_v
2534 WHERE name = v_plan_element_rec.name;
2535 IF l_new_pe_name > 0 THEN
2536 SELECT quota_id INTO g_miss_pe_exp_rec.new_pe_id
2537 FROM cn_quotas_v
2538 WHERE name = v_plan_element_rec.name;
2539
2540 l_pe_counter := l_pe_counter + 1;
2541 g_miss_pe_exp_tbl(l_pe_counter).old_pe_name := g_miss_pe_exp_rec.old_pe_name;
2542 g_miss_pe_exp_tbl(l_pe_counter).old_pe_id := g_miss_pe_exp_rec.old_pe_id;
2543 g_miss_pe_exp_tbl(l_pe_counter).new_pe_name := g_miss_pe_exp_rec.new_pe_name;
2544 g_miss_pe_exp_tbl(l_pe_counter).new_pe_id := g_miss_pe_exp_rec.new_pe_id;
2545 END IF;
2546 ELSE
2547 ROLLBACK TO Create_PlanElement;
2548 IF x_return_status = fnd_api.g_ret_sts_error THEN
2549 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL_EXPECTED');
2550 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2551 fnd_file.put_line(fnd_file.log, fnd_message.get);
2552 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2553 fnd_file.put_line(fnd_file.Log,'4$$$$$');
2554 END IF;
2555 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2556 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2557 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2558 fnd_file.put_line(fnd_file.log, fnd_message.get);
2559 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2560 fnd_file.put_line(fnd_file.Log,'5$$$$$');
2561 END IF;
2562 END IF;
2563 ELSE
2564 ROLLBACK TO Create_PlanElement;
2565 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2566 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2567 fnd_file.put_line(fnd_file.log, fnd_message.get);
2568 fnd_file.put_line(fnd_file.Log,'6$$$$$');
2569 END IF;
2570 ELSE
2571 ROLLBACK TO Create_PlanElement;
2572 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2573 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2574 fnd_file.put_line(fnd_file.log, fnd_message.get);
2575 fnd_file.put_line(fnd_file.Log,'7$$$$$');
2576 END IF;
2577 ELSE
2578 ROLLBACK TO Create_PlanElement;
2579 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2580 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2581 fnd_file.put_line(fnd_file.log, fnd_message.get);
2582 fnd_file.put_line(fnd_file.Log,'8$$$$$');
2583 END IF;
2584 END IF;
2585 END IF;
2586
2587 --*********************************************************************
2588 --****************** Parse Compensation Plan ********************
2589 --*********************************************************************
2590 IF v_child_node_name = 'CnCompPlansVO' THEN
2591 -- Rollback SavePoint
2592 SAVEPOINT Create_CompPlan;
2593 -- Intialising Rate Table record
2594 v_comp_plan_rec := NULL;
2595 -- Get the CnCompPlansVORow
2596 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
2597 -- Cast Node to Element
2598 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
2599 -- Get the Compensation Plan Name
2600 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
2601 -- Get the Compensation Plan Name Value
2602 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
2603 -- Attach prefix to the Name Value
2604 -- Call common utility package for name length check
2605 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
2606 p_name => v_name_node_value,
2607 p_org_id => p_org_id,
2608 p_type => 'PLAN',
2609 p_prefix => p_prefix);
2610
2611 -- Check if Compensation Plan already exists in the Target Instance
2612 SELECT COUNT(name) INTO l_reuse_count
2613 FROM cn_comp_plans
2614 WHERE name = v_name_node_value_new
2615 AND org_id = p_org_id;
2616
2617 --If Compensation Plan exits then do not Insert otherwise insert a new Record.
2618 IF l_reuse_count > 0 THEN
2619
2620 SELECT status_code INTO v_comp_plan_rec.status_code
2621 FROM cn_comp_plans
2622 WHERE name = v_name_node_value_new
2623 AND org_id = p_org_id;
2624
2625 IF v_comp_plan_rec.status_code = 'COMPLETE' THEN
2626
2627 fnd_message.set_name ('CN' , 'CN_COPY_CP_REUSE');
2628 fnd_message.set_token('PLAN_NAME',v_name_node_value_new);
2629 fnd_file.put_line(fnd_file.log, fnd_message.get);
2630 p_reuse_obj_count := p_reuse_obj_count + 1;
2631
2632 ELSE
2633
2634 fnd_message.set_name ('CN' , 'Compensation Plan exist in the target, but in INCOMPLETE Status');
2635 fnd_message.set_token('PLAN_NAME',v_name_node_value_new);
2636 fnd_file.put_line(fnd_file.log, fnd_message.get);
2637 ROLLBACK TO Create_CompPlan;
2638 EXIT;
2639
2640 END IF;
2641
2642
2643
2644 END IF; --end of l_reuse_count>0
2645
2646 IF l_reuse_count = 0 THEN
2647
2648 -- Get other Compensation Plan values
2649 v_comp_plan_rec.name := v_name_node_value_new;
2650 v_comp_plan_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
2651 v_comp_plan_rec.status_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'StatusCode'),0)));
2652 v_comp_plan_rec.allow_rev_class_overlap := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'AllowRevClassOverlap'),0)));
2653 v_comp_plan_rec.org_id := p_org_id;
2654 -- Start Date parameter Logic
2655 IF p_start_date IS NULL THEN
2656 v_comp_plan_rec.start_date := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'StartDate'),0))),'YYYY-MM-DD');
2657 ELSE
2658 v_comp_plan_rec.start_date := p_start_date;
2659 END IF;
2660 -- End Date parameter Logic
2661 IF p_start_date IS NULL AND p_end_date IS NULL THEN
2662 v_comp_plan_rec.end_date := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'EndDate'),0))),'YYYY-MM-DD');
2663 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
2664 v_comp_plan_rec.end_date := p_end_date;
2665 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
2666 v_comp_plan_rec.end_date := NULL;
2667 END IF;
2668
2669 -- Other Attributes Start
2670 v_comp_plan_rec.attribute_category := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'AttributeCategory'),0)));
2671 v_comp_plan_rec.attribute1 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute1'),0)));
2672 v_comp_plan_rec.attribute2 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute2'),0)));
2673 v_comp_plan_rec.attribute3 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute3'),0)));
2674 v_comp_plan_rec.attribute4 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute4'),0)));
2675 v_comp_plan_rec.attribute5 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute5'),0)));
2676 v_comp_plan_rec.attribute6 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute6'),0)));
2677 v_comp_plan_rec.attribute7 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute7'),0)));
2678 v_comp_plan_rec.attribute8 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute8'),0)));
2679 v_comp_plan_rec.attribute9 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute9'),0)));
2680 v_comp_plan_rec.attribute10 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute10'),0)));
2681 v_comp_plan_rec.attribute11 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute11'),0)));
2682 v_comp_plan_rec.attribute12 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute12'),0)));
2683 v_comp_plan_rec.attribute13 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute13'),0)));
2684 v_comp_plan_rec.attribute14 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute14'),0)));
2685 v_comp_plan_rec.attribute15 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute15'),0)));
2686 -- Other Attributes End
2687
2688 --*********************************************************************
2689 -- Import Compensation Plan
2690 --*********************************************************************
2691 l_comp_plan_id := NULL;
2692 v_comp_plan_rec.comp_plan_id := NULL;
2693 cn_comp_plan_pvt.create_comp_plan(
2694 p_api_version => p_api_version,
2695 p_init_msg_list => p_init_msg_list,
2696 p_commit => p_commit,
2697 p_validation_level => p_validation_level,
2698 p_comp_plan => v_comp_plan_rec,
2699 x_comp_plan_id => l_comp_plan_id,
2700 x_return_status => x_return_status,
2701 x_msg_count => x_msg_count,
2702 x_msg_data => x_msg_data);
2703
2704 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2705 l_sql_fail_count := 1;
2706 END IF;
2707
2708 IF l_sql_fail_count = 0 THEN
2709 --*********************************************************************
2710 -- Parse Quota Assignments
2711 --*********************************************************************
2712 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
2713 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
2714 IF v_node_sibling_name_Next = 'CnQuotaAssignsVO' THEN
2715 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
2716 IF v_node_sibling_length_Next > 0 THEN
2717 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
2718 -- Clearing the Temporary Table
2719 v_quota_assign_tbl.DELETE;
2720 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
2721 -- Loop through all the child nodes of CnQuotaAssignsVO Node
2722 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2723 -- Cast Node to Element
2724 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2725 -- Get the Quota Assign Values
2726 v_quota_assign_tbl(i).name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeName'),0)));
2727 v_quota_assign_tbl(i).org_id := p_org_id;
2728 v_quota_assign_tbl(i).comp_plan_id := l_comp_plan_id;
2729 v_quota_assign_tbl(i).description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Description'),0)));
2730 v_quota_assign_tbl(i).quota_sequence := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'QuotaSequence'),0)));
2731
2732 -- Call common utility package for name length check
2733 v_quota_assign_tbl(i).name := cn_plancopy_util_pvt.check_name_length(
2734 p_name => v_quota_assign_tbl(i).name,
2735 p_org_id => p_org_id,
2736 p_type => 'PLANELEMENT',
2737 p_prefix => p_prefix);
2738
2739 -- Check if PE exists in the Target System
2740 l_pe_name_count := 0;
2741 SELECT count(name) INTO l_pe_name_count
2742 FROM cn_quotas_v
2743 WHERE name = v_quota_assign_tbl(i).name;
2744 IF l_pe_name_count > 0 THEN
2745 SELECT quota_id,start_date,end_date
2746 INTO v_quota_assign_tbl(i).quota_id,v_quota_assign_tbl(i).start_date, v_quota_assign_tbl(i).end_date
2747 FROM cn_quotas_v
2748 WHERE name = v_quota_assign_tbl(i).name
2749 AND org_id = p_org_id;
2750 ELSE
2751 ROLLBACK TO Create_CompPlan;
2752 -- Comp Plan Fail Message
2753 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2754 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2755 fnd_file.put_line(fnd_file.log, fnd_message.get);
2756 -- Failed Plan Name collection for Summary Section
2757 IF l_failed_plan_name IS NULL THEN
2758 l_failed_plan_name := v_comp_plan_rec.name;
2759 ELSE
2760 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2761 END IF;
2762 l_sql_fail_count := 1;
2763 EXIT;
2764 END IF;
2765 END LOOP;
2766
2767 IF l_sql_fail_count = 0 THEN
2768 --*********************************************************************
2769 -- Import Quota Assignments
2770 --*********************************************************************
2771 IF (v_quota_assign_tbl.COUNT > 0) THEN
2772 -- Check for at least one PE to be assigned.
2773 l_quota_asgn_count := 0;
2774 FOR i IN v_quota_assign_tbl.FIRST..v_quota_assign_tbl.LAST LOOP
2775 IF (v_quota_assign_tbl(i).start_date <= NVL(v_comp_plan_rec.end_date,v_quota_assign_tbl(i).start_date) AND
2776 NVL(v_quota_assign_tbl(i).end_date,v_comp_plan_rec.start_date) >= v_comp_plan_rec.start_date) THEN
2777 cn_quota_assign_pvt.create_quota_assign(
2778 p_api_version => p_api_version,
2779 p_init_msg_list => p_init_msg_list,
2780 p_commit => p_commit,
2781 p_validation_level => p_validation_level,
2782 p_quota_assign => v_quota_assign_tbl(i),
2783 x_return_status => x_return_status,
2784 x_msg_count => x_msg_count,
2785 x_msg_data => x_msg_data);
2786
2787 IF x_return_status = fnd_api.g_ret_sts_success THEN
2788 -- Validate the CompPlan
2789 v_comp_plan_rec.comp_plan_id := l_comp_plan_id;
2790 cn_comp_plan_pvt.validate_comp_plan(
2791 p_api_version => p_api_version,
2792 p_init_msg_list => p_init_msg_list,
2793 p_commit => p_commit,
2794 p_validation_level => p_validation_level,
2795 p_comp_plan => v_comp_plan_rec,
2796 x_return_status => x_return_status,
2797 x_msg_count => x_msg_count,
2798 x_msg_data => x_msg_data);
2799
2800
2801 IF x_return_status = fnd_api.g_ret_sts_success THEN
2802 fnd_message.set_name ('CN' , 'CN_COPY_PE_ASSIGN');
2803 fnd_message.set_token('PLAN_NAME',v_name_node_value_new);
2804 fnd_message.set_token('PLAN_ELEMENT_NAME',v_quota_assign_tbl(i).name);
2805 fnd_file.put_line(fnd_file.log, fnd_message.get);
2806 l_quota_asgn_count := 1;
2807 ELSE
2808 l_sql_fail_count := 1;
2809 EXIT;
2810 END IF;
2811 ELSE
2812 l_sql_fail_count := 1;
2813 EXIT;
2814 END IF;
2815 ELSE
2816 fnd_message.set_name ('CN' , 'CN_COPY_PE_OUT_RANGE');
2817 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2818 fnd_message.set_token('PLAN_ELEMENT_NAME',v_quota_assign_tbl(i).name);
2819 fnd_file.put_line(fnd_file.log, fnd_message.get);
2820 END IF;
2821 END LOOP;
2822 IF l_sql_fail_count = 0 AND l_quota_asgn_count = 1 THEN
2823 fnd_message.set_name ('CN' , 'CN_COPY_CP_CREATE');
2824 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2825 fnd_message.set_token('PLAN_START_DATE',v_comp_plan_rec.start_date);
2826 IF v_comp_plan_rec.end_date IS NOT NULL THEN
2827 fnd_message.set_token('PLAN_END_DATE', v_comp_plan_rec.end_date);
2828 ELSE
2829 fnd_message.set_token('PLAN_END_DATE', 'NULL');
2830 END IF;
2831 fnd_file.put_line(fnd_file.log, fnd_message.get);
2832 p_success_obj_count := p_success_obj_count + 1;
2833 COMMIT;
2834 ELSE
2835 ROLLBACK TO Create_CompPlan;
2836 -- Comp Plan Fail Message
2837 IF x_return_status = fnd_api.g_ret_sts_error THEN
2838 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL_EXPECTED');
2839 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2840 fnd_file.put_line(fnd_file.log, fnd_message.get);
2841 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2842 END IF;
2843 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2844 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2845 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2846 fnd_file.put_line(fnd_file.log, fnd_message.get);
2847 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2848 END IF;
2849 -- Failed Plan Name collection for Summary Section
2850 IF l_failed_plan_name IS NULL THEN
2851 l_failed_plan_name := v_comp_plan_rec.name;
2852 ELSE
2853 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2854 END IF;
2855 END IF;
2856 ELSE
2857 ROLLBACK TO Create_CompPlan;
2858 -- Comp Plan Fail Message
2859 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2860 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2861 fnd_file.put_line(fnd_file.log, fnd_message.get);
2862 -- Failed Plan Name collection for Summary Section
2863 IF l_failed_plan_name IS NULL THEN
2864 l_failed_plan_name := v_comp_plan_rec.name;
2865 ELSE
2866 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2867 END IF;
2868 END IF;
2869 END IF;
2870 ELSE
2871 ROLLBACK TO Create_CompPlan;
2872 -- Comp Plan Fail Message
2873 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2874 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2875 fnd_file.put_line(fnd_file.log, fnd_message.get);
2876 -- Failed Plan Name collection for Summary Section
2877 IF l_failed_plan_name IS NULL THEN
2878 l_failed_plan_name := v_comp_plan_rec.name;
2879 ELSE
2880 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2881 END IF;
2882 END IF;
2883 ELSE
2884 ROLLBACK TO Create_CompPlan;
2885 -- Comp Plan Fail Message
2886 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2887 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2888 fnd_file.put_line(fnd_file.log, fnd_message.get);
2889 -- Failed Plan Name collection for Summary Section
2890 IF l_failed_plan_name IS NULL THEN
2891 l_failed_plan_name := v_comp_plan_rec.name;
2892 ELSE
2893 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2894 END IF;
2895 END IF;
2896 ELSE
2897 ROLLBACK TO Create_CompPlan;
2898 -- Comp Plan Fail Message
2899 IF x_return_status = fnd_api.g_ret_sts_error THEN
2900 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL_EXPECTED');
2901 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2902 fnd_file.put_line(fnd_file.log, fnd_message.get);
2903 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2904 END IF;
2905 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2906 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2907 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2908 fnd_file.put_line(fnd_file.log, fnd_message.get);
2909 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2910 END IF;
2911 -- Failed Plan Name collection for Summary Section
2912 IF l_failed_plan_name IS NULL THEN
2913 l_failed_plan_name := v_comp_plan_rec.name;
2914 ELSE
2915 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2916 END IF;
2917 END IF;
2918 END IF;
2919 END IF;
2920
2921 /* ****************************** Main Loop End ************************** */
2922 END LOOP;
2923 fnd_file.put_line(fnd_file.log, '**************************************************************');
2924 fnd_file.put_line(fnd_file.log, '******************** END - PLAN COPY IMPORT ******************');
2925 fnd_file.put_line(fnd_file.log, '**************************************************************');
2926
2927 -- ****************************************************************
2928 -- *********** Summary of Import Process Log Messages ***********
2929 -- ****************************************************************
2930 fnd_file.put_line(fnd_file.log, '**************************************************************');
2931 fnd_file.put_line(fnd_file.log, '***************************** SUMMARY ************************');
2932 fnd_file.put_line(fnd_file.log, '**************************************************************');
2933 -- Number of plans to Import
2934 fnd_message.set_name ('CN' , 'CN_COPY_CP_REQ_COUNT');
2935 fnd_message.set_token('COUNT',p_object_count);
2936 fnd_file.put_line(fnd_file.log, fnd_message.get);
2937 -- Number of Plans successfully created in target
2938 fnd_message.set_name ('CN' , 'CN_COPY_CP_SUCCESS_COUNT');
2939 fnd_message.set_token('COUNT',p_success_obj_count);
2940 fnd_file.put_line(fnd_file.log, fnd_message.get);
2941 -- Number of Plans reused in tagret
2942 fnd_message.set_name ('CN' , 'CN_COPY_CP_REUSE_COUNT');
2943 fnd_message.set_token('COUNT',p_reuse_obj_count);
2944 fnd_file.put_line(fnd_file.log, fnd_message.get);
2945 -- Number of Plans which were not imported due to error
2946 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAILED_COUNT');
2947 fnd_message.set_token('LIST',l_failed_plan_name);
2948 fnd_file.put_line(fnd_file.log, fnd_message.get);
2949 fnd_file.put_line(fnd_file.log, '**************************************************************');
2950 fnd_file.put_line(fnd_file.log, '***************************** SUMMARY ************************');
2951 fnd_file.put_line(fnd_file.log, '**************************************************************');
2952
2953 -- Set the Import Status to 'COMPLETE' OR 'FAILED'
2954 IF p_object_count = p_success_obj_count + p_reuse_obj_count THEN
2955 x_import_status := 'COMPLETED';
2956 ELSE
2957 x_import_status := 'FAILED';
2958 END IF;
2959 END IF;
2960 -- Standard call to get message count
2961 FND_MSG_PUB.Count_And_Get(
2962 p_count => x_msg_count,
2963 p_data => x_msg_data,
2964 p_encoded => FND_API.G_FALSE);
2965 EXCEPTION
2966 WHEN FND_API.G_EXC_ERROR THEN
2967 x_return_status := FND_API.G_RET_STS_ERROR;
2968 x_import_status := 'FAILED';
2969 FND_MSG_PUB.count_and_get(
2970 p_count => x_msg_count,
2971 p_data => x_msg_data,
2972 p_encoded => FND_API.G_FALSE);
2973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2975 x_import_status := 'FAILED';
2976 FND_MSG_PUB.count_and_get(
2977 p_count => x_msg_count,
2978 p_data => x_msg_data,
2979 p_encoded => FND_API.G_FALSE);
2980 WHEN OTHERS THEN
2981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2982 x_import_status := 'FAILED';
2983 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2984 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name);
2985 END IF;
2986 FND_MSG_PUB.count_and_get(
2987 p_count => x_msg_count,
2988 p_data => x_msg_data,
2989 p_encoded => FND_API.G_FALSE);
2990 END Parse_XML;
2991
2992 /**********************************************************************/
2993 /* API Body - Finish */
2994 /**********************************************************************/
2995 END CN_COMP_PLAN_XMLCOPY_PVT;