[Home] [Help]
PACKAGE BODY: APPS.PO_RELEASES_SV
Source
1 PACKAGE BODY PO_RELEASES_SV as
2 /* $Header: POXPOR1B.pls 120.3 2011/06/24 07:59:15 vegajula ship $ */
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
97 /*12405805 changed the order of delete_children and delete_row*/
98 /* Added delete_events_entities call to delete unnecessary events n entities*/
99 /** Call the cover routine to delete all of the children
100 */
101
102 po_headers_sv1.delete_events_entities('RELEASE', X_po_release_id);
103 po_releases_sv.delete_children(X_po_release_id);
104 -- dbms_output.put_line('after delete children');
105
106 po_releases_pkg_s2.delete_row(X_row_id);
107 -- dbms_output.put_line('after call to delete row');
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 -- dbms_output.put_line('In exception');
112 po_message_s.sql_error('delete_release', X_progress, sqlcode);
113 raise;
114 END delete_release;
115
116
117 /*===========================================================================
118
119 PROCEDURE NAME: delete_children
120
121
122 ===========================================================================*/
123 PROCEDURE delete_children
124 (X_po_release_id IN NUMBER) IS
125
126 X_progress VARCHAR2(3) := '';
127 X_entity_level VARCHAR2(25) := 'RELEASE';
128 x_line_location_id NUMBER := '';
129 x_item_type VARCHAR2(8);
130 x_item_key VARCHAR2(240);
131
132 CURSOR C is
133 SELECT line_location_id
134 FROM po_line_locations
135 WHERE po_release_id = X_po_release_id;
136
137
138 BEGIN
139
140 /*
141 ** Call the routine to delete all of the release shipments.
142 */
143 po_shipments_sv4.delete_all_shipments(X_po_release_id,
144 X_entity_level,
145 'NOT RFQ/QUOTE');
146
147 /*
148 ** Call the routine to delete all of the release distributions.
149 */
150 po_distributions_sv.delete_distributions(X_po_release_id,
151 'RELEASE');
152
153 /*
154 ** Call the routine to delete all attachements.
155 */
156 fnd_attached_documents2_pkg.delete_attachments('PO_RELEASE',
157 X_po_release_id,
158 '', '', '', '', 'Y');
159
160 OPEN C;
161
162 LOOP
163
164 FETCH C INTO x_line_location_id;
165 EXIT WHEN C%notfound;
166
167 fnd_attached_documents2_pkg.delete_attachments('PO_SHIPMENT',
168 x_line_location_id,
169 '', '', '', '', 'Y');
170 END LOOP;
171
172 CLOSE C;
173
174 /*
175 ** Call the routine to delete all notifications.
176 */
177
178 /*hvadlamu : commenting out the delete and adding the workflow call*/
179
180 /*po_notifications_sv1.delete_po_notif ('RELEASE',
181 X_po_release_id); */
182 SELECT wf_item_type,wf_item_key
183 INTO x_item_type,x_item_key
184 FROM PO_RELEASES
185 WHERE po_release_id = x_po_release_id;
186
187 if ((x_item_type is null) and (x_item_key is null)) then
188 po_approval_reminder_sv.cancel_notif ('BLANKET',
189 x_po_release_id,'Y');
190 else
191 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
192 end if;
193 /*
194 ** Call the routine to remove the req link from the po
195 */
196 po_req_lines_sv.remove_req_from_po(X_po_release_id, 'RELEASE');
197
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 dbms_output.put_line('In exception');
202 po_message_s.sql_error('delete_children', X_progress, sqlcode);
203 END delete_children;
204
205
206 END PO_RELEASES_SV;