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