ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 46E051D2A107552EE0630100007FA2CE select x.supplier_name, x.supplier_number, x.tax_reporting_site, x.tax_id, x.income_tax_type, xxen_util.meaning(x.income_tax_type,'1099 MISC',200) income_tax_type_meaning, x.income_tax_region, &detail_columns round(sum(x.distribution_amount),2) distribution_total, round(sum(x.withheld_amount),2) withheld_amount, round(sum(x.payment_amount),2) payment_amount from ( select aps.vendor_name supplier_name, aps.segment1 supplier_number, assa.vendor_site_code tax_reporting_site, decode( replace(replace(nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)),'-',''),' ',''), '000000000','', nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) ) tax_id, aida.type_1099 income_tax_type, aida.income_tax_region, aia.invoice_num, aia.invoice_date, aia.invoice_amount, aca.check_number, aca.check_date, -- distribution amount (prorated, before MISC4 sign adjustment) decode(aia.cancelled_amount,null,aida.amount, decode(greatest(aida.accounting_date,:p_to_date+1),aida.accounting_date,0, decode(least(aida.accounting_date,:p_from_date-1),aida.accounting_date,0,aida.amount))) / decode(ap_utilities_pkg.net_invoice_amount(aia.invoice_id),0,1,ap_utilities_pkg.net_invoice_amount(aia.invoice_id)) * decode(aipa.amount,0,decode((select count(*) from ap_invoice_payments_all aipa2 where aipa2.invoice_id=aia.invoice_id),null,1,0),aipa.amount) distribution_amount, -- withheld amount (MISC4 only, as positive value) case when aida.type_1099='MISC4' then decode(aia.cancelled_amount,null,aida.amount, decode(greatest(aida.accounting_date,:p_to_date+1),aida.accounting_date,0, decode(least(aida.accounting_date,:p_from_date-1),aida.accounting_date,0,aida.amount))) / decode(ap_utilities_pkg.net_invoice_amount(aia.invoice_id),0,1,ap_utilities_pkg.net_invoice_amount(aia.invoice_id)) * decode(aipa.amount,0,decode((select count(*) from ap_invoice_payments_all aipa2 where aipa2.invoice_id=aia.invoice_id),null,1,0),aipa.amount) end withheld_amount, -- payment amount (with MISC4 sign flip for net reporting) decode(aida.type_1099,'MISC4',-1,1) * decode(aia.cancelled_amount,null,aida.amount, decode(greatest(aida.accounting_date,:p_to_date+1),aida.accounting_date,0, decode(least(aida.accounting_date,:p_from_date-1),aida.accounting_date,0,aida.amount))) / decode(ap_utilities_pkg.net_invoice_amount(aia.invoice_id),0,1,ap_utilities_pkg.net_invoice_amount(aia.invoice_id)) * decode(aipa.amount,0,decode((select count(*) from ap_invoice_payments_all aipa2 where aipa2.invoice_id=aia.invoice_id),null,1,0),aipa.amount) payment_amount from ap_reporting_entities_all area, ap_reporting_entity_lines_all arela, hr_operating_units hou, gl_ledgers gl, gl_code_combinations gcc, ap_suppliers aps, (select x.* from (select assa.*, row_number() over (partition by assa.vendor_id, assa.org_id order by case when assa.tax_reporting_site_flag='Y' then 1 else 2 end, assa.vendor_site_code) row_number from ap_supplier_sites_all assa) x where x.row_number=1) assa, per_all_people_f papf, ap_invoices_all aia, ap_invoice_distributions_all aida, ap_invoice_payments_all aipa, ap_checks_all aca, ce_bank_acct_uses_all cbaua, ce_bank_accounts cba where 1=1 and aps.vendor_id=assa.vendor_id(+) and area.org_id=assa.org_id(+) and area.tax_entity_id=arela.tax_entity_id and area.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id and gcc.chart_of_accounts_id=gl.chart_of_accounts_id and gcc.&balancing_segment=arela.balancing_segment_value and decode(:p_query_driver,'INV',aida.dist_code_combination_id,cba.asset_code_combination_id)=gcc.code_combination_id and aps.vendor_id=aia.vendor_id and aps.vendor_id in (select a1td.vendor_id from ap_1099_tape_data a1td) and nvl(aps.employee_id,-99)=papf.person_id(+) and (sysdate between papf.effective_start_date and papf.effective_end_date or papf.person_id is null) and aia.invoice_id=aida.invoice_id and aida.type_1099 is not null and aia.invoice_id=aipa.invoice_id and aipa.check_id=aca.check_id and aca.ce_bank_acct_use_id=cbaua.bank_acct_use_id and cbaua.bank_account_id=cba.bank_account_id and aca.org_id=area.org_id and (aca.void_date is null or aca.void_date not between :p_from_date and :p_to_date) and aipa.accounting_date>=:p_from_date and aipa.accounting_date<:p_to_date+1 and (:p_federal_reportable='N' or aps.federal_reportable_flag='Y') ) x group by x.supplier_name, x.supplier_number, x.tax_reporting_site, x.tax_id, x.income_tax_type, x.income_tax_region &group_by_columns order by x.supplier_name, x.income_tax_type Y US AP 1099 Payments 1099 Payments report showing payments made to 1099 reportable suppliers. This is the Blitz Report equivalent of the Oracle standard 1099 Payments Report (APXTRRVT). The report lists suppliers from the 1099 tape data (vendors processed for 1099 reporting) along with their payment totals within the specified date range. Key features: - Uses ap_1099_tape_data to filter vendors processed for 1099 reporting - Validates against Tax Reporting Entity and balancing segments - Calculates payment amounts using Oracle AP_UTILITIES_PKG.Net_Invoice_Amount - Determines tax reporting site (site with tax_reporting_site_flag=Y or first alphabetical site) - Handles void checks by excluding payments voided within the date range - Supports Summary (totals by supplier/type) and Detail (individual checks/invoices) modes - MISC4 (Backup Withholding) amounts tracked separately as Withheld Amount - Distribution Total shows gross amounts before withholding adjustments - Payment Amount shows net reportable amounts (with MISC4 as negative) - Query Driver parameter controls balancing segment matching (INV=invoice distribution, PAY=bank cash account) - Employee vendors use national_identifier from per_all_people_f for tax ID - Tax ID cleanup removes dashes, spaces, and treats 000000000 as blank Enginatics &balancing_segment &detail_columns &group_by_columns 1=1 :p_federal_reportable :p_from_date :p_query_driver :p_to_date 1 10 &detail_columns LOV Oracle AP_SRS_RUN_REPORT_OPTION Y select lookup_code id, displayed_field value, description description from ap_lookup_codes where lookup_type='1099 PAYMENT REPORT OPTION' order by value,description Summary Detail Y US Reporting Option 2 &detail_columns x.invoice_num, x.invoice_date, x.invoice_amount, x.check_number, x.check_date, Detail US Reporting Option 3 &group_by_columns , x.invoice_num, x.invoice_date, x.invoice_amount, x.check_number, x.check_date Detail US Reporting Option 4 &group_by_columns Summary US Reporting Option 5 20 :p_from_date Date Y US From Payment Date 6 30 :p_to_date Date last_day(:$flex$.from_payment_date) Y US To Payment Date 7 40 1=1 area.tax_entity_id=:p_tax_entity_id LOV Oracle AP_SRS_TAX_REPORTING_ENTITIES Y select tax_entity_id id, entity_name value, null description from ap_reporting_entities order by value,description Y US Tax Reporting Entity 8 &balancing_segment select lower(fsav.application_column_name) from ap_reporting_entities_all area, hr_operating_units hou, gl_ledgers gl, fnd_segment_attribute_values fsav where area.tax_entity_id=:p_tax_entity_id and area.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id and gl.chart_of_accounts_id=fsav.id_flex_num and fsav.application_id=101 and fsav.id_flex_code='GL#' and fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y' and rownum=1 US Tax Reporting Entity 9 50 1=1 aps.vendor_name=:p_supplier_name LOV Oracle AP_SRS_SUPPLIER_NAME_1099 Y select vendor_id id, v.vendor_name value, null description from po_vendors v where :$flex$.ap_srs_group_by='Vendor' order by v.vendor_name US Supplier Name 10 60 1=1 aida.income_tax_region=:p_income_tax_region LOV Oracle AP_SRS_INCOME_TAX_REGIONS Y select region_short_name id, region_short_name value, region_long_name description from ap_income_tax_regions where sysdate between nvl(active_date,sysdate) and nvl(inactive_date,sysdate) order by upper(region_long_name) US Income Tax Region 11 70 :p_federal_reportable LOV Oracle AP_SRS_YES_NO_MAND Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='YES_NO' order by value,description N Y US Federal Reportable Only 12 80 :p_query_driver LOV custom Y select 'PAY' value, 'Bank Cash Account' description from dual union all select 'INV' value, 'Invoice Distribution Account' description from dual PAY Y US Query Driver Determines which account to use for balancing segment matching: PAY uses the bank cash account, INV uses the invoice distribution charge account :$flex$.from_payment_date From Payment Date Y To Payment Date 547F3822032ABF1E78D38D09F2AAA3B5 Income Tax Region Detail ERIC.CLEGG 12 CHECK_DATE 11 CHECK_NUMBER 1 INCOME_TAX_REGION 1 2 INCOME_TAX_TYPE 2 3 INCOME_TAX_TYPE_MEANING 9 INVOICE_DATE 6 8 INVOICE_NUM 7 10 PAYMENT_AMOUNT 4 SUPPLIER_NAME 3 5 SUPPLIER_NUMBER 4 6 TAX_ID 7 TAX_REPORTING_SITE 5 Reporting Option Detail S Site 660EF7C8504BF3E248AB0783B1978F58 Income Tax Region Summary ERIC.CLEGG 1 INCOME_TAX_REGION 1 2 INCOME_TAX_TYPE 2 3 INCOME_TAX_TYPE_MEANING 4 PAYMENT_AMOUNT INCOME_TAX_REGION ROW 1 INCOME_TAX_TYPE ROW 2 PAYMENT_AMOUNT VALUE 1 SUM S Site F19A5042F6A5D8396446A7A27EBA4A4D Supplier Detail ERIC.CLEGG 11 CHECK_DATE 10 CHECK_NUMBER 5 INCOME_TAX_TYPE 4 6 INCOME_TAX_TYPE_MEANING 8 INVOICE_DATE 5 7 INVOICE_NUM 6 9 PAYMENT_AMOUNT 1 SUPPLIER_NAME 1 2 SUPPLIER_NUMBER 2 3 TAX_ID 4 TAX_REPORTING_SITE 3 Reporting Option Detail S Site 2332323B86F7443D7B2179E3A91A5958 Supplier Summary ERIC.CLEGG 5 INCOME_TAX_TYPE 4 6 INCOME_TAX_TYPE_MEANING 7 PAYMENT_AMOUNT SUM 1 SUPPLIER_NAME 1 2 SUPPLIER_NUMBER 2 3 TAX_ID 4 TAX_REPORTING_SITE 3 S Site ERIC.CLEGG F19A5042F6A5D8396446A7A27EBA4A4D ANDY.HAACK 547F3822032ABF1E78D38D09F2AAA3B5