PACKAGE BODY: APPS.MSD_DP_DIMENSION_PKG

Source

1 PACKAGE BODY msd_dp_dimension_pkg AS
2 /* $Header: msddpdmb.pls 120.0 2005/05/25 19:02:58 appldev noship $ */
3 
4 /* Public Procedures */
5 
6 
7 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME          in VARCHAR2,
8                              P_DP_DIMENSION_CODE         in VARCHAR2,
9                              P_DIMENSION_CODE            in VARCHAR2,
10                              P_OWNER                     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 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 
33 
34         -- Translate owner to file_last_updated_by
35         f_luby := fnd_load_util.owner_id(P_OWNER);
36 
37         -- Translate char last_update_date to date
38         f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
39         begin
40           select LAST_UPDATED_BY, LAST_UPDATE_DATE
41           into db_luby, db_ludate
42           from msd_dp_dimensions
43           where DEMAND_PLAN_ID = l_demand_plan_id
44           and DP_DIMENSION_CODE = P_DP_DIMENSION_CODE
45 	  and DIMENSION_CODE = P_DIMENSION_CODE;
46 
47 	  -- Test for customization and version
48           if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
49                                         db_ludate, P_CUSTOM_MODE)) then
50 	    -- Update existing row
51             update msd_dp_dimensions
52 	    set  last_update_date = f_ludate            ,
53             deleteable_flag = P_DELETEABLE_FLAG             ,
54             enable_nonseed_flag = P_ENABLE_NONSEED_FLAG             ,
55 	    last_updated_by = f_luby             ,
56 	    last_update_login = 0
57 	    where demand_plan_id = l_demand_plan_id
58             and dp_dimension_code = p_dp_dimension_code
59      	    and dimension_code = p_dimension_code;
60 
61           end if;
62         exception
63           when no_data_found then
64             -- Record doesn't exist - insert in all cases
65             insert into msd_dp_dimensions
66             (DEMAND_PLAN_ID            ,
67             DP_DIMENSION_CODE               ,
68             DIMENSION_CODE               ,
69             CREATION_DATE	       ,
70             CREATED_BY                  ,
71             LAST_UPDATE_DATE            ,
72             LAST_UPDATED_BY             ,
73             LAST_UPDATE_LOGIN           ,
74 	    ENABLE_NONSEED_FLAG ,
75 	    DELETEABLE_FLAG
76             )
77             values
78             (l_demand_plan_id            ,
79             P_DP_DIMENSION_CODE               ,
80             P_DIMENSION_CODE               ,
81             f_ludate	       ,
82 	    f_luby                  ,
83 	    f_ludate            ,
84 	    f_luby             ,
85 	    0           ,
86 	    P_ENABLE_NONSEED_FLAG ,
87 	    P_DELETEABLE_FLAG
88 	    );
89         end;
90 
91 END;
92 
93 END msd_dp_dimension_pkg ;