[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS2007_IGIPMTHR_PKG
Source
1 PACKAGE BODY IGI_CIS2007_IGIPMTHR_PKG AS
2 -- $Header: igipmthrb.pls 120.19.12020000.7 2012/10/17 20:57:46 sasukuma ship $
3
4 --==========================================================================
5 ----Logging Declarations
6 --==========================================================================
7 C_STATE_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
8 C_PROC_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
9 C_EVENT_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 C_EXCEP_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
11 C_ERROR_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 C_UNEXP_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
13 g_log_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 g_path_name CONSTANT VARCHAR2(100) := 'igi.plsql.igipmthrb.IGI_CIS2007_IGIPMTHR_PKG';
15
16 PROCEDURE log
17 (
18 p_level IN NUMBER,
19 p_procedure_name IN VARCHAR2,
20 p_debug_info IN VARCHAR2
21 )
22 IS
23
24 BEGIN
25 IF (p_level >= g_log_level ) THEN
26 FND_LOG.STRING(p_level, p_procedure_name, p_debug_info);
27 END IF;
28 END log;
29
30 PROCEDURE init
31 IS
32 l_procedure_name VARCHAR2(100) :='.init';
33 BEGIN
34 log(C_STATE_LEVEL, l_procedure_name, 'Package Information');
35 log(C_STATE_LEVEL, l_procedure_name, '$Header: igipmthrb.pls 120.19.12020000.7 2012/10/17 20:57:46 sasukuma ship $');
36 END;
37
38 PROCEDURE Populate_Vendors(p_in_vendor_from IN VARCHAR2,
39 p_in_vendor_to IN VARCHAR2,
40 p_in_period in varchar2,
41 p_in_start_date in varchar2,
42 p_in_end_date in varchar2,
43 p_out_no_of_rows out nocopy integer)
44 IS
45 l_procedure_name VARCHAR2(100):='.Populate_Vendors';
46
47 -- vendorFrom varchar2(240) := p_in_vendor_from;
48 -- vendorTo varchar2(240) := p_in_vendor_to;
49 ret_count integer := 0;
50 l_start_date date;
51 l_end_date date;
52
53 /* cursor c1 is
54 select vendor_id
55 from po_vendors pov
56 where pov.cis_enabled_flag = 'Y'
57 -- And vendor_type_lookup_code in ('PARTNERSHIP','SOLETRADER','COMPANY','TRUST') bug 5620621
58 AND pov.cis_parent_vendor_id is null;*/
59 /*AND pov.vendor_id NOT IN
60 (SELECT audit_lines.vendor_id
61 FROM igi_cis_mth_ret_hdr_h audit_hdr,
62 igi_cis_mth_ret_lines_h audit_lines
63 WHERE audit_hdr.header_id = audit_lines.header_id
64 AND audit_hdr.request_status_code = 'C'
65 AND audit_hdr.period_name = p_in_period
66 UNION all
67 SELECT audit_lines_t.vendor_id
68 FROM igi_cis_mth_ret_hdr_t audit_hdr_t,
69 igi_cis_mth_ret_lines_t audit_lines_t
70 WHERE audit_hdr_t.header_id = audit_lines_t.header_id
71 AND audit_hdr_t.request_status_code = 'R'
72 AND audit_hdr_t.period_name = p_in_period)
73 AND upper(pov.vendor_name)
74 between upper(vendorFrom) and upper(vendorTo);*/
75
76 TYPE payment_details_rec IS RECORD (
77 child_id AP_SUPPLIERS.vendor_id%type,
78 parent_id AP_SUPPLIERS.parent_vendor_id%type,
79 invoice_id ap_invoices.invoice_id%type,
80 payment_id ap_invoice_payments.invoice_payment_id%type,
81 invoice_payment_amount ap_invoice_payments.amount%type,
82 discount_amount ap_invoice_payments.discount_taken%type);
83
84 TYPE all_payments IS TABLE OF payment_details_rec INDEX BY BINARY_INTEGER;
85 all_payment_list all_payments;
86
87 --Cursor c2 modified for bug # 6069932
88 -- CIS Out Of Scope ER
89 cursor c2 is
90 SELECT /*+ leading(ACA) */ pov1.vendor_id child_id,
91 decode(pov1.cis_parent_vendor_id,null, pov1.vendor_id,
92 decode(nvl(pov.cis_enabled_flag,'N'), 'N', pov1.vendor_id,pov1.cis_parent_vendor_id)) parent_id,
93 aia.invoice_id invoice_id,
94 aipa.invoice_payment_id payment_id,
95 aipa.amount invoice_payment_amount,
96 aipa.discount_taken discount_amount
97 FROM ap_invoices aia,
98 ap_invoice_payments aipa,
99 ap_checks aca,
100 AP_SUPPLIERS pov,
101 AP_SUPPLIERS pov1,
102 -- Bug 5647413 Start
103 ap_supplier_sites pvs
104 -- Bug 5647413 End
105 WHERE aia.invoice_id = aipa.invoice_id
106 AND aca.check_id = aipa.check_id
107 AND aca.void_date IS NULL
108 And aca.check_number is not null
109 -- Bug 5647413 Start
110 and pov.vendor_id(+) = pov1.cis_parent_vendor_id
111 and pvs.vendor_id = pov1.vendor_id
112 and (pov.cis_enabled_flag = 'Y' or pov1.cis_enabled_flag = 'Y')
113 and pvs.allow_awt_flag = 'Y'
114 and aia.vendor_site_id = pvs.vendor_site_id
115 -- Bug 5647413 End
116 --AND aia.invoice_type_lookup_code = 'STANDARD'
117 AND aia.vendor_id = pov1.vendor_id
118 AND trunc(aca.check_date) BETWEEN l_start_date AND l_end_date /*Added for bug 13028312*/
119 AND EXISTS( select 1 from ap_invoice_distributions aida
120 where aida.invoice_id = aia.invoice_id
121 and (aida.awt_group_id is not null OR aida.pay_awt_group_id is not null));
122
123 --c1_rec_info c1%rowtype;
124 --c2_rec_info c2%rowtype;
125 l_lab_cost number;
126 l_mat_cost number;
127 l_awt_amnt number;
128 l_temp_pay_amount number;
129 l_cis_tax number;
130 BEGIN
131
132 l_procedure_name := g_path_name || l_procedure_name;
133 log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
134 log(C_STATE_LEVEL, l_procedure_name, 'p_in_vendor_from='||p_in_vendor_from);
135 log(C_STATE_LEVEL, l_procedure_name, 'p_in_vendor_to='||p_in_vendor_to);
136 log(C_STATE_LEVEL, l_procedure_name, 'p_in_period='||p_in_period);
137 log(C_STATE_LEVEL, l_procedure_name, 'p_in_start_date='||p_in_start_date);
138 log(C_STATE_LEVEL, l_procedure_name, 'p_in_end_date='||p_in_end_date);
139
140 l_lab_cost := 0;
141 l_mat_cost := 0;
142 l_awt_amnt := 0;
143 l_temp_pay_amount := 0;
144 l_start_date := to_date(p_in_start_date, 'DD-MM-YYYY');
145 l_end_date := to_date(p_in_end_date,'DD-MM-YYYY');
146 delete from IGI_CIS_MTH_RET_PAY_GT;
147 /* if p_in_vendor_from is null then
148 select min(vendor_name) , max(vendor_name)
149 into vendorFrom, vendorTo
150 From po_vendors;
151 end if; */
152 open c2;
153 fetch c2 bulk collect into all_payment_list;
154 close c2;
155
156 for i in 1 .. all_payment_list.count
157 loop
158 l_temp_pay_amount := all_payment_list(i).invoice_payment_amount;
159 log(C_STATE_LEVEL, l_procedure_name, 'l_temp_pay_amount='||l_temp_pay_amount);
160 log(C_STATE_LEVEL, l_procedure_name, 'Calling GET_PAYMENT_CIS_DETAILS');
161 GET_PAYMENT_CIS_DETAILS(
162 all_payment_list(i).payment_id,--C_payments_rec.invoice_payment_id,
163 all_payment_list(i).invoice_id,--C_payments_rec.invoice_id,
164 l_start_date,--l_start_date,
165 l_end_date,--l_end_date,
166 l_temp_pay_amount,-- bug 5609552
167 --c2_rec_info.invoice_payment_amount,--C_payments_rec.amount,
168 all_payment_list(i).discount_amount,--C_payments_rec.discount_taken,
169 l_lab_cost,
170 l_mat_cost,
171 l_awt_amnt,
172 l_cis_tax);
173
174 log(C_STATE_LEVEL, l_procedure_name, 'Insertint into IGI_CIS_MTH_RET_PAY_GT');
175 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_ID='||all_payment_list(i).parent_id);
176 log(C_STATE_LEVEL, l_procedure_name, 'CHILD_VENDOR_ID='||all_payment_list(i).child_id);
177 log(C_STATE_LEVEL, l_procedure_name, 'INVOICE_ID='||all_payment_list(i).invoice_id);
178 log(C_STATE_LEVEL, l_procedure_name, 'INVOICE_PAYMENT_ID='||all_payment_list(i).payment_id);
179 log(C_STATE_LEVEL, l_procedure_name, 'AMOUNT='||l_temp_pay_amount);
180 log(C_STATE_LEVEL, l_procedure_name, 'LABOUR_COST='||l_lab_cost);
181 log(C_STATE_LEVEL, l_procedure_name, 'MATERIAL_COST='||l_mat_cost);
182 log(C_STATE_LEVEL, l_procedure_name, 'TOTAL_DEDUCTIONS='||l_awt_amnt);
183 log(C_STATE_LEVEL, l_procedure_name, 'DISCOUNT_AMOUNT='||all_payment_list(i).discount_amount);
184 log(C_STATE_LEVEL, l_procedure_name, 'CIS_TAX='||l_cis_tax);
185 insert into IGI_CIS_MTH_RET_PAY_GT(
186 VENDOR_ID,
187 CHILD_VENDOR_ID,
188 INVOICE_ID,
189 INVOICE_PAYMENT_ID,
190 AMOUNT,
191 LABOUR_COST,
192 MATERIAL_COST,
193 TOTAL_DEDUCTIONS,
194 DISCOUNT_AMOUNT,
195 CIS_TAX)
196 values
197 (all_payment_list(i).parent_id,
198 all_payment_list(i).child_id,
199 all_payment_list(i).invoice_id,
200 all_payment_list(i).payment_id,
201 --c2_rec_info.invoice_payment_amount,
202 l_temp_pay_amount, -- bug 5609552
203 l_lab_cost,
204 l_mat_cost,
205 l_awt_amnt,
206 all_payment_list(i).discount_amount,
207 l_cis_tax);
208 ret_count := ret_count +1;
209 end loop;
210 /*insert into IGI_CIS_MTH_RET_PAY_GT
211 values
212 (c1_rec_info.vendor_id,
213 c1_rec_info.vendor_id,
214 null,
215 null,
216 null);*/
217 p_out_no_of_rows := ret_count;
218 log(C_STATE_LEVEL, l_procedure_name, 'END');
219 EXCEPTION
220 WHEN OTHERS THEN
221 log(C_STATE_LEVEL, l_procedure_name, 'ERROR='||SQLERRM);
222 /* IF c1%ISOPEN THEN
223 CLOSE c1;
224 END IF;*/
225 IF c2%ISOPEN THEN
226 CLOSE c2;
227 END IF;
228 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION');
229 END Populate_Vendors;
230
231 PROCEDURE GET_PAYMENT_CIS_DETAILS(
232 p_inv_pay_id in number, --igi_cis_mth_ret_pay_t.invoice_payment_id%Type,
233 p_inv_id in number,
234 p_tax_mth_start_date in date,
235 p_tax_mth_end_date in date,
236 p_pay_amount in out nocopy number, --igi_cis_mth_ret_pay_t.payment_amount%Type,
237 p_discount_amount in number,
238 p_labour_cost out nocopy number, --igi_cis_mth_ret_pay_t.labour_cost%Type,
239 p_material_cost out nocopy number, --igi_cis_mth_ret_pay_t.material_cost%Type,
240 p_awt_amount out nocopy number,--igi_cis_mth_ret_pay_t.total_deductions%Type
241 p_cis_tax out nocopy number --igi_cis_mth_ret_pay_t.vat_amount%Type,
242 )
243 IS
244 l_procedure_name VARCHAR2(100):='.GET_PAYMENT_CIS_DETAILS';
245 -- Fetch the invoice_amount
246 Cursor C_invoice_amount is
247 Select invoice_amount invoice_amount
248 From ap_invoices
249 Where invoice_id = p_inv_id;
250 -- Fetch the total labour cost for an invoice.
251 Cursor C_labour_cost is
252 Select nvl(sum(amount),0) labour_cost
253 From ap_invoice_distributions
254 where line_type_lookup_code in ('ITEM' , 'ACCRUAL' , 'IPV' , 'ERV' , 'RETAINAGE', 'PREPAY')
255 -- and awt_group_id is not null
256 and IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(NULL,NULL,awt_group_id,pay_awt_group_id) is not null /* Bug 7218825 */
257 and invoice_id = p_inv_id;
258 -- Fetch the total material cost for an invoice.
259 Cursor C_material_cost is
260 Select nvl(sum(amount),0) material_cost
261 From ap_invoice_distributions
262 where line_type_lookup_code in ('ITEM' , 'ACCRUAL' , 'IPV' , 'ERV' , 'RETAINAGE', 'PREPAY')
263 -- and awt_group_id is null
264 and IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(NULL,NULL,awt_group_id,pay_awt_group_id) is null /* Bug 7218825 */
265 and invoice_id = p_inv_id;
266 -- Start 5609552
267 -- Fetch the total vat cost for an invoice.
268 Cursor C_vat_cost is
269 Select nvl(sum(amount),0) vat_cost
270 From ap_invoice_distributions
271 -- where line_type_lookup_code = 'TAX'
272 where line_type_lookup_code IN ('TAX','REC_TAX','NONREC_TAX','TRV','TERV','TIPV') -- Bug 8464796
273 --and awt_group_id is null
274 and IGI_CIS2007_UTIL_PKG.get_payables_option_based_awt(NULL,NULL,awt_group_id,pay_awt_group_id) is null /* Bug 7218825 */
275 and invoice_id = p_inv_id;
276 -- End 5609552
277
278 Cursor C_ret_prepay_amt is
279 Select nvl(sum(amount),0) ret_prepay_amt
280 From ap_invoice_distributions d,
281 ap_invoices_all i
282 where ((i.invoice_type_lookup_code NOT IN ('RETAINAGE RELEASE') AND
283 d.line_type_lookup_code IN ('RETAINAGE', 'PREPAY')) OR
284 (i.invoice_type_lookup_code IN ('RETAINAGE RELEASE') AND
285 d.line_type_lookup_code IN ('PREPAY')))
286 and i.invoice_id = p_inv_id
287 and i.invoice_id = d.invoice_id;
288
289 -- Find out the number of Witholding lines for the invoice payment
290 Cursor C_pay_awt_lines_count is
291 Select count(*) line_count
292 From ap_invoice_distributions
293 where line_type_lookup_code = 'AWT'
294 and invoice_id = p_inv_id
295 and awt_invoice_payment_id = p_inv_pay_id;
296 -- Find out the number of Witholding lines for the tax period.
297 -- If the payables options is set to Apply Withholding Tax
298 -- at Invoice Validation Time, the AWT Tax line(s) generated on
299 -- invoice validation are never linked to the invoice payment.
300 Cursor C_inv_awt_lines_count is
301 Select count(*) line_count
302 From ap_invoice_distributions
303 where line_type_lookup_code = 'AWT'
304 and invoice_id = p_inv_id
305 and awt_invoice_payment_id is null;
306 -- This condition is requried to select only the withholding tax lines
307 -- for the tax period
308 -- and trunc(accounting_date) between trunc(p_tax_mth_start_date)
309 -- and trunc(p_tax_mth_end_date);
310 -- Fetch the withholding tax amount generated for the
311 -- invoice payment.
312 -- If the payables options is set to Apply Withholding Tax
313 -- at Payment Time, the AWT Tax line(s) generated on payment
314 -- are always linked to the invoice payment.
315 Cursor C_pay_awt_amount is
316 Select nvl(sum(amount),0) awt_amount
317 From ap_invoice_distributions
318 where line_type_lookup_code = 'AWT'
319 and invoice_id = p_inv_id
320 and awt_invoice_payment_id = p_inv_pay_id;
321 -- Fetch the withholding tax amount generated for the invoice.
322 -- If the payables options is set to Apply Withholding Tax
323 -- at Invoice Validation Time, the AWT Tax line(s) generated on
324 -- invoice validation are never linked to the invoice payment.
325 Cursor C_inv_awt_amount is
326 Select nvl(sum(amount),0) awt_amount
327 From ap_invoice_distributions
328 where line_type_lookup_code = 'AWT'
329 and invoice_id = p_inv_id
330 and awt_invoice_payment_id is null;
331 -- This condition is requried to select only the withholding tax lines
332 -- for the tax period
333 -- and trunc(accounting_date) between trunc(p_tax_mth_start_date)
334 -- and trunc(p_tax_mth_end_date);
335 -- Define local variables
336 --Calculating the cis related tax amounts
337 -- when the WHT is applied at the payment level
338 Cursor C_pay_cis_tax is
339 Select nvl(sum(amount),0) awt_amount
340 From ap_invoice_distributions
341 where line_type_lookup_code = 'AWT'
342 and invoice_id = p_inv_id
343 and awt_invoice_payment_id = p_inv_pay_id
344 and awt_tax_rate_id in (
345 select tax_rate_id
346 from ap_awt_tax_rates_all
347 where tax_name in (
348 select name from ap_tax_codes_all
349 where awt_vendor_id = fnd_profile.value('IGI_CIS2007_TAX_AUTHORITY')));
350 --when the WHT is applied at the invoice validation level
351 Cursor C_inv_cis_tax is
352 Select nvl(sum(amount),0) awt_amount
353 From ap_invoice_distributions
354 where line_type_lookup_code = 'AWT'
355 and invoice_id = p_inv_id
356 and awt_invoice_payment_id is null
357 and awt_tax_rate_id in (
358 select tax_rate_id
359 from ap_awt_tax_rates_all
360 where tax_name in (
361 select name from ap_tax_codes_all
362 where awt_vendor_id = fnd_profile.value('IGI_CIS2007_TAX_AUTHORITY')));
363 l_inv_amount number;
364 l_inv_labour_amount number; --igi_cis_mth_ret_pay_t.labour_cost%Type;
365 l_inv_material_amount number; --igi_cis_mth_ret_pay_t.material_cost%Type;
366 l_awt_amount number; --igi_cis_mth_ret_pay_t.total_deductions%Type;
367 l_material_amount number; --igi_cis_mth_ret_pay_t.material_cost%Type;
368 l_labour_amount number; --igi_cis_mth_ret_pay_t.labour_cost%Type;
369 -- Start 5609552
370 l_vat_amount number; ---bug fix for 5609552
371 l_inv_vat_amount number; ---bug fix for 5609552
372 -- End 5609552
373 l_pay_awt_lines_count number;
374 l_inv_awt_lines_count number;
375 l_cis_tax number;
376 l_ret_prepay_amount number;
377 Begin
378 l_procedure_name := g_path_name || l_procedure_name;
379 log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
380 log(C_STATE_LEVEL, l_procedure_name, 'p_inv_pay_id='||p_inv_pay_id);
381 log(C_STATE_LEVEL, l_procedure_name, 'p_inv_id='||p_inv_id);
382 log(C_STATE_LEVEL, l_procedure_name, 'p_tax_mth_start_date='||p_tax_mth_start_date);
383 log(C_STATE_LEVEL, l_procedure_name, 'p_tax_mth_end_date='||p_tax_mth_end_date);
384 log(C_STATE_LEVEL, l_procedure_name, 'p_pay_amount='||p_pay_amount);
385 log(C_STATE_LEVEL, l_procedure_name, 'p_discount_amount='||p_discount_amount);
386 log(C_STATE_LEVEL, l_procedure_name, 'p_inv_pay_id='||p_inv_pay_id);
387 log(C_STATE_LEVEL, l_procedure_name, 'p_inv_pay_id='||p_inv_pay_id);
388 log(C_STATE_LEVEL, l_procedure_name, 'p_inv_pay_id='||p_inv_pay_id);
389
390 -- Initialise local variables
391 l_inv_amount := 0;
392 l_inv_labour_amount := 0;
393 l_inv_material_amount := 0;
394 l_awt_amount := 0;
395 l_material_amount := 0;
396 l_labour_amount := 0;
397 -- Start 5609552
398 l_vat_amount := 0;
399 l_inv_vat_amount := 0;
400 l_cis_tax := 0;
401 l_ret_prepay_amount := 0;
402 -- End 5609552
403 -- Fetch the invoice amount
404 For C_invoice_amount_rec in C_invoice_amount Loop
405 l_inv_amount := C_invoice_amount_rec.invoice_amount;
406 End Loop;
407 log(C_STATE_LEVEL, l_procedure_name, 'l_inv_amount='||l_inv_amount);
408 -- Fetch the total labour cost for an invoice.
409 For C_labour_cost_rec in C_labour_cost Loop
410 l_inv_labour_amount := C_labour_cost_rec.labour_cost;
411 End Loop;
412 log(C_STATE_LEVEL, l_procedure_name, 'l_inv_labour_amount='||l_inv_labour_amount);
413 -- Fetch the total material cost for an invoice.
414 For C_material_cost_rec in C_material_cost Loop
415 l_inv_material_amount := C_material_cost_rec.material_cost;
416 End Loop;
417 log(C_STATE_LEVEL, l_procedure_name, 'l_inv_material_amount='||l_inv_material_amount);
418 -- Start 5609552
419 -- Fetch the vat cost for an invoice.
420 For C_vat_cost_rec in C_vat_cost Loop
421 l_inv_vat_amount := C_vat_cost_rec.vat_cost;
422 End Loop;
423 log(C_STATE_LEVEL, l_procedure_name, 'l_inv_vat_amount='||l_inv_vat_amount);
424 -- End 5609552
425
426 For C_ret_prepay_rec in C_ret_prepay_amt Loop
427 l_ret_prepay_amount := -1*C_ret_prepay_rec.ret_prepay_amt;
428 End Loop;
429 log(C_STATE_LEVEL, l_procedure_name, 'l_ret_prepay_amount='||l_ret_prepay_amount);
430
431 -- Find out the number of Witholding lines generated for the
432 -- invoice payment
433 For C_pay_awt_lines_count_rec in C_pay_awt_lines_count Loop
434 l_pay_awt_lines_count := C_pay_awt_lines_count_rec.line_count;
435 End Loop;
436 log(C_STATE_LEVEL, l_procedure_name, 'l_pay_awt_lines_count='||l_pay_awt_lines_count);
437 -- Find out the number of Witholding lines generated for the
438 -- tax period.
439 -- If the payables options is set to Apply Withholding Tax
440 -- at Invoice Validation Time, the AWT Tax line(s) generated on
441 -- invoice validation are never linked to the invoice payment.
442 For C_inv_awt_lines_count_rec in C_inv_awt_lines_count Loop
443 l_inv_awt_lines_count := C_inv_awt_lines_count_rec.line_count;
444 End Loop;
445 log(C_STATE_LEVEL, l_procedure_name, 'l_inv_awt_lines_count='||l_inv_awt_lines_count);
446 -- If no witholding tax lines are found at all
447 If l_pay_awt_lines_count = 0 and l_inv_awt_lines_count = 0 Then
448 log(C_STATE_LEVEL, l_procedure_name, 'l_pay_awt_lines_count and l_inv_awt_lines_count are 0');
449 l_awt_amount := 0;
450 If ((p_pay_amount + p_discount_amount + l_ret_prepay_amount) < l_inv_amount) Then
451 log(C_STATE_LEVEL, l_procedure_name, 'partial payment logic');
452 -- It's a partial payment
453 l_labour_amount := ((l_inv_labour_amount/l_inv_amount) *
454 (p_pay_amount + p_discount_amount));
455 l_material_amount := ((l_inv_material_amount/l_inv_amount) *
456 (p_pay_amount + p_discount_amount));
457 -- Start 5609552
458 l_vat_amount := ((l_inv_vat_amount/l_inv_amount) *
459 (p_pay_amount + p_discount_amount));
460 -- End 5609552
461 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
462 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
463 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
464 Else
465 log(C_STATE_LEVEL, l_procedure_name, 'full payment logic');
466 -- It's a full payment
467 IF (NVL(p_discount_amount,0) = 0) THEN
468 log(C_STATE_LEVEL, l_procedure_name, 'No Discount');
469 l_labour_amount := l_inv_labour_amount;
470 l_material_amount := l_inv_material_amount;
471 l_vat_amount := l_inv_vat_amount; -- bug 5609552
472 ELSE
473 log(C_STATE_LEVEL, l_procedure_name, 'Consider Discount');
474 l_labour_amount := l_inv_labour_amount - (l_inv_labour_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
475 l_material_amount := l_inv_material_amount - (l_inv_material_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
476 l_vat_amount := l_inv_vat_amount - (l_inv_vat_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
477 END IF;
478 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
479 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
480 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
481 End If;
482 Else
483 log(C_STATE_LEVEL, l_procedure_name, 'there are withholding tax lines');
484 -- Witholding tax lines are found
485 -- Witholding tax lines can either be associated with the invoice
486 -- payment or not
487 If l_pay_awt_lines_count > 0 Then
488 log(C_STATE_LEVEL, l_procedure_name, 'l_pay_awt_lines_count is > 0');
489 -- Fetch the withholding tax amount generated for the
490 -- invoice payment.
491 -- Applicable only if the payables options is set to Apply
492 -- Withholding Tax at Payment Time
493 For C_pay_awt_amount_rec in C_pay_awt_amount Loop
494 -- Fetch Withholding Tax Amount
495 -- No need to proportion this since this is already done
496 -- by the Payables AWT Tax Engine
497 l_awt_amount := - C_pay_awt_amount_rec.awt_amount ;
498 For C_pay_cis_tax_rec in C_pay_cis_tax Loop
499 l_cis_tax := - C_pay_cis_tax_rec.awt_amount ;
500 end loop;
501 End Loop;
502 log(C_STATE_LEVEL, l_procedure_name, 'l_awt_amount='||l_awt_amount);
503 log(C_STATE_LEVEL, l_procedure_name, 'l_cis_tax='||l_cis_tax);
504 -- Check if it is partial or full payment
505 If ((p_pay_amount + l_awt_amount + p_discount_amount + l_ret_prepay_amount) < l_inv_amount) Then
506 log(C_STATE_LEVEL, l_procedure_name, 'partial payment logic');
507 -- it's a partial payment.
508 -- Compute the proportionate Labour Cost
509 l_labour_amount := ((l_inv_labour_amount / l_inv_amount) *
510 ( p_pay_amount + l_awt_amount + p_discount_amount));
511 -- Compute the proportionate Material Cost
512 l_material_amount := ((l_inv_material_amount / l_inv_amount) *
513 (p_pay_amount + l_awt_amount + p_discount_amount));
514 -- compute the proportionate vat cost bug 5609552
515 -- Start 5609552
516 l_vat_amount := ((l_inv_vat_amount/l_inv_amount) *
517 (p_pay_amount + l_awt_amount + p_discount_amount));
518 -- End 5609552
519 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
520 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
521 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
522 Else
523 log(C_STATE_LEVEL, l_procedure_name, 'full payment logic');
524 -- it's a full payment or overpayment.
525 IF (NVL(p_discount_amount,0) = 0) THEN
526 log(C_STATE_LEVEL, l_procedure_name, 'No Discount');
527 l_labour_amount := l_inv_labour_amount;
528 l_material_amount := l_inv_material_amount;
529 l_vat_amount := l_inv_vat_amount; -- bug 5609552
530 ELSE
531 log(C_STATE_LEVEL, l_procedure_name, 'Consider Discount');
532 l_labour_amount := l_inv_labour_amount - (l_inv_labour_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
533 l_material_amount := l_inv_material_amount - (l_inv_material_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
534 l_vat_amount := l_inv_vat_amount - (l_inv_vat_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
535 END IF;
536 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
537 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
538 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
539 End If;
540 Else
541 log(C_STATE_LEVEL, l_procedure_name, 'l_pay_awt_lines_count is not > 0');
542 -- Compute the proportionate Withholding tax, Labour cost
543 -- and Material cost for the invoice payment.
544 -- Applicable only if the payables options is set to Apply
545 -- Withholding Tax at Invoice Validation Time
546 For C_inv_awt_amount_rec in C_inv_awt_amount Loop
547 l_awt_amount := - C_inv_awt_amount_rec.awt_amount ;
548 For C_inv_cis_tax_rec in C_inv_cis_tax Loop
549 l_cis_tax := -C_inv_cis_tax_rec.awt_amount ;
550 End Loop;
551 End Loop;
552 log(C_STATE_LEVEL, l_procedure_name, 'l_awt_amount='||l_awt_amount);
553 log(C_STATE_LEVEL, l_procedure_name, 'l_cis_tax='||l_cis_tax);
554 -- Check if it is partial or full payment
555 If ((p_pay_amount + l_awt_amount + p_discount_amount + l_ret_prepay_amount) < l_inv_amount) Then
556 log(C_STATE_LEVEL, l_procedure_name, 'partial payment logic');
557 -- it's a part payment.
558 -- compute the proportionate awt first.
559 l_awt_amount := ((l_awt_amount/(l_inv_amount-l_awt_amount)) *
560 (p_pay_amount + p_discount_amount));
561
562 log(C_STATE_LEVEL, l_procedure_name, 'l_awt_amount='||l_awt_amount);
563 l_cis_tax := ((l_cis_tax/(l_inv_amount-l_awt_amount)) *
564 (p_pay_amount + p_discount_amount));
565 log(C_STATE_LEVEL, l_procedure_name, 'l_cis_tax='||l_cis_tax);
566
567 -- Compute the proportionate Labour Cost using the awt calculated
568 -- in the previous step.
569 l_labour_amount := ((l_inv_labour_amount / l_inv_amount) *
570 (p_pay_amount + p_discount_amount + l_awt_amount));
571 -- Compute the proportionate Material Cost
572 l_material_amount := ((l_inv_material_amount / l_inv_amount) *
573 (p_pay_amount + p_discount_amount + l_awt_amount));
574 -- compute the proportionate vat cost bug 5609552
575 -- Start 5609552
576 l_vat_amount := ((l_inv_vat_amount/l_inv_amount) *
577 (p_pay_amount + l_awt_amount + p_discount_amount));
578 -- End 5609552
579 -- discarded the old way of computation.
580 /*-- Compute the proportionate Withholding Tax Amount
581 -- Need to proportionate this since this is not done by
582 -- by the Payables AWT Tax Engine
583 l_awt_amount := ((l_labour_amount / l_inv_labour_amount) *
584 l_awt_amount );*/
585 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
586 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
587 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
588 Else
589 log(C_STATE_LEVEL, l_procedure_name, 'full payment logic');
590 -- it's a full payment or overpayment.
591 IF (NVL(p_discount_amount,0) = 0) THEN
592 log(C_STATE_LEVEL, l_procedure_name, 'No Discount');
593 l_labour_amount := l_inv_labour_amount;
594 l_material_amount := l_inv_material_amount;
595 l_vat_amount := l_inv_vat_amount; -- bug 5609552
596 ELSE
597 log(C_STATE_LEVEL, l_procedure_name, 'Consider Discount');
598 l_labour_amount := l_inv_labour_amount - (l_inv_labour_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
599 l_material_amount := l_inv_material_amount - (l_inv_material_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
600 l_vat_amount := l_inv_vat_amount - (l_inv_vat_amount*p_discount_amount)/(l_inv_labour_amount+l_inv_material_amount+l_inv_vat_amount);
601 END IF;
602 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
603 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
604 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
605 End If;
606 End If;
607 End If;
608 log(C_STATE_LEVEL, l_procedure_name, 'Finally');
609 log(C_STATE_LEVEL, l_procedure_name, 'l_labour_amount='||l_labour_amount);
610 log(C_STATE_LEVEL, l_procedure_name, 'l_material_amount='||l_material_amount);
611 log(C_STATE_LEVEL, l_procedure_name, 'l_vat_amount='||l_vat_amount);
612 p_labour_cost := round(l_labour_amount,2);
613 p_material_cost := round(l_material_amount,2);
614 p_awt_amount := round(l_awt_amount,2);
615 p_cis_tax := round(l_cis_tax,2);
616 -- Start 5609552
617 p_pay_amount := round((p_pay_amount - l_vat_amount),2);
618 log(C_STATE_LEVEL, l_procedure_name, 'p_labour_cost='||p_labour_cost);
619 log(C_STATE_LEVEL, l_procedure_name, 'p_material_cost='||p_material_cost);
620 log(C_STATE_LEVEL, l_procedure_name, 'p_awt_amount='||p_awt_amount);
621 log(C_STATE_LEVEL, l_procedure_name, 'p_cis_tax='||p_cis_tax);
622 log(C_STATE_LEVEL, l_procedure_name, 'p_pay_amount='||p_pay_amount);
623 --- End 5609552
624 log(C_STATE_LEVEL, l_procedure_name, 'END');
625
626 Exception
627 When Others Then
628 log(C_STATE_LEVEL, l_procedure_name, 'ERROR='||SQLERRM);
629 --dbms_output.put_line('Error in processing ' || sqlerrm);
630 p_labour_cost := -1;
631 p_material_cost := -1;
632 p_awt_amount := -1;
633 p_cis_tax := -1;
634 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION');
635 End Get_Payment_CIS_Details;
636 --
637 -- Procedure for 11.5.8 which will only populate history table
638 -- Modified for ER6137652
639 Procedure POPULATE_MTH_RET_DETAILS(
640 errbuf OUT NOCOPY VARCHAR2,
641 retcode OUT NOCOPY NUMBER,
642 p_nil_return_flag IN varchar2,
643 p_info_crct_flag IN varchar2,
644 p_subcont_verify_flag IN varchar2,
645 p_emp_status_flag IN varchar2,
646 p_inact_indicat_flag IN varchar2,
647 p_period_name IN varchar2,
648 p_mth_ret_mode IN varchar2,
649 p_mth_ret_amt_type IN varchar2,
650 p_mth_report_template IN varchar2,
651 p_mth_report_format IN varchar2,
652 p_mth_sort_by IN varchar2)
653 is
654 l_procedure_name VARCHAR2(100):='.POPULATE_MTH_RET_DETAILS';
655 -- get all the payments info
656 /*cursor C_pay_info is
657 select vendor_id, child_vendor_id, invoice_id,
658 invoice_payment_id, amount, labour_cost, material_cost,
659 total_deductions, discount_amount from igi_cis_mth_ret_pay_gt;*/
660 -- get the reporting entity information
661 Cursor C_rep_entity is
662 Select UNIQUE_TAX_REFERENCE_NUM,ACCOUNTS_OFFICE_REFERENCE,
663 TAX_OFFICE_NUMBER,PAYE_REFERENCE,fnd_profile.value('ORG_ID') ORG_ID,
664 CIS_SENDER_ID
665 From AP_REPORTING_ENTITIES
666 Where UNIQUE_TAX_REFERENCE_NUM is not null;
667 Cursor C_prev_return is
668 select nvl(sum(decode(X.nil_return_flag,'Y',1,0)),0) nil_ret_count,
669 nvl(sum(decode(X.nil_return_flag,'N',1,0)),0) non_nil_ret_count
670 from
671 (Select hdr_h.Nil_return_flag nil_return_flag
672 from IGI_CIS_MTH_RET_HDR_H hdr_h
673 where hdr_h.period_name = p_period_name
674 and hdr_h.request_status_code = 'C') X;
675
676 -- Group the payments according to parent subcontractors
677 Cursor C_non_nil_ret_lines_info is
678 select --:org_id ORG_ID,:header_id HEADER_ID,
679 pov.vendor_id VENDOR_ID,
680 -- Commented for bug 5671997 and add decode to get partnership name in case BT= partnership
681 --pov.vendor_name VENDOR_NAME,
682 decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_name,pov.vendor_name) VENDOR_NAME,
683 pov.vendor_type_lookup_code VENDOR_TYPE_LOOKUP_CODE,
684 pov.first_name FIRST_NAME,
685 pov.second_name SECOND_NAME,
686 pov.last_name LAST_NAME,
687 pov.salutation SALUTATION,
688 pov.trading_name TRADING_NAME,
689 pov.match_status_flag UNMATCHED_TAX_FLAG,
690 --pov.unique_tax_reference_num UNIQUE_TAX_REFERENCE_NUM,
691 decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_utr,
692 pov.unique_tax_reference_num)
693 UNIQUE_TAX_REFERENCE_NUM,
694 pov.company_registration_number COMPANY_REGISTRATION_NUMBER,
695 pov.national_insurance_number NATIONAL_INSURANCE_NUMBER,
696 pov.verification_number VERIFICATION_NUMBER,
697 sum(nvl(pay.amount, 0)) TOTAL_PAYMENTS,
698 sum(nvl(pay.TOTAL_DEDUCTIONS, 0)) TOTAL_DEDUCTIONS,
699 sum(nvl(pay.MATERIAL_COST, 0)) MATERIAL_COST,
700 sum(nvl(pay.LABOUR_COST, 0)) LABOUR_COST,
701 sum(nvl(pay.DISCOUNT_AMOUNT, 0)) DISCOUNT_AMOUNT,
702 sum(nvl(pay.CIS_TAX,0)) CIS_TAX
703 from AP_SUPPLIERS pov, IGI_CIS_MTH_RET_PAY_GT pay
704 where pov.vendor_id = pay.vendor_id
705 group by pov.vendor_id,
706 -- Commented for bug 5671997 and add decode to get partnership name in case BT= partnership
707 -- pov.vendor_name,
708 decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_name,pov.vendor_name),
709 pov.vendor_type_lookup_code,
710 pov.first_name,
711 pov.second_name,
712 pov.last_name,
713 pov.salutation,
714 pov.trading_name,
715 pov.match_status_flag,
716 --pov.unique_tax_reference_num,
717 decode(pov.vendor_type_lookup_code,'PARTNERSHIP',pov.partnership_utr,
718 pov.unique_tax_reference_num),
719 pov.company_registration_number,
720 pov.national_insurance_number,
721 pov.verification_number
722 order by upper(VENDOR_NAME) asc;
723
724 -- nil ret lines info
725 Cursor C_nil_ret_lines_info is
726 Select vendors.vendor_id VENDOR_ID,
727 -- Commented for bug 5671997 and add decode to get partnership name in case BT= partnership
728 -- vendors.vendor_name VENDOR_NAME,
729 decode(vendors.vendor_type_lookup_code,'PARTNERSHIP',vendors.partnership_name,vendors.vendor_name) VENDOR_NAME,
730 vendors.vendor_type_lookup_code VENDOR_TYPE_LOOKUP_CODE,
731 vendors.first_name FIRST_NAME,
732 vendors.second_name SECOND_NAME,
733 vendors.last_name LAST_NAME,
734 vendors.salutation SALUTATION,
735 vendors.trading_name TRADING_NAME,
736 vendors.match_status_flag UNMATCHED_TAX_FLAG,
737 --vendors.unique_tax_reference_num UNIQUE_TAX_REFERENCE_NUM,
738 decode(vendors.vendor_type_lookup_code,'PARTNERSHIP',vendors.partnership_utr,
739 vendors.unique_tax_reference_num)
740 UNIQUE_TAX_REFERENCE_NUM,
741 vendors.company_registration_number COMPANY_REGISTRATION_NUMBER,
742 vendors.national_insurance_number NATIONAL_INSURANCE_NUMBER,
743 vendors.verification_number VERIFICATION_NUMBER,
744 0 TOTAL_PAYMENTS,
745 0 TOTAL_DEDUCTIONS,
746 0 MATERIAL_COST,
747 0 LABOUR_COST,
748 0 DISCOUNT_AMOUNT,
749 0 CIS_TAX
750 from AP_SUPPLIERS vendors
751 where vendors.cis_enabled_flag = 'Y'
752 --And vendor_type_lookup_code in ('PARTNERSHIP','SOLETRADER','COMPANY','TRUST') bug 5620621
753 and vendors.cis_parent_vendor_id is null
754 order by upper(VENDOR_NAME) asc;
755 --
756
757 --Fix 5743166
758
759 Cursor C_period_validator is
760 select count(1) period_allowed
761 From AP_OTHER_PERIODS aop,
762 (SELECT decode(SIGN(to_number(to_char(sysdate, 'DD')) -6), -1, add_months(to_date(('05-' || to_char(sysdate, 'MM-YYYY')), 'DD-MM-YYYY'), 3),
763 add_months(to_date(('05-' || to_char(sysdate, 'MM-YYYY')),'DD-MM-YYYY'), 4)) end_date_criteria from dual) temp
764 where aop.period_type =
765 fnd_profile.value('IGI_CIS2007_CALENDAR')
766 and aop.period_year <= 2099
767 and aop.end_date between to_date('05-05-2007', 'DD-MM-YYYY')
768 AND
769 temp.end_date_criteria
770 and period_name = p_period_name;
771
772 --Fix 5743166
773 ---
774 -- local variables declaration
775 --
776 l_pay_count number;
777 l_period_allowed number;
778 l_nil_ret_count number;
779 l_non_nil_ret_count number;
780 l_header_id number;
781 l_org_id number;
782 l_period_start_date date;
783 l_period_end_date date;
784 e_validation_exception Exception;
785 l_err_all_msg varchar2(1000);
786 l_err_msg varchar2(500);
787 l_err_count number;
788 l_rep_ent_exist number;
789 -- particulars to call deduction report
790 l_request_id number;
791 l_appln_name varchar2(10) := 'IGI';
792 l_con_cp varchar2(15) := 'IGIPMTHR_XMLP';
793 l_con_cp_desc varchar2(200) := 'IGI : CIS2007 Monthly Returns Report';
794 e_request_submit_error exception;
795 l_xml_layout boolean;
796 -- variables after design change
797 l_prelim_count number;
798 l_prelim_hdr_id number;
799 e_param_mismatch_error exception;
800 e_prelim_mand_error exception;
801 e_rep_ent_not_found_error exception;
802
803 Begin
804 l_procedure_name := g_path_name || l_procedure_name;
805 log(C_STATE_LEVEL, l_procedure_name, 'BEGIN');
806 log(C_STATE_LEVEL, l_procedure_name, 'p_nil_return_flag='||p_nil_return_flag);
807 log(C_STATE_LEVEL, l_procedure_name, 'p_info_crct_flag='||p_info_crct_flag);
808 log(C_STATE_LEVEL, l_procedure_name, 'p_subcont_verify_flag='||p_subcont_verify_flag);
809 log(C_STATE_LEVEL, l_procedure_name, 'p_emp_status_flag='||p_emp_status_flag);
810 log(C_STATE_LEVEL, l_procedure_name, 'p_inact_indicat_flag='||p_inact_indicat_flag);
811 log(C_STATE_LEVEL, l_procedure_name, 'p_period_name='||p_period_name);
812 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_ret_mode='||p_mth_ret_mode);
813 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_ret_amt_type='||p_mth_ret_amt_type);
814 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_report_template='||p_mth_report_template);
815 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_report_format='||p_mth_report_format);
816 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_sort_by='||p_mth_sort_by);
817
818 l_period_allowed := 0;
819 l_pay_count := 0;
820 l_header_id := 0;
821 l_org_id := 0;
822 l_nil_ret_count := 0;
823 l_non_nil_ret_count := 0;
824 l_err_msg := '';
825 l_err_all_msg := '';
826 l_err_count := 0;
827 l_prelim_count := 0;
828 l_rep_ent_exist := 0;
829
830 -- information correct has to be 'Yes'.
831 if p_info_crct_flag = 'N' then
832 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_SUB_VER_MAND_CP');
833 l_err_msg := FND_MESSAGE.GET;
834 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
835 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
836 l_err_count := l_err_count + 1;
837 end if;
838 -- Subcontractor verification is mandatory
839 if p_nil_return_flag = 'N' and p_subcont_verify_flag is null then
840 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_SUB_VER_MAND_CP');
841 l_err_msg := FND_MESSAGE.GET;
842 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
843 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
844 l_err_count := l_err_count + 1;
845 end if;
846 -- Employment Status declaration is mandatory
847 if p_nil_return_flag = 'N' and p_emp_status_flag is null then
848 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_EMP_STAT_MAND_CP');
849 l_err_msg := FND_MESSAGE.GET;
850 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
851 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
852 l_err_count := l_err_count + 1;
853 --IGI_CIS2007_EMP_STAT_MAND_CP
854 end if;
855
856 if l_err_count > 0 then
857 raise e_validation_exception;
858 end if;
859 --
860 -- verify if Period is beyond 3 future months.
861 --
862 For C_period_validator_rec in C_period_validator loop
863 l_period_allowed := C_period_validator_rec.period_allowed;
864 log(C_STATE_LEVEL, l_procedure_name, 'l_period_allowed='||l_period_allowed);
865 End loop;
866
867 if l_period_allowed = 0 then
868 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_MTH_RET_NA');
869 FND_MESSAGE.SET_TOKEN('PERIOD', p_period_name);
870 l_err_msg := FND_MESSAGE.GET;
871 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
872 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
873 raise e_validation_exception;
874 End if;
875
876 -- get the start_date and end_date from ap_other_periods
877 select start_date,end_date
878 into l_period_start_date,l_period_end_date
879 from ap_other_periods
880 where period_type = fnd_profile.value('IGI_CIS2007_CALENDAR')
881 and period_name = p_period_name;
882
883 log(C_STATE_LEVEL, l_procedure_name, 'l_period_start_date='||l_period_start_date);
884 log(C_STATE_LEVEL, l_procedure_name, 'l_period_end_date='||l_period_end_date);
885
886 -- Throw error if there is a nil return already.
887 For C_prev_return_rec in C_prev_return loop
888 l_nil_ret_count := C_prev_return_rec.nil_ret_count;
889 l_non_nil_ret_count := C_prev_return_rec.non_nil_ret_count;
890 log(C_STATE_LEVEL, l_procedure_name, 'l_nil_ret_count='||l_nil_ret_count);
891 log(C_STATE_LEVEL, l_procedure_name, 'l_non_nil_ret_count='||l_non_nil_ret_count);
892 End loop;
893 If l_nil_ret_count > 0 then
894 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_NIL_RET_EXISTS');
895 FND_MESSAGE.SET_TOKEN('PERIOD_NAME', p_period_name);
896 l_err_msg := FND_MESSAGE.GET;
897 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
898 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
899 raise e_validation_exception;
900 End if;
901 --
902 -- Throw error if there is a Non nil return already.
903 --
904 If l_non_nil_ret_count > 0 then
905 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_MTH_RET_EXISTS');
906 FND_MESSAGE.SET_TOKEN('PERIOD_NAME', p_period_name);
907 l_err_msg := FND_MESSAGE.GET;
908 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
909 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
910 raise e_validation_exception;
911 End if;
912 -- Procedure call to calculate the payments
913 log(C_STATE_LEVEL, l_procedure_name, 'Calling igi_cis2007_igipmthr_pkg.Populate_Vendors');
914 igi_cis2007_igipmthr_pkg.Populate_Vendors(null, --p_in_vendor_from IN VARCHAR2,
915 null, --p_in_vendor_to IN VARCHAR2,
916 p_period_name, -- p_in_period in varchar2,
917 to_char(l_period_start_date,'DD-MM-YYYY'),--p_in_start_date in varchar2,
918 to_char(l_period_end_date,'DD-MM-YYYY'),--p_in_end_date in varchar2,
919 l_pay_count--p_out_no_of_rows out nocopy integer
920 );
921 log(C_STATE_LEVEL, l_procedure_name, 'l_pay_count='||l_pay_count);
922 --
923 -- throw error if there are payments for nil return
924 --
925 if l_pay_count > 0 and p_nil_return_flag = 'Y' then
926 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_VENDORS_PAID');
927 FND_MESSAGE.SET_TOKEN('PERIOD', p_period_name);
928 l_err_msg := FND_MESSAGE.GET;
929 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
930 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
931 raise e_validation_exception;
932 end if;
933 --
934 -- throw error if there are no payments for non-nil return
935 --
936 if l_pay_count = 0 and p_nil_return_flag = 'N' then
937 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_NO_VENDORS_PAID');
938 FND_MESSAGE.SET_TOKEN('PERIOD', p_period_name);
939 l_err_msg := FND_MESSAGE.GET;
940 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_err_msg);
941 l_err_all_msg := l_err_all_msg ||' '|| l_err_msg;
942 raise e_validation_exception;
943 end if;
944 --
945 -- get the next header id
946 --
947 SELECT IGI_CIS_MTH_RET_HDR_T_S.nextval
948 INTO l_header_id
949 FROM dual;
950 log(C_STATE_LEVEL, l_procedure_name, 'l_header_id='||l_header_id);
951 --
952 -- populate the header_table
953 --
954 For C_rep_entity_rec in C_rep_entity loop
955 l_org_id := C_rep_entity_rec.ORG_ID;
956 log(C_STATE_LEVEL, l_procedure_name, 'l_org_id='||l_org_id);
957 log(C_STATE_LEVEL, l_procedure_name, 'cis_sender_id='||C_rep_entity_rec.cis_sender_id);
958 log(C_STATE_LEVEL, l_procedure_name, 'tax_office_number='||C_rep_entity_rec.tax_office_number);
959 log(C_STATE_LEVEL, l_procedure_name, 'PAYE_REFERENCE='||C_rep_entity_rec.PAYE_REFERENCE);
960 log(C_STATE_LEVEL, l_procedure_name, 'UNIQUE_TAX_REFERENCE_NUM='||C_rep_entity_rec.UNIQUE_TAX_REFERENCE_NUM);
961 log(C_STATE_LEVEL, l_procedure_name, 'ACCOUNTS_OFFICE_REFERENCE='||C_rep_entity_rec.ACCOUNTS_OFFICE_REFERENCE);
962 log(C_STATE_LEVEL, l_procedure_name, 'cis_sender_id='||C_rep_entity_rec.cis_sender_id);
963 log(C_STATE_LEVEL, l_procedure_name, 'cis_sender_id='||C_rep_entity_rec.cis_sender_id);
964 log(C_STATE_LEVEL, l_procedure_name, 'cis_sender_id='||C_rep_entity_rec.cis_sender_id);
965 -- for debugging
966
967 log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_hdr_t');
968 insert into igi_cis_mth_ret_hdr_t(
969 HEADER_ID,
970 ORG_ID,
971 CIS_SENDER_ID,
972 TAX_OFFICE_NUMBER,
973 PAYE_REFERENCE,
974 REQUEST_ID,
975 REQUEST_STATUS_CODE,
976 PROGRAM_APPLICATION_ID,
977 PROGRAM_ID,
978 PROGRAM_LOGIN_ID,
979 UNIQUE_TAX_REFERENCE_NUM,
980 ACCOUNTS_OFFICE_REFERENCE,
981 PERIOD_NAME,
982 PERIOD_ENDING_DATE,
983 NIL_RETURN_FLAG,
984 EMPLOYMENT_STATUS_FLAG,
985 SUBCONT_VERIFY_FLAG,
986 INFORMATION_CORRECT_FLAG,
987 INACTIVITY_INDICATOR,
988 LAST_UPDATE_DATE,
989 LAST_UPDATED_BY,
990 LAST_UPDATE_LOGIN,
991 CREATION_DATE,
992 CREATED_BY
993 )
994 values(
995 l_header_id,
996 l_org_id,
997 C_rep_entity_rec.cis_sender_id,
998 C_rep_entity_rec.tax_office_number,
999 C_rep_entity_rec.PAYE_REFERENCE,
1000 FND_GLOBAL.CONC_REQUEST_ID(), -- REQUEST_ID
1001 'P', -- REQUEST_STATUS_CODE
1002 FND_GLOBAL.PROG_APPL_ID(), -- PROGRAM_APPLICATION_ID
1003 FND_GLOBAL.CONC_PROGRAM_ID(), -- PROGRAM_ID
1004 FND_GLOBAL.CONC_LOGIN_ID(), -- PROGRAM_LOGIN_ID
1005 C_rep_entity_rec.UNIQUE_TAX_REFERENCE_NUM,
1006 C_rep_entity_rec.ACCOUNTS_OFFICE_REFERENCE,
1007 p_period_name,
1008 l_period_end_date,
1009 p_nil_return_flag,
1010 p_emp_status_flag,
1011 p_subcont_verify_flag,
1012 p_info_crct_flag,
1013 nvl(p_inact_indicat_flag,'N'),
1014 sysdate,
1015 FND_GLOBAL.USER_ID(),
1016 FND_GLOBAL.LOGIN_ID(),
1017 sysdate,
1018 FND_GLOBAL.USER_ID()
1019 );
1020 l_rep_ent_exist := 1;
1021 end loop;
1022 -- for debugging
1023 log(C_STATE_LEVEL, l_procedure_name, 'l_rep_ent_exist='||l_rep_ent_exist);
1024 if l_rep_ent_exist <> 1 then
1025 l_err_msg := 'Reporting Entites Not Found';
1026 FND_FILE.PUT_LINE(FND_FILE.LOG,l_err_msg);
1027 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_err_msg);
1028 raise e_rep_ent_not_found_error;
1029 End if;
1030 --
1031 -- populate the lines table
1032 --
1033 if p_nil_return_flag = 'Y' then
1034 log(C_STATE_LEVEL, l_procedure_name, 'p_nil_return_flag is Y');
1035 For C_nil_ret_rec in C_nil_ret_lines_info loop
1036 log(C_STATE_LEVEL, l_procedure_name, '================');
1037 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_ID='||C_nil_ret_rec.VENDOR_ID);
1038 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_NAME='||C_nil_ret_rec.VENDOR_NAME);
1039 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_TYPE_LOOKUP_CODE='||C_nil_ret_rec.VENDOR_TYPE_LOOKUP_CODE);
1040 log(C_STATE_LEVEL, l_procedure_name, 'FIRST_NAME='||C_nil_ret_rec.FIRST_NAME);
1041 log(C_STATE_LEVEL, l_procedure_name, 'SECOND_NAME='||C_nil_ret_rec.SECOND_NAME);
1042 log(C_STATE_LEVEL, l_procedure_name, 'LAST_NAME='||C_nil_ret_rec.LAST_NAME);
1043 log(C_STATE_LEVEL, l_procedure_name, 'SALUTATION='||C_nil_ret_rec.SALUTATION);
1044 log(C_STATE_LEVEL, l_procedure_name, 'TRADING_NAME='||C_nil_ret_rec.TRADING_NAME);
1045 log(C_STATE_LEVEL, l_procedure_name, 'UNMATCHED_TAX_FLAG='||C_nil_ret_rec.UNMATCHED_TAX_FLAG);
1046 log(C_STATE_LEVEL, l_procedure_name, 'UNIQUE_TAX_REFERENCE_NUM='||C_nil_ret_rec.UNIQUE_TAX_REFERENCE_NUM);
1047 log(C_STATE_LEVEL, l_procedure_name, 'COMPANY_REGISTRATION_NUMBER='||C_nil_ret_rec.COMPANY_REGISTRATION_NUMBER);
1048 log(C_STATE_LEVEL, l_procedure_name, 'NATIONAL_INSURANCE_NUMBER='||C_nil_ret_rec.NATIONAL_INSURANCE_NUMBER);
1049 log(C_STATE_LEVEL, l_procedure_name, 'VERIFICATION_NUMBER='||C_nil_ret_rec.VERIFICATION_NUMBER);
1050 log(C_STATE_LEVEL, l_procedure_name, 'TOTAL_PAYMENTS='||C_nil_ret_rec.TOTAL_PAYMENTS);
1051 log(C_STATE_LEVEL, l_procedure_name, 'LABOUR_COST='||C_nil_ret_rec.LABOUR_COST);
1052 log(C_STATE_LEVEL, l_procedure_name, 'MATERIAL_COST='||C_nil_ret_rec.MATERIAL_COST);
1053 log(C_STATE_LEVEL, l_procedure_name, 'TOTAL_DEDUCTIONS='||C_nil_ret_rec.TOTAL_DEDUCTIONS);
1054 log(C_STATE_LEVEL, l_procedure_name, 'DISCOUNT_AMOUNT='||C_nil_ret_rec.DISCOUNT_AMOUNT);
1055 log(C_STATE_LEVEL, l_procedure_name, 'CIS_TAX='||C_nil_ret_rec.CIS_TAX);
1056 log(C_STATE_LEVEL, l_procedure_name, '================');
1057 log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_lines_t');
1058 insert into igi_cis_mth_ret_lines_t(
1059 HEADER_ID,
1060 ORG_ID,
1061 VENDOR_ID,
1062 VENDOR_NAME,
1063 VENDOR_TYPE_LOOKUP_CODE,
1064 FIRST_NAME,
1065 SECOND_NAME,
1066 LAST_NAME,
1067 SALUTATION,
1068 TRADING_NAME,
1069 UNMATCHED_TAX_FLAG,
1070 UNIQUE_TAX_REFERENCE_NUM,
1071 COMPANY_REGISTRATION_NUMBER,
1072 NATIONAL_INSURANCE_NUMBER,
1073 VERIFICATION_NUMBER,
1074 TOTAL_PAYMENTS,
1075 LABOUR_COST,
1076 MATERIAL_COST,
1077 TOTAL_DEDUCTIONS,
1078 DISCOUNT_AMOUNT,
1079 CIS_TAX,
1080 LAST_UPDATE_DATE,
1081 LAST_UPDATED_BY,
1082 LAST_UPDATE_LOGIN,
1083 CREATION_DATE,
1084 CREATED_BY)
1085 values(
1086 l_header_id,
1087 l_org_id,
1088 C_nil_ret_rec.VENDOR_ID,
1089 C_nil_ret_rec.VENDOR_NAME,
1090 C_nil_ret_rec.VENDOR_TYPE_LOOKUP_CODE,
1091 C_nil_ret_rec.FIRST_NAME,
1092 C_nil_ret_rec.SECOND_NAME,
1093 C_nil_ret_rec.LAST_NAME,
1094 C_nil_ret_rec.SALUTATION,
1095 C_nil_ret_rec.TRADING_NAME,
1096 C_nil_ret_rec.UNMATCHED_TAX_FLAG,
1097 C_nil_ret_rec.UNIQUE_TAX_REFERENCE_NUM,
1098 C_nil_ret_rec.COMPANY_REGISTRATION_NUMBER,
1099 C_nil_ret_rec.NATIONAL_INSURANCE_NUMBER,
1100 C_nil_ret_rec.VERIFICATION_NUMBER,
1101 C_nil_ret_rec.TOTAL_PAYMENTS,
1102 C_nil_ret_rec.LABOUR_COST,
1103 C_nil_ret_rec.MATERIAL_COST,
1104 C_nil_ret_rec.TOTAL_DEDUCTIONS,
1105 C_nil_ret_rec.DISCOUNT_AMOUNT,
1106 C_nil_ret_rec.CIS_TAX,
1107 sysdate,
1108 FND_GLOBAL.USER_ID(),
1109 FND_GLOBAL.LOGIN_ID(),
1110 sysdate,
1111 FND_GLOBAL.USER_ID()
1112 );
1113 End loop;
1114 Elsif p_nil_return_flag = 'N' then
1115 log(C_STATE_LEVEL, l_procedure_name, 'p_nil_return_flag is not Y');
1116 For C_n_nil_ret_rec in C_non_nil_ret_lines_info loop
1117 log(C_STATE_LEVEL, l_procedure_name, '================');
1118 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_ID='||C_n_nil_ret_rec.VENDOR_ID);
1119 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_NAME='||C_n_nil_ret_rec.VENDOR_NAME);
1120 log(C_STATE_LEVEL, l_procedure_name, 'VENDOR_TYPE_LOOKUP_CODE='||C_n_nil_ret_rec.VENDOR_TYPE_LOOKUP_CODE);
1121 log(C_STATE_LEVEL, l_procedure_name, 'FIRST_NAME='||C_n_nil_ret_rec.FIRST_NAME);
1122 log(C_STATE_LEVEL, l_procedure_name, 'SECOND_NAME='||C_n_nil_ret_rec.SECOND_NAME);
1123 log(C_STATE_LEVEL, l_procedure_name, 'LAST_NAME='||C_n_nil_ret_rec.LAST_NAME);
1124 log(C_STATE_LEVEL, l_procedure_name, 'SALUTATION='||C_n_nil_ret_rec.SALUTATION);
1125 log(C_STATE_LEVEL, l_procedure_name, 'TRADING_NAME='||C_n_nil_ret_rec.TRADING_NAME);
1126 log(C_STATE_LEVEL, l_procedure_name, 'UNMATCHED_TAX_FLAG='||C_n_nil_ret_rec.UNMATCHED_TAX_FLAG);
1127 log(C_STATE_LEVEL, l_procedure_name, 'UNIQUE_TAX_REFERENCE_NUM='||C_n_nil_ret_rec.UNIQUE_TAX_REFERENCE_NUM);
1128 log(C_STATE_LEVEL, l_procedure_name, 'COMPANY_REGISTRATION_NUMBER='||C_n_nil_ret_rec.COMPANY_REGISTRATION_NUMBER);
1129 log(C_STATE_LEVEL, l_procedure_name, 'NATIONAL_INSURANCE_NUMBER='||C_n_nil_ret_rec.NATIONAL_INSURANCE_NUMBER);
1130 log(C_STATE_LEVEL, l_procedure_name, 'VERIFICATION_NUMBER='||C_n_nil_ret_rec.VERIFICATION_NUMBER);
1131 log(C_STATE_LEVEL, l_procedure_name, 'TOTAL_PAYMENTS='||C_n_nil_ret_rec.TOTAL_PAYMENTS);
1132 log(C_STATE_LEVEL, l_procedure_name, 'LABOUR_COST='||C_n_nil_ret_rec.LABOUR_COST);
1133 log(C_STATE_LEVEL, l_procedure_name, 'MATERIAL_COST='||C_n_nil_ret_rec.MATERIAL_COST);
1134 log(C_STATE_LEVEL, l_procedure_name, 'TOTAL_DEDUCTIONS='||C_n_nil_ret_rec.TOTAL_DEDUCTIONS);
1135 log(C_STATE_LEVEL, l_procedure_name, 'DISCOUNT_AMOUNT='||C_n_nil_ret_rec.DISCOUNT_AMOUNT);
1136 log(C_STATE_LEVEL, l_procedure_name, 'CIS_TAX='||C_n_nil_ret_rec.CIS_TAX);
1137 log(C_STATE_LEVEL, l_procedure_name, '================');
1138 log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_lines_t');
1139 insert into igi_cis_mth_ret_lines_t(
1140 HEADER_ID,
1141 ORG_ID,
1142 VENDOR_ID,
1143 VENDOR_NAME,
1144 VENDOR_TYPE_LOOKUP_CODE,
1145 FIRST_NAME,
1146 SECOND_NAME,
1147 LAST_NAME,
1148 SALUTATION,
1149 TRADING_NAME,
1150 UNMATCHED_TAX_FLAG,
1151 UNIQUE_TAX_REFERENCE_NUM,
1152 COMPANY_REGISTRATION_NUMBER,
1153 NATIONAL_INSURANCE_NUMBER,
1154 VERIFICATION_NUMBER,
1155 TOTAL_PAYMENTS,
1156 LABOUR_COST,
1157 MATERIAL_COST,
1158 TOTAL_DEDUCTIONS,
1159 DISCOUNT_AMOUNT,
1160 CIS_TAX,
1161 LAST_UPDATE_DATE,
1162 LAST_UPDATED_BY,
1163 LAST_UPDATE_LOGIN,
1164 CREATION_DATE,
1165 CREATED_BY)
1166 values(
1167 l_header_id,
1168 l_org_id,
1169 C_n_nil_ret_rec.VENDOR_ID,
1170 C_n_nil_ret_rec.VENDOR_NAME,
1171 C_n_nil_ret_rec.VENDOR_TYPE_LOOKUP_CODE,
1172 C_n_nil_ret_rec.FIRST_NAME,
1173 C_n_nil_ret_rec.SECOND_NAME,
1174 C_n_nil_ret_rec.LAST_NAME,
1175 C_n_nil_ret_rec.SALUTATION,
1176 C_n_nil_ret_rec.TRADING_NAME,
1177 C_n_nil_ret_rec.UNMATCHED_TAX_FLAG,
1178 C_n_nil_ret_rec.UNIQUE_TAX_REFERENCE_NUM,
1179 C_n_nil_ret_rec.COMPANY_REGISTRATION_NUMBER,
1180 C_n_nil_ret_rec.NATIONAL_INSURANCE_NUMBER,
1181 C_n_nil_ret_rec.VERIFICATION_NUMBER,
1182 C_n_nil_ret_rec.TOTAL_PAYMENTS,
1183 C_n_nil_ret_rec.LABOUR_COST,
1184 C_n_nil_ret_rec.MATERIAL_COST,
1185 C_n_nil_ret_rec.TOTAL_DEDUCTIONS,
1186 C_n_nil_ret_rec.DISCOUNT_AMOUNT,
1187 C_n_nil_ret_rec.CIS_TAX,
1188 sysdate,
1189 FND_GLOBAL.USER_ID(),
1190 FND_GLOBAL.LOGIN_ID(),
1191 sysdate,
1192 FND_GLOBAL.USER_ID()
1193 );
1194 End loop;
1195 End if;
1196 --
1197 -- populate the payments table
1198 --
1199 log(C_STATE_LEVEL, l_procedure_name, 'insert into igi_cis_mth_ret_pay_t');
1200 insert into igi_cis_mth_ret_pay_t
1201 (
1202 HEADER_ID,
1203 ORG_ID,
1204 VENDOR_ID,
1205 CHILD_VENDOR_ID,
1206 INVOICE_ID,
1207 INVOICE_PAYMENT_ID,
1208 AMOUNT,
1209 LABOUR_COST,
1210 MATERIAL_COST,
1211 TOTAL_DEDUCTIONS,
1212 DISCOUNT_AMOUNT,
1213 CIS_TAX,--11699868
1214 LAST_UPDATE_DATE,--date
1215 LAST_UPDATED_BY, -- num
1216 LAST_UPDATE_LOGIN,-- num
1217 CREATION_DATE,--date
1218 CREATED_BY --num
1219 )
1220 Select
1221 l_header_id,
1222 l_org_id,
1223 VENDOR_ID,
1224 CHILD_VENDOR_ID,
1225 INVOICE_ID,
1226 INVOICE_PAYMENT_ID,
1227 AMOUNT,
1228 LABOUR_COST,
1229 MATERIAL_COST,
1230 TOTAL_DEDUCTIONS,
1231 DISCOUNT_AMOUNT,
1232 CIS_TAX,--11699868
1233 sysdate,
1234 FND_GLOBAL.USER_ID(),
1235 FND_GLOBAL.LOGIN_ID(),
1236 sysdate,
1237 FND_GLOBAL.USER_ID()
1238 from igi_cis_mth_ret_pay_gt;
1239 -- for debugging
1240 FND_FILE.PUT_LINE(FND_FILE.LOG,'igi_cis_mth_ret_pay_t populate with '||SQL%ROWCOUNT);
1241 commit;
1242 --
1243 -- submit the CP request to run the subcontractor deduction report
1244 --
1245 IF p_mth_ret_mode = 'P' then
1246 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_ret_mode is P');
1247 IF (p_mth_report_template is NULL OR p_mth_report_format is NULL) THEN
1248 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'IGIPMTHR','en','US','PDF');
1249 ELSE
1250 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,p_mth_report_template,'en','US',p_mth_report_format);
1251 END IF;
1252 fnd_request.set_org_id(l_org_id);
1253 IF p_mth_sort_by IS NULL then
1254 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_sort_by IS NULL');
1255 l_request_id := fnd_request.submit_request(application => l_appln_name,
1256 program => l_con_cp,
1257 description => l_con_cp_desc,
1258 start_time => NULL,
1259 sub_request => FALSE,
1260 argument1 => p_period_name,
1261 argument2 => NULL,
1262 argument3 => NULL,
1263 argument4 => 'D', -- Original
1264 argument5 => 'VENDOR_NAME', -- sort
1265 argument6 => 'P', -- prelim
1266 argument7 => 'N', -- del rows
1267 argument8 => 'D', -- detail
1268 argument9 => p_mth_ret_amt_type, -- amount type
1269 argument10 => chr(0));
1270 ELSE
1271 log(C_STATE_LEVEL, l_procedure_name, 'p_mth_sort_by IS NOT NULL');
1272 l_request_id := fnd_request.submit_request(application => l_appln_name,
1273 program => l_con_cp,
1274 description => l_con_cp_desc,
1275 start_time => NULL,
1276 sub_request => FALSE,
1277 argument1 => p_period_name,
1278 argument2 => NULL,
1279 argument3 => NULL,
1280 argument4 => 'D', -- Original
1281 argument5 => p_mth_sort_by, -- sort
1282 argument6 => 'P', -- prelim
1283 argument7 => 'N', -- del rows
1284 argument8 => 'D', -- detail
1285 argument9 => p_mth_ret_amt_type, -- amount type
1286 argument10 => chr(0));
1287 END IF;
1288
1289 IF l_request_id = 0 THEN
1290 RAISE e_request_submit_error;
1291 END IF;
1292 -- bug 5620621 start
1293 -- Added line below while testing 11.5.8
1294 IF (p_mth_report_template is NULL OR p_mth_report_format is NULL) then
1295 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'IGIPMTHR','en','US','PDF');
1296 ELSE
1297 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,p_mth_report_template,'en','US',p_mth_report_format);
1298 END IF;
1299 fnd_request.set_org_id(l_org_id);
1300 IF p_mth_sort_by IS NULL then
1301 l_request_id := fnd_request.submit_request(application => l_appln_name,
1302 program => l_con_cp,
1303 description => l_con_cp_desc,
1304 start_time => NULL,
1305 sub_request => FALSE,
1306 argument1 => p_period_name,
1307 argument2 => NULL,
1308 argument3 => NULL,
1309 argument4 => 'D', -- Original
1310 argument5 => 'VENDOR_NAME', -- sort
1311 argument6 => 'P', -- prelim
1312 argument7 => 'Y', -- del rows
1313 argument8 => 'S', -- summary
1314 argument9 => p_mth_ret_amt_type, -- amount type
1315 argument10 => chr(0));
1316 ELSE
1317 l_request_id := fnd_request.submit_request(application => l_appln_name,
1318 program => l_con_cp,
1319 description => l_con_cp_desc,
1320 start_time => NULL,
1321 sub_request => FALSE,
1322 argument1 => p_period_name,
1323 argument2 => NULL,
1324 argument3 => NULL,
1325 argument4 => 'D', -- Original
1326 argument5 => p_mth_sort_by, -- sort
1327 argument6 => 'P', -- prelim
1328 argument7 => 'Y', -- del rows
1329 argument8 => 'S', -- summary
1330 argument9 => p_mth_ret_amt_type, -- amount type
1331 argument10 => chr(0));
1332 END IF;
1333 IF l_request_id = 0 THEN
1334 RAISE e_request_submit_error;
1335 END IF;
1336 -- bug 5620621 end
1337 retcode := 0; -- CP completed successfully
1338 end if;
1339
1340 l_prelim_hdr_id := l_header_id;
1341 --update the status to C and update who columns
1342 update IGI_CIS_MTH_RET_HDR_T
1343 set --REQUEST_STATUS_CODE = 'C',
1344 PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID(),
1345 PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID(),
1346 PROGRAM_LOGIN_ID = FND_GLOBAL.CONC_LOGIN_ID()
1347 where HEADER_ID = l_prelim_hdr_id;
1348 --call the procedure to mov recods
1349 -- mov the records and then commit
1350
1351 if p_mth_ret_mode = 'F' then
1352 MOVE_TO_HISTORY(l_prelim_hdr_id, 'C');
1353 --- commit and run the report
1354 commit;
1355 FND_FILE.PUT_LINE(FND_FILE.LOG,'Records moved to History tables successfully ');
1356 --
1357 -- submit the CP request to run the subcontractor deduction report
1358 --
1359 IF (p_mth_report_template is NULL OR p_mth_report_format is NULL) THEN
1360 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'IGIPMTHR','en','US','PDF');
1361 ELSE
1362 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,p_mth_report_template,'en','US',p_mth_report_format);
1363 END IF;
1364 fnd_request.set_org_id(l_org_id);
1365 IF p_mth_sort_by is NULL then
1366 l_request_id := fnd_request.submit_request(application => l_appln_name,
1367 program => l_con_cp,
1368 description => l_con_cp_desc,
1369 start_time => NULL,
1370 sub_request => FALSE,
1371 argument1 => p_period_name,
1372 argument2 => NULL,
1373 argument3 => NULL,
1374 argument4 => 'O', -- Original
1375 argument5 => 'VENDOR_NAME', -- sort
1376 argument6 => 'F',
1377 argument7 => 'Y', --delete temp
1378 argument8 => 'S',
1379 argument9 => p_mth_ret_amt_type, --amount type
1380 argument10 => chr(0));
1381 ELSE
1382 l_request_id := fnd_request.submit_request(application => l_appln_name,
1383 program => l_con_cp,
1384 description => l_con_cp_desc,
1385 start_time => NULL,
1386 sub_request => FALSE,
1387 argument1 => p_period_name,
1388 argument2 => NULL,
1389 argument3 => NULL,
1390 argument4 => 'O', -- Original
1391 argument5 => p_mth_sort_by, -- sort
1392 argument6 => 'F',
1393 argument7 => 'Y', --delete temp
1394 argument8 => 'S',
1395 argument9 => p_mth_ret_amt_type, --amount type
1396 argument10 => chr(0));
1397 END IF;
1398 IF l_request_id = 0 THEN
1399 RAISE e_request_submit_error;
1400 END IF;
1401 retcode := 0; -- CP completed successfully
1402 end if;
1403 --
1404 -- handling exception
1405 --
1406 log(C_STATE_LEVEL, l_procedure_name, 'END');
1407 Exception
1408 when e_request_submit_error then
1409 errbuf := 'Error while calling the deduction report';
1410 retcode := 2;
1411 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION 1='||errbuf);
1412 when e_validation_exception then
1413 -- setting out parameters
1414 errbuf := l_err_all_msg;
1415 retcode := 2;
1416 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION 2='||errbuf);
1417 when e_prelim_mand_error then
1418 errbuf := l_err_msg ;
1419 retcode := 2;
1420 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION 3='||errbuf);
1421 when e_param_mismatch_error then
1422 errbuf := l_err_msg;
1423 retcode := 2;
1424 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION 4='||errbuf);
1425 when e_rep_ent_not_found_error then
1426 errbuf := l_err_msg;
1427 retcode := 2;
1428 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION 5='||errbuf);
1429 when others then
1430 -- for debugging
1431 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in processing' || sqlerrm);
1432 -- rollback the insert and updates
1433 rollback;
1434 errbuf := sqlerrm;
1435 retcode := 2;
1436 log(C_STATE_LEVEL, l_procedure_name, 'END EXCEPTION 6='||errbuf);
1437 end POPULATE_MTH_RET_DETAILS;
1438
1439 PROCEDURE MOVE_TO_HISTORY(p_header_id IN number,
1440 p_request_status_code IN varchar2)
1441 is
1442 Begin
1443 insert into igi_cis_mth_ret_hdr_h
1444 (HEADER_ID,
1445 ORG_ID,
1446 CIS_SENDER_ID,
1447 TAX_OFFICE_NUMBER,
1448 PAYE_REFERENCE,
1449 REQUEST_ID,
1450 REQUEST_STATUS_CODE,
1451 PROGRAM_APPLICATION_ID,
1452 PROGRAM_ID,
1453 PROGRAM_LOGIN_ID,
1454 UNIQUE_TAX_REFERENCE_NUM,
1455 ACCOUNTS_OFFICE_REFERENCE,
1456 PERIOD_NAME,
1457 PERIOD_ENDING_DATE,
1458 NIL_RETURN_FLAG,
1459 EMPLOYMENT_STATUS_FLAG,
1460 SUBCONT_VERIFY_FLAG,
1461 INFORMATION_CORRECT_FLAG,
1462 INACTIVITY_INDICATOR,
1463 LAST_UPDATE_DATE,
1464 LAST_UPDATED_BY,
1465 LAST_UPDATE_LOGIN,
1466 CREATION_DATE,
1467 CREATED_BY)
1468 select HEADER_ID,
1469 ORG_ID,
1470 CIS_SENDER_ID,
1471 TAX_OFFICE_NUMBER,
1472 PAYE_REFERENCE,
1473 REQUEST_ID,
1474 p_request_status_code,--REQUEST_STATUS_CODE,
1475 PROGRAM_APPLICATION_ID,
1476 PROGRAM_ID,
1477 PROGRAM_LOGIN_ID,
1478 UNIQUE_TAX_REFERENCE_NUM,
1479 ACCOUNTS_OFFICE_REFERENCE,
1480 PERIOD_NAME,
1481 PERIOD_ENDING_DATE,
1482 NIL_RETURN_FLAG,
1483 EMPLOYMENT_STATUS_FLAG,
1484 SUBCONT_VERIFY_FLAG,
1485 INFORMATION_CORRECT_FLAG,
1486 INACTIVITY_INDICATOR,
1487 sysdate, --LAST_UPDATE_DATE
1488 FND_GLOBAL.USER_ID(),--LAST_UPDATED_BY
1489 FND_GLOBAL.LOGIN_ID(),--LAST_UPDATE_LOGIN
1490 sysdate, --CREATION_DATE
1491 FND_GLOBAL.USER_ID() --CREATED_BY
1492 from igi_cis_mth_ret_hdr_t
1493 where HEADER_ID = p_header_id ;
1494
1495 insert into igi_cis_mth_ret_lines_h
1496 (HEADER_ID,
1497 ORG_ID,
1498 VENDOR_ID,
1499 VENDOR_NAME,
1500 VENDOR_TYPE_LOOKUP_CODE,
1501 FIRST_NAME,
1502 SECOND_NAME,
1503 LAST_NAME,
1504 SALUTATION,
1505 TRADING_NAME,
1506 UNMATCHED_TAX_FLAG,
1507 UNIQUE_TAX_REFERENCE_NUM,
1508 COMPANY_REGISTRATION_NUMBER,
1509 NATIONAL_INSURANCE_NUMBER,
1510 VERIFICATION_NUMBER,
1511 TOTAL_PAYMENTS,
1512 LABOUR_COST,
1513 MATERIAL_COST,
1514 TOTAL_DEDUCTIONS,
1515 DISCOUNT_AMOUNT,
1516 CIS_TAX,
1517 LAST_UPDATE_DATE,
1518 LAST_UPDATED_BY,
1519 LAST_UPDATE_LOGIN,
1520 CREATION_DATE,
1521 CREATED_BY)
1522 select HEADER_ID,
1523 ORG_ID,
1524 VENDOR_ID,
1525 VENDOR_NAME,
1526 VENDOR_TYPE_LOOKUP_CODE,
1527 FIRST_NAME,
1528 SECOND_NAME,
1529 LAST_NAME,
1530 SALUTATION,
1531 TRADING_NAME,
1532 UNMATCHED_TAX_FLAG,
1533 UNIQUE_TAX_REFERENCE_NUM,
1534 COMPANY_REGISTRATION_NUMBER,
1535 NATIONAL_INSURANCE_NUMBER,
1536 VERIFICATION_NUMBER,
1537 TOTAL_PAYMENTS,
1538 LABOUR_COST,
1539 MATERIAL_COST,
1540 TOTAL_DEDUCTIONS,
1541 DISCOUNT_AMOUNT,
1542 CIS_TAX,
1543 sysdate, --LAST_UPDATE_DATE
1544 FND_GLOBAL.USER_ID(),--LAST_UPDATED_BY
1545 FND_GLOBAL.LOGIN_ID(),--LAST_UPDATE_LOGIN
1546 sysdate, --CREATION_DATE
1547 FND_GLOBAL.USER_ID() --CREATED_BY
1548 from igi_cis_mth_ret_lines_t
1549 where HEADER_ID = p_header_id ;
1550
1551 insert into igi_cis_mth_ret_pay_h
1552 (HEADER_ID,
1553 ORG_ID,
1554 VENDOR_ID,
1555 CHILD_VENDOR_ID,
1556 INVOICE_ID,
1557 INVOICE_PAYMENT_ID,
1558 AMOUNT,
1559 LAST_UPDATE_DATE,
1560 LAST_UPDATED_BY,
1561 LAST_UPDATE_LOGIN,
1562 CREATION_DATE,
1563 CREATED_BY,
1564 LABOUR_COST,
1565 MATERIAL_COST,
1566 TOTAL_DEDUCTIONS,
1567 DISCOUNT_AMOUNT,
1568 CIS_TAX)
1569 Select HEADER_ID,
1570 ORG_ID,
1571 VENDOR_ID,
1572 CHILD_VENDOR_ID,
1573 INVOICE_ID,
1574 INVOICE_PAYMENT_ID,
1575 AMOUNT,
1576 sysdate, --LAST_UPDATE_DATE
1577 FND_GLOBAL.USER_ID(),--LAST_UPDATED_BY
1578 FND_GLOBAL.LOGIN_ID(),--LAST_UPDATE_LOGIN
1579 sysdate, --CREATION_DATE
1580 FND_GLOBAL.USER_ID(), --CREATED_BY
1581 LABOUR_COST,
1582 MATERIAL_COST,
1583 TOTAL_DEDUCTIONS,
1584 DISCOUNT_AMOUNT,
1585 CIS_TAX
1586 from igi_cis_mth_ret_pay_t
1587 where HEADER_ID = p_header_id;
1588 -- delete the records from interface tables
1589 delete from igi_cis_mth_ret_hdr_t where header_id = p_header_id;
1590 delete from igi_cis_mth_ret_lines_t where header_id = p_header_id;
1591 delete from igi_cis_mth_ret_pay_t where header_id = p_header_id;
1592 End MOVE_TO_HISTORY;
1593
1594 PROCEDURE RUN_MTH_RET_REPORT(p_period_name IN varchar2,
1595 p_orig_dub IN varchar2,
1596 p_sort_by IN varchar2,
1597 p_ret_mode IN varchar2,
1598 p_del_preview IN varchar2,
1599 p_report_lev IN varchar2,--bug 5620621
1600 p_request_id OUT NOCOPY integer)
1601 is
1602 l_procedure_name VARCHAR2(100):='.RUN_MTH_RET_REPORT';
1603 l_request_id number;
1604 l_appln_name varchar2(10) := 'IGI';
1605 l_con_cp varchar2(15) := 'IGIPMTHR_XMLP';
1606 l_con_cp_desc varchar2(200) := 'IGI : CIS2007 Monthly Returns Report';
1607 l_xml_layout boolean;
1608
1609 Begin
1610 l_xml_layout := FND_REQUEST.ADD_LAYOUT(l_appln_name,'IGIPMTHR','en','US','PDF');
1611 fnd_request.set_org_id(mo_global.get_current_org_id);
1612 l_request_id := fnd_request.submit_request(application => l_appln_name,
1613 program => l_con_cp,
1614 description => l_con_cp_desc,
1615 start_time => NULL,
1616 sub_request => FALSE,
1617 argument1 => p_period_name,
1618 argument2 => NULL,
1619 argument3 => NULL,
1620 argument4 => p_orig_dub, -- Original
1621 argument5 => p_sort_by, -- sort
1622 argument6 => p_ret_mode,
1623 argument7 => p_del_preview,
1624 argument8 => p_report_lev, --'S',bug 5620621
1625 argument9 => 'P', --Positive amount ER6137652
1626 argument10 => chr(0));
1627 p_request_id := l_request_id;
1628 End RUN_MTH_RET_REPORT;
1629
1630 /* PROCEDURE POST_REPORT_DELETE(p_request_id in number,
1631 p_header_id in number)
1632 is
1633 l_phase VARCHAR2(100);
1634 l_status VARCHAR2(100);
1635 l_dev_phase VARCHAR2(100);
1636 l_dev_status VARCHAR2(100);
1637 l_message VARCHAR2(1000);
1638 e_request_wait_error exception;
1639 Begin
1640 IF NOT fnd_concurrent.wait_for_request(p_request_id,
1641 20, -- interval seconds
1642 0, -- max wait seconds
1643 l_phase,
1644 l_status,
1645 l_dev_phase,
1646 l_dev_status,
1647 l_message) THEN
1648 RAISE e_request_wait_error;
1649 END IF;
1650 IF l_dev_phase = 'COMPLETE' THEN
1651 -- delete records
1652 delete from igi_cis_mth_ret_hdr_t where header_id = p_header_id;
1653 delete from igi_cis_mth_ret_lines_t where header_id = p_header_id;
1654 delete from igi_cis_mth_ret_pay_t where header_id = p_header_id;
1655 commit;
1656 END IF;
1657 End POST_REPORT_DELETE;*/
1658 BEGIN
1659 init;
1660 END IGI_CIS2007_IGIPMTHR_PKG;