1 PACKAGE PA_MC_CURRENCY_PKG AS
2 --$Header: PAXMCURS.pls 120.3 2005/08/11 11:35:51 eyefimov noship $
3 FunctionalCurrency fnd_currencies.currency_code%TYPE;
4
5 Invoice_Action Varchar2(15) := NULL;
6 -- Updated by PAIGEN at CANCEL time with value CANCEL.
7
8 /* Global Record and Table Definitions */
9
10 TYPE rsob IS RECORD
11 (rsob_id gl_alc_ledger_rships_v.ledger_id%TYPE,
12 rcurrency_code gl_alc_ledger_rships_v.currency_code%TYPE);
13
14 TYPE rsob_tab IS TABLE OF rsob
15 INDEX BY BINARY_INTEGER;
16
17 g_rsob_tab rsob_tab;
18
19 /* Type of message */
20 LOG NUMBER := 1;
21 DEBUG NUMBER := 2;
22
23 FUNCTION CurrRound( x_amount IN NUMBER ,
24 x_currency_code IN VARCHAR2 := FunctionalCurrency )
25 RETURN NUMBER;
26
27 PRAGMA RESTRICT_REFERENCES( CurrRound, WNPS,WNDS );
28
29 FUNCTION functional_currency(x_org_id IN NUMBER) RETURN VARCHAR2;
30 -- PRAGMA RESTRICT_REFERENCES( functional_currency, WNPS,WNDS );
31
32 FUNCTION set_of_books(x_org_id IN NUMBER) RETURN NUMBER;
33 -- PRAGMA RESTRICT_REFERENCES( set_of_books, WNPS,WNDS );
34
35 FUNCTION set_of_books RETURN NUMBER;
36 PRAGMA RESTRICT_REFERENCES( set_of_books, WNPS,WNDS );
37
38 FUNCTION get_mrc_sob_type_code( x_set_of_books_id IN NUMBER )
39 RETURN VARCHAR2;
40 PRAGMA RESTRICT_REFERENCES( get_mrc_sob_type_code, WNPS,WNDS );
41
42 FUNCTION get_mrc_sob_type_code RETURN VARCHAR2;
43 PRAGMA RESTRICT_REFERENCES( get_mrc_sob_type_code, WNPS,WNDS );
44
45 PROCEDURE eiid_details( x_eiid IN NUMBER,
46 x_orig_trx OUT NOCOPY VARCHAR2,
47 x_adj_item OUT NOCOPY NUMBER,
48 x_linkage OUT NOCOPY VARCHAR2,
49 x_ei_date OUT NOCOPY DATE,
50 --Bug#1078399
51 --New parameter x_txn_source added in eiid_details() - to be used to
52 --check whether the EI is an imported-one or not.
53 x_txn_source OUT NOCOPY VARCHAR2,
54 x_err_stack IN OUT NOCOPY VARCHAR2,
55 x_err_stage IN OUT NOCOPY VARCHAR2,
56 x_err_code OUT NOCOPY NUMBER);
57
58 PROCEDURE eiid_details( x_eiid IN NUMBER,
59 x_orig_trx OUT NOCOPY VARCHAR2,
60 x_adj_item OUT NOCOPY NUMBER,
61 x_linkage OUT NOCOPY VARCHAR2,
62 x_ei_date OUT NOCOPY DATE,
63 x_err_stack IN OUT NOCOPY VARCHAR2,
64 x_err_stage IN OUT NOCOPY VARCHAR2,
65 x_err_code OUT NOCOPY NUMBER);
66
67 FUNCTION max_cost_line( x_eiid IN NUMBER,
68 x_sob IN NUMBER) RETURN NUMBER;
69 PRAGMA RESTRICT_REFERENCES( max_cost_line, WNPS,WNDS );
70
71 FUNCTION max_rev_line(x_eiid IN NUMBER,
72 x_sob IN NUMBER) RETURN NUMBER;
73 PRAGMA RESTRICT_REFERENCES( max_rev_line, WNPS,WNDS );
74
75 PROCEDURE get_orig_cost_rates( x_adj_item IN NUMBER,
76 x_line_num IN NUMBER,
77 x_set_of_books_id IN NUMBER,
78 x_exchange_rate OUT NOCOPY NUMBER,
79 x_exchange_date OUT NOCOPY DATE,
80 x_exchange_rate_type OUT NOCOPY VARCHAR2,
81 x_err_stack IN OUT NOCOPY VARCHAR2,
82 x_err_stage IN OUT NOCOPY VARCHAR2,
83 x_err_code OUT NOCOPY NUMBER);
84
85 PROCEDURE get_orig_ei_cost_rates( x_exp_item_id IN NUMBER,
86 x_set_of_books_id IN NUMBER,
87 x_exchange_rate OUT NOCOPY NUMBER,
88 x_exchange_date OUT NOCOPY DATE,
89 x_exchange_rate_type OUT NOCOPY VARCHAR2,
90 x_err_stack IN OUT NOCOPY VARCHAR2,
91 x_err_stage IN OUT NOCOPY VARCHAR2,
92 x_err_code OUT NOCOPY NUMBER);
93
94 PROCEDURE get_cost_amts(x_exp_item_id IN NUMBER,
95 x_set_of_books_id IN NUMBER,
96 x_line_num IN NUMBER,
97 x_amount OUT NOCOPY NUMBER,
98 x_quantity OUT NOCOPY NUMBER,
99 x_exchange_rate OUT NOCOPY NUMBER,
100 x_exchange_date OUT NOCOPY DATE,
101 x_exchange_rate_type OUT NOCOPY VARCHAR2,
102 x_err_stack IN OUT NOCOPY VARCHAR2,
103 x_err_stage IN OUT NOCOPY VARCHAR2,
104 x_err_code OUT NOCOPY NUMBER);
105
106 PROCEDURE get_max_cost_amts(x_exp_item_id IN NUMBER,
107 x_set_of_books_id IN NUMBER,
108 x_raw_cost OUT NOCOPY NUMBER,
109 x_burdened_cost OUT NOCOPY NUMBER,
110 x_exchange_rate OUT NOCOPY NUMBER,
111 x_exchange_date OUT NOCOPY DATE,
112 x_exchange_rate_type OUT NOCOPY VARCHAR2,
113 x_err_stack IN OUT NOCOPY VARCHAR2,
114 x_err_stage IN OUT NOCOPY VARCHAR2,
115 x_err_code OUT NOCOPY NUMBER);
116
117 PROCEDURE get_max_crdl_amts(x_exp_item_id IN NUMBER,
118 x_set_of_books_id IN NUMBER,
119 x_revenue OUT NOCOPY NUMBER,
120 x_bill_amount OUT NOCOPY NUMBER,
121 x_exchange_rate OUT NOCOPY NUMBER,
122 x_err_stack IN OUT NOCOPY VARCHAR2,
123 x_err_stage IN OUT NOCOPY VARCHAR2,
124 x_err_code OUT NOCOPY NUMBER);
125
126
127 PROCEDURE get_orig_rev_rates( x_adj_item IN NUMBER,
128 x_line_num IN NUMBER,
129 x_set_of_books_id IN NUMBER,
130 x_exchange_rate OUT NOCOPY NUMBER,
131 x_exchange_date OUT NOCOPY DATE,
132 x_exchange_rate_type OUT NOCOPY VARCHAR2,
133 x_err_stack IN OUT NOCOPY VARCHAR2,
134 x_err_stage IN OUT NOCOPY VARCHAR2,
135 x_err_code OUT NOCOPY NUMBER);
136
137 PROCEDURE get_orig_ei_mc_rates( x_adj_exp_item_id IN NUMBER,
138 x_xfer_exp_item_id IN NUMBER,
139 x_set_of_books_id IN NUMBER,
140 x_raw_cost OUT NOCOPY NUMBER,
141 x_raw_cost_rate OUT NOCOPY NUMBER,
142 x_burden_cost OUT NOCOPY NUMBER,
143 x_burden_cost_rate OUT NOCOPY NUMBER,
144 x_bill_amount OUT NOCOPY NUMBER,
145 x_bill_rate OUT NOCOPY NUMBER,
146 x_accrued_revenue OUT NOCOPY NUMBER,
147 x_accrual_rate OUT NOCOPY NUMBER,
148 x_transfer_price OUT NOCOPY NUMBER,
149 x_adjusted_rate OUT NOCOPY NUMBER,
150 x_exchange_rate OUT NOCOPY NUMBER,
151 x_exchange_date OUT NOCOPY DATE,
152 x_exchange_rate_type OUT NOCOPY VARCHAR2,
153 x_err_stack IN OUT NOCOPY VARCHAR2,
154 x_err_stage IN OUT NOCOPY VARCHAR2,
155 x_err_code OUT NOCOPY NUMBER);
156
157 --Overloaded procedure
158
159 PROCEDURE get_orig_ei_mc_rates( x_adj_exp_item_id IN NUMBER,
160 x_xfer_exp_item_id IN NUMBER,
161 x_set_of_books_id IN NUMBER,
162 x_raw_cost OUT NOCOPY NUMBER,
163 x_raw_cost_rate OUT NOCOPY NUMBER,
164 x_burden_cost OUT NOCOPY NUMBER,
165 x_burden_cost_rate OUT NOCOPY NUMBER,
166 x_bill_amount OUT NOCOPY NUMBER,
167 x_bill_rate OUT NOCOPY NUMBER,
168 x_accrued_revenue OUT NOCOPY NUMBER,
169 x_accrual_rate OUT NOCOPY NUMBER,
170 x_transfer_price OUT NOCOPY NUMBER,
171 x_adjusted_rate OUT NOCOPY NUMBER,
172 x_exchange_rate OUT NOCOPY NUMBER,
173 x_exchange_date OUT NOCOPY DATE,
174 x_exchange_rate_type OUT NOCOPY VARCHAR2,
175 x_raw_revenue OUT NOCOPY NUMBER,/*3024103*/
176 x_adj_revenue OUT NOCOPY NUMBER,/*3024103*/
177 x_forecast_revenue OUT NOCOPY NUMBER,/*3024103*/
178 x_err_stack IN OUT NOCOPY VARCHAR2,
179 x_err_stage IN OUT NOCOPY VARCHAR2,
180 x_err_code OUT NOCOPY NUMBER);
181
182 PROCEDURE get_orig_event_amts( x_project_id IN NUMBER,
183 x_event_num IN NUMBER,
184 x_task_id IN NUMBER,
185 x_set_of_books_id IN NUMBER,
186 x_bill_amount OUT NOCOPY NUMBER,
187 x_revenue_amount OUT NOCOPY NUMBER,
188 x_rev_rate_type OUT NOCOPY VARCHAR2,
189 x_rev_exchange_rate OUT NOCOPY NUMBER,
190 x_rev_exchange_date OUT NOCOPY DATE,
191 x_inv_exchange_rate OUT NOCOPY NUMBER,
192 x_inv_exchange_date OUT NOCOPY DATE,
193 x_err_stack IN OUT NOCOPY VARCHAR2,
194 x_err_stage IN OUT NOCOPY VARCHAR2,
195 x_err_code OUT NOCOPY NUMBER);
196
197
198 PROCEDURE get_imported_rates( x_set_of_books_id IN NUMBER,
199 x_exp_item_id IN NUMBER,
200 x_raw_cost OUT NOCOPY NUMBER,
201 x_raw_cost_rate OUT NOCOPY NUMBER,
202 x_burden_cost OUT NOCOPY NUMBER,
203 x_burden_cost_rate OUT NOCOPY NUMBER,
204 x_exchange_rate OUT NOCOPY NUMBER,
205 x_exchange_date OUT NOCOPY DATE,
206 x_exchange_rate_type OUT NOCOPY VARCHAR2,
207 x_err_stack IN OUT NOCOPY VARCHAR2,
208 x_err_stage IN OUT NOCOPY VARCHAR2,
209 x_err_code OUT NOCOPY NUMBER);
210
211
212 PROCEDURE get_ap_keys( x_eiid IN NUMBER,
213 x_ref2 OUT NOCOPY VARCHAR2,
214 x_ref3 OUT NOCOPY VARCHAR2,
215 x_err_stack IN OUT NOCOPY VARCHAR2,
216 x_err_stage IN OUT NOCOPY VARCHAR2,
217 x_err_code OUT NOCOPY NUMBER);
218
219 /* added two IN parameters to get_ap_rate, system_reference4 and transaction_source
220 for AP Variance processing*/
221
222 PROCEDURE get_ap_rate( x_invoice_id IN NUMBER,
223 x_line_num IN NUMBER,
224 x_system_reference4 IN VARCHAR2 DEFAULT NULL,
225 x_transaction_source IN VARCHAR2 DEFAULT NULL,
226 x_sob IN NUMBER,
227 x_exchange_rate OUT NOCOPY NUMBER,
228 x_exchange_date OUT NOCOPY DATE,
229 x_exchange_rate_type OUT NOCOPY VARCHAR2,
230 x_amount OUT NOCOPY NUMBER,
231 x_err_stack IN OUT NOCOPY VARCHAR2,
232 x_err_stage IN OUT NOCOPY VARCHAR2,
233 x_err_code OUT NOCOPY NUMBER);
234
235 FUNCTION sum_rev_rdl( x_project_id IN NUMBER,
236 x_dr_num IN NUMBER,
237 x_sob IN NUMBER) RETURN NUMBER;
238 PRAGMA RESTRICT_REFERENCES( sum_rev_rdl, WNPS,WNDS );
239
240 FUNCTION sum_inv( x_project_id IN NUMBER,
241 x_di_num IN NUMBER,
242 x_line_num IN NUMBER,
243 x_sob IN NUMBER) RETURN NUMBER;
244 PRAGMA RESTRICT_REFERENCES( sum_inv, WNPS,WNDS );
245
246 FUNCTION sum_inv_rdl( x_project_id IN NUMBER,
247 x_di_num IN NUMBER,
248 x_line_num IN NUMBER,
249 x_sob IN NUMBER) RETURN NUMBER;
250 PRAGMA RESTRICT_REFERENCES( sum_inv_rdl, WNPS,WNDS );
251
252 FUNCTION sum_inv_erdl( x_project_id IN NUMBER,
253 x_di_num IN NUMBER,
254 x_line_num IN NUMBER,
255 x_sob IN NUMBER) RETURN NUMBER;
256 PRAGMA RESTRICT_REFERENCES( sum_inv_erdl, WNPS,WNDS );
257
258 FUNCTION sum_inv_ev( x_project_id IN NUMBER,
259 x_task_id IN NUMBER,
260 x_event_num IN NUMBER,
261 x_sob IN NUMBER) RETURN NUMBER;
262 PRAGMA RESTRICT_REFERENCES( sum_inv_ev, WNPS,WNDS );
263
264 FUNCTION sum_mc_cust_rdl_erdl( x_project_id IN NUMBER,
265 x_draft_revenue_num IN NUMBER,
266 x_draft_revenue_item_line_num IN NUMBER) RETURN NUMBER;
267 PRAGMA RESTRICT_REFERENCES( sum_mc_cust_rdl_erdl, WNPS,WNDS );
268
269 FUNCTION event_date( x_project_id IN NUMBER,
270 x_task_id IN NUMBER,
271 x_event_Num IN NUMBER) RETURN DATE;
272 PRAGMA RESTRICT_REFERENCES( event_date, WNPS,WNDS );
273
274 FUNCTION orgid( x_project_id IN NUMBER) RETURN NUMBER;
275 PRAGMA RESTRICT_REFERENCES( orgid, WNPS,WNDS );
276
277 FUNCTION get_wo_factor(x_project_id IN NUMBER,
278 x_di_num IN NUMBER,
279 x_di_num_org IN NUMBER ) RETURN NUMBER;
280
281 FUNCTION get_cancel_flag( x_project_id IN NUMBER,
282 x_di_num IN NUMBER ) RETURN VARCHAR2;
283
284 FUNCTION get_invoice_action RETURN VARCHAR2;
285
286 FUNCTION get_rtn_amount( x_project_id IN NUMBER,
287 x_di_num IN NUMBER,
288 x_rtn_pcnt IN NUMBER,
289 x_sob_id IN NUMBER ) RETURN NUMBER;
290 PRAGMA RESTRICT_REFERENCES( get_rtn_amount , WNPS,WNDS );
291
292
293 PROCEDURE raise_error(x_msg IN VARCHAR2,
294 x_module IN VARCHAR2,
295 x_currency IN VARCHAR2 default NULL );
296
297 /*------------------------------ ins_mc_txn_interface_all ----------------------*/
298 /* This procedure will populate the Pa_mc_txn_interface_all table for a invoice */
299 /* distribution line pulled over from AP . First it will look for the data in */
300 /* the AP MRC sub-table otherwise it will get the rates from GL based on the */
301 /* Invoice Date and compute the amounts and populate the pa_mc_txn_interface_all*/
302 /* table */
303 /*------------------------------------------------------------------------------*/
304
305 /* Changed the IN parameter names and local variables
309 p_invoice_payment_id to p_system_reference4
306 from p_vendor_id to p_system_reference1,
307 p_invoice_id to p_system_reference2,
308 p_dist_line_num to p_system_reference3,
310 */
311
312 /*
313 PROCEDURE ins_mc_txn_interface_all(
314
315 p_vendor_id IN NUMBER,
316 p_invoice_id IN NUMBER,
317 p_dist_line_num IN NUMBER,
318 p_interface_id IN NUMBER,
319 p_transaction_source IN VARCHAR2,
320 p_invoice_payment_id IN NUMBER DEFAULT NULL);
321 */
322
323 PROCEDURE ins_mc_txn_interface_all(
324 p_system_reference1 IN NUMBER,
325 p_system_reference2 IN NUMBER,
326 p_system_reference3 IN NUMBER,
327 p_system_reference4 IN VARCHAR2 DEFAULT NULL,
328 p_interface_id IN NUMBER,
329 p_transaction_source IN VARCHAR2,
330 p_acct_evt_id IN NUMBER DEFAULT NULL); --pricing changes, added param p_acct_evt_id
331
332 PROCEDURE get_ccdl_tp_amts( x_exp_item_id IN NUMBER,
333 x_set_of_books_id IN NUMBER,
334 x_transfer_price OUT NOCOPY NUMBER,
335 x_tp_exchange_rate OUT NOCOPY NUMBER,
336 x_tp_exchange_date OUT NOCOPY DATE,
337 x_tp_rate_type OUT NOCOPY VARCHAR2,
338 x_err_stack IN OUT NOCOPY VARCHAR2,
339 x_err_stage IN OUT NOCOPY VARCHAR2,
340 x_err_code OUT NOCOPY NUMBER);
341
342 PROCEDURE get_invdtl_tp_amts( x_exp_item_id IN NUMBER,
343 x_set_of_books_id IN NUMBER,
344 x_transfer_price OUT NOCOPY NUMBER,
345 x_tp_exchange_rate OUT NOCOPY NUMBER,
346 x_tp_exchange_date OUT NOCOPY DATE,
347 x_tp_rate_type OUT NOCOPY VARCHAR2,
348 x_err_stack IN OUT NOCOPY VARCHAR2,
349 x_err_stage IN OUT NOCOPY VARCHAR2,
350 x_err_code OUT NOCOPY NUMBER);
351
352
353 PROCEDURE get_po_rate( x_po_dist_id IN NUMBER,
354 x_rcv_txn_id IN VARCHAR2,
355 x_transaction_source IN VARCHAR2,
356 x_sob IN NUMBER,
357 x_exchange_rate OUT NOCOPY NUMBER,
358 x_exchange_date OUT NOCOPY DATE,
359 x_exchange_rate_type OUT NOCOPY VARCHAR2,
360 x_amount OUT NOCOPY NUMBER,
361 x_err_stack IN OUT NOCOPY VARCHAR2,
362 x_err_stage IN OUT NOCOPY VARCHAR2,
366 G_PREV_ORG_ID NUMBER(15);
363 x_err_code OUT NOCOPY NUMBER,
364 x_acct_evt_id IN NUMBER DEFAULT NULL); --pricing changes, added param p_acct_evt_id
365
367 G_PREV_CURRENCY VARCHAR2(30);
368 G_PREV_ORG_ID2 NUMBER(15);
369 G_PREV_SOB_ID NUMBER(15);
370
371 --Introduced for Re-Burdening process.
372
373 PROCEDURE eiid_details( x_eiid IN NUMBER,
374 x_orig_trx OUT NOCOPY VARCHAR2,
375 x_adj_item OUT NOCOPY NUMBER,
376 x_linkage OUT NOCOPY VARCHAR2,
377 x_ei_date OUT NOCOPY DATE,
378 x_txn_source OUT NOCOPY VARCHAR2,
379 x_ei_burdened_cost OUT NOCOPY NUMBER,
380 x_ei_burdened_delta OUT NOCOPY NUMBER,
381 x_err_stack IN OUT NOCOPY VARCHAR2,
382 x_err_stage IN OUT NOCOPY VARCHAR2,
383 x_err_code OUT NOCOPY NUMBER);
384
385 END PA_MC_CURRENCY_PKG;
[Home] [Help]