[Home] [Help]
PACKAGE BODY: APPS.MSD_DP_FORMULA_PARAMETER_PKG
Source
1 PACKAGE BODY msd_dp_formula_parameter_pkg AS
2 /* $Header: msddpfpb.pls 120.1 2006/03/31 08:32:45 brampall noship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in varchar2
7 ,P_FORMULA_NAME in varchar2
8 ,P_WHERE_USED in varchar2
9 ,P_PARAMETER_SEQUENCE in varchar2
10 ,P_OWNER in varchar2
11 ,P_ENABLED_FLAG in varchar2
12 ,P_MANDATORY_FLAG in varchar2
13 ,P_PARAMETER_TYPE in varchar2
14 ,P_PARAMETER_COMPONENT in varchar2
15 ,P_PARAMETER_VALUE in varchar2
16 ,P_SUPPLY_PLAN_FLAG in varchar2
17 ,P_SUPPLY_PLAN_NAME in varchar2
18 ,P_LAST_UPDATE_DATE in varchar2
19 ,P_CUSTOM_MODE in varchar2
20 )
21 is
22
23 l_demand_plan_id number;
24 l_formula_id number;
25 l_plan_type varchar2(10);
26 f_luby number; -- entity owner in file
27 f_ludate date; -- entity update date in file
28 db_luby number; -- entity owner in db
29 db_ludate date; -- entity update date in db
30
31
32 BEGIN
33
34 -- translate values to IDs
35 select DEMAND_PLAN_ID, PLAN_TYPE
36 into l_demand_plan_id, l_plan_type
37 from MSD_DEMAND_PLANS
38 where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
39
40
41 if l_plan_type = 'EOL' then
42 select PARAMETER_ID
43 into l_formula_id
44 from MSD_DP_PARAMETERS
45 where demand_plan_id = l_demand_plan_id
46 and parameter_type = p_formula_name;
47 else
48 select FORMULA_ID
49 into l_formula_id
50 from MSD_DP_FORMULAS
51 where demand_plan_id = l_demand_plan_id
52 and formula_name = p_formula_name;
53 end if;
54
55 -- Translate owner to file_last_updated_by
56 f_luby := fnd_load_util.owner_id(P_OWNER);
57
58 -- Translate char last_update_date to date
59 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
60 begin
61 select LAST_UPDATED_BY, LAST_UPDATE_DATE
62 into db_luby, db_ludate
63 from msd_dp_formula_parameters
64 where DEMAND_PLAN_ID = l_demand_plan_id
65 and formula_id = l_formula_id
66 and where_used = p_where_used
67 and parameter_sequence = p_parameter_sequence;
68
69 -- Test for customization and version
70 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
71 db_ludate, P_CUSTOM_MODE)) then
72 -- Update existing row
73 update msd_dp_formula_parameters
74 set enabled_flag = p_enabled_flag
75 ,mandatory_flag = p_mandatory_flag
76 ,parameter_type = p_parameter_type
77 ,parameter_component = p_parameter_component
78 ,parameter_value = p_parameter_value
79 ,supply_plan_flag = p_supply_plan_flag
80 ,supply_plan_name = p_supply_plan_name
81 ,last_update_date = f_ludate ,
82 last_updated_by = f_luby ,
83 last_update_login = 0
84 where DEMAND_PLAN_ID = l_demand_plan_id
85 and formula_id = l_formula_id
86 and where_used = p_where_used
87 and parameter_sequence = p_parameter_sequence;
88
89 end if;
90 exception
91 when no_data_found then
92 -- Record doesn't exist - insert in all cases
93 insert into msd_dp_formula_parameters
94 (DEMAND_PLAN_ID
95 ,FORMULA_ID
96 ,WHERE_USED
97 ,PARAMETER_SEQUENCE
98 ,ENABLED_FLAG
99 ,MANDATORY_FLAG
100 ,PARAMETER_TYPE
101 ,PARAMETER_COMPONENT
102 ,PARAMETER_VALUE
103 ,SUPPLY_PLAN_FLAG
104 ,SUPPLY_PLAN_NAME ,
105 CREATION_DATE ,
106 CREATED_BY ,
107 LAST_UPDATE_DATE ,
108 LAST_UPDATED_BY ,
109 LAST_UPDATE_LOGIN
110 )
111 values
112 (l_demand_plan_id
113 ,l_formula_id
114 ,P_WHERE_USED
115 ,P_PARAMETER_SEQUENCE
116 ,P_ENABLED_FLAG
117 ,P_MANDATORY_FLAG
118 ,P_PARAMETER_TYPE
119 ,P_PARAMETER_COMPONENT
120 ,P_PARAMETER_VALUE
121 ,P_SUPPLY_PLAN_FLAG
122 ,P_SUPPLY_PLAN_NAME ,
123 f_ludate ,
124 f_luby ,
125 f_ludate ,
126 f_luby ,
127 0
128 );
129 end;
130
131
132 END;
133
134 END msd_dp_formula_parameter_pkg ;