[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS2007_CERT_PKG
Source
1 PACKAGE BODY IGI_CIS2007_CERT_PKG AS
2 -- $Header: igipuprb.pls 120.0.12000000.1 2007/07/13 07:05:52 vensubra noship $
3
4
5 l_debug_level Number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_state_level Number := FND_LOG.LEVEL_STATEMENT;
7 l_proc_level Number := FND_LOG.LEVEL_PROCEDURE;
8 l_event_level Number := FND_LOG.LEVEL_EVENT;
9 l_excep_level Number := FND_LOG.LEVEL_EXCEPTION;
10 l_error_level Number := FND_LOG.LEVEL_ERROR;
11 l_unexp_level Number := FND_LOG.LEVEL_UNEXPECTED;
12 l_path Varchar2(50) := 'IGI.PLSQL.igipuprb.IGI_CIS2007_CERT_PKG.';
13
14
15 Procedure WriteLog ( pp_mesg in varchar2 ) Is
16 l_debug varchar2(1);
17 Begin
18 l_debug := fnd_profile.value('IGI_DEBUG_OPTION');
19 If l_debug = 'Y' Then
20 Fnd_file.put_line( fnd_file.log , pp_mesg ) ;
21 End If;
22 End;
23
24
25 Procedure Debug( p_level IN Number, p_path IN Varchar2, p_mesg IN Varchar2 ) Is
26 Begin
27 If (p_level >= l_debug_level ) THEN
28 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
29 End If;
30 End ;
31
32 Procedure Update_Rates(
33 errbuf OUT NOCOPY VARCHAR2,
34 retcode OUT NOCOPY NUMBER,
35 p_tax_id IN NUMBER,
36 p_group_id IN NUMBER ) Is
37
38 Cursor C_Tax_Info Is
39 SELECT agt.group_id Group_Id,
40 atc.name Tax_Name,
41 atr.tax_rate_id Tax_Rate_Id,
42 atr.tax_rate Tax_Rate,
43 atr.start_date,
44 atr.end_date
45 FROM ap_tax_codes atc,
46 ap_awt_group_taxes agt,
47 ap_awt_tax_rates atr
48 WHERE agt.group_id = nvl(p_group_id, group_id)
49 AND atc.name = agt.tax_name
50 AND atc.tax_id = p_tax_id
51 AND atc.tax_type = 'AWT'
52 AND(trunc(sysdate) BETWEEN trunc(nvl(atc.start_date, sysdate -1))
53 AND trunc(nvl(atc.inactive_date, sysdate + 1)))
54 AND atc.name = atr.tax_name
55 AND atr.rate_type = 'STANDARD'
56 AND(trunc(sysdate) BETWEEN trunc(nvl(atr.start_date, sysdate -1))
57 AND trunc(nvl(atr.end_date, sysdate + 1)))
58 ORDER BY agt.group_id, atr.tax_rate;
59
60
61 Cursor C_Tax_Names(p_new_grp_id in ap_awt_groups.group_id%Type) Is
62 SELECT atr.*
63 FROM ap_awt_tax_rates atr, igi_cis_tax_treatment_h his
64 WHERE his.new_group_id = p_new_grp_id
65 AND atr.tax_rate_id = his.tax_rate_id
66 AND atr.priority = 1
67 ORDER BY atr.vendor_id, atr.vendor_site_id;
68
69 l_new_tax_rate_id ap_awt_tax_rates.tax_rate_id%Type;
70
71 Begin
72 -- Process the latest Valid Tax Rate
73 For C_Tax_Info_Rec in C_Tax_Info Loop
74 -- Debug Messages
75 Debug(l_state_level, 'Update_Rates', 'Processing Tax Info Cursor');
76 Debug(l_state_level, 'Update_Rates', 'Group ID : ' || C_Tax_Info_Rec.group_id );
77 Debug(l_state_level, 'Update_Rates', 'Tax Code : ' || C_Tax_Info_Rec.tax_name);
78 Debug(l_state_level, 'Update_Rates', 'New Tax Rate : ' || C_Tax_Info_Rec.tax_rate);
79 WriteLog('Processing Tax Code : ' || C_Tax_Info_Rec.tax_name);
80 WriteLog('New Tax Rate : ' || C_Tax_Info_Rec.Tax_rate);
81 -- Debug Messages
82
83 -- Process all the latest certificates records for the group id.
84 For C_Tax_Names_Rec IN C_Tax_Names(C_Tax_Info_Rec.group_id) LOOP
85 --Increment the priority of all records with the current tax name
86 -- Debug Messages
87 Debug(l_state_level, 'Update_Rates', 'Certificate Details ');
88 Debug(l_state_level, 'Update_Rates', 'Vendor Id : ' || C_Tax_Names_Rec.vendor_id);
89 Debug(l_state_level, 'Update_Rates', 'Vendor Site Id : ' || C_Tax_Names_Rec.vendor_site_id );
90 Debug(l_state_level, 'Update_Rates', 'Tax Code : ' || C_Tax_Names_Rec.tax_name);
91 -- Debug Messages
92
93 UPDATE ap_awt_tax_rates
94 SET priority = priority + 1
95 WHERE vendor_id = C_Tax_Names_Rec.vendor_id
96 AND vendor_site_id = C_Tax_Names_Rec.vendor_site_id
97 AND tax_name = C_Tax_Names_Rec.tax_name;
98
99 -- Debug Messages
100 If sql%Found Then
101 Debug(l_state_level, 'Update_Rates',
102 'Incremented the priority by 1 for all the vendor site certificates, for the Tax Code '
103 || C_Tax_Names_Rec.tax_name);
104 End If;
105 -- Debug Messages
106
107 --Fetch the start date of the record which has the current tax name and
108 --is of priority 2
109 -- Update the end date of the record to start date of the new rate - 1
110 UPDATE ap_awt_tax_rates
111 SET end_date = C_Tax_Info_Rec.start_date
112 WHERE tax_rate_id = C_Tax_Names_Rec.tax_rate_id;
113
114 -- Debug Messages
115 If sql%Found Then
116 Debug(l_state_level, 'Update_Rates',
117 'End date the current vendor site certificate for the Tax Code '
118 || C_Tax_Names_Rec.tax_name);
119 End If;
120 -- Debug Messages
121
122 -- Generate a new sequence for the new certificate record.
123 SELECT ap_awt_tax_rates_s.nextval
124 INTO l_new_tax_rate_id
125 FROM dual;
126
127 --Insert a new certificate record for the new record with priority 1
128 Insert Into ap_awt_tax_rates(
129 tax_rate_id
130 ,tax_name
131 ,tax_rate
132 ,rate_type
133 ,start_date
134 ,vendor_id
135 ,vendor_site_id
136 ,certificate_number
137 ,certificate_type
138 ,comments
139 ,priority
140 ,org_id
141 ,last_update_date
142 ,last_updated_by
143 ,last_update_login
144 ,creation_date
145 ,created_by)
146 Values(l_new_tax_rate_id -- tax_rate_id
147 ,C_Tax_Names_Rec.tax_name -- tax_name
148 ,C_Tax_Info_Rec.tax_rate -- tax_rate
149 ,'CERTIFICATE' -- rate_type
150 ,TRUNC(C_Tax_Info_Rec.start_date) -- start_date
151 ,C_Tax_Names_Rec.vendor_id -- vendor_id
152 ,C_Tax_Names_Rec.vendor_site_id -- vendor_site_id
153 ,'CERT' -- certificate_number
154 ,'STANDARD' -- certificate_type
155 ,'TAX RATE CHANGE' -- comments
156 ,0 -- priority
157 ,C_Tax_Names_Rec.org_id -- org_id
158 ,sysdate -- last_update_date
159 ,nvl(fnd_profile.VALUE('USER_ID'), 0) -- last_update_by
160 ,nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) -- last_update_login
161 ,sysdate -- creation_date
162 ,nvl(fnd_profile.VALUE('USER_ID'), 0)); -- created_by
163
164 -- Debug Messages
165 If sql%Found Then
166 Debug(l_state_level, 'Update_Rates',
167 'Inserted a new Certificate record for the vendor site for the new rate '
168 || C_Tax_Info_Rec.Tax_rate);
169 WriteLog('Details of the New Certificate inserted');
170 WriteLog('Vendor Id : ' || C_Tax_Names_Rec.vendor_id);
171 WriteLog('Vendor Site Id : ' || C_Tax_Names_Rec.vendor_site_id);
172 WriteLog('Tax Code : ' || C_Tax_Names_Rec.tax_name);
173 WriteLog('Tax Rate : ' || C_Tax_Info_Rec.tax_rate);
174 WriteLog('Start Date : ' || To_Char(C_Tax_Info_Rec.start_date, 'DD-MON-YYYY'));
175 End If;
176 -- Debug Messages
177
178 --Insert a corrosponding row in the history table
179 Insert Into igi_cis_tax_treatment_h(
180 vendor_id
181 ,vendor_site_id
182 ,tax_rate_id
183 ,old_group_id
184 ,new_group_id
185 ,effective_date
186 ,source_name
187 ,last_update_date
188 ,last_updated_by
189 ,last_update_login
190 ,creation_date
191 ,created_by
192 ,request_id
193 ,program_id
194 ,program_application_id
195 ,program_login_id)
196 Values(C_Tax_Names_Rec.vendor_id -- vendor_id
197 ,C_Tax_Names_Rec.vendor_site_id -- vendor_site_id
198 ,l_new_tax_rate_id -- tax_rate_id
199 ,C_Tax_Info_Rec.group_id -- old_group_id
200 ,C_Tax_Info_Rec.group_id -- new_group_id
201 ,TRUNC(C_Tax_Info_Rec.start_date) -- effective_date
202 ,'TAX RATE CHANGE' -- source_name
203 ,sysdate -- last_update_date
204 ,nvl(fnd_profile.VALUE('USER_ID'), 0) -- last_update_by
205 ,nvl(fnd_profile.VALUE('LAST_UPDATE_LOGIN'), 0) -- last_update_login
206 ,sysdate -- creation_date
207 ,nvl(fnd_profile.VALUE('USER_ID'), 0) -- created_by
208 ,fnd_global.conc_request_id -- request_id
209 ,fnd_global.conc_program_id -- program_id
210 ,fnd_global.prog_appl_id -- program_application_id
211 ,fnd_global.conc_login_id); -- program_login_id
212
213 -- Debug Messages
214 If sql%Found Then
215 Debug(l_state_level, 'Update_Rates',
216 'Inserted a new history record for the coresponding new certifcate ');
217 End If;
218 -- Debug Messages
219
220 End Loop; -- C_Tax_Names
221
222 -- Update all the 0 priority records to 1
223 UPDATE ap_awt_tax_rates
224 SET priority = priority + 1
225 WHERE (vendor_id, vendor_site_id, tax_rate_id) in
226 (SELECT atr.vendor_id , atr.vendor_site_id, atr.tax_rate_id
227 FROM ap_awt_tax_rates atr, igi_cis_tax_treatment_h his
228 WHERE new_group_id = C_Tax_Info_Rec.group_id
229 AND atr.tax_rate_id = his.tax_rate_id
230 AND priority = 0 );
231
232 -- Debug Messages
233 If sql%Found Then
234 Debug(l_state_level, 'Update_Rates',
235 'Updated the 0 priority certificates just inserted to 1');
236 End If;
237 -- Debug Messages
238
239 End Loop; -- C_Tax_Info Loop
240 Retcode := 0;
241 Commit;
242 Exception
243 WHEN Others Then
244 Errbuf := 'Error Message: ' || sqlerrm || ' Error Code: ' || to_char(sqlcode);
245 Debug(l_state_level, 'Update_Rates',errbuf);
246 Retcode := 2;
247 End;
248 End IGI_CIS2007_CERT_PKG;