APPS.JTF_TERR_DENORM_PVT dependencies on JTF_TERR

Line 1: PACKAGE BODY JTF_TERR_DENORM_PVT AS

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

Line 21: L_TERR_ID JTF_TERR.TERR_ID%TYPE;

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:

Line 22: L_PARENT_TERR_ID JTF_TERR.PARENT_TERRITORY_ID%TYPE;

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;

Line 23: L_NEW_PARENT_TERR_ID JTF_TERR.PARENT_TERRITORY_ID%TYPE;

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:

Line 24: L_LEAF_FLAG JTF_TERR_DENORM.LEAF_FLAG%TYPE;

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

Line 31: FROM JTF_TERR TR1

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:

Line 32: , JTF_TERR_USGS TRUSG

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);

Line 48: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_applsys_schema || '.JTF_TERR_DENORM';

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:

Line 60: FROM JTF_TERR

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:

Line 65: INSERT INTO JTF_TERR_DENORM (

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

Line 111: INSERT INTO JTF_TERR_DENORM (

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

Line 157: FROM JTF_TERR TR1

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:

Line 168: INSERT INTO JTF_TERR_DENORM (

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

Line 226: END JTF_TERR_DENORM_PVT;

222: P_ERROR_CODE := sqlcode;
223: P_ERROR_MSG := sqlerrm;
224: END Populate_API;
225:
226: END JTF_TERR_DENORM_PVT;