ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 46E051D2A107552EE0630100007FA2CEY 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 &il_reg_columns &il_tax_columns 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 &il_tax_columns_inner_amt &il_tax_columns_inner_rate &il_tax_columns_inner_tp 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 &il_from 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') &il_where ) 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 Added India GST columns, displayed only when India Localization is installed. 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 When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CGST Rate, SGST Rate, IGST Rate, GST Supplier Registration No, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code. Enginatics &balancing_segment &detail_columns &group_by_columns &il_from &il_reg_columns &il_tax_columns &il_tax_columns_inner_amt &il_tax_columns_inner_rate &il_tax_columns_inner_tp &il_where 1=1 :p_federal_reportable :p_from_date :p_query_driver :p_to_date 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_from ,(select jpr.party_id, jpr.party_site_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id, jpr.party_site_id) il_reg_site ,(select jpr.party_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin_hdr, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id) il_reg_hdr ,(select jtlv.trx_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, max(jtlv.self_assessed_flag) reverse_charge_flag, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_trx_curr)) custom_amount, sum(decode(jrav.reporting_code,null,jtlv.rounded_tax_amt_trx_curr)) unclassified_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc where jrav.entity_id(+)=jtlv.tax_type_id and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.det_factor_id=jtdf.det_factor_id(+) and jtdf.application_id(+)=200 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=200 group by jtlv.trx_id ) il_tax Y US India Localization 3 &il_reg_columns ,max(x.gst_registration_no) "GST Registration No", max(x.gst_pan_no) "GST PAN No", max(x.gst_tan_no) "GST TAN No", Y US India Localization 4 &il_tax_columns sum(x.cgst_amount) "CGST Amount", sum(x.sgst_amount) "SGST Amount", sum(x.igst_amount) "IGST Amount", max(x.cgst_tax_rate) "CGST Rate", max(x.sgst_tax_rate) "SGST Rate", max(x.igst_tax_rate) "IGST Rate", max(x.gst_supplier_registration_no) "GST Supplier Registration No", sum(x.custom_amount) custom_amount, sum(x.unclassified_amount) "Unclassified Tax Amount", sum(x.taxable_value) taxable_value, max(x.hsn_sac_code) "HSN SAC Code" Y US India Localization 5 &il_tax_columns_inner_amt ,il_tax.cgst_amount, il_tax.sgst_amount, il_tax.igst_amount, il_tax.custom_amount, il_tax.unclassified_amount, il_tax.taxable_value, il_tax.hsn_sac_code Y US India Localization 6 &il_tax_columns_inner_rate ,il_tax.cgst_tax_rate, il_tax.sgst_tax_rate, il_tax.igst_tax_rate Y US India Localization 7 &il_tax_columns_inner_tp ,nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) gst_supplier_registration_no, nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) gst_registration_no, nvl(il_reg_site.pan_site,nvl(il_reg_site.sec_pan_site,nvl(il_reg_hdr.pan_hdr,il_reg_hdr.sec_pan_hdr))) gst_pan_no, nvl(il_reg_site.tan_site,nvl(il_reg_site.sec_tan_site,nvl(il_reg_hdr.tan_hdr,il_reg_hdr.sec_tan_hdr))) gst_tan_no Y US India Localization 8 &il_where and aia.vendor_site_id=il_reg_site.party_site_id(+) and aia.vendor_id=il_reg_hdr.party_id(+) and aia.invoice_id=il_tax.trx_id(+) Y US India Localization 9 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 10 &detail_columns x.invoice_num, x.invoice_date, x.invoice_amount, x.check_number, x.check_date, Detail US Reporting Option 11 &group_by_columns , x.invoice_num, x.invoice_date, x.invoice_amount, x.check_number, x.check_date Detail US Reporting Option 12 &group_by_columns Summary US Reporting Option 13 20 :p_from_date Date Y US From Payment Date 14 30 :p_to_date Date last_day(:$flex$.from_payment_date) Y US To Payment Date 15 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 16 &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 17 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 18 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 19 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 20 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 547F3822032ABF1E78D38D09F2AAA3B5 Income Tax Region Detail ERIC.CLEGG 16 CGST Amount 19 CGST Rate 12 CHECK_DATE 11 CHECK_NUMBER 14 GST PAN No 13 GST Registration No 22 GST Supplier Registration No 15 GST TAN No 24 HSN SAC Code 18 IGST Amount 21 IGST Rate 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 17 SGST Amount 20 SGST Rate 4 SUPPLIER_NAME 3 5 SUPPLIER_NUMBER 4 6 TAX_ID 7 TAX_REPORTING_SITE 5 23 Unclassified Tax Amount Reporting Option Detail S Site 660EF7C8504BF3E248AB0783B1978F58 Income Tax Region Summary ERIC.CLEGG 8 CGST Amount 11 CGST Rate 6 GST PAN No 5 GST Registration No 14 GST Supplier Registration No 7 GST TAN No 16 HSN SAC Code 10 IGST Amount 13 IGST Rate 1 INCOME_TAX_REGION 1 2 INCOME_TAX_TYPE 2 3 INCOME_TAX_TYPE_MEANING 4 PAYMENT_AMOUNT 9 SGST Amount 12 SGST Rate 15 Unclassified Tax Amount INCOME_TAX_REGION ROW 2 INCOME_TAX_TYPE ROW 5 PAYMENT_AMOUNT VALUE 3 SUM S Site F19A5042F6A5D8396446A7A27EBA4A4D Supplier Detail ERIC.CLEGG 15 CGST Amount 18 CGST Rate 11 CHECK_DATE 10 CHECK_NUMBER 13 GST PAN No 12 GST Registration No 21 GST Supplier Registration No 14 GST TAN No 23 HSN SAC Code 17 IGST Amount 20 IGST Rate 5 INCOME_TAX_TYPE 4 6 INCOME_TAX_TYPE_MEANING 8 INVOICE_DATE 5 7 INVOICE_NUM 6 9 PAYMENT_AMOUNT 16 SGST Amount 19 SGST Rate 1 SUPPLIER_NAME 1 2 SUPPLIER_NUMBER 2 3 TAX_ID 4 TAX_REPORTING_SITE 3 22 Unclassified Tax Amount Reporting Option Detail S Site 2332323B86F7443D7B2179E3A91A5958 Supplier Summary ERIC.CLEGG 11 CGST Amount 14 CGST Rate 9 GST PAN No 8 GST Registration No 17 GST Supplier Registration No 10 GST TAN No 19 HSN SAC Code 13 IGST Amount 16 IGST Rate 5 INCOME_TAX_TYPE 4 6 INCOME_TAX_TYPE_MEANING 7 PAYMENT_AMOUNT SUM 12 SGST Amount 15 SGST Rate 1 SUPPLIER_NAME 1 2 SUPPLIER_NUMBER 2 3 TAX_ID 4 TAX_REPORTING_SITE 3 18 Unclassified Tax Amount S Site ERIC.CLEGG F19A5042F6A5D8396446A7A27EBA4A4D ANDY.HAACK 547F3822032ABF1E78D38D09F2AAA3B5