PACKAGE BODY: APPS.MSD_DP_SCENARIO_OUTPUT_LVL_PKG

Source

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