[json] RIA
Viewer
- InstapayReconciliation:
- {
- fields: [
- "order_number",
- "order_date ",
- "customer ",
- "currency_from",
- "amount_sent",
- "tax",
- "total",
- "comm",
- "vendor_txn_id",
- "customer_transaction_amount ",
- "FORMAT_DATETIME('%d-%m-%Y', DATETIME(created_at, 'Asia/Kuala_Lumpur')) as CreatedAt",
- "recon_flag as ReconFlag",
- ],
- filter:
- {},
- whereClause: "and recon_flag !='present_in_both'",
- orderBy: "created_at desc",
- timestampColumn: "created_at",
- table: "master_recon",
- },
- InstapayRiaDebitAmount:
- {
- fields: "round(sum(amount),2) AS Debit",
- whereClause: "AND source_channel = 'ria' and source_type = 'regular' and source_transaction_type = 'debit' and recon_flag IN ('present_in_ria','present_in_both')",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaReversalAmount:
- {
- fields: "round(sum(amount),2) AS Reversal",
- whereClause: "AND source_channel = 'ria' and source_type = 'regular' and source_transaction_type = 'reversal' and recon_flag IN ('present_in_ria','present_in_both')",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaTotalAmount:
- {
- fields: "round(sum(amount),2) AS Total",
- whereClause: "AND source_channel = 'ria' and source_type = 'regular' and source_transaction_type in ('debit', 'reversal') and recon_flag IN ('present_in_ria','present_in_both')",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaDebitYSEAmount:
- {
- fields: "round(sum(amount),2) AS DebitYSE",
- whereClause: "AND source_channel = 'ria' and source_type = 'yse' and source_transaction_type = 'debit' and recon_flag='remittance_debit'",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaReversalYSEAmount:
- {
- fields: "round(sum(amount),2) AS ReversalYSE",
- whereClause: "AND source_channel = 'ria' and source_type = 'yse' and source_transaction_type = 'reversal' and recon_flag='remittance_reversal'",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaTotalYSEAmount:
- {
- fields: "round(sum(amount),2) AS Total",
- whereClause: "AND source_channel = 'ria' and source_type = 'yse' and recon_flag in ('remittance_debit', 'remittance_reversal')",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaDebitVarianceAmount:
- {
- fields: "round(SUM(case when recon_flag IN ('present_in_ria','present_in_both') then (CASE WHEN total < 0 THEN total ELSE 0 END) when recon_flag = 'remittance_debit' then -1*ifnull(account_amount,0) end), 2) as Variance",
- whereClause: "AND recon_flag IN ('remittance_debit', 'present_in_ria','present_in_both') AND source_channel = 'ria'",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaReversalVarianceAmount:
- {
- fields: "round(SUM(case when recon_flag IN ('present_in_ria','present_in_both') then (CASE WHEN total > 0 THEN total ELSE 0 END) when recon_flag = 'remittance_reversal' then -1*ifnull(account_amount,0) end), 2) as Variance",
- whereClause: "AND recon_flag IN ('remittance_reversal', 'present_in_ria','present_in_both') AND source_channel = 'ria'",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
- InstapayRiaTotalVarianceAmount:
- {
- fields: "round(SUM(case when recon_flag IN ('present_in_ria','present_in_both') then total when recon_flag in ('remittance_reversal', 'remittance_debit') then -1*ifnull(account_amount,0) end), 2) as Variance",
- whereClause: "AND recon_flag IN ('remittance_debit', 'remittance_reversal', 'present_in_ria','present_in_both') AND source_channel = 'ria'",
- timestampColumn: "created_at",
- table: `master_recon`,
- },
Editor
You can edit this paste and save as new: