[Home] [Help]
PACKAGE BODY: APPS.MSD_DP_FORMULA_PKG
Source
1 PACKAGE BODY msd_dp_formula_pkg AS
2 /* $Header: msddpfb.pls 120.2 2005/12/22 00:02:21 amitku noship $ */
3
4 /* Public Procedures */
5
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in varchar2
7 ,P_FORMULA_NAME in varchar2
8 ,P_OWNER in varchar2
9 ,P_CREATION_SEQUENCE in varchar2
10 ,P_FORMULA_DESC in varchar2
11 ,P_CUSTOM_TYPE in varchar2
12 ,P_EQUATION in varchar2
13 ,P_CUSTOM_FIELD1 in varchar2
14 ,P_CUSTOM_FIELD2 in varchar2
15 ,P_CUSTOM_SUBTYPE in varchar2
16 ,P_CUSTOM_ADDTLCALC in varchar2
17 ,P_ISBY in varchar2
18 ,P_VALID_FLAG in varchar2
19 ,P_NUMERATOR in varchar2
20 ,P_DENOMINATOR in varchar2
21 ,P_SUPPLY_PLAN_FLAG in varchar2
22 ,P_SUPPLY_PLAN_NAME in varchar2
23 ,P_UPLOAD_FORMULA_ID in varchar2
24 ,P_LAST_UPDATE_DATE in varchar2
25 ,P_FORMAT in varchar2 /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4373422)*/
26 ,P_START_PERIOD in varchar2 /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4744717)*/
27 ,P_CUSTOM_MODE in varchar2
28 )
29 is
30
31 l_demand_plan_id number;
32 f_luby number; -- entity owner in file
33 f_ludate date; -- entity update date in file
34 db_luby number; -- entity owner in db
35 db_ludate date; -- entity update date in db
36
37
38 BEGIN
39
40 -- translate values to IDs
41 select DEMAND_PLAN_ID
42 into l_demand_plan_id
43 from MSD_DEMAND_PLANS
44 where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
45
46
47
48
49 -- Translate owner to file_last_updated_by
50 f_luby := fnd_load_util.owner_id(P_OWNER);
51
52 -- Translate char last_update_date to date
53 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
54 begin
55 select LAST_UPDATED_BY, LAST_UPDATE_DATE
56 into db_luby, db_ludate
57 from msd_dp_formulas
58 where DEMAND_PLAN_ID = l_demand_plan_id
59 and FORMULA_NAME = P_FORMULA_NAME;
60
61 -- Test for customization and version
62 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
63 db_ludate, P_CUSTOM_MODE)) then
64 -- Update existing row
65 update msd_dp_formulas
66 set CREATION_SEQUENCE = P_CREATION_SEQUENCE
67 ,FORMULA_DESC = P_FORMULA_DESC
68 ,CUSTOM_TYPE = P_CUSTOM_TYPE
69 ,EQUATION = P_EQUATION
70 ,CUSTOM_FIELD1 = P_CUSTOM_FIELD1
71 ,CUSTOM_FIELD2 = P_CUSTOM_FIELD2
72 ,CUSTOM_SUBTYPE = P_CUSTOM_SUBTYPE
73 ,CUSTOM_ADDTLCALC = P_CUSTOM_ADDTLCALC
74 ,ISBY = P_ISBY
75 ,VALID_FLAG = P_VALID_FLAG
76 ,NUMERATOR = P_NUMERATOR
77 ,DENOMINATOR = P_DENOMINATOR
78 ,SUPPLY_PLAN_FLAG = P_SUPPLY_PLAN_FLAG
79 ,SUPPLY_PLAN_NAME = P_SUPPLY_PLAN_NAME
80 ,UPLOAD_FORMULA_ID = P_UPLOAD_FORMULA_ID
81 ,last_update_date = f_ludate
82 ,format = P_FORMAT /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4373422)*/
83 ,last_updated_by = f_luby
84 ,last_update_login = 0
85 ,START_PERIOD=P_START_PERIOD /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4744717)*/
86 where demand_plan_id = l_demand_plan_id
87 and formula_name = p_formula_name;
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_formulas
94 (DEMAND_PLAN_ID
95 ,FORMULA_ID
96 ,FORMULA_NAME
97 ,CREATION_SEQUENCE
98 ,FORMULA_DESC
99 ,CUSTOM_TYPE
100 ,EQUATION
101 ,CUSTOM_FIELD1
102 ,CUSTOM_FIELD2
103 ,CUSTOM_SUBTYPE
104 ,CUSTOM_ADDTLCALC
105 ,ISBY
106 ,VALID_FLAG
107 ,NUMERATOR
108 ,DENOMINATOR
109 ,SUPPLY_PLAN_FLAG
110 ,SUPPLY_PLAN_NAME
111 ,UPLOAD_FORMULA_ID
112 ,CREATION_DATE
113 ,CREATED_BY
114 ,LAST_UPDATE_DATE
115 ,FORMAT /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4373422)*/
116 ,LAST_UPDATED_BY
117 ,LAST_UPDATE_LOGIN
118 ,START_PERIOD /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4744717)*/
119 )
120 values
121 (l_demand_plan_id
122 ,msd_dp_parameters_s.nextval
123 ,P_FORMULA_NAME
124 ,P_CREATION_SEQUENCE
125 ,P_FORMULA_DESC
126 ,P_CUSTOM_TYPE
127 ,P_EQUATION
128 ,P_CUSTOM_FIELD1
129 ,P_CUSTOM_FIELD2
130 ,P_CUSTOM_SUBTYPE
131 ,P_CUSTOM_ADDTLCALC
132 ,P_ISBY
133 ,P_VALID_FLAG
134 ,P_NUMERATOR
135 ,P_DENOMINATOR
136 ,P_SUPPLY_PLAN_FLAG
137 ,P_SUPPLY_PLAN_NAME
138 ,P_UPLOAD_FORMULA_ID
139 ,f_ludate
140 ,f_luby
141 ,f_ludate
142 ,P_FORMAT /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4373422)*/
143 ,f_luby
144 ,0
145 ,P_START_PERIOD /* Added a new coulumn in MSD_DP_FORMULAS table (Bug#4744717)*/
146
147 );
148 end;
149
150 END;
151
152 END msd_dp_formula_pkg ;