[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_VARIABLES_PKG
Source
1 PACKAGE BODY POA_EDW_VARIABLES_PKG AS
2 /* $Header: poavarb.pls 115.7 2004/01/28 06:35:05 sdiwakar noship $ */
3
4 -- We populate some variables in order to avoid checking NULL value
5
6 l_dist_id_check_cut NUMBER := -99977;
7 l_check_cut_out DATE;
8 l_line_loc_goods_rcv NUMBER := -99977;
9 l_goods_rcv_out DATE;
10 l_dist_id_inv_create NUMBER := -99977;
11 l_inv_create_out DATE;
12 l_dist_id_inv_rcv NUMBER := -99977;
13 l_inv_rcv_out DATE;
14 l_req_id_req_app NUMBER;
15 l_req_app_out DATE;
16 l_dist_id_ipv NUMBER := -99977;
17 l_ipv_out NUMBER;
18 l_dist_id_supp_app NUMBER := -99977;
19 l_supp_app_out VARCHAR2(240);
20 l_header_id_app_by NUMBER := -99977;
21 l_app_by_out VARCHAR2(240);
22 l_doc_id_acc_date NUMBER;
23 l_p_type_acc_date VARCHAR2(15);
24 l_acc_date_out DATE;
25
26 l_rate_date DATE := to_date('01/01/0039', 'DD/MM/YYYY');
27 l_item_id NUMBER := -99998;
28 l_rate NUMBER := -99999;
29 l_uom_code VARCHAR2(150) := '**ZZZZZZZ';
30 l_uom_conv_rate NUMBER;
31 l_global_currency_rate NUMBER;
32 l_currency_code VARCHAR2(150) := '**XXXXXXX';
33 l_rate_type VARCHAR2(150) := '**YYYYYYY';
34
35 -- We populate some variables in order to avoid checking NULL value
36
37 PROCEDURE init
38 IS
39 BEGIN
40
41 l_dist_id_check_cut := -99977;
42 l_check_cut_out := NULL;
43 l_line_loc_goods_rcv := -99977;
44 l_goods_rcv_out := NULL;
45 l_dist_id_inv_create := -99977;
46 l_inv_create_out := NULL;
47 l_dist_id_inv_rcv := -99977;
48 l_inv_rcv_out := NULL;
49 l_req_id_req_app := NULL;
50 l_req_app_out := NULL;
51 l_dist_id_ipv := -99977;
52 l_ipv_out := NULL;
53 l_dist_id_supp_app := -99977;
54 l_supp_app_out := NULL;
55 l_header_id_app_by := -99977;
56 l_app_by_out := NULL;
57 l_doc_id_acc_date := NULL;
58 l_p_type_acc_date := NULL;
59 l_acc_date_out := NULL;
60
61 l_rate_date := to_date('01/01/0039', 'DD/MM/YYYY');
62 l_item_id := -99998;
63 l_rate := -99999;
64 l_uom_code := '**ZZZZZZZ';
65 l_uom_conv_rate := NULL;
66 l_global_currency_rate := NULL;
67 l_currency_code := '**XXXXXXX';
68 l_rate_type := '**YYYYYYY';
69
70 END init;
71
72 FUNCTION get_check_cut_date (p_po_distribution_id IN NUMBER) RETURN DATE
73 IS
74 BEGIN
75
76 /* --this can be ignored
77 if (p_po_distribution_id IS NULL) then
78 l_check_cut_out := NULL;
79 l_dist_id_check_cut := p_po_distribution_id;
80 return l_check_cut_out;
81 end if;
82 */
83
84 if (p_po_distribution_id <> l_dist_id_check_cut) then
85 l_dist_id_check_cut := p_po_distribution_id;
86 l_check_cut_out := POA_EDW_SPEND_PKG.get_check_cut_date(p_po_distribution_id);
87 end if;
88
89 return l_check_cut_out;
90
91 END GET_CHECK_CUT_DATE;
92
93 FUNCTION get_goods_received_date (p_po_line_location_id IN NUMBER) RETURN DATE
94 IS
95 BEGIN
96
97 /* --this can be ignored
98 if (p_po_line_location_id IS NULL) then
99 l_goods_rcv_out := NULL;
100 l_line_loc_goods_rcv := p_po_line_location_id;
101 return l_goods_rcv_out;
102 end if;
103 */
104
105 if (p_po_line_location_id <> l_line_loc_goods_rcv) then
106 l_line_loc_goods_rcv := p_po_line_location_id;
107 l_goods_rcv_out := POA_EDW_SPEND_PKG.get_goods_received_date(p_po_line_location_id);
108 end if;
109 return l_goods_rcv_out;
110
111 END GET_GOODS_RECEIVED_DATE;
112
113 FUNCTION get_invoice_creation_date (p_po_distribution_id IN NUMBER) RETURN DATE
114 IS
115 BEGIN
116
117 /* --this can be ignored
118 if (p_po_distribution_id IS NULL) then
119 l_inv_create_out := NULL;
120 l_dist_id_inv_create := p_po_distribution_id;
121 return l_inv_create_out;
122 end if;
123 */
124
125 if (p_po_distribution_id <> l_dist_id_inv_create) then
126 l_dist_id_inv_create := p_po_distribution_id;
127 l_inv_create_out := POA_EDW_SPEND_PKG.get_invoice_creation_date(p_po_distribution_id);
128 end if;
129 return l_inv_create_out;
130
131 END get_invoice_creation_date;
132
133 FUNCTION get_invoice_received_date (p_po_distribution_id IN NUMBER) RETURN DATE
134 IS
135 BEGIN
136
137 /* --this can be ignored
138 if (p_po_distribution_id IS NULL) then
139 l_inv_rcv_out := NULL;
140 l_dist_id_inv_rcv := p_po_distribution_id;
141 return l_inv_rcv_out;
142 end if;
143 */
144
145 if (p_po_distribution_id <> l_dist_id_inv_rcv) then
146 l_dist_id_inv_rcv := p_po_distribution_id;
147 l_inv_rcv_out := POA_EDW_SPEND_PKG.get_invoice_received_date(p_po_distribution_id);
148 end if;
149
150 return l_inv_rcv_out;
151
152 END get_invoice_received_date;
153
154 FUNCTION get_req_approval_date (p_po_req_dist_id IN NUMBER) RETURN DATE
155 IS
156 BEGIN
157
158 if (p_po_req_dist_id IS NULL) then
159 l_req_app_out := NULL;
160 l_req_id_req_app := p_po_req_dist_id;
161 return l_req_app_out;
162 end if;
163
164 if (p_po_req_dist_id <> l_req_id_req_app OR l_req_id_req_app is NULL) then
165 l_req_id_req_app := p_po_req_dist_id;
166 l_req_app_out := POA_EDW_SPEND_PKG.get_req_approval_date(p_po_req_dist_id);
167 end if;
168
169 return l_req_app_out;
170
171 END get_req_approval_date;
172
173
174 FUNCTION get_ipv (p_po_distribution_id IN NUMBER) RETURN NUMBER
175 IS
176 BEGIN
177
178 /* --this can be ignored
179 if (p_po_distribution_id IS NULL) then
180 l_ipv_out := NULL;
181 l_dist_id_ipv := p_po_distribution_id;
182 return l_ipv_out;
183 end if;
184 */
185
186 if (p_po_distribution_id <> l_dist_id_ipv) then
187 l_dist_id_ipv := p_po_distribution_id;
188 l_ipv_out := POA_EDW_SPEND_PKG.get_ipv(p_po_distribution_id);
189 end if;
190 return l_ipv_out;
191
192 END get_ipv;
193
194 FUNCTION get_supplier_approved (p_po_distribution_id IN NUMBER) RETURN VARCHAR2
195 IS
196 BEGIN
197
198 /* --this can be ignored
199 if (p_po_distribution_id IS NULL) then
200 l_supp_app_out := NULL;
201 l_dist_id_supp_app := p_po_distribution_id;
202 return l_supp_app_out;
203 end if;
204 */
205
206 if (p_po_distribution_id <> l_dist_id_supp_app) then
207 l_dist_id_supp_app := p_po_distribution_id;
208 l_supp_app_out := POA_EDW_SPEND_PKG.get_supplier_approved(p_po_distribution_id);
209 end if;
210 return l_supp_app_out;
211
212 END get_supplier_approved;
213
214 FUNCTION get_supplier_approved (p_po_distribution_id IN NUMBER,
215 p_vendor_id IN NUMBER,
216 p_vendor_site_id IN NUMBER,
217 p_ship_to_org_id IN NUMBER,
218 p_item_id IN NUMBER,
219 p_category_id IN NUMBER) RETURN VARCHAR2
220 IS
221 BEGIN
222
223 /* --this can be ignored
224 if (p_po_distribution_id IS NULL) then
225 l_supp_app_out := NULL;
226 l_dist_id_supp_app := p_po_distribution_id;
227 return l_supp_app_out;
228 end if;
229 */
230
231 if (p_po_distribution_id <> l_dist_id_supp_app) then
232 l_dist_id_supp_app := p_po_distribution_id;
233 l_supp_app_out := POA_EDW_SPEND_PKG.get_supplier_approved(p_po_distribution_id, p_vendor_id, p_vendor_site_id, p_ship_to_org_id, p_item_id, p_category_id);
234 end if;
235 return l_supp_app_out;
236
237 END get_supplier_approved;
238
239 FUNCTION approved_by (p_po_header_id IN NUMBER) RETURN NUMBER
240 IS
241 BEGIN
242
243 /* --this can be ignored
244 if (p_po_header_id IS NULL) then
245 l_app_by_out := NULL;
246 l_header_id_app_by := p_po_header_id;
247 return l_app_by_out;
248 end if;
249 */
250
251 if (p_po_header_id <> l_header_id_app_by) then
252 l_header_id_app_by := p_po_header_id;
253 l_app_by_out := POA_EDW_SPEND_PKG.approved_by(p_po_header_id);
254 end if;
255 return l_app_by_out;
256
257 END approved_by;
258
259 FUNCTION get_acceptance_date (p_po_doc_id IN NUMBER, p_type IN VARCHAR2) RETURN DATE
260 IS
261 BEGIN
262
263 if (p_po_doc_id IS NULL OR p_type IS NULL) then
264 l_acc_date_out := NULL;
265 l_doc_id_acc_date := p_po_doc_id;
266 l_p_type_acc_date := p_type;
267 return l_acc_date_out;
268 end if;
269
270 if (p_po_doc_id <> l_doc_id_acc_date OR p_type <> l_p_type_acc_date OR
271 l_doc_id_acc_date is NULL OR l_p_type_acc_date is NULL) then
272 l_doc_id_acc_date := p_po_doc_id;
273 l_p_type_acc_date := p_type;
274 l_acc_date_out := POA_EDW_SPEND_PKG.get_acceptance_date(p_po_doc_id, p_type);
275 end if;
276 return l_acc_date_out;
277
278 END get_acceptance_date;
279
280 ---------------------
281
282 FUNCTION get_global_currency_rate (p_rate_type VARCHAR2,
283 p_currency_code VARCHAR2,
284 p_rate_date DATE,
285 p_rate NUMBER) RETURN NUMBER
286 IS
287 BEGIN
288
289 if (p_rate_type is NULL) then
290 if (p_rate_date <> l_rate_date OR p_currency_code <> l_currency_code
291 OR l_rate_type is NOT NULL) then
292 l_currency_code := p_currency_code;
293 l_rate_date := p_rate_date;
294 l_rate_type := p_rate_type;
295 l_global_currency_rate := edw_currency.get_rate (
296 p_currency_code, p_rate_date, NULL);
297 end if;
298 elsif (p_rate_type = 'User') then
299 if (p_rate_date <> l_rate_date OR p_currency_code <> l_currency_code OR
300 p_rate_type <> l_rate_type OR l_rate_type is NULL OR
301 p_rate <> l_rate) then
302 l_currency_code := p_currency_code;
303 l_rate_date := p_rate_date;
304 l_rate_type := p_rate_type;
305 l_rate := p_rate;
306 l_global_currency_rate := edw_currency.get_rate (
307 p_currency_code, p_rate_date, NULL) * p_rate;
308 end if;
309 else /* p_rate_type is NOT NULL and p_rate_type <> 'User' */
310 if (p_rate_date <> l_rate_date OR p_currency_code <> l_currency_code OR
311 p_rate_type <> l_rate_type OR l_rate_type is NULL) then
312 l_rate_type := p_rate_type;
313 l_currency_code := p_currency_code;
314 l_rate_date := p_rate_date;
315 l_global_currency_rate := edw_currency.get_rate (
316 p_currency_code, p_rate_date, p_rate_type);
317 end if;
318 end if;
319
320 return l_global_currency_rate;
321
322 END get_global_currency_rate;
323
324
325 FUNCTION get_uom_conv_rate (p_uom_code VARCHAR2,
326 p_item_id NUMBER) RETURN NUMBER
327 IS
328 BEGIN
329
330 /* --this can be ignored
331 if(p_uom_code is NULL) then
332 l_uom_code := p_uom_code;
333 l_uom_conv_rate := NULL;
334 return l_uom_conv_rate;
335 end if;
336 */
337
338 if (p_item_id is NULL) then
339 if (p_uom_code <> l_uom_code OR l_item_id is NOT NULL) then
340 l_uom_code := p_uom_code;
341 l_item_id := p_item_id;
342 l_uom_conv_rate := edw_util.get_uom_conv_rate (p_uom_code, p_item_id);
343 end if;
344 else
345 if (p_uom_code <> l_uom_code OR p_item_id <> l_item_id
346 OR l_item_id is NULL) then
347 l_uom_code := p_uom_code;
348 l_item_id := p_item_id;
349 l_uom_conv_rate := edw_util.get_uom_conv_rate (p_uom_code, p_item_id);
350 end if;
351 end if;
352
353 return l_uom_conv_rate;
354
355 END get_uom_conv_rate;
356
357
358 END POA_EDW_VARIABLES_PKG;