1 PACKAGE BODY AR_DEP_BAL_RPT_PKG
2 -- $Header: AR_DEPBALRPT_PB.pls 120.0.12010000.2 2008/10/24 12:16:57 tthangav ship $
3 --*************************************************************************
4 -- Copyright (c) 2000 Oracle Product Development
5 -- All rights reserved
6 --*************************************************************************
7 --
8 -- HEADER
9 -- Source control header
10 --
11 -- PROGRAM NAME
12 -- AR_DEP_BAL_RPT_PKG
13 --
14 -- DESCRIPTION
15 -- This script creates the package Body of AR_DEP_BAL_RPT_PKG
16 -- This package is used to report on Deposit Balance Detail .
17 --
18 -- USAGE
19 -- To install sqlplus <apps_user>/<apps_pwd> @AR_DEPBALRPT_PB.pls
20 -- To execute sqlplus <apps_user>/<apps_pwd> AR_DEP_BAL_RPT_PKG
21 --
22 -- PROGRAM LIST DESCRIPTION
23 --
24 -- DEPENDENCIES
25 -- None
26 --
27 -- CALLED BY
28 -- Deposit Balance Report - Japan.
29 --
30 -- LAST UPDATE DATE 14-Aug-2007
31 -- Date the program has been modified for the last time
32 --
33 -- HISTORY
34 -- =======
35 --
36 -- VERSION DATE AUTHOR(S) DESCRIPTION
37 -- ------- ----------- --------------- ------------------------------------
38 -- Draft1A 27-Jul-2007 Rakesh Pulla Initial Creation
39 -- Draft1B 13-Aug-2007 Rakesh Pulla Incorporated the SO Review comments as per Ref # 28859
40 -- Draft1C 17-Oct-2008 Rakesh Pulla Made changes to the function beforeReport tofix the Bug # 7439767
41 --************************************************************************
42 AS
43 FUNCTION beforeReport RETURN BOOLEAN
44 IS
45 BEGIN -- Beginning of the Function beforeReport .
46 /* Query to get the ledger Id */
47 BEGIN
48 SELECT GLL.ledger_id
49 INTO gn_ledger_id
50 FROM gl_ledgers GLL
51 ,gl_access_set_norm_assign GASNA
52 WHERE GASNA.access_set_id = FND_PROFILE.VALUE('GL_ACCESS_SET_ID')
53 AND GLL.ledger_id = GASNA.ledger_id
54 AND GLL.ledger_category_code = 'PRIMARY';
55 END;
56 /*Query to get the Start date for a given Period From parameter */
57 BEGIN
58 SELECT GLP.start_date
59 INTO gc_per_start_date
60 FROM gl_periods GLP
61 ,gl_ledgers GLL
62 WHERE GLL.ledger_id = gn_ledger_id
63 AND GLP.period_set_name = GLL.period_set_name
64 AND GLP.period_name = P_PERIOD_FROM;
65 END;
66 /*Query to get the End date for a given Period To parameter */
67 BEGIN
68 SELECT GLP.end_date
69 INTO gc_per_end_date
70 FROM gl_periods GLP
71 ,gl_ledgers GLL
72 WHERE GLL.ledger_id = gn_ledger_id
73 AND GLP.period_set_name = GLL.period_set_name
74 AND GLP.period_name = P_PERIOD_TO;
75 END;
76
77 IF p_customer_name IS NOT NULL THEN
78 gc_customer := 'AND HCA.cust_account_id = :P_CUSTOMER_NAME ';
79 ELSE
80 gc_customer := 'AND 1=1';
81 END IF;
82
83 IF p_currency IS NOT NULL THEN
84 gc_currency := 'AND RCT.invoice_currency_code = :P_CURRENCY ';
85 ELSE
86 gc_currency := 'AND 1=1';
87 END IF;
88 RETURN TRUE;
89 END beforeReport; --End of the Function beforeReport
90
91 FUNCTION description(p_value IN VARCHAR2, p_segment IN VARCHAR2)
92 RETURN VARCHAR2
93 IS
94 lc_value_desc VARCHAR2(100);
95 BEGIN
96 /* Query to get the Descriptions of the Segment values. */
97 BEGIN
98 IF p_segment LIKE '%SEGMENT%' THEN
99 SELECT DISTINCT FFV.description
100 INTO lc_value_desc
101 FROM gl_code_combinations GCC
102 ,fnd_id_flex_structures FFS
103 ,fnd_id_flex_segments FSEG
104 ,fnd_flex_values_vl FFV
105 WHERE GCC.chart_of_accounts_id = FFS.id_flex_num
106 AND FFS.id_flex_num = FSEG.id_flex_num
107 AND FFS.id_flex_code = FSEG.id_flex_code
108 AND FSEG.application_column_name = p_segment
109 AND FSEG.flex_value_set_id = FFV.flex_value_set_id
110 AND FFS.id_flex_code = 'GL#'
111 AND GCC.chart_of_accounts_id = (SELECT GAS.chart_of_accounts_id
112 FROM gl_access_sets GAS
113 WHERE GAS.access_set_id = FND_PROFILE.value('GL_ACCESS_SET_ID'))
114 AND FFV.flex_value = p_value;
115 ELSE
116 /* Query to get the Descriptions of the Attribute values. */
117 SELECT FFV.description
118 INTO lc_value_desc
119 FROM fnd_descr_flex_col_usage_vl FDFCU
120 ,fnd_flex_values_vl FFV
121 WHERE FDFCU.flex_value_set_id = FFV.flex_value_set_id
122 AND FDFCU.application_id = 222
123 AND FDFCU.descriptive_flexfield_name = 'RA_CUSTOMER_TRX'
124 AND FDFCU.descriptive_flex_context_code ='Global Data Elements'
125 AND FDFCU.application_column_name = p_segment
126 AND FFV.flex_value = p_value;
127 END IF;
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 lc_value_desc := NULL;
131 END;
132 RETURN lc_value_desc;
133 END description;
134
135
136 FUNCTION commitment_balance( p_customer_trx_id IN NUMBER) RETURN NUMBER
137 IS
138 ln_commitment_bal NUMBER;
139 ln_carrying_bal NUMBER;
140 ln_invoice_bal NUMBER;
141 ln_deposit_cancel NUMBER;
142 BEGIN
143 BEGIN /*Query to find the Balances for the given Customer Trx Id */
144 SELECT NVL(RCTL.extended_amount,0)
145 INTO ln_commitment_bal
146 FROM hz_cust_accounts HCA
147 ,ra_customer_trx_lines RCTL
148 ,ra_customer_trx RCT
149 ,ra_cust_trx_types RCTT
150 WHERE RCT.customer_trx_id = p_customer_trx_id
151 AND RCT.cust_trx_type_id = RCTT.cust_trx_type_id
152 AND RCT.org_id = RCTT.org_id
153 AND RCT.customer_trx_id = RCTL.customer_trx_id
154 AND RCT.bill_to_customer_id = HCA.cust_account_id
155 AND RCTT.type = 'DEP'
156 ORDER BY RCT.trx_number;
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 RETURN( NULL );
160 WHEN TOO_MANY_ROWS THEN
161 RETURN(NULL);
162 END;
163
164 BEGIN
165 SELECT NVL( ln_commitment_bal, 0) - (NVL(SUM( ARA.AMOUNT),0) * -1)
166 INTO ln_invoice_bal
167 FROM ra_customer_trx RCT
168 ,ra_cust_trx_line_gl_dist_all RCTD
169 ,ra_cust_trx_types_all RCTT
170 ,hz_cust_accounts HCA
171 ,hz_parties HZP
172 ,gl_code_combinations GCC
173 ,ar_adjustments_all ARA
174 ,ar_distributions_all ARD
175 WHERE RCT.initial_customer_trx_id = p_customer_trx_id
176 AND RCTD.customer_trx_id = RCT.customer_trx_id
177 AND RCT.customer_trx_id = DECODE(RCTT.type, 'INV', ARA.customer_trx_id,
178 'CM', ARA.subsequent_trx_id)
179 AND RCT.cust_trx_type_id = RCTT.cust_trx_type_id
180 AND RCT.org_id = RCTT.org_id
181 AND RCT.bill_to_customer_id = HCA.cust_account_id
182 AND HCA.party_id = HZP.party_id
183 AND ARD.code_combination_id = GCC.code_combination_id
184 AND ARA.gl_date < gc_per_start_date
185 AND ARA.adjustment_id = ARD.source_id
186 AND ARD.source_table = 'ADJ'
187 AND ARD.source_type = 'REC'
188 AND RCTT.type IN ('INV','CM')
189 AND RCT.complete_flag = 'Y'
190 AND NVL( ARA.subsequent_trx_id, -111) = DECODE(RCTT.type, 'INV', -111,
191 'CM', RCT.customer_trx_id)
192 AND RCTT.post_to_gl = 'Y'
193 AND RCTD.account_class = 'REC'
194 AND RCTD.latest_rec_flag = 'Y';
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 RETURN( NULL );
198 WHEN TOO_MANY_ROWS THEN
199 RETURN(NULL);
200 END;
201
202 BEGIN
203 SELECT NVL(SUM(APSA.amount_due_original),0)
204 INTO ln_deposit_cancel
205 FROM ar_payment_schedules_all APSA
206 ,ra_customer_trx_all RCT
207 WHERE RCT.previous_customer_trx_id=p_customer_trx_id
208 AND APSA.gl_date < gc_per_start_date
209 AND RCT.customer_trx_id=APSA.customer_trx_id
210 AND APSA.class = 'CM';
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 RETURN( NULL );
214 WHEN TOO_MANY_ROWS THEN
215 RETURN(NULL);
216 END;
217 ln_carrying_bal:= ln_invoice_bal + ln_deposit_cancel;
218 RETURN(ln_carrying_bal);
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 RAISE;
223 END commitment_balance;
224
225 END AR_DEP_BAL_RPT_PKG;
[Home] [Help]