1 package body XTR_GAIN_LOSS_DNM_PKG as
2 /* $Header: xtrgldpb.pls 120.3 2005/06/29 08:14:47 badiredd ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in OUT NOCOPY VARCHAR2,
6 X_GAIN_LOSS_DNM_ID in NUMBER,
7 X_BATCH_ID in NUMBER,
8 X_COMPANY_CODE in VARCHAR2,
9 X_DEAL_NUMBER in NUMBER,
10 X_TRANSACTION_NUMBER in NUMBER,
11 X_DATE_TYPE in VARCHAR2,
12 X_AMOUNT in NUMBER,
13 X_AMOUNT_TYPE in VARCHAR2,
14 X_ACTION in VARCHAR2,
15 X_CURRENCY in VARCHAR2,
16 X_JOURNAL_DATE in DATE,
17 X_REVAL_EFF_FLAG in VARCHAR2,
18 X_CREATION_DATE in DATE,
19 X_CREATED_BY in NUMBER,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER
23 ) is
24 cursor C is select ROWID from XTR_GAIN_LOSS_DNM
25 where GAIN_LOSS_DNM_ID = X_GAIN_LOSS_DNM_ID
26 ;
27 begin
28 insert into XTR_GAIN_LOSS_DNM (
29 LAST_UPDATE_LOGIN,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_DATE,
32 COMPANY_CODE,
33 DEAL_NUMBER,
34 TRANSACTION_NUMBER,
35 DATE_TYPE,
36 AMOUNT,
37 AMOUNT_TYPE,
38 ACTION,
39 CURRENCY,
40 JOURNAL_DATE,
41 CREATION_DATE,
42 CREATED_BY,
43 GAIN_LOSS_DNM_ID,
44 BATCH_ID,
45 REVAL_EFF_FLAG
46 ) VALUES
47 ( X_LAST_UPDATE_LOGIN,
48 X_LAST_UPDATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_COMPANY_CODE,
51 X_DEAL_NUMBER,
52 X_TRANSACTION_NUMBER,
53 X_DATE_TYPE,
54 X_AMOUNT,
55 X_AMOUNT_TYPE,
56 X_ACTION,
57 X_CURRENCY,
58 X_JOURNAL_DATE,
59 X_CREATION_DATE,
60 X_CREATED_BY,
61 X_GAIN_LOSS_DNM_ID,
62 X_BATCH_ID,
63 X_REVAL_EFF_FLAG);
64
65 open c;
66 fetch c into X_ROWID;
67 if (c%notfound) then
68 close c;
69 raise no_data_found;
70 end if;
71 close c;
72
73 end INSERT_ROW;
74
75 procedure LOCK_ROW (
76 X_GAIN_LOSS_DNM_ID in NUMBER,
77 X_BATCH_ID in NUMBER,
78 X_COMPANY_CODE in VARCHAR2,
79 X_DEAL_NUMBER in NUMBER,
80 X_TRANSACTION_NUMBER in NUMBER,
81 X_DATE_TYPE in VARCHAR2,
82 X_AMOUNT in NUMBER,
83 X_AMOUNT_TYPE in VARCHAR2,
84 X_ACTION in VARCHAR2,
85 X_CURRENCY in VARCHAR2,
86 X_JOURNAL_DATE in DATE,
87 X_REVAL_EFF_FLAG in VARCHAR2
88 ) is
89 cursor c1 is select
90 COMPANY_CODE,
91 DEAL_NUMBER,
92 TRANSACTION_NUMBER,
93 DATE_TYPE,
94 AMOUNT,
95 AMOUNT_TYPE,
96 ACTION,
97 CURRENCY,
98 JOURNAL_DATE,
99 BATCH_ID,
100 REVAL_EFF_FLAG
101 from XTR_GAIN_LOSS_DNM
102 where GAIN_LOSS_DNM_ID = X_GAIN_LOSS_DNM_ID
103 for update of GAIN_LOSS_DNM_ID nowait;
104 begin
105 for tlinfo in c1 loop
106 if ( ((tlinfo.BATCH_ID = X_BATCH_ID)
107 OR ((tlinfo.BATCH_ID is null) AND (X_BATCH_ID is null)))
108 AND ((tlinfo.COMPANY_CODE = X_COMPANY_CODE)
109 OR ((tlinfo.COMPANY_CODE is null) AND (X_COMPANY_CODE is null)))
110 AND ((tlinfo.DEAL_NUMBER = X_DEAL_NUMBER)
111 OR ((tlinfo.DEAL_NUMBER is null) AND (X_DEAL_NUMBER is null)))
112 AND ((tlinfo.TRANSACTION_NUMBER = X_TRANSACTION_NUMBER)
113 OR ((tlinfo.TRANSACTION_NUMBER is null) AND (X_TRANSACTION_NUMBER is null)))
114 AND ((tlinfo.DATE_TYPE = X_DATE_TYPE)
115 OR ((tlinfo.DATE_TYPE is null) AND (X_DATE_TYPE is null)))
116 AND ((tlinfo.AMOUNT = X_AMOUNT)
117 OR ((tlinfo.AMOUNT is null) AND (X_AMOUNT is null)))
118 AND ((tlinfo.AMOUNT_TYPE = X_AMOUNT_TYPE)
119 OR ((tlinfo.AMOUNT_TYPE is null) AND (X_AMOUNT_TYPE is null)))
120 AND ((tlinfo.ACTION = X_ACTION)
121 OR ((tlinfo.ACTION is null) AND (X_ACTION is null)))
122 AND ((tlinfo.CURRENCY = X_CURRENCY)
123 OR ((tlinfo.CURRENCY is null) AND (X_CURRENCY is null)))
124 AND ((tlinfo.JOURNAL_DATE = X_JOURNAL_DATE)
125 OR ((tlinfo.JOURNAL_DATE is null) AND (X_JOURNAL_DATE is null)))
126 AND ((tlinfo.REVAL_EFF_FLAG = X_REVAL_EFF_FLAG)
127 OR ((tlinfo.REVAL_EFF_FLAG is null) AND (X_REVAL_EFF_FLAG is null)))
128 ) then
129 null;
130 else
131 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
132 app_exception.raise_exception;
133 end if;
134 end loop;
135 return;
136 end LOCK_ROW;
137
138 procedure UPDATE_ROW (
139 X_GAIN_LOSS_DNM_ID in NUMBER,
140 X_BATCH_ID in NUMBER,
141 X_COMPANY_CODE in VARCHAR2,
142 X_DEAL_NUMBER in NUMBER,
143 X_TRANSACTION_NUMBER in NUMBER,
144 X_DATE_TYPE in VARCHAR2,
145 X_AMOUNT in NUMBER,
146 X_AMOUNT_TYPE in VARCHAR2,
147 X_ACTION in VARCHAR2,
148 X_CURRENCY in VARCHAR2,
149 X_JOURNAL_DATE in DATE,
150 X_REVAL_EFF_FLAG in VARCHAR2,
151 X_LAST_UPDATE_DATE in DATE,
152 X_LAST_UPDATED_BY in NUMBER,
153 X_LAST_UPDATE_LOGIN in NUMBER
154 ) is
155 begin
156 update XTR_GAIN_LOSS_DNM set
157 COMPANY_CODE = X_COMPANY_CODE,
158 DEAL_NUMBER = X_DEAL_NUMBER,
159 TRANSACTION_NUMBER = X_TRANSACTION_NUMBER,
160 DATE_TYPE = X_DATE_TYPE,
161 AMOUNT = X_AMOUNT,
162 AMOUNT_TYPE = X_AMOUNT_TYPE,
163 ACTION = X_ACTION,
164 CURRENCY = X_CURRENCY,
165 JOURNAL_DATE = X_JOURNAL_DATE,
166 BATCH_ID = X_BATCH_ID,
167 REVAL_EFF_FLAG = X_REVAL_EFF_FLAG,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
171 where GAIN_LOSS_DNM_ID = X_GAIN_LOSS_DNM_ID;
172
173 if (sql%notfound) then
174 raise no_data_found;
175 end if;
176 end UPDATE_ROW;
177
178 procedure DELETE_ROW (
179 X_GAIN_LOSS_DNM_ID in NUMBER
180 ) is
181 begin
182 delete from XTR_GAIN_LOSS_DNM
183 where GAIN_LOSS_DNM_ID = X_GAIN_LOSS_DNM_ID;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189 end DELETE_ROW;
190
191 end XTR_GAIN_LOSS_DNM_PKG;