search

labordata / lm20

labor relations consultants
MIT License
2 stars 1 forks source link

Patch mystery data misalignment #14

Closed hancush closed 1 month ago

hancush commented 1 month ago

Leah reported a mismatch between a PDF LM20 report and the data in our database.

I've found another discrepancy between the query and the actual LM-20. This LM-20 has the rptID of 897473. In the spreadsheet export, it has LRI Consulting as the company but it is Specs Family Partners per the form.

If I run their extract query (with a filter for the incorrect rptId) against the linked database (below), I see the same issue:

name title organization po_box,_bldg,_room_no,_if_any street city state zip_code file_number amended name organization po_box,_bldg,_room_no,_if_any street state city zip_code date_entered_into date_fiscal_year_ends type_of_person Object of activity specified Nature of activity Specific period of performance Specific extent of performance name organization po_box,_bldg,_room_no,_if_any street city state zip specific_subject_employees specific_subject_labor_orgs SIGNED Date SIGNED Date Number of signatures atom_link atom_updated specific_nature_of_activity notes
Michael Damian Esposito Consultant Espo Employee Relations LLC 400 7507 Roswell Road Sandy Springs GA 30350 - 8500 71368 N Phil Wilson LRI Consulting Services Inc. P.O. Box 1529   OK Broken Arrow 14013 2024-06-05 Dec 31 Other Y Engaged to educate employees regarding exercising their rights to organize and bargain collectively. Various days beginning 6/6/2024 - 6/14/2024 Completed Ron Hasinger ADM - Archer Daniels Midland Company   77 W. Wacher Drive Chicago IL 60601 Dry Mill hourly production employees and lab techs Teamsters Local 238 Jul 29, 2024 Jul 29, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=897473&rptForm=LM20Form 2024-09-17 05:00:00Z Engaged to educate employees regarding exercising their rights to organize and bargain collectively. Verbal agreement to represent ADM at their facility in Cedar Rapids, IA, to educate employees regarding their rights to organize and bargain collectively. Consultations billed at $212.50/hour, including travel and expenses. Agreement has not been reduced to writing, is for no specific time, and may be terminated by either party at any time.

The attached database contains two records for LRI consulting, one of them linking to the correct LM20:

name title organization po_box,_bldg,_room_no,_if_any street city state zip_code file_number amended name organization po_box,_bldg,_room_no,_if_any street state city zip_code date_entered_into date_fiscal_year_ends type_of_person Object of activity specified Nature of activity Specific period of performance Specific extent of performance name organization po_box,_bldg,_room_no,_if_any street city state zip specific_subject_employees specific_subject_labor_orgs SIGNED Date SIGNED Date Number of signatures atom_link atom_updated specific_nature_of_activity notes
Michael Damian Esposito Consultant Espo Employee Relations LLC 400 7507 Roswell Road Sandy Springs GA 30350 - 8500 71368 N Phil Wilson LRI Consulting Services Inc. P.O. Box 1529   OK Broken Arrow 14013 2024-06-05 Dec 31 Other Y Engaged to educate employees regarding exercising their rights to organize and bargain collectively. Various days beginning 6/6/2024 - 6/14/2024 Completed Ron Hasinger ADM - Archer Daniels Midland Company   77 W. Wacher Drive Chicago IL 60601 Dry Mill hourly production employees and lab techs Teamsters Local 238 Jul 29, 2024 Jul 29, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=897473&rptForm=LM20Form 2024-09-17 05:00:00Z Engaged to educate employees regarding exercising their rights to organize and bargain collectively. Verbal agreement to represent ADM at their facility in Cedar Rapids, IA, to educate employees regarding their rights to organize and bargain collectively. Consultations billed at $212.50/hour, including travel and expenses. Agreement has not been reduced to writing, is for no specific time, and may be terminated by either party at any time.
Michael Damian Esposito Consultant Espo Employee Relations LLC 400 7507 Roswell Road Sandy Springs GA 30350 - 8500 71394 Y Phil Wilson LRI Consulting Services Inc. P.O. Box 1529   OK Broken Arrow 14013 2024-06-05 Dec 31 Other Y Engaged to educate employees regarding exercising their rights to organize and bargain collectively. Various days beginning 6/6/2024 - 6/14/2024 Completed Ron Hasinger ADM - Archer Daniels Midland Company   77 W. Wacher Drive Chicago IL 60601 Dry Mill hourly production employees and lab techs Teamsters Local 238 Jul 29, 2024 Jul 29, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=895577&rptForm=LM20Form 2024-07-29 05:00:00Z Engaged to educate employees regarding exercising their rights to organize and bargain collectively. Verbal agreement to represent ADM at their facility in Cedar Rapids, IA, to educate employees regarding their rights to organize and bargain collectively. Consultations billed at $212.50/hour, including travel and expenses. Agreement has not been reduced to writing, is for no specific time, and may be terminated by either party at any time.

Meanwhile, there are a number of records for Specs, but none of them refer to the LM20 Leah sent us. The closest match is the record tied to Simon Jara, https://olmsapps.dol.gov/query/orgReport.do?rptId=896618&rptForm=LM20Form, but one is an individual filing and the other is a corporate filing.

name title organization po_box,_bldg,_room_no,_if_any street city state zip_code file_number amended name organization po_box,_bldg,_room_no,_if_any street state city zip_code date_entered_into date_fiscal_year_ends type_of_person Object of activity specified Nature of activity Specific period of performance Specific extent of performance name organization po_box,_bldg,_room_no,_if_any street city state zip specific_subject_employees specific_subject_labor_orgs SIGNED Date SIGNED Date Number of signatures atom_link atom_updated specific_nature_of_activity notes
Peter A List Founder & CEO Logic Labor Relations, LLC PO Box 2877   Pawleys Island SC 29585 70082 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-08-19 Dec 24 Corporation C Y Traveled to various facility locations in Texas. Interacted with employees to discuss employee relations; Engaged in walk-throughs and answered employee questions. Various days beginning 8/19/2024 Ongoing Luisa Perez LABOR RELATIONS LMP INC   3107 Diplomat Pkwy W Cape Coral FL 33993 Employees employed by the employer at its various facilities in Texas: Including Austin, El Paso, Ft. Worth, Houston, Plano, and San Antonio. No Petition Sep 16, 2024 Sep 16, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=897382&rptForm=LM20Form 2024-09-16 05:00:00Z Traveled to various facility locations in Texas. Interacted with employees to discuss employee relations; Engaged in walk-throughs and answered employee questions. Oral agreement made with Logic Labor Relations LLC, $3,750 per day, per consultant, plus actual and reasonable expenses.
Peter A List Founder & CEO Logic Labor Relations, LLC PO Box 2877   Pawleys Island SC 29585 70082 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-08-19 Dec 24 Corporation C Y Traveled to various facility locations in Texas. Interacted with employees to discuss employee relations; Engaged in walk-throughs and answered employee questions. Various days beginning 8/19/2024 Ongoing Justin Shoemaker Individual     Pawleys Island SC 29585 Employees employed by the employer at its various facilities in Texas: Including Austin, El Paso, Ft. Worth, Houston, Plano, and San Antonio. No Petition Sep 16, 2024 Sep 16, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=897382&rptForm=LM20Form 2024-09-16 05:00:00Z Traveled to various facility locations in Texas. Interacted with employees to discuss employee relations; Engaged in walk-throughs and answered employee questions. Oral agreement made with Logic Labor Relations LLC, $3,750 per day, per consultant, plus actual and reasonable expenses.
Peter A List Founder & CEO Logic Labor Relations, LLC PO Box 2877   Pawleys Island SC 29585 70082 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-08-19 Dec 24 Corporation C Y Traveled to various facility locations in Texas. Interacted with employees to discuss employee relations; Engaged in walk-throughs and answered employee questions. Various days beginning 8/19/2024 Ongoing Simon Jara Concordia Resolve Consulting LLC     Scottsdale AZ 85258 Employees employed by the employer at its various facilities in Texas: Including Austin, El Paso, Ft. Worth, Houston, Plano, and San Antonio. No Petition Sep 16, 2024 Sep 16, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=897382&rptForm=LM20Form 2024-09-16 05:00:00Z Traveled to various facility locations in Texas. Interacted with employees to discuss employee relations; Engaged in walk-throughs and answered employee questions. Oral agreement made with Logic Labor Relations LLC, $3,750 per day, per consultant, plus actual and reasonable expenses.
Luisa M Perez Individual Luisa Perez STE 3157 #62 3941 Tamiami Trail Punta Gorda FL 33950 68687 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-08-25 Dec 24 Other Y Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Various days beginning 8/25/2024 Ongoing Peter List Logic Labor Relations, LLC     Pawleys Island SC 29585 Employees employed by the employer at its various facilities in Texas: Including Austin, El Paso, Ft. Worth, Houston, Plano, and San Antonio. No Petition Sep 16, 2024 Sep 16, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=897387&rptForm=LM20Form 2024-09-16 05:00:00Z Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Oral agreement made with Logic Labor Relations LLC, $2,812.50 per day, plus actual and reasonable expenses.
Justin Shoemaker Individual Individual PO BOX 983   Pawleys Island SC 29585 69686 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-08-26 Dec 24 Individual Y Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Various days beginning 8/26/2024 Ongoing Peter List Logic Labor Relations, LLC     Pawleys Island SC 29585 Employees employed by the employer at its various facilities in Texas: Including Austin, El Paso, Ft. Worth, Houston, Plano, and San Antonio. No Petition Sep 16, 2024   1 https://olmsapps.dol.gov/query/orgReport.do?rptId=897402&rptForm=LM20Form 2024-09-16 05:00:00Z Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Oral agreement made with Logic Labor Relations LLC, $2,812.50 per day, plus actual and reasonable expenses.
Simon Jara Vice President Concordia Resolve Consulting, Inc.   9393 N 90th St Suite 102 PMB8 Scottsdale AZ 85258 71368 N Mike Garcia Specs Family Partners LTD 2410 Smith Street TX Houston 77006 2024-07-22 Dec 24 Individual Y Holding and conducting meetings with management and employees to answer questions related to rights afforded by the National Labor Relations Act (NLRA). 07/23/24 08/15/24 Peter List Logic Labor Relations, LLC     Pawleys Island SC 29585 warehouse and drivers teamsters Local 745 Aug 26, 2024   1 https://olmsapps.dol.gov/query/orgReport.do?rptId=896618&rptForm=LM20Form 2024-08-26 05:00:00Z Holding and conducting meetings with management and employees to answer questions related to rights afforded by the National Labor Relations Act (NLRA). Verbal agreement to represent Specs company at their facility in Dallas in a campaign to prevent Teamsters 745 from organizing their employees for purposes of collective bargaining. All consultations billed at $2812.50 per day not including travel and expenses. Agreement has never been educed to writing, is for no specific time, and may be terminated by either party at any time.
Luisa M Perez Manager LABOR RELATIONS LMP INC   3107 DIPLOMAT PKWY W CAPE CORAL FL 33993 68687 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-07-22 Dec 24 Other Y Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Various days beginning 7/24/2024 Ongoing Peter List Logic Labor Relations, LLC     Pawleys Island SC 29585 Included: On Premise Warehouse Associates; On Premise Drivers FT; On Premise Trainers; Retail Warehouse Class A Drivers; On Premise Receivers; Retail Warehouse Merchandisers; Retail Warehouse Transfer Drivers; and Wholesale Warehouse Merchandisers. Facility location 7777 Hines Place Dallas, TX 75235. Teamsters Local 745 Aug 22, 2024 Aug 22, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=896499&rptForm=LM20Form 2024-08-22 05:00:00Z Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Oral agreement made through Logic Labor Relations LLC, $2,812.50 per day, plus actual and reasonable expenses
Peter A List Founder & CEO Logic Labor Relations, LLC PO Box 2877   Pawleys Island SC 29585 70082 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-07-22 Dec 24 Corporation C Y Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Various days beginning 7/22/2024 Ongoing Luisa Perez LABOR RELATIONS LMP INC   3107 DIPLOMAT PKWY W Cape Coral FL 33993 Included: On Premise Warehouse Associates; On Premise Drivers FT; On Premise Trainers; Retail Warehouse Class A Drivers; On Premise Receivers; Retail Warehouse Merchandisers; Retail Warehouse Transfer Drivers; and Wholesale Warehouse Merchandisers. Facility location 7777 Hines Place Dallas, TX 75235. Teamsters Local 745 Aug 16, 2024 Aug 19, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=896342&rptForm=LM20Form 2024-08-19 05:00:00Z Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Oral agreement made with Logic Labor Relations LLC, $3,750 per day, per consultant, plus actual and reasonable expenses.
Peter A List Founder & CEO Logic Labor Relations, LLC PO Box 2877   Pawleys Island SC 29585 70082 N Mike Garcia Specs Family Partners LTD   2410 Smith St TX Houston 77006 2024-07-22 Dec 24 Corporation C Y Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Various days beginning 7/22/2024 Ongoing Simon Jara Concordia Resolve Consulting LLC   9393 N 90th St Scottsdale AZ 85258 Included: On Premise Warehouse Associates; On Premise Drivers FT; On Premise Trainers; Retail Warehouse Class A Drivers; On Premise Receivers; Retail Warehouse Merchandisers; Retail Warehouse Transfer Drivers; and Wholesale Warehouse Merchandisers. Facility location 7777 Hines Place Dallas, TX 75235. Teamsters Local 745 Aug 16, 2024 Aug 19, 2024 2 https://olmsapps.dol.gov/query/orgReport.do?rptId=896342&rptForm=LM20Form 2024-08-19 05:00:00Z Traveled to employer; Interacted with employees and provided information regarding the NLRB, unions, and the election process. Oral agreement made with Logic Labor Relations LLC, $3,750 per day, per consultant, plus actual and reasonable expenses.

This is probably (?) an issue with our SQL, but it's curious that it seems to be a transient error. Can report IDs be reassigned?

Query: 

select
  distinct c.name,
  c.title,
  c.organization,
  c.[po_box,_bldg,_room_no,_if_any],
  c.street,
  c.city,
  c.state,
  c.zip_code,
  f.srNum as file_number,
  f.amended,
  l.name,
  l.organization,
  l.[po_box,_bldg,_room_no,_if_any],
  l.street,
  l.state,
  l.city,
  l.zip_code,
  l.date_entered_into,
  l.date_fiscal_year_ends,
  l.type_of_person,
  CASE WHEN l.direct = 'X' OR l.indirect = 'X' THEN 'Y' ELSE 'N' END as 'Object of activity specified',
  a.[specific_nature_of_activity] as 'Nature of activity',
  a.[specific_period_of_performance] as 'Specific period of performance',
  a.[specific_extent_of_performance] as 'Specific extent of performance',
  p.name,
  p.organization,
  p.[po_box,_bldg,_room_no,_if_any],
  p.street,
  p.city,
  p.state,
  p.zip,
  [specific_subject_employees],
  specific_subject_labor_orgs,
  sl.date as 'SIGNED Date',
  sr.date as 'SIGNED Date',
  CASE WHEN sl.signed IS NOT NULL and sr.signed IS NOT NULL THEN 2 WHEN sl.signed IS NOT NULL OR sr.signed IS NOT NULL THEN 1 ELSE 0 END as 'Number of signatures',
  filing_url as atom_link,
  datetime(receiveDate, 'utc') || 'Z' as atom_updated,
  [specific_nature_of_activity],
  l.notes
from
  contact c
  join lm20 l on c.rptid = l.rptid
  join [specific_activity] a on a.rptid = l.rptid
  join performer p on p.rptid = l.rptid
  join filing f on f.rptid = l.rptid
  join signatures sl on sl.signature_number = 13
  and sl.rptid = l.rptid
  join signatures sr on sr.signature_number = 14
  and sr.rptid = l.rptid
where
  c.name is not null
  and l.rptId = 897473
order by
  datetime(receiveDate, 'utc') desc;

Database:

lm20-db-nightly-20240918-fb6ce1.zip

fgregg commented 1 month ago

The problem is appears in the output of the scraper:

{
    "amended": "N",
    "beginDate": "2023-08-20",
    "endDate": "2024-08-19",
    "registerDate": "2024-09-17",
    "formFiled": "LM-20",
    "receiveDate": "2024-09-17",
    "rptId": 897473,
    "srFilerId": 710112,
    "yrCovered": 2024,
    "empLabOrg": "Concordia Resolve Consulting, Inc.",
    "amendment": "0",
    "empTrdName": "",
    "subLabOrg": "SPECS FAMILY PARTNERS LTD",
    "formLink": "LM20Form",
    "srNum": 71368,
    "repOrgsCnt": 1,
    "paperOrElect": "E",
    "city": "Scottsdale",
    "state": "AZ",
    "address1": "9393 N 90th St Suite 102 PMB8",
    "address2": "",
    "zip": "85258",
    "termDate": "",
    "amount": "0",
    "originalRptId": "897473",
    "detailed_form_data": {
        "file_number": "71394",
        "amended": "",
        "person_filing": {
            "name_and_mailing_address": {
                "name": "Michael Damian Esposito",
                "title": "Consultant",
                "organization": "Espo Employee Relations LLC",
                "po_box,_bldg,_room_no,_if_any": "400",
                "street": "7507 Roswell Road",
                "city": "Sandy Springs",
                "state": "GA",
                "zip_code": "30350 - 8500"
            },
            "any_other_name_or_address_necessary_to_verify_report": {
                "name": "",
                "title": "",
                "organization": "",
                "po_box,_bldg,_room_no,_if_any": "",
                "street": "",
                "city": "",
                "state": "",
                "zip_code": ""
            }
        },
        "date_fiscal_year_ends": "Dec / 31",
        "type_of_person": "Other",
        "employer": {
            "name": "Phil Wilson",
            "title": "",
            "organization": "LRI Consulting Services Inc.",
            "po_box,_bldg,_room_no,_if_any": "P.O. Box 1529",
            "street": "",
            "city": "Broken Arrow",
            "state": "OK",
            "zip_code": "14013",
            "date_entered_into": "06/05/2024"
        },
        "signatures": {
            "13": {
                "signed": "Michael D Esposito",
                "title": "PRESIDENT",
                "date": "Jul 29, 2024",
                "telephone_number": "404-386-4034"
            },
            "14": {
                "signed": "Michael D Esposito",
                "title": "TREASURER",
                "date": "Jul 29, 2024",
                "telephone_number": "404-386-4034"
            }
        },
        "direct_or_indirect": {
            "direct": "X",
            "indirect": null
        },
        "terms_and_conditions": {
            "written_agreement": "",
            "notes": "Verbal agreement to represent ADM at their facility in Cedar Rapids, IA, to educate employees regarding their rights to organize and bargain collectively.  Consultations billed at $212.50/hour, including travel and expenses. Agreement has not been reduced to writing, is for no specific time, and may be terminated by either party at any time."
        },
        "specific_activities": [
            {
                "nature_of_activity": "Engaged to educate employees regarding exercising their rights to organize and bargain collectively.",
                "period_of_performance": "Various days beginning 6/6/2024 - 6/14/2024",
                "extent_of_performance": "Completed",
                "performers": [
                    {
                        "name": "Ron Hasinger",
                        "organization": "ADM - Archer Daniels Midland Company",
                        "po_box,_bldg,_room_no,_if_any": "",
                        "street": "77 W. Wacher Drive",
                        "city": "Chicago",
                        "state": "IL",
                        "zip": "60601"
                    }
                ],
                "subject_employees": "Dry Mill hourly production employees and lab techs",
                "subject_labor_orgs": "Teamsters Local 238"
            }
        ]
    },
    "file_urls": [],
    "file_headers": {},
    "filing_url": "https://olmsapps.dol.gov/query/orgReport.do?rptId=897473&rptForm=LM20Form",
    "files": []
}
fgregg commented 1 month ago

okay, the srNum for this file is 71368, but the file number (which should be the same) in the scraped data is 71394. so that's a good clue!!