PACKAGE BODY: APPS.MSD_PLAN_TYPE_VIEWS_PKG

Source

1 package body MSD_PLAN_TYPE_VIEWS_PKG as
2 /* $Header: msdptvpb.pls 120.0 2005/05/25 18:42:07 appldev noship $ */
3 
4 PROCEDURE LOAD_ROW(
5           X_plan_type    varchar2,
6           X_view_type  varchar2,
7           X_view_name     varchar2,
8           x_last_update_date in varchar2,
9 	  x_lob_flag in varchar2,
10           x_owner in varchar2,
11           x_custom_mode in varchar2) IS
12 
13         f_luby    number;  -- entity owner in file
14         f_ludate  date;    -- entity update date in file
15         db_luby   number;  -- entity owner in db
16         db_ludate date;    -- entity update date in db
17     begin
18          -- Translate owner to file_last_updated_by
19          if (x_owner = 'SEED') then
20            f_luby := 1;
21          else
22            f_luby := 0;
23          end if;
24 
25          -- Translate char last_update_date to date
26          f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
27 
28         begin
29           select LAST_UPDATED_BY, LAST_UPDATE_DATE
30           into db_luby, db_ludate
31           from MSD_PLAN_TYPE_VIEWS
32           where PLAN_TYPE = x_plan_type
33             and VIEW_TYPE = x_view_type
34             --and VIEW_NAME = x_view_name
35             and nvl(LOB_FLAG,'-1') = nvl( x_lob_flag,'-1') ;
36 
37           -- Update record, honoring customization mode.
38           -- Record should be updated only if:
39           -- a. CUSTOM_MODE = FORCE, or
40           -- b. file owner is CUSTOM, db owner is SEED
41           -- c. owners are the same, and file_date > db_date
42           if ((x_custom_mode = 'FORCE') or
43               ((f_luby = 0) and (db_luby = 1)) or
44               ((f_luby = db_luby) and (f_ludate > db_ludate)))
45           then
46             update MSD_PLAN_TYPE_VIEWS set
47              PLAN_TYPE = x_plan_type,
48              VIEW_TYPE = x_view_type,
49              VIEW_NAME = x_view_name,
50              LOB_FLAG = x_lob_flag,
51              LAST_UPDATE_DATE = f_ludate,
52              LAST_UPDATED_BY = f_luby,
53              LAST_UPDATE_LOGIN = 0
54             where PLAN_TYPE = x_plan_type
55               and VIEW_TYPE = x_view_type
56               --and VIEW_NAME = x_view_name
57                and nvl(LOB_FLAG,-1) = nvl( x_lob_flag,-1) ;
58           end if;
59         exception
60           when no_data_found then
61             -- Record doesn't exist - insert in all cases
62             insert into MSD_PLAN_TYPE_VIEWS (
63               PLAN_TYPE,
64               VIEW_TYPE,
65               VIEW_NAME,
66                LOB_FLAG,
67               CREATION_DATE,
68               CREATED_BY,
69               LAST_UPDATE_DATE,
70               LAST_UPDATED_BY,
71               LAST_UPDATE_LOGIN
72             ) values (
73               x_plan_type,
74               x_view_type,
75               x_view_name,
76                x_lob_flag,
77               f_ludate,
78               f_luby,
79               f_ludate,
80               f_luby,
81               0);
82         end;
83      end LOAD_ROW;
84 
85 
86 end MSD_PLAN_TYPE_VIEWS_PKG;