1 PACKAGE BODY FA_DEP_SUMM_TAX_REG_PKG
2 -- $Header: FADSTRPB.pls 120.1.12010000.1 2008/07/28 13:11:42 appldev ship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- FADSTRPB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of FA_DEP_SUMM_TAX_REG_PKG.
13 -- This package is used to generate Depreciation Summary Tax Register for Russia.
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 -- PROGRAM DESCRIPTION
20 -- beforereport Used to assign values for the variables used
21 -- for dynamic query.
22 -- currency_code Used to obtain the Currency Code
23 -- company_code Used to obtain the Company Code
24 --
25 -- DEPENDENCIES
26 -- None.
27 --
28 -- CALLED BY
29 -- DataTemplate Extract in Depreciation Summary Tax Register (Russia).
30 --
31 --
32 -- LAST UPDATE DATE 04-JAN-2007
33 -- Date the program has been modified for the last time
34 --
35 -- HISTORY
36 -- =======
37 --
38 -- VERSION DATE AUTHOR(S) DESCRIPTION
39 -- ------- ----------- --------------- ------------------------------------
40 -- 1.00 24-JAN-2007 Sandeep Kumar G. Creation
41 --
42 -- ****************************************************************************************
43 AS
44
45 --=====================================================================
46 --=====================================================================
47
48 FUNCTION beforereport RETURN BOOLEAN
49 IS
50 ld_period_close_date DATE;
51 ld_calender_close_date DATE;
52 ld_fiscal_start_date DATE;
53 BEGIN
54 --*********************************************************
55 --To select company Name
56 --*********************************************************
57 BEGIN
58 SELECT fsc.company_name
59 INTO gc_company_name
60 FROM fa_system_controls fsc;
61 EXCEPTION
62 WHEN NO_DATA_FOUND THEN
63 gc_company_name := NULL;
64 WHEN TOO_MANY_ROWS THEN
65 gc_company_name := NULL;
66 END;
67
68 --*********************************************************
69 --To select Functional Currency
70 --*********************************************************
71 BEGIN
72 SELECT gl.currency_code
73 INTO gc_currency_code
74 FROM fa_book_controls fbc
75 ,gl_ledgers gl
76 WHERE fbc.book_type_code = P_BOOK
77 AND fbc.set_of_books_id = gl.ledger_id;
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 gc_currency_code := NULL;
81 WHEN TOO_MANY_ROWS THEN
82 gc_currency_code := NULL;
83 END;
84
85 --*********************************************************
86 --To Fetch Column Name, value set for segment 'Tax Group'
87 --*********************************************************
88 BEGIN
89 SELECT fsav.application_column_name
90 ,fifs.flex_value_set_id
91 INTO gc_major_category
92 ,gn_maj_cat_value_set_id
93 FROM fnd_segment_attribute_values fsav
94 ,fnd_id_flex_segments fifs
95 WHERE fifs.segment_name = 'Tax Group'
96 --fsav.segment_attribute_type = 'BASED_CATEGORY'
97 AND fsav.attribute_value = 'Y'
98 AND fsav.id_flex_code = 'CAT#'
99 AND fsav.id_flex_num = 101
100 AND fifs.application_column_name = fsav.application_column_name
101 AND fifs.id_flex_num = fsav.id_flex_num
102 AND fifs.id_flex_code = fsav.id_flex_code;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 gc_major_category := NULL;
106 gn_maj_cat_value_set_id := NULL;
107 WHEN TOO_MANY_ROWS THEN
108 gc_major_category := NULL;
109 gn_maj_cat_value_set_id := NULL;
110 END;
111
112 --*********************************************************
113 --To Fetch Column Name for segment 'Asset Type'
114 --*********************************************************
115 /*SELECT fsav.application_column_name
116 INTO gc_minor_category
117 FROM fnd_segment_attribute_values fsav
118 WHERE segment_attribute_type = 'MINOR_CATEGORY'
119 AND attribute_value = 'Y'
120 AND id_flex_code = 'CAT#';
121 */
122 BEGIN
123 SELECT fifs.application_column_name
124 INTO gc_minor_category
125 FROM fnd_id_flex_segments fifs
126 WHERE fifs.segment_name = 'Asset Type'
127 AND fifs.id_flex_num = 101
128 AND fifs.id_flex_code = 'CAT#';
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 gc_minor_category := NULL;
132 WHEN TOO_MANY_ROWS THEN
133 gc_minor_category := NULL;
134 END;
135
136 --*********************************************************
137 --To Fetch Period Counter, Period Close Date, Fiscal year,
138 --Calendar Period Close Date for the Period Chosen
139 --*********************************************************
140 BEGIN
141 SELECT fdp.period_counter
142 ,NVL(fdp.period_close_date,sysdate)
143 ,fdp.fiscal_year
144 ,fdp.calendar_period_close_date
145 INTO gn_period_counter
146 ,ld_period_close_date
147 ,gc_fiscal_year
148 ,ld_calender_close_date
149 FROM fa_deprn_periods fdp
150 WHERE fdp.book_type_code = P_BOOK
151 AND fdp.period_name = P_PERIOD1;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 gn_period_counter := NULL;
155 ld_period_close_date := NULL;
156 gc_fiscal_year := NULL;
157 ld_calender_close_date := NULL;
158 WHEN TOO_MANY_ROWS THEN
159 gn_period_counter := NULL;
160 ld_period_close_date := NULL;
161 gc_fiscal_year := NULL;
162 ld_calender_close_date := NULL;
163 END;
164
165 gc_pcd := ' TO_DATE('''||ld_period_close_date||''',''DD-MON-YYYY'')';
166 gc_cal_pcd := ' TO_DATE('''||ld_calender_close_date||''',''DD-MON-YYYY'')';
167
168 --*********************************************************
169 --To Fetch fiscal start date for the period chosen
170 --*********************************************************
171 BEGIN
172 SELECT ffy.start_date
173 INTO ld_fiscal_start_date
174 FROM fa_book_controls fbc
175 ,fa_fiscal_year ffy
176 WHERE fbc.book_type_code = P_BOOK
177 AND ffy.fiscal_year = gc_fiscal_year
178 AND ffy.fiscal_year_name = fbc.fiscal_year_name;
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 ld_fiscal_start_date := NULL;
182 WHEN TOO_MANY_ROWS THEN
183 ld_fiscal_start_date := NULL;
184 END;
185 gc_fiscal_start_date := ' TO_DATE('''||ld_fiscal_start_date||''',''DD-MON-YYYY'')';
186 RETURN (TRUE);
187
188 END beforereport;
189
190 FUNCTION company_name RETURN VARCHAR2
191 IS
192 BEGIN
193 RETURN (gc_company_name);
194 END company_name;
195
196
197 FUNCTION currency_code RETURN VARCHAR2
198 IS
199 BEGIN
200 RETURN (gc_currency_code);
201 END currency_code;
202
203 END FA_DEP_SUMM_TAX_REG_PKG;