Votre template de données de gestion du cycle de revenus
Votre template de données de gestion du cycle de revenus
- Attributs recommandés à collecter
- Activités clés à suivre
- Guide d'extraction
Attributs de gestion du cycle de revenus
| Nom | Description | ||
|---|---|---|---|
| Événement de Facturation BillingEvent | L'identifiant unique pour une seule prestation de service ou livraison de produit qui génère une charge, servant d'identifiant principal de `case`. | ||
| Description L' Dans l'analyse par Pourquoi c'est important C'est l' Où obtenir Il s'agit souvent d'un identifiant unique pour un compte hospitalier (HAR) ou une session de facturation spécifique dans Epic Resolute. Consultez la documentation d'Epic Resolute pour des tables spécifiques comme HAR ou les enregistrements de session de facturation. Exemples BE10098765BE20012345BE30054321 | |||
| Nom de l'activité ActivityName | Le nom de l'`événement` ou de la tâche spécifique effectuée dans le processus de gestion du cycle de revenus. | ||
| Description Cet L'analyse des activités est le fondement du Pourquoi c'est important Définit les étapes de la carte des processus, rendant possible la visualisation, l'analyse et l'optimisation du flux de travail dans le cycle de revenus. Où obtenir Ceci est généralement dérivé des Exemples Frais SaisisDemande de remboursement soumise au payeurPaiement ReçuCompte Clos | |||
| Timestamp de l'événement EventTimestamp | La date et l'heure précises auxquelles une activité ou un événement spécifique s'est produit. | ||
| Description L' En analyse, les Pourquoi c'est important Cet Où obtenir Trouvé dans les tables de journal de transactions ou d'événements au sein d'Epic Resolute, associé à chaque activité enregistrée. Les champs sont souvent nommés avec des suffixes comme Dt, DTTM, ou Time. Exemples 2023-04-15T09:30:00Z2023-04-16T11:05:21Z2023-05-01T14:00:00Z | |||
| Code de Motif de Refus DenialReasonCode | Un code standardisé indiquant la raison pour laquelle un payeur a refusé une demande de remboursement soumise. | ||
| Description Lorsqu'un payeur rejette une réclamation, il fournit un code de motif qui explique le refus, tel que 'Service non couvert', 'Réclamation en double' ou 'Nécessite des informations supplémentaires'. Ces codes sont souvent normalisés comme Cet Pourquoi c'est important Explique directement pourquoi les demandes de remboursement sont rejetées, fournissant les insights exploitables nécessaires pour réduire les taux de refus, prévenir les pertes de revenus et accélérer les paiements. Où obtenir Ces Exemples CO-16 : La demande de remboursement/le service manque d'informationsOA-18 : Demande de remboursement/service en doublePR-96 : Frais non couverts | |||
| Motif de l'ajustement AdjustmentReason | La raison d'un ajustement manuel ou automatisé apporté au solde du compte d'un patient. | ||
| Description Cet Ceci est essentiel pour le Pourquoi c'est important Fournit un aperçu des pertes de revenus et de la précision de la facturation en expliquant pourquoi les soldes des comptes sont modifiés, aidant ainsi à réduire les radiations inutiles. Où obtenir Situé dans les détails de transaction pour les entrées d'ajustement au sein du module de comptabilité patient d'Epic Resolute. Exemples Marge contractuelleRadiation de petit soldeCorrection de frais en double | |||
| Service de facturation BillingDepartment | Le service ou l'équipe fonctionnelle responsable de l'`événement` de facturation ou de l'activité. | ||
| Description Cet Cette dimension est cruciale pour le Pourquoi c'est important Permet l'analyse comparative des performances entre différents départements, aidant à identifier les meilleures pratiques et les domaines nécessitant une amélioration ou des ressources supplémentaires. Où obtenir Ces informations peuvent être liées à l'enregistrement de l'utilisateur, au compte patient ou à l'emplacement du service au sein d'Epic Resolute. Exemples Facturation de cardiologieRadiologie RCMBureau central de facturation | |||
| Solde Impayé OutstandingBalance | Le montant restant dû par le payeur ou le patient pour l'`événement` de facturation. | ||
| Description Cet Le solde impayé est crucial pour les rapports financiers et pour le 'Rapport de vieillissement des soldes impayés'. L'analyse de cette valeur au fil du temps et selon différentes dimensions comme le payeur ou le service aide à prioriser les efforts de recouvrement, à gérer les flux de trésorerie et à évaluer les risques financiers. Pourquoi c'est important Mesure directement l'impact financier des retards de processus et est essentiel pour prioriser les recouvrements, gérer la trésorerie et comprendre les créances clients. Où obtenir Il s'agit d'un champ central sur l'enregistrement du compte patient ou du compte hospitalier (HAR) dans Epic Resolute. C'est un solde courant qui est mis à jour par les transactions financières. Exemples 1500.00250.750.00 | |||
| Type de service ServiceType | La catégorie ou le type de service médical qui a été rendu. | ||
| Description Cet L'analyse du cycle de revenus par type de service peut découvrir des variations de processus spécifiques à certaines zones cliniques. Par exemple, les procédures chirurgicales peuvent avoir des exigences de saisie des frais et d'autorisation plus complexes qu'une consultation de bureau standard, entraînant différents comportements et défis de processus. Pourquoi c'est important Fournit un contexte clinique aux Où obtenir Dérivé du fichier maître de description des frais (CDM), de la ligne de service ou du département associé à la transaction de frais dans Epic. Exemples Chirurgie hospitalièreRadiologie ambulatoireServices d'urgence | |||
| Utilisateur responsable ResponsibleUser | L'identifiant de l'utilisateur ou de l'employé qui a effectué l'activité. | ||
| Description Cet L'analyse par utilisateur aide à identifier les meilleurs performeurs, à cerner les besoins en formation et à comprendre la répartition de la charge de travail. Elle est clé pour la gestion des performances et pour l'investigation des déviations de processus associées à des individus ou des rôles spécifiques. Pourquoi c'est important Permet l'analyse des performances par individu ou par rôle, aidant à identifier les opportunités de formation, les déséquilibres de charge de travail et les goulots d'étranglement liés aux ressources. Où obtenir Généralement trouvé dans les pistes d'audit ou les Exemples j.doebsmith123User7890 | |||
| Date d'échéance du paiement PaymentDueDate | La date à laquelle le paiement pour le service facturé est attendu. | ||
| Description Cet La date d'échéance du paiement est essentielle pour la création du 'Rapport de vieillissement des soldes impayés'. En comparant la date actuelle à la date d'échéance des soldes ouverts, les comptes clients peuvent être classés en catégories de vieillissement ( Pourquoi c'est important Sert de base à l'analyse du vieillissement des comptes clients, ce qui est crucial pour prioriser les recouvrements et gérer les risques financiers liés aux factures impayées. Où obtenir Cette date est souvent calculée en fonction de la date de la facture et des conditions de paiement stockées dans le contrat du payeur ou les informations du compte patient dans Epic. Exemples 2023-05-302023-06-152023-07-01 | |||
| Dernière mise à jour des données LastDataUpdate | L'`horodatage` indiquant la dernière `actualisation` ou `extraction` des `données` de cet `événement` depuis le `système source`. | ||
| Description Cet Ceci est important pour comprendre la pertinence de l'analyse et à des fins de validation des Pourquoi c'est important S'assure que les utilisateurs comprennent l'actualité des données qu'ils analysent, ce qui est essentiel pour prendre des décisions commerciales précises et à jour. Où obtenir Ce Exemples 2023-06-10T02:00:00Z2023-06-11T02:00:00Z | |||
| Est Automatisé IsAutomated | Un indicateur booléen signalant si l'activité a été réalisée par un système ou un processus automatisé. | ||
| Description Ce drapeau distingue les tâches exécutées automatiquement par le système, telles que la génération automatisée de réclamations ou les vérifications d'éligibilité, et celles effectuées manuellement par un utilisateur. L'analyse de cet Pourquoi c'est important Distingue les activités pilotées par le système des activités pilotées par l'humain, ce qui est essentiel pour évaluer l'impact de l'automatisation et identifier de nouvelles opportunités d'automatisation. Où obtenir Ceci est souvent dérivé en vérifiant si l'utilisateur responsable ( Exemples truefaux | |||
| Heure de fin de l'événement EventEndTime | Le `timestamp` indiquant quand une activité a été complétée, utile pour calculer la durée de l'activité. | ||
| Description Cet Lorsque disponible, Pourquoi c'est important Permet le calcul précis de la durée de chaque activité, ce qui est crucial pour identifier les tâches chronophages et mesurer la productivité des ressources. Où obtenir Ceci peut être disponible dans certains modules Epic Resolute qui suivent le début et la fin des tâches, tels que les Exemples 2023-04-15T09:45:00Z2023-04-16T11:15:30Z2023-05-01T14:02:00Z | |||
| ID patient PatientId | L'identifiant unique du patient recevant le service. | ||
| Description Cet Bien que non généralement utilisé comme dimension d'analyse principale pour protéger la vie privée des patients, il est essentiel pour la validation des Pourquoi c'est important Lie les données financières à un patient spécifique, permettant la validation des données et le potentiel d'une analyse plus large du parcours complet d'un patient, bien qu'il doive être traité avec prudence en raison des préoccupations de confidentialité. Où obtenir Un identifiant fondamental trouvé dans Epic, lié aux dossiers d'enregistrement et de compte du patient. Exemples MRN-1234567MRN-8765432MRN-5551234 | |||
| Montant Ajusté AdjustedAmount | La valeur monétaire d'une transaction d'ajustement. | ||
| Description Ce champ enregistre le montant spécifique en dollars d'un ajustement de compte. Il peut être une valeur positive ou négative, représentant un crédit ou un débit au solde du compte. Ce montant est la principale métrique pour le Pourquoi c'est important Quantifie l'impact financier des ajustements de compte, rendant possible la mesure des pertes de revenus et du coût des inexactitudes de facturation. Où obtenir Situé dans les tables de détails des transactions financières d'Epic Resolute, associé aux transactions de type ajustement. Exemples -1250.45-50.0025.10 | |||
| Nom du payeur PayerName | Le nom de la compagnie d'assurance, de l'entité gouvernementale ou de toute autre partie responsable du paiement. | ||
| Description Cet Segmenter le processus par payeur est une technique d'analyse puissante. Elle peut révéler que certains payeurs ont des taux de refus plus élevés, des cycles de paiement plus longs ou des exigences plus complexes. Cet aperçu permet d'adapter les stratégies de facturation à des payeurs spécifiques pour améliorer l'efficacité et la rapidité des paiements. Pourquoi c'est important Permet l'analyse des performances par payeur, révélant quels payeurs ont des taux de refus élevés ou des cycles de paiement lents, permettant ainsi des stratégies de suivi ciblées. Où obtenir Ces informations font partie des détails de la couverture du patient, liées au compte hospitalier (HAR) dans Epic Resolute. Exemples Medicare partie BUnitedHealthcareAetna PPO | |||
| Numéro de Sinistre ClaimId | L'identifiant unique attribué à une réclamation d'assurance soumise à un payeur. | ||
| Description Cet Le suivi par Pourquoi c'est important Fournit un identifiant granulaire pour le suivi du cycle de vie de chaque soumission de réclamation spécifique, ce qui est crucial pour l'analyse des nouvelles soumissions et des appels. Où obtenir Généré par le module de gestion des demandes de remboursement d'Epic Resolute lorsqu'une demande de remboursement est créée. Il est stocké dans les tables de données des demandes de remboursement. Exemples CLM-2023-98765CLAIM-0012345623189A4567 | |||
| Système source SourceSystem | Le système d'information d'où proviennent les `données`. | ||
| Description Cet Bien qu'il puisse sembler redondant dans une vue mono-système, c'est une bonne pratique pour la gouvernance des Pourquoi c'est important Fournit une traçabilité et un contexte cruciaux pour les Où obtenir Il s'agit généralement d'une valeur statique ajoutée pendant le processus d'extraction et de transformation des données pour étiqueter l'origine du jeu de données. Exemples Epic ResoluteEpicResolute_V2023 | |||
| Temps de traitement ProcessingTime | La durée calculée du temps passé à travailler activement sur une activité. | ||
| Description Le temps de traitement, également appelé temps d'exécution, mesure la durée d'une activité de son début à sa fin. Il représente le temps pendant lequel une ressource a été effectivement dédiée à la tâche. Cette métrique est calculée comme la différence entre 'EventEndTime' et 'EventTimestamp'. L'analyse du temps de traitement permet d'identifier les tâches spécifiques les plus chronophages, ce qui permet de concentrer les efforts sur la rationalisation et l'amélioration de l'efficacité. C'est une mesure fondamentale de la productivité des ressources. Pourquoi c'est important Mesure la durée réelle du travail des activités, aidant à identifier les tâches chronophages et à évaluer l'efficacité des ressources. Où obtenir Ce n'est pas un champ dans le système source. Il est calculé lors de la transformation des Exemples 900605120 | |||
| Temps total du cycle de revenus TotalRevenueCycleTime | La durée totale calculée entre le premier `événement` de service et le paiement final ou la clôture du compte. | ||
| Description Il s'agit d'un Cette métrique de haut niveau offre une vue holistique de l'efficacité globale du processus RCM. Le suivi de ce Pourquoi c'est important Fournit une vue d'ensemble, de bout en bout, de l'efficacité des processus, mesurant directement le temps nécessaire pour convertir un service en trésorerie. Où obtenir Il s'agit d'une métrique calculée dans l'outil de Exemples 259200038880005184000 | |||
Activités de gestion du cycle de revenus
| Activité | Description | ||
|---|---|---|---|
| Compte Clos | Il s'agit de l'activité finale, signifiant que le solde impayé de l'`événement` de facturation a atteint zéro et qu'il n'y a plus d'activités en attente. Cela peut être dû à un paiement intégral, des ajustements ou une radiation. | ||
| Pourquoi c'est important Cet Où obtenir Il s'agit généralement d'un Capture Déduit en calculant un total cumulé du solde du compte et en identifiant l'horodatage de la dernière transaction qui a ramené le solde à zéro. Type d'événement inferred | |||
| Demande de remboursement refusée par le payeur | Représente la réception d'une notification du payeur indiquant que la réclamation a été refusée. Cela est capturé lorsque Epic traite un avis de virement électronique (fichier 835) ou lorsqu'un utilisateur enregistre manuellement un refus. | ||
| Pourquoi c'est important Cette activité déclenche une boucle de retravail critique. L'analyse des raisons et des volumes de refus est essentielle pour identifier les causes profondes, améliorer les taux de paiement dès la première soumission et réduire les retards de recouvrement. Où obtenir Explicitement enregistré comme une transaction ou une mise à jour de statut sur la demande de remboursement. Les informations de refus, y compris les codes de motif, sont généralement reçues électroniquement et imputées au compte. Capture Filtrer par types de transaction spécifiques ou mises à jour du statut des demandes de remboursement qui indiquent un refus. Type d'événement explicit | |||
| Demande de remboursement soumise au payeur | Ceci marque l'`événement` où la réclamation est officiellement envoyée au payeur d'assurance pour adjudication. Dans Epic, il s'agit d'un `événement` suivi, enregistré lorsque le fichier de réclamation électronique est transmis à la chambre de compensation ou au payeur. | ||
| Pourquoi c'est important Ce jalon est crucial car il lance le décompte du délai de paiement du payeur. L'analyse de ceci aide à mesurer l'efficacité du processus de transmission des réclamations et soutient le Où obtenir Il s'agit d'un Capture Capturer l'horodatage associé au changement de statut de la demande de remboursement à 'Soumise' ou 'Transmise'. Type d'événement explicit | |||
| Frais Saisis | Représente l'enregistrement formel des frais facturables pour les services rendus. Dans Epic, il s'agit généralement d'une transaction explicite enregistrée sur le compte du patient, souvent générée automatiquement à partir d'actions cliniques ou saisie manuellement. | ||
| Pourquoi c'est important Il s'agit d'un premier jalon critique. Mesurer la vitesse et la précision de la saisie des frais aide à accélérer le processus de facturation et à assurer que tous les services fournis sont facturés. Où obtenir Explicitement enregistré dans les journaux de transactions de Resolute. Chaque frais est une entrée distincte avec une date d'imputation, une date de service et un montant, souvent trouvée dans des tables comme ARPB_TRANSACTIONS. Capture Capturer les transactions d'imputation des frais à partir du journal des transactions financières du système. Type d'événement explicit | |||
| Paiement imputé au compte | C'est l'`événement` où un paiement reçu est appliqué ou alloué à des frais spécifiques sur le compte du patient. Cette action réduit le solde impayé de l'`événement` de facturation. | ||
| Pourquoi c'est important Une imputation efficace des paiements est cruciale pour maintenir des soldes de compte précis et clôturer les événements de facturation. Elle permet l'identification correcte des soldes restants pour la facturation secondaire ou le recouvrement. Où obtenir Il s'agit d'une transaction explicite dans Resolute. L'enregistrement du paiement lie une transaction de paiement à une ou plusieurs transactions de frais, ce qui est enregistré dans les tables de détails de transaction. Capture Capturer l'enregistrement de transaction qui applique un paiement à une charge, identifiable par des types de transaction spécifiques. Type d'événement explicit | |||
| Paiement Reçu | Représente la réception du paiement d'un payeur ou d'un patient. Cet `événement` est généralement enregistré lorsqu'un avis de virement électronique (ERA) est chargé ou qu'un chèque manuel est saisi dans le système. | ||
| Pourquoi c'est important Cette activité est un jalon majeur indiquant l'arrivée de revenus. Le temps entre la soumission de la réclamation et la réception du paiement est une mesure clé de la performance des comptes clients. Où obtenir Explicitement enregistré comme une transaction de paiement dans Resolute. Ces transactions sont journalisées avec une date, une source et un montant, souvent avant d'être entièrement imputées aux frais individuels. Capture Capturer les transactions de paiement à partir du journal des transactions financières, souvent identifiées par des types de transaction spécifiques. Type d'événement explicit | |||
| Service Rendu | Cette activité marque le moment où un service clinique est fourni au patient, ce qui initie l'`événement` de facturation. Ceci est souvent capturé à partir du DME Epic (EpicCare) lorsqu'un clinicien valide une visite ou une procédure. | ||
| Pourquoi c'est important Il s'agit de l' Où obtenir Cet Capture Déduit de la date de service associée à la première transaction de frais pour l'événement de facturation. Type d'événement inferred | |||
| Ajustement de compte effectué | Cette activité représente une transaction de non-paiement qui modifie le solde du compte, comme un ajustement contractuel, une radiation de petit solde ou une remise commerciale. Elle est enregistrée comme un type de transaction spécifique. | ||
| Pourquoi c'est important L'analyse des ajustements est essentielle pour identifier les fuites de revenus. Des volumes élevés de certains types d'ajustements peuvent indiquer des problèmes avec les barèmes de frais, les contrats ou les politiques internes. Où obtenir Explicitement enregistré comme transactions d'ajustement dans les journaux financiers de Resolute. Chaque ajustement aura un type ou un code de motif spécifique associé. Capture Filtrer par types de transaction qui correspondent à des ajustements financiers ou à des radiations. Type d'événement explicit | |||
| Demande de remboursement générée | Cette activité signifie la création par le système d'une réclamation ou d'une facture formelle basée sur les frais saisis. C'est une étape préparatoire avant l'envoi de la réclamation au payeur ou au patient. | ||
| Pourquoi c'est important Le suivi de la génération des réclamations aide à isoler les retards entre la saisie des frais et leur préparation pour la soumission. C'est une étape interne clé qui peut impacter la ponctualité globale de la facturation. Où obtenir Ceci est généralement enregistré lorsqu'un job de lot de facturation ou de génération de réclamations est exécuté. Le système enregistrera un Capture Identifier les entrées de journal ou les changements de statut indiquant que la demande de remboursement a été compilée et est prête à être soumise. Type d'événement explicit | |||
| Demande de remboursement soumise à nouveau | Cet `événement` se produit après qu'une réclamation refusée a été corrigée et est renvoyée au payeur. Il s'agit d'un `événement` de soumission distinct qui est lié à la réclamation originale. | ||
| Pourquoi c'est important Il s'agit d'une partie clé de la boucle de retravail. Mesurer le temps de nouvelle soumission et le taux de réussite des réclamations resoumises est vital pour comprendre l'efficacité du processus de résolution des refus. Où obtenir Il s'agit d'un Capture Capturer l'horodatage pour une soumission de demande de remboursement signalée comme une correction ou une nouvelle soumission. Type d'événement explicit | |||
| Solde envoyé au recouvrement | Ceci marque le moment où un solde de compte impayé est transféré à un processus de recouvrement interne ou externe. Il s'agit souvent d'un changement de statut explicite sur le compte ou l'`événement` de facturation. | ||
| Pourquoi c'est important Cette activité déclenche la phase finale de recouvrement des soldes impayés. Le suivi du taux de réussite et du temps de cycle du processus de recouvrement est vital pour minimiser les créances irrécouvrables. Où obtenir Il s'agit généralement d'un Capture Identifier le changement de statut ou la transaction qui indique qu'un compte a été confié à une agence de recouvrement. Type d'événement explicit | |||
| Suivi du refus initié | Cette activité marque le début du processus interne d'examen et de résolution d'une réclamation refusée. Elle est souvent capturée lorsqu'un utilisateur prend en charge la réclamation refusée dans une `workqueue` ou en modifie le statut. | ||
| Pourquoi c'est important Le suivi de ceci aide à mesurer la réactivité de l'équipe de gestion des refus. Les retards entre un refus et le début du suivi peuvent prolonger inutilement le cycle de revenus. Où obtenir Ceci est généralement inféré à partir des changements de statut ou de l'historique d'affectation de la réclamation dans les Capture Déduire d'un changement de statut de la demande de remboursement ou d'une entrée de journal d'audit montrant qu'un utilisateur a commencé à traiter le refus. Type d'événement inferred | |||
Guides d'extraction
Étapes
- Établir la connexion à la base de données : Obtenez des identifiants en lecture seule pour la base de données Epic Clarity. Utilisez un client SQL standard, tel que DBeaver ou Microsoft SQL Server Management Studio, pour vous connecter au serveur de base de données.
- Identifier les tables principales : Les tables primaires pour cette extraction comprennent
HSP_ACCOUNTpour les informations de cas,HSP_TRANSACTIONSpour les événements financiers,CLP_CLAIM_INFOpour le statut des demandes de remboursement, etF_ARHB_TX_SET_POST_HXpour les détails d'imputation des paiements. Vous joindrez également des fichiers maîtres commeCLARITY_EMPpour les détails des utilisateurs. - Définir la portée : Avant d'écrire la requête, déterminez la portée de votre analyse. Définissez une période spécifique, généralement de 3 à 6 mois, et identifiez les zones de service hospitalier spécifiques (
SERV_AREA_ID) ou les classes de comptes que vous souhaitez inclure ou exclure. - Développer la requête SQL : Construisez une requête SQL en utilisant une expression de table commune (CTE) pour sélectionner d'abord l'ensemble des valeurs
HSP_ACCOUNT_IDqui tombent dans votre portée définie. Cela servira de population de base des événements de facturation. - Unir les requêtes d'activités individuelles : Pour chacune des 12 activités requises, rédigez une instruction
SELECTdistincte qui récupère les données des tables pertinentes. Effectuez une jointure avec votre CTE initiale pour vous assurer de n'analyser que les comptes visés. - Combiner les requêtes avec UNION ALL : Utilisez l'opérateur
UNION ALLpour combiner les résultats de toutes les requêtes d'activités individuelles en un seul journal d'événements cohérent. Cela empile les lignes de chaque requête verticalement. - Mapper au schéma standard : Dans chaque instruction
SELECT, aliassez les colonnes pour qu'elles correspondent au schéma ProcessMind requis :BillingEvent,ActivityName,EventTimestamp,ResponsibleUser, etc. UtilisezNULLpour les attributs qui ne sont pas applicables à une activité spécifique. - Exécuter et affiner la requête : Exécutez la requête complète sur la base de données Clarity. En raison de la taille des tables, cela peut prendre un temps considérable. Si la performance est un problème, restreignez davantage la période ou ajoutez des filtres plus spécifiques dans la CTE initiale.
- Examiner la sortie : Une fois la requête terminée, inspectez les premières centaines de lignes de la sortie. Vérifiez que toutes les colonnes sont présentes, que les horodatages sont dans un format cohérent et que les différentes valeurs de
ActivityNameapparaissent comme prévu. - Exporter au format CSV : Exportez l'ensemble des résultats de votre client SQL vers un fichier CSV. Assurez-vous que le fichier utilise l'encodage UTF-8 et inclut une ligne d'en-tête avec les noms de colonnes corrects.
- Préparer pour le téléchargement : Avant de télécharger vers ProcessMind, ouvrez le fichier CSV pour confirmer qu'il n'y a pas d'erreurs de formatage. Vérifiez que le format de l'horodatage est cohérent, par exemple,
AAAA-MM-JJ HH:MM:SS. Le fichier est maintenant prêt pour l'ingestion.
Configuration
- Connexion à la base de données : Un compte utilisateur en lecture seule avec accès à la base de données Epic Clarity est requis.
- Paramètres de période : La requête fournie utilise les variables
@StartDateet@EndDate. Celles-ci doivent être configurées pour définir la période d'analyse. Une plage de 3 à 6 mois est recommandée pour équilibrer le volume de données et les performances. - Mappage des tables et colonnes : La requête suppose des noms de tables et de colonnes Clarity standard. La configuration ou la version spécifique d'Epic de votre organisation peut présenter des variations. Vous devrez peut-être ajuster les noms de tables, les noms de colonnes ou les conditions de jointure en conséquence.
- Codes de transaction et de statut : La requête inclut des espaces réservés comme
[Your Denial Tx Type]et[Your Collections Status Code]. Vous devez consulter les administrateurs de votre système Epic ou examiner les fichiers maîtres pertinents, tels queZC_TX_TYPEouZC_ACCOUNT_STATUS, pour trouver les codes corrects pour votre instance. - Filtrage : Pour de meilleures performances et une analyse plus ciblée, ajoutez des filtres à la CTE
BaseAccountsinitiale. Les filtres courants incluentSERV_AREA_IDpour limiter par zone de service hospitalier ouACCOUNT_CLASS_Cpour se concentrer sur la facturation des patients hospitalisés ou externes.
a Exemple de requête sql
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-06-30';
WITH BaseAccounts AS (
SELECT DISTINCT
HA.HSP_ACCOUNT_ID
FROM
HSP_ACCOUNT HA
WHERE
HA.ADM_DATE_TIME >= @StartDate
AND HA.ADM_DATE_TIME <= @EndDate
-- Add additional filters here if needed, for example:
-- AND HA.SERV_AREA_ID = [Your Service Area ID]
)
-- 1. Service Rendered
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Service Rendered' AS ActivityName,
tx.SERVICE_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
AND tx.ORIG_REV_TX_ID IS NULL -- Not a reversal
UNION ALL
-- 2. Charges Captured
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Charges Captured' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
UNION ALL
-- 3. Claim Generated
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Generated' AS ActivityName,
claim.GENERATED_TIME AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.GENERATED_TIME IS NOT NULL
UNION ALL
-- 4. Claim Submitted to Payer
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Submitted to Payer' AS ActivityName,
claim.XMIT_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.XMIT_DATE IS NOT NULL
UNION ALL
-- 5. Claim Denied by Payer
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Denied by Payer' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
remit.REMIT_CODE_ID AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN F_ARHB_TX_SET_POST_HX remit ON tx.TX_ID = remit.TX_ID
WHERE tx.TX_TYPE_C IN ([Your Denial Tx Type]) -- Placeholder for denial transaction type codes
UNION ALL
-- 6. Denial Follow-Up Initiated (assumes status change on account)
SELECT
hist.HSP_ACCOUNT_ID AS BillingEvent,
'Denial Follow-Up Initiated' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
NULL AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCT_STATUS_HX hist
INNER JOIN BaseAccounts ba ON hist.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON hist.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE hist.ACCOUNT_STATUS_C = [Your Denial Followup Status Code] -- Placeholder for a status indicating follow-up
UNION ALL
-- 7. Claim Resubmitted
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Resubmitted' AS ActivityName,
claim.RESUBMIT_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON claim.RESUBMIT_USER_ID = emp.USER_ID
WHERE claim.RESUBMIT_DATE IS NOT NULL
UNION ALL
-- 8. Payment Received & 9. Payment Posted to Account (combined for this query)
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Payment Posted to Account' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE tx.TX_TYPE_C IN ([Your Payer Payment Tx Type], [Your Patient Payment Tx Type]) -- Placeholder for payment transaction types
UNION ALL
-- 10. Account Adjustment Made
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Account Adjustment Made' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
zcar.NAME AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN ZC_ADJ_REASON zcar ON tx.ADJ_REASON_C = zcar.ADJ_REASON_C
WHERE tx.TX_TYPE_C IN ([Your Adjustment Tx Type]) -- Placeholder for adjustment transaction types
UNION ALL
-- 11. Balance Sent to Collections
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Balance Sent to Collections' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
INNER JOIN HSP_ACCT_STATUS_HX hist ON acct.HSP_ACCOUNT_ID = hist.HSP_ACCOUNT_ID AND hist.ACCOUNT_STATUS_C = [Your Collections Status Code]
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE acct.ACCOUNT_STATUS_C = [Your Collections Status Code] -- Placeholder for collections status
UNION ALL
-- 12. Account Closed
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Account Closed' AS ActivityName,
acct.CLOSED_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- System or Final transaction user
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE acct.ACCT_FIN_BALANCE = 0
AND acct.CLOSED_DATE IS NOT NULL
AND acct.CLOSED_DATE BETWEEN @StartDate and @EndDate
ORDER BY
BillingEvent,
EventTimestamp;Étapes
- Établir la connexion à la base de données : Obtenez des identifiants en lecture seule pour la base de données Epic Clarity. Utilisez un client SQL standard, tel que DBeaver ou Microsoft SQL Server Management Studio, pour vous connecter au serveur de base de données.
- Identifier les tables principales : Les tables primaires pour cette extraction comprennent
HSP_ACCOUNTpour les informations de cas,HSP_TRANSACTIONSpour les événements financiers,CLP_CLAIM_INFOpour le statut des demandes de remboursement, etF_ARHB_TX_SET_POST_HXpour les détails d'imputation des paiements. Vous joindrez également des fichiers maîtres commeCLARITY_EMPpour les détails des utilisateurs. - Définir la portée : Avant d'écrire la requête, déterminez la portée de votre analyse. Définissez une période spécifique, généralement de 3 à 6 mois, et identifiez les zones de service hospitalier spécifiques (
SERV_AREA_ID) ou les classes de comptes que vous souhaitez inclure ou exclure. - Développer la requête SQL : Construisez une requête SQL en utilisant une expression de table commune (CTE) pour sélectionner d'abord l'ensemble des valeurs
HSP_ACCOUNT_IDqui tombent dans votre portée définie. Cela servira de population de base des événements de facturation. - Unir les requêtes d'activités individuelles : Pour chacune des 12 activités requises, rédigez une instruction
SELECTdistincte qui récupère les données des tables pertinentes. Effectuez une jointure avec votre CTE initiale pour vous assurer de n'analyser que les comptes visés. - Combiner les requêtes avec UNION ALL : Utilisez l'opérateur
UNION ALLpour combiner les résultats de toutes les requêtes d'activités individuelles en un seul journal d'événements cohérent. Cela empile les lignes de chaque requête verticalement. - Mapper au schéma standard : Dans chaque instruction
SELECT, aliassez les colonnes pour qu'elles correspondent au schéma ProcessMind requis :BillingEvent,ActivityName,EventTimestamp,ResponsibleUser, etc. UtilisezNULLpour les attributs qui ne sont pas applicables à une activité spécifique. - Exécuter et affiner la requête : Exécutez la requête complète sur la base de données Clarity. En raison de la taille des tables, cela peut prendre un temps considérable. Si la performance est un problème, restreignez davantage la période ou ajoutez des filtres plus spécifiques dans la CTE initiale.
- Examiner la sortie : Une fois la requête terminée, inspectez les premières centaines de lignes de la sortie. Vérifiez que toutes les colonnes sont présentes, que les horodatages sont dans un format cohérent et que les différentes valeurs de
ActivityNameapparaissent comme prévu. - Exporter au format CSV : Exportez l'ensemble des résultats de votre client SQL vers un fichier CSV. Assurez-vous que le fichier utilise l'encodage UTF-8 et inclut une ligne d'en-tête avec les noms de colonnes corrects.
- Préparer pour le téléchargement : Avant de télécharger vers ProcessMind, ouvrez le fichier CSV pour confirmer qu'il n'y a pas d'erreurs de formatage. Vérifiez que le format de l'horodatage est cohérent, par exemple,
AAAA-MM-JJ HH:MM:SS. Le fichier est maintenant prêt pour l'ingestion.
Configuration
- Connexion à la base de données : Un compte utilisateur en lecture seule avec accès à la base de données Epic Clarity est requis.
- Paramètres de période : La requête fournie utilise les variables
@StartDateet@EndDate. Celles-ci doivent être configurées pour définir la période d'analyse. Une plage de 3 à 6 mois est recommandée pour équilibrer le volume de données et les performances. - Mappage des tables et colonnes : La requête suppose des noms de tables et de colonnes Clarity standard. La configuration ou la version spécifique d'Epic de votre organisation peut présenter des variations. Vous devrez peut-être ajuster les noms de tables, les noms de colonnes ou les conditions de jointure en conséquence.
- Codes de transaction et de statut : La requête inclut des espaces réservés comme
[Your Denial Tx Type]et[Your Collections Status Code]. Vous devez consulter les administrateurs de votre système Epic ou examiner les fichiers maîtres pertinents, tels queZC_TX_TYPEouZC_ACCOUNT_STATUS, pour trouver les codes corrects pour votre instance. - Filtrage : Pour de meilleures performances et une analyse plus ciblée, ajoutez des filtres à la CTE
BaseAccountsinitiale. Les filtres courants incluentSERV_AREA_IDpour limiter par zone de service hospitalier ouACCOUNT_CLASS_Cpour se concentrer sur la facturation des patients hospitalisés ou externes.
a Exemple de requête sql
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-06-30';
WITH BaseAccounts AS (
SELECT DISTINCT
HA.HSP_ACCOUNT_ID
FROM
HSP_ACCOUNT HA
WHERE
HA.ADM_DATE_TIME >= @StartDate
AND HA.ADM_DATE_TIME <= @EndDate
-- Add additional filters here if needed, for example:
-- AND HA.SERV_AREA_ID = [Your Service Area ID]
)
-- 1. Service Rendered
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Service Rendered' AS ActivityName,
tx.SERVICE_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
AND tx.ORIG_REV_TX_ID IS NULL -- Not a reversal
UNION ALL
-- 2. Charges Captured
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Charges Captured' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
proc.PROC_NAME AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EAP proc ON tx.PROC_ID = proc.PROC_ID
WHERE tx.TX_TYPE_C = 1 -- Charge Transaction Type
UNION ALL
-- 3. Claim Generated
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Generated' AS ActivityName,
claim.GENERATED_TIME AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.GENERATED_TIME IS NOT NULL
UNION ALL
-- 4. Claim Submitted to Payer
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Submitted to Payer' AS ActivityName,
claim.XMIT_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- Often a system process
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE claim.XMIT_DATE IS NOT NULL
UNION ALL
-- 5. Claim Denied by Payer
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Denied by Payer' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
remit.REMIT_CODE_ID AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN F_ARHB_TX_SET_POST_HX remit ON tx.TX_ID = remit.TX_ID
WHERE tx.TX_TYPE_C IN ([Your Denial Tx Type]) -- Placeholder for denial transaction type codes
UNION ALL
-- 6. Denial Follow-Up Initiated (assumes status change on account)
SELECT
hist.HSP_ACCOUNT_ID AS BillingEvent,
'Denial Follow-Up Initiated' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
NULL AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCT_STATUS_HX hist
INNER JOIN BaseAccounts ba ON hist.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON hist.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE hist.ACCOUNT_STATUS_C = [Your Denial Followup Status Code] -- Placeholder for a status indicating follow-up
UNION ALL
-- 7. Claim Resubmitted
SELECT
claim.HSP_ACCOUNT_ID AS BillingEvent,
'Claim Resubmitted' AS ActivityName,
claim.RESUBMIT_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM CLP_CLAIM_INFO claim
INNER JOIN BaseAccounts ba ON claim.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON claim.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON claim.RESUBMIT_USER_ID = emp.USER_ID
WHERE claim.RESUBMIT_DATE IS NOT NULL
UNION ALL
-- 8. Payment Received & 9. Payment Posted to Account (combined for this query)
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Payment Posted to Account' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE tx.TX_TYPE_C IN ([Your Payer Payment Tx Type], [Your Patient Payment Tx Type]) -- Placeholder for payment transaction types
UNION ALL
-- 10. Account Adjustment Made
SELECT
tx.HSP_ACCOUNT_ID AS BillingEvent,
'Account Adjustment Made' AS ActivityName,
tx.POST_DATE AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
zcar.NAME AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_TRANSACTIONS tx
INNER JOIN BaseAccounts ba ON tx.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN HSP_ACCOUNT acct ON tx.HSP_ACCOUNT_ID = acct.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_EMP emp ON tx.POSTING_USER_ID = emp.USER_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN ZC_ADJ_REASON zcar ON tx.ADJ_REASON_C = zcar.ADJ_REASON_C
WHERE tx.TX_TYPE_C IN ([Your Adjustment Tx Type]) -- Placeholder for adjustment transaction types
UNION ALL
-- 11. Balance Sent to Collections
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Balance Sent to Collections' AS ActivityName,
hist.CHANGE_AUDIT_DTTM AS EventTimestamp,
emp.USER_ID AS ResponsibleUser,
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
INNER JOIN HSP_ACCT_STATUS_HX hist ON acct.HSP_ACCOUNT_ID = hist.HSP_ACCOUNT_ID AND hist.ACCOUNT_STATUS_C = [Your Collections Status Code]
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
LEFT JOIN CLARITY_EMP emp ON hist.CHANGE_AUDIT_USER_ID = emp.USER_ID
WHERE acct.ACCOUNT_STATUS_C = [Your Collections Status Code] -- Placeholder for collections status
UNION ALL
-- 12. Account Closed
SELECT
acct.HSP_ACCOUNT_ID AS BillingEvent,
'Account Closed' AS ActivityName,
acct.CLOSED_DATE AS EventTimestamp,
NULL AS ResponsibleUser, -- System or Final transaction user
dep.DEPARTMENT_NAME AS BillingDepartment,
NULL AS DenialReasonCode,
NULL AS AdjustmentReason,
acct.ACCT_FIN_BALANCE AS OutstandingBalance,
NULL AS ServiceType
FROM HSP_ACCOUNT acct
INNER JOIN BaseAccounts ba ON acct.HSP_ACCOUNT_ID = ba.HSP_ACCOUNT_ID
LEFT JOIN CLARITY_DEP dep ON acct.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE acct.ACCT_FIN_BALANCE = 0
AND acct.CLOSED_DATE IS NOT NULL
AND acct.CLOSED_DATE BETWEEN @StartDate and @EndDate
ORDER BY
BillingEvent,
EventTimestamp;