[Home] [Help]
PACKAGE BODY: APPS.PO_RELEASES_SV
Source
1 PACKAGE BODY PO_RELEASES_SV as
2 /* $Header: POXPOR1B.pls 120.1 2005/06/10 01:58:21 kpsingh noship $ */
3
4 /*======================== PO_RELEASES_SV ===============================*/
5
6 /*===========================================================================
7
8 PROCEDURE NAME: lock_row_for_status_update
9
10 ===========================================================================*/
11
12 PROCEDURE lock_row_for_status_update (x_po_release_id IN NUMBER)
13 IS
14 CURSOR C IS
15 SELECT *
16 FROM po_releases
17 WHERE po_release_id = x_po_release_id
18 FOR UPDATE of po_release_id NOWAIT;
19 Recinfo C%ROWTYPE;
20
21 x_progress VARCHAR2(3) := '';
22
23 BEGIN
24 x_progress := '010';
25 OPEN C;
26 FETCH C INTO Recinfo;
27 IF (C%NOTFOUND) then
28 CLOSE C;
29 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
30 APP_EXCEPTION.Raise_Exception;
31 END IF;
32 CLOSE C;
33
34 EXCEPTION
35 WHEN app_exception.record_lock_exception THEN
36 po_message_s.app_error ('PO_ALL_CANNOT_RESERVE_RECORD');
37
38 WHEN OTHERS THEN
39 dbms_output.put_line('In Exception');
40 PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_STATUS_UPDATE', x_progress, sqlcode);
41 RAISE;
42 END;
43
44
45 /*===========================================================================
46
47 PROCEDURE NAME: delete_release
48
49
50 ===========================================================================*/
51 PROCEDURE delete_release
52 (X_po_release_id IN NUMBER,
53 X_row_id IN VARCHAR2) IS
54
55 X_progress VARCHAR2(3) := '';
56 x_item_type PO_RELEASES_ALL.WF_ITEM_TYPE%TYPE;
57 x_item_key PO_RELEASES_ALL.WF_ITEM_KEY%TYPE;
58 X_release_type PO_RELEASES_ALL.RELEASE_TYPE%TYPE;
59
60 BEGIN
61
62 /*
63 ** Call the table handler to delete the release shipment row.
64 */
65 /* Bug 2904413 */
66 X_progress := '010';
67
68 SELECT wf_item_key, wf_item_type, release_type
69 INTO x_item_key, x_item_type, X_release_type
70 FROM po_releases
71 WHERE po_release_id = X_po_release_id;
72
73 X_progress := '020';
74
75 if ((x_item_type is null) and (x_item_key is null)) then
76 po_approval_reminder_sv.cancel_notif (X_release_type, X_po_release_id,'Y');
77 else
78 po_approval_reminder_sv.cancel_notif (X_release_type, X_po_release_id,'Y');
79 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
80 end if;
81
82 X_progress := '030';
83
84 Delete From po_action_history
85 Where OBJECT_TYPE_CODE = 'RELEASE' and
86 OBJECT_SUB_TYPE_CODE = X_release_type and
87 OBJECT_ID = X_po_release_id;
88
89 X_progress := '040';
90
91 fnd_attached_documents2_pkg.delete_attachments('PO_RELEASES', X_po_release_id,'', '', '', '', 'Y');
92
93 /* Bug 2904413 */
94 X_progress := '050';
95
96 po_releases_pkg_s2.delete_row(X_row_id);
97 -- dbms_output.put_line('after call to delete row');
98
99 /*
100 ** Call the cover routine to delete all of the children
101 */
102 po_releases_sv.delete_children(X_po_release_id);
103 -- dbms_output.put_line('after delete children');
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 -- dbms_output.put_line('In exception');
108 po_message_s.sql_error('delete_release', X_progress, sqlcode);
109 raise;
110 END delete_release;
111
112
113 /*===========================================================================
114
115 PROCEDURE NAME: delete_children
116
117
118 ===========================================================================*/
119 PROCEDURE delete_children
120 (X_po_release_id IN NUMBER) IS
121
122 X_progress VARCHAR2(3) := '';
123 X_entity_level VARCHAR2(25) := 'RELEASE';
124 x_line_location_id NUMBER := '';
125 x_item_type VARCHAR2(8);
126 x_item_key VARCHAR2(240);
127
128 CURSOR C is
129 SELECT line_location_id
130 FROM po_line_locations
131 WHERE po_release_id = X_po_release_id;
132
133
134 BEGIN
135
136 /*
137 ** Call the routine to delete all of the release shipments.
138 */
139 po_shipments_sv4.delete_all_shipments(X_po_release_id,
140 X_entity_level,
141 'NOT RFQ/QUOTE');
142
143 /*
144 ** Call the routine to delete all of the release distributions.
145 */
146 po_distributions_sv.delete_distributions(X_po_release_id,
147 'RELEASE');
148
149 /*
150 ** Call the routine to delete all attachements.
151 */
152 fnd_attached_documents2_pkg.delete_attachments('PO_RELEASE',
153 X_po_release_id,
154 '', '', '', '', 'Y');
155
156 OPEN C;
157
158 LOOP
159
160 FETCH C INTO x_line_location_id;
161 EXIT WHEN C%notfound;
162
163 fnd_attached_documents2_pkg.delete_attachments('PO_SHIPMENT',
164 x_line_location_id,
165 '', '', '', '', 'Y');
166 END LOOP;
167
168 CLOSE C;
169
170 /*
171 ** Call the routine to delete all notifications.
172 */
173
174 /*hvadlamu : commenting out the delete and adding the workflow call*/
175
176 /*po_notifications_sv1.delete_po_notif ('RELEASE',
177 X_po_release_id); */
178 SELECT wf_item_type,wf_item_key
179 INTO x_item_type,x_item_key
180 FROM PO_RELEASES
181 WHERE po_release_id = x_po_release_id;
182
183 if ((x_item_type is null) and (x_item_key is null)) then
184 po_approval_reminder_sv.cancel_notif ('BLANKET',
185 x_po_release_id,'Y');
186 else
187 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
188 end if;
189 /*
190 ** Call the routine to remove the req link from the po
191 */
192 po_req_lines_sv.remove_req_from_po(X_po_release_id, 'RELEASE');
193
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 dbms_output.put_line('In exception');
198 po_message_s.sql_error('delete_children', X_progress, sqlcode);
199 END delete_children;
200
201
202 END PO_RELEASES_SV;