Demantra- Key Tables

DEMANTRA: Key Tables

 Base Tables : 

S.No	Table Name	Important Columns	Description
1	SALES_DATA	ITEM_ID, LOCATION_ID, SALES_DATE	This is the main table which contains all the sales data history and forecast. It also contains any new series you created
2	MDP_MATRIX	ITEM_ID, LOCATION_ID, SALES_DATE, T_EP_ITEM_EP_ID, T_EP_SITE_EP_ID	This is table contains the combinations of item and locations
3	T_EP_ITEMS	T_EP_ITEM_EP_ID, ITEM, DM_ITEM_DESC	Stores the item information
4	T_EP_SITES	T_EP_SITE_EP_ID, SITE, DM_SITE_DESC	States the location/sites information
5	INPUTS	DATET	Contains the time buckets
6	COMPUTED_FIELDS	COMPUTED_NAME, COMPUTED_TITLE,DBNAME	Series definition and its name
7	WF_PROCESS_LOG	SCHEMA_ID, PROCESS_ID	Workflow instances or processes
8	TRANSFER_QUERY	VIEW_NAME, TABLE_NAME, QUERY_NAME, FROM_DATE, UNTIL_DATE	integration interface definitions
9	VERSION_DETAILS	VERSION	Demantra application version details
10	DB_EXCEPTION_LOG	ERR_DATE, PROC_NAME, ERR_MSG	Stores erros if there are any errors in the system
11	NOTES	NOTE_DESC, USER_ID	Notes information
12	WF_SCHEMAS	SCHEMA_NAME	Workflow definition
13	T_EP_EBS_CUST_CLASS	T_EP_CUST_CLASS_EP_ID, EBS_CUST_CLASS, EBS_CUST_CLASS_DESC	Customer Class
14	T_EP_EBS_CUSTOMER	T_EP_EBS_CUSTOMER_EP_ID,EBS_CUSTOMER, EBS_CUSTOMER_DESC	Customer information
15	T_EP_EBS_ACCOUNT	T_EP_EBS_ACCOUNT_EP_ID, EBS_ACCOUNT, EBS_ACCOUNT_DESC	Customer Account information
16	T_EP_ORGANIZATION	T_EP_ORGANIZATION-EP_ID, ORGANIZATION, DM_ORG_DESC	Inventory Organizations
17	T_EP_I_ATT_1	I_ATT_1	Level/Attribute information
18	T_EP_I_ATT_2	I_ATT_2	Level/Attribute information
19	T_EP_I_ATT_3	I_ATT_3	Level/Attribute information
20	T_EP_I_ATT_4	I_ATT_4	Level/Attribute information
21	T_EP_I_ATT_5	I_ATT_5	Level/Attribute information
22	SERIES_GROUPS	GROUP_NAME	Series GroupHeader
23	SERIES_GROUPS_M	SERIES_ID, GROUP_ID	Series group and series

 Interface Tables : 

S.No	Table Name	Important Columns	Description
1	T_SRC_ITEM_TMPL	DM_ITEM_CODE, DM_ITEM_DESC	Interface Table for Items
2	T_SRC_LOC_TMPL	DM_SITE_CODE, DM_SITE_DESC,DM_ORG_CODE, DM_ORG_DESC	Interface Table for Locations
3	T_SRC_SALES_TMPL	DM_ITEM_CODE,DM_SITE_CODE, SALES_DATE,  DM_ORG_CODE,,ACTUAL_QTY, ITEM_PRICE	Interface Tables for sales history data

 Important Procedures :

S.No	Procedure Name	Description
1	EP_LOAD_MAIN	Main procedure which loads all item , locations and sales date into Demantra
2	EP_LOAD_ITEMS	Loads only items
3	EP_LOAD_LOCATIONS	Loads only locations
4	EP_LOAD_SALES	loads only sales data
5	PRE_LOGON	Alter the session to set the language, date format etc

Demantra-Create series using SQL

DEMANTRA:Create series from backend




insert into COMPUTED_FIELDS (
FORECAST_TYPE_ID
, COMPUTED_NAME
, EXP_TEMPLATE
, DISP_COLOR
, DISP_LSTYLE
, DISP_LSYMBOL
, PRINT_COLOR
, PRINT_LSTYLE
, PRINT_LSYMBOL
, DISP_ORDER
, INFO_TYPE
, TABLE_FORMAT
, DO_HAVING
, COMPUTED_TITLE
, FIELD_TYPE
, SUM_FUNC
, MODE_1
, MODE_COLOR
, SCALEBLE
, TIME_AVG
, MODULE_TYPE
, DEPENDANTS
, EDITABLE
, IS_PROPORTION
, NULL_AS_ZERO
, DBNAME
, IS_DDLB
, IS_CHECK
, SERIES_WIDTH
, DROPDOWN_TABLE_NAME
, WEB_FORMULA
, IS_DEFAULT
, HINT_MESSAGE
, COMPUTEDFIELD_EXPRESSION
, CLIENT_EXP_DISP
, HIST_PRED_TYPE
, ITEMCHANGE
, LOCK_EXP
, DATA_TABLE_NAME
, COMP_DEPEND_ORDER
, DEPEND_ON_EXP_SERVER
, BACKGROUND_COLOR_EXP
, SYNCRO_FIELD
, LOCK_EXP_DISP
, BACKGROUND_EXP_DISP
, WEB_LOCK_EXPRESSION
, ATTRIBUTES_DEPENDENTS
, CALCULATION_METHOD
, LOOP_NUMBER
, MOVE_UPD_BET_FORE
, MOVE_FROM_SALES_TO_FOR
, LOOKUP_TYPE
, LOOKUP_TABLE
, LOOKUP_DISPLAY_FIELD
, LOOKUP_DATA_FIELD
, LOOKUP_EXTRA_FROM
, LOOKUP_EXTRA_WHERE
, COL_SERIES_WIDTH
, IS_RANKING
, PROP_CALC_SERIES
, UNLINKED_LEVEL_ID
, UPDATE_BY_SERIES_ID
, EXTRA_FROM
, EXTRA_WHERE
, BASE_LEVEL
, COLOR_DEPENDANTS
, LOCK_EXP_DEPENDANTS
, EXPRESSION_TYPE
, INT_AGGR_FUNC
, WAVG_BY_SERIES
, AGGR_BY
, SUMMARY_LINE_EXP
, SUMMARY_LINE_EXP_DISP
, SUMMARY_LINE_DEPENDENTS
, PRESERVATION_TYPE
, FILTER_EXP
, FILTER_EXP_DISP
, FILTER_EXP_DEPENDANTS
, FILTER_EXP_COL_DEPENDANTS
, IS_EDITABLE_SUMMARY
, MOVE_PRESERVATION_TYPE
, TABLE_ID
, DATA_TYPE
, SAME_VAL_UPDATE
, BM_VALIDATED
, MODIFIED_BY
, LOOKUP_SECURITY_TYPE_ID
, APPLICATION_ID
, LOOKUP_MIN_PERMISSION_ID)
VALUES
( 4961
, 'XXLOC_TEST_SER1'
, 'MAX(XXLOC_TEST(branch_daa.sales_date))'
, 255
, 1
, 1
, 255
, 1
, 1
, 11562
, 2
, 'Hyperlink'
, 0
, 'xxtest1'
, 1
, 'MAX'
, ''
, ''
, 0
, ''
, 0
, ''
, 3
, 0
, 0
, ''
, 0
, 0
, 250
, ''
, ''
, 0
, ''
, ''
, ''
, 3
, ''
, ''
, 'BRANCH_DATA'
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, 0
, ''
, ''
, ''
, ''
, ''
, 10
, ''
, ''
, ''
, ''
, 'DUAL'
, ''
, 0
, ''
, ''
, 1
, 'MAX'
, ''
, 0
, ''
, ''
, ''
, 3
, ''
, ''
, ''
, ''
, 0
, 3
, 4806
, 1
, 0
, 1
, 'BM'
, ''
, 'D97B38AFAB9F416BB40F9AC317062327'
,''
);

INSERT INTO series_groups_m(group_id,series_id)
      VALUES(0,vi_forecast_type_id);

INSERT INTO SERIES_GROUPS_M(GROUP_ID,SERIES_ID)
      VALUES(0,4961);

Demantra -Calling Demantra Workflow From PL/Sql Script

DEMANTRA:Calling Demantra Workflow From Plsql Script


create or replace procedure run_workflow ( p_wf_name varchar2)
is
l_server VARCHAR2 (50);
l_dm_user varchar2 (20) :=’dm’;
l_servlet VARCHAR2 (4000) DEFAULT ‘WorkflowServer?action=run_proc’;
l_wf_process_id wf_process_log.process_id%TYPE;
l_wf_url VARCHAR2 (500);
l_cnt NUMBER;
l_password varchar2(1000);
l_wf_name varchar2(1000);

BEGIN

dbms_output.put_line( ‘Procedure Started’);

SELECT pval
INTO l_server
FROM sys_params
WHERE pname = ‘AppServerURL’;

                     dbms_output.put_line(‘l_server : ‘ || l_server);

                      l_wf_name := replace(p_wf_name,’ ‘,’%20’);

dbms_output.put_line(‘l_wf_name : ‘ || l_wf_name);

select password
into l_password
from user_id
where user_name = l_dm_user;

dbms_output.put_line(‘l_password : ‘ || l_password);

l_servlet :=
l_servlet
||chr(38)|| ‘user=’
|| l_dm_user
|| chr(38) ||’password=’
|| l_password
|| chr(38)|| ‘schema=’
|| l_wf_name;
l_wf_url := l_server || ‘/’ || l_servlet;

commit;
dbms_output.put_line( ‘l_servlet : ‘ || l_servlet);
dbms_output.put_line(‘l_wf_url : ‘ || l_wf_url);

commit;

SELECT UTL_HTTP.request (l_wf_url)
INTO l_wf_process_id
FROM DUAL;

dbms_output.put_line(‘l_wf_process_id : ‘ || l_wf_process_id);
commit;

IF l_wf_process_id = -1
THEN

 dbms_output.put_line( ‘ERROR !!!! Unable to submit workflow ‘);

raise_application_error (-20030,‘Workflow schema consists of white spaces’);
END IF;

SELECT COUNT (1)
INTO l_cnt
FROM wf_process_log
WHERE process_id = l_wf_process_id;

IF l_cnt = 0
THEN
dbms_output.put_line( ‘ERROR !!!!Workflow did not started..please check the log ‘);
raise_application_error (-20030,‘Workflow failed since server is down’);
END IF;

dbms_output.put_line(‘End of procedure’);

commit;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20999, SQLERRM);
end run_workflow ;

Apps-Find Responsibility name using concurrent program

Find Responsibility name using concurrent program

 SELECT  frt.responsibility_name

  ,frg.request_group_name

  ,frgu.request_unit_type

  ,frgu.request_unit_id

  ,fcpt.user_concurrent_program_name

   FROM  fnd_Responsibility fr

  ,fnd_responsibility_tl frt

  ,fnd_request_groups frg

  ,fnd_request_group_units frgu

  ,fnd_concurrent_programs_tl fcpt

  WHERE  frt.responsibility_id = fr.responsibility_id

    AND  frg.request_group_id = fr.request_group_id

    AND  frgu.request_group_id = frg.request_group_id

    AND  fcpt.concurrent_program_id = frgu.request_unit_id

    AND  frt.LANGUAGE = USERENV('LANG')

    AND  fcpt.LANGUAGE = USERENV('LANG')

    AND  fcpt.user_concurrent_program_name = :conc_prg_name

    ORDER BY 1,2,3,4;

Apps-Query to find Application Name and Short Name

Query to find Application Name and Short Name

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   -- AND fat.application_name = 'Payables'  -- <change it>
 ORDER BY fat.application_name;

Apps-Submit a Concurrent Request from Backend

Submit a Concurrent Request from Backend


DECLARE
l_responsibility_id NUMBER;
l_application_id     NUMBER;
l_user_id           NUMBER;
l_request_id            NUMBER;
BEGIN
  --
  SELECT DISTINCT fr.responsibility_id,
    frx.application_id
     INTO l_responsibility_id,
    l_application_id
     FROM apps.fnd_responsibility frx,
    apps.fnd_responsibility_tl fr
    WHERE fr.responsibility_id = frx.responsibility_id
  AND LOWER (fr.responsibility_name) LIKE LOWER('Responsibility name');

   SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'USERNAME';

  --To set environment context.

  apps.fnd_global.apps_initialize (l_user_id,l_responsibility_id,l_application_id);

  --Submitting Concurrent Request

  l_request_id := fnd_request.submit_request (
                            application   => 'CUSTOM',
                            program       => 'Concurrent Program Name',
                            description   => 'Description',
                            start_time    => sysdate,
                            sub_request   => FALSE,
   argument1     => 'Argument1'
  );

  COMMIT;

  IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;

Apps- Copy Responsibilities of one user to another user

Copy Responsibilities of one user to another user 



DECLARE
  resp_count NUMBER := 0;

  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'MYUSER'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
    AND furga.end_date IS NULL;


BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
   
      fnd_user_pkg.addresp
                 (username            => 'HCM',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
   
      resp_count := resp_count + 1;
   
    EXCEPTION
    WHEN OTHERS THEN
   
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
   
    END;
  END LOOP;

  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );

  COMMIT;
END;