BatchProcessorAllRulesTest SQL Log

prof: Total queries: 63, number of unique sql: 22

SELECT from claim_batch_log, repeated: 5 times
select batchinfo0_.etl_batch_id, batchinfo0_.batch_status, batchinfo0_.last_updated_user, batchinfo0_.end_date_time, batchinfo0_.source_system, batchinfo0_.last_updated_date from claim_batch_log batchinfo0_ where batchinfo0_.etl_batch_id=?
INSERT into rules_batch_log, repeated: 1 times
insert into rules_batch_log (rules_batch_log_key, etl_batch_id, claim_type, engine_version, error, end_date_time, hostname, resume_mode, last_updated_user, number_of_claims, number_of_failures, number_of_lines, number_of_processed_claims, number_of_processed_lines, number_of_waiting_for_data, rule_names, environment_name, start_date_time, status, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
UPDATE update claim_batch_log, repeated: 4 times
update claim_batch_log set batch_status=?, last_updated_user=?, end_date_time=?, source_system=?, last_updated_date=? where etl_batch_id=?
SELECT from rules_batch_log, repeated: 3 times
select processing0_.rules_batch_log_key, processing0_.etl_batch_id, processing0_.claim_type, processing0_.engine_version, processing0_.error, processing0_.end_date_time, processing0_.hostname, processing0_.resume_mode, processing0_.last_updated_user, processing0_.number_of_claims, processing0_.number_of_failures, processing0_.number_of_lines, processing0_.number_of_processed_claims, processing0_.number_of_processed_lines, processing0_.number_of_waiting_for_data, processing0_.rule_names, processing0_.environment_name, processing0_.start_date_time, processing0_.status, processing0_.last_updated_date_time from rules_batch_log processing0_ where processing0_.rules_batch_log_key=?
UPDATE update rules_batch_log, repeated: 3 times
update rules_batch_log set etl_batch_id=?, claim_type=?, engine_version=?, error=?, end_date_time=?, hostname=?, resume_mode=?, last_updated_user=?, number_of_claims=?, number_of_failures=?, number_of_lines=?, number_of_processed_claims=?, number_of_processed_lines=?, number_of_waiting_for_data=?, rule_names=?, environment_name=?, start_date_time=?, status=?, last_updated_date_time=? where rules_batch_log_key=?
SELECT from rules.charge_amount_threshold, repeated: 1 times
select dollarthre0_.charge_amount_threshold_key, dollarthre0_.claim_type, dollarthre0_.last_updated_user, dollarthre0_.threshold_amount, dollarthre0_.last_updated_date_time from rules.charge_amount_threshold dollarthre0_
DELETE from validation_failure, repeated: 1 times
delete from validation_failure where decision_key in ( select decision1_.decision_key from decision decision1_ where decision1_.etl_batch_id=? and decision1_.decision<>'REJECTED' )
DELETE from decision, repeated: 1 times
delete from decision where etl_batch_id=? and decision<>'REJECTED'
SELECT from dim_va_claim, repeated: 1 times
select distinct claim0_.claim_key, formtype1_.form_type_key, profclaiml2_.claim_detail_key, placeofser7_.pos_key, provider8_.provider_key, locations9_.provider_location_key, procedure10_.procedure_key, modifiers11_.claim_line_modifier_key, provider3_.provider_key, locations5_.provider_location_key, provider4_.provider_key, patient6_.patient_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, profclaiml2_.etl_batch_id, profclaiml2_.claim_key, profclaiml2_.date_paid, profclaiml2_.is_current, profclaiml2_.line_item_control_number, profclaiml2_.charge_amount, profclaiml2_.pos_key, profclaiml2_.procedure_key, profclaiml2_.rendering_provider_key, profclaiml2_.service_date_from, profclaiml2_.service_date_to, profclaiml2_.claim_key, profclaiml2_.claim_detail_key, placeofser7_.pos_code, provider8_.provider_name, provider8_.npi, provider8_.tax_id, locations9_.address1, locations9_.city, locations9_.country_code, locations9_.postal_code, locations9_.provider_key, locations9_.provider_phone, locations9_.state_long_name, locations9_.provider_key, locations9_.provider_location_key, procedure10_.procedure_code, procedure10_.short_description, modifiers11_.modifier_code, modifiers11_.claim_detail_key_dt, modifiers11_.claim_detail_key_in, modifiers11_.is_current, modifiers11_.claim_detail_key_pr, modifiers11_.claim_detail_key_pr, modifiers11_.claim_line_modifier_key, provider3_.provider_name, provider3_.npi, provider3_.tax_id, locations5_.address1, locations5_.city, locations5_.country_code, locations5_.postal_code, locations5_.provider_key, locations5_.provider_phone, locations5_.state_long_name, locations5_.provider_key, locations5_.provider_location_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_professional_medical_claim_details profclaiml2_ on claim0_.claim_key=profclaiml2_.claim_key left outer join dim_place_of_service placeofser7_ on profclaiml2_.pos_key=placeofser7_.pos_key left outer join dim_provider provider8_ on profclaiml2_.rendering_provider_key=provider8_.provider_key left outer join provider_location locations9_ on provider8_.provider_key=locations9_.provider_key left outer join dim_procedure_code procedure10_ on profclaiml2_.procedure_key=procedure10_.procedure_key left outer join claim_line_modifier modifiers11_ on profclaiml2_.claim_detail_key=modifiers11_.claim_detail_key_pr left outer join dim_provider provider3_ on claim0_.billing_provider_key=provider3_.provider_key left outer join provider_location locations5_ on provider3_.provider_key=locations5_.provider_key left outer join dim_provider provider4_ on claim0_.rendering_provider_key=provider4_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key where claim0_.is_current='Y' and profclaiml2_.is_current='Y' and ( modifiers11_.claim_line_modifier_key is null or modifiers11_.is_current='Y' ) and formtype1_.form_code='837P' and ( profclaiml2_.claim_detail_key not in ( select decision12_.claim_detail_key from decision decision12_ where decision12_.claim_key=claim0_.claim_key ) ) and claim0_.etl_batch_id=?
SELECT from ccra.referral, repeated: 6 times
select referral0_.referral_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested from ccra.referral referral0_ where referral0_.referral_number=?
SELECT from dim_va_claim, repeated: 3 times
select distinct claim0_.claim_key, formtype1_.form_type_key, profclaiml2_.claim_detail_key, placeofser7_.pos_key, provider8_.provider_key, procedure9_.procedure_key, modifiers10_.claim_line_modifier_key, decisions3_.decision_key, provider4_.provider_key, provider5_.provider_key, patient6_.patient_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, profclaiml2_.etl_batch_id, profclaiml2_.claim_key, profclaiml2_.date_paid, profclaiml2_.is_current, profclaiml2_.line_item_control_number, profclaiml2_.charge_amount, profclaiml2_.pos_key, profclaiml2_.procedure_key, profclaiml2_.rendering_provider_key, profclaiml2_.service_date_from, profclaiml2_.service_date_to, profclaiml2_.claim_key, profclaiml2_.claim_detail_key, placeofser7_.pos_code, provider8_.provider_name, provider8_.npi, provider8_.tax_id, procedure9_.procedure_code, procedure9_.short_description, modifiers10_.modifier_code, modifiers10_.claim_detail_key_dt, modifiers10_.claim_detail_key_in, modifiers10_.is_current, modifiers10_.claim_detail_key_pr, modifiers10_.claim_detail_key_pr, modifiers10_.claim_line_modifier_key, decisions3_.etl_batch_id, decisions3_.claim_key, decisions3_.claim_detail_key, decisions3_.decision_date_time, decisions3_.decision, decisions3_.last_updated_user, decisions3_.last_updated_date_time, decisions3_.claim_key, decisions3_.decision_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, provider5_.provider_name, provider5_.npi, provider5_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_professional_medical_claim_details profclaiml2_ on claim0_.claim_key=profclaiml2_.claim_key left outer join dim_place_of_service placeofser7_ on profclaiml2_.pos_key=placeofser7_.pos_key left outer join dim_provider provider8_ on profclaiml2_.rendering_provider_key=provider8_.provider_key left outer join dim_procedure_code procedure9_ on profclaiml2_.procedure_key=procedure9_.procedure_key left outer join claim_line_modifier modifiers10_ on profclaiml2_.claim_detail_key=modifiers10_.claim_detail_key_pr inner join decision decisions3_ on claim0_.claim_key=decisions3_.claim_key left outer join dim_provider provider4_ on claim0_.billing_provider_key=provider4_.provider_key left outer join dim_provider provider5_ on claim0_.rendering_provider_key=provider5_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key where claim0_.is_current='Y' and ( decisions3_.decision in ( 'APPROVED' , 'BILL_SENT' , 'PAID' ) ) and claim0_.source_system='EDI'
SELECT from ccra.referral, repeated: 3 times
select distinct referral0_.referral_key, preauthori1_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, preauthori1_.date_from, preauthori1_.date_to, preauthori1_.preauthorization_number, preauthori1_.referral_key, preauthori1_.referral_key, preauthori1_.preauthorization_key from ccra.referral referral0_ left outer join ccra.preauthorization preauthori1_ on referral0_.referral_key=preauthori1_.referral_key where referral0_.referral_number=?
SELECT from ccra.preauthorization_detail, repeated: 3 times
select preauthori0_.preauthorization_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_key, preauthori0_.preauthorized_service, preauthori0_.quantity, preauthori0_.subcategory, orderset1_.orderset_key, orderset1_.code, orderset1_.order_set_description, orderset1_.pre_authorization_detail_key from ccra.preauthorization_detail preauthori0_ left outer join ccra.orderset orderset1_ on preauthori0_.preauthorization_detail_key=orderset1_.pre_authorization_detail_key where preauthori0_.preauthorization_key=?
SELECT from ccra.ordersetdate, repeated: 2 times
select ordersetda0_.orderset_date_key, ordersetda0_.date_from, ordersetda1_.orderset_date_key, ordersetda1_.orderset_date_item_key, ordersetda1_.orderset_date_item_key, ordersetda1_.orderset_date_key, ordersetda1_.procedure_code, orderset2_.orderset_key, orderset2_.code, orderset2_.order_set_description, orderset2_.pre_authorization_detail_key from ccra.ordersetdate ordersetda0_ left outer join ccra.ordersetdateitem ordersetda1_ on ordersetda0_.orderset_date_key=ordersetda1_.orderset_date_key left outer join ccra.orderset orderset2_ on ordersetda0_.orderset_date_key=orderset2_.orderset_key where ordersetda0_.orderset_date_key=?
SELECT from seoc, repeated: 1 times
select seoc0_.seoc_key, seoc0_.category_of_care, seoc0_.duration, seoc0_.end_date, seoc0_.is_pal, seoc0_.name, seoc0_.seoc_id, seoc0_.start_date, seoc0_.version from seoc seoc0_ where seoc0_.seoc_id=?
SELECT from seoc_procedure_junction, repeated: 1 times
select procedures0_.seoc_key, procedures0_.procedure_key, procedures0_.seoc_key, procedures0_.procedure_key, procedure1_.procedure_key, procedure1_.procedure_code, procedure1_.short_description from seoc_procedure_junction procedures0_ inner join dim_procedure_code procedure1_ on procedures0_.procedure_key=procedure1_.procedure_key where procedures0_.seoc_key=?
SELECT from ccra.referral, repeated: 3 times
select referral0_.referral_key, referralpa1_.referral_patient_key, preauthori2_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, referralpa1_.icn, referralpa1_.referral_key, referralpa1_.referral_key, referralpa1_.referral_patient_key, preauthori2_.date_from, preauthori2_.date_to, preauthori2_.preauthorization_number, preauthori2_.referral_key, preauthori2_.referral_key, preauthori2_.preauthorization_key from ccra.referral referral0_ inner join ccra.referral_patient referralpa1_ on referral0_.referral_key=referralpa1_.referral_key left outer join ccra.preauthorization preauthori2_ on referral0_.referral_key=preauthori2_.referral_key where referral0_.referral_number=? and referral0_.referral_date<=?
SELECT from lkup_provider_sanction, repeated: 3 times
select providersa0_.provider_sanction_key, providersa0_.exclusion_date, providersa0_.npi, providersa0_.reinstate_date, providersa0_.tax_id from lkup_provider_sanction providersa0_ where ( providersa0_.exclusion_date is not null ) and ( providersa0_.npi in ( ? ) )
SELECT from ccra.payment_authority_obligation, repeated: 3 times
select paymentaut0_.payment_authority_obligation_key, paymentaut0_.end_date, paymentaut0_.obligation_key, paymentaut0_.payment_authority_key, paymentaut0_.start_date from ccra.payment_authority_obligation paymentaut0_ where paymentaut0_.payment_authority_obligation_key=?
INSERT into decision, repeated: 5 times
insert into decision (decision_key, etl_batch_id, claim_key, claim_detail_key, decision_date_time, decision, last_updated_user, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?)
INSERT into validation_failure, repeated: 9 times
insert into validation_failure (validation_failure_key, carc_code, decision_key, last_updated_user, message, rarc_code, rule_name, last_updated_date_time, validation_name) values (null, ?, ?, ?, ?, ?, ?, ?, ?)
SELECT from decision, repeated: 1 times
select decision0_.decision_key, decision0_.etl_batch_id, decision0_.claim_key, decision0_.claim_detail_key, decision0_.decision_date_time, decision0_.decision, decision0_.last_updated_user, decision0_.last_updated_date_time from decision decision0_ where decision0_.etl_batch_id=?

inst: Total queries: 111, number of unique sql: 22

SELECT from claim_batch_log, repeated: 5 times
select batchinfo0_.etl_batch_id, batchinfo0_.batch_status, batchinfo0_.last_updated_user, batchinfo0_.end_date_time, batchinfo0_.source_system, batchinfo0_.last_updated_date from claim_batch_log batchinfo0_ where batchinfo0_.etl_batch_id=?
INSERT into rules_batch_log, repeated: 1 times
insert into rules_batch_log (rules_batch_log_key, etl_batch_id, claim_type, engine_version, error, end_date_time, hostname, resume_mode, last_updated_user, number_of_claims, number_of_failures, number_of_lines, number_of_processed_claims, number_of_processed_lines, number_of_waiting_for_data, rule_names, environment_name, start_date_time, status, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
UPDATE update claim_batch_log, repeated: 4 times
update claim_batch_log set batch_status=?, last_updated_user=?, end_date_time=?, source_system=?, last_updated_date=? where etl_batch_id=?
SELECT from rules_batch_log, repeated: 3 times
select processing0_.rules_batch_log_key, processing0_.etl_batch_id, processing0_.claim_type, processing0_.engine_version, processing0_.error, processing0_.end_date_time, processing0_.hostname, processing0_.resume_mode, processing0_.last_updated_user, processing0_.number_of_claims, processing0_.number_of_failures, processing0_.number_of_lines, processing0_.number_of_processed_claims, processing0_.number_of_processed_lines, processing0_.number_of_waiting_for_data, processing0_.rule_names, processing0_.environment_name, processing0_.start_date_time, processing0_.status, processing0_.last_updated_date_time from rules_batch_log processing0_ where processing0_.rules_batch_log_key=?
UPDATE update rules_batch_log, repeated: 3 times
update rules_batch_log set etl_batch_id=?, claim_type=?, engine_version=?, error=?, end_date_time=?, hostname=?, resume_mode=?, last_updated_user=?, number_of_claims=?, number_of_failures=?, number_of_lines=?, number_of_processed_claims=?, number_of_processed_lines=?, number_of_waiting_for_data=?, rule_names=?, environment_name=?, start_date_time=?, status=?, last_updated_date_time=? where rules_batch_log_key=?
SELECT from rules.charge_amount_threshold, repeated: 1 times
select dollarthre0_.charge_amount_threshold_key, dollarthre0_.claim_type, dollarthre0_.last_updated_user, dollarthre0_.threshold_amount, dollarthre0_.last_updated_date_time from rules.charge_amount_threshold dollarthre0_
DELETE from validation_failure, repeated: 1 times
delete from validation_failure where decision_key in ( select decision1_.decision_key from decision decision1_ where decision1_.etl_batch_id=? and decision1_.decision<>'REJECTED' )
DELETE from decision, repeated: 1 times
delete from decision where etl_batch_id=? and decision<>'REJECTED'
SELECT from dim_va_claim, repeated: 1 times
select distinct claim0_.claim_key, formtype1_.form_type_key, instclaiml2_.claim_detail_key, procedure8_.procedure_key, modifiers9_.claim_line_modifier_key, provider3_.provider_key, locations5_.provider_location_key, provider4_.provider_key, patient6_.patient_key, institutio7_.claim_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, instclaiml2_.etl_batch_id, instclaiml2_.claim_key, instclaiml2_.date_paid, instclaiml2_.is_current, instclaiml2_.line_item_control_number, instclaiml2_.charge_amount, instclaiml2_.procedure_key, instclaiml2_.revenue_code, instclaiml2_.service_date, instclaiml2_.claim_key, instclaiml2_.claim_detail_key, procedure8_.procedure_code, procedure8_.short_description, modifiers9_.modifier_code, modifiers9_.claim_detail_key_dt, modifiers9_.claim_detail_key_in, modifiers9_.is_current, modifiers9_.claim_detail_key_pr, modifiers9_.claim_detail_key_in, modifiers9_.claim_line_modifier_key, provider3_.provider_name, provider3_.npi, provider3_.tax_id, locations5_.address1, locations5_.city, locations5_.country_code, locations5_.postal_code, locations5_.provider_key, locations5_.provider_phone, locations5_.state_long_name, locations5_.provider_key, locations5_.provider_location_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code, institutio7_.admission_date, institutio7_.statement_from_date, institutio7_.statement_to_date from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_institutional_medical_claim_details instclaiml2_ on claim0_.claim_key=instclaiml2_.claim_key left outer join dim_procedure_code procedure8_ on instclaiml2_.procedure_key=procedure8_.procedure_key left outer join claim_line_modifier modifiers9_ on instclaiml2_.claim_detail_key=modifiers9_.claim_detail_key_in left outer join dim_provider provider3_ on claim0_.billing_provider_key=provider3_.provider_key left outer join provider_location locations5_ on provider3_.provider_key=locations5_.provider_key left outer join dim_provider provider4_ on claim0_.rendering_provider_key=provider4_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key inner join dim_institutional_claim institutio7_ on claim0_.claim_key=institutio7_.claim_key where claim0_.is_current='Y' and instclaiml2_.is_current='Y' and ( modifiers9_.claim_line_modifier_key is null or modifiers9_.is_current='Y' ) and formtype1_.form_code='837I' and ( instclaiml2_.claim_detail_key not in ( select decision10_.claim_detail_key from decision decision10_ where decision10_.claim_key=claim0_.claim_key ) ) and claim0_.etl_batch_id=?
SELECT from ccra.referral, repeated: 14 times
select referral0_.referral_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested from ccra.referral referral0_ where referral0_.referral_number=?
SELECT from dim_va_claim, repeated: 3 times
select distinct claim0_.claim_key, formtype1_.form_type_key, instclaiml2_.claim_detail_key, procedure8_.procedure_key, modifiers9_.claim_line_modifier_key, decisions3_.decision_key, provider4_.provider_key, provider5_.provider_key, patient6_.patient_key, institutio7_.claim_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, instclaiml2_.etl_batch_id, instclaiml2_.claim_key, instclaiml2_.date_paid, instclaiml2_.is_current, instclaiml2_.line_item_control_number, instclaiml2_.charge_amount, instclaiml2_.procedure_key, instclaiml2_.revenue_code, instclaiml2_.service_date, instclaiml2_.claim_key, instclaiml2_.claim_detail_key, procedure8_.procedure_code, procedure8_.short_description, modifiers9_.modifier_code, modifiers9_.claim_detail_key_dt, modifiers9_.claim_detail_key_in, modifiers9_.is_current, modifiers9_.claim_detail_key_pr, modifiers9_.claim_detail_key_in, modifiers9_.claim_line_modifier_key, decisions3_.etl_batch_id, decisions3_.claim_key, decisions3_.claim_detail_key, decisions3_.decision_date_time, decisions3_.decision, decisions3_.last_updated_user, decisions3_.last_updated_date_time, decisions3_.claim_key, decisions3_.decision_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, provider5_.provider_name, provider5_.npi, provider5_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code, institutio7_.admission_date, institutio7_.statement_from_date, institutio7_.statement_to_date from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_institutional_medical_claim_details instclaiml2_ on claim0_.claim_key=instclaiml2_.claim_key left outer join dim_procedure_code procedure8_ on instclaiml2_.procedure_key=procedure8_.procedure_key left outer join claim_line_modifier modifiers9_ on instclaiml2_.claim_detail_key=modifiers9_.claim_detail_key_in inner join decision decisions3_ on claim0_.claim_key=decisions3_.claim_key left outer join dim_provider provider4_ on claim0_.billing_provider_key=provider4_.provider_key left outer join dim_provider provider5_ on claim0_.rendering_provider_key=provider5_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key inner join dim_institutional_claim institutio7_ on claim0_.claim_key=institutio7_.claim_key where claim0_.is_current='Y' and ( decisions3_.decision in ( 'APPROVED' , 'BILL_SENT' , 'PAID' ) )
SELECT from ccra.referral, repeated: 7 times
select distinct referral0_.referral_key, preauthori1_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, preauthori1_.date_from, preauthori1_.date_to, preauthori1_.preauthorization_number, preauthori1_.referral_key, preauthori1_.referral_key, preauthori1_.preauthorization_key from ccra.referral referral0_ left outer join ccra.preauthorization preauthori1_ on referral0_.referral_key=preauthori1_.referral_key where referral0_.referral_number=?
SELECT from ccra.preauthorization_detail, repeated: 6 times
select preauthori0_.preauthorization_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_key, preauthori0_.preauthorized_service, preauthori0_.quantity, preauthori0_.subcategory, orderset1_.orderset_key, orderset1_.code, orderset1_.order_set_description, orderset1_.pre_authorization_detail_key from ccra.preauthorization_detail preauthori0_ left outer join ccra.orderset orderset1_ on preauthori0_.preauthorization_detail_key=orderset1_.pre_authorization_detail_key where preauthori0_.preauthorization_key=?
SELECT from ccra.ordersetdate, repeated: 5 times
select ordersetda0_.orderset_date_key, ordersetda0_.date_from, ordersetda1_.orderset_date_key, ordersetda1_.orderset_date_item_key, ordersetda1_.orderset_date_item_key, ordersetda1_.orderset_date_key, ordersetda1_.procedure_code, orderset2_.orderset_key, orderset2_.code, orderset2_.order_set_description, orderset2_.pre_authorization_detail_key from ccra.ordersetdate ordersetda0_ left outer join ccra.ordersetdateitem ordersetda1_ on ordersetda0_.orderset_date_key=ordersetda1_.orderset_date_key left outer join ccra.orderset orderset2_ on ordersetda0_.orderset_date_key=orderset2_.orderset_key where ordersetda0_.orderset_date_key=?
SELECT from seoc, repeated: 5 times
select seoc0_.seoc_key, seoc0_.category_of_care, seoc0_.duration, seoc0_.end_date, seoc0_.is_pal, seoc0_.name, seoc0_.seoc_id, seoc0_.start_date, seoc0_.version from seoc seoc0_ where seoc0_.seoc_id=?
SELECT from ccra.referral, repeated: 7 times
select referral0_.referral_key, referralpa1_.referral_patient_key, preauthori2_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, referralpa1_.icn, referralpa1_.referral_key, referralpa1_.referral_key, referralpa1_.referral_patient_key, preauthori2_.date_from, preauthori2_.date_to, preauthori2_.preauthorization_number, preauthori2_.referral_key, preauthori2_.referral_key, preauthori2_.preauthorization_key from ccra.referral referral0_ inner join ccra.referral_patient referralpa1_ on referral0_.referral_key=referralpa1_.referral_key left outer join ccra.preauthorization preauthori2_ on referral0_.referral_key=preauthori2_.referral_key where referral0_.referral_number=? and referral0_.referral_date<=?
SELECT from seoc_procedure_junction, repeated: 5 times
select procedures0_.seoc_key, procedures0_.procedure_key, procedures0_.seoc_key, procedures0_.procedure_key, procedure1_.procedure_key, procedure1_.procedure_code, procedure1_.short_description from seoc_procedure_junction procedures0_ inner join dim_procedure_code procedure1_ on procedures0_.procedure_key=procedure1_.procedure_key where procedures0_.seoc_key=?
SELECT from lkup_provider_sanction, repeated: 7 times
select providersa0_.provider_sanction_key, providersa0_.exclusion_date, providersa0_.npi, providersa0_.reinstate_date, providersa0_.tax_id from lkup_provider_sanction providersa0_ where ( providersa0_.exclusion_date is not null ) and ( providersa0_.npi in ( ? ) )
SELECT from ccra.payment_authority_obligation, repeated: 6 times
select paymentaut0_.payment_authority_obligation_key, paymentaut0_.end_date, paymentaut0_.obligation_key, paymentaut0_.payment_authority_key, paymentaut0_.start_date from ccra.payment_authority_obligation paymentaut0_ where paymentaut0_.payment_authority_obligation_key=?
INSERT into decision, repeated: 8 times
insert into decision (decision_key, etl_batch_id, claim_key, claim_detail_key, decision_date_time, decision, last_updated_user, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?)
INSERT into validation_failure, repeated: 17 times
insert into validation_failure (validation_failure_key, carc_code, decision_key, last_updated_user, message, rarc_code, rule_name, last_updated_date_time, validation_name) values (null, ?, ?, ?, ?, ?, ?, ?, ?)
SELECT from decision, repeated: 1 times
select decision0_.decision_key, decision0_.etl_batch_id, decision0_.claim_key, decision0_.claim_detail_key, decision0_.decision_date_time, decision0_.decision, decision0_.last_updated_user, decision0_.last_updated_date_time from decision decision0_ where decision0_.etl_batch_id=?

dental: Total queries: 98, number of unique sql: 23

SELECT from claim_batch_log, repeated: 5 times
select batchinfo0_.etl_batch_id, batchinfo0_.batch_status, batchinfo0_.last_updated_user, batchinfo0_.end_date_time, batchinfo0_.source_system, batchinfo0_.last_updated_date from claim_batch_log batchinfo0_ where batchinfo0_.etl_batch_id=?
INSERT into rules_batch_log, repeated: 1 times
insert into rules_batch_log (rules_batch_log_key, etl_batch_id, claim_type, engine_version, error, end_date_time, hostname, resume_mode, last_updated_user, number_of_claims, number_of_failures, number_of_lines, number_of_processed_claims, number_of_processed_lines, number_of_waiting_for_data, rule_names, environment_name, start_date_time, status, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
UPDATE update claim_batch_log, repeated: 4 times
update claim_batch_log set batch_status=?, last_updated_user=?, end_date_time=?, source_system=?, last_updated_date=? where etl_batch_id=?
SELECT from rules_batch_log, repeated: 3 times
select processing0_.rules_batch_log_key, processing0_.etl_batch_id, processing0_.claim_type, processing0_.engine_version, processing0_.error, processing0_.end_date_time, processing0_.hostname, processing0_.resume_mode, processing0_.last_updated_user, processing0_.number_of_claims, processing0_.number_of_failures, processing0_.number_of_lines, processing0_.number_of_processed_claims, processing0_.number_of_processed_lines, processing0_.number_of_waiting_for_data, processing0_.rule_names, processing0_.environment_name, processing0_.start_date_time, processing0_.status, processing0_.last_updated_date_time from rules_batch_log processing0_ where processing0_.rules_batch_log_key=?
UPDATE update rules_batch_log, repeated: 3 times
update rules_batch_log set etl_batch_id=?, claim_type=?, engine_version=?, error=?, end_date_time=?, hostname=?, resume_mode=?, last_updated_user=?, number_of_claims=?, number_of_failures=?, number_of_lines=?, number_of_processed_claims=?, number_of_processed_lines=?, number_of_waiting_for_data=?, rule_names=?, environment_name=?, start_date_time=?, status=?, last_updated_date_time=? where rules_batch_log_key=?
SELECT from rules.charge_amount_threshold, repeated: 1 times
select dollarthre0_.charge_amount_threshold_key, dollarthre0_.claim_type, dollarthre0_.last_updated_user, dollarthre0_.threshold_amount, dollarthre0_.last_updated_date_time from rules.charge_amount_threshold dollarthre0_
DELETE from validation_failure, repeated: 1 times
delete from validation_failure where decision_key in ( select decision1_.decision_key from decision decision1_ where decision1_.etl_batch_id=? and decision1_.decision<>'REJECTED' )
DELETE from decision, repeated: 1 times
delete from decision where etl_batch_id=? and decision<>'REJECTED'
SELECT from dim_va_claim, repeated: 1 times
select distinct claim0_.claim_key, formtype1_.form_type_key, dentclaiml2_.claim_detail_key, procedure8_.procedure_key, modifiers9_.claim_line_modifier_key, provider3_.provider_key, locations5_.provider_location_key, provider4_.provider_key, patient6_.patient_key, dentclaimt7_.claim_tooth_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, dentclaiml2_.etl_batch_id, dentclaiml2_.claim_key, dentclaiml2_.date_paid, dentclaiml2_.is_current, dentclaiml2_.line_item_control_number, dentclaiml2_.charge_amount, dentclaiml2_.procedure_key, dentclaiml2_.service_date, dentclaiml2_.claim_key, dentclaiml2_.claim_detail_key, procedure8_.procedure_code, procedure8_.short_description, modifiers9_.modifier_code, modifiers9_.claim_detail_key_dt, modifiers9_.claim_detail_key_in, modifiers9_.is_current, modifiers9_.claim_detail_key_pr, modifiers9_.claim_detail_key_dt, modifiers9_.claim_line_modifier_key, provider3_.provider_name, provider3_.npi, provider3_.tax_id, locations5_.address1, locations5_.city, locations5_.country_code, locations5_.postal_code, locations5_.provider_key, locations5_.provider_phone, locations5_.state_long_name, locations5_.provider_key, locations5_.provider_location_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code, dentclaimt7_.claim_key, dentclaimt7_.end_date, dentclaimt7_.start_date, dentclaimt7_.tooth_number, dentclaimt7_.tooth_status, dentclaimt7_.claim_key, dentclaimt7_.claim_tooth_key from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_dental_claim_details dentclaiml2_ on claim0_.claim_key=dentclaiml2_.claim_key left outer join dim_procedure_code procedure8_ on dentclaiml2_.procedure_key=procedure8_.procedure_key left outer join claim_line_modifier modifiers9_ on dentclaiml2_.claim_detail_key=modifiers9_.claim_detail_key_dt left outer join dim_provider provider3_ on claim0_.billing_provider_key=provider3_.provider_key left outer join provider_location locations5_ on provider3_.provider_key=locations5_.provider_key left outer join dim_provider provider4_ on claim0_.rendering_provider_key=provider4_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key left outer join claim_tooth_status dentclaimt7_ on claim0_.claim_key=dentclaimt7_.claim_key where claim0_.is_current='Y' and dentclaiml2_.is_current='Y' and ( modifiers9_.claim_line_modifier_key is null or modifiers9_.is_current='Y' ) and formtype1_.form_code='837D' and ( dentclaiml2_.claim_detail_key not in ( select decision10_.claim_detail_key from decision decision10_ where decision10_.claim_key=claim0_.claim_key ) ) and claim0_.etl_batch_id=?
SELECT from ccra.referral, repeated: 12 times
select referral0_.referral_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested from ccra.referral referral0_ where referral0_.referral_number=?
SELECT from dim_va_claim, repeated: 3 times
select distinct claim0_.claim_key, formtype1_.form_type_key, dentclaiml2_.claim_detail_key, procedure8_.procedure_key, modifiers9_.claim_line_modifier_key, decisions3_.decision_key, provider4_.provider_key, provider5_.provider_key, patient6_.patient_key, dentclaimt7_.claim_tooth_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, dentclaiml2_.etl_batch_id, dentclaiml2_.claim_key, dentclaiml2_.date_paid, dentclaiml2_.is_current, dentclaiml2_.line_item_control_number, dentclaiml2_.charge_amount, dentclaiml2_.procedure_key, dentclaiml2_.service_date, dentclaiml2_.claim_key, dentclaiml2_.claim_detail_key, procedure8_.procedure_code, procedure8_.short_description, modifiers9_.modifier_code, modifiers9_.claim_detail_key_dt, modifiers9_.claim_detail_key_in, modifiers9_.is_current, modifiers9_.claim_detail_key_pr, modifiers9_.claim_detail_key_dt, modifiers9_.claim_line_modifier_key, decisions3_.etl_batch_id, decisions3_.claim_key, decisions3_.claim_detail_key, decisions3_.decision_date_time, decisions3_.decision, decisions3_.last_updated_user, decisions3_.last_updated_date_time, decisions3_.claim_key, decisions3_.decision_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, provider5_.provider_name, provider5_.npi, provider5_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code, dentclaimt7_.claim_key, dentclaimt7_.end_date, dentclaimt7_.start_date, dentclaimt7_.tooth_number, dentclaimt7_.tooth_status, dentclaimt7_.claim_key, dentclaimt7_.claim_tooth_key from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_dental_claim_details dentclaiml2_ on claim0_.claim_key=dentclaiml2_.claim_key left outer join dim_procedure_code procedure8_ on dentclaiml2_.procedure_key=procedure8_.procedure_key left outer join claim_line_modifier modifiers9_ on dentclaiml2_.claim_detail_key=modifiers9_.claim_detail_key_dt inner join decision decisions3_ on claim0_.claim_key=decisions3_.claim_key left outer join dim_provider provider4_ on claim0_.billing_provider_key=provider4_.provider_key left outer join dim_provider provider5_ on claim0_.rendering_provider_key=provider5_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key left outer join claim_tooth_status dentclaimt7_ on claim0_.claim_key=dentclaimt7_.claim_key where claim0_.is_current='Y' and ( decisions3_.decision in ( 'APPROVED' , 'BILL_SENT' , 'PAID' ) )
SELECT from ccra.referral, repeated: 6 times
select distinct referral0_.referral_key, preauthori1_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, preauthori1_.date_from, preauthori1_.date_to, preauthori1_.preauthorization_number, preauthori1_.referral_key, preauthori1_.referral_key, preauthori1_.preauthorization_key from ccra.referral referral0_ left outer join ccra.preauthorization preauthori1_ on referral0_.referral_key=preauthori1_.referral_key where referral0_.referral_number=?
SELECT from ccra.preauthorization_detail, repeated: 6 times
select preauthori0_.preauthorization_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_key, preauthori0_.preauthorized_service, preauthori0_.quantity, preauthori0_.subcategory, orderset1_.orderset_key, orderset1_.code, orderset1_.order_set_description, orderset1_.pre_authorization_detail_key from ccra.preauthorization_detail preauthori0_ left outer join ccra.orderset orderset1_ on preauthori0_.preauthorization_detail_key=orderset1_.pre_authorization_detail_key where preauthori0_.preauthorization_key=?
SELECT from ccra.ordersetdate, repeated: 5 times
select ordersetda0_.orderset_date_key, ordersetda0_.date_from, ordersetda1_.orderset_date_key, ordersetda1_.orderset_date_item_key, ordersetda1_.orderset_date_item_key, ordersetda1_.orderset_date_key, ordersetda1_.procedure_code, orderset2_.orderset_key, orderset2_.code, orderset2_.order_set_description, orderset2_.pre_authorization_detail_key from ccra.ordersetdate ordersetda0_ left outer join ccra.ordersetdateitem ordersetda1_ on ordersetda0_.orderset_date_key=ordersetda1_.orderset_date_key left outer join ccra.orderset orderset2_ on ordersetda0_.orderset_date_key=orderset2_.orderset_key where ordersetda0_.orderset_date_key=?
SELECT from seoc, repeated: 4 times
select seoc0_.seoc_key, seoc0_.category_of_care, seoc0_.duration, seoc0_.end_date, seoc0_.is_pal, seoc0_.name, seoc0_.seoc_id, seoc0_.start_date, seoc0_.version from seoc seoc0_ where seoc0_.seoc_id=?
SELECT from seoc_procedure_junction, repeated: 4 times
select procedures0_.seoc_key, procedures0_.procedure_key, procedures0_.seoc_key, procedures0_.procedure_key, procedure1_.procedure_key, procedure1_.procedure_code, procedure1_.short_description from seoc_procedure_junction procedures0_ inner join dim_procedure_code procedure1_ on procedures0_.procedure_key=procedure1_.procedure_key where procedures0_.seoc_key=?
SELECT from ccra.referral, repeated: 6 times
select referral0_.referral_key, referralpa1_.referral_patient_key, preauthori2_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, referralpa1_.icn, referralpa1_.referral_key, referralpa1_.referral_key, referralpa1_.referral_patient_key, preauthori2_.date_from, preauthori2_.date_to, preauthori2_.preauthorization_number, preauthori2_.referral_key, preauthori2_.referral_key, preauthori2_.preauthorization_key from ccra.referral referral0_ inner join ccra.referral_patient referralpa1_ on referral0_.referral_key=referralpa1_.referral_key left outer join ccra.preauthorization preauthori2_ on referral0_.referral_key=preauthori2_.referral_key where referral0_.referral_number=? and referral0_.referral_date<=?
SELECT from lkup_provider_sanction, repeated: 5 times
select providersa0_.provider_sanction_key, providersa0_.exclusion_date, providersa0_.npi, providersa0_.reinstate_date, providersa0_.tax_id from lkup_provider_sanction providersa0_ where ( providersa0_.exclusion_date is not null ) and ( providersa0_.npi in ( ? ) )
SELECT from ccra.payment_authority_obligation, repeated: 5 times
select paymentaut0_.payment_authority_obligation_key, paymentaut0_.end_date, paymentaut0_.obligation_key, paymentaut0_.payment_authority_key, paymentaut0_.start_date from ccra.payment_authority_obligation paymentaut0_ where paymentaut0_.payment_authority_obligation_key=?
INSERT into decision, repeated: 7 times
insert into decision (decision_key, etl_batch_id, claim_key, claim_detail_key, decision_date_time, decision, last_updated_user, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?)
INSERT into validation_failure, repeated: 13 times
insert into validation_failure (validation_failure_key, carc_code, decision_key, last_updated_user, message, rarc_code, rule_name, last_updated_date_time, validation_name) values (null, ?, ?, ?, ?, ?, ?, ?, ?)
SELECT from lkup_provider_sanction, repeated: 1 times
select providersa0_.provider_sanction_key, providersa0_.exclusion_date, providersa0_.npi, providersa0_.reinstate_date, providersa0_.tax_id from lkup_provider_sanction providersa0_ where ( providersa0_.exclusion_date is not null ) and ( providersa0_.tax_id in ( ? ) )
SELECT from decision, repeated: 1 times
select decision0_.decision_key, decision0_.etl_batch_id, decision0_.claim_key, decision0_.claim_detail_key, decision0_.decision_date_time, decision0_.decision, decision0_.last_updated_user, decision0_.last_updated_date_time from decision decision0_ where decision0_.etl_batch_id=?

pharmacy: Total queries: 27, number of unique sql: 16

SELECT from claim_batch_log, repeated: 5 times
select batchinfo0_.etl_batch_id, batchinfo0_.batch_status, batchinfo0_.last_updated_user, batchinfo0_.end_date_time, batchinfo0_.source_system, batchinfo0_.last_updated_date from claim_batch_log batchinfo0_ where batchinfo0_.etl_batch_id=?
INSERT into rules_batch_log, repeated: 1 times
insert into rules_batch_log (rules_batch_log_key, etl_batch_id, claim_type, engine_version, error, end_date_time, hostname, resume_mode, last_updated_user, number_of_claims, number_of_failures, number_of_lines, number_of_processed_claims, number_of_processed_lines, number_of_waiting_for_data, rule_names, environment_name, start_date_time, status, last_updated_date_time) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
UPDATE update claim_batch_log, repeated: 4 times
update claim_batch_log set batch_status=?, last_updated_user=?, end_date_time=?, source_system=?, last_updated_date=? where etl_batch_id=?
SELECT from rules_batch_log, repeated: 3 times
select processing0_.rules_batch_log_key, processing0_.etl_batch_id, processing0_.claim_type, processing0_.engine_version, processing0_.error, processing0_.end_date_time, processing0_.hostname, processing0_.resume_mode, processing0_.last_updated_user, processing0_.number_of_claims, processing0_.number_of_failures, processing0_.number_of_lines, processing0_.number_of_processed_claims, processing0_.number_of_processed_lines, processing0_.number_of_waiting_for_data, processing0_.rule_names, processing0_.environment_name, processing0_.start_date_time, processing0_.status, processing0_.last_updated_date_time from rules_batch_log processing0_ where processing0_.rules_batch_log_key=?
UPDATE update rules_batch_log, repeated: 3 times
update rules_batch_log set etl_batch_id=?, claim_type=?, engine_version=?, error=?, end_date_time=?, hostname=?, resume_mode=?, last_updated_user=?, number_of_claims=?, number_of_failures=?, number_of_lines=?, number_of_processed_claims=?, number_of_processed_lines=?, number_of_waiting_for_data=?, rule_names=?, environment_name=?, start_date_time=?, status=?, last_updated_date_time=? where rules_batch_log_key=?
SELECT from rules.charge_amount_threshold, repeated: 1 times
select dollarthre0_.charge_amount_threshold_key, dollarthre0_.claim_type, dollarthre0_.last_updated_user, dollarthre0_.threshold_amount, dollarthre0_.last_updated_date_time from rules.charge_amount_threshold dollarthre0_
DELETE from validation_failure, repeated: 1 times
delete from validation_failure where decision_key in ( select decision1_.decision_key from decision decision1_ where decision1_.etl_batch_id=? and decision1_.decision<>'REJECTED' )
DELETE from decision, repeated: 1 times
delete from decision where etl_batch_id=? and decision<>'REJECTED'
SELECT from dim_va_claim, repeated: 1 times
select distinct claim0_.claim_key, formtype1_.form_type_key, pharmclaim2_.claim_detail_key, provider3_.provider_key, locations5_.provider_location_key, provider4_.provider_key, patient6_.patient_key, pharmacycl7_.claim_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, pharmclaim2_.etl_batch_id, pharmclaim2_.claim_key, pharmclaim2_.date_paid, pharmclaim2_.is_current, pharmclaim2_.line_item_control_number, pharmclaim2_.gross_amount_due, pharmclaim2_.product_id, pharmclaim2_.prescription_service_reference, pharmclaim2_.quantity_dispensed, pharmclaim2_.date_of_service, pharmclaim2_.claim_key, pharmclaim2_.claim_detail_key, provider3_.provider_name, provider3_.npi, provider3_.tax_id, locations5_.address1, locations5_.city, locations5_.country_code, locations5_.postal_code, locations5_.provider_key, locations5_.provider_phone, locations5_.state_long_name, locations5_.provider_key, locations5_.provider_location_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, patient6_.address1, patient6_.address2, patient6_.etl_batch_id, patient6_.city, patient6_.date_of_birth, patient6_.first_name, patient6_.icn, patient6_.insured_id, patient6_.last_name, patient6_.member_id, patient6_.middle_initial, patient6_.postal_code, patient6_.sex, patient6_.ssn, patient6_.state_code, pharmacycl7_.provider_key from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_pharmacy_claim_details pharmclaim2_ on claim0_.claim_key=pharmclaim2_.claim_key left outer join dim_provider provider3_ on claim0_.billing_provider_key=provider3_.provider_key left outer join provider_location locations5_ on provider3_.provider_key=locations5_.provider_key left outer join dim_provider provider4_ on claim0_.rendering_provider_key=provider4_.provider_key left outer join dim_patient patient6_ on claim0_.patient_key=patient6_.patient_key inner join dim_pharmacy_claim pharmacycl7_ on claim0_.claim_key=pharmacycl7_.claim_key where claim0_.is_current='Y' and pharmclaim2_.is_current='Y' and formtype1_.form_code='NCPDP D.0' and ( pharmclaim2_.claim_detail_key not in ( select decision8_.claim_detail_key from decision decision8_ where decision8_.claim_key=claim0_.claim_key ) ) and claim0_.etl_batch_id=?
SELECT from ccra.referral, repeated: 1 times
select referral0_.referral_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested from ccra.referral referral0_ where referral0_.referral_number=?
SELECT from dim_va_claim, repeated: 1 times
select distinct claim0_.claim_key, formtype1_.form_type_key, pharmclaim2_.claim_detail_key, decisions3_.decision_key, provider4_.provider_key, patient5_.patient_key, pharmacycl6_.claim_key, claim0_.etl_batch_id, claim0_.billing_provider_key, claim0_.ccn_region, claim0_.charge_amount, claim0_.claim_id, claim0_.end_date, claim0_.form_type_key, claim0_.is_current, claim0_.last_updated_date, claim0_.other_insurance, claim0_.patient_key, claim0_.patient_account_number, claim0_.preauth_number, claim0_.claim_receipt_date, claim0_.referral_number, claim0_.referring_provider_key, claim0_.rendering_provider_key, claim0_.source_system, claim0_.start_date, claim0_.station_key, formtype1_.form_code, pharmclaim2_.etl_batch_id, pharmclaim2_.claim_key, pharmclaim2_.date_paid, pharmclaim2_.is_current, pharmclaim2_.line_item_control_number, pharmclaim2_.gross_amount_due, pharmclaim2_.product_id, pharmclaim2_.prescription_service_reference, pharmclaim2_.quantity_dispensed, pharmclaim2_.date_of_service, pharmclaim2_.claim_key, pharmclaim2_.claim_detail_key, decisions3_.etl_batch_id, decisions3_.claim_key, decisions3_.claim_detail_key, decisions3_.decision_date_time, decisions3_.decision, decisions3_.last_updated_user, decisions3_.last_updated_date_time, decisions3_.claim_key, decisions3_.decision_key, provider4_.provider_name, provider4_.npi, provider4_.tax_id, patient5_.address1, patient5_.address2, patient5_.etl_batch_id, patient5_.city, patient5_.date_of_birth, patient5_.first_name, patient5_.icn, patient5_.insured_id, patient5_.last_name, patient5_.member_id, patient5_.middle_initial, patient5_.postal_code, patient5_.sex, patient5_.ssn, patient5_.state_code, pharmacycl6_.provider_key from dim_va_claim claim0_ inner join dim_claim_form_type formtype1_ on claim0_.form_type_key=formtype1_.form_type_key inner join f_pharmacy_claim_details pharmclaim2_ on claim0_.claim_key=pharmclaim2_.claim_key inner join decision decisions3_ on claim0_.claim_key=decisions3_.claim_key left outer join dim_provider provider4_ on claim0_.billing_provider_key=provider4_.provider_key left outer join dim_patient patient5_ on claim0_.patient_key=patient5_.patient_key inner join dim_pharmacy_claim pharmacycl6_ on claim0_.claim_key=pharmacycl6_.claim_key where claim0_.is_current='Y' and ( decisions3_.decision in ( 'APPROVED' , 'BILL_SENT' , 'PAID' ) )
SELECT from ccra.referral, repeated: 1 times
select distinct referral0_.referral_key, preauthori1_.preauthorization_key, referral0_.referral_expiration_date, referral0_.payment_authority_obligation_key, referral0_.referral_date, referral0_.referral_number, referral0_.referring_provider, referral0_.service_requested, preauthori1_.date_from, preauthori1_.date_to, preauthori1_.preauthorization_number, preauthori1_.referral_key, preauthori1_.referral_key, preauthori1_.preauthorization_key from ccra.referral referral0_ left outer join ccra.preauthorization preauthori1_ on referral0_.referral_key=preauthori1_.referral_key where referral0_.referral_number=?
SELECT from ccra.preauthorization_detail, repeated: 1 times
select preauthori0_.preauthorization_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_detail_key, preauthori0_.preauthorization_key, preauthori0_.preauthorized_service, preauthori0_.quantity, preauthori0_.subcategory, orderset1_.orderset_key, orderset1_.code, orderset1_.order_set_description, orderset1_.pre_authorization_detail_key, ordersetda2_.orderset_date_key, ordersetda2_.date_from, orderset3_.orderset_key, orderset3_.code, orderset3_.order_set_description, orderset3_.pre_authorization_detail_key from ccra.preauthorization_detail preauthori0_ left outer join ccra.orderset orderset1_ on preauthori0_.preauthorization_detail_key=orderset1_.pre_authorization_detail_key left outer join ccra.ordersetdate ordersetda2_ on orderset1_.orderset_key=ordersetda2_.orderset_date_key left outer join ccra.orderset orderset3_ on ordersetda2_.orderset_date_key=orderset3_.orderset_key where preauthori0_.preauthorization_key=?
SELECT from ccra.ordersetdateitem, repeated: 1 times
select ordersetda0_.orderset_date_key, ordersetda0_.orderset_date_item_key, ordersetda0_.orderset_date_item_key, ordersetda0_.orderset_date_key, ordersetda0_.procedure_code from ccra.ordersetdateitem ordersetda0_ where ordersetda0_.orderset_date_key=?
SELECT from seoc, repeated: 1 times
select seoc0_.seoc_key, seoc0_.category_of_care, seoc0_.duration, seoc0_.end_date, seoc0_.is_pal, seoc0_.name, seoc0_.seoc_id, seoc0_.start_date, seoc0_.version from seoc seoc0_ where seoc0_.seoc_id=?
SELECT from seoc_procedure_junction, repeated: 1 times
select procedures0_.seoc_key, procedures0_.procedure_key, procedures0_.seoc_key, procedures0_.procedure_key, procedure1_.procedure_key, procedure1_.procedure_code, procedure1_.short_description from seoc_procedure_junction procedures0_ inner join dim_procedure_code procedure1_ on procedures0_.procedure_key=procedure1_.procedure_key where procedures0_.seoc_key=? java.util.concurrent.CompletionException: java.lang.IllegalStateException: procedure not valid for pharmacy claim type at org.springframework.aop.interceptor.AsyncExecutionAspectSupport.lambda$doSubmit$0(AsyncExecutionAspectSupport.java:273) at java.util.concurrent.CompletableFuture$AsyncSupply.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.IllegalStateException: procedure not valid for pharmacy claim type at gov.va.ccrs.brengine.claim.PharmacyClaimLine.procedure(PharmacyClaimLine.java:68) at gov.va.ccrs.brengine.rule.seoc.EpisodeRules.validate(EpisodeRules.java:118) at gov.va.ccrs.brengine.rule.seoc.EpisodeRules.validateReferral(EpisodeRules.java:101) at gov.va.ccrs.brengine.rule.seoc.EpisodeRules.process(EpisodeRules.java:54) at executeRules(RulesClaimExecutor.java:100) at executeRulesAndPresistDecisions(RulesClaimExecutor.java:64) at executeRulesForClaimQueue(RulesClaimExecutor.java:48) at gov.va.ccrs.brengine.rule.RulesClaimExecutor$$FastClassBySpringCGLIB$$d090f078.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115) at org.springframework.aop.interceptor.AsyncExecutionAspectSupport.lambda$doSubmit$0(AsyncExecutionAspectSupport.java:270) ... 2 common frames omitted