[Home] [Help]
PACKAGE BODY: APPS.HZ_HIERARCHY_NODES_PKG
Source
1 PACKAGE BODY HZ_HIERARCHY_NODES_PKG AS
2 /*$Header: ARHHINTB.pls 115.3 2003/04/15 20:35:48 cvijayan noship $ */
3
4 PROCEDURE Insert_Row (
5 X_ROWID OUT NOCOPY ROWID,
6 X_HIERARCHY_TYPE IN VARCHAR2,
7 X_PARENT_ID IN NUMBER,
8 X_PARENT_TABLE_NAME IN VARCHAR2,
9 X_PARENT_OBJECT_TYPE IN VARCHAR2,
10 X_CHILD_ID IN NUMBER,
11 X_CHILD_TABLE_NAME IN VARCHAR2,
12 X_CHILD_OBJECT_TYPE IN VARCHAR2,
13 X_LEVEL_NUMBER IN NUMBER,
14 X_TOP_PARENT_FLAG IN VARCHAR2,
15 X_LEAF_CHILD_FLAG IN VARCHAR2,
16 X_EFFECTIVE_START_DATE IN DATE,
17 X_EFFECTIVE_END_DATE IN DATE,
18 X_STATUS IN VARCHAR2,
19 X_RELATIONSHIP_ID IN NUMBER,
20 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2
21 ) IS
22
23 BEGIN
24
25 INSERT INTO HZ_HIERARCHY_NODES (
26 HIERARCHY_TYPE,
27 PARENT_ID,
28 PARENT_TABLE_NAME,
29 PARENT_OBJECT_TYPE,
30 CHILD_ID,
31 CHILD_TABLE_NAME,
32 CHILD_OBJECT_TYPE,
33 LEVEL_NUMBER,
34 TOP_PARENT_FLAG,
35 LEAF_CHILD_FLAG,
36 EFFECTIVE_START_DATE,
37 EFFECTIVE_END_DATE,
38 STATUS,
39 RELATIONSHIP_ID,
40 CREATED_BY,
41 CREATION_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATE_LOGIN,
45 ACTUAL_CONTENT_SOURCE
46 )
47 VALUES (
48 DECODE( X_HIERARCHY_TYPE, FND_API.G_MISS_CHAR, NULL, X_HIERARCHY_TYPE ),
49 DECODE( X_PARENT_ID, FND_API.G_MISS_NUM, NULL, X_PARENT_ID ),
50 DECODE( X_PARENT_TABLE_NAME, FND_API.G_MISS_CHAR, NULL, X_PARENT_TABLE_NAME ),
51 DECODE( X_PARENT_OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_PARENT_OBJECT_TYPE ),
52 DECODE( X_CHILD_ID, FND_API.G_MISS_NUM, NULL, X_CHILD_ID ),
53 DECODE( X_CHILD_TABLE_NAME, FND_API.G_MISS_CHAR, NULL, X_CHILD_TABLE_NAME ),
54 DECODE( X_CHILD_OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_CHILD_OBJECT_TYPE ),
55 DECODE( X_LEVEL_NUMBER, FND_API.G_MISS_NUM, NULL, X_LEVEL_NUMBER ),
56 DECODE( X_TOP_PARENT_FLAG, FND_API.G_MISS_CHAR, NULL, X_TOP_PARENT_FLAG ),
57 DECODE( X_LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR, NULL, X_LEAF_CHILD_FLAG ),
58 DECODE( X_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), X_EFFECTIVE_START_DATE ),
59 DECODE( X_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), X_EFFECTIVE_END_DATE ),
60 DECODE( X_STATUS, FND_API.G_MISS_CHAR, NULL, X_STATUS ),
61 DECODE( X_RELATIONSHIP_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_ID ),
62 HZ_UTILITY_V2PUB.CREATED_BY,
63 HZ_UTILITY_V2PUB.CREATION_DATE,
64 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
65 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
66 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
67 DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, 'USER_ENTERED', NULL, 'USER_ENTERED', X_ACTUAL_CONTENT_SOURCE )
68 ) RETURNING
69 ROWID
70 INTO
71 X_ROWID;
72
73 END Insert_Row;
74
75 PROCEDURE Update_Row (
76 X_Rowid IN OUT NOCOPY VARCHAR2,
77 X_HIERARCHY_TYPE IN VARCHAR2,
78 X_PARENT_ID IN NUMBER,
79 X_PARENT_TABLE_NAME IN VARCHAR2,
80 X_PARENT_OBJECT_TYPE IN VARCHAR2,
81 X_CHILD_ID IN NUMBER,
82 X_CHILD_TABLE_NAME IN VARCHAR2,
83 X_CHILD_OBJECT_TYPE IN VARCHAR2,
84 X_LEVEL_NUMBER IN NUMBER,
85 X_TOP_PARENT_FLAG IN VARCHAR2,
86 X_LEAF_CHILD_FLAG IN VARCHAR2,
87 X_EFFECTIVE_START_DATE IN DATE,
88 X_EFFECTIVE_END_DATE IN DATE,
89 X_STATUS IN VARCHAR2,
90 X_RELATIONSHIP_ID IN NUMBER,
91 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2
92 ) IS
93
94 BEGIN
95
96 UPDATE HZ_HIERARCHY_NODES SET
97 HIERARCHY_TYPE = DECODE( X_HIERARCHY_TYPE, NULL, HIERARCHY_TYPE, FND_API.G_MISS_NUM, HIERARCHY_TYPE, X_HIERARCHY_TYPE ),
98 PARENT_ID = DECODE( X_PARENT_ID, NULL, PARENT_ID, FND_API.G_MISS_NUM, PARENT_ID, X_PARENT_ID ),
99 PARENT_TABLE_NAME = DECODE( X_PARENT_TABLE_NAME, NULL, PARENT_TABLE_NAME, FND_API.G_MISS_CHAR, PARENT_TABLE_NAME, X_PARENT_TABLE_NAME ),
100 PARENT_OBJECT_TYPE = DECODE( X_PARENT_OBJECT_TYPE, NULL, PARENT_OBJECT_TYPE, FND_API.G_MISS_CHAR, PARENT_OBJECT_TYPE, X_PARENT_OBJECT_TYPE ),
101 CHILD_ID = DECODE( X_CHILD_ID, NULL, CHILD_ID, FND_API.G_MISS_NUM, CHILD_ID, X_CHILD_ID ),
102 CHILD_TABLE_NAME = DECODE( X_CHILD_TABLE_NAME, NULL, CHILD_TABLE_NAME, FND_API.G_MISS_CHAR, CHILD_TABLE_NAME, X_CHILD_TABLE_NAME ),
103 CHILD_OBJECT_TYPE = DECODE( X_CHILD_OBJECT_TYPE, NULL, CHILD_OBJECT_TYPE, FND_API.G_MISS_CHAR, CHILD_OBJECT_TYPE, X_CHILD_OBJECT_TYPE ),
104 LEVEL_NUMBER = DECODE( X_LEVEL_NUMBER, NULL, LEVEL_NUMBER, FND_API.G_MISS_NUM, NULL, X_LEVEL_NUMBER ),
105 TOP_PARENT_FLAG = DECODE( X_TOP_PARENT_FLAG, NULL, TOP_PARENT_FLAG, FND_API.G_MISS_CHAR, NULL, X_TOP_PARENT_FLAG ),
106 LEAF_CHILD_FLAG = DECODE( X_LEAF_CHILD_FLAG, NULL, LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR, NULL, X_LEAF_CHILD_FLAG ),
107 EFFECTIVE_START_DATE = DECODE( X_EFFECTIVE_START_DATE, NULL, EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_START_DATE ),
108 EFFECTIVE_END_DATE = DECODE( X_EFFECTIVE_END_DATE, NULL, EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_END_DATE ),
109 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, X_STATUS ),
110 RELATIONSHIP_ID = DECODE( X_RELATIONSHIP_ID, NULL, RELATIONSHIP_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_ID ),
111 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
112 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
113 CREATION_DATE = CREATION_DATE,
114 CREATED_BY = CREATED_BY,
115 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
116 ACTUAL_CONTENT_SOURCE = DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, 'USER_ENTERED', NULL, 'USER_ENTERED', X_ACTUAL_CONTENT_SOURCE )
117 WHERE ROWID = X_Rowid;
118
119 IF ( SQL%NOTFOUND ) THEN
120 RAISE NO_DATA_FOUND;
121 END IF;
122
123 END Update_Row;
124
125
126 PROCEDURE Select_Row (
127 X_HIERARCHY_TYPE IN VARCHAR2,
128 X_PARENT_ID IN NUMBER,
129 X_PARENT_TABLE_NAME IN VARCHAR2,
130 X_PARENT_OBJECT_TYPE IN VARCHAR2,
131 X_CHILD_ID IN NUMBER,
132 X_CHILD_TABLE_NAME IN VARCHAR2,
133 X_CHILD_OBJECT_TYPE IN VARCHAR2,
134 X_EFFECTIVE_START_DATE IN DATE,
135 X_EFFECTIVE_END_DATE IN DATE,
136 X_LEVEL_NUMBER OUT NOCOPY NUMBER,
137 X_TOP_PARENT_FLAG OUT NOCOPY VARCHAR2,
138 X_LEAF_CHILD_FLAG OUT NOCOPY VARCHAR2,
139 X_STATUS OUT NOCOPY VARCHAR2,
140 X_RELATIONSHIP_ID OUT NOCOPY NUMBER
141 ) IS
142
143 BEGIN
144
145 SELECT
146 NVL( LEVEL_NUMBER, FND_API.G_MISS_NUM ),
147 NVL( TOP_PARENT_FLAG, FND_API.G_MISS_CHAR ),
148 NVL( LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR ),
149 NVL( STATUS, FND_API.G_MISS_CHAR ),
150 NVL( RELATIONSHIP_ID, FND_API.G_MISS_NUM )
151 INTO
152 X_LEVEL_NUMBER,
153 X_TOP_PARENT_FLAG,
154 X_LEAF_CHILD_FLAG,
155 X_STATUS,
156 X_RELATIONSHIP_ID
157 FROM HZ_HIERARCHY_NODES
158 WHERE HIERARCHY_TYPE = X_HIERARCHY_TYPE
159 AND PARENT_ID = X_PARENT_ID
160 AND PARENT_TABLE_NAME = X_PARENT_TABLE_NAME
161 AND PARENT_OBJECT_TYPE = X_PARENT_OBJECT_TYPE
162 AND CHILD_ID = X_CHILD_ID
163 AND CHILD_TABLE_NAME = X_CHILD_TABLE_NAME
164 AND CHILD_OBJECT_TYPE = X_CHILD_OBJECT_TYPE
165 AND EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE
166 AND EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE ;
167 EXCEPTION
168 WHEN NO_DATA_FOUND THEN
169 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
170 FND_MESSAGE.SET_TOKEN( 'RECORD', 'hierarchy node');
171 FND_MESSAGE.SET_TOKEN( 'VALUE', X_HIERARCHY_TYPE );
172 FND_MSG_PUB.ADD;
173 RAISE FND_API.G_EXC_ERROR;
174
175 END Select_Row;
176
177 END HZ_HIERARCHY_NODES_PKG;