API tools faq
paste
ryanarnold

hv_labgen_order_details_v dll

ryanarnold
Jul 12th, 2019
153
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 13.54 KB | None | 0 0
raw download clone embed print report
  1.   CREATE OR REPLACE EDITIONABLE VIEW "HV_LABGEN_ORDER_DETAILS_V" ("SOURCE_CLINIC", "ORDER_RECNUM", "SALES_GROUP", "NPI_LONGITUDE", "NPI_LATITUDE", "CLINIC_NAME", "CLINIC_RECNUM", "NPI", "PHYSICIAN_NAME", "PHYSICIAN_RECNUM", "INSURANCE_RECNUM", "INSURANCE_TYPE", "INSURANCE_CARRIER", "INSURANCE_RECNUM2", "INSURANCE_TYPE2", "INSURANCE_CARRIER2", "PATIENT_CHART", "POLICY_NUMBER", "GROUP_NUMBER", "POLICY_NUMBER2", "GROUP_NUMBER2", "PATIENT_RECNUM", "PATIENT_NAME", "PATIENT_LAST_NAME_NEW", "PATIENT_FIRST_NAME_NEW", "PATIENT_SSNUM", "PATIENT_MIDDLE_NAME", "PATIENT_ADDRESS1", "PATIENT_ADDRESS2", "PATIENT_CITY", "PATIENT_STATE", "PATIENT_ZIP", "PATIENT_PHONE", "GAURANTOR_LAST_NAME", "GAURANTOR_MIDDLE_NAME", "GAURANTOR_FIRST_NAME", "GAURANTOR_ADDRESS", "GAURANTOR_CITY", "GAURANTOR_STATE", "GAURANTOR_ZIP", "GAURANTOR_DOB", "GAURANTOR_SEX", "GAURANTOR_PHONE", "GAURANTOR_SSNUM", "GAURANTOR_RELATIONSHIP", "GUARANTOR_LAST_NAME2", "GUARANTOR_MIDDLE2", "GUARANTOR_FIRST_NAME2", "GUARANTOR_SSNUM2", "GUARANTOR_SEX2", "GUARANTOR_PHONE2", "GUARANTOR_DOB2", "GAURANTOR_RELATIONSHIP2", "ACCESSION", "ORDER_DATE", "ORDER_DATE_TIME", "REPORT_DATE", "PENDING", "PRIMARY_CLAIM_ID", "FINAL_DATE", "FINAL_DATE_TIME", "COLLECTED_DATE", "COLLECTED_DATE_NEW", "COLLECTED_DATE_TIME", "COLLECTED_DATE_TIME_NEW", "RECEIVED_DATE", "RECEIVED_DATE_TIME", "ORDER_ENTERED_BY", "ACCESSION_W_PREFIX", "ADJUDICATION_DATE", "FIRST_CLAIM_CREATION_DATE", "INSURANCE_PAY_TYPE", "PATIENT_DOB", "PATIENT_DOB_NEW", "PATIENT_SEX", "TEST_TYPE_CODE", "TEST_TYPE_NAME", "COMMENTS", "NUM_TESTS", "NUM_CPTS", "FEES", "AVG_TEST_DAY", "NUMBER_OF_TNP_TESTS", "INTERNAL_COST", "HOLD_STATUS", "HOLD_START_DATE", "HOLD_START_DATE_TIME", "HOLD_END_DATE", "HOLD_END_DATE_TIME", "FLAG_STATUS", "FLAG_START_DATE", "FLAG_START_DATE_TIME", "FLAG_END_DATE", "FLAG_END_DATE_TIME", "ENTERED_DATE", "FINAL_COST", "TRAVEL_CHARGES", "SKILLED_NONSKILLED_DESIG", "NUMBER_OF_ATTACHMENTS", "COSTED_CODES") AS
  2.   SELECT o.source_clinic                                 AS source_clinic,
  3.        o.recnum                                        AS order_recnum,
  4.        ISNULL(p.npi_sales_group,'QMP9')                   AS sales_group,
  5.        p.longitude                                     AS npi_longitude,
  6.        p.latitude                                      AS npi_latitude,
  7.        c.name                                          AS clinic_name,
  8.        o.client_recnum                                 AS clinic_recnum,
  9.        p1.npi                                          AS npi,
  10.        p1.name                                         AS physician_name,
  11.        o.physician_recnum                              AS physician_recnum,
  12.        o.billtype                                      AS insurance_recnum,
  13.        CASE i.testcode
  14.                WHEN 8 THEN     'Medicare'
  15.                WHEN 9 THEN     'Medicaid'
  16.                      ELSE 'Other' END                           AS insurance_type,
  17.        i.name                                          AS insurance_carrier,
  18.        i2.recnum                                       AS insurance_recnum2,
  19.        CASE i2.testcode
  20.                WHEN 8 THEN     'Medicare'
  21.                WHEN 9 THEN     'Medicaid'
  22.                      ELSE 'Other' END                           AS insurance_type2,
  23.        i2.name                                         AS insurance_carrier2,
  24.        pt.chart                                        AS patient_chart,
  25.        pt.insid1_new                                   AS policy_number,
  26.        pt.group1_new                                   AS group_number,
  27.        pt.insid2                                       AS policy_number2,
  28.        pt.group2                                       AS group_number2,
  29.        pt.recnum                                       AS patient_recnum,
  30.        isnull(pt.lastn, '')+', '+isnull(pt.firstn, '')                       AS patient_name,
  31.        pt.lastn_new                                    AS patient_last_name_new,
  32.        pt.firstn_new                                   AS patient_first_name_new,
  33.        pt.ssnum                                        AS patient_ssnum,
  34.        pt.middle                                       AS patient_middle_name,
  35.        pt.address1_new                                 AS patient_address1,
  36.        pt.address2_new                                 AS patient_address2,
  37.        pt.city_new                                     AS patient_city,
  38.        pt.state_new                                    AS patient_state,
  39.        pt.zip_new                                      AS patient_zip,
  40.        pt.phone_new                                    AS patient_phone,
  41.        pt.ilname_new                                   AS gaurantor_last_name,
  42.        pt.imiddle                                      AS gaurantor_middle_name,
  43.        pt.ifname_new                                   AS gaurantor_first_name,
  44.        pt.iaddr1_new                                   AS gaurantor_address,
  45.        pt.iaddr2_new                                   AS gaurantor_city,
  46.        pt.istate_new                                   AS gaurantor_state,
  47.        pt.izip_new                                     AS gaurantor_zip,
  48.        pt.idob                                         AS gaurantor_dob,
  49.        pt.isex                                         AS gaurantor_sex,
  50.        pt.iphone                                       AS gaurantor_phone,
  51.        pt.issnum                                       AS gaurantor_ssnum,
  52.        CASE
  53.                WHEN pt.relation = 'SE' THEN  'Self'
  54.                WHEN pt.relation = 'SP' THEN  'Spouse'
  55.                WHEN pt.relation = 'CH' THEN  'Child'
  56.                WHEN pt.relation = 'PA' THEN  'Parent'
  57.                WHEN pt.relation = 'OT' THEN  'Other'
  58.                WHEN pt.relation IS NULL THEN  NULL
  59.                      ELSE 'Unknown' END                         AS gaurantor_relationship,
  60.        pt.secondary_last_name                          AS guarantor_last_name2,
  61.        pt.secondary_middle                             AS guarantor_middle2,
  62.        pt.secondary_first_name                         AS guarantor_first_name2,
  63.        pt.secondary_ssnum                              AS guarantor_ssnum2,
  64.        pt.secondary_sex                                AS guarantor_sex2,
  65.        pt.secondary_phone                              AS guarantor_phone2,
  66.        pt.secondary_dob                                AS guarantor_dob2,
  67.        CASE
  68.                WHEN pt.irelation = 'SE' THEN  'Self'
  69.                WHEN pt.irelation = 'SP' THEN  'Spouse'
  70.                WHEN pt.irelation = 'CH' THEN  'Child'
  71.                WHEN pt.irelation = 'PA' THEN  'Parent'
  72.                WHEN pt.irelation = 'OT' THEN  'Other'
  73.                WHEN pt.irelation IS NULL THEN  NULL
  74.                      ELSE 'Unknown' END                         AS gaurantor_relationship2,
  75.        o.accession                                     AS accession,
  76.        o.entrydt                                       AS order_date,
  77.        o.order_date_time                               AS order_date_time,
  78.        o.repdate                                       AS report_date,
  79.        o.pending                                       AS pending,
  80.        o.primary_claim_id                              AS primary_claim_id,
  81.        d.finaldate                                     AS final_date,
  82.        d.final_date_time                               AS final_date_time,
  83.        o.collectdt                                     AS collected_date,
  84.        o.collectdt_new                                 AS collected_date_new,
  85.        o.collected_date_time                           AS collected_date_time,
  86.        o.collected_date_time_new                       AS collected_date_time_new,
  87.        TRUNC(o.received_date_time)                     AS received_date,
  88.        o.received_date_time                            AS received_date_time,
  89.        o.order_entered_by                              AS order_entered_by,
  90.        o.accession_w_prefix                            AS accession_w_prefix,
  91.        o.adjudication_date                             AS adjudication_date,
  92.        o.first_claim_creation_date                     AS first_claim_creation_date,
  93.        o.insurance_pay_type                            AS insurance_pay_type,
  94.        pt.dob                                          AS patient_dob,
  95.        pt.dob_new                                      AS patient_dob_new,
  96.        pt.sex                                          AS patient_sex,
  97.        o.bodysite                                      AS test_type_code,
  98.        o.test_type_name                                AS test_type_name,
  99.        isnull(o.comment1, '')+ISNULL(CASE  WHEN o.comment1 IS NULL THEN NULL ELSE ' ' END, '')
  100.                                          +isnull(o.comment2, '')  AS comments,
  101.        a.number_of_tests                               AS num_tests,
  102.        a.number_of_cpts                                AS num_cpts,
  103.        a.sg_cost                                       AS fees,
  104.        a.avg_test_compl                                AS avg_test_day,
  105.        a.number_of_tnp                                 AS number_of_tnp_tests,
  106.        a.cogs                                          AS internal_cost,
  107.        o.acc_hold                                      AS hold_status,
  108.        o.acc_hold_start                                AS hold_start_date,
  109.        o.acc_hold_start_time                           AS hold_start_date_time,
  110.        o.acc_hold_end                                  AS hold_end_date,
  111.        o.acc_hold_end_time                             AS hold_end_date_time,
  112.        o.acc_flag                                      AS flag_status,
  113.        o.acc_flag_start                                AS flag_start_date,
  114.        o.acc_flag_start_time                           AS flag_start_date_time,
  115.        o.acc_flag_end                                  AS flag_end_date,
  116.        o.acc_flag_end_time                             AS flag_end_date_time,
  117.        o.entered_date                                  AS entered_date,
  118.        o.final_cost                                    AS final_cost,
  119.        o.travel_charges                                AS travel_charges,
  120.        o.skilled_nonskilled_desig                      AS skilled_nonskilled_desig,
  121.        o.number_of_attachments                         AS number_of_attachments,
  122.        cost.costed_codes                               AS costed_codes
  123. FROM   hv_labgen_orders                o,
  124.        hv_labgen_physicians_npi_v      p,
  125.        hv_labgen_clients               c,
  126.        hv_labgen_patients              pt,
  127.        hv_labgen_insurance             i,
  128.        hv_labgen_insurance             i2,
  129.        hv_labgen_report_dates          d,
  130.        hv_labgen_clients               p1,
  131.        hv_labgen_accession_costs       a,
  132.        hv_labgen_acc_costed_codes_v    cost
  133. WHERE  p.recnum                (+)  = o.physician_recnum * 1
  134. AND    p.source_clinic         (+)  = isnull(o.source_clinic, '')+''
  135. AND    p.from_date             (+) <= o.entrydt + 1 - 1
  136. AND    p.to_date               (+) >= o.entrydt + 1 - 1
  137. AND    c.recnum                (+)  = o.client_recnum * 1
  138. AND    c.source_clinic         (+)  = isnull(o.source_clinic, '')+''
  139. AND    pt.recnum               (+)  = o.patient_recnum * 1
  140. AND    pt.source_clinic        (+)  = isnull(o.source_clinic, '')+''
  141. AND    i.recnum                (+)  = isnull(o.billtype, '')+''
  142. AND    i.source_clinic         (+)  = isnull(o.source_clinic, '')+''
  143. AND    i2.recnum               (+)  = isnull(pt.ins2, '')+''
  144. AND    i2.source_clinic        (+)  = isnull(pt.source_clinic, '')+''
  145. AND    d.accession             (+)  = isnull(o.accession, '')+''
  146. AND    d.source_clinic         (+)  = isnull(o.source_clinic, '')+''
  147. AND    p1.recnum               (+)  = o.physician_recnum * 1
  148. AND    p1.source_clinic        (+)  = isnull(o.source_clinic, '')+''
  149. AND    a.recnum                (+)  = o.recnum * 1
  150. AND    a.source_clinic         (+)  = isnull(o.source_clinic, '')+''
  151. AND    cost.accession          (+)  = isnull(o.accession, '')+''
  152. AND    cost.source_clinic      (+)  = isnull(o.source_clinic, '')+''
  153. --
  154. --  Don't extract Test Physicians, Patients and Clinics and 0 test accessions
  155. --
  156. AND    EXISTS
  157.        (SELECT 'X'
  158.         FROM   hv_labgen_ordered_tests ot
  159.         WHERE  ot.source_clinic = o.source_clinic
  160.         AND    ot.order_recnum  = o.recnum
  161.         AND   )
  162. AND    (o.source_clinic NOT IN ('Axis', 'Clearview', 'Precision')
  163.         OR
  164.         (o.source_clinic = 'Axis'
  165.          AND
  166.          ISNULL(o.patient_recnum,0) NOT IN (-10))
  167.         OR
  168.         (o.source_clinic = 'Clearview'
  169.          AND
  170.          isnull(get_dec_val(pt.lastn_new), '')+isnull(get_dec_val(pt.firstn_new), '') NOT IN ('MOUSEMINNIE', 'TESTTEST', 'TESTFAX 1384')
  171.          AND
  172.          ISNULL(o.patient_recnum,0) NOT IN (12, 15, 29, 306, 437, 3480, 14941, 18758, 21220, 21648, 22223, 24558, 24559, 24560, 24561, 30745,
  173.                                          24562, 25315, 26176, 26218, 26939, 27598, 28239, 28240, 28774, 30083, 15053, 28830, 29579))
  174.         OR
  175.         (o.source_clinic = 'Precision'
  176.          AND
  177.          ISNULL(o.patient_recnum,0) NOT IN (6, 28, 29, 30, 31, 32, 25893)))
  178. AND    ((o.source_clinic <> 'Precision'
  179.          AND
  180.          ISNULL(o.physician_recnum,5)    NOT IN (1, 2, 743)
  181.          AND
  182.          ISNULL(o.client_recnum,5)         >= 3
  183.          AND
  184.          ISNULL(o.client_recnum,5)       NOT IN (743))
  185.         OR
  186.          (o.source_clinic = 'Precision'
  187.           AND
  188.           ISNULL(o.physician_recnum,5)    NOT IN (1)
  189.           AND
  190.           ISNULL(o.client_recnum,5)       NOT IN (1)));
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!