[Home] [Help]
305: --
306: -- Notes : Create SQL expressions that will be used in calculation.
307: -- 1) Validate the expression and return the result in x_status (Valid or Invalid)
308: -- 2) Classify expressions into sub types for formula validation and dynamic rate table validation
309: -- 3) If there are embedded expressions, record the embedding relations in cn_calc_edges
310: --
311: -- End of comments
312: PROCEDURE create_expression(
313: p_api_version IN NUMBER
448: INSTR(p_piped_sql_select, '(', l_select_start, 1) = l_select_start
449: AND (l_select_end - l_select_start) > 1
450: ) THEN
451: -- insert calc edges (calc edges has no table handler)
452: INSERT INTO cn_calc_edges
453: (
454: org_id
455: , calc_edge_id
456: , parent_id
462: , last_update_date
463: , last_updated_by
464: )
465: SELECT org_id
466: , cn_calc_edges_s.NEXTVAL
467: , x_calc_sql_exp_id
468: , calc_sql_exp_id
469: , 'EE'
470: , SYSDATE
480: TO_NUMBER(
481: SUBSTR(p_piped_sql_select, l_dummy, INSTR(p_piped_sql_select, '_', l_dummy, 1) - l_dummy)
482: );
483:
484: INSERT INTO cn_calc_edges
485: (
486: org_id
487: , calc_edge_id
488: , parent_id
495: , last_updated_by
496: )
497: VALUES (
498: p_org_id
499: , cn_calc_edges_s.NEXTVAL
500: , x_calc_sql_exp_id
501: , l_calc_formula_id
502: , 'FE'
503: , SYSDATE
586: --
587: -- Notes : Update SQL expressions that will be used in calculation.
588: -- 1) validate the expression and return the result in x_status (Valid or Invalid)
589: -- 2) re-classify expressions into sub types for formula validation and dynamic rate table validation
590: -- 3) adjust the corresponding embedding relations in cn_calc_edges
591: -- 4) if the expression is used, update the parent expressions, formulas accordingly
592: --
593: -- End of comments
594: PROCEDURE update_expression(
631: CURSOR parent_exist IS
632: SELECT 1
633: FROM DUAL
634: WHERE (EXISTS(SELECT 1
635: FROM cn_calc_edges
636: WHERE child_id = p_calc_sql_exp_id AND edge_type = 'EE'))
637: OR (
638: EXISTS(SELECT 1
639: FROM cn_calc_formulas
761: AND (l_select_end - l_select_start) > 1
762: ) THEN
763: l_exp_names := l_exp_names || l_token || '|';
764:
765: INSERT INTO cn_calc_edges
766: (
767: org_id
768: , calc_edge_id
769: , parent_id
775: , last_update_date
776: , last_updated_by
777: )
778: SELECT org_id
779: , cn_calc_edges_s.NEXTVAL
780: , p_calc_sql_exp_id
781: , calc_sql_exp_id
782: , 'EE'
783: , SYSDATE
788: FROM cn_calc_sql_exps
789: WHERE NAME = l_token
790: AND NOT EXISTS(
791: SELECT 1
792: FROM cn_calc_edges
793: WHERE parent_id = p_calc_sql_exp_id
794: AND child_id = (SELECT calc_sql_exp_id
795: FROM cn_calc_sql_exps
796: WHERE NAME = l_token AND edge_type = 'EE'));
801: SUBSTR(p_piped_sql_select, l_dummy, INSTR(p_piped_sql_select, '_', l_dummy, 1) - l_dummy)
802: );
803: l_formula_ids := l_formula_ids || l_calc_formula_id || '|';
804:
805: INSERT INTO cn_calc_edges
806: (
807: org_id
808: , calc_edge_id
809: , parent_id
815: , last_update_date
816: , last_updated_by
817: )
818: SELECT p_org_id
819: , cn_calc_edges_s.NEXTVAL
820: , p_calc_sql_exp_id
821: , l_calc_formula_id
822: , 'FE'
823: , SYSDATE
827: , fnd_global.user_id
828: FROM DUAL
829: WHERE NOT EXISTS(
830: SELECT 1
831: FROM cn_calc_edges
832: WHERE parent_id = p_calc_sql_exp_id
833: AND child_id = l_calc_formula_id
834: AND edge_type = 'FE');
835: END IF;
838: END LOOP;
839:
840: -- delete obsolete calc edges
841: --IF (l_formula_ids <> '|') THEN
842: DELETE FROM cn_calc_edges
843: WHERE parent_id = p_calc_sql_exp_id
844: AND INSTR(l_formula_ids, '|' || child_id || '|', 1, 1) = 0
845: AND edge_type = 'FE';
846:
846:
847: --END IF;
848:
849: --IF (l_exp_names <> '|') THEN
850: DELETE FROM cn_calc_edges a
851: WHERE a.parent_id = p_calc_sql_exp_id
852: AND a.edge_type = 'EE'
853: AND NOT EXISTS(
854: SELECT 1
917: -- Initial version 1.0
918: --
919: -- Notes : Delete an expression
920: -- 1) if it is used, it can not be deleted
921: -- 2) delete the embedding relations in cn_calc_edges if there is any
922: --
923: -- End of comments
924: PROCEDURE delete_expression(
925: p_api_version IN NUMBER
938: CURSOR parent_exist IS
939: SELECT 1
940: FROM DUAL
941: WHERE (EXISTS(SELECT 1
942: FROM cn_calc_edges
943: WHERE child_id = p_calc_sql_exp_id AND edge_type = 'EE'))
944: OR (
945: EXISTS(SELECT 1
946: FROM cn_calc_formulas
988: END IF;
989:
990: cn_calc_sql_exps_pkg.delete_row(x_calc_sql_exp_id => p_calc_sql_exp_id);
991:
992: DELETE FROM cn_calc_edges e
993: WHERE edge_type IN('EE', 'FE') AND NOT EXISTS(SELECT 1
994: FROM cn_calc_sql_exps
995: WHERE calc_sql_exp_id = e.parent_id);
996:
1071: CURSOR parent_names IS
1072: SELECT name
1073: FROM cn_calc_sql_exps
1074: WHERE calc_sql_exp_id IN (SELECT parent_id
1075: FROM cn_calc_edges
1076: CONNECT BY child_id = PRIOR parent_id
1077: AND edge_type = 'EE'
1078: START WITH child_id = p_calc_sql_exp_id
1079: AND edge_type = 'EE')
1521: AND ccf.calc_formula_id = p_current_id;
1522:
1523: CURSOR get_child_edges IS
1524: SELECT child_id
1525: FROM cn_calc_edges
1526: WHERE edge_type = 'FE' AND parent_id = p_current_id;
1527:
1528: CURSOR get_sql_sel IS
1529: SELECT DBMS_LOB.SUBSTR(sql_select)
1670: OR perf_measure_id = p_current_id;
1671:
1672: CURSOR get_parent_exps IS
1673: SELECT parent_id exp_id
1674: FROM cn_calc_edges
1675: WHERE edge_type = 'FE' AND child_id = p_current_id;
1676:
1677: l_current_id NUMBER;
1678: l_pe_tbl cn_calc_sql_exps_pvt.num_tbl_type;