1 package body GMD_ITEM_SUBSTITUTION_DTL_PKG as
2 /* $Header: GMDITSDB.pls 120.1 2005/07/13 02:57:13 kkillams noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_SUBSTITUTION_LINE_ID in NUMBER,
6 X_SUBSTITUTION_ID in NUMBER,
7 X_INVENTORY_ITEM_ID in NUMBER,
8 X_UNIT_QTY in NUMBER,
9 X_DETAIL_UOM in VARCHAR2,
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 GMD_ITEM_SUBSTITUTION_DTL
17 where SUBSTITUTION_LINE_ID = X_SUBSTITUTION_LINE_ID
18 ;
19 begin
20 insert into GMD_ITEM_SUBSTITUTION_DTL (
21 SUBSTITUTION_LINE_ID,
22 SUBSTITUTION_ID,
23 INVENTORY_ITEM_ID,
24 UNIT_QTY,
25 DETAIL_UOM,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_SUBSTITUTION_LINE_ID,
33 X_SUBSTITUTION_ID,
34 X_INVENTORY_ITEM_ID,
35 X_UNIT_QTY,
36 X_DETAIL_UOM,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN
42 );
43
44 open c;
45 fetch c into X_ROWID;
46 if (c%notfound) then
47 close c;
48 raise no_data_found;
49 end if;
50 close c;
51
52 end INSERT_ROW;
53
54 procedure LOCK_ROW (
55 X_SUBSTITUTION_LINE_ID in NUMBER,
56 X_SUBSTITUTION_ID in NUMBER,
57 X_INVENTORY_ITEM_ID in NUMBER,
58 X_UNIT_QTY in NUMBER,
59 X_DETAIL_UOM in VARCHAR2
60 ) is
61 cursor c is select
62 SUBSTITUTION_ID,
63 INVENTORY_ITEM_ID,
64 UNIT_QTY,
65 DETAIL_UOM
66 from GMD_ITEM_SUBSTITUTION_DTL
67 where SUBSTITUTION_LINE_ID = X_SUBSTITUTION_LINE_ID
68 for update of SUBSTITUTION_LINE_ID nowait;
69 recinfo c%rowtype;
70
71 begin
72 open c;
73 fetch c into recinfo;
74 if (c%notfound) then
75 close c;
76 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
77 app_exception.raise_exception;
78 end if;
79 close c;
80 if ( (recinfo.SUBSTITUTION_ID = X_SUBSTITUTION_ID)
81 AND (recinfo.INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID)
82 AND (recinfo.UNIT_QTY = X_UNIT_QTY)
83 AND (recinfo.DETAIL_UOM = X_DETAIL_UOM)
84 ) then
85 null;
86 else
87 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
88 app_exception.raise_exception;
89 end if;
90 return;
91 end LOCK_ROW;
92
93 procedure UPDATE_ROW (
94 X_SUBSTITUTION_LINE_ID in NUMBER,
95 X_SUBSTITUTION_ID in NUMBER,
96 X_INVENTORY_ITEM_ID in NUMBER,
97 X_UNIT_QTY in NUMBER,
98 X_DETAIL_UOM in VARCHAR2,
99 X_LAST_UPDATE_DATE in DATE,
100 X_LAST_UPDATED_BY in NUMBER,
101 X_LAST_UPDATE_LOGIN in NUMBER
102 ) is
103 begin
104 update GMD_ITEM_SUBSTITUTION_DTL set
105 SUBSTITUTION_ID = X_SUBSTITUTION_ID,
106 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
107 UNIT_QTY = X_UNIT_QTY,
108 DETAIL_UOM = X_DETAIL_UOM,
109 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
110 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
111 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
112 where SUBSTITUTION_LINE_ID = X_SUBSTITUTION_LINE_ID;
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117
118 end UPDATE_ROW;
119
120 procedure DELETE_ROW (
121 X_SUBSTITUTION_LINE_ID in NUMBER
122 ) is
123 begin
124
125 delete from GMD_ITEM_SUBSTITUTION_DTL
126 where SUBSTITUTION_LINE_ID = X_SUBSTITUTION_LINE_ID;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131 end DELETE_ROW;
132 end GMD_ITEM_SUBSTITUTION_DTL_PKG;