1 PACKAGE BODY AR_DEP_BAL_RPT_PKG
2 -- $Header: AR_DEPBALRPT_PB.pls 120.0.12000000.1 2007/10/25 11:33:51 sgudupat noship $
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 --************************************************************************
41 AS
42 FUNCTION beforeReport RETURN BOOLEAN
43 IS
44 BEGIN -- Beginning of the Function beforeReport .
45 /* Query to get the ledger Id */
46 BEGIN
47 SELECT GLL.ledger_id
48 INTO gn_ledger_id
49 FROM gl_ledgers GLL
50 ,gl_access_set_norm_assign GASNA
51 WHERE GASNA.access_set_id = FND_PROFILE.VALUE('GL_ACCESS_SET_ID')
52 AND GLL.ledger_id = GASNA.ledger_id
53 AND GLL.ledger_category_code = 'PRIMARY';
54 END;
55 /*Query to get the Start date for a given Period From parameter */
56 BEGIN
57 SELECT GLP.start_date
58 INTO gc_per_start_date
59 FROM gl_periods GLP
60 ,gl_ledgers GLL
61 WHERE GLL.ledger_id = gn_ledger_id
62 AND GLP.period_set_name = GLL.period_set_name
63 AND GLP.period_name = P_PERIOD_FROM;
64 END;
65 /*Query to get the End date for a given Period To parameter */
66 BEGIN
67 SELECT GLP.end_date
68 INTO gc_per_end_date
69 FROM gl_periods GLP
70 ,gl_ledgers GLL
71 WHERE GLL.ledger_id = gn_ledger_id
72 AND GLP.period_set_name = GLL.period_set_name
73 AND GLP.period_name = P_PERIOD_TO;
74 END;
75
76 IF p_customer_name IS NOT NULL THEN
77 gc_customer := 'AND HZP.party_id = :P_CUSTOMER_NAME ';
78 ELSE
79 gc_customer := 'AND 1=1';
80 END IF;
81
82 IF p_currency IS NOT NULL THEN
83 gc_currency := 'AND RCT.invoice_currency_code = :P_CURRENCY ';
84 ELSE
85 gc_currency := 'AND 1=1';
86 END IF;
87 RETURN TRUE;
88 END beforeReport; --End of the Function beforeReport
89
90 FUNCTION description(p_value IN VARCHAR2, p_segment IN VARCHAR2)
91 RETURN VARCHAR2
92 IS
93 lc_value_desc VARCHAR2(100);
94 BEGIN
95 /* Query to get the Descriptions of the Segment values. */
96 BEGIN
97 IF p_segment LIKE '%SEGMENT%' THEN
98 SELECT DISTINCT FFV.description
99 INTO lc_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_segment
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 GAS.chart_of_accounts_id
111 FROM gl_access_sets GAS
112 WHERE GAS.access_set_id = FND_PROFILE.value('GL_ACCESS_SET_ID'))
113 AND FFV.flex_value = p_value;
114 ELSE
115 /* Query to get the Descriptions of the Attribute values. */
116 SELECT FFV.description
117 INTO lc_value_desc
118 FROM fnd_descr_flex_col_usage_vl FDFCU
119 ,fnd_flex_values_vl FFV
120 WHERE FDFCU.flex_value_set_id = FFV.flex_value_set_id
121 AND FDFCU.application_id = 222
122 AND FDFCU.descriptive_flexfield_name = 'RA_CUSTOMER_TRX'
123 AND FDFCU.descriptive_flex_context_code ='Global Data Elements'
124 AND FDFCU.application_column_name = p_segment
125 AND FFV.flex_value = p_value;
126 END IF;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 lc_value_desc := NULL;
130 END;
131 RETURN lc_value_desc;
132 END description;
133
134
135 FUNCTION commitment_balance( p_customer_trx_id IN NUMBER) RETURN NUMBER
136 IS
137 ln_commitment_bal NUMBER;
138 ln_carrying_bal NUMBER;
139 ln_invoice_bal NUMBER;
140 ln_deposit_cancel NUMBER;
141 BEGIN
142 BEGIN /*Query to find the Balances for the given Customer Trx Id */
143 SELECT NVL(RCTL.extended_amount,0)
144 INTO ln_commitment_bal
145 FROM hz_cust_accounts HCA
146 ,ra_customer_trx_lines RCTL
147 ,ra_customer_trx RCT
148 ,ra_cust_trx_types RCTT
149 WHERE RCT.customer_trx_id = p_customer_trx_id
150 AND RCT.cust_trx_type_id = RCTT.cust_trx_type_id
151 AND RCT.org_id = RCTT.org_id
152 AND RCT.customer_trx_id = RCTL.customer_trx_id
153 AND RCT.bill_to_customer_id = HCA.cust_account_id
154 AND RCTT.type = 'DEP'
155 ORDER BY RCT.trx_number;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 RETURN( NULL );
159 WHEN TOO_MANY_ROWS THEN
160 RETURN(NULL);
161 END;
162
163 BEGIN
164 SELECT NVL( ln_commitment_bal, 0) - (NVL(SUM( ARA.AMOUNT),0) * -1)
165 INTO ln_invoice_bal
166 FROM ra_customer_trx RCT
167 ,ra_cust_trx_line_gl_dist_all RCTD
168 ,ra_cust_trx_types_all RCTT
169 ,hz_cust_accounts HCA
170 ,hz_parties HZP
171 ,gl_code_combinations GCC
172 ,ar_adjustments_all ARA
173 ,ar_distributions_all ARD
174 WHERE RCT.initial_customer_trx_id = p_customer_trx_id
175 AND RCTD.customer_trx_id = RCT.customer_trx_id
176 AND RCT.customer_trx_id = DECODE(RCTT.type, 'INV', ARA.customer_trx_id,
177 'CM', ARA.subsequent_trx_id)
178 AND RCT.cust_trx_type_id = RCTT.cust_trx_type_id
179 AND RCT.org_id = RCTT.org_id
180 AND RCT.bill_to_customer_id = HCA.cust_account_id
181 AND HCA.party_id = HZP.party_id
182 AND ARD.code_combination_id = GCC.code_combination_id
183 AND ARA.gl_date < gc_per_start_date
184 AND ARA.adjustment_id = ARD.source_id
185 AND ARD.source_table = 'ADJ'
186 AND ARD.source_type = 'REC'
187 AND RCTT.type IN ('INV','CM')
188 AND RCT.complete_flag = 'Y'
189 AND NVL( ARA.subsequent_trx_id, -111) = DECODE(RCTT.type, 'INV', -111,
190 'CM', RCT.customer_trx_id)
191 AND RCTT.post_to_gl = 'Y'
192 AND RCTD.account_class = 'REC'
193 AND RCTD.latest_rec_flag = 'Y';
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 RETURN( NULL );
197 WHEN TOO_MANY_ROWS THEN
198 RETURN(NULL);
199 END;
200
201 BEGIN
202 SELECT NVL(SUM(APSA.amount_due_original),0)
203 INTO ln_deposit_cancel
204 FROM ar_payment_schedules_all APSA
205 ,ra_customer_trx_all RCT
206 WHERE RCT.previous_customer_trx_id=p_customer_trx_id
207 AND APSA.gl_date < gc_per_start_date
208 AND RCT.customer_trx_id=APSA.customer_trx_id
209 AND APSA.class = 'CM';
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 RETURN( NULL );
213 WHEN TOO_MANY_ROWS THEN
214 RETURN(NULL);
215 END;
216 ln_carrying_bal:= ln_invoice_bal + ln_deposit_cancel;
217 RETURN(ln_carrying_bal);
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 RAISE;
222 END commitment_balance;
223
224 END AR_DEP_BAL_RPT_PKG;
[Home] [Help]