Table to facilitate reporting on pledges and pending installment payments, this table is a combination of the RE objects Fact_GiftCommitment and v_PHSOutstandingPledgeInstallments.
The table contains one row for each application split, eg one row for each gift split just like Fact_GiftCommitment.
Note: to add/edit information in this area about the table in general, choose edit entry from the black menu at the top of the page. To add/edit information in the table below for specific fields, choose the edit link UNDER the table. These options are only available if you have edit permissions AND you have logged in to wordpress – any questions pls let Helen know
| Field Name | Sample Data | Comments |
|---|---|---|
| GiftCommitmentFactID | 1 | PK |
| GiftFactID | 113537 | key to gift in DW |
| GiftSystemID | 115008 | FK to gift in RE |
| GiftSplitSystemID | 115008 | FK to gift in RE |
| ConstituentDimID | 26359 | FK to constituent in DW |
| PledgeStatus | Closed (fully paid or written off) Overdue Pledge (unpaid installment in the past) Upcoming Payment (payment coming up in the next 90 days) Payments on Schedule (all others not addressed above – nothing overdue, and nothing due in next 90) |
|
| GiftSubType | No Gift Subtype | |
| Donor | John M. Connors (404203) | |
| PledgeSplitAmount | 250000 | |
| PledgeSplitBalance | 150000 | |
| PledgeTotalAmount | 4000000 | |
| PledgeTotalBalance | 150000 | |
| PledgeWriteOffTotal | 0 | Can we do this at the split level in CRM, we had to jump through hoops do do this in RE |
| ReminderFlag | YES | |
| SplitRatio | 1 | |
| GiftTypeDimID | 278 | FK to gift type in DW |
| CampaignDimID | 68 | FK to campaign in DW |
| FundDimID | 806 | FK to fund in DW |
| FundIdentifier | 21783 | |
| FundDescription | The Jolly Fund | FundDescription |
| AppealDimID | 94 | FK to appeal in DW |
| GiftDateDimID | 20030801 | FK to gift date in DW |
| GiftDate | 00:00.0 | actual gift date |
| NextInstallmentDate | NULL | next installment date |
| NextInstallmentDateDimID | 19000101 | FK to next installment date in DW dim_date |
| InstallmentFrequencyDimID | 10 | FK to installment frequency in DW |
| FirstInstallmentDate | 00:00.0 | Date of first installment |
| FirstInstallmentDateDimID | 20030801 | FK to dim_date for first installment |
| LastInstallmentDate | 00:00.0 | |
| LastInstallmentDateDimID | 20030801 | |
| FirstPaymentDate | 00:00.0 | |
| FirstPaymentDateDimID | 20030731 | |
| LastPaymentDate | 00:00.0 | |
| LastPaymentDateDimID | 20030731 | |
| MissedPaymentAmount | NULL | delta between total installments prior to today, and total payments made prior to today |
| MissedPaymentCount | NULL | |
| FundDimID | 6614 | |
| LastPaymentAmount | 50000 | |
| LastPaymentDate | 00:00.0 | |
| MostRecentInstallmentDueDate | 00:00.0 | |
| FinalInstallmentDueDate | 00:00.0 | |
| PastDue0-90Days | 0 | |
| PastDue91-180Days | 0 | |
| PastDue181-365Days | 0 | |
| PastDue365plusDays | 0 | |
| DueNext0-90Days | 0 | |
| DueNext91plusDays | 150000 | |
| PriorFYBalanceDue | 0 | PriorFYBalance field from view, propose renaming to make it more clear |
| CurrentFYBalanceDue | 0 | |
| CFYPlus1BalanceDue | 50000 | |
| CFYPlus2BalanceDue | 50000 | |
| CFYPlus3BalanceDue | 50000 | |
| CFYPlus4OrMoreBalanceDue | 0 | |
| ETLControlID | 1 | needed?? |
| SourceID | 3 | ID of the affiliate database, do we need an indicator like this? |