1 PACKAGE BODY FA_JAPAN_DEP_TAX_SUMMARY_PKG AS
2 /* $Header: FADTXSB.pls 120.2.12010000.3 2010/02/16 07:02:26 pmadas ship $ */
3
4 function BeforeReport return boolean is
5
6 begin
7
8 Select
9 SC.Location_flex_structure,
10 SOB.Currency_code
11 into h_loc_flex_struct,
12 CURRENCY_CODE
13 from FA_SYSTEM_CONTROLS SC,
14 FA_BOOK_CONTROLS BC,
15 GL_SETS_OF_BOOKS SOB
16 where BC.BOOK_TYPE_CODE = P_BOOK
17 and SOB.Set_of_books_id = BC.Set_of_books_ID;
18
19 select meaning
20 into h_imperial_code
21 from FA_LOOKUPS
22 where lookup_type ='JP_IMPERIAL'
23 and lookup_code = to_char(to_date(to_char(p_year),'YYYY'),'E','nls_calendar=''Japanese Imperial''');
24
25 select to_number(to_char(to_date(to_char(p_year),'YYYY'),'YY','nls_calendar=''Japanese Imperial'''))
26 into h_imperial_year
27 from dual;
28
29 select meaning
30 into h_today_imperial_code
31 from FA_LOOKUPS
32 where lookup_type ='JP_IMPERIAL'
33 and lookup_code = to_char(to_date(to_char(sysdate,'YYYY'),'YYYY'),'E','nls_calendar=''Japanese Imperial''');
34
35 select to_number(to_char(to_date(to_char(sysdate,'YYYY'),'YYYY'),'YY','nls_calendar=''Japanese Imperial'''))
36 into h_today_imperial_year
40
37 from dual;
38
39 return (TRUE);
41 end;
42
43 function AfterReport return boolean is
44 begin
45 return (TRUE);
46 end;
47
48 function d_state_descformula(l_state in varchar2) return varchar2 is
49 c_state_desc varchar2(400);
50 begin
51 c_state_desc :=
52 fa_rx_flex_pkg.get_description(
53 p_application_id => 140,
54 p_id_flex_code => 'LOC#',
55 p_id_flex_num => h_loc_flex_struct,
56 p_qualifier => 'LOC_STATE',
57 p_data => l_state);
58
59 c_state_desc := l_state||' - '||c_state_desc;
60
61 return(c_state_desc);
62 end;
63
64
65
66 --Functions to refer Oracle report placeholders--
67
68 Function H_IMPERIAL_CODE_p return varchar2 is
69 Begin
70 return H_IMPERIAL_CODE;
71 END;
72 Function H_IMPERIAL_YEAR_p return number is
73 Begin
74 return H_IMPERIAL_YEAR;
75 END;
76 Function H_TODAY_IMPERIAL_CODE_p return varchar2 is
77 Begin
78 return H_TODAY_IMPERIAL_CODE;
79 END;
80 Function H_TODAY_IMPERIAL_YEAR_p return number is
81 Begin
82 return H_TODAY_IMPERIAL_YEAR;
83 END;
84 Function H_DECISION_p return varchar2 is
85 Begin
86 return H_DECISION;
87 END;
88 Function H_STATE_DESC_p return varchar2 is
89 Begin
90 return H_STATE_DESC;
91 END;
92 Function CURRENCY_CODE_p return varchar2 is
93 Begin
94 return CURRENCY_CODE;
95 END;
96 Function H_LOC_FLEX_STRUCT_p return number is
97 Begin
98 return H_LOC_FLEX_STRUCT;
99 END;
100
101 Function get_fnd_meaning(l_type in varchar2, l_code in varchar2) return varchar2 is
102 l_meaning varchar2(80);
103 begin
104 select meaning
105 into l_meaning
106 from fnd_lookups
107 where lookup_type = l_type
108 and lookup_code = l_code;
109
110 return (l_meaning);
111 end;
112
113 Function get_fa_meaning(l_type in varchar2, l_code in varchar2) return varchar2 is
114 l_meaning varchar2(80);
115 begin
116 select meaning
117 into l_meaning
118 from fa_lookups
119 where lookup_type = l_type
120 and lookup_code = l_code;
121
122 return (l_meaning);
123 end;
124
125 function d_decisionformula(l_state in varchar2) return varchar2 is
126 c_sum_theoretical_nbv number;
127 c_sum_evaluated_nbv number;
128 begin
129
130 select sum(theoretical_nbv), sum(evaluated_nbv)
131 into c_sum_theoretical_nbv, c_sum_evaluated_nbv
132 from FA_DEPRN_TAX_REP_ITF DTI
133 where DTI.END_COST >0
134 and DTI.STATE = l_state
135 and DTI.REQUEST_ID = p_request_id;
136
137 if c_sum_theoretical_nbv > c_sum_evaluated_nbv then
138 H_DECISION :='THEORETICAL';
139 else
140 H_DECISION :='EVALUATED';
141 end if;
142
143 return(H_DECISION);
144
145 end;
146
147 -- Bug 9362009: Modified the function to display data even if a single asset exists.
148 Function NO_DATA_FOUND_p return varchar2 is
149 l_found varchar2(3);
150 l_count number;
151 begin
152
153 l_found := 'YES';
154 l_count := 0;
155
156 begin
157 select count(1)
158 into l_count
159 from FA_DEPRN_TAX_REP_ITF
160 where REQUEST_ID = p_request_id
161 and ASSET_ID IS NOT NULL;
162
163 if l_count > 0 then
164 l_found := 'NO';
165 else
166 l_found := 'YES';
167 end if;
168
169 exception
170 when others then
171 l_found := 'YES';
172 end;
173
174 return (l_found);
175 end;
176 -- End Bug 9362009
177
178 END FA_JAPAN_DEP_TAX_SUMMARY_PKG ;