This query can be used to join the Bill Details with the various Charge record types for a high-level view of the Billing for Charges that will include the Quantity and Amount of the Charges.
It is important to note that the Amount/Rate on the Charge record is going to be the current value for the Rate and Quantity of the Charge. The Cost in the Bill Details reflects what was Billed at the moment in time that the Bill was generated. So as soon as any changes are made in the current rate/quantities your report will no longer be accurate. Since the Bill Details does not store the Quantity and Rate for each Charge there is no way to run Historical Reports. It is only accurate at the exact time that the Bill Details are generated. Users need to export this data at the time the Bill is run and store it somewhere outside of PCR-360 to maintain a historical or snapshot view of how the data looked when the Bill was generated.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
SELECT
BD.CHRG_CATALOG_NAME,
BD.COST,
COALESCE(SC.OVERRIDE_AMT, SC.AMOUNT,
EC.OVERRIDE_AMT, EC.AMOUNT,
GC.OVERRIDE_AMT, GC.AMOUNT,
SDC.OVERRIDE_AMT, SDC.AMOUNT
) AS RATE,
COALESCE(SC.QUANTITY, EC.QUANTITY, GC.QUANTITY, SDC.QUANTITY, 1) AS QUANTITY
FROM BILL_DETAILS BD
INNER JOIN CHARGES_BILLED CB
ON CB.BILL_DETAILS_RECID = BD.RECID
INNER JOIN CHARGES C
ON C.RECID = CB.CHARGES_RECID
LEFT JOIN SERVICES_CHARGES SC
ON SC.RECID = C.ORIG_TABLE_RECID AND C.ORIG_TABLE_NAME = 'SERVICES_CHARGES'
LEFT JOIN EQUIPMENT_CHARGES EC
ON EC.RECID = C.ORIG_TABLE_RECID AND C.ORIG_TABLE_NAME = 'EQUIPMENT_CHARGES'
LEFT JOIN GLA_CHARGES GC
ON GC.RECID = C.ORIG_TABLE_RECID AND C.ORIG_TABLE_NAME = 'GLA_CHARGES'
LEFT JOIN SERVICE_DESK_CHARGES SDC
ON SDC.RECID = C.ORIG_TABLE_RECID AND C.ORIG_TABLE_NAME = 'SERVICE_DESK_CHARGES' |