[Home] [Help]
PACKAGE BODY: APPS.AP_1099_UTILITIES_PKG
Source
1 PACKAGE BODY AP_1099_UTILITIES_PKG AS
2 /* $Header: ap1099utlb.pls 120.11 2007/11/19 13:20:05 ppodhiya 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
62 l_dynamic_sql1 := l_dynamic_sql1
63 || 'round(sum(decode(ID.type_1099,''MISC1'','
64 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
65 || 'null,0,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,ABS(ID.amount)))),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 || ' *IP.amount),0)),2),' -- Bug 5768112
75 || 'round(sum(decode(ID.type_1099,''MISC2'','
76 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
77 || 'null,0,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,ABS(ID.amount)))),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,'
83 || ' INV_NET_AMT.netamount ))'
84 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
85 || '*IP.amount),0)),2),' -- Bug 5768112
86 || 'round(sum(decode(ID.type_1099,''MISC3'','
87 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
88 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
89 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
90 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
91 || ' decode(I.invoice_amount,0,decode(I.cancelled_amount,'
92 || 'null,1,0,1,I.cancelled_amount),'
93 || ' decode( INV_NET_AMT.netamount ,0,1,'
94 || ' INV_NET_AMT.netamount ))'
95 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
96 || '*IP.amount),0)),2),' -- Bug 5768112
97 || '-1*(round(sum(decode(ID.type_1099,''MISC4'','
98 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
99 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
100 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
101 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
102 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
103 || 'null,1,0,1,I.cancelled_amount),'
104 || ' decode( INV_NET_AMT.netamount ,0,1,'
105 || ' INV_NET_AMT.netamount ))'
106 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2)),' -- Bug 5260442
107 || '*IP.amount),0)),2)),' -- Bug 5768112
108 || 'round(sum(decode(ID.type_1099,''MISC5'','
109 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
110 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
111 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
112 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
113 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
114 || 'null,1,0,1,I.cancelled_amount),'
115 || ' decode( INV_NET_AMT.netamount ,0,1,'
116 || ' INV_NET_AMT.netamount ))'
117 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
118 || '*IP.amount),0)),2),' -- Bug 5768112
119 || 'round(sum(decode(ID.type_1099,''MISC6'','
120 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
121 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
122 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
123 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
124 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
125 || 'null,1,0,1,I.cancelled_amount),'
126 || ' decode( INV_NET_AMT.netamount ,0,1,'
127 || ' INV_NET_AMT.netamount ))'
128 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
129 || '*IP.amount),0)),2),' -- Bug 5768112
130 || 'round(sum(decode(ID.type_1099,''MISC7'','
131 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
132 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
133 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
134 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
135 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
136 || 'null,1,0,1,I.cancelled_amount),'
137 || ' decode( INV_NET_AMT.netamount ,0,1,'
138 || ' INV_NET_AMT.netamount ))'
139 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),'; -- Bug 5260442
140 || '*IP.amount),0)),2),'; -- Bug 5768112
141
142 l_dynamic_sql2:= 'round(sum(decode(ID.type_1099,''MISC8'','
143 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
144 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
145 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
146 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
147 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
148 || 'null,1,0,1,I.cancelled_amount),'
149 || ' decode( INV_NET_AMT.netamount ,0,1,'
150 || ' INV_NET_AMT.netamount ))'
151 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
152 || '*IP.amount),0)),2),' -- Bug 5768112
153 || 'round(sum(decode(ID.type_1099,''MISC9'','
154 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
155 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
156 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
157 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
158 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
159 || 'null,1,0,1,I.cancelled_amount),'
160 || ' decode( INV_NET_AMT.netamount ,0,1,'
161 || ' INV_NET_AMT.netamount ))'
162 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
163 || '*IP.amount),0)),2),' -- Bug 5768112
164 || 'round(sum(decode(ID.type_1099,''MISC10'','
165 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
166 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
167 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
168 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
169 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
170 || 'null,1,0,1,I.cancelled_amount),'
171 || ' decode( INV_NET_AMT.netamount ,0,1,'
172 || ' INV_NET_AMT.netamount ))'
173 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
174 || '*IP.amount),0)),2),' -- Bug 5768112
175 || 'round(sum(decode(ID.type_1099,''MISC13'','
176 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
177 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
178 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
179 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
180 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
181 || 'null,1,0,1,I.cancelled_amount),'
182 || ' decode( INV_NET_AMT.netamount ,0,1,'
183 || ' INV_NET_AMT.netamount ))'
184 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
185 || '*IP.amount),0)),2),' -- Bug 5768112
186 || 'round(sum(decode(ID.type_1099,''MISC14'','
187 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
188 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
189 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
190 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
191 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
192 || 'null,1,0,1,I.cancelled_amount),'
193 || ' decode( INV_NET_AMT.netamount ,0,1,'
194 || ' INV_NET_AMT.netamount ))'
195 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' ;-- Bug 5260442
196 || '*IP.amount),0)),2), '; -- Bug 5768112
197
198 l_dynamic_sql2_1 := 'round(sum(decode(ID.type_1099,''MISC15a NT'','
199 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
200 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
201 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
202 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
203 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
204 || 'null,1,0,1,I.cancelled_amount),'
205 || ' decode( INV_NET_AMT.netamount ,0,1,'
206 || ' INV_NET_AMT.netamount ))'
207 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
208 || '*IP.amount),0)),2),' -- Bug 5768112
209 || 'round(sum(decode(ID.type_1099,''MISC15a T'','
210 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
211 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
212 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
213 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
214 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
215 || 'null,1,0,1,I.cancelled_amount),'
216 || ' decode( INV_NET_AMT.netamount ,0,1,'
217 || ' INV_NET_AMT.netamount ))'
218 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
219 || '*IP.amount),0)),2),' -- Bug 5768112
220 || 'round(sum(decode(ID.type_1099,''MISC15b'','
221 || '(decode(I.invoice_amount,0,decode(I.cancelled_amount,'
222 || 'null,0,decode(greatest(ID.accounting_date,'''||l_id_end_date||''' ),'
223 || 'ID.accounting_date,0,decode(least(ID.accounting_date,'''||l_id_start_date||''' ),'
224 || 'ID.accounting_date,0,ABS(ID.amount)))),ID.amount)/'
225 || 'decode(I.invoice_amount,0,decode(I.cancelled_amount,'
226 || 'null,1,0,1,I.cancelled_amount),'
227 || ' decode( INV_NET_AMT.netamount ,0,1,'
228 || ' INV_NET_AMT.netamount ))'
229 --|| ' *decode(IP.payment_base_amount,null,IP.amount,IP.payment_base_amount)),0)),2),' -- Bug 5260442
230 || '*IP.amount),0)),2), ' -- Bug 5768112
231 || l_org_id -- Bug 4946930
232 || ' FROM ap_income_tax_regions ITR, '
233 || 'ap_reporting_entity_lines_all REL, '
234 || 'po_vendors P, '
235 || 'ce_bank_accounts ABA, ' --for bug 6275528 - replaced ap_bank_accounts_all with ce_bank_Accounts
236 --as ap_bank_accounts_All is obsolete in R12.
237 || 'ce_bank_acct_uses_all BAU, ' -- Bug 6604204. Included table to join AP_CHECKS and
238 -- CE_BANK_ACCOUNTS table.
239 || 'ap_checks_all AC, '
240 || 'ap_invoices_all I, '
241 || 'gl_code_combinations CC, '
242 || 'ap_invoice_distributions_all ID, '
243 || 'ap_invoice_payments_all IP,' --Bug 6064614 --for bug 6275528 - changed ap_invoice_payments
244 --to ap_invoice_payments_all.
245 || '( select AI.invoice_id , nvl(AIL1.amount+AI.invoice_amount , AI.invoice_amount) as netamount ' --Bug5943123
246 || ' from ap_invoices_all AI, ( select AIL.invoice_id, SUM(AIL.amount) as amount '
247 || ' FROM ap_invoice_lines_all AIL ' --Bug6064614
248 || ' WHERE (nvl(AIL.invoice_includes_prepay_flag,''N'') = ''N'' ' --Bug6052333
249 || ' AND (AIL.line_type_lookup_code = ''PREPAY'' '
250 || ' OR (AIL.line_type_lookup_code =''TAX'' '
251 || ' AND AIL.prepay_invoice_id IS NOT NULL '
252 || ' AND AIL.prepay_line_number IS NOT NULL))) '
253 || ' OR AIL.line_type_lookup_code = ''AWT'' '
254 || ' GROUP by AIL.invoice_id ) AIL1 ' --Bug5943123
255 || ' where AI.invoice_id = AIL1.invoice_id (+) ) INV_NET_AMT '
256 || 'WHERE ID.income_tax_region = ITR.region_short_name (+) '
257 || 'AND P.vendor_id=I.vendor_id '
258 || 'AND AC.void_date is null ' --4480766
259 || 'AND I.invoice_id=IP.invoice_id '
260 || 'AND I.invoice_id=ID.invoice_id '
261 || 'AND INV_NET_AMT.invoice_id = I.invoice_id '
262 || 'AND IP.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
263 || 'AND ID.type_1099 is not null '
264 ---- || 'AND AC.bank_account_id = ABA.bank_account_id ' Commeted this for bug 6275528 - as in ap_checks_all
265 ---bank_account_id is not getting stamped in R12.bank_account_anme is there so we will use it.
266 --|| 'AND AC.bank_account_name = ABA.bank_account_name ' --added for bug 6275528 for the aobve explained reason.
267 -- Commented for Bug 6604204.
268 || 'AND AC.ce_bank_acct_use_id = BAU.bank_acct_use_id ' -- Bug 6604204. Please refer bug for details.
269 || 'AND BAU.bank_account_id = ABA.bank_account_id ' -- Bug 6604204. Please refer bug for details.
270 || 'AND AC.org_id = mo_global.get_current_org_id ' --added for bug 6275528
271 || 'AND IP.check_id = AC.check_id '
272 || 'AND REL.tax_entity_id = ' || p_tax_entity_id|| ' '
273 || 'AND CC.chart_of_accounts_id = ' || l_chart_of_accounts_id || ' ';
274
275
276 if p_calling_module = 'PAYMENTS REPORT' and nvl(p_federal_reportable_flag,'N') = 'N' then
277 null;
278 else -- for all other situations we want federal reportable vendors only
279 l_dynamic_sql3:= 'AND P.federal_reportable_flag = ''Y'' ';
280 end if;
281
282 if p_vendor_id is not null then
283 l_dynamic_sql3 := l_dynamic_sql3 || 'AND P.vendor_id = '||p_vendor_id||' ';
284 end if;
285
286 if p_query_driver = 'INV' then
287 l_dynamic_sql3 := l_dynamic_sql3 || 'AND ID.dist_code_combination_id = CC.code_combination_id ';
288 else
289 l_dynamic_sql3 := l_dynamic_sql3 || 'AND ABA.asset_code_combination_id = CC.code_combination_id ';
290 end if;
291
292 if p_region is not null then
293 l_dynamic_sql3 := l_dynamic_sql3 || 'AND id.income_tax_region = '''|| p_region ||''' ';
294 end if;
295
296 if (l_app_column_name LIKE 'SEGMENT1') THEN
297 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment1 = REL.balancing_segment_value ';
298 elsif (l_app_column_name LIKE 'SEGMENT2') THEN
299 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment2 = REL.balancing_segment_value ';
300 elsif (l_app_column_name LIKE 'SEGMENT3') THEN
301 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment3 = REL.balancing_segment_value ';
302 elsif (l_app_column_name LIKE 'SEGMENT4') THEN
303 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment4 = REL.balancing_segment_value ';
304 elsif (l_app_column_name LIKE 'SEGMENT5') THEN
305 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment5 = REL.balancing_segment_value ';
306 elsif (l_app_column_name LIKE 'SEGMENT6') THEN
307 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment6 = REL.balancing_segment_value ';
308 elsif (l_app_column_name LIKE 'SEGMENT7') THEN
309 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment7 = REL.balancing_segment_value ';
310 elsif (l_app_column_name LIKE 'SEGMENT8') THEN
311 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment8 = REL.balancing_segment_value ';
312 elsif (l_app_column_name LIKE 'SEGMENT9') THEN
313 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment9 = REL.balancing_segment_value ';
314 elsif (l_app_column_name LIKE 'SEGMENT10') THEN
315 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment10 = REL.balancing_segment_value ';
316 elsif (l_app_column_name LIKE 'SEGMENT11') THEN
317 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment11 = REL.balancing_segment_value ';
318 elsif (l_app_column_name LIKE 'SEGMENT12') THEN
319 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment12 = REL.balancing_segment_value ';
320 elsif (l_app_column_name LIKE 'SEGMENT13') THEN
321 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment13 = REL.balancing_segment_value ';
322 elsif (l_app_column_name LIKE 'SEGMENT14') THEN
323 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment14 = REL.balancing_segment_value ';
324 elsif (l_app_column_name LIKE 'SEGMENT15') THEN
325 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment15 = REL.balancing_segment_value ';
326 elsif (l_app_column_name LIKE 'SEGMENT16') THEN
327 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment16 = REL.balancing_segment_value ';
328 elsif (l_app_column_name LIKE 'SEGMENT17') THEN
329 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment17 = REL.balancing_segment_value ';
330 elsif (l_app_column_name LIKE 'SEGMENT18') THEN
331 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment18 = REL.balancing_segment_value ';
332 elsif (l_app_column_name LIKE 'SEGMENT19') THEN
333 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment19 = REL.balancing_segment_value ';
334 elsif (l_app_column_name LIKE 'SEGMENT20') THEN
335 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment20 = REL.balancing_segment_value ';
336 elsif (l_app_column_name LIKE 'SEGMENT21') THEN
337 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment21 = REL.balancing_segment_value ';
338 elsif (l_app_column_name LIKE 'SEGMENT22') THEN
339 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment22 = REL.balancing_segment_value ';
340 elsif (l_app_column_name LIKE 'SEGMENT23') THEN
341 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment23 = REL.balancing_segment_value ';
342 elsif (l_app_column_name LIKE 'SEGMENT24') THEN
343 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment24 = REL.balancing_segment_value ';
344 elsif (l_app_column_name LIKE 'SEGMENT25') THEN
345 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment25 = REL.balancing_segment_value ';
346 elsif (l_app_column_name LIKE 'SEGMENT26') THEN
347 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment26 = REL.balancing_segment_value ';
348 elsif (l_app_column_name LIKE 'SEGMENT27') THEN
349 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment27 = REL.balancing_segment_value ';
350 elsif (l_app_column_name LIKE 'SEGMENT28') THEN
351 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment28 = REL.balancing_segment_value ';
352 elsif (l_app_column_name LIKE 'SEGMENT29') THEN
353 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment29 = REL.balancing_segment_value ';
354 elsif (l_app_column_name LIKE 'SEGMENT30') THEN
355 l_dynamic_sql3 := l_dynamic_sql3 || 'AND CC.segment30 = REL.balancing_segment_value ';
356 end if;
357
358 if p_combined_flag = '1' then
359 l_dynamic_sql3 := l_dynamic_sql3 || 'GROUP BY ITR.region_code, P.vendor_id';
360 else
361 l_dynamic_sql3 := l_dynamic_sql3 || 'GROUP BY P.vendor_id';
362 end if;
363
364 declare
365 stemp VARCHAR2(80);
366 nlength NUMBER := 1;
367
368 BEGIN
369
370 WHILE(length(l_dynamic_sql1) >= nlength)
371 LOOP
372 stemp := substrb(l_dynamic_sql1, nlength, 80);
373 fnd_file.put_line(FND_FILE.LOG, stemp);
374 nlength := (nlength + 80);
375 END LOOP;
376 end;
377
378 declare
379 stemp VARCHAR2(80);
380 nlength NUMBER := 1;
381
382 BEGIN
383
384 WHILE(length(l_dynamic_sql2) >= nlength)
385 LOOP
386 stemp := substrb(l_dynamic_sql2, nlength, 80);
387 fnd_file.put_line(FND_FILE.LOG, stemp);
388 nlength := (nlength + 80);
389 END LOOP;
390 end;
391
392 declare
393 stemp VARCHAR2(80);
394 nlength NUMBER := 1;
395
396 BEGIN
397
398 WHILE(length(l_dynamic_sql2_1) >= nlength)
399 LOOP
400 stemp := substrb(l_dynamic_sql2_1, nlength, 80);
401 fnd_file.put_line(FND_FILE.LOG, stemp);
402 nlength := (nlength + 80);
403 END LOOP;
404 end;
405
406 declare
407 stemp VARCHAR2(80);
408 nlength NUMBER := 1;
409
410 BEGIN
411
412 WHILE(length(l_dynamic_sql3) >= nlength)
413 LOOP
414 stemp := substrb(l_dynamic_sql3, nlength, 80);
415 fnd_file.put_line(FND_FILE.LOG, stemp);
416 nlength := (nlength + 80);
417 END LOOP;
418 end;
419
420 execute immediate l_dynamic_sql1 || l_dynamic_sql2 || l_dynamic_sql2_1 || l_dynamic_sql3;
421
422 select count(*) into x from ap_1099_tape_data;
423 fnd_file.put_line(FND_FILE.LOG, to_char(x));
424
425
426 end insert_1099_data;
427
428
429 END AP_1099_UTILITIES_PKG;