Notes

INSERT INTO lu_chart_query (pk_lu_chart_query_id, shortname, data_source_type, query_string, create_user, create_time, update_user, update_time) VALUES
(79006, 'Receivables Forecasting (ADL)', 'MAIN',"
SELECT customer_number AS A_Customer_Number,
customer_name AS A_Customer_Name,
IF(projected_date >= CURRENT_DATE,'Future','Past') AS AFS_Period,
customer_segment AS AFM_Customer_Segment,
company_code AS AFM_Company_Code,
IF(projected_date >= CURRENT_DATE,DATE_ADD(projected_date, INTERVAL(4-WEEKDAY(projected_date)) DAY),DATE_ADD(DATE_SUB(CURRENT_DATE,INTERVAL 1 WEEK), INTERVAL(4-WEEKDAY(CURRENT_DATE)) DAY)) AS D_Week_Ending,
processor AS AFM_Processor,
SUM(open_amount) AS M_Cash_Projected
FROM
(SELECT DATE_ADD(due_date, INTERVAL (tmp.avg_days) DAY) AS projected_date,
water.ar_items.customer_number,
water.ar_items.company_code,
water.ar_items.open_amount,
water.ar_items.invoice_number AS invoice_number,
IFNULL(
(SELECT `display_text`
FROM water.`dim_customer`
FORCE INDEX(customer_fetch)
WHERE `account_id` = $account_id
AND `account_customer_number_norm` = water.ar_items.customer_number
LIMIT 1), 'Not Available') AS customer_name,
IFNULL(
(SELECT `shortname`
FROM water.`dim_customer_segment`
WHERE fk_account_id = $account_id
AND `pk_customer_segment_id` =
(SELECT MAX(fk_customer_segment_id)
FROM water.`dim_customer`
FORCE INDEX(customer_fetch)
WHERE `account_id` = $account_id
AND account_customer_number_norm = water.ar_items.customer_number
AND company_code = water.ar_items.company_code
) ), 'Unknown') AS customer_segment,
IFNULL(
(SELECT full_name
FROM lu_user
JOIN map_user_customer ON `fk_processor_id` = `pk_user_id`
WHERE `FK_CUSTOMER_MAP_ID` IN
(SELECT customer_map_id
FROM water.`dim_customer`
FORCE INDEX(customer_fetch)
WHERE `account_id` = $account_id
AND account_customer_number_norm = water.ar_items.customer_number
AND company_code = water.ar_items.company_code )
LIMIT 1), 'Unknown') AS processor
FROM water.ar_items
JOIN
(SELECT customer_number,
company_code,
IFNULL(SUM(`avg_days_late` * `count_cleared_inv`) / SUM(`count_cleared_inv`),0) AS avg_days
FROM water.ar_customer_metrics_aggregate
WHERE account_id = $account_id
AND month_last_date > LAST_DAY(SUBDATE(CURRENT_DATE(), INTERVAL 4 MONTH))
AND month_last_date <= LAST_DAY(SUBDATE(CURRENT_DATE(), INTERVAL 1 MONTH))
AND customer_number IN
(SELECT DISTINCT(`customer_number`)
FROM water.`ar_cont_aggregate` FORCE INDEX(open_invoices)
WHERE account_id = $account_id AND `total_receivables` > 0 )
GROUP BY customer_number,company_code) tmp
ON tmp.customer_number = water.ar_items.customer_number
AND tmp.company_code = water.ar_items.company_code
WHERE account_id = $account_id
AND clearing_date = '9999-12-31'
AND open_amount > 0
AND invoice_number!='Unknown'
) res
GROUP BY customer_number,company_code,D_Week_Ending
ORDER BY D_Week_Ending", 'pawan.saw', NOW(), 'pawan.saw', NOW());







INSERT INTO lu_chart_data_provider (pk_lu_chart_data_provider_id,shortname,description,provider_class,fk_query_id ,create_user,create_time,update_user,update_time)
VALUES (79006,'Receivables Forecasting (ADL)','','NULL',79006,'pawan.saw',NOW(),'pawan.saw',NOW());





INSERT INTO chart (pk_chart_id,shortname,description,fk_chart_type,fk_product_id,fk_chart_category_id ,img_url,sequence_id,fk_drilldown_chart_id, fk_chart_data_provider_id,fk_chart_builder_id,exclude_flag,create_user,create_time,update_user,update_time,is_deleted,is_internal,dw_data_table,`options`)
VALUES (79006,'Receivables Forecasting (ADL)','',(SELECT pk_lu_chart_type_id FROM lu_chart_type WHERE shortname ='Standard' LIMIT 1),(SELECT pk_product_id FROM analytics_lu_product WHERE shortname ='CMS'), (SELECT pk_lu_chart_category_id FROM lu_chart_category WHERE shortname ='Custom' LIMIT 1),NULL,NULL,NULL, 79006,NULL,NULL,'pawan.saw',NOW(),'pawan.saw',NOW(),0,0,NULL,'{
"plotOptions": {
"series": {
"minPointLength": 3
}
},
"subtitle": {
"useHTML": true,
"text": "Average Days Late (ADL) for each customer is calculated based on previous cash realization patterns. <br/>ADL is added to invoice due date of open invoices (credits excluded) to arrive at Cash projected date and respective invoice open amounts are aggregated to arrive at Cash Projected.",
"style": {
"fontSize": "12px"
},
"align": "left"
}
}');





INSERT INTO map_chart_account (fk_chart_id,fk_account_id,exclude_flag,create_user,create_time,update_user,update_time)
VALUES (79006,79,NULL,'pawan.saw',NOW(),'pawan.saw',NOW());










INSERT INTO `lu_chart_query` (`pk_lu_chart_query_id`, `shortname`, `data_source_type`, `query_string`, `create_user`, `create_time`, `update_user`, `update_time`)
VALUES (79106, 'Cash Projected - Invoices', 'MAIN',"SELECT customer_number AS A_customer_number,
customer_name AS A_customer_name,
invoice_number AS A_invoice_number,
company_code AS A_company_code,
open_amount AS M_open_amount,
invoice_date AS D_invoice_date,
due_date AS D_due_date,
average_days_late AS M_average_days_late,
projected_date AS D_projected_date
FROM
(SELECT customer_number,
customer_name,
company_code,
IF(projected_date >= CURRENT_DATE,'Future','Past') AS AFS_Period,
invoice_number,
customer_segment,
processor,
open_amount,
invoice_date,
due_date,
average_days_late,
projected_date,
IF(projected_date >= CURRENT_DATE,DATE_ADD(projected_date, INTERVAL(4-WEEKDAY(projected_date)) DAY),DATE_ADD(DATE_SUB(CURRENT_DATE,INTERVAL 1 WEEK), INTERVAL(4-WEEKDAY(CURRENT_DATE)) DAY)) AS D_Week_Ending
FROM
(SELECT DATE_ADD(due_date, INTERVAL (tmp.avg_days) DAY) AS projected_date,
water.ar_items.customer_number,
water.ar_items.company_code,
water.ar_items.open_amount,
water.ar_items.invoice_number,
water.ar_items.invoice_date,
water.ar_items.due_date,
avg_days AS average_days_late,
IFNULL(
(SELECT `display_text`
FROM water.`dim_customer`
FORCE INDEX(customer_fetch)
WHERE `account_id` = $account_id
AND `account_customer_number_norm` = water.ar_items.customer_number
LIMIT 1), 'Not Available') AS customer_name,
IFNULL(
(SELECT `shortname`
FROM water.`dim_customer_segment`
WHERE fk_account_id = $account_id
AND `pk_customer_segment_id` =
(SELECT MAX(fk_customer_segment_id)
FROM water.`dim_customer`
FORCE INDEX(customer_fetch)
WHERE `account_id` = $account_id
AND account_customer_number_norm = water.ar_items.customer_number
AND company_code = water.ar_items.company_code
) ), 'Unknown') AS customer_segment,
IFNULL(
(SELECT full_name
FROM lu_user
JOIN map_user_customer ON `fk_processor_id` = `pk_user_id`
WHERE `FK_CUSTOMER_MAP_ID` IN
(SELECT customer_map_id
FROM water.`dim_customer`
FORCE INDEX(customer_fetch)
WHERE `account_id` = $account_id
AND account_customer_number_norm = water.ar_items.customer_number
AND company_code = water.ar_items.company_code )
LIMIT 1), 'Unknown') AS processor
FROM water.ar_items
JOIN
(SELECT customer_number,
company_code,
IFNULL(SUM(`avg_days_late` * `count_cleared_inv`) / SUM(`count_cleared_inv`),0) AS avg_days
FROM water.ar_customer_metrics_aggregate
WHERE account_id = $account_id
AND month_last_date > LAST_DAY(SUBDATE(CURRENT_DATE(), INTERVAL 4 MONTH))
AND month_last_date <= LAST_DAY(SUBDATE(CURRENT_DATE(), INTERVAL 1 MONTH))
AND customer_number IN
(SELECT DISTINCT(`customer_number`)
FROM water.`ar_cont_aggregate`
FORCE INDEX(open_invoices)
WHERE account_id = $account_id
AND `total_receivables` > 0 )
GROUP BY customer_number,
company_code) tmp ON tmp.customer_number = water.ar_items.customer_number
AND tmp.company_code = water.ar_items.company_code
WHERE account_id = $account_id
AND clearing_date = '9999-12-31'
AND open_amount > 0 AND invoice_number!='Unknown')tmp2) res
WHERE $filterQuery
ORDER BY customer_name,
invoice_date,
average_days_late",'pawan.saw', NOW(), 'pawan.saw', NOW());



INSERT INTO `lu_chart_data_provider`
(`pk_lu_chart_data_provider_id`,`shortname`,`description`,`provider_class`,`fk_query_id`,`create_user`,`create_time`,`update_user`,`update_time`)
VALUES
(79106,'Cash Projected - Invoices','','NULL',79106,'pawan.saw',NOW(),'pawan.saw',NOW());


INSERT INTO `chart`
(`pk_chart_id`,`shortname`,`description`,`fk_chart_type`,`fk_product_id`,`fk_chart_category_id`,`img_url`,`sequence_id`,`fk_drilldown_chart_id`,
`fk_chart_data_provider_id`,`fk_chart_builder_id`,`exclude_flag`,`create_user`,`create_time`,`update_user`,`update_time`,`is_deleted`,`is_internal`,`dw_data_table`)
VALUES
(79106,'Cash Projected - Invoices',' ',(SELECT `pk_lu_chart_type_id`
FROM `lu_chart_type`
WHERE `shortname` ='SimpleGrid' LIMIT 1),(SELECT pk_product_id FROM analytics_lu_product WHERE shortname ='CMS'),
(SELECT `pk_lu_chart_category_id`
FROM `lu_chart_category`
WHERE `shortname` ='Custom' LIMIT 1),NULL,NULL,NULL,
79106,NULL,1,'pawan.saw',NOW(),'pawan.saw',NOW(),0,0,NULL);



INSERT INTO `map_chart_account`
( `fk_chart_id`,`fk_account_id`,`exclude_flag`,`create_user`,`create_time`,`update_user`,`update_time`)
VALUES
(79106,79,NULL,'pawan.saw',NOW(),'pawan.saw',NOW());



INSERT INTO `map_chart_drilldown` (`pk_map_chart_drilldown_id`, `source_chart_id`, `drilldown_chart_id`, `source_chart_field`, `drilldown_filter_field`, `create_user`, `create_time`, `update_user`, `update_time`)
VALUES (NULL, '79006','79106', 'D_Week_Ending', "DATE_FORMAT(D_Week_Ending,'%b %d, %Y')", 'pawan.saw', NOW(), 'pawan.saw', NOW());

INSERT INTO `map_chart_drilldown` (`pk_map_chart_drilldown_id`, `source_chart_id`, `drilldown_chart_id`, `source_chart_field`, `drilldown_filter_field`, `create_user`, `create_time`, `update_user`, `update_time`)
VALUES (NULL, '79006','79106', 'AFM_Company_Code', "company_code", 'pawan.saw', NOW(), 'pawan.saw', NOW());

INSERT INTO `map_chart_drilldown` (`pk_map_chart_drilldown_id`, `source_chart_id`, `drilldown_chart_id`, `source_chart_field`, `drilldown_filter_field`, `create_user`, `create_time`, `update_user`, `update_time`)
VALUES (NULL, '79006','79106', 'AFM_Customer_Segment', "customer_segment", 'pawan.saw', NOW(), 'pawan.saw', NOW());

INSERT INTO `map_chart_drilldown` (`pk_map_chart_drilldown_id`, `source_chart_id`, `drilldown_chart_id`, `source_chart_field`, `drilldown_filter_field`, `create_user`, `create_time`, `update_user`, `update_time`)
VALUES (NULL, '79006','79106', 'AFM_Processor', "processor", 'pawan.saw', NOW(), 'pawan.saw', NOW());

INSERT INTO `map_chart_drilldown` (`pk_map_chart_drilldown_id`, `source_chart_id`, `drilldown_chart_id`, `source_chart_field`, `drilldown_filter_field`, `create_user`, `create_time`, `update_user`, `update_time`)
VALUES (NULL, '79006','79106', 'AFS_Period', "AFS_Period", 'pawan.saw', NOW(), 'pawan.saw', NOW());