[Home] [Help]
PACKAGE BODY: APPS.CSTPACLM
Source
1 package body cstpaclm as
2 /* $Header: CSTACLMB.pls 120.1 2005/08/29 17:35:47 sheyu noship $ */
3
4 function layer_id (
5 i_org_id in number,
6 i_item_id in number,
7 i_cost_group_id in number,
8 o_err_num out NOCOPY number,
9 o_err_code out NOCOPY varchar2,
10 o_err_msg out NOCOPY varchar2
11 )
12 return integer
13 is
14 retval number;
15 begin
16
17 o_err_num := 0;
18 o_err_code := '';
19 o_err_msg := '';
20
21 select layer_id
22 into retval
23 from cst_quantity_layers
24 where organization_id = i_org_id
25 and inventory_item_id = i_item_id
26 and cost_group_id = i_cost_group_id;
27
28 return retval;
29 EXCEPTION
30
31 when NO_DATA_FOUND THEN
32 return 0;
33 when others then
34 o_err_num := SQLCODE;
35 o_err_msg := 'CSTPACLM.LAYER_ID:' || substrb(SQLERRM,1,150);
36 return 0;
37
38 end layer_id;
39
40
41 function layer_det_exist (
42 i_org_id in number,
43 i_item_id in number,
44 i_cost_group_id in number,
45 o_err_num out NOCOPY number,
46 o_err_code out NOCOPY varchar2,
47 o_err_msg out NOCOPY varchar2
48 )
49 return integer
50 is
51 retval number;
52 begin
53
54 o_err_num := 0;
55 o_err_code := '';
56 o_err_msg := '';
57
58
59 SELECT distinct LAYER_ID
60 INTO retval
61 FROM CST_LAYER_COST_DETAILS
62 WHERE LAYER_ID =
63 (SELECT LAYER_ID
64 FROM CST_QUANTITY_LAYERS
65 WHERE ORGANIZATION_ID = i_org_id
66 AND INVENTORY_ITEM_ID = i_item_id
67 AND COST_GROUP_ID = i_cost_group_id);
68
69 return retval;
70 EXCEPTION
71
72 when NO_DATA_FOUND THEN
73 return 0;
74 when others then
75 o_err_num := SQLCODE;
76 o_err_msg := 'CSTPACLM.LAYER_DET_EXIST:' || substrb(SQLERRM,1,150);
77 return 0;
78
79 end layer_det_exist;
80
81
82 function create_layer (
83 i_org_id in number,
84 i_item_id in number,
85 i_cost_group_id in number,
86 i_user_id in number,
87 i_request_id in number,
88 i_prog_id in number,
89 i_prog_appl_id in number,
90 i_txn_id in number,
91 o_err_num out NOCOPY number,
92 o_err_code out NOCOPY varchar2,
93 o_err_msg out NOCOPY varchar2
94 )
95 return integer
96 is
97 l_layer_id number;
98 begin
99
100 o_err_num := 0;
101 o_err_code := '';
102 o_err_msg := '';
103
104 /*
105 ** check for existing layer
106 */
107 l_layer_id := cstpaclm.layer_id(i_org_id,
108 i_item_id,
109 i_cost_group_id,
110 o_err_num,
111 o_err_code,
112 o_err_msg);
113
114 if (l_layer_id = 0) then
115
116 /*
117 ** if the layer_id is 0, then the layer doesn't exist, so we
118 ** should create it
119 */
120 select cst_quantity_layers_s.nextval
121 into l_layer_id
122 from dual;
123
124 BEGIN
125 insert into cst_quantity_layers (
126 layer_id,
127 organization_id,
128 inventory_item_id,
129 cost_group_id,
130 layer_quantity,
131 last_update_date,
132 last_updated_by,
133 creation_date,
134 created_by,
135 request_id,
136 program_id,
137 program_application_id,
138 PL_MATERIAL,
139 PL_MATERIAL_OVERHEAD,
140 PL_RESOURCE,
141 PL_OUTSIDE_PROCESSING,
142 PL_OVERHEAD,
143 TL_MATERIAL,
144 TL_MATERIAL_OVERHEAD,
145 TL_RESOURCE,
146 TL_OUTSIDE_PROCESSING,
147 TL_OVERHEAD,
148 MATERIAL_COST,
149 MATERIAL_OVERHEAD_COST ,
150 RESOURCE_COST,
151 OUTSIDE_PROCESSING_COST,
152 OVERHEAD_COST,
153 PL_ITEM_COST,
154 TL_ITEM_COST,
155 ITEM_COST,
156 UNBURDENED_COST,
157 BURDEN_COST,
158 CREATE_TRANSACTION_ID
159 )
160 values (
161 l_layer_id,
162 i_org_id,
163 i_item_id,
164 i_cost_group_id,
165 0,
166 SYSDATE,
167 i_user_id,
168 SYSDATE,
169 i_user_id,
170 i_request_id,
171 i_prog_id,
172 i_prog_appl_id,
173 0,
174 0,
175 0,
176 0,
177 0,
178 0,
179 0,
180 0,
181 0,
182 0,
183 0,
184 0,
185 0,
186 0,
187 0,
188 0,
189 0,
190 0,
191 0,
192 0,
193 i_txn_id
194 );
195 EXCEPTION
196 WHEN DUP_VAL_ON_INDEX THEN
197 /* Bug 4408497: A layer already exists for this org/item/cg, so return that layer */
198 l_layer_id := cstpaclm.layer_id(i_org_id,
199 i_item_id,
200 i_cost_group_id,
201 o_err_num,
202 o_err_code,
203 o_err_msg);
204 return l_layer_id;
205 WHEN OTHERS THEN
206 o_err_num := SQLCODE;
207 o_err_msg := 'CSTPACLM.CREATE_LAYER:' || substrb(SQLERRM,1,150);
208 return 0;
209 END;
210 end if;
211
212 return l_layer_id;
213
214 EXCEPTION
215
216 when others then
217 o_err_num := SQLCODE;
218 o_err_msg := 'CSTPACLM.CREATE_LAYER:' || substrb(SQLERRM,1,150);
219 return 0;
220
221 end create_layer;
222
223 end cstpaclm;