[Home] [Help]
PACKAGE BODY: APPS.AME_STRING_VALUES_API
Source
1 PACKAGE BODY AME_STRING_VALUES_API AS
2 /* $Header: amesvapi.pkb 120.1 2005/10/14 04:14:09 ubhat noship $ */
3
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_LOGIN out nocopy NUMBER
9 ) is
10 begin
11 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATE_LOGIN := 0;
14 end OWNER_TO_WHO;
15
16 procedure KEY_TO_IDS (
17 X_CONDITION_ID in NUMBER,
18 X_STRING_VALUE in VARCHAR2,
19 X_STRING_VALUE_COUNT out nocopy NUMBER
20 ) is
21 cursor CSR_GET_STRING_VALUE_COUNT
22 (
23 X_CONDITION_ID in NUMBER,
24 X_STRING_VALUE in VARCHAR2
25 ) is
26 select COUNT(*)
27 from AME_STRING_VALUES
28 where CONDITION_ID = X_CONDITION_ID
29 and STRING_VALUE = X_STRING_VALUE;
30 begin
31 if X_CONDITION_ID is not null then
32 open CSR_GET_STRING_VALUE_COUNT (X_CONDITION_ID, X_STRING_VALUE);
33 fetch CSR_GET_STRING_VALUE_COUNT into X_STRING_VALUE_COUNT;
34 close CSR_GET_STRING_VALUE_COUNT;
35 end if;
36 end KEY_TO_IDS;
37
38 procedure INSERT_ROW (
39 X_CONDITION_ID in NUMBER,
40 X_STRING_VALUE in VARCHAR2,
41 X_CREATED_BY in NUMBER,
42 X_CREATION_DATE in DATE,
43 X_LAST_UPDATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATE_LOGIN in NUMBER,
46 X_START_DATE in DATE,
47 X_OBJECT_VERSION_NUMBER in NUMBER)
48 is
49
50 begin
51
52 insert into AME_STRING_VALUES
53 (
54 CONDITION_ID,
55 STRING_VALUE,
56 CREATED_BY,
57 CREATION_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATE_LOGIN,
61 START_DATE,
62 END_DATE,
63 OBJECT_VERSION_NUMBER
64 ) values (
65 X_CONDITION_ID,
66 X_STRING_VALUE,
67 X_CREATED_BY,
68 X_CREATION_DATE,
69 X_LAST_UPDATED_BY,
70 X_LAST_UPDATE_DATE,
71 X_LAST_UPDATE_LOGIN,
72 X_START_DATE,
73 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
74 X_OBJECT_VERSION_NUMBER);
75
76 end INSERT_ROW;
77
78 procedure DELETE_ROW (
79 X_CONDITION_ID in NUMBER
80 ) is
81 begin
82 delete from AME_STRING_VALUES
83 where CONDITION_ID = X_CONDITION_ID;
84
85 if (sql%notfound) then
86 raise no_data_found;
87 end if;
88 end DELETE_ROW;
89
90 procedure LOAD_ROW (
91 X_CONDITION_ID in VARCHAR2,
92 X_CONDITION_TYPE in VARCHAR2,
93 X_ATTRIBUTE_NAME in VARCHAR2,
94 X_STRING_VALUE in VARCHAR2,
95 X_OWNER in VARCHAR2,
96 X_LAST_UPDATE_DATE in VARCHAR2
97 )
98 is
99 X_ATTRIBUTE_ID NUMBER;
100 X_CREATED_BY NUMBER;
101 X_LAST_UPDATED_BY NUMBER;
102 X_LAST_UPDATE_LOGIN NUMBER;
103 X_STRING_VALUE_COUNT NUMBER;
104 X_STRING_VALUE_ROW VARCHAR2(20);
105 begin
106 OWNER_TO_WHO (
107 X_OWNER,
108 X_CREATED_BY,
109 X_LAST_UPDATED_BY,
110 X_LAST_UPDATE_LOGIN
111 );
112 if X_CONDITION_ID < 0 then
113 KEY_TO_IDS (
114 X_CONDITION_ID,
115 X_STRING_VALUE,
116 X_STRING_VALUE_COUNT
117 );
118 -- the current row was not found but there is a corresponding
119 -- condition insert a new row
120 if (X_STRING_VALUE_COUNT = 0)
121 and (X_CONDITION_ID is not null) then
122 INSERT_ROW (
123 X_CONDITION_ID,
124 X_STRING_VALUE,
125 X_CREATED_BY,
126 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
127 X_LAST_UPDATED_BY,
128 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
129 X_LAST_UPDATE_LOGIN,
130 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
131 1);
132 end if;
133 end if;
134 exception
135 when others then
136 ame_util.runtimeException('ame_string_values_api',
137 'load_row',
138 sqlcode,
139 sqlerrm);
140 raise;
141 end LOAD_ROW;
142 --
143 END AME_STRING_VALUES_API;