1 PACKAGE jai_rcv_excise_processing_pkg AS
2 /* $Header: jai_rcv_exc_prc.pls 120.4.12010000.2 2008/11/18 06:55:07 vumaasha ship $ */
3
4 /*
5 OPEN ISSUES:
6 1) Partial Delivery of CGIN items to NonBonded Delivery then what should we do the second claim of RECEIVE
7 */
8
9 /* --------------------------------------------------------------------------------------
10 Filename:
11
12 Change History:
13
14 Date Bug Remarks
15 --------- ---------- -------------------------------------------------------------
16 08-Jun-2005 Version 116.1 jai_rcv_exc_prc -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
17 as required for CASE COMPLAINCE.
18
19 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.2
20 GL Sources and GL Categories got changed. Refer bug for the details
21
22
23 30/10/2006 SACSETHI for bug 5228046, File version 120.2
24 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
25 This bug has datamodel and spec changes.
26
27 16/04/2007 Vkarnaam for bug #5989740 File version 120.3
28 Forward Porting the changes in 115 bug 5907436(Enh:handling Secondary and Higher Education Cess).
29
30 18/11/2008 vumaasha
31 Forward porting the changes in the 115bug 4545776
32 Changed the cursor c_source_orgn_loc to fetch shipment_num (
33 rcv_shipment_headers )
34 or delivery_id ( wsh_new_deliveries ) based on whether delivery_id is
35 number or character value respectively.
36
37 --------------------------------------------------------------------------------------
38 */
39
40 lb_rg_debug CONSTANT BOOLEAN := true;
41 gn_cenvat_rnd CONSTANT NUMBER := 0;
42
43 gv_source_name CONSTANT VARCHAR2(25) := 'Purchasing India';
44 gv_category_name CONSTANT VARCHAR2(25) := 'Receiving India';
45
46
47 CENVAT_CREDIT CONSTANT VARCHAR2(2) := 'Cr';
48 CENVAT_DEBIT CONSTANT VARCHAR2(2) := 'Dr';
49 SECOND_50PTG_CLAIM CONSTANT VARCHAR2(15) := '2nd 50% Claim';
50
51 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
52 CGIN_FIRST_CLAIM CONSTANT VARCHAR2(10) := '1st Claim';
53 CGIN_SECOND_CLAIM CONSTANT VARCHAR2(10) := '2nd Claim';
54
55 TYPE tax_breakup IS RECORD(
56 basic_excise NUMBER := 0,
57 addl_excise NUMBER := 0,
58 other_excise NUMBER := 0,
59 cvd NUMBER := 0,
60 non_cenvat NUMBER := 0,
61 excise_edu_cess NUMBER := 0,
62 cvd_edu_cess NUMBER := 0,
63 addl_cvd NUMBER := 0, -- Date 30/10/2006 Bug 5228046 added by SACSETHI
64 /*added the following by vkaranam for budget 07 impact - bug#5907436*/
65 sh_exc_edu_cess NUMBER := 0,
66 sh_cvd_edu_cess NUMBER := 0
67
68 );
69
70 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
71 SELECT *
72 FROM JAI_RCV_TRANSACTIONS
73 WHERE transaction_id = cp_transaction_id;
74
75 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
76 SELECT shipment_header_id, requisition_line_id, primary_quantity, uom_code, unit_of_measure,
77 po_header_id, po_line_id, po_line_location_id, transaction_id, subinventory,
78 vendor_id, vendor_site_id, customer_id, customer_site_id, oe_order_line_id,
79 transaction_type, destination_type_code, source_document_code, quantity
80 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ,attribute_category attr_cat, attribute1, attribute2, attribute3, attribute4, attribute5 rma_type
81 FROM rcv_transactions
82 WHERE transaction_id = cp_transaction_id;
83
84 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
85 CURSOR c_jai_receipt_line(cp_shipment_line_id IN NUMBER) IS
86 select rma_type
87 from JAI_RCV_LINES
88 where shipment_line_id = cp_shipment_line_id;
89
90 CURSOR c_orgn_info(cp_organization_id IN NUMBER, cp_location_id IN NUMBER) IS
91 SELECT modvat_rm_account_id, modvat_cg_account_id, modvat_pla_account_id, cenvat_rcvble_account,
92 excise_in_rg23d, excise_23d_account, excise_rcvble_account,
93 ssi_unit_flag, pref_rg23a, pref_rg23c, pref_pla, nvl(ssi_unit_flag, 'N') allow_negative_pla,
94 excise_edu_cess_rm_account, excise_edu_cess_cg_account, excise_edu_cess_rcvble_accnt -- Vijay Shankar for Bug#3940588
95 , cess_paid_payable_account_id -- Vijay Shankar for Bug#4211045
96 , rtv_account_flag, rtv_expense_account_id ,-- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.. SSI Func.
97 /*added the following columns by vkaranam for budget 07 impact - bug#5989740*/
98 sh_cess_cg_account_id,sh_cess_paid_payable_acct_id,sh_cess_rcvble_acct_id,sh_cess_rm_account,sh_cess_rnd_account_id
99
100 FROM JAI_CMN_INVENTORY_ORGS
101 WHERE organization_id = cp_organization_id
102 AND location_id = cp_location_id;
103
104 CURSOR c_rcv_params(cp_organization_id IN NUMBER) IS
105 SELECT receiving_account_id
106 FROM rcv_parameters
107 WHERE organization_id = cp_organization_id;
108
109
110 /*
111 || vumaasha bug#5749963. Added decode statement for shipment_num.
112 */
113
114
115 CURSOR c_source_orgn_loc(cp_shipment_hdr_id IN NUMBER, cp_req_line_id IN NUMBER) IS
116 SELECT organization_id, location_id
117 FROM JAI_OM_WSH_LINES_ALL
118 WHERE delivery_id = ( SELECT
119 decode(ltrim(translate(shipment_num,'0123456789','~'),'~'),NULL,rsh.shipment_num,
120 (select delivery_id
121 from wsh_new_deliveries
122 where name=rsh.shipment_num
123 )
124
125 )
126 from rcv_shipment_headers rsh
127 where shipment_header_id = cp_shipment_hdr_id
128 )
129 AND order_line_id IN ( select line_id
130 from oe_order_lines_all
131 where source_document_type_id = 10 -- 10 corresponds to 'Internal' document type
132 and source_document_line_id = cp_req_line_id
133 );
134
135 PROCEDURE do_cenvat_rounding(
136 p_transaction_id IN NUMBER,
137 pr_tax IN OUT NOCOPY TAX_BREAKUP,
138 p_codepath IN OUT NOCOPY VARCHAR2
139 );
140
141 PROCEDURE process_transaction(
142 p_transaction_id IN NUMBER,
143 p_cenvat_claimed_ptg IN OUT NOCOPY VARCHAR2,
144 p_process_status OUT NOCOPY VARCHAR2,
145 p_process_message OUT NOCOPY VARCHAR2,
146 p_simulate_flag IN VARCHAR2,
147 p_codepath IN OUT NOCOPY VARCHAR2,
148 -- following parameters introduced for second claim of receive transaction
149 p_process_special_reason IN VARCHAR2 DEFAULT NULL,
150 p_process_special_qty IN NUMBER DEFAULT NULL
151 );
152
153 PROCEDURE rg_i_entry(
154 p_transaction_id IN NUMBER,
155 pr_tax IN TAX_BREAKUP,
156 p_register_entry_type IN VARCHAR2,
157 p_register_id OUT NOCOPY NUMBER,
158 p_process_status OUT NOCOPY VARCHAR2,
159 p_process_message OUT NOCOPY VARCHAR2,
160 p_simulate_flag IN VARCHAR2,
161 p_codepath IN OUT NOCOPY VARCHAR2
162 );
163
164 PROCEDURE rg23_part_i_entry(
165 p_transaction_id IN NUMBER,
166 pr_tax IN TAX_BREAKUP,
167 p_register_entry_type IN VARCHAR2,
168 p_register_id OUT NOCOPY NUMBER,
169 p_process_status OUT NOCOPY VARCHAR2,
170 p_process_message OUT NOCOPY VARCHAR2,
171 p_simulate_flag IN VARCHAR2,
172 p_codepath IN OUT NOCOPY VARCHAR2
173 );
174
175 PROCEDURE rg23_d_entry(
176 p_transaction_id IN NUMBER,
177 pr_tax IN TAX_BREAKUP,
178 p_register_entry_type IN VARCHAR2,
179 p_register_id OUT NOCOPY NUMBER,
180 p_process_status OUT NOCOPY VARCHAR2,
181 p_process_message OUT NOCOPY VARCHAR2,
182 p_simulate_flag IN VARCHAR2,
183 p_codepath IN OUT NOCOPY VARCHAR2
184 );
185
186 PROCEDURE rg23_part_ii_entry(
187 p_transaction_id IN NUMBER,
188 pr_tax IN TAX_BREAKUP,
189 p_part_i_register_id IN NUMBER,
190 p_register_entry_type IN VARCHAR2,
191 p_reference_num IN VARCHAR2,
192 p_register_id OUT NOCOPY NUMBER,
193 p_process_status OUT NOCOPY VARCHAR2,
194 p_process_message OUT NOCOPY VARCHAR2,
195 p_simulate_flag IN VARCHAR2,
196 p_codepath IN OUT NOCOPY VARCHAR2
197 );
198
199 PROCEDURE pla_entry(
200 p_transaction_id IN NUMBER,
201 pr_tax IN TAX_BREAKUP,
202 p_register_entry_type IN VARCHAR2,
203 p_register_id OUT NOCOPY NUMBER,
204 p_process_status OUT NOCOPY VARCHAR2,
205 p_process_message OUT NOCOPY VARCHAR2,
206 p_simulate_flag IN VARCHAR2,
207 p_codepath IN OUT NOCOPY VARCHAR2
208 );
209
210 PROCEDURE accounting_entries(
211 p_transaction_id IN NUMBER,
212 pr_tax IN TAX_BREAKUP,
213 p_cgin_code IN VARCHAR2,
214 p_cenvat_accounting_type IN VARCHAR2,
215 p_amount_register IN VARCHAR2,
216 p_cenvat_account_id OUT NOCOPY NUMBER,
217 p_process_status OUT NOCOPY VARCHAR2,
218 p_process_message OUT NOCOPY VARCHAR2,
219 p_simulate_flag IN VARCHAR2,
220 p_codepath IN OUT NOCOPY VARCHAR2
221 , pv_retro_reference IN VARCHAR2 DEFAULT NULL --Added by Eric on Jan 18,2008 for retro
222 );
223
224 PROCEDURE derive_cgin_scenario(
225 p_transaction_id IN NUMBER,
226 p_cgin_code OUT NOCOPY VARCHAR2,
227 p_process_status OUT NOCOPY VARCHAR2,
228 p_process_message OUT NOCOPY VARCHAR2,
229 p_codepath IN OUT NOCOPY VARCHAR2
230 );
231
232 PROCEDURE update_registers(
233 p_quantity_register_id IN NUMBER,
234 p_quantity_register IN VARCHAR2,
235 p_payment_register_id IN NUMBER,
236 p_payment_register IN VARCHAR2,
237 p_charge_account_id IN NUMBER,
238 p_process_status OUT NOCOPY VARCHAR2,
239 p_process_message OUT NOCOPY VARCHAR2,
240 p_simulate_flag IN VARCHAR2,
241 p_codepath IN OUT NOCOPY VARCHAR2
242 );
243
244 PROCEDURE validate_transaction(
245 p_transaction_id IN NUMBER,
246 p_validation_type IN VARCHAR2,
247 p_process_status OUT NOCOPY VARCHAR2,
248 p_process_message OUT NOCOPY VARCHAR2,
249 p_simulate_flag IN VARCHAR2,
250 p_codepath IN OUT NOCOPY VARCHAR2
251 );
252
253 PROCEDURE generate_excise_invoice(
254 p_transaction_id IN NUMBER,
255 p_organization_id IN NUMBER,
256 p_location_id IN NUMBER,
257 p_excise_invoice_no OUT NOCOPY VARCHAR2,
258 p_excise_invoice_date OUT NOCOPY DATE,
259 p_simulate_flag IN VARCHAR2,
260 p_errbuf OUT NOCOPY VARCHAR2,
261 p_codepath IN OUT NOCOPY VARCHAR2
262 );
263
264 FUNCTION get_receive_claimed_ptg(
265 p_transaction_id IN NUMBER,
266 p_shipment_line_id IN NUMBER,
267 p_codepath IN OUT NOCOPY VARCHAR2
268 ) RETURN NUMBER;
269
270 PROCEDURE get_tax_amount_breakup(
271 p_shipment_line_id IN NUMBER,
272 p_transaction_id IN NUMBER,
273 p_curr_conv_rate IN NUMBER,
274 pr_tax OUT NOCOPY TAX_BREAKUP,
275 p_breakup_type IN VARCHAR2,
276 p_codepath IN OUT NOCOPY VARCHAR2
277 );
278
279 PROCEDURE other_cenvat_rg_recording(
280 p_source_register IN VARCHAR2,
281 p_source_register_id IN NUMBER,
282 p_tax_type IN VARCHAR2,
283 p_credit IN NUMBER,
284 p_debit IN NUMBER,
285 p_process_status OUT NOCOPY VARCHAR2,
286 p_process_message OUT NOCOPY VARCHAR2
287 );
288
289 PROCEDURE check_cenvat_balances(
290 p_organization_id IN NUMBER,
291 p_location_id IN NUMBER,
292 p_transaction_amount IN NUMBER,
293 p_register_type IN VARCHAR2,
294 p_process_flag OUT NOCOPY VARCHAR2,
295 p_process_message OUT NOCOPY VARCHAR2
296 );
297
298 PROCEDURE derive_duty_registers(
299 p_organization_id IN NUMBER,
300 p_location_id IN NUMBER,
301 p_item_class IN VARCHAR2,
302 pr_tax IN TAX_BREAKUP,
303 p_cenvat_register_type OUT NOCOPY VARCHAR2,
304 -- p_edu_cess_register_type OUT VARCHAR2,
305 p_process_flag OUT NOCOPY VARCHAR2,
306 p_process_message OUT NOCOPY VARCHAR2,
307 p_codepath IN OUT NOCOPY VARCHAR2
308 );
309
310 procedure rtv_processing_for_ssi(
311 pn_transaction_id NUMBER,
312 pv_codepath in out nocopy varchar2,
313 pv_process_status out nocopy varchar2,
314 pv_process_message out nocopy varchar2
315 );
316
317 END jai_rcv_excise_processing_pkg;
[Home] [Help]