Back to Home

Data Load

Currently there is no automated job in place so the manual load is required monthly.

Steps

1. Open New Project with Visual Studio, Right Click SSIS Packages and Add Existion Package.
2. Add Package path for ReviewTool_LoadSample.dtsx.
3. Open the SSIS Package file ReviewTool_LoadSample.dtsx and Go to Data Flow
4. Double Click Review Tool. Choose Connection Manager
5. In SQL command text, you can change the Paid_Date for Report Months and Adjucated_Date for Month Adjustments.
6. Press Start to execute the package

with MyAdjustedClaim as (

       select distinct claim_ID, [Audit_Date],Audit_Stream,Auditor_ID,Adjudication_Type,Audit_Type,paid_amount as Audit_Paid_Amount, Paid_Date,Processor_ID,

       case when platform_code = 'Pro' then 'Proclaim' when platform_code = 'Power' then 'PMHS' when platform_code = 'Facets' then 'Facets'
                               when platform_code = 'DentaCom' then 'Dentacom' else null end as [System]

       from dbo.SAM3_Detail

       where Paid_Date BETWEEN  '9/1/2020' AND '11/30/2020' and claim_error_type = 'No Error'

              and  claim_ID not in (select claim_ID from dbo.SAM3_Detail 

                                                          where error_code is not NULL AND Paid_Date BETWEEN   '9/1/2020' AND '11/30/2020' ))

select distinct c.Claim_ID as ClaimID,c.Audit_Date as AuditDate,c.Audit_Stream as SampleType,c.Auditor_ID as NTID, c.Adjudication_Type as AdjudicationType,

       c.Audit_Paid_Amount as PaidAmount, a.paid_amount as AdjustredPaidAmount,c.paid_date as PaidDate,c.[System] as [Platform],a.Adjudicated_Date,

       a.Reason_code as ReasonCode,b.Reason_category as ReasonCategory ,b.reason_description as ReasonDescription

from MyAdjustedClaim c

       inner join dbo.Adjustment_Detail a

              on (a.claim_number = c.claim_ID and a.[system] = c.[system] and a.Adjudicated_Date > c.audit_date)

       left join [SOBI_PERF_PROF].[dbo].[Adjustment_Reason_Code_Dimension] b on a.reason_code = b.reason_code

where a.Adjudicated_Date between '12/1/2020' and '12/31/2020'