1 PACKAGE CSTPPINV AS
2 /* $Header: CSTPINVS.pls 120.4.12010000.2 2008/08/08 12:31:59 smsasidh ship $ */
3
4 PROCEDURE cost_inv_txn (
5 i_pac_period_id IN NUMBER,
6 i_legal_entity IN NUMBER,
7 i_cost_type_id IN NUMBER,
8 i_cost_group_id IN NUMBER,
9 i_cost_method IN NUMBER,
10 i_txn_id IN NUMBER,
11 i_txn_action_id IN NUMBER,
12 i_txn_src_type_id IN NUMBER,
13 i_item_id IN NUMBER,
14 i_txn_qty IN NUMBER,
15 i_txn_org_id IN NUMBER,
16 i_txfr_org_id IN NUMBER,
17 i_subinventory_code IN VARCHAR2,
18 i_exp_flag IN NUMBER,
19 i_exp_item IN NUMBER,
20 i_pac_rates_id IN NUMBER,
21 i_process_group IN NUMBER,
22 i_master_org_id IN NUMBER,
23 i_uom_control IN NUMBER,
24 i_user_id IN NUMBER,
25 i_login_id IN NUMBER,
26 i_request_id IN NUMBER,
27 i_prog_id IN NUMBER,
28 i_prog_appl_id IN NUMBER,
29 i_txn_category IN NUMBER,
30 i_transfer_price_pd IN NUMBER := 0, -- INVCONV for process-discrete txfer
31 o_err_num OUT NOCOPY NUMBER,
32 o_err_code OUT NOCOPY VARCHAR2,
33 o_err_msg OUT NOCOPY VARCHAR2
34 );
35
36 PROCEDURE get_interorg_cost(
37 i_legal_entity IN NUMBER,
38 i_pac_period_id IN NUMBER,
39 i_cost_type_id IN NUMBER,
40 i_cost_group_id IN NUMBER,
41 i_txn_cost_group_id IN NUMBER,
42 i_txfr_cost_group_id IN NUMBER,
43 i_txn_id IN NUMBER,
44 i_txn_action_id IN NUMBER,
45 i_item_id IN NUMBER,
46 i_txn_qty IN NUMBER,
47 i_txn_org_id IN NUMBER,
48 i_txfr_org_id IN NUMBER,
49 i_user_id IN NUMBER,
50 i_login_id IN NUMBER,
51 i_request_id IN NUMBER,
52 i_prog_id IN NUMBER,
53 i_prog_appl_id IN NUMBER,
54 i_transfer_price_pd IN NUMBER := 0, -- INVCONV for process-discrete txfer
55 o_err_num OUT NOCOPY NUMBER,
56 o_err_code OUT NOCOPY VARCHAR2,
57 o_err_msg OUT NOCOPY VARCHAR2
58 );
59
60 PROCEDURE get_txfr_trp_cost(
61 i_source_txn_id IN NUMBER,
62 i_source_cost IN NUMBER,
63 x_txfr_credit OUT NOCOPY NUMBER,
64 x_trp_cost OUT NOCOPY NUMBER,
65 o_err_num OUT NOCOPY NUMBER,
66 o_err_code OUT NOCOPY VARCHAR2,
67 o_err_msg OUT NOCOPY VARCHAR2
68 );
69
70 PROCEDURE add_elemental_cost(
71 i_pac_period_id IN NUMBER,
72 i_cost_type_id IN NUMBER,
73 i_cost_group_id IN NUMBER,
74 i_txn_id IN NUMBER,
75 i_item_id IN NUMBER,
76 i_cost_element_id IN NUMBER,
77 i_level_type IN NUMBER,
78 i_incr_cost IN NUMBER,
79 i_user_id IN NUMBER,
80 i_login_id IN NUMBER,
81 i_request_id IN NUMBER,
82 i_prog_id IN NUMBER,
83 i_prog_appl_id IN NUMBER,
84 o_err_num OUT NOCOPY NUMBER,
85 o_err_code OUT NOCOPY VARCHAR2,
86 o_err_msg OUT NOCOPY VARCHAR2
87 );
88
89 PROCEDURE insert_elemental_cost(
90 i_pac_period_id IN NUMBER,
91 i_cost_type_id IN NUMBER,
92 i_cost_group_id IN NUMBER,
93 i_txn_id IN NUMBER,
94 i_item_id IN NUMBER,
95 i_cost_element_id IN NUMBER,
96 i_level_type IN NUMBER,
97 i_cost IN NUMBER,
98 i_user_id IN NUMBER,
99 i_login_id IN NUMBER,
100 i_request_id IN NUMBER,
101 i_prog_id IN NUMBER,
102 i_prog_appl_id IN NUMBER,
103 o_err_num OUT NOCOPY NUMBER,
104 o_err_code OUT NOCOPY VARCHAR2,
105 o_err_msg OUT NOCOPY VARCHAR2
106 );
107
108 PROCEDURE get_pacp_cost(
109 i_cost_source_cost_group IN NUMBER,
110 i_pac_period_id IN NUMBER,
111 i_cost_type_id IN NUMBER,
112 i_cost_group_id IN NUMBER,
113 i_txn_id IN NUMBER,
114 i_item_id IN NUMBER,
115 i_conv_rate IN NUMBER,
116 i_user_id IN NUMBER,
117 i_login_id IN NUMBER,
118 i_request_id IN NUMBER,
119 i_prog_id IN NUMBER,
120 i_prog_appl_id IN NUMBER,
121 x_pacp_used OUT NOCOPY NUMBER,
122 x_pacp_cost OUT NOCOPY NUMBER,
123 o_err_num OUT NOCOPY NUMBER,
124 o_err_code OUT NOCOPY VARCHAR2,
125 o_err_msg OUT NOCOPY VARCHAR2
126 );
127
128 PROCEDURE get_perp_ship_cost(
129 i_pac_period_id IN NUMBER,
130 i_cost_type_id IN NUMBER,
131 i_cost_group_id IN NUMBER,
132 i_txn_id IN NUMBER,
133 i_mta_txn_id IN NUMBER,
134 i_item_id IN NUMBER,
135 i_from_org IN NUMBER,
136 i_conv_rate IN NUMBER,
137 i_user_id IN NUMBER,
138 i_login_id IN NUMBER,
139 i_request_id IN NUMBER,
140 i_prog_id IN NUMBER,
141 i_prog_appl_id IN NUMBER,
142 x_perp_ship_cost OUT NOCOPY NUMBER,
143 o_err_num OUT NOCOPY NUMBER,
144 o_err_code OUT NOCOPY VARCHAR2,
145 o_err_msg OUT NOCOPY VARCHAR2
146 );
147
148 PROCEDURE get_prev_period_cost(
149 i_legal_entity IN NUMBER,
150 i_cost_source_cost_group IN NUMBER,
151 i_pac_period_id IN NUMBER,
152 i_cost_type_id IN NUMBER,
153 i_cost_group_id IN NUMBER,
154 i_txn_id IN NUMBER,
155 i_item_id IN NUMBER,
156 i_conv_rate IN NUMBER,
157 i_user_id IN NUMBER,
158 i_login_id IN NUMBER,
159 i_request_id IN NUMBER,
160 i_prog_id IN NUMBER,
161 i_prog_appl_id IN NUMBER,
162 x_prev_period_id OUT NOCOPY NUMBER,
163 x_prev_period_cost OUT NOCOPY NUMBER,
164 o_err_num OUT NOCOPY NUMBER,
165 o_err_code OUT NOCOPY VARCHAR2,
166 o_err_msg OUT NOCOPY VARCHAR2
167 );
168
169 PROCEDURE get_snd_rcv_rate(
170 i_txn_id IN NUMBER,
171 i_from_org IN NUMBER,
172 i_to_org IN NUMBER,
173 o_conv_rate OUT NOCOPY NUMBER,
174 o_err_num OUT NOCOPY NUMBER,
175 o_err_code OUT NOCOPY VARCHAR2,
176 o_err_msg OUT NOCOPY VARCHAR2
177 );
178
179 PROCEDURE get_from_to_uom(
180 i_item_id IN NUMBER,
181 i_from_org IN NUMBER,
182 i_to_org IN NUMBER,
183 o_from_uom OUT NOCOPY VARCHAR2,
184 o_to_uom OUT NOCOPY VARCHAR2,
185 o_err_num OUT NOCOPY NUMBER,
186 o_err_code OUT NOCOPY VARCHAR2,
187 o_err_msg OUT NOCOPY VARCHAR2
188 );
189
190
191 PROCEDURE get_um_rate(
192 i_txn_org_id IN NUMBER,
193 i_master_org_id IN NUMBER,
194 i_txn_cost_group_id IN NUMBER,
195 i_txfr_cost_group_id IN NUMBER,
196 i_txn_action_id IN NUMBER,
197 i_item_id IN NUMBER,
198 i_uom_control IN NUMBER,
199 i_user_id IN NUMBER,
200 i_login_id IN NUMBER,
201 i_request_id IN NUMBER,
202 i_prog_id IN NUMBER,
203 i_prog_appl_id IN NUMBER,
204 o_um_rate OUT NOCOPY NUMBER,
205 o_err_num OUT NOCOPY NUMBER,
206 o_err_code OUT NOCOPY VARCHAR2,
207 o_err_msg OUT NOCOPY VARCHAR2
208 );
209
210 PROCEDURE cost_acct_events(
211 i_pac_period_id IN NUMBER,
212 i_legal_entity IN NUMBER,
213 i_cost_type_id IN NUMBER,
214 i_cost_group_id IN NUMBER,
215 i_cost_method IN NUMBER,
216 i_txn_id IN NUMBER,
217 i_item_id IN NUMBER,
218 i_txn_qty IN NUMBER,
219 i_txn_org_id IN NUMBER,
220 i_master_org_id IN NUMBER,
221 i_uom_control IN NUMBER,
222 i_user_id IN NUMBER,
223 i_login_id IN NUMBER,
224 i_request_id IN NUMBER,
225 i_prog_id IN NUMBER,
226 i_prog_appl_id IN NUMBER,
227 o_err_num OUT NOCOPY NUMBER,
228 o_err_code OUT NOCOPY VARCHAR2,
229 o_err_msg OUT NOCOPY VARCHAR2
230 );
231
232 PROCEDURE get_exp_flag(
233 i_item_id IN NUMBER,
234 i_txn_org_id IN NUMBER,
235 i_subinventory_code IN VARCHAR2,
236 o_exp_item OUT NOCOPY NUMBER,
237 o_exp_flag OUT NOCOPY NUMBER,
238 o_err_num OUT NOCOPY NUMBER,
239 o_err_code OUT NOCOPY VARCHAR2,
240 o_err_msg OUT NOCOPY VARCHAR2
241 );
242
243 PROCEDURE cost_interorg_txn_grp1 (
244 i_pac_period_id IN NUMBER,
245 i_legal_entity IN NUMBER,
246 i_cost_type_id IN NUMBER,
247 i_cost_group_id IN NUMBER,
248 i_cost_method IN NUMBER,
249 i_start_date IN VARCHAR2,
250 i_end_date IN VARCHAR2,
251 i_pac_rates_id IN NUMBER,
252 i_process_group IN NUMBER,
253 i_master_org_id IN NUMBER,
254 i_uom_control IN NUMBER,
255 i_user_id IN NUMBER,
256 i_login_id IN NUMBER,
257 i_request_id IN NUMBER,
258 i_prog_id IN NUMBER,
259 i_prog_appl_id IN NUMBER,
260 o_err_num OUT NOCOPY NUMBER,
261 o_err_code OUT NOCOPY VARCHAR2,
262 o_err_msg OUT NOCOPY VARCHAR2
263 );
264
265 PROCEDURE cost_interorg_txn_grp2 (
266 i_pac_period_id IN NUMBER,
267 i_legal_entity IN NUMBER,
268 i_cost_type_id IN NUMBER,
269 i_cost_group_id IN NUMBER,
270 i_cost_method IN NUMBER,
271 i_start_date IN VARCHAR2,
272 i_end_date IN VARCHAR2,
273 i_pac_rates_id IN NUMBER,
274 i_process_group IN NUMBER,
275 i_master_org_id IN NUMBER,
276 i_uom_control IN NUMBER,
277 i_user_id IN NUMBER,
278 i_login_id IN NUMBER,
279 i_request_id IN NUMBER,
280 i_prog_id IN NUMBER,
281 i_prog_appl_id IN NUMBER,
282 o_err_num OUT NOCOPY NUMBER,
283 o_err_code OUT NOCOPY VARCHAR2,
284 o_err_msg OUT NOCOPY VARCHAR2
285 );
286
287 PROCEDURE cost_txn_grp2 (
288 i_pac_period_id IN NUMBER,
289 i_legal_entity IN NUMBER,
290 i_cost_type_id IN NUMBER,
291 i_cost_group_id IN NUMBER,
292 i_cost_method IN NUMBER,
293 i_start_date IN VARCHAR2,
294 i_end_date IN VARCHAR2,
295 i_pac_rates_id IN NUMBER,
296 i_process_group IN NUMBER,
297 i_master_org_id IN NUMBER,
298 i_uom_control IN NUMBER,
299 i_mat_relief_algo IN NUMBER,
300 i_user_id IN NUMBER,
301 i_login_id IN NUMBER,
302 i_request_id IN NUMBER,
303 i_prog_id IN NUMBER,
304 i_prog_appl_id IN NUMBER,
305 o_err_num OUT NOCOPY NUMBER,
306 o_err_code OUT NOCOPY VARCHAR2,
307 o_err_msg OUT NOCOPY VARCHAR2
308 );
309
310 TYPE t_item_id_tbl IS TABLE OF MTL_SYSTEM_ITEMS.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
311 TYPE t_cost_layer_id_tbl IS TABLE OF CST_PAC_ITEM_COSTS.cost_layer_id%TYPE INDEX BY BINARY_INTEGER;
312 TYPE t_qty_layer_id_tbl IS TABLE OF CST_PAC_QUANTITY_LAYERS.quantity_layer_id%TYPE INDEX BY BINARY_INTEGER;
313 TYPE t_cost_element_id_tbl IS TABLE OF CST_COST_ELEMENTS.cost_element_id%TYPE INDEX BY BINARY_INTEGER;
314 TYPE t_level_type_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.level_type%TYPE INDEX BY BINARY_INTEGER;
315 TYPE t_txn_category_tbl IS TABLE OF CST_PAC_PERIOD_BALANCES.txn_category%TYPE INDEX BY BINARY_INTEGER;
316 TYPE t_item_balance_tbl IS TABLE OF CST_PAC_ITEM_COST_DETAILS.item_balance%TYPE INDEX BY BINARY_INTEGER;
317 TYPE t_make_balance_tbl IS TABLE OF CST_PAC_ITEM_COST_DETAILS.make_balance%TYPE INDEX BY BINARY_INTEGER;
318 TYPE t_buy_balance_tbl IS TABLE OF CST_PAC_ITEM_COST_DETAILS.buy_balance%TYPE INDEX BY BINARY_INTEGER;
319
320 TYPE t_item_index_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
321
322 TYPE t_item_quantity_tbl IS TABLE OF CST_PAC_ITEM_COSTS.total_layer_quantity%TYPE INDEX BY BINARY_INTEGER;
323 TYPE t_make_quantity_tbl IS TABLE OF CST_PAC_ITEM_COSTS.make_quantity%TYPE INDEX BY BINARY_INTEGER;
324 TYPE t_buy_quantity_tbl IS TABLE OF CST_PAC_ITEM_COSTS.buy_quantity%TYPE INDEX BY BINARY_INTEGER;
325 TYPE t_issue_quantity_tbl IS TABLE OF CST_PAC_ITEM_COSTS.issue_quantity%TYPE INDEX BY BINARY_INTEGER;
326
327 l_item_id_tbl t_item_id_tbl;
328 l_cost_layer_id_tbl t_cost_layer_id_tbl;
329 l_qty_layer_id_tbl t_qty_layer_id_tbl;
330
331 l_cost_element_id_tbl t_cost_element_id_tbl;
332 l_level_type_tbl t_level_type_tbl;
333 l_txn_category_tbl t_txn_category_tbl;
334
335 l_item_balance_tbl t_item_balance_tbl;
336 l_make_balance_tbl t_make_balance_tbl;
337 l_buy_balance_tbl t_buy_balance_tbl;
338
339 l_item_quantity_tbl t_item_quantity_tbl;
340 l_make_quantity_tbl t_make_quantity_tbl;
341 l_buy_quantity_tbl t_buy_quantity_tbl;
342 l_issue_quantity_tbl t_issue_quantity_tbl;
343
344 l_item_start_index_tbl t_item_index_tbl;
345 l_item_end_index_tbl t_item_index_tbl;
346
347 END CSTPPINV;