widana

mlm_r_report_summary_mutasi_saldo

Aug 7th, 2017
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION mlm_r_report_summary_mutasi_saldo(character varying, bigint, bigint, character varying, bigint, bigint)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pSessionId          ALIAS FOR $1;
  6.     pTenantId           ALIAS FOR $2;
  7.     pOuId               ALIAS FOR $3;
  8.     pPeriod             ALIAS FOR $4;
  9.     pUserId             ALIAS FOR $5;
  10.     pRoleId             ALIAS FOR $6;
  11.    
  12.     pRefHeader          REFCURSOR := 'refHeader';
  13.    
  14.     vDocTypeDpBalance   bigint := 252;
  15.     vDocTypeCnArBonus   bigint := 255;
  16.     vTglAwalBulan       character varying(8);
  17.     vEmptyId        bigint := -99;
  18.     vPartnerRank        character varying := 'PARTNERRANK';
  19.     vYes            character varying := 'Y';
  20.     vGroupCustomer      character varying := 'C';
  21.     vCustomerDs     character varying := 'CUSTOMER DS';
  22.  
  23. BEGIN
  24.    
  25.     DELETE FROM tt_mlm_r_report_mutasi_saldo WHERE session_id = pSessionId;
  26.  
  27.     /*
  28.     * insert ke table temporary untuk credit amount dan debt amount panggil function f_get_saldo_awal_partner_credit_limit_usage untuk
  29.     * menentukan nilai nya.
  30.     */
  31.     INSERT INTO tt_mlm_r_report_mutasi_saldo(session_id, partner_id, order_type, credit_amount, debt_amount, remark)
  32.     SELECT pSessionId, a.partner_id, 'Saldo Akhir',
  33.         CASE WHEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) > 0 THEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) ELSE 0 END AS credit_amount,
  34.         CASE WHEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) < 0 THEN f_get_saldo_awal_partner_credit_limit_usage(a.tenant_id, a.partner_id, pPeriod) * -1 ELSE 0 END AS debt_amount,
  35.         'Saldo Akhir'
  36.     FROM m_partner a
  37.     INNER JOIN  m_partner_cp n ON  a.partner_id = n.partner_id
  38.     INNER JOIN m_ctgr_partner b ON b.tenant_id = a.tenant_id AND b.ctgr_partner_id = a.ctgr_partner_id  
  39.     INNER JOIN  mlm_ds  h ON a.partner_id = h.partner_id  
  40.     INNER JOIN  mlm_member  i ON h.member_id = i.member_id  
  41.     INNER JOIN  m_area_sales  j ON h.area_sales_id = j.area_sales_id  
  42.     INNER JOIN  m_policy_area_sales  k ON h.area_sales_id = k.area_sales_id  
  43.     INNER JOIN t_user_role f ON f.user_id = pUserId AND f.role_id = pRoleId AND k.user_role_id = f.user_role_id
  44.     INNER JOIN m_policy_partner g ON g.ctgr_partner_id = b.ctgr_partner_id AND g.user_role_id = f.user_role_id  
  45.     INNER JOIN t_combo_value d ON d.code = a.rank AND d.combo_id = vPartnerRank  
  46.     LEFT OUTER JOIN m_partner_address c ON c.tenant_id = a.tenant_id AND c.partner_id = a.partner_id AND c.flg_default = vYes  
  47.     INNER JOIN m_partner_type e ON e.tenant_id = a.tenant_id AND e.partner_id = a.partner_id AND e.group_partner = vGroupCustomer  
  48.     INNER JOIN m_type_partner z ON e.type_partner_id = z.type_partner_id AND z.type_partner_code IN (vCustomerDs)  
  49.     WHERE a.tenant_id = pTenantId;
  50.  
  51.     /*
  52.     * tambahkan credit amount dan debt amount dari dokumen DNAP.
  53.     */
  54.     WITH TEMP AS (
  55.         SELECT B.mlm_r_report_mutasi_saldo_id, A.partner_id, B.order_type, SUM(CASE WHEN A.record_type = 1 THEN A.amount ELSE 0 END) AS credit_amount, SUM(CASE WHEN A.record_type = -1 THEN A.amount ELSE 0 END) AS debt_amount
  56.         FROM sl_log_partner_credit_limit_usage A, tt_mlm_r_report_mutasi_saldo B
  57.         WHERE A.tenant_id = pTenantId
  58.             AND B.session_id = pSessionId
  59.             AND A.partner_id = B.partner_id
  60.             AND SUBSTRING(A.doc_date, 1, 6) = pPeriod
  61.             AND A.amount <> 0
  62.             AND A.record_type <> 0
  63.         GROUP BY A.partner_id, B.order_type, B.mlm_r_report_mutasi_saldo_id)
  64.     UPDATE tt_mlm_r_report_mutasi_saldo Z
  65.         SET credit_amount = Z.credit_amount+A.credit_amount, debt_amount = Z.debt_amount+A.debt_amount
  66.     FROM TEMP A
  67.     WHERE Z.session_id = pSessionId
  68.         AND A.mlm_r_report_mutasi_saldo_id = Z.mlm_r_report_mutasi_saldo_id
  69.         AND A.partner_id = Z.partner_id
  70.         AND A.order_type = Z.order_type;
  71.  
  72.     OPEN pRefHeader FOR
  73.         SELECT B.partner_code, B.partner_name,
  74.             A.credit_amount, A.debt_amount, (A.debt_amount-A.credit_amount) AS total_saldo_akhir
  75.         FROM tt_mlm_r_report_mutasi_saldo A
  76.         INNER JOIN m_partner B ON A.partner_id = B.partner_id
  77.         WHERE session_id = pSessionId
  78.         ORDER BY B.partner_code ASC;
  79.     RETURN NEXT pRefHeader;
  80.    
  81.     DELETE FROM tt_mlm_r_report_mutasi_saldo WHERE session_id = pSessionId;
  82.    
  83. END;
  84. $BODY$
  85.   LANGUAGE plpgsql VOLATILE
  86.   COST 100
  87.   ROWS 1000;
Advertisement
Add Comment
Please, Sign In to add comment