[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.
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 ROUND(--added by Nitin for bug no 6681800
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)),
NVL(b.rounding_factor, 0)
) tax_amount
/*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 = lv_process_status_flag
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*/
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 => trunc(sysdate),
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,
pv_attribute_context => NULL,
pv_attribute1 => NULL,
pv_attribute2 => NULL,
pv_attribute3 => NULL,
pv_attribute4 => NULL,
pv_attribute5 => NULL);
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;
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;
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,
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_attribute_context => NULL,
pv_attribute1 => NULL,
pv_attribute2 => NULL,
pv_attribute3 => NULL,
pv_attribute4 => NULL,
pv_attribute5 => 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_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_attribute_context => NULL,
pv_attribute1 => NULL,
pv_attribute2 => NULL,
pv_attribute3 => NULL,
pv_attribute4 => NULL,
pv_attribute5 => NULL);
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);