1 PACKAGE BODY AP_BAL_PKG
2 -- $Header: APPREBCPB.pls 120.17 2011/08/24 12:04:52 mkmeda ship $
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 -- | History
46 -- | 23-Sep-2009 wjharris Bug B 8973431/8935239 - FUNCTION description() :
47 -- | remove table gl_code_combinations from join
48 --
49 -- ****************************************************************************************
50 AS
51
52 --=====================================================================
53 FUNCTION get_no_of_holds(p_invoice_id IN NUMBER)
54 RETURN NUMBER
55 IS
56 ln_count NUMBER;
57 BEGIN
58 BEGIN
59 SELECT COUNT(1)
60 INTO ln_count
61 FROM AP_HOLDS ah
62 WHERE ah.invoice_id = p_invoice_id
63 AND NVL(ah.status_flag,'X') <> 'R'; --Added the Condition Based on the Bug 6473102
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 ln_count := 0;
67 END;
68 RETURN (ln_count);
69 END get_no_of_holds;
70
71 --=====================================================================
72 --=====================================================================
73 FUNCTION get_settlement_date(p_invoice_id IN NUMBER
74 ,p_line_number IN NUMBER)
75 RETURN DATE
76 IS
77 ld_settlement_date DATE;
78 BEGIN
79 BEGIN
80 SELECT MAX(AVPFPV.accounting_date)
81 INTO ld_settlement_date
82 FROM ap_view_prepays_fr_prepay_v AVPFPV
83 WHERE AVPFPV.prepay_id = p_invoice_id
84 AND AVPFPV.prepay_line_number = p_line_number;
85 EXCEPTION
86 WHEN no_data_found THEN
87 ld_settlement_date := NULL;
88 END;
89 RETURN ld_settlement_date;
90 END get_settlement_date;
91
92 --=====================================================================
93 --=====================================================================
94 FUNCTION description(p_seg_value IN VARCHAR2,p_seg_type IN VARCHAR2)
95 RETURN VARCHAR2
96 IS
97 v_value_desc VARCHAR2(240); -- Bug 8814452 , Increasing the size to
98 -- sync. with fnd_flex_values_vl.description
99 BEGIN
100 BEGIN
101 IF p_seg_type LIKE '%SEGMENT%' THEN
102 SELECT DISTINCT ffv.description
103 INTO v_value_desc
104 FROM
105 -- gl_code_combinations gcc, .. B 8973431/8935239 .... remove table from join
106 fnd_id_flex_structures ffs
107 ,fnd_id_flex_segments fseg
108 ,fnd_flex_values_vl ffv
109 --WHERE gcc.chart_of_accounts_id = ffs.id_flex_num .. B 8973431/8935239
110 --AND ffs.id_flex_num = fseg.id_flex_num .. B 8973431/8935239
111 WHERE ffs.id_flex_num = fseg.id_flex_num -- B 8973431/8935239
112 AND ffs.id_flex_code = fseg.id_flex_code
113 AND fseg.application_column_name = p_seg_type
114 AND fseg.flex_value_set_id = ffv.flex_value_set_id
115 AND ffs.id_flex_code = 'GL#'
116 --AND gcc.chart_of_accounts_id = (SELECT chart_of_accounts_id .. B 8973431/8935239
117 AND ffs.id_flex_num = (SELECT chart_of_accounts_id -- B 8973431/8935239
118 FROM gl_access_sets
119 WHERE access_set_id = fnd_profile.value('GL_ACCESS_SET_ID'))
120 AND ffv.flex_value = p_seg_value;
121 ELSE
122 SELECT ffv.description
123 INTO v_value_desc
124 FROM fnd_descr_flex_col_usage_vl fdfcu
125 ,fnd_flex_values_vl ffv
126 WHERE fdfcu.flex_value_set_id = ffv.flex_value_set_id
127 AND fdfcu.application_id = 200
128 AND fdfcu.descriptive_flexfield_name = 'AP_INVOICES'
129 AND fdfcu.descriptive_flex_context_code ='Global Data Elements'
130 AND fdfcu.application_column_name = p_seg_type
131 AND ffv.flex_value = p_seg_value;
132 END IF;
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 v_value_desc := NULL;
136 END;
137 RETURN v_value_desc;
138 END description;
139
140 --=====================================================================
141 --=====================================================================
142 FUNCTION beforereport RETURN BOOLEAN
143 IS
144 ld_param_from_date DATE;
145 ld_param_to_date DATE;
146 ld_period_from_date DATE;
147 ld_period_to_date DATE;
148 BEGIN
149 BEGIN
150 SELECT gled.name
151 INTO gc_ledger_name
152 FROM gl_ledgers gled
153 ,gl_access_set_norm_assign gasna
154 WHERE gasna.access_set_id = FND_PROFILE.VALUE('GL_ACCESS_SET_ID')
155 AND gled.ledger_id = gasna.ledger_id
156 AND gled.ledger_category_code = 'PRIMARY';
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 gc_ledger_name := NULL;
160 END;
161
162 /*--*************************************************
163 -- Used for DATE Conditions
164 --*************************************************/
165 IF FROM_DATE_PARAM IS NULL THEN
166 ld_param_from_date := SYSDATE;
167 ld_param_to_date := SYSDATE;
168 ELSIF FROM_DATE_PARAM IS NOT NULL AND TO_DATE_PARAM IS NULL THEN
169 ld_param_from_date := TO_DATE(FROM_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
170 ld_param_to_date := TO_DATE(FROM_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
171 ELSE
172 ld_param_from_date := TO_DATE(FROM_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
173 ld_param_to_date := TO_DATE(TO_DATE_PARAM,'YYYY/MM/DD HH24:MI:SS');
174 END IF;
175
176 IF PERIOD_FROM_PARAM IS NULL AND PERIOD_TO_PARAM IS NULL THEN
177 ld_period_from_date := SYSDATE;
178 ld_period_to_date := SYSDATE;
179 ELSE
180 SELECT gps.start_date
181 INTO ld_period_from_date
182 FROM gl_period_statuses gps
183 WHERE gps.period_name = PERIOD_FROM_PARAM
184 AND gps.application_id = 200
185 AND gps.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
186
187 SELECT gps.end_date
188 INTO ld_period_to_date
189 FROM gl_period_statuses gps
190 WHERE gps.period_name = PERIOD_TO_PARAM
191 AND gps.application_id = 200
192 AND gps.set_of_books_id = FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
193 END IF;
194
195 IF ld_param_from_date <= ld_period_from_date THEN
196 gd_from_date := ld_param_from_date;
197 ELSE
198 gd_from_date := ld_period_from_date;
199 END IF;
200
201 --Made changes as per the Bug 6473102
202 IF PERIOD_TO_PARAM IS NOT NULL THEN
203 gd_to_date := ld_period_to_date;
204 ELSIF ld_param_to_date >= ld_period_to_date AND TO_DATE_PARAM IS NOT NULL THEN
205 gd_to_date := ld_param_to_date;
206 ELSIF ld_param_to_date < ld_period_to_date AND ld_period_to_date <> SYSDATE THEN
207 gd_to_date := ld_period_to_date;
208 ELSE
209 gd_to_date := ld_param_to_date;
210 END IF;
211
212 /*--*************************************************
213 -- Used for Currency Code Conditions
214 --*************************************************/
215 IF CURR_CODE_PARAM IS NOT NULL AND curr_code_param <> 'ANY' THEN
216 gc_currency := ' AND ai.invoice_currency_code = :CURR_CODE_PARAM ';
217 ELSE
218 gc_currency := ' AND 1 = 1 ';
219 END IF;
220 fnd_file.put_line(fnd_file.log,'gc_currency::'||gc_currency);
221
222 /*--*************************************************
223 -- Used for Posted Only (Y/N) Conditions
224 --*************************************************/
225
226 -- gc_from_clause := ' ,ap_checks_all ac ';
227 IF POSTED_ONLY_PARAM = 'Y' THEN
228 /*
229 gc_from_clause := gc_from_clause ||' ,gl_import_references gir '
230 ||' ,gl_je_headers gjh ';
231 gc_status := ' AND gir.gl_sl_link_id = xal.gl_sl_link_id '
232 ||' AND gir.gl_sl_link_table = xal.gl_sl_link_table '
233 ||' AND gir.je_header_id = gjh.je_header_id '
234 ||' AND xal.ledger_id = gjh.ledger_id '
235 ||' AND gjh.status = ''P''';
236 --gc_status := ' AND xah.gl_transfer_status_code = ''Y'' ';
237 gc_select_clause := ' gjh.status ';
238 */
239 --gc_status := ' AND gjh.status = ''P''';
240 gc_status := ' AND xah.gl_transfer_status_code = ''Y'' ';
241 ELSE
242 gc_status := ' AND 1 = 1 ';
243 -- gc_select_clause := ' NULL ';
244 END IF;
245 fnd_file.put_line(fnd_file.log,'gc_status::'||gc_status);
246 /*--*************************************************
247 --Dynamic WHERE based on supplier_from_param and supplier_to_param parameter
248 --*************************************************/
249 IF supplier_from_param IS NOT NULL AND supplier_to_param IS NOT NULL THEN
250 IF supplier_from_param < supplier_to_param THEN
251 gc_supplier := ' AND POV.vendor_name BETWEEN :supplier_from_param AND :supplier_to_param ';
252 ELSE
253 gc_supplier := ' AND POV.vendor_name BETWEEN :supplier_to_param AND :supplier_from_param ';
254 END IF;
255 ELSIF supplier_from_param IS NOT NULL AND supplier_to_param IS NULL THEN
256 gc_supplier := ' AND POV.vendor_name = :supplier_from_param';
257 ELSE
258 gc_supplier := ' AND 1 = 1';
259 END IF;
260 /*--*************************************************
261 -- These are mainly used to confirm which part of the query should result the
262 -- output and which sould not. This is decided based on the P_PAID parameter.
263 -- If the Parameter returns 'Y' then only Paid records will display hence only
264 -- first Query will result the output. If the P_PAID returns 'N' then both paid
265 -- and Unpaid records needs to be displayed hence both the records results the
266 -- data.
267 --*************************************************/
268 -- Commented the Code based on the Bug# 6497821
269 /*
270 gc_pre_amt_appl := ' NVL(avprpv.prepay_amount_applied,0) pre_amt_appl_fr_curr '
271 ||' ,NVL(avprpv.prepay_amount_applied,0) * NVL(ai.exchange_rate,1) pre_amt_appl_fn_curr';
272 gc_from_clause := ' ap_invoice_payments_all aip '||
273 ' ,ap_checks_all ac '||
274 ' ,(SELECT avprpv.org_id org_id
275 ,avprpv.prepay_id prepay_id
276 ,avprpv.prepay_line_number prepay_line_number
277 ,avprpv.prepay_amount_applied prepay_amount_applied
278 FROM ap_view_prepays_fr_prepay_v avprpv
279 WHERE NVL(avprpv.accounting_date,:gd_from_date) <= :gd_from_date) avprpv ';
280 gc_where_clause := ' AND ai.invoice_id = aip.invoice_id(+) '
281 ||' AND aip.check_id = ac.check_id(+) '
282 ||' AND ail.invoice_id = avprpv.prepay_id(+) '
283 ||' AND ail.line_number = avprpv.prepay_line_number(+) '
284 ||' AND ail.org_id = avprpv.org_id(+) ';
285 */
286 gc_where_clause := ' AND 1 = 1 ';
287 IF PAID_ONLY_PARAM = 'Y' THEN
288 gc_where_clause := gc_where_clause ||' AND ai.payment_status_flag = ''Y''';
289 END IF;
290
291 /*--*************************************************
292 -- Used for Report Specific conditions
293 -- DUMMY_PARAM = 2 => Advances in Selected Currency Report
294 -- where it doesn't require TAX data
295 -- DUMMY_PARAM = 3 => AP Prepayment Balance Report
296 -- where it require only ACTUAL Balance type data
297 -- Also must not display the Cancelled Prepayments/Invoices
298 --*************************************************/
299 IF DUMMY_PARAM = 2 THEN
300 gc_additional_where := ' AND ail.line_type_lookup_code NOT IN (''TAX'') ';
301 ELSIF DUMMY_PARAM = 3 THEN
302 gc_additional_where := ' AND ail.line_type_lookup_code NOT IN (''TAX'') '
303 ||' AND ai.cancelled_date IS NULL ';
304 ELSE
305 gc_additional_where := ' AND 1 = 1 ';
306 END IF;
307
308 /*--*************************************************
309 -- Identify whether the report must run for Single
310 -- Operating unit or multiple based on P_ORG_ID
311 --*************************************************/
312 IF ORG_ID_PARAM IS NOT NULL THEN
313 gc_org_where := ' AND ai.org_id = :ORG_ID_PARAM ';
314 ELSE
315 gc_org_where := ' AND 1 = 1';
316 END IF;
317 fnd_file.put_line(fnd_file.log,'gc_org_where::'||gc_org_where);
318 RETURN TRUE;
319 END beforereport;
320
321 FUNCTION PREPAY_AMT_APPLIED(p_invoice_id IN NUMBER,p_inv_date IN DATE)
322 RETURN NUMBER
323 IS
324 ln_inv_amt_ent NUMBER;
325 BEGIN
326
327 /* bug#12697320 modified the joins to access XAL first and then XDL */
328 SELECT NVL(sum(NVL(xdl.unrounded_ENTERED_dR,0)-NVL(xdl.unrounded_entered_cr,0)),0)
329 INTO ln_inv_amt_ent
330 FROM ap_invoices ai
331 ,ap_invoice_distributions aid
332 ,ap_invoice_distributions aidinv
333 ,ap_invoices aiinv
334 ,xla_ae_headers xah --Perf 7511696 events replaced with headers
335 ,xla_ae_lines xal
336 ,xla_distribution_links xdl
337 WHERE ai.invoice_id=p_invoice_id
338 AND ai.invoice_date <= p_inv_date
339 AND aid.invoice_id=ai.invoice_id
340 AND aid.line_type_lookup_code='ITEM'
341 AND aidinv.prepay_distribution_id=aid.invoice_distribution_id
342 AND aiinv.invoice_id=aidinv.invoice_id
343 AND XAH.EVENT_TYPE_CODE IN ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED')
344 AND XAH.EVENT_ID = AIDINV.ACCOUNTING_EVENT_ID
345 AND XAL.AE_HEADER_ID =XAH.AE_HEADER_ID
346 AND XAL.ACCOUNTING_CLASS_CODE ='LIABILITY'
347 AND XAL.AE_HEADER_ID =XDL.AE_HEADER_ID
348 AND XAL.AE_LINE_NUM =XDL.AE_LINE_NUM
349 AND XAH.APPLICATION_ID =200
350 AND XDL.APPLICATION_ID =200
351 AND XAL.APPLICATION_ID =200;
352
353 RETURN (ln_inv_amt_ent);
354 END PREPAY_AMT_APPLIED;
355
356 END AP_BAL_PKG;
[Home] [Help]