1 PACKAGE BODY AP_BAL_PKG
2 -- $Header: APPREBCPB.pls 120.12.12000000.1 2007/10/25 14:12:14 sgudupat noship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- APPREBCPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of AP_PREPAY_BAL_PKG.
13 -- This package is used to generate Prepayment Balance Report.
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 --PROGRAM LIST
20 --NAME USAGE
21 --
22 -- get_ledger_name Used to obtain the Ledger Name for which the report is running
23 --
24 -- get_no_of_holds Used to obtain the number of holds for a particular Prepayment
25 --
26 -- beforereport Used to initialize the dynamic variable based on the input
27 -- obtained from parameters.
28 --
29 -- DEPENDENCIES
30 -- None.
31 --
32 -- CALLED BY
33 -- DataTemplate Extract in Prepayment Balance Report (CZech).
34 --
35 --
36 -- LAST UPDATE DATE 27-Mar-2007
37 -- Date the program has been modified for the last time
38 --
39 -- HISTORY
40 -- =======
41 --
42 -- VERSION DATE AUTHOR(S) DESCRIPTION
43 -- ------- ----------- --------------- ------------------------------------
44 -- 1.00 27-Mar-2007 Sandeep Kumar G. Creation
45 --
46 -- ****************************************************************************************
47 AS
48
49 --=====================================================================
50 FUNCTION get_no_of_holds(p_invoice_id IN NUMBER)
51 RETURN NUMBER
52 IS
53 ln_count NUMBER;
54 BEGIN
55 BEGIN
56 SELECT COUNT(1)
57 INTO ln_count
58 FROM AP_HOLDS ah
59 WHERE ah.invoice_id = p_invoice_id
60 AND NVL(ah.status_flag,'X') <> 'R'; --Added the Condition Based on the Bug 6473102
61 EXCEPTION
62 WHEN NO_DATA_FOUND THEN
63 ln_count := 0;
64 END;
65 RETURN (ln_count);
66 END get_no_of_holds;
67
68 --=====================================================================
69 --=====================================================================
70 FUNCTION get_settlement_date(p_invoice_id IN NUMBER
71 ,p_line_number IN NUMBER)
72 RETURN DATE
73 IS
74 ld_settlement_date DATE;
75 BEGIN
76 BEGIN
77 SELECT MAX(AVPFPV.accounting_date)
78 INTO ld_settlement_date
79 FROM ap_view_prepays_fr_prepay_v AVPFPV
80 WHERE AVPFPV.prepay_id = p_invoice_id
81 AND AVPFPV.prepay_line_number = p_line_number;
82 EXCEPTION
83 WHEN no_data_found THEN
84 ld_settlement_date := NULL;
85 END;
86 RETURN ld_settlement_date;
87 END get_settlement_date;
88
89 --=====================================================================
90 --=====================================================================
91 FUNCTION description(p_seg_value IN VARCHAR2,p_seg_type IN VARCHAR2)
92 RETURN VARCHAR2
93 IS
94 v_value_desc VARCHAR2(100);
95 BEGIN
96 BEGIN
97 IF p_seg_type LIKE '%SEGMENT%' THEN
98 SELECT DISTINCT ffv.description
99 INTO v_value_desc
100 FROM gl_code_combinations gcc
101 ,fnd_id_flex_structures ffs
102 ,fnd_id_flex_segments fseg
103 ,fnd_flex_values_vl ffv
104 WHERE gcc.chart_of_accounts_id = ffs.id_flex_num
105 AND ffs.id_flex_num = fseg.id_flex_num
106 AND ffs.id_flex_code = fseg.id_flex_code
107 AND fseg.application_column_name = p_seg_type
108 AND fseg.flex_value_set_id = ffv.flex_value_set_id
109 AND ffs.id_flex_code = 'GL#'
110 AND gcc.chart_of_accounts_id = (SELECT chart_of_accounts_id
111 FROM gl_access_sets
112 WHERE access_set_id = fnd_profile.value('GL_ACCESS_SET_ID'))
113 AND ffv.flex_value = p_seg_value;
114 ELSE
115 SELECT ffv.description
116 INTO v_value_desc
117 FROM fnd_descr_flex_col_usage_vl fdfcu
118 ,fnd_flex_values_vl ffv
119 WHERE fdfcu.flex_value_set_id = ffv.flex_value_set_id
120 AND fdfcu.application_id = 200
121 AND fdfcu.descriptive_flexfield_name = 'AP_INVOICES'
122 AND fdfcu.descriptive_flex_context_code ='Global Data Elements'
123 AND fdfcu.application_column_name = p_seg_type
124 AND ffv.flex_value = p_seg_value;
125 END IF;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 v_value_desc := NULL;
129 END;
130 RETURN v_value_desc;
131 END description;
132
133 --=====================================================================
134 --=====================================================================
135 FUNCTION beforereport RETURN BOOLEAN
136 IS
137 ld_param_from_date DATE;
138 ld_param_to_date DATE;
139 ld_period_from_date DATE;
140 ld_period_to_date DATE;
141 BEGIN
142 BEGIN
143 SELECT gled.name
144 INTO gc_ledger_name
145 FROM gl_ledgers gled
146 ,gl_access_set_norm_assign gasna
147 WHERE gasna.access_set_id = FND_PROFILE.VALUE('GL_ACCESS_SET_ID')
148 AND gled.ledger_id = gasna.ledger_id
149 AND gled.ledger_category_code = 'PRIMARY';
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 gc_ledger_name := NULL;
153 END;
154
155 /*--*************************************************
156 -- Used for DATE Conditions
157 --*************************************************/
158 IF FROM_DATE_PARAM IS NULL THEN
159 ld_param_from_date := SYSDATE;
160 ld_param_to_date := SYSDATE;
161 ELSIF FROM_DATE_PARAM IS NOT NULL AND TO_DATE_PARAM IS NULL THEN
162 ld_param_from_date := TO_DATE(FROM_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
163 ld_param_to_date := TO_DATE(FROM_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
164 ELSE
165 ld_param_from_date := TO_DATE(FROM_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
166 ld_param_to_date := TO_DATE(TO_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
167 END IF;
168
169 IF PERIOD_FROM_PARAM IS NULL AND PERIOD_TO_PARAM IS NULL THEN
170 ld_period_from_date := SYSDATE;
171 ld_period_to_date := SYSDATE;
172 ELSE
173 SELECT gps.start_date
174 INTO ld_period_from_date
175 FROM gl_period_statuses gps
176 WHERE gps.period_name = PERIOD_FROM_PARAM
177 AND gps.application_id = 200
178 AND gps.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
179
180 SELECT gps.end_date
181 INTO ld_period_to_date
182 FROM gl_period_statuses gps
183 WHERE gps.period_name = PERIOD_TO_PARAM
184 AND gps.application_id = 200
185 AND gps.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
186 END IF;
187
188 IF ld_param_from_date <= ld_period_from_date THEN
189 gd_from_date := ld_param_from_date;
190 ELSE
191 gd_from_date := ld_period_from_date;
192 END IF;
193
194 --Made changes as per the Bug 6473102
195 IF PERIOD_TO_PARAM IS NOT NULL THEN
196 gd_to_date := ld_period_to_date;
197 ELSIF ld_param_to_date >= ld_period_to_date AND TO_DATE_PARAM IS NOT NULL THEN
198 gd_to_date := ld_param_to_date;
199 ELSIF ld_param_to_date < ld_period_to_date AND ld_period_to_date <> SYSDATE THEN
200 gd_to_date := ld_period_to_date;
201 ELSE
202 gd_to_date := ld_param_to_date;
203 END IF;
204
205 /*--*************************************************
206 -- Used for Currency Code Conditions
207 --*************************************************/
208 IF CURR_CODE_PARAM IS NOT NULL AND curr_code_param <> 'ANY' THEN
209 gc_currency := ' AND ai.invoice_currency_code = :CURR_CODE_PARAM ';
210 ELSE
211 gc_currency := ' AND 1 = 1 ';
212 END IF;
213 fnd_file.put_line(fnd_file.log,'gc_currency::'||gc_currency);
214
215 /*--*************************************************
216 -- Used for Posted Only (Y/N) Conditions
217 --*************************************************/
218
219 -- gc_from_clause := ' ,ap_checks_all ac ';
220 IF POSTED_ONLY_PARAM = 'Y' THEN
221 /*
222 gc_from_clause := gc_from_clause ||' ,gl_import_references gir '
223 ||' ,gl_je_headers gjh ';
224 gc_status := ' AND gir.gl_sl_link_id = xal.gl_sl_link_id '
225 ||' AND gir.gl_sl_link_table = xal.gl_sl_link_table '
226 ||' AND gir.je_header_id = gjh.je_header_id '
227 ||' AND xal.ledger_id = gjh.ledger_id '
228 ||' AND gjh.status = ''P''';
229 --gc_status := ' AND xah.gl_transfer_status_code = ''Y'' ';
230 gc_select_clause := ' gjh.status ';
231 */
232 --gc_status := ' AND gjh.status = ''P''';
233 gc_status := ' AND xah.gl_transfer_status_code = ''Y'' ';
234 ELSE
235 gc_status := ' AND 1 = 1 ';
236 -- gc_select_clause := ' NULL ';
237 END IF;
238 fnd_file.put_line(fnd_file.log,'gc_status::'||gc_status);
239 /*--*************************************************
240 --Dynamic WHERE based on supplier_from_param and supplier_to_param parameter
241 --*************************************************/
242 IF supplier_from_param IS NOT NULL AND supplier_to_param IS NOT NULL THEN
243 IF supplier_from_param < supplier_to_param THEN
244 gc_supplier := ' AND POV.vendor_name BETWEEN :supplier_from_param AND :supplier_to_param ';
245 ELSE
246 gc_supplier := ' AND POV.vendor_name BETWEEN :supplier_to_param AND :supplier_from_param ';
247 END IF;
248 ELSIF supplier_from_param IS NOT NULL AND supplier_to_param IS NULL THEN
249 gc_supplier := ' AND POV.vendor_name = :supplier_from_param';
250 ELSE
251 gc_supplier := ' AND 1 = 1';
252 END IF;
253 /*--*************************************************
254 -- These are mainly used to confirm which part of the query should result the
255 -- output and which sould not. This is decided based on the P_PAID parameter.
256 -- If the Parameter returns 'Y' then only Paid records will display hence only
257 -- first Query will result the output. If the P_PAID returns 'N' then both paid
258 -- and Unpaid records needs to be displayed hence both the records results the
259 -- data.
260 --*************************************************/
261 -- Commented the Code based on the Bug# 6497821
262 /*
263 gc_pre_amt_appl := ' NVL(avprpv.prepay_amount_applied,0) pre_amt_appl_fr_curr '
264 ||' ,NVL(avprpv.prepay_amount_applied,0) * NVL(ai.exchange_rate,1) pre_amt_appl_fn_curr';
265 gc_from_clause := ' ap_invoice_payments_all aip '||
266 ' ,ap_checks_all ac '||
267 ' ,(SELECT avprpv.org_id org_id
268 ,avprpv.prepay_id prepay_id
269 ,avprpv.prepay_line_number prepay_line_number
270 ,avprpv.prepay_amount_applied prepay_amount_applied
271 FROM ap_view_prepays_fr_prepay_v avprpv
272 WHERE NVL(avprpv.accounting_date,:gd_from_date) <= :gd_from_date) avprpv ';
273 gc_where_clause := ' AND ai.invoice_id = aip.invoice_id(+) '
274 ||' AND aip.check_id = ac.check_id(+) '
275 ||' AND ail.invoice_id = avprpv.prepay_id(+) '
276 ||' AND ail.line_number = avprpv.prepay_line_number(+) '
277 ||' AND ail.org_id = avprpv.org_id(+) ';
278 */
279 gc_where_clause := ' AND 1 = 1 ';
280 IF PAID_ONLY_PARAM = 'Y' THEN
281 gc_where_clause := gc_where_clause ||' AND ai.payment_status_flag = ''Y''';
282 END IF;
283
284 /*--*************************************************
285 -- Used for Report Specific conditions
286 -- DUMMY_PARAM = 2 => Advances in Selected Currency Report
287 -- where it doesn't require TAX data
288 -- DUMMY_PARAM = 3 => AP Prepayment Balance Report
289 -- where it require only ACTUAL Balance type data
290 -- Also must not display the Cancelled Prepayments/Invoices
291 --*************************************************/
292 IF DUMMY_PARAM = 2 THEN
293 gc_additional_where := ' AND ail.line_type_lookup_code NOT IN (''TAX'') ';
294 ELSIF DUMMY_PARAM = 3 THEN
295 gc_additional_where := ' AND ail.line_type_lookup_code NOT IN (''TAX'') '
296 ||' AND ai.cancelled_date IS NULL ';
297 ELSE
298 gc_additional_where := ' AND 1 = 1 ';
299 END IF;
300
301 /*--*************************************************
302 -- Identify whether the report must run for Single
303 -- Operating unit or multiple based on P_ORG_ID
304 --*************************************************/
305 IF ORG_ID_PARAM IS NOT NULL THEN
306 gc_org_where := ' AND ai.org_id = :ORG_ID_PARAM ';
307 ELSE
308 gc_org_where := ' AND 1 = 1';
309 END IF;
310 fnd_file.put_line(fnd_file.log,'gc_org_where::'||gc_org_where);
311 RETURN TRUE;
312 END beforereport;
313
314 FUNCTION PREPAY_AMT_APPLIED(p_invoice_id IN NUMBER,p_inv_date IN DATE)
315 RETURN NUMBER
316 IS
317 ln_inv_amt_ent NUMBER;
318 BEGIN
319 SELECT NVL(sum(NVL(xdl.unrounded_ENTERED_dR,0)-NVL(xdl.unrounded_entered_cr,0)),0)
320 INTO ln_inv_amt_ent
321 FROM ap_invoices ai
322 ,ap_invoice_distributions aid
323 ,ap_invoice_distributions aidinv
324 ,ap_invoices aiinv
325 ,xla_events xe
326 ,xla_ae_lines xal
327 ,xla_distribution_links xdl
328 WHERE ai.invoice_id=p_invoice_id
329 AND ai.invoice_date <= p_inv_date
330 AND aid.invoice_id=ai.invoice_id
331 AND aid.line_type_lookup_code='ITEM'
332 AND aidinv.prepay_distribution_id=aid.invoice_distribution_id
333 AND aiinv.invoice_id=aidinv.invoice_id
334 AND xe.event_type_code IN ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED')
335 AND xe.event_id=xdl.event_id
336 AND xdl.event_id=aidinv.accounting_event_id
337 AND xal.ae_header_id=xdl.ae_header_id
338 AND xal.ae_line_num=xdl.ae_line_num
339 AND xal.accounting_class_code='LIABILITY'
340 AND xe.application_id=200
341 AND xdl.application_id=200
342 AND xal.application_id=200;
343
344 RETURN (ln_inv_amt_ent);
345 END PREPAY_AMT_APPLIED;
346
347 END AP_BAL_PKG;
[Home] [Help]