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