1 package body BIS_TERRITORY_HIERARCHIES_PKG AS
2 /* $Header: BISTERHB.pls 115.1 99/07/17 16:10:54 porting shi $ */
3 /*=======================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME
8 | DESCRIPTION
9 | PL/SQL body for package: BIS_TERRITORY_HIERARCHIES_PKG
10 *=======================================================================*/
11 procedure INSERT_ROW (
12 X_ROWID in out VARCHAR2,
13 X_PARENT_TERRITORY_CODE in VARCHAR2,
14 X_PARENT_TERRITORY_TYPE in VARCHAR2,
15 X_CHILD_TERRITORY_CODE in VARCHAR2,
16 X_CHILD_TERRITORY_TYPE in VARCHAR2,
17 X_START_DATE_ACTIVE in DATE,
18 X_END_DATE_ACTIVE in DATE,
19 X_CREATION_DATE in DATE,
20 X_CREATED_BY in NUMBER,
21 X_LAST_UPDATE_DATE in DATE,
22 X_LAST_UPDATED_BY in NUMBER,
23 X_LAST_UPDATE_LOGIN in NUMBER
24 ) is
25 cursor C is select ROWID from BIS_TERRITORY_HIERARCHIES
26 where PARENT_TERRITORY_CODE = X_PARENT_TERRITORY_CODE
27 and PARENT_TERRITORY_TYPE = X_PARENT_TERRITORY_TYPE
28 and CHILD_TERRITORY_CODE = X_CHILD_TERRITORY_CODE
29 and CHILD_TERRITORY_TYPE = X_CHILD_TERRITORY_TYPE;
30 begin
31 insert into BIS_TERRITORY_HIERARCHIES (
32 PARENT_TERRITORY_CODE,
33 PARENT_TERRITORY_TYPE,
34 CHILD_TERRITORY_CODE,
35 CHILD_TERRITORY_TYPE,
36 START_DATE_ACTIVE,
37 END_DATE_ACTIVE,
38 CREATED_BY,
39 CREATION_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_DATE,
42 LAST_UPDATE_LOGIN
43 ) select
44 X_PARENT_TERRITORY_CODE,
45 X_PARENT_TERRITORY_TYPE,
46 X_CHILD_TERRITORY_CODE,
47 X_CHILD_TERRITORY_TYPE,
48 X_START_DATE_ACTIVE,
49 X_END_DATE_ACTIVE,
50 X_CREATED_BY,
51 X_CREATION_DATE,
52 X_LAST_UPDATED_BY,
53 X_LAST_UPDATE_DATE,
54 X_LAST_UPDATE_LOGIN
55 from dual;
56
57 open c;
58 fetch c into X_ROWID;
59 if (c%notfound) then
60 close c;
61 raise no_data_found;
62 end if;
63 close c;
64
65 end INSERT_ROW;
66
67 procedure LOCK_ROW (
68 X_PARENT_TERRITORY_CODE in VARCHAR2,
69 X_PARENT_TERRITORY_TYPE in VARCHAR2,
70 X_CHILD_TERRITORY_CODE in VARCHAR2,
71 X_CHILD_TERRITORY_TYPE in VARCHAR2,
72 X_START_DATE_ACTIVE in DATE,
73 X_END_DATE_ACTIVE in DATE
74 ) is
75 cursor c1 is select
76 START_DATE_ACTIVE,
77 END_DATE_ACTIVE,
78 CHILD_TERRITORY_TYPE
79 from BIS_TERRITORY_HIERARCHIES
80 where PARENT_TERRITORY_CODE = X_PARENT_TERRITORY_CODE
81 and PARENT_TERRITORY_TYPE = X_PARENT_TERRITORY_TYPE
82 and CHILD_TERRITORY_CODE = X_CHILD_TERRITORY_CODE
83 and CHILD_TERRITORY_TYPE = X_CHILD_TERRITORY_TYPE
84 for update of PARENT_TERRITORY_CODE nowait;
85 begin
86 for tlinfo in c1 loop
87 if ( (tlinfo.CHILD_TERRITORY_TYPE = X_CHILD_TERRITORY_TYPE)
88 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
89 OR ((tlinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
90 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
91 OR ((tlinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
92 ) then
93 null;
94 else
95 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
96 app_exception.raise_exception;
97 end if;
98 end loop;
99 return;
100 end LOCK_ROW;
101
102 procedure UPDATE_ROW (
103 X_PARENT_TERRITORY_CODE in VARCHAR2,
104 X_PARENT_TERRITORY_TYPE in VARCHAR2,
105 X_CHILD_TERRITORY_CODE in VARCHAR2,
106 X_CHILD_TERRITORY_TYPE in VARCHAR2,
107 X_START_DATE_ACTIVE in DATE,
108 X_END_DATE_ACTIVE in DATE,
109 X_LAST_UPDATE_DATE in DATE,
110 X_LAST_UPDATED_BY in NUMBER,
111 X_LAST_UPDATE_LOGIN in NUMBER
112 ) is
113 begin
114 update BIS_TERRITORY_HIERARCHIES set
115 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
116 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
117 CHILD_TERRITORY_TYPE = X_CHILD_TERRITORY_TYPE,
118 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
119 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
120 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
121 where PARENT_TERRITORY_CODE = X_PARENT_TERRITORY_CODE
122 and PARENT_TERRITORY_TYPE = X_PARENT_TERRITORY_TYPE
123 and CHILD_TERRITORY_CODE = X_CHILD_TERRITORY_CODE
124 and CHILD_TERRITORY_TYPE = X_CHILD_TERRITORY_TYPE;
125
126 if (sql%notfound) then
127 raise no_data_found;
128 end if;
129 end UPDATE_ROW;
130
131 procedure DELETE_ROW (
132 X_PARENT_TERRITORY_CODE in VARCHAR2,
133 X_PARENT_TERRITORY_TYPE in VARCHAR2,
134 X_CHILD_TERRITORY_CODE in VARCHAR2,
135 X_CHILD_TERRITORY_TYPE in VARCHAR2
136 ) is
137 begin
138 delete from BIS_TERRITORY_HIERARCHIES
139 where PARENT_TERRITORY_CODE = X_PARENT_TERRITORY_CODE
140 and PARENT_TERRITORY_TYPE = X_PARENT_TERRITORY_TYPE
141 and CHILD_TERRITORY_CODE = X_CHILD_TERRITORY_CODE
142 and CHILD_TERRITORY_TYPE = X_CHILD_TERRITORY_TYPE;
143
144 if (sql%notfound) then
145 raise no_data_found;
146 end if;
147
148 end DELETE_ROW;
149
150 end BIS_TERRITORY_HIERARCHIES_PKG;
[Home] [Help]