1 PACKAGE PA_DEDUCTIONS AUTHID CURRENT_USER AS
2 -- /* $Header: PADEDTXS.pls 120.0 2010/09/22 11:04:53 lamalviy noship $ */
3
4 CURSOR cur_dctn_hdr_info (c_dctn_req_id NUMBER) IS
5 SELECT *
6 FROM PA_DEDUCTIONS_ALL WHERE deduction_req_id = c_dctn_req_id AND status IN('WORKING','REJECTED','FAILED');
7
8 cur_dctn_hdr cur_dctn_hdr_info%ROWTYPE;
9
10 TYPE g_dctn_hdrid IS TABLE OF PA_DEDUCTIONS_ALL.DEDUCTION_REQ_ID%TYPE INDEX BY BINARY_INTEGER;
11
12 TYPE g_dctn_txnid IS TABLE OF PA_DEDUCTION_TRANSACTIONS_ALL.DEDUCTION_REQ_TRAN_ID%TYPE
13 INDEX BY BINARY_INTEGER;
14
15 TYPE g_dctn_hdr_rec IS RECORD (
16 deduction_req_id NUMBER(15),
17 project_id NUMBER(15),
18 vendor_id NUMBER(15),
19 vendor_site_id NUMBER,
20 change_doc_num VARCHAR2(30),
21 change_doc_type VARCHAR2(15),
22 ci_id NUMBER,
23 po_number VARCHAR2(20),
24 po_header_id NUMBER,
25 deduction_req_num VARCHAR2(30),
26 debit_memo_num VARCHAR2(30),
27 currency_code VARCHAR2(30),
28 conversion_ratetype VARCHAR2(30),
29 conversion_ratedate DATE,
30 conversion_rate NUMBER,
31 total_amount NUMBER,
32 deduction_req_date DATE,
33 debit_memo_date DATE,
34 description VARCHAR2(4000),
35 status VARCHAR2(15),
36 org_id NUMBER
37 );
38
39 TYPE g_dctn_txn_rec IS RECORD (
40 deduction_req_id NUMBER(15),
41 deduction_req_tran_id NUMBER(15),
42 project_id NUMBER(15),
43 task_id NUMBER(15),
44 expenditure_type VARCHAR2(30),
45 expenditure_item_date DATE,
46 gl_date DATE,
47 expenditure_org_id NUMBER(15),
48 quantity NUMBER,
49 override_quantity NUMBER,
50 expenditure_item_id NUMBER(15),
51 projfunc_currency_code VARCHAR2(30),
52 orig_projfunc_amount NUMBER,
53 override_projfunc_amount NUMBER,
54 conversion_ratetype VARCHAR2(30),
55 conversion_ratedate DATE,
56 conversion_rate NUMBER,
57 amount NUMBER,
58 description VARCHAR2(4000),
59 status VARCHAR2(15)
60 );
61
62 TYPE g_dctn_hdrtbl IS TABLE OF g_dctn_hdr_rec INDEX BY BINARY_INTEGER;
63 TYPE g_dctn_txntbl IS TABLE OF g_dctn_txn_rec INDEX BY BINARY_INTEGER;
64
65 g_validate_txn VARCHAR2(1) := 'N';
66 g_user_id NUMBER(15) := FND_GLOBAL.USER_ID;
67
68 /*---------------------------------------------------------------------------------------------------------
69 -- This procedure populates PA_DEDUCTIONS_ALL table after validating the data.
70 -- Input parameters
71 -- Parameters Type Required Description
72 -- p_dctn_hdr TABLE YES It stores the deduction header information
73 -- Out parameters
74 -- Parameters Type Required Description
75 -- p_return_status VARCHAR2 YES The return status of the APIs.
76 -- Valid values are:
77 -- S (API completed successfully),
78 -- E (business rule violation error) and
79 -- U(Unexpected error, such as an Oracle error.
80 -- p_msg_count NUMBER YES Holds the number of messages in the global message
81 table. Calling programs should use this as the
82 basis to fetch all the stored messages.
83 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
84 one error/warning message Otherwise the column is
85 left blank.
86 -- p_calling_mode VARCHAR2 YES Holds whether the call is being made from public
87 API or from the create deductions page. This is to
88 enforce additional validations in case if this is
89 called from Public API.
90 ----------------------------------------------------------------------------------------------------------*/
91
92 Procedure Create_Deduction_Hdr( p_dctn_hdr IN OUT NOCOPY g_dctn_hdrtbl
93 ,p_msg_count OUT NOCOPY NUMBER
94 ,p_msg_data OUT NOCOPY VARCHAR2
95 ,p_return_status OUT NOCOPY VARCHAR2
96 ,p_calling_mode IN VARCHAR2
97 );
98
99 /*---------------------------------------------------------------------------------------------------------
100 -- This procedure populates PA_DEDUCTION_TRANSACTIONS_ALL table after validating the data.
101 -- Input parameters
102 -- Parameters Type Required Description
103 -- p_dctn_dtl TABLE YES It stores the deduction transactions information
104 -- Out parameters
105 -- Parameters Type Required Description
106 -- p_return_status VARCHAR2 YES The return status of the APIs.
107 -- Valid values are:
108 -- S (API completed successfully),
109 -- E (business rule violation error) and
110 -- U(Unexpected error, such as an Oracle error.
111 -- p_msg_count NUMBER YES Holds the number of messages in the global message
112 table. Calling programs should use this as the
113 basis to fetch all the stored messages.
114 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
115 one error/warning message Otherwise the column is
116 left blank.
117 -- p_calling_mode VARCHAR2 YES Holds whether the call is being made from public
118 API or from the create deductions page. This is to
119 enforce additional validations in case if this is
120 called from Public API.
121 ----------------------------------------------------------------------------------------------------------*/
122 Procedure Create_Deduction_Txn( p_dctn_dtl IN OUT NOCOPY g_dctn_txntbl
123 ,p_msg_count OUT NOCOPY NUMBER
124 ,p_msg_data OUT NOCOPY VARCHAR2
125 ,p_return_status OUT NOCOPY VARCHAR2
126 ,p_calling_mode IN VARCHAR2
127 );
128
129
130 /*---------------------------------------------------------------------------------------------------------
131 -- This procedure is to update existing data in PA_DEDUCTIONS_ALL table after validating the data.
132 -- Input parameters
133 -- Parameters Type Required Description
134 -- p_dctn_hdr TABLE YES It stores the deduction header information
135 -- Out parameters
136 -- Parameters Type Required Description
137 -- p_return_status VARCHAR2 YES The return status of the APIs.
138 -- Valid values are:
139 -- S (API completed successfully),
140 -- E (business rule violation error) and
141 -- U(Unexpected error, such as an Oracle error.
142 -- p_msg_count NUMBER YES Holds the number of messages in the global message
143 table. Calling programs should use this as the
144 basis to fetch all the stored messages.
145 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
146 one error/warning message Otherwise the column is
147 left blank.
148 -- p_calling_mode VARCHAR2 YES Holds whether the call is being made from public
149 API or from the create deductions page. This is to
150 enforce additional validations in case if this is
151 called from Public API.
152 ----------------------------------------------------------------------------------------------------------*/
153 Procedure Update_Deduction_Hdr( p_dctn_hdr IN OUT NOCOPY g_dctn_hdrtbl
154 ,p_msg_count OUT NOCOPY NUMBER
155 ,p_msg_data OUT NOCOPY VARCHAR2
156 ,p_return_status OUT NOCOPY VARCHAR2
157 ,p_calling_mode IN VARCHAR2
158 );
159
160 /*---------------------------------------------------------------------------------------------------------
161 -- This procedure is to update existing data in PA_DEDUCTION_TRANSACTIONS_ALL table after validating the data.
162 -- Input parameters
163 -- Parameters Type Required Description
164 -- p_dctn_dtl TABLE YES It stores the deduction transactions information
165 -- Out parameters
166 -- Parameters Type Required Description
167 -- p_return_status VARCHAR2 YES The return status of the APIs.
168 -- Valid values are:
169 -- S (API completed successfully),
170 -- E (business rule violation error) and
171 -- U(Unexpected error, such as an Oracle error.
172 -- p_msg_count NUMBER YES Holds the number of messages in the global message
173 table. Calling programs should use this as the
174 basis to fetch all the stored messages.
175 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
176 one error/warning message Otherwise the column is
177 left blank.
178 -- p_calling_mode VARCHAR2 YES Holds whether the call is being made from public
179 API or from the create deductions page. This is to
180 enforce additional validations in case if this is
181 called from Public API.
182 ----------------------------------------------------------------------------------------------------------*/
183 Procedure Update_Deduction_Txn( p_dctn_dtl IN OUT NOCOPY g_dctn_txntbl
184 ,p_msg_count OUT NOCOPY NUMBER
185 ,p_msg_data OUT NOCOPY VARCHAR2
186 ,p_return_status OUT NOCOPY VARCHAR2
187 ,p_calling_mode IN VARCHAR2
188 );
189
190 /*---------------------------------------------------------------------------------------------------------
191 -- This procedure is to delete existing data in PA_DEDUCTIONS_ALL table after validating the data.
192 -- Input parameters
193 -- Parameters Type Required Description
194 -- p_dctn_hdrid TABLE YES It stores the array of deducion requests
195 -- Out parameters
196 -- Parameters Type Required Description
197 -- p_return_status VARCHAR2 YES The return status of the APIs.
198 -- Valid values are:
199 -- S (API completed successfully),
200 -- E (business rule violation error) and
201 -- U(Unexpected error, such as an Oracle error.
202 -- p_msg_count NUMBER YES Holds the number of messages in the global message
203 table. Calling programs should use this as the
204 basis to fetch all the stored messages.
205 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
206 one error/warning message Otherwise the column is
207 left blank.
208 ----------------------------------------------------------------------------------------------------------*/
209 Procedure Delete_Deduction_Hdr( p_dctn_hdrid g_dctn_hdrid
210 ,p_msg_count OUT NOCOPY NUMBER
211 ,p_msg_data OUT NOCOPY VARCHAR2
212 ,p_return_status OUT NOCOPY VARCHAR2
213 );
214
215 /*---------------------------------------------------------------------------------------------------------
216 -- This procedure is to delete existing data in PA_DEDUCTION_TRANSACTIONS_ALL table after validating the data.
217 -- Input parameters
218 -- Parameters Type Required Description
219 -- p_dctn_txnid TABLE YES It stores the array of deducion request transactions
220 -- Out parameters
221 -- Parameters Type Required Description
222 -- p_return_status VARCHAR2 YES The return status of the APIs.
223 -- Valid values are:
224 -- S (API completed successfully),
225 -- E (business rule violation error) and
226 -- U(Unexpected error, such as an Oracle error.
227 -- p_msg_count NUMBER YES Holds the number of messages in the global message
228 table. Calling programs should use this as the
229 basis to fetch all the stored messages.
230 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
231 one error/warning message Otherwise the column is
232 left blank.
233 ----------------------------------------------------------------------------------------------------------*/
234
235 Procedure Delete_Deduction_Txn(p_dctn_txnid g_dctn_txnid
236 ,p_msg_count OUT NOCOPY NUMBER
237 ,p_msg_data OUT NOCOPY VARCHAR2
238 ,p_return_status OUT NOCOPY VARCHAR2
239 );
240
241 /*---------------------------------------------------------------------------------------------------------
242 -- This procedure is to validate Deduction header information and return the result to the called proc.
243 -- Input parameters
244 -- Parameters Type Required Description
245 -- p_dctn_hdr TABLE YES It stores the deduction header information
246 -- Out parameters
247 -- Parameters Type Required Description
248 -- p_return_status VARCHAR2 YES The return status of the APIs.
249 -- Valid values are:
250 -- S (API completed successfully),
251 -- E (business rule violation error) and
252 -- U(Unexpected error, such as an Oracle error.
253 -- p_msg_count NUMBER YES Holds the number of messages in the global message
254 table. Calling programs should use this as the
255 basis to fetch all the stored messages.
256 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
257 one error/warning message Otherwise the column is
258 left blank.
259 -- p_calling_mode VARCHAR2 YES Holds whether the call is being made from public
260 API or from the create deductions page. This is to
261 enforce additional validations in case if this is
262 called from Public API.
263 ----------------------------------------------------------------------------------------------------------*/
264 Function Validate_Deduction_Hdr( p_dctn_hdr IN OUT NOCOPY g_dctn_hdrtbl
265 ,p_msg_count OUT NOCOPY NUMBER
266 ,p_msg_data OUT NOCOPY VARCHAR2
267 ,p_return_status OUT NOCOPY VARCHAR2
268 ,p_calling_mode IN VARCHAR2:=''
269 )
270 Return Boolean;
271
272 /*---------------------------------------------------------------------------------------------------------
273 -- This procedure is to validate Deduction header information and return the result to the called proc.
274 -- Input parameters
275 -- Parameters Type Required Description
276 -- p_dctn_dtl TABLE YES It stores deduction request transactions information
277 -- Out parameters
278 -- Parameters Type Required Description
279 -- p_return_status VARCHAR2 YES The return status of the APIs.
280 -- Valid values are:
281 -- S (API completed successfully),
282 -- E (business rule violation error) and
283 -- U(Unexpected error, such as an Oracle error.
284 -- p_msg_count NUMBER YES Holds the number of messages in the global message
285 table. Calling programs should use this as the
286 basis to fetch all the stored messages.
287 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
288 one error/warning message Otherwise the column is
289 left blank.
290 -- p_calling_mode VARCHAR2 YES Holds whether the call is being made from public
291 API or from the create deductions page. This is to
292 enforce additional validations in case if this is
293 called from Public API.
294 ----------------------------------------------------------------------------------------------------------*/
295 Function Validate_Deduction_Txn( p_dctn_dtl IN OUT NOCOPY g_dctn_txntbl
296 ,p_msg_count OUT NOCOPY NUMBER
297 ,p_msg_data OUT NOCOPY VARCHAR2
298 ,p_return_status OUT NOCOPY VARCHAR2
299 ,p_calling_mode IN VARCHAR2:=''
300 )
301 Return Boolean;
302
303 /*---------------------------------------------------------------------------------------------------------
304 -- This procedure is to submit the deducion request for approval thereby for the creation of Debit memo.
305 -- This is being called on pressing the submit button on Create deductions page.
306 -- Input parameters
307 -- Parameters Type Required Description
308 -- p_dctn_req_id NUMBER YES Deduction request id for which debit memo needs
309 to be raised
310 -- Out parameters
311 -- Parameters Type Required Description
312 -- p_return_status VARCHAR2 YES The return status of the APIs.
313 -- Valid values are:
314 -- S (API completed successfully),
315 -- E (business rule violation error) and
316 -- U(Unexpected error, such as an Oracle error.
317 -- p_msg_count NUMBER YES Holds the number of messages in the global message
318 table. Calling programs should use this as the
319 basis to fetch all the stored messages.
320 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
321 one error/warning message Otherwise the column is
322 left blank.
323 ----------------------------------------------------------------------------------------------------------*/
324 Procedure Submit_For_DebitMemo ( p_dctn_req_id IN PA_DEDUCTIONS_ALL.deduction_req_id%TYPE
325 ,p_msg_count OUT NOCOPY NUMBER
326 ,p_msg_data OUT NOCOPY VARCHAR2
327 ,p_return_status OUT NOCOPY VARCHAR2
328 );
329
330 /*---------------------------------------------------------------------------------------------------------
331 -- This procedure is to raise a debit memo in payables. This is being called from deduction request
332 -- approval workflow on deduction request's approval.
333 -- Input parameters
334 -- Parameters Type Required Description
335 -- p_dctn_req_id NUMBER YES Deduction request id for which debit memo needs
336 to be raised
337 -- Out parameters
338 -- Parameters Type Required Description
339 -- p_return_status VARCHAR2 YES The return status of the APIs.
340 -- Valid values are:
341 -- S (API completed successfully),
342 -- E (business rule violation error) and
343 -- U(Unexpected error, such as an Oracle error.
344 -- p_msg_count NUMBER YES Holds the number of messages in the global message
345 table. Calling programs should use this as the
346 basis to fetch all the stored messages.
347 -- p_msg_data VARCHAR2 YES Holds the message code, if the API returned only
348 one error/warning message Otherwise the column is
349 left blank.
350 ----------------------------------------------------------------------------------------------------------*/
351 Procedure Submit_For_DebitMemo ( p_dctn_hdr_rec IN cur_dctn_hdr_info%ROWTYPE
352 ,p_msg_count OUT NOCOPY NUMBER
353 ,p_msg_data OUT NOCOPY VARCHAR2
354 ,p_return_status OUT NOCOPY VARCHAR2
355 );
356
357 /*---------------------------------------------------------------------------------------------------------
358 -- This function is to return the list of invoices which are assoiciated to a debit memo that is created
359 -- out of a deduction request.
360 -- Input parameters
361 -- Parameters Type Required Description
362 -- p_vendor_Id NUMBER YES vendor id
363 -- p_vendor_site_id NUMBER YES vendor site id
364 -- ded_req_num VARCHAR2 YES deduction request id
365 ----------------------------------------------------------------------------------------------------------*/
366 function invoice_dm_map(p_vendor_id NUMBER,
367 p_vendor_site_id NUMBER,
368 ded_req_num IN VARCHAR2) return VARCHAR2;
369
370
371
372 /*---------------------------------------------------------------------------------------------------------
373 -- This procedure is to raise a debit memo in payables. This is being called from concurrenmt program
374 -- Input parameters
375 -- Parameters Type Required Description
376 -- p_dctn_req_id NUMBER YES deduction request id
377 ----------------------------------------------------------------------------------------------------------*/
378 Procedure Import_DebitMemo ( errbuf OUT NOCOPY varchar2,
379 ret_code OUT NOCOPY varchar2,
380 p_dctn_req_id IN NUMBER
381 );
382
383 /*Bug#9498500:Moved the procedure validate_unprocessed_ded() to PAAPVALS/B.pls
384 ---------------------------------------------------------------------------------------------------------
385 -- This procedure is to validate a retention invoice in payables. This is being called from Payables
386 -- Input parameters
387 -- Parameters Type Required Description
388 -- invoice_id NUMBER YES invoice_id being validated
389 -- cmt_exist_flag VARCHAR returns whether unprocessed dedns exist
390 ----------------------------------------------------------------------------------------------------------
391 Procedure validate_unprocessed_ded ( invoice_id IN ap_invoices_all.invoice_id%type,
392 cmt_exist_flag OUT NOCOPY VARCHAR2);
393 */
394
395 END PA_DEDUCTIONS;
[Home] [Help]