1 PACKAGE jai_rcv_rgm_claims_pkg AUTHID CURRENT_USER AS
2 /* $Header: jai_rcv_rgm_clm.pls 120.4 2007/05/28 10:10:39 sacsethi ship $ */
3 /****************************************************************************************************************************************************************************************
4
5 Change History -
6 1. 27-Jan-2005 Sanjikum for Bug #4248727 Version #115.0
7 New Package created for creating VAT Processing
8
9 2. 04/04/2005 Sanjikum for Bug #4279050 Version #115.1
10 Problem
11 -------
12 In the Procedure update_rcv_lines, For setting the flag lv_process_status_flag, first Partial Claim is checked and then Full Claimed,
13 which is creating the problem in case of full claim happens in the first installment
14
15 Fix
16 ---
17 1) In the procedure - update_rcv_lines, added one more parameter - p_shipment_header_id
18
19 3. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
20 Code is modified due to the Impact of Receiving Transactions DFF Elimination
21
22 * High Dependancy for future Versions of this object *
23
24 4. 08-Jun-2005 Version 116.1 jai_rcv_rgm_clm -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
25 as required for CASE COMPLAINCE.
26
27 5 28-05-2007 SACSETHI for bug 6071533 file Version 120.4
28
29 VAT CLAIM ACCOUNTING ENTRY IS NOT GETTING GENERATED FOR RECEIPTS
30
31 Dependncies - jai_rcv_rgm_clm.pls , jai_rcv_rgm_clm.plb
32
33 Future Dependencies For the release Of this Object:-
34 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
35 A datamodel change )
36
37 ============================================================================================================
38 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39 Current Version Current Bug Dependent Files Version Author Date Remarks
40 Of File On Bug/Patchset Dependent On
41 jai_ap_interface_pkg_s.sql
42 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
43 115.0 4248727 4245089 This is Part of VAT Enhancement, so dependent on VAT Enhancement
44 115.1 4279050 4279050 jai_rcv_rgm_claims_b.sql 115.3 Sanjikum 07/04/2005
45 115.1 4279050 4279050 ja_in_create_4279050.sql 115.0 Sanjikum 07/04/2005
46 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
47 ****************************************************************************************************************************************************************************************/
48
49 gv_debug CONSTANT BOOLEAN := TRUE;
50
51 gv_invoice_no_dflt CONSTANT VARCHAR2(5) := '-X9X'; --File.Sql.35 Cbabu
52 gd_invoice_date_dflt CONSTANT DATE := to_date('01/01/1900','DD/MM/YYYY');
53
54 CURSOR c_trx(cp_transaction_id IN NUMBER)
55 IS
56 SELECT *
57 FROM JAI_RCV_TRANSACTIONS
58 WHERE transaction_id = cp_transaction_id;
59
60 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
61 SELECT shipment_header_id,
62 shipment_line_id,
63 transaction_id,
64 transaction_type,
65 quantity,
66 unit_of_measure,
67 uom_code,
68 parent_transaction_id,
69 organization_id,
70 location_id,
71 subinventory,
72 currency_conversion_rate,
73 routing_header_id,
74 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. attribute_category attr_cat,
75 nvl(attribute5, 'XX') rma_type,
76 nvl(attribute4, 'N') generate_excise_invoice,
77 attribute3 online_claim_flag,
78 */
79 source_document_code,
80 po_header_id,
81 vendor_id,
82 vendor_site_id,
83 transaction_date
84 --,attribute_category,
85 --attribute4
86 FROM rcv_transactions
87 WHERE transaction_id = cp_transaction_id;
88
89 PROCEDURE get_location(
90 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
91 p_location_id OUT NOCOPY hr_locations_all.location_id%TYPE,
92 p_process_status OUT NOCOPY VARCHAR2,
93 p_process_message OUT NOCOPY VARCHAR2);
94
95 PROCEDURE generate_schedule(
96 p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT NULL,
97 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
98 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
102 p_override IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
99 p_transaction_id IN rcv_transactions.transaction_id%TYPE DEFAULT NULL,
100 p_tax_type IN JAI_CMN_TAXES_ALL.tax_type%TYPE DEFAULT NULL,
101 p_tax_id IN JAI_CMN_TAXES_ALL.tax_id%TYPE DEFAULT NULL,
103 p_process_status OUT NOCOPY VARCHAR2,
104 p_process_message OUT NOCOPY VARCHAR2,
105 /*Bug 5096787. Added by Lakshmi Gopalsami */
106 p_simulate_flag IN VARCHAR2 DEFAULT 'N' -- Date 28/05/2007 sacsethi for bug 6071533 Change default value from null to N
107 );
108
109 PROCEDURE insert_rcv_lines(
110 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
111 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
112 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
113 p_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE,
114 p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
115 p_process_status OUT NOCOPY VARCHAR2,
116 p_process_message OUT NOCOPY VARCHAR2);
117
118 PROCEDURE update_rcv_lines(
119 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
120 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
121 p_receipt_num IN JAI_RCV_LINES.receipt_num%TYPE DEFAULT NULL,
122 p_recoverable_amount IN jai_rcv_rgm_lines.recoverable_amount%TYPE DEFAULT NULL,
123 p_recovered_amount IN jai_rcv_rgm_lines.recovered_amount%TYPE DEFAULT NULL,
124 p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT -999,
125 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE, --File.Sql.35 Cbabu DEFAULT '-X9X',
126 p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE, --File.Sql.35 Cbabu DEFAULT TO_DATE('01/01/1900','DD/MM/YYYY'),
127 p_vendor_id IN po_vendors.vendor_id%TYPE DEFAULT -999,
128 p_vendor_site_id IN po_vendor_sites_all.vendor_site_id%TYPE DEFAULT NULL,
129 p_correct_receive_qty IN jai_rcv_rgm_lines.correct_receive_qty%TYPE DEFAULT NULL,
130 p_rtv_qty IN jai_rcv_rgm_lines.rtv_qty%TYPE DEFAULT NULL,
131 p_correct_rtv_qty IN jai_rcv_rgm_lines.correct_rtv_qty%TYPE DEFAULT NULL,
132 p_process_status OUT NOCOPY VARCHAR2,
133 p_process_message OUT NOCOPY VARCHAR2);
134
135 PROCEDURE process_vat(
136 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
137 p_process_status OUT NOCOPY VARCHAR2,
138 p_process_message OUT NOCOPY VARCHAR2);
139
140
141 PROCEDURE process_claim(
142 p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE,
143 p_regime_regno IN VARCHAR2 DEFAULT NULL,
144 p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
145 p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
146 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
147 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
148 p_batch_id IN jai_rcv_rgm_lines.batch_num%TYPE DEFAULT NULL,
149 p_force IN VARCHAR2 DEFAULT NULL,
150 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
151 p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE,
152 p_called_from IN VARCHAR2,
153 p_process_status OUT NOCOPY VARCHAR2,
154 p_process_message OUT NOCOPY VARCHAR2);
155
156
157 PROCEDURE process_no_claim(
158 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
159 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
160 p_batch_id IN jai_rcv_rgm_lines.batch_num%TYPE DEFAULT NULL,
161 p_regime_regno IN VARCHAR2 DEFAULT NULL,
162 p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
163 p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
164 p_process_status OUT NOCOPY VARCHAR2,
165 p_process_message OUT NOCOPY VARCHAR2,
166 /* Bug 5096787. Added by Lakshmi Gopalsami */
167 p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE DEFAULT NULL );
168
169 PROCEDURE process_batch(
170 errbuf OUT NOCOPY VARCHAR2,
171 retcode OUT NOCOPY VARCHAR2,
172 p_regime_id IN JAI_RGM_DEFINITIONS.regime_id%TYPE,
173 p_regime_regno IN VARCHAR2 DEFAULT NULL,
174 p_organization_id IN hr_all_organization_units.organization_id%TYPE DEFAULT NULL,
175 p_location_id IN hr_locations_all.location_id%TYPE DEFAULT NULL,
176 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE DEFAULT NULL,
177 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
178 p_batch_id IN jai_rcv_rgm_lines.batch_num%TYPE DEFAULT NULL,
179 p_force IN VARCHAR2 DEFAULT NULL,
180 p_commit_switch IN VARCHAR2,
181 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
182 pv_invoice_date IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
183 p_called_from IN VARCHAR2);
184
185 PROCEDURE do_rtv_accounting(
186 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
187 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
188 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
189 p_called_from IN VARCHAR2,
190 p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE,
191 p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE,
192 p_process_status OUT NOCOPY VARCHAR2,
193 p_process_message OUT NOCOPY VARCHAR2);
194
195 PROCEDURE do_rma_accounting(
196 p_transaction_id IN rcv_transactions.transaction_id%TYPE,
197 p_process_status OUT NOCOPY VARCHAR2,
198 p_process_message OUT NOCOPY VARCHAR2);
199
200 END jai_rcv_rgm_claims_pkg;