[Home] [Help]
PACKAGE BODY: APPS.FA_FASTXPRF_XMLP_PKG
Source
1 PACKAGE BODY FA_FASTXPRF_XMLP_PKG AS
2 /* $Header: FASTXPRFB.pls 120.0.12010000.1 2008/07/28 13:17:44 appldev ship $ */
3
4 function report_nameformula(Company_Name in varchar2) return varchar2 is
5 begin
6
7 DECLARE
8 l_report_name VARCHAR2(80);
9 l_conc_program_id NUMBER;
10 BEGIN
11 --Added during DT Fix
12 P_CONC_REQUEST_ID := fnd_global.CONC_REQUEST_ID;
13 --End of DT Fix
14 RP_Company_Name := Company_Name;
15
16 SELECT cr.concurrent_program_id
17 INTO l_conc_program_id
18 FROM FND_CONCURRENT_REQUESTS cr
19 WHERE cr.program_application_id = 140
20 AND cr.request_id = P_CONC_REQUEST_ID;
21
22 SELECT cp.user_concurrent_program_name
23 INTO l_report_name
24 FROM FND_CONCURRENT_PROGRAMS_VL cp
25 WHERE
26 cp.concurrent_program_id= l_conc_program_id
27 and cp.application_id = 140;
28
29 l_report_name := substr(l_report_name,1,instr(l_report_name,' (XML)'));
30
31 RP_Report_Name := l_report_name;
32 RETURN(l_report_name);
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 RP_Report_Name := ':Tax Preference Report:';
37 RETURN(RP_REPORT_NAME);
38 END;
39 RETURN NULL; end;
40
41 function BeforeReport return boolean is
42 begin
43
44 /*SRW.USER_EXIT('FND SRWINIT');*/null;
45 return (TRUE);
46 end;
47
48 function AfterReport return boolean is
49 begin
50
51 begin
52
53 rollback;
54 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
55
56 end;
57 return (TRUE);
58 end;
59
60 function period1_pcformula(DISTRIBUTION_SOURCE_BOOK in varchar2) return number is
61 begin
62
63 DECLARE
64 l_period_POD DATE;
65 l_period_PCD DATE;
66 l_period_PC NUMBER(15);
67 l_period_FY NUMBER(15);
68 l_corp_end_pc NUMBER(15);
69 BEGIN
70 SELECT DP_FED.period_counter,
71 DP_FED.period_open_date,
72 nvl(DP_FED.period_close_date, sysdate),
73 DP_FED.fiscal_year,
74 DP_CORP.Period_Counter
75 INTO l_period_PC,
76 l_period_POD,
77 l_period_PCD,
78 l_period_FY,
79 l_corp_end_pc
80 FROM FA_DEPRN_PERIODS DP_CORP,
81 FA_DEPRN_PERIODS DP_FED
82 WHERE DP_FED.book_type_code = P_BOOK
83 AND DP_FED.period_name = P_PERIOD1
84 AND DP_CORP.BOOK_TYPE_CODE = DISTRIBUTION_SOURCE_BOOK
85 AND DP_CORP.PERIOD_COUNTER = (SELECT MAX(DP.PERIOD_COUNTER)
86 FROM FA_DEPRN_PERIODS DP
87 WHERE DP.BOOK_TYPE_CODE =
88 DISTRIBUTION_SOURCE_BOOK AND
89 DP.CALENDAR_PERIOD_CLOSE_DATE <=
90 DP_FED.CALENDAR_PERIOD_CLOSE_DATE);
91
92 Period1_POD := l_period_POD;
93 Period1_PCD := l_period_PCD;
94 Period1_FY := l_period_FY;
95 CORP_END_PC := l_corp_end_pc;
96 return(l_period_PC);
97 END;
98 RETURN NULL; end;
99
100 function c_do_insertformula(period1_pc in number, DISTRIBUTION_SOURCE_BOOK in varchar2, acct_flex_bal_seg in varchar2) return number is
101 --Added during DT Fix
102 PRAGMA AUTONOMOUS_TRANSACTION;
103 --End of DT Fix
104 begin
105 declare
106
107 l_fed_min_pc NUMBER(15);
108 l_fed_max_pc NUMBER(15);
109 l_corp_min_pc NUMBER(15);
110 l_temp number(15);
111 l_sql_str VARCHAR2(32767);
112
113 begin
114
115 SELECT min(fp1.period_counter),
116 max(fp1.period_counter),
117 min(fp2.period_counter)
118 INTO l_fed_min_pc, l_fed_max_pc,
119 l_corp_min_pc
120 FROM fa_deprn_periods fp1, fa_deprn_periods fp2
121 WHERE fp1.book_type_code = P_BOOK
122 and fp1.period_close_date is not NULL
123 and fp1.period_counter <= period1_pc
124 and fp1.fiscal_year = period1_FY
125 and fp2.book_type_code = DISTRIBUTION_SOURCE_BOOK
126 and fp2.period_close_date is NOT NULL
127 and fp2.period_counter <= corp_end_pc
128 and fp2.fiscal_year = period1_FY;
129
130 min_fed_pc := l_fed_min_pc;
131 max_fed_pc := l_fed_max_pc;
132 min_corp_pc := l_corp_min_pc;
133
134
135 l_sql_str := 'INSERT INTO FA_TAX_REPORT(
136 REQUEST_ID,
137 COMP_CODE,
138 DEPRN_METHOD,
139 ASSET_ACCOUNT,
140 ASSET_ID,
141 FED_YTD_DEPRN,
142 CORP_YTD_DEPRN)
143 SELECT ' || to_char(p_conc_request_id) || ' REQUEST_ID, ' ||
144 acct_flex_bal_seg || ' COMP_CODE,
145 BK_FED.DEPRN_METHOD_CODE FED_METHOD,
146 CB_CORP.ASSET_COST_ACCT ASSET_ACCOUNT,
147 DD_FED.ASSET_ID ASSET_ID,
148 SUM(DD_FED.deprn_amount) FED_YTD_DEPRN,
149 0
150 FROM
151 FA_DEPRN_PERIODS DP,
152 FA_DEPRN_DETAIL DD_FED,
153 FA_ASSET_HISTORY AH,
154 FA_BOOKS BK_FED,
155 FA_CATEGORY_BOOKS CB_CORP,
156 FA_CATEGORIES CAT,
157 FA_DISTRIBUTION_HISTORY DH,
158 GL_CODE_COMBINATIONS DHCC
159 WHERE
160 DD_FED.BOOK_TYPE_CODE = ''' || P_BOOK || ''' AND
161 DD_FED.PERIOD_COUNTER between ' || to_char(l_fed_min_pc) ||
162 ' and ' || to_char(l_fed_max_pc) || ' AND
163 DD_FED.DEPRN_SOURCE_CODE <> ''B'' AND
164 BK_FED.BOOK_TYPE_CODE = DD_FED.BOOK_TYPE_CODE AND
165 BK_FED.ASSET_ID = DD_FED.ASSET_ID AND
166 BK_FED.DATE_EFFECTIVE <= DP.PERIOD_CLOSE_DATE AND
167 NVL(BK_FED.DATE_INEFFECTIVE, DP.PERIOD_CLOSE_DATE) >= DP.PERIOD_CLOSE_DATE AND
168 DP.BOOK_TYPE_CODE = DD_FED.BOOK_TYPE_CODE AND
169 DP.PERIOD_COUNTER = DD_FED.PERIOD_COUNTER AND
170 CB_CORP.BOOK_TYPE_CODE = ''' || DISTRIBUTION_SOURCE_BOOK || ''' AND
171 CB_CORP.ASSET_COST_ACCT BETWEEN
172 NVL(''' || p_from_acct || ''', CB_CORP.ASSET_COST_ACCT)
173 AND NVL(''' || p_to_acct || ''', CB_CORP.ASSET_COST_ACCT) AND
174 CB_CORP.CATEGORY_ID = CAT.CATEGORY_ID AND
175 AH.ASSET_ID = DD_FED.ASSET_ID AND
176 AH.DATE_EFFECTIVE <= DP.PERIOD_CLOSE_DATE AND
177 NVL(AH.DATE_INEFFECTIVE, DP.PERIOD_CLOSE_DATE + 1) > DP.PERIOD_CLOSE_DATE AND
178 DH.BOOK_TYPE_CODE = ''' || DISTRIBUTION_SOURCE_BOOK || ''' AND
179 CB_CORP.CATEGORY_ID = AH.CATEGORY_ID AND
180 DH.ASSET_ID = DD_FED.ASSET_ID AND
181 DH.DISTRIBUTION_ID = DD_FED.DISTRIBUTION_ID AND
182 DH.DATE_EFFECTIVE <= DP.PERIOD_CLOSE_DATE AND
183 NVL(DH.DATE_INEFFECTIVE, DP.PERIOD_CLOSE_DATE) >= DP.PERIOD_CLOSE_DATE AND
184 DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID(+)
185 GROUP BY ' ||
186 acct_flex_bal_seg || ',
187 BK_FED.DEPRN_METHOD_CODE,
188 CB_CORP.ASSET_COST_ACCT,
189 3,
190 DD_FED.ASSET_ID';
191
192 /*srw.do_sql(l_sql_str);*/null;
193
194 execute immediate(l_sql_str);
195 --Added during DT Fix
196 commit;
197 --End of DT Fix
198 return(1);
199 end;
200 end;
201
202 --Functions to refer Oracle report placeholders--
203
204 Function ACCT_BAL_APROMPT_p return varchar2 is
205 Begin
206 return ACCT_BAL_APROMPT;
207 END;
208 Function CAT_MAJ_RPROMPT_p return varchar2 is
209 Begin
210 return CAT_MAJ_RPROMPT;
211 END;
212 Function CORP_END_PC_p return number is
213 Begin
214 return CORP_END_PC;
215 END;
216 Function Period1_POD_p return date is
217 Begin
218 return Period1_POD;
219 END;
220 Function Period1_PCD_p return date is
221 Begin
222 return Period1_PCD;
223 END;
224 Function Period1_FY_p return number is
225 Begin
226 return Period1_FY;
227 END;
228 Function min_fed_pc_p return number is
229 Begin
230 return min_fed_pc;
231 END;
232 Function max_fed_pc_p return number is
233 Begin
234 return max_fed_pc;
235 END;
236 Function min_corp_pc_p return number is
237 Begin
238 return min_corp_pc;
239 END;
240 Function RP_COMPANY_NAME_p return varchar2 is
241 Begin
242 return RP_COMPANY_NAME;
243 END;
244 Function RP_REPORT_NAME_p return varchar2 is
245 Begin
246 return RP_REPORT_NAME;
247 END;
248 Function RP_BAL_LPROMPT_p return varchar2 is
249 Begin
250 return RP_BAL_LPROMPT;
251 END;
252 END FA_FASTXPRF_XMLP_PKG ;
253