Noetix: Including the GL Cost in the GMF_Item_Costs Template

This blog post covers how to add in a field called GL_Cost_Item_Period_Total which displays the GL Cost from the GMF.GL_ITEM_CST table for both Release 11i and Release 12 of Oracle E-Business Suite.

The field uses a sub-query as a comparison between adding in the table and doing a sub-query revealed that adding the table causes rows to be excluded from the result set (and therefore is a big Noetix No-No).


The SQL required for Release 11 (in the column definition) is;

(SELECT MAX(GIC.ACCTG_COST)
   FROM GMF.GL_ITEM_CST GIC
  WHERE 1=1
    AND GIC.PERIOD_CODE = CLDTL.PERIOD_CODE
    AND GIC.Orgn_Code = XMAP.PROCESS_ORGN_CODE
    AND GIC.ITEM_ID = COSTS.Item_Id
) GL_Cost_Item_Period_Total

By using the MAX value we ensure that only a single record is returned. In our configuration this is correct but you should do some testing to ensure the value is correct in all circumstances (a lot easier than it sounds - just change the SQL above to be MAX(GIC.ACCTG_COST) - MIN(GIC.ACCTG_COST), do a rebuild and look for non-zero valued columns).

The SQL required for Release 12 is;

(SELECT MAX(GIC.ACCTG_COST)
   FROM GMF.GL_ITEM_CST GIC
  WHERE 1=1
    AND GIC.PERIOD_ID = PSTAT.PERIOD_ID
    AND GIC.Organization_Id = XMAP.ORGANIZATION_ID
    AND GIC.INVENTORY_ITEM_ID = COSTS.INVENTORY_ITEM_ID
) GL_Cost_Item_Period_Total

The change is around the join condition and reflect the changes Oracle has made at Release 12.

The code to test the new view is;

SELECT DISTINCT 
  Item$Item, -- You'll need to change this to your item-identifying flex field
  PERIOD_CODE, 
  GL_Cost_Item_Period_Total 
FROM GMFG0_Item_Costs
;

The complete code for the file called "gmf_item_costs_gl_cost_item_period_total_xu2.sql" which needs to be saved in your Installs directory and linked to from wnoetxu2.sql is below;

-- Template for adding a simple column to the Noetix template table
-- This template can be used for adding columns of type 'COL' and 'EXPR'
-- It MUST NOT be used for column types 'LOOK', 'ATTR' or 'AUTOJOIN'
-- ****************************************************************************
-- File Name:    gmf_item_costs_gl_cost_item_period_total_xu2.sql
-- Date Created: 26-JUL-2011
-- Purpose:
--    To add in a column that shows the GL Cost Total for the Item and Period
-- ****************************************************************************


-- output to .lst file
@utlspon gmf_item_costs_gl_cost_item_period_total_xu2


COLUMN max_from NEW_VALUE max_from_position


SELECT MAX(from_clause_position) max_from
FROM   n_view_table_templates
WHERE  view_label = 'GMF_Item_Costs';


COLUMN max_col NEW_VALUE max_col_position


SELECT MAX(column_position) max_col
FROM   n_view_column_templates
WHERE  view_label = 'GMF_Item_Costs';


-- -----------
INSERT INTO n_view_column_templates
  (view_label, query_position, column_label, table_alias, column_expression, column_position, column_type, description, group_by_flag, gen_search_by_col_flag, profile_option, product_version, created_by, creation_date, last_updated_by, last_update_date)
VALUES
  ('GMF_Item_Costs' -- view_label
  ,1 -- query_position
  ,'GL_Cost_Item_Period_Total' -- column_label
  ,NULL -- table_alias
  ,'(SELECT MAX(GIC.ACCTG_COST) ' || 
   '   FROM GMF.GL_ITEM_CST GIC ' || 
   '  WHERE 1=1 ' || 
   '    AND GIC.PERIOD_ID = PSTAT.PERIOD_ID ' || 
   '    AND GIC.Organization_Id = XMAP.ORGANIZATION_ID ' || 
   '    AND GIC.INVENTORY_ITEM_ID = COSTS.INVENTORY_ITEM_ID ' || 
   ')' -- column_expression
  ,(&max_col_position + 1) -- column_position
  ,'EXPR' -- column_type
  ,'The total GL Cost associated with the Item and Period. NOTE Do not try and SUM this column as the value will be incorrect' -- description
  ,'N' -- group_by_flag
  ,'N' -- gen_search_by_col_flag
  ,null -- profile_option
  ,'12+' -- product_version
  ,'A Pellew' -- created_by
  ,TO_DATE('26-JUL-2011') -- creation_date
  ,'A Pellew' -- last_updated_by
  ,TO_DATE('26-JUL-2011')) -- last_update_date
;


INSERT INTO n_view_column_templates
  (view_label, query_position, column_label, table_alias, column_expression, column_position, column_type, description, group_by_flag, gen_search_by_col_flag, profile_option, product_version, created_by, creation_date, last_updated_by, last_update_date)
VALUES
  ('GMF_Item_Costs' -- view_label
  ,1 -- query_position
  ,'GL_Cost_Item_Period_Total' -- column_label
  ,NULL -- table_alias
  ,'(SELECT MAX(GIC.ACCTG_COST) ' || 
   '   FROM GMF.GL_ITEM_CST GIC ' || 
   '  WHERE 1=1 ' || 
   '    AND GIC.PERIOD_CODE = CLDTL.PERIOD_CODE ' || 
   '    AND GIC.Orgn_Code = XMAP.PROCESS_ORGN_CODE ' || 
   '    AND GIC.ITEM_ID = COSTS.Item_Id ' || 
   ')' -- column_expression
  ,(&max_col_position + 1) -- column_position
  ,'EXPR' -- column_type
  ,'The total GL Cost associated with the Item and Period. NOTE Do not try and SUM this column as the value will be incorrect' -- description
  ,'N' -- group_by_flag
  ,'N' -- gen_search_by_col_flag
  ,null -- profile_option
  ,'11-11.999' -- product_version
  ,'A Pellew' -- created_by
  ,TO_DATE('26-JUL-2011') -- creation_date
  ,'A Pellew' -- last_updated_by
  ,TO_DATE('26-JUL-2011')) -- last_update_date
;


INSERT INTO n_view_table_templates
  (view_label, query_position, table_alias, from_clause_position, application_label, table_name, product_version, base_table_flag, subquery_flag, gen_search_by_col_flag, created_by, creation_date, last_updated_by, last_update_date)
VALUES
  ('GMF_Item_Costs' -- view_label
  ,1 -- query_position
  ,'GIC' -- table_alias
  ,(&max_from_position + 1) -- from_clause_position
  ,'GMF' -- application_label
  ,'GL_ITEM_CST' -- table_name
  ,'%' -- product_version
  ,'N' -- base_table_flag
  ,'Y' -- subquery_flag
  ,'N' -- gen_search_by_col_flag
  ,'A Pellew' -- created_by
  ,to_date('26-JUL-2011') -- creation_date
  ,'A Pellew' -- last_updated_by
  ,to_date('26-JUL-2011')) -- last_update_date
;


COMMIT;


@utlspoff


NOTE: Updated on the 27th July 2011 removing the "chr(13)" codes, this (ironically) seems to cause the column to not be added to the view. Not quite sure why (and neither are Noetix - as the column does appear in the help) but removing the additional code solves the problem.