1 package body pa_billing_extensions_pkg as
2 /* $Header: PAXIBEXB.pls 120.3 2005/08/19 17:13:49 mwasowic noship $ */
3 -----------------------------------------------------------------------------
4 -- this procedure will check for unique existence of name
5 -- ( billing extension name )
6 -----------------------------------------------------------------------------
7 procedure check_unique_name (x_return_status IN OUT NOCOPY number, --File.Sql.39 bug 4440895
8 x_rowid IN varchar2,
9 x_name IN varchar2 )
10 is
11 x_dummy number;
12 --
13 begin
14 --
15 select 1
16 into x_dummy
17 from sys.dual
18 where not exists
19 (select 1
20 from pa_billing_extensions
21 where name = x_name
22 and ((x_rowid is NULL) or (rowid <> x_rowid)));
23 --
24 x_return_status := 0;
25 --
26 EXCEPTION
27 WHEN NO_DATA_FOUND then
28 x_return_status := 1;
29 WHEN OTHERS then
30 x_return_status := SQLCODE;
31 end check_unique_name;
32
33 ------------------------------------------------------------------
34
35 procedure check_unique_order (x_return_status IN OUT NOCOPY number, --File.Sql.39 bug 4440895
36 x_rowid IN varchar2,
37 x_order IN number)
38 is
39 x_dummy number;
40 --
41 begin
42 --
43 select 1
44 into x_dummy
45 from sys.dual
46 where not exists
47 (select 1
48 from pa_billing_extensions
49 where processing_order = x_order
50 and ((x_rowid is NULL) or (rowid <> x_rowid)));
51 --
52 x_return_status := 0;
53 --
54 EXCEPTION
55 WHEN NO_DATA_FOUND then
56 x_return_status := 1;
57 WHEN OTHERS then
58 x_return_status := SQLCODE;
59 end check_unique_order;
60 ------------------------------------------------------------------
61 --
62 procedure check_references( x_return_status IN OUT NOCOPY number, --File.Sql.39 bug 4440895
63 x_rowid IN varchar2,
64 x_bill_extension_id IN number )
65 is
66 --
67 x_dummy number;
68 stage number;
69 begin
70 --
71 stage := 1;
72 --
73 select 1
74 into x_dummy
75 from sys.dual
76 where not exists
77 ( select 1
78 from pa_billing_assignments
79 where billing_extension_id = x_bill_extension_id );
80 --
81 x_return_status := 0;
82 --
83 EXCEPTION
84 WHEN NO_DATA_FOUND then
85 if stage = 1 then
86 x_return_status := 1;
87 end if;
88 WHEN OTHERS then
89 x_return_status := SQLCODE;
90 --
91 end check_references;
92
93 procedure check_events( x_return_status IN OUT NOCOPY number, --File.Sql.39 bug 4440895
94 x_rowid IN varchar2,
95 x_bill_extension_id IN number )
96 is
97 --
98 x_dummy number;
99 stage number;
100 begin
101 --
102 stage := 1;
103 --
104 select 1 into x_dummy
105 from sys.dual
106 where not exists ( select 1
107 from pa_billing_assignments
108 where billing_extension_id = x_bill_extension_id
109 and billing_assignment_id in
110 ( select billing_assignment_id
111 from pa_events ));
112 --
113 x_return_status := 0;
114 --
115 EXCEPTION
116 WHEN NO_DATA_FOUND then
117 x_return_status := 1;
118 WHEN OTHERS then
119 x_return_status := SQLCODE;
120 --
121 end check_events;
122
123 --------------------------------------------------------------------
124
125 procedure get_nextval( x_return_status IN OUT NOCOPY number, --File.Sql.39 bug 4440895
126 x_nextval IN OUT NOCOPY number ) --File.Sql.39 bug 4440895
127 is
128 --
129 begin
130 --
131 select pa_billing_extensions_s.nextval into x_nextval
132 from sys.dual;
133 --
134 x_return_status := 0;
135 --
136 EXCEPTION
137 WHEN NO_DATA_FOUND then
138 x_return_status := 1;
139 WHEN OTHERS then
140 x_return_status := SQLCODE;
141 --
142 end get_nextval;
143 --
144 END pa_billing_extensions_pkg;