[Home] [Help]
PACKAGE BODY: APPS.AMS_PROD_TEMPLATE_ATTR_PKG
Source
1 package body AMS_PROD_TEMPLATE_ATTR_PKG as
2 /* $Header: amstptab.pls 115.5 2003/03/11 00:25:59 mukumar ship $ */
3
4
5 procedure LOAD_ROW(
6 X_template_attribute_id IN NUMBER
7 ,X_template_id IN NUMBER
8 ,X_parent_attribute_code IN VARCHAR2
9 ,X_parent_select_all IN VARCHAR2
10 ,X_attribute_code IN VARCHAR2
11 ,X_default_flag IN VARCHAR2
12 ,X_editable_flag IN VARCHAR2
13 ,X_hide_flag IN VARCHAR2
14 ,X_Owner IN VARCHAR2
15 ,X_CUSTOM_MODE IN VARCHAR2
16 ) is
17
18 l_user_id number := 0;
19 l_obj_verno number;
20 l_dummy_char varchar2(1);
21 l_row_id varchar2(100);
22 l_template_attribute_id number;
23 l_db_luby_id number;
24
25
26 cursor c_obj_verno is
27 select object_version_number
28 from ams_prod_template_attr
29 where template_attribute_id = X_TEMPLATE_attribute_ID;
30
31 cursor c_chk_temp_attr_exists is
32 select 'x'
33 from ams_prod_template_attr
34 where template_attribute_id = X_TEMPLATE_attribute_ID;
35
36 cursor c_get_temp_attr_id is
37 select ams_prod_template_attr_S.nextval
38 from dual;
39
40 cursor c_db_data_details is
41 select last_updated_by, nvl(object_version_number,1)
42 from ams_prod_template_attr
43 where template_attribute_id = X_TEMPLATE_attribute_ID;
44
45
46 BEGIN
47
48 -- set the last_updated_by to be used while updating the data in customer data.
49 if X_OWNER = 'SEED' then
50 l_user_id := 1;
51 elsif X_OWNER = 'ORACLE' THEN
52 l_user_id := 2;
53 elsif X_OWNER = 'SYSADMIN' THEN
54 l_user_id := 0;
55 end if ;
56
57 open c_chk_temp_attr_exists;
58 fetch c_chk_temp_attr_exists into l_dummy_char;
59
60 if c_chk_temp_attr_exists%notfound
61 then
62 close c_chk_temp_attr_exists;
63 if X_TEMPLATE_attribute_ID is null
64 then
65 open c_get_temp_attr_id;
66 fetch c_get_temp_attr_id into l_template_attribute_id;
67 close c_get_temp_attr_id;
68 else
69 l_template_attribute_id := X_TEMPLATE_attribute_ID;
70 end if;
71
72 l_obj_verno := 1;
73
74 INSERT INTO ams_prod_template_attr(
75 TEMPLATE_ATTRIBUTE_ID,
76 template_id,
77 last_update_date,
78 last_updated_by,
79 creation_date,
80 created_by,
81 object_version_number,
82 last_update_login,
83 PARENT_ATTRIBUTE_CODE,
84 PARENT_SELECT_ALL ,
85 ATTRIBUTE_CODE ,
86 DEFAULT_FLAG ,
87 EDITABLE_FLAG ,
88 HIDE_FLAG
89 ) VALUES (
90 l_template_attribute_id
91 ,x_template_id
92 ,SYSDATE
93 ,l_user_id
94 ,SYSDATE
95 ,l_user_id
96 ,1
97 ,0
98 ,x_PARENT_ATTRIBUTE_CODE
99 ,x_PARENT_SELECT_ALL
100 ,x_ATTRIBUTE_CODE
101 ,x_DEFAULT_FLAG
102 ,x_EDITABLE_FLAG
103 ,x_HIDE_FLAG );
104
105
106 else
107 close c_chk_temp_attr_exists;
108 /* open c_obj_verno;
109 fetch c_obj_verno into l_obj_verno;
110 close c_obj_verno;
111 */
112 open c_db_data_details;
113 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
114 close c_db_data_details;
115 if (l_db_luby_id IN (1,2,0)
116 OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
117 Update ams_prod_template_attr
118 SET last_update_date = sysdate,
119 last_updated_by = l_user_id,
120 object_version_number = l_obj_verno +1,
121 last_update_login = 0,
122 PARENT_ATTRIBUTE_CODE = x_PARENT_ATTRIBUTE_CODE ,
123 PARENT_SELECT_ALL = x_PARENT_SELECT_ALL,
124 ATTRIBUTE_CODE = x_ATTRIBUTE_CODE ,
125 DEFAULT_FLAG = x_DEFAULT_FLAG ,
126 EDITABLE_FLAG = x_EDITABLE_FLAG ,
127 HIDE_FLAG = x_HIDE_FLAG
128 WHERE TEMPLATE_attribute_ID = X_template_attribute_id
129 AND object_version_number = l_obj_verno;
130 end if;
131 end if;
132
133 END LOAD_ROW;
134
135 end AMS_PROD_TEMPLATE_ATTR_PKG;