[Home] [Help]
PACKAGE BODY: APPS.AP_UPDATE_1099_AWT_DISTS_PKG
Source
1 PACKAGE BODY Ap_Update_1099_Awt_Dists_Pkg AS
2 /* $Header: apupawtb.pls 120.0 2003/06/13 17:56:21 isartawi noship $ */
3
4 ----------------------------------------------------------------------------
5 FUNCTION Get_Income_Tax_Region(
6 P_invoice_id IN NUMBER,
7 P_calling_sequence IN VARCHAR2 )
8 RETURN VARCHAR2 IS
9 --
10 l_result BOOLEAN;
11 l_state VARCHAR2(10);
12 l_debug_info VARCHAR2(240);
13 l_current_calling_sequence VARCHAR2(200);
14 --
15 BEGIN
16 l_current_calling_sequence := P_calling_sequence||'->'||
17 'Get_Income_Tax_Region';
18 --
19 l_debug_info := 'Get Income Tax Region';
20 --
21 SELECT SUBSTR(state, 1, 10)
22 INTO l_state
23 FROM po_vendor_sites PVS,
24 ap_invoices AI
25 WHERE AI.invoice_id = P_invoice_id
26 AND PVS.vendor_site_id = AI.vendor_site_id;
27 --
28 return (l_state);
29 --
30 EXCEPTION
31 WHEN OTHERS THEN
32 IF (SQLCODE <> -20001 ) THEN
33 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
34 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
35 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
36 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
37 FND_MESSAGE.SET_TOKEN('PARAMETERS','P_invoice_id: '
38 ||TO_CHAR(P_invoice_id)
39 );
40 END IF;
41 APP_EXCEPTION.RAISE_EXCEPTION;
42 --
43 END Get_Income_Tax_Region;
44 --
45 PROCEDURE Upgrade(
46 errbuf OUT NOCOPY VARCHAR2,
47 retcode OUT NOCOPY NUMBER,
48 P_calling_sequence IN VARCHAR2 ) IS
49 --
50 l_request_id NUMBER;
51 l_login_id NUMBER;
52 l_user_id NUMBER;
53 l_program_application_id NUMBER;
54 l_program_id NUMBER;
55 l_result BOOLEAN;
56 l_debug_info VARCHAR2(240);
57 l_date VARCHAR2(10);
58 l_Start_dt VARCHAR2(10);
59 l_Start_date DATE;
60 l_mm NUMBER;
61 l_yyyy NUMBER;
62 l_count INTEGER := 0;
63 l_total_count INTEGER := 0;
64 --
65 l_enable_1099_on_awt_flag ap_system_parameters.enable_1099_on_awt_flag%TYPE;
66 l_federal_reportable_flag po_vendors.federal_reportable_flag%TYPE;
67 l_type_1099 ap_invoice_distributions.type_1099%TYPE;
68 l_combined_filing_flag ap_system_parameters.combined_filing_flag%TYPE;
69 l_income_tax_region_asp ap_system_parameters.income_tax_region%TYPE;
70 l_income_tax_region_pvs ap_system_parameters.income_tax_region%TYPE;
71 l_income_tax_region ap_system_parameters.income_tax_region%TYPE;
72 l_income_tax_region_flag ap_system_parameters.income_tax_region_flag%TYPE;
73 --
74 l_commit_size INTEGER := 10000;
75 l_min_invoice_distribution_id NUMBER;
76 l_max_invoice_distribution_id NUMBER;
77
78 l_current_calling_sequence VARCHAR2(2000);
79 BEGIN
80 --
81 l_current_calling_sequence := P_calling_sequence||'->'||
82 'Ap_Update_1099_Awt_Dists_Pkg.Upgrade';
83 --
84 SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD' )
85 INTO l_date
86 FROM dual;
87 --
88 l_debug_info := 'Sysdate '||l_date;
89 --
90 l_mm := to_number(SUBSTR(l_date, 6, 2));
91 l_yyyy := to_number(substr(l_date, 1, 4));
92 if l_mm < 3 then
93 l_yyyy := l_yyyy -1;
94 end if;
95 --
96 l_start_dt := to_char(l_yyyy)||'/01/01';
97 --
98 l_debug_info := 'l_start_dt '||l_start_dt;
99 --
100 l_start_date := FND_DATE.CANONICAL_TO_DATE(l_start_dt);
101 --
102 l_debug_info := 'Get Profiles';
103 --
104 l_user_id := FND_GLOBAL.user_id;
105 l_request_id := FND_GLOBAL.conc_request_id;
106 l_login_id := FND_GLOBAL.login_id;
107 l_program_application_id := FND_GLOBAL.prog_appl_id;
108 l_program_id := FND_GLOBAL.conc_program_id;
109
110 l_debug_info := 'Get Info from Ap System Parameters';
111 --
112 SELECT NVL(enable_1099_on_awt_flag, 'N'),
113 combined_filing_flag,
114 income_tax_region_flag,
115 income_tax_region
116 INTO l_enable_1099_on_awt_flag,
117 l_combined_filing_flag,
118 l_income_tax_region_flag,
119 l_income_tax_region
120 FROM ap_system_parameters;
121 --
122 SELECT NVL(MIN(invoice_distribution_id),0),
123 NVL(MAX(invoice_distribution_id),0)
124 INTO l_min_invoice_distribution_id, l_max_invoice_distribution_id
125 FROM ap_invoice_distributions ID,
126 ap_invoices AI,
127 po_vendor_sites PVS,
128 po_vendors PV
129 WHERE ID.invoice_id = AI.invoice_id
130 AND AI.vendor_site_id = PVS.vendor_site_id
131 AND PV.vendor_id = PVS.vendor_id
132 AND PV.federal_reportable_flag = 'Y'
133 AND PVS.tax_reporting_site_flag = 'Y'
134 AND NVL(ID.type_1099, 'DUMMY') <> 'MISC4'
135 AND ID.line_type_lookup_code = 'AWT'
136 AND (ID.invoice_id IN (SELECT IP.invoice_id
137 FROM AP_Invoice_Payments IP
138 WHERE ID.invoice_id = IP.invoice_id
139 AND nvl(IP.accounting_date,sysdate)
140 BETWEEN l_start_date AND sysdate
141 )
142 OR
143 ID.invoice_id IN (SELECT AI.invoice_id
144 FROM Ap_Invoices AI
145 WHERE ID.invoice_id = AI.invoice_id
146 AND NVL(AI.PAYMENT_STATUS_FLAG, 'N') <> 'Y'
147 )
148 );
149 --
150 WHILE ( l_min_invoice_distribution_id <= l_max_invoice_distribution_id AND
151 l_max_invoice_distribution_id <> 0)
152 --
153 LOOP
154 l_debug_info := 'Update TYPE_1099 on AID';
155 --
156 UPDATE ap_invoice_distributions ID
157 SET ID.type_1099 = 'MISC4',
158 ID.income_tax_Region =
159 decode(l_combined_filing_flag, 'Y',
160 decode(l_income_tax_region_flag, 'Y',
161 Ap_Update_1099_Awt_Dists_Pkg.Get_Income_tax_region(
162 ID.invoice_id,
163 l_current_calling_sequence),
164 l_income_tax_region
165 ), NULL
166 ),
167 ID.last_update_date = SYSDATE,
168 ID.last_updated_by = l_user_id,
169 ID.last_update_login = l_login_id,
170 ID.program_update_date = SYSDATE,
171 ID.program_application_id = l_program_application_id,
172 ID.program_id = l_program_id,
173 ID.request_id = l_request_id
174 WHERE ID.invoice_id IN (
175 SELECT AI.invoice_id
176 FROM ap_invoices AI,
177 po_vendors PV,
178 po_vendor_sites PVS
179 WHERE AI.vendor_id = PV.vendor_id
180 AND AI.vendor_site_id = PVS.vendor_site_id
181 AND PV.vendor_id = PVS.vendor_id
182 AND PV.federal_reportable_flag = 'Y'
183 AND PVS.tax_reporting_site_flag = 'Y'
184 )
185 AND (ID.invoice_id IN (SELECT IP.invoice_id
186 FROM AP_Invoice_Payments IP
187 WHERE ID.invoice_id = IP.invoice_id
188 AND nvl(IP.accounting_date,sysdate)
189 BETWEEN l_start_date AND sysdate
190 )
191 OR
192 ID.invoice_id IN (SELECT AI.invoice_id
193 FROM Ap_Invoices AI
194 WHERE ID.invoice_id = AI.invoice_id
195 AND NVL(AI.PAYMENT_STATUS_FLAG, 'N') <> 'Y'
196 )
197 )
198 AND ID.line_type_lookup_code = 'AWT'
199 AND NVL(ID.type_1099, 'DUMMY') <> 'MISC4'
200 AND invoice_distribution_id
201 BETWEEN l_min_invoice_distribution_id
202 AND l_min_invoice_distribution_id + l_commit_size - 1 ;
203 l_count := SQL%ROWCOUNT;
204 --
205 COMMIT;
206 --
207 l_min_invoice_distribution_id := l_min_invoice_distribution_id +
208 l_commit_size;
209 --
210 l_total_count := l_count + l_total_count;
211 END LOOP;
212 AP_Debug_Pkg.Print('Y', ' ');
213 AP_Debug_Pkg.Print('Y', 'Number of Distributions Updated : '
214 || TO_CHAR(l_total_count));
215 AP_Debug_Pkg.Print('Y', ' ');
216
217 EXCEPTION
218 --
219 WHEN OTHERS THEN
220 IF (SQLCODE <> -20001 ) THEN
221 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
222 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM );
223 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence );
224 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info );
225 END IF;
226 --
227 errbuf := FND_MESSAGE.GET;
228 retcode := 2;
229 --
230 END Upgrade;
231 --
232 END Ap_Update_1099_Awt_Dists_Pkg;