[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 2006/09/15 17:37:18 rjreddy 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 -----------------------------------------------------------------------------------------------------------*/
18 procedure insert_row
19 ( x_record jg_zz_vat_alloc_rules%rowtype
20 , x_allocation_rule_id out nocopy jg_zz_vat_alloc_rules.allocation_rule_id%type
21 , x_row_id out nocopy rowid
22 )
23 is
24
25 cursor c_gen_alloc_rule_id
26 is
27 select jg_zz_vat_alloc_rules_s.nextval
28 from dual;
29
30 begin
31
32 if x_record.allocation_rule_id is null then
33 /* Generate a new allocation_rule_id if not provied in procedure arguments */
34 open c_gen_alloc_rule_id;
35 fetch c_gen_alloc_rule_id into x_allocation_rule_id;
36 close c_gen_alloc_rule_id;
37 else
38 /* Use the allocation_rule_id given in the procedure arguments */
39 x_allocation_rule_id := x_record.allocation_rule_id;
40 end if;
41
42 insert into jg_zz_vat_alloc_rules
43 ( allocation_rule_id
44 , vat_reporting_entity_id
45 , source
46 , financial_document_type
47 , vat_transaction_type
48 , tax_id
49 , tax_code
50 , tax_status
51 , tax_jurisdiction_code
52 , tax_rate_code
53 , tax_rate_id
54 , tax_box_recoverable
55 , tax_box_non_recoverable
56 , taxable_box_non_recoverable
57 , total_box
58 , effective_from_date
59 , effective_to_date
60 , period_type
61 , created_by
62 , creation_date
63 , last_updated_by
64 , last_update_date
65 , last_update_login
66 , tax_rec_sign_flag
67 , tax_non_rec_sign_flag
68 , taxable_non_rec_sign_flag
69
70
71
72 )
73 values ( x_allocation_rule_id
74 , x_record.vat_reporting_entity_id
75 , x_record.source
76 , x_record.financial_document_type
77 , x_record.vat_transaction_type
78 , x_record.tax_id
79 , x_record.tax_code
80 , x_record.tax_status
81 , x_record.tax_jurisdiction_code
82 , x_record.tax_rate_code
83 , x_record.tax_rate_id
84 , x_record.tax_box_recoverable
85 , x_record.tax_box_non_recoverable
86 , x_record.taxable_box_non_recoverable
87 , x_record.total_box
88 , x_record.effective_from_date
89 , x_record.effective_to_date
90 , x_record.period_type
91 , x_record.created_by
92 , x_record.creation_date
93 , x_record.last_updated_by
94 , x_record.last_update_date
95 , x_record.last_update_login
96 , x_record.tax_rec_sign_flag
97 , x_record.tax_non_rec_sign_flag
98 , x_record.taxable_non_rec_sign_flag
99
100 )returning rowid into x_row_id;
101 exception
102 when others then
103 x_allocation_rule_id := null;
104 x_row_id := null;
105 raise;
106 end insert_row;
107
108 /*------------------------------------------------------------------------------------------------------------*/
109
110 procedure lock_row
111 ( x_row_id rowid
112 , x_record jg_zz_vat_alloc_rules%rowtype
113 )
114 is
115
116 cursor c_locked_row is
117 select jzvar.*
118 from jg_zz_vat_alloc_rules jzvar
119 where rowid = x_row_id
120 for update nowait;
121
122 lr_locked_row jg_zz_vat_alloc_rules%rowtype;
123
124 begin
125
126 open c_locked_row;
127 fetch c_locked_row into lr_locked_row;
128
129 if (c_locked_row%notfound) then
130 close c_locked_row;
131 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
132 app_exception.raise_exception;
133 end if;
134
135 close c_locked_row;
136
137 if ( lr_locked_row.allocation_rule_id = x_record.allocation_rule_id
138 and lr_locked_row.vat_reporting_entity_id = x_record.vat_reporting_entity_id
139 and lr_locked_row.source = x_record.source
140 and lr_locked_row.financial_document_type = x_record.financial_document_type
141 and nvl(lr_locked_row.vat_transaction_type,'X$') = nvl(x_record.vat_transaction_type, 'X$')
142 and nvl(lr_locked_row.tax_id,-9999) = nvl(x_record.tax_id,-99999)
143 and nvl(lr_locked_row.tax_status,'X$') = nvl(x_record.tax_status,'X$')
144 and nvl(lr_locked_row.tax_jurisdiction_code,'X$') = nvl(x_record.tax_jurisdiction_code,'X$')
145 and nvl(lr_locked_row.tax_rate_code, 'X$') = nvl(x_record.tax_rate_code, 'X$')
146 and nvl(lr_locked_row.tax_rate_id,'-9999') = nvl(x_record.tax_rate_id,'-9999')
147 and nvl(lr_locked_row.tax_box_recoverable,'X$') = nvl(x_record.tax_box_recoverable, 'X$')
148 and nvl(lr_locked_row.tax_box_non_recoverable,'X$') = nvl(x_record.tax_box_non_recoverable, 'X$')
149 and nvl(lr_locked_row.taxable_box_non_recoverable,'X$') = nvl(x_record.taxable_box_non_recoverable, 'X$')
150 and nvl(lr_locked_row.total_box,'X$') = nvl(x_record.total_box,'X$')
151 and lr_locked_row.effective_from_date = x_record.effective_from_date
152 and nvl(lr_locked_row.effective_to_date,sysdate) = nvl(x_record.effective_to_date, sysdate)
153 and nvl(lr_locked_row.period_type,'X$') = nvl(x_record.period_type,'X$')
154 and nvl(lr_locked_row.tax_rec_sign_flag,'+') = nvl(x_record.tax_rec_sign_flag,'+')
155 and nvl(lr_locked_row.tax_non_rec_sign_flag,'+') = nvl(x_record.tax_non_rec_sign_flag,'+')
156 and nvl(lr_locked_row.taxable_non_rec_sign_flag,'+') = nvl(x_record.taxable_non_rec_sign_flag,'+')
157 )
158 then
159 return;
160 else
161 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
162 app_exception.raise_exception;
163 end if;
164 end lock_row;
165
166 /*------------------------------------------------------------------------------------------------------------*/
167
168 procedure update_row( x_record jg_zz_vat_alloc_rules%rowtype
169 )
170 is
171 le_no_rows_updated exception;
172 lx_row_id rowid;
173
174 begin
175
176 update jg_zz_vat_alloc_rules
177 set vat_reporting_entity_id = x_record.vat_reporting_entity_id
178 , source = x_record.source
179 , financial_document_type = x_record.financial_document_type
180 , vat_transaction_type = x_record.vat_transaction_type
181 , tax_id = x_record.tax_id
182 , tax_code = x_record.tax_code
183 , tax_status = x_record.tax_status
184 , tax_jurisdiction_code = x_record.tax_jurisdiction_code
185 , tax_rate_code = x_record.tax_rate_code
186 , tax_rate_id = x_record.tax_rate_id
187 , tax_box_recoverable = x_record.tax_box_recoverable
188 , tax_box_non_recoverable = x_record.tax_box_non_recoverable
189 , taxable_box_non_recoverable = x_record.taxable_box_non_recoverable
190 , total_box = x_record.total_box
191 , effective_from_date = x_record.effective_from_date
192 , effective_to_date = x_record.effective_to_date
193 , period_type = x_record.period_type
194 , created_by = x_record.created_by
195 , creation_date = x_record.creation_date
196 , last_updated_by = x_record.last_updated_by
197 , last_update_date = x_record.last_update_date
198 , last_update_login = x_record.last_update_login
199 , tax_rec_sign_flag = x_record.tax_rec_sign_flag
200 , tax_non_rec_sign_flag = x_record.tax_non_rec_sign_flag
201 , taxable_non_rec_sign_flag = x_record.taxable_non_rec_sign_flag
202 where allocation_rule_id = x_record.allocation_rule_id;
203
204 end update_row;
205
206 /*------------------------------------------------------------------------------------------------------------*/
207
208 procedure delete_row( x_allocation_rule_id jg_zz_vat_alloc_rules.allocation_rule_id%type)
209 is
210 le_no_rows_deleted exception;
211 begin
212
213 delete from jg_zz_vat_alloc_rules
214 where allocation_rule_id = x_allocation_rule_id ;
215
216 end delete_row;
217
218 end jg_zz_vat_alloc_rules_pkg;