PACKAGE BODY: APPS.MSD_DP_DOC_DIMENSION_PKG

Source

1 PACKAGE BODY msd_dp_doc_dimension_pkg AS
2 /* $Header: msddpsddb.pls 120.0 2005/05/25 20:11:20 appldev noship $ */
3 
4 /* Public Procedures */
5 
6 PROCEDURE LOAD_ROW(P_DEMAND_PLAN_NAME in varchar2
7          ,P_DOCUMENT_NAME	in varchar2
8          ,P_DIMENSION_CODE	in varchar2
9          ,P_OWNER             in varchar2
10          ,P_SEQUENCE_NUMBER    in varchar2
11          ,P_AXIS               in varchar2
12          ,P_HIERARCHY_ID       in varchar2
13          ,P_SELECTION_TYPE     in varchar2
14          ,P_SELECTION_SCRIPT   in varchar2
15          ,P_ENABLED_FLAG       in varchar2
16          ,P_MANDATORY_FLAG     in varchar2
17 	 ,P_LAST_UPDATE_DATE  in varchar2
18          ,P_CUSTOM_MODE  in varchar2
19          )
20 is
21 
22 l_demand_plan_id number;
23 l_document_id number;
24 f_luby    number;  -- entity owner in file
25 f_ludate  date;    -- entity update date in file
26 db_luby   number;  -- entity owner in db
27 db_ludate date;    -- entity update date in db
28 
29 
30 BEGIN
31 
32 
33         -- translate values to IDs
34         select DEMAND_PLAN_ID
35         into l_demand_plan_id
36         from MSD_DEMAND_PLANS
37         where DEMAND_PLAN_NAME = P_DEMAND_PLAN_NAME;
38 
39 	select DOCUMENT_ID
40 	into l_document_id
41 	from MSD_DP_SEEDED_DOCUMENTS
42 	where DEMAND_PLAN_ID = l_demand_plan_id
43 	and DOCUMENT_NAME = P_DOCUMENT_NAME;
44 
45 
46 
47         -- Translate owner to file_last_updated_by
48         f_luby := fnd_load_util.owner_id(P_OWNER);
49 
50         -- Translate char last_update_date to date
51         f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
52         begin
53           select LAST_UPDATED_BY, LAST_UPDATE_DATE
54           into db_luby, db_ludate
55           from msd_dp_seeded_doc_dimensions
56           where DEMAND_PLAN_ID = l_demand_plan_id
57           and DOCUMENT_ID = l_document_id
58 	  and DIMENSION_CODE = P_DIMENSION_CODE;
59 
60 	  -- Test for customization and version
61           if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
62                                         db_ludate, P_CUSTOM_MODE)) then
63 	    -- Update existing row
64             update msd_dp_seeded_doc_dimensions
65 	    set  sequence_number = p_sequence_number
66             ,axis = p_axis
67             ,hierarchy_id = p_hierarchy_id
68             ,selection_type = p_selection_type
69             ,selection_script = p_selection_script
70             ,enabled_flag = p_enabled_flag
71             ,mandatory_flag = p_mandatory_flag    ,
72 	    last_update_date = f_ludate            ,
73 	    last_updated_by = f_luby             ,
74 	    last_update_login = 0
75             where demand_plan_id = l_demand_plan_id
76             and document_id = l_document_id
77    	    and dimension_code = p_dimension_code;
78 
79           end if;
80         exception
81           when no_data_found then
82             -- Record doesn't exist - insert in all cases
83             insert into msd_dp_seeded_doc_dimensions
84             (DEMAND_PLAN_ID
85             ,DOCUMENT_ID
86             ,DIMENSION_CODE
87             ,SEQUENCE_NUMBER
88             ,AXIS
89             ,HIERARCHY_ID
90             ,SELECTION_TYPE
91             ,SELECTION_SCRIPT
92             ,ENABLED_FLAG
93             ,MANDATORY_FLAG     ,
94             CREATION_DATE	       ,
95             CREATED_BY                  ,
96             LAST_UPDATE_DATE            ,
97             LAST_UPDATED_BY             ,
98             LAST_UPDATE_LOGIN
99             )
100             values
101             (l_demand_plan_id
102             ,l_document_id
103             ,P_DIMENSION_CODE
104             ,P_SEQUENCE_NUMBER
105             ,P_AXIS
106             ,P_HIERARCHY_ID
107             ,P_SELECTION_TYPE
108             ,P_SELECTION_SCRIPT
109             ,P_ENABLED_FLAG
110             ,P_MANDATORY_FLAG     ,
111             f_ludate	       ,
112 	    f_luby                  ,
113 	    f_ludate            ,
114 	    f_luby             ,
115 	    0
116 	    );
117         end;
118 
119 END;
120 
121 END msd_dp_doc_dimension_pkg ;