1 PACKAGE BODY WSH_DELIVERY_LINES_DPW_V_PKG as
2 /* $Header: WSHDLNHB.pls 115.0 99/07/16 08:18:48 porting ship $ */
3
4 -- This package is required to lock and update the delivery lines' info for
5 -- the Departure Planning Workbench.
6 -- Because the delivery lines consist of rows from SO_LINE_DETAILS and
7 -- SO_PICKING_LINE_DETAILS, there are two Lock and Update row procedures.
8 -- The "LD" procedures update the SO_LINE_DETAILS table and the "PLD"
9 -- procedures update the SO_PICKING_LINE_DETAILS table.
10
11 PROCEDURE Lock_LD_Row(X_Rowid VARCHAR2,
12 X_Line_Detail_Id NUMBER,
13 X_Released_Flag VARCHAR2,
14 X_Delivery_Id NUMBER,
15 X_Departure_Id NUMBER,
16 X_Load_Seq_Number NUMBER,
17 X_Master_Container_Item_Id NUMBER,
18 X_Detail_Container_Item_Id NUMBER,
19 X_DPW_Assigned_Flag VARCHAR2,
20 X_Last_Update_Date DATE,
21 X_Last_Updated_By NUMBER,
22 X_Last_Update_Login NUMBER
23 ) IS
24 CURSOR C IS
25 SELECT *
26 FROM so_line_details
27 WHERE rowid = X_Rowid
28 FOR UPDATE of Line_Detail_Id NOWAIT;
29 Recinfo C%ROWTYPE;
30
31 BEGIN
32 OPEN C;
33 FETCH C INTO Recinfo;
34 if (C%NOTFOUND) then
35 CLOSE C;
36 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
37 APP_EXCEPTION.Raise_Exception;
38 end if;
39 CLOSE C;
40 if (
41 (Recinfo.line_detail_id = X_Line_Detail_Id)
42 AND ( (Recinfo.released_flag = X_Released_Flag)
43 OR ( (Recinfo.released_flag IS NULL)
44 AND (X_Released_Flag IS NULL)))
45 AND ( (Recinfo.delivery_id = X_Delivery_Id)
46 OR ( (Recinfo.delivery_id IS NULL)
47 AND (X_Delivery_Id IS NULL)))
48 AND ( (Recinfo.departure_id = X_Departure_Id)
49 OR ( (Recinfo.departure_id IS NULL)
50 AND (X_Departure_Id IS NULL)))
51 AND ( (Recinfo.load_seq_number = X_Load_Seq_Number)
52 OR ( (Recinfo.load_seq_number IS NULL)
53 AND (X_Load_Seq_Number IS NULL)))
54 AND ( (Recinfo.master_container_item_id = X_Master_Container_Item_Id)
55 OR ( (Recinfo.master_container_item_id IS NULL)
56 AND (X_Master_Container_Item_Id IS NULL)))
57 AND ( (Recinfo.detail_container_item_id = X_Detail_Container_Item_Id)
58 OR ( (Recinfo.detail_container_item_id IS NULL)
59 AND (X_Detail_Container_Item_Id IS NULL)))
60 AND ( (Recinfo.dpw_assigned_flag = X_DPW_Assigned_Flag)
61 OR ( (Recinfo.dpw_assigned_flag IS NULL)
62 AND (X_DPW_Assigned_Flag IS NULL)))
63 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
64 OR ( (Recinfo.last_update_date IS NULL)
65 AND (X_Last_Update_Date IS NULL)))
66 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
67 OR ( (Recinfo.last_updated_by IS NULL)
68 AND (X_Last_Updated_By IS NULL)))
69 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
70 OR ( (Recinfo.last_update_login IS NULL)
71 AND (X_Last_Update_Login IS NULL)))
72 ) then
73 return;
74 else
75 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
76 APP_EXCEPTION.Raise_Exception;
77 end if;
78 END Lock_LD_Row;
79
80 PROCEDURE Update_LD_Row(X_Rowid VARCHAR2,
81 X_Delivery_Id NUMBER,
82 X_Departure_Id NUMBER,
83 X_Load_Seq_Number NUMBER,
84 X_Master_Container_Item_Id NUMBER,
85 X_Detail_Container_Item_Id NUMBER,
86 X_DPW_Assigned_Flag VARCHAR2,
87 X_Last_Update_Date DATE,
88 X_Last_Updated_By NUMBER,
89 X_Last_Update_Login NUMBER
90 ) IS
91 BEGIN
92 UPDATE so_line_details
93 SET
94 delivery_id = X_Delivery_Id,
95 departure_id = X_Departure_Id,
96 load_seq_number = X_Load_Seq_Number,
97 master_container_item_id = X_Master_Container_Item_Id,
98 detail_container_item_id = X_Detail_Container_Item_Id,
99 dpw_assigned_flag = X_DPW_Assigned_Flag,
100 last_update_date = X_Last_Update_Date,
101 last_updated_by = X_Last_Updated_By,
102 last_update_login = X_Last_Update_Login
103 WHERE rowid = X_Rowid;
104
105 if (SQL%NOTFOUND) then
106 Raise NO_DATA_FOUND;
107 end if;
108 END Update_LD_Row;
109
110
111 PROCEDURE Lock_PLD_Row(X_Rowid VARCHAR2,
112 X_Picking_Line_Detail_Id NUMBER,
113 X_Released_Flag VARCHAR2,
114 X_Delivery_Id NUMBER,
115 X_Departure_Id NUMBER,
116 X_Load_Seq_Number NUMBER,
117 X_Master_Container_Item_Id NUMBER,
118 X_Detail_Container_Item_Id NUMBER,
119 X_DPW_Assigned_Flag VARCHAR2,
120 X_Last_Update_Date DATE,
121 X_Last_Updated_By NUMBER,
122 X_Last_Update_Login NUMBER
123 ) IS
124 CURSOR C IS
125 SELECT *
126 FROM so_picking_line_details
127 WHERE rowid = X_Rowid
128 FOR UPDATE of Picking_Line_Detail_Id NOWAIT;
129 Recinfo C%ROWTYPE;
130
131
132 BEGIN
133 OPEN C;
134 FETCH C INTO Recinfo;
135 if (C%NOTFOUND) then
136 CLOSE C;
137 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
138 APP_EXCEPTION.Raise_Exception;
139 end if;
140 CLOSE C;
141 if (
142 (Recinfo.picking_line_detail_id = X_Picking_Line_Detail_Id)
143 AND ( (Recinfo.released_flag = X_Released_Flag)
144 OR ( (Recinfo.released_flag IS NULL)
145 AND (X_Released_Flag IS NULL)))
146 AND ( (Recinfo.delivery_id = X_Delivery_Id)
147 OR ( (Recinfo.delivery_id IS NULL)
148 AND (X_Delivery_Id IS NULL)))
149 AND ( (Recinfo.departure_id = X_Departure_Id)
150 OR ( (Recinfo.departure_id IS NULL)
151 AND (X_Departure_Id IS NULL)))
152 AND ( (Recinfo.load_seq_number = X_Load_Seq_Number)
153 OR ( (Recinfo.load_seq_number IS NULL)
154 AND (X_Load_Seq_Number IS NULL)))
155 AND ( (Recinfo.master_container_item_id = X_Master_Container_Item_Id)
156 OR ( (Recinfo.master_container_item_id IS NULL)
157 AND (X_Master_Container_Item_Id IS NULL)))
158 AND ( (Recinfo.detail_container_item_id = X_Detail_Container_Item_Id)
159 OR ( (Recinfo.detail_container_item_id IS NULL)
160 AND (X_Detail_Container_Item_Id IS NULL)))
161 AND ( (Recinfo.dpw_assigned_flag = X_DPW_Assigned_Flag)
162 OR ( (Recinfo.dpw_assigned_flag IS NULL)
163 AND (X_DPW_Assigned_Flag IS NULL)))
164 AND ( (Recinfo.last_update_date = X_Last_Update_Date)
165 OR ( (Recinfo.last_update_date IS NULL)
166 AND (X_Last_Update_Date IS NULL)))
167 AND ( (Recinfo.last_updated_by = X_Last_Updated_By)
168 OR ( (Recinfo.last_updated_by IS NULL)
169 AND (X_Last_Updated_By IS NULL)))
170 AND ( (Recinfo.last_update_login = X_Last_Update_Login)
171 OR ( (Recinfo.last_update_login IS NULL)
172 AND (X_Last_Update_Login IS NULL)))
173 ) then
174 return;
175 else
176 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
177 APP_EXCEPTION.Raise_Exception;
178 end if;
179 END Lock_PLD_Row;
180
181 PROCEDURE Update_PLD_Row(X_Rowid VARCHAR2,
182 X_Delivery_Id NUMBER,
183 X_Departure_Id NUMBER,
184 X_Load_Seq_Number NUMBER,
185 X_Master_Container_Item_Id NUMBER,
186 X_Detail_Container_Item_Id NUMBER,
187 X_DPW_Assigned_Flag VARCHAR2,
188 X_Last_Update_Date DATE,
189 X_Last_Updated_By NUMBER,
190 X_Last_Update_Login NUMBER
191 ) IS
192 BEGIN
193 UPDATE so_picking_line_details
194 SET
195 delivery_id = X_Delivery_Id,
196 departure_id = X_Departure_Id,
197 load_seq_number = X_Load_Seq_Number,
198 master_container_item_id = X_Master_Container_Item_Id,
199 detail_container_item_id = X_Detail_Container_Item_Id,
200 dpw_assigned_flag = X_DPW_Assigned_Flag,
201 last_update_date = X_Last_Update_Date,
202 last_updated_by = X_Last_Updated_By,
203 last_update_login = X_Last_Update_Login
204 WHERE rowid = X_Rowid;
205
206 if (SQL%NOTFOUND) then
207 Raise NO_DATA_FOUND;
208 end if;
209 END Update_PLD_Row;
210
211
212 END WSH_DELIVERY_LINES_DPW_V_PKG;
[Home] [Help]