PACKAGE BODY: APPS.MSD_DP_SCENARIO_EVENT_PKG

Source

1 PACKAGE BODY msd_dp_scenario_event_pkg AS
2 /* $Header: msddpseb.pls 120.0 2005/05/25 17:59:04 appldev noship $ */
3 
4 /* Public Procedures */
5 
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME       in varchar2,
7                                  P_SCENARIO_NAME          in varchar2,
8                                  P_EVENT_NAME             in varchar2,
9                                  P_OWNER                  in varchar2,
10                                  P_EVENT_ASSOCIATION_PRIORITY in varchar2,
11                                  P_DELETEABLE_FLAG            in varchar2,
12                                  P_LAST_UPDATE_DATE           in varchar2,
13                                  P_ENABLE_NONSEED_FLAG             in VARCHAR2,
14                                  P_CUSTOM_MODE             in VARCHAR2
15                                  )
16 is
17 
18 l_demand_plan_id number;
19 l_scenario_id number;
20 l_event_id number;
21 f_luby    number;  -- entity owner in file
22 f_ludate  date;    -- entity update date in file
23 db_luby   number;  -- entity owner in db
24 db_ludate date;    -- entity update date in db
25 
26 BEGIN
27 
28         -- translate values to IDs
29         select DEMAND_PLAN_ID
30         into l_demand_plan_id
31         from MSD_DEMAND_PLANS
32         where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
33 
34         select SCENARIO_ID
35 	into l_scenario_id
36 	from MSD_DP_SCENARIOS
37 	where DEMAND_PLAN_ID = l_demand_plan_id
38 	and SCENARIO_NAME = P_SCENARIO_NAME;
39 
40 	select EVENT_ID
41 	into l_event_id
42 	from MSD_EVENTS
43 	where EVENT_NAME = P_EVENT_NAME;
44 
45 
46         -- Translate owner to file_last_updated_by
47         f_luby := fnd_load_util.owner_id(P_OWNER);
48 
49         -- Translate char last_update_date to date
50         f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
51         begin
52           select LAST_UPDATED_BY, LAST_UPDATE_DATE
53           into db_luby, db_ludate
54           from msd_dp_scenario_events
55           where DEMAND_PLAN_ID = l_demand_plan_id
56           and SCENARIO_ID = l_scenario_id
57 	  and EVENT_ID = l_event_id;
58 
59 	  -- Test for customization and version
60           if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
61                                         db_ludate, P_CUSTOM_MODE)) then
62 	    -- Update existing row
63             update msd_dp_scenario_events
64 	    set  event_association_priority = p_event_association_priority,
65 	    last_update_date = f_ludate            ,
66             deleteable_flag = P_DELETEABLE_FLAG             ,
67             enable_nonseed_flag = P_ENABLE_NONSEED_FLAG             ,
68 	    last_updated_by = f_luby             ,
69 	    last_update_login = 0
70             where demand_plan_id = l_demand_plan_id
71             and scenario_id = l_scenario_id
72    	    and event_id = l_event_id;
73 
74           end if;
75         exception
76           when no_data_found then
77             -- Record doesn't exist - insert in all cases
78             insert into msd_dp_scenario_events
79             (DEMAND_PLAN_ID            ,
80 	    SCENARIO_ID ,
81             EVENT_ID,
82             EVENT_ASSOCIATION_PRIORITY               ,
83             CREATION_DATE	       ,
84             CREATED_BY                  ,
85             LAST_UPDATE_DATE            ,
86             LAST_UPDATED_BY             ,
87             LAST_UPDATE_LOGIN           ,
88 	    ENABLE_NONSEED_FLAG ,
89 	    DELETEABLE_FLAG
90             )
91             values
92             (l_demand_plan_id            ,
93             l_scenario_id  ,
94             l_event_id,
95             p_event_association_priority               ,
96             f_ludate	       ,
97 	    f_luby                  ,
98 	    f_ludate            ,
99 	    f_luby             ,
100 	    0           ,
101 	    P_ENABLE_NONSEED_FLAG ,
102 	    P_DELETEABLE_FLAG
103 	    );
104         end;
105 
106 END;
107 
108 END msd_dp_scenario_event_pkg ;