[Home] [Help]
PACKAGE: APPS.JAI_RCV_TRX_PROCESSING_PKG
Source
1 PACKAGE jai_rcv_trx_processing_pkg AS
2 /* $Header: jai_rcv_trx_prc.pls 120.7 2007/08/29 09:34:06 pramasub ship $ */
3
4 gv_func_curr CONSTANT VARCHAR2(3) := 'INR';
5 lb_debug CONSTANT BOOLEAN := TRUE;
6 lv_debug CONSTANT VARCHAR2(1) := 'Y';
7
8 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
9 INDIA_RMA_RECEIPT CONSTANT VARCHAR2(30) := 'India RMA Receipt';
10 INDIA_RECEIPT CONSTANT VARCHAR2(30) := 'India Receipt';
11 SOURCE_RMA CONSTANT VARCHAR2(30) := 'RMA';
12
13 /* Excise/VAT Invoice generation Status for RTV */
14 INV_GEN_STATUS_PENDING CONSTANT VARCHAR2(30) := 'PENDING';
15 INV_GEN_STATUS_NA CONSTANT VARCHAR2(30) := 'NOT_APPLICABLE';
16 INV_GEN_STATUS_GENERATE CONSTANT VARCHAR2(30) := 'GENERATE';
17 INV_GEN_STATUS_INV_NA CONSTANT VARCHAR2(30) := 'INVOICE_NOT_APPLICABLE';
18 INV_GEN_STATUS_INV_GENERATED CONSTANT VARCHAR2(30) := 'INVOICE_GENERATED';
19
20 CALLED_FROM_RCV_TRIGGER CONSTANT VARCHAR2(30) := 'RECEIPT_TAX_INSERT_TRG';
21 CALLED_FROM_JAINPORE CONSTANT VARCHAR2(30) := 'JAINPORE';
22 CALLED_FROM_JAINMVAT CONSTANT VARCHAR2(30) := 'JAINMVAT';
23 CALLED_FROM_JAINRTVN CONSTANT VARCHAR2(30) := 'JAINRTVN';
24 CALLED_FROM_JAITIGRTV CONSTANT VARCHAR2(30) := 'JAITIGRTV';
25 CALLED_FROM_FND_REQUEST CONSTANT VARCHAR2(30) := 'Batch';
26
27 /* MAPPING for JAI_RCV_TRANSACTIONS Attributes incase of transaction_type = 'RETURN TO VENDOR'
28 attribute1 => EXCISE_INVOICE_GENERATION_ACTION
29 attribute2 => VAT_INVOICE_GENERATION_ACTION
30 attribute3 => EXCISE_INVOICE_GENERATION_BATCH_NO
31 attribute4 => VAT_INVOICE_GENERATION_BATCH_NO
32 */
33
34 /* Vijay Shankar for Bug#4250171 */
35 CALLED_FROM_OPM CONSTANT VARCHAR2(15) := 'OPM';
36 OPM_RECEIPT CONSTANT VARCHAR2(15) := 'OPM RECEIPT';
37 OPM_RETURNS CONSTANT VARCHAR2(30) := 'OPM Receipt Correction';
38
39 NO_ITEM_CLASS CONSTANT VARCHAR2(4) := 'OTIN'; -- Vijay Shankar for Bug#4070938
40 NO_SETUP CONSTANT VARCHAR(1) := 'X';
41
42 gv_shipment_header_id NUMBER ; -- added, CSahoo for Bug 5344225
43 gv_group_id NUMBER ; -- added, CSahoo for Bug 5344225
44
45
46
47 /*bgowrava for forward porting Bug#5756676..start*/
48 lv_online_qty_flag VARCHAR2(1);
49 lv_qty_upd_event VARCHAR2(30);
50 lv_excise_flag VARCHAR2(1);
51 ln_part_i_register_id NUMBER;
52 lv_cgin_code VARCHAR2(100);
53 lv_register_type VARCHAR2(1);
54 lv_process_status VARCHAR2(15);
55 lv_process_message VARCHAR2(4000);
56 CURSOR cur_qty_setup( cp_organization_id NUMBER,
57 cp_location_id NUMBER
58 )
59 IS
60 SELECT quantity_register_update_event
61 FROM JAI_CMN_INVENTORY_ORGS
62 WHERE organization_id = cp_organization_id
63 AND location_id = cp_location_id ;
64
65 CURSOR cur_item_excise_flag( cp_organization_id NUMBER,
66 cp_inventory_item_id NUMBER
67 )
68 IS
69 SELECT excise_flag
70 FROM JAI_INV_ITM_SETUPS
71 WHERE organization_id = cp_organization_id
72 AND inventory_item_id = cp_inventory_item_id;
73
74 /*bgowrava for forward porting Bug#5756676..end*/
75
76 CURSOR c_trx(cp_transaction_id IN NUMBER) IS
77 SELECT *
78 FROM JAI_RCV_TRANSACTIONS
79 WHERE transaction_id = cp_transaction_id;
80
81 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
82 SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
83 parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate
84 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
85 , routing_header_id -- porting of Bug#3949109 (3927371)
86 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. , attribute3 online_claim_flag
87 , source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
88 , po_line_location_id --pramasub FP of Bug #4281841
89 FROM rcv_transactions
90 WHERE transaction_id = cp_transaction_id;
91
92 CURSOR c_excise_invoice_no(cp_shipment_line_id IN NUMBER) IS
93 SELECT excise_invoice_no, excise_invoice_date, online_claim_flag
94 FROM JAI_RCV_LINES
95 WHERE shipment_line_id = cp_shipment_line_id;
96 -- pramasub FP start
97 /*
98 || Start additions by ssumaith - Iprocurement Bug#4281841.
99 */
100
101 CURSOR check_rcpt_source(p_line_location_id IN NUMBER) IS
102 SELECT apps_source_code
103 FROM po_requisition_headers_all
104 WHERE requisition_header_id IN
105 (SELECT requisition_header_id
106 FROM po_requisition_lines_all
107 WHERE line_location_id = p_line_location_id
108 );
109
110 lv_apps_source_code VARCHAR2(30);
111
112 /*
113 || End additions by ssumaith - Iprocurement Bug#4281841
114 */
115 -- pramasub FP end
116
117 -- Constants that will be returned from functions or procedures. returned values should be compared with these values
118 MFG_ORGN CONSTANT VARCHAR2(1) := 'M';
119 TRADING_ORGN CONSTANT VARCHAR2(1) := 'T';
120 BONDED_SUBINV CONSTANT VARCHAR2(1) := 'B';
121 TRADING_SUBINV CONSTANT VARCHAR2(1) := 'T';
122
123 -- added by Vijay Shankar for Bug#3940588.
124 -- to support the deferred cenvat claim functionality. this is required because we obsoleted the old receipts code
125 PROCEDURE process_deferred_cenvat_claim(
126 p_batch_id IN NUMBER,
127 p_called_from IN VARCHAR2,
128 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
129 p_process_flag OUT NOCOPY VARCHAR2,
130 p_process_message OUT NOCOPY VARCHAR2
131 );
132
133 PROCEDURE process_batch(
134 errbuf OUT NOCOPY VARCHAR2,
135 retcode OUT NOCOPY VARCHAR2,
136 p_organization_id IN NUMBER,
137 pv_transaction_from IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
138 pv_transaction_to IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
139 p_transaction_type IN VARCHAR2,
140 p_parent_trx_type IN VARCHAR2,
141 p_shipment_header_id IN NUMBER, -- New parameter added by Vijay Shankar for Bug#3940588
142 p_receipt_num IN VARCHAR2,
143 p_shipment_line_id IN NUMBER, -- New parameter added by Vijay Shankar for Bug#3940588
144 p_transaction_id IN NUMBER,
145 p_commit_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
146 p_called_from IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Batch',
147 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
148 p_trace_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N'
149 p_request_id IN NUMBER DEFAULT NULL, -- CSahoo for Bug 5344225
150 p_group_id IN NUMBER DEFAULT NULL -- CSahoo for Bug 5344225
151 );
152
153 PROCEDURE process_transaction(
154 p_transaction_id IN NUMBER,
155 p_process_flag IN OUT NOCOPY VARCHAR2,
156 p_process_message IN OUT NOCOPY VARCHAR2,
157 p_cenvat_rg_flag IN OUT NOCOPY VARCHAR2,
158 p_cenvat_rg_message IN OUT NOCOPY VARCHAR2,
159 p_common_err_mesg OUT NOCOPY VARCHAR2,
160 p_called_from IN VARCHAR2,
161 p_simulate_flag IN VARCHAR2,
162 p_codepath IN OUT NOCOPY VARCHAR2,
163 -- following parameters introduced for second claim of receive transaction
164 p_process_special_reason IN VARCHAR2 DEFAULT NULL,
165 p_process_special_qty IN NUMBER DEFAULT NULL,
166 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
167 p_excise_processing_reqd IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT jai_constants.yes,
168 p_vat_processing_reqd IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT jai_constants.yes
169 );
170
171 PROCEDURE populate_details(
172 p_transaction_id IN NUMBER,
173 p_process_status OUT NOCOPY VARCHAR2,
174 p_process_message OUT NOCOPY VARCHAR2,
175 p_simulate_flag IN VARCHAR2,
176 p_codepath IN OUT NOCOPY VARCHAR2
177 );
178
179 PROCEDURE validate_transaction(
180 p_transaction_id IN NUMBER,
181 p_process_flag IN OUT NOCOPY VARCHAR2,
182 p_process_message IN OUT NOCOPY VARCHAR2,
183 p_cenvat_rg_flag IN OUT NOCOPY VARCHAR2,
184 p_cenvat_rg_message IN OUT NOCOPY VARCHAR2,
185 /* following two flags introduced by Vijay Shankar for Bug#4250236(4245089). VAT Implementation */
186 p_process_vat_flag IN OUT NOCOPY VARCHAR2,
187 p_process_vat_message IN OUT NOCOPY VARCHAR2,
188 p_called_from IN VARCHAR2,
189 p_simulate_flag IN VARCHAR2,
190 p_codepath IN OUT NOCOPY VARCHAR2
191 );
192
193 PROCEDURE process_rtv(
194 pv_errbuf OUT NOCOPY VARCHAR2,
195 pv_retcode OUT NOCOPY VARCHAR2,
196 pn_batch_num IN NUMBER,
197 pn_min_transaction_id IN NUMBER,
198 pn_max_transaction_id IN NUMBER,
199 pv_called_from IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
200 pv_commit_switch IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'Y',
201 pv_debug_switch IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'N'
202 );
203
204 FUNCTION process_iso_transaction(
205 p_transaction_id IN NUMBER,
206 p_shipment_line_id IN NUMBER
207 ) RETURN BOOLEAN;
208
209 FUNCTION get_trxn_tax_amount(
210 p_transaction_id IN NUMBER,
211 p_shipment_line_id IN NUMBER,
212 p_curr_conv_rate IN NUMBER,
213 p_return_in_inr_curr IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'Y'
214 ) RETURN NUMBER;
215
216 FUNCTION get_trxn_cenvat_amount(
217 p_transaction_id IN NUMBER,
218 p_shipment_line_id IN NUMBER,
219 p_organization_type IN VARCHAR2,
220 p_curr_conv_rate IN NUMBER
221 ) RETURN NUMBER;
222
223 FUNCTION get_apportion_factor(
224 p_transaction_id IN NUMBER
225 ) RETURN NUMBER;
226
227 -- Vijay Shankar for Bug#3940588. RECEIPTS DEPLUG
228 FUNCTION get_equivalent_qty_of_receive(
229 p_transaction_id IN NUMBER
230 ) RETURN NUMBER;
231
232 FUNCTION get_message(
233 p_message_code IN VARCHAR2
234 ) RETURN VARCHAR2;
235
236 FUNCTION get_object_code(
237 p_object_name IN VARCHAR2,
238 p_event_name IN VARCHAR2
239 ) RETURN VARCHAR2;
240
241 FUNCTION get_ancestor_id(
242 p_transaction_id IN NUMBER,
243 p_shipment_line_id IN NUMBER,
244 p_required_trx_type IN VARCHAR2
245 ) RETURN NUMBER;
246
247 FUNCTION get_accrue_on_receipt(
248 p_po_distribution_id IN NUMBER,
249 p_po_line_location_id IN NUMBER DEFAULT NULL
250 ) RETURN VARCHAR2;
251
252 END jai_rcv_trx_processing_pkg;