PACKAGE BODY: APPS.AMS_PROD_TEMP_RESP_PKG

Source

1 package body AMS_PROD_TEMP_RESP_PKG as
2 /* $Header: amstptrb.pls 115.4 2003/03/11 00:26:01 mukumar noship $ */
3 
4 
5 procedure  LOAD_ROW(
6    X_TEMPL_RESPONSIBILITY_ID  IN NUMBER
7   ,X_TEMPLATE_ID              IN NUMBER
8   ,X_RESPONSIBILITY_ID        IN NUMBER
9   ,X_Owner                    IN VARCHAR2
10  ,X_CUSTOM_MODE               IN       VARCHAR2
11 ) is
12 
13 l_user_id   number := 0;
14 l_obj_verno  number;
15 l_dummy_char  varchar2(1);
16 l_row_id    varchar2(100);
17 L_TEMPL_RESPONSIBILITY_ID   number;
18 l_db_luby_id number;
19 
20 cursor  c_obj_verno is
21   select object_version_number
22   from    AMS_TEMPL_RESPONSIBILITY
23   where  TEMPL_RESPONSIBILITY_ID =  X_TEMPL_RESPONSIBILITY_ID;
24 
25 cursor c_chk_temp_resp_exists is
26   select 'x'
27   from   AMS_TEMPL_RESPONSIBILITY
28   where  TEMPL_RESPONSIBILITY_ID =  X_TEMPL_RESPONSIBILITY_ID;
29 
30 cursor c_get_temp_resp_id is
31    select AMS_TEMPL_RESPONSIBILITY_S.nextval
32    from dual;
33 
34 cursor  c_db_data_details is
35   select last_updated_by, nvl(object_version_number,1)
36   from    AMS_TEMPL_RESPONSIBILITY
37   where  TEMPL_RESPONSIBILITY_ID =  X_TEMPL_RESPONSIBILITY_ID;
38 
39 BEGIN
40 
41   if X_OWNER = 'SEED' then
42     l_user_id := 1;
43   elsif X_OWNER = 'ORACLE' THEN
44     l_user_id := 2;
45   elsif X_OWNER = 'SYSADMIN' THEN
46     l_user_id := 0;
47   end if ;
48 
49  open c_chk_temp_resp_exists;
50  fetch c_chk_temp_resp_exists into l_dummy_char;
51 
52  if c_chk_temp_resp_exists%notfound
53  then
54     close c_chk_temp_resp_exists;
55     if X_TEMPL_RESPONSIBILITY_ID is null
56     then
57       open  c_get_temp_resp_id;
58       fetch c_get_temp_resp_id into L_TEMPL_RESPONSIBILITY_ID;
59       close c_get_temp_resp_id;
60     else
61        L_TEMPL_RESPONSIBILITY_ID := X_TEMPL_RESPONSIBILITY_ID;
62     end if;
63 
64     l_obj_verno := 1;
65 
66 
67     INSERT INTO AMS_TEMPL_RESPONSIBILITY(
68            TEMPL_RESPONSIBILITY_ID,
69            template_id,
70            RESPONSIBILITY_ID,
71            last_update_date,
72            last_updated_by,
73            creation_date,
74            created_by,
75            object_version_number,
76            last_update_login
77      ) VALUES (
78             L_TEMPL_RESPONSIBILITY_ID
79             ,x_template_id
80 	    ,x_RESPONSIBILITY_ID
81            ,SYSDATE
82            ,l_user_id
83            ,SYSDATE
84            ,l_user_id
85            ,1
86            ,0);
87 
88 
89   else
90      close c_chk_temp_resp_exists;
91    open c_db_data_details;
92    fetch c_db_data_details into l_db_luby_id, l_obj_verno;
93    close c_db_data_details;
94    if (l_db_luby_id IN (1,2,0)
95       OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
96          Update AMS_TEMPL_RESPONSIBILITY
97          SET  last_update_date = sysdate,
98            last_updated_by = l_user_id,
99            object_version_number = l_obj_verno +1,
100            last_update_login = 0,
101 	   template_id = x_template_id,
102            RESPONSIBILITY_ID = x_RESPONSIBILITY_ID
103         WHERE TEMPL_RESPONSIBILITY_ID =  X_TEMPL_RESPONSIBILITY_ID
104         AND   object_version_number = l_obj_verno;
105    end if;
106 end if;
107 
108 END LOAD_ROW;
109 
110 end AMS_PROD_TEMP_RESP_PKG;