AP 1099 Electronic Media XML Publisher

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: 1099 Electronic Media XML Publisher Report
Application: Payables
Source: 1099 Electronic Media XML Publisher
Short Name: APXT7CMT2
DB package: AP_APXT7CMT2_XMLP_PKG
SELECT rownum N_rownum, x.*, 
    AP_APXT7CMT2_XMLP_PKG.c_error_dummyformula(ERROR_TEXT, VENDOR_NAME) C_error_dummy, 
    AP_APXT7CMT2_XMLP_PKG.cf_negative_miscformula(MISC2, MISC3, MISC4, MISC5, MISC6, MISC7, MISC8, MISC9, MISC10, MISC11/*Bug33372456*/, MISC13, MISC14, MISC15A, MISC15B, ERROR_TEXT, VENDOR_NAME) CF_NEGATIVE_MISC,
    AP_APXT7CMT2_XMLP_PKG.c_b_recordformula(MISC7, PAYEE_NAME_CONTROL, TIN_TYPE, EIN, VENDOR_ID, MISC1, MISC2, MISC3, MISC4, MISC5, MISC6, MISC8, MISC10, MISC11/*Bug33372456*/, MISC13, MISC14, MISC15A, MISC15B, FOREIGN_PAYEE_FLAG, TAX_REPORTING_NAME, VENDOR_NAME, VENDOR_LINE, VENDOR_CITY, VENDOR_STATE, VENDOR_ZIP, REGION_CODE, MISC9, NEC4) C_B_record
FROM (
SELECT   rownum C_rownum,        pvw.vendor_name vendor_name,
       pvw.tax_reporting_name tax_reporting_name,
       rpad(pvw.name_control, 4) payee_name_control,
       regexp_replace(pvw.address_line1 || ' ' || pvw.address_line2 || ' ' ||
       pvw.address_line3, '[^0-9A-Za-z'' @#$%^&*()_+-=,!\`~{}./?:";''|[][^\]]*', '') vendor_line,
       decode(substr(pvw.organization_type_lookup_code, 1, 7),
              'FOREIGN',
              substr(pvw.city, 1, 21) || ' ' || substr(pvw.state, 1, 9) || ' ' ||
              substr(ft.territory_short_name, 1, 10),
              rpad(substr(pvw.city, 1, 40), 40)) vendor_city,
       rpad(substr(pvw.state, 1, 2), 2) vendor_state,
       rpad(substr(replace(replace(pvw.zip, '-', ''), ' ', ''), 1, 9), 9) vendor_zip,
       decode(pvw.address_line1, '', 'No Address Line 1. ') ||
       decode(substr(pvw.organization_type_lookup_code, 1, 7),
              'FOREIGN',
              decode(pvw.country, '', 'No country. '),
              decode(length(replace(replace(nvl(pvw.national_identifier,
                                                nvl(pvw.individual_1099,
                                                    pvw.num_1099)),
                                            '-',
                                            ''),
                                    ' ',
                                    '')),
                     9,
                     '',
                     'Bad EIN/SSN. ') || decode(pvw.city, '', 'No city. ') ||
              decode(nvl(pvw.province, pvw.state), '', 'No state. ') ||
              decode(replace(replace(pvw.zip, '-', ''), ' ', ''),
                     '',
                     'No postal code. ')) error_text,
       sum(TD.misc1) misc1,
       sum(TD.misc2) misc2,
       sum(TD.misc3) misc3,
       sum(TD.misc4) misc4,
       sum(TD.misc5) misc5,
       sum(TD.misc6) misc6,
       sum(TD.misc7 + TD.misc15aT + TD.misc15b) misc7,
       sum(TD.misc8) misc8,
       sum(TD.misc9) misc9,
       sum(TD.misc10) misc10,
       sum(TD.misc11) misc11,/*Bug33372456*/
       sum(TD.misc13) misc13,
       sum(TD.misc14) misc14,
       sum(TD.misc15aT + TD.misc15aNT) misc15a,
       sum(TD.misc15aT + TD.misc15b) misc15b,
       decode(substr(pvw.organization_type_lookup_code, 1, 7),
              'FOREIGN',
              '1',
              ' ') foreign_payee_flag,
       decode(replace(pvw.organization_type_lookup_code, 'FOREIGN '),
              'CORPORATION',
              '1',
              'INDIVIDUAL',
              '2',
              ' ') tin_type,
       decode(replace(replace(nvl(pvw.national_identifier,
                                  nvl(pvw.individual_1099, pvw.num_1099)),
                              '-',
                              ''),
                      ' ',
                      ''),
              '000000000',
              '',
              replace(replace(nvl(pvw.national_identifier,
                                  nvl(pvw.individual_1099, pvw.num_1099)),
                              '-',
                              ''),
                      ' ',
                      '')) EIN,
       pvw.segment1 vendor_num,
       lpad(substr(TD.region_code, 1, 2), 2, '0') region_code,
       pvw.vendor_id,              sum(td.nec4) nec4,
       CASE 
          WHEN to_char(:P_START_YEAR_DATE, 'YYYY')<2020 THEN
             (SUM(td.misc1) + SUM(td.misc2) + SUM(td.misc3) + SUM(td.misc5) + SUM(td.misc6) + SUM(td.misc7) + SUM(td.misc15at) + SUM(td.misc15b) + SUM(td.misc8) + SUM(td.misc9) + SUM(td.misc10) + SUM(td.misc11) + SUM(td.misc13) + SUM(td.misc14))/*Bug33372456 - added MISC11*/
          ELSE
             (SUM(td.misc1) + SUM(td.misc2) + SUM(td.misc3) + SUM(td.misc5) + SUM(td.misc6) + 0 + SUM(td.misc8) + SUM(td.misc9) + SUM(td.misc10) + SUM(td.misc11) + SUM(td.misc13) + SUM(td.misc14))/*Bug33372456 - added MISC11*/
       END misc_total
  FROM ap_1099_tape_data TD,
       fnd_territories_vl ft,
       (SELECT P.vendor_id VENDOR_ID,
       P.vendor_name VENDOR_NAME,
       P.tax_reporting_name TAX_REPORTING_NAME,
       p.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
       NULL NATIONAL_IDENTIFIER,
       P.individual_1099 INDIVIDUAL_1099,
       P.num_1099 NUM_1099,
       pvs.province PROVINCE,
       pvs.address_line1 ADDRESS_LINE1,
       pvs.address_line2 ADDRESS_LINE2,
       pvs.address_line3 ADDRESS_LINE3,
       pvs.state STATE,
       pvs.city CITY,
       pvs.zip ZIP,
       pvs.COUNTRY COUNTRY,
       p.segment1 SEGMENT1,
       p.name_control NAME_CONTROL
FROM po_vendors P,
     po_vendor_sites pvs
 WHERE pvs.vendor_id = P.vendor_id
   AND nvl(p.vendor_type_lookup_code, 'DUMMY') <> 'EMPLOYEE' 
   AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
       (pvs.vendor_site_code =
       (select min(vendor_site_code)
            from po_vendor_sites pvs2
           where pvs2.vendor_id = pvs.vendor_id
             and nvl(inactive_date, sysdate + 9000) =
                 (select max(decode(inactive_date,
                                    '',
                                    sysdate + 9000,
                                    inactive_date))
                    from po_vendor_sites pvs3
                   where pvs3.vendor_id = pvs.vendor_id)) 
         AND not exists
        (SELECT 'A tax reporting site exists for this vendor'
            FROM po_vendor_sites pvs4
           WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
             AND pvs4.vendor_id = pvs.vendor_id)))
UNION ALL
SELECT distinct PV.vendor_id VENDOR_ID,
       Pv.vendor_name VENDOR_NAME,
       Pv.tax_reporting_name TAX_REPORTING_NAME,
       pv.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
       papf.national_identifier NATIONAL_IDENTIFIER,
       NULL INDIVIDUAL_1099,
       NULL NUM_1099,
       NULL PROVINCE,
 CASE pvs.vendor_site_code when 'HOME' then
                  per_addr.address_line1
              when 'OFFICE' then
                  per_loc.address_line_1              
              when 'PROVISIONAL' then
                  per_addr_prov.address_line1
              else case when per_addr.address_id is NULL then
                           per_loc.address_line_1 
                         else
                           per_addr.address_line1 
                   end
              end ADDRESS_LINE1,
 CASE pvs.vendor_site_code when 'HOME' then
                  per_addr.address_line2
              when 'OFFICE' then
                  per_loc.address_line_2         
              when 'PROVISIONAL' then
                  per_addr_prov.address_line2
              else case when per_addr.address_id is NULL then
                           per_loc.address_line_2 
                         else
                           per_addr.address_line2 
                   end 
              end ADDRESS_LINE2,
 CASE pvs.vendor_site_code when 'HOME' then
                  per_addr.address_line3
              when 'OFFICE' then
                  per_loc.address_line_3         
              when 'PROVISIONAL' then
                  per_addr_prov.address_line3
              else case when per_addr.address_id is NULL then
                           per_loc.address_line_3 
                         else
                           per_addr.address_line3
                   end 
              end ADDRESS_LINE3,                                  
 CASE pvs.vendor_site_code when 'HOME' then
                  case per_addr.style when 'CA' then
                         ''
                      when 'CA_GLB' then
                         ''
                      else
                         NVL(per_addr.region_2, '') 
                      end 
               when 'OFFICE' then
                   case per_loc.style when 'CA' then
                         ''
                      when 'CA_GLB' then
                         '' 
                      else
                         NVL(per_loc.region_2, '') 
                      end 
               when 'PROVISIONAL' then
                 case per_addr_prov.style when 'CA' then
                         ''
                     when 'CA_GLB' then
                         '' 
                     else
                         NVL(per_addr_prov.region_2, '') 
                     end 
               else
                 case when per_addr.address_id is NULL then
                      case per_loc.style when 'CA' then
                         ''
                      when 'CA_GLB' then
                         '' 
                      else
                         NVL(per_loc.region_2, '') 
                      end 
                   else
                      case per_addr.style when 'CA' then
                         ''
                       when 'CA_GLB' then
                         ''
                       else
                         NVL(per_addr.region_2, '') 
                       end  
                   end  
                end STATE,
 CASE pvs.vendor_site_code when 'HOME' then               
                    per_addr.town_or_city
               when 'OFFICE' then
                    per_loc.town_or_city
               when 'PROVISIONAL' then
                    per_addr_prov.town_or_city
               else 
                   case when per_addr.address_id is NULL then
                       per_loc.town_or_city
                    else
                       per_addr.town_or_city 
                    end 
               end CITY,
 CASE pvs.vendor_site_code when 'HOME' then
                   per_addr.postal_code
               when 'OFFICE' then
                   per_loc.postal_code
               when 'PROVISIONAL' then
                   per_addr_prov.postal_code
               else 
                  case when per_addr.address_id is NULL then
                    per_loc.postal_code
                  else
                    per_addr.postal_code 
                  end 
               end ZIP,
 CASE pvs.vendor_site_code when 'HOME' then
                   per_addr.country
               when 'OFFICE' then
                   per_loc.country
               when 'PROVISIONAL' then
                   per_addr_prov.country
               else 
                  case when per_addr.address_id is NULL then
                    per_loc.country
                  else
                    per_addr.country 
                  end 
               end COUNTRY,
  pv.segment1 SEGMENT1,
  pv.name_control NAME_CONTROL
  FROM HR_LOCATIONS          per_loc,
       PER_ADDRESSES         per_addr,
       PER_ALL_ASSIGNMENTS_F per_assgn,
       PER_ADDRESSES         per_addr_prov,
       (SELECT distinct papf1.person_id, papf1.national_identifier 
           FROM PER_ALL_PEOPLE_F papf1
            WHERE NVL(papf1.effective_end_date,sysdate +9000) = 
            (SELECT MAX(DECODE(papf2.effective_end_date,'',sysdate+9000,papf2.effective_end_date)) 
                 FROM PER_ALL_PEOPLE_F papf2 
              WHERE papf1.person_id=papf2.person_id)) papf,
       PO_VENDOR_SITES pvs,
       PO_VENDORS pv
 WHERE pv.employee_id = papf.person_id
   AND nvl(pv.vendor_type_lookup_code, 'DUMMY') = 'EMPLOYEE' 
   AND pv.vendor_id = pvs.vendor_id
   AND pv.employee_id = per_addr.person_id(+)
   AND per_addr.primary_flag(+) = 'Y'
   AND SYSDATE BETWEEN per_addr.date_from(+) AND
       NVL(per_addr.date_to(+), SYSDATE + 1)
   AND pv.employee_id = per_addr_prov.person_id(+)
   AND nvl(per_addr_prov.address_type(+),'N') = 'M'
   AND SYSDATE BETWEEN per_addr_prov.date_from(+) AND
       NVL(per_addr_prov.date_to(+), SYSDATE + 1)
   AND pv.employee_id = per_assgn.person_id(+)
   AND per_assgn.location_id = per_loc.location_id(+)
   AND per_assgn.assignment_type(+) = 'E'
   AND per_assgn.primary_flag(+) = 'Y'
   AND (TRUNC(SYSDATE) BETWEEN per_assgn.effective_start_date(+) AND
       per_assgn.effective_end_date(+))
   AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
       (pvs.vendor_site_code =
       (select min(vendor_site_code)
            from po_vendor_sites pvs2
           where pvs2.vendor_id = pvs.vendor_id
             and nvl(inactive_date, sysdate + 9000) =
                 (select max(decode(inactive_date,
                                    '',
                                    sysdate + 9000,
                                    inactive_date))
                    from po_vendor_sites pvs3
                   where pvs3.vendor_id = pvs.vendor_id)) AND not exists
        (SELECT 'A tax reporting site exists for this vendor'
            FROM po_vendor_sites pvs4
           WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
             AND pvs4.vendor_id = pvs.vendor_id)))) pvw 
 WHERE TD.vendor_id = pvw.vendor_id
  AND pvw.vendor_id IN (SELECT X.vendor_id
                         FROM ap_1099_tape_data X
                        GROUP BY vendor_id
                       HAVING sum(nvl(misc1, 0)) + sum(nvl(misc3, 0)) + sum(nvl(misc6, 0)) + sum(nvl(misc7, 0)) + sum(nvl(misc9, 0)) + sum(nvl(misc10, 0)) + sum(nvl(misc11, 0)) >= :P_FEDERAL_REPORTING_LIMIT/*Bug33372456*/ OR sum(nvl(misc2, 0)) >= 10 OR sum(nvl(misc8, 0)) >= 10 OR sum(nvl(misc15aT, 0)) + sum(nvl(misc15aNT, 0)) >= :P_FEDERAL_REPORTING_LIMIT OR sum(nvl(misc13, 0)) + sum(nvl(misc14, 0)) + sum(nvl(misc5, 0)) > 0 OR sum(nvl(misc15b, 0)) > 0 OR sum(nvl(misc4, 0)) > 0 OR sum(nvl(misc11, 0)) >= :P_FEDERAL_REPORTING_LIMIT/*Bug33372456*/
                       UNION
                       SELECT Y.vendor_id
                         FROM ap_1099_tape_data Y
                        WHERE region_code is not null)
   AND nvl(misc1, 0) + nvl(misc2, 0) + nvl(misc3, 0) + nvl(misc4, 0) +
       nvl(misc5, 0) + nvl(misc6, 0) + nvl(misc7, 0) + nvl(misc8, 0) +
       nvl(misc9, 0) + nvl(misc10, 0) + nvl(misc13, 0) + nvl(misc14, 0) +
       nvl(misc15aT, 0) + nvl(misc15aNT, 0) + nvl(misc15b, 0) + nvl(misc11, 0)/*Bug33372456*/ > 0
   AND ft.territory_code(+) = pvw.country
 GROUP BY rownum,
          pvw.vendor_name,
          TD.region_code,
          pvw.name_control,
          pvw.organization_type_lookup_code,
          nvl(pvw.national_identifier, nvl(pvw.individual_1099, pvw.num_1099)),
          pvw.segment1,
          pvw.tax_reporting_name,
          pvw.country,
          ft.territory_short_name,
          pvw.address_line1,
          pvw.address_line2,
          pvw.address_line3,
          pvw.city,
          pvw.province,
          pvw.state,
          pvw.zip,
          pvw.vendor_id
 ORDER BY 1) x
  WHERE (x.misc_total> 0 OR to_char(:P_START_YEAR_DATE, 'YYYY')<2020)
    AND :P_RETURN_TYPE = 'MISC'
UNION ALL
SELECT rownum N_rownum, x.*,
    AP_APXT7CMT2_XMLP_PKG.c_error_dummyformula(N_ERROR_TEXT, N_VENDOR_NAME) C_error_dummy, 
    AP_APXT7CMT2_XMLP_PKG.cf_negative_miscformula(N_MISC2, N_MISC3, N_MISC4, N_MISC5, N_MISC6, N_MISC7, N_MISC8, N_MISC9, N_MISC10, N_MISC11/*Bug33372456*/, N_MISC13, N_MISC14, N_MISC15A, N_MISC15B, N_ERROR_TEXT, N_VENDOR_NAME) CF_NEGATIVE_MISC,
    AP_APXT7CMT2_XMLP_PKG.c_b_recordformula(N_MISC7, N_PAYEE_NAME_CONTROL, N_TIN_TYPE, N_EIN, N_VENDOR_ID, N_MISC1, N_MISC2, N_MISC3, N_MISC4, N_MISC5, N_MISC6, N_MISC8, N_MISC10, N_MISC11/*Bug33372456*/, N_MISC13, N_MISC14, N_MISC15A, N_MISC15B, N_FOREIGN_PAYEE_FLAG, N_TAX_REPORTING_NAME, N_VENDOR_NAME, N_VENDOR_LINE, N_VENDOR_CITY, N_VENDOR_STATE, N_VENDOR_ZIP, N_REGION_CODE, N_MISC9, N_NEC4) C_B_record
FROM (
SELECT rownum N_rownum,
       pvw.vendor_name n_vendor_name,
       pvw.tax_reporting_name n_tax_reporting_name,
       rpad(pvw.name_control, 4) n_payee_name_control,
       regexp_replace(pvw.address_line1 || ' ' || pvw.address_line2 || ' ' ||
       pvw.address_line3, '[^0-9A-Za-z'' @#$%^&*()_+-=,!\`~{}./?:";''|[][^\]]*', '') n_vendor_line,
       decode(substr(pvw.organization_type_lookup_code, 1, 7),
              'FOREIGN',
              substr(pvw.city, 1, 21) || ' ' || substr(pvw.state, 1, 9) || ' ' ||
              substr(ft.territory_short_name, 1, 10),
              rpad(substr(pvw.city, 1, 40), 40)) n_vendor_city,
       rpad(substr(pvw.state, 1, 2), 2) n_vendor_state,
       rpad(substr(replace(replace(pvw.zip, '-', ''), ' ', ''), 1, 9), 9) n_vendor_zip,
       decode(pvw.address_line1, '', 'No Address Line 1. ') ||
       decode(substr(pvw.organization_type_lookup_code, 1, 7),
              'FOREIGN',
              decode(pvw.country, '', 'No country. '),
              decode(length(replace(replace(nvl(pvw.national_identifier,
                                                nvl(pvw.individual_1099,
                                                    pvw.num_1099)),
                                            '-',
                                            ''),
                                    ' ',
                                    '')),
                     9,
                     '',
                     'Bad EIN/SSN. ') || decode(pvw.city, '', 'No city. ') ||
              decode(nvl(pvw.province, pvw.state), '', 'No state. ') ||
              decode(replace(replace(pvw.zip, '-', ''), ' ', ''),
                     '',
                     'No postal code. ')) n_error_text,
       sum(TD.misc1) n_misc1,
       sum(TD.misc2) n_misc2,
       sum(TD.misc3) n_misc3,
       sum(TD.misc4) n_misc4,
       sum(TD.misc5) n_misc5,
       sum(TD.misc6) n_misc6,
       sum(TD.misc7 + TD.misc15aT + TD.misc15b) n_misc7,
       sum(TD.misc8) n_misc8,
       sum(TD.misc9) n_misc9,
       sum(TD.misc10) n_misc10,
       sum(TD.misc11) n_misc11,/*Bug33372456*/
       sum(TD.misc13) n_misc13,
       sum(TD.misc14) n_misc14,
       sum(TD.misc15aT + TD.misc15aNT) n_misc15a,
       sum(TD.misc15aT + TD.misc15b) n_misc15b,
       decode(substr(pvw.organization_type_lookup_code, 1, 7),
              'FOREIGN',
              '1',
              ' ') n_foreign_payee_flag,
       decode(replace(pvw.organization_type_lookup_code, 'FOREIGN '),
              'CORPORATION',
              '1',
              'INDIVIDUAL',
              '2',
              ' ') n_tin_type,
       decode(replace(replace(nvl(pvw.national_identifier,
                                  nvl(pvw.individual_1099, pvw.num_1099)),
                              '-',
                              ''),
                      ' ',
                      ''),
              '000000000',
              '',
              replace(replace(nvl(pvw.national_identifier,
                                  nvl(pvw.individual_1099, pvw.num_1099)),
                              '-',
                              ''),
                      ' ',
                      '')) n_EIN,
       pvw.segment1 n_vendor_num,
       /* NULL n_region_code, Bug33372456 commented and added below*/
       lpad(substr(TD.region_code, 1, 2), 2, '0') n_region_code,
       pvw.vendor_id n_vendor_id,              sum(td.nec4) n_nec4,
       NULL misc_total
  FROM ap_1099_tape_data TD,
       fnd_territories_vl ft,
       (SELECT P.vendor_id VENDOR_ID,
       P.vendor_name VENDOR_NAME,
       P.tax_reporting_name TAX_REPORTING_NAME,
       p.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
       NULL NATIONAL_IDENTIFIER,
       P.individual_1099 INDIVIDUAL_1099,
       P.num_1099 NUM_1099,
       pvs.province PROVINCE,
       pvs.address_line1 ADDRESS_LINE1,
       pvs.address_line2 ADDRESS_LINE2,
       pvs.address_line3 ADDRESS_LINE3,
       pvs.state STATE,
       pvs.city CITY,
       pvs.zip ZIP,
       pvs.COUNTRY COUNTRY,
       p.segment1 SEGMENT1,
       p.name_control NAME_CONTROL
FROM po_vendors P,
     po_vendor_sites pvs
 WHERE pvs.vendor_id = P.vendor_id
   AND nvl(p.vendor_type_lookup_code, 'DUMMY') <> 'EMPLOYEE' 
   AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
       (pvs.vendor_site_code =
       (select min(vendor_site_code)
            from po_vendor_sites pvs2
           where pvs2.vendor_id = pvs.vendor_id
             and nvl(inactive_date, sysdate + 9000) =
                 (select max(decode(inactive_date,
                                    '',
                                    sysdate + 9000,
                                    inactive_date))
                    from po_vendor_sites pvs3
                   where pvs3.vendor_id = pvs.vendor_id)) 
         AND not exists
        (SELECT 'A tax reporting site exists for this vendor'
            FROM po_vendor_sites pvs4
           WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
             AND pvs4.vendor_id = pvs.vendor_id)))
UNION ALL
SELECT distinct PV.vendor_id VENDOR_ID,
       Pv.vendor_name VENDOR_NAME,
       Pv.tax_reporting_name TAX_REPORTING_NAME,
       pv.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
       papf.national_identifier NATIONAL_IDENTIFIER,
       NULL INDIVIDUAL_1099,
       NULL NUM_1099,
       NULL PROVINCE,
 CASE pvs.vendor_site_code when 'HOME' then
                  per_addr.address_line1
              when 'OFFICE' then
                  per_loc.address_line_1              
              when 'PROVISIONAL' then
                  per_addr_prov.address_line1
              else case when per_addr.address_id is NULL then
                           per_loc.address_line_1 
                         else
                           per_addr.address_line1 
                   end
              end ADDRESS_LINE1,
 CASE pvs.vendor_site_code when 'HOME' then
                  per_addr.address_line2
              when 'OFFICE' then
                  per_loc.address_line_2         
              when 'PROVISIONAL' then
                  per_addr_prov.address_line2
              else case when per_addr.address_id is NULL then
                           per_loc.address_line_2 
                         else
                           per_addr.address_line2 
                   end 
              end ADDRESS_LINE2,
 CASE pvs.vendor_site_code when 'HOME' then
                  per_addr.address_line3
              when 'OFFICE' then
                  per_loc.address_line_3         
              when 'PROVISIONAL' then
                  per_addr_prov.address_line3
              else case when per_addr.address_id is NULL then
                           per_loc.address_line_3 
                         else
                           per_addr.address_line3
                   end 
              end ADDRESS_LINE3,                                  
 CASE pvs.vendor_site_code when 'HOME' then
                  case per_addr.style when 'CA' then
                         ''
                      when 'CA_GLB' then
                         ''
                      else
                         NVL(per_addr.region_2, '') 
                      end 
               when 'OFFICE' then
                   case per_loc.style when 'CA' then
                         ''
                      when 'CA_GLB' then
                         '' 
                      else
                         NVL(per_loc.region_2, '') 
                      end 
               when 'PROVISIONAL' then
                 case per_addr_prov.style when 'CA' then
                         ''
                     when 'CA_GLB' then
                         '' 
                     else
                         NVL(per_addr_prov.region_2, '') 
                     end 
               else
                 case when per_addr.address_id is NULL then
                      case per_loc.style when 'CA' then
                         ''
                      when 'CA_GLB' then
                         '' 
                      else
                         NVL(per_loc.region_2, '') 
                      end 
                   else
                      case per_addr.style when 'CA' then
                         ''
                       when 'CA_GLB' then
                         ''
                       else
                         NVL(per_addr.region_2, '') 
                       end  
                   end  
                end STATE,
 CASE pvs.vendor_site_code when 'HOME' then               
                    per_addr.town_or_city
               when 'OFFICE' then
                    per_loc.town_or_city
               when 'PROVISIONAL' then
                    per_addr_prov.town_or_city
               else 
                   case when per_addr.address_id is NULL then
                       per_loc.town_or_city
                    else
                       per_addr.town_or_city 
                    end 
               end CITY,
 CASE pvs.vendor_site_code when 'HOME' then
                   per_addr.postal_code
               when 'OFFICE' then
                   per_loc.postal_code
               when 'PROVISIONAL' then
                   per_addr_prov.postal_code
               else 
                  case when per_addr.address_id is NULL then
                    per_loc.postal_code
                  else
                    per_addr.postal_code 
                  end 
               end ZIP,
 CASE pvs.vendor_site_code when 'HOME' then
                   per_addr.country
               when 'OFFICE' then
                   per_loc.country
               when 'PROVISIONAL' then
                   per_addr_prov.country
               else 
                  case when per_addr.address_id is NULL then
                    per_loc.country
                  else
                    per_addr.country 
                  end 
               end COUNTRY,
  pv.segment1 SEGMENT1,
  pv.name_control NAME_CONTROL
  FROM HR_LOCATIONS          per_loc,
       PER_ADDRESSES         per_addr,
       PER_ALL_ASSIGNMENTS_F per_assgn,
       PER_ADDRESSES         per_addr_prov,
       (SELECT distinct papf1.person_id, papf1.national_identifier 
           FROM PER_ALL_PEOPLE_F papf1
            WHERE NVL(papf1.effective_end_date,sysdate +9000) = 
            (SELECT MAX(DECODE(papf2.effective_end_date,'',sysdate+9000,papf2.effective_end_date)) 
                 FROM PER_ALL_PEOPLE_F papf2 
              WHERE papf1.person_id=papf2.person_id)) papf,
       PO_VENDOR_SITES pvs,
       PO_VENDORS pv
 WHERE pv.employee_id = papf.person_id
   AND nvl(pv.vendor_type_lookup_code, 'DUMMY') = 'EMPLOYEE' 
   AND pv.vendor_id = pvs.vendor_id
   AND pv.employee_id = per_addr.person_id(+)
   AND per_addr.primary_flag(+) = 'Y'
   AND SYSDATE BETWEEN per_addr.date_from(+) AND
       NVL(per_addr.date_to(+), SYSDATE + 1)
   AND pv.employee_id = per_addr_prov.person_id(+)
   AND nvl(per_addr_prov.address_type(+),'N') = 'M'
   AND SYSDATE BETWEEN per_addr_prov.date_from(+) AND
       NVL(per_addr_prov.date_to(+), SYSDATE + 1)
   AND pv.employee_id = per_assgn.person_id(+)
   AND per_assgn.location_id = per_loc.location_id(+)
   AND per_assgn.assignment_type(+) = 'E'
   AND per_assgn.primary_flag(+) = 'Y'
   AND (TRUNC(SYSDATE) BETWEEN per_assgn.effective_start_date(+) AND
       per_assgn.effective_end_date(+))
   AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
       (pvs.vendor_site_code =
       (select min(vendor_site_code)
            from po_vendor_sites pvs2
           where pvs2.vendor_id = pvs.vendor_id
             and nvl(inactive_date, sysdate + 9000) =
                 (select max(decode(inactive_date,
                                    '',
                                    sysdate + 9000,
                                    inactive_date))
                    from po_vendor_sites pvs3
                   where pvs3.vendor_id = pvs.vendor_id)) AND not exists
        (SELECT 'A tax reporting site exists for this vendor'
            FROM po_vendor_sites pvs4
           WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
             AND pvs4.vendor_id = pvs.vendor_id)))) pvw 
 WHERE TD.vendor_id = pvw.vendor_id
  AND pvw.vendor_id IN (SELECT X.vendor_id
                         FROM ap_1099_tape_data X
                        GROUP BY vendor_id
                        HAVING sum(nvl(misc1, 0)) + sum(nvl(misc3, 0)) + sum(nvl(misc6, 0)) + sum(nvl(misc7, 0)) + sum(nvl(misc9, 0)) + sum(nvl(misc10, 0)) + sum(nvl(misc11, 0))/*Bug33372456*/ >= :P_FEDERAL_REPORTING_LIMIT OR sum(nvl(misc2, 0)) >= 10 OR sum(nvl(misc8, 0)) >= 10 OR sum(nvl(misc15aT, 0)) + sum(nvl(misc15aNT, 0)) >= :P_FEDERAL_REPORTING_LIMIT OR sum(nvl(misc13, 0)) + sum(nvl(misc14, 0)) + sum(nvl(misc5, 0)) > 0 OR sum(nvl(misc15b, 0)) > 0 /*OR sum(nvl(misc4, 0)) > 0 Bug33372456 - commented*/OR sum(nvl(nec4, 0)) > 0/*Bug33372456*/ OR sum(nvl(misc7, 0)) +sum(nvl(misc15aT, 0)) +sum(nvl(misc15b, 0)) >= :P_FEDERAL_REPORTING_LIMIT /*Bug33372456*/ OR sum(nvl(misc11, 0)) >= :P_FEDERAL_REPORTING_LIMIT /*Bug33372456*/
                       UNION
                       SELECT Y.vendor_id
                         FROM ap_1099_tape_data Y
                        WHERE region_code is not null)
   AND nvl(misc1, 0) + nvl(misc2, 0) + nvl(misc3, 0) + nvl(misc4, 0) +
       nvl(misc5, 0) + nvl(misc6, 0) + nvl(misc7, 0) + nvl(misc8, 0) +
       nvl(misc9, 0) + nvl(misc10, 0) + nvl(misc13, 0) + nvl(misc14, 0) +
       nvl(misc15aT, 0) + nvl(misc15aNT, 0) + nvl(misc15b, 0) + nvl(misc11, 0)/*Bug33372456*/ > 0
   AND ft.territory_code(+) = pvw.country
 GROUP BY rownum,
          pvw.vendor_name,
          TD.region_code,/*Bug33372456*/
          pvw.name_control,
          pvw.organization_type_lookup_code,
          nvl(pvw.national_identifier, nvl(pvw.individual_1099, pvw.num_1099)),
          pvw.segment1,
          pvw.tax_reporting_name,
          pvw.country,
          ft.territory_short_name,
          pvw.address_line1,
          pvw.address_line2,
          pvw.address_line3,
          pvw.city,
          pvw.province,
          pvw.state,
          pvw.zip,
          pvw.vendor_id) x
  WHERE x.n_misc7 > 0 
    AND to_char(:P_START_YEAR_DATE, 'YYYY')>=2020
    AND :P_RETURN_TYPE = 'NEC'
  ORDER BY 1
Parameter NameSQL textValidation
Operating Unit
 
LOV
Control Name
 
Char
Control Code
 
Char
From Payment Date
 
Date
To Payment Date
 
Date
Tax Reporting Entity
 
LOV Oracle
Media Type
 
LOV Oracle
Test Submission
 
LOV Oracle
Foreign Corporation
 
LOV Oracle
Last Year Filing
 
LOV Oracle
Payer Name Source
 
LOV Oracle
Query Driver
 
Char
Contact Telephone Number
 
Number
Contact Name
 
Char
Contact E-mail
 
Char
File Indicator
 
LOV Oracle
Original File
 
LOV Oracle
Replacement Alpha Character
 
Char
Electronic File Name
 
Char
Federal Reporting Limit
 
Number
Return Type
 
LOV Oracle
Download
Blitz Report™