[Home] [Help]
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 ;