[Home] [Help]
PACKAGE BODY: APPS.HZ_HIERARCHY_NODES_PKG
Source
1 PACKAGE BODY HZ_HIERARCHY_NODES_PKG AS
2 /*$Header: ARHHINTB.pls 120.5 2011/06/21 06:29:24 rgokavar ship $ */
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 -- Bug#11871389
61 -- DECODE( X_STATUS, FND_API.G_MISS_CHAR, NULL, X_STATUS ),
62 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
63 DECODE( X_RELATIONSHIP_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_ID ),
64 HZ_UTILITY_V2PUB.CREATED_BY,
65 HZ_UTILITY_V2PUB.CREATION_DATE,
66 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
67 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
68 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
69 DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, 'USER_ENTERED', NULL, 'USER_ENTERED', X_ACTUAL_CONTENT_SOURCE )
70 ) RETURNING
71 ROWID
72 INTO
73 X_ROWID;
74
75 END Insert_Row;
76
77 PROCEDURE Update_Row (
78 X_Rowid IN OUT NOCOPY VARCHAR2,
79 X_HIERARCHY_TYPE IN VARCHAR2,
80 X_PARENT_ID IN NUMBER,
81 X_PARENT_TABLE_NAME IN VARCHAR2,
82 X_PARENT_OBJECT_TYPE IN VARCHAR2,
83 X_CHILD_ID IN NUMBER,
84 X_CHILD_TABLE_NAME IN VARCHAR2,
85 X_CHILD_OBJECT_TYPE IN VARCHAR2,
86 X_LEVEL_NUMBER IN NUMBER,
87 X_TOP_PARENT_FLAG IN VARCHAR2,
88 X_LEAF_CHILD_FLAG IN VARCHAR2,
89 X_EFFECTIVE_START_DATE IN DATE,
90 X_EFFECTIVE_END_DATE IN DATE,
91 X_STATUS IN VARCHAR2,
92 X_RELATIONSHIP_ID IN NUMBER,
93 X_ACTUAL_CONTENT_SOURCE IN VARCHAR2
94 ) IS
95
96 BEGIN
97
98 UPDATE HZ_HIERARCHY_NODES SET
99 HIERARCHY_TYPE = DECODE( X_HIERARCHY_TYPE, NULL, HIERARCHY_TYPE, FND_API.G_MISS_NUM, HIERARCHY_TYPE, X_HIERARCHY_TYPE ),
100 PARENT_ID = DECODE( X_PARENT_ID, NULL, PARENT_ID, FND_API.G_MISS_NUM, PARENT_ID, X_PARENT_ID ),
101 PARENT_TABLE_NAME = DECODE( X_PARENT_TABLE_NAME, NULL, PARENT_TABLE_NAME, FND_API.G_MISS_CHAR, PARENT_TABLE_NAME, X_PARENT_TABLE_NAME ),
102 PARENT_OBJECT_TYPE = DECODE( X_PARENT_OBJECT_TYPE, NULL, PARENT_OBJECT_TYPE, FND_API.G_MISS_CHAR, PARENT_OBJECT_TYPE, X_PARENT_OBJECT_TYPE ),
103 CHILD_ID = DECODE( X_CHILD_ID, NULL, CHILD_ID, FND_API.G_MISS_NUM, CHILD_ID, X_CHILD_ID ),
104 CHILD_TABLE_NAME = DECODE( X_CHILD_TABLE_NAME, NULL, CHILD_TABLE_NAME, FND_API.G_MISS_CHAR, CHILD_TABLE_NAME, X_CHILD_TABLE_NAME ),
105 CHILD_OBJECT_TYPE = DECODE( X_CHILD_OBJECT_TYPE, NULL, CHILD_OBJECT_TYPE, FND_API.G_MISS_CHAR, CHILD_OBJECT_TYPE, X_CHILD_OBJECT_TYPE ),
106 LEVEL_NUMBER = DECODE( X_LEVEL_NUMBER, NULL, LEVEL_NUMBER, FND_API.G_MISS_NUM, NULL, X_LEVEL_NUMBER ),
107 TOP_PARENT_FLAG = DECODE( X_TOP_PARENT_FLAG, NULL, TOP_PARENT_FLAG, FND_API.G_MISS_CHAR, NULL, X_TOP_PARENT_FLAG ),
108 LEAF_CHILD_FLAG = DECODE( X_LEAF_CHILD_FLAG, NULL, LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR, NULL, X_LEAF_CHILD_FLAG ),
109 EFFECTIVE_START_DATE = DECODE( X_EFFECTIVE_START_DATE, NULL, EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_START_DATE ),
110 EFFECTIVE_END_DATE = DECODE( X_EFFECTIVE_END_DATE, NULL, EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, NULL, X_EFFECTIVE_END_DATE ),
111 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, NULL, X_STATUS ),
112 RELATIONSHIP_ID = DECODE( X_RELATIONSHIP_ID, NULL, RELATIONSHIP_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_ID ),
113 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
114 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
115 CREATION_DATE = CREATION_DATE,
116 CREATED_BY = CREATED_BY,
117 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
118 ACTUAL_CONTENT_SOURCE = DECODE( X_ACTUAL_CONTENT_SOURCE, FND_API.G_MISS_CHAR, 'USER_ENTERED', NULL, 'USER_ENTERED', X_ACTUAL_CONTENT_SOURCE )
119 WHERE ROWID = X_Rowid;
120
121 IF ( SQL%NOTFOUND ) THEN
122 RAISE NO_DATA_FOUND;
123 END IF;
124
125 END Update_Row;
126
127
128 PROCEDURE Select_Row (
129 X_HIERARCHY_TYPE IN VARCHAR2,
130 X_PARENT_ID IN NUMBER,
131 X_PARENT_TABLE_NAME IN VARCHAR2,
132 X_PARENT_OBJECT_TYPE IN VARCHAR2,
133 X_CHILD_ID IN NUMBER,
134 X_CHILD_TABLE_NAME IN VARCHAR2,
135 X_CHILD_OBJECT_TYPE IN VARCHAR2,
136 X_EFFECTIVE_START_DATE IN DATE,
137 X_EFFECTIVE_END_DATE IN DATE,
138 X_LEVEL_NUMBER OUT NOCOPY NUMBER,
139 X_TOP_PARENT_FLAG OUT NOCOPY VARCHAR2,
140 X_LEAF_CHILD_FLAG OUT NOCOPY VARCHAR2,
141 X_STATUS OUT NOCOPY VARCHAR2,
142 X_RELATIONSHIP_ID OUT NOCOPY NUMBER
143 ) IS
144
145 BEGIN
146
147 SELECT
148 NVL( LEVEL_NUMBER, FND_API.G_MISS_NUM ),
149 NVL( TOP_PARENT_FLAG, FND_API.G_MISS_CHAR ),
150 NVL( LEAF_CHILD_FLAG, FND_API.G_MISS_CHAR ),
151 NVL( STATUS, FND_API.G_MISS_CHAR ),
152 NVL( RELATIONSHIP_ID, FND_API.G_MISS_NUM )
153 INTO
154 X_LEVEL_NUMBER,
155 X_TOP_PARENT_FLAG,
156 X_LEAF_CHILD_FLAG,
157 X_STATUS,
158 X_RELATIONSHIP_ID
159 FROM HZ_HIERARCHY_NODES
160 WHERE HIERARCHY_TYPE = X_HIERARCHY_TYPE
161 AND PARENT_ID = X_PARENT_ID
162 AND PARENT_TABLE_NAME = X_PARENT_TABLE_NAME
163 AND PARENT_OBJECT_TYPE = X_PARENT_OBJECT_TYPE
164 AND CHILD_ID = X_CHILD_ID
165 AND CHILD_TABLE_NAME = X_CHILD_TABLE_NAME
166 AND CHILD_OBJECT_TYPE = X_CHILD_OBJECT_TYPE
167 AND EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE
168 AND EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE ;
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
172 FND_MESSAGE.SET_TOKEN( 'RECORD', 'hierarchy node');
173 FND_MESSAGE.SET_TOKEN( 'VALUE', X_HIERARCHY_TYPE );
174 FND_MSG_PUB.ADD;
175 RAISE FND_API.G_EXC_ERROR;
176
177 END Select_Row;
178
179 END HZ_HIERARCHY_NODES_PKG;