[Home] [Help]
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;
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;
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 */
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
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.
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
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.
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
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 (
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 (