PACKAGE BODY: APPS.PA_DIST_LIST_DENORM_PKG

Source

1 PACKAGE BODY PA_DIST_LIST_DENORM_PKG AS
2  /* $Header: PATDLDHB.pls 115.1 2002/04/09 11:33:31 pkm ship     $ */
3 procedure INSERT_ROW (
4   P_LIST_ID 		in NUMBER,
5   P_RESOURCE_TYPE_ID	in NUMBER,
6   P_RESOURCE_SOURCE_ID	in NUMBER,
7   P_ACCESS_LEVEL 	in NUMBER,
8   P_MENU_ID 		in NUMBER,
9   P_CREATED_BY 		in NUMBER,
10   P_CREATION_DATE 	in DATE,
11   P_LAST_UPDATED_BY 	in NUMBER,
12   P_LAST_UPDATE_DATE 	in DATE,
13   P_LAST_UPDATE_LOGIN 	in NUMBER
14 ) IS
15      CURSOR  c1 IS
16       SELECT rowid
17         FROM   PA_DISTRIBUTION_LISTS
18         WHERE  list_id = p_list_id;
19   l_row_id  ROWID;
20  BEGIN
21     Insert into PA_DIST_LIST_DENORM (
22   LIST_ID             ,
23   RESOURCE_TYPE_ID    ,
24   RESOURCE_SOURCE_ID  ,
25   ACCESS_LEVEL        ,
26   MENU_ID             ,
27   CREATED_BY          ,
28   CREATION_DATE       ,
29   LAST_UPDATED_BY     ,
30   LAST_UPDATE_DATE    ,
31   LAST_UPDATE_LOGIN
32   ) VALUES (
33   P_LIST_ID             ,
34   P_RESOURCE_TYPE_ID    ,
35   P_RESOURCE_SOURCE_ID  ,
36   P_ACCESS_LEVEL        ,
37   P_MENU_ID             ,
38   P_CREATED_BY          ,
39   P_CREATION_DATE       ,
40   P_LAST_UPDATED_BY     ,
41   P_LAST_UPDATE_DATE    ,
42   P_LAST_UPDATE_LOGIN
43   );
44  OPEN c1;
45   FETCH c1 INTO l_row_id;
46   IF (c1%NOTFOUND) THEN
47     CLOSE c1;
48     RAISE NO_DATA_FOUND;
49   END IF;
50   CLOSE c1;
51 
52 EXCEPTION
53     WHEN OTHERS THEN -- catch the exceptions here
54         RAISE;
55 END INSERT_ROW;
56 
57 procedure UPDATE_ROW (
58   P_LIST_ID             in NUMBER,
59   P_RESOURCE_TYPE_ID    in NUMBER,
60   P_RESOURCE_SOURCE_ID  in NUMBER,
61   P_ACCESS_LEVEL        in NUMBER,
62   P_MENU_ID             in NUMBER,
63   P_LAST_UPDATED_BY     in NUMBER,
64   P_LAST_UPDATE_DATE    in DATE,
65   P_LAST_UPDATE_LOGIN   in NUMBER
66 ) IS
67  BEGIN
68    UPDATE PA_DIST_LIST_DENORM
69    SET
70       ACCESS_LEVEL 	    = P_ACCESS_LEVEL,
71       MENU_ID               = P_MENU_ID,
72       LAST_UPDATED_BY       = P_LAST_UPDATED_BY,
73       LAST_UPDATE_DATE      = P_LAST_UPDATE_DATE,
74       LAST_UPDATE_LOGIN     = P_LAST_UPDATE_LOGIN
75    WHERE LIST_ID = P_LIST_ID
76    AND RESOURCE_TYPE_ID = P_RESOURCE_TYPE_ID
77    AND RESOURCE_SOURCE_ID = P_RESOURCE_SOURCE_ID;
78  EXCEPTION
79     WHEN OTHERS THEN -- catch the exceptins here
80         RAISE;
81  END UPDATE_ROW;
82 
83 
84 procedure DELETE_ROW (
85   P_LIST_ID             in NUMBER,
86   P_RESOURCE_TYPE_ID    in NUMBER,
87   P_RESOURCE_SOURCE_ID  in NUMBER
88 )
89   IS
90  BEGIN
91    DELETE FROM PA_DIST_LIST_DENORM
92    WHERE LIST_ID = P_LIST_ID
93    AND RESOURCE_TYPE_ID = P_RESOURCE_TYPE_ID
94    AND RESOURCE_SOURCE_ID = P_RESOURCE_SOURCE_ID;
95 
96  EXCEPTION
97     WHEN OTHERS THEN
98         RAISE;
99  END DELETE_ROW;
100 
101 END  PA_DIST_LIST_DENORM_PKG;