[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.36 2007/11/13 01:22:19 jxsingh noship $*/
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(240);
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
296 TYPE v_pe_exp_rec IS RECORD
297 (old_pe_name cn_quotas.name%TYPE,
298 new_pe_name cn_quotas.name%TYPE,
299 old_pe_id cn_quotas.quota_id%TYPE,
300 new_pe_id cn_quotas.quota_id%TYPE);
301 TYPE v_pe_exp_tbl IS TABLE OF v_pe_exp_rec INDEX BY BINARY_INTEGER;
302 g_miss_pe_exp_rec v_pe_exp_rec;
303 g_miss_pe_exp_tbl v_pe_exp_tbl;
304 l_new_pe_name cn_quotas.name%TYPE;
305 l_pe_counter NUMBER;
306
307 CURSOR c_expense_account_id (p_exp_acc_desc VARCHAR2) IS
308 SELECT code_combination_id
309 FROM gl_sets_of_books glb, cn_repositories r, gl_code_combinations glc
310 WHERE account_type = 'E'
311 AND glb.chart_of_accounts_id = glc.chart_of_accounts_id
312 AND r.set_of_books_id = glb.set_of_books_id
313 AND SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = p_exp_acc_desc
314 AND r.org_id = p_org_id;
315
316 CURSOR c_liab_account_id (p_liab_acc_desc VARCHAR2) IS
317 SELECT code_combination_id
318 FROM gl_sets_of_books glb, cn_repositories r, gl_code_combinations glc
319 WHERE account_type = 'L'
320 AND glb.chart_of_accounts_id = glc.chart_of_accounts_id
321 AND r.set_of_books_id = glb.set_of_books_id
322 AND SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5 = p_liab_acc_desc
323 AND r.org_id = p_org_id;
324
325 BEGIN
326 /**********************************************************************/
327 /* Standard API Checks */
328 /**********************************************************************/
329 -- Standard Start of API savepoint
330 -- SAVEPOINT Parse_XML;
331 -- Standard call to check for call compatibility.
332 IF NOT fnd_api.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME )THEN
333 RAISE fnd_api.g_exc_unexpected_error;
334 END IF;
335
336 -- Initialize message list if p_init_msg_list is set to TRUE.
337 IF fnd_api.to_Boolean( p_init_msg_list ) THEN
338 fnd_msg_pub.initialize;
339 END IF;
340
341 -- Initialize the Import Status to 'FAILED'
342 x_import_status := 'FAILED';
343 /**********************************************************************/
344 /* API Body - Start */
345 /**********************************************************************/
346 -- Create DOMDocument handle:
347 v_doc := dbms_xmldom.newDOMDocument(p_xml);
348 -- Create node from DOMDocument handle:
349 v_node := dbms_xmldom.makeNode(v_doc);
350 -- Get First Child (Parent Node) of the node
351 v_parent_node := dbms_xmldom.getFirstChild(v_node);
352 -- Get the length of parent node
353 v_parent_node_length := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_parent_node));
354 -- Plan element Counter for Interdependent PE check in Expression
355 l_pe_counter := 0;
356
357 IF v_parent_node_length > 0 THEN
358 v_parent_node_list := dbms_xmldom.getChildNodes(v_parent_node);
359 FOR i IN 0..v_parent_node_length-1 LOOP
360 -- All Counters and Checks initialization
361 l_sql_fail_count := 0;
362 l_reuse_count := 0;
363 l_pe_num := 0;
364 l_pe_exist := TRUE;
365
366 -- Loop through all the child nodes of OIC_PLAN_COPY Node
367 v_child_node := dbms_xmldom.item(v_parent_node_list,i);
368 v_child_node_name := dbms_xmldom.getNodeName(dbms_xmldom.item(v_parent_node_list,i));
369
370 /* ****************************** Main Loop Start ************************ */
371
372 --*********************************************************************
373 --********************** Parse Expression ***********************
374 --*********************************************************************
375 IF v_child_node_name = 'CnCalcSqlExpsVO' THEN
376 -- Rollback SavePoint
377 SAVEPOINT Create_Expression;
378 -- Intialising Rate Table record
379 v_expression_rec := NULL;
380 -- Get the CnCalcSqlExpsVORow
381 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
382 -- Cast Node to Element
383 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
384 -- Get the Expression Name
385 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
386 -- Get the Expression Name Value
387 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
388 -- Attach prefix to the Name Value
389 -- v_name_node_value_new := p_prefix || v_name_node_value;
390
391 -- Call common utility package for name length check
392 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
393 p_name => v_name_node_value,
394 p_org_id => p_org_id,
395 p_type => 'EXPRESSION',
396 p_prefix => p_prefix);
397
398 -- Check if Expression already exists in the Target Instance
399 SELECT COUNT(name) INTO l_reuse_count
400 FROM cn_calc_sql_exps
401 WHERE name = v_name_node_value_new
402 AND org_id = p_org_id;
403
404 --If Expression exists then do not Insert otherwise insert a new Record.
405 IF l_reuse_count > 0 THEN
406 fnd_message.set_name ('CN' , 'CN_COPY_EXP_REUSE');
407 fnd_message.set_token('EXPRESSION_NAME',v_name_node_value_new);
408 fnd_file.put_line(fnd_file.log, fnd_message.get);
409 END IF;
410
411 IF l_reuse_count = 0 THEN
412 -- Get the other Expression values
413 l_source_org_id := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'OrgId'),0)));
414 v_expression_rec.org_id := p_org_id;
415 v_expression_rec.name := v_name_node_value_new;
416 v_expression_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
417 v_expression_rec.status := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Status'),0)));
418 v_expression_rec.exp_type_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ExpTypeCode'),0)));
419 v_expression_rec.expression_disp := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ExpressionDisp'),0)));
420 v_expression_rec.sql_select := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SqlSelect'),0)));
421 v_expression_rec.sql_from := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SqlFrom'),0)));
422 v_expression_rec.piped_sql_select := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedSqlSelect'),0)));
423 v_expression_rec.piped_sql_from := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedSqlFrom'),0)));
424 v_expression_rec.piped_expression_disp := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PipedExpressionDisp'),0)));
425
426 --*********************************************************************
427 -- Parse Formula or/and Expression in Expression - Calc Edges
428 --*********************************************************************
429 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
430 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
431 IF v_node_sibling_name_Next = 'CnCalcEdgesVO' THEN
432 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
433 IF v_node_sibling_length_Next > 0 THEN
434 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
435 -- Clearing the Temporary Table
436 g_miss_calc_edges_tbl.DELETE;
437 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
438 -- Loop through all the child nodes of CnRateDimTiers Node
439 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
440 -- Cast Node to Element
441 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
442 -- Get Calc Edges Information
443 l_formula_pkg_source := NULL;
444 l_formula_pkg_target := NULL;
445 l_formula_name_source := NULL;
446 g_miss_calc_edges_tbl(i).child_name
447 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ChildName'),0)));
448 g_miss_calc_edges_tbl(i).edge_type
449 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EdgeType'),0)));
450 g_miss_calc_edges_tbl(i).parent_name
451 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ParentName'),0)));
452 -- Call common utility package for name length check
453 g_miss_calc_edges_tbl(i).parent_name := cn_plancopy_util_pvt.check_name_length(
454 p_name => g_miss_calc_edges_tbl(i).parent_name,
455 p_org_id => p_org_id,
456 p_type => 'EXPRESSION',
457 p_prefix => p_prefix);
458
459 ----------------------------------------------
460 -- Step1: Check if Expression contains Formula
461 ----------------------------------------------
462 IF g_miss_calc_edges_tbl(i).edge_type = 'FE' THEN
463 -- Storing old name of the formula in source system
464 l_formula_name_source := g_miss_calc_edges_tbl(i).child_name;
465 -- Call common utility package for name length check
466 g_miss_calc_edges_tbl(i).child_name := cn_plancopy_util_pvt.check_name_length(
467 p_name => g_miss_calc_edges_tbl(i).child_name,
468 p_org_id => p_org_id,
469 p_type => 'FORMULA',
470 p_prefix => p_prefix);
471
472 g_miss_calc_edges_tbl(i).child_id
473 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ChildId'),0)));
474
475 -- Formula Package Information
476 l_formula_pkg_source := 'cn_formula_'||g_miss_calc_edges_tbl(i).child_id||'_'||l_source_org_id||'_pkg';
477
478 -- Get Formula Information
479 IF g_miss_calc_edges_tbl(i).parent_name = v_expression_rec.name THEN
480 SELECT COUNT(name) INTO l_formula_name_count
481 FROM cn_calc_formulas
482 WHERE name = g_miss_calc_edges_tbl(i).child_name
483 AND org_id = p_org_id;
484 IF l_formula_name_count = 0 THEN
485 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_MISS');
486 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
487 fnd_message.set_token('FORMULA_NAME',g_miss_calc_edges_tbl(i).child_name);
488 fnd_file.put_line(fnd_file.log, fnd_message.get);
489 l_sql_fail_count := 1;
490 EXIT;
491 ELSE
492 SELECT calc_formula_id INTO g_miss_calc_edges_tbl(i).child_id
493 FROM cn_calc_formulas
494 WHERE name = g_miss_calc_edges_tbl(i).child_name
495 AND org_id = p_org_id;
496 END IF;
497 l_formula_pkg_target := 'cn_formula_'||g_miss_calc_edges_tbl(i).child_id||'_'||p_org_id||'_pkg';
498 v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,l_formula_pkg_source,l_formula_pkg_target);
499 v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.sql_select,l_formula_pkg_source,l_formula_pkg_target);
500 v_expression_rec.expression_disp := REPLACE(v_expression_rec.expression_disp,l_formula_name_source,g_miss_calc_edges_tbl(i).child_name);
501 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);
502 END IF;
503 END IF;
504 ---------------------------------------------------------
505 -- Step2: Check if Expression contains another Expression
506 ---------------------------------------------------------
507 IF g_miss_calc_edges_tbl(i).edge_type = 'EE' THEN
508 -- Storing old name of the formula in source system
509 l_exp_name_source := g_miss_calc_edges_tbl(i).child_name;
510 -- Call common utility package for name length check
511 g_miss_calc_edges_tbl(i).child_name := cn_plancopy_util_pvt.check_name_length(
512 p_name => g_miss_calc_edges_tbl(i).child_name,
513 p_org_id => p_org_id,
514 p_type => 'EXPRESSION',
515 p_prefix => p_prefix);
516 g_miss_calc_edges_tbl(i).child_id
517 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'ChildId'),0)));
518 -- Get Formula Information
519 IF g_miss_calc_edges_tbl(i).parent_name = v_expression_rec.name THEN
520 SELECT COUNT(name) INTO l_exp_name_count
521 FROM cn_calc_sql_exps
522 WHERE name = g_miss_calc_edges_tbl(i).child_name
523 AND org_id = p_org_id;
524 IF l_exp_name_count = 0 THEN
525 fnd_message.set_name ('CN' , 'CN_COPY_EXP_EXP_MISS');
526 fnd_message.set_token('EXPRESSION_NAME_1',v_expression_rec.name);
527 fnd_message.set_token('EXPRESSION_NAME_2',g_miss_calc_edges_tbl(i).child_name);
528 fnd_file.put_line(fnd_file.log, fnd_message.get);
529 l_sql_fail_count := 1;
530 EXIT;
531 ELSE
532 SELECT calc_sql_exp_id INTO g_miss_calc_edges_tbl(i).child_id
533 FROM cn_calc_sql_exps
534 WHERE name = g_miss_calc_edges_tbl(i).child_name
535 AND org_id = p_org_id;
536 END IF;
537 v_expression_rec.expression_disp := REPLACE(v_expression_rec.expression_disp,l_exp_name_source,g_miss_calc_edges_tbl(i).child_name);
538 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);
539 END IF;
540 END IF;
541 END LOOP;
542 END IF;
543 END IF;
544
545 IF l_sql_fail_count = 0 THEN
546 --*********************************************************************
547 -- Parse Plan Element in Expression
548 --*********************************************************************
549 WHILE l_pe_exist LOOP
550 -- l_pe_count := instr(v_expression_rec.piped_sql_select, 'PE.',1,l_pe_num+1);
551 l_pe_count := instr(v_expression_rec.piped_sql_select, 'PE.',1);
552 IF l_pe_count = 0 THEN
553 l_pe_exist := FALSE;
554 ELSE
555 IF g_miss_pe_exp_tbl.COUNT > 0 THEN
556 FOR i IN g_miss_pe_exp_tbl.FIRST..g_miss_pe_exp_tbl.LAST LOOP
557 v_expression_rec.sql_select := REPLACE(v_expression_rec.sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.',g_miss_pe_exp_tbl(i).new_pe_id||'PE.');
558 v_expression_rec.piped_sql_select := REPLACE(v_expression_rec.piped_sql_select,g_miss_pe_exp_tbl(i).old_pe_id||'PE.',g_miss_pe_exp_tbl(i).new_pe_id||'PE.');
559 v_expression_rec.expression_disp := REPLACE(v_expression_rec.expression_disp,g_miss_pe_exp_tbl(i).old_pe_name,g_miss_pe_exp_tbl(i).new_pe_name);
560 v_expression_rec.piped_expression_disp := REPLACE(v_expression_rec.piped_expression_disp,g_miss_pe_exp_tbl(i).old_pe_name,g_miss_pe_exp_tbl(i).new_pe_name);
561 END LOOP;
562 l_pe_exist := FALSE;
563 ELSE
564 --fnd_message.set_name ('CN' , 'CN_COPY_EXP_PE_MTRC_MISS');
565 --fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
566 --fnd_message.set_token('PLAN_ELEMENT_NAME', g_miss_pe_exp_tbl(i).name);
567 --fnd_file.put_line(fnd_file.log, fnd_message.get);
568 l_sql_fail_count := 1;
569 l_pe_exist := FALSE;
570 EXIT;
571 END IF;
572 END IF;
573 END LOOP;
574
575 --*********************************************************************
576 -- Parse External Element in Expression
577 --*********************************************************************
578 l_ee_count := 0;
579 l_ee_piped_sql_from := v_expression_rec.piped_sql_from;
580 IF l_ee_piped_sql_from IS NOT NULL THEN
581 LOOP
582 l_ee_tab_name := SUBSTR(l_ee_piped_sql_from,1,INSTR(l_ee_piped_sql_from, '|')-1);
583 IF l_ee_tab_name <> 'DUAL' THEN
584 -- Check if Alias exists
585 l_ee_alias := instr(l_ee_tab_name, ' ',1);
586 IF l_ee_alias > 0 THEN
587 l_ee_tab_name_new := SUBSTR(l_ee_piped_sql_from,1,INSTR(l_ee_piped_sql_from, ' ')-1);
588 ELSE
589 l_ee_tab_name_new := l_ee_tab_name;
590 END IF;
591 -- Check object exists in Target System
592 SELECT COUNT(name) INTO l_ee_exist_obj_check
593 FROM cn_objects
594 WHERE org_id = p_org_id
595 AND calc_eligible_flag = 'Y'
596 AND object_type = 'TBL'
597 AND name = l_ee_tab_name_new;
598 -- Error Message - If table does not exist in Target System
599 IF l_ee_exist_obj_check = 0 THEN
600 fnd_message.set_name ('CN' , 'CN_COPY_EXP_EXT_MAP_MISS');
601 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
602 fnd_file.put_line(fnd_file.log, fnd_message.get);
603 l_sql_fail_count := 1;
604 EXIT;
605 END IF;
606 END IF;
607 -- Remove the table name which is checked and pick the next table
608 l_ee_piped_sql_from := REPLACE(l_ee_piped_sql_from,l_ee_tab_name||'|','');
609 l_ee_count := INSTR(l_ee_piped_sql_from, '|');
610 IF l_ee_count = 0 THEN
611 EXIT;
612 END IF;
613 END LOOP;
614 END IF;
615
616 IF l_sql_fail_count = 0 THEN
617 --*********************************************************************
618 -- Import Expression
619 --*********************************************************************
620 l_calc_sql_exp_id := NULL;
621 cn_calc_sql_exps_pvt.create_expression(
622 p_api_version => p_api_version,
623 p_init_msg_list => p_init_msg_list,
624 p_commit => p_commit,
625 p_validation_level => p_validation_level,
626 p_org_id => p_org_id,
627 p_name => v_expression_rec.name,
628 p_description => v_expression_rec.description,
629 p_expression_disp => v_expression_rec.expression_disp,
630 p_sql_select => v_expression_rec.sql_select,
631 p_sql_from => v_expression_rec.sql_from,
632 p_piped_expression_disp => v_expression_rec.piped_expression_disp,
633 p_piped_sql_select => v_expression_rec.piped_sql_select,
634 p_piped_sql_from => v_expression_rec.piped_sql_from,
635 x_calc_sql_exp_id => l_calc_sql_exp_id,
636 x_exp_type_code => v_expression_rec.exp_type_code,
637 x_status => v_expression_rec.status,
638 x_return_status => x_return_status,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data,
641 x_object_version_number => x_object_version_number);
642 IF x_return_status = fnd_api.g_ret_sts_success THEN
643 fnd_message.set_name ('CN' , 'CN_COPY_EXP_CREATE');
644 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
645 fnd_file.put_line(fnd_file.log, fnd_message.get);
646 COMMIT;
647 ELSE
648 ROLLBACK TO Create_Expression;
649 IF x_return_status = fnd_api.g_ret_sts_error THEN
650 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL_EXPECTED');
651 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
652 fnd_file.put_line(fnd_file.log, fnd_message.get);
653 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
654 END IF;
655 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
656 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL');
657 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
658 fnd_file.put_line(fnd_file.log, fnd_message.get);
659 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
660 END IF;
661 END IF;
662 ELSE
663 ROLLBACK TO Create_Expression;
664 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL');
665 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
666 fnd_file.put_line(fnd_file.log, fnd_message.get);
667 END IF;
668 ELSE
669 ROLLBACK TO Create_Expression;
670 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FAIL');
671 fnd_message.set_token('EXPRESSION_NAME',v_expression_rec.name);
672 fnd_file.put_line(fnd_file.log, fnd_message.get);
673 END IF;
674 END IF;
675 END IF;
676
677 --*********************************************************************
678 --****************** Parse Rate Dimension ***********************
679 --*********************************************************************
680 IF v_child_node_name = 'CnRateDimensionsVO' THEN
681 -- Rollback SavePoint
682 SAVEPOINT Create_RateDimension;
683 -- Intialising Rate Table record
684 v_rate_dimension_rec := NULL;
685 -- Get the CnRateDimensionsVORow
686 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
687 -- Cast Node to Element
688 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
689 -- Get the Rate Dimension Name
690 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
691 -- Get the Rate Dimension Name Value
692 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
693 -- Attach prefix to the Name Value
694 -- v_name_node_value_new := p_prefix || v_name_node_value;
695
696 -- Call common utility package for name length check
697 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
698 p_name => v_name_node_value,
699 p_org_id => p_org_id,
700 p_type => 'RATEDIMENSION',
701 p_prefix => p_prefix);
702
703 -- Check if Rate Dimension already exists in the Target Instance
704 SELECT COUNT(name) INTO l_reuse_count
705 FROM cn_rate_dimensions
706 WHERE name = v_name_node_value_new
707 AND org_id = p_org_id;
708
709 --If Rate Dimension exists then do not Insert, Else insert a new record.
710 IF l_reuse_count > 0 THEN
711 fnd_message.set_name ('CN' , 'CN_COPY_RD_REUSE');
712 fnd_message.set_token('RATE_DIMENSION_NAME',v_name_node_value_new);
713 fnd_file.put_line(fnd_file.log, fnd_message.get);
714 END IF;
715
716 IF l_reuse_count = 0 THEN
717 -- Get the other Rate Dimension Values
718 v_rate_dimension_rec.org_id := p_org_id;
719 v_rate_dimension_rec.name := v_name_node_value_new;
720 v_rate_dimension_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
721 v_rate_dimension_rec.dim_unit_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'DimUnitCode'),0)));
722 v_rate_dimension_rec.number_tier := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'NumberTier'),0)));
723
724 --*********************************************************************
725 -- Parse Rate Dim Tiers
726 --*********************************************************************
727 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
728 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
729 IF v_node_sibling_name_Next = 'CnRateDimTiersVO' THEN
730 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
731 IF v_node_sibling_length_Next > 0 THEN
732 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
733 -- Clearing the Temporary Table
734 v_rate_dim_tiers_tbl.DELETE;
735 g_miss_rate_dim_exp_tbl.DELETE;
736 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
737 -- Loop through all the child nodes of CnRateDimTiers Node
738 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
739 -- Cast Node to Element
740 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
741 -- Get the Rate Dim Tier Values
742 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)));
743 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)));
744 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)));
745 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)));
746 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)));
747 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)));
748
749 IF g_miss_rate_dim_exp_tbl(i).min_exp_name IS NOT NULL THEN
750 -- Call common utility package for name length check
751 g_miss_rate_dim_exp_tbl(i).min_exp_name := cn_plancopy_util_pvt.check_name_length(
752 p_name => g_miss_rate_dim_exp_tbl(i).min_exp_name,
753 p_org_id => p_org_id,
754 p_type => 'EXPRESSION',
755 p_prefix => p_prefix);
756 SELECT COUNT(name) INTO l_exp_name_count
757 FROM cn_calc_sql_exps
758 WHERE name = g_miss_rate_dim_exp_tbl(i).min_exp_name
759 AND org_id = p_org_id;
760
761 IF l_exp_name_count = 0 THEN
762 l_sql_fail_count := 1;
763 EXIT;
764 ELSE
765 SELECT calc_sql_exp_id INTO v_rate_dim_tiers_tbl(i).min_exp_id
766 FROM cn_calc_sql_exps
767 WHERE name = g_miss_rate_dim_exp_tbl(i).min_exp_name
768 AND org_id = p_org_id;
769 END IF;
770 END IF;
771 IF g_miss_rate_dim_exp_tbl(i).max_exp_name IS NOT NULL THEN
772 -- Call common utility package for name length check
773 g_miss_rate_dim_exp_tbl(i).max_exp_name := cn_plancopy_util_pvt.check_name_length(
774 p_name => g_miss_rate_dim_exp_tbl(i).max_exp_name,
775 p_org_id => p_org_id,
776 p_type => 'EXPRESSION',
777 p_prefix => p_prefix);
778
779 SELECT COUNT(name) INTO l_exp_name_count
780 FROM cn_calc_sql_exps
781 WHERE name = g_miss_rate_dim_exp_tbl(i).max_exp_name
782 AND org_id = p_org_id;
783 IF l_exp_name_count = 0 THEN
784 l_sql_fail_count := 1;
785 EXIT;
786 ELSE
787 SELECT calc_sql_exp_id INTO v_rate_dim_tiers_tbl(i).max_exp_id
788 FROM cn_calc_sql_exps
789 WHERE name = g_miss_rate_dim_exp_tbl(i).max_exp_name
790 AND org_id = p_org_id;
791 END IF;
792 END IF;
793 END LOOP;
794
795 IF l_sql_fail_count = 0 THEN
796 --*********************************************************************
797 -- Import Rate Dimension and Rate Dim Tiers
798 --*********************************************************************
799 l_rate_dimension_id := NULL;
800 cn_rate_dimensions_pvt.create_dimension(
801 p_api_version => p_api_version,
802 p_init_msg_list => p_init_msg_list,
803 p_commit => p_commit,
804 p_validation_level => p_validation_level,
805 p_name => v_rate_dimension_rec.name,
806 p_description => v_rate_dimension_rec.description,
807 p_dim_unit_code => v_rate_dimension_rec.dim_unit_code,
808 p_number_tier => v_rate_dimension_rec.number_tier,
809 p_tiers_tbl => v_rate_dim_tiers_tbl,
810 p_org_id => p_org_id,
811 x_rate_dimension_id => l_rate_dimension_id,
812 x_return_status => x_return_status,
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data);
815 IF x_return_status = fnd_api.g_ret_sts_success THEN
816 fnd_message.set_name ('CN' , 'CN_COPY_RD_CREATE');
817 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
818 fnd_file.put_line(fnd_file.log, fnd_message.get);
819 IF (g_miss_rate_dim_exp_tbl.COUNT > 0 AND v_rate_dimension_rec.dim_unit_code = 'EXPRESSION') THEN
820 FOR i IN g_miss_rate_dim_exp_tbl.FIRST..g_miss_rate_dim_exp_tbl.LAST LOOP
821 fnd_message.set_name ('CN' , 'CN_COPY_EXP_RD_ASSIGN');
822 fnd_message.set_token('EXPRESION_NAME',g_miss_rate_dim_exp_tbl(i).min_exp_name);
823 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
824 fnd_file.put_line(fnd_file.log, fnd_message.get);
825 fnd_message.set_name ('CN' , 'CN_COPY_EXP_RD_ASSIGN');
826 fnd_message.set_token('EXPRESION_NAME',g_miss_rate_dim_exp_tbl(i).max_exp_name);
827 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
828 fnd_file.put_line(fnd_file.log, fnd_message.get);
829 END LOOP;
830 END IF;
831 COMMIT;
832 ELSE
833 ROLLBACK TO Create_RateDimension;
834 IF x_return_status = fnd_api.g_ret_sts_error THEN
835 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL_EXPECTED');
836 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
837 fnd_file.put_line(fnd_file.log, fnd_message.get);
838 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
839 END IF;
840 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
841 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
842 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
843 fnd_file.put_line(fnd_file.log, fnd_message.get);
844 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
845 END IF;
846 END IF;
847 ELSE
848 ROLLBACK TO Create_RateDimension;
849 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
850 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
851 fnd_file.put_line(fnd_file.log, fnd_message.get);
852 END IF;
853 ELSE
854 ROLLBACK TO Create_RateDimension;
855 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
856 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
857 fnd_file.put_line(fnd_file.log, fnd_message.get);
858 END IF;
859 ELSE
860 ROLLBACK TO Create_RateDimension;
861 fnd_message.set_name ('CN' , 'CN_COPY_RD_FAIL');
862 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_dimension_rec.name);
863 fnd_file.put_line(fnd_file.log, fnd_message.get);
864 END IF;
865 END IF;
866 END IF;
867
868 --*********************************************************************
869 --*************** Parse Rate Table - Rate Schedule **************
870 --*********************************************************************
871 IF v_child_node_name = 'CnRateSchedulesVO' THEN
872 -- Rollback SavePoint
873 SAVEPOINT Create_RateSchedule;
874 -- Intialising Rate Table record
875 v_rate_table_rec := NULL;
876 -- Get the CnRateSchedulesVORow
877 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
878 -- Cast Node to Element
879 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
880 -- Get the Rate Table Name
881 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
882 -- Get the Rate Table Name Value
883 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
884
885 -- Attach prefix to the Name Value
886 -- Call common utility package for name length check
887 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
888 p_name => v_name_node_value,
889 p_org_id => p_org_id,
890 p_type => 'RATETABLE',
891 p_prefix => p_prefix);
892
893 -- Check if Rate Table already exists in the Target Instance
894 SELECT COUNT(name) INTO l_reuse_count
895 FROM cn_rate_schedules
896 WHERE name = v_name_node_value_new
897 AND org_id = p_org_id;
898
899 --If Rate Table exists then do not Insert otherwise insert a new Record.
900 IF l_reuse_count > 0 THEN
901 ROLLBACK TO Create_RateSchedule;
902 fnd_message.set_name ('CN' , 'CN_COPY_RT_REUSE');
903 fnd_message.set_token('RATE_TABLE_NAME',v_name_node_value_new);
904 fnd_file.put_line(fnd_file.log, fnd_message.get);
905 END IF;
906
907 IF l_reuse_count = 0 THEN
908 -- Get the other Rate Table values
909 v_rate_table_rec.name := v_name_node_value_new;
910 v_rate_table_rec.commission_unit_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'CommissionUnitCode'),0)));
911 v_rate_table_rec.org_id := p_org_id;
912 v_rate_table_rec.number_dim := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'NumberDim'),0)));
913 --*********************************************************************
914 -- Parse Rate Schedule Dims
915 --*********************************************************************
916 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
917 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
918 IF v_node_sibling_name_Next = 'CnRateSchDimsVO' THEN
919 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
920 IF v_node_sibling_length_Next > 0 THEN
921 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
922 -- Clearing the Temporary Table
923 v_rate_sch_dims_tbl.DELETE;
924 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
925 -- Loop through all the child nodes of CnRateDimTiers Node
926 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
927 -- Cast Node to Element
928 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
929 -- Get the Rate Dim Tier Values
930 v_rate_sch_dims_tbl(i).rate_dim_name
931 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateDimensionName'),0)));
932 -- Call common utility package for name length check
933 v_rate_sch_dims_tbl(i).rate_dim_name := cn_plancopy_util_pvt.check_name_length(
934 p_name => v_rate_sch_dims_tbl(i).rate_dim_name,
935 p_org_id => p_org_id,
936 p_type => 'RATEDIMENSION',
937 p_prefix => p_prefix);
938 v_rate_sch_dims_tbl(i).rate_dim_sequence
939 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateDimSequence'),0)));
940
941 SELECT COUNT(name) INTO l_rate_dim_name_count
942 FROM cn_rate_dimensions
943 WHERE name = v_rate_sch_dims_tbl(i).rate_dim_name
944 AND org_id = p_org_id;
945
946 IF l_rate_dim_name_count = 0 THEN
947 l_sql_fail_count := 1;
948 EXIT;
949 ELSE
950 SELECT rate_dimension_id INTO v_rate_sch_dims_tbl(i).rate_dimension_id
951 FROM cn_rate_dimensions
952 WHERE name = v_rate_sch_dims_tbl(i).rate_dim_name
953 AND org_id = p_org_id;
954 END IF;
955 END LOOP;
956
957 IF l_sql_fail_count = 0 THEN
958 --*********************************************************************
959 -- Parse Rate Tiers
960 --*********************************************************************
961 v_node_sibling_Previous := v_node_sibling_Next;
962 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
963 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
964 IF v_node_sibling_name_Next = 'CnRateTiersVO' THEN
965 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
966 IF v_node_sibling_length_Next > 0 THEN
967 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
968 -- Clearing the Temporary Table
969 v_rate_tiers_tbl.DELETE;
970 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
971 -- Loop through all the child nodes of CnRateDimTiers Node
972 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
973 -- Cast Node to Element
974 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
975 -- Get the Rate Dim Tier Values
976 v_rate_tiers_tbl(i).p_org_id := p_org_id;
977 v_rate_tiers_tbl(i).p_commission_amount
978 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CommissionAmount'),0)));
979 v_rate_tiers_tbl(i).p_rate_sequence
980 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateSequence'),0)));
981 END LOOP;
982 ELSE
983 ROLLBACK TO Create_RateSchedule;
984 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
985 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
986 fnd_file.put_line(fnd_file.log, fnd_message.get);
987 END IF;
988 END IF;
989 --*********************************************************************
990 -- Import Rate Table - Rate Schedule
991 --*********************************************************************
992 l_rate_schedule_id := NULL;
993 cn_multi_rate_schedules_pvt.create_schedule(
994 p_api_version => p_api_version,
995 p_init_msg_list => p_init_msg_list,
996 p_commit => p_commit,
997 p_validation_level => p_validation_level,
998 p_name => v_rate_table_rec.name,
999 p_commission_unit_code => v_rate_table_rec.commission_unit_code,
1000 p_number_dim => v_rate_table_rec.number_dim,
1001 p_dims_tbl => v_rate_sch_dims_tbl,
1002 p_org_id => p_org_id,
1003 x_rate_schedule_id => l_rate_schedule_id,
1004 x_return_status => x_return_status,
1005 x_msg_count => x_msg_count,
1006 x_msg_data => x_msg_data);
1007 IF x_return_status = fnd_api.g_ret_sts_success THEN
1008 --*********************************************************************
1009 -- Import Rate Tiers
1010 --*********************************************************************
1011 IF (v_rate_tiers_tbl.COUNT > 0) THEN
1012 FOR i IN v_rate_tiers_tbl.FIRST..v_rate_tiers_tbl.LAST LOOP
1013 cn_multi_rate_schedules_pvt.update_rate(
1014 p_rate_schedule_id => l_rate_schedule_id,
1015 p_rate_sequence => v_rate_tiers_tbl(i).p_rate_sequence,
1016 p_commission_amount => v_rate_tiers_tbl(i).p_commission_amount,
1017 p_object_version_number => x_object_version_number,
1018 p_org_id => p_org_id);
1019 END LOOP;
1020 END IF;
1021 fnd_message.set_name ('CN' , 'CN_COPY_RT_CREATE');
1022 fnd_message.set_token('RATE_TABLE_NAME',v_name_node_value_new);
1023 fnd_file.put_line(fnd_file.log, fnd_message.get);
1024 IF (v_rate_sch_dims_tbl.COUNT > 0) THEN
1025 FOR i IN v_rate_sch_dims_tbl.FIRST..v_rate_sch_dims_tbl.LAST LOOP
1026 fnd_message.set_name ('CN' , 'CN_COPY_RD_RT_ASSIGN');
1027 fnd_message.set_token('RATE_DIMENSION_NAME',v_rate_sch_dims_tbl(i).rate_dim_name);
1028 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1029 fnd_file.put_line(fnd_file.log, fnd_message.get);
1030 END LOOP;
1031 END IF;
1032 COMMIT;
1033 ELSE
1034 ROLLBACK TO Create_RateDimension;
1035 IF x_return_status = fnd_api.g_ret_sts_error THEN
1036 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL_EXPECTED');
1037 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1038 fnd_file.put_line(fnd_file.log, fnd_message.get);
1039 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1040 END IF;
1041 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1042 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1043 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1044 fnd_file.put_line(fnd_file.log, fnd_message.get);
1045 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1046 END IF;
1047 END IF;
1048 ELSE
1049 ROLLBACK TO Create_RateSchedule;
1050 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1051 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1052 fnd_file.put_line(fnd_file.log, fnd_message.get);
1053 END IF;
1054 ELSE
1055 ROLLBACK TO Create_RateSchedule;
1056 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1057 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1058 fnd_file.put_line(fnd_file.log, fnd_message.get);
1059 END IF;
1060 ELSE
1061 ROLLBACK TO Create_RateSchedule;
1062 fnd_message.set_name ('CN' , 'CN_COPY_RT_FAIL');
1063 fnd_message.set_token('RATE_TABLE_NAME',v_rate_table_rec.name);
1064 fnd_file.put_line(fnd_file.log, fnd_message.get);
1065 END IF;
1066 END IF;
1067 END IF;
1068
1069 --*********************************************************************
1070 --************************ Parse Formula ************************
1071 --*********************************************************************
1072 IF v_child_node_name = 'CnCalcFormulasVO' THEN
1073 -- Rollback SavePoint
1074 SAVEPOINT Create_Formula;
1075 -- Intialising formula record
1076 v_formula_rec := NULL;
1077 -- Get the CnCalcFormulasVORow
1078 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
1079 -- Cast Node to Element
1080 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
1081 -- Get the Formula Name
1082 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
1083 -- Get the Formula Name Value
1084 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
1085 -- Attach prefix to the Name Value
1086 -- v_name_node_value_new := p_prefix || v_name_node_value;
1087
1088 -- Call common utility package for name length check
1089 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
1090 p_name => v_name_node_value,
1091 p_org_id => p_org_id,
1092 p_type => 'FORMULA',
1093 p_prefix => p_prefix);
1094
1095 -- Check if Formula already exists in the Target Instance
1096 SELECT COUNT(name) INTO l_reuse_count
1097 FROM cn_calc_formulas
1098 WHERE name = v_name_node_value_new
1099 AND org_id = p_org_id;
1100
1101 --If Formula exists then do not Insert otherwise insert a new Record.
1102 IF l_reuse_count > 0 THEN
1103 fnd_message.set_name ('CN' , 'CN_COPY_FM_REUSE');
1104 fnd_message.set_token('FORMULA_NAME',v_name_node_value_new);
1105 fnd_file.put_line(fnd_file.log, fnd_message.get);
1106 END IF;
1107
1108 IF l_reuse_count = 0 THEN
1109 -- Get the other Formula values
1110 v_formula_rec.org_id := p_org_id;
1111 v_formula_rec.name := v_name_node_value_new;
1112 v_formula_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
1113 v_formula_rec.formula_status := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FormulaStatus'),0)));
1114 v_formula_rec.split_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SplitFlag'),0)));
1115 v_formula_rec.cumulative_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'CumulativeFlag'),0)));
1116 v_formula_rec.itd_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ItdFlag'),0)));
1117 v_formula_rec.trx_group_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'TrxGroupCode'),0)));
1118 v_formula_rec.threshold_all_tier_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ThresholdAllTierFlag'),0)));
1119 v_formula_rec.number_dim := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'NumberDim'),0)));
1120 v_formula_rec.formula_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FormulaType'),0)));
1121 v_formula_rec.modeling_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ModelingFlag'),0)));
1122 l_output_exp_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'OutputExpName'),0)));
1123 l_f_output_exp_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FOutputExpName'),0)));
1124 l_perf_measure_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PerfMeasureName'),0)));
1125
1126 IF l_output_exp_name IS NULL THEN
1127 l_sql_fail_count := 1;
1128 ELSE
1129 -- Call common utility package for name length check
1130 l_output_exp_name := cn_plancopy_util_pvt.check_name_length(
1131 p_name => l_output_exp_name,
1132 p_org_id => p_org_id,
1133 p_type => 'EXPRESSION',
1134 p_prefix => p_prefix);
1135
1136 SELECT COUNT(name) INTO l_exp_name_count
1137 FROM cn_calc_sql_exps
1138 WHERE name = l_output_exp_name
1139 AND org_id = p_org_id;
1140
1141 IF l_exp_name_count = 0 THEN
1142 l_sql_fail_count := 1;
1143 ELSE
1144 SELECT calc_sql_exp_id INTO v_formula_rec.output_exp_id
1145 FROM cn_calc_sql_exps
1146 WHERE name = l_output_exp_name
1147 AND org_id = p_org_id;
1148 END IF;
1149
1150 IF l_f_output_exp_name IS NOT NULL THEN
1151 -- Call common utility package for name length check
1152 l_f_output_exp_name := cn_plancopy_util_pvt.check_name_length(
1153 p_name => l_f_output_exp_name,
1154 p_org_id => p_org_id,
1155 p_type => 'EXPRESSION',
1156 p_prefix => p_prefix);
1157
1158 SELECT COUNT(name) INTO l_exp_name_count
1159 FROM cn_calc_sql_exps
1160 WHERE name = l_f_output_exp_name
1161 AND org_id = p_org_id;
1162 IF l_exp_name_count = 0 THEN
1163 l_sql_fail_count := 1;
1164 ELSE
1165 SELECT calc_sql_exp_id INTO v_formula_rec.f_output_exp_id
1166 FROM cn_calc_sql_exps
1167 WHERE name = l_f_output_exp_name
1168 AND org_id = p_org_id;
1169 END IF;
1170 END IF;
1171
1172 IF l_perf_measure_name IS NOT NULL THEN
1173 -- Call common utility package for name length check
1174 l_perf_measure_name := cn_plancopy_util_pvt.check_name_length(
1175 p_name => l_perf_measure_name,
1176 p_org_id => p_org_id,
1177 p_type => 'EXPRESSION',
1178 p_prefix => p_prefix);
1179
1180 SELECT COUNT(name) INTO l_exp_name_count
1181 FROM cn_calc_sql_exps
1182 WHERE name = l_perf_measure_name
1183 AND org_id = p_org_id;
1184
1185 IF l_exp_name_count = 0 THEN
1186 l_sql_fail_count := 1;
1187 ELSE
1188 SELECT calc_sql_exp_id INTO v_formula_rec.perf_measure_id
1189 FROM cn_calc_sql_exps
1190 WHERE name = l_perf_measure_name
1191 AND org_id = p_org_id;
1192 END IF;
1193 END IF;
1194 END IF;
1195 IF l_sql_fail_count = 0 THEN
1196 --*********************************************************************
1197 -- Parse Formula - Input Expression Assignment
1198 --*********************************************************************
1199 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
1200 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1201 IF v_node_sibling_name_Next = 'CnFormulaInputsVO' THEN
1202 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1203 IF v_node_sibling_length_Next > 0 THEN
1204 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1205 -- Clearing the Temporary Table
1206 v_input_exp_tbl.DELETE;
1207 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1208 -- Loop through all the child nodes of CnRateDimTiers Node
1209 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1210 -- Cast Node to Element
1211 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1212 -- Get the Rate Dim Tier Values
1213 v_input_exp_tbl(i).rate_dim_sequence
1214 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateDimSequence'),0)));
1215 v_input_exp_tbl(i).calc_exp_name
1216 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CalcSqlExpName'),0)));
1217 v_input_exp_tbl(i).calc_exp_status
1218 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CalcSqlExpStatus'),0)));
1219 v_input_exp_tbl(i).f_calc_exp_name
1220 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'FCalcSqlExpName'),0)));
1221 v_input_exp_tbl(i).f_calc_exp_status
1222 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'FCalcSqlExpStatus'),0)));
1223 v_input_exp_tbl(i).cumulative_flag
1224 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CumulativeFlag'),0)));
1225 v_input_exp_tbl(i).split_flag
1226 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'SplitFlag'),0)));
1227
1228 IF v_input_exp_tbl(i).calc_exp_name IS NULL THEN
1229 l_sql_fail_count := 1;
1230 EXIT;
1231 ELSE
1232 -- Call common utility package for name length check
1233 v_input_exp_tbl(i).calc_exp_name := cn_plancopy_util_pvt.check_name_length(
1234 p_name => v_input_exp_tbl(i).calc_exp_name,
1235 p_org_id => p_org_id,
1236 p_type => 'EXPRESSION',
1237 p_prefix => p_prefix);
1238
1239 SELECT COUNT(name) INTO l_exp_name_count
1240 FROM cn_calc_sql_exps
1241 WHERE name = v_input_exp_tbl(i).calc_exp_name
1242 AND org_id = p_org_id;
1243 IF l_exp_name_count = 0 THEN
1244 l_sql_fail_count := 1;
1245 EXIT;
1246 ELSE
1247 SELECT calc_sql_exp_id INTO v_input_exp_tbl(i).calc_sql_exp_id
1248 FROM cn_calc_sql_exps
1249 WHERE name = v_input_exp_tbl(i).calc_exp_name
1250 AND org_id = p_org_id;
1251 END IF;
1252 END IF;
1253
1254 IF v_input_exp_tbl(i).f_calc_exp_name IS NOT NULL THEN
1255 -- Call common utility package for name length check
1256 v_input_exp_tbl(i).f_calc_exp_name := cn_plancopy_util_pvt.check_name_length(
1257 p_name => v_input_exp_tbl(i).f_calc_exp_name,
1258 p_org_id => p_org_id,
1259 p_type => 'EXPRESSION',
1260 p_prefix => p_prefix);
1261
1262 SELECT COUNT(name) INTO l_exp_name_count
1263 FROM cn_calc_sql_exps
1264 WHERE name = v_input_exp_tbl(i).f_calc_exp_name
1265 AND org_id = p_org_id;
1266
1267 IF l_exp_name_count = 0 THEN
1268 l_sql_fail_count := 1;
1269 EXIT;
1270 ELSE
1271 SELECT calc_sql_exp_id INTO v_input_exp_tbl(i).f_calc_sql_exp_id
1272 FROM cn_calc_sql_exps_all
1273 WHERE name = v_input_exp_tbl(i).f_calc_exp_name
1274 AND org_id = p_org_id;
1275 END IF;
1276 END IF;
1277 END LOOP;
1278
1279 IF l_sql_fail_count = 0 THEN
1280 --*********************************************************************
1281 -- Parse Formula - Rate Table Assignment
1282 --*********************************************************************
1283 v_node_sibling_Previous := v_node_sibling_Next;
1284 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1285 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1286 IF v_node_sibling_name_Next = 'CnRtFormulaAsgnsVO' THEN
1287 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1288 IF v_node_sibling_length_Next > 0 THEN
1289 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1290 -- Clearing the Temporary Table
1291 v_rt_assign_tbl.DELETE;
1292 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1293 -- Loop through all the child nodes of CnRateDimTiers Node
1294 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1295 -- Cast Node to Element
1296 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1297 -- Get the Formula Rate Table Values
1298 v_rt_assign_tbl(i).start_date
1299 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
1300 v_rt_assign_tbl(i).end_date
1301 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
1302 v_rt_assign_tbl(i).rate_schedule_name
1303 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleName'),0)));
1304 v_rt_assign_tbl(i).rate_schedule_type
1305 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleType'),0)));
1306
1307 IF v_rt_assign_tbl(i).rate_schedule_name IS NULL THEN
1308 l_sql_fail_count := 1;
1309 EXIT;
1310 ELSE
1311 -- Call common utility package for name length check
1312 v_rt_assign_tbl(i).rate_schedule_name := cn_plancopy_util_pvt.check_name_length(
1313 p_name => v_rt_assign_tbl(i).rate_schedule_name,
1314 p_org_id => p_org_id,
1315 p_type => 'RATETABLE',
1316 p_prefix => p_prefix);
1317
1318 SELECT COUNT(name) INTO l_rate_schedule_name_count
1319 FROM cn_rate_schedules
1320 WHERE name = v_rt_assign_tbl(i).rate_schedule_name
1321 AND org_id = p_org_id;
1322
1323 IF l_rate_schedule_name_count = 0 THEN
1324 l_sql_fail_count := 1;
1325 EXIT;
1326 ELSE
1327 SELECT rate_schedule_id INTO v_rt_assign_tbl(i).rate_schedule_id
1328 FROM cn_rate_schedules
1329 WHERE name = v_rt_assign_tbl(i).rate_schedule_name
1330 AND org_id = p_org_id;
1331 END IF;
1332 END IF;
1333 END LOOP;
1334 ELSE
1335 l_sql_fail_count := 1;
1336 --ROLLBACK TO Create_Formula;
1337 --fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1338 --fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1339 --fnd_file.put_line(fnd_file.log, fnd_message.get);
1340 END IF;
1341 END IF;
1342
1343 IF l_sql_fail_count = 0 THEN
1344 --**********************************************
1345 -- Import Formula
1346 --**********************************************
1347 l_calc_formula_id := Null;
1348 v_formula_rec.calc_formula_id := NULL;
1349 cn_calc_formulas_pvt.create_formula(
1350 p_api_version => p_api_version,
1351 p_init_msg_list => p_init_msg_list,
1352 p_commit => p_commit,
1353 p_validation_level => p_validation_level,
1354 p_generate_packages => FND_API.G_TRUE,
1355 p_name => v_formula_rec.name,
1356 p_description => v_formula_rec.description,
1357 p_formula_type => v_formula_rec.formula_type,
1358 p_trx_group_code => v_formula_rec.trx_group_code,
1359 p_number_dim => v_formula_rec.number_dim,
1360 p_cumulative_flag => v_formula_rec.cumulative_flag,
1361 p_itd_flag => v_formula_rec.itd_flag,
1362 p_split_flag => v_formula_rec.split_flag,
1363 p_threshold_all_tier_flag => v_formula_rec.threshold_all_tier_flag,
1364 p_modeling_flag => v_formula_rec.modeling_flag,
1365 p_perf_measure_id => v_formula_rec.perf_measure_id,
1366 p_output_exp_id => v_formula_rec.output_exp_id,
1367 p_f_output_exp_id => v_formula_rec.f_output_exp_id,
1368 p_input_tbl => v_input_exp_tbl,
1369 p_rt_assign_tbl => v_rt_assign_tbl,
1370 p_org_id => p_org_id,
1371 x_calc_formula_id => l_calc_formula_id,
1372 x_formula_status => v_formula_rec.formula_status,
1373 x_return_status => x_return_status,
1374 x_msg_count => x_msg_count,
1375 x_msg_data => x_msg_data);
1376
1377 IF x_return_status = fnd_api.g_ret_sts_success THEN
1378 fnd_message.set_name ('CN' , 'CN_COPY_FM_CREATE');
1379 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1380 fnd_file.put_line(fnd_file.log, fnd_message.get);
1381 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1382 fnd_message.set_token('EXPRESSION_NAME',l_output_exp_name);
1383 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1384 fnd_file.put_line(fnd_file.log, fnd_message.get);
1385 IF l_f_output_exp_name IS NOT NULL THEN
1386 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1387 fnd_message.set_token('EXPRESSION_NAME',l_f_output_exp_name);
1388 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1389 fnd_file.put_line(fnd_file.log, fnd_message.get);
1390 END IF;
1391 IF l_perf_measure_name IS NOT NULL THEN
1392 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1393 fnd_message.set_token('EXPRESSION_NAME',l_perf_measure_name);
1394 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1395 fnd_file.put_line(fnd_file.log, fnd_message.get);
1396 END IF;
1397 IF (v_input_exp_tbl.COUNT > 0) THEN
1398 FOR i IN v_input_exp_tbl.FIRST..v_input_exp_tbl.LAST LOOP
1399 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1400 fnd_message.set_token('EXPRESSION_NAME',v_input_exp_tbl(i).calc_exp_name);
1401 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1402 fnd_file.put_line(fnd_file.log, fnd_message.get);
1403 IF v_input_exp_tbl(i).f_calc_exp_name IS NOT NULL THEN
1404 fnd_message.set_name ('CN' , 'CN_COPY_EXP_FM_ASSIGN');
1405 fnd_message.set_token('EXPRESSION_NAME',v_input_exp_tbl(i).f_calc_exp_name);
1406 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1407 fnd_file.put_line(fnd_file.log, fnd_message.get);
1408 END IF;
1409 END LOOP;
1410 END IF;
1411 IF (v_rt_assign_tbl.COUNT > 0) THEN
1412 FOR i IN v_rt_assign_tbl.FIRST..v_rt_assign_tbl.LAST LOOP
1413 fnd_message.set_name ('CN' , 'CN_COPY_RT_FM_ASSIGN');
1414 fnd_message.set_token('RATE_TABLE_NAME',v_rt_assign_tbl(i).rate_schedule_name);
1415 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1416 fnd_message.set_token('ASSIGNMENT_START_DATE', v_rt_assign_tbl(i).start_date);
1417 IF v_rt_assign_tbl(i).end_date IS NOT NULL THEN
1418 fnd_message.set_token('ASSIGNMENT_END_DATE', v_rt_assign_tbl(i).end_date);
1419 ELSE
1420 fnd_message.set_token('ASSIGNMENT_END_DATE', 'NULL');
1421 END IF;
1422 fnd_file.put_line(fnd_file.log, fnd_message.get);
1423 END LOOP;
1424 END IF;
1425 COMMIT;
1426 ELSE
1427 -- No ROLLBACK TO Create_Formula - Generate has a separate COMMIT cycle.
1428 IF x_return_status = fnd_api.g_ret_sts_error THEN
1429 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL_EXPECTED');
1430 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1431 fnd_file.put_line(fnd_file.log, fnd_message.get);
1432 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1433 END IF;
1434 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1435 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1436 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1437 fnd_file.put_line(fnd_file.log, fnd_message.get);
1438 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
1439 END IF;
1440 END IF;
1441 ELSE
1442 ROLLBACK TO Create_Formula;
1443 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1444 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1445 fnd_file.put_line(fnd_file.log, fnd_message.get);
1446 END IF;
1447 ELSE
1448 ROLLBACK TO Create_Formula;
1449 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1450 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1451 fnd_file.put_line(fnd_file.log, fnd_message.get);
1452 END IF;
1453 ELSE
1454 ROLLBACK TO Create_Formula;
1455 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1456 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1457 fnd_file.put_line(fnd_file.log, fnd_message.get);
1458 END IF;
1459 ELSE
1460 ROLLBACK TO Create_Formula;
1461 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1462 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1463 fnd_file.put_line(fnd_file.log, fnd_message.get);
1464 END IF;
1465 ELSE
1466 ROLLBACK TO Create_Formula;
1467 fnd_message.set_name ('CN' , 'CN_COPY_FM_FAIL');
1468 fnd_message.set_token('FORMULA_NAME',v_formula_rec.name);
1469 fnd_file.put_line(fnd_file.log, fnd_message.get);
1470 END IF;
1471 END IF;
1472 END IF;
1473
1474 --*********************************************************************
1475 --********************** Parse Plan Element *********************
1476 --*********************************************************************
1477 IF v_child_node_name = 'CnQuotasVO' THEN
1478 -- Rollback SavePoint
1479 SAVEPOINT Create_PlanElement;
1480 -- Intialising Rate Table record
1481 v_plan_element_rec := NULL;
1482 -- Get the CnQuotasVORow
1483 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
1484 -- Cast Node to Element
1485 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
1486 -- Get the Plan Element Name
1487 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
1488 -- Get the Plan Element Name Value
1489 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
1490 -- Attach prefix to the Name Value
1491 -- Call common utility package for name length check
1492 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
1493 p_name => v_name_node_value,
1494 p_org_id => p_org_id,
1495 p_type => 'PLANELEMENT',
1496 p_prefix => p_prefix);
1497
1498 -- Check if Plan Element already exists in the Target Instance
1499 SELECT COUNT(name) INTO l_reuse_count
1500 FROM cn_quotas_v
1501 WHERE name = v_name_node_value_new
1502 AND org_id = p_org_id;
1503
1504 --If Plan Element exists then do not Insert otherwise insert a new Record.
1505 IF l_reuse_count > 0 THEN
1506 fnd_message.set_name ('CN' , 'CN_COPY_PE_REUSE');
1507 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1508 fnd_file.put_line(fnd_file.log, fnd_message.get);
1509 END IF;
1510
1511 -- If Plan Element does not exist then proceed further.
1512 IF l_reuse_count = 0 THEN
1513 -- Old value of plan element for Interdependent cases
1514 g_miss_pe_exp_rec.old_pe_name := v_name_node_value;
1515 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)));
1516 -- Get the other Plan Element values
1517 v_plan_element_rec.quota_id := NULL;
1518 v_plan_element_rec.name := v_name_node_value_new;
1519 v_plan_element_rec.element_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaTypeCode'),0)));
1520 v_plan_element_rec.target := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Target'),0)));
1521 v_plan_element_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
1522 v_plan_element_rec.payment_amount := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PaymentAmount'),0)));
1523 v_plan_element_rec.org_id := p_org_id;
1524 v_plan_element_rec.incentive_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'IncentiveTypeCode'),0)));
1525 v_plan_element_rec.payee_assign_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PayeeAssignFlag'),0)));
1526 v_plan_element_rec.performance_goal := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PerformanceGoal'),0)));
1527 v_plan_element_rec.status := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaStatus'),0)));
1528 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)));
1529 v_plan_element_rec.quota_group_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'QuotaGroupCode'),0)));
1530 v_plan_element_rec.payment_group_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PaymentGroupCode'),0)));
1531 v_plan_element_rec.indirect_credit := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'IndirectCredit'),0)));
1532 v_plan_element_rec.calc_formula_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'FormulaName'),0)));
1533 v_plan_element_rec.credit_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'CreditTypeName'),0)));
1534 v_plan_element_rec.interval_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'IntervalTypeName'),0)));
1535 -- Other Attributes Start
1536 v_plan_element_rec.package_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PackageName'),0)));
1537 v_plan_element_rec.attribute_category := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'AttributeCategory'),0)));
1538 v_plan_element_rec.attribute1 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute1'),0)));
1539 v_plan_element_rec.attribute2 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute2'),0)));
1540 v_plan_element_rec.attribute3 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute3'),0)));
1541 v_plan_element_rec.attribute4 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute4'),0)));
1542 v_plan_element_rec.attribute5 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute5'),0)));
1543 v_plan_element_rec.attribute6 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute6'),0)));
1544 v_plan_element_rec.attribute7 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute7'),0)));
1545 v_plan_element_rec.attribute8 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute8'),0)));
1546 v_plan_element_rec.attribute9 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute9'),0)));
1547 v_plan_element_rec.attribute10 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute10'),0)));
1548 v_plan_element_rec.attribute11 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute11'),0)));
1549 v_plan_element_rec.attribute12 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute12'),0)));
1550 v_plan_element_rec.attribute13 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute13'),0)));
1551 v_plan_element_rec.attribute14 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute14'),0)));
1552 v_plan_element_rec.attribute15 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute15'),0)));
1553 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)));
1554 v_plan_element_rec.vesting_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'VestingFlag'),0)));
1555 v_plan_element_rec.period_type := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'PeriodType'),0)));
1556 -- New Column added to cn_quotas table in R12+
1557 v_plan_element_rec.sreps_enddated_flag := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'SalesrepsEnddatedFlag'),0)));
1558 -- Liability and Expense Account Information
1559 l_expense_acc_desc := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'ExpenseAccountDesc'),0)));
1560 l_liability_acc_desc := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'LiabilityAccountDesc'),0)));
1561 -- Other Attributes End
1562
1563 -- Find Expense Account information in Target System
1564 l_expense_account_id := 0;
1565 IF l_expense_acc_desc IS NOT NULL THEN
1566 OPEN c_expense_account_id (l_expense_acc_desc);
1567 FETCH c_expense_account_id INTO l_expense_account_id;
1568 CLOSE c_expense_account_id;
1569 IF l_expense_account_id IS NULL THEN
1570 v_plan_element_rec.expense_account_id := NULL;
1571 ELSE
1572 v_plan_element_rec.expense_account_id := l_expense_account_id;
1573 END IF;
1574 ELSE
1575 v_plan_element_rec.expense_account_id := NULL;
1576 END IF;
1577
1578 -- Find Liability Account information in Target System
1579 l_liab_account_id := 0;
1580 IF l_liability_acc_desc IS NOT NULL THEN
1581 OPEN c_liab_account_id (l_liability_acc_desc);
1582 FETCH c_liab_account_id INTO l_liab_account_id;
1583 CLOSE c_liab_account_id;
1584 IF l_liab_account_id IS NULL THEN
1585 v_plan_element_rec.liability_account_id := NULL;
1586 ELSE
1587 v_plan_element_rec.liability_account_id := l_liab_account_id;
1588 END IF;
1589 ELSE
1590 v_plan_element_rec.liability_account_id := NULL;
1591 END IF;
1592
1593 -- Check for External Formula
1594 IF v_plan_element_rec.element_type = 'EXTERNAL' AND
1595 v_plan_element_rec.package_name IS NULL THEN
1596 fnd_message.set_name ('CN' , 'CN_COPY_PE_EXT_FM_MISS');
1597 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1598 fnd_file.put_line(fnd_file.log, fnd_message.get);
1599 l_sql_fail_count := 1;
1600 END IF;
1601
1602 -- Check for Formula Name in Target System.
1603 IF v_plan_element_rec.element_type = 'FORMULA' THEN
1604 -- Call common utility package for name length check
1605 v_plan_element_rec.calc_formula_name := cn_plancopy_util_pvt.check_name_length(
1606 p_name => v_plan_element_rec.calc_formula_name,
1607 p_org_id => p_org_id,
1608 p_type => 'FORMULA',
1609 p_prefix => p_prefix);
1610 SELECT COUNT(name) INTO l_formula_name_count
1611 FROM cn_calc_formulas
1612 WHERE name = v_plan_element_rec.calc_formula_name
1613 AND org_id = p_org_id;
1614 IF l_formula_name_count = 0 THEN
1615 l_sql_fail_count := 1;
1616 END IF;
1617 END IF;
1618
1619 -- Check Interval Type Name in Target System
1620 SELECT COUNT(name) INTO l_int_type_count
1621 FROM cn_interval_types
1622 WHERE name = v_plan_element_rec.interval_name
1623 AND org_id = p_org_id;
1624 IF l_int_type_count = 0 THEN
1625 fnd_message.set_name ('CN' , 'CN_COPY_PE_INT_TYPE_MISS');
1626 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1627 fnd_message.set_token('INTERVAL_TYPE_NAME',v_plan_element_rec.interval_name);
1628 fnd_file.put_line(fnd_file.log, fnd_message.get);
1629 l_sql_fail_count := 1;
1630 END IF;
1631
1632 -- Check Credit Type Name in Target System
1633 SELECT COUNT(name) INTO l_crd_type_count
1634 FROM cn_credit_types
1635 WHERE name = v_plan_element_rec.credit_type
1636 AND org_id = p_org_id;
1637 IF l_crd_type_count = 0 THEN
1638 fnd_message.set_name ('CN' , 'CN_COPY_PE_CRD_TYPE_MISS');
1639 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1640 fnd_message.set_token('CREDIT_TYPE_NAME',v_plan_element_rec.credit_type);
1641 fnd_file.put_line(fnd_file.log, fnd_message.get);
1642 l_sql_fail_count := 1;
1643 END IF;
1644
1645 -- If all of the above are NOT NULL then proceed further
1646 IF l_sql_fail_count = 0 THEN
1647 -- Check Payment Group Code in Target System
1648 SELECT COUNT(lookup_code) INTO l_pmt_group_code
1649 FROM cn_lookups
1650 WHERE lookup_type = 'PAYMENT_GROUP_CODE'
1651 AND lookup_code = v_plan_element_rec.payment_group_code;
1652
1653 -- If Payment Group does not exists, Set it to 'STANDARD'
1654 IF l_pmt_group_code = 0 THEN
1655 fnd_message.set_name ('CN' , 'CN_COPY_PE_PMT_GRP_DFLT');
1656 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1657 fnd_message.set_token('PAYMENT_GROUP_CODE_NAME',v_plan_element_rec.payment_group_code);
1658 fnd_file.put_line(fnd_file.log, fnd_message.get);
1659 v_plan_element_rec.payment_group_code := 'STANDARD';
1660 END IF;
1661
1662 -- Check if Start Date and End Date values are passed for change.
1663 IF p_start_date IS NULL THEN
1664 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');
1665 ELSE
1666 v_plan_element_rec.start_date := p_start_date;
1667 END IF;
1668
1669 IF p_start_date IS NULL AND p_end_date IS NULL THEN
1670 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');
1671 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
1672 v_plan_element_rec.end_date := p_end_date;
1673 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
1674 v_plan_element_rec.end_date := NULL;
1675 END IF;
1676
1677 --*********************************************************************
1678 -- Parse Quota Rules - Revenue Class Assignments
1679 --*********************************************************************
1680 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
1681 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1682 IF v_node_sibling_name_Next = 'CnQuotaRulesVO' THEN
1683 -- Initializing for at least one rev class count
1684 l_rev_class_least_count := 0;
1685 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1686 -- Clearing the Temporary Table
1687 v_revenue_class_tbl.DELETE;
1688 IF v_node_sibling_length_Next > 0 THEN
1689 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1690 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1691 -- Loop through all the child nodes of CnQuotaAssignsVO Node
1692 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1693 -- Cast Node to Element
1694 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1695
1696 -- Find If Revenue Class exists in the Target System
1697 l_rev_class_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClsName'),0)));
1698 SELECT COUNT(name) into l_rev_class_name_count
1699 FROM cn_revenue_classes
1700 WHERE name = l_rev_class_name
1701 AND org_id = p_org_id;
1702
1703 -- Get the Revenue Class Values - Only If Revenue Class exists in the Target System
1704 IF l_rev_class_name_count <> 0 THEN
1705 v_revenue_class_tbl(i).rev_class_name
1706 := l_rev_class_name;
1707 v_revenue_class_tbl(i).rev_class_target
1708 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Target'),0)));
1709 v_revenue_class_tbl(i).rev_class_payment_amount
1710 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PaymentAmount'),0)));
1711 v_revenue_class_tbl(i).rev_class_performance_goal
1712 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PerformanceGoal'),0)));
1713 v_revenue_class_tbl(i).description
1714 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Description'),0)));
1715 v_revenue_class_tbl(i).org_id
1716 := p_org_id;
1717 -- Other Attributes Start
1718 v_revenue_class_tbl(i).attribute_category
1719 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'AttributeCategory'),0)));
1720 v_revenue_class_tbl(i).attribute1
1721 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
1722 v_revenue_class_tbl(i).attribute2
1723 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
1724 v_revenue_class_tbl(i).attribute3
1725 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
1726 v_revenue_class_tbl(i).attribute4
1727 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
1728 v_revenue_class_tbl(i).attribute5
1729 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
1730 v_revenue_class_tbl(i).attribute6
1731 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
1732 v_revenue_class_tbl(i).attribute7
1733 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
1734 v_revenue_class_tbl(i).attribute8
1735 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
1736 v_revenue_class_tbl(i).attribute9
1737 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
1738 v_revenue_class_tbl(i).attribute10
1739 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
1740 v_revenue_class_tbl(i).attribute11
1741 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
1742 v_revenue_class_tbl(i).attribute12
1743 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
1744 v_revenue_class_tbl(i).attribute13
1745 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
1746 v_revenue_class_tbl(i).attribute14
1747 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
1748 v_revenue_class_tbl(i).attribute15
1749 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
1750 v_revenue_class_tbl(i).rev_class_name_old
1751 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClassNameOld'),0)));
1752 -- Other Attributes End
1753
1754 --Check for atleast one revenue class assign to Plan Element
1755 l_rev_class_least_count := 1;
1756 ELSE
1757 fnd_message.set_name ('CN' , 'CN_COPY_PE_REV_CLS_MISS');
1758 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1759 fnd_message.set_token('PRODUCT_NAME',l_rev_class_name);
1760 fnd_file.put_line(fnd_file.log, fnd_message.get);
1761 END IF;
1762 END LOOP;
1763
1764 -- If atleast one revenue class exists then proceed further
1765 IF l_rev_class_least_count = 1 THEN
1766 --*********************************************************************
1767 -- Parse Quota Rule Uplifts
1768 --*********************************************************************
1769 v_node_sibling_Previous := v_node_sibling_Next;
1770 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1771 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1772 IF v_node_sibling_name_Next = 'CnQuotaRuleUpliftsVO' THEN
1773 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1774 IF v_node_sibling_length_Next > 0 THEN
1775 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1776 -- Clearing the Temporary Table
1777 v_rev_uplift_tbl.DELETE;
1778 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1779 -- Loop through all the child nodes of CnQuotaAssignsVO Node
1780 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1781 -- Cast Node to Element
1782 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1783
1784 -- Find Revenue Class existing in the Target System
1785 l_uplift_start_date
1786 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
1787 l_uplift_end_date
1788 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
1789 l_rev_class_name
1790 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClsName'),0)));
1791
1792 -- Check for Id
1793 SELECT COUNT(name) INTO l_rev_class_name_count
1794 FROM cn_revenue_classes
1795 WHERE name = l_rev_class_name
1796 AND org_id = p_org_id;
1797
1798 -- Uplift Factors start date and end date should fall inside Plan Element date range.
1799 IF l_uplift_start_date >= v_plan_element_rec.start_date AND
1800 l_uplift_end_date <= NVL(v_plan_element_rec.end_date,l_uplift_end_date) THEN
1801
1802 -- Get the Revenue Class Values - Only If Revenue Class exists in the Target System
1803 IF l_rev_class_name_count <> 0 THEN
1804 -- Get the Quota Assign Values
1805 v_rev_uplift_tbl(i).rev_class_name
1806 := l_rev_class_name;
1807 v_rev_uplift_tbl(i).start_date
1808 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
1809 v_rev_uplift_tbl(i).end_date
1810 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
1811 v_rev_uplift_tbl(i).rev_class_payment_uplift
1812 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PaymentFactor'),0)));
1813 v_rev_uplift_tbl(i).rev_class_quota_uplift
1814 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'QuotaFactor'),0)));
1815 v_rev_uplift_tbl(i).org_id
1816 := p_org_id;
1817 -- Other Attributes Start
1818 v_rev_uplift_tbl(i).attribute_category
1819 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'AttributeCategory'),0)));
1820 v_rev_uplift_tbl(i).attribute1
1821 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
1822 v_rev_uplift_tbl(i).attribute2
1823 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
1824 v_rev_uplift_tbl(i).attribute3
1825 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
1826 v_rev_uplift_tbl(i).attribute4
1827 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
1828 v_rev_uplift_tbl(i).attribute5
1829 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
1830 v_rev_uplift_tbl(i).attribute6
1831 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
1832 v_rev_uplift_tbl(i).attribute7
1833 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
1834 v_rev_uplift_tbl(i).attribute8
1835 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
1836 v_rev_uplift_tbl(i).attribute9
1837 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
1838 v_rev_uplift_tbl(i).attribute10
1839 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
1840 v_rev_uplift_tbl(i).attribute11
1841 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
1842 v_rev_uplift_tbl(i).attribute12
1843 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
1844 v_rev_uplift_tbl(i).attribute13
1845 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
1846 v_rev_uplift_tbl(i).attribute14
1847 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
1848 v_rev_uplift_tbl(i).attribute15
1849 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
1850 v_rev_uplift_tbl(i).rev_class_name_old
1851 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClassNameOld'),0)));
1852 v_rev_uplift_tbl(i).start_date_old
1853 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDateOld'),0))),'YYYY-MM-DD');
1854 v_rev_uplift_tbl(i).end_date_old
1855 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDateOld'),0))),'YYYY-MM-DD');
1856 -- Other Attributes End
1857 END IF;
1858 ELSE
1859 fnd_message.set_name ('CN' , 'CN_COPY_PE_FCTRS_OUT_RANGE');
1860 fnd_message.set_token('PLAN_ELEMENT_NAME',v_name_node_value_new);
1861 fnd_file.put_line(fnd_file.log, fnd_message.get);
1862 END IF;
1863 END LOOP;
1864 END IF;
1865 END IF;
1866 --*********************************************************************
1867 -- Parse Transaction Factors
1868 --*********************************************************************
1869 IF v_node_sibling_name_Next = 'CnQuotaRuleUpliftsVO' THEN
1870 v_node_sibling_Previous := v_node_sibling_Next;
1871 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1872 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1873 END IF;
1874 IF v_node_sibling_name_Next = 'CnTrxFactorsVO' THEN
1875 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1876 IF v_node_sibling_length_Next > 0 THEN
1877 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1878 -- Clearing the Temporary Table
1879 v_trx_factor_tbl.DELETE;
1880 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1881 -- Loop through all the child nodes of CnTrxFactorsVO Node
1882 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1883 -- Cast Node to Element
1884 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1885
1886 -- Find Revenue Class existing in the Target System
1887 l_rev_class_name :=
1888 dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RevClsName'),0)));
1889 SELECT COUNT(name) INTO l_rev_class_name_count
1890 FROM cn_revenue_classes
1891 WHERE name = l_rev_class_name
1892 AND org_id = p_org_id;
1893
1894 -- Get the Revenue Class Values - Only If Revenue Class exists in the Target System
1895 IF l_rev_class_name_count <> 0 THEN
1896 -- Get the Quota Assign Values
1897 v_trx_factor_tbl(i).trx_type
1898 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'TrxType'),0)));
1899 v_trx_factor_tbl(i).event_factor
1900 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EventFactor'),0)));
1901 v_trx_factor_tbl(i).rev_class_name
1902 := l_rev_class_name;
1903 v_trx_factor_tbl(i).org_id
1904 := p_org_id;
1905 END IF;
1906 END LOOP;
1907 END IF;
1908 END IF;
1909 --*********************************************************************
1910 -- Parse RT Quota Assigns
1911 --*********************************************************************
1912 v_node_sibling_Previous := v_node_sibling_Next;
1913 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
1914 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
1915 IF v_node_sibling_name_Next = 'CnRtQuotaAsgnsVO' THEN
1916 -- Initializing formula and RT count check in PE
1917 l_rt_fm_notexist_count := 0;
1918 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
1919 IF v_node_sibling_length_Next > 0 THEN
1920 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
1921 -- Clearing the Temporary Table
1922 v_rt_quota_asgns_tbl.DELETE;
1923 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
1924 -- Loop through all the child nodes of CnQuotaAssignsVO Node
1925 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
1926 -- Cast Node to Element
1927 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
1928 -- Get the Quota Assign Values
1929 v_rt_quota_asgns_tbl(i).rate_schedule_name
1930 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleName'),0)));
1931 v_rt_quota_asgns_tbl(i).calc_formula_name
1932 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'CalcFormulaName'),0)));
1933 v_rt_quota_asgns_tbl(i).start_date
1934 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDate'),0))),'YYYY-MM-DD');
1935 v_rt_quota_asgns_tbl(i).end_date
1936 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDate'),0))),'YYYY-MM-DD');
1937 v_rt_quota_asgns_tbl(i).org_id
1938 := p_org_id;
1939 -- Other Attributes Start
1940 v_rt_quota_asgns_tbl(i).attribute_category
1941 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'AttributeCategory'),0)));
1942 v_rt_quota_asgns_tbl(i).attribute1
1943 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute1'),0)));
1944 v_rt_quota_asgns_tbl(i).attribute2
1945 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute2'),0)));
1946 v_rt_quota_asgns_tbl(i).attribute3
1947 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute3'),0)));
1948 v_rt_quota_asgns_tbl(i).attribute4
1949 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute4'),0)));
1950 v_rt_quota_asgns_tbl(i).attribute5
1951 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute5'),0)));
1952 v_rt_quota_asgns_tbl(i).attribute6
1953 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute6'),0)));
1954 v_rt_quota_asgns_tbl(i).attribute7
1955 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute7'),0)));
1956 v_rt_quota_asgns_tbl(i).attribute8
1957 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute8'),0)));
1958 v_rt_quota_asgns_tbl(i).attribute9
1959 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute9'),0)));
1960 v_rt_quota_asgns_tbl(i).attribute10
1961 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute10'),0)));
1962 v_rt_quota_asgns_tbl(i).attribute11
1963 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute11'),0)));
1964 v_rt_quota_asgns_tbl(i).attribute12
1965 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute12'),0)));
1966 v_rt_quota_asgns_tbl(i).attribute13
1967 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute13'),0)));
1968 v_rt_quota_asgns_tbl(i).attribute14
1969 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute14'),0)));
1970 v_rt_quota_asgns_tbl(i).attribute15
1971 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'Attribute15'),0)));
1972 v_rt_quota_asgns_tbl(i).rate_schedule_name_old
1973 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'RateScheduleNameOld'),0)));
1974 v_rt_quota_asgns_tbl(i).start_date_old
1975 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'StartDateOld'),0))),'YYYY-MM-DD');
1976 v_rt_quota_asgns_tbl(i).end_date_old
1977 := to_date(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'EndDateOld'),0))),'YYYY-MM-DD');
1978 -- Other Attributes End
1979
1980 -- Call common utility package for name length check
1981 v_rt_quota_asgns_tbl(i).rate_schedule_name := cn_plancopy_util_pvt.check_name_length(
1982 p_name => v_rt_quota_asgns_tbl(i).rate_schedule_name,
1983 p_org_id => p_org_id,
1984 p_type => 'RATETABLE',
1985 p_prefix => p_prefix);
1986 -- Check for Rate Table Name existence in Target System
1987 SELECT COUNT(name) INTO l_rate_schedule_name_count
1988 FROM cn_rate_schedules
1989 WHERE name = v_rt_quota_asgns_tbl(i).rate_schedule_name
1990 AND org_id = p_org_id;
1991
1992 -- Call common utility package for name length check
1993 -- Only if quota type is 'FORMULA'
1994 IF v_plan_element_rec.element_type = 'FORMULA' THEN
1995 v_rt_quota_asgns_tbl(i).calc_formula_name := cn_plancopy_util_pvt.check_name_length(
1996 p_name => v_rt_quota_asgns_tbl(i).calc_formula_name,
1997 p_org_id => p_org_id,
1998 p_type => 'FORMULA',
1999 p_prefix => p_prefix);
2000 -- Check for Formula Name existence in Target System
2001 SELECT COUNT(name) INTO l_formula_name_count
2002 FROM cn_calc_formulas
2003 WHERE name = v_rt_quota_asgns_tbl(i).calc_formula_name
2004 AND org_id = p_org_id;
2005 END IF;
2006 -- If Rate Table does not exist, do not create Plan Element
2007 IF l_rate_schedule_name_count = 0 THEN
2008 l_rt_fm_notexist_count := 1;
2009 EXIT;
2010 END IF;
2011 -- If Formula does not exist, do not create Plan Element
2012 IF v_plan_element_rec.element_type = 'FORMULA' AND l_formula_name_count = 0 THEN
2013 l_rt_fm_notexist_count := 1;
2014 EXIT;
2015 END IF;
2016 END LOOP;
2017 END IF;
2018
2019 -- If Rate Table and Formula exists, proceed further
2020 IF l_rt_fm_notexist_count = 0 THEN
2021 --*********************************************************************
2022 -- Parse Period Quotas
2023 --*********************************************************************
2024 v_node_sibling_Previous := v_node_sibling_Next;
2025 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_node_sibling_Previous);
2026 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
2027 IF v_node_sibling_name_Next = 'CnPeriodQuotasVO' THEN
2028 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
2029 IF v_node_sibling_length_Next > 0 THEN
2030 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
2031 -- Clearing the Temporary Table
2032 v_period_quotas_tbl.DELETE;
2033 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
2034 -- Loop through all the child nodes of CnQuotaAssignsVO Node
2035 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2036 -- Cast Node to Element
2037 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2038 -- Checking Period Status and Period Range
2039 l_period_name := NULL;
2040 l_period_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodName'),0)));
2041 SELECT count(period_name) INTO l_period_exist_count
2042 FROM cn_period_statuses
2043 WHERE period_name = l_period_name
2044 AND org_id = p_org_id;
2045
2046 IF l_period_exist_count <> 0 THEN
2047 SELECT end_date INTO l_period_end_date
2048 FROM cn_period_statuses
2049 WHERE period_name = l_period_name
2050 AND org_id = p_org_id;
2051
2052 IF p_end_date IS NULL AND NVL(v_plan_element_rec.end_date,l_period_end_date) >= l_period_end_date THEN
2053 -- Get the Quota Assign Values
2054 v_period_quotas_tbl(i).period_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodName'),0)));
2055 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)));
2056 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)));
2057 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)));
2058 v_period_quotas_tbl(i).org_id := p_org_id;
2059 -- Other Attributes Start
2060 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)));
2061 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)));
2062 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)));
2063 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)));
2064 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)));
2065 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)));
2066 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)));
2067 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)));
2068 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)));
2069 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)));
2070 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)));
2071 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)));
2072 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)));
2073 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)));
2074 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)));
2075 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)));
2076 -- Other Attributes End
2077 END IF;
2078 IF p_end_date is NOT NULL AND p_end_date > l_period_end_date THEN
2079 -- Get the Quota Assign Values
2080 v_period_quotas_tbl(i).period_name := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_sibling_cast_Next,'PeriodName'),0)));
2081 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)));
2082 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)));
2083 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)));
2084 v_period_quotas_tbl(i).org_id := p_org_id;
2085 -- Other Attributes Start
2086 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)));
2087 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)));
2088 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)));
2089 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)));
2090 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)));
2091 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)));
2092 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)));
2093 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)));
2094 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)));
2095 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)));
2096 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)));
2097 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)));
2098 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)));
2099 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)));
2100 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)));
2101 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)));
2102 -- Other Attributes End
2103 END IF;
2104 END IF;
2105 END LOOP;
2106 -- Clearing the Temporary Table to pass it as Null
2107 -- v_period_quotas_tbl.DELETE;
2108 END IF;
2109 END IF;
2110
2111 --Call to Plan Element Public API to create Plan Element
2112 cn_plan_element_pub.create_plan_element (
2113 p_api_version => p_api_version,
2114 p_init_msg_list => p_init_msg_list,
2115 p_commit => p_commit,
2116 p_validation_level => p_validation_level,
2117 x_return_status => x_return_status,
2118 x_msg_count => x_msg_count,
2119 x_msg_data => x_msg_data,
2120 p_plan_element_rec => v_plan_element_rec,
2121 p_revenue_class_rec_tbl => v_revenue_class_tbl,
2122 p_rev_uplift_rec_tbl => v_rev_uplift_tbl,
2123 p_trx_factor_rec_tbl => v_trx_factor_tbl,
2124 p_period_quotas_rec_tbl => v_period_quotas_tbl,
2125 p_rt_quota_asgns_rec_tbl => v_rt_quota_asgns_tbl,
2126 x_loading_status => x_loading_status,
2127 p_is_duplicate => 'N');
2128
2129 IF x_return_status = fnd_api.g_ret_sts_success THEN
2130 -- Log Message for Plan Element creation.
2131 fnd_message.set_name ('CN' , 'CN_COPY_PE_CREATE');
2132 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2133 fnd_message.set_token('PLAN_ELEMENT_START_DATE',v_plan_element_rec.start_date);
2134 IF v_plan_element_rec.end_date IS NOT NULL THEN
2135 fnd_message.set_token('PLAN_ELEMENT_END_DATE', v_plan_element_rec.end_date);
2136 ELSE
2137 fnd_message.set_token('PLAN_ELEMENT_END_DATE', 'NULL');
2138 END IF;
2139 fnd_file.put_line(fnd_file.log, fnd_message.get);
2140 -- Log Message for Formula to Quota Assignment
2141 fnd_message.set_name ('CN' , 'CN_COPY_FM_PE_ASSIGN');
2142 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2143 fnd_message.set_token('FORMULA_NAME',v_plan_element_rec.calc_formula_name);
2144 fnd_file.put_line(fnd_file.log, fnd_message.get);
2145 -- Log Message for Rate Table to Quota Assignment
2146 IF (v_rt_quota_asgns_tbl.COUNT > 0) THEN
2147 FOR i IN v_rt_quota_asgns_tbl.FIRST..v_rt_quota_asgns_tbl.LAST LOOP
2148 fnd_message.set_name ('CN' , 'CN_COPY_RT_PE_ASSIGN');
2149 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2150 fnd_message.set_token('RATE_TABLE_NAME',v_rt_quota_asgns_tbl(i).rate_schedule_name);
2151 fnd_message.set_token('ASSIGNMENT_START_DATE',v_rt_quota_asgns_tbl(i).start_date);
2152 IF v_rt_quota_asgns_tbl(i).end_date IS NOT NULL THEN
2153 fnd_message.set_token('ASSIGNMENT_END_DATE', v_rt_quota_asgns_tbl(i).end_date);
2154 ELSE
2155 fnd_message.set_token('ASSIGNMENT_END_DATE', 'NULL');
2156 END IF;
2157 fnd_file.put_line(fnd_file.log, fnd_message.get);
2158 END LOOP;
2159 END IF;
2160 COMMIT;
2161 -- New value of plan element for Interdependent cases
2162 g_miss_pe_exp_rec.new_pe_name := v_plan_element_rec.name;
2163 SELECT COUNT(name) INTO l_new_pe_name
2164 FROM cn_quotas_v
2165 WHERE name = v_plan_element_rec.name;
2166 IF l_new_pe_name > 0 THEN
2167 SELECT quota_id INTO g_miss_pe_exp_rec.new_pe_id
2168 FROM cn_quotas_v
2169 WHERE name = v_plan_element_rec.name;
2170 l_pe_counter := l_pe_counter + 1;
2171 g_miss_pe_exp_tbl(l_pe_counter).old_pe_name := g_miss_pe_exp_rec.old_pe_name;
2172 g_miss_pe_exp_tbl(l_pe_counter).old_pe_id := g_miss_pe_exp_rec.old_pe_id;
2173 g_miss_pe_exp_tbl(l_pe_counter).new_pe_name := g_miss_pe_exp_rec.new_pe_name;
2174 g_miss_pe_exp_tbl(l_pe_counter).new_pe_id := g_miss_pe_exp_rec.new_pe_id;
2175 END IF;
2176 ELSE
2177 ROLLBACK TO Create_PlanElement;
2178 IF x_return_status = fnd_api.g_ret_sts_error THEN
2179 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL_EXPECTED');
2180 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2181 fnd_file.put_line(fnd_file.log, fnd_message.get);
2182 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2183 END IF;
2184 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2185 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2186 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2187 fnd_file.put_line(fnd_file.log, fnd_message.get);
2188 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2189 END IF;
2190 END IF;
2191 ELSE
2192 ROLLBACK TO Create_PlanElement;
2193 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2194 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2195 fnd_file.put_line(fnd_file.log, fnd_message.get);
2196 END IF;
2197 ELSE
2198 ROLLBACK TO Create_PlanElement;
2199 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2200 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2201 fnd_file.put_line(fnd_file.log, fnd_message.get);
2202 END IF;
2203 ELSE
2204 ROLLBACK TO Create_PlanElement;
2205 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2206 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2207 fnd_file.put_line(fnd_file.log, fnd_message.get);
2208 END IF;
2209 ELSE
2210 ROLLBACK TO Create_PlanElement;
2211 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2212 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2213 fnd_file.put_line(fnd_file.log, fnd_message.get);
2214 END IF;
2215 ELSE
2216 ROLLBACK TO Create_PlanElement;
2217 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2218 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2219 fnd_file.put_line(fnd_file.log, fnd_message.get);
2220 END IF;
2221 ELSE
2222 ROLLBACK TO Create_PlanElement;
2223 fnd_message.set_name ('CN' , 'CN_COPY_PE_FAIL');
2224 fnd_message.set_token('PLAN_ELEMENT_NAME',v_plan_element_rec.name);
2225 fnd_file.put_line(fnd_file.log, fnd_message.get);
2226 END IF;
2227 END IF;
2228 END IF;
2229
2230 --*********************************************************************
2231 --****************** Parse Compensation Plan ********************
2232 --*********************************************************************
2233 IF v_child_node_name = 'CnCompPlansVO' THEN
2234 -- Rollback SavePoint
2235 SAVEPOINT Create_CompPlan;
2236 -- Intialising Rate Table record
2237 v_comp_plan_rec := NULL;
2238 -- Get the CnCompPlansVORow
2239 v_node_first_child := dbms_xmldom.getFirstChild(v_child_node);
2240 -- Cast Node to Element
2241 v_element_cast := dbms_xmldom.makeElement(v_node_first_child);
2242 -- Get the Compensation Plan Name
2243 v_name_node := dbms_xmldom.getChildrenByTagName(v_element_cast,'Name');
2244 -- Get the Compensation Plan Name Value
2245 v_name_node_value := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(v_name_node,0)));
2246 -- Attach prefix to the Name Value
2247 -- Call common utility package for name length check
2248 v_name_node_value_new := cn_plancopy_util_pvt.check_name_length(
2249 p_name => v_name_node_value,
2250 p_org_id => p_org_id,
2251 p_type => 'PLAN',
2252 p_prefix => p_prefix);
2253
2254 -- Check if Compensation Plan already exists in the Target Instance
2255 SELECT COUNT(name) INTO l_reuse_count
2256 FROM cn_comp_plans
2257 WHERE name = v_name_node_value_new
2258 AND org_id = p_org_id;
2259
2260 --If Compensation Plan exits then do not Insert otherwise insert a new Record.
2261 IF l_reuse_count > 0 THEN
2262 fnd_message.set_name ('CN' , 'CN_COPY_CP_REUSE');
2263 fnd_message.set_token('PLAN_NAME',v_name_node_value_new);
2264 fnd_file.put_line(fnd_file.log, fnd_message.get);
2265 p_reuse_obj_count := p_reuse_obj_count + 1;
2266 END IF;
2267
2268 IF l_reuse_count = 0 THEN
2269 -- Get other Compensation Plan values
2270 v_comp_plan_rec.name := v_name_node_value_new;
2271 v_comp_plan_rec.description := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Description'),0)));
2272 v_comp_plan_rec.status_code := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'StatusCode'),0)));
2273 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)));
2274 v_comp_plan_rec.org_id := p_org_id;
2275 -- Start Date parameter Logic
2276 IF p_start_date IS NULL THEN
2277 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');
2278 ELSE
2279 v_comp_plan_rec.start_date := p_start_date;
2280 END IF;
2281 -- End Date parameter Logic
2282 IF p_start_date IS NULL AND p_end_date IS NULL THEN
2283 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');
2284 ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL THEN
2285 v_comp_plan_rec.end_date := p_end_date;
2286 ELSIF p_start_date IS NOT NULL AND p_end_date IS NULL THEN
2287 v_comp_plan_rec.end_date := NULL;
2288 END IF;
2289
2290 -- Other Attributes Start
2291 v_comp_plan_rec.attribute_category := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'AttributeCategory'),0)));
2292 v_comp_plan_rec.attribute1 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute1'),0)));
2293 v_comp_plan_rec.attribute2 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute2'),0)));
2294 v_comp_plan_rec.attribute3 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute3'),0)));
2295 v_comp_plan_rec.attribute4 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute4'),0)));
2296 v_comp_plan_rec.attribute5 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute5'),0)));
2297 v_comp_plan_rec.attribute6 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute6'),0)));
2298 v_comp_plan_rec.attribute7 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute7'),0)));
2299 v_comp_plan_rec.attribute8 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute8'),0)));
2300 v_comp_plan_rec.attribute9 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute9'),0)));
2301 v_comp_plan_rec.attribute10 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute10'),0)));
2302 v_comp_plan_rec.attribute11 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute11'),0)));
2303 v_comp_plan_rec.attribute12 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute12'),0)));
2304 v_comp_plan_rec.attribute13 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute13'),0)));
2305 v_comp_plan_rec.attribute14 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute14'),0)));
2306 v_comp_plan_rec.attribute15 := dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(dbms_xmldom.item(dbms_xmldom.getChildrenByTagName(v_element_cast,'Attribute15'),0)));
2307 -- Other Attributes End
2308
2309 --*********************************************************************
2310 -- Import Compensation Plan
2311 --*********************************************************************
2312 l_comp_plan_id := NULL;
2313 v_comp_plan_rec.comp_plan_id := NULL;
2314 cn_comp_plan_pvt.create_comp_plan(
2315 p_api_version => p_api_version,
2316 p_init_msg_list => p_init_msg_list,
2317 p_commit => p_commit,
2318 p_validation_level => p_validation_level,
2319 p_comp_plan => v_comp_plan_rec,
2320 x_comp_plan_id => l_comp_plan_id,
2321 x_return_status => x_return_status,
2322 x_msg_count => x_msg_count,
2323 x_msg_data => x_msg_data);
2324
2325 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2326 l_sql_fail_count := 1;
2327 END IF;
2328
2329 IF l_sql_fail_count = 0 THEN
2330 --*********************************************************************
2331 -- Parse Quota Assignments
2332 --*********************************************************************
2333 v_node_sibling_Next := dbms_xmldom.getNextSibling(v_child_node);
2334 v_node_sibling_name_Next := dbms_xmldom.getNodeNAME(v_node_sibling_Next);
2335 IF v_node_sibling_name_Next = 'CnQuotaAssignsVO' THEN
2336 v_node_sibling_length_Next := dbms_xmldom.getLength(dbms_xmldom.getChildNodes(v_node_sibling_Next));
2337 IF v_node_sibling_length_Next > 0 THEN
2338 v_node_sibling_list_Next := dbms_xmldom.getChildNodes(v_node_sibling_Next);
2339 -- Clearing the Temporary Table
2340 v_quota_assign_tbl.DELETE;
2341 FOR i IN 0..v_node_sibling_length_Next-1 LOOP
2342 -- Loop through all the child nodes of CnQuotaAssignsVO Node
2343 v_node_sibling_child_Next := dbms_xmldom.item(v_node_sibling_list_Next,i);
2344 -- Cast Node to Element
2345 v_element_sibling_cast_Next := dbms_xmldom.makeElement(v_node_sibling_child_Next);
2346 -- Get the Quota Assign Values
2347 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)));
2348 v_quota_assign_tbl(i).org_id := p_org_id;
2349 v_quota_assign_tbl(i).comp_plan_id := l_comp_plan_id;
2350 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)));
2351 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)));
2352
2353 -- Call common utility package for name length check
2354 v_quota_assign_tbl(i).name := cn_plancopy_util_pvt.check_name_length(
2355 p_name => v_quota_assign_tbl(i).name,
2356 p_org_id => p_org_id,
2357 p_type => 'PLANELEMENT',
2358 p_prefix => p_prefix);
2359
2360 -- Check if PE exists in the Target System
2361 l_pe_name_count := 0;
2362 SELECT count(name) INTO l_pe_name_count
2363 FROM cn_quotas_v
2364 WHERE name = v_quota_assign_tbl(i).name;
2365 IF l_pe_name_count > 0 THEN
2366 SELECT quota_id,start_date,end_date
2367 INTO v_quota_assign_tbl(i).quota_id,v_quota_assign_tbl(i).start_date, v_quota_assign_tbl(i).end_date
2368 FROM cn_quotas_v
2369 WHERE name = v_quota_assign_tbl(i).name
2370 AND org_id = p_org_id;
2371 ELSE
2372 ROLLBACK TO Create_CompPlan;
2373 -- Comp Plan Fail Message
2374 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2375 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2376 fnd_file.put_line(fnd_file.log, fnd_message.get);
2377 -- Failed Plan Name collection for Summary Section
2378 IF l_failed_plan_name IS NULL THEN
2379 l_failed_plan_name := v_comp_plan_rec.name;
2380 ELSE
2381 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2382 END IF;
2383 l_sql_fail_count := 1;
2384 EXIT;
2385 END IF;
2386 END LOOP;
2387
2388 IF l_sql_fail_count = 0 THEN
2389 --*********************************************************************
2390 -- Import Quota Assignments
2391 --*********************************************************************
2392 IF (v_quota_assign_tbl.COUNT > 0) THEN
2393 -- Check for at least one PE to be assigned.
2394 l_quota_asgn_count := 0;
2395 FOR i IN v_quota_assign_tbl.FIRST..v_quota_assign_tbl.LAST LOOP
2396 IF (v_quota_assign_tbl(i).start_date <= NVL(v_comp_plan_rec.end_date,v_quota_assign_tbl(i).start_date) AND
2397 NVL(v_quota_assign_tbl(i).end_date,v_comp_plan_rec.start_date) >= v_comp_plan_rec.start_date) THEN
2398 cn_quota_assign_pvt.create_quota_assign(
2399 p_api_version => p_api_version,
2400 p_init_msg_list => p_init_msg_list,
2401 p_commit => p_commit,
2402 p_validation_level => p_validation_level,
2403 p_quota_assign => v_quota_assign_tbl(i),
2404 x_return_status => x_return_status,
2405 x_msg_count => x_msg_count,
2406 x_msg_data => x_msg_data);
2407 IF x_return_status = fnd_api.g_ret_sts_success THEN
2408 -- Validate the CompPlan
2409 v_comp_plan_rec.comp_plan_id := l_comp_plan_id;
2410 cn_comp_plan_pvt.validate_comp_plan(
2411 p_api_version => p_api_version,
2412 p_init_msg_list => p_init_msg_list,
2413 p_commit => p_commit,
2414 p_validation_level => p_validation_level,
2415 p_comp_plan => v_comp_plan_rec,
2416 x_return_status => x_return_status,
2417 x_msg_count => x_msg_count,
2418 x_msg_data => x_msg_data);
2419 IF x_return_status = fnd_api.g_ret_sts_success THEN
2420 fnd_message.set_name ('CN' , 'CN_COPY_PE_ASSIGN');
2421 fnd_message.set_token('PLAN_NAME',v_name_node_value_new);
2422 fnd_message.set_token('PLAN_ELEMENT_NAME',v_quota_assign_tbl(i).name);
2423 fnd_file.put_line(fnd_file.log, fnd_message.get);
2424 l_quota_asgn_count := 1;
2425 ELSE
2426 l_sql_fail_count := 1;
2427 EXIT;
2428 END IF;
2429 ELSE
2430 l_sql_fail_count := 1;
2431 EXIT;
2432 END IF;
2433 ELSE
2434 fnd_message.set_name ('CN' , 'CN_COPY_PE_OUT_RANGE');
2435 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2436 fnd_message.set_token('PLAN_ELEMENT_NAME',v_quota_assign_tbl(i).name);
2437 fnd_file.put_line(fnd_file.log, fnd_message.get);
2438 END IF;
2439 END LOOP;
2440 IF l_sql_fail_count = 0 AND l_quota_asgn_count = 1 THEN
2441 fnd_message.set_name ('CN' , 'CN_COPY_CP_CREATE');
2442 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2443 fnd_message.set_token('PLAN_START_DATE',v_comp_plan_rec.start_date);
2444 IF v_comp_plan_rec.end_date IS NOT NULL THEN
2445 fnd_message.set_token('PLAN_END_DATE', v_comp_plan_rec.end_date);
2446 ELSE
2447 fnd_message.set_token('PLAN_END_DATE', 'NULL');
2448 END IF;
2449 fnd_file.put_line(fnd_file.log, fnd_message.get);
2450 p_success_obj_count := p_success_obj_count + 1;
2451 COMMIT;
2452 ELSE
2453 ROLLBACK TO Create_CompPlan;
2454 -- Comp Plan Fail Message
2455 IF x_return_status = fnd_api.g_ret_sts_error THEN
2456 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL_EXPECTED');
2457 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2458 fnd_file.put_line(fnd_file.log, fnd_message.get);
2459 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2460 END IF;
2461 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2462 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2463 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2464 fnd_file.put_line(fnd_file.log, fnd_message.get);
2465 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2466 END IF;
2467 -- Failed Plan Name collection for Summary Section
2468 IF l_failed_plan_name IS NULL THEN
2469 l_failed_plan_name := v_comp_plan_rec.name;
2470 ELSE
2471 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2472 END IF;
2473 END IF;
2474 ELSE
2475 ROLLBACK TO Create_CompPlan;
2476 -- Comp Plan Fail Message
2477 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2478 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2479 fnd_file.put_line(fnd_file.log, fnd_message.get);
2480 -- Failed Plan Name collection for Summary Section
2481 IF l_failed_plan_name IS NULL THEN
2482 l_failed_plan_name := v_comp_plan_rec.name;
2483 ELSE
2484 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2485 END IF;
2486 END IF;
2487 END IF;
2488 ELSE
2489 ROLLBACK TO Create_CompPlan;
2490 -- Comp Plan Fail Message
2491 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2492 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2493 fnd_file.put_line(fnd_file.log, fnd_message.get);
2494 -- Failed Plan Name collection for Summary Section
2495 IF l_failed_plan_name IS NULL THEN
2496 l_failed_plan_name := v_comp_plan_rec.name;
2497 ELSE
2498 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2499 END IF;
2500 END IF;
2501 ELSE
2502 ROLLBACK TO Create_CompPlan;
2503 -- Comp Plan Fail Message
2504 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2505 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2506 fnd_file.put_line(fnd_file.log, fnd_message.get);
2507 -- Failed Plan Name collection for Summary Section
2508 IF l_failed_plan_name IS NULL THEN
2509 l_failed_plan_name := v_comp_plan_rec.name;
2510 ELSE
2511 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2512 END IF;
2513 END IF;
2514 ELSE
2515 ROLLBACK TO Create_CompPlan;
2516 -- Comp Plan Fail Message
2517 IF x_return_status = fnd_api.g_ret_sts_error THEN
2518 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL_EXPECTED');
2519 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2520 fnd_file.put_line(fnd_file.log, fnd_message.get);
2521 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2522 END IF;
2523 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2524 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAIL');
2525 fnd_message.set_token('PLAN_NAME',v_comp_plan_rec.name);
2526 fnd_file.put_line(fnd_file.log, fnd_message.get);
2527 fnd_file.put_line(fnd_file.log, '***ERROR: '||x_msg_data);
2528 END IF;
2529 -- Failed Plan Name collection for Summary Section
2530 IF l_failed_plan_name IS NULL THEN
2531 l_failed_plan_name := v_comp_plan_rec.name;
2532 ELSE
2533 l_failed_plan_name := l_failed_plan_name||', '||v_comp_plan_rec.name;
2534 END IF;
2535 END IF;
2536 END IF;
2537 END IF;
2538
2539 /* ****************************** Main Loop End ************************** */
2540 END LOOP;
2541 fnd_file.put_line(fnd_file.log, '**************************************************************');
2542 fnd_file.put_line(fnd_file.log, '******************** END - PLAN COPY IMPORT ******************');
2543 fnd_file.put_line(fnd_file.log, '**************************************************************');
2544
2545 -- ****************************************************************
2546 -- *********** Summary of Import Process Log Messages ***********
2547 -- ****************************************************************
2548 fnd_file.put_line(fnd_file.log, '**************************************************************');
2549 fnd_file.put_line(fnd_file.log, '***************************** SUMMARY ************************');
2550 fnd_file.put_line(fnd_file.log, '**************************************************************');
2551 -- Number of plans to Import
2552 fnd_message.set_name ('CN' , 'CN_COPY_CP_REQ_COUNT');
2553 fnd_message.set_token('COUNT',p_object_count);
2554 fnd_file.put_line(fnd_file.log, fnd_message.get);
2555 -- Number of Plans successfully created in target
2556 fnd_message.set_name ('CN' , 'CN_COPY_CP_SUCCESS_COUNT');
2557 fnd_message.set_token('COUNT',p_success_obj_count);
2558 fnd_file.put_line(fnd_file.log, fnd_message.get);
2559 -- Number of Plans reused in tagret
2560 fnd_message.set_name ('CN' , 'CN_COPY_CP_REUSE_COUNT');
2561 fnd_message.set_token('COUNT',p_reuse_obj_count);
2562 fnd_file.put_line(fnd_file.log, fnd_message.get);
2563 -- Number of Plans which were not imported due to error
2564 fnd_message.set_name ('CN' , 'CN_COPY_CP_FAILED_COUNT');
2565 fnd_message.set_token('LIST',l_failed_plan_name);
2566 fnd_file.put_line(fnd_file.log, fnd_message.get);
2567 fnd_file.put_line(fnd_file.log, '**************************************************************');
2568 fnd_file.put_line(fnd_file.log, '***************************** SUMMARY ************************');
2569 fnd_file.put_line(fnd_file.log, '**************************************************************');
2570
2571 -- Set the Import Status to 'COMPLETE' OR 'FAILED'
2572 IF p_object_count = p_success_obj_count + p_reuse_obj_count THEN
2573 x_import_status := 'COMPLETED';
2574 ELSE
2575 x_import_status := 'FAILED';
2576 END IF;
2577 END IF;
2578 -- Standard call to get message count
2579 FND_MSG_PUB.Count_And_Get(
2580 p_count => x_msg_count,
2581 p_data => x_msg_data,
2582 p_encoded => FND_API.G_FALSE);
2583 EXCEPTION
2584 WHEN FND_API.G_EXC_ERROR THEN
2585 x_return_status := FND_API.G_RET_STS_ERROR;
2586 x_import_status := 'FAILED';
2587 FND_MSG_PUB.count_and_get(
2588 p_count => x_msg_count,
2589 p_data => x_msg_data,
2590 p_encoded => FND_API.G_FALSE);
2591 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2593 x_import_status := 'FAILED';
2594 FND_MSG_PUB.count_and_get(
2595 p_count => x_msg_count,
2596 p_data => x_msg_data,
2597 p_encoded => FND_API.G_FALSE);
2598 WHEN OTHERS THEN
2599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2600 x_import_status := 'FAILED';
2601 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2602 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name);
2603 END IF;
2604 FND_MSG_PUB.count_and_get(
2605 p_count => x_msg_count,
2606 p_data => x_msg_data,
2607 p_encoded => FND_API.G_FALSE);
2608 END Parse_XML;
2609
2610 /**********************************************************************/
2611 /* API Body - Finish */
2612 /**********************************************************************/
2613 END CN_COMP_PLAN_XMLCOPY_PVT;