Monday, February 18, 2013

Matrix Using Clarity

There is a requirement in Financial Management of Clarity

--  Resources are will work two different countries(USA,Europe)
 and need to update matrix in the back end. In the report the actual should calculate based on the country which resource works. 


I have updated the matrix based on the below Xog query 




   
       





   <?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_matrix.xsd">
    <Header action="write" externalSource="NIKU" objectType="matrix" version="12.1.1.1208"/>
    <matrices>
        <matrix defaultCurrencyCode="EUR" name="Healthcare Design" type="Cost/Rate">
            <columns>
                <column name="resource"/>
            </columns>
            <matrixRows>
   
                  <   fromDate="2013-01-01" rate="104" resource="HCD0002"
                    standardCost="151" toDate="2013-12-31"/>
   
       
   

For pick the values based on the country/Operational OBS used below function in the backend(oracle) by using PL/SQL

CREATE OR REPLACE FUNCTION CLARITY.z_get_res_rate_fct (
   param_date           VARCHAR2,
   param_resource_id    VARCHAR2,
   param_obs_id         VARCHAR2)
   RETURN NUMBER
AS
   rate     NUMBER;
   matrix   NUMBER;
   matrix1  NUMBER; 
   param_obs_in NUMBER;
BEGIN


-- Actual cost -- if we works for same country project
   SELECT CASE 
             WHEN param_obs_id = 5003878  THEN 5002161 -- Europe
             WHEN param_obs_id = 5003879  THEN 5002360 -- United States
             WHEN param_obs_id = 5003880  THEN 5002161  -- China connect with Europe as per the requirment
             ELSE 0
          END into matrix
     FROM DUAL;

  

select nvl(( SELECT numval3
          FROM (  SELECT pmv.numval3
               FROM ppa_matrix pm, ppa_matrixvalues pmv
              WHERE     pm.matrixkey = pmv.matrixkey
                    AND value1 = param_resource_id
                    and pmv.matrixkey = nvl(matrix,pmv.matrixkey)
                    AND TO_CHAR (pmv.fromdate, 'YYYY') = param_date
           ORDER BY fromDate DESC, numval3 DESC) tes
    WHERE ROWNUM = 1),0) INTO rate from dual;
    
-- Standard cost -- If we works for different country project    
    
    IF RATE=0 THEN
    
    param_obs_in:=0;
        
        IF param_obs_id=5003878 THEN        
          param_obs_in:=5002360;
        ELSIF param_obs_id=5003879 THEN
          param_obs_in:=5002161;
        ELSIF param_obs_id=5003880 THEN
          param_obs_in:=5002360;
        END IF  ;
        
        
        SELECT param_obs_in INTO matrix1 FROM DUAL;
        
             SELECT numval2
             INTO rate
             FROM (SELECT pmv.numval2
                       FROM ppa_matrix pm, ppa_matrixvalues pmv
                      WHERE     pm.matrixkey = pmv.matrixkey
                            AND value1 = param_resource_id
                            and pmv.matrixkey = nvl(matrix1,pmv.matrixkey)
                            AND TO_CHAR (pmv.fromdate, 'YYYY') = param_date
                   ORDER BY fromDate DESC, numval3 DESC) tes WHERE ROWNUM = 1;
    END IF ;
   RETURN rate;
END;
/

Note: When ever do Matrix XOG writing for resources, make sure financial information configured in the resource level. If not configured it wont allow to write matrix for the resource in the back end.



Posted by at