[Home] [Help]
PACKAGE BODY: APPS.AS_ISSUE_RELATIONSHIP_PKG
Source
1 PACKAGE BODY AS_ISSUE_RELATIONSHIP_PKG AS
2 /* $Header: asxifirb.pls 115.5 2002/11/06 00:41:41 appldev ship $ */
3 -- Start of Comments
4 -- Package name : AS_ISSUE_RELATIONSHIP_PKG
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AS_ISSUE_RELATIONSHIP_PKG';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxifirb.pls';
7 PROCEDURE insert_row( p_row_id IN OUT VARCHAR2,
8 p_issue_relationship_id IN OUT NUMBER,
9 p_issue_relationship_type IN VARCHAR2,
10 p_subject_id IN NUMBER,
11 p_object_id IN NUMBER,
12 p_start_date_active IN DATE,
13 p_end_date_active IN DATE,
14 p_directional_flag IN VARCHAR2,
15 p_last_update_date IN DATE,
16 p_last_updated_by IN NUMBER,
17 p_creation_date IN DATE,
18 p_created_by IN NUMBER,
19 p_last_update_login IN NUMBER,
20 p_attribute_category IN VARCHAR2,
21 p_attribute1 IN VARCHAR2,
22 p_attribute2 IN VARCHAR2,
23 p_attribute3 IN VARCHAR2,
24 p_attribute4 IN VARCHAR2,
25 p_attribute5 IN VARCHAR2,
26 p_attribute6 IN VARCHAR2,
27 p_attribute7 IN VARCHAR2,
28 p_attribute8 IN VARCHAR2,
29 p_attribute9 IN VARCHAR2,
30 p_attribute10 IN VARCHAR2,
31 p_attribute11 IN VARCHAR2,
32 p_attribute12 IN VARCHAR2,
33 p_attribute13 IN VARCHAR2,
34 p_attribute14 IN VARCHAR2,
35 p_attribute15 IN VARCHAR2) IS
36 CURSOR C1 IS SELECT AS_ISSUE_RELATIONSHIPS_S.nextval FROM sys.dual;
37 CURSOR C2 IS SELECT ROWID FROM as_issue_relationships
38 WHERE issue_relationship_id = p_issue_relationship_id;
39 BEGIN
40 IF (p_issue_relationship_id IS NULL) OR (p_issue_relationship_id = FND_API.G_MISS_NUM) THEN
41 OPEN C1;
42 FETCH C1 INTO p_issue_relationship_id;
43 CLOSE C1;
44 END IF;
45 INSERT INTO AS_ISSUE_RELATIONSHIPS ( issue_relationship_id ,
46 subject_id,
47 object_id,
48 issue_relationship_type,
49 directional_flag,
50 start_date_active,
51 end_date_active,
52 last_update_date,
53 last_updated_by ,
54 creation_date,
55 created_by,
56 last_update_login,
57 attribute_category ,
58 attribute1 ,
59 attribute2 ,
60 attribute3 ,
61 attribute4,
62 attribute5,
63 attribute6 ,
64 attribute7,
65 attribute8 ,
66 attribute9,
67 attribute10,
68 attribute11,
69 attribute12,
70 attribute13,
71 attribute14 ,
72 attribute15 ) VALUES
73 (DECODE(p_issue_relationship_id, FND_API.G_MISS_NUM, NULL, p_issue_relationship_id),
74 DECODE(p_subject_id, FND_API.G_MISS_NUM, NULL, p_subject_id),
75 DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id),
76 DECODE(p_issue_relationship_type, FND_API.G_MISS_CHAR, NULL, p_issue_relationship_type),
77 DECODE(p_directional_flag, FND_API.G_MISS_CHAR, NULL, p_directional_flag),
78 DECODE(p_start_date_active, FND_API.G_MISS_DATE, NULL, p_start_date_active),
79 DECODE(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
80 DECODE(p_last_update_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_last_update_date),
81 DECODE(p_last_updated_by, FND_API.G_MISS_NUM, NULL, p_last_updated_by),
82 DECODE(p_creation_date, FND_API.G_MISS_DATE, TO_DATE(NULL), p_creation_date),
83 DECODE(p_created_by, FND_API.G_MISS_NUM, NULL, p_created_by),
84 DECODE(p_last_update_login, FND_API.G_MISS_NUM, NULL, p_last_update_login),
85 DECODE(p_attribute_category, FND_API.G_MISS_CHAR, NULL, p_attribute_category),
86 DECODE(p_attribute1, FND_API.G_MISS_CHAR, NULL, p_attribute1),
87 DECODE(p_attribute2, FND_API.G_MISS_CHAR, NULL, p_attribute2),
88 DECODE(p_attribute3, FND_API.G_MISS_CHAR, NULL, p_attribute3),
89 DECODE(p_attribute4, FND_API.G_MISS_CHAR, NULL, p_attribute4),
90 DECODE(p_attribute5, FND_API.G_MISS_CHAR, NULL, p_attribute5),
91 DECODE(p_attribute6, FND_API.G_MISS_CHAR, NULL, p_attribute6),
92 DECODE(p_attribute7, FND_API.G_MISS_CHAR, NULL, p_attribute7),
93 DECODE(p_attribute8, FND_API.G_MISS_CHAR, NULL, p_attribute8),
94 DECODE(p_attribute9, FND_API.G_MISS_CHAR, NULL, p_attribute9),
95 DECODE(p_attribute10, FND_API.G_MISS_CHAR, NULL, p_attribute10),
96 DECODE(p_attribute11, FND_API.G_MISS_CHAR, NULL, p_attribute11),
97 DECODE(p_attribute12, FND_API.G_MISS_CHAR, NULL, p_attribute12),
98 DECODE(p_attribute13, FND_API.G_MISS_CHAR, NULL, p_attribute13),
99 DECODE(p_attribute14, FND_API.G_MISS_CHAR, NULL, p_attribute14),
100 DECODE(p_attribute15, FND_API.G_MISS_CHAR, NULL, p_attribute15));
101
102 OPEN c2;
103 FETCH c2 INTO p_row_id;
104 IF (c2%NOTFOUND) THEN
105 CLOSE c2;
106 RAISE no_data_found;
107 END IF;
108 CLOSE c2;
109 END INSERT_ROW;
110
111 PROCEDURE update_row ( p_issue_relationship_id IN NUMBER,
112 p_issue_relationship_type IN VARCHAR2,
113 p_subject_id IN NUMBER,
114 p_object_id IN NUMBER,
115 p_start_date_active IN DATE,
116 p_end_date_active IN DATE,
117 p_directional_flag IN VARCHAR2,
118 p_last_update_date IN DATE,
119 p_last_updated_by IN NUMBER,
120 p_creation_date IN DATE,
121 p_created_by IN NUMBER,
122 p_last_update_login IN NUMBER,
123 p_attribute_category IN VARCHAR2,
124 p_attribute1 IN VARCHAR2,
125 p_attribute2 IN VARCHAR2,
126 p_attribute3 IN VARCHAR2,
127 p_attribute4 IN VARCHAR2,
128 p_attribute5 IN VARCHAR2,
129 p_attribute6 IN VARCHAR2,
130 p_attribute7 IN VARCHAR2,
131 p_attribute8 IN VARCHAR2,
132 p_attribute9 IN VARCHAR2,
133 p_attribute10 IN VARCHAR2,
134 p_attribute11 IN VARCHAR2,
135 p_attribute12 IN VARCHAR2,
136 p_attribute13 IN VARCHAR2,
137 p_attribute14 IN VARCHAR2,
138 p_attribute15 IN VARCHAR2) IS
139 BEGIN
140 UPDATE AS_ISSUE_RELATIONSHIPS SET
141 SUBJECT_ID = DECODE(p_subject_id, FND_API.G_MISS_NUM, SUBJECT_ID, p_subject_id),
142 OBJECT_ID = DECODE(p_object_id, FND_API.G_MISS_NUM, OBJECT_ID, p_object_id),
143 ISSUE_RELATIONSHIP_TYPE = DECODE(p_issue_relationship_type, FND_API.G_MISS_CHAR, ISSUE_RELATIONSHIP_TYPE, p_issue_relationship_type),
144 DIRECTIONAL_FLAG = DECODE(p_directional_flag, FND_API.G_MISS_CHAR, DIRECTIONAL_FLAG, p_directional_flag),
145 START_DATE_ACTIVE = DECODE(p_start_date_active, FND_API.G_MISS_DATE, START_DATE_ACTIVE, p_start_date_active),
146 END_DATE_ACTIVE = DECODE(p_end_date_active, FND_API.G_MISS_DATE, END_DATE_ACTIVE, p_end_date_active),
147 ATTRIBUTE1 = DECODE(P_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, P_ATTRIBUTE1),
148 ATTRIBUTE2 = DECODE(P_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, P_ATTRIBUTE2),
149 ATTRIBUTE3 = DECODE(P_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, P_ATTRIBUTE3),
150 ATTRIBUTE4 = DECODE(P_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, P_ATTRIBUTE4),
151 ATTRIBUTE5 = DECODE(P_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, P_ATTRIBUTE5),
152 ATTRIBUTE6 = DECODE(P_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, P_ATTRIBUTE6),
153 ATTRIBUTE7 = DECODE(P_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, P_ATTRIBUTE7),
154 ATTRIBUTE8 = DECODE(P_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, P_ATTRIBUTE8),
155 ATTRIBUTE9 = DECODE(P_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, P_ATTRIBUTE9),
156 ATTRIBUTE10 = DECODE(P_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, P_ATTRIBUTE10),
157 ATTRIBUTE11 = DECODE(P_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, P_ATTRIBUTE11),
158 ATTRIBUTE12 = DECODE(P_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, P_ATTRIBUTE12),
159 ATTRIBUTE13 = DECODE(P_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, P_ATTRIBUTE13),
160 ATTRIBUTE14 = DECODE(P_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, P_ATTRIBUTE14),
161 ATTRIBUTE15 = DECODE(P_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, P_ATTRIBUTE15),
162 ATTRIBUTE_CATEGORY = DECODE(P_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, P_ATTRIBUTE_CATEGORY),
163 LAST_UPDATE_DATE = DECODE(P_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, P_LAST_UPDATE_DATE),
164 LAST_UPDATED_BY = DECODE(P_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, P_LAST_UPDATED_BY),
165 CREATION_DATE = DECODE(P_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, P_CREATION_DATE),
166 CREATED_BY = DECODE(P_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, P_CREATED_BY),
167 LAST_UPDATE_LOGIN = DECODE(P_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, P_LAST_UPDATE_LOGIN)
168
169 WHERE ISSUE_RELATIONSHIP_ID = P_ISSUE_RELATIONSHIP_ID ;
170 IF (SQL%NOTFOUND) THEN
171 RAISE NO_DATA_FOUND;
172 END IF;
173 END UPDATE_ROW;
174
175 PROCEDURE DELETE_ROW (p_issue_relationship_id IN NUMBER ) IS
176 BEGIN
177 DELETE FROM AS_ISSUE_RELATIONSHIPS
178 WHERE ISSUE_RELATIONSHIP_ID = P_ISSUE_RELATIONSHIP_ID;
179 IF (SQL%NOTFOUND) THEN
180 RAISE NO_DATA_FOUND;
181 END IF;
182 END DELETE_ROW;
183 END AS_ISSUE_RELATIONSHIP_PKG;