1 PACKAGE PO_Mass_Update_PO_PVT AS
2 /* $Header: PO_Mass_Update_PO_PVT.pls 120.3 2008/01/09 14:32:09 rakchakr noship $*/
3
4 -- Global variables to hold the Buyer_Info procedure Information
5
6 p_old_buyer_name VARCHAR2(400);
7 p_old_username VARCHAR2(400);
8 p_new_username VARCHAR2(400);
9 p_org_name VARCHAR2(100);
10 p_new_buyer_name VARCHAR2(400);
11 p_supplier_name VARCHAR2(400);
12 p_inc_close_po VARCHAR2(100);
13 p_new_user_display_name VARCHAR2(400);
14 p_old_user_display_name VARCHAR2(400);
15 p_old_buyer_user_id NUMBER;
16 p_new_buyer_user_id NUMBER;
17
18 -- Global variables to hold the concurrent program parameter values.
19
20 g_old_personid NUMBER;
21 g_document_type VARCHAR2(200);
22 g_document_no_from VARCHAR2(200);
23 g_document_no_to VARCHAR2(200);
24 g_date_from DATE;
25 g_date_to DATE;
26 g_supplier_id NUMBER;
27 g_old_username VARCHAR2(200);
28
29 --------------------------------------------------------------------------------------------------
30 -- Start of Comments
31
32 -- API Name : Do_Update
33 -- Type : Private
34 -- Pre-reqs : None
35 -- Function : Calls the procedure Update_Buyer/Update_Approver/Update_Deliver_To
36 -- or All of the above to update the Buyer/Approver/Deliver_To person
37 -- accordingly to the input received from the Update_Person parameter value set.
38 -- Parameters :
39
40 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
41 -- p_old_personid Id of the old person.
42 -- p_new_personid Id of the new person.
43 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
44 -- p_document_no_from Document number from.
45 -- p_document_no_to Document number to.
46 -- p_date_from Date from.
47 -- p_date_to Date to.
48 -- p_supplier_id Supplier id.
49 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
50 -- p_commit_interval Commit interval.
51
52 -- OUT : p_msg_data Actual message in encoded format.
53 -- p_msg_count Holds the number of messages in the API list.
54 -- p_return_status Return status of the API (Includes 'S','E','U').
55
56 -- End of Comments
57 --------------------------------------------------------------------------------------------------
58
59
60 PROCEDURE DO_Update(p_update_person IN VARCHAR2,
61 p_old_personid IN NUMBER,
62 p_new_personid IN NUMBER,
63 p_document_type IN VARCHAR2,
64 p_document_no_from IN VARCHAR2,
65 p_document_no_to IN VARCHAR2,
66 p_date_from IN DATE,
67 p_date_to IN DATE,
68 p_supplier_id IN NUMBER,
69 p_include_close_po IN VARCHAR2,
70 p_commit_interval IN NUMBER,
71 p_msg_data OUT NOCOPY VARCHAR2,
72 p_msg_count OUT NOCOPY NUMBER,
73 p_return_status OUT NOCOPY VARCHAR2);
74
75 --------------------------------------------------------------------------------------------------
76 -- Start of Comments
77
78 -- API Name : Update_Buyer
79 -- Type : Private
80 -- Pre-reqs : None
81 -- Function : Updates the old buyer with the new buyer provided and also updates the
82 -- worklfow attributes when the PO and release are in Inprocess and
83 -- Pre-approved status.
84
85 -- Parameters :
86
87 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
88 -- p_old_personid Id of the old person.
89 -- p_new_personid Id of the new person.
90 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
91 -- p_document_no_from Document number from.
92 -- p_document_no_to Document number to.
93 -- p_date_from Date from.
94 -- p_date_to Date to.
95 -- p_supplier_id Supplier id.
96 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
97 -- p_commit_interval Commit interval.
98
99 -- OUT : p_msg_data Actual message in encoded format.
100 -- p_msg_count Holds the number of messages in the API list.
101 -- p_return_status Return status of the API (Includes 'S','E','U').
102
103 -- End of Comments
104 --------------------------------------------------------------------------------------------------
105
106 PROCEDURE Update_Buyer(p_update_person IN VARCHAR2,
107 p_old_personid IN NUMBER,
108 p_new_personid IN NUMBER,
109 p_document_type IN VARCHAR2,
110 p_document_no_from IN VARCHAR2,
111 p_document_no_to IN VARCHAR2,
112 p_date_from IN DATE,
113 p_date_to IN DATE,
114 p_supplier_id IN NUMBER,
115 p_include_close_po IN VARCHAR2,
116 p_commit_interval IN NUMBER,
117 p_msg_data OUT NOCOPY VARCHAR2,
118 p_msg_count OUT NOCOPY NUMBER,
119 p_return_status OUT NOCOPY VARCHAR2);
120
121 --------------------------------------------------------------------------------------------------
122 -- Start of Comments
123
124 -- API Name : Update_Approver
125 -- Type : Private
126 -- Pre-reqs : None
127 -- Function : Updates the old approver with the new approver provided and also forwards
128 -- the notification from old approver to new approver in case of In process
129 -- and Pre-approved PO's and releases.
130
131 -- Parameters :
132
133 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
134 -- p_old_personid Id of the old person.
135 -- p_new_personid Id of the new person.
136 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
137 -- p_document_no_from Document number from.
138 -- p_document_no_to Document number to.
139 -- p_date_from Date from.
140 -- p_date_to Date to.
141 -- p_supplier_id Supplier id.
142 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
143 -- p_commit_interval Commit interval.
144
145 -- OUT : p_msg_data Actual message in encoded format.
146 -- p_msg_count Holds the number of messages in the API list.
147 -- p_return_status Return status of the API (Includes 'S','E','U').
148
149 -- End of Comments
150 --------------------------------------------------------------------------------------------------
151
152 PROCEDURE Update_Approver(p_update_person IN VARCHAR2,
153 p_old_personid IN NUMBER,
154 p_new_personid IN NUMBER,
155 p_document_type IN VARCHAR2,
156 p_document_no_from IN VARCHAR2,
157 p_document_no_to IN VARCHAR2,
158 p_date_from IN DATE,
159 p_date_to IN DATE,
160 p_supplier_id IN NUMBER,
161 p_include_close_po IN VARCHAR2,
162 p_commit_interval IN NUMBER,
163 p_msg_data OUT NOCOPY VARCHAR2,
164 p_msg_count OUT NOCOPY NUMBER,
165 p_return_status OUT NOCOPY VARCHAR2);
166
167 --------------------------------------------------------------------------------------------------
168 -- Start of Comments
169
170 -- API Name : Update_Deliver_To
171 -- Type : Private
172 -- Pre-reqs : None
173 -- Function : Updates the old deliver to person with the new deliver to person provided.
174
175 -- Parameters :
176
177 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
178 -- p_old_personid Id of the old person.
179 -- p_new_personid Id of the new person.
180 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
181 -- p_document_no_from Document number from.
182 -- p_document_no_to Document number to.
183 -- p_date_from Date from.
184 -- p_date_to Date to.
185 -- p_supplier_id Supplier id.
186 -- p_include_close_po Include Close PO's or not (Value as Yes or No).
187 -- p_commit_interval Commit interval.
188
189 -- OUT : p_msg_data Actual message in encoded format.
190 -- p_msg_count Holds the number of messages in the API list.
191 -- p_return_status Return status of the API (Includes 'S','E','U').
192
193 -- End of Comments
194 --------------------------------------------------------------------------------------------------
195
196 PROCEDURE Update_Deliver_To(p_update_person IN VARCHAR2,
197 p_old_personid IN NUMBER,
198 p_new_personid IN NUMBER,
199 p_document_type IN VARCHAR2,
200 p_document_no_from IN VARCHAR2,
201 p_document_no_to IN VARCHAR2,
202 p_date_from IN DATE,
203 p_date_to IN DATE,
204 p_supplier_id IN NUMBER,
205 p_include_close_po IN VARCHAR2,
206 p_commit_interval IN NUMBER,
207 p_msg_data OUT NOCOPY VARCHAR2,
208 p_msg_count OUT NOCOPY NUMBER,
209 p_return_status OUT NOCOPY VARCHAR2);
210
211 --------------------------------------------------------------------------------------------------
212 -- Start of Comments
213
214 -- API Name : Print_Output
215 -- Type : Private
216 -- Pre-reqs : None
217 -- Function : Prints the header and body of the output file showing the documents and
218 -- document types updated along with the person who have been updated in the
219 -- document.
220
221 -- Parameters :
222
223 -- IN : p_update_person Person needs to be updated(Buyer/Approver/Deliver_To).
224 -- p_old_buyer_name Buyer name of the old person.
225 -- p_new_buyer_name Buyer name of the new person.
226 -- p_org_name Operating unit name.
227 -- p_document_type Type of the document(STANDARD,BLANKET.CONTRACT,PLANNED).
228 -- p_document_no_from Document number from.
229 -- p_document_no_to Document number to.
230 -- p_date_from Date from.
231 -- p_date_to Date to.
232 -- p_supplier_name Supplier name.
233
234 -- OUT : p_msg_data Actual message in encoded format.
235 -- p_msg_count Holds the number of messages in the API list.
236 -- p_return_status Return status of the API (Includes 'S','E','U').
237
238 -- End of Comments
239 --------------------------------------------------------------------------------------------------
240
241 PROCEDURE Print_Output(p_update_person IN VARCHAR2,
242 p_old_buyer_name IN VARCHAR2,
243 p_new_buyer_name IN VARCHAR2,
244 p_org_name IN VARCHAR2,
245 p_document_type IN VARCHAR2,
246 p_document_no_from IN VARCHAR2,
247 p_document_no_to IN VARCHAR2,
248 p_date_from IN DATE,
249 p_date_to IN DATE,
250 p_supplier_name IN VARCHAR2,
251 p_msg_data OUT NOCOPY VARCHAR2,
252 p_msg_count OUT NOCOPY NUMBER,
253 p_return_status OUT NOCOPY VARCHAR2);
254
255 --------------------------------------------------------------------------------------------------
256 -- Start of Comments
257
258 -- API Name : Buyer_Info
259 -- Type : Private
260 -- Pre-reqs : None
261 -- Function : Gets the Buyer Information.
262
263 -- Parameters :
264
265 -- IN : p_old_personid Id of the old person.
266 -- p_new_personid Id of the new person.
267 -- p_supplier_id Supplier id.
268
269 -- OUT : p_old_buyer_name Buyer name of the old person.
270 -- p_new_buyer_name Buyer name of the new person.
271 -- p_old_username User name of the old person.
272 -- p_new_username User name of the new person.
273 -- p_new_user_display_name Display name of the new person.
274 -- p_old_buyer_user_id Old person's Buyer user id.
275 -- p_new_buyer_user_id New person's Buyer user id.
276 -- p_supplier_name Supplier name.
277 -- p_org_name Operating unit name.
278 -- p_msg_data Actual message in encoded format.
279 -- p_msg_count Holds the number of messages in the API list.
280 -- p_return_status Return status of the API (Includes 'S','E','U').
281
282 -- End of Comments
283 --------------------------------------------------------------------------------------------------
284
285 PROCEDURE Buyer_Info(p_old_personid IN NUMBER,
286 p_new_personid IN NUMBER,
287 p_supplier_id IN NUMBER,
288 p_old_buyer_name OUT NOCOPY VARCHAR2,
289 p_new_buyer_name OUT NOCOPY VARCHAR2,
290 p_old_username OUT NOCOPY VARCHAR2,
291 p_new_username OUT NOCOPY VARCHAR2,
292 p_new_user_display_name OUT NOCOPY VARCHAR2,
293 p_old_buyer_user_id OUT NOCOPY NUMBER,
294 p_new_buyer_user_id OUT NOCOPY NUMBER,
295 p_supplier_name OUT NOCOPY VARCHAR2,
296 p_org_name OUT NOCOPY VARCHAR2,
297 p_msg_data OUT NOCOPY VARCHAR2,
298 p_msg_count OUT NOCOPY NUMBER,
299 p_return_status OUT NOCOPY VARCHAR2);
300
301 --------------------------------------------------------------------------------------------------
302
303 -- Functions declared to return the value of the parameters passed in this API.
304
305 --------------------------------------------------------------------------------------------------
306
307 FUNCTION get_old_personid RETURN NUMBER;
308
309 FUNCTION get_document_type RETURN VARCHAR2;
310
311 FUNCTION get_document_no_from RETURN VARCHAR2;
312
313 FUNCTION get_document_no_to RETURN VARCHAR2;
314
315 FUNCTION get_date_from RETURN DATE;
316
317 FUNCTION get_date_to RETURN DATE;
318
319 FUNCTION get_supplier_id RETURN NUMBER;
320
321 FUNCTION get_old_username RETURN VARCHAR2;
322
323
324 END PO_Mass_Update_PO_PVT;
[Home] [Help]