[Home] [Help]
PACKAGE BODY: APPS.AP_OPEN_BAL_REV_RPT_PKG
Source
1 PACKAGE BODY ap_open_bal_rev_rpt_pkg
2 -- $Header: APOBRRPB.pls 120.4 2008/01/25 11:22:33 sgudupat noship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Corporation Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- APOBRRPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of AP_OPEN_BAL_REV_RPT_PKG.
13 -- This package is used to generate AP Open Balances Revaluation Report.
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 -- DEPENDENCIES
20 -- None.
21 --
22 --
23 -- LAST UPDATE DATE 25-JAN-2008
24 -- Date the program has been modified for the last time
25 --
26 -- HISTORY
27 -- =======
28 --
29 -- VERSION DATE AUTHOR(S) DESCRIPTION
30 -- ------- ----------- --------------- ------------------------------------
31 -- 1.0 21-FEB-2007 Praveen Gollu Creation
32 -- 1.1 25-JAN-2008 Sandeep G Fix for Bug #6773558
33 --****************************************************************************************
34 AS
35 FUNCTION beforereport
36 RETURN BOOLEAN
37 IS
38 lc_exchange_rate_type VARCHAR2 (2000);
39 ex_exchange_rate_type EXCEPTION;
40 ln_exch_rate NUMBER;
41 BEGIN
42 SELECT gl.ledger_id, gl.currency_code
43 INTO gc_ledger_id, gc_func_currency
44 FROM gl_ledgers gl, hr_operating_units hou
45 WHERE hou.organization_id = fnd_profile.VALUE ('org_id')
46 AND gl.ledger_id = hou.set_of_books_id;
47
48 BEGIN
49 SELECT NAME
50 INTO gc_operating_name
51 FROM hr_operating_units
52 WHERE organization_id = p_org_id;
53 EXCEPTION
54 WHEN NO_DATA_FOUND
55 THEN
56 gc_operating_name := NULL;
57 END;
58
59 IF p_org_id IS NULL
60 THEN
61 gc_ou_where := 'AND 1=1';
62 ELSE
63 gc_ou_where := 'AND ai.org_id=:p_org_id';
64 END IF;
65
66 IF p_include_domestic_inv = 'N'
67 THEN
68 gc_include_dom_inv :=
69 ' AND ai.invoice_currency_code <> :gc_func_currency';
70 ELSE
71 gc_include_dom_inv := ' AND 1 = 1';
72 END IF;
73
74 IF p_supplier IS NOT NULL
75 THEN
76 SELECT vendor_name
77 INTO gc_supplier_name
78 FROM po_vendors
79 WHERE vendor_id = p_supplier;
80 ELSE
81 gc_supplier_name := NULL;
82 END IF;
83
84 IF p_supplier IS NOT NULL
85 THEN
86 gc_supplier := ' AND ai.vendor_id=:P_SUPPLIER';
87 ELSE
88 gc_supplier := ' AND 1 = 1';
89 END IF;
90
91 IF p_currency = 'ANY' OR p_currency IS NULL
92 THEN
93 IF p_exchange_rate_type = 'User'
94 THEN
95 fnd_message.set_name ('SQLAP', 'AP_EXCHANGE_RATE_TYPE');
96 lc_exchange_rate_type := fnd_message.get;
97 RAISE ex_exchange_rate_type;
98 ELSE
99 gc_currency := ' AND 1 = 1';
100 END IF;
101 ELSE
102 gc_currency := 'AND ai.invoice_currency_code = :P_CURRENCY';
103 END IF;
104
105 RETURN TRUE;
106 EXCEPTION
107 WHEN ex_exchange_rate_type
108 THEN
109 fnd_file.put_line (fnd_file.LOG, lc_exchange_rate_type);
110 RETURN FALSE;
111 END beforereport;
112
113 FUNCTION exch_rate_calc (currency IN VARCHAR2)
114 RETURN NUMBER
115 AS
116 ln_exch_rate NUMBER;
117 p_date_from VARCHAR2 (11);
118 lc_error_msg VARCHAR2 (3000);
119 lc_exchange_rate_type VARCHAR2 (2000);
120 ex_exchange_rate_type EXCEPTION;
121 BEGIN
122 BEGIN
123 IF currency <> gc_func_currency
124 THEN
125 BEGIN
126 SELECT gdr.conversion_rate
127 INTO ln_exch_rate
128 FROM gl_daily_rates gdr
129 WHERE gdr.conversion_type = p_exchange_rate_type
130 AND gdr.from_currency = currency
131 AND gdr.to_currency = gc_func_currency
132 AND gdr.conversion_date = p_as_of_date;
133 EXCEPTION
134 WHEN NO_DATA_FOUND
135 THEN
136 fnd_message.set_name ('SQLAP', 'AP_EXCHANGE_RATE');
137 fnd_message.set_token ('P_EXCHANGE_RATE_TYPE',
138 p_exchange_rate_type
139 );
140 fnd_message.set_token ('CURRENCY', currency);
141 fnd_message.set_token ('P_AS_OF_DATE', p_as_of_date);
142 lc_exchange_rate_type := fnd_message.get;
143 fnd_file.put_line (fnd_file.LOG, lc_exchange_rate_type);
144 raise_application_error (-20101, lc_exchange_rate_type);
145 END;
146 ELSE
147 ln_exch_rate := 1;
148 END IF;
149
150 RETURN (ln_exch_rate);
151 EXCEPTION
152 WHEN ex_exchange_rate_type
153 THEN
154 fnd_file.put_line (fnd_file.LOG, lc_exchange_rate_type);
155 END;
156 END exch_rate_calc;
157
158 FUNCTION amtduereval (
159 func_curr_amt IN NUMBER,
160 exch_rate IN NUMBER,
161 tran_curr IN VARCHAR
162 )
163 RETURN NUMBER
164 IS
165 BEGIN
166 IF tran_curr = gc_func_currency
167 THEN
168 RETURN (NVL (func_curr_amt, 0));
169 ELSE
170 RETURN (NVL (func_curr_amt * exch_rate, 0));
171 END IF;
172 END amtduereval;
173
174 FUNCTION vat_calc_amt (
175 prepay_amt_app IN NUMBER,
176 tax_amt IN NUMBER,
177 inv_amt IN NUMBER
178 )
179 RETURN NUMBER
180 IS
181 return_number NUMBER;
182 sum_amt NUMBER;
183 BEGIN
184 fnd_file.put_line (fnd_file.LOG,
185 'return'
186 || (inv_amt - tax_amt)
187 * ((inv_amt - prepay_amt_app) / inv_amt)
188 );
189 sum_amt := inv_amt + tax_amt;
190 return_number :=
191 (sum_amt - tax_amt)
192 * ((sum_amt - prepay_amt_app) / sum_amt);
193 RETURN (return_number);
194 END vat_calc_amt;
195
196 FUNCTION amtduefilter (p_amt_due IN NUMBER)
197 RETURN BOOLEAN
198 IS
199 BEGIN
200 IF p_amt_due = 0 OR p_amt_due IS NULL
201 THEN
202 RETURN (FALSE);
203 ELSE
204 RETURN (TRUE);
205 END IF;
206 END;
207 END ap_open_bal_rev_rpt_pkg;