[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This Package is coded for Corrections Enhancement to invoke CENVAT and RG related insert APIs for PO Functionality.
- PROCESS_BATCH
Main procedure that is called from JAINRVCTP Concurrent Program. All the Concurrent Program Parameters are Optional,
in the sence that all the unprocessed and Pending Receipt Transactions will be Picked up for processing. Calls different
APIs to process the transaction and pass Localization Related Accounting, Cenvat and RG Entries
This procedure doesnot COMMIT the data if it is called from an Application
- PROCESS_TRANSACTION
Driving procedure that validates the transaction processing and if eligible, then calls different API's based on
trasaction type for processing. This should never be invoked directly as this needs data in JAI_RCV_TRANSACTIONS and which
is populated in batch procedure only.
- POPULATE_DETAILS
UPDATEs JAI_RCV_TRANSACTIONS with details(transaction,setup, validity) of the transaction that will be used
while processing the transaction
- VALIDATE_TRANSACTION
Validates applicability of Transaction for Normal and Cenvat Accounting, RG Processing. Finally after validation, updates
process_flag, cenvat_rg_flag, messages of JAI_RCV_TRANSACTIONS with relevant details. Further these values are used
to decide whether to proceed or not for Accounting and Cenvat processing
- PROCESS_ISO_TRANSACTION
RETURNs true if ISO Entries needs to be passed for transaction, else returns false. This is basically an applicability func.
- GET_ANCESTOR_ID
RETURNs the transaction_id of PARANT transaction type required for the current transaction
- GET_APPORTION_FACTOR
RETURNs the factor that should be used for multiplication with transaction Quantity and JAI_RCV_LINE_TAXES.tax amount to
get the transaction tax amount
- GET_TRXN_CENVAT_AMOUNT
RETURNs the transaction EXCISE Amount
- GET_TRXN_TAX_AMOUNT
RETURNs the transaction total TAX Amount (Excluding Modvat Recovery, TDS)
Other Procedures/Functions are coded for simplicity of the APPLICATION logic
2 26/10/2004 Vijay Shankar for Bugs#3927371,3949109,3949502 Version:115.1
Bugs#3927371 - Code modified to PROCESS only CORRECT, DELIVER and RTR transactions in PROCESS_TRANSACTIONS procedure
Code modified to return back ERROR Status only if any error (i.e process_status='E') occurs during Processing.
If process_status is 'X', then Normal Status is return back by just printing an Information Message in Log
Bugs#3949109 - added code in POPULATE_DETAILS to fetch subinventory from DELIVER transaction incase of direct delivery if the RECEIVE transaction
donot have either of location_id or subinventory attached with it
Bugs#3949502 - For a CORRECT of DELIVER transaction Subinventory is not getting populated, which is Stopping Accounting
of CORRECT transaction. this is resolved by fetching Subinventory from parent DELIVER and use it for processing
3 18/12/2004 Vijay Shankar for Bug# 4038024, 4070938, 4038044. FileVersion: 115.2
Bug#4038024, 4038044
Modified the code in populate_details to fetch Subinventory/location from parent transaction if it is not present in
the current transaction
Bug#4070938
Modified the value contained in the Package Variable NO_ITEM_CLASS to contain OTIN instead of 'XXXX'. This would mean
that, if there is no item attached to Receipt Line or if this is a non localization item, then it is treated as OTIN item
4 03/01/2005 Vijay Shankar for Bug# 3940588, Version:115.3
Following are the changes made for the purpose of RECEIPTsDEPLUG of Old Code and link it with New Corrections Code
- Added the procedures/functions
- PROCESS_DEFERRED_CENVAT_CLAIM : Will be invoked when request for JAINRVCTP is submitted from JAINMVAT form.
This is coded to process records of JAI_RCV_CENVAT_CLAIM_T table that needs to be claimed/unclaimed.
Incase of Unclaim, the transactions that are not yet processed/pending for claim or excise is not included in
cost are processed for Cenvat Costing. This happens for DELIVER, RTR and related CORRECTIONS
- GET_EQUIVALENT_QTY_OF_RECEIVE : Function that Returns quantity equivalent to RECEIVE UOM. Useful incase there
are changed between RECEIVE and other receiving transactions
- TRANSACTION_PROPROCESSOR : when Called for all RECEIVE and MATCH transactions, this does initial processing of
shipment line which will be used for all receiving transactions. Also this is place for other transactions also
where in initial processing has to happen for any transaction before going ahead for actual processing.
This inserts data into JAI_RCV_CENVAT_CLAIMS, JAI_CMN_RG_OTHERS tables for Cenvat and Cess Amounts
- TRANSACTION_POSTPROCESSOR : Does the post processing logic like updating some quantity columns at shipment
line level for DELIVER, RTR, RTV and related CORRECTs
- Increased the filtering condition for transactions processing by adding p_shipment_header_id and p_shipment_line_id
parameters to PROCESS_BATCH procedure
- Modified CURSORs c_trxns_to_populate_dtls and c_get_transactions of PROCESS_BATCH, to pickup only those transactions
where in users cannot modify the taxes anymore
- UPDATEs JAI_RCV_LINES with tax_modified_flag as 'N' so that taxes for that line cannot be modified anymore
when p_called_from is JAINPORE (Localization Receipts form)
- Calls to transaction_preprocessor and transaction_postprocessed are made to do processing required before and after
actual processing
- Changes in PROCESS_TRANSACTION procedure
- Opened up the code to execute procedure for all localization supported receiving transactions
- Modified the condition which if satisfied makes a call to jai_rcv_excise_processing_pkg.process_transaction
- Added p_process_special_reason, p_process_special_qty parameters in call to jai_rcv_excise_processing_pkg.process_transaction
- Modified POPULATE_DETAILS procedure to populate tax_transaction_id and third_party_flag values. Tax_Transaction_id is the
transaction_id related to parent transaction for which taxes are defaulted/attached. usually this is either RECEIVE or MATCH trx
- Also changes are made to update ja_in_rcv_transaction.transaction_type to RECEIVE incase of MATCH transaction
- Modifed VALIDATE_TRANSACTION to function properly. In this procedure different validations are applied that are
required for NON-CENVAT and CENVAT processing of transactions
- get_ancestor_id modified to support MATCH transaction also
- Changes required for Education CESS are done in all procedures/functions to consider CESS taxes also whereever
Excise and CVD taxes are referred
5. 09/02/2005 Vijay Shankar for Bug #4172424, Version 115.4
Issue -
RG23 D register / accounting entries are not happening
(i) if the item class is FGIN/FGEX
(ii) if the Claim Cenvat on Receipt flag on receipt is not filled in or set to NO.
Fix -
Following changes have been -
(i) Changed the cursor - c_receipt_cenvat_dtl.
Added in Input parameter - cp_organization_type.
Select for the column online_claim_flag is changed from online_claim_flag to
decode(cp_organization_type, 'M', online_claim_flag, jai_constants.yes)
(ii) While opening the cursor c_receipt_cenvat_dtl, passed the additional parameter
r_trx.organization_type
(iii) In the If condition after lv_statement_id := 27, added the condition -
and r_trx.organization_type = 'M'
6. 17/02/2005 Vijay Shankar for Bug#4171469, Version: 115.5
changes are made in process_iso_transaction Function as given below to return NOT TO PROCESS ISO if it is
a trading to trading and both the orgs have excise_in_rg23d flag as 'Y'
7 23/02/2005 Vijay Shankar for Bug#4179823, FileVersion:115.6
Modified an IF condition in validate_transaction procedure to allow FGIN items in case of RMA Receipts.
Previously it is allowing for ISO receipts only incase of FGIN items which is wrong
8 28/02/2005 Vijay Shankar for Bug#4208224,4215402 FileVersion:115.7
Bug#4208224
The concept of commit interval is giving FETCH OUT OF SEQUENCE Error as we are using FOR UPDATE OF clause
for main cursors after RECEIPTS DEPLUG. So, the commit interval concept is removed with this bugfix
Bug#4215402
Signature of the function get_accrue_on_receipt is modified to accept po_line_location_id also, because there
can be cases where a call to this procedure can pass a null value for po_distribution_id and thus returns a
wrong value to caller. this happens in case of receiving of non inventory items.
So a new parameter is added, which is used to pick the accrue_on_receipt_flag from po_line_locations_all table
if po_distribution_id is null
9 19/03/2005 Vijay Shankar for Bug#4250236(4245089). FileVersion: 115.8
.added two new parameter in validate_transaction to hold the process_vat flag and message.
.made a call to jai_rcv_rgm_claims_pkg.insert_rcv_lines in transaction preprocessor
.made a call to jai_rcv_rgm_claims_pkg.process_vat based on vat flag. this will do required processing
before vat claim happens
.added required validation in VALIDATE_TRANSACTION for vat processing and set correct values to process_vat_status
and related message
.added process_vat_status filter in main cursor c_get_transactions to fetch unprocessed VAT transactions
10 25/03/2005 Vijay Shankar for Bug#4250171. Version: 115.9
Following changes are made to make VAT Functionality work for OPM Receipts
.transaction_preprocessor is not invoked
.started updating ja_in_rcv_transaction which is not done in previous version of this object
.location_id logic execution is stopped if it is OPM Receipt as there might have been already a value
for this column in the record being processed
.process_status and cenvat_rg_flag variables are made 'X' in validate transaction if OPM RECEIPT
11 01/04/2005 Vijay Shankar for Bug#4278511. Version:115.10
Incase of ISO receipts, location_id has to be derived from SUBINVENTORY attached to the transaction if present, otherwise
we need to fetch location of RCV_TRANSACTONS. Code is modified in populate_details procedure
12 12/04/2005 Harshita for Bug#4300708. Version:116.0 (115.11)
When a new receipt gets created, it takes some time for the RTP concurrent to complete and the receipt to
get generated. Meanwhile, the customer is clicking on the 'NEW' button and proceeding with
the creation of a new receipt.
Thus accounting entries for these receipt would not be generated as the concurrent
'India - Receiving Transactions Processor' does not get fired. The India - RTP concurrent currently fires
only after the receipt gets generated in the Receipts Localized screen and the user either closes the form
or clicks on the 'NEW' button.
To overcome this issue, The concurrent 'India - Receiving Transactions Processor' has been
scheduled. The parameter of the concurrent 'P_CALLED_FROM' has been made visible and
defaulted to 'JAINPORE'. The concurrent has been updated to account all the receipts at
the organization level when it is called from JAINPORE and the shipemnt_header_id is null.
13 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
Code is modified due to the Impact of Receiving Transactions DFF Elimination
Code added to implement the functionality of Tax Invoice Generation by grouping the RTV's based
on Orgn, Loc, Vendor and site. New procedure process_rtv is written for this functionality and
linked to JAITIGRTV concurrent
* High Dependancy for future Versions of this object *
14 19/05/2005 rallamse for Bug#4336482, Version 116.1
For SEED there is a change in concurrent "JAINRVCTP" to use FND_STANDARD_DATE with STANDARD_DATE format
Procedure ja_in_rg_rounding_pkg.do_rounding signature modified by converting p_transaction_from, p_transaction_to
of DATE datatype to pv_transaction_from, pv_transaction_to of varchar2 datatype.
The varchar2 values are converted to DATE fromat using fnd_date.canonical_to_date function.
15 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
16. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.4
Removal of SQL LITERALs is done
17. 17-Jul-2005 Sanjikum for Bug#4495135, File Version 117.1
Following changes are done in procedure - populate_details
1) Added the variable - ln_tax_apportion_factor and populated the same using - get_apportion_factor(p_transaction_id)
2) Added the call to jai_rcv_transactions_pkg.update_row to update jai_rcv_transactions.tax_apportion_factor
18. 27/07/2005 Ramananda for Bug#4516577, Version 120.2
Problem
-------
ISO Accounting Entries from Trading to Excise bonded inventory are not generated in case of following Scenarios
1. Trading organization to Trading Organization (only Source organizations with the 'Excise in RG23D' setup).
2. Trading organization to Manufacturing Organization (Source Organization with the 'Excise in RG23D' setup).
Fix
---
In the function - process_iso_transaction, made the following changes
1. In the If condition -
" IF r_src_org.excise_in_rg23d <> 'Y' OR r_dest_org.excise_in_rg23d <> 'Y' THEN"
removed the second part of the OR "r_dest_org.excise_in_rg23d <> 'Y'"
2. In the If condition - "ELSIF r_dest_org.manufacturing = 'Y' THEN"
Added the If condition - "IF r_src_org.excise_in_rg23d <> 'Y' THEN"
for the statement - lb_process_iso_transaction := false;
Issue : A call to jai_rcv_rgm_claims_pkg.insert_rcv_lines is made even though there
do not exist any VAT type of taxes in the receipt.
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
Dependency due to this bug:-
jai_rcv_rgm_clm.plb (120.2)
18. 01/09/2005 Bug4586752. Added by Lakshmi gopalsami Version 120.5
Assigned the value of location_id in populate_details
and used for populating jai_rcv_transactions
Dependency (Functional)
----------------------
JAIRGMCT.fmb 120.2
JAIRGMPT.fmb 120.3
JAIRGMSG.fmb 120.2
jai_rcv_trx_prc.plb 120.5
19. 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
20. 26-May-2006 Sanjikum for Bug#4929410, File Version 120.7
1) Changes done related to performance
21. 17-Jul-2006 Aiyer for the bug 5378630 , File Version 120.6
Issue:-
India Receiving transaction processor fails during validation phase for RMA
type of transactions.
Fix:-
Converting the reference of RMA TYPE "FG RETURN" into "GOODS RETURN" as FG return is not as per the abbreviation
standard
22. 30-OCT-2006 SACSETHI for bug 5228046, File version 120.2
Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
This bug has datamodel and spec changes.
23. 13-FEB-2007 Vkaranam for bug 4636397,File Version 120.14
Forward porting the change in 11i bug 4626571 (India Localization- Unordered Receitps-50% Gets Hit Without Claiming Modvat).
Changes are done in get_ancestor_id function.
24. 16-Feb-2007 srjayara for bug 5064235 -- forward porting for bug# 5054114 in 11i
File version 120.15
Issue : The subinventory's location_id is not getting populated in ja_in_rcv_transactions for
'RECEIVE' line
Fix : The subinventory's location_id will be populated in ja_in_rcv_transactions for 'RECEIVE' line
by fetching the subinventory from rcv_transactions 'DELIVER' line.
25. 21-Feb-2007 CSahoo for BUG 5344225, File Version 120.16
Forward porting of 11i BUG 5343848
Added two input parameters p_request_id,p_group_id to the procedure process_batch.
Added a parameter request_id Default null
If the request_id is not null
Called the fnd_concurrent.wait_for_request_id .
This call will wait till the RVCTP concurrent is completed.
Added a parameter group_id Default null
If the group_id is not null
Run a infinite loop till the data in rcv_transactions_interface table
is purged for the particular group_id.
The rest of the code in the concurrent is processed only after
the data is purged.
16/04/2007 Kunkumar for bugno 5989740
forward porting to R12 filename:ja_in_receipt_transactions_pkg.sql version 115.42.6107.2
26. 10/05/2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version :120.18
Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
Fix : Changes are made to hit the Qty register independent of the Amount register.
This would happen in following cases:
i) In case of deferred claim, the Qty register would be hit at RECEIVE or MATCH.
Previously it was at CLAIM only. Decision to hit the Qty register at RECEIVE
or CLAIM would be made depending on Setup.
ii) For an excisable item, if there are no taxes attached then the Qty register
would be hit. Previously the Qty register was hit at the time of CLAIM and
CLAIM can be done only if there are taxes.
A spec variable lv_online_qty_flag is set depending on the above conditions.
The changes are made in validate_transaction procedure for this.
Changes are made in process_transaction to hit the Qty register if lv_online_qty_flag
is set to Y.
The changes are made on top of 115.34 as 115.35 is obsolete.
Dependency Due to this Bug : Yes.
27. 10/05/2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version :120.18
Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
Fix : The variable which has the count of excise taxes was used before
the cursor was used to fetch the value and so the count is always coming as zero.
Now moved the cursor position.
28. 10/05/2007 bgowrava for forward porting Bug#5756676, 11i bug#5747013. File Version :120.18
Issue : QTY REGISTER SHOULD BE UPDATED ON RECEIVE DEPENDING ON SETUP
Fix : If the organization , location combination does not have any setup for
"Update Qty Register Event" then we should get the setup value from
NULL site. To do this we were checking if cur_qty_setup%NOTFOUND for location id.
This would never be true as the cursor would fetch a record. Now modified this
to lv_qty_upd_event IS NULL. If this is NULL then we will fetch it from NULL site.
29. 14-05-2007 ssawant for bug 5879769, File Version 120.19
Objects was not compiling. so changes are done to make it compiling.
30. 04/06/2007 sacsethi for bug 6109941 File Version 120.20
CODE REVIEW COMMENTS FOR ENHANCEMENTS
Problem- Code related to cenvat amount was wrongly commented
31. 21/06/2007 rchandan for bug#6109941, File Version 120.21
Issue: Code review for enhancements(ER bug#5747013)
Fix: removed the decalaration of lv_online_qty_flag as it is already decalred in the spec and
added a nvl check in an if condition.
32. 01-08-2007 rchandan for bug#6030615 , Version 120.23
Issue : Inter org Forward porting
33. 05-JAN-2009 Bug 7662347 File version 120.11.12000000.4 / 120.25.12010000.2 / 120.26
Issue : RG23 Part I register is not hit during RTV when there are no excise taxes.
Fix : Changed the code so that lv_online_qty_flag will be Y for the RTV transactions
also, for receipts which do not have excise taxes. Also added a variable
lv_qty_register_entry_type in the process_transaction procedure so that the
quantity register will be hit with proper sign.
Dependancy:
-----------
----------------------------------------------------------------------------------------------------------------------------*/
/* following procedure added by Vijay Shankar for Bug#3940588 */
PROCEDURE transaction_preprocessor(
p_shipment_line_id IN NUMBER,
p_transaction_id IN NUMBER,
p_process_status IN OUT NOCOPY VARCHAR2,
p_process_message IN OUT NOCOPY VARCHAR2,
p_simulate_flag IN VARCHAR2 --File.Sql.35 Cbabu DEFAULT 'N'
) IS
--added by ssawant
CURSOR cur_qty_setup( cp_organization_id NUMBER,cp_location_id NUMBER)
IS
SELECT quantity_register_update_event
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id ;
SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate,
attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
, routing_header_id -- porting of Bug#3949109 (3927371)
, attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
, po_line_location_id
FROM rcv_transactions
WHERE transaction_id = cp_transaction_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 ;
select count(1) into ln_dup_chk
from JAI_RCV_CENVAT_CLAIMS
where transaction_id = r_trx.transaction_id;
INSERT INTO JAI_RCV_CENVAT_CLAIMS(
transaction_id, shipment_line_id, cenvat_amount, cenvat_claimed_ptg, cenvat_sequence,
other_cenvat_amt, other_cenvat_claimed_amt, creation_date, created_by, last_update_date,
last_updated_by, last_update_login,
online_claim_flag,
vendor_changed_flag
) VALUES (
r_trx.transaction_id, r_trx.shipment_line_id, ln_cenvat_amount, 0, 0,
ln_other_cenvat_amt, 0, sysdate, fnd_global.user_id, sysdate,
fnd_global.user_id, fnd_global.login_id,
r_excise_invoice_no.online_claim_flag, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. nvl(r_base_trx.online_claim_flag, jai_constants.no),
jai_constants.no
);
jai_rcv_rgm_claims_pkg.insert_rcv_lines(
p_shipment_header_id => null,
p_shipment_line_id => p_shipment_line_id,
p_transaction_id => p_transaction_id,
p_regime_code => jai_constants.vat_regime
, p_process_status => p_process_status,
p_process_message => p_process_message,
p_simulate_flag => p_simulate_flag
);
JA_IN_RCV_CENVAT_PKG.insert_row(
p_shipment_line_id => p_shipment_line_id ,
p_tax_transaction_id => r_trx.tax_transaction_id,
p_tax_qty => r_trx.quantity,
p_tax_qty_uom_code => r_trx.uom_code,
p_receipt_num => r_trx.receipt_num,
p_receipt_date => r_trx.transaction_date,
p_excise_invoice_no => r_trx.excise_invoice_no,
p_excise_invoice_date => r_trx.excise_invoice_date,
p_basic_excise => ln_basic,
p_addl_excise => ln_addl,
p_cvd => ln_cvd,
p_other_excise => ln_other,
p_cenvat_claimed_ptg => 0,
p_cenvat_claimed_amt => 0,
p_claimable_flag => null,
p_receive_qty => 0,
p_receive_corr_qty => null,
p_deliver_bonded_qty => null,
p_deliver_nonbonded_qty => null,
p_deliver_corr_bonded_qty => null,
p_deliver_corr_nonbonded_qty => null,
p_rtr_bonded_qty => null,
p_rtr_nonbonded_qty => null,
p_rtv_qty => null,
p_rtv_corr_qty => null,
p_excise_vendor_id => null,
p_excise_vendor_site_id => null,
p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_status
);
UPDATE JAI_RCV_CENVAT_CLAIMS
SET cenvat_amount = nvl(ln_cenvat_amount, cenvat_amount),
other_cenvat_amt = nvl(ln_cenvat_amount, other_cenvat_amt),
cenvat_claimed_ptg = nvl(ln_cenvat_claimed_ptg, 100),
cenvat_sequence = nvl(cenvat_sequence ,0) + 1, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE transaction_id = r_trx.tax_transaction_id;
UPDATE JAI_RCV_CENVAT_CLAIMS
SET non_bonded_delivery_flag = 'Y',
cenvat_claimed_ptg = 100
--last_update_date = sysdate,
--last_updated_by = fnd_global.user_id
WHERE transaction_id = p_transaction_id;
JA_IN_RCV_CENVAT_PKG.update_quantities(
p_shipment_line_id => p_shipment_line_id,
p_tax_transaction_id => r_trx.tax_transaction_id,
p_transaction_type => r_trx.transaction_type,
p_parent_transaction_type => r_trx.parent_transaction_type,
p_subinventory_type => r_trx.loc_subinventory_type,
p_transaction_quantity => r_trx.quantity,
p_transaction_uom_code => r_trx.uom_code,
p_called_from => 'jai_rcv_trx_processing_pkg.transaction_preprocessor',
p_simulate_flag => p_simulate_flag,
p_process_status => p_process_status,
p_process_message => p_process_message
);
SELECT jpol.line_id
FROM jai_po_osp_lines jpol,
jai_po_osp_hdrs jpoh
WHERE jpol.form_id = jpoh.form_id
AND (jpoh.po_header_id, jpol.po_line_id ) IN
( SELECT po_header_id, po_line_id
FROM rcv_transactions
WHERE transaction_id = p_transaction_id
);
select transaction_id, shipment_line_id, organization_type
from JAI_RCV_TRANSACTIONS
where (transaction_type IN (lv_ttype_deliver, lv_type_rtr) --'DELIVER', 'RETURN TO RECEIVING')
or (transaction_type= lv_ttype_correct and parent_transaction_type = lv_ttype_deliver) --'CORRECT' , 'DELIVER'
)
and tax_transaction_id = cp_tax_transaction_id
and shipment_line_id = cp_shipment_line_id
-- and cenvat_rg_flag <> ('P','X') -- pending for parent receive claim
-- and cenvat_rg_flag IN ('N', 'X', 'P', 'XT')
and attribute1 = jai_rcv_deliver_rtr_pkg.cenvat_costed_flag
and (attribute2 IS NULL or attribute2 <> jai_constants.yes)
FOR UPDATE OF cenvat_rg_status, cenvat_rg_message
order by shipment_line_id, transaction_id;
SELECT * FROM JAI_RCV_CENVAT_CLAIM_T
WHERE batch_identifier = p_batch_id
AND error_flag IS NULL
FOR UPDATE OF transaction_id
ORDER BY transaction_id;
SELECT cenvat_claimed_ptg, quantity_for_2nd_claim
FROM JAI_RCV_CENVAT_CLAIMS
WHERE transaction_id = cp_transaction_id;
UPDATE JAI_RCV_TRANSACTIONS
SET CENVAT_RG_STATUS = 'X',
cenvat_rg_message = 'Cenvat Unclaimed'
WHERE CURRENT OF c_trxs_for_unclaim;
UPDATE JAI_RCV_TRANSACTIONS
SET CENVAT_RG_STATUS = 'X',
cenvat_rg_message = 'Cenvat Unclaimed'
WHERE transaction_id = temp_rec.transaction_id
-- following is to take care of Pending DELIVER and RTR and related CORRECTS
-- as the parent receive is not yet claim so need of passing CENVAT RG entries.
-- Simply update the flag as the transactions is already costed during delivery processing
OR ( shipment_line_id = r_trx.shipment_line_id
AND tax_transaction_id = r_trx.tax_transaction_id
AND (transaction_type IN ('DELIVER', 'RETURN TO RECEIVING')
or (transaction_type='CORRECT' and parent_transaction_type = 'DELIVER')
)
AND loc_subinv_type = 'N' -- non bonded
AND CENVAT_RG_STATUS = 'P' -- waiting for receipt line claim
);
UPDATE JAI_RCV_CENVAT_CLAIMS
SET cenvat_amount = 0,
other_cenvat_amt = 0,
cenvat_sequence = cenvat_sequence + 1,
unclaim_cenvat_flag ='Y',
unclaim_cenvat_date = trunc(sysdate),
unclaimed_cenvat_amount = temp_rec.unclaimed_cenvat_amount,
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE transaction_id = temp_rec.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_line_id = temp_rec.shipment_line_id
AND upper(tax_type) IN ('EXCISE',
'ADDL. EXCISE',
'OTHER EXCISE',
'CVD',
jai_constants.tax_type_add_cvd,
-- Modified by SACSETHI Bug# 5228046
-- Forward porting the change in 11i bug 5365523
-- (Additional CVD Enhancement) as part of the R12 bug 5228046
jai_constants.tax_type_exc_edu_cess,
jai_constants.tax_type_cvd_edu_cess, jai_constants.tax_type_sh_exc_edu_cess,
jai_constants.tax_type_sh_cvd_edu_cess) -- By kunkumar for bug 5989740
AND modvat_flag ='Y';
UPDATE JAI_RCV_CENVAT_CLAIM_T
SET error_flag = 'Y',
error_description = substr(lv_cenvat_rg_message,1,150),
process_date = sysdate
WHERE CURRENT OF c_trxs_to_be_claimed;
DELETE FROM JAI_RCV_CENVAT_CLAIM_T
WHERE CURRENT OF c_trxs_to_be_claimed;
SELECT a.sid, a.serial#, b.spid
FROM v$session a, v$process b
WHERE audsid = ln_audsid
AND a.paddr = b.addr;
SELECT name FROM v$database;
select 1
from rcv_transactions_interface
where group_id = cp_group_id
and rownum=1 ;
select 1
from rcv_transactions_interface
where
group_id = cp_group_id and
(transaction_status_code = 'ERROR' or processing_status_code = 'ERROR') ;
SELECT rowid, transaction_id, shipment_line_id, process_status, cenvat_rg_status,
transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg
, attribute_category -- Vijay Shankar for Bug#4250171
FROM JAI_RCV_TRANSACTIONS a
WHERE (p_organization_id IS NULL OR organization_id = p_organization_id)
AND a.receipt_num IS NULL
AND (p_shipment_header_id IS NULL OR a.shipment_header_id = p_shipment_header_id)
AND
( ( p_called_from = 'JAINPORE' and p_shipment_header_id is null) -- added, Harshita for bug #4300708
OR ( exists (select 1 from JAI_RCV_LINES b
where a.shipment_line_id = b.shipment_line_id
and tax_modified_flag='N') )
)
FOR UPDATE OF transaction_id
ORDER BY receipt_num, transaction_id;
SELECT rowid, transaction_id, process_status, cenvat_rg_status, process_message, cenvat_rg_message,
transaction_type, parent_transaction_type, receipt_num, cenvat_claimed_ptg,
shipment_line_id -- Vijay Shankar for Bug#3940588
FROM JAI_RCV_TRANSACTIONS a
WHERE ( p_simulate_flag = 'Y'
OR
( process_status IS NULL OR process_status IN ('N', 'E','P') OR cenvat_rg_status IN ('N', 'E','P')
-- following condition added by Vijay Shankar for Bug#4250236(4245089). VAT Implementation
OR -- process_vat_status IN ('N', 'E', 'P') this condition is modified as below for DFF elimination. Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
-- Vijay Shankar for Bug#3940588 RECEIPTS DEPLUG
-- This is no more required because 2nd 50% Claim is handled seperately through deferred Claim procedure
-- However process_transaction still uses cenvat_claimed_ptg flag to process 2nd 50% Claim
-- OR cenvat_claimed_ptg = 50
)
)
AND receipt_num IS NOT NULL -- a Check to see whether populate details is done for this trx or not
AND (p_transaction_id IS NULL OR transaction_id = p_transaction_id)
AND (p_organization_id IS NULL OR organization_id = p_organization_id)
AND (p_shipment_header_id IS NULL OR shipment_header_id = p_shipment_header_id) -- Vijay Shankar for Bug#3940588
AND (p_shipment_line_id IS NULL OR shipment_line_id = p_shipment_line_id) -- Vijay Shankar for Bug#3940588
-- followingcondition is not required
--AND (p_receipt_num IS NULL OR receipt_num = p_receipt_num)
AND (p_transaction_type IS NULL OR transaction_type = p_transaction_type)
AND (p_parent_trx_type IS NULL OR parent_transaction_type = p_parent_trx_type) -- Vijay Shankar for Bug#3940588
AND (
(p_transaction_from IS NULL AND p_transaction_to IS NULL)
OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
)
-- Check to pickup only those lines in which taxes cannot be modified,
and
( ( p_shipment_header_id is null and p_called_from = 'JAINPORE' ) OR -- added, Harshita for bug #4300708
exists (select 1 from JAI_RCV_LINES b
where b.shipment_line_id = a.shipment_line_id
and b.tax_modified_flag='N')
)
-- 3927371 (generic finding) this should process only these transactions if only receipt number is specified.
-- This should be removed when old code is obsoleted with new code
-- AND transaction_type IN ('CORRECT', 'DELIVER', 'RETURN TO RECEIVING'); -- commented for Vijay Shankar for Bug#3940588
FOR UPDATE OF transaction_id -- added by Vijay Shankar for Bug#3940588
ORDER BY transaction_id; -- added by Vijay Shankar for Bug#3940588 */
SELECT rowid
FROM jai_rcv_transactions
WHERE transaction_id IN
( SELECT transaction_id
FROM jai_rtp_populate_t)
FOR UPDATE OF transaction_id ;
INSERT
INTO JAI_RTP_POPULATE_T
(TRANSACTION_ID,
SHIPMENT_LINE_ID,
PROCESS_FLAG,
CENVAT_RG_FLAG,
TRANSACTION_TYPE,
PARENT_TRANSACTION_TYPE,
RECEIPT_NUM,
CENVAT_CLAIMED_PTG,
ATTRIBUTE_CATEGORY)
(
SELECT transaction_id,
shipment_line_id,
process_status,
cenvat_rg_status,
transaction_type,
parent_transaction_type,
receipt_num,
cenvat_claimed_ptg,
attribute_category
FROM jai_rcv_transactions a
WHERE organization_id = p_organization_id
AND a.receipt_num IS NULL
AND p_called_from = 'JAINPORE');
INSERT
INTO JAI_RTP_POPULATE_T
(TRANSACTION_ID,
SHIPMENT_LINE_ID,
PROCESS_FLAG,
CENVAT_RG_FLAG,
TRANSACTION_TYPE,
PARENT_TRANSACTION_TYPE,
RECEIPT_NUM,
CENVAT_CLAIMED_PTG,
ATTRIBUTE_CATEGORY)
(
SELECT transaction_id,
shipment_line_id,
process_status,
cenvat_rg_status,
transaction_type,
parent_transaction_type,
receipt_num,
cenvat_claimed_ptg,
attribute_category
FROM jai_rcv_transactions a
WHERE organization_id = p_organization_id
AND a.shipment_header_id = p_shipment_header_id
AND a.receipt_num IS NULL
AND EXISTS (SELECT 1
FROM jai_rcv_lines b
WHERE a.shipment_line_id = b.shipment_line_id
AND tax_modified_flag='N')
);
SELECT rowid
FROM jai_rcv_transactions
WHERE transaction_id IN
( SELECT transaction_id
FROM jai_rtp_trans_t )
FOR UPDATE OF transaction_id ;
INSERT
INTO JAI_RTP_TRANS_T
(TRANSACTION_ID,
PROCESS_FLAG,
CENVAT_RG_FLAG,
PROCESS_MESSAGE,
CENVAT_RG_MESSAGE,
TRANSACTION_TYPE,
PARENT_TRANSACTION_TYPE,
RECEIPT_NUM,
CENVAT_CLAIMED_PTG,
SHIPMENT_LINE_ID)
(
SELECT transaction_id,
process_status,
cenvat_rg_status,
process_message,
cenvat_rg_message,
transaction_type,
parent_transaction_type,
receipt_num,
cenvat_claimed_ptg,
shipment_line_id
FROM jai_rcv_transactions a
WHERE organization_id = p_organization_id
AND transaction_type = p_transaction_type
AND parent_transaction_type = p_parent_trx_type
AND receipt_num IS NOT NULL
AND ( p_simulate_flag = 'Y'
OR
( process_status IS NULL
OR process_status IN ('N', 'E','P')
OR cenvat_rg_status IN ('N', 'E','P')
OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
)
)
AND (
( p_transaction_from IS NULL AND p_transaction_to IS NULL)
OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
)
) ;
INSERT
INTO JAI_RTP_TRANS_T
(TRANSACTION_ID,
PROCESS_FLAG,
CENVAT_RG_FLAG,
PROCESS_MESSAGE,
CENVAT_RG_MESSAGE,
TRANSACTION_TYPE,
PARENT_TRANSACTION_TYPE,
RECEIPT_NUM,
CENVAT_CLAIMED_PTG,
SHIPMENT_LINE_ID)
(
SELECT transaction_id,
process_status,
cenvat_rg_status,
process_message,
cenvat_rg_message,
transaction_type,
parent_transaction_type,
receipt_num,
cenvat_claimed_ptg,
shipment_line_id
FROM jai_rcv_transactions a
WHERE organization_id = p_organization_id
AND transaction_type = p_transaction_type
AND receipt_num IS NOT NULL
AND ( p_simulate_flag = 'Y'
OR
( process_status IS NULL
OR process_status IN ('N', 'E','P')
OR cenvat_rg_status IN ('N', 'E','P')
OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
)
)
AND (
( p_transaction_from IS NULL AND p_transaction_to IS NULL)
OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
)
) ;
INSERT
INTO JAI_RTP_TRANS_T
(TRANSACTION_ID,
PROCESS_FLAG,
CENVAT_RG_FLAG,
PROCESS_MESSAGE,
CENVAT_RG_MESSAGE,
TRANSACTION_TYPE,
PARENT_TRANSACTION_TYPE,
RECEIPT_NUM,
CENVAT_CLAIMED_PTG,
SHIPMENT_LINE_ID)
(
SELECT transaction_id,
process_status,
cenvat_rg_status,
process_message,
cenvat_rg_message,
transaction_type,
parent_transaction_type,
receipt_num,
cenvat_claimed_ptg,
shipment_line_id
FROM jai_rcv_transactions a
WHERE organization_id = p_organization_id
AND receipt_num IS NOT NULL
AND ( p_simulate_flag = 'Y'
OR
( process_status IS NULL
OR process_status IN ('N', 'E','P')
OR cenvat_rg_status IN ('N', 'E','P')
OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
)
)
AND (
( p_transaction_from IS NULL AND p_transaction_to IS NULL)
OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
)
) ;
INSERT
INTO JAI_RTP_TRANS_T
(TRANSACTION_ID,
PROCESS_FLAG,
CENVAT_RG_FLAG,
PROCESS_MESSAGE,
CENVAT_RG_MESSAGE,
TRANSACTION_TYPE,
PARENT_TRANSACTION_TYPE,
RECEIPT_NUM,
CENVAT_CLAIMED_PTG,
SHIPMENT_LINE_ID)
(
SELECT transaction_id,
process_status,
cenvat_rg_status,
process_message,
cenvat_rg_message,
transaction_type,
parent_transaction_type,
receipt_num,
cenvat_claimed_ptg,
shipment_line_id
FROM jai_rcv_transactions a
WHERE organization_id = p_organization_id
AND shipment_header_id = p_shipment_header_id AND
( p_simulate_flag = 'Y'
OR
( process_status IS NULL
OR process_status IN ('N', 'E','P')
OR cenvat_rg_status IN ('N', 'E','P')
OR process_vat_status IN ('N', 'E', 'P', jai_constants.unexpected_error, jai_constants.expected_error)
)
)
AND receipt_num IS NOT NULL
AND (p_transaction_id IS NULL OR transaction_id = p_transaction_id)
AND (p_shipment_line_id IS NULL OR shipment_line_id = p_shipment_line_id)
AND (p_transaction_type IS NULL OR transaction_type = p_transaction_type)
AND (p_parent_trx_type IS NULL OR parent_transaction_type = p_parent_trx_type)
AND (
(p_transaction_from IS NULL AND p_transaction_to IS NULL)
OR (p_transaction_from IS NULL AND trunc(creation_date) <= p_transaction_to)
OR (p_transaction_to IS NULL AND trunc(creation_date) >= p_transaction_from)
OR (trunc(creation_date) BETWEEN p_transaction_from AND p_transaction_to)
)
AND EXISTS (SELECT 1
FROM jai_rcv_lines b
WHERE b.shipment_line_id = a.shipment_line_id
AND b.tax_modified_flag='N')
);
UPDATE JAI_RCV_LINES a
SET tax_modified_flag = 'N',
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
WHERE shipment_header_id = p_shipment_header_id
AND tax_modified_flag IN ('Y', 'X')
-- This condition will take not to update tax modified flag incase localization table is not populated
-- eg.Incase of Unordered Receipt, Until MATCH happens we should not update the tax_modified_flag to 'Y'
AND exists (select 1 from JAI_RCV_TRANSACTIONS
where shipment_line_id = a.shipment_line_id);
FOR trx IN (SELECT * FROM JAI_RTP_POPULATE_T ORDER BY receipt_num, transaction_id) LOOP
-- SAVEPOINT start_trx_population;
FOR trx IN (SELECT * FROM JAI_RTP_TRANS_T ORDER BY transaction_id) LOOP
BEGIN
lv_common_err_mesg := null;
update JAI_RCV_LINES
set tax_modified_flag = 'N'
where receipt_num = trx.receipt_num ;
UPDATE JAI_RCV_TRANSACTIONS
SET codepath = lv_codepath
--WHERE rowid = trx.row_id
--commented the above and added the below by sanjikum for Bug#4929410
WHERE transaction_id = trx.transaction_id;
PROCEDURE insert_rtv_batch_group(
pn_batch_group_id OUT NOCOPY NUMBER,
pn_batch_num IN NUMBER,
pv_regime_code IN VARCHAR2,
pn_organization_id IN NUMBER,
pn_location_id IN NUMBER,
pn_vendor_id IN NUMBER,
pn_vendor_site_id IN NUMBER,
pv_invoice_no IN VARCHAR2,
pd_invoice_date IN DATE,
pv_process_status IN VARCHAR2,
pv_process_message IN VARCHAR2
) IS
ln_user_id NUMBER;
INSERT INTO jai_rcv_rtv_batch_grps(
batch_group_id,
batch_num, regime_code, organization_id, location_id,
vendor_id, vendor_site_id, invoice_no, invoice_date,
creation_date, created_by, last_update_date, last_updated_by, last_update_login
) VALUES (
jai_rcv_rtv_batch_grps_s.nextval,
pn_batch_num, pv_regime_code, pn_organization_id, pn_location_id,
pn_vendor_id, pn_vendor_site_id, pv_invoice_no, pd_invoice_date,
sysdate, ln_user_id, sysdate, ln_user_id, ln_login_id
) RETURNING batch_group_id INTO pn_batch_group_id;
END insert_rtv_batch_group;
select a.receipt_num, a.transaction_id, a.shipment_line_id, a.shipment_header_id,
a.organization_id, a.location_id, b.vendor_id, b.vendor_site_id, b.excise_batch_num, b.vat_batch_num,
a.excise_invoice_no, a.vat_invoice_no, a.excise_invoice_date, a.vat_invoice_date
, a.parent_transaction_id,
a.PROCESS_STATUS, a.process_message, a.CENVAT_RG_STATUS, a.cenvat_rg_message,
a.PROCESS_VAT_STATUS, a.process_vat_message
, a.attribute1 excise_invoice_action, a.attribute2 vat_invoice_action,
nvl(b.receipt_excise_rate,0) receipt_excise_rate,
nvl(b.rtv_excise_rate, nvl(b.receipt_excise_rate, 0)) rtv_excise_rate,
decode( b.excise_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_excise_in_batch,
decode( b.vat_batch_num, cpn_batch_num, jai_constants.yes, jai_constants.no) process_vat_in_batch
from JAI_RCV_TRANSACTIONS a, jai_rcv_rtv_batch_trxs b
where a.transaction_id = b.transaction_id
and (pn_min_transaction_id is null or a.transaction_id >= pn_min_transaction_id)
and (pn_max_transaction_id is null or a.transaction_id <= pn_max_transaction_id)
and
( ( b.excise_batch_num = cpn_batch_num and a.excise_invoice_no is null)
or ( b.vat_batch_num = cpn_batch_num and a.vat_invoice_no is null)
)
order by a.organization_id, a.location_id, b.vendor_id, b.vendor_site_id, a.transaction_id
for update of a.excise_invoice_no, a.excise_invoice_date, a.vat_invoice_no, a.vat_invoice_date;
select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = cpv_regime_code;
update JAI_RCV_TRANSACTIONS a
set excise_invoice_no = lv_excise_invoice_no,
excise_invoice_date = ld_excise_invoice_date,
--attribute3 = pn_batch_num,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where current of c_rtv_trxs;
INSERT INTO JAI_RCV_RTV_DTLS(
transaction_id, parent_transaction_id, shipment_line_id,
excise_invoice_no, excise_invoice_date, rg_register_part_i,
creation_date, created_by, last_update_date, last_updated_by, last_update_login
) VALUES (
rtv_rec.transaction_id, rtv_rec.parent_transaction_id, rtv_rec.shipment_line_id,
lv_excise_invoice_no, ld_excise_invoice_date, NULL,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id
);
update JAI_RCV_TRANSACTIONS a
set vat_invoice_no = lv_vat_invoice_no,
vat_invoice_date = ld_vat_invoice_date,
--attribute4 = pn_batch_num,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where current of c_rtv_trxs;
insert_rtv_batch_group(
pn_batch_group_id => ln_excise_batch_group_id,
pn_batch_num => pn_batch_num,
pv_regime_code => jai_constants.excise_regime,
pn_organization_id => ln_organization_id,
pn_location_id => ln_location_id,
pn_vendor_id => ln_vendor_id,
pn_vendor_site_id => ln_vendor_site_id,
pv_invoice_no => lv_excise_invoice_no,
pd_invoice_date => ld_excise_invoice_date,
pv_process_status => lv_process_flag,
pv_process_message => lv_process_message
);
RAISE_APPLICATION_ERROR( 20015, 'Error in Excise insert_rtv_batch_group. MSG:'||lv_process_message);
insert_rtv_batch_group(
pn_batch_group_id => ln_vat_batch_group_id,
pn_batch_num => pn_batch_num,
pv_regime_code => jai_constants.vat_regime,
pn_organization_id => ln_organization_id,
pn_location_id => ln_location_id,
pn_vendor_id => ln_vendor_id,
pn_vendor_site_id => ln_vendor_site_id,
pv_invoice_no => lv_vat_invoice_no,
pd_invoice_date => ld_vat_invoice_date,
pv_process_status => lv_process_flag,
pv_process_message => lv_process_message
);
RAISE_APPLICATION_ERROR( 20015, 'Error in Excise insert_rtv_batch_group. MSG:'||lv_process_message);
update jai_rcv_rtv_batch_trxs
set excise_batch_group_id = decode(rtv_rec.process_excise_in_batch, jai_constants.yes, ln_excise_batch_group_id),
vat_batch_group_id = decode(rtv_rec.process_vat_in_batch, jai_constants.yes, ln_vat_batch_group_id)
where transaction_id = rtv_rec.transaction_id;
jai_rcv_transactions_pkg.update_attributes(
p_transaction_id => rtv_rec.transaction_id,
p_attribute1 => lv_excise_inv_gen_action,
p_attribute2 => lv_vat_inv_gen_action
);
UPDATE JAI_RCV_TRANSACTIONS
SET codepath = lv_codepath
WHERE current of c_rtv_trxs;
SELECT shipment_header_id, shipment_line_id, transaction_type, quantity, unit_of_measure, uom_code,
parent_transaction_id, organization_id, location_id, subinventory, currency_conversion_rate,
attribute_category attr_cat, nvl(attribute5, 'XX') rma_type, nvl(attribute4, 'N') generate_excise_invoice
, routing_header_id -- porting of Bug#3949109 (3927371)
, attribute3 online_claim_flag, source_document_code, po_header_id -- Vijay Shankar for Bug#3940588
, po_line_location_id
FROM rcv_transactions
WHERE transaction_id = cp_transaction_id;
SELECT to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||'-jirt_pkg.process_transaction-'||p_transaction_id INTO lv_temp FROM DUAL;
jai_rcv_transactions_pkg.update_process_flags(
p_transaction_id => p_transaction_id,
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_cenvat_rg_flag => p_cenvat_rg_flag,
p_cenvat_claimed_ptg => ln_cenvat_claimed_ptg,
p_cenvat_rg_message => p_cenvat_rg_message,
p_process_vat_flag => lv_process_vat_flag,
p_process_vat_message => lv_process_vat_message,
/*Vijay Shankar for Bug#4250171 p_process_vat_flag => null,
p_process_vat_message => null,
*/p_process_date => SYSDATE
);
SELECT shipment_line_id, item_id
FROM rcv_shipment_lines
WHERE shipment_line_id = cp_shipment_line_id;
SELECT shipment_header_id, receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id = cp_shipment_header_id;
SELECT item_class, modvat_flag, excise_flag, item_trading_flag
FROM JAI_INV_ITM_SETUPS
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_inventory_item_id;
SELECT inventory_item_flag, inventory_asset_flag
FROM mtl_system_items
WHERE organization_id = cp_organization_id
AND inventory_item_id = cp_inventory_item_id;
SELECT decode( manufacturing, 'Y', 'M', decode(trading, 'Y', 'T', 'X')) organization_type, excise_in_rg23d
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND location_id = 0;
SELECT primary_cost_method
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT nvl(inventory_organization_id, -99999) inventory_organization_id
FROM hr_locations_all
WHERE location_id = cp_location_id;
SELECT location_id, decode(bonded, 'Y', 'B', decode(trading, 'Y', 'T', 'N')) loc_subinventory_type
FROM JAI_INV_SUBINV_DTLS
WHERE organization_id = cp_organization_id
AND sub_inventory_name = cp_subinventory;
SELECT asset_inventory
FROM mtl_secondary_inventories
WHERE organization_id = cp_organization_id
AND secondary_inventory_name = cp_subinventory;
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'
AND transaction_id > cp_receive_trx_id;
SELECT nvl(count(distinct vendor_id), 0)
from JAI_RCV_LINE_TAXES
where shipment_line_id = cp_shipment_line_id
AND vendor_id <> (SELECT vendor_id
FROM rcv_shipment_headers
WHERE shipment_header_id = cp_shipment_header_id
)
AND upper(tax_type) NOT IN (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery) --('TDS', 'MODVAT RECOVERY')
AND tax_amount > 0 ;
SELECT subinventory
FROM rcv_transactions
WHERE transaction_type = 'DELIVER'
AND shipment_line_id = (SELECT shipment_line_id
FROM jai_rcv_transactions
WHERE transaction_id = cp_transaction_id);
UPDATE JAI_RCV_TRANSACTIONS
SET transaction_type = lv_transaction_type
WHERE transaction_id = p_transaction_id;
jai_rcv_transactions_pkg.update_row(
p_transaction_id => p_transaction_id,
p_parent_transaction_type => r_parent_base_trx.transaction_type,
p_receipt_num => r_shp_hdr_dtls.receipt_num,
p_inventory_item_id => r_shp_line_dtls.item_id,
p_item_class => nvl(r_loc_item_dtls.item_class, NO_ITEM_CLASS),
p_item_cenvatable => nvl(r_loc_item_dtls.modvat_flag, NO_SETUP),
p_item_excisable => nvl(r_loc_item_dtls.excise_flag, NO_SETUP),
p_item_trading_flag => nvl(r_loc_item_dtls.item_trading_flag, NO_SETUP),
p_inv_item_flag => nvl(r_base_item_dtls.inventory_item_flag, 'N'),
p_inv_asset_flag => r_base_item_dtls.inventory_asset_flag,
p_location_id => nvl(ln_location_id, 0),
p_loc_subinv_type => nvl(lv_loc_subinv_type, NO_SETUP),
p_base_subinv_asset_flag => lv_base_subinv_asset_flag,
p_organization_type => r_loc_orgn_dtls.organization_type,
p_excise_in_trading => nvl(r_loc_orgn_dtls.excise_in_rg23d, 'N'),
p_costing_method => r_mtl_params.primary_cost_method,
p_boe_applied_flag => NULL,
p_third_party_flag => lv_third_party_flag, -- Vijay Shankar for Bug#3940588
p_tax_amount => ln_tax_amount,
p_cenvat_amount => ln_cenvat_amount,
p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
p_excise_invoice_date => r_exc_inv_no.excise_invoice_date,
p_tax_transaction_id => ln_tax_transaction_id, -- Vijay Shankar for Bug#3940588
p_assessable_value => NULL -- This needs to be populated during Tax Calculation itself
);
jai_rcv_transactions_pkg.update_row(
p_transaction_id => p_transaction_id,
p_tax_apportion_factor => ln_tax_apportion_factor
);
- Third Party Flag needs to be Updated if CORRECTion transactions has to uptake the TP functionality or Whole Receipts
functionality is moved into the this NEW RECEIPTS CODE
- Assessable value needs to be populated
---------------------------------------------*/
CURSOR c_trx(cp_transaction_id IN NUMBER) IS
SELECT *
FROM JAI_RCV_TRANSACTIONS
WHERE transaction_id = cp_transaction_id;
SELECT excise_invoice_no, excise_invoice_date, online_claim_flag,
claim_modvat_flag, nvl(rma_type, 'XXXX') rma_type
FROM JAI_RCV_LINES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT count(1) total_cnt,
sum( decode(upper(tax_type), 'EXCISE', 1,
'ADDL. EXCISE', 1,
'OTHER EXCISE', 1,
'CVD', 1,
jai_constants.tax_type_add_cvd,1,
-- Modified by SACSETHI Bug# 5228046
-- Forward porting the change in 11i bug 5365523
-- (Additional CVD Enhancement) as part of the R12 bug 5228046
jai_constants.tax_type_exc_edu_cess,1,
jai_constants.tax_type_cvd_edu_cess,1,jai_constants.tax_type_sh_exc_edu_cess,1,
jai_constants.tax_type_sh_cvd_edu_cess,1, 0) --kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
) excise_cnt
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id
AND tax_type not in (jai_constants.tax_type_tds, jai_constants.tax_type_modvat_recovery); --('TDS', 'MODVAT RECOVERY')
SELECT count(1)
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id
-- AND tax_type NOT IN ('TDS','Modvat Recovery')
AND modvat_flag = jai_constants.yes
AND upper(tax_type) IN ( 'EXCISE',
'ADDL. EXCISE',
'OTHER EXCISE',
'CVD',
jai_constants.tax_type_add_cvd ,
-- Modified by SACSETHI Bug# 5228046
-- Forward porting the change in 11i bug 5365523
-- (Additional CVD Enhancement) as part of the R12 bug 5228046
jai_constants.tax_type_exc_edu_cess,
jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,
jai_constants.tax_type_sh_cvd_edu_cess);--Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
SELECT count(1)
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id
AND modvat_flag = jai_constants.yes
AND tax_type IN
( select tax_type from jai_regime_tax_types_v /*modified this to use View as part of OPM changes */
where regime_code = cp_regime_code
);
SELECT count(1)
FROM JAI_RCV_JOURNAL_ENTRIES
WHERE transaction_id = cp_parent_transaction_id
AND transaction_type = cp_parent_transaction_type;
SELECT creation_date
FROM (SELECT creation_date
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE receipt_ref = cp_parent_transaction_id
AND organization_id = cp_organization_id
AND transaction_source_num = 18
UNION
SELECT creation_date
FROM JAI_CMN_RG_PLA_TRXS
WHERE ref_document_id = cp_parent_transaction_id
AND organization_id = cp_organization_id
AND transaction_source_num = 19);
SELECT creation_date
FROM JAI_CMN_RG_23D_TRXS
WHERE receipt_ref = cp_parent_transaction_id
AND organization_id = cp_organization_id
AND transaction_source_num = 18;
SELECT decode(cp_organization_type, 'M', online_claim_flag, jai_constants.yes) online_claim_flag, -- Changed by Vijay Shankar for Bug #4172424
cenvat_claimed_ptg, cenvat_claimed_amt, unclaim_cenvat_flag, cenvat_amount
FROM JAI_RCV_CENVAT_CLAIMS
WHERE transaction_id = cp_transaction_id;
FND_file.put_line( fnd_file.log, 'Quantity Update Event:'||lv_qty_upd_event);
SELECT count(1) INTO ln_rtv_cnt
FROM JAI_RCV_RTV_DTLS
WHERE transaction_id = r_trx.parent_transaction_id;
jai_rcv_transactions_pkg.update_excise_invoice_no(
p_transaction_id => p_transaction_id,
p_excise_invoice_no => r_exc_inv_no.excise_invoice_no,
p_excise_invoice_date => r_exc_inv_no.excise_invoice_date
);
/* Call to update the Flag values as the validation is completed */
jai_rcv_transactions_pkg.update_process_flags(
p_transaction_id => p_transaction_id,
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_cenvat_rg_flag => p_cenvat_rg_flag,
p_cenvat_rg_message => p_cenvat_rg_message,
p_process_vat_flag => p_process_vat_flag,
p_process_vat_message => p_process_vat_message,
p_process_date => SYSDATE
);
jai_rcv_transactions_pkg.update_attributes(
p_transaction_id => p_transaction_id,
p_attribute1 => lv_excise_inv_gen_action,
p_attribute2 => lv_vat_inv_gen_action
);
SELECT receipt_source_code
from rcv_shipment_headers
WHERE shipment_header_id = (select shipment_header_id
from rcv_transactions
where transaction_id = cp_transaction_id);
SELECT from_organization_id, to_organization_id
FROM rcv_shipment_lines
WHERE shipment_line_id = cp_shipment_line_id;
SELECT count(1)
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id
-- CVD is Not Considered, because in ISO scenario CVD is not supported.
AND upper(tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_sh_exc_edu_cess); --Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
SELECT nvl(trading, 'N') trading, nvl(manufacturing, 'N') manufacturing
, nvl(excise_in_rg23d, 'N') excise_in_rg23d -- Vijay Shankar for Bug#4171469
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND rownum = 1;
FOR i IN (select transaction_id, transaction_type, parent_transaction_id
from rcv_transactions
where shipment_line_id = p_shipment_line_id
and transaction_id <= p_transaction_id
order by transaction_id desc)
LOOP
IF i.transaction_id = ln_trx_id THEN
-- added for Vijay Shankar for Bug#3940588
--IF p_required_trx_type = 'MATCH' THEN /*commented by vkaranam for bug #4636397*/
IF i.transaction_type = 'MATCH' THEN /*added by vkaranam for bug #4636397*/
lv_transaction_type := 'RECEIVE';
eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_tax_amount(14108, 10626, 50, 'N') amount from dual;
SELECT
sum(
nvl(tax_amount, 0) / decode(currency, jai_general_pkg.INDIAN_CURRENCY, cp_curr_conv_rate, 1)
) non_inr_tax_amount,
sum(
nvl(tax_amount, 0) * decode(currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
) inr_tax_amount
FROM JAI_RCV_LINE_TAXES
WHERE shipment_line_id = cp_shipment_line_id
AND tax_type NOT IN ('TDS', 'Modvat Recovery');
eg in ILDEV -> select jai_rcv_trx_processing_pkg.get_trxn_cenvat_amount(14108, 10626, 50) amount from dual;
SELECT
sum(
nvl(a.tax_amount, 0) * (b.mod_cr_percentage/100)
* decode(a.currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
) manufacturing_cenvat,
sum(
nvl(a.tax_amount, 0) * decode(a.currency, jai_general_pkg.INDIAN_CURRENCY, 1, cp_curr_conv_rate)
) trading_cenvat
FROM JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
WHERE shipment_line_id = cp_shipment_line_id
AND a.tax_id = b.tax_id
AND upper(a.tax_type) IN ( 'EXCISE',
'ADDL. EXCISE',
'OTHER EXCISE',
'CVD',
jai_constants.tax_type_add_cvd,
-- Modified by SACSETHI Bug# 5228046
-- Forward porting the change in 11i bug 5365523
-- (Additional CVD Enhancement) as part of the R12 bug 5228046
jai_constants.tax_type_exc_edu_cess,
jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess,
jai_constants.tax_type_sh_cvd_edu_cess) --Added by kunkumar for bugno5989740 -- Vijay Shankar for Bug#3940588 EDU CESS
AND (cp_organization_type = 'T' OR (cp_organization_type <> 'T' AND a.modvat_flag = 'Y') );
SELECT qty_received, transaction_id
FROM JAI_RCV_LINES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT qty_received, transaction_id
FROM JAI_RCV_LINES
WHERE shipment_line_id = cp_shipment_line_id;
SELECT accrue_on_receipt_flag
FROM po_distributions_all
WHERE po_distribution_id = cp_po_distribution_id;
SELECT accrue_on_receipt_flag
FROM po_line_locations_all
WHERE line_location_id = cp_po_line_location_id;