1 package body PA_COST_BASE_VIEW_PKG as
2 -- $Header: PAXCICVB.pls 120.1 2005/08/23 19:19:53 spunathi noship $
3
4 -- constant
5 NO_DATA_FOUND_ERR constant number := 100;
6
7 ------------------------------------------------------------------------------
8 procedure check_unique(cp_structure IN varchar2,
9 c_base IN varchar2,
10 c_base_type IN varchar2,
11 status IN OUT NOCOPY number)
12 is
13 dummy number;
14 begin
15
16 status := 0;
17
18 /*
19 * Performance related changes:
20 * the view pa_cost_base_v does not get merged.
21 * The query looks into the base tables instead of the view.
22 */
23 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
24 (SELECT 1 FROM pa_cost_base_cost_codes cbv
25 WHERE cbv.cost_base = c_base
26 AND cbv.cost_base_type = c_base_type
27 AND cbv.cost_plus_structure = cp_structure)
28 AND NOT EXISTS
29 (SELECT 1 FROM pa_cost_base_exp_types cbv
30 WHERE cbv.cost_base = c_base
31 AND cbv.cost_base_type = c_base_type
32 AND cbv.cost_plus_structure = cp_structure);
33
34 exception
35
36 when NO_DATA_FOUND then
37 status := NO_DATA_FOUND_ERR;
38
39 when OTHERS then
40 status := SQLCODE;
41
42 end check_unique;
43
44 ------------------------------------------------------------------------------
45 procedure check_rev_compiled(cp_structure IN varchar2,
46 status IN OUT NOCOPY number)
47 is
48 dummy number;
49 begin
50
51 status := 0;
52
53 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
54 (SELECT 1
55 FROM pa_ind_rate_sch_revisions
56 WHERE cost_plus_structure = cp_structure
57 AND compiled_date IS NOT NULL);
58
59 exception
60
61 when NO_DATA_FOUND then
62 status := NO_DATA_FOUND_ERR;
63
64 when OTHERS then
65 status := SQLCODE;
66
67 end check_rev_compiled;
68
69
70 ------------------------------------------------------------------------------
71 procedure check_references(cp_structure IN varchar2,
72 c_base IN varchar2,
73 c_base_type IN varchar2,
74 status IN OUT NOCOPY number)
75 is
76
77 -- local variables
78 dummy number;
79
80 begin
81
82 status := 0;
83
84 -- check pa_cost_base_cost_codes table
85 -- check pa_cost_base_exp_types table
86 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
87 (SELECT 1 FROM pa_cost_base_cost_codes cbicc,
88 pa_cost_base_exp_types cbet
89 WHERE ( (cbicc.cost_plus_structure = cp_structure)
90 and (cbicc.cost_base = c_base)
91 and (cbicc.cost_base_type = c_base_type))
92 or ( (cbet.cost_plus_structure = cp_structure)
93 and (cbet.cost_base = c_base)
94 and (cbet.cost_base_type = c_base_type)));
95
96 exception
97
98 when NO_DATA_FOUND then
99 -- there are at least one foreign key in details table
100 status := NO_DATA_FOUND_ERR;
101
102 when OTHERS then
103 status := SQLCODE;
104
105 end check_references;
106
107 ------------------------------------------------------------------------------
108 procedure cascade_delete(cp_structure IN varchar2,
109 c_base IN varchar2,
110 c_base_type IN varchar2,
111 status IN OUT NOCOPY number)
112 is
113 begin
114 DELETE pa_cost_base_cost_codes
115 WHERE cost_plus_structure = cp_structure
116 AND cost_base = c_base
117 AND cost_base_type = c_base_type;
118
119 DELETE pa_cost_base_exp_types
120 WHERE cost_plus_structure = cp_structure
121 AND cost_base = c_base
122 AND cost_base_type = c_base_type;
123
124 COMMIT;
125
126 exception
127
128 when OTHERS then
129 status := SQLCODE;
130
131 end cascade_delete;
132
133
134 end PA_COST_BASE_VIEW_PKG ;
[Home] [Help]