PACKAGE BODY: APPS.MSD_DP_HIERARCHY_PKG

Source

1 PACKAGE BODY msd_dp_hierarchy_pkg AS
2 /* $Header: msddphrb.pls 120.0 2005/05/25 19:57:37 appldev noship $ */
3 
4 /* Public Procedures */
5 
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME          in varchar2,
7                    P_DP_DIMENSION_CODE         in varchar2,
8                    P_HIERARCHY_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 f_luby    number;  -- entity owner in file
19 f_ludate  date;    -- entity update date in file
20 db_luby   number;  -- entity owner in db
21 db_ludate date;    -- entity update date in db
22 
23 BEGIN
24 
25         -- translate values to IDs
26         select DEMAND_PLAN_ID
27         into l_demand_plan_id
28         from MSD_DEMAND_PLANS
29         where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
30 
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_hierarchies
43           where DEMAND_PLAN_ID = l_demand_plan_id
44           and HIERARCHY_ID = P_HIERARCHY_ID;
45 
46 	  -- Test for customization and version
47           if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
48                                         db_ludate, P_CUSTOM_MODE)) then
49 	    -- Update existing row
50             update msd_dp_hierarchies
51 	    set  last_update_date = f_ludate            ,
52             deleteable_flag = P_DELETEABLE_FLAG             ,
53             enable_nonseed_flag = P_ENABLE_NONSEED_FLAG             ,
54 	    last_updated_by = f_luby             ,
55 	    last_update_login = 0
56 	    where demand_plan_id = l_demand_plan_id
57 	    and hierarchy_id = p_hierarchy_id
58 	    and dp_dimension_code  = p_dp_dimension_code;
59 
60           end if;
61         exception
62           when no_data_found then
63             -- Record doesn't exist - insert in all cases
64             insert into msd_dp_hierarchies
65             (DEMAND_PLAN_ID            ,
66             DP_DIMENSION_CODE               ,
67             HIERARCHY_ID               ,
68             CREATION_DATE	       ,
69             CREATED_BY                  ,
70             LAST_UPDATE_DATE            ,
71             LAST_UPDATED_BY             ,
72             LAST_UPDATE_LOGIN           ,
73 	    ENABLE_NONSEED_FLAG ,
74 	    DELETEABLE_FLAG
75             )
76             values
77             (l_demand_plan_id            ,
78             P_DP_DIMENSION_CODE               ,
79             P_HIERARCHY_ID               ,
80             f_ludate	       ,
81 	    f_luby                  ,
82 	    f_ludate            ,
83 	    f_luby             ,
84 	    0           ,
85 	    P_ENABLE_NONSEED_FLAG ,
86 	    P_DELETEABLE_FLAG
87 	    );
88         end;
89 
90 END;
91 
92 END msd_dp_hierarchy_pkg ;