PACKAGE BODY: APPS.PA_DIST_LIST_ITEMS_PKG

Source

1 PACKAGE BODY PA_DIST_LIST_ITEMS_PKG AS
2  /* $Header: PATDLIHB.pls 120.2.12010000.2 2008/09/12 00:41:38 skkoppul ship $ */
3 procedure INSERT_ROW (
4   P_LIST_ITEM_ID 	in OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
5   P_LIST_ID 		in NUMBER,
6   P_RECIPIENT_TYPE 	in VARCHAR2,
7   P_RECIPIENT_ID 	in VARCHAR2,
8   P_ACCESS_LEVEL 	in NUMBER,
9   P_MENU_ID 		in NUMBER,
10   P_EMAIL           in VARCHAR2,
11   P_RECORD_VERSION_NUMBER in NUMBER,
12   P_CREATED_BY 		in NUMBER,
13   P_CREATION_DATE 	in DATE,
14   P_LAST_UPDATED_BY 	in NUMBER,
15   P_LAST_UPDATE_DATE 	in DATE,
16   P_LAST_UPDATE_LOGIN 	in NUMBER
17 )  IS
18       CURSOR  c1 IS
19       SELECT rowid
20         FROM   PA_DIST_LIST_ITEMS
21         WHERE  list_item_id = p_list_item_id;
22   l_row_id  ROWID;
23   l_list_item_id	NUMBER; -- Bug 4565156. For Manual NOCOPY Fix.
24  BEGIN
25   l_list_item_id := P_LIST_ITEM_ID; -- Bug 4565156. Storing original value.
26 
27   Insert into PA_DIST_LIST_ITEMS (
28       LIST_ITEM_ID        ,
29       LIST_ID             ,
30       RECIPIENT_TYPE      ,
31       RECIPIENT_ID        ,
32       ACCESS_LEVEL        ,
33       MENU_ID             ,
34       EMAIL               ,
35       RECORD_VERSION_NUMBER ,
36       CREATED_BY          ,
37       CREATION_DATE       ,
38       LAST_UPDATED_BY     ,
39       LAST_UPDATE_DATE    ,
40       LAST_UPDATE_LOGIN
41   ) VALUES
42   (  PA_DIST_LIST_ITEMS_S.NEXTVAL        ,
43      P_LIST_ID             ,
44      P_RECIPIENT_TYPE      ,
45      P_RECIPIENT_ID        ,
46      P_ACCESS_LEVEL        ,
47      P_MENU_ID             ,
48      P_EMAIL               ,
49      1 ,
50      P_CREATED_BY          ,
51      P_CREATION_DATE       ,
52      P_LAST_UPDATED_BY     ,
53      P_LAST_UPDATE_DATE    ,
54      P_LAST_UPDATE_LOGIN
55   ) returning list_item_id INTO p_list_item_id;
56   OPEN c1;
57   FETCH c1 INTO l_row_id;
58   IF (c1%NOTFOUND) THEN
59     CLOSE c1;
60     P_LIST_ITEM_ID := l_list_item_id; -- Bug 4565156. Restoring original value.
61     RAISE NO_DATA_FOUND;
62   END IF;
63   CLOSE c1;
64 
65   EXCEPTION
66     WHEN OTHERS THEN -- catch the exceptions here
67         RAISE;
68   END INSERT_ROW;
69 
70 
71 procedure UPDATE_ROW (
72   P_LIST_ITEM_ID        in NUMBER,
73   P_LIST_ID             in NUMBER,
74   P_RECIPIENT_TYPE      in VARCHAR2,
75   P_RECIPIENT_ID        in VARCHAR2,
76   P_ACCESS_LEVEL        in NUMBER,
77   P_MENU_ID             in NUMBER,
78   P_EMAIL               in VARCHAR2,
79   P_RECORD_VERSION_NUMBER in NUMBER,
80   P_LAST_UPDATED_BY     in NUMBER,
81   P_LAST_UPDATE_DATE    in DATE,
82   P_LAST_UPDATE_LOGIN   in NUMBER
83 ) IS
84  BEGIN
85    UPDATE PA_DIST_LIST_ITEMS
86    SET
87       LIST_ID 		    = P_LIST_ID             ,
88       RECIPIENT_TYPE 	    = P_RECIPIENT_TYPE      ,
89       RECIPIENT_ID 	    = P_RECIPIENT_ID        ,
90       ACCESS_LEVEL 	    = P_ACCESS_LEVEL        ,
91       MENU_ID 		    = P_MENU_ID             ,
92       EMAIL                 = NVL(P_EMAIL,EMAIL)    ,
93       RECORD_VERSION_NUMBER = RECORD_VERSION_NUMBER + 1,
94       LAST_UPDATED_BY       = P_LAST_UPDATED_BY     ,
95       LAST_UPDATE_DATE      = P_LAST_UPDATE_DATE    ,
96       LAST_UPDATE_LOGIN     = P_LAST_UPDATE_LOGIN
97    WHERE LIST_ITEM_ID 	    = P_LIST_ITEM_ID;
98  EXCEPTION
99     WHEN OTHERS THEN -- catch the exceptins here
100         RAISE;
101  END UPDATE_ROW;
102 
103 
104 
105 procedure DELETE_ROW (
106 		      P_LIST_ITEM_ID in NUMBER )
107  IS
108  BEGIN
109    DELETE FROM PA_DIST_LIST_ITEMS
110    WHERE LIST_ITEM_ID = P_LIST_ITEM_ID;
111 
112  EXCEPTION
113     WHEN OTHERS THEN
114         RAISE;
115  END DELETE_ROW;
116 
117 END  PA_DIST_LIST_ITEMS_PKG;