1 PACKAGE BODY XLA_TP_SUMMARY_RPT_PKG
2 -- $Header: XLA_TPSUMRPT_PB.pls 120.4.12000000.1 2007/10/23 12:57:20 sgudupat noship $
3 /*===========================================================================+
4 | Copyright (c) 2003 Oracle Corporation BelmFont, California, USA |
5 | ALL rights reserved. |
6 +============================================================================+
7 | FILENAME |
8 | XLA_TPSUMRPT_PB.pls |
9 | |
10 | PACKAGE NAME |
11 | XLA_TP_SUMMARY_RPT_PKG |
12 | |
13 | DESCRIPTION |
14 | Package Body. This provides XML extract for Trading Partner |
15 | Summary Report |
16 | |
17 | HISTORY |
18 | 05/06/2007 Rakesh Pulla Created |
19 | 18/09/2007 Rakesh Pulla Modified the code as it has an impact |
20 | on the new parameter added |
21 | (Third Party type) |
22 | 09/10/2007 Rakesh Pulla Changes incorporated according to the |
23 | Bug # 6401736 and 6460402 |
24 | 12/10/2007 Rakesh Pulla Added the functions min_period |
25 | & max_period |
26 | |
27 +===========================================================================*/
28 AS
29 FUNCTION beforeReport RETURN BOOLEAN
30 IS
31 C_NULL_PARTY_COLS CONSTANT VARCHAR2(1000):= ',NULL PARTY_ID
32 ,NULL PARTY_NUMBER
33 ,NULL PARTY_NAME
34 ,NULL PARTY_SITE_ID
35 ,NULL PARTY_SITE_NUMBER
36 ,NULL PARTY_SITE_TAX_REGS_NUMBER';
37 C_NULL_PARTY_GROUP CONSTANT VARCHAR2(1000):= ',NULL
38 ,NULL
39 ,NULL
40 ,NULL
41 ,NULL
42 ,NULL';
43
44 BEGIN -- Begining of the Function beforereport
45
46 -- following will set the right transaction security.
47 XLA_SECURITY_PKG.set_security_context(P_RESP_APPLICATION_ID);
48 /* Query to select the Period Start Date and Period End Date */
49 BEGIN
50 SELECT
51 MAX(CASE GP.period_name WHEN P_PERIOD_FROM THEN
52 TO_DATE(TO_CHAR(GP.start_date,'DD-MON-YYYY')
53 ||' 00:00:00','DD-MM-YYYY HH24:MI:SS') END)
54 ,MAX(CASE GP.period_name WHEN P_PERIOD_TO THEN
55 TO_DATE(TO_CHAR(GP.end_date,'DD-MON-YYYY')
56 ||' 23:59:59','DD-MM-YYYY HH24:MI:SS') END)
57 INTO P_PERIOD_START_DATE,P_PERIOD_END_DATE
58 FROM gl_periods GP
59 ,gl_ledgers GLL
60 WHERE GLL.ledger_id=P_LEDGER_ID
61 AND GP.period_set_name =GLL.period_set_name
62 AND GP.period_name IN (P_PERIOD_FROM,P_PERIOD_TO);
63 END;
64 /* Query to select the ledger name and the ledger currency.*/
65 BEGIN
66 SELECT currency_code
67 INTO P_LEDGER_CURRENCY
68 FROM gl_ledgers_public_v
69 WHERE ledger_id=P_LEDGER_ID;
70 END;
71 /* Query to fetch the Effective period number from */
72 BEGIN
73 SELECT effective_period_num
74 INTO P_PERIOD_NUM_FROM
75 FROM gl_period_statuses GPS
76 WHERE GPS.period_name=P_PERIOD_FROM
77 AND GPS.application_id=101
78 AND GPS.ledger_id=P_LEDGER_ID;
79 END;
80 /* Query to fetch the Effective period number to */
81 BEGIN
82 SELECT effective_period_num
83 INTO P_PERIOD_NUM_TO
84 FROM gl_period_statuses GPS
85 WHERE GPS.period_name=P_PERIOD_TO
86 AND GPS.application_id=101
87 AND GPS.ledger_id=P_LEDGER_ID;
88 END;
89
90 /* Removed in order to fix the issues in Bug # 6401736 */
91 /* Query to fetch the Period From which exist in the Control Balances Table.
92 BEGIN
93 SELECT period_name
94 INTO P_AC_PERIOD_FROM
95 FROM gl_period_statuses
96 WHERE effective_period_num=(
97 SELECT MIN(GPS.effective_period_num)
98 FROM gl_period_statuses GPS, xla_control_balances XCB
99 WHERE GPS.effective_period_num BETWEEN P_PERIOD_NUM_FROM
100 AND P_PERIOD_NUM_TO
101 AND XCB.period_name=GPS.period_name
102 AND XCB.ledger_id=GPS.ledger_id
103 AND XCB.application_id=GPS.application_id
104 AND GPS.application_id=P_RESP_APPLICATION_ID
105 AND GPS.ledger_id=P_LEDGER_ID)
106 AND ledger_id=P_LEDGER_ID
107 AND application_id=P_RESP_APPLICATION_ID;
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 fnd_file.put_line (fnd_file.LOG, 'The given Period From does not exist in the xla_control_balances table');
111 END; */
112
113 /* Query to fetch the Period To which exist in the Control Balances Table.
114 BEGIN
115 SELECT period_name
116 INTO P_AC_PERIOD_TO
117 FROM gl_period_statuses
118 WHERE effective_period_num=(
119 SELECT MAX(GPS.effective_period_num)
120 FROM gl_period_statuses GPS, xla_control_balances XCB
121 WHERE GPS.effective_period_num
122 BETWEEN P_PERIOD_NUM_FROM
123 AND P_PERIOD_NUM_TO
124 AND XCB.period_name=GPS.period_name
125 AND XCB.ledger_id=GPS.ledger_id
126 AND XCB.application_id=GPS.application_id
127 AND GPS.application_id=P_RESP_APPLICATION_ID
128 AND GPS.ledger_id=P_LEDGER_ID)
129 AND ledger_id=P_LEDGER_ID
130 AND application_id=P_RESP_APPLICATION_ID;
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 fnd_file.put_line (fnd_file.LOG, 'The given Period To does not exist in the xla_control_balances table');
134 END; */
135 /* Third party information based on application_id */
136 IF P_PARTY_TYPE = 'SUPPLIER' THEN
137 p_party_col := ',APS.vendor_id PARTY_ID
138 ,APS.segment1 PARTY_NUMBER
139 ,APS.vendor_name PARTY_NAME
140 ,NVL(APSSA.vendor_site_id,-999) PARTY_SITE_ID
141 ,HPS.party_site_number PARTY_SITE_NUMBER';
142
143 p_party_group := ',APS.vendor_id
144 ,APS.segment1
145 ,APS.vendor_name
146 ,NVL(APSSA.vendor_site_id,-999)
147 ,HPS.party_site_number';
148
149 p_party_tab := ',ap_suppliers APS
150 ,ap_supplier_sites_all APSSA';
151
152
153 p_party_join := ' AND APS.vendor_id = XCB.party_id
154 AND HZP.party_id = APS.party_id
155 AND APSSA.vendor_site_id(+) = XCB.party_site_id
156 AND HPS.party_site_id(+) = APSSA.party_site_id
157 AND XCB.party_type_code = ''S''';
158
159
160 IF (P_THIRD_PARTY_FROM IS NOT NULL) AND (P_THIRD_PARTY_TO IS NOT NULL) THEN
161 p_party_name_join:= ' AND UPPER(APS.vendor_name) BETWEEN UPPER(:P_THIRD_PARTY_FROM)
162 AND UPPER(:P_THIRD_PARTY_TO) ';
163 ELSIF (P_THIRD_PARTY_FROM IS NULL) AND (P_THIRD_PARTY_TO IS NOT NULL) THEN
164 p_party_name_join:= ' AND UPPER(APS.vendor_name) <= UPPER(:P_THIRD_PARTY_TO) ';
165 ELSIF (P_THIRD_PARTY_FROM IS NOT NULL) AND (P_THIRD_PARTY_TO IS NULL) THEN
166 p_party_name_join:= ' AND UPPER(APS.vendor_name) >= UPPER(:P_THIRD_PARTY_FROM) ';
167 ELSIF (P_THIRD_PARTY_FROM IS NULL) AND (P_THIRD_PARTY_TO IS NULL) THEN
168 p_party_name_join:= ' AND 1=1 ';
169 END IF;
170
171 IF P_THIRD_PARTY_TYPE IS NOT NULL THEN
172 p_party_join:= p_party_join || ' AND APS.vendor_type_lookup_code = :P_THIRD_PARTY_TYPE';
173 END IF;
174
175 ELSIF P_PARTY_TYPE = 'CUSTOMER' THEN
176 p_party_col := ',HCA.cust_account_id PARTY_ID
177 ,HCA.account_number PARTY_NUMBER
178 ,HZP.party_name PARTY_NAME
179 ,NVL(HZCU.site_use_id, -999) PARTY_SITE_ID
180 ,HPS.party_site_number PARTY_SITE_NUMBER';
181
182 p_party_group := ',HCA.cust_account_id
183 ,HCA.account_number
184 ,HZP.party_name
185 ,NVL(HZCU.site_use_id, -999)
186 ,HPS.party_site_number';
187
188 p_party_tab := ',hz_cust_accounts HCA
189 ,hz_cust_acct_sites_all HCAS
190 ,hz_cust_site_uses_all HZCU';
191
192 p_party_join := ' AND HZP.party_id = HCA.party_id
193 AND HCA.cust_account_id = XCB.party_id
194 AND HZCU.site_use_id(+) = XCB.party_site_id
195 AND HCAS.cust_acct_site_id(+) = HZCU.cust_acct_site_id
196 AND XCB.party_type_code = ''C''
197 AND HPS.party_site_id(+) = HCAS.party_site_id';
198
199
200 IF (P_THIRD_PARTY_FROM IS NOT NULL) AND (P_THIRD_PARTY_TO IS NOT NULL) THEN
201 p_party_name_join:= ' AND UPPER(HZP.party_name) BETWEEN UPPER(:P_THIRD_PARTY_FROM)
202 AND UPPER(:P_THIRD_PARTY_TO) ';
203 ELSIF (P_THIRD_PARTY_FROM IS NULL) AND (P_THIRD_PARTY_TO IS NOT NULL) THEN
204 p_party_name_join:= ' AND UPPER(HZP.party_name) <= UPPER(:P_THIRD_PARTY_TO) ';
205 ELSIF (P_THIRD_PARTY_FROM IS NOT NULL) AND (P_THIRD_PARTY_TO IS NULL) THEN
206 p_party_name_join:= ' AND UPPER(HZP.party_name) >= UPPER(:P_THIRD_PARTY_FROM) ';
207 ELSIF (P_THIRD_PARTY_FROM IS NULL) AND (P_THIRD_PARTY_TO IS NULL) THEN
208 p_party_name_join:= ' AND 1=1 ';
209 END IF;
210
211 IF P_THIRD_PARTY_TYPE IS NOT NULL THEN
212 p_party_join:= p_party_join || ' AND HCA.customer_class_code = :P_THIRD_PARTY_TYPE';
213 END IF;
214
215 ELSE
216 p_party_col := C_NULL_PARTY_COLS;
217 p_party_group := C_NULL_PARTY_GROUP;
218 END IF;
219
220 IF P_PERIOD_START_DATE = P_ACCOUNT_DATE_FROM THEN
221 P_BEG_DATE_RANGE:= 'SELECT 0 beg_date_range_act_dr
222 ,0 beg_date_range_act_cr
223 FROM SYS.DUAL ';
224 ELSE
225 P_BEG_DATE_RANGE:= 'SELECT NVL(SUM(XAL.accounted_dr),0) beg_date_range_act_dr
226 ,NVL(SUM(XAL.accounted_cr),0) beg_date_range_act_cr
227 FROM xla_ae_lines XAL
228 WHERE XAL.party_id= :party_id
229 AND XAL.party_site_id= :party_site_id
230 AND XAL.application_id= :P_RESP_APPLICATION_ID
231 AND XAL.ledger_id= :P_LEDGER_ID
232 AND XAL.code_combination_id= :code_combination_id
233 AND XAL.control_balance_flag=''Y''
234 AND XAL.accounting_date >= :P_PERIOD_START_DATE
235 AND XAL.accounting_date < :P_ACCOUNT_DATE_FROM ';
236 END IF;
237 IF P_PERIOD_END_DATE = P_ACCOUNT_DATE_TO THEN
238 P_PER_DATE_RANGE:= 'SELECT 0 per_date_range_act_dr
239 ,0 per_date_range_act_cr
240 FROM SYS.DUAL ';
241 ELSE
242 P_PER_DATE_RANGE:= 'SELECT NVL(SUM(XAL.accounted_dr),0) per_date_range_act_dr
243 ,NVL(SUM(XAL.accounted_cr),0) per_date_range_act_cr
244 FROM xla_ae_lines XAL
245 WHERE XAL.party_id= :party_id
246 AND XAL.party_site_id= :party_site_id
247 AND XAL.application_id= :P_RESP_APPLICATION_ID
248 AND XAL.ledger_id= :P_LEDGER_ID
249 AND XAL.code_combination_id= :code_combination_id
250 AND XAL.control_balance_flag=''Y''
251 AND XAL.accounting_date > :P_ACCOUNT_DATE_TO
252 AND XAL.accounting_date <= :P_PERIOD_END_DATE ';
253 END IF;
254 RETURN (TRUE);
255 END beforeReport; -- End of the beforereport
256
257 FUNCTION min_period( p_period_num IN NUMBER) RETURN NUMBER
258 IS
259 ln_actual_per_num NUMBER;
260 BEGIN
261 /*Query to find the minimum period existing in the control balances table */
262 SELECT MIN(p_period_num)
263 INTO ln_actual_per_num
264 FROM gl_period_statuses GPS
265 WHERE GPS.effective_period_num
266 BETWEEN P_PERIOD_NUM_FROM
267 AND P_PERIOD_NUM_TO
268 AND GPS.application_id=P_RESP_APPLICATION_ID
269 AND GPS.ledger_id=P_LEDGER_ID;
270
271 RETURN(ln_actual_per_num);
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN
274 RETURN(NULL);
275 WHEN TOO_MANY_ROWS THEN
276 RETURN(NULL);
277 WHEN OTHERS THEN
278 RAISE;
279 END min_period;
280
281 FUNCTION max_period( p_period_num IN NUMBER) RETURN NUMBER
282 IS
283 ln_actual_per_num NUMBER;
284 BEGIN
285 /*Query to find the maximum period existing in the control balances table */
286 SELECT MAX(p_period_num)
287 INTO ln_actual_per_num
288 FROM gl_period_statuses GPS
289 WHERE GPS.effective_period_num
290 BETWEEN P_PERIOD_NUM_FROM
291 AND P_PERIOD_NUM_TO
292 AND GPS.application_id=P_RESP_APPLICATION_ID
293 AND GPS.ledger_id=P_LEDGER_ID;
294
295 RETURN(ln_actual_per_num);
296 EXCEPTION
297 WHEN NO_DATA_FOUND THEN
298 RETURN(NULL);
299 WHEN TOO_MANY_ROWS THEN
300 RETURN(NULL);
301 WHEN OTHERS THEN
302 RAISE;
303 END max_period;
304 END XLA_TP_SUMMARY_RPT_PKG ;
[Home] [Help]