{"action":"created","issue":{"url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY/issues/33","repository_url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY","labels_url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY/issues/33/labels{/name}","comments_url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY/issues/33/comments","events_url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY/issues/33/events","html_url":"https://github.com/GCSO2/D3S-INVENTORY/issues/33","id":136219042,"number":33,"title":"Database: get part charge instructions","user":{"login":"giovanicani","id":5666297,"avatar_url":"https://avatars.githubusercontent.com/u/5666297?v=3","gravatar_id":"","url":"https://api.github.com/users/giovanicani","html_url":"https://github.com/giovanicani","followers_url":"https://api.github.com/users/giovanicani/followers","following_url":"https://api.github.com/users/giovanicani/following{/other_user}","gists_url":"https://api.github.com/users/giovanicani/gists{/gist_id}","starred_url":"https://api.github.com/users/giovanicani/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/giovanicani/subscriptions","organizations_url":"https://api.github.com/users/giovanicani/orgs","repos_url":"https://api.github.com/users/giovanicani/repos","events_url":"https://api.github.com/users/giovanicani/events{/privacy}","received_events_url":"https://api.github.com/users/giovanicani/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"tufailzafar","id":5826517,"avatar_url":"https://avatars.githubusercontent.com/u/5826517?v=3","gravatar_id":"","url":"https://api.github.com/users/tufailzafar","html_url":"https://github.com/tufailzafar","followers_url":"https://api.github.com/users/tufailzafar/followers","following_url":"https://api.github.com/users/tufailzafar/following{/other_user}","gists_url":"https://api.github.com/users/tufailzafar/gists{/gist_id}","starred_url":"https://api.github.com/users/tufailzafar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tufailzafar/subscriptions","organizations_url":"https://api.github.com/users/tufailzafar/orgs","repos_url":"https://api.github.com/users/tufailzafar/repos","events_url":"https://api.github.com/users/tufailzafar/events{/privacy}","received_events_url":"https://api.github.com/users/tufailzafar/received_events","type":"User","site_admin":false},"milestone":null,"comments":4,"created_at":"2016-02-24T23:12:38Z","updated_at":"2016-03-23T16:36:37Z","closed_at":null,"body":"**Basic idea of the get part charge amount... just that for Trailcon it will work a bit different, but make sure there is a function similar to this, that is used across the system**.\r\n```sql\r\nFUNCTION get_part_charge_amount(p_par_id INT(10), p_cus_id INT(10)) RETURNS DECIMAL(10,2)\r\n    READS SQL DATA\r\n    DETERMINISTIC\r\nBEGIN\r\n\r\n\t/* \r\n\t * Increased to DECIMAL(15,5) due to a truncation error that was only happening on the JAVA side. \r\n\t * A ROUND was placed at the end of the function to make sure there is only 2 decimal digits being returned.\r\n\t * Giovani on 11/11/2011.\r\n\t * \r\n\t */\r\n    DECLARE v_charge_amount,\r\n            v_charge_override,\r\n            v_item_level_charge DECIMAL(15,5) DEFAULT 0;\r\n\r\n            \r\n    DECLARE v_based_on, \r\n            v_item_level_charge_based_on VARCHAR(10);\r\n            \r\n    DECLARE v_enable_item_level_charge VARCHAR(1);\r\n    \r\n    DECLARE v_percentage_override,\r\n            v_item_level_markup_percentage,\r\n            v_parcon_percentage,\r\n            v_parcon_default_percentage DECIMAL(5,2) DEFAULT 0;          \r\n               \r\n    \r\n    SELECT IFNULL(par.enable_item_level_charge,'N')        enable_item_level_charge  ,\r\n           IFNULL(par.item_level_charge_based_on,'CHARGE') item_level_charge_based_on,\r\n           IFNULL(par.item_level_charge,0)                 item_level_charge         ,\r\n           IFNULL(par.item_level_markup_percentage,0)      item_level_markup_percentage\r\n    INTO   v_enable_item_level_charge  ,\r\n           v_item_level_charge_based_on,\r\n           v_item_level_charge         ,\r\n           v_item_level_markup_percentage\r\n    FROM   inv_parts par\r\n    WHERE  par.par_id = p_par_id;\r\n\r\n\r\n    -- If enable item level charge is flagged in the part level, it will \r\n    -- override any other charge setting on the system.\r\n    IF v_enable_item_level_charge = 'Y' THEN\r\n        IF v_item_level_charge_based_on = 'CHARGE' THEN\r\n        \r\n            SET v_charge_amount = v_item_level_charge;\r\n            \r\n        ELSEIF v_item_level_charge_based_on = 'PERCENT' THEN\r\n            \r\n            SET v_charge_amount =  ROUND(get_part_cost_amount(p_par_id) * (1 + v_item_level_markup_percentage / 100),2);        \r\n            \r\n        END IF;\r\n    \r\n    ELSE\r\n\r\n        \r\n        SELECT parcharconover.based_on                ,\r\n               parcharconover.percentage_override     ,\r\n               parcharconover.charge_override\r\n        INTO   v_based_on                ,\r\n               v_percentage_override     ,\r\n               v_charge_override\r\n        FROM   inv_part_charge_contract_overrides parcharconover\r\n               INNER JOIN inv_part_contracts parcon\r\n               ON     parcharconover.parcon_id = parcon.parcon_id\r\n               INNER JOIN ym_customers cus\r\n               ON     parcon.parcon_id  = cus.parcon_id\r\n        WHERE  parcharconover.par_id = p_par_id\r\n        AND    cus.cus_id            = p_cus_id;\r\n\r\n        -- If found is because there is a overwritten value for this part for this customer.\r\n        IF (FOUND_ROWS() > 0) THEN\r\n        \r\n            IF v_based_on = 'CHARGE' THEN\r\n\r\n                SET v_charge_amount = v_charge_override;\r\n                \r\n            ELSEIF v_based_on = 'PERCENT' THEN\r\n            \r\n                SET v_charge_amount = get_part_cost_amount(p_par_id) * (1 + v_percentage_override / 100);        \r\n                \r\n            END IF;            \r\n            \r\n        ELSE\r\n        \r\n\t        -- The SQL below contemplates contracts such as COST + 15%, where a contract is defined on the parts contracts screen but not assigned to every single part on the system.\r\n    \t    -- This only makes sense for PERCENT markup's.\r\n        \t-- Giovani Cani on 2012/02/08.\r\n        \r\n\t\t\tSELECT parcon.percentage\r\n\t\t\tINTO   v_percentage_override\r\n\t        FROM   inv_part_contracts parcon\r\n\t               INNER JOIN ym_customers cus\r\n\t               ON     parcon.parcon_id  = cus.parcon_id\r\n\t        WHERE  cus.cus_id          = p_cus_id;\r\n        \t\r\n       \t\tIF (FOUND_ROWS() > 0) THEN\r\n        \r\n\t\t\t\tSET v_charge_amount = get_part_cost_amount(p_par_id) * (1 + v_percentage_override / 100);        \r\n\t                \r\n\t        -- If not found / customer doesn't have a contract associated (new customers), then search what is the default contract for the corporation.\r\n\t        -- Select below assumes that the default contract is associated to the part.\r\n\t        ELSE\r\n\t        \r\n\t\t\t\tSELECT parcharconover.based_on, \r\n\t\t\t\t\t   IFNULL(parcharconover.percentage_override, parcon.percentage),\r\n\t                   IFNULL(parcharconover.charge_override,0)\r\n\t              INTO v_based_on                ,\r\n\t                   v_percentage_override     ,\r\n\t                   v_charge_override\r\n\t              FROM inv_part_contracts parcon\r\n\t              JOIN inv_part_charge_contract_overrides parcharconover \r\n\t                     ON parcon.parcon_id = parcharconover.parcon_id\r\n\t             WHERE parcon.cus_id       = get_parent_from_cus_id(p_cus_id)\r\n\t               AND parcharconover.par_id = p_par_id\r\n\t               AND parcon.default_flag = 'Y'\r\n\t             LIMIT 1;\r\n\t            \r\n\t            IF (FOUND_ROWS() > 0) THEN\r\n\t            \r\n\t            \tIF v_based_on = 'CHARGE' THEN\r\n\t\r\n\t                \tSET v_charge_amount = v_charge_override;\r\n\t                \r\n\t            \tELSEIF v_based_on = 'PERCENT' THEN\r\n\t            \r\n\t                \tSET v_charge_amount = get_part_cost_amount(p_par_id) * (1 + v_percentage_override / 100);        \r\n\t\t                \r\n\t    \t        END IF;\r\n\t        \r\n\t            -- If there is no contract assigned to customer, and not override contract on part, then use the percent associated to the\r\n\t            -- default contract for the corporation.\r\n\t            ELSE\r\n\t            \r\n\t                SELECT parcon.percentage\r\n\t                INTO   v_parcon_default_percentage\r\n\t                FROM   inv_part_contracts parcon\r\n\t                WHERE  parcon.cus_id       = get_parent_from_cus_id(p_cus_id)\r\n\t                AND    parcon.active_flag  = 'Y'\r\n\t                AND    parcon.default_flag = 'Y'\r\n\t                LIMIT 1;\r\n\t            \r\n\t                IF (FOUND_ROWS() > 0) THEN\r\n\t        \r\n\t                    SET v_charge_amount = get_part_cost_amount(p_par_id) * (1 + v_parcon_default_percentage / 100);        \r\n\t\r\n\t                END IF;\r\n\t            \r\n\t            END IF;\r\n\t        \r\n\t        END IF;\r\n\t   \r\n\t    END IF;\r\n    END IF;\r\n   \r\n    RETURN ROUND(v_charge_amount,2);\r\n\r\nEND"},"comment":{"url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY/issues/comments/200427846","html_url":"https://github.com/GCSO2/D3S-INVENTORY/issues/33#issuecomment-200427846","issue_url":"https://api.github.com/repos/GCSO2/D3S-INVENTORY/issues/33","id":200427846,"user":{"login":"tufailzafar","id":5826517,"avatar_url":"https://avatars.githubusercontent.com/u/5826517?v=3","gravatar_id":"","url":"https://api.github.com/users/tufailzafar","html_url":"https://github.com/tufailzafar","followers_url":"https://api.github.com/users/tufailzafar/followers","following_url":"https://api.github.com/users/tufailzafar/following{/other_user}","gists_url":"https://api.github.com/users/tufailzafar/gists{/gist_id}","starred_url":"https://api.github.com/users/tufailzafar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tufailzafar/subscriptions","organizations_url":"https://api.github.com/users/tufailzafar/orgs","repos_url":"https://api.github.com/users/tufailzafar/repos","events_url":"https://api.github.com/users/tufailzafar/events{/privacy}","received_events_url":"https://api.github.com/users/tufailzafar/received_events","type":"User","site_admin":false},"created_at":"2016-03-23T16:36:37Z","updated_at":"2016-03-23T16:36:37Z","body":"work in progress"}}