1 package body PA_COPY_STRUCTURE_PKG as
2 -- $Header: PAXCICPB.pls 120.1 2005/08/23 19:19:48 spunathi noship $
3
4
5 procedure check_structure(cp_structure IN varchar2,
6 status IN OUT NOCOPY number) is
7
8 -- local variables
9 dummy number;
10
11 begin
12
13 status := 0;
14
15 -- check pa_cost_plus_structures table
16
17 SELECT 1 INTO dummy FROM sys.dual WHERE EXISTS
18 (SELECT 1 FROM pa_cost_plus_structures
19 WHERE cost_plus_structure = cp_structure);
20
21 exception
22
23 when OTHERS then
24 -- there are at least one foreign key in details table
25 status := SQLCODE;
26
27 end check_structure;
28
29
30 procedure check_existence(cp_structure IN varchar2,
31 status IN OUT NOCOPY number) is
32
33 -- local variables
34 dummy number;
35
36 begin
37
38 status := 0;
39
40 -- check pa_cost_base_v table
41
42 /*
43 * Performance related change:
44 * The view pa_cost_base_v doesnot get merged here.
45 * Instead of the view, the query now directly looks into the
46 * base tables.
47 */
48 SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
49 (SELECT 1 FROM pa_cost_base_cost_codes
50 WHERE cost_plus_structure = cp_structure)
51 AND NOT EXISTS
52 (SELECT 1 FROM pa_cost_base_exp_types
53 WHERE cost_plus_structure = cp_structure)
54 ;
55
56 exception
57
58 when OTHERS then
59 -- there are at least one foreign key in details table
60 status := SQLCODE;
61
62 end check_existence;
63
64
65
66 --
67 -- PROCEDURE
68 -- copy_structure
69 --
70 -- PURPOSE
71 -- The objective of this procedure is to check whether the
72 -- cost plus structure has been used. 'Used' is defined as
73 -- there are costed expenditure items in this cost plus structure.
74 --
75 -- HISTORY
76 --
77 -- 07-MAY-94 S Lee Created
78 --
79
80 procedure copy_structure(source IN varchar2,
81 destination IN varchar2,
82 status IN OUT NOCOPY number,
83 stage IN OUT NOCOPY number)
84 is
85
86 -- cursor definition
87 CURSOR icc_cursor
88 IS
89 SELECT cost_base,
90 cost_base_type,
91 ind_cost_code,
92 precedence
93 FROM pa_cost_base_cost_codes
94 WHERE cost_plus_structure = source;
95
96 CURSOR et_cursor
97 IS
98 SELECT cost_base,
99 cost_base_type,
100 expenditure_type
101 FROM pa_cost_base_exp_types
102 WHERE cost_plus_structure = source;
103
104 -- Local variables
105 cbicc_id number;
106 structure_type varchar2(30);
107 icc_precedence number;
108
109 -- Standard who
110 x_last_updated_by NUMBER(15);
111 x_created_by NUMBER(15);
112 x_last_update_login NUMBER(15);
113
114
115 begin
116
117 stage := 100;
118 status := 0;
119
120 --
121 -- Standard who
122 --
123
124 x_created_by := FND_GLOBAL.USER_ID;
125 x_last_updated_by := FND_GLOBAL.USER_ID;
126 x_last_update_login := FND_GLOBAL.LOGIN_ID;
127
128 SELECT cost_plus_structure_type
129 INTO structure_type
130 FROM pa_cost_plus_structures
131 WHERE cost_plus_structure = destination;
132
133 if (structure_type = 'A') then
134 icc_precedence := 1 ;
135 else
136 icc_precedence := NULL;
137 end if;
138
139
140 for icc_row in icc_cursor loop
141 SELECT pa_cost_base_cost_codes_s.nextval into cbicc_id FROM sys.dual;
142
143 INSERT INTO pa_cost_base_cost_codes
144 (cost_base_cost_code_id,
145 cost_plus_structure,
146 cost_base,
147 cost_base_type,
148 ind_cost_code,
149 precedence,
150 last_update_date,
151 last_updated_by,
152 creation_date,
153 created_by,
154 last_update_login
155 )
156 VALUES
157 (cbicc_id,
158 destination,
159 icc_row.cost_base,
160 icc_row.cost_base_type,
161 icc_row.ind_cost_code,
162 NVL(icc_precedence,icc_row.precedence),
163 SYSDATE,
164 x_last_updated_by,
165 SYSDATE,
166 x_created_by,
167 x_last_update_login);
168
169 end loop;
170
171 stage := 200;
172
173 for et_row in et_cursor loop
174
175 INSERT INTO pa_cost_base_exp_types
176 (cost_plus_structure,
177 cost_base,
178 cost_base_type,
179 expenditure_type,
180 last_update_date,
181 last_updated_by,
182 creation_date,
183 created_by,
184 last_update_login
185 )
186 VALUES
187 (destination,
188 et_row.cost_base,
189 et_row.cost_base_type,
190 et_row.expenditure_type,
191 SYSDATE,
192 x_last_updated_by,
193 SYSDATE,
194 x_created_by,
195 x_last_update_login);
196
197 end loop;
198
199 COMMIT;
200
201 exception
202 WHEN OTHERS THEN
203 status := SQLCODE;
204 end copy_structure;
205
206 end PA_COPY_STRUCTURE_PKG ;
[Home] [Help]