AP Update Income Tax Details Utility - draft

Categories: BI Publisher
Columns: Vendor Name R, Vendor Num R, Site Id R, Site Code R, Region R, Vendor Id R, Count R, C Update Regions ...
Application: Payables
Source: Update Income Tax Details Utility (XML) - Not Supported: Reserved For Future Use
            SELECT  substr(pv.vendor_name,1,30) vendor_name_R,
                            substr(pv.segment1,1,20) vendor_num_R,
                            pvs.vendor_site_id site_id_R,
                            substr(pvs.vendor_site_code, 1, 13) site_code_R,
                            pvs.state region_R,
                            pv.vendor_id vendor_id_R,
                            count(d.distribution_line_number) count_R,
                AP_APXT7UTT_XMLP_PKG.c_update_regionsformula(pvs.state, pv.vendor_id, pvs.vendor_site_id) C_update_regions
            FROM    AP_Invoice_Distributions d, AP_Invoices i,
                            PO_Vendor_Sites pvs, PO_Vendors pv,
                            AP_Invoice_Payments ip
                    WHERE   (pv.vendor_id = :P_vendor_option OR nvl(:P_vendor_option, -1) = -1)
                    AND   (     (:P_region_code not in ('VENDOR SITE', 'INCOME TAX REPORTING SITE')                             AND NVL(d.income_tax_region, 'DuMmY') != :P_region_code)
                              OR (:P_region_code = 'VENDOR SITE'
                                        AND NVL(d.income_tax_region, 'DuMmY') != pvs.state
                                        AND pvs.state in (SELECT region_short_name
                                                                   FROM   ap_income_tax_regions
                                                                   WHERE  sysdate < nvl(inactive_date,sysdate+1)
                                                                   AND    sysdate >= nvl(active_date,sysdate)))
                               OR(:P_region_code = 'INCOME TAX REPORTING SITE'                                                          AND NVL(d.income_tax_region, 'DuMmY') <>
																 (select pvs2.state
																	from po_vendor_sites pvs2
																	where pvs2.tax_reporting_site_flag= 'Y'
																	and pvs2.vendor_id=pv.vendor_id
																	and pvs2.state in
																	(SELECT region_short_name
																		FROM   ap_income_tax_regions
																		WHERE  sysdate < nvl(inactive_date,sysdate+1)
																		AND    sysdate >= nvl(active_date,sysdate))))
                    AND     pv.vendor_id = i.vendor_id
                    AND     pvs.vendor_site_id = i.vendor_site_id
                    AND     i.invoice_id = d.invoice_id
                    AND     i.invoice_id = ip.invoice_id
                    AND    ip.accounting_date between :P_start_date and :P_end_date
                    AND     nvl(pv.federal_reportable_flag,'N') = 'Y'
                    GROUP BY pv.vendor_id, pv.vendor_name, pvs.vendor_site_id,
                             pvs.vendor_site_code, pv.segment1, pvs.state
                    ORDER BY upper(pv.vendor_name), upper(pvs.vendor_site_code)
Parameter Name SQL text Validation
Supplier Name (skip for All)
LOV Oracle
Start Date
End Date
Income Tax Type Action
LOV Oracle
Income Tax Region Action
LOV Oracle
Update Income Tax Region To
LOV Oracle