PACKAGE BODY: APPS.MSD_DP_CALENDAR_PKG

Source

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