API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 12th, 2018
83
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 21.02 KB | None | 0 0
raw download clone embed print report
  1. -- Function: smurds.fnc_init_bi_f_loyal_monthly_result(date, text)
  2.  
  3. -- DROP FUNCTION smurds.fnc_init_bi_f_loyal_monthly_result(date, text);
  4.  
  5. CREATE OR REPLACE FUNCTION smurds.fnc_init_bi_f_loyal_monthly_result(paramdate date, paraminit text)
  6. RETURNS text AS
  7. $BODY$
  8. declare
  9. lvitext text;
  10.  
  11. lvperi_from int := 0;
  12. lvperi_to int := 0;
  13.  
  14. lvdate_from DATE;
  15. lvdate_to DATE;
  16.  
  17.  
  18. lvcounter int := 0;
  19. begin
  20.  
  21. lvperi_from = TO_CHAR(paramdate,'YYYYMM')::INT;
  22. lvdate_from = (lvperi_from||'01')::DATE;
  23. lvdate_to = LAST_DAY(lvdate_from);
  24.  
  25. lvperi_to = TO_CHAR(paramdate + '1 month'::INTERVAL,'YYYYMM')::INT;
  26.  
  27. TRUNCATE stg_f_loyal_monthly_result_candidate;
  28. TRUNCATE stg_f_loyal_monthly_result_so;
  29. TRUNCATE stg_f_loyal_monthly_result_delv;
  30. TRUNCATE stg_f_loyal_monthly_result_inv;
  31. TRUNCATE stg_f_loyal_monthly_result_base;
  32. TRUNCATE stg_f_loyal_monthly_result_base_result;
  33. TRUNCATE stg_f_loyal_monthly_result_base_frontage;
  34. TRUNCATE stg_f_loyal_monthly_result_ach;
  35.  
  36. --++FS9956
  37. /*
  38. INSERT INTO stg_f_loyal_monthly_result_so
  39. SELECT t1.vbeln, t1.kunnr, t3.kunnr_cm,
  40. SUM(CASE WHEN t1.auart <> 'ZS23' THEN t2.kzwi2 ELSE 0 END) value_so,
  41. SUM(CASE WHEN t1.auart = 'ZS23' THEN t2.kzwi2 * -1 ELSE 0 END) value_so_return,
  42. SUM(CASE WHEN m2.kdgrp IS NOT NULL AND t1.auart <> 'ZS23' THEN t2.kzwi2 ELSE 0 END) pd_value_so,
  43. SUM(CASE WHEN m2.kdgrp IS NOT NULL AND t1.auart = 'ZS23' THEN t2.kzwi2 * -1 ELSE 0 END) pd_value_so_return
  44. FROM bi_f_so_detail t2
  45. INNER JOIN
  46. (
  47. SELECT t1.vbeln, t1.kunnr, t1.auart
  48. FROM bi_f_so_header t1
  49. INNER JOIN
  50. (
  51. SELECT *
  52. FROM smusap_pl.zta_x_lyt_canddt t2
  53. WHERE t2.joindt <> '9999-12-31'
  54. AND ((t2.datab <= lvdate_to AND t2.datbi >= lvdate_to) OR ( t2.datab = '9999-12-31' ) )
  55. ) t2 ON t2.kunnr = t1.kunnr AND t2.joindt <= t1.erdat_header
  56. --WHERE t1.auart IN ('ZS23', 'ZS01', 'ZS06', 'ZS07')
  57. WHERE t1.auart IN ('ZS01', 'ZS06', 'ZS07', 'ZS39', 'ZS12', 'ZS49', 'ZS43', 'ZS61','ZS23')
  58. AND t1.erdat_header BETWEEN lvdate_from AND lvdate_to
  59. GROUP BY t1.vbeln, t1.kunnr, t1.auart
  60. ) t1 ON t2.vbeln = t1.vbeln
  61. LEFT JOIN smusap_pl.zta_x_lyt_tgt m1 ON m1.kunnr = t1.kunnr AND lvdate_from BETWEEN m1.datab AND m1.datbi
  62. LEFT JOIN smusap_pl.zta_x_lyt_drive m2 ON m2.kdgrp = m1.kdgrp
  63. AND (m2.matnr = t2.matnr OR m2.mvgr2 = t2.mvgr2 OR m2.prodh = t2.prodh)
  64. LEFT JOIN smusap_pl.zta_s_lyt_grpcm t3 ON t3.kunnr = t1.kunnr
  65. WHERE t2.period_mcsi BETWEEN lvperi_from AND lvperi_to
  66. AND t2.abgru = '-1'
  67. GROUP BY t1.vbeln, t1.kunnr, t3.kunnr_cm;
  68. */
  69.  
  70.  
  71. --++FS10665
  72. INSERT INTO stg_f_loyal_monthly_result_candidate
  73. SELECT m0.kunnr, MAX(m0.joindt), MAX((TO_CHAR(m0.joindt,'YYYYMM')||'01')::DATE) joindt_01,
  74. MAX(m0.vkorg) AS vkorg , MAX(m0.vkbur) AS vkbur,
  75. --MAX(m0.exclude_vis) AS hanger
  76. '' hanger
  77. FROM smusap_pl.zta_x_lyt_canddt m0
  78. WHERE m0.joindt <> '9999-12-31'
  79. AND ((m0.datab <= lvdate_to AND m0.datbi >= lvdate_to) OR ( m0.datab = '9999-12-31' ) )
  80. GROUP BY m0.kunnr;
  81.  
  82. INSERT INTO stg_f_loyal_monthly_result_so
  83. SELECT t1.vbeln, t1.kunnr, t3.kunnr_cm
  84. FROM bi_f_so_header t1
  85. INNER JOIN stg_f_loyal_monthly_result_candidate m0 ON m0.kunnr = t1.kunnr AND m0.joindt_01 <= t1.erdat_header
  86. LEFT JOIN smusap_pl.zta_s_lyt_grpcm t3 ON t3.kunnr = t1.kunnr
  87. --WHERE t1.auart IN ('ZS23', 'ZS01', 'ZS06', 'ZS07')
  88. WHERE t1.auart IN ('ZS01', 'ZS06', 'ZS07', 'ZS39', 'ZS12', 'ZS49', 'ZS43', 'ZS61','ZS23','ZS70','ZS71')
  89. AND t1.erdat_header BETWEEN lvdate_from AND lvdate_to
  90. GROUP BY t1.vbeln, t1.kunnr, t3.kunnr_cm;
  91. --++FS10665
  92.  
  93. INSERT INTO stg_f_loyal_monthly_result_delv
  94. SELECT COALESCE(t3.kunnr_cm, t1.kunnr) kunnr, SUM(t1.delv_value) delv_value, SUM(t1.delv_return_value) delv_return_value,
  95. MAX(m1.target) target,
  96. --MAX(m2.target) AS pd_target,
  97. MAX(m1.targetpd) AS pd_target,
  98. SUM( CASE WHEN m2.kdgrp IS NOT NULL THEN t1.delv_value ELSE 0 END) pd_delv_value,
  99. SUM( CASE WHEN m2.kdgrp IS NOT NULL THEN t1.delv_return_value ELSE 0 END) pd_delv_return_value
  100. FROM
  101. (
  102. SELECT t2.kunnr, t1.matnr, t1.mvgr2, t1.prodh,
  103. SUM(CASE WHEN t1.lfart <> 'ZD23' THEN t1.value_base_unit * (t1.qty_delv_base_unit - COALESCE(t1.qty_pod_base_unit,0)) ELSE 0 END) AS delv_value,
  104. SUM(CASE WHEN t1.lfart = 'ZD23' THEN t1.value_base_unit * (t1.qty_delv_base_unit - COALESCE(t1.qty_pod_base_unit,0)) ELSE 0 END) AS delv_return_value
  105. FROM bi_f_delv_detail t1
  106. INNER JOIN stg_f_loyal_monthly_result_so t2 ON t2.vbeln = t1.vgbel
  107. LEFT JOIN bi_f_ic_material m1 ON m1.matnr = t1.matnr
  108. WHERE t1.period_mcsi BETWEEN lvperi_from AND lvperi_to
  109. AND m1.matnr IS NULL
  110. GROUP BY t2.kunnr, t1.matnr, t1.mvgr2, t1.prodh
  111. ) t1
  112. LEFT JOIN smusap_pl.zta_x_lyt_tgt m1 ON m1.kunnr = t1.kunnr AND lvdate_from BETWEEN m1.datab AND m1.datbi
  113. LEFT JOIN smusap_pl.zta_x_lyt_drive m2 ON m2.kdgrp = m1.kdgrp
  114. AND (m2.matnr = t1.matnr OR m2.mvgr2 = t1.mvgr2 OR m2.prodh = t1.prodh)
  115. LEFT JOIN smusap_pl.zta_s_lyt_grpcm t3 ON t3.kunnr = t1.kunnr
  116. GROUP BY COALESCE(t3.kunnr_cm, t1.kunnr);
  117.  
  118. INSERT INTO stg_f_loyal_monthly_result_inv
  119. SELECT COALESCE(t3.kunnr_cm, t1.kunnr) kunnr, SUM(t1.bill_value) bill_value, SUM(t1.bill_return_value) bill_return_value,
  120. MAX(m1.target) target, MAX(m1.targetpd) AS pd_target,
  121. SUM( CASE WHEN m2.kdgrp IS NOT NULL THEN t1.bill_value ELSE 0 END) pd_bill_value,
  122. SUM( CASE WHEN m2.kdgrp IS NOT NULL THEN t1.bill_return_value ELSE 0 END) pd_bill_return_value
  123. FROM
  124. (
  125. SELECT t2.kunnr, t1.matnr, t1.mvgr2, t1.prodh,
  126. SUM(CASE WHEN t1.fkart <> 'ZI23' THEN t1.kzwi2 ELSE 0 END) AS bill_value,
  127. SUM(CASE WHEN t1.fkart = 'ZI23' THEN t1.kzwi2 ELSE 0 END) AS bill_return_value
  128. FROM bi_f_billing_detail t1
  129. INNER JOIN stg_f_loyal_monthly_result_so t2 ON t2.vbeln = t1.aubel
  130. LEFT JOIN bi_f_ic_material m1 ON m1.matnr = t1.matnr
  131. WHERE t1.fksto <> 'X' and t1.uepos in (0,-1) --and i.uepos in (0,-1) and i.spart_customer in ('88')
  132. AND t1.fkart NOT IN ('ZI91' , 'ZI92')
  133. AND t1.period_mcsi BETWEEN lvperi_from AND lvperi_to
  134. AND m1.matnr IS NULL
  135. GROUP BY t2.kunnr, t1.matnr, t1.mvgr2, t1.prodh
  136. ) t1
  137. LEFT JOIN smusap_pl.zta_x_lyt_tgt m1 ON m1.kunnr = t1.kunnr AND lvdate_from BETWEEN m1.datab AND m1.datbi
  138. LEFT JOIN smusap_pl.zta_x_lyt_drive m2 ON m2.kdgrp = m1.kdgrp
  139. AND (m2.matnr = t1.matnr OR m2.mvgr2 = t1.mvgr2 OR m2.prodh = t1.prodh)
  140. LEFT JOIN smusap_pl.zta_s_lyt_grpcm t3 ON t3.kunnr = t1.kunnr
  141. GROUP BY COALESCE(t3.kunnr_cm, t1.kunnr);
  142.  
  143. INSERT INTO stg_f_loyal_monthly_result_base
  144. SELECT t1.kunnr, t1.bill_value, t1.bill_return_value, t1.base_j, t1.base_q, FLOOR(t1.base_j / COALESCE(NULLIF(m2.qty1 * m2.qty2,0),1)) AS base_k
  145. FROM
  146. (
  147. SELECT t1.kunnr, t1.bill_value, t1.bill_return_value, t1.base_j, FLOOR(t1.base_j / COALESCE(NULLIF(m2.qty1 * m2.qty2,0),1)) AS base_q
  148. FROM
  149. (
  150. SELECT t1.kunnr, t1.bill_value, t1.bill_return_value, FLOOR(( t1.bill_value - t1.bill_return_value) / COALESCE(NULLIF(m1.valcd,0),1)) AS base_j
  151. FROM stg_f_loyal_monthly_result_inv t1
  152. LEFT JOIN smusap_pl.zta_x_lyt_valcrd m1 ON m1.card = 'J' AND lvperi_from BETWEEN m1.datab AND m1.datbi
  153. ) t1
  154. LEFT JOIN smusap_pl.zta_x_lyt_card m2 ON m2.card1 = 'J' AND m2.card2 = 'Q' AND lvperi_from BETWEEN m2.datab AND m2.datbi
  155. ) t1
  156. LEFT JOIN smusap_pl.zta_x_lyt_card m2 ON m2.card1 = 'J' AND m2.card2 = 'K' AND lvperi_from BETWEEN m2.datab AND m2.datbi;
  157.  
  158. INSERT INTO stg_f_loyal_monthly_result_base_result
  159. SELECT t1.kunnr, t1.base_j, t1.base_q, t1.base_k,
  160. CASE WHEN m11.valty = 'P' THEN FLOOR(m11.valcd/100::numeric * t1.base_j) ELSE m11.valcd END AS target_j,
  161. CASE WHEN m12.valty = 'P' THEN FLOOR(m12.valcd/100::numeric * t1.base_q) ELSE m12.valcd END AS target_q,
  162. CASE WHEN m13.valty = 'P' THEN FLOOR(m13.valcd/100::numeric * t1.base_k) ELSE m13.valcd END AS target_k,
  163. CASE WHEN m21.valty = 'P' THEN FLOOR(m21.valcd/100::numeric * t1.base_j) ELSE m21.valcd END AS frontage_j,
  164. CASE WHEN m22.valty = 'P' THEN FLOOR(m22.valcd/100::numeric * t1.base_q) ELSE m22.valcd END AS frontage_q,
  165. CASE WHEN m23.valty = 'P' THEN FLOOR(m23.valcd/100::numeric * t1.base_k) ELSE m23.valcd END AS frontage_k,
  166. CASE WHEN m31.valty = 'P' THEN FLOOR(m31.valcd/100::numeric * t1.base_j) ELSE m31.valcd END AS pd_j,
  167. CASE WHEN m32.valty = 'P' THEN FLOOR(m32.valcd/100::numeric * t1.base_q) ELSE m32.valcd END AS pd_q,
  168. CASE WHEN m33.valty = 'P' THEN FLOOR(m33.valcd/100::numeric * t1.base_k) ELSE m33.valcd END AS pd_k
  169. FROM stg_f_loyal_monthly_result_base t1
  170. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m11 ON m11.bnsty = 'T' AND m11.card = 'J' AND lvdate_from BETWEEN m11.datab AND m11.datbi
  171. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m12 ON m12.bnsty = 'T' AND m12.card = 'Q' AND lvdate_from BETWEEN m12.datab AND m12.datbi
  172. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m13 ON m13.bnsty = 'T' AND m13.card = 'K' AND lvdate_from BETWEEN m13.datab AND m13.datbi
  173. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m21 ON m21.bnsty = 'F' AND m21.card = 'J' AND lvdate_from BETWEEN m21.datab AND m21.datbi
  174. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m22 ON m22.bnsty = 'F' AND m22.card = 'Q' AND lvdate_from BETWEEN m22.datab AND m22.datbi
  175. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m23 ON m23.bnsty = 'F' AND m23.card = 'K' AND lvdate_from BETWEEN m23.datab AND m23.datbi
  176. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m31 ON m31.bnsty = 'P' AND m31.card = 'J' AND lvdate_from BETWEEN m31.datab AND m31.datbi
  177. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m32 ON m32.bnsty = 'P' AND m32.card = 'Q' AND lvdate_from BETWEEN m32.datab AND m32.datbi
  178. LEFT JOIN smusap_pl.zta_x_lyt_hitbns m33 ON m33.bnsty = 'P' AND m33.card = 'K' AND lvdate_from BETWEEN m33.datab AND m33.datbi;
  179.  
  180.  
  181. /*
  182. - Flag frontage
  183. cek survey terakhir yang dilakukan pada customer untuk bulan yang dihitung dari table MY_SFAMD_T_SUMMARY_DISPLAY.
  184. Select cust_id, max(doc_date) doc_date from smusap.MY_SFAMD_T_SUMMARY_DISPLAY where to_char(doc_date,’YYYYMM’) = periode yang dihitung.
  185. dari hasil query di atas ambil jawaban untuk survey pada tanggal terakhir di survey setiap customer dimana disp_id in (maintain m_parameter) dan element_id (maintain m_parameter).
  186. Value_achieve dari hasil query ini harus bernilai 1 semua.
  187. Parameter ambil di MY_SFAMD_M_PARAMETER
  188. 1. PARAMETER_ID= ‘loyalty_display_id’ untuk parameter display_id
  189. 2. PARAMETER_ID= ‘loyalty_element_id’untuk parameter element_id
  190.  
  191. Jika kondisi diatas terpenuh maka isi field ini dengan Y else N. jika tidak ditemui hasil survey otomatis N.
  192. */
  193.  
  194.  
  195. --++FS10665 loyalty_display_element_id ke loyalty_frontage_display_element_id
  196. INSERT INTO stg_f_loyal_monthly_result_base_frontage
  197. SELECT t1.kunnr, CASE WHEN t1.value_achieve = 1 THEN 'Y' ELSE 'N' END AS flag_frontage, param_type
  198. FROM
  199. (
  200. SELECT COALESCE(t3.kunnr_cm, t1.kunnr) kunnr, param_type, MIN(t1.value_achieve) value_achieve
  201. FROM
  202. (
  203. SELECT LPAD(t1.cust_id,10,'0') kunnr, m1.param_type, t1.doc_date, t1.disp_id, t1.element_id, t1.value_achieve
  204. FROM smusap.my_sfamd_t_summary_display t1
  205. --INNER JOIN smusap.my_sfamd_m_parameter m1 ON m1.parameter_id = 'loyalty_display_id' AND t1.disp_id = m1.value
  206. --INNER JOIN smusap.my_sfamd_m_parameter m1 ON m1.parameter_id = 'loyalty_element_id' AND t1.element_id = m1.value
  207. INNER JOIN
  208. (
  209. SELECT param_type, idx[1] AS disp_id, idx[2] AS element_id
  210. FROM
  211. (
  212. SELECT param_type, m1.value, regexp_split_to_array(m1.value,'-') idx
  213. FROM
  214. (
  215. --loyalty_display_element_id => Layak bayar
  216. --loyalty_frontage_display_element_id => Frontage
  217. SELECT CASE WHEN parameter_id = 'loyalty_display_element_id' THEN 'LB' ELSE 'FT' END AS param_type,
  218. m1.value
  219. FROM smusap.my_sfamd_m_parameter m1
  220. WHERE m1.parameter_id IN ('loyalty_display_element_id', 'loyalty_frontage_display_element_id')
  221. ) m1
  222. ) m1
  223. GROUP BY param_type, idx[1], idx[2]
  224. ) m1 ON t1.disp_id = m1.disp_id AND t1.element_id = m1.element_id
  225. INNER JOIN
  226. (
  227. SELECT t1.cust_id, MAX(t1.doc_date) AS doc_date
  228. FROM smusap.my_sfamd_t_summary_display t1
  229. WHERE t1.doc_date BETWEEN lvdate_from AND lvdate_to
  230. GROUP BY t1.cust_id
  231. ) AS t0 ON t0.cust_id = t1.cust_id AND t0.doc_date = t1.doc_date
  232. WHERE t1.doc_date BETWEEN lvdate_from AND lvdate_to
  233. ) t1
  234. LEFT JOIN smusap_pl.zta_s_lyt_grpcm t3 ON t3.kunnr = t1.kunnr
  235. GROUP BY COALESCE(t3.kunnr_cm, t1.kunnr), param_type
  236. ) t1;
  237.  
  238. /*
  239. INSERT INTO stg_f_loyal_monthly_result_ach
  240. SELECT t1.kunnr, t1.flag_target, t1.flag_frontage, t1.flag_pd,
  241. t1.flag_layak_bayar,
  242. t1.base_j, t1.target_j, t1.frontage_j, t1.pd_j,
  243. t1.base_q, t1.target_q, t1.frontage_q, t1.pd_q,
  244. t1.base_k, t1.target_k, t1.frontage_k, t1.pd_k,
  245. t1.total_j, t1.total_q,
  246. CASE WHEN t1.flag_layak_bayar = 'Y' THEN
  247. CASE WHEN t1.flag_target = 'Y' THEN FLOOR(t1.total_q / m2.qty1* m2.qty2) + t1.target_k ELSE 0 END +
  248. CASE WHEN t1.flag_frontage = 'Y' THEN t1.frontage_k ELSE 0 END +
  249. CASE WHEN t1.flag_pd = 'Y' THEN t1.pd_k ELSE 0 END
  250. ELSE 0 END AS total_k
  251. FROM
  252. (
  253. SELECT t1.kunnr, t1.flag_target, t1.flag_frontage, t1.flag_pd,
  254. t1.flag_layak_bayar,
  255. t1.base_j, t1.target_j, t1.frontage_j, t1.pd_j,
  256. t1.base_q, t1.target_q, t1.frontage_q, t1.pd_q,
  257. t1.base_k, t1.target_k, t1.frontage_k, t1.pd_k,
  258. t1.total_j,
  259. CASE WHEN t1.flag_layak_bayar = 'Y' THEN
  260. CASE WHEN t1.flag_target = 'Y' THEN FLOOR(t1.total_j / m2.qty1* m2.qty2) + t1.target_q ELSE 0 END +
  261. CASE WHEN t1.flag_frontage = 'Y' THEN t1.frontage_q ELSE 0 END +
  262. CASE WHEN t1.flag_pd = 'Y' THEN t1.pd_q ELSE 0 END
  263. ELSE 0 END AS total_q
  264. FROM
  265. (
  266. SELECT t1.kunnr, t1.flag_target, t1.flag_frontage, t1.flag_pd,
  267. t1.flag_layak_bayar,
  268. t2.base_j, t2.target_j, t2.frontage_j, t2.pd_j,
  269. t2.base_q, t2.target_q, t2.frontage_q, t2.pd_q,
  270. t2.base_k, t2.target_k, t2.frontage_k, t2.pd_k,
  271. CASE WHEN t1.flag_layak_bayar = 'Y' THEN
  272. CASE WHEN t1.flag_target = 'Y' THEN t2.base_j + t2.target_j ELSE 0 END +
  273. CASE WHEN t1.flag_frontage = 'Y' THEN t2.frontage_j ELSE 0 END +
  274. CASE WHEN t1.flag_pd = 'Y' THEN t2.pd_j ELSE 0 END
  275. ELSE 0 END AS total_j
  276. FROM
  277. (
  278. SELECT t1.kunnr, t1.flag_target, t1.flag_frontage, t1.flag_pd,
  279. CASE WHEN t1.flag_target = 'Y' AND
  280. t1.flag_frontage = 'Y' AND
  281. t1.flag_pd = 'Y' THEN 'Y' ELSE 'N' END flag_layak_bayar
  282. FROM
  283. (
  284. SELECT t1.kunnr,
  285. CASE WHEN t1.bill_value - t1.bill_return_value >= t1.target THEN 'Y' ELSE 'N' END flag_target,
  286. CASE WHEN t2.flag_frontage = 'Y' THEN 'Y' ELSE 'N' END AS flag_frontage,
  287. CASE WHEN t1.pd_bill_value - t1.pd_target >= t1.target THEN 'Y' ELSE 'N' END flag_pd
  288. FROM stg_f_loyal_monthly_result_inv t1
  289. LEFT JOIN stg_f_loyal_monthly_result_base_frontage t2 ON t2.kunnr = t1.kunnr
  290. ) t1
  291. ) t1
  292. LEFT JOIN stg_f_loyal_monthly_result_base_result t2 ON t2.kunnr = t1.kunnr
  293. ) t1
  294. LEFT JOIN smusap_pl.zta_x_lyt_card m2 ON m2.card1 = 'J' AND m2.card2 = 'Q' AND lvdate_from BETWEEN m2.datab AND m2.datbi
  295. ) t1
  296. LEFT JOIN smusap_pl.zta_x_lyt_card m2 ON m2.card1 = 'Q' AND m2.card2 = 'K' AND lvdate_from BETWEEN m2.datab AND m2.datbi;
  297. */
  298.  
  299. INSERT INTO stg_f_loyal_monthly_result_ach
  300. SELECT t1.kunnr, t1.flag_target, t1.flag_frontage, t1.flag_pd,
  301. t1.flag_layak_bayar,
  302. t2.base_j, t2.target_j, t2.frontage_j, t2.pd_j,
  303. t2.base_q, t2.target_q, t2.frontage_q, t2.pd_q,
  304. t2.base_k, t2.target_k, t2.frontage_k, t2.pd_k,
  305. CASE WHEN t1.flag_layak_bayar = 'Y' THEN
  306. CASE WHEN t1.flag_target = 'Y' THEN t2.base_j + t2.target_j ELSE t2.base_j END +
  307. CASE WHEN t1.flag_frontage = 'Y' THEN t2.frontage_j ELSE 0 END +
  308. CASE WHEN t1.flag_pd = 'Y' THEN t2.pd_j ELSE 0 END
  309. ELSE 0 END AS total_j,
  310. CASE WHEN t1.flag_layak_bayar = 'Y' THEN
  311. CASE WHEN t1.flag_target = 'Y' THEN t2.base_q + t2.target_q ELSE t2.base_q END +
  312. CASE WHEN t1.flag_frontage = 'Y' THEN t2.frontage_q ELSE 0 END +
  313. CASE WHEN t1.flag_pd = 'Y' THEN t2.pd_q ELSE 0 END
  314. ELSE 0 END AS total_q,
  315. CASE WHEN t1.flag_layak_bayar = 'Y' THEN
  316. CASE WHEN t1.flag_target = 'Y' THEN t2.base_k + t2.target_k ELSE t2.base_k END +
  317. CASE WHEN t1.flag_frontage = 'Y' THEN t2.frontage_k ELSE 0 END +
  318. CASE WHEN t1.flag_pd = 'Y' THEN t2.pd_k ELSE 0 END
  319. ELSE 0 END AS total_k
  320. FROM
  321. (
  322. SELECT t1.kunnr, t1.flag_target, t1.flag_frontage, t1.flag_pd,
  323. --++FS9956
  324. /*
  325. CASE WHEN t1.flag_target = 'Y' AND
  326. t1.flag_frontage = 'Y' AND
  327. t1.flag_pd = 'Y' THEN 'Y' ELSE 'N' END flag_layak_bayar
  328. */
  329. --CASE WHEN t1.flag_frontage = 'Y' OR m0.hanger = 'X' THEN 'Y' ELSE 'N' END flag_layak_bayar
  330. CASE WHEN t1.flag_layak_bayar = 'Y' OR m0.hanger = 'X' THEN 'Y' ELSE 'N' END flag_layak_bayar
  331. FROM
  332. (
  333. SELECT t1.kunnr,
  334. CASE WHEN t1.bill_value - t1.bill_return_value >= t1.target THEN 'Y' ELSE 'N' END flag_target,
  335. CASE WHEN t21.flag_frontage = 'Y' THEN 'Y' ELSE 'N' END AS flag_frontage,
  336. CASE WHEN t22.flag_frontage = 'Y' THEN 'Y' ELSE 'N' END AS flag_layak_bayar,
  337. --CASE WHEN t1.pd_bill_value - t1.pd_target >= t1.target THEN 'Y' ELSE 'N' END flag_pd
  338. CASE WHEN t1.pd_bill_value - t1.pd_bill_return_value >= t1.pd_target THEN 'Y' ELSE 'N' END flag_pd
  339. FROM stg_f_loyal_monthly_result_inv t1
  340. --'LB' ELSE 'FT' END AS param_type,
  341. LEFT JOIN stg_f_loyal_monthly_result_base_frontage t21 ON t21.kunnr = t1.kunnr AND t21.param_type = 'FT'
  342. LEFT JOIN stg_f_loyal_monthly_result_base_frontage t22 ON t22.kunnr = t1.kunnr AND t22.param_type = 'LB'
  343. ) t1
  344. LEFT JOIN stg_f_loyal_monthly_result_candidate m0 ON m0.kunnr = t1.kunnr
  345. ) t1
  346. LEFT JOIN stg_f_loyal_monthly_result_base_result t2 ON t2.kunnr = t1.kunnr ;
  347.  
  348. DELETE FROM bi_f_loyal_monthly_result WHERE period = lvperi_from;
  349.  
  350. INSERT INTO bi_f_loyal_monthly_result
  351. SELECT lvperi_from AS period ,
  352. COALESCE(NULLIF(m1.kunnr, ''), '-1') AS kunnr ,
  353. COALESCE(NULLIF(m2.bukrs, ''), '-1') AS bukrs ,
  354. COALESCE(NULLIF(m1.vkorg, ''), '-1') AS vkorg ,
  355. COALESCE(NULLIF(m1.vkbur, ''), '-1') AS vkbur ,
  356. COALESCE(t1.delv_value, 0) AS delv_value ,
  357. COALESCE(t1.delv_return_value, 0) AS delv_return_value ,
  358. COALESCE(t2.bill_value, 0) AS bill_value ,
  359. COALESCE(t2.bill_return_value, 0) AS bill_return_value ,
  360. COALESCE(t1.pd_delv_value, 0) AS pd_delv_value ,
  361. COALESCE(t1.pd_delv_return_value, 0) AS pd_delv_return_value ,
  362. COALESCE(t2.pd_bill_value, 0) AS pd_bill_value ,
  363. COALESCE(t2.pd_bill_return_value, 0) AS pd_bill_return_value ,
  364.  
  365. COALESCE(t1.target, t2.target, 0) AS target ,
  366. COALESCE(t1.pd_target, t2.pd_target, 0) AS pd_target ,
  367.  
  368. COALESCE(t3.base_j, 0) AS base_j ,
  369. COALESCE(t3.base_q, 0) AS base_q ,
  370. COALESCE(t3.base_k, 0) AS base_k ,
  371. COALESCE(t3.target_j, 0) AS target_j ,
  372. COALESCE(t3.target_q, 0) AS target_q ,
  373. COALESCE(t3.target_k, 0) AS target_k ,
  374. COALESCE(t3.frontage_j, 0) AS frontage_j ,
  375. COALESCE(t3.frontage_q, 0) AS frontage_q ,
  376. COALESCE(t3.frontage_k, 0) AS frontage_k ,
  377. COALESCE(t3.pd_j, 0) AS pd_j ,
  378. COALESCE(t3.pd_q, 0) AS pd_q ,
  379. COALESCE(t3.pd_k, 0) AS pd_k ,
  380. COALESCE(t3.total_j, 0) AS total_j ,
  381. COALESCE(t3.total_q, 0) AS total_q ,
  382. COALESCE(t3.total_k, 0) AS total_k ,
  383. COALESCE(NULLIF(t3.flag_target, ''), 'N') AS flag_target ,
  384. COALESCE(NULLIF(t3.flag_frontage, ''), 'N') AS flag_frontage ,
  385. COALESCE(NULLIF(t3.flag_pd, ''), 'N') AS flag_pd ,
  386. COALESCE(NULLIF(t3.flag_layak_bayar, ''), 'N') AS flag_layak_bayar,
  387.  
  388. /*
  389. COALESCE(t4.value_so,0) value_so,
  390. COALESCE(t4.value_so_return,0) value_so_return,
  391. COALESCE(t4.pd_value_so,0) pd_value_so,
  392. COALESCE(t4.pd_value_so_return,0) pd_value_so_return,
  393. */
  394. SUBSTR(COALESCE(t5.kunnr_group,'-1'),1,100) kunnr_group
  395. --FROM smusap_pl.zta_x_lyt_canddt m1
  396. FROM
  397. (
  398. SELECT COALESCE(t3.kunnr_cm, m1.kunnr) kunnr ,
  399. MAX(m1.vkorg) AS vkorg ,
  400. MAX(m1.vkbur) AS vkbur
  401. FROM smusap_pl.zta_x_lyt_canddt m1
  402. LEFT JOIN smusap_pl.zta_s_lyt_grpcm t3 ON t3.kunnr = m1.kunnr
  403. WHERE m1.joindt <> '9999-12-31'
  404. AND ((m1.datab <= lvdate_to AND m1.datbi >= lvdate_to) OR ( m1.datab = '9999-12-31' ) )
  405. GROUP BY COALESCE(t3.kunnr_cm, m1.kunnr)
  406. ) m1
  407. LEFT JOIN smusap_pl.tvko m2 ON m2.vkorg = m1.vkorg
  408. LEFT JOIN stg_f_loyal_monthly_result_delv t1 ON t1.kunnr = m1.kunnr
  409. LEFT JOIN stg_f_loyal_monthly_result_inv t2 ON t2.kunnr = m1.kunnr
  410. LEFT JOIN stg_f_loyal_monthly_result_ach t3 ON t3.kunnr = m1.kunnr
  411. LEFT JOIN
  412. (
  413. SELECT COALESCE(kunnr_cm, kunnr) kunnr
  414. /*SUM(value_so) value_so,
  415. SUM(value_so_return) value_so_return,
  416. SUM(pd_value_so) pd_value_so,
  417. SUM(pd_value_so_return) pd_value_so_return
  418. */
  419. FROM stg_f_loyal_monthly_result_so
  420. GROUP BY COALESCE(kunnr_cm, kunnr)
  421. ) t4 ON t4.kunnr = m1.kunnr
  422. LEFT JOIN
  423. (
  424. SELECT kunnr_cm kunnr, string_agg(ltrim(kunnr,0), ',') kunnr_group
  425. FROM smusap_pl.zta_s_lyt_grpcm
  426. GROUP BY kunnr_cm
  427. ) t5 ON t5.kunnr = m1.kunnr;
  428.  
  429.  
  430. GET DIAGNOSTICS lvcounter = ROW_COUNT;
  431.  
  432. lvitext := 'CDC bi_f_loyal_monthly_result (' || paramdate || ') ' || lvcounter || ' Already Done on ' || now();
  433. return lvitext;
  434. commit;
  435. end;
  436. $BODY$
  437. LANGUAGE plpgsql VOLATILE;
  438. ALTER FUNCTION smurds.fnc_init_bi_f_loyal_monthly_result(date, text)
  439. OWNER TO smuetl;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!