[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.5.12020000.2 2012/07/13 12:13:30 mkmeda ship $
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 -- bug9564002
43 P_ORG_ID := NVL(P_ORG_ID, FND_GLOBAL.ORG_ID);
44
45 SELECT gl.ledger_id, gl.currency_code
46 INTO gc_ledger_id, gc_func_currency
47 FROM gl_ledgers gl, hr_operating_units hou
48 /* Bug 9725033 replaced fnd_profile.VALUE ('org_id') with fnd_global.org_id() */
49 WHERE hou.organization_id = P_ORG_ID --bug9564002
50 AND gl.ledger_id = hou.set_of_books_id;
51
52 BEGIN
53 SELECT NAME
54 INTO gc_operating_name
55 FROM hr_operating_units
56 WHERE organization_id = p_org_id;
57 EXCEPTION
58 WHEN NO_DATA_FOUND
59 THEN
60 gc_operating_name := NULL;
61 END;
62
63 IF p_org_id IS NULL
64 THEN
65 gc_ou_where := 'AND 1=1';
66 ELSE
67 gc_ou_where := 'AND ai.org_id=:p_org_id';
68 END IF;
69
70 IF p_include_domestic_inv = 'N'
71 THEN
72 gc_include_dom_inv :=
73 ' AND ai.invoice_currency_code <> :gc_func_currency';
74 ELSE
75 gc_include_dom_inv := ' AND 1 = 1';
76 END IF;
77
78 IF p_supplier IS NOT NULL
79 THEN
80 SELECT vendor_name
81 INTO gc_supplier_name
82 FROM po_vendors
83 WHERE vendor_id = p_supplier;
84 ELSE
85 gc_supplier_name := NULL;
86 END IF;
87
88 IF p_supplier IS NOT NULL
89 THEN
90 gc_supplier := ' AND ai.vendor_id=:P_SUPPLIER';
91 ELSE
92 gc_supplier := ' AND 1 = 1';
93 END IF;
94
95 IF p_currency = 'ANY' OR p_currency IS NULL
96 THEN
97 IF p_exchange_rate_type = 'User'
98 THEN
99 fnd_message.set_name ('SQLAP', 'AP_EXCHANGE_RATE_TYPE');
100 lc_exchange_rate_type := fnd_message.get;
101 RAISE ex_exchange_rate_type;
102 ELSE
103 gc_currency := ' AND 1 = 1';
104 END IF;
105 ELSE
106 gc_currency := 'AND ai.invoice_currency_code = :P_CURRENCY';
107 END IF;
108
109 RETURN TRUE;
110 EXCEPTION
111 WHEN ex_exchange_rate_type
112 THEN
113 fnd_file.put_line (fnd_file.LOG, lc_exchange_rate_type);
114 RETURN FALSE;
115 END beforereport;
116
117 FUNCTION exch_rate_calc (currency IN VARCHAR2)
118 RETURN NUMBER
119 AS
120 ln_exch_rate NUMBER;
121 p_date_from VARCHAR2 (11);
122 lc_error_msg VARCHAR2 (3000);
123 lc_exchange_rate_type VARCHAR2 (2000);
124 ex_exchange_rate_type EXCEPTION;
125 BEGIN
126 BEGIN
127 IF currency <> gc_func_currency
128 THEN
129 BEGIN
130 SELECT gdr.conversion_rate
131 INTO ln_exch_rate
132 FROM gl_daily_rates gdr
133 WHERE gdr.conversion_type = p_exchange_rate_type
134 AND gdr.from_currency = currency
135 AND gdr.to_currency = gc_func_currency
136 AND gdr.conversion_date = p_as_of_date;
137 EXCEPTION
138 WHEN NO_DATA_FOUND
139 THEN
140 fnd_message.set_name ('SQLAP', 'AP_EXCHANGE_RATE');
141 fnd_message.set_token ('P_EXCHANGE_RATE_TYPE',
142 p_exchange_rate_type
143 );
144 fnd_message.set_token ('CURRENCY', currency);
145 fnd_message.set_token ('P_AS_OF_DATE', p_as_of_date);
146 lc_exchange_rate_type := fnd_message.get;
147 fnd_file.put_line (fnd_file.LOG, lc_exchange_rate_type);
148 raise_application_error (-20101, lc_exchange_rate_type);
149 END;
150 ELSE
151 ln_exch_rate := 1;
152 END IF;
153
154 RETURN (ln_exch_rate);
155 EXCEPTION
156 WHEN ex_exchange_rate_type
157 THEN
158 fnd_file.put_line (fnd_file.LOG, lc_exchange_rate_type);
159 END;
160 END exch_rate_calc;
161
162 FUNCTION amtduereval (
163 func_curr_amt IN NUMBER,
164 exch_rate IN NUMBER,
165 tran_curr IN VARCHAR
166 )
167 RETURN NUMBER
168 IS
169 BEGIN
170 IF tran_curr = gc_func_currency
171 THEN
172 RETURN (NVL (func_curr_amt, 0));
173 ELSE
174 RETURN (NVL (func_curr_amt * exch_rate, 0));
175 END IF;
176 END amtduereval;
177
178 FUNCTION vat_calc_amt (
179 prepay_amt_app IN NUMBER,
180 tax_amt IN NUMBER,
181 inv_amt IN NUMBER
182 )
183 RETURN NUMBER
184 IS
185 return_number NUMBER;
186 sum_amt NUMBER;
187 BEGIN
188 fnd_file.put_line (fnd_file.LOG,
189 'return'
190 || (inv_amt - tax_amt)
191 * ((inv_amt - prepay_amt_app) / inv_amt)
192 );
193 sum_amt := inv_amt + tax_amt;
194 return_number :=
195 (sum_amt - tax_amt)
196 * ((sum_amt - prepay_amt_app) / sum_amt);
197 RETURN (return_number);
198 END vat_calc_amt;
199
200 FUNCTION amtduefilter (p_amt_due IN NUMBER)
201 RETURN BOOLEAN
202 IS
203 BEGIN
204 IF p_amt_due = 0 OR p_amt_due IS NULL
205 THEN
206 RETURN (FALSE);
207 ELSE
208 RETURN (TRUE);
209 END IF;
210 END;
211 END ap_open_bal_rev_rpt_pkg;