1 package body XTR_HEDGE_POLICIES_PKG as
2 /* $Header: xtrhpolb.pls 120.4 2005/06/29 08:18:55 badiredd ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_HEDGE_POLICY_ID in NUMBER,
6 X_SOURCE_TYPE_ID in NUMBER,
7 X_CASHFLOW_FLAG in VARCHAR2,
8 X_FAIRVALUE_FLAG in VARCHAR2,
9 X_ECONOMIC_FLAG in VARCHAR2, --Bug 3378028
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from XTR_HEDGE_POLICIES
17 where HEDGE_POLICY_ID = X_HEDGE_POLICY_ID;
18 begin
19 insert into XTR_HEDGE_POLICIES (
20 HEDGE_POLICY_ID,
21 SOURCE_TYPE_ID,
22 CASHFLOW_FLAG,
23 FAIRVALUE_FLAG,
24 ECONOMIC_FLAG,
25 CREATED_BY,
26 CREATION_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATE_LOGIN
30 ) VALUES (
31 X_HEDGE_POLICY_ID,
32 X_SOURCE_TYPE_ID,
33 X_CASHFLOW_FLAG,
34 X_FAIRVALUE_FLAG,
35 X_ECONOMIC_FLAG,
36 X_CREATED_BY,
37 X_CREATION_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATE_LOGIN);
41
42 open c;
43 fetch c into X_ROWID;
44 if (c%notfound) then
45 close c;
46 raise no_data_found;
47 end if;
48 close c;
49
50 end INSERT_ROW;
51
52 procedure LOCK_ROW (
53 X_HEDGE_POLICY_ID in NUMBER,
54 X_SOURCE_TYPE_ID in NUMBER,
55 X_CASHFLOW_FLAG in VARCHAR2,
56 X_FAIRVALUE_FLAG in VARCHAR2,
57 X_ECONOMIC_FLAG in VARCHAR2 --Bug 3378028
58 ) is
59 cursor c1 is select
60 SOURCE_TYPE_ID,
61 CASHFLOW_FLAG,
62 FAIRVALUE_FLAG,
63 ECONOMIC_FLAG,
64 HEDGE_POLICY_ID
65 from XTR_HEDGE_POLICIES
66 where HEDGE_POLICY_ID = X_HEDGE_POLICY_ID
67 for update of HEDGE_POLICY_ID nowait;
68 begin
69 for tlinfo in c1 loop
70 if ( (tlinfo.HEDGE_POLICY_ID = X_HEDGE_POLICY_ID)
71 AND (tlinfo.SOURCE_TYPE_ID = X_SOURCE_TYPE_ID)
72 AND ((tlinfo.CASHFLOW_FLAG = X_CASHFLOW_FLAG)
73 OR ((tlinfo.CASHFLOW_FLAG is null) AND (X_CASHFLOW_FLAG is null)))
74 AND ((tlinfo.FAIRVALUE_FLAG = X_FAIRVALUE_FLAG)
75 OR ((tlinfo.FAIRVALUE_FLAG is null) AND (X_FAIRVALUE_FLAG is null)))
76 AND ((tlinfo.ECONOMIC_FLAG = X_ECONOMIC_FLAG)
77 OR ((tlinfo.ECONOMIC_FLAG is null) AND (X_ECONOMIC_FLAG is null)))
78 ) then
79 null;
80 else
81 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
82 app_exception.raise_exception;
83 end if;
84 end loop;
85 return;
86 end LOCK_ROW;
87
88 procedure UPDATE_ROW (
89 X_HEDGE_POLICY_ID in NUMBER,
90 X_SOURCE_TYPE_ID in NUMBER,
91 X_CASHFLOW_FLAG in VARCHAR2,
92 X_FAIRVALUE_FLAG in VARCHAR2,
93 X_ECONOMIC_FLAG in VARCHAR2, --Bug 3378028
94 X_LAST_UPDATE_DATE in DATE,
95 X_LAST_UPDATED_BY in NUMBER,
96 X_LAST_UPDATE_LOGIN in NUMBER
97 ) is
98 begin
99 update XTR_HEDGE_POLICIES set
100 SOURCE_TYPE_ID = X_SOURCE_TYPE_ID,
101 CASHFLOW_FLAG = X_CASHFLOW_FLAG,
102 FAIRVALUE_FLAG = X_FAIRVALUE_FLAG,
103 ECONOMIC_FLAG = X_ECONOMIC_FLAG,
104 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
105 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
106 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
107 where HEDGE_POLICY_ID = X_HEDGE_POLICY_ID;
108
109 if (sql%notfound) then
110 raise no_data_found;
111 end if;
112 end UPDATE_ROW;
113
114 procedure DELETE_ROW (
115 X_HEDGE_POLICY_ID in NUMBER
116 ) is
117 begin
118 delete from XTR_HEDGE_POLICIES
119 where HEDGE_POLICY_ID = X_HEDGE_POLICY_ID;
120
121 if (sql%notfound) then
122 raise no_data_found;
123 end if;
124
125 end DELETE_ROW;
126 end;