1 PACKAGE jai_ap_dtc_generation_pkg
2 /* $Header: jai_ap_dtc_gen.pls 120.15.12020000.2 2013/03/22 02:55:09 cholei noship $ */
3 AS
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_inv_dtc_generation_pkg_s.sql
6
7 Created By : Zhhou
8
9 Created Date : 19-Dec-2011
10
11 Bug :
12
13 Purpose : Implementation of tax defaultation functionality on AP invoice.
14
15 Called from : Trigger ja_ap_aid_before_trg
16
17
18 CHANGE HISTORY:
19 -------------------------------------------------------------------------------
20 S.No Date Author and Details
21 -------------------------------------------------------------------------------
22 1. 19/12/2011 Create this package for DTC copied from TDS
23
24
25
26 ---------------------------------------------------------------------------- */
27 g_inr_currency_rounding NUMBER := 2;--Update by Zhiwei Hou on 20120111
28 g_fcy_currency_rounding NUMBER := 2;
29 -- added, Harshita for Bug#5131075(5346558)
30 gn_invoice_id NUMBER ;
31 gv_request_id NUMBER ;
32
33 -- Bug 5722028. Added by csahoo
34 gn_tds_rounding_factor NUMBER;
35 gd_tds_rounding_effective_date DATE;
36
37 FUNCTION get_rnded_value (p_tax_amount in number )
38 RETURN NUMBER ;
39 -- End for bug 5722028.
40
41 procedure status_update_chk_validate
42 (
43 p_invoice_id in number,
44 p_invoice_line_number in number default null, /* AP lines uptake */
45 p_invoice_distribution_id in number default null,
46 p_match_status_flag in varchar2 default null,
47 p_is_invoice_validated out nocopy varchar2,
48 p_process_flag out nocopy varchar2,
49 p_process_message out nocopy varchar2,
50 p_codepath in out nocopy varchar2
51 );
52
53
54
55
56 procedure generate_tds_invoices
57 (
58 pn_invoice_id in number,
59 pn_invoice_line_number in number default null, /* AP lines */
60 pn_invoice_distribution_id in number default null, /* Prepayment apply / unapply scenario */
61 pv_invoice_num_prepay_apply in varchar2 default null, /* Prepayment application secanrio */
62 pv_invoice_num_to_tds_apply in varchar2 default null, /* Prepayment unapplication secanrio */
63 pv_invoice_num_to_vendor_apply in varchar2 default null, /* Prepayment unapplication secanrio */
64 pv_invoice_num_to_vendor_can in varchar2 default null, /* Invoice Cancel Secnario */
65 pn_threshold_hdr_id in number default null, /* For validate scenario only */
66 pn_taxable_amount in number,
67 pn_tax_amount in number,
68 pn_tax_id in number,
69 pv_section_code in varchar2, --Added by Chong for bug#16048702
70 pd_accounting_date in date,
71 pv_tds_event in varchar2,
72 pn_threshold_grp_id in number,
73 pv_tds_invoice_num out nocopy varchar2,
74 pv_cm_invoice_num out nocopy varchar2,
75 pn_threshold_trx_id out nocopy number,
76 -- Bug 5722028. Added by csahoo
77 pd_creation_date in date,
78 pn_calc_tax_amount in number default 0, /* Bug 12965614 */
79 p_process_flag out nocopy varchar2,
80 p_process_message out nocopy varchar2
81 );
82
83 procedure process_threshold_transition
84 (
85 p_threshold_grp_id in number,
86 p_threshold_slab_id in number,
87 p_invoice_id in number,
88 p_vendor_id in number,
89 p_vendor_site_id in number,
90 p_accounting_date in date,
91 p_tds_event in varchar2,
92 p_org_id in number,
93 --Added by Chong.Lei for bug#13787158 begin
94 pn_prepayment_inovice_id IN NUMBER DEFAULT NULL,
95 pn_unapply_amount IN NUMBER DEFAULT NULL,
96 --Added by Chong.Lei for bug#13787158 end
97 pv_tds_invoice_num out nocopy varchar2,
98 pv_cm_invoice_num out nocopy varchar2,
99 p_process_flag out nocopy varchar2,
100 p_process_message out nocopy varchar2
101 );
102
103 procedure import_and_approve
104 (
105 p_invoice_id in number,
106 p_start_thhold_trx_id in number,
107 p_tds_event in varchar2,
108 p_process_flag out nocopy varchar2,
109 p_process_message out nocopy varchar2
110 ) ;
111
112 procedure approve_tds_invoices
113 (
114 errbuf out nocopy varchar2,
115 retcode out nocopy varchar2,
116 p_parent_request_id in number,
117 p_invoice_id in number,
118 p_vendor_id in number,
119 p_vendor_site_id in number,
120 p_start_thhold_trx_id in number
121 );
122
123 /* Following procedure is called from ja_in_ap_aia_before_trg to update the ids of the TDS invoices */
124 procedure populate_tds_invoice_id
125 (
126 p_invoice_id in number,
127 p_invoice_num in varchar2,
128 p_vendor_id in number,
129 p_vendor_site_id in number,
130 p_process_flag out nocopy varchar2,
131 p_process_message out nocopy varchar2
132 );
133
134 procedure maintain_thhold_grps
135 (
136 p_threshold_grp_id in out nocopy number ,
137 p_vendor_id in number default null,
138 p_org_tan_num in varchar2 default null,
139 p_vendor_pan_num in varchar2 default null,
140 p_section_type in varchar2 default null,
141 p_section_code in varchar2 default null,
142 p_fin_year in number default null,
143 p_org_id in number default null,
144 p_trx_invoice_amount in number default null,
145 p_trx_invoice_cancel_amount in number default null,
146 p_trx_invoice_apply_amount in number default null,
147 p_trx_invoice_unapply_amount in number default null,
148 p_trx_tax_paid in number default null,
149 p_trx_thhold_change_tax_paid in number default null,
150 p_trx_threshold_slab_id in number default null,
151 p_tds_event in varchar2,
152 p_invoice_id in number default null,
153 p_invoice_line_number in number default null, /* AP lines Uptake */
154 p_invoice_distribution_id in number default null,
155 p_remarks in varchar2 default null,
156 --Added by zhiwei for Bug#13359892 DTC enhancement on 20111130 begin
157 -----------------------------------------------------------------------------------
158 p_threshold_hdr_id in number default null,
159 -----------------------------------------------------------------------------------
160 --Added by zhiwei for Bug#13359892 DTC enhancement on 20111130 end
161 -- Bug 5722028. Added by csahoo
162 p_creation_date in date default sysdate,
163 p_threshold_grp_audit_id out nocopy number,
164 p_process_flag out nocopy varchar2,
165 P_process_message out nocopy varchar2,
166 p_codepath in out nocopy varchar2
167 );
168
169 procedure insert_tds_thhold_trxs --4333449
170 (
171 p_invoice_id in number,
172 p_tds_event in varchar2,
173 p_tax_id in number default null,
174 p_tax_rate in number default null,
175 p_taxable_amount in number default null,
176 p_tax_amount in number default null,
177 p_tds_authority_vendor_id in number default null,
178 p_tds_authority_vendor_site_id in number default null,
179 p_invoice_tds_authority_num in varchar2 default null,
180 p_invoice_tds_authority_type in varchar2 default null,
181 p_invoice_tds_authority_curr in varchar2 default null,
182 p_invoice_tds_authority_amt in number default null,
183 p_invoice_tds_authority_id in number default null,
184 p_vendor_id in number default null,
185 p_vendor_site_id in number default null,
186 p_invoice_vendor_num in varchar2 default null,
187 p_invoice_vendor_type in varchar2 default null,
188 p_invoice_vendor_curr in varchar2 default null,
189 p_invoice_vendor_amt in number default null,
190 p_invoice_vendor_id in number default null,
191 p_parent_inv_payment_priority in number default null,
192 p_parent_inv_exchange_rate in number default null
193 );
194
195 -- added, Harshita for Bug 5096787
196 Procedure create_tds_after_holds_release
197 (
198 errbuf out nocopy varchar2,
199 retcode out nocopy varchar2,
200 p_invoice_id IN number ,
201 p_invoice_amount IN number ,
202 p_payment_status_flag IN varchar2 ,
203 p_invoice_type_lookup_code IN varchar2 ,
204 p_vendor_id IN number ,
205 p_vendor_site_id IN number ,
206 p_accounting_date IN DATE ,
207 p_invoice_currency_code IN varchar2 ,
208 p_exchange_rate IN number ,
209 p_set_of_books_id IN number ,
210 p_org_id IN number ,
211 p_call_from IN varchar2 ,
212 p_process_flag IN varchar2 ,
213 p_process_message IN varchar2 ,
214 p_codepath IN varchar2 ,
215 p_request_id IN number default null-- added, Harshita for Bug#5131075(5346558)
216 ) ;
217 -- ended, Harshita for Bug 5096787
218
219 --Procedure Added by Sanjikum for Bug#5131075(4718907)
220 PROCEDURE get_tds_threshold_slab(
221 p_prepay_distribution_id IN NUMBER,
222 p_invoice_id IN NUMBER, --Added by Chong.Lei for DTC enhancement Bug#13359892
223 p_threshold_grp_id IN OUT NOCOPY NUMBER,
224 p_threshold_hdr_id IN OUT NOCOPY NUMBER,
225 p_effective_invoice_amt IN NUMBER DEFAULT null, --Added by Chong.Lei for DTC enhancement Bug#13359892
226 p_threshold_slab_id OUT NOCOPY NUMBER,
227 p_threshold_type OUT NOCOPY VARCHAR2,
228 p_process_flag OUT NOCOPY VARCHAR2,
229 p_process_message OUT NOCOPY VARCHAR2,
230 p_codepath IN OUT NOCOPY VARCHAR2);
231
232 --Procedure Added by Sanjikum for Bug#5131075(4718907)
233 PROCEDURE process_threshold_rollback(
234 p_invoice_id IN VARCHAR2,
235 p_before_threshold_type IN VARCHAR2,
236 p_after_threshold_type IN VARCHAR2,
237 p_before_threshold_slab_id IN NUMBER,
238 p_after_threshold_slab_id IN NUMBER,
239 p_threshold_grp_id IN NUMBER,
240 p_org_id IN NUMBER,
241 p_accounting_date IN DATE,
242 p_invoice_distribution_id IN NUMBER DEFAULT NULL,
243 p_prepay_distribution_id IN NUMBER DEFAULT NULL,
244 p_called_from IN VARCHAR2,
245 p_process_flag OUT NOCOPY VARCHAR2,
246 p_process_message OUT NOCOPY VARCHAR2,
247 p_codepath IN OUT NOCOPY VARCHAR2);
248 PROCEDURE get_prepay_invoice_id
249 (
250 p_prepay_inv_dist_id NUMBER,
251 p_prepay_inv_id OUT NOCOPY NUMBER
252
253 );
254
255 --Added by Zhiwei for DTC enhancement Bug#13359892 begin
256 -----------------------------------------------------------------
257 /* FUNCTION get_natural_account(cn_ccid number)RETURN number;*/
258
259 /* --Commented by Chong for eDTC bug#16414088 on 20130118 Start
260 procedure populate_repository
261 (
262 errbuf OUT NOCOPY VARCHAR2 ,
263 retcode OUT NOCOPY VARCHAR2 ,
264 pd_from_date in varchar2,
265 pd_to_date in varchar2
266 );
267 --Commented by Chong for eDTC bug#16414088 on 20130118 end */
268
269 procedure generate_dtc_invoices
270 (
271 pn_invoice_id in number,
272 pn_invoice_line_number in number default null, /* AP lines */
273 pn_invoice_distribution_id in number default null, /* Prepayment apply / unapply scenario */
274 pv_invoice_num_prepay_apply in varchar2 default null, /* Prepayment application secanrio */
278 pn_threshold_hdr_id in number default null, /* For validate scenario only */
275 pv_invoice_num_to_tds_apply in varchar2 default null, /* Prepayment unapplication secanrio */
276 pv_invoice_num_to_vendor_apply in varchar2 default null, /* Prepayment unapplication secanrio */
277 pv_invoice_num_to_vendor_can in varchar2 default null, /* Invoice Cancel Secnario */
279 pn_taxable_amount in number,
280 pn_tax_amount in number,
281 --pn_tax_id in number,
282 pn_tax_category_id in number,
283 pv_section_type in varchar2,
284 pv_section_code in varchar2,
285 pd_accounting_date in date,
286 pv_tds_event in varchar2,
287 pn_threshold_grp_id in number,
288 pv_tds_invoice_num out nocopy varchar2,
289 pv_cm_invoice_num out nocopy varchar2,
290 pn_threshold_trx_id out nocopy number,
291 pd_creation_date in date,
292 pn_calc_tax_amount in number default 0, /* Added for bug 12965614 */
293 p_process_flag out nocopy varchar2,
294 p_process_message out nocopy varchar2
295 );
296 procedure process_dtc_at_inv_validate
297 (
298 p_invoice_id in number,
299 p_vendor_id in number,
300 p_vendor_site_id in number,
301 p_accounting_date in date,
302 p_invoice_currency_code in varchar2,
303 p_exchange_rate in number,
304 p_set_of_books_id in number,
305 p_org_id in number,
306 p_call_from in varchar2,
307 p_creation_date in date,
308 p_process_flag out nocopy varchar2,
309 p_process_message out nocopy varchar2,
310 p_codepath in out nocopy varchar2
311 );
312 ------------------------------------------------------------------
313 --Added by Zhiwei for DTC enhancement Bug#13359892 end
314 FUNCTION get_tax_rounding(pn_invoice_id in number) RETURN NUMBER; -- Added by Wenqiong 20120111
315 --Added by Zhiwei Hou on 20120114 begin
316 Function get_natural_account_value(
317 pn_invoice_id number,
318 pn_invoice_distribution_id number,
319 pv_segment_name varchar2
320 )return varchar2;
321 FUNCTION chk_prepay_inv(pn_invoice_id number)return number;
322 FUNCTION chk_no_prepay_inv(pn_invoice_id number)return number;
323 --Added by Zhiwei Hou on 20120114 end
324
325
326 --Added by Zhiwei Hou on 20120202 begin
327 --------------------------------------------------------------------
328
329 procedure chk_tax_exists(
330 pn_invoice_id number,
331 pn_count OUT NOCOPY NUMBER
332 );
333
334 --------------------------------------------------------------------
335 --Added by Zhiwei Hou on 20120202 begin
336
337
338 --Added by Zhiwei Hou for Bug#13740826 begin
339 -----------------------------------------------------
340 procedure get_amount_change(
341 pn_invoice_id number,
342 pn_diff OUT NOCOPY NUMBER
343 );
344 -----------------------------------------------------
345 --Added by Zhiwei Hou for Bug#13740826 end
346 --Added by Chong.lei Bug#13782492 on 20120301 begin
347 --------------------------------------------------------------------
348 procedure chk_accounting_date(
349 pn_invoice_id number,
350 pn_cnt OUT NOCOPY NUMBER
351 );
352 --------------------------------------------------------------------
353 --Added by Chong.lei Bug#13782492 on 20120301 end
354
355 --Added by Zhiwei Hou for Bug#13818183 on 20120308 begin
356 ---------------------------------------------------------
357 procedure get_diff_section_code(
358 pn_invoice_id number,
359 pn_diff out nocopy number
360 );
361 ----------------------------------------------------------
362 --Added by Zhiwei Hou for Bug#13818183 on 20120308 end
363
364
365 --Added by Chong.lei on 20120202 begin
366 --------------------------------------------------------------------
367 procedure generate_rollback_dtc_invoices
368 ( pn_invoice_id in number,
369 pn_threshold_grp_id in number,
370 pn_threshold_hdr_id in number default null, /* For validate scenario only */
371 pv_section_type in varchar2,
372 pv_section_code in varchar2,
373 pn_taxable_amount in number,
374 pn_tax_amount in number,
375 pd_accounting_date in date,
376 pv_tds_event in varchar2,
377 pv_tds_invoice_num out nocopy varchar2,
378 pv_cm_invoice_num out nocopy varchar2,
379 pn_threshold_trx_id out nocopy number,
380 pd_creation_date in date,
381 pn_calc_tax_amount in number default 0, /* Added for bug 12965614 */
382 p_process_flag out nocopy varchar2,
383 p_process_message out nocopy varchar2,
384 p_codepath in out nocopy varchar2
385 );
386 --Added by Chong.Lei on 20120202 begin
387
388 GV_MODULE_PREFIX VARCHAR2 (100) := 'JAI_AP_DTC_GENERATION_PKG';--Added by Zhiwei Hou on 20120203
389 end jai_ap_dtc_generation_pkg;
[Home] [Help]