[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_ALLOC_RULES_PKG
Source
1 package body jg_zz_vat_alloc_rules_pkg as
2 /* $Header: jgzzvarb.pls 120.4.12010000.2 2009/06/17 05:14:14 vkejriwa ship $*/
3 /* CHANGE HISTORY ------------------------------------------------------------------------------------------
4 DATE AUTHOR VERSION BUG NO. DESCRIPTION
5 (DD/MM/YYYY) (UID)
6 ------------------------------------------------------------------------------------------------------------
7
8 23/6/2006 BRATHOD 120.2 5166688 Modified the signature of INSERT_ROW procedure in
9 to return rowid to caller of API by adding out
10 parameter in the call. Refer bug# 5166688 for details
11 24/07/2006 BRATHOD 120.3 Bug:5408280
12 Modified the package to upateke the impact due to
13 addition of new sign flag columns and removal of
14 SIGN_FLAG column
15 15/09/2006 RJREDDY 120.4 5532038 Removed the taxable_box_recoverable and taxable_rec_sign_flag
16 field from all the procedures
17 17/06/2009 VKEJRIWA 120.4.12000000.2 8587516 Added the taxable_box_recoverable and taxable_rec_sign_flag
18 field to all the procedures
19 -----------------------------------------------------------------------------------------------------------*/
20 procedure insert_row
21 ( x_record jg_zz_vat_alloc_rules%rowtype
22 , x_allocation_rule_id out nocopy jg_zz_vat_alloc_rules.allocation_rule_id%type
23 , x_row_id out nocopy rowid
24 )
25 is
26
27 cursor c_gen_alloc_rule_id
28 is
29 select jg_zz_vat_alloc_rules_s.nextval
30 from dual;
31
32 begin
33
34 if x_record.allocation_rule_id is null then
35 /* Generate a new allocation_rule_id if not provied in procedure arguments */
36 open c_gen_alloc_rule_id;
37 fetch c_gen_alloc_rule_id into x_allocation_rule_id;
38 close c_gen_alloc_rule_id;
39 else
40 /* Use the allocation_rule_id given in the procedure arguments */
41 x_allocation_rule_id := x_record.allocation_rule_id;
42 end if;
43
44 insert into jg_zz_vat_alloc_rules
45 ( allocation_rule_id
46 , vat_reporting_entity_id
47 , source
48 , financial_document_type
49 , vat_transaction_type
50 , tax_id
51 , tax_code
52 , tax_status
53 , tax_jurisdiction_code
54 , tax_rate_code
55 , tax_rate_id
56 , tax_box_recoverable
57 , tax_box_non_recoverable
58 , taxable_box_recoverable
59 , taxable_box_non_recoverable
60 , total_box
61 , effective_from_date
62 , effective_to_date
63 , period_type
64 , created_by
65 , creation_date
66 , last_updated_by
67 , last_update_date
68 , last_update_login
69 , tax_rec_sign_flag
70 , tax_non_rec_sign_flag
71 , taxable_rec_sign_flag
72 , taxable_non_rec_sign_flag
73
74
75
76 )
77 values ( x_allocation_rule_id
78 , x_record.vat_reporting_entity_id
79 , x_record.source
80 , x_record.financial_document_type
81 , x_record.vat_transaction_type
82 , x_record.tax_id
83 , x_record.tax_code
84 , x_record.tax_status
85 , x_record.tax_jurisdiction_code
86 , x_record.tax_rate_code
87 , x_record.tax_rate_id
88 , x_record.tax_box_recoverable
89 , x_record.tax_box_non_recoverable
90 , x_record.taxable_box_recoverable
91 , x_record.taxable_box_non_recoverable
92 , x_record.total_box
93 , x_record.effective_from_date
94 , x_record.effective_to_date
95 , x_record.period_type
96 , x_record.created_by
97 , x_record.creation_date
98 , x_record.last_updated_by
99 , x_record.last_update_date
100 , x_record.last_update_login
101 , x_record.tax_rec_sign_flag
102 , x_record.tax_non_rec_sign_flag
103 , x_record.taxable_rec_sign_flag
104 , x_record.taxable_non_rec_sign_flag
105
106 )returning rowid into x_row_id;
107 exception
108 when others then
109 x_allocation_rule_id := null;
110 x_row_id := null;
111 raise;
112 end insert_row;
113
114 /*------------------------------------------------------------------------------------------------------------*/
115
116 procedure lock_row
117 ( x_row_id rowid
118 , x_record jg_zz_vat_alloc_rules%rowtype
119 )
120 is
121
122 cursor c_locked_row is
123 select jzvar.*
124 from jg_zz_vat_alloc_rules jzvar
125 where rowid = x_row_id
126 for update nowait;
127
128 lr_locked_row jg_zz_vat_alloc_rules%rowtype;
129
130 begin
131
132 open c_locked_row;
133 fetch c_locked_row into lr_locked_row;
134
135 if (c_locked_row%notfound) then
136 close c_locked_row;
137 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
138 app_exception.raise_exception;
139 end if;
140
141 close c_locked_row;
142
143 if ( lr_locked_row.allocation_rule_id = x_record.allocation_rule_id
144 and lr_locked_row.vat_reporting_entity_id = x_record.vat_reporting_entity_id
145 and lr_locked_row.source = x_record.source
146 and lr_locked_row.financial_document_type = x_record.financial_document_type
147 and nvl(lr_locked_row.vat_transaction_type,'X$') = nvl(x_record.vat_transaction_type, 'X$')
148 and nvl(lr_locked_row.tax_id,-9999) = nvl(x_record.tax_id,-99999)
149 and nvl(lr_locked_row.tax_status,'X$') = nvl(x_record.tax_status,'X$')
150 and nvl(lr_locked_row.tax_jurisdiction_code,'X$') = nvl(x_record.tax_jurisdiction_code,'X$')
151 and nvl(lr_locked_row.tax_rate_code, 'X$') = nvl(x_record.tax_rate_code, 'X$')
152 and nvl(lr_locked_row.tax_rate_id,'-9999') = nvl(x_record.tax_rate_id,'-9999')
153 and nvl(lr_locked_row.tax_box_recoverable,'X$') = nvl(x_record.tax_box_recoverable, 'X$')
154 and nvl(lr_locked_row.tax_box_non_recoverable,'X$') = nvl(x_record.tax_box_non_recoverable, 'X$')
155 and nvl(lr_locked_row.taxable_box_recoverable,'X$') = nvl(x_record.taxable_box_recoverable, 'X$')
156 and nvl(lr_locked_row.taxable_box_non_recoverable,'X$') = nvl(x_record.taxable_box_non_recoverable, 'X$')
157 and nvl(lr_locked_row.total_box,'X$') = nvl(x_record.total_box,'X$')
158 and lr_locked_row.effective_from_date = x_record.effective_from_date
159 and nvl(lr_locked_row.effective_to_date,sysdate) = nvl(x_record.effective_to_date, sysdate)
160 and nvl(lr_locked_row.period_type,'X$') = nvl(x_record.period_type,'X$')
161 and nvl(lr_locked_row.tax_rec_sign_flag,'+') = nvl(x_record.tax_rec_sign_flag,'+')
162 and nvl(lr_locked_row.tax_non_rec_sign_flag,'+') = nvl(x_record.tax_non_rec_sign_flag,'+')
163 and nvl(lr_locked_row.taxable_rec_sign_flag,'+') = nvl(x_record.taxable_rec_sign_flag,'+')
164 and nvl(lr_locked_row.taxable_non_rec_sign_flag,'+') = nvl(x_record.taxable_non_rec_sign_flag,'+')
165 )
166 then
167 return;
168 else
169 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
170 app_exception.raise_exception;
171 end if;
172 end lock_row;
173
174 /*------------------------------------------------------------------------------------------------------------*/
175
176 procedure update_row( x_record jg_zz_vat_alloc_rules%rowtype
177 )
178 is
179 le_no_rows_updated exception;
180 lx_row_id rowid;
181
182 begin
183
184 update jg_zz_vat_alloc_rules
185 set vat_reporting_entity_id = x_record.vat_reporting_entity_id
186 , source = x_record.source
187 , financial_document_type = x_record.financial_document_type
188 , vat_transaction_type = x_record.vat_transaction_type
189 , tax_id = x_record.tax_id
190 , tax_code = x_record.tax_code
191 , tax_status = x_record.tax_status
192 , tax_jurisdiction_code = x_record.tax_jurisdiction_code
193 , tax_rate_code = x_record.tax_rate_code
194 , tax_rate_id = x_record.tax_rate_id
195 , tax_box_recoverable = x_record.tax_box_recoverable
196 , tax_box_non_recoverable = x_record.tax_box_non_recoverable
197 , taxable_box_recoverable = x_record.taxable_box_recoverable
198 , taxable_box_non_recoverable = x_record.taxable_box_non_recoverable
199 , total_box = x_record.total_box
200 , effective_from_date = x_record.effective_from_date
201 , effective_to_date = x_record.effective_to_date
202 , period_type = x_record.period_type
203 , created_by = x_record.created_by
204 , creation_date = x_record.creation_date
205 , last_updated_by = x_record.last_updated_by
206 , last_update_date = x_record.last_update_date
207 , last_update_login = x_record.last_update_login
208 , tax_rec_sign_flag = x_record.tax_rec_sign_flag
209 , tax_non_rec_sign_flag = x_record.tax_non_rec_sign_flag
210 , taxable_rec_sign_flag = x_record.taxable_rec_sign_flag
211 , taxable_non_rec_sign_flag = x_record.taxable_non_rec_sign_flag
212 where allocation_rule_id = x_record.allocation_rule_id;
213
214 end update_row;
215
216 /*------------------------------------------------------------------------------------------------------------*/
217
218 procedure delete_row( x_allocation_rule_id jg_zz_vat_alloc_rules.allocation_rule_id%type)
219 is
220 le_no_rows_deleted exception;
221 begin
222
223 delete from jg_zz_vat_alloc_rules
224 where allocation_rule_id = x_allocation_rule_id ;
225
226 end delete_row;
227
228 end jg_zz_vat_alloc_rules_pkg;