[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_DENORM_PVT
Source
1 PACKAGE BODY JTF_TERR_DENORM_PVT AS
2 /* $Header: jtftrdnb.pls 115.4 2000/07/09 12:42:28 pkm ship $ */
3
4 PROCEDURE Populate_API(
5 P_ERROR_CODE OUT NUMBER
6 , P_ERROR_MSG OUT VARCHAR2
7 , P_SOURCE_ID IN NUMBER
8 ) IS
9
10 L_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID();
11 L_PROGRAM_APPL_ID NUMBER := FND_GLOBAL.PROG_APPL_ID();
12 L_PROGRAM_ID NUMBER := FND_GLOBAL.CONC_PROGRAM_ID();
13 L_USER_ID NUMBER := FND_GLOBAL.USER_ID();
14 L_SYSDATE DATE := SYSDATE;
15
16 L_NUM_ROWS_READ INTEGER := 0;
17 L_NUM_ROWS_INSERTED INTEGER := 0;
18
19 L_ROOT_TERR_ID NUMBER := 1;
20
21 L_TERR_ID JTF_TERR.TERR_ID%TYPE;
22 L_PARENT_TERR_ID JTF_TERR.PARENT_TERRITORY_ID%TYPE;
23 L_NEW_PARENT_TERR_ID JTF_TERR.PARENT_TERRITORY_ID%TYPE;
24 L_LEAF_FLAG JTF_TERR_DENORM.LEAF_FLAG%TYPE;
25
26 L_LEVEL_FROM_PARENT NUMBER := 0;
27
28 CURSOR LC_TERR IS
29 SELECT TR1.TERR_ID TERR_ID
30 , TR1.PARENT_TERRITORY_ID PARENT_TERR_ID
31 FROM JTF_TERR TR1
32 , JTF_TERR_USGS TRUSG
33 WHERE TR1.TERR_ID = TRUSG.TERR_ID
34 AND TRUSG.SOURCE_ID = P_SOURCE_ID ;
35
36 l_status varchar2(10);
37 l_industry varchar2(10);
38 l_applsys_schema varchar2(30);
39 l_result boolean;
40
41 BEGIN
42
43 l_result := fnd_installation.get_app_info('JTF',
44 l_status,
45 l_industry,
46 l_applsys_schema);
47
48 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_applsys_schema || '.JTF_TERR_DENORM';
49
50 FOR LR_TERR IN LC_TERR
51 LOOP
52
53 L_NUM_ROWS_READ := L_NUM_ROWS_READ + 1 ;
54 L_TERR_ID := LR_TERR.TERR_ID;
55 L_PARENT_TERR_ID := LR_TERR.PARENT_TERR_ID;
56 L_LEVEL_FROM_PARENT := 0;
57
58 SELECT DECODE(COUNT(*),0,'Y','N')
59 INTO L_LEAF_FLAG
60 FROM JTF_TERR
61 WHERE PARENT_TERRITORY_ID = L_TERR_ID ;
62
63 BEGIN
64
65 INSERT INTO JTF_TERR_DENORM (
66 TERR_ID
67 , PARENT_TERR_ID
68 , CREATION_DATE
69 , CREATED_BY
70 , LAST_UPDATE_DATE
71 , LAST_UPDATED_BY
72 , LAST_UPDATE_LOGIN
73 , REQUEST_ID
74 , PROGRAM_APPLICATION_ID
75 , PROGRAM_ID
76 , PROGRAM_UPDATE_DATE
77 , IMMEDIATE_PARENT_FLAG
78 , ROOT_FLAG
79 , LEAF_FLAG
80 , LEVEL_FROM_PARENT
81 )
82 VALUES (
83 L_TERR_ID
84 , L_TERR_ID
85 , L_SYSDATE
86 , L_USER_ID
87 , L_SYSDATE
88 , L_USER_ID
89 , L_USER_ID
90 , L_REQUEST_ID
91 , L_PROGRAM_APPL_ID
92 , L_PROGRAM_ID
93 , L_SYSDATE
94 , 'N'
95 , 'N'
96 , L_LEAF_FLAG
97 , L_LEVEL_FROM_PARENT
98 );
99
100 L_NUM_ROWS_INSERTED := L_NUM_ROWS_INSERTED + 1;
101
102 END;
103
104 IF L_PARENT_TERR_ID IS NOT NULL THEN
105
106 -- Insert immediate parent details
107 BEGIN
108
109 L_LEVEL_FROM_PARENT := L_LEVEL_FROM_PARENT + 1;
110
111 INSERT INTO JTF_TERR_DENORM (
112 TERR_ID
113 , PARENT_TERR_ID
114 , CREATION_DATE
115 , CREATED_BY
116 , LAST_UPDATE_DATE
117 , LAST_UPDATED_BY
118 , LAST_UPDATE_LOGIN
119 , REQUEST_ID
120 , PROGRAM_APPLICATION_ID
121 , PROGRAM_ID
122 , PROGRAM_UPDATE_DATE
123 , IMMEDIATE_PARENT_FLAG
124 , ROOT_FLAG
125 , LEAF_FLAG
126 , LEVEL_FROM_PARENT
127 )
128 VALUES (
129 L_TERR_ID
130 , L_PARENT_TERR_ID
131 , L_SYSDATE
132 , L_USER_ID
133 , L_SYSDATE
134 , L_USER_ID
135 , L_USER_ID
136 , L_REQUEST_ID
137 , L_PROGRAM_APPL_ID
138 , L_PROGRAM_ID
139 , L_SYSDATE
140 , 'Y'
141 , DECODE(L_PARENT_TERR_ID,L_ROOT_TERR_ID,'Y','N')
142 , L_LEAF_FLAG
143 , L_LEVEL_FROM_PARENT
144 );
145
146 L_NUM_ROWS_INSERTED := L_NUM_ROWS_INSERTED + 1;
147
148 END; -- Immediate parent
149
150 LOOP
151
152 -- Check for the ancestors
153 BEGIN
154
155 SELECT TR1.PARENT_TERRITORY_ID
156 INTO L_NEW_PARENT_TERR_ID
157 FROM JTF_TERR TR1
158 WHERE TR1.TERR_ID = L_PARENT_TERR_ID ;
159
160 END;
161
162 EXIT WHEN L_NEW_PARENT_TERR_ID IS NULL ;
163
164 -- Insert the ancestor details
165 BEGIN
166 L_LEVEL_FROM_PARENT := L_LEVEL_FROM_PARENT + 1;
167
168 INSERT INTO JTF_TERR_DENORM (
169 TERR_ID
170 , PARENT_TERR_ID
171 , CREATION_DATE
172 , CREATED_BY
173 , LAST_UPDATE_DATE
174 , LAST_UPDATED_BY
175 , LAST_UPDATE_LOGIN
176 , REQUEST_ID
177 , PROGRAM_APPLICATION_ID
178 , PROGRAM_ID
179 , PROGRAM_UPDATE_DATE
180 , IMMEDIATE_PARENT_FLAG
181 , ROOT_FLAG
182 , LEAF_FLAG
183 , LEVEL_FROM_PARENT
184 )
185 VALUES (
186 L_TERR_ID
187 , L_NEW_PARENT_TERR_ID
188 , L_SYSDATE
189 , L_USER_ID
190 , L_SYSDATE
191 , L_USER_ID
192 , L_USER_ID
193 , L_REQUEST_ID
194 , L_PROGRAM_APPL_ID
195 , L_PROGRAM_ID
196 , L_SYSDATE
197 , 'N'
198 , 'N'
199 , L_LEAF_FLAG
200 , L_LEVEL_FROM_PARENT
201 );
202
203 L_NUM_ROWS_INSERTED := L_NUM_ROWS_INSERTED + 1;
204
205 L_PARENT_TERR_ID := L_NEW_PARENT_TERR_ID;
206
207 END;
208
209 END LOOP;
210
211 END IF; -- END OF IF L_PARENT_TERR_ID IS NOT NULL
212
213 END LOOP;
214
215 COMMIT;
216
217 --DBMS_OUTPUT.PUT_LINE('ROWS READ : ' || L_NUM_ROWS_READ);
218 --DBMS_OUTPUT.PUT_LINE('ROWS INSERTED: ' || L_NUM_ROWS_INSERTED);
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 P_ERROR_CODE := sqlcode;
223 P_ERROR_MSG := sqlerrm;
224 END Populate_API;
225
226 END JTF_TERR_DENORM_PVT;