1 PACKAGE BODY AP_TURNOVER_RPT_PKG AS
2 -- $Header: APTURNOVERRPTPB.pls 120.1 2008/05/27 14:45:09 rapulla noship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Corporation Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- APTURNOVERRPTPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of AP_TURNOVER_RPT_PKG.
13 -- This package is used to generate AP Turnover 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 21-FEB-2007
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 --
33 --****************************************************************************************
34 FUNCTION beforeReport
35 RETURN BOOLEAN
36 IS
37 BEGIN
38 IF SUPPLIER_FROM_PARAM IS NOT NULL AND SUPPLIER_TO_PARAM IS NOT NULL THEN
39 IF SUPPLIER_FROM_PARAM <= SUPPLIER_TO_PARAM THEN
40 gc_supplier_where:='AND UPPER(asup.vendor_name) BETWEEN UPPER(:SUPPLIER_FROM_PARAM) AND UPPER(:SUPPLIER_TO_PARAM)';
41 ELSE
42 gc_supplier_where:='AND UPPER(asup.vendor_name) BETWEEN UPPER(:SUPPLIER_TO_PARAM) AND UPPER(:SUPPLIER_FROM_PARAM)';
43 END IF;
44 END IF;
45
46 IF LEDGER_ID_PARAM IS NOT NULL THEN
47 SELECT name
48 INTO gc_ledger_name
49 FROM gl_ledgers
50 WHERE ledger_id = LEDGER_ID_PARAM;
51 ELSE
52 gc_ledger_name :=' ';
53 END IF;
54
55 IF OU_FROM_PARAM <= OU_TO_PARAM THEN
56 gc_operunit_where:='AND UPPER(hou.name) BETWEEN UPPER(:OU_FROM_PARAM) AND UPPER(:OU_TO_PARAM)';
57 ELSE
58 gc_operunit_where:='AND UPPER(hou.name) BETWEEN UPPER(:OU_TO_PARAM) AND UPPER(:OU_FROM_PARAM)';
59 END IF;
60
61 IF REPORT_TYPE_PARAM = 1 THEN
62 gc_orderby:='ORDER BY 1,2,4,9,10';
63 ELSE
64 gc_orderby:='ORDER BY 1,9,10,2,4';
65 END IF;
66
67 IF CURRENCY_PARAM IS NOT NULL THEN
68 gc_currency_where:='AND ai.invoice_currency_code= :CURRENCY_PARAM';
69 END IF;
70
71 IF PRPMT_PROCESSING_PARAM=2 THEN
72 gc_prepay_invoice_select := ' ai.gl_date ';
73
74 gc_prepay_invoice_from := ' gl_code_combinations gcc';
75
76 gc_prepay_where := ' gcc.code_combination_id=xal.code_combination_id';
77 gc_prepay_invoice_where := gc_prepay_where ;--||' AND gcc.code_combination_id = :ACCOUNT_ID';
78
79 ELSIF PRPMT_PROCESSING_PARAM=1 THEN
80 gc_prepay_invoice_select := ' (CASE WHEN asps.recon_accounting_flag=''Y''';
81 gc_prepay_invoice_select := gc_prepay_invoice_select ||' THEN ac.cleared_date';
82 gc_prepay_invoice_select := gc_prepay_invoice_select ||' WHEN asps.recon_accounting_flag=''N''';
83 gc_prepay_invoice_select := gc_prepay_invoice_select ||' AND asps.when_to_account_pmt=''ALWAYS''';
84 gc_prepay_invoice_select := gc_prepay_invoice_select ||' THEN ac.check_date END) ';
85
86 gc_prepay_invoice_from := gc_prepay_invoice_from ||' ap_invoice_payments aip';
87 gc_prepay_invoice_from := gc_prepay_invoice_from ||' ,ap_checks ac';
88 gc_prepay_invoice_from := gc_prepay_invoice_from ||' ,ap_system_parameters asps';
89 gc_prepay_invoice_from := gc_prepay_invoice_from ||' ,gl_code_combinations gcc';
90 --************************
91 --WHERE Clause
92 --************************
93 gc_prepay_where := ' aip.invoice_id=ai.invoice_id';
94 gc_prepay_where := gc_prepay_where ||' AND ac.check_id=aip.check_id';
95 gc_prepay_where := gc_prepay_where ||' AND asps.org_id=ai.org_id';
96 gc_prepay_where := gc_prepay_where ||' AND gcc.code_combination_id=xal.code_combination_id';
97 gc_prepay_where := gc_prepay_where ||' AND (CASE WHEN asps.recon_accounting_flag=''Y''';
98 gc_prepay_where := gc_prepay_where ||' THEN ac.cleared_date';
99 gc_prepay_where := gc_prepay_where ||' WHEN asps.recon_accounting_flag=''N''';
100 gc_prepay_where := gc_prepay_where ||' THEN ac.check_date END) IS NOT NULL';
101 gc_prepay_invoice_where := gc_prepay_where ;--||' AND gcc.code_combination_id = :ACCOUNT_ID';
102
103 END IF;
104 RETURN TRUE;
105 END beforeReport;
106
107 FUNCTION get_summary_mask_account(Acct_num IN VARCHAR2) RETURN VARCHAR2
108 IS
109 lc_stat VARCHAR2(100);
110 lc_var VARCHAR2(1) :='*';
111 ln_count NUMBER :=0;
112 lv_acc_summ_mask VARCHAR2(100);
113 ln_length NUMBER;
114 CURSOR cu_Acctnum IS
115 SELECT TRIM ( SUBSTR ( acc_num
116 , INSTR (acc_num, '-', 1, LEVEL) + 1
117 , INSTR (acc_num, '-', 1, LEVEL + 1)
118 -INSTR ( acc_num, '-', 1, LEVEL)- 1
119 )) AS account_segments
120 , TRIM ( SUBSTR ( sum_mask
121 , INSTR ( sum_mask, '.', 1, LEVEL) + 1
122 , INSTR ( sum_mask, '.', 1, LEVEL + 1)
123 - INSTR ( sum_mask, '.', 1, LEVEL)- 1
124 )) AS summary_mask
125 FROM ( SELECT '-' || Acct_num || '-' acc_num
126 , '.' || SUMMARY_MASK_PARAM || '.' sum_mask
127 FROM DUAL )
128 CONNECT BY LEVEL <= LENGTH ( acc_num ) - LENGTH ( REPLACE ( acc_num, '-', '')) - 1;
129 BEGIN
130 IF REPORT_LEVEL_PARAM =4 OR REPORT_LEVEL_PARAM =5 OR REPORT_LEVEL_PARAM =6 THEN
131 IF SUMMARY_MASK_PARAM IS NOT NULL THEN
132 FOR lcu_Acctnum IN cu_Acctnum
133 LOOP
134 IF lcu_Acctnum.summary_mask = 'T' THEN
135 SELECT LENGTH(lcu_Acctnum.account_segments) into ln_length from dual;
136 lc_stat := NULL;
137 FOR i IN 1..ln_length LOOP
138 lc_stat := lc_stat||lc_var;
139 END LOOP;
140 ELSIF lcu_Acctnum.summary_mask = 'D' THEN
141 lc_stat := lcu_Acctnum.account_segments;
142 END IF;
143 ln_count := ln_count+1;
144 IF ln_count=1 THEN
145 lv_acc_summ_mask := lc_stat;
146 ELSIF ln_count > 1 THEN
147 lv_acc_summ_mask := lv_acc_summ_mask||'-'||lc_stat;
148 END IF;
149 END LOOP;
150 ELSIF SUMMARY_MASK_PARAM IS NULL THEN
151 lv_acc_summ_mask := Acct_num;
152 END IF;
153 RETURN(lv_acc_summ_mask);
154
155 ELSE
156 RETURN(Acct_num);
157 END IF;
158 END get_summary_mask_account;
159
160 FUNCTION opening_balance(p_in_sup_id IN NUMBER
161 ,p_in_sup_site_id IN NUMBER
162 ,p_in_curr IN VARCHAR2
163 ,p_in_code_comb_id IN NUMBER
164 ,p_in_orgs_id IN NUMBER) RETURN NUMBER
165 IS
166 lc_payment_query VARCHAR2(3000);
167 BEGIN
168 BEGIN
169 SELECT SUM(NVL(xdl.unrounded_accounted_dr,0)-NVL(xdl.unrounded_accounted_cr,0))
170 INTO gn_open_payment_balance
171 FROM ap_invoices aia
172 ,ap_invoice_payments aip
173 ,ap_checks ac
174 ,ap_payment_history aph
175 ,ap_lookup_codes alc
176 ,ap_system_parameters asps
177 ,xla_events xe
178 ,xla_distribution_links xdl
179 ,xla_ae_lines xal
180 ,xla_ae_headers xah
181 ,gl_code_combinations gcc
182 WHERE aia.vendor_id = p_in_sup_id
183 AND aia.org_id = p_in_orgs_id
184 AND aia.vendor_site_id = p_in_sup_site_id
185 AND aia.invoice_currency_code = p_in_curr
186 AND aia.invoice_id = aip.invoice_id
187 AND aia.gl_date < TO_DATE(PERIOD_START_DATE_PARAM)
188 AND aip.check_id = ac.check_id
189 AND ac.check_id = aph.check_id
190 AND alc.lookup_type = 'PAYMENT TYPE'
191 AND asps.org_id = aia.org_id
192 AND alc.lookup_code = ac.payment_type_flag
193 AND aph.accounting_event_id = xe.event_id
194 AND xe.application_id = 200
195 AND xe.event_id = xdl.event_id
196 AND xdl.application_id = 200
197 AND aph.accounting_event_id = xdl.event_id
198 AND xdl.source_distribution_type = 'AP_PMT_DIST'
199 AND xdl.applied_to_source_id_num_1 = aia.invoice_id
200 AND xdl.ae_header_id = xal.ae_header_id
201 AND xal.application_id = 200
202 AND xdl.ae_line_num = xal.ae_line_num
203 AND xdl.accounting_line_code NOT IN ('AP_LIAB_AWT_PMT')
204 AND xdl.applied_to_entity_code = 'AP_INVOICES'
205 AND xal.ae_header_id = xah.ae_header_id
206 AND xah.application_id = 200
207 AND xah.ledger_id = LEDGER_ID_PARAM
208 AND xdl.rounding_class_code = 'LIABILITY'
209 AND gcc.code_combination_id = xal.code_combination_id
210 AND gcc.code_combination_id = p_in_code_comb_id
211 AND ((PRPMT_PROCESSING_PARAM = 2)
212 OR (PRPMT_PROCESSING_PARAM = 1
213 AND (aia.invoice_type_lookup_code IN ('CREDIT','DEBIT','STANDARD','AWT','EXPENSE REPORT','MIXED')
214 OR (aia.invoice_type_lookup_code = 'PREPAYMENT'
215 AND (CASE WHEN asps.recon_accounting_flag='Y'
216 THEN ac.cleared_date
217 WHEN asps.recon_accounting_flag='N'
218 THEN ac.check_date END) IS NOT NULL))));
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 gn_open_payment_balance:=0;
222 END;
223 --for standard invoice
224 BEGIN
225 SELECT SUM(NVL(xal.accounted_cr,0)-NVL(xal.accounted_dr,0))
226 INTO gn_open_invoice_balance
227 FROM ap_invoices ai
228 ,xla_transaction_entities xte
229 ,xla_events xe
230 ,xla_ae_headers xah
231 ,xla_ae_lines xal
232 ,gl_code_combinations gcc
233 WHERE ai.set_of_books_id = LEDGER_ID_PARAM
234 AND ai.vendor_id = p_in_sup_id
235 AND ai.org_id = p_in_orgs_id
236 AND ai.vendor_site_id = p_in_sup_site_id
237 AND ai.invoice_currency_code = p_in_curr
238 AND ai.gl_date < PERIOD_START_DATE_PARAM
239 AND ai.invoice_type_lookup_code IN ('CREDIT','DEBIT','STANDARD','AWT','EXPENSE REPORT','MIXED')
240 AND xte.source_id_int_1 = ai.invoice_id
241 AND xte.application_id = 200
242 AND xe.application_id = 200
243 AND xah.application_id = 200
244 AND xal.application_id = 200
245 AND xte.entity_code = 'AP_INVOICES'
246 AND xe.entity_id = xte.entity_id
247 AND xah.entity_id = xte.entity_id
248 AND xe.event_type_code NOT IN ('PREPAYMENT UNAPPLIED','PREPAYMENT APPLIED')
249 AND xah.event_id = xe.event_id
250 AND xal.ae_header_id = xah.ae_header_id
251 AND ai.invoice_id = NVL(xal.upg_tax_reference_id1,ai.invoice_id)
252 AND xal.accounting_class_code = 'LIABILITY'
253 AND xal.ledger_id = ai.set_of_books_id
254 AND gcc.code_combination_id = xal.code_combination_id
255 AND gcc.code_combination_id = p_in_code_comb_id;
256 EXCEPTION
257 WHEN NO_DATA_FOUND THEN
258 gn_open_invoice_balance:=0;
259 END;
260 g_open_balance :=NVL(gn_open_payment_balance,0)-NVL(gn_open_invoice_balance,0);
261 RETURN(NVL(g_open_balance,0));
262 END opening_balance;
263
264 END AP_TURNOVER_RPT_PKG;
[Home] [Help]