varontron
commented
8 years ago The problem here after cursory review I believe is that the deparser is unable to handle YADA parameters in the list of SelectItem objects. This is probably resolvable, but without a named column reference or alias, the YADA column syntax (e.g., YADA_1, YADA_2, which is typically used only for standard parameters) may have to be employed.
I had to change: INSERT into DEPOT_STUDY_PERSON_LINK(DEPOT_STUDY_PERSON_LINK_ID, PERSON_ID, STUDY_ID, DEPOT_ID, INV_UPLOAD_ID,INV_EFFECTIVE_DATE, COMMIT_TIME, IS_ACTIVE, MOD_TIME) SELECT ?n, (SELECT MIN(PERSON_ID) FROM PERSON WHERE lower(EMP521)=?v), (SELECT STUDY_ID FROM STUDY WHERE PROTOCOL_NUMBER = ?v), (SELECT DEPOT_ID FROM DEPOT where LOCAL_NAME = ?v), INV_UPLOAD_ID, INV_EFFECTIVE_DATE, SYSTIMESTAMP,1, SYSTIMESTAMP FROM INV_UPLOAD where INV_UPLOAD_ID=?n
to 2 steps:
INSERT into DEPOT_STUDY_PERSON_LINK(DEPOT_STUDY_PERSON_LINK_ID, PERSON_ID, STUDY_ID, DEPOT_ID, INV_UPLOAD_ID,INV_EFFECTIVE_DATE, COMMIT_TIME, IS_ACTIVE, MOD_TIME) VALUES(?n,?n,?n,?n,?n,SYSDATE,SYSTIMESTAMP,1,SYSTIMESTAMP)
and then an update statement to get the correct SYSDATE - because I did not want the app to have to know it in advance.
UPDATE DEPOT_STUDY_PERSON_LINK SET INV_EFFECTIVE_DATE = (SELECT INV_EFFECTIVE_DATE FROM INV_UPLOAD WHERE INV_UPLOAD_ID=DEPOT_STUDY_PERSON_LINK.INV_UPLOAD_ID) WHERE DEPOT_STUDY_PERSON_LINK_ID = ?n
Likewise, I had to change the below: INSERT into INVENTORY(inventory_id, depot_study_person_link_id, md5, row_cat, drug_name, strength,strength_unit, container_amt, container_unit, pcn_number, batch, quantity, expiry_date, orig_json, comments, is_active, mod_time) SELECT idgenseq.nextval, ?n, md5, row_cat, drug_name, strength, strength_unit, container_amt, container_unit, pcn_number, batch, quantity, expiry_date, orig_json, inv_comments, 1,systimestamp FROM PROCESSED_DATA WHERE INV_UPLOAD_ID=?n and PROTOCOL_NUMBER=?v and DEPOT=?v AND NOT (IS_IGNORE_ROW=1)
to
INSERT into INVENTORY(inventory_id, depot_study_person_link_id, md5, row_cat, drug_name, strength,strength_unit, container_amt, container_unit, pcn_number, batch, quantity, expiry_date, orig_json, comments, is_active, mod_time) SELECT idgenseq.nextval, DEPOT_STUDY_PERSON_LINK_ID, md5, row_cat, drug_name, strength, strength_unit, container_amt, container_unit, pcn_number, batch, quantity, expiry_date, orig_json, inv_comments, 1,systimestamp FROM PROCESSED_DATA p, DEPOT_STUDY_PERSON_LINK l WHERE p.INV_UPLOAD_ID=?n and PROTOCOL_NUMBER=?v and DEPOT=?v AND NOT (IS_IGNORE_ROW=1) AND l.DEPOT_STUDY_PERSON_LINK_ID = ?n