[Home] [Help]
PACKAGE BODY: APPS.MSD_DP_DOC_DIM_SELECTION_PKG
Source
1 PACKAGE BODY msd_dp_doc_dim_selection_pkg AS
2 /* $Header: msddpddsb.pls 120.0 2005/05/26 01:25:35 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_SELECTION_SEQUENCE in varchar2
10 ,P_OWNER in varchar2
11 ,P_ENABLED_FLAG in varchar2
12 ,P_MANDATORY_FLAG in varchar2
13 ,P_SELECTION_TYPE in varchar2
14 ,P_SELECTION_COMPONENT in varchar2
15 ,P_SELECTION_VALUE in varchar2
16 ,P_SUPPLY_PLAN_FLAG in varchar2
17 ,P_SUPPLY_PLAN_NAME in varchar2
18 ,P_LAST_UPDATE_DATE in varchar2
19 ,P_CUSTOM_MODE in varchar2
20 )
21 is
22
23 l_demand_plan_id number;
24 l_document_id number;
25 f_luby number; -- entity owner in file
26 f_ludate date; -- entity update date in file
27 db_luby number; -- entity owner in db
28 db_ludate date; -- entity update date in db
29
30
31 BEGIN
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 -- Translate owner to file_last_updated_by
47 f_luby := fnd_load_util.owner_id(P_OWNER);
48
49 -- Translate char last_update_date to date
50 f_ludate := nvl(to_date(P_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
51 begin
52 select LAST_UPDATED_BY, LAST_UPDATE_DATE
53 into db_luby, db_ludate
54 from msd_dp_doc_dim_selections
55 where DEMAND_PLAN_ID = l_demand_plan_id
56 and DOCUMENT_ID = l_document_id
57 and DIMENSION_CODE = P_DIMENSION_CODE
58 and SELECTION_SEQUENCE = P_SELECTION_SEQUENCE;
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_doc_dim_selections
65 set enabled_flag = p_enabled_flag
66 ,mandatory_flag = p_mandatory_flag
67 ,selection_type = p_selection_type
68 ,selection_component = p_selection_component
69 ,selection_value = p_selection_value
70 ,supply_plan_flag = p_supply_plan_flag
71 ,supply_plan_name = p_supply_plan_name
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 and selection_sequence = p_selection_sequence;
79
80 end if;
81 exception
82 when no_data_found then
83 -- Record doesn't exist - insert in all cases
84 insert into msd_dp_doc_dim_selections
85 (DEMAND_PLAN_ID
86 ,DOCUMENT_ID
87 ,DIMENSION_CODE
88 ,SELECTION_SEQUENCE
89 ,ENABLED_FLAG
90 ,MANDATORY_FLAG
91 ,SELECTION_TYPE
92 ,SELECTION_COMPONENT
93 ,SELECTION_VALUE
94 ,SUPPLY_PLAN_FLAG
95 ,SUPPLY_PLAN_NAME ,
96 CREATION_DATE ,
97 CREATED_BY ,
98 LAST_UPDATE_DATE ,
99 LAST_UPDATED_BY ,
100 LAST_UPDATE_LOGIN
101 )
102 values
103 (l_demand_plan_id
104 ,l_document_id
105 ,P_DIMENSION_CODE
106 ,P_SELECTION_SEQUENCE
107 ,P_ENABLED_FLAG
108 ,P_MANDATORY_FLAG
109 ,P_SELECTION_TYPE
110 ,P_SELECTION_COMPONENT
111 ,P_SELECTION_VALUE
112 ,P_SUPPLY_PLAN_FLAG
113 ,P_SUPPLY_PLAN_NAME ,
114 f_ludate ,
115 f_luby ,
116 f_ludate ,
117 f_luby ,
118 0
119 );
120 end;
121
122 END;
123
124 END msd_dp_doc_dim_selection_pkg ;