| 12345678910111213141516171819202122232425262728 |
- -- Preserve every column and correction rule while allowing tenant/month predicates
- -- to reach the indexed receipt table before projecting corrected amounts.
- CREATE OR REPLACE VIEW "ReviewRewardIncome" AS
- SELECT
- r."objectId",
- r."company",
- r."taskId",
- r."taskKey",
- r."coachId",
- r."coachName",
- r."studentId",
- r."studentName",
- r."courseName",
- r."storeId",
- r."storeName",
- r."plannedDate",
- r."completedAt",
- r."incomeMonth",(r.amount+COALESCE(c.amount,0))::numeric(12,2) AS amount,
- CASE WHEN c."objectId" IS NULL THEN r.status ELSE 'ineligible' END AS status,
- r."exception",
- r."createdAt",
- CASE WHEN c."objectId" IS NULL THEN r."ineligibleReason" ELSE 'corrected_overdue' END AS "ineligibleReason",
- r.amount AS "originalAmount",
- c."objectId" AS "correctionId",
- c.reason AS "correctionReason",
- c.amount AS "correctionAmount",
- c."adjustmentBatchId"
- FROM "ReviewReward" r LEFT JOIN "ReviewRewardCorrection" c ON c.company=r.company AND c."rewardId"=r."objectId";
|
