1 package body XDP_FA_PARAMETERS_PKG as
2 /* $Header: XDPFAPRB.pls 120.1 2005/06/15 22:59:39 appldev $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_FULFILLMENT_ACTION_ID in NUMBER,
6 X_PARAMETER_NAME in VARCHAR2,
7 X_EVALUATION_PROCEDURE in VARCHAR2,
8 X_EVALUATION_SEQ in NUMBER,
9 X_LOG_IN_AUDIT_TRAIL_FLAG in VARCHAR2,
10 X_DISPLAY_SEQ in NUMBER,
11 X_DEFAULT_VALUE in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from XDP_FA_PARAMETERS
19 where FULFILLMENT_ACTION_ID = X_FULFILLMENT_ACTION_ID
20 and PARAMETER_NAME = X_PARAMETER_NAME;
21 begin
22 insert into XDP_FA_PARAMETERS (
23 FULFILLMENT_ACTION_ID,
24 PARAMETER_NAME,
25 EVALUATION_PROCEDURE,
26 EVALUATION_SEQ,
27 LOG_IN_AUDIT_TRAIL_FLAG,
28 DEFAULT_VALUE,
29 DISPLAY_SEQ,
30 CREATION_DATE,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 CREATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_FULFILLMENT_ACTION_ID,
37 X_PARAMETER_NAME,
38 X_EVALUATION_PROCEDURE,
39 X_EVALUATION_SEQ,
40 X_LOG_IN_AUDIT_TRAIL_FLAG,
41 X_DEFAULT_VALUE,
42 X_DISPLAY_SEQ,
43 X_CREATION_DATE,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_CREATED_BY,
47 X_LAST_UPDATE_LOGIN
48 ) ;
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOCK_ROW (
60 X_FULFILLMENT_ACTION_ID in NUMBER,
61 X_PARAMETER_NAME in VARCHAR2,
62 X_EVALUATION_PROCEDURE in VARCHAR2,
63 X_EVALUATION_SEQ in NUMBER,
64 X_LOG_IN_AUDIT_TRAIL_FLAG in VARCHAR2,
65 X_DISPLAY_SEQ in NUMBER,
66 X_DEFAULT_VALUE in VARCHAR2
67 ) is
68 cursor c1 is select
69 EVALUATION_PROCEDURE,
70 EVALUATION_SEQ,
71 LOG_IN_AUDIT_TRAIL_FLAG,
72 DISPLAY_SEQ,
73 DEFAULT_VALUE
74 from XDP_FA_PARAMETERS
75 where FULFILLMENT_ACTION_ID = X_FULFILLMENT_ACTION_ID
76 and PARAMETER_NAME = X_PARAMETER_NAME
77 for update of FULFILLMENT_ACTION_ID nowait;
78 begin
79 for tlinfo in c1 loop
80 if ( ((tlinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
81 OR ((tlinfo.DEFAULT_VALUE is null) AND (X_DEFAULT_VALUE is null)))
82 AND ((tlinfo.EVALUATION_PROCEDURE = X_EVALUATION_PROCEDURE)
83 OR ((tlinfo.EVALUATION_PROCEDURE is null) AND (X_EVALUATION_PROCEDURE is null)))
84 AND ((tlinfo.EVALUATION_SEQ = X_EVALUATION_SEQ)
85 OR ((tlinfo.EVALUATION_SEQ is null) AND (X_EVALUATION_SEQ is null)))
86 AND ((tlinfo.LOG_IN_AUDIT_TRAIL_FLAG = X_LOG_IN_AUDIT_TRAIL_FLAG)
87 OR ((tlinfo.LOG_IN_AUDIT_TRAIL_FLAG is null) AND (X_LOG_IN_AUDIT_TRAIL_FLAG is null)))
88 AND ((tlinfo.DISPLAY_SEQ = X_DISPLAY_SEQ)
89 OR ((tlinfo.DISPLAY_SEQ is null) AND (X_DISPLAY_SEQ is null)))
90 ) then
91 null;
92 else
93 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
94 app_exception.raise_exception;
95 end if;
96 end loop;
97 return;
98 end LOCK_ROW;
99
100 procedure UPDATE_ROW (
101 X_FULFILLMENT_ACTION_ID in NUMBER,
102 X_PARAMETER_NAME in VARCHAR2,
103 X_EVALUATION_PROCEDURE in VARCHAR2,
104 X_EVALUATION_SEQ in NUMBER,
105 X_LOG_IN_AUDIT_TRAIL_FLAG in VARCHAR2,
106 X_DISPLAY_SEQ in NUMBER,
107 X_DEFAULT_VALUE in VARCHAR2,
108 X_LAST_UPDATE_DATE in DATE,
109 X_LAST_UPDATED_BY in NUMBER,
110 X_LAST_UPDATE_LOGIN in NUMBER
111 ) is
112 begin
113 update XDP_FA_PARAMETERS set
114 EVALUATION_PROCEDURE = X_EVALUATION_PROCEDURE,
115 EVALUATION_SEQ = X_EVALUATION_SEQ,
116 LOG_IN_AUDIT_TRAIL_FLAG = X_LOG_IN_AUDIT_TRAIL_FLAG,
117 DISPLAY_SEQ = X_DISPLAY_SEQ,
118 DEFAULT_VALUE = X_DEFAULT_VALUE,
119 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
120 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
121 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
122 where FULFILLMENT_ACTION_ID = X_FULFILLMENT_ACTION_ID
123 and PARAMETER_NAME = X_PARAMETER_NAME ;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128 end UPDATE_ROW;
129
130 procedure DELETE_ROW (
131 X_FULFILLMENT_ACTION_ID in NUMBER,
132 X_PARAMETER_NAME in VARCHAR2
133 ) is
134 begin
135 delete from XDP_FA_PARAMETERS
136 where FULFILLMENT_ACTION_ID = X_FULFILLMENT_ACTION_ID
137 and PARAMETER_NAME = X_PARAMETER_NAME ;
138
139 if (sql%notfound) then
140 raise no_data_found;
141 end if;
142
143 end DELETE_ROW;
144
145 end XDP_FA_PARAMETERS_PKG;