[Home] [Help]
PACKAGE BODY: APPS.AP_1099_UTILITIES_PKG
Source
1 PACKAGE BODY AP_1099_UTILITIES_PKG AS
2 /* $Header: ap1099utlb.pls 120.16.12020000.5 2012/12/31 07:11:31 nbshaik ship $ */
3
4 PROCEDURE insert_1099_data
5 ( p_calling_module IN varchar2,
6 p_sob_id IN number,
7 p_tax_entity_id IN number,
8 p_combined_flag IN varchar2,
9 p_start_date IN date,
10 p_end_date IN date,
11 p_vendor_id IN number,
12 p_query_driver IN varchar2,
13 p_min_reportable_flag IN varchar2,
14 p_federal_reportable_flag in varchar2,
15 p_region varchar2) is
16
17 l_chart_of_accounts_id number;
18 l_app_column_name varchar2(25);
19
20 l_dynamic_sql1 varchar2(4000);
21 l_dynamic_sql2 varchar2(4000);
22 l_dynamic_sql2_1 varchar2(4000);
23 l_dynamic_sql3 varchar2(4000);
24
25 l_id_start_date date;
26 l_id_end_date date;
27
28 l_org_id number; -- Bug 4946930
29 x number;
30
31 begin
32
33 l_org_id := mo_global.get_current_org_id;
34 l_id_start_date := p_start_date-1;
35 l_id_end_date := p_end_date+1;
36
37
38 SELECT fnd.application_column_name, gl.chart_of_accounts_id
39 INTO l_app_column_name, l_chart_of_accounts_id
40 FROM fnd_segment_attribute_values fnd, gl_sets_of_books gl
41 WHERE segment_attribute_type = 'GL_BALANCING'
42 AND fnd.attribute_value = 'Y'
43 AND fnd.id_flex_code = 'GL#'
44 AND fnd.id_flex_num = gl.chart_of_accounts_id
45 AND gl.set_of_books_id = p_sob_id;
46
47
48 l_dynamic_sql1:=
49 'INSERT INTO ap_1099_tape_data '
50 || '(vendor_id,region_code,'
51 || 'misc1,misc2,misc3,misc4,misc5,'
52 || ' misc6,misc7,misc8,misc9,misc10,'
53 || 'misc13, misc14, misc15aNT, misc15aT, misc15b,org_id) ' -- Bug 4946930
54 || 'SELECT P.vendor_id,';
55
56 if p_combined_flag = '1' then
57 l_dynamic_sql1 := l_dynamic_sql1 || 'ITR.region_code,';
58 else
59 l_dynamic_sql1 := l_dynamic_sql1 || 'null,';
60 end if;
61 /*Bug11845743 : Removed ABS() function from the below query for all ID.amount columns*/
62 l_dynamic_sql1 := l_dynamic_sql1
63 || 'round(sum(decode(ID.type_1099,''MISC1'','
64 || '(decode(I.cancelled_amount,'
65 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
66 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
67 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
68 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
69 || 'null,1,0,1,I.cancelled_amount),'
70 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
71 || ' INV_NET_AMT.netamount ))'
72 -- Bug 5768112. Backing out the fix of bug 5620442
73 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
74 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
75 || 'round(sum(decode(ID.type_1099,''MISC2'','
76 || '(decode(I.cancelled_amount,'
77 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
78 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
79 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
80 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
81 || 'null,1,0,1,I.cancelled_amount),'
82 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
83 || ' INV_NET_AMT.netamount ))'
84 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
85 || 'round(sum(decode(ID.type_1099,''MISC3'','
86 || '(decode(I.cancelled_amount,'
87 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
88 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
89 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
90 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
91 || 'null,1,0,1,I.cancelled_amount),'
92 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
93 || ' INV_NET_AMT.netamount ))'
94 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
95 || '-1*(round(sum(decode(ID.type_1099,''MISC4'','
96 || '(decode(I.cancelled_amount,'
97 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
98 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
99 || 'ID.accounting_date,0, ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
100 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
101 || 'null,1,0,1,I.cancelled_amount),'
102 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
103 || ' INV_NET_AMT.netamount ))'
104 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2)),' -- Bug 5768112
105 || 'round(sum(decode(ID.type_1099,''MISC5'','
106 || '(decode(I.cancelled_amount,'
107 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
108 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
109 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
110 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
111 || 'null,1,0,1,I.cancelled_amount),'
112 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
113 || ' INV_NET_AMT.netamount ))'
114 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
115 || 'round(sum(decode(ID.type_1099,''MISC6'','
116 || '(decode(I.cancelled_amount,'
117 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
118 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
119 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
120 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
121 || 'null,1,0,1,I.cancelled_amount),'
122 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
123 || ' INV_NET_AMT.netamount ))'
124 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
125 || 'round(sum(decode(ID.type_1099,''MISC7'','
126 || '(decode(I.cancelled_amount,'
127 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
128 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
129 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
130 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
131 || 'null,1,0,1,I.cancelled_amount),'
132 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
133 || ' INV_NET_AMT.netamount ))'
134 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' ; -- Bug 5768112
135
136 l_dynamic_sql2:= 'round(sum(decode(ID.type_1099,''MISC8'','
137 || '(decode(I.cancelled_amount,'
138 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
139 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
140 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
141 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
142 || 'null,1,0,1,I.cancelled_amount),'
143 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
144 || ' INV_NET_AMT.netamount ))'
145 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
146 || 'round(sum(decode(ID.type_1099,''MISC9'','
147 || '(decode(I.cancelled_amount,'
148 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
149 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
150 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
151 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
152 || 'null,1,0,1,I.cancelled_amount),'
153 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
154 || ' INV_NET_AMT.netamount ))'
155 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
156 || 'round(sum(decode(ID.type_1099,''MISC10'','
157 || '(decode(I.cancelled_amount,'
158 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
159 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
160 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
161 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
162 || 'null,1,0,1,I.cancelled_amount),'
163 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
164 || ' INV_NET_AMT.netamount ))'
165 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
166 || 'round(sum(decode(ID.type_1099,''MISC13'','
167 || '(decode(I.cancelled_amount,'
168 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
169 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
170 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
171 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
172 || 'null,1,0,1,I.cancelled_amount),'
173 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
174 || ' INV_NET_AMT.netamount ))'
175 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
176 || 'round(sum(decode(ID.type_1099,''MISC14'','
177 || '(decode(I.cancelled_amount,'
178 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
179 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
180 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
181 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
182 || 'null,1,0,1,I.cancelled_amount),'
183 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
184 || ' INV_NET_AMT.netamount ))'
185 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),'; -- Bug 5768112
186
187 l_dynamic_sql2_1 := 'round(sum(decode(ID.type_1099,''MISC15a NT'','
188 || '(decode(I.cancelled_amount,'
189 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
190 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
191 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
192 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
193 || 'null,1,0,1,I.cancelled_amount),'
194 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
195 || ' INV_NET_AMT.netamount ))'
196 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
197 || 'round(sum(decode(ID.type_1099,''MISC15a T'','
198 || '(decode(I.cancelled_amount,'
199 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
200 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
201 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
202 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
203 || 'null,1,0,1,I.cancelled_amount),'
204 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
205 || ' INV_NET_AMT.netamount ))'
206 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
207 || 'round(sum(decode(ID.type_1099,''MISC15b'','
208 || '(decode(I.cancelled_amount,'
209 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
210 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
211 || 'ID.accounting_date,0,ID.amount)))/' /*Bug11845743: ABS(ID.amount)*/
212 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
213 || 'null,1,0,1,I.cancelled_amount),'
214 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
215 || ' INV_NET_AMT.netamount ))'
216 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
217 || l_org_id -- Bug 4946930
218 || ' FROM ap_income_tax_regions ITR, '
219 || 'ap_reporting_entity_lines_all REL, '
220 || 'po_vendors P, '
221 || 'ce_bank_accounts ABA, ' --for bug 6275528 - replaced ap_bank_accounts_all with ce_bank_Accounts
222 --as ap_bank_accounts_All is obsolete in R12.
223 || 'ce_bank_acct_uses_all BAU, ' -- Bug 6604204. Included table to join AP_CHECKS and
224 -- CE_BANK_ACCOUNTS table.
225 || 'ap_checks_all AC, '
226 || 'ap_invoices_all I, '
227 || 'gl_code_combinations CC, '
228 || 'ap_invoice_distributions_all ID, '
229 || 'ap_invoice_payments_all IP,' --Bug 6064614 --for bug 6275528 - changed ap_invoice_payments
230 --to ap_invoice_payments_all.
231 || '( '
232 || ' select invoice_id, count(*) ip_count '
233 || ' from ap_invoice_payments_all aip '
234 || ' where aip.amount <> 0 '
235 || ' and aip.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
236 || ' group by invoice_id '
237 || ' ) ip2, '
238 || '( select AI.invoice_id , nvl(AIL1.amount+AI.invoice_amount , AI.invoice_amount) as netamount ' --Bug5943123
239 || ' from ap_invoices_all AI, ( select AIL.invoice_id, SUM(AIL.amount) as amount '
240 || ' FROM ap_invoice_lines_all AIL ' --Bug6064614
241 || ' WHERE (nvl(AIL.invoice_includes_prepay_flag,''N'') = ''N'' ' --Bug6052333
242 || ' AND (AIL.line_type_lookup_code = ''PREPAY'' '
243 || ' OR (AIL.line_type_lookup_code =''TAX'' '
244 || ' AND AIL.prepay_invoice_id IS NOT NULL '
245 || ' AND AIL.prepay_line_number IS NOT NULL))) '
246 || ' OR AIL.line_type_lookup_code = ''AWT'' '
247 || ' GROUP by AIL.invoice_id ) AIL1 ' --Bug5943123
248 || ' where AI.invoice_id = AIL1.invoice_id (+) ) INV_NET_AMT '
249 || 'WHERE ID.income_tax_region = ITR.region_short_name (+) '
250 || 'AND P.vendor_id=I.vendor_id '
251 || 'AND (AC.void_date is null '
252 || ' OR AC.void_date NOT BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''') ' --4480766, 8925235
253 || 'AND I.invoice_id=IP.invoice_id '
254 || 'AND IP.invoice_id = ip2.invoice_id(+) '
255 || 'AND I.invoice_id=ID.invoice_id '
256 || 'AND INV_NET_AMT.invoice_id = I.invoice_id '
257 || 'AND IP.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
258 --|| 'AND ID.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' ' --Bug 11845743 --Bug12940711
259 || 'AND ID.type_1099 is not null '
260 ---- || 'AND AC.bank_account_id = ABA.bank_account_id ' Commeted this for bug 6275528 - as in ap_checks_all
261 ---bank_account_id is not getting stamped in R12.bank_account_anme is there so we will use it.
262 --|| 'AND AC.bank_account_name = ABA.bank_account_name ' --added for bug 6275528 for the aobve explained reason.
263 -- Commented for Bug 6604204.
264 || 'AND AC.ce_bank_acct_use_id = BAU.bank_acct_use_id ' -- Bug 6604204. Please refer bug for details.
265 || 'AND BAU.bank_account_id = ABA.bank_account_id ' -- Bug 6604204. Please refer bug for details.
266 || 'AND AC.org_id = mo_global.get_current_org_id ' --added for bug 6275528
267 || 'AND IP.check_id = AC.check_id '
268 || 'AND REL.tax_entity_id = ' || p_tax_entity_id|| ' '
269 || 'AND CC.chart_of_accounts_id = ' || l_chart_of_accounts_id || ' ';
270
271 if p_calling_module = 'PAYMENTS REPORT' and nvl(p_federal_reportable_flag,'N') = 'N' then
272 null;
273 else -- for all other situations we want federal reportable vendors only
274 l_dynamic_sql3:= 'AND P.federal_reportable_flag = ''Y'' ';
275 end if;
276
277 if p_vendor_id is not null then
278 l_dynamic_sql3 := l_dynamic_sql3 || 'AND P.vendor_id = '||p_vendor_id||' ';
279 end if;
280
281 if p_query_driver = 'INV' then
282 l_dynamic_sql3 := l_dynamic_sql3 || 'AND ID.dist_code_combination_id = CC.code_combination_id ';
283 else
284 l_dynamic_sql3 := l_dynamic_sql3 || 'AND ABA.asset_code_combination_id = CC.code_combination_id ';
285 end if;
286
287 if p_region is not null then
288 l_dynamic_sql3 := l_dynamic_sql3 || 'AND id.income_tax_region = '''|| p_region ||''' ';
289 end if;
290
291 if (l_app_column_name LIKE 'SEGMENT1') THEN
292 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment1 = REL.balancing_segment_value ';
293 elsif (l_app_column_name LIKE 'SEGMENT2') THEN
294 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment2 = REL.balancing_segment_value ';
295 elsif (l_app_column_name LIKE 'SEGMENT3') THEN
296 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment3 = REL.balancing_segment_value ';
297 elsif (l_app_column_name LIKE 'SEGMENT4') THEN
298 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment4 = REL.balancing_segment_value ';
299 elsif (l_app_column_name LIKE 'SEGMENT5') THEN
300 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment5 = REL.balancing_segment_value ';
301 elsif (l_app_column_name LIKE 'SEGMENT6') THEN
302 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment6 = REL.balancing_segment_value ';
303 elsif (l_app_column_name LIKE 'SEGMENT7') THEN
304 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment7 = REL.balancing_segment_value ';
305 elsif (l_app_column_name LIKE 'SEGMENT8') THEN
306 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment8 = REL.balancing_segment_value ';
307 elsif (l_app_column_name LIKE 'SEGMENT9') THEN
308 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment9 = REL.balancing_segment_value ';
309 elsif (l_app_column_name LIKE 'SEGMENT10') THEN
310 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment10 = REL.balancing_segment_value ';
311 elsif (l_app_column_name LIKE 'SEGMENT11') THEN
312 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment11 = REL.balancing_segment_value ';
313 elsif (l_app_column_name LIKE 'SEGMENT12') THEN
314 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment12 = REL.balancing_segment_value ';
315 elsif (l_app_column_name LIKE 'SEGMENT13') THEN
316 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment13 = REL.balancing_segment_value ';
317 elsif (l_app_column_name LIKE 'SEGMENT14') THEN
318 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment14 = REL.balancing_segment_value ';
319 elsif (l_app_column_name LIKE 'SEGMENT15') THEN
320 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment15 = REL.balancing_segment_value ';
321 elsif (l_app_column_name LIKE 'SEGMENT16') THEN
322 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment16 = REL.balancing_segment_value ';
323 elsif (l_app_column_name LIKE 'SEGMENT17') THEN
324 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment17 = REL.balancing_segment_value ';
325 elsif (l_app_column_name LIKE 'SEGMENT18') THEN
326 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment18 = REL.balancing_segment_value ';
327 elsif (l_app_column_name LIKE 'SEGMENT19') THEN
328 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment19 = REL.balancing_segment_value ';
329 elsif (l_app_column_name LIKE 'SEGMENT20') THEN
330 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment20 = REL.balancing_segment_value ';
331 elsif (l_app_column_name LIKE 'SEGMENT21') THEN
332 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment21 = REL.balancing_segment_value ';
333 elsif (l_app_column_name LIKE 'SEGMENT22') THEN
334 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment22 = REL.balancing_segment_value ';
335 elsif (l_app_column_name LIKE 'SEGMENT23') THEN
336 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment23 = REL.balancing_segment_value ';
337 elsif (l_app_column_name LIKE 'SEGMENT24') THEN
338 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment24 = REL.balancing_segment_value ';
339 elsif (l_app_column_name LIKE 'SEGMENT25') THEN
340 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment25 = REL.balancing_segment_value ';
341 elsif (l_app_column_name LIKE 'SEGMENT26') THEN
342 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment26 = REL.balancing_segment_value ';
343 elsif (l_app_column_name LIKE 'SEGMENT27') THEN
344 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment27 = REL.balancing_segment_value ';
345 elsif (l_app_column_name LIKE 'SEGMENT28') THEN
346 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment28 = REL.balancing_segment_value ';
347 elsif (l_app_column_name LIKE 'SEGMENT29') THEN
348 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment29 = REL.balancing_segment_value ';
349 elsif (l_app_column_name LIKE 'SEGMENT30') THEN
350 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment30 = REL.balancing_segment_value ';
351 end if;
352
353 if p_combined_flag = '1' then
354 l_dynamic_sql3 := l_dynamic_sql3 || 'GROUP BY ITR.region_code, P.vendor_id';
355 else
356 l_dynamic_sql3 := l_dynamic_sql3 || 'GROUP BY P.vendor_id';
357 end if;
358
359 declare
360 stemp VARCHAR2(80);
361 nlength NUMBER := 1;
362
363 BEGIN
364
365 WHILE(length(l_dynamic_sql1) >= nlength)
366 LOOP
367 stemp := substrb(l_dynamic_sql1, nlength, 80);
368 fnd_file.put_line(FND_FILE.LOG, stemp);
369 nlength := (nlength + 80);
370 END LOOP;
371 end;
372
373 declare
374 stemp VARCHAR2(80);
375 nlength NUMBER := 1;
376
377 BEGIN
378
379 WHILE(length(l_dynamic_sql2) >= nlength)
380 LOOP
381 stemp := substrb(l_dynamic_sql2, nlength, 80);
382 fnd_file.put_line(FND_FILE.LOG, stemp);
383 nlength := (nlength + 80);
384 END LOOP;
385 end;
386
387 declare
388 stemp VARCHAR2(80);
389 nlength NUMBER := 1;
390
391 BEGIN
392
393 WHILE(length(l_dynamic_sql2_1) >= nlength)
394 LOOP
395 stemp := substrb(l_dynamic_sql2_1, nlength, 80);
396 fnd_file.put_line(FND_FILE.LOG, stemp);
397 nlength := (nlength + 80);
398 END LOOP;
399 end;
400
401 declare
402 stemp VARCHAR2(80);
403 nlength NUMBER := 1;
404
405 BEGIN
406
407 WHILE(length(l_dynamic_sql3) >= nlength)
408 LOOP
409 stemp := substrb(l_dynamic_sql3, nlength, 80);
410 fnd_file.put_line(FND_FILE.LOG, stemp);
411 nlength := (nlength + 80);
412 END LOOP;
413 end;
414
415 execute immediate l_dynamic_sql1 || l_dynamic_sql2 || l_dynamic_sql2_1 || l_dynamic_sql3;
416
417 select count(*) into x from ap_1099_tape_data;
418 fnd_file.put_line(FND_FILE.LOG, to_char(x));
419
420
421 end insert_1099_data;
422
423 -- Added for backup withholding enhancement.
424 -- Please refer bug8947583 for details.
425
426 PROCEDURE do_awt_withholding_update
427 ( p_calling_module IN varchar2,
428 p_sob_id IN number,
429 p_tax_entity_id IN number,
430 p_combined_flag IN varchar2,
431 p_start_date IN date,
432 p_end_date IN date,
433 p_vendor_id IN number,
434 p_query_driver IN varchar2,
435 p_min_reportable_flag IN varchar2,
436 p_federal_reportable_flag in varchar2,
437 p_region varchar2) is
438
439 l_chart_of_accounts_id number;
440 l_app_column_name varchar2(25);
441
442 l_dynamic_sql1 varchar2(4000);
443 l_dynamic_sql2 varchar2(4000);
444 l_dynamic_sql2_1 varchar2(4000);
445 l_dynamic_sql3 varchar2(4000);
446
447 l_id_start_date date;
448 l_id_end_date date;
449
450 l_org_id number; -- Bug 4946930
451 x number;
452
453 TYPE r_backup_awt_info IS RECORD(
454 vendor_id AP_1099_TAPE_DATA_ALL.vendor_id%TYPE,
455 region_code AP_1099_TAPE_DATA_ALL.region_code%TYPE,
456 misc1 AP_1099_TAPE_DATA_ALL.misc1%TYPE,
457 misc2 AP_1099_TAPE_DATA_ALL.misc2%TYPE,
458 misc3 AP_1099_TAPE_DATA_ALL.misc3%TYPE,
459 misc4 AP_1099_TAPE_DATA_ALL.misc4%TYPE,
460 misc5 AP_1099_TAPE_DATA_ALL.misc5%TYPE,
461 misc6 AP_1099_TAPE_DATA_ALL.misc6%TYPE,
462 misc7 AP_1099_TAPE_DATA_ALL.misc7%TYPE,
463 misc8 AP_1099_TAPE_DATA_ALL.misc8%TYPE,
464 misc9 AP_1099_TAPE_DATA_ALL.misc9%TYPE,
465 misc10 AP_1099_TAPE_DATA_ALL.misc10%TYPE,
466 misc13 AP_1099_TAPE_DATA_ALL.misc13%TYPE,
467 misc14 AP_1099_TAPE_DATA_ALL.misc14%TYPE,
468 misc15aNT AP_1099_TAPE_DATA_ALL.misc15aNT%TYPE,
469 misc15aT AP_1099_TAPE_DATA_ALL.misc15aT%TYPE,
470 misc15b AP_1099_TAPE_DATA_ALL.misc15b%TYPE
471 ) ;
472
473 l_backup_awt_rec r_backup_awt_info ;
474
475 TYPE c_backup_awt IS REF CURSOR;
476
477 c_awt c_backup_awt;
478
479 begin
480
481 l_org_id := mo_global.get_current_org_id;
482 l_id_start_date := p_start_date-1;
483 l_id_end_date := p_end_date+1;
484
485
486 SELECT fnd.application_column_name, gl.chart_of_accounts_id
487 INTO l_app_column_name, l_chart_of_accounts_id
488 FROM fnd_segment_attribute_values fnd, gl_sets_of_books gl
489 WHERE segment_attribute_type = 'GL_BALANCING'
490 AND fnd.attribute_value = 'Y'
491 AND fnd.id_flex_code = 'GL#'
492 AND fnd.id_flex_num = gl.chart_of_accounts_id
493 AND gl.set_of_books_id = p_sob_id;
494
495
496 l_dynamic_sql1:=
497 'SELECT P.vendor_id,';
498
499 if p_combined_flag = '1' then
500 l_dynamic_sql1 := l_dynamic_sql1 || 'ITR.region_code,';
501 else
502 l_dynamic_sql1 := l_dynamic_sql1 || 'null,';
503 end if;
504 /*Bug12940711 : Removed ABS() function from the below query for all ID.amount columns*/
505 l_dynamic_sql1 := l_dynamic_sql1
506 || 'round(sum(decode(ID.type_1099,''MISC1'','
507 || '(decode(I.cancelled_amount,'
508 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
509 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
510 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
511 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
512 || 'null,1,0,1,I.cancelled_amount),'
513 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
514 || ' INV_NET_AMT.netamount ))'
515 -- Bug 5768112. Backing out the fix of bug 5620442
516 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
517 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
518 || 'round(sum(decode(ID.type_1099,''MISC2'','
519 || '(decode(I.cancelled_amount,'
520 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
521 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
522 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
523 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
524 || 'null,1,0,1,I.cancelled_amount),'
525 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
526 || ' INV_NET_AMT.netamount ))'
527 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
528 || 'round(sum(decode(ID.type_1099,''MISC3'','
529 || '(decode(I.cancelled_amount,'
530 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
531 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
532 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
533 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
534 || 'null,1,0,1,I.cancelled_amount),'
535 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
536 || ' INV_NET_AMT.netamount ))'
537 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
538 || '-1*(round(sum(decode(ID.type_1099,''MISC4'','
539 || '(decode(I.cancelled_amount,'
540 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
541 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
542 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
543 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
544 || 'null,1,0,1,I.cancelled_amount),'
545 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
546 || ' INV_NET_AMT.netamount ))'
547 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2)),' -- Bug 5768112
548 || 'round(sum(decode(ID.type_1099,''MISC5'','
549 || '(decode(I.cancelled_amount,'
550 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
551 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
552 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
553 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
554 || 'null,1,0,1,I.cancelled_amount),'
555 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
556 || ' INV_NET_AMT.netamount ))'
557 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
558 || 'round(sum(decode(ID.type_1099,''MISC6'','
559 || '(decode(I.cancelled_amount,'
560 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
561 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
562 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
563 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
564 || 'null,1,0,1,I.cancelled_amount),'
565 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
566 || ' INV_NET_AMT.netamount ))'
567 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
568 || 'round(sum(decode(ID.type_1099,''MISC7'','
569 || '(decode(I.cancelled_amount,'
570 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
571 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
572 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
573 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
574 || 'null,1,0,1,I.cancelled_amount),'
575 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
576 || ' INV_NET_AMT.netamount ))'
577 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' ; -- Bug 5768112
578
579 l_dynamic_sql2:= 'round(sum(decode(ID.type_1099,''MISC8'','
580 || '(decode(I.cancelled_amount,'
581 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
582 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
583 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
584 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
585 || 'null,1,0,1,I.cancelled_amount),'
586 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
587 || ' INV_NET_AMT.netamount ))'
588 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
589 || 'round(sum(decode(ID.type_1099,''MISC9'','
590 || '(decode(I.cancelled_amount,'
591 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
592 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
593 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
594 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
595 || 'null,1,0,1,I.cancelled_amount),'
596 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
597 || ' INV_NET_AMT.netamount ))'
598 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
599 || 'round(sum(decode(ID.type_1099,''MISC10'','
600 || '(decode(I.cancelled_amount,'
601 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
602 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
603 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
604 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
605 || 'null,1,0,1,I.cancelled_amount),'
606 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
607 || ' INV_NET_AMT.netamount ))'
608 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
609 || 'round(sum(decode(ID.type_1099,''MISC13'','
610 || '(decode(I.cancelled_amount,'
611 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
612 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
613 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
614 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
615 || 'null,1,0,1,I.cancelled_amount),'
616 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
617 || ' INV_NET_AMT.netamount ))'
618 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
619 || 'round(sum(decode(ID.type_1099,''MISC14'','
620 || '(decode(I.cancelled_amount,'
621 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
622 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
623 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
624 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
625 || 'null,1,0,1,I.cancelled_amount),'
626 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
627 || ' INV_NET_AMT.netamount ))'
628 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),'; -- Bug 5768112
629
630 l_dynamic_sql2_1 := 'round(sum(decode(ID.type_1099,''MISC15a NT'','
631 || '(decode(I.cancelled_amount,'
632 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
633 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
634 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
635 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
636 || 'null,1,0,1,I.cancelled_amount),'
637 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
638 || ' INV_NET_AMT.netamount ))'
639 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
640 || 'round(sum(decode(ID.type_1099,''MISC15a T'','
641 || '(decode(I.cancelled_amount,'
642 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
643 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
644 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
645 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
646 || 'null,1,0,1,I.cancelled_amount),'
647 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
648 || ' INV_NET_AMT.netamount ))'
649 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2),' -- Bug 5768112
650 || 'round(sum(decode(ID.type_1099,''MISC15b'','
651 || '(decode(I.cancelled_amount,'
652 || 'null,ID.amount,decode(greatest(ID.accounting_date,'''|| l_id_end_date||''' ),'
653 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
654 || 'ID.accounting_date,0,ID.amount)))/' /*Bug12940711: ABS(ID.amount)*/
655 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
656 || 'null,1,0,1,I.cancelled_amount),'
657 || ' decode( INV_NET_AMT.netamount ,0,1,' /*Bug5943123 This change is done in almost 15 places in this same pls*/
658 || ' INV_NET_AMT.netamount ))'
659 || ' *decode(IP.amount, 0, decode(ip2.ip_count, null, 1, 0), IP.amount)),0)),2)' -- Bug 5768112
660 || ' FROM ap_income_tax_regions ITR, '
661 || 'ap_reporting_entity_lines_all REL, '
662 || 'po_vendors P, '
663 || 'ce_bank_accounts ABA, ' --for bug 6275528 - replaced ap_bank_accounts_all with ce_bank_Accounts
664 --as ap_bank_accounts_All is obsolete in R12.
665 || 'ce_bank_acct_uses_all BAU, ' -- Bug 6604204. Included table to join AP_CHECKS and
666 -- CE_BANK_ACCOUNTS table.
667 || 'ap_checks_all AC, '
668 || 'ap_invoices_all I, '
669 || 'gl_code_combinations CC, '
670 || 'ap_invoice_distributions_all ID, '
671 || 'ap_invoice_payments_all IP,' --Bug 6064614 --for bug 6275528 - changed ap_invoice_payments
672 --to ap_invoice_payments_all.
673 || '( '
674 || ' select invoice_id, count(*) ip_count '
675 || ' from ap_invoice_payments_all aip '
676 || ' where aip.amount <> 0 '
677 || ' and aip.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
678 || ' group by invoice_id '
679 || ' ) ip2, '
680 || '( select AI.invoice_id , nvl(AIL1.amount+AI.invoice_amount , AI.invoice_amount) as netamount ' --Bug5943123
681 || ' from ap_invoices_all AI, ( select AIL.invoice_id, SUM(AIL.amount) as amount '
682 || ' FROM ap_invoice_lines_all AIL ' --Bug6064614
683 || ' WHERE (nvl(AIL.invoice_includes_prepay_flag,''N'') = ''N'' ' --Bug6052333
684 || ' AND (AIL.line_type_lookup_code = ''PREPAY'' '
685 || ' OR (AIL.line_type_lookup_code =''TAX'' '
686 || ' AND AIL.prepay_invoice_id IS NOT NULL '
687 || ' AND AIL.prepay_line_number IS NOT NULL))) '
688 || ' OR AIL.line_type_lookup_code = ''AWT'' '
689 || ' GROUP by AIL.invoice_id ) AIL1 ' --Bug5943123
690 || ' where AI.invoice_id = AIL1.invoice_id (+) ) INV_NET_AMT '
691 || 'WHERE ID.income_tax_region = ITR.region_short_name (+) '
692 || 'AND P.vendor_id=I.vendor_id '
693 || 'AND (AC.void_date is null '
694 || ' OR AC.void_date NOT BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''') ' --4480766, 8925235
695 || 'AND I.invoice_id=IP.invoice_id '
696 || 'AND IP.invoice_id = ip2.invoice_id(+) '
697 || 'AND I.invoice_id=ID.invoice_id '
698 || 'AND exists '
699 || ' ( '
700 || ' select 1 '
701 || ' from ap_invoice_distributions_all AID '
702 || ' where AID.invoice_id = I.invoice_id '
703 || ' AND AID.type_1099 = ''MISC4'' '
704 || ' ) '
705 || 'AND INV_NET_AMT.invoice_id = I.invoice_id '
706 || 'AND IP.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
707 || 'AND ID.type_1099 is not null '
708 ---- || 'AND AC.bank_account_id = ABA.bank_account_id ' Commeted this for bug 6275528 - as in ap_checks_all
709 ---bank_account_id is not getting stamped in R12.bank_account_anme is there so we will use it.
710 --|| 'AND AC.bank_account_name = ABA.bank_account_name ' --added for bug 6275528 for the aobve explained reason.
711 -- Commented for Bug 6604204.
712 || 'AND AC.ce_bank_acct_use_id = BAU.bank_acct_use_id ' -- Bug 6604204. Please refer bug for details.
713 || 'AND BAU.bank_account_id = ABA.bank_account_id ' -- Bug 6604204. Please refer bug for details.
714 || 'AND AC.org_id = mo_global.get_current_org_id ' --added for bug 6275528
715 || 'AND IP.check_id = AC.check_id '
716 || 'AND REL.tax_entity_id = ' || p_tax_entity_id|| ' '
717 || 'AND CC.chart_of_accounts_id = ' || l_chart_of_accounts_id || ' ';
718
719 if p_calling_module = 'PAYMENTS REPORT' and nvl(p_federal_reportable_flag,'N') = 'N' then
720 null;
721 else -- for all other situations we want federal reportable vendors only
722 l_dynamic_sql3:= 'AND P.federal_reportable_flag = ''Y'' ';
723 end if;
724
725 if p_vendor_id is not null then
726 l_dynamic_sql3 := l_dynamic_sql3 || 'AND P.vendor_id = '||p_vendor_id||' ';
727 end if;
728
729 if p_query_driver = 'INV' then
730 l_dynamic_sql3 := l_dynamic_sql3 || 'AND ID.dist_code_combination_id = CC.code_combination_id ';
731 else
732 l_dynamic_sql3 := l_dynamic_sql3 || 'AND ABA.asset_code_combination_id = CC.code_combination_id ';
733 end if;
734
735 if p_region is not null then
736 l_dynamic_sql3 := l_dynamic_sql3 || 'AND id.income_tax_region = '''|| p_region ||''' ';
737 end if;
738
739 if (l_app_column_name LIKE 'SEGMENT1') THEN
740 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment1 = REL.balancing_segment_value ';
741 elsif (l_app_column_name LIKE 'SEGMENT2') THEN
742 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment2 = REL.balancing_segment_value ';
743 elsif (l_app_column_name LIKE 'SEGMENT3') THEN
744 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment3 = REL.balancing_segment_value ';
745 elsif (l_app_column_name LIKE 'SEGMENT4') THEN
746 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment4 = REL.balancing_segment_value ';
747 elsif (l_app_column_name LIKE 'SEGMENT5') THEN
748 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment5 = REL.balancing_segment_value ';
749 elsif (l_app_column_name LIKE 'SEGMENT6') THEN
750 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment6 = REL.balancing_segment_value ';
751 elsif (l_app_column_name LIKE 'SEGMENT7') THEN
752 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment7 = REL.balancing_segment_value ';
753 elsif (l_app_column_name LIKE 'SEGMENT8') THEN
754 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment8 = REL.balancing_segment_value ';
755 elsif (l_app_column_name LIKE 'SEGMENT9') THEN
756 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment9 = REL.balancing_segment_value ';
757 elsif (l_app_column_name LIKE 'SEGMENT10') THEN
758 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment10 = REL.balancing_segment_value ';
759 elsif (l_app_column_name LIKE 'SEGMENT11') THEN
760 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment11 = REL.balancing_segment_value ';
761 elsif (l_app_column_name LIKE 'SEGMENT12') THEN
762 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment12 = REL.balancing_segment_value ';
763 elsif (l_app_column_name LIKE 'SEGMENT13') THEN
764 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment13 = REL.balancing_segment_value ';
765 elsif (l_app_column_name LIKE 'SEGMENT14') THEN
766 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment14 = REL.balancing_segment_value ';
767 elsif (l_app_column_name LIKE 'SEGMENT15') THEN
768 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment15 = REL.balancing_segment_value ';
769 elsif (l_app_column_name LIKE 'SEGMENT16') THEN
770 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment16 = REL.balancing_segment_value ';
771 elsif (l_app_column_name LIKE 'SEGMENT17') THEN
772 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment17 = REL.balancing_segment_value ';
773 elsif (l_app_column_name LIKE 'SEGMENT18') THEN
774 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment18 = REL.balancing_segment_value ';
775 elsif (l_app_column_name LIKE 'SEGMENT19') THEN
776 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment19 = REL.balancing_segment_value ';
777 elsif (l_app_column_name LIKE 'SEGMENT20') THEN
778 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment20 = REL.balancing_segment_value ';
779 elsif (l_app_column_name LIKE 'SEGMENT21') THEN
780 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment21 = REL.balancing_segment_value ';
781 elsif (l_app_column_name LIKE 'SEGMENT22') THEN
782 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment22 = REL.balancing_segment_value ';
783 elsif (l_app_column_name LIKE 'SEGMENT23') THEN
784 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment23 = REL.balancing_segment_value ';
785 elsif (l_app_column_name LIKE 'SEGMENT24') THEN
786 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment24 = REL.balancing_segment_value ';
787 elsif (l_app_column_name LIKE 'SEGMENT25') THEN
788 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment25 = REL.balancing_segment_value ';
789 elsif (l_app_column_name LIKE 'SEGMENT26') THEN
790 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment26 = REL.balancing_segment_value ';
791 elsif (l_app_column_name LIKE 'SEGMENT27') THEN
792 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment27 = REL.balancing_segment_value ';
793 elsif (l_app_column_name LIKE 'SEGMENT28') THEN
794 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment28 = REL.balancing_segment_value ';
795 elsif (l_app_column_name LIKE 'SEGMENT29') THEN
796 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment29 = REL.balancing_segment_value ';
797 elsif (l_app_column_name LIKE 'SEGMENT30') THEN
798 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment30 = REL.balancing_segment_value ';
799 end if;
800
801 if p_combined_flag = '1' then
802 l_dynamic_sql3 := l_dynamic_sql3 || 'GROUP BY ITR.region_code, P.vendor_id';
803 else
804 l_dynamic_sql3 := l_dynamic_sql3 || 'GROUP BY P.vendor_id';
805 end if;
806
807 open c_awt for l_dynamic_sql1 || l_dynamic_sql2 || l_dynamic_sql2_1 || l_dynamic_sql3;
808
809 LOOP
810
811 fetch c_awt into l_backup_awt_rec ;
812 exit when c_awt%NOTFOUND ;
813
814 update ap_1099_tape_data atd
815 set misc1 = decode(atd.misc1, 0, l_backup_awt_rec.misc1, atd.misc1),
816 misc2 = decode(atd.misc2, 0, l_backup_awt_rec.misc2, atd.misc2),
817 misc3 = decode(atd.misc3, 0, l_backup_awt_rec.misc3, atd.misc3),
818 misc5 = decode(atd.misc5, 0, l_backup_awt_rec.misc5, atd.misc5),
819 misc6 = decode(atd.misc6, 0, l_backup_awt_rec.misc6, atd.misc6),
820 misc7 = decode(atd.misc7, 0, l_backup_awt_rec.misc7, atd.misc7),
821 misc8 = decode(atd.misc8, 0, l_backup_awt_rec.misc8, atd.misc8),
822 misc9 = decode(atd.misc9, 0, l_backup_awt_rec.misc9, atd.misc9),
823 misc10 = decode(atd.misc10, 0, l_backup_awt_rec.misc10, atd.misc10),
824 misc13 = decode(atd.misc13, 0, l_backup_awt_rec.misc13, atd.misc13),
825 misc14 = decode(atd.misc14, 0, l_backup_awt_rec.misc14, atd.misc14),
826 misc15aNT = decode(atd.misc15aNT, 0, l_backup_awt_rec.misc15aNT, atd.misc15aNT),
827 misc15aT = decode(atd.misc15aT, 0, l_backup_awt_rec.misc15aT, atd.misc15aT),
828 misc15b = decode(atd.misc15b, 0, l_backup_awt_rec.misc15b, atd.misc15b)
829 where atd.vendor_id = l_backup_awt_rec.vendor_id
830 and nvl(atd.region_code, -99) = nvl(l_backup_awt_rec.region_code, -99)
831 and atd.misc4 > 0 ;
832
833 END LOOP ;
834
835 close c_awt ;
836
837 end do_awt_withholding_update;
838
839 --bug 13461067
840 -- Added to nullify the data of misc amounts if the
841 -- supplier has exccption so that in 1099 pdf report
842 -- for an exception supplier no misc amounts will be shown
843 --bug 15940811 query modified to show all the exceptions
844 -- as shown in the 1099 Supplier Exceptions Report
845 --bug 16036823 riverted the bug fix 15940811 and changed code
846 --to show exceptions based on the old code
847
848 PROCEDURE nullify_1099_data is
849
850 vendor_name varchar2(1000);
851 vendor_id number(10);
852 current_error_text varchar2(1000);
853
854 --bug 15940811 query modified to show all the exceptions
855 -- as shown in the 1099 Supplier Exceptions Report
856 --bug 13461067 "current_error_text_man" column added for the
857 --enhancement bug which will hold the exception text for 1099
858 --forms pdf format
859 CURSOR curs_1099 is
860 SELECT pvw.vendor_name vendor_name,
861 TD.vendor_id vendor_id,
862 decode(pvw.address_line1, '', 'No Address Line 1. ') ||
863 decode(substr(pvw.organization_type_lookup_code, 1, 7),
864 'FOREIGN',
865 decode(pvw.country, '', 'No country. '),
866 nvl(decode(length(replace(replace(nvl(pvw.national_identifier,
867 nvl(pvw.individual_1099,
868 pvw.num_1099)),
869 '-',
870 ''),
871 ' ',
872 '')),
873 0,
874 '',
875 9,
876 '',
877 'TIN not 9 digits. '),
878 decode(ltrim(translate(nvl(pvw.national_identifier,
879 nvl(pvw.individual_1099, pvw.num_1099)),
880 '1234567890- ',
881 ' ')),
882 '',
883 '',
884 'TIN contains non-numeric digit(s). ')) ||
885 decode(pvw.city, '', 'No city. ') ||
886 decode(nvl(pvw.province, pvw.state), '', 'No state. ') ||
887 decode(replace(replace(pvw.zip, '-', ''), ' ', ''),
888 '',
889 'No postal code. ')) current_error_text
890 FROM ap_1099_tape_data TD,
891 (SELECT P.vendor_id VENDOR_ID,
892 P.vendor_name VENDOR_NAME,
893 P.tax_reporting_name TAX_REPORTING_NAME,
894 p.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
895 NULL NATIONAL_IDENTIFIER,
896 P.individual_1099 INDIVIDUAL_1099,
897 P.num_1099 NUM_1099,
898 pvs.province PROVINCE,
899 pvs.address_line1 ADDRESS_LINE1,
900 pvs.address_line2 ADDRESS_LINE2,
901 pvs.address_line3 ADDRESS_LINE3,
902 pvs.state STATE,
903 pvs.city CITY,
904 pvs.zip ZIP,
905 pvs.COUNTRY COUNTRY
906 FROM po_vendors P,
907 po_vendor_sites pvs
908 WHERE pvs.vendor_id = P.vendor_id
909 AND nvl(p.vendor_type_lookup_code, 'DUMMY') <> 'EMPLOYEE' /*Bug 9247826*/
910 AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
911 (pvs.vendor_site_code =
912 (select min(vendor_site_code)
913 from po_vendor_sites pvs2
914 where pvs2.vendor_id = pvs.vendor_id
915 and nvl(inactive_date, sysdate + 9000) =
916 (select max(decode(inactive_date,
917 '',
918 sysdate + 9000,
919 inactive_date))
920 from po_vendor_sites pvs3
921 where pvs3.vendor_id = pvs.vendor_id))
922 AND not exists
923 (SELECT 'A tax reporting site exists for this vendor'
924 FROM po_vendor_sites pvs4
925 WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
926 AND pvs4.vendor_id = pvs.vendor_id)))
927 UNION ALL
928 SELECT distinct PV.vendor_id VENDOR_ID,
929 Pv.vendor_name VENDOR_NAME,
930 Pv.tax_reporting_name TAX_REPORTING_NAME,
931 pv.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
932 papf.national_identifier NATIONAL_IDENTIFIER,
933 NULL INDIVIDUAL_1099,
934 NULL NUM_1099,
935 NULL PROVINCE,
936 CASE pvs.vendor_site_code when 'HOME' then
937 per_addr.address_line1
938 when 'OFFICE' then
939 per_loc.address_line_1
940 when 'PROVISIONAL' then
941 per_addr_prov.address_line1
942 else case when per_addr.address_id is NULL then
943 per_loc.address_line_1
944 else
945 per_addr.address_line1
946 end
947 end ADDRESS_LINE1,
948 CASE pvs.vendor_site_code when 'HOME' then
949 per_addr.address_line2
950 when 'OFFICE' then
951 per_loc.address_line_2
952 when 'PROVISIONAL' then
953 per_addr_prov.address_line2
954 else case when per_addr.address_id is NULL then
955 per_loc.address_line_2
956 else
957 per_addr.address_line2
958 end
959 end ADDRESS_LINE2,
960 CASE pvs.vendor_site_code when 'HOME' then
961 per_addr.address_line3
962 when 'OFFICE' then
963 per_loc.address_line_3
964 when 'PROVISIONAL' then
965 per_addr_prov.address_line3
966 else case when per_addr.address_id is NULL then
967 per_loc.address_line_3
968 else
969 per_addr.address_line3
970 end
971 end ADDRESS_LINE3,
972 CASE pvs.vendor_site_code when 'HOME' then
973 case per_addr.style when 'CA' then
974 ''
975 when 'CA_GLB' then
976 ''
977 else
978 NVL(per_addr.region_2, '')
979 end
980 when 'OFFICE' then
981 case per_loc.style when 'CA' then
982 ''
983 when 'CA_GLB' then
984 ''
985 else
986 NVL(per_loc.region_2, '')
987 end
988 when 'PROVISIONAL' then
989 case per_addr_prov.style when 'CA' then
990 ''
991 when 'CA_GLB' then
992 ''
993 else
994 NVL(per_addr_prov.region_2, '')
995 end
996 else
997 case when per_addr.address_id is NULL then
998 case per_loc.style when 'CA' then
999 ''
1000 when 'CA_GLB' then
1001 ''
1002 else
1003 NVL(per_loc.region_2, '')
1004 end
1005 else
1006 case per_addr.style when 'CA' then
1007 ''
1008 when 'CA_GLB' then
1009 ''
1010 else
1011 NVL(per_addr.region_2, '')
1012 end
1013 end
1014 end STATE,
1015 CASE pvs.vendor_site_code when 'HOME' then
1016 per_addr.town_or_city
1017 when 'OFFICE' then
1018 per_loc.town_or_city
1019 when 'PROVISIONAL' then
1020 per_addr_prov.town_or_city
1021 else
1022 case when per_addr.address_id is NULL then
1023 per_loc.town_or_city
1024 else
1025 per_addr.town_or_city
1026 end
1027 end CITY,
1028 CASE pvs.vendor_site_code when 'HOME' then
1029 per_addr.postal_code
1030 when 'OFFICE' then
1031 per_loc.postal_code
1032 when 'PROVISIONAL' then
1033 per_addr_prov.postal_code
1034 else
1035 case when per_addr.address_id is NULL then
1036 per_loc.postal_code
1037 else
1038 per_addr.postal_code
1039 end
1040 end ZIP,
1041 CASE pvs.vendor_site_code when 'HOME' then
1042 per_addr.country
1043 when 'OFFICE' then
1044 per_loc.country
1045 when 'PROVISIONAL' then
1046 per_addr_prov.country
1047 else
1048 case when per_addr.address_id is NULL then
1049 per_loc.country
1050 else
1051 per_addr.country
1052 end
1053 end COUNTRY
1054 FROM HR_LOCATIONS per_loc,
1055 PER_ADDRESSES per_addr,
1056 PER_ALL_ASSIGNMENTS_F per_assgn,
1057 PER_ADDRESSES per_addr_prov,
1058 (SELECT distinct papf1.person_id, papf1.national_identifier
1059 FROM PER_ALL_PEOPLE_F papf1
1060 WHERE NVL(papf1.effective_end_date,sysdate +9000) =
1061 (SELECT MAX(DECODE(papf2.effective_end_date,'',sysdate+9000,papf2.effective_end_date))
1062 FROM PER_ALL_PEOPLE_F papf2
1063 WHERE papf1.person_id=papf2.person_id)) papf,
1064 PO_VENDOR_SITES pvs,
1065 PO_VENDORS pv
1066 WHERE pv.employee_id = papf.person_id
1067 AND nvl(pv.vendor_type_lookup_code, 'DUMMY') = 'EMPLOYEE' /*Bug 9247826*/
1068 AND pv.vendor_id = pvs.vendor_id
1069 AND pv.employee_id = per_addr.person_id(+)
1070 AND per_addr.primary_flag(+) = 'Y'
1071 AND SYSDATE BETWEEN per_addr.date_from(+) AND
1072 NVL(per_addr.date_to(+), SYSDATE + 1)
1073 AND pv.employee_id = per_addr_prov.person_id(+)
1074 AND nvl(per_addr_prov.address_type(+),'N') = 'M'
1075 AND SYSDATE BETWEEN per_addr_prov.date_from(+) AND
1076 NVL(per_addr_prov.date_to(+), SYSDATE + 1)
1077 AND pv.employee_id = per_assgn.person_id(+)
1078 AND per_assgn.location_id = per_loc.location_id(+)
1079 AND per_assgn.assignment_type(+) = 'E'
1080 AND per_assgn.primary_flag(+) = 'Y'
1081 AND (TRUNC(SYSDATE) BETWEEN per_assgn.effective_start_date(+) AND
1082 per_assgn.effective_end_date(+))
1083 AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
1084 (pvs.vendor_site_code =
1085 (select min(vendor_site_code)
1086 from po_vendor_sites pvs2
1087 where pvs2.vendor_id = pvs.vendor_id
1088 and nvl(inactive_date, sysdate + 9000) =
1089 (select max(decode(inactive_date,
1090 '',
1091 sysdate + 9000,
1092 inactive_date))
1093 from po_vendor_sites pvs3
1094 where pvs3.vendor_id = pvs.vendor_id)) AND not exists
1095 (SELECT 'A tax reporting site exists for this vendor'
1096 FROM po_vendor_sites pvs4
1097 WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
1098 AND pvs4.vendor_id = pvs.vendor_id)))) pvw /*Bug 8768541 :added the inline view for emp address from HR*/
1099 where pvw.vendor_id = TD.vendor_id
1100 GROUP BY pvw.vendor_name,
1101 TD.vendor_id,
1102 nvl(pvw.national_identifier, nvl(pvw.individual_1099, pvw.num_1099)),
1103 pvw.tax_reporting_name,
1104 pvw.organization_type_lookup_code,
1105 pvw.country,
1106 pvw.address_line1,
1107 pvw.address_line2,
1108 pvw.address_line3,
1109 pvw.city,
1110 pvw.province,
1111 pvw.state,
1112 pvw.zip;
1113
1114
1115 BEGIN
1116 FOR rec_1099_curs IN curs_1099
1117 LOOP
1118 IF rec_1099_curs.current_error_text is not null THEN
1119 UPDATE ap_1099_tape_data
1120 SET
1121 misc1 =null,
1122 misc2 =null,
1123 misc3 =null,
1124 misc4 =null,
1125 misc5 =null,
1126 misc6 =null,
1127 misc7 =null,
1128 misc15aT =null,
1129 misc15b =null,
1130 misc8 =null,
1131 misc9 =null,
1132 misc10 =null,
1133 misc13 =null,
1134 misc14 =null,
1135 misc15aNT =null
1136 WHERE vendor_id=rec_1099_curs.vendor_id;
1137 END IF;
1138
1139 END LOOP;
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 NULL;
1143
1144 END ;
1145
1146 END AP_1099_UTILITIES_PKG;