I suspect that this is not a completely new result but a reprocess of a result and the prior processing of the result was not cleared out completely.
SQL> set serveroutput on SQL> exec labresult.process Start of processing: ++++ Process exteral lab order: F2DC0BC13BAB48DCB35B31977BCDC223 with lab_order_number: WD055935A, accession number: 021584300 and pms_id = WD055935A Results received. Will be processed. F2DC0BC13BAB48DCB35B31977BCDC223. which = 1 Process exteral lab order: F2DC0BC13BAB48DCB35B31977BCDC223 with lab_order_number: WD055935A, accession number: 021584300 and pms_id = WD055935A Results received. Will be processed. F2DC0BC13BAB48DCB35B31977BCDC223. which = 2 Process exteral lab order: F2DC0BC13BAB48DCB35B31977BCDC223 with lab_order_number: WD055935A, accession number: 021584300 and pms_id = WD055935A Results received. Will be processed. F2DC0BC13BAB48DCB35B31977BCDC223. which = 3 Process exteral lab order: F2DC0BC13BAB48DCB35B31977BCDC223 with lab_order_number: WD055935A, accession number: 021584300 and pms_id = WD055935A At start of orders At fmv_guid with: 801, reflex Value returned: AA0952A4F1517A8BE0530100007FDE3B* Past multiplier Past recurrence count 801, reflex Past ordertype_id Past specialty_id Past orders required Past lab_test_guid Past lab_name At fu_guid: AA0952A4F1517A8BE0530100007FDE3B Past fu_guid Past orders nullable Past result required Past result nullable Past result_detail required Past result_detail nullable Past result_enconter_map Past orders_pending_review Past orders_filed_review End of assigning values Error creating record in ORDERS with guid = 8C87B2E5312B458AA60825F06E4DFD83: ORA-00001: unique constraint (B_MULTIONC.ORDERS_PK) violated zzzzz before Error creating record in RESULT with guid = zc87b2e5312b458aa60825f06e4dfd8z: ORA-00001: unique constraint (B_MULTIONC.RESULT_PK) violated Error creating record in RESUKT_DETAIL with guid = zc87b2e5312b458aa60825f06e4dfd8z: ORA-00001: unique constraint (B_MULTIONC.RSLTDTL_PK) violated ^^^^^ c_rec_rem.result_guid is: zc87b2e5312b458aa60825f06e4dfd8z Error creating record in RESUKT_ENCOUNTER_MAP with guid = zc87b2e5312b458aa60825f06e4dfd8z: ORA-00001: unique constraint (B_MULTIONC.REM_REM_2_UK) violated Error creating record in ORDERS_PENDING_REVIEW with orders_guid = 8C87B2E5312B458AA60825F06E4DFD83: ORA-00001: unique constraint (B_MULTIONC.ORDPENDREV_PK) violated Error creating record in ORDERS_FILED_REVIEW with orders_guid = 8C87B2E5312B458AA60825F06E4DFD83: ORA-00001: unique constraint (B_MULTIONC.ORDFILEREV_PK) violated Results received. Will be processed. F2DC0BC13BAB48DCB35B31977BCDC223. which = 4 Process exteral lab order: 3CBACCED02AE4ECD8154F1DB0861318E with lab_order_number: CS0000TC3, accession number: 8529631470 and pms_id = Results received. Will be processed. 3CBACCED02AE4ECD8154F1DB0861318E. which = 1 Process exteral lab order: 3CBACCED02AE4ECD8154F1DB0861318E with lab_order_number: CS0000TC3, accession number: 8529631470 and pms_id = Results received. Will be processed. 3CBACCED02AE4ECD8154F1DB0861318E. which = 2 Process exteral lab order: 3CBACCED02AE4ECD8154F1DB0861318E with lab_order_number: CS0000TC3, accession number: 8529631470 and pms_id = Results received. Will be processed. 3CBACCED02AE4ECD8154F1DB0861318E. which = 3 Process exteral lab order: 3CBACCED02AE4ECD8154F1DB0861318E with lab_order_number: CS0000TC3, accession number: 8529631470 and pms_id = Results received. Will be processed. 3CBACCED02AE4ECD8154F1DB0861318E. which = 4 Process exteral lab order: FC6D47F84285495182D887B78644D831 with lab_order_number: CS0000TC3, accession number: 8529631471 and pms_id = Results received. Will be processed. FC6D47F84285495182D887B78644D831. which = 1 Process exteral lab order: FC6D47F84285495182D887B78644D831 with lab_order_number: CS0000TC3, accession number: 8529631471 and pms_id = Results received. Will be processed. FC6D47F84285495182D887B78644D831. which = 2 Process exteral lab order: FC6D47F84285495182D887B78644D831 with lab_order_number: CS0000TC3, accession number: 8529631471 and pms_id = Results received. Will be processed. FC6D47F84285495182D887B78644D831. which = 3 Process exteral lab order: FC6D47F84285495182D887B78644D831 with lab_order_number: CS0000TC3, accession number: 8529631471 and pms_id = Results received. Will be processed. FC6D47F84285495182D887B78644D831. which = 4 End of processing: ++++
Notice the multiple ORA-00001: unique constraint errors in the above.Here is part of my code that gets executed when unsolicited result come in:
-- Not null columns for ORDERS
c_rec_order.GUID := p_guid_elt;
dbms_output.put_line('At fmv_guid with: ' || c_rec_elo.lab_id || ', ' || v_testcode);
c_rec_order.FMV_GUID := labresult.get_fmv_guid(c_rec_elo.lab_id, v_testcode);
dbms_output.put_line('Value returned: ' || c_rec_order.FMV_GUID || '*');
c_rec_order.ORDERSTTS_ID := 18;
c_rec_order.RESULTMODE_ID := 1;
c_rec_order.ORDERSRCE_ID := 1;
c_rec_order.MULTIPLIER := 1;
dbms_output.put_line('Past multiplier');
c_rec_order.CLINIC_GUID := c_rec_elo.CLINIC_GUID;
c_rec_order.PATIENT_GUID := c_rec_plo.PATIENT_GUID;
c_rec_order.RECMGR_HIDE := 'N';
c_rec_order.ABN_STATUS_ID := -1;
c_rec_order.ORDERSTATE_ID := 1;
c_rec_order.ABN_REASON_ID := -1;
c_rec_order.RECURRENCE_COUNT := 0;
dbms_output.put_line('Past recurrence count');
dbms_output.put_line(c_rec_elo.lab_id || ', ' || v_testcode);
c_rec_order.ORDERTYPE_ID := nvl(labresult.get_ordertype(c_rec_elo.lab_id, v_testcode), 1);
dbms_output.put_line('Past ordertype_id');
c_rec_order.SPECIALTY_ID := 65;
dbms_output.put_line('Past specialty_id');
c_rec_order.DXLINKTYP_ID := 0;
c_rec_order.NUM_LAB_QUESTIONS := 0;
dbms_output.put_line('Past orders required');
Notice that orders.guid gets created from external_lab_order.guid
When reusing a result either
When reusing a result either
- it needs to be completely cleaned out from both the external_lab* and orders/results tables
- A different value assigned to external_lab_test.guid
What you are seeing is consistent with neither 1 and 2 being done.Also note the guids of the form z%z. This is the format of guids for records created by my process to handle unsolicited result.
That fact that they already exist supports my guess that this is an attempt to reuse already processed data and neither 1 or 2 done.I am sure that if I delete the record with orders.guid = ‘8C87B2E5312B458AA60825F06E4DFD83’ and all children then my code will do its job without errors.
That fact that they already exist supports my guess that this is an attempt to reuse already processed data and neither 1 or 2 done.I am sure that if I delete the record with orders.guid = ‘8C87B2E5312B458AA60825F06E4DFD83’ and all children then my code will do its job without errors.
————————————————————————————————————————————————————————————————
