[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT subinventory
FROM rcv_transactions
WHERE shipment_line_id = cp_shipment_line_id
AND parent_transaction_id = cp_receive_trx_id
AND transaction_type = cp_transaction_type --'DELIVER' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND transaction_id > cp_receive_trx_id;
SELECT location_id
FROM JAI_INV_SUBINV_DTLS
WHERE organization_id = cp_organization_id
AND sub_inventory_name = cp_subinventory;
SELECT nvl(inventory_organization_id, -99999) inventory_organization_id
FROM hr_locations_all
WHERE location_id = cp_location_id;
In the Procedure update_rcv_lines, For setting the flag lv_process_status_flag, first Partial Claim is checked and then Full Claimed,
which is creating the problem in case of full claim happens in the first installment
Fix
---
1) In the Procedure update_rcv_lines, For setting the flag lv_process_status_flag, now first Full Claimed is checked
and then Partial Claim
2) In the procedure - update_rcv_lines, added one more parameter - p_shipment_header_id
5. 08/04/2005 Sanjikum for Bug #4279050 Version #116.0 (115.5)
Re-checked the same file again
6. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
Code is modified due to the Impact of Receiving Transactions DFF Elimination
* High Dependancy for future Versions of this object *
7. 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
8. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.3
Removal of SQL LITERALs is done
9. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
GL Sources and GL Categories got changed. Refer bug for the details
10.02-Aug-2005 Ramananda for Bug#4530112. File Version 120.2
Problem
-------
In case of RTV, if VAT Claim is not done, system is asking to make the VAT Claim first
Fix
---
1) In the Procedure process_vat, added cursor c_rcv_rgm_lines
2) In the Procedure process_vat, added an IF condition -
"IF r_rcv_rgm_lines.invoice_no IS NULL AND (r_trx.transaction_type = 'RETURN TO VENDOR'
OR (r_trx.transaction_type = 'CORRECT' AND r_trx.parent_transaction_type IN ('RECEIVE', 'MATCH', 'RETURN TO VENDOR') ) ) THEN"
Added the following as the object is not compiled because of R12 changes.
These were introudced as default values were removed from the procedure spec. and function spec.
1. Procedue call update_rcv_lines is changed:
p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
2. Procedure call generate_schedule is changed:
p_override => jai_constants.no
Dependency Due to this Bug:-
File jai_rcv_tax.plb (120.4) is changed as part of this Bug,
so this object is dependent on current Bug and object jai_rcv_tax.plb(120.4)
11. 02-Aug-2005 Ramananda for Bug#4519719. File Version 120.2
Issue : Processing should not take place if no VAT type of taxes
Fis : Added a condition to check if VAT type of taxes exist in the receipt
before the call to jai_rcv_rgm_claims_pkg.insert_rcv_lines. If no VAT type of taxes
exist, the return from the procedure.
Dependency due to this bug:-
jai_rcv_trx_prc.plb (120.4)
12. 02-Sep-2005 Bug4589354. Added by Lakshmi Gopalsami version 120.3
Commented the following condition.
OR (r_base_trx.source_document_code = 'REQ' and
Dependencies :
jai_rcv_trx_prc.plb 120.6
jai_rcv_rgm_clm.plb 120.3
13. 25-Jan-2006 Bug4929929. Added by Lakshmi Gopalsami Version 120.4
Removed the NVL in cursor c_receipt_source_code
as shipment_header_id and shipment_line_id cannot be null.
14. 26-FEB-2007 SSAWANT , File version 120.7
Forward porting the change in 11.5 bug 4760317 to R12 bug no 4950914
a) Following changes are done in procedure - generate_schedule
- In the definition of procedure added one more parameter - p_simulate_flag
- In the cursor - cur_tax, added one more where condition - "AND NVL(a.tax_amount,0) <> 0;"
- In loop of cursor - cur_tax, after calling procedure - generate_term_schedules, added a delete statement
- In loop of cursor - cur_tax, after calling procedure - generate_term_schedules, added the code for
b) Following changes are done in procedure - process_vat
- Added a new condition to return from the procedure, if ja_in_rcv_transactions.process_vat_flag is 'SS'.
After this added the call to procedure - generate_schedule
c) Following changes are done in procedure - process_claim
- Before call to procedure - jai_rgm_trx_recording_pkg.insert_vat_repository_entry, added the condition that
either of debit or credit amount should be null
d) Following changes are done in procedure - process_no_claim
- Changed the definition of procedure
- Changed the definition of cursor - c_shipment_lines and changed the statement to open this cursor
- Before call to procedure - ja_in_receipt_accounting_pkg.process_transaction, added the condition that
either of debit or credit amount should be null
e) Following changes are done in procedure - process_batch
- Commented the condition - "IF p_batch_id IS NULL AND p_shipment_header_id IS NULL AND p_shipment_line_id IS NULL THEN"
- Changed the call to procedure - process_no_claim
f) Following changes are done in procedure - do_rtv_accounting
- Before call to procedure - jai_rgm_trx_recording_pkg.insert_vat_repository_entry, added the condition that
either of debit or credit amount should be null
g) Following changes are done in procedure - do_rma_accounting
- In the loop of cursor - cur_tax, Added the following condition -
IF NVL(rec_tax.tax_amount,0) = 0 THEN
goto END_OF_LOOP;
1. Removed update of ATTRIBUTE (DFF) columns of RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES table
2. added excise_invoice_no and excise_invoice_date in cursor c_shipment_lines
3. Changed reference of r_trx.excise_invoice_no and r_trx.excise_invoice_date to
rec_lines.excise_invoice_no and rec_lines.excise_invoice_date resp.
19 4-JUN-2009 Bug 8488470 File version 120.6.12000000.6 / 120.15.12010000.3 / 120.17
Issue - Accounting entries not rounded properly during CORRECT / RTV transactions.
Cause - The unrounded amount is being used to generate the schedules. After this,
the installment amounts are rounded as per setup. But the last installment
gets unrounded to account for the difference between total of all installment
amounts and the total recoverable amount. If the claim is done in single
installment, then effectively there is no rounding.
Fix - Modified procedure generate_schedule to use the rounded amount to generate
claim schedule, so that all installments would be populated with rounded
amounts.
20. 02-JUL-2009 Bgowrava for Bug#8414075 , File Version 120.6.12000000.7 / 120.15.12010000.4 / 120.18
Addded nvl condition to ln_process_special_amount while calling the procedure jai_rcv_deliver_rtr_pkg.process_transaction.
Also rounded the ln_amount value according to rounding factor mentioned in the tax setupthe same is passed in the
call to jai_cmn_rgm_terms_pkg.generate_term_schedules.
21 09-AUG-2009 Bug 8648138
Issue - If VAT is unclaimed after running "Mass Addtions Create" program, the unclaimed amount does not
flow to assets when "Mass Additions Create" is run again.
Fix - Added code in process_deferred_cenvat_claim procedure to update related flags in ap_invoice_distributions_all
for the matched invoices so that the tax distributions will be picked up by the "Mass Additions Create" program.
22. 20-AUG-2009 JMEENA for bug#8302581
Modified procedure do_rma_accounting and added code to debit the Liability account and credit Interim Liabality
account for the Non Recoverable Item In RMA order having VAT taxes.
Called procedure jai_cmn_rgm_recording_pkg.insert_vat_repository_entry to insert records in the jai_rgm_trx_records
table to reverse the VAT settlement entries for the non recoverable Items in the RMA Order.
23. 05-may-2010 vkaranam for bug#9662961
Issue:
RTV accounting entries for VAT is hitting the final recovery account due to which the VAT settlement
details are not matich with the GL balances.
Fix:
RTV accounting entries for VAT shall hit the liability account instead of the final recovery amount.
passed the VAT liability account while calling jai_rcv_accounting_pkg.process_transaction.
changes are done in do_rtv_accounting procedure.
24. 05-aug-2010 vkaranam for bug#9970962
Issue:VAT NOT CLAIMED ON RECEIPT BUT CLAIM STATUS IS FULL CLAIMED
Fix:
changes are done in update_rcv_lines procedure.
25. 17-Mar-2011 abezgam for Bug#11880460
Description: Following errors encountered by autobuild when compiling the package jai_rcv_rgm_claims_pkg.
1. wrong number or types of arguments in call to insert_vat_repository_entry
2. 'TEMP_REC'.'SHIPMENT_LINE_ID': invalid identifier
Fix:
1. Corrected the parameters in the call to the procedure jai_cmn_rgm_recording_pkg.insert_vat_repository_entry.
2. Made the correction in the procedure process_no_claim. Made it in sync with the 12.1 code.
25. 28sep12 vkaranam for bug#14061440
Issue:VAT claim accounting is happening on the sysdate for the backdated receipt.
fix:
changes are done in procedure process_claim,do_rma_accounting.
After the fix:
----------------
for Receive ,Correct of Receive:
accounting date is the receipt transaction date
transaction date in the repository is receipt transaction date
For RTV:no changes required as RTV VAT credit can be reversed (unclaimed)
only on the sysdate.
accounting date is the sysdate
transaction date in the repository is sysdate
For RMA:
accounting date is the transaction date of RMA receipt
transaction date in the repository is transaction date of RMA receipt
Future Dependencies For the release Of this Object:-
(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/
A datamodel change )
============================================================================================================
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
jai_ap_interface_pkg_b.sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.0 4248727 4245089 This is Part of VAT Enhancement, so dependent on VAT Enhancement
115.2 4250171 4250171 There are changes done for OPM. So dependency is introduced
115.3 4278511 4278511 There are changes done for OPM. So dependency is introduced
115.4 4279050 4279050 jai_rcv_rgm_claims_s.sql 115.1 Sanjikum 07/04/2005
115.4 4279050 4279050 ja_in_create_4279050.sql 115.0 Sanjikum 07/04/2005
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
****************************************************************************************************************************************************************************************/
p_process_status := jai_constants.successful;
SELECT shipment_header_id, shipment_line_id
FROM JAI_RCV_LINES
WHERE shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
ORDER BY shipment_line_id;
SELECT transaction_id,
transaction_type,
transaction_date,
tax_transaction_id,
parent_transaction_type,
currency_conversion_rate,
quantity,
DECODE(transaction_type, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT',
DECODE(parent_transaction_type, 'RECEIVE', SIGN(quantity), 'RETURN TO VENDOR', SIGN(quantity)*-1)) quantity_multiplier
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id)
AND transaction_id = NVL(cp_transaction_id, transaction_id)
AND (
transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
OR
( transaction_type = 'CORRECT'
AND parent_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
)
)
ORDER BY transaction_id;
SELECT DECODE(a.currency, jai_constants.func_curr, a.tax_amount*(b.mod_cr_percentage/100),
a.tax_amount*cp_currency_conversion_rate*(b.mod_cr_percentage/100)) tax_amount --Removed Round condition by Bgowrava for Bug#8414075
/*DECODE(a.currency, jai_constants.func_curr, a.tax_amount, a.tax_amount*cp_currency_conversion_rate),
NVL(b.rounding_factor, 0) Commented by Nitin for Bug:# 6681800
) tax_amount*/,
a.tax_type,
a.tax_id,
NVL(b.rounding_factor,0) rounding_factor
FROM JAI_RCV_LINE_TAXES a,
JAI_CMN_TAXES_ALL b
WHERE a.transaction_id = cp_transaction_id
AND a.tax_type IN ( select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime
)
AND a.tax_id = b.tax_id
AND a.modvat_flag = 'Y'
--Added for Bug#4950914
AND NVL(a.tax_amount,0) <> 0;
SELECT term_id, rcv_rgm_line_id, receipt_date
FROM jai_rcv_rgm_lines
WHERE shipment_line_id = cp_shipment_line_id;
SELECT SUM(installment_amount) total_installment_amount, MAX(installment_no) max_installment_no
FROM jai_rgm_trm_schedules_t
WHERE schedule_id = cp_schedule_id;
SELECT COUNT(*) count
FROM jai_rcv_rgm_claims
WHERE rcv_rgm_line_id = cp_rcv_rgm_line_id
AND transaction_id = cp_transaction_id
AND tax_id = cp_tax_id
AND installment_no IN ( SELECT installment_no
FROM jai_rgm_trm_schedules_t
WHERE schedule_id = cp_schedule_id);
DELETE jai_rcv_rgm_claims
WHERE shipment_header_id = NVL(p_shipment_header_id, shipment_header_id)
AND shipment_line_id = NVL(p_shipment_line_id, shipment_line_id)
AND transaction_id = NVL(p_transaction_id, transaction_id)
AND tax_type = NVL(p_tax_type, tax_type)
AND tax_id = NVL(p_tax_id, tax_id);
DELETE jai_rgm_trm_schedules_t
WHERE schedule_id = ln_schedule_id;
DELETE jai_rgm_trm_schedules_t
WHERE schedule_id = ln_schedule_id;
UPDATE jai_rgm_trm_schedules_t
SET installment_amount = ROUND(installment_amount, tax_rec.rounding_factor)
WHERE schedule_id = ln_schedule_id;
UPDATE jai_rgm_trm_schedules_t
SET installment_amount = installment_amount + ln_amount - r_sum_schedules.total_installment_amount
WHERE installment_no = r_sum_schedules.max_installment_no
AND schedule_id = ln_schedule_id;
UPDATE jai_rgm_trm_schedules_t
SET installment_amount = installment_amount*rec_txns.quantity_multiplier
WHERE schedule_id = ln_schedule_id;
INSERT
INTO jai_rcv_rgm_claims
(
CLAIM_SCHEDULE_ID,
RCV_RGM_LINE_ID,
Shipment_header_id,
Shipment_line_id,
Regime_code,
Tax_transaction_id,
Transaction_type,
Transaction_id,
Parent_transaction_type,
Installment_no,
Installment_amount,
Claimed_amount,
Scheduled_date,
claimed_date,
Status,
Manual_claim_flag,
Remarks,
Tax_type,
Tax_id,
Trx_tax_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT jai_rcv_rgm_claims_s.NEXTVAL,
r_term.rcv_rgm_line_id,
rec_lines.shipment_header_id,
rec_lines.shipment_line_id,
jai_constants.vat_regime,
rec_txns.tax_transaction_id,
rec_txns.transaction_type,
rec_txns.transaction_id,
rec_txns.parent_transaction_type,
installment_no,
installment_amount,
NULL,
installment_date,
NULL,
'N',
NULL,
NULL,
tax_rec.tax_type,
tax_rec.tax_id,
NULL,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id
FROM jai_rgm_trm_schedules_t
WHERE schedule_id = ln_schedule_id;
DELETE jai_rgm_trm_schedules_t
WHERE schedule_id = ln_schedule_id;
PROCEDURE insert_rcv_lines(
p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE,
p_transaction_id IN rcv_transactions.transaction_id%TYPE,
p_regime_code IN JAI_RGM_DEFINITIONS.regime_code%TYPE,
p_simulate_flag IN VARCHAR2, --File.Sql.35 Cbabu DEFAULT 'N',
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2)
IS
r_trx c_trx%ROWTYPE;
SELECT nvl(rtl.tax_amount, 0) tax_amount,
nvl(rtl.modvat_flag, 'N') modvat_flag,
nvl(rtl.currency, jai_constants.func_curr) currency,
nvl(jtc.rounding_factor, 0) rnd,
nvl(jtc.mod_cr_percentage, 0) modvat_percentage /*Added by Nitin for bug # 6681800*/
FROM JAI_RCV_LINE_TAXES rtl,
JAI_CMN_TAXES_ALL jtc
WHERE shipment_line_id = cp_shipment_line_id
AND jtc.tax_id = rtl.tax_id
AND rtl.modvat_flag = 'Y'
AND jtc.tax_type IN (select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime);
SELECT a.shipment_line_id, a.transaction_id, a.inventory_item_id, a.receipt_num, b.line_num
, excise_invoice_no, excise_invoice_date -- brathod, Bug# 6109941
FROM JAI_RCV_LINES a,
rcv_shipment_lines b
WHERE a.shipment_header_id = b.shipment_header_id
AND a.shipment_line_id = b.shipment_line_id
AND a.shipment_header_id = NVL(cp_shipment_header_id,a.shipment_header_id)
AND a.shipment_line_id = NVL(cp_shipment_line_id, a.shipment_line_id);
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = NVL(cp_regime_id, regime_id)
AND regime_code = NVL(cp_regime_code, regime_code);
SELECT receipt_source_code
FROM rcv_shipment_headers a,
rcv_shipment_lines b
WHERE a.shipment_header_id = b.shipment_header_id
AND a.shipment_header_id = cp_shipment_header_id
AND b.shipment_line_id = cp_shipment_line_id;
SELECT 1
FROM JAI_RCV_LINE_TAXES a , jai_regime_tax_types_v b
WHERE shipment_line_id = cp_shipment_line_id AND
b.regime_code= jai_constants.vat_regime
and b.tax_type = a.tax_type ;
fnd_file.put_line(fnd_file.log, '1 insert_rcv_lines: Bef Main loop:');
select count(1)
into ln_dup_chk
from jai_rcv_rgm_lines
where transaction_id = r_trx.transaction_id;
fnd_file.put_line(fnd_file.log, '2 insert_rcv_lines:ln_dup_chk:'||ln_dup_chk);
fnd_file.put_line(fnd_file.log, '3 insert_rcv_lines:ln_recoverable_amount:'||ln_recoverable_amount);
fnd_file.put_line(fnd_file.log, '4 insert_rcv_lines: after get_location:'||r_base_trx.location_id);
INSERT
INTO jai_rcv_rgm_lines
(
RCV_RGM_LINE_ID,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
ORGANIZATION_ID,
LOCATION_ID,
INVENTORY_ITEM_ID,
RECEIPT_NUM,
RECEIPT_DATE,
REGIME_CODE,
REGIME_ITEM_CLASS,
TRANSACTION_ID,
RECOVERABLE_AMOUNT,
RECOVERED_AMOUNT,
PROCESS_STATUS_FLAG,
TERM_ID,
INVOICE_NO,
INVOICE_DATE,
VENDOR_ID,
VENDOR_SITE_ID,
RECEIPT_SOURCE_CODE,
RECEIVE_QTY,
CORRECT_RECEIVE_QTY,
RTV_QTY,
CORRECT_RTV_QTY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
jai_rcv_rgm_lines_s.NEXTVAL,
r_base_trx.shipment_header_id,
r_base_trx.shipment_line_id,
r_base_trx.organization_id,
ln_location_id,
rec_lines.inventory_item_id,
rec_lines.receipt_num,
r_base_trx.transaction_date,
jai_constants.vat_regime,
lv_item_class,
r_trx.transaction_id,
ln_recoverable_amount,
0,
DECODE(NVL(ln_recoverable_amount,0), 0, 'X', 'N'),
ln_term_id,
lv_def_vat_invoice_no, /* bduvarag Bug# 5899383*/
ld_def_vat_invoice_Date, /* bduvarag Bug# 5899383*/
NULL,
NULL,
r_receipt_source_code.receipt_source_code,
r_base_trx.quantity,
NULL,
NULL,
NULL,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id);
END insert_rcv_lines;
PROCEDURE update_rcv_lines(
p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE DEFAULT NULL,
p_receipt_num IN JAI_RCV_LINES.receipt_num%TYPE DEFAULT NULL,
p_recoverable_amount IN jai_rcv_rgm_lines.recoverable_amount%TYPE DEFAULT NULL,
p_recovered_amount IN jai_rcv_rgm_lines.recovered_amount%TYPE DEFAULT NULL,
p_term_id IN jai_rgm_terms.term_id%TYPE DEFAULT -999,
p_invoice_no IN JAI_RCV_TRANSACTIONS.vat_invoice_no%TYPE, --File.Sql.35 Cbabu DEFAULT '-X9X',
p_invoice_date IN JAI_RCV_TRANSACTIONS.vat_invoice_date%TYPE, --File.Sql.35 Cbabu DEFAULT TO_DATE('01/01/1900','DD/MM/YYYY'),
p_vendor_id IN po_vendors.vendor_id%TYPE DEFAULT -999,
p_vendor_site_id IN po_vendor_sites_all.vendor_site_id%TYPE DEFAULT NULL,
p_correct_receive_qty IN jai_rcv_rgm_lines.correct_receive_qty%TYPE DEFAULT NULL,
p_rtv_qty IN jai_rcv_rgm_lines.rtv_qty%TYPE DEFAULT NULL,
p_correct_rtv_qty IN jai_rcv_rgm_lines.correct_rtv_qty%TYPE DEFAULT NULL,
p_process_status OUT NOCOPY VARCHAR2,
p_process_message OUT NOCOPY VARCHAR2)
IS
ln_recoverable_amount NUMBER;
SELECT NVL(SUM(installment_amount),0) recoverable_amount,
NVL(SUM(claimed_amount),0) recovered_amount
FROM jai_rcv_rgm_claims
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id;
UPDATE JAI_RCV_RGM_LINES
SET recoverable_amount = r_total_amount.recoverable_amount,
recovered_amount = r_total_amount.recovered_amount,
correct_receive_qty = NVL(correct_receive_qty,0) + NVL(p_correct_receive_qty,0),
rtv_qty = NVL(rtv_qty,0) + NVL(p_rtv_qty,0),
correct_rtv_qty = NVL(correct_rtv_qty,0) + NVL(p_correct_rtv_qty,0)
WHERE shipment_line_id = NVL(p_shipment_line_id,shipment_line_id)
AND receipt_num = NVL(p_receipt_num, receipt_num)
RETURNING recoverable_amount, recovered_amount INTO ln_recoverable_amount, ln_recovered_amount;
UPDATE JAI_RCV_RGM_LINES
SET process_status_flag = nvl(lv_process_status_flag,process_status_flag) --added nvl process_status_flag condition for bug#9970962
WHERE shipment_line_id = NVL(p_shipment_line_id,shipment_line_id)
AND receipt_num = NVL(p_receipt_num, receipt_num);
END update_rcv_lines;
SELECT SUM(DECODE(currency, jai_constants.func_curr, tax_amount, tax_amount*cp_currency_conversion_rate)) tax_amount
FROM JAI_RCV_LINE_TAXES
WHERE transaction_id = cp_transaction_id
AND tax_type in (select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime)
AND NVL(modvat_flag,'N') = 'Y';*/
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = NVL(cp_regime_id, regime_id)
AND regime_code = NVL(cp_regime_code, regime_code);
SELECT vat_invoice_no, vat_invoice_date
FROM JAI_RCV_TRANSACTIONS
WHERE transaction_id = cp_transaction_id;
SELECT currency_conversion_rate,
quantity,
DECODE(transaction_type, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT',
DECODE(parent_transaction_type, 'RECEIVE', SIGN(quantity), 'RETURN TO VENDOR', SIGN(quantity)*-1)) quantity_multiplier
FROM JAI_RCV_TRANSACTIONS
WHERE transaction_id = cp_transaction_id
AND (
transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
OR
( transaction_type = 'CORRECT'
AND parent_transaction_type IN ('RECEIVE', 'RETURN TO VENDOR')
)
);*/
SELECT *
FROM jai_rcv_rgm_lines
WHERE shipment_line_id = cp_shipment_line_id;
update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
p_shipment_line_id => r_base_trx.shipment_line_id,
p_rtv_qty => ln_rtv_qty,
p_correct_receive_qty => ln_correct_receive_qty,
p_correct_rtv_qty => ln_correct_rtv_qty,
p_process_message => lv_process_message,
p_process_status => lv_process_status,
/* Added the following by Ramananda for bug#4530112 */
p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35
p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35
);
fnd_file.put_line(fnd_file.log, '1 RECEIVE: Before insert_rcv_lines:');
insert_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
p_shipment_line_id => r_base_trx.shipment_line_id,
p_transaction_id => r_trx.transaction_id,
p_regime_code => jai_constants.vat_regime,
p_process_message => lv_process_message,
p_process_status => lv_process_status,
p_simulate_flag => lv_simulate_flag --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.log, '1.1 RECEIVE: After insert_rcv_lines:');
update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
p_shipment_line_id => r_base_trx.shipment_line_id,
p_rtv_qty => r_trx.quantity,
--p_recoverable_amount => ln_total_cenvat_amount,
p_process_message => lv_process_message,
p_process_status => lv_process_status,
p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.log, 'After update_rcv_lines:');
UPDATE JAI_RCV_TRANSACTIONS
SET vat_invoice_no = r_vat_invoice.vat_invoice_no,
vat_invoice_date = r_vat_invoice.vat_invoice_date
WHERE transaction_id = r_trx.transaction_id;
update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
p_shipment_line_id => r_base_trx.shipment_line_id,
p_correct_receive_qty => r_base_trx.quantity,
--p_recoverable_amount => ln_total_cenvat_amount,
p_process_status => lv_process_status,
p_process_message => lv_process_message,
p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.log, 'After Update_rcv_lines');
UPDATE JAI_RCV_TRANSACTIONS
SET vat_invoice_no = r_trx.vat_invoice_no,
vat_invoice_date = TRUNC(SYSDATE)
WHERE transaction_id = r_base_trx.transaction_id; --r_trx now points to the parent transaction
update_rcv_lines(p_shipment_header_id => r_base_trx.shipment_header_id,
p_shipment_line_id => r_base_trx.shipment_line_id,
p_correct_rtv_qty => r_base_trx.quantity,
p_process_message => lv_process_message,
p_process_status => lv_process_status,
p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.log, 'After Update_rcv_lines');
UPDATE JAI_RCV_TRANSACTIONS
SET PROCESS_VAT_STATUS = SUBSTR(p_process_status,1,2),
process_vat_message = SUBSTR(p_process_message,1,1000)
WHERE transaction_id = p_transaction_id;
SELECT b.organization_id,
b.location_id,
b.shipment_header_id,
b.shipment_line_id,
b.scheduled_date,
b.transaction_id,
b.tax_type,
b.installment_amount,
b.installment_no,
b.invoice_no,
b.invoice_date,
b.receipt_num,
b.rcv_rgm_line_id
FROM JAI_RGM_ORG_REGNS_V a,
jai_rcv_rgm_txns_v b,
JAI_RGM_DEFINITIONS c
WHERE a.regime_code = c.regime_code
AND c.regime_id = cp_regime_id
AND a.attribute_value = NVL(cp_regime_regno, a.attribute_value)
AND a.attribute_type_code = jai_constants.rgm_attr_type_code_primary --'PRIMARY'
AND a.attribute_code = jai_constants.attr_code_regn_no --'REGISTRATION_NO'
AND a.organization_id = NVL(cp_organization_id, a.organization_id)
AND a.location_id = NVL(cp_location_id, a.location_id)
AND b.shipment_header_id = NVL(cp_shipment_header_id,b.shipment_header_id)
AND b.shipment_line_id = NVL(cp_shipment_line_id, b.shipment_line_id)
AND ( NVL(cp_batch_id,0) = 0
OR (NVL(cp_batch_id,0) <> 0 AND b.batch_num = cp_batch_id)
)
AND a.organization_id = b.organization_id
AND a.location_id = b.location_id
AND b.scheduled_date <= DECODE(cp_force, 'Y', b.scheduled_date, SYSDATE)
AND b.invoice_no IS NOT NULL
AND b.process_status_flag NOT IN ('Z') /* 'Z' meaning the line is marked for UNCLAIM, but not yet processed*/
AND NVL(b.installment_amount,0) <> 0;
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = NVL(cp_regime_id, regime_id)
AND regime_code = NVL(cp_regime_code, regime_code);
SELECT MIN(claim_schedule_id) claim_schedule_id
FROM jai_rcv_rgm_claims
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND tax_type = cp_tax_type
AND installment_no = cp_installment_no
AND nvl(status,'N') = 'N';
SELECT a.vat_invoice_no, a.vat_invoice_date, b.transaction_id, a.tax_transaction_id,
a.excise_invoice_no , a.excise_invoice_Date /*bduvarag Bug5899383*/
,a.transaction_date /*added by vkaranam for bug#14061440*/
FROM JAI_RCV_TRANSACTIONS a,
JAI_RCV_LINES b
WHERE a.transaction_id = b.transaction_id
AND b.shipment_header_id = cp_shipment_header_id
AND b.shipment_line_id = cp_shipment_line_id;
UPDATE JAI_RCV_TRANSACTIONS
SET vat_invoice_no = rec_claims.invoice_no,
vat_invoice_date = rec_claims.invoice_date
WHERE transaction_id = r_vat_invoice.transaction_id;
UPDATE JAI_RCV_LINES
SET excise_invoice_no = rec_claims.invoice_no,
excise_invoice_Date = rec_claims.invoice_date
WHERE shipment_header_id = rec_claims.shipment_header_id
AND excise_invoice_no IS NULL;
UPDATE jai_rcv_transactions
SET excise_invoice_no = rec_claims.invoice_no,
excise_invoice_Date = rec_claims.invoice_date
WHERE transaction_id = r_vat_invoice.transaction_id
AND excise_invoice_no IS NULL;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => r_regime.regime_id,
pv_tax_type => rec_claims.tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => rec_claims.organization_id,
pn_location_id => rec_claims.location_id,
pv_source => jai_constants.source_rcv,
pv_source_trx_type => lv_source_trx_type,
pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
pn_source_id => nvl(r_vat_invoice.tax_transaction_id, r_vat_invoice.transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay */
pd_transaction_date => nvl(ld_accounting_date,trunc(sysdate)),--14061440
pv_account_name => lv_account_name,
pn_charge_account_id => ln_code_combination_id,
pn_balancing_account_id => ln_interim_recovery_account,
pn_credit_amount => ln_entered_cr,
pn_debit_amount => ln_entered_dr,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => r_claim_schedule.claim_schedule_id,
pn_batch_id => NULL,
pn_inv_organization_id => rec_claims.organization_id,
pv_invoice_no => lv_invoice_no,
pd_invoice_date => ld_invoice_date,
pv_called_from => 'JAI_RGM_CLAIM_PKG.PROCESS_CLAIM',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
--Added by Bo Li for bug9305067 2010-4-14 BEGIN
--------------------------------------------------
pv_trx_reference_context => NULL,
pv_trx_reference1 => NULL,
pv_trx_reference2 => NULL,
pv_trx_reference3 => NULL,
pv_trx_reference4 => NULL,
pv_trx_reference5 => NULL
--------------------------------------------------
--Added by Bo Li for bug9305067 2010-4-14 END
);
UPDATE jai_rcv_rgm_claims
SET status = 'Y',
claimed_amount = installment_amount,
claimed_date = TRUNC(SYSDATE)
WHERE shipment_header_id = rec_claims.shipment_header_id
AND shipment_line_id = rec_claims.shipment_line_id
AND tax_type = rec_claims.tax_type
AND installment_no = rec_claims.installment_no
AND status <> 'Y';
update_rcv_lines(p_shipment_header_id => rec_claims.shipment_header_id,
p_shipment_line_id => rec_claims.shipment_line_id,
p_recovered_amount => rec_claims.installment_amount,
p_process_message => lv_process_message,
p_process_status => lv_process_status,
p_invoice_no => jai_rcv_rgm_claims_pkg.gv_invoice_no_dflt, --File.Sql.35 Cbabu
p_invoice_date => jai_rcv_rgm_claims_pkg.gd_invoice_date_dflt --File.Sql.35 Cbabu
);
SELECT shipment_header_id,
shipment_line_id,
receipt_num,
transaction_id
FROM jai_rcv_rgm_lines
WHERE (( NVL(cp_batch_id,0) = 0
AND shipment_header_id = NVL(cp_shipment_header_id, shipment_header_id)
AND shipment_line_id = NVL(cp_shipment_line_id, shipment_line_id))
OR ( NVL(cp_batch_id,0) <> 0
AND batch_id = cp_batch_id))
AND process_status_flag = 'Z'
ORDER BY transaction_id;*/
SELECT b.organization_id,
b.location_id,
b.shipment_header_id,
b.shipment_line_id,
b.transaction_id,
b.receipt_num,
b.rcv_rgm_line_id
FROM JAI_RGM_ORG_REGNS_V a,
jai_rcv_rgm_lines b,
JAI_RGM_DEFINITIONS c
WHERE a.regime_code = c.regime_code
AND c.regime_id = cp_regime_id
AND a.attribute_value = NVL(cp_regime_regno, a.attribute_value)
AND a.attribute_type_code = jai_constants.rgm_attr_type_code_primary --'PRIMARY'
AND a.attribute_code = jai_constants.attr_code_regn_no --'REGISTRATION_NO'
AND a.organization_id = NVL(cp_organization_id, a.organization_id)
AND a.location_id = NVL(cp_location_id, a.location_id)
AND b.shipment_header_id = NVL(cp_shipment_header_id,b.shipment_header_id)
AND b.shipment_line_id = NVL(cp_shipment_line_id, b.shipment_line_id)
AND ( NVL(cp_batch_id,0) = 0
OR (NVL(cp_batch_id,0) <> 0 AND b.BATCH_NUM = cp_batch_id)
)
AND a.organization_id = b.organization_id
AND a.location_id = b.location_id
AND b.process_status_flag = 'Z'; /* 'Z' meaning the line is marked for UNCLAIM, but not yet processed*/
SELECT jtl.tax_type,
/*Added by Nitin for Bug # 6681800 */ SUM(DECODE(jtl.currency, jai_constants.func_curr, jtl.tax_amount*(rtl.mod_cr_percentage/100), jtl.tax_amount*(rtl.mod_cr_percentage/100)*cp_currency_conversion_rate)) tax_amount
/*Commented by Nitin for bug :6681800 SUM(DECODE(currency, jai_constants.func_curr, tax_amount, tax_amount*cp_currency_conversion_rate)) tax_amount*/
FROM JAI_RCV_LINE_TAXES jtl ,JAI_CMN_TAXES_ALL rtl /* Need to have join with JAI_CMN_TAXES_ALL*/
WHERE jtl.transaction_id = cp_transaction_id
AND jtl.tax_type in (select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime)
AND NVL(jtl.modvat_flag,'N') = 'Y'
AND jtl.tax_id = rtl.tax_id -- Bug 7454592. Added by Lakshmi Gopalsami
GROUP BY jtl.tax_type;
SELECT SUM(ROUND(DECODE(a.currency, jai_constants.func_curr, /*Added by Nitin for bug # 6681800*/a.tax_amount*(b.mod_cr_percentage/100), a.tax_amount*(b.mod_cr_percentage/100)*cp_currency_conversion_rate),NVL(b.rounding_factor,1))) tax_amount
FROM JAI_RCV_LINE_TAXES a,
JAI_CMN_TAXES_ALL b
WHERE a.transaction_id = cp_transaction_id
AND a.tax_type in (select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime)
AND a.tax_id = b.tax_id
AND NVL(a.modvat_flag,'N') = 'Y';
SELECT transaction_id,
organization_id,
location_id,
currency_conversion_rate
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND transaction_type = 'RECEIVE';
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = NVL(cp_regime_id, regime_id)
AND regime_code = NVL(cp_regime_code, regime_code);
SELECT receiving_account_id
FROM rcv_parameters
WHERE organization_id = cp_organization_id;
SELECT transaction_id,
tax_transaction_id
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND ( transaction_type = lv_ttype_receive --'RECEIVE'
OR
( transaction_type = lv_ttype_correct -- 'CORRECT'
AND parent_transaction_type = lv_ttype_receive )--'RECEIVE')
)
ORDER BY transaction_id;
SELECT transaction_id,
tax_transaction_id,
SIGN(quantity) quantity_multiplier
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND ( transaction_type IN (lv_ttype_deliver, lv_ttype_rtr) --('DELIVER', 'RETURN TO RECEIVING')
OR
( transaction_type = lv_ttype_correct --'CORRECT'
AND parent_transaction_type IN (lv_ttype_deliver, lv_ttype_rtr)) --('DELIVER', 'RETURN TO RECEIVING'))
)
ORDER BY transaction_id;
SELECT 1
FROM po_distributions_all pda,
rcv_shipment_lines rsl,
po_headers_all pha
where pha.po_header_id = rsl.po_header_id
and pda.po_header_id = pha.po_header_id
and rsl.shipment_line_id = cp_shipment_line_id
and rsl.shipment_header_id = cp_shipment_header_id
and pda.project_id is not null;
SELECT DISTINCT invoice_id
FROM ap_invoice_distributions_all
WHERE rcv_transaction_id = cp_transaction_id;
SELECT SUM(quantity)
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND ( transaction_type = 'RECEIVE'
OR
( transaction_type = 'CORRECT'
AND parent_transaction_type = 'RECEIVE')
);
SELECT SUM(quantity)
FROM JAI_RCV_TRANSACTIONS
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND ( transaction_type = 'RETURN TO VENDOR'
OR
( transaction_type = 'CORRECT'
AND parent_transaction_type = 'RETURN TO VENDOR')
);
ln_entered_dr := rec_tax.tax_amount*ln_apportion_factor * ln_available_qty_apportion;--Updated by Wenqiong for bug 14743050, add ln_available_qty_apportion.
ln_entered_cr := rec_tax.tax_amount*ln_apportion_factor * ln_available_qty_apportion;--Updated by Wenqiong for bug 14743050, add ln_available_qty_apportion.
UPDATE ap_invoice_distributions_all aida
SET assets_addition_flag = 'U',
assets_tracking_flag = 'Y',
charge_applicable_to_dist_id = (select invoice_distribution_id from ap_invoice_distributions_all
where line_type_lookup_code in ('ITEM', 'ACCRUAL')
and po_distribution_id = aida.po_distribution_id and invoice_id = ln_invoice_id)
/*Added by nprashar for bug # 11659328*/
WHERE invoice_id = ln_invoice_id
AND invoice_distribution_id /*distribution_line_number*/ IN /*Replaced distribution_line_number by invoice_distribution_id for bug # 11659328*/
(SELECT invoice_distribution_id /*distribution_line_number*/
FROM jai_ap_match_inv_taxes jatd,
jai_rcv_line_taxes jrtl
WHERE jrtl.tax_id = jatd.tax_id
AND jatd.invoice_id = ln_invoice_id
AND jrtl.transaction_id = r_receive_transaction.transaction_id
AND jrtl.shipment_line_id = r_shipment_lines.shipment_line_id
AND jrtl.modvat_flag = 'Y'
AND jrtl.tax_type IN (SELECT tax_type
FROM jai_regime_tax_types_v
WHERE regime_code = jai_constants.vat_regime))
AND LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS' ; /*Added line_type_lookup_code condition for bug # 11659328 */
END IF; /*Bug 11880460 modified the above select statement in sync with the code in 12.1 by abezgam*/
UPDATE JAI_RCV_LINE_TAXES
SET modvat_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE shipment_header_id = r_shipment_lines.shipment_header_id
AND shipment_line_id = r_shipment_lines.shipment_line_id
AND tax_type IN (select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime)
AND modvat_flag = 'Y';
UPDATE jai_rcv_rgm_lines
SET process_status_flag = 'U',
recoverable_amount = 0,
recovered_amount = 0
WHERE shipment_header_id = r_shipment_lines.shipment_header_id
AND shipment_line_id = r_shipment_lines.shipment_line_id;
DELETE jai_rcv_rgm_claims
WHERE shipment_header_id = r_shipment_lines.shipment_header_id
AND shipment_line_id = r_shipment_lines.shipment_line_id;
FOR rec_get_rcv_trxs IN ( SELECT transaction_id
from rcv_transactions
WHERE shipment_header_id = r_shipment_lines.shipment_header_id
AND shipment_line_id = r_shipment_lines.shipment_line_id
AND TRANSACTION_TYPE = 'RECEIVE')
LOOP
jai_pa_costing_pkg.update_interface_cost_tax( p_transaction_id => rec_get_rcv_trxs.transaction_id,
p_process_flag => lv_process_status,
p_process_message => lv_process_message);
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE (regime_id is null OR ( cp_regime_id IS NULL OR regime_id = cp_regime_id )) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND (regime_code = cp_regime_code OR regime_code is null);
SELECT b.organization_id,
b.location_id,
b.receipt_num,
a.tax_type,
(NVL(SUM(a.installment_amount),0) - NVL(SUM(a.claimed_amount),0))*-1 installment_amount
FROM jai_rcv_rgm_claims a,
jai_rcv_rgm_lines b
WHERE a.rcv_rgm_line_id = b.rcv_rgm_line_id
AND a.transaction_id = cp_transaction_id
GROUP BY b.organization_id,
b.location_id,
b.receipt_num,
a.tax_type;
SELECT NVL(max(installment_no),0) installment_no --for Bug #4279050, changed from min to max
FROM jai_rcv_rgm_claims
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND transaction_type = cp_transaction_type --'RECEIVE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND claimed_date IS NOT NULL;
SELECT (NVL(SUM(installment_amount),0) - NVL(SUM(claimed_amount),0))*-1 installment_amount
FROM jai_rcv_rgm_claims
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND transaction_id = cp_transaction_id
AND installment_no > cp_installment_no
AND tax_type = cp_tax_type;
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE (regime_id is null OR ( cp_regime_id IS NULL OR regime_id = cp_regime_id )) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND (regime_code = cp_regime_code OR regime_code is null);
SELECT MIN(claim_schedule_id) claim_schedule_id
FROM jai_rcv_rgm_claims
WHERE shipment_header_id = cp_shipment_header_id
AND shipment_line_id = cp_shipment_line_id
AND tax_type = cp_tax_type
AND transaction_id = cp_transaction_id
AND NVL(claimed_amount,0) = 0;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => r_regime.regime_id,
pv_tax_type => rec_taxes.tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => rec_taxes.organization_id,
pn_location_id => rec_taxes.location_id,
pv_source => jai_constants.source_rcv,
pv_source_trx_type => 'VAT CLAIM',
pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
pn_source_id => p_transaction_id, /* r_claim_schedule.claim_schedule_id, Vijay */
pd_transaction_date => trunc(sysdate),
pv_account_name => jai_constants.recovery,
-- pn_charge_account_id => ln_rec_ccid,
--commented the above and added the below by vkaranam for Bug#9662961
pn_charge_account_id => ln_lib_ccid,
pn_balancing_account_id => ln_int_ccid,
pn_credit_amount => ln_entered_cr,
pn_debit_amount => ln_entered_dr,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => r_claim_schedule.claim_schedule_id,
pn_batch_id => NULL,
pn_inv_organization_id => rec_taxes.organization_id,
pv_invoice_no => p_invoice_no,
pd_invoice_date => p_invoice_date,
pv_called_from => 'JAI_RGM_CLAIM_PKG.DO_RTV_ACCOUNTING',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
--Added by Bo Li for bug9305067 2010-4-14 BEGIN
--------------------------------------------------
pv_trx_reference_context => NULL,
pv_trx_reference1 => NULL,
pv_trx_reference2 => NULL,
pv_trx_reference3 => NULL,
pv_trx_reference4 => NULL,
pv_trx_reference5 => NULL
--------------------------------------------------
--Added by Bo Li for bug9305067 2010-4-14 END
);
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => r_regime.regime_id,
pv_tax_type => rec_taxes.tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => rec_taxes.organization_id,
pn_location_id => rec_taxes.location_id,
pv_source => jai_constants.source_rcv,
pv_source_trx_type => 'VAT CLAIM for RTV',
pv_source_table_name => TABLE_RCV_TRANSACTIONS, /* 'JAI_RCV_RGM_CLAIMS', Vijay */
pn_source_id => nvl(r_trx.tax_transaction_id, p_transaction_id), /* r_claim_schedule.claim_schedule_id, Vijay*/
pd_transaction_date => TRUNC(SYSDATE),
pv_account_name => jai_constants.recovery,
pn_charge_account_id => ln_rec_ccid,
pn_balancing_account_id => ln_int_ccid,
pn_credit_amount => ln_entered_cr,
pn_debit_amount => ln_entered_dr,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => r_claim_schedule.claim_schedule_id,
pn_batch_id => NULL,
pn_inv_organization_id => rec_taxes.organization_id,
pv_invoice_no => p_invoice_no,
pd_invoice_date => p_invoice_date,
pv_called_from => 'JAI_RGM_CLAIM_PKG.DO_RTV_ACCOUNTING',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
pv_trx_reference_context => NULL,
--Added by Bo Li for bug9305067 2010-4-14 BEGIN
--------------------------------------------------
pv_trx_reference1 => NULL,
pv_trx_reference2 => NULL,
pv_trx_reference3 => NULL,
pv_trx_reference4 => NULL,
pv_trx_reference5 => NULL
-------------------------------------------------
--Added by Bo Li for bug9305067 2010-4-14 END
);/*Bug 11880460 abezgam Changed the parameters from pv_attribute% to pv_tax_reference% as per precedure definition in jai_cmn_rgm_recording_pkg*/
UPDATE jai_rcv_rgm_claims
SET claimed_amount = installment_amount,
claimed_date = TRUNC(SYSDATE),
status = 'Y'
WHERE shipment_header_id = p_shipment_header_id
AND shipment_line_id = p_shipment_line_id
AND transaction_id = p_transaction_id
AND installment_no <= r_min_installment_no.installment_no
AND status = 'N';
SELECT a.tax_type,/*COL (b.mod_cr_percentage/100) added by Nitin for bug #6681800*/
SUM(ROUND(DECODE(a.currency, jai_constants.func_curr, a.tax_amount*(b.mod_cr_percentage/100), a.tax_amount*(b.mod_cr_percentage/100)*cp_currency_conversion_rate),NVL(b.rounding_factor,0))) tax_amount
FROM JAI_RCV_LINE_TAXES a,
JAI_CMN_TAXES_ALL b
WHERE a.transaction_id = cp_transaction_id
AND a.tax_type in (select tax_type
from jai_regime_tax_types_v
where regime_code = jai_constants.vat_regime)
AND NVL(a.modvat_flag,'N') = 'Y'
AND a.tax_id = b.tax_id
GROUP BY a.tax_type;
SELECT *
FROM JAI_RGM_DEFINITIONS
WHERE (regime_id is null OR ( cp_regime_id IS NULL OR regime_id = cp_regime_id )) /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
AND (regime_code = cp_regime_code OR regime_code is null);
SELECT item_id
FROM rcv_shipment_lines rsl,
rcv_transactions rt
WHERE rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_id = p_transaction_id;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => r_regime.regime_id,
pv_tax_type => rec_tax.tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => r_trx.organization_id,
pn_location_id => r_trx.location_id,
pv_source => jai_constants.source_rcv,
pv_source_trx_type => upper(lv_account_nature),
pv_source_table_name => upper(lv_reference_24),
pn_source_id => r_trx.transaction_id ,
pd_transaction_date => nvl(ld_accounting_date,trunc(sysdate)),--14061440
pv_account_name => jai_constants.Liability,
pn_charge_account_id => ln_debit_ccid,
pn_balancing_account_id => ln_credit_ccid,
pn_credit_amount => ln_entered_cr,
pn_debit_amount => ln_entered_dr,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => NULL,
pn_batch_id => NULL,
pn_inv_organization_id => r_trx.organization_id,
pv_invoice_no => r_trx.vat_invoice_no,
pd_invoice_date => r_trx.vat_invoice_date,
pv_called_from => 'JAINPORE',
pv_process_flag => lv_process_status,
pv_process_message => lv_process_message,
--Added by Bo Li for bug9305067 2010-4-14 BEGIN
-----------------------------------------------
pv_trx_reference_context => 'RMA',
pv_trx_reference1 => NULL,
pv_trx_reference2 => NULL,
pv_trx_reference3 => NULL,
pv_trx_reference4 => NULL,
pv_trx_reference5 => NULL
----------------------------------------------
--Added by Bo Li for bug9305067 2010-4-14 END
);/*Bug 11880460 Changed the parameters from pv_attribute% to pv_tax_reference% as per precedure definition in jai_cmn_rgm_recording_pkg*/