1 PACKAGE jai_ar_trx_pkg AUTHID CURRENT_USER AS
2 /* $Header: jai_ar_trx.pls 120.6.12010000.2 2010/01/27 09:19:38 erma ship $ */
3
4 /*
5 --------------------------------------------------------------------------------------
6 Filename:
7
8 Change History:
9
10 Date Bug Remarks
11 --------- ---------- -------------------------------------------------------------
12 08-Jun-2005 Version 116.1 jai_ar_trx -Object is Modified to refer to New DB Entity names in
13 place of Old DB Entity Names as required for CASE COMPLAINCE.
14
15 23-06-2005 Version 116.2
16 Ramananda for bug#4468353 due to ebtax uptake by AR
17
18 28/12/2005 4892111 Hjujjuru, File Version 120.3
19
20 Modified the Hard Coded value of the tax 'Localization' to 'LOCALIZATION'
21 in all its occurences
22
23 05-Jul-2006 Aiyer for the bug 5369250, Version 120.5
24 Issue:-
25 The concurrent failes with the following error :-
26 "FDPSTP failed due to ORA-01861: literal does not match format string ORA-06512: at line 1 "
27
28 Reason:-
29 The procedure update_excise_invoice_no has two parameters p_start_date and p_end_date which are of type date , however the concurrent program
30 passes it in the canonical format and hence the failure.
31
32 Fix:-
33 Modified the procedure update_excise_invoice_no.
34 Changed the datatype of p_start_date and p_end_date from date to varchar2 as this parameter.
35 Also added the new parameters ld_start_date and ld_end_date. The values in p_start_date and p_end_date would be converted to date format and
36 stored in these local variables
37
38 Dependency due to this fix:-
39 None
40
41 -----------------------------------------------------------------------------------------------*/
42
43
44 /*-----------------------------------------------------------------------
45 | Added by Ramananda for bug#4468353 due to ebtax uptake by AR, start |
46 | |
47 | The following objects references the cursors created in the object |
48 | 1. jai_ar_match_tax.plb |
49 | 2. jai_ar_rcta_t7.sql |
50 | 3. jai_ar_trx.plb |
51 | 4. jai_jar_tl_t1.sql |
52 | 5. jai_jar_tl_t2.sql |
53 | 6. JAINARTX.fmb |
54 ------------------------------------------------------------------------*/
55 --Get the Tax_regime_Code
56 CURSOR c_tax_regime_code_cur(p_org_id IN NUMBER) IS
57 SELECT Zx_Migrate_Util.GET_TAX_REGIME(
58 'SALES_TAX',
59 p_org_id)
60 FROM dual ;
61
62 --Get the party_tax_profile_id
63 CURSOR c_party_tax_profile_id_cur(p_org_id IN NUMBER) IS
64 SELECT party_tax_profile_id
65 FROM zx_party_tax_profile zptp
66 WHERE party_id = p_org_id
67 and party_type_code = 'OU';
68
69 --Get the tax_rate_id
70 CURSOR c_tax_rate_id_cur (cp_tax_regime_code zx_rates_b.tax_regime_code%type, cp_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type) IS
71 SELECT tax_rate_id
72 FROM zx_rates_b zrb
73 WHERE zrb.tax = 'LOCALIZATION' -- 'Localization' , Harshita for Bug 4907217
74 AND zrb.tax_regime_code = cp_tax_regime_code
75 AND zrb.tax_status_code = 'STANDARD'
76 AND zrb.active_flag = 'Y'
77 AND zrb.content_owner_id = cp_party_tax_profile_id
78 AND trunc(sysdate) between trunc(zrb.effective_from) and trunc(nvl(zrb.effective_to, sysdate)) ;
79
80 --Get the max(tax_rate_id)
81 CURSOR c_max_tax_rate_id_cur (cp_tax_regime_code zx_rates_b.tax_regime_code%type) IS
82 SELECT max(tax_rate_id)
83 FROM zx_rates_b zrb
84 WHERE zrb.tax = 'LOCALIZATION' -- 'Localization' , Harshita for Bug 4907217
85 AND zrb.tax_regime_code = cp_tax_regime_code
86 AND zrb.tax_status_code = 'STANDARD'
87 AND zrb.active_flag = 'Y'
88 AND trunc(sysdate) between trunc(zrb.effective_from) and trunc(nvl(zrb.effective_to, sysdate)) ;
89
90 /*------------------------------------------------------------------------------------
91 | Above code is Added by Ramananda for bug#4468353 due to ebtax uptake by AR, end |
92 -------------------------------------------------------------------------------------*/
93
94 procedure update_excise_invoice_no(
95 retcode OUT NOCOPY varchar2,
96 errbuf OUT NOCOPY varchar2,
97 p_org_id number, /* Bug 5096787. Added by Lakshmi Gopalsami Added following two parameters. */
98 p_start_date VARCHAR2, /* modified by aiyer for the bug 5369250 */
99 p_end_date VARCHAR2 DEFAULT NULL,
100 p_customer_trx_id number
101 );
102
103 procedure validate_invoice( p_customer_trx_id IN RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID%TYPE ,
104 p_trx_number IN RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE , /* should not be used in a where clause as this is not unique */
105 p_error_flag OUT NOCOPY VARCHAR2 ,
106 p_error_message OUT NOCOPY VARCHAR2
107 );
108
109
110 --==========================================================================
111 -- PROCEDURE NAME:
112 -- update_reference Public
113 --
114 -- DESCRIPTION:
115 -- This procedure is written that update the reference field in AR
116 -- transaction workbench when the AR invoice has been created manually
117 --
118 -- ER NAME/BUG#
119 -- VAT/Excise Number shown in AR transaction workbench
120 -- Bug 9303168
121 --
122 -- PARAMETERS:
123 -- In: pn_customer_trx_id Indicates the customer trx id
124 --
125 -- DESIGN REFERENCES:
126 -- TD named "VAT Invoice Number on AR Invoice Technical Design.doc" has been
127 -- referenced in the section 6.1
128 --
129 -- CALL FROM
130 -- The concurrent program "India - Excise/VAT Number in Transactions Workbench"
131 --
132 -- CHANGE HISTORY:
133 -- 25-Jan-2010 BO Li Created by Bo Li
134
135 --==========================================================================
136 PROCEDURE update_reference
137 ( retcode OUT NOCOPY VARCHAR2
138 , errbuf OUT NOCOPY VARCHAR2
139 , pn_customer_trx_id NUMBER);
140 END jai_ar_trx_pkg;
[Home] [Help]