1 package body PA_COST_BASE_TYPE_PKG as
2 /* $Header: PAXLOUPB.pls 120.2 2005/08/08 12:40:44 sbharath noship $ */
3 ---------------------------------------------------------------------------
4 procedure check_unique (x_return_status IN OUT NOCOPY number,
5 x_rowid IN varchar2,
6 x_lookup_code IN varchar2)
7 is
8 x_dummy number;
9
10 begin
11 x_return_status := 0;
12
13 select 1
14 into x_dummy
15 from sys.dual
16 where not exists
17 (select 1
18 from pa_lookups p
19 where p.lookup_type like 'COST BASE TYPE'
20 and ((x_rowid is NULL) or (rowid <> x_rowid))
21 and p.lookup_code = x_lookup_code);
22
23 x_return_status := 0;
24
25 EXCEPTION
26 WHEN NO_DATA_FOUND then
27 x_return_status := 1;
28
29 WHEN OTHERS then
30 x_return_status := SQLCODE;
31
32 end check_unique;
33 ----------------------------------------------------------------------------
34 ----------------------------------------------------------------------------
35 procedure check_references (x_return_status IN OUT NOCOPY number,
36 x_stage IN OUT NOCOPY number,
37 x_lookup_code IN varchar2)
38 is
39 x_dummy number;
40
41 begin
42 x_return_status := 0;
43 x_stage := 0;
44
45 begin
46 select 1
47 into x_dummy
48 from sys.dual
49 where not exists
50 (select 1
51 from pa_cost_bases cb
52 where cb.cost_base_type = x_lookup_code);
53
54 x_return_status := 0; -- ie. value does not exist is child table
55 -- delete allowed.
56
57 EXCEPTION
58 when NO_DATA_FOUND then -- ie. value exists in child table
59 -- delete NOT allowed.
60 x_return_status := 1; -- since it is part of the exception
61 x_stage := 1;
62
63 when OTHERS then
64 x_return_status := SQLCODE;
65
66 end;
67
68 end check_references;
69 ----------------------------------------------------------------------------
70 end PA_COST_BASE_TYPE_PKG;
[Home] [Help]