Transferring data from one table to another, some relational columns

Transferring data from one table to another, some relational columns

Table 1 - this table is completely populated from an XLSX file...

Tables 2 and 3 - contain 1-1 references for a couple of the columns in the
final table.

Table 3 - the on I am trying to get populated with first three tables....

As you can see, the tables are sensible, there is no einsteinic equations
or conversions going on. Here is the code that I have already tried,
unsuccessfully:
INSERT INTO att_oem_orders SELECT NULL, ost.om_or_po, (SELECT j.job_id
FROM jobs j WHERE j.project_number = project_no), NULL, (SELECT ao.id FROM
att_oem WHERE ao.item_no = item_no), ost.po_number, (SELECT ol.id FROM
order_lsc WHERE STATUS = ol.line_status_code), ost.ordered_date,
ost.shipment_date, NULL, NULL, ost.item_qty, NULL, NULL, NULL, NULL,
ost.shipping_to, ost.tracking_number, ost.carrier) FROM oem_temp_sync
WHERE ost.item_qty > 0